# Solving Duplicate Student Records: How an AI Agent Used psLens MCP to Reverse-Engineer PeopleSoft Name Search Normalization in 3 Minutes > How an AI coding agent connected to the psLens MCP server traced PeopleSoft's LAST_NAME_SRCH and FIRST_NAME_SRCH normalization logic across subrecords and Application Packages to stop an integration from creating duplicate EMPLIDs. --- Site [llms.txt](/llms.txt) --- Duplicate person records (`EMPLID`s) are one of the most expensive data-integrity problems in PeopleSoft Campus Solutions and HCM. Once a student or employee is assigned two `EMPLID`s, 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 `EMPLID`s—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 `EMPLID`s (`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: ```text 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: ```mermaid %%{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: ```json { "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`): | # | Field | Type | Length | Search |List | | :--- | :--------------------- | :-------- | :----- | :----- | :-- | | 5 | `NAMEGBL_SBR` | SubRecord | - | | | | 5.9 | `-> LAST_NAME_SRCH` | Character | 30 | | Yes | | 5.10 | `-> FIRST_NAME_SRCH` | Character | 30 | | Yes | | 5.11 | `-> LAST_NAME` | Character | 30 | | | | 5.12 | `-> FIRST_NAME` | Character | 30 | | | | 5.15 | `-> SECOND_LAST_SRCH` | Character | 30 | | Yes | ### 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: ```json { "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`**: ```peoplecode 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`: ```json { "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`: ```peoplecode &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: ```peoplecode 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: | Character Type | Examples | Input Name | Stored in `LAST_NAME_SRCH` | | :--- | :--- | :--- | :--- | | **Apostrophes & Single Quotes** | `'`, `’` | `O'Rourke` | **`OROURKE`** | | **Hyphens & Dashes** | `-`, `–` | `Smith-Jones` | **`SMITHJONES`** | | **Spaces** | ` ` | `De La Cruz` | **`DELACRUZ`** | | **Periods & Commas** | `.`, `,` | `St. John` | **`STJOHN`** | | **Digits & Symbols** | `0–9`, `@`, `#`, `/` | `III`, `Jr.` | **`III`**, **`JR`** | --- ## 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 ``: ```sql 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`: ```json { "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: | MCP Tool Invoked | What It Answered in Seconds | | :--- | :--- | | **`list_databases`** | Identified the active Campus Solutions 9.2 instance (`CS92DEV-CHG`, PT 8.61.03) | | **`get_definition`** | Expanded `NAMES` and proved `FIRST_NAME_SRCH` / `LAST_NAME_SRCH` belong to `NAMEGBL_SBR` | | **`search_code_sql`** | Located `NAMEGBL_SBR.COUNTRY_NM_FORMAT.SavePreChange`, found `FormatName` / `RefreshName` line numbers, and verified `%Replace` and `%Quote` do not exist in Meta-SQL | | **`get_peoplecode`** | Extracted the exact normalization logic from `HCR_NAME:NameFormat` and the 280+ character mappings in `HCR_FUNCLIB:CharacterReplacement` | 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](/docs/mcp/) 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](/blog/tracing-peoplesoft-field-updates-with-pslens-mcp/). - [Book a live demo](/contact/) 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.
Stay Updated

Receive new articles directly in your inbox.