Friday, May 23, 2008

Export PeopleSoft Attachments using PL/SQL

Oracle provides PeopleTools developers with the ability to store files with transactions using the File Attachment API. Likewise, the File Attachment API includes PeopleCode functions for extracting and displaying attachments. What if you need to export attachments directly from a database or from SQR where you don't have access to the File Attachment PeopleCode functions? Here is some PL/SQL demonstrating how to export attachments. You just need to set the file name and the attachment record name. I labeled the values you need to change with TODO:. Code for other databases should be relatively similar.

CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'c:\temp'
/

DECLARE
-- Max PS attachment chunk size is 28000
CV_BUFFER_MAX NUMBER := 28000;

-- Name of file to export. This is the name that was used to store the file
-- and is the name that will be used to create a new file.
-- TODO: change the name to match the name of your exported file
lv_file_name VARCHAR2(128) := 'theattachedfile.xls';
lv_buffer RAW(28000);

lv_file_ref utl_file.file_type;

BEGIN
lv_file_ref := utl_file.fopen('TEMP_DIR', lv_file_name, 'WB');

FOR r_chunks IN
(SELECT FILE_DATA
, FILE_SIZE
-- TODO: Change record to the name of your attachment record
FROM PSFILE_ATTDET
WHERE ATTACHSYSFILENAME = lv_file_name) LOOP

dbms_lob.read(r_chunks.FILE_DATA, r_chunks.FILE_SIZE, 1, lv_buffer);
utl_file.put_raw(lv_file_ref, lv_buffer, true);
END LOOP;

utl_file.fclose(lv_file_ref);

EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(lv_file_ref) THEN
UTL_FILE.fclose(lv_file_ref);
END IF;
RAISE;
END;
/

You will notice that this code exports an attachment to the database server's file system.

How do you find file attachment records? The easiest way I can think of is to query the PSRECFIELD table for all record definitions that contain the FILE_ATTDET_SBR sub record. Here is the SQL:

SELECT *
FROM PSRECFIELD
WHERE FIELDNAME = 'FILE_ATTDET_SBR'

17 comments:

Julian said...

Thanks, but there is actually a limitation for UTL_FILE put to a size of 32K only, so it wont work for bigger attachments

Greg said...

Jim,
I attempted to find your direct email but it’s not in your blog.

I hope you can help me. Like you, I am a certified public accountant and software developer. However, I develop solutions for Microsoft Business Solutions Dynamics GP and Intuit’s QuickBooks. I am also a local community activist in my hometown city, Detroit, MI.

Some community organizations have asked me to conduct a ‘community audit’ of the procurement transactions of the Detroit Public Schools (DPS) for a given fiscal year. The DPS runs their accounting activity on a current version of PeopleSoft. I submitted a request for electronic data (csv format) under Michigan’s Freedom of Information Act where I requested transactional level data elements including check number, check amount, check date, invoice number, vendor name, vendor ID, purchase order number, amount, etc.., for the fiscal year in question.

The DPS denied my request on the grounds that they cannot provide this information because they don’t have a report in the format that I have requested.

I contend that they have the ability to easily comply with this request by simply using the IT tools available through PeopleSoft and Oracle. Am I correct or am I missing something?

A few basic questions:

1. Is the PeopleSoft database Oracle? If so, can this information be extracted creating a SQL query? If so, would that take a ton of man hours?
2. Can the information in my request be provided through the PeopleSoft API? If so, how?

I thank you for any light you can shed on this matter, Jim.

Feel free to contact me regarding this email.

Greg Frazier, CPA
gregfrazier@gfcpa.com
(313) 931-0522

Greg said...

I forgot, I'd appreciate any follow-up comments regarding the previous posting.

Greg

Jim Marion said...

@Greg, you are exactly right. Yes, the government agency in question has the ability to provide you with the requested data. I used to work for a government agency that used PeopleSoft. We regularly satisfied these public records requests. A government agency using PeopleSoft can either use standard SQL database tools or PeopleSoft's PS Query tool. Both of these tools provide SQL access to the data in the PeopleSoft database. Likewise, PeopleSoft includes an ODBC driver that provides database independent, secure access to the PeopleSoft database. PeopleSoft runs on several SQL compliant databases including Oracle, DB2, and Microsoft SQL Server. The ability to extract data from a PeopleSoft database is not an issue. Please post back if you have any other questions.

Greg said...

Thanks Jim, you're timely response is appreciated. If it's okay, I'll keep you and your readership informed of current developments.

We have already issued a lawsuit and will go to trial if necessary.

This is a clear case of the DPS using the ignorance of the legal system (judges, jury and lawyers) as a way to prevent taxpayers from having access to information that is rightfully the property of citizens.

Jim Marion said...

Thanks Greg. Getting data out of PeopleSoft is not the issue.

As I said, I used to work for a government agency. I must admit, I really hated those public information requests for salaries. There is nothing like having your salary posted on the internet so all your friends and family can see how much you make.

Greg said...

We're on the same page, Jim. We're not requesting salary information. I appreciate the sensitvity of that disclosure. Our objective is vendor related financial transactions, and determining the extent to which proper accounting and budgetary controls are enforced, e.g. checks matched to approved invoices, invoices matched to approved purchase orders, purchase orders matched to approved requisitions, etc...

We want to obtain a quantitative picture of the extent to which weak accounting and budgetary controls contribute to consistent fiscal deficits, and determine if the raw data is evidence of weak controls.

Greg said...

Jim,

Is there a report in PeopleSoft's Accounts Payable Module that provides both invoice and payment detail? Specifically, the following detailed information per transaction:

For Invoice Line Items:
- Invoice Number
- Invoice Date
- Invoice Amount
- PO Number
- Vendor ID

For Payment Line Items:
- Check Number
- Check Date
- Check Amount
- Invoice Number check was applied

If, so, what are the parameters to run such a report? Can a date range be specified?

If not, what combination of reports would provide this information so that if the reports are exported to text files a database of this detail could be recreated?

Thanks Jim.

Greg

Jim Marion said...

@Greg, for delivered reports, I have no idea. Does the data exist? Absolutely. And, since it exists, it can be extracted and reported just like any other PeopleSoft data.

Manoj said...

Guess I'm bringing something that's little related to this thread.

We are on PeopleTools 8.50 and just found out that Attachment functionality doesn't work on Modal (popup secondary pages). So we are searching for ways of accomplishing this using Javascript (or other means).

Do you know how the attachment functionality works in PeopleSoft (localfile to database record) and if its possible to bring files into PeopleSoft records without usng the AddAttachment method.

Jim Marion said...

@Manoj,

AddAttachment is the only delivered way, and is the way I recommend. If you need to use attachments in a modal dialog, then you may have to resort to a JSP/Servlet type of attachment. With this approach, you will write JavaScript/HTML to prompt for the attachment, and then you will create a server side handler in JSP/Java or whatever language you prefer. The server side file handler will be responsible for moving the file to the appropriate destination.

I know I've heard discussions about the behavior you are seeing. You might check with Global Support if there is a patch that resolves this or if it is resolved in a future release.

Manoj said...

@Jim, Thanks. In fact, there is a BUG (BUG 11631000) raised for this with GSC. But it claims that Modal windows was not designed for AddAttachments.

They have enhanced this on 8.52, but we can't upgrade to 8.52 just for this.

I am also wondering, if the fix(1992365000) they gave for 8.52 can be used for 8.50, but then GSC would have done it already, if it were easy, isnt? I dont know how GSC works, have got some pretty ordinary and irresponsible support from them at times

Jim Marion said...

@Manoj, you have the same information as me, I just wasn't sure how public it was, that is why I had to send you to Global Support for the information ;).

I understand what you are saying about support. When you get a "less than desirable" answer from support, I highly recommend escalating the case, asking for transfers, etc. If you believe you weren't treated appropriately, then don't take "no" for an answer. I used to be a customer, so I understand your situation.

In regards to AddAttachment, there really isn't much on the PeopleCode/App Designer side that we can fix. File processing is pretty much handled on the web server and C++ app server code. This makes it a bit more difficult to pull out and patch. Nevertheless, you can always ask for a POC for your tools release. If they fixed this behavior in a later release, then they must have felt it was important.

Manoj said...

Spoke to GSC and they have no intention of fixing the bug in 8.50/.51. They say that they don't have a strong business case, as I am the only 2nd customer who raised this issue. They also said it was too complex to rewrite it in the lesser tools version. Meh!

On an alternate line of thinking, do you think we can do something else. For instance, reading the the file into a Java Object and loading the attachment table into chunks. Though, I have no idea how to read a local file into a JavaObject.

Jim Marion said...

@Manoj, yes, you can do something different. You don't need to use the file attachment API. Without the API, it will require more code. What you would do is write some HTML/JavaScript to prompt for the file to uplad, and then create a JSP/servlet to process the upload. This is how non-PeopleSoft applications handle file uploads.

About reading local files... this depends on what you do with the file after upload. Prior to upload, the file won't be local. It will be remote, on the client workstation. After the JSP/servlet processes the file, it will be local to the web server, but not the app server (unless they are on the same instance). PeopleCode runs on the app server, so this may be of no value. To get the file into the appropriate location and update the PeopleSoft meta-data accordingly, you will likely have to use JDBC to update the file attachment Meta-data and to import the data.

PeopleSoft database file attachments are chunked into rows. The chunk size is the PeopleTools Max Attachment Size (PeopleTools > Utilities > Administration > PeopleTools Options).

Another option is to stage the file on a process scheduler server using FTP or some other file transfer mechanism (from Java) and then use an App Engine with PutAttachment to store the file in the target location.

You don't have to use JSP or servlets. I mention them because they run within the PeopleSoft servlet container. Since everything is happening outside PeopleSoft at this point, you could just as easily use any other web server and web server language.

Nav Singh said...

Hi Jim,

We are on PT 8.45 & CRM 8.80. And mobile agent is being used for the FieldService.
Currently we are facing issue with the attachment while synchronizing mobile agent with online application. We are using PutAttachment function for transferring the file to the file server.

Below is the scenario:
Technician attaches the invoice, as a html file, to the ServiceOrder component on mobile.
He synchronize the ServiceOrder back to online application, and the invoice html file is uploaded to file server.
But, if two technicians synchronize the mobile agent at the same time then the invoice files gets mixedup.
For example:- Technician A tries to sync ServiceOrder 1, with invoice file name SO1.html. At the same time technician B tries to sync ServiceOrder 2, with invoice file name SO2.html.
Now what happens is the content of file SO1.html is replaced by SO2.html on file server, although the file name & link to FTP file doesn't change.

My doubt is that, for every file transferred from the WebServer/syncServer to AppServer is created as PSSYNC0.blob(as seen in the AppServer log). And before this blob file is transferred to FileServer it is replaced by the next file in the queue.
So my question is can we change the name of PSSYNC0.blob to some unique name for every file? Or do we have any other work around for this?

Posting the AppServer log in next post, as I can't do it here due to long post

Best regards,
Nav

Jim Marion said...

@Nav, I am not familiar with this behavior. You can try opening a case with support, but 8.45 is quite old. You can also try posting your question on the PeopleSoft General Discussion OTN Forum