One of the Sarbanes Oxley impacts I’ve seen on the enterprise accounting side is how supporting documentation for journal entries must be saved and retained for many years. If done the old fashioned way of printing paper and filing it, this could go beyond simply killing trees and bring about deforestation on a global scale. I’m sure I saw something about this in Al Gore’s video.

My current client is a private company, but they’re considering going public and so they’re implementing PeopleSoft with SOX in mind. One of the things they wanted to know up front is whether or not they could attach supporting documentation electronically to the journal entries and thereby avoid having to manage so much paper.

Using PeopleSoft’s Attachment Server functionality, I was able to get a proof of concept up and running without too much effort. Here’s the screen shot:
Supporting Documentation Tab

From the user’s point of view, they navigate to the Enter/Update Journal Entries. Then they click on this Supporting Documentation tab where they can view existing documentation or add new files to the journal.

The Add button takes them to a page that lets them browse their computer for a file, and upload it to the web site. If you’ve ever used a web-based e-mail client and attached a file you get the idea.

Once the file is uploaded it is saved in the PeopleSoft database and associated to the Journal using the Business Unit and Journal ID keys. The user can enter a description of the file, and the Operator ID and date/time stamp is saved with the journal.

We also added business logic to keep users from adding or changing attachments after the journal has been posted. This may have to be relaxed to some extent to meet the realities of a closing cycle.

Okay, that’s enough boring business functional stuff. Let’s get technical!

If you’ve never used the attachment server, here’s what you need to know in bullet form:

  • It can be set up to store attachments in a directory or in the database. In my opinion, the database method is better.

  • What little bit is required to set up the attachment server is under Set Up Financials /Supply Chain > Common Definitions > File Attachments > Administer File Attachments. If you’re not using FSCM you’ll find it in a similar place in your application.

  • It’s documented in different places in PeopleBooks, but a good place to start is PeopleBooks > PeopleCode Developers Guide > Understanding File Attachments and PeopleCode.

  • If you’d like to see a delivered example, check out the LOAD_JRNL_PNL component (General Ledger > Journals > Import Journals > External Flat Files). If you don’t have PS Financials, you’ll have to find your own example.

  • The PeopleCode built-in file attachment functions are AddAttachment, DeleteAttachment, and ViewAttachment. You’ll probably use the wrapper functions on the FILE_ATTACH_WRK record instead of calling them directly.



The steps I went through to create this proof of concept were:

  1. Created a new record to hold attachments called XX_JRNL_ATTACH. It had the following fields:

    • BUSINESS_UNIT – Key Field for Journal

    • JOURNAL_ID – Key Field for Journal

    • ATTACHSYSFILENAME – Key Field. Unique System Identifier for the attached file. Making it a key field insures uniqueness.

    • ATTACHUSERFILE – The name of the file from the user’s hard drive.

    • DESCR – Allows the users to enter a description of the attachment so they don’t have to view it to see what it is.

    • And of course the audit fields CREATED_BY_USER, CREATED_DTTM, LAST_UPDATED_BY and LAST_UPDATE_DTTM to track who modified something and when.


  2. Created the page to enter attachments on. BU and Journal ID were on level zero, AttachUserFile, Descr, and anything else related to the attachments were in the scroll area in Level 1.

  3. Inserted my custom page in the JOURNAL_ENTRY_IE component.

  4. Then I activated the necessary Component PeopleCode. I basically copied the releveant component PeopleCode from the LOAD_JRNL_PNL to the JOURNAL_ENTRY_IE component. You’ll find it under the FILE_ATTACH_WRK record in both components, and you’ll need to copy PeopleCode for all three fields ATTACHADD, ATTACHDELETE and ATTACHVIEW.
    Component PeopleCode for LOAD_JRNL_PNL

    Component PeopleCode for JOURNAL_ENTRY_IE

  5. I put some RowInit PeopleCode on the XX_JRNL_ATTACH record to logically gray out the Add or View buttons depending on whether or not a file was on the row. You can see an example of this at LOAD_JRNL_REQ.OPRID.RowInit.

  6. I also added RowDelete PeopleCode to call the DeleteAttachment function when a row is deleted. Then I made the decision to not include the DeleteAttachment button since a user could just as easily delete and re-add the row.

  7. Of course there is a little PeopleCode to correctly update the audit fields, but I’ll bet you don’t need my help with that piece.



