Re: Proposal: Outline of financial table schema for 1.4

["Isak Hansen" <..hidden..>]

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?


> 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?


> 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.

Viable to build that on top of this structure? If not, do you think
the use case is common enough to support it in lsmb, or would we have
to customize the schema?


Regards,
Isak