ECC Inventory Management, Inventory Trace Move
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Inventory Trace Move Data Set
Dataset Key: inv-trace-move
Query Procedure: INV_ECC_TRACK_TRACE_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Description: Inventory Trace Move Data Set
Dataset Key: inv-trace-move
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 /*+ leading(mtln) index ( mtln MTL_TRANSACTION_LOT_NUMBERS_N8 ) use_nl(mmt mtt mp hou flv1) */ to_char (1 || '-' || to_char (mtln.transaction_id)|| '-' || mtln.lot_number) ecc_spec_id , subinventory_code , transfer_subinventory , 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 , (SELECT concatenated_segments FROM mtl_item_locations_kfv WHERE inventory_location_id = mmt.transfer_locator_id AND organization_id = mmt.organization_id AND subinventory_code = nvl(mmt.transfer_subinventory,subinventory_code)) transfer_locator ,nvl(mmt.transfer_locator_id,-999) transfer_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 ,CASE when (('Y'='Y') ) THEN 'Y' ELSE 'N' END onhand_enable 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 mtt.transaction_type_id=mmt.transaction_type_id 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 (+) =65 AND flv1.language (+) = hou.language AND hou.language in ('US')AND hou.language=mtt.language AND mtln.organization_id = nvl('',mtln.organization_id) AND mtln.transaction_date >= TO_DATE('12-NOV-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mtln.transaction_date <= TO_DATE('12-DEC-22 10:10:02','DD-MON-RR HH24:MI:SS') AND ( logical_transaction = 2 OR logical_transaction IS NULL ) AND mmt.transaction_action_id = 2 AND mmt.transaction_quantity < 0 UNION SELECT /*+ index ( mmt MTL_MATERIAL_TRANSACTIONS_N9 ) use_nl(mmt mtt mp hou flv1) */ to_char (0 || '-' || to_char (mmt.transaction_id)) ecc_spec_id , mmt.subinventory_code , mmt.transfer_subinventory , 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 , (SELECT concatenated_segments FROM mtl_item_locations_kfv WHERE inventory_location_id =mmt.transfer_locator_id AND organization_id = mmt.organization_id AND subinventory_code = nvl(mmt.transfer_subinventory,subinventory_code)) transfer_locator ,nvl(mmt.transfer_locator_id,-999) transfer_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 ,CASE when (('Y'='Y') ) THEN 'Y' ELSE 'N' END onhand_enable 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 mtt.transaction_type_id=mmt.transaction_type_id 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 (+) =65 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') AND ( logical_transaction = 2 OR logical_transaction IS NULL ) AND mmt.transaction_action_id = 2 AND mmt.transaction_quantity < 0 ) 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 |