SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/Reports/SQL/Views/OrderAddresses.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view return the OrderAddress 'origin' address and
*                 either the 'destination' address or 'moving to' address if it exists.
*
*/

CREATE view [dbo].[OrderAddresses]
(
    OrderFID,
    OriginAddress1,
    OriginAddress2,
    OriginAddress3,
    OriginAddress,
    OriginAddressFull,
    OriginCity,
    OriginState,
    OriginCountryCodeStandardFID,            
    OriginCountry,        
    OriginPostalCode,
    DestinationAddress1,
    DestinationAddress2,
    DestinationAddress3,    
    DestinationAddress,
    DestinationAddressFull,
    DestinationCity,
    DestinationState,
    DestinationCountryCodeStandardFID,
    DestinationCountry,
    DestinationPostalCode
)
as

select
    OrderFID = Orders.PriKey,
    -- Seperated Origin Address
    OriginAddress1 = OriginAddress.Address1,
    OriginAddress2 = OriginAddress.Address2,
    OriginAddress3 = OriginAddress.Address3,
    -- Combined Origin Address
    OriginAddress = dbo.BuildAddressFromAddressParts( OriginAddress.Address1, OriginAddress.Address2, OriginAddress.Address3 ),
    OriginAddressFull = dbo.BuildAddressCityStateCountryPostalCode
        (
            dbo.BuildAddressFromAddressParts( OriginAddress.Address1, OriginAddress.Address2, OriginAddress.Address3 ),
            OriginAddress.City,
            OriginAddress.State,
            OriginAddress.CountryCodeStandardFID,
            OriginAddress.PostalCode
        ),
    OriginCity = OriginAddress.City,
    OriginState = OriginAddress.State,
    OriginCountryCodeStandardFID = OriginAddress.CountryCodeStandardFID,
    OriginCountry = OriginCountryCodeStandard.CountryName,
    OriginPostalCode = OriginAddress.PostalCode,
    -- Seperated Destination Address
    DestinationAddress1    =
        case
            when MovingToAddress.OrderAddressID is null then DestinationAddress.Address1
            else MovingToAddress.Address1
        end,
    DestinationAddress2    =
        case
            when MovingToAddress.OrderAddressID is null then DestinationAddress.Address2
            else MovingToAddress.Address2
        end,
    DestinationAddress3    =
        case
            when MovingToAddress.OrderAddressID is null then DestinationAddress.Address3
            else MovingToAddress.Address3
        end,
    -- Combined Destination Address
    DestinationAddress =    
        case
            when MovingToAddress.OrderAddressID is null
                then dbo.BuildAddressFromAddressParts( DestinationAddress.Address1, DestinationAddress.Address2, DestinationAddress.Address3 )
            else dbo.BuildAddressFromAddressParts( MovingToAddress.Address1, MovingToAddress.Address2, MovingToAddress.Address3 )
        end,
    DestinationAddressFull =    
        case
            when MovingToAddress.OrderAddressID is null
                then dbo.BuildAddressCityStateCountryPostalCode
                    (
                        dbo.BuildAddressFromAddressParts( DestinationAddress.Address1, DestinationAddress.Address2, DestinationAddress.Address3 ),
                        DestinationAddress.City,
                        DestinationAddress.State,
                        DestinationAddress.CountryCodeStandardFID,
                        DestinationAddress.PostalCode
                    )
            else dbo.BuildAddressCityStateCountryPostalCode
                    (
                        dbo.BuildAddressFromAddressParts( MovingToAddress.Address1, MovingToAddress.Address2, MovingToAddress.Address3 ),
                        MovingToAddress.City,
                        MovingToAddress.State,
                        MovingToAddress.CountryCodeStandardFID,
                        MovingToAddress.PostalCode
                    )
        end,
    DestinationCity =
        case
            when MovingToAddress.OrderAddressID is null then DestinationAddress.City
            else MovingToAddress.City
        end,
    DestinationState =
        case
            when MovingToAddress.OrderAddressID is null then DestinationAddress.State
            else MovingToAddress.State
        end,
    DestinationCountryCodeStandardFID =
        case
            when MovingToAddress.OrderAddressID is null then DestinationAddress.CountryCodeStandardFID
            else MovingToAddress.CountryCodeStandardFID
        end,
    DestinationCountry =
        case
            when MovingToAddress.OrderAddressID is null then DestinationCountryCodeStandard.CountryName
            else MovingToCountryCodeStandard.CountryName
        end,
    DestinationPostalCode =
        case when MovingToAddress.OrderAddressID is null then DestinationAddress.PostalCode
            else MovingToAddress.PostalCode
        end
from Orders
left outer join AddressType            as OriginAddressType on ( OriginAddressType.TypeName = 'Origin' )    
left outer join OrderAddress         as OriginAddress on ( OriginAddress.OrderFID = Orders.Prikey and OriginAddress.AddressTypeFID = OriginAddressType.AddressTypeID )
left outer join CountryCodeStandard as OriginCountryCodeStandard on ( OriginCountryCodeStandard.CountryCodeStandardID = OriginAddress.CountryCodeStandardFID )
left outer join AddressType            as DestinationAddressType on ( DestinationAddressType.TypeName = 'Destination' )    
left outer join OrderAddress         as DestinationAddress on ( DestinationAddress.OrderFID = Orders.Prikey and DestinationAddress.AddressTypeFID = DestinationAddressType.AddressTypeID )
left outer join CountryCodeStandard as DestinationCountryCodeStandard on ( DestinationCountryCodeStandard.CountryCodeStandardID = DestinationAddress.CountryCodeStandardFID )
left outer join AddressType            as MovingToAddressType on ( MovingToAddressType.TypeName = 'MovingTo' )    
left outer join OrderAddress         as MovingToAddress on ( MovingToAddress.OrderFID = Orders.Prikey and MovingToAddress.AddressTypeFID = MovingToAddressType.AddressTypeID )
left outer join CountryCodeStandard as MovingToCountryCodeStandard on ( MovingToCountryCodeStandard.CountryCodeStandardID = MovingToAddress.CountryCodeStandardFID )

GO
GRANT SELECT ON  [dbo].[OrderAddresses] TO [MssExec]
GRANT INSERT ON  [dbo].[OrderAddresses] TO [MssExec]
GRANT DELETE ON  [dbo].[OrderAddresses] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrderAddresses] TO [MssExec]
GO