Train Order Managment

Description
query sales for OM
Run Train Order Managment and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT hp.party_name "Customer",
       msi.segment1  ISBN,
       substr(msi.DEFAULT_SO_SOURCE_TYPE, 1, 10) item_source,
       ooh.order_number order_num,
       hl_ship.address1 || Decode(hl_ship.address2, NULL, '', chr(10)) ||
       hl_ship.address2 || Decode(hl_ship.address3, NULL, '', chr(10)) ||
       hl_ship.address3 || Decode(hl_ship.address4, NULL, '', chr(10)) ||
       hl_ship.address4 || Decode(hl_ship.city, NULL, '', chr(10)) ||
       hl_ship.city || Decode(hl_ship.state, NULL, '', ',') ||
       hl_ship.state || Decode(hl_ship.postal_code, '', ',') ||
       hl_ship.postal_code ship_to,
       oola.cust_po_number PO,
       hcs_ship.dates_positive_tolerance LAD_DAYS,
       oola.request_date RD,
       oola.promise_date,
       oola.schedule_ship_date SSD,
       trunc(oola.actual_shipment_date) Date_ship,
       trunc(oola.LATEST_ACCEPTABLE_DATE) LAD,
       oola.line_number || '.' || oola.shipment_number line_num,
       oola.ordered_quantity QTY,
       oola.SHIPPED_QUANTITY Ship_qty,
       oola.INVOICED_QUANTITY inv_qty,
       ooh.transactional_curr_code,
       oola.unit_selling_price price,
       round(oola.ordered_quantity * oola.unit_selling_price) amount,
       oola.flow_status_code Status,
       flv.meaning Item_type,
       ooh.CREATION_DATE Created,
       msi.INVENTORY_ITEM_STATUS_CODE item_status,
       msi.invoice_enabled_flag inv_enaB
  FROM apps.oe_order_headers_all     ooh,
       apps.oe_order_lines_all       oola,
       apps.mtl_system_items_b       msi,
       apps.oe_transaction_types_tl  ootl,
       apps.oe_transaction_types_all oot,
       apps.fnd_lookup_values        flv,
       apps.hz_parties               hp,
       apps.hz_cust_accounts         hca,
       APPS.hz_cust_site_uses_all    hcs_ship,
       apps.hz_cust_acct_sites_all   hca_ship,
       apps.hz_party_sites           hps_ship,
       apps.hz_parties               hp_ship,
       apps.hz_locations             hl_ship
 
 WHERE 1=1
 and oola.header_id = ooh.header_id
   AND oola.inventory_item_id = msi.inventory_item_id
   AND oola.ship_from_org_id = msi.organization_id
      --AND oola.flow_status_code = 'AWAITING_SHIPPING'
   AND oot.transaction_type_id = ooh.order_type_id
   AND ootl.transaction_type_id = ooh.order_type_id
   AND ootl.language = 'US'
   AND ooh.open_flag = 'Y'
      --and msi.SHIPPABLE_ITEM_FLAG = 'Y'
      --and msi.SOURCE_TYPE=1
   and msi.item_type = apps.flv.lookup_code
   and flv.lookup_type = 'ITEM_TYPE'
   and msi.segment1 = oola.ordered_item
      --and oola.request_date < oola.schedule_ship_date
   AND ooh.creation_date > sysdate - 1
      --AND ooh.creation_date between to_date('10-SEP-16', 'DD-MON-RR') and
      --  to_date('22-SEP-16', 'DD-MON-RR')
  -- and oola.SHIPPED_QUANTITY > 0
   AND ooh.sold_to_org_id = hca.cust_account_id(+)
   AND hca.party_id = hp.party_id(+)
      --and hp.party_name = 'AMAZON EU SARL, UK BRANCH'
   and oola.CANCELLED_FLAG = 'N'
   AND ooh.ship_to_org_id = hcs_ship.site_use_id
   AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
   AND hca_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.party_id = hp_ship.party_id
   AND hps_ship.location_id = hl_ship.location_id
      --and wdd.source_code = 'OE'
      --and oola.header_id = wdd.source_header_id (+)
      --and wdd.released_status in( 'S','R', 'B')
      --and oola.line_id = wdd.source_line_id (+)
Parameter Name SQL text Validation
Line Status
oola.flow_status_code=xxen_util.lookup_code(:line_status,'LINE_FLOW_STATUS',660)
LOV
Blitz Report™