EMPLIDC=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
F=Female
M=Male
U=Unknown
GPMY_CP8A_HDRVW(SQL View) |
Index Back |
|---|---|
Header recordUsed to retrieve the personal details while viewing CP8A |
| SELECT J.EMPLID ,J.COMPANY ,NAME , DESCR , ADDRESS1 , ADDRESS2 , ADDRESS3 ,CITY ,STATE ,POSTAL ,A.COUNTRY ,MAR_STATUS ,SEX ,BIRTHDATE ,NATIONAL_ID ,GPMY_EMPL_TAX_NBR ,E.HIRE_DT ,E.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_EMPLOYMENT E ,PS_NAMES 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 <= %CurrentDateIn) 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 <= %CurrentDateIn) AND A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_ADDRESSES A1 WHERE A1.EMPLID = A.EMPLID AND A1.ADDRESS_TYPE = A.ADDRESS_TYPE AND A1.EFFDT <= %CurrentDateIn) 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 <= %CurrentDateIn) 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<=%CurrentDateIn) AND NAM.EMPLID=J.EMPLID AND NAM.NAME_TYPE='PRI' AND NAM.EFFDT=( SELECT MAX(EFFDT) FROM PS_NAMES WHERE EMPLID=NAM.EMPLID AND EFFDT<=%CurrentDateIn) AND A.ADDRESS_TYPE='HOME' |
| # | 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 |