USP_REVENUEBATCH_GETDEFAULTBENEFITS

CREATE procedure dbo.USP_REVENUEBATCH_GETDEFAULTBENEFITS
(
  @APPLICATIONINFO nvarchar(60),
  @REVENUESTREAMS xml,
  @APPEALID uniqueidentifier,
  @AMOUNT money,
  @DATE datetime,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @BENEFITS xml output,
  @PERCENTAGEBENEFITS xml output,
  @EXCHANGERATE decimal(20, 8),
  @BASEEXCHANGERATEID uniqueidentifier
)
as

  declare @DEFAULTMONEYBENEFITS as table
  (
    ID uniqueidentifier,
    BENEFITID uniqueidentifier,
    QUANTITY smallint,
    UNITVALUE money,
    SEQUENCE int,
    BENEFITCURRENCYID uniqueidentifier
  );

  if @APPEALID is not null
  begin
    insert into
      @DEFAULTMONEYBENEFITS
    select
      newid() as ID,
      BENEFITDETAILS.BENEFITID,
      BENEFITDETAILS.QUANTITY,
      BENEFITDETAILS.VALUE,
      BENEFITDETAILS.SEQUENCE,
      BENEFIT.BASECURRENCYID as BENEFITCURRENCYID
    from
      dbo.UFN_APPEAL_GETBENEFITDETAILS_ALL_2(@APPEALID, @AMOUNT, @TRANSACTIONCURRENCYID, @BASECURRENCYID) as BENEFITDETAILS
    left join
      dbo.BENEFIT on BENEFITDETAILS.BENEFITID = BENEFIT.ID
    where
      BENEFITDETAILS.USEPERCENT = 0;
  end

  declare @ROLLINGMAXSEQUENCE int = coalesce((select max(SEQUENCE) from @DEFAULTMONEYBENEFITS), 0);

  insert into
    @DEFAULTMONEYBENEFITS
  select
    newid() as ID,
    BENEFITDETAILS.BENEFITID,
    BENEFITDETAILS.QUANTITY,
    BENEFITDETAILS.VALUE,
    @ROLLINGMAXSEQUENCE + BENEFITDETAILS.SEQUENCE,
    BENEFIT.BASECURRENCYID as BENEFITCURRENCYID
  from
    dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETBENEFITS(@AMOUNT, @APPLICATIONINFO, @REVENUESTREAMS, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @EXCHANGERATE,
      @BASEEXCHANGERATEID) as BENEFITDETAILS
  left join
    dbo.BENEFIT on BENEFITDETAILS.BENEFITID = BENEFIT.ID;

  --Store the total benefit amount so we can calculate the percentage amounts for percent benefits

  declare @ROLLINGBENEFITAMOUNT money = 0;

  select
    @ROLLINGBENEFITAMOUNT = @ROLLINGBENEFITAMOUNT + (MONEYBENEFITS.QUANTITY * MONEYBENEFITS.UNITVALUE)
  from
    @DEFAULTMONEYBENEFITS as MONEYBENEFITS
  where
    MONEYBENEFITS.BENEFITCURRENCYID = @TRANSACTIONCURRENCYID;

  select
    @ROLLINGBENEFITAMOUNT = @ROLLINGBENEFITAMOUNT + dbo.UFN_CURRENCY_CONVERT(MONEYBENEFITS.QUANTITY * MONEYBENEFITS.UNITVALUE,
                                                      dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(MONEYBENEFITS.BENEFITCURRENCYID, @TRANSACTIONCURRENCYID, @DATE, 1, null))
  from
    @DEFAULTMONEYBENEFITS as MONEYBENEFITS
  where
    MONEYBENEFITS.BENEFITCURRENCYID <> @TRANSACTIONCURRENCYID;

  declare @DEFAULTPERCENTAGEBENEFITS as table
  (
    ID uniqueidentifier,
    BENEFITID uniqueidentifier,
    VALUEPERCENT int,
    PERCENTAPPLICABLEAMOUNT money,
    SEQUENCE int,
    BENEFITCURRENCYID uniqueidentifier
  );

  insert into
    @DEFAULTPERCENTAGEBENEFITS
  select
    newid() as ID,
    BENEFITDETAILS.BENEFITID,
    BENEFITDETAILS.VALUEPERCENT,
    0 as PERCENTAPPLICABLEAMOUNT,
    BENEFITDETAILS.SEQUENCE,
    BENEFIT.BASECURRENCYID as BENEFITCURRENCYID
  from
    dbo.UFN_APPEAL_GETBENEFITDETAILS_ALL_2(@APPEALID, @AMOUNT, @TRANSACTIONCURRENCYID, @BASECURRENCYID) as BENEFITDETAILS
  left join
    dbo.BENEFIT on BENEFITDETAILS.BENEFITID = BENEFIT.ID
  where
    BENEFITDETAILS.USEPERCENT = 1;

  --Update the percentage benefits with their applicable amount (total gift amount minus the benefit amount of prior benefits)

  declare PERCENTAGEBENEFITAMOUNTCURSOR cursor local fast_forward for
  select
    ID,
    VALUEPERCENT
  from
    @DEFAULTPERCENTAGEBENEFITS
  order by
    SEQUENCE;

  declare @CURRENTPERCENTAGEBENEFITID uniqueidentifier;
  declare @CURRENTPERCENTAGEBENEFITVALUEPERCENT int;

  open PERCENTAGEBENEFITAMOUNTCURSOR;
  fetch next from PERCENTAGEBENEFITAMOUNTCURSOR into @CURRENTPERCENTAGEBENEFITID, @CURRENTPERCENTAGEBENEFITVALUEPERCENT;

  while (@@FETCH_STATUS = 0)
  begin
    declare @CURRENTPERCENTAGEBENEFITAPPLICABLEAMOUNT money = (@AMOUNT - @ROLLINGBENEFITAMOUNT);

    update
      DEFAULTPERCENTAGEBENEFITS
    set
      DEFAULTPERCENTAGEBENEFITS.PERCENTAPPLICABLEAMOUNT = @CURRENTPERCENTAGEBENEFITAPPLICABLEAMOUNT
    from
      @DEFAULTPERCENTAGEBENEFITS as DEFAULTPERCENTAGEBENEFITS
    where
      DEFAULTPERCENTAGEBENEFITS.ID = @CURRENTPERCENTAGEBENEFITID;

    set @ROLLINGBENEFITAMOUNT = @ROLLINGBENEFITAMOUNT + (@CURRENTPERCENTAGEBENEFITAPPLICABLEAMOUNT * (@CURRENTPERCENTAGEBENEFITVALUEPERCENT / 100.00))

    fetch next from PERCENTAGEBENEFITAMOUNTCURSOR into @CURRENTPERCENTAGEBENEFITID, @CURRENTPERCENTAGEBENEFITVALUEPERCENT;
  end

  close PERCENTAGEBENEFITAMOUNTCURSOR;
  deallocate PERCENTAGEBENEFITAMOUNTCURSOR;

  select
    @BENEFITS =
    (
      select
        ID,
        BENEFITID,
        QUANTITY,
        UNITVALUE,
        SEQUENCE,
        BENEFITCURRENCYID,
        '' as DETAILS
      from
        @DEFAULTMONEYBENEFITS
      for xml path('ITEM'), type, elements, root('BENEFITS'), binary base64
    );

  select
    @PERCENTAGEBENEFITS =
    (
      select
        ID,
        BENEFITID,
        VALUEPERCENT,
        PERCENTAPPLICABLEAMOUNT,
        SEQUENCE,
        BENEFITCURRENCYID,
        '' as DETAILS
      from
        @DEFAULTPERCENTAGEBENEFITS
      for xml path('ITEM'), type, elements, root('PERCENTAGEBENEFITS'), binary base64
    );