DEPT_JOBCD_LANG

(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 ,B.LANGUAGE_CD ,A.EFFDT ,B.DESCR FROM PS_POSITION_DATA A ,PS_JOBCODE_LANG 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,B.LANGUAGE_CD,A.EFFDT,B.DESCR

  • Related Language Record for DEPT_JOBCODES

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 EFFDT Date(10) DATE Effective Date
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description