USP_GLOBALCHANGE_ADDCONSTITUENTINTERACTION

Parameters

Parameter	Parameter Type	Mode
@IDSETREGISTERID	uniqueidentifier	IN
@EXPECTEDDATE	datetime	IN
@ACTUALDATE	datetime	IN
@FUNDRAISERID	uniqueidentifier	IN
@INTERACTIONTYPECODEID	uniqueidentifier	IN
@OBJECTIVE	nvarchar(100)	IN
@STATUSCODE	tinyint	IN
@COMMENT	nvarchar(max)	IN
@EVENTID	uniqueidentifier	IN
@PARTICIPANTS	xml	IN
@INTERACTIONCATEGORYID	uniqueidentifier	IN
@INTERACTIONSUBCATEGORYID	uniqueidentifier	IN
@SITES	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN
@ASOF	datetime	IN
@NUMBERADDED	int	INOUT
@NUMBEREDITED	int	INOUT
@NUMBERDELETED	int	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_GLOBALCHANGE_ADDCONSTITUENTINTERACTION
            (
                @IDSETREGISTERID uniqueidentifier = null,
                @EXPECTEDDATE datetime = null,
                @ACTUALDATE datetime = null,
                @FUNDRAISERID uniqueidentifier = null,
                @INTERACTIONTYPECODEID uniqueidentifier,
                @OBJECTIVE nvarchar(100) = '',
                @STATUSCODE tinyint = 0,
                @COMMENT nvarchar(max) = '',
                @EVENTID uniqueidentifier = null, 
                @PARTICIPANTS xml = null,
                @INTERACTIONCATEGORYID uniqueidentifier = null,
                @INTERACTIONSUBCATEGORYID uniqueidentifier = null,
                @SITES xml = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as            
                set nocount off;

                set @NUMBERADDED = 0;
                set @NUMBEREDITED = 0;
                set @NUMBERDELETED = 0; 

                if @IDSETREGISTERID is null begin
                    raiserror ('IDSETREGISTERID is required',13,1);
                end

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

                declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';

                if @SITES is not null begin
                    set @SITES = (SELECT 
                                        ID, 
                                        SITEID
                                    FROM 
                                        dbo.UFN_CONSTITUENTINTERACTION_GETSITES_FROMITEMLISTXML(@SITES)
                                    where
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, SITEID) = 1
                    or dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                                        for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64);
                end                

                declare @BYPASSSECURITY bit;
                declare @BYPASSSITESECURITY bit;

                exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;                                

                declare @IDSETRECORDS table (CONSTITUENTID uniqueidentifier, INTERACTIONID uniqueidentifier);

                insert into @IDSETRECORDS(CONSTITUENTID, INTERACTIONID)
                    select 
                        ID, 
                        newID() 
                    from 
                        dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @IDSETREGISTERID, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY)

                insert into dbo.INTERACTION (
                    ID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    CONSTITUENTID,
                    EXPECTEDDATE,
                    ACTUALDATE,
                    FUNDRAISERID,
                    INTERACTIONTYPECODEID,
                    OBJECTIVE,
                    STATUSCODE,
                    COMMENT,
                    EVENTID,
                    INTERACTIONSUBCATEGORYID) 
                select
                    IDSETRECORDS.INTERACTIONID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    IDSETRECORDS.CONSTITUENTID,
                    @EXPECTEDDATE,
                    @ACTUALDATE,
                    @FUNDRAISERID,
                    @INTERACTIONTYPECODEID,
                    @OBJECTIVE,
                    @STATUSCODE,
                    @COMMENT,
                    @EVENTID,
                    @INTERACTIONSUBCATEGORYID
                from
                    @IDSETRECORDS as IDSETRECORDS

                set @NUMBERADDED = @@ROWCOUNT;

                declare IDSETCURSOR cursor local fast_forward for
                    select
                        INTERACTIONID
                    from
                        @IDSETRECORDS

                declare @INTERACTIONID uniqueidentifier;
                declare @CURRENTDATE datetime;

                set @CURRENTDATE = getdate();

                open IDSETCURSOR;

                fetch next from IDSETCURSOR into @INTERACTIONID;

                while (@@FETCH_STATUS = 0)
                begin                    
                    exec dbo.USP_CONSTITUENTINTERACTION_GETSITES_ADDFROMXML @INTERACTIONID, @SITES, @CHANGEAGENTID, @CURRENTDATE;                                            
                    exec dbo.USP_INTERACTION_PARTICIPANTS_ADDFROMXML @INTERACTIONID, @PARTICIPANTS, @CHANGEAGENTID;

                    fetch next from IDSETCURSOR into @INTERACTIONID;
                end

                close IDSETCURSOR;                
                deallocate IDSETCURSOR;
                return 0;