USP_DATAFORMTEMPLATE_VIEW_ADDRESSVALIDATIONPAGEDATA

The load procedure used by the view dataform template "Address Validation 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.
@ADDRESSVALIDATIONPROCESSID	uniqueidentifier	INOUT	Address validation 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
@VALIDATIONCOMPLETE	bit	INOUT	Validation complete
@ALLOWCOMMIT	bit	INOUT	Allow commit
@EXCEPTIONREPORTVISIBLE	bit	INOUT
@DATATUNEUPID	uniqueidentifier	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADDRESSVALIDATIONPAGEDATA
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ADDRESSVALIDATIONPROCESSID 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,
    @VALIDATIONCOMPLETE bit = null output,
    @ALLOWCOMMIT bit = null output,
    @EXCEPTIONREPORTVISIBLE bit = null output,
    @DATATUNEUPID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @EXCEPTIONREPORTID uniqueidentifier;

    select @DATALOADED = 1,
        @ADDRESSVALIDATIONPROCESSID = AVP.ID,
        @BATCHID = AV.BATCHID,
        @BATCHSTATUSCODE = dbo.UFN_BATCH_GETSTATUSCODE(AV.BATCHID),
        @VALIDATIONCOMPLETE = AV.VALIDATIONCOMPLETE,
        @STEPCODE = AV.STEPCODE,
        @NAME = AV.NAME,
        @DESCRIPTION = AV.DESCRIPTION,
        @SITEID = AV.SITEID,
        @SITENAME = SITE.NAME,
        @PAGECAPTION = coalesce(SITE.NAME + ' - ', '') + coalesce(AV.NAME, ''),
        @ALLOWCOMMIT = BWS.ALLOWCOMMIT,
        @EXCEPTIONREPORTID = BATCHTYPECATALOG.EXCEPTIONREPORTREPORTSPECID
    from dbo.ADDRESSVALIDATION AV
    inner join dbo.ADDRESSVALIDATIONPROCESS AVP 
        on AVP.ADDRESSVALIDATIONID = AV.ID
    left join dbo.SITE 
        on AV.SITEID = SITE.ID
    inner join dbo.BATCH B 
        on AV.BATCHID = B.ID
    inner join dbo.BATCHWORKFLOWSTATE BWS 
        on B.BATCHWORKFLOWSTATEID = BWS.ID
    left join dbo.BATCHTEMPLATE 
        on B.BATCHTEMPLATEID = BATCHTEMPLATE.ID
    left join dbo.BATCHTYPECATALOG 
        on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
    where AV.ID = @ID;

    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;