UFN_REVENUETRIBUTE_CALCULATEGROSSAMOUNT

CREATE function dbo.UFN_REVENUETRIBUTE_CALCULATEGROSSAMOUNT
(
  @TRIBUTEID uniqueidentifier,
  @REVENUEID uniqueidentifier,
  @TRIBUTEAMOUNT money
)
returns money
with execute as caller
as
begin
  declare @GROSSAMOUNT money;
  declare @TRIBUTETAXCLAIMAMOUNT money;
  declare @TRIBUTEPERCENTAGE decimal(18,16) = 0;
  declare @DATE datetime;
  declare @REVENUETAXCLAIMAMOUNT money;
  declare @TRANSACTIONTYPE smallint;
  declare @BASETAXRATE numeric(30,6);
  declare @TRANSITIONALTAXRATE numeric(30,6);

  select
    @DATE = REVENUE.DATE,
    @TRANSACTIONTYPE = REVENUE.TRANSACTIONTYPECODE
  from
    dbo.REVENUETRIBUTE
  inner join
    dbo.REVENUE on REVENUE.ID = REVENUETRIBUTE.REVENUEID
  where
    REVENUETRIBUTE.TRIBUTEID = @TRIBUTEID and
    REVENUE.ID = @REVENUEID;

  declare @TRIBUTES_TOTALAMOUNT money;

  select
    @TRIBUTES_TOTALAMOUNT = sum(AMOUNT)
  from
    dbo.REVENUETRIBUTE
  where
    REVENUEID = @REVENUEID;

  if @TRIBUTES_TOTALAMOUNT > 0
  begin
    set @TRIBUTEPERCENTAGE = @TRIBUTEAMOUNT / @TRIBUTES_TOTALAMOUNT;
  end

  set @BASETAXRATE = dbo.UFN_GETGIFTAIDBASETAXRATE(@DATE);
  set @TRANSITIONALTAXRATE = dbo.UFN_GETGIFTAIDTRANSITIONALTAXRATE(@DATE);

  --pledges require a different calculation

  if @TRANSACTIONTYPE = 1
  begin
    select
      @REVENUETAXCLAIMAMOUNT = (sum(coalesce(TAXCLAIM.BASETAXCLAIMAMOUNT + TAXCLAIM.TRANSITIONALTAXCLAIMAMOUNT, 0))) * @TRIBUTEPERCENTAGE
    from
      dbo.REVENUESPLIT
    cross apply
      dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT(REVENUESPLIT.ID) as TAXCLAIM
    where
      REVENUESPLIT.REVENUEID = @REVENUEID
  end
  else
  begin
    set @REVENUETAXCLAIMAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMAMOUNT(@REVENUEID, 1) * @TRIBUTEPERCENTAGE;
  end

  set @TRIBUTETAXCLAIMAMOUNT = @TRIBUTEAMOUNT * ((@BASETAXRATE + @TRANSITIONALTAXRATE) / (100 - (@BASETAXRATE + @TRANSITIONALTAXRATE)))

  if @TRIBUTETAXCLAIMAMOUNT > @REVENUETAXCLAIMAMOUNT
  begin
    set @TRIBUTETAXCLAIMAMOUNT = @REVENUETAXCLAIMAMOUNT
  end

  set @GROSSAMOUNT = @TRIBUTEAMOUNT + coalesce(@TRIBUTETAXCLAIMAMOUNT, 0);

  return @GROSSAMOUNT;
end