XXGF GL Balance by Account Hierarchy

Description
Categories: OPM month end close, Toolkit - Operations
Columns: Ledger, Level, Account Type, Account, Account Desc, Start Balance, Jan 2021, Feb 2021, Total, YtD ...
Summary GL report including one line per GL account. This report has multiple collapsible/expandable summary levels based on the GL account hierarchy, with starting balance, total amount per month, ending total and YTD balance.
Parameter 'Additional Segment' can be used to include additional segments e.g. cost center or balancing segment.
select
y.ledger,
lpad(' ',2*(y.level__-1))||y.level__ level_,
&segment2_columns_first
&account_type2
lpad(' ',2*(y.level__-1))||y.flex_value "&segment1_name",
xxen_util.segment_description(y.flex_value,'&segment1',y.chart_of_accounts_id) "&segment1_name desc",
&segment2_columns
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
y.type,
y.path_,
y.flex_value
from
(
select distinct
w.ledger,
w.type,
w.level__,
w.path_,
&account_type
w.flex_value &segment2,
w.period_name,
sum(w.start_bal       ) over (partition by w.ledger, w.type, w.path_, &account_type w.flex_value &segment2) start_balance,
sum(w.start_bal*w.rate) over (partition by w.ledger, w.type, w.path_, &account_type w.flex_value &segment2) start_balance_reval,
sum(w.amount          ) over (partition by w.ledger, w.type, w.path_, &account_type w.flex_value &segment2) total,
sum(w.amount*w.rate   ) over (partition by w.ledger, w.type, w.path_, &account_type w.flex_value &segment2) total_reval,
sum(w.amount          ) over (partition by w.ledger, w.type, w.path_, &account_type w.flex_value &segment2, w.period_name) amount,
sum(w.amount*w.rate   ) over (partition by w.ledger, w.type, w.path_, &account_type w.flex_value &segment2, w.period_name) amount_reval,
w.currency_code,
w.flex_value_set_id,
w.chart_of_accounts_id
from
(
select
gl.name ledger,
v.type,
v.level__,
v.path_||nvl2(v.flex_value,null,'|'||gcc.&segment1) path_,
&account_type
nvl(v.flex_value,gcc.&segment1) flex_value &segment2,
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,
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,
v.flex_value_set_id,
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,
(
select
ffv.summary_flag parent_flag,
gcc.*
from
(
select
(select fifs.flex_value_set_id from fnd_id_flex_segments fifs where gcc.chart_of_accounts_id=fifs.id_flex_num and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.application_column_name='&segment1') flex_value_set_id,
gcc.*
from
gl_code_combinations gcc
) gcc,
(select ffv.* from fnd_flex_values ffv where ffv.parent_flex_value_low is null) ffv
where
gcc.flex_value_set_id=ffv.flex_value_set_id(+) and
gcc.&segment1=ffv.flex_value(+)
) gcc,
(
select
'Parent' type,
rowgen.column_value level__,
regexp_substr(u.path,'[^|]+',1,rowgen.column_value) flex_value,
nvl(substr(u.path,1,instr(u.path,'|',1,rowgen.column_value)-1),u.path) path_,
u.*
from
(
select --generate all paths from top to the lowermost parents, which only contain childs
level level_,
substr(sys_connect_by_path(ffvnh.parent_flex_value,'|'),2) path,
ffvnh.child_flex_value_low,
ffvnh.child_flex_value_high,
ffvnh.flex_value_set_id
from
(select ffvnh.* from fnd_flex_value_norm_hierarchy ffvnh where ffvnh.flex_value_set_id in (&flex_value_set_ids)) ffvnh
where
connect_by_isleaf=1 and
ffvnh.range_attribute='C'
connect by nocycle
ffvnh.parent_flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and
ffvnh.flex_value_set_id=prior ffvnh.flex_value_set_id and
prior ffvnh.range_attribute='P'
start with
1=1
) u,
table(xxen_util.rowgen(u.level_)) rowgen
union all
select
'Child' type,
level+1 level__,
null flex_value,
substr(sys_connect_by_path(ffvnh.parent_flex_value,'|'),2) path_,
null level_,
null path,
ffvnh.child_flex_value_low,
ffvnh.child_flex_value_high,
ffvnh.flex_value_set_id
from
(select ffvnh.* from fnd_flex_value_norm_hierarchy ffvnh where :show_account_level='Y' and ffvnh.flex_value_set_id in (&flex_value_set_ids)) ffvnh
where
connect_by_isleaf=1 and
ffvnh.range_attribute='C'
connect by nocycle
ffvnh.parent_flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and
ffvnh.flex_value_set_id=prior ffvnh.flex_value_set_id and
prior ffvnh.range_attribute='P'
start with
1=1
) v,
(
select distinct --best matching summary templates for all ledgers
x.ledger_id,
min(x.template_id) keep (dense_rank first order by x.score,x.template_id) over (partition by x.ledger_id) template_id
from
(
select
gl.ledger_id,
gst.template_id,
decode(fsav.account,'SEGMENT1',0,decode(gst.segment1_type,'T',0,'D',decode(fsav.balancing,'SEGMENT1',2,4),decode(fsav.balancing,'SEGMENT1',1,2)))+
decode(fsav.account,'SEGMENT2',0,decode(gst.segment2_type,'T',0,'D',decode(fsav.balancing,'SEGMENT2',2,4),decode(fsav.balancing,'SEGMENT2',1,2)))+
decode(fsav.account,'SEGMENT3',0,decode(gst.segment3_type,'T',0,'D',decode(fsav.balancing,'SEGMENT3',2,4),decode(fsav.balancing,'SEGMENT3',1,2)))+
decode(fsav.account,'SEGMENT4',0,decode(gst.segment4_type,'T',0,'D',decode(fsav.balancing,'SEGMENT4',2,4),decode(fsav.balancing,'SEGMENT4',1,2)))+
decode(fsav.account,'SEGMENT5',0,decode(gst.segment5_type,'T',0,'D',decode(fsav.balancing,'SEGMENT5',2,4),decode(fsav.balancing,'SEGMENT5',1,2)))+
decode(fsav.account,'SEGMENT6',0,decode(gst.segment6_type,'T',0,'D',decode(fsav.balancing,'SEGMENT6',2,4),decode(fsav.balancing,'SEGMENT6',1,2)))+
decode(fsav.account,'SEGMENT7',0,decode(gst.segment7_type,'T',0,'D',decode(fsav.balancing,'SEGMENT7',2,4),decode(fsav.balancing,'SEGMENT7',1,2)))+
decode(fsav.account,'SEGMENT8',0,decode(gst.segment8_type,'T',0,'D',decode(fsav.balancing,'SEGMENT8',2,4),decode(fsav.balancing,'SEGMENT8',1,2)))+
decode(fsav.account,'SEGMENT9',0,decode(gst.segment9_type,'T',0,'D',decode(fsav.balancing,'SEGMENT9',2,4),decode(fsav.balancing,'SEGMENT9',1,2)))+
decode(fsav.account,'SEGMENT10',0,decode(gst.segment10_type,'T',0,'D',decode(fsav.balancing,'SEGMENT10',2,4),decode(fsav.balancing,'SEGMENT10',1,2)))+
decode(fsav.account,'SEGMENT11',0,decode(gst.segment11_type,'T',0,'D',decode(fsav.balancing,'SEGMENT11',2,4),decode(fsav.balancing,'SEGMENT11',1,2)))
score,
decode(fsav.account,
'SEGMENT1',gst.segment1_type,
'SEGMENT2',gst.segment2_type,
'SEGMENT3',gst.segment3_type,
'SEGMENT4',gst.segment4_type,
'SEGMENT5',gst.segment5_type,
'SEGMENT6',gst.segment6_type,
'SEGMENT7',gst.segment7_type,
'SEGMENT8',gst.segment8_type,
'SEGMENT9',gst.segment9_type,
'SEGMENT10',gst.segment10_type,
'SEGMENT11',gst.segment11_type
) account_segment_type
from
gl_ledgers gl,
(
select
y.*
from
(
select
fsav.id_flex_num,
fsav.segment_attribute_type,
fsav.application_column_name
from
fnd_segment_attribute_values fsav
where
fsav.application_id=101 and
fsav.id_flex_code='GL#' and
fsav.segment_attribute_type in ('GL_ACCOUNT','GL_BALANCING') and
fsav.attribute_value='Y'
) x
pivot (
max(x.application_column_name)
for segment_attribute_type in ('GL_BALANCING' balancing, 'GL_ACCOUNT' account)
) y
) fsav,
gl_summary_templates gst
where
gl.chart_of_accounts_id=fsav.id_flex_num and
gl.ledger_id=gst.ledger_id
) x
where
x.account_segment_type='D'
) gstv
where
2=2 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 and
gcc.parent_flag='N' and
gcc.flex_value_set_id=v.flex_value_set_id and
gcc.&segment1 between v.child_flex_value_low and v.child_flex_value_high and
gl.ledger_id=gstv.ledger_id(+)
) w
) x
pivot (
max(x.amount), max(x.amount_reval) reval
for period_name in (
&pivot_columns
)
) y
order by
y.ledger,
&order_by_segment2
y.path_
&order_by_account_type
&segment2
Parameter NameSQL textValidation
Show Account Type
account_type,
LOV
Balance Type
 
