GVT_IRR_FIN_VW5

(SQL View)
 Index Back

IRR Basic Pay-Cur Yr Hrly OT

Basic Pay for EE's during periods where CSRS or FERS coverage was not in effect. This view looks at Hourly OT earnings only.

SELECT Z.EMPLID, Z.EMPL_RCD, Z.GVT_IRR_TYPE, Z.EFFDT, Z.EFFSEQ, PC.GVT_PAY_YEAR, SUM(B.OT_HRLY_EARNS) FROM PS_PAY_CALENDAR PC, PS_PAY_CHECK A, PS_PAY_EARNINGS B, PS_GVT_JOB C, PS_RTRMNT_PLAN_TBL D, PS_EARNINGS_TBL E, 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.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 A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_GVT_JOB C1 WHERE C1.EMPLID = A.EMPLID AND C1.EMPL_RCD = A.EMPL_RCD AND C1.EFFDT <= A.PAY_END_DT) AND C.EFFSEQ = (SELECT MAX(C2.EFFSEQ) FROM PS_GVT_JOB C2 WHERE C2.EMPLID = A.EMPLID AND C2.EMPL_RCD = A.EMPL_RCD AND C2.EFFDT = C.EFFDT) AND C.GVT_RETIRE_PLAN = D.BENEFIT_PLAN AND D.PLAN_TYPE = '70' AND D.EFFDT = (SELECT MAX(D1.EFFDT) FROM PS_RTRMNT_PLAN_TBL D1 WHERE D1.BENEFIT_PLAN = C.GVT_RETIRE_PLAN AND D1.PLAN_TYPE = '70' AND D1.EFFDT <= C.EFFDT) AND D.GVT_IRR_RET_FORM <> 'C' AND D.GVT_IRR_RET_FORM <> 'F' AND B.ERNCD_OT_HRS = E.ERNCD AND E.EFFDT = (SELECT MAX(E1.EFFDT) FROM PS_EARNINGS_TBL E1 WHERE E1.ERNCD = B.ERNCD_OT_HRS AND E1.EFFDT <= B.PAY_END_DT) AND E.GVT_IRR_REPORTABLE = 'Y' AND A.EMPLID=Z.EMPLID AND A.EMPL_RCD=Z.EMPL_RCD AND A.PAY_END_DT <= Z.EFFDT GROUP BY Z.EMPLID, Z.EMPL_RCD, Z.GVT_IRR_TYPE, Z.EFFDT, Z.EFFSEQ, PC.GVT_PAY_YEAR

# 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 AMOUNT Signed Number(20,3) DECIMAL(18,3) NOT NULL Commuting Allowance Paid Per Cycle.