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;