INV Serial number transaction register

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Serial number transaction register
Application: Inventory
Source: Serial number transaction register (XML)
Short Name: INVTRSNT_XML
DB package: INV_INVTRSNT_XMLP_PKG
Run INV Serial number transaction register and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Unit of Measure
 
LOV Oracle
Organization
 
LOV Oracle
Transaction Dates From
 
Date
To
 
Date
Audit Detail
 
LOV Oracle
Reason Detail
 
LOV Oracle
Location Detail
 
LOV Oracle
Category Detail
 
LOV Oracle
Lot Number Detail
 
LOV Oracle
Serial Numbers From
 
LOV Oracle
To 2
 
LOV Oracle
Items From
 
Char
To 3
 
Char
Transaction Types From
 
LOV Oracle
To 4
 
LOV Oracle
Transaction Reasons From
 
LOV Oracle
To 5
 
LOV Oracle
Subinventories From
 
LOV Oracle
To 6
 
LOV Oracle
Category Set
 
LOV Oracle
Categories From
 
Char
To 7
 
Char
Source Type
 
LOV Oracle
Transaction Sources From
 
Char
To 8
 
Char
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: