Re: Proposal: Outline of financial table schema for 1.4

["Chris Travers" <..hidden..>]

On Nov 13, 2007 4:59 AM, Isak Hansen <..hidden..> wrote:
> On 11/9/07, Chris Travers <..hidden..> wrote:
> > This is offered in the interest of getting discussion going before we
> > start to implement.  It is not a final proposal and one will probably
> > be offered after 1.3 splits off from trunk.
> >
> > At the moment I am thinking of something like the following physical tables:
> >
> > journal_entry_type (
> > id serial,
> > type text primary key
> > );
> >
>
> The entry type is used to keep different subjournals/ledgers apart?

Yes.  Payments vs Sales, vs Purchases, etc.

>
>
> > journal replaces gl and transactions and also is the base table for
> > all transactions:
> > create table journal (
> > source text not null,  -- invoice number or source document number
> > id serial not null,
> > description text not null,
> > locked_by int references session(id) on delete set null, -- used for
> > batch locking of transactions for long payment workloads
> > entry_type int references journal_entry_type(id),
> > transaction_date date not null default now(),
>
> Transaction date enough, or add a field for accounting date as well?

Maybe "entered_on date, posted_on date"?

>
>
> > approved bool default false, -- this way we do vouchers by default,
> > department_id int references department(id) not null, -- thinking we
> > should require department categorizations at some point
> > primary_key(id) -- I don't like surrogate primary keys here but no
> > other set of fields can be guaranteed to be unique
> > );
>
> *snip*
>
> > create table order_type (
> > id serial not null unique,
> > label text primary key
> > ); -- PO, RFQ, Customer RFQ, Sales Order, Quotation, Vendor Quotation,
> > etc.  Invoices are not listed here.
> >
> > create table orders ( -- an invoice is treated as an order with a
> > corresponding journal entry.
> > credit_account int references entity_credit_account(id) not null,
> > id serial not null,
> > include_tax bool default false not null,
> > terms int not null default 0,
> > language_id int references language(id)
> > order_number text not null
> > );
> >
> > create table invoice ( -- journal.source would hold the invoice number
> > order_id int references orders(id),  -- in the case of what we
> > currently do as AR/AP transactions, this would be null
> > journal_id int references journal(id),
> > on_hold bool default false,
> > reverse bool default false,
> > credit_account int references entity_credit_account(id) not null,
> > language_id int references language(id),
> > PRIMARY KEY  (journal_id)
> > );
> >
>
> We need to collect several orders in a single invoice. I imagine
> multiple invoices per order could be useful as well, even if that's
> not a requirement for me.

Hmmm.....  The way LSMB currently does this is to create new orders
and close the old ones.  That could be easily enough managed.
However, I am seeing a few cases where that may not be desirable.  For
example, suppose you open 3 orders with me, and my first invoice I
bill you for everything on the first order, all but one item on the
second order, and a single item on the third.  It might be better to
have a separate mapping of line items to orders and invoices (and this
might make COGS handling easier too).

Best Wishes,
Chris Travers