USP_GLOBALCHANGE_DELETEMAILPREFERENCE

Parameters

Parameter	Parameter Type	Mode
@IDSETREGISTERID	uniqueidentifier	IN
@MAILTYPECODE	tinyint	IN
@ACKNOWLEDGEMENTID	uniqueidentifier	IN
@CORRESPONDENCEID	uniqueidentifier	IN
@PLEDGEREMINDERID	uniqueidentifier	IN
@BUSINESSUNITCODEID	uniqueidentifier	IN
@CATEGORYCODEID	uniqueidentifier	IN
@EVENTCATEGORYCODEID	uniqueidentifier	IN
@SITEID	uniqueidentifier	IN
@CORRESPONDENCECODEID	uniqueidentifier	IN
@RECEIPTTYPECODE	int	IN
@PURPOSEID	uniqueidentifier	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_DELETEMAILPREFERENCE
            (
                @IDSETREGISTERID uniqueidentifier = null, 
                @MAILTYPECODE tinyint = null,
                @ACKNOWLEDGEMENTID uniqueidentifier = null,
                @CORRESPONDENCEID uniqueidentifier = null,
                @PLEDGEREMINDERID uniqueidentifier = null,
                @BUSINESSUNITCODEID uniqueidentifier = null,
                @CATEGORYCODEID uniqueidentifier = null,
                @EVENTCATEGORYCODEID uniqueidentifier = null,
                @SITEID uniqueidentifier = null,
                @CORRESPONDENCECODEID uniqueidentifier = null,
                @RECEIPTTYPECODE int = 0,
                @PURPOSEID 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; 
                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;

                begin try

                    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);

                    if @MAILTYPECODE is null
                    begin
                        delete dbo.MAILPREFERENCE
                        where CONSTITUENTID in (select ID from @SELECTION)
                    end
                    else
                    begin                
                        delete dbo.MAILPREFERENCE
                        where CONSTITUENTID in (select ID from @SELECTION) and
                            (
                                (MAILTYPECODE = @MAILTYPECODE) and
                                ((BUSINESSUNITCODEID = @BUSINESSUNITCODEID) or (BUSINESSUNITCODEID is null and @BUSINESSUNITCODEID is null)) and
                                ((CATEGORYCODEID = @CATEGORYCODEID) or (CATEGORYCODEID is null and @CATEGORYCODEID is null)) and
                                ((EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID) or (EVENTCATEGORYCODEID is null and @EVENTCATEGORYCODEID is null)) and
                                ((SITEID = @SITEID) or (SITEID is null and @SITEID is null)) and
                                ((ACKNOWLEDGEMENTID = @ACKNOWLEDGEMENTID) or (ACKNOWLEDGEMENTID is null and @ACKNOWLEDGEMENTID is null)) and
                                ((CORRESPONDENCEID = @CORRESPONDENCEID) or (CORRESPONDENCEID is null and @CORRESPONDENCEID is null)) and
                                ((PLEDGEREMINDERID = @PLEDGEREMINDERID) or (PLEDGEREMINDERID is null and @PLEDGEREMINDERID is null)) and
                                ((CORRESPONDENCECODEID = @CORRESPONDENCECODEID) or (CORRESPONDENCECODEID is null and @CORRESPONDENCECODEID is null)) and
                                ((PURPOSEID = @PURPOSEID) or (PURPOSEID is null and @PURPOSEID is null))
                            )
                    end

                    set @NUMBERDELETED = @@ROWCOUNT

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