Best Practices with PeopleSoft Table Updates and Inserts

This section will discuss best practices in performing PeopleSoft updates.

TLDR

• 99% of the time, all updates/inserts/deletes on PeopleSoft tables should be done through a Component Interface (CI).

  • If you find yourself writing SQL like INSERT INTO PS_.... or UPDATE PS_...., you are probably doing something “wrong.”

• If your web service is just pulling data out of PeopleSoft (GET Operation), then doing SQL selects against the database table is fine. CI does not provide any real benefit to GET operations. In fact, CI gets in the way of GET operations.

Understanding PeopleSoft Architecture

First, let’s briefly review the high-level architecture of PeopleSoft.

• PeopleSoft is a rapid application development platform if you are using it correctly. If you operate outside of the “rails” of PeopleTools, you can create a mess that is difficult to maintain and upgrade. Operating within the “rails” of PeopleTools means you let the PeopleTools framework do the heavy lifting for you. This is the key to a successful PeopleSoft implementation.

• When an end user interacts with some user interface in PeopleSoft, they interact with a PeopleTools Component.

  • A Component is responsible for maintaining parent-child relationships with tables it “governs”.
  • A Component encapsulates all the business logic for those tables.
  • A Component maintains data integrity by enforcing prompt table and other logic.
  • A Component maintains effective dated logic.
  • A Component triggers data syncs to other systems (PERSON_BASIC_SYNC, DEPT_SYNC, etc).
  • A Component triggers inter-module updates. A Component - For example, if a person updates the state in which they live, their state payroll tax data may be updated.
  • A Component triggers unknown lines of PeopleCode behind the scenes. This could be 2 lines or millions of lines of PeopleCode.
  • A Component has underlying logic that changes via PUMs and customizations.
  • A Component maps to a set of pages that dictate the user interface in the web browser.

• The PeopleTools “component processor” in the application server handles all direct interaction with the database tables for the PeopleSoft user interfaces (aka Components).

  • All SQL inserts, updates, and deletes are handled inside that business logic.
  • PeopleSoft developers creating user interfaces generally never have to write SQL that performs direct updates/inserts/deletes. If a developer creates a component and writes SQL INSERT/DELETES/UPDATES in PeopleCode, they are doing something “against the grain” of PeopleTools, but that is a whole book I have not written yet.

The main point I want to make before we continue here is that all the business logic for a table is tied to some PeopleSoft Component. If you bypass the component, you bypass the business logic. CI is about reusing that business logic and not repeating and recreating logic.

I have seen many clients create a complete mess and have batch processes “ramming” data into PeopleSoft tables. This is a recipe for drastically increasing the total cost and complexity of operating. It makes upgrades difficult and patching extremely difficult and time-consuming. The testing process for bundles turns into a major affair. In contrast, I have integrations written against core components that have been running for ten years without a single change across many clients. Those clients have upgraded and applied quarterly patches to the underlying components, and there were no impacts on my integration code or any testing burden. This is because the basic structure of the component has not changed. There may have been underlying logic changes in the Component PeopleCode. However, users of the CI did not have to change their code.

Generally, using CI will make your integrations more robust and easier to maintain.

Updating PeopleSoft Tables

You are reading this book because you are interested in integrations with PeopleSoft. What should you do if you have an external system that needs to update a PeopleSoft table? The section above shows the importance of a PeopleSoft Component. That component has all the business logic, and you do NOT want to recreate that or bypass it.

PeopleTools allows developers to create a Component Interface (CI) that takes a Component and creates an API based on it. The Component Interface definition is tied to one Component and changing the component can break or change the Component Interface. The API created can be accessed using PeopleCode. Not all changes to the underlying component will break the CI, but some will. Only changes to the underlying search record or table structure will break the CI. If you change the underlying PeopleCode or add fields, the CI will not break.

If you have a piece of PeopleCode that needs to perform some table update, you should generally pushing that through a CI. DO NOT WRITE UPDATE/DELETE SQL STATEMENTS IN PEOPLESOFT!

Your Service Operation PeopleCode handler should be performing all updates/deletes/inserts through a CI. Specifically, the CI will be doing all those updates for you. Your PeopleCode will interact with the CI API structure, which will closely resemble the underlying structure of the component.

Here is a high-level diagram of what that looks like.

When is CI NOT appropriate or overkill?

If your web service is creating a custom “staging” table that the web service inserts data into and there is no PeopleSoft UI logic then that is a use case where CI does not make sense. CI’s are used for triggering business logic that is not within your control. If you are mirroring some data from an external system and PeopleSoft does not have internal logic on that data then CI does NOT make sense for that use case.

Should I use CI for GETs

If you are writing a “GET” operation from PeopleSoft, it is OK to do SQL SELECTs directly against the database most of the time. This depends on the nature of the web service. Oftentimes, your web service might pull system information for another service. If you try to use CI, the amount of code to generate that data would be at least an order of magnitude more if you used CI rather than SQL.

There are some edge-cases where using CI on a GET might make sense. I often see them around self-service components with complex business logic, calculations, and complex data aggregations. If the external system is passing some “user context” like “get me the Sally’s student balance at the university,” using a CI for that could make sense because:

• The CI has all the built-in data security
• There is likely business logic behind filtering and determining a student’s balance (future due, past due, etc.)
• You are returning limited fields.

Further Reading on Component Interface

• Component Interface Training for Developers
• Component Interface KB Articles from Cedar Hills Group
  • Should You Expose a Component Interface as a Web Service
  • Criteria to Use Component Interface
  • Should I Re-use An Existing Component Interface (CI) Definition Or Create A New One?
  • PeopleCode Events that do not fire in Component Interface
  • Limitations of PeopleTools Component Interfaces and Web Services
  • Signs you should be using a Component Interface
  • 10 Step Component Interface Development Process