WTHD_1099_VW2

(SQL View)

SELECT WTHD.WTHD_RPT_ID , WTHD.WTHD_SETID , WTHD.WTHD_CNTL_ID , WTHD.WTHD_ENTITY , WTHD.WTHD_TYPE , WTHD.CALENDAR_SETID , WTHD.CALENDAR_ID , WTHD.FISCAL_YEAR , WTHD.PERIOD , WTHD.PROCESS_DTTM , WTHD.VENDOR_SETID , WTHD.BUSINESS_UNIT , WTHD.VENDOR_ID , WTHD.VNDR_LOC , WTHD.PAYER_TIN , WTHD.ADDRESS_SEQ_NUM , WTHD.LINE_NO , WTHD.SEND_STATUS , WTHD.ACTIVE_STATUS , WTHD.DESCR , WTHD.CURRENCY_CD , WTHD.WTHD_BASIS_AMT , WTHD.WTHD_LIABILITY_AMT , WTHD.PAID_AMT , WTHD.SEX , WTHD.BIRTHDATE , WTHD.BIRTHPLACE , WTHD.NUM_CHILDREN , WTHD.COMPANY_FLG , WTHD.TAX_DIST_REF , WTHD.CERTIFICATE_TYPE , WTHD.CERTIFICATE_NUM , WTHD.CERTIFICATE_EXP_DT , WTHD.DIRECT_SALES , WTHD.SECOND_TIN , WTHD.STATE_TAX_WH , WTHD.TIN_TYPE , WTHD.TIN , WTHD.WTHD_CONTROL_NAME , WTHD.PROFESSION , WTHD.WTHD_PAN_NUM , WTHD.WTHD_PAN_WARD , WTHD.TRADE_BUS_INC , WTHD.COUNTRY_PAID , WTHD.FOREIGN_TAX_PAID , WTHD.GBR_VENDOR_TYPE , WTHD.GBR_REG_NO , WTHD.GBR_VERIFY , WTHD.GBR_VERIFY_NBR , WTHD.GBR_VERIFY_DATE , WTHD.BOND_PREMIUM , WTHD.MARKET_DISCOUNT , WTHD.PRIVATE_BOND_INT , WTHD.TAX_EXMPT_INTEREST , WTHD.BOND_PREMIUM_TAXEX , WTHD.FATCA_INDICATOR , WTHD.REPORT_BOND_PREM , WTHD.NAME1 , WTHD.NAME2 , WTHD.COUNTRY , WTHD.ADDRESS1 , WTHD.ADDRESS2 , WTHD.ADDRESS3 , WTHD.ADDRESS4 , WTHD.CITY , WTHD.NUM1 , WTHD.NUM2 , WTHD.HOUSE_TYPE , WTHD.ADDR_FIELD1 , WTHD.ADDR_FIELD2 , WTHD.ADDR_FIELD3 , WTHD.COUNTY , WTHD.STATE , WTHD.POSTAL , WTHD.GEO_CODE , WTHD.IN_CITY_LIMIT , WTHD.CUSIP_NBR FROM PS_WTHD_1099_VW3 WTHD WHERE NOT EXISTS ( SELECT 'X' FROM PS_WTHD_SENT_DTL SENT WHERE WTHD.WTHD_RPT_ID = SENT.WTHD_RPT_ID AND WTHD.WTHD_SETID = SENT.WTHD_SETID AND WTHD.WTHD_CNTL_ID = SENT.WTHD_CNTL_ID AND WTHD.WTHD_ENTITY = SENT.WTHD_ENTITY AND WTHD.WTHD_TYPE = SENT.WTHD_TYPE AND WTHD.CALENDAR_SETID = SENT.CALENDAR_SETID AND WTHD.CALENDAR_ID = SENT.CALENDAR_ID AND WTHD.FISCAL_YEAR = SENT.FISCAL_YEAR AND WTHD.PERIOD = SENT.PERIOD AND WTHD.VENDOR_SETID = SENT.VENDOR_SETID AND WTHD.VENDOR_ID = SENT.VENDOR_ID AND WTHD.VNDR_LOC = SENT.VNDR_LOC AND WTHD.PAYER_TIN = SENT.PAYER_TIN AND WTHD.TIN_TYPE = SENT.TIN_TYPE AND WTHD.TIN = SENT.TIN AND WTHD.LINE_NO = SENT.LINE_NO AND SENT.ACTIVE_STATUS = 'A' )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 WTHD_RPT_ID Character(10) VARCHAR2(10) NOT NULL Withholding Report ID

