USP_DATALIST_BENEFITS

This datalist returns all benefits.
Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@BENEFITCATEGORYCODEID	uniqueidentifier	IN	Category
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_BENEFITS 
                (
                    @NAME nvarchar(100) = null,
                    @BENEFITCATEGORYCODEID uniqueidentifier = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    set @NAME = coalesce(@NAME, '')

                    declare @BENEFITS table (
                        ID uniqueidentifier, 
                        NAME nvarchar(100), 
                        BENEFITCATEGORY_TRANSLATION nvarchar(100), 
                        DESCRIPTION nvarchar(255),
                        VALUE money,
                        SENDBENEFIT nvarchar(20),
                        VALUEPERCENT decimal(20,2),
                        SITES nvarchar(max),
                        BASECURRENCYID uniqueidentifier,
                        BENEFITAPPLIED bit
                    )

                    insert into @BENEFITS
                    select distinct BENEFIT.ID,
                            BENEFIT.NAME,
                            CATEGORY.DESCRIPTION as BENEFITCATEGORY_TRANSLATION,
                            BENEFIT.DESCRIPTION,
                            case when BENEFIT.USEPERCENT=1 then null else BENEFIT.VALUE end as VALUE,
                            BENEFIT.SENDBENEFIT,
                            case when BENEFIT.USEPERCENT=1 then BENEFIT.VALUEPERCENT else null end as VALUEPERCENT,
                            (
                                select dbo.UDA_BUILDLIST(SITE.NAME)
                                from dbo.SITE
                                    inner join dbo.BENEFITSITE on BENEFITSITE.SITEID = SITE.ID
                                where BENEFITSITE.BENEFITID = BENEFIT.ID
                            ) as SITES,
                            BENEFIT.BASECURRENCYID,
                            0 as BENEFITAPPLIED
                    from dbo.BENEFIT
                    left join dbo.BENEFITCATEGORYCODE CATEGORY on BENEFIT.BENEFITCATEGORYCODEID = CATEGORY.ID
                    left join dbo.BENEFITSITE on BENEFITSITE.BENEFITID = BENEFIT.ID
                    where 
                        (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[BENEFITSITE].[SITEID] or (SITEID is null and [BENEFITSITE].[SITEID] is null))) and
                        (@SITEFILTERMODE = 0 or 
                            BENEFITSITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
                        ) and
                        ((BENEFIT.BENEFITCATEGORYCODEID = @BENEFITCATEGORYCODEID or @BENEFITCATEGORYCODEID is null) and
                        (@NAME = '' or BENEFIT.NAME like @NAME + '%'))
                    order by CATEGORY.DESCRIPTION asc, BENEFIT.NAME asc

                    update @BENEFITS
                    set BENEFITAPPLIED = 1
                    where ID in (
                        select BENEFITID 
                        from dbo.EVENTPRICEBENEFIT
                    )

                    update @BENEFITS
                    set BENEFITAPPLIED = 1
                    where BENEFITAPPLIED = 0 and ID in (
                        select BENEFITID 
                        from dbo.REGISTRANTBENEFIT
                    )

                    update @BENEFITS
                    set BENEFITAPPLIED = 1
                    where BENEFITAPPLIED = 0 and ID in (
                        select BENEFITID 
                        from dbo.MEMBERSHIPLEVELBENEFIT
                    )

                    update @BENEFITS
                    set BENEFITAPPLIED = 1
                    where BENEFITAPPLIED = 0 and ID in (
                        select REVENUEBENEFIT_EXT.BENEFITID 
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUEBENEFIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEBENEFIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    )

                    select
                        ID, 
                        NAME, 
                        BENEFITCATEGORY_TRANSLATION, 
                        DESCRIPTION,
                        VALUE,
                        SENDBENEFIT,
                        VALUEPERCENT,
                        SITES,
                        BASECURRENCYID,
                        BENEFITAPPLIED
                    from @BENEFITS