The PeopleCode SendMail() function is a quick and easy way to send emails from your PeopleSoft application. Unlike workflow routing and Notification classes, SendMail() provides the most functionality when it comes to sending emails. Over the newer releases, PeopleSoft has further added new capabilities to this function like overriding the content-type and sender details. However, the feature that I find the least used is the inclusion of file attachments. This is because in PIA, PeopleCode runs either on the Application server or the Batch server. The files that can be attached should be either 1) located on the server, or 2) accessed by the server through a network share. For this reason, most applications that use file attachments are Application Engine programs (running on the Batch server) that attach log files from within the server.

If you’ve used web-based email services like Yahoo, then you’ll find it a common feature to allow attachments to be uploaded by a user from his workstation and send those attachments with the email message. Is this possible in PeopleSoft’s internet architecture? The answer is yes, this article will show you how.

First, let’s assume we have 2 buttons on a page: [Select Attachment] and [Send]. The [Select Attachment] button will upload the file to a server; the [Send] button will trigger the sending of the email with attachment. Because of PIA’s multi-tiered architecture (web server <--> application server <--> dbms server), uploading a file to the application server is a challenge.

The PeopleCode function for uploading a file from a PeopleSoft page is AddAttachment(). The following dialog is displayed when this function is called on an online page:

Add Attachment Dialog

PeopleSoft also delivered a PeopleCode-defined function that serves as an interface to AddAttachment() that contains additional filename and error checking. The function is named add_attachment and is located on FILE_ATTACH_WRK.ATTACHADD FieldChange. This is what we’re going to use.

Approach 1: File transfer via FTP

If you look at the documentation of AddAttachment() in PeopleBooks — PeopleCode Developer’s Guide > Understanding File Attachments and PeopleCode, you’ll find that there are two types of destination that this function uploads the file to. The first type of destination is FTP. You can use this destination type if your application server already has an FTP service. The FieldChange PeopleCode on [Select Attachment] would look like this:


Declare Function add_attachment PeopleCode FILE_ATTACH_WRK.ATTACHADD FieldChange;
Component string &ATTACHSYSFILENAME, &ATTACHUSERFILE;
Component string &Sysfilename;
Local number &RetCode;
 
REM Select sysfilename prefix that will serve as uniqueness identifier;
&ATTACHSYSFILENAME = %UserId;

add_attachment("ftp://user:password@appserver.ftp.address/", "", "", 0, False, "", &ATTACHSYSFILENAME, &ATTACHUSERFILE, 2, &RetCode);

In the above code, prior to the call to add_attachment, &ATTACHSYSFILENAME defines a prefix that will be appended to the actual system file created on the destination. It is set to %UserId to avoid filename conflicts with different users that might simultaneously be uploading a file with the same name. After the add_attachment is executed, the value of &ATTACHUSERFILE is set to the source filename (without path) selected by the user; &ATTACHSYSFILENAME would then set to %UserId | &ATTACHUSERFILE.

The FieldChange PeopleCode for the [Send] button, would then call SendMail() and delete the uploaded file:


Component string &ATTACHSYSFILENAME, &ATTACHUSERFILE;
Local File &tmpfile;
Local String &Sysfilename = "/path/to/ftp/destination/" | &ATTACHSYSFILENAME;
 
SendMail(0, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &Sysfilename, &ATTACHUSERFILE);
 
REM Cleanup: Remove temporary data;
&tmpfile = GetFile(&Sysfilename, "W", "A", %FilePath_Absolute);
&tmpfile.Delete();

In the above code, the value of /path/to/ftp/destination/ would depend on the FTP service configuration. &Sysfilename should contain the absolute path of the file on the server.

Approach 2: Upload file to intermediate database table

Because of the requirement of running a FTP service on the Application server, the first approach may not be a good option. In my experience, it would be tough convincing an administrator to setup a FTP service on a server machine just for the benefit of a single application. An alternative, yet also more portable option, is to upload the file to a database table. This option does not require additional configuration on the server. This is a two-step process though, and is not as efficient as the first option.

First, the file is uploaded to a database table. If the file is large, it may be chunked and stored across multiple rows. This process is documented thoroughly in the PeopleBook PeopleCode Developer’s Guide > Understanding File Attachments and PeopleCode. Now that the file is stored on a table, it can be retrieved later and saved to an Application server file prior to calling SendMail().

