Re: How to generate unique document number ?

frank.van.bortel_at_gmail.com wrote:
> krislioe_at_gmail.com schreef:
>
> > Hi All,
> >
> > We need to generate Document Number where there should be any gap, so
> > that we cannot use sequence. For example : Invoice Number.
> >
>
> Why not? Do not use the cache on a sequence. It will minimise
> the risk of "loosing" numbers due to shutdown. However, there's
> no always the possibility of a user that decides to rollback the
> transaction, or an error may occur, causing the transaction to
> be rolled back.
>
> > one possible approach like : Select MAX(InvoiceNumber)+1 from invoice,
> > what if that table has millions of rows, is this reliable enough ?
>
> Reliable - in what way? There's not one way you would
> be able to ensure you will not be missing numbers, AND
> have a scalable solution.
>
> > How to avoid two user do the same query at same time that lead to
> > dupplicate number ?
> >
>
> By locking the table - as I said: no way it's going to scale.
> By creating an autonomous transaction - still possible to
> get missing numbers.
>
> Bottom line: use a sequence, and explain some numbers
> may be missing - write to a "missing" table as soon as
> the number gets issued, and remove when the transaction
> completes. Make the write to your "missing" table
> an autonomous transaction

The only reliable, scalable method I know to issue gapless sequential keys is assign the key after the data is committed via a batch run. Using the example of an invoice. We assign the number online using a sequence at the time of shipment prior to actually performing the invoicing process. We have gaps. We insert the sequence value into a audit log in the same transaction that updates the order with the invoice number . Later the batch invoicing process pulls the invoice number from the order (and probably populates the invoice date). This last year new auditors wanted sequential invoice numbers but they accepted the audit invoice number table.

During batch since only one process actually creates the invoices a single row table could be used as the source for the invoice number. To maintain transaction control over the invoices created and numbers used we would write the invoices to a table. That way the number of invoices created would match the value of the invoice number table. At the end of processing the physical invoice table could be dumped to a printer.

For a document you can use a sequence at the time of creation. This could be the PK and allow the populating of associated tables. We will call this the internal key. Then in batch you could update all documents with their external key. You could use a UK constraint on this column. Once the document header is created you could then find and update the related rows using the PK so that each related row now also references the document external key.

This is overkill, but if you MUST have a sequential key it is one way to get it. Now, if you auditors will be any happier once they find out how you assign it may be another story.

There is no need for sequential keys. Cross reference reports which show that every Ordered Item Built was Shipped and Invoiced provide the audit trail, not sequential invoice, shipper, order, or any other type of document number.

HTH -- Mark D Powell -- Received on Wed Sep 13 2006 - 08:44:06 CDT