USP_DATAFORMTEMPLATE_ADD_RESEARCHGROUPCOPYMEMBERS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@EXISTINGGROUPID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@CATEGORYCODEID	uniqueidentifier	IN
@OWNERID	uniqueidentifier	IN
@OTHERSCANVIEW	bit	IN
@OTHERSCANMODIFY	bit	IN
@SITES	xml	IN
@RESEARCHGROUPMEMBERS	xml	IN
Definition

Copy

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RESEARCHGROUPCOPYMEMBERS (
                        @ID uniqueidentifier = null output,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @CURRENTAPPUSERID uniqueidentifier,
                        @EXISTINGGROUPID uniqueidentifier = null,
                    @NAME nvarchar(100) = null,
                    @DESCRIPTION nvarchar(255) = null,
            @CATEGORYCODEID uniqueidentifier = null,
                    @OWNERID uniqueidentifier = null,
                    @OTHERSCANVIEW bit = 1,
                    @OTHERSCANMODIFY bit = 1,
                        @SITES xml = null,
                        @RESEARCHGROUPMEMBERS xml = null
                    ) with execute as owner as
                        set nocount on;

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


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

                        begin try
                            exec dbo.USP_RESEARCHGROUP_VALIDATESITES @SITES, @CURRENTAPPUSERID, '45DB2DB3-94AF-43FF-827A-C7C7032D92AA', 1;

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

                              --create Research Group
                              insert into dbo.RESEARCHGROUP (
                                  ID,
                                  NAME,
                                  DESCRIPTION,
                  CATEGORYCODEID,
                                  OWNERID,
                  OTHERSCANVIEW,
                                  OTHERSCANMODIFY,
                                  ADDEDBYID,
                                  CHANGEDBYID,
                                  DATEADDED,
                                  DATECHANGED
                              )
                              values (
                                  @ID,
                                  @NAME,
                                  @DESCRIPTION,
                  @CATEGORYCODEID,
                                  @OWNERID,
                  @OTHERSCANVIEW,
                                  @OTHERSCANMODIFY,
                                  @CHANGEAGENTID,
                                  @CHANGEAGENTID,
                                  @CURRENTDATE,
                                  @CURRENTDATE
                              );


                              if not @SITES is null
                                  exec dbo.USP_RESEARCHGROUP_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;

                exec USP_RESEARCHGROUP_GETMEMBERS_ADDFROMXML @ID, @RESEARCHGROUPMEMBERS, @CHANGEAGENTID, @CURRENTDATE;

              end
              else
              begin

                set @ID = @EXISTINGGROUPID;
                insert into [RESEARCHGROUPMEMBER] (
                  RESEARCHGROUPID,
                  CONSTITUENTID,
                  ADDEDBYID,
                  CHANGEDBYID,
                  DATEADDED,
                  DATECHANGED)
                select 
                  @ID, 
                  CONSTITUENTID,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE
                from dbo.UFN_RESEARCHGROUP_GETMEMBERS_FROMITEMLISTXML(@RESEARCHGROUPMEMBERS) as MEMBERS
                where not exists(select top 1 1 from dbo.RESEARCHGROUPMEMBER 
                                  where RESEARCHGROUPMEMBER.RESEARCHGROUPID = @ID
                                    and RESEARCHGROUPMEMBER.CONSTITUENTID = MEMBERS.CONSTITUENTID)

              end

              exec dbo.USP_RESEARCHGROUP_RECALCULATENUMMEMBERS @ID, @CHANGEAGENTID;

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

                        return 0;