SELECT ws_id, year_id, budget_group, worksheet_name, year_name
, service_package, cc_id, currency_code
,stage1_id, stage1, year_amount1, stage1_name
,stage2_id, stage2, year_amount2, stage2_name
,stage3_id, stage3, year_amount3, stage3_name
,stage4_id, stage4, year_amount4, stage4_name
,PSB_PSBRPACV_XMLP_PKG.CF_ACCOUNTINGFLEXFIELDFORMULA(CC_ID) CF_ACCOUNTINGFLEXFIELD
,PSB_PSBRPACV_XMLP_PKG.CF_VARIANCE_AMOUNTFORMULA(YEAR_AMOUNT2,YEAR_AMOUNT1) CF_VARIANCE_AMOUNT
,PSB_PSBRPACV_XMLP_PKG.CF_VARIANCE_PERCENTFORMULA(PSB_PSBRPACV_XMLP_PKG.CF_VARIANCE_AMOUNTFORMULA(YEAR_AMOUNT2,YEAR_AMOUNT1),YEAR_AMOUNT1) CF_VARIANCE_PERCENT
FROM (SELECT ws_id, cc_id, sp_id
,year_id, type, currency_code, worksheet_name, year_name
,budget_group, service_package
,stage1_id, stage1, year_amount1, stage1_name
,stage2_id, stage2, year_amount2, stage2_name
,stage3_id, stage3, year_amount3, stage3_name
,stage4_id, stage4, year_amount4, stage4_name
,ROW_NUMBER() OVER (PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id ORDER BY ws_id, year_id, bg_id, sp_id, cc_id) as sno
FROM (SELECT ws_id, bg_id, cc_id, sp_id, currency_code
,budget_group, service_package
,year_id, type, worksheet_name, year_name
,stage_id stage1_id
,stage stage1
, DECODE(LEAD (year_amount, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount), NULL,
DECODE(stage_id,:p_stage2,0,year_amount),year_amount) year_amount1
,stage_name stage1_name
,LEAD (stage_id, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount) as stage2_id
,LEAD (stage, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount) as stage2
, DECODE(LEAD (year_amount, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount), NULL,
DECODE(stage_id,:p_stage2,
LEAD (year_amount, 0) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount),0),
LEAD (year_amount, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount)) as year_amount2
,LEAD (stage_name, 1) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount) as stage2_name
,DECODE(:p_stage3, NULL, NULL, LEAD (stage_id, 2) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount)) as stage3_id
,DECODE(:p_stage3, NULL, NULL, LEAD (stage, 2) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,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, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount))) as year_amount3
,DECODE(:p_stage3, NULL, NULL, LEAD (stage_name, 2) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount)) as stage3_name
,DECODE(:p_stage4, NULL, NULL, LEAD (stage_id, 3) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount)) as stage4_id
,DECODE(:p_stage4, NULL, NULL, LEAD (stage, 3) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount)) as stage4
,TO_NUMBER(DECODE(:p_stage4, NULL, NULL, LEAD (year_amount, 3) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount))) as year_amount4
,DECODE(:p_stage4, NULL, NULL, LEAD (stage_name, 3) OVER ( PARTITION BY ws_id, year_id, bg_id, sp_id, cc_id
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id,type,stage,year_amount)) as stage4_name
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
,pwal.code_combination_id cc_id
,pwal.currency_code currency_code
,pwal.current_stage_seq stage
,pbs.budget_stage_id stage_id
,pwal.ytd_amount year_amount
,pw.name worksheet_name
,pbp.name year_name
,pbg.name budget_group
,psp.service_package_name service_package
,pbs.name stage_name
,pwal.account_type type
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_budget_periods pbp,
gl_code_combinations glcc
WHERE pw.worksheet_id = :p_worksheet_id
AND 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 pwal.service_package_id = psp.service_package_id
AND pw.worksheet_id = psp.worksheet_id
AND pw.stage_set_id = pbss.budget_stage_set_id
AND pbs.budget_stage_set_id = pbss.budget_stage_set_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 pwal.code_combination_id = glcc.code_combination_id
AND glcc.summary_flag = 'N'
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, :p_stage4)
AND pwal.budget_year_id LIKE NVL(:p_budget_year_id, pwal.budget_year_id)
ORDER BY ws_id, year_id, bg_id, sp_id, cc_id, currency_code, stage
)
)
ORDER BY ws_id,cc_id,sp_id,year_id,type
,stage1, year_amount1
,stage2, year_amount2
,stage3, year_amount3
,stage4, year_amount4)
WHERE sno = 1
order by ws_id, year_id, budget_group, service_package, cc_id, currency_code |
