ECC Inventory Management, Inventory Trace Issues

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