Autocode generation for business partner in sap b1

[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])

BEGIN

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.

[Johan_H]

Hi,

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?

Regards,

Johan

[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.

[Former Member]

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

Thanks

[Johan_H]

Hi,

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

Regards,

Johan