ECC Inventory Management, Inventory Trace Move

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