USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMPAGEEXPRESSION

The load procedure used by the view dataform template "Membership Program Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	Name
@SITE	nvarchar(100)	INOUT	Site
@EXPIRESON	nvarchar(20)	INOUT	Expiration date
@ALLOWMULTIPLEMEMBERSHIPS	bit	INOUT	Allow multiple memberships
@DESCRIPTION	nvarchar(255)	INOUT	Description
@ISACTIVE	bit	INOUT	Active/Inactive
@GIFTAIDQUALIFICATIONSTATUS	nvarchar(25)	INOUT	Gift Aid status
@MEMBERSHIPRENEWALEFFORTPROCESSID	uniqueidentifier	INOUT	Process ID
@HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS	bit	INOUT	Has membership renewal effort process status
@ISBASICCMS	bit	INOUT	Approved for website
@NETCOMMUNITYLINKESTABLISHED	bit	INOUT	Blackbaud Internet Solutions link established?
@ALLOWADDONADULT	bit	INOUT	Allow additional members
@ADDONADULTPRICE	money	INOUT	Price
@ALLOWADDONGUEST	bit	INOUT	Allow guests
@ADDONGUESTPRICE	money	INOUT	Price
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@HASMEMBERSHIPS	bit	INOUT	Has memberships
@PROGRAMBASEDONCODE	tinyint	INOUT
@PROGRAMTYPECODE	tinyint	INOUT
@TERMCOUNT	tinyint	INOUT
@ISANNUALDUESBASED	bit	INOUT
@HASTYPES	bit	INOUT
@TEXTID	nvarchar(50)	INOUT
@ISTYPEPROGRAM	bit	INOUT
@WHEREISREVENUETRACKEDCODE	tinyint	INOUT
@MULTIPLETERMS	bit	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMPAGEEXPRESSION
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(100) = null output,
                    @SITE nvarchar(100) = null output,
                    @EXPIRESON nvarchar(20) = null output,
                    @ALLOWMULTIPLEMEMBERSHIPS bit = null output,
                    @DESCRIPTION nvarchar(255) = null output,
                    @ISACTIVE bit = null output,
                    @GIFTAIDQUALIFICATIONSTATUS nvarchar(25) = null output,
                    @MEMBERSHIPRENEWALEFFORTPROCESSID uniqueidentifier = null output,
                    @HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS bit = null output,
                    @ISBASICCMS bit = null output,
                    @NETCOMMUNITYLINKESTABLISHED bit = null output,
                    @ALLOWADDONADULT bit = null output,
                    @ADDONADULTPRICE money = null output,
                    @ALLOWADDONGUEST bit = null output,
                    @ADDONGUESTPRICE money = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @HASMEMBERSHIPS bit = null output,
                    @PROGRAMBASEDONCODE tinyint = null output,
                    @PROGRAMTYPECODE tinyint = null output,
                    @TERMCOUNT tinyint = null output,
                    @ISANNUALDUESBASED bit = null output,
                    @HASTYPES bit = null output,
                    @TEXTID nvarchar(50) = null output,
                    @ISTYPEPROGRAM bit = null output,
                    @WHEREISREVENUETRACKEDCODE tinyint  = null output,
                    @MULTIPLETERMS bit = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;
                    set @TEXTID = @ID

                    select 
                        @DATALOADED = 1,
                        @NAME = MEM.NAME,
                        @SITE = SITE.NAME,
                        @EXPIRESON = MEM.EXPIRESON,
                        @ALLOWMULTIPLEMEMBERSHIPS = MEM.ALLOWMULTIPLEMEMBERSHIPS,
                        @DESCRIPTION = MEM.DESCRIPTION,
                        @ISACTIVE = MEM.ISACTIVE,
                        @ISBASICCMS = ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID AS BIT), 0),
                        @NETCOMMUNITYLINKESTABLISHED = dbo.[UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED](),
                        @ALLOWADDONADULT = MEM.ALLOWADDONADULT,
                        @ADDONADULTPRICE = MEM.ADDONADULTPRICE,
                        @ALLOWADDONGUEST = MEM.ALLOWADDONGUEST,
                        @ADDONGUESTPRICE = MEM.ADDONGUESTPRICE,
                        @BASECURRENCYID = MEM.BASECURRENCYID,
                        @ISANNUALDUESBASED = case when (MEM.PROGRAMTYPECODE = 0 and MEM.PROGRAMBASEDONCODE = 0) then 1
                                    else 0
                                    end,
                        @ISTYPEPROGRAM = MEM.ISTYPEPROGRAM,
                        @WHEREISREVENUETRACKEDCODE = MEM.WHEREISREVENUETRACKEDCODE,
                        @MULTIPLETERMS = MEM.MULTIPLETERMS
                    from 
                        dbo.MEMBERSHIPPROGRAM MEM
                        left join dbo.MICROSITEPAGE 
                            on (MICROSITEPAGE.OBJECTID = MEM.ID)
                            and (MICROSITEPAGE.EXCLUDED = 0)
                    left join dbo.SITE 
                        on MEM.SITEID = SITE.ID
                    where MEM.ID = @ID;

                    --Gift Aid is for UK only

                    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
                    begin
                        set @GIFTAIDQUALIFICATIONSTATUS = dbo.UFN_MEMBERSHIPQUALIFICATIONSTATUS(@ID)
                    end

                    -- Membership mailing process ID

                    select @MEMBERSHIPRENEWALEFFORTPROCESSID = [ID]
                    from dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS
                        where MEMBERSHIPPROGRAMID = @ID

                    -- Membership renewal notice status

                    if exists (select MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS.ID
                                from dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS
              inner join dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS
                                    on MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS.PARAMETERSETID = MKTMEMBERSHIPRENEWALEFFORTPROCESS.ID
                                where MKTMEMBERSHIPRENEWALEFFORTPROCESS.MEMBERSHIPPROGRAMID = @ID
                    )
                        set @HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS = 1
                    else
                        set @HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS = 0

                    if exists(select 1 from dbo.MEMBERSHIP where MEMBERSHIP.MEMBERSHIPPROGRAMID = @ID)
                                  set @HASMEMBERSHIPS = 1
                              else
                                  set @HASMEMBERSHIPS = 0


          select
              @PROGRAMBASEDONCODE = MP.PROGRAMBASEDONCODE,
              @PROGRAMTYPECODE = MP.PROGRAMTYPECODE,
              @TERMCOUNT = MAX(MLT.SEQUENCE),
            @HASTYPES = CASE WHEN MLTYPES.MAXSEQUENCE > 0 THEN 1 ELSE 0 END
          from 
            dbo.MEMBERSHIPPROGRAM MP
                            join dbo.UFN_MEMBERSHIPPROGRAM_GETTERMS(@ID) MLT ON MLT.MEMBERSHIPPROGRAMID = MP.ID
            left outer join (
                              select 
                                max(MEMBERSHIPLEVELTYPE.SEQUENCE) MAXSEQUENCE, 
                                MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID 
                              from dbo.MEMBERSHIPLEVELTYPE 
                                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTYPE.LEVELID = MEMBERSHIPLEVEL.ID
                              where 
                                MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID
                              group by
                                MEMBERSHIPPROGRAMID
                            ) MLTYPES on MLTYPES.MEMBERSHIPPROGRAMID = MP.ID
          where
              MP.ID = @ID
          group by 
              MP.PROGRAMBASEDONCODE,
              MP.PROGRAMTYPECODE,
            CASE WHEN MLTYPES.MAXSEQUENCE > 0 THEN 1 ELSE 0 END

                    return 0;