Getting data out of PeopleSoft

Question

We have a PeopleSoft installation and I am building a separate web application that needs to pull data from the PeopleSoft database. The web application will be on a different server than PeopleSoft, but the same internal network.

What are my options?

Answer 1

This one's an oldie but it may still be of interest.

PeopleSoft has it's own schema within the host database (Oracle, SQL Server, DB2 etc) which are the PSxxx tables, eg: PSRECDEFN is the equivalent of Oracle's DBA_TABLES. These tables should not be touched by any external code. The application tables are stored in PS_xxx tables, eg: PS_JOB. These tables can be read and updated by any SQL code.

Many batch programs in PeopleSoft (eg: Application Engines, COBOL or SQRs) access the tables directly, and this is the fastest way to get data into or out of the database. However PeopleSoft has quite a rich application layer which is bypassed when doing direct SQL. This application layer must be replicated in direct SQL code, especially for inserts or updates. There may be updates to other tables, calculations or increments of database-stored counters.

To determine how to do this one must look through the PeopleCode (a VB6-like interpreted language), page design (via Application Designer) and use the PeopleCode and SQL trace tools. These days the application layer is huge, so this can be a lengthy task for non-trivial pages. PeopleSoft groups related pages into "Components", and all pages in the component are saved at the same time.

Component Interfaces were introduced with PeopleTools 8 as a means to avoid doing all of this. Using a generator within the PeopleSoft app designer, a Component Interface is generated based on the component. For many components these can be used to access the pages as a user would, and can be accessed via PeopleCode programs, and therefore via App Engine programs and via the Integration Broker. They can also be wrapped in Java code and access directly by code able to execute against the app server with a web service wrapper. This method is best for low-volume transactions: heavy extracts work better with native SQL.

The online development and tracing tools in PeopleSoft are pretty good, and the documentation is excellent (although quite extensive) and available on: http://download.oracle.com/docs/cd/E17566_01/epm91pbr0/eng/psbooks/psft_homepage.htm

If you are just looking at bringing out data from a given Component, the easiest way would be to turn on the SQL trace (under the utilities menu in PeopleSoft) and bring up some records for the Component. Wading through the trace file will give you a good idea of what to do, and much of the SQL could be cut and pasted. Another method would be to find an existing report that is similiar to what you are trying to do and cut out the SQL.

Have a PeopleSoft business analyst on hand to help you develop the requirements wouldn't hurt either.

Answer 2

Yes - Integration Broker is Peoplesoft's proprietary implementation of a publish/subscribe mechanism, speaking xml. You could of course just write code that goes against your database using JDBC or OLE/ODBC. Nothing keeps you from doing this. However, you must understand the Peoplesoft database schema, so that you are pulling from, or inserting/updating/deleting all of the proper data. Peoplesoft takes care of this for you.

Also, check out Component Interfaces - and they are exposed as an API to Java or C/C++.