PY_MOD_STATE_VW

(SQL View)
 Index Back

Paycheck Modeling State Tax Vw

Paycheck Modeling State Tax View

SELECT DISTINCT A.EMPLID , A.COMPANY , A.EFFDT , A.STATE , A.RESIDENT , A.SPECIAL_SWT_STATUS , A.SWT_MAR_STATUS , A.SWT_ALLOWANCES , A.SWT_ADDL_ALLOWNCES , A.ANNL_EXEMPTION_AMT , A.PERCENT_OF_FWT , A.SWT_ADDL_AMT , A.SWT_ADDL_PCT , A.ADDL_AMT_ADJ , A.WT4A_FILED , A.EIC_STATUS_ST , A.RES_ADDL_ALLOW , B.DESCR , 0 , PY_SWT_FORM_RECVD FROM PS_MODEL_STATE_TAX A , PS_STATE_TBL B WHERE A.STATE NOT IN ('AK', 'FL', 'NV', 'NH', 'SD', 'TN', 'TX', 'WY') AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_MODEL_STATE_TAX A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.COMPANY = A_ED.COMPANY AND A.STATE = A_ED.STATE AND A_ED.EFFDT <= %CurrentDateIn) AND A.STATE = B.STATE AND B.COUNTRY = 'USA' AND EXISTS ( SELECT 'Y' FROM PS_MODEL_PAY_TAX X WHERE X.PY_MOD_EMPLID = A.EMPLID AND X.STATE = A.STATE AND ((((X.TAX_CLASS IN ('U','6','1','3','5','7','A','AA','AC','AE','D','F','G','H','I','L','M','N','O','T','V','W')) OR ( X.TAX_CLASS IN ( SELECT AB.TAX_CLASS FROM PS_PY_PFF_STPG_DTL AA , PS_PY_PFF_STPG_CHD AB , PS_PY_PFF_PGID_CHD AC WHERE AA.STATUS='A' AND AC.PY_PFF_TAX_CAT = 'E' AND AB.STATE =A.STATE AND AA.EFFDT=( SELECT MAX(AB1.EFFDT) FROM PS_PY_PFF_STPG_DTL AB1 WHERE AA.STATE=AB1.STATE AND AA.PY_PFF_PROGID=AB1.PY_PFF_PROGID AND AB1.EFFDT <= %CurrentDateIn) AND AA.EFFSEQ=( SELECT MAX(AB2.EFFSEQ) FROM PS_PY_PFF_STPG_DTL AB2 WHERE AB2.STATE=AA.STATE AND AB2.PY_PFF_PROGID=AA.PY_PFF_PROGID AND AB2.EFFDT=AA.EFFDT AND AA.EFFDT <= %CurrentDateIn) AND AA.STATE=AB.STATE AND AA.PY_PFF_PROGID=AB.PY_PFF_PROGID AND AA.EFFDT=AB.EFFDT AND AA.EFFSEQ=AB.EFFSEQ AND AA.PY_PFF_PROGID=AC.PY_PFF_PROGID AND AC.EFFDT=( SELECT MAX(AC1.EFFDT) FROM PS_PY_PFF_PGID_CHD AC1 WHERE AC.PY_PFF_PROGID=AC1.PY_PFF_PROGID AND AC.TAX_CLASS=AC1.TAX_CLASS AND AC1.EFFDT <= %CurrentDateIn) AND AC.EFFSEQ=( SELECT MAX(AC2.EFFSEQ) FROM PS_PY_PFF_PGID_CHD AC2 WHERE AC.PY_PFF_PROGID=AC2.PY_PFF_PROGID AND AC.TAX_CLASS=AC2.TAX_CLASS AND AC2.EFFDT=AC.EFFDT AND AC2.EFFDT <= %CurrentDateIn) AND AB.TAX_CLASS=AC.TAX_CLASS ))) AND X.LOCALITY = ' ') OR (X.TAX_CLASS = 'B' AND X.STATE = 'OR') OR (X.TAX_CLASS = 'P' AND X.STATE = 'PA' AND X.LOCALITY LIKE 'O%')) AND ((X.TAX_CUR <> 0) OR (X.TAX_CUR = 0 AND X.STATE IN ('AS', 'GU', 'VI'))))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

3 EFFDT Date(10) DATE Effective Date

Default Value: %date

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

Prompt Table: PY_STATETAX_VW

5 RESIDENT Character(1) VARCHAR2(1) NOT NULL Resident

Y/N Table Edit

Default Value: Y

6 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
7 SWT_MAR_STATUS Character(1) VARCHAR2(1) NOT NULL SWT Marital/Tax Status

Prompt Table: SWT_MARSTAT_TBL

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

Default Value: I

15 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

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

Y/N Table Edit

Default Value: N

17 RES_ADDL_ALLOW Number(3,0) SMALLINT NOT NULL Number of Qualifying Children
18 DESCR Character(30) VARCHAR2(30) NOT NULL Description
19 DIST_PCT Number(7,3) DECIMAL(6,3) NOT NULL Percent of Distribution
20 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