USP_DATAFORMTEMPLATE_VIEW_ACKNOWLEDGEMENTSUMMARY

The load procedure used by the view dataform template "Acknowledgement Summary View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ACTIVATING	bit	INOUT	ACTIVATING
@ACTIVATEMAILINGPROCESSID	uniqueidentifier	INOUT	ACTIVATEMAILINGPROCESSID
@STARTEDON	datetime	INOUT	Last run on
@NUMBERPROCESSED	int	INOUT	Records processed
@MESSAGE	nvarchar(max)	INOUT	Status message
@LETTERCOUNT	int	INOUT	LETTERCOUNT
@HASLETTERSELECTIONS	bit	INOUT
@HASINCOMPLETEEMAILLETTER	bit	INOUT
@UPDATECOUNTPARAMETERSETID	uniqueidentifier	INOUT
@ACTIVE	bit	INOUT
@HASCOMPLETELETTER	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ACKNOWLEDGEMENTSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ACTIVATING bit = null output,
    @ACTIVATEMAILINGPROCESSID uniqueidentifier = null output,
    @STARTEDON datetime = null output,
    @NUMBERPROCESSED int = null output,
    @MESSAGE nvarchar(max) = null output,
    @LETTERCOUNT int = null output,
    @HASLETTERSELECTIONS bit = null output,
    @HASINCOMPLETEEMAILLETTER bit = null output,
    @UPDATECOUNTPARAMETERSETID uniqueidentifier = null output,
    @ACTIVE bit = null output,
    @HASCOMPLETELETTER bit = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @ACTIVE = MKTSEGMENTATION.ACTIVE,
        @ACTIVATEMAILINGPROCESSID = MKTSEGMENTATIONACTIVATEPROCESS.ID,
        @UPDATECOUNTPARAMETERSETID = MKTSEGMENTATIONSEGMENTCALCULATEPROCESS.ID
    from dbo.MKTSEGMENTATION 
    inner join dbo.MKTSEGMENTATIONACTIVATEPROCESS 
        on MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = MKTSEGMENTATION.ID
    inner join dbo.MKTSEGMENTATIONSEGMENTCALCULATEPROCESS
        on MKTSEGMENTATION.ID = MKTSEGMENTATIONSEGMENTCALCULATEPROCESS.SEGMENTATIONID
    where MKTSEGMENTATION.ID = @ID;

    select top(1)        
        @ACTIVATING = case when BUSINESSPROCESSSTATUS.STATUSCODE = 1 then 1 else 0 end,
        @STARTEDON = BUSINESSPROCESSSTATUS.STARTEDON,
        @NUMBERPROCESSED = BUSINESSPROCESSSTATUS.NUMBERPROCESSED,
        @MESSAGE = case when BUSINESSPROCESSSTATUS.STATUSCODE = 0 then BUSINESSPROCESSSTATUS.STATUS else BUSINESSPROCESSSTATUS.ERRORMESSAGE end,
        @HASLETTERSELECTIONS = case when exists(select COMMUNICATIONLETTER.ID from dbo.COMMUNICATIONLETTER left outer join dbo.COMMUNICATIONLETTERSELECTION on COMMUNICATIONLETTER.ID = COMMUNICATIONLETTERSELECTION.COMMUNICATIONLETTERID and COMMUNICATIONLETTERSELECTION.FILTERTYPECODE = 0 where COMMUNICATIONLETTER.SEGMENTATIONID = @ID and COMMUNICATIONLETTERSELECTION.ID is not null) then 1 else 0 end,
        @HASINCOMPLETEEMAILLETTER = 
            case when exists(
                        select COMMUNICATIONLETTER.ID 
                        from dbo.COMMUNICATIONLETTER 
                        where COMMUNICATIONLETTER.SEGMENTATIONID = @ID 
                            and COMMUNICATIONLETTER.CHANNELCODE > 0 
                            and (len(COMMUNICATIONLETTER.EMAILSUBJECT) = 0 
                            or len(COMMUNICATIONLETTER.EMAILFROMADDRESS) = 0 
                            or len(COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME) = 0 
                            or len(COMMUNICATIONLETTER.EMAILCONTENTHTML) = 0)) 
                            then 1 
                        else 0 
            end,
        @HASCOMPLETELETTER = 
            case 
                when exists (
                    select COMMUNICATIONLETTER.ID
                    from dbo.COMMUNICATIONLETTER
                    inner join dbo.COMMUNICATIONLETTERSELECTION
                        on COMMUNICATIONLETTER.ID = COMMUNICATIONLETTERSELECTION.COMMUNICATIONLETTERID
                    where COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID
                        and COMMUNICATIONLETTER.CHANNELCODE = 0
                        or (
                            len(COMMUNICATIONLETTER.EMAILSUBJECT) > 0  
                            and len(COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME) > 0 
                            and len(COMMUNICATIONLETTER.EMAILFROMADDRESS) > 0 
                            and len(COMMUNICATIONLETTER.EMAILCONTENTHTML) > 0
                        )
                    )
                    then 1
                else 0
            end
    from dbo.MKTSEGMENTATIONACTIVATEPROCESS
    left outer join dbo.MKTSEGMENTATIONACTIVATEPROCESSSTATUS 
        on MKTSEGMENTATIONACTIVATEPROCESSSTATUS.PARAMETERSETID = MKTSEGMENTATIONACTIVATEPROCESS.ID    
    left outer join dbo.BUSINESSPROCESSSTATUS 
        on BUSINESSPROCESSSTATUS.ID = MKTSEGMENTATIONACTIVATEPROCESSSTATUS.ID
    where MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = @ID
    order by
        BUSINESSPROCESSSTATUS.STARTEDON desc;

    select
        @LETTERCOUNT = count(ID)
    from
        dbo.COMMUNICATIONLETTER
    where
        SEGMENTATIONID = @ID;

    return 0;