USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAM_3
The save procedure used by the edit dataform template "Membership Program Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @SITEID | uniqueidentifier | IN | Site |
| @CARDFORMAT | nvarchar(255) | IN | Card format |
| @EXPIRESONCODE | tinyint | IN | Expiration date |
| @BACKDATEMEMBERSHIPS | bit | IN | |
| @ALLOWMULTIPLEMEMBERSHIPS | bit | IN | Allow multiple memberships |
| @CUTOFFDAY | tinyint | IN | Cutoff date |
| @CUTOFFDATEFORYEAR | char(4) | IN | Cutoff date |
| @EXPIRATIONDATES | xml | IN | Expiration dates |
| @LETTERTEMPLATEID | uniqueidentifier | IN | Letter template to use |
| @ALLOWADDONADULT | bit | IN | Allow additional members |
| @ADDONADULTPRICE | money | IN | Price |
| @ALLOWADDONGUEST | bit | IN | Allow guests |
| @ADDONGUESTPRICE | money | IN | Price |
| @REPORTCATALOGID | uniqueidentifier | IN | Report to use |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAM_3 (
@ID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(255),
@SITEID uniqueidentifier,
@CARDFORMAT nvarchar(255),
@EXPIRESONCODE tinyint,
@BACKDATEMEMBERSHIPS bit,
@ALLOWMULTIPLEMEMBERSHIPS bit,
@CUTOFFDAY tinyint,
@CUTOFFDATEFORYEAR char(4),
@EXPIRATIONDATES xml,
@LETTERTEMPLATEID uniqueidentifier,
@ALLOWADDONADULT bit,
@ADDONADULTPRICE money,
@ALLOWADDONGUEST bit,
@ADDONGUESTPRICE money,
@REPORTCATALOGID uniqueidentifier
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CUTOFFDAY is null
set @CUTOFFDAY = 0;
if @SITEID is null
begin
if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1
begin
raiserror('Site is required.',13,1)
return
end
end
if @EXPIRESONCODE != 4
set @EXPIRATIONDATES = null
else
begin
if @EXPIRATIONDATES is null
raiserror('Please enter at least one expiration date.', 13, 1);
end
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
declare @OLDNAME nvarchar(100);
select @OLDNAME = NAME from dbo.MEMBERSHIPPROGRAM where ID = @ID;
if @OLDNAME <> @NAME
begin
declare @QUERYNAME nvarchar(255);
select @QUERYNAME = 'V_QUERY_MEMBERSHIP_' + upper(REPLACE(CONVERT(nvarchar(36), @ID), '-', ''))
declare @ADHOCNAME nvarchar(255);
select @ADHOCNAME = dbo.[UFN_ADHOCQUERY_FIELDISINUSEBY](@QUERYNAME, '')
if len(@ADHOCNAME) > 0
begin
raiserror('Cannot modify the name value. The generated query is used by an ad hoc query as a filter or output field.', 13, 1);
return 1;
end
end
declare @BASECURRENCYID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @ORGANIZATIONADDONADULTPRICE money = @ADDONADULTPRICE;
declare @ORGANIZATIONADDONGUESTPRICE money = @ADDONGUESTPRICE;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
select
@BASECURRENCYID = BASECURRENCYID
from
dbo.MEMBERSHIPPROGRAM
where
ID = @ID;
if (@ORGANIZATIONCURRENCYID <> @BASECURRENCYID)
begin
set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
set @ORGANIZATIONADDONADULTPRICE = dbo.UFN_CURRENCY_CONVERT(@ADDONADULTPRICE, @ORGANIZATIONEXCHANGERATEID);
set @ORGANIZATIONADDONGUESTPRICE = dbo.UFN_CURRENCY_CONVERT(@ADDONGUESTPRICE, @ORGANIZATIONEXCHANGERATEID);
end
if (coalesce(@CARDFORMAT, '') <> '') and @LETTERTEMPLATEID is null
begin
declare @CARDCATALOGID uniqueidentifier
select top 1 @CARDCATALOGID = ID from dbo.REPORTCATALOG where NAME = @CARDFORMAT
if not @CARDCATALOGID is null
set @REPORTCATALOGID = @CARDCATALOGID
end
else
begin
set @REPORTCATALOGID = null
set @CARDFORMAT = ''
end
if (@REPORTCATALOGID is null) and (coalesce(@CARDFORMAT, '') <> '')
begin
select top 1 @REPORTCATALOGID = ID from dbo.REPORTCATALOG where NAME = @CARDFORMAT
end
-- handle updating the data
update dbo.MEMBERSHIPPROGRAM set
NAME = @NAME,
DESCRIPTION = @DESCRIPTION,
SITEID = @SITEID,
CARDFORMAT = @CARDFORMAT,
EXPIRESONCODE = @EXPIRESONCODE,
BACKDATEMEMBERSHIPS = @BACKDATEMEMBERSHIPS,
ALLOWMULTIPLEMEMBERSHIPS = @ALLOWMULTIPLEMEMBERSHIPS,
CUTOFFDAY = @CUTOFFDAY,
CUTOFFDATEFORYEAR = @CUTOFFDATEFORYEAR,
LETTERTEMPLATEID = @LETTERTEMPLATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
ALLOWADDONADULT = @ALLOWADDONADULT,
ADDONADULTPRICE = @ADDONADULTPRICE,
ALLOWADDONGUEST = @ALLOWADDONGUEST,
ADDONGUESTPRICE = @ADDONGUESTPRICE,
ORGANIZATIONADDONADULTPRICE = @ORGANIZATIONADDONADULTPRICE,
ORGANIZATIONADDONGUESTPRICE = @ORGANIZATIONADDONGUESTPRICE,
REPORTCATALOGID = @REPORTCATALOGID
where ID = @ID;
if not @EXPIRATIONDATES is null
exec dbo.USP_MEMBERSHIPPROGRAM_GETENDDATE_UPDATEFROMXML @ID, @EXPIRATIONDATES, @CHANGEAGENTID;
else
delete from dbo.MEMBERSHIPPROGRAMENDDATE where MEMBERSHIPPROGRAMID = @ID;
if @OLDNAME <> @NAME
begin
declare @QUERYID uniqueidentifier;
select @QUERYID = ID from dbo.QUERYVIEWCATALOG where OBJECTNAME = @QUERYNAME;
delete from dbo.QUERYVIEWRELATIONSHIP where RELATEDQUERYVIEWID = @QUERYID;
delete from dbo.QUERYVIEWRELATIONSHIP where ROOTQUERYVIEWID = @QUERYID;
delete from dbo.QUERYVIEWCATALOG where ID = @QUERYID;
exec dbo.USP_MEMBERSHIPPROGRAM_CREATEQUERY @ID, @NAME, @CHANGEAGENTID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;