GL Balance (pivot)

➜ Back to Library
Description
Categories: Enginatics
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).
select
y.ledger,
&account_type2
&segment_columns2
y.start_balance,
&period_columns
y.total,
nvl(y.start_balance,0)+nvl(y.total,0) end_balance,
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 NameSQL textValidation
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
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
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
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
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
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 Account Analysis (with VT) GL Balance GL Account Analysis (Distributions) XXFT GL Balance Mrugesh: GL Balance AP Accounted Invoice Aging
Blitz Report™