HR_EC_JOBS_VW

(SQL View)
 Index Back

Compensation History

Fluid Compensation History uses this view to load multiple jobs titles in job selectory field.

SELECT DISTINCT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , JOB.JOBCODE , JOB.SETID_JOBCODE , JC.DESCR , JOB.EMPL_STATUS , JOB.JOB_INDICATOR , 0 FROM PS_JOB JOB , PS_JOBCODE_TBL JC WHERE JOB.EFFDT = ( SELECT MAX(ED1.EFFDT) FROM PS_JOB ED1 WHERE ED1.EMPLID = JOB.EMPLID AND ED1.EMPL_RCD = JOB.EMPL_RCD AND ED1.EFFDT <= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_JOB ES WHERE ES.EMPLID = JOB.EMPLID AND ES.EMPL_RCD = JOB.EMPL_RCD AND ES.EFFDT = JOB.EFFDT) AND JOB.SETID_JOBCODE = JC.SETID AND JOB.JOBCODE = JC.JOBCODE AND %EffdtCheck(JOBCODE_TBL, JC, JOB.EFFDT) AND JC.EFF_STATUS = 'A'

  • Related Language Record: HR_EC_JOBS_LV

    • # 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 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    6 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 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
    9 JOB_INDICATOR Character(1) VARCHAR2(1) NOT NULL This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee. In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
    N=Not Applicable
    P=Primary Job
    S=Secondary Job
    10 SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number