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.

  1. Run the script listed below on your SQL Server Viewpoint database to install the Stored Procedure.
  2. Create a Custom Button in VA Custom Form Buttons.
    1. Form: HQBC
    2. Button ID: Next ID (+ for New)
    3. Text: Unlock Batch
    4. Action Type: Stored Procedure
    5. Action: uspUnlockBatch
    6. Button Refresh: Refresh Data Set
    7. Set Parameters on Parameters Tab (this passes the batch info from the HQ Batch Control form to the Stored Procedure)
      1. ID: 1, Name: @Co, DefaultType: 3, Default Value: 0
      2. ID: 2, Name: @mth, DefaultType: 3, Default Value: 10
      3. ID: 3, Name: @batchID, DefaultType: 3, Default Value: 20
      4. ID: 4, Name: @msg, DefaultType: 0, Default Value: No Error
  3. Open the HQ Batch Control form and move the Custom Button to desired location.
  4. Test to ensure it works as desired.

SQL Query

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