W3EB_HLTHBON_VW (SQL View)	Index Back
Contact Phone from Vendor View Used to retrieve the contact from the Vendor Cotnact table for Contacts of Type "C" and their "MAIN" phone number.

W3EB_HLTHBON_VW

(SQL View)

Contact Phone from Vendor View

Used to retrieve the contact from the Vendor Cotnact table for Contacts of Type "C" and their "MAIN" phone number.

SELECT L.SETID , L.VENDOR_ID , L.CNTCT_SEQ_NUM , L.EFFDT , L.PHONE_TYPE , L.PHONE , L.EXTENSION , A.CONTACT_NAME , A.CONTACT_TYPE , A.CONTACT_TITLE , A.PREFERRED_LANGUAGE FROM PS_VNDR_CNTCT_PHN L , PS_VENDOR_CNTCT A WHERE L.SETID = A.SETID AND L.VENDOR_ID = A.VENDOR_ID AND L.CNTCT_SEQ_NUM = A.CNTCT_SEQ_NUM AND L.EFFDT=A.EFFDT AND L.PHONE_TYPE = 'MAIN' AND A.EFF_STATUS = 'A' AND A.CONTACT_TYPE = 'U' AND A.CNTCT_SEQ_NUM = ( SELECT MAX(S.CNTCT_SEQ_NUM) FROM PS_VENDOR_CNTCT S WHERE S.SETID = A.SETID AND S.VENDOR_ID = A.VENDOR_ID AND S.CNTCT_SEQ_NUM= A.CNTCT_SEQ_NUM AND S.EFFDT= A.EFFDT) AND A.EFFDT = ( SELECT MAX(J.EFFDT) FROM PS_VENDOR_CNTCT J WHERE J.SETID = A.SETID AND J.VENDOR_ID = A.VENDOR_ID AND J.CNTCT_SEQ_NUM=A.CNTCT_SEQ_NUM AND J.EFFDT=A.EFFDT)

# 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(3,0) SMALLINT NOT NULL Contact Sequence Number

4 EFFDT Date(10) DATE Effective Date

5 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business
CAMP=Campus
CELL=Mobile
DORM=Dormitory
FAX=FAX
HOME=Home
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
WORK=Work

6 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

7 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone number extension

8 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL Contact Name

9 CONTACT_TYPE Character(1) VARCHAR2(1) NOT NULL Contact Type
B=Billing Contact
C=Claims
E=EDI Contact
G=General
I=Internal Corporate Contact
M=Executive Management
O=Policy
P=Accounts Payable
R=Broker
S=Sales Contact
U=Customer Service
V=Service Contact
W=Warehousing/Shipping Contact

10 CONTACT_TITLE Character(35) VARCHAR2(35) NOT NULL Contact Title

11 PREFERRED_LANGUAGE Character(3) VARCHAR2(3) NOT NULL Preferred Language
E=English
F=French

#	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(3,0)	SMALLINT NOT NULL	Contact Sequence Number
4	EFFDT	Date(10)	DATE	Effective Date
5	PHONE_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Phone Type BUSN=Business CAMP=Campus CELL=Mobile DORM=Dormitory FAX=FAX HOME=Home MAIN=Main OTR=Other PGR1=Pager 1 PGR2=Pager 2 TELX=Telex WORK=Work
6	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
7	EXTENSION	Character(6)	VARCHAR2(6) NOT NULL	Phone number extension
8	CONTACT_NAME	Character(50)	VARCHAR2(50) NOT NULL	Contact Name
9	CONTACT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Contact Type B=Billing Contact C=Claims E=EDI Contact G=General I=Internal Corporate Contact M=Executive Management O=Policy P=Accounts Payable R=Broker S=Sales Contact U=Customer Service V=Service Contact W=Warehousing/Shipping Contact
10	CONTACT_TITLE	Character(35)	VARCHAR2(35) NOT NULL	Contact Title
11	PREFERRED_LANGUAGE	Character(3)	VARCHAR2(3) NOT NULL	Preferred Language E=English F=French