Sending the Concurrent Request Output in Mail Attachment

     A Concurrent Program output cannot be sent as an attachment in the Oracle Application by itself. To implement this task find the steps.(Note: Most of the Scripts are designed for the Unix Based Systems).

  Normally the Concurrent Program output files are stored under the Directory: $APPLCSF/$APPLOUT and the all the Logfiles for the Same are stored under the $APPLCSF/$APPLLOG Directory. (For E.g. A Concurrent Request is submitted with it's number 2211122 then the output file generated under $APPLCSF/$APPLOUT as o2211122.out, irrespective of the format set at the concurrent request level and a log file is generated under $APPLCSF/$APPLLOG as l2211122.req)

Step 1:

      Write a concurrent Program and register as PDF (it will not work with the XML based reports, only the RDF based reports without as PDF can only be used.)

    Assume the XX_REPT1 is my concurrent program.

Step 2:

    Write a shell script as given below and regsiter as HOST file:

     a)  A Shell script by name XX_SENDMAIL.prog need to be   saved under the XX_TOP/bin directory.
     b) A Soft link to be created for $FND_TOP/$APPLBIN/fndcpesr to XX_SENDMAIL.
                   ln -sf $FND_TOP/$APPLBIN/fndcpesr XX_SENDMAIL

     c) Make sure the shell script has the 755 permissions.
   

     d) A Concurrent Program executable for XX_SENDMAIL need to be registered under Oracle Apps as HOST type.


      e) This Concurrent Program uses 5 default parameters internally, any any custom parameter will be used as fifth and above for the Shell script:


                    i)  First Parameter $0 : Program Name
                    ii) Second Parameter $1 : Username / Password
                   iii) Third Parameter $2 :  Apps User id
                    iv) Fourth Parameter $3 : Apps User Name
                     v) Fifth Parameter $4 :  Request ID


A Sample Shell Script to run any Shell script as a Concurrent Program is given below:

#------------------------------------------
outfile=$FND_TOP/$APPLOUT/o$4.out

echo '====================' > `echo $outfile`

  echo 'Program Name  :'$0 >> `echo $outfile`
  echo 'username/pwd  :' $1 >> `echo $outfile`
  echo 'apps user id :' $2 >> `echo $outfile`
  echo 'apps username:' $3 >> `echo $outfile`
  echo 'request id   :' $4 >> `echo $outfile`
  echo ------------------- >> `echo $outfile`
  echo 'Program arguments:' >>  `echo $outfile`
  echo ------------------- >> `echo $outfile`
#  shellscript arguments  start below :
  echo '$5= : '$5 >> `echo $outfile`
  echo '$6= : '$6 >> `echo $outfile`
  echo '$7= : '$7 >> `echo $outfile`
  echo '$8= : '$8 >> `echo $outfile`

  echo '$9= : '$9 >> `echo $outfile` 

#  shellscript commands can be entered below :
echo "Hello World " >> `echo $outfile`
echo '=====================' >> `echo $outfile`
#---------------------------------------------

  To send the mail from the Unix Servers, we can use many command line tools like mail,mailx,sendmail,mutt,pine etc. But some of the command line client tools need to be installed and some won't support to send attachment. mail and mailx  is the vastly used command line mail client and these can be used for without attachments or text based attachment mails.But not for the PDF based Attachment mails.

For eg:

mailx -s "Test mail"  xyz2abc.com< 1.txt

The above command embeds the contents of the text file into the mail. When we try to send the pdf file, then it embeds the junk characters in the body of email,as it doesn't have the encoding method. So, we need to encode this format which can be understandable by the Mail Servers to read the file as attachment.For which we need to use the uuencode command.

For Eg:

  uuencode file.pdf file.pdf | mailx -s "Test Mail as Attachment" xyz@abc.com

    The above command successfully sends a pdf format file as an attachment and it can be received by most recent Email Servers.But some exchange servers still can't understand this encoding and the file.pdf will be embedded inside the body of the mail will be seen with so many junk letters.

     To Overcome the above error, and after a brief research, found the sendmail command tool, with MIME encoding, uuencode to send a PDF attachment and the Body with HTML Code in a mail.The below shell Script can be used to send the pdf file attachment irrespective of the Mail Server(Gmail,Yahoo,MS Exchange etc)

To use a single shell script for all your concurrent requests, made a quite few changes in the above script and registration of the Concurrent Program with the set of following parameters:

 outfile=$APPLCSF/$APPLOUT/o$4.out <<< This will get the output file name for the current Shell script

echo '=====================================================' > `echo $outfile`

 echo 'Program Name  :'$0 >> `echo $outfile`   - Displays the Program name in Output file after completion of the program
 echo 'apps user id :' $2 >> `echo $outfile` 
 echo 'apps username:' $3 >> `echo $outfile`
 echo 'request id   :' $4 >> `echo $outfile`
 echo ------------------- >> `echo $outfile`
 echo 'Program arguments:' >>  `echo $outfile`
 echo ------------------- >> `echo $outfile`  


# There are 5 Parameters passed from the Script initiation PL/SQL Procedure to this Shell script
 echo ' email-id   :' $5 >> `echo $outfile`     
 echo 'request id   :' $6 >> `echo $outfile`
 echo 'subject   :' $7 >> `echo $outfile`
 echo 'att_name   :' $8 >> `echo $outfile`
 cp $APPLCSF/$APPLOUT/o$6.out $APPLCSF/$APPLOUT/o$6.pdf

PDFFILE=$APPLCSF/$APPLOUT/o$6.pdf
ATTNAME=$8
MAILFROM=oracle.alerts@arm.com.ng
SUBJECT=$7
MAILTO=$5
MAILBCC=$9  << This is the last parameter in the concurrent request, the email id in the last parameter can be set for Bcc or Cc.For CC just use MAILCC instead of MAILBCC>>
boundary="****"
{
echo  "From: $MAILFROM"
echo  "To: $MAILTO"
echo  "Bcc: $MAILBCC"
echo  "Subject:" $SUBJECT
echo "Mime-Version:1.0"${version}
echo "Content-Type:Multipart/mixed;boundary=Message-Boundary-"${boundary}
echo "--Message-Boundary-"${boundary}
echo "Content-type:text/html;charset=US-ASCII"
echo "Content-transfer-encoding: 7BIT"
echo "Content-Disposition:inline"
echo "Content-Description: Read Me First"
echo ""
echo "<html>
<head>
<title>HTML E-mail</title>
</head>
<title>HTML E-mail</title>
</head>
<body>
<p>Dear Sir/Madam,</p>
<p>&nbsp;</p>
<p>Please find the Attached Output File Details.</p>
<p>&nbsp;</p>
<p>Thanks and Regards,</p>
<p>Oracle Production Instance(XXX Company,Location)</p>

</body>
</html>"
echo ""
echo "--Message-Boundary-"${boundary}
echo "Content-type:Application/Octet-Stream;name=${PDFFILE};type=Binary"
echo "Content-disposition:inline;filename=${ATTNAME}"
echo "Content-transfer-encoding: X-UUencode"

echo "--Message-Boundary-"${boundary}
uuencode $PDFFILE $PDFFILE
}| /usr/lib/sendmail -t

     The above program can be used dynamically for any concurrent with the desired email-id's in TO,BCC and also a Subject.

    Register the above schell script as host executable and then register that executable to the desirec concurrent program with 4 Parameters:
    a) Email Address - Mandatory
    b) Request ID - Mandatory
    c) Subject - Mandatory
    d)Att_name - Mandatory
    e) Bcc_email - Optional

Let us register the concurrent Program with the shortname as XX_MAIL_SEND


Step 3:

     Now we need a master program to run the XX_REPT1 and XX_MAIL_SEND.  The logic inside this master concurrent program is :
  
   a) Submit the concurrent Program XX_REPT1
   b) Get the Request ID of the Concurrent request
   c) Check the Request ID is NULL or not
   d) IF not NULL then execute the check the PHASE_CODE of the above request
   e) LOOP the checking of PHASE_CODE until it compeltes
   f) If the STATUS_CODE is NORMAL then send initiate XX_MAIL_SEND program with the mandatory parameters.

Please find the Procedure of Master Program:

 CREATE OR REPLACE PROCEDURE apps.xx_conc_submit (
   errbuf    OUT   VARCHAR2,
   retcode   OUT   NUMBER
)
IS
   l_request_id     NUMBER;
   p_request_id   NUMBER;
   l_phase_code     VARCHAR2 (1);
   l_status_code    VARCHAR2 (1);
   l_date           VARCHAR2 (100);
BEGIN
   fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),
                               fnd_profile.VALUE ('RESP_ID'),
                               fnd_profile.VALUE ('RESP_APPL_ID'),
                               NULL,
                               NULL
                              );
   l_request_id :=
      fnd_request.submit_request (application      => 'XX',
                                  program          => 'XX_REPT1',
                                  description      => NULL,
                                  start_time       => NULL,
                                  sub_request      => FALSE
                                 );
   COMMIT;
   fnd_file.put_line (fnd_file.LOG,
                      'before loop l_request_id=' || l_request_id
                     );

   IF l_request_id IS NOT NULL
   THEN
----
      fnd_file.put_line (fnd_file.LOG,
                         ' Request Id inside IF :  ' || l_request_id
                        );

      SELECT phase_code
        INTO l_phase_code
        FROM fnd_concurrent_requests
       WHERE request_id = l_request_id;

      WHILE ('C' != l_phase_code)
      LOOP
         SELECT phase_code
           INTO l_phase_code
           FROM fnd_concurrent_requests
          WHERE request_id = l_request_id;
      END LOOP;

      fnd_file.put_line (fnd_file.LOG,
                         ' Phase_code after loop :  ' || l_phase_code
                        );

      SELECT status_code
        INTO l_status_code
        FROM fnd_concurrent_requests
       WHERE request_id = l_request_id AND phase_code = l_phase_code;

----
      IF (l_phase_code = 'C' AND l_status_code = 'C')
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Inside IF' || l_phase_code);
         l_request_id :=
            fnd_request.submit_request
               (application      => 'XX
                program          => ' XX_MAIL_SEND',
                description      => NULL,
                start_time       => NULL,
                sub_request      => FALSE,
                argument1        => 'abc@gmail.com,def@zzz.com,kkk@zzz.com',
                argument2        => l_request_id,
                argument3        => 'Test Subject',
                argument4        => 'attachement_name.pdf',
                argument5        => 'bcc1@ddd.com,bcc2@ddd.com'
               );
      END IF;
   END IF;
END xx_conc_submit;

     Thanks for all the resources over web to get into this final code.My special thanks to Oracle Maestro Mr.Ezhilarasan,without him it might not had completed. All the Very Best