USP_DATALIST_CONSTITUENTPROFILEDASHBOARDRECOGNITION

This datalist returns recognition data for the constituent profile report.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent ID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISVISIBLE	bit	IN	Is visible
@SELECTEDCURRENCYID	uniqueidentifier	IN	Selected currency ID
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDRECOGNITION
                (
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @ISVISIBLE bit = 1,
                    @SELECTEDCURRENCYID uniqueidentifier = null
                )
                as
                    set nocount on;
                    if @ISVISIBLE = 1
                    begin

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

                    select RP.NAME + ' - ' + RL.NAME as RECOGNITIONNAME,
                           (select min(CR2.JOINDATE) from dbo.CONSTITUENTRECOGNITION CR2
                                        where CR2.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID
                                            and CR2.CONSTITUENTID = CR.CONSTITUENTID) as JOINDATE,
                           CR.EXPIRATIONDATE,
                           dbo.UFN_CONSTITUENTRECOGNITION_GETCONSECUTIVEYEARS(CR.ID) as CONSECUTIVEYEARS,
                           dbo.UFN_CONSTITUENTRECOGNITION_TOTALAMOUNTINCURRENCY(CR.ID, @SELECTEDCURRENCYID) as TOTALAMOUNT,
                           dbo.UFN_CONSTITUENTRECOGNITION_PLANNEDGIFTAMOUNTINCURRENCY(CR.ID, @SELECTEDCURRENCYID) as TOTALPLANNEDGIFTAMOUNT,
                           (select min(dbo.UFN_RECOGNITIONLEVEL_GETAMOUNTINCURRENCY(RLN.ID, @SELECTEDCURRENCYID)) from dbo.RECOGNITIONLEVEL RLN
                                                    where RLN.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID 
                                                        and dbo.UFN_RECOGNITIONLEVEL_GETAMOUNTINCURRENCY(RLN.ID, @SELECTEDCURRENCYID) > dbo.UFN_CONSTITUENTRECOGNITION_TOTALAMOUNTINCURRENCY(CR.ID, @SELECTEDCURRENCYID))
                                                        - dbo.UFN_CONSTITUENTRECOGNITION_TOTALAMOUNTINCURRENCY(CR.ID, @SELECTEDCURRENCYID) as AMOUNTTONEXTLEVEL,
                            case when (CR.STATUSCODE = 0 and CR.EXPIRATIONDATE <  @TODAY) then 'Lapsed'
                                        else CR.STATUS
                                        end as STATUS,
                            CR.JOINDATE as DATEACHIEVED, 
                            dbo.UFN_CONSTITUENTRECOGNITION_GETCONSECUTIVESINCE(CR.ID) as CONSECUTIVESINCE,
                            RP.TYPECODE 
                    from dbo.CONSTITUENTRECOGNITION CR
                    inner join dbo.RECOGNITIONPROGRAM RP on CR.RECOGNITIONPROGRAMID = RP.ID
                    inner join dbo.RECOGNITIONLEVEL RL on CR.RECOGNITIONLEVELID = RL.ID
                    where CR.CONSTITUENTID = @CONSTITUENTID
                        and CR.JOINDATE = (select max(JOINDATE) from dbo.CONSTITUENTRECOGNITION CR3
                                                            where CR3.CONSTITUENTID = CR.CONSTITUENTID
                                                                and CR3.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID)
                    end