GL Balance by Entered Currency

➜ Back to Library
Description
Lists GL account balances for a ledger or ledger set and an accounting period, showing the functional currency balance alongside one separate column per entered (transaction) currency.

The entered currency columns are generated dynamically at runtime from the currencies actually found in GL_BALANCES for the selected ledger scope and period, so no currency list is hard coded. Selecting a led ...  Lists GL account balances for a ledger or ledger set and an accounting period, showing the functional currency balance alongside one separate column per entered (transaction) currency.

The entered currency columns are generated dynamically at runtime from the currencies actually found in GL_BALANCES for the selected ledger scope and period, so no currency list is hard coded. Selecting a ledger set produces the union of the currencies of all its assigned ledgers.

A ledger and period with no foreign entered currencies completes normally and returns a single empty placeholder column, rather than failing on an empty PIVOT list.

Balances are actual end balances (opening balance plus period net movement) of detail accounts. The functional currency balance is the account's total ledger currency balance; the entered currency columns show the amounts entered in each foreign currency (gl_balances rows with translated_flag='R'). Summary account, statistical (STAT) and translated balances are excluded.   more 
select
x.*
from
(
select
gl.name ledger,
gb.period_name,
gcck.concatenated_segments account,
gl.currency_code functional_currency,
sum(case when gb.translated_flag is null then nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)+nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) end) over (partition by gb.ledger_id, gb.period_name, gb.code_combination_id) functional_currency_balance,
case when gb.translated_flag='R' then gb.currency_code end entered_currency_code,
case when gb.translated_flag='R' then nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)+nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) end entered_currency_balance
from
gl_ledgers gl,
gl_balances gb,
gl_code_combinations_kfv gcck
where
gb.ledger_id=gl.ledger_id and
gb.code_combination_id=gcck.code_combination_id and
gb.actual_flag='A' and
gb.template_id is null and
(gb.translated_flag='R' or gb.translated_flag is null and gb.currency_code=gl.currency_code) and
gb.period_name=:p_period_name and
&gl_flex_value_security
gl.ledger_id in
(
select gl2.ledger_id from gl_ledgers gl2 where gl2.ledger_id=:p_ledger_id
union all
select glsnav.ledger_id from gl_ledger_set_norm_assign_v glsnav where glsnav.ledger_set_id=:p_ledger_id
)
)
pivot
(
sum(entered_currency_balance)
for entered_currency_code in
(
&entered_currency_pivot
)
) x
order by
x.ledger,
x.period_name,
x.account
Parameter NameSQL textValidation
Ledger
 
LOV
Period
 
LOV
Download
Related Blitz Reports
CAC WIP Account Value GL Setup Upload CAC Inventory to G/L Reconciliation (Restricted by Org Access) CAC AP Accrual IR ISO Match Analysis CAC Standard Cost Update Submissions CAC Receiving Expense Value (Period-End) XXFT GL Balance GL Account Distribution Analysis QA: GL Balance
Blitz Report™