Fixed issue 38879:The process "Create price list" increases the product price
authorArmaignac <collazoandy4@gmail.com>
Mon, 25 Jun 2018 15:28:36 -0400
changeset 34270 fceb99795039
parent 34269 72eebae99400
child 34271 d0cc6cddbf69
Fixed issue 38879:The process "Create price list" increases the product price

The process Create Price List was increasing the product price when the price list
schema have two or more lines at product level.

Now the price list schema lines defined at product level is take into account in
the Create Price List process.

A new JUnit test was added to properly check this behavior.

A new preference was created to calculate the product price in a hierarchical way
src-db/database/model/functions/M_PRICELIST_CREATE.xml
src-db/database/sourcedata/AD_REF_LIST.xml
src-test/src/org/openbravo/test/pricelist/PriceListTest.java
src-test/src/org/openbravo/test/pricelist/data/PriceListTestConstants.java
src-test/src/org/openbravo/test/pricelist/data/PriceListTestData.java
src-test/src/org/openbravo/test/pricelist/data/PriceListTestData17.java
--- a/src-db/database/model/functions/M_PRICELIST_CREATE.xml	Mon Jun 25 18:16:41 2018 +0200
+++ b/src-db/database/model/functions/M_PRICELIST_CREATE.xml	Mon Jun 25 15:28:36 2018 -0400
@@ -19,7 +19,7 @@
   * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
   * All Rights Reserved.
   * Contributor(s): Openbravo SLU
-  * Contributions are Copyright (C) 2001-2017 Openbravo, S.L.U.
+  * Contributions are Copyright (C) 2001-2018 Openbravo, S.L.U.
   *
   * Specifically, this derivative work is based upon the following Compiere
   * file and version.
@@ -53,6 +53,8 @@
     v_validfromdate M_PriceList_Version.ValidFrom%TYPE;
     v_isCostMigrated NUMBER;
     v_clientCurrencyId AD_Client.C_Currency_ID%TYPE;
+    v_count NUMBER;
+    v_priceListPrefValue CHAR(1):='Y';
     --
     -- Get PL Parameter
     Cur_DiscountLine RECORD;
@@ -68,7 +70,6 @@
     v_Sql_insert VARCHAR2(2000);
     v_rdbms VARCHAR2(2000):=AD_GET_RDBMS();
 
-    v_priceRemoved CHAR(1) := 'N';
   BEGIN
     --  Update AD_PInstance
     DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing') ;
@@ -134,8 +135,16 @@
     IF (v_Costbased = 'N' AND (v_PriceList_Version_Base_ID IS NULL OR v_PriceList_Version_Base_ID='')) THEN
       RAISE_APPLICATION_ERROR(-20000, '@BasePriceListRequired@');
     END IF;
+    -- Calculate Hierarchical Product Price Preference
+    SELECT COUNT(1) INTO v_count
+    FROM ad_preference
+    WHERE property = 'HierarchicalPriceList';
+    IF (v_count > 0) THEN
+      v_priceListPrefValue := AD_GET_PREFERENCE_VALUE('HierarchicalPriceList', 'Y', v_client_id, v_org_id, v_User, NULL, NULL);    
+    END IF;
     --Create temporary tables 
     v_temp:=C_CREATE_TEMPORARY_TABLES();
+    DELETE FROM C_TEMP_Selection2;
     DBMS_OUTPUT.PUT_LINE('  PriceList_Version_ID=' || v_PriceList_Version_ID) ;
     -- Checking Prerequisites
     -- -- PO Prices must exists
@@ -158,7 +167,6 @@
         rowcount:=SQL%ROWCOUNT;
         v_Message:='@Deleted@=' || rowcount || ' - ';
         DBMS_OUTPUT.PUT_LINE(v_Message) ;
