PeopleSoft HCM Tool

Reads HR data from an Oracle PeopleSoft HCM database over JDBC.

Tool ID

peoplesoft-hcm

Why SQL and not REST

PeopleSoft ships no delivered synchronous REST API for reading worker or job data. Everything Oracle delivers pre-built for HR data (PERSON_BASIC_SYNC, WORKFORCE_SYNC, DEPT_SYNC and their _FULLSYNC partners) is asynchronous publish/subscribe — PeopleSoft pushes it to a subscribing node on change, and an external system cannot invoke it to pull data on demand.

The generic PeopleTools alternatives both require per-site work: Query Access Services REST needs a PS Query authored and the PTPT2200 permission list granted, and Component-Interface REST services must be generated per CI (and replay the whole component buffer per row, which rules them out for bulk extract).

Direct read-only SQL is the path that works on every PeopleSoft version without site-specific setup — and it is what Oracle's own Access Governance connector does, using a least-privilege read-only account against purpose-built views.

Not to be confused with Oracle Fusion Cloud HCM. Fusion is a different product and does ship native REST APIs (/hcmRestApi/resources/.../workers). Those endpoints do not exist in PeopleSoft.

What this adds over the generic sql tool

Effective-dating. PeopleSoft data is a history, not a state, and "the current row" has to be derived on every read. This tool ships that derivation so you don't hand-write it:

• Max EFFDT bounded by the as-of date, so future-dated rows stay out. Scheduled promotions and terminations sit in PS_JOB with a future EFFDT; leaking one is a confidentiality incident, not just a data bug.
• Max EFFSEQ within that date, via its own correlated subquery. PeopleSoft allows several rows for the same person on the same day (a promotion and a pay change both effective today). MAX(EFFDT) alone returns all of them and silently inflates the row count — the classic PeopleSoft reporting bug.
• Correlation on EMPLID + EMPL_RCD, because concurrent jobs are normal. Note EMPL_RCD carries no ranking semantics: EMPL_RCD = 0 is not guaranteed to be the primary job.
• SETID-based control-table joins. Joining on DEPTID alone is wrong and fails silently under a multi-SETID configuration — it attaches the wrong description or multiplies rows. SETID_DEPT and SETID_JOBCODE on PS_JOB are confirmed from Oracle sources; SETID_LOCATION is not, so the location join is made only where that column actually exists and is skipped otherwise rather than degraded into a wrong answer.
• PER_ORG = 'EMP' filtering by default. Employees, contingent workers (CWR) and persons of interest (POI) share PS_JOB with nothing else to tell them apart, so an unfiltered roster reports contractors as staff.

The password stays in the InTouch credential vault and never enters a task definition or the assistant's context. The connection is opened read-only; this tool never writes to an HR system of record, and the query operation rejects anything that is not a single SELECT.

Portability

PeopleSoft is certified on Oracle, SQL Server and DB2, and the generated SQL is strictly ANSI — no SYSDATE/GETDATE()/CURRENT DATE, no ROWNUM/TOP/FETCH FIRST, no ||-vs-+. Dates are JDBC bind parameters; maxRows is applied through the JDBC driver. One query text runs on all three.

PeopleTools meta-SQL (%CurrentDateIn, %EffdtCheck, %Join) is deliberately not used — it is expanded by the PeopleTools runtime and is a syntax error over JDBC. (%EffdtCheck would not have been sufficient anyway: it generates only the EFFDT half, with no EFFSEQ and no EFF_STATUS.)

Schema introspection

On each run the tool reads the actual column list of the tables it needs and shapes the query to match, because PeopleSoft installs are heavily customized and the delivered schema has moved across releases. Specifically:

• the primary-job indicator is selected as whichever of JOB_INDICATOR or PRIMARY_JOB_IND exists, aliased to a stable PRIMARY_JOB column;
• PER_ORG is read from PS_JOB if present, otherwise via an EXISTS against PS_PER_ORG_ASGN;
• activeOnly uses HR_STATUS where present, else falls back to Oracle's published inactive EMPL_STATUS set (D, R, T, V, X);
• absent control tables drop their join rather than breaking the query;
• an absent or ungranted PS_JOB reports the schema-owner/grant problem by name.

