DEPT_JOBCODES

(SQL View)
 Index Back

Job Codes within Department

The list of all Job Codes used in a given Department. This view is used for F4 prompt support in Position Budgeting.

SELECT E.SETID ,A.DEPTID ,B.SETID ,A.JOBCODE ,A.EFFDT ,B.DESCR FROM PS_POSITION_DATA A ,PS_JOBCODE_TBL B ,PS_DEPT_TBL E WHERE E.SETID=(SELECT Z.SETID FROM PS_SET_CNTRL_REC Z WHERE Z.SETCNTRLVALUE=A.BUSINESS_UNIT AND Z.RECNAME='DEPT_TBL') AND E.DEPTID=A.DEPTID AND B.SETID=(SELECT Z.SETID FROM PS_SET_CNTRL_REC Z WHERE Z.SETCNTRLVALUE=A.BUSINESS_UNIT AND Z.RECNAME='JOBCODE_TBL') AND A.JOBCODE = B.JOBCODE AND B.EFFDT = (SELECT MAX(D.EFFDT) FROM PS_JOBCODE_TBL D WHERE D.JOBCODE = B.JOBCODE AND D.SETID = B.SETID AND D.EFFDT<=%CURRENTDATEIN) AND E.EFFDT=(SELECT MAX(F.EFFDT) FROM PS_DEPT_TBL F WHERE F.DEPTID = E.DEPTID AND F.SETID=E.SETID AND F.EFFDT<=%CURRENTDATEIN) GROUP BY E.SETID,A.DEPTID,B.SETID,A.JOBCODE,A.EFFDT,B.DESCR

  • Related Language Record: DEPT_JOBCD_LANG

    • # 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

    Prompt Table: DEPT_TBL

    3 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
    4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    5 EFFDT Date(10) DATE Effective Date
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description