USP_DATAFORMTEMPLATE_ADD_BATCH2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCH2  
(  
    @ID uniqueidentifier = null output,  
    @CURRENTAPPUSERID uniqueidentifier,  
    @BATCHNUMBER nvarchar(100) = '',  
    @DESCRIPTION nvarchar(1000) = '',  
    @OWNERID uniqueidentifier = null,             
    @PROJECTEDNUMBEROFRECORDS int = 0,  
    @PROJECTEDTOTALAMOUNT money = 0,  
    @BATCHTEMPLATEID uniqueidentifier,  
    @ORIGINATINGBATCHID uniqueidentifier = null,  
    @CHANGEAGENTID uniqueidentifier = null,  
    @CREATECUSTOMBATCH bit = null,  
    @FORMDEFINITIONXML xml = null,  
    @AUTOSAVEONROWCHANGE bit = 0,  
    @OVERRIDEBATCHNUMBER bit = 0,  
    @AUTOMATCHTHRESHOLD decimal(20, 4) = 0,  
    @OVERALLMATCHTHRESHOLD decimal(20, 4) = 0,  
    @PARAMETERSXML xml = null,  
    @CHECKFORDUPLICATES bit = null,  
    @DISABLEADDNEWROW bit = 0,   
    @SKIPBATCHASSIGNMENTALERT bit = 0  
)  
as  
    set nocount on;  

    declare @TEMPLATEID uniqueidentifier;  
    declare @ORIGINATINGTEMPLATEID uniqueidentifier;  
    declare @NAME nvarchar(100);  
    declare @BATCHWORKFLOWSTATEID uniqueidentifier;  
    declare @BATCHWORKFLOWID uniqueidentifier;    
    declare @BATCHTYPECATALOGID uniqueidentifier;  
    declare @BATCHNUMBERINGSCHEMEID uniqueidentifier;   
    declare @USENUMBERINGSCHEME bit;  
    declare @ISMANUALSCHEME bit;  
    declare @SITEID uniqueidentifier;      
    set @USENUMBERINGSCHEME = 0;  
    select @SITEID = SITEID from dbo.BATCHTEMPLATE where ID = @BATCHTEMPLATEID;  
    if @CHANGEAGENTID is null    
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;  

    if @CREATECUSTOMBATCH = null  
      set @CREATECUSTOMBATCH = 0;  

    if (@OVERRIDEBATCHNUMBER = 0 and (@BATCHNUMBER is null or len(@BATCHNUMBER) = 0))  
      set @USENUMBERINGSCHEME = 1;  

    if (@USENUMBERINGSCHEME = 1)  
    begin  
        -- find the batch number from the workflow  

        select  
            @BATCHTYPECATALOGID = BATCHTEMPLATE.BATCHTYPECATALOGID,  
            @BATCHWORKFLOWID = BATCHTEMPLATE.BATCHWORKFLOWID,  
            @BATCHNUMBERINGSCHEMEID = BATCHTEMPLATE.BATCHNUMBERINGSCHEMEID  
        from   
            dbo.BATCHTEMPLATE  
        where  
            BATCHTEMPLATE.ID = @BATCHTEMPLATEID;  

        if @BATCHNUMBERINGSCHEMEID is null  
          raiserror('ERR_NUMBERINGSCHEME_NOTDEFINED', 13, 1);  

        exec dbo.USP_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER_ATOMIC @ID = @BATCHNUMBERINGSCHEMEID  
          , @BATCHTEMPLATEID = @BATCHTEMPLATEID  
          , @BATCHNUMBER = @BATCHNUMBER output  
          , @CHANGEAGENTID = @CHANGEAGENTID;  
    end  
    else  
    begin  
        select  
            @BATCHNUMBERINGSCHEMEID = BATCHTEMPLATE.BATCHNUMBERINGSCHEMEID,  
            @ISMANUALSCHEME = case BATCHNUMBERINGSCHEME.NUMBERINGSCHEMECODE when 0 then 0 else 1 end  
        from dbo.BATCHTEMPLATE  
          left join dbo.BATCHNUMBERINGSCHEME on BATCHTEMPLATE.BATCHNUMBERINGSCHEMEID = BATCHNUMBERINGSCHEME.ID  
        where  
            BATCHTEMPLATE.ID = @BATCHTEMPLATEID;  

        if @BATCHNUMBERINGSCHEMEID is null  
          raiserror('ERR_NUMBERINGSCHEME_NOTDEFINED', 13, 1);  

        -- Only check for rights when the originating ID is not provided. If it is, then we are coming  

        -- from the commit process for creating exception batches.  

        if @ORIGINATINGBATCHID is null  
        begin  
          if @ISMANUALSCHEME = 0 and dbo.UFN_SECURITY_APPUSER_GRANTED_CHANGEBATCHNUMBER(@CURRENTAPPUSERID, @BATCHTEMPLATEID) = 0  
          raiserror('ERR_CANNOTCUSTOMIZE_NUMBER', 13, 1);  
        end  
        if exists (select 
                    top 1 BATCH.ID 
                  from dbo.BATCH 
                  inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
                  where BATCHNUMBER = @BATCHNUMBER and BATCHNUMBERINGSCHEMEID = @BATCHNUMBERINGSCHEMEID)
              raiserror('ERR_BATCHNUMBER_ISUNIQUE', 13, 1);    
    end  

    if @CREATECUSTOMBATCH = 1  
    begin  
      declare @HASSECURITY bit;  

      select @HASSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHTEMPLATECUSTOMIZE(@CURRENTAPPUSERID, @BATCHTEMPLATEID);  
      if @HASSECURITY = 0  
      begin  
        raiserror('ERR_CANNOTCUSTOMIZE_BATCH', 13, 1);  
      end;  
      set @ORIGINATINGTEMPLATEID = @BATCHTEMPLATEID;  
      set @TEMPLATEID = newID();  

      -- This might have been set up above when getting the next batch number.  

      if (@BATCHTYPECATALOGID is null)  
          select @BATCHTYPECATALOGID = BATCHTEMPLATE.BATCHTYPECATALOGID, @BATCHWORKFLOWID = BATCHTEMPLATE.BATCHWORKFLOWID from dbo.BATCHTEMPLATE where BATCHTEMPLATE.ID = @BATCHTEMPLATEID;  

      set @NAME = @BATCHNUMBER;  
      if exists(select top 1 NAME from dbo.BATCHTEMPLATE where NAME = @NAME)  
      begin  
        --make name unique?  

        set @NAME = @NAME + ' ' + convert(nvarchar(30), GETDATE(), 109);  
      end;  
      exec dbo.USP_BATCHTEMPLATEPROCESSOR_CREATEORUPDATE @TEMPLATEID output, @BATCHTYPECATALOGID, @NAME, '', @FORMDEFINITIONXML, @BATCHWORKFLOWID, @CHANGEAGENTID, @CREATECUSTOMBATCH, @SITEID;  

      -- copy new features from template to custom  

      exec dbo.USP_BATCHTEMPLATE_SETDEFAULTSFORCUSTOM @ID = @TEMPLATEID, @ORIGINALID = @BATCHTEMPLATEID;  

      -- copy security from originating template  

      declare @TEMPLATEPERMISSIONS xml;  
      declare @CUSTOMIZEPERMISSIONS xml;  

      set @TEMPLATEPERMISSIONS = (select [GRANTORDENY], NEWID() as [ID], [NAME], [SYSTEMROLEID]  
                                  from dbo.[UFN_BATCHTEMPLATE_GETSYSTEMROLEPERMISSIONS](@BATCHTEMPLATEID)  
                                  where GRANTORDENY <> 2  
                                  order by NAME  
                                  for xml raw('ITEM'),type,elements,root('TEMPLATEPERMISSIONS'),BINARY BASE64)  

      set @CUSTOMIZEPERMISSIONS = (select [GRANTORDENY], NEWID() as [ID], [NAME], [SYSTEMROLEID]  
                       from dbo.[UFN_BATCHTEMPLATE_GETSYSTEMROLECUSTOMIZEPERMISSIONS](@BATCHTEMPLATEID)  
                       where GRANTORDENY <> 2  
                       order by NAME  
                       for xml raw('ITEM'),type,elements,root('CUSTOMIZEPERMISSIONS'),BINARY BASE64)   

      exec dbo.USP_BATCHTEMPLATE_GETSYSTEMROLEPERMISSIONS_ADDFROMXML @TEMPLATEID, @TEMPLATEPERMISSIONS, @CHANGEAGENTID;                                          
      exec dbo.USP_BATCHTEMPLATE_GETSYSTEMROLECUSTOMIZEPERMISSIONS_ADDFROMXML @TEMPLATEID, @CUSTOMIZEPERMISSIONS, @CHANGEAGENTID;  

      update dbo.BATCHTEMPLATE  
      set ORIGINATINGTEMPLATEID = @ORIGINATINGTEMPLATEID  
      where BATCHTEMPLATE.ID = @TEMPLATEID  

      set @BATCHTEMPLATEID = @TEMPLATEID;  
    end;  
    else    
    begin  
      declare @TEMPLATE_FORMDEFINITIONXML xml;  
      select @TEMPLATE_FORMDEFINITIONXML = BATCHTEMPLATE.FORMDEFINITIONXML, @BATCHWORKFLOWID = BATCHTEMPLATE.BATCHWORKFLOWID from dbo.BATCHTEMPLATE where BATCHTEMPLATE.ID = @BATCHTEMPLATEID;  
      if @FORMDEFINITIONXML is null  
        --WI 182042 if creating an exception batch use the same formmetadata (with defaults, hidden and required fields) that the originating batch has  

        if @ORIGINATINGBATCHID is null  
          set @FORMDEFINITIONXML = @TEMPLATE_FORMDEFINITIONXML;  
        else  
          select @FORMDEFINITIONXML = BATCH.FORMDEFINITIONXML, @PARAMETERSXML = PARAMETERSXML from dbo.BATCH where BATCH.ID = @ORIGINATINGBATCHID  
    end;  

    if @ID is null  
      set @ID = newid();  

    begin try  

      declare @APPUSERID uniqueidentifier;  

      if @OWNERID is null  
        set @APPUSERID = @CURRENTAPPUSERID;  
      else  
        set @APPUSERID = @OWNERID;    

      if @BATCHWORKFLOWID is not null   
        begin  
          select   
            @BATCHWORKFLOWSTATEID = ID   
          from dbo.BATCHWORKFLOWSTATE   
          where   
            BATCHWORKFLOWID = @BATCHWORKFLOWID   
            and ISINITIALSTATE = 1;  
        end;  
      else  
        set @BATCHWORKFLOWSTATEID = null;  

      if @BATCHWORKFLOWSTATEID is null  
      begin  
        raiserror('ERR_INVALIDWORKFLOW_DEFINED', 13, 1);  
      end;  

      --need to create new dataforms if necessary  


    exec USP_BATCH2_ADD @ID = @ID,
           @BATCHNUMBER = @BATCHNUMBER,
           @DESCRIPTION = @DESCRIPTION,
           @OWNERID = @APPUSERID,
           @BATCHTEMPLATEID = @BATCHTEMPLATEID,
           @BATCHWORKFLOWSTATEID = @BATCHWORKFLOWSTATEID,
           @PROJECTEDNUMBEROFRECORDS = @PROJECTEDNUMBEROFRECORDS,
           @PROJECTEDTOTALAMOUNT = @PROJECTEDTOTALAMOUNT,
           @FORMDEFINITIONXML = @FORMDEFINITIONXML,
           @ORIGINATINGBATCHID = @ORIGINATINGBATCHID,
           @CHANGEAGENTID = @CHANGEAGENTID,
           @AUTOSAVEONROWCHANGE = @AUTOSAVEONROWCHANGE,
           @AUTOMATCHTHRESHOLD = @AUTOMATCHTHRESHOLD,
           @OVERALLMATCHTHRESHOLD = @OVERALLMATCHTHRESHOLD,
           @PARAMETERSXML = @PARAMETERSXML,
           @DISABLEADDNEWROW = @DISABLEADDNEWROW,
           @CHECKFORDUPLICATES =  @CHECKFORDUPLICATES

    if @SKIPBATCHASSIGNMENTALERT = 0 begin  
        exec dbo.USP_BATCHASSIGNEDALERT_SEND null, @ID;  
    end;  

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

    return 0;