Scenario: Looking for a way to easily update insurance tax state for a specific phase code across all jobs.

Example Solution:

It is a good idea to test this in a test database before impacting your live database.

  1. Create a backup of your database.
  2. Create Stored Procedure, see SQL Query listed below.
  3. In Vista UD Module, add a Custom Button to the PRBTCHUP form with the following parameters settings, changing the @Phase, @state & @inscode default values to your desired settings.
  4. Move the Custom Button on PR Batch Update form
  5. Test the stored procedure in your test company/database before using it live.

SQL Query

/** User Defined Stored Procedure **/ 

/** Purpose: To update Timecard Entry lines to set Insurance State for a Specific Phase Code **/

/** Created by Constructive Tech Solutions 11/13/2018 **/

USE [Viewpoint]
GO

CREATE PROCEDURE udPRPhaseTaxUpdate (@PRCo bCompany = null, @Phase bPhase = null, @mth bMonth = null, @batchid bBatchID = null, @state varchar(4) = null, @inscode bInsCode = null, @ReturnMessage varchar(30) OUTPUT)

AS
BEGIN
	declare @rcode int = 0

	UPDATE PRTB
	SET InsState = @state, InsCode = @inscode
	WHERE Co = @PRCo AND Phase = @Phase AND Mth = @mth AND BatchId = @batchid

	select @ReturnMessage = 'Updated ' + cast(@@ROWCOUNT as
        varchar(16)) + ' Rows', @rcode = 0

END

RETURN @rcode