GL Account Analysis - Average Balance Audit
Description
- Categories
- BI Publisher
- Application
- General Ledger
- Source
- Account Analysis - Average Balance Audit (XML)
- Short Name
- GLXAVADT_XML
- DB package
- GL_GLXAVADT_XMLP_PKG
Imported from BI Publisher
Average Balance Audit Trail Report
Average Balance Audit Trail Report
SQL 47 lines
SELECT CC.code_combination_id CCID, null BAL_DATA, null FLEXDATA, dbal.accounting_date ACC_DATE, dbal.end_of_date_balance_num END_OF_DATE_BALANCE, decode(:P_BALANCE_TYPE, 'PATD', dbal.period_aggregate_num, 'QATD', dbal.quarter_aggregate_num, 'YATD', dbal.year_aggregate_num, 0) AGGREGATE, decode(:P_BALANCE_TYPE, 'PATD', dbal.period_average_to_date_num, 'QATD', dbal.quarter_average_to_date_num, 'YATD', dbal.year_average_to_date_num, 0) AVERAGE, fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') BAL_FIELD, fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION') BAL_DESC, fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') BAL_FIELD_W_DEP, fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_secure', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'SECURITY') BAL_SECURE, GL_GLXAVADT_XMLP_PKG.opening_balformula(CC.code_combination_id) opening_bal, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') ACCT_FIELD, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') ACCT_FIELD_W_DEP, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION') ACCT_DESC, fnd_flex_xml_publisher_apis.process_kff_combination_1('flexfield', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') FLEXFIELD, fnd_flex_xml_publisher_apis.process_kff_combination_1('flexdesc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') FLEXDESC, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_secure', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'SECURITY') ACCT_SECURE, GL_GLXAVADT_XMLP_PKG.last_ccidformula(:last_ccid, CC.code_combination_id, :opening_bal) last_ccid, GL_GLXAVADT_XMLP_PKG.daily_activityformula(dbal.end_of_date_balance_num) daily_activity, GL_GLXAVADT_XMLP_PKG.last_eod_p last_eod FROM GL_DAILY_BALANCES_V DBAL, GL_CODE_COMBINATIONS CC WHERE dbal.ledger_id = :P_LEDGER_ID AND dbal.ledger_currency = :LEDGER_CURRENCY AND dbal.period_set_name = :PERIOD_SET_NAME AND dbal.period_type = :PERIOD_TYPE AND dbal.accounting_date <= :P_REPORTING_DATE AND dbal.accounting_date >= :START_DATE AND cc.chart_of_accounts_id = :STRUCT_NUM AND cc.code_combination_id = dbal.code_combination_id AND dbal.currency_code = :REPORTING_CURR AND dbal.currency_type = decode(:P_CURRENCY_TYPE, 'E', 'E', 'U') AND &WHERE_FLEX_RANGE &WHERE_DAS ORDER BY &ORDERBY_BAL, &ORDERBY_ACCT, &ORDERBY_ALL, dbal.accounting_date |
Parameters 7
| Parameter | Type | List of values SQL |
|---|---|---|
| Ledger | LOV Oracle | |
| Currency Type | LOV Oracle | |
| Entered Currency | LOV Oracle | |
| Reporting Date | Date | |
| Amount Type | LOV Oracle | |
| Account From | Char | |
| Account To | Char |