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, simple 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
AS
  BEGIN try
    SET nocount ON
    DECLARE @IsaQAUser INT = 0 ,
      @RequiresQA      INT = 0
    SELECT @vp_ErrorNum = 0 ,
           @vp_ErrorMessage = ,
           @vp_Message = 
    IF EXISTS
    (
           SELECT 1
           FROM   iv10001 P
           JOIN   iv00101 I
           ON     p.itemnmbr = i.itemnmbr
           WHERE  itmclscd LIKE ‘CK%’ )
    BEGIN
      IF EXISTS
      (
             SELECT 1
             FROM   dynamics..sy01400
             WHERE  userid = Rtrim(SYSTEM_USER)
             AND    Upper(usrclass) LIKE ‘QA%’ )
      SELECT @IsaQAUser = 1
      IF EXISTS
      (
             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
      IF EXISTS
      (
             SELECT 1
             FROM   iv10001
             WHERE  ivdoctyp = @vp_IVDocumentType
             AND    Rtrim(ivdocnbr) = @vp_IVDocumentNumber
             AND    Rtrim(trxloctn) = ‘CKQUARANT’
             AND    Rtrim(trnstloc) = ‘CKSTOCK’
             AND    @IsaQAUser <> 1 )
      BEGIN
        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
    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
    NORMALEXIT: GOTO endsp EXITWITHERROR:
    SELECT @vp_ErrorMessage = @vp_ErrorMessage ENDSP:
    RETURN (@vp_ErrorNum )go