Views [dbo].[AgentsContactInformation]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnNo
Created6:18:18 PM Tuesday, February 27, 2007
Last Modified7:40:25 PM Wednesday, February 28, 2024
Columns
Name
OrderFID
BookingAgentFID
BookingAgentID
BookingAgentName
BookingAgentContact
BookingAgentPhoneNumber
BookingAgentPhoneExtension
BookingAgentFaxNumber
BookingAgentAddress
BookingAgentCity
BookingAgentState
BookingAgentCountry
BookingAgentPostalCode
BookingAgentMemo
OriginAgentFID
OriginAgentID
OriginAgentName
OriginAgentContact
OriginAgentPhoneNumber
OriginAgentPhoneExtension
OriginAgentFaxNumber
OriginAgentAddress
OriginAgentCity
OriginAgentState
OriginAgentCountry
OriginAgentPostalCode
OriginAgentMemo
HaulingAgentFID
HaulingAgentID
HaulingAgentName
HaulingAgentContact
HaulingAgentPhoneNumber
HaulingAgentPhoneExtension
HaulingAgentFaxNumber
HaulingAgentAddress
HaulingAgentCity
HaulingAgentState
HaulingAgentCountry
HaulingAgentPostalCode
HaulingAgentMemo
DestinationAgentFID
DestinationAgentID
DestinationAgentName
DestinationAgentContact
DestinationAgentPhoneNumber
DestinationAgentPhoneExtension
DestinationAgentFaxNumber
DestinationAgentAddress
DestinationAgentCity
DestinationAgentState
DestinationAgentCountry
DestinationAgentPostalCode
DestinationAgentMemo
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    Description: This view return the OrderAddress 'origin' address and
*                 either the 'destination' address or 'moving to' address if it exists.
*
*/


CREATE view [dbo].[AgentsContactInformation]
(
    OrderFID,
    BookingAgentFID,
    BookingAgentID,
    BookingAgentName,
    BookingAgentContact,
    BookingAgentPhoneNumber,
    BookingAgentPhoneExtension,
    BookingAgentFaxNumber,
    BookingAgentAddress,
    BookingAgentCity,
    BookingAgentState,
    BookingAgentCountry,
    BookingAgentPostalCode,
    BookingAgentMemo,
    
    OriginAgentFID,
    OriginAgentID,
    OriginAgentName,
    OriginAgentContact,
    OriginAgentPhoneNumber,
    OriginAgentPhoneExtension,
    OriginAgentFaxNumber,
    OriginAgentAddress,
    OriginAgentCity,
    OriginAgentState,
    OriginAgentCountry,
    OriginAgentPostalCode,
    OriginAgentMemo,
    
    HaulingAgentFID,
    HaulingAgentID,
    HaulingAgentName,
    HaulingAgentContact,
    HaulingAgentPhoneNumber,
    HaulingAgentPhoneExtension,
    HaulingAgentFaxNumber,
    HaulingAgentAddress,
    HaulingAgentCity,
    HaulingAgentState,
    HaulingAgentCountry,
    HaulingAgentPostalCode,
    HaulingAgentMemo,
    
    DestinationAgentFID,
    DestinationAgentID,
    DestinationAgentName,
    DestinationAgentContact,
    DestinationAgentPhoneNumber,
    DestinationAgentPhoneExtension,
    DestinationAgentFaxNumber,
    DestinationAgentAddress,
    DestinationAgentCity,
    DestinationAgentState,
    DestinationAgentCountry,
    DestinationAgentPostalCode,
    DestinationAgentMemo
)
as

