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! Privacy Policy
