USP_REPORT_VSESECONDARY_SECTION1A

            CREATE procedure dbo.USP_REPORT_VSESECONDARY_SECTION1A
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDEINACTIVE smallint = 0,
                @INCLUDEDECEASED smallint = 0,
                @USEGIFTDATE smallint = 0
            )
            with execute as owner
            as
                set nocount on;

                declare @CURRENTOPERATIONSPLEDGES int;
                declare @CURRENTOPERATIONSAMOUNT money;
                declare @CAPITALPURPOSESPLEDGES int;
                declare @CAPITALPURPOSESAMOUNT money;

                -- Exclude college

                declare @EXCLUDEUSAGETYPECODE bit = 0; 

                declare @REVENUEIDS table
                (
                    REVENUEID uniqueidentifier
                );

                insert into @REVENUEIDS
                (
                    REVENUEID
                )
                (
                    select
                        distinct REPORT_VSECATEGORY.REVENUEID
                    from 
                        dbo.UFN_REPORT_VSECATEGORY_PLEDGE(@STARTDATE, @ENDDATE, @USEGIFTDATE) as REPORT_VSECATEGORY
                        inner join dbo.VSECATEGORY on VSECATEGORY.ID = REPORT_VSECATEGORY.VSECATEGORYID
                        inner join dbo.CONSTITUENT with (nolock) on CONSTITUENT.ID = REPORT_VSECATEGORY.CONSTITUENTID
                        inner join dbo.UFN_CONSTITUENT_GETVSECONSTITUENTS(@STARTDATE, @ENDDATE, @EXCLUDEUSAGETYPECODE) as VSECONSTITUENT on VSECONSTITUENT.CONSTITUENTID = REPORT_VSECATEGORY.CONSTITUENTID 
                        left outer join dbo.DECEASEDCONSTITUENT as DECEASED on DECEASED.ID = CONSTITUENT.ID
                    where
                        REPORT_VSECATEGORY.APPLICATIONCODE <> 6
                        and VSECATEGORY.USAGETYPECODE <> @EXCLUDEUSAGETYPECODE
                        and (case when @STARTDATE is null 
                            then 0
                            else datediff(day, @STARTDATE, case when @USEGIFTDATE <> 0 then REPORT_VSECATEGORY.DATE else isnull(REPORT_VSECATEGORY.POSTDATE, REPORT_VSECATEGORY.DATE) end) end
                            ) >= 0
                        and (case when @ENDDATE is null 
                            then 0
                            else datediff(day, @ENDDATE, case when @USEGIFTDATE <> 0 then REPORT_VSECATEGORY.DATE else isnull(REPORT_VSECATEGORY.POSTDATE, REPORT_VSECATEGORY.DATE) end) end
                            ) <= 0
                        and case when @INCLUDEINACTIVE <> 0 then 0 else CONSTITUENT.ISINACTIVE end = 0
                        and case when @INCLUDEDECEASED <> 0 
                            then CONSTITUENT.id
                            else (case when DECEASED.ID is null then CONSTITUENT.ID else null end) end = CONSTITUENT.ID
                )

                select
                    @CURRENTOPERATIONSPLEDGES = coalesce(count(CURRENTOPERATIONS.REVENUEID), 0),
                    @CURRENTOPERATIONSAMOUNT = coalesce(sum(CURRENTOPERATIONS.PLEDGEBALANCE), 0)
                from (
                    select
                        REVENUE.REVENUEID REVENUEID,
                        dbo.UFN_REVENUE_VSEPLEDGEBALANCE(REVENUE.REVENUEID, @ENDDATE, @EXCLUDEUSAGETYPECODE, 0) PLEDGEBALANCE
                    from
                        @REVENUEIDS REVENUE
                    ) CURRENTOPERATIONS
                where
                    CURRENTOPERATIONS.PLEDGEBALANCE > 0

                select
                    @CAPITALPURPOSESPLEDGES = coalesce(count(CAPITALPURPOSES.REVENUEID), 0),
                    @CAPITALPURPOSESAMOUNT = coalesce(sum(CAPITALPURPOSES.PLEDGEBALANCE), 0)
                from (
                    select
                        REVENUE.REVENUEID REVENUEID,
                        dbo.UFN_REVENUE_VSEPLEDGEBALANCE(REVENUE.REVENUEID, @ENDDATE, @EXCLUDEUSAGETYPECODE, 1) PLEDGEBALANCE
                    from
                        @REVENUEIDS REVENUE
                    ) CAPITALPURPOSES
                where
                    CAPITALPURPOSES.PLEDGEBALANCE > 0

                select
                    @CURRENTOPERATIONSPLEDGES as CURRENTOPERATIONSPLEDGES,
                    @CURRENTOPERATIONSAMOUNT as CURRENTOPERATIONSAMOUNT,
                    @CAPITALPURPOSESPLEDGES as CAPITALPURPOSESPLEDGES,
                    @CAPITALPURPOSESAMOUNT as CAPITALPURPOSESAMOUNT;