select
    OrderFID = Orders.PriKey,
    
    --Booking Agent Information
    BookingAgentFID                    = BookingAgent.AgentPriKey,
    BookingAgentID                    = BookingAgent.AgentID,
    BookingAgentName                = BookingAgent.Name,
    BookingAgentContact                = BAgentContact.Contact,
    DestinationAgentPhoneNumber        = BookingAgentPhone.PhoneNumber,
    DestinationAgentPhoneExtension    = BookingAgentPhone.PhoneExtension,
    DestinationAgentFaxNumber        = BookingAgentPhone.FaxNumber,
    BookingAgentAddress                = BookingAgentAddress.Address,
    BookingAgentCity                = BookingAgentAddress.City,
    BookingAgentState                = BookingAgentAddress.State,
    BookingAgentCountry                = BookingAgentAddress.Country,
    BookingAgentPostalCode            = BookingAgentAddress.PostalCode,
    BookingAgentMemo                = BookingAgent.Memo,
    
    --Origin Agent Information
    OriginAgentFID                = OriginAgent.AgentPriKey,
    OriginAgentID                = OriginAgent.AgentID,
    OriginAgentName                = OriginAgent.Name,
    OriginAgentContact            = OAgentContact.Contact,
    OriginAgentPhoneNumber        = OriginAgentPhone.PhoneNumber,
    OriginAgentPhoneExtension    = OriginAgentPhone.PhoneExtension,
    OriginAgentFaxNumber        = OriginAgentPhone.FaxNumber,
    OriginAgentAddress            = OriginAgentAddress.Address,
    OriginAgentCity                = OriginAgentAddress.City,
    OriginAgentState            = OriginAgentAddress.State,
    OriginAgentCountry            = OriginAgentAddress.Country,
    OriginAgentPostalCode        = OriginAgentAddress.PostalCode,
    OriginAgentMemo                = OriginAgent.Memo,
    
    --Hauling Agent Information
    HaulingAgentFID                = HaulingAgent.AgentPriKey,
    HaulingAgentID                = HaulingAgent.AgentID,
    HaulingAgentName            = HaulingAgent.Name,
    HaulingAgentContact            = HAgentContact.Contact,
    HaulingAgentPhoneNumber        = HaulingAgentPhone.PhoneNumber,
    HaulingAgentPhoneExtension    = HaulingAgentPhone.PhoneExtension,
    HaulingAgentFaxNumber        = HaulingAgentPhone.FaxNumber,
    HaulingAgentAddress            = HaulingAgentAddress.Address,
    HaulingAgentCity            = HaulingAgentAddress.City,
    HaulingAgentState            = HaulingAgentAddress.State,
    HaulingAgentCountry            = HaulingAgentAddress.Country,
    HaulingAgentPostalCode        = HaulingAgentAddress.PostalCode,
    HaulingAgentMemo            = HaulingAgent.Memo,
    
    --Destination Agent Information
    DestinationAgentFID                = DestinationAgent.AgentPriKey,
    DestinationAgentID                = DestinationAgent.AgentID,
    DestinationAgentName            = DestinationAgent.Name,
    DestinationAgentContact            = DAgentContact.Contact,
    DestinationAgentPhoneNumber        = DestinationAgentPhone.PhoneNumber,
    DestinationAgentPhoneExtension    = DestinationAgentPhone.PhoneExtension,
    DestinationAgentFaxNumber        = DestinationAgentPhone.FaxNumber,
    DestinationAgentAddress            = DestinationAgentAddress.Address,
    DestinationAgentCity            = DestinationAgentAddress.City,
    DestinationAgentState            = DestinationAgentAddress.State,
    DestinationAgentCountry            = DestinationAgentAddress.Country,
    DestinationAgentPostalCode        = DestinationAgentAddress.PostalCode,
    DestinationAgentMemo            = DestinationAgent.Memo
from
    Orders
    left outer join Agent                as BookingAgent                on ( Orders.BookAgent = BookingAgent.AgentPriKey )
    left outer join AgentAddresses        as BookingAgentAddress        on ( Orders.BookAgent = BookingAgentAddress.AgentFID )
    left outer join AgentPhoneNumbers    as BookingAgentPhone        on ( Orders.BookAgent = BookingAgentPhone.AgentFID )
    left outer join AgentContact BAgentContact on BookingAgent.AgentPriKey = BAgentContact.AgentFID and BAgentContact.PrimaryContact = 1
    
    left outer join Agent                as OriginAgent                on ( Orders.OrgAgent = OriginAgent.AgentPriKey )
    left outer join AgentAddresses        as OriginAgentAddress        on ( Orders.OrgAgent = OriginAgentAddress.AgentFID )
    left outer join AgentPhoneNumbers    as OriginAgentPhone            on ( Orders.OrgAgent = OriginAgentPhone.AgentFID )
    left outer join AgentContact OAgentContact on OriginAgent.AgentPriKey = OAgentContact.AgentFID and OAgentContact.PrimaryContact = 1
    
    left outer join Agent                as HaulingAgent                on ( Orders.HaulAgent = HaulingAgent.AgentPriKey )
    left outer join AgentAddresses        as HaulingAgentAddress        on ( Orders.HaulAgent = HaulingAgentAddress.AgentFID )
    left outer join AgentPhoneNumbers    as HaulingAgentPhone        on ( Orders.HaulAgent = HaulingAgentPhone.AgentFID )
    left outer join AgentContact HAgentContact on HaulingAgent.AgentPriKey = HAgentContact.AgentFID and HAgentContact.PrimaryContact = 1
    
    left outer join Agent                as DestinationAgent            on ( Orders.DestAgent = DestinationAgent.AgentPriKey )
    left outer join AgentAddresses        as DestinationAgentAddress    on ( Orders.DestAgent = DestinationAgentAddress.AgentFID )
    left outer join AgentPhoneNumbers    as DestinationAgentPhone    on ( Orders.DestAgent = DestinationAgentPhone.AgentFID )
    left outer join AgentContact DAgentContact on DestinationAgent.AgentPriKey = DAgentContact.AgentFID and DAgentContact.PrimaryContact = 1
GO
GRANT SELECT ON  [dbo].[AgentsContactInformation] TO [MssExec]
GRANT INSERT ON  [dbo].[AgentsContactInformation] TO [MssExec]
GRANT DELETE ON  [dbo].[AgentsContactInformation] TO [MssExec]
GRANT UPDATE ON  [dbo].[AgentsContactInformation] TO [MssExec]
GO
Uses