CREATE PROCEDURE [dbo].[XLedgerImportSubledgerHiddenIndicators]
    --the "prikey" in this case is the external DBId,
    --"Int" is the XLedger DbId for the subledger (i.e. customer or supplier)
        -- when "un-hiding" something the "Int" will be null.
    @items PriKeyToIntMap readonly,
    @xLedgerSyncHistoryId int
AS
BEGIN

    declare @hiddenSubledgerChanges table
    (
        SubledgerExternalDbId int not null,
        [Hidden] bit not null
    )

    set nocount on
    ;with Items as(
        select
        XLedgerDbId = items.Id,
        CustomerOrSupplierDbId = items.[Value]
        from @items items
    ) merge XLedgerHiddenSubledger with(tablock) using Items on (
        Items.XLedgerDbId = XLedgerHiddenSubledger.XLedgerDbId or
        Items.CustomerOrSupplierDbId = XLedgerHiddenSubledger.XLedgerSubledgerDbId
    )
    when not matched and Items.CustomerOrSupplierDbId is not null then
        insert(
            XLedgerDbId,
            XLedgerSubledgerDbId,
            XLedgerSyncHistoryFid
        )
        values(
            Items.XLedgerDbId,
            Items.CustomerOrSupplierDbId,
            @xLedgerSyncHistoryId
        )
    when matched and Items.CustomerOrSupplierDbId is not null then update set
        XLedgerDbId = Items.XLedgerDbId,
        XLedgerSubledgerDbId = Items.CustomerOrSupplierDbId,
        XLedgerSyncHistoryFid = @xLedgerSyncHistoryId
    when matched and Items.CustomerOrSupplierDbId is null then delete
    output
        isnull(inserted.XLedgerSubledgerDbId, deleted.XLedgerSubledgerDbId ),
        convert(bit, case when inserted.XLedgerSubledgerDbId is null then 0 else 1 end)
        into @hiddenSubledgerChanges
    ;

    update AccountingCustomer
        set [Hidden] =
            HiddenSubledgerChanges.[Hidden]
        from @hiddenSubledgerChanges HiddenSubledgerChanges
        inner join XLedgerCustomer on HiddenSubledgerChanges.SubledgerExternalDbId = XLedgerCustomer.ExternalDbId
        inner join AccountingCustomer on XLedgerCustomer.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId

    update AccountingVendor
            set [Hidden] =
            HiddenSubledgerChanges.[Hidden]
        from @hiddenSubledgerChanges HiddenSubledgerChanges
        inner join XLedgerSupplier on HiddenSubledgerChanges.SubledgerExternalDbId = XLedgerSupplier.ExternalDbId
        inner join AccountingVendor on XLedgerSupplier.AccountingVendorFid = AccountingVendor.AccountingVendorId

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