USP_DATALIST_CAMPAIGNFUNDRAISER

                    CREATE procedure dbo.USP_DATALIST_CAMPAIGNFUNDRAISER (
                        @CAMPAIGNID uniqueidentifier,
                        @VIEWINACTIVE bit = 0
                    ) as begin
                        set nocount on;

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

                        declare @MINCOMPARERESULT int;
                        set @MINCOMPARERESULT = case @VIEWINACTIVE when 0 then 0 else 1 end;

                        with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
                        select
                            CAMPAIGNFUNDRAISER.ID,
                            CAMPAIGNFUNDRAISER.CONSTITUENTID,
                            CONSTITUENT.NAME,
                            CAMPAIGNFUNDRAISERPOSITIONCODE.DESCRIPTION as POSITIONDESCRIPTION,
                            CAMPAIGNFUNDRAISERTASKCODE.DESCRIPTION as TASKDESCRIPTION,
                            (
                                select SITE.NAME 
                                from dbo.ORGANIZATIONPOSITIONHOLDER
                                inner join dbo.ORGANIZATIONPOSITION on ORGANIZATIONPOSITION.ID = ORGANIZATIONPOSITIONHOLDER.POSITIONID
                                inner join dbo.SITE on SITE.ID = ORGANIZATIONPOSITION.SITEID
                                where ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = CONSTITUENT.ID
                                and ORGANIZATIONPOSITIONHOLDER.DATETO is null
                            ) as SITE,
                            (
                                select top 1 
                                    KPIINSTANCE.ID
                                from 
                                    dbo.KPIINSTANCE 
                                where 
                                    KPIINSTANCE.KPICATALOGID = '3ac33006-0c14-4227-b375-3bf2e61da1e1'
                                and
                                    KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNFUNDRAISER.CAMPAIGNID as varchar(36))
                                and
                                    KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="FUNDRAISERID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNFUNDRAISER.CONSTITUENTID as varchar(36))
                            ) as KPIINSTANCEID,
                            CAMPAIGNFUNDRAISER.DATEFROM,
                            CAMPAIGNFUNDRAISER.DATETO,
                            CAMPAIGNFUNDRAISER.STATUS as TIMEFRAME,
                            dbo.UFN_CONSTITUENT_ISCOMMITTEE(CAMPAIGNFUNDRAISER.CONSTITUENTID) as ISCOMMITTEE

                        from
                            dbo.CAMPAIGNFUNDRAISER
                        left outer join
                            dbo.CONSTITUENT on CAMPAIGNFUNDRAISER.CONSTITUENTID = CONSTITUENT.ID
                        left outer join
                            dbo.CAMPAIGNFUNDRAISERPOSITIONCODE on CAMPAIGNFUNDRAISER.CAMPAIGNFUNDRAISERPOSITIONCODEID = CAMPAIGNFUNDRAISERPOSITIONCODE.ID
                        left outer join
                            dbo.CAMPAIGNFUNDRAISERTASKCODE on CAMPAIGNFUNDRAISER.CAMPAIGNFUNDRAISERTASKCODEID = CAMPAIGNFUNDRAISERTASKCODE.ID

                        where
                            CAMPAIGNFUNDRAISER.CAMPAIGNID = @CAMPAIGNID
                            and dbo.UFN_DATE_COMPARETODATERANGE(@CURRENTDATE, CAMPAIGNFUNDRAISER.DATEFROM, CAMPAIGNFUNDRAISER.DATETO) <= @MINCOMPARERESULT

                        order by 
                            TIMEFRAME, NAME, POSITIONDESCRIPTION;
                    end