GL Balance by Entered Currency Copy link
Description
- Categories
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.
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.
SQL 43 lines
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 |
Parameters 2
| Parameter | Type | List of values SQL |
|---|---|---|
| Ledger | LOV | |
| Period | LOV |