USP_DATAFORMTEMPLATE_EDITLOAD_BATCHCOMMITRESUMEPARAMETERS

The load procedure used by the edit dataform template "Batch Commit Resume Process Parameter Edit 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.
@BATCHNUMBER	nvarchar(100)	INOUT	Batch number
@CREATEOUTPUTIDSET	bit	INOUT	Create output selection
@OUTPUTIDSETNAME	nvarchar(100)	INOUT	Selection name
@OVERWRITEOUTPUTIDSET	bit	INOUT	Overwrite existing selection
@CREATEEXCEPTIONBATCH	bit	INOUT	Create exception batch
@EXCEPTIONBATCHNAME	nvarchar(60)	INOUT	Exception batch number
@CREATECONTROLREPORT	bit	INOUT	Create control report
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@EXCEPTIONBATCHNUMBERCODE	tinyint	INOUT	Exception batch number code
@EXCEPTIONPREFIX	nvarchar(8)	INOUT	Exception prefix
@ORIGINALBATCHNUMBER	nvarchar(100)	INOUT	Original batch number
@EXCEPTIONLEVEL	int	INOUT	Exception level
@OVERRIDEBATCHNUMBER	bit	INOUT	Override
@USENUMBERINGSCHEME	bit	INOUT	Use numbering scheme
@LASTSEPARATOR	nvarchar(9)	INOUT	Last Separator
@EXCEPTIONBATCHISCHILD	bit	INOUT	Exception batch is child
@OUTPUTSELECTIONEXISTS	bit	INOUT	Output selection exists

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_BATCHCOMMITRESUMEPARAMETERS
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @BATCHNUMBER nvarchar(100) = null output,
  @CREATEOUTPUTIDSET bit = null output,
  @OUTPUTIDSETNAME nvarchar(100) = null output,
  @OVERWRITEOUTPUTIDSET bit = null output,
  @CREATEEXCEPTIONBATCH bit = null output,
  @EXCEPTIONBATCHNAME nvarchar(60) = null output,
  @CREATECONTROLREPORT bit = null output,
  @TSLONG bigint = 0 output,
  @EXCEPTIONBATCHNUMBERCODE tinyint = null output,
  @EXCEPTIONPREFIX nvarchar(8) = null output,
  @ORIGINALBATCHNUMBER nvarchar(100) = null output,
  @EXCEPTIONLEVEL int = null output,
  @OVERRIDEBATCHNUMBER bit = null output,
  @USENUMBERINGSCHEME bit  = null output,
  @LASTSEPARATOR nvarchar(9) = null output,
  @EXCEPTIONBATCHISCHILD bit = null output,
  @OUTPUTSELECTIONEXISTS bit = null output
)
as
  set nocount on;

  declare @NUMBERINGSCHEMECODE int;
  declare @NUMBERINGSCHEMEID uniqueidentifier;
  declare @BATCHTEMPLATEID uniqueidentifier;

  set @DATALOADED = 0;
  set @TSLONG = 0;
  set @EXCEPTIONBATCHISCHILD = 0;
  set @OUTPUTSELECTIONEXISTS = 0;

  select    
    @DATALOADED = 1,
    @BATCHNUMBER = [BATCH].[BATCHNUMBER],
    @CREATEOUTPUTIDSET = [BATCH].[CREATEOUTPUTIDSET],
    @OUTPUTIDSETNAME = [BATCH].[OUTPUTIDSETNAME],
    @OVERWRITEOUTPUTIDSET = [BATCH].[OVERWRITEOUTPUTIDSET],
    @CREATEEXCEPTIONBATCH = [BATCH].[CREATEEXCEPTIONBATCH],
    @EXCEPTIONBATCHNAME = [BATCH].[EXCEPTIONBATCHNAME],
    @CREATECONTROLREPORT = [BATCH].[CREATECONTROLREPORT],
    @OVERRIDEBATCHNUMBER = [BATCH].[OVERRIDE],
    @TSLONG = [BATCH].[TSLONG],
    @EXCEPTIONPREFIX = BATCHNUMBERINGSCHEME.EXCEPTIONPREFIX,
    @EXCEPTIONBATCHNUMBERCODE = BATCHNUMBERINGSCHEME.EXCEPTIONBATCHNUMBERCODE,
    @USENUMBERINGSCHEME = BATCHNUMBERINGSCHEME.EXCEPTIONBATCHNUMBERCODE,
    @NUMBERINGSCHEMEID = BATCHNUMBERINGSCHEME.ID,
    @NUMBERINGSCHEMECODE = BATCHNUMBERINGSCHEME.NUMBERINGSCHEMECODE,
    @BATCHTEMPLATEID = BATCHTEMPLATEID
  from
    dbo.[BATCH]
  inner join
    dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
  inner join
    dbo.BATCHTYPECATALOG on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
  left join
    dbo.BATCHNUMBERINGSCHEME on BATCHTEMPLATE.BATCHNUMBERINGSCHEMEID = BATCHNUMBERINGSCHEME.ID
  where
    [BATCH].[ID] = @ID;

  exec dbo.USP_BATCH_GETORIGINALNUMBERANDLEVEL @ID, @ORIGINALBATCHNUMBER output, @EXCEPTIONLEVEL output

  if (@CREATEEXCEPTIONBATCH = 1) and (@EXCEPTIONBATCHNAME is not null) and (len(@EXCEPTIONBATCHNAME) > 0)
  begin
    declare @EXCEPTIONBATCHID uniqueidentifier;
    select @EXCEPTIONBATCHID = dbo.UFN_BATCH_GETIDBYTEMPLATE(@EXCEPTIONBATCHNAME, @BATCHTEMPLATEID);

    select
      @EXCEPTIONBATCHISCHILD = 1
    from
      dbo.BATCH
    where
      ID = @EXCEPTIONBATCHID and
      ORIGINATINGBATCHID = @ID;
  end

  if dbo.UFN_BUSINESSPROCESS_IDSETEXISTS(@OUTPUTIDSETNAME) = 1
    set @OUTPUTSELECTIONEXISTS = 1;

  if @NUMBERINGSCHEMECODE = 0
  begin
    select top 1
      @LASTSEPARATOR = SEPARATOR
    from
    (
      select 
        SEQUENCEPLACECODE placecode_1,
        SEQUENCESEPARATOR separator_1,
        CONVERT(bit, 1) include_1,
        LABELPLACECODE placecode_2,
        LABELSEPARATOR separator_2,
        INCLUDELABEL include_2,
        DATEFORMATPLACECODE placecode_3,
        DATESEPARATOR separator_3,
        INCLUDEDATEFORMAT include_3
      from 
        dbo.BATCHNUMBERINGSCHEME
      where
        ID = @NUMBERINGSCHEMEID
    ) p
    UNPIVOT
      (PLACECODE FOR ID1 IN (placecode_1, placecode_2, placecode_3)) AS unpvt
    UNPIVOT
      (SEPARATOR for ID2 in (separator_1, separator_2, separator_3)) as unpvt2
    UNPIVOT
      (INCLUDE for ID3 in (include_1, include_2, include_3)) as unpvt3  
    where
      RIGHT(ID1, 1) = RIGHT(ID2, 1) and
      RIGHT(ID1, 1) = RIGHT(ID3, 1) and
      INCLUDE = 1
    order by
      PLACECODE desc
  end

  if @LASTSEPARATOR = '< None >'
    set @LASTSEPARATOR = ''

  if  @LASTSEPARATOR = '< Space >'
    set @LASTSEPARATOR = ' '

  return 0;