create procedure [dbo].[XLedgerRequestTransactionHoldChange]
    @xLedgerTransactionHeaderIds IntList readonly,
    @onHold bit -- 1 = on hold, 0 = off hold
as
begin
    set nocount on

    declare @holdStatusId int =
        ( select Id = XLedgerTransactionHoldStatus.XLedgerTransactionHoldStatusId
        from XLedgerTransactionHoldStatus
        where [Description] = case when @onHold = 1 then 'NotYetOnHold' else 'NotYetOffHold' end )

    --set the statuses.  Mostly important to allow for retries if the subledger dbId changed.
    update XLedgerTransactionHeader set
        XLedgerTransactionHoldStatusFid = @holdStatusId
    from @xLedgerTransactionHeaderIds Ids
    where XLedgerTransactionHeader.XLedgerTransactionHeaderId = Ids.Item


    declare @rowCount int = 0

    --enqueue items to be processed
    insert into XLedgerTransactionHoldSubmission(
        XLedgerTransactionHeaderFID,
        XLedgerSubledgerTransactionDbId,
        OnHold
    )
    select
        Ids.Item,
        XLedgerTransactionHeader.SubledgerTransactionXLedgerDbId,
        @onHold
    from @xLedgerTransactionHeaderIds Ids
    inner join XLedgerTransactionHeader on Ids.Item = XLedgerTransactionHeader.XLedgerTransactionHeaderId

    set @rowCount = @@rowcount

    --Write an MssWebEvent record to activate the background process.
    if @rowCount > 0
    begin
        insert into MssWebEvent( MssWebEventTypeFid )
        select MssWebEventType.MssWebEventTypeId
        from MssWebEventType where MssWebEventType.EventTypeName = 'XLedgerTransactionHoldChangeRequested'
    end

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