GPUS_YE_EE5_VW

(SQL View)
 Index Back

Year End Employee Data View

Year End Employee Data View

SELECT DISTINCT A.EMPLID ,A.GPUS_CAL_YEAR ,A.GPUS_TAXFORM_ID ,A.GPUS_YE_RPT_CO ,A.GPUS_TAX_EIN ,B.GPUS_SEQ_NBR ,A.GPUS_YE_STATUS ,B.SSN ,B.GPUS_SPOUSE_SSN ,B.GPUS_SWT_EIN ,B.GPUS_YE_STATE ,B.GPUS_YE_FNAME ,B.GPUS_YE_MNAME ,B.GPUS_YE_LNAME ,B.GPUS_YE_SUFFIX ,%subrec(ADDRESS_SBR,B) ,B.GPUS_STATUTORYFLAG ,B.GPUS_PENSION_FLAG ,B.GPUS_3SICKPAY_FLAG ,B.GPUS_MARITAL_STAT ,B.GPUS_CONTROL_NBR FROM PS_GPUS_YE_EE0_VW A ,PS_GPUS_YE_DAT B ,PS_GPUS_YE_CO_EIN C WHERE A.EMPLID = B.EMPLID AND A.GPUS_CAL_YEAR = B.GPUS_CAL_YEAR AND A.GPUS_TAXFORM_ID = B.GPUS_TAXFORM_ID AND A.GPUS_YE_RPT_CO = C.GPUS_YE_RPT_CO AND B.GPUS_TAX_EIN = C.GPUS_TAX_EIN AND (C.EFFDT <= %DateIn(%NumToChar(B.GPUS_CAL_YEAR) %Concat '-12-31') AND C.EFFDT >= %DateIn(%NumToChar(B.GPUS_CAL_YEAR) %Concat '-01-01'))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: GP_PERS_SRCH

2 GPUS_CAL_YEAR Number(4,0) SMALLINT NOT NULL Calendar Year
3 GPUS_TAXFORM_ID Character(3) VARCHAR2(3) NOT NULL Tax Form ID
10=W-2
110=W-2c
120=W-2cAS
130=W-2cGU
140=W-2cPR
150=W-2cVI
20=W-2AS
30=W-2GU
40=W-2PR
50=W-2VI
4 GPUS_YE_RPT_CO Character(3) VARCHAR2(3) NOT NULL Year-End Reporting Company

Prompt Table: GPUS_RPT_CO_VW

5 GPUS_TAX_EIN Character(9) VARCHAR2(9) NOT NULL Federal Employer Identification Number
6 GPUS_SEQ_NBR Number(3,0) SMALLINT NOT NULL Sequesnce Number
7 GPUS_YE_STATUS Character(2) VARCHAR2(2) NOT NULL Year-End Status Flag
10=Complete
20=Void
30=W-2c Issued
8 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
9 GPUS_SPOUSE_SSN Character(9) VARCHAR2(9) NOT NULL Spouse Social Security Number
10 GPUS_SWT_EIN Character(20) VARCHAR2(20) NOT NULL Employer Identification Number (SWT)
11 GPUS_YE_STATE Character(2) VARCHAR2(2) NOT NULL State
12 GPUS_YE_FNAME Character(15) VARCHAR2(15) NOT NULL First Name
13 GPUS_YE_MNAME Character(15) VARCHAR2(15) NOT NULL Middle Name
14 GPUS_YE_LNAME Character(20) VARCHAR2(20) NOT NULL Last Name
15 GPUS_YE_SUFFIX Character(4) VARCHAR2(4) NOT NULL Suffix
16 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: SCC_CNT_ADFMTVW

17 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
18 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
19 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
20 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
21 CITY Character(30) VARCHAR2(30) NOT NULL City
22 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
23 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
24 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
25 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
26 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
27 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
28 COUNTY Character(30) VARCHAR2(30) NOT NULL County
29 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

30 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
31 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
32 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

33 GPUS_STATUTORYFLAG Character(1) VARCHAR2(1) NOT NULL Statutory Employee

Y/N Table Edit

34 GPUS_PENSION_FLAG Character(1) VARCHAR2(1) NOT NULL Pension Flag

Y/N Table Edit

35 GPUS_3SICKPAY_FLAG Character(1) VARCHAR2(1) NOT NULL Third Party Sick Pay Flag

Y/N Table Edit

36 GPUS_MARITAL_STAT Character(2) VARCHAR2(2) NOT NULL Marital Status
10=Single
20=Married
37 GPUS_CONTROL_NBR Character(12) VARCHAR2(12) NOT NULL Control Number