USP_DATAFORMTEMPLATE_EDIT_BATCHSTATECHANGE

The save procedure used by the edit dataform template "Batch Status Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@OWNERID	uniqueidentifier	IN	Owner
@NEXTBATCHWORKFLOWTASKID	uniqueidentifier	IN	New status
@COMMENTS	nvarchar(2000)	IN	Comments
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BATCHSTATECHANGE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @OWNERID uniqueidentifier,
    @NEXTBATCHWORKFLOWTASKID uniqueidentifier,
    @COMMENTS nvarchar(2000),
    @CURRENTAPPUSERID uniqueidentifier
)
as
set nocount on;

begin try

    declare @lockName nvarchar(36);
    set @lockName = upper(cast(@ID as nvarchar(36)));

    declare @result int;

    exec @result = sp_getapplock @Resource=@lockName, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=0

    if @result = 0
        begin        
            declare @GRANTED bit
            select @GRANTED=dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER(@CURRENTAPPUSERID, @ID)
            if @GRANTED = 0
                raiserror('You do not have permission to update this batch.', 13, 1);

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

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

            declare @PREVIOUSOWNERID uniqueidentifier;
            select @PREVIOUSOWNERID = BATCH.APPUSERID from dbo.BATCH where BATCH.ID = @ID;

            declare @NEXTBATCHWORKFLOWSTATEID uniqueidentifier;
            select @NEXTBATCHWORKFLOWSTATEID = NEXTBATCHWORKFLOWSTATEID from dbo.BATCHWORKFLOWTASK where ID = @NEXTBATCHWORKFLOWTASKID;

            update dbo.BATCH
                set APPUSERID = @OWNERID,
                    BATCHWORKFLOWSTATEID = @NEXTBATCHWORKFLOWSTATEID,
                    COMMENTS = @COMMENTS,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CHANGEDATE
            where ID = @ID;

            insert into dbo.BATCHWORKFLOWSTATUS (BATCHID, APPUSERID, BATCHWORKFLOWSTATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, APPUSERNAME)
                values (@ID, @OWNERID, @NEXTBATCHWORKFLOWSTATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, COALESCE(dbo.UFN_APPUSER_GETNAME(@OWNERID),''));

            exec dbo.USP_BATCHASSIGNEDALERT_SEND @PREVIOUSOWNERID, @ID;

            exec sp_releaseapplock @Resource=@lockName, @LockOwner='Session';

        end;
    else
        raiserror('This batch is in use and cannot be deleted.', 13, 1);

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

return 0;