Below is an example of a stored procedure that allows a stuck batch to be reopened. This can allow you to reopen the batch without access to the SQL Server or having to call Viewpoint Support. SQL Server access and appropriate permissions are required to install this stored procedure. It is always a good idea to test customizations in a test environment before installing on your live environment.
- Run the script listed below on your SQL Server Viewpoint database to install the Stored Procedure.
- Create a Custom Button in VA Custom Form Buttons.
- Form: HQBC
- Button ID: Next ID (+ for New)
- Text: Unlock Batch
- Action Type: Stored Procedure
- Action: uspUnlockBatch
- Button Refresh: Refresh Data Set
- Set Parameters on Parameters Tab (this passes the batch info from the HQ Batch Control form to the Stored Procedure)
- ID: 1, Name: @Co, DefaultType: 3, Default Value: 0
- ID: 2, Name: @mth, DefaultType: 3, Default Value: 10
- ID: 3, Name: @batchID, DefaultType: 3, Default Value: 20
- ID: 4, Name: @msg, DefaultType: 0, Default Value: No Error
- Open the HQ Batch Control form and move the Custom Button to desired location.
- Test to ensure it works as desired.
USE [Viewpoint] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[uspUnlockBatch] /** User Defined Validation Procedure **/ /** Purpose: Used to Reopen a Stuck Batch **/ /** Created by Constructive Tech Solutions 12/31/2019 **/ ( @co bCompany ,@mth bMonth ,@batchid bBatchID ,@ReturnMessage varchar(255) OUTPUT ) AS DECLARE @rcode INT SELECT @rcode = 0 IF (SELECT Status from HQBC WHERE Co = @co and Mth = @mth and BatchId = @batchid) <> 4 SELECT @rcode=1,@ReturnMessage='Batch is Not in Status 4' ELSE BEGIN UPDATE HQBC SET Status = 0 WHERE Co = @co and Mth = @mth and BatchId = @batchid SELECT @rcode=1,@ReturnMessage='Batch Status Updated to 0-Open' END spexit: RETURN @rcode GO GRANT EXECUTE ON [dbo].[uspUnlockBatch] TO VCSUsers GO