COMPANYPAY_END_DTPAY_BEGIN_DTDefault Value: %date
GVT_ANNU_DTL_VW(SQL View) |
Index Back |
|---|---|
View Re-Employed Annuitants |
| SELECT DISTINCT A.COMPANY , CAL.PAY_END_DT , CAL.PAY_BEGIN_DT , A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.GVT_RETIRE_PLAN , A.GVT_ANNUITY_OFFSET , A.DEPTID , B.NAME , B.BIRTHDATE , C.HIRE_DT , C.REHIRE_DT , C.TERMINATION_DT , D.NATIONAL_ID , E.REG_PAY_HRS , E.OFF_CYCLE , X.XLATSHORTNAME FROM PS_GVT_JOB A , PS_GVT_PERS_DATA B , PS_GVT_NM_DT_VW N , PS_GVT_EMPLOYMENT C , PS_GVT_PERS_NID D , PS_PAY_EARNINGS E , PS_PAY_CALENDAR CAL , PSXLATITEM X WHERE A.GVT_ANN_IND IN ('1', '4', '5', 'A', 'C', 'E') AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_GVT_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.GVT_ANN_IND IN ('1', '4', '5', 'A', 'C', 'E') AND A1.EFFDT <= CAL.PAY_END_DT) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_GVT_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.GVT_ANN_IND IN ('1', '4', '5', 'A', 'C', 'E') AND A2.EFFDT = A.EFFDT) AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT = A.EFFDT AND B.EFFSEQ = A.EFFSEQ AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT AND C.EFFSEQ = A.EFFSEQ AND D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD AND D.EFFDT = A.EFFDT AND D.EFFSEQ = A.EFFSEQ AND E.EMPLID = A.EMPLID AND E.COMPANY = A.COMPANY AND E.PAYGROUP = A.PAYGROUP AND E.REG_PAY_HRS <> 0 AND X.FIELDNAME = 'GVT_WORK_SCHED' AND X.EFF_STATUS = 'A' AND X.FIELDVALUE = A.GVT_WORK_SCHED AND %EffdtCheck(PSXLATITEM, X, A.EFFDT) AND CAL.COMPANY = A.COMPANY AND CAL.PAY_END_DT = E.PAY_END_DT AND N.EMPLID = B.EMPLID AND N.EMPL_RCD = B.EMPL_RCD AND N.EFFDT = B.EFFDT AND N.EFFSEQ = B.EFFSEQ |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | COMPANY |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 2 | PAY_END_DT |
Date(10) | DATE NOT NULL | Pay Period End Date |
| 3 | PAY_BEGIN_DT |
Date(10) | DATE NOT NULL | Pay Period Begin Date |
| 4 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 5 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 6 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 7 | EFFSEQ | Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 8 | GVT_RETIRE_PLAN | Character(2) | VARCHAR2(2) NOT NULL | Retirement Plan |
| 9 | GVT_ANNUITY_OFFSET | Number(8,0) | INTEGER NOT NULL | CSRS Annuity Offset Amount |
| 10 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 11 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 12 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 13 | HIRE_DT | Date(10) | DATE | First Start Date |
| 14 | REHIRE_DT | Date(10) | DATE | Rehire Date |
| 15 | TERMINATION_DT | Date(10) | DATE | Termination Date |
| 16 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 17 | REG_PAY_HRS | Signed Number(8,2) | DECIMAL(6,2) NOT NULL | Regular Hours Input |
| 18 | OFF_CYCLE | Character(1) | VARCHAR2(1) NOT NULL | Off Cycle ? |
| 19 | GVT_DUTY_STATUS | Character(10) | VARCHAR2(10) NOT NULL | Duty Status |