USP_DATALIST_PAYMENTEVENTREGISTRANTBENEFITS

Returns the benefits that a registrant will receive.
Parameters

Parameter	Parameter Type	Mode	Description
@REGISTRANTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@TRANSACTIONCURRENCYID	uniqueidentifier	IN	Transaction currency ID
@BASECURRENCYID	uniqueidentifier	IN	Base currency ID
Definition

Copy


CREATE procedure dbo.USP_DATALIST_PAYMENTEVENTREGISTRANTBENEFITS
(
    @REGISTRANTID uniqueidentifier,
    @TRANSACTIONCURRENCYID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier = null
)
as
    set nocount on;

    if @BASECURRENCYID is null and @TRANSACTIONCURRENCYID is null
        select @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    declare  @BENEFITS table  
    (  
        BENEFITID uniqueidentifier,  
        QUANTITY int,  
        UNITVALUE money,  
        DETAILS nvarchar(255),  
        BENEFITNAME nvarchar(100),  
        TRANSACTIONTOTALVALUE money  
    ); 

    --Fetch overall benefits

    insert into  
        @BENEFITS 
    select
        BENEFITID,
        sum(QUANTITY),
        UNITVALUE,
        DETAILS,
        BENEFITNAME,
        sum(TRANSACTIONTOTALVALUE)
    from
        dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETPAYMENTBENEFITS(@REGISTRANTID, default, @TRANSACTIONCURRENCYID, @BASECURRENCYID, default, default, default)
        group by BENEFITID, UNITVALUE, DETAILS, BENEFITNAME;

    declare @PAIDBENEFITS table(BENEFITID uniqueidentifier, QUANTITY int, TRANSACTIONTOTALVALUE money);

    --Fetch paid benefits 

    insert into @PAIDBENEFITS  
      select
      PAIDBENEFITS_TEMP.BENEFITID,
      sum(PAIDBENEFITS_TEMP.QUANTITY),
      sum(PAIDBENEFITS_TEMP.TRANSACTIONTOTALVALUE)
    from
      (
        select
          distinct FINANCIALTRANSACTION.ID,
          BENEFIT.ID 'BENEFITID',
          (REVENUEBENEFIT.QUANTITY) 'QUANTITY',
          (
            REVENUEBENEFIT.UNITVALUE * REVENUEBENEFIT.QUANTITY
          ) 'TRANSACTIONTOTALVALUE'
        from
          dbo.REVENUEBENEFIT
          inner join dbo.BENEFIT on BENEFIT.ID = REVENUEBENEFIT.BENEFITID
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUEBENEFIT.REVENUEID
          inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
          inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
        where REGISTRANTID = @REGISTRANTID
          and FINANCIALTRANSACTION.DELETEDON is null
          and REVENUEBENEFIT.UNITVALUE > 0
      ) PAIDBENEFITS_TEMP
    group by
      PAIDBENEFITS_TEMP.BENEFITID;



    --Fetch remaining benefits

    update @BENEFITS 
    set QUANTITY = BENIFITS.QUANTITY - PAID.QUANTITY,
    TRANSACTIONTOTALVALUE = BENIFITS.TRANSACTIONTOTALVALUE - PAID.TRANSACTIONTOTALVALUE             
    from @BENEFITS as BENIFITS
    inner join @PAIDBENEFITS as PAID on PAID.BENEFITID = BENIFITS.BENEFITID;

     delete from @BENEFITS where QUANTITY <= 0;

     select BENEFITID, QUANTITY, UNITVALUE, DETAILS, BENEFITNAME, TRANSACTIONTOTALVALUE from @BENEFITS;