X

Best Practices from Oracle Development's A‑Team

Creating Event Based E-Mail and Text Notifications for BICS using Apex

Introduction

Event Based notification functionality for Oracle's BI Cloud Service (BICS) will be available later this year, but until that time some of the same functionality can be created through Apex.

This article will walk through a process for creating notifications, both e-mail and SMS text message, based on a data event within BICS.  The article will also demonstrate how these can be scheduled to run automatically.  While written with a BICS audience in mind, it is also relevant to standalone Apex users looking to implement event based notifications.

Some possible use cases for this:

- Notify administrators if row counts in fact or dimension tables change outside of norms, possibly suggesting data load or other data quality issues

- Notify report consumers if business related thresholds are crossed, possibly suggesting opportunities, or areas that need attention

 

Main Article

Alert Condition Statement

In a previous blog, using Apex RESTful services to extract data from BICS, the SQL behind a report in the Sample Apps dashboard was examined.  This will be re-used in this article to demonstrate a notification based alert.

On the Sample Dashboard is a tabular report that shows the Top Product Performers based on revenue, highlighted below:

Dashboard-1024x815

Using the ‘Administration’ option, and then ‘Manage Sessions’, the SQL that the BI Server runs can be located and examined. With a little re-work and some testing within SQL Workshop, the SQL statement can be recreated.

select
extract(year from c.time_bill_dt) as year,
sum(c.revenue) as revenue,
sum (c.cost_fixed + c.cost_variable) as cost,
sum(1) as orders,
p.product,
g.region,
o.channel_name
from cloud_f_cost c
join cloud_d_products p on p.prod_item_key=c.prod_item_key
join cloud_d_geography g on g.addr_key = c.addr_key
join cloud_d_orders o on o.order_key = c.order_key
group by
extract(year from c.time_bill_dt),
p.product,
g.region,
o.channel_name

In this example, an alert will be created for whenever the on-line revenue in the Pocket Fun ES product line for Europe drops below $3,500,000 for the current year (which in the sample app data is 2013).  The 'where' statement for this condition would be.

where product='PocketFun ES' and year=2013 and Region='EMEA' and Channel_name='Online' and revenue<3500000;

This SQL statement will be built into a Stored Procedure.  If the SQL statement is TRUE - so the conditions of the WHERE statement are met - then a row will be returned.  This row will include the E-Mail address details for the recipients of the alert. This row will then be passed into an Apex e-mail function and an e-mail generated.

If the conditions of the SQL statement are not met, then no row will be returned, and nothing will be passed to the Apex e-mail function.

The SQL statement could be as simple as counting the number of rows of data in a table, or replicate a complex set of queries as in this example. The only requirement is that when the condition is true - a row of data be returned.

 

Constructing the Stored Procedure

Please note - cutting and pasting from this HTML article can cause some incorrect characters to be copied.  To remedy this, use this link for a plain text version of the code.  

The structure of the Stored Procedure is broken out below.  The Stored Procedure can be created in the SQL Workshop of Apex.

The first section creates the procedure, named 'EMAIL_POCKETFUN_PERFORMANCE' and defines variables that will be used.

CREATE OR REPLACE PROCEDURE "EMAIL_POCKETFUN_PERFORMANCE"
AS
EMAIL_TO VARCHAR2(255);
EMAIL_FROM VARCHAR2(255);
EMAIL_CC VARCHAR2(255);
EMAIL_BCC VARCHAR2(255);
BEGIN
begin

The next section begins the 'SELECT' statement, that will include the Alert recipients if the condition is met.

Most cell phone companies allow an e-mail to be sent directly as an SMS message, so if these alerts need to be received as a text message, the e-mail address of the phone could be used.  This table shows the 4 main US carriers and the e-mail format.  Similar syntax is used by most global cell phone carriers.  For specifics, check with the carrier.

 

Cell Phone Carrier E-Mail Format
Sprint phonenumber@messaging.sprintpcs.com
Verizon phonenumber@vtext.com
T-Mobile phonenumber@tmomail.net
AT&T phonenumber@txt.att.net

select
'joe.salesmanager@oracle.com' as EMAIL_TO,
'john.smith@oracle.com' as EMAIL_FROM,
'3031234567@vtext.com' as EMAIL_CC,
'bigboss@oracle.com' as EMAIL_BCC
INTO EMAIL_TO,EMAIL_FROM,EMAIL_CC,EMAIL_BCC
from

The next section contains the Condition in the form of a SQL sub-select statement.  In this example is the SQL described earlier that calculates whether the revenue for 'PocketFun ES', for European OnLine sales in 2013 is under $3,500,000.

If the condition is true, the select statement returns a row of data.  If it is false, no data is returned.  The data itself is not important, only whether a row is returned or not.

