USP_GLOBALCHANGE_DELETECONSTITUENTSITE

            CREATE procedure dbo.USP_GLOBALCHANGE_DELETECONSTITUENTSITE
            (
                @IDSETREGISTERID uniqueidentifier, 
                @SITEID uniqueidentifier,
                @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;

                declare @CURRENTDATE datetime

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

                declare @BYPASSSECURITY bit;
                declare @BPID uniqueidentifier;
                declare @BYPASSSITESECURITY bit;
                declare @USERHASSITEACCESS bit;

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

                set @USERHASSITEACCESS = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, @SITEID)

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

                -- If the user should not bypass security and does not have rights to the chosen site, exit the procedure.

                if @BYPASSSITESECURITY = 0 and @USERHASSITEACCESS = 0
                    return 0;

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

                declare @SELECTION TABLE (ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

                begin try
                    begin
                        declare @CONTEXTCACHE varbinary(128);

                        /* Cache current context information@ */
                        set @CONTEXTCACHE = CONTEXT_INFO();

                        /* Set CONTEXT_INFO to @CHANGEAGENTID */
                        set CONTEXT_INFO @CHANGEAGENTID;

                        if @IDSETREGISTERID is not null 
                            begin

                                /* filter out records for which the owner does not have rights. */
                                if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
                                begin
                                    insert into @SELECTION (ID) 
                                    select 
                                        SELECTEDCONSTITUENTS.ID 
                                    from 
                                        dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) SELECTEDCONSTITUENTS
                                        left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @BPID) as CONSTIT_RACS on SELECTEDCONSTITUENTS.ID = CONSTIT_RACS.ID
                                    where
                                        (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                                        and 
                                        (
                                            @BYPASSSITESECURITY = 1 or 
                                            exists 
                                            (
                                                select 1 
                                                from dbo.CONSTITUENTSITE
                                                where CONSTITUENTSITE.CONSTITUENTID = SELECTEDCONSTITUENTS.ID 
                                                and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, CONSTITUENTSITE.SITEID) = 1
                                            )
                                        )
                                end
                                else
                                begin
                                    insert into @SELECTION (ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID);
                                end

                                /* delete selected site records */
                                delete from dbo.CONSTITUENTSITE where CONSTITUENTSITE.SITEID = @SITEID and CONSTITUENTSITE.CONSTITUENTID in(select ID from @SELECTION)
                                set @NUMBERDELETED = @@ROWCOUNT;
                            end
                        else
                            begin

                                if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
                                begin
                                    /* delete all records for which the owner has rights. */
                                    delete from dbo.CONSTITUENTSITE 
                                    where CONSTITUENTID in 
                                    (
                                        select 
                                            CS.CONSTITUENTID
                                        from
                                            dbo.CONSTITUENTSITE CS
                                            left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @BPID) as CONSTIT_RACS on CS.CONSTITUENTID = CONSTIT_RACS.ID
                                        where 
                                            CS.SITEID = @SITEID 
                                            and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                                            and (@BYPASSSITESECURITY = 1 or @USERHASSITEACCESS = 1)
                                    )

                                end
                                else
                                begin
                                    /* delete all site records */
                                    delete from dbo.CONSTITUENTSITE where CONSTITUENTSITE.SITEID = @SITEID
                                end

                                set @NUMBERDELETED = @@ROWCOUNT;                                
                            end

                        /* Reset CONTEXT_INFO to previous value */
                        if not @contextCache is null
                            set CONTEXT_INFO @CONTEXTCACHE;
                    end

                end try

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