Reports 2017-11-18T12:27:27+00:00

INV Transaction register(enginatics)

Description
Categories: Enginatics
Application: Inventory
Source: 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

Parameter Name SQL text Validation
Sort By
 
LOV Oracle
Unit of Measure
 
LOV Oracle
Transaction Dates From
 
Date
To date
 
Date
To item
 
To txn type
 
LOV Oracle
To
 
LOV Oracle