W2_STATE_LCL_VW

(SQL View)
 Index Back

W-2 State/Local Data View

This view selects state tax data for on-line display of employee W-2 information.

SELECT DISTINCT A.COMPANY ,A.CALENDAR_YEAR ,A.EMPLID ,A.TAXFORM_ID ,A.SEQUENCE_NUMBER ,A.STATE ,A.LOCALITY ,A.W2_AMOUNT ,B.W2_AMOUNT FROM PS_W2_AMOUNTS A ,PS_W2_AMOUNTS B ,PS_TAXFORM_TAX D ,PS_TAXFORM_TAX E WHERE A.COMPANY = B.COMPANY AND A.EMPLID = B.EMPLID AND A.TAXFORM_ID IN ('W', 'C') AND B.TAXFORM_ID = A.TAXFORM_ID AND D.TAXFORM_ID = A.TAXFORM_ID AND E.TAXFORM_ID = A.TAXFORM_ID AND ((A.BOX = D.BOX AND D.AMOUNT_TYPE = 'G') AND (B.BOX = E.BOX AND E.AMOUNT_TYPE = 'T')) AND D.TAX_CLASS = 'H' AND E.TAX_CLASS = D.TAX_CLASS AND A.CALENDAR_YEAR = B.CALENDAR_YEAR AND A.SEQUENCE_NUMBER = B.SEQUENCE_NUMBER AND A.STATE = B.STATE AND A.LOCALITY = B.LOCALITY AND A.STATE <> '$U' AND D.EFFDT = E.EFFDT AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_TAXFORM_TAX Q WHERE Q.TAXFORM_ID = A.TAXFORM_ID AND Q.EFFDT < %DateIn('2001-01-01')) GROUP BY A.COMPANY, A.EMPLID, A.TAXFORM_ID, A.CALENDAR_YEAR, A.SEQUENCE_NUMBER, A.BOX, A.STATE, A.LOCALITY, A.W2_AMOUNT, B.W2_AMOUNT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 CALENDAR_YEAR Number(4,0) SMALLINT NOT NULL Calendar Year
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 TAXFORM_ID Character(1) VARCHAR2(1) NOT NULL Tax Form Identification
A=T4A Slip
B=Business Payrolls Survey - CAN
C=WCB Assessable Earnings
E=Record of Employment
F=940 - Annual Employer's FUTA
I=W-2VI
J=W-2cVI
M=W-2GU
N=W-2cGU
P=W-2PR
Q=W-2cPR
R=RL-1 Slip
S=W-2AS
T=T4 Slip
U=W-2cAS
V=RL-2 Slip
W=W-2
X=W-2c
5 SEQUENCE_NUMBER Number(2,0) SMALLINT NOT NULL Sequence Number
6 STATE Character(6) VARCHAR2(6) NOT NULL State
7 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
8 TXGRS_YTD Signed Number(12,2) DECIMAL(10,2) NOT NULL Taxable Gross YTD
9 TAX_YTD Signed Number(12,2) DECIMAL(10,2) NOT NULL Tax YTD