USP_DATALIST_CONSTITUENTGROUP_REVENUEHISTORY

CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUP_REVENUEHISTORY
(
    @GROUPID uniqueidentifier,    
    @REVENUETYPEOPTIONCODE smallint = 0,
    @DATERANGECODE smallint = 2,
    @ISGROUP bit = 1,
    @PRESERVERELATIONSHIPS bit = 1,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;

    if @DATERANGECODE = 1 --Last 30 Days

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
    else if @DATERANGECODE = 2 --Last 90 Days

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
    else if @DATERANGECODE = 3 --Last Year

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)));
    else
        set @STARTDATE = @CURRENTDATE;            

    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

    -- households always include member giving, other types get looked up

    declare @GROUPINCLUDESMEMBERREVENUE bit;
    declare @ISHOUSEHOLD bit;

    select 
    @GROUPINCLUDESMEMBERREVENUE = 
      case
        when GD.GROUPTYPECODE = 0 then 1
        when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
      end,
    @ISHOUSEHOLD = 
      case
        when GD.GROUPTYPECODE = 0 then 1
        when GD.GROUPTYPECODE = 1 then 0
      end
    from dbo.GROUPDATA GD
    left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
    where GD.ID=@GROUPID;

    declare @REVTABLE table (ID uniqueidentifier);
    insert into @REVTABLE(ID)
        select
            REVENUE.ID
        from
            dbo.REVENUE
        where 
            REVENUE.CONSTITUENTID = @GROUPID
            and
            exists 
            (
                select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                where RSSUB.REVENUEID = REVENUE.ID
                and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                and 
                (
                    @SITEFILTERMODE = 0
                    or 
                    exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                )
            );

    if @GROUPINCLUDESMEMBERREVENUE = 1
        insert into @REVTABLE(ID)
            select
                REVENUE.ID
            from
                dbo.REVENUE
            inner join dbo.GROUPMEMBER GM 
                on REVENUE.CONSTITUENTID = GM.MEMBERID
            left join dbo.GROUPMEMBERDATERANGE GMDR 
                on GMDR.GROUPMEMBERID = GM.ID    
            where 
                GM.GROUPID = @GROUPID
                and
                ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= REVENUE.DATE)) or
                (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= REVENUE.DATE)) or
                (GMDR.DATEFROM <= REVENUE.DATE and GMDR.DATETO >= REVENUE.DATE)) and
                (@DATERANGECODE = 0 or (REVENUE.DATE >= @STARTDATE and REVENUE.DATE <= @ENDDATE))
                and
                exists 
                (
                    select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                    cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                    where RSSUB.REVENUEID = REVENUE.ID
                    and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                    and 
                    (
                        @SITEFILTERMODE = 0
                        or 
                        exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                    )
                );


    --Final select

    select 
        ID,
        RECORDID,                
        DATEADDED,
        TYPECODE,
        PARENT,
        TYPE,                
        DATE,
        AMOUNT,
        GROSSAMOUNT,
        BALANCE,
        DESIGNATIONS,
        PAYMENTMETHOD,                
        CATEGORY,
        CONSTITUENT,
        row_number() over (order by [TYPE]) as SEQUENCE,
        MATCHINGGIFTCONSTITUENT,
        (
            select 
                dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) 
            from 
                dbo.CAMPAIGN 
            inner join 
                dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.ID
            inner join
                dbo.REVENUESPLIT on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
            where
                REVENUESPLIT.REVENUEID = REVENUEHISTORY.ID
        ) as CAMPAIGNS,
        SITES
    from
    (
        -- Donations, event registration payments, and membership payments

        select
            cast(RS.ID as nvarchar(36))  as ID,
            REVENUE.ID as RECORDID,
            ISVISIBLE = 
                Case
                    when REVENUE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 0 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 1 then 1 else 0 end        -- Gifts

                    when REVENUE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 1 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 5 then 1 else 0 end        -- Event registration fees

                    when REVENUE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 5 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 7 then 1 else 0 end        -- Membership fees

                end,
            REVENUE.TRANSACTIONTYPECODE as TYPECODE,
            case 
                when RS.APPLICATIONCODE = 0 then RS.APPLICATION
                else RS.APPLICATION + ' Payment' 
            end as TYPE,
            dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
            REVENUE.DATE,
            REVENUE.DATEADDED,
            RS.AMOUNT as AMOUNT,
            dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT(RS.ID, 1) as GROSSAMOUNT,
            null as BALANCE,
            dbo.UFN_DESIGNATION_GETNAME(RS.DESIGNATIONID) as DESIGNATIONS,
            RPM.PAYMENTMETHOD as PAYMENTMETHOD,
            C.NAME as CONSTITUENT,
            PARENT = null,
            MATCHINGGIFTCONSTITUENT = null,
            dbo.UFN_REVENUESPLIT_BUILDSITELIST(RS.ID) SITES
        from 
            @REVTABLE RT
            inner join REVENUE on RT.ID = REVENUE.ID
            inner join dbo.REVENUESPLIT RS on REVENUE.ID = RS.REVENUEID
            inner join dbo.CONSTITUENT C on REVENUE.CONSTITUENTID = C.ID
            inner join dbo.REVENUEPAYMENTMETHOD RPM on REVENUE.ID = RPM.REVENUEID
            -- Do not return Matching gift claims for Orgs in group matching gifts from individuals in group, nor the group itself

            -- Causes duplicate key exception when added to the grid

            left outer join dbo.REVENUEMATCHINGGIFT RMG on RMG.ID = REVENUE.ID
            left outer join dbo.REVENUE R2 on R2.ID = RMG.MGSOURCEREVENUEID
            left outer join dbo.GROUPMEMBER GM2 on R2.CONSTITUENTID = GM2.MEMBERID
            left join dbo.REVENUECATEGORY RC on RS.ID = RC.ID    
            left join dbo.GLREVENUECATEGORYMAPPING on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID            
        where 
            (REVENUE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,1,5)) and
            (@DATERANGECODE = 0 or (REVENUE.DATE >= @STARTDATE and REVENUE.DATE <= @ENDDATE))
        group by 
            REVENUE.ID, RS.ID, REVENUE.TRANSACTIONTYPECODE, RS.APPLICATIONCODE, REVENUE.TRANSACTIONTYPECODE, REVENUE.TRANSACTIONTYPE,
            REVENUE.DATE, REVENUE.DATEADDED, RS.AMOUNT, RPM.PAYMENTMETHOD, C.NAME, RS.APPLICATION, RS.DESIGNATIONID

        union all
        -- Collect Revenue History for Members                         

        --        Pledges, Recurring gifts, Event registration fees, Matching gift claims,

        --        Matching gift payments, Other revenue (2.0?), Membership fees (2.0?), Grant awards

        select
            cast(REVENUE.ID as nvarchar(36))  as ID,
            REVENUE.ID as RECORDID,
            ISVISIBLE = 
                Case
                    when REVENUE.TRANSACTIONTYPECODE = 1 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 2 then 1 else 0 end        -- Pledges

                    when REVENUE.TRANSACTIONTYPECODE = 2 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 3 then 1 else 0 end        -- Recurring gifts

                    when REVENUE.TRANSACTIONTYPECODE = 3 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 4 then 1 else 0 end        -- Matching gift claims

                    when REVENUE.TRANSACTIONTYPECODE = 4 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 8 then 1 else 0 end        -- Planned gifts

                end,
            REVENUE.TRANSACTIONTYPECODE as TYPECODE,
            REVENUE.TRANSACTIONTYPE as TYPE,
            dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
            REVENUE.DATE,
            REVENUE.DATEADDED,
            REVENUE.AMOUNT as AMOUNT,
            case REVENUE.TRANSACTIONTYPECODE
                when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT(REVENUE.ID)
                when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT(REVENUE.ID, 1)
                else REVENUE.AMOUNT end as GROSSAMOUNT,
            case REVENUE.TRANSACTIONTYPECODE 
                when 1 then dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) 
                when 3 then dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) 
                else null 
            end as BALANCE,
            dbo.UFN_REVENUE_DESIGNATIONLIST(REVENUE.ID) as DESIGNATIONS,
            case REVENUE.TRANSACTIONTYPECODE 
                when 1 then null 
                when 3 then null 
                else RPM.PAYMENTMETHOD
            end as PAYMENTMETHOD,
            C.NAME as CONSTITUENT,
            PARENT = null,
            MATCHINGGIFTCONSTITUENT = null,
            dbo.UFN_REVENUESPLIT_BUILDSITELIST(RS.ID) SITES
        from @REVTABLE RT
        inner join REVENUE
            on RT.ID = REVENUE.ID
        inner join dbo.REVENUESPLIT RS
            on REVENUE.ID = RS.REVENUEID
        inner join dbo.CONSTITUENT C
            on REVENUE.CONSTITUENTID = C.ID
        inner join dbo.REVENUEPAYMENTMETHOD RPM
            on REVENUE.ID = RPM.REVENUEID
        -- Do not return Matching gift claims for Orgs in group matching gifts from individuals in group, nor the group itself

        -- Causes duplicate key exception when added to the grid

        left outer join dbo.REVENUEMATCHINGGIFT RMG
            on RMG.ID = REVENUE.ID
        left outer join dbo.REVENUE R2
            on R2.ID = RMG.MGSOURCEREVENUEID
        left outer join dbo.GROUPMEMBER GM2
            on R2.CONSTITUENTID = GM2.MEMBERID
        left join dbo.REVENUECATEGORY RC
            on RS.ID = RC.ID    
        left join dbo.GLREVENUECATEGORYMAPPING
            on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID            
        where 
            (REVENUE.TRANSACTIONTYPECODE in (1,2,4) or 
            (REVENUE.TRANSACTIONTYPECODE = 3 and R2.CONSTITUENTID <> @GROUPID and (GM2.GROUPID is null or GM2.GROUPID <> @GROUPID))) 
            and
            (@DATERANGECODE = 0 or (REVENUE.DATE >= @STARTDATE and REVENUE.DATE <= @ENDDATE))            
        group by REVENUE.ID, REVENUE.TRANSACTIONTYPECODE, RS.APPLICATIONCODE, REVENUE.TRANSACTIONTYPECODE, REVENUE.TRANSACTIONTYPE,
            REVENUE.DATE, REVENUE.DATEADDED, RPM.PAYMENTMETHOD, C.NAME, REVENUE.AMOUNT, RS.ID

        union all

            -- Pledge payment, Recurring gift payment, Matching gift payment, Planned gift payment, Grant award payment

        select 
            ID =
                Case RS.APPLICATIONCODE
                    when 3 then cast(RS.ID as nvarchar(36)) + cast(RGA.SOURCEREVENUEID as nvarchar(36))
                    else cast(RS.ID as nvarchar(36))
                end,
            REVENUE.ID as RECORDID,
            ISVISIBLE = 
                Case RS.APPLICATIONCODE
                    when 2 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 2 then 1 else 0 end
                    when 3 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 3 then 1 else 0 end
                    when 6 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 8 then 1 else 0 end                        
                    when 7 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 4 then 1 else 0 end

                    --Should I include a grant award option?

                    when 8 then case @REVENUETYPEOPTIONCODE when 0 then 1 else 0 end
                end,
            REVENUE.TRANSACTIONTYPECODE as TYPECODE,
            RS.APPLICATION + ' Payment' as TYPE,
            dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
            REVENUE.DATE,
            REVENUE.DATEADDED,
            AMOUNT = 
                case RS.APPLICATIONCODE
                    when 3 then sum(RGA.AMOUNT)
                    else sum(ISP.AMOUNT)
                end,
            case RS.APPLICATIONCODE
                when 3 then sum((RGA.AMOUNT/RS.AMOUNT) * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(RS.ID, 1) + RGA.AMOUNT)
                else sum((ISP.AMOUNT/RS.AMOUNT) * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(RS.ID, 1) + ISP.AMOUNT) end
                    as GROSSAMOUNT,
            null as BALANCE,
            dbo.UFN_DESIGNATION_GETNAME(RS.DESIGNATIONID) as DESIGNATIONS,
            RPM.PAYMENTMETHOD,
            C.NAME as CONSTITUENT,
            PARENT = 
                case @PRESERVERELATIONSHIPS
                    when 1 then
                        case RS.APPLICATIONCODE
                            when 3 then cast(RGA.SOURCEREVENUEID as nvarchar(36))
                            else cast(ISP.PLEDGEID as nvarchar(36))
                        end
                    else null
                end,
            MATCHINGGIFTCONSTITUENT = null,
            dbo.UFN_REVENUESPLIT_BUILDSITELIST(RS.ID) SITES
        from 
            @REVTABLE RT
            inner join REVENUE on RT.ID = REVENUE.ID
            inner join dbo.REVENUESPLIT RS on REVENUE.ID = RS.REVENUEID
            inner join dbo.REVENUEPAYMENTMETHOD RPM on REVENUE.ID = RPM.REVENUEID
            left outer join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS.ID
            left outer join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = ISP.INSTALLMENTSPLITID
            left outer join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = RS.ID
            inner join dbo.CONSTITUENT C on REVENUE.CONSTITUENTID = C.ID
            left join dbo.REVENUECATEGORY RC on RS.ID = RC.ID    
            left join dbo.GLREVENUECATEGORYMAPPING on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
        where 
            REVENUE.TRANSACTIONTYPECODE = 0 and    
            RS.APPLICATIONCODE in (2,3,6,7,8) and -- Pledge payment, Recurring gift payment, Matching gift payment, Grant award payment

            (@DATERANGECODE = 0 or (REVENUE.DATE >= @STARTDATE and REVENUE.DATE <= @ENDDATE))
        group by 
            REVENUE.ID, RS.ID, REVENUE.TRANSACTIONTYPECODE, REVENUE.DATE, REVENUE.DATEADDED, REVENUE.AMOUNT, RS.APPLICATIONCODE, RS.APPLICATION, RS.DESIGNATIONID,
            RGA.SOURCEREVENUEID, ISP.PLEDGEID, RPM.PAYMENTMETHOD, REVENUE.TRANSACTIONTYPE, INSTALLMENTSPLIT.DESIGNATIONID,C.NAME


        union all

            -- Write offs

        select
            null as ID,
            null as RECORDID,
            ISVISIBLE = case @REVENUETYPEOPTIONCODE when 0 then 1 when 2 then 1 else 0 end,
            null as TRANSACTIONTYPECODE,
            case REVENUE.TRANSACTIONTYPECODE
                when 1 then 'Pledge write-off' 
                when 6 then 'Grant award write-off' 
            end as TYPE,
            null as CATEGORY,
            WO.DATE as DATE,
            WO.DATEADDED as DATEADDED,
            IWO.AMOUNT as AMOUNT,
            IWO.AMOUNT as GROSSAMOUNT,
            null as BALANCE,
            null as DESIGNATIONS,    
            null as PAYMENTMETHOD,
            C.NAME as CONSTITUENT,
            PARENT = 
                case @PRESERVERELATIONSHIPS
                    when 1 then cast(WO.REVENUEID as nvarchar(36))
                    else null
                end,
            MATCHINGGIFTCONSTITUENT = null,
            SITE.NAME SITES
        from 
            @REVTABLE RT
            inner join dbo.WRITEOFF WO on RT.ID = WO.REVENUEID
            inner join REVENUE on REVENUE.ID = WO.REVENUEID                
            inner join dbo.INSTALLMENTWRITEOFF IWO on IWO.WRITEOFFID = WO.ID                             
            inner join dbo.WRITEOFFSPLIT WOS on WOS.WRITEOFFID = WO.ID      
            inner join dbo.CONSTITUENT C on REVENUE.CONSTITUENTID = C.ID
            inner join dbo.REVENUE WOR on WO.REVENUEID = WOR.ID
            inner join dbo.DESIGNATION on DESIGNATION.ID = WOS.DESIGNATIONID
            inner join dbo.DESIGNATIONLEVEL on coalesce(DESIGNATION.DESIGNATIONLEVEL5ID,DESIGNATION.DESIGNATIONLEVEL4ID,DESIGNATION.DESIGNATIONLEVEL3ID,DESIGNATION.DESIGNATIONLEVEL2ID,DESIGNATION.DESIGNATIONLEVEL1ID) = DESIGNATIONLEVEL.ID
            left join dbo.SITE on SITE.ID = DESIGNATIONLEVEL.SITEID
        where
            (@DATERANGECODE = 0 or (WO.DATE >= @STARTDATE and WO.DATE <= @ENDDATE))

        union all

            -- Matching gift claims

        select distinct
            ID =    case 
                        when RS2.APPLICATIONCODE in (2,7) then cast(RS.ID as nvarchar(36)) + cast(ISP.ID as nvarchar(36))
                        when RS2.APPLICATIONCODE = 3 then cast(RS.ID as nvarchar(36)) + cast(R2.ID as nvarchar(36)) + cast(RGA.SOURCEREVENUEID as nvarchar(36))
                        else cast(RS.ID as nvarchar(36)) + cast(RMG.MGSOURCEREVENUEID as nvarchar(36))
                    end,
            REVENUE.ID as RECORDID,
            case @REVENUETYPEOPTIONCODE 
                when 0 then 1 
                when 1 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 0 then 1 else 0 end
                when 2 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 2 then 1 else 0 end
                when 3 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 3 then 1 else 0 end
                when 4 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 7 then 1 else 0 end
                when 5 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 1 then 1 else 0 end
                when 7 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 5 then 1 else 0 end
            end,
            REVENUE.TRANSACTIONTYPECODE as TYPECODE,
            REVENUE.TRANSACTIONTYPE + ' (' + C.NAME + ')' as TYPE,
            dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
            REVENUE.DATE,
            REVENUE.DATEADDED,
            RS.AMOUNT,
            RS.AMOUNT as GROSSAMOUNT,
            dbo.UFN_PLEDGE_GETDESIGNATIONBALANCE(REVENUE.ID, RS.DESIGNATIONID) [BALANCE],
            dbo.UFN_DESIGNATION_GETNAME(RS.DESIGNATIONID) as DESIGNATIONS,
            RPM.PAYMENTMETHOD,
            C.NAME,
            PARENT = 
                case @PRESERVERELATIONSHIPS
                    when 1 then
                        case 
                            when RS2.APPLICATIONCODE in (2,7) then cast(ISP.ID as nvarchar(36))
                            when RS2.APPLICATIONCODE = 3 then cast(R2.ID as nvarchar(36)) + cast(RGA.SOURCEREVENUEID as nvarchar(36))
                            else cast(RMG.MGSOURCEREVENUEID as nvarchar(36))
                        end
                    else null
                end,
            MATCHINGGIFTCONSTITUENT = C.NAME,
            dbo.UFN_REVENUESPLIT_BUILDSITELIST(RS.ID) SITES
        from 
            @REVTABLE RT
            inner join REVENUE on RT.ID = REVENUE.ID
            inner join dbo.REVENUEMATCHINGGIFT RMG on RMG.ID = REVENUE.ID
            inner join dbo.REVENUE R2 on R2.ID = RMG.MGSOURCEREVENUEID
            inner join dbo.REVENUESPLIT RS on RS.REVENUEID = REVENUE.ID
            inner join dbo.REVENUESPLIT RS2 on RS2.REVENUEID = R2.ID
            left outer join dbo.REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = REVENUE.ID
            left outer join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS2.ID
            inner join dbo.CONSTITUENT C on C.ID = REVENUE.CONSTITUENTID
            inner join dbo.CONSTITUENT C2 on R2.CONSTITUENTID = C2.ID
            left outer join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = RS2.ID            
            left join dbo.REVENUECATEGORY RC on RS.ID = RC.ID    
            left join dbo.GLREVENUECATEGORYMAPPING on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID            
        where 
            REVENUE.TRANSACTIONTYPECODE = 3 and    -- Matching gift claims

            (@DATERANGECODE = 0 or (REVENUE.DATE >= @STARTDATE and REVENUE.DATE <= @ENDDATE))
        group by 
            REVENUE.ID, RS.ID, REVENUE.TRANSACTIONTYPECODE, RS.AMOUNT, REVENUE.TRANSACTIONTYPE, REVENUE.DATE, REVENUE.DATEADDED, RPM.PAYMENTMETHOD, RGA.SOURCEREVENUEID,
            R2.TRANSACTIONTYPECODE, C.NAME, RS2.APPLICATIONCODE, ISP.ID, RMG.MGSOURCEREVENUEID, R2.ID, RS.DESIGNATIONID

    ) as REVENUEHISTORY
    where ISVISIBLE = 1
    order by TYPE, DATE, DATEADDED;