USP_REPORT_VSECOLLEGE_SECTION3C

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

                select
                    case 
                        when VSECATEGORY.VSECATEGORYCODE = 13 then 13
                        when VSECATEGORY.VSECATEGORYCODE in (18, 19, 20, 21, 22, 23, 24, 25, 26) then 18
                        else 19
                    end as VSECATEGORYCODE,
                    case
                        when VSECATEGORY.VSECATEGORYCODE = 13 then '1. Endowment - Unrestricted'
                        when VSECATEGORY.VSECATEGORYCODE in (18, 19, 20, 21, 22, 23, 24, 25, 26) then '2. Endowment - Restricted'
                        else '3. Other Purposes'
                    end as LABEL,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (0) then REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT else 0 end), 0) as ALUMNI_FV,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (0) then (case when REPORT_VSECOLLEGE.FACEVALUE = 0 then 0 else (REPORT_VSECOLLEGE.PRESENTVALUE * (REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSECOLLEGE.FACEVALUE)) end) else 0 end), 0) as ALUMNI_PV,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (4) then REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT else 0 end), 0) as PARENTS_FV,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (4) then (case when REPORT_VSECOLLEGE.FACEVALUE = 0 then 0 else (REPORT_VSECOLLEGE.PRESENTVALUE * (REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSECOLLEGE.FACEVALUE)) end) else 0 end), 0) as PARENTS_PV,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (8, 9, 10, 11, 12, 13, 14, 15, 16, 17) then REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT else 0 end), 0) as OTHERS_FV,
                    coalesce(sum(case when REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (8, 9, 10, 11, 12, 13, 14, 15, 16, 17) then (case when REPORT_VSECOLLEGE.FACEVALUE = 0 then 0 else (REPORT_VSECOLLEGE.PRESENTVALUE * (REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSECOLLEGE.FACEVALUE)) end) else 0 end), 0) as OTHERS_PV
                from
                    dbo.VSECATEGORY
                left outer join dbo.UFN_REPORT_VSECOLLEGE_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) REPORT_VSECOLLEGE on REPORT_VSECOLLEGE.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                where
                    VSECATEGORY.VSECATEGORYCODE in (12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)
                    and VSECATEGORY.USAGETYPECODE <> 1
                group by VSECATEGORY.VSECATEGORYCODE;