FED_ST_TAX_VW(SQL View) |
Index Back |
|---|---|
EE U.S. Federal State Tax VwThis record is used to record and maintain federal tax information that the system will use to calculate federal taxes for employees. The tax data is defined by company. |
| SELECT DISTINCT M.EMPLID ,M.EMPL_RCD ,M.EFFDT ,B.COMPANY ,B.EFFDT ,B.SPECIAL_FWT_STATUS ,B.FWT_MAR_STATUS ,B.FWT_ALLOWANCES ,B.FWT_ADDL_AMT ,B.FWT_ADDL_PCT ,B.FWT_FORM_VERSION ,B.FWT_MULTI_JOBS ,B.FWT_TTL_DEP_AMT ,B.FWT_OTH_INCOME ,B.FWT_DED_ADJ ,B.FWT_PENSION_INCOME ,B.FWT_PAYMENT_TYPE ,B.PY_PERCENT ,B.FUT_EXEMPT ,B.EIC_STATUS ,B.STATUTORY_EE ,B.DECEASED ,B.PENSION_PLAN ,B.LEGAL_REP ,B.DEFERRED_COMP ,B.LAST_ACTION ,C.STATE ,C.SPECIAL_SWT_STATUS ,C.SWT_MAR_STATUS ,C.SWT_ALLOWANCES ,C.SWT_ADDL_ALLOWNCES ,C.SWT_ADDL_AMT ,C.SWT_ADDL_PCT ,C.ADDL_AMT_ADJ ,C.ANNL_EXEMPTION_AMT ,C.PERCENT_OF_FWT ,M.STATE ,D.SPECIAL_SWT_STATUS ,D.SWT_MAR_STATUS ,D.SWT_ALLOWANCES ,D.SWT_ADDL_ALLOWNCES ,D.SWT_ADDL_AMT ,D.SWT_ADDL_PCT ,D.ADDL_AMT_ADJ ,D.ANNL_EXEMPTION_AMT ,D.PERCENT_OF_FWT ,D.NON_RES_DECLARED ,D.UI_JURISDICTION ,D.WAGE_PLAN_CD ,D.SDI_STATUS ,D.SUT_EXEMPT ,M.DIST_PCT FROM PS_FED_TAX_DATA B ,PS_STATE_TAX_DATA C ,PS_STATE_TAX_DATA D ,PS_TAX_DISTRIB M WHERE C.EMPLID=B.EMPLID AND C.EFFDT=B.EFFDT AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_STATE_TAX_DATA CC WHERE CC.EMPLID = C.EMPLID) AND C.COMPANY=B.COMPANY AND C.RESIDENT='Y' AND D.EMPLID=B.EMPLID AND D.EFFDT=B.EFFDT AND D.COMPANY=B.COMPANY AND D.STATE IN ( SELECT K.STATE FROM PS_TAX_DISTRIB K WHERE K.EMPLID=D.EMPLID AND K.EFFDT = ( SELECT MAX(KK.EFFDT) FROM PS_TAX_DISTRIB KK WHERE K.EMPLID=KK.EMPLID AND K.EMPL_RCD=KK.EMPL_RCD)) AND M.EMPLID=D.EMPLID AND D.STATE = M.STATE AND M.EFFDT = ( SELECT MAX(MM.EFFDT) FROM PS_TAX_DISTRIB MM WHERE M.EMPLID = MM.EMPLID AND M.EMPL_RCD=MM.EMPL_RCD) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | EFFDT | Date(10) | DATE | Effective Date |
| 4 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 5 | TAX_EFFDT | Date(10) | DATE | Tax Effective Date |
| 6 | 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 |
| 7 | FWT_MAR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
FWT Marital Status
H=Head of Household M=Married S=Single X=Exempt |
| 8 | FWT_ALLOWANCES | Number(3,0) | SMALLINT NOT NULL | FWT Allowances |
| 9 | FWT_ADDL_AMT | Number(8,2) | DECIMAL(7,2) NOT NULL | FWT Additional Amount |
| 10 | FWT_ADDL_PCT | Number(6,3) | DECIMAL(5,3) NOT NULL | FWT Additional Percentage |
| 11 | 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 |
| 12 | FWT_MULTI_JOBS | Character(1) | VARCHAR2(1) NOT NULL | 2020 W4 changes - Multiple Jobs Indicator |
| 13 | FWT_TTL_DEP_AMT | Number(11,2) | DECIMAL(10,2) NOT NULL | 2020 W4 - Total Dependent Amount |
| 14 | FWT_OTH_INCOME | Number(11,2) | DECIMAL(10,2) NOT NULL | 2020 W4 - Other Income |
| 15 | FWT_DED_ADJ | Number(11,2) | DECIMAL(10,2) NOT NULL | 2020 W4 - Deduction Adjustment |
| 16 | FWT_PENSION_INCOME | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Job/Multiple Pension Income. |
| 17 | FWT_PAYMENT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Payment Type
A=Nonperiodic payments B=Distribution payments |
| 18 | PY_PERCENT | Number(6,3) | DECIMAL(5,3) NOT NULL | Percentage |
| 19 | FUT_EXEMPT | Character(1) | VARCHAR2(1) NOT NULL | Exempt from FUT |
| 20 | 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 |
| 21 | STATUTORY_EE | Character(1) | VARCHAR2(1) NOT NULL | Statutory Employee |
| 22 | DECEASED | Character(1) | VARCHAR2(1) NOT NULL | Deceased. |
| 23 | PENSION_PLAN | Character(1) | VARCHAR2(1) NOT NULL | Retirement Plan |
| 24 | LEGAL_REP | Character(1) | VARCHAR2(1) NOT NULL | Legal Representative |
| 25 | DEFERRED_COMP | Character(1) | VARCHAR2(1) NOT NULL | Deferred Compensation |
| 26 | 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 |
| 27 | RES_STATE | Character(6) | VARCHAR2(6) NOT NULL | Resident State |
| 28 | RES_SWT_STATUS | Character(1) | VARCHAR2(1) NOT NULL | Res State Withholding Status |
| 29 | RES_MAR_STATUS | Character(1) | VARCHAR2(1) NOT NULL | Res State Tax Calc Status |
| 30 | RES_ALLOW | Number(3,0) | SMALLINT NOT NULL | Resident State Allowances |
| 31 | RES_ADDL_ALLOW | Number(3,0) | SMALLINT NOT NULL | Res State Addl Allowances |
| 32 | RES_ADDL_AMT | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Res State Addl Tax Amount |
| 33 | RES_ADDL_PCT | Signed Number(7,3) | DECIMAL(5,3) NOT NULL | Res State Addl Tax Percent |
| 34 | RES_ADDL_AMT_ADJ | Character(1) | VARCHAR2(1) NOT NULL | Res State Addl Amt Adjustment |
| 35 | RES_EXEMPT_AMT | Number(5,0) | INTEGER NOT NULL | Res State tax exempt Amt |
| 36 | RES_PCT_FWT | Signed Number(7,3) | DECIMAL(5,3) NOT NULL | Res State Tax Percent of FWT |
| 37 | WRK_STATE | Character(6) | VARCHAR2(6) NOT NULL | Payroll Work State |
| 38 | WRK_SWT_STATUS | Character(1) | VARCHAR2(1) NOT NULL | Work State Taxibility Status |
| 39 | WRK_MAR_STATUS | Character(1) | VARCHAR2(1) NOT NULL | Work State Tax Calc Status |
| 40 | WRK_ALLOW | Number(3,0) | SMALLINT NOT NULL | Work State SWT Allowance |
| 41 | WRK_ADDL_ALLOW | Number(3,0) | SMALLINT NOT NULL | Work State SWT Addl Allow |
| 42 | WRK_ADDL_AMT | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Work State Addl Tax Amount |
| 43 | WRK_ADDL_PCT | Signed Number(7,3) | DECIMAL(5,3) NOT NULL | Work State Addl Tax Pct |
| 44 | WRK_ADDL_AMT_ADJ | Character(1) | VARCHAR2(1) NOT NULL | Wrk State Addl Amt Adjustment |
| 45 | WRK_EXEMPT_AMT | Number(5,0) | INTEGER NOT NULL | Work State Exemption Amount |
| 46 | WRK_PCT_FWT | Signed Number(7,3) | DECIMAL(5,3) NOT NULL | Work State Percent of FWT |
| 47 | NON_RES_DECLARED | Character(1) | VARCHAR2(1) NOT NULL | Non-Residency Statement Filed |
| 48 | UI_JURISDICTION | Character(1) | VARCHAR2(1) NOT NULL | UI Jurisdiction |
| 49 | WAGE_PLAN_CD | Character(1) | VARCHAR2(1) NOT NULL |
Wage Plan Code
A=DI Exempt/UI State J=DI State/UI Exempt L=DI Voluntary/UI Exempt P=DI Exempt/UI Exempt R=DI Special Exempt/UI State S=DI State/UI State U=DI Voluntary/UI State |
| 50 | SDI_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
State Disability Insurance Status
E=Exempt N=Not Applicable V=Voluntary Disability Plan Y=Subject |
| 51 | SUT_EXEMPT | Character(1) | VARCHAR2(1) NOT NULL | Exempt From SUT |
| 52 | DIST_PCT | Number(7,3) | DECIMAL(6,3) NOT NULL | Percent of Distribution |