[Home] [Help]
PACKAGE BODY: APPS.HZ_MATCH_RULES_PKG
Source
1 PACKAGE BODY HZ_MATCH_RULES_PKG AS
2 /*$Header: ARHDQMRB.pls 120.13 2006/05/05 18:52:30 repuri noship $ */
3
4 procedure INSERT_ROW (
5 X_MATCH_RULE_ID in out NOCOPY NUMBER,
6 X_RULE_PURPOSE in VARCHAR2,
7 X_MATCH_ALL_FLAG in VARCHAR2,
8 X_ACTIVE_FLAG in VARCHAR2,
9 X_NO_OVERRIDE_SCORE in NUMBER,
10 X_AUTO_MERGE_SCORE in NUMBER,
11 X_COMPILATION_FLAG in VARCHAR2,
12 X_MATCH_SCORE in NUMBER,
13 X_RULE_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER,
20 X_OBJECT_VERSION_NUMBER in NUMBER,
21 X_AUTOMERGE_FLAG IN VARCHAR2,
22 X_MATCH_RULE_TYPE IN VARCHAR2,
23 X_USE_CONTACT_ADDR_FLAG IN VARCHAR2 DEFAULT NULL,
24 X_USE_CONTACT_CPT_FLAG IN VARCHAR2 DEFAULT NULL
25
26 ) is
27
28 CURSOR C2 IS SELECT HZ_MATCH_RULES_s.nextval FROM sys.dual;
29 l_success VARCHAR2(1) := 'N';
30 BEGIN
31 WHILE l_success = 'N' LOOP
32 BEGIN
33 IF ( X_MATCH_RULE_ID IS NULL) OR (X_MATCH_RULE_ID = FND_API.G_MISS_NUM) THEN
34 OPEN C2;
35 FETCH C2 INTO X_MATCH_RULE_ID;
36 CLOSE C2;
37 END IF;
38
39 insert into HZ_MATCH_RULES_B (
40 RULE_PURPOSE,
41 MATCH_ALL_FLAG,
42 ACTIVE_FLAG,
43 NO_OVERRIDE_SCORE,
44 AUTO_MERGE_SCORE,
45 COMPILATION_FLAG,
46 MATCH_RULE_ID,
47 MATCH_SCORE,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 OBJECT_VERSION_NUMBER,
54 AUTOMERGE_FLAG,
55 MATCH_RULE_TYPE,
56 USE_CONTACT_ADDR_FLAG,
57 USE_CONTACT_CPT_FLAG
58 ) values (
59 X_RULE_PURPOSE,
60 X_MATCH_ALL_FLAG,
61 X_ACTIVE_FLAG,
62 X_NO_OVERRIDE_SCORE,
63 X_AUTO_MERGE_SCORE,
64 X_COMPILATION_FLAG,
65 X_MATCH_RULE_ID,
66 X_MATCH_SCORE,
67 hz_utility_v2pub.creation_date,
68 X_CREATED_BY,
69 hz_utility_v2pub.last_update_date,
70 X_LAST_UPDATED_BY,
71 X_LAST_UPDATE_LOGIN,
72 1,
73 X_AUTOMERGE_FLAG,
74 X_MATCH_RULE_TYPE,
75 X_USE_CONTACT_ADDR_FLAG,
76 X_USE_CONTACT_CPT_FLAG
77 );
78
79
80 l_success := 'Y';
81 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
82 IF INSTRB( SQLERRM, 'HZ_MATCH_RULES_B_U1' ) <> 0 THEN
83 DECLARE
84 l_count NUMBER;
85 l_dummy VARCHAR2(1);
86 BEGIN
87 l_count := 1;
88 WHILE l_count > 0 LOOP
89 SELECT HZ_MATCH_RULES_s.nextval
90 into X_MATCH_RULE_ID FROM sys.dual;
91 BEGIN
92 SELECT 'Y' INTO l_dummy
93 FROM HZ_MATCH_RULES_B
94 WHERE MATCH_RULE_ID = X_MATCH_RULE_ID;
95 l_count := 1;
96 EXCEPTION WHEN NO_DATA_FOUND THEN
97 l_count := 0;
98 END;
99 END LOOP;
100 END;
101 END IF;
102 END;
103 END LOOP;
104
105 insert into HZ_MATCH_RULES_TL (
106 LAST_UPDATE_DATE,
107 CREATION_DATE,
108 CREATED_BY,
109 DESCRIPTION,
110 RULE_NAME,
111 MATCH_RULE_ID,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN,
114 LANGUAGE,
115 SOURCE_LANG,
116 OBJECT_VERSION_NUMBER
117 ) select
118 X_LAST_UPDATE_DATE,
119 X_CREATION_DATE,
120 X_CREATED_BY,
121 X_DESCRIPTION,
122 X_RULE_NAME,
123 X_MATCH_RULE_ID,
124 X_LAST_UPDATED_BY,
125 X_LAST_UPDATE_LOGIN,
126 L.LANGUAGE_CODE,
127 userenv('LANG'),
128 1
129 from FND_LANGUAGES L
130 where L.INSTALLED_FLAG in ('I', 'B')
131 and not exists
132 (select NULL
133 from HZ_MATCH_RULES_TL T
134 where T.MATCH_RULE_ID = X_MATCH_RULE_ID
135 and T.LANGUAGE = L.LANGUAGE_CODE);
136
137
138 end INSERT_ROW;
139
140 procedure INSERT_ROW (
141 X_MATCH_RULE_ID in out NOCOPY NUMBER,
142 X_RULE_PURPOSE in VARCHAR2,
143 X_MATCH_ALL_FLAG in VARCHAR2,
144 X_ACTIVE_FLAG in VARCHAR2,
145 X_NO_OVERRIDE_SCORE in NUMBER,
146 X_AUTO_MERGE_SCORE in NUMBER,
147 X_COMPILATION_FLAG in VARCHAR2,
148 X_MATCH_SCORE in NUMBER,
149 X_RULE_NAME in VARCHAR2,
150 X_DESCRIPTION in VARCHAR2,
151 X_CREATION_DATE in DATE,
152 X_CREATED_BY in NUMBER,
153 X_LAST_UPDATE_DATE in DATE,
154 X_LAST_UPDATED_BY in NUMBER,
155 X_LAST_UPDATE_LOGIN in NUMBER,
156 X_OBJECT_VERSION_NUMBER in NUMBER,
157 X_AUTOMERGE_FLAG IN VARCHAR2 DEFAULT 'N',
158 X_USE_CONTACT_ADDR_FLAG IN VARCHAR2 DEFAULT NULL,
159 X_USE_CONTACT_CPT_FLAG IN VARCHAR2 DEFAULT NULL
160 ) is
161
162 BEGIN
163 INSERT_ROW(X_MATCH_RULE_ID,X_RULE_PURPOSE,X_MATCH_ALL_FLAG,X_ACTIVE_FLAG,X_NO_OVERRIDE_SCORE,
164 X_AUTO_MERGE_SCORE,X_COMPILATION_FLAG,X_MATCH_SCORE,X_RULE_NAME,X_DESCRIPTION,
165 X_CREATION_DATE,X_CREATED_BY,X_LAST_UPDATE_DATE,X_LAST_UPDATED_BY,X_LAST_UPDATE_LOGIN,
166 X_OBJECT_VERSION_NUMBER,X_AUTOMERGE_FLAG,'SINGLE',X_USE_CONTACT_ADDR_FLAG, X_USE_CONTACT_CPT_FLAG );
167 end INSERT_ROW;
168
169 procedure LOCK_ROW (
170 X_MATCH_RULE_ID in NUMBER,
171 X_OBJECT_VERSION_NUMBER IN NUMBER
172 ) is
173 cursor c is select
174 OBJECT_VERSION_NUMBER
175 from HZ_MATCH_RULES_B
176 where MATCH_RULE_ID = X_MATCH_RULE_ID
177 for update of MATCH_RULE_ID nowait;
178 recinfo c%rowtype;
179
180 begin
181 open c;
182
183 fetch c into recinfo;
184 if (c%notfound) then
185 close c;
186 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
187 app_exception.raise_exception;
188 end if;
189 close c;
190 if(
191 ( recinfo.OBJECT_VERSION_NUMBER IS NULL AND X_object_version_number IS NULL )
192 OR ( recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND
193 X_object_version_number IS NOT NULL AND
194 recinfo.OBJECT_VERSION_NUMBER = X_object_version_number )
195 ) then
196 null;
197 else
198 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
199 app_exception.raise_exception;
200 end if;
201
202 return;
203 end LOCK_ROW;
204
205 PROCEDURE uncompile_match_rule_sets(p_rule_id NUMBER)
206 IS
207
208 BEGIN
209 UPDATE HZ_MATCH_RULES_B
210 SET COMPILATION_FLAG = 'U'
211 WHERE MATCH_RULE_ID IN (SELECT DISTINCT MATCH_RULE_SET_ID
212 FROM HZ_MATCH_RULE_CONDITIONS
213 WHERE CONDITION_MATCH_RULE_ID = p_rule_id
214 )
215 AND nvl(COMPILATION_FLAG,'N') = 'C';
216
217 END;
218
219 procedure UPDATE_ROW (
220 X_MATCH_RULE_ID in NUMBER,
221 X_RULE_PURPOSE in VARCHAR2,
222 X_MATCH_ALL_FLAG in VARCHAR2,
223 X_ACTIVE_FLAG in VARCHAR2,
224 X_NO_OVERRIDE_SCORE in NUMBER,
225 X_AUTO_MERGE_SCORE in NUMBER,
226 X_COMPILATION_FLAG in VARCHAR2,
227 X_MATCH_SCORE in NUMBER,
228 X_RULE_NAME in VARCHAR2,
229 X_DESCRIPTION in VARCHAR2,
230 X_LAST_UPDATE_DATE in DATE,
231 X_LAST_UPDATED_BY in NUMBER,
232 X_LAST_UPDATE_LOGIN in NUMBER,
233 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
234 X_AUTOMERGE_FLAG IN VARCHAR2 DEFAULT NULL,
235 X_USE_CONTACT_ADDR_FLAG IN VARCHAR2 DEFAULT NULL,
236 X_USE_CONTACT_CPT_FLAG IN VARCHAR2 DEFAULT NULL
237 ) is
238
239 p_object_version_number number;
240 begin
241
242 p_object_version_number := NVL(X_object_version_number, 1) + 1;
243
244 update HZ_MATCH_RULES_B set
245 RULE_PURPOSE = X_RULE_PURPOSE,
246 MATCH_ALL_FLAG = X_MATCH_ALL_FLAG,
247 ACTIVE_FLAG = X_ACTIVE_FLAG,
248 NO_OVERRIDE_SCORE = X_NO_OVERRIDE_SCORE,
249 AUTO_MERGE_SCORE = X_AUTO_MERGE_SCORE,
250 COMPILATION_FLAG = X_COMPILATION_FLAG,
251 MATCH_SCORE = X_MATCH_SCORE,
252 LAST_UPDATE_DATE = hz_utility_v2pub.last_update_date,
253 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
254 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
255 OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER,
256 AUTOMERGE_FLAG = NVL(X_AUTOMERGE_FLAG,AUTOMERGE_FLAG),
257 USE_CONTACT_ADDR_FLAG = X_USE_CONTACT_ADDR_FLAG,
258 USE_CONTACT_CPT_FLAG = X_USE_CONTACT_CPT_FLAG
259 where MATCH_RULE_ID = X_MATCH_RULE_ID;
260
261 if (sql%notfound) then
262 raise no_data_found;
263 end if;
264
265 update HZ_MATCH_RULES_TL set
266 RULE_NAME = X_RULE_NAME,
267 DESCRIPTION = X_DESCRIPTION,
268 LAST_UPDATE_DATE =hz_utility_v2pub.last_update_date,
269 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
270 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
271 SOURCE_LANG = userenv('LANG'),
272 OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
273 where MATCH_RULE_ID = X_MATCH_RULE_ID
274 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
275
276 X_OBJECT_VERSION_NUMBER := p_OBJECT_VERSION_NUMBER;
277
278 if (sql%notfound) then
279 raise no_data_found;
280 end if;
281 --Uncompile all the match rule sets having this match rule as a condition match rule.
282 uncompile_match_rule_sets(X_MATCH_RULE_ID);
283 end UPDATE_ROW;
284
285 procedure DELETE_ROW (
286 X_MATCH_RULE_ID in NUMBER
287 ) is
288 begin
289 --Uncompile all the match rule sets having this match rule as a condition match rule.
290 uncompile_match_rule_sets(X_MATCH_RULE_ID);
291
292 -- Start of changes for Bug 3962742
293 --Delete associated entries in following entities
294 DELETE FROM HZ_SECONDARY_TRANS
295 WHERE secondary_attribute_id IN (SELECT secondary_attribute_id
296 FROM HZ_MATCH_RULE_SECONDARY
297 WHERE match_rule_id = X_MATCH_RULE_ID);
298
299 DELETE FROM HZ_PRIMARY_TRANS
300 WHERE primary_attribute_id IN (SELECT primary_attribute_id
301 FROM HZ_MATCH_RULE_PRIMARY
302 WHERE match_rule_id = X_MATCH_RULE_ID);
303
304 DELETE FROM HZ_MATCH_RULE_SECONDARY
305 WHERE MATCH_RULE_ID = X_MATCH_RULE_ID;
306
307 DELETE FROM HZ_MATCH_RULE_PRIMARY
308 WHERE MATCH_RULE_ID = X_MATCH_RULE_ID;
309
310 -- End of changes for Bug 3962742
311
312 delete from HZ_MATCH_RULES_TL
313 where MATCH_RULE_ID = X_MATCH_RULE_ID;
314
315 if (sql%notfound) then
316 raise no_data_found;
317 end if;
318
319 delete from HZ_MATCH_RULES_B
320 where MATCH_RULE_ID = X_MATCH_RULE_ID;
321
322 if (sql%notfound) then
323 raise no_data_found;
324 end if;
325 end DELETE_ROW;
326
327 procedure ADD_LANGUAGE
328 is
329 begin
330 delete from HZ_MATCH_RULES_TL T
331 where not exists
332 (select NULL
333 from HZ_MATCH_RULES_B B
334 where B.MATCH_RULE_ID = T.MATCH_RULE_ID
335 );
336
337 update HZ_MATCH_RULES_TL T set (
338 RULE_NAME,
339 DESCRIPTION
340 ) = (select
341 B.RULE_NAME,
342 B.DESCRIPTION
343 from HZ_MATCH_RULES_TL B
344 where B.MATCH_RULE_ID = T.MATCH_RULE_ID
345 and B.LANGUAGE = T.SOURCE_LANG)
346 where (
347 T.MATCH_RULE_ID,
348 T.LANGUAGE
349 ) in (select
350 SUBT.MATCH_RULE_ID,
351 SUBT.LANGUAGE
352 from HZ_MATCH_RULES_TL SUBB, HZ_MATCH_RULES_TL SUBT
353 where SUBB.MATCH_RULE_ID = SUBT.MATCH_RULE_ID
354 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
355 and (SUBB.RULE_NAME <> SUBT.RULE_NAME
356 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
357 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
358 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
359 ));
360
361 insert into HZ_MATCH_RULES_TL (
362 LAST_UPDATE_DATE,
363 CREATION_DATE,
364 CREATED_BY,
365 DESCRIPTION,
366 RULE_NAME,
367 MATCH_RULE_ID,
368 LAST_UPDATED_BY,
369 LAST_UPDATE_LOGIN,
370 LANGUAGE,
371 SOURCE_LANG
372 ) select
373 B.LAST_UPDATE_DATE,
374 B.CREATION_DATE,
375 B.CREATED_BY,
376 B.DESCRIPTION,
377 B.RULE_NAME,
378 B.MATCH_RULE_ID,
379 B.LAST_UPDATED_BY,
380 B.LAST_UPDATE_LOGIN,
381 L.LANGUAGE_CODE,
382 B.SOURCE_LANG
383 from HZ_MATCH_RULES_TL B, FND_LANGUAGES L
384 where L.INSTALLED_FLAG in ('I', 'B')
385 and B.LANGUAGE = userenv('LANG')
386 and L.LANGUAGE_CODE <> B.LANGUAGE
387 and not exists
388 (select NULL
389 from HZ_MATCH_RULES_TL T
390 where T.MATCH_RULE_ID = B.MATCH_RULE_ID
391 and T.LANGUAGE = L.LANGUAGE_CODE);
392 end ADD_LANGUAGE;
393
394 procedure LOAD_ROW (
395 X_MATCH_RULE_ID in NUMBER,
396 X_RULE_PURPOSE in VARCHAR2,
397 X_MATCH_ALL_FLAG in VARCHAR2,
398 X_ACTIVE_FLAG in VARCHAR2,
399 X_NO_OVERRIDE_SCORE in NUMBER,
400 X_AUTO_MERGE_SCORE in NUMBER,
401 X_COMPILATION_FLAG in VARCHAR2,
402 X_MATCH_SCORE in NUMBER,
403 X_RULE_NAME in VARCHAR2,
404 X_DESCRIPTION in VARCHAR2,
405 X_LAST_UPDATE_DATE in DATE,
406 X_LAST_UPDATED_BY in NUMBER,
407 X_LAST_UPDATE_LOGIN in NUMBER,
408 X_OBJECT_VERSION_NUMBER in NUMBER,
409 X_OWNER in VARCHAR2,
410 X_AUTOMERGE_FLAG IN VARCHAR2 DEFAULT NULL,
411 X_USE_CONTACT_ADDR_FLAG IN VARCHAR2 DEFAULT NULL,
412 X_USE_CONTACT_CPT_FLAG IN VARCHAR2 DEFAULT NULL
413 ) IS
414
415 begin
416
417 declare
418 user_id number := 0;
419 row_id varchar2(64);
420 L_MATCH_RULE_ID NUMBER := X_MATCH_RULE_ID;
421 L_OBJECT_VERSION_NUMBER number;
422
423 begin
424 if (X_OWNER = 'SEED') then
425 user_id := 1;
426 end if;
427
428 L_OBJECT_VERSION_NUMBER := NVL(X_OBJECT_VERSION_NUMBER,1) + 1;
429
430 HZ_MATCH_RULES_PKG.UPDATE_ROW(
431 X_MATCH_RULE_ID => X_MATCH_RULE_ID,
432 X_RULE_PURPOSE => X_RULE_PURPOSE,
433 X_MATCH_ALL_FLAG => X_MATCH_ALL_FLAG,
434 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
435 X_NO_OVERRIDE_SCORE => X_NO_OVERRIDE_SCORE,
436 X_AUTO_MERGE_SCORE => X_AUTO_MERGE_SCORE,
437 X_COMPILATION_FLAG => X_COMPILATION_FLAG,
438 X_MATCH_SCORE => X_MATCH_SCORE,
439 X_RULE_NAME => X_RULE_NAME,
440 X_DESCRIPTION => X_DESCRIPTION,
441 X_LAST_UPDATE_DATE => sysdate,
442 X_LAST_UPDATED_BY => user_id,
443 X_LAST_UPDATE_LOGIN =>0,
444 X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
445 X_AUTOMERGE_FLAG => X_AUTOMERGE_FLAG,
446 X_USE_CONTACT_ADDR_FLAG => X_USE_CONTACT_ADDR_FLAG,
447 X_USE_CONTACT_CPT_FLAG => X_USE_CONTACT_CPT_FLAG
448 );
449
450 exception
451 when NO_DATA_FOUND then
452
453 HZ_MATCH_RULES_PKG.INSERT_ROW(
454 X_MATCH_RULE_ID => L_MATCH_RULE_ID,
455 X_RULE_PURPOSE => X_RULE_PURPOSE,
456 X_MATCH_ALL_FLAG => X_MATCH_ALL_FLAG,
457 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
458 X_NO_OVERRIDE_SCORE => X_NO_OVERRIDE_SCORE,
459 X_AUTO_MERGE_SCORE => X_AUTO_MERGE_SCORE,
460 X_COMPILATION_FLAG => X_COMPILATION_FLAG,
461 X_MATCH_SCORE => X_MATCH_SCORE,
462 X_RULE_NAME => X_RULE_NAME,
463 X_DESCRIPTION => X_DESCRIPTION,
464 X_CREATION_DATE => sysdate,
465 X_CREATED_BY => user_id,
466 X_LAST_UPDATE_DATE => sysdate,
467 X_LAST_UPDATED_BY => user_id,
468 X_LAST_UPDATE_LOGIN =>0,
469 X_OBJECT_VERSION_NUMBER => 1,
470 X_AUTOMERGE_FLAG => X_AUTOMERGE_FLAG,
471 X_USE_CONTACT_ADDR_FLAG => X_USE_CONTACT_ADDR_FLAG,
472 X_USE_CONTACT_CPT_FLAG => X_USE_CONTACT_CPT_FLAG
473 );
474
475 end;
476 end LOAD_ROW;
477
478 procedure TRANSLATE_ROW (
479 X_MATCH_RULE_ID in NUMBER,
480 X_RULE_NAME in VARCHAR2,
481 X_DESCRIPTION in VARCHAR2,
482 X_OWNER in VARCHAR2) IS
483
484 begin
485
486 -- only update rows that have not been altered by user
487 UPDATE hz_match_rules_tl set
488 RULE_NAME = X_RULE_NAME,
489 DESCRIPTION = X_DESCRIPTION,
490 source_lang = userenv('LANG'),
491 last_update_date = sysdate,
492 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
493 last_update_login = 0
494 where match_rule_id = X_MATCH_RULE_ID
495 and userenv('LANG') in (language, source_lang);
496
497 end TRANSLATE_ROW;
498
499 end HZ_MATCH_RULES_PKG;