details: https://code.openbravo.com/erp/devel/pi/rev/9ba0cfebdf14
changeset: 32680:9ba0cfebdf14
user: Mark <markmm82 <at> gmail.com>
date: Thu Sep 07 15:23:54 2017 -0400
summary: Fixes issue 36707: Price List Schema is not working in a
"hierarchical" way
Price List Schema with several lines was not working in a "hierarchical" way,
because every time it was processing a new M_DiscountSchemaLine it was deleting
the previously calculated product prices, and it always save the last
calculation
taking the last line of the price list version as basis.
To fix that, only are removed previously calculated product prices that will be
regenerated at start of the process for the Price List Version being processed.
And these product prices only are removed and inserted once, the first time the
discount will be applied. For next discounts, product prices will be updated
using
previously calculated prices.
This way, apply several lines of the Price List Schema will be executed
sequencially:
first is applied the discounts for the first line and all prices calculated will
be the basis for the next one to apply, this process until all lines be applied.
diffstat:
src-db/database/model/functions/M_PRICELIST_CREATE.xml | 29 +++++++++++++----
1 files changed, 21 insertions(+), 8 deletions(-)
diffs (81 lines):
diff -r 941a129396ad -r 9ba0cfebdf14
src-db/database/model/functions/M_PRICELIST_CREATE.xml
--- a/src-db/database/model/functions/M_PRICELIST_CREATE.xml Wed Sep 13
10:38:03 2017 +0200
+++ b/src-db/database/model/functions/M_PRICELIST_CREATE.xml Thu Sep 07
15:23:54 2017 -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-2015 Openbravo, S.L.U.
+ * Contributions are Copyright (C) 2001-2017 Openbravo, S.L.U.
*
* Specifically, this derivative work is based upon the following Compiere
* file and version.
@@ -67,7 +67,8 @@
v_Sql_analyze_pg VARCHAR2(2000):='ANALYZE C_TEMP_Selection';
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') ;
@@ -157,6 +158,7 @@
rowcount:=SQL%ROWCOUNT;
v_Message:='@Deleted@=' || rowcount || ' - ';
DBMS_OUTPUT.PUT_LINE(v_Message) ;
+ v_priceRemoved := 'Y';
END IF;
/**
* For All Discount Lines in Sequence
@@ -261,8 +263,8 @@
EXECUTE IMMEDIATE v_Sql_analyze_pg;
END IF;
- -- Delete Prices in Selection, so that we can insert
- IF(v_PriceList_Version_Base_ID IS NULL OR
v_PriceList_Version_Base_ID<>v_PriceList_Version_ID) THEN
+ -- 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
v_ResultStr:=v_ResultStr || ', Delete';
DELETE
FROM M_ProductPrice
@@ -273,8 +275,7 @@
WHERE M_ProductPrice.M_Product_ID=s.C_TEMP_Selection_ID
)
;
- rowcount:=SQL%ROWCOUNT;
- v_Message:=', @Deleted@=' || rowcount;
+ v_priceRemoved := 'Y';
END IF;
-- --------------------
-- Copy (Insert) Prices
@@ -304,7 +305,13 @@
COALESCE(M_GET_COST (mp.M_Product_ID, add_hms(v_validfromdate, 23,
59, 59), CASE v_isCostMigrated WHEN 0 THEN mp.costtype ELSE null END, v_Org_ID,
mp.ad_client_id, null, v_clientCurrencyId, v_Currency_ID),0)
from m_product mp
where EXISTS (SELECT 1 FROM C_TEMP_Selection s WHERE
mp.M_Product_ID=s.C_TEMP_Selection_ID)
- AND mp.IsActive='Y';
+ AND mp.IsActive='Y'
+ AND NOT EXISTS (
+ SELECT 1
+ FROM M_ProductPrice pp
+ WHERE pp.M_Product_ID = mp.M_Product_ID
+ AND pp.M_PriceList_Version_ID = v_PriceList_Version_ID
+ );
ELSE
-- Copy and Convert from other PriceList_Version
@@ -336,7 +343,13 @@
WHERE pp.M_PriceList_Version_ID=v_PriceList_Version_Base_ID
AND EXISTS
(SELECT 1 FROM C_TEMP_Selection s WHERE
pp.M_Product_ID=s.C_TEMP_Selection_ID)
- AND pp.IsActive='Y';
+ AND pp.IsActive='Y'
+ AND NOT EXISTS (
+ SELECT 1
+ FROM M_ProductPrice pp2
+ WHERE pp2.M_Product_ID = pp.M_Product_ID
+ AND pp2.M_PriceList_Version_ID = v_PriceList_Version_ID
+ );
END IF;
rowcount:=SQL%ROWCOUNT;
v_Message:=v_Message || ', @Inserted@=' || rowcount;
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
