Merge branch 'b-3.6.1' of ../kivitendo-erp_20220811

[kivitendo-erp.git] / sql / Pg-upgrade2 / create_record_template_tables.sql

diff --git a/sql/Pg-upgrade2/create_record_template_tables.sql b/sql/Pg-upgrade2/create_record_template_tables.sql
new file mode 100644 (file)
index 0000000..01adfd3
--- /dev/null
+++ b/sql/Pg-upgrade2/create_record_template_tables.sql
@@ -0,0 +1,64 @@
+-- @tag: create_record_template_tables
+-- @description: Einführung echter Vorlagen in der Finanzbuchhaltung anstelle der Entwurfsfunktion
+-- @depends: release_3_4_1
+
+DROP TABLE IF EXISTS record_template_items;
+DROP TABLE IF EXISTS record_templates;
+DROP TYPE IF EXISTS record_template_type;
+
+CREATE TYPE record_template_type AS ENUM ('ar_transaction', 'ap_transaction', 'gl_transaction');
+CREATE TABLE record_templates (
+  id             SERIAL,
+  template_name  TEXT                 NOT NULL,
+  template_type  record_template_type NOT NULL,
+
+  customer_id    INTEGER,
+  vendor_id      INTEGER,
+  currency_id    INTEGER              NOT NULL,
+  department_id  INTEGER,
+  project_id     INTEGER,
+  employee_id    INTEGER,
+  taxincluded    BOOLEAN              NOT NULL DEFAULT FALSE,
+  direct_debit   BOOLEAN              NOT NULL DEFAULT FALSE,
+  ob_transaction BOOLEAN              NOT NULL DEFAULT FALSE,
+  cb_transaction BOOLEAN              NOT NULL DEFAULT FALSE,
+
+  reference      TEXT,
+  description    TEXT,
+  ordnumber      TEXT,
+  notes          TEXT,
+  ar_ap_chart_id INTEGER,
+
+  itime          TIMESTAMP            NOT NULL DEFAULT now(),
+  mtime          TIMESTAMP            NOT NULL DEFAULT now(),
+
+  PRIMARY KEY (id),
+  CONSTRAINT record_templates_customer_id_fkey    FOREIGN KEY (customer_id)    REFERENCES customer   (id) ON DELETE SET NULL,
+  CONSTRAINT record_templates_vendor_id_fkey      FOREIGN KEY (vendor_id)      REFERENCES vendor     (id) ON DELETE SET NULL,
+  CONSTRAINT record_templates_currency_id_fkey    FOREIGN KEY (currency_id)    REFERENCES currencies (id) ON DELETE CASCADE,
+  CONSTRAINT record_templates_department_id_fkey  FOREIGN KEY (department_id)  REFERENCES department (id) ON DELETE SET NULL,
+  CONSTRAINT record_templates_project_id_fkey     FOREIGN KEY (project_id)     REFERENCES project    (id) ON DELETE SET NULL,
+  CONSTRAINT record_templates_employee_id_fkey    FOREIGN KEY (employee_id)    REFERENCES employee   (id) ON DELETE SET NULL,
+  CONSTRAINT record_templates_ar_ap_chart_id_fkey FOREIGN KEY (ar_ap_chart_id) REFERENCES chart      (id) ON DELETE SET NULL
+);
+
+CREATE TRIGGER mtime_record_templates BEFORE UPDATE ON record_templates FOR EACH ROW EXECUTE PROCEDURE set_mtime();
+
+CREATE TABLE record_template_items (
+  id                 SERIAL,
+  record_template_id INTEGER         NOT NULL,
+
+  chart_id           INTEGER         NOT NULL,
+  tax_id             INTEGER         NOT NULL,
+  project_id         INTEGER,
+  amount1            NUMERIC (15, 5) NOT NULL,
+  amount2            NUMERIC (15, 5),
+  source             TEXT,
+  memo               TEXT,
+
+  PRIMARY KEY (id),
+  CONSTRAINT record_template_items_record_template_id FOREIGN KEY (record_template_id) REFERENCES record_templates (id) ON DELETE CASCADE,
+  CONSTRAINT record_template_items_chart_id           FOREIGN KEY (chart_id)           REFERENCES chart            (id) ON DELETE CASCADE,
+  CONSTRAINT record_template_items_tax_id             FOREIGN KEY (tax_id)             REFERENCES tax              (id) ON DELETE CASCADE,
+  CONSTRAINT record_template_items_project_id         FOREIGN KEY (project_id)         REFERENCES project          (id) ON DELETE SET NULL
+);