USP_UPDATE_BENEFITS
Executes the "Registrant Benefit Extension Update" record operation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the ID of the record being updated. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the update. |
Definition
Copy
CREATE procedure dbo.USP_UPDATE_BENEFITS
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null
)
as
set nocount on
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
DECLARE @EVENTID uniqueidentifier
SELECT @EVENTID= EVENTID FROM REGISTRANT WHERE EVENTID= @ID
----------------------------------------
IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_GETREGISTRANT_EVENT_INFO') )
DROP TABLE #TEMP_GETREGISTRANT_EVENT_INFO
CREATE TABLE #TEMP_GETREGISTRANT_EVENT_INFO (REGISTRANTID uniqueidentifier NOT NULL, [Total amount raised] money,PROGRESSMEMBERECRUITMENTGOAL money,
PROGRESSDONORRETENTIONGOAL decimal(5, 2), PROGRESSCOMMUNICATIONGOAL int )
----------------------------------------
INSERT INTO #TEMP_GETREGISTRANT_EVENT_INFO
------
SELECT ID
, dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(REGISTRANT.ID, REGISTRANT.EVENTID) AS [Total amount raised]
, ((SELECT COUNT(fc.TYPEGUID) from FAFCOMMUNICATIONSLOG fl join FAFEVENTCOMMUNICATIONCHANNEL fc on fl.EMAILJOBID = fc.EMAILJOBID
WHERE fl.CLIENTUSERSID IN (SELECT dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT (REGISTRANT.CONSTITUENTID)) AND EVENTID= REGISTRANT.EVENTID)) AS PROGRESSMEMBERECRUITMENTGOAL
, (SELECT DONORRETAINEDNUMBER FROM V_QUERY_REGISTRANT_FUNDRAISINGTOTAL WHERE ID= REGISTRANT.ID) AS PROGRESSDONORRETENTIONGOAL
, (SELECT COUNT(ID) FROM FAFCOMMUNICATIONSLOG WHERE CONSTITUENTID= REGISTRANT.CONSTITUENTID AND EVENTID= REGISTRANT.EVENTID) AS PROGRESSCOMMUNICATIONGOAL
FROM REGISTRANT
WHERE EVENTID= @EVENTID
----------------------------------------------------------------------
IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_GET_FAFINCENTIVELEVEL_BENEFITS') )
DROP TABLE #TEMP_GET_FAFINCENTIVELEVEL_BENEFITS
CREATE TABLE #TEMP_GET_FAFINCENTIVELEVEL_BENEFITS ( prim_id int NOT NULL IDENTITY(1,1) PRIMARY KEY, FAFINCENTIVELEVELID uniqueidentifier, TYPECODE tinyint,
MAXIMUMAMOUNT MONEY, BENEFITID uniqueidentifier, INCENTIVENAME varchar(100), BENEFITNAME varchar(100)
, MAXIMUMNUMBER integer, MAXIMUMRETENTION decimal(5, 2) )
----------------------------------------
INSERT INTO #TEMP_GET_FAFINCENTIVELEVEL_BENEFITS
------
SELECT FL.ID, FL.TYPECODE, FL.MAXIMUMAMOUNT, FLB.BENEFITID, FL.NAME AS INCENTIVENAME, B.NAME AS BENEFITNAME, FL.MAXIMUMNUMBER , FL.MAXIMUMRETENTION
FROM FAFINCENTIVELEVEL FL JOIN FAFINCENTIVELEVELBENEFIT FLB ON FL.ID = FLB.FAFINCENTIVELEVELID JOIN BENEFIT B ON FLB.BENEFITID= B.ID WHERE FL.EVENTID= @EVENTID
----------------------------------------------------------------------
DECLARE @i int, @count int, @FAFINCENTIVELEVELID uniqueidentifier, @TYPECODE tinyint, @MAXIMUMAMOUNT MONEY, @BENEFITID uniqueidentifier
, @INCENTIVENAME varchar(100), @BENEFITNAME varchar(100), @MAXIMUMNUMBER integer, @MAXIMUMRETENTION decimal (5,2)
SELECT @i = 1, @count = count(*) FROM #TEMP_GET_FAFINCENTIVELEVEL_BENEFITS
----------------------------------------------------------------------
--1...Allow participant to only receive benefits from each level
IF EXISTS (SELECT * FROM EVENTATTRIBUTES WHERE EVENTID= @EVENTID AND ALLOWPARTICIPANTONLYRECEIVEBENEFITLEVELCODE= 0)
BEGIN
IF EXISTS (SELECT * FROM FAFINCENTIVELEVEL FL JOIN FAFINCENTIVELEVELBENEFIT FLB ON FL.ID = FLB.FAFINCENTIVELEVELID AND FL.EVENTID= @EVENTID)
BEGIN
DELETE FROM REGISTRANTBENEFITEXTENSION WHERE REGISTRANTID IN (SELECT REGISTRANTID FROM #TEMP_GETREGISTRANT_EVENT_INFO) AND BENEFITTYPECODE <> 0
---------------------------
WHILE (@i <= @count)
BEGIN
SELECT @FAFINCENTIVELEVELID= FAFINCENTIVELEVELID, @TYPECODE= TYPECODE, @MAXIMUMAMOUNT= MAXIMUMAMOUNT, @BENEFITID = BENEFITID,
@INCENTIVENAME= INCENTIVENAME, @BENEFITNAME= BENEFITNAME, @MAXIMUMNUMBER= MAXIMUMNUMBER, @MAXIMUMRETENTION= (MAXIMUMRETENTION * 100)
FROM #TEMP_GET_FAFINCENTIVELEVEL_BENEFITS WHERE prim_id = @i
IF @MAXIMUMAMOUNT >0
INSERT INTO REGISTRANTBENEFITEXTENSION
(ID, REGISTRANTID, BENEFITID, BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
SELECT newid(), REGISTRANTID, @BENEFITID, @TYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE [Total amount raised] >= @MAXIMUMAMOUNT
IF @MAXIMUMNUMBER > 0 AND @TYPECODE =2
INSERT INTO REGISTRANTBENEFITEXTENSION
(ID, REGISTRANTID, BENEFITID, BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
SELECT newid(), REGISTRANTID, @BENEFITID, @TYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE PROGRESSMEMBERECRUITMENTGOAL >= @MAXIMUMNUMBER
IF @MAXIMUMRETENTION > 0
INSERT INTO REGISTRANTBENEFITEXTENSION
(ID, REGISTRANTID, BENEFITID, BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
SELECT newid(), REGISTRANTID, @BENEFITID, @TYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE PROGRESSDONORRETENTIONGOAL >= @MAXIMUMRETENTION
IF @MAXIMUMNUMBER > 0 AND @TYPECODE =4
INSERT INTO REGISTRANTBENEFITEXTENSION
(ID, REGISTRANTID, BENEFITID, BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
SELECT newid(), REGISTRANTID, @BENEFITID, @TYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE PROGRESSCOMMUNICATIONGOAL >= @MAXIMUMNUMBER
SET @i = @i + 1
END
END
END
ELSE
BEGIN
-----------------------------------------------------------------------------------------------------
--2...Allow participant to only receive benefits from highest level
IF EXISTS (SELECT * FROM EVENTATTRIBUTES WHERE EVENTID= @EVENTID AND ALLOWPARTICIPANTONLYRECEIVEBENEFITLEVELCODE= 1)
BEGIN
IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_REGISTRANTBENEFITEXTENSION') )
DROP TABLE #TEMP_REGISTRANTBENEFITEXTENSION
CREATE TABLE #TEMP_REGISTRANTBENEFITEXTENSION (prim_id int NOT NULL IDENTITY(1,1) PRIMARY KEY, REGISTRANTID uniqueidentifier, TYPECODE tinyint,
MAXIMUMAMOUNT MONEY, BENEFITID uniqueidentifier, INCENTIVENAME varchar(100), BENEFITNAME varchar(100)
, MAXIMUMNUMBER integer, MAXIMUMRETENTION decimal(5, 2) )
IF EXISTS (SELECT * FROM FAFINCENTIVELEVEL FL JOIN FAFINCENTIVELEVELBENEFIT FLB ON FL.ID = FLB.FAFINCENTIVELEVELID AND FL.EVENTID= @EVENTID)
BEGIN
DELETE FROM REGISTRANTBENEFITEXTENSION WHERE REGISTRANTID IN (SELECT REGISTRANTID FROM #TEMP_GETREGISTRANT_EVENT_INFO) AND BENEFITTYPECODE <> 0
---------------------------
WHILE (@i <= @count)
BEGIN
SELECT @FAFINCENTIVELEVELID= FAFINCENTIVELEVELID, @TYPECODE= TYPECODE, @MAXIMUMAMOUNT= MAXIMUMAMOUNT, @BENEFITID = BENEFITID,
@INCENTIVENAME= INCENTIVENAME, @BENEFITNAME= BENEFITNAME, @MAXIMUMNUMBER= MAXIMUMNUMBER, @MAXIMUMRETENTION= (MAXIMUMRETENTION * 100)
FROM #TEMP_GET_FAFINCENTIVELEVEL_BENEFITS WHERE prim_id = @i
IF @MAXIMUMAMOUNT >0
INSERT INTO #TEMP_REGISTRANTBENEFITEXTENSION
(REGISTRANTID, BENEFITID, TYPECODE, MAXIMUMAMOUNT, MAXIMUMNUMBER, MAXIMUMRETENTION)
SELECT REGISTRANTID, @BENEFITID, @TYPECODE, @MAXIMUMAMOUNT, @MAXIMUMNUMBER, @MAXIMUMRETENTION
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE [Total amount raised] >= @MAXIMUMAMOUNT
IF @MAXIMUMNUMBER > 0 AND @TYPECODE =2
INSERT INTO #TEMP_REGISTRANTBENEFITEXTENSION
(REGISTRANTID, BENEFITID, TYPECODE, MAXIMUMAMOUNT, MAXIMUMNUMBER, MAXIMUMRETENTION)
SELECT REGISTRANTID, @BENEFITID, @TYPECODE, @MAXIMUMAMOUNT, @MAXIMUMNUMBER, @MAXIMUMRETENTION
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE PROGRESSMEMBERECRUITMENTGOAL >= @MAXIMUMNUMBER
IF @MAXIMUMRETENTION > 0
INSERT INTO #TEMP_REGISTRANTBENEFITEXTENSION
(REGISTRANTID, BENEFITID, TYPECODE, MAXIMUMAMOUNT, MAXIMUMNUMBER, MAXIMUMRETENTION)
SELECT REGISTRANTID, @BENEFITID, @TYPECODE, @MAXIMUMAMOUNT, @MAXIMUMNUMBER, @MAXIMUMRETENTION
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE PROGRESSDONORRETENTIONGOAL >= @MAXIMUMRETENTION
IF @MAXIMUMNUMBER > 0 AND @TYPECODE = 4
INSERT INTO #TEMP_REGISTRANTBENEFITEXTENSION
(REGISTRANTID, BENEFITID, TYPECODE, MAXIMUMAMOUNT, MAXIMUMNUMBER, MAXIMUMRETENTION)
SELECT REGISTRANTID, @BENEFITID, @TYPECODE, @MAXIMUMAMOUNT, @MAXIMUMNUMBER, @MAXIMUMRETENTION
FROM #TEMP_GETREGISTRANT_EVENT_INFO
WHERE PROGRESSCOMMUNICATIONGOAL >= @MAXIMUMNUMBER
SET @i = @i + 1
END
INSERT INTO REGISTRANTBENEFITEXTENSION
(ID, REGISTRANTID, BENEFITID, BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
---------------------------
SELECT newid(), TR01.REGISTRANTID, TR01.BENEFITID, TR01.TYPECODE,@CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_REGISTRANTBENEFITEXTENSION TR01
JOIN (SELECT MAX (MAXIMUMAMOUNT) as MAXIMUMAMOUNT, REGISTRANTID, TYPECODE FROM #TEMP_REGISTRANTBENEFITEXTENSION WHERE TYPECODE=1 GROUP BY REGISTRANTID, TYPECODE) TR1
ON TR01.REGISTRANTID= TR1.REGISTRANTID AND TR01.MAXIMUMAMOUNT= TR1.MAXIMUMAMOUNT
WHERE TR01.TYPECODE=1
UNION ALL
SELECT newid(), TR02.REGISTRANTID, TR02.BENEFITID, TR02.TYPECODE,@CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_REGISTRANTBENEFITEXTENSION TR02
JOIN (SELECT MAX (MAXIMUMNUMBER) as MAXIMUMNUMBER, REGISTRANTID, TYPECODE FROM #TEMP_REGISTRANTBENEFITEXTENSION WHERE TYPECODE=2 GROUP BY REGISTRANTID, TYPECODE) TR2
ON TR02.REGISTRANTID= TR2.REGISTRANTID AND TR02.MAXIMUMNUMBER= TR2.MAXIMUMNUMBER
WHERE TR02.TYPECODE=2
UNION ALL
SELECT newid(), TR03.REGISTRANTID, TR03.BENEFITID, TR03.TYPECODE,@CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_REGISTRANTBENEFITEXTENSION TR03
JOIN (SELECT MAX (MAXIMUMRETENTION) as MAXIMUMRETENTION, REGISTRANTID, TYPECODE FROM #TEMP_REGISTRANTBENEFITEXTENSION WHERE TYPECODE=3 GROUP BY REGISTRANTID, TYPECODE) TR3
ON TR03.REGISTRANTID= TR3.REGISTRANTID AND TR03.MAXIMUMRETENTION= TR3.MAXIMUMRETENTION
WHERE TR03.TYPECODE=3
UNION ALL
SELECT newid(), TR04.REGISTRANTID, TR04.BENEFITID, TR04.TYPECODE,@CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
FROM #TEMP_REGISTRANTBENEFITEXTENSION TR04
JOIN (SELECT MAX (MAXIMUMNUMBER) as MAXIMUMNUMBER, REGISTRANTID, TYPECODE FROM #TEMP_REGISTRANTBENEFITEXTENSION WHERE TYPECODE=4 GROUP BY REGISTRANTID, TYPECODE) TR4
ON TR04.REGISTRANTID= TR4.REGISTRANTID AND TR04.MAXIMUMNUMBER= TR4.MAXIMUMNUMBER
WHERE TR04.TYPECODE=4
END
END
END
-----------------------------------------------------------------------------------------------------
--3...Allow participant to only waive benefits
IF EXISTS (SELECT * FROM EVENTATTRIBUTES WHERE EVENTID= @EVENTID AND ALLOWPARTICIPANTSWAIVEBENEFITS= 1)
BEGIN
IF EXISTS (SELECT * FROM FAFINCENTIVELEVEL FL JOIN FAFINCENTIVELEVELBENEFIT FLB ON FL.ID = FLB.FAFINCENTIVELEVELID AND FL.EVENTID= @EVENTID)
BEGIN
DELETE FROM REGISTRANTBENEFITEXTENSION WHERE REGISTRANTID IN (SELECT REGISTRANTID FROM #TEMP_GETREGISTRANT_EVENT_INFO) AND BENEFITTYPECODE <> 0
AND REGISTRANTID IN (SELECT ID FROM REGISTRANT WHERE BENEFITSWAIVED= 1)
END
END
return 0;