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
This section will discuss best practices in performing PeopleSoft updates.
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.
First, let’s have a quick review of the high-level architecture of PeopleSoft.
pageswhich 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.
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!
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.
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.
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: