Issue #23459 Populate new ccpay_card_type from ccard relation.

diff --git a/foundation-database/manifest.js b/foundation-database/manifest.js
index 82d6e51..35330d2 100644 (file)
--- a/foundation-database/manifest.js
+++ b/foundation-database/manifest.js
@@ -920,6 +920,7 @@
     "public/trigger_functions/cashrcptitem.sql",
     "public/trigger_functions/cashrcptmisc.sql",
     "public/trigger_functions/ccard.sql",
+    "public/trigger_functions/ccpay.sql",
     "public/trigger_functions/char.sql",
     "public/trigger_functions/charass.sql",
     "public/trigger_functions/charopt.sql",
@@ -1626,6 +1627,7 @@
     "public/tables/report/WarehouseMasterList.xml",
     "public/tables/report/items.xml",
 
-    "public/patches/fixacl.sql"
+    "public/patches/fixacl.sql",
+    "public/patches/populate_ccpay_card_type.sql"
   ]
 }

diff --git a/foundation-database/public/patches/populate_ccpay_card_type.sql b/foundation-database/public/patches/populate_ccpay_card_type.sql
new file mode 100644 (file)
index 0000000..6326bf9
--- /dev/null
+++ b/foundation-database/public/patches/populate_ccpay_card_type.sql
@@ -0,0 +1,3 @@
+-- Issue #23459 adds ccpay_card_type. Populate it from historical ccard relations.
+UPDATE ccpay SET ccpay_card_type = (SELECT ccard_type FROM ccard WHERE ccard_id = ccpay_ccard_id)
+WHERE ccpay_ccard_id IS NOT NULL;

diff --git a/foundation-database/public/trigger_functions/ccpay.sql b/foundation-database/public/trigger_functions/ccpay.sql
new file mode 100644 (file)
index 0000000..72c1e7c
--- /dev/null
+++ b/foundation-database/public/trigger_functions/ccpay.sql
@@ -0,0 +1,27 @@
+CREATE OR REPLACE FUNCTION _ccpayBeforeTrigger () RETURNS TRIGGER AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+  _cardType TEXT;
+
+BEGIN
+  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
+    -- If ccpay_ccard_id is set, we don't care if ccpay_card_type is set,
+    -- we want to get the Card Type from ccard.
+    IF (NEW.ccpay_ccard_id IS NOT NULL) THEN
+      SELECT ccard_type INTO _cardType
+      FROM ccard
+      WHERE ccard_id = NEW.ccpay_ccard_id;
+
+      IF (_cardType IS NOT NULL) THEN
+        NEW.ccpay_card_type = _cardType;
+      END IF;
+    END IF;
+  END IF;
+
+  RETURN NEW;
+END;
+$$   LANGUAGE 'plpgsql';
+
+SELECT dropIfExists('TRIGGER', 'ccpayBeforeTrigger');
+CREATE TRIGGER ccpayBeforeTrigger BEFORE INSERT OR UPDATE ON ccpay FOR EACH ROW EXECUTE PROCEDURE _ccpayBeforeTrigger();