create procedure [dbo].[BcImportCustomers]
    @items BcCustomerImportItems readonly
as
begin
set nocount on

    --[BcCustomerId] int not null identity(1,1),
    --[BcId] uniqueidentifier not null,
    --[AccountingCustomerFid] int not null,
    --[CreatedOn] datetimeoffset not null, -- the date on which we first imported it.
    --[LastImportedOn] datetimeoffset not null,
    --[LastUpdatedInBcOn] datetimeoffset not null, -- last modified date in Bc



    --BCTD:  Possibly follow XLedger's approach of importing payment terms etc here.

    declare @AccountingCustomerIdHolder table
    (
        AccountingCustomerFid int not null,
        BcId uniqueidentifier not null,
        LastUpdatedInBcOn datetimeoffset not null
    )

    --declare @bob IntList


    --select Item42 from @bob

    ;with
    MergeData as
    (
        select
            BcCustomer.AccountingCustomerFid,
            --BcCustomerClass.AccountingCustomerClassFid,
            --BcCustomerPaymentTerms.AccountingCustomerPaymentTermsFid,
            Items.BcId,
            --Items.SubLedgerGroupExternalId,
            Items.[Name],
            Items.CustomerNumber,
            Items.Email,
            --Items.OnHold,
            Items.CreditLimit,
            Items.LastModified
            --Items.NoCreditFlag,
            --Items.Phone1,
            --Items.Phone2,
            --Items.Phone3,
            --Items.Fax,
            --Items.Contact,
            --Items.TaxExempt
        from @items Items
        left outer join BcCustomer on Items.BcId = BcCustomer.BcId
        --left outer join BcCustomerClass on Items.SubLedgerGroupExternalId = BcCustomerClass.BcId
        --left outer join BcCustomerPaymentTerms on Items.PaymentTermsExternalId = BcCustomerPaymentTerms.BcId
    )
    merge AccountingCustomer with (tablock) using MergeData on
        MergeData.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
    when matched then update set
        [Name] = MergeData.[Name],
        CustomerNumber = MergeData.CustomerNumber,
        Email = MergeData.Email,
        --OnHold = MergeData.OnHold,
        OnHold = 0, -- BCTD:  How are we going to interact with on hold?
        CreditLimit = MergeData.CreditLimit,
        --NoCreditFlag = MergeData.NoCreditFlag,
        NoCreditFlag = 0
        --Phone1 = MergeData.Phone1,
        --Phone2 = MergeData.Phone2,
        --Phone3 = MergeData.Phone3,
        --Fax = MergeData.Fax,
        --Contact = MergeData.Contact,
        --TaxExempt = MergeData.TaxExempt,
        --AccountingCustomerClassFid = MergeData.AccountingCustomerClassFid,
        --AccountingCustomerPaymentTermsFid = MergeData.AccountingCustomerPaymentTermsFid
    when not matched then
        insert
        (
            [Name],
            CustomerNumber,
            Email,
            OnHold,
            CreditLimit,
            NoCreditFlag
            --Phone1,
            --Phone2,
            --Phone3,
            --Fax,
            --Contact,
            --TaxExempt,
            --AccountingCustomerClassFid,
            --AccountingCustomerPaymentTermsFid
        )
        values
        (
            MergeData.[Name],
            MergeData.CustomerNumber,
            MergeData.Email,
            0,--OnHold - (BCTD:  deal with OnHold)
            MergeData.CreditLimit,
            --MergeData.NoCreditFlag,
            0 -- NoCreditFlag

            --MergeData.Phone1,
            --MergeData.Phone2,
            --MergeData.Phone3,
            --MergeData.Fax,
            --MergeData.Contact,
            --MergeData.TaxExempt,
            --MergeData.AccountingCustomerClassFid,
            --MergeData.AccountingCustomerPaymentTermsFid
        )
    output
        inserted.AccountingCustomerId,
        MergeData.BcId,
        MergeData.LastModified
    into @AccountingCustomerIdHolder;


    declare @now datetimeoffset = sysdatetimeoffset()

    --select Bob from Orders
        -- Step 3b: Use the 3a merge output table to update XLedgerCustomer table.
    ;with
    MergeData as
    (
        select
            AccountingCustomerIdHolder.BcId,
            AccountingCustomerIdHolder.AccountingCustomerFid,
            LastUpdatedInBcOn

        from @AccountingCustomerIdHolder as AccountingCustomerIdHolder
    )
    merge BcCustomer with (tablock) using MergeData on
        MergeData.BcId = BcCustomer.BcId
    when matched then update set
        LastImportedOn = @now
    when not matched then
        insert
        (
            BcId,
            AccountingCustomerFid,
            CreatedOn,
            LastImportedOn,
            LastUpdatedInBcOn
        )
        values
        (
            MergeData.BcId,
            MergeData.AccountingCustomerFid,
            @now, -- CreatedOn
            @now, -- LastImportedOn
            MergeData.LastUpdatedInBcOn
        );

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