INV Serial number detail

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Serial number detail (XML)
Short Name: INVIRSNO_XML
DB package: INV_INVIRSNO_XMLP_PKG
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, 
	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, 
	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
Parameter Name SQL text Validation
Sort Type
 
Number
GL Structure
 
Number
Organization
 
Number
To
 
LOV Oracle
Vendor Serial Numbers From
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
To
 
Items From
 
To
 
LOV Oracle
Serial Numbers From
 
LOV Oracle
Source Type
 
LOV Oracle
Serialized Unit Status
 
LOV Oracle