PY_IN_YE_FMS_VW (SQL View)	Index Back
Insights Year End Forms View Insights Year End Forms View. Used in Insights visualization.

PY_IN_YE_FMS_VW

(SQL View)

Insights Year End Forms View

Insights Year End Forms View. Used in Insights visualization.

SELECT DISTINCT A.EMPLID ,A.PY_TAX_YEAR ,A.W2_REPORTING_CO ,A.TAXFORM_ID ,A.SEQUENCE_NUMBER ,A.W2C_BATCH_NBR ,A.W2C_BATCH_YR ,A.PY_YE_FORM_SEQNUM ,' ' ,' ' ,' ' ,B.W2_STATE ,B1.COMPANY||' - '||B1.DESCR ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = A.TAXFORM_ID AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) ,A.PRINT_TYPE ,%CurrentDateTimeIn FROM PS_PY_SS_USA_GDE A ,PS_YE_DATA B ,PS_W2_COMPANY W ,PS_COMPANY_TBL B1 WHERE A.EMPLID=B.EMPLID AND A.TAXFORM_ID=B.TAXFORM_ID AND A.TAXFORM_ID IN ('W','I','M','P','S','X','U','N','J' ) AND A.PY_TAX_YEAR=B.CALENDAR_YEAR AND A.SEQUENCE_NUMBER=B.SEQUENCE_NUMBER AND A.W2_REPORTING_CO=W.W2_REPORTING_CO AND B.COMPANY=W.COMPANY AND B.CALENDAR_YEAR=W.CALENDAR_YEAR AND B.W2_STATE<>' ' AND B1.COMPANY=A.W2_REPORTING_CO AND %EffdtCheck(COMPANY_TBL B2, B1, %CurrentDateIn) UNION SELECT DISTINCT A.EMPLID ,A.PY_TAX_YEAR ,A.W2_REPORTING_CO ,A.TAXFORM_ID ,A.SEQUENCE_NUMBER ,A.W2C_BATCH_NBR ,A.W2C_BATCH_YR ,A.PY_YE_FORM_SEQNUM ,' ' ,' ' ,' ' ,B.W2_STATE , B1.COMPANY||' - '||B1.DESCR ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = A.TAXFORM_ID AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) ,A.PRINT_TYPE ,%CurrentDateTimeIn FROM PS_PY_SS_USA_GDE A ,PS_YE_DATA B ,PS_W2_COMPANY W ,PS_COMPANY_TBL B1 WHERE A.EMPLID=B.EMPLID AND A.TAXFORM_ID=B.TAXFORM_ID AND A.TAXFORM_ID IN ('W','I','M','P','S','X','U','N','J' ) AND A.PY_TAX_YEAR=B.CALENDAR_YEAR AND A.SEQUENCE_NUMBER=B.SEQUENCE_NUMBER AND A.W2_REPORTING_CO=W.W2_REPORTING_CO AND B.COMPANY=W.COMPANY AND B.CALENDAR_YEAR=W.CALENDAR_YEAR AND A.SEQUENCE_NUMBER=1 AND B.W2_STATE =' ' AND B1.COMPANY=A.W2_REPORTING_CO AND %EffdtCheck(COMPANY_TBL B2, B1, %CurrentDateIn) AND NOT EXISTS ( SELECT C.EMPLID FROM PS_YE_DATA C , PS_W2_COMPANY W1 WHERE A.EMPLID=C.EMPLID AND A.PY_TAX_YEAR=C.CALENDAR_YEAR AND A.TAXFORM_ID=C.TAXFORM_ID AND C.COMPANY=W1.COMPANY AND A.W2_REPORTING_CO=W1.W2_REPORTING_CO AND C.CALENDAR_YEAR=W.CALENDAR_YEAR AND C.W2_STATE<>' ' ) UNION SELECT DISTINCT A.EMPLID ,A.CALENDAR_YEAR ,W.W2_REPORTING_CO ,A.TAXFORM_ID ,A.SEQUENCE_NUMBER , 0 , 0 , 0 , ' ' ,' ' ,' ' ,A.W2_STATE , B1.COMPANY||' - '||B1.DESCR ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = A.TAXFORM_ID AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) ,'P' ,%CurrentDateTimeIn FROM PS_YE_DATA A ,PS_COMPANY_TBL B1 ,PS_W2_COMPANY W WHERE A.COMPANY=W.COMPANY AND B1.COMPANY=W.W2_REPORTING_CO AND A.CALENDAR_YEAR=W.CALENDAR_YEAR AND A.TAXFORM_ID='P' AND %EffdtCheck(COMPANY_TBL B2, B1, %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 PY_TAX_YEAR Number(4,0) SMALLINT NOT NULL Represents the year for personal income tax purposes.

3 W2_REPORTING_CO Character(3) VARCHAR2(3) NOT NULL W-2 Reporting Company

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 W2C_BATCH_NBR Number(4,0) SMALLINT NOT NULL W-2c Batch Number

7 W2C_BATCH_YR Number(4,0) SMALLINT NOT NULL W-2c Batch Year

8 PY_YE_FORM_SEQNUM Number(2,0) SMALLINT NOT NULL A sequential number to indicate the number of W2/W-2c that have been issued/generated for an employee. Initial copy will have a value of 1. Used in Self Service Year-End Forms.

9 IRS_INCOME_CD_1042 Character(2) VARCHAR2(2) NOT NULL This field is to store the income code associated with the tax 1042 earnings. This income code is required on 1042S form.

10 DISTRIB_CODE Character(2) VARCHAR2(2) NOT NULL Distribution Code

11 COMPANY_1099R Character(3) VARCHAR2(3) NOT NULL 1099R Company

12 STATE Character(6) VARCHAR2(6) NOT NULL State

13 PY_W2_CMP_DESCR Character(254) VARCHAR2(254) NOT NULL W-2 company description

14 TAXFORM_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Taxform ID Description

15 PRINT_TYPE Character(1) VARCHAR2(1) NOT NULL Forms Print Type
O=Self-Service Only
P=Paper

16 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.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	PY_TAX_YEAR	Number(4,0)	SMALLINT NOT NULL	Represents the year for personal income tax purposes.
3	W2_REPORTING_CO	Character(3)	VARCHAR2(3) NOT NULL	W-2 Reporting Company
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	W2C_BATCH_NBR	Number(4,0)	SMALLINT NOT NULL	W-2c Batch Number
7	W2C_BATCH_YR	Number(4,0)	SMALLINT NOT NULL	W-2c Batch Year
8	PY_YE_FORM_SEQNUM	Number(2,0)	SMALLINT NOT NULL	A sequential number to indicate the number of W2/W-2c that have been issued/generated for an employee. Initial copy will have a value of 1. Used in Self Service Year-End Forms.
9	IRS_INCOME_CD_1042	Character(2)	VARCHAR2(2) NOT NULL	This field is to store the income code associated with the tax 1042 earnings. This income code is required on 1042S form.
10	DISTRIB_CODE	Character(2)	VARCHAR2(2) NOT NULL	Distribution Code
11	COMPANY_1099R	Character(3)	VARCHAR2(3) NOT NULL	1099R Company
12	STATE	Character(6)	VARCHAR2(6) NOT NULL	State
13	PY_W2_CMP_DESCR	Character(254)	VARCHAR2(254) NOT NULL	W-2 company description
14	TAXFORM_ID_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Taxform ID Description
15	PRINT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Forms Print Type O=Self-Service Only P=Paper
16	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.