when trying to create a price list with the TPE currency
window: price list
Name: currency test
and then save changes
in detail create a record
Price List Schema: Purchase 2001
Valid from: 05/09/2020
click in : Create Price List
it throws the following error:
The class M_PriceList_Create was just a direct migration of the oracle PL/SQL function made by in 2005, not converted to java methods, just copied the whole functionality from PL/SQL into java JDBC.
So, I think a correct solution would be to migrate the class to java (there is a version from compiere last open source version, but not sure if is applicable).
In the meantime a solution would be to convert the DECODE to CASE within the M_PriceList_Create class.
I decided to go for the best fix possible for this class, not just fixing a code that is hard to maintain.
I went to full analyze the expected output, describing the process and tests here:
Starting point is the open source version of Compiere org/compiere/process/PriceListCreate.java
This version adds column M_DiscountSchemaLine.Description and rounding options for Hundred and Thousand, so the pull request includes this
Then added to this class the iDempiere changes related to:
use correctly the T_Selection with AD_PInstance_ID
changes related to "Ticket #1002354: Price List Enhancement" - which added Product Costs as an option for getting the initial price
added Group1 and Group2 as selectors for products in schema
added product category tree discovery when using product category in schema
The compiere version had some issues with the currency conversion, when returning null because the rate didn't exist then it returned a negative non-converted value - I fixed this to match the current version (which I think is expected), if no conversion rate, then zero is returned
Tested postgresql-native and oracle
Tested schema with group1, group2, plain category and a tree category containg two children
Tested creating price lists based on another price list
Tested creating price lists based on Product Costs
Tested creating price lists based on Purchase PO
In some of the tests I compared the output from the process running on 7.1 and this code. Noticed the output was different, after a detailed analysis of the expected data, I noticed the 7.1 version was wrong and the new class is generating the correct prices
So, this pull request is not just fixing an old class written as a translation from PL/SQL, converting it to more proper java, but also adding a couple of new features and fixing some bugs