Skip to Content
0
Former Member
Nov 03, 2010 at 03:54 AM

Auto increment Business Partner Code

61 Views

Dear Expert

I have assigned a Formatted Search to auto increment Business Partner code as a format of C00001, C00002, V00001, V00002 etc. The Formatted Search fires when the Business Partner Name changes. The SQL query assigned in the BP code field is as follow:

declare @t char(1)
set @t=$[$40.1]
Select case @t
When 'S' then 'V'
When 'C' then 'C'
When 'L' then 'L' end
+isnull(substring(str(100000+max(right(p.CardCode,5))+1,6),2,5),
        '00001') 
From OCRD p
Where p.CardCode like '%[0-9][0-9][0-9][0-9][0-9]'
  and p.CardType=@t

Also, I have assigned a Store Procedure in the database under SBO_SP_TransactionNotification in order to restrict users from adding Business Partner not following the pattern designed. The code of the Store Procedure is as below:

If @object_type = '2' and @transaction_type in ('A','U') 
BEGIN
declare @t char(1)
set @t=(Select p.CardType from OCRD p
	where p.cardcode=@list_of_cols_val_tab_del) 
if @list_of_cols_val_tab_del !=
(Select case @t
 When 'S' then 'V'
 When 'C' then 'C'
 When 'L' then 'L' end
 +isnull(substring(str(100000+max(right(p.CardCode,5))+1,6),2,5),
        '00001') 
From OCRD p
Where p.CardCode like '%[0-9][0-9][0-9][0-9][0-9]'
  and p.CardType=@t
  and p.cardcode!=@list_of_cols_val_tab_del)
Select @error =1,
 @error_message = 'Partner code invalid !' 
END

By assigning these code, I could achieve the auto increment of BP Code and users are restricted to add BP following the pattern. However, I have experienced a problem. When it is in Find mode of the Business Partner Master Data tab, if I want to navigate to the last record or previous record, the BP code field is automatically updated to the next code and the system prompt me to update. i.e. previously I have added two customers C00001 and C00002, when I use the navigate function (Previous record), the BP code field updated from C00002 to C00003. Please advice if there is any error in the Query or SP code.

Thank you

Regards

Elton