PSB Stage Variance Report for Account by Responsibility

➜ Back to Library
Description
Categories: BI Publisher
Application: Public Sector Budgeting (Obsolete)
Source: Stage Variance Report for Account by Responsibility (XML)
Short Name: PSBRRACV_XML
DB package: PSB_PSBRPACV_XMLP_PKG
Run PSB Stage Variance Report for Account 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, 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
Parameter Name SQL text Validation
Worksheet 
 
 LOV Oracle
Budget Year 
 
 LOV Oracle
Stage 1 
 
 LOV Oracle
Stage 2 
 
 LOV Oracle
Stage 3 
 
 LOV Oracle
Stage 4 
 
 LOV Oracle
Download
Blitz Report™

Blitz Report™ provides multiple benefits: