USP_DATALIST_MEMBERSHIPPROGRAM

Displays a list of Membership Programs.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@INCLUDEINACTIVE	bit	IN	Include inactive programs
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM
            (
                @CURRENTAPPUSERID uniqueidentifier = null,
                @INCLUDEINACTIVE bit = 0,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null
            )
            as
                set nocount on;

                select 
                    MEMBERSHIPPROGRAM.ID,
                    MEMBERSHIPPROGRAM.NAME,
                    MEMBERSHIPPROGRAM.DESCRIPTION,
                    SITE.NAME as SITE,
                    MEMBERSHIPPROGRAM.EXPIRESON,
                    MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS,
                    MEMBERSHIPPROGRAM.ISACTIVE,
                    MEMBERSHIPPROGRAM.CUTOFFDAY,
                    MEMBERSHIPPROGRAM.CUTOFFDATEFORYEAR,
                    MEMBERSHIPPROGRAM.EXPIRESONCODE,
          MEMBERSHIPPROGRAM.BASECURRENCYID,
          MEMBERSHIPPROGRAM.ALLOWADDONADULT,
          MEMBERSHIPPROGRAM.ADDONADULTPRICE,
          MEMBERSHIPPROGRAM.ALLOWADDONGUEST,
          MEMBERSHIPPROGRAM.ADDONGUESTPRICE,
          coalesce(MEMBERSHIPPROGRAMCONTRIBUTION.WHATDATETOCALCULATEEXPIRATIONDATECODE, 0) as CONTRIBUTIONBASEDEXPIRESONCODE,
          MEMBERSHIPPROGRAM.PROGRAMTYPECODE,
          MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE,
          MEMBERSHIPPROGRAM.WHEREISREVENUETRACKEDCODE
                from 
                    dbo.MEMBERSHIPPROGRAM
                left join dbo.SITE
                    on SITE.ID = MEMBERSHIPPROGRAM.SITEID
                left outer join dbo.MEMBERSHIPPROGRAMCONTRIBUTION
                    on MEMBERSHIPPROGRAMCONTRIBUTION.ID = MEMBERSHIPPROGRAM.ID
                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MEMBERSHIPPROGRAM].[SITEID] or (SITEID is null and [MEMBERSHIPPROGRAM].[SITEID] is null)))
                    and (
                        @SITEFILTERMODE = 0 
                            or MEMBERSHIPPROGRAM.SITEID in(
                                    select SITEID
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
                                )
                    )
                    and (
                        MEMBERSHIPPROGRAM.ISACTIVE = 1 
                            or @INCLUDEINACTIVE = 1
                    )