Periodic Alert to send an email when the concurrent programs error out.

I have come across a requirement where my client wants to know the Concurrent Program details which are error-ed out to his email and below is the process i have followed.

SELECT   request_id, fcpt.user_concurrent_program_name program_name,

         DECODE (fcr.phase_code, 'C', 'Completed') phase,

         DECODE (fcr.status_code, 'E', 'Error') status,

         TO_CHAR (actual_start_date, 'MM/DD/YY HH24:mi:SS') act_start

    INTO &req_id, &prg_name,

         &phase,

         &status,

         &start_date

    FROM apps.fnd_concurrent_requests fcr,

         apps.fnd_concurrent_programs_tl fcpt

   WHERE 1 = 1

     AND fcpt.user_concurrent_program_name IN

            ('Specify the specific Concurrent Program Name which you want to monitor')

     AND fcr.concurrent_program_id = fcpt.concurrent_program_id

     AND fcpt.LANGUAGE = 'US'

     AND fcr.status_code = 'E'

GROUP BY request_id,

         fcpt.user_concurrent_program_name,

         fcr.requested_start_date,

         fcr.actual_completion_date,

         fcr.actual_start_date,

         fcr.phase_code,

         fcr.status_code

Open the Application and Navigate to Alert Manager Responsibility

Alert -> Define

1)Enter all the Mandatory(Yellow in colour) fields here.

2)Select the type of alert you need according to the Requirement(Here i have taken Periodic Alert

with frequency Every N Calendar Days).

3)Give the start time and end time.

4)Give the select statement.

5)Click on verify which will parse the SQL you entered.

6)Click on RUN and it will show how many rows are returned by your select statement(depends on requirement).

7)Save the form(CTRL+S).

8)Click on actions button which will open a window as shown below.

Give the Action Name,description and the level of Action.

Next, Click on Action Details.

Select the Action Type as Message and give the email address in TO,CC  and BCC and also Specific subject to the mail which will be delivered.

In Text write the content you need to display in the email body.

Here,I was specific about the Concurrent program name, request id, phase and status of the concurrent program.

Close the form after saving it.

Click on Action Sets Button.

Fill the details as shown.

Enable the Suppress Duplicates to remove the duplicates fetched by the query to return in email.

Click on Action Set Details Button to open a form as shown below.

1)Enter the Action Name you earlier entered in the Actions form.

2)Select Exit Action Set Successfully to exit the action set after the process.

3)Save and close the form and in the Alerts form Click on Alert Details Button.

Give the Oracle ID and Operating Unit in the installations TAB.

Save the From and close.

And you are Done.!!

Challa.