ECC Inventory Management, Inventory Trace Issues
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Inventory Trace Issues Data Set
Dataset Key: inv-trace-issues
Query Procedure: INV_ECC_TRACK_TRACE_PVT.get_ecc_data_load_info
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Description: Inventory Trace Issues Data Set
Dataset Key: inv-trace-issues
Query Procedure: INV_ECC_TRACK_TRACE_PVT.get_ecc_data_load_info
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select x.* from ( SELECT * FROM (SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ to_char (1 || '-' || to_char (mtln.transaction_id)|| '-' || mtln.lot_number) ecc_spec_id , mmt.subinventory_code , mmt.inventory_item_id , mmt.organization_id , lot_number , flv1.meaning TRANSACTION_TYPE , mtt.transaction_type_name , (SELECT concatenated_segments FROM mtl_item_locations_kfv WHERE inventory_location_id =mmt.locator_id AND organization_id = mmt.organization_id AND subinventory_code = nvl(mmt.subinventory_code,subinventory_code)) locator , nvl (mmt.locator_id, - 999) locator_id , hou.name organization_name , mmt.transaction_id , ( SELECT user_name FROM fnd_user WHERE user_id = mmt.created_by ) user_name , mmt.transaction_date , mp.organization_code , hou.language FROM mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln, mtl_transaction_types_tl mtt , mtl_parameters mp , hr_all_organization_units_tl hou , fnd_lookup_values flv1 WHERE mmt.transaction_action_id != 24 AND mmt.transaction_id = mtln.transaction_id AND mmt.transaction_type_id = mtt.transaction_type_id AND ( logical_transaction = 2 OR logical_transaction IS NULL ) AND mmt.transaction_action_id = 1 AND mmt.transaction_type_id IN (32, 1, 63 , 31) AND mmt.transaction_quantity < 0 AND mmt.transaction_source_type_id in (6,13,4) AND mp.organization_id = mmt.organization_id AND mp.organization_id = hou.organization_id AND flv1.lookup_type (+) = 'INV_ECC_TRACK_TRACE_NODETYPE' AND flv1.view_application_id (+) = 700 AND flv1.lookup_code (+) =70 AND flv1.language (+) = hou.language AND hou.language in ('US')AND hou.language=mtt.language AND mmt.organization_id = nvl('',mmt.organization_id) AND mmt.transaction_date >= TO_DATE('12-NOV-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:10:02','DD-MON-RR HH24:MI:SS') UNION SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ to_char (0 || '-' || to_char (mmt.transaction_id)) ecc_spec_id , subinventory_code , mmt.inventory_item_id , mmt.organization_id , '####' lot_number , flv1.meaning TRANSACTION_TYPE , mtt.transaction_type_name , (SELECT concatenated_segments FROM mtl_item_locations_kfv WHERE inventory_location_id =mmt.locator_id AND organization_id = mmt.organization_id AND subinventory_code = nvl(mmt.subinventory_code,subinventory_code)) locator , nvl (mmt.locator_id, - 999) locator_id , hou.name organization_name , mmt.transaction_id , ( SELECT user_name FROM fnd_user WHERE user_id = mmt.created_by ) user_name , mmt.transaction_date , mp.organization_code , hou.language FROM mtl_material_transactions mmt , mtl_transaction_types_tl mtt , mtl_parameters mp , mtl_system_items_b_kfv msib , hr_all_organization_units_tl hou , fnd_lookup_values flv1 WHERE mmt.transaction_action_id != 24 AND mmt.transaction_type_id = mtt.transaction_type_id AND ( logical_transaction = 2 OR logical_transaction IS NULL ) AND mmt.transaction_action_id = 1 AND mmt.transaction_type_id IN (32, 1, 63 , 31) AND mmt.transaction_quantity < 0 AND mmt.transaction_source_type_id in (6,13,4) AND mp.organization_id = mmt.organization_id AND mmt.inventory_item_id=msib.inventory_item_id AND mmt.organization_id=msib.organization_id AND mmt.organization_id=hou.organization_id AND flv1.lookup_type (+) = 'INV_ECC_TRACK_TRACE_NODETYPE' AND flv1.view_application_id (+) = 700 AND flv1.lookup_code (+) =70 AND flv1.language (+) = hou.language AND hou.language in ('US')AND hou.language=mtt.language AND msib.lot_control_code=1 AND msib.organization_id = nvl('',msib.organization_id) AND mmt.organization_id = nvl('',mmt.organization_id) AND mmt.transaction_date >= TO_DATE('12-NOV-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:10:02','DD-MON-RR HH24:MI:SS')) PIVOT ( MAX ( TRANSACTION_TYPE ) AS TRANSACTION_TYPE , MAX( TRANSACTION_TYPE_NAME) AS TRANSACTION_TYPE_NAME , MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME FOR language IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization Code |
| LOV |