ECC Genealogy and Trace, Serial Numbers

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: gnt-serialtrasactions
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Genealogy and Trace, Serial Numbers and other Oracle EBS reports with Blitz Report™ on our demo environment
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,
                WIP_ECC_GNT_PVT.get_orgname(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_N5)  */ 
                        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: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 /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                           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: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)
                    AND mut.receipt_issue_type=2
                    UNION ALL
                    SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                        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: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.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:09:50','DD-MON-RR HH24:MI:SS')
                        AND mut.transaction_date <= TO_DATE('12-DEC-22 10:09:50','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:09:50','DD-MON-RR HH24:MI:SS')
                        AND wmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','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
Parameter Name SQL text Validation
Organization Code 
x.organization_code=:organization_code
 LOV
Download
 
Related Blitz Reports
ECC Process Genealogy and Trace, Serial Numbers ECC Genealogy and Trace, Work Orders CAC Purchase Price Variance ECC Discrete Manufacturing, Serial Numbers ECC Process Genealogy and Trace, Lot Numbers ECC Genealogy and Trace, Sales Orders ECC Genealogy and Trace, Lot Numbers GL Account Analysis (Distributions) GL Account Analysis
Blitz Report™

Blitz Report™ provides multiple benefits: