INV Serial number transaction register

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Serial number transaction register (XML)
Short Name: INVTRSNT_XML
DB package: INV_INVTRSNT_XMLP_PKG
SELECT 
decode(:P_BREAK_ID, 
 1, nvl(null, 'None'),
 2,mut.transaction_date,
 nvl(mut.serial_number,'None'))  break_column,
decode(:P_BREAK_ID, 
             1,msi.inventory_item_id ,
              '1')     break_item_id, 
decode(:P_BREAK_ID,
             1,msi.description, 
              '2')      break_item_desc,
 decode(:P_BREAK_ID, 
        1, NULL, 
        &P_ITEM_FLEXDATA)            item_flexdata,
  decode(:P_BREAK_ID,1,'4',
  msi.inventory_item_id)              item_id, 
  decode(:P_BREAK_ID,1,'5',
  msi.description )                   item_desc,        
  decode(:P_BREAK_ID,
         2,'6',
         mut.transaction_date)      txn_date, 
  decode(:P_BREAK_ID,3,'7',nvl(mut.serial_number,'None'))  serial_number,
  mmt.revision                       rev,          
  mtxt.transaction_type_name  txn_type_name,        
  mut.transaction_source_id          txn_source_id, 
  DECODE
   (mut.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wip1.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,
        mut.transaction_source_name)  txn_source_data,
   mut.subinventory_code        subinv,
   DECODE
   (:P_rpt_uom,
    1, ROUND(NVL(abs(mmt.transaction_quantity),0)/
    NVL(mmt.primary_quantity,0),:P_QTY_PRECISION),
    2, 1*sign(mmt.transaction_quantity),
    ROUND(NVL(abs(mmt.transaction_quantity),0)/
    NVL(mmt.primary_quantity,0),:P_QTY_PRECISION))
    rpt_qty,
 DECODE
   (:P_rpt_uom,
  1,ROUND(NVL(mmt.transaction_quantity,0),:P_QTY_PRECISION),
   2,ROUND(NVL(mmt.primary_quantity,0),:P_QTY_PRECISION),       ROUND(NVL(mmt.transaction_quantity,0),:P_QTY_PRECISION))  
  tot_qty,
  DECODE
   (:P_rpt_uom,
     1,mmt.transaction_uom,
     2,msi.primary_uom_code,
       mmt.transaction_uom)       uom,
 ROUND((  round(NVL(mmt.actual_cost,0),:C_ext_precision)  *  sign(mmt.transaction_quantity))
                   ,:C_std_precision)  extd_cost,
               mcat.category_id             catg_id,
  null            catg_flexdata,
  mut.transaction_id                 txn_id, 
  mmt.transaction_set_id             txn_set_id, 
  mmt.created_by                     txn_create_by1, 
  fndu.user_name                     txn_user_name,
  mut.creation_date                  txn_create_dt, 
  mmt.transaction_reference          txn_ref, 
  mtr.reason_name                    reason_name,
  mut.locator_id                     locator_id, 
  null                    loc_flexdata,
  mut.transaction_source_type_id     txn_src_type_id,
   ' '                       lot_number,
  MP.ORGANIZATION_CODE  organization, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_break_item_value', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_break_item_value, 
	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, 
	--&C_txn_src_value C_txn_src_value, 
	decode(mut.transaction_source_type_id,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value2', '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_txn_src_value3', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),DECODE
   (mut.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wip1.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,
        mut.transaction_source_name)) C_txn_src_value,
	INV_INVTRSNT_XMLP_PKG.calc_unit_cost(DECODE ( :P_rpt_uom , 1 , ROUND ( NVL ( mmt.transaction_quantity , 0 ) , :P_QTY_PRECISION ) , 2 , ROUND ( NVL ( mmt.primary_quantity , 0 ) , :P_QTY_PRECISION ) , ROUND ( NVL ( mmt.transaction_quantity , 0 ) , :P_QTY_PRECISION ) ), ROUND ( ( round ( NVL ( mmt.actual_cost , 0 ) , :C_ext_precision ) * sign ( mmt.transaction_quantity ) ) , :C_std_precision ), :C_ext_precision) C_unit_cost, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_value', 'INV', 'MTLL', 101, mil.ORGANIZATION_ID, mil.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_locator_value, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_catg_value
FROM 
   &C_type_break_from 
   MTL_ITEM_LOCATIONS mil , 
   PO_HEADERS_ALL     poh ,
   MTL_SALES_ORDERS mkts , 
   GL_CODE_COMBINATIONS glcc , 
   WIP_ENTITIES wip1 , 
   CST_COST_UPDATES cupd ,
   MTL_CYCLE_COUNT_HEADERS cch ,
   PO_REQUISITION_HEADERS_ALL porh   ,
   MTL_GENERIC_DISPOSITIONS mdsp ,
   MTL_PHYSICAL_INVENTORIES mpi , 
   MTL_UNIT_TRANSACTIONS       mut,
   MTL_CATEGORIES mcat , 
   MTL_ITEM_CATEGORIES micat ,
   MTL_SYSTEM_ITEMS            msi,
   FND_USER fndu , 
   MTL_TRANSACTION_TYPES       mtxt,
   MTL_TRANSACTION_REASONS     mtr,
   MTL_MATERIAL_TRANSACTIONS      mmt,
    MTL_PARAMETERS MP 
WHERE 
&P_CATG_WHERE
and
&P_ITEM_WHERE
and mmt.reason_id = mtr.reason_id (+) 
and mmt.created_by = fndu.user_id (+) 
and mmt.transaction_type_id = mtxt.transaction_type_id (+)
and mut.locator_id = mil.inventory_location_id (+) 
and mut.organization_id = mil.organization_id (+)
and mut.transaction_source_type_id   != 11 
&C_serial_where
&C_reason_where
&C_txn_type_where 
&C_subinv_where
&C_source_type_where
&C_source_where
and mp.organization_id = mmt.organization_id                 
and mut.transaction_source_id = poh.po_header_id (+)
and mut.transaction_source_id = mkts.sales_order_id (+)
and mut.transaction_source_id = glcc.code_combination_id (+)
and mut.transaction_source_id = wip1.wip_entity_id (+)
and mut.organization_id = wip1.organization_id (+)
and mut.transaction_source_id = mdsp.disposition_id (+)
and mut.transaction_source_id = porh.requisition_header_id (+)
and mut.transaction_source_id = cch.cycle_count_header_id (+) 
and mut.organization_id = cch.organization_id (+)
and mut.transaction_source_id = mpi.physical_inventory_id (+) 
and mut.organization_id = mpi.organization_id (+)
and mut.transaction_source_id = cupd.cost_update_id (+)
and mut.organization_id = cupd.organization_id (+)
and mmt.inventory_item_id = msi.inventory_item_id 
and mmt.organization_id = msi.organization_id 
and micat.category_id = mcat.category_id 
and mmt.inventory_item_id = micat.inventory_item_id 
and micat.category_set_id = :P_CATG_SET_ID 
and mmt.organization_id = micat.organization_id 
and mmt.transaction_id = mut.transaction_id
and mmt.transaction_date >= :P_TXN_DATE_LO 
and mmt.transaction_date <= :P_TXN_DATE_HI + 1 - (1/(24*3600))
and mmt.organization_id = NVL(:P_ORG_ID ,MMT.ORGANIZATION_ID) 
UNION    
SELECT 
decode(:P_BREAK_ID, 
 1, nvl(null, 'None'),
 2,mut.transaction_date,
 nvl(mut.serial_number,'None'))  break_column,
decode(:P_BREAK_ID, 
             1,msi.inventory_item_id ,
              '1')     break_item_id, 
