GL Balance Detail (YTD)

Description
Categories: Enginatics, Financials
GL balance data on detailed segment level, year to date amount totals.
Similar to GL Balance Detail (pivot), but better performance without showing period amounts.
select
z.*,
nvl(z.start_balance,0)+nvl(z.total,0) ytd
from
(
select /*+ no_merge(x)*/
x.ledger,
&segment_columns2
max(x.start_balance) over (partition by &segment_columns 1) start_balance,
x.amount total
from
(
select
gl.name ledger,
&account_type
&segment_columns
gl.chart_of_accounts_id,
sum(decode(gps.start_period,'Y',nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0))) start_balance,
sum(nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)) amount
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 gcc
where
1=1 and
gps0.period_name=:period_name and
gb.actual_flag=xxen_util.lookup_code(:balance_type,'XLA_BALANCE_TYPE',602) 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=gcc.code_combination_id
group by
gl.name, &account_type &segment_columns gl.chart_of_accounts_id
) x
) z
order by
&order_by 1
Parameter Name SQL text Validation
Show All Segments
select
lower(fifs.application_column_name)||',' text
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
:dummy=:dummy and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:ledger) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num
LOV
Balance Type
 
LOV
Ledger
gl.name=:ledger
LOV
GL_SEGMENT1
gcc.segment1=:segment1
LOV
GL_SEGMENT2 To
gcc.segment2<=:segment2_to
LOV
GL_SEGMENT2 From
gcc.segment2>=:segment2_from
LOV
GL_SEGMENT2
gcc.segment2=:segment2
LOV
GL_SEGMENT3 To
gcc.segment3<=:segment3_to
LOV
GL_SEGMENT3 From
gcc.segment3>=:segment3_from
LOV
GL_SEGMENT3
gcc.segment3=:segment3
LOV
GL_SEGMENT4 To
gcc.segment4<=:segment4_to
LOV
GL_SEGMENT4 From
gcc.segment4>=:segment4_from
LOV
GL_SEGMENT4
gcc.segment4=:segment4
LOV
GL_SEGMENT5 To
gcc.segment5<=:segment5_to
LOV
GL_SEGMENT5 From
gcc.segment5>=:segment5_from
LOV
GL_SEGMENT5
gcc.segment5=:segment5
LOV
GL_SEGMENT6 To
gcc.segment6<=:segment6_to
LOV
GL_SEGMENT6 From
gcc.segment6>=:segment6_from
LOV
GL_SEGMENT6
gcc.segment6=:segment6
LOV
GL_SEGMENT7 To
gcc.segment7<=:segment7_to
LOV
GL_SEGMENT7 From
gcc.segment7>=:segment7_from
LOV
GL_SEGMENT7
gcc.segment7=:segment7
LOV
Account Type
gcc.account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
LOV
Period
 
LOV
Summary Template
gb.template_id=(select gstv.template_id from gl_summary_templates_v gstv where gstv.template_name=:summary_template and gstv.ledger_name=:ledger)
LOV
Summary Template
gb.template_id is null and
gcc.summary_flag='N'
Show All Segments
select 'xxen_util.meaning(x.account_type,''ACCOUNT_TYPE'',0) account_type,' text from dual
union all
select y.* from
(
select
--:dummy
case when x.level_=1 then
'x.'||lower(fifs.application_column_name)||' "'||fifst.form_left_prompt||'",' else
'xxen_util.segment_description(x.'||lower(fifs.application_column_name)||', '''||fifs.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' desc",'
end text
from
(select level level_ from dual connect by level<3) x,
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:ledger) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num,
x.level_
) y
Show All Segments
select
--:dummy
'"'||fifst.form_left_prompt||'",' text
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:ledger) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num
Show All Segments
gcc.account_type,
Show Intercompany
select
--:dummy
case when x.level_=1 then
'x.'||lower(fsav.application_column_name)||' "'||fifst.form_left_prompt||'",' else
'xxen_util.segment_description(x.'||lower(fsav.application_column_name)||', '''||fsav.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' desc",'
end text
from
(select level level_ from dual connect by level<3) x,
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Intercompany
select
--:dummy
'"'||fifst.form_left_prompt||'",' text
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Cost Center
select
--:dummy
case when x.level_=1 then
'x.'||lower(fsav.application_column_name)||' "'||fifst.form_left_prompt||'",' else
'xxen_util.segment_description(x.'||lower(fsav.application_column_name)||', '''||fsav.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' desc",'
end text
from
(select level level_ from dual connect by level<3) x,
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Cost Center
select
--:dummy
'"'||fifst.form_left_prompt||'",' text
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Account
select
--:dummy
case when x.level_=1 then
'x.'||lower(fsav.application_column_name)||' "'||fifst.form_left_prompt||'",' else
'xxen_util.segment_description(x.'||lower(fsav.application_column_name)||', '''||fsav.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' desc",'
end text
from
(select level level_ from dual connect by level<3) x,
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Account
select
--:dummy
'"'||fifst.form_left_prompt||'",' text
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Company
select
--:dummy
case when x.level_=1 then
'x.'||lower(fsav.application_column_name)||' "'||fifst.form_left_prompt||'",' else
'xxen_util.segment_description(x.'||lower(fsav.application_column_name)||', '''||fsav.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' desc",'
end text
from
(select level level_ from dual connect by level<3) x,
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Company
select
--:dummy
'"'||fifst.form_left_prompt||'",' text
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_tl fifst
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.name=:ledger) and
fsav.application_id=fifst.application_id and
fsav.id_flex_code=fifst.id_flex_code and
fsav.id_flex_num=fifst.id_flex_num and
fsav.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Intercompany
select
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.name=:ledger)
LOV
Show Cost Center
select
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.name=:ledger)
LOV
Show Account
select
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.name=:ledger)
LOV
Show Company
select
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.name=:ledger)
LOV