Reports2017-11-18T12:27:27+00:00

GL Balance

Description
Categories: Enginatics, Financials

select
x.ledger,
decode(:summary_mode,'PtD',x.period_name,:period_name) period_name,
&columns2
x.segment2 account,
(select ffvv.description from fnd_id_flex_segments fifs, fnd_flex_values_vl ffvv where fifs.application_column_name='SEGMENT2' and x.segment2=ffvv.flex_value and x.chart_of_accounts_id=fifs.id_flex_num and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.flex_value_set_id=ffvv.flex_value_set_id) account_description,
x.start_balance,
x.debit,
x.credit,
nvl(x.start_balance,0)+nvl(x.debit,0)-nvl(x.credit,0) end_balance
from
(
select distinct
gps0.ledger,
decode(:summary_mode,'PtD',gps.period_name,:period_name) period_name,
&columns
gcc.segment2,
sum(decode(gps.period_num,gps.min_period_num,nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0))) over (partition by &period_name &columns gcc.segment2) start_balance,
sum(nvl(gb.period_net_dr,0)) over (partition by &period_name &columns gcc.segment2) debit,
sum(nvl(gb.period_net_cr,0)) over (partition by &period_name &columns gcc.segment2) credit,
gcc.chart_of_accounts_id,
decode(:summary_mode,'PtD',gps.effective_period_num) effective_period_num
from
(
select
gl.name ledger,
gps.*,
gl.currency_code
from
gl_period_statuses gps,
gl_ledgers gl
where
1=1 and
gps.application_id=101 and
gps.ledger_id=gl.ledger_id
) gps0,
(select min(gps.period_num) over (partition by gps.ledger_id, gps.application_id, &period_name gps.period_year) min_period_num, gps.* from gl_period_statuses gps) gps,
gl_balances gb,
gl_code_combinations gcc
where
2=2 and
gps0.application_id=gps.application_id and
gps0.ledger_id=gps.ledger_id and
gps0.period_year=gps.period_year and
(:summary_mode='YtD' and gps0.period_num>=gps.period_num or :summary_mode='PtD' and gps0.period_num=gps.period_num) and
gps.ledger_id=gb.ledger_id and
gps.period_name=gb.period_name and
gps.currency_code=gb.currency_code and
gb.code_combination_id=gcc.code_combination_id and
gcc.summary_flag='N'
) x
order by
x.segment2,
&order_by
decode(:summary_mode,'PtD',x.effective_period_num) desc

Parameter Name SQL text Validation
Ledger
gl.name=:ledger_name
LOV
Period
gps.period_name=:period_name
LOV
Summary Mode
gps.period_name,
Level
x.segment1,
Level
x.segment5,
Level
x.segment1 company,
(select ffvv.description from fnd_id_flex_segments fifs, fnd_flex_values_vl ffvv where fifs.application_column_name='SEGMENT1' and x.segment1=ffvv.flex_value and x.chart_of_accounts_id=fifs.id_flex_num and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.flex_value_set_id=ffvv.flex_value_set_id) company_description,
Level
x.segment5 intercompany,
(select ffvv.description from fnd_id_flex_segments fifs, fnd_flex_values_vl ffvv where fifs.application_column_name='SEGMENT5' and x.segment5=ffvv.flex_value and x.chart_of_accounts_id=fifs.id_flex_num and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.flex_value_set_id=ffvv.flex_value_set_id) intercompany_description,
Level
gcc.segment1,
Type
gb.actual_flag=decode(:actual_flag,'Actual','A','Budget','B','Encumbrance','E')
LOV
Summary Mode
 
LOV
Lager
gcc.segment5=:intercompany
LOV
Level
gcc.segment5,
LOV
Company
gcc.segment1=:company
LOV

Download
 

By continuing to use the site, you agree to the use of cookies. Accept