INV Serial number transaction register

Description
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
INV Serial number transaction register and other Oracle EBS reports with Blitz Report™ on our demo environment
Copy
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