AV_INST_P_SRCH3

(SQL View)
Index Back

Advancement People Search View

Common view used when searching for/selecting a person for display in 360 services. This view goes against the names table as well as the personal data table and accumulator table.

SELECT A.OPRCLASS , C.OPRID , C.INSTITUTION , A.EMPLID , B.NAME , D.COUNTRY , D.NATIONAL_ID_TYPE , D.NID_DESCRSHORT , D.NATIONAL_ID , D.NATIONAL_ID_MSK , A.BIRTHDATE , A.BIRTHDATE_MSK , E.SEX , A.CAMPUS_ID , B.LAST_NAME_SRCH , B.FIRST_NAME_SRCH FROM PS_PERSON_MSK_VW A , PS_HCR_PERSON_NM_I B , PS_SCRTY_TBL_INST C , PS_PERS_NID_MSK_VW D , PS_HCR_PER_PDE_I E , PS_AV_CNST_ACC F WHERE A.EMPLID = B.EMPLID AND A.OPRCLASS = D.OPRCLASS AND A.EMPLID = D.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND B.EMPLID = E.EMPLID AND F.EMPLID = A.EMPLID AND F.INSTITUTION = C.INSTITUTION AND %EffdtCheck( HCR_PER_PDE_I E1, E, %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
3 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Institution

Default Value: AV_OPR_DEF_TBL.INSTITUTION

Prompt Table: INSTITUTN_SCRTY

4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

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