XX GL Balance

➜ Back to Library
Description
Summary GL report including one line per accounting period for each account segment level, including product code, with amounts for opening balance, debits, credits, change amount, ending balance.
Run XX GL Balance and other Oracle EBS reports with Blitz Report™ on our demo environment
select
u.period_name,
u.ledger,
&segment_columns2
u.start_balance,
u.debit,
u.credit,
u.amount,
u.end_balance,
u.currency_code,
&reval_columns
&segment_columns3
&hierarchy_levels4
u.effective_period_num,
(select flvv.description from fnd_lookup_values_vl flvv where u.actual_flag=flvv.lookup_code and flvv.lookup_type='BATCH_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0) balance_type
from
(
select distinct
z.period_name,
z.ledger,
&account_type
&hierarchy_levels3
&segment_columns
sum(z.start_balance) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) start_balance,
sum(z.debit) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) debit,
sum(z.credit) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) credit,
sum(z.amount) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) amount,
sum(z.end_balance) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) end_balance,
z.currency_code,
sum(z.start_balance*z.rate) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) start_balance_reval,
sum(z.debit*z.rate) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) debit_reval,
sum(z.credit*z.rate) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) credit_reval,
sum(z.amount*z.rate) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) amount_reval,
sum(z.end_balance*z.rate) over (partition by z.ledger, z.period_name, z.currency_code, &segment_columns z.actual_flag) end_balance_reval,
gcck.chart_of_accounts_id,
z.effective_period_num,
z.actual_flag
from
(
select
y.column_value,
decode(y.column_value,2,'00 '||xxen_report.column_translation('START_BALANCE'),case when :show_start_balance is not null or x.period_count>1 then lpad(x.period_num,2,'0')||' ' end||x.period_name) period_name,
x.ledger,
decode(y.column_value,2,null,x.start_balance) start_balance,
decode(y.column_value,2,null,x.debit) debit,
decode(y.column_value,2,null,x.credit) credit,
decode(y.column_value,2,x.start_balance,x.amount) amount,
decode(y.column_value,2,null,x.end_balance) end_balance,
x.rate,
x.currency_code,
decode(y.column_value,2,0,x.effective_period_num) effective_period_num,
x.start_effective_period_num,
x.code_combination_id,
x.actual_flag
from
(
select
gb.period_name,
gl.name ledger,
nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0) start_balance,
gb.period_net_dr debit,
gb.period_net_cr credit,
nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) amount,
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_balance,
decode(gl.currency_code,:reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where gl.currency_code=gdr.from_currency and gdr.to_currency=:reval_currency and gp.end_date=gdr.conversion_date and gdct.user_conversion_type=:reval_conversion_type and gdct.conversion_type=gdr.conversion_type)) rate,
gl.currency_code,
count(distinct gp.period_num) over () period_count,
gp.period_num,
gp.period_year*10000+gp.period_num effective_period_num,
min(gp.period_year*10000+gp.period_num) over () start_effective_period_num,
gb.code_combination_id,
gb.actual_flag
from
gl_ledgers gl,
gl_periods gp,
gl_balances gb
where
1=1 and
gl.period_set_name=gp.period_set_name and
gl.accounted_period_type=gp.period_type and
gp.period_name=gb.period_name and
gl.ledger_id=gb.ledger_id and
gl.currency_code=gb.currency_code
) x,
table(xxen_util.rowgen(case when :show_start_balance is not null and x.effective_period_num=x.start_effective_period_num then 2 else 1 end)) y
where
2=2
) z,
(
select
&hierarchy_levels2
gcck.*
from
(
select
(select fifs.flex_value_set_id from fnd_id_flex_segments fifs where gcck.chart_of_accounts_id=fifs.id_flex_num and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.application_column_name='&hierarchy_segment_column') flex_value_set_id,
gcck.*
from
gl_code_combinations_kfv gcck
where
4=4
) gcck,
(
select
&hierarchy_levels
x.flex_value_set_id,
x.child_flex_value_low,
x.child_flex_value_high
from
(
select
substr(sys_connect_by_path(ffvnh.parent_flex_value,'|'),2) path,
ffvnh.child_flex_value_low,
ffvnh.child_flex_value_high,
ffvnh.flex_value_set_id
from
(select ffvnh.* from fnd_flex_value_norm_hierarchy ffvnh where ffvnh.flex_value_set_id=:flex_value_set_id) ffvnh
where
connect_by_isleaf=1 and
ffvnh.range_attribute='C'
connect by nocycle
ffvnh.parent_flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and
ffvnh.flex_value_set_id=prior ffvnh.flex_value_set_id and
prior ffvnh.range_attribute='P'
start with
ffvnh.parent_flex_value=:parent_flex_value
) x
) h
where
3=3 and
gcck.flex_value_set_id=h.flex_value_set_id(+)
) gcck
where
z.code_combination_id=gcck.code_combination_id
) u
order by
u.ledger,
&segment_columns
u.effective_period_num,
balance_type
Parameter Name SQL text Validation
Ledger 
gl.name=:ledger
 LOV
Period 
gp.period_name=:period_name
 LOV
Period From 
gp.period_year*10000+gp.period_num>=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_from)
 LOV
Period To 
gp.period_year*10000+gp.period_num<=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_to)
 LOV
