3 SELECT evergreen.upgrade_deps_block_check('1345', :eg_version);
5 CREATE TABLE acq.shipment_notification (
7 receiver INT NOT NULL REFERENCES actor.org_unit (id),
8 provider INT NOT NULL REFERENCES acq.provider (id),
9 shipper INT NOT NULL REFERENCES acq.provider (id),
10 recv_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
11 recv_method TEXT NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI',
12 process_date TIMESTAMPTZ,
13 processed_by INT REFERENCES actor.usr(id) ON DELETE SET NULL,
14 container_code TEXT NOT NULL, -- vendor-supplied super-barcode
15 lading_number TEXT, -- informational
17 CONSTRAINT container_code_once_per_provider UNIQUE(provider, container_code)
20 CREATE INDEX acq_asn_container_code_idx ON acq.shipment_notification (container_code);
22 CREATE TABLE acq.shipment_notification_entry (
23 id SERIAL PRIMARY KEY,
24 shipment_notification INT NOT NULL REFERENCES acq.shipment_notification (id)
26 lineitem INT REFERENCES acq.lineitem (id)
27 ON UPDATE CASCADE ON DELETE SET NULL,
28 item_count INT NOT NULL -- How many items the provider shipped
31 /* TODO alter valid_message_type constraint */
33 ALTER TABLE acq.edi_message DROP CONSTRAINT valid_message_type;
34 ALTER TABLE acq.edi_message ADD CONSTRAINT valid_message_type
50 DELETE FROM acq.edi_message WHERE message_type = 'DESADV';
52 DELETE FROM acq.shipment_notification_entry;
53 DELETE FROM acq.shipment_notification;
55 ALTER TABLE acq.edi_message DROP CONSTRAINT valid_message_type;
56 ALTER TABLE acq.edi_message ADD CONSTRAINT valid_message_type
67 DROP TABLE acq.shipment_notification_entry;
68 DROP TABLE acq.shipment_notification;