[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_DUP_PERSON
Source
1 PACKAGE BODY Igs_Pe_Dup_Person AS
2 /* $Header: IGSPE04B.pls 120.0 2005/06/01 22:03:02 appldev noship $ */
3 PROCEDURE FIND_DUPLICATES
4 (
5 X_MATCH_SET_ID IN VARCHAR2,
6 X_SURNAME IN VARCHAR2,
7 X_GIVEN_NAMES IN VARCHAR2,
8 X_BIRTH_DT IN DATE,
9 X_SEX IN VARCHAR2,
10 X_DUP_FOUND OUT NOCOPY VARCHAR2,
11 X_WHERE_CLAUSE OUT NOCOPY VARCHAR2,
12 X_EXACT_PARTIAL IN OUT NOCOPY VARCHAR2,
13 X_PERSON_ID IN NUMBER DEFAULT NULL,
14 X_PREF_ALTERNATE_ID IN VARCHAR2 DEFAULT NULL
15 ) IS
16 /*
17 || Created By : prabhat.patel
18 || Created On : 09-OCT-2002
19 || Purpose : Handles the Unique Constraint logic defined for the columns.
20 || Known limitations, enhancements or remarks :
21 || Change History :
22 || Who When What
23 || ssaleem 10-nov-2004 3877652 - Handling Inactive Persons
24 || asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
25 || asbala 15-SEP-2003 3049826: Added code to check value of
26 Profile Option: Duplicate Match Criteria Source Type (Value should be 'MANUAL')
27 || pkpatel 4-MAY-2003 PKM Issue to use bind variable
28 || (reverse chronological order - newest change first)
29 */
30
31 lv_errbuf VARCHAR2(100);
32 lv_retcode VARCHAR2(1);
33 l_Select_Clause VARCHAR2(4000);
34 lv_Do_Partial VARCHAR2(1);
35 l_Ext_Cursor NUMBER;
36 lnRows NUMBER(5);
37 l_where_clause VARCHAR2(32767) := ' person_id IN ( ';
38 l_person_id hz_parties.party_id%TYPE;
39 l_match_found BOOLEAN := FALSE;
40 l_match_set_id igs_pe_match_sets_all.match_set_id%TYPE;
41
42 -- Cursor to check value of Profile Option: Duplicate Match Criteria
43 CURSOR c_match_set_criteria (cp_system_source_type igs_pe_src_types.system_source_type%TYPE,
44 cp_match_set_id igs_pe_match_sets_all.match_set_id%TYPE,
45 cp_closed_ind igs_pe_match_sets_all.closed_ind%TYPE) IS
46 SELECT match.match_set_id
47 FROM igs_pe_match_sets_all match,igs_pe_src_types src
48 WHERE src.system_source_type = cp_system_source_type AND
49 src.source_type_id = match.source_type_id AND
50 match.match_set_id = cp_match_set_id AND
51 match.closed_ind = cp_closed_ind;
52
53 -- cursor to populate global variable g_partial_if_null
54 CURSOR c_get_partial_if_null(cp_match_set_id igs_pe_match_sets.partial_if_null%TYPE) IS
55 SELECT partial_if_null,exclude_inactive_ind
56 FROM igs_pe_match_sets
57 WHERE match_set_id = cp_match_set_id;
58
59 partial_if_null_rec c_get_partial_if_null%ROWTYPE;
60
61 -- cursor to populate the PL/SQL tables
62 CURSOR c_matchset_data_cur(cp_match_set_id igs_pe_mtch_set_data_all.match_set_id%TYPE) IS
63 SELECT data_element, drop_if_null, partial_include, exact_include
64 FROM igs_pe_mtch_set_data_all
65 WHERE match_set_id = cp_match_set_id;
66
67 matchset_data_rec c_matchset_data_cur%ROWTYPE;
68 l_count_exact NUMBER;
69 l_count_partial NUMBER;
70 l_profile_value VARCHAR2(60);
71 BEGIN
72 -- To check value of Profile : Duplicate Match Criteria and pass appropriate error message
73 l_profile_value := fnd_profile.VALUE('IGS_PE_DUP_MATCH_CRITERIA');
74
75 IF l_profile_value IS NULL THEN
76 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PROF_DUP_CRTRIA_MANUAL');
77 APP_EXCEPTION.RAISE_EXCEPTION;
78 ELSE
79 OPEN c_match_set_criteria('MANUAL',TO_NUMBER(l_profile_value),'N');
80 FETCH c_match_set_criteria INTO l_match_set_id;
81 IF c_match_set_criteria%NOTFOUND THEN
82 CLOSE c_match_set_criteria;
83 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PROF_DUP_CRTRIA_MANUAL');
84 APP_EXCEPTION.RAISE_EXCEPTION;
85 END IF;
86 CLOSE c_match_set_criteria;
87 END IF;
88
89 Igs_Pe_Identify_Dups.g_match_set_id := x_match_set_id;
90 OPEN c_get_partial_if_null(x_match_set_id);
91 FETCH c_get_partial_if_null INTO partial_if_null_rec;
92 CLOSE c_get_partial_if_null;
93
94 Igs_Pe_Identify_Dups.g_partial_if_null := partial_if_null_rec.partial_if_null;
95 Igs_Pe_Identify_Dups.g_exclude_inactive_ind := partial_if_null_rec.exclude_inactive_ind;
96
97 -- Populate the PL/SQL tables and other global variables used for caching :- Bug 3130316
98 l_count_exact := 1;
99 l_count_partial := 1;
100 FOR matchset_data_rec IN c_matchset_data_cur(x_match_set_id) LOOP
101 IF matchset_data_rec.data_element NOT IN ('SURNAME','GIVEN_NAME_1_CHAR') THEN
102 IF matchset_data_rec.exact_include = 'Y' THEN
103 Igs_Pe_Identify_Dups.g_matchset_exact(l_count_exact).data_element := matchset_data_rec.data_element;
104 Igs_Pe_Identify_Dups.g_matchset_exact(l_count_exact).drop_if_null := matchset_data_rec.drop_if_null;
105 l_count_exact := l_count_exact + 1;
106 END IF;
107 IF matchset_data_rec.partial_include = 'Y' THEN
108 Igs_Pe_Identify_Dups.g_matchset_partial(l_count_partial).data_element := matchset_data_rec.data_element;
109 Igs_Pe_Identify_Dups.g_matchset_partial(l_count_partial).drop_if_null := matchset_data_rec.drop_if_null;
110 l_count_partial := l_count_partial + 1;
111 END IF;
112 END IF;
113 END LOOP;
114
115 Igs_Pe_Identify_Dups.form_dup_whereclause (
116 x_errbuf => lv_errbuf,
117 x_retcode => lv_retcode,
118 x_match_set_id => x_match_set_id,
119 x_match_category => x_exact_partial,
120 x_view_name => 'IGS_PE_DUP_MATCHES_P_V',
121 x_person_id => x_person_id,
122 x_surname => x_Surname,
123 x_given_names => x_Given_Names,
124 x_birth_dt => x_Birth_Dt,
125 x_pref_alternate_id => x_Pref_Alternate_Id,
126 x_sex => x_Sex,
127 x_select_clause => l_Select_Clause
128 );
129
130
131 -- Run the select clause to find out the duplicate records if the select statement is not null using Dynamic SQL.
132 lv_Do_Partial := 'Y';
133 IF l_Select_Clause IS NOT NULL AND l_Select_Clause <> 'PARTIAL_MATCH' THEN
134 BEGIN
135 l_ext_cursor := DBMS_SQL.OPEN_CURSOR;
136 fnd_dsql.set_cursor(l_ext_cursor);
137
138 DBMS_SQL.PARSE (l_ext_cursor, l_Select_Clause, DBMS_SQL.V7);
139 fnd_dsql.do_binds;
140
141 dbms_sql.define_column(l_ext_cursor, 1, l_person_id);
142
143 lnRows := DBMS_SQL.EXECUTE (l_ext_cursor);
144
145 LOOP
146 -- fetch a row
147 IF dbms_sql.fetch_rows(l_ext_cursor) > 0 THEN
148 -- fetch columns from the row and prepare the where clause to pass to the form.
149 dbms_sql.column_value(l_ext_cursor, 1, l_person_id);
150 l_where_clause := l_where_clause || l_person_id || ',';
151 l_match_found := TRUE;
152 ELSE
153 EXIT;
154 END IF;
155 END LOOP;
156
157 -- There are exact matched records.
158 IF l_match_found THEN
159 X_DUP_FOUND := 'Y';
160 X_EXACT_PARTIAL := 'E';
161 X_WHERE_CLAUSE := RTRIM(l_where_clause,',') || ')';
162 RETURN;
163
164 ELSE -- No exact matched records. Do partial match.
165
166 lv_Do_Partial := 'Y';
167 l_where_clause := ' person_id IN ( ';
168 l_match_found := FALSE;
169 END IF;
170
171 DBMS_SQL.CLOSE_CURSOR (l_ext_cursor);
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 IF DBMS_SQL.IS_OPEN(l_Ext_Cursor) THEN
176 DBMS_SQL.CLOSE_CURSOR(l_Ext_Cursor);
177 END IF;
178 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
179 FND_MESSAGE.SET_TOKEN('NAME','igs_pe_dup_person.find_duplicates'||'-'||SQLERRM);
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 END;
182 END IF;
183
184 -- If the exact selec statement does not return any rows, go for partial match.
185 IF lv_Do_Partial = 'Y' THEN
186 /*change made for bug number 2158920 */
187 x_exact_partial := 'P';
188
189 Igs_Pe_Identify_Dups.form_dup_whereclause (
190 x_errbuf => lv_errbuf,
191 x_retcode => lv_retcode,
192 x_match_set_id => x_match_set_id,
193 x_match_category => x_exact_partial,
194 x_view_name => 'IGS_PE_DUP_MATCHES_P_V',
195 x_person_id => x_person_id,
196 x_surname => x_Surname,
197 x_given_names => x_Given_Names,
198 x_birth_dt => x_Birth_Dt,
199 x_pref_alternate_id => x_Pref_Alternate_Id,
200 x_sex => x_Sex,
201 x_select_clause => l_Select_Clause
202 );
203
204 -- Run the select statement using dynamic SQL to find the partial duplicate records.
205 IF l_Select_Clause IS NOT NULL THEN
206 BEGIN
207 l_Ext_Cursor := DBMS_SQL.OPEN_CURSOR;
208 fnd_dsql.set_cursor(l_ext_cursor);
209
210 DBMS_SQL.PARSE (l_Ext_Cursor, l_Select_Clause, DBMS_SQL.V7);
211 fnd_dsql.do_binds;
212
213 dbms_sql.define_column(l_ext_cursor, 1, l_person_id);
214
215 lnRows := DBMS_SQL.EXECUTE (l_Ext_Cursor);
216
217 LOOP
218 -- fetch a row
219 IF dbms_sql.fetch_rows(l_ext_cursor) > 0 THEN
220
221 -- fetch columns from the row and concatenate to for the where clause to be returned to the form
222 dbms_sql.column_value(l_ext_cursor, 1, l_person_id);
223 l_where_clause := l_where_clause || l_person_id || ',';
224 l_match_found := TRUE;
225 ELSE
226 EXIT;
227 END IF;
228 END LOOP;
229
230 -- There are partial matched records.
231 IF l_match_found THEN
232 x_dup_found := 'Y';
233 x_exact_partial := 'P';
234 x_where_clause := RTRIM(l_where_clause,',')|| ')'; -- SUBSTR(l_where_clause,1,length(l_where_clause)-1);
235
236 ELSE -- There are no matched records
237
238 x_where_clause := NULL;
239 x_exact_partial := NULL;
240 x_dup_found := 'N';
241 END IF;
242
243 DBMS_SQL.CLOSE_CURSOR (l_Ext_Cursor);
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 IF DBMS_SQL.IS_OPEN(l_Ext_Cursor) THEN
248 DBMS_SQL.CLOSE_CURSOR(l_Ext_Cursor);
249 END IF;
250 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
251 FND_MESSAGE.SET_TOKEN('NAME','igs_pe_dup_person.find_duplicates'||'-'||SQLERRM);
252 APP_EXCEPTION.RAISE_EXCEPTION;
253 END;
254 END IF;
255 END IF;
256 END FIND_DUPLICATES;
257 END Igs_Pe_Dup_Person;