SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/MoversSuite/International/SQL/Views/InternationalBookingNumberPhoneNumbers.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view returns the International Booking Number Phone Number of the "Main" and "Fax" type
*
*/

CREATE view [dbo].[InternationalBookingNumberPhoneNumbers]
(
    InternationalBookingNumberFID,
    PickupCountryCodeStandardFID,
    PickupAreaCode,
    PickupLocalNumber,
    PickupPhoneNumber,
    PickupExtension,
    ReturnCountryCodeStandardFID,
    ReturnAreaCode,
    ReturnLocalNumber,
    ReturnPhoneNumber,
    ReturnExtension
)
as

select
    InternationalBookingNumberFID = InternationalBookingNumberID,
    PickupCountryCodeStandardFID = PickupNumber.CountryCodeStandardFID,
    PickupAreaCode = PickupNumber.AreaCode,
    PickupLocalNumber = PickupNumber.LocalNumber,
    PickupPhoneNumber = dbo.GetFormattedPhoneNumber( PickupNumber.CountryCodeStandardFID, PickupNumber.AreaCode, PickupNumber.LocalNumber ),
    PickupExtension = PickupNumber.Extension,
    ReturnCountryCodeStandardFID = ReturnNumber.CountryCodeStandardFID,
    ReturnAreaCode = ReturnNumber.AreaCode,
    ReturnLocalNumber = ReturnNumber.LocalNumber,
    ReturnPhoneNumber = dbo.GetFormattedPhoneNumber( ReturnNumber.CountryCodeStandardFID, ReturnNumber.AreaCode, ReturnNumber.LocalNumber ),
    ReturnExtension = ReturnNumber.Extension
from InternationalBookingNumber
    left outer join PhoneType as PickupType on PickupType.TypeName = 'Pickup'
    left outer join InternationalBookingNumberPhoneNumber as PickupNumber on
    (
        PickupNumber.InternationalBookingNumberFID = InternationalBookingNumber.InternationalBookingNumberID and
        PickupNumber.PhoneTypeFID = PickupType.PhoneTypeID
    )
    left outer join PhoneType as ReturnType on ReturnType.TypeName = 'Return'
    left outer join InternationalBookingNumberPhoneNumber as ReturnNumber on
    (
        ReturnNumber.InternationalBookingNumberFID = InternationalBookingNumber.InternationalBookingNumberID and
        ReturnNumber.PhoneTypeFID = ReturnType.PhoneTypeID
    )

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