USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTRECOGNITION

The load procedure used by the view dataform template "Constituent Recognition 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.
@MEMBERSHIPNAME	nvarchar(203)	INOUT	Membership
@JOINDATE	datetime	INOUT	Join date
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@CONSECUTIVEYEARS	tinyint	INOUT	Years consecutive
@TOTALAMOUNT	money	INOUT	Total recognized
@TOTALPLANNEDGIFTAMOUNT	money	INOUT	Total planned gifts
@AMOUNTTONEXTLEVEL	money	INOUT	Amount to next level
@STATUS	nvarchar(20)	INOUT	Status
@DATEACHIEVED	datetime	INOUT	Date achieved
@CONSECUTIVESINCE	smallint	INOUT	Member consecutively since
@ISANNUAL	bit	INOUT	Is annual
@ISSEPARATEPLANNEDGIFTAMOUNT	bit	INOUT	Is separate planned gift amount
@PLANNEDAMOUNTTONEXTLEVEL	money	INOUT	Amount to next level
@SITENAME	nvarchar(250)	INOUT	Site
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@DECLINEDRECOGNITIONLEVELS	nvarchar(150)	INOUT	Declined levels
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTRECOGNITION
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @MEMBERSHIPNAME nvarchar(203) = null output,
                    @JOINDATE datetime = null output,
                    @EXPIRATIONDATE datetime = null output,
                    @CONSECUTIVEYEARS tinyint = null output,
                    @TOTALAMOUNT money = null output,
                    @TOTALPLANNEDGIFTAMOUNT money = null output,
                    @AMOUNTTONEXTLEVEL money = null output,
                    @STATUS nvarchar(20) = null output,
                    @DATEACHIEVED datetime = null output,
                    @CONSECUTIVESINCE smallint = null output,
                    @ISANNUAL bit = null output,
                    @ISSEPARATEPLANNEDGIFTAMOUNT bit = null output,
                    @PLANNEDAMOUNTTONEXTLEVEL money = null output,
                    @SITENAME nvarchar(250) = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @DECLINEDRECOGNITIONLEVELS nvarchar(150) = null output
                )
                as
                    set nocount on;
                    set @DATALOADED = 0;

                    declare @TODAY datetime;
                    set @TODAY = getdate();

                    with PROGRAM_NOW as
                    (
                        select
                            ID,
                            JOINDATE,
                            EXPIRATIONDATE,
                            RECOGNITIONPROGRAMID,
                            CONSTITUENTID,
                            cast(1 as bigint) as ROWNUMBER
                        from dbo.CONSTITUENTRECOGNITION
                        where ID = @ID

                        union all

                        select
                            C.ID,
                            C.JOINDATE,
                            C.EXPIRATIONDATE,
                            C.RECOGNITIONPROGRAMID,
                            C.CONSTITUENTID,
                            ROW_NUMBER() over (partition by C.RECOGNITIONPROGRAMID order by C.EXPIRATIONDATE desc) as ROWNUMBER
                        from dbo.CONSTITUENTRECOGNITION C
                            inner join PROGRAM_NOW P on C.RECOGNITIONPROGRAMID = P.RECOGNITIONPROGRAMID
                            and C.CONSTITUENTID = P.CONSTITUENTID
                            and P.ID <> C.ID
                            and C.EXPIRATIONDATE BETWEEN dateadd(yyyy, -1, P.EXPIRATIONDATE) and DATEADD(dd,-1,P.EXPIRATIONDATE)
                    )
                    select
                        @CONSECUTIVEYEARS = count(distinct EXPIRATIONDATE),
                        @CONSECUTIVESINCE = datepart(yyyy, min(JOINDATE))
                    from PROGRAM_NOW
                    where ROWNUMBER = 1;

                    declare @CONSTITUENTID uniqueidentifier = null;
                    declare @RECOGNITIONPROGRAMID uniqueidentifier = null;

                    select
                        @CONSTITUENTID = CONSTITUENTID,
                        @RECOGNITIONPROGRAMID = RECOGNITIONPROGRAMID
                    from dbo.CONSTITUENTRECOGNITION
                    where ID = @ID;

                    select
                        @JOINDATE = min(JOINDATE)
                    from dbo.CONSTITUENTRECOGNITION
                    where
                        RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and
                        CONSTITUENTID = @CONSTITUENTID;

                    with DECLINED_LEVELS as
                    (
                        select
                            RL.ID,
                            RL.NAME
                        from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL CRDL
                            inner join dbo.RECOGNITIONLEVEL RL on RL.ID = CRDL.RECOGNITIONLEVELID
                        where
                            CRDL.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID and
                            CRDL.CONSTITUENTID = @CONSTITUENTID
                    )
                    select
                        @DATALOADED = 1,
                        @MEMBERSHIPNAME = RP.NAME + ' - ' + RL.NAME,
                        @EXPIRATIONDATE = CR.EXPIRATIONDATE,
                        @TOTALAMOUNT = CR.TOTALAMOUNT,
                        @TOTALPLANNEDGIFTAMOUNT = CR.TOTALPLANNEDGIFTAMOUNT,
                        @AMOUNTTONEXTLEVEL = 
                            case when RL.AMOUNT <> (select MAX(AMOUNT) from dbo.RECOGNITIONLEVEL where RECOGNITIONPROGRAMID = RP.ID)
                                then
                                    (
                                        select min(AMOUNT)
                                        from dbo.RECOGNITIONLEVEL RLN
                                        where
                                            RLN.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID and
                                            AMOUNT > CR.TOTALAMOUNT and
                                            RLN.ID not in (select ID from DECLINED_LEVELS)
                                    ) - CR.TOTALAMOUNT
                            else null end,
                        @PLANNEDAMOUNTTONEXTLEVEL =
                            (
                                select min(PLANNEDGIFTAMOUNT)
                                from dbo.RECOGNITIONLEVEL RLN
                                where
                                    RLN.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID and
                                    PLANNEDGIFTAMOUNT > CR.TOTALPLANNEDGIFTAMOUNT and
                                    RLN.ID not in (select ID from DECLINED_LEVELS)
                            ) - CR.TOTALPLANNEDGIFTAMOUNT,
                        @STATUS =
                            case
                                when (CR.STATUSCODE = 0 and CR.EXPIRATIONDATE < @TODAY) then 'Lapsed'
                                else CR.STATUS
                            end,
                        @DATEACHIEVED = CR.JOINDATE,
                        @ISANNUAL = case when RP.TYPECODE = 0 then 1 else 0 end,
                        @ISSEPARATEPLANNEDGIFTAMOUNT = case when RP.PLANNEDGIFTCODE = 2 then 1 else 0 end,
                        @SITENAME = SITE.NAME,
                        @BASECURRENCYID = CR.BASECURRENCYID,
                        @DECLINEDRECOGNITIONLEVELS = (select dbo.UDA_BUILDLISTWITHDELIMITER(NAME,',') from DECLINED_LEVELS)
                    from dbo.CONSTITUENTRECOGNITION CR
                        inner join dbo.RECOGNITIONPROGRAM RP on CR.RECOGNITIONPROGRAMID = RP.ID
                        inner join dbo.RECOGNITIONLEVEL RL on CR.RECOGNITIONLEVELID = RL.ID
                        left join dbo.SITE on RP.SITEID = SITE.ID
                    where CR.ID = @ID;

                    return 0;