create procedure [dbo].[XLedgerImportApTransactions]
    @transactions XLedgerApTransactionImportItems readonly
as
begin
    set nocount on


    declare @affectedTransactionHeaders table
    (
        XLedgerTransactionHeaderId int not null,
        XLedgerSubledgerTransactionDbId int not null,
        OnHoldChange bit null -- 1 = place on hold, 0 = take off hold, null = leave it alone
    )

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

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

    update XLedgerTransactionHeader set
        SubledgerTransactionXLedgerDbId = ApTransaction.SubledgerTransactionXLedgerDbId
    output
        inserted.XLedgerTransactionHeaderId,
        inserted.SubledgerTransactionXLedgerDbId,
        case
            when deleted.SubledgerTransactionXLedgerDbId = inserted.SubledgerTransactionXLedgerDbId then null
            when deleted.XLedgerTransactionHoldStatusFid = @notYetOnHoldStatus then 1
            when deleted.XLedgerTransactionHoldStatusFid = @notYetOffHoldStatus then 0
            else null
        end
    into @affectedTransactionHeaders
    from @transactions ApTransaction
    where XLedgerTransactionHeader.ArApExternalIdentifier = ApTransaction.ExternalIdentifier



    declare @rowCount int = 0

    insert into XLedgerTransactionHoldSubmission(
        XLedgerTransactionHeaderFID,
        XLedgerSubledgerTransactionDbId,
        OnHold
    )
    select
        AffectedTransactionHeaders.XLedgerTransactionHeaderId,
        AffectedTransactionHeaders.XLedgerSubledgerTransactionDbId,
        AffectedTransactionHeaders.OnHoldChange
    from @affectedTransactionHeaders AffectedTransactionHeaders
    where
        AffectedTransactionHeaders.OnHoldChange is not null

    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].[XLedgerImportApTransactions] TO [MssExec]
GO