PY_IC_W4_FTX_VW

(SQL View)
Index Back

Self Service W4- FTaxDataView

View 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)

  • Parent record: PERSON
  • # 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