GL Balance (pivot)

➜ Back to Library
Description
Categories: Enginatics, Toolkit - Operations
Repository: Github
Summary GL report including one line per account segment level, including product code, with opening balance, total transaction amount per month for each period selected, and closing balance (trial balance).
Run GL Balance (pivot) and other Oracle EBS reports with Blitz Report™ on our demo environment
select
y.ledger,
&account_type2
&segment_columns2
y.start_balance,
&period_columns
y.total,
nvl(y.start_balance,0)+nvl(y.total,0) ytd,
y.currency_code currency,
&start_balance_reval
&period_columns_reval
&total_reval
&concatenated_segments
y.chart_of_accounts_id
from
(
select distinct
w.ledger,
&account_type
&segment_columns
&concatenated_segments
w.period_name,
sum(w.start_bal       ) over (partition by w.ledger, &account_type &segment_columns w.chart_of_accounts_id) start_balance,
sum(w.start_bal*w.rate) over (partition by w.ledger, &account_type &segment_columns w.chart_of_accounts_id) start_balance_reval,
sum(w.amount          ) over (partition by w.ledger, &account_type &segment_columns w.chart_of_accounts_id) total,
sum(w.abs_amount      ) over (partition by w.ledger, &account_type &segment_columns w.chart_of_accounts_id) abs_total,
sum(w.amount*w.rate   ) over (partition by w.ledger, &account_type &segment_columns w.chart_of_accounts_id) total_reval,
sum(w.amount          ) over (partition by w.ledger, &account_type &segment_columns w.chart_of_accounts_id, w.period_name) amount,
sum(w.amount*w.rate   ) over (partition by w.ledger, &account_type &segment_columns w.chart_of_accounts_id, w.period_name) amount_reval,
w.currency_code,
w.chart_of_accounts_id
from
(
select
gl.name ledger,
&account_type
&segment_columns
&concatenated_segments1
gps.period_name,
decode(gps.start_period,'Y',nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) start_bal,
nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) amount,
abs(nvl(gb.period_net_dr,0))+abs(nvl(gb.period_net_cr,0)) abs_amount,
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 gps.end_date=gdr.conversion_date and gdct.user_conversion_type=:reval_conversion_type and gdct.conversion_type=gdr.conversion_type)) rate,
gl.currency_code,
gl.chart_of_accounts_id
from
gl_ledgers gl,
gl_period_statuses gps0,
(select decode(gps.period_num,min(gps.period_num) over (partition by gps.ledger_id, gps.application_id, gps.period_year),'Y') start_period, gps.* from gl_period_statuses gps) gps,
gl_balances gb,
gl_code_combinations_kfv gcck
where
1=1 and
gps0.period_name=:period_name and
gb.actual_flag=(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) and
gl.ledger_id=gps0.ledger_id and
gps0.application_id=101 and
gps0.application_id=gps.application_id and
gps0.ledger_id=gps.ledger_id and
gps0.period_year=gps.period_year and
gps0.period_num>=gps.period_num and
gps.ledger_id=gb.ledger_id and
gps.period_name=gb.period_name and
gl.currency_code=gb.currency_code and
gb.code_combination_id=gcck.code_combination_id
) w
) x
pivot (
max(x.amount), max(x.amount_reval) reval
for period_name in (
&pivot_columns
)
) y
where
2=2
order by
y.ledger,
&order_by_account_type
&order_by
1
Parameter Name SQL text Validation
Ledger 
gl.name=:ledger
 LOV
Ledger Category 
gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and
gl.ledger_id in (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(+))
 LOV
Period 
 
 LOV
Show Full Year 
select distinct
decode(gps.adjustment_period_flag,'Y','adj'||decode(gps.row_number,1,null,gps.row_number)||'_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||',' pivot_text,
gps.effective_period_num
from
gl_ledgers gl,
(select row_number() over (partition by gps.application_id, gps.ledger_id, gps.period_year, gps.adjustment_period_flag order by gps.period_num) row_number, gps.* from gl_period_statuses gps) gps
where
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (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(+)) and
gps.period_name=:period_name and
gl.ledger_id=gps.ledger_id and
gps.application_id=101
 LOV Oracle
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 (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (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(+)))
 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 (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (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(+)))
 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 (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (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(+)))
 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 (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (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(+)))
 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 (gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') or xxen_util.contains(:ledger,gl.name)='Y') and
gl.ledger_id in (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(+)) and
rownum=1)
order by
fifsv.segment_num
) x
 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
Show Account Type 
gl_account_type,
 LOV
Account Type 
gcck.gl_account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
 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 
 
 LOV
Budget Name 
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.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type = :encumbrance_type)
 LOV
Exclude Inactive 
(y.start_balance<>0 or y.abs_total>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 Balance by Account Hierarchy GL Account Analysis XX GL Balance GL Balance GL Account Analysis (Distributions) XXFT GL Balance Mrugesh: GL Balance AP Accounted Invoice Aging GL Account Analysis (Distributions) 11g
Blitz Report™

Blitz Report™ provides multiple benefits: