USP_DATALIST_PROSPECTFUNDINGINTERESTS

Returns the prospect's funding interests.

Parameters

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

                    select 
                        PROSPECTFUNDINGINTEREST.ID,
                        dbo.UFN_FUNDINGTYPECODE_GETDESCRIPTION(PROSPECTFUNDINGINTEREST.FUNDINGTYPECODEID) as FUNDINGTYPE,
                        PROSPECTFUNDINGINTEREST.FUNDINGTYPECODEID,
                        PROSPECTFUNDINGINTEREST.INTERESTLEVEL,
                        PROSPECTFUNDINGINTEREST.COMMENT,
                        DBO.UFN_PROSPECTFUNDINGINTEREST_GETSITELIST(PROSPECTFUNDINGINTEREST.ID) as SITE
                    from 
                        dbo.PROSPECTFUNDINGINTEREST
                    where 
                        PROSPECTFUNDINGINTEREST.PROSPECTID = @PROSPECTID
                        and
                        (
                            select count(*) 
                            from dbo.UFN_SITEID_MAPFROM_PROSPECTFUNDINGINTERESTID(PROSPECTFUNDINGINTEREST.ID) as PROSPECTFUNDINGINTERESTSITE 
                            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTFUNDINGINTERESTSITE].[SITEID] or (SITEID is null and [PROSPECTFUNDINGINTERESTSITE].[SITEID] is null)))
                        ) > 0
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                                PROSPECTFUNDINGINTEREST.ID in 
                                (
                                    select PROSPECTFUNDINGINTERESTSITE.PROSPECTFUNDINGINTERESTID
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                    inner join dbo.PROSPECTFUNDINGINTERESTSITE on PROSPECTFUNDINGINTERESTSITE.SITEID = SITEFILTER.SITEID
                                )
                        )
                    order by 
                        FUNDINGTYPE