GPBR_EM_ES_R_VW

(SQL View)
 Index Back

Emp Rcd by EstabID and EmplID

Get Employee Record by Establishment ID and Employee ID

SELECT A.ESTABID , A.EMPLID , A.EMPL_RCD , C.NAME_DISPLAY FROM PS_JOB A , PS_ESTAB_TBL_BRA B ,PS_NAMES C WHERE A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND A.ESTABID = B.ESTABID AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ESTAB_TBL_BRA WHERE ESTABID = B.ESTABID AND EFFDT <= %CurrentDateIn) AND C.EMPLID = A.EMPLID AND C.NAME_TYPE = 'PRI' AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PS_NAMES WHERE EMPLID = C.EMPLID AND NAME_TYPE = C.NAME_TYPE AND EFF_STATUS = C.EFF_STATUS AND EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country