[Home] [Help]
PACKAGE BODY: APPS.HZ_DUP_MERGE_PUB
Source
1 PACKAGE BODY HZ_DUP_MERGE_PUB AS
2 /*$Header: ARHBCMBB.pls 120.1 2006/03/24 00:47:14 awu noship $ */
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
291 l_dup_id_objs := p_dup_id_objs;
292 for i in 1..l_dup_id_objs.count loop
293 validate_ssm_id(
294 px_id => l_dup_id_objs(i).party_id,
295 px_os => l_dup_id_objs(i).orig_system,
296 px_osr => l_dup_id_objs(i).orig_system_reference,
297 x_return_status => x_return_status,
298 x_msg_count => x_msg_count,
299 x_msg_data => x_msg_data);
300
301 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
302 RAISE fnd_api.g_exc_error;
303 END IF;
304
305 end loop;
306
307 l_dup := check_obj_dup_value(l_dup_id_objs);
308
309 if l_dup = 'Y'
310 then
311 FND_MESSAGE.SET_NAME('AR','HZ_MERGE_UNIQUE_CONST');
312 FND_MSG_PUB.ADD();
313 RAISE fnd_api.g_exc_error;
314 end if;
315
316 OPEN get_party_name(l_dup_id_objs(1).party_id); -- choose any party
317 FETCH get_party_name INTO l_party_name;
318 CLOSE get_party_name;
319
320 if l_dup_id_objs.count = 1
321 then
322 l_dup_set_rec.merge_type := 'SAME_PARTY_MERGE';
323 else
324 l_dup_set_rec.merge_type := 'PARTY_MERGE';
325 end if;
326
327 l_dup_batch_rec.dup_batch_name := l_party_name ||'-'|| to_char(sysdate);
328 l_dup_batch_rec.match_rule_id := nvl(fnd_profile.value('HZ_DL_IDENTIFY_DUP_RULE'),-2);
329 -- match rule is not really needed in create case. default to -2.
330 l_dup_batch_rec.application_id := 222;
331 l_dup_batch_rec.request_type := 'SYSTEM_GENERATED';
332 l_dup_batch_id := NULL;
333 l_dup_set_rec.winner_party_id := l_dup_id_objs(1).party_id; -- call default master later
334 l_dup_set_rec.status := 'SYSBATCH';
335 l_dup_set_rec.assigned_to_user_id := NULL;
336
337 for i in 1..l_dup_id_objs.count loop
338 l_dup_party_tbl(i).party_id := l_dup_id_objs(i).party_id;
339 l_dup_party_tbl(i).score := -1;
340 l_dup_party_tbl(i).merge_flag := 'N';
341 end loop;
342
343 HZ_DUP_PVT.create_dup_batch(
344 p_dup_batch_rec => l_dup_batch_rec
345 ,p_dup_set_rec => l_dup_set_rec
346 ,p_dup_party_tbl => l_dup_party_tbl
347 ,x_dup_batch_id => l_dup_batch_id
348 ,x_dup_set_id => l_dup_set_id
349 ,x_return_status => x_return_status
350 ,x_msg_count => x_msg_count
351 ,x_msg_data => x_msg_data );
352
353 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356
357 if p_dup_id_objs.count > 1
358 then
359 hz_dup_pvt.default_master(
360 p_dup_set_id => l_dup_set_id,
361 x_master_party_id => l_master_party_id,
362 x_master_party_name => l_party_name,
363 x_return_status => x_return_status,
364 x_msg_count => x_msg_count,
365 x_msg_data => x_msg_data );
366
367 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
368 RAISE FND_API.G_EXC_ERROR;
369 END IF;
370
371 update hz_dup_batch
372 set dup_batch_name = l_party_name || ' - '||l_dup_batch_id
373 where dup_batch_id = l_dup_batch_id;
374
375 end if;
376
377 HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
378 p_dup_set_id => l_dup_set_id,
379 p_merge_batch_id => null,
380 p_init_msg_list => FND_API.G_FALSE,
381 p_reject_req_flag => 'N',
382 x_return_status => x_return_status,
383 x_msg_count => x_msg_count,
384 x_msg_data => x_msg_data);
385
386 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389
390 -- populate merge tables
391 hz_dup_pvt.submit_dup (
392 p_dup_set_id => l_dup_set_id
393 ,x_request_id => l_request_id
394 ,x_return_status => x_return_status
395 ,x_msg_count => x_msg_count
396 ,x_msg_data => x_msg_data);
397
398 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
399 RAISE FND_API.G_EXC_ERROR;
400 END IF;
401
402 jtf_notes_pub.Create_note
403 (p_parent_note_id =>null
404 ,p_jtf_note_id =>null
405 ,p_api_version =>1
406 ,p_init_msg_list =>FND_API.g_false
407 ,p_commit =>FND_API.g_false
408 ,p_validation_level =>100
409 ,x_return_status =>x_return_status
410 ,x_msg_count =>x_msg_count
411 ,x_msg_data =>x_msg_data
412 ,p_org_id =>null
413 ,p_source_object_id => l_dup_set_id
414 ,p_source_object_code => 'MERGE_DUP_SET'
415 ,p_notes => p_note_text
416 ,p_notes_detail=>null
417 ,p_note_status =>'I'
418 ,p_entered_by =>fnd_global.user_id
422 ,p_last_updated_by =>fnd_global.user_id
419 ,p_entered_date => sysdate
420 ,x_jtf_note_id => l_jtf_note_id
421 ,p_last_update_date => sysdate
423 ,p_creation_date => sysdate
424 ,p_created_by => fnd_global.user_id
425 ,p_last_update_login => fnd_global.login_id
426 ,p_attribute1 => NULL
427 ,p_attribute2 => NULL
428 ,p_attribute3 => NULL
429 ,p_attribute4 => NULL
430 ,p_attribute5 => NULL
431 ,p_attribute6 => NULL
432 ,p_attribute7 => NULL
433 ,p_attribute8 => NULL
434 ,p_attribute9 => NULL
435 ,p_attribute10 => NULL
436 ,p_attribute11 => NULL
437 ,p_attribute12 => NULL
438 ,p_attribute13 => NULL
439 ,p_attribute14 => NULL
440 ,p_attribute15 => NULL
441 ,p_context => NULL
442 ,p_note_type => 'GENERAL'
443 );
444
445 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449 x_merge_request_id := l_dup_set_id;
450
451
452 -- Debug info.
453 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
454 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
455 p_msg_data=>x_msg_data,
456 p_msg_type=>'WARNING',
457 p_msg_level=>fnd_log.level_exception);
458 END IF;
459 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
460 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
461 p_prefix=>l_debug_prefix,
462 p_msg_level=>fnd_log.level_procedure);
463 END IF;
464
465 EXCEPTION
466 WHEN fnd_api.g_exc_error THEN
467 ROLLBACK TO create_dup_merge_req_pub;
468 x_return_status := fnd_api.g_ret_sts_error;
469
470 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
471 p_count => x_msg_count,
472 p_data => x_msg_data);
473
474 -- Debug info.
475 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
476 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
477 p_msg_data=>x_msg_data,
478 p_msg_type=>'ERROR',
479 p_msg_level=>fnd_log.level_error);
480 END IF;
481 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
482 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
483 p_prefix=>l_debug_prefix,
484 p_msg_level=>fnd_log.level_procedure);
485 END IF;
486
487 WHEN fnd_api.g_exc_unexpected_error THEN
488 ROLLBACK TO create_dup_merge_req_pub;
489 x_return_status := fnd_api.g_ret_sts_unexp_error;
490
491 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
492 p_count => x_msg_count,
493 p_data => x_msg_data);
494
495 -- Debug info.
496 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
497 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
498 p_msg_data=>x_msg_data,
499 p_msg_type=>'UNEXPECTED ERROR',
500 p_msg_level=>fnd_log.level_error);
501 END IF;
502 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
503 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
504 p_prefix=>l_debug_prefix,
505 p_msg_level=>fnd_log.level_procedure);
506 END IF;
507
508 WHEN OTHERS THEN
509 ROLLBACK TO create_dup_merge_req_pub;
510 x_return_status := fnd_api.g_ret_sts_unexp_error;
511
512 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
513 fnd_message.set_token('ERROR' ,SQLERRM);
514 fnd_msg_pub.add;
515
516 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
517 p_count => x_msg_count,
518 p_data => x_msg_data);
519
520 -- Debug info.
521 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
522 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
523 p_msg_data=>x_msg_data,
524 p_msg_type=>'SQL ERROR',
525 p_msg_level=>fnd_log.level_error);
526 END IF;
527 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
528 hz_utility_v2pub.debug(p_message=>'create_dup_merge_request(-)',
529 p_prefix=>l_debug_prefix,
530 p_msg_level=>fnd_log.level_procedure);
531 END IF;
532 END create_dup_merge_request;
533
534 END HZ_DUP_MERGE_PUB;