GL Balance

Description
Categories: Enginatics
Repository: Github
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.
Run GL Balance and other Oracle EBS reports with Blitz Report™ on our demo environment
select
u.period_name,
u.ledger,
&segment_columns2
u.balance_currency,
u.start_balance,
u.debit,
u.credit,
u.amount,
u.end_balance,
u.ledger_currency,
u.ledger_start_balance,
u.ledger_debit,
u.ledger_credit,
u.ledger_amount,
u.ledger_end_balance,
--
u.ledger_amount_act actual,
u.ledger_amount_bud budget,
u.ledger_amount_bud_v_act budget_v_actual,
-u.ledger_amount_bud_v_act actual_v_budget,
u.ledger_amount_enc encumbrance,
u.ledger_amount_enc_v_act encumb_v_actual,
-u.ledger_amount_enc_v_act actual_v_encumb,
--
&reval_columns
&segment_columns3
&hierarchy_levels4
u.effective_period_num,
(select flvv.description from fnd_lookup_values_vl flvv where u.actual_flag=flvv.lookup_code and flvv.lookup_type='BATCH_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0) balance_type,
(select gbv.budget_name from gl_budget_versions gbv where gbv.budget_version_id=u.budget_version_id) budget_name,
(select get.encumbrance_type from gl_encumbrance_types get where get.encumbrance_type_id = u.encumbrance_type_id) encumbrance_type,
case u.actual_flag
when 'A' then '(A) ' || (select flvv.description from fnd_lookup_values_vl flvv where u.actual_flag=flvv.lookup_code and flvv.lookup_type='BATCH_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0)
when 'B' then '(B) ' || (select gbv.budget_name from gl_budget_versions gbv where gbv.budget_version_id=u.budget_version_id)
when 'E' then '(E) ' || (select get.encumbrance_type from gl_encumbrance_types get where get.encumbrance_type_id = u.encumbrance_type_id)
else null
end balance_type_label,
u.period_name_label,
u.period_start
from
(
select distinct
z.period_start,
z.period_name,
z.period_name_label,
z.ledger,
&account_type
&hierarchy_levels3
&segment_columns
z.balance_currency,
sum(z.start_balance) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) start_balance,
sum(z.debit) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) debit,
sum(z.credit) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) credit,
sum(z.amount) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) amount,
sum(z.end_balance) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) end_balance,
--
z.ledger_currency,
sum(z.start_balance_func) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_start_balance,
sum(z.debit_func) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_debit,
sum(z.credit_func) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_credit,
sum(z.amount_func) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_amount,
sum(z.end_balance_func) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_end_balance,
--
sum(decode(z.actual_flag,'A',z.amount_func,0)) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_amount_act,
sum(decode(z.actual_flag,'B',z.amount_func,0)) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_amount_bud,
sum(decode(z.actual_flag,'E',z.amount_func,0)) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_amount_enc,
sum(decode(z.actual_flag,'B',z.amount_func,'A',-z.amount_func,0)) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_amount_bud_v_act,
sum(decode(z.actual_flag,'E',z.amount_func,'A',-z.amount_func,0)) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) ledger_amount_enc_v_act,
--
:reval_currency reval_currency,
z.rate reval_rate,
sum(z.start_balance_func*z.rate) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) reval_start_balance,
sum(z.debit_func*z.rate) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) reval_debit,
sum(z.credit_func*z.rate) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) reval_credit,
sum(z.amount_func*z.rate) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) reval_amount,
sum(z.end_balance_func*z.rate) over (partition by z.ledger, z.period_name, z.ledger_currency, z.balance_currency, &segment_columns z.actual_flag, z.budget_version_id, z.encumbrance_type_id) reval_end_balance,
gcck.chart_of_accounts_id,
z.effective_period_num,
z.actual_flag,
z.budget_version_id,
z.encumbrance_type_id
from
(
select
y.column_value,
decode(y.column_value,2,x.period_start - 1,x.period_start) period_start,
decode(y.column_value,2,'Open Bal.',x.period_name) period_name,
decode(y.column_value,2,'00 '||xxen_report.column_translation('START_BALANCE'),to_char(x.period_year) || '-' || lpad(x.period_num,2,'0')||' ('||x.period_name||')') period_name_label,
x.ledger,
x.ledger_currency,
x.balance_currency,
decode(y.column_value,2,null,x.start_balance) start_balance,
decode(y.column_value,2,null,x.debit) debit,
decode(y.column_value,2,null,x.credit) credit,
decode(y.column_value,2,x.start_balance,x.amount) amount,
decode(y.column_value,2,null,x.end_balance) end_balance,
--
decode(y.column_value,2,null,x.start_balance_func) start_balance_func,
decode(y.column_value,2,null,x.debit_func) debit_func,
decode(y.column_value,2,null,x.credit_func) credit_func,
decode(y.column_value,2,x.start_balance_func,x.amount_func) amount_func,
decode(y.column_value,2,null,x.end_balance_func) end_balance_func,
--
x.rate,
decode(y.column_value,2,0,x.effective_period_num) effective_period_num,
x.start_effective_period_num,
x.code_combination_id,
x.actual_flag,
x.budget_version_id,
x.encumbrance_type_id
from
(
select
gb.period_name,
gl.name ledger,
--nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0) start_balance,
case when :p_currency_type = 'E' and gb.currency_code = gl.currency_code and gb.actual_flag = 'A'
then nvl(gb.begin_balance_dr_beq,0)-nvl(gb.begin_balance_cr_beq,0)
else nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)
end start_balance,
--gb.period_net_dr debit,
case when :p_currency_type = 'E' and gb.currency_code = gl.currency_code and gb.actual_flag = 'A'
then gb.period_net_dr_beq
else gb.period_net_dr
end debit,
--gb.period_net_cr credit,
case when :p_currency_type = 'E' and gb.currency_code = gl.currency_code and gb.actual_flag = 'A'
then gb.period_net_cr_beq
else gb.period_net_cr
end credit,
--nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) amount,
case when :p_currency_type = 'E' and gb.currency_code = gl.currency_code and gb.actual_flag = 'A'
then nvl(gb.period_net_dr_beq,0)-nvl(gb.period_net_cr_beq,0)
else nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)
end amount,
--nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)+nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) end_balance,
case when :p_currency_type = 'E' and gb.currency_code = gl.currency_code and gb.actual_flag = 'A'
then nvl(gb.begin_balance_dr_beq,0)-nvl(gb.begin_balance_cr_beq,0)+nvl(gb.period_net_dr_beq,0)-nvl(gb.period_net_cr_beq,0)
else nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)+nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)
end end_balance,
--
-- functional amounts
--
case
when gb.currency_code = 'STAT' then to_number(null)
when gb.currency_code = gl.currency_code
then case when :p_currency_type = 'T' or gb.actual_flag != 'A'
     then nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)
     else nvl(gb.begin_balance_dr_beq,0)-nvl(gb.begin_balance_cr_beq,0)
     end
