ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F41B3869E053B46B63588994 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 Y PSB PSBRPACV_XML US PSB Stage Variance Report for Account Application: Public Sector Budgeting (Obsolete) Source: Stage Variance Report for Account (XML) Short Name: PSBRPACV_XML DB package: PSB_PSBRPACV_XMLP_PKG BI Publisher :p_budget_year_id :p_service_package_id :p_stage1 :p_stage2 :p_stage3 :p_stage4 :p_worksheet_id 1 10 :p_worksheet_id LOV Oracle PSB_SRS_ACCOUNT_WRKSHT_ALL Y select worksheet_id id, worksheet_id value, name description from psb_worksheets pw where current_stage_seq>(select min(sequence_number) from psb_budget_stages where budget_stage_set_id=pw.stage_set_id) and ws_creation_complete='Y' order by value,description Y US Worksheet 2 20 :p_service_package_id LOV Oracle PSB_SRS_SERVICE_PACKAGES_PER_ACCOUNT_WRKSHT Y select service_package_id id, short_name value, name description from psb_service_packages where global_worksheet_id= (select nvl(global_worksheet_id,worksheet_id) from psb_worksheets where worksheet_id=:$flex$.psb_srs_account_wrksht_all) order by value,description US Service Package 3 30 :p_budget_year_id LOV Oracle PSB_SRS_ACCOUNT_BUDGET_YEAR_ALL Y select budget_period_id id, name value, description description from psb_budget_periods pbp where budget_period_type='Y' and budget_calendar_id in (select budget_calendar_id from psb_worksheets where worksheet_id= :$flex$.psb_srs_account_wrksht_all) and budget_year_type_id in (select budget_year_type_id from psb_budget_year_types where year_category_type in ('CY','PP')) order by value,description US Budget Year 4 40 :p_stage1 LOV Oracle PSB_SRS_ACCOUNT_STAGE1_ALL Y select budget_stage_id id, name value, description description from psb_budget_stages pbs where budget_stage_set_id in (select pbss.budget_stage_set_id from psb_worksheets pw,psb_budget_stage_sets pbss where pw.stage_set_id=pbss.budget_stage_set_id and pw.worksheet_id=:$flex$.psb_srs_account_wrksht_all) order by pbs.budget_stage_id Y US Stage 1 5 50 :p_stage2 LOV Oracle PSB_SRS_ACCOUNT_STAGE2_ALL Y select budget_stage_id id, name value, description description from psb_budget_stages pbs where budget_stage_set_id in (select pbss.budget_stage_set_id from psb_worksheets pw,psb_budget_stage_sets pbss where pw.stage_set_id=pbss.budget_stage_set_id and pw.worksheet_id= :$flex$.psb_srs_account_wrksht_all) and budget_stage_id not in (:$flex$.psb_srs_account_stage1_all) order by pbs.budget_stage_id Y US Stage 2 :$flex$.psb_srs_account_stage1_all Stage 1 Stage 2 :$flex$.psb_srs_account_wrksht_all Worksheet Budget Year :$flex$.psb_srs_account_wrksht_all Worksheet Service Package :$flex$.psb_srs_account_wrksht_all Worksheet Stage 1 :$flex$.psb_srs_account_wrksht_all Worksheet Stage 2