[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_PARTY_MERGE_PUB
Source
1 PACKAGE BODY JTF_RS_PARTY_MERGE_PUB AS
2 /* $Header: jtfrsbmb.pls 120.0.12000000.2 2007/04/02 23:28:09 nsinghai ship $ */
3
4 /************************************************************
5
6 This is the part and party site merge package for jtf resources
7
8 *****************************************************************************/
9 PROCEDURE synchronize_resource(p_resource_id IN NUMBER,
10 p_category IN VARCHAR2,
11 p_address_id IN NUMBER,
12 p_source_id IN NUMBER,
13 x_ret_status out NOCOPY VARCHAR2);
14
15
16 PROCEDURE resource_party_merge(
17 p_entity_name IN VARCHAR2,
18 p_from_id IN NUMBER,
19 x_to_id OUT NOCOPY NUMBER,
20 p_from_fk_id IN NUMBER,
21 p_to_fk_id IN NUMBER,
22 p_parent_entity_name IN VARCHAR2,
23 p_batch_id IN NUMBER,
24 p_batch_party_id IN NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2)
26 IS
27
28 cursor cat_cur(l_source_id number)
29 is
30 select resource_id
31 ,category
32 ,resource_number
33 ,address_id
34 ,contact_id
35 ,object_version_number
36 , created_by
37 , creation_date
38 , last_updated_by
39 , last_update_date
40 , last_update_login
41 from jtf_rs_resource_extns
42 where source_id = l_source_id
43 and category IN ('PARTY','PARTNER') ;
44
45 cursor to_party_cur(l_party_id number)
46 is
47 select resource_id
48 from jtf_rs_resource_extns
49 where category = 'PARTY'
50 and source_id = l_party_id;
51
52 to_party_rec to_party_cur%rowtype;
53
54 cursor to_partner_cur(l_party_id number)
55 is
56 select resource_id
57 from jtf_rs_resource_extns
58 where category = 'PARTNER'
59 and source_id = l_party_id;
60
61 to_partner_rec to_partner_cur%rowtype;
62
63 cursor partner_cur(l_party_id in number)
64 is
65 SELECT par.party_type,
66 prt.party_site_id address_id
67 FROM hz_parties par,
68 hz_party_sites prt
69 WHERE par.party_id = l_party_id
70 AND par.party_id = prt.party_id(+)
71 AND nvl(prt.identifying_address_flag, 'Y') = 'Y'
72 AND nvl(prt.status, 'A') = 'A';
73
74 partner_rec partner_cur%rowtype;
75
76 cursor party_addr_cur(l_party_id in number)
77 is
78 select prt.party_site_id address_id
79 from hz_party_sites prt
80 where prt.party_id = l_party_id
81 and prt.identifying_address_flag = 'Y'
82 and prt.status = 'A';
83
84 party_addr_rec party_addr_cur%rowtype;
85
86 l_api_name varchar2(30) := 'RESOURCE_PARTY_MERGE';
87 l_date Date;
88 l_user_id Number;
89 l_login_id Number;
90 l_address_id number;
91 -------------------------------------
92 l_error_handle varchar2(30) ;
93 L_OBJECT_VER_NUMBER NUMBER;
94 L_RETURN_STATUS VARCHAR2(2);
95 L_MSG_COUNT NUMBER;
96 L_MSG_DATA VARCHAR2(2000);
97 -------------------------------------
98
99 begin
100 --GET USER ID AND SYSDATE
101 l_date := sysdate;
102 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
103 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
104 -------------------------------------
105 l_error_handle := NVL(FND_PROFILE.Value('JTF_RS_PARTY_MRG_FRMID_TOID_ER'),'ERROR');
106 -------------------------------------
107 savepoint party_merge_sp;
108
109 -----Debug Messages--------------------
110 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Party Merge-----------------');
111 FND_FILE.put_line(fnd_file.log, ' Begin JTF_RS_PARTY_MERGE_PUB.resource_party_merge(+) ');
112 FND_FILE.put_line(fnd_file.log, ' p_entity_name :'||p_entity_name);
113 FND_FILE.put_line(fnd_file.log, ' p_from_id :'||p_from_id);
114 FND_FILE.put_line(fnd_file.log, ' p_from_fk_id :'||p_from_fk_id);
115 FND_FILE.put_line(fnd_file.log, ' p_to_fk_id :'||p_to_fk_id);
116 FND_FILE.put_line(fnd_file.log, ' p_parent_entity_name:'||p_parent_entity_name);
117 FND_FILE.put_line(fnd_file.log, ' p_batch_id :'||p_batch_id);
118 FND_FILE.put_line(fnd_file.log, ' p_batch_party_id :'||p_batch_party_id);
119 FND_FILE.put_line(fnd_file.log, ' Error Handling Mode :'||l_error_handle);
120 -----End Debug Messages----------------
121
122 x_return_status := fnd_api.g_ret_sts_success;
123
124 if (p_entity_name <> 'JTF_RS_RESOURCE_EXTNS')
125 or (p_parent_entity_name <> 'HZ_PARTIES')
126 then
127 fnd_message.set_name ('JTF', 'JTF_RS_ENTITY_NAME_ERR');
128 fnd_message.set_token('P_ENTITY',p_entity_name);
129 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
130 FND_MSG_PUB.add;
131 RAISE fnd_api.g_exc_error;
132 end if;
133
134 FOR cat_rec IN cat_cur(p_from_fk_id) LOOP
135 l_address_id := cat_rec.address_id;
136 if (cat_rec.category = 'PARTY')
137 THEN
138 open to_party_cur(p_to_fk_id);
139 fetch to_party_cur into to_party_rec;
140 if(to_party_cur%found)
141 then
142 ----------------------------------------------------
143 -- Check if user wants to end date the record before erroring the process itself.
144 IF (l_error_handle = 'END_DATE') THEN
145
146 /* even if it says end date employee, it is for end dating all type of resources */
147 /* End Date the cat_rec.resource_id resource i.e. the one which we want to anyway change to new party */
148
149 l_object_ver_number := cat_rec.object_version_number ;
150
151 /* Calling publish API to raise merge resource event. Fix for Enhancement: 3295476 */
152 begin
153 jtf_rs_wf_events_pub.merge_resource
154 (p_api_version => 1.0
155 ,p_init_msg_list => fnd_api.g_false
156 ,p_commit => fnd_api.g_false
157 ,p_resource_id => cat_rec.resource_id
158 ,p_repl_resource_id => to_party_rec.resource_id
159 ,p_end_date_active => trunc(sysdate-1)
160 ,x_return_status => l_return_status
161 ,x_msg_count => l_msg_count
162 ,x_msg_data => l_msg_data);
163
164 EXCEPTION when others then
165 null;
166 end;
167
168 /* End of publish API call */
169
170 JTF_RS_RESOURCE_UTL_PUB.END_DATE_EMPLOYEE
171 (P_API_VERSION => 1.0,
172 P_INIT_MSG_LIST => FND_API.G_FALSE,
173 P_COMMIT => FND_API.G_FALSE,
174 P_RESOURCE_ID => cat_rec.resource_id,
175 P_END_DATE_ACTIVE => trunc(sysdate-1) ,
176 X_OBJECT_VER_NUMBER => l_object_ver_number,
177 X_RETURN_STATUS => l_return_status,
178 X_MSG_COUNT => l_msg_count,
179 X_MSG_DATA => l_msg_data ) ;
180
181 IF (nvl(l_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success) then
182 x_return_status := l_return_status ;
183 fnd_message.set_name ('JTF', 'JTF_RS_REJECT_MERGE');
184 FND_MSG_PUB.add;
185 RAISE fnd_api.g_exc_error;
186 END IF;
187
188 ELSIF (l_error_handle = 'ERROR') THEN
189 ----------------------------------------------------
190 -- reject merge
191 fnd_message.set_name ('JTF', 'JTF_RS_REJECT_MERGE');
192 FND_MSG_PUB.add;
193 x_return_status := fnd_api.g_ret_sts_error;
194 RAISE fnd_api.g_exc_error;
195 ----------------------------------------------------
196 END IF;
197 ----------------------------------------------------
198 else
199 l_address_id := null ;
200 open party_addr_cur(p_to_fk_id);
201 fetch party_addr_cur into l_address_id;
202 close party_addr_cur;
203
204 -- update from record with new source id
205 update jtf_rs_resource_extns
206 set source_id = p_to_fk_id,
207 address_id = l_address_id,
208 object_version_number = object_version_number + 1
209 where resource_id = cat_rec.resource_id;
210
211 -- x_to_id := p_from_id;
212
213 if(nvl(l_address_id, fnd_api.g_miss_num) <> nvl(cat_rec.address_id, fnd_api.g_miss_num))
214 then
215 insert into JTF_RS_RESOURCE_EXTN_AUD (
216 RESOURCE_AUDIT_ID,
217 RESOURCE_ID,
218 OLD_SOURCE_ID,
219 NEW_SOURCE_ID,
220 OLD_ADDRESS_ID,
221 NEW_ADDRESS_ID,
222 NEW_OBJECT_VERSION_NUMBER,
223 OLD_OBJECT_VERSION_NUMBER,
224 CREATED_BY,
225 CREATION_DATE,
226 LAST_UPDATED_BY,
227 LAST_UPDATE_DATE,
228 LAST_UPDATE_LOGIN
229 ) values (
230 JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
231 cat_rec.resource_id,
232 p_from_fk_id,
233 p_to_fk_id,
234 cat_rec.address_id,
235 l_address_id,
236 cat_rec.object_version_number + 1,
237 cat_rec.object_version_number,
238 l_user_id,
239 l_date,
240 l_user_id,
241 l_date,
242 l_login_id
243 );
244 else
245 insert into JTF_RS_RESOURCE_EXTN_AUD (
246 RESOURCE_AUDIT_ID,
247 RESOURCE_ID,
248 OLD_SOURCE_ID,
249 NEW_SOURCE_ID,
250 NEW_OBJECT_VERSION_NUMBER,
251 OLD_OBJECT_VERSION_NUMBER,
252 CREATED_BY,
253 CREATION_DATE,
254 LAST_UPDATED_BY,
255 LAST_UPDATE_DATE,
256 LAST_UPDATE_LOGIN
257 ) values (
258 JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
259 cat_rec.resource_id,
260 p_from_fk_id,
261 p_to_fk_id,
262 cat_rec.object_version_number + 1,
263 cat_rec.object_version_number,
264 l_user_id,
265 l_date,
266 l_user_id,
267 l_date,
268 l_login_id
269 );
270 end if;
271
272 --synchrnize this resource
273 synchronize_resource(p_resource_id => cat_rec.resource_id,
274 p_category => cat_rec.category,
275 p_address_id => l_address_id,
276 p_source_id => p_to_fk_id,
277 x_ret_status => x_return_status);
278
279 if(x_return_status <> fnd_api.g_ret_sts_success)
280 then
281 RAISE fnd_api.g_exc_error;
282 end if;
283 end if;
284 close to_party_cur;
285 end if; -- party
286
287 if (cat_rec.category = 'PARTNER')
288 then
289 open to_partner_cur(p_to_fk_id);
290 fetch to_partner_cur into to_partner_rec;
291 if (to_partner_cur%found)
292 then
293 ----------------------------------------------------
294 -- Check if user wants to end date the record before erroring the process itself.
295 IF (l_error_handle = 'END_DATE') THEN
296
297 /* even if it says end date employee, it is for end dating all type of resources */
298 /* End Date the cat_rec.resource_id resource i.e. the one which we want to anyway change to new party */
299
300 l_object_ver_number := cat_rec.object_version_number ;
301
302 /* Calling publish API to raise merge resource event. Fix for Enhancement: 3295476 */
303
304 begin
305 jtf_rs_wf_events_pub.merge_resource
306 (p_api_version => 1.0
307 ,p_init_msg_list => fnd_api.g_false
308 ,p_commit => fnd_api.g_false
309 ,p_resource_id => cat_rec.resource_id
310 ,p_repl_resource_id => to_partner_rec.resource_id
311 ,p_end_date_active => trunc(sysdate-1)
312 ,x_return_status => l_return_status
313 ,x_msg_count => l_msg_count
314 ,x_msg_data => l_msg_data);
315
316 EXCEPTION when others then
317 null;
318 end;
319
320 /* End of publish API call */
321
322 JTF_RS_RESOURCE_UTL_PUB.END_DATE_EMPLOYEE
323 (P_API_VERSION => 1.0,
324 P_INIT_MSG_LIST => FND_API.G_FALSE,
325 P_COMMIT => FND_API.G_FALSE,
326 P_RESOURCE_ID => cat_rec.resource_id,
327 P_END_DATE_ACTIVE => trunc(sysdate-1) ,
328 X_OBJECT_VER_NUMBER => l_object_ver_number,
329 X_RETURN_STATUS => l_return_status,
330 X_MSG_COUNT => l_msg_count,
331 X_MSG_DATA => l_msg_data ) ;
332
333 IF (nvl(l_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success) then
334 x_return_status := l_return_status ;
335 fnd_message.set_name ('JTF', 'JTF_RS_REJECT_MERGE');
336 FND_MSG_PUB.add;
337 RAISE fnd_api.g_exc_error;
338 END IF;
339
340 ELSIF (l_error_handle = 'ERROR') THEN
341 ----------------------------------------------------
342 -- reject merge
343 fnd_message.set_name ('JTF', 'JTF_RS_REJECT_MERGE');
344 FND_MSG_PUB.add;
345 x_return_status := fnd_api.g_ret_sts_error;
346 RAISE fnd_api.g_exc_error;
347 ----------------------------------------------------
348 END IF;
349 ----------------------------------------------------
350 else
351 --get primary address of the new party if party_type = 'PARTY_RELATIONSHIP'
352 l_address_id := null ;
353 open partner_cur(p_to_fk_id);
354 fetch partner_cur into partner_rec;
355 close partner_cur;
356
357 if(partner_rec.party_type = 'PARTY_RELATIONSHIP')
358 then
359 l_address_id := partner_rec.address_id;
360 else
361 l_address_id := cat_rec.address_id;
362 end if;
363
364 -- update from record with new source id
365 update jtf_rs_resource_extns
366 set source_id = p_to_fk_id,
367 address_id = l_address_id,
368 object_version_number = object_version_number + 1
369 where resource_id = cat_rec.resource_id;
370 -- x_to_id := p_from_id;
371
372 if(nvl(l_address_id, fnd_api.g_miss_num) <> nvl(cat_rec.address_id, fnd_api.g_miss_num))
373 then
374 insert into JTF_RS_RESOURCE_EXTN_AUD (
375 RESOURCE_AUDIT_ID,
376 RESOURCE_ID,
377 OLD_SOURCE_ID,
378 NEW_SOURCE_ID,
379 OLD_ADDRESS_ID,
380 NEW_ADDRESS_ID,
381 NEW_OBJECT_VERSION_NUMBER,
382 OLD_OBJECT_VERSION_NUMBER,
383 CREATED_BY,
384 CREATION_DATE,
385 LAST_UPDATED_BY,
386 LAST_UPDATE_DATE,
387 LAST_UPDATE_LOGIN
388 ) values (
389 JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
390 cat_rec.resource_id,
391 p_from_fk_id,
392 p_to_fk_id,
393 cat_rec.address_id,
394 l_address_id,
395 cat_rec.object_version_number + 1,
396 cat_rec.object_version_number,
397 l_user_id,
398 l_date,
399 l_user_id,
400 l_date,
401 l_login_id
402 );
403 else
404 insert into JTF_RS_RESOURCE_EXTN_AUD (
405 RESOURCE_AUDIT_ID,
406 RESOURCE_ID,
407 OLD_SOURCE_ID,
408 NEW_SOURCE_ID,
409 NEW_OBJECT_VERSION_NUMBER,
410 OLD_OBJECT_VERSION_NUMBER,
411 CREATED_BY,
412 CREATION_DATE,
413 LAST_UPDATED_BY,
414 LAST_UPDATE_DATE,
415 LAST_UPDATE_LOGIN
416 ) values (
417 JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
418 cat_rec.resource_id,
419 p_from_fk_id,
420 p_to_fk_id,
421 cat_rec.object_version_number + 1,
422 cat_rec.object_version_number,
423 l_user_id,
424 l_date,
425 l_user_id,
426 l_date,
427 l_login_id
428 );
429 end if;
430
431 synchronize_resource(p_resource_id => cat_rec.resource_id,
432 p_category => cat_rec.category,
433 p_address_id => l_address_id,
434 p_source_id => p_to_fk_id,
435 x_ret_status => x_return_status);
436 if(x_return_status <> fnd_api.g_ret_sts_success)
437 then
438 RAISE fnd_api.g_exc_error;
439 end if;
440
441 end if;
442 close to_partner_cur;
443 end if; -- end of partner
444 END LOOP;-- end of cat_cur loop
445
446 FND_FILE.put_line(fnd_file.log, ' End JTF_RS_PARTY_MERGE_PUB.resource_party_merge(-) ');
447 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Party Merge-----------------');
448
449 EXCEPTION
450 WHEN fnd_api.g_exc_unexpected_error
451 THEN
452 x_return_status := fnd_api.g_ret_sts_unexp_error;
453 ROLLBACK TO PARTY_MERGE_SP;
454 WHEN fnd_api.g_exc_error
455 THEN
456 ROLLBACK TO PARTY_MERGE_SP;
457 x_return_status := fnd_api.g_ret_sts_error;
458 WHEN OTHERS
459 THEN
460 ROLLBACK TO PARTY_MERGE_SP;
461 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
462 fnd_message.set_token('P_SQLCODE',SQLCODE);
463 fnd_message.set_token('P_SQLERRM',SQLERRM);
464 fnd_message.set_token('P_API_NAME', l_api_name);
465 FND_MSG_PUB.add;
466 x_return_status := fnd_api.g_ret_sts_unexp_error;
467 END resource_party_merge;
468
469
470 PROCEDURE resource_party_site_merge(
471 p_entity_name IN VARCHAR2,
472 p_from_id IN NUMBER,
473 x_to_id OUT NOCOPY NUMBER,
474 p_from_fk_id IN NUMBER,
475 p_to_fk_id IN NUMBER,
476 p_parent_entity_name IN VARCHAR2,
477 p_batch_id IN NUMBER,
478 p_batch_party_id IN NUMBER,
479 x_return_status OUT NOCOPY VARCHAR2)
480 is
481 l_api_name varchar2(30) := 'RESOURCE_PARTY_SITE_MERGE';
482
483 cursor from_cur(l_address_id number)
484 is
485 select resource_id
486 ,category
487 ,resource_number
488 ,source_id
489 ,contact_id
490 ,object_version_number
491 , created_by
492 , creation_date
493 , last_updated_by
494 , last_update_date
495 , last_update_login
496 from jtf_rs_resource_extns
497 where address_id = l_address_id
498 and category = 'PARTNER';
499
500 cursor to_cur(l_source_id number,
501 l_address_id number)
502 is
503 select resource_id
504 from jtf_rs_resource_extns
505 where category = 'PARTNER'
506 and source_id = l_source_id
507 and address_id = l_address_id ;
508
509 to_rec to_cur%rowtype;
510 l_date Date;
511 l_user_id Number;
512 l_login_id Number;
513 -------------------------------------
514 l_error_handle varchar2(30) ;
515 L_OBJECT_VER_NUMBER NUMBER;
516 L_RETURN_STATUS VARCHAR2(2);
517 L_MSG_COUNT NUMBER;
518 L_MSG_DATA VARCHAR2(2000);
519 -------------------------------------
520
521 begin
522 --GET USER ID AND SYSDATE
523 l_date := sysdate;
524 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
525 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
526 -------------------------------------
527 l_error_handle := NVL(FND_PROFILE.Value('JTF_RS_PARTY_MRG_FRMID_TOID_ER'),'ERROR');
528 -------------------------------------
529
530 savepoint site_merge_sp;
531
532 -----Debug Messages--------------------
533 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Party Site Merge-----------------');
534 FND_FILE.put_line(fnd_file.log, ' Begin JTF_RS_PARTY_MERGE_PUB.resource_party_site_merge(+) ');
535 FND_FILE.put_line(fnd_file.log, ' p_entity_name :'||p_entity_name);
536 FND_FILE.put_line(fnd_file.log, ' p_from_id :'||p_from_id);
537 FND_FILE.put_line(fnd_file.log, ' p_from_fk_id :'||p_from_fk_id);
538 FND_FILE.put_line(fnd_file.log, ' p_to_fk_id :'||p_to_fk_id);
539 FND_FILE.put_line(fnd_file.log, ' p_parent_entity_name:'||p_parent_entity_name);
540 FND_FILE.put_line(fnd_file.log, ' p_batch_id :'||p_batch_id);
541 FND_FILE.put_line(fnd_file.log, ' p_batch_party_id :'||p_batch_party_id);
542 FND_FILE.put_line(fnd_file.log, ' Error Handling Mode :'||l_error_handle);
543 -----End Debug Messages----------------
544
545 x_return_status := fnd_api.g_ret_sts_success;
546
547 if (p_entity_name <> 'JTF_RS_RESOURCE_EXTNS')
548 or (p_parent_entity_name <> 'HZ_PARTY_SITES')
549 then
550 fnd_message.set_name ('JTF', 'JTF_RS_ENTITY_NAME_ERR');
551 fnd_message.set_token('P_ENTITY',p_entity_name);
552 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
553 FND_MSG_PUB.add;
554 RAISE fnd_api.g_exc_error;
555 end if;
556
557 -- get the values of the from record
558 FOR from_rec IN from_cur(p_from_fk_id) LOOP
559 --check if there exists another resource with same source id and the new address id
560 open to_cur(from_rec.source_id,
561 p_to_fk_id);
562 fetch to_cur into to_rec;
563 if(to_cur%found)
564 then
565 ----------------------------------------------------
566 -- Check if user wants to end date the record before erroring the process itself.
567 IF (l_error_handle = 'END_DATE') THEN
568
569 /* even if it says end date employee, it is for end dating all type of resources */
570 /* End Date the cat_rec.resource_id resource i.e. the one which we want to anyway change to new party */
571
572 l_object_ver_number := from_rec.object_version_number ;
573
574 /* Calling publish API to raise merge resource event. Fix for Enhancement: 3295476 */
575
576 begin
577 jtf_rs_wf_events_pub.merge_resource
578 (p_api_version => 1.0
579 ,p_init_msg_list => fnd_api.g_false
580 ,p_commit => fnd_api.g_false
581 ,p_resource_id => from_rec.resource_id
582 ,p_repl_resource_id => to_rec.resource_id
583 ,p_end_date_active => trunc(sysdate-1)
584 ,x_return_status => l_return_status
585 ,x_msg_count => l_msg_count
586 ,x_msg_data => l_msg_data);
587
588 EXCEPTION when others then
589 null;
590 end;
591
592 /* End of publish API call */
593
594 JTF_RS_RESOURCE_UTL_PUB.END_DATE_EMPLOYEE
595 (P_API_VERSION => 1.0,
596 P_INIT_MSG_LIST => FND_API.G_FALSE,
597 P_COMMIT => FND_API.G_FALSE,
598 P_RESOURCE_ID => from_rec.resource_id,
599 P_END_DATE_ACTIVE => trunc(sysdate-1) ,
600 X_OBJECT_VER_NUMBER => l_object_ver_number,
601 X_RETURN_STATUS => l_return_status,
602 X_MSG_COUNT => l_msg_count,
603 X_MSG_DATA => l_msg_data ) ;
604
605 IF (nvl(l_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success) then
606 x_return_status := l_return_status ;
607 fnd_message.set_name ('JTF', 'JTF_RS_REJECT_MERGE');
608 FND_MSG_PUB.add;
609 RAISE fnd_api.g_exc_error;
610 END IF;
611
612 ELSIF (l_error_handle = 'ERROR') THEN
613 ----------------------------------------------------
614 --reject merge
615 fnd_message.set_name ('JTF', 'JTF_RS_REJECT_MERGE');
616 FND_MSG_PUB.add;
617 x_return_status := fnd_api.g_ret_sts_error;
618 RAISE fnd_api.g_exc_error;
619 ----------------------------------------------------
620 END IF;
621 ----------------------------------------------------
622 else
623 --if another resource does not exist change the same resource to have the new address id
624 update jtf_rs_resource_extns
625 set address_id = p_to_fk_id,
626 object_version_number = object_version_number + 1
627 where resource_id = from_rec.resource_id;
628 -- x_to_id := p_from_id;
629
630 insert into JTF_RS_RESOURCE_EXTN_AUD (
631 RESOURCE_AUDIT_ID,
632 RESOURCE_ID,
633 OLD_ADDRESS_ID,
634 NEW_ADDRESS_ID,
635 NEW_OBJECT_VERSION_NUMBER,
636 OLD_OBJECT_VERSION_NUMBER,
637 CREATED_BY,
638 CREATION_DATE,
639 LAST_UPDATED_BY,
640 LAST_UPDATE_DATE,
641 LAST_UPDATE_LOGIN
642 ) values (
643 JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
644 from_rec.resource_id,
645 p_from_fk_id,
646 p_to_fk_id,
647 from_rec.object_version_number + 1,
648 from_rec.object_version_number,
649 l_user_id,
650 l_date,
651 l_user_id,
652 l_date,
653 l_login_id
654 );
655 --since site merge changes the address_id only it is not required to do synchronization.
656 --So Commenting the code below. This is done as part og Bug fix 3695580
657 /*
658 synchronize_resource(p_resource_id => from_rec.resource_id,
659 p_category => from_rec.category,
660 p_address_id => p_to_fk_id,
661 p_source_id => from_rec.source_id,
662 x_ret_status => x_return_status);
663
664 if(x_return_status <> fnd_api.g_ret_sts_success)
665 then
666 RAISE fnd_api.g_exc_error;
667 end if;
668 */
669 end if;
670 close to_cur;
671
672 END LOOP; -- end of from_cur loop
673
674 FND_FILE.put_line(fnd_file.log, ' End JTF_RS_PARTY_MERGE_PUB.resource_party_site_merge(-) ');
675 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Party Site Merge-----------------');
676
677 EXCEPTION
678 WHEN fnd_api.g_exc_unexpected_error
679 THEN
680 x_return_status := fnd_api.g_ret_sts_unexp_error;
681 ROLLBACK TO site_merge_sp;
682 WHEN fnd_api.g_exc_error
683 THEN
684 ROLLBACK TO site_merge_sp;
685 x_return_status := fnd_api.g_ret_sts_error;
686 WHEN OTHERS
687 THEN
688 ROLLBACK TO site_merge_sp;
689 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
690 fnd_message.set_token('P_SQLCODE',SQLCODE);
691 fnd_message.set_token('P_SQLERRM',SQLERRM);
692 fnd_message.set_token('P_API_NAME', l_api_name);
693 FND_MSG_PUB.add;
694 x_return_status := fnd_api.g_ret_sts_unexp_error;
695 end resource_party_site_merge;
696
697 PROCEDURE resource_party_cont_merge(
698 p_entity_name IN VARCHAR2,
699 p_from_id IN NUMBER,
700 x_to_id OUT NOCOPY NUMBER,
701 p_from_fk_id IN NUMBER,
702 p_to_fk_id IN NUMBER,
703 p_parent_entity_name IN VARCHAR2,
704 p_batch_id IN NUMBER,
705 p_batch_party_id IN NUMBER,
706 x_return_status OUT NOCOPY VARCHAR2)
707 is
708 l_api_name varchar2(30) := 'RESOURCE_PARTY_CONT_MERGE';
709
710 cursor res_cur(l_contact_id number)
711 is
712 select resource_id,
713 source_id,
714 address_id,
715 object_version_number
716 from jtf_rs_resource_extns
717 where contact_id = l_contact_id
718 and category IN ('PARTY','PARTNER') ;
719
720 cursor cont_cur(l_party_id number,
721 l_party_site_id number,
722 l_contact_id number)
723 is
724 select 'x'
725 from jtf_rs_party_contacts_vl
726 where party_id = l_party_id
727 and nvl(party_site_id, -1) = nvl(l_party_site_id, -1)
728 and contact_id = l_contact_id ;
729
730 l_date Date;
731 l_user_id Number;
732 l_login_id Number;
733 dummy varchar2(1) ;
734 begin
735 savepoint cont_merge_sp;
736 x_return_status := fnd_api.g_ret_sts_success;
737
738 --GET USER ID AND SYSDATE
739 l_date := sysdate;
740 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
741 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
742
743 -----Debug Messages--------------------
744 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Contact Merge-----------------');
745 FND_FILE.put_line(fnd_file.log, ' Begin JTF_RS_PARTY_MERGE_PUB.resource_party_cont_merge(+) ');
746 FND_FILE.put_line(fnd_file.log, ' p_entity_name :'||p_entity_name);
747 FND_FILE.put_line(fnd_file.log, ' p_from_id :'||p_from_id);
748 FND_FILE.put_line(fnd_file.log, ' p_from_fk_id :'||p_from_fk_id);
749 FND_FILE.put_line(fnd_file.log, ' p_to_fk_id :'||p_to_fk_id);
750 FND_FILE.put_line(fnd_file.log, ' p_parent_entity_name:'||p_parent_entity_name);
751 FND_FILE.put_line(fnd_file.log, ' p_batch_id :'||p_batch_id);
752 FND_FILE.put_line(fnd_file.log, ' p_batch_party_id :'||p_batch_party_id);
753 -----End Debug Messages----------------
754
755 if (p_entity_name <> 'JTF_RS_RESOURCE_EXTNS')
756 or (p_parent_entity_name <> 'HZ_ORG_CONTACTS')
757 then
758 fnd_message.set_name ('JTF', 'JTF_RS_ENTITY_NAME_ERR');
759 fnd_message.set_token('P_ENTITY',p_entity_name);
760 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
761 FND_MSG_PUB.add;
762 RAISE fnd_api.g_exc_error;
763 end if;
764
765 FOR res_rec IN res_cur(p_from_fk_id) LOOP
766
767 /* -- Bug 5921975 (Removed validation as it was doing p1-c2 validation which is
768 -- not valid and will always fail). (02-APR-2007)
769 open cont_cur(res_rec.source_id,
770 res_rec.address_id,
771 p_to_fk_id) ;
772 fetch cont_cur into dummy ;
773 if cont_cur%NOTFOUND then
774 fnd_message.set_name ('JTF', 'JTF_RS_VALID_TO_ID_ERR');
775 FND_MSG_PUB.add;
776 x_return_status := fnd_api.g_ret_sts_error;
777 RAISE fnd_api.g_exc_error;
778 else
779 */
780 update jtf_rs_resource_extns
781 set contact_id = p_to_fk_id,
782 object_version_number = object_version_number + 1
783 where resource_id = res_rec.resource_id;
784
785 -- x_to_id := p_from_id;
786
787
788 insert into JTF_RS_RESOURCE_EXTN_AUD (
789 RESOURCE_AUDIT_ID,
790 RESOURCE_ID,
791 OLD_CONTACT_ID,
792 NEW_CONTACT_ID,
793 NEW_OBJECT_VERSION_NUMBER,
794 OLD_OBJECT_VERSION_NUMBER,
795 CREATED_BY,
796 CREATION_DATE,
797 LAST_UPDATED_BY,
798 LAST_UPDATE_DATE,
799 LAST_UPDATE_LOGIN
800 ) values (
801 JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
802 res_rec.resource_id,
803 p_from_fk_id,
804 p_to_fk_id,
805 res_rec.object_version_number + 1,
806 res_rec.object_version_number,
807 l_user_id,
808 l_date,
809 l_user_id,
810 l_date,
811 l_login_id
812 );
813 -- end if ;
814 -- close cont_cur ;
815 END LOOP ;
816
817 FND_FILE.put_line(fnd_file.log, ' End JTF_RS_PARTY_MERGE_PUB.resource_party_cont_merge(-) ');
818 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Contact Merge-----------------');
819
820 EXCEPTION
821 WHEN fnd_api.g_exc_unexpected_error
822 THEN
823 x_return_status := fnd_api.g_ret_sts_unexp_error;
824 ROLLBACK TO cont_merge_sp;
825 WHEN fnd_api.g_exc_error
826 THEN
827 ROLLBACK TO cont_merge_sp;
828 x_return_status := fnd_api.g_ret_sts_error;
829 WHEN OTHERS
830 THEN
831 ROLLBACK TO cont_merge_sp;
832 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
833 fnd_message.set_token('P_SQLCODE',SQLCODE);
834 fnd_message.set_token('P_SQLERRM',SQLERRM);
835 fnd_message.set_token('P_API_NAME', l_api_name);
836 FND_MSG_PUB.add;
837 x_return_status := fnd_api.g_ret_sts_unexp_error;
838 end resource_party_cont_merge;
839
840 PROCEDURE synchronize_resource(p_resource_id IN NUMBER,
841 p_category IN VARCHAR2,
842 p_address_id IN NUMBER,
843 p_source_id IN NUMBER,
844 x_ret_status out NOCOPY VARCHAR2)
845 IS
846 l_api_name varchar2(30) := 'SYNCHRONIZE_RESOURCE';
847 cursor party_cur
848 is
849 SELECT PARTY.PARTY_NUMBER,
850 PARTY.PARTY_NAME,
851 PARTY.EMAIL_ADDRESS,
852 PARTY.ADDRESS1 ,
853 PARTY.ADDRESS2 ,
854 PARTY.ADDRESS3 ,
855 PARTY.ADDRESS4 ,
856 PARTY.CITY ,
857 PARTY.POSTAL_CODE ,
858 PARTY.STATE ,
859 PARTY.PROVINCE,
860 PARTY.COUNTY ,
861 PARTY.COUNTRY ,
862 CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
863 TO_NUMBER(NULL) ORG_ID,
864 NULL ORG_NAME,
865 PARTY.PERSON_FIRST_NAME,
866 PARTY.PERSON_MIDDLE_NAME,
867 PARTY.PERSON_LAST_NAME
868 FROM
869 HZ_PARTIES PARTY,
870 HZ_CONTACT_POINTS CT_POINT1
871 WHERE PARTY.PARTY_ID = p_source_id
872 AND PARTY.PARTY_TYPE NOT IN ('ORGANIZATION', 'GROUP')
873 AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
874 AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
875 AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
876 AND CT_POINT1.STATUS (+)= 'A'
877 AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
878
879 party_rec party_cur%rowtype;
880
881 CURSOR par_cur
882 is
883 SELECT PARTY.PARTY_NAME,
884 PARTY.PARTY_NUMBER,
885 PARTY.EMAIL_ADDRESS,
886 /*PARTY.ADDRESS1 ,
887 PARTY.ADDRESS2 ,
888 PARTY.ADDRESS3 ,
889 PARTY.ADDRESS4 ,
890 PARTY.CITY ,
891 PARTY.POSTAL_CODE ,
892 PARTY.STATE ,
893 PARTY.PROVINCE,
894 PARTY.COUNTY ,
895 PARTY.COUNTRY , */
896 CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
897 REL.OBJECT_ID ORG_ID,
898 PARTY.PARTY_NAME ORG_NAME,
899 PARTY.PERSON_FIRST_NAME,
900 PARTY.PERSON_MIDDLE_NAME,
901 PARTY.PERSON_LAST_NAME
902 FROM
903 HZ_PARTIES PARTY,
904 HZ_PARTIES PARTY2,
905 HZ_PARTIES PARTY3,
906 HZ_CONTACT_POINTS CT_POINT1,
907 -- HZ_PARTY_RELATIONSHIPS REL
908 HZ_RELATIONSHIPS REL
909 WHERE PARTY.PARTY_ID = p_source_id
910 AND (
911 (
912 PARTY.PARTY_TYPE = 'ORGANIZATION'
913 AND
914 PARTY.PARTY_ID = REL.SUBJECT_ID
915 )
916 OR
917 (
918 PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
919 AND
920 PARTY.PARTY_ID = REL.PARTY_ID
921 )
922 )
923 -- AND REL.PARTY_RELATIONSHIP_TYPE IN ('PARTNER_OF', 'VAD_OF', 'THIRD_PARTY_FOR')
924 --
925 AND REL.RELATIONSHIP_CODE IN
926 ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
927 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY')
928 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
929 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
930 AND REL.DIRECTIONAL_FLAG = 'F'
931 AND REL.STATUS = 'A'
932 --
933 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
934 AND (PARTY2.PARTY_TYPE = 'PERSON'
935 OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
936 AND REL.OBJECT_ID = PARTY3.PARTY_ID
937 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
938 AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
939 AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
940 AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
941 AND CT_POINT1.STATUS (+)= 'A'
942 AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
943
944 par_rec par_cur%rowtype;
945
946 cursor par_address_cur
947 is
948 SELECT PARTY.PARTY_NUMBER,
949 PARTY.PARTY_NAME,
950 PARTY.EMAIL_ADDRESS,
951 LOC.ADDRESS1
952 ,LOC.ADDRESS2
953 ,LOC.ADDRESS3
954 ,LOC.ADDRESS4
955 ,LOC.CITY
956 ,LOC.POSTAL_CODE
957 ,LOC.STATE
958 ,LOC.PROVINCE
959 ,LOC.COUNTY
960 ,LOC.COUNTRY ,
961 CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
962 REL.OBJECT_ID ORG_ID,
963 PARTY.PARTY_NAME ORG_NAME,
964 PARTY.PERSON_FIRST_NAME,
965 PARTY.PERSON_MIDDLE_NAME,
966 PARTY.PERSON_LAST_NAME
967 FROM
968 HZ_PARTIES PARTY,
969 HZ_PARTIES PARTY2,
970 HZ_PARTIES PARTY3,
971 HZ_PARTY_SITES PARTY_SITE,
972 HZ_LOCATIONS LOC,
973 HZ_CONTACT_POINTS CT_POINT1,
974 -- HZ_PARTY_RELATIONSHIPS REL
975 HZ_RELATIONSHIPS REL
976 WHERE PARTY.PARTY_ID = p_source_id
977 AND (
978 (
979 PARTY.PARTY_TYPE = 'ORGANIZATION'
980 AND
981 PARTY.PARTY_ID = REL.SUBJECT_ID
982 )
983 OR
984 (
985 PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
986 AND
987 PARTY.PARTY_ID = REL.PARTY_ID
988 )
989 )
990 -- AND REL.PARTY_RELATIONSHIP_TYPE IN ('PARTNER_OF', 'VAD_OF', 'THIRD_PARTY_FOR')
991 --
992 AND REL.RELATIONSHIP_CODE IN
993 ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
994 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY')
995 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
996 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
997 AND REL.DIRECTIONAL_FLAG = 'F'
998 AND REL.STATUS = 'A'
999 --
1000 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
1001 AND (PARTY2.PARTY_TYPE = 'PERSON'
1002 OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
1003 AND REL.OBJECT_ID = PARTY3.PARTY_ID
1004 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
1005 AND PARTY_SITE.PARTY_SITE_ID = p_address_id
1006 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+)
1007 AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
1008 AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
1009 AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
1010 AND CT_POINT1.STATUS (+)= 'A'
1011 AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
1012
1013
1014 par_address_rec par_address_cur%rowtype;
1015
1016 begin
1017
1018 FND_FILE.put_line(fnd_file.log, ' Begin JTF_RS_PARTY_MERGE_PUB.synchronize_resource(+) ');
1019
1020 SAVEPOINT synchronize_resource_sp;
1021 x_ret_status := fnd_api.g_ret_sts_success;
1022
1023 if(p_category = 'PARTY')
1024 THEN
1025 open party_cur;
1026 fetch party_cur into party_rec;
1027 if (party_cur%found)
1028 then
1029
1030 UPDATE JTF_RS_RESOURCE_EXTNS RES
1031 SET RES.OBJECT_VERSION_NUMBER = res.object_version_number + 1 ,
1032 RES.LAST_UPDATE_DATE = sysdate,
1033 RES.LAST_UPDATED_BY = fnd_global.user_id,
1034 RES.SOURCE_NUMBER = party_rec.party_number ,
1035 RES.SOURCE_NAME = party_rec.party_name,
1036 RES.SOURCE_EMAIL = party_rec.email_address,
1037 RES.SOURCE_ADDRESS1= party_rec.address1,
1038 RES.SOURCE_ADDRESS2 = party_rec.address2,
1039 RES.SOURCE_ADDRESS3 = party_rec.address3,
1040 RES.SOURCE_ADDRESS4 = party_rec.address4,
1041 RES.SOURCE_CITY = party_rec.city,
1042 RES.SOURCE_POSTAL_CODE = party_rec.postal_code,
1043 RES.SOURCE_STATE = party_rec.state,
1044 RES.SOURCE_PROVINCE = party_rec.province,
1045 RES.SOURCE_COUNTY = party_rec.county,
1046 RES.SOURCE_COUNTRY = party_rec.country,
1047 RES.SOURCE_PHONE = party_rec.phone,
1048 --RES.SOURCE_MGR_ID ,
1049 --RES.SOURCE_MGR_NAME ,
1050 RES.SOURCE_ORG_ID = party_rec.org_id ,
1051 RES.SOURCE_ORG_NAME = party_rec.org_name,
1052 RES.SOURCE_FIRST_NAME = party_rec.person_first_name,
1053 RES.SOURCE_MIDDLE_NAME = party_rec.person_middle_name,
1054 RES.SOURCE_LAST_NAME = party_rec.person_last_name
1055 WHERE RES.RESOURCE_ID = p_resource_id;
1056 end if;
1057 close party_cur;
1058 elsif(p_category = 'PARTNER')
1059 then
1060 if(p_address_id is null)
1061 THEN
1062 open par_cur;
1063 fetch par_cur into par_rec;
1064 if (par_cur%found)
1065 then
1066 UPDATE JTF_RS_RESOURCE_EXTNS RES
1067 SET RES.OBJECT_VERSION_NUMBER = res.object_version_number + 1 ,
1068 RES.LAST_UPDATE_DATE = sysdate,
1069 RES.LAST_UPDATED_BY = fnd_global.user_id,
1070 RES.SOURCE_NUMBER = par_rec.party_number ,
1071 RES.SOURCE_NAME = par_rec.party_name,
1072 RES.SOURCE_EMAIL = par_rec.email_address,
1073 /*RES.SOURCE_ADDRESS1= par_rec.address1,
1074 RES.SOURCE_ADDRESS2 = par_rec.address2,
1075 RES.SOURCE_ADDRESS3 = par_rec.address3,
1076 RES.SOURCE_ADDRESS4 = par_rec.address4,
1077 RES.SOURCE_CITY = par_rec.city,
1078 RES.SOURCE_POSTAL_CODE = par_rec.postal_code,
1079 RES.SOURCE_STATE = par_rec.state,
1080 RES.SOURCE_PROVINCE = par_rec.province,
1081 RES.SOURCE_COUNTY = par_rec.county,
1082 RES.SOURCE_COUNTRY = par_rec.country, */
1083 RES.SOURCE_PHONE = par_rec.phone,
1084 --RES.SOURCE_MGR_ID ,
1085 --RES.SOURCE_MGR_NAME ,
1086 RES.SOURCE_ORG_ID = par_rec.org_id ,
1087 RES.SOURCE_ORG_NAME = par_rec.org_name,
1088 RES.SOURCE_FIRST_NAME = par_rec.person_first_name,
1089 RES.SOURCE_MIDDLE_NAME = par_rec.person_middle_name,
1090 RES.SOURCE_LAST_NAME = par_rec.person_last_name
1091 where RES.RESOURCE_ID = p_resource_id;
1092 end if;
1093 close par_cur;
1094 else
1095
1096 open par_address_cur;
1097 fetch par_address_cur into par_address_rec;
1098 if (par_address_cur%found)
1099 then
1100 UPDATE JTF_RS_RESOURCE_EXTNS RES
1101 SET RES.OBJECT_VERSION_NUMBER = res.object_version_number + 1 ,
1102 RES.LAST_UPDATE_DATE = sysdate,
1103 RES.LAST_UPDATED_BY = fnd_global.user_id,
1104 RES.SOURCE_NUMBER = par_address_rec.party_number ,
1105 RES.SOURCE_NAME = par_address_rec.party_name,
1106 RES.SOURCE_EMAIL = par_address_rec.email_address,
1107 RES.SOURCE_ADDRESS1= par_address_rec.address1,
1108 RES.SOURCE_ADDRESS2 = par_address_rec.address2,
1109 RES.SOURCE_ADDRESS3 = par_address_rec.address3,
1110 RES.SOURCE_ADDRESS4 = par_address_rec.address4,
1111 RES.SOURCE_CITY = par_address_rec.city,
1112 RES.SOURCE_POSTAL_CODE = par_address_rec.postal_code,
1113 RES.SOURCE_STATE = par_address_rec.state,
1114 RES.SOURCE_PROVINCE = par_address_rec.province,
1115 RES.SOURCE_COUNTY = par_address_rec.county,
1116 RES.SOURCE_COUNTRY = par_address_rec.country,
1117 RES.SOURCE_PHONE = par_address_rec.phone,
1118 --RES.SOURCE_MGR_ID ,
1119 --RES.SOURCE_MGR_NAME ,
1120 RES.SOURCE_ORG_ID = par_address_rec.org_id ,
1121 RES.SOURCE_ORG_NAME = par_address_rec.org_name,
1122 RES.SOURCE_FIRST_NAME = par_address_rec.person_first_name,
1123 RES.SOURCE_MIDDLE_NAME = par_address_rec.person_middle_name,
1124 RES.SOURCE_LAST_NAME = par_address_rec.person_last_name
1125 where RES.RESOURCE_ID = p_resource_id;
1126 end if;
1127 close par_address_cur;
1128
1129
1130 end if; -- end of address_id check
1131
1132 end if; -- end of category check
1133
1134 update jtf_rs_resource_extns_tl res
1135 set resource_name
1136 = (select party_name
1137 from hz_parties
1138 where party_id = p_source_id)
1139 where res.resource_id = p_resource_id;
1140 FND_FILE.put_line(fnd_file.log, ' End JTF_RS_PARTY_MERGE_PUB.synchronize_resource(-) ');
1141
1142 EXCEPTION
1143 WHEN fnd_api.g_exc_unexpected_error
1144 THEN
1145 ROLLBACK TO synchronize_resource_sp;
1146 x_ret_status := fnd_api.g_ret_sts_error;
1147 WHEN fnd_api.g_exc_error
1148 THEN
1149 ROLLBACK TO synchronize_resource_sp;
1150 x_ret_status := fnd_api.g_ret_sts_error;
1151 WHEN OTHERS
1152 THEN
1153 ROLLBACK TO synchronize_resource_sp;
1154 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1155 fnd_message.set_token('P_SQLCODE',SQLCODE);
1156 fnd_message.set_token('P_SQLERRM',SQLERRM);
1157 fnd_message.set_token('P_API_NAME', l_api_name);
1158 FND_MSG_PUB.add;
1159 x_ret_status := fnd_api.g_ret_sts_error;
1160
1161 end synchronize_resource;
1162
1163 PROCEDURE resource_support_site_merge(
1164 p_entity_name IN VARCHAR2,
1165 p_from_id IN NUMBER,
1166 x_to_id OUT NOCOPY NUMBER,
1167 p_from_fk_id IN NUMBER,
1168 p_to_fk_id IN NUMBER,
1169 p_parent_entity_name IN VARCHAR2,
1170 p_batch_id IN NUMBER,
1171 p_batch_party_id IN NUMBER,
1172 x_return_status OUT NOCOPY VARCHAR2)
1173 is
1174 l_api_name varchar2(30) := 'RESOURCE_SUPPORT_SITE_MERGE';
1175
1176 cursor res_cur(c_support_site_id number)
1177 is
1178 select resource_id,
1179 source_id,
1180 object_version_number
1181 from jtf_rs_resource_extns
1182 where support_site_id = c_support_site_id;
1183
1184 --cursor support_site_cur(c_support_site_id number)
1185 --is
1186 --select 'X'
1187 --from hz_party_sites p,
1188 -- hz_party_site_uses psu
1189 --where p.party_site_id = psu.party_site_id
1190 --and psu.site_use_type = 'SUPPORT_SITE'
1191 --and p.party_site_id = c_support_site_id;
1192
1193 l_date Date;
1194 l_user_id Number;
1195 l_login_id Number;
1196 dummy varchar2(1) ;
1197 begin
1198 savepoint support_site_merge_sp;
1199 x_return_status := fnd_api.g_ret_sts_success;
1200
1201 -----Debug Messages--------------------
1202 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Support Site Merge-----------------');
1203 FND_FILE.put_line(fnd_file.log, ' Begin JTF_RS_PARTY_MERGE_PUB.resource_support_site_merge(+) ');
1204 FND_FILE.put_line(fnd_file.log, ' p_entity_name :'||p_entity_name);
1205 FND_FILE.put_line(fnd_file.log, ' p_from_id :'||p_from_id);
1206 FND_FILE.put_line(fnd_file.log, ' p_from_fk_id :'||p_from_fk_id);
1207 FND_FILE.put_line(fnd_file.log, ' p_to_fk_id :'||p_to_fk_id);
1208 FND_FILE.put_line(fnd_file.log, ' p_parent_entity_name:'||p_parent_entity_name);
1209 FND_FILE.put_line(fnd_file.log, ' p_batch_id :'||p_batch_id);
1210 FND_FILE.put_line(fnd_file.log, ' p_batch_party_id :'||p_batch_party_id);
1211 -----End Debug Messages----------------
1212
1213 --GET USER ID AND SYSDATE
1214 l_date := sysdate;
1215 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1216 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1217
1218 if (p_entity_name <> 'JTF_RS_RESOURCE_EXTNS')
1219 or (p_parent_entity_name <> 'HZ_PARTY_SITES')
1220 then
1221 fnd_message.set_name ('JTF', 'JTF_RS_ENTITY_NAME_ERR');
1222 fnd_message.set_token('P_ENTITY',p_entity_name);
1223 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
1224 FND_MSG_PUB.add;
1225 RAISE fnd_api.g_exc_error;
1226 end if;
1227
1228 FOR res_rec IN res_cur(p_from_fk_id) LOOP
1229 -- open support_site_cur(p_to_fk_id) ;
1230 -- fetch support_site_cur into dummy ;
1231 -- if support_site_cur%NOTFOUND then
1232 -- fnd_message.set_name ('JTF', 'JTF_RS_VALID_TO_ID_ERR');
1233 -- FND_MSG_PUB.add;
1234 -- x_return_status := fnd_api.g_ret_sts_error;
1235 -- RAISE fnd_api.g_exc_error;
1236 -- else
1237 update jtf_rs_resource_extns
1238 set support_site_id = p_to_fk_id,
1239 object_version_number = object_version_number + 1
1240 where resource_id = res_rec.resource_id;
1241
1242 -- x_to_id := p_from_id;
1243
1244 insert into JTF_RS_RESOURCE_EXTN_AUD (
1245 RESOURCE_AUDIT_ID,
1246 RESOURCE_ID,
1247 OLD_SUPPORT_SITE_ID,
1248 NEW_SUPPORT_SITE_ID,
1249 NEW_OBJECT_VERSION_NUMBER,
1250 OLD_OBJECT_VERSION_NUMBER,
1251 CREATED_BY,
1252 CREATION_DATE,
1253 LAST_UPDATED_BY,
1254 LAST_UPDATE_DATE,
1255 LAST_UPDATE_LOGIN
1256 ) values (
1257 JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
1258 res_rec.resource_id,
1259 p_from_fk_id,
1260 p_to_fk_id,
1261 res_rec.object_version_number + 1,
1262 res_rec.object_version_number,
1263 l_user_id,
1264 l_date,
1265 l_user_id,
1266 l_date,
1267 l_login_id
1268 );
1269 -- end if ;
1270 -- close support_site_cur ;
1271 END LOOP ;
1272
1273 FND_FILE.put_line(fnd_file.log, ' End JTF_RS_PARTY_MERGE_PUB.resource_support_site_merge(-) ');
1274 FND_FILE.put_line(fnd_file.log, ' ------------------Resource Support Site Merge-----------------');
1275
1276 EXCEPTION
1277 WHEN fnd_api.g_exc_unexpected_error
1278 THEN
1279 x_return_status := fnd_api.g_ret_sts_unexp_error;
1280 ROLLBACK TO support_site_merge_sp;
1281 WHEN fnd_api.g_exc_error
1282 THEN
1283 ROLLBACK TO support_site_merge_sp;
1284 x_return_status := fnd_api.g_ret_sts_error;
1285 WHEN OTHERS
1286 THEN
1287 ROLLBACK TO support_site_merge_sp;
1288 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1289 fnd_message.set_token('P_SQLCODE',SQLCODE);
1290 fnd_message.set_token('P_SQLERRM',SQLERRM);
1291 fnd_message.set_token('P_API_NAME', l_api_name);
1292 FND_MSG_PUB.add;
1293 x_return_status := fnd_api.g_ret_sts_unexp_error;
1294 end resource_support_site_merge;
1295
1296 end;