UFN_REVENUEBATCH_EVENTREGISTRANT_GETBENEFITS

CREATE function dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETBENEFITS
(
  @AMOUNT money,
  @APPLICATIONINFO nvarchar(60),
  @REVENUESTREAMS xml,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @EXCHANGERATE decimal(20, 8),
  @BASEEXCHANGERATEID uniqueidentifier
)
returns @RESULT table
(
  BENEFITID uniqueidentifier,
  VALUE money,
  QUANTITY int,
  SEQUENCE int
)
as
begin

  declare @SINGLEAPPLICATIONID uniqueidentifier;
  declare @APPLICATIONTYPECODE tinyint;

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

  declare @REGISTRANTIDS table
  (
    ID uniqueidentifier,
    AMOUNT money
  );

  if @SINGLEAPPLICATIONID is null
  begin

    if @REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM') <> 0
    begin

      insert into
        @REGISTRANTIDS
      select
        APPLICATIONS.APPLICATIONID,
        APPLICATIONS.APPLIED
      from
      (
        select
          coalesce(APPLICATIONID, APPLICATIONCOMMITMENTID) as APPLICATIONID,
          APPLIED
        from
          dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
        where
          APPLIED > 0
      )
        as APPLICATIONS
      left join
        dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANT.ID = APPLICATIONS.APPLICATIONID
      left join
        dbo.REGISTRANT on REGISTRANT.ID = APPLICATIONS.APPLICATIONID
      where
        REGISTRANT.ID is not null
        or
        BATCHREVENUEREGISTRANT.ID is not null

    end

  end
  else
  begin

    insert into
      @REGISTRANTIDS
    select
      @SINGLEAPPLICATIONID,
      @AMOUNT
    where
      @APPLICATIONTYPECODE = 7;

  end

  declare @MUSTPAYFULLREGISTRATIONAMOUNT bit = 1;

  insert into
    @RESULT
  select
    REGISTRANTBENEFIT.BENEFITID,
    REGISTRANTBENEFIT.UNITVALUE,
    REGISTRANTBENEFIT.QUANTITY,
    (row_number() over(order by ID)) - 1 as SEQUENCE
  from
    @REGISTRANTIDS as REGISTRANT
  cross apply
    dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETPAYMENTBENEFITS(REGISTRANT.ID, REGISTRANT.AMOUNT, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @EXCHANGERATE,
      @BASEEXCHANGERATEID, @MUSTPAYFULLREGISTRATIONAMOUNT) as REGISTRANTBENEFIT;

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

  insert into
    @RESULT
  select
    REGISTRANTBENEFIT.BENEFITID,
    REGISTRANTBENEFIT.UNITVALUE,
    REGISTRANTBENEFIT.QUANTITY,
    @ROLLINGMAXSEQUENCE + (row_number() over(order by ID)) as SEQUENCE
  from
    @REGISTRANTIDS as REGISTRANT
  cross apply
    dbo.UFN_REVENUEBATCH_BATCHEVENTREGISTRANT_GETPAYMENTBENEFITS(REGISTRANT.ID, @TRANSACTIONCURRENCYID, @BASECURRENCYID) as REGISTRANTBENEFIT;

  return;

end