USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTCORRESPONDENCEPREVIEWAPPEALMAILING

The load procedure used by the view dataform template "Constituent Correspondence Preview Appeal Mailing View Form"

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.
@DATESENT	datetime	INOUT	Date sent
@CORRESPONDENCETYPE	nvarchar(100)	INOUT	Communication
@DETAILS	nvarchar(100)	INOUT	Details
@CATEGORY	nvarchar(100)	INOUT	Category
@ADDEDBY	nvarchar(128)	INOUT	Added By
@COMMENT	nvarchar(255)	INOUT	Comments
@RESPONSES	xml	INOUT	Responses
@SITE	nvarchar(1024)	INOUT	Site
@BOUNCED	bit	INOUT
@SPAMCOMPLAINT	bit	INOUT
@BOUNCETEXT	nvarchar(1000)	INOUT
@EMAILADDRESS	nvarchar(250)	INOUT
@OPTEDOUT	bit	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTCORRESPONDENCEPREVIEWAPPEALMAILING(
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @DATESENT datetime = null output,
                    @CORRESPONDENCETYPE nvarchar(100) = null output,
                    @DETAILS nvarchar(100) = null output,
                    @CATEGORY nvarchar(100) = null output,
                    @ADDEDBY nvarchar(128) = null output,
                    @COMMENT nvarchar(255) = null output,
                    @RESPONSES xml = null output,
                    @SITE nvarchar(1024) = null output,
                    @BOUNCED bit = null output,
                    @SPAMCOMPLAINT bit = null  output,
                    @BOUNCETEXT nvarchar(1000) = null output,
                    @EMAILADDRESS nvarchar(250) = null output,
                    @OPTEDOUT bit = null output
                ) as begin
                    set nocount on;

                    set @DATALOADED = 0;
                    declare @MSID uniqueidentifier;
                    declare @CONSTITUENTID uniqueidentifier;
                    declare @NOTSENT bit;

                    select 
                        @MSID = [MKTSEGMENTATIONID],
                        @CONSTITUENTID =CONSTITUENTID 
                    from dbo.CONSTITUENTAPPEAL where ID = @ID;

                    --Get Email recipient bounce/spam complaint status

                    exec USP_GET_CONSTITUENT_APPEALEMAILSTATUS_FORGIVENMARKETINGSEGMENT  
                        @CONSTITUENTID = @CONSTITUENTID,
                        @MKTSEGMENTATIONID = @MSID, 
                        @BOUNCE = @BOUNCED output,
                        @SPAMCOMPLAINT = @SPAMCOMPLAINT output,
                        @ERRORMESSAGE = @BOUNCETEXT output,
                        @EMAILADDRESS = @EMAILADDRESS output,
                        @OPTEDOUT  = @OPTEDOUT output,
                        @ISNOTSENT = @NOTSENT output;

                    select
                        @DATALOADED = 1,
                        @DATESENT = case when @NOTSENT = 1 then null else CA.DATESENT end,
                        @CORRESPONDENCETYPE = case MS.MAILINGTYPECODE 
                                                when 2 then 'Membership Renewal'
                                                else 'Appeal Mailing' 
                                                end,
                        @DETAILS = A.[NAME] + case when A.[NAME] is not null and A.[NAME] <> '' and MS.[NAME] is not null and MS.[NAME] <> '' then ' - ' else '' end + coalesce(MS.[NAME], ''),
                        @CATEGORY = '',
                        @ADDEDBY = CHA.USERNAME,
                        @COMMENT = CA.COMMENTS,
                        @RESPONSES = dbo.UFN_CONSTITUENTAPPEAL_GETRESPONSES_TOITEMLISTXML(CA.ID),
                        @SITE = coalesce(dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME(A.SITEID), 'All sites')                        
                    from 
                        dbo.[CONSTITUENTAPPEAL] CA
                        left join dbo.[APPEAL] A on A.[ID] = CA.[APPEALID]
                        left join dbo.[MKTSEGMENTATION] MS on MS.[ID] = CA.[MKTSEGMENTATIONID]
                        left join dbo.[CHANGEAGENT] CHA on CHA.[ID] = CA.[ADDEDBYID]
                    where
                        CA.ID = @ID; 

                    return 0;

                end