Issue #23741:ensure no duplicate check numbers

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

diff --git a/foundation-database/public/functions/fetchnextchecknumber.sql b/foundation-database/public/functions/fetchnextchecknumber.sql
index 888b310..19da7a0 100644 (file)
--- a/foundation-database/public/functions/fetchnextchecknumber.sql
+++ b/foundation-database/public/functions/fetchnextchecknumber.sql
@@ -1,10 +1,11 @@
 
-CREATE OR REPLACE FUNCTION fetchNextCheckNumber(INTEGER) RETURNS INTEGER AS '
+CREATE OR REPLACE FUNCTION fetchNextCheckNumber(pBankaccntid INTEGER) RETURNS INTEGER 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;
   _nextChkNumber INTEGER;
+  _checkheadid INTEGER;
 
 BEGIN
 
@@ -12,6 +13,18 @@ BEGIN
   FROM bankaccnt
   WHERE (bankaccnt_id=pBankaccntid);
 
+  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 = (bankaccnt_nextchknum + 1)
   WHERE (bankaccnt_id=pBankaccntid);
@@ -19,5 +32,5 @@ BEGIN
   RETURN _nextChkNumber;
 
 END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE 'plpgsql';