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