RE_LS_VEN_CNTCT

(SQL View)
 Index Back

Supplier Contact Details

This joins the Supplier Contact, address and contact telephone information. Since these associations are optional this view is a composite of other views that perform outer joins on this tables.

SELECT PHONE.SETID ,PHONE.VENDOR_ID ,PHONE.CNTCT_SEQ_NUM ,PHONE.CONTACT_NAME ,PHONE.CONTACT_TITLE ,PHONE.CONTACT_TYPE ,PHONE.ADDRESS_SEQ_NUM ,PHONE.PREFERRED_LANGUAGE ,PHONE.EMAILID ,PHONE.PHONE_TYPE ,PHONE.COUNTRY_CODE ,PHONE.PHONE ,PHONE.FAX ,PHONE.EXTENSION , VEN_ADDR.COUNTRY , VEN_ADDR.ADDRESS1 , VEN_ADDR.ADDRESS2 , VEN_ADDR.ADDRESS3 , VEN_ADDR.ADDRESS4 , VEN_ADDR.CITY , VEN_ADDR.NUM1 , VEN_ADDR.NUM2 , VEN_ADDR.HOUSE_TYPE , VEN_ADDR.ADDR_FIELD1 , VEN_ADDR.ADDR_FIELD2 , VEN_ADDR.ADDR_FIELD3 , VEN_ADDR.COUNTY , VEN_ADDR.STATE , VEN_ADDR.POSTAL , VEN_ADDR.GEO_CODE , VEN_ADDR.IN_CITY_LIMIT FROM PS_RE_LS_VEN_PHN PHONE , PS_RE_LS_VEN_ADDR VEN_ADDR WHERE PHONE.SETID = VEN_ADDR.SETID AND PHONE.VENDOR_ID = VEN_ADDR.VENDOR_ID AND PHONE.CNTCT_SEQ_NUM = VEN_ADDR.CNTCT_SEQ_NUM AND PHONE.ADDRESS_SEQ_NUM = VEN_ADDR.ADDRESS_SEQ_NUM AND PHONE.CONTACT_NAME <> ' '

# 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 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
4 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL The individual contact name associated with a given bank/counterparty.
5 TITLE Character(35) VARCHAR2(35) NOT NULL Title
6 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
7 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
8 PREFERRED_LANGUAGE Character(3) VARCHAR2(3) NOT NULL Preferred Language
ARA=Arabic
CFR=Canadian French
CZE=Czech
DAN=Danish
DUT=Dutch
E=English
ENG=English
ESP=Spanish
F=French
FIN=Finnish
FRA=French
GER=German
HUN=Hungarian
INE=International English
ITA=Italian
JPN=Japanese
KOR=Korean
NOR=Norwegian
POL=Polish
POR=Portuguese
RUS=Russian
SVE=Swedish
THA=Thai
UKE=UK English
ZHS=Simplified Chinese
ZHT=Traditional Chinese
9 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
10 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
11 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
12 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
13 FAX Character(24) VARCHAR2(24) NOT NULL Fax Number
14 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
15 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

16 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
17 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
18 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
19 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
20 CITY Character(30) VARCHAR2(30) NOT NULL City
21 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
22 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
23 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
24 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
25 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
26 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
27 COUNTY Character(30) VARCHAR2(30) NOT NULL County
28 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

29 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
30 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
31 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit