I was doing some work with file attachments in PeopleSoft over the past couple of weeks and I have to say PeopleSoft’s ability to handle attachments is flat out bad. However, it does work. The client I was working with found that storing documents in the database was starting to consume an extreme amount of space which made the database have issues.
In PeopleTools 8.54 there is a batch copy attachment feature that lets you move attachments from one URL to another. So you can define a URL for sftp and another for the database record and copy attachments to/from those URLs. In 8.53 there is just an online page for this, however, if you are doing a copy of more than about 1000 documents you will want to switch to a batch mode.
I was also fascinated with the orphan cleanup process that looks at the referencing record and the storage record and clears out any non-referenced storage record documents. PeopleSoft does this by looking for the FILE_ATTACH_SBR subrecord in the record definitions and generates a list of attached file names and then compares that to the records associated with FILE_ATTDET_SBR subrecord. This process cleared out about 25,000 documents that were not referenced.
When doing the actual copy from the database to the sftp (which was local), I did it in chunks by identifying the correct reference record and specifying that in the file record array for the copyattachment function. As I had one record that had almost 200,000 documents, which if you don’t specify the reference records it will look at all the records with the FILE_ATTACH_SBR subrecord which in the Finance 9.2 environment I am in was 108 records, however only 8 had references.
I also built an index for the record with the actual document which was unique on the fields: ATTACHSYSFILENAME, VERSION, FILE_SEQ, which is different then the delivered index thus allowing the look up to be much more efficient.