ECC Genealogy and Trace, Serial Numbers
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
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 |
|
LOV |