VENDOR_SETIDVENDOR_IDVNDR_LOCPrompt Table: EM_VNDR_LOC_VW
EFFDTDefault Value: %date
Y/N Table Edit
Default Value: N
EM_VNDR_LOC_V3(SQL View) |
Index Back |
|---|---|
Supplier Location ViewView of supplier location descriptions. |
| SELECT DISTINCT C.SETID , C.VENDOR_ID , A.NAME1 , C.VNDR_LOC , C.EFFDT , B.DESCR , E.ADDRESS1 , E.ADDRESS2 , E.ADDRESS3 , E.ADDRESS4 , E.CITY , E.STATE , E.POSTAL , D.TIN , D.DIRECT_SALES , D.SECOND_TIN , D.STATE_TAX_WH , D.WTHD_CONTROL_NAME , D.TRADE_BUS_INC , D.FOREIGN_TAX_PAID , D.COUNTRY_PAID , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , D.BOND_PREMIUM , D.MARKET_DISCOUNT , D.PRIVATE_BOND_INT , D.TAX_EXMPT_INTEREST , D.BOND_PREMIUM_TAXEX , D.FATCA_INDICATOR , D.REPORT_BOND_PREM FROM PS_VENDOR A , PS_VNDR_LOC_SCROL B , PS_VENDOR_LOC C , PS_VENDOR_WTHD D , PS_VENDOR_ADDR E WHERE B.SETID = A.SETID AND B.VENDOR_ID = A.VENDOR_ID AND C.SETID = A.SETID AND C.VENDOR_ID = A.VENDOR_ID AND C.VNDR_LOC = B.VNDR_LOC AND C.EFFDT = ( SELECT MAX(I.EFFDT) FROM PS_VENDOR_LOC I WHERE I.SETID = C.SETID AND I.VENDOR_ID = C.VENDOR_ID AND I.VNDR_LOC = C.VNDR_LOC AND I.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND D.SETID = A.SETID AND D.VENDOR_ID = A.VENDOR_ID AND D.VNDR_LOC = B.VNDR_LOC AND D.WTHD_ENTITY = 'IRS' AND D.EFFDT = ( SELECT MAX(J.EFFDT) FROM PS_VENDOR_WTHD J WHERE J.SETID = D.SETID AND J.VENDOR_ID = D.VENDOR_ID AND J.VNDR_LOC = D.VNDR_LOC AND J.EFFDT <= %CurrentDateIn) AND E.SETID = A.SETID AND E.VENDOR_ID = A.VENDOR_ID AND E.ADDRESS_SEQ_NUM = D.ADDRESS_SEQ_NUM AND E.EFFDT = ( SELECT MAX(K.EFFDT) FROM PS_VENDOR_ADDR K WHERE K.SETID = E.SETID AND K.VENDOR_ID = E.VENDOR_ID AND K.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 2 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 3 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
| 4 | VNDR_LOC |
Character(10) | VARCHAR2(10) NOT NULL |
Vendor Location
Prompt Table: EM_VNDR_LOC_VW |
| 5 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 6 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 7 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 8 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 9 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 10 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 11 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 12 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 13 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 14 | TIN | Character(20) | VARCHAR2(20) NOT NULL | Taxpayer Identification Number |
| 15 | DIRECT_SALES | Character(1) | VARCHAR2(1) NOT NULL | Direct Sales of $5000 or More |
| 16 | SECOND_TIN | Character(1) | VARCHAR2(1) NOT NULL | IRS Notified Twice of Invalid |
| 17 | STATE_TAX_WH | Number(16,2) | DECIMAL(15,2) NOT NULL | State Income Tax Withheld |
| 18 | WTHD_CONTROL_NAME | Character(4) | VARCHAR2(4) NOT NULL | Withholding Control Name |
| 19 | TRADE_BUS_INC | Character(1) | VARCHAR2(1) NOT NULL | Trade or Business Income Ind |
| 20 | FOREIGN_TAX_PAID | Number(16,2) | DECIMAL(15,2) NOT NULL | Foreign Tax Paid |
| 21 | COUNTRY_PAID | Character(40) | VARCHAR2(40) NOT NULL | Country Paid |
| 22 | WTHD_AMT_TYPE_G | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Goverment |
| 23 | WTHD_AMT_TYPE_I | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Interest |
| 24 | WTHD_AMT_TYPE_M | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Misc |
| 25 | RP_YEAR | Number(4,0) | SMALLINT NOT NULL | RP Terms Year |
| 26 | UPD_AMT_TYPE_G | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Goverment |
| 27 | UPD_AMT_TYPE_I | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Interest |
| 28 | UPD_AMT_TYPE_M | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Misc |
| 29 | MAN_AMT_TYPE_G | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Goverment |
| 30 | MAN_AMT_TYPE_I | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Interest |
| 31 | MAN_AMT_TYPE_M | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Withholding Amount Misc |
| 32 | WTHD_BASIS_AMT2 | Signed Number(33,3) | DECIMAL(31,3) NOT NULL | WTHD_BASIS_AMT2 |
| 33 | WTHD_BASIS_AMT3 | Signed Number(33,3) | DECIMAL(31,3) NOT NULL | WTHD_BASIS_AMT2 |
| 34 | WTHD_BASIS_AMT4 | Signed Number(33,3) | DECIMAL(31,3) NOT NULL | WTHD_BASIS_AMT2 |
| 35 | BOND_PREMIUM | Number(13,2) | DECIMAL(12,2) NOT NULL | Bond Premium |
| 36 | MARKET_DISCOUNT | Number(13,2) | DECIMAL(12,2) NOT NULL | Market Discount |
| 37 | PRIVATE_BOND_INT | Number(13,2) | DECIMAL(12,2) NOT NULL | Private Activity Bond Interest |
| 38 | TAX_EXMPT_INTEREST | Number(13,2) | DECIMAL(12,2) NOT NULL | Tax-exempt Interest |
| 39 | BOND_PREMIUM_TAXEX | Number(13,2) | DECIMAL(12,2) NOT NULL | Bond Premium on Tax Exempt Bond |
| 40 | FATCA_INDICATOR | Character(1) | VARCHAR2(1) NOT NULL |
FATCA Filiing Requirement Indicator
Y/N Table Edit Default Value: N |
| 41 | REPORT_BOND_PREM | Number(13,2) | DECIMAL(12,2) NOT NULL | Bond Premium on Treasury Obligations |