INV Material account distribution detail

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Material account distribution detail (XML)
Short Name: INVTRDST_XML
DB package: INV_INVTRDST_XMLP_PKG
select  To_Char(mta.transaction_date,'DD-MON-RRRR')                   Txn_date,
        &P_acct_flex                           C_acct_flex0,       
        &P_item_flex                           C_item_flex0,  
        msi.description                        Description,
        &C_type_option                         Type,
        mta.transaction_source_type_id         Type_id, 
 decode(mta.transaction_source_type_id, 
                1,to_char(mmt.transaction_source_id), 
                4,to_char(mmt.transaction_source_id),
                5,to_char(mmt.transaction_source_id), 
                7,to_char(mmt.transaction_source_id), 
                9,to_char(mmt.transaction_source_id), 
                10,to_char(mmt.transaction_source_id), 
                11,to_char(mmt.transaction_source_id), 
                NVL(mmt.transaction_source_name, to_char(mmt.transaction_source_id))
                )  Source,
             decode(mmt.transaction_action_id,
			                 3,
                decode(mmt.organization_id,mta.organization_id,mmt.subinventory_code,
                              mmt.transfer_subinventory),
							  2,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
							 28,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
                             5,
                             mmt.SUBINVENTORY_CODE,
                             mmt.subinventory_code)  SubInv,
        decode(mta.transaction_source_type_id,
	     11,mmt.quantity_adjusted,
	         mta.primary_quantity )          Quantity,
        msi.primary_uom_code                   Primary_uom,
        decode(mmt.transaction_action_id,30,
                      abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0,1,null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec)),
(abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0, 1, null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec))   *
       sign(mta.base_transaction_value)   *
       sign(mta.primary_quantity)  ) )       Cost,
        nvl(mta.base_transaction_value,0)  *
       :P_EXCHANGE_RATE    Value, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad0, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field0, 
	INV_INVTRDST_XMLP_PKG.c_acct_value0_rformula(:C_acct_value0, :C_ext_prec) C_acct_value0_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field0', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field0, 
	INV_INVTRDST_XMLP_PKG.value_rformula(nvl ( mta.base_transaction_value , 0 ) * :P_EXCHANGE_RATE, :C_ext_prec) Value_R 
	--,&C_source_lex C_source_lex
	,INV_INVTRDST_XMLP_PKG.C_sourceFormula( decode(mta.transaction_source_type_id, 
                1,to_char(mmt.transaction_source_id), 
                4,to_char(mmt.transaction_source_id),
                5,to_char(mmt.transaction_source_id), 
                7,to_char(mmt.transaction_source_id), 
                9,to_char(mmt.transaction_source_id), 
                10,to_char(mmt.transaction_source_id), 
                11,to_char(mmt.transaction_source_id), 
                NVL(mmt.transaction_source_name, to_char(mmt.transaction_source_id))
                ), mta.transaction_source_type_id) C_source
from
       mtl_system_items            msi,  
      &C_from_cat   
        &C_from_type
        &C_source_from
        mtl_material_transactions   mmt,
        gl_code_combinations        gcc,
        mtl_transaction_accounts    mta
where   mta.transaction_id = mmt.transaction_id
and     &P_where_acct
and     &P_where_item
and     &P_where_cat &C_where_cat
&C_where_type
and     mta.inventory_item_id = msi.inventory_item_id
and     mta.reference_account = gcc.code_combination_id
and     mta.organization_id = :P_org_id
and     msi.organization_id = :P_org_id
&P_date_range
&C_where_value
&C_where_subinv
&C_where_reason 
&C_source_where_no
&P_source_type
&P_txn_type
&P_gl_batch
and mta.transaction_source_type_id not in (2,3,6,8,12) 
and     mta.accounting_line_type  <> 15
UNION ALL
select To_Char(mta.transaction_date,'DD-MON-RRRR')                  Txn_date,
        &P_acct_flex                           C_acct_flex0,       
        &P_item_flex                           C_item_flex0,  
        msi.description                        Description,
        &C_type_option                         Type,
        mta.transaction_source_type_id         Type_id, 
         fnd_flex_xml_publisher_apis.process_kff_combination_1('p_so_flex', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE')     Source,
              decode(mmt.transaction_action_id,
			                 3,
                decode(mmt.organization_id,mta.organization_id,mmt.subinventory_code,
                              mmt.transfer_subinventory),
							  2,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
							 28,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
                             5,
                             mmt.SUBINVENTORY_CODE,
                             mmt.subinventory_code)   SubInv,
        decode(mta.transaction_source_type_id,
	     11,mmt.quantity_adjusted,
	         mta.primary_quantity )          Quantity,
        msi.primary_uom_code                   Primary_uom,
        decode(mmt.transaction_action_id,30,
                      abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0,1,null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec)),
(abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0, 1, null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec))   *
       sign(mta.base_transaction_value)   *
       sign(mta.primary_quantity)  ) )       Cost,
        nvl(mta.base_transaction_value,0)  *
       :P_EXCHANGE_RATE    Value, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad0, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field0, 
	INV_INVTRDST_XMLP_PKG.c_acct_value0_rformula(:C_acct_value0, :C_ext_prec) C_acct_value0_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field0', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field0, 
	INV_INVTRDST_XMLP_PKG.value_rformula(nvl ( mta.base_transaction_value , 0 ) * :P_EXCHANGE_RATE, :C_ext_prec) Value_R 
	--,&C_source_lex C_source_lex
	,INV_INVTRDST_XMLP_PKG.C_sourceFormula(fnd_flex_xml_publisher_apis.process_kff_combination_1('p_so_flex', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),mta.transaction_source_type_id) C_source
from
       mtl_system_items            msi,  
      &C_from_cat   
        &C_from_type
        &C_source_from
        mtl_material_transactions   mmt,
        gl_code_combinations        gcc,
        MTL_SALES_ORDERS            mkts,
        mtl_transaction_accounts    mta
where   mta.transaction_id = mmt.transaction_id
and     &P_where_acct
and     &P_where_item
and     &P_where_cat &C_where_cat
&C_where_type
and     mta.inventory_item_id = msi.inventory_item_id
and     mta.reference_account = gcc.code_combination_id
and     mta.organization_id = :P_org_id 
and     msi.organization_id = :P_org_id
&P_date_range
&C_where_value
&C_where_subinv
&C_where_reason 
&C_source_where_so
&P_source_type
&P_txn_type
&P_gl_batch
and     mta.transaction_source_id = mkts.sales_order_id  
and     mta.transaction_source_type_id in (2,8,12)
and     mta.accounting_line_type  <> 15
UNION ALL
select  To_Char(mta.transaction_date,'DD-MON-RRRR')                  Txn_date,
        &P_acct_flex                           C_acct_flex0,       
        &P_item_flex                           C_item_flex0,  
        msi.description                        Description,
        &C_type_option                         Type,
        mta.transaction_source_type_id         Type_id, 
        fnd_flex_xml_publisher_apis.process_kff_combination_1('p_alias_flex', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE')     Source,
              decode(mmt.transaction_action_id,
			                 3,
                decode(mmt.organization_id,mta.organization_id,mmt.subinventory_code,
                              mmt.transfer_subinventory),
							  2,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
							 28,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
                             5,
                             mmt.SUBINVENTORY_CODE,
                             mmt.subinventory_code)  SubInv,
        decode(mta.transaction_source_type_id,
	     11,mmt.quantity_adjusted,
	         mta.primary_quantity )          Quantity,
        msi.primary_uom_code                   Primary_uom,
        decode(mmt.transaction_action_id,30,
                      abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0,1,null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec)),
(abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0, 1, null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec))   *
       sign(mta.base_transaction_value)   *
       sign(mta.primary_quantity)  ) )       Cost,
        nvl(mta.base_transaction_value,0)  *
       :P_EXCHANGE_RATE    Value, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad0, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field0, 
	INV_INVTRDST_XMLP_PKG.c_acct_value0_rformula(:C_acct_value0, :C_ext_prec) C_acct_value0_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field0', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field0, 
	INV_INVTRDST_XMLP_PKG.value_rformula(nvl ( mta.base_transaction_value , 0 ) * :P_EXCHANGE_RATE, :C_ext_prec) Value_R 
	--,&C_source_lex C_source_lex
	,INV_INVTRDST_XMLP_PKG.C_sourceFormula(fnd_flex_xml_publisher_apis.process_kff_combination_1('p_alias_flex', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),mta.transaction_source_type_id) C_source
from
       mtl_system_items            msi,  
      &C_from_cat   
        &C_from_type
        &C_source_from
        mtl_material_transactions   mmt,
        gl_code_combinations        gcc,
        MTL_GENERIC_DISPOSITIONS    mdsp,
        mtl_transaction_accounts    mta
