second draft, refactored financial schema

[Chris Travers <..hidden..>]

Attached is the new schema.  I have named as a .sql file so that many
text editors will provide color coded syntax to help readability.

If another tool opens up when you try to open it, just open with a text editor.

A couple tables have been renamed to allow for compatibility views
(invoice becomes eca_invoice)

Among other things this adds an order_number field, which tracks
orders of the other party.  I don't know that it is really needed in
eca_invoice except that if we have to stop and we don't get to orders
and invoices in 1.4, this provides a logical stopping point.

Best Wishes,
Chris Travers

db redesign 

New table schema mapped to old

CREATE TABLE journal_type AS (
   id serial not null unique,
   name text primary key
);

INSERT INTO journal_type (id, name) 
VALUES (1, 'General'), 
       (2, 'Sales'),
       (3, 'Purchases'),
       (4, 'Receipts'),
       (5, 'Payments');

CREATE TABLE journal_entry AS (
    id serial not null, --ar.id, ap.id, gl.id, transactions.id
    reference text not null,  -- ar.invnumber, ap.invnumber, gl.reference, acc_trans.source
    description text, --gl.description, acc_trans.memo
    locked_by int references session(session_id) on delete set null, -- used for 
    journal int references journal_type(id), --transactions.table
    post_date date not null default now(), --ar.transdate, ap.transdate, gl.transdate, acc_trans.transdate
    effective_start date not null, -- new, not previously used
    effective_end date not null, -- New, not previously used
    currency char(3) not null,
    approved bool default false, -- this way we do vouchers by default,
    is_template bool default false, -- new, not previously used
    entered_by int not null references entity(id),  -- ar and ap.person_id
    approved_by int references entity(id), --new not prev. used
    primary key (id), -- no other set of fields guaranteed to be unique
    check (is_template is false or approved is false)
            -- templates can never be approved.
);

CREATE UNIQUE INDEX je_unique_source ON (journal, source) 
WHERE journal IN (1, 2); -- cannot reuse GL source and AR invoice numbers

CREATE TABLE journal_line AS (
    account_id int references account(id)  not null, -- acc_trans.chart_id
    journal_id int references journal(id) not null,  -- acc_trans.trans_id
    amount numeric not null check (amount <> 'NaN'), -- acc_trans.amount
    cleared bool not null default false,-- acc_trans.cleared
    reconciliation_report int references cr_report(id),
    project_id int references project(id), -- acc_trans.project_id
    department_id int references department(id) not null, --dpt_trans.department_id
    line_type text references account_link_description,-- new, persistent record
                  -- of what kind of line item was entered.
    id serial,  -- acc_trans.entry_id
    primary key (id) -- required to be a surrogate key because it is possible to have a single payment which pays two identical bills.
);

CREATE TABLE eca_invoice AS (
    order_id int references orders(id),  -- in the case of what we
         -- currently do as AR/AP transactions, this would be null
         -- ar.ordnumber, ap.ordnumber
    journal_id int references journal(id),
    on_hold bool default false, -- ar.on_hold, ap.on_hold
    reverse bool default false, -- ar.reverse, ap.reverse
    credit_id int references entity_credit_account(id) not null,
        -- ar.entity_credit_account, ap.entity_credit_account
    due date not null, --ar.duedate, ap.duedate
    language_code char(6) references language(code), 
            --ar.language_code, ap.language_code
    force_closed bool not null default false, -- ar.force_closed, 
                         -- ap.force_closed
    order_number text, --ar.ponumber, ap.ordnumber
    PRIMARY KEY  (journal_id)
);

COMMENT ON COLUMN inventory_order.order_number IS
$$ This is the order number of the other party.  So for a sales invoice, this
would be a purchase order, and for a vendor invoice, this would be a sales
order.$$;


CREATE TABLE payment_map (
    line_id int references journal_line(line_id),
    pays int references journal(id) not null,
    primary key(line_id)
);

CREATE TABLE inventory_order (
    id serial not null unique,
    reference text not null,
    order_class int not null references oe_class(id), -- oe.
    description text,
    taxincluded bool not null default false, --ar.taxincluded, ap.taxincluded
    parent int references inventory_order(id), --ar.ordnumber, ap.ordnumber
    consolidated_as int references inventory_order(id), -- new
    shipping_carrier text, 
           -- should we create new shipping carrier entity class?
    order_number text, --ar.ponumber, ap.ordnumber
    credit_id int not null references entity_credit_account(id),
            -- oe.entity_credit_account 
    
);

