GL Balance (pivot)

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).

GL Balance (pivot) and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

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 gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1))

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 gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1)) 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 gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1)))

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 gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1)))

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 gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1)))

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 gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1)))

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 gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1)) 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 = :p_budget_name)

LOV

Encumbrance Type

gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type = :p_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
)