USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPDUESBATCHDUPLICATECONSTITUENTAUTOMATCHWITHRULES
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @BILLTOCONSTITUENTID | uniqueidentifier | IN | |
| @DOMANUALREVIEWFORAUTOMATCH | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPDUESBATCHDUPLICATECONSTITUENTAUTOMATCHWITHRULES
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@BILLTOCONSTITUENTID uniqueidentifier,
@DOMANUALREVIEWFORAUTOMATCH bit
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime = getdate();
declare @OLDBILLTOCONSTITUENTID uniqueidentifier;
select @OLDBILLTOCONSTITUENTID = BILLTOCONSTITUENTID
from dbo.BATCHMEMBERSHIPDUES
where ID = @ID;
begin try
update dbo.BATCHMEMBERSHIPDUES set
BILLTOCONSTITUENTID = @BILLTOCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
update dbo.BATCHMEMBERSHIPDUESMEMBER set
CONSTITUENTID = @BILLTOCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
BATCHMEMBERSHIPDUESID = @ID and
CONSTITUENTID = @OLDBILLTOCONSTITUENTID;
update dbo.BATCHMEMBERSHIPDUESMEMBERSHIPCARD set
CONSTITUENTID = @BILLTOCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
BATCHMEMBERSHIPDUESID = @ID and
CONSTITUENTID = @OLDBILLTOCONSTITUENTID;
-- Since constituent has changed, recognitions should be recalculated
declare @MEMBERSHIPPROGRAMID uniqueidentifier;
declare @MEMBERSHIPLEVELID uniqueidentifier;
declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
declare @MEMBERSHIPTRANSACTIONTYPECODE tinyint;
declare @REVENUETYPECODE tinyint;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @EXCHANGERATE decimal(20,8);
declare @DISCOUNTTYPE uniqueidentifier;
declare @PROMOTIONCODE nvarchar(50);
declare @GIVENANONYMOUSLY bit;
select
@MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID,
@MEMBERSHIPLEVELID = MEMBERSHIPLEVELID,
@MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERMID,
@MEMBERSHIPTRANSACTIONTYPECODE = MEMBERSHIPTRANSACTIONTYPECODE,
@REVENUETYPECODE = REVENUETYPECODE,
@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
@BASECURRENCYID = BASECURRENCYID,
@EXCHANGERATE = EXCHANGERATE,
@DISCOUNTTYPE = MEMBERSHIPPROMOID,
@PROMOTIONCODE = PROMOTIONCODE,
@GIVENANONYMOUSLY = DONATIONGIVENANONYMOUSLY
from dbo.BATCHMEMBERSHIPDUES
where ID = @ID;
-- Adding or renewing, and paying in full
if ((@MEMBERSHIPTRANSACTIONTYPECODE = 0 or @MEMBERSHIPTRANSACTIONTYPECODE = 1 or @MEMBERSHIPTRANSACTIONTYPECODE = 3) and @REVENUETYPECODE = 0)
begin
declare @DEDUCTIBILITYCODE tinyint;
declare @DUESTREATEDASCONTRIBUTION bit;
select
@DEDUCTIBILITYCODE = DEDUCTIBILITYCODE,
@DUESTREATEDASCONTRIBUTION = DUESTREATEDASCONTRIBUTION
from dbo.MEMBERSHIPPROGRAM
where ID = @MEMBERSHIPPROGRAMID;
declare @LEVELRECEIPTAMOUNT money;
declare @OBTAINLEVELCODE tinyint;
declare @HASCONTRIBUTIONDESIGNATIONS bit;
select
@LEVELRECEIPTAMOUNT = RECEIPTAMOUNT,
@OBTAINLEVELCODE = OBTAINLEVELCODE,
@HASCONTRIBUTIONDESIGNATIONS =
case
when exists(select ID from dbo.MEMBERSHIPLEVELDESIGNATION where MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID) then 1
else 0
end
from dbo.MEMBERSHIPLEVEL
where ID = @MEMBERSHIPLEVELID;
if @OBTAINLEVELCODE = 0 and @DUESTREATEDASCONTRIBUTION = 1 and @HASCONTRIBUTIONDESIGNATIONS = 1
begin
declare @DATE date = getdate();
declare @MEMBERSHIPAMOUNT money;
select
@MEMBERSHIPAMOUNT = AMOUNT
from dbo.MEMBERSHIPLEVELTERM
where ID = @MEMBERSHIPLEVELTERMID
and LEVELID = @MEMBERSHIPLEVELID;
declare @PROMOTIONAMOUNT money = 0;
if @DISCOUNTTYPE is not null or len(coalesce(@PROMOTIONCODE, '')) > 0
begin
declare @PROMOTIONTYPECODE tinyint = 0;
declare @DISCOUNTCALCULATIONTYPECODE tinyint = 0;
declare @DISCOUNTAMOUNT money = 0;
declare @DISCOUNTPERCENT decimal = 0;
declare @APPLIEDDISCOUNTID uniqueidentifier;
if @DISCOUNTTYPE is not null
begin
select
@APPLIEDDISCOUNTID = MEMBERSHIPPROMO.ID,
@PROMOTIONTYPECODE = MEMBERSHIPPROMO.PROMOTIONTYPECODE,
@DISCOUNTCALCULATIONTYPECODE = MEMBERSHIPPROMO.DISCOUNTCALCULATIONTYPECODE,
@DISCOUNTAMOUNT = MEMBERSHIPPROMO.AMOUNT,
@DISCOUNTPERCENT = MEMBERSHIPPROMO.[PERCENT]
from dbo.MEMBERSHIPPROMO
inner join dbo.MEMBERSHIPPROMOAVAILABILITY
on MEMBERSHIPPROMO.ID = MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROMOID
where MEMBERSHIPPROMO.ID = @DISCOUNTTYPE
and MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID;
end
else
begin
select
@APPLIEDDISCOUNTID = MEMBERSHIPPROMO.ID,
@PROMOTIONTYPECODE = PROMOTIONTYPECODE,
@DISCOUNTCALCULATIONTYPECODE = DISCOUNTCALCULATIONTYPECODE,
@DISCOUNTAMOUNT = AMOUNT,
@DISCOUNTPERCENT = [PERCENT]
from dbo.MEMBERSHIPPROMO
inner join dbo.MEMBERSHIPPROMOCODE
on MEMBERSHIPPROMO.ID = MEMBERSHIPPROMOCODE.MEMBERSHIPPROMOID
where MEMBERSHIPPROMOCODE.PROMOTIONALCODE = @PROMOTIONCODE
and (MEMBERSHIPPROMOCODE.VALIDFROM is null or MEMBERSHIPPROMOCODE.VALIDFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE))
and (MEMBERSHIPPROMOCODE.VALIDTO is null or MEMBERSHIPPROMOCODE.VALIDTO >= dbo.UFN_DATE_GETLATESTTIME(@DATE));
end
if @APPLIEDDISCOUNTID is null
set @PROMOTIONAMOUNT = 0;
else if @PROMOTIONTYPECODE = 0
begin
-- Discount
if @DISCOUNTCALCULATIONTYPECODE = 0
set @PROMOTIONAMOUNT = @DISCOUNTAMOUNT;
else
set @PROMOTIONAMOUNT = @MEMBERSHIPAMOUNT * (@DISCOUNTPERCENT / 100);
end
else
set @PROMOTIONAMOUNT = 0;
end
declare @BASETAXDEDUCTIBLEAMOUNT money;
set @BASETAXDEDUCTIBLEAMOUNT =
case @DEDUCTIBILITYCODE
when 0 then @MEMBERSHIPAMOUNT - @PROMOTIONAMOUNT
when 1 then @LEVELRECEIPTAMOUNT - @PROMOTIONAMOUNT
when 2 then 0
end;
if @BASETAXDEDUCTIBLEAMOUNT < 0
set @BASETAXDEDUCTIBLEAMOUNT = 0;
if @TRANSACTIONCURRENCYID <> @BASECURRENCYID
set @BASETAXDEDUCTIBLEAMOUNT = @BASETAXDEDUCTIBLEAMOUNT * @EXCHANGERATE;
declare @DESIGNATIONSTABLE table(DESIGNATIONID uniqueidentifier, AMOUNT money);
declare @MEMBERSHIPRECOGNITION xml;
insert into @DESIGNATIONSTABLE(DESIGNATIONID, AMOUNT)
select
MEMBERSHIPLEVELDESIGNATION.DESIGNATIONID,
case MEMBERSHIPLEVELDESIGNATION.[PERCENT]
when 0 then 0
else (MEMBERSHIPLEVELDESIGNATION.[PERCENT] / 100) * @BASETAXDEDUCTIBLEAMOUNT
end as AMOUNT
from dbo.MEMBERSHIPLEVELDESIGNATION
where MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID;
set @MEMBERSHIPRECOGNITION = (
select
RECOGNITIONS.CONSTITUENTID,
RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
DESIGNATIONS.DESIGNATIONID,
@DATE as EFFECTIVEDATE,
RECOGNITIONS.AMOUNT,
@TRANSACTIONCURRENCYID as APPLICATIONCURRENCYID
from @DESIGNATIONSTABLE DESIGNATIONS
inner join dbo.DESIGNATION
on DESIGNATIONS.DESIGNATIONID = DESIGNATION.ID
outer apply dbo.UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_2(@GIVENANONYMOUSLY, @BILLTOCONSTITUENTID, DESIGNATIONS.AMOUNT, @DATE, null, default, default) as RECOGNITIONS
left outer join dbo.REVENUERECOGNITIONTYPECODE
on RECOGNITIONS.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
for xml raw('ITEM'),type,elements,root('MEMBERSHIPRECOGNITION'),BINARY BASE64
);
delete from dbo.BATCHMEMBERSHIPDUESMEMBERSHIPRECOGNITION
where BATCHMEMBERSHIPDUESID = @ID;
if @MEMBERSHIPRECOGNITION is not null
exec dbo.USP_MEMBERSHIPDUESBATCH_GETMEMBERSHIPRECOGNITIONS_ADDFROMXML @ID, @MEMBERSHIPRECOGNITION, null, null;
end
end
else
begin
update dbo.BATCHMEMBERSHIPDUESMEMBERSHIPRECOGNITION set
CONSTITUENTID = @BILLTOCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
BATCHMEMBERSHIPDUESID = @ID and
CONSTITUENTID = @OLDBILLTOCONSTITUENTID;
end
update dbo.BATCHMEMBERSHIPDUESDONATIONRECOGNITION set
CONSTITUENTID = @BILLTOCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
BATCHMEMBERSHIPDUESID = @ID and
CONSTITUENTID = @OLDBILLTOCONSTITUENTID;
update dbo.BATCHMEMBERSHIPDUESDONATIONSOLICITOR set
CONSTITUENTID = @BILLTOCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
BATCHMEMBERSHIPDUESID = @ID and
CONSTITUENTID = @OLDBILLTOCONSTITUENTID;
-- add solicit codes for matched constituent
insert into [dbo].[BATCHMEMBERSHIPDUESSOLICITCODE]
(
[ID],
[BATCHMEMBERSHIPDUESID],
[SOLICITCODEID],
[CONSTITUENTSOLICITCODEID],
[STARTDATE],
[ENDDATE],
[COMMENTS],
[SEQUENCE],
[CONSENTPREFERENCECODE],
[SOURCECODEID],
[SOURCEFILEPATH],
[PRIVACYPOLICYFILEPATH],
[SUPPORTINGINFORMATION],
[CONSENTSTATEMENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid() ID,
@ID [BATCHMEMBERSHIPDUESID],
[SOLICITCODEID],
[ID] [CONSTITUENTSOLICITCODEID],
[STARTDATE],
[ENDDATE],
[COMMENTS],
[SEQUENCE],
[CONSENTPREFERENCECODE],
[SOURCECODEID],
[SOURCEFILEPATH],
[PRIVACYPOLICYFILEPATH],
[SUPPORTINGINFORMATION],
[CONSENTSTATEMENT],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.CONSTITUENTSOLICITCODE
where [CONSTITUENTID] = @BILLTOCONSTITUENTID;
-- address the auto end date issues for consent based solicit codes
declare @SOLICITCODES xml = dbo.UFN_MEMBERSHIPDUESBATCH_GETSOLICITCODES_TOITEMLISTXML(@ID);
exec dbo.USP_MEMBERSHIPDUESBATCH_ADJUSTSOLICITCODEDATERANGES @BILLTOCONSTITUENTID, @SOLICITCODES, @CHANGEAGENTID;
declare @BBNCTRANID int = 0;
select
@BBNCTRANID = BATCHMEMBERSHIPDUESBBNCINFO.BBNCTRANID,
@MEMBERSHIPPROGRAMID = BATCHMEMBERSHIPDUES.MEMBERSHIPPROGRAMID
from dbo.BATCHMEMBERSHIPDUESBBNCINFO
inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.ID = BATCHMEMBERSHIPDUESBBNCINFO.BATCHMEMBERSHIPDUESID
where BATCHMEMBERSHIPDUESBBNCINFO.BATCHMEMBERSHIPDUESID = @ID;
if @BBNCTRANID > 0
begin
declare @EXISTINGMEMBERSHIPID uniqueidentifier;
select top 1
@EXISTINGMEMBERSHIPID = MEMBERSHIP.ID
from dbo.MEMBER
inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
where
MEMBER.CONSTITUENTID = @BILLTOCONSTITUENTID and
MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
MEMBERSHIP.STATUSCODE <> 1; --Cancelled
if @EXISTINGMEMBERSHIPID is not null
begin
declare @NEWMEMBERSHIPLEVELID uniqueidentifier;
declare @NEWMEMBERSHIPLEVELTERMID uniqueidentifier;
declare @TRANSACTIONDATE date;
declare @MEMBERSHIPEXPIRESONDATE date;
select top 1
@NEWMEMBERSHIPLEVELID = BATCHMEMBERSHIPDUES.MEMBERSHIPLEVELID,
@NEWMEMBERSHIPLEVELTERMID = BATCHMEMBERSHIPDUES.MEMBERSHIPLEVELTERMID,
@TRANSACTIONDATE = BATCHMEMBERSHIPDUES.DATE
from dbo.BATCHMEMBERSHIPDUES
where
BATCHMEMBERSHIPDUES.ID = @ID;
declare @ACTIONCODE tinyint = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@EXISTINGMEMBERSHIPID, @NEWMEMBERSHIPLEVELID, @TRANSACTIONDATE);
if @ACTIONCODE = 0 or @ACTIONCODE = 5 --join/rejoin
begin
set @MEMBERSHIPEXPIRESONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@NEWMEMBERSHIPLEVELID, @NEWMEMBERSHIPLEVELTERMID, @TRANSACTIONDATE);
end
else
begin
declare @CURRENTEXPIRATIONDATE datetime;
select
@CURRENTEXPIRATIONDATE = MEMBERSHIP.EXPIRATIONDATE
from dbo.MEMBERSHIP
where ID = @EXISTINGMEMBERSHIPID;
set @MEMBERSHIPEXPIRESONDATE = dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION_2(@CURRENTEXPIRATIONDATE, @NEWMEMBERSHIPLEVELTERMID);
end
update dbo.BATCHMEMBERSHIPDUES set
MEMBERSHIPTRANSACTIONTYPECODE = 1, --'Renew code - MDB will treat this as rejoin/renew appropriately.
MEMBERSHIPEXPIRESONDATE = @MEMBERSHIPEXPIRESONDATE,
EXISTINGMEMBERSHIPID = @EXISTINGMEMBERSHIPID
where ID = @ID;
end
end
select
@DOMANUALREVIEWFORAUTOMATCH = DOMANUALREVIEWFORAUTOMATCH
from dbo.BATCHCONSTITUENTUPDATE where ID = @ID;
-- 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.BATCHMEMBERSHIPDUES where ID = @ID;
-- Apply constituent matching rules
if @DOMANUALREVIEWFORAUTOMATCH = 0
exec dbo.USP_BATCHCONSTITUENT_APPLYBUSINESSRULES
@ID, @BILLTOCONSTITUENTID, @CHANGEAGENTID,
@NAMECODE, 0, @SIMILARADDRESSCODE,
@UNSIMILARADDRESSCODE, @NEWADDRESSPRIMARYCODE, @BIRTHDATERULECODE,
@DIFFERENTPHONECODE, @NEWPHONEPRIMARYCODE, @DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;