GM_PERSONAL4_VW (SQL View)	Index Back
Grants Personal Data View 1 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.

GM_PERSONAL4_VW

(SQL View)

Grants Personal Data View 1

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 , A.EFFDT , A.UPDATE_PERS_DATA , A.ADDRESS1_AC , A.ADDRESS2_AC , A.ADDRESS3_AC , A.CITY_AC , A.MAR_STATUS , A.SMOKER , A.NAME FROM PS_PERS_DATA_EFFDT A ,PS_JOB B WHERE A.EMPLID = B.EMPLID AND B.EMPL_STATUS NOT IN ('T','D','I') AND B.EMPL_RCD= ( SELECT MIN(R.EMPL_RCD) FROM PS_JOB R WHERE R.EMPLID= B.EMPLID AND R.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_JOB H WHERE H.EMPLID = B.EMPLID AND H.EMPL_RCD = R.EMPL_RCD AND H.EFFDT <= %CurrentDateIn) AND R.EFFSEQ = ( SELECT MAX(Q.EFFSEQ) FROM PS_JOB Q WHERE Q.EMPLID = B.EMPLID AND Q.EMPL_RCD = R.EMPL_RCD AND Q.EFFDT = R.EFFDT) AND R.EMPL_STATUS NOT IN ('T','D','I') ) AND B.EFFDT =( SELECT MAX(H1.EFFDT) FROM PS_JOB H1 WHERE H1.EMPLID = B.EMPLID AND H1.EMPL_RCD = B.EMPL_RCD AND H1.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(Q1.EFFSEQ) FROM PS_JOB Q1 WHERE Q1.EMPLID = B.EMPLID AND Q1.EMPL_RCD = B.EMPL_RCD AND Q1.EFFDT = B.EFFDT) AND A.EFFDT=( SELECT MAX(T.EFFDT) FROM PS_PERS_DATA_EFFDT T WHERE T.EMPLID=A.EMPLID AND T.EFFDT<=A.EFFDT)

# 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 UPDATE_PERS_DATA Character(1) VARCHAR2(1) NOT NULL Update Personal Data

4 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1

5 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2

6 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3

7 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City

8 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

9 SMOKER Character(1) VARCHAR2(1) NOT NULL Smoker

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

#	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	UPDATE_PERS_DATA	Character(1)	VARCHAR2(1) NOT NULL	Update Personal Data
4	ADDRESS1_AC	Character(55)	VARCHAR2(55) NOT NULL	Alternate Character Address 1
5	ADDRESS2_AC	Character(55)	VARCHAR2(55) NOT NULL	Alternate Character Address 2
6	ADDRESS3_AC	Character(55)	VARCHAR2(55) NOT NULL	Alternate Character Address 3
7	CITY_AC	Character(30)	VARCHAR2(30) NOT NULL	Alternate Character City
8	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
9	SMOKER	Character(1)	VARCHAR2(1) NOT NULL	Smoker
10	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name