Error creating a Price List - using -DPostgreSQLNative=Y


when trying to create a price list with the TPE currency

window: price list
client: GardenWorl
Organization: *
Name: currency test
Currency: TPE
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:




Carlos Ruiz
May 9, 2020, 1:37 PM

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.


Carlos Ruiz

Carlos Ruiz
June 23, 2020, 9:46 PM

Pull request:

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/

  • 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

Successful Tests:

  • 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



Carlos Ruiz


Layda Salas



Tested By



Fix versions

Due date