Issue #24315:consolidate po check functions and make error translatable

diff --git a/foundation-database/public/functions/convertquote.sql b/foundation-database/public/functions/convertquote.sql
index 85b97d5..e26484e 100644 (file)
--- a/foundation-database/public/functions/convertquote.sql
+++ b/foundation-database/public/functions/convertquote.sql
@@ -65,22 +65,16 @@ BEGIN
   END IF;
 
   IF (_usespos) THEN
-    SELECT quhead_number INTO _qunumber
-    FROM quhead
-    WHERE (quhead_id=pQuheadid)
-      AND (COALESCE(quhead_custponumber, '') = '');
-    IF (FOUND) THEN
+    SELECT quhead_number, COALESCE(quhead_custponumber, ''), cohead_id INTO _qunumber, _ponumber, _soheadid
+    FROM quhead LEFT OUTER JOIN cohead ON ( (cohead_cust_id=quhead_cust_id) AND
+                                            (UPPER(cohead_custponumber)=UPPER(quhead_custponumber)) )
+    WHERE (quhead_id=pQuheadid);
+    IF (_ponumber = '') THEN
       RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
                       _qunumber, _qunumber;
     END IF;
-  END IF;
   
-  IF ( (_usespos) AND (NOT _blanketpos) ) THEN
-    SELECT quhead_number, quhead_custponumber INTO _qunumber, _ponumber
-    FROM quhead JOIN cohead ON ( (cohead_cust_id=quhead_cust_id) AND
-                                 (UPPER(cohead_custponumber)=UPPER(quhead_custponumber)) )
-    WHERE (quhead_id=pQuheadid);
-    IF (FOUND) THEN
+    IF ( (NOT _blanketpos) AND (_soheadid IS NOT NULL) ) THEN
       RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
                       _ponumber, _qunumber,
                       _ponumber, _qunumber;

diff --git a/foundation-database/public/functions/convertquotetoinvoice.sql b/foundation-database/public/functions/convertquotetoinvoice.sql
index b64426d..22c8c21 100644 (file)
--- a/foundation-database/public/functions/convertquotetoinvoice.sql
+++ b/foundation-database/public/functions/convertquotetoinvoice.sql
@@ -69,6 +69,23 @@ BEGIN
   END IF;
 
 -- PO/blanket PO checks
+  IF (_usespos) THEN
+    SELECT quhead_number, COALESCE(quhead_custponumber, ''), invchead_id INTO _qunumber, _ponumber, _iheadid
+    FROM quhead LEFT OUTER JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
+                                              (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
+    WHERE (quhead_id=pQuheadid);
+    IF (_ponumber = '') THEN
+      RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
+                      _qunumber, _qunumber;
+    END IF;
+  
+    IF ( (NOT _blanketpos) AND (_iheadid IS NOT NULL) ) THEN
+      RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
+                      _ponumber, _qunumber,
+                      _ponumber, _qunumber;
+    END IF;
+  END IF;
+  
 
   IF (_usespos) THEN
     SELECT quhead_number INTO _qunumber