PSB Stage Variance Report for Position by Responsibility

Description
Categories: BI Publisher
Application: Public Sector Budgeting (Obsolete)
Source: Stage Variance Report for Position by Responsibility (XML)
Short Name: PSBRRPSV_XML
DB package: PSB_PSBRPPSV_XMLP_PKG
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
Parameter Name SQL text Validation
Worksheet
 
LOV Oracle
Budget Year
 
LOV Oracle
Stage 1
 
LOV Oracle
Stage 2
 
LOV Oracle
Stage 3
 
LOV Oracle