USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAM_ALERTS

The load procedure used by the view dataform template "Membership Work Center Program Alerts View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PROGRAMID	uniqueidentifier	INOUT	PROGRAMID
@NUMCARDSTOPRINT	int	INOUT	NUMCARDSTOPRINT
@MEMBERSHIPRENEWALEFFORTPROCESSID	uniqueidentifier	INOUT	MEMBERSHIPRENEWALEFFORTPROCESSID
@HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS	bit	INOUT	HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS
@HASMEMBERSHIPPRODUCTINSTALLED	bit	INOUT	HASMEMBERSHIPPRODUCTINSTALLED

Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAM_ALERTS
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @PROGRAMID uniqueidentifier = null output,
                @NUMCARDSTOPRINT int = null output,
                @MEMBERSHIPRENEWALEFFORTPROCESSID uniqueidentifier = null output,
                @HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS bit = null output,
                @HASMEMBERSHIPPRODUCTINSTALLED bit = null output
            )
            as
                set nocount on;

                set @DATALOADED = 1;

                declare @CURRENTDATE datetime = getdate();

                -- Membership mailing process ID
                select @MEMBERSHIPRENEWALEFFORTPROCESSID = [ID]
                from dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS
                    where MEMBERSHIPPROGRAMID = @ID

                -- Membership renewal notice status
                if exists (
                    select MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS.ID
                    from dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS
                    inner join dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS
                        on MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS.PARAMETERSETID = MKTMEMBERSHIPRENEWALEFFORTPROCESS.ID
                    where MKTMEMBERSHIPRENEWALEFFORTPROCESS.MEMBERSHIPPROGRAMID = @ID
                )
                    set @HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS = 1
                else
                    set @HASMEMBERSHIPRENEWALEFFORTPROCESSSTATUS = 0

                select 
                    @NUMCARDSTOPRINT = count(*)
                from dbo.MEMBERSHIPCARD
                inner join dbo.MEMBER
                    on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
                inner join dbo.MEMBERSHIP
                    on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                where MEMBERSHIP.MEMBERSHIPPROGRAMID = @ID
                and MEMBERSHIPCARD.STATUSCODE = 0
                and MEMBERSHIP.STATUSCODE <> 1
                and (MEMBERSHIP.EXPIRATIONDATE is null or MEMBERSHIP.EXPIRATIONDATE > @CURRENTDATE)
                and (MEMBERSHIPCARD.EXPIRATIONDATE is null or MEMBERSHIPCARD.EXPIRATIONDATE > @CURRENTDATE)

                if exists(
                    select 1
                    from dbo.INSTALLEDPRODUCTLIST
                    where ID = '5E696818-62EE-4C90-8C28-B1F52D77176E'
                ) set @HASMEMBERSHIPPRODUCTINSTALLED = 1

                return 0;