VIEW: APPS.GLBV_GL_AVERAGE_BALANCES

Source

View Text - Preformatted

SELECT DB.ledger_id, LGR.name, DB.code_combination_id, '_KF:SQLGL:GL#:GCC', '_LA:DECODE(DB.template_id,NULL,''DTL'',''SUM''):GL_LOOKUPS:SUMMARY_FLAG:MEANING', DB.currency_code, LK.meaning, DB.converted_from_currency, DPM.period_name, DPM.accounting_date, round ( decode(trunc(DPM.accounting_date) - trunc(DB.period_start_date)+1, 1, decode(DB.currency_type, 'O', DB.end_of_day1, 'T', DB.end_of_day1, DB.period_aggregate1), 2, decode(DB.currency_type, 'O', DB.end_of_day2, 'T', DB.end_of_day2, (DB.period_aggregate2 - DB.period_aggregate1)), 3, decode(DB.currency_type, 'O', DB.end_of_day3, 'T', DB.end_of_day3, (DB.period_aggregate3 - DB.period_aggregate2)), 4, decode(DB.currency_type, 'O', DB.end_of_day4, 'T', DB.end_of_day4, (DB.period_aggregate4 - DB.period_aggregate3)), 5, decode(DB.currency_type, 'O', DB.end_of_day5, 'T', DB.end_of_day5, (DB.period_aggregate5 - DB.period_aggregate4)), 6, decode(DB.currency_type, 'O', DB.end_of_day6, 'T', DB.end_of_day6, (DB.period_aggregate6 - DB.period_aggregate5)), 7, decode(DB.currency_type, 'O', DB.end_of_day7, 'T', DB.end_of_day7, (DB.period_aggregate7 - DB.period_aggregate6)), 8, decode(DB.currency_type, 'O', DB.end_of_day8, 'T', DB.end_of_day8, (DB.period_aggregate8 - DB.period_aggregate7)), 9, decode(DB.currency_type, 'O', DB.end_of_day9, 'T', DB.end_of_day9, (DB.period_aggregate9 - DB.period_aggregate8)), 10, decode(DB.currency_type, 'O', DB.end_of_day10, 'T', DB.end_of_day10, (DB.period_aggregate10 - DB.period_aggregate9)), 11, decode(DB.currency_type, 'O', DB.end_of_day11, 'T', DB.end_of_day11, (DB.period_aggregate11 - DB.period_aggregate10)), 12, decode(DB.currency_type, 'O', DB.end_of_day12, 'T', DB.end_of_day12, (DB.period_aggregate12 - DB.period_aggregate11)), 13, decode(DB.currency_type, 'O', DB.end_of_day13, 'T', DB.end_of_day13, (DB.period_aggregate13 - DB.period_aggregate12)), 14, decode(DB.currency_type, 'O', DB.end_of_day14, 'T', DB.end_of_day14, (DB.period_aggregate14 - DB.period_aggregate13)), 15, decode(DB.currency_type, 'O', DB.end_of_day15, 'T', DB.end_of_day15, (DB.period_aggregate15 - DB.period_aggregate14)), 16, decode(DB.currency_type, 'O', DB.end_of_day16, 'T', DB.end_of_day16, (DB.period_aggregate16 - DB.period_aggregate15)), 17, decode(DB.currency_type, 'O', DB.end_of_day17, 'T', DB.end_of_day17, (DB.period_aggregate17 - DB.period_aggregate16)), 18, decode(DB.currency_type, 'O', DB.end_of_day18, 'T', DB.end_of_day18, (DB.period_aggregate18 - DB.period_aggregate17)), 19, decode(DB.currency_type, 'O', DB.end_of_day19, 'T', DB.end_of_day19, (DB.period_aggregate19 - DB.period_aggregate18)), 20, decode(DB.currency_type, 'O', DB.end_of_day20, 'T', DB.end_of_day20, (DB.period_aggregate20 - DB.period_aggregate19)), 21, decode(DB.currency_type, 'O', DB.end_of_day21, 'T', DB.end_of_day21, (DB.period_aggregate21 - DB.period_aggregate20)), 22, decode(DB.currency_type, 'O', DB.end_of_day22, 'T', DB.end_of_day22, (DB.period_aggregate22 - DB.period_aggregate21)), 23, decode(DB.currency_type, 'O', DB.end_of_day23, 'T', DB.end_of_day23, (DB.period_aggregate23 - DB.period_aggregate22)), 24, decode(DB.currency_type, 'O', DB.end_of_day24, 'T', DB.end_of_day24, (DB.period_aggregate24 - DB.period_aggregate23)), 25, decode(DB.currency_type, 'O', DB.end_of_day25, 'T', DB.end_of_day25, (DB.period_aggregate25 - DB.period_aggregate24)), 26, decode(DB.currency_type, 'O', DB.end_of_day26, 'T', DB.end_of_day26, (DB.period_aggregate26 - DB.period_aggregate25)), 27, decode(DB.currency_type, 'O', DB.end_of_day27, 'T', DB.end_of_day27, (DB.period_aggregate27 - DB.period_aggregate26)), 28, decode(DB.currency_type, 'O', DB.end_of_day28, 'T', DB.end_of_day28, (DB.period_aggregate28 - DB.period_aggregate27)), 29, decode(DB.currency_type, 'O', DB.end_of_day29, 'T', DB.end_of_day29, (DB.period_aggregate29 - DB.period_aggregate28)), 30, decode(DB.currency_type, 'O', DB.end_of_day30, 'T', DB.end_of_day30, (DB.period_aggregate30 - DB.period_aggregate29)), 31, decode(DB.currency_type, 'O', DB.end_of_day31, 'T', DB.end_of_day31, (DB.period_aggregate31 - DB.period_aggregate30)), 32, decode(DB.currency_type, 'O', DB.end_of_day32, 'T', DB.end_of_day32, (DB.period_aggregate32 - DB.period_aggregate31)), 33, decode(DB.currency_type, 'O', DB.end_of_day33, 'T', DB.end_of_day33, (DB.period_aggregate33 - DB.period_aggregate32)), 34, decode(DB.currency_type, 'O', DB.end_of_day34, 'T', DB.end_of_day34, (DB.period_aggregate34 - DB.period_aggregate33)), 35, decode(DB.currency_type, 'O', DB.end_of_day35, 'T', DB.end_of_day35, (DB.period_aggregate35 - DB.period_aggregate34)),0) / nvl (FC.minimum_accountable_unit, power(10, -FC.precision))) * nvl (FC.minimum_accountable_unit, power(10, -FC.precision)) END_OF_DAY, round(decode(trunc(DPM.accounting_date) - trunc(DB.period_start_date) + 1, 1, DB.period_aggregate1, 2, DB.period_aggregate2, 3, DB.period_aggregate3, 4, DB.period_aggregate4, 5, DB.period_aggregate5, 6, DB.period_aggregate6, 7, DB.period_aggregate7, 8, DB.period_aggregate8, 9, DB.period_aggregate9, 10, DB.period_aggregate10, 11, DB.period_aggregate11, 12, DB.period_aggregate12, 13, DB.period_aggregate13, 14, DB.period_aggregate14, 15, DB.period_aggregate15, 16, DB.period_aggregate16, 17, DB.period_aggregate17, 18, DB.period_aggregate18, 19, DB.period_aggregate19, 20, DB.period_aggregate20, 21, DB.period_aggregate21, 22, DB.period_aggregate22, 23, DB.period_aggregate23, 24, DB.period_aggregate24, 25, DB.period_aggregate25, 26, DB.period_aggregate26, 27, DB.period_aggregate27, 28, DB.period_aggregate28, 29, DB.period_aggregate29, 30, DB.period_aggregate30, 31, DB.period_aggregate31, 32, DB.period_aggregate32, 33, DB.period_aggregate33, 34, DB.period_aggregate34, 35, DB.period_aggregate35, 0) / ( DPM.accounting_date - DB.period_start_date + 1 ) / nvl (FC.minimum_accountable_unit, power (10, -FC.precision))) * nvl (FC.minimum_accountable_unit, power (10, -FC.precision)) PERIOD_AVERAGE_TO_DATE, round(decode(trunc(DPM.accounting_date) - trunc(DB.period_start_date) + 1, 1, nvl(DB.quarter_aggregate1, opening_quarter_aggregate + DB.period_aggregate1), 2, nvl(DB.quarter_aggregate2, opening_quarter_aggregate + DB.period_aggregate2), 3, nvl(DB.quarter_aggregate3, opening_quarter_aggregate + DB.period_aggregate3), 4, nvl(DB.quarter_aggregate4, opening_quarter_aggregate + DB.period_aggregate4), 5, nvl(DB.quarter_aggregate5, opening_quarter_aggregate + DB.period_aggregate5), 6, nvl(DB.quarter_aggregate6, opening_quarter_aggregate + DB.period_aggregate6), 7, nvl(DB.quarter_aggregate7, opening_quarter_aggregate + DB.period_aggregate7), 8, nvl(DB.quarter_aggregate8, opening_quarter_aggregate + DB.period_aggregate8), 9, nvl(DB.quarter_aggregate9, opening_quarter_aggregate + DB.period_aggregate9), 10, nvl(DB.quarter_aggregate10, opening_quarter_aggregate + DB.period_aggregate10), 11, nvl(DB.quarter_aggregate11, opening_quarter_aggregate + DB.period_aggregate11), 12, nvl(DB.quarter_aggregate12, opening_quarter_aggregate + DB.period_aggregate12), 13, nvl(DB.quarter_aggregate13, opening_quarter_aggregate + DB.period_aggregate13), 14, nvl(DB.quarter_aggregate14, opening_quarter_aggregate + DB.period_aggregate14), 15, nvl(DB.quarter_aggregate15, opening_quarter_aggregate + DB.period_aggregate15), 16, nvl(DB.quarter_aggregate16, opening_quarter_aggregate + DB.period_aggregate16), 17, nvl(DB.quarter_aggregate17, opening_quarter_aggregate + DB.period_aggregate17), 18, nvl(DB.quarter_aggregate18, opening_quarter_aggregate + DB.period_aggregate18), 19, nvl(DB.quarter_aggregate19, opening_quarter_aggregate + DB.period_aggregate19), 20, nvl(DB.quarter_aggregate20, opening_quarter_aggregate + DB.period_aggregate20), 21, nvl(DB.quarter_aggregate21, opening_quarter_aggregate + DB.period_aggregate21), 22, nvl(DB.quarter_aggregate22, opening_quarter_aggregate + DB.period_aggregate22), 23, nvl(DB.quarter_aggregate23, opening_quarter_aggregate + DB.period_aggregate23), 24, nvl(DB.quarter_aggregate24, opening_quarter_aggregate + DB.period_aggregate24), 25, nvl(DB.quarter_aggregate25, opening_quarter_aggregate + DB.period_aggregate25), 26, nvl(DB.quarter_aggregate26, opening_quarter_aggregate + DB.period_aggregate26), 27, nvl(DB.quarter_aggregate27, opening_quarter_aggregate + DB.period_aggregate27), 28, nvl(DB.quarter_aggregate28, opening_quarter_aggregate + DB.period_aggregate28), 29, nvl(DB.quarter_aggregate29, opening_quarter_aggregate + DB.period_aggregate29), 30, nvl(DB.quarter_aggregate30, opening_quarter_aggregate + DB.period_aggregate30), 31, nvl(DB.quarter_aggregate31, opening_quarter_aggregate + DB.period_aggregate31), 32, nvl(DB.quarter_aggregate32, opening_quarter_aggregate + DB.period_aggregate32), 33, nvl(DB.quarter_aggregate33, opening_quarter_aggregate + DB.period_aggregate33), 34, nvl(DB.quarter_aggregate34, opening_quarter_aggregate + DB.period_aggregate34), 35, nvl(DB.quarter_aggregate35, opening_quarter_aggregate + DB.period_aggregate35), 0) / ( DPM.accounting_date - DB.quarter_start_date + 1 ) / nvl(FC.minimum_accountable_unit, power(10, -FC.precision))) * nvl(FC.minimum_accountable_unit, power(10, -FC.precision)) QUARTER_AVERAGE_TO_DATE, round(decode(trunc(DPM.accounting_date) - trunc(DB.period_start_date) + 1, 1, nvl(DB.year_aggregate1, opening_year_aggregate + DB.period_aggregate1), 2, nvl(DB.year_aggregate2, opening_year_aggregate + DB.period_aggregate2), 3, nvl(DB.year_aggregate3, opening_year_aggregate + DB.period_aggregate3), 4, nvl(DB.year_aggregate4, opening_year_aggregate + DB.period_aggregate4), 5, nvl(DB.year_aggregate5, opening_year_aggregate + DB.period_aggregate5), 6, nvl(DB.year_aggregate6, opening_year_aggregate + DB.period_aggregate6), 7, nvl(DB.year_aggregate7, opening_year_aggregate + DB.period_aggregate7), 8, nvl(DB.year_aggregate8, opening_year_aggregate + DB.period_aggregate8), 9, nvl(DB.year_aggregate9, opening_year_aggregate + DB.period_aggregate9), 10, nvl(DB.year_aggregate10, opening_year_aggregate + DB.period_aggregate10), 11, nvl(DB.year_aggregate11, opening_year_aggregate + DB.period_aggregate11), 12, nvl(DB.year_aggregate12, opening_year_aggregate + DB.period_aggregate12), 13, nvl(DB.year_aggregate13, opening_year_aggregate + DB.period_aggregate13), 14, nvl(DB.year_aggregate14, opening_year_aggregate + DB.period_aggregate14), 15, nvl(DB.year_aggregate15, opening_year_aggregate + DB.period_aggregate15), 16, nvl(DB.year_aggregate16, opening_year_aggregate + DB.period_aggregate16), 17, nvl(DB.year_aggregate17, opening_year_aggregate + DB.period_aggregate17), 18, nvl(DB.year_aggregate18, opening_year_aggregate + DB.period_aggregate18), 19, nvl(DB.year_aggregate19, opening_year_aggregate + DB.period_aggregate19), 20, nvl(DB.year_aggregate20, opening_year_aggregate + DB.period_aggregate20), 21, nvl(DB.year_aggregate21, opening_year_aggregate + DB.period_aggregate21), 22, nvl(DB.year_aggregate22, opening_year_aggregate + DB.period_aggregate22), 23, nvl(DB.year_aggregate23, opening_year_aggregate + DB.period_aggregate23), 24, nvl(DB.year_aggregate24, opening_year_aggregate + DB.period_aggregate24), 25, nvl(DB.year_aggregate25, opening_year_aggregate + DB.period_aggregate25), 26, nvl(DB.year_aggregate26, opening_year_aggregate + DB.period_aggregate26), 27, nvl(DB.year_aggregate27, opening_year_aggregate + DB.period_aggregate27), 28, nvl(DB.year_aggregate28, opening_year_aggregate + DB.period_aggregate28), 29, nvl(DB.year_aggregate29, opening_year_aggregate + DB.period_aggregate29), 30, nvl(DB.year_aggregate30, opening_year_aggregate + DB.period_aggregate30), 31, nvl(DB.year_aggregate31, opening_year_aggregate + DB.period_aggregate31), 32, nvl(DB.year_aggregate32, opening_year_aggregate + DB.period_aggregate32), 33, nvl(DB.year_aggregate33, opening_year_aggregate + DB.period_aggregate33), 34, nvl(DB.year_aggregate34, opening_year_aggregate + DB.period_aggregate34), 35, nvl(DB.year_aggregate35, opening_year_aggregate + DB.period_aggregate35), 0) / ( DPM.accounting_date - DB.year_start_date + 1 ) / nvl(FC.minimum_accountable_unit, power (10, -FC.precision)) ) * nvl (FC.minimum_accountable_unit, power (10, -FC.precision)) YEAR_AVERAGE_TO_DATE FROM GL_LOOKUPS LK, GL_DAILY_BALANCES DB, GL_DATE_PERIOD_MAP DPM, FND_CURRENCIES FC, GL_LEDGERS LGR, GL_CODE_COMBINATIONS GCC WHERE (DPM.period_name || '' = DB.period_name) AND (DPM.period_set_name || '' = LGR.period_set_name) AND (DPM.period_type || '' = LGR.accounted_period_type) AND (FC.currency_code = DB.currency_code) AND (DB.ledger_id = LGR.ledger_id) AND (DB.code_combination_id = GCC.code_combination_id) AND LK.LOOKUP_TYPE = 'CURRENCY_TYPE' AND LK.LOOKUP_CODE = DECODE( DB.currency_type, 'E', 'FE', 'C', 'FC', 'T', 'FT', 'O', 'FT', 'U', DECODE( DB.currency_code, 'STAT', 'STAT', 'FUNC' ) ) AND '_SEC:GL:DB.LEDGER_ID,DB.code_combination_id' IS NOT NULL WITH READ ONLY

