Generating Invoices

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.