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.
About Chuck Boecking: I am an ERP educator. I believe that open source ERP have achieved mainstream capabilities, and as a result, more companies can create greater efficiency across their organization. I started using the iDempiere code base in 2003. Back then, it was called Compiere. In 2006, I started my first multi-million dollar installation. Since then, ADempiere has helped me create great success with distribution and manufacturing companies all over the world. My vision of success is to find companies that can best use open source ERP to help them achieve a single, global instance that drives a discontinuous increase in profitability. I believe that organizations win when they own their technology.
If you have questions, comments or concerns, let me know. I definitely want your feedback.
You can contact me by phone using 512.850.6068.
My email is firstname.lastname@example.org.
You can complete the form on this page.
Thank you for taking the time. I look forward to speaking with you.