PY_MOD_TXJLC_VW

(SQL View)
 Index Back

Paycheck Modeling Tax Juris

Paycheck Modeling Employee Local Tax Jurisdiction View

SELECT DISTINCT B.EMPLID , B.COMPANY , 'C' , B.STATE , B.LOCALITY , ' ' , ' ' , ' ' , 0 , 0 , 0 , 0 , 0 , 0 , ' ' , ' ' , ' ' , 0 , ' ' , ' ' , 0 , 0 , 0 , ' ' , ' ' , B.LWT_ALLOWANCES , B.LWT_AMT , B.LWT_PCT , B.SPECIAL_LWT_STATUS , B.LWT_MAR_STATUS , C.LOCALITY_NAME %Concat ', ' %Concat A.DESCR , ' ' , ' ' , B.LWT_MAR_STATUS , B.LWT_ALLOWANCES , ' ' , ' ' , 0 , 0 , 0 , 0 , 0 , ' ' FROM PS_PY_MOD_STATE_VW A , PS_MODEL_LOCAL_TAX B , PS_LOCAL_TAX_TBL C WHERE A.STATE IN ('IN', 'MI', 'NY') AND A.EMPLID = B.EMPLID AND A.COMPANY = B.COMPANY AND A.EFFDT = B.EFFDT AND A.STATE = B.STATE AND B.STATE = C.STATE AND B.LOCALITY = C.LOCALITY AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_LOCAL_TAX_TBL C_ED WHERE C.STATE = C_ED.STATE AND C.LOCALITY = C_ED.LOCALITY AND C_ED.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 PY_MOD_TAX_JRSDCTN Character(1) VARCHAR2(1) NOT NULL Paycheck Modeling Tax Jurisdiction
A=Federal
B=State
C=Local
4 STATE Character(6) VARCHAR2(6) NOT NULL State
5 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
6 RESIDENT Character(1) VARCHAR2(1) NOT NULL Resident

Y/N Table Edit

Default Value: Y

7 SPECIAL_SWT_STATUS Character(1) VARCHAR2(1) NOT NULL Special Tax Status
A=Nonresident alien (Fed Rules)
E=No taxable gross; no tax taken
G=Maintain taxable gross
N=None
8 SWT_MAR_STATUS Character(1) VARCHAR2(1) NOT NULL SWT Marital/Tax Status

Prompt Table: SWT_MARSTAT_TBL

9 SWT_ALLOWANCES Number(3,0) SMALLINT NOT NULL Withholding Allowances
10 SWT_ADDL_ALLOWNCES Number(3,0) SMALLINT NOT NULL Additional Allowances
11 ANNL_EXEMPTION_AMT Number(8,0) INTEGER NOT NULL MS Annual Exemption Amount
12 PERCENT_OF_FWT Number(6,3) DECIMAL(5,3) NOT NULL AZ Withholding Percent
13 SWT_ADDL_AMT Signed Number(9,2) DECIMAL(7,2) NOT NULL SWT Additional Amount
14 SWT_ADDL_PCT Number(6,3) DECIMAL(5,3) NOT NULL SWT Additional Percentage
15 ADDL_AMT_ADJ Character(1) VARCHAR2(1) NOT NULL Additional Amount Adjustment
D=Decrease Additional Amount
I=Increase Additional Amount

Default Value: I

16 WT4A_FILED Character(1) VARCHAR2(1) NOT NULL Designates that Form WT-4A has been filed to allow the addiitonal amount to be used as a standing Flat Tax Amount. As such, Reciprocity rules are enforced.

Y/N Table Edit

17 EIC_STATUS_ST Character(1) VARCHAR2(1) NOT NULL Earned Income Credit

Y/N Table Edit

Default Value: N

18 RES_ADDL_ALLOW Number(3,0) SMALLINT NOT NULL Number of Qualifying Children
19 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

20 FWT_MAR_STATUS Character(1) VARCHAR2(1) NOT NULL FWT Marital Status
H=Head of Household
M=Married
S=Single
X=Exempt
21 FWT_ALLOWANCES Number(3,0) SMALLINT NOT NULL FWT Allowances
22 FWT_ADDL_AMT Number(8,2) DECIMAL(7,2) NOT NULL FWT Additional Amount
23 FWT_ADDL_PCT Number(6,3) DECIMAL(5,3) NOT NULL FWT Additional Percentage
24 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

25 MARRIED_FILE_SINGL Character(1) VARCHAR2(1) NOT NULL Married Filing Single

Y/N Table Edit

Default Value: N

26 LWT_ALLOWANCES Number(3,0) SMALLINT NOT NULL Local Withholding Allowances
27 LWT_AMT Number(8,2) DECIMAL(7,2) NOT NULL Additional Amount
28 LWT_PCT Number(6,3) DECIMAL(5,3) NOT NULL Additional Percentage
29 SPECIAL_LWT_STATUS Character(1) VARCHAR2(1) NOT NULL Special Tax Status
E=No taxable gross; no tax taken
G=Maintain taxable gross
N=None

Default Value: N

30 LWT_MAR_STATUS Character(1) VARCHAR2(1) NOT NULL LWT Marital/Tax Status

Prompt Table: SWT_MARSTAT_TBL

31 PY_MOD_DESCR60_LBL Character(75) VARCHAR2(75) NOT NULL Tax Jurisdiction
32 PY_MOD_DESCR25 Character(25) VARCHAR2(25) NOT NULL Description
33 PY_MOD_DESCR30 Character(40) VARCHAR2(40) NOT NULL Description
34 MARITAL_STAT Character(1) VARCHAR2(1) NOT NULL Marital Status
1=Single
2=Married
3=Separated
4=Divorced
5=Widowed
35 ALLOWANCE_AMT Number(6,0) INTEGER NOT NULL Allowance Amount
36 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

Default Value: A

37 FWT_MULTI_JOBS Character(1) VARCHAR2(1) NOT NULL 2020 W4 changes - Multiple Jobs Indicator

Y/N Table Edit

Default Value: N

38 FWT_TTL_DEP_AMT Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Total Dependent Amount
39 FWT_OTH_INCOME Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Other Income
40 FWT_DED_ADJ Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Deduction Adjustment
41 PY_MOD_CHILD_U17 Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Deduction Adjustment
42 PY_MOD_OTH_DEPNDNT Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Deduction Adjustment
43 PY_SWT_FORM_RECVD Character(1) VARCHAR2(1) NOT NULL State form received.
B=Received - Line 2 Blank
N=Form Not Received
Y=Received - Line 2 Not Blank

Y/N Table Edit

Default Value: N