USP_RECOGNITIONPROGRAM_DELETE

Executes the "Recognition Program: Delete" record operation.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being deleted.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the delete.
Definition

Copy


                    CREATE procedure dbo.USP_RECOGNITIONPROGRAM_DELETE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as begin


                        if exists
                        (
                            select 
                                1
                            from
                                dbo.CONSTITUENTRECOGNITION
                            where
                                RECOGNITIONPROGRAMID = @ID
                        )
                        begin
                            raiserror('ERR_RECOGNITIONPROGRAM_HASCONSTITUENTRECOGNITION.', 13, 1);
                            return 1;
                        end

                        declare @QUERYNAME nvarchar(255);
                        select @QUERYNAME = 'V_QUERY_RECOGNITION_' + upper(REPLACE(CONVERT(nvarchar(36), @ID), '-', ''))

                        declare @ADHOCNAME nvarchar(255);
                        select @ADHOCNAME = dbo.[UFN_ADHOCQUERY_FIELDISINUSEBY](@QUERYNAME, '')
                        if len(@ADHOCNAME) > 0
                            begin
                                raiserror('ERR_RECOGNITIONPROGRAM_HASQUERY.', 13, 1);
                                return 1;
                            end

                        declare @QUERYID uniqueidentifier;
                        select @QUERYID = ID from dbo.QUERYVIEWCATALOG where OBJECTNAME = @QUERYNAME;

                        delete from dbo.QUERYVIEWRELATIONSHIP where RELATEDQUERYVIEWID = @QUERYID;
                        delete from dbo.QUERYVIEWRELATIONSHIP where ROOTQUERYVIEWID = @QUERYID;
                        delete from dbo.QUERYVIEWCATALOG where ID = @QUERYID;


                        -- TY, bug 127195 - clean up constituentrecognitionrevenue records

                        delete 
                            from dbo.CONSTITUENTRECOGNITIONREVENUE
                        where 
                            RECOGNITIONPROGRAMID = @ID
                            and not exists
                            (
                                select 
                                    'x'
                                from 
                                    dbo.CONSTITUENTRECOGNITION
                                    inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.ID = CONSTITUENTRECOGNITIONREVENUE.REVENUERECOGNITIONID
                                    where CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = @ID
                                    and CONSTITUENTRECOGNITION.CONSTITUENTID = REVENUERECOGNITION.CONSTITUENTID
                            );

                        exec USP_RECOGNITIONPROGRAM_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
                        return 0;

                    end