USP_MEMBERSHIPPROGRAMCONTRIBUTION_PROCESSPROGRAM
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @NUMPROCESSED | int | INOUT | |
| @PROCESSDATE | datetime | IN | |
| @OUTPUTTABLE | nvarchar(128) | IN |
Definition
Copy
CREATE procedure dbo.USP_MEMBERSHIPPROGRAMCONTRIBUTION_PROCESSPROGRAM
(
@MEMBERSHIPPROGRAMID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NUMPROCESSED int = 0 output,
@PROCESSDATE datetime = null,
@OUTPUTTABLE nvarchar(128)
)
with execute as owner
as
begin
set nocount on;
begin try
--Criteria option setting
declare @CRITERIADEFINITIONTYPECODE tinyint = 0
--Other contribution settings
declare @ISMULTIPLEREVENUETRANSACTIONSCOUNTED bit = 0
declare @WHATDATETOCALCULATEEXPIRATIONDATECODE tinyint = 0
declare @WHATHAPPENSIFTHEYGIVEMORECODE tinyint = 0
--Program settings
declare @ALLOWMULTIPLEMEMBERSHIPS bit = 0;
declare @RENEWALWINDOWREVENUETYPECODE tinyint
declare @PROGRAMBASEDONCODE tinyint
declare @PROGRAMNAME nvarchar(100)
--Currency
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
declare @PROGRAMBASECURRENCYID uniqueidentifier
declare @PROGRAMBASECURRENCYDIGITS tinyint;
declare @PROGRAMBASECURRENCYROUNDINGTYPECODE tinyint;
--Process variables
set @NUMPROCESSED = 0
set @PROCESSDATE = getdate()
declare @CURRENTEARLIESTDATETIME datetime = dbo.UFN_DATE_GETEARLIESTTIME(@PROCESSDATE)
declare @LASTRUNONDATE date
declare @OUTPUTSQL nvarchar(max)
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
exec dbo.USP_MEMBERSHIPPROGRAMCONTRIBUTION_GETPROGRAMCRITERIA
@MEMBERSHIPPROGRAMID,
@CRITERIADEFINITIONTYPECODE = @CRITERIADEFINITIONTYPECODE output,
@ISMULTIPLEREVENUETRANSACTIONSCOUNTED = @ISMULTIPLEREVENUETRANSACTIONSCOUNTED output,
@WHATDATETOCALCULATEEXPIRATIONDATECODE = @WHATDATETOCALCULATEEXPIRATIONDATECODE output,
@WHATHAPPENSIFTHEYGIVEMORECODE = @WHATHAPPENSIFTHEYGIVEMORECODE output
-- Pull all useful information about the membership program
select
@ALLOWMULTIPLEMEMBERSHIPS = MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS,
@RENEWALWINDOWREVENUETYPECODE = RENEWALWINDOWREVENUETYPECODE,
@PROGRAMNAME = NAME,
@PROGRAMBASEDONCODE = PROGRAMBASEDONCODE,
@LASTRUNONDATE = LASTPROCESSEDON
from dbo.MEMBERSHIPPROGRAM
where MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID
select
@PROGRAMBASECURRENCYDIGITS = CURRENCY.DECIMALDIGITS,
@PROGRAMBASECURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
from dbo.CURRENCY
where ID = @PROGRAMBASECURRENCYID
declare @TERMSBYLEVEL table
(
LEVELID uniqueidentifier,
TERMID uniqueidentifier,
LOWAMOUNT money,
HIGHAMOUNT money,
AFTEREXPIRATION tinyint,
TERMTIMELENGTH int,
SEQUENCE tinyint
)
insert into @TERMSBYLEVEL (LEVELID,TERMID,LOWAMOUNT,HIGHAMOUNT,AFTEREXPIRATION,TERMTIMELENGTH,SEQUENCE)
select
MEMBERSHIPLEVELTERM.LEVELID,
MEMBERSHIPLEVELTERM.ID,
MEMBERSHIPLEVELTERM.LOWAMOUNT,
MEMBERSHIPLEVELTERM.AMOUNT,
MEMBERSHIPLEVEL.AFTEREXPIRATION,
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then MEMBERSHIPLEVELTERM.TERMTIMELENGTH
when 1 then MEMBERSHIPLEVELTERM.TERMTIMELENGTH * 12
end,
MEMBERSHIPLEVEL.SEQUENCE
from dbo.MEMBERSHIPLEVEL
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
where
MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
declare @ORDEREDTERMS table
(
ID tinyint IDENTITY(1,1),
MINIMUMDATE date,
TERMTIMELENGTH int
)
insert into @ORDEREDTERMS(MINIMUMDATE,TERMTIMELENGTH)
select
dateadd(month,TERMSBYLEVEL.TERMTIMELENGTH*-1, @PROCESSDATE) as MINIMUMDATE,
TERMTIMELENGTH
from @TERMSBYLEVEL TERMSBYLEVEL
group by TERMSBYLEVEL.TERMTIMELENGTH
order by TERMTIMELENGTH asc
--Processing is essentially broken into two blocks: whether (2) or not (1) we're considering the sum of multiple revenue transaction to award memberships
--This first block handles awarding membership if we're not adding the splits from multiple revenue transaction together
if @ISMULTIPLEREVENUETRANSACTIONSCOUNTED = 0
begin
if object_id('tempdb..#CONTRIBUTIONPROCESS_REVENUETOCONSIDER') is not null
drop table #CONTRIBUTIONPROCESS_REVENUETOCONSIDER;
create table #CONTRIBUTIONPROCESS_REVENUETOCONSIDER
(
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
FINANCIALTRANSACTIONID uniqueidentifier,
FINANCIALTRANSACTIONLINEITEMID uniqueidentifier,
AMOUNTINCURRENCY money,
TYPECODE tinyint,
EFFECTIVEDATE datetime,
DATECHANGED date,
APPLICATIONCODE tinyint,
DESIGNATIONID uniqueidentifier,
MEMBERSHIPTRANSACTIONID uniqueidentifier
)
--Populate #CONTRIBUTIONPROCESS_REVENUETOCONSIDER with qualifying revenue
if @CRITERIADEFINITIONTYPECODE = 0
begin
--Populate #CONTRIBUTIONPROCESS_REVENUETOCONSIDER with filter criteria
exec dbo.USP_MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE_TOCONSIDER_POPULATE_BYFILTERCRITERIA
@MEMBERSHIPPROGRAMID,
@PROCESSDATE,
@LASTRUNONDATE
end
else if @CRITERIADEFINITIONTYPECODE = 1
begin
--Populate #CONTRIBUTIONPROCESS_REVENUETOCONSIDER with application selection
exec dbo.USP_MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE_TOCONSIDER_POPULATE_BYAPPLICATIONSELECTION
@MEMBERSHIPPROGRAMID,
@PROCESSDATE,
@LASTRUNONDATE
end
--If this program is a 'both ways' program and doesn't allow multiple memberships,
--we need to not consider constituents who are already members of a dues-based level of this program.
if @PROGRAMBASEDONCODE = 2 and @ALLOWMULTIPLEMEMBERSHIPS = 0
begin
delete from #CONTRIBUTIONPROCESS_REVENUETOCONSIDER
where CONSTITUENTID in (
select REVENUETOCONSIDER.CONSTITUENTID
from #CONTRIBUTIONPROCESS_REVENUETOCONSIDER REVENUETOCONSIDER
inner join dbo.MEMBER on MEMBER.CONSTITUENTID = REVENUETOCONSIDER.CONSTITUENTID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
inner join dbo.MEMBERSHIPLEVEL on
MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
and MEMBERSHIP.STATUSCODE <> 1
where
MEMBERSHIPLEVEL.OBTAINLEVELCODE = 0
and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
)
end
--Sum(splits) based on Constituent/Revenue so we can get the new count of the single gift since some splits of a gift could have been excluded
if object_id('tempdb..#CONTRIBUTIONPROCESS_SUMMEDREVENUE') is not null
drop table #CONTRIBUTIONPROCESS_SUMMEDREVENUE;
create table #CONTRIBUTIONPROCESS_SUMMEDREVENUE
(
CONSTITUENTID uniqueidentifier,
FINANCIALTRANSACTIONID uniqueidentifier,
AMOUNTINCURRENCY money,
EFFECTIVEDATE datetime
)
insert into #CONTRIBUTIONPROCESS_SUMMEDREVENUE
(
CONSTITUENTID,
FINANCIALTRANSACTIONID,
AMOUNTINCURRENCY,
EFFECTIVEDATE
)
select
CONSTITUENTID,
FINANCIALTRANSACTIONID,
sum(AMOUNTINCURRENCY) as AMOUNTINCURRENCY,
EFFECTIVEDATE
from #CONTRIBUTIONPROCESS_REVENUETOCONSIDER
group by CONSTITUENTID,FINANCIALTRANSACTIONID,EFFECTIVEDATE;
if object_id('tempdb..#CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT') is not null
drop table #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT;
-- Create and insert into LEVELSTOADD, this will contain all constituents and the revenue that qualifies them for the program/level/term that
-- they currently are not in.
create table #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT
(
CONSTITUENTID uniqueidentifier,
FINANCIALTRANSACTIONID uniqueidentifier,
AMOUNTINCURRENCY money,
EFFECTIVEDATE datetime,
LEVELID uniqueidentifier,
TERMID uniqueidentifier,
AFTEREXPIRATION tinyint,
TERMTIMELENGTH int,
MEMBERSHIPID uniqueidentifier,
MEMBERSHIPTRANSACTIONID uniqueidentifier,
MEMBERID uniqueidentifier,
EXPIRATIONDATE datetime,
EXISTINGMEMBERSHIPID uniqueidentifier
);
insert into #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT
select
CONSTITUENTID,
FINANCIALTRANSACTIONID,
AMOUNTINCURRENCY,
EFFECTIVEDATE,
LEVELID,
TERMID,
AFTEREXPIRATION,
TERMTIMELENGTH,
NEWID() as MEMBERSHIPID,
NEWID() as MEMBERSHIPTRANSACTIONID,
NEWID() as MEMBERID,
case @WHATDATETOCALCULATEEXPIRATIONDATECODE
when 0 then dbo.UFN_DATE_GETLATESTTIME(dateadd(month,TERMTIMELENGTH,EFFECTIVEDATE)) --the transaction date of the gift
when 1 then dbo.UFN_DATE_GETLATESTTIME(dateadd(month,TERMTIMELENGTH,dateadd(s,-1,dateadd(mm, DATEDIFF(m,0,EFFECTIVEDATE)+1,0)))) --the last day of the month the gift was received
end as EXPIRATIONDATE,
(
select top 1 MEMBERSHIP.ID
from dbo.MEMBER
inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
where MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and ALLTERMREVENUE.CONSTITUENTID = MEMBER.CONSTITUENTID
and MEMBERSHIP.STATUSCODE <> 1
and @PROCESSDATE > dateadd(month,ALLTERMREVENUE.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
) as EXISTINGMEMBERSHIPID
from (
select
CONSTITUENTID,
FINANCIALTRANSACTIONID,
AMOUNTINCURRENCY,
EFFECTIVEDATE,
TERMSBYLEVEL.LEVELID,
TERMSBYLEVEL.TERMID,
MEMBERSHIPLEVEL.AFTEREXPIRATION,
ORDEREDTERMS.TERMTIMELENGTH,
ROW_NUMBER() over (partition by CONSTITUENTID order by MEMBERSHIPLEVEL.SEQUENCE DESC,ORDEREDTERMS.TERMTIMELENGTH DESC, AMOUNTINCURRENCY desc) as ROWNUM
from #CONTRIBUTIONPROCESS_SUMMEDREVENUE as SUMMEDREVENUE
inner join @ORDEREDTERMS ORDEREDTERMS on EFFECTIVEDATE >= ORDEREDTERMS.MINIMUMDATE
inner join @TERMSBYLEVEL TERMSBYLEVEL on TERMSBYLEVEL.TERMTIMELENGTH = ORDEREDTERMS.TERMTIMELENGTH
inner join MEMBERSHIPLEVEL on
TERMSBYLEVEL.LEVELID = MEMBERSHIPLEVEL.ID
and SUMMEDREVENUE.AMOUNTINCURRENCY between TERMSBYLEVEL.LOWAMOUNT and TERMSBYLEVEL.HIGHAMOUNT
) as ALLTERMREVENUE
where
ROWNUM=1
and not exists (
select MEMBERSHIP.ID
from dbo.MEMBER
inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
where
MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and ALLTERMREVENUE.CONSTITUENTID = MEMBER.CONSTITUENTID
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and @PROCESSDATE < dateadd(month,ALLTERMREVENUE.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
and MEMBERSHIP.STATUSCODE <> 1
)
insert into dbo.MEMBERSHIP
(
ID,
MEMBERSHIPPROGRAMID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
JOINDATE,
EXPIRATIONDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
LEVELSTOADD.MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
LEVELSTOADD.LEVELID,
LEVELSTOADD.TERMID,
@CURRENTEARLIESTDATETIME,
dbo.UFN_DATE_GETLATESTTIME(EXPIRATIONDATE),
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT as LEVELSTOADD
where
LEVELSTOADD.EXISTINGMEMBERSHIPID is null
and LEVELSTOADD.EXPIRATIONDATE is not null
update dbo.MEMBERSHIP
set
MEMBERSHIP.STATUSCODE = 0,
MEMBERSHIP.EXPIRATIONDATE = LEVELSTOADD.EXPIRATIONDATE,
MEMBERSHIP.MEMBERSHIPLEVELID = LEVELSTOADD.LEVELID,
MEMBERSHIP.MEMBERSHIPLEVELTERMID = LEVELSTOADD.TERMID
from dbo.MEMBERSHIP
inner join #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT as LEVELSTOADD on coalesce(LEVELSTOADD.EXISTINGMEMBERSHIPID,LEVELSTOADD.MEMBERSHIPID) = MEMBERSHIP.ID
where LEVELSTOADD.EXPIRATIONDATE is not null
insert into dbo.MEMBERSHIPTRANSACTION
(
ID,
MEMBERSHIPID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
TRANSACTIONDATE,
EXPIRATIONDATE,
ACTIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
REVENUESPLITID
)
select
LEVELSTOADD.MEMBERSHIPTRANSACTIONID,
case when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then LEVELSTOADD.MEMBERSHIPID
else LEVELSTOADD.EXISTINGMEMBERSHIPID
end,
LEVELSTOADD.LEVELID,
LEVELSTOADD.TERMID,
@CURRENTEARLIESTDATETIME,
dbo.UFN_DATE_GETLATESTTIME(EXPIRATIONDATE),
case
when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then 0
else case when @RENEWALWINDOWREVENUETYPECODE = 0 then 5
else 0
end
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE,
(select top 1 REVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID from #CONTRIBUTIONPROCESS_REVENUETOCONSIDER REVENUETOCONSIDER where REVENUETOCONSIDER.FINANCIALTRANSACTIONID = LEVELSTOADD.FINANCIALTRANSACTIONID)
from #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT as LEVELSTOADD
where LEVELSTOADD.EXPIRATIONDATE is not null
insert into dbo.MEMBER
(
ID,
CONSTITUENTID,
MEMBERSHIPID,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
LEVELSTOADD.MEMBERID,
LEVELSTOADD.CONSTITUENTID,
LEVELSTOADD.MEMBERSHIPID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT as LEVELSTOADD
where
LEVELSTOADD.EXISTINGMEMBERSHIPID is null
and LEVELSTOADD.EXPIRATIONDATE is not null
and LEVELSTOADD.CONSTITUENTID is not null
insert into dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
(
FINANCIALTRANSACTIONLINEITEMID,
MEMBERSHIPID,
MEMBERSHIPPROGRAMID,
CONSTITUENTID,
ORIGINALMEMBERSHIPTRANSACTIONID,
CURRENTMEMBERSHIPTRANSACTIONID,
AMOUNT,
EFFECTIVEDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID,
case
when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then LEVELSTOADD.MEMBERSHIPID
else LEVELSTOADD.EXISTINGMEMBERSHIPID
end,
@MEMBERSHIPPROGRAMID,
LEVELSTOADD.CONSTITUENTID,
LEVELSTOADD.MEMBERSHIPTRANSACTIONID,
LEVELSTOADD.MEMBERSHIPTRANSACTIONID,
REVENUETOCONSIDER.AMOUNTINCURRENCY,
LEVELSTOADD.EFFECTIVEDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT as LEVELSTOADD
inner join #CONTRIBUTIONPROCESS_REVENUETOCONSIDER as REVENUETOCONSIDER on LEVELSTOADD.FINANCIALTRANSACTIONID = REVENUETOCONSIDER.FINANCIALTRANSACTIONID
where LEVELSTOADD.EXPIRATIONDATE is not null
set @NUMPROCESSED += (select count(*) from #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT as LEVELSTOADD where LEVELSTOADD.EXPIRATIONDATE is not null)
--Renew memberships that are available for renewal based on the process date(current date) and the largest gift they have given is > the cost to renew
if object_id('tempdb..#CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT') is not null
drop table #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT;
create table #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT
(
CONSTITUENTID uniqueidentifier,
FINANCIALTRANSACTIONID uniqueidentifier,
AMOUNTINCURRENCY money,
EFFECTIVEDATE date,
LEVELID uniqueidentifier,
TERMID uniqueidentifier,
AFTEREXPIRATION tinyint,
TERMTIMELENGTH int,
TERMLENGTHCODE tinyint,
MEMBERSHIPID uniqueidentifier,
MEMBERSHIPTRANSACTIONID uniqueidentifier,
MEMBERID uniqueidentifier,
EXPIRATIONDATE date,
ACTIONCODE tinyint
)
insert into #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT
(
CONSTITUENTID,
FINANCIALTRANSACTIONID,
AMOUNTINCURRENCY,
EFFECTIVEDATE,
LEVELID,
TERMID,
AFTEREXPIRATION,
TERMTIMELENGTH,
TERMLENGTHCODE,
MEMBERSHIPID,
MEMBERSHIPTRANSACTIONID,
EXPIRATIONDATE,
ACTIONCODE
)
select
MAXGIFTS.CONSTITUENTID,
MAXGIFTS.FINANCIALTRANSACTIONID,
MAXGIFTS.AMOUNTINCURRENCY,
MAXGIFTS.EFFECTIVEDATE,
QUALIFIEDLEVEL.LEVELID,
QUALIFIEDLEVEL.TERMID,
MAXGIFTS.AFTEREXPIRATION,
MAXGIFTS.TERMTIMELENGTH,
MAXGIFTS.TERMLENGTHCODE,
MAXGIFTS.MEMBERSHIPID,
NEWID(),
case when TERMLENGTHCODE = 0 then dbo.UFN_DATE_GETLATESTTIME(dateadd(month,MAXGIFTS.TERMTIMELENGTH,MAXGIFTS.EXPIRATIONDATE))
else dbo.UFN_DATE_GETLATESTTIME(dateadd(year,MAXGIFTS.TERMTIMELENGTH,MAXGIFTS.EXPIRATIONDATE))
end as EXPIRATIONDATE,
case when MAXGIFTS.LEVELID = QUALIFIEDLEVEL.LEVELID then 1 --Renew
else 2 -- Upgrade
end as ACTIONCODE
from (
select
SUMMEDGIFTS.CONSTITUENTID,
SUMMEDGIFTS.FINANCIALTRANSACTIONID,
SUMMEDGIFTS.AMOUNTINCURRENCY,
ROW_NUMBER() over (partition by SUMMEDGIFTS.CONSTITUENTID order by SUMMEDGIFTS.AMOUNTINCURRENCY desc) as ROWNUM,
SUMMEDGIFTS.EFFECTIVEDATE,
MEMBERSHIPLEVEL.ID as LEVELID,
MEMBERSHIPLEVELTERM.ID as TERMID,
MEMBERSHIPLEVEL.AFTEREXPIRATION,
MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
MEMBERSHIP.ID AS MEMBERSHIPID,
MEMBERSHIP.EXPIRATIONDATE,
MEMBERSHIPLEVEL.SEQUENCE
from #CONTRIBUTIONPROCESS_SUMMEDREVENUE as SUMMEDGIFTS
inner join dbo.MEMBER on MEMBER.CONSTITUENTID = SUMMEDGIFTS.CONSTITUENTID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID
left join #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT LEVELSTOADD on LEVELSTOADD.FINANCIALTRANSACTIONID = SUMMEDGIFTS.FINANCIALTRANSACTIONID
where
(
@PROCESSDATE < dateadd(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
and @PROCESSDATE >
case @WHATHAPPENSIFTHEYGIVEMORECODE
when 2 then coalesce(MEMBERSHIP.LASTRENEWEDON,MEMBERSHIP.JOINDATE)
else
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE))
end
end
)
and (
SUMMEDGIFTS.EFFECTIVEDATE < dateadd(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
and SUMMEDGIFTS.EFFECTIVEDATE >=
case @WHATHAPPENSIFTHEYGIVEMORECODE
when 2 then coalesce(MEMBERSHIP.LASTRENEWEDON,MEMBERSHIP.JOINDATE)
else
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE))
end
end
)
and SUMMEDGIFTS.AMOUNTINCURRENCY >= MEMBERSHIPLEVELTERM.LOWAMOUNT
and MEMBERSHIP.STATUSCODE <> 1
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and LEVELSTOADD.FINANCIALTRANSACTIONID is null
) as MAXGIFTS
cross apply
(
select top(1)
MEMBERSHIPLEVEL.ID as LEVELID,
MEMBERSHIPLEVELTERM.ID as TERMID
from dbo.MEMBERSHIPLEVEL
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
where
MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MAXGIFTS.AMOUNTINCURRENCY >= (MEMBERSHIPLEVELTERM.LOWAMOUNT)
and MAXGIFTS.AMOUNTINCURRENCY <= (MEMBERSHIPLEVELTERM.AMOUNT)
and MEMBERSHIPLEVEL.SEQUENCE >= MAXGIFTS.SEQUENCE
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and MEMBERSHIPLEVELTERM.TERMTIMELENGTH = MAXGIFTS.TERMTIMELENGTH
and MEMBERSHIPLEVELTERM.TERMLENGTHCODE = MAXGIFTS.TERMLENGTHCODE
order by MEMBERSHIPLEVEL.SEQUENCE desc
) as QUALIFIEDLEVEL
where MAXGIFTS.ROWNUM = 1
update dbo.MEMBERSHIP
set
STATUSCODE = 0,
MEMBERSHIPLEVELID = LEVELSTORENEW.LEVELID,
MEMBERSHIPLEVELTERMID = LEVELSTORENEW.TERMID,
LASTRENEWEDON = @CURRENTEARLIESTDATETIME,
EXPIRATIONDATE = LEVELSTORENEW.EXPIRATIONDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIP
inner join #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT LEVELSTORENEW on LEVELSTORENEW.MEMBERSHIPID = MEMBERSHIP.ID
insert into dbo.MEMBERSHIPTRANSACTION
(
ID,
MEMBERSHIPID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
TRANSACTIONDATE,
EXPIRATIONDATE,
ACTIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
REVENUESPLITID
)
select
LEVELSTORENEW.MEMBERSHIPTRANSACTIONID,
LEVELSTORENEW.MEMBERSHIPID,
LEVELSTORENEW.LEVELID,
LEVELSTORENEW.TERMID,
@CURRENTEARLIESTDATETIME,
dbo.UFN_DATE_GETLATESTTIME(EXPIRATIONDATE),
LEVELSTORENEW.ACTIONCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE,
(select top 1 REVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID from #CONTRIBUTIONPROCESS_REVENUETOCONSIDER REVENUETOCONSIDER where REVENUETOCONSIDER.FINANCIALTRANSACTIONID = LEVELSTORENEW.FINANCIALTRANSACTIONID)
from #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT as LEVELSTORENEW
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set CURRENTMEMBERSHIPTRANSACTIONID = LEVELSTORENEW.MEMBERSHIPTRANSACTIONID
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT as LEVELSTORENEW on LEVELSTORENEW.MEMBERSHIPID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID
insert into dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
(
FINANCIALTRANSACTIONLINEITEMID,
MEMBERSHIPID,
MEMBERSHIPPROGRAMID,
CONSTITUENTID,
ORIGINALMEMBERSHIPTRANSACTIONID,
CURRENTMEMBERSHIPTRANSACTIONID,
AMOUNT,
EFFECTIVEDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID,
LEVELSTORENEW.MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
LEVELSTORENEW.CONSTITUENTID,
LEVELSTORENEW.MEMBERSHIPTRANSACTIONID,
LEVELSTORENEW.MEMBERSHIPTRANSACTIONID,
REVENUETOCONSIDER.AMOUNTINCURRENCY,
LEVELSTORENEW.EFFECTIVEDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT as LEVELSTORENEW
inner join #CONTRIBUTIONPROCESS_REVENUETOCONSIDER as REVENUETOCONSIDER on LEVELSTORENEW.FINANCIALTRANSACTIONID = REVENUETOCONSIDER.FINANCIALTRANSACTIONID
set @NUMPROCESSED += (select count(*) from #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT)
--Deal with upgrading potential memberships if the option that revenue outside of the renewal window is counted toward and upgrade
if @WHATHAPPENSIFTHEYGIVEMORECODE = 1
begin
if object_id('tempdb..#CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT') is not null
drop table #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT;
create table #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT
(
CONSTITUENTID uniqueidentifier,
FINANCIALTRANSACTIONID uniqueidentifier,
AMOUNTINCURRENCY money,
EFFECTIVEDATE date,
LEVELID uniqueidentifier,
TERMID uniqueidentifier,
AFTEREXPIRATION tinyint,
TERMTIMELENGTH int,
TERMLENGTHCODE tinyint,
MEMBERSHIPID uniqueidentifier,
MEMBERSHIPTRANSACTIONID uniqueidentifier,
MEMBERID uniqueidentifier,
EXPIRATIONDATE date
)
insert into #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT
(
CONSTITUENTID,
FINANCIALTRANSACTIONID,
AMOUNTINCURRENCY,
EFFECTIVEDATE,
LEVELID,
TERMID,
AFTEREXPIRATION,
TERMTIMELENGTH,
TERMLENGTHCODE,
MEMBERSHIPID,
MEMBERSHIPTRANSACTIONID,
EXPIRATIONDATE
)
select
TOPGIFT.CONSTITUENTID,
TOPGIFT.FINANCIALTRANSACTIONID,
TOPGIFT.AMOUNTINCURRENCY,
TOPGIFT.EFFECTIVEDATE,
TOPGIFT.LEVELID,
TOPGIFT.TERMID,
TOPGIFT.AFTEREXPIRATION,
TOPGIFT.TERMTIMELENGTH,
TOPGIFT.TERMLENGTHCODE,
TOPGIFT.MEMBERSHIPID,
TOPGIFT.MEMBERSHIPTRANSACTIONID,
TOPGIFT.EXPIRATIONDATE
from (
select
MAXGIFTS.CONSTITUENTID,
MAXGIFTS.FINANCIALTRANSACTIONID,
MAXGIFTS.AMOUNTINCURRENCY,
MAXGIFTS.EFFECTIVEDATE,
QUALIFIEDLEVEL.LEVELID,
QUALIFIEDLEVEL.TERMID,
MAXGIFTS.AFTEREXPIRATION,
MAXGIFTS.TERMTIMELENGTH,
MAXGIFTS.TERMLENGTHCODE,
MAXGIFTS.MEMBERSHIPID,
NEWID() as MEMBERSHIPTRANSACTIONID,
MAXGIFTS.EXPIRATIONDATE,
ROW_NUMBER() over (partition by MAXGIFTS.CONSTITUENTID order by MAXGIFTS.AMOUNTINCURRENCY desc) as ROWNUM
from (
select
SUMMEDGIFTS.CONSTITUENTID,
SUMMEDGIFTS.FINANCIALTRANSACTIONID,
SUMMEDGIFTS.AMOUNTINCURRENCY,
SUMMEDGIFTS.EFFECTIVEDATE,
MEMBERSHIPLEVEL.ID as LEVELID,
MEMBERSHIPLEVEL.AFTEREXPIRATION,
MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
MEMBERSHIP.ID AS MEMBERSHIPID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPLEVELTERM.AMOUNT as MEMBERSHIPLEVELTERMAMOUNT,
(
select SUM(AMOUNT)
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID = MEMBERSHIP.ID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.ORIGINALMEMBERSHIPTRANSACTIONID in (
select MEMBERSHIPTRANSACTION.ID
from dbo.MEMBERSHIPTRANSACTION
where MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
and MEMBERSHIPTRANSACTION.EXPIRATIONDATE = (
select max(MT.EXPIRATIONDATE)
from dbo.MEMBERSHIPTRANSACTION MT
where MT.MEMBERSHIPID = MEMBERSHIP.ID
)
)
) as AMOUNTGIVENTOPROGRAM,
MEMBERSHIP.EXPIRATIONDATE
from #CONTRIBUTIONPROCESS_SUMMEDREVENUE as SUMMEDGIFTS
inner join dbo.MEMBER on MEMBER.CONSTITUENTID = SUMMEDGIFTS.CONSTITUENTID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID
left join #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT LEVELSTOADD on LEVELSTOADD.FINANCIALTRANSACTIONID = SUMMEDGIFTS.FINANCIALTRANSACTIONID
left join #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT LEVELSTORENEW on LEVELSTORENEW.FINANCIALTRANSACTIONID = SUMMEDGIFTS.FINANCIALTRANSACTIONID
where
(
@PROCESSDATE <
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE)
end
)
and (
SUMMEDGIFTS.EFFECTIVEDATE <
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE)
end
)
and LEVELSTOADD.FINANCIALTRANSACTIONID is null
and MEMBERSHIP.STATUSCODE <> 1
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and LEVELSTORENEW.FINANCIALTRANSACTIONID is null
) as MAXGIFTS
cross apply
(
select top(1)
MEMBERSHIPLEVEL.ID as LEVELID,
MEMBERSHIPLEVELTERM.ID as TERMID
from dbo.MEMBERSHIPLEVEL
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
where
MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MAXGIFTS.AMOUNTINCURRENCY >= (MEMBERSHIPLEVELTERM.LOWAMOUNT-MAXGIFTS.AMOUNTGIVENTOPROGRAM)
and MAXGIFTS.AMOUNTINCURRENCY <= (MEMBERSHIPLEVELTERM.AMOUNT-MAXGIFTS.AMOUNTGIVENTOPROGRAM)
and MEMBERSHIPLEVEL.SEQUENCE > MAXGIFTS.SEQUENCE
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and MEMBERSHIPLEVELTERM.TERMTIMELENGTH = MAXGIFTS.TERMTIMELENGTH
and MEMBERSHIPLEVELTERM.TERMLENGTHCODE = MAXGIFTS.TERMLENGTHCODE
order by MEMBERSHIPLEVEL.SEQUENCE desc
) as QUALIFIEDLEVEL
) as TOPGIFT
where TOPGIFT.ROWNUM = 1
update dbo.MEMBERSHIP
set
MEMBERSHIPLEVELID = LEVELSTOUPGRADE.LEVELID,
MEMBERSHIPLEVELTERMID = LEVELSTOUPGRADE.TERMID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIP
inner join #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT as LEVELSTOUPGRADE on LEVELSTOUPGRADE.MEMBERSHIPID = MEMBERSHIP.ID
insert into dbo.MEMBERSHIPTRANSACTION
(
ID,
MEMBERSHIPID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
TRANSACTIONDATE,
EXPIRATIONDATE,
ACTIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
REVENUESPLITID
)
select
LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID,
LEVELSTOUPGRADE.MEMBERSHIPID,
LEVELSTOUPGRADE.LEVELID,
LEVELSTOUPGRADE.TERMID,
@CURRENTEARLIESTDATETIME,
dbo.UFN_DATE_GETLATESTTIME(EXPIRATIONDATE),
2, --Upgrade
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE,
(select top 1 REVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID from #CONTRIBUTIONPROCESS_REVENUETOCONSIDER REVENUETOCONSIDER where REVENUETOCONSIDER.FINANCIALTRANSACTIONID = LEVELSTOUPGRADE.FINANCIALTRANSACTIONID)
from #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT as LEVELSTOUPGRADE
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set CURRENTMEMBERSHIPTRANSACTIONID = LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT as LEVELSTOUPGRADE on LEVELSTOUPGRADE.MEMBERSHIPID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID
insert into dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
(
FINANCIALTRANSACTIONLINEITEMID,
MEMBERSHIPID,
MEMBERSHIPPROGRAMID,
CONSTITUENTID,
ORIGINALMEMBERSHIPTRANSACTIONID,
CURRENTMEMBERSHIPTRANSACTIONID,
AMOUNT,
EFFECTIVEDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID,
LEVELSTOUPGRADE.MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
LEVELSTOUPGRADE.CONSTITUENTID,
LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID,
LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID,
REVENUETOCONSIDER.AMOUNTINCURRENCY,
LEVELSTOUPGRADE.EFFECTIVEDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT as LEVELSTOUPGRADE
inner join #CONTRIBUTIONPROCESS_REVENUETOCONSIDER as REVENUETOCONSIDER on LEVELSTOUPGRADE.FINANCIALTRANSACTIONID = REVENUETOCONSIDER.FINANCIALTRANSACTIONID
set @NUMPROCESSED += (select count(*) from #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT)
end
drop table #CONTRIBUTIONPROCESS_REVENUETOCONSIDER
set @OUTPUTSQL = N'insert into dbo.' + @OUTPUTTABLE + N' (MEMBERSHIPID, MEMBERSHIPPROGRAMNAME, MEMBERSHIPLEVELNAME, MEMBERSHIPTERMNAME, CONSTITUENTNAME)
select
LEVELSTORENEW.MEMBERSHIPID,
@PROGRAMNAME,
MEMBERSHIPLEVEL.NAME,
CAST(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(3)) + MEMBERSHIPLEVELTERM.TERMLENGTH,
NF.NAME
from #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT as LEVELSTORENEW
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = LEVELSTORENEW.TERMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = LEVELSTORENEW.LEVELID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = LEVELSTORENEW.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
union all
select
case when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then LEVELSTOADD.MEMBERSHIPID
else LEVELSTOADD.EXISTINGMEMBERSHIPID
end as MEMBERSHIPID,
@PROGRAMNAME,
MEMBERSHIPLEVEL.NAME,
CAST(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(3)) + MEMBERSHIPLEVELTERM.TERMLENGTH,
NF.NAME
from #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT as LEVELSTOADD
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = LEVELSTOADD.TERMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = LEVELSTOADD.LEVELID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = LEVELSTOADD.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
where LEVELSTOADD.EXPIRATIONDATE is not null '
if @WHATHAPPENSIFTHEYGIVEMORECODE = 1
begin
set @OUTPUTSQL = @OUTPUTSQL + 'union all
select
LEVELSTOUPGRADE.MEMBERSHIPID,
@PROGRAMNAME,
MEMBERSHIPLEVEL.NAME,
CAST(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(3)) + MEMBERSHIPLEVELTERM.TERMLENGTH,
NF.NAME
from #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT as LEVELSTOUPGRADE
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = LEVELSTOUPGRADE.TERMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = LEVELSTOUPGRADE.LEVELID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = LEVELSTOUPGRADE.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF '
end
exec sp_executesql @OUTPUTSQL, N'@PROGRAMNAME nvarchar(100)',@PROGRAMNAME=@PROGRAMNAME
drop table #CONTRIBUTIONPROCESS_LEVELSTOADDFULLGIFT
drop table #CONTRIBUTIONPROCESS_LEVELSTORENEWFULLGIFT
if @WHATHAPPENSIFTHEYGIVEMORECODE = 1
drop table #CONTRIBUTIONPROCESS_LEVELSTOUPGRADEFULLGIFT
end --end if @ISMULTIPLEREVENUETRANSACTIONSCOUNTED = 1
/*** Block for when we're considering the sum of multiple revenue transactions to award memberships ***/
else if @ISMULTIPLEREVENUETRANSACTIONSCOUNTED = 1
begin
--Updating line items that have been previously considered by the process and since been updated.
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set
CONSTITUENTID = CHANGEDSPLITS.CONSTITUENTID,
AMOUNT = CHANGEDSPLITS.AMOUNTINCURRENCY,
EFFECTIVEDATE = CHANGEDSPLITS.EFFECTIVEDATE,
DATECHANGED = @PROCESSDATE,
CHANGEDBYID = @CHANGEAGENTID
from (
select
REVENUESPLIT_INCURRENCY.ID,
REVENUESPLIT_INCURRENCY.AMOUNTINCURRENCY,
REVENUESPLIT_INCURRENCY.DATE as EFFECTIVEDATE,
REVENUE_INCURRENCY.CONSTITUENTID
from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@PROGRAMBASECURRENCYID, @ORGANIZATIONCURRENCYID, @PROGRAMBASECURRENCYDIGITS, @PROGRAMBASECURRENCYROUNDINGTYPECODE) as REVENUE_INCURRENCY
inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@PROGRAMBASECURRENCYID, @ORGANIZATIONCURRENCYID, @PROGRAMBASECURRENCYDIGITS, @PROGRAMBASECURRENCYROUNDINGTYPECODE) as REVENUESPLIT_INCURRENCY on
REVENUESPLIT_INCURRENCY.REVENUEID = REVENUE_INCURRENCY.ID
where
(REVENUE_INCURRENCY.DATECHANGED > @LASTRUNONDATE or @LASTRUNONDATE is null) and
(REVENUESPLIT_INCURRENCY.DATE <= @PROCESSDATE or @PROCESSDATE is null)
--If the split has been deleted, should we remove this record?
) CHANGEDSPLITS
where
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID = CHANGEDSPLITS.ID and
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID is null and
(
CHANGEDSPLITS.CONSTITUENTID <> MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID or
CHANGEDSPLITS.AMOUNTINCURRENCY <> MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.AMOUNT or
CHANGEDSPLITS.EFFECTIVEDATE <> MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE
)
--Populate MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE with qualifying revenue
if @CRITERIADEFINITIONTYPECODE = 0
begin
--Populate MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE with filter criteria
exec dbo.USP_MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE_POPULATE_BYFILTERCRITERIA
@MEMBERSHIPPROGRAMID,
@CHANGEAGENTID,
@PROCESSDATE,
@LASTRUNONDATE
end
else if @CRITERIADEFINITIONTYPECODE = 1
begin
--Populate MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE with application selection
exec dbo.USP_MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE_POPULATE_BYAPPLICATIONSELECTION
@MEMBERSHIPPROGRAMID,
@CHANGEAGENTID,
@PROCESSDATE,
@LASTRUNONDATE
end
if object_id('tempdb..#CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK') is not null
drop table #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK;
create table #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK (
CONSTITUENTID uniqueidentifier primary key clustered(CONSTITUENTID)
)
insert into #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK(CONSTITUENTID)
(
select distinct MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
where
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.DATECHANGED = @PROCESSDATE
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
)
--If this program is a 'both ways' program and doesn't allow multiple memberships,
--we need to not consider constituents who are already members of a dues-based level of this program.
if @PROGRAMBASEDONCODE = 2 and @ALLOWMULTIPLEMEMBERSHIPS = 0
begin
delete from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK
where CONSTITUENTID in (
select CONSTITUENTSTOCHECK.CONSTITUENTID
from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK CONSTITUENTSTOCHECK
inner join dbo.MEMBER on MEMBER.CONSTITUENTID = CONSTITUENTSTOCHECK.CONSTITUENTID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
inner join dbo.MEMBERSHIPLEVEL on
MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
and MEMBERSHIP.STATUSCODE <> 1
where
MEMBERSHIPLEVEL.OBTAINLEVELCODE = 0
and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
)
end
--Sum(splits) based on Constituent/Revenue so we can get the new count of the single gift since some splits of a gift could have been excluded
if object_id('tempdb..#CONTRIBUTIONPROCESS_LEVELSTOADD') is not null
drop table #CONTRIBUTIONPROCESS_LEVELSTOADD;
-- Create and insert into LEVELSTOADD, this will contain all constituents and the revenue that qualifies them for the program/level/term that
-- they currently are not in.
create table #CONTRIBUTIONPROCESS_LEVELSTOADD
(
CONSTITUENTID uniqueidentifier,
EFFECTIVEDATE datetime,
LEVELID uniqueidentifier,
TERMID uniqueidentifier,
AFTEREXPIRATION tinyint,
TERMTIMELENGTH int,
TERMLENGTHCODE tinyint,
MEMBERSHIPID uniqueidentifier,
MEMBERSHIPTRANSACTIONID uniqueidentifier,
MEMBERID uniqueidentifier,
EXPIRATIONDATE date,
EXISTINGMEMBERSHIPID uniqueidentifier
);
with MAXSUMMED_CTE as (
select CONSTITUENTID,
SUM(AMOUNT) as AMOUNT,
MAX(EFFECTIVEDATE) AS EFFECTIVEDATE,
TERMSBYLEVEL.LEVELID,
TERMSBYLEVEL.TERMID,
MEMBERSHIPLEVEL.AFTEREXPIRATION,
ORDEREDTERMS.TERMTIMELENGTH,
MEMBERSHIPLEVEL.SEQUENCE
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join @ORDEREDTERMS ORDEREDTERMS on EFFECTIVEDATE >= ORDEREDTERMS.MINIMUMDATE
inner join @TERMSBYLEVEL TERMSBYLEVEL on TERMSBYLEVEL.TERMTIMELENGTH = ORDEREDTERMS.TERMTIMELENGTH
inner join MEMBERSHIPLEVEL on TERMSBYLEVEL.LEVELID = MEMBERSHIPLEVEL.ID
where
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID in (select CONSTITUENTID from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK)
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID is null
and not exists (
select MEMBERSHIP.ID
from dbo.MEMBER
inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
where MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID = MEMBER.CONSTITUENTID
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and @PROCESSDATE < dateadd(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
)
group by CONSTITUENTID,TERMSBYLEVEL.LEVELID,TERMSBYLEVEL.TERMID,MEMBERSHIPLEVEL.AFTEREXPIRATION,ORDEREDTERMS.TERMTIMELENGTH,
MEMBERSHIPLEVEL.SEQUENCE,TERMSBYLEVEL.LOWAMOUNT,TERMSBYLEVEL.HIGHAMOUNT
having SUM(AMOUNT) between TERMSBYLEVEL.LOWAMOUNT and TERMSBYLEVEL.HIGHAMOUNT
)
insert into #CONTRIBUTIONPROCESS_LEVELSTOADD
(
CONSTITUENTID,
LEVELID,
TERMID,
AFTEREXPIRATION,
TERMTIMELENGTH,
MEMBERSHIPID,
MEMBERSHIPTRANSACTIONID,
MEMBERID,
EXPIRATIONDATE,
EXISTINGMEMBERSHIPID
)
select
MAXSUMMED_CTE.CONSTITUENTID,
LEVELID,
TERMID,
AFTEREXPIRATION,
TERMTIMELENGTH,
NEWID(),
NEWID(),
NEWID(),
case @WHATDATETOCALCULATEEXPIRATIONDATECODE
when 0 then dbo.UFN_DATE_GETLATESTTIME(dateadd(month,TERMTIMELENGTH,EFFECTIVEDATE))
when 1 then dbo.UFN_DATE_GETLATESTTIME(dateadd(month,TERMTIMELENGTH,dateadd(s,-1,dateadd(mm, DATEDIFF(m,0,EFFECTIVEDATE)+1,0))))
end as EXPIRATIONDATE,
(
select top 1 MEMBERSHIP.ID
from dbo.MEMBER
inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
where
MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MAXSUMMED_CTE.CONSTITUENTID = MEMBER.CONSTITUENTID
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
and MEMBERSHIP.STATUSCODE <> 1
and @PROCESSDATE > dateadd(month,MAXSUMMED_CTE.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
) as EXISTINGMEMBERSHIPID
from (
select MAXSUMMED_CTE.CONSTITUENTID,MAXSUMMED_CTE.SEQUENCE,MAX(MAXSUMMED_CTE.TERMTIMELENGTH) as MAXTERM
from (
select CONSTITUENTID,MAX(sequence) as MXSEQUENCE
from MAXSUMMED_CTE
group by CONSTITUENTID
) as TEMP
inner join MAXSUMMED_CTE on MAXSUMMED_CTE.CONSTITUENTID = TEMP.CONSTITUENTID and MAXSUMMED_CTE.SEQUENCE = TEMP.MXSEQUENCE
group by MAXSUMMED_CTE.CONSTITUENTID,MAXSUMMED_CTE.SEQUENCE
) as TEMP2
inner join MAXSUMMED_CTE on MAXSUMMED_CTE.CONSTITUENTID = TEMP2.CONSTITUENTID and MAXSUMMED_CTE.SEQUENCE = TEMP2.SEQUENCE and MAXSUMMED_CTE.TERMTIMELENGTH = TEMP2.MAXTERM
insert into dbo.MEMBERSHIP
(
ID,
MEMBERSHIPPROGRAMID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
JOINDATE,
EXPIRATIONDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
LEVELSTOADD.MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
LEVELSTOADD.LEVELID,
LEVELSTOADD.TERMID,
@CURRENTEARLIESTDATETIME,
EXPIRATIONDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOADD as LEVELSTOADD
where LEVELSTOADD.EXISTINGMEMBERSHIPID is null
and LEVELSTOADD.EXPIRATIONDATE is not null
update dbo.MEMBERSHIP
set
MEMBERSHIP.STATUSCODE = 0,
MEMBERSHIP.EXPIRATIONDATE = LEVELSTOADD.EXPIRATIONDATE,
MEMBERSHIP.MEMBERSHIPLEVELID = LEVELSTOADD.LEVELID,
MEMBERSHIP.MEMBERSHIPLEVELTERMID = LEVELSTOADD.TERMID
from dbo.MEMBERSHIP
inner join #CONTRIBUTIONPROCESS_LEVELSTOADD as LEVELSTOADD on coalesce(LEVELSTOADD.EXISTINGMEMBERSHIPID,LEVELSTOADD.MEMBERSHIPID) = MEMBERSHIP.ID
where LEVELSTOADD.EXPIRATIONDATE is not null
insert into dbo.MEMBERSHIPTRANSACTION
(
ID,
MEMBERSHIPID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
TRANSACTIONDATE,
EXPIRATIONDATE,
ACTIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
LEVELSTOADD.MEMBERSHIPTRANSACTIONID,
case when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then LEVELSTOADD.MEMBERSHIPID
else LEVELSTOADD.EXISTINGMEMBERSHIPID
end,
LEVELSTOADD.LEVELID,
LEVELSTOADD.TERMID,
@CURRENTEARLIESTDATETIME,
dbo.UFN_DATE_GETLATESTTIME(EXPIRATIONDATE),
case
when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then 0
else
case when @RENEWALWINDOWREVENUETYPECODE = 0 then 5
else 0
end
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOADD as LEVELSTOADD
where LEVELSTOADD.EXPIRATIONDATE is not null
insert into dbo.MEMBER
(
ID,
CONSTITUENTID,
MEMBERSHIPID,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
LEVELSTOADD.MEMBERID,
LEVELSTOADD.CONSTITUENTID,
LEVELSTOADD.MEMBERSHIPID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOADD as LEVELSTOADD
where LEVELSTOADD.EXISTINGMEMBERSHIPID is null
and LEVELSTOADD.EXPIRATIONDATE is not null
and LEVELSTOADD.CONSTITUENTID is not null
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set
MEMBERSHIPID = case
when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then LEVELSTOADD.MEMBERSHIPID
else LEVELSTOADD.EXISTINGMEMBERSHIPID
end,
ORIGINALMEMBERSHIPTRANSACTIONID = LEVELSTOADD.MEMBERSHIPTRANSACTIONID,
CURRENTMEMBERSHIPTRANSACTIONID = LEVELSTOADD.MEMBERSHIPTRANSACTIONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join #CONTRIBUTIONPROCESS_LEVELSTOADD as LEVELSTOADD on LEVELSTOADD.CONSTITUENTID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID
inner join @ORDEREDTERMS ORDEREDTERMS on MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE >= ORDEREDTERMS.MINIMUMDATE and LEVELSTOADD.TERMTIMELENGTH = ORDEREDTERMS.TERMTIMELENGTH
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID is null
and LEVELSTOADD.EXPIRATIONDATE is not null
delete from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK
where CONSTITUENTID in (select LEVELSTOADD.CONSTITUENTID from #CONTRIBUTIONPROCESS_LEVELSTOADD LEVELSTOADD)
set @NUMPROCESSED += (select count(*) from #CONTRIBUTIONPROCESS_LEVELSTOADD as LEVELSTOADD where LEVELSTOADD.EXPIRATIONDATE is not null)
--Renew memberships that are available for renewal based on the process date(current date) and the largest gift they have given is > the cost to renew
if object_id('tempdb..#CONTRIBUTIONPROCESS_LEVELSTORENEW') is not null
drop table #CONTRIBUTIONPROCESS_LEVELSTORENEW;
create table #CONTRIBUTIONPROCESS_LEVELSTORENEW
(
CONSTITUENTID uniqueidentifier,
MEMBERSHIPID uniqueidentifier,
MEMBERSHIPLEVELID uniqueidentifier,
MEMBERSHIPTERMID uniqueidentifier,
MEMBERSHIPTRANSACTIONID uniqueidentifier,
EXPIRATIONDATE date,
TERMTIMELENGTH int,
TERMLENGTHCODE tinyint,
ACTIONCODE tinyint
)
insert into #CONTRIBUTIONPROCESS_LEVELSTORENEW
(
CONSTITUENTID,
MEMBERSHIPID,
MEMBERSHIPLEVELID,
MEMBERSHIPTERMID,
MEMBERSHIPTRANSACTIONID,
EXPIRATIONDATE,
TERMTIMELENGTH,
TERMLENGTHCODE,
ACTIONCODE
)
select
SUMMEDGIFT.CONSTITUENTID,
SUMMEDGIFT.MEMBERSHIPID,
QUALIFIEDLEVEL.LEVELID,
QUALIFIEDLEVEL.TERMID,
NEWID(),
case when TERMLENGTHCODE = 0 then dbo.UFN_DATE_GETLATESTTIME(dateadd(month,SUMMEDGIFT.TERMTIMELENGTH,SUMMEDGIFT.EXPIRATIONDATE))
else dbo.UFN_DATE_GETLATESTTIME(dateadd(year,SUMMEDGIFT.TERMTIMELENGTH,SUMMEDGIFT.EXPIRATIONDATE))
end as EXPIRATIONDATE,
SUMMEDGIFT.TERMTIMELENGTH,
SUMMEDGIFT.TERMLENGTHCODE,
case when SUMMEDGIFT.MEMBERSHIPLEVELID = QUALIFIEDLEVEL.LEVELID then 1 --Renew
else 2 -- Upgrade
end as ACTIONCODE
from (
select
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID,
sum(MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.AMOUNT) as AMOUNT,
max(EFFECTIVEDATE) as EFFECTIVEDATE,
MEMBERSHIPLEVELTERM.LOWAMOUNT,
MEMBERSHIPLEVELTERM.AMOUNT as HIGHAMOUNT,
MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
MEMBERSHIP.ID as MEMBERSHIPID,
MEMBERSHIP.MEMBERSHIPLEVELID,
MEMBERSHIP.MEMBERSHIPLEVELTERMID,
MEMBERSHIP.EXPIRATIONDATE,
MEMBERSHIPLEVEL.SEQUENCE
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join dbo.MEMBER on MEMBER.CONSTITUENTID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID
where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID in (select CONSTITUENTID from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK)
and MEMBERSHIP.STATUSCODE <> 1
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.ORIGINALMEMBERSHIPTRANSACTIONID is null
and (
@PROCESSDATE < dateadd(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
and @PROCESSDATE >
case @WHATHAPPENSIFTHEYGIVEMORECODE
when 2 then coalesce(MEMBERSHIP.LASTRENEWEDON,MEMBERSHIP.JOINDATE)
else
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE))
end
end
)
and (MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE < dateadd(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE >=
case @WHATHAPPENSIFTHEYGIVEMORECODE
when 2 then coalesce(MEMBERSHIP.LASTRENEWEDON,MEMBERSHIP.JOINDATE)
else
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE))
end
end
)
group by MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID,MEMBERSHIP.ID,MEMBERSHIPLEVELTERM.LOWAMOUNT,
MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIPLEVELTERM.TERMLENGTHCODE,MEMBERSHIP.MEMBERSHIPLEVELID,MEMBERSHIP.MEMBERSHIPLEVELTERMID,MEMBERSHIPLEVELTERM.AMOUNT,MEMBERSHIP.EXPIRATIONDATE, MEMBERSHIPLEVEL.SEQUENCE
) as SUMMEDGIFT
cross apply (
select top(1)
MEMBERSHIPLEVEL.ID as LEVELID,
MEMBERSHIPLEVELTERM.ID as TERMID
from dbo.MEMBERSHIPLEVEL
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and SUMMEDGIFT.AMOUNT >= (MEMBERSHIPLEVELTERM.LOWAMOUNT)
and SUMMEDGIFT.AMOUNT <= (MEMBERSHIPLEVELTERM.AMOUNT)
and MEMBERSHIPLEVEL.SEQUENCE >= SUMMEDGIFT.SEQUENCE
and MEMBERSHIPLEVELTERM.TERMTIMELENGTH = SUMMEDGIFT.TERMTIMELENGTH
and MEMBERSHIPLEVELTERM.TERMLENGTHCODE = SUMMEDGIFT.TERMLENGTHCODE
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
order by MEMBERSHIPLEVEL.SEQUENCE desc
) as QUALIFIEDLEVEL
insert into dbo.MEMBERSHIPTRANSACTION
(
ID,
MEMBERSHIPID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
TRANSACTIONDATE,
EXPIRATIONDATE,
ACTIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
LEVELSTORENEW.MEMBERSHIPTRANSACTIONID,
LEVELSTORENEW.MEMBERSHIPID,
LEVELSTORENEW.MEMBERSHIPLEVELID,
LEVELSTORENEW.MEMBERSHIPTERMID,
@CURRENTEARLIESTDATETIME,
dbo.UFN_DATE_GETLATESTTIME(EXPIRATIONDATE),
LEVELSTORENEW.ACTIONCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTORENEW as LEVELSTORENEW
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set
MEMBERSHIPID = LEVELSTORENEW.MEMBERSHIPID,
ORIGINALMEMBERSHIPTRANSACTIONID = LEVELSTORENEW.MEMBERSHIPTRANSACTIONID,
CURRENTMEMBERSHIPTRANSACTIONID = LEVELSTORENEW.MEMBERSHIPTRANSACTIONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join #CONTRIBUTIONPROCESS_LEVELSTORENEW as LEVELSTORENEW on LEVELSTORENEW.CONSTITUENTID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = LEVELSTORENEW.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = LEVELSTORENEW.MEMBERSHIPID
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID is null
and (
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE < dateadd(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE)
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE >=
case @WHATHAPPENSIFTHEYGIVEMORECODE
when 2 then coalesce(MEMBERSHIP.LASTRENEWEDON,MEMBERSHIP.JOINDATE)
else
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE))
end
end
)
and LEVELSTORENEW.EXPIRATIONDATE is not null
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set CURRENTMEMBERSHIPTRANSACTIONID = LEVELSTORENEW.MEMBERSHIPTRANSACTIONID
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join #CONTRIBUTIONPROCESS_LEVELSTORENEW as LEVELSTORENEW on LEVELSTORENEW.MEMBERSHIPID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID
update dbo.MEMBERSHIP
set
STATUSCODE = 0,
MEMBERSHIPLEVELID = LEVELSTORENEW.MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID = LEVELSTORENEW.MEMBERSHIPTERMID,
LASTRENEWEDON = @CURRENTEARLIESTDATETIME,
EXPIRATIONDATE = LEVELSTORENEW.EXPIRATIONDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIP
inner join #CONTRIBUTIONPROCESS_LEVELSTORENEW LEVELSTORENEW on LEVELSTORENEW.MEMBERSHIPID = MEMBERSHIP.ID
delete from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK
where CONSTITUENTID in (select LEVELSTORENEW.CONSTITUENTID from #CONTRIBUTIONPROCESS_LEVELSTORENEW LEVELSTORENEW)
set @NUMPROCESSED += (select count(*) from #CONTRIBUTIONPROCESS_LEVELSTORENEW)
if @WHATHAPPENSIFTHEYGIVEMORECODE = 1
begin
declare @FINANCIALTRANSACTIONID uniqueidentifier,
@FINANCIALTRANSACTIONLINEITEMID uniqueidentifier,
@MEMBERSHIPID uniqueidentifier,
@ORIGINALMEMBERSHIPTRANSACTIONID uniqueidentifier,
@CURRENTMEMBERSHIPTRANSACTIONID uniqueidentifier,
@NEWFINANCIALTRANSACTIONLINEITEMID uniqueidentifier;
--Remove old FINANCIALTRANSACTIONLINEITEM for which application type is changed and process new FINANCIALTRANSACTIONLINEITEM in membership program
declare REVENUEPROCESS cursor local fast_forward for
select FINANCIALTRANSACTIONID, MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID, MEMBERSHIPID, ORIGINALMEMBERSHIPTRANSACTIONID, CURRENTMEMBERSHIPTRANSACTIONID
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID
where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is not null
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID is not null
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID in (select CONSTITUENTID from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK);
open REVENUEPROCESS;
fetch next from REVENUEPROCESS into @FINANCIALTRANSACTIONID, @FINANCIALTRANSACTIONLINEITEMID, @MEMBERSHIPID, @ORIGINALMEMBERSHIPTRANSACTIONID, @CURRENTMEMBERSHIPTRANSACTIONID;
while (@@FETCH_STATUS = 0)
begin
set @NEWFINANCIALTRANSACTIONLINEITEMID = null;
select @NEWFINANCIALTRANSACTIONLINEITEMID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID
where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID is null
and FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;
if (@NEWFINANCIALTRANSACTIONLINEITEMID is not null)
begin
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set MEMBERSHIPID = @MEMBERSHIPID,
ORIGINALMEMBERSHIPTRANSACTIONID = @ORIGINALMEMBERSHIPTRANSACTIONID,
CURRENTMEMBERSHIPTRANSACTIONID = @CURRENTMEMBERSHIPTRANSACTIONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID = @NEWFINANCIALTRANSACTIONLINEITEMID;
delete
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID = @FINANCIALTRANSACTIONLINEITEMID;
end
fetch next from REVENUEPROCESS into @FINANCIALTRANSACTIONID, @FINANCIALTRANSACTIONLINEITEMID, @MEMBERSHIPID, @ORIGINALMEMBERSHIPTRANSACTIONID, @CURRENTMEMBERSHIPTRANSACTIONID;
end
close REVENUEPROCESS;
deallocate REVENUEPROCESS;
if object_id('tempdb..#CONTRIBUTIONPROCESS_LEVELSTOUPGRADE') is not null
drop table #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE;
create table #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE
(
CONSTITUENTID uniqueidentifier,
AMOUNTINCURRENCY money,
EFFECTIVEDATE date,
LEVELID uniqueidentifier,
TERMID uniqueidentifier,
AFTEREXPIRATION tinyint,
TERMTIMELENGTH int,
TERMLENGTHCODE tinyint,
MEMBERSHIPID uniqueidentifier,
MEMBERSHIPTRANSACTIONID uniqueidentifier,
MEMBERID uniqueidentifier,
EXPIRATIONDATE date
)
insert into #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE
(
CONSTITUENTID,
AMOUNTINCURRENCY,
EFFECTIVEDATE,
LEVELID,
TERMID,
AFTEREXPIRATION,
TERMTIMELENGTH,
TERMLENGTHCODE,
MEMBERSHIPID,
MEMBERSHIPTRANSACTIONID,
EXPIRATIONDATE
)
select
SUMMEDGIFT.CONSTITUENTID,
SUMMEDGIFT.AMOUNTINCURRENCY,
SUMMEDGIFT.EFFECTIVEDATE,
QUALIFIEDLEVEL.LEVELID,
QUALIFIEDLEVEL.TERMID,
SUMMEDGIFT.AFTEREXPIRATION,
SUMMEDGIFT.TERMTIMELENGTH,
SUMMEDGIFT.TERMLENGTHCODE,
SUMMEDGIFT.MEMBERSHIPID,
NEWID(),
SUMMEDGIFT.EXPIRATIONDATE
from (
select
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID,
sum(MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.AMOUNT) as AMOUNTINCURRENCY,
max(EFFECTIVEDATE) as EFFECTIVEDATE,
MEMBERSHIPLEVEL.ID as LEVELID,
MEMBERSHIPLEVEL.AFTEREXPIRATION,
MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
MEMBERSHIP.ID AS MEMBERSHIPID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPLEVELTERM.AMOUNT as MEMBERSHIPLEVELTERMAMOUNT,
MEMBERSHIP.EXPIRATIONDATE
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join dbo.MEMBER on MEMBER.CONSTITUENTID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID
where
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID in (select CONSTITUENTID from #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK)
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and (
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.ORIGINALMEMBERSHIPTRANSACTIONID is null
or MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.ORIGINALMEMBERSHIPTRANSACTIONID in (
select MEMBERSHIPTRANSACTION.ID
from dbo.MEMBERSHIPTRANSACTION
where MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
and MEMBERSHIPTRANSACTION.EXPIRATIONDATE = (
select max(MT.EXPIRATIONDATE)
from dbo.MEMBERSHIPTRANSACTION MT
where MT.MEMBERSHIPID = MEMBERSHIP.ID
)
)
)
and MEMBERSHIP.STATUSCODE <> 2
and (
@PROCESSDATE <
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE)
end
)
and (
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE <
case MEMBERSHIPLEVEL.BEFOREEXPIRATION
when 13 then -- Start of membership
case MEMBERSHIPLEVELTERM.TERMLENGTHCODE
when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1 * MEMBERSHIPLEVELTERM.TERMTIMELENGTH,MEMBERSHIP.EXPIRATIONDATE))
end
else dateadd(month,MEMBERSHIPLEVEL.BEFOREEXPIRATION*-1,MEMBERSHIP.EXPIRATIONDATE)
end
)
group by MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVELTERM.ID,
MEMBERSHIPLEVEL.AFTEREXPIRATION,
MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
MEMBERSHIP.ID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPLEVELTERM.AMOUNT,
MEMBERSHIP.EXPIRATIONDATE
) as SUMMEDGIFT
cross apply (
select top(1)
MEMBERSHIPLEVEL.ID as LEVELID,
MEMBERSHIPLEVELTERM.ID as TERMID
from dbo.MEMBERSHIPLEVEL
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and SUMMEDGIFT.AMOUNTINCURRENCY >= (MEMBERSHIPLEVELTERM.LOWAMOUNT)
and SUMMEDGIFT.AMOUNTINCURRENCY <= (MEMBERSHIPLEVELTERM.AMOUNT)
and MEMBERSHIPLEVEL.SEQUENCE > SUMMEDGIFT.SEQUENCE
and MEMBERSHIPLEVELTERM.TERMTIMELENGTH = SUMMEDGIFT.TERMTIMELENGTH
and MEMBERSHIPLEVELTERM.TERMLENGTHCODE = SUMMEDGIFT.TERMLENGTHCODE
and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1
order by MEMBERSHIPLEVEL.SEQUENCE desc
) as QUALIFIEDLEVEL
update dbo.MEMBERSHIP
set
MEMBERSHIPLEVELID = LEVELSTOUPGRADE.LEVELID,
MEMBERSHIPLEVELTERMID = LEVELSTOUPGRADE.TERMID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIP
inner join #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE as LEVELSTOUPGRADE on LEVELSTOUPGRADE.MEMBERSHIPID = MEMBERSHIP.ID
insert into dbo.MEMBERSHIPTRANSACTION
(
ID,
MEMBERSHIPID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
TRANSACTIONDATE,
EXPIRATIONDATE,
ACTIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID,
LEVELSTOUPGRADE.MEMBERSHIPID,
LEVELSTOUPGRADE.LEVELID,
LEVELSTOUPGRADE.TERMID,
@CURRENTEARLIESTDATETIME,
dbo.UFN_DATE_GETLATESTTIME(EXPIRATIONDATE),
2,
@CHANGEAGENTID,
@CHANGEAGENTID,
@PROCESSDATE,
@PROCESSDATE
from #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE as LEVELSTOUPGRADE
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
set
MEMBERSHIPID = LEVELSTOUPGRADE.MEMBERSHIPID,
ORIGINALMEMBERSHIPTRANSACTIONID = LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID,
CURRENTMEMBERSHIPTRANSACTIONID = LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE as LEVELSTOUPGRADE on LEVELSTOUPGRADE.CONSTITUENTID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.CONSTITUENTID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID is null
and LEVELSTOUPGRADE.EXPIRATIONDATE is not null
update dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE set
CURRENTMEMBERSHIPTRANSACTIONID = LEVELSTOUPGRADE.MEMBERSHIPTRANSACTIONID
from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
inner join #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE as LEVELSTOUPGRADE on LEVELSTOUPGRADE.MEMBERSHIPID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID
set @NUMPROCESSED += (select count(*) from #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE)
end --end @WHATHAPPENSIFTHEYGIVEMORECODE = 1
drop table #CONTRIBUTIONPROCESS_CONSTITUENTSTOCHECK
-- Create success and exception output tables and counts of each for the process status page.
set @OUTPUTSQL = N'insert into dbo.' + @OUTPUTTABLE + N' (MEMBERSHIPID, MEMBERSHIPPROGRAMNAME, MEMBERSHIPLEVELNAME, MEMBERSHIPTERMNAME, CONSTITUENTNAME)
select
LEVELSTORENEW.MEMBERSHIPID,
@PROGRAMNAME,
MEMBERSHIPLEVEL.NAME,
CAST(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(3)) + MEMBERSHIPLEVELTERM.TERMLENGTH,
NF.NAME
from #CONTRIBUTIONPROCESS_LEVELSTORENEW as LEVELSTORENEW
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = LEVELSTORENEW.MEMBERSHIPTERMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = LEVELSTORENEW.MEMBERSHIPLEVELID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = LEVELSTORENEW.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
union all
select
case when LEVELSTOADD.EXISTINGMEMBERSHIPID is null then LEVELSTOADD.MEMBERSHIPID
else LEVELSTOADD.EXISTINGMEMBERSHIPID
end as MEMBERSHIPID,
@PROGRAMNAME,
MEMBERSHIPLEVEL.NAME,
CAST(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(3)) + MEMBERSHIPLEVELTERM.TERMLENGTH,
NF.NAME
from #CONTRIBUTIONPROCESS_LEVELSTOADD as LEVELSTOADD
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = LEVELSTOADD.TERMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = LEVELSTOADD.LEVELID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = LEVELSTOADD.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
where LEVELSTOADD.EXPIRATIONDATE is not null '
if @WHATHAPPENSIFTHEYGIVEMORECODE = 1
begin
set @OUTPUTSQL = @OUTPUTSQL + 'union all
select
LEVELSTOUPGRADE.MEMBERSHIPID,
@PROGRAMNAME,
MEMBERSHIPLEVEL.NAME,
CAST(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(3)) + MEMBERSHIPLEVELTERM.TERMLENGTH,
NF.NAME
from #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE as LEVELSTOUPGRADE
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = LEVELSTOUPGRADE.TERMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = LEVELSTOUPGRADE.LEVELID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = LEVELSTOUPGRADE.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF '
end
exec sp_executesql @OUTPUTSQL, N'@PROGRAMNAME nvarchar(100)',@PROGRAMNAME=@PROGRAMNAME
drop table #CONTRIBUTIONPROCESS_LEVELSTOADD
drop table #CONTRIBUTIONPROCESS_LEVELSTORENEW
if @WHATHAPPENSIFTHEYGIVEMORECODE = 1
drop table #CONTRIBUTIONPROCESS_LEVELSTOUPGRADE;
end --end if @ISMULTIPLEREVENUETRANSACTIONSCOUNTED = 1
update dbo.MEMBERSHIPPROGRAM
set
LASTPROCESSEDON = @PROCESSDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @PROCESSDATE
where ID = @MEMBERSHIPPROGRAMID
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end