USP_DATAFORMTEMPLATE_VIEW_IMPORTPROCESS

The load procedure used by the view dataform template "Import 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.
@NAME	nvarchar(255)	INOUT	Name
@DESCRIPTION	nvarchar(1000)	INOUT	Description
@FILENAME	nvarchar(255)	INOUT	File name
@BATCHTEMPLATEID	uniqueidentifier	INOUT	Batch process instance ID
@BATCHTEMPLATE	nvarchar(255)	INOUT	Batch process instance
@BUSINESSPROCESSCATALOGID	uniqueidentifier	INOUT	Business process catalog ID
@LASTBUSINESSPROCESSSTATUSID	uniqueidentifier	INOUT	Last business process status ID
@LASTBATCHID	uniqueidentifier	INOUT	Last batch ID
@LASTBATCHNUMBER	nvarchar(100)	INOUT	Last batch number
@LASTEXCEPTIONCOUNT	int	INOUT	Last exception count
@PARAMETERSID	uniqueidentifier	INOUT	Parameters ID
@USEWILDCARDS	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_IMPORTPROCESS(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(255) = null output,
    @DESCRIPTION nvarchar(1000) = null output,
    @FILENAME nvarchar(255) = null output,
    @BATCHTEMPLATEID uniqueidentifier = null output,
    @BATCHTEMPLATE nvarchar(255) = null output,
    @BUSINESSPROCESSCATALOGID uniqueidentifier = null output,
    @LASTBUSINESSPROCESSSTATUSID uniqueidentifier = null output,
    @LASTBATCHID uniqueidentifier = null output,
    @LASTBATCHNUMBER nvarchar(100) = null output,
    @LASTEXCEPTIONCOUNT int = null output,
    @PARAMETERSID uniqueidentifier = null output,
  @USEWILDCARDS bit = null output)
with execute as caller
as
    set nocount on;

    begin try

        set @DATALOADED = 0

        select @DATALOADED = 1,
            @NAME = IMPORTPROCESS.NAME,
            @DESCRIPTION = IMPORTPROCESS.DESCRIPTION,
            @FILENAME = IMPORTPROCESS.FILENAME,
            @BATCHTEMPLATEID = IMPORTPROCESS.BATCHTEMPLATEID,
            @BATCHTEMPLATE = BATCHTEMPLATE.NAME,
            @PARAMETERSID = IMPORTPROCESS.ID,
      @USEWILDCARDS = IMPORTPROCESS.USEWILDCARDS
        from dbo.IMPORTPROCESS
        inner join dbo.BATCHTEMPLATE on IMPORTPROCESS.BATCHTEMPLATEID = BATCHTEMPLATE.ID
        where IMPORTPROCESS.ID = @ID;

        set @BUSINESSPROCESSCATALOGID = 'C5B4AF35-5239-4380-A784-BF2336D9CB7C';

        select top(1) @LASTBUSINESSPROCESSSTATUSID = BUSINESSPROCESSSTATUS.ID,
            @LASTEXCEPTIONCOUNT = (select count(*) from dbo.IMPORTPROCESSEXCEPTION where BUSINESSPROCESSSTATUSID = BUSINESSPROCESSSTATUS.ID),
      @BUSINESSPROCESSCATALOGID = BUSINESSPROCESSCATALOGID
            from dbo.BUSINESSPROCESSSTATUS 
            inner join dbo.IMPORTPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID = IMPORTPROCESSSTATUS.ID
            inner join dbo.IMPORTPROCESS on IMPORTPROCESSSTATUS.PARAMETERSETID = IMPORTPROCESS.ID
        where BUSINESSPROCESSCATALOGID in ('C5B4AF35-5239-4380-A784-BF2336D9CB7C', '2873772C-37BF-4C20-855D-49BE32F186CD')
        and IMPORTPROCESS.ID = @ID order by STARTEDON desc;

        if @LASTBUSINESSPROCESSSTATUSID is null
        begin
            set @LASTBUSINESSPROCESSSTATUSID = '00000000-0000-0000-0000-000000000000'
        end
        else
        begin
            select top(1) @LASTBATCHID = IMPORTPROCESSBATCH.BATCHID, 
                @LASTBATCHNUMBER = BATCH.BATCHNUMBER
                from dbo.IMPORTPROCESSBATCH
                left join dbo.BATCH on IMPORTPROCESSBATCH.BATCHID = BATCH.ID
                where IMPORTPROCESSBATCH.BUSINESSPROCESSSTATUSID = @LASTBUSINESSPROCESSSTATUSID;
        end;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch;

    return 0;