(select
extract(year from c.time_bill_dt) as year,
sum(c.revenue) as revenue,
sum (c.cost_fixed + c.cost_variable) as cost,
sum(1) as orders,
p.product,
g.region,
o.channel_name
from cloud_f_cost c
join cloud_d_products p on p.prod_item_key=c.prod_item_key
join cloud_d_geography g on g.addr_key = c.addr_key
join cloud_d_orders o on o.order_key = c.order_key
group by
extract(year from c.time_bill_dt),
p.product,
g.region,
o.channel_name)
where product='PocketFun ES' and year=2013 and Region='EMEA' and Channel_name='Online' and revenue<3500000;

If data is returned, the alert recipient information will be passed into the apex_mail.send queue.  If no data is returned, the above select will return nothing, and so no recipient information will be added to the send queue.

The final piece of the statement calls the 'APEX_MAIL.PUSH_QUEUE' command to actually send the e-mail.

end;
apex_mail.send(
p_to => EMAIL_TO,
P_FROM => EMAIL_FROM,
P_BODY => 'Message',
p_body_html => 'ALERT - PocketFun ES EMEA 2013 Online Underforming',
P_SUBJ => 'ALERT - PocketFun ES EMEA 2013 Online Underforming',
p_cc => EMAIL_CC,
p_bcc => EMAIL_BCC,
p_replyto => ''
);
APEX_MAIL.PUSH_QUEUE;
END;

 

Repeating the whole Stored Procedure in it's entirety:

CREATE OR REPLACE PROCEDURE "EMAIL_POCKETFUN_PERFORMANCE"
AS
EMAIL_TO VARCHAR2(255);
EMAIL_FROM VARCHAR2(255);
EMAIL_CC VARCHAR2(255);
EMAIL_BCC VARCHAR2(255);
BEGIN
begin
select
'joe.salesmanager@oracle.com' as EMAIL_TO,
'john.smith@oracle.com' as EMAIL_FROM,
'3031234567@vtext.com' as EMAIL_CC,
'bigboss@oracle.com' as EMAIL_BCC
INTO EMAIL_TO,EMAIL_FROM,EMAIL_CC,EMAIL_BCC
from
(select
extract(year from c.time_bill_dt) as year,
sum(c.revenue) as revenue,
sum (c.cost_fixed + c.cost_variable) as cost,
sum(1) as orders,
p.product,
g.region,
o.channel_name
from cloud_f_cost c
join cloud_d_products p on p.prod_item_key=c.prod_item_key
join cloud_d_geography g on g.addr_key = c.addr_key
join cloud_d_orders o on o.order_key = c.order_key
group by
extract(year from c.time_bill_dt),
p.product,
g.region,
o.channel_name)
where product='PocketFun ES' and year=2013 and Region='EMEA' and Channel_name='Online' and revenue<3500000; end; apex_mail.send( p_to => EMAIL_TO,
P_FROM => EMAIL_FROM,
P_BODY => 'Message',
p_body_html => 'ALERT - PocketFun ES EMEA 2013 Online Underforming',
P_SUBJ => 'ALERT - PocketFun ES EMEA 2013 Online Underforming',
p_cc => EMAIL_CC,
p_bcc => EMAIL_BCC,
p_replyto => ''
);
APEX_MAIL.PUSH_QUEUE;
END;

 

Testing the Stored Procedure

Once compiled, the Procedure can be tested:

begin
EMAIL_POCKETFUN_PERFORMANCE;
end;

If the Condition is TRUE - which in this case it is, since in the SAMPLE APP data the revenue for this combination is $3,450,000 - an alert will be generated.

 

Scheduling the Stored Procedure

The procedure can be scheduled to run using the Apex Cloud Scheduler.  The job_action value must equal the name of the Stored Procedure created previously.  In this example a scheduled job is created to run Daily, starting at 7am.  More details of how to use the scheduler can be found in the 'Further Reading' section below.

BEGIN
cloud_scheduler.create_job(
job_name => 'POCKETFUN_PERFORMANCE_EMAIL',
job_type => 'STORED_PROCEDURE',
job_action => 'EMAIL_POCKETFUN_PERFORMANCE',
start_date => '28-AUG-15 07.00.00.000000 AM -07:00',
repeat_interval => 'FREQ=DAILY',
enabled => TRUE,
comments => 'POCKETFUN_PERFORMANCE_EMAIL');
END;

To confirm the schedule:

SELECT * FROM user_scheduler_jobs;

To delete the scheduled job:

BEGIN
cloud_scheduler.drop_job(
job_name => 'EMAIL_POCKETFUN_PERFORMANCE');
END;

 

Summary

This article demonstrated how a Stored Procedure can be used and scheduled to trigger an Event Based Alert for BICS.

 

Further Reading

Apex Mail

https://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_mail.htm#AEAPI341

Scheduler

http://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha