UFN_CORPORATION_GETGIVINGTOTAL_INCURRENCY

CREATE function [dbo].[UFN_CORPORATION_GETGIVINGTOTAL_INCURRENCY]
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @CURRENCYID uniqueidentifier = null,
  @ORIGINCODE tinyint = null,
  @CURRENCYCODE tinyint = null
)
returns money
as
  begin
  declare @d money;    

  set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
  set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;

  select
    @DECIMALDIGITS = DECIMALDIGITS,
    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
  from
    dbo.UFN_CURRENCY_GETPROPERTIES(@CURRENCYID);

  select 
    @d = sum(coalesce(REVENUESPLIT.AMOUNTINCURRENCY,0)) - sum(coalesce(IWO.AMOUNT, 0))
  from 
    dbo.FINANCIALTRANSACTION REVENUE 
  inner join 
    dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
  left join (
    select 
      INSTALLMENTSPLIT.PLEDGEID, 
      INSTALLMENTSPLIT.DESIGNATIONID, 
      sum(IWO.AMOUNTINCURRENCY) as AMOUNT
    from 
      dbo.INSTALLMENTSPLIT
    inner join 
      dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE, @CURRENCYCODE) IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
    group by 
      INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID
  ) IWO on IWO.PLEDGEID = REVENUESPLIT.REVENUEID and IWO.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
  left join 
    dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
  cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVENUESITE
  where
    REVENUE.DELETEDON is null and
    REVENUE.CONSTITUENTID = @CONSTITUENTID 
  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.TYPECODE in (1,3) --Pledge or MG claim

    or (
      REVENUE.TYPECODE = 0 
      and (
        REVENUESPLIT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) 
        or (REVENUESPLIT.APPLICATIONCODE = 7 and INSTALLMENTSPLITPAYMENT.ID is null) -- unapplied MG claim payments

      )
    )
  ) --Payments                        

  and 
    dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, REVENUESITE.SITEID) = 1

  return @d;
end