UFN_REVENUEBATCH_CALCULATERECEIPTAMOUNT

CREATE function dbo.UFN_REVENUEBATCH_CALCULATERECEIPTAMOUNT
(
  @AMOUNT money,
  @RECEIPTAMOUNT money,
  @APPLICATIONINFO nvarchar(60),
  @DATE datetime,
  @BENEFITSWAIVED bit,
  @BENEFITS xml,
  @PERCENTAGEBENEFITS xml,
  @REVENUESTREAMS xml,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @BASEEXCHANGERATEID uniqueidentifier
)
returns money
with execute as caller
begin

  --Calculate the total amount of benefits

  declare @BENEFITAMOUNT money = 0;

  if @BENEFITSWAIVED = 0
  begin

    select
      @BENEFITAMOUNT = 
        coalesce
        (
          sum
          (
            case
              when BASECURRENCYID <> @TRANSACTIONCURRENCYID then
                dbo.UFN_CURRENCY_CONVERT(BASETOTALVALUE, BASEEXCHANGERATEID)
            else
              BASETOTALVALUE
            end
          ),
          0
        )
    from
    (
      select
        REVBENEFITSFROMXML.QUANTITY * REVBENEFITSFROMXML.UNITVALUE as BASETOTALVALUE,
        BENEFIT.BASECURRENCYID as BASECURRENCYID,
        case
          when REVBENEFITSFROMXML.BASECURRENCYID <> @TRANSACTIONCURRENCYID then
            dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(REVBENEFITSFROMXML.BASECURRENCYID, @TRANSACTIONCURRENCYID, @DATE, 1, null)
          else
            null
        end as BASEEXCHANGERATEID
      from
        dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@BENEFITS) as REVBENEFITSFROMXML
      inner join
        dbo.BENEFIT on BENEFIT.ID = REVBENEFITSFROMXML.BENEFITID
    ) as BENEFITSFROMXML;

    select
      @BENEFITAMOUNT = @BENEFITAMOUNT + coalesce(sum(PERCENTAPPLICABLEAMOUNT * VALUEPERCENT / 100), 0)
    from
      dbo.UFN_REVENUEBATCH_GETBENEFITSPCT_FROMITEMLISTXML(@PERCENTAGEBENEFITS);
  end

  --Commitments: Get total amount applied and total receipt amount

  declare @COMMITMENTAPPLIEDAMOUNT money = 0;
  declare @COMMITMENTRECEIPTAMOUNT money = 0;

  declare @SINGLEAPPLICATIONID uniqueidentifier;
  declare @APPLICATIONTYPECODE tinyint;

  if len(@APPLICATIONINFO) > 3
    select
      @SINGLEAPPLICATIONID = SINGLEAPPLICATIONID,
      @APPLICATIONTYPECODE = APPLICATIONTYPECODE
    from
      dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@APPLICATIONINFO);

  if @SINGLEAPPLICATIONID is null
  begin
    if @REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM') <> 0
      with STREAMSAPPLICATION_CTE as
      (
        select
          APPLIED,
          APPLICATIONID,
          OVERPAYMENTAPPLICATIONTYPECODE,
          case
            when (BATCHREVENUEREGISTRANT.ID is not null) or (REGISTRANT.ID is not null) then
              6
            when FINANCIALTRANSACTION.ID is not null then
              case FINANCIALTRANSACTION.TYPECODE
                when 6 then
                  9
                when 8 then
                  10
                else
                  FINANCIALTRANSACTION.TYPECODE
              end
            else
              APPLICATIONS.TYPECODE
          end as TYPECODE,
          case
            when BATCHREVENUEREGISTRANT.ID is not null then
              1
            else
              0
          end as APPLIEDTOBATCHREGISTRANT
        from
        (
          select
            APPLIED,
            coalesce(APPLICATIONID, APPLICATIONCOMMITMENTID) as APPLICATIONID,
            OVERPAYMENTAPPLICATIONTYPECODE,
            TYPECODE
          from
            dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
          where
            APPLIED > 0
        )
          as APPLICATIONS
        left join
          dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = APPLICATIONS.APPLICATIONID
        left join
          dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANT.ID = APPLICATIONS.APPLICATIONID
        left join
          dbo.REGISTRANT on REGISTRANT.ID = APPLICATIONS.APPLICATIONID
      ),
      STREAMSRECEIPTAMOUNT_CTE as
      (
        select
         APPLIED,
          case
            when TYPECODE = 6 then
              dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS
              (
                APPLICATIONID, @DATE, APPLIED, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @BASEEXCHANGERATEID, 0, APPLIEDTOBATCHREGISTRANT, null
              )
            when TYPECODE = 1 then
              dbo.UFN_PLEDGE_CALCULATERECEIPTAMOUNT(APPLICATIONID, APPLIED, OVERPAYMENTAPPLICATIONTYPECODE)
            else
              APPLIED
          end as RECEIPTAMOUNT --JamesWill WI125245 2010-10-19 Event receipt amounts are in the receipt amount field. So don't double count them here.

        from
          STREAMSAPPLICATION_CTE
      )
      select
        @COMMITMENTAPPLIEDAMOUNT = coalesce(sum(APPLIED), 0),
        @COMMITMENTRECEIPTAMOUNT = coalesce(sum(RECEIPTAMOUNT), 0)
      from
        STREAMSRECEIPTAMOUNT_CTE
  end
  else
  begin
    set @COMMITMENTAPPLIEDAMOUNT = @AMOUNT;

    if @APPLICATIONTYPECODE = 5 -- pledge

    begin
      set @COMMITMENTRECEIPTAMOUNT = dbo.UFN_PLEDGE_CALCULATERECEIPTAMOUNT(@SINGLEAPPLICATIONID, @AMOUNT, default);
    end
    else if @APPLICATIONTYPECODE = 7 -- event registration

    begin
      select
      @COMMITMENTRECEIPTAMOUNT = dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS
                                 (
                                   @SINGLEAPPLICATIONID, @DATE, @AMOUNT, @TRANSACTIONCURRENCYID, @BASECURRENCYID,
                                   @BASEEXCHANGERATEID, 0, 0, null
                                 )
    end
    else
    begin
      set @COMMITMENTRECEIPTAMOUNT = @AMOUNT;
    end
  end

  --Donations: Receipt amount defaults to the applied amount

  declare @DONATIONAPPLIEDAMOUNT money = @AMOUNT - @COMMITMENTAPPLIEDAMOUNT;
  declare @DONATIONRECEIPTAMOUNT money = @DONATIONAPPLIEDAMOUNT;

  declare @CALCULATEDRECEIPTAMOUNT money = (@DONATIONRECEIPTAMOUNT + @COMMITMENTRECEIPTAMOUNT) - @BENEFITAMOUNT;

  if @CALCULATEDRECEIPTAMOUNT < 0
    set @CALCULATEDRECEIPTAMOUNT = 0;

  return @CALCULATEDRECEIPTAMOUNT;
end