Skip to Content

Autocode generation for business partner in sap b1

Mar 07 at 03:33 PM


avatar image
Former Member

Hi Experts,

I want to make the autocode generation fms based on the customer group.

i am trying with below query, i am not getting the appropriate result.

can anyone help me on this.

declare @type as varchar(10)

declare @temp as char(15)

declare @group as int

declare @user as nvarchar(10)

set @group = (Select max(groupcode) from OCRD where GROUPCODE = $[OCRD.GROUPCODE])

set @type = (select max(cardtype) from OCRD where cardTYPe = $[OCRD.CARDTYPE]) set @user = (SELECT T0.USER_CODE FROM OUSR T0 WHERE t0.INTERNAL_K = $[USER])


SEt @temp=(select max(right(cardcode,4)) + 1 from ocrd where (cardtype=@type) and groupcode= @group)

set @temp= (select distinct max(left(y.GroupName,4)) from ocrd x inner join ocrg y on x.groupcode = y.groupcode where (x.cardtype=@type) and x.groupcode= @group) +'-' +@user +isnull(replicate(0,4-len(@temp)),'')+@temp

select cast(@temp as char(15)) End

Please help me to fix this query for FMS.

10 |10000 characters needed characters left characters exceeded


It would help if you could describe in what way it is not working. What result are you getting with this query, and what would be an appropriate result?



Former Member

Hi Johan,

Thanks for your kind response.

I am looking for the query which give me the autocodo generation for business partner. but the sequence for the business partner should be on the bp group. like

initial 4 digits of groupname + '-' + logged in usercode + '-' + 00001 ex: CADI-2006-00001

sequence should be based on the group name and user code CADI-2006-00001, CADI-2006-00002, CADI-2006-00003....

if the other user logged in with the user code '2007', for the same group it should be like CADI-2007-00001, CADI-2007-00002, CADI-2007-00003....

similarly this should be applied for all the groups and users.

Note: i have also the bp codes manually created. so max(cardcode) will give proper value because some they created in alphanumeric and some they created in numeric.



Ok, I think I understand what you want. The query seems to be correct though. What result is it giving you?



Former Member

Hi Experts, Can anyone help me out for the above query.


* Please Login or Register to Answer, Follow or Comment.

0 Answers