Re: COGS incorrect when invoices are reposted

[David Tangye <..hidden..>]

On Thu, 2006-09-21 at 22:19 -0700, Chris Travers wrote:
> ... I record $2000 in
> inventory purchases (and my COGS is now $1000 below when I made the
> sale, and so could be negative).  If I don't sell them in that
> accounting period, the numbers are off and need to be adjusted.  This
> isn't the end of the world as at least it is transparent and can be
> adjusted by an accountant
??? Maybe I gotta think this through more, but I think this *is* the end
of the world for an accounting system. Why would an accountant spot it?
Other sales will probably bury the mistake, and I doubt that accountants
in this country check every transaction. Mine checks none, its up to the
corporate bookkeeper to do it right, um well as said before the buck
stops with the director, but in this part of the real world its really
only the book-keeper that sees every transaction.

> I think the best way to handle this isn't to do a simple repurchase but:
> Calculate the COGS on the 100 most recently sold widgets ($1000), take
> this from the COGS in the original invoice ($1000 - $1000 = $0) and
> apply that as an adjustment at the time of the original invoice.
> Again, there are cases where that number may be off but again, I would
> thing it could easily be adjusted for end-of-period reports.

The old system I designed effectively generated and recorded each item
and its costs etc, as each business event occurred that acted on it. To
me that is what relational databases are all about. Then reversals,
returns etc become simple. The schema is expanded to cater for this.

With the current schema, the only way I see to reverse stuff to to not
try to apply the reverse of application logic as it seems LSMB is doing,
but simply write a new record in in the database with negative/opposite
values. Oh but that will not work, as there are no database triggers to
add the reverse (subtract) to related tables. In other words I am back
at square one: seeing how shot this database is.

David's lesson 42: Triggers are your friend.