Very
often you get requirement from the client to email the output of a report to
the users. So here is a sample way by which you can generate the report output
and email the same to the user.
Following are the setups involved:
1.
Create a Request set with two concurrent programs
a.
First concurrent program is developed based on the customer
requirement to generate the report output.
b.
Second concurrent program gathers the output details into a
custom table.
2. Create an Alert on the custom table to send the output of the Request set’s first concurrent program to the users.
To achieve required customization
following steps need to be performed:
1.
Creation
of database objects
2.
Creation
of a PL-SQL script
3.
Defining
concurrent programs
4.
Defining
request set
5.
Defining
alert
A custom table is created and registered in a particular
responsibility (e.g. PUR). This table stores information about concurrent
program (request id, output file path), mail id etc. Alert uses this
information for sending mails.
Table script:
CREATE TABLE XYZ_TAB
(
REQUEST_ID NUMBER,
PRIORITY_REQUEST_ID NUMBER,
FILE_NAME VARCHAR2(1000) NOT NULL,
FILE_SIZE NUMBER,
EMAIL_ID VARCHAR2(1000) NOT NULL,
)
This table needs to be registered in the required schema,
only after registering this table can be used in alerts.
Following command should be for registering:
EXECUTE
AD_DD.REGISTER_TABLE ('PO’,’ ‘XXX_TAB’);
Creation of a PL-SQL script
This PL/SQL script inserts
information about a completed concurrent program in the custom table XXX_TAB_ABC.
Information is gathered form table FND_CONCURRENT_REQUESTS table and
inserts into XXX_TAB_ABC table
every time it is executed by request set. This script is to be registered as a
concurrent program.
CREATE OR REPLACE PROCEDURE XXX_PRC (errbuf OUT VARCHAR2,
retcode OUT NUMBER)
IS
l_vr_filename VARCHAR2 (700);
l_vr_mailid VARCHAR2 (700);
l_nm_reqid NUMBER;
l_nm_priorityreqid NUMBER;
l_nm_filesize NUMBER;
BEGIN
retcode := 0;
l_nm_reqid := fnd_global.conc_request_id;
/*
FETCHES THE REQUEST ID OF THE REQUEST SET.*/
BEGIN
SELECT priority_request_id
INTO l_nm_priorityreqid
FROM fnd_concurrent_requests
WHERE request_id = l_nm_reqid;
fnd_file.put_line (fnd_file.LOG,
'PRIORITY_REQUEST_ID'
|| l_nm_priorityreqid);
EXCEPTION
WHEN OTHERS
THEN
retcode := 4;
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;
/*FETCHES
THE DETAILS- OUTPUT FILE NAME & OUTPUT FILE SIZE*/
BEGIN
SELECT outfile_name, ofile_size
INTO l_vr_filename, l_nm_filesize
FROM fnd_concurrent_requests
WHERE priority_request_id =
l_nm_priorityreqid
AND request_id <> l_nm_reqid
AND request_type = 'P';
fnd_file.put_line (fnd_file.LOG,
'OUTFILE_NAME' ||
l_vr_filename || l_nm_filesize);
BEGIN
NULL;
/*This part of code can be used for getting email id*/
/* set value for L_VR_FILENAME */
END;
IF (l_nm_filesize = 1)
THEN
fnd_file.put_line (fnd_file.LOG, 'NO DATA EXISTS');
ELSE
/*INSERTING RECORDS IN
CUSTOM TABLE*/
INSERT INTO xxx_tab_abc
VALUES (l_nm_reqid,
l_nm_priorityreqid,
l_vr_filename,
l_nm_filesize,
l_vr_filename);
fnd_file.put_line (fnd_file.LOG, 'INSERTED');
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
retcode := 4;
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;
END XXX_PRC;
Defining
concurrent programs
Two
concurrent programs need to be defined:
1. Concurrent program
for report
2. Concurrent program
for stored procedure
Defining
request set:
Define
a request set on the above concurrent programs.
Defining
alert:
This is the final step of the process.
Navigate to Alert Manager> Alert > Define.
An event based alert need to be defined on the custom table <XXX_TAB>
created earlier as <XXX_ALERT>. This alert fires whenever
a row is inserted into the table and sends a mail to recipient.
Select
statement reveals that the output of the alert is the output file path that is
taken from the custom table <XXX_TAB>.
Now
navigate to action details tab and define the alert action. In ‘To’ Field enter
e-mail recipient id or variable taken from EMAIL_ID column (&OP2) of XXX_TAB_ABC. In the ‘File(C)’ field enter variable used
for storing file path in the SQL statement.
