/**
* Don't use this view directly but instead use CustomerAddressInformation_Synonym.
* CustomerAddressInformation_Synonym will either point to this view or to
* CustomerAddressInformation_XLedger if that is active.
*/
create view [dbo].[CustomerAddressInformation_Legacy]
(
    CustomerFid,
    CustomerAddressId,
    CustomerNumber,
    AddressCode,
    [Address1],
    [Address2],
    [Address3],
    City,
    [State],
    PostalCode,
    CountryName,
    Contact,
    CustomerJoinField,
    CustomerAddressJoinField,
    CountryCodeStandardFID
)
as
select
    CustomerFid = convert( int, null ),
    CustomerAddressId = convert( int, null ),
    CustomerNumber = RM00101.CUSTNMBR,
    -- See CustomerAddressJoinField below.
    AddressCode = convert( varchar(15), '' ),
    [Address1] = RM00101.[Address1],
    [Address2] = RM00101.[Address2],
    [Address3] = RM00101.[Address3],
    City = RM00101.City,
    [State] = RM00101.[State],
    PostalCode = RM00101.ZIP,
    CountryName = RM00101.Country,
    Contact = RM00101.CNTCPRSN,
    CustomerJoinField = RM00101.CUSTNMBR,
    -- Even though there is a RM00101.ADRSCODE column, we use '' here so that when we join
    -- this view, it will match and pull customer address info from the RM00101 table.
    CustomerAddressJoinField = convert( char(15), '' ),
    CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID
from RM00101_Synonym as RM00101
left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = RM00101.COUNTRY
union all
select
    CustomerFid = convert( int, null ),
    CustomerAddressId = convert( int, null ),
    CustomerNumber = RM00101.CUSTNMBR,
    AddressCode = RM00102.ADRSCODE,
    [Address1] = RM00102.[Address1],
    [Address2] = RM00102.[Address2],
    [Address3] = RM00102.[Address3],
    City = RM00102.City,
    [State] = RM00102.[State],
    PostalCode = RM00102.ZIP,
    CountryName = RM00102.COUNTRY,
    Contact = RM00102.CNTCPRSN,
    CustomerJoinField = RM00102.CUSTNMBR,
    CustomerAddressJoinField = RM00102.ADRSCODE,
    CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID
from RM00101_Synonym as RM00101
inner join RM00102_Synonym as RM00102 on RM00102.CUSTNMBR = RM00101.CUSTNMBR
left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = RM00101.COUNTRY
GO
GRANT SELECT ON  [dbo].[CustomerAddressInformation_Legacy] TO [MssExec]
GRANT INSERT ON  [dbo].[CustomerAddressInformation_Legacy] TO [MssExec]
GRANT DELETE ON  [dbo].[CustomerAddressInformation_Legacy] TO [MssExec]
GRANT UPDATE ON  [dbo].[CustomerAddressInformation_Legacy] TO [MssExec]
GO