create procedure [dbo].[XLedgerImportBankAccounts]
    @items XLedgerBankAccountImportItems readonly
as
begin
set nocount on

    declare @now datetimeoffset = sysdatetimeoffset()

    declare @BankAccountToXLedgerMap table
    (
        BankAccountFid int not null,
        ExternalDbId int not null
    )

    ;with Items as
    (
        select
            XLedgerBankAccount.AccountingBankAccountFid,
            XLedgerAccount.AccountingAccountFid,
            ImportItems.Description,
            ImportItems.Code,
            ImportItems.XLedgerDbId,
            ImportItems.BankAccountNumber
        from @items ImportItems
        inner join XLedgerAccount on ImportItems.GlAccountXLedgerDbId = XLedgerAccount.ExternalDbId

        --only insert the bank account if it is a known company in moverssuite
        inner join XLedgerCompany on ImportItems.CompanyXLedgerDbId = XLedgerCompany.XLedgerDbId
        left outer join XLedgerBankAccount on ImportItems.XLedgerDbId = XLedgerBankAccount.XLedgerDbId
    )

    merge AccountingBankAccount with (tablock) using Items on Items.AccountingBankAccountFid = AccountingBankAccount.AccountingBankAccountId
    when not matched then
        insert(
            [AccountingAccountFid],
            [Description],
            [Code],
            [AccountNumber],
            [Hidden]
            
            )
    values(
        Items.AccountingAccountFid,
        Items.Description,
        Items.Code,
        Items.BankAccountNumber,
        0
    )
    when matched then
        update set
            AccountingAccountFid = Items.AccountingAccountFid,
            [Description] = Items.[Description],
            Code = Items.Code,
            AccountNumber = Items.BankAccountNumber
    output inserted.AccountingBankAccountId, Items.XLedgerDbId into @BankAccountToXLedgerMap
    ;


    ;with Items as
    (
        select
            ImportItems.XLedgerDbId,
            BankAccountToXLedgerMap.BankAccountFid,
            XLedgerCompany.XLedgerCompanyId
            from @items ImportItems
            inner join @BankAccountToXLedgerMap BankAccountToXLedgerMap on
                ImportItems.XLedgerDbId = BankAccountToXLedgerMap.ExternalDbId
            inner join XLedgerCompany on ImportItems.CompanyXLedgerDbId = XLedgerCompany.XLedgerDbId

    )
    merge XLedgerBankAccount with (tablock) using Items on
        Items.BankAccountFid = XLedgerBankAccount.AccountingBankAccountFid
    when not matched then
        insert(
            XLedgerDbId,
            CreatedOn,
            LastImportedOn,
            AccountingBankAccountFid,
            XLedgerCompanyFid)
        values(
            Items.XLedgerDbId,
            @now,
            @now,
            Items.BankAccountFid,
            Items.XLedgerCompanyId)
    when matched then update
        set LastImportedOn = @now,
        XLedgerCompanyFid = Items.XLedgerCompanyId
    ;


    --mark anything as hidden that wasn't encountered via this import process (and un-hide everything else..)
    ;with HiddenStatus as
    (
        select
        XLedgerBankAccount.AccountingBankAccountFid,
        [Hidden] = convert(bit, case when BankAccountToXLedgerMap.ExternalDbId is null then 1 else 0 end )
        from XLedgerBankAccount
        left outer join @BankAccountToXLedgerMap BankAccountToXLedgerMap on XLedgerBankAccount.XLedgerDbId = BankAccountToXLedgerMap.ExternalDbId
    )
    update AccountingBankAccount set [Hidden] = HiddenStatus.[Hidden]
    from HiddenStatus
    inner join AccountingBankAccount on HiddenStatus.AccountingBankAccountFid = AccountingBankAccount.AccountingBankAccountID



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