2 WTHD_SETID Character(5) VARCHAR2(5) NOT NULL Setid for the Withholding Code

3 WTHD_CNTL_ID Character(10) VARCHAR2(10) NOT NULL Control ID

4 WTHD_ENTITY Character(5) VARCHAR2(5) NOT NULL Withholding Entity

5 WTHD_TYPE Character(5) VARCHAR2(5) NOT NULL Withholding Type
Default Value: 1099

6 CALENDAR_SETID Character(5) VARCHAR2(5) NOT NULL Calendar SetID

7 CALENDAR_ID Character(2) VARCHAR2(2) NOT NULL Calendar ID

8 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year

9 PERIOD Number(3,0) SMALLINT NOT NULL Period

10 PROCESS_DTTM DateTime(26) TIMESTAMP Process Date Time

11 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

13 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

14 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location

15 PAYER_TIN Character(20) VARCHAR2(20) NOT NULL Payer Taxpayer Identification

16 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number

17 LINE_NO Number(3,0) SMALLINT NOT NULL Line Number

18 SEND_STATUS Character(1) VARCHAR2(1) NOT NULL Send Status

19 ACTIVE_STATUS Character(1) VARCHAR2(1) NOT NULL Transfer Template Status
A=Active
I=Inactive

20 DESCR Character(30) VARCHAR2(30) NOT NULL Description

21 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

22 WTHD_BASIS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Withholding Basis Amount

23 WTHD_LIABILITY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Withholding Liability Amount

24 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount

25 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown

26 BIRTHDATE Date(10) DATE Date of Birth

27 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
Prompt Table: STATE_TBL

28 NUM_CHILDREN Number(3,0) SMALLINT NOT NULL Number of children

29 COMPANY_FLG Character(1) VARCHAR2(1) NOT NULL Company
Y/N Table Edit
Default Value: N

30 TAX_DIST_REF Character(13) VARCHAR2(13) NOT NULL Tax District Reference

31 CERTIFICATE_TYPE Character(1) VARCHAR2(1) NOT NULL Certificate type

32 CERTIFICATE_NUM Character(15) VARCHAR2(15) NOT NULL Certificate Number

33 CERTIFICATE_EXP_DT Date(10) DATE Certificate Expiration

34 DIRECT_SALES Character(1) VARCHAR2(1) NOT NULL Direct Sales of $5000 or More
Y/N Table Edit
Default Value: N

35 SECOND_TIN Character(1) VARCHAR2(1) NOT NULL IRS Notified Twice of Invalid
Y/N Table Edit
Default Value: N

36 STATE_TAX_WH Number(16,2) DECIMAL(15,2) NOT NULL State Income Tax Withheld

37 TIN_TYPE Character(1) VARCHAR2(1) NOT NULL TIN Type
F=Federal Employer ID Number
S=Social Security Number

38 TIN Character(20) VARCHAR2(20) NOT NULL Taxpayer Identification Number

39 WTHD_CONTROL_NAME Character(4) VARCHAR2(4) NOT NULL Withholding Control Name

40 PROFESSION Character(30) VARCHAR2(30) NOT NULL Profession

41 WTHD_PAN_NUM Character(15) VARCHAR2(15) NOT NULL PAN No.

42 WTHD_PAN_WARD Character(30) VARCHAR2(30) NOT NULL PAN Ward

43 TRADE_BUS_INC Character(1) VARCHAR2(1) NOT NULL Trade or Business Income Ind
Y/N Table Edit

44 COUNTRY_PAID Character(40) VARCHAR2(40) NOT NULL Country Paid

45 FOREIGN_TAX_PAID Number(16,2) DECIMAL(15,2) NOT NULL Foreign Tax Paid

46 GBR_VENDOR_TYPE Character(1) VARCHAR2(1) NOT NULL Indicates a Corporation or Individual
C=Corporation
I=Individual

47 GBR_REG_NO Character(9) VARCHAR2(9) NOT NULL Registration/National Insurance number

48 GBR_VERIFY Character(1) VARCHAR2(1) NOT NULL Verification Complete or Not
Y/N Table Edit
Default Value: N

