1 package body FUN_PARTY_MERGE_PUB AS
2 -- $Header:
3
4
5 --========================================================================
6 -- PROCEDURE : merge_trx_batches Called by HZ Party merge routine
7 -- Should not be called by any other application
8 --
9 -- COMMENT : This procedure is used to perform for following actions
10 -- When the relationship party merges
11 -- the corresponding initiator_id needs to be merged
12 --========================================================================
13 PROCEDURE merge_trx_batches(
14 p_Entity_name IN VARCHAR2, -- Name of the Entity being merged
15 p_from_id IN NUMBER, -- PK of the Party ID being merged
16 p_to_id IN OUT NOCOPY NUMBER, -- PK of the target Party ID; returned if duplicate
17 p_From_FK_id IN NUMBER, -- same as p_from_id
18 p_To_FK_id IN NUMBER, -- same as p_to_id
19 p_Parent_Entity_name IN VARCHAR2, -- should always be 'HZ_PARTIES'
20 p_batch_id IN NUMBER, -- Batch ID running the merge
21 p_Batch_Party_id IN NUMBER, -- same as the From Party ID
22 x_return_status OUT NOCOPY VARCHAR2)
23
24 IS
25
26 l_merge_reason_code VARCHAR2(30);
27 RESOURCE_BUSY EXCEPTION;
28 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
29
30 Cursor C1 is
31 Select 'X' from
32 FUN_TRX_BATCHES
33 Where initiator_id = p_from_fk_id
34 for update nowait;
35
36 Cursor C2 is
37 Select 'X' from
38 FUN_TRX_HEADERS
39 Where initiator_id = p_from_fk_id
40 for update nowait;
41
42 BEGIN
43
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 --check the merge reason, if merge reason is 'Duplicate Record' then no validation is performed.
47 --otherwise check if the resource is being used somewhere
48 SELECT merge_reason_code
49 INTO l_merge_reason_code
50 FROM hz_merge_batch
51 WHERE batch_id = p_batch_id;
52
53 IF l_merge_reason_code = 'DUPLICATE' THEN
54 -- if reason code is duplicate then allow the party merge to happen without
55 -- any validations.
56 null;
57 ELSE
58 -- if there are any validations to be done, include it in this section
59 null;
60 END IF;
61
62 /* Perform the Merge */
63
64 /* If Parent (i.e., Party ID) has NOT changed, then nothing needs to be done. Set
65 Merged To ID is the same as Merged From ID and Return */
66
67 IF p_from_FK_id = p_to_FK_id THEN
68 p_to_id := p_from_id;
69
70 RETURN;
71 END IF;
72
73 /********************************************************************************
74 If the Party_ID (Parent) has changed, then transfer the dependent record to the
75 new parent.
76 *******************************************************************************/
77
78 IF p_from_FK_id <> p_to_FK_id THEN
79
80 IF p_parent_entity_name = 'HZ_PARTIES' THEN
81
82 IF fun_tca_pkg.get_le_id(p_from_FK_id) = fun_tca_pkg.get_le_id(p_to_FK_id) THEN
83 fnd_message.set_name('FUN', 'FUN_LOCKING_TABLE');
84 fnd_message.set_token('TABLE_NAME', 'FUN_TRX_BATCHES', FALSE);
85
86 Open C1;
87 Close C1;
88
89 fnd_message.set_name('FUN', 'FUN_UPDATING_TABLE');
90 fnd_message.set_token('TABLE_NAME', 'FUN_TRX_BATCHES', FALSE);
91
92 /*delete the existing target party ID */
93 -- DELETE FROM FUN_TRX_BATCHES
94 -- WHERE party_id = p_To_Fk_id;
95
96 /* Update the "From" Party_ID to be equal to the new target */
97 UPDATE FUN_TRX_BATCHES
98 SET initiator_id = p_To_FK_id,
99 LAST_UPDATED_BY = hz_utility_pub.user_id,
100 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
101 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
102 WHERE initiator_id = p_From_FK_id;
103
104 Open C2;
105 Close C2;
106
107 UPDATE FUN_TRX_HEADERS
108 SET initiator_id = p_To_FK_id,
109 LAST_UPDATED_BY = hz_utility_pub.user_id,
110 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
111 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
112 WHERE initiator_id = p_From_FK_id;
113
114 END IF;
115
116 RETURN;
117
118 END IF;
119 END IF;
120
121 EXCEPTION
122 WHEN RESOURCE_BUSY THEN
123 FND_MESSAGE.SET_NAME('FUN','FUN_TRX_BATCHES_LOCK');
124 FND_MSG_PUB.ADD;
125 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
126 WHEN OTHERS THEN
127 FND_MESSAGE.SET_NAME('FUN','HZ_API_OTHERS_EXCEP');
128 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
129 FND_MSG_PUB.ADD;
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131
132 END merge_trx_batches;
133
134
135 --========================================================================
136 -- PROCEDURE : merge_trx_headers Called by HZ Party merge routine
137 -- Should not be called by any other application
138 --
139 -- COMMENT : This procedure is used to perform for following actions
140 -- When the relationship party merges
141 -- the corresponding recipient_id need to be merged
142 --========================================================================
143 PROCEDURE merge_trx_headers(
144 p_Entity_name IN VARCHAR2, -- Name of the Entity being merged
145 p_from_id IN NUMBER, -- PK of the Party ID being merged
146 p_to_id IN OUT NOCOPY NUMBER, -- PK of the target Party ID; returned if duplicate
147 p_From_FK_id IN NUMBER, -- same as p_from_id
148 p_To_FK_id IN NUMBER, -- same as p_to_id
149 p_Parent_Entity_name IN VARCHAR2, -- should always be 'HZ_PARTIES'
150 p_batch_id IN NUMBER, -- Batch ID running the merge
151 p_Batch_Party_id IN NUMBER, -- same as the From Party ID
152 x_return_status OUT NOCOPY VARCHAR2)
153
154 IS
155
156 l_merge_reason_code VARCHAR2(30);
157 RESOURCE_BUSY EXCEPTION;
158 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
159
160 Cursor C1 is
161 Select 'X' from
162 FUN_TRX_HEADERS
163 Where recipient_id = p_from_fk_id
164 for update nowait;
165
166 BEGIN
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 --check the merge reason, if merge reason is 'Duplicate Record' then no validation is performed.
171 --otherwise check if the resource is being used somewhere
172 SELECT merge_reason_code
173 INTO l_merge_reason_code
174 FROM hz_merge_batch
175 WHERE batch_id = p_batch_id;
176
177 IF l_merge_reason_code = 'DUPLICATE' THEN
178 -- if reason code is duplicate then allow the party merge to happen without
179 -- any validations.
180 null;
181 ELSE
182 -- if there are any validations to be done, include it in this section
183 null;
184 END IF;
185
186 /* Perform the Merge */
187
188 /* If Parent (i.e., Party ID) has NOT changed, then nothing needs to be done. Set
189 Merged To ID is the same as Merged From ID and Return */
190
191 IF p_from_FK_id = p_to_FK_id THEN
192 p_to_id := p_from_id;
193
194 RETURN;
195 END IF;
196
197 /********************************************************************************
198 If the Party_ID (Parent) has changed, then transfer the dependent record to the
199 new parent.
200 *******************************************************************************/
201
202 IF p_from_FK_id <> p_to_FK_id THEN
203
204 IF p_parent_entity_name = 'HZ_PARTIES' THEN
205
206 IF fun_tca_pkg.get_le_id(p_from_FK_id) = fun_tca_pkg.get_le_id(p_to_FK_id) THEN
207 fnd_message.set_name('FUN', 'FUN_LOCKING_TABLE');
208 fnd_message.set_token('TABLE_NAME', 'FUN_TRX_HEADERS', FALSE);
209
210 Open C1;
211 Close C1;
212
213 fnd_message.set_name('FUN', 'FUN_UPDATING_TABLE');
214 fnd_message.set_token('TABLE_NAME', 'FUN_TRX_HEADERS', FALSE);
215
216 /*delete the existing target party ID */
217 -- DELETE FROM FUN_TRX_BATCHES
218 -- WHERE party_id = p_To_Fk_id;
219
220
221 /* Update the "From" Party_ID to be equal to the new target */
222 UPDATE FUN_TRX_HEADERS
223 SET recipient_id = p_To_FK_id,
224 LAST_UPDATED_BY = hz_utility_pub.user_id,
225 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
226 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
227 WHERE recipient_id = p_From_FK_id;
228
229 END IF;
230
231 RETURN;
232
233 END IF;
234 END IF;
235
236 EXCEPTION
237 WHEN RESOURCE_BUSY THEN
238 FND_MESSAGE.SET_NAME('FUN','FUN_TRX_HEADERS_LOCK');
239 FND_MSG_PUB.ADD;
240 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
241 WHEN OTHERS THEN
242 FND_MESSAGE.SET_NAME('FUN','HZ_API_OTHERS_EXCEP');
243 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
244 FND_MSG_PUB.ADD;
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246
247 END merge_trx_headers;
248
249
250 --========================================================================
251 -- PROCEDURE : merge_dist_lines Called by HZ Party merge routine
252 -- Should not be called by any other application
253 --
254 -- COMMENT : This procedure is used to perform for following actions
255 -- When the relationship party merges
256 -- the corresponding party_id needs to be merged
257 --========================================================================
258 PROCEDURE merge_dist_lines(
259 p_Entity_name IN VARCHAR2, -- Name of the Entity being merged
260 p_from_id IN NUMBER, -- PK of the Party ID being merged
261 p_to_id IN OUT NOCOPY NUMBER, -- PK of the target Party ID; returned if duplicate
262 p_From_FK_id IN NUMBER, -- same as p_from_id
263 p_To_FK_id IN NUMBER, -- same as p_to_id
264 p_Parent_Entity_name IN VARCHAR2, -- should always be 'HZ_PARTIES'
265 p_batch_id IN NUMBER, -- Batch ID running the merge
266 p_Batch_Party_id IN NUMBER, -- same as the From Party ID
267 x_return_status OUT NOCOPY VARCHAR2)
268
269 IS
270
271 l_merge_reason_code VARCHAR2(30);
272 RESOURCE_BUSY EXCEPTION;
273 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
274
275 Cursor C1 is
276 Select 'X' from
277 FUN_DIST_LINES
278 Where party_id = p_from_fk_id
279 for update nowait;
280
281 BEGIN
282
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 --check the merge reason, if merge reason is 'Duplicate Record' then no validation is performed.
286 --otherwise check if the resource is being used somewhere
287 SELECT merge_reason_code
288 INTO l_merge_reason_code
289 FROM hz_merge_batch
290 WHERE batch_id = p_batch_id;
291
292 IF l_merge_reason_code = 'DUPLICATE' THEN
293 -- if reason code is duplicate then allow the party merge to happen without
294 -- any validations.
295 null;
296 ELSE
297 -- if there are any validations to be done, include it in this section
298 null;
299 END IF;
300
301 /* Perform the Merge */
302
303 /* If Parent (i.e., Party ID) has NOT changed, then nothing needs to be done. Set
304 Merged To ID is the same as Merged From ID and Return */
305
306 IF p_from_FK_id = p_to_FK_id THEN
307 p_to_id := p_from_id;
308
309 RETURN;
310 END IF;
311
312 /********************************************************************************
313 If the Party_ID (Parent) has changed, then transfer the dependent record to the
314 new parent.
315 *******************************************************************************/
316
317 IF p_from_FK_id <> p_to_FK_id THEN
318
319 IF p_parent_entity_name = 'HZ_PARTIES' THEN
320
321 IF fun_tca_pkg.get_le_id(p_from_FK_id) = fun_tca_pkg.get_le_id(p_to_FK_id) THEN
322 fnd_message.set_name('FUN', 'FUN_LOCKING_TABLE');
323 fnd_message.set_token('TABLE_NAME', 'FUN_DIST_LINES', FALSE);
324
325 Open C1;
326 Close C1;
327
328 fnd_message.set_name('FUN', 'FUN_UPDATING_TABLE');
329 fnd_message.set_token('TABLE_NAME', 'FUN_DIST_LINES', FALSE);
330
331 /*delete the existing target party ID */
332 -- DELETE FROM FUN_TRX_BATCHES
333 -- WHERE party_id = p_To_Fk_id;
334
335
336 /* Update the "From" Party_ID to be equal to the new target */
337 UPDATE FUN_DIST_LINES
338 SET party_id = p_To_FK_id,
339 LAST_UPDATED_BY = hz_utility_pub.user_id,
340 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
341 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
342 WHERE party_id = p_From_FK_id;
343
344 END IF;
345
346 RETURN;
347
348 END IF;
349 END IF;
350
351 EXCEPTION
352 WHEN RESOURCE_BUSY THEN
353 FND_MESSAGE.SET_NAME('FUN','FUN_DIST_LINES_LOCK');
354 FND_MSG_PUB.ADD;
355 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
356 WHEN OTHERS THEN
357 FND_MESSAGE.SET_NAME('FUN','HZ_API_OTHERS_EXCEP');
358 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
359 FND_MSG_PUB.ADD;
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361
362 END merge_dist_lines;
363
364
365 --========================================================================
366 -- PROCEDURE : merge_customer_maps Called by HZ Party merge routine
367 -- Should not be called by any other application
368 --
369 -- COMMENT : This procedure is used to perform for following actions
370 -- When the relationship party merges
371 -- the corresponding site_use_id needs to be merged
372 --========================================================================
373 PROCEDURE merge_customer_maps(
374 p_Entity_name IN VARCHAR2, -- Name of the Entity being merged
375 p_from_id IN NUMBER, -- PK of the Party ID being merged
376 p_to_id IN OUT NOCOPY NUMBER, -- PK of the target Party ID; returned if duplicate
377 p_From_FK_id IN NUMBER, -- same as p_from_id
378 p_To_FK_id IN NUMBER, -- same as p_to_id
379 p_Parent_Entity_name IN VARCHAR2, -- should always be 'HZ_PARTIES'
380 p_batch_id IN NUMBER, -- Batch ID running the merge
381 p_Batch_Party_id IN NUMBER, -- same as the From Party ID
382 x_return_status OUT NOCOPY VARCHAR2)
383
384 IS
385
386 l_merge_reason_code VARCHAR2(30);
387 RESOURCE_BUSY EXCEPTION;
388 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
389
390 Cursor C1 is
391 Select 'X' from
392 FUN_CUSTOMER_MAPS
393 Where site_use_id = p_from_fk_id
394 for update nowait;
395
396 BEGIN
397
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399
400 --check the merge reason, if merge reason is 'Duplicate Record' then no validation is performed.
401 --otherwise check if the resource is being used somewhere
402 SELECT merge_reason_code
403 INTO l_merge_reason_code
404 FROM hz_merge_batch
405 WHERE batch_id = p_batch_id;
406
407 IF l_merge_reason_code = 'DUPLICATE' THEN
408 -- if reason code is duplicate then allow the party merge to happen without
409 -- any validations.
410 null;
411 ELSE
412 -- if there are any validations to be done, include it in this section
413 null;
414 END IF;
415
416 /* Perform the Merge */
417
418 /* If Parent (i.e., Party ID) has NOT changed, then nothing needs to be done. Set
419 Merged To ID is the same as Merged From ID and Return */
420
421 IF p_from_FK_id = p_to_FK_id THEN
422 p_to_id := p_from_id;
423
424 RETURN;
425 END IF;
426
427 /********************************************************************************
428 If the Party_ID (Parent) has changed, then transfer the dependent record to the
429 new parent.
430 *******************************************************************************/
431
432 IF p_from_FK_id <> p_to_FK_id THEN
433
434 IF p_parent_entity_name = 'HZ_PARTY_SITE_USES' THEN
435
436 fnd_message.set_name('FUN', 'FUN_LOCKING_TABLE');
437 fnd_message.set_token('TABLE_NAME', 'FUN_CUSTOMER_MAPS', FALSE);
438
439 Open C1;
440 Close C1;
441
442 fnd_message.set_name('FUN', 'FUN_UPDATING_TABLE');
443 fnd_message.set_token('TABLE_NAME', 'FUN_CUSTOMER_MAPS', FALSE);
444
445 /*delete the existing target party ID */
446 -- DELETE FROM FUN_TRX_BATCHES
447 -- WHERE party_id = p_To_Fk_id;
448
449
450 /* Update the "From" Party_ID to be equal to the new target */
451 UPDATE FUN_CUSTOMER_MAPS
452 SET site_use_id = p_To_FK_id,
453 LAST_UPDATED_BY = hz_utility_pub.user_id,
454 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
455 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
456 WHERE site_use_id = p_From_FK_id;
457 RETURN;
458
459
460 END IF;
461 END IF;
462
463 EXCEPTION
464 WHEN RESOURCE_BUSY THEN
465 FND_MESSAGE.SET_NAME('FUN','FUN_CUSTOMER_MAPS_LOCK');
466 FND_MSG_PUB.ADD;
467 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
468 WHEN OTHERS THEN
469 FND_MESSAGE.SET_NAME('FUN','HZ_API_OTHERS_EXCEP');
470 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
471 FND_MSG_PUB.ADD;
472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
473
474 END merge_customer_maps;
475
476
477 --========================================================================
478 -- PROCEDURE : merge_supplier_maps Called by HZ Party merge routine
479 -- Should not be called by any other application
480 --
481 -- COMMENT : This procedure is used to perform for following actions
482 -- When the relationship party merges
483 -- the corresponding vendor_site_id needs to be merged
484 --========================================================================
485 PROCEDURE merge_supplier_maps(
486 p_Entity_name IN VARCHAR2, -- Name of the Entity being merged
487 p_from_id IN NUMBER, -- PK of the Party ID being merged
488 p_to_id IN OUT NOCOPY NUMBER, -- PK of the target Party ID; returned if duplicate
489 p_From_FK_id IN NUMBER, -- same as p_from_id
490 p_To_FK_id IN NUMBER, -- same as p_to_id
491 p_Parent_Entity_name IN VARCHAR2, -- should always be 'HZ_PARTIES'
492 p_batch_id IN NUMBER, -- Batch ID running the merge
493 p_Batch_Party_id IN NUMBER, -- same as the From Party ID
494 x_return_status OUT NOCOPY VARCHAR2)
495
496 IS
497
498 l_merge_reason_code VARCHAR2(30);
499 RESOURCE_BUSY EXCEPTION;
500 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
501
502 Cursor C1 is
503 Select 'X' from
504 FUN_SUPPLIER_MAPS
505 Where vendor_site_id = p_from_fk_id
506 for update nowait;
507
508 BEGIN
509
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511
512 --check the merge reason, if merge reason is 'Duplicate Record' then no validation is performed.
513 --otherwise check if the resource is being used somewhere
514 SELECT merge_reason_code
515 INTO l_merge_reason_code
516 FROM hz_merge_batch
517 WHERE batch_id = p_batch_id;
518
519 IF l_merge_reason_code = 'DUPLICATE' THEN
520 -- if reason code is duplicate then allow the party merge to happen without
521 -- any validations.
522 null;
523 ELSE
524 -- if there are any validations to be done, include it in this section
525 null;
526 END IF;
527
528 /* Perform the Merge */
529
530 /* If Parent (i.e., Party ID) has NOT changed, then nothing needs to be done. Set
531 Merged To ID is the same as Merged From ID and Return */
532
533 IF p_from_FK_id = p_to_FK_id THEN
534 p_to_id := p_from_id;
535
536 RETURN;
537 END IF;
538
539 /********************************************************************************
540 If the Party_ID (Parent) has changed, then transfer the dependent record to the
541 new parent.
542 *******************************************************************************/
543
544 IF p_from_FK_id <> p_to_FK_id THEN
545
546 IF p_parent_entity_name = 'HZ_PARTY_SITES' THEN
547
548 fnd_message.set_name('FUN', 'FUN_LOCKING_TABLE');
549 fnd_message.set_token('TABLE_NAME', 'FUN_SUPPLIER_MAPS', FALSE);
550
551 Open C1;
552 Close C1;
553
554 fnd_message.set_name('FUN', 'FUN_UPDATING_TABLE');
555 fnd_message.set_token('TABLE_NAME', 'FUN_SUPPLIER_MAPS', FALSE);
556
557 /*delete the existing target party ID */
558 -- DELETE FROM FUN_TRX_BATCHES
559 -- WHERE party_id = p_To_Fk_id;
560
561
562 /* Update the "From" Party_ID to be equal to the new target */
563 UPDATE FUN_SUPPLIER_MAPS
564 SET vendor_site_id = p_To_FK_id,
565 LAST_UPDATED_BY = hz_utility_pub.user_id,
566 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
567 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
568 WHERE vendor_site_id = p_From_FK_id;
569 RETURN;
570
571
572 END IF;
573 END IF;
574
575 EXCEPTION
576 WHEN RESOURCE_BUSY THEN
577 FND_MESSAGE.SET_NAME('FUN','FUN_SUPPLIER_MAPS_LOCK');
578 FND_MSG_PUB.ADD;
579 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
580 WHEN OTHERS THEN
581 FND_MESSAGE.SET_NAME('FUN','HZ_API_OTHERS_EXCEP');
582 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
583 FND_MSG_PUB.ADD;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585
586 END merge_supplier_maps;
587
588
589 END FUN_PARTY_MERGE_PUB;
[Home] [Help]