2

I need to generate invoices in large batch which will be transformed to EDI and transported to our HQ. There is an order table with all the orders coming in. At some point in the day I have to grab a set (about 1k) and generate the invoices.

  1. Where would be the best place to generate the invoice numbers? On SQL Server backend? Or grab the batch into a .NET application and then generate the invoice numbers there? The invoice numbers can be random but must not repeat (for 3-4 years). I have a max of 12 digits to play with and can be alpha numeric. Where can I find information on generating invoice numbers.

Note: While I am generating the invoices I need to compute the order totals and tax.

I would appreciate your input.

Greg B
  • 14,597
  • 18
  • 87
  • 141
Saif Khan
  • 18,402
  • 29
  • 102
  • 147

4 Answers4

4

Invoice numbers may be regulated by legal demands (where I live they need to be in sequence, and I don't think that there may be gaps in the sequence).

You often see that the numbers include an element that will scope them in time (such as the year: 200903472). That way you minimize the risk of using the same number twice.

You say you have ~1K invoices a day. That means that 6-figure invoice number will cover your needs. So 4-digit year followed by zero-padded 6-figure invoice number should probably get you going.

I would probably have the database generate them to ensure that they are unique.

Fredrik Mörk
  • 155,851
  • 29
  • 291
  • 343
  • how do I generate this in sql server, well, how do I keep track of the seed so on the next generation I am able to pick up the last. How would one hadle the issue of if the process fails during the invoice process? how do I roll back the sequence? – Saif Khan Jun 23 '09 at 21:14
  • I am not a database expert, but I suppose you could keep a table for invoice numbers (or perhaps one that just stores the highest number used so far for each year), and have a procedure that will pick the latest, add 1, store it and return the result. But I am sure there are more efficient approaches. – Fredrik Mörk Jun 23 '09 at 21:18
  • I am thinking it moght be best to store the seed in the database. On an invoice process I would grab the last seed and increment from there, when the process is completed without errors I just update the seed. – Saif Khan Jun 23 '09 at 21:20
  • That would probably work, but I see two weak points; you need to guarantee that only one single process is working with this, and you need to handle exceptions, so that you don't use 500 new numbers and fail to update the database due to some exception in the invoice generation process. – Fredrik Mörk Jun 23 '09 at 21:29
1

Add a table to your database which stores the value of the last Invoice Number used, and a stored procedure to increment this value and return the new value to you to use in the creation of your Invoice.

When you save your new Invoice, make the call to the SP to get the next Invoice Number one of the last things you do - after validation passed but immdiately before writing to disk - in order to try to minimise the risk of "holes" in your numbering sequence.

kevinw
  • 2,088
  • 1
  • 14
  • 15
  • Yes, generating a sequence w/o gaps is hard, this about the right sequence. The updates to the Invoice and LastNum records should be in 1 transaction. – H H Aug 28 '10 at 21:06
0

Using sequential ids is probably best unless you want them to be random for some reason (like because customers shouldn't be able to guess the order id of another order from roughly the same time period). Using sequential ids allows you to read the current max and then check that none have been written in the range you are going to write just before you commit orders from your batch process.

If you don't want the burden of checking the database and can be sure that other processes aren't going to interfere, you might be able to leverage DateTime.UtcNow and a Base64 conversion. A really clunky illustration might be something like:

Convert.ToBase64String(new byte[] { (byte)DateTime.UtcNow.Year, 
                                    (byte)DateTime.UtcNow.Month,
                                    (byte)DateTime.UtcNow.Day,
                                    (byte)DateTime.UtcNow.Hour,
                                    (byte)DateTime.UtcNow.Minute,
                                    (byte)DateTime.UtcNow.Second,
                                    (byte)DateTime.UtcNow.Millisecond })

Or

Convert.ToBase64String(new byte[] { (byte)DateTime.UtcNow.Ticks })

You'd probably be more interested in a Base32 conversion (it uses letters A-Z and digits 2-7), though there's no native .Net conversion so you'd have to search one out.

Jacob Proffitt
  • 12,664
  • 3
  • 41
  • 47
0

I think if there are no laws to restrict the rules about generate the invoice id, it can be whatever you want to be.

Now I have a good example about:

Actually I'm programming a invoice system and I had generate my id code sequentially, and I got some gaps, because when the customer for example cancel the contract with the company, and I had already generate 12 invoices once before.

(because here we are an ISP company and the contract must be 6, 12 or 24 months)

I got the gap in my database, because the users of the system deleted the canceled invoices. If I really needs a sequential number I got a problem with the law. And I have many other situations here that gave me a gap in my sequential invoice number.

I really don't know how to avoid this kind of problem cause there are numerous situation where it happen. Any Idea?

Community
  • 1
  • 1
devasia2112
  • 5,844
  • 6
  • 36
  • 56
  • 1
    In such as case I am still able to keep the invoice number in sequence because I never really delete the invoice. When the user clicks delete, with reason of course, I just change a flag from valid to invalid. – Saif Khan Nov 10 '10 at 05:17
  • Yes. You are right, but now is to late for me, the users has already deleted some invoices, but from now on, I'm denying the delete operation. Thanks for the tip.! – devasia2112 Nov 12 '10 at 12:52