USP_DATALIST_MARRIAGECONSTITUENTNAMEFORMATS

This datalist returns all of the name formats for a constituent and spouse.

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_MARRIAGECONSTITUENTNAMEFORMATS
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  with [CONSTITUENTS_CTE] as
    (
      select @CONSTITUENTID CONSTITUENTID

      union all

      select RELATIONSHIP.RECIPROCALCONSTITUENTID  CONSTITUENTID
      from dbo.RELATIONSHIP 
      where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
    )
  select
    CONSTITUENT.ID CONSTITUENTID, 
    NF.NAME CONSTITUENTNAME, 
    NAMEFORMAT.FORMATTEDNAME,
    NAMEFORMATTYPECODE.DESCRIPTION,
    NAMEFORMAT.SEQUENCE,
    NAMEFORMAT.PRIMARYADDRESSEE,
    NAMEFORMAT.PRIMARYSALUTATION,
    NAMEFORMAT.ID,
    coalesce(SITE.NAME, 'All sites') as SITENAME
  from
    dbo.NAMEFORMAT 
    inner join CONSTITUENTS_CTE on NAMEFORMAT.CONSTITUENTID = CONSTITUENTS_CTE. CONSTITUENTID
    inner join dbo.CONSTITUENT on NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.NAMEFORMATTYPECODE on NAMEFORMATTYPECODE.ID = NAMEFORMAT.NAMEFORMATTYPECODEID
    left join dbo.SITE on SITE.ID = NAMEFORMAT.SITEID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
  where
    (
      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;