USP_DATALIST_CONSTITUENTRECOGNITION

            CREATE procedure dbo.USP_DATALIST_CONSTITUENTRECOGNITION
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @CURRENCYCODE tinyint = 0
            )
            as
                set nocount on;

                declare @CURRENTDATE datetime = getdate();

                --multi currency set up

                declare @CURRENCYID uniqueidentifier

                declare @MULTICURRENCYENABLED bit;
                set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 

                if @MULTICURRENCYENABLED = 0 
                    set @CURRENCYCODE = 0

                if @CURRENCYCODE = 1
                    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                if @CURRENCYCODE = 3
                begin
                    set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
                    if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                        set @CURRENCYCODE = 1;
                end

                ;with 
                [CONSTITUENT_CTE] as 
                (
                    select @CONSTITUENTID [ID]
                    union -- Check security against Constituent Recognition View Form

                    select ID from dbo.UFN_GROUP_GETCURRENTMEMBERS(@CONSTITUENTID, @CURRENTAPPUSERID,  '0a58f120-563e-485d-99d4-e5bc76202f88', 0)
                    union 
                    select ID from dbo.UFN_CONSTITUENT_GETGROUPS(@CONSTITUENTID) where dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1
                )
                select 
                    CONSTITUENTRECOGNITION.ID,
                    CONSTITUENTRECOGNITION.STATUSCODE,
                    RECOGNITIONPROGRAM.NAME as RECOGNITIONPROGRAM,
                    RECOGNITIONLEVEL.NAME as RECOGNITIONLEVEL,
                    RECOGNITIONPROGRAM.TYPECODE,
                    (
                        select min(JOINDATE) from dbo.CONSTITUENTRECOGNITION CR 
                        where CR.RECOGNITIONPROGRAMID = CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID
                        and CR.CONSTITUENTID = @CONSTITUENTID
                    ) as JOINDATE,
                    CONSTITUENTRECOGNITION.JOINDATE as DATEACHIEVED,
                    CONSTITUENTRECOGNITION.EXPIRATIONDATE,
                    case @CURRENCYCODE 
                        when 0 then CONSTITUENTRECOGNITION.TOTALAMOUNT
                        when 3 then 
                            case 
                                when CONSTITUENTRECOGNITION.BASECURRENCYID = @CURRENCYID 
                                    then CONSTITUENTRECOGNITION.TOTALAMOUNT
                                else
                                    dbo.UFN_CURRENCY_CONVERT(CONSTITUENTRECOGNITION.TOTALAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(CONSTITUENTRECOGNITION.BASECURRENCYID, @CURRENCYID, CONSTITUENTRECOGNITION.JOINDATE, 1, null))
                            end
                        when 1 then CONSTITUENTRECOGNITION.ORGANIZATIONTOTALAMOUNT
                    end as TOTALAMOUNT,
                    dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENTRECOGNITION.CONSTITUENTID) as CONSTITUENT,
                    case 
                        when (CONSTITUENTRECOGNITION.STATUSCODE = 0 and CURRENTRECOGNITIONPROGRAM.EXPIRED = 1) then 'Lapsed'
                        else CONSTITUENTRECOGNITION.STATUS
                    end as STATUS,
                    case when RECOGNITIONLEVEL.AMOUNT <> (select MAX(AMOUNT) from dbo.RECOGNITIONLEVEL where RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID)
                        then (
                            select 
                                case @CURRENCYCODE 
                                    when 1 then CUR.AMOUNT - CONSTITUENTRECOGNITION.ORGANIZATIONTOTALAMOUNT
                                    when 3 then 
                                        case 
                                            when CONSTITUENTRECOGNITION.BASECURRENCYID = @CURRENCYID 
                                                then CUR.AMOUNT - CONSTITUENTRECOGNITION.TOTALAMOUNT
                                            else
                                                dbo.UFN_CURRENCY_CONVERT(CUR.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(CONSTITUENTRECOGNITION.BASECURRENCYID,@CURRENCYID,CONSTITUENTRECOGNITION.JOINDATE,1,null))
                                                - dbo.UFN_CURRENCY_CONVERT(CONSTITUENTRECOGNITION.TOTALAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(CONSTITUENTRECOGNITION.BASECURRENCYID,@CURRENCYID,CONSTITUENTRECOGNITION.JOINDATE,1,null))
                                        end 
                                    else CUR.AMOUNT - CONSTITUENTRECOGNITION.TOTALAMOUNT
                                end
                            from 
                            (
                                select 
                                    case @CURRENCYCODE 
                                        when 1 then min(RL.ORGANIZATIONAMOUNT)
                                        else min(RL.AMOUNT)
                                    end as AMOUNT
                                from dbo.RECOGNITIONLEVEL RL
                                inner join dbo.CONSTITUENTRECOGNITION CR on CR.RECOGNITIONPROGRAMID = RL.RECOGNITIONPROGRAMID
                                inner join [CONSTITUENT_CTE] on [CONSTITUENT_CTE].ID = CR.CONSTITUENTID
                                where 
                                    CR.JOINDATE = (
                                                            select max(JOINDATE) from dbo.CONSTITUENTRECOGNITION CR2
                                                            where CR2.CONSTITUENTID = CONSTITUENTRECOGNITION.CONSTITUENTID
                                                            and CR2.RECOGNITIONPROGRAMID = CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID
                                                    )
                                    and RL.ID not in   (
                                                            select RECOGNITIONLEVELID from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL CRDL
                                                            where CR.RECOGNITIONPROGRAMID = CRDL.RECOGNITIONPROGRAMID
                                                            and CR.CONSTITUENTID = CRDL.CONSTITUENTID
                                                       )
                                    and --amount in RECOGNITIONLEVEL greater than amount in CONSTITUENTRECOGNITION,

                                        --taking into account whether we are measuring org amount or total amount 

                                    (
                                        (case @CURRENCYCODE 
                                            when 1 then RL.ORGANIZATIONAMOUNT
                                            else case 
                                                    when CR.BASECURRENCYID = RL.BASECURRENCYID 
                                                        then RL.AMOUNT
                                                    else
                                                        dbo.UFN_CURRENCY_CONVERT(RL.AMOUNT,dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(RL.BASECURRENCYID,CR.BASECURRENCYID,CR.JOINDATE,1,null))
                                                 end
                                         end) > 
                                         (case @CURRENCYCODE
                                            when 1 then CONSTITUENTRECOGNITION.ORGANIZATIONTOTALAMOUNT
                                            else CONSTITUENTRECOGNITION.TOTALAMOUNT
                                         end)
                                    )
                                    and CR.RECOGNITIONPROGRAMID = CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID
                                    and RL.ISACTIVE = 1
                            ) CUR
                        ) 
                    else null
                    end as AMOUNTNEXT,
                    SITE.NAME as SITE,
                    case @CURRENCYCODE 
                        when 0 then CONSTITUENTRECOGNITION.BASECURRENCYID
                        else @CURRENCYID
                    end as BASECURRENCYID
                from dbo.UFN_CONSTITUENT_GETCURRENTRECOGNITIONPROGRAMS(@CURRENTDATE) as CURRENTRECOGNITIONPROGRAM
                inner join CONSTITUENT_CTE on CURRENTRECOGNITIONPROGRAM.CONSTITUENTID = CONSTITUENT_CTE.ID
                inner join dbo.CONSTITUENTRECOGNITION on CONSTITUENTRECOGNITION.ID = CURRENTRECOGNITIONPROGRAM.ID
                inner join dbo.RECOGNITIONPROGRAM on CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID  = RECOGNITIONPROGRAM.ID
                inner join dbo.RECOGNITIONLEVEL on RECOGNITIONLEVEL.ID = CONSTITUENTRECOGNITION.RECOGNITIONLEVELID
                left join dbo.SITE on SITE.ID = RECOGNITIONPROGRAM.SITEID
                where 
                (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[RECOGNITIONPROGRAM].[SITEID] or (SITEID is null and [RECOGNITIONPROGRAM].[SITEID] is null)))
                and
                (
                    @SITEFILTERMODE = 0 
                    or RECOGNITIONPROGRAM.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
                    or @CURRENTAPPUSERID is null
                )
                order by CONSTITUENTRECOGNITION.EXPIRATIONDATE desc;