SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    Description: This view returns the International Booking Number Phone Number of the "Pickup", "Return" and "Transit" types
*
*/

CREATE view [dbo].[InternationalBookingNumberPhoneNumbers]
(
    InternationalBookingNumberFID,
    PickupCountryCodeStandardFID,
    PickupAreaCode,
    PickupLocalNumber,
    PickupPhoneNumber,
    PickupExtension,
    ReturnCountryCodeStandardFID,
    ReturnAreaCode,
    ReturnLocalNumber,
    ReturnPhoneNumber,
    ReturnExtension,
    TransitCountryCodeStandardFID,
    TransitAreaCode,
    TransitLocalNumber,
    TransitPhoneNumber,
    TransitExtension
)
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,
    TransitCountryCodeStandardFID = TransitNumber.CountryCodeStandardFID,
    TransitAreaCode = TransitNumber.AreaCode,
    TransitLocalNumber = TransitNumber.LocalNumber,
    TransitPhoneNumber = dbo.GetFormattedPhoneNumber( TransitNumber.CountryCodeStandardFID, TransitNumber.AreaCode, TransitNumber.LocalNumber ),
    TransitExtension = TransitNumber.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
    )
    left outer join PhoneType as TransitType on TransitType.TypeName = 'Transit'
    left outer join InternationalBookingNumberPhoneNumber as TransitNumber on
    (
        TransitNumber.InternationalBookingNumberFID = InternationalBookingNumber.InternationalBookingNumberID and
        TransitNumber.PhoneTypeFID = TransitType.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