That’s it in a nutshell. I certainly welcome your comments, questions and suggestions.
Written by :
 
Trackback(0)
Comments (11)Add Comment
0
printing attachment
written by arshad, November 07, 2007
Hi

I went through your post. Have you tried printing the attachments using SQR or crystal or any other reporting tool of PS. If yes, could you please let me know how you were able to do that.

Regards
Arshad
Brent Martin
RE: Printing Attachment
written by Brent Martin, November 17, 2007
If you click the View button, it'll open the attachment in whatever tool you have on your workstation (excel, adobe acrobat, etc). Once you have it on your workstation, you can print from there.
0
Systems Analyst
written by Susan Paden, December 28, 2007
This is great information. Thank you for posting. I actually need to do both storing the file in the database AND ftping it to a server. Have you seen this done? I think I'm making it way harder than it needs to be. If you have any tips they would be appreciated.

THANKS MUCH! smilies/smiley.gif
Brent Martin
RE: Systems Analyst
written by Brent Martin, December 28, 2007
I believe you can configure multiple file attachment servers to do both ways, but it seems like the user would have to upload it twice (which I'm guessing isn't what you want).

I'm not sure exactly what problem you're looking to solve, but sometimes users have files on their computers that they need to process with an SQR. I use the attachment functionality to let them attach the file in a run control page. Then I create an Application Engine process that invokes PeopleCode to detach the file to a directory on the server. Then I just point my SQR process to pick the file up from that directory location on the server. Create a job that runs the App Engine first, then the SQR and you're done.

If you're running Financials, you can use "Flat File Journal Import Request" as a model for how this can be done.

If you just need to store the file in the database, and in a directory on your application server, you could just add some FieldChange PCode to "detach" the file to a directory on your app server after the attachment has been loaded.

Check out the PeopleCode function "GetAttachment" to save a database attachment to a file.

Hope this helps...
0
...
written by Susan Paden, December 28, 2007
Thanks Brent. I'm trying to use the GetAttachment and I keep getting failures. Do you have any code examples?
Brent Martin
...
written by Brent Martin, December 28, 2007
This code came out of the journal import app engine GL_JRNL_IMP. One thing i like is that it opens a file that you can write debug messages to. Hope it helps!

SQLExec("SELECT UNICODE_ENABLED FROM PSSTATUS", &RtnCode);
If &RtnCode = 1 Then
&DBcharset = "U"; /* UNICODE */
Else
&DBcharset = "ANSI"; /* ANSI */
End-If;
&CharSet = LOAD_JRNL_AET.CHARSET;
If None(&CharSet) Then
&CharSet = &DBcharset;
End-If;

&LogFileName = "GL_JRNL_IMP_" | String(LOAD_JRNL_AET.PROCESS_INSTANCE) | ".LOG";
&LogFile = GetFile(&LogFileName, "W", &DBcharset, i;lePath_Relative);
&DataFileName = LOAD_JRNL_AET.ATTACHUSERFILE;

&LogFile.WriteLine(MsgGetText(5825, 106, "Flat File Journal Import"));
&LogFile.WriteLine(" ");
&LogFile.WriteLine(" " | Úte);
&LogFile.WriteLine(Rept("-", 40));


try
&RtnCode = GetAttachment(URL.GL_FILE_IMPORT, LOAD_JRNL_AET.ATTACHSYSFILENAME.Value, &DataFileName, "PS_FILEDIR");
catch Exception &Excp;
&RtnCode = 99;
If &Excp.MessageSetNumber = 2 And
&Excp.MessageNumber = 788 Then
MessageBox(0, "", 5825, 118, "Check PS_FILEDIR variable");
Else
Error (&Excp.ToString());
End-If;
end-try;

