Views [dbo].[InternationalBOLConsigneePhoneNumbers]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnNo
Created6:18:19 PM Tuesday, February 27, 2007
Last Modified2:29:34 AM Saturday, February 4, 2017
Columns
Name
InternationalBOLConsigneeFID
PhoneCountryCodeStandardFID
PhoneAreaCode
PhoneLocalNumber
PhoneNumber
PhoneExtension
FaxCountryCodeStandardFID
FaxAreaCode
FaxLocalNumber
FaxNumber
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses