USP_MERGETASK_CONSTITUENTAPPEAL
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @DELETEDUPES | bit | IN |
Definition
Copy
CREATE procedure dbo.[USP_MERGETASK_CONSTITUENTAPPEAL]
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@DELETEDUPES bit = 0
)
as
set nocount on;
declare @SEGMENTATIONID uniqueidentifier;
declare @MAILINGTYPECODE tinyint;
declare @TABLENAME nvarchar(128);
declare @COLUMNNAME nvarchar(128);
declare @BASETABLENAME nvarchar(128);
declare @BASEPRIMARYKEYFIELD nvarchar(128);
declare @IDFIELD nvarchar(128);
declare @DONORIDFIELD nvarchar(128);
declare @SQL nvarchar(max);
declare @CURRENTDATE datetime = getdate();
begin try
/*****************************/
/* Merge constituent appeals */
/*****************************/
if @DELETEDUPES = 0
update dbo.[CONSTITUENTAPPEAL] set
[CONSTITUENTID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [CONSTITUENTID] = @SOURCEID;
else
--Omit duplicate appeals if the @DELETEDUPES flag is set
update dbo.[CONSTITUENTAPPEAL] set
[CONSTITUENTID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [CONSTITUENTID] = @SOURCEID
and [ID] not in
(
select a.[ID]
from dbo.[CONSTITUENTAPPEAL] a
inner join dbo.[CONSTITUENTAPPEAL] b on a.[APPEALID] = b.[APPEALID]
and (a.[MKTSEGMENTATIONID] = b.[MKTSEGMENTATIONID] or (a.[MKTSEGMENTATIONID] is null and b.[MKTSEGMENTATIONID] is null))
and (a.[MKTSEGMENTATIONSEGMENTID] = b.[MKTSEGMENTATIONSEGMENTID] or (a.[MKTSEGMENTATIONSEGMENTID] is null and b.[MKTSEGMENTATIONSEGMENTID] is null))
and (a.[MKTSEGMENTATIONTESTSEGMENTID] = b.[MKTSEGMENTATIONTESTSEGMENTID] or (a.[MKTSEGMENTATIONTESTSEGMENTID] is null and b.[MKTSEGMENTATIONTESTSEGMENTID] is null))
and a.[FINDERNUMBER] = b.[FINDERNUMBER]
where a.[CONSTITUENTID] = @SOURCEID
and b.[CONSTITUENTID] = @TARGETID
);
/*********************************************************************************************************************************************/
/* Notes for merging Marketing Effort related data: */
/* 1) Ignore @DELETEDUPES for all marketing effort related data below because it is important that we keep all records around and associated */
/* with the current constituent so that mailing totals, counts, costs, etc. stay the same. */
/* 2) Since this merge task can only be run on BBEC-based systems, and for performance reasons, we are using known tables and IDs below */
/* so that we don't waste the extra cycles on unnecessary record source and product flag checks. */
/*********************************************************************************************************************************************/
/*****************************/
/* Merge mailing data tables */
/*****************************/
declare MAILINGCURSOR cursor local fast_forward for
select distinct
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATION].[MAILINGTYPECODE]
from dbo.[CONSTITUENTSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [CONSTITUENTSEGMENT].[SEGMENTID]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
where [CONSTITUENTSEGMENT].[CONSTITUENTID] = @SOURCEID;
open MAILINGCURSOR;
fetch next from MAILINGCURSOR into @SEGMENTATIONID, @MAILINGTYPECODE;
while (@@fetch_status = 0)
begin
--Mailing data table...
set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
begin
if @MAILINGTYPECODE in (1, 5) --Acknowledgement
begin
set @BASETABLENAME = 'REVENUE';
set @BASEPRIMARYKEYFIELD = 'ID';
set @IDFIELD = 'REVENUEID';
set @DONORIDFIELD = 'CONSTITUENTID';
end
else if @MAILINGTYPECODE = 2 --Membership
begin
set @BASETABLENAME = 'MEMBER';
set @BASEPRIMARYKEYFIELD = 'MEMBERSHIPID';
set @IDFIELD = 'MEMBERSHIPID';
set @DONORIDFIELD = 'CONSTITUENTID';
end
else if @MAILINGTYPECODE = 3 --Sponsorship
begin
set @BASETABLENAME = 'SPONSORSHIP';
set @BASEPRIMARYKEYFIELD = 'ID';
set @IDFIELD = 'SPONSORSHIPID';
set @DONORIDFIELD = 'CONSTITUENTID';
end
else
set @BASETABLENAME = null;
--Keep track of the very original constituent IDs from a mailing. If a constituent has already been merged once, then
--we don't want subsequent merges to overwrite the very original constituent ID, so we can check their findernumbers to
--see if it is the very original constituent ID or not (basically if the finder number already exists in the table, then
--we don't overwrite it). We only want to save the very original constituent ID that was in the mailing when it was activated.
--Also, update the mailing data table with the new merged constituent ID.
--
--For mailings that are not constituent based (acknowledgements, membership, sponsorship), we need to make sure the base-record
--was actually merged onto the new constituent or not. This can happen because the user can setup different "merge configurations"
--with different options and may choose to not merge some data. This would leave the base-record on the old constituent, and in
--this case it would not be appropriate to update the constituent ID in our mailing data table. Some merge tasks will delete the
--base record, so we need to use a "left" join and "is null" to account for those.
--
--This exact same dynamic SQL is used in "Blackbaud.AppFx.Marketing.Catalog\USP_MKTSEGMENTATIONACTIVATE_MERGECONSTITUENTS.xml". Please
--make sure both of these files stay consistent when modifications are made.
set @SQL = 'insert into dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] ([SEGMENTATIONID], [FINDERNUMBER], [ORIGINALCONSTITUENTID])' + char(13) +
' select' + char(13) +
' @SEGMENTATIONID,' + char(13) +
' [DONORS].[FINDERNUMBER],' + char(13) +
' @SOURCEID' + char(13) +
' from dbo.[' + @TABLENAME + '] as [DONORS]' + char(13) +
' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DONORS].[SEGMENTID] and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0' + char(13);
if @BASETABLENAME is not null
--Some merge tasks will delete the base record, so we need to "left" join (instead of "inner" join) and add an "is null" to the "where" clause to take that into account...
set @SQL += ' left join dbo.[' + @BASETABLENAME + '] on [' + @BASETABLENAME + '].[' + @BASEPRIMARYKEYFIELD + '] = [DONORS].[' + @IDFIELD + ']' + (case when @MAILINGTYPECODE = 2 then ' and [MEMBER].[ISPRIMARY] = 1 and [MEMBER].[ISDROPPED] = 0' else '' end) + char(13);
set @SQL += ' where [DONORS].[DONORQUERYVIEWCATALOGID] = ''DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0''' + char(13) +
' and [DONORS].[DONORID] = @SOURCEID' + char(13);
if @BASETABLENAME is not null
set @SQL += ' and ([' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] is null or [' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] <> @SOURCEID)' + char(13);
set @SQL += ' and not exists(select * from dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] where [FINDERNUMBER] = [DONORS].[FINDERNUMBER] and [SEGMENTATIONID] = @SEGMENTATIONID);' + char(13) +
char(13) +
'update dbo.[' + @TABLENAME + '] set' + char(13) +
' [DONORID] = @TARGETID' + char(13) +
'from dbo.[' + @TABLENAME + '] as [DONORS]' + char(13);
if @BASETABLENAME is not null
--Some merge tasks will delete the base record, so we need to "left" join (instead of "inner" join) and add an "is null" to the "where" clause to take that into account...
set @SQL += 'left join dbo.[' + @BASETABLENAME + '] on [' + @BASETABLENAME + '].[' + @BASEPRIMARYKEYFIELD + '] = [DONORS].[' + @IDFIELD + ']' + (case when @MAILINGTYPECODE = 2 then ' and [MEMBER].[ISPRIMARY] = 1 and [MEMBER].[ISDROPPED] = 0' else '' end) + char(13);
set @SQL += 'where [DONORS].[DONORQUERYVIEWCATALOGID] = ''DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0''' + char(13) +
'and [DONORS].[DONORID] = @SOURCEID' + (case when @BASETABLENAME is null then ';' else '' end) + char(13);
if @BASETABLENAME is not null
set @SQL += 'and ([' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] is null or [' + @BASETABLENAME + '].[' + @DONORIDFIELD + '] <> @SOURCEID);' + char(13);
exec sp_executesql @SQL, N'@SOURCEID uniqueidentifier, @TARGETID uniqueidentifier, @SEGMENTATIONID uniqueidentifier', @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @SEGMENTATIONID = @SEGMENTATIONID;
end
fetch next from MAILINGCURSOR into @SEGMENTATIONID, @MAILINGTYPECODE;
end
close MAILINGCURSOR;
deallocate MAILINGCURSOR;
/****************************/
/* Merge Finder File tables */
/****************************/
--They could have mapped a constituent ID during a finder file import process. If so, then we need to merge the constituent IDs
--because it will affect which constituent gets pulled during finder number lookup on a payment record or in batch. Check to see
--if they even have any finder files that mapped constituent ID as an import field by comparing the list layout field mappings to
--the primary key of the recordsource query view, and then only loop through those. Even though the finder files are imported
--per mailing, we cannot handle this in the mailing data table cursor above because we don't have any record of the imported
--constituent IDs in the CONSTITUENTSEGMENT table.
declare FINDERFILECURSOR cursor local fast_forward for
select distinct
[MKTFINDERFILEIMPORTPROCESS].[SEGMENTATIONID],
[MKTLISTLAYOUTFIELD].[FIELDNAME]
from dbo.[MKTFINDERFILEIMPORTPROCESS]
inner join dbo.[MKTLISTLAYOUT] on [MKTLISTLAYOUT].[ID] = [MKTFINDERFILEIMPORTPROCESS].[LISTLAYOUTID]
inner join dbo.[MKTLISTLAYOUTFIELD] on [MKTLISTLAYOUTFIELD].[LISTLAYOUTID] = [MKTLISTLAYOUT].[ID]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTLISTLAYOUT].[RECORDSOURCEID] and [QUERYVIEWCATALOG].[OBJECTNAME] = [MKTLISTLAYOUTFIELD].[OBJECTNAME] and [QUERYVIEWCATALOG].[PRIMARYKEYFIELD] = [MKTLISTLAYOUTFIELD].[FIELDNAME]
where [MKTFINDERFILEIMPORTPROCESS].[STATUSCODE] = 1
and [MKTFINDERFILEIMPORTPROCESS].[QUANTITY] > 0
and [MKTLISTLAYOUT].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0'
and [MKTLISTLAYOUTFIELD].[MAPPINGCODE] = 1 --system
and [MKTLISTLAYOUTFIELD].[DATATYPE] = 8; --guid
open FINDERFILECURSOR;
fetch next from FINDERFILECURSOR into @SEGMENTATIONID, @COLUMNNAME;
while (@@fetch_status = 0)
begin
set @TABLENAME = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@SEGMENTATIONID);
if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME and [COLUMN_NAME] = @COLUMNNAME)
begin
set @SQL = 'update dbo.[' + @TABLENAME + '] set' + char(13) +
' [' + @COLUMNNAME + '] = @TARGETID' + char(13) +
'where [' + @COLUMNNAME + '] = @SOURCEID';
exec sp_executesql @SQL, N'@SOURCEID uniqueidentifier, @TARGETID uniqueidentifier', @SOURCEID = @SOURCEID, @TARGETID = @TARGETID;
end
fetch next from FINDERFILECURSOR into @SEGMENTATIONID, @COLUMNNAME;
end
close FINDERFILECURSOR;
deallocate FINDERFILECURSOR;
/**********************************/
/* Merge CONSTITUENTSEGMENT table */
/**********************************/
update dbo.[CONSTITUENTSEGMENT] set
[CONSTITUENTID] = @TARGETID
where [CONSTITUENTID] = @SOURCEID;
/******************************************/
/* Merge Source Analysis Rule (SAR) table */
/******************************************/
update dbo.[MKTSOURCEANALYSISRULEDATA_DFB4B8C1_5E9A_4C14_ACE3_01C096B53BA0] set
[DONORID] = @TARGETID
where [DONORID] = @SOURCEID;
/******************************************/
/* Merge acquisition list matchback table */
/******************************************/
update dbo.[MKTSEGMENTLISTDATADONORS_DFB4B8C1_5E9A_4C14_ACE3_01C096B53BA0] set
[DONORID] = @TARGETID
where [DONORID] = @SOURCEID;
/******************************************/
/* Finder number constituent table */
/******************************************/
if exists(select 1 from dbo.[MKTFINDERNUMBERCONSTITUENT] where [ID] = @SOURCEID)
begin
--first we need to retrieve the information for the source data because there can never be 2 rows with the same constituent ID
declare
@MKTFINDERNUMBERCONSTITUENTAPPEALID uniqueidentifier,
@APPEALID uniqueidentifier,
@MKTSEGMENTATIONID uniqueidentifier,
@SOURCECODE nvarchar(50),
@FINDERNUMBER bigint,
@DATESENT datetime,
@COMMENTS nvarchar(50),
@MKTPACKAGEID uniqueidentifier,
@MKTSEGMENTATIONSEGMENTID uniqueidentifier,
@MKTSEGMENTATIONTESTSEGMENTID uniqueidentifier,
@MKTFINDERNUMBERCONSTITUENTSITEID uniqueidentifier,
@SITEID uniqueidentifier,
@KEYNAME nvarchar(100),
@KEYNAMEPREFIX nvarchar(50),
@FIRSTNAME nvarchar(50),
@MIDDLENAME nvarchar(50),
@MAIDENNAME nvarchar(100),
@NICKNAME nvarchar(50),
@TITLECODEID uniqueidentifier,
@SUFFIXCODEID uniqueidentifier,
@GENDERCODE tinyint,
@BIRTHDATE char(8),
@ISORGANIZATION bit,
@MARITALSTATUSCODEID uniqueidentifier,
@INDUSTRYCODEID uniqueidentifier,
@NUMEMPLOYEES int,
@NUMSUBSIDIARIES int,
@PARENTCORPID uniqueidentifier,
@ADDRESSTYPECODEID uniqueidentifier,
@DONOTMAIL bit,
@DONOTMAILREASONCODEID uniqueidentifier,
@COUNTRYID uniqueidentifier,
@STATEID uniqueidentifier,
@ADDRESSBLOCK nvarchar(150),
@CITY nvarchar(50),
@POSTCODE nvarchar(12),
@DPC nvarchar(8),
@CART nvarchar(10),
@LOT nvarchar(5),
@OMITFROMVALIDATION bit,
@COUNTYCODEID uniqueidentifier,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@LASTVALIDATIONATTEMPTDATE datetime,
@VALIDATIONMESSAGE nvarchar(200),
@CERTIFICATIONDATA int,
@PHONETYPECODEID uniqueidentifier,
@NUMBER nvarchar(100),
@EMAILADDRESSTYPECODEID uniqueidentifier,
@EMAILADDRESS nvarchar(100),
@CURRENTAPPUSERID uniqueidentifier,
@WEBADDRESS nvarchar(2047),
@ISGROUP bit,
@GIVESANONYMOUSLY bit,
@GROUPTYPECODE tinyint,
@GROUPTYPEID uniqueidentifier,
@GROUPDESCRIPTION nvarchar(300),
@GROUPSTARTDATE datetime,
@NAMEFORMATFUNCTIONID uniqueidentifier,
@DONOTCALL bit,
@DONOTEMAIL bit,
@TITLE2CODEID uniqueidentifier,
@SUFFIX2CODEID uniqueidentifier,
@GENDERCODEID uniqueidentifier;
--first get constituent data
select
@KEYNAME = [KEYNAME],
@KEYNAMEPREFIX = [KEYNAMEPREFIX],
@FIRSTNAME = [FIRSTNAME],
@MIDDLENAME = [MIDDLENAME],
@MAIDENNAME = [MAIDENNAME],
@NICKNAME = [NICKNAME],
@TITLECODEID = [TITLECODEID],
@SUFFIXCODEID = [SUFFIXCODEID],
@GENDERCODE = [GENDERCODE],
@BIRTHDATE = [BIRTHDATE],
@ISORGANIZATION = [ISORGANIZATION],
@MARITALSTATUSCODEID = [MARITALSTATUSCODEID],
@INDUSTRYCODEID = [INDUSTRYCODEID],
@NUMEMPLOYEES = [NUMEMPLOYEES],
@NUMSUBSIDIARIES = [NUMSUBSIDIARIES],
@PARENTCORPID = [PARENTCORPID],
@ADDRESSTYPECODEID = [ADDRESSTYPECODEID],
@DONOTMAIL = [DONOTMAIL],
@DONOTMAILREASONCODEID = [DONOTMAILREASONCODEID],
@COUNTRYID = [COUNTRYID],
@STATEID = [STATEID],
@ADDRESSBLOCK = [ADDRESSBLOCK],
@CITY = [CITY],
@POSTCODE = [POSTCODE],
@DPC = [DPC],
@CART = [CART],
@LOT = [LOT],
@OMITFROMVALIDATION = [OMITFROMVALIDATION],
@COUNTYCODEID = [COUNTYCODEID],
@CONGRESSIONALDISTRICTCODEID = [CONGRESSIONALDISTRICTCODEID],
@LASTVALIDATIONATTEMPTDATE = [LASTVALIDATIONATTEMPTDATE],
@VALIDATIONMESSAGE = [VALIDATIONMESSAGE],
@CERTIFICATIONDATA = [CERTIFICATIONDATA],
@PHONETYPECODEID = [PHONETYPECODEID],
@NUMBER = [NUMBER],
@EMAILADDRESSTYPECODEID = [EMAILADDRESSTYPECODEID],
@EMAILADDRESS = [EMAILADDRESS],
@CURRENTAPPUSERID = [CURRENTAPPUSERID],
@WEBADDRESS = [WEBADDRESS],
@ISGROUP = [ISGROUP],
@GIVESANONYMOUSLY = [GIVESANONYMOUSLY],
@GROUPTYPECODE = [GROUPTYPECODE],
@GROUPTYPEID = [GROUPTYPEID],
@GROUPDESCRIPTION = [GROUPDESCRIPTION],
@GROUPSTARTDATE = [GROUPSTARTDATE],
@NAMEFORMATFUNCTIONID = [NAMEFORMATFUNCTIONID],
@DONOTCALL = [DONOTCALL],
@DONOTEMAIL = [DONOTEMAIL],
@TITLE2CODEID = [TITLE2CODEID],
@SUFFIX2CODEID = [SUFFIX2CODEID],
@GENDERCODEID = [GENDERCODEID]
from
dbo.[MKTFINDERNUMBERCONSTITUENT]
where
[ID] = @SOURCEID;
--constituent appeal information
select
@MKTFINDERNUMBERCONSTITUENTAPPEALID = [ID],
@APPEALID = [APPEALID],
@MKTSEGMENTATIONID = [MKTSEGMENTATIONID],
@SOURCECODE = [SOURCECODE],
@FINDERNUMBER = [FINDERNUMBER],
@DATESENT = [DATESENT],
@COMMENTS = [COMMENTS],
@MKTPACKAGEID = [MKTPACKAGEID],
@MKTSEGMENTATIONSEGMENTID = [MKTSEGMENTATIONSEGMENTID],
@MKTSEGMENTATIONTESTSEGMENTID = [MKTSEGMENTATIONTESTSEGMENTID]
from
dbo.[MKTFINDERNUMBERCONSTITUENTAPPEAL]
where
[CONSTITUENTID] = @SOURCEID;
--constituent site
select
@MKTFINDERNUMBERCONSTITUENTSITEID = [ID],
@SITEID = [SITEID]
from
dbo.[MKTFINDERNUMBERCONSTITUENTSITE]
where
[MKTFINDERNUMBERCONSTITUENTID] = @SOURCEID;
--now we delete the source since we cannot have them both in these tables
delete
dbo.[MKTFINDERNUMBERCONSTITUENT]
where
[ID] = @SOURCEID;
--only if the target ID does not already exist do we need to insert an entry into MKTFINDERNUMBERCONSTITUENT
if not exists(select 1 from dbo.[MKTFINDERNUMBERCONSTITUENT] where [ID] = @TARGETID)
begin
insert dbo.[MKTFINDERNUMBERCONSTITUENT]
([ID], [KEYNAME], [KEYNAMEPREFIX], [FIRSTNAME], [MIDDLENAME], [MAIDENNAME], [NICKNAME], [TITLECODEID], [SUFFIXCODEID], [GENDERCODE], [BIRTHDATE], [ISORGANIZATION], [MARITALSTATUSCODEID], [INDUSTRYCODEID], [NUMEMPLOYEES], [NUMSUBSIDIARIES], [PARENTCORPID], [ADDRESSTYPECODEID], [DONOTMAIL], [DONOTMAILREASONCODEID], [COUNTRYID], [STATEID], [ADDRESSBLOCK], [CITY], [POSTCODE], [DPC], [CART], [LOT], [OMITFROMVALIDATION], [COUNTYCODEID], [CONGRESSIONALDISTRICTCODEID], [LASTVALIDATIONATTEMPTDATE], [VALIDATIONMESSAGE], [CERTIFICATIONDATA], [PHONETYPECODEID], [NUMBER], [EMAILADDRESSTYPECODEID], [EMAILADDRESS], [CURRENTAPPUSERID], [WEBADDRESS], [ISGROUP], [GIVESANONYMOUSLY], [GROUPTYPECODE], [GROUPTYPEID], [GROUPDESCRIPTION], [GROUPSTARTDATE], [NAMEFORMATFUNCTIONID], [DONOTCALL], [DONOTEMAIL], [TITLE2CODEID], [SUFFIX2CODEID], [GENDERCODEID])
values
(@TARGETID, @KEYNAME, @KEYNAMEPREFIX, @FIRSTNAME, @MIDDLENAME, @MAIDENNAME, @NICKNAME, @TITLECODEID, @SUFFIXCODEID, @GENDERCODE, @BIRTHDATE, @ISORGANIZATION, @MARITALSTATUSCODEID, @INDUSTRYCODEID, @NUMEMPLOYEES, @NUMSUBSIDIARIES, @PARENTCORPID, @ADDRESSTYPECODEID, @DONOTMAIL, @DONOTMAILREASONCODEID, @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @DPC, @CART, @LOT, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @PHONETYPECODEID, @NUMBER, @EMAILADDRESSTYPECODEID, @EMAILADDRESS, @CURRENTAPPUSERID, @WEBADDRESS, @ISGROUP, @GIVESANONYMOUSLY, @GROUPTYPECODE, @GROUPTYPEID, @GROUPDESCRIPTION, @GROUPSTARTDATE, @NAMEFORMATFUNCTIONID, @DONOTCALL, @DONOTEMAIL, @TITLE2CODEID, @SUFFIX2CODEID, @GENDERCODEID);
end
if @MKTFINDERNUMBERCONSTITUENTAPPEALID is not null
insert dbo.[MKTFINDERNUMBERCONSTITUENTAPPEAL]
([ID], [CONSTITUENTID], [APPEALID], [MKTSEGMENTATIONID], [SOURCECODE], [FINDERNUMBER], [DATESENT], [COMMENTS], [MKTPACKAGEID], [MKTSEGMENTATIONSEGMENTID], [MKTSEGMENTATIONTESTSEGMENTID])
values
(@MKTFINDERNUMBERCONSTITUENTAPPEALID, @TARGETID, @APPEALID, @MKTSEGMENTATIONID, @SOURCECODE, @FINDERNUMBER, @DATESENT, @COMMENTS, @MKTPACKAGEID, @MKTSEGMENTATIONSEGMENTID, @MKTSEGMENTATIONTESTSEGMENTID);
if @MKTFINDERNUMBERCONSTITUENTSITEID is not null
insert dbo.[MKTFINDERNUMBERCONSTITUENTSITE]
([ID], [MKTFINDERNUMBERCONSTITUENTID], [SITEID])
values
(@MKTFINDERNUMBERCONSTITUENTSITEID, @TARGETID, @SITEID);
end
/******************************************/
/* Merge plan item task table */
/******************************************/
update dbo.[MKTMARKETINGPLANITEMTASK] set
[OWNERID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [OWNERID] = @SOURCEID;
/******************************************/
/* Merge appeal mailing task table */
/******************************************/
update dbo.[APPEALMAILINGTASK] set
[OWNERID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [OWNERID] = @SOURCEID;
/******************************************/
/* Merge appeal attachment table */
/******************************************/
update dbo.[APPEALATTACHMENT] set
[AUTHORID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [AUTHORID] = @SOURCEID;
/******************************************/
/* Merge appeal media link table */
/******************************************/
update dbo.[APPEALMEDIALINK] set
[AUTHORID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [AUTHORID] = @SOURCEID;
/******************************************/
/* Merge appeal note table */
/******************************************/
update dbo.[APPEALNOTE] set
[AUTHORID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [AUTHORID] = @SOURCEID;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;