USP_DATAFORMTEMPLATE_VIEW_COAUPDATESUBMITPROCESS

The load procedure used by the view dataform template "AddressFinder Submit Process 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.
@DATELASTRUN	datetime	INOUT	Date last run
@DESCRIPTION	nvarchar(250)	INOUT	Description
@STEP	nvarchar(100)	INOUT	Step
@STEPCODE	tinyint	INOUT	Step
@SELECTIONNAME	nvarchar(250)	INOUT	Selected constituents
@ADDRESSINCLUDECODE	tinyint	INOUT	Address include code
@ADDRESSTYPECODE	nvarchar(250)	INOUT	Address type code
@CREATEOUTPUTIDSET	bit	INOUT	Create output selection
@OUTPUTIDSETNAME	nvarchar(100)	INOUT	Output selection name
@OVERWRITEOUTPUTIDSET	bit	INOUT	Overwrite existing selection
@PARAMETERSID	uniqueidentifier	INOUT	Parameterset ID
@FILEUPLOADED	bit	INOUT	File uploaded
@FILEDOWNLOADED	bit	INOUT	File downloaded
@COAUPDATEID	uniqueidentifier	INOUT	Change of address update ID
@COAUPDATERESUBMITPROCESSID	uniqueidentifier	INOUT	AddressFinder submit process ID
@PROCESSCAPTION	nvarchar(300)	INOUT	Process caption
@NUMBERRECORDSTOSEND	int	INOUT

Definition

Copy



        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COAUPDATESUBMITPROCESS
        (
          @ID uniqueidentifier,
          @DATALOADED bit = 0 output,
          @DATELASTRUN datetime = null output,
          @DESCRIPTION nvarchar(250) = null output,
          @STEP nvarchar(100) = null output,
          @STEPCODE tinyint = null output,
          @SELECTIONNAME nvarchar(250) = null output,
          @ADDRESSINCLUDECODE tinyint = null output,
          @ADDRESSTYPECODE nvarchar(250) = null output,
          @CREATEOUTPUTIDSET bit = null output,
          @OUTPUTIDSETNAME nvarchar(100) = null output,
          @OVERWRITEOUTPUTIDSET bit = null output,
          @PARAMETERSID uniqueidentifier = null output,
          @FILEUPLOADED bit = null output,
          @FILEDOWNLOADED bit = null output,
          @COAUPDATEID uniqueidentifier = null output,
          @COAUPDATERESUBMITPROCESSID uniqueidentifier = null output,
          @PROCESSCAPTION nvarchar(300) = null output,
          @NUMBERRECORDSTOSEND int = null output
        )
        as
          set nocount on;
          declare @COAUPDATECREATEPROCESSID uniqueidentifier;

          set @DATALOADED = 0;

          select @DATALOADED = 1,
            @DATELASTRUN = COAUPDATE.DATELASTRUN,
            @DESCRIPTION = COAUPDATE.DESCRIPTION,
            @STEP = COAUPDATE.STEP,
            @STEPCODE = COAUPDATE.STEPCODE,
            @SELECTIONNAME = coalesce(dbo.UFN_IDSET_GETNAME([CREATE].IDSETREGISTERID), 'All constituents'),
            @ADDRESSINCLUDECODE = [CREATE].ADDRESSINCLUDECODE,
            @ADDRESSTYPECODE = dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION([CREATE].ADDRESSTYPECODEID),
            @CREATEOUTPUTIDSET = [CREATE].CREATEOUTPUTIDSET,
            @OUTPUTIDSETNAME = [CREATE].OUTPUTIDSETNAME,
            @OVERWRITEOUTPUTIDSET = [CREATE].OVERWRITEOUTPUTIDSET,
            @PARAMETERSID = @ID,
            @FILEUPLOADED = COAUPDATE.FILEUPLOADED,
            @FILEDOWNLOADED = COAUPDATE.FILEDOWNLOADED,
            @COAUPDATEID = COAUPDATE.ID,
            @COAUPDATERESUBMITPROCESSID = RESUBMIT.ID,
            @PROCESSCAPTION = coalesce(SITE.NAME + ' - ', '') + coalesce(COAUPDATE.NAME, ''),
            @COAUPDATECREATEPROCESSID = [CREATE].[ID]
          from dbo.COAUPDATECREATEPROCESS [CREATE]
          inner join dbo.[COAUPDATESUBMITPROCESS] on [CREATE].[COAUPDATEID] = [COAUPDATESUBMITPROCESS].[COAUPDATEID]
          inner join dbo.COAUPDATE on COAUPDATE.ID = [CREATE].COAUPDATEID
          left join dbo.SITE on COAUPDATE.SITEID = SITE.ID
          inner join dbo.COAUPDATEGETPROCESS GET on COAUPDATE.ID = GET.COAUPDATEID
          inner join dbo.COAUPDATERESUBMITPROCESS RESUBMIT on RESUBMIT.COAUPDATEID = COAUPDATE.ID
          where COAUPDATESUBMITPROCESS.ID = @ID;

          if @DATALOADED = 1
            select 
              top 1 @NUMBERRECORDSTOSEND = isnull([NUMBERPROCESSED] ,0)
            from 
              dbo.[BUSINESSPROCESSSTATUS]
            where
              [STATUSCODE] = 0
              and
              [BUSINESSPROCESSCATALOGID] = '476deb56-3869-4216-9592-02ddae091f53'
              and
              [BUSINESSPROCESSPARAMETERSETID] = @COAUPDATECREATEPROCESSID
            order by
              [ENDEDON] desc

          return 0;