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

    declare @theManagerErrors table
    (
        BatchItemId int,
        AcctTransactionId int
    )

    insert into @theManagerErrors
    (
        BatchItemId,
        AcctTransactionId
    )
    -- Get all XLedger failures for this user that have not been successfully 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
    inner join XLedgerBranchObjectValue on XLedgerBranchObjectValue.XLedgerDbId = XLedgerJournalEntryBatchItem.NominalBranchObjectValueXLedgerDbId and
        XLedgerBranchObjectValue.[Hidden] = 0
    inner join UserAccess on UserAccess.SysUserID = @sysUserId
    inner join SecModules on SecModules.[Description] = 'Revenue Entry'
    inner join SecProfileDetail on SecProfileDetail.SecProfilePriKey = UserAccess.SecProfilePriKey and
        SecProfileDetail.ModulePriKey = SecModules.ModulePriKey and
        SecProfileDetail.BranchPriKey = XLedgerBranchObjectValue.BranchFid and
        SecProfileDetail.ManagerFlag = 1
    where XLedgerJournalEntryBatchItem.Resolved = 0 and
        XLedgerJournalEntryBatchItem.XLedgerTransactionHeaderFid is null

    -- 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 theManagerErrors
    output DELETED.BatchItemId into @ResolvedFailures( BatchItemId )
    from @theManagerErrors as theManagerErrors
    inner join XLedgerJournalEntryBatchItem on
        XLedgerJournalEntryBatchItem.AcctTransactionFid = theManagerErrors.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( theManagerErrors.BatchItemId ),
            AcctTransactionId = theManagerErrors.AcctTransactionId
        from @theManagerErrors as theManagerErrors
        group by theManagerErrors.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 = XLedgerTransactionUploadFailure.XLedgerJournalEntryBatchFid
    inner join XLedgerTransactionUploadFailureLocation on
        XLedgerTransactionUploadFailureLocation.XLedgerTransactionUploadFailureLocationId = XLedgerTransactionUploadFailure.XLedgerTransactionUploadFailureLocationFid
end
GO
GRANT EXECUTE ON  [dbo].[XLedgerCheckForTransactionUploadErrorsForManager] TO [MssExec]
GO