1 PACKAGE BODY PVX_PARTY_MERGE_PKG AS
2 /* $Header: pvxvmrgb.pls 120.7 2006/06/12 10:37:33 rdsharma ship $ */
3
4 -- Start of Comments
5 -- Package name : PVX_PARTY_MERGE_PKG
6 -- Purpose : Merges duplicate parties in PV tables. The
7
8 --
9 -- History
10 -- MM-DD-YYYY NAME MODIFICATIONS
11 -- 02-21-2001 ajchatto added MERGE_PARTNER_ENTITY_ATTRIBUTES procedure to merge party
12 --
13 -- End of Comments
14
15
16 G_PROC_NAME CONSTANT VARCHAR2(30) := 'PV_PARTY_MERGE_PKG';
17 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
18 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PVX_PARTY_MERGE_PKG';
20
21
22 -- -----------------------------------------------------------------------------------
23 -- Use for inserting output messages to the message table.
24 -- -----------------------------------------------------------------------------------
25 PROCEDURE Debug(
26 p_msg_string IN VARCHAR2
27 );
28
29 PROCEDURE Set_Message(
30 p_msg_level IN NUMBER,
31 p_msg_name IN VARCHAR2,
32 p_token1 IN VARCHAR2,
33 p_token1_value IN VARCHAR2,
34 p_token2 IN VARCHAR2 := NULL,
35 p_token2_value IN VARCHAR2 := NULL,
36 p_token3 IN VARCHAR2 := NULL,
37 p_token3_value IN VARCHAR2 := NULL
38 );
39
40
41
42 -- --------------------------------------------------------------------------
43 -- MERGE_REFERRALS_B
44 --
45 -- --------------------------------------------------------------------------
46 PROCEDURE MERGE_REFERRALS_B (
47 p_entity_name IN VARCHAR2,
48 p_from_id IN NUMBER,
49 p_to_id OUT NOCOPY NUMBER,
50 p_from_fk_id IN NUMBER,
51 p_to_fk_id IN NUMBER,
52 p_parent_entity_name IN VARCHAR2,
53 p_batch_id IN NUMBER,
54 p_batch_party_id IN NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2)
56 IS
57 l_api_name VARCHAR2(30) := 'MERGE_REFERRALS_B';
58 l_msg_count NUMBER;
59 l_msg_data VARCHAR2(4000);
60
61 RESOURCE_BUSY EXCEPTION;
62 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
63
64 BEGIN
65 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
66 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
67
68
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 IF (p_from_fk_id = p_to_fk_id) THEN
72 p_to_id := p_from_id;
73 RETURN;
74 END IF;
75
76 IF p_from_fk_id <> p_to_fk_id THEN
77 BEGIN
78 -- ---------------------------------------------------------------
79 -- Merge party (hz_parties)
80 -- ---------------------------------------------------------------
81 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
82 UPDATE pv_referrals_b
83 SET partner_id = p_to_fk_id,
84 last_update_date = SYSDATE,
85 last_updated_by = G_USER_ID,
86 last_update_login = G_LOGIN_ID
87 WHERE partner_id = p_from_fk_id;
88
89 UPDATE pv_referrals_b
90 SET customer_party_id = p_to_fk_id,
91 last_update_date = SYSDATE,
92 last_updated_by = G_USER_ID,
93 last_update_login = G_LOGIN_ID
94 WHERE customer_party_id = p_from_fk_id;
95
96 -- ---------------------------------------------------------------
97 -- Merge party_sites (hz_party_sites)
98 -- ---------------------------------------------------------------
99 ELSIF (p_parent_entity_name = 'HZ_PARTY_SITES') THEN
100 UPDATE pv_referrals_b
101 SET customer_party_site_id = p_to_fk_id,
102 last_update_date = SYSDATE,
103 last_updated_by = G_USER_ID,
104 last_update_login = G_LOGIN_ID
105 WHERE customer_party_site_id = p_from_fk_id;
106
107 -- ---------------------------------------------------------------
108 -- Merge contact_points (hz_contact_points)
109 -- ---------------------------------------------------------------
110 ELSIF (p_parent_entity_name = 'HZ_CONTACT_POINTS') THEN
111 UPDATE pv_referrals_b
112 SET customer_contact_party_id = p_to_fk_id,
113 last_update_date = SYSDATE,
114 last_updated_by = G_USER_ID,
115 last_update_login = G_LOGIN_ID
116 WHERE customer_contact_party_id = p_from_fk_id;
117
118 -- ---------------------------------------------------------------
119 -- Merge org_contacts (hz_org_contacts)
120 -- ---------------------------------------------------------------
121 ELSIF (p_parent_entity_name = 'HZ_ORG_CONTACTS') THEN
122 UPDATE pv_referrals_b
123 SET customer_org_contact_id = p_to_fk_id,
124 last_update_date = SYSDATE,
125 last_updated_by = G_USER_ID,
126 last_update_login = G_LOGIN_ID
127 WHERE customer_org_contact_id = p_from_fk_id;
128 END IF;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 Debug(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
133 x_return_status := FND_API.G_RET_STS_ERROR;
134 raise;
135 END;
136 END IF;
137
138 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
139 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
140 END MERGE_REFERRALS_B;
141
142 -----------------------------------------------------------------------------
143 --Function to check if the party is a PV Partner
144 -----------------------------------------------------------------------------
145
146 FUNCTION IsPVPartner(p_party_id NUMBER)
147 return VARCHAR2
148 IS
149 CURSOR IsPVPartner IS
150 SELECT partner_party_id from pv_partner_profiles where
151 partner_party_id = p_party_id and
152 (status = 'A' OR STATUS = 'I');
153
154 l_partner_party_id number;
155
156 BEGIN
157 OPEN IsPVPartner;
158 FETCH IsPVPartner into l_partner_party_id;
159 if IsPVPartner%found then
160 CLOSE IsPVPartner;
161 return 'Y';
162 else
163 CLOSE IsPVPartner;
164 return 'N';
165 end if;
166
167 END;
168
169 -- --------------------------------------------------------------------------
170
171 -----------------------------------------------------------------------------
172 --Function to get the Internal Vendor PARTY_ID
173 -----------------------------------------------------------------------------
174
175 FUNCTION get_intVendorOrg(p_party_id NUMBER, p_partner_id NUMBER)
176 return NUMBER
177 IS
178
179 CURSOR intVendOrg_csr(cv_party_id NUMBER, cv_partner_id NUMBER) IS
180 SELECT hzr.object_id
181 FROM pv_partner_profiles ppp,
182 hz_relationships hzr
183 WHERE ppp.partner_party_id = cv_party_id
184 AND ppp.partner_id = cv_partner_id
185 AND hzr.subject_id = ppp.partner_party_id
186 AND hzr.party_id = ppp.partner_id
187 AND hzr.subject_type = 'ORGANIZATION'
188 AND hzr.subject_table_name = 'HZ_PARTIES'
189 AND hzr.object_type = 'ORGANIZATION'
190 AND hzr.object_table_name = 'HZ_PARTIES'
191 AND hzr.relationship_code = 'PARTNER_OF' ;
192
193 l_vendor_id number;
194
195 BEGIN
196 OPEN intVendOrg_csr(p_party_id, p_partner_id);
197 FETCH intVendOrg_csr into l_vendor_id;
198 CLOSE intVendOrg_csr;
199
200 return l_vendor_id;
201 END;
202
203 -- --------------------------------------------------------------------------
204
205 -- --------------------------------------------------------------------------
206 -- MERGE_PARTNER_PROFILES1
207 --
208 -- This is a blank API which does not do any updates.
209 -- --------------------------------------------------------------------------
210 PROCEDURE MERGE_PARTNER_PROFILES1 (
211 p_entity_name IN VARCHAR2,
212 p_from_id IN NUMBER,
213 p_to_id OUT NOCOPY NUMBER,
214 p_from_fk_id IN NUMBER,
215 p_to_fk_id IN NUMBER,
216 p_parent_entity_name IN VARCHAR2,
217 p_batch_id IN NUMBER,
218 p_batch_party_id IN NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2)
220 IS
221 l_api_name VARCHAR2(30) := 'MERGE_PARTNER_PROFILES1';
222 l_msg_count NUMBER;
223 l_msg_data VARCHAR2(4000);
224
225 RESOURCE_BUSY EXCEPTION;
226 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
227
228 BEGIN
229 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
230 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
231
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
235 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
236
237 exception
238 WHEN FND_API.G_EXC_ERROR THEN
239 x_return_status := FND_API.G_RET_STS_ERROR;
240 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
241 p_count => l_msg_count,
242 p_data => l_msg_data);
243
244 when others then
245 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
246 fnd_message.set_token('ERROR',SQLERRM);
247 fnd_msg_pub.add;
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 raise;
250
251 END MERGE_PARTNER_PROFILES1;
252
253 -- --------------------------------------------------------------------------
254 -- MERGE_PARTNER_PROFILES2
255 -- --------------------------------------------------------------------------
256
257 PROCEDURE MERGE_PARTNER_PROFILES2 (
258 p_entity_name IN VARCHAR2,
259 p_from_id IN NUMBER,
260 p_to_id OUT NOCOPY NUMBER,
261 p_from_fk_id IN NUMBER,
262 p_to_fk_id IN NUMBER,
263 p_parent_entity_name IN VARCHAR2,
264 p_batch_id IN NUMBER,
265 p_batch_party_id IN NUMBER,
266 x_return_status OUT NOCOPY VARCHAR2)
267 IS
268 l_merge_reason_code VARCHAR2(30);
269 l_api_name VARCHAR2(30) := 'MERGE_PARTNER_PROFILES2';
270 l_msg_count NUMBER;
271 l_msg_data VARCHAR2(4000);
272 l_to_partner_id NUMBER;
273 l_exist NUMBER;
274
275 RESOURCE_BUSY EXCEPTION;
276 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
277
278 -- ------------------------------------------------------------------
279 -- These are the "from" records that are still "active" in
280 -- hz_relationships. We don't need any that have been "merged."
281 -- ------------------------------------------------------------------
282 /* CURSOR c1 IS
283 SELECT DISTINCT a.partner_profile_id, a.partner_party_id, b.party_id partner_id
284 FROM pv_partner_profiles a,
285 hz_relationships b
286 WHERE a.partner_party_id = p_from_fk_id AND
287 a.partner_id = b.party_id AND
288 b.relationship_code = 'PARTNER_OF' AND
289 b.status = 'A'; */
290
291 /**** Fixed the issue reported in bug # 5307731 by adding STATUS check **********/
292 CURSOR c1 IS
293 SELECT partner_profile_id, partner_party_id, partner_id
294 FROM pv_partner_profiles
295 WHERE partner_party_id = p_from_fk_id
296 AND status = 'A';
297
298 /**** Fixed the issue reported in bug # 5307731 by adding STATUS check **********/
299 CURSOR c2 IS
300 SELECT partner_profile_id, partner_party_id, partner_id
301 FROM pv_partner_profiles
302 WHERE partner_party_id = p_to_fk_id
303 AND status = 'A';
304
305 BEGIN
306
307 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
308 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
309
310 x_return_status := FND_API.G_RET_STS_SUCCESS;
311
312 IF (p_from_fk_id = p_to_fk_id) THEN
313 p_to_id := p_from_id;
314 RETURN;
315 END IF;
316
317 -- -------------------------------------------------------------------
318 -- We do not want to update partner_party_id of any records if the
319 -- corresponding PARTNER_OF relationship has a status of 'M'. We are
320 -- only interested in "active" ones.
321 -- -------------------------------------------------------------------
322 /* FOR x IN c1 LOOP
323 UPDATE pv_partner_profiles
324 SET partner_party_id = p_to_fk_id,
325 last_update_date = SYSDATE,
326 last_updated_by = G_USER_ID,
327 last_update_login = G_LOGIN_ID
328 WHERE partner_profile_id = x.partner_profile_id;
329 END LOOP; */
330
331
332 --PN Coding starts here
333 if IsPVPartner(p_from_fk_id) = 'Y' THEN
334 if IsPVPartner(p_to_fk_id) = 'Y' THEN
335 -- Update the status of the from to merged
336 FOR x IN c1 LOOP
337 FOR y IN c2 LOOP
338 IF get_intVendorOrg(x.partner_party_id, x.partner_id) = get_intVendorOrg(y.partner_party_id, y.partner_id)
339 THEN
340 UPDATE pv_partner_profiles
341 SET status = 'M',
342 last_update_date = SYSDATE,
343 last_updated_by = G_USER_ID,
344 last_update_login = G_LOGIN_ID
345 WHERE partner_profile_id = x.partner_profile_id;
346 ELSE
347
348 UPDATE pv_partner_profiles
349 SET partner_party_id = p_to_fk_id,
350 last_update_date = SYSDATE,
351 last_updated_by = G_USER_ID,
352 last_update_login = G_LOGIN_ID
353 WHERE partner_profile_id = x.partner_profile_id;
354 END IF;
355 END LOOP;
356 END LOOP;
357 else
358
359 -- Update the party id of the partner to that of customer/Non PRM partner
360 FOR x IN c1 LOOP
361 UPDATE pv_partner_profiles
362 SET partner_party_id = p_to_fk_id,
363 last_update_date = SYSDATE,
364 last_updated_by = G_USER_ID,
365 last_update_login = G_LOGIN_ID
366 WHERE partner_profile_id = x.partner_profile_id;
367 END LOOP;
368 end if;
369
370 end if;
371
372 -- --------------------------------------------------------
373 -- Exception Handling
374 -- --------------------------------------------------------
375 exception
376 WHEN FND_API.G_EXC_ERROR THEN
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
379 p_count => l_msg_count,
380 p_data => l_msg_data);
381
382 when others then
383 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
384 fnd_message.set_token('ERROR',SQLERRM);
385 fnd_msg_pub.add;
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 raise;
388
389 END MERGE_PARTNER_PROFILES2;
390
391
392
393 -- --------------------------------------------------------------------------
394 -- MERGE_PARTNER_ENTITY_ATTRS
395 --
396 -- This is a blank API which does not do any updates.
397 -- --------------------------------------------------------------------------
398 PROCEDURE MERGE_PARTNER_ENTITY_ATTRS (
399 p_entity_name IN VARCHAR2,
400 p_from_id IN NUMBER,
401 p_to_id OUT NOCOPY NUMBER,
402 p_from_fk_id IN NUMBER,
403 p_to_fk_id IN NUMBER,
404 p_parent_entity_name IN VARCHAR2,
405 p_batch_id IN NUMBER,
406 p_batch_party_id IN NUMBER,
407 x_return_status OUT NOCOPY VARCHAR2)
408 IS
409 l_api_name VARCHAR2(30) := 'MERGE_PARTNER_PROFILES';
410
411 RESOURCE_BUSY EXCEPTION;
412 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
413
414 BEGIN
415 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
416 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
417
418 x_return_status := FND_API.G_RET_STS_SUCCESS;
419
420 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
421 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
422
423 exception
424 when others then
425 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
426 fnd_message.set_token('ERROR',SQLERRM);
427 fnd_msg_pub.add;
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429 raise;
430
431 END MERGE_PARTNER_ENTITY_ATTRS;
432
433
434 -- --------------------------------------------------------------------------
435 -- MERGE_LEAD_ASSIGNMENTS
436 -- --------------------------------------------------------------------------
437 PROCEDURE MERGE_LEAD_ASSIGNMENTS (
438 p_entity_name IN VARCHAR2,
439 p_from_id IN NUMBER,
440 p_to_id OUT NOCOPY NUMBER,
441 p_from_fk_id IN NUMBER,
442 p_to_fk_id IN NUMBER,
443 p_parent_entity_name IN VARCHAR2,
444 p_batch_id IN NUMBER,
445 p_batch_party_id IN NUMBER,
446 x_return_status OUT NOCOPY VARCHAR2)
447 IS
448 cursor c1 is
449 select 1
450 from PV_LEAD_ASSIGNMENTS
451 where partner_id = p_from_fk_id
452 for update nowait;
453
454
455 l_merge_reason_code VARCHAR2(30);
456 l_api_name VARCHAR2(30) := 'MERGE_LEAD_ASSIGNMENTS';
457 l_count NUMBER(10) := 0;
458 l_ppf_id NUMBER := Null;
459
460 RESOURCE_BUSY EXCEPTION;
461 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
462
463 BEGIN
464 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
465 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
466
467 x_return_status := FND_API.G_RET_STS_SUCCESS;
468
469 select merge_reason_code
470 into l_merge_reason_code
471 from hz_merge_batch
472 where batch_id = p_batch_id;
473
474 if l_merge_reason_code = 'DUPLICATE' then
475 -- if reason code is duplicate then allow the party merge to happen without
476 -- any validations.
477 null;
478 else
479 -- if there are any validations to be done, include it in this section
480 null;
481 end if;
482
483 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
484 -- needs to be done. Set Merged To Id is same as Merged From Id and return
485
486 if p_from_fk_id = p_to_fk_id then
487 p_to_id := p_from_id;
488 return;
489 end if;
490
491 -- If the parent has changed(ie. Parent is getting merged) then transfer the
492 -- dependent record to the new parent. Before transferring check if a similar
493 -- dependent record exists on the new parent. If a duplicate exists then do
494 -- not transfer and return the id of the duplicate record as the Merged To Id
495
496
497 IF p_from_fk_id <> p_to_fk_id THEN
498 FOR x IN (SELECT DISTINCT partner_id
499 FROM pv_lead_assignments
500 WHERE partner_id = p_to_fk_id)
501 LOOP
502 p_to_id := x.partner_id;
503 END LOOP;
504
505 IF (p_to_id IS NULL) THEN
506 FOR x IN (SELECT DISTINCT related_party_id
507 FROM pv_lead_assignments
508 WHERE related_party_id = p_to_fk_id)
509 LOOP
510 p_to_id := x.related_party_id;
511 END LOOP;
512 END IF;
513
514 UPDATE PV_LEAD_ASSIGNMENTS
515 SET partner_id = p_to_fk_id,
516 last_update_date = SYSDATE,
517 last_updated_by = G_USER_ID,
518 last_update_login = G_LOGIN_ID
519 WHERE partner_id = p_from_fk_id;
520 END IF;
521
522 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
523 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
524
525
526 exception
527 when others then
528 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
529 fnd_message.set_token('ERROR',SQLERRM);
530 fnd_msg_pub.add;
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532 raise;
533
534 END MERGE_LEAD_ASSIGNMENTS;
535
536
537 PROCEDURE MERGE_ASSIGNMENT_LOGS (
538 p_entity_name IN VARCHAR2,
539 p_from_id IN NUMBER,
540 p_to_id OUT NOCOPY NUMBER,
541 p_from_fk_id IN NUMBER,
542 p_to_fk_id IN NUMBER,
543 p_parent_entity_name IN VARCHAR2,
544 p_batch_id IN NUMBER,
545 p_batch_party_id IN NUMBER,
546 x_return_status OUT NOCOPY VARCHAR2)
547 IS
548 cursor c1 is
549 select 1
550 from PV_ASSIGNMENT_LOGS
551 where partner_id = p_from_fk_id
552 for update nowait;
553
554
555 l_merge_reason_code VARCHAR2(30);
556 l_api_name VARCHAR2(30) := 'MERGE_ASSIGNMENT_LOGS';
557 l_count NUMBER(10) := 0;
558 l_ppf_id NUMBER := Null;
559
560 RESOURCE_BUSY EXCEPTION;
561 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
562
563 BEGIN
564 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
565 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
566
567 x_return_status := FND_API.G_RET_STS_SUCCESS;
568
569 select merge_reason_code
570 into l_merge_reason_code
571 from hz_merge_batch
572 where batch_id = p_batch_id;
573
574 if l_merge_reason_code = 'DUPLICATE' then
575 -- if reason code is duplicate then allow the party merge to happen without
576 -- any validations.
577 null;
578 else
579 -- if there are any validations to be done, include it in this section
580 null;
581 end if;
582
583 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
584 -- needs to be done. Set Merged To Id is same as Merged From Id and return
585
586 if p_from_fk_id = p_to_fk_id then
587 p_to_id := p_from_id;
588 return;
589 end if;
590
591 -- If the parent has changed(ie. Parent is getting merged) then transfer the
592 -- dependent record to the new parent. Before transferring check if a similar
593 -- dependent record exists on the new parent. If a duplicate exists then do
594 -- not transfer and return the id of the duplicate record as the Merged To Id
595
596
597 if p_from_fk_id <> p_to_fk_id then
598
599 begin
600 select DISTINCT partner_id into l_ppf_id
601 from PV_ASSIGNMENT_LOGS
602 where partner_id = p_to_fk_id;
603 exception
604 When NO_DATA_FOUND then
605 l_ppf_id := Null;
606 end;
607
608
609 update PV_ASSIGNMENT_LOGS
610 set partner_id = decode(partner_id, p_from_fk_id, p_to_fk_id, partner_id),
611 last_update_date = SYSDATE,
612 last_updated_by = G_USER_ID,
613 last_update_login = G_LOGIN_ID
614 where partner_id = p_from_fk_id;
615
616 if l_ppf_id is not Null then
617 p_to_id := l_ppf_id;
618 end if;
619
620 end if;
621
622 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
623 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
624
625 exception
626 when others then
627 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
628 fnd_message.set_token('ERROR',SQLERRM);
629 fnd_msg_pub.add;
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 raise;
632
633 END MERGE_ASSIGNMENT_LOGS;
634
635
636 -- --------------------------------------------------------------------------
637 -- MERGE_SEARCH_ATTR_VALUES
638 --
639 -- This is a blank API which does not do any updates.
640 -- --------------------------------------------------------------------------
641 PROCEDURE MERGE_SEARCH_ATTR_VALUES (
642 p_entity_name IN VARCHAR2,
643 p_from_id IN NUMBER,
644 p_to_id OUT NOCOPY NUMBER,
645 p_from_fk_id IN NUMBER,
646 p_to_fk_id IN NUMBER,
647 p_parent_entity_name IN VARCHAR2,
648 p_batch_id IN NUMBER,
649 p_batch_party_id IN NUMBER,
650 x_return_status OUT NOCOPY VARCHAR2)
651 IS
652 l_api_name VARCHAR2(30) := 'MERGE_SEARCH_ATTR_VALUES';
653
654 RESOURCE_BUSY EXCEPTION;
655 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
656
657 BEGIN
658 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
659 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
660
661 x_return_status := FND_API.G_RET_STS_SUCCESS;
662
663 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
664 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
665
666 exception
667 when others then
668 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
669 fnd_message.set_token('ERROR',SQLERRM);
670 fnd_msg_pub.add;
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 raise;
673
674 END MERGE_SEARCH_ATTR_VALUES;
675
676
677 PROCEDURE MERGE_LEAD_PSS_LINES (
678 p_entity_name IN VARCHAR2,
679 p_from_id IN NUMBER,
680 p_to_id OUT NOCOPY NUMBER,
681 p_from_fk_id IN NUMBER,
682 p_to_fk_id IN NUMBER,
683 p_parent_entity_name IN VARCHAR2,
684 p_batch_id IN NUMBER,
685 p_batch_party_id IN NUMBER,
686 x_return_status OUT NOCOPY VARCHAR2)
687 IS
688 cursor c1 is
689 select 1
690 from PV_LEAD_PSS_LINES
691 where partner_id = p_from_fk_id
692 for update nowait;
693
694
695 l_merge_reason_code VARCHAR2(30);
696 l_api_name VARCHAR2(30) := 'MERGE_LEAD_PSS_LINES';
697 l_count NUMBER(10) := 0;
698 l_ppf_id NUMBER := Null;
699
700 RESOURCE_BUSY EXCEPTION;
701 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
702
703 BEGIN
704 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
705 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
706
707 x_return_status := FND_API.G_RET_STS_SUCCESS;
708
709 select merge_reason_code
710 into l_merge_reason_code
711 from hz_merge_batch
712 where batch_id = p_batch_id;
713
714 if l_merge_reason_code = 'DUPLICATE' then
715 -- if reason code is duplicate then allow the party merge to happen without
716 -- any validations.
717 null;
718 else
719 -- if there are any validations to be done, include it in this section
720 null;
721 end if;
722
723 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
724 -- needs to be done. Set Merged To Id is same as Merged From Id and return
725
726 if p_from_fk_id = p_to_fk_id then
727 p_to_id := p_from_id;
728 return;
729 end if;
730
731 -- If the parent has changed(ie. Parent is getting merged) then transfer the
732 -- dependent record to the new parent. Before transferring check if a similar
733 -- dependent record exists on the new parent. If a duplicate exists then do
734 -- not transfer and return the id of the duplicate record as the Merged To Id
735
736
737 if p_from_fk_id <> p_to_fk_id then
738
739 begin
740 select DISTINCT partner_id into l_ppf_id
741 from PV_LEAD_PSS_LINES
742 where partner_id = p_to_fk_id;
743 exception
744 When NO_DATA_FOUND then
745 l_ppf_id := Null;
746 end;
747
748
749 update PV_LEAD_PSS_LINES
750 set partner_id = decode(partner_id, p_from_fk_id, p_to_fk_id, partner_id),
751 last_update_date = SYSDATE,
752 last_updated_by = G_USER_ID,
753 last_update_login = G_LOGIN_ID
754 where partner_id = p_from_fk_id;
755
756 if l_ppf_id is not Null then
757 p_to_id := l_ppf_id;
758 end if;
759
760 end if;
761
762 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
763 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
764
765
766 exception
767 when others then
768 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
769 fnd_message.set_token('ERROR',SQLERRM);
770 fnd_msg_pub.add;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 raise;
773
774 END MERGE_LEAD_PSS_LINES;
775
776
777 PROCEDURE MERGE_GE_PARTY_NOTIFICATIONS (
778 p_entity_name IN VARCHAR2,
779 p_from_id IN NUMBER,
780 p_to_id OUT NOCOPY NUMBER,
781 p_from_fk_id IN NUMBER,
782 p_to_fk_id IN NUMBER,
783 p_parent_entity_name IN VARCHAR2,
784 p_batch_id IN NUMBER,
785 p_batch_party_id IN NUMBER,
786 x_return_status OUT NOCOPY VARCHAR2)
787 IS
788 cursor c1 is
789 select 1
790 from PV_GE_PARTY_NOTIFICATIONS
791 where partner_id = p_from_fk_id
792 for update nowait;
793
794
795 l_merge_reason_code VARCHAR2(30);
796 l_api_name VARCHAR2(30) := 'MERGE_GE_PARTY_NOTIFICATIONS';
797 l_count NUMBER(10) := 0;
798 l_ppf_id NUMBER := Null;
799
800 RESOURCE_BUSY EXCEPTION;
801 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
802
803 BEGIN
804 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
805 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
806
807 x_return_status := FND_API.G_RET_STS_SUCCESS;
808
809 select merge_reason_code
810 into l_merge_reason_code
811 from hz_merge_batch
812 where batch_id = p_batch_id;
813
814 if l_merge_reason_code = 'DUPLICATE' then
815 -- if reason code is duplicate then allow the party merge to happen without
816 -- any validations.
817 null;
818 else
819 -- if there are any validations to be done, include it in this section
820 null;
821 end if;
822
823 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
824 -- needs to be done. Set Merged To Id is same as Merged From Id and return
825
826 if p_from_fk_id = p_to_fk_id then
827 p_to_id := p_from_id;
828 return;
829 end if;
830
831 -- If the parent has changed(ie. Parent is getting merged) then transfer the
832 -- dependent record to the new parent. Before transferring check if a similar
833 -- dependent record exists on the new parent. If a duplicate exists then do
834 -- not transfer and return the id of the duplicate record as the Merged To Id
835
836
837 if p_from_fk_id <> p_to_fk_id then
838
839
840 BEGIN
841 SELECT DISTINCT partner_id INTO l_ppf_id
842 FROM PV_GE_PARTY_NOTIFICATIONS
843 WHERE partner_id = p_to_fk_id;
844 EXCEPTION
845 WHEN NO_DATA_FOUND THEN
846 l_ppf_id := Null;
847 END;
848
849
850
851 update PV_GE_PARTY_NOTIFICATIONS
852 set partner_id = p_to_fk_id,
853 last_update_date = SYSDATE,
854 last_updated_by = G_USER_ID,
855 last_update_login = G_LOGIN_ID
856 where partner_id = p_from_fk_id;
857
858 if l_ppf_id is not Null then
859 p_to_id := l_ppf_id;
860 end if;
861
862 end if;
863
864 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
865 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
866
867 exception
868 when others then
869 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
870 fnd_message.set_token('ERROR',SQLERRM);
871 fnd_msg_pub.add;
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
873 raise;
874
875 END MERGE_GE_PARTY_NOTIFICATIONS;
876
877 -- blank api
878 PROCEDURE MERGE_PG_ENRL_REQUESTS (
879 p_entity_name IN VARCHAR2,
880 p_from_id IN NUMBER,
881 p_to_id OUT NOCOPY NUMBER,
882 p_from_fk_id IN NUMBER,
883 p_to_fk_id IN NUMBER,
884 p_parent_entity_name IN VARCHAR2,
885 p_batch_id IN NUMBER,
886 p_batch_party_id IN NUMBER,
887 x_return_status OUT NOCOPY VARCHAR2)
888 IS
889 cursor c1 is
890 select 1
891 from PV_PG_ENRL_REQUESTS
892 where partner_id = p_from_fk_id
893 for update nowait;
894
895
896 l_merge_reason_code VARCHAR2(30);
897 l_api_name VARCHAR2(30) := 'MERGE_PG_ENRL_REQUESTS';
898 l_count NUMBER(10) := 0;
899 l_ppf_id NUMBER := Null;
900
901 RESOURCE_BUSY EXCEPTION;
902 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
903
904 BEGIN
905 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
906 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
907
908 x_return_status := FND_API.G_RET_STS_SUCCESS;
909
910 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
911 -- needs to be done. Set Merged To Id is same as Merged From Id and return
912
913 if p_from_fk_id = p_to_fk_id then
914 p_to_id := p_from_id;
915 return;
916 end if;
917
918
919 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
920 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
921
922
923 exception
924 when others then
925 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
926 fnd_message.set_token('ERROR',SQLERRM);
927 fnd_msg_pub.add;
928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
929 raise;
930
931 END MERGE_PG_ENRL_REQUESTS;
932
933
934 PROCEDURE MERGE_PG_MEMBERSHIPS (
935 p_entity_name IN VARCHAR2,
936 p_from_id IN NUMBER,
937 p_to_id OUT NOCOPY NUMBER,
938 p_from_fk_id IN NUMBER,
939 p_to_fk_id IN NUMBER,
940 p_parent_entity_name IN VARCHAR2,
941 p_batch_id IN NUMBER,
942 p_batch_party_id IN NUMBER,
943 x_return_status OUT NOCOPY VARCHAR2)
944 IS
945
946 CURSOR memb_type_cur( p_ptr_id NUMBER) IS
947 SELECT attr_value
948 FROM pv_enty_attr_values
949 WHERE entity='PARTNER'
950 AND entity_id=p_ptr_id
951 AND attribute_id=6
952 AND latest_flag='Y';
953
954 l_merge_reason_code VARCHAR2(30);
955 l_current_memb_type VARCHAR2(30);
956 l_api_name VARCHAR2(30) := 'MERGE_PG_MEMBERSHIPS';
957 l_count NUMBER(10) := 0;
958 l_ppf_id NUMBER := Null;
959 l_msg_count NUMBER(10);
960 l_msg_data VARCHAR2(2000);
961
962 RESOURCE_BUSY EXCEPTION;
963 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
964
965 BEGIN
966 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
967 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
968
969 x_return_status := FND_API.G_RET_STS_SUCCESS;
970
971
972 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
973 -- needs to be done. Set Merged To Id is same as Merged From Id and return
974
975 IF p_from_fk_id = p_to_fk_id THEN
976 p_to_id := p_from_id;
977 return;
978 END IF;
979
980
981 IF p_from_fk_id <> p_to_fk_id THEN
982
983 BEGIN
984 SELECT DISTINCT partner_id INTO l_ppf_id
985 FROM PV_PG_MEMBERSHIPS
986 WHERE partner_id = p_to_fk_id;
987 EXCEPTION
988 WHEN NO_DATA_FOUND THEN
989 l_ppf_id := Null;
990 END;
991
992
993 /*
994 update PV_PG_MEMBERSHIPS
995 set partner_id = p_to_fk_id,
996 last_update_date = SYSDATE,
997 last_updated_by = G_USER_ID,
998 last_update_login = G_LOGIN_ID
999 where partner_id = p_from_fk_id;
1000 */
1001
1002 /*
1003
1004 PV_PG_MEMBERSHIPS_PVT.Terminate_ptr_memberships does the following
1005 1. Terminate all Active/future Program Memberships of partner and cancel incomplete and awaiting approvals enrollment requests
1006 2. also if partner is global,t will terminate subsidiary memberships and cancel the subsidiary incompelete and awaiting approvals enrollment requests
1007 */
1008
1009 OPEN memb_type_cur( p_from_fk_id );
1010 FETCH memb_type_cur INTO l_current_memb_type;
1011 CLOSE memb_type_cur;
1012
1013 PV_PG_MEMBERSHIPS_PVT.Terminate_ptr_memberships
1014 (
1015 p_api_version_number => 1.0
1016 ,p_init_msg_list => FND_API.G_FALSE
1017 ,p_commit => FND_API.G_FALSE
1018 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1019 ,p_partner_id => p_from_fk_id
1020 ,p_memb_type => l_current_memb_type
1021 ,p_status_reason_code => 'PARTY_MERGE' -- seed PARTY_MERGE it validates against PV_MEMB_STATUS_REASON_CODE
1022 ,p_comments => null
1023 ,x_return_status => x_return_status
1024 ,x_msg_count => l_msg_count
1025 ,x_msg_data => l_msg_data
1026 );
1027
1028 Debug(G_PKG_NAME || '.' || l_api_name || 'after Terminate_ptr_memberships API call : ' ||
1029 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
1030
1031 /*
1032 Pv_ptr_member_type_pvt.Register_term_ptr_memb_type does the following
1033 1. If Partner is Global, end date the global subsidiary relationship with all its subsidiaries
1034 2. If Partner is Subsdiary, end date the subsidiary-gloabl relationship with all its global
1035 */
1036
1037 IF l_current_memb_type IN ( 'GLOBAL', 'SUBSIDIARY' ) THEN
1038 Pv_ptr_member_type_pvt.Register_term_ptr_memb_type
1039 (
1040 p_api_version_number => 1.0
1041 ,p_init_msg_list => FND_API.G_FALSE
1042 ,p_commit => FND_API.G_FALSE
1043 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1044 ,p_partner_id => p_from_fk_id
1045 ,p_current_memb_type => l_current_memb_type
1046 ,p_new_memb_type => null
1047 ,p_global_ptr_id => null
1048 ,x_return_status => x_return_status
1049 ,x_msg_count => l_msg_count
1050 ,x_msg_data => l_msg_data
1051 );
1052 END IF;
1053
1054 IF l_ppf_id is not Null THEN
1055 p_to_id := l_ppf_id;
1056 END IF;
1057
1058 END IF;
1059
1060 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
1061 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1066 fnd_message.set_token('ERROR',SQLERRM);
1067 fnd_msg_pub.add;
1068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1069 RAISE;
1070
1071 END MERGE_PG_MEMBERSHIPS;
1072
1073
1074
1075
1076 -- *******************************************************
1077 -- Start of Comments
1078 -- *******************************************************
1079 -- API Name: MERGE_PARTNER_ACCESSES
1080 -- Purpose : Merges partner_id in PV_PARTNER_ACCESSES table
1081 -- Type : Private
1082 -- Pre-Req : None.
1083 -- Parameters:
1084 -- IN - All IN parameters are REQUIRED.
1085 -- p_entity_name VARCHAR2 - Name of the entity that is being merged
1086 -- p_from_id NUMBER - Id of the record that is being merged
1087 -- p_from_fk_id NUMBER - Id of the Old Parent
1088 -- p_to_fk_id NUMBER - Id of the New Parent
1089 -- p_parent_entity_name VARCHAR2 - Parent entity name
1090 -- p_batch_id NUMBER - Id of the Batch
1091 -- p_batch_party_id NUMBER - Id of the batch and party record
1092 -- OUT:
1093 -- x_to_id NUMBER - Id of the record under the new parent
1094 -- that its merged to
1095 -- x_return_status VARCHAR2 - Return the status of the procedure
1096 --
1097 -- Version : Current version 1.0
1098 --
1099 -- End of Comments
1100 --
1101
1102 PROCEDURE MERGE_PARTNER_ACCESSES
1103 ( p_entity_name IN VARCHAR2
1104 ,p_from_id IN NUMBER
1105 ,p_to_id IN OUT NOCOPY NUMBER
1106 ,p_from_fk_id IN NUMBER
1107 ,p_to_fk_id IN NUMBER
1108 ,p_parent_entity_name IN VARCHAR2
1109 ,p_batch_id IN NUMBER
1110 ,p_batch_party_id IN NUMBER
1111 ,x_return_status IN OUT NOCOPY VARCHAR2
1112 )
1113 IS
1114
1115 CURSOR c_get_pm_access_id (c_from_fk_id NUMBER, c_to_fk_id NUMBER) IS
1116 select partner_access_id from pv_partner_accesses a
1117 where partner_id= c_from_fk_id
1118 and exists (select null from pv_partner_accesses b
1119 where partner_id = c_to_fk_id
1120 and b.resource_id = a.resource_id );
1121
1122 -- Cursor l_chng_partner_exist_csr.
1123 CURSOR l_chng_partner_exist_csr(cv_partner_id NUMBER) IS
1124 SELECT processed_flag, object_version_number
1125 FROM pv_tap_batch_chg_partners
1126 WHERE partner_id = cv_partner_id;
1127
1128 l_merge_reason_code VARCHAR2(30);
1129 l_api_name VARCHAR2(30) := 'MERGE_PARTNER_ACCESSES';
1130 l_count NUMBER(10) := 0;
1131 l_ppf_id NUMBER := Null;
1132 l_processed_flag VARCHAR2(1);
1133 l_return_status VARCHAR2(1);
1134 l_msg_count NUMBER;
1135 l_msg_data VARCHAR(2000);
1136 l_object_version NUMBER;
1137 l_partner_id NUMBER;
1138
1139 l_batch_chg_prtnrs_rec PV_BATCH_CHG_PRTNR_PVT.Batch_Chg_Prtnrs_Rec_Type:= PV_BATCH_CHG_PRTNR_PVT.g_miss_Batch_Chg_Prtnrs_rec;
1140
1141
1142 RESOURCE_BUSY EXCEPTION;
1143 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1144
1145 BEGIN
1146 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_TAP_MERGE_PKG.ACCESS_MERGE start : '
1147 ||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Entity: '||p_parent_entity_name);
1149 FND_FILE.PUT_LINE(FND_FILE.LOG,'from_fk: '||p_from_fk_id);
1150 FND_FILE.PUT_LINE(FND_FILE.LOG,'to_fk: '||p_to_fk_id);
1151
1152 x_return_status := FND_API.G_RET_STS_SUCCESS;
1153
1154 select merge_reason_code into l_merge_reason_code
1155 from HZ_MERGE_BATCH
1156 where batch_id = p_batch_id;
1157
1158 IF l_merge_reason_code = 'DUPLICATE' THEN
1159 -- *********************************************************************
1160 -- if reason code is duplicate then allow the party merge to happen
1161 -- without any validations.
1162 -- *********************************************************************
1163 null;
1164 ELSE
1165 -- *********************************************************************
1166 -- if there are any validations to be done, include it in this section
1167 -- *********************************************************************
1168 null;
1169 END IF;
1170
1171 -- ************************************************************************
1172 -- If the parent has NOT changed (ie. Parent getting transferred) then
1173 -- nothing needs to be done. Set Merged To Id is same as Merged From Id
1174 -- and return
1175 -- ************************************************************************
1176 if p_from_fk_id = p_to_fk_id then
1177 p_to_id := p_from_id;
1178 return;
1179 end if;
1180
1181 -- ************************************************************************
1182 -- If the parent has changed(ie. Parent is getting merged) then transfer
1183 -- the dependent record to the new parent. Before transferring check if a
1184 -- similar dependent record exists on the new parent. If a duplicate exists
1185 -- then do not transfer and return the id of the duplicate record as the
1186 -- Merged To Id
1187 -- ************************************************************************
1188 IF p_from_fk_id <> p_to_fk_id THEN
1189 BEGIN
1190 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing PV_PARTNER_ACCESSES Table');
1191 IF p_parent_entity_name = 'HZ_PARTIES' THEN
1192 FOR I in c_get_pm_access_id (p_from_fk_id, p_to_fk_id) LOOP
1193 FND_FILE.PUT_LINE(FND_FILE.LOG,
1194 'Deleting PARTY partner_access_id: '||I.partner_access_id);
1195 DELETE FROM pv_tap_access_terrs
1196 WHERE partner_access_id = I.partner_access_id;
1197
1198 DELETE FROM pv_partner_accesses
1199 WHERE partner_access_id = I.partner_access_id;
1200 END LOOP;
1201
1202 -- merge party
1203 UPDATE PV_PARTNER_ACCESSES
1204 set object_version_number = nvl(object_version_number,0) + 1,
1205 partner_id = p_to_fk_id,
1206 last_update_date = SYSDATE,
1207 last_updated_by = G_USER_ID,
1208 last_update_login = G_LOGIN_ID,
1209 program_application_id=hz_utility_pub.program_application_id,
1210 program_id = hz_utility_pub.program_id,
1211 program_update_date = SYSDATE
1212 where partner_id = p_from_fk_id;
1213
1214 OPEN l_chng_partner_exist_csr(p_to_fk_id);
1215 FETCH l_chng_partner_exist_csr INTO l_processed_flag, l_object_version;
1216 l_batch_chg_prtnrs_rec.partner_id := p_to_fk_id;
1217 l_batch_chg_prtnrs_rec.processed_flag := 'P';
1218 IF l_chng_partner_exist_csr%NOTFOUND THEN
1219
1220 CLOSE l_chng_partner_exist_csr;
1221
1222 -- Call Channel_Team_Organization_Update to re-assign the Channel team
1223 PV_BATCH_CHG_PRTNR_PVT.Create_Batch_Chg_Partners(
1224 p_api_version_number => 1.0 ,
1225 p_init_msg_list => FND_API.G_FALSE,
1226 p_commit => FND_API.G_FALSE,
1227 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1228 x_return_status => l_return_status,
1229 x_msg_count => l_msg_count,
1230 x_msg_data => l_msg_data,
1231 p_batch_chg_prtnrs_rec => l_batch_chg_prtnrs_rec,
1232 x_partner_id => l_partner_id );
1233
1234 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1235 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1236 RAISE FND_API.G_EXC_ERROR;
1237 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1239 END IF;
1240 END IF;
1241 ELSE
1242 CLOSE l_chng_partner_exist_csr;
1243 IF (l_processed_flag <> 'P') THEN
1244 l_batch_chg_prtnrs_rec.object_version_number := l_object_version;
1245 PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
1246 p_api_version_number => 1.0
1247 ,p_init_msg_list => FND_API.G_FALSE
1248 ,p_commit => FND_API.G_FALSE
1249 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1250 ,x_return_status => l_return_status
1251 ,x_msg_count => l_msg_count
1252 ,x_msg_data => l_msg_data
1253 ,p_batch_chg_prtnrs_rec => l_batch_chg_prtnrs_rec);
1254
1255 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1256 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1257 RAISE FND_API.G_EXC_ERROR;
1258 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1259 FND_MESSAGE.SET_NAME('PV', 'PV_API_FAILED');
1260 FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners');
1261 FND_MSG_PUB.Add;
1262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1263 END IF;
1264 END IF;
1265 END IF; --l_processed_flag <> 'P'
1266 END IF; -- l_chng_partner_exist_csr%NOTFOUND
1267
1268 END IF; -- p_parent_entity_name = 'HZ_PARTIES'
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1272 fnd_message.set_token('ERROR',SQLERRM);
1273 fnd_msg_pub.add;
1274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1275 raise;
1276 END;
1277 END IF; -- p_from_fk_id <> p_to_fk_id
1278
1279 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PVX_PARTY_MERGE_PKG.MERGE_PARTNER_ACCESSES end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1280
1281 end MERGE_PARTNER_ACCESSES;
1282
1283
1284 PROCEDURE MERGE_PV_GE_PTNR_RESPS (
1285 p_entity_name IN VARCHAR2,
1286 p_from_id IN NUMBER,
1287 p_to_id OUT NOCOPY NUMBER,
1288 p_from_fk_id IN NUMBER,
1289 p_to_fk_id IN NUMBER,
1290 p_parent_entity_name IN VARCHAR2,
1291 p_batch_id IN NUMBER,
1292 p_batch_party_id IN NUMBER,
1293 x_return_status OUT NOCOPY VARCHAR2)
1294 IS
1295 l_merge_reason_code VARCHAR2(30);
1296 l_api_name VARCHAR2(30) := 'MERGE_PV_GE_PTNR_RESPS';
1297 l_msg_count NUMBER;
1298 l_msg_data VARCHAR(2000);
1299
1300 RESOURCE_BUSY EXCEPTION;
1301 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1302
1303 BEGIN
1304 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
1305 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
1306
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308
1309 if p_from_fk_id = p_to_fk_id then
1310 p_to_id := p_from_id;
1311 return;
1312 end if;
1313
1314 Pv_User_Resp_Pvt.manage_merged_party_memb_resp(
1315 p_api_version_number => 1.0
1316 ,p_init_msg_list => FND_API.G_FALSE
1317 ,p_commit => FND_API.G_FALSE
1318 ,x_return_status => x_return_status
1319 ,x_msg_count => l_msg_count
1320 ,x_msg_data => l_msg_data
1321 ,p_from_partner_id => p_from_fk_id
1322 ,p_to_partner_id => p_to_fk_id
1323 );
1324 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1325 RAISE FND_API.G_EXC_ERROR;
1326 END IF;
1327
1328 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
1329 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
1330
1331 -- --------------------------------------------------------
1332 -- Exception Handling
1333 -- --------------------------------------------------------
1334 exception
1335 WHEN FND_API.G_EXC_ERROR THEN
1336 x_return_status := FND_API.G_RET_STS_ERROR;
1337 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1338 p_count => l_msg_count,
1339 p_data => l_msg_data);
1340
1341 when others then
1342 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1343 fnd_message.set_token('ERROR',SQLERRM);
1344 fnd_msg_pub.add;
1345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1346 raise;
1347 END MERGE_PV_GE_PTNR_RESPS;
1348
1349 PROCEDURE MERGE_CONTRACT_BINDING_CONTACT (
1350 p_entity_name IN VARCHAR2,
1351 p_from_id IN NUMBER,
1352 p_to_id OUT NOCOPY NUMBER,
1353 p_from_fk_id IN NUMBER,
1354 p_to_fk_id IN NUMBER,
1355 p_parent_entity_name IN VARCHAR2,
1356 p_batch_id IN NUMBER,
1357 p_batch_party_id IN NUMBER,
1358 x_return_status OUT NOCOPY VARCHAR2
1359 )
1360 IS
1361 cursor c1 is
1362 select 1
1363 from PV_PG_ENRL_REQUESTS
1364 where partner_id = p_from_fk_id
1365 for update nowait;
1366
1367
1368 l_merge_reason_code VARCHAR2(30);
1369 l_api_name VARCHAR2(30) := 'MERGE_CONTRACT_BINDING_CONTACT';
1370 l_count NUMBER(10) := 0;
1371 l_ppf_id NUMBER := Null;
1372
1373 RESOURCE_BUSY EXCEPTION;
1374 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1375
1376 BEGIN
1377 Debug(G_PKG_NAME || '.' || l_api_name || ' starts: ' ||
1378 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
1379
1380 x_return_status := FND_API.G_RET_STS_SUCCESS;
1381
1382 select merge_reason_code
1383 into l_merge_reason_code
1384 from hz_merge_batch
1385 where batch_id = p_batch_id;
1386
1387 if l_merge_reason_code = 'DUPLICATE' then
1388 -- if reason code is duplicate then allow the party merge to happen without
1389 -- any validations.
1390 null;
1391 else
1392 -- if there are any validations to be done, include it in this section
1393 null;
1394 end if;
1395
1396 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1397 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1398
1399 if p_from_fk_id = p_to_fk_id then
1400 p_to_id := p_from_id;
1401 return;
1402 end if;
1403
1404 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1405 -- dependent record to the new parent. Before transferring check if a similar
1406 -- dependent record exists on the new parent. If a duplicate exists then do
1407 -- not transfer and return the id of the duplicate record as the Merged To Id
1408
1409
1410 if p_from_fk_id <> p_to_fk_id then
1411
1412
1413 BEGIN
1414 SELECT DISTINCT partner_id INTO l_ppf_id
1415 FROM PV_PG_ENRL_REQUESTS
1416 WHERE partner_id = p_to_fk_id;
1417 EXCEPTION
1418 WHEN NO_DATA_FOUND THEN
1419 l_ppf_id := Null;
1420 END;
1421
1422
1423
1424 update PV_PG_ENRL_REQUESTS
1425 set contract_binding_contact_id = p_to_fk_id,
1426 last_update_date = SYSDATE,
1427 last_updated_by = G_USER_ID,
1428 last_update_login = G_LOGIN_ID
1429 where contract_binding_contact_id = p_from_fk_id;
1430
1431 if l_ppf_id is not Null then
1432 p_to_id := l_ppf_id;
1433 end if;
1434
1435 end if;
1436
1437 Debug(G_PKG_NAME || '.' || l_api_name || ' ends: ' ||
1438 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
1439
1440 exception
1441 when others then
1442 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1443 fnd_message.set_token('ERROR',SQLERRM);
1444 fnd_msg_pub.add;
1445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1446 raise;
1447
1448 END MERGE_CONTRACT_BINDING_CONTACT;
1449
1450 --=============================================================================+
1451 --| Private Procedure |
1452 --| |
1453 --| Debug |
1454 --| |
1455 --| Parameters |
1456 --| IN |
1457 --| OUT |
1458 --| |
1459 --| |
1460 --| NOTES: |
1461 --| |
1462 --| HISTORY |
1463 --| |
1464 --==============================================================================
1465 PROCEDURE Debug(
1466 p_msg_string IN VARCHAR2
1467 )
1468 IS
1469 l_count NUMBER;
1470 l_msg VARCHAR2(2000);
1471
1472 BEGIN
1473 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1474 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
1475 FND_MSG_PUB.Add;
1476
1477 l_count := FND_MSG_PUB.count_msg;
1478
1479 FOR l_cnt IN 1 .. l_count LOOP
1480 l_msg := FND_MSG_PUB.get(l_cnt, FND_API.g_false);
1481 FND_FILE.PUT_LINE(FND_FILE.LOG, '(' || l_cnt || ') ' || l_msg);
1482 END LOOP;
1483 END Debug;
1484
1485
1486 --=============================================================================+
1487 --| Private Procedure |
1488 --| |
1489 --| Set_Message |
1490 --| |
1491 --| Parameters |
1492 --| IN |
1493 --| OUT |
1494 --| |
1495 --| |
1496 --| NOTES: |
1497 --| |
1498 --| HISTORY |
1499 --| |
1500 --==============================================================================
1501 PROCEDURE Set_Message(
1502 p_msg_level IN NUMBER,
1503 p_msg_name IN VARCHAR2,
1504 p_token1 IN VARCHAR2,
1505 p_token1_value IN VARCHAR2,
1506 p_token2 IN VARCHAR2 := NULL ,
1507 p_token2_value IN VARCHAR2 := NULL,
1508 p_token3 IN VARCHAR2 := NULL,
1509 p_token3_value IN VARCHAR2 := NULL
1510 )
1511 IS
1512 l_count NUMBER;
1513 l_msg VARCHAR2(2000);
1514
1515 BEGIN
1516 FND_MESSAGE.Set_Name('PV', p_msg_name);
1517 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1518
1519 IF (p_token2 IS NOT NULL) THEN
1520 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
1521 END IF;
1522
1523 IF (p_token3 IS NOT NULL) THEN
1524 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
1525 END IF;
1526
1527 FND_MSG_PUB.Add;
1528
1529 l_count := FND_MSG_PUB.count_msg;
1530
1531 FOR l_cnt IN 1 .. l_count LOOP
1532 l_msg := FND_MSG_PUB.get(l_cnt, FND_API.g_false);
1533 FND_FILE.PUT_LINE(FND_FILE.LOG, '(' || l_cnt || ') ' || l_msg);
1534 END LOOP;
1535 END Set_Message;
1536 -- ==============================End of Set_Message==============================
1537
1538 END PVX_PARTY_MERGE_PKG ;
[Home] [Help]