ADP_EMPL_VW

(SQL View)
 Index Back

ADP Connection

ADP Connection view used by ADP and not in export.

SELECT A.COMPANY , A.PAYGROUP , A.EMPLID , A.EMPL_RCD , B.NATIONAL_ID , C.NAME , C.LAST_NAME_SRCH , C.FIRST_NAME_SRCH FROM PS_JOB A , PS_PERS_NID B , PS_PERSON_NAME C , PS_PERSON_ADDR_HOM E , PS_PI_PARTIC F WHERE A.PER_ORG = 'EMP' AND C.EMPLID = A.EMPLID AND B.EMPLID = A.EMPLID AND B.EMPLID = E.EMPLID AND E.COUNTRY = B.COUNTRY AND A.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_JOB H WHERE H.EMPLID = A.EMPLID AND H.EMPL_RCD = A.EMPL_RCD AND H.EFFDT <= F.PAY_END_DT) AND A.EFFSEQ = ( SELECT MAX(I.EFFSEQ) FROM PS_JOB I WHERE I.EMPLID = A.EMPLID AND I.EMPL_RCD = A.EMPL_RCD AND I.EFFDT = A.EFFDT) AND B.NATIONAL_ID_TYPE = 'PR' AND B.COUNTRY = 'USA' AND A.EMPL_RCD = 0 AND F.EMPLID = A.EMPLID AND F.EMPL_RCD = A.EMPL_RCD AND F.COMPANY = A.COMPANY AND F.PAYGROUP = A.PAYGROUP AND F.PAY_END_DT = ( SELECT MAX(F1.PAY_END_DT) FROM PS_PI_PARTIC F1 WHERE F1.EMPLID = F.EMPLID AND F1.EMPL_RCD = F.EMPL_RCD AND F1.COMPANY = F.COMPANY AND F1.PAYGROUP = F.PAYGROUP) AND F.PI_RUN_NUM = ( SELECT MAX(F2.PI_RUN_NUM) FROM PS_PI_PARTIC F2 WHERE F2.EMPLID = F.EMPLID AND F2.EMPL_RCD = F.EMPL_RCD AND F2.COMPANY = F.COMPANY AND F2.PAYGROUP = F.PAYGROUP AND F2.PAY_END_DT = F.PAY_END_DT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD_NUM Number(3,0) SMALLINT NOT NULL Employee Record Number
5 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
6 NAME Character(50) VARCHAR2(50) NOT NULL Name
7 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
8 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name