UFN_SMARTQUERY_GLOBALPLEDGEWRITEOFF

create function dbo.UFN_SMARTQUERY_GLOBALPLEDGEWRITEOFF (
  @STARTDATE datetime
  ,@ENDDATE datetime
  ,@MAXPLEDGEAMOUNT money
  ,@WRITEOFFMETHOD tinyint
  ,@CURRENCYID uniqueidentifier
  ,@CURRENCYCODE tinyint
  ,@CURRENTAPPUSERID uniqueidentifier = null
  ,@MAXROWS int
  )
returns @T table (
  ID uniqueidentifier
  ,[NAME] nvarchar(154)
  ,[DATE] datetime
  ,BALANCE money
  ,CURRENCYID uniqueidentifier
  ,KEYNAME nvarchar(100)
  ,FIRSTNAME nvarchar(50)
  ,MIDDLENAME nvarchar(50)
  ,CONSTITUENTID uniqueidentifier
  )
as
begin
  declare @ISADMIN bit;

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
  set @STARTDATE = coalesce(dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE), '1753-01-01');
  set @ENDDATE = coalesce(dbo.UFN_DATE_GETLATESTTIME(@ENDDATE), '9999-01-01');
  set @CURRENCYID = case @CURRENCYCODE
      when 0
        then dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
      else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
      end

  declare @ASOF datetime = getdate();
  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  if @CURRENCYID = null
    set @CURRENCYID = @ORGANIZATIONCURRENCYID;

  declare @ORIGINCODE tinyint;

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

  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;

  select @DECIMALDIGITS = CURRENCY.DECIMALDIGITS
    ,@ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
  from dbo.CURRENCY
  where CURRENCY.ID = @CURRENCYID;

  insert into @T
  select top (@MAXROWS) REVENUE.ID
    ,CONSTITUENT.[NAME]
    ,REVENUE.[DATE]
    ,REVENUE.BALANCEINCURRENCY as [BALANCE]
    ,@CURRENCYID
    ,CONSTITUENT.KEYNAME
    ,CONSTITUENT.FIRSTNAME
    ,CONSTITUENT.MIDDLENAME
    ,CONSTITUENT.ID as [CONSTITUENTID]
  from dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ASOF, @ORIGINCODE, null) as REVENUE
  inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
  where REVENUE.BALANCEINCURRENCY > 0
    and (
      @WRITEOFFMETHOD = 0
      /*Caller specified start date and end date*/
      or (
        @WRITEOFFMETHOD = 1
        and REVENUE.[DATE] between @STARTDATE and @ENDDATE
        )
      /*Caller specified a maximum pledge amount*/
      or (
        @WRITEOFFMETHOD = 2
        and REVENUE.BALANCEINCURRENCY <= @MAXPLEDGEAMOUNT
        )
      )
    and (REVENUE.TRANSACTIONTYPECODE = 1)
    and (
      @ISADMIN = 1
      or (
        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '32296A81-0AFE-45cb-A186-DC0EF17297F8', CONSTITUENT.ID) = 1)
        and (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '32296A81-0AFE-45cb-A186-DC0EF17297F8', REVENUE.ID) = 1)
        )
      )
  order by CONSTITUENT.KEYNAME
    ,CONSTITUENT.FIRSTNAME
    ,CONSTITUENT.MIDDLENAME
    ,CONSTITUENT.ID
    ,REVENUE.[DATE]
    ,REVENUE.ID;

  return;
end;