CREATE PROCEDURE [dbo].[XLedgerImportCustomers]
    @items XLedgerCustomerImportItems readonly,
    @addresses XLedgerAddressImportItems readonly
AS
begin
    set nocount on
    declare @AccountingCustomerIdHolder table
    (
        AccountingCustomerFid int not null,
        ExternalDbId int not null
    )
    ;with
    MergeData as
    (
        select
            XLedgerCustomer.AccountingCustomerFid,
            Items.ExternalDbId,
            Items.[Name]
        from @items Items
        left outer join XLedgerCustomer on Items.ExternalDbId = XLedgerCustomer.ExternalDbId
    )
    merge AccountingCustomer with (tablock) using MergeData on
        MergeData.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
    when matched then update set
        [Name] = MergeData.[Name]
    when not matched by target then insert( [Name], Deleted ) values (MergeData.[Name], 0)
    output inserted.AccountingCustomerId, MergeData.ExternalDbId into @AccountingCustomerIdHolder;

    insert into XLedgerCustomer
    (
        ExternalDbId,
        AccountingCustomerFid
    )
    select
        ExternalDbId = AccountingCustomerIdHolder.ExternalDbId,
        AccountingCustomerIdHolder.AccountingCustomerFid
    from @AccountingCustomerIdHolder AccountingCustomerIdHolder
    where not exists (
        select top 1 1
            from XLedgerCustomer
            where ExternalDbId = AccountingCustomerIdHolder.ExternalDbId );

    ;with MergeData as
    (
        select
            AccountingCustomer.AccountingCustomerId,
            AccountingCustomerAddressType.AccountingCustomerAddressTypeId,
            AddressDeleted = convert(bit, case when Addresses.ParentExternalDbId is null then 1 else 0 end ),
            Addresses.Address1,
            Addresses.Address2,
            Addresses.Address3,
            Addresses.City,
            Addresses.State,
            Addresses.PostalCode,
            CountryCodeStandard.CountryCodeStandardID
        from @items Items
        inner join XLedgerCustomer on Items.ExternalDbId = XLedgerCustomer.ExternalDbId
        cross join AccountingCustomerAddressType
        inner join AccountingCustomer on XLedgerCustomer.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
        left outer join @addresses Addresses on
            XLedgerCustomer.ExternalDbId = Addresses.ParentExternalDbId and
            AccountingCustomerAddressType.TypeName = Addresses.AddressType
        left outer join CountryCodeStandard on Addresses.CountryAlpha2Code = CountryCodeStandard.Alpha2Code
    ) merge AccountingCustomerAddress with (tablock) using MergeData ON
        MergeData.AccountingCustomerId = AccountingCustomerAddress.AccountingCustomerFid AND
        MergeData.AccountingCustomerAddressTypeId = AccountingCustomerAddress.AccountingCustomerAddressTypeFid
    when matched and MergeData.AddressDeleted = 0 then update SET
        Address1 = MergeData.Address1,
        Address2 = MergeData.Address2,
        Address3 = MergeData.Address3,
        City = MergeData.City,
        [State] = MergeData.State,
        [PostalCode] = MergeData.PostalCode,
        [CountryCodeStandardFID] = MergeData.CountryCodeStandardID
    when matched and MergeData.AddressDeleted = 1 then delete
    when not matched and AddressDeleted = 0 then insert (
            AccountingCustomerFid,
            AccountingCustomerAddressTypeFid,
            Address1,
            Address2,
            Address3,
            City,
            State,
            PostalCode,
            CountryCodeStandardFID
    ) values (
        MergeData.AccountingCustomerId,
        MergeData.AccountingCustomerAddressTypeId,
        MergeData.Address1,
        MergeData.Address2,
        MergeData.Address3,
        MergeData.City,
        MergeData.State,
        MergeData.PostalCode,
        MergeData.CountryCodeStandardID
    )
    ;

end
GO
GRANT EXECUTE ON  [dbo].[XLedgerImportCustomers] TO [MssExec]
GO