Strategies to Integrate with PeopleSoft

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”:

• Pulling data from PeopleSoft
  • The level of effort here is relatively easy.
• Pushing/Posting/Updating data to PeopleSoft.
  • The level of effort here is high. This is where most of the time is spent on an integration project.

PeopleSoft Architecture Notes

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.

• PeopleTools is around 30 years old and has evolved over the years. It started in the world of client-server and self-hosting.
  • This predates many application concepts like SAAS, Cloud hosting, etc.
  • The core tenants of the architecture has not changed drastically. There is no longer a thick client on the user’s machine like in the old days. The user interface is in HTML and all the business logic is funneled through an application server.
  • The PeopleSoft application server is where most of the business logic is executed and interpreted.
• An extremely important thing to understand is PeopleTools was designed to be platform independent at both the OS level and the database level.
  • It was designed to run on almost any major database or operating system at that time. This lead to a fairly broad adoption and sales as the product could run inside any client’s infrastructure and “meet them where they were.”
  • This is less important today in the cloud world. SQL Server and Oracle database have pretty much won in the commercial world. Informix and DB2 are pretty much gone.
  • PeopleTools does not support PostgreSQL or MySQL nor does it support any cloud vendor SQL offerings from Google or AWS. Supporting open source databases would not be in Oracle’s corporate interest.
• Since the system was designed to run on multiple database platforms, the system cannot rely on anything that the database offers.
  • This is very important to understand as many applications are often designed for a specific database platform and the application logic ends up being very tightly coupled to functionality offered by the database.
  • PeopleSoft applications absolutely do NOT fall into this category. In Peoplesoft, there is no reliance on the database for:
    • User security
    • Table Security
    • Triggers
    • Stored Procedures
    • Primary and Foreign keys
• Then where is all of that code and logic stored?
  • PeopleSoft has its own proprietary “meta-data” tables referred to as “PeopleTools” database tables. The PeopleSoft application server knows how to read these and turn them into application code, user interfaces, and business logic. Everything if funneled through the application server.
    • User Accounts and Security are created and maintained in PeopleTools tables.
    • Reporting security like “what tables can Sally query from” are created and maintained in PeopleTools tables.
    • Database primary and foreign keys are created and maintained in PeopleTools tables.
    • Scheduling of Batch processes is handled in a built-in system.
    • Global and regional settings are stored in the database.
    • …and many other of aspects of a large system…

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.

Data GETs

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:

• What application connects to the back-end database?
• What tables can the client query from?
• Can the client execute any SQL they want against the database or is it limited in some way?
• How is the data moved across system boundaries?
• How is the connection secured?

Direct DB Connection

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:

• The system integrator delivers some sort of application (docker container, daemon, agent, service) that sits inside the network where PeopleSoft is hosted. That application will connect directly to the PeopleSoft Oracle or SQL Sever database.
  • This software will then have some proprietary API it exposes to the external system in the form of web services, file transfers or data pushes in some form to the integrator. I have seen several implementations of this.
• The external servers connect directly to the back-end database using standard DB Level accounts over the internet.
  • I try to steer people away from this route for many reasons including security, networking issues, driver issues, and other complexity. However, it is still very common.

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.

Flat File

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.

Web Services

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:

• Designing what data needs to be exchanged.
• How the data is encoded and structured.
• What parameters are required.
• Determining how to limit sets of data to the client (data security)
• Actually writing the PeopleCode.

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.

Data Updates

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:

• Have domain knowledge of the underlying user interface that needs to be updated
• Have an understanding of how the institution’s data and data security works because those are enforced in the UI.
• Have an understanding of component and component interface design and programming concepts.
• Have a good understanding of the business requirements.

The knowledge above is “hard won” and takes time.

In Summary:

• Data GET web services are fairly easy to create
• Data Updates web services are much harder and require more system knowledge