USP_KPI_DESIGNATION_REVENUETOTAL

CREATE procedure dbo.USP_KPI_DESIGNATION_REVENUETOTAL
(
    @VALUE money output,
    @DESIGNATIONID uniqueidentifier,
    @APPEALID uniqueidentifier = null,
    @BUSINESSUNITCODEID uniqueidentifier = null,
    @APPEALREPORTCODE1ID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ASOFDATE datetime,
    @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
    @CURRENCYID uniqueidentifier = null
)
as
    set nocount on;

    declare @RECEIVED money;
    declare @PLEDGEBALANCE money;

    declare @DL1ID uniqueidentifier;
    declare @DL2ID uniqueidentifier;
    declare @DL3ID uniqueidentifier;
    declare @DL4ID uniqueidentifier;
    declare @DL5ID uniqueidentifier;

    select
        @DL1ID = DESIGNATIONLEVEL1ID,
        @DL2ID = DESIGNATIONLEVEL2ID,
        @DL3ID = DESIGNATIONLEVEL3ID,
        @DL4ID = DESIGNATIONLEVEL4ID,
        @DL5ID = DESIGNATIONLEVEL5ID,
        @CURRENCYID = BASECURRENCYID
    from dbo.DESIGNATION
    where ID = @DESIGNATIONID;

    if @ORGPOSITIONSSELECTIONID is null
    begin
        with DESIGNATIONS_CTE as (
            select ID
            from dbo.DESIGNATION
            where DESIGNATIONLEVEL1ID = @DL1ID
                and (DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null)
                and (DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null)
                and (DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null)
                and (DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
        )
        select @RECEIVED = coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID,@CURRENCYID)), 0)
        from dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT
        inner join dbo.REVENUESPLIT_EXT PAYMENTSPLIT_EXT on PAYMENTSPLIT_EXT.ID = PAYMENTSPLIT.ID
        inner join DESIGNATIONS_CTE PAYMENTSPLITDESIGNATION on PAYMENTSPLITDESIGNATION.ID = PAYMENTSPLIT_EXT.DESIGNATIONID
        inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
        inner join dbo.REVENUE_EXT PAYMENT_EXT on PAYMENT_EXT.ID = PAYMENT.ID
        left join dbo.APPEAL on APPEAL.ID = PAYMENT_EXT.APPEALID
        left join dbo.REVENUESPLITBUSINESSUNIT RSBU on RSBU.REVENUESPLITID = PAYMENTSPLIT.ID and RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
        where
            (PAYMENT.[DATE] >= @STARTDATE or @STARTDATE is null)
            and (PAYMENT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
            and (APPEAL.ID = @APPEALID or @APPEALID is null)
            and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
            and (RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
            and PAYMENT.TYPECODE = 0;

        with DESIGNATIONS_CTE as (
            select ID
            from dbo.DESIGNATION
            where DESIGNATIONLEVEL1ID = @DL1ID
                and (DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null)
                and (DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null)
                and (DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null)
                and (DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
        )
        select @PLEDGEBALANCE =
            coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FTLI.ID,@CURRENCYID)), 0)
            - (
                coalesce(( --Subtract payments of the above pledges

                    select coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID,@CURRENCYID)), 0)
                    from dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT
                    inner join dbo.REVENUESPLIT_EXT PAYMENTSPLIT_EXT on PAYMENTSPLIT_EXT.ID = PAYMENTSPLIT.ID
                    inner join DESIGNATIONS_CTE PAYMENTSPLITDESIGNATION on PAYMENTSPLITDESIGNATION.ID = PAYMENTSPLIT_EXT.DESIGNATIONID
                    inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUE_EXT PAYMENT_EXT on PAYMENT_EXT.ID = PAYMENT.ID
                    left join dbo.APPEAL PAYMENTAPPEAL on PAYMENTAPPEAL.ID = PAYMENT_EXT.APPEALID
                    left join dbo.REVENUESPLITBUSINESSUNIT PAYMENTRSBU on PAYMENTRSBU.REVENUESPLITID = PAYMENTSPLIT.ID and PAYMENTRSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
                    where
                        PAYMENTSPLIT.ID in (
                            select INSTALLMENTPAYMENT.PAYMENTID
                            from dbo.INSTALLMENTPAYMENT
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = INSTALLMENTPAYMENT.PLEDGEID
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join DESIGNATIONS_CTE on DESIGNATIONS_CTE.ID = REVENUESPLIT_EXT.DESIGNATIONID
                            left join dbo.APPEAL on APPEAL.ID = REVENUE_EXT.APPEALID
                            left join dbo.REVENUESPLITBUSINESSUNIT RSBU on RSBU.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID and RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
                            where (FINANCIALTRANSACTION.[DATE] >= @STARTDATE or @STARTDATE is null)
                                and (FINANCIALTRANSACTION.[DATE] <= @ASOFDATE or @ASOFDATE is null)
                                and (APPEAL.ID = @APPEALID or @APPEALID is null)
                                and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
                                and (RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
                                and FINANCIALTRANSACTION.TYPECODE <> 3  -- Matching gift claim

                        )
                        and (PAYMENT.[DATE] >= @STARTDATE or @STARTDATE is null)
                        and (PAYMENT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
                        and (PAYMENTAPPEAL.ID = @APPEALID or @APPEALID is null)
                        and (PAYMENTAPPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
                        and (PAYMENTRSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
                ), 0)
                +
                coalesce(( --Subtract Writeoffs of the above pledges

                    select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID,@CURRENCYID))
                    from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
                    inner join dbo.REVENUESPLIT_EXT WRITEOFFSPLIT_EXT on WRITEOFFSPLIT_EXT.ID = WRITEOFFSPLIT.ID
                    inner join DESIGNATIONS_CTE on DESIGNATIONS_CTE.ID = WRITEOFFSPLIT_EXT.DESIGNATIONID
                    inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.FINANCIALTRANSACTIONID
                    inner join dbo.FINANCIALTRANSACTION WRITEOFFPARENT on WRITEOFFPARENT.ID = WRITEOFF.PARENTID
                    inner join dbo.REVENUE_EXT WRITEOFFPARENT_EXT on WRITEOFFPARENT_EXT.ID = WRITEOFFPARENT.ID
                    left join dbo.APPEAL on APPEAL.ID = WRITEOFFPARENT_EXT.APPEALID
                    where
                        WRITEOFF.TYPECODE = 20  -- Writeoff

                        and WRITEOFFSPLIT.DELETEDON is null
                        and (WRITEOFFPARENT.[DATE] >= @STARTDATE or @STARTDATE is null)
                        and (WRITEOFFPARENT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
                        and (APPEAL.ID = @APPEALID or @APPEALID is null)
                        and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
                        --and (APPEAL.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)

                        and (
                            @BUSINESSUNITCODEID in (
                                select
                                    distinct BUSINESSUNITCODEID
                                from dbo.REVENUESPLITBUSINESSUNIT RSBU
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RSBU.REVENUESPLITID
                                where FTLI.FINANCIALTRANSACTIONID = WRITEOFFPARENT.ID
                            )
                            or @BUSINESSUNITCODEID is null
                        )
                        and WRITEOFFPARENT.TYPECODE in (1,8)  -- Pledge, Donor challenge claim

                ), 0))
        from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
        inner join dbo.REVENUESPLIT_EXT FTLI_EXT on FTLI_EXT.ID = FTLI.ID
        inner join DESIGNATIONS_CTE on DESIGNATIONS_CTE.ID = FTLI_EXT.DESIGNATIONID
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
        inner join dbo.REVENUE_EXT FT_EXT on FT_EXT.ID = FT.ID
        left join dbo.APPEAL on APPEAL.ID = FT_EXT.APPEALID
        left join dbo.REVENUESPLITBUSINESSUNIT RSBU on RSBU.REVENUESPLITID = FTLI.ID and RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
        where
            (FT.[DATE] >= @STARTDATE or @STARTDATE is null)
            and (FT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
            and (APPEAL.ID = @APPEALID or @APPEALID is null)
            and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
            and (RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
            and FT.TYPECODE in (1,8);  -- Pledge, Donor challenge claim

    end
    else
    begin
        declare @IDS as table(ID uniqueidentifier);
        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

        with DESIGNATIONS_CTE as (
            select ID
            from dbo.DESIGNATION
            where DESIGNATIONLEVEL1ID = @DL1ID
                and (DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null)
                and (DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null)
                and (DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null)
                and (DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
        )
        select @RECEIVED = coalesce(sum(dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY(REVENUESOLICITOR.ID,@CURRENCYID)), 0)
        from dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT
        inner join dbo.REVENUESPLIT_EXT PAYMENTSPLIT_EXT on PAYMENTSPLIT_EXT.ID = PAYMENTSPLIT.ID
        inner join DESIGNATIONS_CTE on DESIGNATIONS_CTE.ID = PAYMENTSPLIT_EXT.DESIGNATIONID
        inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
        inner join dbo.REVENUE_EXT PAYMENT_EXT on PAYMENT_EXT.ID = PAYMENT.ID
        inner join dbo.REVENUESOLICITOR on REVENUESOLICITOR.REVENUESPLITID = PAYMENTSPLIT.ID
        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
            on OPH.CONSTITUENTID = REVENUESOLICITOR.CONSTITUENTID and PAYMENT.[DATE] between OPH.DATEFROM and coalesce(OPH.DATETO, PAYMENT.[DATE])
        inner join @IDS as SELECTION on SELECTION.ID = OPH.ID
        left join dbo.APPEAL on APPEAL.ID = PAYMENT_EXT.APPEALID
        left join dbo.REVENUESPLITBUSINESSUNIT RSBU on RSBU.REVENUESPLITID = PAYMENTSPLIT.ID and RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
        where
            (PAYMENT.[DATE] >= @STARTDATE or @STARTDATE is null)
            and (PAYMENT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
            and (APPEAL.ID = @APPEALID or @APPEALID is null)
            and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
            and (RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
            and PAYMENT.TYPECODE = 0;

        with DESIGNATIONS_CTE as (
            select ID
            from dbo.DESIGNATION
            where DESIGNATIONLEVEL1ID = @DL1ID
                and (DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null)
                and (DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null)
                and (DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null)
                and (DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
        )
        --Calculate Pledge Balance as Pledge Balance * percent Solicitor is responsible for

        select @PLEDGEBALANCE =
            coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FTLI.ID,@CURRENCYID)), 0)
            - (
                coalesce(( --Subtract payments of the above pledges

                    select coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID,@CURRENCYID)), 0)
                    from dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT
                    inner join dbo.REVENUESPLIT_EXT PAYMENTSPLIT_EXT on PAYMENTSPLIT_EXT.ID = PAYMENTSPLIT.ID
                    inner join DESIGNATIONS_CTE PAYMENTSPLITDESIGNATION on PAYMENTSPLITDESIGNATION.ID = PAYMENTSPLIT_EXT.DESIGNATIONID
                    inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUE_EXT PAYMENT_EXT on PAYMENT_EXT.ID = PAYMENT.ID
                    inner join dbo.REVENUESOLICITOR on REVENUESOLICITOR.REVENUESPLITID = PAYMENTSPLIT.ID
                    inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
                        on OPH.CONSTITUENTID = REVENUESOLICITOR.CONSTITUENTID and PAYMENT.[DATE] between OPH.DATEFROM and coalesce(OPH.DATETO, PAYMENT.[DATE])
                    inner join @IDS as SELECTION on SELECTION.ID = OPH.ID
                    left join dbo.APPEAL PAYMENTAPPEAL on PAYMENTAPPEAL.ID = PAYMENT_EXT.APPEALID
                    left join dbo.REVENUESPLITBUSINESSUNIT PAYMENTRSBU on PAYMENTRSBU.REVENUESPLITID = PAYMENTSPLIT.ID and PAYMENTRSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
                    where
                        PAYMENTSPLIT.ID in (
                            select INSTALLMENTPAYMENT.PAYMENTID
                            from dbo.INSTALLMENTPAYMENT
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = INSTALLMENTPAYMENT.PLEDGEID
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join DESIGNATIONS_CTE on DESIGNATIONS_CTE.ID = REVENUESPLIT_EXT.DESIGNATIONID
                            left join dbo.APPEAL on APPEAL.ID = REVENUE_EXT.APPEALID
                            left join dbo.REVENUESPLITBUSINESSUNIT RSBU on RSBU.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID and RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
                            where
                                (FINANCIALTRANSACTION.[DATE] >= @STARTDATE or @STARTDATE is null)
                                and (FINANCIALTRANSACTION.[DATE] <= @ASOFDATE or @ASOFDATE is null)
                                and (APPEAL.ID = @APPEALID or @APPEALID is null)
                                and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
                                and (RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
                                and FINANCIALTRANSACTION.TYPECODE <> 3  -- Matching gift claim

                        )
                        and (PAYMENT.[DATE] >= @STARTDATE or @STARTDATE is null)
                        and (PAYMENT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
                        and (PAYMENTAPPEAL.ID = @APPEALID or @APPEALID is null)
                        and (PAYMENTAPPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
                        and (PAYMENTRSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
                ), 0)
                +
                coalesce(( --Subtract Writeoffs of the above pledges

                    select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID,@CURRENCYID))
                    from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
                    inner join dbo.REVENUESPLIT_EXT WRITEOFFSPLIT_EXT on WRITEOFFSPLIT_EXT.ID = WRITEOFFSPLIT.ID
                    inner join DESIGNATIONS_CTE on DESIGNATIONS_CTE.ID = WRITEOFFSPLIT_EXT.DESIGNATIONID
                    inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.FINANCIALTRANSACTIONID
                    inner join dbo.FINANCIALTRANSACTION WRITEOFFPARENT on WRITEOFFPARENT.ID = WRITEOFF.PARENTID
                    inner join dbo.REVENUE_EXT WRITEOFFPARENT_EXT on WRITEOFFPARENT_EXT.ID = WRITEOFFPARENT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFPARENTSPLIT on WRITEOFFPARENTSPLIT.FINANCIALTRANSACTIONID = WRITEOFFPARENT.ID
                    inner join dbo.REVENUESOLICITOR on REVENUESOLICITOR.REVENUESPLITID = WRITEOFFPARENTSPLIT.ID
                    inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
                        on OPH.CONSTITUENTID = REVENUESOLICITOR.CONSTITUENTID and WRITEOFFPARENT.[DATE] between OPH.DATEFROM and coalesce(OPH.DATETO, WRITEOFFPARENT.[DATE])
                    inner join @IDS as SELECTION on SELECTION.ID = OPH.ID
                    left join dbo.REVENUESPLITBUSINESSUNIT RSBU on RSBU.REVENUESPLITID = WRITEOFFPARENTSPLIT.ID and RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
                    left join dbo.APPEAL on APPEAL.ID = WRITEOFFPARENT_EXT.APPEALID
                    where
                        WRITEOFF.TYPECODE = 20  -- Writeoff

                        and WRITEOFFSPLIT.DELETEDON is null
                        and (WRITEOFFPARENT.[DATE] >= @STARTDATE or @STARTDATE is null)
                        and (WRITEOFFPARENT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
                        and (APPEAL.ID = @APPEALID or @APPEALID is null)
                        and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
                        and (RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
                        and WRITEOFFPARENT.TYPECODE in (1,8)  -- Pledge, Donor challenge claim

                ), 0)
            )
        from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
        inner join dbo.REVENUESPLIT_EXT FTLI_EXT on FTLI_EXT.ID = FTLI.ID
        inner join DESIGNATIONS_CTE on DESIGNATIONS_CTE.ID = FTLI_EXT.DESIGNATIONID
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
        inner join dbo.REVENUE_EXT FT_EXT on FT_EXT.ID = FT.ID
        inner join dbo.REVENUESOLICITOR on REVENUESOLICITOR.REVENUESPLITID = FTLI.ID
        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
            on OPH.CONSTITUENTID = REVENUESOLICITOR.CONSTITUENTID and FT.[DATE] between OPH.DATEFROM and coalesce(OPH.DATETO, FT.[DATE])
        inner join @IDS as SELECTION on SELECTION.ID = OPH.ID
        left join dbo.APPEAL on APPEAL.ID = FT_EXT.APPEALID
        left join dbo.REVENUESPLITBUSINESSUNIT RSBU on RSBU.REVENUESPLITID = FTLI.ID and RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
        where
            (FT.[DATE] >= @STARTDATE or @STARTDATE is null)
            and (FT.[DATE] <= @ASOFDATE or @ASOFDATE is null)
            and (APPEAL.ID = @APPEALID or @APPEALID is null)
            and (APPEAL.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null)
            and (RSBU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
            and FT.TYPECODE in (1,8);  -- Pledge, Donor challenge claim

    end
    declare @REFUNDVALUE money=null            

    select @REFUNDVALUE =
            sum(
            (coalesce(LI.QUANTITY,0)) *                
            (coalesce(LI.QUANTITY * LI.UNITVALUE - EXT.DISCOUNTS,0) 
        - (case
        when coalesce(TICKET.ID, SALESORDERITEMMERCHANDISEUNIT.ID) is null then
            isnull([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT], 0)
        when LI.BASEAMOUNT > coalesce(TICKET.AMOUNTPAID, SALESORDERITEMMERCHANDISEUNIT.AMOUNTPAID) then
            coalesce(TICKET.ORDERLEVELDISCOUNTSAPPLIED, SALESORDERITEMMERCHANDISEUNIT.ORDERLEVELDISCOUNTSAPPLIED)        
        else 0    
        end)
            ) 
            )           
    from dbo.FINANCIALTRANSACTION as FT    
    inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID    
    inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID    
    inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID    
    inner join dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID] 
    left join dbo.TICKET on TICKET.ID = EXT.SALESORDERITEMIZEDITEMID
    left join SALESORDERITEMMERCHANDISEUNIT on SALESORDERITEMMERCHANDISEUNIT.ID = EXT.SALESORDERITEMIZEDITEMID
    left join (  
                select  
                        SALESORDERITEMID,   
                        sum(AMOUNT) as AMOUNT   
                from dbo.SALESORDERITEMORDERDISCOUNTDETAIL   
                group by SALESORDERITEMID  
            ) SALESORDERITEMORDERDISCOUNTDETAIL   
    on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID     
    left join dbo.[SALESORDERITEMDONATION]    
            on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]    
    where            
            [SALESORDERITEMDONATION].DESIGNATIONID=@DESIGNATIONID and    
            [SALESORDERITEM].[TYPECODE] in (0,1,2,3,14,16) and    
            FT.TYPECODE = 23  -- Refund

    set @VALUE = coalesce(@RECEIVED, 0) + coalesce(@PLEDGEBALANCE, 0) - coalesce(@REFUNDVALUE, 0);