PV_SUPP_TPO_MK2

(SQL View)
 Index Back

Active supplier list

Depends on views PV_SUPP_TPO_MK1

SELECT A.SETID , A.VENDOR_ID , A.ADDRESS_SEQ_NUM , A.NAME1 , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , A.EMAILID , B.CONTACT_NAME , B.CONTACT_TYPE , B.contact_title , B.CONTACTurl , B.DESCR , B.CONTACTEMAIL , B.PHONE_TYPE , B.COUNTRY_CODE , B.PHONE , B.EXTENSION FROM PS_VENDOR_ADDR A LEFT OUTER JOIN PS_PV_SUPP_TPO_MK1 B ON A.SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.address_seq_num = B.address_seq_num WHERE A.EFF_STATUS = 'A' AND A.ADDRESS_SEQ_NUM = 1 AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_VENDOR_ADDR A1 WHERE A1.setid = A.setid AND A1.vendor_id = A.vendor_id AND A1.address_seq_num = A.address_seq_num AND A1.eff_status = 'A' AND A1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
3 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
4 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
5 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
6 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
7 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
8 CITY Character(30) VARCHAR2(30) NOT NULL City
9 STATE Character(6) VARCHAR2(6) NOT NULL State
10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
11 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
12 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
13 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL The individual contact name associated with a given bank/counterparty.
14 CONTACT_TYPE Character(1) VARCHAR2(1) NOT NULL An attribute that describes the specific purpose of a given bank/counterparty contact person.
B=Billing Contact
C=Contract Collaborator
D=Commercial Paper Contact
E=External Contact
F=Cash Forecast
G=General
I=Internal Corporate Contact
L=Line of Credit Contact
M=Executive Management
O=Investment Pool Contact
P=Accounts Payable
R=Broker
S=Sales Contact
V=Service Contact
W=Warehousing/Shipping Contact
15 CONTACT_TITLE Character(35) VARCHAR2(35) NOT NULL The professional title for a given bank/counterparty contact person.
16 CONTACTURL Character(254) VARCHAR2(254) NOT NULL Integration Server Business Entity Internet URL (Universal Resource Locator)
17 DESCR Character(30) VARCHAR2(30) NOT NULL Description
18 CONTACTEMAIL Character(70) VARCHAR2(70) NOT NULL Integration Server Business Entity Contact Email
19 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business Phone
CAMP=Campus Phone
CELL=Cellular Phone
DORM=Dormitory Phone
FAX=FAX
HOME=Home Phone
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
20 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
21 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
22 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension