spDelete_EmailProjectAppeal_EmailList

CREATE  PROCEDURE [dbo].[spDelete_EmailProjectAppeal_EmailList]
@PKID int,
@CurrentUsersID int

as

DECLARE @GUID uniqueidentifier
DECLARE @AppealListType tinyint
DECLARE @SoftDelete bit

SET @SoftDelete = 0

SELECT @AppealListType = AppealListType FROM EmailProjectAppeal_EmailList WHERE [ID] = @PKID

IF @AppealListType = 3 --Exclusion list is appeal wide not related to one specific email so check if any emails have been sent to determine delete action
    BEGIN
        IF EXISTS(SELECT NULL FROM EmailJob ej 
        INNER JOIN Email e on ej.EmailID = e.[ID]
        INNER JOIN EmailProjectAppeal_ListEmail epale ON e.ProjectAppealListEmailID = epale.[ID]
        INNER JOIN EmailProjectAppeal_EmailList epael ON epale.[EmailProjectAppealID] = epael.[EmailProjectAppealID] 
        WHERE epael.[ID] = @PKID)
        SET @SoftDelete = 1
    END
ELSE --Target and Seed Lists will have a direct relationship with an email so use this check
    BEGIN
        IF dbo.fnIsProjectComponentLinked(0,0,@PKID,0) = 1
            SET @SoftDelete = 1
    END

--This list has seen action so update delete flag
IF @SoftDelete = 1
    BEGIN
        BEGIN TRANSACTION
            UPDATE [dbo].[EmailProjectAppeal_EmailList]
            SET Deleted = 1
            WHERE [ID] = @PKID

            update dbo.[EmailProjectAppeal_ListEmail]
            set deleted = 1
            from dbo.[EmailProjectAppeal_ListEmail] EPA_LE
            inner join dbo.[EmailProjectAppeal_EmailList] EPA_EL on EPA_EL.ID = EPA_LE.EmailProjectAppealListID
            where EPA_EL.ID = @PKID

            update dbo.[Email]
            set deleted = 1
            from dbo.[EMAIL] E
            inner join dbo.[EmailProjectAppeal_ListEmail] EPA_LE on EPA_LE.ID =  E.ProjectAppealListEmailID
            inner join dbo.[EmailProjectAppeal_EmailList] EPA_EL on EPA_EL.ID = EPA_LE.EmailProjectAppealListID
            where EPA_EL.ID = @PKID            

            SELECT @Guid=Guid FROM [dbo].[EmailProjectAppeal_EmailList] WHERE id = @PKID

            EXEC spAuditThis @CurrentUsersID, 3, @Guid, 36
        COMMIT TRANSACTION
    END
ELSE --This one, no action so no reason to keep it around 
    BEGIN
        DELETE [dbo].[EmailProjectAppeal_EmailList] WHERE [ID] = @PKID
    END