USP_DATALIST_CONSTITUENTNAMEFORMATS_2

This datalist returns all of the name formats 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_CONSTITUENTNAMEFORMATS_2
                (
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    select
                        NAMEFORMAT.FORMATTEDNAME,
                        NAMEFORMATTYPECODE.DESCRIPTION,
                        NAMEFORMAT.SEQUENCE,
                        NAMEFORMAT.PRIMARYADDRESSEE,
                        NAMEFORMAT.PRIMARYSALUTATION,
                        NAMEFORMAT.ID,
                        coalesce(SITE.NAME, 'All sites') as SITENAME,
            NAMEFORMAT.NAMEFORMATFUNCTIONID,
            NAMEFORMAT.NAMEFORMATTYPECODEID
                    from
                        dbo.NAMEFORMAT 
                        left join dbo.NAMEFORMATTYPECODE on NAMEFORMATTYPECODE.ID = NAMEFORMAT.NAMEFORMATTYPECODEID
                        left join dbo.SITE on SITE.ID = NAMEFORMAT.SITEID
                    where
                        NAMEFORMAT.CONSTITUENTID = @CONSTITUENTID and
                        (
                            NAMEFORMAT.SITEID is null 
                            or (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[NAMEFORMAT].[SITEID] or (SITEID is null and [NAMEFORMAT].[SITEID] is null))) 
                        ) and
                        (
                            @SITEFILTERMODE = 0
                                or NAMEFORMAT.SITEID in ( 
                                    select SITEID
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
                                )
                        )

                    order by
                        SEQUENCE;