GPCN_BDGT_CM_VW

(SQL View)
 Index Back

Budget Comparison Report

View for Budget Comparison Report by Department

SELECT a.gpcn_bdgt_item , a.gpcn_bdgt_prd , a.setid , a.tree_name , a.tree_effdt , c.gpcn_bdgt_unit , c.descr , c.gpcn_bdgt_rsv , CASE WHEN ( SELECT SUM(f.calc_val) FROM ps_gpcn_bdgt_pr_vw f WHERE f.chr_pin_val = c.gpcn_bdgt_unit AND f.gpcn_bdgt_item = c.gpcn_bdgt_item AND f.gpcn_bdgt_prd = c.gpcn_bdgt_prd AND f.setid = c.setid AND f.tree_name = c.tree_name AND f.tree_effdt = c.tree_effdt GROUP BY f.chr_pin_val) IS NULL THEN 0 ELSE ( SELECT SUM(f.calc_val) FROM ps_gpcn_bdgt_pr_vw f WHERE f.chr_pin_val = c.gpcn_bdgt_unit AND f.gpcn_bdgt_item = c.gpcn_bdgt_item AND f.gpcn_bdgt_prd = c.gpcn_bdgt_prd AND f.setid = c.setid AND f.tree_name = c.tree_name AND f.tree_effdt = c.tree_effdt GROUP BY f.chr_pin_val) END , ( SELECT MAX( slice_end_dt ) FROM ps_gpcn_bdgt_pr_vw f WHERE f.chr_pin_val = c.gpcn_bdgt_unit AND f.gpcn_bdgt_item = c.gpcn_bdgt_item AND f.gpcn_bdgt_prd = c.gpcn_bdgt_prd AND f.setid = c.setid AND f.tree_name = c.tree_name AND f.tree_effdt = c.tree_effdt) AS slice_end_dt FROM ps_gpcn_bdgt_distr a, ps_gpcn_bdgt_dteff b, PS_GPCN_BDGT_DT_BU c WHERE a.gpcn_bdgt_stats = 'D' AND a.gpcn_bdgt_item = b.gpcn_bdgt_item AND a.gpcn_bdgt_prd = b.gpcn_bdgt_prd AND a.setid = b.setid AND a.tree_name = b.tree_name AND a.tree_effdt = b.tree_effdt AND a.gpcn_bdgt_item = c.gpcn_bdgt_item AND a.gpcn_bdgt_prd = c.gpcn_bdgt_prd AND a.setid = c.setid AND a.tree_name = c.tree_name AND a.tree_effdt = c.tree_effdt AND b.effdt = c.effdt AND b.effdt = ( SELECT MAX(effdt) FROM ps_gpcn_bdgt_dteff d WHERE a.gpcn_bdgt_item = d.gpcn_bdgt_item AND a.gpcn_bdgt_prd = d.gpcn_bdgt_prd AND a.setid = d.setid AND a.tree_name = d.tree_name AND a.tree_effdt = d.tree_effdt) AND c.gpcn_bdgt_unit NOT LIKE 'ALL_DEPT%'

  • Related Language Record: GPCN_BG_CM_LANG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 GPCN_BDGT_ITEM Character(10) VARCHAR2(10) NOT NULL Budget Item

    Prompt Table: GPCN_BDGT_ITEM

    2 GPCN_BDGT_PRD Character(10) VARCHAR2(10) NOT NULL Budget Period

    Prompt Table: GPCN_BDGT_PRD

    3 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Prompt Table: SETID_TBL

    4 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name

    Prompt Table: GPCN_SEC_T_VW

    5 TREE_EFFDT Date(10) DATE NOT NULL Effective date of the Tree

    Prompt Table: GPCN_SEC_TE_VW

    6 GPCN_BDGT_UNIT Character(20) VARCHAR2(20) NOT NULL Bdget Unit
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 GPCN_BDGT_RSV Signed Number(16,2) DECIMAL(14,2) NOT NULL Reserved Budget
    9 CALC_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Value

    Default Value: 0

    10 SLICE_END_DT Date(10) DATE Element Slice End Date