[Home] [Help]
PACKAGE BODY: APPS.HZ_DUP_MERGE_PUB
Source
1 PACKAGE BODY HZ_DUP_MERGE_PUB AS
2 /*$Header: ARHBCMBB.pls 120.1.12010000.3 2009/03/30 07:58:01 vsegu ship $ */
3
4 -- PROCEDURE create_dup_merge_request
5 --
6 -- DESCRIPTION
7 -- Create merge request for duplicate parties
8 --
9 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
10 --
11 -- ARGUMENTS
12 -- IN:
13 -- p_init_msg_list Standard IN parameter to initialize message stack.
14 -- p_dup_id_objs An object table of duplicate party ids.
15 -- p_note_text note for the merge request
16 --
17 -- OUT:
18 -- x_return_status Return status after the call. The status can
19 -- be fnd_api.g_ret_sts_success (success),
20 -- fnd_api.g_ret_sts_error (error),
21 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
22 -- x_msg_count Number of messages in message stack.
23 -- x_msg_data Message text if x_msg_count is 1.
24 -- x_merge_request_id merge request id
25 --
26 -- NOTES
27 --
28 -- MODIFICATION HISTORY
29 --
30 -- 10-JAN-2006 AWU Created.
31
32
33 FUNCTION is_ss_provided(
34 p_os IN VARCHAR2,
35 p_osr IN VARCHAR2
36 ) RETURN VARCHAR2 IS
37 BEGIN
38 IF((p_os is null or p_os = fnd_api.g_miss_char)
39 and (p_osr is null or p_osr = fnd_api.g_miss_char))THEN
40 RETURN 'N';
41 ELSE
42 RETURN 'Y';
43 END IF;
44 END is_ss_provided;
45
46 PROCEDURE validate_ssm_id(
47 px_id IN OUT NOCOPY NUMBER,
48 px_os IN OUT NOCOPY VARCHAR2,
49 px_osr IN OUT NOCOPY VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2
53 ) IS
54 CURSOR is_pty_valid(l_pty_id NUMBER) IS
55 SELECT status, party_id
56 FROM HZ_PARTIES
57 WHERE party_id = l_pty_id
58 AND status in ('A', 'I');
59
60 l_ss_flag VARCHAR2(1);
61 l_debug_prefix VARCHAR2(30);
62 l_status varchar2(1);
63 l_valid_id number;
64 l_owner_table_id number;
65 l_count number;
66 begin
67 -- Debug info.
68 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
69 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(+)',
70 p_prefix=>l_debug_prefix,
71 p_msg_level=>fnd_log.level_procedure);
72 END IF;
73
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76 -- if px_id pass in, check if px_id is valid or not
77 IF(px_id IS NOT NULL) THEN
78
79 OPEN is_pty_valid(px_id);
80 FETCH is_pty_valid INTO l_status, l_valid_id;
81 CLOSE is_pty_valid;
82 end if;
83
84 if l_status = 'M'
85 then
86 FND_MESSAGE.SET_NAME('AR','HZ_DL_ALREADY_MERGED');
87 FND_MSG_PUB.ADD();
88 RAISE fnd_api.g_exc_error;
89 end if;
90
91
92 l_ss_flag := is_ss_provided(p_os => px_os,
93 p_osr => px_osr);
94
95 -- if px_os/px_osr pass in, get owner_table_id and set l_ss_flag to 'Y'
96 IF(l_ss_flag = 'Y')THEN
97
98 -- Get how many rows return
99 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
100 p_orig_system => px_os,
101 p_orig_system_reference => px_osr,
102 p_owner_table_name => 'HZ_PARTIES');
103
104 IF(l_count = 1) THEN
105 -- Get owner_table_id
106 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
107 p_orig_system => px_os,
108 p_orig_system_reference => px_osr,
109 p_owner_table_name => 'HZ_PARTIES',
110 x_owner_table_id => l_owner_table_id,
111 x_return_status => x_return_status);
112
113 OPEN is_pty_valid(l_owner_table_id);
114 FETCH is_pty_valid INTO l_status, l_valid_id;
115 CLOSE is_pty_valid;
116 if l_status = 'M'
117 then
118 FND_MESSAGE.SET_NAME('AR','HZ_DL_ALREADY_MERGED');
119 FND_MSG_PUB.ADD();
120 RAISE fnd_api.g_exc_error;
121 end if;
122 end if;
123
124 end if;
125
126 -- if px_id pass in
127 IF(px_id IS NOT NULL) THEN
128 -- if px_id is invalid, raise error
129 IF(l_valid_id IS NULL) THEN
130 FND_MESSAGE.SET_NAME('AR','HZ_DL_MR_INV_PARTYNUM');
131 fnd_message.set_token('PARTY_ID', px_id);
132 FND_MSG_PUB.ADD();
133 RAISE fnd_api.g_exc_error;
134 -- if px_id is valid
135 ELSE
136 -- check if px_os/px_osr is passed
137 IF(l_ss_flag = 'Y') THEN
138 IF(l_count = 0) THEN
139 FND_MESSAGE.SET_NAME('AR','HZ_DL_MR_INV_OSOSR');
140 fnd_message.set_token('OSOSR', px_os||' - '||px_osr);
141 FND_MSG_PUB.ADD();
142 RAISE fnd_api.g_exc_error;
143 -- if px_os/px_osr is valid
144 ELSE
145 -- if px_os/px_osr is valid, but not same as px_id
146 IF(l_owner_table_id <> px_id) OR (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
147 FND_MESSAGE.SET_NAME('AR','HZ_DL_MR_INVALID');
148 fnd_message.set_token('OSOSR', px_os||' - '||px_osr);
149 FND_MSG_PUB.ADD();
150 RAISE fnd_api.g_exc_error;
151 END IF;
152 END IF;
153 -- if px_os/px_osr is valid and return value is same as px_id
154 -- do nothing
155 END IF;
156 END IF;
157 -- if px_id not pass in
158 ELSE
159 -- check if px_os/px_osr can find TCA identifier, owner_table_id
160 -- if not found, raise error
161 -- else, get owner_table_id and assign it to px_id
162 IF(l_ss_flag = 'Y') AND (l_count = 1) AND (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
163 px_id := l_owner_table_id;
164 ELSE
165 FND_MESSAGE.SET_NAME('AR','HZ_DL_MR_INV_OSOSR');
166 fnd_message.set_token('OSOSR', px_os||' - '||px_osr);
167 FND_MSG_PUB.ADD();
168 RAISE fnd_api.g_exc_error;
169 END IF;
170 END IF;
171
172 EXCEPTION
173 WHEN fnd_api.g_exc_error THEN
174 x_return_status := fnd_api.g_ret_sts_error;
175
176 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
177 p_count => x_msg_count,
178 p_data => x_msg_data);
179
180 -- Debug info.
181 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
182 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
183 p_msg_data=>x_msg_data,
184 p_msg_type=>'ERROR',
185 p_msg_level=>fnd_log.level_error);
186 END IF;
187 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
188 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
189 p_prefix=>l_debug_prefix,
190 p_msg_level=>fnd_log.level_procedure);
191 END IF;
192 WHEN fnd_api.g_exc_unexpected_error THEN
193 x_return_status := fnd_api.g_ret_sts_unexp_error;
194
195 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
196 p_count => x_msg_count,
197 p_data => x_msg_data);
198
199 -- Debug info.
200 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
201 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
202 p_msg_data=>x_msg_data,
203 p_msg_type=>'UNEXPECTED ERROR',
204 p_msg_level=>fnd_log.level_error);
205 END IF;
206 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
207 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
208 p_prefix=>l_debug_prefix,
209 p_msg_level=>fnd_log.level_procedure);
210 END IF;
211 WHEN OTHERS THEN
212 x_return_status := fnd_api.g_ret_sts_unexp_error;
213
214 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
215 fnd_message.set_token('ERROR' ,SQLERRM);
216 fnd_msg_pub.add;
217
218 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
219 p_count => x_msg_count,
220 p_data => x_msg_data);
221
222 -- Debug info.
223 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
224 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
225 p_msg_data=>x_msg_data,
226 p_msg_type=>'SQL ERROR',
227 p_msg_level=>fnd_log.level_error);
228 END IF;
229 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
230 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
231 p_prefix=>l_debug_prefix,
232 p_msg_level=>fnd_log.level_procedure);
233 END IF;
234 END validate_ssm_id;
235
236 function check_obj_dup_value(p_dup_id_objs IN HZ_DUP_ID_OBJ_TBL) return varchar2 is
237 TYPE PartyIdTbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
238 l_PartyIdTbl PartyIdTbl;
239 begin
240 for i in 1..p_dup_id_objs.count loop
241 if l_PartyIdTbl.EXISTS(p_dup_id_objs(i).party_id)
242 then return 'Y';
243 else l_PartyIdTbl(p_dup_id_objs(i).party_id) := 1;
244 end if;
245 end loop;
246 return 'N';
247 end;
248
249 PROCEDURE create_dup_merge_request(
250 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
251 p_dup_id_objs IN HZ_DUP_ID_OBJ_TBL,
252 p_note_text IN VARCHAR2,
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2,
256 x_merge_request_id OUT NOCOPY NUMBER
257 ) IS
258
259 CURSOR get_party_name(l_party_id NUMBER) is
260 select party_name
261 from HZ_PARTIES
262 where party_id = l_party_id;
263
264 l_jtf_note_id number;
265 l_dup_set_id number;
266 l_dup_batch_rec HZ_DUP_PVT.DUP_BATCH_REC_TYPE;
267 l_dup_set_rec HZ_DUP_PVT.DUP_SET_REC_TYPE;
268 l_dup_party_tbl HZ_DUP_PVT.DUP_PARTY_TBL_TYPE;
269 l_dup_id_objs HZ_DUP_ID_OBJ_TBL;
270 l_party_name varchar2(360);
271 l_dup_batch_id number;
272 l_request_id number;
273 l_master_party_id number;
274 l_debug_prefix VARCHAR2(30) := '';
275 l_dup varchar2(1);
276 begin
277
278 -- Standard start of API savepoint
279 SAVEPOINT create_dup_merge_req_pub;
280
281 -- initialize API return status to success.
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283
284 -- Debug info.
285 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
286 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(+)',
287 p_prefix=>l_debug_prefix,
288 p_msg_level=>fnd_log.level_procedure);
289 END IF;
290 --Bug8342391
291 IF FND_API.to_Boolean(p_init_msg_list) THEN
292 FND_MSG_PUB.initialize;
293 END IF;
294
295 l_dup_id_objs := p_dup_id_objs;
296 for i in 1..l_dup_id_objs.count loop
297 validate_ssm_id(
298 px_id => l_dup_id_objs(i).party_id,
299 px_os => l_dup_id_objs(i).orig_system,
300 px_osr => l_dup_id_objs(i).orig_system_reference,
301 x_return_status => x_return_status,
302 x_msg_count => x_msg_count,
303 x_msg_data => x_msg_data);
304
305 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
306 RAISE fnd_api.g_exc_error;
307 END IF;
308
309 end loop;
310
311 l_dup := check_obj_dup_value(l_dup_id_objs);
312
313 if l_dup = 'Y'
314 then
315 FND_MESSAGE.SET_NAME('AR','HZ_MERGE_UNIQUE_CONST');
316 FND_MSG_PUB.ADD();
317 RAISE fnd_api.g_exc_error;
318 end if;
319
320 OPEN get_party_name(l_dup_id_objs(1).party_id); -- choose any party
321 FETCH get_party_name INTO l_party_name;
322 CLOSE get_party_name;
323
324 if l_dup_id_objs.count = 1
325 then
326 l_dup_set_rec.merge_type := 'SAME_PARTY_MERGE';
327 else
328 l_dup_set_rec.merge_type := 'PARTY_MERGE';
329 end if;
330
331 l_dup_batch_rec.dup_batch_name := l_party_name ||'-'|| to_char(sysdate);
332 l_dup_batch_rec.match_rule_id := nvl(fnd_profile.value('HZ_DL_IDENTIFY_DUP_RULE'),-2);
333 -- match rule is not really needed in create case. default to -2.
334 l_dup_batch_rec.application_id := 222;
335 l_dup_batch_rec.request_type := 'SYSTEM_GENERATED';
336 l_dup_batch_id := NULL;
337 l_dup_set_rec.winner_party_id := l_dup_id_objs(1).party_id; -- call default master later
338 l_dup_set_rec.status := 'SYSBATCH';
339 l_dup_set_rec.assigned_to_user_id := NULL;
340
341 for i in 1..l_dup_id_objs.count loop
342 l_dup_party_tbl(i).party_id := l_dup_id_objs(i).party_id;
343 l_dup_party_tbl(i).score := -1;
344 l_dup_party_tbl(i).merge_flag := 'N';
345 end loop;
346
347 HZ_DUP_PVT.create_dup_batch(
348 p_dup_batch_rec => l_dup_batch_rec
349 ,p_dup_set_rec => l_dup_set_rec
350 ,p_dup_party_tbl => l_dup_party_tbl
351 ,x_dup_batch_id => l_dup_batch_id
352 ,x_dup_set_id => l_dup_set_id
353 ,x_return_status => x_return_status
354 ,x_msg_count => x_msg_count
355 ,x_msg_data => x_msg_data );
356
357 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
358 RAISE FND_API.G_EXC_ERROR;
359 END IF;
360
361 if p_dup_id_objs.count > 1
362 then
363 hz_dup_pvt.default_master(
364 p_dup_set_id => l_dup_set_id,
365 x_master_party_id => l_master_party_id,
366 x_master_party_name => l_party_name,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data );
370
371 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374
375 update hz_dup_batch
376 set dup_batch_name = l_party_name || ' - '||l_dup_batch_id
377 where dup_batch_id = l_dup_batch_id;
378
379 end if;
380
381 HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
382 p_dup_set_id => l_dup_set_id,
383 p_merge_batch_id => null,
384 p_init_msg_list => FND_API.G_FALSE,
385 p_reject_req_flag => 'N',
386 x_return_status => x_return_status,
387 x_msg_count => x_msg_count,
388 x_msg_data => x_msg_data);
389
390 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 -- populate merge tables
395 hz_dup_pvt.submit_dup (
396 p_dup_set_id => l_dup_set_id
397 ,x_request_id => l_request_id
398 ,x_return_status => x_return_status
399 ,x_msg_count => x_msg_count
400 ,x_msg_data => x_msg_data);
401
402 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
403 RAISE FND_API.G_EXC_ERROR;
404 END IF;
405
406 IF p_note_text IS NOT NULL THEN
407
408 jtf_notes_pub.Create_note
409 (p_parent_note_id =>null
410 ,p_jtf_note_id =>null
411 ,p_api_version =>1
412 ,p_init_msg_list =>FND_API.g_false
413 ,p_commit =>FND_API.g_false
414 ,p_validation_level =>100
415 ,x_return_status =>x_return_status
416 ,x_msg_count =>x_msg_count
417 ,x_msg_data =>x_msg_data
418 ,p_org_id =>null
419 ,p_source_object_id => l_dup_set_id
420 ,p_source_object_code => 'MERGE_DUP_SET'
421 ,p_notes => p_note_text
422 ,p_notes_detail=>null
423 ,p_note_status =>'I'
424 ,p_entered_by =>fnd_global.user_id
425 ,p_entered_date => sysdate
426 ,x_jtf_note_id => l_jtf_note_id
427 ,p_last_update_date => sysdate
428 ,p_last_updated_by =>fnd_global.user_id
429 ,p_creation_date => sysdate
430 ,p_created_by => fnd_global.user_id
431 ,p_last_update_login => fnd_global.login_id
432 ,p_attribute1 => NULL
433 ,p_attribute2 => NULL
434 ,p_attribute3 => NULL
435 ,p_attribute4 => NULL
436 ,p_attribute5 => NULL
437 ,p_attribute6 => NULL
438 ,p_attribute7 => NULL
439 ,p_attribute8 => NULL
440 ,p_attribute9 => NULL
441 ,p_attribute10 => NULL
442 ,p_attribute11 => NULL
443 ,p_attribute12 => NULL
444 ,p_attribute13 => NULL
445 ,p_attribute14 => NULL
446 ,p_attribute15 => NULL
447 ,p_context => NULL
448 ,p_note_type => 'GENERAL'
449 );
450
451 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
452 RAISE FND_API.G_EXC_ERROR;
453 END IF;
454
455 END IF; --p_note_text
456 x_merge_request_id := l_dup_set_id;
457
458
459 -- Debug info.
460 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
461 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
462 p_msg_data=>x_msg_data,
463 p_msg_type=>'WARNING',
464 p_msg_level=>fnd_log.level_exception);
465 END IF;
466 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
467 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
468 p_prefix=>l_debug_prefix,
469 p_msg_level=>fnd_log.level_procedure);
470 END IF;
471
472 EXCEPTION
473 WHEN fnd_api.g_exc_error THEN
474 ROLLBACK TO create_dup_merge_req_pub;
475 x_return_status := fnd_api.g_ret_sts_error;
476
477 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
478 p_count => x_msg_count,
479 p_data => x_msg_data);
480
481 -- Debug info.
482 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
483 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
484 p_msg_data=>x_msg_data,
485 p_msg_type=>'ERROR',
486 p_msg_level=>fnd_log.level_error);
487 END IF;
488 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
489 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
490 p_prefix=>l_debug_prefix,
491 p_msg_level=>fnd_log.level_procedure);
492 END IF;
493
494 WHEN fnd_api.g_exc_unexpected_error THEN
495 ROLLBACK TO create_dup_merge_req_pub;
496 x_return_status := fnd_api.g_ret_sts_unexp_error;
497
498 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
499 p_count => x_msg_count,
500 p_data => x_msg_data);
501
502 -- Debug info.
503 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
504 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
505 p_msg_data=>x_msg_data,
506 p_msg_type=>'UNEXPECTED ERROR',
507 p_msg_level=>fnd_log.level_error);
508 END IF;
509 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
510 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
511 p_prefix=>l_debug_prefix,
512 p_msg_level=>fnd_log.level_procedure);
513 END IF;
514
515 WHEN OTHERS THEN
516 ROLLBACK TO create_dup_merge_req_pub;
517 x_return_status := fnd_api.g_ret_sts_unexp_error;
518
519 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
520 fnd_message.set_token('ERROR' ,SQLERRM);
521 fnd_msg_pub.add;
522
523 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
524 p_count => x_msg_count,
525 p_data => x_msg_data);
526
527 -- Debug info.
528 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
529 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
530 p_msg_data=>x_msg_data,
531 p_msg_type=>'SQL ERROR',
532 p_msg_level=>fnd_log.level_error);
533 END IF;
534 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
535 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
536 p_prefix=>l_debug_prefix,
537 p_msg_level=>fnd_log.level_procedure);
538 END IF;
539 END create_dup_merge_request;
540
541 END HZ_DUP_MERGE_PUB;