INV Transaction register

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