LOV
Ledger
gl.name=:ledger
LOV
Account Type
gcc.account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
LOV
Period
 
LOV
Ledger Category
gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101) and
gl.ledger_id in (select gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID'))
LOV
Revaluation Conversion Type
 
LOV
Revaluation Currency
 
LOV
Show Full Year
select distinct
decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||',' pivot_text,
gps.effective_period_num
from
gl_ledgers gl,
gl_period_statuses gps
where
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101) 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=fnd_profile.value('GL_ACCESS_SET_ID')) and
gps.period_name=:period_name and
gl.ledger_id=gps.ledger_id and
gps.application_id=101
LOV Oracle
Hierarchy Segment
select
min(fifsv.application_column_name) text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:hierarchy_segment and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101))
LOV
Additional Segment1
select
', '||min(fifsv.application_column_name) text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:additional_segment1 and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101))
LOV
Additional Segment2
select
', '||min(fifsv.application_column_name) text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:additional_segment2 and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101))
LOV
Additional Segment3
select
', '||min(fifsv.application_column_name) text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:additional_segment3 and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101))
LOV
Sort by Additional Segment
select
'y.'||min(lower(fifsv.application_column_name))||',' text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:order_by_add_segment and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101))
LOV
Hierarchy Value
ffvnh.parent_flex_value=:parent_flex_value
LOV
Show Child Account Level
 
