GM_PERS_SRCH_VW(SQL View) |
Index Back |
|---|---|
Grants Personal Data ViewPERSONAL_DATA is one of the core records in the database. Use it to record personal and demographic data about an employee or applicant including name, address, birthdate, and ethnic group. View of Personal Data because we can only have one related display from a record for each panel. This view created to include Display Name in search related to preferred name enhancement. |
| SELECT A.EMPLID ,A.EFFDT ,B.BIRTHDATE ,B.SEX ,%subrec(ADDRESS_SBR,A) ,%subrec(ADDR_OTR_SBR,A) ,A.MAR_STATUS ,A.SMOKER ,CASE WHEN ( SELECT 'Y' FROM PSRECDEFN WHERE RECNAME = 'RS_PREF_WRK') = 'Y' AND ( SELECT 'Y' FROM PS_RS_INSTALLATION WHERE RS_PREFERRED_SW = 'Y') = 'Y' AND A.KNOWN_NAME <> ' ' THEN A.KNOWN_NAME ELSE A.NAME END ,A.NAME_DISPLAY ,A.LAST_NAME_SRCH FROM PS_PERS_DATA_EFFDT A ,PS_PERSONAL_DATA B WHERE A.EMPLID=B.EMPLID AND A.EFFDT=( SELECT MAX(E.EFFDT) FROM PS_PERS_DATA_EFFDT E WHERE E.EMPLID=A.EMPLID AND E.EFFDT<=%CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EFFDT |
Date(10) | DATE | Effective Date |
| 3 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 4 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown |
| 5 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: COUNTRY_TBL |
| 6 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 7 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 8 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 9 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 10 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 11 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
| 12 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
| 13 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat WW=Trailer |
| 14 | ADDR_FIELD1 | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 |
| 15 | ADDR_FIELD2 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 |
| 16 | ADDR_FIELD3 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 |
| 17 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 18 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: %EDIT_STATE |
| 19 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 20 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
| 21 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL |
In City Limit
Y/N Table Edit |
| 22 | COUNTRY_OTHER | Character(3) | VARCHAR2(3) NOT NULL |
Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT
Prompt Table: COUNTRY_TBL |
| 23 | ADDRESS1_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 1 Other |
| 24 | ADDRESS2_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 2 Other |
| 25 | ADDRESS3_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 3 Other |
| 26 | ADDRESS4_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 4 Other |
| 27 | CITY_OTHER | Character(30) | VARCHAR2(30) NOT NULL | City Other |
| 28 | COUNTY_OTHER | Character(30) | VARCHAR2(30) NOT NULL | County Other |
| 29 | STATE_OTHER | Character(6) | VARCHAR2(6) NOT NULL |
State Other
Prompt Table: %EDIT_STATE_OTH |
| 30 | POSTAL_OTHER | Character(12) | VARCHAR2(12) NOT NULL | Postal Other |
| 31 | NUM1_OTHER | Character(6) | VARCHAR2(6) NOT NULL | Number 1 Other |
| 32 | NUM2_OTHER | Character(6) | VARCHAR2(6) NOT NULL | Number 2 Other |
| 33 | HOUSE_TYPE_OTHER | Character(2) | VARCHAR2(2) NOT NULL |
House Type Other
AB=House Boat Reference WW=House Trailer Reference |
| 34 | ADDR_FIELD1_OTHER | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 Other |
| 35 | ADDR_FIELD2_OTHER | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 Other |
| 36 | ADDR_FIELD3_OTHER | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 Other |
| 37 | IN_CITY_LMT_OTHER | Character(1) | VARCHAR2(1) NOT NULL | In City Limit Other |
| 38 | GEO_CODE_OTHER | Character(11) | VARCHAR2(11) NOT NULL | Geo Code Other |
| 39 | MAR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Marital Status
C=Common-Law D=Divorced E=Separated H=Head of Household M=Married S=Single U=Unknown W=Widowed |
| 40 | SMOKER | Character(1) | VARCHAR2(1) NOT NULL | Smoker |
| 41 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 42 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name |
| 43 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |