Reports2017-11-18T12:27:27+00:00

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 Name SQL text Validation
GL Flex Structure
 
Number
Organization
 
Number
GL Batch
 
LOV Oracle
To
 
Accounts From
 
To
 
Date
Transaction Dates From
 
Date
Sort By
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept