From 7017ce31e906b5fa69c34074dd193f0c0c23822c Mon Sep 17 00:00:00 2001 From: scottmk Date: Mon, 28 Dec 2009 21:47:36 +0000 Subject: [PATCH] Add new view acq.ordered_funding_source_credit, to define priorities for spending from funding source credits. See COMMENT in Open-ILS/src/sql/Pg/upgrade/0125.schema.acq-ord-fund-src-credit.sql. M Open-ILS/src/sql/Pg/200.schema.acq.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0125.schema.acq-ord-fund-src-credit.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@15237 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 16 +++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 56 +++++++++++++++++ .../0125.schema.acq-ord-fund-src-credit.sql | 61 +++++++++++++++++++ 4 files changed, 134 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0125.schema.acq-ord-fund-src-credit.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 7570dde4a7..014144b374 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4514,6 +4514,22 @@ 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 1f64788254..50b0e86324 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 ('0124'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0125'); -- Scott McKellar 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 02f086c128..61d812168a 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -105,6 +105,62 @@ CREATE TABLE acq.funding_source_credit ( effective_date TIMESTAMPTZ NOT NULL default now() ); +CREATE VIEW acq.ordered_funding_source_credit AS + SELECT + CASE WHEN deadline_date IS NULL THEN + 2 + ELSE + 1 + END AS sort_priority, + CASE WHEN deadline_date IS NULL THEN + effective_date + ELSE + deadline_date + END AS sort_date, + id, + funding_source, + amount, + note + FROM + acq.funding_source_credit; + +COMMENT ON VIEW acq.ordered_funding_source_credit IS $$ +/* + * Copyright (C) 2009 Georgia Public Library Service + * Scott McKellar + * + * The acq.ordered_funding_source_credit view is a prioritized + * ordering of funding source credits. When ordered by the first + * three columns, this view defines the order in which the various + * credits are to be tapped for spending, subject to the allocations + * in the acq.fund_allocation table. + * + * The first column reflects the principle that we should spend + * money with deadlines before spending money without deadlines. + * + * The second column reflects the principle that we should spend the + * oldest money first. For money with deadlines, that means that we + * spend first from the credit with the earliest deadline. For + * money without deadlines, we spend first from the credit with the + * earliest effective date. + * + * The third column is a tie breaker to ensure a consistent + * ordering. + * + * **** + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + */ +$$; + CREATE TABLE acq.fund ( id SERIAL PRIMARY KEY, org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, diff --git a/Open-ILS/src/sql/Pg/upgrade/0125.schema.acq-ord-fund-src-credit.sql b/Open-ILS/src/sql/Pg/upgrade/0125.schema.acq-ord-fund-src-credit.sql new file mode 100644 index 0000000000..cb637b601a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0125.schema.acq-ord-fund-src-credit.sql @@ -0,0 +1,61 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0125'); -- Scott McKellar + +CREATE VIEW acq.ordered_funding_source_credit AS + SELECT + CASE WHEN deadline_date IS NULL THEN + 2 + ELSE + 1 + END AS sort_priority, + CASE WHEN deadline_date IS NULL THEN + effective_date + ELSE + deadline_date + END AS sort_date, + id, + funding_source, + amount, + note + FROM + acq.funding_source_credit; + +COMMENT ON VIEW acq.ordered_funding_source_credit IS $$ +/* + * Copyright (C) 2009 Georgia Public Library Service + * Scott McKellar + * + * The acq.ordered_funding_source_credit view is a prioritized + * ordering of funding source credits. When ordered by the first + * three columns, this view defines the order in which the various + * credits are to be tapped for spending, subject to the allocations + * in the acq.fund_allocation table. + * + * The first column reflects the principle that we should spend + * money with deadlines before spending money without deadlines. + * + * The second column reflects the principle that we should spend the + * oldest money first. For money with deadlines, that means that we + * spend first from the credit with the earliest deadline. For + * money without deadlines, we spend first from the credit with the + * earliest effective date. + * + * The third column is a tie breaker to ensure a consistent + * ordering. + * + * **** + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + */ +$$; + +COMMIT; -- 2.43.2