Oracle PeopleSoft

Oracle PeopleSoft Human Capital Management (HCM) is an enterprise HR platform used to manage employee lifecycle data, including workforce profiles, job assignments, organizational units, and contact information. It is widely deployed in large organizations as a system of record for identity and HR data.

This integration enables DataBee to collect user inventory data from Oracle PeopleSoft by leveraging the delivered Query Access Service (QAS) — a native REST/JSON interface that exposes PeopleSoft queries over Integration Broker without requiring custom application development.

Integration Method: Data Collector API Ingest via PeopleSoft Integration Broker
Tables: User Inventory Info (5003), Training Inventory (99405002)

This integration supports following events:

This integration was tested against the following versions

Configuration Overview

1. Build a PeopleSoft query in Query Manager that returns the required employee fields.

2. Verify the delivered QAS_QRY_REST service operations are present in Integration Broker.

3. Activate the QAS_EXECUTEQRY_REST_GET service operation, set authentication to Basic Authentication, and confirm handlers and routings are active.

4. Create a dedicated permission list, role, and service account user in PeopleSoft.

5. Configure the integration in DataBee using the Data Collector API Ingest method.

Oracle Peoplesoft Configuration

A video walkthrough of the report building process is shown below.

Step 1 — Navigate to Query Manager

1. Log in to your PeopleSoft web portal to go to the main navigation menu.

   

2. From the main navigation menu, click Reporting Tools.

   

3. Under Reporting Tools, click Query.

   

4. Click Query Manager.

   

Step 2 — Create a New Employee Query

1. On the Query Manager page, click Create New Query.

   

Step 2.1 — Add the PERSONAL_DATA Record

1. In the record search field, type PERSONAL_DATA and click Search. From the results, select the PERSONAL_DATA record.

   

2. Select the fields to include from PERSONAL_DATA (e.g., EMPLID, NAME, BIRTHDATE, LAST_NAME, FIRST_NAME, MIDDLE_NAME, SEX, ETHNIC_GRP_CD).

   

3. Below mentioned fields must be selected from the PERSONAL_DATA table. Use pagination arrows as shown above to navigate to another page for selecting the fields.

   EMPLID	MIDDLE_NAME	COUNTRY	NUM2	LASTUPDDTTM
   FIRST_NAME	NAME	CITY	STATE
   LAST_NAME	NAME_DISPLAY	NUM1	POSTAL

Step 2.2 — Add the JOB Record and Join

1. Navigate to the Records tab and click Add Record (or Go to Records) to add a second record.

   

2. Search for and select the JOB record and click on Join Record.

   

3. PeopleSoft will prompt you to define the join. Select Join Record and define the join on EMPLID between PERSONAL_DATA and JOB.

   

4. On the Criteria tab for the JOB record, add effective-date criteria to return only the most current job row (e.g., EFF_STATUS = 'A').

   

5. Select the fields to include from JOB (e.g., DEPTID, JOBCODE, EMPL_STATUS, LOCATION, EFFDT, HR_STATUS, BUSINESS_UNIT, EMPL_TYPE).

   

6. Below mentioned fields must be selected from the JOB table. Use pagination arrows as shown above to navigate to another page for selecting the field.

   EMPL_RCD	DEPTID	HR_STATUS	ACTION	REG_TEMP
   EFFDT	JOBCODE	POSITION_OVERRIDE	ACTION_DT	FULL_PART_TIME
   EFFSEQ	POSITION_NBR	POSN_CHANGE_RECORD	ACTION_REASON	COMPANY
   PER_ORG	SUPERVISOR_ID	EMPL_STATUS	LOCATION	EMPL_TYPE
   SETID_JOBCODE	REPORTS_TO	LAST_HIRE_DT	TERMINATION_DT	EXPECTED_RETURN_DT
   EXPECTED_END_DT	LASTUPDDTTM	LASTUPDOPRID	JOB_ENTRY_DT	DEPT_ENTRY_DT
   POSITION_ENTRY_DT	BUSINESS_UNIT

Step 2.3 — Add Additional Records (Business Unit, Email, Location, Job Code)

1. Repeat the Add Record → Search → Join → Select Fields process for each of the following records to enrich the query output.

2. Click Go to Records to add the next record.

   

