SEV_MAINT_SRCH

(SQL View)
Index Back

SEVIS ID Maint Search Record

SEV_MAINT_SRCH is a security view used by several SEVIS components to allow users to access only records from the I20 Form that contain the School Codes or Program Sponsor Numbers that they are cleared to see.

SELECT A.OPRCLASS , A.EMPLID , A.NAME , A.SEX , A.BIRTHDATE , A.BIRTHDATE_MSK , A.CAMPUS_ID , B.NATIONAL_ID , B.NATIONAL_ID_MSK , B.COUNTRY , B.NATIONAL_ID_TYPE , B.NID_DESCRSHORT , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH FROM PS_PEOPLE_SRCH A , PS_PERS_NID_MSK_VW B WHERE (EXISTS ( SELECT 'X' FROM PS_DS2019_FORM W WHERE W.EMPLID = A.EMPLID) OR EXISTS ( SELECT 'X' FROM PS_DS2019_DEP X WHERE X.SEV_DEP_EMPLID = A.EMPLID) OR EXISTS ( SELECT 'X' FROM PS_I20_FORM Y WHERE Y.EMPLID = A.EMPLID) OR EXISTS ( SELECT 'X' FROM PS_SEV_I20_DEP Z WHERE Z.SEV_DEP_EMPLID = A.EMPLID)) AND A.OPRCLASS = B.OPRCLASS AND A.EMPLID = B.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
5 BIRTHDATE Date(10) DATE Date of Birth
6 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
7 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
8 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
9 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
10 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country
11 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
12 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description
13 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
14 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name