USP_DATAFORMTEMPLATE_ADD_GENERATECONSTITUENTUPDATEBATCHPROCESS

create procedure dbo.USP_DATAFORMTEMPLATE_ADD_GENERATECONSTITUENTUPDATEBATCHPROCESS
(
        @ID uniqueidentifier = null output,
        @CURRENTAPPUSERID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier = null,
        @NAME nvarchar(100),
        @IDSETREGISTERID uniqueidentifier,
        @BATCHTEMPLATEID uniqueidentifier,
        @BATCHNUMBER nvarchar(100) = '',
        @OVERRIDEBATCHNUMBER bit = 0,
        @DESCRIPTION nvarchar(1000) = '',
        @OWNERID uniqueidentifier = null,
        @AUTOSAVEONROWCHANGE bit = 0,
        @PROJECTEDNUMBEROFRECORDS int = 0,
        @SITEID uniqueidentifier = null
)
as

set nocount on;

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

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

        declare @CURRENTDATE datetime;
        set @CURRENTDATE = getdate();

        declare @BATCHNUMBERINGSCHEMEID uniqueidentifier;
        declare @APPUSERID uniqueidentifier;
        declare @BATCHWORKFLOWSTATEID uniqueidentifier;


        if (@OVERRIDEBATCHNUMBER = 0 or @BATCHNUMBER is null or len(@BATCHNUMBER) = 0)
        begin
            select @BATCHNUMBERINGSCHEMEID = BATCHNUMBERINGSCHEMEID
            from dbo.BATCHTEMPLATE
            where ID = @BATCHTEMPLATEID;

            if @BATCHNUMBERINGSCHEMEID is null
                raiserror('ERR_NUMBERINGSCHEME_NOTDEFINED', 13, 1);
        end
        else
        begin
            if dbo.UFN_SECURITY_APPUSER_GRANTED_CHANGEBATCHNUMBER(@CURRENTAPPUSERID, @BATCHTEMPLATEID) = 0
                raiserror('ERR_CANNOTCUSTOMIZE_NUMBER', 13, 1);

            if (select count(1) from dbo.BATCH where BATCHNUMBER = @BATCHNUMBER and BATCHTEMPLATEID = @BATCHTEMPLATEID) > 0
                raiserror('ERR_DUPLICATE_NUMBER', 13, 1);
        end

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

        select 
                @BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID 
        from 
                dbo.BATCHTEMPLATE 
                inner join dbo.BATCHWORKFLOW on BATCHTEMPLATE.BATCHWORKFLOWID = BATCHWORKFLOW.ID
                inner join dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOW.ID = BATCHWORKFLOWSTATE.BATCHWORKFLOWID
        where 
                BATCHTEMPLATE.ID = @BATCHTEMPLATEID
        and 
                ISINITIALSTATE = 1;

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

        begin try

            if (@SITEID is not null) 
            begin
                if dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, @SITEID) = 0 
                begin
                        raiserror ('ERR_SITE_NOACCESS',13,1);
                        return 1;
                end
            end

            insert into dbo.GENERATEBATCHPROCESSCUB
                (ID, NAME, IDSETREGISTERID, BATCHTEMPLATEID, BATCHNUMBER, OVERRIDEBATCHNUMBER, DESCRIPTION, APPUSERID, AUTOSAVEONROWCHANGE, PROJECTEDNUMBEROFRECORDS, 
                 ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (@ID, @NAME, @IDSETREGISTERID, @BATCHTEMPLATEID, @BATCHNUMBER, @OVERRIDEBATCHNUMBER, @DESCRIPTION, @APPUSERID, @AUTOSAVEONROWCHANGE, @PROJECTEDNUMBEROFRECORDS, 
                 @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);


            exec dbo.USP_BUSINESSPROCESSINSTANCE_ADD @CHANGEAGENTID = @CHANGEAGENTID, 
                    @BUSINESSPROCESSCATALOGID = '44ccccea-9e4b-4935-9ab0-60c9f23a543d', 
                    @BUSINESSPROCESSPARAMETERSETID = @ID, 
                    @OWNERID = @CURRENTAPPUSERID,
                    @SITEID = @SITEID;

        end try

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

        return 0