decode(:P_BREAK_ID,
             1,msi.description, 
              '2')      break_item_desc,
 decode(:P_BREAK_ID, 
        1, NULL, 
        &P_ITEM_FLEXDATA)            item_flexdata,
  decode(:P_BREAK_ID,1,'4',
  msi.inventory_item_id)              item_id, 
  decode(:P_BREAK_ID,1,'5',
  msi.description )                   item_desc,        
  decode(:P_BREAK_ID,
         2,'6',
         mut.transaction_date)      txn_date, 
  decode(:P_BREAK_ID,3,'7',nvl(mut.serial_number,'None'))  serial_number,
  mmt.revision                       rev,          
  mtxt.transaction_type_name  txn_type_name,        
  mut.transaction_source_id          txn_source_id, 
  DECODE
   (mut.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wip1.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,
        mut.transaction_source_name)  txn_source_data,
   mut.subinventory_code        subinv,
  DECODE
   (:P_rpt_uom,
    1, ROUND(NVL(abs(mmt.transaction_quantity),0)/
    NVL(mmt.primary_quantity,0),:P_QTY_PRECISION),
    2, 1*sign(mmt.transaction_quantity),
    ROUND(NVL(abs(mmt.transaction_quantity),0)/
    NVL(mmt.primary_quantity,0),:P_QTY_PRECISION))
    rpt_qty,
   DECODE
   (:P_rpt_uom,
  1,ROUND(NVL(mmt.transaction_quantity,0),:P_QTY_PRECISION),
   2,ROUND(NVL(mmt.primary_quantity,0),:P_QTY_PRECISION),       ROUND(NVL(mmt.transaction_quantity,0),:P_QTY_PRECISION))  
  tot_qty, 
   DECODE
   (:P_rpt_uom,
     1,mmt.transaction_uom,
     2,msi.primary_uom_code,
       mmt.transaction_uom)       uom,
ROUND((  round(NVL(mmt.actual_cost,0),:C_ext_precision)  *  sign(mmt.transaction_quantity))
                   ,:C_std_precision)  extd_cost,
             mcat.category_id             catg_id,
  null            catg_flexdata,
  mut.transaction_id                 txn_id, 
  mmt.transaction_set_id             txn_set_id, 
  mmt.created_by                     txn_create_by1, 
  fndu.user_name                     txn_user_name,
  mut.creation_date                  txn_create_dt, 
  mmt.transaction_reference          txn_ref, 
  mtr.reason_name                    reason_name,
  mut.locator_id                     locator_id, 
  null                    loc_flexdata,
  mut.transaction_source_type_id     txn_src_type_id,
  mtln.lot_number                    lot_number,
  MP.ORGANIZATION_CODE  organization, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_break_item_value', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_break_item_value, 
	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, 
	--&C_txn_src_value C_txn_src_value, 
	decode(mut.transaction_source_type_id,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value2', '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_txn_src_value3', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),DECODE
   (mut.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wip1.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,
        mut.transaction_source_name)) C_txn_src_value,
	INV_INVTRSNT_XMLP_PKG.calc_unit_cost(DECODE ( :P_rpt_uom , 1 , ROUND ( NVL ( mmt.transaction_quantity , 0 ) , :P_QTY_PRECISION ) , 2 , ROUND ( NVL ( mmt.primary_quantity , 0 ) , :P_QTY_PRECISION ) , ROUND ( NVL ( mmt.transaction_quantity , 0 ) , :P_QTY_PRECISION ) ), ROUND ( ( round ( NVL ( mmt.actual_cost , 0 ) , :C_ext_precision ) * sign ( mmt.transaction_quantity ) ) , :C_std_precision ), :C_ext_precision) C_unit_cost, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_value', 'INV', 'MTLL', 101, mil.ORGANIZATION_ID, mil.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_locator_value, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_catg_value
FROM 
        &C_type_break_from 
        MTL_TRANSACTION_LOT_NUMBERS mtln, 
        MTL_ITEM_LOCATIONS mil , 
        PO_HEADERS_ALL poh , 
        MTL_SALES_ORDERS mkts , 
        GL_CODE_COMBINATIONS glcc , 
        WIP_ENTITIES wip1 , 
        CST_COST_UPDATES cupd , 
        MTL_CYCLE_COUNT_HEADERS cch , 
        PO_REQUISITION_HEADERS_ALL porh   , 
        MTL_GENERIC_DISPOSITIONS mdsp , 
        MTL_PHYSICAL_INVENTORIES mpi , 
        MTL_UNIT_TRANSACTIONS mut , 
        MTL_CATEGORIES mcat , 
        MTL_ITEM_CATEGORIES micat , 
        MTL_SYSTEM_ITEMS msi , 
        FND_USER fndu , 
        MTL_TRANSACTION_TYPES mtxt , 
        MTL_TRANSACTION_REASONS mtr , 
        MTL_MATERIAL_TRANSACTIONS mmt ,
        MTL_PARAMETERS MP 
WHERE 
&P_CATG_WHERE
and
&P_ITEM_WHERE
and mmt.reason_id = mtr.reason_id (+) 
and mmt.created_by = fndu.user_id (+) 
and mmt.transaction_type_id = mtxt.transaction_type_id (+)
and mut.locator_id = mil.inventory_location_id (+) 
and mut.organization_id = mil.organization_id (+)
and mut.transaction_source_type_id   != 11 
&C_serial_where
&C_reason_where
&C_txn_type_where 
&C_subinv_where
&C_source_type_where
&C_source_where
and mp.organization_id = mmt.organization_id                 
and mut.transaction_source_id = poh.po_header_id (+)
and mut.transaction_source_id = mkts.sales_order_id (+)
and mut.transaction_source_id = glcc.code_combination_id (+)
and mut.transaction_source_id = wip1.wip_entity_id (+)
and mut.organization_id = wip1.organization_id (+)
and mut.transaction_source_id = mdsp.disposition_id (+)
and mut.transaction_source_id = porh.requisition_header_id (+)
and mut.transaction_source_id = cch.cycle_count_header_id (+) 
and mut.organization_id = cch.organization_id (+)
and mut.transaction_source_id = mpi.physical_inventory_id (+) 
and mut.organization_id = mpi.organization_id (+)
and mut.transaction_source_id = cupd.cost_update_id (+)
and mut.organization_id = cupd.organization_id (+)
and mmt.inventory_item_id = msi.inventory_item_id 
and mmt.organization_id = msi.organization_id 
and micat.category_id = mcat.category_id 
and mmt.inventory_item_id = micat.inventory_item_id 
and micat.category_set_id = :P_CATG_SET_ID 
and mmt.organization_id = micat.organization_id 
and mtln.serial_transaction_id = mut.transaction_id 
and mmt.transaction_id = mtln.transaction_id 
and mmt.transaction_date >= :P_TXN_DATE_LO 
and mmt.transaction_date <= :P_TXN_DATE_HI + 1 - (1/(24*3600))
and mmt.organization_id =  NVL(:P_ORG_ID, MMT.ORGANIZATION_ID) 
order by 33,1,2,8,20 desc
Parameter Name SQL text Validation
Category Structure
 
Number
Organization or Item
 
Number
GL Structure
 
Number
Quantity Precision
 
LOV Oracle
P_DUMMY
 
Number
P_G_ORG
 
Number
Sort By
 
Number
To
 
Transaction Sources From
 
Source Type
 
LOV Oracle
To
 
Categories From
 
Category Set
 
LOV Oracle
To
 
LOV Oracle
Subinventories From
 
LOV Oracle
To
 
LOV Oracle
Transaction Reasons From
 
LOV Oracle
To
 
LOV Oracle
Transaction Types From
 
LOV Oracle
To
 
Items From
 
To
 
LOV Oracle
Serial Numbers From
 
LOV Oracle
Lot Number Detail
 
LOV Oracle
Category Detail
 
LOV Oracle
Location Detail
 
LOV Oracle
Reason Detail
 
LOV Oracle
Audit Detail
 
LOV Oracle
To
 
Date
Transaction Dates From
 
Date
Organization
 
LOV Oracle
Unit of Measure
 
LOV Oracle