USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPRENEWALEFFORTPROCESS

The save procedure used by the edit dataform template "Membership Renewal Effort Process Edit Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@MEMBERSHIPPROGRAMID	uniqueidentifier	IN	Membership program ID
@MEMBERSHIPMAILINGPROCESSID	uniqueidentifier	IN	Membership renewal notices
@APPEALID	uniqueidentifier	IN	Use existing appeal
@APPEALNAME	nvarchar(100)	IN	Appeal name
@APPEALDESCRIPTION	nvarchar(100)	IN	Appeal description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPRENEWALEFFORTPROCESS (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @MEMBERSHIPMAILINGPROCESSID uniqueidentifier,
    @APPEALID uniqueidentifier,
    @APPEALNAME nvarchar(100),
    @APPEALDESCRIPTION nvarchar(100),
    @CURRENTAPPUSERID uniqueidentifier
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

        declare @MEMBERSHIPMAILINGTEMPLATEID uniqueidentifier
        select @MEMBERSHIPMAILINGTEMPLATEID = MEMBERSHIPMAILINGTEMPLATEID
        from dbo.MKTMEMBERSHIPMAILINGPROCESS 
        where ID = @MEMBERSHIPMAILINGPROCESSID

        if @MEMBERSHIPMAILINGTEMPLATEID is null
        begin
                raiserror('ERR_MEMBERSHIPRENEWALEFFORT_FINDTEMPLATE', 13, 1)
                return 1
        end

        -- Update template in membership renewal effort process table

        update dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS
        set MEMBERSHIPMAILINGTEMPLATEID = @MEMBERSHIPMAILINGTEMPLATEID
        where ID = @ID

        -- Add new appeal

        if @APPEALID is null
        begin
            exec dbo.USP_DATAFORMTEMPLATE_ADD_APPEAL
                @APPEALID output,
                @CHANGEAGENTID ,    
                @APPEALNAME,            --@NAME

                @APPEALDESCRIPTION,     --@DESCRIPTION

                null,                   --@APPEALCATEGORYCODEID    

                null,                   --@BUSINESSUNITCODEID

                @CURRENTDATE,           --@STARTDATE

                null,                   --@ENDDATE

                0,                      --@GOAL

                null,                   --@APPEALREPORT1CODEID

                null,                   --@SITEID

                @MEMBERSHIPPROGRAMID,
                @CURRENTAPPUSERID

            if @APPEALID is null
            begin
                raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEAPPEAL', 13, 1)
                return 1
            end
        end

        -- Update appeal in membership renewal effort template if desirable

        if (select MEMBERSHIPPROGRAMID from dbo.APPEAL where ID = @APPEALID) = @MEMBERSHIPPROGRAMID
            and not exists (select 1
                        from dbo.[MKTMEMBERSHIPMAILINGTEMPLATEAPPEAL]
                        where [APPEALSYSTEMID] = @APPEALID
                            and [MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID
        )
        begin
            select @APPEALNAME = NAME, @APPEALDESCRIPTION = DESCRIPTION
            from dbo.APPEAL where ID = @APPEALID

            declare @APPEALINFORMATION xml
            set @APPEALINFORMATION = (
                select 
                    [QUERYVIEWCATALOG].[DISPLAYNAME] as "RECORDSOURCENAME",
                    [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as "SEARCHLISTCATALOGID",
                    [MKTAPPEALRECORDSOURCE].[ID] as "RECORDSOURCEID",
                    @APPEALID as APPEALSYSTEMID,
                    @APPEALNAME as APPEALID,
                    @APPEALDESCRIPTION as APPEALDESCRIPTION
                from dbo.[MKTAPPEALRECORDSOURCE] inner join QUERYVIEWCATALOG on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
                where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
                for xml raw('ITEM'),type,elements,root('APPEALINFORMATION'),BINARY BASE64
            )

             /* Save appeal information */
             exec dbo.[USP_MKTMEMBERSHIPMAILINGTEMPLATEAPPEAL_SAVEFIELD]
               @MEMBERSHIPMAILINGTEMPLATEID,
               @APPEALINFORMATION
        end

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

return 0;