USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPRENEWALEFFORTPROCESSSTATUS

The load procedure used by the view dataform template "Membership Renewal Effort Process Status View Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Membership program ID
@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	Membership renewal effort process ID
@NETCOMMUNITYLINKESTABLISHED	bit	INOUT	Blackbaud Internet Solutions link established?
@NETCOMMUNITYEMAILJOBENABLED	bit	INOUT	Blackbaud Internet Solutions email job enabled?
@NETCOMMUNITYEMAILJOBSTARTED	bit	INOUT	Blackbaud Internet Solutions email job started?
@BUSINESSPROCESSOUTPUTID	uniqueidentifier	INOUT	Business process output ID
@NUMBERPROCESSEDEMAIL	int	INOUT	Email quantity
@EMAILJOBSTATUSCODE	int	INOUT	Email job status code
@HASBASICDEVELOPMENT	bit	INOUT	HASBASICDEVELOPMENT
@WORDMERGEENABLED	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPRENEWALEFFORTPROCESSSTATUS
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MEMBERSHIPPROGRAMID uniqueidentifier = null 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,
  @NETCOMMUNITYLINKESTABLISHED bit = null output,
  @NETCOMMUNITYEMAILJOBENABLED bit = null output, 
  @NETCOMMUNITYEMAILJOBSTARTED bit = null output, 
  @BUSINESSPROCESSOUTPUTID uniqueidentifier = null output,
  @NUMBERPROCESSEDEMAIL int = null output,
  @EMAILJOBSTATUSCODE int = null output,
  @HASBASICDEVELOPMENT bit = null output,
  @WORDMERGEENABLED bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select 
        @DATALOADED = 1,
        @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID,
        @NAME = MEMBERSHIPPROGRAM.NAME,
        @SITE = SITE.NAME,
        @EXPIRESON = MEMBERSHIPPROGRAM.EXPIRESON,
        @ALLOWMULTIPLEMEMBERSHIPS = MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS,
        @DESCRIPTION = MEMBERSHIPPROGRAM.DESCRIPTION,
        @ISACTIVE = MEMBERSHIPPROGRAM.ISACTIVE,
        @MEMBERSHIPRENEWALEFFORTPROCESSID = @ID,
        @NETCOMMUNITYLINKESTABLISHED = dbo.[UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED]()
    from dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS
    inner join dbo.MEMBERSHIPPROGRAM
        on MKTMEMBERSHIPRENEWALEFFORTPROCESS.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
    left join dbo.SITE 
        on MEMBERSHIPPROGRAM.SITEID = SITE.ID
    where MKTMEMBERSHIPRENEWALEFFORTPROCESS.ID = @ID;

    -- For sending email
    declare @BUSINESSPROCESSSTATUSID uniqueidentifier

    select @BUSINESSPROCESSSTATUSID = [SEGMENTATIONEXPORTPROCESSSTATUSID] 
    from (select top 1 [MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS].[SEGMENTATIONEXPORTPROCESSSTATUSID]
            from  dbo.[MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS]  
        left outer join dbo.[MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS]
        on [MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS].[MEMBERSHIPRENEWALEFFORTPROCESSSTATUSID] = [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[ID]
        where [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[PARAMETERSETID] = @ID
        order by [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].DATEADDED desc) as a

    declare @EXPORTPROCESSOUTPUTTABLE table (
        [ID] uniqueidentifier, 
        [PARAMETERSETID] uniqueidentifier, 
        [TABLEKEY] nvarchar(50), 
        [QUANTITY] integer, 
        [STATUSCODE] tinyint, 
        [WORDMERGEENABLED] bit, 
        [NETCOMMUNITYEMAILJOBENABLED] bit, 
        [NETCOMMUNITYEMAILJOBSTARTED] bit,
        [CHANNELCODE] tinyint
    )

    insert into @EXPORTPROCESSOUTPUTTABLE
        exec dbo.[USP_MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS_GETSEGMENTATIONEXPORTPROCESSOUTPUT] @BUSINESSPROCESSSTATUSID

    select top 1
        @NETCOMMUNITYEMAILJOBENABLED  = NETCOMMUNITYEMAILJOBENABLED, 
        @NETCOMMUNITYEMAILJOBSTARTED  = NETCOMMUNITYEMAILJOBSTARTED, 
        @BUSINESSPROCESSOUTPUTID  = ID,
        @NUMBERPROCESSEDEMAIL  = QUANTITY,
        @EMAILJOBSTATUSCODE  = STATUSCODE
    from @EXPORTPROCESSOUTPUTTABLE 
    where [NETCOMMUNITYEMAILJOBENABLED] = 1

    select top 1
        @WORDMERGEENABLED  = WORDMERGEENABLED
    from @EXPORTPROCESSOUTPUTTABLE 
    where [WORDMERGEENABLED] = 1

    --Gift Aid is for UK only
    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
        set @GIFTAIDQUALIFICATIONSTATUS = dbo.UFN_MEMBERSHIPQUALIFICATIONSTATUS(@MEMBERSHIPPROGRAMID)
    end

    if exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = '42C15648-749E-4859-A56D-3A6474814CC7')
        set @HASBASICDEVELOPMENT = 1

    return 0;