Views [dbo].[OrderPhoneNumbers]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnNo
Created6:18:17 PM Tuesday, February 27, 2007
Last Modified2:29:34 AM Saturday, February 4, 2017
Columns
Name
OrderFID
OriginHomeCountryCodeStandardFID
OriginHomeAreaCode
OriginHomeLocalNumber
OriginHomePhoneNumber
OriginWorkCountryCodeStandardFID
OriginWorkAreaCode
OriginWorkLocalNumber
OriginWorkPhoneNumber
OriginWorkExtension
DestinationHomeCountryCodeStandardFID
DestinationHomeAreaCode
DestinationHomeLocalNumber
DestinationHomePhoneNumber
DestinationWorkCountryCodeStandardFID
DestinationWorkAreaCode
DestinationWorkLocalNumber
DestinationWorkPhoneNumber
DestinationWorkExtension
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/MoversSuite/Common/SQL/Views/OrderPhoneNumbers.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view return the OrderPhoneNumber 'origin' phone information and
*                 either the 'destination' phone information or 'moving to' phone information if it exists.
*
*/


CREATE view [dbo].[OrderPhoneNumbers]
(
    OrderFID,
    OriginHomeCountryCodeStandardFID,
    OriginHomeAreaCode,
    OriginHomeLocalNumber,
    OriginHomePhoneNumber,
    OriginWorkCountryCodeStandardFID,
    OriginWorkAreaCode,
    OriginWorkLocalNumber,
    OriginWorkPhoneNumber,
    OriginWorkExtension,
    DestinationHomeCountryCodeStandardFID,
    DestinationHomeAreaCode,
    DestinationHomeLocalNumber,
    DestinationHomePhoneNumber,
    DestinationWorkCountryCodeStandardFID,
    DestinationWorkAreaCode,
    DestinationWorkLocalNumber,
    DestinationWorkPhoneNumber,
    DestinationWorkExtension
)
as

select
    OrderFID = Orders.PriKey,
    
    -- Seperated Origin Home Phone
    OriginHomeCountryCodeStandardFID = ohPhoneNumber.CountryCodeStandardFID,
    OriginHomeAreaCode = ohPhoneNumber.AreaCode,
    OriginHomeLocalNumber = ohPhoneNumber.LocalNumber,
    
    -- Combined Origin Home Phone
    OriginHomePhoneNumber = dbo.GetFormattedPhoneNumber( ohPhoneNumber.CountryCodeStandardFID, ohPhoneNumber.AreaCode, ohPhoneNumber.LocalNumber ),
    
    -- Seperated Origin Work Phone
    OriginWorkCountryCodeStandardFID = owPhoneNumber.CountryCodeStandardFID,
    OriginWorkAreaCode = owPhoneNumber.AreaCode,
    OriginWorkLocalNumber = owPhoneNumber.LocalNumber,
    
    -- Combined Origin Work Phone
    OriginWorkPhoneNumber = dbo.GetFormattedPhoneNumber( owPhoneNumber.CountryCodeStandardFID, owPhoneNumber.AreaCode, owPhoneNumber.LocalNumber ),
    OriginWorkExtension = owPhoneNumber.Extension,
    
    -- Seperated Destination Home Phone
    DestinationHomeCountryCodeStandardFID =
        case
            when mhPhoneNumber.OrderPhoneNumberID is null then dhPhoneNumber.CountryCodeStandardFID
            else mhPhoneNumber.CountryCodeStandardFID
        end,
    DestinationHomeAreaCode =
        case
            when mhPhoneNumber.OrderPhoneNumberID is null then dhPhoneNumber.AreaCode
            else mhPhoneNumber.AreaCode
        end,
    DestinationHomeLocalNumber =
        case
            when mhPhoneNumber.OrderPhoneNumberID is null then dhPhoneNumber.LocalNumber
            else mhPhoneNumber.LocalNumber
        end,
    
    -- Combined Destination Home Phone
    DestinationHomePhoneNumber =
        case
            when mhPhoneNumber.OrderPhoneNumberID is null
                then dbo.GetFormattedPhoneNumber( dhPhoneNumber.CountryCodeStandardFID, dhPhoneNumber.AreaCode, dhPhoneNumber.LocalNumber )
            else dbo.GetFormattedPhoneNumber( mhPhoneNumber.CountryCodeStandardFID, mhPhoneNumber.AreaCode, mhPhoneNumber.LocalNumber )
        end,
    
    -- Seperated Destination Work Phone
    DestinationWorkCountryCodeStandardFID =
        case
            when mwPhoneNumber.OrderPhoneNumberID is null then dwPhoneNumber.CountryCodeStandardFID
            else mwPhoneNumber.CountryCodeStandardFID
        end,
    DestinationWorkAreaCode =
        case
            when mwPhoneNumber.OrderPhoneNumberID is null then dwPhoneNumber.AreaCode
            else mwPhoneNumber.AreaCode
        end,
    DestinationWorkLocalNumber =
        case
            when mwPhoneNumber.OrderPhoneNumberID is null then dwPhoneNumber.LocalNumber
            else mwPhoneNumber.LocalNumber
        end,
    
    -- Combined Destination Work Phone
    DestinationWorkPhoneNumber =
        case
            when mwPhoneNumber.OrderPhoneNumberID is null
                then dbo.GetFormattedPhoneNumber( dwPhoneNumber.CountryCodeStandardFID, dwPhoneNumber.AreaCode, dwPhoneNumber.LocalNumber )
            else dbo.GetFormattedPhoneNumber( mwPhoneNumber.CountryCodeStandardFID, mwPhoneNumber.AreaCode, mwPhoneNumber.LocalNumber )
        end,
    DestinationWorkExtension =
        case
            when mwPhoneNumber.OrderPhoneNumberID is null
                then dwPhoneNumber.Extension
            else mwPhoneNumber.Extension
        end