-        v_priceRemoved := 'Y';
       END IF;
       /**
       * For All Discount Lines in Sequence
@@ -264,7 +272,7 @@
         END IF;
 
         -- Product Price will be created with initial values only the first time and will be used as basis for next iterations
-        IF (v_priceRemoved = 'N' AND (v_PriceList_Version_Base_ID IS NULL OR v_PriceList_Version_Base_ID <> v_PriceList_Version_ID)) THEN
+        IF (v_PriceList_Version_Base_ID IS NULL OR v_PriceList_Version_Base_ID <> v_PriceList_Version_ID) THEN
           v_ResultStr:=v_ResultStr || ', Delete';
           DELETE
           FROM M_ProductPrice
@@ -274,8 +282,24 @@
             FROM C_TEMP_Selection s
             WHERE M_ProductPrice.M_Product_ID=s.C_TEMP_Selection_ID
             )
-            ;
-          v_priceRemoved := 'Y';
+            AND NOT EXISTS
+            (SELECT 1
+            FROM C_TEMP_Selection2 sel2
+            WHERE M_ProductPrice.M_Product_ID=sel2.C_TEMP_Selection_ID
+            );
+          IF(v_priceListPrefValue = 'Y') THEN
+            INSERT INTO C_TEMP_Selection2 
+            (
+             C_TEMP_Selection_ID, QUERY_ID
+            )
+            SELECT sel.C_TEMP_Selection_ID, ' '
+            FROM C_TEMP_Selection sel
+            WHERE NOT EXISTS
+              (SELECT 1
+               FROM C_TEMP_Selection2 sel2
+               WHERE sel.C_TEMP_Selection_ID=sel2.C_TEMP_Selection_ID
+              );  
+          END IF;
         END IF;
         -- --------------------
         -- Copy (Insert) Prices
--- a/src-db/database/sourcedata/AD_REF_LIST.xml	Mon Jun 25 18:16:41 2018 +0200
+++ b/src-db/database/sourcedata/AD_REF_LIST.xml	Mon Jun 25 15:28:36 2018 -0400
@@ -11162,6 +11162,18 @@
 <!--9A689DF384A545CC8AA70882A486B7AE-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--9A689DF384A545CC8AA70882A486B7AE--></AD_REF_LIST>
 
+<!--9A904D1FC0154EFB820803D7A89D83A9--><AD_REF_LIST>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <AD_REF_LIST_ID><![CDATA[9A904D1FC0154EFB820803D7A89D83A9]]></AD_REF_LIST_ID>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <VALUE><![CDATA[HierarchicalPriceList]]></VALUE>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <NAME><![CDATA[Enable Hierarchical Price List]]></NAME>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <DESCRIPTION><![CDATA[Enable hierarchical product price calcutation in price list]]></DESCRIPTION>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <AD_REFERENCE_ID><![CDATA[A26BA480E2014707B47257024C3CBFF7]]></AD_REFERENCE_ID>
+<!--9A904D1FC0154EFB820803D7A89D83A9-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--9A904D1FC0154EFB820803D7A89D83A9--></AD_REF_LIST>
+
 <!--9B378327FCD1463D89E94D7475788537--><AD_REF_LIST>
 <!--9B378327FCD1463D89E94D7475788537-->  <AD_REF_LIST_ID><![CDATA[9B378327FCD1463D89E94D7475788537]]></AD_REF_LIST_ID>
 <!--9B378327FCD1463D89E94D7475788537-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src-test/src/org/openbravo/test/pricelist/PriceListTest.java	Mon Jun 25 18:16:41 2018 +0200
+++ b/src-test/src/org/openbravo/test/pricelist/PriceListTest.java	Mon Jun 25 15:28:36 2018 -0400
@@ -11,7 +11,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU 
- * All portions are Copyright (C) 2017 Openbravo SLU 
+ * All portions are Copyright (C) 2017-2018 Openbravo SLU 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -63,6 +63,7 @@
 import org.openbravo.test.pricelist.data.PriceListTestData14;
 import org.openbravo.test.pricelist.data.PriceListTestData15;
 import org.openbravo.test.pricelist.data.PriceListTestData16;
+import org.openbravo.test.pricelist.data.PriceListTestData17;
 import org.openbravo.test.pricelist.data.PriceListTestData2;
 import org.openbravo.test.pricelist.data.PriceListTestData3;
 import org.openbravo.test.pricelist.data.PriceListTestData4;
@@ -108,6 +109,7 @@
   private boolean isSalesPrice;
   private boolean isBasedOnCost;
   private boolean isPriceIncludesTax;
+  private boolean isDefault;
 
   // Price List Version
   private String basePriceListVersionId;
@@ -120,6 +122,7 @@
     this.isSalesPrice = data.isSalesPrice();
     this.isBasedOnCost = data.isBasedOnCost();
     this.isPriceIncludesTax = data.isPriceIncludesTax();
+    this.isDefault = data.isDefault();
     this.basePriceListVersionId = data.getBasePriceListVersionId();
     this.expectedProductPricesData = data.getExpectedProductPrices();
   }
@@ -208,7 +211,12 @@
                 "16",
                 "Price List Schema with four different rules applied. Unit Price and List Price discounts applied.",
                 new PriceListTestData16() //
-            } });
+            },
+            {
+                "17",
+                "Data used for Test: Price List Schema with more than one rule applied. One rule with 50% unit price for one product, and second one with one product of of same Category and an unit price of 50%",
+                new PriceListTestData17() //
+            }, });
   }
 
   @Test
@@ -413,6 +421,7 @@
     pl.setSalesPriceList(isSalesPrice);
     pl.setCostBasedPriceList(isBasedOnCost);
     pl.setPriceIncludesTax(isPriceIncludesTax);
+    pl.setDefault(isDefault);
     OBDal.getInstance().save(pl);
     return pl;
   }
--- a/src-test/src/org/openbravo/test/pricelist/data/PriceListTestConstants.java	Mon Jun 25 18:16:41 2018 +0200
+++ b/src-test/src/org/openbravo/test/pricelist/data/PriceListTestConstants.java	Mon Jun 25 15:28:36 2018 -0400
@@ -11,7 +11,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU 
- * All portions are Copyright (C) 2017 Openbravo SLU 
+ * All portions are Copyright (C) 2017-2018 Openbravo SLU 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -53,6 +53,8 @@
   public static final String CUSTOMER_B_PRICE_LIST_VERSION_ID = "4028E6C72959682B01295B03CFDE0249";
   // Final good A Product
   public static final String FINAL_GOOD_A_PRODUCT_ID = "4028E6C72959682B01295ADC1D07022A";
+  // Final good B Product
+  public static final String FINAL_GOOD_B_PRODUCT_ID = "4028E6C72959682B01295ADC1DC2022E";
   public static final String FINAL_GOOD_A_PRODUCT_NAME = "Final good A";
   // Final good B Product Name
   public static final String FINAL_GOOD_B_PRODUCT_NAME = "Final good B";
--- a/src-test/src/org/openbravo/test/pricelist/data/PriceListTestData.java	Mon Jun 25 18:16:41 2018 +0200
+++ b/src-test/src/org/openbravo/test/pricelist/data/PriceListTestData.java	Mon Jun 25 15:28:36 2018 -0400
@@ -11,7 +11,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU 
- * All portions are Copyright (C) 2017 Openbravo SLU 
+ * All portions are Copyright (C) 2017-2018 Openbravo SLU 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -41,6 +41,7 @@
   private boolean isSalesPrice;
   private boolean isBasedOnCost;
   private boolean isPriceIncludesTax;
+  private boolean isDefault;
 
   // Price List Version
   private String basePriceListVersionId;
@@ -115,6 +116,14 @@
     this.basePriceListVersionId = basePriceListVersionId;
   }
 
+  public boolean isDefault() {
+    return isDefault;
+  }
+
+  public void setDefault(boolean isDefault) {
+    this.isDefault = isDefault;
+  }
+
   /**
    * Returns Map should be used to verify Product Prices values after test is executed. Map has the
    * following structure: &lt;Product name, [Unit Price Expected, List Price Expected]&gt;
@@ -126,5 +135,4 @@
   public void setExpectedProductPrices(HashMap<String, String[]> expectedProductPrices) {
     this.expectedProductPrices = expectedProductPrices;
   }
-
 }
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-test/src/org/openbravo/test/pricelist/data/PriceListTestData17.java	Mon Jun 25 15:28:36 2018 -0400
@@ -0,0 +1,85 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html 
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License. 
+ * The Original Code is Openbravo ERP. 
+ * The Initial Developer of the Original Code is Openbravo SLU 
+ * All portions are Copyright (C) 2018 Openbravo SLU 
+ * All Rights Reserved. 
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+ */
+
+package org.openbravo.test.pricelist.data;
+
+import java.math.BigDecimal;
+import java.util.HashMap;
+
+/**
+ * Data used for Test: Price List Schema with more than one rule applied. One rule with 50% unit
+ * price for one product, and second one with one product of of same Category and an unit price of
+ * 50%
+ * 
+ * @author Andy Armaignac
+ *
+ */
+public class PriceListTestData17 extends PriceListTestData {
+
+  @Override
+  public void initialize() {
+
+    // Define rule to be applied for a product
+    PriceListSchemaLineTestData ruleLine1 = new PriceListSchemaLineTestData();
+    ruleLine1.setProductId(PriceListTestConstants.FINAL_GOOD_A_PRODUCT_ID);
+    ruleLine1.setBaseListPriceValue(PriceListTestConstants.REFLIST_VALUE_NET_LIST_PRICE);
+    ruleLine1.setSurchargeListPriceAmount(BigDecimal.ZERO);
+    ruleLine1.setListPriceDiscount(BigDecimal.ZERO);
+    ruleLine1.setBaseStandardPriceValue(PriceListTestConstants.REFLIST_VALUE_NET_UNIT_PRICE);
+    ruleLine1.setSurchargeStandardPriceAmount(BigDecimal.ZERO);
+    ruleLine1.setStandardPriceDiscount(BigDecimal.valueOf(50L));
+
+    // Define a rule to be applied for the second product
+    PriceListSchemaLineTestData ruleLine2 = new PriceListSchemaLineTestData();
+    ruleLine2.setProductId(PriceListTestConstants.FINAL_GOOD_B_PRODUCT_ID);
+    ruleLine2.setBaseListPriceValue(PriceListTestConstants.REFLIST_VALUE_NET_LIST_PRICE);
+    ruleLine2.setSurchargeListPriceAmount(BigDecimal.ZERO);
+    ruleLine2.setListPriceDiscount(BigDecimal.ZERO);
+    ruleLine2.setBaseStandardPriceValue(PriceListTestConstants.REFLIST_VALUE_NET_UNIT_PRICE);
+    ruleLine2.setSurchargeStandardPriceAmount(BigDecimal.ZERO);
+    ruleLine2.setStandardPriceDiscount(BigDecimal.valueOf(50L));
+
+    // Add lines
+    setTestPriceListRulesData(new PriceListSchemaLineTestData[] { ruleLine1, ruleLine2 });
+
+    /**
+     * This Map will be used to verify Product Prices values after test is executed. Map has the
+     * following structure: <Product name, [Unit Price Expected, List Price Expected]>
+     */
+    HashMap<String, String[]> productPriceLines = new HashMap<String, String[]>();
+    productPriceLines.put(PriceListTestConstants.FINAL_GOOD_A_PRODUCT_NAME, new String[] { "1.00",
+        "2.00" });
+    productPriceLines.put(PriceListTestConstants.FINAL_GOOD_B_PRODUCT_NAME, new String[] { "1.00",
+        "2.00" });
+    setExpectedProductPrices(productPriceLines);
+
+    // Price List Header
+    setOrganizationId(PriceListTestConstants.SPAIN_ORGANIZATION_ID);
+    setPriceListName(PriceListTestConstants.PRICE_LIST_NAME);
+    setCurrencyId(PriceListTestConstants.EUR_CURRENCY_ID);
+    setSalesPrice(true);
+    setBasedOnCost(false);
+    setPriceIncludesTax(false);
+    setDefault(true);
+
+    // Price List Version
+    setBasePriceListVersionId(PriceListTestConstants.CUSTOMER_A_PRICE_LIST_VERSION_ID);
+
+  }
+}