OM Useful Queries

-- zom
select hou.name ou, mp.organization_code,mp.organization_id,ooha.header_id,ooha.order_number,oola.*
from oe_order_headers_all ooha
    ,oe_order_lines_all oola
    ,hr_operating_units hou
    ,mtl_parameters mp
where 1=1
  and ooha.order_number = 11238463494
  and ooha.org_id = 6941
  and oola.header_id = ooha.header_id
  and ooha.org_id = hou.organization_id
  and oola.ship_from_org_id = mp.organization_id
  and oola.cancelled_flag <> 'Y'
  and oola.flow_status_code not in ('CLOSED','AWAITING_FULFILLMENT','CANCELLED')
  order by 1,2,3
  ;

-- zomwsh
select hou.name ou, mp.organization_code,ooha.header_id,ooha.order_number
,wnd.name delivery,fcr.request_id pick_release_request_id,wdd.request_id ship_confirm_request_id
from oe_order_headers_all ooha
    ,oe_order_lines_all oola
    ,hr_operating_units hou
    ,wsh_delivery_assignments wda
    ,wsh_delivery_details wdd
    ,wsh_new_deliveries wnd
    ,wsh_picking_batches wpb
    ,mtl_parameters mp
    ,fnd_concurrent_requests fcr
where 1=1 and ooha.order_number = 11238463494
  and oola.header_id = ooha.header_id
  and ooha.org_id = hou.organization_id
  and wdd.source_header_id = oola.header_id
  and wdd.source_line_id = oola.line_id
  and wda.delivery_detail_id = wdd.delivery_detail_id
  and wda.delivery_id = wnd.delivery_id
  and wpb.batch_id = wdd.batch_id
  --and wdd.delivery_detail_id = 196027682
  and wdd.organization_id = mp.organization_id
  and fcr.request_id = wpb.request_id
  --and fcr.argument1 = wpb.batch_id
  --and fcr.concurrent_program_id = 40178
  order by 1,2,3
  ;

--zwsh
select wnd.* from wsh_new_deliveries wnd, wsh_delivery_assignments wda, wsh_delivery_details wdd
where 1=1
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
--and wdd.delivery_detail_id = 1245469
and wda.delivery_id = 90431545
;

PO, PR and RCV useful queries

-- zpr
select pha.*
from po_requisition_headers_all prha
, po_requisition_lines_all prla
where 1=1
--and prha.org_id = 6941
and prha.segment1 = '15668'
and prha.requisition_header_id = prla.requisition_header_id
--and prla.quantity <> prla.quantity_delivered
;

-- zprpo
select prha.segment1,prla.*
from po_headers_all pha
, po_lines_all pla
, po_distributions_all pda
, po_req_distributions_all prda
, po_requisition_lines_all prla
, po_requisition_headers_all prha
where 1=1
and pha.org_id = 6941
and pha.segment1 = '6715'
and pha.po_header_id = pla.po_header_id
and pla.po_line_id = pda.po_line_id
and pda.req_distribution_id = prda.distribution_id
and prda.requisition_line_id = prla.requisition_line_id
and prla.requisition_header_id = prha.requisition_header_id
;

-- zpo
select pha.segment1,plla.*
from po_headers_all pha
    ,po_lines_all pla
    --,po_line_locations_all plla
where 1=1 --and pha.po_header_id = 73815567
  and pha.segment1 = '2740'
  and pha.org_id = 6941
  and pha.po_header_id = pla.po_header_id
  and pla.po_header_id = pla.po_header_id
  --and pla.po_line_id = plla.po_line_id
  order by 1,2,3
  ;

--zrcv
select rsh.*,rsl.*
from rcv_shipment_headers rsh
    ,rcv_shipment_lines rsl
where 1=1 --and pha.po_header_id = 73815567
  and rsh.receipt_num in ('11228','12175','11362')
  --and rsh.shipment_num in ('11228','12175','11362')
  and rsh.shipment_header_id = rsl.shipment_header_id
  --order by 1,2,3
  ;

--zporcv 
select pha.segment1,pla.line_num,rt.*
from po_headers_all pha
    ,po_lines_all pla
    --,po_line_locations_all plla
,rcv_shipment_headers rsh
    ,rcv_shipment_lines rsl
    ,rcv_transactions rt
where 1=1 --and pha.po_header_id = 73815567
  and pha.segment1 = '2740'
  and pha.org_id = 6941
  and pha.po_header_id = pla.po_header_id
  and pla.po_header_id = plla.po_header_id
  --and pla.po_line_id = plla.po_line_id
  and rsl.po_line_id = pla.po_line_id
  and rsh.shipment_header_id = rsl.shipment_header_id
  and rsl.shipment_header_id = rt.shipment_header_id
  and rsl.shipment_line_id = rt.shipment_line_id
  order by 1,2,3
  ;