cancel
Showing results for 
Search instead for 
Did you mean: 

Customize Format for Document Numbering SAP B1

Former Member
0 Kudos

Hi all,

Just wonder is it possible for SAP B1 to setup the document numbering format as yyyymmddxxxx

yyyy = year

mm = month

dd = day

xxxx = sequential number

So far, I see the supplementary code can go most details to week only, but I want to make it everyday.

Is anyone experience this before. Please share the possible solution or any workaround.

Really appreciate the reply.

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

What types of documents do you want them to be like this? Do you want document number manual or auto add?

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Hope this work on the Journal Entry and should be automatic.

Thanks

Former Member
0 Kudos

That is not possible based on B1 system design. You may create a UDF and assign an FMS to reach your goal.

Former Member
0 Kudos

Hi Gordon,

What you mean is the current design apply to journal entry only? or for all the documents?

Anyway, just wonder do you have any FMS that is close to my issue? Mind to share?

Thanks

Former Member
0 Kudos

Current design of the auto number is by numeric only. This applies to all documents. I never got such requirement to get the number you need.

Try this:

SELECT Replace(Convert(date,GETDATE()), '-','')+CONVERT(varchar(4),$[$8.0.0])

Omit the 2nd part first.

Former Member
0 Kudos

Hi Gordon,

The 2nd part not working, any idea?

Anyway, I had try the query below, it work, but the increment cannot start over on each day.

What I want is the increment refresh on each day.

Btw, I had try to saved this query in a UDF in journal entry module, set to auto refresh when one of the field changes. The problem is, I had try most of the field, but it will not auto refresh. Do you have any idea?

Can help me on this? Really appreciate your time.

Declare @year as Integer

Declare @month as Integer

Declare @day as Integer

Declare @fmt_num as Integer

Declare @time as varchar(10)

SELECT @time=cast(SYSDATETIME() as varchar)

Select @fmt_num=count(OJDT.TransId) from OJDT where Year($[OJDT.refdate.date])=Year(OJDT.refdate) and OJDT.U_SEQUENCENO<>' '

SELECT 'J'+RIGHT(YEAR(@time),2)+replace(str(MONTH(@time),2),' ','0') + replace(str(DAY(@time),2),' ','0') +replace(str(@fmt_num+1,4,0),' ',0)

Thanks

Former Member
0 Kudos

To make it work for each day, you may need a UDT to hold the current sequential number for each new JE added.

Or select $[OJDT.TransId.number] - min(T0.TransId) FROM OJDT WHERE DateDiff(dd,CreateDate, getDate())=0

Former Member
0 Kudos

Hi Gordon,

Sorry, I don't get what you mean by creating a UDT to keep the JE sequential?

And the sequel don't work either.

Or do you have the full sequel to make the sequential work on each day?

Thanks

Former Member
0 Kudos

JE has transid auto increased. You need find a way to get daily number by deducting the first ID min(T0.TransId) for the current day.

UDT will be too much for you to program.