GPCN_BDGT_AL_VW

(SQL View)
 Index Back

Budget Alerts

View for Budget Alerts Report

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 , d.gpcn_alert_pct , d.gpcn_pay_edt , CASE WHEN ( SELECT SUM(f.calc_val) FROM ps_gpcn_bdgt_pr_vw f WHERE f.slice_end_dt <= d.gpcn_pay_edt AND f.chr_pin_val = c.gpcn_bdgt_unit AND f.gpcn_bdgt_item = d.gpcn_bdgt_item AND f.gpcn_bdgt_prd = d.gpcn_bdgt_prd AND f.setid = d.setid AND f.tree_name = d.tree_name AND f.tree_effdt = d.tree_effdt 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.slice_end_dt <= d.gpcn_pay_edt AND f.chr_pin_val = c.gpcn_bdgt_unit AND f.gpcn_bdgt_item = d.gpcn_bdgt_item AND f.gpcn_bdgt_prd = d.gpcn_bdgt_prd AND f.setid = d.setid AND f.tree_name = d.tree_name AND f.tree_effdt = d.tree_effdt 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 FROM ps_gpcn_bdgt_distr a, ps_gpcn_bdgt_dteff b, ps_gpcn_bdgt_dt_bu c, PS_GPCN_BDGT_ALERT d WHERE a.gpcn_bdgt_stats = 'D' AND d.status = 'A' 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 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 a.tree_effdt = c.tree_effdt AND b.effdt = c.effdt AND b.effdt = ( SELECT MAX(effdt) FROM ps_gpcn_bdgt_dteff e WHERE a.gpcn_bdgt_item = e.gpcn_bdgt_item AND a.gpcn_bdgt_prd = e.gpcn_bdgt_prd AND a.setid = e.setid AND a.tree_name = e.tree_name AND a.tree_effdt = e.tree_effdt) AND c.gpcn_bdgt_unit NOT LIKE 'ALL_DEPT%'

  • Related Language Record: GPCN_BG_AL_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 GPCN_ALERT_PCT Number(6,2) DECIMAL(5,2) NOT NULL Alert Percentage
    10 GPCN_PAY_EDT Date(10) DATE Budget payroll end date
    11 CALC_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Value

    Default Value: 0