SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/MoversSuite/International/SQL/Views/InternationalBookingNumberAddresses.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view returns the International Booking Number information for both pickup and return
*/

CREATE view [dbo].[InternationalBookingNumberAddresses]
(
    InternationalBookingNumberFID,
    PickupContact,
    PickupAddress,
    PickupCity,
    PickupState,
    PickupCountryCodeStandardFID,
    PickupPostalCode,
    ReturnContact,
    ReturnAddress,
    ReturnCity,
    ReturnState,
    ReturnCountryCodeStandardFID,
    ReturnPostalCode
)
as

select
    InternationalBookingNumberFID = InternationalBookingNumber.InternationalBookingNumberID,
    PickupContact = PickupAddress.Contact,
    PickupAddress = dbo.BuildAddressFromAddressParts
    (
        PickupAddress.Address1,
        PickupAddress.Address2,
        PickupAddress.Address3
    ),
    PickupCity = PickupAddress.City,
    PickupState = PickupAddress.State,
    PickupCountryCodeStandardFID = PickupAddress.CountryCodeStandardFID,
    PickupPostalCode = PickupAddress.PostalCode,
    ReturnContact = ReturnAddress.Contact,
    ReturnAddress = dbo.BuildAddressFromAddressParts
    (
        ReturnAddress.Address1,
        ReturnAddress.Address2,
        ReturnAddress.Address3
    ),
    ReturnCity = ReturnAddress.City,
    ReturnState = ReturnAddress.State,
    ReturnCountryCodeStandardFID = ReturnAddress.CountryCodeStandardFID,
    ReturnPostalCode = ReturnAddress.PostalCode
from InternationalBookingNumber
left outer join AddressType as PickupType on PickupType.TypeName = 'Pickup'
left outer join InternationalBookingNumberAddress as PickupAddress on
(
    InternationalBookingNumber.InternationalBookingNumberID = PickupAddress.InternationalBookingNumberFID and
    PickupType.AddressTypeID = PickupAddress.AddressTypeFID
)
left outer join AddressType as ReturnType on ReturnType.TypeName = 'Return'
left outer join InternationalBookingNumberAddress as ReturnAddress on
(
    InternationalBookingNumber.InternationalBookingNumberID = ReturnAddress.InternationalBookingNumberFID and
    ReturnType.AddressTypeID = ReturnAddress.AddressTypeFID
)
                                            
GO
GRANT SELECT ON  [dbo].[InternationalBookingNumberAddresses] TO [MssExec]
GRANT INSERT ON  [dbo].[InternationalBookingNumberAddresses] TO [MssExec]
GRANT DELETE ON  [dbo].[InternationalBookingNumberAddresses] TO [MssExec]
GRANT UPDATE ON  [dbo].[InternationalBookingNumberAddresses] TO [MssExec]
GO