USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMGENERAL

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@NAME	nvarchar(100)	INOUT
@DESCRIPTION	nvarchar(255)	INOUT
@TERM	xml	INOUT
@TERMCOUNT	int	INOUT
@CATEGORY	nvarchar(50)	INOUT
@SITE	nvarchar(100)	INOUT
@PROGRAMTYPE	nvarchar(50)	INOUT
@PROGRAMBASEDON	nvarchar(50)	INOUT
@GIFTAIDQUALIFICATIONSTATUS	nvarchar(25)	INOUT
@ALLOWMULTIPLEMEMBERSHIPS	bit	INOUT
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT
@WEBFORMSONLINE	bit	INOUT
@PROGRAMTYPECODE	tinyint	INOUT
@PROGRAMBASEDONCODE	tinyint	INOUT
@WHEREISREVENUETRACKEDCODE	tinyint	INOUT
Definition

Copy

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMGENERAL
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(100) = null output,
                    @DESCRIPTION nvarchar(255) = null output,
                    @TERM xml = null output,
                    @TERMCOUNT integer = null output,
                    @CATEGORY nvarchar(50) = null output,
                    @SITE nvarchar(100) = null output,
                    @PROGRAMTYPE nvarchar(50) = null output,
                    @PROGRAMBASEDON nvarchar(50) = null output,
                    @GIFTAIDQUALIFICATIONSTATUS nvarchar(25) = null output,
                    @ALLOWMULTIPLEMEMBERSHIPS bit = null output,
                    @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                    @WEBFORMSONLINE bit = null output,
                    @PROGRAMTYPECODE tinyint = null output,
                    @PROGRAMBASEDONCODE tinyint = null output,
                    @WHEREISREVENUETRACKEDCODE tinyint = null output
                )
                as
                    set nocount on;

                    -- be sure to set this, in case the select returns no rows
                    set @DATALOADED = 0;

                    set @TERM = (
                        SELECT
                          CAST(MLT.TERMTIMELENGTH as nvarchar(10)) + ' ' + MLT.TERMLENGTH AS TERMDESC,
                          MP.ID AS MEMBERSHIPPROGRAMID
                        FROM dbo.MEMBERSHIPPROGRAM MP
                        INNER JOIN dbo.MEMBERSHIPLEVEL ML on MP.ID = ML.MEMBERSHIPPROGRAMID
                        INNER JOIN dbo.MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID
                        WHERE MP.ID = @ID
                        group by MLT.TERMLENGTH, MLT.TERMTIMELENGTH, MP.ID
                        order by MLT.TERMLENGTH, MLT.TERMTIMELENGTH, MP.ID
                        for xml raw('ITEM'),type,elements,root('TERM'),BINARY BASE64);

                    set @TERMCOUNT = (
                        SELECT
                          count(distinct  CAST(MLT.TERMTIMELENGTH as nvarchar(10)) + ' ' + MLT.TERMLENGTH)
                        FROM dbo.MEMBERSHIPPROGRAM MP
                        INNER JOIN dbo.MEMBERSHIPLEVEL ML on MP.ID = ML.MEMBERSHIPPROGRAMID
                        INNER JOIN dbo.MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID
                        WHERE MP.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


                    -- populate the output parameters, which correspond to fields on the form.  Note that
                    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
                    -- will display a "no data loaded" message.
                    SELECT DISTINCT @DATALOADED = 1,
                       @NAME = MP.NAME,
                       @DESCRIPTION = MP.DESCRIPTION,
                       @CATEGORY = MP.CATEGORY,
                       @SITE = S.NAME,
                       @PROGRAMTYPE = MP.PROGRAMTYPE,
                       @PROGRAMBASEDON = MP.PROGRAMBASEDON,
                       @ALLOWMULTIPLEMEMBERSHIPS = MP.ALLOWMULTIPLEMEMBERSHIPS,
                       @MEMBERSHIPPROGRAMID = MP.ID,
                       @WEBFORMSONLINE = case 
                            when exists(select 1 from dbo.MICROSITEPAGE where MICROSITEPAGE.OBJECTID = [MP].[ID] and MICROSITEPAGE.EXCLUDED = 0)
                                then 1
                            else 0
                       end,
                       @PROGRAMTYPECODE = [MP].[PROGRAMTYPECODE],
                       @PROGRAMBASEDONCODE = [MP].[PROGRAMBASEDONCODE],
                       @WHEREISREVENUETRACKEDCODE = [MP].[WHEREISREVENUETRACKEDCODE]
                    FROM dbo.MEMBERSHIPPROGRAM MP
                    LEFT JOIN dbo.[SITE] S on MP.SITEID = S.ID
                    where MP.ID = @ID