隨手筆記

透過料號查詢訂單是否未CLOSED

select oh.order_number,
       ol.line_number || '.' || ol.shipment_number line,
       ol.flow_status_code,
       oh.created_by
  from oe_order_lines_all ol, oe_order_headers_all oh
 where ol.inventory_item_id = (select msib.inventory_item_id
                                 from mtl_system_items_b msib
                                where msib.segment1 = 'XXXXX'
                                  and rownum <= 1)
   and ol.flow_status_code not in ('CLOSED','CANCELLED')
   and ol.header_id = oh.header_id

--------------------------------------------------------------------------------

透過ship to org id 查詢訂單是否未Closd

select oh.order_number,
       ol.line_number || '.' || ol.shipment_number line_number,
       ol.flow_status_code status
  from oe_order_lines_all ol, oe_order_headers_all oh
 where ol.header_id = oh.header_id
   and oh.ship_to_org_id = XXXX   
   and ol.flow_status_code not in ('CLOSED','CANCELLED')