SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/MoversSuite/Common/SQL/Views/AgentAddresses.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view returns the Agent Address information
*
*/

CREATE view [dbo].[AgentAddresses]
(
    AgentFID,
    Address1,
    Address2,
    Address3,
    Address,
    City,
    State,
    Country,
    CountryCodeStandardFID,
    PostalCode
)
as

select
    AgentFID = Agent.AgentPriKey,
    AgentAddress.Address1,
    AgentAddress.Address2,
    AgentAddress.Address3,
    Address = dbo.BuildAddressFromAddressParts( AgentAddress.Address1, AgentAddress.Address2, AgentAddress.Address3 ),
    City = AgentAddress.City,
    State = AgentAddress.State,
    Country = CountryCodeStandard.CountryName,
    CountryCodeStandardFID = AgentAddress.CountryCodeStandardFID,
    PostalCode = AgentAddress.PostalCode
from Agent
left outer join AddressType on AddressType.TypeName = 'Main'
left outer join AgentAddress on
(
    AgentAddress.AgentFID = Agent.AgentPriKey and
    AgentAddress.AddressTypeFID = AddressType.AddressTypeID
)
left outer join CountryCodeStandard on CountryCodeStandard.CountryCodeStandardID = AgentAddress.CountryCodeStandardFID

GO
GRANT SELECT ON  [dbo].[AgentAddresses] TO [MssExec]
GRANT INSERT ON  [dbo].[AgentAddresses] TO [MssExec]
GRANT DELETE ON  [dbo].[AgentAddresses] TO [MssExec]
GRANT UPDATE ON  [dbo].[AgentAddresses] TO [MssExec]
GO