Email the output of a concurrent program using UTL_MAIL

 Email the output of a concurrent program using UTL_MAIL

1. Write a procedure that will submit the concurrent program whose output has to be sent as an Email and once the program completes, send the output as Email using UTL_MAIL.send_attach_varchar2.

2. Register this procedure as a concurrent program so that this program can be run from Oracle Applications which will email a concurrent program’s output.

Sample code explanation :

1. Write the below procedure which submits the desired concurrent program and waits until it completes and then sends the output of that program to the specified Email address using the utilityUTL_MAIL.send_attach_varchar2

CREATE OR REPLACE PROCEDURE apps.XXAA_send_email (

   errbuf                       VARCHAR2,

   retode                       NUMBER,

   p_concurrent_program_name    VARCHAR2,

   p_parameter1                 NUMBER)

IS

   /*Variable declaration*/

   fhandle                       UTL_FILE.file_type;

   vtextout                      VARCHAR2 (32000);

   text                          VARCHAR2 (32000);

   v_request_id                  NUMBER := NULL;

   v_request_status              BOOLEAN;

   v_phase                       VARCHAR2 (2000);

   v_wait_status                 VARCHAR2 (2000);

   v_dev_phase                   VARCHAR2 (2000);

   v_dev_status                  VARCHAR2 (2000);

   v_message                     VARCHAR2 (2000);

   v_application_id              NUMBER;

   v_concurrent_program_id       NUMBER;

   v_conc_prog_short_name        VARCHAR2 (100);

   v_conc_prog_appl_short_name   VARCHAR2 (100);

   v_output_file_path            VARCHAR2 (200);

BEGIN

   fnd_file.put_line (fnd_file.output, '');

   fnd_file.put_line (fnd_file.output,

                      'Conc Prog: ' || p_concurrent_program_name);

   fnd_file.put_line (fnd_file.output, 'Parameter 1:' || p_parameter1);

   /* Get Concurrent_program_id of the desired program and application_id */

   BEGIN

      SELECT concurrent_program_id, application_id

        INTO v_concurrent_program_id, v_application_id

        FROM fnd_concurrent_programs_tl

       WHERE user_concurrent_program_name = p_concurrent_program_name;

      fnd_file.put_line (fnd_file.LOG,

                         'Conc Prog ID:' || v_concurrent_program_id);

      fnd_file.put_line (fnd_file.LOG,

                         'Application ID: ' || v_application_id);

      /* Get the program's Short name */

      SELECT concurrent_program_name

        INTO v_conc_prog_short_name

        FROM fnd_concurrent_programs

       WHERE concurrent_program_id = v_concurrent_program_id;

      fnd_file.put_line (fnd_file.LOG,

                         'Conc Prog Short Name: ' || v_conc_prog_short_name);

      /* Get the Application Short name */

      SELECT application_short_name

        INTO v_conc_prog_appl_short_name

        FROM fnd_application

       WHERE application_id = v_application_id;

      fnd_file.put_line (

         fnd_file.LOG,

         'Application Short Name:' || v_conc_prog_appl_short_name);

   EXCEPTION

      WHEN OTHERS

      THEN

         fnd_file.put_line (fnd_file.LOG, 'Error: ' || SQLERRM);

   END;

   /* Calling fnd_request.submit_request to submit the desired the concurrent program*/

   v_request_id :=

      fnd_request.submit_request (v_conc_prog_appl_short_name,

                                  v_conc_prog_short_name,

                                  NULL,                          --Description

                                  NULL,            --Time to start the program

                                  FALSE,                        -- sub program

                                  p_parameter1);

   fnd_file.put_line (fnd_file.LOG,

                      'Concurrent Request Submitted

Successfully: ' || v_request_id);

   COMMIT;

   IF v_request_id IS NOT NULL

   THEN

      /*Calling fnd_concurrent.wait_for_request to wait for the

      program to complete */

      v_request_status :=

         fnd_concurrent.wait_for_request (request_id   => v_request_id,

                                          INTERVAL     => 10,

                                          max_wait     => 0,

                                          phase        => v_phase,

                                          status       => v_wait_status,

                                          dev_phase    => v_dev_phase,

                                          dev_status   => v_dev_status,

                                          MESSAGE      => v_message);

      v_dev_phase := NULL;

      v_dev_status := NULL;

   END IF;

   /* Getting the path where output file of the program is

   created */

   SELECT outfile_name

     INTO v_output_file_path

     FROM fnd_concurrent_requests

    WHERE request_id = v_request_id;

   /* Open the output file in Read mode */

   fhandle :=

      UTL_FILE.fopen (

         '/opt/oracle/OACRP1/common/admin/out/OACRP1_dtuusebs14',

         'o' || v_request_id || '.out',

         'r');

   IF UTL_FILE.is_open (fhandle)

   THEN

      DBMS_OUTPUT.put_line ('File read open');

   ELSE

      DBMS_OUTPUT.put_line ('File read not open');

   END IF;

   /* Get the contents of the file into variable “text”*/

   LOOP

      BEGIN

         UTL_FILE.get_line (fhandle, vtextout);

         text := text || vtextout || UTL_TCP.crlf;

      EXCEPTION

         WHEN NO_DATA_FOUND

         THEN

            EXIT;

      END;

   END LOOP;

   UTL_FILE.fclose (fhandle);

   /*Calling UTL_MAIL.send_attach_varchar2 to send the output as Email attachment */

   UTL_MAIL.send_attach_varchar2 (

      sender       => 'XXXXXXX.XXXXXX@gmail.com',

      recipients   => 'xxxxxx.yyyyyy@gmail.com',

      subject      => 'Testmail',

      MESSAGE      => 'Hello',

      attachment   => text,

      att_inline   => FALSE);

END;