SEV_PRG_SP_SRCH

(SQL View)
 Index Back

SEVIS Program Sponsor Srch

SEV_PRG_SP_SRCH is a security view used by several SEVIS components to allow users to access only records from the DS2019 Form that contain the Program Sponsor that they are cleared to see.

SELECT B.OPRCLASS , A.EMPLID , A.SEV_PRG_NBR , C.OPRID , A.EFFDT , B.NAME , B.SEX , B.BIRTHDATE , B.BIRTHDATE_MSK , B.CAMPUS_ID , E.NATIONAL_ID , E.NATIONAL_ID_MSK , E.COUNTRY , E.NATIONAL_ID_TYPE , E.NID_DESCRSHORT , B.LAST_NAME_SRCH , B.FIRST_NAME_SRCH , A.INSTITUTION , A.ACAD_CAREER FROM PS_DS2019_FORM A , PS_PEOPLE_SRCH B , PS_SEV_PRG_SP_SCTY C , PS_PERS_NID_MSK_VW E WHERE B.OPRCLASS = E.OPRCLASS AND C.ACCESS_CD = 'Y' AND A.EMPLID = B.EMPLID AND (C.SEV_PRG_NBR = A.SEV_PRG_NBR OR C.SEV_PRG_NBR = 'ALL') AND A.EMPLID = E.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 SEV_PRG_NBR Character(9) VARCHAR2(9) NOT NULL SEVIS Program Number

Default Value: OPR_DEF_TBL_CS.SEV_PRG_NBR

Prompt Table: SEV_PRG_SCTY_PR

4 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
5 EFFDT Date(10) DATE Effective Date

Default Value: %date

6 NAME Character(50) VARCHAR2(50) NOT NULL Name
7 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
8 BIRTHDATE Date(10) DATE Date of Birth
9 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
10 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
11 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
12 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
13 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country
14 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
15 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description
16 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
17 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
18 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
19 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine