ECC Process Genealogy and Trace, Sales Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: opm-gnt-sotransactions
Query Procedure: GMO_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: oe_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: opm-gnt-sotransactions
Query Procedure: GMO_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: oe_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Process 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) */ ooh.order_number, ooh.order_source_id, ooh.header_id, ooh.org_id org_id, (select name from hr_all_organization_units h where h.organization_id = 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) 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, GMO_ECC_GNT_PVT.Check_SO_Holds(ooh.header_id) so_hold, so_txns.inventory_item_id, msiv.concatenated_segments item, msiv.description from (select /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ 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 FROM mtl_material_transactions mmt WHERE mmt.transaction_source_type_id IN (2,12,8) AND mmt.TRANSACTION_DATE >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:10:45','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 FROM( select /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ 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 from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln 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.TRANSACTION_DATE >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) union all select /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ 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 from mtl_material_transactions mmt, mtl_unit_transactions mut 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.TRANSACTION_DATE >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:10:45','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, mtl_system_items_vl msiv /*, ( 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 so_txns.inventory_item_id = msiv.inventory_item_id 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 |