GL Balances (Drilldown)

➜ Back to Library
Description
Categories: Enginatics, Upload
Repository: Github
** This report is used by the GL Financial Statement and Drilldown report, to show Balance details per code combination. **

Shows GL balance amounts (Opening Balance, Debit, Credit, Net, Closing Balance) grouped by code combination, with drill-to-journal capability.
Supports all balance types: PTD, YTD, QTD, PJTD, FYS, FYE, CTD.
The Drill To Journal column allows further drilldown int ...  ** This report is used by the GL Financial Statement and Drilldown report, to show Balance details per code combination. **

Shows GL balance amounts (Opening Balance, Debit, Credit, Net, Closing Balance) grouped by code combination, with drill-to-journal capability.
Supports all balance types: PTD, YTD, QTD, PJTD, FYS, FYE, CTD.
The Drill To Journal column allows further drilldown into journal details.   more 
select
gl.name ledger,
gl.ledger_id,
gb.period_name,
gcck.concatenated_segments,
gcck.code_combination_id,
gcck.chart_of_accounts_id,
&balance_columns
xxen_util.description(gb.actual_flag,'BATCH_TYPE',101) balance_type_name,
gb.actual_flag,
(select gbv.budget_name from gl_budget_versions gbv where gb.budget_version_id=gbv.budget_version_id) budget_name,
(select get.encumbrance_type from gl_encumbrance_types get where gb.encumbrance_type_id=get.encumbrance_type_id) encumbrance_type,
gb.currency_code,
&segment_columns
case when xxen_api.user_preference('XXEN_FSG_DD_TO_NEW_WORKBOOK')='Y' then '=dd' else '=dds' end
||'("GJ","'||gl.ledger_id||','||gb.period_name||','||:amount_type||','||gb.currency_code||','||gb.actual_flag||','||(select gbv.budget_name from gl_budget_versions gbv where gb.budget_version_id=gbv.budget_version_id)||','||(select get.encumbrance_type from gl_encumbrance_types get where gb.encumbrance_type_id=get.encumbrance_type_id)||',,,'||gcck.code_combination_id||'")' drill_to_journal
from
gl_ledgers gl,
gl_balances gb,
gl_code_combinations_kfv gcck
where
1=1 and
&gl_flex_value_security
&balance_where
gb.ledger_id=gl.ledger_id and
gb.code_combination_id=gcck.code_combination_id and
gcck.chart_of_accounts_id=gl.chart_of_accounts_id and
gcck.summary_flag='N' and
gb.template_id is null and
(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)<>0 or nvl(gb.period_net_dr,0)<>0 or nvl(gb.period_net_cr,0)<>0)
order by
gcck.concatenated_segments
Parameter NameSQL textValidation
Ledger
gl.name=:ledger
LOV
Period
 
LOV
Ledger ID
gl.ledger_id=:ledger_id
Number
Amount Type
 
Char
Currency Code
gb.currency_code=:currency_code
LOV
Currency Type
 
Char
Balance Type
gb.actual_flag in (
select 
 flvv.lookup_code
from 
fnd_lookup_values_vl flvv 
where flvv.description=:balance_type and 
flvv.lookup_type='BATCH_TYPE' and 
flvv.view_application_id=101 and 
flvv.security_group_id=0
)
LOV
Budget Name
(gb.actual_flag<>'B' or gb.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name = :budget_name)
)
LOV
Encumbrance Type
(gb.actual_flag<>'E' or gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type = :encumbrance_type))
LOV
Concatenated Segments
gcck.concatenated_segments=:concatenated_segments
LOV
Code Combination ID
gcck.code_combination_id=:code_combination_id
Number
Restrict CCIDs through GTT
gb.code_combination_id in (select xrti.id1 from xxen_report_temp_ids xrti)
Char
Download
Related Blitz Reports
QA: GL Balance GL Balance GL Balance Drilldown CYTA Test: GL Balance XXFT GL Balance GL Account Distribution Analysis GL Account Analysis SAM: GL Account Analysis (Drilldown) (with inventory and WIP) GL Journals (Drilldown)
Blitz Report™