| Subject | Re: [firebird-support] Generating unique continual integers for invoice numbering |
|---|---|
| Author | Helen Borrie |
| Post date | 2007-12-14T12:56:12Z |
At 10:30 PM 14/12/2007, you wrote:
There are various ways to achieve this unbroken sequence without compromising integrity, but this isn't one of them. You might get some ideas from this paper:
http://www.ibobjects.com/TechInfo.html#ti_AuditableSeries
I'd also STRONGLY recommend that you study your requirements very closely, consulting an auditing accountant if necessary. The usual requirement is to have every possible invoice number *accounted for*, which is not the same as requiring an extant invoice corresponding to every possible invoice number.
What this means to the software developer is that you must make provision to keep audit logs for "unused" numbers, i.e. a file of unused numbers and the reason they became so. Rollbacks are not the only reason why invoice numbers become unused...even in the dark old days of pre-numbered stationery and carbon paper, if a clerk made up an invoice and later had to cancel it for some reason, it was *always* a requirement to keep a record of the lost invoice for the auditors.
Many countries publish official guidelines. It is actually quite likely that you are not allowed to present a system that recycles cancelled invoice numbers.
Do not be tempted to use your invoice numbers as keys for your tables. Use surrogate keys and apply a unique constraint to the invoice number. Link invoice details to invoice headers via the surrogate key, not the invoice number. You can use a generator to create a set of numbers at the beginning of the year, into a table that has fields to store other data, such as status and the value of the primary key of the invoice header. You can normalise and use it directly as a parent to the invoice header or you can use it simply for storage, reading the next unused number into the invoice header and flagging the number in the number table as "used".
./heLen
>My solution is that I have BeforeInsert trigger defined and there I checkAnd if you have two transactions that do this at the same time, they will be duplicates or, if you protect the number with a unique constraint, there will be a unique key violation.
>the last invoice number in the year of date of issue, add 1 and set
>NEW.INVOICE_NUMBER.
There are various ways to achieve this unbroken sequence without compromising integrity, but this isn't one of them. You might get some ideas from this paper:
http://www.ibobjects.com/TechInfo.html#ti_AuditableSeries
I'd also STRONGLY recommend that you study your requirements very closely, consulting an auditing accountant if necessary. The usual requirement is to have every possible invoice number *accounted for*, which is not the same as requiring an extant invoice corresponding to every possible invoice number.
What this means to the software developer is that you must make provision to keep audit logs for "unused" numbers, i.e. a file of unused numbers and the reason they became so. Rollbacks are not the only reason why invoice numbers become unused...even in the dark old days of pre-numbered stationery and carbon paper, if a clerk made up an invoice and later had to cancel it for some reason, it was *always* a requirement to keep a record of the lost invoice for the auditors.
Many countries publish official guidelines. It is actually quite likely that you are not allowed to present a system that recycles cancelled invoice numbers.
Do not be tempted to use your invoice numbers as keys for your tables. Use surrogate keys and apply a unique constraint to the invoice number. Link invoice details to invoice headers via the surrogate key, not the invoice number. You can use a generator to create a set of numbers at the beginning of the year, into a table that has fields to store other data, such as status and the value of the primary key of the invoice header. You can normalise and use it directly as a parent to the invoice header or you can use it simply for storage, reading the next unused number into the invoice header and flagging the number in the number table as "used".
./heLen