SEPA: Feld »Signaturdatum«; Bearbeiten der Felder in Stammdatenmasken; Speichern...

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

diff --git a/sql/Pg-upgrade2/add_customer_mandator_id.sql b/sql/Pg-upgrade2/add_customer_mandator_id.sql
index e27eba5..99a788e 100644 (file)
--- a/sql/Pg-upgrade2/add_customer_mandator_id.sql
+++ b/sql/Pg-upgrade2/add_customer_mandator_id.sql
@@ -1,6 +1,18 @@
 -- @tag: add_customer_mandator_id
--- @description: Einführen einer Mandanten ID Spalte bei Kunden und Lieferanten.
+-- @description: Einführen einer Mandanten-ID- und Signatur-Datums-Spalte bei Kunden.
 -- @depends: release_3_0_0
 
-ALTER TABLE customer ADD mandator_id text;
-ALTER TABLE vendor   ADD mandator_id text;
+ALTER TABLE customer          ADD COLUMN mandator_id                  text;
+ALTER TABLE customer          ADD COLUMN mandate_date_of_signature    date;
+ALTER TABLE sepa_export_items ADD COLUMN vc_mandator_id               text;
+ALTER TABLE sepa_export_items ADD COLUMN vc_mandate_date_of_signature date;
+
+UPDATE sepa_export_items
+SET vc_mandator_id = (
+  SELECT c.customernumber
+  FROM ar
+  LEFT JOIN customer c ON (ar.customer_id = c.id)
+  WHERE ar.id = sepa_export_items.ar_id
+),
+vc_mandate_date_of_signature = '2010-01-01'::date
+WHERE ar_id IS NOT NULL;