PY_IC_W4_FTX_VW(SQL View) |
Index Back |
---|---|
Self Service W4- FTaxDataViewView to support Self Service W4 Update. This view contains the employee's current W4 information. It is based on FED_TAX_DATA. Changes made to the record FED_TAX_DATA would need to be analyzed and made to PY_IC_W4_FTX_VW, if necessary. |
SELECT DISTINCT A.EMPLID , A.COMPANY , A.EFFDT , A.SPECIAL_FWT_STATUS , A.FWT_MAR_STATUS , A.FWT_ALLOWANCES , A.FWT_ADDL_AMT , A.FWT_ADDL_PCT , A.FUT_EXEMPT , A.EIC_STATUS , A.STATUTORY_EE , A.DECEASED , A.PENSION_PLAN , A.LEGAL_REP , A.DEFERRED_COMP , A.USE_TOTAL_WAGE , A.LAST_ACTION , A.W4_PRCSS , A.W5_PRCSS , A.LOCK_IN_RECVD , A.LOCK_IN_LIMIT , A.LAST_UPDATE_DATE , A.DATE_OF_ENTRY , A.COUNTRY , A.FORM_1001_RECD , A.FORM_1001_SUBMT_DT , A.FORM_1001_EXP_DT , A.FORM_8233_RECD , A.FORM_8233_SUBMT_DT , A.FORM_8233_EXP_DT , A.TAXPAYER_ID_NO , A.TREATY_EXP_DT , A.TREATY_ID , A.FORM_1078_RECD , A.FORM_1078_SUBMT_DT , A.THIRDPARTY_SICKPAY , A.W4_NAME_CHECK , A.PY_FLAG_FOR_IRS , A.PY_FLAG_FOR_IRS_DT , A.CREATION_DT , A.MARRIED_FILE_SINGL , A.NRA_EXEMPT_WHRULE , A.NQDC_W2_RPTG , A.FWT_FORM_VERSION , A.FWT_MULTI_JOBS , A.FWT_TTL_DEP_AMT , A.FWT_OTH_INCOME , A.FWT_DED_ADJ, A.FWT_PENSION_INCOME, A.FWT_PAYMENT_TYPE, A.PY_PERCENT FROM PS_FED_TAX_DATA A , PS_JOB B WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_FED_TAX_DATA A1 WHERE A1.EMPLID = A.EMPLID AND A1.COMPANY = A.COMPANY AND A1.EFFDT <= %CurrentDateIn) AND B.EMPLID=A.EMPLID AND B.COMPANY = A.COMPANY AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.COMPANY = B.COMPANY AND B1.EFFDT <= %CurrentDateIn) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
2 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
3 | EFFDT | Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
4 | SPECIAL_FWT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Special FWT Status
A=Nonresident alien E=No taxable gross; no tax taken G=Maintain taxable gross N=None Default Value: N |
5 | FWT_MAR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
FWT Marital Status
H=Head of Household M=Married S=Single X=Exempt |
6 | FWT_ALLOWANCES | Number(3,0) | SMALLINT NOT NULL | FWT Allowances |
7 | FWT_ADDL_AMT | Number(8,2) | DECIMAL(7,2) NOT NULL | FWT Additional Amount |
8 | FWT_ADDL_PCT | Number(6,3) | DECIMAL(5,3) NOT NULL | FWT Additional Percentage |
9 | FUT_EXEMPT | Character(1) | VARCHAR2(1) NOT NULL |
Exempt from FUT
Y/N Table Edit Default Value: COMPANY_TBL.FUT_EXEMPT |
10 | EIC_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Earned Income Credit Status
M=Married, both spouses filing N=Not applicable S=Single,or Head of Household U=Married without spouse filing Default Value: N |
11 | STATUTORY_EE | Character(1) | VARCHAR2(1) NOT NULL |
Statutory Employee
Y/N Table Edit Default Value: N |
12 | DECEASED | Character(1) | VARCHAR2(1) NOT NULL |
Deceased.
Y/N Table Edit Default Value: N |
13 | PENSION_PLAN | Character(1) | VARCHAR2(1) NOT NULL |
Retirement Plan
Y/N Table Edit Default Value: N |
14 | LEGAL_REP | Character(1) | VARCHAR2(1) NOT NULL |
Legal Representative
Y/N Table Edit Default Value: N |
15 | DEFERRED_COMP | Character(1) | VARCHAR2(1) NOT NULL |
Deferred Compensation
Y/N Table Edit Default Value: N |
16 | USE_TOTAL_WAGE | Character(1) | VARCHAR2(1) NOT NULL |
Use Total Wage for Multi-State
Y/N Table Edit Default Value: N |
17 | LAST_ACTION | Character(1) | VARCHAR2(1) NOT NULL |
Last Action
C=System D=Hire Process J=Job Data O=Online User S=Supplier Deduction File W=Employee Self Service User |
18 | W4_PRCSS | Character(1) | VARCHAR2(1) NOT NULL |
W4 Process Flag
N=None S=Notification Sent W=New W-4 Received |
19 | W5_PRCSS | Character(1) | VARCHAR2(1) NOT NULL |
W5 Process Flag
N=None S=Notification Sent W=New W-5 Received |
20 | LOCK_IN_RECVD | Character(1) | VARCHAR2(1) NOT NULL |
Lock-In Letter Received
Default Value: N |
21 | LOCK_IN_LIMIT | Number(3,0) | SMALLINT NOT NULL | Limit On Allowances |
22 | LAST_UPDATE_DATE | Date(10) | DATE | Date of last update |
23 | DATE_OF_ENTRY | Date(10) | DATE | Date of Entry |
24 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
25 | FORM_1001_RECD | Character(1) | VARCHAR2(1) NOT NULL |
Form W8 Recd
N=No X=Not Applicable Y=Yes Default Value: N |
26 | FORM_1001_SUBMT_DT | Date(10) | DATE | W8 In Effect Date |
27 | FORM_1001_EXP_DT | Date(10) | DATE | Form W8 Expiration Date. When this expiration date is reached, the Payroll Calculation will use the "Before Form" tax rate to calculate Non Resident Alien tax. |
28 | FORM_8233_RECD | Character(1) | VARCHAR2(1) NOT NULL |
Form 8233 Recd
N=No X=Not Applicable Y=Yes Default Value: N |
29 | FORM_8233_SUBMT_DT | Date(10) | DATE | Form 8233 Submission Date |
30 | FORM_8233_EXP_DT | Date(10) | DATE | Form 8233 Expiration Date. When this expiration date is reached, the Payroll Calculation will use the "Before Form" tax rate to calculate Non Resident Alien tax. |
31 | TAXPAYER_ID_NO | Character(9) | VARCHAR2(9) NOT NULL | Taxpayer ID Number |
32 | TREATY_EXP_DT | Date(10) | DATE | Treaty Exp Date |
33 | TREATY_ID | Character(10) | VARCHAR2(10) NOT NULL | Treaty ID |
34 | FORM_1078_RECD | Character(1) | VARCHAR2(1) NOT NULL |
Form W9 Received
N=No Y=Yes Default Value: N |
35 | FORM_1078_SUBMT_DT | Date(10) | DATE | W9 Submit Date |
36 | THIRDPARTY_SICKPAY | Character(1) | VARCHAR2(1) NOT NULL |
Third-party sick pay
Y/N Table Edit Default Value: N |
37 | W4_NAME_CHECK | Character(1) | VARCHAR2(1) NOT NULL |
W4 Name Check
Y/N Table Edit Default Value: N |
38 | PY_FLAG_FOR_IRS | Character(1) | VARCHAR2(1) NOT NULL |
W4 Flagged for Transmission
Y/N Table Edit Default Value: N |
39 | PY_FLAG_FOR_IRS_DT | Date(10) | DATE | Flagged for IRS On |
40 | CREATION_DT | Date(10) | DATE | Creation Date |
41 | MARRIED_FILE_SINGL | Character(1) | VARCHAR2(1) NOT NULL |
Married Filing Single
Y/N Table Edit Default Value: N |
42 | NRA_EXEMPT_WHRULE | Character(1) | VARCHAR2(1) NOT NULL |
Exempt indicator for Non Resident Alien employees that witholding rules not applicable.
N=Subject to Rule R=Not Applicable Y=Exempt from Rule Y/N Table Edit Default Value: N |
43 | NQDC_W2_RPTG | Character(1) | VARCHAR2(1) NOT NULL |
Non Qualified prevously Deferred payments from the State are paid in another state.
A=Applies N=Not Applicable Default Value: N |
44 | FWT_FORM_VERSION | Character(1) | VARCHAR2(1) NOT NULL |
2020 W4 - to indicate if the tax data is based on 2019 W4 form or 2020 W4 form.
A=W-4 - 2019 or Earlier B=W-4 - 2020 or Later C=W-4P - 2022 or Later D=W-4R - 2022 or Later |
45 | FWT_MULTI_JOBS | Character(1) | VARCHAR2(1) NOT NULL |
2020 W4 changes - Multiple Jobs Indicator
Y/N Table Edit Default Value: N |
46 | FWT_TTL_DEP_AMT | Number(11,2) | DECIMAL(10,2) NOT NULL | 2020 W4 - Total Dependent Amount |
47 | FWT_OTH_INCOME | Number(11,2) | DECIMAL(10,2) NOT NULL | 2020 W4 - Other Income |
48 | FWT_DED_ADJ | Number(11,2) | DECIMAL(10,2) NOT NULL | 2020 W4 - Deduction Adjustment |
49 | FWT_PENSION_INCOME | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Job/Multiple Pension Income. |
50 | FWT_PAYMENT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Payment Type
A=Nonperiodic payments B=Distribution payments Default Value: A |
51 | PY_PERCENT | Number(6,3) | DECIMAL(5,3) NOT NULL | Percentage |