USP_DATAFORMTEMPLATE_ADD_PROSPECTRESEARCHREQUEST

The save procedure used by the add dataform template "Prospect Research Request Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@STATUSCODE	tinyint	IN	Status
@RECORDTYPECODE	tinyint	IN	Record type
@CONSTITUENTS	xml	IN	Prospects to research
@CONSTITUENTNAMES	xml	IN	Prospects to research
@EVENTID	uniqueidentifier	IN	Event to include in request
@RESEARCHGROUPID	uniqueidentifier	IN	Research group to include in request
@PROSPECTRESEARCHREQUESTPRIORITYCODEID	uniqueidentifier	IN	Priority
@DUEDATE	date	IN	Due date
@PROSPECTRESEARCHREQUESTTYPECODEID	uniqueidentifier	IN	Research type
@PROSPECTRESEARCHREQUESTREASONCODEID	uniqueidentifier	IN	Request reason
@REQUESTEDBYID	uniqueidentifier	IN	Requested by
@SUBMITTEDBYID	uniqueidentifier	IN	Submitted by
@NOTES	nvarchar(max)	IN	Notes
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITES	xml	IN	Sites
@APPUSERGRANTEDRESEARCHGROUPS	bit	IN
@APPUSERGRANTEDEVENTS	bit	IN

Definition

