> Hi All,
>
> I am thinking to use a generator for invoice number in my application.
> Any idea/code on how to generate auditable invoice number,
> meaning we CANNOT have a break in invoice number in invoice table.
> Thanks.
>
> regards,
> CM

If I want no gaps in a sequence, I always generate the ID and insert the
value into a table in a separate transaction and commit it. That's the first
step. An insert for this purpose will never deadlock and never raise an
exception (other than for hardware failure) between the ID acquisition and
the commit which should happen within 2-3 lines of code.
Then you are left to update this record with either a valid invoice OR you
update it with an "invoice operation cancelled" operation with annotation to
satisfy your auditor. Such a cancel operation may include the original
transaction amount and a mirrored creation of a credit - depends on your
auditor.

There is another way which I don't like, it involves management/restoration
of unused numbers. You need to constatnly interogate wht table for gaps and
use the gap numbers. My auditors don't like this method mainly because you
end up with a lower number aquiring a transaction date greater than a higher
number. I like the number sequence marrying the date sequence.
Alan