Interfacing SQL*LIMS v5 to a Global ERP


By Jeff Vannest, Senior Consultant

Most of this year has been spent designing and writing an interface between SQL*LIMS v5.1 and the new Enterprise Resource Planning (ERP) system being implemented by a global pharmaceutical manufacturing company. The goal was threefold: notify LIMS system administrators of changes made to materials, vendors, and manufactures; automatically create and update lots in the LIMS system; and send lot statuses and results from LIMS back to the ERP when testing was complete. This article will review the decision-making process used to plan and develop the interface.

History
My work with this particular company started in 2002 when they were planning to replace their aging global LIMS system with SQL*LIMS v4.1. Although no development work had yet started, the decision to use a particular custom solution for interfacing had already been made. The interface was intended to move SQL*LIMS template data from a global configuration center into subscribing LIMS site instances, and to move Manufacturing Resource Planning (MRP) data between an existing MRP system and the new LIMS.

Although the MRP portion of the interface was deployed to a few sites in the United States and Europe for both SQL*LIMS v4.1 and v5, it soon became clear that the highly customized nature of the interface was slow and too difficult to support. The entire system was single-threaded, which meant that all transactions – both inbound and outbound – had to be processed one at a time. And, data was being transferred using an obtuse and hard-coded format; it was impossible to introduce new data into the system without causing a significant amount of technical rework and software re-release.

Fresh Start
Soon after having completed the initial deployment of the first global LIMS instance in 2005, team members began hearing rumors that the company now wished to replace their aging MRP system. Although there were only two software packages that were finally considered for the new system, the company choose Oracle E-Business Suite (EBS). EBS is an incredibly robust system that would replace not only their original MRP system, but would implement new ERP software for financials, procurement, and several other aspects of running a global manufacturing company.

Therefore, the challenge given to me was how to interface SQL*LIMS v5 to an Oracle EBS? What data formats should be considered? What software should be used?

Considerations
Even during the interface planning stages, I was given several options for implementing the interface. First, the new ERP could connect directly to our existing interface software, which meant that data would come in and out of the LIMS exactly as it had been planned back in 2002. Second, we could use a new data format and have the data placed either on LIMS file system via FTP or into the LIMS database in a temporary table. Or third, we could choose to re-implement the interface using web services.

My very strong recommendation to this company was to use web services. Using this option meant that the LIMS system could take advantage of XML-based data exchange. This was very important to me because it meant that data being transferred between systems would be self-describing. No longer would changes in data definition break the interface or cause the re-release of interfacing components. Also, it meant that the interface could take advantage of the multi-threaded nature of a web server; transactions not requiring a first-in, first-out processing could be executed in parallel, which would significantly increase the speed of the entire system.

New Direction
SQL*LIMS v5 does not support web services, so whatever architecture was chosen had to extend the SQL*LIMS architecture in a manner that was consistent with the original application and would cause the least number of customizations to the vendor system. While many possible solutions were investigated, the following major components were chosen:

  • Web services would be written to send and receive data. Each data transaction would have its own web service endpoint and would be packaged separately so that future enhancements would be easy to implement. This allowed the solution to leverage the SQL*LIMS application tier by running the web services from the Oracle Application Server software, or from any production quality application server such as Oracle Weblogic, Sun Glassfish, etc.
  • Data would be transferred between the LIMS and ERP system using the Business To Manufacturing Markup Language (B2MML). These XML schemas are explicitly designed to implement the ANSI/ISA 95 family of standards, which help define the data that must move between ERP and manufacturing systems. By using open-standard XML schemas, the company was able to leverage the knowledge and experience of other manufacturing organizations and professionals all over the world.
  • An interface monitor would be added to run as a Windows service on the SQL*LIMS Business Logic Server (BLS), which is where the core of the SQL*LIMS monitors execute. The new monitor would process transaction data waiting in the queue table in the LIMS database, create lots, update lots, and send system notifications as required. Although adding a monitor introduced new custom code, it also allowed the solution to use existing architectural knowledge and training as a foundation for implementation.
  • A single Oracle form was added to the SQL*LIMS Utilities menu that allowed users to review inbound and outbound transactions and review transactions that had triggered errors. The form allowed the user to identify which transactions were legitimate errors, and which could be re-processed successfully.

Summary
I am often asked to make LIMS customizations. The first question I ask is, “Why?” Customizations can mean that a vendor product becomes unsupportable in the present, and may remove it vendor upgrade path in the future. However, with the correct experience and knowledge, changes to a vendor product can be made to augment, rather than modify, existing functionality. These are the best customizations; these are the customizations that show pay-off for the time I spent and for the customer who must now support the system for the next several years. The keys are to use industry-standard technologies and methodologies and to leverage existing architecture, experience, and training.

About the author: Jeff Vannest is a Senior Consultant at J&R Consulting, Inc, a consulting firm specializing in LIMS systems, and excels in technical design, development and implementation. Weekly articles on LIMS software and contact information can be found at http://www.jandrconsult.com.