ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 7CFF657443A541A786C8DF9C2BD61CA6Y 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 Excluded summary account balances via gb.template_id is null (detail balances only), matching the standard GL Balance report restriction. Without it, summary template balances double count on top of the detail accounts. #,##0.00;[Red]-#,##0.00 US GL Balance by Entered Currency 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. &entered_currency_pivot &gl_flex_value_security :p_ledger_id :p_period_name 1 -20 &gl_flex_value_security select 'xxen_util.flex_ccid_accessible(fnd_global.resp_appl_id,fnd_global.resp_id,gcck.code_combination_id)=''Y'' and' from dual where xxen_util.has_flex_value_security='Y' Char x US GL Flex Value Security 2 -10 &entered_currency_pivot select nvl( listagg(''''||x.currency_code||''' as "'||x.currency_code||'"',',') within group (order by x.currency_code), '''XXX'' as "N/A"' ) from ( select distinct gb.currency_code from gl_balances gb where gb.period_name=:p_period_name and gb.actual_flag='A' and gb.translated_flag='R' and gb.template_id is null and gb.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 ) ) x Char x US Entered Currency Pivot 3 10 :p_ledger_id LOV custom Y select gl.ledger_id id, gl.name value, gl.description from gl_ledgers gl where gl.ledger_id in ( select gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') union select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+) ) order by gl.name Y US Ledger Ledger or ledger set. Selecting a ledger set reports all of its assigned ledgers and produces the union of their entered currencies. 4 20 :p_period_name LOV custom Y select gps.period_name id, gps.period_name value, null description, max(gps.start_date) start_date from gl_period_statuses gps where gps.application_id=101 and gps.ledger_id in ( select gl2.ledger_id from gl_ledgers gl2 where gl2.ledger_id=:$flex$.Ledger union all select glsnav.ledger_id from gl_ledger_set_norm_assign_v glsnav where glsnav.ledger_set_id=:$flex$.Ledger ) group by gps.period_name order by max(gps.start_date) desc Y US Period