EP_SRCH_JOB_LVW

(SQL View)
 Index Back

Perf Analytics Job Data View

Extracts Job data for Insights Performance Analytics

SELECT A.EMPLID ,A.EMPL_RCD ,D.LANGUAGE_CD ,A.EFFDT ,A.EFFSEQ ,BU.DESCR ,D.DESCR ,JC.DESCR ,CO.DESCR FROM PS_JOB A , PS_JOBCODE_LANG JC , PS_DEPT_TBL_LANG D , PS_BUS_UNIT_HR_LNG BU , PS_COMPNY_TBL_LANG CO WHERE JC.SETID = A.SETID_JOBCODE AND JC.JOBCODE = A.JOBCODE AND JC.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= A.EFFDT ) AND D.SETID = A.SETID_DEPT AND D.DEPTID = A.DEPTID AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID = D.SETID AND D1.DEPTID = D.DEPTID AND D1.EFFDT <= A.EFFDT) AND BU.BUSINESS_UNIT = A.BUSINESS_UNIT AND CO.COMPANY = A.COMPANY AND CO.EFFDT = ( SELECT MAX(EFFDT) FROM PS_COMPANY_TBL CO1 WHERE CO1.COMPANY=CO.COMPANY AND CO1.EFFDT <= A.EFFDT) AND D.LANGUAGE_CD = BU.LANGUAGE_CD AND D.LANGUAGE_CD = CO.LANGUAGE_CD AND D.LANGUAGE_CD = JC.LANGUAGE_CD

  • Related Language Record for EP_SRCH_JOB_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Default Value: PER_ORG_ASGN.EMPLID

    Prompt Table: PERS_SRCH_GBL

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    4 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    5 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    6 EP_BU_DESCR Character(30) VARCHAR2(30) NOT NULL Business Unit
    7 EP_DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department
    8 EP_JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title
    9 EP_COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company