USP_GLOBALCHANGE_DELETECONSTITUENTSOLICITICODE

            CREATE procedure dbo.USP_GLOBALCHANGE_DELETECONSTITUENTSOLICITICODE
            (
                @IDSETREGISTERID uniqueidentifier = null, 
                @SOLICITCODEID uniqueidentifier  = 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;

                declare @CURRENTDATE datetime

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

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

                declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';
                declare @BYPASSSECURITY bit;
                declare @BYPASSSITESECURITY bit;                
                declare @USERHASSITEACCESS bit;
                declare @SITEID uniqueidentifier;

                select @SITEID = SITEID from dbo.SOLICITCODE where ID = @SOLICITCODEID;
                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 given solicit code, exit the procedure.

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

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

                begin try
                    -- retrieve records that the user can delete solicit codes from

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

                    delete dbo.CONSTITUENTSOLICITCODE
                    where
                        CONSTITUENTID in (select ID from @SELECTION)
                        and SOLICITCODEID = @SOLICITCODEID

                    set @NUMBERDELETED = @@ROWCOUNT
                end try

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