Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.Read More & Purchase
Are you a software vendor or application developer that is trying to understand or form a strategy on how to integrate with PeopleSoft? This section is going to give you a 10,000-foot picture of the different ways to integrate with PeopleSoft. This book is written for the PeopleSoft “Integration Broker.” Therefore, we will favor the web service method in this section.
There are two integration “directions”:
This is a long article. A few different terms that I use:
First let’s discuss the high-level architecture of PeopleSoft which is important to understand when trying to integrate with PeopleSoft.
PeopleSoft applications like Campus Solutions, Human Resources and Finance are all written with a proprietary language/IDE called “PeopleTools”. PeopleTools has some unique properties that are not present in many other development environments. For someone trying to integrate with PeopleSoft there are a few key points that may help you understand at a high-level how things are structured.
We are going to start with the easy path first which is pulling data from PeopleSoft. This would be in the form of a “GET” in REST Terminology. At a very high-level, this involves running some SQL against the database and returning it encoded in some form to the client. There are a few key questions here:
I have worked with many software vendors and system integrators trying to extract data from PeopleSoft. I have seen several models in how this is implemented.
One popular model I see when the software vendor does not have any PeopleSoft expertise is a direct database connection. This model is often taken because the vendor may connect to many types of systems so hitting the database directly reduces their complexity as each ERP or SAAS system has its own quirks and hitting the database is going to the lowest common denominator.
There are two popular models:
In both of these models, the external system is connecting directly to the back-end database and all PeopleSoft functionality is completely bypassed. Bypassing the PeopleSoft logic for GETs is not always an issue. That is assuming that the external system knows how PeopleSoft data is structured and what tables to join from. I often see external systems stumble over PeopleSoft effective dates. I often see the PeopleSoft team create database views that hide the complexity of PeopleSoft or that limit data based on institutional needs. Then the external client can only read from those views at the database level for security reasons.
Of course, a PeopleSoft developer could write an “extract” using a batch process and push it to some location. I have some notes why I don’t always like that way in the section why use web services. However, there are still many systems and banks that require flat files and that is not going away any time soon.
Now let’s bring in web services into the mix.
In order to GET data from PeopleSoft, you might have a PeopleSoft developer create a “Service Operation” (aka Web Service) that the external client can call over HTTPS. There are many examples in this book on how to create those. There are pieces of code and objects that end up getting created in the PeopleSoft database. In the end, a web service in PeopleSoft is just a way to call some application logic written in PeopleCode which pulls some data out of the system and returns it to the client. There is a bunch of “plumbing” to setup and secure that PeopleCode. In the end, the work is encapsulated in the PeopleCode. That PeopleCode logic will run on the application server and all connections to the database are proxied through the application server. Most of the development time is spent on:
I have countless web services running in client production systems. Many of these were bespoke web services written for very specific use cases and very specific client applications or software systems. I also have written generic frameworks like my PeopleSoft Simple Web Services which is very reusable if you stay within the design assumptions. That allows you to create infinite web services with only configuration and no code changes. Similarly, I also license a generic web service that functions very similar to GraphQL but for PeopleSoft which a few system integrators I consult for use in their integrations, but I don’t actively market. PeopleTools also has several delivered options which I have documented in the Services Delivered by Oracle section. The most notable there is a way to expose PeopleSoft Query Manager queries as web services.
There is a trade-off between creating specific single use web services and generic web services that allow for heavy re-use. The development time for generic web services are often much higher and required more technical expertise and system mastery. The use-case specific web services are often quicker to create and deploy but are often more rigid. I tried to summarize those ranking categories in this radar chart.
If you are optimizing for code re-use then a generic approach is optimal. However, if the code is for a very specific use case, then it is often better to deploy a custom solution as it will be easier to develop.
Now let’s transition to the more complex web service category: Updating data in PeopleSoft. In the REST terminology, this would be POST, PUT, and DELETE. These are much harder to create for reasons we will explain in this section.
I wrote a section in the Best Practices chapter titled Best Practices with PeopleSoft Table Updates and Inserts which really sums up many of the points that need to be covered here. I suggest you read that and then return here.
The main points of that section are that for PeopleSoft, you should never attempt to insert, delete or update data directly in the database with direct SQL. As a general rule, those should all be funneled through a component interface (CI). That means external clients should not have any database rights to update tables. It also means that any PeopleCode for custom-built web services should also not be doing straight SQL updates and deletes. Of course there are exceptions to this (See the additional reading sections in the linked article above.)
The diagram in that section is very high-level and does not totally agree with the ones in this section. Let’s redraw that here to show what the structure actually is for updates to PeopleSoft.
Writing web services that call Component Interfaces is NOT rocket science. However, it is not always straight forward either. The developer is constrained by what the CI offers and how the underlying application user interface is structured. It really depends on the use case of the actual web service and what data needs to be updated. I actually have written web services that are required to work with several CI’s in one web service call. That would look like the following diagram.
This is where it can get tricky because each of those CIs represent a database transaction and commit. You could easily run into a situation where the client sent in some bad data that you do not detect until the last CI and there is no way to roll back those previous CI database commits because it is NOT atomic. The updates are NOT always fully committed in this situation, and you may need to send back partial success status codes. It can get ugly. I try NOT to design services like this. However, there are times when the calling system is already designed and inflexible, and you have to work within the boundaries given.
The developer designing and creating these PeopleSoft web services must have a good functional understanding of the underlying user interface and system configuration to write code that updates CIs. The object model for a CI is directly related to the underlying user interface. If the developer struggles to understand the underlying user interface they will struggle to write the web service.
To be effective in writing a robust update web services the developer must: