/**
* Do NOT access this view by this view name.  Instead, use the CustomerInformationWithDefaultAddress synonym which will either
* point to this view or to the CustomerInformationWithDefaultAddress_Legacy as appropriate for that system.
*/
create view [dbo].[CustomerInformationWithDefaultAddress_XLedger]
(
    CustomerNumber,
    CustomerName,
    AddressCode,
    OnHold,
    NoCreditFlag,
    CreditLimit,
    Inactive,
    CustomerClass,
    [Address1],
    [Address2],
    [Address3],
    City,
    [State],
    PostalCode,
    Country,
    CustomerId,
    CustomerAddressId,
    CustomerJoinField,
    CustomerAddressJoinField,
    Hold,
    Contact,
    TaxExempt
)
as
select
    CustomerNumber = AccountingCustomer.CustomerNumber,
    CustomerName = AccountingCustomer.[Name],
    AddressCode = dbo.GetAccountingCustomerAddressTypeAddressCode( AccountingCustomerAddressType.TypeName ),
    OnHold = case
        when AccountingCustomer.OnHold = 1 AND AccountingCustomer.Hidden = 1 then 'On Hold, Inactive'
        when AccountingCustomer.OnHold = 1 then 'On Hold'
        when AccountingCustomer.Hidden = 1 then 'Inactive'
        else ''
    end,
    NoCreditFlag = AccountingCustomer.NoCreditFlag,
    CreditLimit = AccountingCustomer.CreditLimit,
    Inactive = AccountingCustomer.[Hidden],
    CustomerClass = AccountingCustomerClass.ClassCode,
    [Address1] = AccountingCustomerAddress.[Address1],
    [Address2] = AccountingCustomerAddress.[Address2],
    [Address3] = AccountingCustomerAddress.[Address3],
    City = AccountingCustomerAddress.City,
    [State] = AccountingCustomerAddress.[State],
    PostalCode = AccountingCustomerAddress.PostalCode,
    Country = CountryCodeStandard.VanlineCountryCode,
    CustomerId = AccountingCustomer.AccountingCustomerId,
    CustomerAddressId = AccountingCustomerAddress.AccountingCustomerAddressId,
    CustomerJoinField = AccountingCustomer.AccountingCustomerId,
    CustomerAddressJoinField = AccountingCustomerAddress.AccountingCustomerAddressId,
    Hold = AccountingCustomer.OnHold,
    Contact = AccountingCustomer.Contact,
    TaxExempt = AccountingCustomer.TaxExempt
from AccountingCustomer
inner join AccountingCustomerAddressType on AccountingCustomerAddressType.TypeName = dbo.GetDefaultAccountingCustomerAddressTypeName()
left outer join AccountingCustomerAddress on AccountingCustomerAddress.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId and
    AccountingCustomerAddress.AccountingCustomerAddressTypeFid = AccountingCustomerAddressType.AccountingCustomerAddressTypeId
left outer join CountryCodeStandard on CountryCodeStandard.CountryCodeStandardID = AccountingCustomerAddress.CountryCodeStandardFID
left outer join AccountingCustomerClass on AccountingCustomerClass.AccountingCustomerClassId = AccountingCustomer.AccountingCustomerClassFid
GO
GRANT SELECT ON  [dbo].[CustomerInformationWithDefaultAddress_XLedger] TO [MssExec]
GRANT INSERT ON  [dbo].[CustomerInformationWithDefaultAddress_XLedger] TO [MssExec]
GRANT DELETE ON  [dbo].[CustomerInformationWithDefaultAddress_XLedger] TO [MssExec]
GRANT UPDATE ON  [dbo].[CustomerInformationWithDefaultAddress_XLedger] TO [MssExec]
GO