SAD_CRM_FLT_PER

(SQL View)
 Index Back

Personal Data Filter

This view is used to filter personal data that will be published to CRM.

SELECT A.EMPLID , A.BIRTHPLACE , A.BIRTHCOUNTRY , A.BIRTHSTATE , A.DT_OF_DEATH , C.HIGHEST_EDUC_LVL , C.FT_STUDENT , 'Z' , D.MILITARY_STATUS , D.CITIZEN_PROOF1 , D.CITIZEN_PROOF2 , B.CAMPUS_ID , B.DEATH_CERTIF_NBR , B.FERPA , B.PLACE_OF_DEATH FROM PS_PERSON A LEFT OUTER JOIN PS_PERS_DATA_US_VW D ON A.EMPLID = D.EMPLID , PS_PERSON_SA B , PS_PERS_DATA_EFFDT C WHERE A.EMPLID= B.EMPLID AND A.EMPLID = C.EMPLID AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_PERS_DATA_EFFDT C1 WHERE C.EMPLID = C1.EMPLID AND C1.EFFDT <=%CurrentDateIn) AND A.EMPLID IN ( SELECT EMPLID FROM PS_SAD_CRM_EMPLIDS)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Default Value: NEW

2 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
3 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country

Prompt Table: COUNTRY_TBL

4 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State

Prompt Table: BIRTHSTATE_VW

5 DT_OF_DEATH Date(10) DATE Date of Death
6 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level

Default Value: A

7 FT_STUDENT Character(1) VARCHAR2(1) NOT NULL Full-Time Student

Y/N Table Edit

Default Value: N

8 PER_TYPE Character(1) VARCHAR2(1) NOT NULL Person Type
C=Cobra Participant
D=GP Dependent/Beneficiary
E=Leased Worker
H=Prehire
I=Independent Contractor
L=Time & Labor Contractor
M=Temporary Worker
N=Non HR Administered Employee
O=Other Non-Employee
P=Pension Payee
S=Stock Non-Employee
T=Training Non-Employee
U=Succession Candidate Non-Emplo
9 MILITARY_STATUS Character(1) VARCHAR2(1) NOT NULL Veteran Status / Military Status
1=Not indicated
2=No Military Service
3=Veteran of the Vietnam Era
4=Active Duty/Campaign Badge Vet
4=Other Protected Veteran
5=Active Reserve
6=Inactive Reserve
7=Retired Military
8=Veteran (VA Ineligible)
9=Vietnam & Active Duty Vet
9=Vietnam & Other Protected Vet
A=Armed Forces Service Medal Vet
B=Pre-Vietnam-Era Veteran
C=Separated/Srvc Medal/Active
C=Separated/Srvc Medal/Other Vet
D=Protected, Not Classified
N=Not a Vietnam-Era Veteran
O=Service Medal & Active Duty
O=Service Medal & Other Vet
P=Post-Vietnam-Era Veteran
R=Recently Separated Veteran
S=Separated & Service Medal Vet
T=Separated & Active Duty Vet
T=Separated & Other Vet
V=Vietnam-Era Veteran
X=Not a Veteran
X=Not a Veteran
Z=Not a Protected Veteran
10 CITIZEN_PROOF1 Character(10) VARCHAR2(10) NOT NULL Citizenship (Proof 1)
11 CITIZEN_PROOF2 Character(10) VARCHAR2(10) NOT NULL Citizenship (Proof 2)
12 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
13 DEATH_CERTIF_NBR Character(10) VARCHAR2(10) NOT NULL Death Certificate Nbr
14 FERPA Character(1) VARCHAR2(1) NOT NULL FERPA
15 PLACE_OF_DEATH Character(30) VARCHAR2(30) NOT NULL Place of Death