USP_REPORT_MATCHINGGIFTSUMMARY

            CREATE procedure dbo.USP_REPORT_MATCHINGGIFTSUMMARY
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @REPORTUSERID nvarchar(128) = null,
                @CURRENCYCODE tinyint = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            as
            set nocount on;

            declare @CURRENTAPPUSERID uniqueidentifier;
            declare @ISADMIN bit;
            declare @APPUSER_IN_NONRACROLE bit;
            declare @APPUSER_IN_NOSECGROUPROLE bit;

            set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
            set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
            set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
            set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

            declare @CURRENTDATE datetime = getdate();
            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            declare @SELECTEDCURRENCYID uniqueidentifier;
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;
            declare @ORIGINCODE tinyint;

            select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

            if @CURRENCYCODE = 1
            begin
                set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
                select
                    @DECIMALDIGITS = DECIMALDIGITS,
                    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                from
                    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID);
            end

            select
                [PLEDGEID],
                ISNULL(sum([AMOUNT]), 0.0) as [TOTALPAID]
            into #V_INSTALLMENTPAYMENT_TOTALPAID
            from dbo.INSTALLMENTPAYMENT
            group by
                    [PLEDGEID];

            select
                FINANCIALTRANSACTION.ID as [REVENUEID],
                ISNULL(#V_INSTALLMENTPAYMENT_TOTALPAID.[TOTALPAID], 0.0) as [TOTALPAID],
                PLEDGEBALANCES.BALANCEINCURRENCY as [BALANCE]
            into #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE
            from dbo.FINANCIALTRANSACTION
            inner join
                dbo.REVENUEMATCHINGGIFT on dbo.FINANCIALTRANSACTION.ID = dbo.REVENUEMATCHINGGIFT.ID
            left join
                #V_INSTALLMENTPAYMENT_TOTALPAID ON FINANCIALTRANSACTION.ID = #V_INSTALLMENTPAYMENT_TOTALPAID.PLEDGEID
            left join
                dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENTDATE, @ORIGINCODE, @CURRENCYCODE) PLEDGEBALANCES on PLEDGEBALANCES.ID = FINANCIALTRANSACTION.ID
            where REVENUEMATCHINGGIFT.ISACTIVE = 0
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTION.TYPECODE = 3;

            select
                COMPANYNAME = COMPANY.NAME,
                COMPANYKEYNAME = COMPANY.KEYNAME,
                CONSTITUENTNAME = CONSTITUENT_NF.NAME,
                MATCHEDPAYMENTS = case @CURRENCYCODE when 0 then CONSTITRD.BASEAMOUNT when 2 then CONSTITRD.TRANSACTIONAMOUNT else CONSTITRD.ORGAMOUNT end,
                PLEDGEAMOUNT = case @CURRENCYCODE when 0 then REVENUE.BASEAMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGAMOUNT end,
                SUMPAYMENTS = coalesce(PLEDGEAMOUNTSPAID.AMOUNTPAIDINCURRENCY, 0),
                SUMWRITEOFFS = coalesce(WO.AMOUNT, 0),
                BALANCE = PLEDGEBALANCES.BALANCEINCURRENCY - isnull(#INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE.BALANCE, 0.0),
                'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTID,
                MGCURRENCYPROPERTIES.ISO4217 [MGISOCURRENCYCODE],
                MGCURRENCYPROPERTIES.CURRENCYSYMBOL [MGCURRENCYSYMBOL],
                MGCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [MGCURRENCYSYMBOLDISPLAYSETTINGCODE],
                MGCURRENCYPROPERTIES.DECIMALDIGITS [MGDECIMALDIGITS],
                CONSTITRDCURRENCYPROPERTIES.ISO4217 [CONSTITRDISOCURRENCYCODE],
                CONSTITRDCURRENCYPROPERTIES.CURRENCYSYMBOL [CONSTITRDCURRENCYSYMBOL],
                CONSTITRDCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CONSTITRDCURRENCYSYMBOLDISPLAYSETTINGCODE],
                CONSTITRDCURRENCYPROPERTIES.DECIMALDIGITS [CONSTITRDDECIMALDIGITS]
            from
                dbo.CONSTITUENT COMPANY
            /*#IDSETEXTENSION*/
            inner join
                dbo.FINANCIALTRANSACTION REVENUE with (nolock) on COMPANY.ID = REVENUE.CONSTITUENTID
            left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
            inner join
                dbo.REVENUEMATCHINGGIFT RMG on REVENUE.ID = RMG.ID
            inner join
                dbo.FINANCIALTRANSACTION CONSTITRD with (nolock) on RMG.MGSOURCEREVENUEID = CONSTITRD.ID
            left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V_CONSTITRD with (noexpand) on CONSTITRD.ID = V_CONSTITRD.FINANCIALTRANSACTIONID
            inner join
                dbo.CONSTITUENT with (nolock) on CONSTITRD.CONSTITUENTID = CONSTITUENT.ID
            left join
                dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENTDATE, @ORIGINCODE, @CURRENCYCODE) PLEDGEBALANCES on PLEDGEBALANCES.ID = REVENUE.ID
            left join
                dbo.UFN_PLEDGE_GETAMOUNTPAIDINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENTDATE, @ORIGINCODE, @CURRENCYCODE) PLEDGEAMOUNTSPAID on PLEDGEAMOUNTSPAID.PLEDGEID = REVENUE.ID
            outer apply
                dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then V.BASECURRENCYID when 2 then REVENUE.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) MGCURRENCYPROPERTIES
            outer apply
                dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then V_CONSTITRD.BASECURRENCYID when 2 then CONSTITRD.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) CONSTITRDCURRENCYPROPERTIES
            outer apply
                dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
            left join
                (select sum(INSTALLMENTSPLITWRITEOFF.AMOUNTINCURRENCY) AMOUNT, WRITEOFF.PARENTID [REVENUEID]
                    from dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE, @CURRENCYCODE) INSTALLMENTSPLITWRITEOFF
                    inner join dbo.FINANCIALTRANSACTION WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                    where WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
                    group by WRITEOFF.PARENTID) WO on WO.REVENUEID = REVENUE.ID
            left join
                #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE on REVENUE.ID = #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE.REVENUEID
            where 
                REVENUE.TYPECODE = 3 and REVENUE.DELETEDON is null and CONSTITRD.DELETEDON is null
                and (@STARTDATE is null or cast(REVENUE.DATE as datetime) >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                and (@ENDDATE is null or cast(REVENUE.DATE as datetime) <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
                and REVENUE.ID not in (select REVENUEID from #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE where TOTALPAID = 0.0)
                and not (REVENUE.BASEAMOUNT = 0 and REVENUE.TRANSACTIONAMOUNT > 0)
                and not (CONSTITRD.BASEAMOUNT = 0 and CONSTITRD.TRANSACTIONAMOUNT > 0)
                and (@ISADMIN = 1 or 
                        @APPUSER_IN_NONRACROLE = 1 or
                            (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, COMPANY.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
                            and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1))
                and exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT where REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '5DC00A99-AD1C-428F-8FB7-8467FA19BDCB', REVSITES.SITEID)
                            ))
                and exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT where REVENUESPLIT.FINANCIALTRANSACTIONID = CONSTITRD.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '5DC00A99-AD1C-428F-8FB7-8467FA19BDCB', REVSITES.SITEID)
                            ))
            order by
                COMPANY.KEYNAME, COMPANY.KEYNAMEPREFIX, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME;

            drop table #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE;
            drop table #V_INSTALLMENTPAYMENT_TOTALPAID;