RE_CAFM_EMP

(SQL View)
 Index Back

Full Employee SYNC MSG record

This view is the basis record for the full SYNC to the CAFM.

SELECT A.EMPLID , A.NAME_PREFIX , A.FIRST_NAME , A.MIDDLE_NAME , A.LAST_NAME , A.BUSINESS_TITLE , A.HIRE_DT , A.TERMINATION_DT , A.BUSINESS_UNIT , A.DEPTID , A.SETID_DEPT , A.LOCATION , A.EMPL_STATUS , A.EMPL_CLASS , A.EMPL_TYPE , %Coalesce(B.EMAIL_ADDR, ' ') AS EMAIL_ADDR , %Coalesce(C.RS_PHONE, ' ') AS RS_PHONE , %Coalesce(D.RS_PHONE, ' ') AS PHONE_WORK , %Coalesce(E.ADDRESS1, ' ') AS ADDRESS1 , %Coalesce(E.ADDRESS2, ' ') AS ADDRESS2 , %Coalesce(E.ADDRESS2, ' ') AS ADDRESS3 , %Coalesce(E.ADDRESS2, ' ') AS ADDRESS4 , %Coalesce(E.STATE, ' ') AS STATE , %Coalesce(E.CITY, ' ') AS CITY , %Coalesce(E.POSTAL, ' ') AS POSTAL , %Coalesce(E.COUNTRY, ' ') AS COUNTRY FROM %Table(RE_CAFM_EMP_STG) A , PS_RE_EMP_EMAIL B , PS_RE_EMP_PHN_H C , PS_RE_EMP_PHN_W D , PS_RE_EMP_JOB_LOC E WHERE A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EMPLID = D.EMPLID AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.LOCATION = E.LOCATION

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
1=Mr.
2=Mrs.
3=Ms.
3 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
4 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL Middle Name
5 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
6 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
7 HIRE_DT Date(10) DATE Hire Date
8 TERMINATION_DT Date(10) DATE Termination Date
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
11 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID

Prompt Table: SETID_TBL

12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
13 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee Status
A=Active
D=Deceased
I=Inactive
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
14 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification
A=Assignee
C=Consultant
E=Expatriate
G=Agency/Temp
I=Intern
P=Apprentice
R=Contractor
T=Trainee
15 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
16 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address
17 RS_PHONE Character(24) VARCHAR2(24) NOT NULL Phone number.
18 PHONE_WORK Character(12) VARCHAR2(12) NOT NULL Telephone
19 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
20 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
21 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
22 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
23 STATE Character(6) VARCHAR2(6) NOT NULL State
24 CITY Character(30) VARCHAR2(30) NOT NULL City
25 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
26 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country