GL Balance (pivot)

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

SQL 80 lines

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

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

Parameters 45

Parameter

Type

List of values SQL

Ledger

LOV

gl.name=:ledger

Ledger Category

LOV

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(+))

Period

LOV

Show Full Year

LOV Oracle

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

Show Company

LOV

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(+)))

Show Account

LOV

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(+)))

Show Cost Center

LOV

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(+)))

Show Intercompany

LOV

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(+)))

Show All Segments

LOV

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

Summary Template

LOV

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)

Show Account Type

LOV

gl_account_type,

Account Type

LOV

gcck.gl_account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)

Concatenated Segments

Char

gcck.concatenated_segments=:concatenated_segments

GL_SEGMENT1

LOV

gcck.segment1=:segment1

GL_SEGMENT1 From

LOV

gcck.segment1>=:segment1_from

GL_SEGMENT1 To

LOV

gcck.segment1<=:segment1_to

GL_SEGMENT2

LOV

gcck.segment2=:segment2

GL_SEGMENT2 From

LOV

gcck.segment2>=:segment2_from

GL_SEGMENT2 To

LOV

gcck.segment2<=:segment2_to

GL_SEGMENT3

LOV

gcck.segment3=:segment3

GL_SEGMENT3 From

LOV

gcck.segment3>=:segment3_from

GL_SEGMENT3 To

LOV

gcck.segment3<=:segment3_to

GL_SEGMENT4

LOV

gcck.segment4=:segment4

GL_SEGMENT4 From

LOV

gcck.segment4>=:segment4_from

GL_SEGMENT4 To

LOV

gcck.segment4<=:segment4_to

GL_SEGMENT5

LOV

gcck.segment5=:segment5

GL_SEGMENT5 From

LOV

gcck.segment5>=:segment5_from

GL_SEGMENT5 To

LOV

gcck.segment5<=:segment5_to

GL_SEGMENT6

LOV

gcck.segment6=:segment6

GL_SEGMENT6 From

LOV

gcck.segment6>=:segment6_from

GL_SEGMENT6 To

LOV

gcck.segment6<=:segment6_to

GL_SEGMENT7

LOV

gcck.segment7=:segment7

GL_SEGMENT7 From

LOV

gcck.segment7>=:segment7_from

GL_SEGMENT7 To

LOV

gcck.segment7<=:segment7_to

GL_SEGMENT8

LOV

gcck.segment8=:segment8

GL_SEGMENT8 From

LOV

gcck.segment8>=:segment8_from

GL_SEGMENT8 To

LOV

gcck.segment8<=:segment8_to

Revaluation Currency

LOV

Revaluation Conversion Type

LOV

Balance Type

LOV

Budget Name

LOV

gb.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name = :budget_name)

Encumbrance Type

LOV

gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type = :encumbrance_type)

Exclude Inactive

LOV

(y.start_balance<>0 or y.abs_total>0)

Batch

LOV

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
)

Journal

LOV

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
)

Description

SQL 80 lines

Parameters 45

Find out more

Features

Download

FAQ