HR_SS_EE_RCD_VW

(SQL View)
 Index Back

Compensation History

Compensation History Self Service Transaction: This view returns Job All Titles for each employees.

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFDT , A.EFFSEQ , A.DEPTID , A.SETID_DEPT , B.DESCR , A.JOBCODE , A.SETID_JOBCODE , C.DESCR , A.SUPERVISOR_ID , A.EMPL_STATUS FROM PS_JOB A , PS_DEPT_TBL B , PS_JOBCODE_TBL C WHERE A.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOB JOB WHERE JOB.EMPLID=A.EMPLID AND JOB.EMPL_RCD=A.EMPL_RCD AND JOB.EFFDT<=%CurrentDateIn) AND EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND A.SETID_JOBCODE = C.SETID AND A.JOBCODE = C.JOBCODE AND %EffdtCheck(JOBCODE_TBL, C, A.EFFDT) AND C.EFF_STATUS = 'A' AND A.SETID_DEPT = B.SETID AND A.DEPTID = B.DEPTID AND %EffdtCheck(DEPT_TBL, B, A.EFFDT) AND B.EFF_STATUS = 'A'

  • Related Language Record: HR_SSEERCDVWLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 SS_CHANGE_DATE Date(10) DATE Select to see more details
    5 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

    7 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    10 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    11 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    12 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    13 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
    A=Active
    D=Deceased
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration