INV Material account distribution detail

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