Proposal: Outline of financial table schema for 1.4

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

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
);

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(),
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
);

journal_line replaces acc_trans
create table journal_line (
account_id int references account(id)  not null,
journal_id int references journal(id) not null,
amount numeric not null check (amount <> 'NaN'),
cleared bool not null default false,
memo text,
entered_by int references user(entity_id),
line_id serial not null,
primary key (entry_id) -- required to be a surrogate key because it is
possible to have a single payment which pays two identical bills.
);

payment_map provides the map between journal_lines and the paid
journal entry.  This is almost backward of how SL and LSMB 1.3 do it
but it allows for better tracking of individual payments.
create table payment_map (
line_id int references journal_line(line_id),
journal_id int references journal(id) not null,
primary key(line_id)
);

forex_line privides extensions for forex transaction

create table forex_line (
line_id int references journal_line(line_id),
currency char(3), -- should we look at making a separate currency
table?  Maybe attaching to country?
primary key (line_id)
);

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)
);

CREATE TABLE order_line ( -- replaces invoice and order_item
entry_id serial not null unique,
parts_id int references parts(id),
quantity numeric not null check (quantity <> 'NaN'),
lot_price numeric not null check (quantity <> 'NaN'),
allocated numeric not null check (allocated <> 'NaN' AND
abs(allocated) < abs(quantity))
allocated_price numeric not null check(allocated_price <> 'NaN' AND
abs(allocated_price) < abs(lot_price), -- tracking allocated prices
separately so that we can handle purchase quantities in intelligeable
ways.
lot_size numeric not null check (lot_size <> 'NaN'),
lot_qty numeric not null check (lot_qtty <> 'NaN'),
displayed_sku text not null,
displayed_description text not null
);

CREATE TABLE forex_order_line (
entry_id int not null primary key references order_line(entry_id),
fx_lot_price numeric not null ot null check (fx_lot_price <> 'NaN')
);

Thoughts?

Comments?

Best Wishes,
Chris Travers