Issue #23255:address code review comments

diff --git a/foundation-database/public/functions/importbankreccleared.sql b/foundation-database/public/functions/importbankreccleared.sql
index 356c46c..4fddd3e 100644 (file)
--- a/foundation-database/public/functions/importbankreccleared.sql
+++ b/foundation-database/public/functions/importbankreccleared.sql
@@ -7,17 +7,19 @@ DECLARE
   _result INTEGER := 0;
   _cleared BOOLEAN;
   _doctype TEXT;
-  _docid INTEGER;
-  _bankadjid INTEGER;
+  _docid INTEGER := -1;
+  _bankadjid INTEGER := -1;
+  _debitbankadjtypeid INTEGER := -1;
+  _creditbankadjtypeid INTEGER := -1;
   _b RECORD;
   _r RECORD;
 
 BEGIN
   -- cache some information
   IF (pBankrecid = -1) THEN
-    _bankrecid = fetchMetricValue('ImportBankRecId');
+    _bankrecid := fetchMetricValue('ImportBankRecId');
   ELSE
-    _bankrecid = pBankrecid;
+    _bankrecid := pBankrecid;
   END IF;
 
   SELECT * INTO _b
@@ -30,6 +32,16 @@ BEGIN
     RAISE EXCEPTION 'bankrec already posted';
   END IF;
 
+   _debitbankadjtypeid := fetchMetricValue('ImportBankRecDebitAdj');
+  IF (_debitbankadjtypeid = -1) THEN
+    RAISE EXCEPTION 'Metric ImportBankRecDebitAdj not defined [xtuple: reconcileBankAccount, -1]';
+  END IF;
+
+   _creditbankadjtypeid := fetchMetricValue('ImportBankRecCreditAdj');
+  IF (_creditbankadjtypeid = -1) THEN
+    RAISE EXCEPTION 'Metric ImportBankRecCreditAdj not defined [xtuple: reconcileBankAccount, -2]';
+  END IF;
+
   -- loop thru bankrecimport and toggle cleared items
   FOR _r IN
   SELECT *,
@@ -65,14 +77,13 @@ BEGIN
         SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
                                     cashrcpt_curr_rate, _r.debit,
                                     _r.bankrecimport_effdate) INTO _cleared
-        FROM cashrcpt JOIN gltrans ON ((gltrans_source='A/R')
-                                  AND (gltrans_doctype='CR')
-                                  AND (NOT gltrans_rec)
-                                  AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
-                                  AND (gltrans_misc_id=cashrcpt_id))
-                      LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
-                                                      bankrecitem_source_id=gltrans_id)
-        WHERE (cashrcpt_id=_docid)
+        FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
+                                                     bankrecitem_source_id=gltrans_id)
+        WHERE (gltrans_source='A/R')
+          AND (gltrans_doctype='CR')
+          AND (gltrans_misc_id=_docid)
+          AND (NOT gltrans_rec)
+          AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
           AND (NOT COALESCE(bankrecitem_cleared, FALSE));
       ELSE
 
@@ -82,7 +93,7 @@ BEGIN
         _bankadjid := -1;
         SELECT bankadj_id INTO _bankadjid
         FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
-        WHERE (bankadjtype_id=fetchMetricValue('ImportBankRecDebitAdj'))
+        WHERE (bankadjtype_id=_debitbankadjtypeid)
           AND (bankadj_docnumber=_r.bankrecimport_reference)
           AND (bankadj_bankaccnt_id=_b.bankaccnt_id);
         IF (NOT FOUND) THEN
@@ -91,23 +102,19 @@ BEGIN
              bankadj_docnumber, bankadj_amount, bankadj_notes,
              bankadj_curr_id)
           VALUES
