Views [dbo].[OrderAddresses]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnNo
Created6:18:19 PM Tuesday, February 27, 2007
Last Modified2:30:42 AM Saturday, February 4, 2017
Columns
Name
OrderFID
OriginAddress1
OriginAddress2
OriginAddress3
OriginAddress
OriginAddressFull
OriginCity
OriginState
OriginCountryCodeStandardFID
OriginCountry
OriginPostalCode
DestinationAddress1
DestinationAddress2
DestinationAddress3
DestinationAddress
DestinationAddressFull
DestinationCity
DestinationState
DestinationCountryCodeStandardFID
DestinationCountry
DestinationPostalCode
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses