USP_REPORT_VSECOLLEGE_SECTION3B

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

                -- Writing the query this way as opposed to how it's written in 3A

                -- is for optimization purposes. So, unless it's causing problems

                -- please leave it like this. - DavidHe



                declare @REPORT_VSECOLLEGE table
                (
                    VSECATEGORYCODE tinyint,
                    ALUMNI money,
                    PARENTS money,
                    OTHERINDIVIDUALS money,
                    FOUNDATIONS money,
                    CORPORATIONS money,
                    RELIGIOUSORGANIZATIONS money,
                    FUNDRAISINGCONSORTIA money,
                    OTHERORGANIZATIONS money
                );

                insert into @REPORT_VSECOLLEGE
                (
                    VSECATEGORYCODE,
                    ALUMNI,
                    PARENTS,
                    OTHERINDIVIDUALS,
                    FOUNDATIONS,
                    CORPORATIONS,
                    RELIGIOUSORGANIZATIONS,
                    FUNDRAISINGCONSORTIA,
                    OTHERORGANIZATIONS
                )
                (
                    select  
                        REPORT_VSECOLLEGE.VSECATEGORYCODE,
                        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.UFN_REPORT_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) REPORT_VSECOLLEGE
                    where
                        REPORT_VSECOLLEGE.VSECATEGORYCODE in (12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)
                    group by REPORT_VSECOLLEGE.VSECATEGORYCODE
                );

                select  
                    case VSECATEGORY.VSECATEGORYCODE
                            when 12 then 12
                            when 13 then 13
                            when 15 then 15
                            else 14
                        end as VSECATEGORYCODE,
                        case VSECATEGORY.VSECATEGORYCODE
                            when 12 then '1. Prop., Build. & Equipment'
                            when 13 then '2. Endowment - Unrestricted'
                            when 15 then '4. Loan Funds'
                            else '3. Endowment - Restricted'
                        end as LABEL,
                        coalesce(ALUMNI, 0) ALUMNI,
                        coalesce(PARENTS, 0) PARENTS,
                        coalesce(OTHERINDIVIDUALS, 0)OTHERINDIVIDUALS,
                        coalesce(FOUNDATIONS, 0) FOUNDATIONS,
                        coalesce(CORPORATIONS, 0) CORPORATIONS,
                        coalesce(RELIGIOUSORGANIZATIONS, 0) RELIGIOUSORGANIZATIONS,
                        coalesce(FUNDRAISINGCONSORTIA, 0) FUNDRAISINGCONSORTIA,
                        coalesce(OTHERORGANIZATIONS, 0) OTHERORGANIZATIONS
                from
                    dbo.VSECATEGORY
                left outer join @REPORT_VSECOLLEGE REPORT_VSECOLLEGE on REPORT_VSECOLLEGE.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                where
                    VSECATEGORY.USAGETYPECODE <> 1