GM_PERS_ADDR_VW

(SQL View)
 Index Back

Grants Personal Data View 3

PERSONAL_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.

SELECT A.EMPLID , %subrec(ADDRESS_SBR,A) , A.ADDRESS1_AC , A.ADDRESS2_AC , A.ADDRESS3_AC , A.CITY_AC , %subrec(ADDR_OTR_SBR,A) , P.STATE_FORMAT , P.COUNTRY_FORMAT FROM PS_PERS_DATA_EFFDT A , PS_GM_STATE_CTRY_V P WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_PERS_DATA_EFFDT A1 WHERE A1.EMPLID = A.EMPLID AND A1.EFFDT <= %CurrentDateIn) AND A.STATE = P.STATE AND A.COUNTRY = P.COUNTRY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

3 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
4 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
5 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
6 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
7 CITY Character(30) VARCHAR2(30) NOT NULL City
8 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
9 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
10 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
11 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
12 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
13 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
14 COUNTY Character(30) VARCHAR2(30) NOT NULL County
15 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

16 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
17 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
18 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

19 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
20 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
21 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
22 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
23 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

Prompt Table: COUNTRY_TBL

24 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
25 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
26 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
27 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
28 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
29 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
30 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

Prompt Table: %EDIT_STATE_OTH

31 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
32 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
33 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
34 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
AB=House Boat Reference
WW=House Trailer Reference
35 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
36 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
37 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
38 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other
39 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
40 STATE_FORMAT Character(40) VARCHAR2(40) NOT NULL STATE FORMAT
41 COUNTRY_FORMAT Character(40) VARCHAR2(40) NOT NULL COUNTRY FORMAT