Email alert for approved Purchasing Requisitions

Today's tip is for those implementers who are dealing with users who expect a little more than what the Oracle EBS does..you know, users who want the system to do things like:

* Notifications on what work they shall be doing
* Warnings when you mismatch 1,000.00 for 10,000.00
* Email the office's restaurant menu daily
* Weather analysis for the weekend on Cancún

You know, just simple things.

But hey, don't get me wrong, users might get a little too enthusiastic on what Oracle EBS does, but part of the problem comes with our pre-sales and sales buddies. Sometimes they get a little too excited when explaining all the ERP features to the users, but well what can we do this is how it works.

Anyway, after that subtle comment on over-enthusiastic users and our non-technical buddies on sales, Have you ever wonder how can you notify to buyers when a purchasing document has been approved? Simple right, just tell the user to log into his/her notifications within Oracle EBS and done. Well not quite, this tip is for users that are a little more reluctant to the whole ERP thing. Users who prefer this kind of notifications on email.

This can be achieved by using Oracle Alerts. Oracle Alerts is a EBS module that allow users to configure actions that trigger on predefined events. Most common type of alerts are email and notifications.

This functionality is quite simple to implement:
First, you will need Oracle Purchasing installed and running (of course).
Second, you need Oracle Alerts up and running.
Third, verify that Workflow Mailer is up and running (this is how email notifications are sent)
Fourth, you will need some technical skills for this, if you are one of those consultants that claim to be only functional please go to SAP, if you are on Oracle you should have some technical skills.

Later versions of Oracle Alerts use Workflow Mailer to perform email notifications, but you should verify which version are you on, since the sendmail command was used on previous releases.

For this example we will be notifiyng the buyer when a purchase requisition is been approved. Here is how is done:

1.- Configure an Alert
Go to Alert Manager responsibility
Log into Define form
Type a name and a description for the alert
Set it for type Event
Under Application choose Purchasing and under table choose PO_REQUISITION_HEADERS_ALL





Under the SQL box type the following statement:

SELECT prh.segment1 num_req, prh.description,

prh.creation_date fecha_creacion, prh.approved_date fecha_aprob,

prh.preparer_id id_usuario, pap.full_name solicitante, decode(prh.org_id,119,'XXX@XXX.com,120,XX@XXX.com')

INTO &num_req, &descr, &fec_creacion, &fec_aprob,

&id_usuario, &solicitante, &mail_comprador

FROM po_requisition_headers_all prh,

po_requisition_lines_all prl,

per_all_people_f pap

WHERE prh.requisition_header_id = prl.requisition_header_id

AND approved_date IS NOT NULL

AND pap.person_id = prh.preparer_id

AND NVL (prh.cancel_flag, 'N') = 'N'

AND prh.authorization_status = 'APPROVED'

AND prh.org_id in (119,120)

AND prh.rowid = :rowid

Just some points to notice on the SQL:

* For this example we have hard-coded the buyer's email depending on the org_id. This can be set dynamic since there a buyer field in the requisition but the users will have to type the buyer for every line in the requisition. Once you got the buyer you can get the email for that user.
* The :rowid variable restricts the statement only to the current record (the one that is being updated, inserted)
* The fields preceded by (&) within the into clause, are the fields passed as variables to the alert. This fields can be used later in the message or email.

Validate the SQL statement and confirm that there are no errors.



Now go to the Actions b utton and create a new action called SEND_MAIL (this can be any name you want)
Set the action level to Summary








Now go to the Action Details button and set the action type as Message. You will see a kind of email form. Under the TO field type &mail_comprador which is the variable name on the SQL statement for the buyer's email. Set a Subject for the email
Enter the text that you wan t the buyer to see. In this text you can include any variable from the SQL statement (such as requisition number, preparer, etc)







Close the window and now go to Action Sets and type an action set name.

Go to Action Set Details and unde r the Members tab verify that the action is set correctly





Close the window and now go to the Alert Details and verify that you have the correct operating units for the alert to work on.



Now you have succesfully created the alert. Now try approving a requisition that meets the criteria in your alert. Afterwards run the Workflow Background Process concurrent program (emails won't be send before this concurrent is executed) and wait for the email. It shouldn't take more that a couple minutes to arrive.

Hope it helps.

Diego