USP_DATAFORMTEMPLATE_VIEW_COAUPDATEPAGEDATA

The load procedure used by the view dataform template "AddressFinder Page Expression 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.
@FILEUPLOADED	bit	INOUT	File uploaded
@FILEAVAILABLE	bit	INOUT	File available
@FILEDOWNLOADED	bit	INOUT	File downloaded
@COAUPDATESUBMITPROCESSID	uniqueidentifier	INOUT	AddressFinder submit process ID
@COAUPDATEGETPROCESSID	uniqueidentifier	INOUT	AddressFinder get process ID
@BATCHID	uniqueidentifier	INOUT	Batch ID
@BATCHSTATUSCODE	tinyint	INOUT	Batch status code
@STEPCODE	tinyint	INOUT	Step code
@NAME	nvarchar(150)	INOUT	Name
@DESCRIPTION	nvarchar(250)	INOUT	Description
@SITEID	uniqueidentifier	INOUT	Site ID
@SITENAME	nvarchar(150)	INOUT	Site name
@PAGECAPTION	nvarchar(300)	INOUT	Page caption
@CASSRPTFILENAME	nvarchar(255)	INOUT	File name
@CASSRPTFILE	varbinary	INOUT	File
@NCOARPTFILENAME	nvarchar(255)	INOUT	File name
@NCOARPTFILE	varbinary	INOUT	File
@ALLOWCOMMIT	bit	INOUT	Allow commit
@CASSRPTFILEAVAILABLE	bit	INOUT	CASSRPTFILEAVAILABLE
@NCOARPTFILEAVAILABLE	bit	INOUT	NCOARPTFILEAVAILABLE
@CONTROLREPORTVISIBLE	bit	INOUT
@EXCEPTIONREPORTVISIBLE	bit	INOUT
@DATATUNEUPID	uniqueidentifier	INOUT
@COAUPDATECREATEPROCESSID	uniqueidentifier	INOUT

Definition

