USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTWHITEMAIL

The load procedure used by the view dataform template "White Mail Segment View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@QUERYVIEWCATALOGID	uniqueidentifier	INOUT	Record source ID
@QUERYVIEWCATALOG	nvarchar(100)	INOUT	Record source
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@SEGMENTCATEGORYCODE	nvarchar(100)	INOUT	Category
@STATUSCODE	tinyint	INOUT	Status code
@ACTIVEFROM	date	INOUT	Active from
@ACTIVETO	date	INOUT	to
@SOURCECODEID	uniqueidentifier	INOUT	Source code
@SOURCECODE	nvarchar(50)	INOUT	Source code
@CODEVALUEID	uniqueidentifier	INOUT	Code value ID
@CODE	nvarchar(10)	INOUT	Code
@GROUPS	nvarchar(4000)	INOUT	Groups
@RESPONDERS	int	INOUT	Responders
@RESPONSES	int	INOUT	Responses
@TOTALGIFTAMOUNT	money	INOUT	Total given
@AVERAGEGIFTAMOUNT	money	INOUT	Average gift
@DATEREFRESHED	datetime	INOUT	Current as of
@SEGMENTWHITEMAILREFRESHPROCESSID	uniqueidentifier	INOUT	Refresh process ID
@INUSE	bit	INOUT	In use?
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@ORGANIZATIONTOTALGIFTAMOUNT	money	INOUT	Total given
@ORGANIZATIONAVERAGEGIFTAMOUNT	money	INOUT	Average gift
@ORGANIZATIONCURRENCYID	uniqueidentifier	INOUT	Base currency
@CURRENCY	nvarchar(110)	INOUT	Currency
@SITEID	uniqueidentifier	INOUT	Site ID
@SITE	nvarchar(100)	INOUT	Site

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTWHITEMAIL]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @QUERYVIEWCATALOGID uniqueidentifier = null output,
  @QUERYVIEWCATALOG nvarchar(100) = null output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @SEGMENTCATEGORYCODE nvarchar(100) = null output,
  --@SEGMENTATIONID uniqueidentifier = null output,
  --@SEGMENTATION nvarchar(100) = null output,
  @STATUSCODE tinyint = null output,
  @ACTIVEFROM date = null output,
  @ACTIVETO date = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @SOURCECODE nvarchar(50) = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @CODE nvarchar(10) = null output,
  @GROUPS nvarchar(4000) = null output,
  @RESPONDERS integer = null output,
  @RESPONSES integer = null output,
  @TOTALGIFTAMOUNT money = null output,
  @AVERAGEGIFTAMOUNT money = null output,
  @DATEREFRESHED datetime = null output,
  @SEGMENTWHITEMAILREFRESHPROCESSID uniqueidentifier = null output,
  @INUSE bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @ORGANIZATIONTOTALGIFTAMOUNT money = null output,
  @ORGANIZATIONAVERAGEGIFTAMOUNT money = null output,
  @ORGANIZATIONCURRENCYID uniqueidentifier = null output,
  @CURRENCY nvarchar(110) = null output,
  @SITEID uniqueidentifier = null output,
  @SITE nvarchar(100) = null output
)
as
  set nocount on;

  exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENT_2]
    @ID = @ID,
    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
    @DATALOADED = @DATALOADED output,
    @NAME = @NAME output,
    @DESCRIPTION = @DESCRIPTION output,
    @SEGMENTCATEGORYCODEID = null,
    @CODE = @CODE output,
    @QUERYVIEWCATALOGID = @QUERYVIEWCATALOGID output,
    @SELECTIONS = null,
    @GROUPS = null,
    @SMARTQUERIESEXIST = null,
    @ISINUSE = null,
    @DATECHANGED = null,
    @ALLOWCODEUPDATE = null,
    @CODEVALUEID = @CODEVALUEID output,
    @TSLONG = null,
    @SITEID = @SITEID output,
    @SITEREQUIRED = null;

  if @DATALOADED = 1
    begin
      set @DATALOADED = 0;

      select
        @DATALOADED = 1,
        @QUERYVIEWCATALOG = [QUERYVIEWCATALOG].[DISPLAYNAME],
        --@SEGMENTATIONID = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID],
        --@SEGMENTATION = [MKTSEGMENTATION].[NAME],
        @SEGMENTCATEGORYCODE = [MKTSEGMENTCATEGORYCODE].[DESCRIPTION],
        @STATUSCODE = [MKTSEGMENTWHITEMAIL].[STATUSCODE],
        @ACTIVEFROM = [MKTSEGMENTWHITEMAIL].[ACTIVEFROM],
        @ACTIVETO = [MKTSEGMENTWHITEMAIL].[ACTIVETO],
        @SOURCECODEID = [MKTSEGMENTWHITEMAIL].[SOURCECODEID],
        @SOURCECODE = dbo.[UFN_MKTSOURCECODE_BUILDCODE](@ID, null, null),
        @GROUPS = (select stuff((select ', ' + [MKTSEGMENTGROUP].[NAME]
                                 from dbo.[MKTSEGMENTGROUP]
                                 inner join dbo.[MKTGROUPSEGMENTS] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
                                 where [MKTGROUPSEGMENTS].[SEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
                                 for xml path(''), type
                                 ).value('.', 'varchar(max)') , 1, 2, '')),
        @RESPONDERS = [MKTSEGMENTWHITEMAILCACHE].[RESPONDERS],
        @RESPONSES = [MKTSEGMENTWHITEMAILCACHE].[RESPONSES],
        @TOTALGIFTAMOUNT = [MKTSEGMENTWHITEMAILCACHE].[TOTALGIFTAMOUNT],
        @AVERAGEGIFTAMOUNT = [MKTSEGMENTWHITEMAILCACHE].[AVERAGEGIFTAMOUNT],
        @DATEREFRESHED = [MKTSEGMENTWHITEMAILREFRESHPROCESS].[DATEREFRESHED],
        @SEGMENTWHITEMAILREFRESHPROCESSID = [MKTSEGMENTWHITEMAILREFRESHPROCESS].[ID],
        @INUSE = convert(bit, case when (select isnull(sum([RESPONSES]), 0) from dbo.[MKTSEGMENTWHITEMAILCACHE] where [ID] = @ID) > 0 then 1 else 0 end),
        @BASECURRENCYID = [MKTSEGMENT].[BASECURRENCYID],
        @ORGANIZATIONTOTALGIFTAMOUNT = [MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONTOTALGIFTAMOUNT],
        @ORGANIZATIONAVERAGEGIFTAMOUNT = [MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONAVERAGEGIFTAMOUNT],
        @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY](),
        @CURRENCY = dbo.[UFN_CURRENCY_GETDESCRIPTION]([MKTSEGMENT].[BASECURRENCYID]),
        @SITE = [SITE].[NAME]
      from dbo.[MKTSEGMENTWHITEMAIL]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
      inner join dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS] on [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      left outer join dbo.[MKTSEGMENTWHITEMAILCACHE] on [MKTSEGMENTWHITEMAILCACHE].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
      --left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]
      left outer join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
      left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENT].[SITEID]
      where [MKTSEGMENTWHITEMAIL].[ID] = @ID;
    end

  return 0;