49 GBR_VERIFY_NBR Character(13) VARCHAR2(13) NOT NULL Sub Contractor Verification Number

50 GBR_VERIFY_DATE Date(10) DATE Sub Contractor Verification Number

51 BOND_PREMIUM Number(13,2) DECIMAL(12,2) NOT NULL Bond Premium

52 MARKET_DISCOUNT Number(13,2) DECIMAL(12,2) NOT NULL Market Discount

53 PRIVATE_BOND_INT Number(13,2) DECIMAL(12,2) NOT NULL Private Activity Bond Interest

54 TAX_EXMPT_INTEREST Number(13,2) DECIMAL(12,2) NOT NULL Tax-exempt Interest

55 BOND_PREMIUM_TAXEX Number(13,2) DECIMAL(12,2) NOT NULL Bond Premium on Tax Exempt Bond

56 FATCA_INDICATOR Character(1) VARCHAR2(1) NOT NULL FATCA Filiing Requirement Indicator
Y/N Table Edit
Default Value: N

57 REPORT_BOND_PREM Number(13,2) DECIMAL(12,2) NOT NULL Bond Premium on Treasury Obligations

58 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

59 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2

60 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

61 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

62 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

63 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3

64 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4

65 CITY Character(30) VARCHAR2(30) NOT NULL City

66 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1

67 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2

68 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer

69 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1

70 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2

71 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3

72 COUNTY Character(30) VARCHAR2(30) NOT NULL County

73 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: %EDIT_STATE

74 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

75 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code

76 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
Y/N Table Edit

