SBO_TN

[former_member209725]

Hi Experts...

Plz check this Sbo_TN.

*IF (@transaction_type = 'A'OR @transaction_type ='U') AND @object_type = '2'

BEGIN

If exists (SELECT T0.CardCode FROM OCRD AS T0 INNER JOIN

OCPR T1 ON T0.CardCode = T1.CardCode

WHERE (T0.CntctPrsn IS NULL OR T1.Cellolar is NULL ) AND (T0.CardCode = @list_of_cols_val_tab_del))

begin

SET @error = 111

SET @error_message = N'MUST SPECIFY CONTACT PERSON MOBILE NO.'

end

END*

This query running successfully. But I need that user can't put characters, symbols, or space in Cellolar field. It should just accept 10 numeric digits.

Plz help for same.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Try:

IF @transaction_type IN ('A','U') AND @object_type = '2'

BEGIN

If exists (SELECT T0.CardCode FROM OCRD AS T0 INNER JOIN

OCPR T1 ON T0.CardCode = T1.CardCode

WHERE (T0.CntctPrsn IS NULL OR ISNUMERIC(T1.Cellolar) = 0 )

AND (T0.CardCode = @list_of_cols_val_tab_del))

Begin

SET @error = 111

SET @error_message = N'MUST SPECIFY CONTACT PERSON MOBILE NO.'

End

END

Thanks,

Gordon

[former_member209725 · Accepted solution]

Hi Gordon...

Thanks for such prompt reply.

One more thing I want to add in this TN that value should not bigger than 10 digits.

Regards,

Ravi

[Former Member · Accepted solution]

Try:

WHERE (T0.CntctPrsn IS NULL OR ISNUMERIC(T1.Cellolar) = 0 OR LEN(T1.Cellolar)>10)

AND (T0.CardCode = @list_of_cols_val_tab_del))

[former_member206488 · Accepted solution]

Hi Ravi,

Try this:

IF  @object_type = '2'AND@transaction_type IN ('A','U')
BEGIN
If exists (SELECT T0.CardCode FROM OCRD AS T0 INNER JOIN
			 OCPR T1 ON T0.CardCode = T1.CardCode
		   WHERE (T0.CntctPrsn IS NULL OR ISNUMERIC(T1.Cellolar) = 0 or LEN(T1.Cellolar)>10 ) 
            AND (T0.CardCode = @list_of_cols_val_tab_del))
Begin
SET @error = -1000
SET @error_message = N'Contact person mobile number is mandatory and should be 10 digits numeric code!!'
End
END

Thanks,

Neetu

[former_member209725 · Accepted solution]

Hi Mam..

Thanks for help.

It works a I need.

closed.

Regards,

Ravi

[Former Member]

Hi Ravi............

Try this.......

IF (@transaction_type = 'A'OR @transaction_type ='U') AND @object_type = '2'
BEGIN
If exists (SELECT T0.CardCode FROM OCRD AS T0 INNER JOIN
OCPR T1 ON T0.CardCode = T1.CardCode
WHERE (T0.CntctPrsn IS NULL OR T1.Cellolar is NULL ) And T1.Cellolar in ('Q','W','E','R','T','Y','U','I','O','P','A','S','D','F','G','H','J','K','L','Z','X','C','V','B','N','M','!','@','#','$','%','^','&','*',' ') AND (T0.CardCode = @list_of_cols_val_tab_del))
begin
SET @error = 111
SET @error_message = N'MUST SPECIFY CONTACT PERSON MOBILE NO.'
end
END

Hope this will help you....

Regards,

Rahul