GPES_SSTCEMP_VW (SQL View)	Index Back
TCs Generation EE and Amount Do not retrive employees with any of BA21, BA22, BA09, BA10 or BA11 less than cero; or, employees which the sum of BA01, BA02, BA21 and BA22 is less than cero.

GPES_SSTCEMP_VW

(SQL View)

TCs Generation EE and Amount

Do not retrive employees with any of BA21, BA22, BA09, BA10 or BA11 less than cero; or, employees which the sum of BA01, BA02, BA21 and BA22 is less than cero.

SELECT A.SSN_EMPLOYER , A.GPES_SSTC_PAYMNT , A.GPES_SSTC_CNTRL_DT , A.GPES_OOD_PYMT_SUR , A.GPES_MONTH , A.GPES_YEAR , A.GPES_YEAR_RECALC , A.GPES_MONTH_RECALC , A.SLICE_BGN_DT , A.EMPLID , A.GPES_SSTC_ELEMENT , SUM(A.GPES_SSTC_AMOUNT) , SUM(A.GPES_SSTC_CT_EE) , SUM(A.GPES_SSTC_CT_ER) FROM PS_GPES_SSTC_RSLT A WHERE NOT EXISTS ( SELECT DISTINCT 'X' FROM PS_GPES_SSTC_RSLT C WHERE C.SSN_EMPLOYER = A.SSN_EMPLOYER AND C.EMPLID = A.EMPLID AND C.GPES_SSTC_PAYMNT = A.GPES_SSTC_PAYMNT AND C.GPES_YEAR = A.GPES_YEAR AND C.GPES_MONTH = A.GPES_MONTH AND C.GPES_YEAR_RECALC = A.GPES_YEAR_RECALC AND C.GPES_MONTH_RECALC = A.GPES_MONTH_RECALC AND %Coalesce(C.GPES_SSTC_CNTRL_DT, %CurrentDateIn) = %Coalesce(A.GPES_SSTC_CNTRL_DT, %CurrentDateIn) AND C.GPES_SSTC_ELEMENT IN('BA01','BA21','BA02','BA22','BA42','BA09','BA10','BA11') GROUP BY SSN_EMPLOYER, GPES_SSTC_PAYMNT, GPES_SSTC_CNTRL_DT, GPES_MONTH, GPES_YEAR, SLICE_BGN_DT, EMPLID, GPES_MONTH_RECALC, GPES_YEAR_RECALC, GPES_SSTC_ELEMENT HAVING SUM(GPES_SSTC_AMOUNT) <0) GROUP BY A.SSN_EMPLOYER, A.GPES_SSTC_PAYMNT, A.GPES_SSTC_CNTRL_DT, A.GPES_OOD_PYMT_SUR, A.GPES_MONTH, A.GPES_YEAR, A.SLICE_BGN_DT, A.EMPLID, A.GPES_MONTH_RECALC, GPES_YEAR_RECALC, GPES_SSTC_ELEMENT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number

2 GPES_SSTC_PAYMNT Character(3) VARCHAR2(3) NOT NULL Payment Type. This field is used to populate the GP Write Array, which is used to generate the FAN File.
L00=Regular
L02=Litigation Salary
L03=Retroactivity wo/Surcharge
L04=Reductions Payback
L09=Retroactivity w/Surcharge
L13=Vacations
L90=Retro w/Surcharge Bases Increa
L91=Retro w/Surchage new Slices
Default Value: L00

3 GPES_SSTC_CNTRL_DT Date(10) DATE Social Security TC�s control date

4 GPES_OOD_PYMT_SUR Number(2,0) SMALLINT NOT NULL FAN surcharge because of an out of date payment

5 GPES_MONTH Number(2,0) SMALLINT NOT NULL Month

6 GPES_YEAR Number(4,0) SMALLINT NOT NULL Year

7 GPES_YEAR_RECALC Number(4,0) SMALLINT NOT NULL Retro year

8 GPES_MONTH_RECALC Number(2,0) SMALLINT NOT NULL Retro month

9 SLICE_BGN_DT Date(10) DATE Element Slice Begin Date

10 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

11 GPES_SSTC_ELEMENT Character(4) VARCHAR2(4) NOT NULL This code indicates if the element represents a base or a deduction. This field is used to populate the GP Write Array, which is used to generate the FAN File.

12 GPES_SSTC_AMOUNT Signed Number(11,2) DECIMAL(9,2) NOT NULL Amount of the element.(Entire part). This field is used to populate the GP Write Array, which is used to generate the FAN File.

13 GPES_SSTC_CT_EE Signed Number(17,2) DECIMAL(15,2) NOT NULL Employee Social Security Contributions. This field is used to populate the GP Write Array, which is used to generate the FAN File.

14 GPES_SSTC_CT_ER Signed Number(15,2) DECIMAL(13,2) NOT NULL Employer Social Security Contributions. This field is used to populate the GP Write Array, which is used to generate the FAN File.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SSN_EMPLOYER	Character(13)	VARCHAR2(13) NOT NULL	Employer Social Security number
2	GPES_SSTC_PAYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Type. This field is used to populate the GP Write Array, which is used to generate the FAN File. L00=Regular L02=Litigation Salary L03=Retroactivity wo/Surcharge L04=Reductions Payback L09=Retroactivity w/Surcharge L13=Vacations L90=Retro w/Surcharge Bases Increa L91=Retro w/Surchage new Slices Default Value: L00
3	GPES_SSTC_CNTRL_DT	Date(10)	DATE	Social Security TC�s control date
4	GPES_OOD_PYMT_SUR	Number(2,0)	SMALLINT NOT NULL	FAN surcharge because of an out of date payment
5	GPES_MONTH	Number(2,0)	SMALLINT NOT NULL	Month
6	GPES_YEAR	Number(4,0)	SMALLINT NOT NULL	Year
7	GPES_YEAR_RECALC	Number(4,0)	SMALLINT NOT NULL	Retro year
8	GPES_MONTH_RECALC	Number(2,0)	SMALLINT NOT NULL	Retro month
9	SLICE_BGN_DT	Date(10)	DATE	Element Slice Begin Date
10	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
11	GPES_SSTC_ELEMENT	Character(4)	VARCHAR2(4) NOT NULL	This code indicates if the element represents a base or a deduction. This field is used to populate the GP Write Array, which is used to generate the FAN File.
12	GPES_SSTC_AMOUNT	Signed Number(11,2)	DECIMAL(9,2) NOT NULL	Amount of the element.(Entire part). This field is used to populate the GP Write Array, which is used to generate the FAN File.
13	GPES_SSTC_CT_EE	Signed Number(17,2)	DECIMAL(15,2) NOT NULL	Employee Social Security Contributions. This field is used to populate the GP Write Array, which is used to generate the FAN File.
14	GPES_SSTC_CT_ER	Signed Number(15,2)	DECIMAL(13,2) NOT NULL	Employer Social Security Contributions. This field is used to populate the GP Write Array, which is used to generate the FAN File.