• If you’ve enabled Average Daily Balances (ADB), daily data is stored in GL_DAILY_BALANCES (one row per period with day buckets) and made report-friendly by GL_DAILY_BALANCES_V (one row per GL date).

• For functional/entered balances (currency types U/E/C), End-of-Day (EOD) is derived from period aggregates; the physical END_OF_DAY# columns are populated only for translated balances (T/O).

• Prefer querying the view for analytics and filtering early by Ledger, Year/Period, Currency Type, and key segments for performance.

Why Daily Balances (ADB) matter

Average Daily Balances support regulatory and analytical use cases such as financial-services average balance reporting and daily P&L. Fusion GL extends ADB to income statement accounts and exposes EOD, daily activity, PATD, QATD, and YATD through reports, OTBI, and Smart View.

Where the data lives (and why the view is your friend)

GL_DAILY_BALANCES (table): Stores daily balances by period with an array of columns for each day (e.g., PERIOD_AGGREGATE1…31 and, for translated balances, END_OF_DAY1…31). For non-translated balances, END_OF_DAY* are NULL and EOD is computed from period aggregates.
GL_DAILY_BALANCES_V (view): Normalizes the period-wide structure into one row per accounting date and exposes ready-made columns like END_OF_DATE_BALANCE_NUM, PERIOD_AVERAGE_TO_DATE_NUM, etc.
Tip: For ad‑hoc analysis, you can also use OTBI’s “General Ledger – Average Daily Balances Real Time” subject area, which reads from the Essbase cube and returns ADB measures at GL date grain.

The currency type gotcha (why your END_OF_DAY columns look empty)

In GL_DAILY_BALANCES, END_OF_DAY1…31 are used only for translated balances (currency types T and O). For functional/entered balances (U/E/C), EOD is derived as: For day k=1, EOD = PERIOD_AGGREGATE1; for day k>1, EOD = PERIOD_AGGREGATEk − PERIOD_AGGREGATE(k−1). The view presents a unified END_OF_DATE_BALANCE_NUM so you don’t have to compute it.

A practical query you can use today

Filters on ledger, year/periods, currency type U, and a sample Segment3 filter. Adjust segment concatenation to match your chart of accounts length.

SELECT

gcc.segment1 || ‘.’ || gcc.segment2 || ‘.’ || gcc.segment3 || ‘.’ ||

gcc.segment4 || ‘.’ || gcc.segment5 || ‘.’ || gcc.segment6 || ‘.’ ||

gcc.segment7 || ‘.’ || gcc.segment8 || ‘.’ || gcc.segment9 AS conc_seg,

gdb.ledger_name,

gdb.currency_code,

gdb.period_name,

gdb.accounting_date,

gdb.end_of_date_balance_num

FROM gl_daily_balances_v gdb

JOIN gl_ledgers gl

ON gl.ledger_id = gdb.ledger_id

JOIN gl_code_combinations gcc

ON gdb.code_combination_id = gcc.code_combination_id

WHERE gdb.currency_type = ‘U’ — functional/statistical

AND gdb.ledger_name = :p_ledger_name

AND gdb.period_year = :p_year

AND gdb.period_name IN (:p_period1, :p_period2) — e.g., ‘Sep-24′,’Oct-24’

AND gcc.segment3 = :p_segment3

ORDER BY conc_seg, gdb.accounting_date;

Add daily opening balance and net activity (and split into DR/CR if needed)

Compute opening as prior-day EOD and daily activity as EOD(today) − EOD(yesterday) using window functions:

WITH base AS (

SELECT

gdb.ledger_id,

gdb.ledger_name,

gdb.currency_code,

gdb.code_combination_id,

gcc.segment1 || ‘.’ || gcc.segment2 || ‘.’ || gcc.segment3 || ‘.’ ||

gcc.segment4 || ‘.’ || gcc.segment5 || ‘.’ || gcc.segment6 || ‘.’ ||

gcc.segment7 || ‘.’ || gcc.segment8 || ‘.’ || gcc.segment9 AS conc_seg,

gdb.accounting_date,

gdb.end_of_date_balance_num AS eod

FROM gl_daily_balances_v gdb

JOIN gl_code_combinations gcc

ON gdb.code_combination_id = gcc.code_combination_id

WHERE gdb.currency_type = :p_currency_type — ‘U’ functional, ‘E’ entered, ‘T’ translated

AND gdb.ledger_id = :p_ledger_id

AND gdb.period_year = :p_year

AND gdb.period_name IN (:p_periods)

AND gcc.segment3 = :p_segment3

)

SELECT

conc_seg,

ledger_name,

currency_code,

accounting_date,

LAG(eod) OVER (