GPES_CRT_COL_VW

(SQL View)
 Index Back

CRT Inquiry Employee VW

Employee View for Creta Inquiry

SELECT D.SSN_EMPLOYER , D.GPES_SSTC_PAYMNT , D.GPES_YEAR , D.GPES_MONTH , D.GPES_MONTH_RECALC , D.GPES_YEAR_RECALC , D.EMPLID , D.EMPL_RCD , D.GPES_CRT_STATUS_EE , D.GPES_CRT_STAT_RSN , D.GPES_CRT_BRK_LQ , D.GPES_REUSE_PRV_BS , A.COMPANY , %Coalesce(MAX(T.GPES_CRT_STAT_VAL), '3') , %Coalesce(MAX(T.GPES_CRT_STAT_TRM), '3') , %Coalesce(MAX(T.GPES_CRT_STAT_VRF), '3') , MAX(T.GPES_CRT_SOURCE) , D.GPES_SSTC_CNTRL_DT FROM PS_GPES_CRT_LQ_EE D LEFT OUTER JOIN PS_GPES_CRT_SS_TRM T ON D.EMPLID = T.EMPLID AND D.EMPL_RCD = T.EMPL_RCD AND D.SSN_EMPLOYER = T.SSN_EMPLOYER AND D.GPES_YEAR = T.GPES_YEAR AND D.GPES_MONTH = T.GPES_MONTH AND D.GPES_SSTC_PAYMNT = T.GPES_SSTC_PAYMNT AND D.GPES_YEAR_RECALC = T.GPES_YEAR_RECALC AND D.GPES_MONTH_RECALC = T.GPES_MONTH_RECALC , PS_GPES_CCC_VW A WHERE A.SSN_EMPLOYER = D.SSN_EMPLOYER AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPES_CCC_VW WHERE SSN_EMPLOYER = D.SSN_EMPLOYER AND EFFDT <= D.SLICE_BGN_DT) AND D.GPES_CRT_DATA_TYPE <> 'Q' AND %Sql(GPES_CRT_MAX_VER_REV_NUM, PS_GP_PYE_PRC_STAT, D) GROUP BY D.SSN_EMPLOYER , D.GPES_SSTC_PAYMNT , D.GPES_YEAR , D.GPES_MONTH , D.GPES_MONTH_RECALC , D.GPES_YEAR_RECALC , D.EMPLID , D.EMPL_RCD , D.GPES_CRT_STATUS_EE , D.GPES_CRT_STAT_RSN , D.GPES_CRT_BRK_LQ , D.GPES_REUSE_PRV_BS , A.COMPANY, D.GPES_SSTC_CNTRL_DT

# 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/Surch. Base Incr.
L91=Retro w/Surch. New Slices
V03=Retro wo/ Surcharge Vacations
V90=Retro w/ Surcharge Vacations
3 GPES_YEAR Number(4,0) SMALLINT NOT NULL Year
4 GPES_MONTH Number(2,0) SMALLINT NOT NULL Month
5 GPES_MONTH_RECALC Number(2,0) SMALLINT NOT NULL Retro month
6 GPES_YEAR_RECALC Number(4,0) SMALLINT NOT NULL Retro year
7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
8 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
9 GPES_CRT_STATUS_EE Character(1) VARCHAR2(1) NOT NULL CRET@ Employee reporting status
0=No Reported
1=Reported
E=With Errors
M=Manually Selected
V=Validated
X=Excluded

Default Value: 0

10 GPES_CRT_STAT_RSN Character(1) VARCHAR2(1) NOT NULL Check Box to select No Reported employees for Creta reporting
A=Data not Needed
N=Negative Bases
P=Reuse Previous Bases
11 GPES_CRT_BRK_LQ Number(9,0) DECIMAL(9) NOT NULL Breaking Point
12 GPES_REUSE_PRV_BS Character(1) VARCHAR2(1) NOT NULL Reuse Previous Month Bases for Creta reporting
13 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
14 GPES_CRT_STAT_VAL Character(1) VARCHAR2(1) NOT NULL CRET@ Social Security calculation verification status.
0=Matched
1=Mismatch
3=Not Verified

Default Value: 3

15 GPES_CRT_STAT_TRM Character(1) VARCHAR2(1) NOT NULL CRET@ Social Security employee slice verification status.
0=Matched
1=Mismatch
3=Not Verified

Default Value: 3

16 GPES_CRT_STAT_VRF Character(1) VARCHAR2(1) NOT NULL CRET@ Social Security employee slice verification status.
0=Matched
1=Mismatch
3=Not Verified
17 GPES_CRT_SOURCE Character(3) VARCHAR2(3) NOT NULL CRET@ source calculation type file
C=Calculation
S=Slices of Employees
18 GPES_SSTC_CNTRL_DT Date(10) DATE Social Security TCs control date