LOV Oracle
Summary Template Only
gstv.template_id=gb.template_id
LOV Oracle
Show Full Year
select
y.column_text
from
(
select distinct
max(x.effective_period_num) over (partition by x.column_text) max_effective_period_num,
x.column_text
from
(
select
decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||',' column_text,
gps.effective_period_num
from
gl_ledgers gl,
gl_period_statuses gps0,
gl_period_statuses gps
where
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101) 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=fnd_profile.value('GL_ACCESS_SET_ID')) and
gps0.period_name=:period_name 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
) x
order by
max_effective_period_num
) y
Show Full Year
select distinct
decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||'_reval '||decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||'_'||:reval_currency||',' column_text
from
gl_ledgers gl,
gl_period_statuses gps
where
:reval_currency is not null and
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101) 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=fnd_profile.value('GL_ACCESS_SET_ID')) and
gps.period_name=:period_name and
gl.ledger_id=gps.ledger_id and
gps.application_id=101
Show Full Year
select
y.column_text
from
(
select distinct
max(x.effective_period_num) over (partition by x.column_text) max_effective_period_num,
x.column_text
from
(
select
decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||'_reval '||decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american')||'_'||:reval_currency||',' column_text,
gps.effective_period_num
from
gl_ledgers gl,
gl_period_statuses gps0,
gl_period_statuses gps
where
:reval_currency is not null and
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101) 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=fnd_profile.value('GL_ACCESS_SET_ID')) and
gps0.period_name=:period_name 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
) x
order by
max_effective_period_num
) y
Show Full Year
select distinct
''''||gps.period_name||''' '||decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american') pivot_text
from
gl_ledgers gl,
gl_period_statuses gps
where
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101) 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=fnd_profile.value('GL_ACCESS_SET_ID')) and
gps.period_name=:period_name and
gl.ledger_id=gps.ledger_id and
gps.application_id=101
Show Full Year
select
y.pivot_text||decode(y.max_effective_period_num,max(y.max_effective_period_num) over (),null,',')
from
(
select distinct
max(x.effective_period_num) over (partition by x.pivot_text) max_effective_period_num,
x.pivot_text
from
(
select
''''||gps.period_name||''' '||decode(gps.adjustment_period_flag,'Y','adj_')||to_char(gps.start_date+8,'mon_YYYY','nls_date_language=american') pivot_text,
gps.effective_period_num
from
gl_ledgers gl,
gl_period_statuses gps0,
gl_period_statuses gps
where
(gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101) 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=fnd_profile.value('GL_ACCESS_SET_ID')) and
gps0.period_name=:period_name 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
) x
order by
max_effective_period_num
) y
Hierarchy Segment
select distinct
listagg(fifsv.flex_value_set_id,',') within group (order by fifsv.flex_value_set_id) over () flex_value_set_ids
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:hierarchy_segment and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101))
Hierarchy Segment
<parameter_value>
Additional Segment1
select
'y.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(y.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', y.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:additional_segment1 and
:additional_segment1<>nvl(:order_by_add_segment,'x') and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101)) and
rownum=1
Additional Segment2
select
'y.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(y.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', y.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:additional_segment2 and
:additional_segment2<>nvl(:order_by_add_segment,'x') and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101)) and
rownum=1
Additional Segment3
select
'y.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(y.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', y.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:additional_segment3 and
:additional_segment3<>nvl(:order_by_add_segment,'x') and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101)) and
rownum=1
Sort by Additional Segment
select
'y.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(y.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', y.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.form_left_prompt=:order_by_add_segment and
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:ledger_category,'GL_ASF_LEDGER_CATEGORY',101)) and
rownum=1
Show Account Type
xxen_util.meaning(y.account_type,'ACCOUNT_TYPE',0) account_type,
Show Account Type
,decode(y.account_type,'A',1,'L',2,'O',3,'R',4,'E',5,'C',6,'D',7)
Summary Template Only
gb.template_id is null and
gcc.summary_flag='N'
Revaluation Currency
y.start_balance_reval start_balance_<parameter_value>,
Revaluation Currency
y.total_reval total_<parameter_value>,
nvl(y.start_balance_reval,0)+nvl(y.total_reval,0) ytd_<parameter_value>,
Download
Blitz Report In Action
Blitz Report™