USP_REPORT_VSESECONDARY_SECTION3C

            CREATE procedure dbo.USP_REPORT_VSESECONDARY_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 = 14 then 18
                        else 19
                    end as ADJUSTEDVSECATEGORYCODE,
                    case
                        when VSECATEGORY.VSECATEGORYCODE = 13 then '1. Endowment - Unrestricted'
                        when VSECATEGORY.VSECATEGORYCODE = 14 then '2. Endowment - Restricted'
                        else '3. Other Purposes'
                    end as LABEL,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (0) then REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT else 0 end), 0) as ALUMNI_FV,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (0) then (case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end) else 0 end), 0) as ALUMNI_PV,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (5, 6) then REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT else 0 end), 0) as PARENTS_FV,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (5, 6) then (case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end) else 0 end), 0) as PARENTS_PV,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (7) then REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT else 0 end), 0) as GRANDPARENTS_FV,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (7) then (case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end) else 0 end), 0) as GRANDPARENTS_PV,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (8, 9, 10, 11, 12, 13, 14, 15, 16, 17) then REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT else 0 end), 0) as OTHERS_FV,
                    coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (8, 9, 10, 11, 12, 13, 14, 15, 16, 17) then (case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end) else 0 end), 0) as OTHERS_PV
                from
                    dbo.VSECATEGORY
                left outer join dbo.UFN_REPORT_VSESECONDARY_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) REPORT_VSESECONDARY on REPORT_VSESECONDARY.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                where
                    VSECATEGORY.VSECATEGORYCODE in (12, 13, 14, 15, 16)
                    and VSECATEGORY.USAGETYPECODE <> 0
                    and dbo.UFN_PLANNEDGIFT_ISDEFERRED(REPORT_VSESECONDARY.PLANNEDGIFTID) = 0
                group by VSECATEGORY.VSECATEGORYCODE