PSB Stage Variance Report for Position by Responsibility

➜ Back to Library
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
Run PSB Stage Variance Report for Position by Responsibility and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
Related Blitz Reports
PSB Stage Variance Report for Position PSB Stage Variance Report for Account by Responsibility PSB Stage Variance Report for Account PSB Pooled Position Report for Worksheet PSB Pooled Position Report for Worksheet by Responsibility PSB Worksheet Position Costing Report XML PSB Worksheet Position Costing Report by Responsibility PSB Worksheet Position Costing PSB Worksheet Account Costing Report By Responsibility
Blitz Report™

Blitz Report™ provides multiple benefits: