Automating Draft Order Document Cleanup in iDempiere using the Scheduler: A Java Process Tutorial

iDempiere is a powerful open-source ERP and CRM software that allows businesses to manage their operations more efficiently. However, over time, draft order documents can accumulate and slow down system performance. In this tutorial, we will show you how to automate the cleanup of old draft order documents in iDempiere using the built-in Scheduler feature and a custom Java process.

Prerequisites

Before we begin, make sure that you have the following:

• A working installation of iDempiere
• Basic knowledge of Java programming
• Access to the iDempiere source code

Creating the Java Process

The first step is to create a custom Java process that will delete old draft order documents. Here’s how to do it:

1. Create a new Java class in your iDempiere project. You can name it something like “CleanDraftOrderDocumentsProcess”.
2. In your class, implement the org.compiere.process.SvrProcess interface. This interface requires you to implement the execute method, which is the main entry point for your process.

package com.yourcompany;

import org.compiere.process.SvrProcess;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.util.DB;

public class CleanDraftOrderDocumentsProcess implements SvrProcess {

    private int daysOld;

    @Override
    protected void prepare() {
        ProcessInfoParameter[] para = getParameter();
        for (int i = 0; i < para.length; i++) {
            String name = para[i].getParameterName();
            if (para[i].getParameter() == null)
                ;
            else if (name.equals("DaysOld"))
                daysOld = para[i].getParameterAsInt();
        }
    }   

    @Override
    protected String doIt() throws Exception {
        String sql = "DELETE FROM C_OrderLine WHERE C_Order_ID IN (SELECT C_Order_ID FROM C_Order WHERE IsSOTrx='Y' AND DocStatus='DR' AND Created < (CURRENT_DATE - ?))";
        int orderLineRowsDeleted = DB.executeUpdate(sql, new Object[] { daysOld }, false, null);

        sql = "DELETE FROM C_Order WHERE IsSOTrx='Y' AND DocStatus='DR' AND Created < (CURRENT_DATE - ?)";
        int orderRowsDeleted = DB.executeUpdate(sql, new Object[] { daysOld }, false, null);

        return orderLineRowsDeleted + " order line(s) and " + orderRowsDeleted + " order(s) have been deleted.";
    }
}

3. In the setParameter method, retrieve the value of the “DaysOld” parameter and store it in a variable.
4. In the doIt method, use a SQL query to delete all draft order documents that are older than the specified number of days. The DB.executeUpdate method is used to execute the SQL query.
5. Save your Java class and compile it.

Creating the Process in iDempiere

Next, you need to create a new process in iDempiere that will run your custom Java process. Here’s how to do it:

1. In the iDempiere web interface, navigate to the “Processes” window.
2. Click on the “New” button to create a new process.
3. Give your process a name, such as “Clean Draft Order Documents”.
4. In the “Classname” field, enter the fully qualified name of your Java class (e.g. “com.yourcompany.CleanDraftOrderDocumentsProcess”).
5. Click on the “Save” button to save your process.

Configuring the Process Parameters

Now that your process has been created, you need to configure its parameters. Here’s how to do it:

1. In the “Processes” window, locate the process you just created and click on its name to open it.
2. Click on the “Parameters” tab.
3. Click on the “New” button to create a new parameter.
4. Give your parameter a name, such as “DaysOld”.
5. Set the parameter type to “Integer”.

Configuring the Scheduler

Now that you have created the Java process and configured its parameters, it’s time to schedule it to run automatically using the Scheduler. Here’s how to do it:

1. In the iDempiere web interface, navigate to the “Scheduler” window.
2. Click on the “New” button to create a new schedule.
3. Name your schedule, such as “Clean Draft Order Documents”.
4. In the “Process” field, select the process you created earlier.
5. In the “Schedule Type” field, select how often you want the process to run. For example, you can choose “Daily” to run the process once a day.
6. In the “Parameter” field, enter the value for the “DaysOld” parameter that you want to use. For example, if you want to delete draft order documents that are older than 30 days, you would enter “30” in this field.
7. Click on the “Save” button to save your schedule.

Testing the Process

At this point, you have successfully created a Java process to clean up old draft order documents and configured the Scheduler to run it automatically every day. To test your process, simply wait for the scheduled time to arrive and check if the old draft order documents have been deleted.

Conclusion

In this tutorial, we have shown you how to automate the cleanup of old draft order documents in iDempiere using a custom Java process and the built-in Scheduler feature. By scheduling the process to run automatically, you can save time and improve the performance of your iDempiere installation.