USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPRENEWALEFFORT_PRELOAD

The load procedure used by the edit dataform template "Membership Renewal Effort Add Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPPROGRAMID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@MEMBERSHIPPROGRAMNAME	nvarchar(50)	INOUT	Name
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(500)	INOUT	Description
@RENEWALNOTICES	xml	INOUT	Renewal notices
@MEMBERSHIPLEVELS	xml	INOUT	Membership levels to include

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPRENEWALEFFORT_PRELOAD
(
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @MEMBERSHIPPROGRAMNAME nvarchar(50) = null output,
    @NAME nvarchar(100) = null output,
    @DESCRIPTION nvarchar(500) = null output,
     @RENEWALNOTICES xml = null output,
    @MEMBERSHIPLEVELS xml = null output

)
as
    set nocount on;

    select @MEMBERSHIPPROGRAMNAME = NAME, @NAME = NAME
    from dbo.MEMBERSHIPPROGRAM
    where ID = @MEMBERSHIPPROGRAMID;

    declare @NEXTMONTHMAILPACKAGE uniqueidentifier;
    declare @NEXTMONTHEMAILPACKAGE uniqueidentifier;
    declare @THISMONTHMAILPACKAGE uniqueidentifier;
    declare @THISMONTHEMAILPACKAGE uniqueidentifier;
    declare @LASTMONTHMAILPACKAGE uniqueidentifier;
    declare @LASTMONTHEMAILPACKAGE uniqueidentifier;

    select @NEXTMONTHMAILPACKAGE  = ID from dbo.MKTPACKAGE where ID = 'DEDB6CB3-DCD4-489C-8E4A-21A0069A072B';
    select @NEXTMONTHEMAILPACKAGE = ID from dbo.MKTPACKAGE where ID = '7862A822-4421-43ED-A2BA-3DB77003E027';
    select @THISMONTHMAILPACKAGE  = ID from dbo.MKTPACKAGE where ID = 'A0CEB66A-BCD7-4F48-94AF-66FC956C4365';
    select @THISMONTHEMAILPACKAGE = ID from dbo.MKTPACKAGE where ID = '4A059B3F-A2A5-494C-814B-D04EDE4CA085';
    select @LASTMONTHMAILPACKAGE  = ID from dbo.MKTPACKAGE where ID = '38E5B71A-8F6D-4505-ACF0-DDFACF9BCF55';
    select @LASTMONTHEMAILPACKAGE = ID from dbo.MKTPACKAGE where ID = '2A21F700-6F87-4106-9473-F3D7412AB5F7';

    set @RENEWALNOTICES = (

        select * from (
            select 0 as SELECTED, 0 as TIMETOEXPIRATION, null as MAILPACKAGEID, null as EMAILPACKAGEID
            union all
            select 0, 1, null, null
            union all
            select 1, 2, @NEXTMONTHMAILPACKAGE, @NEXTMONTHEMAILPACKAGE
            union all
            select 1, 3, @THISMONTHMAILPACKAGE, @THISMONTHEMAILPACKAGE
            union all
            select 1, 4, @LASTMONTHMAILPACKAGE, @LASTMONTHEMAILPACKAGE
            union all
            select 0, 5, null, null
            union all
            select 0, 6, null, null
        ) as RENEWALNOTICES

        for xml raw('ITEM'),type,elements,root('RENEWALNOTICES'),BINARY BASE64            
    )

    set @MEMBERSHIPLEVELS = (
        select ID, NAME, 1 as SELECTED
        from dbo.MEMBERSHIPLEVEL
        where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
            and ISACTIVE = 1
        for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELS'),BINARY BASE64            
    );

    return 0;