INV Lot transaction register

âžœ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Lot transaction register
Application: Inventory
Source: Lot transaction register (XML)
Short Name: INVTRLNT_XML
DB package: INV_INVTRLNT_XMLP_PKG
Run INV Lot transaction register and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
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
Unit of Measure 
 
 LOV Oracle
Transaction Dates From 
 
 Date
To 
 
 Date
Audit Detail 
 
 LOV Oracle
Reason Detail 
 
 LOV Oracle
Location Detail 
 
 LOV Oracle
Category Detail 
 
 LOV Oracle
Serial Number Detail 
 
 LOV Oracle
Lot Numbers From 
 
 LOV Oracle
To 2 
 
 LOV Oracle
Items From 
 
 Char
To 3 
 
 Char
Transaction Types From