-            (_b.bankaccnt_id, fetchMetricValue('ImportBankRecDebitAdj'), _r.bankrecimport_effdate,
+            (_b.bankaccnt_id, _debitbankadjtypeid, _r.bankrecimport_effdate,
              _r.bankrecimport_reference, _r.debit, 'Import Bankrec Adjustment',
              _b.bankaccnt_curr_id)
           RETURNING bankadj_id INTO _bankadjid;
         END IF;
 
-        IF (_bankadjid > 0) THEN
-          SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
-                                      1.0, _r.debit,
-                                      _r.bankrecimport_effdate) INTO _cleared
-          FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
-                                                       bankrecitem_source_id=bankadj_id)
-          WHERE (bankadj_id=_bankadjid)
-            AND (NOT COALESCE(bankrecitem_cleared, FALSE));
-        ELSE
-          RAISE EXCEPTION 'bankadjtype not found';
-        END IF;
+        SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
+                                    1.0, _r.debit,
+                                    _r.bankrecimport_effdate) INTO _cleared
+        FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
+                                                     bankrecitem_source_id=bankadj_id)
+        WHERE (bankadj_id=_bankadjid)
+          AND (NOT COALESCE(bankrecitem_cleared, FALSE));
       END IF;
 
       -- done with receipts
@@ -125,14 +132,13 @@ BEGIN
         SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
                                     checkhead_curr_rate, _r.credit,
                                     _r.bankrecimport_effdate) INTO _cleared
-        FROM checkhead JOIN gltrans ON ((gltrans_source='A/P')
-                                    AND (gltrans_doctype='CK')
-                                    AND (NOT gltrans_rec)
-                                    AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
-                                    AND (gltrans_misc_id=checkhead_id))
-                       LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
-                                                       bankrecitem_source_id=gltrans_id)
-        WHERE (checkhead_id=_docid)
+        FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
+                                                     bankrecitem_source_id=gltrans_id)
+        WHERE (gltrans_source='A/P')
+          AND (gltrans_doctype='CK')
+          AND (gltrans_misc_id=_docid)
+          AND (NOT gltrans_rec)
+          AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
           AND (NOT COALESCE(bankrecitem_cleared, FALSE));
       ELSE
 
@@ -142,30 +148,26 @@ BEGIN
         _bankadjid := -1;
         SELECT bankadj_id INTO _bankadjid
         FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
-        WHERE (bankadjtype_id=fetchMetricValue('ImportBankRecCreditAdj'))
+        WHERE (bankadjtype_id=_creditbankadjtypeid)
           AND (bankadj_docnumber=_r.bankrecimport_reference);
         IF (NOT FOUND) THEN
           INSERT INTO bankadj
-            (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date, bankadj_docnumber,
-             bankadj_amount, bankadj_notes, bankadj_curr_id)
-          SELECT
-            _b.bankaccnt_id, bankadjtype_id, _r.bankrecimport_effdate, _r.bankrecimport_reference,
-            _r.credit, 'Import Bankrec Adjustment', _b.bankaccnt_curr_id
-          FROM bankadjtype
-          WHERE (bankadjtype_id=fetchMetricValue('ImportBankRecCreditAdj'))
+            (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
+             bankadj_docnumber, bankadj_amount, bankadj_notes,
+             bankadj_curr_id)
+          VALUES
+            (_b.bankaccnt_id, _creditbankadjtypeid, _r.bankrecimport_effdate,
+             _r.bankrecimport_reference, _r.credit, 'Import Bankrec Adjustment',
+             _b.bankaccnt_curr_id)
           RETURNING bankadj_id INTO _bankadjid;
         END IF;
 
-        IF (_bankadjid > 0) THEN
-          SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
-                                      1.0, _r.credit,
-                                      _r.bankrecimport_effdate) INTO _cleared
-          FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
-                                                       bankrecitem_source_id=_bankadjid)
-          WHERE (NOT COALESCE(bankrecitem_cleared, FALSE));
-        ELSE
-          RAISE EXCEPTION 'bankadjtype not found';
-        END IF;
+        SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
+                                    1.0, _r.credit,
+                                    _r.bankrecimport_effdate) INTO _cleared
+        FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
+                                                     bankrecitem_source_id=_bankadjid)
+        WHERE (NOT COALESCE(bankrecitem_cleared, FALSE));
       END IF;
 
       -- done with checks