AV_INST_P_SRCH (SQL View)	Index Back
Advancement People Search View Common view used when searching for/selecting a person for display/update. This view goes against the names table as well as the personal data table. The PeopleCode contained on this view is based upon the peoplecode in PEOPLE_SRCH. ----------------------------------------------------------------------------------------------------------------- 10/24/2003 8.9 Person Model Before: SELECT A.OPRCLASS , C.OPRID , C.INSTITUTION , A.EMPLID , A.NAME , D.COUNTRY , E.NATIONAL_ID_TYPE , E.DESCRSHORT , D.NATIONAL_ID , D.NATIONAL_ID_MSK , A.BIRTHDATE , A.BIRTHDATE_MSK , A.SEX , A.CAMPUS_ID , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH FROM PS_PERS_DTA_MSK_VW A , PS_SCRTY_TBL_INST C , PS_PERS_NID_MSK_VW D , PS_NID_TYPE_TBL E WHERE A.OPRCLASS = D.OPRCLASS AND A.EMPLID = D.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE ----------------------------------------------------------------------------------------------------------------- Changed the order of the search field to be consistent with other search records in the system - 22 Aug 2000. SELECT A.EMPLID, A.NAME, D.COUNTRY, E.NATIONAL_ID_TYPE, E.DESCRSHORT, D.NATIONAL_ID, C.INSTITUTION, C.OPRID, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, A.LAST_NAME_SRCH, A.FIRST_NAME_SRCH FROM PS_PERSONAL_DATA A, PS_SCRTY_TBL_INST C, PS_PERS_NID D, PS_NID_TYPE_TBL E WHERE A.EMPLID = D.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE ----------------------------------------------------------------------------------------------------------------- 06/23/99 Jitendra Gupta - Removed the distinct criteria and Names Table SELECT A.EMPLID, A.NAME, D.COUNTRY, E.NATIONAL_ID_TYPE, E.DESCRSHORT, D.NATIONAL_ID, C.INSTITUTION, C.OPRID, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, A.LAST_NAME_SRCH, A.FIRST_NAME_SRCH FROM PS_PERSONAL_DATA A, PS_SCRTY_TBL_INST C, PS_PERS_NID D, PS_NID_TYPE_TBL E WHERE A.EMPLID = D.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE ______________________________________________________ 04/02/99 Curt Dejean - Removed the check for max Effdt SELECT DISTINCT A.NAME, A.EMPLID, D.COUNTRY, E.NATIONAL_ID_TYPE, E.DESCRSHORT, D.NATIONAL_ID, C.INSTITUTION, C.OPRID, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, B.LAST_NAME_SRCH, B.FIRST_NAME_SRCH, B.NAME_TYPE FROM PS_PERSONAL_DATA A, PS_NAMES B, PS_SCRTY_TBL_INST C, PS_PERS_NID D, PS_NID_TYPE_TBL E WHERE A.EMPLID = B.EMPLID AND A.EMPLID = D.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE AND B.EFFDT IN (SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID=A.EMPLID AND B1.LAST_NAME_SRCH <> ' ')

AV_INST_P_SRCH

(SQL View)

Advancement People Search View

Common view used when searching for/selecting a person for display/update. This view goes against the names table as well as the personal data table. The PeopleCode contained on this view is based upon the peoplecode in PEOPLE_SRCH. ----------------------------------------------------------------------------------------------------------------- 10/24/2003 8.9 Person Model Before: SELECT A.OPRCLASS , C.OPRID , C.INSTITUTION , A.EMPLID , A.NAME , D.COUNTRY , E.NATIONAL_ID_TYPE , E.DESCRSHORT , D.NATIONAL_ID , D.NATIONAL_ID_MSK , A.BIRTHDATE , A.BIRTHDATE_MSK , A.SEX , A.CAMPUS_ID , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH FROM PS_PERS_DTA_MSK_VW A , PS_SCRTY_TBL_INST C , PS_PERS_NID_MSK_VW D , PS_NID_TYPE_TBL E WHERE A.OPRCLASS = D.OPRCLASS AND A.EMPLID = D.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE ----------------------------------------------------------------------------------------------------------------- Changed the order of the search field to be consistent with other search records in the system - 22 Aug 2000. SELECT A.EMPLID, A.NAME, D.COUNTRY, E.NATIONAL_ID_TYPE, E.DESCRSHORT, D.NATIONAL_ID, C.INSTITUTION, C.OPRID, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, A.LAST_NAME_SRCH, A.FIRST_NAME_SRCH FROM PS_PERSONAL_DATA A, PS_SCRTY_TBL_INST C, PS_PERS_NID D, PS_NID_TYPE_TBL E WHERE A.EMPLID = D.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE ----------------------------------------------------------------------------------------------------------------- 06/23/99 Jitendra Gupta - Removed the distinct criteria and Names Table SELECT A.EMPLID, A.NAME, D.COUNTRY, E.NATIONAL_ID_TYPE, E.DESCRSHORT, D.NATIONAL_ID, C.INSTITUTION, C.OPRID, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, A.LAST_NAME_SRCH, A.FIRST_NAME_SRCH FROM PS_PERSONAL_DATA A, PS_SCRTY_TBL_INST C, PS_PERS_NID D, PS_NID_TYPE_TBL E WHERE A.EMPLID = D.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE ______________________________________________________ 04/02/99 Curt Dejean - Removed the check for max Effdt SELECT DISTINCT A.NAME, A.EMPLID, D.COUNTRY, E.NATIONAL_ID_TYPE, E.DESCRSHORT, D.NATIONAL_ID, C.INSTITUTION, C.OPRID, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, B.LAST_NAME_SRCH, B.FIRST_NAME_SRCH, B.NAME_TYPE FROM PS_PERSONAL_DATA A, PS_NAMES B, PS_SCRTY_TBL_INST C, PS_PERS_NID D, PS_NID_TYPE_TBL E WHERE A.EMPLID = B.EMPLID AND A.EMPLID = D.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.COUNTRY = E.COUNTRY AND D.NATIONAL_ID_TYPE = E.NATIONAL_ID_TYPE AND B.EFFDT IN (SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID=A.EMPLID AND B1.LAST_NAME_SRCH <> ' ')

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 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 %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

#	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