USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPLEVEL2_3
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(50) | IN | |
| @TABID | tinyint | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @BASECURRENCYID | uniqueidentifier | IN | |
| @MEMBERSHIPLEVELS | xml | IN | |
| @RECURRINGPRICES | xml | IN | |
| @PROGRAMTYPECODE | tinyint | IN | |
| @PROGRAMBASEDONCODE | tinyint | IN | |
| @ANNUALLYPAYMENTOPTION | bit | IN | |
| @SEMIANNUALLYPAYMENTOPTION | bit | IN | |
| @QUARTERLYPAYMENTOPTION | bit | IN | |
| @MONTHLYPAYMENTOPTION | bit | IN | |
| @MULTITERMPRICES | xml | IN | |
| @LIFETIMEPAYMENTOPTIONS | xml | IN | |
| @MULTIPLETERMSUNITS | xml | IN | |
| @ANNUALLYOPTIONACTIVE | bit | IN | |
| @SEMIANNUALLYOPTIONACTIVE | bit | IN | |
| @QUARTERLYOPTIONACTIVE | bit | IN | |
| @MONTHLYOPTIONACTIVE | bit | IN | |
| @ISTYPEPROGRAM | bit | IN | |
| @LIFETIMEINSTALLMENTPOSTSTATUSCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPLEVEL2_3 (
@ID nvarchar(50), --actually the tabid + '@' + recordid
@TABID tinyint,
@CHANGEAGENTID uniqueidentifier = null,
@BASECURRENCYID uniqueidentifier,
@MEMBERSHIPLEVELS xml,
@RECURRINGPRICES xml,
@PROGRAMTYPECODE tinyint,
@PROGRAMBASEDONCODE tinyint,
@ANNUALLYPAYMENTOPTION bit,
@SEMIANNUALLYPAYMENTOPTION bit,
@QUARTERLYPAYMENTOPTION bit,
@MONTHLYPAYMENTOPTION bit,
@MULTITERMPRICES xml,
@LIFETIMEPAYMENTOPTIONS xml,
@MULTIPLETERMSUNITS xml,
@ANNUALLYOPTIONACTIVE bit,
@SEMIANNUALLYOPTIONACTIVE bit,
@QUARTERLYOPTIONACTIVE bit,
@MONTHLYOPTIONACTIVE bit,
@ISTYPEPROGRAM bit,
@LIFETIMEINSTALLMENTPOSTSTATUSCODE tinyint
)
as
set nocount on;
declare @CONTEXTCACHE varbinary(128);
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @MEMBERSHIPPROGRAMID uniqueidentifier
declare @PARSEINDEX tinyint
set @PARSEINDEX = charindex('@',@ID)
set @TABID = substring(@ID,0,@PARSEINDEX)
set @MEMBERSHIPPROGRAMID = substring(@ID, @PARSEINDEX + 1, LEN(@ID) - @PARSEINDEX)
-----
-- Fetch codes currently in use by other levels of this membership program
-----
declare @RENEWALWINDOWREVENUETYPECODE tinyint
declare @NONRENEWALACTIONTYPECODE tinyint
declare @MEMBERSHIPTERMTYPECODE tinyint
declare @BEFOREEXPIRATION tinyint
declare @AFTEREXPIRATION tinyint
declare @RENEWALWINDOWSTARTTYPECODE tinyint
declare @RENEWALWINDOWSTARTTIMECODE tinyint
declare @RENEWALWINDOWSTARTINTERVALCODE tinyint
declare @RENEWALWINDOWSTARTCUTOFFDAY tinyint
declare @OBTAINLEVELCODE tinyint
declare @MEMBERSHIPLEVELRENEWAL1ID uniqueidentifier
declare @MEMBERSHIPLEVELRENEWAL2ID uniqueidentifier
declare @MEMBERSHIPLEVELRENEWAL3ID uniqueidentifier
declare @TERMTIMELENGTH tinyint
declare @TERMTIMELENGTHCODE tinyint
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier =
dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID,
dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @CURRENTDATE, null, null)
--declare @TERMCOUNT int
--select @TERMCOUNT = COUNT(*) from dbo.UFN_MEMBERSHIPPROGRAM_GETTERMS(@MEMBERSHIPPROGRAMID)
declare @HASMULTIPLETERMS bit
select @HASMULTIPLETERMS =
case
when --annual program with a single term
PROGRAMTYPECODE = 0 AND (MP.MULTIPLETERMS = 0) then 0
when --recurring program (has special terms and prices)
PROGRAMTYPECODE = 1 then 0
else 1 --All programs use multiple terms unless specified in above in the when clauses.
end
from dbo.MEMBERSHIPPROGRAM MP
where ID = @MEMBERSHIPPROGRAMID
select top 1
@RENEWALWINDOWREVENUETYPECODE = ML.RENEWALWINDOWREVENUETYPECODE,
@NONRENEWALACTIONTYPECODE = ML.NONRENEWALACTIONTYPECODE,
@MEMBERSHIPTERMTYPECODE = ML.MEMBERSHIPTERMTYPECODE,
@BEFOREEXPIRATION = ML.BEFOREEXPIRATION,
@AFTEREXPIRATION = ML.AFTEREXPIRATION,
@RENEWALWINDOWSTARTTYPECODE = ML.RENEWALWINDOWSTARTTYPECODE,
@RENEWALWINDOWSTARTTIMECODE = ML.RENEWALWINDOWSTARTTIMECODE,
@RENEWALWINDOWSTARTINTERVALCODE = ML.RENEWALWINDOWSTARTINTERVALCODE,
@RENEWALWINDOWSTARTCUTOFFDAY = ML.RENEWALWINDOWSTARTCUTOFFDAY,
@OBTAINLEVELCODE = ML.OBTAINLEVELCODE,
@MEMBERSHIPLEVELRENEWAL1ID = MEMBERSHIPLEVELRENEWAL1ID,
@MEMBERSHIPLEVELRENEWAL2ID = MEMBERSHIPLEVELRENEWAL2ID,
@MEMBERSHIPLEVELRENEWAL3ID = MEMBERSHIPLEVELRENEWAL3ID,
@TERMTIMELENGTH = MLT.TERMTIMELENGTH,
@TERMTIMELENGTHCODE = MLT.TERMLENGTHCODE
from
dbo.MEMBERSHIPLEVEL ML
join dbo.MEMBERSHIPLEVELTERM MLT on MLT.LEVELID = ML.ID
where ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
-- There are times when it might be possible for the above to return null values (or rather have no rows). This should be rare,
-- but the ISNULL handles cases where it happens in the wild. Note that if any of the default constraints
-- change on MEMBERSHIPLEVEL, these will need to change as well.
select
@RENEWALWINDOWREVENUETYPECODE = ISNULL(@RENEWALWINDOWREVENUETYPECODE, 0),
@NONRENEWALACTIONTYPECODE = ISNULL(@NONRENEWALACTIONTYPECODE, 4),
@MEMBERSHIPTERMTYPECODE = ISNULL(@MEMBERSHIPTERMTYPECODE, 0),
@BEFOREEXPIRATION = ISNULL(@BEFOREEXPIRATION, 0),
@AFTEREXPIRATION = ISNULL(@AFTEREXPIRATION, 0),
@RENEWALWINDOWSTARTTYPECODE = ISNULL(@RENEWALWINDOWSTARTTYPECODE, 2),
@RENEWALWINDOWSTARTTIMECODE = ISNULL(@RENEWALWINDOWSTARTTIMECODE, 0),
@RENEWALWINDOWSTARTINTERVALCODE = ISNULL(@RENEWALWINDOWSTARTINTERVALCODE, 0),
@RENEWALWINDOWSTARTCUTOFFDAY = ISNULL(@RENEWALWINDOWSTARTCUTOFFDAY, 1),
@OBTAINLEVELCODE = ISNULL(@OBTAINLEVELCODE, 0),
@TERMTIMELENGTH = ISNULL(@TERMTIMELENGTH,1),
@TERMTIMELENGTHCODE = ISNULL(@TERMTIMELENGTHCODE,1)
update dbo.MEMBERSHIPPROGRAM set ISTYPEPROGRAM = @ISTYPEPROGRAM
where MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID
-----
-- Fetch renewal settings currently in use by other levels of this membership program
-- Must change if these settings become customizeable by level.
-----
declare @NEWMEMBERSHIPLEVELIDS_TEMP table (ID uniqueidentifier)
insert into @NEWMEMBERSHIPLEVELIDS_TEMP
select
T.membershiplevel.value('(ID)[1]','uniqueidentifier')
from @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(membershiplevel)
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = T.membershiplevel.value('(ID)[1]','uniqueidentifier')
where MEMBERSHIPLEVEL.ID is null
declare @MEMBERSHIPLEVELRENEWAL_TEMP table
(ID uniqueidentifier, MEMBERSHIPLEVELID uniqueidentifier, INTERVALCODE tinyint, STATUSCODE tinyint, SEQUENCE tinyint);
insert into @MEMBERSHIPLEVELRENEWAL_TEMP
select
newid(), NEWLEVELS_TEMP.ID, INTERVALCODE, STATUSCODE, 1
from dbo.MEMBERSHIPLEVELRENEWAL
cross join @NEWMEMBERSHIPLEVELIDS_TEMP NEWLEVELS_TEMP
where MEMBERSHIPLEVELRENEWAL.ID = @MEMBERSHIPLEVELRENEWAL1ID
insert into @MEMBERSHIPLEVELRENEWAL_TEMP
select
newid(), NEWLEVELS_TEMP.ID, INTERVALCODE, STATUSCODE, 2
from dbo.MEMBERSHIPLEVELRENEWAL
cross join @NEWMEMBERSHIPLEVELIDS_TEMP NEWLEVELS_TEMP
where MEMBERSHIPLEVELRENEWAL.ID = @MEMBERSHIPLEVELRENEWAL2ID
insert into @MEMBERSHIPLEVELRENEWAL_TEMP
select
newid(), NEWLEVELS_TEMP.ID, INTERVALCODE, STATUSCODE, 3
from dbo.MEMBERSHIPLEVELRENEWAL
cross join @NEWMEMBERSHIPLEVELIDS_TEMP NEWLEVELS_TEMP
where MEMBERSHIPLEVELRENEWAL.ID = @MEMBERSHIPLEVELRENEWAL3ID
declare @DESIGNATIONTABLE table
(
DESIGNATIONID uniqueidentifier,
[PERCENT] decimal(20,4)
)
insert into @DESIGNATIONTABLE (DESIGNATIONID, [PERCENT])
select
MPD.DESIGNATIONID,
MPD.[PERCENT]
from
dbo.MEMBERSHIPPROGRAM MP
inner join dbo.MEMBERSHIPPROGRAMDESIGNATION MPD on MP.ID = MPD.MEMBERSHIPPROGRAMID
where
MP.ID = @MEMBERSHIPPROGRAMID
-----
-- Begin update
-----
begin try
-- Note (KellySu)
-- The merge statement still has problems when used in conjunction with SQL-XML.
-- Sometimes the merge + XML will fail with an error message 'Attempting to set a non-NULL-able column's value to NULL'.
-- MS PSS suggestion is to move the SQL-XML parsing out into a temporary table, and then
-- merge using the temporary table.
declare @levels table (
ID uniqueidentifier,
NAME nvarchar(100),
DESCRIPTION nvarchar(255),
TIERCODEID uniqueidentifier,
MEMBERSALLOWED smallint,
CHILDRENALLOWED smallint,
CARDSALLOWED smallint,
FORCEMANUALDOWNGRADES bit,
OBTAINLEVELCODE tinyint,
SEQUENCE int,
AMOUNT money,
CHANGEDBYID uniqueidentifier,
DATECHANGED datetime,
ISACTIVE bit,
MEMBERSHIPTERMTYPECODE tinyint
)
insert into @levels
select
ID = T.c.value('(ID)[1]','uniqueidentifier'),
NAME = T.c.value('(NAME)[1]','nvarchar(100)'),
DESCRIPTION = T.c.value('(DESCRIPTION)[1]','nvarchar(255)'),
TIERCODEID = T.c.value('(TIERCODEID)[1]','uniqueidentifier'),
MEMBERSALLOWED = T.c.value('(MEMBERSALLOWED)[1]','smallint'),
CHILDRENALLOWED = T.c.value('(CHILDRENALLOWED)[1]','smallint'),
CARDSALLOWED = T.c.value('(CARDSALLOWED)[1]','smallint'),
FORCEMANUALDOWNGRADES = T.c.value('(FORCEMANUALDOWNGRADES)[1]','bit'),
OBTAINLEVELCODE = T.c.value('(OBTAINLEVELCODE)[1]','tinyint'),
SEQUENCE = T.c.value('(SEQUENCE)[1]', 'int'),
AMOUNT = T.c.value('(AMOUNT[1])','money'),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = GETDATE(),
ISACTIVE = T.c.value('(ISACTIVE)[1]','bit'),
MEMBERSHIPTERMTYPECODE = @MEMBERSHIPTERMTYPECODE
from
@MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(c)
-----
-- Update level records
-----
;WITH
TARGET_TBL AS (select MEMBERSHIPLEVEL.* from dbo.MEMBERSHIPLEVEL where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID)
merge TARGET_TBL AS ML
using (
select ID,
NAME,
DESCRIPTION,
TIERCODEID,
MEMBERSALLOWED,
CHILDRENALLOWED,
CARDSALLOWED,
FORCEMANUALDOWNGRADES,
OBTAINLEVELCODE,
SEQUENCE,
AMOUNT,
CHANGEDBYID,
DATECHANGED,
ISACTIVE,
MEMBERSHIPTERMTYPECODE
from @levels
) AS src on ML.ID = src.ID
when matched then
update set
NAME = src.NAME,
DESCRIPTION = src.DESCRIPTION,
TIERCODEID = src.TIERCODEID,
MEMBERSALLOWED = src.MEMBERSALLOWED,
CHILDRENALLOWED = src.CHILDRENALLOWED,
CARDSALLOWED = src.CARDSALLOWED,
FORCEMANUALDOWNGRADES = src.FORCEMANUALDOWNGRADES,
OBTAINLEVELCODE = src.OBTAINLEVELCODE,
ISACTIVE = src.ISACTIVE,
MEMBERSHIPTERMTYPECODE = src.MEMBERSHIPTERMTYPECODE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = GETDATE(),
SEQUENCE = src.SEQUENCE
when not matched by target then
insert (
ID,
NAME,
MEMBERSHIPPROGRAMID,
DESCRIPTION,
TIERCODEID,
MEMBERSALLOWED,
CHILDRENALLOWED,
CARDSALLOWED,
ISACTIVE,
FORCEMANUALDOWNGRADES,
RENEWALWINDOWREVENUETYPECODE,
NONRENEWALACTIONTYPECODE,
MEMBERSHIPTERMTYPECODE,
BEFOREEXPIRATION,
AFTEREXPIRATION,
RENEWALWINDOWSTARTTYPECODE,
RENEWALWINDOWSTARTTIMECODE,
RENEWALWINDOWSTARTINTERVALCODE,
RENEWALWINDOWSTARTCUTOFFDAY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
SEQUENCE,
RECEIPTAMOUNT,
OBTAINLEVELCODE,
BASECURRENCYID)
values (
src.ID,
src.NAME,
@MEMBERSHIPPROGRAMID,
src.DESCRIPTION,
src.TIERCODEID,
src.MEMBERSALLOWED,
src.CHILDRENALLOWED,
src.CARDSALLOWED,
src.ISACTIVE,
src.FORCEMANUALDOWNGRADES,
@RENEWALWINDOWREVENUETYPECODE,
@NONRENEWALACTIONTYPECODE,
src.MEMBERSHIPTERMTYPECODE,
@BEFOREEXPIRATION,
@AFTEREXPIRATION,
@RENEWALWINDOWSTARTTYPECODE,
@RENEWALWINDOWSTARTTIMECODE,
@RENEWALWINDOWSTARTINTERVALCODE,
@RENEWALWINDOWSTARTCUTOFFDAY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
src.SEQUENCE,
case when @PROGRAMTYPECODE = 0 and @PROGRAMBASEDONCODE > 0 and @HASMULTIPLETERMS = 0 then 0 else src.AMOUNT end,
src.OBTAINLEVELCODE,
@BASECURRENCYID);
-----
-- Add contribution designation(s) if applicable
-----
if exists (select DESIGNATIONID from @DESIGNATIONTABLE) and exists (select ID from @NEWMEMBERSHIPLEVELIDS_TEMP)
begin
insert into dbo.MEMBERSHIPLEVELDESIGNATION
(
ID, MEMBERSHIPLEVELID, DESIGNATIONID,
[PERCENT], ADDEDBYID, CHANGEDBYID,
DATEADDED, DATECHANGED
)
select
newID(), NMID.ID, DESIGNATIONID,
[PERCENT], @CHANGEAGENTID, @CHANGEAGENTID,
@CURRENTDATE, @CURRENTDATE
from
@DESIGNATIONTABLE DT
join @NEWMEMBERSHIPLEVELIDS_TEMP NMID on 1 = 1 -- Join all records to assign all designations to all new levels
end
-----
-- Update level renewal window settings
-----
-- First make all the renewal rows for new levels
insert into dbo.MEMBERSHIPLEVELRENEWAL
(ID, MEMBERSHIPLEVELID, INTERVALCODE, STATUSCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID,
MEMBERSHIPLEVELID,
INTERVALCODE,
STATUSCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERSHIPLEVELRENEWAL_TEMP
-- Then update the levels we've added to reference their renewals
update dbo.MEMBERSHIPLEVEL
set
MEMBERSHIPLEVELRENEWAL1ID = (
select ID from @MEMBERSHIPLEVELRENEWAL_TEMP
where MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
and SEQUENCE = 1)
, MEMBERSHIPLEVELRENEWAL2ID = (
select ID from @MEMBERSHIPLEVELRENEWAL_TEMP
where MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
and SEQUENCE = 2)
, MEMBERSHIPLEVELRENEWAL3ID = (
select ID from @MEMBERSHIPLEVELRENEWAL_TEMP
where MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
and SEQUENCE = 3)
where
ID in (select MEMBERSHIPLEVELID from @MEMBERSHIPLEVELRENEWAL_TEMP)
-- Similarly update the renewal rules table
insert into dbo.MEMBERSHIPLEVELRENEWALRULES
(ID, MEMBERSHIPLEVELID, BEFOREEXPIRATION, AFTEREXPIRATION, STARTDATE, ENDDATE, MEMBERSHIPLEVELRENEWAL1ID, MEMBERSHIPLEVELRENEWAL2ID, MEMBERSHIPLEVELRENEWAL3ID, NONRENEWALACTIONTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
newid(),
MEMBERSHIPLEVEL.ID,
@BEFOREEXPIRATION,
@AFTEREXPIRATION,
getdate(),
null,
MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID,
MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID,
MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID,
@NONRENEWALACTIONTYPECODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.MEMBERSHIPLEVEL
where ID in (select MEMBERSHIPLEVELID from @MEMBERSHIPLEVELRENEWAL_TEMP)
declare @DELETEID uniqueidentifier
declare DELETE_CURSOR cursor for
select MEMBERSHIPLEVEL.ID from MEMBERSHIPLEVEL where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and MEMBERSHIPLEVEL.ID not in (select ML.ID from @levels ML);
open DELETE_CURSOR
fetch next from DELETE_CURSOR into @DELETEID
while @@FETCH_STATUS = 0
begin
exec dbo.USP_MEMBERSHIPLEVEL_DELETE @DELETEID, @CHANGEAGENTID
fetch next from DELETE_CURSOR into @DELETEID
end
close DELETE_CURSOR
deallocate DELETE_CURSOR
-----
-- Update level term records
-----
-- Annual
if @PROGRAMTYPECODE = 0
begin
--declare @HASMULTIPLETERMS bit
--select @HASMULTIPLETERMS = MULTIPLETERMS
--case
--annual program with a single term
--when PROGRAMTYPECODE = 0 AND (@MULTIPLETERMS = 1) then 0
--recurring program (has special terms and prices)
--when PROGRAMTYPECODE = 1 then 0
--All programs use multiple terms unless specified in above in the when clauses.
--else 1
--end
--from
--dbo.MEMBERSHIPPROGRAM MP
--where
--MP.ID = @MEMBERSHIPPROGRAMID
if (@HASMULTIPLETERMS = 0)
begin
-- Update existing records and insert new ones.
-- Note (KellySu)
-- The merge statement has a CTE called TARGET_TBL that is required for SQL 2008 R2 SP1 and below. Without this CTE,
-- the merge will sometimes fail with an error message 'Attempting to set a non-NULL-able column's value to NULL'. This is
-- a bug that is fixed in CU1 for SQL 2008 R2 (ref - http://support.microsoft.com/kb/981037, http://support.microsoft.com/kb/981355)
declare @terms table (
LEVELID uniqueidentifier,
AMOUNT money,
LOWAMOUNT money,
ORGANIZATIONAMOUNT money,
ORGANIZATIONLOWAMOUNT money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
CHANGEDBYID uniqueidentifier,
DATECHANGED datetime
)
insert into @terms
select
LEVELID = T.c.value('(ID)[1]','uniqueidentifier'),
AMOUNT = T.c.value('(AMOUNT)[1]','money'),
LOWAMOUNT = T.c.value('(LOWAMOUNT)[1]','money'),
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT)[1]','money'), @ORGANIZATIONEXCHANGERATEID),
ORGANIZATIONLOWAMOUNT = dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT)[1]','money'), @ORGANIZATIONEXCHANGERATEID),
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = GETDATE()
from
@MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(c)
;WITH
TARGET_TBL AS (select MLT.* from dbo.MEMBERSHIPLEVELTERM MLT JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID where ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID)
merge TARGET_TBL as MLT
using (
select
LEVELID,
AMOUNT,
LOWAMOUNT,
ORGANIZATIONAMOUNT,
ORGANIZATIONLOWAMOUNT,
ORGANIZATIONEXCHANGERATEID,
CHANGEDBYID,
DATECHANGED
from
@terms
) as src on src.LEVELID = MLT.LEVELID
when matched then
update set
AMOUNT = src.AMOUNT,
LOWAMOUNT = src.LOWAMOUNT,
ORGANIZATIONAMOUNT = src.ORGANIZATIONAMOUNT,
ORGANIZATIONLOWAMOUNT = src.ORGANIZATIONLOWAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = GETDATE()
when not matched by target then
insert (
ID,
LEVELID,
AMOUNT,
LOWAMOUNT,
TERMTIMELENGTH,
TERMLENGTHCODE,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONLOWAMOUNT
)
values (
newid(),
src.LEVELID,
src.AMOUNT,
src.LOWAMOUNT,
@TERMTIMELENGTH,
@TERMTIMELENGTHCODE,
@BASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ORGANIZATIONEXCHANGERATEID,
src.ORGANIZATIONAMOUNT,
src.ORGANIZATIONLOWAMOUNT );
end --if (@HASMULTIPLETERMS = 0)
else
begin
declare @multipleterms table (
ID uniqueidentifier,
LEVEL nvarchar(max),
LEVELID uniqueidentifier,
SEQUENCE int,
ISACTIVE bit,
AMOUNT money,
LOWAMOUNT money,
TERMTIMELENGTH int,
TERMLENGTHCODE int,
BASECURRENCYID uniqueidentifier,
CHANGEDBYID uniqueidentifier,
ADDEDBYID uniqueidentifier,
DATECHANGED datetime,
DATEADDED datetime,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
ORGANIZATIONAMOUNT money,
ORGANIZATIONLOWAMOUNT money
)
;with SEQUENCE_CTE(SEQUENCE) as (
select n
from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as s(n)
)
insert into @multipleterms
select
ID = case S.SEQUENCE
when 0 then T.c.value('(TERMID1)[1]','uniqueidentifier')
when 1 then T.c.value('(TERMID2)[1]','uniqueidentifier')
when 2 then T.c.value('(TERMID3)[1]','uniqueidentifier')
when 3 then T.c.value('(TERMID4)[1]','uniqueidentifier')
when 4 then T.c.value('(TERMID5)[1]','uniqueidentifier')
when 5 then T.c.value('(TERMID6)[1]','uniqueidentifier')
when 6 then T.c.value('(TERMID7)[1]','uniqueidentifier')
when 7 then T.c.value('(TERMID8)[1]','uniqueidentifier')
when 8 then T.c.value('(TERMID9)[1]','uniqueidentifier')
when 9 then T.c.value('(TERMID10)[1]','uniqueidentifier')
end,
LEVEL = T.c.value('(LEVEL)[1]','nvarchar(max)'),
LEVELID = T.c.value('(LEVELID)[1]','uniqueidentifier'),
SEQUENCE = S.SEQUENCE,
ISACTIVE = case S.SEQUENCE
when 0 then isnull(T.c.value('(TERMACTIVE1)[1]','bit'),0)
when 1 then isnull(T.c.value('(TERMACTIVE2)[1]','bit'),0)
when 2 then isnull(T.c.value('(TERMACTIVE3)[1]','bit'),0)
when 3 then isnull(T.c.value('(TERMACTIVE4)[1]','bit'),0)
when 4 then isnull(T.c.value('(TERMACTIVE5)[1]','bit'),0)
when 5 then isnull(T.c.value('(TERMACTIVE6)[1]','bit'),0)
when 6 then isnull(T.c.value('(TERMACTIVE7)[1]','bit'),0)
when 7 then isnull(T.c.value('(TERMACTIVE8)[1]','bit'),0)
when 8 then isnull(T.c.value('(TERMACTIVE9)[1]','bit'),0)
when 9 then isnull(T.c.value('(TERMACTIVE10)[1]','bit'),0)
end,
AMOUNT = case S.SEQUENCE
when 0 then isnull(T.c.value('(AMOUNT1)[1]','money'),0)
when 1 then isnull(T.c.value('(AMOUNT2)[1]','money'),0)
when 2 then isnull(T.c.value('(AMOUNT3)[1]','money'),0)
when 3 then isnull(T.c.value('(AMOUNT4)[1]','money'),0)
when 4 then isnull(T.c.value('(AMOUNT5)[1]','money'),0)
when 5 then isnull(T.c.value('(AMOUNT6)[1]','money'),0)
when 6 then isnull(T.c.value('(AMOUNT7)[1]','money'),0)
when 7 then isnull(T.c.value('(AMOUNT8)[1]','money'),0)
when 8 then isnull(T.c.value('(AMOUNT9)[1]','money'),0)
when 9 then isnull(T.c.value('(AMOUNT10)[1]','money'),0)
end,
LOWAMOUNT = case S.SEQUENCE
when 0 then isnull(T.c.value('(LOWAMOUNT1)[1]','money'),0)
when 1 then isnull(T.c.value('(LOWAMOUNT2)[1]','money'),0)
when 2 then isnull(T.c.value('(LOWAMOUNT3)[1]','money'),0)
when 3 then isnull(T.c.value('(LOWAMOUNT4)[1]','money'),0)
when 4 then isnull(T.c.value('(LOWAMOUNT5)[1]','money'),0)
when 5 then isnull(T.c.value('(LOWAMOUNT6)[1]','money'),0)
when 6 then isnull(T.c.value('(LOWAMOUNT7)[1]','money'),0)
when 7 then isnull(T.c.value('(LOWAMOUNT8)[1]','money'),0)
when 8 then isnull(T.c.value('(LOWAMOUNT9)[1]','money'),0)
when 9 then isnull(T.c.value('(LOWAMOUNT10)[1]','money'),0)
end,
TERMTIMELENGTH = O.c.value('(TERMTIMELENGTH)[1]','integer'),
TERMLENGTHCODE = O.c.value('(TERMLENGTH)[1]','integer'),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
ADDEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DATEADDED = @CURRENTDATE,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT = case s.sequence
when 0 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT1)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 1 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT2)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 2 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT3)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 3 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT4)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 4 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT5)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 5 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT6)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 6 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT7)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 7 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT8)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 8 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT9)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 9 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT10)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
end,
ORGANIZATIONLOWAMOUNT = case s.sequence
when 0 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT1)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 1 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT2)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 2 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT3)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 3 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT4)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 4 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT5)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 5 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT6)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 6 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT7)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 7 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT8)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 8 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT9)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
when 9 then isnull(dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWAMOUNT10)[1]','money'), @ORGANIZATIONEXCHANGERATEID),0)
end
--ISACTIVE = O.c.value('(ISACTIVE)[1]','bit')
from @MULTITERMPRICES.nodes('/MULTITERMPRICES/ITEM') T(c),
@MULTIPLETERMSUNITS.nodes('/MULTIPLETERMSUNITS/ITEM') O(c)
JOIN SEQUENCE_CTE S ON O.c.value('(TERMSEQUENCE)[1]','integer') = s.sequence
;with TARGET_TBL AS (select MLT.* from dbo.MEMBERSHIPLEVELTERM MLT JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID where ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID),
SRC_CTE AS (select * from @multipleterms)
merge TARGET_TBL as MLT
using SRC_CTE
on SRC_CTE.ID = MLT.ID
when matched then
update set
AMOUNT = SRC_CTE.AMOUNT,
LOWAMOUNT = SRC_CTE.LOWAMOUNT,
BASECURRENCYID = SRC_CTE.BASECURRENCYID,
CHANGEDBYID = SRC_CTE.CHANGEDBYID,
DATECHANGED = SRC_CTE.DATECHANGED,
ORGANIZATIONEXCHANGERATEID = SRC_CTE.ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT = SRC_CTE.ORGANIZATIONAMOUNT,
ORGANIZATIONLOWAMOUNT = SRC_CTE.ORGANIZATIONLOWAMOUNT,
TERMTIMELENGTH = SRC_CTE.TERMTIMELENGTH,
TERMLENGTHCODE = SRC_CTE.TERMLENGTHCODE,
SEQUENCE = SRC_CTE.SEQUENCE,
ISACTIVE = SRC_CTE.ISACTIVE
when not matched by target then
insert (
ID,
LEVELID,
TERMLENGTHCODE,
TERMTIMELENGTH,
SEQUENCE,
AMOUNT,
LOWAMOUNT,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONLOWAMOUNT,
ISACTIVE)
values (
newid(),
SRC_CTE.LEVELID,
SRC_CTE.TERMLENGTHCODE,
SRC_CTE.TERMTIMELENGTH,
SRC_CTE.SEQUENCE,
SRC_CTE.AMOUNT,
SRC_CTE.LOWAMOUNT,
SRC_CTE.BASECURRENCYID,
SRC_CTE.ADDEDBYID,
SRC_CTE.CHANGEDBYID,
SRC_CTE.DATEADDED,
SRC_CTE.DATECHANGED,
SRC_CTE.ORGANIZATIONEXCHANGERATEID,
SRC_CTE.ORGANIZATIONAMOUNT,
SRC_CTE.ORGANIZATIONLOWAMOUNT,
SRC_CTE.ISACTIVE
)
when not matched by source then delete
;
--If the new terms contain a month term and the expiration date settings are not valid for monthly, change it to the valid setting(rolling year).
if exists ( select 1 from @multipleterms where TERMLENGTHCODE = 0) and exists (select 1 from dbo.MEMBERSHIPPROGRAMENDDATE where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID)
begin
set @CONTEXTCACHE = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.MEMBERSHIPPROGRAMENDDATE
where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID;
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
update dbo.MEMBERSHIPLEVEL set
MEMBERSHIPTERMTYPECODE = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID;
update dbo.MEMBERSHIPPROGRAM set
EXPIRESONCODE = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @MEMBERSHIPPROGRAMID;
end
end
end --if @PROGRAMTYPECODE = 0
-- Recurring
if @PROGRAMTYPECODE = 1
begin
declare @recurring table (
ID uniqueidentifier,
LEVELID uniqueidentifier,
SEQUENCE integer,
PAYMENTOPTION integer,
AMOUNT money,
BASECURRENCYID uniqueidentifier,
CHANGEDBYID uniqueidentifier,
ADDEDBYID uniqueidentifier,
DATECHANGED datetime,
DATEADDED datetime,
ISACTIVE bit,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
ORGANIZATIONAMOUNT money
)
;with recurringoptions as (
select PAYMENTOPTION, ISACTIVE, PRICESEQ = (ROW_NUMBER() over(order by PAYMENTOPTION) - 1)
from (values
((select 0 where @ANNUALLYPAYMENTOPTION = 1), (select @ANNUALLYOPTIONACTIVE where @ANNUALLYPAYMENTOPTION = 1)),
((select 1 where @SEMIANNUALLYPAYMENTOPTION = 1), (select @SEMIANNUALLYOPTIONACTIVE where @SEMIANNUALLYPAYMENTOPTION = 1)),
((select 2 where @QUARTERLYPAYMENTOPTION = 1), (select @QUARTERLYOPTIONACTIVE where @QUARTERLYPAYMENTOPTION = 1)),
((select 3 where @MONTHLYPAYMENTOPTION = 1), (select @MONTHLYOPTIONACTIVE where @MONTHLYPAYMENTOPTION = 1))) AS S(PAYMENTOPTION, ISACTIVE)
where PAYMENTOPTION IS NOT NULL
)
insert into @recurring
select *
from (
select
ID = case opt.PAYMENTOPTION
when 0 THEN T.c.value('(ANNUALLYTERMID)[1]','uniqueidentifier')
when 1 THEN T.c.value('(SEMIANNUALLYTERMID)[1]','uniqueidentifier')
when 2 THEN T.c.value('(QUARTERLYTERMID)[1]','uniqueidentifier')
when 3 THEN T.c.value('(MONTHLYTERMID)[1]','uniqueidentifier')
end,
LEVELID = T.c.value('(LEVELID)[1]','uniqueidentifier'),
SEQUENCE = opt.PRICESEQ,
PAYMENTOPTION = opt.PAYMENTOPTION,
AMOUNT = case opt.PAYMENTOPTION
when 0 THEN T.c.value('(ANNUALLYPRICE)[1]','money')
when 1 THEN T.c.value('(SEMIANNUALLYPRICE)[1]','money')
when 2 THEN T.c.value('(QUARTERLYPRICE)[1]','money')
when 3 THEN T.c.value('(MONTHLYPRICE)[1]','money')
end,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
ADDEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DATEADDED = @CURRENTDATE,
ISACTIVE = opt.ISACTIVE,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT =
CASE opt.PAYMENTOPTION
WHEN 0 THEN dbo.UFN_CURRENCY_CONVERT(T.c.value('(ANNUALLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
WHEN 1 THEN dbo.UFN_CURRENCY_CONVERT(T.c.value('(SEMIANNUALLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
WHEN 2 THEN dbo.UFN_CURRENCY_CONVERT(T.c.value('(QUARTERLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
WHEN 3 THEN dbo.UFN_CURRENCY_CONVERT(T.c.value('(MONTHLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
END
from
@RECURRINGPRICES.nodes('/RECURRINGPRICES/ITEM') T(c),
recurringoptions opt) as s --cross product quickly produces one row per payment option
where s.AMOUNT is not null
;with
TARGET_TBL AS (select MLT.* from dbo.MEMBERSHIPLEVELTERM MLT JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID where ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID),
SRC_CTE AS (select * from @recurring)
merge TARGET_TBL as MLT
using SRC_CTE
on (SRC_CTE.ID = MLT.ID and SRC_CTE.AMOUNT is not null and SRC_CTE.PAYMENTOPTION IS NOT NULL)
when matched then
update set
AMOUNT = SRC_CTE.AMOUNT,
BASECURRENCYID = SRC_CTE.BASECURRENCYID,
CHANGEDBYID = SRC_CTE.CHANGEDBYID,
DATECHANGED = SRC_CTE.DATECHANGED,
ORGANIZATIONEXCHANGERATEID = SRC_CTE.ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT = SRC_CTE.ORGANIZATIONAMOUNT,
ISACTIVE = SRC_CTE.ISACTIVE
when not matched by target then
insert (
ID,
LEVELID,
RECURRINGPAYMENTOPTIONCODE,
SEQUENCE,
AMOUNT,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ISACTIVE)
values (
newid(),
SRC_CTE.LEVELID,
SRC_CTE.PAYMENTOPTION,
SRC_CTE.SEQUENCE,
SRC_CTE.AMOUNT,
SRC_CTE.BASECURRENCYID,
SRC_CTE.ADDEDBYID,
SRC_CTE.CHANGEDBYID,
SRC_CTE.DATEADDED,
SRC_CTE.DATECHANGED,
SRC_CTE.ORGANIZATIONEXCHANGERATEID,
SRC_CTE.ORGANIZATIONAMOUNT,
SRC_CTE.ISACTIVE);
end --if @PROGRAMTYPECODE = 1
-- Lifetime
if @PROGRAMTYPECODE = 2
begin
--Update installment post status
if @LIFETIMEINSTALLMENTPOSTSTATUSCODE is not null
begin
update dbo.MEMBERSHIPPROGRAM
set
INSTALLMENTPOSTSTATUSCODE = @LIFETIMEINSTALLMENTPOSTSTATUSCODE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @MEMBERSHIPPROGRAMID
end
declare @lifetime table (
ID uniqueidentifier,
LEVEL nvarchar(max),
LEVELID uniqueidentifier,
SEQUENCE int,
AMOUNT money,
ISACTIVE bit,
LIFETIMEPAYMENTOPTIONCODE integer,
LIFETIMENUMBEROFPAYMENTS integer,
BASECURRENCYID uniqueidentifier,
CHANGEDBYID uniqueidentifier,
ADDEDBYID uniqueidentifier,
DATECHANGED datetime,
DATEADDED datetime,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
ORGANIZATIONAMOUNT money
)
;with SEQUENCE_CTE(SEQUENCE) as (
select n
from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as s(n)
)
insert into @lifetime
select
ID = case S.SEQUENCE
when 0 then T.c.value('(TERMID1)[1]','uniqueidentifier')
when 1 then T.c.value('(TERMID2)[1]','uniqueidentifier')
when 2 then T.c.value('(TERMID3)[1]','uniqueidentifier')
when 3 then T.c.value('(TERMID4)[1]','uniqueidentifier')
when 4 then T.c.value('(TERMID5)[1]','uniqueidentifier')
when 5 then T.c.value('(TERMID6)[1]','uniqueidentifier')
when 6 then T.c.value('(TERMID7)[1]','uniqueidentifier')
when 7 then T.c.value('(TERMID8)[1]','uniqueidentifier')
when 8 then T.c.value('(TERMID9)[1]','uniqueidentifier')
when 9 then T.c.value('(TERMID10)[1]','uniqueidentifier')
end,
LEVEL = T.c.value('(LEVEL)[1]','nvarchar(max)'),
LEVELID = T.c.value('(LEVELID)[1]','uniqueidentifier'),
SEQUENCE = S.SEQUENCE,
AMOUNT = case S.SEQUENCE
when 0 then T.c.value('(AMOUNT1)[1]','money')
when 1 then T.c.value('(AMOUNT2)[1]','money')
when 2 then T.c.value('(AMOUNT3)[1]','money')
when 3 then T.c.value('(AMOUNT4)[1]','money')
when 4 then T.c.value('(AMOUNT5)[1]','money')
when 5 then T.c.value('(AMOUNT6)[1]','money')
when 6 then T.c.value('(AMOUNT7)[1]','money')
when 7 then T.c.value('(AMOUNT8)[1]','money')
when 8 then T.c.value('(AMOUNT9)[1]','money')
when 9 then T.c.value('(AMOUNT10)[1]','money')
end,
ISACTIVE = case S.SEQUENCE
when 0 then T.c.value('(TERMACTIVE1)[1]','bit')
when 1 then T.c.value('(TERMACTIVE2)[1]','bit')
when 2 then T.c.value('(TERMACTIVE3)[1]','bit')
when 3 then T.c.value('(TERMACTIVE4)[1]','bit')
when 4 then T.c.value('(TERMACTIVE5)[1]','bit')
when 5 then T.c.value('(TERMACTIVE6)[1]','bit')
when 6 then T.c.value('(TERMACTIVE7)[1]','bit')
when 7 then T.c.value('(TERMACTIVE8)[1]','bit')
when 8 then T.c.value('(TERMACTIVE9)[1]','bit')
when 9 then T.c.value('(TERMACTIVE10)[1]','bit')
end,
LIFETIMEPAYMENTOPTIONCODE = O.c.value('(PAYMENTOPTION)[1]','integer'),
LIFETIMENUMBEROFPAYMENTS = O.c.value('(NUMBEROFPAYMENTS)[1]','integer'),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
ADDEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DATEADDED = @CURRENTDATE,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT = case s.sequence
when 0 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT1)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 1 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT2)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 2 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT3)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 3 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT4)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 4 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT5)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 5 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT6)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 6 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT7)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 7 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT8)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 8 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT9)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
when 9 then dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT10)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
end
from @MULTITERMPRICES.nodes('/MULTITERMPRICES/ITEM') T(c),
@LIFETIMEPAYMENTOPTIONS.nodes('/LIFETIMEPAYMENTOPTIONS/ITEM') O(c)
JOIN SEQUENCE_CTE S ON O.c.value('(TERMSEQUENCE)[1]','integer') = s.sequence
begin
set @CONTEXTCACHE = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete dbo.[MEMBERSHIPLEVELTERM]
from dbo.[MEMBERSHIPLEVELTERM] as MLT
INNER JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID
where ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
AND MLT.ID not in (select ID from @lifetime)
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
end
;with TARGET_TBL AS (select MLT.* from dbo.MEMBERSHIPLEVELTERM MLT JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID where ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID),
SRC_CTE AS (select * from @lifetime)
merge TARGET_TBL as MLT
using SRC_CTE
on SRC_CTE.ID = MLT.ID
when matched then
update set
AMOUNT = SRC_CTE.AMOUNT,
BASECURRENCYID = SRC_CTE.BASECURRENCYID,
CHANGEDBYID = SRC_CTE.CHANGEDBYID,
DATECHANGED = SRC_CTE.DATECHANGED,
ORGANIZATIONEXCHANGERATEID = SRC_CTE.ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT = SRC_CTE.ORGANIZATIONAMOUNT,
LIFETIMEPAYMENTOPTIONCODE = SRC_CTE.LIFETIMEPAYMENTOPTIONCODE,
LIFETIMENUMBEROFPAYMENTS = SRC_CTE.LIFETIMENUMBEROFPAYMENTS,
SEQUENCE = SRC_CTE.SEQUENCE,
ISACTIVE = SRC_CTE.ISACTIVE
when not matched by target then
insert (
ID,
LEVELID,
LIFETIMEPAYMENTOPTIONCODE,
LIFETIMENUMBEROFPAYMENTS,
SEQUENCE,
AMOUNT,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ISACTIVE)
values (
newid(),
SRC_CTE.LEVELID,
SRC_CTE.LIFETIMEPAYMENTOPTIONCODE,
SRC_CTE.LIFETIMENUMBEROFPAYMENTS,
SRC_CTE.SEQUENCE,
SRC_CTE.AMOUNT,
SRC_CTE.BASECURRENCYID,
SRC_CTE.ADDEDBYID,
SRC_CTE.CHANGEDBYID,
SRC_CTE.DATEADDED,
SRC_CTE.DATECHANGED,
SRC_CTE.ORGANIZATIONEXCHANGERATEID,
SRC_CTE.ORGANIZATIONAMOUNT,
SRC_CTE.ISACTIVE
)
;
end --if @PROGRAMTYPECODE = 2
update dbo.MEMBERSHIPLEVELBENEFIT set
QUANTITY = T.c.value('(MEMBERSALLOWED)[1]','smallint')
from dbo.MEMBERSHIPLEVELBENEFIT
inner join @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(c) on MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = T.c.value('(ID)[1]','uniqueidentifier')
where MEMBERSHIPLEVELBENEFIT.NUMBERTOOFFERCODE = 0
and MEMBERSHIPLEVELBENEFIT.QUANTITY <> T.c.value('(MEMBERSALLOWED)[1]','smallint')
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;