INV Vendor lot trace

SELECT 
  NVL( MTLN.SUPPLIER_LOT_NUMBER , 'None')    VENDOR_LOT2,   --17153772
  NVL( MTLN.LOT_NUMBER, 'None')              TXN_LOT_NUM2,
  null              ITEM_FLEX2, 
  MSI.INVENTORY_ITEM_ID         ITEM_ID2, 
  MTLN.TRANSACTION_ID           TXN_ID2,
  MMT.REVISION                  ITEM_REV2,
  MSI.DESCRIPTION               ITEM_DESC2,
  MTT.TRANSACTION_TYPE_NAME     TXN_TYPE_NAME2,
  MTLN.TRANSACTION_SOURCE_ID    TXN_SRC_ID2, 
  MTLN.TRANSACTION_SOURCE_TYPE_ID  TXN_SRC_TYPE_ID2,
  DECODE(MTLN.TRANSACTION_SOURCE_TYPE_ID, 
         1,POH.SEGMENT1, 
         2,null, 
         3,null, 
         4,NULL, 
         5,WIP.WIP_ENTITY_NAME,
         6,null, 
         7,PORH.SEGMENT1,
         8,&P_MKTS_FLEXDATA, 
         9,CCH.CYCLE_COUNT_HEADER_NAME,
        10,MPI.PHYSICAL_INVENTORY_NAME,
        11,CUPD.DESCRIPTION,
        12,&P_MKTS_FLEXDATA,
        13,MTLN.TRANSACTION_SOURCE_NAME, 
           MTLN.TRANSACTION_SOURCE_NAME) 
                                 TXN_SOURCE_DATA2,
  MMT.TRANSACTION_DATE           TXN_DATE2, 
  MTLN.ORGANIZATION_ID              ORG_ID2, 
  MMT.TRANSACTION_UOM          TXN_UOM2,
  ROUND(NVL(MTLN.PRIMARY_QUANTITY,0), :P_qty_precision)  TXN_LOT_QTY2,
  DECODE(MSI.TRACKING_QUANTITY_IND, 'PS', MMT.SECONDARY_UOM_CODE, NULL) SEC_TXN_UOM2,
  DECODE(MSI.TRACKING_QUANTITY_IND, 'PS', ROUND(NVL(MTLN.SECONDARY_TRANSACTION_QUANTITY,0),:P_qty_precision), NULL) SEC_TXN_LOT_QTY2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_VALUE2, 
	decode(MTLN.TRANSACTION_SOURCE_TYPE_ID,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_1', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_2', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),6,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_3', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_1', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_1', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),DECODE(MTLN.TRANSACTION_SOURCE_TYPE_ID, 
         1,POH.SEGMENT1, 
         2,null, 
         3,null, 
         5,WIP.WIP_ENTITY_NAME,
         6,null, 
         7,PORH.SEGMENT1,
         8,&P_MKTS_FLEXDATA, 
         9,CCH.CYCLE_COUNT_HEADER_NAME,
        10,MPI.PHYSICAL_INVENTORY_NAME,
        11,CUPD.DESCRIPTION,
        12,&P_MKTS_FLEXDATA,
        13,MTLN.TRANSACTION_SOURCE_NAME, 
           MTLN.TRANSACTION_SOURCE_NAME)) C_SOURCE_VALUE2
	--&C_source_value2 C_SOURCE_VALUE2
FROM 
  MTL_SYSTEM_ITEMS  MSI, 
  MTL_TRANSACTION_LOT_NUMBERS MTLN, 
  MTL_MATERIAL_TRANSACTIONS MMT,
  PO_HEADERS_ALL   POH, 
  PO_REQUISITION_HEADERS_ALL PORH, 
  MTL_SALES_ORDERS MKTS, 
  GL_CODE_COMBINATIONS GLCC, 
  WIP_ENTITIES  WIP, 
  MTL_GENERIC_DISPOSITIONS MDSP, 
  CST_COST_UPDATES CUPD, 
  MTL_CYCLE_COUNT_HEADERS CCH, 
  MTL_TRANSACTION_TYPES   MTT,
  MTL_PHYSICAL_INVENTORIES MPI 
WHERE 
   MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID (+)
  AND 
   &P_ITEM_WHERE 
  AND 
   MTLN.ORGANIZATION_ID = :P_ORG  
  AND 
   MTLN.ORGANIZATION_ID = MMT.ORGANIZATION_ID (+)
  AND 
   MTLN.ORGANIZATION_ID = MSI.ORGANIZATION_ID 
  AND
   MTLN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
  AND 
   MTLN.TRANSACTION_ID = MMT.TRANSACTION_ID  --17153772
  &C_LOT_WHERE
   AND 
    ( :P_VEND_LOT_NUMBER IS  NULL 
       OR 
       MTLN.SUPPLIER_LOT_NUMBER = :P_VEND_LOT_NUMBER )
  AND 
   trunc(MTLN.TRANSACTION_DATE) >= :P_START_DATE ---17304210 
  AND 
   trunc(MTLN.TRANSACTION_DATE) <= :P_END_DATE_1  --17304210
  AND 
   MTLN.TRANSACTION_SOURCE_ID = 
   POH.PO_HEADER_ID (+)
  AND  
   MTLN.TRANSACTION_SOURCE_ID = 
   MKTS.SALES_ORDER_ID (+) 
  AND  
   MTLN.TRANSACTION_SOURCE_ID = 
   GLCC.CODE_COMBINATION_ID (+)
  AND ( 
   MTLN.TRANSACTION_SOURCE_ID =
   WIP.WIP_ENTITY_ID (+) 
  AND 
   MTLN.ORGANIZATION_ID = 
   WIP.ORGANIZATION_ID (+)
   )
  AND
   MTLN.TRANSACTION_SOURCE_ID = 
   MDSP.DISPOSITION_ID (+) 
  AND 
   MTLN.TRANSACTION_SOURCE_ID = 
   PORH.REQUISITION_HEADER_ID (+)
  AND
   (  
   MTLN.TRANSACTION_SOURCE_ID =
   CCH.CYCLE_COUNT_HEADER_ID (+) 
  AND 
   MTLN.ORGANIZATION_ID = 
   CCH.ORGANIZATION_ID (+) 
   ) 
  AND
   ( 
   MTLN.TRANSACTION_SOURCE_ID = 
   MPI.PHYSICAL_INVENTORY_ID (+) 
  AND 
   MTLN.ORGANIZATION_ID = 
   MPI.ORGANIZATION_ID (+)
   )
  AND 
   (
   MTLN.TRANSACTION_SOURCE_ID = 
   CUPD.COST_UPDATE_ID (+)   AND 
   MTLN.ORGANIZATION_ID = 
   CUPD.ORGANIZATION_ID (+) 
   )
ORDER BY  mtln.supplier_lot_number ,  mtln.lot_number ,  
 &P_ITEM_ORDER_BY, mmt.revision, mtln.transaction_date, 
mtln.transaction_id