RE_LS_VEN_PHN (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.

RE_LS_VEN_PHN

(SQL View)

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 VENDOR.SETID ,VENDOR.VENDOR_ID ,VENDOR.CNTCT_SEQ_NUM ,VENDOR.CONTACT_NAME ,VENDOR.CONTACT_TITLE ,VENDOR.CONTACT_TYPE ,VENDOR.PREFERRED_LANGUAGE ,VENDOR.EMAILID ,VENDOR.ADDRESS_SEQ_NUM ,PHONE.PHONE_TYPE ,PHONE.COUNTRY_CODE ,PHONE.PHONE ,PHONE.FAX ,PHONE.EXTENSION FROM %Sql(RE_LS_VEN_PHONE_OUTER_JOIN_F) VENDOR.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VENDOR_CNTCT B WHERE B.SETID = VENDOR.SETID AND B.VENDOR_ID = VENDOR.VENDOR_ID AND B.CNTCT_SEQ_NUM = VENDOR.CNTCT_SEQ_NUM AND B.EFFDT <= %CurrentDateIn AND B.EFF_STATUS = 'A')

# 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 CONTACT_TITLE Character(35) VARCHAR2(35) NOT NULL The professional title for a given bank/counterparty contact person.

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 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

8 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address

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

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

#	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	CONTACT_TITLE	Character(35)	VARCHAR2(35) NOT NULL	The professional title for a given bank/counterparty contact person.
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	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
8	EMAILID	Character(70)	VARCHAR2(70) NOT NULL	A user's E-mail address
9	ADDRESS_SEQ_NUM	Number(5,0)	INTEGER NOT NULL	Address Sequence Number
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