USP_REPORT_VSECOLLEGE_SECTION3A

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

                if object_id('tempdb..#PAYMENTRECORD') is not null
                    drop table #PAYMENTRECORD

                select
                    DATE,
                    POSTDATE,
                    REVENUESPLITAMOUNT,
                    VSECATEGORYCODE,
                    VSECATEGORY,
                    VSECATEGORYPURPOSETYPECODE,
                    CONSTITUENTID,
                    TRANSACTIONTYPECODE,
                    REVENUEID,
                    REVENUESPLITID, 
                    APPLICATIONCODE,
                    VEHICLECODE
                into #PAYMENTRECORD
                from dbo.UFN_REPORT_VSECATEGORY_PAYMENT(@STARTDATE, @ENDDATE, @USEGIFTDATE, 1) 

                select
                    VSECATEGORY.VSECATEGORYCODE as VSECATEGORYCODE,
                    case VSECATEGORY.VSECATEGORYCODE
                        when 0 then '1. Unrestricted'
                        when 2 then 'Restricted  2. Academic Divisions'
                        when 3 then '3. Faculty & Staff Compensation'
                        when 4 then '4. Research'
                        when 5 then '5. Public Service & Extension'
                        when 6 then '6. Library'
                        when 7 then '7. Operation & Maint. of Plant'
                        when 8 then '8. Student Financial Aid'
                        when 9 then '9. Athletics'
                        when 10 then '10. Other Restricted'
                    end as LABEL,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (0) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as ALUMNI,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (4) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as PARENTS,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (8, 9, 10, 11) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as OTHERINDIVIDUALS,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (12, 13) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as FOUNDATIONS,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (14) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as CORPORATIONS,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (15) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as RELIGIOUSORGANIZATIONS,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (16) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as FUNDRAISINGCONSORTIA,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (17) and REPORT_VSECOLLEGE.REVENUETYPE = 0 then REPORT_VSECOLLEGE.REVENUESPLITAMOUNT else 0 end), 0) as OTHERORGANIZATIONS
                from
                    dbo.VSECATEGORY
                left outer join
                (
                    -- NOTE: This is basically the same code as UFN_REPORT_VSECOLLEGE except for the output of UFN_REPORT_VSECATEGORY_PAYMENT

                    -- was put in a temp table earlier.  Changes made here may also need to be made to UFN_REPORT_VSECOLLEGE.

                    select distinct 
                        REPORT_VSECATEGORY.DATE,
                        REPORT_VSECATEGORY.POSTDATE,
                        REPORT_VSECATEGORY.REVENUESPLITAMOUNT,
                        REPORT_VSECATEGORY.VSECATEGORYCODE,
                        REPORT_VSECATEGORY.VSECATEGORY,
                        REPORT_VSECATEGORY.VSECATEGORYPURPOSETYPECODE,
                        VSECONSTITUENT.VSECONSTITUENCYCODE,
                        VSECONSTITUENT.VSECONSTITUENCY,
                        VSECONSTITUENT.TYPECODE as VSECONSTITUENTTYPECODE,
                        VSECONSTITUENT.[TYPE] as VSECONSTITUENCYTYPE,
                        REPORT_VSECATEGORY.CONSTITUENTID as CONSTITUENTID,
                        REPORT_VSECATEGORY.TRANSACTIONTYPECODE as REVENUETYPE,
                        REPORT_VSECATEGORY.REVENUEID,
                        REPORT_VSECATEGORY.REVENUESPLITID,
                        --REPORT_VSECATEGORY.PAYMENTMETHODCODE,

                        DECEASED.ID as DECEASEDCONSTITUENTID,
                        REPORT_VSECATEGORY.APPLICATIONCODE,
                        REPORT_VSECATEGORY.VEHICLECODE
                    from #PAYMENTRECORD as REPORT_VSECATEGORY
                    inner join dbo.CONSTITUENT with (nolock) on CONSTITUENT.ID = REPORT_VSECATEGORY.CONSTITUENTID
                    inner join dbo.UFN_CONSTITUENT_GETVSECONSTITUENTS(@STARTDATE, @ENDDATE, 1) as VSECONSTITUENT on VSECONSTITUENT.CONSTITUENTID = CONSTITUENT.ID 
                    left outer join dbo.DECEASEDCONSTITUENT as DECEASED on DECEASED.ID = REPORT_VSECATEGORY.CONSTITUENTID
                    where
                        (@INCLUDEINACTIVE <> 0 or CONSTITUENT.ISINACTIVE = 0) and
                        (@INCLUDEDECEASED <> 0 or DECEASED.ID is null)
                ) as REPORT_VSECOLLEGE on REPORT_VSECOLLEGE.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                where
                    VSECATEGORY.VSECATEGORYCODE in (0, 2, 3, 4, 5, 6, 7, 8, 9, 10)
                    and VSECATEGORY.USAGETYPECODE <> 1
                group by VSECATEGORY.VSECATEGORYCODE;