from Orders
    --Origin home phone
    left outer join OrderPhoneType        ohPhoneType                on ( ohPhoneType.TypeName = 'Origin Home' )    
    left outer join OrderPhoneNumber    ohPhoneNumber            on ( ohPhoneNumber.OrderFID = Orders.Prikey and ohPhoneNumber.OrderPhoneTypeFID = ohPhoneType.OrderPhoneTypeID )
    --Origin work phone
    left outer join OrderPhoneType        owPhoneType                on ( owPhoneType.TypeName = 'Origin Work' )    
    left outer join OrderPhoneNumber    owPhoneNumber            on ( owPhoneNumber.OrderFID = Orders.Prikey and owPhoneNumber.OrderPhoneTypeFID = owPhoneType.OrderPhoneTypeID )
    --Destination home phone
    left outer join OrderPhoneType        dhPhoneType                on ( dhPhoneType.TypeName = 'Destination Home' )    
    left outer join OrderPhoneNumber    dhPhoneNumber            on ( dhPhoneNumber.OrderFID = Orders.Prikey and dhPhoneNumber.OrderPhoneTypeFID = dhPhoneType.OrderPhoneTypeID )
    --Destination work phone
    left outer join OrderPhoneType        dwPhoneType                on ( dwPhoneType.TypeName = 'Destination Work' )    
    left outer join OrderPhoneNumber    dwPhoneNumber            on ( dwPhoneNumber.OrderFID = Orders.Prikey and dwPhoneNumber.OrderPhoneTypeFID = dwPhoneType.OrderPhoneTypeID )
    --MovingTo home phone
    left outer join OrderPhoneType        mhPhoneType                on ( mhPhoneType.TypeName = 'Moving To Home' )    
    left outer join OrderPhoneNumber    mhPhoneNumber            on ( mhPhoneNumber.OrderFID = Orders.Prikey and mhPhoneNumber.OrderPhoneTypeFID = mhPhoneType.OrderPhoneTypeID )
    --MovingTo work phone
    left outer join OrderPhoneType        mwPhoneType                on ( mwPhoneType.TypeName = 'Moving To Work' )    
    left outer join OrderPhoneNumber    mwPhoneNumber            on ( mwPhoneNumber.OrderFID = Orders.Prikey and mwPhoneNumber.OrderPhoneTypeFID = mwPhoneType.OrderPhoneTypeID )
    
GO
GRANT SELECT ON  [dbo].[OrderPhoneNumbers] TO [MssExec]
GRANT INSERT ON  [dbo].[OrderPhoneNumbers] TO [MssExec]
GRANT DELETE ON  [dbo].[OrderPhoneNumbers] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrderPhoneNumbers] TO [MssExec]
GO
Uses