{"id":186,"date":"2011-03-25T07:10:08","date_gmt":"2011-03-25T07:10:08","guid":{"rendered":"http:\/\/sendyoursmiles.com\/articles\/email-the-output-of-a-concurrent-program-as-attachment"},"modified":"2011-03-25T07:10:08","modified_gmt":"2011-03-25T07:10:08","slug":"email-the-output-of-a-concurrent-program-as-attachment","status":"publish","type":"post","link":"https:\/\/erpschools.com\/erps\/articles\/email-the-output-of-a-concurrent-program-as-attachment","title":{"rendered":"Email the output of a concurrent program as Attachment"},"content":{"rendered":"

This article illustrates the steps to be followed to Email a concurrent program’s output.<\/p>\n

    \n
  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.<\/li>\n
  2. \n
    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.<\/div>\n<\/li>\n<\/ol>\n

    Detailed explanation with sample code:<\/strong><\/p>\n

      \n
    1. \n
      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 utility UTL_MAIL.send_attach_varchar2<\/div>\n

       <\/li>\n<\/ol>\n

      <\/p>\n

      CREATE OR REPLACE PROCEDURE apps.erp_send_email
      \n(
      \nerrbuf VARCHAR2,
      \nretode NUMBER,
      \np_concurrent_program_name VARCHAR2,
      \np_parameter1 NUMBER
      \n)
      \nIS
      \n\/*Variable declaration*\/
      \nfhandle UTL_FILE.file_type;
      \nvtextout VARCHAR2 (32000);
      \ntext VARCHAR2 (32000);
      \nv_request_id NUMBER := NULL;
      \nv_request_status BOOLEAN;
      \nv_phase VARCHAR2 (2000);
      \nv_wait_status VARCHAR2 (2000);
      \nv_dev_phase VARCHAR2 (2000);
      \nv_dev_status VARCHAR2 (2000);
      \nv_message VARCHAR2 (2000);
      \nv_application_id NUMBER;
      \nv_concurrent_program_id NUMBER;
      \nv_conc_prog_short_name VARCHAR2 (100);
      \nv_conc_prog_appl_short_name VARCHAR2 (100);
      \nv_output_file_path VARCHAR2 (200);<\/p>\n

      BEGIN
      \nfnd_file.put_line (fnd_file.output, '------------------------------------------------------);
      \nfnd_file.put_line (fnd_file.output, 'Conc Prog: ' || p_concurrent_program_name);
      \nfnd_file.put_line (fnd_file.output, 'Parameter 1:' ||p_parameter1);<\/p>\n

      \/* Get Concurrent_program_id of the desired program and application_id *\/
      \nBEGIN
      \nSELECT concurrent_program_id, application_id
      \nINTO v_concurrent_program_id, v_application_id
      \nFROM fnd_concurrent_programs_tl
      \nWHERE user_concurrent_program_name = p_concurrent_program_name;<\/p>\n

      fnd_file.put_line (fnd_file.LOG,'Conc Prog ID:' || v_concurrent_program_id);
      \nfnd_file.put_line (fnd_file.LOG, 'Application ID: ' || v_application_id );<\/p>\n

      \/* Get the program's Short name *\/
      \nSELECT concurrent_program_name
      \nINTO v_conc_prog_short_name
      \nFROM fnd_concurrent_programs
      \nWHERE concurrent_program_id = v_concurrent_program_id;<\/p>\n

      fnd_file.put_line (fnd_file.LOG,'Conc Prog Short Name: ' || v_conc_prog_short_name);<\/p>\n

      \/* Get the Application Short name *\/
      \nSELECT application_short_name
      \nINTO v_conc_prog_appl_short_name
      \nFROM fnd_application
      \nWHERE application_id = v_application_id;<\/p>\n

      fnd_file.put_line (fnd_file.LOG,'Application Short Name:' || v_conc_prog_appl_short_name);<\/p>\n

      EXCEPTION
      \nWHEN OTHERS THEN
      \nfnd_file.put_line (fnd_file.LOG, 'Error: ' || SQLERRM);
      \nEND;<\/p>\n

      \/* Calling fnd_request.submit_request to submit the desired the concurrent program*\/<\/p>\n

      v_request_id:=
      \nfnd_request.submit_request(v_conc_prog_appl_short_name,
      \nv_conc_prog_short_name,
      \nNULL, --Description
      \nNULL, --Time to start the program
      \nFALSE, -- sub program
      \np_parameter1
      \n);<\/p>\n

      fnd_file.put_line (fnd_file.LOG,'Concurrent Request Submitted Successfully: ' || v_request_id);<\/p>\n

      COMMIT;<\/p>\n

      IF v_request_id IS NOT NULL
      \nTHEN<\/p>\n

      \/*Calling fnd_concurrent.wait_for_request to wait for the program to complete *\/<\/p>\n

      v_request_status:=
      \nfnd_concurrent.wait_for_request
      \n(
      \nrequest_id => v_request_id,
      \nINTERVAL => 10,
      \nmax_wait => 0,
      \nphase => v_phase,
      \nstatus => v_wait_status,
      \ndev_phase => v_dev_phase,
      \ndev_status => v_dev_status,
      \nMESSAGE => v_message<\/p>\n

      );<\/p>\n

      v_dev_phase := NULL;
      \nv_dev_status := NULL;
      \nEND IF;<\/p>\n

      \/* Getting the path where output file of the program is created *\/
      \nSELECT outfile_name
      \nINTO v_output_file_path
      \nFROM fnd_concurrent_requests
      \nWHERE request_id = v_request_id;<\/p>\n

      \/* Open the output file in Read mode *\/
      \nfhandle := UTL_FILE.fopen ('\/opt\/oracle\/ERPS\/common\/admin\/out\/ERPSchools','o' || v_request_id || '.out', 'r');<\/p>\n

      IF UTL_FILE.is_open (fhandle)
      \nTHEN
      \nDBMS_OUTPUT.put_line ('File read open');
      \nELSE
      \nDBMS_OUTPUT.put_line ('File read not open');
      \nEND IF;<\/p>\n

      \/* Get the contents of the file into variable \"text\"*\/
      \nLOOP
      \nBEGIN
      \nUTL_FILE.get_line (fhandle, vtextout);
      \ntext := text || vtextout || UTL_TCP.crlf;
      \nEXCEPTION
      \nWHEN NO_DATA_FOUND THEN<\/p>\n

      EXIT;
      \nEND;
      \nEND LOOP;<\/p>\n

      UTL_FILE.fclose (fhandle);<\/p>\n

      \/*Calling UTL_MAIL.send_attach_varchar2 to send the output as
      \nEmail attachment *\/
      \nUTL_MAIL.send_attach_varchar2
      \n(
      \nsender => 'prudhvi@erpschools.com',
      \nrecipients => 'training@erpschools.com',
      \nsubject => 'Testmail',
      \nMESSAGE => 'Hello',
      \nattachment => text,
      \natt_inline => FALSE
      \n);
      \nEND;<\/p>\n

      <\/code><\/p>\n

        \n
      1. Register the above written procedure as a concurrent program<\/li>\n<\/ol>\n