To implement this functionality, we would have to know the following:
1. The concurrent request id of the program that is tied to an xml publisher defined layout
2. The path and name of the output file that is generated by the output post processor for the concurrent request
3. An email utility that can mail the generated output as an attachment
It is quite obvious for us to know from where to pick up the request id. It would be interesting to know the name of the file and the path where the xml publisher generated output is stored.
Let us assume that we have a concurrent request that is tied to an xml publisher layout which will format the output to a PDF document. When the concurrent request completes its run, it generates 2 output files in this case. The first file is the xml data file and the second file is the pdf file. Both the files get generated and stored in the usual $APPLCSF/$APPLOUT path.
What is of interest here is the file name given to the pdf file. This file name looks to be a combination of the XML publisher definition code, the request id and a running number. How do we get to know this filename from within oracle?
There is a table named FND_CONC_REQ_OUTPUTS that stores the request id, dir path and the file name, filesize and so on. This is the table that we use to read the file name from after looking it up with a particular request id. Once this is done, we are all set to email the file as an attachment. How do we do it from within oracle?
Oracle provides us with a PL/SQL wrapper named SendMailJPkg for its internal java mail utility that is loaded into the database. We can use this to send the email and the contents of the pdf file as an attachment. The utility has a method named SendMail. This method has a list of parameters which are pretty straightforward to lookup and understand. The last parameter is a pl/sql table named attachments. We can send multiple attachments using this pl/sql table type parameter. In our case, to send the pdf as an attachment, we would simply have to instantiate one element in the pl/sql table and pass the file name including the path as a value to the element. Set all the other appropriate param values and call the method. The pdf file would land up nicely in the user's inbox.
1. The concurrent request id of the program that is tied to an xml publisher defined layout
2. The path and name of the output file that is generated by the output post processor for the concurrent request
3. An email utility that can mail the generated output as an attachment
It is quite obvious for us to know from where to pick up the request id. It would be interesting to know the name of the file and the path where the xml publisher generated output is stored.
Let us assume that we have a concurrent request that is tied to an xml publisher layout which will format the output to a PDF document. When the concurrent request completes its run, it generates 2 output files in this case. The first file is the xml data file and the second file is the pdf file. Both the files get generated and stored in the usual $APPLCSF/$APPLOUT path.
What is of interest here is the file name given to the pdf file. This file name looks to be a combination of the XML publisher definition code, the request id and a running number. How do we get to know this filename from within oracle?
There is a table named FND_CONC_REQ_OUTPUTS that stores the request id, dir path and the file name, filesize and so on. This is the table that we use to read the file name from after looking it up with a particular request id. Once this is done, we are all set to email the file as an attachment. How do we do it from within oracle?
Oracle provides us with a PL/SQL wrapper named SendMailJPkg for its internal java mail utility that is loaded into the database. We can use this to send the email and the contents of the pdf file as an attachment. The utility has a method named SendMail. This method has a list of parameters which are pretty straightforward to lookup and understand. The last parameter is a pl/sql table named attachments. We can send multiple attachments using this pl/sql table type parameter. In our case, to send the pdf as an attachment, we would simply have to instantiate one element in the pl/sql table and pass the file name including the path as a value to the element. Set all the other appropriate param values and call the method. The pdf file would land up nicely in the user's inbox.
