SAD_CRM_FLT_BAS

(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.COUNTRY_NM_FORMAT , A.NAME , A.NAME_INITIALS , A.NAME_PREFIX , A.NAME_SUFFIX , A.NAME_ROYAL_PREFIX , A.NAME_ROYAL_SUFFIX , A.NAME_TITLE , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH , A.LAST_NAME , A.FIRST_NAME , A.MIDDLE_NAME , A.SECOND_LAST_SRCH , A.SECOND_LAST_NAME , A.NAME_AC , A.PREF_FIRST_NAME , A.COUNTRY , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.ADDRESS4 , A.CITY , A.NUM1 , A.NUM2 , A.HOUSE_TYPE , A.ADDR_FIELD1 , A.ADDR_FIELD2 , A.ADDR_FIELD3 , A.COUNTY , A.STATE , A.POSTAL , A.GEO_CODE , A.IN_CITY_LIMIT , A.COUNTRY_OTHER , A.ADDRESS1_OTHER , A.ADDRESS2_OTHER , A.ADDRESS3_OTHER , A.ADDRESS4_OTHER , A.CITY_OTHER , A.COUNTY_OTHER , A.STATE_OTHER , A.POSTAL_OTHER , A.NUM1_OTHER , A.NUM2_OTHER , A.HOUSE_TYPE_OTHER , A.ADDR_FIELD1_OTHER , A.ADDR_FIELD2_OTHER , A.ADDR_FIELD3_OTHER , A.IN_CITY_LMT_OTHER , A.GEO_CODE_OTHER , A.COUNTRY_CODE , A.PHONE , A.SEX , A.MAR_STATUS , A.BIRTHDATE , A.BIRTHPLACE , A.BIRTHCOUNTRY , A.BIRTHSTATE , A.DT_OF_DEATH , A.HIGHEST_EDUC_LVL , A.FT_STUDENT , A.CAMPUS_ID , A.DEATH_CERTIF_NBR , A.FERPA , A.PLACE_OF_DEATH FROM PS_PERSONAL_DATA A , PS_PERS_DATA_EFFDT B ,PS_SAD_CRM_EMPLIDS C WHERE A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_PERS_DATA_EFFDT B1 WHERE B1.EMPLID = B.EMPLID AND B1.EFFDT <= %CurrentDateIn) AND A.EMPLID = C.EMPLID

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

Default Value: NEW

2 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
5 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
6 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix
7 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix
8 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Suffix
9 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Title
10 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
11 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
12 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
13 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
14 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
15 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
16 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
17 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
18 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
19 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
20 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
21 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
22 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
23 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
24 CITY Character(30) VARCHAR2(30) NOT NULL City
25 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
26 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
27 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
28 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
29 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
30 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
31 COUNTY Character(30) VARCHAR2(30) NOT NULL County
32 STATE Character(6) VARCHAR2(6) NOT NULL State
33 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
34 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
35 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
36 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT
37 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
38 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
39 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
40 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
41 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
42 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
43 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other
44 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
45 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
46 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
47 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
AB=House Boat Reference
WW=House Trailer Reference
48 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
49 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
50 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
51 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other
52 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
53 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
54 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
55 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec

Default Value: U

56 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed

Default Value: S

57 BIRTHDATE Date(10) DATE Date of Birth
58 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
59 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country

Prompt Table: COUNTRY_TBL

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

Prompt Table: BIRTHSTATE_VW

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

Default Value: A

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

Y/N Table Edit

Default Value: N

64 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
65 DEATH_CERTIF_NBR Character(10) VARCHAR2(10) NOT NULL Death Certificate Nbr
66 FERPA Character(1) VARCHAR2(1) NOT NULL FERPA

Y/N Table Edit

Default Value: N

67 PLACE_OF_DEATH Character(30) VARCHAR2(30) NOT NULL Place of Death