kivitendo/sql/Pg-upgrade2/reclamations.sql @ master
| 34099460 | Tamino Steinert | -- @tag: reclamations
|
||
-- @description: Add reclamations, reclamation_items and reclamation_reasons
|
||||
-- @depends: release_3_5_7
|
||||
-- @ignore: 0
|
||||
CREATE TABLE reclamation_reasons (
|
||||
id SERIAL PRIMARY KEY,
|
||||
name TEXT NOT NULL,
|
||||
description TEXT NOT NULL,
|
||||
position INTEGER NOT NULL,
|
||||
itime TIMESTAMP without time zone DEFAULT now(),
|
||||
mtime TIMESTAMP without time zone,
|
||||
valid_for_sales BOOLEAN NOT NULL DEFAULT false,
|
||||
valid_for_purchase BOOLEAN NOT NULL DEFAULT false
|
||||
);
|
||||
CREATE TRIGGER mtime_reclamation_reasons
|
||||
BEFORE UPDATE ON reclamation_reasons
|
||||
FOR EACH ROW EXECUTE PROCEDURE set_mtime();
|
||||
CREATE TABLE reclamations (
|
||||
--basic
|
||||
id INTEGER NOT NULL DEFAULT nextval('id'),
|
||||
record_number TEXT NOT NULL,
|
||||
transdate DATE DEFAULT now(),
|
||||
itime TIMESTAMP without time zone DEFAULT now(),
|
||||
mtime TIMESTAMP without time zone,
|
||||
delivered BOOLEAN NOT NULL DEFAULT false,
|
||||
closed BOOLEAN NOT NULL DEFAULT false,
|
||||
--header
|
||||
employee_id INTEGER NOT NULL REFERENCES employee(id),
|
||||
globalproject_id INTEGER REFERENCES project(id),
|
||||
delivery_term_id INTEGER REFERENCES delivery_terms(id),
|
||||
shipto_id INTEGER REFERENCES shipto(shipto_id),
|
||||
department_id INTEGER REFERENCES department(id),
|
||||
contact_id INTEGER REFERENCES contacts(cp_id),
|
||||
shipvia TEXT,
|
||||
transaction_description TEXT,
|
||||
shippingpoint TEXT,
|
||||
cv_record_number TEXT,
|
||||
reqdate DATE,
|
||||
--money/summery
|
||||
amount NUMERIC(15,5),
|
||||
netamount NUMERIC(15,5),
|
||||
payment_id INTEGER REFERENCES payment_terms(id),
|
||||
currency_id INTEGER NOT NULL REFERENCES currencies(id),
|
||||
taxincluded BOOLEAN NOT NULL,
|
||||
tax_point DATE,
|
||||
exchangerate NUMERIC(15,5),
|
||||
taxzone_id INTEGER NOT NULL REFERENCES tax_zones(id),
|
||||
--other
|
||||
notes TEXT,
|
||||
intnotes TEXT,
|
||||
language_id INTEGER REFERENCES language(id),
|
||||
salesman_id INTEGER REFERENCES employee(id),
|
||||
customer_id INTEGER REFERENCES customer(id),
|
||||
vendor_id INTEGER REFERENCES vendor(id),
|
||||
CONSTRAINT reclamations_customervendor_check CHECK (
|
||||
(customer_id IS NOT NULL AND vendor_id IS NULL)
|
||||
OR (vendor_id IS NOT NULL AND customer_id IS NULL)
|
||||
),
|
||||
PRIMARY KEY (id)
|
||||
);
|
||||
CREATE TRIGGER mtime_reclamations BEFORE UPDATE ON reclamations FOR EACH ROW EXECUTE PROCEDURE set_mtime();
|
||||
| 9810bb98 | Tamino Steinert | CREATE INDEX reclamations_record_number_key ON reclamations (record_number);
|
||
CREATE INDEX reclamations_customer_id_key ON reclamations (customer_id);
|
||||
CREATE INDEX reclamations_vendor_id_key ON reclamations (vendor_id);
|
||||
| 34099460 | Tamino Steinert | ALTER TABLE defaults ADD COLUMN p_reclamation_record_number TEXT NOT NULL DEFAULT 0;
|
||
ALTER TABLE defaults ADD COLUMN s_reclamation_record_number TEXT NOT NULL DEFAULT 0;
|
||||
CREATE TABLE reclamation_items (
|
||||
--base
|
||||
id SERIAL PRIMARY KEY,
|
||||
reclamation_id INTEGER NOT NULL REFERENCES reclamations(id) ON DELETE CASCADE,
|
||||
reason_id INTEGER NOT NULL REFERENCES reclamation_reasons(id),
|
||||
reason_description_ext TEXT,
|
||||
reason_description_int TEXT,
|
||||
position INTEGER NOT NULL CHECK(position > 0),
|
||||
itime TIMESTAMP without time zone DEFAULT now(),
|
||||
mtime TIMESTAMP without time zone,
|
||||
--header
|
||||
project_id INTEGER REFERENCES project(id) ON DELETE SET NULL,
|
||||
--part description
|
||||
parts_id INTEGER NOT NULL REFERENCES parts(id),
|
||||
description TEXT,
|
||||
longdescription TEXT,
|
||||
serialnumber TEXT,
|
||||
base_qty REAL,
|
||||
qty REAL,
|
||||
unit character varying(20) REFERENCES units(name),
|
||||
--money
|
||||
sellprice NUMERIC(15,5),
|
||||
lastcost NUMERIC(15,5),
|
||||
discount REAL,
|
||||
pricegroup_id INTEGER REFERENCES pricegroup(id),
|
||||
price_factor_id INTEGER REFERENCES price_factors(id),
|
||||
price_factor NUMERIC(15,5) DEFAULT 1,
|
||||
active_price_source TEXT NOT NULL DEFAULT ''::text,
|
||||
active_discount_source TEXT NOT NULL DEFAULT ''::text,
|
||||
--other
|
||||
reqdate DATE
|
||||
);
|
||||
CREATE TRIGGER mtime_reclamation_items BEFORE UPDATE ON reclamation_items FOR EACH ROW EXECUTE PROCEDURE set_mtime();
|