Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Problem when create auto numbering in BP master using Formatted Search

Hi all,

I have a problem in creating auto numbering in BP for Customer type using Formatted search

My query is like this

declare @tempNo as char(20) 
set @tempNo=(select  Isnull(max(REPLACE(OCRD.CardCode , LEFT(OCRD.CardCode,10),'')),0)  + 1
from OCRD where (CardType ='C') and LEFT(OCRD.CardCode,3) = 'CU.' ) 
set @tempNo='CU.'+left(convert(varchar,GETDATE(),112),6)+'.'+@tempNo 
select cast(@tempNo as char(20))

It should be creating the new number for example

CU.201101.1

CU.201102.2

CU.201103.3

CU.201103.4

The format will be like this CU.YYYYMM.autonumbering

So if the current year is still the same it will always increase the number and it will reset the number become 1 if the current year is change.

But when i execute the number , the number doesn't increase, still generate the last number CU.201103.4

I have use this query in generate item number, it runs well but different format.

Is there some thing wrong with my query?

Thanks in advance

Regards

Jia shun

Edited by: Jia Shun on Jun 15, 2011 4:30 PM

Former Member
replied

To generate this date-dependent BP code, try this FMS:

declare @ddm varchar(20)
set @ddm='CU.'+(select convert(char(6),GETDATE(),112))+'.'
Select @ddm+
isnull((Select ltrim(max(substring(c.CardCode,9,6)+1))
From OCRD c 
Where substring(c.CardCode,1,10)=@ddm ),'1')

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question