---
title: "PK82028_PHONETIC_NAME stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "SAIF"
last_reviewed: "2026-07-28"
library: SAIF.Business.Claim.Person
reference_type: package
---

# PK82028_PHONETIC_NAME

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.Claim.Person` |
| **Calling classes** | `clsClaimPersonName` |

## Purpose

PK82028_PHONETIC_NAME centralizes Oracle-side logic consumed by `SAIF.Business.Claim.Person` for retrieval, persistence, sequence generation, or derived calculations.

## Procedures / Methods

### createPhoneticName

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `clsClaimPersonName` |

**Description:** createPhoneticName is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inLastName` | IN | VARCHAR2 | — | Parameter from package specification. |
| `inFirstName` | IN | VARCHAR2 | — | Parameter from package specification. |
| `inMiddleInitial` | IN | VARCHAR2 | — | Parameter from package specification. |

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PROC-RULE-001 | Package-backed logic is required by this library. | The library calls this Oracle package. | Return values or mutate data for the calling BOF object. |

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| createPhoneticName | Oracle routine referenced by `SAIF.Business.Claim.Person`. | Called from package-backed BOF logic. |

## Source Code

<details>
<summary>PL/SQL package specification and body</summary>

```sql
PACKAGE pk82028_phonetic_name AS

 FUNCTION createPhoneticName(inLastName IN VARCHAR2, inFirstName IN VARCHAR2, inMiddleInitial IN VARCHAR2) RETURN VARCHAR2;

END pk82028_phonetic_name;
 / 
PACKAGE BODY pk82028_phonetic_name
IS

/*****************************************************************
*Package Title  :
*Name           : pk82028_phonetic_name
*****************************************************************
*Description:
*   	Create the phonetic name from pased last, first, and middle names.
*
*PROGRAMMER     ANALYST                 DATE       MOD #
*  Tony Busch						03/07/2007     Add code to handle duplicate letters after
*                                                  a match is found on one of that last name tables
*  Shirlie Poe						12/13/2006	   change v_goodname to 54
*  		   										   to accommodate 50 character
*												   last name
* Tony Busch						11/21/2005	   New Package
*******************************************************************/


FUNCTION createPhoneticName(inLastName IN VARCHAR2, inFirstName IN VARCHAR2, inMiddleInitial IN VARCHAR2)
  RETURN VARCHAR2 AS

v_lastname varchar2(50);
v_firstname varchar2(30);
v_midinit varchar2(1);

v_goodname varchar2(54);
v_phoneticname varchar2(9);
v_checkname varchar2(50);
v_lastposition number;
v_resultname varchar2(50);
v_found boolean;
v_found2 boolean;
v_lastchar varchar2(1);

 CURSOR phonetic_replace1_cur
   IS
      SELECT phonetic_name_order,
             from_characters,
             to_characters
        FROM PHONETIC_NAME_TRANSLATION
	   WHERE phonetic_translation_code = '1'
	ORDER BY phonetic_name_order;

 CURSOR phonetic_replace2_cur
   IS
      SELECT phonetic_name_order,
             from_characters,
             to_characters
        FROM PHONETIC_NAME_TRANSLATION
	   WHERE phonetic_translation_code = '2'
	ORDER BY phonetic_name_order;

CURSOR phonetic_replace3_cur
   IS
      SELECT phonetic_name_order,
             from_characters,
             to_characters
        FROM PHONETIC_NAME_TRANSLATION
	   WHERE phonetic_translation_code = '3'
	ORDER BY phonetic_name_order;

CURSOR phonetic_firstname_cur
   IS
      SELECT phonetic_name_order,
             from_characters,
             to_characters
        FROM PHONETIC_NAME_TRANSLATION
	   WHERE phonetic_translation_code = 'F'
	ORDER BY phonetic_name_order;

BEGIN

v_lastname := upper(inLastName);
v_firstname := upper(inFirstName);
if not inMiddleInitial is null then
   v_midinit := substr(upper(inMiddleInitial),1 ,1);
end if;
v_lastposition := 1;

for i IN 1 .. length(v_lastname)
loop
  if substr(v_lastname, i, 1) between 'A' and 'Z' then
  	  v_goodname := v_goodname || substr(v_lastname, i, 1);
  end if;
end loop;

v_found := false;
v_found2 := false;

-- search table 1 for a match starting with the first character

for cursorrec in phonetic_replace1_cur
	 loop
	 	 exit when phonetic_replace1_cur%NOTFOUND;
		  if length(v_goodname) < length(cursorrec.from_characters) then
		 	v_goodname := rpad(v_goodname, length(cursorrec.from_characters));
		 end if;
	 	 if cursorrec.from_characters = substr(v_goodname, 1, length(cursorrec.from_characters)) then
	 	 	v_lastchar := substr(v_goodname, length(cursorrec.from_characters) + (v_lastposition - 1), 1);
			v_checkname := v_checkname || cursorrec.to_characters;
			v_found := true;
			v_lastposition := v_lastposition + length(cursorrec.from_characters);
	 		exit when v_found = true;
	 	 end if;
	 end loop;

-- if we have a match on the first table then set the phonetic name
 if v_found = true then
	v_resultname := v_checkname;
	v_checkname := '';
 end if;
 v_goodname := rtrim(v_goodname);

 -- search table 2 if we did not get a hit on table 1
 if v_found = false then
    v_checkname := '';
	v_lastposition := 1;
 	 for cursorrec in phonetic_replace2_cur
	 loop
	 	 exit when phonetic_replace2_cur%NOTFOUND;
		 if length(v_goodname) < length(cursorrec.from_characters) then
		 	v_goodname := rpad(v_goodname, length(cursorrec.from_characters));
		 end if;
	 	 if cursorrec.from_characters = substr(v_goodname, 1, length(cursorrec.from_characters)) then
	 	 	v_lastchar := substr(v_goodname, length(cursorrec.from_characters) + (v_lastposition - 1), 1);
			v_checkname := v_checkname || cursorrec.to_characters;
			v_found2 := true;
			v_lastposition := v_lastposition + length(cursorrec.from_characters);
	 		exit when v_found2 = true;
		 else
		 	 v_lastchar := substr(v_goodname, 0, 1);
	 	 end if;
	 end loop;
 end if;

 -- if we have a match on the second table then set the phonetic name
 if v_found2 = true then
 	v_resultname := v_checkname;
	v_checkname := '';
 end if;

 -- nver start at the first position when checking the remainder of the name
 if v_lastposition = 1 then
 	v_lastposition := 2;
	v_checkname := substr(v_goodname, 1, 1);
 end if;

 --continue to define the last name
 while v_lastposition <= length(v_goodname)
 loop
 	 v_found := false;
 	 for cursorrec in phonetic_replace3_cur
	 loop
	 	 exit when phonetic_replace3_cur%NOTFOUND;
		 if length(substr(v_goodname, v_lastposition, length(cursorrec.from_characters))) < length(cursorrec.from_characters) then
		 	v_goodname := rpad(v_goodname, v_lastposition + length(cursorrec.from_characters));
		 end if;
	 	 if cursorrec.from_characters = substr(v_goodname, v_lastposition, length(cursorrec.from_characters)) then
		    v_lastchar := substr(v_goodname, length(cursorrec.from_characters) + (v_lastposition - 1), 1);
		    if not cursorrec.to_characters = ' ' then
	 	 	   v_checkname := v_checkname || cursorrec.to_characters;
			end if;
			v_found := true;
			v_lastposition := v_lastposition + length(cursorrec.from_characters);
			v_goodname := rtrim(v_goodname);
			exit when v_found = true;
	 	 end if;
	 end loop;
	 if v_found = false then
	    if not v_lastchar = ' ' then
		   if not length(v_goodname) < v_lastposition then
			    if v_lastchar = substr(v_goodname, v_lastposition, 1) then
				    if length(rtrim(v_goodname)) > v_lastposition then
					   v_goodname := substr(v_goodname, 1, v_lastposition - 1) || substr(v_goodname, v_lastposition + 1);
					else
					   v_goodname := substr(v_goodname, 1, v_lastposition - 1);
					end if;
				else
				    v_checkname := v_checkname || substr(v_goodname, v_lastposition, 1);
	 				v_lastposition := v_lastposition + 1;
				end if;
			else
			    v_checkname := v_checkname || substr(v_goodname, v_lastposition, 1);
	 			v_lastposition := v_lastposition + 1;
			end if;
		else
		    v_checkname := v_checkname || substr(v_goodname, v_lastposition, 1);
	 		v_lastposition := v_lastposition + 1;
		end if;
		v_goodname := rtrim(v_goodname);
		v_lastchar := ' ';
	 end if;
 end loop;

 v_resultname := v_resultname || v_checkname;

 -- remove all vowels exception the first
 v_checkname := v_resultname;
 v_resultname := '';
 v_lastposition := 0;
 for i in 1 .. length(v_checkname)
 loop
 	 if not substr(v_checkname, i, 1) in ('A', 'E', 'I', 'O', 'U') then
	    v_resultname := v_resultname || substr(v_checkname, i, 1);
	 elsif v_lastposition = 0 then
	 	v_lastposition := 1;
		v_resultname := v_resultname || substr(v_checkname, i, 1);
	 end if;
 end loop;

 v_checkname := v_resultname;
 v_resultname := substr(v_checkname, 1, 1);

 -- remove any duplicate letters
 for i IN 2 .. length(v_checkname)
 loop
	 if not substr(v_checkname, i - 1, 1) = substr(v_checkname, i, 1) then
	 	v_resultname := v_resultname || substr(v_checkname, i, 1);
	 end if;
 end loop;


 --save the results for the last name
 v_phoneticname := rpad(v_phoneticname || substr(v_resultname, 1, 5),5);

--- check the first name table
loop
 	 v_found := false;
	 if length(v_firstname) < 4 then
	 	v_checkname := rpad(v_firstname,4);
	 else
	 	v_checkname := v_firstname;
	 end if;

 	 for cursorrec in phonetic_firstname_cur
	 loop
	 	 exit when phonetic_firstname_cur%NOTFOUND;
	 	 if cursorrec.from_characters = substr(v_checkname, 1, length(cursorrec.from_characters)) then
	 	 	v_resultname := cursorrec.to_characters;
			v_found := true;
			exit when v_found = true;
	 	 end if;
	 end loop;
	 exit when v_found = true;
	 if v_found = false then
  	 	v_resultname := rpad(substr(v_firstname,1 ,3), 3);
		v_found := true;
		exit when v_found = true;
	 end if;
 end loop;

 -- move first name results to the phonetic name
  v_phoneticname := rpad(v_phoneticname || substr(v_resultname, 1, 3),8);

 -- add middle initial to the phonetic name
 if length(v_midinit) = 0 or
    v_midinit is null then
	v_phoneticname := rpad(v_phoneticname,9);
 else
    v_phoneticname := rpad(v_phoneticname || substr(v_midinit, 1, 1),9);
 end if;

 RETURN v_phoneticname;

END createPhoneticName;

END pk82028_phonetic_name;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- `Business\SAIF.Business.Claim.Person\clsClaimPersonName.vb`