INV Material account distribution summary

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Material account distribution summary (XML)
Short Name: INVTRACS_XML
DB package: INV_INVTRACS_XMLP_PKG
select   &P_acct_flex                    Account,
         decode(:P_sort_id,1,&P_item_flex,&SORT_COL)         Sort_option,
         &C_batch_sel
         round(sum(nvl(mta.base_transaction_value,0)), :r_extended_precision) Net_activity,
         round(sum(decode( nvl(mta.base_transaction_value,0) /
                               abs(decode(
                                     nvl(mta.base_transaction_value,0),
                                     0, 1, nvl( mta.base_transaction_value,0  )  )),
                  1, nvl(mta.base_transaction_value,0), 0 )  ), :r_extended_precision) sum_debits,
       round(sum(decode( nvl(mta.base_transaction_value,0) /
                               abs(decode(
                                     nvl(mta.base_transaction_value,0),
                                     0, 1, nvl( mta.base_transaction_value,0  )  )),
                  -1, nvl(abs(mta.base_transaction_value),0), 0 )  ), :r_extended_precision)
                                                                                           sum_credits,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field,
/*	INV_INVTRACS_XMLP_PKG.c_sort_padformula(:C_sort_pad, decode ( :P_sort_id , 1 , ( MSI.SEGMENT1 || '\n' || MSI.SEGMENT2 || '\n' || MSI.SEGMENT3 || '\n' || MSI.SEGMENT4 || '\n' || MSI.SEGMENT5 || '\n' || MSI.SEGMENT6 || '\n' || MSI.SEGMENT7 || '\n' || MSI.SEGMENT8 || '\n' || MSI.SEGMENT9 || '\n' || MSI.SEGMENT10 || '\n' || MSI.SEGMENT11 || '\n' || MSI.SEGMENT12 || '\n' || MSI.SEGMENT13 || '\n' || MSI.SEGMENT14 || '\n' || MSI.SEGMENT15 || '\n' || MSI.SEGMENT16 || '\n' || MSI.SEGMENT17 || '\n' || MSI.SEGMENT18 || '\n' || MSI.SEGMENT19 || '\n' || MSI.SEGMENT20 ) , 'X' )) C_sort_pad,*/
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field,
	decode(:P_sort_id,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),&SORT_COL) C_item_field,
	INV_INVTRACS_XMLP_PKG.get_debit(round ( sum ( nvl ( mta.base_transaction_value , 0 ) ) , :r_extended_precision )) C_debits,
	INV_INVTRACS_XMLP_PKG.get_credit(round ( sum ( nvl ( mta.base_transaction_value , 0 ) ) , :r_extended_precision )) C_credits
from
 mtl_material_transactions mmt,
 &C_item_from
  gl_code_combinations      gcc,
  mtl_transaction_accounts  mta
       &C_type_from
       &C_gl_batch_from
where  mta.transaction_id = mmt.transaction_id
&C_type_where
&C_item_where
and    mta.reference_account = gcc.code_combination_id
and    mta.organization_id = :P_org_id
and    mta.accounting_line_type <> 15
and    &P_where_acct
&P_date_range
&C_gl_batch_where
group by &P_acct_flex, decode(:P_sort_id,1,&P_item_flex,&SORT_COL), &C_batch_desc
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),decode(:P_sort_id,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),&SORT_COL)
order by 8, 10, 5, 1 , 2
Parameter NameSQL textValidation
GL Flex Structure
 
Number
Organization
 
Number
GL Batch
 
LOV Oracle
To
 
Accounts From
 
To
 
Date
Transaction Dates From
 
Date
Sort By
 
LOV Oracle