UFN_REPORT_APPEALPROFILE_REVENUE

CREATE function dbo.[UFN_REPORT_APPEALPROFILE_REVENUE]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @CURRENCYCODE tinyint,
  @CURRENCYID uniqueidentifier,
  @ORGANIZATIONCURRENCYID uniqueidentifier,
  @CURRENCYISO nvarchar(3),
  @CURRENCYDECIMALDIGITS tinyint,
  @CURRENCYSYMBOL nvarchar(5),
  @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint,
  @CURRENCYROUNDINGTYPECODE tinyint,
  @ORGANIZATIONAMOUNTORIGINCODE tinyint
)
returns table
as return (
  with [SECURITY] ([CURRENTAPPUSERID], [ISSYSADMIN], [INNONRACROLE], [INNOGROUPROLE]) as (
    select
      @CURRENTAPPUSERID as [CURRENTAPPUSERID],
      dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) as [ISSYSADMIN],
      dbo.[UFN_SECURITY_APPUSER_IN_NONRACROLE](@CURRENTAPPUSERID) as [INNONRACROLE],
      dbo.[UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE](@CURRENTAPPUSERID) as [INNOGROUPROLE]
  ), [APPEALS] as (
    select
      [APPEAL].[ID] as [APPEALID],
      [SECURITY].*
    from dbo.[APPEAL]
    cross apply [SECURITY]
    where ([SECURITY].[ISSYSADMIN] = 1 or dbo.[UFN_SITEALLOWEDFORUSER]([SECURITY].[CURRENTAPPUSERID], [APPEAL].[SITEID]) = 1)
  )
  select
    [APPEALREVENUESPLIT].[APPEALID],
    [APPEALREVENUESPLIT].[REVENUEID],
    case when [FT].[TYPECODE] = 0 and [REVENUESPLIT].[APPLICATIONCODE] = 3 then [APPEALREVENUESPLIT].[REVENUEID] else null end as [REGULARREVENUEID],
    [APPEALREVENUESPLIT].[REVENUESPLITID],
    [FT].[CONSTITUENTID],
    cast([APPEALREVENUESPLIT].[DATE] as datetime) as [DATE],
    [APPEALREVENUESPLIT].[REVENUESPLITAMOUNTINCURRENCY] as [AMOUNT],
    null as [BALANCE],
    [APPEALREVENUESPLIT].[DESIGNATIONNAME] as [DESIGNATION],
    [APPEALREVENUESPLIT].[DESIGNATIONID] as [DESIGNATIONID],
    [APPEALREVENUESPLIT].[REVENUESPLITAMOUNTINCURRENCY] as [TRANSACTIONAMOUNT],
    case when [APPEALREVENUESPLIT].[TRANSACTIONTYPECODE] = 2 and [APPEALREVENUESPLIT].[APPLICATIONCODE] = 0 then 0 else 1 end as [INCLUDEINSUMMARY]
  from [APPEALS]
  inner join dbo.[UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK] (
    @CURRENCYID, 
    @ORGANIZATIONCURRENCYID, 
    @CURRENCYDECIMALDIGITS, 
    @CURRENCYROUNDINGTYPECODE, 
    case when @CURRENCYCODE = 1 then 1 else 3 end
  ) as [APPEALREVENUESPLIT] on [APPEALREVENUESPLIT].[APPEALID] = [APPEALS].[APPEALID]
  inner join dbo.[FINANCIALTRANSACTION] as [FT] on [FT].[ID] = [APPEALREVENUESPLIT].[REVENUEID]
  inner join dbo.[REVENUESPLIT_EXT] as [REVENUESPLIT] on [REVENUESPLIT].[ID] = [APPEALREVENUESPLIT].[REVENUESPLITID]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [FT].[CONSTITUENTID]
  where [APPEALREVENUESPLIT].[TRANSACTIONTYPECODE] not in (1, 3, 7) -- pledges, matching gifts, auction donation

  and [APPEALREVENUESPLIT].[APPLICATIONCODE] not in (2, 7) -- pledges, matching gift applications are included the union below

  and (
    [APPEALS].[ISSYSADMIN] = 1
    or [APPEALS].[INNONRACROLE] = 1
    or dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT](@CURRENTAPPUSERID, [CONSTITUENT].[ID], [APPEALS].[INNOGROUPROLE]) = 1
  )

  -- union so that non-pledge revenue does not have to go through UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK


  union all

  select
    [APPEALREVENUESPLIT].[APPEALID],
    [APPEALREVENUESPLIT].[REVENUEID],
    case when [FT].[TYPECODE] = 0 and [REVENUESPLIT].[APPLICATIONCODE] = 3 then [APPEALREVENUESPLIT].[REVENUEID] else null end as [REGULARREVENUEID],
    [APPEALREVENUESPLIT].[REVENUESPLITID],
    [FT].[CONSTITUENTID],
    cast([APPEALREVENUESPLIT].[DATE] as datetime) as [DATE],
    [APPEALREVENUESPLIT].[REVENUESPLITAMOUNTINCURRENCY] - [APPEALREVENUESPLIT].[WRITEOFFSPLITAMOUNTINCURRENCY] as [AMOUNT],
    [PLEDGEBALANCE].[BALANCEINCURRENCY] as [BALANCE],
    [APPEALREVENUESPLIT].[DESIGNATIONNAME] as [DESIGNATION],
    [APPEALREVENUESPLIT].[DESIGNATIONID] as [DESIGNATIONID],
    [APPEALREVENUESPLIT].[REVENUESPLITAMOUNTINCURRENCY] as [TRANSACTIONAMOUNT],
    1 as [INCLUDEINSUMMARY]
  from [APPEALS]
  inner join dbo.[UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK] (
    @CURRENCYID,
    @ORGANIZATIONCURRENCYID,
    @CURRENCYDECIMALDIGITS,
    @CURRENCYROUNDINGTYPECODE,
    case when @CURRENCYCODE = 1 then 1 else 3 end
  ) as [APPEALREVENUESPLIT] on [APPEALREVENUESPLIT].[APPEALID] = [APPEALS].[APPEALID]
  inner join dbo.[FINANCIALTRANSACTION] as [FT] on [FT].[ID] = [APPEALREVENUESPLIT].[REVENUEID]
  inner join dbo.[REVENUESPLIT_EXT] as [REVENUESPLIT] on [REVENUESPLIT].[ID] = [APPEALREVENUESPLIT].[REVENUESPLITID]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [FT].[CONSTITUENTID]
  inner join dbo.[UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK] (
    @CURRENCYID,
    @ORGANIZATIONCURRENCYID,
    @CURRENCYDECIMALDIGITS,
    @CURRENCYROUNDINGTYPECODE,
    getdate(),
    @ORGANIZATIONAMOUNTORIGINCODE,
    case when @CURRENCYCODE = 1 then 1 else 3 end
  ) as [PLEDGEBALANCE] on [PLEDGEBALANCE].[ID] = [APPEALREVENUESPLIT].[REVENUEID]
  where [APPEALREVENUESPLIT].[TRANSACTIONTYPECODE] in (1, 3, 7)
  and (
    [APPEALS].[ISSYSADMIN] = 1
    or [APPEALS].[INNONRACROLE] = 1
    or dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT](@CURRENTAPPUSERID, [CONSTITUENT].[ID], [APPEALS].[INNOGROUPROLE]) = 1
  )
)