Copy



        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COAUPDATEPAGEDATA
        (
          @ID uniqueidentifier,
          @DATALOADED bit = 0 output,
          @FILEUPLOADED bit = null output,
          @FILEAVAILABLE bit = null output,
          @FILEDOWNLOADED bit = null output,
          @COAUPDATESUBMITPROCESSID uniqueidentifier = null output,
          @COAUPDATEGETPROCESSID uniqueidentifier = null output,
          @BATCHID uniqueidentifier = null output,
          @BATCHSTATUSCODE tinyint = null output,
          @STEPCODE tinyint = null output,
          @NAME nvarchar(150) = null output,
          @DESCRIPTION nvarchar(250) = null output,
          @SITEID uniqueidentifier = null output,
          @SITENAME nvarchar(150) = null output,
          @PAGECAPTION nvarchar(300) = null output,
          @CASSRPTFILENAME nvarchar(255) = null output,
          @CASSRPTFILE varbinary(max) = null output,
          @NCOARPTFILENAME nvarchar(255) = null output,
          @NCOARPTFILE varbinary(max) = null output,
          @ALLOWCOMMIT bit = null output,
          @CASSRPTFILEAVAILABLE bit = null output,
          @NCOARPTFILEAVAILABLE bit = null output,
          @CONTROLREPORTVISIBLE bit = null output,
          @EXCEPTIONREPORTVISIBLE bit = null output,
          @DATATUNEUPID uniqueidentifier = null output,
          @COAUPDATECREATEPROCESSID uniqueidentifier = null output
        )
        as
          set nocount on;

          set @DATALOADED = 0;

          select @DATALOADED = 1,
            @FILEUPLOADED = COAUPDATE.FILEUPLOADED,
            @FILEAVAILABLE = COAUPDATE.FILEAVAILABLE,
            @FILEDOWNLOADED = COAUPDATE.FILEDOWNLOADED,
            @COAUPDATESUBMITPROCESSID = SUBMIT.ID,
            @COAUPDATEGETPROCESSID = GET.ID,
            @BATCHID = COAUPDATE.BATCHID,
            @BATCHSTATUSCODE = dbo.UFN_BATCH_GETSTATUSCODE(COAUPDATE.BATCHID),
            @STEPCODE = COAUPDATE.STEPCODE,
            @NAME = COAUPDATE.NAME,
            @DESCRIPTION = COAUPDATE.DESCRIPTION,
            @SITEID = COAUPDATE.SITEID,
            @SITENAME = SITE.NAME,
            @PAGECAPTION = coalesce(SITE.NAME + ' - ', '') + coalesce(COAUPDATE.NAME, ''),
            @CASSRPTFILENAME = 'CASS_AddressFinderSummary.pdf',
            @CASSRPTFILE = COAUPDATEFILE.CASSRPTFILE,
            @NCOARPTFILENAME = 'NCOARPT.pdf',
            @NCOARPTFILE = COAUPDATEFILE.NCOARPTFILE,
            @CASSRPTFILEAVAILABLE = (case when COAUPDATEFILE.CASSRPTFILE is null then 0 else 1 end),
            @NCOARPTFILEAVAILABLE = 0,
            @COAUPDATECREATEPROCESSID = [COAUPDATECREATEPROCESS].[ID]
          from dbo.COAUPDATE
          inner join dbo.COAUPDATESUBMITPROCESS SUBMIT on SUBMIT.COAUPDATEID = COAUPDATE.ID
          inner join dbo.COAUPDATEGETPROCESS GET on GET.COAUPDATEID = COAUPDATE.ID
          inner join dbo.COAUPDATEFILE on COAUPDATE.ID = COAUPDATEFILE.ID
          inner join dbo.[COAUPDATECREATEPROCESS] on [COAUPDATE].[ID] = [COAUPDATECREATEPROCESS].[COAUPDATEID]
          left join dbo.SITE on COAUPDATE.SITEID = SITE.ID
          where COAUPDATE.ID = @ID;

          if @DATALOADED = 1
            select @ALLOWCOMMIT = BWS.ALLOWCOMMIT
            from dbo.BATCHWORKFLOWSTATE BWS
            inner join dbo.BATCHWORKFLOW BW on BW.ID = BWS.BATCHWORKFLOWID
            where BW.BATCHTYPECATALOGID = '721c4686-8574-4456-a9af-4da79d94a0e6'

          declare @CONTROLREPORTID uniqueidentifier;
          declare @EXCEPTIONREPORTID uniqueidentifier;

          select
            @CONTROLREPORTID = BATCHTYPECATALOG.CONTROLREPORTREPORTSPECID,
            @EXCEPTIONREPORTID = BATCHTYPECATALOG.EXCEPTIONREPORTREPORTSPECID
          from dbo.BATCH 
          left join dbo.BATCHTEMPLATE 
            on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
          left join dbo.BATCHTYPECATALOG 
            on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
          where BATCH.ID = @BATCHID;

          if exists (select 1 from dbo.BUSINESSPROCESSOUTPUT inner join dbo.BATCHSTATUS on BATCHSTATUS.PARAMETERSETID = @BATCHID and BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID and BUSINESSPROCESSOUTPUT.TABLEKEY = 'CONTROL') and @BATCHSTATUSCODE > 0 and len(@CONTROLREPORTID) > 0
            set @CONTROLREPORTVISIBLE = 1;
          else
            set @CONTROLREPORTVISIBLE = 0;

          if exists (select 1 from dbo.BUSINESSPROCESSOUTPUT inner join dbo.BATCHSTATUS on BATCHSTATUS.PARAMETERSETID = @BATCHID and BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID and BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION') and @BATCHSTATUSCODE > 0 and len(@EXCEPTIONREPORTID) > 0
            set @EXCEPTIONREPORTVISIBLE = 1;
          else
            set @EXCEPTIONREPORTVISIBLE = 0;    

          select top(1)
            @DATATUNEUPID = DATATUNEUP.ID
          from dbo.DATATUNEUP
          order by DATATUNEUP.DATEADDED;

          return 0;