create procedure [dbo].[XLedgerCheckForTransactionUploadErrorsForRevenueClerk]
    @sysUserId int
as
begin
    set nocount on

    declare @theRevenueClerkErrors table
    (
        BatchItemId int,
        AcctTransactionId int
    )

    insert into @theRevenueClerkErrors
    (
        BatchItemId,
        AcctTransactionId
    )
    -- Get all XLedger failures for this user that have not been successfully re-processed.
    select
        BatchItemId = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId,
        AcctTransactionId = XLedgerJournalEntryBatchItem.AcctTransactionFid
    from XLedgerTransactionUploadFailureSysUserMap
    inner join XLedgerTransactionUploadFailure on
        XLedgerTransactionUploadFailure.XLedgerTransactionUploadFailureId = XLedgerTransactionUploadFailureSysUserMap.XLedgerTransactionUploadFailureFid
    inner join XLedgerJournalEntryBatchItem on
        XLedgerJournalEntryBatchItem.XLedgerTransactionUploadFailureFid = XLedgerTransactionUploadFailure.XLedgerTransactionUploadFailureId
    where XLedgerTransactionUploadFailureSysUserMap.SysUserFid = @sysUserId and
        XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is null and
        XLedgerJournalEntryBatchItem.Resolved = 0

    -- Remove any of those that were reprocessed successfully.  Each AcctTransaction should
    -- only be processed successfully one time.
    declare @theResolvedCount int
    declare @ResolvedFailures table
    (
        BatchItemId int
    )
    delete from theRevenueClerkErrors
    output DELETED.BatchItemId into @ResolvedFailures( BatchItemId )
    from @theRevenueClerkErrors as theRevenueClerkErrors
    inner join XLedgerJournalEntryBatchItem on
        XLedgerJournalEntryBatchItem.AcctTransactionFid = theRevenueClerkErrors.AcctTransactionId and
        XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is not null

    set @theResolvedCount = @@ROWCOUNT

    -- If there were any failures that we just removed, then mark them
    -- as Resolved so that we can forever ignore them.
    if( @theResolvedCount > 0 )
    begin
        update XLedgerJournalEntryBatchItem set
            Resolved = 1
        from XLedgerJournalEntryBatchItem
        inner join @ResolvedFailures as ResolvedFailures on
            ResolvedFailures.BatchItemId = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId
        where XLedgerJournalEntryBatchItem.Resolved = 0
    end

    ;with FailedBatchItems as
    (
        -- Get the most recent (the largest BatchItemId) for each AcctTransaction that has yet to be processed successfully.
        select
            BatchItemId = max( theRevenueClerkErrors.BatchItemId ),
            AcctTransactionId = theRevenueClerkErrors.AcctTransactionId
        from @theRevenueClerkErrors as theRevenueClerkErrors
        group by theRevenueClerkErrors.AcctTransactionId
    )
    select distinct
        Id = XLedgerTransactionUploadFailure.XLedgerTransactionUploadFailureId,
        [Description] = XLedgerTransactionUploadFailure.ErrorMessage,
        [Location] = XLedgerTransactionUploadFailureLocation.[Description],
        BatchHeaderDescription = XLedgerJournalEntryBatch.[Description]
    from FailedBatchItems
    inner join XLedgerJournalEntryBatchItem on
        XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId = FailedBatchItems.BatchItemId
    inner join XLedgerTransactionUploadFailure on
        XLedgerTransactionUploadFailure.XLedgerTransactionUploadFailureId = XLedgerJournalEntryBatchItem.XLedgerTransactionUploadFailureFid
    inner join XLedgerJournalEntryBatch on
        XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid
    inner join XLedgerTransactionUploadFailureLocation on
        XLedgerTransactionUploadFailureLocation.XLedgerTransactionUploadFailureLocationId = XLedgerTransactionUploadFailure.XLedgerTransactionUploadFailureLocationFid
end
GO
GRANT EXECUTE ON  [dbo].[XLedgerCheckForTransactionUploadErrorsForRevenueClerk] TO [MssExec]
GO