One of the Sarbanes Oxley impacts Ive 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. Im sure I saw something about this in Al Gores video.
My current client is a private company, but theyre considering going public and so theyre 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 PeopleSofts Attachment Server functionality, I was able to get a proof of concept up and running without too much effort. Heres the screen shot:
From the users 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 youve 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, thats enough boring business functional stuff. Lets get technical!
If youve never used the attachment server, heres what you need to know in bullet form:
The steps I went through to create this proof of concept were:
Thats it in a nutshell. I certainly welcome your comments, questions and suggestions.
RE: Printing Attachment
written by Brent Martin, November 17, 2007
Systems Analyst
written by Susan Paden, December 28, 2007
RE: Systems Analyst
written by Brent Martin, December 28, 2007
...
written by Susan Paden, December 28, 2007
...
written by Brent Martin, December 28, 2007
...
written by Susan Paden, December 28, 2007
How to perform file attachment?
written by Hollowman, March 04, 2008
How do we do Anti virus scan for the Attachment
written by Atul, March 08, 2008
...
written by Brent Martin, March 09, 2008
Detaching via PL/SQL
written by Brent Martin, May 29, 2008
My current client is a private company, but theyre considering going public and so theyre 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 PeopleSofts Attachment Server functionality, I was able to get a proof of concept up and running without too much effort. Heres the screen shot:
From the users 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 youve 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, thats enough boring business functional stuff. Lets get technical!
If youve never used the attachment server, heres 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 youre not using FSCM youll find it in a similar place in your application.
- Its documented in different places in PeopleBooks, but a good place to start is PeopleBooks > PeopleCode Developers Guide > Understanding File Attachments and PeopleCode.
- If youd like to see a delivered example, check out the LOAD_JRNL_PNL component (General Ledger > Journals > Import Journals > External Flat Files). If you dont have PS Financials, youll have to find your own example.
- The PeopleCode built-in file attachment functions are AddAttachment, DeleteAttachment, and ViewAttachment. Youll 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:
- 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 users hard drive.
- DESCR Allows the users to enter a description of the attachment so they dont 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.
- 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.
- Inserted my custom page in the JOURNAL_ENTRY_IE component.
- 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. Youll find it under the FILE_ATTACH_WRK record in both components, and youll need to copy PeopleCode for all three fields ATTACHADD, ATTACHDELETE and ATTACHVIEW.
- 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.
- 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.
- Of course there is a little PeopleCode to correctly update the audit fields, but Ill bet you dont need my help with that piece.
Thats it in a nutshell. I certainly welcome your comments, questions and suggestions.
Trackback(0)
TrackBack URI for this entry
Comments (11)
Subscribe to this comment's feed
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
-
-
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!
THANKS MUCH!
-
- +0
-
-
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...
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?
-
- +0
-
-
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),
| " " | 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), | " " | MsgGetText(5825, 116, "Validating imported data"));
Else
/***** Rollback all transactions *****/
&LogFile.Close();
MessageBox(0, "", 5825, 102, "Errors found - Rollback");
Exit (1);
End-If;
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),
| " " | 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),
| " " | 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!
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
-
-
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.
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
-
-
written by Atul, March 08, 2008
is there a way anti virus scan be done for the attachment being stored in database !
-
- +0
-
-
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!
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!
-
- +0
-
-
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
-
- +0
-
-
Write comment
| < Prev |
|---|
Last Updated on Monday, 18 December 2006 10:11.
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