GPMY_CP22A_HDVW

(SQL View)
 Index Back

Header record

Used to retrieve the personal details while viewing CP22A

SELECT J.EMPLID ,J.COMPANY ,NAM.NAME , DESCR , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 ,A.CITY ,A.STATE ,A.POSTAL ,A.COUNTRY ,PER.MAR_STATUS ,PER.SEX ,X.BIRTHDATE ,NID.NATIONAL_ID ,GPMY_EMPL_TAX_NBR ,J.HIRE_DT ,J.TERMINATION_DT FROM PS_JOBCODE_TBL JC , PS_JOB J ,PS_ADDRESSES A ,PS_PERS_DATA_EFFDT PER ,PS_PERS_NID NID ,PS_GPMY_TAX_PYE TAX ,PS_PERSON X ,PS_GPMY_CP22A_HDR E ,PS_PERSON_NAME NAM WHERE NID.EMPLID=J.EMPLID AND X.EMPLID=J.EMPLID AND E.EMPLID = J.EMPLID AND TAX.EMPLID=J.EMPLID AND TAX.EFFDT=( SELECT MAX(TAX1.EFFDT) FROM PS_GPMY_TAX_PYE TAX1 WHERE TAX1.EMPLID = TAX.EMPLID AND TAX1.EFFDT <= E.CHECK_DT) AND NID.COUNTRY='MYS' AND A.EMPLID=J.EMPLID AND PER.EMPLID=J.EMPLID AND PER.EFFDT=( SELECT MAX(PER1.EFFDT) FROM PS_PERS_DATA_EFFDT PER1 WHERE PER1.EMPLID = PER.EMPLID AND PER1.EFFDT <= E.CHECK_DT) AND A.ADDRESS_TYPE = 'HOME' AND A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_ADDRESSES A1 WHERE A1.EMPLID = A.EMPLID AND A1.EFFDT <= E.CHECK_DT AND A1.ADDRESS_TYPE = A.ADDRESS_TYPE) AND J.EFFDT = ( SELECT MAX( J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <= E.CHECK_DT) AND J.EFFSEQ = ( SELECT MAX( J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JC2 WHERE JC2.SETID=JC.SETID AND JC2.JOBCODE=JC.JOBCODE AND JC2.EFFDT<=E.CHECK_DT) AND NAM.EMPLID=J.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
5 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
6 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
7 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
8 CITY Character(30) VARCHAR2(30) NOT NULL City
9 STATE Character(6) VARCHAR2(6) NOT NULL State
10 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
12 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed
13 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
14 BIRTHDATE Date(10) DATE Date of Birth
15 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
16 GPMY_EMPL_TAX_NBR Character(16) VARCHAR2(16) NOT NULL Tax Reference Number
17 HIRE_DT Date(10) DATE First Start Date
18 TERMINATION_DT Date(10) DATE Termination Date