Copy



          CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PROSPECTRESEARCHREQUEST
          (
            @ID uniqueidentifier = null output,
            @CHANGEAGENTID uniqueidentifier,
            @STATUSCODE tinyint = 0,
            @RECORDTYPECODE tinyint = 0,
            @CONSTITUENTS xml = null,
            @CONSTITUENTNAMES xml = null,
            @EVENTID uniqueidentifier = null,
            @RESEARCHGROUPID uniqueidentifier = null,
            @PROSPECTRESEARCHREQUESTPRIORITYCODEID uniqueidentifier = null,
            @DUEDATE date = null,
            @PROSPECTRESEARCHREQUESTTYPECODEID uniqueidentifier = null,
            @PROSPECTRESEARCHREQUESTREASONCODEID uniqueidentifier = null,
            @REQUESTEDBYID uniqueidentifier = null,
            @SUBMITTEDBYID uniqueidentifier = null,
            @NOTES nvarchar(max) = null,
            @CURRENTAPPUSERID uniqueidentifier,
            @SITES xml = null,
            @APPUSERGRANTEDRESEARCHGROUPS bit = null,
            @APPUSERGRANTEDEVENTS bit = null
          )
          as

          set nocount on;

          if @ID is null
            set @ID = newid();

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

          declare @CURRENTDATE datetime;
          set @CURRENTDATE = getdate();

          set @STATUSCODE = 1;

          declare @REQUESTNO int;

          update dbo.PROSPECTRESEARCHREQUESTCONFIGURATION
          set REQUESTNO = REQUESTNO + 1;

          select @REQUESTNO = REQUESTNO from dbo.PROSPECTRESEARCHREQUESTCONFIGURATION;
          if @REQUESTNO is null
          begin
            insert into dbo.PROSPECTRESEARCHREQUESTCONFIGURATION
            (REQUESTNO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
            values
            (1, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID);

            set @REQUESTNO = 1;
          end

          begin try
            if coalesce((select count(distinct CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)), 0)
               <> coalesce((select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)), 0) or
               coalesce((select count(distinct CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)), 0)
               <> coalesce((select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)), 0)
            begin
              raiserror('BBERR_PROSPECTRESEARCHREQUEST_DUPLICATECONSTITUENTS', 13, 1);
              return 1;
            end
            else if (select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)) = 0 and 
              (select count(CONSTITUENTID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)) = 0 begin
              raiserror('BBERR_PROSPECTRESEARCHREQUEST_NOCONSTITUENTS', 13, 1);
              return 1;
            end

            exec dbo.USP_PROSPECTRESEARCHREQUEST_VALIDATESITES @SITES, @CURRENTAPPUSERID, '7a968ff9-8e1b-483c-af13-2f55dcb14e50', 1, 0, @REQUESTEDBYID, @SUBMITTEDBYID;

            if (select count(ID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)) < 501 and (select count(ID) from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES)) < 501
            begin
              insert into dbo.PROSPECTRESEARCHREQUEST
                (ID, 
                STATUSCODE, RECORDTYPECODE, EVENTID, RESEARCHGROUPID, PROSPECTRESEARCHREQUESTPRIORITYCODEID, 
                DUEDATE, PROSPECTRESEARCHREQUESTTYPECODEID, PROSPECTRESEARCHREQUESTREASONCODEID, REQUESTEDBYID, 
                SUBMITTEDBYID, NOTES, REQUESTNO, 
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values
                (@ID, 
                @STATUSCODE, @RECORDTYPECODE, @EVENTID, @RESEARCHGROUPID, @PROSPECTRESEARCHREQUESTPRIORITYCODEID, 
                @DUEDATE, @PROSPECTRESEARCHREQUESTTYPECODEID, @PROSPECTRESEARCHREQUESTREASONCODEID, @REQUESTEDBYID, 
                @SUBMITTEDBYID, @NOTES, @REQUESTNO, 
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

              if not @SITES is null
              begin
                exec dbo.USP_PROSPECTRESEARCHREQUEST_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;
              end

              if @RECORDTYPECODE = 0 or @RECORDTYPECODE = 3
                insert into dbo.PROSPECTRESEARCHREQUESTCONSTITUENT
                  (PROSPECTRESEARCHREQUESTID, 
                  REQUESTNO, CONSTITUENTID, STATUSCODE, PROSPECTRESEARCHREQUESTPRIORITYCODEID, DUEDATE, 
                  PROSPECTRESEARCHREQUESTTYPECODEID, PROSPECTRESEARCHREQUESTREASONCODEID, NOTES, 
                  ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                  @ID, 
                  convert(varchar, @REQUESTNO) + '-' + convert(varchar, row_number() over(order by CONSTITUENTID)), 
                  CONSTITUENTID, @STATUSCODE, @PROSPECTRESEARCHREQUESTPRIORITYCODEID, @DUEDATE, 
                  @PROSPECTRESEARCHREQUESTTYPECODEID, @PROSPECTRESEARCHREQUESTREASONCODEID, @NOTES, 
                  @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_FROMITEMLISTXML(@CONSTITUENTS)
                where coalesce(CONSTITUENTID, '00000000-0000-0000-0000-000000000000') <> '00000000-0000-0000-0000-000000000000';
              else
                insert into dbo.PROSPECTRESEARCHREQUESTCONSTITUENT
                  (PROSPECTRESEARCHREQUESTID, 
                  REQUESTNO, CONSTITUENTID, STATUSCODE, PROSPECTRESEARCHREQUESTPRIORITYCODEID, DUEDATE, 
                  PROSPECTRESEARCHREQUESTTYPECODEID, PROSPECTRESEARCHREQUESTREASONCODEID, NOTES, 
                  ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                  @ID, 
                  convert(varchar, @REQUESTNO) + '-' + convert(varchar, row_number() over(order by CONSTITUENTID)), 
                  CONSTITUENTID, @STATUSCODE, @PROSPECTRESEARCHREQUESTPRIORITYCODEID, @DUEDATE, 
                  @PROSPECTRESEARCHREQUESTTYPECODEID, @PROSPECTRESEARCHREQUESTREASONCODEID, @NOTES, 
                  @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from
                  dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTNAMES_FROMITEMLISTXML(@CONSTITUENTNAMES);

              exec dbo.USP_PROSPECTRESEARCHREQUEST_CREATEDALERT_SEND @CURRENTAPPUSERID, @ID;
              exec dbo.USP_PROSPECTRESEARCHREQUEST_UNASSIGNEDPENDINGALERT_SEND @CURRENTAPPUSERID, @ID;
            end
            else begin
              raiserror('There may only be 500 prospects per request.', 13, 1);
              return 1;
            end

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

          return 0;