JOBCODE_BDGT_VW

(SQL View)
 Index Back

Budget Job Codes View

The JOBCODE_BDGT_VW selects all current jobcodes which are not used by position management. These jobcodes are available for budgeting.

SELECT DISTINCT B.SETID , B.BUDGET_DEPTID , A.SETID , A.JOBCODE , A.DESCR , A.EFF_STATUS , A.POSN_MGMT_INDC FROM PS_JOBCODE_TBL A , PS_DEPT_TBL B , PS_SET_JOB_DEP_VW C WHERE A.SETID = C.SETID_JOBCODE AND B.SETID = C.SETID AND ((A.EFFDT > B.EFFDT AND NOT EXISTS ( SELECT 'X' FROM PS_JOBCODE_TBL A1 WHERE A1.SETID = A.SETID AND A1.JOBCODE = A.JOBCODE AND A1.EFFDT < A.EFFDT AND A1.EFFDT > B.EFFDT) AND NOT EXISTS ( SELECT 'X' FROM PS_DEPT_TBL B1 WHERE B1.SETID = B.SETID AND B1.DEPTID = B.DEPTID AND B1.EFFDT < A.EFFDT AND B1.EFFDT > B.EFFDT)) OR A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_JOBCODE_TBL A2 WHERE A2.SETID = A.SETID AND A2.JOBCODE = A.JOBCODE AND A2.EFFDT <= B.EFFDT))

  • Related Language Record: JC_BDGT_L_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    3 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    7 POSN_MGMT_INDC Character(1) VARCHAR2(1) NOT NULL Used by Position Management