Skip to Content
0

Stored Procedure validate phonenumber of Contacts

May 18, 2017 at 01:12 PM

70

avatar image

Hi all,

I am struggling with a SP on the OCPR table which should run from the Business Partner Master Data screen. When I add/edit a contactperson to a businesspartner, I would like to check if the phonenumber of that contact starts with '+'. This seems simple enough:

IF @object_type in ('2')  and @transaction_type in ('A','U')
	BEGIN
		DECLARE @DATA as NVARCHAR(50)
		SET @DATA = (SELECT T0.Name FROM OCPR T0 WHERE T0.CntctCode = @list_of_cols_val_tab_del AND LEFT(T0.Tel1,1) <> '+')
		
	IF @DATA <> ''
		Begin
		select @error = 99, @error_message = 'Invalid phone'
		End
END

But I can't get this to work because it seems T0.CntctCode is not in @list_of_cols_val_tab_del. Which makes sense because I am actually updating the businesspartner. Any help is much appreciated. Brian

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Balaji Selvaraj May 19, 2017 at 03:46 PM
1

Hi Brian,

When you add/edit a contact person to a business partner use below SP. Here i had given mobile no field for where condition you can set Tel1/ Tel2 field as per your need.

IF @transaction_type IN (N'A', N'U') AND (@Object_type IN ('2'))

BEGIN

IF EXISTS(select * FROM OCRD INNER JOIN OCPR T1 ON OCRD.[CardCode] = T1.[CardCode] WHERE (T1.[Cellolar] is null OR T1.[Cellolar]='') AND OCRD.CardCode=@list_of_cols_val_tab_del)

BEGIN

SET @error = 10

SET @error_message = 'Kindly add the contact person mobile number'

END

END

Regards:

Balaji.S

Share
10 |10000 characters needed characters left characters exceeded
Brian de Lange May 31, 2017 at 10:42 AM
0

Hi Balaji,

Thanks. That sent me on the right track to fix the issue

Brian

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Close the thread if you got the solution

Regards:

Balaji.S

0