where   mta.transaction_id = mmt.transaction_id
and     &P_where_acct
and     &P_where_item
and     &P_where_cat &C_where_cat
&C_where_type
and     mta.inventory_item_id = msi.inventory_item_id
and     mta.reference_account = gcc.code_combination_id
and     mta.organization_id = :P_org_id
and     msi.organization_id = :P_org_id
&P_date_range
&C_where_value
&C_where_subinv
&C_where_reason 
&C_source_where_alias
&P_source_type
&P_txn_type
&P_gl_batch
and     mta.transaction_source_id = mdsp.disposition_id
and     mta.transaction_source_type_id = 6
and     mta.accounting_line_type  <> 15
UNION ALL
select  To_Char(mta.transaction_date,'DD-MON-RRRR')                  Txn_date,
        &P_acct_flex                           C_acct_flex0,       
        &P_item_flex                           C_item_flex0,  
        msi.description                        Description,
        &C_type_option                         Type,
        mta.transaction_source_type_id         Type_id, 
        fnd_flex_xml_publisher_apis.process_kff_combination_1('p_gl_flex', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')     Source,
              decode(mmt.transaction_action_id,
			                 3,
                decode(mmt.organization_id,mta.organization_id,mmt.subinventory_code,
                              mmt.transfer_subinventory),
							  2,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
							 28,
                   decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1,
                             mmt.transfer_subinventory,mmt.subinventory_code),
                             5,
                             mmt.SUBINVENTORY_CODE,
                             mmt.subinventory_code)   SubInv,
        decode(mta.transaction_source_type_id,
	     11,mmt.quantity_adjusted,
	         mta.primary_quantity )          Quantity,
        msi.primary_uom_code                   Primary_uom,
        decode(mmt.transaction_action_id,30,
                      abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0,1,null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec)),
(abs(round(nvl(mta.rate_or_amount,mta.base_transaction_value/decode(mta.primary_quantity,0, 1, null,1,mta.primary_quantity)) * :P_EXCHANGE_RATE ,:C_ext_prec))   *
       sign(mta.base_transaction_value)   *
       sign(mta.primary_quantity)  ) )       Cost,
        nvl(mta.base_transaction_value,0)  *
       :P_EXCHANGE_RATE    Value, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad0, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field0', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field0, 
	INV_INVTRDST_XMLP_PKG.c_acct_value0_rformula(:C_acct_value0, :C_ext_prec) C_acct_value0_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field0', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field0, 
	INV_INVTRDST_XMLP_PKG.value_rformula(nvl ( mta.base_transaction_value , 0 ) * :P_EXCHANGE_RATE, :C_ext_prec) Value_R 
	--,&C_source_lex C_source_lex
	,INV_INVTRDST_XMLP_PKG.C_sourceFormula(fnd_flex_xml_publisher_apis.process_kff_combination_1('p_gl_flex', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),mta.transaction_source_type_id) C_source
from
       mtl_system_items            msi,  
      &C_from_cat   
        &C_from_type
        &C_source_from
        mtl_material_transactions   mmt,
        gl_code_combinations        gcc,
        GL_CODE_COMBINATIONS        glc,
        mtl_transaction_accounts    mta
where   mta.transaction_id = mmt.transaction_id
and     &P_where_acct
and     &P_where_item
and     &P_where_cat &C_where_cat
&C_where_type
and     mta.inventory_item_id = msi.inventory_item_id
and     mta.reference_account = gcc.code_combination_id
and     mta.organization_id = :P_org_id
and     msi.organization_id = :P_org_id
&P_date_range
&C_where_value
&C_where_subinv
&C_where_reason 
&C_source_where_gl
&P_source_type
&P_txn_type
&P_gl_batch
and     mta.transaction_source_id = glc.code_combination_id
and     mta.transaction_source_type_id = 3
and     mta.accounting_line_type  <> 15
order by 2 asc,13 asc, 14 asc, 15 asc,1 asc,3 asc,5 asc,7 asc,8 asc, 9 asc, 11 asc, 12 asc
Parameter NameSQL textValidation
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
Account Flex Structure
 
Number
Dynamic Precision Option
 
LOV Oracle
Organization
 
Number
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Transaction Reason
 
LOV Oracle
Transaction Type
 
LOV Oracle
To
 
Sources From
 
Transaction Source Type
 
LOV Oracle
GL Batch
 
LOV Oracle
To
 
Number
Transaction Values From
 
Number
To
 
LOV Oracle
Subinventories From
 
LOV Oracle
To
 
Items From
 
To
 
Categories From
 
Category Set
 
LOV Oracle
To
 
Accounts From
 
To
 
Date
Transaction Dates From
 
Date
Transaction Type Option
 
LOV Oracle
Sort By
 
LOV Oracle