USP_DATALIST_MEMBERSHIPLEVELBENEFITFORCONSTITUENT

Displays the list of benefits for a membership level limited by the constituent for payment add.

Parameters

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

Definition

Copy



            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPLEVELBENEFITFORCONSTITUENT
            (
                @MEMBERSHIPLEVELID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier = null,
                @TRANSACTIONCURRENCYID uniqueidentifier = null,
                @BASECURRENCYID uniqueidentifier = null
            )
            as
            set nocount on;

            select 
                MLB.ID,
                BENEFIT.NAME,
                MLB.QUANTITY,
                MLB.UNITVALUE,
                (MLB.QUANTITY * MLB.UNITVALUE) AS [TOTALVALUE],
                MLB.DETAILS,
                MLB.SEQUENCE,
                MLB.BENEFITID,
                MLB.BASECURRENCYID,
                case 
                    when MLB.BASECURRENCYID = @TRANSACTIONCURRENCYID then (MLB.QUANTITY * MLB.UNITVALUE)
                    else
                        dbo.UFN_CURRENCY_CONVERT(
                            (MLB.QUANTITY * MLB.UNITVALUE),
                            dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(
                                MLB.BASECURRENCYID,
                                @TRANSACTIONCURRENCYID,
                                getdate(),
                                1,
                                default
                            )
                        )
                end as TRANSACTIONTOTALVALUE
            from dbo.MEMBERSHIPLEVELBENEFIT MLB
            inner join dbo.BENEFIT on MLB.BENEFITID = BENEFIT.ID
            where 
            BENEFIT.USEPERCENT = 0 and
            MLB.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID and
            (
                @CONSTITUENTID = null or
                BENEFIT.ID not in
                (
                    select BENEFITID
                    from dbo.BENEFITCONSTITUENTDECLINED
                    where BENEFITCONSTITUENTDECLINED.CONSTITUENTID = @CONSTITUENTID
                )
            ) and
            MLB.BASECURRENCYID = @BASECURRENCYID
            order by MLB.SEQUENCE asc