If &RtnCode = 0 Then
&ImportFile = GetFile(&DataFileName, "R", &CharSet, i;lePath_Relative);
If &ImportFile.IsOpen Then
&ImportFile.SetFileLayout(FileLayout.GL_JRNL_IMPORT);
&LogFile.WriteLine(Left(String(&#xTi;me), smilies/cool.gif | " " | MsgGetText(5825, 104, "Processing file...", &DataFileName));
MessageBox(0, "", 5825, 104, "", &DataFileName);

&File_RS = &ImportFile.CreateRowset();
ImportData(&File_RS);
&ImportFile.Close();
Else
/* Cannot open file */
&LogFile.WriteLine(MsgGetText(5825, 126, "GetFile error", &DataFileName));
&ERRcount = &ERRcount 1;
End-If;
Else
/* Error getting attachment */
&LogFile.WriteLine(MsgGetText(5825, 125, "GetAttachment error", &DataFileName, &RtnCode));
&ERRcount = &ERRcount 1;
End-If;


If &ERRcount = 0 Then
&LogFile.WriteLine(Left(String(&#xTi;me), smilies/cool.gif | " " | MsgGetText(5825, 116, "Validating imported data"));
Else
/***** Rollback all transactions *****/
&LogFile.Close();
MessageBox(0, "", 5825, 102, "Errors found - Rollback");
Exit (1);
End-If;
0
...
written by Susan Paden, December 28, 2007
Thanks again Brent. I think where I'm getting hung up is that I need to first add it to the database THEN I need to 'GetAttachment' and send it to a server. Every example I find is from the app server to a file server.
Here is what I have for code:

&RETCODE = AddAttachment(&URL_ID, &ATTACHSYSFILENAME, &FILEEXTENSION, &ATTACHUSERFILE, &FILESIZE);

&GetAttach = GetAttachment(URL.AUC_TEMP, CHMC_CKRQ_ATTCH.ATTACHSYSFILENAME.Value, &ATTACHSYSFILENAME, "FILE");


The Addattachment is working but the getattachment is not. I'm pretty new to PeopleCode and honestly I have no idea what I need to do.

Thanks again!

0
How to perform file attachment?
written by Hollowman, March 04, 2008
Hi,

I want to develope a page for uploading files.

Can any one tell me any tutorial/book/article demonstrating the step by step procedure to do this?

An example would suffice.

I have read "Understanding File Attachments and PeopleCode" from PeopleCode Developer’s Guide - Enterprise PeopleTools 8.49 PeopleBook, but still it is not clear how to proceed.

Thanks.
0
How do we do Anti virus scan for the Attachment
written by Atul, March 08, 2008
is there a way anti virus scan be done for the attachment being stored in database !
Brent Martin
...
written by Brent Martin, March 09, 2008
Hollowman - I don't have a tutorial for the attachment server. How I made it happen the first time was to find some delivered code that did the job and clone from that. It was a Financials system and I used the Journal Upload component. I'm sure you'll find at least one example in whatever app you're working on.

Atul - That's a really good question. There's no way to do it "out of the box". One thought would be to modify the PCode so that after it saves it to the database, it detaches it into a directory with a real-time virus scanner running, then waits a few seconds and see if the file still exists. If the virus scanner quarantines it, the file will disappear and you can assume you have a virus and handle it accordingly. Okay I'm sure there are better ideas but that might be a start.

Good luck!
Brent Martin
Detaching via PL/SQL
written by Brent Martin, May 29, 2008
Jim Marion has a post about how to detach a file using PL/SQL. Check it out! http://jjmpsj.blogspot.com/200...using.html

Write comment

security code
Write the displayed characters


busy

Last Updated on Monday, 18 December 2006 10:11.