RS_BUS_CRD_VW

(SQL View)
 Index Back

RS_BUS_CRD_VW

View to get Resource\EMPLID information to be used for Business Card

SELECT A.EMPLID , E.EMPLOYEE_PHOTO , A.FIRST_NAME %Concat ' ' %Concat A.LAST_NAME , B.RS_PHONE , C.EMAIL_ADDR , J.BUSINESS_UNIT , J.LOCATION , J.JOBCODE , J.DEPTID , J.STD_HOURS , J.SUPERVISOR_ID FROM PS_PERSONAL_DATA A , PS_RS_EMPL_EMAIL_V C , PS_RS_EMPL_PHONE_V B RIGHT OUTER JOIN PS_JOB J ON J.EMPLID = B.EMPLID LEFT OUTER JOIN PS_EMPL_PHOTO E ON J.EMPLID = E.EMPLID AND E.PHOTO_SIZENAME = 'PAGE' WHERE A.EMPLID = C.EMPLID AND A.EMPLID = J.EMPLID AND J.EMPL_RCD = ( SELECT MIN(EMPL_RCD) FROM PS_JOB J1 WHERE J.EMPLID = J1.EMPLID) AND J.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_JOB H WHERE J.EMPLID = H.EMPLID AND J.EMPL_RCD = H.EMPL_RCD AND H.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(I.EFFSEQ) FROM PS_JOB I WHERE J.EMPLID = I.EMPLID AND J.EMPL_RCD = I.EMPL_RCD AND J.EFFDT = I.EFFDT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPLOYEE_PHOTO Image / Attachment(256) VARCHAR2(256) Employee Photograph
3 PERSON_NAME Character(100) VARCHAR2(100) NOT NULL Name
4 RS_PHONE Character(24) VARCHAR2(24) NOT NULL Phone number.
5 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
7 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
10 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
11 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID