USP_GLOBALCHANGE_CLEANUPINACTIVEPROSPECTSINTERACTIONS

            CREATE procedure dbo.USP_GLOBALCHANGE_CLEANUPINACTIVEPROSPECTSINTERACTIONS
            (
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @IDSETREGISTERID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
                set nocount off;

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate();
                set @NUMBERADDED = 0;
                set @NUMBEREDITED = 0;
                set @NUMBERDELETED = 0; 


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

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

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

                declare @SELECTION table (ID uniqueidentifier not null primary key)

                    insert into @SELECTION (ID)
                    select ID
                    from dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @IDSETREGISTERID, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY);

                begin try
                    update INTERACTION set
                        STATUSCODE = 4, -- Cancelled

                        ACTUALDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    from dbo.INTERACTION
                    where
                        PROSPECTPLANID is not null and
                        STATUSCODE in (0, 1) and -- Planned or Pending

                        dbo.UFN_PROSPECTPLAN_ISACTIVEPROSPECT(PROSPECTPLANID) = 0 and
                        INTERACTION.CONSTITUENTID in (select ID from @SELECTION)

                    set @NUMBEREDITED = @@ROWCOUNT
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch