SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    Description: This view returns the International BOL Consginee Phone Number of the "Main" and "Fax" type
*
*/

CREATE view [dbo].[InternationalBOLConsigneePhoneNumbers]
(
    InternationalBOLConsigneeFID,
    PhoneCountryCodeStandardFID,
    PhoneAreaCode,
    PhoneLocalNumber,
    PhoneNumber,
    PhoneExtension,
    FaxCountryCodeStandardFID,
    FaxAreaCode,
    FaxLocalNumber,
    FaxNumber
)
as

select
    InternationalBOLConsigneeFID = InternationalBOLConsigneeID,
    PhoneCountryCodeStandardFID = PhoneNumber.CountryCodeStandardFID,
    PhoneAreaCode = PhoneNumber.AreaCode,
    PhoneLocalNumber = PhoneNumber.LocalNumber,
    PhoneNumber = dbo.GetFormattedPhoneNumber( PhoneNumber.CountryCodeStandardFID, PhoneNumber.AreaCode, PhoneNumber.LocalNumber ),
    PhoneExtension = PhoneNumber.Extension,
    FaxCountryCodeStandardFID = FaxNumber.CountryCodeStandardFID,
    FaxAreaCode = FaxNumber.AreaCode,
    FaxLocalNumber = FaxNumber.LocalNumber,
    FaxNumber = dbo.GetFormattedPhoneNumber( FaxNumber.CountryCodeStandardFID, FaxNumber.AreaCode, FaxNumber.LocalNumber )
from InternationalBOLConsignee
    left outer join PhoneType as MainType on MainType.TypeName = 'Main'
    left outer join InternationalBOLConsigneePhoneNumber as PhoneNumber    on
    (
        PhoneNumber.InternationalBOLConsigneeFID = InternationalBOLConsignee.InternationalBOLConsigneeID and
        PhoneNumber.PhoneTypeFID = MainType.PhoneTypeID
    )
    left outer join PhoneType as FaxType on FaxType.TypeName = 'Fax'
    left outer join InternationalBOLConsigneePhoneNumber as FaxNumber on
    (
        FaxNumber.InternationalBOLConsigneeFID = InternationalBOLConsignee.InternationalBOLConsigneeID and
        FaxNumber.PhoneTypeFID = FaxType.PhoneTypeID
    )
GO
GRANT SELECT ON  [dbo].[InternationalBOLConsigneePhoneNumbers] TO [MssExec]
GRANT INSERT ON  [dbo].[InternationalBOLConsigneePhoneNumbers] TO [MssExec]
GRANT DELETE ON  [dbo].[InternationalBOLConsigneePhoneNumbers] TO [MssExec]
GRANT UPDATE ON  [dbo].[InternationalBOLConsigneePhoneNumbers] TO [MssExec]
GO