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