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
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
Keep an eye on our social media for updates on changes coming to TitaniumGP. We hope to see you! Privacy Policy
