USP_DATALIST_LETTERTEMPLATE

Returns a list of letter templates.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ID	uniqueidentifier	IN
@SECURITYFEATUREID	uniqueidentifier	IN
@SECURITYFEATURETYPE	tinyint	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_LETTERTEMPLATE
(
  @CURRENTAPPUSERID uniqueidentifier,
  @ID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  select
    LETTERTEMPLATE.ID,
    LETTERTEMPLATE.NAME,
    LETTERTEMPLATE.DESCRIPTION,
    LETTERTEMPLATE.LETTERFILENAME,
    LETTERTEMPLATE.LETTERTEMPLATETYPE,
    coalesce(BUSINESSPROCESSVIEW.CAPTION, QUERYVIEWCATALOG.DISPLAYNAME) as QUERYVIEWNAME,
    SITE.NAME as SITENAME,
    [EXPORTDEFINITION].[NAME] [EXPORTDEFINITIONNAME],
    [DATAFORMINSTANCECATALOG].[ID] [VIEWDATAFORMTEMPLATEID],
    [EXPORTDEFINITION].[ID] [EXPORTDEFINITIONID]
  from
    dbo.LETTERTEMPLATE
  left join
    dbo.BUSINESSPROCESSVIEW on LETTERTEMPLATE.QUERYVIEWID = BUSINESSPROCESSVIEW.QUERYVIEWCATALOGID
  left join
    dbo.QUERYVIEWCATALOG on LETTERTEMPLATE.QUERYVIEWID = QUERYVIEWCATALOG.ID
  left join
    dbo.SITE on LETTERTEMPLATE.SITEID = SITE.ID
  left join
    dbo.[EXPORTDEFINITION] on [LETTERTEMPLATE].[EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]
  left join
    dbo.[DATAFORMINSTANCECATALOG] on [EXPORTDEFINITION].[VIEWDATAFORMTEMPLATEID] = [DATAFORMINSTANCECATALOG].[DATAFORMTEMPLATECATALOGID]
  where
    (@ID is null or [LETTERTEMPLATE].[ID] = @ID) and
    (
      (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[LETTERTEMPLATE].[SITEID] or (SITEID is null and [LETTERTEMPLATE].[SITEID] is null)))
    )
  order by
    LETTERTEMPLATE.DATEADDED;