Similar to a Certificate of Analysis report used for manufactured product, the Veterinary Biologics Production and Test Report is used to submit biologics data to the United States Department of Agriculture Animal and Plant Health Inspection Service (APHIS) after study completion but before product release. Unlike a pharmaceutical Certificate of Analysis report that can be generated directly out of the LIMS system, a blank copy of the APHIS 2008 report must be downloaded from the USDA website and comes in the form of a Adobe PDF form template. Into this PDF file laboratory personnel must manually enter results into each of the boxes on the form using Adobe Acrobat. While this provides data to APHIS in a consistent format, manual completion of each PDF file is very time consuming.

In this article, I will describe a solution that can be used to automatically generate completed APHIS 2008 PDF forms directly from the SQL*LIMS v5 software.

Standard SQL*LIMS Reporting
My previous article, “XML Reporting in SQL*LIMS”, explains how reports work in the core SQL*LIMS product. In brief, a report is requested from the SQL*LIMS client, sent to the Oracle Reports server in the Oracle AS software, and the Oracle Report output is sent to the appropriate destination, the screen, a printer, a file, etc.

Design Decisions
During design, several issues needed to be address by this solution:
1. How to extract report data from the database
2. How to get the data into the federally-provided APHIS 2008 PDF form.
3. What to do with the completed form.

Selecting Data
Like the previous XML reporting solution, all data was selected using a PL/SQL package. As before, this offered a greater range of algorithmic possibilities than provided in Oracle Reports, and placed all logic into a single location that was more easily maintained than an Oracle Report.

Using the data provided by the PL/SQL data extract package, this solution required that the Oracle Report output data in the Adobe XFDF format. Similar to XML, this output format was easily reproducible in Oracle Reports, and allowed for the solution to remain consistent with the standard report execution method provided in the core SQL*LIMS software, the Run Events form, or automatic execution via status action.

Placing XFDF data on the PDF
Because data formatted to the Adobe XFDF specification can be manually imported into an Adobe PDF form, the next issue to be addressed was how to cause this data importation to be automatically triggered. Fortunately, the iText java library provided the perfect solution for programmatically inserting XFDF data into an existing PDF form.

The iText library was used to create and implement a new Pluggable Destination in the Oracle AS server. This allowed the server to receive the XFDF data created by the Oracle Reports client, import the data into the APHIS PDF file using the iText java library, and save the completed PDF file to a user-selected folder on the server. Each file name is made unique by lot and specification name, as well as site location, and a report creation timestamp.

Pluggable Destinations in SQL*LIMS
As before, a simple tweak to the EVENT_DISTRIBUTION_CLASS system parameter in SQL*LIMS allowed for events to be configured to the “PdfForm” destination. This modification to the software only allows the event to be executed in server mode, but this is actually a limitation intrinsic to the Oracle Forms software, which does not yet support Oracle Reports Pluggable Destinations in its internal APIs.

Summary
As with any good technical solution, all of the technology in this solution is transparent to the SQL*LIMS user: The Veterinary Biologics event is executed using the Run Events form and a PDF file containing the requested LIMS data appears in a shared folder within seconds. And, while the solution was implemented specifically for the APHIS 2008 Veterinary Biologics report, the Pluggable Destination that was created to augment the Oracle AS server was created to be generic enough to work with future reports that require PDF form templates.