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

GVT_IRR_FIN_VW7

(SQL View)

IRR Basic Pay-Cur Yr Other Ern

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

SELECT Z.EMPLID, Z.EMPL_RCD, Z.GVT_IRR_TYPE, Z.EFFDT, Z.EFFSEQ, PC.GVT_PAY_YEAR, SUM(C.OTH_EARNS) FROM PS_PAY_CALENDAR PC, PS_PAY_CHECK A, PS_PAY_EARNINGS B, PS_PAY_OTH_EARNS C, PS_GVT_JOB D, PS_RTRMNT_PLAN_TBL E, PS_EARNINGS_TBL F, 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 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 A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = (SELECT MAX(D1.EFFDT) FROM PS_GVT_JOB D1 WHERE D1.EMPLID = A.EMPLID AND D1.EMPL_RCD = A.EMPL_RCD AND D1.EFFDT <= A.PAY_END_DT) AND D.EFFSEQ = (SELECT MAX(D2.EFFSEQ) FROM PS_GVT_JOB D2 WHERE D2.EMPLID = A.EMPLID AND D2.EMPL_RCD = A.EMPL_RCD AND D2.EFFDT = D.EFFDT) AND D.GVT_RETIRE_PLAN = E.BENEFIT_PLAN AND E.PLAN_TYPE = '70' AND E.EFFDT = (SELECT MAX(E1.EFFDT) FROM PS_RTRMNT_PLAN_TBL E1 WHERE E1.BENEFIT_PLAN = D.GVT_RETIRE_PLAN AND E1.PLAN_TYPE = '70' AND E1.EFFDT <= D.EFFDT) AND E.GVT_IRR_RET_FORM <> 'C' AND E.GVT_IRR_RET_FORM <> 'F' 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_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 Rcd Nbr

3 GVT_IRR_TYPE Character(1) VARCHAR2(1) NOT NULL IRR Type based on above 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 Rcd Nbr
3	GVT_IRR_TYPE	Character(1)	VARCHAR2(1) NOT NULL	IRR Type based on above 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.