/**
*    
*    Description: This view returns the International Linehaul Carrier Address information
*
*/

create view [dbo].[InternationalLinehaulCarrierAddresses]
(
    InternationalLinehaulCarrierFID,
    Address1,
    Address2,
    Address3,
    Address,
    City,
    State,
    Country,
    CountryCodeStandardFID,
    PostalCode
)
as

select
    InternationalLinehaulCarrierFID = InternationalLinehaulCarrier.InternationalLinehaulCarrierID,
    InternationalLinehaulCarrierAddress.Address1,
    InternationalLinehaulCarrierAddress.Address2,
    InternationalLinehaulCarrierAddress.Address3,
    Address = dbo.BuildAddressFromAddressParts( InternationalLinehaulCarrierAddress.Address1, InternationalLinehaulCarrierAddress.Address2, InternationalLinehaulCarrierAddress.Address3 ),
    City = InternationalLinehaulCarrierAddress.City,
    State = InternationalLinehaulCarrierAddress.State,
    Country = CountryCodeStandard.CountryName,
    CountryCodeStandardFID = InternationalLinehaulCarrierAddress.CountryCodeStandardFID,
    PostalCode = InternationalLinehaulCarrierAddress.PostalCode
from InternationalLinehaulCarrier
left outer join AddressType on AddressType.TypeName = 'Main'
left outer join InternationalLinehaulCarrierAddress on
(
    InternationalLinehaulCarrierAddress.InternationalLinehaulCarrierFID = InternationalLinehaulCarrier.InternationalLinehaulCarrierID and
    InternationalLinehaulCarrierAddress.AddressTypeFID = AddressType.AddressTypeID
)
left outer join CountryCodeStandard on CountryCodeStandard.CountryCodeStandardID = InternationalLinehaulCarrierAddress.CountryCodeStandardFID
GO
GRANT SELECT ON  [dbo].[InternationalLinehaulCarrierAddresses] TO [MssExec]
GRANT INSERT ON  [dbo].[InternationalLinehaulCarrierAddresses] TO [MssExec]
GRANT DELETE ON  [dbo].[InternationalLinehaulCarrierAddresses] TO [MssExec]
GRANT UPDATE ON  [dbo].[InternationalLinehaulCarrierAddresses] TO [MssExec]
GO