ECC Genealogy and Trace, Sales Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: gnt-sotransactions
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: oe_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: gnt-sotransactions
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: oe_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Genealogy and Trace, Sales Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( select /*+ leading(so_txns) no_merge(so_txns) use_nl(so_txns ooh) */ ooh.order_number, ooh.order_source_id, ooh.header_id, ooh.org_id org_id, WIP_ECC_GNT_PVT.get_orgname(ooh.org_id) ou_name, hp.party_id, hp.party_name, salesrep_lkp.resource_name SalesRep, nodetype_lkp.meaning node_type, /* action_lkp.meaning */ 'QA' SO_QA_RESULTS, ooh.booked_date, ordered_date, order_type_lkp.name order_type, order_type_lkp.language language, to_char(so_txns.query_type||'-'||so_txns.organization_id||'-'||so_txns.transaction_id|| '-'||so_txns.gen_object_id||'-'||so_txns.lot_number) ecc_spec_id, so_txns.transaction_id, so_txns.transaction_type_id, (select transaction_type_name from mtl_transaction_types mtt where mtt.transaction_type_id = so_txns.transaction_type_id) transaction_type_name, so_txns.transaction_action_id, so_txns.transaction_quantity, so_txns.transaction_uom, so_txns.transaction_date, so_txns.organization_id txn_org_id , so_txns.revision, decode(so_txns.query_type,1,so_txns.gen_object_id,null) lot_gen_obj_id, decode(so_txns.query_type,2,so_txns.gen_object_id,null) ser_gen_obj_id, (select WIP_ECC_GNT_PVT.Check_SO_Holds(ooh.header_id) from dual) so_hold, so_txns.inventory_item_id MMT_INVENTORY_ITEM_ID, so_txns.lot_number MMT_LOT_NUMBER, so_txns.subinventory_code MMT_SUBINVENTORY_CODE, so_txns.locator_id MMT_LOCATOR_ID, WIP_ECC_GNT_PVT.get_locator(so_txns.organization_id, so_txns.subinventory_code, so_txns.locator_id) MMT_LOCATOR_SEGMENTS, so_txns.transfer_subinventory MMT_TRANSFER_SUBINVENTORY, so_txns.transfer_locator_id MMT_TRANSFER_LOCATOR_ID, WIP_ECC_GNT_PVT.get_locator(so_txns.organization_id, so_txns.transfer_subinventory, so_txns.transfer_locator_id) MMT_TRANSFER_LOCATOR_SEGMENTS, WIP_ECC_GNT_PVT.get_username(so_txns.created_by) MMT_CREATED_BY, so_txns.pick_date PICK_DATE, so_txns.ship_date SHIP_DATE, WIP_ECC_GNT_PVT.get_orgname(so_txns.organization_id) ORGANIZATION_NAME, so_txns.po_header_id PO_HEADER_ID, so_txns.item MMT_ITEM from (select /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ 0 query_type, (select ool.header_id from oe_order_lines_all ool where ool.line_id = mmt.trx_source_line_id and ool.inventory_item_id = mmt.inventory_item_id) header_id, mmt.transaction_id, mmt.transaction_type_id, mmt.transaction_action_id, abs(mmt.transaction_quantity) transaction_quantity, mmt.transaction_uom, mmt.transaction_date, mmt.revision, null gen_object_id, mmt.organization_id, mmt.inventory_item_id, '####' lot_number, mmt.subinventory_code, nvl(mmt.locator_id,-999) locator_id, mmt.transfer_subinventory, nvl(mmt.transfer_locator_id,-999) transfer_locator_id, mmt.created_by, mmt.transaction_date pick_date, mmt.transaction_date ship_date, to_number(null) po_header_id, msi.concatenated_segments item FROM mtl_material_transactions mmt, mtl_system_items_vl msi WHERE mmt.transaction_source_type_id IN (2,12,8) AND mmt.organization_id =msi.organization_id AND mmt.inventory_item_id=msi.inventory_item_id AND msi.lot_control_code =1 AND mmt.TRANSACTION_DATE >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) UNION ALL select /*+ INDEX(jsr JMF_SHIKYU_REPLENISHMENTS_N1) */ 3 query_type, jsr.REPLENISHMENT_SO_HEADER_ID header_id, to_number(REPLENISHMENT_SO_LINE_ID) transaction_id, to_number(null) transaction_type_id, to_number(null) transaction_action_id, abs(jsr.allocated_quantity) transaction_quantity, to_char(null) transaction_uom, jsr.SCHEDULE_SHIP_DATE transaction_date, to_char(null) revision, to_number(null) gen_object_id, jsr.TP_ORGANIZATION_ID organization_id, jsr.shikyu_component_id, '####' lot_number, to_char(null), -999 locator_id, to_char(null), -999 transfer_locator_id, null, null pick_date, null ship_date, jsr.replenishment_po_header_id po_header_id, null item FROM JMF_SHIKYU_REPLENISHMENTS jsr WHERE jsr.SCHEDULE_SHIP_DATE >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND jsr.SCHEDULE_SHIP_DATE <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') AND (jsr.TP_ORGANIZATION_ID = nvl('',jsr.TP_ORGANIZATION_ID) or jsr.OEM_ORGANIZATION_ID = nvl('',jsr.OEM_ORGANIZATION_ID) ) UNION ALL SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ 0 query_type, (select ool.header_id from oe_order_lines_all ool where ool.line_id = mmt.trx_source_line_id and ool.inventory_item_id = mmt.inventory_item_id) header_id, mmt.transaction_id , mmt.transaction_type_id , mmt.transaction_action_id , abs(mmt.transaction_quantity) transaction_quantity , mmt.transaction_uom , mmt.transaction_date , mmt.revision , NULL gen_object_id , mmt.organization_id , mmt.inventory_item_id , mtln.lot_number lot_number , mmt.subinventory_code , nvl(mmt.locator_id, - 999) locator_id , mmt.transfer_subinventory , nvl(mmt.transfer_locator_id, - 999) transfer_locator_id , mmt.created_by , mmt.transaction_date pick_date , mmt.transaction_date ship_date , to_number(null) po_header_id , msi.concatenated_segments item FROM mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln , mtl_system_items_vl msi WHERE mmt.transaction_source_type_id IN (2, 12, 8) AND mmt.transaction_id = mtln.transaction_id AND mmt.transaction_id = mtln.transaction_id AND mmt.inventory_item_id = mtln.inventory_item_id AND mmt.organization_id = mtln.organization_id AND mmt.organization_id=msi.organization_id AND mmt.inventory_item_id=msi.inventory_item_id AND mmt.TRANSACTION_DATE >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) UNION ALL SELECT query_type, (select ool.header_id FROM mtl_material_transactions mmt1, oe_order_lines_all ool WHERE mmt1.transaction_id =iso_txn.transfer_transaction_id AND ool.line_id =mmt1.source_line_id AND ool.inventory_item_id =iso_txn.inventory_item_id) header_id, transaction_id, transaction_type_id, transaction_action_id, transaction_quantity, transaction_uom, transaction_date, revision, gen_object_id, organization_id, iso_txn.inventory_item_id, iso_txn.lot_number, iso_txn.subinventory_code, iso_txn.locator_id, iso_txn.transfer_subinventory, iso_txn.transfer_locator_id, iso_txn.created_by, iso_txn.transaction_date pick_date, iso_txn.transaction_date ship_date, to_number(null) po_header_id, iso_txn.item FROM( select /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ 1 query_type, mmt.transfer_transaction_id, mmt.inventory_item_id, mmt.transaction_id, mmt.transaction_type_id, mmt.transaction_action_id, abs(mtln.transaction_quantity) transaction_quantity, mmt.transaction_uom, mmt.transaction_date, mmt.revision, (select mln.gen_object_id from mtl_lot_numbers mln where mln.lot_number = mtln.lot_number and mln.inventory_item_id = mtln.inventory_item_id and mln.organization_id = mtln.organization_id) gen_object_id, mmt.organization_id, mtln.lot_number, mmt.subinventory_code, nvl (mmt.locator_id, - 999) locator_id, mmt.transfer_subinventory, nvl (mmt.transfer_locator_id, - 999) transfer_locator_id, mmt.created_by, mmt.transaction_date pick_date, mmt.transaction_date ship_date, to_number(null) po_header_id, msi.concatenated_segments item from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln, mtl_system_items_vl msi where mmt.transaction_action_id=12 and mmt.transaction_type_id=61 and mmt.transaction_source_type_id=7 and mmt.transaction_id = mtln.transaction_id and mmt.inventory_item_id=msi.inventory_item_id and mmt.organization_id=msi.organization_id AND mmt.TRANSACTION_DATE >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) union all select /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ 2 query_type, mmt.transfer_transaction_id, mmt.inventory_item_id, mmt.transaction_id, mmt.transaction_type_id, mmt.transaction_action_id, 1, mmt.transaction_uom, mmt.transaction_date, mmt.revision, (select msn.gen_object_id from mtl_serial_numbers msn where msn.serial_number = mut.serial_number and msn.inventory_item_id = mut.inventory_item_id) gen_object_id, mmt.organization_id, '####' lot_number, mmt.subinventory_code, nvl (mmt.locator_id, - 999) locator_id, mmt.transfer_subinventory, nvl (mmt.transfer_locator_id, - 999) transfer_locator_id, mmt.created_by, mmt.transaction_date pick_date, mmt.transaction_date ship_date, to_number(null) po_header_id, msi.concatenated_segments item from mtl_material_transactions mmt, mtl_unit_transactions mut, mtl_system_items_vl msi where mmt.transaction_action_id=12 and mmt.transaction_type_id=61 and mmt.transaction_source_type_id=7 and mmt.transaction_id = mut.transaction_id and mmt.organization_id=msi.organization_id and mmt.inventory_item_id=msi.inventory_item_id AND mmt.TRANSACTION_DATE >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id)) iso_txn) so_txns, oe_order_headers_all ooh, hz_cust_accounts hca, hz_parties hp, ( SELECT t.name name ,t.language,b.transaction_type_id ,b.org_id FROM oe_transaction_types_tl t,oe_transaction_types_all b WHERE b.transaction_type_code = 'ORDER' AND b.transaction_type_id = t.transaction_type_id AND t.language in ('US') ) order_type_lkp, ( SELECT jrt.resource_name , jrt.language,jrs.org_id,jrs.salesrep_id FROM jtf_rs_salesreps jrs, jtf_rs_resource_extns jre, jtf_rs_resource_extns_tl jrt WHERE jrs.resource_id = jre.resource_id AND jre.resource_id = jrt.resource_id AND jre.category = jrt.category AND jre.category IN ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT') AND jrt.language in ('US') ) salesrep_lkp , ( select meaning,language,lookup_code FROM fnd_lookup_values WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE' AND language in ('US') ) nodetype_lkp /*, ( select meaning,language FROM fnd_lookup_values WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE' AND lookup_code = '6' ) action_lkp */ where ooh.header_id = so_txns.header_id and ooh.sold_to_org_id = hca.cust_account_id AND hca.party_id = hp.party_id AND order_type_lkp.transaction_type_id = ooh.order_type_id AND order_type_lkp.org_id=ooh.org_id AND order_type_lkp.language = salesrep_lkp.language AND salesrep_lkp.salesrep_id = ooh.salesrep_id AND salesrep_lkp.org_id= ooh.org_id AND nodetype_lkp.lookup_code = to_char(decode(ooh.source_document_type_id,10,10,9)) AND nodetype_lkp.language = order_type_lkp.language /* AND action_lkp.language = order_type_lkp.language */ AND order_type_lkp.language in('US') ) PIVOT ( MAX ( order_type ) AS order_type ,MAX( node_type ) AS node_type, /*MAX( SO_QA_RESULTS ) AS SO_QA_RESULTS , */ MAX ( SalesRep ) AS SalesRep FOR language IN ('US' "US")) ) x where 2=2 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Operating Unit |
|
LOV |
