ECC Inventory Management, Inventory Trace Issues

➜ Back to Library
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
Run ECC Inventory Management, Inventory Trace Issues and other Oracle EBS reports with Blitz Report™ on our demo environment
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 
x.organization_code=:organization_code
 LOV
Download
Related Blitz Reports
CAC Purchase Price Variance ECC Inventory Management, Inventory Trace Sales Orders ECC Inventory Management, Inventory Trace Move ECC Project Manufacturing Genealogy & Trace, Purchase Orders ECC Genealogy and Trace, Purchase Orders ECC Process Genealogy and Trace, Purchase Orders ECC Discrete Completions, Assembly Completion Transactions CAC Calculate Average Item Costs GL Account Analysis (Distributions)
Blitz Report™

Blitz Report™ provides multiple benefits: