Skip to Content
avatar image
Former Member

Autocode generation for business partner in sap b1

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.

Add comment
10|10000 characters needed characters exceeded

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

  • Hi,

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

    Regards,

    Johan

  • Former Member

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

    Thanks

  • Get RSS Feed

0 Answers