Author Info
Chris Malek

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
Contents
Do you need a quick way to create web services?
PeopleSoft Simple Web Services (SWS)

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

Strategies in Integrating 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 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”:

  • 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.

Terminology

This is a long article. A few different terms that I use:

Client
This is the application that is making a request to PeopleSoft.
Web Service
This is synonymous to a PeopleSoft “Service Operation”. It generally exposed some logic written in PeopleCode to an external client.
Application Server
PeopleSoft has an application server that runs on servers. This is a proprietary piece of software that does most of the heavy lifting of application logic in PeopleSoft. There is a web server involved but it does not actually execute and create the HTML sent to the clients.

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. There is no reliance on the database for:
    • User security
    • Table Security
    • Triggers
    • Stored Procedures
    • Primary and Foreign keys
  • 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.
  • 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 of aspects of a large system…
File File Schematic

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 or file transfers. I have seen several implementations of this.
  • The external servers makes a direct connection 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, and complexity. However, it is still very common.
Direct DB Connect Schematic

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.

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.

File File Schematic

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

  • 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.
Web Service Schematic

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.

Generic Versus Specific

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.

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 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.

Generic Versus Specific

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.

Generic Versus Specific

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:

  • Have domain knowledge of the underlying user interface that needs to be updated
  • Have an understanding of how the institutions data and data security works
  • Have an understanding of component and componet interface design and programming concepts
  • Have a good understanding of the business requirements