USP_DATAFORMTEMPLATE_EDIT_RECOGNITIONPROGRAM

The save procedure used by the edit dataform template "Recognition Program Edit Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@SITEID	uniqueidentifier	IN	Site
@EXPIRESONCODE	tinyint	IN	Expiration date
@BACKDATEMEMBERSHIPS	bit	IN	Set expiration to end of following year when starting after cutoff day
@CUTOFFDATEFORYEAR	char(4)	IN	Cutoff date

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECOGNITIONPROGRAM (
                    @ID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @NAME nvarchar(100),
                    @DESCRIPTION nvarchar(255),
                    @SITEID uniqueidentifier,
                    @EXPIRESONCODE tinyint,
                    @BACKDATEMEMBERSHIPS bit,
                    @CUTOFFDATEFORYEAR char(4)
                )
                as

                    set nocount on;

                    if @SITEID is null
                        begin 
                        if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 
                            begin
                            raiserror('Site is required.',13,1)
                            return
                            end
                        end

                    if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                    declare @CURRENTDATE datetime
                    set @CURRENTDATE = getdate()

                    begin try
                        declare @OLDNAME nvarchar(100);
                        select @OLDNAME = NAME from dbo.RECOGNITIONPROGRAM where ID = @ID;

                        if @OLDNAME <> @NAME 
                            begin
                            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('Cannot modify the name value.  The generated query is used by an ad hoc query as a filter or output field.', 13, 1);
                                    return 1;
                                end                        

                            -- This set of conditions is used to check if the recognition program is used by an export definition.

                            declare @EXPORTDEFINITIONNAME nvarchar(128);
                            select @EXPORTDEFINITIONNAME = dbo.[UFN_EXPORTDEFINITION_FIELDISINUSEBY](@QUERYNAME);
                            if len(@EXPORTDEFINITIONNAME) > 0
                                begin 
                                    raiserror('BBERR_VALIDATE_USEDBYEXPORTDEFINITION', 13, 1);
                                    return 1;
                                end
                            end


                        update dbo.RECOGNITIONPROGRAM set
                            NAME = @NAME,
                            DESCRIPTION = @DESCRIPTION,
                            SITEID = @SITEID,
                            EXPIRESONCODE = @EXPIRESONCODE,
                            BACKDATEMEMBERSHIPS = @BACKDATEMEMBERSHIPS,
                            CUTOFFDATEFORYEAR = @CUTOFFDATEFORYEAR,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @ID;

                        if @OLDNAME <> @NAME 
                            begin
                            declare @QUERYID uniqueidentifier;
                            select @QUERYID = ID from dbo.QUERYVIEWCATALOG where OBJECTNAME = @QUERYNAME;

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

                            exec dbo.USP_RECOGNITIONPROGRAM_CREATEQUERY @ID, @NAME, @CHANGEAGENTID;
                            end

                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch

                return 0;