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

CREATE view [dbo].[InternationalCarrierAddresses]
(
    InternationalCarrierFID,
    Address,
    City,
    State,
    CountryCodeStandardFID,
    PostalCode
)
as

select
    InternationalCarrierFID = InternationalCarrier.InternationalCarrierID,
    Address = dbo.BuildAddressFromAddressParts
    (
        InternationalCarrierAddress.Address1,
        InternationalCarrierAddress.Address2,
        InternationalCarrierAddress.Address3
    ),
    City = InternationalCarrierAddress.City,
    State = InternationalCarrierAddress.State,
    CountryCodeStandardFID = InternationalCarrierAddress.CountryCodeStandardFID,
    PostalCode = InternationalCarrierAddress.PostalCode
from InternationalCarrier
left outer join AddressType on AddressType.TypeName = 'Main'
left outer join InternationalCarrierAddress on
(
    InternationalCarrier.InternationalCarrierID = InternationalCarrierAddress.InternationalCarrierFID and
    AddressType.AddressTypeID = InternationalCarrierAddress.AddressTypeFID
)
                                            
GO
GRANT SELECT ON  [dbo].[InternationalCarrierAddresses] TO [MssExec]
GRANT INSERT ON  [dbo].[InternationalCarrierAddresses] TO [MssExec]
GRANT DELETE ON  [dbo].[InternationalCarrierAddresses] TO [MssExec]
GRANT UPDATE ON  [dbo].[InternationalCarrierAddresses] TO [MssExec]
GO