Issue #24584:create supply order with correct order number

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

diff --git a/foundation-database/public/functions/convertquote.sql b/foundation-database/public/functions/convertquote.sql
index bf15b9b..c9d7759 100644 (file)
--- a/foundation-database/public/functions/convertquote.sql
+++ b/foundation-database/public/functions/convertquote.sql
@@ -4,6 +4,8 @@ CREATE OR REPLACE FUNCTION convertQuote(INTEGER) RETURNS INTEGER AS $$
 -- See www.xtuple.com/CPAL for the full text of the software license.
 DECLARE
   pQuheadid ALIAS FOR $1;
+  _qunumber TEXT;
+  _ponumber TEXT;
   _soheadid INTEGER;
   _soitemid INTEGER;
   _orderid INTEGER;
@@ -62,13 +64,20 @@ BEGIN
     RETURN -5;
   END IF;
 
-  IF ( (_usespos) AND (NOT _blanketpos) ) THEN
-    PERFORM cohead_id
-    FROM quhead JOIN cohead ON ( (cohead_cust_id=quhead_cust_id) AND
-                                 (UPPER(cohead_custponumber)=UPPER(quhead_custponumber)) )
+  IF (_usespos) 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 (FOUND) THEN
-      RAISE EXCEPTION 'Duplicate Customer PO';
+    IF (_ponumber = '') THEN
+      RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
+                      _qunumber, _qunumber;
+    END IF;
+  
+    IF ( (NOT _blanketpos) AND (_soheadid IS NOT NULL) ) THEN
+      RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
+                      _ponumber, _qunumber,
+                      _ponumber, _qunumber;
     END IF;
   END IF;
   
@@ -180,7 +189,7 @@ BEGIN
     AND   (comment_source_id=pQuheadid) );
 
   FOR _r IN SELECT quitem.*,
-                   quhead_number, quhead_prj_id,
+                   quhead_number, quhead_prj_id, quhead_saletype_id,
                    itemsite_item_id, itemsite_leadtime,
                    itemsite_createsopo, itemsite_createsopr,
                    item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
@@ -215,7 +224,8 @@ BEGIN
 
     IF (fetchMetricBool('enablextcommissionission')) THEN
       PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
-                                        _r.itemsite_item_id, _r.quitem_price,
+                                        _r.itemsite_item_id, _r.quhead_saletype_id,
+                                        _r.quitem_price, _r.quitem_custprice,
                                         _soitemid, 'SalesItem')
       FROM quhead
       WHERE (quhead_id=pQuheadid);
@@ -242,8 +252,10 @@ BEGIN
     IF (_r.quitem_createorder) THEN
 
       IF (_r.item_type IN ('M')) THEN
-        SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
-                         _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'S', _soitemid, _r.quhead_prj_id ) INTO _orderId
+        SELECT createWo( CAST(_soNum AS INTEGER), supply.itemsite_id, 1,
+                         (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
+                         _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo,
+                         'S', _soitemid, _r.quhead_prj_id ) INTO _orderId
         FROM itemsite sold, itemsite supply
         WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
          AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
@@ -258,7 +270,8 @@ BEGIN
            AND  (charass_target_id=_r.quitem_id));
 
       ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
-        SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
+        SELECT createPr( CAST(_soNum AS INTEGER), _r.quitem_itemsite_id,
+                         (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
                          _r.quitem_scheddate, '', 'S', _soitemid ) INTO _orderId;
         _orderType := 'R';
         UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;