From 952bba8ad936ab4fb6cee97b9dcc86ab342e9a47 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 1 Dec 2021 12:55:13 -0500 Subject: [PATCH] LP1952931 ASN shipment notifiation SQL Signed-off-by: Bill Erickson --- .../src/sql/Pg/upgrade/XXXX.schema.acq-asn.sql | 28 ++++++++++++++++++++++ 1 file changed, 28 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-asn.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-asn.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-asn.sql new file mode 100644 index 0000000000..24567574ff --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-asn.sql @@ -0,0 +1,28 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version); + +CREATE TABLE acq.shipment_notification ( + 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', + container_code TEX NOT NULL, -- vendor-supplied super-barcode + lading_number TEXT, -- informational + note TEXT, + CONSTRAINT container_code_once_per_provider UNIQUE(provider, container_code) +); + +CREATE INDEX acq_asn_container_code_idx ON acq.shipment_notification (container_code); + +CREATE TABLE acq.shipment_notification_entry ( + id SERIAL PRIMARY KEY, + shipment_notification INT NOT NULL REFERENCES acq.invoice (id) ON DELETE CASCADE, + lineitem INT REFERENCES acq.lineitem (id) ON UPDATE CASCADE ON DELETE SET NULL, + item_count INT NOT NULL -- How many items the provider shipped +); + +COMMIT; + -- 2.11.0