Oracle Alerts R12 : Event Based Alert

There are two types of alert in Oracle Apps. Event Alert and Periodic Alert.

Event Alert fires when an event occurs, i .e., when an insert or update is done on a table.

This exercise is to create an Event Based Alert in Oracle Apps. The objective is to update the po_header_id in PO_HEADERS_ALL table when a po line is either created or updated.

Navigation : Alert Manager > Alert > Define

A sample of alert screen.

We need to fill the mandatory fields with details as shown below. On creation of a po line or update of a po line the corresponding po_header_id of the record will be stored in the variable PO_NUMBER.

The click on Actions button and give the Action Name.

The click on Action Details. Choose Action Details as SQL Statement Script. Use the following code to update record in PO_HEADERS_ALL.

UPDATE po_headers_all set attribute1 = &PO_NUMBER where po_header_id = &PO_NUMBER;

commit;

Then close the screen and move to main alert screen. Click on Action Set Details and give the Action Set Name.

Click on Alert Set Details and go the Members tab and select the Action (Update Record).

Go to the main Alerts screen and click on Alert Details button. Then move to Installation tab. Give the Operating Unit.

Save and close.

In order to check whether the Alert has processed, go to the following navigation.

Alert Manager > History

Query for the alert we have created and click on Find Actions.

A concurrent program, Check Event Alert will be triggered when alert gets executed. Query to find the status of the concurrent program.

SELECT REQUEST_ID

, REQUEST_DATE

, PHASE_CODE

, STATUS_CODE

, ORACLE_ID

, PROGRAM_APPLICATION_ID

, CONCURRENT_PROGRAM_ID

FROM APPLSYS.FND_CONCURRENT_REQUESTS

where Concurrent_program_id = (select Concurrent_program_id from fnd_concurrent_programs_tl where

USER_CONCURRENT_PROGRAM_NAME = 'Check Event Alert') order by request_date desc;