DEPT_BDGT_VW

(SQL View)
 Index Back

Dept Budgets View

DEPT_BUDGET is the record which contains the Department Budgets Levels and their respective caps. The available options are Department, Position Pool, Position Number, Job Code, and Appointment. There will be one budget record for each level that is chosen in the department. Furthermore, for each cap that is chosen the cap amount is checked against all higher level caps to assure that the lower level cap does not exceed its respective higher level caps.

SELECT A.SETID , A.DEPTID , A.FISCAL_YEAR , A.POSITION_POOL_ID , A.SETID_JOBCODE , A.JOBCODE , A.POSITION_NBR , A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFF_STATUS , A.EFFDT , A.BUDGET_LEVEL_INDC , A.OVERALL_BDGT_CAP , A.ERN_BDGT_CAP , A.DED_BDGT_CAP , A.TAX_BDGT_CAP , 0 , 0 , 0 FROM PS_DEPT_BUDGET A WHERE A.EFFSEQ = ( SELECT MAX(B.EFFSEQ) FROM PS_DEPT_BUDGET B WHERE B.SETID = A.SETID AND B.DEPTID = A.DEPTID AND B.FISCAL_YEAR = A.FISCAL_YEAR AND B.POSITION_POOL_ID = A.POSITION_POOL_ID AND B.SETID_JOBCODE = A.SETID_JOBCODE AND B.JOBCODE = A.JOBCODE AND B.POSITION_NBR = A.POSITION_NBR AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT = A.EFFDT)

  • Parent record: DEPT_BUDGET_DT

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Prompt Table: SETID_TBL

    2 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    3 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
    4 POSITION_POOL_ID Character(3) VARCHAR2(3) NOT NULL Position Pool ID
    5 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    6 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    7 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    8 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    9 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    10 EFFDT Date(10) DATE Effective Date
    11 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    12 ASOFDATE Date(10) DATE As of Date
    13 BUDGET_LEVEL_INDC Character(1) VARCHAR2(1) NOT NULL Budget Level
    A=Appointment
    D=Department
    J=Jobcode
    O=Position Pool
    P=Position
    14 OVERALL_BDGT_CAP Number(13,2) DECIMAL(12,2) NOT NULL Overall Budget Cap
    15 ERN_BDGT_CAP Number(13,2) DECIMAL(12,2) NOT NULL Earnings Budget Cap
    16 DED_BDGT_CAP Number(13,2) DECIMAL(12,2) NOT NULL Deduction Budget Cap
    17 TAX_BDGT_CAP Number(13,2) DECIMAL(12,2) NOT NULL Tax Budget Cap
    18 EARN_OR_ALL_BDGT Number(13,2) DECIMAL(12,2) NOT NULL Earnings or Overall Budget Amt
    19 DEDUCTION_BDGT Number(13,2) DECIMAL(12,2) NOT NULL Deduction Budget Amount
    20 TAX_BDGT Number(13,2) DECIMAL(12,2) NOT NULL Tax Budget Amount