INV Transaction register

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Transaction register (XML)
Short Name: INVTRREG_XML
DB package: INV_INVTRREG_XMLP_PKG
SELECT 
decode(:P_BREAK_ID, 
-- 1, nvl(null, ''),
 1, nvl(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'), ''),
 2,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
 3,nvl(&C_type_break_column,''),
 4,nvl(&C_type_break_column,''),
 5,nvl(mtr.reason_name,''),
 6,nvl(mmt.subinventory_code,''), 
-- 7,nvl(null, ''),
7,nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'), ''),
    NULL )                break_column,
decode(:P_BREAK_ID, 
             1,mmt.inventory_item_id ,
              '1')     break_item_id, 
decode(:P_BREAK_ID,
             1,msi.description, 
              '2')      break_item_desc,
decode(:P_BREAK_ID,
             7,mcat.category_id,'3')  break_catg_id,
 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,     
  msi.lot_control_code      item_lot_cntl,   
  decode(:P_BREAK_ID,
         2,'6',
         mmt.transaction_date)      txn_date, 
  mmt.revision                       rev,          
  decode(:P_BREAK_ID, 
         3,'7' , 
          mtxt.transaction_type_name) txn_type_name,        
  mmt.transaction_source_id          txn_source_id, 
  DECODE
   (mmt.transaction_source_type_id, 
      1,poh.segment1, 
      --2,null,
	  2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
      --3,null,
	  3,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_glcc_flexdata', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
      4,mtrh.request_number    ,                
      5,wipe.wip_entity_name, 
      --6,null, 
	  6,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_mdsp_flexdata', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),
      7,porh.segment1,
      --8,&P_MKTS_FLEXDATA,
	  8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
      9,cch.cycle_count_header_name,
     10,mpi.physical_inventory_name,
     11,cupd.description,
     --12,&P_MKTS_FLEXDATA,
	   12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
        mmt.transaction_source_name)  txn_source_data,
  decode(:P_BREAK_ID,6,'8',
         mmt.subinventory_code)          subinv,
  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)) rpt_qty,
  DECODE 
   (:P_rpt_uom,
       1,NVL(mmt.transaction_quantity,0),
       2,NVL(mmt.primary_quantity,0),
         NVL(mmt.transaction_quantity,0))     unrounded_qty,
        NVL(mmt.actual_cost,0) * 
                   NVL(mmt.primary_quantity,0)   unrounded_tot_cost,
 decode(:P_rpt_uom, 
               1,mmt.transaction_uom, 
               2,msi.primary_uom_code )  uom,
 ROUND((NVL(mmt.actual_cost,0) * 
                NVL(mmt.primary_quantity,0)),:C_std_precision) extd_cost,
  decode(:P_BREAK_ID,7,NULL,
               mcat.category_id )             catg_id,
  decode(:P_BREAK_ID,7,NULL,
         &P_CATG_FLEXDATA )          catg_flexdata,
  mmt.transaction_id                 txn_id, 
  mmt.transaction_set_id             txn_set_id, 
  mmt.created_by                     txn_create_by1, 
  fndu.user_name                     txn_user_name,
  mmt.creation_date                  txn_create_dt, 
  mmt.transaction_reference          txn_ref,
  decode(:P_BREAK_ID,5,NULL, 
          mtr.reason_name)           reason_name,
  mmt.locator_id                     locator_id, 
  --null                    loc_flexdata,
  decode(mil.inventory_location_id,null,null,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'))                    loc_flexdata,
  mmt.transaction_source_type_id     txn_src_type_id,
  mut.serial_number                  serial_number,
   msi.lot_control_code             item_lot_control,
decode(:P_BREAK_ID, 
 2, nvl(&P_ITEM_FLEXDATA, ' '),
 1,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
 4,nvl(
DECODE
   (mmt.transaction_source_type_id, 
      1,poh.segment1, 
      --2,&P_MKTS_FLEXDATA,
	        2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
      3,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_glcc_flexdata', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
      5,wipe.wip_entity_name, 
      6,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_mdsp_flexdata', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'), 
      7,porh.segment1,
      --8,&P_MKTS_FLEXDATA,
	  8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
      9,cch.cycle_count_header_name,
     10,mpi.physical_inventory_name,
     11,cupd.description,
     --12,&P_MKTS_FLEXDATA,
	 12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
        mmt.transaction_source_name),
' '),
 3,nvl(
DECODE
   (mmt.transaction_source_type_id, 
      1,poh.segment1, 
      --2,&P_MKTS_FLEXDATA,
	  2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
      3,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_glcc_flexdata', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
      5,wipe.wip_entity_name, 
      6,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_mdsp_flexdata', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'), 
      7,porh.segment1,
      --8,&P_MKTS_FLEXDATA,
	  8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
      9,cch.cycle_count_header_name,
     10,mpi.physical_inventory_name,
     11,cupd.description,
     --12,&P_MKTS_FLEXDATA,
	 12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
        mmt.transaction_source_name)
,' '),
 5,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
 6,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
 7,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
    NULL )  
 Break2,
decode(:P_BREAK_ID, 
 2, nvl(mmt.revision, ' '),
 1,nvl(mmt.revision, ' '),
 4,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
 3,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
 5,nvl(&P_ITEM_FLEXDATA, ' '),
 6,nvl(&P_ITEM_FLEXDATA, ' '),
7,nvl(&P_ITEM_FLEXDATA, ' '),
   NULL)    Break3,
