USP_DATALIST_ALLCONSTITUENTCORRESPONDENCE

This datalist returns all correspondence for a constituent.

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DATEFILTER	tinyint	IN	Date range
@CORRESPONDENCETYPECODE	smallint	IN	Communication
@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.
@CHANNEL	smallint	IN	Channel

Definition

Copy



CREATE procedure dbo.USP_DATALIST_ALLCONSTITUENTCORRESPONDENCE
(
  @CONSTITUENTID uniqueidentifier,
  @DATEFILTER tinyint = null,
  @CORRESPONDENCETYPECODE smallint = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CHANNEL smallint = null
)
as
  set nocount on;

  if @DATEFILTER is null
  begin
    set @DATEFILTER = 9;  -- last 12 months;

  end

  if @CORRESPONDENCETYPECODE is null
  begin
    set @CORRESPONDENCETYPECODE = 99; -- all;

  end

  if @CHANNEL is null
  begin
    set @CHANNEL = 99;
  end

  declare @STARTDATE datetime;
  declare @ENDDATE datetime;

  exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER=@DATEFILTER, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output;

  select
    ID,
    DATESENT,
    CORRESPONDENCETYPECODE,
    CORRESPONDENCETYPE,
    DETAILS,
    PROCESSOR,
    COMMENTS,
    RECORDID,
    FINDERNUMBER,
    CONSTITUENTID,
    HASRESPONSES,
    PREVIEWDATAFORM,
    PREVIEWDATAFORMRECORDID,
    TIMEFRAMETEXT,
    TIMEFRAMEGROUPSORT,
    SITE,
    CHANNEL,
    NOTSENT,
   case  when NOTSENT = 1  
                then 'RES:warning'
            else
                'RES:lv_spacer'
        end as IMAGEKEY
  from
    dbo.UFN_CONSTITUENT_CORRESPONDENCE(@CONSTITUENTID, @CURRENTAPPUSERID, @SITEFILTERMODE,
      @SITESSELECTED, @SECURITYFEATUREID, @SECURITYFEATURETYPE, @CORRESPONDENCETYPECODE,
      @STARTDATE, @ENDDATE, @CHANNEL)
  order by
    TIMEFRAMEGROUPSORT asc, DATESENT desc, CORRESPONDENCETYPECODE;