GVT_IRR_FIN_VW6 (SQL View)	Index Back
IRR Basic Pay-Cur Yr Sal Reg Basic Pay for EE's during periods where CSRS or FERS coverage was not in effect. This view looks at Salaried Reg earnings only.

GVT_IRR_FIN_VW6

(SQL View)

IRR Basic Pay-Cur Yr Sal Reg

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

SELECT Z.EMPLID, Z.EMPL_RCD, Z.GVT_IRR_TYPE, Z.EFFDT, Z.EFFSEQ, PC.GVT_PAY_YEAR, SUM(B.REG_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_REG_EARNS = E.ERNCD AND E.EFFDT = (SELECT MAX(E1.EFFDT) FROM PS_EARNINGS_TBL E1 WHERE E1.ERNCD = B.ERNCD_REG_EARNS 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.

#	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.