Sending concurrent request output by e-mail - part II

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