Step 1. Create a record for storing file attachments. In this example, we create a record and save it as FILE_STORE. Inside this record, only a single subrecord is added: FILE_ATTDET_SBR. Build/create this record. Note: if you’re running on DB2 OS390/zOS, check that Maximum Attachment Chunk Size option in PeopleTools Options page is set lower than the tablespace size of PS_FILE_STORE. The default Maximum Attachment Chunk Size is 28000, it is better to create PS_FILE_STORE in a 32K tablespace.

Once PS_FILE_STORE is created, the FieldChange PeopleCode for [Select Attachment] would be:


Declare Function add_attachment PeopleCode FILE_ATTACH_WRK.ATTACHADD FieldChange;
Component string &ATTACHSYSFILENAME, &ATTACHUSERFILE;
Component string &Sysfilename;
Local number &RetCode;

REM Select sysfilename prefix that will serve as uniqueness identifier;
&ATTACHSYSFILENAME = %UserId;

add_attachment("record://FILE_STORE", "", "", 0, False, "", &ATTACHSYSFILENAME, &ATTACHUSERFILE, 2, &RetCode);

Now, FieldChange PeopleCode for [Send] button would have addition steps for copying data from table to file and deleting rows from the table:


Component string &ATTACHSYSFILENAME, &ATTACHUSERFILE;
Local File &tmpfile;
Local integer &i;
 
REM Transfer data from DB table to local filesystem on Application server;
REM A check for latest version should be included in case user uploaded same file multiple times;
Local Rowset &rsAttachment = CreateRowset(Record.FILE_STORE);
&rsAttachment.Fill("where ATTACHSYSFILENAME = :1 AND VERSION = (SELECT MAX(VERSION) FROM PS_FILE_STORE WHERE ATTACHSYSFILENAME = FILL.ATTACHSYSFILENAME)  order by FILE_SEQ", &ATTACHSYSFILENAME);
&tmpfile = GetFile(&ATTACHSYSFILENAME, "N");
Local String &Sysfilename = &tmpfile.Name;
For &i = 1 To &rsAttachment.RowCount
  &tmpfile.WriteRaw(&rsAttachment(&i).FILE_STORE.FILE_DATA.Value);
End-For;
&tmpfile.Close();
 
SendMail(0, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &Sysfilename, &ATTACHUSERFILE);
 
REM Cleanup: Remove temporary data;
SQLExec("DELETE FROM PS_FILE_STORE WHERE ATTACHSYSFILENAME = :1", &ATTACHSYSFILENAME);
&tmpfile = GetFile(&Sysfilename, "W", "A", %FilePath_Absolute);
&tmpfile.Delete();

Update

As noted by Ketan in the comments area, a better way to copy the attachment to the local filesystem is by using the GetAttachment() function. The FieldChange PeopleCode for the [Send] button would now look like this:


Component string &ATTACHSYSFILENAME, &ATTACHUSERFILE;
Local File &tmpfile;
 
Local string &filesyspath = GetEnv("PS_SERVDIR") | "/files";
REM Create PS_SERVDIR/files directory if it doesn't yet exists;
CreateDirectory(&filesyspath, %FilePath_Absolute);
&Sysfilename = &filesyspath | "/" | &ATTACHSYSFILENAME;
 
REM Transfer data from DB table to local filesystem on Application server;
If GetAttachment("record://FILE_STORE", &ATTACHSYSFILENAME, &Sysfilename) = 1 Then
   /* Problem getting file */
End-If;
 
SendMail(0, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &Sysfilename, &ATTACHUSERFILE);
 
REM Cleanup: Remove temporary data;
SQLExec("DELETE FROM PS_FILE_STORE WHERE ATTACHSYSFILENAME = :1", &ATTACHSYSFILENAME);
&tmpfile = GetFile(&Sysfilename, "W", "A", %FilePath_Absolute);
&tmpfile.Delete();

A thing to note about the above snippet is that line CreateDirectory(&filesyspath, %FilePath_Absolute); was added to ensure that PS_SERVDIR/files is available. Unlike GetFile(&ATTACHSYSFILENAME, "N"), GetAttachment() does not automatically create PS_SERVDIR/files directory if it does not yet exists in the application server.

Additional Notes

  • In approach 1, you could assign a different machine as the destination FTP server. However, the path to the destination folder must be shared and accessible from the Application server. Deleting the file would then require the DeleteAttachment() function.
  • In approach 1 and the application server is Unix-based, the user that runs the application server processes should also have write access to the ftp destination files. Otherwise, the use of DeleteAttachment() function may also be required. Approach 2 should not have such limitations because the application server process should have full access to the default destination folder of GetFile().
  • For simplicity, the above code samples only deal with a single file attachment. It could be easily extended to handle multiple attachments. SendMail() allows semicolon-separated list of values for the attachments.