INV Lot transaction register

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Lot transaction register (XML)
Short Name: INVTRLNT_XML
DB package: INV_INVTRLNT_XMLP_PKG
SELECT 
  null              break_column ,
  msi.inventory_item_id         break_item_id, 
  msi.description               break_item_desc, 
  mtln.transaction_date         txn_date, 
  mtln.lot_number               lot_number ,
  mmt.vendor_lot_number         vend_lot,
  mmt.revision                  rev,          
  mtxt.transaction_type_name    txn_type_name,        
  mtln.transaction_source_id    txn_source_id, 
  DECODE
   (mtln.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wipe.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,
        mtln.transaction_source_name)  txn_source_data,
  mmt.subinventory_code               subinv,
  sum(DECODE 
   (:P_rpt_uom,
       1,ROUND(NVL(mtln.transaction_quantity,0),:P_QTY_PRECISION),
       2,ROUND(NVL(mtln.primary_quantity,0),:P_QTY_PRECISION),
         ROUND(NVL(mtln.transaction_quantity,0),:P_QTY_PRECISION))) rpt_qty,
  DECODE
   (:P_rpt_uom,
     1,mmt.transaction_uom,
     2,msi.primary_uom_code,
       mmt.transaction_uom)       uom,
  sum(ROUND((round(NVL(mmt.actual_cost,0),:C_ext_precision)  * 
                   round(NVL(mtln.primary_quantity,0),:P_QTY_PRECISION)),:C_std_precision))  extd_cost,
  mcat.category_id               catg_id,
  null                catg_flexdata,
  mtln.transaction_id             txn_id, 
  mmt.transaction_set_id          txn_set_id, 
  mtln.created_by                 txn_create_by1, 
  fndu.user_name                  txn_user_name,
  trunc(mtln.creation_date)       txn_create_dt, 
  mmt.transaction_reference       txn_ref, 
  mtr.reason_name                 reason_name,
  mmt.locator_id                  locator_id, 
  null                 loc_flexdata,
  mtln.transaction_source_type_id txn_src_type_id,
  SUM(ROUND(NVL(mtln.secondary_transaction_quantity,0), :P_QTY_PRECISION))  sec_qty,
  msi.secondary_uom_code ,
  mtln.grade_code, 
	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, 
	--&C_txn_src_value C_txn_src_value,
	decode(mtln.transaction_source_type_id,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_2', '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_value_3', '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_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),DECODE
   (mtln.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wipe.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,
        mtln.transaction_source_name)) C_txn_src_value,
	INV_INVTRLNT_XMLP_PKG.c_secondary_uomformula(msi.inventory_item_id, SUM ( ROUND ( NVL ( mtln.secondary_transaction_quantity , 0 ) , :P_QTY_PRECISION ) ), msi.secondary_uom_code) C_secondary_uom, 
	INV_INVTRLNT_XMLP_PKG.calc_unit_cost(sum ( DECODE ( :P_rpt_uom , 1 , ROUND ( NVL ( mtln.transaction_quantity , 0 ) , :P_QTY_PRECISION ) , 2 , ROUND ( NVL ( mtln.primary_quantity , 0 ) , :P_QTY_PRECISION ) , ROUND ( NVL ( mtln.transaction_quantity , 0 ) , :P_QTY_PRECISION ) ) ), sum ( ROUND ( ( round ( NVL ( mmt.actual_cost , 0 ) , :C_ext_precision ) * round ( NVL ( mtln.primary_quantity , 0 ) , :P_QTY_PRECISION ) ) , :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,
	INV_INVTRLNT_XMLP_PKG.CP_sec_qty_p CP_sec_qty
FROM 
   MTL_TRANSACTION_LOT_NUMBERS mtln,
   MTL_SYSTEM_ITEMS            msi,
   MTL_MATERIAL_TRANSACTIONS   mmt,
   MTL_TRANSACTION_TYPES       mtxt,
   MTL_TRANSACTION_REASONS     mtr,
   MTL_ITEM_CATEGORIES         micat,
   MTL_ITEM_LOCATIONS          mil,
   MTL_CATEGORIES              mcat,
   FND_USER                    fndu,
   PO_HEADERS_ALL              poh,
   MTL_SALES_ORDERS            mkts,
   GL_CODE_COMBINATIONS        glcc,
   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
WHERE 
   &P_CATG_WHERE
   AND &P_ITEM_WHERE
   and mmt.reason_id = mtr.reason_id (+) 
   and mtln.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 mtln.transaction_source_type_id != 11 
   &C_lot_where
   &C_txn_type_where
   &C_subinv_where
   &C_reason_where
   &C_txn_source_type_where
   &C_source_where
   and mtln.transaction_source_id = poh.po_header_id (+)
   and mtln.transaction_source_id = mkts.sales_order_id (+)
   and mtln.transaction_source_id = glcc.code_combination_id (+)
   and mtln.transaction_source_id = wipe.wip_entity_id (+)
   and mtln.organization_id = wipe.organization_id (+)
   and mtln.transaction_source_id = mdsp.disposition_id (+)
   and mtln.transaction_source_id = porh.requisition_header_id (+)
   and mtln.transaction_source_id = cch.cycle_count_header_id (+) 
   and mtln.organization_id = cch.organization_id (+)
   and mtln.transaction_source_id = mpi.physical_inventory_id (+) 
   and mtln.organization_id = mpi.organization_id (+)
   and mtln.transaction_source_id = cupd.cost_update_id (+)
   and mtln.organization_id = cupd.organization_id (+)
   and mtln.inventory_item_id = msi.inventory_item_id 
   and mtln.organization_id = msi.organization_id 
   and micat.category_id = mcat.category_id 
   and mtln.inventory_item_id = micat.inventory_item_id 
   and micat.category_set_id = :P_CATG_SET_ID 
   and mtln.organization_id = micat.organization_id 
   and mtln.transaction_id = mmt.transaction_id 
   and (mtln.transaction_date) >= :P_TXN_DATE_LO 
   and (mtln.transaction_date) <= :P_TXN_DATE_HI +1-1/(24*3600) 
   and mtln.organization_id = :P_ORG 
GROUP BY
  msi.inventory_item_id,
  msi.description ,
  mtln.transaction_date ,
  mtln.lot_number  ,
  mmt.vendor_lot_number  ,
  mmt.revision  ,
  mtxt.transaction_type_name ,
  mtln.transaction_source_id ,
  DECODE
   (mtln.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wipe.wip_entity_name, 
      6,null, 
      7,porh.segment1,
      8,(mkts.segment1||mkts.segment2||mkts.segment3||mkts.segment4||mkts.segment5||mkts.segment6||mkts.segment7||mkts.segment8||mkts.segment9||mkts.segment10||mkts.segment11||mkts.segment12||mkts.segment13||mkts.segment14||mkts.segment15||mkts.segment16||mkts.segment17||mkts.segment18||mkts.segment19||mkts.segment20),
      9,cch.cycle_count_header_name,
     10,mpi.physical_inventory_name,
     11,cupd.description,
     12,(mkts.segment1||mkts.segment2||mkts.segment3||mkts.segment4||mkts.segment5||mkts.segment6||mkts.segment7||mkts.segment8||mkts.segment9||mkts.segment10||mkts.segment11||mkts.segment12||mkts.segment13||mkts.segment14||mkts.segment15||mkts.segment16||mkts.segment17||mkts.segment18||mkts.segment19||mkts.segment20),
        mtln.transaction_source_name)  ,
  mmt.subinventory_code  ,
  DECODE
   (:P_rpt_uom,
     1,mmt.transaction_uom,
     2,msi.primary_uom_code,
       mmt.transaction_uom)      ,
  mcat.category_id             ,
  null               ,
  mtln.transaction_id           ,
  mmt.transaction_set_id         ,
  mtln.created_by                ,
  fndu.user_name                  ,
  trunc(mtln.creation_date)      ,
  mmt.transaction_reference     ,
  mtr.reason_name               ,
  mmt.locator_id                 ,
  mtln.transaction_source_type_id,
  msi.secondary_uom_code ,
  mtln.grade_code, 
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'),
decode(mtln.transaction_source_type_id,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_2', '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_value_3', '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_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),DECODE
   (mtln.transaction_source_type_id, 
      1,poh.segment1, 
      2,null,
      3,null,
      5,wipe.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,
        mtln.transaction_source_name)),
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'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
order by 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'),msi.inventory_item_id,msi.secondary_uom_code,mtln.grade_code,mtln.transaction_id
/*ORDER BY 
   mtln.lot_number,  mtln.transaction_date,  mtln.transaction_id*/
Parameter Name SQL text Validation
Category Structure
 
Number
GL Structure
 
Number
Quantity Precision
 
LOV Oracle
Organization
 
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
Lot Numbers From
 
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
Ask a question