77 CUSIP_NBR Character(13) VARCHAR2(13) NOT NULL CUSIP ID - Used on Security

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	WTHD_RPT_ID	Character(10)	VARCHAR2(10) NOT NULL	Withholding Report ID
2	WTHD_SETID	Character(5)	VARCHAR2(5) NOT NULL	Setid for the Withholding Code
3	WTHD_CNTL_ID	Character(10)	VARCHAR2(10) NOT NULL	Control ID
4	WTHD_ENTITY	Character(5)	VARCHAR2(5) NOT NULL	Withholding Entity
5	WTHD_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Withholding Type Default Value: 1099
6	CALENDAR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Calendar SetID
7	CALENDAR_ID	Character(2)	VARCHAR2(2) NOT NULL	Calendar ID
8	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
9	PERIOD	Number(3,0)	SMALLINT NOT NULL	Period
10	PROCESS_DTTM	DateTime(26)	TIMESTAMP	Process Date Time
11	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
12	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
13	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
14	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
15	PAYER_TIN	Character(20)	VARCHAR2(20) NOT NULL	Payer Taxpayer Identification
16	ADDRESS_SEQ_NUM	Number(5,0)	INTEGER NOT NULL	Address Sequence Number
17	LINE_NO	Number(3,0)	SMALLINT NOT NULL	Line Number
18	SEND_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Send Status
19	ACTIVE_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Transfer Template Status A=Active I=Inactive
20	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
21	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
22	WTHD_BASIS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Withholding Basis Amount
23	WTHD_LIABILITY_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Withholding Liability Amount
24	PAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount
25	SEX	Character(1)	VARCHAR2(1) NOT NULL	Gender F=Female M=Male U=Unknown
26	BIRTHDATE	Date(10)	DATE	Date of Birth
27	BIRTHPLACE	Character(30)	VARCHAR2(30) NOT NULL	Birth Location Prompt Table: STATE_TBL
28	NUM_CHILDREN	Number(3,0)	SMALLINT NOT NULL	Number of children
29	COMPANY_FLG	Character(1)	VARCHAR2(1) NOT NULL	Company Y/N Table Edit Default Value: N
30	TAX_DIST_REF	Character(13)	VARCHAR2(13) NOT NULL	Tax District Reference
31	CERTIFICATE_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Certificate type
32	CERTIFICATE_NUM	Character(15)	VARCHAR2(15) NOT NULL	Certificate Number
33	CERTIFICATE_EXP_DT	Date(10)	DATE	Certificate Expiration
34	DIRECT_SALES	Character(1)	VARCHAR2(1) NOT NULL	Direct Sales of $5000 or More Y/N Table Edit Default Value: N
35	SECOND_TIN	Character(1)	VARCHAR2(1) NOT NULL	IRS Notified Twice of Invalid Y/N Table Edit Default Value: N
36	STATE_TAX_WH	Number(16,2)	DECIMAL(15,2) NOT NULL	State Income Tax Withheld
37	TIN_TYPE	Character(1)	VARCHAR2(1) NOT NULL	TIN Type F=Federal Employer ID Number S=Social Security Number
38	TIN	Character(20)	VARCHAR2(20) NOT NULL	Taxpayer Identification Number
39	WTHD_CONTROL_NAME	Character(4)	VARCHAR2(4) NOT NULL	Withholding Control Name
40	PROFESSION	Character(30)	VARCHAR2(30) NOT NULL	Profession
41	WTHD_PAN_NUM	Character(15)	VARCHAR2(15) NOT NULL	PAN No.
42	WTHD_PAN_WARD	Character(30)	VARCHAR2(30) NOT NULL	PAN Ward
43	TRADE_BUS_INC	Character(1)	VARCHAR2(1) NOT NULL	Trade or Business Income Ind Y/N Table Edit
44	COUNTRY_PAID	Character(40)	VARCHAR2(40) NOT NULL	Country Paid
45	FOREIGN_TAX_PAID	Number(16,2)	DECIMAL(15,2) NOT NULL	Foreign Tax Paid
46	GBR_VENDOR_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Indicates a Corporation or Individual C=Corporation I=Individual
47	GBR_REG_NO	Character(9)	VARCHAR2(9) NOT NULL	Registration/National Insurance number
48	GBR_VERIFY	Character(1)	VARCHAR2(1) NOT NULL	Verification Complete or Not Y/N Table Edit Default Value: N
49	GBR_VERIFY_NBR	Character(13)	VARCHAR2(13) NOT NULL	Sub Contractor Verification Number
50	GBR_VERIFY_DATE	Date(10)	DATE	Sub Contractor Verification Number
51	BOND_PREMIUM	Number(13,2)	DECIMAL(12,2) NOT NULL	Bond Premium
52	MARKET_DISCOUNT	Number(13,2)	DECIMAL(12,2) NOT NULL	Market Discount
53	PRIVATE_BOND_INT	Number(13,2)	DECIMAL(12,2) NOT NULL	Private Activity Bond Interest
54	TAX_EXMPT_INTEREST	Number(13,2)	DECIMAL(12,2) NOT NULL	Tax-exempt Interest
55	BOND_PREMIUM_TAXEX	Number(13,2)	DECIMAL(12,2) NOT NULL	Bond Premium on Tax Exempt Bond
56	FATCA_INDICATOR	Character(1)	VARCHAR2(1) NOT NULL	FATCA Filiing Requirement Indicator Y/N Table Edit Default Value: N
57	REPORT_BOND_PREM	Number(13,2)	DECIMAL(12,2) NOT NULL	Bond Premium on Treasury Obligations
58	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
59	NAME2	Character(40)	VARCHAR2(40) NOT NULL	Name 2
60	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
61	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
62	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
63	ADDRESS3	Character(55)	VARCHAR2(55) NOT NULL	Address 3
64	ADDRESS4	Character(55)	VARCHAR2(55) NOT NULL	Address 4
65	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
66	NUM1	Character(6)	VARCHAR2(6) NOT NULL	Number 1
67	NUM2	Character(6)	VARCHAR2(6) NOT NULL	Number 2
68	HOUSE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	House Type AB=House Boat WW=Trailer
69	ADDR_FIELD1	Character(2)	VARCHAR2(2) NOT NULL	Address Field 1
70	ADDR_FIELD2	Character(4)	VARCHAR2(4) NOT NULL	Address Field 2
71	ADDR_FIELD3	Character(4)	VARCHAR2(4) NOT NULL	Address Field 3
72	COUNTY	Character(30)	VARCHAR2(30) NOT NULL	County
73	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: %EDIT_STATE
74	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
75	GEO_CODE	Character(11)	VARCHAR2(11) NOT NULL	Geo Code
76	IN_CITY_LIMIT	Character(1)	VARCHAR2(1) NOT NULL	In City Limit Y/N Table Edit
77	CUSIP_NBR	Character(13)	VARCHAR2(13) NOT NULL	CUSIP ID - Used on Security