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.
-
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.
-
Johan Hakkesteegt Former Member
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
Add comment