GL Balance Detail

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github Columns: Period Name, Ledger, Account Type, Company, Company Desc, Department, Department Desc, Account, Account Desc, Sub-Account ...
Summary GL report including one line per accounting period for each account segment level, including product code, with amounts for opening balance, debits, credits, change amount, ending balance.
select
x.period_name,
x.ledger,
&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,
&segment_columns3
x.effective_period_num
from
(
select distinct
gb.period_name,
gl.name ledger,
&account_type
&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
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.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
) x
order by
x.ledger,
&segment_columns
x.effective_period_num
Parameter Name SQL text Validation
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 xxen_util.contains(:ledger,gl.name)='Y')
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 xxen_util.contains(:ledger,gl.name)='Y')
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 xxen_util.contains(:ledger,gl.name)='Y')
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 xxen_util.contains(:ledger,gl.name)='Y')
LOV
Show All Segments
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 xxen_util.contains(:ledger,gl.name)='Y' and rownum=1)
order by
fifsv.segment_num
LOV
Revaluation Currency
 
LOV
Balance Type
 
LOV
Ledger
gl.name=:ledger
LOV
GL_SEGMENT1
gcc.segment1=:segment1
LOV
GL_SEGMENT1 From
gcc.segment1>=:segment1_from
LOV
GL_SEGMENT1 To
gcc.segment1<=:segment1_to
LOV
GL_SEGMENT2
gcc.segment2=:segment2
LOV
GL_SEGMENT2 From
gcc.segment2>=:segment2_from
LOV
GL_SEGMENT2 To
gcc.segment2<=:segment2_to
LOV
GL_SEGMENT3
gcc.segment3=:segment3
LOV
GL_SEGMENT3 From
gcc.segment3>=:segment3_from
LOV
GL_SEGMENT3 To
gcc.segment3<=:segment3_to
LOV
GL_SEGMENT4
gcc.segment4=:segment4
LOV
GL_SEGMENT4 From
gcc.segment4>=:segment4_from
LOV
GL_SEGMENT4 To
gcc.segment4<=:segment4_to
LOV
GL_SEGMENT5
gcc.segment5=:segment5
LOV
GL_SEGMENT5 From
gcc.segment5>=:segment5_from
LOV
GL_SEGMENT5 To
gcc.segment5<=:segment5_to
LOV
GL_SEGMENT6
gcc.segment6=:segment6
LOV
GL_SEGMENT6 From
gcc.segment6>=:segment6_from
LOV
GL_SEGMENT6 To
gcc.segment6<=:segment6_to
LOV
GL_SEGMENT7
gcc.segment7=:segment7
LOV
GL_SEGMENT7 From
gcc.segment7>=:segment7_from
LOV
GL_SEGMENT7 To
gcc.segment7<=:segment7_to
LOV
Account Type
gcc.account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
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
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
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
GL_SEGMENT10
gcc.segment10=:segment10
LOV
GL_SEGMENT10 From
gcc.segment10>=:segment10_from
LOV
GL_SEGMENT10 To
gcc.segment10<=:segment10_to
LOV
GL_SEGMENT8
gcc.segment8=:segment8
LOV
GL_SEGMENT8 From
gcc.segment8>=:segment8_from
LOV
GL_SEGMENT8 To
gcc.segment8<=:segment8_to
LOV
GL_SEGMENT9
gcc.segment9=:segment9
LOV
GL_SEGMENT9 From
gcc.segment9>=:segment9_from
LOV
GL_SEGMENT9 To
gcc.segment9<=:segment9_to
LOV
Revaluation Conversion Type
 
LOV
Show Company
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y')
Show Company
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||'||'' - '''||'||xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' with desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y')
Show Account
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y')
Show Account
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||'||'' - '''||'||xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' with desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y')
Show Cost Center
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='FA_COST_CTR' and fsav.attribute_value='Y')
Show Cost Center
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||'||'' - '''||'||xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' with desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='FA_COST_CTR' and fsav.attribute_value='Y')
Show Intercompany
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_INTERCOMPANY' and fsav.attribute_value='Y')
Show Intercompany
select distinct
--:dummy
'x.'||lower(fifsv.application_column_name)||'||'' - '''||'||xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' with desc",' text
from
fnd_id_flex_segments_vl fifsv
where
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') and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_INTERCOMPANY' and fsav.attribute_value='Y')
Show All Segments
gcc.account_type,
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
'x.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",'
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 xxen_util.contains(:ledger,gl.name)='Y' and rownum=1)
order by
fifsv.segment_num
) y
Show All Segments
select
--:dummy
'x.'||lower(fifsv.application_column_name)||'||'' - '''||'||xxen_util.segment_description(x.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', x.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' with desc",' 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 xxen_util.contains(:ledger,gl.name)='Y' and rownum=1)
order by
fifsv.segment_num
Summary Template
gb.template_id is null and
gcc.summary_flag='N'
Revaluation Currency
nvl2(xal.gl_sl_link_id,xal.entered_dr,gjl.entered_dr)*decode(nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code),:revaluation_currency,1,gdr.conversion_rate) <parameter_value>_dr,
nvl2(xal.gl_sl_link_id,xal.entered_cr,gjl.entered_cr)*decode(nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code),:revaluation_currency,1,gdr.conversion_rate) <parameter_value>_cr,
(nvl(nvl2(xal.gl_sl_link_id,xal.entered_dr,gjl.entered_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xal.entered_cr,gjl.entered_cr),0))*decode(nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code),:revaluation_currency,1,gdr.conversion_rate) <parameter_value>_amount,
:revaluation_currency <parameter_value>_currency,