GVT_IRR_FIN_VW9(SQL View) |
Index Back |
|---|---|
Current Yr LWOP |
| SELECT Z.EMPLID, Z.EMPL_RCD, Z.GVT_IRR_TYPE, Z.EFFDT, Z.EFFSEQ, PC.GVT_PAY_YEAR, H.GVT_IRR_RET_FORM, SUM(C.OTH_HRS) FROM PS_PAY_CALENDAR PC, PS_PAY_CHECK A, PS_PAY_EARNINGS B, PS_PAY_OTH_EARNS C, PS_EARNINGS_TBL F, PS_GVT_JOB G, PS_RTRMNT_PLAN_TBL H, PS_GVT_IRR_CONTROL Z WHERE PC.COMPANY = A.COMPANY AND PC.PAYGROUP = A.PAYGROUP AND PC.PAY_END_DT = A.PAY_END_DT AND A.PAY_END_DT <= Z.EFFDT AND A.COMPANY=B.COMPANY AND A.PAYGROUP=B.PAYGROUP AND A.PAY_END_DT=B.PAY_END_DT AND A.OFF_CYCLE=B.OFF_CYCLE AND A.PAGE_NUM=B.PAGE_NUM AND A.LINE_NUM=B.LINE_NUM AND A.SEPCHK=B.SEPCHK AND B.OK_TO_PAY='Y' AND B.COMPANY=C.COMPANY AND B.PAYGROUP=C.PAYGROUP AND B.PAY_END_DT=C.PAY_END_DT AND B.OFF_CYCLE=C.OFF_CYCLE AND B.PAGE_NUM=C.PAGE_NUM AND B.LINE_NUM=C.LINE_NUM AND B.SEPCHK=C.SEPCHK AND B.ADDL_NBR = C.ADDL_NBR AND C.ERNCD = F.ERNCD AND F.EFFDT = (SELECT MAX(F1.EFFDT) FROM PS_EARNINGS_TBL F1 WHERE F1.ERNCD = C.ERNCD AND F1.EFFDT <= B.PAY_END_DT) AND F.GVT_IRR_LWOP = 'Y' AND A.EMPLID=G.EMPLID AND A.EMPL_RCD=G.EMPL_RCD AND G.EFFDT=(SELECT MAX(G1.EFFDT) FROM PS_GVT_JOB G1 WHERE G1.EMPLID=A.EMPLID AND G1.EMPL_RCD=A.EMPL_RCD AND G1.EFFDT <= A.PAY_END_DT) AND G.EFFSEQ=(SELECT MAX(G2.EFFSEQ) FROM PS_GVT_JOB G2 WHERE G2.EMPLID=A.EMPLID AND G2.EMPL_RCD=A.EMPL_RCD AND G2.EFFDT = G.EFFDT) AND H.PLAN_TYPE='70' AND H.BENEFIT_PLAN=G.GVT_RETIRE_PLAN AND H.EFFDT=(SELECT MAX(H1.EFFDT) FROM PS_RTRMNT_PLAN_TBL H1 WHERE H1.BENEFIT_PLAN = G.GVT_RETIRE_PLAN AND H1.PLAN_TYPE = '70' AND H1.EFFDT <= G.EFFDT) AND A.EMPLID=Z.EMPLID AND A.EMPL_RCD=Z.EMPL_RCD GROUP BY Z.EMPLID, Z.EMPL_RCD, Z.GVT_IRR_TYPE, Z.EFFDT, Z.EFFSEQ, PC.GVT_PAY_YEAR, H.GVT_IRR_RET_FORM |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | GVT_IRR_TYPE |
Character(1) | VARCHAR2(1) NOT NULL |
IRR Type based on NOAC
A=Final Disability C=Correction D=Death M=Military Deposit N=Not Applicable P=Preliminary Disability R=Retirement S=Supplemental T=Transfer/Resignation |
| 4 | EFFDT |
Date(10) | DATE | Effective Date |
| 5 | EFFSEQ |
Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 6 | GVT_YEAR |
Number(4,0) | SMALLINT NOT NULL | Year |
| 7 | GVT_IRR_RET_FORM | Character(1) | VARCHAR2(1) NOT NULL |
IRR Retirement Form Number
C=CSRS (SF2806/SF2807) F=FERS (SF3100/SF3103) N=Not Applicable |
| 8 | HOURS_PER_YEAR | Number(7,2) | DECIMAL(6,2) NOT NULL | Hours per Year |