Business Rules in BIO Industry

Each company has different and unique set of Business Rules, some might make a lot of sense to one and not much to others, this means, Dynamics GP does not have all the controls that any particular company might need letting the user be in charge of enforcing all Standard Operating procedures.

In a recent discussion with a TitaniumGP customer in the Bio industry, we discussed the ability to prevent Materials Management users from transferring inventory from the Quarantine location to the generally available stock room.

To be allowed to move this inventory, a certain set of rules (not described in this document) must be completed before the material can be made available, this review is performed by personnel from the QA group.

In this company, users from the QA group had a User Class that was prefixed with the letters QA and this was the indicator to allow them to perform inventory transfers from / to these locations.

Using TGP Validate, we added a script that was attached to the Save and Post buttons of the Item Transfer Entry window that will perform the validation of this business rule, if it fails, it will prevent the user from saving or posting this transaction:

While not discussed in this document, certain checks needed to be added to the lot using elastic windows:

If the user is not in any of the QA User Classes. TGP Validate will prevent to move forward with saving or posting this document.

To implement this Business Rule, simply create the SQL Stored Procedure at the bottom of this blog and indicate in the TGP Validate control to execute this SP in the Item Transfer Entry window in both Save and Post buttons:

— ================================================================
— Author:       Joe Smith — Created date: 2/26/2019 — Description:  TitaniumGP Validate for window
— ================================================================
CREATE PROCEDURE [dbo].[TGPVal_IV_Transfer_Entry]
  @vp_IVDocumentType                 INT = NULL,
  @vp_IVDocumentNumber               VARCHAR(100) = NULL,
  @vp_IVTransferScroll_LineSEQNumber NUMERIC (19,5) = NULL,
  @vp_ErrorNum                       INT = 0 output,
  @vp_ErrorMessage                   NVARCHAR(4000) =  output,
  @vp_Message                        NVARCHAR(4000) =  output
  BEGIN try
    SET nocount ON
    DECLARE @IsaQAUser INT = 0 ,
      @RequiresQA      INT = 0
    SELECT @vp_ErrorNum = 0 ,
           @vp_ErrorMessage = ,
           @vp_Message = 
           SELECT 1
           FROM   iv10001 P
           JOIN   iv00101 I
           ON     p.itemnmbr = i.itemnmbr
           WHERE  itmclscd LIKE ‘CK%’ )
             SELECT 1
             FROM   dynamics..sy01400
             WHERE  userid = Rtrim(SYSTEM_USER)
             AND    Upper(usrclass) LIKE ‘QA%’ )
      SELECT @IsaQAUser = 1
             SELECT 1
             FROM   adv_itemmaintenance_control
             WHERE  [QA Release Required] = 1
             AND    itemnmbr IN
                           SELECT itemnmbr
                           FROM   iv10001
                           WHERE  ivdoctyp = @vp_IVDocumentType
                           AND    ivdocnbr = @vp_IVDocumentNumber ) )
      SELECT @RequiresQA = 1
             SELECT 1
             FROM   iv10001
             WHERE  ivdoctyp = @vp_IVDocumentType
             AND    Rtrim(ivdocnbr) = @vp_IVDocumentNumber
             AND    Rtrim(trxloctn) = ‘CKQUARANT’
             AND    Rtrim(trnstloc) = ‘CKSTOCK’
             AND    @IsaQAUser <> 1 )
        SELECT @vp_ErrorNum = 100
        SELECT @vp_ErrorMessage = ‘This item transfer should be handled by the QA Team’ + Char(10) + ‘Your User ID is not allowed to transfer items directly to the Stock Room’ GOTO exitwitherror
    END — CK Items       GOTO NormalExit         END TRY
    BEGIN catch
      DECLARE @vp_ErrorSeverity INT ,
        @vp_ErrorState          INT ,
        @vp_ErrorProcedure      NVARCHAR(128),
        @vp_ErrorLine           INT
      SELECT @vp_ErrorNum = Error_number(),
             @vp_ErrorSeverity = Error_severity(),
             @vp_ErrorState = Error_state(),
             @vp_ErrorProcedure = Error_procedure(),
             @vp_ErrorLine = Error_line(),
             @vp_ErrorMessage = Error_message() GOTO exitwitherror
    END catch
    SELECT @vp_ErrorMessage = @vp_ErrorMessage ENDSP:
    RETURN (@vp_ErrorNum )go

You May Also Like:

How to Prevent Incorrect State or Country Codes in Dynamics GP

How to Prevent Incorrect State or Country Codes in Dynamics GP

Add your Own Business Rules in Dynamics GP Dynamics GP has several business rules, such as entering journal entries with valid accounts or invoices with a vendor that is already in the system.Other rules that users might need are not available. For example, Dynamics...

How to Update the Batch ID of Multiple Orders with TitaniumGP

How to Update the Batch ID of Multiple Orders with TitaniumGP

This document explains how to mass-update the Batch Number of orders in Dynamics GP with TitaniumGP, but it can also be used to mass-update any other field in the header, or in the user define window. One way that companies use the Batch Number in Sales Documents in...

Introducing TGP Resources

Introducing TGP Resources

Getting information from the data dictionaries has never been this easy, TGP Resources allows you to extract information from existing dictionaries for querying or to create SQL views with possible values. Use this information to create reports in SQL Reporting...


Submit a Comment

Your email address will not be published.