Purchasing.vVendorWithContacts View

Description

Vendor (company) names and the names of vendor employees to contact.

Properties

Creation Date	27/10/2017 14:33
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
BusinessEntityID	Integer	4
Name	dbo.Name	50
ContactType	dbo.Name	50
Title	VarWChar	8
FirstName	dbo.Name	50
MiddleName	dbo.Name	50
LastName	dbo.Name	50
Suffix	VarWChar	10
PhoneNumber	dbo.Phone	25
PhoneNumberType	dbo.Name	50
EmailAddress	VarWChar	50
EmailPromotion	Integer	4

Objects that Purchasing.vVendorWithContacts depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
Person.BusinessEntityContact	Table	Cross-reference table mapping stores, vendors, and employees to people	1
Person.ContactType	Table	Lookup table containing the types of business entity contacts.	1
Person.EmailAddress	Table	Where to send a person email.	1
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	3
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	1
Person.PersonPhone	Table	Telephone number and type of a person.	1
dbo.Phone	User Defined Data Type		2
Person.PhoneNumberType	Table	Type of phone number of a person.	1
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	2
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	2
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	1

View Definition

CREATE VIEW [Purchasing].[vVendorWithContacts] AS 

SELECT 
    v.[BusinessEntityID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,pp.[PhoneNumber] 
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress] 
    ,p.[EmailPromotion] 
FROM [Purchasing].[Vendor] v
    INNER JOIN [Person].[BusinessEntityContact] bec 
    ON bec.[BusinessEntityID] = v.[BusinessEntityID]
    INNER JOIN [Person].ContactType ct
    ON ct.[ContactTypeID] = bec.[ContactTypeID]
    INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = bec.[PersonID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON ea.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

Related Objects