ECC Process Genealogy and Trace, Serial Numbers Copy link

select
x.*
from
(
 SELECT * FROM (SELECT /*+ leading(ser_txn) no_merge(ser_txn)  */  ser_txn.organization_id|| '-'|| msn.gen_object_id org_gen_obj_id,
                to_char(ser_txn.query_type||'-'||ser_txn.transaction_id
                        ||'-'|| msn.gen_object_id||'-'||ser_txn.entity_id) ecc_spec_id,
                ser_txn.organization_id,
                (select name from hr_all_organization_units h
                 where h.organization_id = ser_txn.organization_id) org_name,
                mp.organization_code,
                ser_txn.inventory_item_id,
                msiv.concatenated_segments item,
                msiv.description,
                msn.serial_number,
			   to_char(msn.serial_number
						|| ' ('
						|| msiv.concatenated_segments
						|| ', '
						|| mp.organization_code
						|| ')') SERIAL_ITEM_ORG,
                msn.gen_object_id,
                decode(ser_txn.query_type, 2, ser_txn.entity_id, NULL) work_order_id,
                decode(ser_txn.query_type, 3, msn.gen_object_id, 6, msn.gen_object_id,NULL) transfer_gen_obj_id,
                decode(ser_txn.query_type, 3, ser_txn.entity_id, 6, ser_txn.entity_id, NULL) transfer_org_id,
                decode(ser_txn.query_type, 1, ser_txn.entity_id, 4, ser_txn.entity_id,NULL) so_id,
                ser_txn.transaction_date,
                ser_txn.transaction_quantity,
                ser_txn.transaction_uom,
                ser_txn.revision ser_revision,
                ser_txn.transaction_action_id,
                ser_txn.transaction_type_id,
                (SELECT transaction_type_name
                 FROM mtl_transaction_types mtt
                 WHERE mtt.transaction_type_id = ser_txn.transaction_type_id
                ) transaction_type_name,
                serial_status_lkp.meaning serial_status,
                nodetype_lkp.meaning node_type,
                serial_status_lkp.language language,
                /* action_lkp1.meaning */ 'QA' SERIAL_QA_RESULTS,
                /* action_lkp2.meaning */ 'VIEW' SERIAL_TXN_VIEW,
                msn.initialization_date,
                msn.revision,
                msn.lot_number,
                msn.parent_serial_number,
                msn.vendor_serial_number,
                msn.vendor_lot_number,
                msn.origination_date,
                msn.eqp_serial_number,
                msn.inspection_status,
                msn.maintainable_flag,
                msn.parent_lot_number,
                msn.operational_status_flag,
                msn.ship_date,
                msn.completion_date,
				decode(msn.intraoperation_step_type,4,'FALSE','TRUE') reject_flag,
				decode(msn.intraoperation_step_type,5,'FALSE','TRUE') scrap_flag,
				mtl_status_lkp.status_code mtl_status_code,
                null status_flag
            FROM
                ( SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15)  */ 
                        1 query_type,
                        mmt.organization_id,
                        mmt.inventory_item_id,
                        mut.serial_number,
                        (select ool.header_id from oe_order_lines_all ool
                         where ool.line_id = mmt.trx_source_line_id) entity_id,
                        mmt.transaction_date   transaction_date,
                        1 transaction_quantity,
                        mmt.transaction_id,
                        mmt.transaction_uom    transaction_uom,
                        mmt.transaction_action_id,
                        mmt.transaction_type_id,
                        mmt.revision
                    FROM mtl_material_transactions   mmt,
                        mtl_unit_transactions       mut
                    WHERE mmt.transaction_source_type_id IN (2,12,8)
                        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)
                    UNION ALL
                    SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15)  */ 
                           6,
                           mmt.transfer_organization_id,
                           mmt.inventory_item_id,
                           mut.serial_number,
                           mmt.organization_id,
                           mmt.transaction_date,
                           1,
                           mmt.transaction_id,
                           mmt.transaction_uom,
                           mmt.transaction_action_id,
                           mmt.transaction_type_id,
                           mmt.revision
                    FROM mtl_material_transactions mmt,
                         mtl_unit_transactions mut
                    where mmt.transaction_type_id in (12,3)
                    AND mut.transaction_id = mmt.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)
                    AND mut.receipt_issue_type=2
                    UNION ALL
                    SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15)  */ 
                        2,
                        mmt.organization_id,
                        mmt.inventory_item_id,
                        mut.serial_number,
                        mmt.transaction_source_id,
                        mmt.transaction_date transaction_date,
                        1 transaction_quantity,
                        mmt.transaction_id,
                        mmt.transaction_uom,
                        mmt.transaction_action_id,
                        mmt.transaction_type_id,
                        mmt.revision
                    FROM mtl_material_transactions   mmt,
                        mtl_unit_transactions       mut
                    WHERE mmt.transaction_source_type_id = 5
                        AND mmt.transaction_id = mut.transaction_id
                        AND mmt.organization_id = nvl('',mmt.organization_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.transaction_action_id IN (1,27,33,34)
                    UNION ALL
                    SELECT /*+ INDEX(mut MTL_UNIT_TRANSACTIONS_N5)  */ 
                        0,
                        mut.organization_id,
                        mut.inventory_item_id,
                        mut.serial_number,
                        mut.transaction_source_id,
                        mut.transaction_date transaction_date,
                        1 transaction_quantity,
                        mut.transaction_id,
                        NULL,
                        NULL,
                        NULL,
                        NULL
                    FROM
                        mtl_unit_transactions mut
                    WHERE
                        mut.transaction_source_type_id IN (1,5,2,12,8,13,7)
                        AND mut.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                        AND mut.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                        AND mut.organization_id = nvl('',mut.organization_id)
					UNION ALL
                    SELECT /*+ INDEX(wmt WIP_MOVE_TRANSACTIONS_N2)  */ 
                        0,
                        wmt.organization_id,
                        wmt.primary_item_id,
                        msn.serial_number,
                        wmt.wip_entity_id,
                        wmt.transaction_date ,
                        1 transaction_quantity,
                        wmt.transaction_id,
                        NULL,
                        NULL,
                        NULL,
                        NULL
                    FROM
                        mtl_serial_numbers msn,
						wip_move_transactions wmt,
						wip_serial_move_transactions wsmt
                    WHERE wmt.wip_entity_id = msn.wip_entity_id
					    AND wmt.transaction_id = wsmt.transaction_id
						AND msn.inventory_item_id = wmt.primary_item_id
						AND msn.serial_number = wsmt.assembly_serial_number
                        AND wmt.to_operation_seq_num = msn.operation_seq_num
						AND wmt.to_intraoperation_step_type = msn.intraoperation_step_type
                        AND	msn.intraoperation_step_type IN (4,5)
                        AND wmt.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                        AND wmt.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                        AND wmt.organization_id = nvl('',wmt.organization_id)
                ) ser_txn,
                mtl_serial_numbers    msn,
                mtl_system_items_vl   msiv,
                mtl_parameters        mp,
                ( SELECT ml4.meaning ,ml4.lookup_code , ml4.language
                  FROM fnd_lookup_values ml4
                  WHERE ml4.lookup_type = 'SERIAL_NUM_STATUS'
                  AND ml4.language in ('US')
                ) serial_status_lkp ,
                ( select meaning,language FROM fnd_lookup_values
                  WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE'
                  AND lookup_code = '2' AND language in ('US')
                ) nodetype_lkp,
                (SELECT mstl.status_code,language,mstl.status_id
                 FROM mtl_material_statuses_tl mstl
                 WHERE mstl.language in ('US')
                ) mtl_status_lkp
				/* ,
                ( select meaning,language FROM fnd_lookup_values
                  WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE'
                  AND lookup_code = '5'
                ) action_lkp1,
                ( select meaning,language FROM fnd_lookup_values
                  WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE'
                  AND lookup_code = '7'
                ) action_lkp2 */
            WHERE
                ser_txn.inventory_item_id = msn.inventory_item_id
                AND ser_txn.serial_number = msn.serial_number
                AND ser_txn.organization_id = msiv.organization_id
                AND ser_txn.inventory_item_id = msiv.inventory_item_id
                AND ser_txn.organization_id = mp.organization_id
                AND msn.current_status = serial_status_lkp.lookup_code
                AND nodetype_lkp.language = serial_status_lkp.language
				AND mtl_status_lkp.status_id = msn.status_id
                AND mtl_status_lkp.language = serial_status_lkp.language
                /* AND action_lkp1.language = serial_status_lkp.language
                AND action_lkp2.language = serial_status_lkp.language */
                AND serial_status_lkp.language in ('US')) PIVOT ( MAX ( serial_status ) AS serial_status ,MAX(node_type) AS node_type,
			                                              MAX(mtl_status_code) AS mtl_status_code
			/* , MAX(SERIAL_QA_RESULTS) AS SERIAL_QA_RESULTS , MAX(SERIAL_TXN_VIEW) AS SERIAL_TXN_VIEW */
			FOR language  IN ('US' "US")) 
) x
where
2=2