Stored Procedure for GST STate validation ON BP Master

[former_member188586]

Hi to all

IF @object_type = '2' and @transaction_type in ('A','U')

BEGIN

if Exists(


SELECT DISTINCT 'TRUE' FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] WHERE T1.[State] ='AP' AND T1.[GSTRegnNo]LIkE '37_%_%_%_%_%_%_%_%_%_%_%_%_%')


BEGIN
Select @error = -1,
@error_message = 'Plase Enter Correct GST No based State'
End
End

----My Requiremnet is

where State: Ap then GSTREGno Start with 37

and

where State: TG then GSTREGno Start with 36

Thanks for Advance help

[former_member390407 · Accepted solution]

Hi there,

Please, try the following

IF @object_type = '2' and @transaction_type in ('A','U')
BEGIN
	SELECT TOP 1 @error = '1', @error_message = 'Plase Enter Correct GST No based State'
	FROM CRD1 
	WHERE CRD1.CardCode = @list_of_cols_val_tab_del -- Or @list_of_key_cols_tab_del, I always mix them up(
		AND (
				(CRD1.[State] ='AP' AND CRD1.[GSTRegnNo] NOT LIKE '37%')
				OR
				(CRD1.[State] ='TG ' AND CRD1.[GSTRegnNo] NOT LIKE '36%')
			)
END

[former_member188586 · Accepted solution]

Hi

Thanks for Quick replay

i want my Requirement like as follow

• 1.Starting 2 Letters represent the State. Hence set the validation to not accept the wrong code. (List of State Codes has been enclosed).

• 2.Length of GSTIN is 15 Digit.

• 3.Format of GSTIN will be 36BBCCK7073N1Z1 36 (2 Single Digit Numbers represents the State Code ) AACCK (Five English Letters 4^th letter contains A (or) C (or) F (or) G (or) H (or) L (or) P (or) T ) 7073 (Four Single Digit Numbers) N1Z1 (4 lettered mix of Alphabets and Single Digit Numbers Contains Alphabet, Number, Alphabet, Number or Alphabet).

based on the above requirement, i want the function

[former_member390407 · Accepted solution]

You can play with this pattern, but the idea is that you can use SQL Server like wildcards. The parttern below shows: 36 + 5 letters in specified range + 4 digits + 4 mixed letters and digits. Adapt it for your needs if I misunderstood.

IF @object_type = '2' and @transaction_type in ('A','U')
BEGIN
	SELECT TOP 1 @error = '1', @error_message = 'Plase Enter Correct GST No based State'
	FROM CRD1 
	WHERE CRD1.CardCode = @list_of_cols_val_tab_del
		AND (
				(CRD1.[State] ='AP' AND CRD1.[GSTRegnNo] NOT LIKE '37[ACHGFLPT][ACHGFLPT][ACHGFLPT][ACHGFLPT][ACHGFLPT][0-9][0-9][0-9][0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]')
				OR
				(CRD1.[State] ='TG ' AND CRD1.[GSTRegnNo] NOT LIKE '36[ACHGFLPT][ACHGFLPT][ACHGFLPT][ACHGFLPT][ACHGFLPT][0-9][0-9][0-9][0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]')
			)
END
<br>

[former_member188586 · Accepted solution]

Sergei Travkin

THE above pattern is not working...

4^th letter contains A (or) C (or) F (or) G (or) H (or) L (or) P (or) T )

could you suggest me ...

i tried like this THEN ALSO NOT WORKING

(CRD1.[State] ='AP' AND CRD1.[GSTRegnNo] NOT LIKE '37[A-z][A-Z][A-Z][ACHGFLPT][A-Z][0-9][0-9][0-9][0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]')

WHATS WRONG THIS CODE

HERE I FOCUSED

BBCCK (HERE  4th letter contains A (or) C (or) F (or) G (or) H (or) L (or) P (or) T )) ONLY 

[former_member188586 · Accepted solution]

Thank you so much .

it's working fine