INV Serial number detail

SELECT 
  msn.serial_number                 serial_number, 
&P_ITEM_FLEXDATA                  item_flexdata, 
  msn.inventory_item_id             item_id,  
  msi.description                   item_desc, 
  msn.revision                      item_rev,
  msn.lot_number                    lot_number,
  pov.vendor_name                   vendor_name, 
  msn.vendor_serial_number          vendor_serial_num, 
  msn.vendor_lot_number             vendor_lot, 
  msn.initialization_date           create_date, 
  msn.completion_date               assign_date, 
  msn.ship_date                     ship_date, 
  msn.current_status                curr_stat, 
  mlu.meaning                       curr_stat_name,
  msn.current_subinventory_code     curr_subinv, 
  msn.current_locator_id            curr_loc_id, 
 &P_LOC_FLEXDATA                   loc_flexdata, 
  msn.descriptive_text              comments,
  DECODE
   (msn.last_txn_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,msn.last_txn_source_name,
        msn.last_txn_source_name) txn_source_data,
    mtst.transaction_source_type_name  txn_src_type_name,
    msn.last_txn_source_type_id  txn_source_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_value, 
	DECODE(msn.current_locator_id,null,'',fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_value', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE')) C_loc_value, 
	DECODE(mkts.sales_order_id,null,'',fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_source_value', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE')) C_txn_source_value 
FROM 
   MTL_SERIAL_NUMBERS          msn,
   MTL_SYSTEM_ITEMS            msi,
   MFG_LOOKUPS                 mlu,
   MTL_TXN_SOURCE_TYPES       mtst,
   PO_VENDORS                  pov,
   MTL_ITEM_LOCATIONS          mil,
   PO_HEADERS_ALL                  poh,
   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_PHYSICAL_INVENTORIES    mpi,
   PO_REQUISITION_HEADERS_ALL      porh
WHERE 
       msn.last_txn_source_type_id = mtst.transaction_source_type_id (+)
and mlu.lookup_type = 'SERIAL_NUM_STATUS' 
and mlu.lookup_code = msn.current_status
and msn.current_organization_id = mil.organization_id (+) 
and msn.current_locator_id = mil.inventory_location_id (+) 
and  &P_ITEM_WHERE
and msn.inventory_item_id = msi.inventory_item_id 
and msn.current_organization_id = msi.organization_id 
and msn.original_unit_vendor_id = pov.vendor_id (+)
&C_SERIAL_WHERE
&C_VENDOR_WHERE
&C_VEND_SERIAL_WHERE
&C_UNIT_STATUS_WHERE
&C_SOURCE_TYPE_WHERE
and msn.current_organization_id = :P_ORG 
and 
(  msn.last_txn_source_id    = poh.po_header_id (+) 
   and 
   msn.last_txn_source_id    = mkts.sales_order_id (+)
   and 
   msn.last_txn_source_id    = glcc.code_combination_id (+)
   and 
   msn.last_txn_source_id    = wip.wip_entity_id (+)
   and 
   msn.current_organization_id  = wip.organization_id (+)
   and 
   msn.last_txn_source_id    = mdsp.disposition_id (+)
   and 
   msn.last_txn_source_id    = porh.requisition_header_id (+) 
   and 
   msn.last_txn_source_id    = cch.cycle_count_header_id (+) 
   and 
   msn.current_organization_id  = cch.organization_id (+)
   and 
   msn.last_txn_source_id    = mpi.physical_inventory_id (+)  
   and 
   msn.current_organization_id  = mpi.organization_id (+)
   and 
   msn.last_txn_source_id    = cupd.cost_update_id (+)
   and 
   msn.current_organization_id  = cupd.organization_id (+)
)
Order by 
 msn.serial_number, 
&P_ITEM_ORDER_BY