Business Unit (BUS_UNIT_TBL_HR)

1. Search for and add the BUS_UNIT_TBL_HR record.

   

2. Define the join criteria on BUSINESS_UNIT.

   

3. Add filter criteria for the Business Unit if required.

   

4. Select the required fields from the Business Unit table (e.g., DESCR, DESCRSHORT).

   

5. Below mentioned fields must be selected from the BUS_UNIT_TBL_HR table.

   DESCR

   ACTIVE_INACTIVE

Email (EMAIL_ADDRESSES)

1. Add the EMAIL_ADDRESSES record.

   

2. Join EMPLID and set criteria for the same.

   

   

3. Select EMAIL_ADDR from the email table.

   

4. Below mentioned fields must be selected from the EMAIL_ADDRESSES table.

   EMAIL_ADDR

   E_ADDR_TYPE

Location (LOCATION_TBL)

1. Add the LOCATION_TBL record.

   

2. Join on LOCATION and set effective-date criteria.

   

   

3. Select the required location fields (e.g., DESCR, COUNTRY, STATE, CITY).

   

4. Below mentioned fields must be selected from the LOCATION_TBL table.

   EFF_STATUS	DESCR	BUILDING	FLOOR	COUNTRY
   CITY	NUM1	NUM2	STATE	POSTAL

Job Code (JOBCODE_TBL)

1. Add the JOBCODE_TBL record.

   

2. Join JOBCODE and SETID, and set effective-date criteria.

   

   

3. Select the required job code fields (e.g., DESCR, DESCRSHORT, MANAGER_LEVEL).

   

4. Below mentioned fields must be selected from the JOBCODE_TBL table.

   EFF_STATUS	DESCR	BUILDING	FLOOR	COUNTRY
   CITY	NUM1	NUM2	STATE	POSTAL

Step 2.4 — Review Final Records

After adding all records, confirm the full list of joined records is displayed on the Records tab. The query should show all six records: PERSONAL_DATA, JOB, BUS_UNIT_TBL_HR, EMAIL_ADDRESSES, LOCATION_TBL, and JOBCODE_TBL in same order as below.

Note:

If the order of records is changed, then it will affect the mapping of feed.

Step 2.5 — Reorder Fields and Set Order By

1. Navigate to the Fields tab. Click Reorder / Sort to arrange the output columns in a logical order with EMPLID first.

   

2. Set Order By on the EMPLID field to enable cursor-based pagination. Select the EMPLID field and in New Order By column, type 1.

   

3. Confirm and save the Order By setting.

   

   Note:

   Sorting EMPLID ascending is required for the DataBee feed to correctly paginate through results using the EMPLID cursor.

Step 2.6 — Add EMPLID Prompt Criteria

The DataBee feed paginates through results by passing the last retrieved EMPLID as a prompt value on each subsequent API call. You must add a criteria row that binds EMPLID to a prompt parameter.

1. Navigate to the Criteria tab. Click Add Criteria.

   

2. In the field search, type EMPLID and select the PERSONAL_DATA.EMPLID field.

   

3. Select the A.EMPLID (PERSONAL_DATA.EMPLID) field from the results.

   

4. Set the Condition Type to Greater Than (>).

   

5. In the Expression 2 section, set the expression type to Prompt and click Add Prompt.

   

6. Change the Prompt Type to RFT Long and the field type to Character.

   

7. Note the Unique Prompt Name assigned to this prompt (e.g., BIND1). Copy this value — it must match the Employee ID Prompt Parameter field in DataBee.

   

8. Save the criteria details.

   

Step 2.7 — Save the Query

1. Click Save to open the Save Query dialog.

   

2. Enter a clear, stable query name (e.g., EMPLOYEE_DETAILS). Note that the name must be uppercase and contain only letters, numbers, and underscores.

3. Set the Query Type to Public (recommended) or Private depending on your security requirements.

4. Fill in a description.

   

5. Click OK or Save to finalize.

   

6. Record the exact Employee Query Name and Query Type as highlighted below — both are required when configuring the integration in DataBee.

   

Step 3 — Create a New Training Query

1. On the Query Manager page, click Create New Query.

   

Step 3.1 — Add the TRAINING record

1. In the record search field, type TRAINING and click Search. From the results, select the TRAINING record.

   

