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 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 for GET operations. In fact, CI gets in the way for GET operations.

Understanding PeopleSoft Architecture

First, let’s have a quick review of the high-level architecture of PeopleSoft.

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

The PeopleTools “component processor” in the application server handles all direct interaction with a database tables for the PeopleSoft user interfaces (aka Components). All SQL inserts, updates, and deletes are all 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 is creating a component, and they are doing this they are doing something “against the grain” of PeopleTools, but that is a whole other 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 by-pass the component, you bypass the business logic.

Updating PeopleSoft Tables

You are reading this book because you are interested in integrations with PeopleSoft. If you have some external system that needs to update a PeopleSoft table what should you do? 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) which takes a Component and wraps it and creates an API out of 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.

If you have a piece of PeopleCode that needs to perform some table update you should generally be doing that through a CI. DO NOT WRITE UDPATE/DELETE SQL STATEMENT IN PEOPLESOFT!

Your Service Operation PeopleCode handler should be doing all updates/delete/inserts through a CI. Specifically, the CI will be doing all those updates for you. Your PeopleCode will be interacting 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 really depends on the nature of the web service. Often times your web service might be pulling 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 were using CI rather than SQL.

However, there are some case where CI on a GET might make sense. These are generally around self-service components that you are exposing to an external system. 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