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_CP21_HDRVW(SQL View) |
Index Back |
|---|---|
Header RecordUsed to retrieve the personal details while viewing CP21 |
| SELECT J.EMPLID , NAME , DESCR , J.COMPANY , MAR_STATUS , SEX , BIRTHDATE , GPMY_EMPL_TAX_NBR , E.HIRE_DT , E.TERMINATION_DT FROM PS_JOBCODE_TBL JC , PS_JOB J , PS_PERS_DATA_EFFDT PER , PS_GPMY_TAX_PYE TAX , PS_PERSON X , PS_EMPLOYMENT E , PS_NAMES NAM WHERE 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 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 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) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 3 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 4 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 5 | 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 |
| 6 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown |
| 7 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 8 | GPMY_EMPL_TAX_NBR | Character(16) | VARCHAR2(16) NOT NULL | Tax Reference Number |
| 9 | HIRE_DT | Date(10) | DATE | First Start Date |
| 10 | TERMINATION_DT | Date(10) | DATE | Termination Date |