PY_W4_APPR_VW (SQL View)	Index Back
Upd W4 Approval View Record to be used as the base for the Component for Updatable W4's Approval

PY_W4_APPR_VW

(SQL View)

Upd W4 Approval View

Record to be used as the base for the Component for Updatable W4's Approval

SELECT B.EMPLID , B.COMPANY , B.STATE , T.SESSION_ID , T.SEQ_NBR , N.NAME_DISPLAY , B.SPECIAL_SWT_STATUS , B.SWT_MAR_STATUS , B.SWT_ALLOWANCES , B.SWT_ADDL_AMT , B.SWT_ADDL_ALLOWNCES , B.SWT_ADDL_PCT , B.ANNL_EXEMPTION_AMT , B.RES_ADDL_ALLOW , B.ADDL_AMT_ADJ , T.SWT_MAR_STATUS , T.SWT_ALLOWANCES , T.SWT_ADDL_AMT , T.SWT_ADDL_ALLOWNCES , T.PERCENT_OF_FWT , T.ANNL_EXEMPTION_AMT , T.RES_ADDL_ALLOW , T.ADDL_AMT_ADJ , T.SPECIAL_FWT_STATUS , T.SPECIAL_SWT_STATUS , ' ' , ' ' , ' ' , ' ' , 0 , 0 , 0 , 0 , 0 , 0 , W1.EOAWTHREAD_STATUS , B.PERCENT_OF_FWT FROM PS_STATE_TAX_DATA B , PS_PY_W4_STG_STATE T , PS_NAMES N , PS_PY_W4_APPR_XRF W1 WHERE B.COMPANY = T.COMPANY AND B.EMPLID = T.EMPLID AND B.STATE = T.STATE AND T.COMPANY = W1.COMPANY AND T.EMPLID = W1.EMPLID AND T.STATE = W1.STATE AND T.SESSION_ID = W1.SESSION_ID AND T.SEQ_NBR = W1.SEQ_NBR AND B.EMPLID = N.EMPLID AND ((B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_STATE_TAX_DATA B1 WHERE B1.EMPLID=B.EMPLID AND B1.COMPANY=B.COMPANY AND B1.STATE=B.STATE AND B1.EFFDT <= %CurrentDateIn)) OR (B.EFFDT = ( SELECT MIN(EFFDT) FROM PS_STATE_TAX_DATA B1 WHERE B1.EMPLID=B.EMPLID AND B1.COMPANY=B.COMPANY AND B1.STATE=B.STATE AND B1.EFFDT > %CurrentDateIn))) AND N.EFFDT=( SELECT MAX(EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID=N.EMPLID AND N1.NAME_TYPE=N.NAME_TYPE AND N1.EFFDT <= %CurrentDateIn) AND N.NAME_TYPE = 'PRI' AND W1.EOAWTHREAD_STATUS = 'P' UNION ALL SELECT A.EMPLID , A.COMPANY , '$U' , S.SESSION_ID , S.SEQ_NBR , M.NAME_DISPLAY , A.SPECIAL_FWT_STATUS , A.FWT_MAR_STATUS , A.FWT_ALLOWANCES , A.FWT_ADDL_AMT , 0 , 0 , 0 , 0 , 'I' , S.FWT_MAR_STATUS , S.FWT_ALLOWANCES , S.FWT_ADDL_AMT , 0 , 0 , 0 , 0 , 'I' , S.SPECIAL_FWT_STATUS , S.SPECIAL_SWT_STATUS , A.FWT_FORM_VERSION , S.FWT_FORM_VERSION , A.FWT_MULTI_JOBS , S.FWT_MULTI_JOBS , A.FWT_TTL_DEP_AMT , S.FWT_TTL_DEP_AMT , A.FWT_OTH_INCOME , S.FWT_OTH_INCOME , A.FWT_DED_ADJ , S.FWT_DED_ADJ , W.EOAWTHREAD_STATUS , 0 FROM PS_FED_TAX_DATA A , PS_PY_W4_STG_STATE S , PS_NAMES M , PS_PY_W4_APPR_XRF W WHERE A.COMPANY = S.COMPANY AND A.EMPLID = S.EMPLID AND S.STATE = '$U' AND S.COMPANY = W.COMPANY AND S.EMPLID = W.EMPLID AND S.STATE = W.STATE AND S.SESSION_ID = W.SESSION_ID AND S.SEQ_NBR = W.SEQ_NBR AND A.EMPLID = M.EMPLID AND ((A.EFFDT =( SELECT MAX(EFFDT) FROM PS_FED_TAX_DATA A1 WHERE A1.EMPLID=A.EMPLID AND A1.COMPANY=A.COMPANY AND A1.EFFDT <= %CurrentDateIn)) OR (A.EFFDT = ( SELECT MIN(EFFDT) FROM PS_FED_TAX_DATA A1 WHERE A1.EMPLID=A.EMPLID AND A1.COMPANY=A.COMPANY AND A1.EFFDT > %CurrentDateIn))) AND M.EFFDT=( SELECT MAX(EFFDT) FROM PS_NAMES M1 WHERE M1.EMPLID=M.EMPLID AND M1.NAME_TYPE=M.NAME_TYPE AND M1.EFFDT <= %CurrentDateIn) AND M.NAME_TYPE = 'PRI' AND W.EOAWTHREAD_STATUS = 'P'

# 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 STATE Character(6) VARCHAR2(6) NOT NULL State

4 SESSION_ID Character(36) VARCHAR2(36) NOT NULL Session ID

5 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number

6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

7 PRV_WT_STATUS Character(1) VARCHAR2(1) NOT NULL Previous Withholding Status
A=Nonresident alien
E=No taxable gross; no tax taken
G=Maintain taxable gross
N=None

8 PRV_MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status

9 NO_ALLOWANCES Number(3,0) SMALLINT NOT NULL Number of Allowances Permitted

10 PY_W4_ADDL_AMT Number(8,2) DECIMAL(7,2) NOT NULL W4-Current Additional Amount

11 SWT_ADDL_ALLOWNCES Number(3,0) SMALLINT NOT NULL Additional Allowances

12 PY_W4_ADDL_PCT Number(6,3) DECIMAL(5,3) NOT NULL W4-Additional Percent (FWT or SWT)

13 ANNL_EXEMPTION_AMT Number(8,0) INTEGER NOT NULL MS Annual Exemption Amount

14 RES_ADDL_ALLOW Number(3,0) SMALLINT NOT NULL Res State Addl Allowances

15 ADDL_AMT_ADJ Character(1) VARCHAR2(1) NOT NULL Additional Amount Adjustment
D=Decrease Additional Amount
I=Increase Additional Amount

16 MAR_STATUS_FORM Character(1) VARCHAR2(1) NOT NULL Marital Status Form

17 PY_W4_ALLWNCES_FRM Number(3,0) SMALLINT NOT NULL W4-New Number of Allowances

18 PY_W4_ADDL_AMT_FRM Number(8,2) DECIMAL(7,2) NOT NULL W4-New Additional Amount

19 SWT_ADDL_ALLOW_TAX Number(3,0) SMALLINT NOT NULL Additional Allowances

20 PY_W4_ADDL_PCT_FRM Number(6,3) DECIMAL(5,3) NOT NULL W4-New Additional Percent (FWT or SWT)

21 ANNL_EXEMP_AMT_FRM Number(8,0) INTEGER NOT NULL MS Annual Exemption Amount

22 RES_ADDL_ALLOW_FRM Number(3,0) SMALLINT NOT NULL Corresponding field of RES_ADDL_ALLOW

23 ADDL_AMT_ADJ_FRM Character(1) VARCHAR2(1) NOT NULL Additional Amount Adjustment Indicator - From
D=Decrease Additional Amount
I=Increase Additional Amount

24 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

25 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

26 PRV_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=2019 or Earlier
B=2020 or Later

27 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

28 PRV_MULTI_JOBS Character(1) VARCHAR2(1) NOT NULL 2020 W4 changes - Multiple Jobs Indicator

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

30 PRV_TTL_DEP_AMT Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Total Dependent Amount

31 FWT_TTL_DEP_AMT Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Total Dependent Amount

32 PRV_OTH_INCOME Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Other Income

33 FWT_OTH_INCOME Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Other Income

34 PRV_DED_ADJ Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Deduction Adjustment

35 FWT_DED_ADJ Number(11,2) DECIMAL(10,2) NOT NULL 2020 W4 - Deduction Adjustment

36 EOAWTHREAD_STATUS Character(1) VARCHAR2(1) NOT NULL Thread Status
A=Approved
C=Complete
D=Denied
E=Pending Denial
H=Hard Deny
I=Initial
N=Not Active
P=Pending
S=Awaiting Further Approvals
T=Terminated
W=Canceled
X=Suspended/Pending Denial

37 PERCENT_OF_FWT Number(6,3) DECIMAL(5,3) NOT NULL AZ Withholding Percent

#	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	STATE	Character(6)	VARCHAR2(6) NOT NULL	State
4	SESSION_ID	Character(36)	VARCHAR2(36) NOT NULL	Session ID
5	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
6	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
7	PRV_WT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Previous Withholding Status A=Nonresident alien E=No taxable gross; no tax taken G=Maintain taxable gross N=None
8	PRV_MAR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Marital Status
9	NO_ALLOWANCES	Number(3,0)	SMALLINT NOT NULL	Number of Allowances Permitted
10	PY_W4_ADDL_AMT	Number(8,2)	DECIMAL(7,2) NOT NULL	W4-Current Additional Amount
11	SWT_ADDL_ALLOWNCES	Number(3,0)	SMALLINT NOT NULL	Additional Allowances
12	PY_W4_ADDL_PCT	Number(6,3)	DECIMAL(5,3) NOT NULL	W4-Additional Percent (FWT or SWT)
13	ANNL_EXEMPTION_AMT	Number(8,0)	INTEGER NOT NULL	MS Annual Exemption Amount
14	RES_ADDL_ALLOW	Number(3,0)	SMALLINT NOT NULL	Res State Addl Allowances
15	ADDL_AMT_ADJ	Character(1)	VARCHAR2(1) NOT NULL	Additional Amount Adjustment D=Decrease Additional Amount I=Increase Additional Amount
16	MAR_STATUS_FORM	Character(1)	VARCHAR2(1) NOT NULL	Marital Status Form
17	PY_W4_ALLWNCES_FRM	Number(3,0)	SMALLINT NOT NULL	W4-New Number of Allowances
18	PY_W4_ADDL_AMT_FRM	Number(8,2)	DECIMAL(7,2) NOT NULL	W4-New Additional Amount
19	SWT_ADDL_ALLOW_TAX	Number(3,0)	SMALLINT NOT NULL	Additional Allowances
20	PY_W4_ADDL_PCT_FRM	Number(6,3)	DECIMAL(5,3) NOT NULL	W4-New Additional Percent (FWT or SWT)
21	ANNL_EXEMP_AMT_FRM	Number(8,0)	INTEGER NOT NULL	MS Annual Exemption Amount
22	RES_ADDL_ALLOW_FRM	Number(3,0)	SMALLINT NOT NULL	Corresponding field of RES_ADDL_ALLOW
23	ADDL_AMT_ADJ_FRM	Character(1)	VARCHAR2(1) NOT NULL	Additional Amount Adjustment Indicator - From D=Decrease Additional Amount I=Increase Additional Amount
24	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
25	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
26	PRV_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=2019 or Earlier B=2020 or Later
27	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
28	PRV_MULTI_JOBS	Character(1)	VARCHAR2(1) NOT NULL	2020 W4 changes - Multiple Jobs Indicator
29	FWT_MULTI_JOBS	Character(1)	VARCHAR2(1) NOT NULL	2020 W4 changes - Multiple Jobs Indicator
30	PRV_TTL_DEP_AMT	Number(11,2)	DECIMAL(10,2) NOT NULL	2020 W4 - Total Dependent Amount
31	FWT_TTL_DEP_AMT	Number(11,2)	DECIMAL(10,2) NOT NULL	2020 W4 - Total Dependent Amount
32	PRV_OTH_INCOME	Number(11,2)	DECIMAL(10,2) NOT NULL	2020 W4 - Other Income
33	FWT_OTH_INCOME	Number(11,2)	DECIMAL(10,2) NOT NULL	2020 W4 - Other Income
34	PRV_DED_ADJ	Number(11,2)	DECIMAL(10,2) NOT NULL	2020 W4 - Deduction Adjustment
35	FWT_DED_ADJ	Number(11,2)	DECIMAL(10,2) NOT NULL	2020 W4 - Deduction Adjustment
36	EOAWTHREAD_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Thread Status A=Approved C=Complete D=Denied E=Pending Denial H=Hard Deny I=Initial N=Not Active P=Pending S=Awaiting Further Approvals T=Terminated W=Canceled X=Suspended/Pending Denial
37	PERCENT_OF_FWT	Number(6,3)	DECIMAL(5,3) NOT NULL	AZ Withholding Percent