Creating an Alert

This Wiki is read-only for reference purposes to avoid broken links.

Creating Alert

http://sourceforge.net/forum/message.php?msg_id=3938031 By: avdalen

I struggled with creating an Alert but now that I have it working, I would like to give a couple of pointers for anyone else who is trying it.

Pre-Requisites

• Emailing must be working. Navigate to General Rules→Client Rules→Client to review, setup and test.

Example 1

1. Log in as an admin (or role with appropriate access)
2. Navigate to Performance Analysis→Performance Measurement→Alert
3. Create a new record, name it Orders (or anything you prefer)
4. Select an existing alert processor or create a new one. This alert processor is the component that will send emails the actual emails.
5. Enter subject and alert message
6. Mark as Valid
7. Save record

Alert Rule Tab

1. Create a new record, name it Orders Alert (or anything you prefer)
2. In the sqlSelect field enter
   • "C_ORDER_ID","DOCUMENTNO","DOCSTATUS","TOTALLINES","GRANDTOTAL"
3. In the sqlFrom field enter
   • C_ORDER
4. In the sqlWhere field enter
   • ISACTIVE='Y' and ISSOTRX='Y'
5. Mark as Valid
6. Save record

Alert Recipient Tab

1. Create a new record
2. Select a User/Contact
3. Save record

Testing the new Alert

Start the Application Server if not yet started.

Open compiereMonitor in a browser http://<your server>/compiereMonitor Navigate to the Alert processor that you assigned to your Alert. Click on run now

Navigate back to the Alert processor in the monitor and check the log.

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

NOTE: Remember to restart the Application Server before you attempt to rerun the processor.

---

Example 2

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

Create the Alert as per Example 1 above but this time assign the sqlWhere clause on the Alert Tab as:

• sqlWhere clause

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

---

Example 3

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 - ours was 1000000; we are using PostGre)

- 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'
 ;