USP_DATALIST_MEMBERSHIPBENEFITS

This datalist returns a list of the benefits related to a membership.

Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



                    CREATE procedure dbo.USP_DATALIST_MEMBERSHIPBENEFITS
                    (
                        @MEMBERSHIPID uniqueidentifier
                    )
                    as 
                    set nocount on;

                    select 
                        MEMBERSHIPLEVEL.NAME AS MEMBERSHIPLEVELNAME,
                        dbo.UFN_BENEFIT_GETNAME(REVENUEBENEFIT.BENEFITID) AS BENEFIT,
                        REVENUEBENEFIT.QUANTITY,
                        REVENUEBENEFIT.UNITVALUE,
                        REVENUEBENEFIT.TOTALVALUE,
                        REVENUEBENEFIT.DETAILS,
                        MEMBERSHIPLEVELTERM.TERM AS TERM,
                        MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
                        cast(MEMBERSHIPTRANSACTION.EXPIRATIONDATE as date) EXPIRATIONDATE,
                        REVENUE.ID as REVENUEID,
                        REVENUEBENEFIT.VALUEPERCENT,
                        REVENUEBENEFIT.BASECURRENCYID
                    from dbo.MEMBERSHIPTRANSACTION
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID=MEMBERSHIPLEVEL.ID
                    inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID=MEMBERSHIPLEVELTERM.ID
                    inner join dbo.REVENUESPLIT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
                    inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                    inner join dbo.REVENUEBENEFIT on REVENUE.ID = REVENUEBENEFIT.REVENUEID
                    where 
                      MEMBERSHIPTRANSACTION.MEMBERSHIPID=@MEMBERSHIPID;