Automated Emailing of Concurrent Report Output

Automated Emailing of Concurrent Report Output

The Concurrent Manager in Oracle EBS can automatically send an email notification when a concurrent job has completed, but it does not actually send you the report output, which is probably what you really want! So here’s one way to do it.

1. Create a Workflow, which accepts as input the following parameters:
   1. Concurrent Request ID
   2. Output File Name – from FND_CONC_REQ_OUTPUTS or FND_CONCURRENT_REQUEST
   3. File Type – either ‘TEXT’ or value from FND_CONC_REQ_OUTPUTS.FILE_TYPE
   4. Role Name – the workflow role to which report is to be emailed
2. The workflow needs to retrieve the report output file into a CLOB or BLOB, depending on whether it is a text or binary (PDF, Excel, RTF, HTML). You’ll need to write a PL/SQL procedure to do this, which is called from the workflow message, and uses DBMS_LOB.loadclobfromfile or DBMS_LOB.loadblobfromfile. There are examples of this in the Workflow documentation. The workflow then sends the file as an attachment to the given role. You can customize the message subject and body with whatever information you need.
3. Define a Flexfield Value set, which will return the role names of the users to whom reports may be emailed. For example, a value set based on the SQL will return all users and employees:

SELECT wur.role_name,
       wlr.display_name,
       wlr.email_address,
       wlr.notification_preference
  FROM wf_user_roles wur, wf_local_roles wlr
 WHERE wur.role_orig_system IN ('PER', 'FND_USR')
   AND wur.role_name = wlr.name
   AND wur.user_orig_system = wlr.orig_system;

4. Create a PL/SQL concurrent program, which looks for completed concurrent requests meeting the following conditions.
   • The program has one or more parameters which use the value set created in step 3 above.
   • At least one of those parameters was given a value when this request was run.
   • The request hasn’t already been processed by this program. Yes, you’ll need to create a table to store the requests that have been processed.Create a PL/SQL concurrent program, which looks for completed concurrent requests meeting the following conditions.

When the program finds a request meeting these conditions, it submits the workflow created in step 1, passing it the appropriate parameters, so that it will email the report output the role given in the request’s parameter value. Then, it writes the request ID and role name to a custom table, in order to keep an audit trail, and prevent emailing the same report multiple times to the same user.

The parameters for a request can be found by joining FND_CONCURRENT_REQUESTS to FND_CONC_REQUEST_ARGUMENTS. Arguments 1 – 25 are in the former table, arguments 26 – 99 in the latter. The value set of an argument can be determined by joining to FND_DESCR_FLEX_COLUMN_USAGES.

5. Schedule the concurrent program from step 4 to run at regular intervals, such as every 5 minutes.
6. Add optional parameters to any concurrent program for which you wish to enable emailing. You can add as many email parameters as you wish, and call them whatever you want, as long as they use the value set defined in step 2. Add the parameters after the program’s “real” parameters, and it won’t affect the executable the program calls.