From 4848249e4f3c2da96637ee6717ef1a5eee8866c8 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
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
</permacrud>
</class>
+ <class id="aiit" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::invoice_item_type" oils_persist:tablename="acq.invoice_item_type" reporter:label="Non-bibliographic Invoice Item Type" oils_persist:field_safe="true">
+ <fields oils_persist:primary="code">
+ <field reporter:label="Code" name="code" reporter:selector="name" reporter:datatype="id"/>
+ <field reporter:label="Label" name="label" reporter:datatype="text" oils_persist:i18n="true"/>
+ </fields>
+ <links/>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="CREATE_INVOICE_ITEM_TYPE" global_required="true"/>
+ <retrieve/>
+ <update permission="UPDATE_INVOICE_ITEM_TYPE" global_required="true"/>
+ <delete permission="DELETE_INVOICE_ITEM_TYPE" global_required="true"/>
+ </actions>
+ </permacrud>
+ </class>
+
+ <class id="acqim" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::invoice_method" oils_persist:tablename="acq.invoice_method" reporter:label="Invoice Method used by Vendor" oils_persist:field_safe="true">
+ <fields oils_persist:primary="code">
+ <field reporter:label="Code" name="code" reporter:selector="name" reporter:datatype="id"/>
+ <field reporter:label="Label" name="label" reporter:datatype="text" oils_persist:i18n="true"/>
+ </fields>
+ <links/>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="CREATE_INVOICE_METHOD" global_required="true"/>
+ <retrieve/>
+ <update permission="UPDATE_INVOICE_METHOD" global_required="true"/>
+ <delete permission="DELETE_INVOICE_METHOD" global_required="true"/>
+ </actions>
+ </permacrud>
+ </class>
+
<class id="ccpbt" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="container::copy_bucket_type" oils_persist:tablename="container.copy_bucket_type" reporter:label="Copy Bucket Type" oils_persist:field_safe="true">
<fields oils_persist:primary="code">
<field reporter:label="Code" name="code" reporter:selector="name" reporter:datatype="id"/>
@@ -4444,6 +4476,105 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
</permacrud>
</class>
+ <class id="acqinv" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::invoice" oils_persist:tablename="acq.invoice" reporter:label="Invoice">
+ <fields oils_persist:primary="id" oils_persist:sequence="acq.invoice_id_seq">
+ <field reporter:label="Internal Invoice ID" name="id" reporter:datatype="id"/>
+ <field reporter:label="Receiver" name="receiver" reporter:datatype="org_unit" />
+ <field reporter:label="Provider" name="provider" reporter:datatype="link"/>
+ <field reporter:label="Shipper" name="shipper" reporter:datatype="link"/>
+ <field reporter:label="Receive Date/Time" name="recv_date" reporter:datatype="timestamp" />
+ <field reporter:label="Receive Method" name="receive_method" reporter:datatype="link" />
+ <field reporter:label="Invoice Type" name="invoice_type" reporter:datatype="text" />
+ <field reporter:label="Vendor Invoice ID" name="inv_ident" reporter:datatype="text" />
+ </fields>
+ <links>
+ <link field="receiver" reltype="has_a" key="id" map="" class="aou"/>
+ <link field="provider" reltype="has_a" key="id" map="" class="acqpro"/>
+ <link field="shipper" reltype="has_a" key="id" map="" class="acqpro"/>
+ <link field="receive_method" reltype="has_a" key="id" map="" class="acqim"/>
+ </links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="ADMIN_INVOICE" context_field="receiver"/>
+ <retrieve permission="ADMIN_INVOICE" context_field="receiver"/>
+ <update permission="ADMIN_INVOICE" context_field="receiver"/>
+ <delete permission="ADMIN_INVOICE" context_field="receiver"/>
+ </actions>
+ </permacrud>
+ </class>
+
+ <class id="acqie" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::invoice_entry" oils_persist:tablename="acq.invoice_entry" reporter:label="Invoice Entry">
+ <fields oils_persist:primary="id" oils_persist:sequence="acq.invoice_entry_id_seq">
+ <field reporter:label="ID" name="id" reporter:datatype="id"/>
+ <field reporter:label="Invoice" name="invoice" reporter:datatype="link" />
+ <field reporter:label="Purchase Order" name="purchase_order" reporter:datatype="link"/>
+ <field reporter:label="PO Line Item" name="lineitem" reporter:datatype="link"/>
+ <field reporter:label="Invoice Item Count" name="inv_item_count" reporter:datatype="int" />
+ <field reporter:label="Physical Item Count" name="phys_item_count" reporter:datatype="int" />
+ <field reporter:label="Note" name="note" reporter:datatype="text" />
+ <field reporter:label="Billed Cost per Item" name="billed_per_item" reporter:datatype="bool" />
+ <field reporter:label="Cost Billed" name="cost_billed" reporter:datatype="money" />
+ <field reporter:label="Actual Cost" name="actual_cost" reporter:datatype="money" />
+ </fields>
+ <links>
+ <link field="invoice" reltype="has_a" key="id" map="" class="acqinv"/>
+ <link field="purchase_order" reltype="has_a" key="id" map="" class="acqpo"/>
+ <link field="lineitem" reltype="has_a" key="id" map="" class="acqli"/>
+ </links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </create>
+ <retrieve permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </retrieve>
+ <update permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </update>
+ <delete permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </delete>
+ </actions>
+ </permacrud>
+ </class>
+
+ <class id="acqii" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::invoice_item" oils_persist:tablename="acq.invoice_item" reporter:label="Non-bibliographic Invoice Item">
+ <fields oils_persist:primary="id" oils_persist:sequence="acq.invoice_item_id_seq">
+ <field reporter:label="ID" name="id" reporter:datatype="id"/>
+ <field reporter:label="Invoice" name="invoice" reporter:datatype="link" />
+ <field reporter:label="Purchase Order" name="purchase_order" reporter:datatype="link"/>
+ <field reporter:label="Fund Debit" name="fund_debit" reporter:datatype="link"/>
+ <field reporter:label="Invoice Item Type" name="inv_item_type" reporter:datatype="link" />
+ <field reporter:label="Title or Item Name" name="title" reporter:datatype="text" />
+ <field reporter:label="Author" name="author" reporter:datatype="text" />
+ <field reporter:label="Note" name="note" reporter:datatype="text" />
+ <field reporter:label="Cost Billed" name="cost_billed" reporter:datatype="money" />
+ <field reporter:label="Actual Cost" name="actual_cost" reporter:datatype="money" />
+ </fields>
+ <links>
+ <link field="invoice" reltype="has_a" key="id" map="" class="acqinv"/>
+ <link field="purchase_order" reltype="has_a" key="id" map="" class="acqpo"/>
+ <link field="fund_debit" reltype="has_a" key="id" map="" class="acqfdeb"/>
+ </links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </create>
+ <retrieve permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </retrieve>
+ <update permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </update>
+ <delete permission="ADMIN_INVOICE">
+ <context link="invoice" field="receiver"/>
+ </delete>
+ </actions>
+ </permacrud>
+ </class>
+
<class id="acqpa" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::provider_address" oils_persist:tablename="acq.provider_address" reporter:label="Provider Address">
<fields oils_persist:primary="id" oils_persist:sequence="acq.provider_address_id_seq">
<field reporter:label="Address Type" name="address_type" reporter:datatype="text"/>
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