If the database and apps.server are not installed on the same machine the first proposed solution may not work. The database user will not be able to read concurrent request output files if they are not located in a common directory which can be reached from both the servers. In my case no such common directory existed.
There was one common directory which could be reached from both machines and the code below takes advantage by that. The code is a SQL-script which is fired by a concurrent request.
--+=======================================================================+
--| FILENAME |
--| XXXFND_SUBMIT_AND_EMAIL.sql |
--| |
--| VERSION |
--| 1.0 |
--| |
--| CUSTOMIZATION |
--| FND |
--| |
--| DESCRIPTION |
--| Creates. |
--| |
--| |
--| HISTORY |
--| 26-01-2012 Daniel Borgstrom, Logica Created |
--+=======================================================================*
VARIABLE g_exit_code VARCHAR2(20);
VARIABLE g_os_cp VARCHAR2(1000);
VARIABLE g_os_rm VARCHAR2(1000);
VARIABLE g_file_name VARCHAR2(200);
--+=======================================================================*
--| Submit request
--+=======================================================================*
DECLARE
no_req EXCEPTION;
v_wait_status BOOLEAN;
v_request_id NUMBER;
v_request_phase VARCHAR2(1000);
v_request_status VARCHAR2(1000);
v_dev_request_phase VARCHAR2(1000);
v_dev_request_status VARCHAR2(1000);
v_request_status_mesg VARCHAR2(1000);
v_file_directory VARCHAR2(100) := '$APPLCSF/$APPLOUT/';
v_compl BOOLEAN;
BEGIN
v_request_id := fnd_request.submit_request('FND', -- Application
'FNDSCARU', -- Request (Active Responsibilities and Users)
'','',FALSE,
'','','','','','','','',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '');
-- Verify that the concurrent request was launched
-- successfully.
IF (v_request_id = 0) THEN
raise no_req;
ELSE
fnd_file.put_line(fnd_file.log, 'Submitted request: '||v_request_id);
END IF;
COMMIT;
-- wait for request to finish
v_wait_status := fnd_concurrent.wait_for_request(v_request_id,
2, -- interval in sec
60, -- total wait time in sec
v_request_phase,
v_request_status,
v_dev_request_phase,
v_dev_request_status,
v_request_status_mesg);
IF v_dev_request_phase = 'COMPLETE' AND v_dev_request_status = 'WARNING' THEN
raise no_req;
ELSIF v_dev_request_phase != 'COMPLETE' AND v_dev_request_status != 'NORMAL' THEN
raise no_req;
END IF;
-- Generate scripts to send output file to a shared area
fnd_file.put_line(fnd_file.log, 'Internal file commands');
:g_file_name := 'Active_Resp_'||TO_CHAR(SYSDATE,'YYMMDD')||'.pdf';
fnd_file.put_line(fnd_file.log, 'File name: '||:g_file_name);
:g_os_cp := 'cp -p '||v_file_directory||'o'||v_request_id||'.out '||'/common/'||:g_file_name;
fnd_file.put_line(fnd_file.log, :g_os_cp);
:g_os_rm := 'rm /common/'||:g_file_name;
fnd_file.put_line(fnd_file.log, :g_os_rm);
:g_exit_code := 'SUCCESS';
EXCEPTION
WHEN no_req THEN
fnd_file.put_line(fnd_file.log, 'ERROR: Could not submit request');
v_compl := Fnd_Concurrent.Set_Completion_Status('ERROR', 'Could not submit request');
:g_exit_code := 'FAILURE';
WHEN others THEN
fnd_file.put_line(fnd_file.log, 'ERROR: '||SQLERRM);
v_compl := Fnd_Concurrent.Set_Completion_Status('ERROR', 'ERROR: '||SQLERRM);
:g_exit_code := 'FAILURE';
END;
/
--+=======================================================================*
--| Send host command:
--| Copy to a temporarily folder
--+=======================================================================*
SET SERVEROUTPUT ON
SET PAGESIZE 57
SET LINESIZE 132
SET TERMOUT ON
SET HEADING OFF
SET VERIFY ON
COLUMN os_cp NOPRINT NEW_VALUE os_cp;
SELECT :g_os_cp os_cp
FROM dual
WHERE :g_exit_code = 'SUCCESS';
HOST &os_cp
--+=======================================================================*
--| E-mail file
--+=======================================================================*
DECLARE
v_response VARCHAR2(1000);
v_compl BOOLEAN;
BEGIN
fnd_file.put_line(fnd_file.log, 'Sending e-mail');
-- send the mail
xx_send_mail_file('Sending the output of request Active Responsibilities and Users',
'Result is attached.'||chr(10)||'Sysadmin',
'yourname@yourdomain.com',
9999999999,
:g_file_name,
NULL,
NULL,
v_response);
IF nvl(v_response,'SUCCESS') = 'SUCCESS' THEN
fnd_file.put_line(fnd_file.log, 'Mail response: '||nvl(v_response,'SUCCESS'));
:g_exit_code := 'SUCCESS';
ELSE
fnd_file.put_line(fnd_file.log, 'Could not send e-mail: '||v_response);
v_compl := Fnd_Concurrent.Set_Completion_Status('ERROR', 'Could not send e-mail: '||v_response);
:g_exit_code := 'FAILURE';
END IF;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log, 'ERROR: '||SQLERRM);
v_compl := Fnd_Concurrent.Set_Completion_Status('ERROR', 'ERROR: '||SQLERRM);
:g_exit_code := 'FAILURE';
END;
/
--+=======================================================================*
--| Send host command:
--| Remove tmp file
--+=======================================================================*
SET SERVEROUTPUT ON
SET PAGESIZE 57
SET LINESIZE 132
SET TERMOUT ON
SET HEADING OFF
SET VERIFY ON
COLUMN v_os_rm NOPRINT NEW_VALUE v_os_rm;
SELECT :g_os_rm v_os_rm
FROM dual
WHERE :g_exit_code = 'SUCCESS';
HOST &v_os_rm
--+=======================================================================*
--| Write exit code to list and concurrent manager |
--+=======================================================================*
SET SERVEROUTPUT OFF
SET PAGESIZE 57
SET LINESIZE 132
SET TERMOUT ON
SET HEADING ON
COLUMN exit_statement NOPRINT NEW_VALUE exit_statement;
SELECT :g_exit_code exit_statement
FROM dual;
EXIT
