USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPPROGRAM

The save procedure used by the add dataform template "Membership Program Add Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@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
@ALLOWMULTIPLEMEMBERSHIPS	bit	IN	Allow multiple memberships
@CARDFORMAT	nvarchar(255)	IN	Card format
@EXPIRESONCODE	tinyint	IN	Expiration date
@BACKDATEMEMBERSHIPS	bit	IN
@CUTOFFDAY	tinyint	IN	Cutoff date
@CUTOFFDATEFORYEAR	UDT_MONTHDAY	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_ADD_MEMBERSHIPPROGRAM
                    (
                        @ID uniqueidentifier = null output,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @NAME nvarchar(100) =null,
                        @DESCRIPTION nvarchar(255)=null,
                        @SITEID uniqueidentifier = null,
                        @ALLOWMULTIPLEMEMBERSHIPS bit = null,
                        @CARDFORMAT nvarchar(255) = null,
                        @EXPIRESONCODE tinyint = 1,
                        @BACKDATEMEMBERSHIPS bit = null,
                        @CUTOFFDAY tinyint = null,
                        @CUTOFFDATEFORYEAR dbo.UDT_MONTHDAY = 0000,
                        @EXPIRATIONDATES xml = null,
                        @LETTERTEMPLATEID uniqueidentifier = null,
            @ALLOWADDONADULT bit = null,
            @ADDONADULTPRICE money = null,
            @ALLOWADDONGUEST bit = null,
            @ADDONGUESTPRICE money = null,
            @REPORTCATALOGID uniqueidentifier = null
                    )
                    as
                        set nocount on;

                        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

                        if @id is null
                            set @ID = newid();

                        if @CHANGEAGENTID is null
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        if @CUTOFFDAY is null
                            set @CUTOFFDAY = 0;

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

                        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 = 
                            (select CURRENCYSET.BASECURRENCYID from dbo.CURRENCYSET where ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID))

                        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


                        begin try
                            insert into dbo.MEMBERSHIPPROGRAM
                            (
                                ID,
                                NAME,
                                DESCRIPTION,
                                SITEID,
                                ISACTIVE,
                                ALLOWMULTIPLEMEMBERSHIPS,
                                CARDFORMAT,
                                EXPIRESONCODE,
                                BACKDATEMEMBERSHIPS,
                                CUTOFFDAY,
                                CUTOFFDATEFORYEAR,
                                LETTERTEMPLATEID,
                                BASECURRENCYID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED,
                ALLOWADDONADULT,
                ADDONADULTPRICE,
                ALLOWADDONGUEST,
                ADDONGUESTPRICE,
                ORGANIZATIONEXCHANGERATEID,
                ORGANIZATIONADDONADULTPRICE,
                ORGANIZATIONADDONGUESTPRICE,
                REPORTCATALOGID
                            )
                            values
                            (
                                @ID,
                                @NAME,
                                @DESCRIPTION,
                                @SITEID,
                                1,
                                @ALLOWMULTIPLEMEMBERSHIPS,
                                @CARDFORMAT,
                                @EXPIRESONCODE,
                                @BACKDATEMEMBERSHIPS,
                                @CUTOFFDAY,
                                @CUTOFFDATEFORYEAR,
                                @LETTERTEMPLATEID,
                                @BASECURRENCYID,
                                @CHANGEAGENTID, 
                                @CHANGEAGENTID, 
                                @CURRENTDATE, 
                                @CURRENTDATE,
                0,
                0,
                0,
                0,
                @ORGANIZATIONEXCHANGERATEID,
                0,
                0,
                @REPORTCATALOGID
                            );


                            if not @EXPIRATIONDATES is null
                                exec dbo.USP_MEMBERSHIPPROGRAM_GETENDDATE_ADDFROMXML @ID, @EXPIRATIONDATES, @CHANGEAGENTID, @CURRENTDATE;

                            exec dbo.USP_MEMBERSHIPPROGRAM_CREATEQUERY @ID, @NAME, @CHANGEAGENTID;

                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                        return 0;