USP_DATALIST_EMAILINVALIDRECIPIENT_CMSBOUNCES

create procedure dbo.USP_DATALIST_EMAILINVALIDRECIPIENT_CMSBOUNCES(@EMAILINVALIDRECIPIENTID uniqueidentifier)
as
    set nocount on;

    select 
        case isnull(e.ParentEmailID, 0)
        when 0 then e.Name
        else '[TEST] ' + P.[Name]
        end [NAME],
        ejr.StatusReport BOUNCETEXT,
        ejr.SENTDATE,
        ejr.RecentDSNDate BOUNCEDATE    
    from dbo.EMAILINVALIDRECIPIENT EIR
    inner join dbo.EmailInvalidAccountRecipient EIAR on EIAR.EMAILADDRESS = EIR.ADDRESS
    inner join dbo.EmailJob_Recipient EJR on EJR.ID = EIAR.EmailJobRecipientID
    inner join dbo.Email E on E.ID = EJR.EmailID
    left outer join dbo.Email P on P.ID = E.ParentEmailID
    where EIR.ID = @EMAILINVALIDRECIPIENTID