PostgreSQL Triggers – Enhancing/Customizing Open Source ERP

Hi Everyone,

The purpose of this post is to offer a simple way to enhance ADempiere and iDempiere, open source ERP, using database triggers.

Business Scenario: You are a distributor of products. You use standard costing to record the cost of your products. External influences like duty, freight, product cost, and brokerage all contribute to the fully burdened standard cost of your product. Let’s say your purchasing team is incentivized to predict real cost components and to buy from the best possible vendor for the best possible price.

As an ERP administrator, you easily account for freight, product, and brokerage costs through the vendor invoices; however, duty can be somewhat harder to measure. You decide that you want to add a duty percent field to the product’s purchasing tab, and you want this value to propagate to the Material Receipt Line every time you receive this product. Doing so will allow you to create a report to show each product’s duty contribution to the overall standard cost.

Challenge: how do you get the Material Receipt Line to copy the Duty Percent field from the product’s purchasing tab to itself every time a MRLine is created?

There are many ways to add functionality to make your ERP match your specific business needs. In ADempiere and iDempiere, you can:

• At the Table and Column window, you can add Default Logic to the Material Receipt’s Duty Percent field. Default Logic gives you the ability to set a field when a new record is created through the model or via the user interface. The Table and Column window is part of ADempiere’s active directory. The application dictionary gives you  the ability to configure much of iDempiere without modifying code.
• Using the model’s beforeSave() or afterSave() to programmatically set a field based on business logic. This option uses Java.
• Using a database trigger to set a field. This option uses SQL. I will demonstrate this topic today.

In this example, we will use a trigger. There are pluses and minuses to using triggers to enhance your open source ERP:

• Plus: your learning curve is greatly reduced when using triggers. Creating triggers is easy compared to learning all the touch points in code.
• Plus: you do not need to compile and deploy code any time you make an enhancement.
• Negative: ADempiere and iDempiere typically do not use triggers to implement business logic. Business logic is typically held in code. This is partially because the core developers strive to maintain database independence (Oracle, PostgreSQL, MSSql, etc..). It is also partially because you have more options and features when using code to implement business logic.

Let’s assume that you have not yet mastered how to modify and deploy code changes. The pluses outweighs the negatives; therefore, we will implement our new feature using a PostgreSQL trigger. Below is an example trigger that implements the above scenario.

create or replace function m_inoutline_dutypercent_trigfunc() returns trigger as '
declare
c_bpartner_id_v numeric;
begin 
c_bpartner_id_v := (select c_bpartner_id from m_inout where m_inout_id = new.m_inout_id);
NEW.dutypercent := 
 (select max(dutypercent) 
 from m_product_po ppo 
 where ppo.m_product_id = new.m_product_id 
 and ppo.c_bpartner_id = c_bpartner_id_v 
 and ppo.isactive = ''Y'');
return new;
end;
' language 'plpgsql'
;

CREATE TRIGGER m_inoutline_dutypercent_trig
BEFORE INSERT 
ON m_inoutline
FOR EACH ROW 
execute procedure m_inoutline_dutypercent_trigfunc()
;

Every time you save a Material Receipt Line, the trigger will look up the product’s duty percent for the specified business partner (vendor).

One of the reasons that I wrote this blog is that PostgreSQL implements triggers differently than Oracle, MS-SQL or MySQL. I had trouble finding an example; therefore, I created one here.

I hope this helps!!

Why consider Open Source ERP

Open source ERP gives you every opportunity to prove or disprove its ability to support your company’s ERP needs on a timeline that satisfies your organizational needs. With open source ERP, you do not face the same financial constraints nor do you face the same conflicts of interest as with commercial ERP. Instead, you invest in the appropriate skills and knowledge for your people and processes. Best of all – if open source ERP cannot solve your company’s needs, you can safely justify spending the additional $2K to $5K per person per year for life of your commercial ERP to help drive your organization’s success.

ADempiere vs iDempiere vs Openbravo vs Compiere

Please note that ADempiere, iDempiere and Openbravo are forks or copies from Compiere. Therefore, they have similar abilities mentioned above. The biggest difference is that ADempiere and iDempiere are pure open source. There are no features held behind a commercial or paid license.