From 4848249e4f3c2da96637ee6717ef1a5eee8866c8 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 16 Feb 2010 16:33:30 +0000 Subject: [PATCH] initial invoicing schema git-svn-id: svn://svn.open-ils.org/ILS/trunk@15552 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 131 +++++++++++++++++++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 56 +++++++++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 10 ++ .../sql/Pg/upgrade/0162.schema.acq-invoicing.sql | 61 ++++++++++ 5 files changed, 259 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0162.schema.acq-invoicing.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index bd34beff38..06d21d7a11 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -847,6 +847,38 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -4444,6 +4476,105 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index fb3dffedb3..ceb3e8c339 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0161'); --Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0162'); --miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 82e06c7473..ae3632c3d4 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -700,6 +700,62 @@ CREATE INDEX acq_attribution_debit_idx CREATE INDEX acq_attribution_credit_idx ON acq.debit_attribution( funding_source_credit ); +-- Invoicing + +CREATE TABLE acq.invoice_method ( + code TEXT PRIMARY KEY, + name TEXT NOT NULL -- i18n-ize +); +INSERT INTO acq.invoice_method (code,name) VALUES ('EDI',oils_i18n_gettext('EDI', 'EDI', 'acqim', 'name')); +INSERT INTO acq.invoice_method (code,name) VALUES ('PPR',oils_i18n_gettext('PPR', 'Paper', 'acqit', 'name')); + + +CREATE TABLE acq.invoice ( + id SERIAL PRIMARY KEY, + receiver INT NOT NULL REFERENCES actor.org_unit (id), + provider INT NOT NULL REFERENCES acq.provider (id), + shipper INT NOT NULL REFERENCES acq.provider (id), + recv_date TIMESTAMPTZ NOT NULL DEFAULT NOW(), + recv_method TEXT NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI', + inv_type TEXT, -- A "type" field is desired, but no idea what goes here + inv_ident TEXT NOT NULL -- vendor-supplied invoice id/number +); + +CREATE TABLE acq.invoice_entry ( + id SERIAL PRIMARY KEY, + invoice INT NOT NULL REFERENCES acq.invoice (id) ON DELETE CASCADE, + purchase_order INT REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL, + lineitem INT REFERENCES acq.lineitem (id) ON UPDATE CASCADE ON DELETE SET NULL, + inv_item_count INT NOT NULL, -- How many acqlids did they say they sent + phys_item_count INT, -- and how many did staff count + note TEXT, + billed_per_item BOOL, + cost_billed NUMERIC(8,2), + actual_cost NUMERIC(8,2) +); + +CREATE TABLE acq.invoice_item_type ( + code TEXT PRIMARY KEY, + name TEXT NOT NULL -- i18n-ize +); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('TAX',oils_i18n_gettext('TAX', 'Tax', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('PRO',oils_i18n_gettext('PRO', 'Processing Fee', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('SHP',oils_i18n_gettext('SHP', 'Shipping Charge', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('HND',oils_i18n_gettext('HND', 'Handling Charge', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('ITM',oils_i18n_gettext('ITM', 'Non-library Item', 'aiit', 'name')); + +CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc + id SERIAL PRIMARY KEY, + invoice INT NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE, + purchase_order INT REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL, + fund_debit INT REFERENCES acq.fund_debit (id), + inv_item_type TEXT NOT NULL REFERENCES acq.invoice_item_type (code), + title TEXT, + author TEXT, + note TEXT, + cost_billed NUMERIC(8,2), + actual_cost NUMERIC(8,2) +); -- Patron requests CREATE TABLE acq.user_request_type ( diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 00613fec70..ef94becbcd 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -3921,3 +3921,13 @@ VALUES ( oils_i18n_gettext('acq.fund.balance_limit.block', 'When the amount remaining in the fund, including spent money and encumbrances, goes below this percentage, attempts to spend from the fund will be blocked.', 'coust', 'description'), 'integer' ); + +INSERT INTO acq.invoice_item_type (code,name) VALUES ('TAX',oils_i18n_gettext('TAX', 'Tax', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('PRO',oils_i18n_gettext('PRO', 'Processing Fee', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('SHP',oils_i18n_gettext('SHP', 'Shipping Charge', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('HND',oils_i18n_gettext('HND', 'Handling Charge', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('ITM',oils_i18n_gettext('ITM', 'Non-library Item', 'aiit', 'name')); + +INSERT INTO acq.invoice_method (code,name) VALUES ('EDI',oils_i18n_gettext('EDI', 'EDI', 'acqim', 'name')); +INSERT INTO acq.invoice_method (code,name) VALUES ('PPR',oils_i18n_gettext('PPR', 'Paper', 'acqit', 'name')); + diff --git a/Open-ILS/src/sql/Pg/upgrade/0162.schema.acq-invoicing.sql b/Open-ILS/src/sql/Pg/upgrade/0162.schema.acq-invoicing.sql new file mode 100644 index 0000000000..92e7dbad80 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0162.schema.acq-invoicing.sql @@ -0,0 +1,61 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0162'); -- miker + +CREATE TABLE acq.invoice_method ( + code TEXT PRIMARY KEY, + name TEXT NOT NULL -- i18n-ize +); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('EDI',oils_i18n_gettext('EDI', 'EDI', 'acqim', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('PPR',oils_i18n_gettext('PPR', 'Paper', 'acqit', 'name')); + + +CREATE TABLE acq.invoice ( + id SERIAL PRIMARY KEY, + receiver INT NOT NULL REFERENCES actor.org_unit (id), + provider INT NOT NULL REFERENCES acq.provider (id), + shipper INT NOT NULL REFERENCES acq.provider (id), + recv_date TIMESTAMPTZ NOT NULL DEFAULT NOW(), + recv_method TEXT NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI', + inv_type TEXT, -- A "type" field is desired, but no idea what goes here + inv_ident TEXT NOT NULL -- vendor-supplied invoice id/number +); + +CREATE TABLE acq.invoice_entry ( + id SERIAL PRIMARY KEY, + invoice INT NOT NULL REFERENCES acq.invoice (id) ON DELETE CASCADE, + purchase_order INT REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL, + lineitem INT REFERENCES acq.lineitem (id) ON UPDATE CASCADE ON DELETE SET NULL, + inv_item_count INT NOT NULL, -- How many acqlids did they say they sent + phys_item_count INT, -- and how many did staff count + note TEXT, + billed_per_item BOOL, + cost_billed NUMERIC(8,2), + actual_cost NUMERIC(8,2) +); + +CREATE TABLE acq.invoice_item_type ( + code TEXT PRIMARY KEY, + name TEXT NOT NULL -- i18n-ize +); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('TAX',oils_i18n_gettext('TAX', 'Tax', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('PRO',oils_i18n_gettext('PRO', 'Processing Fee', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('SHP',oils_i18n_gettext('SHP', 'Shipping Charge', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('HND',oils_i18n_gettext('HND', 'Handling Charge', 'aiit', 'name')); +INSERT INTO acq.invoice_item_type (code,name) VALUES ('ITM',oils_i18n_gettext('ITM', 'Non-library Item', 'aiit', 'name')); + +CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc + id SERIAL PRIMARY KEY, + invoice INT NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE, + purchase_order INT REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL, + fund_debit INT REFERENCES acq.fund_debit (id), + inv_item_type TEXT NOT NULL REFERENCES acq.invoice_item_type (code), + title TEXT, + author TEXT, + note TEXT, + cost_billed NUMERIC(8,2), + actual_cost NUMERIC(8,2) +); + +COMMIT; + -- 2.11.0