PL/SQL SOURCE EXAMPLE
The following code is an
example of my work in Oracle's PL/SQL. This particular package ran two different
comparisons against U.S. Government databases, one containing Doctor's names and
DEA licenses, the other medical clinic addresses and their licenses (HIN). The
program scanned through a list of active customers (doctors and medical clinics)
and checked to see that licenses could be found in the DEA and HIBCC databases,
that these licenses were current, and that the doctor's names and clinic
addresses matched those of our active customers. This source example contains a
complete name and address matching algorithm. The package body is
included beneath it's specification. Notice that the specification contains 3
public procedures. The RUN_ALL_MATCH procedure normally would run the other two,
but the two individual procedures (DEA and HIN) could be run separately if
necessary. This package illustrates the use of user defined EXCEPTIONS for
reporting, and PL/SQL records for data passing amoung application procedures.
The package was coordinated with a PERL workflow which captured exception
messages, generated emails to appropriate parties, etc.
PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE DEAHIN_MATCH_ADDR IS
-- the following variable allows the trigger on object_attribute and
-- universal_attr to know if the change to those two tables came from
-- this package or not...
G_DEAHIN_MATCH_RUNNING BOOLEAN := FALSE;
PROCEDURE RUN_HIN_MATCH(l_run_number IN NUMBER) ;
PROCEDURE RUN_DEA_MATCH (l_run_number IN NUMBER, l_admin_id IN NUMBER);
PROCEDURE RUN_ALL_MATCH;
/******************************************************************************
NAME: DEAHIN_MATCH
PURPOSE: Address and name matching from customer master to DEA/HIBCC
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/13/2004 1. Created this package.
PARAMETERS:
INPUT:
OUTPUT:
RETURNED VALUE:
CALLED BY:
CALLS:
EXAMPLE USE: DEAHIN_MATCH.RUN_DEA_MATCH
ASSUMPTIONS:
LIMITATIONS:
ALGORITHM:
NOTES:
Here is the complete list of automatically available Auto Replace Keywords:
Object Name: DEAHIN_MATCH or DEAHIN_MATCH
Sysdate: 5/13/2004
Date/Time: 5/13/2004 1:08:06 PM
Date: 5/13/2004
Time: 1:08:06 PM
Username: (set in TOAD Options, Procedure Editor)
Table Name: %TableName% (set in the Create New Procedure dialog)
******************************************************************************/
END DEAHIN_MATCH_ADDR;
/
PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY DEAHIN_MATCH_ADDR AS
g_err_nbr NUMBER; -- for global error trapping
g_err_msg VARCHAR2(1024);
g_orgaddr_bad_count NUMBER := 0;
g_orgaddr_good_count NUMBER := 0;
g_match_bad_count NUMBER := 0;
g_match_good_count NUMBER := 0;
EXCEPTION_LOG_FAIL EXCEPTION; -- raised on some error in log routines
EXCEPTION_XLATESTOP EXCEPTION;
EXCEPTION_TOKENIZE_NAME EXCEPTION;
EXCEPTION_MAKE_MATCH EXCEPTION;
EXCEPTION_DEA_MAIN EXCEPTION;
EXCEPTION_HIN_MAIN EXCEPTION;
EXCEPTION_RUN_NUMBER EXCEPTION;
EXCEPTION_UPD_FOR_ORG EXCEPTION;
EXCEPTION_UPD_FOR_PRIM EXCEPTION;
EXCEPTION_NO_ADMIN EXCEPTION;
-- declare some tables (binary integer index) whose instantiations will hold arrays
-- of vitals... Each vital colum has an array. The indexes tie them all together
TYPE name_of_vital_table IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
TYPE otn_vital_value_table IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
TYPE std_vital_value_table IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
-- TYPE name_tokens_table is TABLE OF VARCHAR2(48) INDEX BY BINARY_INTEGER;
TYPE r_STREET_ADDR is RECORD
( stnbr varchar2(128),
stdir varchar2(128),
sttype varchar2(128),
stname varchar2(1024),
apname varchar2(128)
);
-- There will be private functions. WRITE_MATCH_TABLES() writes to the two main
-- tables holding the results of each run's analysis. The same function works for
-- both DEA and HIN. WRITE_DIRTY_MASTER() records events from searches in the MASTER
-- tables for both DEA and HIN that have unexpected results. Primarily this will be
-- duplicate DEA's and HINs with in the master database. It should not happen, but I
-- did not want to kill the program in the event of it, so lets see what we get...
-- both functions will return simply a boolean true if they succeed, false if something
-- goes wrong...
FUNCTION WRITE_MATCH_TABLES (a_run_number IN NUMBER,
a_deahin_no IN VARCHAR2,
a_rec_type IN CHAR,
a_status IN CHAR,
ab_deahin_found IN BOOLEAN,
a_mismatch_count IN NUMBER,
at_vital_names IN name_of_vital_table,
at_otn_value IN otn_vital_value_table,
at_std_value IN std_vital_value_table) RETURN boolean IS
l_result BOOLEAN := true;
l_deahin_found CHAR := 'Y';
l_status CHAR := 'Y';
l_vitals_ndx NUMBER := 0;
l_master_key NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF NOT ab_deahin_found THEN
l_deahin_found := 'N';
END IF;
IF a_status = 'DE' THEN
l_status := 'N';
END IF;
-- must capture key for vitals table...
SELECT otnseq_deahin_master_key.NEXTVAL INTO l_master_key from DUAL;
INSERT INTO OTN_DEAHIN_MISMATCH_MASTER
(
MISMATCH_MASTER_KEY,
OTN_DEAHIN,
RUN_NUMBER,
RECORD_TYPE,
DEAHIN_CURRENT,
OTN_DEAHIN_FOUND,
RECORD_DATE
) VALUES (
l_master_key,
a_deahin_no,
a_run_number,
a_rec_type,
l_status,
l_deahin_found,
sysdate
);
-- now write the OTN_DEAHIN_MISMATCH_VITALS table...
FOR l_vitals_ndx IN 1 .. a_mismatch_count LOOP
-- IF at_vital_names.EXISTS(l_vitals_ndx) THEN
INSERT INTO OTN_DEAHIN_MISMATCH_VITALS
(VITAL_KEY,
MISMATCH_MASTER_KEY,
VITAL_NAME,
VITAL_OTN_VALUE,
VITAL_STD_VALUE,
RECORD_TYPE,
RUN_NUMBER,
RECORD_DATE
) VALUES (
otnseq_deahin_vitals_key.nextval,
l_master_key,
at_vital_names(l_vitals_ndx),
at_otn_value(l_vitals_ndx),
at_std_value(l_vitals_ndx),
a_rec_type, -- redundant if normalized
a_run_number, -- redundant if normalized
sysdate
);
-- ELSE
-- dbms_output.put_line('Index '||to_char(l_vitals_ndx)||' for '||a_deahin_no||'max: '||to_char(a_mismatch_count));
-- END IF;
END LOOP;
COMMIT;
return l_result;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
l_result := FALSE;
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,128)||' run: '||to_char(a_run_number)||' type: '||a_rec_type||' DEAHIN: '||a_deahin_no||' OTN: '||at_otn_value(l_vitals_ndx)||' STD: '||at_std_value(l_vitals_ndx) ;
return l_result;
END;
FUNCTION WRITE_DIRTY_MASTER (a_run_number IN NUMBER,
a_deahin_no IN VARCHAR2,
a_rec_type IN CHAR,
a_rec_reason in VARCHAR2)
RETURN boolean IS
l_result BOOLEAN := true;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO OTN_DEAHIN_BAD
(DEAHIN_BAD_KEY,
RUN_NUMBER,
RECORD_TYPE,
DEAHIN_VALUE,
RECORD_REASON,
RECORD_DATE
) values (
OTNSEQ_DEAHIN_BAD_KEY.nextval,
a_run_number,
a_rec_type,
a_deahin_no,
a_rec_reason,
sysdate
);
COMMIT;
return l_result;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
l_result := FALSE;
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,128)||' WRITE BAD on run: '||to_char(a_run_number)||' type: '||a_rec_type||' DEAHIN: '||a_deahin_no ;
return l_result;
END;
-- XLATESTOP is called by TOKENIZE_NAME its job is to return individual WORDS, but either translated
-- or NULLed, depending... They become NULL if they are < 3 chars long, and are cannonized where appropriate
-- for example ONC becomes ONCOLOGY. Finally, stop words like OFFICE or CORP are also nulled...
FUNCTION XLATESTOP (a_word IN VARCHAR2, a_region IN VARCHAR)
RETURN VARCHAR2 IS
ret_word VARCHAR2(56) := a_word;
-- MIN_LEN NUMBER := 3;
BEGIN
-- soundex may ignore punctuation, but we can not ignore it here because
-- it affects word length and our xlate/stop list below This subroutine is
-- used by the HIN match only
ret_word := REPLACE(ret_word, '.','');
ret_word := REPLACE(ret_word, ',','');
--IF LENGTH(ret_word) < MIN_LEN THEN
-- RETURN NULL;
--END IF;
--IF LENGTH(ret_word) > 1 AND substr(ret_word,1,1) = '#' THEN
-- return substr(ret_word,2);
--END IF;
IF a_region = 'JUST_B' THEN
IF ret_word = 'ST' THEN
ret_word := 'SAINT';
END IF;
END IF;
IF ret_word = 'RD' THEN
ret_word := 'ROAD';
END IF;
IF ret_word = 'ST' THEN
ret_word := 'STREET';
END IF;
IF ret_word = 'AVE' THEN
ret_word := 'AVENUE';
END IF;
IF ret_word = 'DR' THEN
ret_word := 'DRIVE';
END IF;
IF ret_word = 'LN' THEN
ret_word := 'LANE';
END IF;
IF ret_word = 'CT' THEN
ret_word := 'COURT';
END IF;
IF ret_word = 'PL' THEN
ret_word := 'PLACE';
END IF;
IF ret_word = 'WY' THEN
ret_word := 'WAY';
END IF;
IF ret_word = 'PLZ' THEN
ret_word := 'PLAZA';
END IF;
IF ret_word = 'SQ' THEN
ret_word := 'SQUARE';
END IF;
IF ret_word = 'HWY' THEN
ret_word := 'HIGHWAY';
END IF;
IF ret_word = 'RT' THEN
ret_word := 'ROUTE';
END IF;
IF ret_word = 'PKY' OR ret_word = 'PKWY' THEN
ret_word := 'PARKWAY';
END IF;
IF ret_word = 'BLV' OR ret_word = 'BLVD' THEN
ret_word := 'BOULEVARD';
END IF;
IF ret_word = 'FWY' or ret_word = 'FRWY' THEN
ret_word := 'FREEWAY';
END IF;
IF ret_word = 'W' THEN
ret_word := 'WEST';
END IF;
IF ret_word = 'N' THEN
ret_word := 'NORTH';
END IF;
IF ret_word = 'S' THEN
ret_word := 'SOUTH';
END IF;
IF ret_word = 'E' THEN
ret_word := 'EAST';
END IF;
IF ret_word = 'NE' THEN
ret_word := 'NORTHEAST';
END IF;
IF ret_word = 'NW' THEN
ret_word := 'NORTHWEST';
END IF;
IF ret_word = 'SE' THEN
ret_word := 'SOUTHEAST';
END IF;
IF ret_word = 'SW' THEN
ret_word := 'SOUTHWEST';
END IF;
IF ret_word = 'STE' OR ret_word = 'STE#' OR ret_word = 'SUITE#' THEN
ret_word := 'SUITE';
END IF;
IF ret_word = 'CWY' OR ret_word = 'CSWY' THEN
ret_word := 'CAUSEWAY';
END IF;
IF ret_word = 'BLD' OR ret_word = 'BLDG' OR ret_word = 'BLDG#' OR ret_word = 'BLD#' THEN
ret_word := 'BUILDING';
END IF;
IF ret_word = 'FL' OR ret_word = 'FLR' THEN
ret_word := 'FLOOR';
END IF;
--IF a_region = 'BUILDING' AND (ret_word = '#' OR ret_word = 'NO' OR ret_word = 'NBR') THEN
-- ret_word := 'NUMBER';
--END IF;
--
--IF a_region = 'SUITE' AND (ret_word = '#' OR ret_word = 'NO' OR ret_word = 'NBR') THEN
-- ret_word := NULL;
--END IF;
IF ret_word = '#' OR ret_word = 'NO' OR ret_word = 'NBR' THEN
IF a_region = 'BUILDING' THEN
ret_word := 'NUMBER';
ELSE
ret_word := 'SUITE';
END IF;
END IF;
IF ret_word = '1ST' THEN
ret_word := 'FIRST';
END IF;
IF ret_word = '2ND' THEN
ret_word := 'SECOND';
END IF;
IF ret_word = '3RD' THEN
ret_word := 'THIRD';
END IF;
IF ret_word = '4TH' THEN
ret_word := 'FOURTH';
END IF;
IF ret_word = '5TH' THEN
ret_word := 'FIFTH';
END IF;
IF ret_word = '6TH' THEN
ret_word := 'SIXTH';
END IF;
IF ret_word = '7TH' THEN
ret_word := 'SEVENTH';
END IF;
IF ret_word = '8TH' THEN
ret_word := 'EIGHTH';
END IF;
IF ret_word = '9TH' THEN
ret_word := 'NINTH';
END IF;
IF ret_word = '10TH' THEN
ret_word := 'TENTH';
END IF;
IF ret_word = 'ONE' THEN
ret_word := '1';
END IF;
IF ret_word = 'TWO' THEN
ret_word := '2';
END IF;
IF ret_word = 'THREE' THEN
ret_word := '3';
END IF;
IF ret_word = 'FOUR' THEN
ret_word := '4';
END IF;
IF ret_word = 'FIVE' THEN
ret_word := '5';
END IF;
IF ret_word = 'SIX' THEN
ret_word := '6';
END IF;
IF ret_word = 'SEVEN' THEN
ret_word := '7';
END IF;
IF ret_word = 'EIGHT' THEN
ret_word := '8';
END IF;
IF ret_word = 'NINE' THEN
ret_word := '9';
END IF;
RETURN ret_word;
EXCEPTION
WHEN OTHERS THEN
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,200);
RAISE EXCEPTION_XLATESTOP;
END;
-- TOKENIZE_NAME is called from MAKE_MATCH. It return a RECORD holding a list of
-- WORDS translated by xlatestop or just passed through, and or were cannonized... This is called
-- ONCE for EACH set of names to be compared, once for the OTN name and once for the
-- HIBCC name. Used by HIN match only
FUNCTION TOKENIZE_NAME (a_word_string IN VARCHAR2)
RETURN r_STREET_ADDR IS
rl_names r_STREET_ADDR;
word_string VARCHAR2(512) := a_word_string;
word_ndx NUMBER := 0;
STATE CHAR(1) := 'A'; -- state machine. Street addr. broken into
-- 3 states. Number is 'A'
-- Direction & name is 'B' notice they can
-- switched in order
-- Suite (if any) is 'C'
-- JUST_SWITCHED_B BOOLEAN := FALSE; -- needed to tell xlator whether
-- we just found the street number
-- or not.
REGION VARCHAR2(26) ; -- enumerated to 'JUST_B', 'BUILDING', 'SUITE'
space_pos NUMBER;
--last_word BOOLEAN := FALSE;
spc CHAR(1) := ' ';
hold_word VARCHAR2(156); -- long enough for any single word?
suite_flag BOOLEAN := FALSE;
building_flag BOOLEAN := FALSE;
-- MIN_LEN NUMBER := 3;
BEGIN
-- NOTE SOUNDEX used below ignores punctuation, so non letter characters will be
-- factored out of individual words automatically. But we will replace a SLASH since
-- this affects the results and merges two words into one...
-- word_string := REPLACE(word_string,'/',' ');
word_string := REPLACE(word_string,'-',' ');
LOOP
EXIT WHEN word_string IS NULL ;
hold_word := ''; -- reset
word_ndx := word_ndx + 1;
space_pos := INSTR(word_string, spc, 1);
-- space_pos should be some positive number > 1 If it is ZERO, we have ONE word...
IF space_pos > 0 THEN
hold_word := SUBSTR(word_string,1,(space_pos - 1));
ELSE
hold_word := word_string;
END IF;
IF LENGTH(hold_word) > 1 AND substr(hold_word,1,1) = '#' THEN
STATE := 'C';
hold_word := substr(hold_word,2);
IF NOT INSTR(rl_names.apname, 'SUITE', 1) > 0 THEN
rl_names.apname := 'SUITE';
END IF;
ELSE
hold_word := xlatestop(hold_word, REGION);
IF REGION = 'JUST_B' THEN
REGION := 'MIDDLE' ;
END IF;
END IF;
IF hold_word IS NOT NULL THEN
IF hold_word = 'SUITE' OR hold_word = 'BUILDING' OR hold_word = 'FLOOR' THEN
STATE := 'C';
IF (NOT building_flag) AND hold_word = 'BUILDING' THEN
building_flag := TRUE;
REGION := 'BUILDING';
IF suite_flag THEN
rl_names.apname := rl_names.apname||' '||hold_word;
suite_flag := FALSE;
ELSE
rl_names.apname := hold_word;
END IF;
END IF;
IF (NOT suite_flag) AND (hold_word = 'SUITE' OR hold_word = 'FLOOR') THEN
suite_flag := TRUE;
REGION := 'SUITE';
IF building_flag THEN
rl_names.apname := rl_names.apname||' '||hold_word;
building_flag := FALSE;
ELSE
rl_names.apname := hold_word;
END IF;
END IF; -- note if we have a second 'SUITE' we just want to drop it
ELSIF STATE = 'C' THEN
rl_names.apname := rl_names.apname||' '||hold_word;
END IF;
IF STATE = 'B' THEN
IF hold_word = 'AVENUE'
OR hold_word = 'STREET'
OR hold_word = 'LANE'
OR hold_word = 'COURT'
OR hold_word = 'DRIVE'
OR hold_word = 'PARKWAY'
OR hold_word = 'BOULEVARD'
OR hold_word = 'WAY'
-- OR hold_word = 'FREEWAY'
OR hold_word = 'PLACE'
OR hold_word = 'PLAZA'
OR hold_word = 'SQUARE'
OR hold_word = 'CAUSEWAY'
OR hold_word = 'ROAD' THEN
IF rl_names.stname IS NOT NULL THEN
rl_names.sttype := hold_word;
ELSE
rl_names.stname := hold_word;
END IF;
ELSIF hold_word = 'NORTH'
OR hold_word = 'SOUTH'
OR hold_word = 'EAST'
OR hold_word = 'WEST'
OR hold_word = 'NORTHWEST'
OR hold_word = 'NORTHEAST'
OR hold_word = 'SOUTHEAST'
OR hold_word = 'SOUTHWEST' THEN
rl_names.stdir := rl_names.stdir||' '||hold_word;
ELSE
rl_names.stname := rl_names.stname||' '||hold_word;
rl_names.stname := RTRIM(rl_names.stname);
END IF;
END IF;
IF STATE = 'A' THEN
rl_names.stnbr := hold_word;
-- dbms_output.put_line(hold_word);
STATE := 'B';
REGION := 'JUST_B';
END IF;
END IF; -- hold_word is NOT NULL
/******
IF hold_word IS NOT NULL THEN
t_names(name_ndx) := SOUNDEX(hold_word); -- might as well take advantage of built-in soundex!
ELSE
-- must decrement name_ndx or can end up with higher count than there are values...
name_ndx := name_ndx - 1;
END IF;
******/
-- now reassign the rest of the string back to a_word_string. Note we LTRIM
-- here in case there is more than one space in front of the next word...
-- again if space_pos is 0 then we had our last word assigned already!
IF space_pos > 0 THEN
word_string := LTRIM(SUBSTR(word_string,(space_pos + 1)));
ELSE
word_string := null;
END IF;
END LOOP;
-- that was easy! Now if we are here, we've got a collection of words, so return them
-- Theoretically, we should only get here if the "one word" test above fails because the last word
-- in the string is less than the MIN_LEN after returning from xlatestop()
RETURN rl_names;
EXCEPTION
WHEN EXCEPTION_XLATESTOP THEN
RAISE;
WHEN OTHERS THEN
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,200)||'---'||a_word_string||' = '||to_char(word_ndx);
RAISE EXCEPTION_TOKENIZE_NAME;
END;
-- MAKE_MATCH is called by RUN_HIN_MATCH which passes two strings, one for OTN and one
-- for HIBCC. The function return TRUE if the strings represent the same ORGNAME and
-- false if they do not...
FUNCTION MAKE_MATCH (a_otn_string IN VARCHAR2, a_hibcc_string IN VARCHAR2,
a_run_number IN NUMBER)
RETURN BOOLEAN IS
ret_val BOOLEAN := FALSE;
v_match_min NUMBER;
v_match_made NUMBER := 0;
rl_otn_tokens r_STREET_ADDR;
rl_hibcc_tokens r_STREET_ADDR;
v_ret_val CHAR(1) := 'N';
BEGIN
v_match_made := 0;
rl_otn_tokens := tokenize_name(ltrim(rtrim(upper(a_otn_string))));
rl_hibcc_tokens := tokenize_name(ltrim(rtrim(upper(a_hibcc_string))));
IF rl_otn_tokens.stnbr = rl_hibcc_tokens.stnbr THEN
v_match_made := 35;
END IF;
IF (SOUNDEX(rl_otn_tokens.stname) = SOUNDEX(rl_hibcc_tokens.stname))
OR (rl_otn_tokens.stname IS NULL AND rl_hibcc_tokens.stname IS NULL) THEN
v_match_made := v_match_made + 15;
END IF;
--IF (rl_otn_tokens.stname = rl_hibcc_tokens.stname)
--OR (rl_otn_tokens.stname IS NULL AND rl_hibcc_tokens.stname IS NULL) THEN
-- v_match_made := v_match_made + 15;
--END IF;
IF rl_otn_tokens.stdir = rl_hibcc_tokens.stdir
OR (rl_otn_tokens.stdir IS NULL AND rl_hibcc_tokens.stdir IS NULL) THEN
v_match_made := v_match_made + 7.5;
END IF;
IF rl_otn_tokens.sttype = rl_hibcc_tokens.sttype
OR (rl_otn_tokens.sttype IS NULL AND rl_hibcc_tokens.sttype IS NULL) THEN
v_match_made := v_match_made + 2.5;
END IF;
IF rl_otn_tokens.apname = rl_hibcc_tokens.apname
OR (rl_otn_tokens.apname IS NULL AND rl_hibcc_tokens.apname IS NULL) THEN
v_match_made := v_match_made + 2.5;
END IF;
IF v_match_made >= 55 THEN
ret_val := TRUE;
v_ret_val := 'Y';
g_match_good_count := g_match_good_count + 1;
ELSE
g_match_bad_count := g_match_bad_count + 1;
END IF;
insert into otn_deahin_name_test
(
runnumber,
ostnbr,
hstnbr,
ostdir,
hstdir,
osttype,
hsttype,
ostname,
hstname,
oapname,
hapname,
decision
) values (
a_run_number,
rl_otn_tokens.stnbr,
rl_hibcc_tokens.stnbr,
rl_otn_tokens.stdir,
rl_hibcc_tokens.stdir,
rl_otn_tokens.sttype,
rl_hibcc_tokens.sttype,
rl_otn_tokens.stname,
rl_hibcc_tokens.stname,
rl_otn_tokens.apname,
rl_hibcc_tokens.apname,
v_ret_val
);
commit;
RETURN ret_val;
/*********
IF t_otn_tokens.COUNT > 0 AND t_hibcc_tokens.COUNT > 0 THEN
-- add the counts together and divide by 2 for numbers of pairs (rounded down). Set minimum match
-- to be 1/2 of that rounded up!
v_match_min := CEIL(FLOOR((t_otn_tokens.COUNT + t_hibcc_tokens.COUNT) / 2) / 2);
-- v_match_min := CEIL(t_otn_tokens.COUNT / 2);
v_match_made := 0; -- initialize
FOR v_otn_token_ndx IN 1 .. t_otn_tokens.COUNT LOOP
FOR v_hibcc_token_ndx IN 1 .. t_hibcc_tokens.COUNT LOOP
IF t_otn_tokens(v_otn_token_ndx) = t_hibcc_tokens(v_hibcc_token_ndx) THEN
v_match_made := v_match_made + 1;
EXIT;
END IF;
END LOOP;
END LOOP;
-- So, how many matches did we make?
IF v_match_made >= v_match_min THEN
ret_val := TRUE;
END IF;
END IF;
**********/
RETURN ret_val;
EXCEPTION
WHEN EXCEPTION_TOKENIZE_NAME THEN
RAISE;
WHEN EXCEPTION_XLATESTOP THEN
RAISE;
WHEN OTHERS THEN
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,200);
RAISE EXCEPTION_MAKE_MATCH;
END;
PROCEDURE RUN_HIN_MATCH (l_run_number IN NUMBER) IS
v_record_type CHAR(1) := 'H';
-- v_otn_hin_base CHAR(7);
v_master_hin HIN.BASE_HIN%TYPE;
v_master_hin_suffix HIN.SUFFIX%TYPE;
v_master_name HIN.NAME%TYPE;
v_master_addr1 HIN.ADDRESS1%TYPE;
v_master_addr2 HIN.ADDRESS2%TYPE;
v_master_state HIN.STATE%TYPE;
v_master_zip HIN.ZIP%TYPE;
v_master_date HIN.DATE_HIN_ASSIGNED%TYPE; -- date assigned
v_master_status HIN.STATUS%TYPE;
v_master_action HIN.LAST_ACTION_CODE%TYPE;
b_vitals_all_match BOOLEAN := TRUE;
b_hin_found BOOLEAN := TRUE;
b_good_log_write BOOLEAN := TRUE;
b_addr_matched BOOLEAN := FALSE;
v_bulk_ndx NUMBER;
debug_countA NUMBER := 0;
debug_countB NUMBER := 0;
-- The following tables are used with a BULK COLLECT to capture HIN info
-- when we do NOT find an exact match between a HIN in the standards table
-- and INICE. In that case we do a search on zip/state, and name.
-- and because we can get more than one record, we will bulk collect them
-- into these tables... Note these table types are not global because
-- they are specific to this match...
TYPE hibcc_hin IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
TYPE hibcc_name IS TABLE OF HIN.NAME%TYPE INDEX BY BINARY_INTEGER;
TYPE hibcc_address1 IS TABLE OF HIN.ADDRESS1%TYPE INDEX BY BINARY_INTEGER;
TYPE hibcc_address2 IS TABLE OF HIN.ADDRESS2%TYPE INDEX BY BINARY_INTEGER;
TYPE hibcc_zip IS TABLE OF HIN.ZIP%TYPE INDEX BY BINARY_INTEGER;
TYPE hibcc_status IS TABLE OF HIN.STATUS%TYPE INDEX BY BINARY_INTEGER;
t_hin hibcc_hin;
t_name hibcc_name;
t_address1 hibcc_address1;
t_address2 hibcc_address2;
t_zip hibcc_zip;
t_status hibcc_status;
-- The tables below must be manipulated as a UNIT. t_vital_names(10)
-- has an otn_value of t_otn_value(10) and a std value of t_std_value(10)
-- etc... If they ever get out of sync. results will be unpredictable
-- and unusable.
t_vital_names name_of_vital_table;
t_otn_value otn_vital_value_table;
t_std_value std_vital_value_table;
v_vitals_table_ndx NUMBER := 0;
-- If we do not substring the zip we have to account for the fact
-- that the cursor zipcode looks like N(5) or N(5)-N(4) while the
-- master (HIN standard) ZIP is N(5) or N(9) in format...
CURSOR c_hin IS
select ORG_ACCOUNT_NBR,
ORG_NAME,
ADDR1,
ADDR2,
STATE,
substr(ZIP_CODE,1,5) ZIP_CODE,
HIN_NO
from V_ORG_HIN;
BEGIN
FOR hin_rec in c_hin LOOP
BEGIN
-- must initialize all v_master variables each time around...
v_master_hin := null;
v_master_hin_suffix := null;
v_master_name := null;
v_master_addr1 := null;
v_master_addr2 := null;
v_master_state := null;
v_master_zip := null;
v_master_date := null;
v_master_status := null;
v_master_action := null;
v_vitals_table_ndx := 0;
t_vital_names . delete;
t_otn_value . delete;
t_std_value . delete;
b_vitals_all_match := TRUE;
b_hin_found := TRUE;
b_addr_matched := FALSE;
-- If we do not substring the zip we have to account for the fact
-- that the cursor zipcode looks like N(5) or N(5)-N(4) while the
-- master (HIN standard) ZIP is N(5) or N(9) in format...
SELECT BASE_HIN,
SUFFIX,
NAME,
ADDRESS1,
ADDRESS2,
STATE,
substr(ZIP,1,5) ZIP,
DATE_HIN_ASSIGNED,
STATUS,
LAST_ACTION_CODE
INTO
v_master_hin,
v_master_hin_suffix,
v_master_name,
v_master_addr1,
v_master_addr2,
v_master_state,
v_master_zip,
v_master_date,
v_master_status,
v_master_action
FROM HIN
WHERE SUBSTR(hin_rec.HIN_NO,1,7) = BASE_HIN
AND SUBSTR(hin_rec.HIN_NO,8,2) = SUFFIX;
-- debug_countA := debug_countA + 1;
-- because our 9 digit HIN is really made up of the HIBCC master BASE_HIN plus SUFFIX
-- If we are here, we seem to have an exact match to HIN_BASE plus SUFFIX, so lets make
-- sure our other vitals match, report if they do not.
-- we will build ARRAYS of vitals for this particular INICE HIN...
IF LTRIM(RTRIM(v_master_zip)) <> LTRIM(RTRIM(hin_rec.ZIP_CODE)) THEN
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'ZIPCODE';
t_otn_value(v_vitals_table_ndx) := hin_rec.ZIP_CODE;
t_std_value(v_vitals_table_ndx) := v_master_zip;
b_vitals_all_match := FALSE;
-- here if zipcode doesn't match we will also check state.
-- if zip does match we presume the state is correct.
IF v_master_state <> hin_rec.STATE THEN
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'STATE';
t_otn_value(v_vitals_table_ndx) := hin_rec.STATE;
t_std_value(v_vitals_table_ndx) := v_master_state;
END IF;
END IF;
-- Tokenize both sets of ORGNAMES. We then compare two arrays of tokens to see if we have a
-- match...
IF make_match(hin_rec.ADDR1, v_master_addr1, l_run_number) THEN
b_addr_matched := TRUE;
ELSIF hin_rec.ADDR2 IS NOT null THEN
IF make_match(hin_rec.ADDR2, v_master_addr1, l_run_number) THEN
b_addr_matched := TRUE;
ELSIF make_match(hin_rec.ADDR1||' '||hin_rec.ADDR2, v_master_addr1, l_run_number) THEN
b_addr_matched := TRUE;
END IF;
END IF;
/************
IF NOT make_match(hin_rec.ADDR1, v_master_addr1, l_run_number) THEN
-- Don't assume here there is no match. We must try our (INICE)
-- ADDR2 against the HIN master ADDRESS1 because our ADDR2 is more
-- often a real address 1!
IF NOT make_match(hin_rec.ADDR2, v_master_addr1, l_run_number) THEN
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'ORGADDRESS';
IF hin_rec.ADDR2 IS NOT NULL THEN
t_otn_value(v_vitals_table_ndx) := substr(upper(hin_rec.ADDR1||'//'||hin_rec.ADDR2),1,128);
ELSE
t_otn_value(v_vitals_table_ndx) := upper(hin_rec.ADDR1);
END IF;
t_std_value(v_vitals_table_ndx) := v_master_addr1;
b_vitals_all_match := FALSE;
END IF;
END IF;
*************/
IF NOT b_addr_matched THEN
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'ORGADDRESS';
IF hin_rec.ADDR2 IS NOT NULL THEN
t_otn_value(v_vitals_table_ndx) := substr(upper(hin_rec.ADDR1||'//'||hin_rec.ADDR2),1,128);
ELSE
t_otn_value(v_vitals_table_ndx) := upper(hin_rec.ADDR1);
END IF;
t_std_value(v_vitals_table_ndx) := v_master_addr1;
b_vitals_all_match := FALSE;
g_orgaddr_bad_count := g_orgaddr_bad_count + 1;
ELSE
g_orgaddr_good_count := g_orgaddr_good_count + 1;
END IF;
-- OK, now set up the write to the master and vitals tables... Call the write function.
IF NOT b_vitals_all_match THEN
b_good_log_write := write_match_tables(l_run_number,
hin_rec.hin_no,
v_record_type,
v_master_status,
b_hin_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
END IF;
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
-- and loop around to get the next row.
EXCEPTION
WHEN TOO_MANY_ROWS THEN
-- OK, major problem here there is a duplicate HIN in the master??
-- Note this in the "dirty master" log.
b_good_log_write := write_dirty_master(l_run_number,
v_master_hin||v_master_hin_suffix,
v_record_type,
'HIN '||v_master_hin||'Plus SUFFIX '||v_master_hin_suffix||' HAS MORE THAN ONE ROW IN HIN MASTER');
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
WHEN NO_DATA_FOUND THEN
-- here is our biggest job. We don't find the exact match, so what do we have that might
-- match.
b_hin_found := FALSE;
-- debug_countB := debug_countB + 1;
SELECT BASE_HIN||SUFFIX,
NAME,
ADDRESS1,
ADDRESS2,
substr(ZIP,1,5) ZIP,
STATUS
BULK COLLECT INTO
t_hin,
t_name,
t_address1,
t_address2,
t_zip,
t_status
FROM HIN
WHERE hin_rec.ZIP_CODE = ZIP;
IF t_hin.COUNT > 0 THEN
-- found one by zip and name
FOR v_bulk_ndx IN t_hin.FIRST .. t_hin.LAST LOOP
IF make_match(hin_rec.ORG_NAME,t_name(v_bulk_ndx),l_run_number) THEN
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'HIN_HIN_NBR';
t_otn_value(v_vitals_table_ndx) := hin_rec.HIN_NO;
t_std_value(v_vitals_table_ndx) := t_hin(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'ORGNAME';
t_otn_value(v_vitals_table_ndx) := upper(hin_rec.ORG_NAME);
t_std_value(v_vitals_table_ndx) := t_name(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'ADDRESS1';
t_otn_value(v_vitals_table_ndx) := hin_rec.ADDR1;
t_std_value(v_vitals_table_ndx) := t_address1(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'ADDRESS2';
t_otn_value(v_vitals_table_ndx) := hin_rec.ADDR2;
t_std_value(v_vitals_table_ndx) := t_address1(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'STATUS';
t_otn_value(v_vitals_table_ndx) := null;
t_std_value(v_vitals_table_ndx) := t_status(v_bulk_ndx);
END IF;
END LOOP;
b_good_log_write := write_match_tables(l_run_number,
hin_rec.hin_no,
v_record_type,
v_master_status,
b_hin_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
ELSE
-- no find by zip or state for org_name.
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'NO MATCH FOUND';
t_otn_value(v_vitals_table_ndx) := UPPER(LTRIM(RTRIM(hin_rec.ORG_NAME)));
t_std_value(v_vitals_table_ndx) := null;
b_good_log_write := write_match_tables(l_run_number,
hin_rec.hin_no,
v_record_type,
v_master_status,
b_hin_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
END IF;
END;
END LOOP;
dbms_output.put_line('Match thinks '||to_char(g_match_bad_count)||' are bad');
dbms_output.put_line('Match thinks '||to_char(g_match_good_count)||' are good');
dbms_output.put_line('RUN_HIN thinks '||to_char(g_orgaddr_bad_count)||' are bad');
dbms_output.put_line('RUN_HIN thinks '||to_char(g_orgaddr_good_count)||' are good');
-- dbms_output.put_line('hit '||to_char(debug_countA)||' no hit '||to_char(debug_countB));
EXCEPTION
WHEN EXCEPTION_LOG_FAIL THEN
RAISE;
WHEN EXCEPTION_MAKE_MATCH THEN
RAISE;
WHEN EXCEPTION_TOKENIZE_NAME THEN
RAISE;
WHEN EXCEPTION_XLATESTOP THEN
RAISE;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,200);
RAISE EXCEPTION_HIN_MAIN;
END RUN_HIN_MATCH;
PROCEDURE RUN_DEA_MATCH (l_run_number IN NUMBER,
l_admin_id IN NUMBER) IS
v_record_type CHAR(1) := 'D';
v_master_dea DEA.DEA_REGISTRATION_NUMBER%TYPE;
v_master_expire_date DEA.EXPIRATION_DATE%TYPE;
v_master_addr1 DEA.ADDRESS1%TYPE;
v_master_addr2 DEA.ADDRESS2%TYPE;
v_master_addr3 DEA.ADDRESS3%TYPE;
v_master_addr4 DEA.ADDRESS4%TYPE;
v_master_addr5 DEA.ADDRESS5%TYPE;
v_master_state DEA.ADDRESS5%TYPE;
v_master_zip DEA.ZIPCODE%TYPE;
b_good_log_write BOOLEAN := TRUE;
b_vitals_all_match BOOLEAN := TRUE;
b_dea_found BOOLEAN := TRUE;
v_master_status CHAR(2) := 'AC'; -- means ACtive. DEA has not expired.
v_exp_date_diff NUMBER;
v_org_upd_count NUMBER := 0;
v_prim_upd_count NUMBER := 0;
v_comma_pos NUMBER := 0;
v_dea_last_name VARCHAR2(128);
-- The following tables are used with a BULK COLLECT to capture DEA info
-- when we do NOT find an exact match between a DEA in the standards table
-- and INICE. In that case we do a search on zip/state, and last name.
-- and because we can get more than one record, we will bulk collect them
-- into these tables... Note these table types are not global because
-- they are specific to this match...
TYPE dea_registration_number IS TABLE OF DEA.DEA_REGISTRATION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
TYPE dea_expiration_date IS TABLE OF DEA.EXPIRATION_DATE%TYPE INDEX BY BINARY_INTEGER;
TYPE dea_address IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
TYPE dea_zipcode IS TABLE OF DEA.ZIPCODE%TYPE INDEX BY BINARY_INTEGER;
TYPE dea_una_obj_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
t_registration_number dea_registration_number;
t_expiration_date dea_expiration_date;
t_address1 dea_address;
t_address2 dea_address;
t_address3 dea_address;
t_address4 dea_address;
t_address5 dea_address;
t_zipcode dea_zipcode;
t_una_obj_id dea_una_obj_id;
v_bulk_ndx NUMBER;
-- The tables below must be manipulated as a UNIT. t_vital_names(10)
-- has an otn_value of t_otn_value(10) and a std value of t_std_value(10)
-- etc... If they ever get out of sync. results will be unpredictable
-- and unusable.
-- Note, these table types are declared globally at top of package since
-- they are used in both HIN and DEA matches
t_vital_names name_of_vital_table;
t_otn_value otn_vital_value_table;
t_std_value std_vital_value_table;
v_vitals_table_ndx NUMBER := 0;
CURSOR c_dea IS
select USER_ORG_ID,
USER_ID,
FIRST_NAME,
LAST_NAME,
ORG_ACCOUNT_NBR,
ORG_NAME,
ADDR1,
ADDR2,
STATE,
SUBSTR(ZIP_CODE,1,5) ZIP_CODE,
DEA_NO,
DEA_EXP_DATE
from V_USER_ORG_DEA;
BEGIN
FOR dea_rec in c_dea LOOP
-- initialize everything
v_master_dea := null;
v_master_expire_date := null;
v_master_addr1 := null;
v_master_addr2 := null;
v_master_addr3 := null;
v_master_addr4 := null;
v_master_addr5 := null;
v_master_state := null;
v_master_zip := null;
v_comma_pos := 0;
v_dea_last_name := null;
b_vitals_all_match := TRUE;
b_dea_found := TRUE;
v_master_status := 'AC'; -- means ACtive. DEA has not expired.
t_vital_names.delete;
t_otn_value.delete;
t_std_value.delete;
t_una_obj_id.delete;
v_vitals_table_ndx := 0;
-- v_bulk_ndx := 0;
BEGIN
SELECT D.DEA_REGISTRATION_NUMBER,
D.EXPIRATION_DATE,
D.ADDRESS1,
D.ADDRESS2,
D.ADDRESS3,
D.ADDRESS4,
D.ADDRESS5,
D.STATE,
D.ZIPCODE
INTO
v_master_dea,
v_master_expire_date,
v_master_addr1,
v_master_addr2,
v_master_addr3,
v_master_addr4,
v_master_addr5,
v_master_state,
v_master_zip
FROM DEA D
WHERE D.DEA_REGISTRATION_NUMBER = dea_rec.DEA_NO;
-- we found a match. Is the DEA expired? Does our expiration
-- date match the DEA source?
-- OTN expiration date is always 1 day earlier than the DEA.
IF trunc(v_master_expire_date) - trunc(sysdate) < 1 THEN
v_master_status := 'DE'; -- changed from default 'AC' for delete
b_vitals_all_match := FALSE;
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'EXPIRED_STD';
t_otn_value(v_vitals_table_ndx) := to_char(dea_rec.DEA_EXP_DATE,'YYYY-MM-DD');
t_std_value(v_vitals_table_ndx) := to_char(v_master_expire_date,'YYYY-MM-DD');
ELSE
-- we are not expired according to the DEA, but we could be expired and therefore
-- updatable in our own DB...
v_exp_date_diff := trunc(v_master_expire_date) - trunc(dea_rec.DEA_EXP_DATE);
IF abs(v_exp_date_diff) > 1 THEN
-- dates do not match! We will UPDATE!
BEGIN
-- update the expiration date in for the owning org.
-- we get the una_obj_id into a variable now cause we need it in the
-- autit insert
/* This is going to have to be a bulk collect...
SELECT unique(una_obj_id) INTO v_una_obj_id
FROM UNIVERSAL_ATTR
WHERE UNA_ATR_ID = 273
AND UNA_STR_VAL = dea_rec.DEA_NO;
-- AND ROWNUM < 2;
*/
SELECT una_obj_id BULK COLLECT INTO t_una_obj_id
FROM UNIVERSAL_ATTR
WHERE UNA_ATR_ID = 273
AND UNA_STR_VAL = dea_rec.DEA_NO;
UPDATE UNIVERSAL_ATTR
SET UNA_DT_VAL = v_master_expire_date
WHERE UNA_ATR_ID = 275
AND UNA_OBJ_ID IN (SELECT UNA_OBJ_ID FROM UNIVERSAL_ATTR
WHERE UNA_ATR_ID = 273
AND UNA_STR_VAL = dea_rec.DEA_NO);
IF SQL%NOTFOUND THEN
-- major problem. There has to be AT LEAST ONE
-- but we can't stop all processing for this, so we just dump a
-- message...
DBMS_OUTPUT.PUT_LINE('ERROR finding owning organization for DEA '||dea_rec.DEA_NO);
ELSE
-- Insert record into audit table for this update
FOR v_bulk_ndx IN t_una_obj_id.FIRST .. t_una_obj_id.LAST LOOP
INSERT INTO OTN_DEAHIN_ATTR_AUDIT
(AUDIT_KEY,
ORIG_VAL,
NEW_VAL,
WHAT_CHANGED,
CHANGE_DATE,
OBJ_UNA_ID,
MODIFIED_BY
) values (
OTNSEQ_DEAHIN_AUDIT_KEY.nextval,
dea_rec.DEA_EXP_DATE,
v_master_expire_date,
'USERORGDEAEXPIRATION',
sysdate,
t_una_obj_id(v_bulk_ndx),
l_admin_id
);
v_org_upd_count := v_org_upd_count + 1;
END LOOP;
COMMIT;
-- v_org_upd_count := v_org_upd_count + 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
rollback;
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,128)||' With obj Id: '||t_una_obj_id(v_bulk_ndx);
RAISE EXCEPTION_UPD_FOR_ORG;
END;
BEGIN
-- now same thing for obj_atr if this happens to be a "primary dea"
-- note that finding NONE here is legit...
/* this will have to be a bulk collect
SELECT unique(oba_obj_id) INTO v_una_obj_id
FROM OBJECT_ATTRIBUTE
WHERE OBA_ATR_ID = 241
AND OBA_STR_VAL = dea_rec.DEA_NO;
--AND ROWNUM < 2;
*/
t_una_obj_id.delete;
SELECT oba_obj_id BULK COLLECT INTO t_una_obj_id
FROM OBJECT_ATTRIBUTE
WHERE OBA_ATR_ID = 241
AND OBA_STR_VAL = dea_rec.DEA_NO;
UPDATE OBJECT_ATTRIBUTE
SET OBA_DT_VAL = v_master_expire_date
WHERE OBA_ATR_ID = 243
AND OBA_OBJ_ID IN (SELECT OBA_OBJ_ID FROM OBJECT_ATTRIBUTE
WHERE OBA_ATR_ID = 241
AND OBA_STR_VAL = dea_rec.DEA_NO);
IF SQL%NOTFOUND THEN
null;
ELSE
FOR v_bulk_ndx IN t_una_obj_id.FIRST .. t_una_obj_id.LAST LOOP
INSERT INTO OTN_DEAHIN_ATTR_AUDIT
(AUDIT_KEY,
ORIG_VAL,
NEW_VAL,
WHAT_CHANGED,
CHANGE_DATE,
OBJ_UNA_ID,
MODIFIED_BY
) values (
OTNSEQ_DEAHIN_AUDIT_KEY.nextval,
dea_rec.DEA_EXP_DATE,
v_master_expire_date,
'ORGDEAEXPIRATION',
sysdate,
t_una_obj_id(v_bulk_ndx),
l_admin_id
);
v_prim_upd_count := v_prim_upd_count + 1;
END LOOP;
COMMIT;
-- v_prim_upd_count := v_prim_upd_count + 1;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null; -- note we trap explicitly because there
-- may legitimately not be a value here.
WHEN OTHERS THEN
rollback;
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,128)||' With obj ID: '||t_una_obj_id(v_bulk_ndx);
RAISE EXCEPTION_UPD_FOR_PRIM;
END;
END IF;
END IF;
-- See if zipcode matches and last name which
-- in the DEA record is found in address1 as 'last, first title'
IF LTRIM(RTRIM(v_master_zip)) = LTRIM(RTRIM(dea_rec.ZIP_CODE)) THEN
-- ZipCode matches, so check the doctor's last name found in
v_comma_pos := INSTR(v_master_addr1,',',1);
IF v_comma_pos > 0 THEN
-- expected condition. We have end of last name...
v_dea_last_name := substr(v_master_addr1,1,(v_comma_pos - 1));
IF UPPER(LTRIM(RTRIM(v_dea_last_name))) != UPPER(LTRIM(RTRIM(dea_rec.LAST_NAME))) THEN
-- No match on name so dump values...
b_vitals_all_match := FALSE;
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'LASTNAME';
t_otn_value(v_vitals_table_ndx) := UPPER(dea_rec.LAST_NAME);
t_std_value(v_vitals_table_ndx) := UPPER(v_dea_last_name);
END IF;
ELSE
-- something is wrong with the structure of the name
-- dump it.
b_vitals_all_match := FALSE;
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'LASTNAME_STRUCT';
t_otn_value(v_vitals_table_ndx) := UPPER(LTRIM(RTRIM(dea_rec.LAST_NAME||', '||dea_rec.FIRST_NAME)));
t_std_value(v_vitals_table_ndx) := UPPER(LTRIM(RTRIM(v_master_addr1)));
END IF;
ELSE
-- we do NOT have a match even on zip, so dump all values to the report!
b_vitals_all_match := FALSE;
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'LASTNAME_BADZIP';
t_otn_value(v_vitals_table_ndx) := UPPER(LTRIM(RTRIM(dea_rec.LAST_NAME||', '||dea_rec.FIRST_NAME)));
t_std_value(v_vitals_table_ndx) := UPPER(v_master_addr1);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'ZIPCODE';
t_otn_value(v_vitals_table_ndx) := dea_rec.ZIP_CODE;
t_std_value(v_vitals_table_ndx) := v_master_zip;
END IF;
-- We have gathered everything for a DEA FOUND report on this
-- DEA... Lets dump it to report...
IF NOT b_vitals_all_match THEN
b_good_log_write := write_match_tables(l_run_number,
dea_rec.dea_no,
v_record_type,
v_master_status,
b_dea_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
END IF;
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
-- OK, major problem here there is a duplicate DEA in the master??
-- Note this in the "dirty master" log.
b_good_log_write := write_dirty_master(l_run_number,
v_master_dea,
v_record_type,
'DEA HAS MORE THAN ONE ROW IN DEA MASTER');
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
WHEN EXCEPTION_UPD_FOR_ORG THEN
RAISE;
WHEN EXCEPTION_UPD_FOR_PRIM THEN
RAISE;
WHEN NO_DATA_FOUND THEN
-- here is our biggest job. We don't find the exact match, so what do we have that might
-- match. NOTE that from here on down, with BULK COLLECT, Oracle no longer behaves as
-- expected with a NO_DATA_FOUND error if an implicit cursor doesn't find anything
-- Thus we have to test the size of the v_bulk_ndx ourselves...
-- First, however, lets just see if our DEA looks expired by more than 30 days. If it is, we probably haven't
-- done business with these folks in a while, so we can just report on the expired DEA and
-- leave it at that...
b_dea_found := FALSE;
IF trunc(sysdate) - dea_rec.DEA_EXP_DATE > 30 THEN
v_master_status := 'DE';
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_EXPIRE_DATE';
t_otn_value(v_vitals_table_ndx) := trunc(dea_rec.DEA_EXP_DATE);
t_std_value(v_vitals_table_ndx) := null;
b_good_log_write := write_match_tables(l_run_number,
dea_rec.dea_no,
v_record_type,
v_master_status,
b_dea_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
ELSE
-- everything else under here means we are looking for some probable
-- match because our DEA has not yet expired...
SELECT D.DEA_REGISTRATION_NUMBER,
D.EXPIRATION_DATE,
D.ADDRESS1,
D.ADDRESS2,
D.ADDRESS3,
D.ADDRESS4,
D.ADDRESS5
BULK COLLECT INTO
t_registration_number,
t_expiration_date,
t_address1,
t_address2,
t_address3,
t_address4,
t_address5
FROM DEA D
WHERE LTRIM(RTRIM(D.ZIPCODE)) = LTRIM(RTRIM(dea_rec.ZIP_CODE))
AND INSTR(D.ADDRESS1, UPPER(LTRIM(RTRIM(dea_rec.LAST_NAME))), 1) > 0;
IF t_registration_number.COUNT > 0 THEN
-- found a match on ZIP and Last name, so lets dump it.
FOR v_bulk_ndx IN t_registration_number.FIRST .. t_registration_number.LAST LOOP
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_DEA_NBR';
t_otn_value(v_vitals_table_ndx) := dea_rec.DEA_NO;
t_std_value(v_vitals_table_ndx) := t_registration_number(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_EXPIRE_DATE';
t_otn_value(v_vitals_table_ndx) := to_char(dea_rec.DEA_EXP_DATE,'YYYY-MM-DD');
t_std_value(v_vitals_table_ndx) := to_char(t_expiration_date(v_bulk_ndx), 'YYYY-MM-DD');
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_NAME';
t_otn_value(v_vitals_table_ndx) := UPPER(dea_rec.LAST_NAME||', '||dea_rec.FIRST_NAME);
t_std_value(v_vitals_table_ndx) := t_address1(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS2';
t_otn_value(v_vitals_table_ndx) := dea_rec.ADDR1;
t_std_value(v_vitals_table_ndx) := t_address2(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS3';
t_otn_value(v_vitals_table_ndx) := dea_rec.ADDR2;
t_std_value(v_vitals_table_ndx) := t_address3(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS4';
t_otn_value(v_vitals_table_ndx) := null;
t_std_value(v_vitals_table_ndx) := t_address4(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS5';
t_otn_value(v_vitals_table_ndx) := null;
t_std_value(v_vitals_table_ndx) := t_address5(v_bulk_ndx);
END LOOP;
b_good_log_write := write_match_tables(l_run_number,
dea_rec.dea_no,
v_record_type,
v_master_status,
b_dea_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
ELSE
SELECT D.DEA_REGISTRATION_NUMBER,
D.EXPIRATION_DATE,
D.ADDRESS1,
D.ADDRESS2,
D.ADDRESS3,
D.ADDRESS4,
D.ADDRESS5,
D.ZIPCODE
BULK COLLECT INTO
t_registration_number,
t_expiration_date,
t_address1,
t_address2,
t_address3,
t_address4,
t_address5,
t_zipcode
FROM DEA D
WHERE UPPER(D.STATE) = UPPER(LTRIM(RTRIM(dea_rec.STATE)))
AND INSTR(D.ADDRESS1, UPPER(LTRIM(RTRIM(dea_rec.LAST_NAME))), 1) > 0;
IF t_registration_number.COUNT > 0 THEN
-- Found a match for the STATE and last name.
FOR v_bulk_ndx IN t_registration_number.FIRST .. t_registration_number.LAST LOOP
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_DEA_NBR';
t_otn_value(v_vitals_table_ndx) := dea_rec.DEA_NO;
t_std_value(v_vitals_table_ndx) := t_registration_number(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_EXPIRE_DATE';
t_otn_value(v_vitals_table_ndx) := to_char(dea_rec.DEA_EXP_DATE,'YYYY-MM-DD');
t_std_value(v_vitals_table_ndx) := to_char(t_expiration_date(v_bulk_ndx), 'YYYY-MM-DD');
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_NAME';
t_otn_value(v_vitals_table_ndx) := UPPER(dea_rec.LAST_NAME||', '||dea_rec.FIRST_NAME);
t_std_value(v_vitals_table_ndx) := t_address1(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS2';
t_otn_value(v_vitals_table_ndx) := dea_rec.ADDR1;
t_std_value(v_vitals_table_ndx) := t_address2(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS3';
t_otn_value(v_vitals_table_ndx) := dea_rec.ADDR2;
t_std_value(v_vitals_table_ndx) := t_address3(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS4';
t_otn_value(v_vitals_table_ndx) := null;
t_std_value(v_vitals_table_ndx) := t_address4(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ADDRESS5';
t_otn_value(v_vitals_table_ndx) := null;
t_std_value(v_vitals_table_ndx) := t_address5(v_bulk_ndx);
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'DEA_ZIPCODE';
t_otn_value(v_vitals_table_ndx) := dea_rec.ZIP_CODE;
t_std_value(v_vitals_table_ndx) := t_zipcode(v_bulk_ndx);
END LOOP;
b_good_log_write := write_match_tables(l_run_number,
dea_rec.dea_no,
v_record_type,
v_master_status,
b_dea_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
ELSE
-- no match on zip or state for last name. lets
-- just report no match.
v_vitals_table_ndx := v_vitals_table_ndx + 1;
t_vital_names(v_vitals_table_ndx) := 'NO MATCH FOUND';
t_otn_value(v_vitals_table_ndx) := UPPER(LTRIM(RTRIM(dea_rec.LAST_NAME||', '||dea_rec.FIRST_NAME)));
t_std_value(v_vitals_table_ndx) := null;
b_good_log_write := write_match_tables(l_run_number,
dea_rec.dea_no,
v_record_type,
v_master_status,
b_dea_found,
v_vitals_table_ndx,
t_vital_names,
t_otn_value,
t_std_value);
IF NOT b_good_log_write THEN
-- something happened to the log. RAISE a global exception
-- so we can bomb from the main routine...
RAISE EXCEPTION_LOG_FAIL;
END IF;
END IF; -- IF STATE not found
END IF; -- IF ZIP not found
END IF; -- OTN DEA EXPIRED
END;
END LOOP;
dbms_output.put_line('INFO: '||v_org_upd_count||' DEA expiration dates updated for owner orgs');
dbms_output.put_line('INFO: '||v_prim_upd_count||' DEA expiration dates updated for primary owners');
EXCEPTION
WHEN EXCEPTION_LOG_FAIL THEN
RAISE;
WHEN EXCEPTION_UPD_FOR_ORG THEN
RAISE;
WHEN EXCEPTION_UPD_FOR_PRIM THEN
RAISE;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,200);
RAISE EXCEPTION_DEA_MAIN;
END RUN_DEA_MATCH;
PROCEDURE RUN_ALL_MATCH IS
v_run_number NUMBER;
v_admin_id NUMBER;
BEGIN
G_DEAHIN_MATCH_RUNNING := TRUE;
BEGIN
SELECT max(RUN_NUMBER) into v_run_number FROM OTN_DEAHIN_MISMATCH_MASTER;
v_run_number := v_run_number + 1;
IF v_run_number IS NULL THEN
v_run_number := 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,200);
RAISE EXCEPTION_RUN_NUMBER;
END;
BEGIN
SELECT usa_id INTO v_admin_id
FROM USER_ACCOUNT
WHERE USA_FIRST_NM = 'DEAHIN'
AND USA_LAST_NM = 'Administrator';
EXCEPTION
WHEN OTHERS THEN
g_err_nbr := SQLCODE;
g_err_msg := substr(SQLERRM,1,200);
RAISE EXCEPTION_NO_ADMIN;
END;
RUN_HIN_MATCH(v_run_number);
-- RUN_DEA_MATCH(v_run_number, v_admin_id);
dbms_output.put_line('INFO: Program ended normally');
G_DEAHIN_MATCH_RUNNING := FALSE;
EXCEPTION
WHEN EXCEPTION_LOG_FAIL THEN
dbms_output.put_line('EXCEPTION LOG FAILURE BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION LOG FAILURE END REPORT');
WHEN EXCEPTION_DEA_MAIN THEN
dbms_output.put_line('EXCEPTION DEA MAIN PROC BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION DEA MAIN END REPORT');
WHEN EXCEPTION_MAKE_MATCH THEN
dbms_output.put_line('EXCEPTION HIN MAKE_MATCH FUNCTION BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION HIN MAKE_MATCH FUNCTION END REPORT');
WHEN EXCEPTION_TOKENIZE_NAME THEN
dbms_output.put_line('EXCEPTION HIN TOKENIZE_NAME FUNCTION BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION HIN TOKENIZE_NAME FUNCTION END REPORT');
WHEN EXCEPTION_XLATESTOP THEN
dbms_output.put_line('EXCEPTION HIN XLATESTOP FUNCTION BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION HIN XLATESTOP FUNCTION END REPORT');
WHEN EXCEPTION_HIN_MAIN THEN
dbms_output.put_line('EXCEPTION HIN MAIN BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION HIN MAIN END REPORT');
WHEN EXCEPTION_RUN_NUMBER THEN
dbms_output.put_line('EXCEPTION RUN NUMBER BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION RUN NUMBER END REPORT');
WHEN EXCEPTION_UPD_FOR_ORG THEN
dbms_output.put_line('EXCEPTION SELECTION FOR DEA ORG OWNER BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION DEA ORG OWNER END REPORT');
WHEN EXCEPTION_UPD_FOR_PRIM THEN
dbms_output.put_line('EXCEPTION SELECTION FOR PRIMARY DEA BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION PRIMARY DEA END REPORT');
WHEN EXCEPTION_NO_ADMIN THEN
dbms_output.put_line('EXCEPTION NO ADMINISTRATOR ID IN USER_ACCOUNT TABLE BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(g_err_nbr));
dbms_output.put_line('EXCEPTION SQLERRM '||g_err_msg);
dbms_output.put_line('EXCEPTION NO ADMIN ID END REPORT');
WHEN OTHERS THEN
dbms_output.put_line('EXCEPTION OTHERS IN RUN PROC BEGIN REPORT');
dbms_output.put_line('EXCEPTION SQLCODE '||to_char(SQLCODE));
dbms_output.put_line('EXCEPTION SQLERRM '||substr(SQLERRM,1,200));
dbms_output.put_line('EXCEPTION OTHERS IN RUN PROC END REPORT');
END RUN_ALL_MATCH;
END DEAHIN_MATCH_ADDR;
/
Mail to mjr