| query_peoplesoft_dbA | Query PeopleSoft Oracle Database directly.
When working with a PeopleSoft database, follow these guidelines in order:
1. FIRST, always check the record structure and fields:
- Use: SELECT RECNAME, FIELDNAME FROM PSRECFIELD WHERE RECNAME = 'YOUR_TABLE'
- This shows all fields in a record/table and helps prevent invalid field errors
2. SECOND, check field properties and translations:
- For field details: SELECT * FROM PSDBFIELD WHERE FIELDNAME = 'YOUR_FIELD'
- For code translations:
* ALWAYS check PSXLATITEM and PSXLATITEMLANG for single/double letter codes
* Example: 'M'/'F' for SEX, 'SP'/'C' for RELATIONSHIP, 'Y'/'N' for flags
* Query: SELECT FIELDNAME, FIELDVALUE, XLATLONGNAME, XLATSHORTNAME
FROM PSXLATITEM
WHERE FIELDNAME = 'YOUR_FIELD'
- Understanding field properties ensures correct data handling
3. THIRD, review table indexes for performance:
- Use: SELECT * FROM PSKEYDEFN WHERE RECNAME = 'YOUR_TABLE'
- Knowing indexes helps write efficient queries using indexed fields
4. FINALLY, write your query using the discovered structure
- Example finding employee data:
1. Check PSRECFIELD for PS_PERSONAL_DATA fields
2. Look up important fields in PSDBFIELD
3. Check PSKEYDEFN for PS_PERSONAL_DATA indexes
4. Write optimized query using indexed fields
5. Don't forget to join with PSXLATITEM for any code fields
AVAILABLE RESOURCES:
- Use describe_table() to get table structure
- Use list_tables() to search for tables
- Use get_translate_values() to decode field codes
- Use get_table_indexes() for performance optimization
:param sql_query: SQL query to execute (e.g., SELECT * FROM PS_EMPLOYEE WHERE EMPLID = :1)
:param parameters: List of query parameters (optional)
:return: A dictionary containing query results or an error message
|
| describe_tableA | Get the structure of a PeopleSoft table/record including all fields,
their types, lengths, and descriptions.
Use this tool FIRST when you need to understand what fields are available
in a table before writing queries.
:param table_name: The PeopleSoft record name (e.g., 'PERSONAL_DATA', 'JOB', 'DEPT_TBL').
Can include or exclude the 'PS_' prefix.
:return: List of fields with their properties
|
| list_tablesA | Search for PeopleSoft tables/records by name pattern or module.
:param pattern: Optional search pattern (e.g., 'EMPLOYEE', 'JOB', 'GP_RSLT').
Searches with wildcards automatically.
:param module: Optional module filter. Values:
- 'HR' or 'CORE': Core HR tables (PERSONAL_DATA, JOB, DEPT, etc.)
- 'GP' or 'PAYROLL': Global Payroll tables (GP_*)
- 'EP' or 'PERFORMANCE': ePerformance tables (EP_*)
- 'BN' or 'BENEFITS': Benefits tables (BEN_*, DEPENDENT_*)
- 'SYSTEM': PeopleTools system tables (PS* without underscore)
:param limit: Maximum number of results (default 50)
:return: List of matching tables with descriptions
|
| get_translate_valuesA | Get all valid translate values for a PeopleSoft field.
Many PeopleSoft fields use single or double letter codes that have
specific meanings. This tool decodes those values.
Common examples:
- SEX: 'M' = Male, 'F' = Female
- HR_STATUS: 'A' = Active, 'I' = Inactive
- EMPL_STATUS: 'A' = Active, 'T' = Terminated, 'L' = Leave, etc.
- MAR_STATUS: 'S' = Single, 'M' = Married, 'D' = Divorced
:param field_name: The field name to look up (e.g., 'SEX', 'HR_STATUS', 'EMPL_STATUS')
:return: List of valid values with their short and long descriptions
|
| get_table_indexesA | Get the index keys for a PeopleSoft table to help write efficient queries.
Understanding indexes helps you:
- Write WHERE clauses that use indexed fields for better performance
- Understand the logical key structure of a table
- Know which fields uniquely identify a row
Common PeopleSoft key patterns:
- Simple: EMPLID (employee ID only)
- Effective-dated: EMPLID, EMPL_RCD, EFFDT, EFFSEQ
- SetID-based: SETID, DEPTID, EFFDT
:param table_name: The PeopleSoft record name (e.g., 'JOB', 'PERSONAL_DATA')
:return: List of indexes with their key fields
|
| get_table_relationshipsA | Find tables related to the specified table by analyzing shared key fields.
This helps discover:
- Parent/child relationships
- Common join patterns
- Related configuration tables
:param table_name: The PeopleSoft record name to analyze
:return: List of potentially related tables grouped by relationship type
|
| get_employeeA | Get comprehensive employee profile including personal data, current job,
department, and employment status.
This is the primary tool for getting employee information. It returns:
- Personal details (name, birthdate, address, etc.)
- Current job information (department, position, manager, etc.)
- Employment status and dates
:param employee_id: The employee ID (EMPLID)
:return: Complete employee profile
|
| search_employeesA | Search for employees by various criteria.
:param name: Search by employee name (partial match supported)
:param department: Filter by department ID
:param company: Filter by company code
:param location: Filter by location code
:param job_code: Filter by job code
:param status: Filter by status: 'active' (default), 'inactive', 'terminated', or 'all'
:param limit: Maximum results to return (default 50)
:return: List of matching employees with basic info
|
| get_job_historyA | Get the complete job history for an employee showing all effective-dated changes.
This shows every job change including:
- Position changes
- Department transfers
- Promotions/demotions
- Salary changes
- Status changes (leave, return to work, termination, etc.)
:param employee_id: The employee ID (EMPLID)
:param limit: Maximum number of history records (default 50)
:return: Chronological list of job changes
|
| get_org_chartA | Get organizational hierarchy showing reporting relationships.
You can start from:
- A specific department (all employees in that dept with their managers)
- A specific manager (all direct and indirect reports)
- A company (top-level org structure)
:param department_id: Starting department ID
:param manager_id: Starting manager's employee ID
:param company: Company code to filter by
:param max_depth: Maximum depth of hierarchy to retrieve (default 3)
:return: Hierarchical organization structure
|
| get_department_infoB | Get detailed information about a department including its current employees.
:param department_id: The department ID (DEPTID)
:return: Department details and employee list
|
| get_payroll_resultsB | Get earnings and deductions results for an employee's payroll run.
:param employee_id: The employee ID (EMPLID)
:param calendar_run: Optional calendar run ID. If not provided, returns latest run.
:param element_type: Optional filter: 'earnings', 'deductions', or 'all' (default)
:return: Payroll results with earnings and deductions
|
| get_payroll_statusA | Get the processing status for all employees in a payroll calendar run.
Status codes:
- 'I': Identified
- 'C': Calculated
- 'F': Finalized
- 'P': Paid
:param calendar_run: The calendar run ID (CAL_RUN_ID)
:return: Summary of payroll processing status
|
| get_accumulator_balancesA | Get accumulator balances (YTD, MTD, etc.) for an employee.
Accumulators track cumulative values like:
- Year-to-date earnings
- Year-to-date taxes
- Lifetime pension contributions
- Month-to-date hours worked
:param employee_id: The employee ID (EMPLID)
:param accum_type: Optional filter by accumulator type (e.g., 'YTD', 'MTD', 'QTD')
:param calendar_run: Optional calendar run ID. If not provided, returns latest.
:return: Accumulator balances
|
| get_payment_infoA | Get payment preparation and payment details for an employee.
:param employee_id: The employee ID (EMPLID)
:param calendar_run: Optional calendar run ID. If not provided, returns latest.
:return: Payment details including net pay and payment method
|
| list_calendar_runsA | List payroll calendar runs with their status.
:param pay_entity: Optional filter by pay entity
:param year: Optional filter by year
:param status: Optional filter by status ('open', 'closed', 'finalized')
:param limit: Maximum results (default 20)
:return: List of calendar runs
|
| get_performance_reviewsA | Get performance appraisals for an employee.
Status codes:
- 'INP': In Progress
- 'COMP': Completed
- 'CANC': Cancelled
- 'PEND': Pending
:param employee_id: The employee ID (EMPLID)
:param year: Optional filter by review year
:param status: Optional filter by status code
:return: List of performance reviews
|
| get_review_detailsB | Get detailed ratings, goals, and comments for a specific performance review.
:param appraisal_id: The appraisal ID (EP_APPR_ID)
:return: Complete review details including sections, items, and ratings
|
| search_reviewsB | Search performance reviews by various criteria.
:param reviewer_id: Filter by reviewer's employee ID
:param department: Filter by employee's department
:param status: Filter by status ('INP', 'COMP', 'CANC', 'PEND')
:param year: Filter by review year
:param limit: Maximum results (default 50)
:return: List of matching reviews
|
| get_benefit_electionsC | Get current benefit plan elections for an employee.
Shows active benefit enrollments including:
- Health plans
- Life insurance
- Retirement plans
- Other voluntary benefits
:param employee_id: The employee ID (EMPLID)
:return: List of benefit elections with plan details
|
| get_dependentsB | Get dependents and beneficiaries for an employee.
Returns:
- Dependent personal information
- Relationship to employee
- Benefits coverage status
- National ID (if available)
:param employee_id: The employee ID (EMPLID)
:return: List of dependents with their details
|
| get_beneficiariesA | Get beneficiary designations for an employee's benefit plans.
:param employee_id: The employee ID (EMPLID)
:param plan_type: Optional filter by plan type (e.g., '2A' for life insurance)
:return: Beneficiary designations by plan
|
| get_benefit_costsB | Get benefit cost information including employee and employer contributions.
:param employee_id: The employee ID (EMPLID)
:param as_of_date: Optional date (YYYY-MM-DD format). Defaults to current date.
:return: Benefit costs by plan
|
| get_record_definitionA | Get complete definition of a PeopleSoft record including all fields,
keys, and properties. Essential for understanding data structures.
Args:
record_name: The record name (e.g., 'JOB', 'PERSONAL_DATA')
Will automatically add PS_ prefix if not present
Returns:
Record metadata including type, fields, keys, and parent record
|
| search_recordsB | Search for PeopleSoft records by name or description.
Args:
search_term: Partial name or description to search for
record_type: Optional filter by type (0=Table, 1=View, 2=Derived, 7=Temp)
Returns:
List of matching records with their types and descriptions
|
| get_component_structureA | Get the structure of a PeopleSoft component including its pages,
records, and navigation path.
Args:
component_name: The component name (e.g., 'JOB_DATA', 'PERSONAL_DATA')
Returns:
Component definition with pages, records, and menu navigation
|
| get_component_pagesA | Get lightweight component-to-pages mapping. Uses only PSPNLGRPDEFN and
PSPNLGROUP with version-safe columns.
Args:
component_name: The component name (e.g., 'JOB_DATA', 'ABSV_PLAN_TABLE')
Returns:
Component name, search record, and list of pages with item number and label
|
| get_page_fieldsB | Get all fields defined on a PeopleSoft page with their properties.
Args:
page_name: The page name (e.g., 'JOB_DATA1', 'PERSONAL_DATA_1')
Returns:
Page definition with all field controls and their record/field bindings
|
| get_page_field_bindingsA | Get simplified page field bindings: RECNAME, FIELDNAME, FIELDNUM, OCCURSLEVEL only.
Lightweight alternative when full page field properties are not needed.
Args:
page_name: The page name (e.g., 'JOB_DATA1', 'ABSV_PLAN_TABLE')
Returns:
Page name and list of record/field bindings
|
| get_peoplecodeA | Get PeopleCode programs attached to a record/field, including the actual source code.
Reads from PSPCMTXT which stores the PeopleCode source as CLOB. Use this to
understand component logic, trace field behavior, or analyze customizations.
Args:
record_name: The record name (e.g., 'JOB', 'ABSV_REQUEST')
field_name: Optional field name to filter (e.g., 'EMPLID', 'EFFDT')
event: Optional event type filter. Common events:
- RowInit: Fires when row is loaded
- FieldChange: Fires when field value changes
- FieldEdit: Validates field before accepting
- SaveEdit: Validates before save
- SavePreChange: Runs before database update
- SavePostChange: Runs after database update
- RowDelete: Fires when row is deleted
- RowInsert: Fires when new row inserted
- SearchInit: Fires on search page load
- SearchSave: Fires when search is executed
include_code: If True (default), returns actual PeopleCode source.
Set to False for just metadata/listing.
max_code_length: Maximum characters of code to return per program (default 32000).
Use for large programs that may exceed response limits.
Returns:
List of PeopleCode programs with their events and source code.
Programs are ordered by field name and event for logical reading.
Example:
# Get all PeopleCode for ABSV_REQUEST record
get_peoplecode("ABSV_REQUEST")
# Get just FieldChange events for BEGIN_DT field
get_peoplecode("ABSV_REQUEST", "BEGIN_DT", "FieldChange")
# List all events without code (for discovery)
get_peoplecode("JOB", include_code=False)
|
| get_permission_list_detailsC | Get details of a permission list including pages, components, and query access.
Args:
permission_list: The permission list name
Returns:
Permission list definition with component and page access
|
| get_roles_for_permission_listB | Find all roles that include a specific permission list.
Args:
permission_list: The permission list name
Returns:
List of roles containing this permission list
|
| get_process_definitionB | Get process scheduler definitions.
Args:
process_name: Optional specific process name
process_type: Optional filter by type (e.g., 'SQR Report', 'Application Engine')
Returns:
Process definitions with their configuration
|
| get_application_engine_stepsA | Get the steps and sections of an Application Engine program.
Args:
ae_program: The AE program name
Returns:
Program structure with sections, steps, and actions
|
| get_integration_broker_servicesC | Get Integration Broker service operations and their configuration.
Args:
service_name: Optional service name filter
Returns:
Service definitions with their operations
|
| get_message_definitionA | Get the structure of an Integration Broker message.
Args:
message_name: The message name
Returns:
Message definition with parts and records
|
| get_query_definitionB | Get a PS Query definition including its records, fields, and criteria.
Args:
query_name: The query name
Returns:
Query definition with records, fields, and criteria
|
| get_sql_definitionA | Get the SQL text for a PeopleSoft SQL object by SQLID.
PSSQLTEXTDEFN stores SQL used by views, App Engine programs, and PeopleCode
(SQL.SQLID). Long SQL is split across multiple rows by SEQNUM.
Args:
sql_id: The SQL object ID (e.g. 'HR_ABSV_JOB_EFFDT', 'GP_PIN_SELECT')
max_length: Maximum total characters to return (default 64000).
Use to avoid huge responses for very long SQL.
Returns:
SQL definition with sql_text (concatenated from all SEQNUM rows),
sql_type, market, and row_count.
Example:
get_sql_definition("HR_ABSV_JOB_EFFDT")
|
| search_sql_definitionsA | Search for SQL object IDs whose text contains the given term.
Useful for discovering which SQL objects reference a table, field, or
other identifier. Does not return full SQL text—use get_sql_definition
for that.
Args:
search_term: Text to search for in SQL (e.g. 'PS_JOB', 'ABSV_REQUEST')
limit: Maximum number of SQLIDs to return (default 50)
Returns:
List of matching SQLIDs with sql_type and market.
Example:
search_sql_definitions("PS_ABSV_REQUEST")
|
| search_peoplecodeA | Search for text within PeopleCode programs.
Args:
search_term: Text to search for in PeopleCode
search_in: Where to search - 'all', 'record', 'component', 'appengine'
Returns:
List of PeopleCode locations containing the search term
|
| get_field_usageA | Find all records that use a specific field - useful for impact analysis.
Args:
field_name: The field name to search for
Returns:
List of records containing this field and key information
|
| get_translate_field_valuesC | Get all translate values for a field with their effective dates.
Args:
field_name: The field name that uses translate values
Returns:
All translate values with descriptions and effective dates
|
| explain_peoplesoft_conceptB | Provide explanation of PeopleSoft/PeopleTools concepts based on actual
system metadata and configuration.
Args:
concept: The concept to explain (e.g., 'effective_dating', 'component',
'record_types', 'security', 'integration_broker')
Returns:
Relevant system metadata that illustrates the concept
|