COMMENT ON COLUMN inventory_order.order_number IS
$$ This is the order number of the other party.  So for a sales invoice, this 
would be a purchase order, and for a vendor invoice, this would be a sales
order.$$;

CREATE TABLE inventory_line ( --orderitems oi, invoice in
    id serial primary key,
    order_id int references inventory_order(id), 
              -- replaces oi.trans_id and in.trnas_id
    display_partnumber text not null, --new
    description text not null, -- replaces oi.description, in.description
    qty numeric not null, -- replaces oi.qty, in.qty
    sellprice numeric not null, --replaces oi.sellprice, in.sellprice
    precision int not null, --replaces oi.precision, in.precision
    discount numeric not null default 0, --replaces oi.discount, in.discount
    unit text,
    project_id int references project(id),
    department_id int not null default 0 references department(id), 
             -- replaces ar/ap/oe.department_id
    required_by date, -- replaces oi.reqdate, oe.reqdate, ar.reqdate, 
                      -- ap.reqdate
    shipped_on date, -- replaces oe.ship, partially shipped lines split
    delivered_on date, -- replaces in.deliverydate
    shipping_point text, -- replaces ar/ap.shipping_point
    serial_number text, -- replaces in.serialnumber
    extended_info text, -- replaces in.notes 
    invoice_id int references invoice(journal_id) 
            -- partially billed lines split
);

CREATE TABLE inventory_cogs ( -- replaces inventory.allocated
    sale_line_id int references inventory_line(id),
    purchase_line_id int references inventory_line(id),
    qty numeric,
    primary key (sale_line_id, purchase_line_id)
);

CREATE TABLE journal_note (
   primary key (id),
   internal_only bool not null default false,
   check(note_class = 5),
   foreign key(ref_key) references journal_entry(id)
);

Old-> New Table mapping


CREATE TABLE acc_trans (
  trans_id int NOT NULL REFERENCES transactions(id), -- journal_line.journal_id
  chart_id int NOT NULL REFERENCES  account(id), -- journal_line.ccount_id
  amount NUMERIC, -- journal_line.amount
  transdate date DEFAULT current_date, -- journal_entry.post_date
  source text, -- journal_entry.reference
  cleared bool DEFAULT 'f', -- journal_line.cleared
  fx_transaction bool DEFAULT 'f', -- 
  project_id int, -- journal_line.project_id
  memo text,  -- journal_entry.description
  invoice_id int, -- not used
  approved bool default true, -- journal_entry.approved
  cleared_on date, 
  reconciled_on date, --huh?  duplication? cr_report.report_date
  voucher_id int references voucher(id),  -- voucher.trans_id
  entry_id SERIAL PRIMARY KEY -- journal_line.id
);

CREATE TABLE gl (
  id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
  reference text, -- journal_entry.reference
  description text, -- journal_entry.description
  transdate date DEFAULT current_date, --journal_entry.post_date
  person_id integer references person(id), --journal_entry.entered_by
  notes text, -- journal_note.note
  approved bool default true, --journal_entry.approved
  department_id int default 0 --jounral_line.department_id
);

CREATE TABLE ar (
  id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
  invnumber text, -- journal_entry.reference
  transdate date DEFAULT current_date, --journal_entry.post_date
  entity_id int REFERENCES entity(id), --obsolete,unused
  taxincluded bool, --inventory_order.taxincluded
  amount NUMERIC, -- summary info removed from refactor
  netamount NUMERIC, -- summary info removed from refactor
  paid NUMERIC, -- summary info removed from refactor
  datepaid date, -- summary info removed from refactor
  duedate date, -- invoice.due
  invoice bool DEFAULT 'f', -- unneeded in refactor
  shippingpoint text, --inventory_line.shipping_point
  terms int2 DEFAULT 0, -- reduneant
  notes text, --journal_note.note
  curr char(3) CHECK ( (amount IS NULL AND curr IS NULL)
      OR (amount IS NOT NULL AND curr IS NOT NULL)), -- journal_entry.currency
  ordnumber text, -- replace by order-invoice linking system
  person_id integer references entity_employee(entity_id), 
         --journal_entry.entered_by
  till varchar(20),
         -- will be broken off to separate module
  quonumber text, -- replaced by order-invoice links
  intnotes text,  -- journal_note.note
  department_id int default 0, -- journal_line.department_id
  shipvia text, -- inventory_order.shipping_carrier
  language_code varchar(6), -- inventory_order.languge_code
  ponumber text, -- will need new linking system to implement this functionality
  on_hold bool default false, -- invoice.on_hold
  reverse bool default false, -- invoice.reverse
  approved bool default true, -- journal_entry.approved
  entity_credit_account int references entity_credit_account(id) not null,
           -- invoice.credit_id
  force_closed bool, --invoice.force_closed
  description text, -- journal_entry.description
  unique(invnumber) -- probably a good idea as per Erik's request --CT
);

CREATE TABLE ap (
  id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
  invnumber text, -- journal_entry.reference
  transdate date DEFAULT current_date, --journal_entry.transdate
  entity_id int REFERENCES entity(id), --unused, obsolete, not included
  taxincluded bool DEFAULT 'f', --inventory_order.taxincluded
  amount NUMERIC, -- summary info removed in refactor
  netamount NUMERIC, --summary info removed in refactor
  paid NUMERIC, -- summary info removed in refactor
  datepaid date, --summary info removed in refactor
  duedate date, -- invoice.due
  invoice bool DEFAULT 'f', --unneeded due to refactor
  ordnumber text, -- replaced with order-invoice links
  curr char(3) CHECK ( (amount IS NULL AND curr IS NULL)
    OR (amount IS NOT NULL AND curr IS NOT NULL)) , -- This can be null, but shouldn't be.
    --journal_entry.currency
  notes text, --journal_note.note
  person_id integer references entity_employee(entity_id), 
           -- journal_entry.entered_by
  till varchar(20), -- moved to add-on in refactor
  quonumber text, -- Replaced by order-invoice links
  intnotes text, -- journal_note.note
  department_id int DEFAULT 0, 
      -- journal_line.department_id, inventory_line.department_id
  shipvia text, -- inventory_order.shipping_carrier
  language_code varchar(6), --invoice.language_code
  ponumber text, -- replaced by order/invoice links
  shippingpoint text, -- inventory_line.shipping_point
  on_hold bool default false, --invoice.onhold
  approved bool default true, --journal_entry.aproved
  reverse bool default false, --invoice.reverse
  terms int2 DEFAULT 0, -- redundant and omitted
  description text, -- journal_entry.description
  force_closed bool, -- invoice.force_closed
  entity_credit_account int references entity_credit_account(id) NOT NULL
);

CREATE TABLE invoice (
  id serial PRIMARY KEY,
  trans_id int REFERENCES transactions(id), -- inventory_line.order_id
  parts_id int REFERENCES parts(id), -- inventory_line.parts_id
  description text, --inventory_line.description
  qty NUMERIC, -- inventory_line.qty
  allocated integer, -- inventory_cogs.qty
  sellprice NUMERIC, -- inventory_line.sellprice
  precision int, --inventory_line.precision
  fxsellprice NUMERIC, -- redundant
  discount numeric, --inventory_line.discount
  assemblyitem bool DEFAULT 'f', -- redundant
  unit varchar(5), -- inventory_line.unit
  project_id int, --inventory_line.project_id
  deliverydate date, --inventory_line.delivered_on
  serialnumber text, --inventory_line.serial_number
  notes text -- inventory_line.extended_info
);

CREATE TABLE payment (
  id serial primary key, -- journal_entry.id
  reference text NOT NULL, -- journal_entry.reference
  gl_id     integer references gl(id), -- N/A
  payment_class integer NOT NULL, -- n/a
  payment_date date default current_date, --journal_entry.post_date
  closed bool default FALSE, -- summary data omitted
  entity_credit_id   integer references entity_credit_account(id), 
        -- invoice.credit_id
  employee_id integer references person(id),
        -- journal_entry.entered_by
  currency char(3), -- journal_entry.currency
  notes text, --journal_note.note
  department_id integer default 0 --journal_line.department_id
);

CREATE TABLE payment_links (  -- re-engineered and turned around
  -- here we map from the payment to the original invoice line because that's 
  -- all we can do.  in the new one, we map from the payment_line to the paid
  -- invoice.
  payment_id integer references Payment(id), 
  entry_id   integer references acc_trans(entry_id),
  type       integer);
CREATE TABLE orderitems (
  id serial PRIMARY KEY, -- inventory_line.id
  trans_id int, --inventory_line.order_id
  parts_id int, --inventory_line.parts_id
  description text, --inventory_line.description
  qty numeric, --inventory_line.qty
  sellprice NUMERIC, --incentory_line.sellprice
  precision int, --inventory_line.precision
  discount numeric, --inventory_line.discount
  unit varchar(5), --inventory_line.unit
  project_id int, --inventory_line.project_id
  reqdate date, --inventory_line.required_by
  ship numeric, --inventory_line.shipped_on/inventory_line.qty
  serialnumber text, --inventory_line.serialnumber
  notes text --inventory_line.extended_info
);