PeopleSoft Server

A Model Context Protocol (MCP) server that enables AI assistants to query and understand PeopleSoft HCM databases. This server provides semantic tools for HR, Payroll, Benefits, Performance, and PeopleTools metadata - allowing natural language questions to be answered with accurate SQL queries.

Features

• 41 semantic tools covering all major PeopleSoft HCM modules
• 4 documentation resources for PeopleSoft concepts and query patterns
• Direct database access via Oracle thin client (no JDBC required)
• PeopleTools introspection for understanding system architecture
• Effective dating support built into all queries

Quick Start

Prerequisites

• Python 3.11+
• Oracle Database connectivity to a PeopleSoft HCM 9.2 instance
• uv package manager (recommended)

Installation

# Clone the repository
git clone <repo-url>
cd peoplesoft-mcp

# Install dependencies
uv sync

Configuration

1. Copy the example environment file and add your credentials:

cp .env.example .env

2. Edit .env with your database credentials:

ORACLE_DSN=hostname:port/service_name
ORACLE_USER=your_username
ORACLE_PASSWORD=your_password

3. Edit .cursor/mcp.json and update the path to your installation.

Running the Server

uv run peoplesoft_server.py

Cursor IDE Integration

The MCP config (.cursor/mcp.json) should look like:

{
  "mcpServers": {
    "peoplesoft": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/mcp_ps/",
        "run",
        "peoplesoft_server.py"
      ]
    }
  }
}

Credentials are loaded from .env automatically - no need to include them in the MCP config.

Available Tools

Schema Introspection (5 tools)

Tool	Description
describe_table	Get table structure, fields, and indexes
list_tables	Search for tables by name pattern
get_translate_values	Decode field codes (XLAT values)
get_table_indexes	View index definitions for performance
get_table_relationships	Find foreign key relationships

HR Module (5 tools)

Tool	Description
get_employee	Get employee details by EMPLID
search_employees	Search employees by name, department, etc.
get_job_history	View job history for an employee
get_org_chart	Get organizational hierarchy
get_department_info	Get department details and headcount

Payroll Module (5 tools)

Tool	Description
get_payroll_results	View payroll calculation results
get_payroll_status	Check payroll processing status
get_accumulator_balances	Get YTD/MTD balances
get_payment_info	Get payment details
list_calendar_runs	List payroll calendar runs

Benefits Module (4 tools)

Tool	Description
get_benefit_elections	View benefit plan elections
get_dependents	Get dependent information
get_beneficiaries	Get beneficiary designations
get_benefit_costs	Calculate benefit costs

Performance Module (3 tools)

Tool	Description
get_performance_reviews	List performance documents
get_review_details	Get detailed review information
search_reviews	Search reviews by criteria

PeopleTools Module (18 tools)

Tool	Description
get_record_definition	Full record structure with fields and keys
search_records	Find records by name or description
get_component_structure	Component pages and navigation
get_page_fields	Fields on a page with record bindings
get_peoplecode	Find PeopleCode on records/fields
get_permission_list_details	Security access for permission lists
get_roles_for_permission_list	Roles containing a permission list
get_process_definition	Process Scheduler job definitions
get_application_engine_steps	AE program structure
get_integration_broker_services	IB service operations
get_message_definition	IB message structure
get_query_definition	PS Query records and fields
get_sql_definition	Get SQL text by SQLID (views, App Engine, PeopleCode)
search_sql_definitions	Search SQL objects by text
search_peoplecode	Search text within PeopleCode
get_field_usage	Impact analysis - where a field is used
get_translate_field_values	All XLAT values for a field
explain_peoplesoft_concept	Explains effective dating, SetID, etc.

Direct Query (1 tool)

Tool	Description
query_peoplesoft_db	Execute custom SQL queries

Available Resources

Resource URI	Description
peoplesoft://schema-guide	Major tables by module
peoplesoft://concepts	Effective dating, EMPLID, SetID, XLAT
peoplesoft://query-examples	SQL query patterns
peoplesoft://peopletools-guide	PeopleTools architecture guide

Project Structure

peoplesoft-mcp/
├── peoplesoft_server.py    # Main MCP server entry point
├── db.py                   # Database connection management
├── tools/                  # Semantic tool modules
│   ├── introspection.py    # Schema discovery tools
│   ├── hr.py               # HR module tools
│   ├── payroll.py          # Payroll module tools
│   ├── benefits.py         # Benefits module tools
│   ├── performance.py      # ePerformance tools
│   └── peopletools.py      # PeopleTools metadata tools
├── tests/                  # Test suites
│   ├── test_business_questions.py   # HR business scenarios
│   └── test_peopletools_questions.py # Technical consultant scenarios
├── docs/                   # Documentation resources
│   ├── peoplesoft_concepts.md
│   ├── peoplesoft_schema_guide.md
│   ├── peopletools_guide.md
│   ├── sql_query_examples.md
│   ├── peopletools-tables-by-tool.md   # Tables required per tool
│   └── migration-analysis.md   # Migration planning notes
└── pyproject.toml          # Project configuration

Running Tests

# Set environment variables
export ORACLE_DSN="hostname:port/service_name"
export ORACLE_USER="username"
export ORACLE_PASSWORD="password"

# Run all tests
uv run pytest tests/ -v -s

# Run specific test suite
uv run pytest tests/test_business_questions.py -v -s
uv run pytest tests/test_peopletools_questions.py -v -s

Example Queries

The MCP enables natural language questions like:

Business Questions:

• "How many active employees are in each company?"
• "What is the average salary by department?"
• "Who reports to manager X?"
• "Show me employees without a supervisor assigned"

Technical Questions:

• "What fields does PS_JOB have?"
• "Where is the DEPTID field used?"
• "What PeopleCode runs on the JOB record?"
• "What SQL does HR_ABSV_JOB_EFFDT use?"
• "Search for SQL objects referencing PS_ABSV_REQUEST"
• "Explain effective dating in PeopleSoft"

Development

Adding New Tools

1. Create a new module in tools/ or add to existing module
2. Define async functions that use db.execute_query()
3. Add a register_tools(mcp) function
4. Import and register in peoplesoft_server.py

Key Concepts

• Effective Dating: Most PeopleSoft tables use EFFDT/EFFSEQ for history
• SetID: Controls data sharing across business units
• Translate Values: Short codes decoded via PSXLATITEM
• EMPLID/EMPL_RCD: Employee ID + employment record number

License

MIT

Changelog

v0.2.1 (2026-03-02)

• Added get_sql_definition - fetch SQL text by SQLID from PSSQLTEXTDEFN
• Added search_sql_definitions - search SQL objects by text
• PeopleTools compatibility: adjusted PSPNLGROUP, PSPNLFIELD, PSAEAPPLDEFN, PSAESTEPDEFN queries for varying column names across PeopleTools versions
• Added docs/peopletools-tables-by-tool.md - tables required per tool

v0.2.0 (2026-02-28)

• Added modular tool architecture with 39 semantic tools
• Added PeopleTools introspection module (16 tools)
• Added comprehensive test suites (42 tests)
• Replaced connection pooling with direct connections for reliability
• Added 4 documentation resources
• Improved effective dating patterns in all queries