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
    

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

    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 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 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 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 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 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 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 detailed information about a department including its current employees.
    
    :param department_id: The department ID (DEPTID)
    :return: Department details and employee list
    

    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 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
    

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 Integration Broker service operations and their configuration.

Args:
    service_name: Optional service name filter

Returns:
    Service definitions with their operations

    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 the structure of an Integration Broker message.

Args:
    message_name: The message name

Returns:
    Message definition with parts and records

    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 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 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 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
    

    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 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 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 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 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 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 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 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

    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
    

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 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")

    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
    

    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 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

    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
    

    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
    

    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
    

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

    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
    

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

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

    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
    

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")