AV_P_RATNG_SRCH(SQL View) |
Index Back |
|---|---|
Advancement People Search ViewCommon 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 DISTINCT A.OPRCLASS , A.EMPLID , C.OPRID , D.RATING_NO , C.INSTITUTION , B.NAME , D.EFFDT , D.RATING_CAT , D.AV_RATING_TYPE , D.IND_CD , D.AV_VALID_VALUE , H.DESCR , D.INTV_CD , 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_PERS_NID_MSK_VW E , PS_HCR_PER_PDE_I F , PS_AV_PRSN_RATING D LEFT OUTER JOIN PS_AV_R_IND_V_VAL H ON D.IND_CD = H.IND_CD AND D.AV_VALID_VALUE <> ' ' 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) AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_AV_PRSN_RATING D1 WHERE D1.INSTITUTION = D.INSTITUTION AND D1.EMPLID =D.EMPLID AND D1.EXT_ORG_ID = D.EXT_ORG_ID AND D1.RATING_NO = D.RATING_NO AND D1.EFFDT <=%CurrentDateIn) AND D.EFF_STATUS = 'A' AND ((D.AV_VALID_VALUE = ' ' ) OR (H.IND_CD = D.IND_CD AND H.EFFDT <= D.EFFDT AND H.AV_VALID_VALUE = D.AV_VALID_VALUE )) |
| # | 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: AV_RATING_P_VW |
| 3 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 4 | RATING_NO |
Number(10,0) | DECIMAL(10) NOT NULL |
Rating Nbr
Default Value: 0 Prompt Table: AV_RTNG_NO_P_VW |
| 5 | INSTITUTION |
Character(5) | VARCHAR2(5) NOT NULL |
Institution
Default Value: AV_OPR_DEF_TBL.INSTITUTION Prompt Table: INSTITUTN_SCRTY |
| 6 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 7 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 8 | RATING_CAT | Character(6) | VARCHAR2(6) NOT NULL | Rating Category |
| 9 | AV_RATING_TYPE | Character(6) | VARCHAR2(6) NOT NULL | Rating Type |
| 10 | IND_CD | Character(6) | VARCHAR2(6) NOT NULL | Indicator Code |
| 11 | AV_VALID_VALUE | Character(15) | VARCHAR2(15) NOT NULL | Valid Value |
| 12 | AV_VALID_VAL_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Ratings search page indicator valid value description |
| 13 | INTV_CD | Character(10) | VARCHAR2(10) NOT NULL | Initiative Code |
| 14 | NID_COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | National ID Country |
| 15 | NATIONAL_ID_TYPE | Character(6) | VARCHAR2(6) NOT NULL | National ID Type |
| 16 | NID_DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | NID Short Description |
| 17 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 18 | NATIONAL_ID_MSK | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 19 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 20 | BIRTHDATE_MSK | Character(10) | VARCHAR2(10) NOT NULL | Date of Birth |
| 21 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown X=Indeterminate/Intersex/Unspec |
| 22 | CAMPUS_ID | Character(16) | VARCHAR2(16) NOT NULL | Campus ID |
| 23 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 24 | FIRST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | First Name |