USP_DATALIST_NAMEDRECOGNITIONHISTORY

Returns a list for all named recognitions for a constituent

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@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_NAMEDRECOGNITIONHISTORY
                (
                    @CONSTITUENTID uniqueidentifier = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    select distinct
                        NAMINGOPPORTUNITYRECOGNITION.ID,
                        NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTNAME,
                        NAMINGOPPORTUNITYRECOGNITION.STARTDATE,
                        NAMINGOPPORTUNITYRECOGNITION.ENDDATE,
                        NAMINGOPPORTUNITYRECOGNITION.QUANTITY,
                        NAMINGOPPORTUNITYRECOGNITION.AMOUNT,
                        NAMINGOPPORTUNITYRECOGNITION.INSCRIPTION,
                        NAMINGOPPORTUNITYRECOGNITION.SPECIALREQUEST,
                        NAMINGOPPORTUNITYRECOGNITION.NAMINGOPPORTUNITYID,
                        NAMINGOPPORTUNITY.NAME,
                        NAMINGOPPORTUNITYRECOGNITION.BASECURRENCYID
                    from
                        dbo.NAMINGOPPORTUNITYRECOGNITION
                        inner join NAMINGOPPORTUNITY on NAMINGOPPORTUNITYRECOGNITION.NAMINGOPPORTUNITYID = NAMINGOPPORTUNITY.ID
                        left join NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.NAMINGOPPORTUNITYRECOGNITIONID = NAMINGOPPORTUNITYRECOGNITION.ID
                    where NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTID = @CONSTITUENTID                         
                        and (NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID is null or (
                            (
                                select count(*) 
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID) REVSITES 
                                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                            ) > 0
                            and    (
                                @SITEFILTERMODE = 0
                                    or exists(
                                        select 1
                                        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                            inner join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID) REVSITES on REVSITES.SITEID = SITEFILTER.SITEID
                                    )
                            )))
                    order by
                        NAMINGOPPORTUNITYRECOGNITION.STARTDATE desc