WTHD_1099_VW1

(SQL View)

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

# 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