USP_DATALIST_UNMAPPEDTRANSACTIONERRORS

CREATE procedure dbo.USP_DATALIST_UNMAPPEDTRANSACTIONERRORS
(
    @ID uniqueidentifier
    ,@TRANSACTIONTYPECODE tinyint = null
    ,@EXCLUDEACKNOWLEDGE bit = 1
)
as
    set nocount on;

    WITH ErrorMessage_CTE (ID, ERRORMESSAGE, TRANSACTIONTYPE, OCCURRENCES, ADDRESSED, DATEADDED)
    AS 
    (
        select MAX(cast(ME.ID as nvarchar(36))), ME.ERRORMESSAGE, ME.TRANSACTIONTYPE, COUNT(ME.ID), ME.ADDRESSED, MIN(ME.DATEADDED)
        from dbo.GLACCOUNTMAPPINGERROR ME
            left join dbo.SALESORDER SO on SO.ID = ME.TRANSACTIONID
            left join dbo.FINANCIALTRANSACTION FT on FT.ID = ME.TRANSACTIONID or FT.ID = SO.REVENUEID
        where
            @ID = FT.PDACCOUNTSYSTEMID
            and (@TRANSACTIONTYPECODE is null or ME.TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE)
            and ((@EXCLUDEACKNOWLEDGE = 0) or (@EXCLUDEACKNOWLEDGE = 1 and ME.ADDRESSED = 0))
            and ME.DELETED = 0
            group by ME.ERRORMESSAGE, ME.TRANSACTIONTYPE, ME.ADDRESSED
    )

    select MAX(cast(ME1.ID as nvarchar(36))) as [ID]
        ,case when (select COUNT(TRANSACTIONTYPE) from ErrorMessage_CTE where ERRORMESSAGE = ME1.ERRORMESSAGE and ADDRESSED = ME1.ADDRESSED group by ERRORMESSAGE) > 1 THEN 'Multiple' ELSE MAX(ME1.TRANSACTIONTYPE) END [TRANSACTIONTYPE]
        ,SUM(ME1.OCCURRENCES) as [OCCURRENCES]
        ,ME1.ERRORMESSAGE
        ,ME1.ADDRESSED as [ADDRESSED]
        ,MIN(ME1.DATEADDED) as [ADDED]
    from ErrorMessage_CTE ME1
    group by ME1.ERRORMESSAGE, ME1.ADDRESSED
    order by SUM(ME1.OCCURRENCES) desc, MIN(ME1.DATEADDED) desc, MAX(ME1.TRANSACTIONTYPE) asc