USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHDUPLICATEAUTOMATCH_2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(255) | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @PRIMARYRECORDID | uniqueidentifier | IN | |
| @DOMANUALREVIEWFORAUTOMATCH | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHDUPLICATEAUTOMATCH_2
(
@ID nvarchar(255), -- The BATCHCONSTITUENTUPDATE.ID field for the row.
@CHANGEAGENTID uniqueidentifier = null,
@PRIMARYRECORDID uniqueidentifier,
@DOMANUALREVIEWFORAUTOMATCH bit
)
as
set nocount on;
declare @BATCHCONSTITUENTUPDATEID uniqueidentifier;
declare @CURRENTDATE datetime;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @CURRENTDATE = getdate()
set @BATCHCONSTITUENTUPDATEID = convert(uniqueidentifier, @ID);
begin try
declare @NEWCONSTITUENTLOOKUPID nvarchar(100)
select
@NEWCONSTITUENTLOOKUPID = LOOKUPID
from dbo.CONSTITUENT
where ID = @PRIMARYRECORDID
-- Pull in spouse fields
declare
@BATCHHASSPOUSE bit = 0,
@SPOUSE_ID uniqueidentifier,
@SPOUSE_TITLECODEID uniqueidentifier,
@SPOUSE_FIRSTNAME nvarchar(50),
@SPOUSE_NICKNAME nvarchar(50),
@SPOUSE_MIDDLENAME nvarchar(50),
@SPOUSE_MAIDENNAME nvarchar(100),
@SPOUSE_KEYNAME nvarchar(100),
@SPOUSE_SUFFIXCODEID uniqueidentifier,
@SPOUSE_GENDERCODE tinyint,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,
@SPOUSE_LOOKUPID nvarchar(100),
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier
--get existing spouse id from batch
select
@BATCHHASSPOUSE = case when SPOUSE_ID is not null or len(SPOUSE_LASTNAME) > 0 then 1 else 0 end
from dbo.BATCHCONSTITUENTUPDATE
where ID = @BATCHCONSTITUENTUPDATEID;
select
@SPOUSE_ID = CONSTITUENT_SPOUSE.ID,
@SPOUSE_TITLECODEID = CONSTITUENT_SPOUSE.TITLECODEID,
@SPOUSE_FIRSTNAME = CONSTITUENT_SPOUSE.FIRSTNAME,
@SPOUSE_NICKNAME = CONSTITUENT_SPOUSE.NICKNAME,
@SPOUSE_MIDDLENAME = CONSTITUENT_SPOUSE.MIDDLENAME,
@SPOUSE_MAIDENNAME = CONSTITUENT_SPOUSE.MAIDENNAME,
@SPOUSE_KEYNAME = CONSTITUENT_SPOUSE.KEYNAME,
@SPOUSE_SUFFIXCODEID = CONSTITUENT_SPOUSE.SUFFIXCODEID,
@SPOUSE_GENDERCODE = CONSTITUENT_SPOUSE.GENDERCODE,
@SPOUSE_BIRTHDATE = CONSTITUENT_SPOUSE.BIRTHDATE,
@SPOUSE_LOOKUPID = CONSTITUENT_SPOUSE.LOOKUPID,
@SPOUSE_RELATIONSHIPTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID = RELATIONSHIP.RECIPROCALTYPECODEID
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT as CONSTITUENT_SPOUSE on
RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT_SPOUSE.ID and
RELATIONSHIP.ISSPOUSE = 1
where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @PRIMARYRECORDID
-- Load spouse recognition defaults
declare
@SOURCETORECIPIENTEXISTS bit = 0,
@SOURCETORECIPIENTMATCHFACTOR decimal(5, 2),
@SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID uniqueidentifier,
@RECIPIENTTOSOURCEEXISTS bit = 0,
@RECIPIENTTOSOURCEMATCHFACTOR decimal(5, 2),
@RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID uniqueidentifier
--Only update spouse information if when they have a spouse on the record
--but no spouse on the batch row
if @SPOUSE_ID is not null and @BATCHHASSPOUSE = 0
begin
declare @RECOGNITIONDEFAULT table
(
SOURCECONSTITUENTID uniqueidentifier,
RECIPIENTCONSTITUENTID uniqueidentifier,
MATCHFACTOR decimal(5, 2),
REVENUERECOGNITIONTYPECODEID uniqueidentifier,
ARESETTHROUGHMEMBERDEFAULTINGALLOTHERMEMBERS bit
)
insert into @RECOGNITIONDEFAULT
(
SOURCECONSTITUENTID,
RECIPIENTCONSTITUENTID,
MATCHFACTOR,
REVENUERECOGNITIONTYPECODEID
)
select
SOURCECONSTITUENTID,
RECIPIENTCONSTITUENTID,
MATCHFACTOR,
REVENUERECOGNITIONTYPECODEID
from dbo.UFN_RECOGNITIONDEFAULTS_GETBETWEENTWOCONSTITUENTS(@PRIMARYRECORDID, @SPOUSE_ID)
select
@SOURCETORECIPIENTEXISTS = 1,
@SOURCETORECIPIENTMATCHFACTOR = MATCHFACTOR,
@SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
from @RECOGNITIONDEFAULT
where
SOURCECONSTITUENTID = @PRIMARYRECORDID and
RECIPIENTCONSTITUENTID = @SPOUSE_ID
select
@RECIPIENTTOSOURCEEXISTS = 1,
@RECIPIENTTOSOURCEMATCHFACTOR = MATCHFACTOR,
@RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
from @RECOGNITIONDEFAULT
where
SOURCECONSTITUENTID = @SPOUSE_ID and
RECIPIENTCONSTITUENTID = @PRIMARYRECORDID
update dbo.BATCHCONSTITUENTUPDATE set
SPOUSE_ID = @SPOUSE_ID,
SPOUSE_TITLECODEID = @SPOUSE_TITLECODEID,
SPOUSE_FIRSTNAME = coalesce(@SPOUSE_FIRSTNAME, ''),
SPOUSE_NICKNAME = coalesce(@SPOUSE_NICKNAME, ''),
SPOUSE_MIDDLENAME = coalesce(@SPOUSE_MIDDLENAME, ''),
SPOUSE_MAIDENNAME = coalesce(@SPOUSE_MAIDENNAME, ''),
SPOUSE_LASTNAME = coalesce(@SPOUSE_KEYNAME, ''),
SPOUSE_SUFFIXCODEID = @SPOUSE_SUFFIXCODEID,
SPOUSE_BIRTHDATE = coalesce(@SPOUSE_BIRTHDATE, '00000000'),
SPOUSE_GENDERCODE = coalesce(@SPOUSE_GENDERCODE, 0),
SPOUSE_LOOKUPID = coalesce(@SPOUSE_LOOKUPID, ''),
SPOUSE_RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID,
SPOUSE_RECIPROCALTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID,
SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @SOURCETORECIPIENTEXISTS,
SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = coalesce(@SOURCETORECIPIENTMATCHFACTOR, 100),
SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = @SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID,
SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @RECIPIENTTOSOURCEEXISTS,
SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = coalesce(@RECIPIENTTOSOURCEMATCHFACTOR, 100),
SPOUSE_PRIMARYRECOGNITIONTYPECODEID = @RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @BATCHCONSTITUENTUPDATEID
end
-- the incoming primary address/phone/email should be made non-primary if the existing record has a primary address/email/phone since the rules are handling this below
if exists (select 'x' from dbo.ADDRESS where CONSTITUENTID = @PRIMARYRECORDID and ISPRIMARY = 1)
update dbo.BATCHCONSTITUENTUPDATEADDRESSES set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
and ISPRIMARY = 1
and ADDRESSID is null;
if exists (select 'x' from dbo.PHONE where CONSTITUENTID = @PRIMARYRECORDID and ISPRIMARY = 1)
update dbo.BATCHCONSTITUENTUPDATEPHONES set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
and ISPRIMARY = 1
and PHONEID is null;
if exists (select 'x' from dbo.EMAILADDRESS where CONSTITUENTID = @PRIMARYRECORDID and ISPRIMARY = 1)
update dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
and ISPRIMARY = 1
and EMAILADDRESSID is null;
declare @EARLIESTDATE date = '0001-01-01';
declare @LATESTDATE date = '9999-12-31';
-- add user-defined constituencies for matched constituent
insert into dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
(
BATCHCONSTITUENTUPDATEID,
CONSTITUENCYCODEID,
DATEFROM,
DATETO,
ORIGINALCONSTITUENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@BATCHCONSTITUENTUPDATEID,
CONSTITUENCYCODEID,
DATEFROM,
DATETO,
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
from
dbo.CONSTITUENCY
where
CONSTITUENTID = @PRIMARYRECORDID
and not exists
(
select 1
from
dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
where
BATCHCONSTITUENTUPDATECONSTITUENCIES.BATCHCONSTITUENTUPDATEID = @ID
and BATCHCONSTITUENTUPDATECONSTITUENCIES.CONSTITUENCYCODEID = CONSTITUENCY.CONSTITUENCYCODEID
and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIES.DATEFROM, @EARLIESTDATE) = coalesce(CONSTITUENCY.DATEFROM, @EARLIESTDATE)
and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIES.DATETO, @LATESTDATE) = coalesce(CONSTITUENCY.DATETO, @LATESTDATE)
);
-- add system constituencies for matched constituent
insert into dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
(
BATCHCONSTITUENTUPDATEID,
CONSTITUENCYCODEID,
DATEFROM,
DATETO,
ORIGINALCONSTITUENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@BATCHCONSTITUENTUPDATEID,
UPDATECONSTITUENCIES.CONSTITUENCYCODEID,
UPDATECONSTITUENCIES.DATEFROM,
UPDATECONSTITUENCIES.DATETO,
UPDATECONSTITUENCIES.ORIGINALCONSTITUENCYID,
UPDATECONSTITUENCIES.ADDEDBYID,
UPDATECONSTITUENCIES.CHANGEDBYID,
UPDATECONSTITUENCIES.DATEADDED,
UPDATECONSTITUENCIES.DATECHANGED
from
dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_FORUPDATEBATCH(@PRIMARYRECORDID) as UPDATECONSTITUENCIES
left join dbo.CONSTITUENCYSYSTEMNAME
on CONSTITUENCYSYSTEMNAME.ID = UPDATECONSTITUENCIES.CONSTITUENCYCODEID
where
CONSTITUENCYSYSTEMNAME.ID is not null
and not exists
(
select 1
from
dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
where
BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.BATCHCONSTITUENTUPDATEID = @ID
and BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.CONSTITUENCYCODEID = UPDATECONSTITUENCIES.CONSTITUENCYCODEID
and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATEFROM, @EARLIESTDATE) = coalesce(UPDATECONSTITUENCIES.DATEFROM, @EARLIESTDATE)
and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATETO, @LATESTDATE) = coalesce(UPDATECONSTITUENCIES.DATETO, @LATESTDATE)
);
-- add solicit codes for matched constituent
insert into [dbo].[BATCHCONSTITUENTUPDATESOLICITCODE]
(
[ID],
[BATCHCONSTITUENTUPDATEID],
[SOLICITCODEID],
[CONSTITUENTSOLICITCODEID],
[STARTDATE],
[ENDDATE],
[COMMENTS],
[SEQUENCE],
[CONSENTPREFERENCECODE],
[SOURCECODEID],
[SOURCEFILEPATH],
[PRIVACYPOLICYFILEPATH],
[SUPPORTINGINFORMATION],
[CONSENTSTATEMENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid() ID,
@BATCHCONSTITUENTUPDATEID [BATCHCONSTITUENTUPDATEID],
[SOLICITCODEID],
[ID] [CONSTITUENTSOLICITCODEID],
[STARTDATE],
[ENDDATE],
[COMMENTS],
[SEQUENCE],
[CONSENTPREFERENCECODE],
[SOURCECODEID],
[SOURCEFILEPATH],
[PRIVACYPOLICYFILEPATH],
[SUPPORTINGINFORMATION],
[CONSENTSTATEMENT],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.CONSTITUENTSOLICITCODE
where [CONSTITUENTID] = @PRIMARYRECORDID;
-- get rules settings from the batch row
declare @NAMECODE tinyint;
declare @SIMILARADDRESSCODE tinyint;
declare @UNSIMILARADDRESSCODE tinyint;
declare @NEWADDRESSPRIMARYCODE tinyint;
declare @BIRTHDATERULECODE tinyint;
declare @DIFFERENTEMAILCODE tinyint;
declare @NEWEMAILPRIMARYCODE tinyint;
declare @DIFFERENTPHONECODE tinyint;
declare @NEWPHONEPRIMARYCODE tinyint;
select
@NAMECODE = NAMECODE,
@SIMILARADDRESSCODE = SIMILARADDRESSCODE,
@UNSIMILARADDRESSCODE = UNSIMILARADDRESSCODE,
@NEWADDRESSPRIMARYCODE = NEWADDRESSPRIMARYCODE,
@BIRTHDATERULECODE = BIRTHDATERULECODE,
@DOMANUALREVIEWFORAUTOMATCH = DOMANUALREVIEWFORAUTOMATCH,
@DIFFERENTEMAILCODE = DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE = NEWEMAILPRIMARYCODE,
@DIFFERENTPHONECODE = DIFFERENTPHONECODE,
@NEWPHONEPRIMARYCODE = NEWPHONEPRIMARYCODE
from dbo.BATCHCONSTITUENTUPDATE
where ID = @BATCHCONSTITUENTUPDATEID;
-- Apply constituent matching rules
if @DOMANUALREVIEWFORAUTOMATCH = 0
begin
exec dbo.USP_BATCHCONSTITUENT_APPLYBUSINESSRULES
@BATCHCONSTITUENTUPDATEID,
@PRIMARYRECORDID,
@CHANGEAGENTID,
@NAMECODE,
1,
@SIMILARADDRESSCODE,
@UNSIMILARADDRESSCODE,
@NEWADDRESSPRIMARYCODE,
@BIRTHDATERULECODE,
@DIFFERENTPHONECODE,
@NEWPHONEPRIMARYCODE,
@DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE;
end
update dbo.BATCHCONSTITUENTUPDATE set
PRIMARYRECORDID = @PRIMARYRECORDID,
LOOKUP_ID = coalesce(@NEWCONSTITUENTLOOKUPID, ''),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @BATCHCONSTITUENTUPDATEID;
-- address the auto end date issues for consent based solicit codes
declare @SOLICITCODES xml = dbo.UFN_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_TOITEMLISTXML(@BATCHCONSTITUENTUPDATEID);
exec USP_CONSTITUENTUPDATEBATCH_ADJUSTSOLICITCODEDATERANGES @PRIMARYRECORDID, @SOLICITCODES, @CHANGEAGENTID;
-- Defaulting DATEFROM/DATETO if possible for constituency
declare @ALLCONSTITUENCY xml = dbo.UFN_CONSTITUENTUPDATEBATCH_GETCONSTITUENCIES_TOITEMLISTXML(@BATCHCONSTITUENTUPDATEID);
if @ALLCONSTITUENCY is not null
begin
exec USP_CONSTITUENTUPDATEBATCH_ADJUSTCONSTITUENCYDATERANGE @BATCHCONSTITUENTUPDATEID, @PRIMARYRECORDID, @ALLCONSTITUENCY, @CHANGEAGENTID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;