Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.About Chris Work with Chris
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 how to think about integrating 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”:
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.
The key take-away from this section is that application logic is interpreted inside a proprietary application server. Generally, interfacing directly with the database is going to cause problems in large and subtle ways. This mostly manifests around updates or inserts into PeopleSoft as we will see in later sections.
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, SETIDs, and XLATs. 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 set up 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 integration. PeopleTools 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 require more technical expertise and system mastery. The use-case specific web services require less time 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. I have developed both and the approach depends on what you are trying 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 permissions 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 have created web services that are required to work with several CI’s in one web service call. That would look like the following diagram.
This multi-CI update model 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 to all CIs 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.
A real world example, where I had to implement this multi-CI-update-for-one-web-service model was in an integration with a third party system into Campus Solutions. The external system had information about a new student that needed to be matriculated at the University. In order to fully matriculate a student, there are several components that must be updated: person data, residency data, career data, term activation data, and often several more. The external system want to post in all data with one web service POST and have the student eligible to enroll in classes.
The developer designing and creating these PeopleSoft web services that interact with CI’s 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:
The knowledge above is “hard won” and takes time.