GL Account Analysis - Average Balance Audit

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: Account Analysis - Average Balance Audit (XML)
Short Name: GLXAVADT_XML
DB package: GL_GLXAVADT_XMLP_PKG
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 NameSQL textValidation
Chart of Accounts ID
 
Number
Access Set Id
 
Number
Account To
 
Account From
 
Amount Type
 
LOV Oracle
Reporting Date
 
Date
Entered Currency
 
LOV Oracle
Currency Type
 
LOV Oracle
Ledger
 
LOV Oracle