INV Serial number transaction register

➜ Back to Library
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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: