Solving Duplicate Student Records: How an AI Agent Used psLens MCP to Reverse-Engineer PeopleSoft Name Search Normalization in 3 Minutes

Duplicate person records (EMPLIDs) are one of the most expensive data-integrity problems in PeopleSoft Campus Solutions and HCM. Once a student or employee is assigned two EMPLIDs, financial aid disbursements, enrollment history, transcripts, and residency records split across two identities.

Recently, a college running PeopleSoft Campus Solutions 9.2 on Microsoft SQL Server reported that their real-time Continuing Education integration was generating duplicate EMPLIDs—specifically for students with apostrophes or punctuation in their names, such as Denise O’Rourke.

The integration followed a standard PeopleSoft enrollment flow:

• Run a Search/Match query against PS_PERSON and PS_NAMES using FIRST_NAME_SRCH, LAST_NAME_SRCH, and BIRTHDATE.
• If a match returns (HTTP 200), stop and route to human review or reuse the existing EMPLID.
• If no match returns (HTTP 404), automatically invoke the Component Interface to create a new person record.

For Denise O’Rourke, the Search/Match query missed her existing record every time and created duplicate EMPLIDs (3154281 and 3154282).

To fix the integration permanently, we needed exact answers to two questions:

“How does PeopleSoft populate FIRST_NAME_SRCH and LAST_NAME_SRCH under the hood, and what exact characters does it strip or replace?”

In a traditional PeopleSoft workflow, answering that requires opening Application Designer, expanding subrecords on NAMES, hunting through SavePreChange and FieldFormula events, and scrolling through thousands of lines of Application Package PeopleCode.

Instead, we handed the problem to an AI coding agent connected to the psLens MCP Server. In under three minutes—without opening Application Designer—the agent traced the entire execution chain, extracted the exact character replacement rules, verified PeopleTools Meta-SQL limitations, and updated the integration specification.

The Prompt and Autonomous Discovery Flow

We gave the AI agent in Google Antigravity a single prompt pointing to our local API documentation and the psLens MCP server:

We have a Search/Match bug on SQL Server where apostrophes are causing duplicate names.
The search match queries the _SRCH name fields, but inbound parameters are not stripping
special characters. Use the psLens MCP server to find how LAST_NAME_SRCH and FIRST_NAME_SRCH
are populated, catalog what characters are stripped out, and propose a fix.

Here is the sequence of MCP tool calls the agent executed autonomously to trace the logic from the database table all the way down to the character-by-character translation array:

%%{init: {"theme": "redux-color"}}%%
flowchart TD
    P["User Prompt:\nHow are FIRST_NAME_SRCH & LAST_NAME_SRCH populated?"]
    T1["list_databases\nIdentify connected CS 9.2 instance (CS92DEV-CHG)"]
    T2["get_definition (record: NAMES)\nDiscover fields live on subrecord NAMEGBL_SBR"]
    T3["search_code_sql (NAMEGBL_SBR)\nLocate SavePreChange hook on COUNTRY_NM_FORMAT"]
    T4["get_peoplecode (NAMEGBL_SBR.COUNTRY_NM_FORMAT)\nTrace FormatName() to App Package HCR_NAME:NameFormat"]
    T5["get_peoplecode (HCR_NAME:NameFormat)\nInspect RefreshName() -> RemoveAccent() -> ReplaceCharacter()"]
    T6["get_peoplecode (HCR_FUNCLIB:CharacterReplacement)\nExtract exact ASCII ranges, Unicode mappings & stripping rules"]
    R["Complete Normalization Spec & Integration Fix"]

    P --> T1 --> T2 --> T3 --> T4 --> T5 --> T6 --> R

Tracing the Logic with psLens MCP

Resolving the Subrecord Hierarchy (NAMES → NAMEGBL_SBR)

The agent began by calling list_databases to identify the connected Campus Solutions 9.2 database (CS92DEV-CHG, PeopleTools 8.61.03), then called get_definition on the NAMES record:

{
  "tool": "get_definition",
  "arguments": {
    "db": "CS92DEV-CHG",
    "type": "record",
    "name": "NAMES"
  }
}

Because psLens automatically expands subrecords in record definitions, the agent immediately saw that LAST_NAME_SRCH and FIRST_NAME_SRCH are not top-level fields on NAMES—they live inside the Global Names subrecord NAMEGBL_SBR (fields 5.9 and 5.10):

Finding the Entry Point in Subrecord PeopleCode

Next, the agent ran search_code_sql for NAMEGBL_SBR and spotted Declare Function references pointing to NAMEGBL_SBR.COUNTRY_NM_FORMAT.SavePreChange. Calling get_peoplecode on that event exposed the entry point:

{
  "tool": "get_peoplecode",
  "arguments": {
    "db": "CS92DEV-CHG",
    "context_type": "record_field",
    "name": "NAMEGBL_SBR.COUNTRY_NM_FORMAT",
    "event_or_step": "SavePreChange",
    "start_line": 1,
    "max_lines": 100
  }
}

The returned PeopleCode showed that NAMEGBL_SBR.COUNTRY_NM_FORMAT.SavePreChange instantiates the Application Package class HCR_NAME:NameFormat:

import HCR_NAME:NameFormat;

Component HCR_NAME:NameFormat &oNameFormat;

Function RemoveAccent(&NAME_IN As string, &COUNTRY_FORMAT_IN As string, &NAME_OUT As string, &LeaveWildcardSearchChars As boolean)
   If &oNameFormat = Null Then
      &oNameFormat = create HCR_NAME:NameFormat();
   End-If;
   &NAME_OUT = &oNameFormat.RemoveAccent(&NAME_IN, &COUNTRY_FORMAT_IN, &LeaveWildcardSearchChars);
End-Function;

Following the Call Chain into HCR_NAME:NameFormat

HCR_NAME:NameFormat is a 2,623-line Application Class. Instead of pulling all 2,623 lines into context at once, the agent used search_code_sql to pinpoint method FormatName (line 529) and method RefreshName (line 440), then paged directly to those lines using get_peoplecode:

{
  "tool": "get_peoplecode",
  "arguments": {
    "db": "CS92DEV-CHG",
    "context_type": "app_package",
    "name": "HCR_NAME:NameFormat",
    "start_line": 435,
    "max_lines": 100
  }
}

Lines 481–491 of HCR_NAME:NameFormat.RefreshName() revealed the exact assignments for every _SRCH column on PS_NAMES:

&inrecName.LAST_NAME_SRCH.Value = %This.RemoveAccent(&inrecName.LAST_NAME.Value, &inNameFormat, False);
&inrecName.FIRST_NAME_SRCH.Value = %This.RemoveAccent(&inrecName.FIRST_NAME.Value, &inNameFormat, False);
&inrecName.SECOND_LAST_SRCH.Value = %This.RemoveAccent(&inrecName.SECOND_LAST_NAME.Value, &inNameFormat, False);

If &inrecName.Name = Record.NAMES Then
   &inrecName.NAME_DISPLAY_SRCH.Value = %This.RemoveAccent(&inrecName.NAME_DISPLAY.Value, &inNameFormat, False);
   &inrecName.PREF_FIRST_NM_SRCH.Value = %This.RemoveAccent(&inrecName.PREF_FIRST_NAME.Value, &inNameFormat, False);
   &inrecName.PREF_LAST_NM_SRCH.Value = %This.RemoveAccent(&inrecName.PREF_LAST_NAME.Value, &inNameFormat, False);
   &inrecName.PREF_MIDL_NM_SRCH.Value = %This.RemoveAccent(&inrecName.PREF_MIDDLE_NAME.Value, &inNameFormat, False);
End-If;

Reading RemoveAccent and ReplaceCharacter at line 630 of HCR_NAME:NameFormat showed that when &inLeaveWildcardSearchChars is False (which is always true when saving a person record), HCR_NAME:NameFormat delegates character processing to HCR_FUNCLIB:CharacterReplacement.

Extracting the Character Engine (HCR_FUNCLIB:CharacterReplacement)

With one more get_peoplecode call against HCR_FUNCLIB:CharacterReplacement (642 lines), the agent extracted the entire normalization algorithm:

method ReplaceCharacter
   /+ &inChar as String +/
   /+ Returns String +/
   
   Local integer &idx, &Code;
   
   &Code = Code(Upper(&inChar));
   
   If (&Code > 64 And &Code < 91) Or
         /* CJK Radicals, Symbols, Katakana, Hiragana, Unified Ideographs */
         (&Code >= 11868 And &Code <= 11995) Or
         (&Code >= 12288 And &Code <= 12543) Or
         (&Code >= 12800 And &Code <= 19903) Or
         (&Code >= 19932 And &Code <= 40879) Or
         (&Code >= 65313 And &Code <= 65439) Then
      Return Upper(&inChar);
   End-If;
   
   %This.LoadCharReplacementArray();
   
   &idx = &arr2Rep.Find(&inChar);
   If &idx > 0 Then
      Return &arr2Rep [&idx, 2];
   End-If;
   
   If %CharType_NotMatched = ContainsOnlyCharType(&inChar, %CharType_AlphaNumeric) Then
      Return Upper(&inChar);
   End-If;
   
   Return "";
end-method;

Catalog of PeopleSoft’s _SRCH Name Normalization Logic

From that three-minute trace, the agent produced a complete, authoritative specification of how PeopleSoft populates FIRST_NAME_SRCH, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_DISPLAY_SRCH, and the preferred name search columns:

Retained Characters

• Standard Latin Letters (A–Z, a–z): Uppercased and retained via ASCII code check (&Code > 64 And &Code < 91).
• CJK & Japanese Character Blocks: Retained in uppercase across specific Unicode ranges (11868–11995, 12288–12543, 12800–19903, 19932–40879, and halfwidth/fullwidth forms 65313–65439).
• Wildcard Search Characters (%, _, \): Retained only during interactive SearchSave page events when &inLeaveWildcardSearchChars = True. They are stripped when storing data to PS_NAMES.

