From 7017ce31e906b5fa69c34074dd193f0c0c23822c Mon Sep 17 00:00:00 2001
From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4>
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
</links>
</class>
+ <class id="acqofscred" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::ordered_funding_source_credit" oils_persist:tablename="acq.ordered_funding_source_credit" reporter:label="Ordered Funding Source Credit">
+ <fields oils_persist:primary="id">
+ <field reporter:label="Ordered Fund Src ID" name="id" reporter:datatype="id"/>
+ <field reporter:label="Sort Priority" name="sort_priority" reporter:datatype="int"/>
+ <field reporter:label="Sort Date" name="sort_date" reporter:datatype="timestamp"/>
+ <field reporter:label="Funding Source ID" name="funding_source" reporter:datatype="link"/>
+ <field reporter:label="Amount" name="amount" reporter:datatype="money"/>
+ <field reporter:label="Note" name="note" reporter:datatype="text"/>
+ </fields>
+ <links>
+ <link field="funding_source" reltype="has_a" key="id" map="" class="acqfs"/>
+ </links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ </permacrud>
+ </class>
+
<class id="acqfdeb" controller="open-ils.cstore open-ils.reporter-store" oils_obj:fieldmapper="acq::fund_debit" oils_persist:tablename="acq.fund_debit" reporter:label="Debit From Fund">
<fields oils_persist:primary="id" oils_persist:sequence="acq.fund_debit_id_seq">
<field reporter:label="Debit ID" name="id" reporter:datatype="id" />
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 <scott@gmail.com>
+ *
+ * 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 <scott@gmail.com>
+ *
+ * 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.11.0