when gb.translated_flag = 'R' then nvl(gb.begin_balance_dr_beq,0)-nvl(gb.begin_balance_cr_beq,0)
else to_number(null)
end start_balance_func,
--
case
when gb.currency_code = 'STAT' then to_number(null)
when gb.currency_code = gl.currency_code
then case when :p_currency_type = 'T' or gb.actual_flag != 'A'
     then gb.period_net_dr
     else gb.period_net_dr_beq
     end
when gb.translated_flag = 'R' then gb.period_net_dr_beq
else to_number(null)
end debit_func,
--
case
when gb.currency_code = 'STAT' then to_number(null)
when gb.currency_code = gl.currency_code
then case when :p_currency_type = 'T' or gb.actual_flag != 'A'
     then gb.period_net_cr
     else gb.period_net_cr_beq
     end
when gb.translated_flag = 'R' then gb.period_net_cr_beq
else to_number(null)
end credit_func,
--
case
when gb.currency_code = 'STAT' then to_number(null)
when gb.currency_code = gl.currency_code
then case when :p_currency_type = 'T' or gb.actual_flag != 'A'
     then nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)
     else nvl(gb.period_net_dr_beq,0)-nvl(gb.period_net_cr_beq,0)
     end
when gb.translated_flag = 'R' then nvl(gb.period_net_dr_beq,0)-nvl(gb.period_net_cr_beq,0)
else to_number(null)
end amount_func,
--
case
when gb.currency_code = 'STAT' then to_number(null)
when gb.currency_code = gl.currency_code
then case when :p_currency_type = 'T' or gb.actual_flag != 'A'
     then nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)
     else nvl(gb.begin_balance_dr_beq,0)-nvl(gb.begin_balance_cr_beq,0)+nvl(gb.period_net_dr_beq,0)-nvl(gb.period_net_cr_beq,0)
     end
