Re: Proposal for database refactoring in 1.4

[Chris Travers <..hidden..>]

On Mon, Oct 3, 2011 at 11:22 AM, John Locke <..hidden..> wrote:
> Hi,
>
> Initial thoughts: Sounds like a huge improvement, and it mostly makes
> sense to me.
>
> The one area I'm not clear on is inventory management, and whether oe
> and order_items should get rolled into the same tables as ar/ap/invoice.
>
> I may be totally off-base here, but I'm thinking that invoices are
> different from orders. How do items on an order get converted to items
> on an order? Is there going to mainly be some sort of "type" column on
> these tables to split it out? Might it be better to keep the order
> management separate from the invoicing, if invoices represent legal
> documents?

They are different in terms of function, but less different in terms
of information stored, and in terms of reporting requirements.

My basic thinking is this:

inventory_orders would represent potential or actual inventory
commitments.   A commitment basically exists in three stages:
1)  Potential (i.e. a quotation, rfq, sales lead, etc)
2)  Actual (i.e. an agreement to commit has been made, so a
sales/purchase order has been issued)
3)  Billed (invoices)

As a commitment moves through this pipeline, it goes from being
completely pro forma to being posted to the books.  The goal
eventually would be to solve some of the existing issues with things
like the requirements report (where partial invoices can mess things
up) and provide robust supply chain reporting capabilities down the
road.  The current system is broken wrt reporting in a number of ways,
and this would simplify each of those.

So what I envision occurring is:

1)  Quotation comes in, we store it in the inventory_order and
inventory_line tables and mark it as type "quotation"
2)  After negotiating with the customer, we create an order, enter
that in, and it is linked to the quotation.  Quotations and orders can
be edited.  The inventory_order table would have a parent int field
for tracking the previous document it was created from and a
consolidated_as field for tracking consolidations.  In this way, we
can preserve knowledge of which order came from which quotation, and
which consolidated order included which other previous orders.  Each
document would have an obsolete flag telling the user that  the order
had been superceded by a new one.
3)  The order can then be partially or wholely converted into an
invoice.  If partially, we obsolete the invoice_lines associated with
the invoice, and split them into two lines, one billed, one unbilled.
4)  When the invoice is saved for approval COGS is not calculated.  It
is calculated only when the invoice is approved.

An invoice with parts references the invoice table which links journal
lines to credit accounts.

What I am thinking of doing is starting on two sides and working
towards the middle.  that means starting with ar/ap/gl transactions
and order entry, and then doing sales/vendor invoices.  ar/ap/gl stuff
is partially complete via an add-on for 1.3.

>
> I'm not saying this is the way it should go -- I'm just asking the
> question "why should these be in the same table?" instead of copying
> these items to equivalent invoice tables at the time of invoice --
> basically keep all the invoice stuff separate from the order stuff.

If we do an equivalent table, maybe it should be inherited?  I guess I
am wondering from a database perspective if equivalent tables of this
sort are actually a good idea.  The case to be made for it would be I
think that this would be that this way you could ensure that COGS
calculations didn't accidently hit order entry lines due to a bug, but
I think this could be done anyway given proper indexes.
>
> What you're describing makes sense to me if an order basically IS an
> invoice, just at an earlier phase of the lifecycle. But it strikes me
> that if you're partially shipping an order and splitting it into
> multiple invoices, that might get messier/harder to figure out what
> happened than if they're treated as two different things.
>
Ok, currently we close the order and open a new one.  However this
runs into issues with the requirements report if you partially ship an
order, then add new items to the open one, and then partially ship
that....  The requirements report has no way of determining exactly
what the order volume for a given month was.....

I think a better way to do this would be to split the existing line
items between billed and unbilled.  This way you aren't stuck trying
to determine whether a closed order was closed because it was
partially shipped, fully shipped, etc.

Best Wishes,
Chris Travers