2. Select the fields to include from the TRAINING table (e.g., EMPLID, COURSE, COURSE_TITLE, SESSION_NBR, COURSE_START_DT, etc).

   

3. Below mentioned fields must be selected from the TRAINING table. Click View All button to see all the fields from the table.

   EMPLID	COURSE_END_DT	COURSE_GRADE	LETTER_CD	TRN_PLN_CATEGORY
   COURSE_START_DT	INTERNAL_EXTERNAL	BUSINESS_UNIT	LETTER_SENT_DT	PART_TIME_CRSE_FRA
   COURSE	DT_NEEDED	DEPTID	LETTER_PRINTED_DT	TIME_SPENT_FRA
   SESSION_NBR	STATUS_DT	ATTENDANCE	PREREQ_MET	DURATION_CONVERTED
   COURSE_TITLE	TRAINING_REASON	WAIT_DT	TRAINING_LEAVE_FRA	COUNTRY
   CITY	COUNTY	STATE	POSTAL	VENDOR
   DESCR	ATTENDANCE_BK	EMPL_RCD

Step 3.2 — Reorder Fields and Set Order By

1. Navigate to the Fields tab. Click Reorder / Sort to arrange the output columns in a logical order with EMPLID first.

   

2. Set Order By on EMPLID field to enable cursor-based pagination. Select the EMPLID field and in New Order By column type 1.

   

3. Confirm and save the Order By setting.

   

   Note:

   Sorting EMPLID ascending is required for the DataBee feed to correctly paginate through results using the EMPLID cursor.

Step 3.3 — Add EMPLID Prompt Criteria

The DataBee feed paginates through results by passing the last retrieved EMPLID as a prompt value on each subsequent API call. You must add a criteria row that binds EMPLID to a prompt parameter.

1. Navigate to the Criteria tab. Click Add Criteria.

   

2. In the field search, type EMPLID and select the EMPLID field.

   

3. Select the A.EMPLID (EMPLID) field from the results.

   

4. Set the Condition Type to Greater Than (>).

   

5. In the Expression 2 section, set the expression type to Prompt and click Add Prompt.

   

6. Change the Prompt Type to RFT Long and the field type to Character.

   

7. Note the Unique Prompt Name assigned to this prompt (e.g., BIND1). Copy this value — it must match the Training Prompt Parameter field in DataBee.

   

8. Save the criteria details.

   

Step 3.4 — Save the Query

1. Click Save to open the Save Query dialog.

   

2. Enter a clear, stable query name (e.g., TRAINING_INFO). Note that the name must be uppercase and contain only letters, numbers, and underscores.

3. Set the Query Type to Public (recommended) or Private depending on your security requirements.

4. Fill in a description.

   

5. Click OK or Save to finalize.

   

6. Record the exact Training Query Name and Query Type as highlighted below — both are required when configuring the integration in DataBee.

   

Step 4 — Verify the QAS Service Operation

The DataBee integration uses the delivered QAS_EXECUTEQRY_REST_GET service operation. Verify it exists and is properly configured in Integration Broker.

1. From the main navigation, navigate to PeopleTools.

   

2. Click Integration Broker.

   

3. Click on the Integration Setup.

   

4. Click on Service Operations.

   

5. On the Service Operations Search page, enter QAS in the Service field and click Search.

   

6. In the search results, locate and open QAS_EXECUTEQRY_REST_GET (Service: QAS_QRY_REST, Operation Alias: ExecuteQuery).

   

Step 5 — Set Authentication and Activate the Service

1. On the General tab of QAS_EXECUTEQRY_REST_GET, set the Req Verification dropdown to Basic Authentication.

   

2. Copy the REST Base URL as shown below for later use. This will be required while configuring integration in Databee.

   

3. Scroll down to the Default Service Operation Version section. Confirm that the Active checkbox is checked. If not, make it Active.

4. Confirm the Routing Status shows Any-to-Local: Exists and Local-to-Local: Exists.

   

Step 6 — Confirm Handlers and Routings are Active

Handlers

1. Click the Handlers tab on the QAS_EXECUTEQRY_REST_GET service operation page.

   

2. Confirm handler REQUESTHDLR is present with:

   • Type: On Request

   • Implementation: Application Class

   • Status: Active

   Note:

   If the status is not Active, change it to Active and click Save.

   