when gb.translated_flag = 'R' then nvl(gb.begin_balance_dr_beq,0)-nvl(gb.begin_balance_cr_beq,0)+nvl(gb.period_net_dr_beq,0)-nvl(gb.period_net_cr_beq,0)
else to_number(null)
end end_balance_func,
--
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 gp.end_date=gdr.conversion_date and gdct.user_conversion_type=:reval_conversion_type and gdct.conversion_type=gdr.conversion_type)) rate,
gl.currency_code ledger_currency,
gb.currency_code balance_currency,
count(distinct gp.period_num) over () period_count,
gp.period_num,
gp.period_year,
gp.period_year*10000+gp.period_num effective_period_num,
min(gp.period_year*10000+gp.period_num) over () start_effective_period_num,
gp.start_date period_start,
gb.code_combination_id,
gb.actual_flag,
gb.budget_version_id,
gb.encumbrance_type_id
from
gl_ledgers gl,
gl_periods gp,
gl_balances gb
where
1=1 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
gb.currency_code=decode(:p_currency_type,'T',gl.currency_code,'S','STAT',nvl(:p_entered_currency,decode(gb.currency_code,'STAT',null,gb.currency_code))) and
case nvl(gb.translated_flag,'?')
 when 'R' then 'R'
 when 'Y' then 'Y'
 when 'N' then 'Y'
 when '?'
 then case :p_currency_type
      when 'E' then case gb.currency_code when gl.currency_code then 'R' else 'X' end
      when 'S' then 'S'
      when 'T' then case gb.currency_code when 'STAT' then 'X' else 'Y' end
      else null
      end
 else null
 end = case :p_currency_type when 'E' then 'R' when 'S' then 'S' when 'T' then 'Y' else null end
) x,
table(xxen_util.rowgen(case when :show_start_balance is not null and x.effective_period_num=x.start_effective_period_num then 2 else 1 end)) y
where
2=2
) z,
(
select
&hierarchy_levels2
gcck.*
from
(
select
(select fifs.flex_value_set_id from fnd_id_flex_segments fifs where gcck.chart_of_accounts_id=fifs.id_flex_num and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.application_column_name='&hierarchy_segment_column') flex_value_set_id,
gcck.*
from
gl_code_combinations_kfv gcck
where
4=4
) gcck,
(
select
&hierarchy_levels
x.flex_value_set_id,
x.child_flex_value_low,
x.child_flex_value_high
from
(
select
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=:flex_value_set_id) 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
ffvnh.parent_flex_value=:parent_flex_value
) x
) h
where
3=3 and
gcck.flex_value_set_id=h.flex_value_set_id(+)
) gcck
where
z.code_combination_id=gcck.code_combination_id
) u
order by
u.ledger,
&segment_columns
u.effective_period_num,
balance_type,
u.balance_currency
Parameter Name SQL text Validation
Ledger
gl.ledger_id in (select nvl(glsnav.ledger_id,gl0.ledger_id) from gl_ledgers gl0, gl_ledger_set_norm_assign_v glsnav where gl0.name=:ledger and gl0.ledger_id=glsnav.ledger_set_id(+))
LOV
Currency Type
 
LOV Oracle
Entered Currency
 
LOV
Period
gp.period_name=:period_name
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
Show Start Balance
 
LOV
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 '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
: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
) x
LOV
Account Type
gcck.gl_account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0)
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
Hierarchy Segment
select
'regexp_substr(x.path,''[^|]+'',1,'||x.column_value||') level'||x.column_value||',' text
from
table(xxen_util.rowgen(:max_hierarchy_levels)) x
LOV
Hierarchy Name
 
