|
Attaching Supporting Documentation to Journals |
|
|
Monday, 18 December 2006 |
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:
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:
- 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.
- 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. 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.
- 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 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.
|
|
Last Updated ( Monday, 18 December 2006 )
|
|
|
Change Control and PeopleSoft Development |
|
|
Wednesday, 05 April 2006 |
If your company is big enough to afford PeopleSoft, chances are it's big enough to worry about IT audits -- whether they are Sarbanes-Oxley related or your company just likes to maintain a high level of quality in their development processes.
Here are some common aspects of PeopleSoft development Change Control processes that I have encountered:
Separation of Duties
While it would be convenient, developers can't migrate their own projects. I normally find developers who write the code, and a DBA or PeopleSoft Administrator who migrates the changes upon request. PeopleSoft security is set up in such a way that developers can't migrate, or make any code changes outside of a development and/or sandbox environment.
Tracking Changes
Auditors often pick a project out of development, and want to see how it progressed from development, through testing, and finally to Production. Or they'll pick a modified object in Production and want to see the entire documentation trail as to how it made it there. Either way, good documentation is essential.
Documentation starts with the original user request. Usually enhancement requests or break/fix can be tied back to an issues database, help desk application, or other central repository. Depending on the complexity, functional and technical documents might be prepared and approved. At some point, a PeopleSoft project is created with a specific naming convention and this project is tied back to the original user request. Then the developer makes his changes and places all of the objects in the project. I've often seen this project name made part of custom SQR and Crystal filenames to make them easier to track.
When the developer is done with unit testing in the development environment (which itself may involve a unit test document), some type of migration request is prepared and sent to the PS Administrator or DBA. At a minimum the migration request will contain the source and target environments, the project name, and filenames of all batch and flat file objects. After making sure everything is in order, the admin does the migration and notes on the migration request who moved the project and when it was moved.
Once the project is tested, there is usually some type of user acceptance that indicates from the business end user that the change fixes the problem. Also in this phase I've seen test plans and sign-offs from other interested parties.
Finally the time comes for the move to production. This certainly involves another migration request, and usually involves some type of Production System change notification document to notify interested parties. Usually production change notifications require some sort of review by a controlling board who will always ask what it does, who approved it, what's the risk, and what's the rollback plan. Once everything is approved, the admin actually moves the project to production and updates all relevant documentation.
The Devil is in the Details
Sounds pretty straightforward, right? Well, it can be. But your process needs to be very clear and everyone must understand them. And the best processes allow for flexibility when timelines are tight or a quick fix is critical.
Unfortunately, PeopleSoft doesn't manage change control very well. You can work around this by using a product like Quest Stat.
If you'd rather not spend the money, you'll need to 1) Convince your auditor that PeopleSoft doesn't do change control but that's OK; or 2) export your projects to flat files and (optionally) check them into a VSS solution.
The trick to convincing your auditors that change control is unnecessary is to have another environment where you can migrate previous versions backwards. For instance, if you mess something up in Development it's Ok because you can pull it from test. Or if a change messes up test, you can pull it from Production. It generally works well enough until the change goes into Production. Then you end up talking about database restores or pulling unmodified code from a reporting or staging instance.
Exporting projects to a flat file and optionally using a source control system seems like it would work, but to tell the truth I've never gone this route. If you have I'd like to hear from you. It doesn't seem like a natural fit for either the developer or the migrator, although the auditor would probably love it. I understand that PeopleSoft used this approach with some success.
Conclusion
Tracking changes like this is probably my least favorite part of the job. But that's why I wanted to post about it. Maybe if we work together we can come up with some best practices that won't add too much overhead to the development process, but still insure the quality that our stakeholders expect.
|
|
Last Updated ( Wednesday, 05 April 2006 )
|
|
|