Translated Characters (Diacritic & Accent Mapping)

LoadCharReplacementArray() loads over 280 Unicode Latin diacritics into &arr2Rep and maps each one to its base uppercase ASCII letter(s):

• Single-character base mappings: À, Á, Â, Ã, Ä, Å, Æ → A; Ç, Ć, Č → C; È, É, Ê, Ë → E; Ì, Í, Î, Ï → I; Ñ, Ń → N; Ò, Ó, Ô, Õ, Ö, Ø → O; Ù, Ú, Û, Ü → U; Ý, Ÿ → Y; Ł → L; Š → S; Ž → Z; Ð → D; Þ → T.
• Multi-character expansion: German Eszett (ß, U+00DF) expands to two characters: SS.

Stripped Characters (Replaced with "")

Every character that does not match the rules above falls through to Return "" and is completely removed from the _SRCH field:

Catching the Second Trap: Dynamic SQL vs. PeopleTools Meta-SQL

Understanding the normalization explained why the integration created duplicate records, and it also exposed a second bug when testing live payloads.

The integration queried PeopleSoft through a generic XML query service (N_DATA_QUERY) with a dynamic <whereClause>:

A.BIRTHDATE = %DATEIN('{{DOB}}') AND
EXISTS (SELECT 1 FROM PS_NAMES NAMES WHERE
  A.EMPLID = NAMES.EMPLID AND
  NAMES.FIRST_NAME_SRCH LIKE %Substring(%Upper('{{FIRST}}'), 1, 4) || '%' AND
  NAMES.LAST_NAME_SRCH LIKE %Substring(%Upper('{{LAST}}'), 1, 4) || '%'
)

When 'O'Rourke' was injected into '{{LAST}}', two separate failures occurred depending on how the string was passed:

• Unescaped Apostrophe ('O'Rourke'): Crashed at SQL compilation time with SQL error in DescribeCol. (2,279) because the internal apostrophe prematurely closed the SQL string literal before any SQL REPLACE() function could even run.
• SQL-Escaped Apostrophe ('O''Rourke'): Compiled cleanly, but evaluated to NAMES.LAST_NAME_SRCH LIKE 'O''R%'. Because PeopleSoft stored the value as 'OROURKE', the query returned 0 rows (HTTP 404), triggering the person-creation Component Interface to mint a duplicate EMPLID.

During the session, we asked the agent two follow-up questions that developers normally spend half an afternoon testing by trial and error:

• “Is there a PeopleTools Meta-SQL function like %Replace that is agnostic to Oracle and SQL Server?”
• “Isn’t there a PeopleSoft %Quote Meta-SQL construct that handles escaping single quotes?”

Instead of guessing or hallucinating a nonexistent Meta-SQL macro, the agent used search_code_sql across the entire PeopleSoft codebase for %Replace and %Quote:

{
  "tool": "search_code_sql",
  "arguments": {
    "db": "CS92DEV-CHG",
    "search_term": "%Quote",
    "scope": "all"
  }
}

Both searches returned 0 matches across all delivered PeopleCode, SQL Objects, and Application Engine programs, while a code search for Quote( confirmed that Quote(&str) is strictly a PeopleCode built-in function—not a Meta-SQL macro available inside CreateSQL() strings.

With the full picture verified against live PeopleSoft metadata, the agent updated our integration specification with the exact pre-query sanitization rules required for the middleware layer (stripping /[^A-Za-z]/g, translating diacritics, and uppercasing before building the XML request) and documented the portable SQL REPLACE() + %Concat pattern for defense in depth.

Why Engineering Teams Use psLens MCP

Without psLens, resolving this ticket requires a senior PeopleSoft developer to stop what they are doing, log into a Windows workstation or remote desktop, launch Application Designer, open NAMES, expand NAMEGBL_SBR, inspect PeopleCode events across multiple fields, open HCR_NAME and HCR_FUNCLIB, and manually read through 3,200+ lines of PeopleCode to catalog the character rules.

With the psLens MCP Server, your AI coding agent does the entire investigation inside your IDE in seconds—while citing every record, subrecord, class, method, and line number:

Whether you are building integrations, debugging batch processes, auditing security, or tracing custom PeopleCode, psLens turns your AI assistant into a PeopleSoft architect with live, read-only visibility into your environment.

See psLens MCP on Your Environment

The psLens MCP server is embedded directly in the single psLens binary with read-only table whitelisting and zero changes to your PeopleSoft infrastructure:

• Read the MCP Server & Agent Skills Documentation to connect Claude Code, Google Antigravity, Cursor, or VS Code.
• Read last week’s walkthrough: Tracing Every Place a PeopleSoft Field Is Updated with psLens MCP.
• Book a live demo to see psLens and an AI agent debug and document your PeopleSoft environment in real time.

Subscribe for Updates

If you found this article helpful, subscribe to get notified of new PeopleSoft technical notes and psLens updates.

• ←Previous
• Next→