View Text - HTML Formatted

SELECT DB.LEDGER_ID
, LGR.NAME
, DB.CODE_COMBINATION_ID
, '_KF:SQLGL:GL#:GCC'
, '_LA:DECODE(DB.TEMPLATE_ID
, NULL
, ''DTL''
, ''SUM''):GL_LOOKUPS:SUMMARY_FLAG:MEANING'
, DB.CURRENCY_CODE
, LK.MEANING
, DB.CONVERTED_FROM_CURRENCY
, DPM.PERIOD_NAME
, DPM.ACCOUNTING_DATE
, ROUND ( DECODE(TRUNC(DPM.ACCOUNTING_DATE) - TRUNC(DB.PERIOD_START_DATE)+1
, 1
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY1
, 'T'
, DB.END_OF_DAY1
, DB.PERIOD_AGGREGATE1)
, 2
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY2
, 'T'
, DB.END_OF_DAY2
, (DB.PERIOD_AGGREGATE2 - DB.PERIOD_AGGREGATE1))
, 3
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY3
, 'T'
, DB.END_OF_DAY3
, (DB.PERIOD_AGGREGATE3 - DB.PERIOD_AGGREGATE2))
, 4
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY4
, 'T'
, DB.END_OF_DAY4
, (DB.PERIOD_AGGREGATE4 - DB.PERIOD_AGGREGATE3))
, 5
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY5
, 'T'
, DB.END_OF_DAY5
, (DB.PERIOD_AGGREGATE5 - DB.PERIOD_AGGREGATE4))
, 6
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY6
, 'T'
, DB.END_OF_DAY6
, (DB.PERIOD_AGGREGATE6 - DB.PERIOD_AGGREGATE5))
, 7
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY7
, 'T'
, DB.END_OF_DAY7
, (DB.PERIOD_AGGREGATE7 - DB.PERIOD_AGGREGATE6))
, 8
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY8
, 'T'
, DB.END_OF_DAY8
, (DB.PERIOD_AGGREGATE8 - DB.PERIOD_AGGREGATE7))
, 9
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY9
, 'T'
, DB.END_OF_DAY9
, (DB.PERIOD_AGGREGATE9 - DB.PERIOD_AGGREGATE8))
, 10
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY10
, 'T'
, DB.END_OF_DAY10
, (DB.PERIOD_AGGREGATE10 - DB.PERIOD_AGGREGATE9))
, 11
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY11
, 'T'
, DB.END_OF_DAY11
, (DB.PERIOD_AGGREGATE11 - DB.PERIOD_AGGREGATE10))
, 12
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY12
, 'T'
, DB.END_OF_DAY12
, (DB.PERIOD_AGGREGATE12 - DB.PERIOD_AGGREGATE11))
, 13
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY13
, 'T'
, DB.END_OF_DAY13
, (DB.PERIOD_AGGREGATE13 - DB.PERIOD_AGGREGATE12))
, 14
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY14
, 'T'
, DB.END_OF_DAY14
, (DB.PERIOD_AGGREGATE14 - DB.PERIOD_AGGREGATE13))
, 15
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY15
, 'T'
, DB.END_OF_DAY15
, (DB.PERIOD_AGGREGATE15 - DB.PERIOD_AGGREGATE14))
, 16
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY16
, 'T'
, DB.END_OF_DAY16
, (DB.PERIOD_AGGREGATE16 - DB.PERIOD_AGGREGATE15))
, 17
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY17
, 'T'
, DB.END_OF_DAY17
, (DB.PERIOD_AGGREGATE17 - DB.PERIOD_AGGREGATE16))
, 18
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY18
, 'T'
, DB.END_OF_DAY18
, (DB.PERIOD_AGGREGATE18 - DB.PERIOD_AGGREGATE17))
, 19
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY19
, 'T'
, DB.END_OF_DAY19
, (DB.PERIOD_AGGREGATE19 - DB.PERIOD_AGGREGATE18))
, 20
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY20
, 'T'
, DB.END_OF_DAY20
, (DB.PERIOD_AGGREGATE20 - DB.PERIOD_AGGREGATE19))
, 21
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY21
, 'T'
, DB.END_OF_DAY21
, (DB.PERIOD_AGGREGATE21 - DB.PERIOD_AGGREGATE20))
, 22
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY22
, 'T'
, DB.END_OF_DAY22
, (DB.PERIOD_AGGREGATE22 - DB.PERIOD_AGGREGATE21))
, 23
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY23
, 'T'
, DB.END_OF_DAY23
, (DB.PERIOD_AGGREGATE23 - DB.PERIOD_AGGREGATE22))
, 24
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY24
, 'T'
, DB.END_OF_DAY24
, (DB.PERIOD_AGGREGATE24 - DB.PERIOD_AGGREGATE23))
, 25
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY25
, 'T'
, DB.END_OF_DAY25
, (DB.PERIOD_AGGREGATE25 - DB.PERIOD_AGGREGATE24))
, 26
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY26
, 'T'
, DB.END_OF_DAY26
, (DB.PERIOD_AGGREGATE26 - DB.PERIOD_AGGREGATE25))
, 27
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY27
, 'T'
, DB.END_OF_DAY27
, (DB.PERIOD_AGGREGATE27 - DB.PERIOD_AGGREGATE26))
, 28
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY28
, 'T'
, DB.END_OF_DAY28
, (DB.PERIOD_AGGREGATE28 - DB.PERIOD_AGGREGATE27))
, 29
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY29
, 'T'
, DB.END_OF_DAY29
, (DB.PERIOD_AGGREGATE29 - DB.PERIOD_AGGREGATE28))
, 30
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY30
, 'T'
, DB.END_OF_DAY30
, (DB.PERIOD_AGGREGATE30 - DB.PERIOD_AGGREGATE29))
, 31
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY31
, 'T'
, DB.END_OF_DAY31
, (DB.PERIOD_AGGREGATE31 - DB.PERIOD_AGGREGATE30))
, 32
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY32
, 'T'
, DB.END_OF_DAY32
, (DB.PERIOD_AGGREGATE32 - DB.PERIOD_AGGREGATE31))
, 33
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY33
, 'T'
, DB.END_OF_DAY33
, (DB.PERIOD_AGGREGATE33 - DB.PERIOD_AGGREGATE32))
, 34
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY34
, 'T'
, DB.END_OF_DAY34
, (DB.PERIOD_AGGREGATE34 - DB.PERIOD_AGGREGATE33))
, 35
, DECODE(DB.CURRENCY_TYPE
, 'O'
, DB.END_OF_DAY35
, 'T'
, DB.END_OF_DAY35
, (DB.PERIOD_AGGREGATE35 - DB.PERIOD_AGGREGATE34))
, 0) / NVL (FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, -FC.PRECISION))) * NVL (FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, -FC.PRECISION)) END_OF_DAY
, ROUND(DECODE(TRUNC(DPM.ACCOUNTING_DATE) - TRUNC(DB.PERIOD_START_DATE) + 1
, 1
, DB.PERIOD_AGGREGATE1
, 2
, DB.PERIOD_AGGREGATE2
, 3
, DB.PERIOD_AGGREGATE3
, 4
, DB.PERIOD_AGGREGATE4
, 5
, DB.PERIOD_AGGREGATE5
, 6
, DB.PERIOD_AGGREGATE6
, 7
, DB.PERIOD_AGGREGATE7
, 8
, DB.PERIOD_AGGREGATE8
, 9
, DB.PERIOD_AGGREGATE9
, 10
, DB.PERIOD_AGGREGATE10
, 11
, DB.PERIOD_AGGREGATE11
, 12
, DB.PERIOD_AGGREGATE12
, 13
, DB.PERIOD_AGGREGATE13
, 14
, DB.PERIOD_AGGREGATE14
, 15
, DB.PERIOD_AGGREGATE15
, 16
, DB.PERIOD_AGGREGATE16
, 17
, DB.PERIOD_AGGREGATE17
, 18
, DB.PERIOD_AGGREGATE18
, 19
, DB.PERIOD_AGGREGATE19
, 20
, DB.PERIOD_AGGREGATE20
, 21
, DB.PERIOD_AGGREGATE21
, 22
, DB.PERIOD_AGGREGATE22
, 23
, DB.PERIOD_AGGREGATE23
, 24
, DB.PERIOD_AGGREGATE24
, 25
, DB.PERIOD_AGGREGATE25
, 26
, DB.PERIOD_AGGREGATE26
, 27
, DB.PERIOD_AGGREGATE27
, 28
, DB.PERIOD_AGGREGATE28
, 29
, DB.PERIOD_AGGREGATE29
, 30
, DB.PERIOD_AGGREGATE30
, 31
, DB.PERIOD_AGGREGATE31
, 32
, DB.PERIOD_AGGREGATE32
, 33
, DB.PERIOD_AGGREGATE33
, 34
, DB.PERIOD_AGGREGATE34
, 35
, DB.PERIOD_AGGREGATE35
, 0) / ( DPM.ACCOUNTING_DATE - DB.PERIOD_START_DATE + 1 ) / NVL (FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER (10
, -FC.PRECISION))) * NVL (FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER (10
, -FC.PRECISION)) PERIOD_AVERAGE_TO_DATE
, ROUND(DECODE(TRUNC(DPM.ACCOUNTING_DATE) - TRUNC(DB.PERIOD_START_DATE) + 1
, 1
, NVL(DB.QUARTER_AGGREGATE1
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE1)
, 2
, NVL(DB.QUARTER_AGGREGATE2
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE2)
, 3
, NVL(DB.QUARTER_AGGREGATE3
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE3)
, 4
, NVL(DB.QUARTER_AGGREGATE4
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE4)
, 5
, NVL(DB.QUARTER_AGGREGATE5
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE5)
, 6
, NVL(DB.QUARTER_AGGREGATE6
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE6)
, 7
, NVL(DB.QUARTER_AGGREGATE7
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE7)
, 8
, NVL(DB.QUARTER_AGGREGATE8
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE8)
, 9
, NVL(DB.QUARTER_AGGREGATE9
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE9)
, 10
, NVL(DB.QUARTER_AGGREGATE10
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE10)
, 11
, NVL(DB.QUARTER_AGGREGATE11
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE11)
, 12
, NVL(DB.QUARTER_AGGREGATE12
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE12)
, 13
, NVL(DB.QUARTER_AGGREGATE13
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE13)
, 14
, NVL(DB.QUARTER_AGGREGATE14
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE14)
, 15
, NVL(DB.QUARTER_AGGREGATE15
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE15)
, 16
, NVL(DB.QUARTER_AGGREGATE16
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE16)
, 17
, NVL(DB.QUARTER_AGGREGATE17
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE17)
, 18
, NVL(DB.QUARTER_AGGREGATE18
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE18)
, 19
, NVL(DB.QUARTER_AGGREGATE19
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE19)
, 20
, NVL(DB.QUARTER_AGGREGATE20
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE20)
, 21
, NVL(DB.QUARTER_AGGREGATE21
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE21)
, 22
, NVL(DB.QUARTER_AGGREGATE22
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE22)
, 23
, NVL(DB.QUARTER_AGGREGATE23
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE23)
, 24
, NVL(DB.QUARTER_AGGREGATE24
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE24)
, 25
, NVL(DB.QUARTER_AGGREGATE25
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE25)
, 26
, NVL(DB.QUARTER_AGGREGATE26
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE26)
, 27
, NVL(DB.QUARTER_AGGREGATE27
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE27)
, 28
, NVL(DB.QUARTER_AGGREGATE28
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE28)
, 29
, NVL(DB.QUARTER_AGGREGATE29
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE29)
, 30
, NVL(DB.QUARTER_AGGREGATE30
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE30)
, 31
, NVL(DB.QUARTER_AGGREGATE31
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE31)
, 32
, NVL(DB.QUARTER_AGGREGATE32
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE32)
, 33
, NVL(DB.QUARTER_AGGREGATE33
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE33)
, 34
, NVL(DB.QUARTER_AGGREGATE34
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE34)
, 35
, NVL(DB.QUARTER_AGGREGATE35
, OPENING_QUARTER_AGGREGATE + DB.PERIOD_AGGREGATE35)
, 0) / ( DPM.ACCOUNTING_DATE - DB.QUARTER_START_DATE + 1 ) / NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, -FC.PRECISION))) * NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, -FC.PRECISION)) QUARTER_AVERAGE_TO_DATE
, ROUND(DECODE(TRUNC(DPM.ACCOUNTING_DATE) - TRUNC(DB.PERIOD_START_DATE) + 1
, 1
, NVL(DB.YEAR_AGGREGATE1
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE1)
, 2
, NVL(DB.YEAR_AGGREGATE2
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE2)
, 3
, NVL(DB.YEAR_AGGREGATE3
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE3)
, 4
, NVL(DB.YEAR_AGGREGATE4
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE4)
, 5
, NVL(DB.YEAR_AGGREGATE5
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE5)
, 6
, NVL(DB.YEAR_AGGREGATE6
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE6)
, 7
, NVL(DB.YEAR_AGGREGATE7
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE7)
, 8
, NVL(DB.YEAR_AGGREGATE8
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE8)
, 9
, NVL(DB.YEAR_AGGREGATE9
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE9)
, 10
, NVL(DB.YEAR_AGGREGATE10
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE10)
, 11
, NVL(DB.YEAR_AGGREGATE11
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE11)
, 12
, NVL(DB.YEAR_AGGREGATE12
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE12)
, 13
, NVL(DB.YEAR_AGGREGATE13
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE13)
, 14
, NVL(DB.YEAR_AGGREGATE14
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE14)
, 15
, NVL(DB.YEAR_AGGREGATE15
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE15)
, 16
, NVL(DB.YEAR_AGGREGATE16
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE16)
, 17
, NVL(DB.YEAR_AGGREGATE17
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE17)
, 18
, NVL(DB.YEAR_AGGREGATE18
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE18)
, 19
, NVL(DB.YEAR_AGGREGATE19
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE19)
, 20
, NVL(DB.YEAR_AGGREGATE20
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE20)
, 21
, NVL(DB.YEAR_AGGREGATE21
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE21)
, 22
, NVL(DB.YEAR_AGGREGATE22
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE22)
, 23
, NVL(DB.YEAR_AGGREGATE23
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE23)
, 24
, NVL(DB.YEAR_AGGREGATE24
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE24)
, 25
, NVL(DB.YEAR_AGGREGATE25
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE25)
, 26
, NVL(DB.YEAR_AGGREGATE26
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE26)
, 27
, NVL(DB.YEAR_AGGREGATE27
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE27)
, 28
, NVL(DB.YEAR_AGGREGATE28
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE28)
, 29
, NVL(DB.YEAR_AGGREGATE29
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE29)
, 30
, NVL(DB.YEAR_AGGREGATE30
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE30)
, 31
, NVL(DB.YEAR_AGGREGATE31
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE31)
, 32
, NVL(DB.YEAR_AGGREGATE32
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE32)
, 33
, NVL(DB.YEAR_AGGREGATE33
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE33)
, 34
, NVL(DB.YEAR_AGGREGATE34
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE34)
, 35
, NVL(DB.YEAR_AGGREGATE35
, OPENING_YEAR_AGGREGATE + DB.PERIOD_AGGREGATE35)
, 0) / ( DPM.ACCOUNTING_DATE - DB.YEAR_START_DATE + 1 ) / NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER (10
, -FC.PRECISION)) ) * NVL (FC.MINIMUM_ACCOUNTABLE_UNIT
, POWER (10
, -FC.PRECISION)) YEAR_AVERAGE_TO_DATE
FROM GL_LOOKUPS LK
, GL_DAILY_BALANCES DB
, GL_DATE_PERIOD_MAP DPM
, FND_CURRENCIES FC
, GL_LEDGERS LGR
, GL_CODE_COMBINATIONS GCC
WHERE (DPM.PERIOD_NAME || '' = DB.PERIOD_NAME)
AND (DPM.PERIOD_SET_NAME || '' = LGR.PERIOD_SET_NAME)
AND (DPM.PERIOD_TYPE || '' = LGR.ACCOUNTED_PERIOD_TYPE)
AND (FC.CURRENCY_CODE = DB.CURRENCY_CODE)
AND (DB.LEDGER_ID = LGR.LEDGER_ID)
AND (DB.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID)
AND LK.LOOKUP_TYPE = 'CURRENCY_TYPE'
AND LK.LOOKUP_CODE = DECODE( DB.CURRENCY_TYPE
, 'E'
, 'FE'
, 'C'
, 'FC'
, 'T'
, 'FT'
, 'O'
, 'FT'
, 'U'
, DECODE( DB.CURRENCY_CODE
, 'STAT'
, 'STAT'
, 'FUNC' ) )
AND '_SEC:GL:DB.LEDGER_ID
, DB.CODE_COMBINATION_ID' IS NOT NULL WITH READ ONLY