SELECT ws_id, year_id, budget_group, worksheet_name, year_name
,service_package, pp_id, position_name, currency_code
,stage1, year_amount1, fte_amount1
,stage2, year_amount2, fte_amount2
,stage3, year_amount3, fte_amount3,
PSB_PSBRPPSV_XMLP_PKG.CF_VARIANCE_AMOUNTFORMULA(year_amount2,year_amount1) CF_VARIANCE_AMOUNT,
PSB_PSBRPPSV_XMLP_PKG.CF_VARIANCE_PERCENTFORMULA(PSB_PSBRPPSV_XMLP_PKG.CF_VARIANCE_AMOUNTFORMULA(year_amount2,year_amount1),year_amount1) CF_VARIANCE_PERCENT
FROM (SELECT ws_id, pp_id, sp_id
,year_id, currency_code, worksheet_name, year_name
,budget_group, service_package, position_name
,stage1, year_amount1, fte_amount1
,stage2, year_amount2, fte_amount2
,stage3, year_amount3, fte_amount3
,ROW_NUMBER() OVER (PARTITION BY ws_id, year_id, bg_id, sp_id, pp_id ORDER BY ws_id, year_id, bg_id, sp_id, pp_id) as sno
FROM (SELECT ws_id, bg_id, pp_id, sp_id, currency_code
,budget_group, service_package
,year_id, position_name, worksheet_name, year_name
,stage stage1
,year_amount year_amount1
,fte_amount fte_amount1
,LEAD (stage, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, pp_id
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id,stage,year_amount) as stage2
,LEAD (year_amount, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, pp_id
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id,stage,year_amount) as year_amount2
,LEAD (fte_amount, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, pp_id
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id,stage,year_amount) as fte_amount2
,TO_NUMBER(DECODE(:p_stage3, NULL, NULL, LEAD (stage, 2) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, pp_id
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id,stage,year_amount))) as stage3
,TO_NUMBER(DECODE(:p_stage3, NULL, NULL, LEAD (year_amount, 2) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, pp_id
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id,stage,year_amount))) as year_amount3
,TO_NUMBER(DECODE(:p_stage3, NULL, NULL, LEAD (fte_amount, 2) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, pp_id
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id,stage,year_amount))) as fte_amount3
FROM (SELECT pw.worksheet_id ws_id
,pwal.budget_year_id year_id
,pw.budget_group_id bg_id
,pwal.service_package_id sp_id
,pp.position_id pp_id
,pwal.currency_code currency_code
,pwal.current_stage_seq stage
,pbs.budget_stage_id stage_id
,SUM(DISTINCT pwal.ytd_amount) year_amount
,SUM(DISTINCT pwal.annual_fte) fte_amount
,pw.name worksheet_name
,pbp.name year_name
,pbg.name budget_group
,psp.service_package_name service_package
,pbs.name stage_name
,pp.name position_name
FROM psb_worksheets pw,
psb_ws_lines pwl,
psb_ws_account_lines pwal,
psb_ws_service_packages_v psp,
psb_budget_groups pbg,
psb_budget_stage_sets pbss,
psb_budget_stages pbs,
psb_ws_fte_lines pwfl,
psb_ws_position_lines pwpl,
psb_positions pp,
psb_budget_periods pbp
WHERE pw.worksheet_id = pwl.worksheet_id
AND pwl.account_line_id = pwal.account_line_id
AND pw.budget_group_id = pbg.budget_group_id
AND pw.worksheet_id = psp.worksheet_id
AND pwal.service_package_id = psp.service_package_id
AND pw.stage_set_id = pbss.budget_stage_set_id
AND pbs.budget_stage_set_id = pbss.budget_stage_set_id
AND pwal.position_line_id = pwpl.position_line_id
AND pwpl.position_id = pp.position_id
AND pwpl.position_line_id = pwfl.position_line_id
AND pbp.budget_period_id = pwal.budget_year_id
AND pbs.sequence_number BETWEEN pwal.start_stage_seq AND nvl(pwal.end_stage_seq, pwal.current_stage_seq)
AND balance_type = 'E'
AND pw.worksheet_id = :p_worksheet_id
AND pwal.service_package_id LIKE NVL(:p_service_package_id, pwal.service_package_id)
AND pbs.budget_stage_id IN (:p_stage1, :p_stage2, :p_stage3)
AND pwal.budget_year_id LIKE NVL(:p_budget_year_id, pwal.budget_year_id)
GROUP BY pw.worksheet_id,pwal.budget_year_id, pw.budget_group_id, pwal.service_package_id, pp.position_id, pwal.currency_code, pwal.current_stage_seq, pbs.budget_stage_id, pw.name, pbp.name, pbg.name, pbs.name, psp.service_package_name, pp.name
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id, currency_code, stage
)
ORDER BY ws_id, year_id, bg_id, sp_id, pp_id
,stage1, year_amount1
,stage2, year_amount2
,stage3, year_amount3)
ORDER BY ws_id, pp_id, sp_id, year_id
,stage1, year_amount1
,stage2, year_amount2
,stage3, year_amount3)
WHERE sno = 1
order by ws_id, year_id, budget_group, service_package, pp_id, position_name, currency_code |
