GL Account Analysis - Average Balance Audit

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Average Balance Audit Trail Report
Application: General Ledger
Source: Account Analysis - Average Balance Audit (XML)
Short Name: GLXAVADT_XML
DB package: GL_GLXAVADT_XMLP_PKG
Run GL Account Analysis - Average Balance Audit and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Ledger
 
LOV Oracle
Currency Type
 
LOV Oracle
Entered Currency
 
LOV Oracle
Reporting Date
 
Date
Amount Type
 
LOV Oracle
Account From
 
Char
Account To
 
Char
Blitz Report™