Credential Required

Yes — a peoplesoft-hcm credential. Server, port, login and password are credential-level vault fields; the JSON adds:

Test Credential connects and then reads PS_INSTALLATION to report the PeopleTools release — connecting alone is not proof, since the most common failure is a good connection to the right database with the wrong schema owner or without SELECT on the PeopleSoft tables.

This tool bundles no JDBC drivers (Oracle and DB2 drivers are not freely redistributable and are already on the server's classpath; a second copy would put rival Driver classes in one JVM).

Operations

All operations accept asOfDate (defaults to today), maxRows, and the output settings.

fields — what columns does this install actually have

Oracle publishes no data dictionary for PeopleSoft HCM: no record/field reference, no ERD in the Help Center, no message payload spec. PeopleBooks document page field labels, not columns. The PeopleTools catalog is therefore the only complete answer — and being per-install, it is the answer that matters anyway, since PeopleSoft deployments are heavily customized.

operation=fields  recordName=JOB,NAMES,DEPT_TBL

Pass the record name, not the table name — record JOB is stored as table PS_JOB, and the catalog is keyed by record. A leading PS_ is stripped for you.

Reads PSRECFIELDDB in preference to PSRECFIELD: the former has subrecords flattened into the physical columns actually present, while the latter leaves subrecord references unexpanded and under-reports the column list. Type metadata (FIELDTYPE, LENGTH, DECIMALPOS) comes from PSDBFIELD and is returned raw and uninterpreted — PeopleTools' numeric type codes aren't documented by Oracle in any citable source, and inventing a mapping is exactly the kind of guess this operation exists to remove.

Use it to settle the questions this connector's introspection works around — whether PER_ORG is on PS_JOB, whether the primary-job column is JOB_INDICATOR or PRIMARY_JOB_IND, whether SETID_LOCATION exists — against your own database rather than against documentation.

codes — stop guessing at code values

Oracle documents PeopleSoft's code sets only partially. It publishes the labels for HR_STATUS (Active/Inactive) and JOB_INDICATOR (Primary/Secondary/Not Applicable) but never the stored characters, and it publishes no complete ACTION list at all — sites are expected to add their own.

So rather than hardcode a guess, ask the database. codes reads PSXLATITEM, PeopleSoft's own translate-value table:

operation=codes  fieldName=EMPL_STATUS,ACTION,PER_ORG,HR_STATUS,JOB_INDICATOR

Returns FIELDNAME, FIELDVALUE, XLATLONGNAME (the meaning), EFFDT, EFF_STATUS, and DELIVERED — Y if Oracle shipped the code, N if the customer added it. PeopleSoft stamps every delivered translate value with EFFDT 1900-01-01, which is what makes that distinction possible, and it is the distinction that decides whether hardcoding a list is safe.

Values are effective-dated individually, so a code retired last year drops out of today's result but still appears with an earlier asOfDate.

Inputs

Published Outputs

operation, rowCount, columnCount, outputFile, rows, truncated, asOfDate, sql (debug mode only).

rowCount is always the true count even when rows is truncated to the inline cap, so a large extract reports its real size rather than looking small.

Status codes and ACTION codes — read this before trusting a filter

activeOnly filters on HR_STATUS = 'A', the documented coarse rollup. The finer EMPL_STATUS is always in the output so you can filter further downstream.

This tool deliberately does not hardcode a set of "active" EMPL_STATUS letters. The delivered set is only partly documented, sites extend it, and the two statuses disagree by design — a retiree still drawing pay is HR_STATUS = 'I' with EMPL_STATUS = 'Q'. A guess here produces a headcount that is wrong in a way nobody notices until it is reconciled against payroll.

Where HR_STATUS is absent the fallback excludes exactly D, R, T, V, X — Oracle's own published definition of an inactive payee. Note that leaves Q (Retired With Pay), U (Terminated With Pay) and W (Short Work Break) in: right for payroll cost, arguably generous for headcount.

Likewise ACTION and ACTION_REASON are customer-configurable — Oracle expects sites to add their own, and publishes no complete delivered list. Defaults are HIR,REH for new_hires and TER,TWB,TWP,RET,RWP for terminations.

Two codes that read like hires and are not, so they are excluded by default:

• ADD is "Add Contingent Worker" — including it reports contractors as new hires
• ADL is "Additional Job" — an existing employee taking a second concurrent job; a real event, but not a new person

Run codes with fieldName=ACTION against your install to see the site's actual list, then pass what you want via actions.

Security notes

Row-level security does not apply. PeopleSoft enforces it in the application layer through security views; there is no database row policy. A direct JDBC read returns every row the login can select. The database grant is therefore the only control — give this connector's login SELECT on precisely the tables it needs and nothing more, ideally through purpose-built read-only views.

PII is not masked at table level. PeopleSoft's Data Privacy framework masks in the UI only; national ID and similar sit plaintext in the tables. Accordingly no operation here selects national ID, bank details, date of birth or demographic detail, and pay lives in its own operation rather than riding along with every headcount extract.

Data-model choices

Three tables are deliberately avoided:

• PS_PERSONAL_DATA — since HRMS 8.3 a denormalized snapshot refreshed by a batch sync, kept for backward compatibility. It lags, and on some installs its refresh is inactive. Identity is read from PS_NAMES (NAME_TYPE = 'PRI', itself effective-dated) instead.
• PS_COMPENSATION — sources disagree on whether COMP_EFFSEQ and COMP_RATECD form part of its key, and a missing key column silently collapses or duplicates multi-rate-code rows. Pay comes from the rate columns on PS_JOB.
• PS_EMPLOYMENT — sources disagree on whether it is a table or a view in HCM 9.2. Hire and termination events are derived from PS_JOB ACTION rows, which is unambiguous and carries the reason code and effective date together.

Verify on your instance

The connector adapts to the schema it finds, so a mismatch degrades rather than crashes — but two things are worth confirming against a live HCM 9.2 instance before relying on an extract:

The tool answers both questions itself — no hand-written SQL needed:

operation=fields  recordName=JOB          → the real column list, incl. whether PER_ORG
                                             and which primary-job spelling this install has
operation=codes   fieldName=EMPL_STATUS,ACTION,HR_STATUS,JOB_INDICATOR
                                          → the site's real codes, with DELIVERED flagging
                                             Oracle's from the customer's own additions

Run those two first against any new install. Everything else in this tool is shaped by what they report.

Build

./gradlew :intouchtools:tools:peoplesoft-hcm:toolJar

Produces build/libs/peoplesoft-hcm-1.0.0.jar, matching connector.json's jars[0].

Install

Assemble $INTOUCH_HOME/tools/installed/peoplesoft-hcm/ with connector.json, peoplesoft-hcm-1.0.0.jar and this README.md, certify it, then load it without a restart:

python3 intouchtools/sign_connector.py $INTOUCH_HOME/tools/installed/peoplesoft-hcm
python3 intouchtools/sign_connector.py $INTOUCH_HOME/tools/installed/peoplesoft-hcm --verify

Then POST /intouch/intouch-tool/scan (or the intouch_scan_tools verb).

Licensing

licenseRequired() returns true, matching the other ERP/HCM connectors. On Department and Enterprise editions the tool name must appear in the server licence's tools list or Server.isToolInstalled blocks it. Unaffected on Personal, Solo and Team.

Publishes

The keys a later task may reference as {{taskName.key}}. Referencing anything not listed here resolves to nothing at run time.

• asOfDate
• columnCount
• operation
• outputFile
• rowCount
• rows
• sql
• truncated