Alert

In IDempiere, Alerts can be set to send information to interested users at specific times or when specific conditions in the database are met. Think of them as automated reports or warnings. Daily activity reports, forecasts, lease expiry warnings, data storage limit warnings - basically anything that you can define with a SQL query, can be turned into an Alert. The information sent and the triggers that cause it to be sent can all be configured in the application without any sourcecode changes.

This article describes the steps necessary to create alerts that serve specific needs.

Prerequisites

Before you begin, ensure you have the e-mail working within ADempiere. Navigate to the Menu » System Admin » General Rules » Client Rules » Client Window to setup and test the e-mail system.

Each user who is to receive the alert information must have a valid e-mail address in their user information.

You should review the existing Alert Processors or create a new one that suits your needs. An Alert Processor determines how often the alert query is assessed. Alert Processors can be found on the Menu » System Admin » General Rules » Server » Alert Processor Window.

You will also need an SQL query that presents the information you want in the Alert.

Steps

1. Log in with a role that has admin access
2. Navigate to the Menu » Performance Analysis » Performance Measurement » Alert Window
3. Create a new record and give it a reasonable name
4. Select an existing alert processor or create a new one
5. Enter the subject and alert message. These will be used as the subject and message in the e-mails that are sent
6. Mark the record as Valid
7. Save the record
8. Navigate to the Template:TabRef
9. Create a new record and give it a reasonable name
10. In the sqlSelect field enter the list of fields from your SQL statement
11. In the sqlFrom field enter the "from" clause of your SQL statement
12. In the sqlWhere field enter the "where" clause from your SQL statement
13. Mark the record as Valid
14. Save the record
15. Navigate to the Template:TabRef
16. Create a new record
17. Select a User/Contact
18. Save record
19. Repeat the three last steps for each user who is to receive the alert.

Testing the new Alert

Start the Application Server if it is not yet started.

In a browser, go to the ADempiere Application Home Page (http://<your server>/admin). Click on ADempiere Server Management and login as the System user.

In the Server Monitor page, navigate to the Alert processor that you assigned to your Alert. Click on run now.

Navigate back to the Alert processor in the Monitor page and check the log.

If the alert failed, you would have to navigate back to the Alert window in ADempiere. Once the necessary corrections have been made, you have to mark the alert and alert rule as Valid again.

Note: You have to restart the Application Server before the changes you make to the Alert will be recognized by the Alert Processor.

Example 1 A list of currently active orders

Send an alert containing a list of all active orders in the system.

Following the steps above, set up the Alert Rule fields as follows:

sqlSelect = "c_order_id","documentno","docstatus","totallines","grandtotal"

sqlFrom = C_ORDER

sqlWhere = ISACTIVE='Y' AND ISSOTRX='Y'

Example 2 Completed orders due for delivery

Send an alert listing completed orders that are due for delivery within the next 7 days.

Complete the steps above for Example 1 but change the sqlWhere clause on the Alert Rule Tab as follows:

sqlWhere = ISACTIVE='Y' AND ISSOTRX='Y' AND DOCSTATUS='CO' AND ISDELIVERED='N' AND DATEPROMISED between sysdate and (sysdate +7)

Example 3 Orders for specific sales reps

Send an alert with an attachment of all order line items entered today with the fields listed below to the sales representative whose name is on the order. (Requires that the sales representative has "email" as notice type in the user window; also check your client_ID - the example uses 1000000)

- Customer Name; Contact Name; Order Number; Order line number; Date Promised; Product Name; Order Line Description; Qty; Unit of Measure; Price, Line amount; Currency; Order-Entry-User 

• sql Select:

   bp.name , au1.name , o.documentno, ol.line, o.datepromised, mp.name , ol.description, ol.qtyordered, uom.name, ol.priceactual,ol.linenetamt, cur.iso_code, au2.name, au3.name

• sql from:

 adempiere.c_orderline ol
 join adempiere.c_bpartner bp on ol.c_bpartner_id=bp.c_bpartner_id 
 join adempiere.c_order o on ol.c_order_id=o.c_order_id
 join adempiere.ad_user au1 on o.ad_user_id=au1.ad_user_id
 left join adempiere.m_product mp on ol.m_product_id=mp.m_product_id
 join adempiere.c_uom uom on ol.c_uom_id=uom.c_uom_id
 join adempiere.c_currency cur on o.c_currency_id=cur.c_currency_id
 join adempiere.ad_user au2 on o.createdby=au2.ad_user_id
 left join adempiere.ad_user au3 on o.salesrep_id=au3.ad_user_id

• sql where:

 ol.ad_client_id='1000000' 
 and extract('day'  from  ol.created)=extract('day' from timestamp 'now()')
 and extract('day'  from  o.created)=extract('day' from timestamp 'now()')
 and au3.name='REPLACE-THIS-WITH-SALESMANS-NAME'
 ;