PY_IN_YE_AMT_VW

(SQL View)
 Index Back

Insights W-2 Amounts View

Insights W-2 Year End Amounts View

SELECT A.COMPANY ,A.EMPLID ,A.CALENDAR_YEAR ,A.TAXFORM_ID ,A.SEQUENCE_NUMBER ,A.BOX ,A.STATE ,A.LOCALITY ,A.WORK_PSD_CD ,A.RES_PSD_CD ,A.W2_AMOUNT ,B.W2_REPORTING_CO ,B.W2_REPORTING_CO||' - '||( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = B.W2_REPORTING_CO AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,K.EMPL_STATUS ,K.HR_STATUS ,K.PAYGROUP ,K.DEPTID ,K.BUSINESS_UNIT ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = A.TAXFORM_ID AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)),%CurrentDateTimeIn FROM PS_YE_AMOUNTS A LEFT OUTER JOIN PS_W2_COMPANY B ON A.COMPANY=B.COMPANY AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND B.TAXFORM_ID='W', PS_JOB K WHERE K.EMPLID=A.EMPLID AND K.COMPANY=A.COMPANY AND K.EMPL_RCD=( SELECT MAX(AA1.EMPL_RCD) FROM PS_JOB AA1 WHERE K.EMPLID=AA1.EMPLID AND K.COMPANY=AA1.COMPANY AND AA1.EFFDT<= %DateIn((%NumtoChar(A.CALENDAR_YEAR) %Concat '-12-31')) ) AND K.EFFDT=( SELECT MAX(A12.EFFDT) FROM PS_JOB A12 WHERE K.EMPLID=A12.EMPLID AND K.EMPL_RCD=A12.EMPL_RCD AND K.COMPANY=A12.COMPANY AND A12.EFFDT<= %DateIn((%NumtoChar(A.CALENDAR_YEAR) %Concat '-12-31')) ) AND K.EffSeq = ( SELECT MAX(D.EffSeq) FROM PS_Job D WHERE D.EmplID = K.EmplID AND D.EMPL_RCD=K.EMPL_RCD AND D.COMPANY=K.COMPANY AND D.EffDt = K.EffDt)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 PY_TAX_YEAR Number(4,0) SMALLINT NOT NULL Represents the year for personal income tax purposes.
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
Y=1099-R
Z=1042-S
5 SEQUENCE_NUMBER Number(2,0) SMALLINT NOT NULL Sequence Number
6 BOX Character(4) VARCHAR2(4) NOT NULL Box
7 STATE Character(6) VARCHAR2(6) NOT NULL State
8 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
9 WORK_PSD_CD Character(6) VARCHAR2(6) NOT NULL Locality
10 RES_PSD_CD Character(6) VARCHAR2(6) NOT NULL Locality
11 W2_AMOUNT Signed Number(15,2) DECIMAL(13,2) NOT NULL W-2 Amount
12 W2_REPORTING_CO Character(3) VARCHAR2(3) NOT NULL W-2 Reporting Company
13 PY_W2_CMP_DESCR Character(254) VARCHAR2(254) NOT NULL W-2 company description
14 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
15 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
16 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
17 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
18 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
19 TAXFORM_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Taxform ID Description
20 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.