USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHDUPLICATEAUTOMATCH_WITHRULES
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @DOMANUALREVIEWFORAUTOMATCH | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHDUPLICATEAUTOMATCH_WITHRULES
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier,
@DOMANUALREVIEWFORAUTOMATCH bit
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
declare
@ORIGINALCONSTITUENTID uniqueidentifier,
@DATE datetime,
@AMOUNT money,
@GIVESANONYMOUSLY bit,
@GIVENANONYMOUSLY bit,
@BASECURRENCYID uniqueidentifier,
@BASEEXCHANGERATEID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@BASECURRENCYDECIMALDIGITS tinyint,
@BASECURRENCYROUNDINGTYPECODE tinyint,
@EXCHANGERATE decimal(20,8),
@CURRENCYSETID uniqueidentifier,
@PDACCOUNTSYSTEMID uniqueidentifier,
@FINDERNUMBER bigint = 0;
select
@GIVESANONYMOUSLY = GIVESANONYMOUSLY
from dbo.CONSTITUENT
where ID = @CONSTITUENTID
select
@ORIGINALCONSTITUENTID = CONSTITUENTID,
@DATE = date,
@AMOUNT = AMOUNT,
@GIVENANONYMOUSLY = case when (@GIVESANONYMOUSLY = 1 or @GIVENANONYMOUSLY = 1) then 1 else @GIVESANONYMOUSLY end,
@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
@BASECURRENCYID = BASECURRENCYID,
@BASEEXCHANGERATEID = BASEEXCHANGERATEID,
@EXCHANGERATE = EXCHANGERATE,
@PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID,
@FINDERNUMBER = [FINDERNUMBER]
from dbo.BATCHREVENUE
where ID = @ID
select @CURRENCYSETID = CURRENCYSETID
from dbo.PDACCOUNTSYSTEM
where ID = @PDACCOUNTSYSTEMID
select
@BASECURRENCYID = CURRENCYSET.BASECURRENCYID
from
dbo.CURRENCYSET
where
CURRENCYSET.ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(null))
select @BASECURRENCYDECIMALDIGITS = DECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE = ROUNDINGTYPECODE
from dbo.CURRENCY where ID = @BASECURRENCYID;
--SA - bug fix 360284. When Automatching, the recognition credits need to be cleared from the BRR table and then redefaulted to the existing constituents'
--This code is very similar to that found in RevenueBatchDataFrorm.Edit.8.xml. Should that code need to be changed, consider updating this code here as well.
--clear the recognitions for the old constituent
delete from
dbo.BATCHREVENUERECOGNITION
where
BATCHREVENUEID = @ID
-- Create default recognitions
declare @SCAMOUNT money;
declare @REVENUESTREAMS xml;
set @REVENUESTREAMS = dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN_TOITEMLISTXML(@ID)
if @AMOUNT > 0
begin
insert into dbo.BATCHREVENUERECOGNITION (
BATCHREVENUEID,
CONSTITUENTID,
REVENUERECOGNITIONTYPECODEID,
AMOUNT,
EFFECTIVEDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select @ID,
RECOGNITIONS.CONSTITUENTID,
RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
RECOGNITIONS.AMOUNT,
@date,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS(@GIVESANONYMOUSLY, @CONSTITUENTID, @AMOUNT, @DATE, null) as RECOGNITIONS;
end
update dbo.BATCHREVENUEAPPLICATIONPLEDGE set
CONSTITUENTID = @CONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.BATCHREVENUEAPPLICATIONPLEDGE
inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATIONPLEDGE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONPLEDGEID
where
BATCHREVENUEAPPLICATION.BATCHREVENUEID = @ID and
BATCHREVENUEAPPLICATIONPLEDGE.CONSTITUENTID = @ORIGINALCONSTITUENTID
update dbo.BATCHREVENUE set
CONSTITUENTID = @CONSTITUENTID,
GIVENANONYMOUSLY =
case
when
(@GIVESANONYMOUSLY = 1 or BATCHREVENUE.GIVENANONYMOUSLY = 1) then
1
else
@GIVESANONYMOUSLY
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
--Update BATCHREVENUECONSTITUENTAPPEAL
update
dbo.[BATCHREVENUECONSTITUENTAPPEAL]
set
[CONSTITUENTID] = @CONSTITUENTID
where
[CONSTITUENTID] = @ORIGINALCONSTITUENTID
and
[FINDERNUMBER] = @FINDERNUMBER;
--update the constituent accounts
exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
@ORIGINALCONSTITUENTID,
@CONSTITUENTID,
@CHANGEAGENTID
-- apply the constituent update rules --
-- 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,
@DIFFERENTEMAILCODE = DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE = NEWEMAILPRIMARYCODE,
@DIFFERENTPHONECODE = DIFFERENTPHONECODE,
@NEWPHONEPRIMARYCODE = NEWPHONEPRIMARYCODE
from dbo.BATCHREVENUE where ID = @ID
select
@DOMANUALREVIEWFORAUTOMATCH = DOMANUALREVIEWFORAUTOMATCH
from dbo.BATCHCONSTITUENTUPDATE where ID = @ID
-- Pull in spouse fields
declare
@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_GENDERCODEID uniqueidentifier,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,
@SPOUSE_LOOKUPID nvarchar(100),
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier
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_GENDERCODEID = CONSTITUENT_SPOUSE.GENDERCODEID,
@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 = @CONSTITUENTID
-- Load spouse recognition defaults
declare
@SOURCETORECIPIENTEXISTS bit = 0,
@SOURCETORECIPIENTMATCHFACTOR decimal(5, 2),
@SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID uniqueidentifier,
@RECIPIENTTOSOURCEEXISTS bit = 0,
@RECIPIENTTOSOURCEMATCHFACTOR decimal(5, 2),
@RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID uniqueidentifier
if @SPOUSE_ID is not null
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(@CONSTITUENTID, @SPOUSE_ID)
select
@SOURCETORECIPIENTEXISTS = 1,
@SOURCETORECIPIENTMATCHFACTOR = MATCHFACTOR,
@SOURCETORECIPIENTREVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
from @RECOGNITIONDEFAULT
where
SOURCECONSTITUENTID = @CONSTITUENTID and
RECIPIENTCONSTITUENTID = @SPOUSE_ID
select
@RECIPIENTTOSOURCEEXISTS = 1,
@RECIPIENTTOSOURCEMATCHFACTOR = MATCHFACTOR,
@RECIPIENTTOSOURCEREVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
from @RECOGNITIONDEFAULT
where
SOURCECONSTITUENTID = @SPOUSE_ID and
RECIPIENTCONSTITUENTID = @CONSTITUENTID
end
-- Apply constituent matching rules
if @DOMANUALREVIEWFORAUTOMATCH = 0
begin
-- 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 = @CONSTITUENTID and ISPRIMARY = 1)
update dbo.BATCHCONSTITUENTUPDATEADDRESSES set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where BATCHCONSTITUENTUPDATEID = @ID
and ISPRIMARY = 1;
if exists (select 'x' from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1)
update dbo.BATCHCONSTITUENTUPDATEPHONES set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where BATCHCONSTITUENTUPDATEID = @ID
and ISPRIMARY = 1;
if exists (select 'x' from dbo.EMAILADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1)
update dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where BATCHCONSTITUENTUPDATEID = @ID
and ISPRIMARY = 1;
exec dbo.USP_BATCHCONSTITUENT_APPLYBUSINESSRULES
@ID,
@CONSTITUENTID,
@CHANGEAGENTID,
@NAMECODE,
0,
@SIMILARADDRESSCODE,
@UNSIMILARADDRESSCODE,
@NEWADDRESSPRIMARYCODE,
@BIRTHDATERULECODE,
@DIFFERENTPHONECODE,
@NEWPHONEPRIMARYCODE,
@DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE;
end
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_GENDERCODEID = @SPOUSE_GENDERCODEID,
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 = @ID
-- add solicit codes for matched constituent
insert into [dbo].[BATCHREVENUECONSTITUENTSOLICITCODE]
(
[ID],
[BATCHREVENUEID],
[SOLICITCODEID],
[CONSTITUENTSOLICITCODEID],
[STARTDATE],
[ENDDATE],
[COMMENTS],
[SEQUENCE],
[CONSENTPREFERENCECODE],
[SOURCECODEID],
[SOURCEFILEPATH],
[PRIVACYPOLICYFILEPATH],
[SUPPORTINGINFORMATION],
[CONSENTSTATEMENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid() ID,
@ID [BATCHREVENUEID],
[SOLICITCODEID],
[ID] [CONSTITUENTSOLICITCODEID],
[STARTDATE],
[ENDDATE],
[COMMENTS],
[SEQUENCE],
[CONSENTPREFERENCECODE],
[SOURCECODEID],
[SOURCEFILEPATH],
[PRIVACYPOLICYFILEPATH],
[SUPPORTINGINFORMATION],
[CONSENTSTATEMENT],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.CONSTITUENTSOLICITCODE
where [CONSTITUENTID] = @CONSTITUENTID;
-- address the auto end date issues for consent based solicit codes
declare @SOLICITCODES xml = dbo.UFN_BATCHREVENUE_GETCONSTITUENTSOLICITCODES_TOITEMLISTXML(@ID);
exec dbo.USP_BATCHREVENUE_ADJUSTSOLICITCODEDATERANGES @CONSTITUENTID, @SOLICITCODES, @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;