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
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
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV