When it is time to create reports based on your data, the consistency of entity data becomes crucial. It is easy to miss or report incorrectly sales if you strictly base your aggregates on the data coming from a system that has free form data entry on these key fields.

New Jersey, N.J, NJ, NJ., N J

All these entries will make it for shipping but not for analytical reporting.

By using TGPValidate to add a business rule to the Customer Maintenance window (or anywhere in the system that accepts the State field) it will assist us in controlling what goes into the system to facilitate reporting and other activities.

The included script, will prevent users from entering values to the state field not included in the standard abbreviation: 

— ================================================================
— Author:       TitaniumGP
— Created date: 2/17/2020
— Description:  TitaniumGP Validate for Customer Maintenance window 
— ================================================================
ALTER PROCEDURE [dbo].[Tgpval_rm_customer_maintenance] @vp_CustomerNumber
VARCHAR(100) = NULL,
@vp_City
VARCHAR(100) = NULL,
@vp_State
VARCHAR(100) = NULL,
@vp_Zip
VARCHAR(100) = NULL,
@vp_CountryCode
VARCHAR(100) = NULL,
@vp_Country
VARCHAR(100) = NULL,
@vp_ErrorNum       INT =
0 output,
@vp_ErrorMessage
NVARCHAR(4000) =  output,
@vp_Message
NVARCHAR(4000) =  output
AS
BEGIN try
SET nocount ON

SELECT @vp_ErrorNum = 0,
@vp_ErrorMessage = ,
@vp_Message = 

IF @vp_CountryCode = ‘US’
AND Rtrim(@vp_State) <> 
AND @vp_State NOT IN ( ‘AK’, ‘AL’, ‘AR’, ‘AZ’,
‘CA’, ‘CO’, ‘CT’, ‘DE’,
‘FL’, ‘GA’, ‘HI’, ‘IA’,
‘ID’, ‘IL’, ‘IN’, ‘KS’,
‘KY’, ‘LA’, ‘MA’, ‘MD’,
‘ME’, ‘MI’, ‘MN’, ‘MO’,
‘MS’, ‘MT’, ‘NC’, ‘ND’,
‘NE’, ‘NH’, ‘NJ’, ‘NM’,
‘NV’, ‘NY’, ‘OH’, ‘OK’,
‘OR’, ‘PA’, ‘RI’, ‘SC’,
‘SD’, ‘TN’, ‘TX’, ‘UT’,
‘VA’, ‘VT’, ‘WA’, ‘WI’,
‘WV’, ‘WY’ )
BEGIN
SELECT @vp_ErrorNum = 250

SELECT @vp_ErrorMessage = ‘Invalid State Abreviation, please review’

GOTO exitwitherror
END

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 )

Keep an eye on our social media for updates on changes coming to TitaniumGP. We hope to see you!

link to LinkedIn
link to Facebook
link to Twitter
link to Instagram