create procedure [dbo].[XLedgerChangeTransactionHoldStatuses]
    @items XLedgerTransactionHoldChangeItem readonly
as
begin

    declare @onHoldStatus int = (
        select Id = XLedgerTransactionHoldStatus.XLedgerTransactionHoldStatusId
        from XLedgerTransactionHoldStatus
        where [Description] = 'OnHold'
    )

    declare @offHoldStatus int = (
        select Id = XLedgerTransactionHoldStatus.XLedgerTransactionHoldStatusId
        from XLedgerTransactionHoldStatus
        where [Description] = 'TakenOffHold'
    )

    update XLedgerTransactionHeader set
        XLedgerTransactionHoldStatusFid =
            case Items.OnHold
                when 1 then @onHoldStatus
                else @offHoldStatus
            end
    from @items Items
    inner join XLedgerTransactionHoldSubmission on
        Items.XLedgerTransactionHoldSubmissionId = XLedgerTransactionHoldSubmission.XLedgerTransactionHoldSubmissionId
    where XLedgerTransactionHeader.XLedgerTransactionHeaderId = XLedgerTransactionHoldSubmission.XLedgerTransactionHeaderFid


    declare @now datetimeoffset = sysdatetimeoffset()

    update XLedgerTransactionHoldSubmission set CompletedOn = @now
    from @items Items
    where XLedgerTransactionHoldSubmission.XLedgerTransactionHoldSubmissionId = Items.XLedgerTransactionHoldSubmissionId
end
GO
GRANT EXECUTE ON  [dbo].[XLedgerChangeTransactionHoldStatuses] TO [MssExec]
GO