spDelete_EmailProjectAppeal

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

as

DECLARE @NewName nvarchar(50)
DECLARE @Name nvarchar(50)
DECLARE @GUID uniqueidentifier

IF dbo.fnIsProjectComponentLinked(0,@PKID,0,0) = 1
    BEGIN
        BEGIN TRANSACTION
            SELECT @Name = [Name] FROM [EmailProjectAppeal] WHERE [ID] = @PKID

            exec spGetUniqueName @Name, @NewName OUTPUT

            UPDATE [dbo].[EmailProjectAppeal]
            SET [Name] = @NewName, Deleted = 1
            WHERE [ID] = @PKID

            update dbo.[EmailProjectAppeal_EmailList]
            set deleted = 1
            from dbo.[EmailProjectAppeal_EmailList] EPA_EL
            inner join dbo.[EmailProjectAppeal] EPA on EPA.ID = EPA_EL.EMAILPROJECTAPPEALID
            where EPA.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
            inner join dbo.[EmailProjectAppeal] EPA on EPA.ID = EPA_EL.EMAILPROJECTAPPEALID
            where EPA.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
            inner join dbo.[EmailProjectAppeal] EPA on EPA.ID = EPA_EL.EMAILPROJECTAPPEALID
            where EPA.ID = @PKID    

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

            EXEC spAuditThis @CurrentUsersID, 3, @Guid, 35
        COMMIT TRANSACTION

    END
ELSE
    BEGIN
        DELETE EmailProjectAppeal WHERE [ID] = @PKID
    END