Issue #23741:ensure no duplicate check numbers

[xtuple] / foundation-database / public / functions / setnextchecknumber.sql

diff --git a/foundation-database/public/functions/setnextchecknumber.sql b/foundation-database/public/functions/setnextchecknumber.sql
index a04b21a..98e915e 100644 (file)
--- a/foundation-database/public/functions/setnextchecknumber.sql
+++ b/foundation-database/public/functions/setnextchecknumber.sql
@@ -1,19 +1,34 @@
 
-CREATE OR REPLACE FUNCTION setNextCheckNumber(INTEGER, INTEGER) RETURNS BOOLEAN AS '
+CREATE OR REPLACE FUNCTION setNextCheckNumber(pBankaccntid INTEGER,
+                                              pNextCheckNumber INTEGER) RETURNS BOOLEAN 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
-  pBankaccntid ALIAS FOR $1;
-  pNextCheckNumber ALIAS FOR $2;
+  _nextChkNumber INTEGER;
+  _checkheadid INTEGER;
 
 BEGIN
 
+  _nextChkNumber := pNextCheckNumber;
+
+  WHILE (TRUE) LOOP
+    SELECT checkhead_id INTO _checkheadid
+    FROM checkhead
+    WHERE (checkhead_number=_nextChkNumber)
+      AND (checkhead_bankaccnt_id=pBankaccntid);
+    IF (NOT FOUND) THEN
+      EXIT;
+    ELSE
+      _nextChkNumber := _nextChkNumber + 1;
+    END IF;
+  END LOOP;
+
   UPDATE bankaccnt
-  SET bankaccnt_nextchknum=pNextCheckNumber
+  SET bankaccnt_nextchknum=_nextChkNumber
   WHERE (bankaccnt_id=pBankaccntid);
 
   RETURN TRUE;
 
 END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE 'plpgsql';