[Home] [Help]
PACKAGE BODY: APPS.IGW_PARTY_MERGE_PUB
Source
1 PACKAGE BODY IGW_PARTY_MERGE_PUB as
2 --$Header: igwtcapb.pls 115.5 2002/11/14 18:48:43 vmedikon noship $
3
4 PROCEDURE person_degrees_party_merge(
5 p_entity_name IN VARCHAR2,
6 p_from_id IN NUMBER,
7 x_to_id OUT NOCOPY NUMBER,
8 p_from_fk_id IN NUMBER,
9 p_to_fk_id IN NUMBER,
10 p_parent_entity_name IN VARCHAR2,
11 p_batch_id IN NUMBER,
12 p_batch_party_id IN NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2)
14 IS
15
16 cursor from_party_cur(l_person_degree_id number) is
17 select *
18 from igw_person_degrees
19 where person_degree_id = l_person_degree_id;
20
21 from_party_rec from_party_cur%rowtype;
22
23 cursor to_party_cur(l_party_id number,l_degree_type_code varchar2, l_degree varchar2, l_graduation_date date) is
24 select *
25 from igw_person_degrees
26 where party_id = l_party_id
27 and degree_type_code = l_degree_type_code and
28 degree = l_degree and graduation_date = l_graduation_date;
29
30 to_party_rec to_party_cur%rowtype;
31
32 l_no number;
33 l_api_name varchar2(30) := 'PERSON_DEGREES_PARTY_MERGE';
34
35 BEGIN
36 savepoint party_merge_sp;
37
38 x_return_status := fnd_api.g_ret_sts_success;
39
40 if (p_entity_name <> 'IGW_PERSON_DEGREES')
41 or (p_parent_entity_name <> 'HZ_PARTIES') then
42 fnd_message.set_name ('IGW', 'IGW_MRG_ENTITY_NAME_ERR');
43 fnd_message.set_token('P_ENTITY',p_entity_name);
44 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
45 FND_MSG_PUB.add;
46 RAISE fnd_api.g_exc_error;
47 end if;
48
49 open from_party_cur(p_from_id); -- p_from_id is the value of person_degree_id
50 fetch from_party_cur into from_party_rec;
51 IF(from_party_cur%found) THEN
52 open to_party_cur(p_to_fk_id,from_party_rec.degree_type_code,from_party_rec.degree,from_party_rec.graduation_date);
53 fetch to_party_cur into to_party_rec;
54 if(to_party_cur%found) then
55
56 --reject merge because the person is already used
57 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
58 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name);
59 FND_MSG_PUB.add;
60 x_return_status := fnd_api.g_ret_sts_error;
61 RAISE fnd_api.g_exc_error;
62
63 else
64 --update from record with new party id
65 update igw_person_degrees
66 set party_id = p_to_fk_id
67 where person_degree_id = p_from_id;
68
69 x_to_id := p_from_id;
70 end if;
71 close to_party_cur;
72 END IF; -- end of car_cur found check
73 close from_party_cur;
74
75 EXCEPTION
76 WHEN fnd_api.g_exc_unexpected_error
77 THEN
78 x_return_status := fnd_api.g_ret_sts_unexp_error;
79 ROLLBACK TO PARTY_MERGE_SP;
80 WHEN fnd_api.g_exc_error
81 THEN
82 ROLLBACK TO PARTY_MERGE_SP;
83 x_return_status := fnd_api.g_ret_sts_error;
84 WHEN OTHERS
85 THEN
86 ROLLBACK TO PARTY_MERGE_SP;
87 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
88 p_procedure_name => l_api_name,
89 p_error_text => SUBSTRB(SQLERRM,1,240));
90 x_return_status := fnd_api.g_ret_sts_unexp_error;
91
92
93 END person_degrees_party_merge;
94 ---------------------------------------------------------------
95
96 PROCEDURE person_biosketch_party_merge(
97 p_entity_name IN VARCHAR2,
98 p_from_id IN NUMBER,
99 x_to_id OUT NOCOPY NUMBER,
100 p_from_fk_id IN NUMBER,
101 p_to_fk_id IN NUMBER,
102 p_parent_entity_name IN VARCHAR2,
103 p_batch_id IN NUMBER,
104 p_batch_party_id IN NUMBER,
105 x_return_status OUT NOCOPY VARCHAR2)
106 IS
107
108 cursor from_party_cur(l_person_biosketch_id number) is
109 select *
110 from igw_person_biosketch
111 where person_biosketch_id = l_person_biosketch_id;
112
113 from_party_rec from_party_cur%rowtype;
114
115 cursor to_party_cur(l_party_id number,l_biosketch_type varchar2,l_line_description varchar2) is
116 select *
117 from igw_person_biosketch
118 where party_id = l_party_id and
119 biosketch_type = l_biosketch_type and line_description = l_line_description;
120
121 to_party_rec to_party_cur%rowtype;
122
123 l_no number;
124 l_api_name varchar2(30) := 'PERSON_BIOSKETCH_PARTY_MERGE';
125
126 BEGIN
127 savepoint party_merge_sp;
128
129 x_return_status := fnd_api.g_ret_sts_success;
130
131 if (p_entity_name <> 'IGW_PERSON_BIOSKETCH')
132 or (p_parent_entity_name <> 'HZ_PARTIES') then
133 fnd_message.set_name ('IGW', 'IGW_MRG_ENTITY_NAME_ERR');
134 fnd_message.set_token('P_ENTITY',p_entity_name);
135 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
136 FND_MSG_PUB.add;
137 RAISE fnd_api.g_exc_error;
138 end if;
139
140 open from_party_cur(p_from_id);
141 fetch from_party_cur into from_party_rec;
142 IF(from_party_cur%found) THEN
143 open to_party_cur(p_to_fk_id,from_party_rec.biosketch_type ,from_party_rec.line_description );
144 fetch to_party_cur into to_party_rec;
145 if(to_party_cur%found) then
146
147 -- reject merge because the person is already used
148 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
149 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name);
150 FND_MSG_PUB.add;
151 x_return_status := fnd_api.g_ret_sts_error;
152 RAISE fnd_api.g_exc_error;
153
154 else
155 --update from record with new party id
156 update igw_person_biosketch
157 set party_id = p_to_fk_id
158 where person_biosketch_id = p_from_id;
159
160 x_to_id := p_from_id;
161 end if;
162 close to_party_cur;
163 END IF; -- end of car_cur found check
164 close from_party_cur;
165
166 EXCEPTION
167 WHEN fnd_api.g_exc_unexpected_error
168 THEN
169 x_return_status := fnd_api.g_ret_sts_unexp_error;
170 ROLLBACK TO PARTY_MERGE_SP;
171 WHEN fnd_api.g_exc_error
172 THEN
173 ROLLBACK TO PARTY_MERGE_SP;
174 x_return_status := fnd_api.g_ret_sts_error;
175 WHEN OTHERS
176 THEN
177 ROLLBACK TO PARTY_MERGE_SP;
178 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
179 p_procedure_name => l_api_name,
180 p_error_text => SUBSTRB(SQLERRM,1,240));
181 x_return_status := fnd_api.g_ret_sts_unexp_error;
182 END person_biosketch_party_merge;
183
184 ----------------------------------------------------------
185
186 PROCEDURE prop_location_party_merge(
187 p_entity_name IN VARCHAR2,
188 p_from_id IN NUMBER,
189 x_to_id OUT NOCOPY NUMBER,
190 p_from_fk_id IN NUMBER,
191 p_to_fk_id IN NUMBER,
192 p_parent_entity_name IN VARCHAR2,
193 p_batch_id IN NUMBER,
194 p_batch_party_id IN NUMBER,
195 x_return_status OUT NOCOPY VARCHAR2)
196 IS
197
198 cursor from_party_cur(l_prop_locations_id number) is
199 select *
200 from igw_prop_locations
201 where prop_location_id = l_prop_locations_id;
202
203 from_party_rec from_party_cur%rowtype;
204
205 cursor to_party_cur(l_party_id number,l_proposal_id number) is
206 select *
207 from igw_prop_locations
208 where proposal_id = l_proposal_id and
209 party_id = l_party_id;
210
211 to_party_rec to_party_cur%rowtype;
212
213 l_no number;
214 l_api_name varchar2(30) := 'PROP_LOCATION_PARTY_MERGE';
215
216 BEGIN
217 savepoint party_merge_sp;
218
219 x_return_status := fnd_api.g_ret_sts_success;
220
221 if (p_entity_name <> 'IGW_PROP_LOCATIONS')
222 or (p_parent_entity_name <> 'HZ_PARTIES') then
223 fnd_message.set_name ('IGW', 'IGW_MRG_ENTITY_NAME_ERR');
224 fnd_message.set_token('P_ENTITY',p_entity_name);
225 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
226 FND_MSG_PUB.add;
227 RAISE fnd_api.g_exc_error;
228 end if;
229
230 open from_party_cur(p_from_id);
231 fetch from_party_cur into from_party_rec;
232 IF(from_party_cur%found) THEN
233 open to_party_cur(p_to_fk_id,from_party_rec.proposal_id);
234 fetch to_party_cur into to_party_rec;
235 if(to_party_cur%found) then
236
237 -- reject merge because the person is already used
238 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
239 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name);
240 FND_MSG_PUB.add;
241 x_return_status := fnd_api.g_ret_sts_error;
242 RAISE fnd_api.g_exc_error;
243
244 else
245 --update from record with new party id
246 update igw_prop_locations
247 set party_id = p_to_fk_id
248 where prop_location_id = p_from_id;
249
250 x_to_id := p_from_id;
251 end if;
252 close to_party_cur;
253 END IF; -- end of car_cur found check
254 close from_party_cur;
255
256 EXCEPTION
257 WHEN fnd_api.g_exc_unexpected_error
258 THEN
259 x_return_status := fnd_api.g_ret_sts_unexp_error;
260 ROLLBACK TO PARTY_MERGE_SP;
261 WHEN fnd_api.g_exc_error
262 THEN
263 ROLLBACK TO PARTY_MERGE_SP;
264 x_return_status := fnd_api.g_ret_sts_error;
265 WHEN OTHERS
266 THEN
267 ROLLBACK TO PARTY_MERGE_SP;
268 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
269 p_procedure_name => l_api_name,
270 p_error_text => SUBSTRB(SQLERRM,1,240));
271 x_return_status := fnd_api.g_ret_sts_unexp_error;
272 END prop_location_party_merge;
273
274 --------------------------------------------------------------------
275
276 PROCEDURE prop_person_party_merge(
277 p_entity_name IN VARCHAR2,
278 p_from_id IN NUMBER,
279 x_to_id OUT NOCOPY NUMBER,
280 p_from_fk_id IN NUMBER,
281 p_to_fk_id IN NUMBER,
282 p_parent_entity_name IN VARCHAR2,
283 p_batch_id IN NUMBER,
284 p_batch_party_id IN NUMBER,
285 x_return_status OUT NOCOPY VARCHAR2)
286 IS
287
288 cursor from_party_cur(l_proposal_person_party_id number) is
289 select *
290 from igw_prop_persons_tca_v
291 where proposal_person_party_id = l_proposal_person_party_id ;
292
293 from_party_rec from_party_cur%rowtype;
294
295 --this is for person party
296 cursor to_party_cur(l_party_id number,l_proposal_id number) is
297 select *
298 from igw_prop_persons_tca_v
299 where proposal_id = l_proposal_id
300 and person_party_id = l_party_id;
301
302 to_party_rec to_party_cur%rowtype;
303
304
305 l_no number;
306 l_api_name varchar2(30) := 'PROP_PERSON_PARTY_MERGE';
307
308 BEGIN
309 savepoint party_merge_sp;
310 x_return_status := fnd_api.g_ret_sts_success;
311
312 if (p_entity_name <> 'IGW_PROP_PERSONS_TCA_V')
313 or (p_parent_entity_name <> 'HZ_PARTIES') then
314 fnd_message.set_name ('IGW', 'IGW_MRG_ENTITY_NAME_ERR');
315 fnd_message.set_token('P_ENTITY',p_entity_name);
316 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
317 FND_MSG_PUB.add;
318 RAISE fnd_api.g_exc_error;
319 end if;
320
321 open from_party_cur(p_from_id);
322 fetch from_party_cur into from_party_rec;
323 IF(from_party_cur%found) THEN
324 open to_party_cur(p_to_fk_id,from_party_rec.proposal_id);
325 fetch to_party_cur into to_party_rec;
326 if(to_party_cur%found) then
327
328 -- reject merge because the person is already used
329 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
330 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name);
331 FND_MSG_PUB.add;
332 x_return_status := fnd_api.g_ret_sts_error;
333 RAISE fnd_api.g_exc_error;
334
335 else
336 --update from record with new party id
337 update igw_prop_persons
338 set person_party_id = p_to_fk_id
339 where proposal_id||person_party_id = p_from_id;
340
341 begin
342 update igw_prop_person_questions
343 set party_id = p_to_fk_id
344 where proposal_id = from_party_rec.proposal_id
345 and party_id = from_party_rec.person_party_id;
346 exception
347 when dup_val_on_index then
348 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
349 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name||' - ASSURANCES');
350 FND_MSG_PUB.add;
351 x_return_status := fnd_api.g_ret_sts_error;
352 RAISE fnd_api.g_exc_error;
353 end;
354
355 begin
356 update igw_prop_person_support
357 set party_id = p_to_fk_id
358 where proposal_id = from_party_rec.proposal_id
359 and party_id = from_party_rec.person_party_id;
360 exception
361 when dup_val_on_index then
362 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
363 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name||' - OTHER SUPPORT');
364 FND_MSG_PUB.add;
365 x_return_status := fnd_api.g_ret_sts_error;
366 RAISE fnd_api.g_exc_error;
367 end;
368
369 begin
370 update igw_budget_persons
371 set party_id = p_to_fk_id
372 where proposal_id = from_party_rec.proposal_id
373 and party_id = from_party_rec.person_party_id;
374 exception
375 when dup_val_on_index then
376 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
377 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name||' - BUDGET PERSONS');
378 FND_MSG_PUB.add;
379 x_return_status := fnd_api.g_ret_sts_error;
380 RAISE fnd_api.g_exc_error;
381 end;
382
383 begin
384 update igw_budget_personnel_details
385 set party_id = p_to_fk_id
386 where proposal_id = from_party_rec.proposal_id
387 and party_id = from_party_rec.person_party_id;
388 exception
389 when dup_val_on_index then
390 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
391 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name||' - BUDGET PERSONNEL DETAILS');
392 FND_MSG_PUB.add;
393 x_return_status := fnd_api.g_ret_sts_error;
394 RAISE fnd_api.g_exc_error;
395 end;
396
397 x_to_id := p_from_id;
398 end if;
399 close to_party_cur;
400 END IF; -- end of car_cur found check
401 EXCEPTION
402 WHEN fnd_api.g_exc_unexpected_error
403 THEN
404 x_return_status := fnd_api.g_ret_sts_unexp_error;
405 ROLLBACK TO PARTY_MERGE_SP;
406 WHEN fnd_api.g_exc_error
407 THEN
408 ROLLBACK TO PARTY_MERGE_SP;
409 x_return_status := fnd_api.g_ret_sts_error;
410 WHEN OTHERS
411 THEN
412 ROLLBACK TO PARTY_MERGE_SP;
413 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
414 p_procedure_name => l_api_name,
418
415 p_error_text => SUBSTRB(SQLERRM,1,240));
416 x_return_status := fnd_api.g_ret_sts_unexp_error;
417 END prop_person_party_merge;
419 -------------------------------------------------------------------------------------
420 PROCEDURE prop_org_party_merge(
421 p_entity_name IN VARCHAR2,
422 p_from_id IN NUMBER,
423 x_to_id OUT NOCOPY NUMBER,
424 p_from_fk_id IN NUMBER,
425 p_to_fk_id IN NUMBER,
426 p_parent_entity_name IN VARCHAR2,
427 p_batch_id IN NUMBER,
428 p_batch_party_id IN NUMBER,
429 x_return_status OUT NOCOPY VARCHAR2)
430 IS
431
432 cursor from_party_cur(l_proposal_person_party_id number) is
433 select *
434 from igw_prop_persons_tca_v
435 where proposal_person_party_id = l_proposal_person_party_id;
436
437 from_party_rec from_party_cur%rowtype;
438
439
440 --this is for organization party
441 cursor to_party_cur(l_party_id number,l_proposal_id number) is
442 select *
443 from igw_prop_persons_tca_v
444 where org_party_id = l_party_id and
445 proposal_id = l_proposal_id;
446
447 to_party_rec to_party_cur%rowtype;
448
449 l_no number;
450 l_api_name varchar2(30) := 'PROP_ORG_PARTY_MERGE';
451
452 BEGIN
453 savepoint party_merge_sp;
454
455 x_return_status := fnd_api.g_ret_sts_success;
456
457 if (p_entity_name <> 'IGW_PROP_PERSONS_TCA_V')
458 or (p_parent_entity_name <> 'HZ_PARTIES') then
459 fnd_message.set_name ('IGW', 'IGW_MRG_ENTITY_NAME_ERR');
460 fnd_message.set_token('P_ENTITY',p_entity_name);
461 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
462 FND_MSG_PUB.add;
463 RAISE fnd_api.g_exc_error;
464 end if;
465
466 --Repeat the same for org party
467 open from_party_cur(p_from_id);
468 fetch from_party_cur into from_party_rec;
469 IF(from_party_cur%found) THEN
470 /*
471 open to_party_cur(p_to_fk_id);
472 fetch to_party_cur into to_party_rec;
473 if(to_party_cur%found) then
474
475 -- reject merge because the person is already used
476 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
477 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name);
478 FND_MSG_PUB.add;
479 x_return_status := fnd_api.g_ret_sts_error;
480 RAISE fnd_api.g_exc_error;
481
482 else */
483 --update from record with new party id
484 update igw_prop_persons
485 set org_party_id = p_to_fk_id
486 where proposal_id||person_party_id = p_from_id;
487
488 x_to_id := p_from_id;/*
489 end if;
490 close to_party_cur;*/
491 END IF; -- end of car_cur found check
492 close from_party_cur;
493
494 EXCEPTION
495 WHEN fnd_api.g_exc_unexpected_error
496 THEN
497 x_return_status := fnd_api.g_ret_sts_unexp_error;
498 ROLLBACK TO PARTY_MERGE_SP;
499 WHEN fnd_api.g_exc_error
500 THEN
501 ROLLBACK TO PARTY_MERGE_SP;
502 x_return_status := fnd_api.g_ret_sts_error;
503 WHEN OTHERS
504 THEN
505 ROLLBACK TO PARTY_MERGE_SP;
506 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
507 p_procedure_name => l_api_name,
508 p_error_text => SUBSTRB(SQLERRM,1,240));
509 x_return_status := fnd_api.g_ret_sts_unexp_error;
510 END prop_org_party_merge;
511
512
513
514 PROCEDURE other_support_location_merge (
515 p_entity_name IN VARCHAR2,
516 p_from_id IN NUMBER,
517 x_to_id OUT NOCOPY NUMBER,
518 p_from_fk_id IN NUMBER,
519 p_to_fk_id IN NUMBER,
520 p_parent_entity_name IN VARCHAR2,
521 p_batch_id IN NUMBER,
522 p_batch_party_id IN NUMBER,
523 x_return_status OUT NOCOPY VARCHAR2) IS
524
525 cursor from_party_cur(l_prop_person_support_id number) is
526 select *
527 from igw_prop_person_support
528 where prop_person_support_id = l_prop_person_support_id;
529
530 from_party_rec from_party_cur%rowtype;
531
532
533 cursor to_party_cur(l_party_id number,l_proposal_id number) is
534 select *
535 from igw_prop_persons_tca_v
536 where org_party_id = l_party_id and
537 proposal_id = l_proposal_id;
538
539 to_party_rec to_party_cur%rowtype;
540
541 l_no number;
542 l_api_name varchar2(30) := 'OTHER_SUPPORT_LOCATION_MERGE';
543
544 BEGIN
545 savepoint party_merge_sp;
546
547 x_return_status := fnd_api.g_ret_sts_success;
548
549 if (p_entity_name <> 'IGW_PROP_PERSON_SUPPORT')
550 or (p_parent_entity_name <> 'HZ_PARTIES') then
551 fnd_message.set_name ('IGW', 'IGW_MRG_ENTITY_NAME_ERR');
555 RAISE fnd_api.g_exc_error;
552 fnd_message.set_token('P_ENTITY',p_entity_name);
553 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
554 FND_MSG_PUB.add;
556 end if;
557
558 --Repeat the same for org party
559 open from_party_cur(p_from_id);
560 fetch from_party_cur into from_party_rec;
561 IF(from_party_cur%found) THEN
562 /*
563 open to_party_cur(p_to_fk_id);
564 fetch to_party_cur into to_party_rec;
565 if(to_party_cur%found) then
566
567 -- reject merge because the person is already used
568 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
569 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name);
570 FND_MSG_PUB.add;
571 x_return_status := fnd_api.g_ret_sts_error;
572 RAISE fnd_api.g_exc_error;
573
574 else */
575 --update from record with new party id
576 update igw_prop_person_support
577 set location_party_id = p_to_fk_id
578 where prop_person_support_id = p_from_id
579 and location_party_id = p_from_fk_id;
580
581 x_to_id := p_from_id;/*
582 end if;
583 close to_party_cur;*/
584 END IF; -- end of car_cur found check
585 close from_party_cur;
586
587 EXCEPTION
588 WHEN fnd_api.g_exc_unexpected_error
589 THEN
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591 ROLLBACK TO PARTY_MERGE_SP;
592 WHEN fnd_api.g_exc_error
593 THEN
594 ROLLBACK TO PARTY_MERGE_SP;
595 x_return_status := fnd_api.g_ret_sts_error;
596 WHEN OTHERS
597 THEN
598 ROLLBACK TO PARTY_MERGE_SP;
599 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
600 p_procedure_name => l_api_name,
601 p_error_text => SUBSTRB(SQLERRM,1,240));
602 x_return_status := fnd_api.g_ret_sts_unexp_error;
603 END other_support_location_merge;
604
605
606 PROCEDURE other_support_pi_party_merge (
607 p_entity_name IN VARCHAR2,
608 p_from_id IN NUMBER,
609 x_to_id OUT NOCOPY NUMBER,
610 p_from_fk_id IN NUMBER,
611 p_to_fk_id IN NUMBER,
612 p_parent_entity_name IN VARCHAR2,
613 p_batch_id IN NUMBER,
614 p_batch_party_id IN NUMBER,
615 x_return_status OUT NOCOPY VARCHAR2) IS
616
617 cursor from_party_cur(l_prop_person_support_id number) is
618 select *
619 from igw_prop_person_support
620 where prop_person_support_id = l_prop_person_support_id;
621
622 from_party_rec from_party_cur%rowtype;
623
624
625 cursor to_party_cur(l_party_id number,l_proposal_id number) is
626 select *
627 from igw_prop_persons_tca_v
628 where org_party_id = l_party_id and
629 proposal_id = l_proposal_id;
630
631 to_party_rec to_party_cur%rowtype;
632
633 l_no number;
634 l_api_name varchar2(30) := 'OTHER_SUPPORT_PI_PARTY_MERGE';
635
636 BEGIN
637 savepoint party_merge_sp;
638
639 x_return_status := fnd_api.g_ret_sts_success;
640
641 if (p_entity_name <> 'IGW_PROP_PERSON_SUPPORT')
642 or (p_parent_entity_name <> 'HZ_PARTIES') then
643 fnd_message.set_name ('IGW', 'IGW_MRG_ENTITY_NAME_ERR');
644 fnd_message.set_token('P_ENTITY',p_entity_name);
645 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
646 FND_MSG_PUB.add;
647 RAISE fnd_api.g_exc_error;
648 end if;
649
650 --Repeat the same for org party
651 open from_party_cur(p_from_id);
652 fetch from_party_cur into from_party_rec;
653 IF(from_party_cur%found) THEN
654 /*
655 open to_party_cur(p_to_fk_id);
656 fetch to_party_cur into to_party_rec;
657 if(to_party_cur%found) then
658
659 -- reject merge because the person is already used
660 fnd_message.set_name ('IGW', 'IGW_MRG_REJECT_MERGE');
661 fnd_message.set_token('API','IGW_PARTY_MERGE_PUB.'||l_api_name);
662 FND_MSG_PUB.add;
663 x_return_status := fnd_api.g_ret_sts_error;
664 RAISE fnd_api.g_exc_error;
665
666 else */
667 --update from record with new party id
668 update igw_prop_person_support
669 set pi_party_id = p_to_fk_id
670 where prop_person_support_id = p_from_id
671 and pi_party_id = p_from_fk_id;
672
673 x_to_id := p_from_id;/*
674 end if;
675 close to_party_cur;*/
676 END IF; -- end of car_cur found check
677 close from_party_cur;
678
679 EXCEPTION
680 WHEN fnd_api.g_exc_unexpected_error
681 THEN
682 x_return_status := fnd_api.g_ret_sts_unexp_error;
683 ROLLBACK TO PARTY_MERGE_SP;
684 WHEN fnd_api.g_exc_error
685 THEN
686 ROLLBACK TO PARTY_MERGE_SP;
687 x_return_status := fnd_api.g_ret_sts_error;
688 WHEN OTHERS
689 THEN
690 ROLLBACK TO PARTY_MERGE_SP;
691 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
695 END other_support_pi_party_merge;
692 p_procedure_name => l_api_name,
693 p_error_text => SUBSTRB(SQLERRM,1,240));
694 x_return_status := fnd_api.g_ret_sts_unexp_error;
696
697
698
699 END; --package