SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    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

-- CTE that gets Origin and Destination locations, assigning a "row number" to each location per order and type, ordered by OrderLocationID.
-- This allows us to select only the "first" location of each type.
with OriginAndDestinationLocations as
(
    select
        OrderLocations.OrderLocationID,
        OrderLocations.OrderFID,
        OrderLocations.OrderLocationAddressTypeFID,
        OrderLocationAddressType.TypeName,
        row_number() over( partition by OrderLocations.OrderFID, OrderLocations.OrderLocationAddressTypeFID order by OrderLocations.OrderLocationID asc ) as RowNumber
    from OrderLocations
    inner join OrderLocationAddressType on
        OrderLocations.OrderLocationAddressTypeFID = OrderLocationAddressType.OrderLocationAddressTypeID
    where OrderLocationAddressType.TypeName in ( 'Origin', 'Destination' )
)
select
    OrderFID = Orders.PriKey,
    -- Seperated Origin Address
    OriginAddress1 =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationAddress.Address1
            else OriginAddress.Address1
        end,
    OriginAddress2 =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationAddress.Address2
            else OriginAddress.Address2
        end,
    OriginAddress3 =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationAddress.Address3
            else OriginAddress.Address3
        end,
    -- Combined Origin Address
    OriginAddress =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then dbo.BuildAddressFromAddressParts( OriginLocationAddress.Address1, OriginLocationAddress.Address2, OriginLocationAddress.Address3 )
            else dbo.BuildAddressFromAddressParts( OriginAddress.Address1, OriginAddress.Address2, OriginAddress.Address3 )
        end,
    OriginAddressFull =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then
                dbo.BuildAddressCityStateCountryPostalCode
                (
                    dbo.BuildAddressFromAddressParts( OriginLocationAddress.Address1, OriginLocationAddress.Address2, OriginLocationAddress.Address3 ),
                    OriginLocationAddress.City,
                    OriginLocationAddress.State,
                    OriginLocationAddress.CountryCodeStandardFID,
                    OriginLocationAddress.PostalCode
                )
            else
                dbo.BuildAddressCityStateCountryPostalCode
                (
                    dbo.BuildAddressFromAddressParts( OriginAddress.Address1, OriginAddress.Address2, OriginAddress.Address3 ),
                    OriginAddress.City,
                    OriginAddress.State,
                    OriginAddress.CountryCodeStandardFID,
                    OriginAddress.PostalCode
                )
        end,
    OriginCity =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationAddress.City
            else OriginAddress.City
        end,
    OriginState =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationAddress.State
            else OriginAddress.State
        end,
    OriginCountryCodeStandardFID =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationAddress.CountryCodeStandardFID
            else OriginAddress.CountryCodeStandardFID
        end,
    OriginCountry =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationCountryCodeStandard.CountryName
            else OriginCountryCodeStandard.CountryName
        end,
    OriginPostalCode =
        case
            when OriginLocationAddress.OrderLocationAddressID is not null then OriginLocationAddress.PostalCode
            else OriginAddress.PostalCode
        end,
    -- Seperated Destination Address
    DestinationAddress1    =
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationAddress.Address1
            when MovingToAddress.OrderAddressID is null then DestinationAddress.Address1
            else MovingToAddress.Address1
        end,
    DestinationAddress2    =
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationAddress.Address2
            when MovingToAddress.OrderAddressID is null then DestinationAddress.Address2
            else MovingToAddress.Address2
        end,
    DestinationAddress3    =
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationAddress.Address3
            when MovingToAddress.OrderAddressID is null then DestinationAddress.Address3
            else MovingToAddress.Address3
        end,
    -- Combined Destination Address
    DestinationAddress =    
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then
                dbo.BuildAddressFromAddressParts( DestinationLocationAddress.Address1, DestinationLocationAddress.Address2, DestinationLocationAddress.Address3 )
            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 DestinationLocationAddress.OrderLocationAddressID is not null then
                    dbo.BuildAddressCityStateCountryPostalCode
                    (
                        dbo.BuildAddressFromAddressParts( DestinationLocationAddress.Address1, DestinationLocationAddress.Address2, DestinationLocationAddress.Address3 ),
                        DestinationLocationAddress.City,
                        DestinationLocationAddress.State,
                        DestinationLocationAddress.CountryCodeStandardFID,
                        DestinationLocationAddress.PostalCode
                    )            
            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 DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationAddress.City
            when MovingToAddress.OrderAddressID is null then DestinationAddress.City
            else MovingToAddress.City
        end,
    DestinationState =
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationAddress.State
            when MovingToAddress.OrderAddressID is null then DestinationAddress.State
            else MovingToAddress.State
        end,
    DestinationCountryCodeStandardFID =
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationAddress.CountryCodeStandardFID
            when MovingToAddress.OrderAddressID is null then DestinationAddress.CountryCodeStandardFID
            else MovingToAddress.CountryCodeStandardFID
        end,
    DestinationCountry =
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationCountryCodeStandard.CountryName
            when MovingToAddress.OrderAddressID is null then DestinationCountryCodeStandard.CountryName
            else MovingToCountryCodeStandard.CountryName
        end,
    DestinationPostalCode =
        case
            when DestinationLocationAddress.OrderLocationAddressID is not null then DestinationLocationAddress.PostalCode
            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 )
-- Get the first origin location on the order. This is a workaround for case MOV-7500. As of the time of that case, the MS O&I Sales app allows multiple Origin and Destination locations to be added to an order.
left outer join OriginAndDestinationLocations as OriginLocation on
    OriginLocation.TypeName = 'Origin' and
    OriginLocation.OrderFID = Orders.PriKey and
    OriginLocation.RowNumber = 1
left outer join OrderLocationAddress OriginLocationAddress on OriginLocationAddress.OrderLocationFID = OriginLocation.OrderLocationID
left outer join CountryCodeStandard OriginLocationCountryCodeStandard on OriginLocationCountryCodeStandard.CountryCodeStandardID = OriginLocationAddress.CountryCodeStandardFID
-- Get the first destination location on the order. This is a workaround for case MOV-7500. As of the time of that case, the MS O&I Sales app allows multiple Origin and Destination locations to be added to an order.
left outer join OriginAndDestinationLocations as DestinationLocation on
    DestinationLocation.TypeName = 'Destination' and
    DestinationLocation.OrderFID = Orders.PriKey and
    DestinationLocation.RowNumber = 1
left outer join OrderLocationAddress DestinationLocationAddress on DestinationLocationAddress.OrderLocationFID = DestinationLocation.OrderLocationID
left outer join CountryCodeStandard DestinationLocationCountryCodeStandard on DestinationLocationCountryCodeStandard.CountryCodeStandardID = DestinationLocationAddress.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