USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPLEVEL
The save procedure used by the add dataform template "Membership Level Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @NAME | nvarchar(50) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @TIERCODEID | uniqueidentifier | IN | Tier |
| @MEMBERSALLOWED | smallint | IN | Members allowed |
| @CARDSALLOWED | smallint | IN | Cards allowed |
| @CHILDRENALLOWED | smallint | IN | Children allowed |
| @BEFOREEXPIRATION | tinyint | IN | month(s) before expiration |
| @AFTEREXPIRATION | tinyint | IN | month(s) after expiration |
| @FORCEMANUALDOWNGRADES | bit | IN | Members must be downgraded manually |
| @TERMS | xml | IN | Terms |
| @MEMBERSHIPTYPES | xml | IN | Membership types |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPLEVEL
(
@ID uniqueidentifier = null output,
@MEMBERSHIPPROGRAMID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(50) = null,
@DESCRIPTION nvarchar(255) = null,
@TIERCODEID uniqueidentifier = null,
@MEMBERSALLOWED smallint = 1,
@CARDSALLOWED smallint = 0,
@CHILDRENALLOWED smallint = 0,
@BEFOREEXPIRATION tinyint = 0,
@AFTEREXPIRATION tinyint = 0,
@FORCEMANUALDOWNGRADES bit = 0,
@TERMS xml = null,
@MEMBERSHIPTYPES xml = null
)
as
set nocount on;
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CARDSALLOWED is null
set @CARDSALLOWED = 0
if @CHILDRENALLOWED is null
set @CHILDRENALLOWED = 0
declare @SEQUENCE as integer;
select @SEQUENCE = isnull(max(SEQUENCE), -1) + 1
from dbo.MEMBERSHIPLEVEL
where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @BASECURRENCYID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
select @BASECURRENCYID =
(select BASECURRENCYID from dbo.MEMBERSHIPPROGRAM where ID = @MEMBERSHIPPROGRAMID)
if (@ORGANIZATIONCURRENCYID <> @BASECURRENCYID)
begin
set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
end
begin try
if exists
(
select count(TYPEID)
from
(
select T.membershiptypes.value('(LEVELTYPECODEID)[1]', 'uniqueidentifier') TYPEID
from @MEMBERSHIPTYPES.nodes('/MEMBERSHIPTYPES/ITEM') T(MEMBERSHIPTYPES)
) Result
group by TYPEID
having count(*) > 1
)
raiserror('Membership types must be unique.', 13, 1);
insert into dbo.MEMBERSHIPLEVEL
(
ID,
NAME,
MEMBERSHIPPROGRAMID,
DESCRIPTION,
TIERCODEID,
MEMBERSALLOWED,
CARDSALLOWED,
CHILDRENALLOWED,
BEFOREEXPIRATION,
AFTEREXPIRATION,
FORCEMANUALDOWNGRADES,
ISACTIVE,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
SEQUENCE
)
values
(
@ID,
@NAME,
@MEMBERSHIPPROGRAMID,
@DESCRIPTION,
@TIERCODEID,
@MEMBERSALLOWED,
@CARDSALLOWED,
@CHILDRENALLOWED,
@BEFOREEXPIRATION,
@AFTEREXPIRATION,
@FORCEMANUALDOWNGRADES,
1,
@BASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@SEQUENCE
);
-- Save renewal rule history
insert into dbo.MEMBERSHIPLEVELRENEWALRULES
(
MEMBERSHIPLEVELID,
BEFOREEXPIRATION,
AFTEREXPIRATION,
STARTDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@BEFOREEXPIRATION,
@AFTEREXPIRATION,
'1/1/1753', -- use the smallest date
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
if not @TERMS is null
begin
set @TERMS = dbo.UFN_MEMBERSHIPLEVELTERMS_CONVERTAMOUNTSINXML(@TERMS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID)
exec dbo.USP_MEMBERSHIPLEVEL_GETTERMS_2_ADDFROMXML @ID, @TERMS, @CHANGEAGENTID, @CURRENTDATE;
end
else
raiserror('Please enter at least one term.', 13, 1);
if not @MEMBERSHIPTYPES is null
exec dbo.USP_MEMBERSHIPLEVEL_GETTYPES_ADDFROMXML @ID, @MEMBERSHIPTYPES, @CHANGEAGENTID, @CURRENTDATE;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0