Show Start Balance 
 
 LOV
Show Company 
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_BALANCING' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y')
 LOV
Show Account 
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_ACCOUNT' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y')
 LOV
Show Cost Center 
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='FA_COST_CTR' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y')
 LOV
Show Intercompany 
select distinct
lower(fsav.application_column_name)||',' text
from
fnd_segment_attribute_values fsav
where
:dummy=:dummy and
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type='GL_INTERCOMPANY' and
fsav.attribute_value='Y' and
fsav.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y')
 LOV
Show All Segments 
select 'concatenated_segments,' text from dual union all
select x.* from
(
select
lower(fifsv.application_column_name)||',' text
from
fnd_id_flex_segments_vl fifsv
where
:dummy=:dummy and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' and rownum=1)
order by
fifsv.segment_num
) x
 LOV
Account Type 
gcck.gl_account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
 LOV
Summary Template 
gb.template_id=(select gst.template_id from gl_summary_templates gst, gl_ledgers gl where gst.ledger_id = gl.ledger_id and gst.template_name=:summary_template and gl.name=:ledger)
 LOV
Hierarchy Segment 
select
'regexp_substr(x.path,''[^|]+'',1,'||x.column_value||') level'||x.column_value||',' text
from
table(xxen_util.rowgen(:max_hierarchy_levels)) x
 LOV
Hierarchy Name 
 
 LOV
Concatenated Segments 
gcck.concatenated_segments=:concatenated_segments
 Char
GL_SEGMENT1 
gcck.segment1=:segment1
 LOV
GL_SEGMENT1 From 
gcck.segment1>=:segment1_from
 LOV
GL_SEGMENT1 To 
gcck.segment1<=:segment1_to
 LOV
GL_SEGMENT2 
gcck.segment2=:segment2
 LOV
GL_SEGMENT2 From 
gcck.segment2>=:segment2_from
 LOV
GL_SEGMENT2 To 
gcck.segment2<=:segment2_to
 LOV
GL_SEGMENT3 
gcck.segment3=:segment3
 LOV
GL_SEGMENT3 From 
gcck.segment3>=:segment3_from
 LOV
GL_SEGMENT3 To 
gcck.segment3<=:segment3_to
 LOV
GL_SEGMENT4 
gcck.segment4=:segment4
 LOV
GL_SEGMENT4 From 
gcck.segment4>=:segment4_from
 LOV
GL_SEGMENT4 To 
gcck.segment4<=:segment4_to
 LOV
GL_SEGMENT5 
gcck.segment5=:segment5
 LOV
GL_SEGMENT5 From 
gcck.segment5>=:segment5_from
 LOV
GL_SEGMENT5 To 
gcck.segment5<=:segment5_to
 LOV
GL_SEGMENT6 
gcck.segment6=:segment6
 LOV
GL_SEGMENT6 From 
gcck.segment6>=:segment6_from
 LOV
GL_SEGMENT6 To 
gcck.segment6<=:segment6_to
 LOV
GL_SEGMENT7 
gcck.segment7=:segment7
 LOV
GL_SEGMENT7 From 
gcck.segment7>=:segment7_from
 LOV
GL_SEGMENT7 To 
gcck.segment7<=:segment7_to
 LOV
GL_SEGMENT8 
gcck.segment8=:segment8
 LOV
GL_SEGMENT8 From 
gcck.segment8>=:segment8_from
 LOV
GL_SEGMENT8 To 
gcck.segment8<=:segment8_to
 LOV
GL_SEGMENT9 
gcck.segment9=:segment9
 LOV
GL_SEGMENT9 From 
gcck.segment9>=:segment9_from
 LOV
GL_SEGMENT9 To 
gcck.segment9<=:segment9_to
 LOV
GL_SEGMENT10 
gcck.segment10=:segment10
 LOV
GL_SEGMENT10 From 
gcck.segment10>=:segment10_from
 LOV
GL_SEGMENT10 To 
gcck.segment10<=:segment10_to
 LOV
Revaluation Currency 
 
 LOV
Revaluation Conversion Type 
 
 LOV
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
Exclude Inactive 
(x.start_balance<>0 or x.debit<>0 or x.credit<>0)
 LOV
Batch 
gcck.code_combination_id in
(select 
 gjl.code_combination_id
 from 
 gl_je_lines   gjl,
 gl_je_headers gjh,
 gl_je_batches gjb
 where
 gjl.je_header_id = gjh.je_header_id and
 gjh.je_batch_id = gjb.je_batch_id and
 gjb.name = :batch_name
)
 LOV
Journal 
gcck.code_combination_id in
(select 
 gjl.code_combination_id
 from 
 gl_je_lines   gjl,
 gl_je_headers gjh
 where
 gjl.je_header_id = gjh.je_header_id and
 gjh.name = :journal_name
)
 LOV
Download
Related Blitz Reports
GL Account Analysis CAC WIP Pending Cost Adjustment CAC ICP PII WIP Pending Cost Adjustment CAC ICP PII Inventory Pending Cost Adjustment GL Balance GL Balance (pivot) GL Account Analysis (Distributions) XXFT GL Balance GL Balance by Account Hierarchy Mrugesh: GL Balance
Blitz Report™

Blitz Report™ provides multiple benefits: