INV Lot transaction register (1)

Description
Categories: BI Publisher
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