DR_CURRENT_JOB

(SQL View)
 Index Back

Current Job for Direct Reports

This is a view that returns the current row for employees and contingent workers.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.PER_ORG , A.DEPTID , A.JOBCODE , A.POSITION_NBR , A.EMPL_STATUS , A.HR_STATUS , A.ACTION , A.ACTION_DT , A.ACTION_REASON , A.LOCATION , A.JOB_ENTRY_DT , A.DEPT_ENTRY_DT , A.POSITION_ENTRY_DT , A.COMPANY , A.BUSINESS_UNIT , A.SETID_DEPT , A.SETID_JOBCODE , A.SETID_LOCATION , A.POSITION_OVERRIDE FROM PS_JOB A WHERE A.PER_ORG IN ( 'EMP', 'CWR') AND A.EFFDT = ( SELECT MAX(C.EffDt) FROM PS_JOB C WHERE C.EmplID = A.EmplID AND C.EMPL_RCD = A.EMPL_RCD AND C.EffDt <= %CurrentDateIn) AND A.EffSeq = ( SELECT MAX(D.EffSeq) FROM PS_JOB D WHERE D.EmplID = A.EmplID AND D.EMPL_RCD = A.EMPL_RCD AND D.EffDt = A.EffDt)

  • Parent record: PER_ORG_ASGN

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

    Prompt Table: PERSON

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 EFFDT Date(10) DATE NOT NULL Effective Date
    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
    CWR=Contingent Worker
    EMP=Employee
    POI=Person of Interest
    6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

    7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    8 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    9 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
    10 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    11 ACTION Character(3) VARCHAR2(3) NOT NULL Action

    Prompt Table: ACTION_TBL

    12 ACTION_DT Date(10) DATE Action Date
    13 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

    Prompt Table: ACTN_REASON_TBL

    14 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    15 JOB_ENTRY_DT Date(10) DATE Job Entry Date
    16 DEPT_ENTRY_DT Date(10) DATE Department Entry Date
    17 POSITION_ENTRY_DT Date(10) DATE Position Entry Date
    18 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    19 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    20 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    21 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    22 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    23 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data