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

GL Balance by Account (flat)

Description
Categories: Enginatics, Financials
select
x.ledger,
x.period_name,
x.&account_segment account,
xxen_util.segment_description(x.&account_segment,'&account_segment',x.chart_of_accounts_id) account_description,
&segment_col2
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
gl.name ledger,
gps.period_name,
gcc.&account_segment,
&segment_cols
sum(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) over (partition by gl.name, gps.period_name, &segment_cols gcc.&account_segment) start_balance,
sum(nvl(gb.period_net_dr,0)) over (partition by gl.name, gps.period_name, &segment_cols gcc.&account_segment) debit,
sum(nvl(gb.period_net_cr,0)) over (partition by gl.name, gps.period_name, &segment_cols gcc.&account_segment) credit,
gcc.chart_of_accounts_id,
gps.effective_period_num
from
gl_ledgers gl,
gl_period_statuses gps0,
gl_period_statuses gps,
gl_balances gb,
gl_code_combinations gcc
where
gl.name=:ledger_name and
gps0.period_name=:period_name and
gb.actual_flag=decode(:actual_flag,'Actual','A','Budget','B','Encumbrance','E') and
1=1 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.summary_flag='N'
) x
order by
x.&account_segment,
&order_by
x.effective_period_num
Parameter Name SQL text Validation
Show Product
gcc.segment5,
LOV
Show Cost Center
gcc.segment4,
LOV
Ledger
 
LOV
Period
 
LOV
Show Product
x.segment5 product,
Show Product
xxen_util.segment_description(x.segment5,'SEGMENT5',x.chart_of_accounts_id) product_description,
Show Product
x.segment5,
Show Cost Center
xxen_util.segment_description(x.segment4,'SEGMENT4',x.chart_of_accounts_id) cost_center_description,
Show Cost Center
x.segment4 cost_center,
Show Cost Center
x.segment4,
Ledger
select
fifs.application_column_name
from
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='US' and
(lower(fifs.segment_name)='account' or lower(fifst.form_left_prompt)='account')
Type
 
LOV
Product
gcc.segment5=:segment5
LOV
Cost Center
gcc.segment4=:segment4
LOV
By continuing to use the site, you agree to the use of cookies. Accept