TL_ESS_EERCD_VW

(SQL View)
 Index Back

Self Service Search Vw

Search view intended for use with Self Service events that are not date driven. For the Operator who is logged in, the view returns a row for each current job associated to the Operator's Employee ID.

SELECT O.OPRID , E.EMPLID , E.EMPL_RCD , P.NAME , J.DEPTID , J.JOBCODE , JC.DESCR FROM PS_TL_EMPL_DATA E , PS_PERSON_NAME P , PS_JOB J , PS_JOBCODE_TBL JC , PS_TL_OPR_CLASS_VW O WHERE E.EMPLID = O.EMPLID AND E.EMPLID = P.EMPLID AND E.EMPLID = J.EMPLID AND E.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= E.EFFDT) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT ) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = J.SETID_JOBCODE AND JC1.JOBCODE = J.JOBCODE AND JC1.EFFDT <= J.EFFDT) AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_TL_EMPL_DATA E1 WHERE E1.EMPLID = E.EMPLID AND E1.EMPL_RCD = E.EMPL_RCD AND E1.EFFDT <= %CurrentDateIn)

  • Related Language Record: TL_ESS_EERCDLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    6 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    7 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description