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