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
;