TL_EDJC_VW

(SQL View)
 Index Back

Active TL_EMPL_DATA w JobCode

This view brings in the jobs in TL_EMPL_DATA that are currently active along with their job description.

SELECT ED.EMPLID , ED.EMPL_RCD , JC.DESCR FROM PS_TL_EMPL_DATA ED , PS_JOB J , PS_JOBCODE_TBL JC WHERE J.EMPLID = ED.EMPLID AND J.EMPL_RCD = ED.EMPL_RCD AND ED.TIME_RPTG_STATUS = 'A' AND ED.EFFDT = ( SELECT MAX(SUB.EFFDT) FROM PS_TL_EMPL_DATA SUB WHERE SUB.EMPLID = ED.EMPLID AND SUB.EMPL_RCD = ED.EMPL_RCD AND SUB.EFFDT <= %CurrentDateIn) AND J.EFFDT = ( SELECT MAX(SUB.EFFDT) FROM PS_JOB SUB WHERE SUB.EMPLID = J.EMPLID AND SUB.EMPL_RCD = J.EMPL_RCD AND SUB.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(SUB.EFFSEQ) FROM PS_JOB SUB WHERE SUB.EMPLID = J.EMPLID AND SUB.EMPL_RCD = J.EMPL_RCD AND SUB.EFFDT = J.EFFDT) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(SUB.EFFDT) FROM PS_JOBCODE_TBL SUB WHERE SUB.SETID = J.SETID_JOBCODE AND SUB.JOBCODE = J.JOBCODE AND SUB.EFFDT <= %CurrentDateIn)

# 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 DESCR Character(30) VARCHAR2(30) NOT NULL Description