How to create an Alert

Contents 1 Objective 2 Defining the alert 3 Defining recipients 4 Scheduling the alerting background process 5 The Result 6 SQL code explained

Objective

The objective of this how-to is to illustrate how you can add new alerts to Openbravo. Alerts are non-intrusive informational messages to individual users or groups (roles) about anything within the system. One can define unlimited number of alerts for various statuses, errors, informative purposes, reminder etc. Some examples are:

• Errors in the application dictionary that the System Administrator needs to be alerted about (e.g. a table without a an identifier)‏
• Errors in master data (e.g. a Business partner without an address)‏
• Notifications about critical situations (e.g. overdue payments)‏

and many more.

Defining the alert

First of all, the condition under which the alert appears needs to be defined. This is done using a SQL statement that needs to follow certain conventions.

To enter a new alert, use the role System Administrator to navigate to General Setup || Application || Alert window. Depending on how you installed Openbravo, you might already have some alerts here. If you do, try to find the Customers with exceeded credit one and double click it. If not, create a new record as indicated below:

where this is the exact code for the SQL field:

 
 SELECT c_bpartner_id AS referencekey_id,
     ad_column_identifier('C_BPartner', c_bpartner_id, 'en_US') AS record_id,
     0 AS ad_role_id,
     NULL AS ad_user_id,
     ad_column_identifier('C_BPartner', c_bpartner_id, 'en_US') ||' has '||SO_CreditLimit||' as limit and has reached '||SO_CreditUsed AS description,
     'Y' AS isActive,
      ad_org_id, 
      ad_client_id, 
      now() AS created,  
      0 AS createdBy,  
      now() AS updated,
      0 AS updatedBy
 FROM c_bpartner 
 WHERE SO_CreditLimit < SO_CreditUsed
 AND iscustomer='Y'
 AND SO_CreditLimit!=0

The fields in question here are:

• Name is a user friendly name for an alert
• SQL is the actual SQL statements whose results (individual records) will be items alerted about
• Tab is a tab of a specific window where the user should be taken to be shown the item in question generated by the SQL statement

We will come back to this code and explain it in detail. First, let's try to see the resulting alert.

Defining recipients

Switch to the role Openbravo Admin (or your defined 'administrator' role), navigate to General Setup || Application || Alert, select the Customers with exceeded credit alert and switch to the Alert Recipients tab. Add a new record as indicated below:

Notice that a role can be added (that includes several users) or a specific user.

Scheduling the alerting background process

For the alerts to be evaluated and triggered, the background process needs to be scheduled since it doesn't come that way out of the box. Using the role Openbravo Admin (or your defined 'administrator' role) navigate to General Setup || Process Scheduling || Process Request and enter a new record:

The Result

Finally, log out and back in and select the role Openbravo Admin (or your defined 'administrator' role). You should be able to see an alert in the navigation bar. By clicking on it, you will automatically be taken to the Alert Management window that should look something like:

SQL code explained

In order to write any kind of alert, the approach is very similar. Let's take another look at the SQL code that describes the condition:

 
 SELECT c_bpartner_id AS referencekey_id,
     ad_column_identifier('C_BPartner', c_bpartner_id, 'en_US') AS record_id,
     '0' AS ad_role_id,
     NULL AS ad_user_id,
     ad_column_identifier('C_BPartner', c_bpartner_id, 'en_US') ||' has '||SO_CreditLimit||' as limit and has reached '||SO_CreditUsed AS description,
     'Y' AS isActive,
      ad_org_id, 
      ad_client_id, 
      now() AS created,  
      '0' AS createdBy,  
      now() AS updated,
      '0' AS updatedBy
 FROM c_bpartner 
 WHERE SO_CreditLimit < SO_CreditUsed
 AND iscustomer='Y'
 AND SO_CreditLimit!=0

Each SQL statement basically simulates an Openbravo table which is why it needs to have all the columns defined above:

• referencekey_id - this is ID (primary key) of the record that needs attention and is the cause of the alert. In our case, this would be the C_BPartner_ID of Neil Reiley.
• record_id - this is a user friendly label for the item that is in questions with the referencekey_id. Use the ad_column_identifier function as shown to retrieve all column values required to uniquely identify the problematic record within the specific table.
• ad_role_id - should always be set to 0
• ad_user_id - should always be set to null
• description - this is the message shown to the user describing what the problem is. You need construct this message according to what the alert is all about, indicating to the user what caused the alert and what the problem is. Again, use the ad_column_identifier function as shown to retrieve all column values required to uniquely identify the problematic record within the specific table.
• isActive - should always be set to 'Y'
• ad_org_id - should be inherited from the actual record that triggers the alert, in this case, the record from the C_BPartner table
• ad_client_id - should be inherited from the actual record that triggers the alert, in this case, the record from the C_BPartner table
• created - should always be set to now()
• createdBy - should always be set to 0
• updated - should always be set to now()
• updatedBy - should always be set to 0

Finally the WHERE clause describes the condition that triggers the alert. In our case the SELECT statement will return all Business Partners (FROM c_bpartner) that are customers (iscustomer='Y') and have exceeded their credit limit (SO_CreditLimit < SO_CreditUsed) which needs to be different from zero (SO_CreditLimit!=0).

In other words, anything you can describe in a SQL statement can be defined as an alert. It's as simple as that.

Category: HowTo