UFN_REPORT_VSESECONDARY_PLANNEDGIFT

            CREATE function dbo.UFN_REPORT_VSESECONDARY_PLANNEDGIFT
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDEINACTIVE smallint = 0,
                @INCLUDEDECEASED smallint = 0,
                @USEGIFTDATE smallint = 0
            )
            returns table
            as
            return(
                select distinct 
                    REVENUE.DATE,
                    REVENUE.POSTDATE,
                    PLANNEDGIFTDESIGNATION.AMOUNT as PLANNEDGIFTDESIGNATIONAMOUNT,
                    PLANNEDGIFT.GIFTAMOUNT as FACEVALUE,
                    PLANNEDGIFT.NETPRESENTVALUE as PRESENTVALUE,
                    VSECATEGORY.VSECATEGORYCODE,
                    VSECATEGORY.VSECATEGORY,
                    VSECATEGORY.PURPOSETYPECODE as VSECATEGORYPURPOSETYPECODE,
                    VSECONSTITUENT.VSECONSTITUENCYCODE,
                    VSECONSTITUENT.VSECONSTITUENCY,
                    VSECONSTITUENT.TYPECODE as VSECONSTITUENTTYPECODE,
                    VSECONSTITUENT.TYPE as VSECONSTITUENCYTYPE,
                    PLANNEDGIFT.CONSTITUENTID as CONSTITUENTID,
                    PLANNEDGIFT.ID as PLANNEDGIFTID,
                    DECEASED.ID as DECEASEDCONSTITUENTID
                from 
                    dbo.PLANNEDGIFT
                    inner join dbo.CONSTITUENT with (nolock) on CONSTITUENT.ID = PLANNEDGIFT.CONSTITUENTID
                    inner join dbo.PLANNEDGIFTDESIGNATION on PLANNEDGIFT.ID = PLANNEDGIFTDESIGNATION.PLANNEDGIFTID
                    inner join dbo.DESIGNATION on PLANNEDGIFTDESIGNATION.DESIGNATIONID = DESIGNATION.ID
                    inner join dbo.VSECATEGORY on DESIGNATION.VSECATEGORYID = VSECATEGORY.ID
                    inner join dbo.UFN_CONSTITUENT_GETVSECONSTITUENTS(@STARTDATE, @ENDDATE, 0) as VSECONSTITUENT on VSECONSTITUENT.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID 
                    inner join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS as PLANNEDGIFTREVENUE on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.PLANNEDGIFTID
                    inner join dbo.REVENUE on PLANNEDGIFTREVENUE.REVENUEID = REVENUE.ID
                    left outer join dbo.DECEASEDCONSTITUENT DECEASED on DECEASED.ID = CONSTITUENT.ID
                where
                    VSECATEGORY.USAGETYPECODE <> 0
                    and REVENUE.DATE is not null
                    and (case when @STARTDATE is null 
                        then 0
                        else datediff(day, @STARTDATE, REVENUE.DATE) end
                        ) >= 0
                    and (case when @ENDDATE is null 
                        then 0
                        else datediff(day, @ENDDATE, REVENUE.DATE) 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
                    and dbo.UFN_PLANNEDGIFT_ISDEFERRED(PLANNEDGIFT.ID) = 0
                    and PLANNEDGIFT.STATUSCODE = 2 -- Accepted

                    and PLANNEDGIFT.VEHICLECODE in (0, 1, 2, 5, 7)
            )