GVT_IRR_FIN_VW2(SQL View) |
Index Back |
|---|---|
Current Year Retirement Deduct |
| SELECT Z.EMPLID , Z.EMPL_RCD , Z.GVT_IRR_TYPE , Z.EFFDT , Z.EFFSEQ , A.GVT_PAY_YEAR , E.GVT_IRR_RET_FORM , SUM(C.DED_CUR) , MAX(E.EMPL_PCT) FROM PS_PAY_CALENDAR A , PS_PAY_CHECK B , PS_PAY_DEDUCTION C , PS_DEDUCTION_TBL D , PS_RTRMNT_PLAN_TBL E , PS_GVT_IRR_CONTROL Z WHERE B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT AND B.PAY_END_DT <= Z.EFFDT AND C.COMPANY = B.COMPANY AND C.PAYGROUP = B.PAYGROUP AND C.PAY_END_DT = B.PAY_END_DT AND C.PAGE_NUM = B.PAGE_NUM AND C.LINE_NUM = B.LINE_NUM AND C.SEPCHK = B.SEPCHK AND C.PLAN_TYPE = '70' AND C.DED_CLASS <= 'K' AND D.PLAN_TYPE = C.PLAN_TYPE AND D.DEDCD = C.DEDCD AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEDUCTION_TBL D1 WHERE D1.PLAN_TYPE=D.PLAN_TYPE AND D1.DEDCD = D.DEDCD AND D1.EFFDT <= C.PAY_END_DT) AND (D.GVT_REPORTING_CD = 'CS' OR D.GVT_REPORTING_CD = 'CO' OR D.GVT_REPORTING_CD = 'FE' OR D.GVT_REPORTING_CD = 'FH' OR D.GVT_REPORTING_CD = 'FO' OR D.GVT_REPORTING_CD = 'FR' OR D.GVT_REPORTING_CD = 'FS' OR D.GVT_REPORTING_CD = 'PR' OR D.GVT_REPORTING_CD = 'RE' OR D.GVT_REPORTING_CD = 'RR' OR D.GVT_REPORTING_CD = 'RS' OR D.GVT_REPORTING_CD = 'XE' OR D.GVT_REPORTING_CD = 'XR' OR D.GVT_REPORTING_CD = 'XS' ) AND C.PLAN_TYPE = E.PLAN_TYPE AND C.BENEFIT_PLAN = E.BENEFIT_PLAN AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_RTRMNT_PLAN_TBL E1 WHERE E1.PLAN_TYPE = C.PLAN_TYPE AND E1.BENEFIT_PLAN = C.BENEFIT_PLAN AND E1.EFFDT <= C.PAY_END_DT) AND B.EMPLID=Z.EMPLID AND B.EMPL_RCD=Z.EMPL_RCD GROUP BY Z.EMPLID, Z.EMPL_RCD, Z.GVT_IRR_TYPE,Z.EFFDT,Z.EFFSEQ,A.GVT_PAY_YEAR, E.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_PAY_YEAR |
Number(4,0) | SMALLINT NOT NULL | Pay 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 | AMOUNT | Signed Number(20,3) | DECIMAL(18,3) NOT NULL | Commuting Allowance Paid Per Cycle. |
| 9 | EMPL_PCT | Number(7,3) | DECIMAL(6,3) NOT NULL | Employee Contribution Percent |