Accepts false positive: new default parameter was added to C_ORDER_POST1.xml (issue 23885)
authorMiguel A. Alsasua <miguel.alsasua@openbravo.com>
Thu, 26 Jun 2014 15:47:58 +0200
changeset 564 7e96b012f728
parent 563 6df910d39bed
child 565 be1d431cfe98
Accepts false positive: new default parameter was added to C_ORDER_POST1.xml (issue 23885)
model/src-db/database/model/functions/C_ORDER_POST1.xml
--- a/model/src-db/database/model/functions/C_ORDER_POST1.xml	Fri Jun 20 09:19:49 2014 +0200
+++ b/model/src-db/database/model/functions/C_ORDER_POST1.xml	Thu Jun 26 15:47:58 2014 +0200
@@ -7,6 +7,9 @@
       <parameter name="p_order_id" type="VARCHAR" mode="in">
         <default/>
       </parameter>
+      <parameter name="p_recalculatediscounts" type="VARCHAR" mode="in">
+        <default><![CDATA[Y]]></default>
+      </parameter>
       <body><![CDATA[/*************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
@@ -121,6 +124,7 @@
   v_productname m_product.name%TYPE;
 
   v_iscashvat C_Order.IsCashVat%TYPE;
+  v_recalculateDiscounts VARCHAR(1):='Y';
 
   BEGIN
     IF (p_PInstance_ID IS NOT NULL) THEN
@@ -145,6 +149,7 @@
       END LOOP; -- Get Parameter
     ELSE
       v_Record_ID:=p_Order_ID;
+      v_recalculateDiscounts := p_recalculateDiscounts;
       SELECT CREATEDBY INTO v_User  FROM C_ORDER  WHERE C_ORDER_ID=p_Order_ID;
     END IF;
     DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID) ;
@@ -795,7 +800,7 @@
      /**************************************************************************
       * Calculate promotions                                                   
       *************************************************************************/
-      IF (v_DocAction = 'CO' AND v_isreturndoctype = 'N') THEN
+      IF (v_DocAction = 'CO' AND v_isreturndoctype = 'N' AND v_recalculateDiscounts = 'Y') THEN
          M_PROMOTION_CALCULATE('O', v_Record_ID, v_User);
       END IF;
 
@@ -803,136 +808,140 @@
       * Calculate Discounts
       *************************************************************************/
 
-      -- Delete first previous discounts (if possible) and then recalculate them
-      UPDATE C_ORDER
-      SET DocStatus='IP', -- In progress
-          Processing='N',
-          Processed='N',
-          Updated=now(),
-          UpdatedBy=v_User
-      WHERE C_Order_ID=v_Record_ID;
+      -- if sales order was created from quotation with "firm quotation" check
+      -- then discounts are not recalculated
+      IF (v_recalculateDiscounts = 'Y') THEN
+        -- Delete first previous discounts (if possible) and then recalculate them
+        UPDATE C_ORDER
+        SET DocStatus='IP', -- In progress
+            Processing='N',
+            Processed='N',
+            Updated=now(),
+            UpdatedBy=v_User
+        WHERE C_Order_ID=v_Record_ID;
 
-      DELETE
-      FROM C_ORDERLINE
-      WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
-        AND C_ORDER_ID = v_Record_ID
-        AND NOT EXISTS (SELECT C_INVOICELINE_ID FROM C_INVOICELINE WHERE C_INVOICELINE.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID)
-        AND NOT EXISTS (SELECT M_INOUTLINE_ID FROM M_INOUTLINE WHERE M_INOUTLINE.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID)
-        AND NOT EXISTS (SELECT M_MATCHPO_ID FROM M_MATCHPO WHERE M_MATCHPO.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID);
+        DELETE
+        FROM C_ORDERLINE
+        WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
+          AND C_ORDER_ID = v_Record_ID
+          AND NOT EXISTS (SELECT C_INVOICELINE_ID FROM C_INVOICELINE WHERE C_INVOICELINE.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID)
+          AND NOT EXISTS (SELECT M_INOUTLINE_ID FROM M_INOUTLINE WHERE M_INOUTLINE.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID)
+          AND NOT EXISTS (SELECT M_MATCHPO_ID FROM M_MATCHPO WHERE M_MATCHPO.C_ORDERLINE_ID = C_ORDERLINE.C_ORDERLINE_ID);
 
-      UPDATE C_ORDERLINE
-      SET pricelist = 0, priceactual = 0, pricelimit = 0, linenetamt = 0, pricestd = 0
-      WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
-        AND C_ORDER_ID=v_Record_ID;
+        UPDATE C_ORDERLINE
+        SET pricelist = 0, priceactual = 0, pricelimit = 0, linenetamt = 0, pricestd = 0
+        WHERE C_ORDER_DISCOUNT_ID IS NOT NULL
+          AND C_ORDER_ID=v_Record_ID;
 
-      UPDATE C_ORDER
-      SET DocStatus=v_DocStatus, -- restore
-          Processing=v_IsProcessing,
-          Processed=v_IsProcessed,
-          Updated=now(),
-          UpdatedBy=v_User
-      WHERE C_Order_ID=v_Record_ID;
+        UPDATE C_ORDER
+        SET DocStatus=v_DocStatus, -- restore
+            Processing=v_IsProcessing,
+            Processed=v_IsProcessed,
+            Updated=now(),
+            UpdatedBy=v_User
+        WHERE C_Order_ID=v_Record_ID;
 
-      v_CumDiscount:=0;
-      v_OldCumDiscount:=0;
-      v_Line:=10;
-      v_precision:=0;
-      SELECT MAX(LINE)
-        INTO v_OrderLineSeqNo
-      FROM C_ORDERLINE
-      WHERE C_ORDER_ID=v_Record_ID;
-      SELECT PricePrecision INTO v_precision
-      FROM C_ORDER o, C_CURRENCY c
-      WHERE o.C_ORDER_ID = v_Record_ID AND  o.C_CURRENCY_ID = c.C_CURRENCY_ID;
-      FOR Cur_COrderDiscount IN
-         (SELECT C_ORDER_DISCOUNT.C_ORDER_DISCOUNT_ID, C_DISCOUNT.DISCOUNT, C_DISCOUNT.M_PRODUCT_ID, C_DISCOUNT.NAME,
-            C_ORDER_DISCOUNT.CASCADE, C_DISCOUNT.C_DISCOUNT_ID, M_PRODUCT.C_UOM_ID
-          FROM C_ORDER_DISCOUNT, C_DISCOUNT, M_PRODUCT
-          WHERE C_ORDER_DISCOUNT.C_DISCOUNT_ID=C_DISCOUNT.C_DISCOUNT_ID
-            AND C_DISCOUNT.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID
-            AND C_ORDER_DISCOUNT.C_ORDER_ID=v_Record_ID
-            AND C_ORDER_DISCOUNT.ISACTIVE='Y'
-            AND C_DISCOUNT.ISACTIVE='Y'
-          ORDER BY C_ORDER_DISCOUNT.LINE
-          )
-      LOOP
-        v_CumDiscount:=(1-v_OldCumDiscount) * Cur_COrderDiscount.Discount/100;
-        v_OldCumDiscount:=v_OldCumDiscount + v_CumDiscount;
-        FOR Cur_TaxDiscount IN
-           (SELECT C_ORDERLINE.C_TAX_ID,
-              SUM(C_ORDERLINE.LINENETAMT) AS LINENETAMT,
-              SUM(C_ORDERLINE.LINE_GROSS_AMOUNT) AS LINEGROSSAMT
-            FROM C_ORDERLINE
-            WHERE C_ORDER_ID=v_Record_ID
-              AND C_ORDERLINE.LINENETAMT<>0
-              AND C_ORDER_DISCOUNT_ID IS NULL
-            GROUP BY C_TAX_ID
+        v_CumDiscount:=0;
+        v_OldCumDiscount:=0;
+        v_Line:=10;
+        v_precision:=0;
+        SELECT MAX(LINE)
+          INTO v_OrderLineSeqNo
+        FROM C_ORDERLINE
+        WHERE C_ORDER_ID=v_Record_ID;
+        SELECT PricePrecision INTO v_precision
+        FROM C_ORDER o, C_CURRENCY c
+        WHERE o.C_ORDER_ID = v_Record_ID AND  o.C_CURRENCY_ID = c.C_CURRENCY_ID;
+        FOR Cur_COrderDiscount IN
+           (SELECT C_ORDER_DISCOUNT.C_ORDER_DISCOUNT_ID, C_DISCOUNT.DISCOUNT, C_DISCOUNT.M_PRODUCT_ID, C_DISCOUNT.NAME,
+              C_ORDER_DISCOUNT.CASCADE, C_DISCOUNT.C_DISCOUNT_ID, M_PRODUCT.C_UOM_ID
+            FROM C_ORDER_DISCOUNT, C_DISCOUNT, M_PRODUCT
+            WHERE C_ORDER_DISCOUNT.C_DISCOUNT_ID=C_DISCOUNT.C_DISCOUNT_ID
+              AND C_DISCOUNT.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID
+              AND C_ORDER_DISCOUNT.C_ORDER_ID=v_Record_ID
+              AND C_ORDER_DISCOUNT.ISACTIVE='Y'
+              AND C_DISCOUNT.ISACTIVE='Y'
+            ORDER BY C_ORDER_DISCOUNT.LINE
             )
         LOOP
-          IF (v_istaxincluded = 'Y') THEN
-            IF (Cur_COrderDiscount.CASCADE='Y') THEN
-              v_line_gross_amount:=(-1) * Cur_TaxDiscount.LINEGROSSAMT * v_CumDiscount;
+          v_CumDiscount:=(1-v_OldCumDiscount) * Cur_COrderDiscount.Discount/100;
+          v_OldCumDiscount:=v_OldCumDiscount + v_CumDiscount;
+          FOR Cur_TaxDiscount IN
+             (SELECT C_ORDERLINE.C_TAX_ID,
+                SUM(C_ORDERLINE.LINENETAMT) AS LINENETAMT,
+                SUM(C_ORDERLINE.LINE_GROSS_AMOUNT) AS LINEGROSSAMT
+              FROM C_ORDERLINE
+              WHERE C_ORDER_ID=v_Record_ID
+                AND C_ORDERLINE.LINENETAMT<>0
+                AND C_ORDER_DISCOUNT_ID IS NULL
+              GROUP BY C_TAX_ID
+              )
+          LOOP
+            IF (v_istaxincluded = 'Y') THEN
+              IF (Cur_COrderDiscount.CASCADE='Y') THEN
+                v_line_gross_amount:=(-1) * Cur_TaxDiscount.LINEGROSSAMT * v_CumDiscount;
+              ELSE
+                v_line_gross_amount:=(-1) * Cur_TaxDiscount.LINEGROSSAMT * Cur_COrderDiscount.Discount/100;
+              END IF;
+              v_Discount:= C_GET_NET_PRICE_FROM_GROSS(Cur_TaxDiscount.C_TAX_ID, v_line_gross_amount, 0, v_precision, 1);
+              v_gross_unit_price:= v_line_gross_amount;
             ELSE
-              v_line_gross_amount:=(-1) * Cur_TaxDiscount.LINEGROSSAMT * Cur_COrderDiscount.Discount/100;
+              IF (Cur_COrderDiscount.CASCADE='Y') THEN
+                v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * v_CumDiscount;
+              ELSE
+                v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * Cur_COrderDiscount.Discount/100;
+              END IF;
+              v_gross_unit_price:= 0;
+              v_line_gross_amount:= 0;
             END IF;
-            v_Discount:= C_GET_NET_PRICE_FROM_GROSS(Cur_TaxDiscount.C_TAX_ID, v_line_gross_amount, 0, v_precision, 1);
-            v_gross_unit_price:= v_line_gross_amount;
-          ELSE
-            IF (Cur_COrderDiscount.CASCADE='Y') THEN
-              v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * v_CumDiscount;
-            ELSE
-              v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * Cur_COrderDiscount.Discount/100;
-            END IF;
-            v_gross_unit_price:= 0;
-            v_line_gross_amount:= 0;
-          END IF;
 
-          SELECT COUNT(*) INTO v_DiscountExist FROM C_ORDERLINE
-          WHERE C_ORDERLINE.C_ORDER_DISCOUNT_ID = Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
-            AND C_ORDERLINE.C_TAX_ID = Cur_TaxDiscount.C_TAX_ID;
-
-          IF (v_DiscountExist = 0) THEN
-            v_OrderLineSeqNo:=10 + v_OrderLineSeqNo;
-            v_OrderLine:=get_uuid();
-            INSERT INTO c_orderline
-              (
-                c_orderline_id, ad_client_id, ad_org_id, isactive, created, createdby,
-                updated, updatedby, c_order_id, line, c_bpartner_id, c_bpartner_location_id,
-                dateordered, datepromised, datedelivered, dateinvoiced, description,
-                m_product_id, m_warehouse_id, directship, c_uom_id, qtyordered,
-                qtyreserved, qtydelivered, qtyinvoiced, m_shipper_id, c_currency_id,
-                pricelist, priceactual, pricelimit, linenetamt, discount, freightamt,
-                c_charge_id, chargeamt, c_tax_id, s_resourceassignment_id, ref_orderline_id,
-                m_attributesetinstance_id, isdescription, quantityorder, m_product_uom_id,
-                m_offer_id, pricestd, C_ORDER_DISCOUNT_ID,
-                gross_unit_price, taxbaseamt,line_gross_amount
-              )
-            VALUES
-              (
-                v_OrderLine, v_Client_ID, v_Org_ID, 'Y', now(), v_UpdatedBy,
-                now(), v_UpdatedBy, v_Record_ID, v_OrderLineSeqNo, NULL, NULL,
-                now(), now(), now(), now(), Cur_COrderDiscount.NAME,
-                Cur_COrderDiscount.M_PRODUCT_ID, v_M_Warehouse_ID, 'N', Cur_COrderDiscount.C_UOM_ID, 1,
-                0, 0, 0, NULL, v_c_currency_id,
-                ROUND(v_Discount,v_precision), ROUND(v_Discount,v_precision), ROUND(v_Discount,v_precision), ROUND(v_Discount,v_precision), 0, 0,
-                NULL, NULL, Cur_TaxDiscount.C_TAX_ID, NULL, NULL,
-                NULL, 'N', NULL, NULL,
-                NULL, ROUND(v_Discount,v_precision), NULL,
-                ROUND(v_gross_unit_price, v_precision), ROUND(v_Discount,v_precision),ROUND(v_line_gross_amount, v_precision)
-              );
-
-            UPDATE C_ORDERLINE
-            SET C_ORDER_DISCOUNT_ID=Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
-            WHERE C_ORDERLINE_ID=v_OrderLine;
-          ELSE
-            UPDATE C_ORDERLINE 
-            SET pricelist = ROUND(v_Discount,v_precision), priceactual = ROUND(v_Discount,v_precision), pricelimit = ROUND(v_Discount,v_precision), linenetamt = ROUND(v_Discount,v_precision), pricestd = ROUND(v_Discount,v_precision)
+            SELECT COUNT(*) INTO v_DiscountExist FROM C_ORDERLINE
             WHERE C_ORDERLINE.C_ORDER_DISCOUNT_ID = Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
               AND C_ORDERLINE.C_TAX_ID = Cur_TaxDiscount.C_TAX_ID;
-          END IF;
+
+            IF (v_DiscountExist = 0) THEN
+              v_OrderLineSeqNo:=10 + v_OrderLineSeqNo;
+              v_OrderLine:=get_uuid();
+              INSERT INTO c_orderline
+                (
+                  c_orderline_id, ad_client_id, ad_org_id, isactive, created, createdby,
+                  updated, updatedby, c_order_id, line, c_bpartner_id, c_bpartner_location_id,
+                  dateordered, datepromised, datedelivered, dateinvoiced, description,
+                  m_product_id, m_warehouse_id, directship, c_uom_id, qtyordered,
+                  qtyreserved, qtydelivered, qtyinvoiced, m_shipper_id, c_currency_id,
+                  pricelist, priceactual, pricelimit, linenetamt, discount, freightamt,
+                  c_charge_id, chargeamt, c_tax_id, s_resourceassignment_id, ref_orderline_id,
+                  m_attributesetinstance_id, isdescription, quantityorder, m_product_uom_id,
+                  m_offer_id, pricestd, C_ORDER_DISCOUNT_ID,
+                  gross_unit_price, taxbaseamt,line_gross_amount
+                )
+              VALUES
+                (
+                  v_OrderLine, v_Client_ID, v_Org_ID, 'Y', now(), v_UpdatedBy,
+                  now(), v_UpdatedBy, v_Record_ID, v_OrderLineSeqNo, NULL, NULL,
+                  now(), now(), now(), now(), Cur_COrderDiscount.NAME,
+                  Cur_COrderDiscount.M_PRODUCT_ID, v_M_Warehouse_ID, 'N', Cur_COrderDiscount.C_UOM_ID, 1,
+                  0, 0, 0, NULL, v_c_currency_id,
+                  ROUND(v_Discount,v_precision), ROUND(v_Discount,v_precision), ROUND(v_Discount,v_precision), ROUND(v_Discount,v_precision), 0, 0,
+                  NULL, NULL, Cur_TaxDiscount.C_TAX_ID, NULL, NULL,
+                  NULL, 'N', NULL, NULL,
+                  NULL, ROUND(v_Discount,v_precision), NULL,
+                  ROUND(v_gross_unit_price, v_precision), ROUND(v_Discount,v_precision),ROUND(v_line_gross_amount, v_precision)
+                );
+
+              UPDATE C_ORDERLINE
+              SET C_ORDER_DISCOUNT_ID=Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
+              WHERE C_ORDERLINE_ID=v_OrderLine;
+            ELSE
+              UPDATE C_ORDERLINE 
+              SET pricelist = ROUND(v_Discount,v_precision), priceactual = ROUND(v_Discount,v_precision), pricelimit = ROUND(v_Discount,v_precision), linenetamt = ROUND(v_Discount,v_precision), pricestd = ROUND(v_Discount,v_precision)
+              WHERE C_ORDERLINE.C_ORDER_DISCOUNT_ID = Cur_COrderDiscount.C_ORDER_DISCOUNT_ID
+                AND C_ORDERLINE.C_TAX_ID = Cur_TaxDiscount.C_TAX_ID;
+            END IF;
+          END LOOP;
+          v_Line:=v_Line + 10;
         END LOOP;
-        v_Line:=v_Line + 10;
-      END LOOP;
+      END IF;
 
       /**
       * Convert to Target DocType