Train Order Managment
Description
query sales for OM
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 |
|
LOV |