GL Balance Detail (flat)

Description
Categories: Enginatics, Financials
GL balance data on detailed segment level. One record for each period and account combination.
select
x.ledger,
x.period_name,
&segment_columns2
x.start_balance,
x.debit,
x.credit,
nvl(x.debit,0)-nvl(x.credit,0) amount,
nvl(x.start_balance,0)+nvl(x.debit,0)-nvl(x.credit,0) end_balance,
x.effective_period_num
from
(
select distinct
gl.name ledger,
gb.period_name,
&segment_columns
sum(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) over (partition by gb.ledger_id, gb.period_name, &segment_columns 1) start_balance,
sum(nvl(gb.period_net_dr,0)) over (partition by gb.ledger_id, gb.period_name, &segment_columns 1) debit,
sum(nvl(gb.period_net_cr,0)) over (partition by gb.ledger_id, gb.period_name, &segment_columns 1) credit,
gcc.chart_of_accounts_id,
gp.period_year*10000+gp.period_num effective_period_num
from
gl_ledgers gl,
gl_periods gp,
gl_balances gb,
gl_code_combinations gcc
where
1=1 and
gl.name=:ledger_name and
gb.actual_flag=decode(:actual_flag,'Actual','A','Budget','B','Encumbrance','E') and
gl.period_set_name=gp.period_set_name and
gl.accounted_period_type=gp.period_type and
gp.period_name=gb.period_name and
gl.ledger_id=gb.ledger_id and
gl.currency_code=gb.currency_code and
gb.code_combination_id=gcc.code_combination_id and
gcc.summary_flag='N'
) x
order by
&segment_columns
x.effective_period_num
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_name) 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
Ledger
gl.name=:ledger_name
LOV
Period To
gp.period_year*10000+gp.period_num<=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_to)
LOV
Period From
gp.period_year*10000+gp.period_num>=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_from)
LOV
Show All Segments
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_name) 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_
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_name) 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_name) 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_name) 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')
Type
 
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_name)
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_name)
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_name)
LOV