SELECT gl_cc.CONCATENATED_SEGMENTS,
ROUND(sum(nvl(pg_intr.entered_dr, 0)), 2) debits,
ROUND(sum(nvl(pg_intr.entered_cr, 0)), 2) credits,
pg_intr.reference2,
pg_intr.period_name,
pg_intr.budget_version_id,
gl_budget_set_name,
pg_intr.gl_budget_set_id,
gl_bv.budget_name,
pg_intr.budget_year_id,
pg_intr.currency_code,
pbud_per.name,
pb_st.name stage_name,
pg_intr.code_combination_id,
pg_intr.worksheet_id worksheet_id,
PSB_PSBOTGLR_XMLP_PKG.CP_1_p CP_1
FROM psb_gl_interfaces pg_intr,
GL_CODE_COMBINATIONS_kfv gl_cc,
psb_budget_periods pbud_per,
gl_budgets_v gl_bv,
psb_gl_budget_sets pg_set,
psb_budget_stages pb_st
WHERE pg_intr.worksheet_id= :p_worksheet_id
AND nvl(pg_intr.budget_source_type,'BP') = :p_budget_source_type
AND gl_cc.code_combination_id = pg_intr.code_combination_id
AND pg_intr.budget_version_id = gl_bv.budget_version_id
AND pbud_per.budget_period_id = pg_intr.budget_year_id
AND pb_st.budget_stage_id(+) = pg_intr.budget_stage_id
AND pg_set.gl_budget_set_id = pg_intr.gl_budget_set_id
AND ((:p_budget_year_id IS NULL)
OR
(pg_intr.budget_year_id = :p_budget_year_id)
)
AND ((:p_gl_budget_set_id IS NULL)
OR
(pg_intr.gl_budget_set_id = :p_gl_budget_set_id)
)
GROUP BY pg_intr.worksheet_id,
gl_cc.concatenated_segments,
pg_intr.reference2,
pg_intr.period_name,
pg_intr.budget_version_id,
gl_budget_set_name,
pg_intr.gl_budget_set_id,
pg_intr.budget_year_id,
gl_bv.budget_name,
pg_intr.currency_code,
pbud_per.name,
pb_st.name,
pg_intr.code_combination_id
&cp_sort
ORDER BY 7 ASC,8 ASC,9 ASC,6 ASC,10 ASC,11 ASC,
5 ASC,13 ASC,12 ASC |