decode(:P_BREAK_ID, 
 2, ' ',
 1, ' ',
 3,nvl(&P_ITEM_FLEXDATA, ' '),
 4,nvl(&P_ITEM_FLEXDATA, ' '),
 5, ' ',
 6, ' ',
 7, ' ',
 NULL)    Break4,
DECODE(msi.tracking_quantity_ind,'PS',ROUND(NVL(mmt.secondary_transaction_quantity,0),:P_QTY_PRECISION)) sec_qty,   
DECODE(msi.tracking_quantity_ind,'PS',mmt.secondary_uom_code) sec_uom,  
INV_INVTRREG_XMLP_PKG.c_break_date_valueformula(
decode(:P_BREAK_ID, 
 1, nvl(null, ''),
 2,nvl(to_number(to_char(mmt.transaction_date,'J')),0),
 3,nvl(&C_type_break_column,''),
 4,nvl(&C_type_break_column,''),
 5,nvl(mtr.reason_name,''),
 6,nvl(mmt.subinventory_code,''), 
 7,nvl(null, ''),
    NULL ) )C_break_date_value,
	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_break_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_break_catg_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, 
	Decode(1,Decode(mmt.transaction_source_type_id,2,1,8,1,12,1,16,1,2),fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),null) C_txn_src_value, 
	INV_INVTRREG_XMLP_PKG.calc_unit_cost(DECODE ( :P_rpt_uom , 1 , NVL ( mmt.transaction_quantity , 0 ) , 2 , NVL ( mmt.primary_quantity , 0 ) , NVL ( mmt.transaction_quantity , 0 ) ), NVL ( mmt.actual_cost , 0 ) * NVL ( mmt.primary_quantity , 0 ), :C_ext_precision) C_unit_cost, 
	decode(mil.inventory_location_id,null,null,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 
   MTL_SYSTEM_ITEMS_VL	msi,
   MTL_TRANSACTION_TYPES	mtxt,
   MTL_TRANSACTION_REASONS   mtr,
   MTL_ITEM_CATEGORIES	micat,
   MTL_ITEM_LOCATIONS	mil,
   MTL_CATEGORIES		mcat,
   MTL_MATERIAL_TRANSACTIONS	mmt,
   FND_USER                    fndu,
   PO_HEADERS_ALL                  poh,
   MTL_SALES_ORDERS            mkts,
   GL_CODE_COMBINATIONS        glc,
   WIP_ENTITIES                wipe, 
   MTL_GENERIC_DISPOSITIONS    mdsp, 
   CST_COST_UPDATES            cupd,
   MTL_CYCLE_COUNT_HEADERS     cch, 
   MTL_PHYSICAL_INVENTORIES    mpi,
   PO_REQUISITION_HEADERS_ALL      porh,
   MTL_UNIT_TRANSACTIONS mut,
  MTL_TXN_REQUEST_HEADERS mtrh
   &C_type_break_from
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 mmt.locator_id = mil.inventory_location_id (+) 
and mmt.organization_id = mil.organization_id(+) 
and mmt.transaction_source_type_id   != 11 
and (mmt.transaction_source_type_id   != 13 or mmt.transaction_type_id != 80)
and mmt.transaction_id = mut.transaction_id (+)
&C_reason_where
&C_txn_type_where 
&C_subinv_where
&C_type_break_where
&C_source_type_where
and (
  (:P_TXN_SOURCE_TYPE_ID=2 and &P_source_where1)
  or (:P_TXN_SOURCE_TYPE_ID=3 and &P_source_where3)
  or (:P_TXN_SOURCE_TYPE_ID=6 and &P_source_where2)
  or (:P_TXN_SOURCE_TYPE_ID=8 and &P_source_where1)
  or (:P_TXN_SOURCE_TYPE_ID=12 and &P_source_where1)
  or (:P_TXN_SOURCE_TYPE_ID not in (2,3,6,8,12) )
  or (:P_TXN_SOURCE_TYPE_ID is null)
   )
&C_source_where
and mmt.transaction_source_id = poh.po_header_id (+)
and mmt.transaction_source_id = mkts.sales_order_id (+)
and mmt.transaction_source_id = glc.code_combination_id (+)
and mmt.transaction_source_id = wipe.wip_entity_id (+)
and mmt.organization_id = wipe.organization_id (+)
and mmt.transaction_source_id = mdsp.disposition_id (+)
and mmt.transaction_source_id = porh.requisition_header_id (+)
and mmt.transaction_source_id = cch.cycle_count_header_id (+) 
and mmt.transaction_source_id = mtrh.header_id(+)  
and mmt.organization_id = cch.organization_id (+)
and mmt.transaction_source_id = mpi.physical_inventory_id (+) 
and mmt.organization_id = mpi.organization_id (+)
and mmt.transaction_source_id = cupd.cost_update_id (+)
and mmt.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 (trunc(mmt.transaction_date)
       --between (:P_TXN_DATE_LO) and  (:P_TXN_DATE_HI) )
	   between (:LP_TXN_DATE_LO) and  (:LP_TXN_DATE_HI) )
and  mmt.organization_id = :P_ORG 
and NVL(logical_transaction,2) = 2  
&C_order_by
Parameter Name SQL text Validation
Category Structure
 
Number
GL Structure
 
Number
Quantity Precision
 
LOV Oracle
Organization
 
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
 
Lot Number Detail
 
LOV Oracle
Serial 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
Unit of Measure
 
LOV Oracle
Sort By
 
LOV Oracle