ECC Process Genealogy and Trace, Sales Orders

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