1 -- @tag: create_record_template_tables
2 -- @description: Einführung echter Vorlagen in der Finanzbuchhaltung anstelle der Entwurfsfunktion
3 -- @depends: release_3_4_1
5 DROP TABLE IF EXISTS record_template_items;
6 DROP TABLE IF EXISTS record_templates;
7 DROP TYPE IF EXISTS record_template_type;
9 CREATE TYPE record_template_type AS ENUM ('ar_transaction', 'ap_transaction', 'gl_transaction');
10 CREATE TABLE record_templates (
12 template_name TEXT NOT NULL,
13 template_type record_template_type NOT NULL,
17 currency_id INTEGER NOT NULL,
18 department_id INTEGER,
21 taxincluded BOOLEAN NOT NULL DEFAULT FALSE,
22 direct_debit BOOLEAN NOT NULL DEFAULT FALSE,
23 ob_transaction BOOLEAN NOT NULL DEFAULT FALSE,
24 cb_transaction BOOLEAN NOT NULL DEFAULT FALSE,
30 ar_ap_chart_id INTEGER,
32 itime TIMESTAMP NOT NULL DEFAULT now(),
33 mtime TIMESTAMP NOT NULL DEFAULT now(),
36 CONSTRAINT record_templates_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE SET NULL,
37 CONSTRAINT record_templates_vendor_id_fkey FOREIGN KEY (vendor_id) REFERENCES vendor (id) ON DELETE SET NULL,
38 CONSTRAINT record_templates_currency_id_fkey FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE CASCADE,
39 CONSTRAINT record_templates_department_id_fkey FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE SET NULL,
40 CONSTRAINT record_templates_project_id_fkey FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE SET NULL,
41 CONSTRAINT record_templates_employee_id_fkey FOREIGN KEY (employee_id) REFERENCES employee (id) ON DELETE SET NULL,
42 CONSTRAINT record_templates_ar_ap_chart_id_fkey FOREIGN KEY (ar_ap_chart_id) REFERENCES chart (id) ON DELETE SET NULL
45 CREATE TRIGGER mtime_record_templates BEFORE UPDATE ON record_templates FOR EACH ROW EXECUTE PROCEDURE set_mtime();
47 CREATE TABLE record_template_items (
49 record_template_id INTEGER NOT NULL,
51 chart_id INTEGER NOT NULL,
52 tax_id INTEGER NOT NULL,
54 amount1 NUMERIC (15, 5) NOT NULL,
55 amount2 NUMERIC (15, 5),
60 CONSTRAINT record_template_items_record_template_id FOREIGN KEY (record_template_id) REFERENCES record_templates (id) ON DELETE CASCADE,
61 CONSTRAINT record_template_items_chart_id FOREIGN KEY (chart_id) REFERENCES chart (id) ON DELETE CASCADE,
62 CONSTRAINT record_template_items_tax_id FOREIGN KEY (tax_id) REFERENCES tax (id) ON DELETE CASCADE,
63 CONSTRAINT record_template_items_project_id FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE SET NULL