AV_P_ACTN_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 , A.EMPLID , A.NAME , C.OPRID , D.CNST_ACTN_NO , C.INSTITUTION , E.COUNTRY , F.NATIONAL_ID_TYPE , F.DESCRSHORT , E.NATIONAL_ID , E.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_AV_CNST_ACTN D , PS_PERS_NID_MSK_VW E , PS_NID_TYPE_TBL F WHERE A.OPRCLASS = E.OPRCLASS AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE -------------------------------------------------------------------------------------- 06/23/99 Jitendra Gupta - Removed the Distinct criteria and the Names table from the view. SELECT A.EMPLID, A.NAME, C.OPRID, D.CNST_ACTN_NO, C.INSTITUTION, E.COUNTRY, F.NATIONAL_ID_TYPE, F.DESCRSHORT, E.NATIONAL_ID, 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_AV_CNST_ACTN D, PS_PERS_NID E, PS_NID_TYPE_TBL F WHERE A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE _________________________________________________ 04/02/99 Curt DeJean - Removed the check for max Effdt SELECT DISTINCT A.NAME, A.EMPLID, C.OPRID, D.CNST_ACTN_NO, C.INSTITUTION, E.COUNTRY, F.NATIONAL_ID_TYPE, F.DESCRSHORT, E.NATIONAL_ID, 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_AV_CNST_ACTN D, PS_PERS_NID E, PS_NID_TYPE_TBL F WHERE A.EMPLID=B.EMPLID AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.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_P_ACTN_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 , A.EMPLID , A.NAME , C.OPRID , D.CNST_ACTN_NO , C.INSTITUTION , E.COUNTRY , F.NATIONAL_ID_TYPE , F.DESCRSHORT , E.NATIONAL_ID , E.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_AV_CNST_ACTN D , PS_PERS_NID_MSK_VW E , PS_NID_TYPE_TBL F WHERE A.OPRCLASS = E.OPRCLASS AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE -------------------------------------------------------------------------------------- 06/23/99 Jitendra Gupta - Removed the Distinct criteria and the Names table from the view. SELECT A.EMPLID, A.NAME, C.OPRID, D.CNST_ACTN_NO, C.INSTITUTION, E.COUNTRY, F.NATIONAL_ID_TYPE, F.DESCRSHORT, E.NATIONAL_ID, 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_AV_CNST_ACTN D, PS_PERS_NID E, PS_NID_TYPE_TBL F WHERE A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE _________________________________________________ 04/02/99 Curt DeJean - Removed the check for max Effdt SELECT DISTINCT A.NAME, A.EMPLID, C.OPRID, D.CNST_ACTN_NO, C.INSTITUTION, E.COUNTRY, F.NATIONAL_ID_TYPE, F.DESCRSHORT, E.NATIONAL_ID, 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_AV_CNST_ACTN D, PS_PERS_NID E, PS_NID_TYPE_TBL F WHERE A.EMPLID=B.EMPLID AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.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.CNST_ACTN_NO , D.ACTN_DESCR , E.COUNTRY , E.NATIONAL_ID_TYPE , E.NID_DESCRSHORT , E.NATIONAL_ID , E.NATIONAL_ID_MSK , A.BIRTHDATE , A.BIRTHDATE_MSK , F.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_AV_CNST_ACTN D , PS_PERS_NID_MSK_VW E , PS_HCR_PER_PDE_I F WHERE A.EMPLID = B.EMPLID AND A.OPRCLASS = E.OPRCLASS AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND B.EMPLID = F.EMPLID AND %EffdtCheck( HCR_PER_PDE_I F1, F, %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: AV_CNST_ACTN_VW

5 NAME Character(50) VARCHAR2(50) NOT NULL Name

6 CNST_ACTN_NO Character(10) VARCHAR2(10) NOT NULL Action Nbr

7 ACTN_DESCR Character(100) VARCHAR2(100) NOT NULL Action Description

8 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country

9 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type

10 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description

11 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID

12 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID

13 BIRTHDATE Date(10) DATE Date of Birth

14 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth

15 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec

16 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID

17 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

18 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: AV_CNST_ACTN_VW
5	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
6	CNST_ACTN_NO	Character(10)	VARCHAR2(10) NOT NULL	Action Nbr
7	ACTN_DESCR	Character(100)	VARCHAR2(100) NOT NULL	Action Description
8	NID_COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	National ID Country
9	NATIONAL_ID_TYPE	Character(6)	VARCHAR2(6) NOT NULL	National ID Type
10	NID_DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	NID Short Description
11	NATIONAL_ID	Character(20)	VARCHAR2(20) NOT NULL	National ID
12	NATIONAL_ID_MSK	Character(20)	VARCHAR2(20) NOT NULL	National ID
13	BIRTHDATE	Date(10)	DATE	Date of Birth
14	BIRTHDATE_MSK	Character(10)	VARCHAR2(10) NOT NULL	Date of Birth
15	SEX	Character(1)	VARCHAR2(1) NOT NULL	Gender F=Female M=Male U=Unknown X=Indeterminate/Intersex/Unspec
16	CAMPUS_ID	Character(16)	VARCHAR2(16) NOT NULL	Campus ID
17	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
18	FIRST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	First Name