LOV
Concatenated Segments
gcck.concatenated_segments=:concatenated_segments
Char
GL_SEGMENT1
gcck.segment1=:segment1
LOV
GL_SEGMENT1 From
gcck.segment1>=:segment1_from
LOV
GL_SEGMENT1 To
gcck.segment1<=:segment1_to
LOV
GL_SEGMENT2
gcck.segment2=:segment2
LOV
GL_SEGMENT2 From
gcck.segment2>=:segment2_from
LOV
GL_SEGMENT2 To
gcck.segment2<=:segment2_to
LOV
GL_SEGMENT3
gcck.segment3=:segment3
LOV
GL_SEGMENT3 From
gcck.segment3>=:segment3_from
LOV
GL_SEGMENT3 To
gcck.segment3<=:segment3_to
LOV
GL_SEGMENT4
gcck.segment4=:segment4
LOV
GL_SEGMENT4 From
gcck.segment4>=:segment4_from
LOV
GL_SEGMENT4 To
gcck.segment4<=:segment4_to
LOV
GL_SEGMENT5
gcck.segment5=:segment5
LOV
GL_SEGMENT5 From
gcck.segment5>=:segment5_from
LOV
GL_SEGMENT5 To
gcck.segment5<=:segment5_to
LOV
GL_SEGMENT6
gcck.segment6=:segment6
LOV
GL_SEGMENT6 From
gcck.segment6>=:segment6_from
LOV
GL_SEGMENT6 To
gcck.segment6<=:segment6_to
LOV
GL_SEGMENT7
gcck.segment7=:segment7
LOV
GL_SEGMENT7 From
gcck.segment7>=:segment7_from
LOV
GL_SEGMENT7 To
gcck.segment7<=:segment7_to
LOV
GL_SEGMENT8
gcck.segment8=:segment8
LOV
GL_SEGMENT8 From
gcck.segment8>=:segment8_from
LOV
GL_SEGMENT8 To
gcck.segment8<=:segment8_to
LOV
GL_SEGMENT9
gcck.segment9=:segment9
LOV
GL_SEGMENT9 From
gcck.segment9>=:segment9_from
LOV
GL_SEGMENT9 To
gcck.segment9<=:segment9_to
LOV
GL_SEGMENT10
gcck.segment10=:segment10
LOV
GL_SEGMENT10 From
gcck.segment10>=:segment10_from
LOV
GL_SEGMENT10 To
gcck.segment10<=:segment10_to
LOV
Revaluation Currency
 
LOV
Revaluation Conversion Type
 
LOV
Balance Type
gb.actual_flag in (
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
)
LOV
Budget Name
(gb.actual_flag<>'B' or gb.budget_version_id in (select gbv.budget_version_id from gl_budget_versions gbv where gbv.budget_name = :budget_name)
)
LOV
Encumbrance Type
(gb.actual_flag<>'E' or gb.encumbrance_type_id in (select get.encumbrance_type_id from gl_encumbrance_types get where get.encumbrance_type = :encumbrance_type))
LOV
Exclude Inactive
(x.start_balance<>0 or x.debit<>0 or x.credit<>0 or
 x.start_balance_func<>0 or x.debit_func<>0 or x.credit_func<>0)
LOV
Batch
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
)
LOV
Journal
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
)
LOV
Relative Period
gp.period_name = (
select 
x.period_name
from
(
select
1-rank() over (partition by gl.name order by ((gp2.period_year * 10000) + gp2.period_num) desc) relative_period,
gp2.period_name,
gp2.period_type
from
gl_periods gp2,
gl_periods gpc2
where
gl.period_set_name=gp2.period_set_name and
gl.accounted_period_type=gp2.period_type and
gp2.adjustment_period_flag='N' and
gl.period_set_name=gpc2.period_set_name and
gl.accounted_period_type=gpc2.period_type and
gpc2.adjustment_period_flag='N' and
trunc(sysdate-1) between gpc2.start_date and gpc2.end_date and
gp2.start_date <= gpc2.start_date
) x
where x.relative_period = :p_relative_period
)
LOV
Relative Period From
gp.period_year*10000+gp.period_num>=(
select
x.period_year_num
from
(
select
1-rank() over (partition by gl.name order by ((gp2.period_year * 10000) + gp2.period_num) desc) relative_period,
gp2.period_year*10000+gp2.period_num period_year_num
from
gl_periods gp2,
gl_periods gpc2
where
gl.period_set_name=gp2.period_set_name and
gl.accounted_period_type=gp2.period_type and
gp2.adjustment_period_flag='N' and
gl.period_set_name=gpc2.period_set_name and
gl.accounted_period_type=gpc2.period_type and
gpc2.adjustment_period_flag='N' and
trunc(sysdate-1) between gpc2.start_date and gpc2.end_date and
gp2.start_date <= gpc2.start_date
) x
where x.relative_period = :p_relative_period_from
)
LOV
Relative Period To
gp.period_year*10000+gp.period_num<=(
select
x.period_year_num
from
(
select
1-rank() over (partition by gl.name order by ((gp2.period_year * 10000) + gp2.period_num) desc) relative_period,
gp2.period_year*10000+gp2.period_num period_year_num
from
gl_periods gp2,
gl_periods gpc2
where
gl.period_set_name=gp2.period_set_name and
gl.accounted_period_type=gp2.period_type and
gp2.adjustment_period_flag='N' and
gl.period_set_name=gpc2.period_set_name and
gl.accounted_period_type=gpc2.period_type and
gpc2.adjustment_period_flag='N' and
trunc(sysdate-1) between gpc2.start_date and gpc2.end_date and
gp2.start_date <= gpc2.start_date
) x
where x.relative_period = :p_relative_period_to
)
LOV
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: