USP_BATCHTEMPLATE_CREATEORUPDATE

CREATE procedure dbo.USP_BATCHTEMPLATE_CREATEORUPDATE
  @ID uniqueidentifier output,
  @NAME nvarchar(60),
  @DESCRIPTION nvarchar(1000) = '',
  @BATCHTYPECATALOGID uniqueidentifier,
  @ADDROWDATAFORMINSTANCEID uniqueidentifier,
  @EDITROWDATAFORMINSTANCEID uniqueidentifier,
  @COMMITROWADDDATAFORMINSTANCEID uniqueidentifier,
  @COMMITROWEDITDATAFORMINSTANCEID uniqueidentifier,
  @COMMITROWEDITDATAFORMIDFIELD nvarchar(100),
  @ROWRECORDOPERATIONID uniqueidentifier,
  @ROWCONTEXTVIEWDATAFORMINSTANCEID uniqueidentifier,
  @ROWCONTEXTEDITDATAFORMINSTANCEID uniqueidentifier,
  @FORMDEFINITIONXML xml,
  @BATCHWORKFLOWID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @CUSTOM bit = 0,
  @SITEID uniqueidentifier = null

  as
    set nocount on

    declare @ObjID uniqueidentifier
    declare @ExistingID uniqueidentifier

    if @ID is null
    begin
      --bez 2/10/08 CR291543-011608

      --creating 2 batch designs with the same name is overwriting the metadata of the original batch.

      --instead we should raise a unique name violation

      --select @ExistingID = ID from dbo.BATCHTEMPLATE where NAME = @NAME

      --set @ID = @ExistingID

      if exists(select ID from dbo.BATCHTEMPLATE where NAME = @NAME)
      begin
        raiserror (N'ERR_BATCHTEMPLATE_UNIQUENAME', 16, 1)
        return 1;
      end
    end
    else
      select @ExistingID = ID from dbo.BATCHTEMPLATE where ID = @ID

    if @CHANGEAGENTID is null  
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if len(@BATCHTYPECATALOGID) = 0
    begin
      raiserror (N'You must specify a valid batch type id for ''%s''.', 16, 1, N'BATCHTYPECATALOGID')
      return 1;
    end
    else
    begin
      select 
        @ObjID = ID 
      from 
        dbo.BATCHTYPECATALOG 
      where 
        ID = @BATCHTYPECATALOGID

      if @ObjID is null
      begin
        raiserror (N'The ID specified for ''%s'' is not a valid batch type.', 16, 1, N'BATCHTYPECATALOGID')
        return 1;
      end
    end


    if len(@ADDROWDATAFORMINSTANCEID) = 0
    begin
      raiserror (N'You must specify a valid data form instance id for ''%s''.', 16, 1, N'ADDROWDATAFORMINSTANCEID')
      return 1;
    end
    else
    begin
      select 
        @ObjID = DATAFORMINSTANCECATALOG.ID 
      from 
        dbo.DATAFORMINSTANCECATALOG inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
      where 
        DATAFORMINSTANCECATALOG.ID = @ADDROWDATAFORMINSTANCEID and DATAFORMTEMPLATECATALOG.[MODE] = 2

      if @ObjID is null
      begin
        raiserror (N'Data form Id specified for ''%s'' is not a valid add data form.', 16, 1, N'ADDROWDATAFORMINSTANCEID')
        return 1;
      end
    end

    if len(@EDITROWDATAFORMINSTANCEID) = 0
    begin
      raiserror (N'You must specify a valid data form instance id for ''%s''.', 16, 1, N'EDITROWDATAFORMINSTANCEID')
      return 1;
    end
    else
    begin
      select 
        @ObjID = DATAFORMINSTANCECATALOG.ID 
      from 
        dbo.DATAFORMINSTANCECATALOG inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
      where 
        DATAFORMINSTANCECATALOG.ID = @EDITROWDATAFORMINSTANCEID and DATAFORMTEMPLATECATALOG.[MODE] = 1

      if @ObjID is null
      begin
        raiserror (N'Data form instance specified by ''%s'' is not a valid add data form.', 16, 1, N'EDITROWDATAFORMINSTANCEID')
        return 1;
      end
    end

    if len(@COMMITROWADDDATAFORMINSTANCEID) = 0 and len(@COMMITROWEDITDATAFORMINSTANCEID) = 0
    begin
      raiserror (N'You must specify at least one commit data form instance id.', 16, 1);
      return 1;
    end

    if len(@COMMITROWADDDATAFORMINSTANCEID) > 0
    begin
      select 
        @ObjID = DATAFORMINSTANCECATALOG.ID 
      from 
        dbo.DATAFORMINSTANCECATALOG inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
      where 
        DATAFORMINSTANCECATALOG.ID = @COMMITROWADDDATAFORMINSTANCEID and DATAFORMTEMPLATECATALOG.[MODE] = 2

      if @ObjID is null
      begin
        raiserror (N'Data form instance specified by ''%s'' is not a valid add data form.', 16, 1, N'COMMITROWADDDATAFORMINSTANCEID')
        return 1;
      end
    end

    if len(@COMMITROWEDITDATAFORMINSTANCEID) > 0
    begin
      if len(@COMMITROWEDITDATAFORMIDFIELD) = 0
      begin
        raiserror (N'You must specify a %s for a %s.', 16, 1, N'COMMITROWEDITDATAFORMIDFIELD', N'COMMITROWEDITDATAFORMINSTANCEID');
        return 1;
      end

      select 
        @ObjID = DATAFORMINSTANCECATALOG.ID 
      from 
        dbo.DATAFORMINSTANCECATALOG inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
      where 
        DATAFORMINSTANCECATALOG.ID = @COMMITROWEDITDATAFORMINSTANCEID and DATAFORMTEMPLATECATALOG.[MODE] = 1

      if @ObjID is null
      begin
        raiserror (N'Data form instance specified by ''%s'' is not a valid edit data form.', 16, 1, N'COMMITROWEDITDATAFORMINSTANCEID')
        return 1;
      end
    end


    if len(@ROWRECORDOPERATIONID) = 0
    begin
      raiserror (N'You must specify a valid record operation id for ''%s''.', 16, 1, N'ROWRECORDOPERATIONID')
      return 1;
    end
    else
    begin
      select 
        @ObjID = ID 
      from 
        dbo.RECORDOPERATIONCATALOG 
      where 
        ID = @ROWRECORDOPERATIONID

      if @ObjID is null
      begin
        raiserror (N'Record operation instance specified for ''%s'' is not a valid record operation.', 16, 1, N'ROWOPERATIONID')
        return 1;
      end
    end

    if len(@ROWCONTEXTVIEWDATAFORMINSTANCEID) > 0
    begin
      select 
        @ObjID = DATAFORMINSTANCECATALOG.ID 
      from 
        dbo.DATAFORMINSTANCECATALOG inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
      where 
        DATAFORMINSTANCECATALOG.ID = @ROWCONTEXTVIEWDATAFORMINSTANCEID and DATAFORMTEMPLATECATALOG.[MODE] = 0

      if @ObjID is null
      begin
        raiserror (N'Data form instance specified by ''%s'' is not a valid view data form.', 16, 1, N'ROWCONTEXTVIEWDATAFORMINSTANCEID')
        return 1;
      end
    end

    if len(@ROWCONTEXTEDITDATAFORMINSTANCEID) > 0
    begin
      select 
        @ObjID = DATAFORMINSTANCECATALOG.ID 
      from 
        dbo.DATAFORMINSTANCECATALOG inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
      where 
        DATAFORMINSTANCECATALOG.ID = @ROWCONTEXTEDITDATAFORMINSTANCEID and DATAFORMTEMPLATECATALOG.[MODE] = 0

      if @ObjID is null
      begin
        raiserror (N'Data form instance specified by ''%s'' is not a valid edit data form.', 16, 1, N'ROWCONTEXTEDITDATAFORMINSTANCEID')
        return 1;
      end
    end

    --find if template name already exists with a different ID, and if so, find a new name

    if 
    (
      exists
      (
        select 
          ID 
        from 
          dbo.BATCHTEMPLATE 
        where 
          NAME = @NAME and ID <> @ID
      )
      and
        @ID = (
          select 
            BATCHTYPECATALOG.SPECXML.value
            (
              'declare namespace ns="bb_appfx_batchtype"; 
              (/ns:BatchTypeSpec/@BatchTemplateID)[1]','uniqueidentifier'
            )
          from 
            dbo.BATCHTYPECATALOG
          where 
            ID = @BATCHTYPECATALOGID)
    )
    begin

      declare @NUMBER int;
      set @NUMBER = 1;

      declare @CORRECTNUMBERFOUND bit;
      set @CORRECTNUMBERFOUND = 0;

      declare @UPDATEDNAME nvarchar(150);

      while @CORRECTNUMBERFOUND = 0 begin
        set @UPDATEDNAME = @NAME + ' ' + cast(@NUMBER as nvarchar(10));

        if (select count(ID) from dbo.BATCHTEMPLATE where NAME = @UPDATEDNAME) = 0 or (select count(ID) from dbo.BATCHTEMPLATE where NAME = @UPDATEDNAME and ID = @ID) = 1
          set @CORRECTNUMBERFOUND = 1;
        else
          set @NUMBER = @NUMBER + 1;
      end

      set @NAME = @UPDATEDNAME;

    end

    declare @HASCUSTOMCOLUMN bit;
    set @HASCUSTOMCOLUMN = 0;
    select 
      @HASCUSTOMCOLUMN = 1 
    from 
      INFORMATION_SCHEMA.COLUMNS 
    where 
      (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'BATCHTEMPLATE') AND (COLUMN_NAME = 'CUSTOM')

    if @SITEID = '00000000-0000-0000-0000-000000000000' 
      set @SITEID = null;

    declare @BATCHNUMBERINGSCHEMEID uniqueidentifier;
    if @ExistingID is null
      select
        @BATCHNUMBERINGSCHEMEID = BATCHNUMBERINGSCHEMEID
      from dbo.BATCHWORKFLOW
      where BATCHWORKFLOW.ID = @BATCHWORKFLOWID

    if @ExistingID is null
    begin
      if @ID is null 
        set @ID = NewID()

      if @HASCUSTOMCOLUMN = 1
        insert into dbo.BATCHTEMPLATE 
        (
          ID, 
          [NAME], 
          DESCRIPTION, 
          BATCHTYPECATALOGID, 
          ADDROWDATAFORMINSTANCEID,
          EDITROWDATAFORMINSTANCEID, 
          COMMITROWADDDATAFORMINSTANCEID, 
          COMMITROWEDITDATAFORMINSTANCEID, 
          COMMITROWEDITDATAFORMIDFIELD, 
          ROWRECORDOPERATIONID, 
          ROWCONTEXTVIEWDATAFORMINSTANCEID,
          ROWCONTEXTEDITDATAFORMINSTANCEID, 
          FORMDEFINITIONXML,
          BATCHWORKFLOWID, 
          CUSTOM, 
          SITEID,
          BATCHNUMBERINGSCHEMEID,
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
        values 
        (
          @ID, 
          @NAME, 
          @DESCRIPTION, 
          @BATCHTYPECATALOGID, 
          @ADDROWDATAFORMINSTANCEID,
          @EDITROWDATAFORMINSTANCEID, 
          @COMMITROWADDDATAFORMINSTANCEID, 
          @COMMITROWEDITDATAFORMINSTANCEID, 
          @COMMITROWEDITDATAFORMIDFIELD, 
          @ROWRECORDOPERATIONID, 
          @ROWCONTEXTVIEWDATAFORMINSTANCEID,
          @ROWCONTEXTEDITDATAFORMINSTANCEID, 
          @FORMDEFINITIONXML, 
          @BATCHWORKFLOWID, 
          @CUSTOM, 
          @SITEID,
          @BATCHNUMBERINGSCHEMEID,
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          GETDATE(), 
          GETDATE()
        )
      else
        insert into dbo.BATCHTEMPLATE 
        (
          ID, 
          [NAME], 
          DESCRIPTION, 
          BATCHTYPECATALOGID, 
          ADDROWDATAFORMINSTANCEID,
          EDITROWDATAFORMINSTANCEID, 
          COMMITROWADDDATAFORMINSTANCEID, 
          COMMITROWEDITDATAFORMINSTANCEID, 
          COMMITROWEDITDATAFORMIDFIELD, 
          ROWRECORDOPERATIONID, 
          ROWCONTEXTVIEWDATAFORMINSTANCEID,
          ROWCONTEXTEDITDATAFORMINSTANCEID, 
          FORMDEFINITIONXML,
          BATCHWORKFLOWID, 
          SITEID,
          BATCHNUMBERINGSCHEMEID,
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
        values 
        (
          @ID, 
          @NAME, 
          @DESCRIPTION, 
          @BATCHTYPECATALOGID, 
          @ADDROWDATAFORMINSTANCEID,
          @EDITROWDATAFORMINSTANCEID, 
          @COMMITROWADDDATAFORMINSTANCEID, 
          @COMMITROWEDITDATAFORMINSTANCEID, 
          @COMMITROWEDITDATAFORMIDFIELD, 
          @ROWRECORDOPERATIONID, 
          @ROWCONTEXTVIEWDATAFORMINSTANCEID,
          @ROWCONTEXTEDITDATAFORMINSTANCEID, 
          @FORMDEFINITIONXML, 
          @BATCHWORKFLOWID, 
          @SITEID,
          @BATCHNUMBERINGSCHEMEID,
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          GETDATE(), 
          GETDATE()
        )
    end
    else if @HASCUSTOMCOLUMN = 1
      update 
        dbo.BATCHTEMPLATE
      set 
        [NAME] = @NAME,
        DESCRIPTION = @DESCRIPTION,
        ADDROWDATAFORMINSTANCEID = @ADDROWDATAFORMINSTANCEID,
        EDITROWDATAFORMINSTANCEID = @EDITROWDATAFORMINSTANCEID,
        COMMITROWADDDATAFORMINSTANCEID = @COMMITROWADDDATAFORMINSTANCEID,
        COMMITROWEDITDATAFORMINSTANCEID = @COMMITROWEDITDATAFORMINSTANCEID,
        COMMITROWEDITDATAFORMIDFIELD = @COMMITROWEDITDATAFORMIDFIELD,
        ROWRECORDOPERATIONID = @ROWRECORDOPERATIONID,
        ROWCONTEXTVIEWDATAFORMINSTANCEID = @ROWCONTEXTVIEWDATAFORMINSTANCEID,
        ROWCONTEXTEDITDATAFORMINSTANCEID = @ROWCONTEXTEDITDATAFORMINSTANCEID,
        FORMDEFINITIONXML = @FORMDEFINITIONXML,
        BATCHWORKFLOWID = @BATCHWORKFLOWID,
        CUSTOM = @CUSTOM,
        SITEID = @SITEID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = GETDATE()
      where 
        ID = @ID
    else
      update 
        dbo.BATCHTEMPLATE
      set 
        [NAME] = @NAME,
        DESCRIPTION = @DESCRIPTION,
        ADDROWDATAFORMINSTANCEID = @ADDROWDATAFORMINSTANCEID,
        EDITROWDATAFORMINSTANCEID = @EDITROWDATAFORMINSTANCEID,
        COMMITROWADDDATAFORMINSTANCEID = @COMMITROWADDDATAFORMINSTANCEID,
        COMMITROWEDITDATAFORMINSTANCEID = @COMMITROWEDITDATAFORMINSTANCEID,
        COMMITROWEDITDATAFORMIDFIELD = @COMMITROWEDITDATAFORMIDFIELD,
        ROWRECORDOPERATIONID = @ROWRECORDOPERATIONID,
        ROWCONTEXTVIEWDATAFORMINSTANCEID = @ROWCONTEXTVIEWDATAFORMINSTANCEID,
        ROWCONTEXTEDITDATAFORMINSTANCEID = @ROWCONTEXTEDITDATAFORMINSTANCEID,
        FORMDEFINITIONXML = @FORMDEFINITIONXML,
        BATCHWORKFLOWID = @BATCHWORKFLOWID,
        SITEID = @SITEID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = GETDATE()
      where 
        ID = @ID;