select a.budget_revision_id,
b.budget_group_name,
e.set_of_books_id,
a.code_combination_id,
a.account_type,
a.currency_code,
a.gl_period_name,
a.gl_budget_version_id,
c.concatenated_segments,
c.description,
d.start_date,
b.submission_date,
fl.meaning transaction_type,
nvl(decode(a.revision_type, 'I', decode(a.revision_value_type, 'A',
a.revision_amount, a.budget_balance*a.revision_amount/100),
-(decode(a.revision_value_type, 'A',a.revision_amount,
a.budget_balance*a.revision_amount/100))), 0) revision_value,
PSB_PSBRPBRT_XMLP_PKG.cf_open_balanceformula(a.code_combination_id, a.gl_budget_version_id, e.set_of_books_id, a.currency_code) CF_open_balance,
--PSB_PSBRPBRT_XMLP_PKG.cf_end_balanceformula(:CF_open_balance, :cs_revision_value) CF_end_balance,
PSB_PSBRPBRT_XMLP_PKG.cf_acct_flex_descformula(a.code_combination_id) CF_Acct_Flex_Desc,
PSB_PSBRPBRT_XMLP_PKG.CP_open_balance_p CP_open_balance,
PSB_PSBRPBRT_XMLP_PKG.CP_end_balance_p CP_end_balance
from psb_budget_revision_accounts_v a,
psb_budget_revisions_v b,
gl_code_combinations_kfv c,
gl_period_statuses d,
psb_gl_budget_sets e,
fnd_lookups fl
where a.budget_group_id in (SELECT budget_group_id
FROM psb_budget_groups
WHERE budget_group_type = 'R'
START WITH budget_group_id = :p_budget_group_id
CONNECT BY PRIOR budget_group_id = parent_budget_group_id)
and a.gl_budget_version_id LIKE NVL(:p_gl_budget_version_id, a.gl_budget_version_id)
and b.submission_status = 'A'
and a.gl_period_name = d.period_name
and d.start_date >= :Input_period_start_date
and d.end_date <= :Input_period_end_date
and a.budget_revision_id = b.budget_revision_id
and b.gl_budget_set_id = e.gl_budget_set_id
and e.set_of_books_id = d.set_of_books_id
and d.application_id = 101
and a.code_combination_id = c.code_combination_id
and fl.lookup_type='PSB_REVISION_TRANSACTION_TYPES'
and fl.enabled_flag = 'Y'
and fl.lookup_code = b.transaction_type
ORDER BY 8 ASC,11 ASC,6 ASC,5 ASC,3 ASC,2 ASC,9 ASC,7 ASC,10 ASC,4 ASC , code_combination_id , submission_date , budget_revision_id |