Oracle EBS Alert - Basics

Idea of this post is to create a simple alert and test if it is working or not.
Just for example, assume a requirement to send Happy birthday mail to employees in an organization.

As you might already know by now, there are 2 kinds of alerts in oracle apps.
Periodic Alerts and Event based Alerts.
You are correct, we have to choose Periodic alert for our requirement.

Employee Birthday can be any calendar day of the year. So we will tell oracle apps 
to check daily once every calender day and see if today is employee's birthday and send email if true.

You need Alert Manager Responsibility to define a new Alert.
Navigate through Alert Manager -> Alert -> Define
Fill the options as given in the screenshot below.

Note that we need to write SQL which satisfies our condition and also to fetch required details. Here is the SQL

select global_name, date_of_birth, email_address
into &emp_name, &dob, &emp_email
from per_all_people_f
where trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');

You can check the SQL for syntax using "verify" button.

One point here, if any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occured (number of rows that satisfied the condition).

Next step is to define action if condition matches. Don't forget to select action level of type "Detail". This is because action should be performed once for every Alert Exception.

Then click on "Action Details" button and define the email message as shown below.
Note the fields highlighted in orange are the alias names that are defined in query (INTO clause). Oracle Alert will dynamically replace these aliases with the actual values at run time. The one highlighted in green is the hard coded constant "Reply to" email address.

That's not enough, we also need "Action Sets" and attach the action which we just created. Just follow the screenshots to do that.

Now comes the question, how do we test this Alert?

Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]

Once concurrent program is successfully ran, the number of exceptions can be verified from Alert Manager -> History and query for alert. Here exceptions mean, the number of records that matched the SQL query and to be processed by Alert Engine.

Which means our Alert is working.
Note that you can do many more than just sending mails. Alert can execute SQL code/function/procedures and it can also run concurrent programs.
So why wait, go ahead and explore more. :-)

Good Trick to speed up Oracle HRMS API debugging

One good trick to ease development and debugging of Oracle Hrms API's.
The code behind any HRMS API has lots of trace statements.
Usual way of getting these debug statements is by enabling PYUPIP trace.
Alternatively if you know all the API parameters, you can turn on the trace by calling it in the following way.

BEGIN 
  hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT'); 
  hr_utility.trace_on; 
  <API call>
  hr_utility.trace_off; 
END;

Trace messages can be redirected to DB pipe using the following code snippet. It assumes DB pipe is already open and running.

BEGIN
  HR_UTILITY.TRACE_ON(NULL,'CRE_BATCH');
   <API CALL;>
  HR_UTILITY.TRACE_OFF;
END;