USP_DATAFORMTEMPLATE_ADD_NAMINGOPPORTUNITYRECOGNITION

The save procedure used by the add dataform template "Naming Opportunity Recognition Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@MGOPPORTUNITYLINKID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@CONSTITUENTNAMEFORMATID	uniqueidentifier	IN	Name format
@CUSTOMNAMEFORMAT	nvarchar(100)	IN	Custom name format
@STARTDATE	UDT_FUZZYDATE	IN	Recognition date
@ENDDATE	UDT_FUZZYDATE	IN	Recognition ends
@AMOUNT	money	IN	Recognition amount
@QUANTITY	int	IN	Quantity
@INSCRIPTION	nvarchar(max)	IN	Inscription
@SPECIALREQUEST	nvarchar(max)	IN	Special request
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_NAMINGOPPORTUNITYRECOGNITION
                    (
                        @ID uniqueidentifier = null output,
                        @MGOPPORTUNITYLINKID uniqueidentifier,
                        @CONSTITUENTID uniqueidentifier = null,
                        @CONSTITUENTNAMEFORMATID uniqueidentifier = null,
                        @CUSTOMNAMEFORMAT nvarchar(100) = '',
                        @STARTDATE dbo.UDT_FUZZYDATE = '00000000',
                        @ENDDATE dbo.UDT_FUZZYDATE = '00000000',
                        @AMOUNT money,
                        @QUANTITY int,
                        @INSCRIPTION nvarchar(max) = '',
                        @SPECIALREQUEST nvarchar(max) = '',
                        @CHANGEAGENTID uniqueidentifier
                    )

                    as
                    set nocount on;

                    begin try
                        if @ID is null
                            set @ID = newid();
                        if @CHANGEAGENTID is null
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
                        declare @CHANGEDATE datetime;
                        set @CHANGEDATE = getdate();

                        if @STARTDATE is null
                            set @STARTDATE = '00000000';
                        if @ENDDATE is null
                            set @ENDDATE = '00000000';

                        --Sanitize the name format to make it agree with our rules

                        if not @CONSTITUENTNAMEFORMATID is null
                            set @CUSTOMNAMEFORMAT = N'';

                        declare @EXISTINGRECOGNITIONS int;
                        declare @MAXRECOGNITIONS int;

                        declare @NAMINGOPPORTUNITYID uniqueidentifier;
                        select @NAMINGOPPORTUNITYID = NAMINGOPPORTUNITYID from dbo.NAMINGOPPORTUNITYMGOPPORTUNITY where ID = @MGOPPORTUNITYLINKID;

                        select @MAXRECOGNITIONS = coalesce(QUANTITY, 0) from dbo.NAMINGOPPORTUNITY where ID = @NAMINGOPPORTUNITYID;
                        select @EXISTINGRECOGNITIONS = coalesce(sum(QUANTITY), 0) from dbo.NAMINGOPPORTUNITYRECOGNITION where NAMINGOPPORTUNITYID = @NAMINGOPPORTUNITYID;

                        declare @BASECURRENCYID uniqueidentifier;
                        declare @CURRENCYEXCHANGERATEID uniqueidentifier;
                        declare @ORGANIZATIONAMOUNT money;
                        declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                        select
                            @BASECURRENCYID = BASECURRENCYID
                        from
                            dbo.NAMINGOPPORTUNITY
                        where    
                            NAMINGOPPORTUNITY.ID = @NAMINGOPPORTUNITYID

                        if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
                        begin
                            set @ORGANIZATIONAMOUNT = @AMOUNT;
                        end
                        else
                        begin
                            set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CHANGEDATE, 0, null);
                            set @ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @CURRENCYEXCHANGERATEID);
                        end

                        if @EXISTINGRECOGNITIONS + @QUANTITY > @MAXRECOGNITIONS
                            raiserror('ERR_TOOMANYRECOGNITIONS', 13, 1);
                        else
                        begin
                            insert into dbo.NAMINGOPPORTUNITYRECOGNITION(ID, NAMINGOPPORTUNITYID, CONSTITUENTID, STARTDATE, ENDDATE, AMOUNT, QUANTITY, INSCRIPTION, SPECIALREQUEST, CONSTITUENTNAMEFORMATID, CUSTOMNAMEFORMAT, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@ID, @NAMINGOPPORTUNITYID, @CONSTITUENTID, @STARTDATE, @ENDDATE, @AMOUNT, @QUANTITY, @INSCRIPTION, @SPECIALREQUEST, @CONSTITUENTNAMEFORMATID, @CUSTOMNAMEFORMAT, @ORGANIZATIONAMOUNT, @CURRENCYEXCHANGERATEID, @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                            insert into dbo.NAMINGOPPORTUNITYRECOGNITIONMGLINK(NAMINGOPPORTUNITYRECOGNITIONID, MGOPPORTUNITYLINKID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@ID, @MGOPPORTUNITYLINKID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;