USP_DATAFORMTEMPLATE_VIEW_SIMPLEACKNOWLEDGEMENT

The load procedure used by the view dataform template "Acknowledgement 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.
@EXCLUSIONS	xml	INOUT	Solicit codes
@NAMEFORMATPARAMETEROPTION	nvarchar(100)	INOUT	Name format
@ADDRESSPROCESSINGOPTION	nvarchar(100)	INOUT	Address processing
@FREQUENCY	int	INOUT	FREQUENCY
@SCHEDULEMODIFIED	bit	INOUT	SCHEDULEMODIFIED
@INCLUDERECIPIENTS	bit	INOUT	Include recognition recipients
@LETTERS	xml	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SIMPLEACKNOWLEDGEMENT
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @EXCLUSIONS xml = null output,
    @NAMEFORMATPARAMETEROPTION nvarchar(100) = null output,
    @ADDRESSPROCESSINGOPTION nvarchar(100)= null output,
    @FREQUENCY int = null output,  
    @SCHEDULEMODIFIED bit = null output,
    @INCLUDERECIPIENTS bit = null output,
    @LETTERS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;    

  declare @MKTSEGMENTATIONACTIVATEPROCESSID nvarchar(36);    

    select 
    @MKTSEGMENTATIONACTIVATEPROCESSID = cast(ID as nvarchar(36)) 
    from 
    dbo.MKTSEGMENTATIONACTIVATEPROCESS 
    where 
    MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = @ID;

  select 
    @DATALOADED = 1,
    @EXCLUSIONS = dbo.UFN_SIMPLEACKNOWLEDGEMENT_GETSOLICITCODES_TOITEMLISTXML(MKTSEGMENTATION.ID),
    @NAMEFORMATPARAMETEROPTION = NAMEFORMATPARAMETER.NAME,
    @ADDRESSPROCESSINGOPTION = ADDRESSPROCESSINGOPTION.NAME,
    @FREQUENCY = isnull((select top 1 sysschedules.freq_type --weekly = 8 daily = 1

                                                from msdb.dbo.sysschedules 
                                                inner join msdb.dbo.sysjobschedules on sysschedules.schedule_id = sysjobschedules.schedule_id
                                                inner join msdb.dbo.sysjobsteps on sysjobschedules.job_id = sysjobsteps.job_id
                                                inner join msdb.dbo.sysjobs on sysjobschedules.job_id = sysjobs.job_id
                                                inner join msdb.dbo.sysproxies on sysjobsteps.proxy_id = sysproxies.proxy_id
                                                where sysproxies.name like 'Blackbaud % Proxy - ' + db_name()
                                                    and sysjobsteps.step_name = 'Business process invoke'
                                                    and (null is null or sysjobsteps.command like '%DatabaseName=' + null + '&%')
                                                    and sysjobsteps.command like '%' + @MKTSEGMENTATIONACTIVATEPROCESSID + '%'),0),
        @SCHEDULEMODIFIED = dbo.UFN_SIMPLEACKNOWLEDGEMENT_SCHEDULEMODIFIED(@MKTSEGMENTATIONACTIVATEPROCESSID),
        @INCLUDERECIPIENTS = isnull(SIMPLEACKNOWLEDGEMENT.INCLUDERECIPIENTS, 0),
        @LETTERS = dbo.UFN_COMMUNICATIONLETTER_GETSCHEDULEINFO_TOITEMLISTXML(MKTSEGMENTATION.ID)
    from 
    dbo.MKTSEGMENTATION
        left join dbo.NAMEFORMATPARAMETER on NAMEFORMATPARAMETER.ID = MKTSEGMENTATION.NAMEFORMATPARAMETERID
        left join dbo.ADDRESSPROCESSINGOPTION on ADDRESSPROCESSINGOPTION.ID = MKTSEGMENTATION.ADDRESSPROCESSINGOPTIONID
    left join dbo.MKTSEGMENTATIONACTIVATEPROCESS on MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = MKTSEGMENTATION.ID
    left join dbo.SIMPLEACKNOWLEDGEMENT on SIMPLEACKNOWLEDGEMENT.ID = MKTSEGMENTATION.ID
    where 
    MKTSEGMENTATION.ID = @ID;

    return 0;