USP_CONVERTDONOTMAILTOSOLICITCODE
Converts constituent do-not-mail, do-not-phone and do-not-email information to solicit codes
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.USP_CONVERTDONOTMAILTOSOLICITCODE(@CHANGEAGENTID uniqueidentifier )
as
declare @DONOTMAILID uniqueidentifier
declare @DONOTPHONEID uniqueidentifier
declare @DONOTEMAILID uniqueidentifier
declare @DONOTMAIL varchar(20)
declare @DONOTPHONE varchar(20)
declare @DONOTEMAIL varchar(20)
set @DONOTMAIL ='Do not mail'
set @DONOTPHONE = 'Do not phone'
set @DONOTEMAIL = 'Do not email'
if not exists (select ID from dbo.SOLICITCODE where DESCRIPTION=@DONOTMAIL and SITEID is null) insert into SOLICITCODE (DESCRIPTION, ADDEDBYID, CHANGEDBYID) values (@DONOTMAIL, @CHANGEAGENTID,@CHANGEAGENTID )
if not exists (select ID from dbo.SOLICITCODE where DESCRIPTION=@DONOTPHONE and SITEID is null) insert into SOLICITCODE (DESCRIPTION, ADDEDBYID, CHANGEDBYID) values (@DONOTPHONE, @CHANGEAGENTID,@CHANGEAGENTID )
if not exists (select ID from dbo.SOLICITCODE where DESCRIPTION=@DONOTEMAIL and SITEID is null) insert into SOLICITCODE (DESCRIPTION, ADDEDBYID, CHANGEDBYID) values (@DONOTEMAIL, @CHANGEAGENTID,@CHANGEAGENTID)
select top 1 @DONOTMAILID= ID from dbo.SOLICITCODE where DESCRIPTION =@DONOTMAIL and SITEID is null
select top 1 @DONOTPHONEID=ID from dbo.SOLICITCODE where DESCRIPTION =@DONOTPHONE and SITEID is null
select top 1 @DONOTEMAILID=ID from dbo.SOLICITCODE where DESCRIPTION =@DONOTEMAIL and SITEID is null
insert into dbo.CONSTITUENTSOLICITCODE (CONSTITUENTID, SOLICITCODEID, ADDEDBYID, CHANGEDBYID, COMMENTS) select ID, @DONOTMAILID, @CHANGEAGENTID, @CHANGEAGENTID, 'Converted from constituent record' from dbo.CONSTITUENT where DONOTMAIL=1
insert into dbo.CONSTITUENTSOLICITCODE (CONSTITUENTID, SOLICITCODEID, ADDEDBYID, CHANGEDBYID, COMMENTS) select ID, @DONOTPHONEID, @CHANGEAGENTID, @CHANGEAGENTID, 'Converted from constituent record' from dbo.CONSTITUENT where DONOTPHONE=1
insert into dbo.CONSTITUENTSOLICITCODE (CONSTITUENTID, SOLICITCODEID, ADDEDBYID, CHANGEDBYID, COMMENTS) select ID, @DONOTEMAILID, @CHANGEAGENTID, @CHANGEAGENTID, 'Converted from constituent record' from dbo.CONSTITUENT where DONOTEMAIL=1