Routings

1. Click the Routings tab.

2. Confirm at least one Inbound routing exists with Status: Active. The routing table should show:

   • An Inbound routing with Sender Node ~~ANY~~ and your local node as the Receiver Node — Status: Active

   

• If no inbound routing exists, click Activate Selected Routings or generate an Any-to-Local routing from the service operation’s General tab using the Regenerate Any-to-Local option and then activate it.

Step 7 - Default Local Node

1. Navigate to PeopleTools -> Integration Broker -> Integration Setup -> Node Definitions.

   

2. Click on Search to list all the nodes.

   

3. Scroll the results and find the node name that has Default Local Node Column as Y (i.e. yes). Copy the node name for later use; it will be required while configuring integration in DataBee.

   

DataBee Configuration

Install DataBee data collector

To install a data collector, refer to the following documentation for more information.

This section describes the steps needed for getting parameters for the data collector installation.

1. Login to the DataBee UI, click on the settings icon at the top right corner of the UI, and select System from the dropdown.

   

2. From the left sidebar, select Data Collectors. The page will display all the data collectors configured until now.

   

3. To create a new data collector, scroll to the bottom of the page and click on the Add Data Collector.

   

4. Fill in the required fields to add data collector:

   1. Collector Name: Enter the name of your Data Collector.

   2. OS: Select os option where you want to install data collector. Click Next to proceed to the next step.

   

5. Copy the following details using Copy to Clipboard for later use, then click on Close.

   • Install Script

   • Tenant ID

   • Receiver URL

   • Collector ID

   • API Key (Click Show API Key to view it)

   

6. Open Windows machine where AD is configured and install data collector. (PowerShell 7 or greater is required)

7. Paste the install script in PowerShell to install the collector and then when prompted, enter tenant id, receiver url, collector id and api key.

   

8. Once installed you’ll see a message Installation completed successfully.

Configure Data Feed

1. Login to the DataBee UI, navigate to Data > Data Feeds and click the Add New Data Feed button.

   

2. Search for Oracle Peoplesoft feed and click on that option.

   

3. Click on the Data Collector option.

   

4. Click on the API Ingest option.

   

5. Enter feed contact information and select the connector that you created from drop down and scroll below.

   

6. In configuration section, confirm the following things and scroll down

   • Base URL: Enter the REST API URL copied previously.

   • Event Types: Users, Security Training

   • Default Local Node: Enter the default node name copied previously.

   • Query Type: Select whether the query created previously is public or private.

   • Employee Query Name: Enter the name of the employee query created previously. Required when Users event type is selected.

   • Employee ID Prompt Parameter: Enter the prompt filter parameter name configured while creating a employee query. Defaults to BIND1. Required when Users event type is selected

   • Training Query Name: Enter the name of the training query created previously. Required when the Security Training event type is selected.

   • Training Prompt Parameter: Enter the prompt filter parameter name configured while creating a training query. Defaults to BIND1. Required when Security Training event type is selected.

   • Start Employee ID: Enter the starting range of your employee id for filtering. Defaults to A.

   

   Sample values are as below.

   

7. In authentication section, confirm the following things and scroll down,

   • Authorization Method: Basic

   • Username: Enter the username of peoplesoft superuser (PS)

   • Password: Enter the superuser password.

   

8. Click Submit.

   

Troubleshooting Tips

• 401 Unauthorized

  • Cause: Incorrect username/password or the user account is inactive.

  • Open the user profile in PeopleTools → Security → User Profiles → User Profiles and confirm that Account Status is Active. Re-verify the credentials.

• 404 Not Found

  • Cause: Incorrect Default Local Node name, query type segment, or URL path.

  • Verify the Default Local Node in DataBee matches the node name in PeopleTools → Integration Broker → Integration Setup → Nodes exactly (case-sensitive).

  • Confirm that the PUBLIC/PRIVATE segment and query name in the URL are both correct.

• 500 Internal Server Error

  • Cause: The REQUESTHDLR handler is not active, or the Pub/Sub domain is not running.

  • Open the QAS_EXECUTEQRY_REST_GET service operation → Handlers tab → confirm REQUESTHDLR status is Active.

  • Verify that the Pub/Sub domain is running via PSADMIN → Domain Status.