3 INSERT INTO config.upgrade_log (version) VALUES ('0162'); -- miker
5 CREATE TABLE acq.invoice_method (
7 name TEXT NOT NULL -- i18n-ize
9 INSERT INTO acq.invoice_method (code,name) VALUES ('EDI',oils_i18n_gettext('EDI', 'EDI', 'acqim', 'name'));
10 INSERT INTO acq.invoice_method (code,name) VALUES ('PPR',oils_i18n_gettext('PPR', 'Paper', 'acqit', 'name'));
13 CREATE TABLE acq.invoice (
14 id SERIAL PRIMARY KEY,
15 receiver INT NOT NULL REFERENCES actor.org_unit (id),
16 provider INT NOT NULL REFERENCES acq.provider (id),
17 shipper INT NOT NULL REFERENCES acq.provider (id),
18 recv_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
19 recv_method TEXT NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI',
20 inv_type TEXT, -- A "type" field is desired, but no idea what goes here
21 inv_ident TEXT NOT NULL -- vendor-supplied invoice id/number
24 CREATE TABLE acq.invoice_entry (
25 id SERIAL PRIMARY KEY,
26 invoice INT NOT NULL REFERENCES acq.invoice (id) ON DELETE CASCADE,
27 purchase_order INT REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
28 lineitem INT REFERENCES acq.lineitem (id) ON UPDATE CASCADE ON DELETE SET NULL,
29 inv_item_count INT NOT NULL, -- How many acqlids did they say they sent
30 phys_item_count INT, -- and how many did staff count
33 cost_billed NUMERIC(8,2),
34 actual_cost NUMERIC(8,2)
37 CREATE TABLE acq.invoice_item_type (
38 code TEXT PRIMARY KEY,
39 name TEXT NOT NULL -- i18n-ize
41 INSERT INTO acq.invoice_item_type (code,name) VALUES ('TAX',oils_i18n_gettext('TAX', 'Tax', 'aiit', 'name'));
42 INSERT INTO acq.invoice_item_type (code,name) VALUES ('PRO',oils_i18n_gettext('PRO', 'Processing Fee', 'aiit', 'name'));
43 INSERT INTO acq.invoice_item_type (code,name) VALUES ('SHP',oils_i18n_gettext('SHP', 'Shipping Charge', 'aiit', 'name'));
44 INSERT INTO acq.invoice_item_type (code,name) VALUES ('HND',oils_i18n_gettext('HND', 'Handling Charge', 'aiit', 'name'));
45 INSERT INTO acq.invoice_item_type (code,name) VALUES ('ITM',oils_i18n_gettext('ITM', 'Non-library Item', 'aiit', 'name'));
47 CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc
48 id SERIAL PRIMARY KEY,
49 invoice INT NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE,
50 purchase_order INT REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
51 fund_debit INT REFERENCES acq.fund_debit (id),
52 inv_item_type TEXT NOT NULL REFERENCES acq.invoice_item_type (code),
56 cost_billed NUMERIC(8,2),
57 actual_cost NUMERIC(8,2)