1 PACKAGE BODY CS_SR_PARTY_MERGE_PKG AS
2 /* $Header: cssrpmgb.pls 120.1 2005/12/22 17:29:27 spusegao noship $ */
3
4
5 G_PROC_NAME CONSTANT VARCHAR2(30) := 'CS_SR_PARTY_MERGE_PKG';
6 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
7 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
8
9 TYPE ROWID_TBL IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
10 TYPE NUM_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
11 TYPE VARCHAR2_30_TBL IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
12
13
14 -- The following procedure merges CS_INCIDENTS_ALL_B columns:
15 -- customer_id
16 -- bill_to_contact_id
17 -- ship_to_contact_id
18 -- bill_to_party_id - added for 11.5.9
19 -- ship_to_party_id - added for 11.5.9
20 -- The above columns are FKs to HZ_PARTIES.PARTY_ID.
21
22 PROCEDURE CS_INC_ALL_MERGE_PARTY (
23 p_entity_name IN VARCHAR2,
24 p_from_id IN NUMBER,
25 x_to_id OUT NOCOPY NUMBER,
26 p_from_fk_id IN NUMBER,
27 p_to_fk_id IN NUMBER,
28 p_parent_entity_name IN VARCHAR2,
29 p_batch_id IN NUMBER,
30 p_batch_party_id IN NUMBER,
31 x_return_status OUT NOCOPY VARCHAR2)
32 IS
33 -- cursor fetches all the records that need to be merged.
34 CURSOR C1 IS
35 SELECT rowid,
36 incident_id,
37 customer_id,
38 bill_to_contact_id,
39 ship_to_contact_id,
40 bill_to_party_id,
41 ship_to_party_id,
42 last_update_program_code
43 FROM cs_incidents_all_b
44 WHERE p_from_fk_id in (customer_id, bill_to_contact_id, ship_to_contact_id,
45 bill_to_party_id, ship_to_party_id )
46 FOR UPDATE NOWAIT;
47
48 l_incident_id NUM_TBL;
49 l_customer_id NUM_TBL;
50 l_bill_to_contact_id NUM_TBL;
51 l_ship_to_contact_id NUM_TBL;
52 l_bill_to_party_id NUM_TBL;
53 l_ship_to_party_id NUM_TBL;
54 l_last_update_program_code VARCHAR2_30_TBL;
55 l_rowid_tbl ROWID_TBL;
56
57 l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
58 l_merge_reason_code VARCHAR2(30);
59 l_api_name VARCHAR2(30) := 'CS_INC_ALL_MERGE_PARTY';
60 l_count NUMBER(10) := 0;
61
62 RESOURCE_BUSY EXCEPTION;
63 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
64 l_return_status VARCHAR2(3);
65 x_msg_count NUMBER(15);
66 x_msg_data VARCHAR2(2000);
67 l_audit_id NUMBER;
68 l_last_fetch BOOLEAN := FALSE ;
69
70
71 BEGIN
72 arp_message.set_line('CS_SR_PARTY_MERGE_PKG.CS_INC_ALL_MERGE_PARTY()+');
73
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76
77 SELECT merge_reason_code
78 INTO l_merge_reason_code
79 FROM hz_merge_batch
80 WHERE batch_id = p_batch_id;
81
82 IF l_merge_reason_code = 'DUPLICATE' THEN
83 -- if reason code is duplicate then allow the party merge to happen without
84 -- any validations.
85 NULL;
86 ELSE
87 -- if there are any validations to be done, include it in this section
88 NULL;
89 END IF;
90
91 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
92 -- needs to be done. Set Merged To Id is same as Merged From Id and return
93
94 IF p_from_fk_id = p_to_fk_id THEN
95 x_to_id := p_from_id;
96 RETURN;
97 END IF;
98
99 -- If the parent has changed(ie. Parent is getting merged) then transfer the
100 -- dependent record to the new parent. Before transferring check if a similar
101 -- dependent record exists on the new parent. If a duplicate exists then do
102 -- not transfer and return the id of the duplicate record as the Merged To Id
103
104 -- In the case of CS_INCIDENTS_ALL_B table, if party id 1000 gets merged to party
105 -- id 2000 then, we have to update all records with customer_id = 1000 to 2000
106
107 IF p_from_fk_id <> p_to_fk_id THEN
108
109 BEGIN
110 -- obtain lock on records to be updated.
111 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
112 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_ALL_B', FALSE);
113
114 OPEN c1;
115 LOOP -- Loop for BULK selecting and processing the BULK selection in a batch of 1000
116
117 FETCH c1 BULK COLLECT INTO l_rowid_tbl,
118 l_incident_id,
119 l_customer_id,
120 l_bill_to_contact_id,
121 l_ship_to_contact_id ,
122 l_bill_to_party_id,
123 l_ship_to_party_id ,
124 l_last_update_program_code
125 LIMIT 1000 ;
126
127 IF c1%NOTFOUND THEN
128 l_last_fetch := TRUE;
129 END IF;
130
131 IF l_rowid_tbl.count = 0 AND l_last_fetch THEN
132 EXIT;
133 END IF ;
134 -- if no records were found to be updated then stop and return to calling prg.
135 -- IF l_rowid_tbl.count = 0 THEN
136 -- RETURN;
137 -- END IF;
138
139 FORALL i IN 1..l_rowid_tbl.COUNT
140 UPDATE cs_incidents_all_b
141 SET customer_id = decode(customer_id, p_from_fk_id,
142 p_to_fk_id, customer_id),
143 bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id,
144 p_to_fk_id, bill_to_contact_id),
145 ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id,
146 p_to_fk_id, ship_to_contact_id),
147 bill_to_party_id = decode(bill_to_party_id, p_from_fk_id,
148 p_to_fk_id, bill_to_party_id),
149 ship_to_party_id = decode(ship_to_party_id, p_from_fk_id,
150 p_to_fk_id, ship_to_party_id),
151 object_version_number = object_version_number + 1,
152 incident_last_modified_date = sysdate ,
153 last_update_program_code = 'PARTY_MERGE',
154 last_update_date = SYSDATE,
155 last_updated_by = G_USER_ID,
156 last_update_login = G_LOGIN_ID
157 WHERE rowid = l_rowid_tbl(i);
158
159 l_count := sql%rowcount;
160
161 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
162 arp_message.set_token('NUM_ROWS', to_char(l_count) );
163
164 FOR i IN 1..l_incident_id.count
165 LOOP
166
167 CS_Servicerequest_UTIL.Prepare_Audit_Record (
168 p_api_version => 1,
169 p_request_id => l_incident_id(i),
170 x_return_status => l_return_status,
171 x_msg_count => x_msg_count,
172 x_msg_data => x_msg_data,
173 x_audit_vals_rec => l_audit_vals_rec );
174
175
176 IF l_return_status <> FND_API.G_RET_STS_ERROR Then
177
178 IF l_audit_vals_rec.customer_id <> l_customer_id(i) THEN
179 l_audit_vals_rec.old_customer_id := l_customer_id(i);
180 END IF;
181 IF l_audit_vals_rec.bill_to_contact_id <> l_bill_to_contact_id(i) THEN
182 l_audit_vals_rec.old_bill_to_contact_id := l_bill_to_contact_id(i);
183 l_audit_vals_rec.change_bill_to_flag := 'Y';
184 END IF;
185 IF l_audit_vals_rec.ship_to_contact_id <> l_ship_to_contact_id(i) THEN
186 l_audit_vals_rec.old_ship_to_contact_id := l_ship_to_contact_id(i);
187 l_audit_vals_rec.change_ship_to_FLAG := 'Y';
188 END IF;
189 IF l_audit_vals_rec.bill_to_party_id <> l_bill_to_party_id(i) THEN
190 l_audit_vals_rec.old_bill_to_party_id := l_bill_to_party_id(i);
191 END IF;
192 IF l_audit_vals_rec.ship_to_party_id <> l_ship_to_party_id(i) THEN
193 l_audit_vals_rec.old_ship_to_party_id := l_ship_to_party_id(i);
194 END IF;
195 l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code(i) ;
196 l_audit_vals_rec.last_update_program_code := 'PARTY_MERGE' ;
197 l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
198 l_audit_vals_rec.updated_entity_id := l_incident_id(i);
199 l_audit_vals_rec.entity_activity_code := 'U' ;
200
201 END IF;
202
203 CS_ServiceRequest_PVT.Create_Audit_Record
204 ( p_api_version => 2.0,
205 x_return_status => l_return_status,
206 x_msg_count => x_msg_count,
207 x_msg_data => x_msg_data,
208 p_request_id => l_incident_id(i),
209 p_audit_id => NULL,
210 p_audit_vals_rec => l_audit_vals_rec,
211 p_user_id => G_USER_ID,
212 p_login_id => G_LOGIN_ID,
213 p_last_update_date => SYSDATE,
214 p_creation_date => SYSDATE,
215 p_comments => NULL,
216 x_audit_id => l_audit_id);
217
218 END LOOP;
219
220 IF l_last_fetch THEN
221 EXIT;
222 END IF ;
223
224 END LOOP ; -- End Loop for BULK selecting and processing the BULK selection in a batch of 1000
225
226 CLOSE c1;
227
228 EXCEPTION
229 WHEN resource_busy THEN
230 arp_message.set_line(g_proc_name || '.' || l_api_name ||
231 '; Could not obtain lock for records in table ' ||
232 'CS_INCIDENTS_ALL_B for customer_id / bill_to_contact_id ' ||
233 '/ ship_to_contact_id / bill_to_party_id / ship_to_part_id = '
234 || p_from_fk_id );
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 raise;
237
238 WHEN others THEN
239 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 RAISE;
242 END;
243
244 END IF; -- if p_from_fk_id <> p_to_fk_id
245
246 END CS_INC_ALL_MERGE_PARTY;
247
248 -- The following procedure will not perform any operations; the update of the
249 -- bill_to_contact_id is done in procedure CS_INC_ALL_MERGE_PARTY
250 PROCEDURE CS_INC_ALL_MERGE_BILL_TO_CONT (
251 p_entity_name IN VARCHAR2,
252 p_from_id IN NUMBER,
253 x_to_id OUT NOCOPY NUMBER,
254 p_from_fk_id IN NUMBER,
255 p_to_fk_id IN NUMBER,
256 p_parent_entity_name IN VARCHAR2,
257 p_batch_id IN NUMBER,
258 p_batch_party_id IN NUMBER,
259 x_return_status OUT NOCOPY VARCHAR2)
260 IS
261 BEGIN
262 x_return_status := FND_API.G_RET_STS_SUCCESS;
263 END CS_INC_ALL_MERGE_BILL_TO_CONT;
264
265 -- The following procedure will not perform any operations; the update of the
266 -- ship_to_contact_id is done in procedure CS_INC_ALL_MERGE_PARTY
267 PROCEDURE CS_INC_ALL_MERGE_SHIP_TO_CONT (
268 p_entity_name IN VARCHAR2,
269 p_from_id IN NUMBER,
270 x_to_id OUT NOCOPY NUMBER,
271 p_from_fk_id IN NUMBER,
272 p_to_fk_id IN NUMBER,
273 p_parent_entity_name IN VARCHAR2,
274 p_batch_id IN NUMBER,
275 p_batch_party_id IN NUMBER,
276 x_return_status OUT NOCOPY VARCHAR2)
277 IS
278 BEGIN
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280 END CS_INC_ALL_MERGE_SHIP_TO_CONT;
281
282
283 -- The following procedure merges CS_INCIDENTS_ALL_B columns:
284 -- bill_to_site_use_id
285 -- ship_to_site_use_id
286 -- The above columns are FKs to HZ_PARTY_SITE_USES.
287 --old proc. PROCEDURE CS_INC_ALL_MERGE_BILL_SITE_USE (
288 PROCEDURE CS_INC_ALL_MERGE_SITE_USES (
289 p_entity_name IN VARCHAR2,
290 p_from_id IN NUMBER,
291 x_to_id OUT NOCOPY NUMBER,
295 p_batch_id IN NUMBER,
292 p_from_fk_id IN NUMBER,
293 p_to_fk_id IN NUMBER,
294 p_parent_entity_name IN VARCHAR2,
296 p_batch_party_id IN NUMBER,
297 x_return_status OUT NOCOPY VARCHAR2)
298 IS
299 -- cursor fetches all the records that need to be merged.
300 CURSOR c1 IS
301 SELECT rowid,incident_id ,
302 bill_to_site_use_id ,
303 ship_to_site_use_id,
304 last_update_program_code
305 FROM cs_incidents_all_b
306 WHERE p_from_fk_id in (bill_to_site_use_id, ship_to_site_use_id)
307 FOR UPDATE NOWAIT;
308
309 l_merge_reason_code VARCHAR2(30);
310 l_api_name VARCHAR2(30) := 'CS_INC_ALL_MERGE_SITE_USES';
311 l_count NUMBER(10) := 0;
312 l_rowid_tbl ROWID_TBL;
313 l_incident_id NUM_TBL;
314 l_bill_to_site_use_id NUM_TBL;
315 l_ship_to_site_use_id NUM_TBL;
316 l_last_update_program_code VARCHAR2_30_TBL;
317 l_last_fetch BOOLEAN := FALSE ;
318 l_audit_id NUMBER;
319 l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
320 l_return_status VARCHAR2(3);
321 x_msg_count NUMBER(15);
322 x_msg_data VARCHAR2(2000);
323 RESOURCE_BUSY EXCEPTION;
324 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
325
326 BEGIN
327
328 arp_message.set_line('CS_SR_PARTY_MERGE_PKG.CS_INC_ALL_MERGE_SITE_USES()+');
329
330 x_return_status := FND_API.G_RET_STS_SUCCESS;
331
332 SELECT merge_reason_code
333 INTO l_merge_reason_code
334 FROM hz_merge_batch
335 WHERE batch_id = p_batch_id;
336
337 IF l_merge_reason_code = 'DUPLICATE' then
338 -- if reason code is duplicate then allow the party merge to happen without
339 -- any validations.
340 null;
341 ELSE
342 -- if there are any validations to be done, include it in this section
343 null;
344 END IF;
345
346 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
347 -- needs to be done. Set Merged To Id is same as Merged From Id and return
348
349 IF p_from_fk_id = p_to_fk_id THEN
350 x_to_id := p_from_id;
351 RETURN;
352 END IF;
353
354 -- If the parent has changed(ie. Parent is getting merged) then transfer the
355 -- dependent record to the new parent. Before transferring check if a similar
356 -- dependent record exists on the new parent. If a duplicate exists then do
357 -- not transfer and return the id of the duplicate record as the Merged To Id
358
359 -- In the case of CS_INCIDENTS_ALL_B table, we store bill_to_site_use_id which is a forign key to
360 -- to HZ_PARTY_SITE_USES.PARTY_SITE_USE_ID. If the party who is tied to this site has been merged,
361 -- then, it is possible that this site use id is being transferred under the new party or it
362 -- may have been deleted if its a duplicate party_site_use_id
363
364 IF p_from_fk_id <> p_to_fk_id THEN
365 BEGIN
366 -- obtain lock on records to be updated.
367 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
368 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_ALL_B', FALSE);
369
370 OPEN c1;
371 LOOP
372 FETCH c1 bulk collect
373 INTO l_rowid_tbl,
374 l_incident_id ,
375 l_bill_to_site_use_id ,
376 l_ship_to_site_use_id ,
377 l_last_update_program_code
378 LIMIT 1000;
379
380 IF c1%NOTFOUND THEN
381 l_last_fetch := TRUE ;
382 END IF ;
383
384 IF l_rowid_tbl.COUNT = 0 AND l_last_fetch THEN
385 EXIT ;
386 END IF ;
387
388 IF l_rowid_tbl.count = 0 THEN
389 RETURN;
390 END IF;
391
392 FORALL i IN 1..l_rowid_tbl.COUNT
393 UPDATE cs_incidents_all_b
394 SET bill_to_site_use_id = decode(bill_to_site_use_id, p_from_fk_id, p_to_fk_id,
395 bill_to_site_use_id ),
396 ship_to_site_use_id = decode(ship_to_site_use_id, p_from_fk_id, p_to_fk_id,
397 ship_to_site_use_id ),
398 object_version_number = object_version_number + 1,
399 incident_last_modified_date = sysdate,
400 last_update_program_code = 'PARTY_MERGE',
401 last_update_date = SYSDATE,
402 last_updated_by = G_USER_ID,
403 last_update_login = G_LOGIN_ID
404 WHERE rowid = l_rowid_tbl(i);
405
406 l_count := SQL%ROWCOUNT;
407
408 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
409 arp_message.set_token('NUM_ROWS', to_char(l_count) );
410
411
412 -- Create an audit record for each service request in cs_incidents_audit_b table
413 -- for which the bill_to_site_use_id or ship_to_site_use_id is updated
414
415 FOR i IN 1..l_incident_id.COUNT
416 LOOP
417 CS_Servicerequest_UTIL.Prepare_Audit_Record (
421 x_msg_count => x_msg_count,
418 p_api_version => 1,
419 p_request_id => l_incident_id(i),
420 x_return_status => l_return_status,
422 x_msg_data => x_msg_data,
423 x_audit_vals_rec => l_audit_vals_rec );
424
425
426 IF l_return_status <> FND_API.G_RET_STS_ERROR Then
427
428 IF l_bill_to_site_use_id(i) = p_from_fk_id THEN
429 l_audit_vals_rec.bill_to_site_use_id := p_to_fk_id ;
430 l_audit_vals_rec.old_bill_to_site_use_id := l_bill_to_site_use_id(i);
431 ELSE
432 l_audit_vals_rec.bill_to_site_use_id := l_bill_to_site_use_id(i);
433 l_audit_vals_rec.old_bill_to_site_use_id := l_bill_to_site_use_id(i);
434 END IF;
435
436 IF l_ship_to_site_use_id(i) = p_from_fk_id THEN
437 l_audit_vals_rec.ship_to_site_use_id := p_to_fk_id ;
438 l_audit_vals_rec.old_ship_to_site_use_id := l_ship_to_site_use_id(i);
439 ELSE
440 l_audit_vals_rec.ship_to_site_use_id := l_ship_to_site_use_id(i);
441 l_audit_vals_rec.old_ship_to_site_use_id := l_ship_to_site_use_id(i);
442 END IF;
443
444
445 l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code(i) ;
446 l_audit_vals_rec.last_update_program_code := 'PARTY_MERGE' ;
447 l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
448 l_audit_vals_rec.updated_entity_id := l_incident_id(i);
449 l_audit_vals_rec.entity_activity_code := 'U' ;
450
451 END IF;
452
453 CS_ServiceRequest_PVT.Create_Audit_Record
454 ( p_api_version => 2.0,
455 x_return_status => l_return_status,
456 x_msg_count => x_msg_count,
457 x_msg_data => x_msg_data,
458 p_request_id => l_incident_id(i),
459 p_audit_id => NULL,
460 p_audit_vals_rec => l_audit_vals_rec,
461 p_user_id => G_USER_ID,
462 p_login_id => G_LOGIN_ID,
463 p_last_update_date => SYSDATE,
464 p_creation_date => SYSDATE,
465 p_comments => NULL,
466 x_audit_id => l_audit_id);
467
468 END LOOP;
469
470 IF l_last_fetch THEN
471 EXIT;
472 END IF ;
473
474 END LOOP ; -- End Loop for BULK selecting and processing the BULK selection in a batch of 1000
475
476 CLOSE c1;
477
478 EXCEPTION
479 WHEN resource_busy THEN
480 arp_message.set_line(g_proc_name || '.' || l_api_name ||
481 '; Could not obtain lock for records in table ' ||
482 'CS_INCIDENTS_ALL_B for bill_to_site_use_id / ship_to_site_use_id / '
483 || p_from_fk_id );
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 RAISE;
486
487 WHEN others THEN
488 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 RAISE;
491 END;
492 END IF;
493
494 END CS_INC_ALL_MERGE_SITE_USES;
498 PROCEDURE CS_INC_ALL_MERGE_INST_SITE_USE (
495
496 -- The following procedure will not perform any operations; the update of the
497 -- install_site_use_id is done in procedure CS_INC_ALL_MERGE_SITE_USES
499 p_entity_name IN VARCHAR2,
500 p_from_id IN NUMBER,
501 x_to_id OUT NOCOPY NUMBER,
502 p_from_fk_id IN NUMBER,
503 p_to_fk_id IN NUMBER,
504 p_parent_entity_name IN VARCHAR2,
505 p_batch_id IN NUMBER,
506 p_batch_party_id IN NUMBER,
507 x_return_status OUT NOCOPY VARCHAR2)
508 IS
509 BEGIN
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511 END CS_INC_ALL_MERGE_INST_SITE_USE;
512
513 -- The following procedure will not perform any operations; the update of the
514 -- ship_to_site_use_id is done in procedure CS_INC_ALL_MERGE_SITE_USES
515 PROCEDURE CS_INC_ALL_MERGE_SHIP_SITE_USE(
516 p_entity_name IN VARCHAR2,
517 p_from_id IN NUMBER,
518 x_to_id OUT NOCOPY NUMBER,
519 p_from_fk_id IN NUMBER,
520 p_to_fk_id IN NUMBER,
521 p_parent_entity_name IN VARCHAR2,
522 p_batch_id IN NUMBER,
523 p_batch_party_id IN NUMBER,
524 x_return_status OUT NOCOPY VARCHAR2)
525 IS
526 BEGIN
527 x_return_status := FND_API.G_RET_STS_SUCCESS;
528 END CS_INC_ALL_MERGE_SHIP_SITE_USE;
529
530
531 -- The following procedure merges CS_INCIDENTS_ALL_B columns:
532 -- site_id
533 -- customer_site_id
534 -- bill_to_site_id - added from 11.5.9
535 -- ship_to_site_id - added from 11.5.9
536 -- install_site_id - added from 11.5.9
537 -- install_site_use_id - moved from proc. CS_INC_ALL_MERGE_SITE_USES
538 -- The above columns are FKs to HZ_PARTY_SITES.
539 PROCEDURE CS_INC_ALL_MERGE_SITE_ID (
540 p_entity_name IN VARCHAR2,
541 p_from_id IN NUMBER,
542 x_to_id OUT NOCOPY NUMBER,
543 p_from_fk_id IN NUMBER,
544 p_to_fk_id IN NUMBER,
545 p_parent_entity_name IN VARCHAR2,
546 p_batch_id IN NUMBER,
547 p_batch_party_id IN NUMBER,
548 x_return_status OUT NOCOPY VARCHAR2)
549 IS
550 -- cursor fetches all the records that need to be merged.
551 CURSOR C1 IS
552 SELECT rowid,
553 incident_id ,
554 site_id,
555 customer_site_id,
556 install_site_use_id,
557 bill_to_site_id,
558 ship_to_site_id,
559 install_site_id,
560 incident_location_id ,
561 incident_location_type,
562 last_update_program_code
563 FROM cs_incidents_all_b
564 WHERE p_from_fk_id IN (site_id, customer_site_id, install_site_use_id,
565 bill_to_site_id, ship_to_site_id, install_site_id)
566 OR (incident_location_type = 'HZ_PARTY_SITE' AND incident_location_id = p_from_fk_id)
567 FOR UPDATE NOWAIT;
568
569 -- CURSOR c2 IS -- This cursor is not required since the audit record creation id done using main cursor.
570 -- SELECT incident_id , site_id , last_update_program_code
571 -- FROM cs_incidents_all_b
572 -- WHERE site_id = p_from_fk_id ;
573
574 l_merge_reason_code VARCHAR2(30);
575 l_api_name VARCHAR2(30) := 'CS_INC_ALL_MERGE_SITE_ID';
576 l_count NUMBER(10) := 0;
577
578 l_rowid_tbl ROWID_TBL;
579 l_incident_id NUM_TBL ;
580 l_site_id NUM_TBL ;
581 l_customer_site_id NUM_TBL ;
582 l_install_site_use_id NUM_TBL ;
583 l_bill_to_site_id NUM_TBL ;
584 l_ship_to_site_id NUM_TBL ;
585 l_install_site_id NUM_TBL ;
586 l_incident_location_id NUM_TBL ;
587 l_incident_location_type VARCHAR2_30_TBl ;
588 l_last_update_program_code VARCHAR2_30_TBL ;
589 l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
590 l_return_status VARCHAR2(3);
591 x_msg_count NUMBER;
592 x_msg_data VARCHAR2(2000);
593 l_audit_id NUMBER;
594 l_last_fetch BOOLEAN := FALSE ;
595
596
597 RESOURCE_BUSY EXCEPTION;
598 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
599
600 BEGIN
601 arp_message.set_line('CS_SR_PARTY_MERGE_PKG.CS_INC_ALL_MERGE_SITE_ID()+');
602
603 x_return_status := FND_API.G_RET_STS_SUCCESS;
604
605 SELECT merge_reason_code
606 INTO l_merge_reason_code
607 FROM hz_merge_batch
608 WHERE batch_id = p_batch_id;
609
610 IF l_merge_reason_code = 'DUPLICATE' THEN
611 -- if reason code is duplicate then allow the party merge to happen without
612 -- any validations.
613 null;
614 ELSE
615 -- if there are any validations to be done, include it in this section
616 null;
617 END IF;
618
619 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
620 -- needs to be done. Set Merged To Id is same as Merged From Id and return
621
625 END IF;
622 IF p_from_fk_id = p_to_fk_id THEN
623 x_to_id := p_from_id;
624 RETURN;
626
627 -- If the parent has changed(ie. Parent is getting merged) then transfer the
628 -- dependent record to the new parent. Before transferring check if a similar
629 -- dependent record exists on the new parent. If a duplicate exists then do
630 -- not transfer and return the id of the duplicate record as the Merged To Id
631
632
633 -- In the case of CS_INCIDENTS_ALL_B table, we store install_site_use_id which is a forign key to
634 -- to HZ_PARTY_SITES.PARTY_SITE_ID. If the party who is tied to this site has been merged,
635 -- then, it is possible that this site use id is being transferred under the new party or it
636 -- may have been deleted if its a duplicate party_site_use_id
637
638
639 IF p_from_fk_id <> p_to_fk_id THEN
640 BEGIN
641 -- obtain lock on records to be updated.
642 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
643 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_ALL_B', FALSE);
644
645 OPEN C1;
646 LOOP -- Bulk fetch loop for 1000 records
647 FETCH C1 BULK COLLECT
648 INTO l_rowid_tbl,
649 l_incident_id ,
650 l_site_id,
651 l_customer_site_id,
652 l_install_site_use_id,
653 l_bill_to_site_id,
654 l_ship_to_site_id,
655 l_install_site_id,
656 l_incident_location_id ,
657 l_incident_location_type ,
658 l_last_update_program_code
659 LIMIT 1000 ;
660
661 IF c1%NOTFOUND THEN
662 l_last_fetch := TRUE ;
663 END IF ;
664
665 IF l_rowid_tbl.COUNT = 0 AND l_last_fetch THEN
666 EXIT ;
667 END IF ;
668
669 -- IF l_rowid_tbl.COUNT = 0 THEN
670 -- RETURN;
671 -- END IF;
672
673 -- OPEN c2;
674 -- FETCH c2 BULK COLLECT INTO l_incident_id ,
675 -- l_site_id ,
676 -- l_last_update_program_code ;
677 -- CLOSE c2;
678
679
680 FORALL i IN 1..l_rowid_tbl.COUNT
681 UPDATE cs_incidents_all_b
682 SET site_id = decode(site_id, p_from_fk_id, p_to_fk_id, site_id),
683 customer_site_id = decode(customer_site_id, p_from_fk_id, p_to_fk_id,
684 customer_site_id),
685 bill_to_site_id = decode(bill_to_site_id , p_from_fk_id, p_to_fk_id,
686 bill_to_site_id ),
687 ship_to_site_id = decode(ship_to_site_id , p_from_fk_id, p_to_fk_id,
688 ship_to_site_id ),
689 install_site_id = decode(install_site_id , p_from_fk_id, p_to_fk_id,
690 install_site_id ),
691 install_site_use_id = decode(install_site_use_id, p_from_fk_id, p_to_fk_id,
692 install_site_use_id ),
693 incident_location_id = Decode(NVL(incident_location_type,'XXX') ,
694 'HZ_LOCATION',incident_location_id ,
695 'HZ_PARTY_SITE',decode(incident_location_id,
696 p_from_fk_id,p_to_fk_id,incident_location_id),
697 'XXX',incident_location_id
698 ),
699 incident_last_modified_date = sysdate ,
700 object_version_number = object_version_number + 1,
701 last_update_program_code = 'PARTY_MERGE',
702 last_update_date = SYSDATE,
703 last_updated_by = G_USER_ID,
704 last_update_login = G_LOGIN_ID
705 WHERE rowid = l_rowid_tbl(i);
706
707 l_count := sql%rowcount;
708
709 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
710 arp_message.set_token('NUM_ROWS', to_char(l_count) );
711
712 -- create audit record in cs_incidents_audit_b table for each service
713 -- request for which site_id is updated.
714
715 FOR i IN 1..l_incident_id.COUNT
716 LOOP
717
718 CS_Servicerequest_UTIL.Prepare_Audit_Record (
719 p_api_version => 1,
720 p_request_id => l_incident_id(i),
721 x_return_status => x_return_status,
722 x_msg_count => x_msg_count,
723 x_msg_data => x_msg_data,
724 x_audit_vals_rec => l_audit_vals_rec );
725
726 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
727
728 -- set the site_id/old_site_id of audit record
729
730 IF l_site_id(i) = p_from_fk_id THEN
731 l_audit_vals_rec.site_id := p_to_fk_id ;
732 l_audit_vals_rec.old_site_id := l_site_id(i);
733 ELSE
734 l_audit_vals_rec.site_id := l_site_id(i);
735 l_audit_vals_rec.old_site_id := l_site_id(i);
736 END IF;
737
738
739 -- set the customer_site_id/old_customer_site_id of audit record
743 l_audit_vals_rec.old_customer_site_id := l_customer_site_id(i);
740
741 IF l_customer_site_id(i) = p_from_fk_id THEN
742 l_audit_vals_rec.customer_site_id := p_to_fk_id ;
744 ELSE
745 l_audit_vals_rec.customer_site_id := l_customer_site_id(i);
746 l_audit_vals_rec.old_customer_site_id := l_customer_site_id(i);
747 END IF;
748
749 -- set the install_site_id/old_install_site_id of audit record
750
751 IF l_install_site_id(i) = p_from_fk_id THEN
752 l_audit_vals_rec.install_site_id := p_to_fk_id ;
753 l_audit_vals_rec.old_install_site_id := l_install_site_id(i);
754 ELSE
755 l_audit_vals_rec.install_site_id := l_install_site_id(i);
756 l_audit_vals_rec.old_install_site_id := l_install_site_id(i);
757 END IF;
758
759 -- set the bill_to_site_id/old_bill_to_site_id of audit record
760
761 IF l_bill_to_site_id(i) = p_from_fk_id THEN
762 l_audit_vals_rec.bill_to_site_id := p_to_fk_id ;
763 l_audit_vals_rec.old_bill_to_site_id := l_bill_to_site_id(i);
764 ELSE
765 l_audit_vals_rec.bill_to_site_id := l_bill_to_site_id(i);
766 l_audit_vals_rec.old_bill_to_site_id := l_bill_to_site_id(i);
767 END IF;
768
769 -- set the ship_to_site_id/old_ship_to_site_id of audit record
770
771 IF l_ship_to_site_id(i) = p_from_fk_id THEN
772 l_audit_vals_rec.ship_to_site_id := p_to_fk_id ;
773 l_audit_vals_rec.old_ship_to_site_id := l_ship_to_site_id(i);
774 ELSE
775 l_audit_vals_rec.ship_to_site_id := l_ship_to_site_id(i);
776 l_audit_vals_rec.old_ship_to_site_id := l_ship_to_site_id(i);
777 END IF;
778
779 -- set the site_id/old_site_id of audit record
780
781 IF l_install_site_use_id(i) = p_from_fk_id THEN
782 l_audit_vals_rec.install_site_use_id := p_to_fk_id ;
783 l_audit_vals_rec.old_install_site_use_id := l_install_site_use_id(i);
784 ELSE
785 l_audit_vals_rec.install_site_use_id := l_install_site_use_id(i);
786 l_audit_vals_rec.old_install_site_use_id := l_install_site_use_id(i);
787 END IF;
788
789 -- set the site_id/old_site_id of audit record
790
791 IF (NVL(l_incident_location_type(i),'XXX') = 'HZ_PARTY_SITE' AND l_incident_location_id(i) = p_from_fk_id) THEN
792 l_audit_vals_rec.incident_location_id := p_to_fk_id ;
793 l_audit_vals_rec.old_incident_location_id := l_incident_location_id(i);
794 ELSE
795 l_audit_vals_rec.incident_location_id := l_incident_location_id(i);
796 l_audit_vals_rec.old_incident_location_id := l_incident_location_id(i);
797 END IF;
798 -- set the last_program_code/old_last_progream_code of audit record
799
800 l_audit_vals_rec. last_update_program_code := 'PARTY_MERGE' ;
801 l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code (i);
802 l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
803 l_audit_vals_rec.updated_entity_id := l_incident_id(i);
804 l_audit_vals_rec.entity_activity_code := 'U';
805
806 END IF;
807
808 CS_ServiceRequest_PVT.Create_Audit_Record (
809 p_api_version => 2.0,
810 x_return_status => x_return_status,
811 x_msg_count => x_msg_count,
812 x_msg_data => x_msg_data,
813 p_request_id => l_incident_id(i),
814 p_audit_id => NULL,
815 p_audit_vals_rec => l_audit_vals_rec ,
816 p_user_id => G_USER_ID,
817 p_login_id => G_LOGIN_ID,
818 p_last_update_date => SYSDATE,
819 p_creation_date => SYSDATE,
820 p_comments => NULL,
821 x_audit_id => l_audit_id);
822
823 END LOOP;
824
825 IF l_last_fetch THEN
826 EXIT ;
827 END IF ;
828
829 END LOOP ; -- Bulk fetch end loop
830
831 CLOSE c1 ;
832
833 EXCEPTION
834 WHEN resource_busy THEN
835 arp_message.set_line(g_proc_name || '.' || l_api_name ||
836 '; Could not obtain lock for records in table ' ||
837 'CS_INCIDENTS_ALL_B for site_id / customer_site_id / ' ||
838 'install_site_use_id / bill_to_site_id / ship_to_site_id / '||
839 'install_site_id = ' || p_from_fk_id );
840 x_return_status := FND_API.G_RET_STS_ERROR;
841 RAISE;
842
843 WHEN others THEN
844 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
845 x_return_status := FND_API.G_RET_STS_ERROR;
846 RAISE;
847 END;
848 END IF;
849 END CS_INC_ALL_MERGE_SITE_ID;
850
851 -- The following procedure will not perform any operations; the update of the
852 -- customer_id is done in procedure CS_INC_ALL_MERGE_SITE_ID
853 PROCEDURE CS_INC_ALL_MERGE_CT_SITE_ID (
857 p_from_fk_id IN NUMBER,
854 p_entity_name IN VARCHAR2,
855 p_from_id IN NUMBER,
856 x_to_id OUT NOCOPY NUMBER,
858 p_to_fk_id IN NUMBER,
859 p_parent_entity_name IN VARCHAR2,
860 p_batch_id IN NUMBER,
861 p_batch_party_id IN NUMBER,
862 x_return_status OUT NOCOPY VARCHAR2)
863 IS
864
865 BEGIN
866 x_return_status := FND_API.G_RET_STS_SUCCESS;
867 END CS_INC_ALL_MERGE_CT_SITE_ID;
868
869 -- The following procedure merges CS_INCIDENT_AUDIT_B columns:
870 -- bill_to_contact_id
871 -- ship_to_contact_id
872 -- old_bill_to_contact_id
873 -- old_ship_to_contact_id
874 -- The above columns are FKs to HZ_PARTIES.
875 PROCEDURE CS_AUDIT_MERGE_PARTY(
876 p_entity_name IN VARCHAR2,
877 p_from_id IN NUMBER,
878 x_to_id OUT NOCOPY NUMBER,
879 p_from_fk_id IN NUMBER,
880 p_to_fk_id IN NUMBER,
881 p_parent_entity_name IN VARCHAR2,
882 p_batch_id IN NUMBER,
883 p_batch_party_id IN NUMBER,
884 x_return_status OUT NOCOPY VARCHAR2)
885 IS
886 cursor c1 is
887 select rowid
888 from cs_incidents_audit_b
889 where p_from_fk_id in (bill_to_contact_id, ship_to_contact_id, old_bill_to_contact_id,
890 old_ship_to_contact_id)
891 for update nowait;
892
893 l_rowid_tbl ROWID_TBL;
894
895 l_merge_reason_code VARCHAR2(30);
896 l_api_name VARCHAR2(30) := 'CS_AUDIT_MERGE_PARTY';
897 l_count NUMBER(10) := 0;
898
899 RESOURCE_BUSY EXCEPTION;
900 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
901
902 BEGIN
903 arp_message.set_line('CS_SR_PARTY_MERGE_PKG.CS_AUDIT_MERGE_PARTY()+');
904
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906
907 select merge_reason_code
908 into l_merge_reason_code
909 from hz_merge_batch
910 where batch_id = p_batch_id;
911
912 if l_merge_reason_code = 'DUPLICATE' then
913 -- if reason code is duplicate then allow the party merge to happen without
914 -- any validations.
915 null;
916 else
917 -- if there are any validations to be done, include it in this section
918 null;
919 end if;
920
921 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
922 -- needs to be done. Set Merged To Id is same as Merged From Id and return
923
924 if p_from_fk_id = p_to_fk_id then
925 x_to_id := p_from_id;
926 return;
927 end if;
928
929 -- If the parent has changed(ie. Parent is getting merged) then transfer the
930 -- dependent record to the new parent. Before transferring check if a similar
931 -- dependent record exists on the new parent. If a duplicate exists then do
932 -- not transfer and return the id of the duplicate record as the Merged To Id
933
934 -- In the case of CS_INCIDENTS_ALL_B table, if party id 1000 gets merged to party
935 -- id 2000 then, we have to update all records with bill_to_contact_id = 1000 to 2000
936
937 if p_from_fk_id <> p_to_fk_id then
938 begin
939 -- obtain lock on records to be updated.
940 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
941 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_AUDIT_B', FALSE);
942
943 open c1;
944 fetch c1 bulk collect into l_rowid_tbl;
945 close c1;
946
947 if l_rowid_tbl.count = 0 then
948 RETURN;
949 end if;
950
951 forall i in 1.. l_rowid_tbl.count
952 update cs_incidents_audit_b
953 set bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id, p_to_fk_id,
954 bill_to_contact_id ),
955 old_bill_to_contact_id= decode(old_bill_to_contact_id, p_from_fk_id, p_to_fk_id,
956 old_bill_to_contact_id ),
957 ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id, p_to_fk_id,
958 ship_to_contact_id ),
959 old_ship_to_contact_id= decode(old_ship_to_contact_id, p_from_fk_id, p_to_fk_id,
960 old_ship_to_contact_id ),
961 object_version_number = object_version_number + 1,
962 last_update_date = SYSDATE,
963 last_updated_by = G_USER_ID,
964 last_update_login = G_LOGIN_ID
965 where rowid = l_rowid_tbl(i);
966
967 l_count := sql%rowcount;
968
969 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
970 arp_message.set_token('NUM_ROWS', to_char(l_count) );
971
972 exception
973 when resource_busy then
974 arp_message.set_line(g_proc_name || '.' || l_api_name ||
975 '; Could not obtain lock for records in table ' ||
976 'CS_INCIDENTS_AUDIT_B for bill_to_contact_id = ' || p_from_fk_id );
977
978 x_return_status := FND_API.G_RET_STS_ERROR;
979 raise;
980
981 when others then
982 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 raise;
988
985 end;
986 end if;
987 END CS_AUDIT_MERGE_PARTY;
989
990 -- The following procedure will not perform any operations; the update of the
991 -- old_bill_to_contact_id is done in procedure CS_AUDIT_MERGE_PARTY
992 PROCEDURE CS_AUDIT_MERGE_OLD_BILL_CONT(
993 p_entity_name IN VARCHAR2,
994 p_from_id IN NUMBER,
995 x_to_id OUT NOCOPY NUMBER,
996 p_from_fk_id IN NUMBER,
997 p_to_fk_id IN NUMBER,
998 p_parent_entity_name IN VARCHAR2,
999 p_batch_id IN NUMBER,
1000 p_batch_party_id IN NUMBER,
1001 x_return_status OUT NOCOPY VARCHAR2)
1002 IS
1003 BEGIN
1004 x_return_status := FND_API.G_RET_STS_SUCCESS;
1005 END CS_AUDIT_MERGE_OLD_BILL_CONT;
1006
1007 -- The following procedure will not perform any operations; the update of the
1008 -- ship_to_contact_id is done in procedure CS_AUDIT_MERGE_PARTY
1009 PROCEDURE CS_AUDIT_MERGE_SHIP_TO_CONT(
1010 p_entity_name IN VARCHAR2,
1011 p_from_id IN NUMBER,
1012 x_to_id OUT NOCOPY NUMBER,
1013 p_from_fk_id IN NUMBER,
1014 p_to_fk_id IN NUMBER,
1015 p_parent_entity_name IN VARCHAR2,
1016 p_batch_id IN NUMBER,
1017 p_batch_party_id IN NUMBER,
1018 x_return_status OUT NOCOPY VARCHAR2)
1019 IS
1020 BEGIN
1021 x_return_status := FND_API.G_RET_STS_SUCCESS;
1022 END CS_AUDIT_MERGE_SHIP_TO_CONT;
1023
1024 -- The following procedure will not perform any operations; the update of the
1025 -- old_ship_to_contact_id is done in procedure CS_AUDIT_MERGE_PARTY
1026 PROCEDURE CS_AUDIT_MERGE_OLD_SHIP_CONT(
1027 p_entity_name IN VARCHAR2,
1028 p_from_id IN NUMBER,
1029 x_to_id OUT NOCOPY NUMBER,
1030 p_from_fk_id IN NUMBER,
1031 p_to_fk_id IN NUMBER,
1032 p_parent_entity_name IN VARCHAR2,
1033 p_batch_id IN NUMBER,
1034 p_batch_party_id IN NUMBER,
1035 x_return_status OUT NOCOPY VARCHAR2)
1036 IS
1037 BEGIN
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039 END CS_AUDIT_MERGE_OLD_SHIP_CONT;
1040
1041
1042 PROCEDURE CS_AUDIT_MERGE_SITE_ID(
1043 p_entity_name IN VARCHAR2,
1044 p_from_id IN NUMBER,
1045 x_to_id OUT NOCOPY NUMBER,
1046 p_from_fk_id IN NUMBER,
1047 p_to_fk_id IN NUMBER,
1048 p_parent_entity_name IN VARCHAR2,
1049 p_batch_id IN NUMBER,
1050 p_batch_party_id IN NUMBER,
1051 x_return_status OUT NOCOPY VARCHAR2)
1052 IS
1053 -- cursor fetches all the records that need to be merged.
1054 cursor c1 is
1055 select rowid
1056 from cs_incidents_audit_b
1057 where p_from_fk_id in (site_id, old_site_id)
1058 for update nowait;
1059
1060 l_rowid_tbl ROWID_TBL;
1061
1062 l_merge_reason_code VARCHAR2(30);
1063 l_api_name VARCHAR2(30) := 'CS_AUDIT_MERGE_SITE_ID';
1064 l_count NUMBER(10) := 0;
1065
1066 RESOURCE_BUSY EXCEPTION;
1067 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1068
1069 BEGIN
1070 arp_message.set_line('CS_SR_PARTY_MERGE_PKG.CS_AUDIT_MERGE_SITE_ID()+');
1071
1072 x_return_status := FND_API.G_RET_STS_SUCCESS;
1073
1074 select merge_reason_code
1075 into l_merge_reason_code
1076 from hz_merge_batch
1077 where batch_id = p_batch_id;
1078
1079 if l_merge_reason_code = 'DUPLICATE' then
1080 -- if reason code is duplicate then allow the party merge to happen without
1081 -- any validations.
1082 null;
1083 else
1084 -- if there are any validations to be done, include it in this section
1085 null;
1086 end if;
1087
1088 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1089 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1090
1091 if p_from_fk_id = p_to_fk_id then
1092 x_to_id := p_from_id;
1093 return;
1094 end if;
1095
1096 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1097 -- dependent record to the new parent. Before transferring check if a similar
1098 -- dependent record exists on the new parent. If a duplicate exists then do
1099 -- not transfer and return the id of the duplicate record as the Merged To Id
1100
1101 -- In the case of CS_INCIDENTS_ALL_B table, if site id 1000 gets merged to site
1102 -- id 2000 then, we have to update all records with site_id = 1000 to 2000
1103
1104 if p_from_fk_id <> p_to_fk_id then
1105 begin
1106 -- obtain lock on records to be updated.
1107 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1108 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_AUDIT_B', FALSE);
1109
1110 open c1;
1111 fetch c1 bulk collect into l_rowid_tbl;
1112 close c1;
1113
1114 if l_rowid_tbl.count = 0 then
1115 RETURN;
1116 end if;
1120 set site_id = decode(site_id, p_from_fk_id, p_to_fk_id,
1117
1118 forall i in 1..l_rowid_tbl.count
1119 update cs_incidents_audit_b
1121 site_id),
1122 old_site_id = decode(old_site_id, p_from_fk_id, p_to_fk_id,
1123 old_site_id),
1124 object_version_number = object_version_number + 1,
1125 last_update_date = SYSDATE,
1126 last_updated_by = G_USER_ID,
1127 last_update_login = G_LOGIN_ID
1128 where rowid = l_rowid_tbl(i);
1129
1130 l_count := sql%rowcount;
1131
1132 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1133 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1134
1135 exception
1136 when resource_busy then
1137 arp_message.set_line(g_proc_name || '.' || l_api_name ||
1138 '; Could not obtain lock for records in table ' ||
1139 'CS_INCIDENTS_AUDIT_B for site_id = ' || p_from_fk_id );
1140
1141 x_return_status := FND_API.G_RET_STS_ERROR;
1142 raise;
1143
1144 when others then
1145 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
1146 x_return_status := FND_API.G_RET_STS_ERROR;
1147 raise;
1148 end;
1149 end if;
1150 END CS_AUDIT_MERGE_SITE_ID;
1151
1152 -- The following procedure will not perform any operations; the update of the
1153 -- old_site_id is done in procedure CS_AUDIT_MERGE_SITE_ID
1154 PROCEDURE CS_AUDIT_MERGE_OLD_SITE_ID(
1155 p_entity_name IN VARCHAR2,
1156 p_from_id IN NUMBER,
1157 x_to_id OUT NOCOPY NUMBER,
1158 p_from_fk_id IN NUMBER,
1159 p_to_fk_id IN NUMBER,
1160 p_parent_entity_name IN VARCHAR2,
1161 p_batch_id IN NUMBER,
1162 p_batch_party_id IN NUMBER,
1163 x_return_status OUT NOCOPY VARCHAR2)
1164 IS
1165 BEGIN
1166 x_return_status := FND_API.G_RET_STS_SUCCESS;
1167 END CS_AUDIT_MERGE_OLD_SITE_ID;
1168
1169
1170 PROCEDURE CS_CONTACTS_MERGE_PARTY(
1171 p_entity_name IN VARCHAR2,
1172 p_from_id IN NUMBER,
1173 x_to_id OUT NOCOPY NUMBER,
1174 p_from_fk_id IN NUMBER,
1175 p_to_fk_id IN NUMBER,
1176 p_parent_entity_name IN VARCHAR2,
1177 p_batch_id IN NUMBER,
1178 p_batch_party_id IN NUMBER,
1179 x_return_status OUT NOCOPY VARCHAR2)
1180 IS
1181 cursor c1 is
1182 select 1
1183 from cs_hz_sr_contact_points
1184 where sr_contact_point_id = p_from_id
1185 and party_id = p_from_fk_id
1186 and contact_type <> 'EMPLOYEE'
1187 for update nowait;
1188
1189 l_merge_reason_code VARCHAR2(30);
1190 l_api_name VARCHAR2(30) := 'CS_CONTACTS_MERGE_PARTY';
1191 l_count NUMBER(10) := 0;
1192 l_incident_id NUM_TBL;
1193 l_sr_contact_point_id NUM_TBL;
1194 v_merged_to_id NUMBER;
1195 l_primary_flag VARCHAR2(1);
1196 l_audit_id NUMBER;
1197 l_return_status VARCHAR2(3);
1198 x_msg_count NUMBER(15);
1199 x_msg_data VARCHAR2(2000);
1200 l_sr_contact_old_rec CS_SERVICEREQUEST_PVT.CONTACTS_REC;
1201 l_sr_contact_new_rec CS_SERVICEREQUEST_PVT.CONTACTS_REC;
1202
1203 RESOURCE_BUSY EXCEPTION;
1204 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1205
1206 BEGIN
1207 arp_message.set_line('CS_PARTY_MERGE_PKG.CS_CONTACTS_MERGE_PARTY()+');
1208
1209 x_return_status := FND_API.G_RET_STS_SUCCESS;
1210
1211 ---dbms_output.put_line('am going to get reason code');
1212
1213 select merge_reason_code
1214 into l_merge_reason_code
1215 from hz_merge_batch
1216 where batch_id = p_batch_id;
1217
1218 ---l_merge_reason_code := null;
1219 --dbms_output.put_line('got reason code');
1220
1221 if l_merge_reason_code = 'DUPLICATE' then
1222 -- if reason code is duplicate then allow the party merge to happen without
1223 -- any validations.
1224 null;
1225 else
1226 -- if there are any validations to be done, include it in this section
1227 null;
1228 end if;
1229
1230 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1231 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1232
1233 if p_from_fk_id = p_to_fk_id then
1234 x_to_id := p_from_id;
1235 return;
1236 end if;
1237
1238 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1239 -- dependent record to the new parent. Before transferring check if a similar
1240 -- dependent record exists on the new parent. If a duplicate exists then do
1241 -- not transfer and return the id of the duplicate record as the Merged To Id
1242
1243
1244 -- In the case of CS_HZ_SR_CONTACT_POINTS table, if party id 1000 gets merged to
1245 -- party id 2000 then, we have to update all records with customer_id = 1000 to 2000
1246
1247 if p_from_fk_id <> p_to_fk_id then
1248 begin
1249 -- obtain lock on records to be updated.
1253 open c1;
1250 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1251 arp_message.set_token('TABLE_NAME', 'CS_HZ_SR_CONTACT_POINTS', FALSE);
1252
1254 close c1;
1255
1256
1257 BEGIN
1258 --Before updating the record with the new party id, check if it will result in a duplicate
1259 --record in cs_hz_sr_contact_points
1260 --If so, need to set one of them to DUPLICATE
1261
1262 --dbms_output.put_line('getting v_merged_to_id');
1263
1264 --V_MERGED_TO_ID will have the SR_CONTACT_POINT_ID which results in a duplicate record
1265 --
1266 select sr_contact_point_id INTO v_merged_to_id
1267 from cs_hz_sr_contact_points
1268 where party_id = p_to_fk_id
1269 and ( contact_point_id, incident_id ) = ( select contact_point_id, incident_id
1270 from cs_hz_sr_contact_points
1271 where sr_contact_point_id = p_from_id )
1272 and sr_contact_point_id <> p_from_id
1273 and contact_type <> 'EMPLOYEE'
1274 and rownum = 1;
1275
1276 --dbms_output.put_line('got v_merged_id' || v_merged_to_id);
1277
1278 EXCEPTION
1279 when no_data_found then
1280 v_merged_to_id := null;
1281 END ;
1282
1283
1284 IF v_merged_to_id IS NULL THEN
1285 --Did'nt find any record which would result in a duplicate
1286 --Hence, just update the record with the new party id.
1287
1288 ----dbms_output.put_line('hi 1' || v_merged_to_id);
1289
1290 UPDATE CS_HZ_SR_CONTACT_POINTS
1291 SET party_id = p_to_fk_id,
1292 object_version_number = object_version_number + 1,
1293 last_update_date = SYSDATE,
1294 last_updated_by = G_USER_ID,
1295 last_update_login = G_LOGIN_ID
1296 WHERE sr_contact_point_id = p_from_id
1297 AND party_id = p_from_fk_id
1298 RETURNING incident_id , sr_contact_point_id BULK COLLECT
1299 INTO l_incident_id , l_sr_contact_point_id ;
1300
1301 FOR i IN 1..l_incident_id.COUNT
1302 LOOP
1303 -- Contact point audit record
1304 -- Populate CP audit Records structure.
1305
1306 CS_SRCONTACT_PKG.Populate_CP_Audit_Rec
1307 (p_sr_contact_point_id => p_from_id ,
1308 x_sr_contact_rec => l_sr_contact_new_rec,
1309 x_return_status => l_return_status,
1310 x_msg_count => x_msg_count,
1311 x_msg_data => x_msg_data ) ;
1312
1313 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1314 RAISE FND_API.G_EXC_ERROR;
1315 END IF ;
1316
1317 -- Change the old value of the party id in the old CP audit record.
1318
1319 l_sr_contact_old_rec := l_sr_contact_new_rec;
1320 l_sr_contact_old_rec.party_id := p_from_fk_id;
1321
1322 -- Create CP audit record
1323
1324 CS_SRCONTACT_PKG.create_cp_audit
1325 (p_sr_contact_point_id => p_from_id,
1326 p_incident_id => l_incident_id(i),
1327 p_new_cp_rec => l_sr_contact_new_rec,
1328 p_old_cp_rec => l_sr_contact_old_rec,
1329 p_cp_modified_by => fnd_global.user_id,
1330 p_cp_modified_on => sysdate,
1331 x_return_status => l_return_status,
1332 x_msg_count => x_msg_count,
1333 x_msg_data => x_msg_data ) ;
1334
1335 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1336 RAISE FND_API.G_EXC_ERROR;
1337 END IF ;
1338
1339 -- Create SR Child Audit Record
1340
1341 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1342 (p_incident_id => l_incident_id(i),
1343 p_updated_entity_code => 'SR_CONTACT_POINT',
1344 p_updated_entity_id => l_sr_contact_point_id(i) ,
1345 p_entity_update_date => sysdate ,
1346 p_entity_activity_code => 'U',
1347 p_update_program_code => 'PARTY_MERGE',
1348 x_audit_id => l_audit_id ,
1349 x_return_status => l_return_status ,
1350 x_msg_count => x_msg_count ,
1351 x_msg_data => x_msg_data ) ;
1352
1353 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1354 RAISE FND_API.G_EXC_ERROR;
1355 END IF ;
1356
1357 END LOOP ;
1358
1359 ---RETURN;
1360
1361 ELSE ---found a dup record
1362
1363
1367 --as DUPLICATE, check if it is the PRIMARY CONTACT. IF so, make the current one
1364 --Found a record which would create duplicates in cs_hz_sr_contact_points
1365 --HEnce, mark the the other found record as duplicate and update the current
1366 --one which needs to updated with the new party id. WHile marking the record
1368 --which is going to be updated with new party id, as PRIMARY CONTACT
1369
1370 --Found a record whcih results in duplicate, hence delete it
1371 DELETE FROM
1372 CS_HZ_SR_CONTACT_POINTS
1373 WHERE sr_contact_point_id = v_merged_to_id
1374 RETURNING primary_flag INTO l_primary_flag ;
1375
1376
1377 ---dbms_output.put_line('hi 2' || v_merged_to_id);
1378
1379 IF l_primary_flag= 'N' OR l_primary_flag IS NULL THEN
1380 UPDATE CS_HZ_SR_CONTACT_POINTS
1381 SET party_id = p_to_fk_id,
1382 object_version_number = object_version_number + 1,
1383 last_update_date = SYSDATE,
1384 last_updated_by = G_USER_ID,
1385 last_update_login = G_LOGIN_ID
1386 WHERE sr_contact_point_id = p_from_id
1387 AND party_id = p_from_fk_id
1388 RETURNING incident_id , sr_contact_point_id BULK COLLECT
1389 INTO l_incident_id , l_sr_contact_point_id ;
1390
1391 FOR i IN 1..l_incident_id.COUNT
1392 LOOP
1393
1394 -- Contact point audit record
1395 -- Populate CP audit Records structure.
1396
1397 CS_SRCONTACT_PKG.Populate_CP_Audit_Rec
1398 (p_sr_contact_point_id => p_from_id ,
1399 x_sr_contact_rec => l_sr_contact_new_rec,
1400 x_return_status => l_return_status,
1401 x_msg_count => x_msg_count,
1402 x_msg_data => x_msg_data ) ;
1403
1404 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1405 RAISE FND_API.G_EXC_ERROR;
1406 END IF ;
1407
1408 -- Change the old value of the party id in the old CP audit record.
1409
1410 l_sr_contact_old_rec := l_sr_contact_new_rec;
1411 l_sr_contact_old_rec.party_id := p_from_fk_id;
1412
1413 -- Create CP audit record
1414
1415 CS_SRCONTACT_PKG.create_cp_audit
1416 (p_sr_contact_point_id => p_from_id,
1417 p_incident_id => l_incident_id(i),
1418 p_new_cp_rec => l_sr_contact_new_rec,
1419 p_old_cp_rec => l_sr_contact_old_rec,
1420 p_cp_modified_by => fnd_global.user_id,
1421 p_cp_modified_on => sysdate,
1422 x_return_status => l_return_status,
1423 x_msg_count => x_msg_count,
1424 x_msg_data => x_msg_data ) ;
1425
1426 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1427 RAISE FND_API.G_EXC_ERROR;
1428 END IF ;
1429
1430 -- Create SR Child Audit Record
1431
1432
1433 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1434 (p_incident_id => l_incident_id(i),
1435 p_updated_entity_code => 'SR_CONTACT_POINT',
1436 p_updated_entity_id => l_sr_contact_point_id(i) ,
1437 p_entity_update_date => sysdate ,
1438 p_entity_activity_code => 'U',
1439 p_update_program_code => 'PARTY_MERGE',
1440 x_audit_id => l_audit_id ,
1441 x_return_status => l_return_status ,
1442 x_msg_count => x_msg_count ,
1443 x_msg_data => x_msg_data ) ;
1444
1445 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1446 RAISE FND_API.G_EXC_ERROR;
1447 END IF ;
1448
1449 END LOOP ;
1450
1451 ELSE
1452 --PRIMARY_FLAG of the deleted record was Y, hence make this record as primary
1453 ---dbms_output.put_line('hi 3' || v_merged_to_id);
1454 UPDATE CS_HZ_SR_CONTACT_POINTS
1455 SET party_id = p_to_fk_id,
1456 object_version_number = object_version_number + 1,
1457 last_update_date = SYSDATE,
1458 last_updated_by = G_USER_ID,
1459 last_update_login = G_LOGIN_ID,
1460 primary_flag = 'Y'
1461 WHERE sr_contact_point_id = p_from_id
1462 AND party_id = p_from_fk_id
1463 RETURNING incident_id , sr_contact_point_id BULK COLLECT
1464 INTO l_incident_id , l_sr_contact_point_id ;
1465
1469 -- Contact point audit record
1466 FOR i IN 1..l_incident_id.COUNT
1467 LOOP
1468
1470 -- Populate CP audit Records structure.
1471
1472 CS_SRCONTACT_PKG.Populate_CP_Audit_Rec
1473 (p_sr_contact_point_id => p_from_id ,
1474 x_sr_contact_rec => l_sr_contact_new_rec,
1475 x_return_status => l_return_status,
1476 x_msg_count => x_msg_count,
1477 x_msg_data => x_msg_data ) ;
1478
1479 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1480 RAISE FND_API.G_EXC_ERROR;
1481 END IF ;
1482
1483 -- Change the old value of the party id in the old CP audit record.
1484
1485 l_sr_contact_old_rec := l_sr_contact_new_rec;
1486 l_sr_contact_old_rec.party_id := p_from_fk_id;
1487 l_sr_contact_old_rec.primary_flag := 'N';
1488
1489 -- Create CP audit record
1490
1491
1492 CS_SRCONTACT_PKG.create_cp_audit
1493 (p_sr_contact_point_id => p_from_id,
1494 p_incident_id => l_incident_id(i),
1495 p_new_cp_rec => l_sr_contact_new_rec,
1496 p_old_cp_rec => l_sr_contact_old_rec,
1497 p_cp_modified_by => fnd_global.user_id,
1498 p_cp_modified_on => sysdate,
1499 x_return_status => l_return_status,
1500 x_msg_count => x_msg_count,
1501 x_msg_data => x_msg_data ) ;
1502
1503 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1504 RAISE FND_API.G_EXC_ERROR;
1505 END IF ;
1506
1507 -- Create SR Child Audit Record
1508
1509 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1510 (p_incident_id => l_incident_id(i),
1511 p_updated_entity_code => 'SR_CONTACT_POINT',
1512 p_updated_entity_id => l_sr_contact_point_id(i) ,
1513 p_entity_update_date => sysdate ,
1514 p_entity_activity_code => 'U',
1515 p_update_program_code => 'PARTY_MERGE',
1516 x_audit_id => l_audit_id ,
1517 x_return_status => l_return_status ,
1518 x_msg_count => x_msg_count ,
1519 x_msg_data => x_msg_data ) ;
1520
1521 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1522 RAISE FND_API.G_EXC_ERROR;
1523 END IF ;
1524
1525 END LOOP ;
1526
1527 END IF;
1528
1529 END IF;
1530
1531 l_count := sql%rowcount;
1532
1533 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1534 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1535
1536 exception
1537 WHEN FND_API.G_EXC_ERROR THEN
1538 x_return_status := FND_API.G_RET_STS_ERROR;
1539 FND_MSG_PUB.Count_And_Get
1540 ( p_count => x_msg_count,
1541 p_data => x_msg_data
1542 );
1543 raise;
1544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1546 FND_MSG_PUB.Count_And_Get
1547 ( p_count => x_msg_count,
1548 p_data => x_msg_data
1549 );
1550 raise;
1551 WHEN resource_busy then
1552 arp_message.set_line(g_proc_name || '.' || l_api_name ||
1553 '; Could not obtain lock for records in table ' ||
1554 'CS_HZ_SR_CONTACT_POINTS for party_id = ' || p_from_fk_id );
1555 x_return_status := FND_API.G_RET_STS_ERROR;
1556 raise;
1557
1558 WHEN others then
1559 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
1560 x_return_status := FND_API.G_RET_STS_ERROR;
1561 FND_MSG_PUB.Count_And_Get
1562 ( p_count => x_msg_count,
1563 p_data => x_msg_data
1564 );
1565 raise;
1566 end;
1567 end if;
1568 END CS_CONTACTS_MERGE_PARTY;
1569
1570
1571 PROCEDURE CS_CONTACTS_MERGE_CONT_POINTS(
1572 p_entity_name IN VARCHAR2,
1573 p_from_id IN NUMBER,
1574 x_to_id OUT NOCOPY NUMBER,
1575 p_from_fk_id IN NUMBER,
1576 p_to_fk_id IN NUMBER,
1580 x_return_status OUT NOCOPY VARCHAR2)
1577 p_parent_entity_name IN VARCHAR2,
1578 p_batch_id IN NUMBER,
1579 p_batch_party_id IN NUMBER,
1581 IS
1582 CURSOR c1 IS
1583 SELECT 1
1584 FROM cs_hz_sr_contact_points
1585 WHERE sr_contact_point_id = p_from_id
1586 AND contact_point_id = p_from_fk_id
1587 AND contact_type <> 'EMPLOYEE'
1588 FOR UPDATE NOWAIT;
1589
1590
1591 l_merge_reason_code VARCHAR2(30);
1592 l_api_name VARCHAR2(60) := 'CS_CONTACTS_MERGE_CONTACT_POINTS';
1593 l_count NUMBER(10) := 0;
1594 l_audit_id NUMBER ;
1595 l_return_status VARCHAR2(3);
1596 x_msg_count NUMBER;
1597 x_msg_data VARCHAR2(1000);
1598 v_merged_to_id NUMBER;
1599 l_primary_flag VARCHAR2(1);
1600 l_incident_id NUM_TBL;
1601 l_sr_contact_point_id NUM_TBL;
1602 l_sr_contact_old_rec CS_SERVICEREQUEST_PVT.CONTACTS_REC;
1603 l_sr_contact_new_rec CS_SERVICEREQUEST_PVT.CONTACTS_REC;
1604
1605 RESOURCE_BUSY EXCEPTION;
1606 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1607
1608 BEGIN
1609 arp_message.set_line('CS_PARTY_MERGE_PKG.CS_CONTACTS_MERGE_CONTACT_POINTS()+');
1610
1611 x_return_status := FND_API.G_RET_STS_SUCCESS;
1612
1613 SELECT merge_reason_code
1614 INTO l_merge_reason_code
1615 FROM hz_merge_batch
1616 WHERE batch_id = p_batch_id;
1617
1618
1619 --dbms_output.put_line('gping to get merged id for ct id');
1620 --l_merge_reason_code := null;
1621 --dbms_output.put_line('gping to get merged id for ct id');
1622
1623 if l_merge_reason_code = 'DUPLICATE' then
1624 -- if reason code is duplicate then allow the party merge to happen without
1625 -- any validations.
1626 null;
1627 else
1628 -- if there are any validations to be done, include it in this section
1629 null;
1630 end if;
1631
1632 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1633 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1634
1635 if p_from_fk_id = p_to_fk_id then
1636 x_to_id := p_from_id;
1637 return;
1638 end if;
1639
1640 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1644
1641 -- dependent record to the new parent. Before transferring check if a similar
1642 -- dependent record exists on the new parent. If a duplicate exists then do
1643 -- not transfer and return the id of the duplicate record as the Merged To Id
1645 -- In the case of CS_HZ_SR_CONTACT_POINTS table, if party id 1000 gets merged to party
1646 -- id 2000 then, we have to update all records with customer_id = 1000 to 2000
1647
1648 IF p_from_fk_id <> p_to_fk_id then
1649 begin
1650 -- obtain lock on records to be updated.
1651 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1652 arp_message.set_token('TABLE_NAME', 'CS_HZ_SR_CONTACT_POINTS', FALSE);
1653
1654 open c1;
1655 close c1;
1656
1657
1658 BEGIN
1659
1660 --dbms_output.put_line('gping to get merged id for ct id');
1661 --check if the merge results in a dupliacte record.
1662 --V_MERGED_TO_ID will have the SR_CONTACT_POINT_ID of the duplicate record.
1663
1664 select sr_contact_point_id INTO v_merged_to_id
1665 from cs_hz_sr_contact_points
1666 where contact_point_id = p_to_fk_id
1667 and ( party_id, incident_id ) = ( select party_id, incident_id
1668 from cs_hz_sr_contact_points
1669 where sr_contact_point_id = p_from_id )
1670 and sr_contact_point_id <> p_from_id
1671 and contact_type <> 'EMPLOYEE'
1672 and rownum = 1;
1673
1674 --dbms_output.put_line('got merged id ' || v_merged_to_id) ;
1675
1676 EXCEPTION
1677 when no_data_found then
1678 v_merged_to_id := null;
1679 END ;
1680
1681
1682 -- dbms_output.put_line('gt merged id for ct id'|| v_merged_to_id);
1683
1684 IF v_merged_to_id IS NULL THEN
1685 --Did'nt find any record which would result in duplicate
1686 --dbms_output.put_line('going to update ct pd ');
1687 --dbms_output.put_line(' meged id is null');
1688
1689 UPDATE CS_HZ_SR_CONTACT_POINTS
1690 SET contact_point_id = p_to_fk_id,
1691 object_version_number = object_version_number + 1,
1692 last_update_date = SYSDATE,
1693 last_updated_by = G_USER_ID,
1694 last_update_login = G_LOGIN_ID
1695 WHERE sr_contact_point_id = p_from_id
1696 AND contact_point_id = p_from_fk_id
1697 RETURNING incident_id , sr_contact_point_id BULK COLLECT
1698 INTO l_incident_id , l_sr_contact_point_id ;
1699
1700 FOR i IN 1..l_incident_id.COUNT
1701 LOOP
1702
1703 -- Contact point audit record
1704 -- Populate CP audit Records structure.
1705
1706 CS_SRCONTACT_PKG.Populate_CP_Audit_Rec
1707 (p_sr_contact_point_id => p_from_id ,
1708 x_sr_contact_rec => l_sr_contact_new_rec,
1709 x_return_status => l_return_status,
1710 x_msg_count => x_msg_count,
1711 x_msg_data => x_msg_data ) ;
1712
1713 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1714 RAISE FND_API.G_EXC_ERROR;
1715 END IF ;
1716
1717 -- Change the old value of the party id in the old CP audit record.
1718
1719 l_sr_contact_old_rec := l_sr_contact_new_rec;
1720 l_sr_contact_old_rec.contact_point_id := p_from_fk_id;
1721
1722 -- Create CP audit record
1723
1724
1725 CS_SRCONTACT_PKG.create_cp_audit
1726 (p_sr_contact_point_id => p_from_id,
1727 p_incident_id => l_incident_id(i),
1728 p_new_cp_rec => l_sr_contact_new_rec,
1729 p_old_cp_rec => l_sr_contact_old_rec,
1730 p_cp_modified_by => fnd_global.user_id,
1731 p_cp_modified_on => sysdate,
1732 x_return_status => l_return_status,
1733 x_msg_count => x_msg_count,
1734 x_msg_data => x_msg_data ) ;
1735
1736 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1737 RAISE FND_API.G_EXC_ERROR;
1738 END IF ;
1739
1740 -- Create SR Child Audit Record
1741
1742 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1743 (p_incident_id => l_incident_id(i),
1744 p_updated_entity_code => 'SR_CONTACT_POINT',
1745 p_updated_entity_id => l_sr_contact_point_id(i) ,
1746 p_entity_update_date => sysdate ,
1747 p_entity_activity_code => 'U',
1748 p_update_program_code => 'PARTY_MERGE',
1749 x_audit_id => l_audit_id ,
1750 x_return_status => l_return_status ,
1751 x_msg_count => x_msg_count ,
1752 x_msg_data => x_msg_data ) ;
1753
1754 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1755 RAISE FND_API.G_EXC_ERROR;
1756 END IF ;
1757
1758 END LOOP ;
1759
1760
1761 ELSE --found dup rec
1765 --as DUPLICATE, check if it is the PRIMARY CONTACT. IF so, make the current one
1762 --Found a record which would create duplicates in cs_hz_sr_contact_points
1763 --HEnce, mark the the other found record as duplicate and update the current
1764 --one which needs to updated with the new party id. WHile marking the record
1766 --which is going to be updated with new party id, as PRIMARY CONTACT
1767
1768 --Found a record which would result in duplicate. hence delete it
1769 ---dbms_output.put_line('merged id is not null');
1770
1771 DELETE FROM CS_HZ_SR_CONTACT_POINTS
1772 WHERE SR_CONTACT_POINT_ID = v_merged_to_id
1773 RETURNING primary_flag INTO l_primary_flag ;
1774
1775 IF l_primary_flag= 'N' OR l_primary_flag IS NULL THEN
1776 ---dbms_output.put_line('primary flag is N');
1777
1778 UPDATE CS_HZ_SR_CONTACT_POINTS
1779 SET contact_point_id = p_to_fk_id,
1780 object_version_number = object_version_number + 1,
1781 last_update_date = SYSDATE,
1782 last_updated_by = G_USER_ID,
1783 last_update_login = G_LOGIN_ID
1784 WHERE sr_contact_point_id = p_from_id
1785 AND contact_point_id = p_from_fk_id
1786 RETURNING incident_id , sr_contact_point_id BULK COLLECT
1787 INTO l_incident_id , l_sr_contact_point_id ;
1788
1789 FOR i IN 1..l_incident_id.COUNT
1790 LOOP
1791 -- Contact point audit record
1792 -- Populate CP audit Records structure.
1793
1794 CS_SRCONTACT_PKG.Populate_CP_Audit_Rec
1795 (p_sr_contact_point_id => p_from_id ,
1796 x_sr_contact_rec => l_sr_contact_new_rec,
1800
1797 x_return_status => l_return_status,
1798 x_msg_count => x_msg_count,
1799 x_msg_data => x_msg_data ) ;
1801 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1802 RAISE FND_API.G_EXC_ERROR;
1803 END IF ;
1804
1805 -- Change the old value of the party id in the old CP audit record.
1806
1807 l_sr_contact_old_rec := l_sr_contact_new_rec;
1808 l_sr_contact_old_rec.contact_point_id := p_from_fk_id;
1809
1810 -- Create CP audit record
1811
1812 CS_SRCONTACT_PKG.create_cp_audit
1813 (p_sr_contact_point_id => p_from_id,
1814 p_incident_id => l_incident_id(i),
1815 p_new_cp_rec => l_sr_contact_new_rec,
1816 p_old_cp_rec => l_sr_contact_old_rec,
1817 p_cp_modified_by => fnd_global.user_id,
1818 p_cp_modified_on => sysdate,
1819 x_return_status => l_return_status,
1820 x_msg_count => x_msg_count,
1821 x_msg_data => x_msg_data ) ;
1822
1823 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1824 RAISE FND_API.G_EXC_ERROR;
1825 END IF ;
1826
1827 -- Create SR Child Audit Record
1828
1829 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1830 (p_incident_id => l_incident_id(i),
1831 p_updated_entity_code => 'SR_CONTACT_POINT',
1832 p_updated_entity_id => l_sr_contact_point_id(i) ,
1833 p_entity_update_date => sysdate ,
1834 p_entity_activity_code => 'U',
1835 p_update_program_code => 'PARTY_MERGE',
1836 x_audit_id => l_audit_id ,
1837 x_return_status => l_return_status ,
1838 x_msg_count => x_msg_count ,
1839 x_msg_data => x_msg_data ) ;
1840
1841 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1842 RAISE FND_API.G_EXC_ERROR;
1843 END IF ;
1844
1845
1846 END LOOP ;
1847 ELSE
1848 --PRIMARY_FLAG of the deleted record was Y, hence make this record as primary
1849 ---dbms_output.put_line('primary flag is Y');
1850 UPDATE CS_HZ_SR_CONTACT_POINTS
1851 SET contact_point_id = p_to_fk_id,
1855 last_update_login = G_LOGIN_ID,
1852 object_version_number = object_version_number + 1,
1853 last_update_date = SYSDATE,
1854 last_updated_by = G_USER_ID,
1856 primary_flag = 'Y'
1857 WHERE sr_contact_point_id = p_from_id
1858 AND contact_point_id = p_from_fk_id
1859 RETURNING incident_id , sr_contact_point_id BULK COLLECT
1860 INTO l_incident_id , l_sr_contact_point_id ;
1861
1862 FOR i IN 1..l_incident_id.COUNT
1863 LOOP
1864
1865 -- Contact point audit record
1866 -- Populate CP audit Records structure.
1867
1868 CS_SRCONTACT_PKG.Populate_CP_Audit_Rec
1869 (p_sr_contact_point_id => p_from_id ,
1870 x_sr_contact_rec => l_sr_contact_new_rec,
1871 x_return_status => l_return_status,
1872 x_msg_count => x_msg_count,
1873 x_msg_data => x_msg_data ) ;
1874
1875 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1876 RAISE FND_API.G_EXC_ERROR;
1877 END IF ;
1878
1879 -- Change the old value of the party id in the old CP audit record.
1880
1881 l_sr_contact_old_rec := l_sr_contact_new_rec;
1882 l_sr_contact_old_rec.contact_point_id := p_from_fk_id;
1883
1884 -- Create CP audit record
1885
1886 CS_SRCONTACT_PKG.create_cp_audit
1887 (p_sr_contact_point_id => p_from_id,
1888 p_incident_id => l_incident_id(i),
1889 p_new_cp_rec => l_sr_contact_new_rec,
1890 p_old_cp_rec => l_sr_contact_old_rec,
1891 p_cp_modified_by => fnd_global.user_id,
1892 p_cp_modified_on => sysdate,
1893 x_return_status => l_return_status,
1894 x_msg_count => x_msg_count,
1895 x_msg_data => x_msg_data ) ;
1896
1897 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1898 RAISE FND_API.G_EXC_ERROR;
1899 END IF ;
1900
1901 -- Create SR Child Audit Record
1902
1903 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1904 (p_incident_id => l_incident_id(i),
1905 p_updated_entity_code => 'SR_CONTACT_POINT',
1906 p_updated_entity_id => l_sr_contact_point_id(i) ,
1907 p_entity_update_date => sysdate ,
1908 p_entity_activity_code => 'U',
1909 p_update_program_code => 'PARTY_MERGE',
1910 x_audit_id => l_audit_id ,
1911 x_return_status => l_return_status ,
1912 x_msg_count => x_msg_count ,
1913 x_msg_data => x_msg_data ) ;
1914
1915 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1916 RAISE FND_API.G_EXC_ERROR;
1917 END IF ;
1918
1919 END LOOP;
1920
1924
1921 END IF; -- IF l_primary_flag= 'N' OR l_primary_flag IS NULL
1922 END IF; -- IF v_merged_to_id IS NULL
1923
1925 l_count := sql%rowcount;
1926
1927 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1928 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1929
1930 exception
1931 WHEN FND_API.G_EXC_ERROR THEN
1932 x_return_status := FND_API.G_RET_STS_ERROR;
1933 FND_MSG_PUB.Count_And_Get
1934 ( p_count => x_msg_count,
1935 p_data => x_msg_data
1936 );
1937 RAISE;
1938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1940 FND_MSG_PUB.Count_And_Get
1941 ( p_count => x_msg_count,
1942 p_data => x_msg_data
1943 );
1944 RAISE;
1945 when resource_busy then
1946 arp_message.set_line(g_proc_name || '.' || l_api_name ||
1947 '; Could not obtain lock for records in table ' ||
1948 'CS_HZ_SR_CONTACT_POINTS for contact_point_id = ' || p_from_fk_id );
1949 x_return_status := FND_API.G_RET_STS_ERROR;
1950 raise;
1951
1952 when others then
1953 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
1954 x_return_status := FND_API.G_RET_STS_ERROR;
1955 raise;
1956 end;
1957 end if; -- IF p_from_fk_id <> p_to_fk_id
1958
1959 END CS_CONTACTS_MERGE_CONT_POINTS;
1960
1961 -- New procedure added for party merge for the following contact points added
1962 -- for the SR customer in 11.5.9
1963 -- cs_incidents_all_b.customer_phone_id -> hz_contact_points.contact_point_id
1964 -- cs_incidents_all_b.customer_email_id -> hz_contact_points.contact_point_id
1965
1966 PROCEDURE CS_INC_ALL_MERGE_CONT_POINTS (
1967 p_entity_name IN VARCHAR2,
1968 p_from_id IN NUMBER,
1969 x_to_id OUT NOCOPY NUMBER,
1970 p_from_fk_id IN NUMBER,
1971 p_to_fk_id IN NUMBER,
1972 p_parent_entity_name IN VARCHAR2,
1973 p_batch_id IN NUMBER,
1974 p_batch_party_id IN NUMBER,
1975 x_return_status OUT NOCOPY VARCHAR2)
1976 IS
1977 -- cursor fetches all the records that need to be merged.
1978 CURSOR c1 IS
1979 SELECT rowid,
1980 incident_id ,
1981 customer_phone_id ,
1982 customer_email_id ,
1983 last_update_program_code
1984 FROM cs_incidents_all_b
1985 WHERE p_from_fk_id IN ( customer_phone_id, customer_email_id )
1986 FOR update nowait;
1987
1988 l_merge_reason_code VARCHAR2(30);
1989 l_api_name VARCHAR2(30) := 'CS_INC_ALL_MERGE_CONT_POINTS';
1990 l_count NUMBER(10) := 0;
1991
1992 l_rowid_tbl ROWID_TBL;
1993 l_incident_id NUM_TBL;
1994 l_customer_phone_id NUM_TBL;
1995 l_customer_email_id NUM_TBL;
1996 l_last_update_program_code VARCHAR2_30_TBL;
1997 l_last_fetch BOOLEAN := FALSE ;
1998 l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
1999 l_audit_id NUMBER;
2000 l_return_status VARCHAR2(3);
2001 x_msg_count NUMBER(15);
2002 x_msg_data VARCHAR2(2000);
2003 RESOURCE_BUSY EXCEPTION;
2004 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
2005
2006 BEGIN
2007 arp_message.set_line('CS_SR_PARTY_MERGE_PKG.CS_INC_ALL_MERGE_CONT_POINTS()+');
2008
2009 x_return_status := FND_API.G_RET_STS_SUCCESS;
2010
2011 SELECT merge_reason_code
2012 INTO l_merge_reason_code
2013 FROM hz_merge_batch
2014 WHERE batch_id = p_batch_id;
2015
2016 IF l_merge_reason_code = 'DUPLICATE' THEN
2017 -- if reason code is duplicate then allow the party merge to happen without
2018 -- any validations.
2019 null;
2020 ELSE
2021 -- if there are any validations to be done, include it in this section
2022 null;
2023 END IF;
2024
2025 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2029 x_to_id := p_from_id;
2026 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2027
2028 IF p_from_fk_id = p_to_fk_id THEN
2030 RETURN;
2031 END IF;
2032
2033 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2034 -- dependent record to the new parent. Before transferring check if a similar
2035 -- dependent record exists on the new parent. If a duplicate exists then do
2036 -- not transfer and return the id of the duplicate record as the Merged To Id
2037
2038 IF p_from_fk_id <> p_to_fk_id THEN
2039 BEGIN
2040 -- obtain lock on records to be updated.
2041 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
2042 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_ALL_B', FALSE);
2043
2044 OPEN c1;
2045 LOOP -- loop to batch process 1000 records
2046 FETCH c1 bulk collect
2047 INTO l_rowid_tbl ,
2048 l_incident_id ,
2049 l_customer_phone_id ,
2050 l_customer_email_id ,
2051 l_last_update_program_code
2052 LIMIT 1000 ;
2053
2054
2055 -- IF l_rowid_tbl.count = 0 THEN
2056 -- RETURN;
2057 -- END IF;
2058
2059 IF c1%NOTFOUND THEN
2060 l_last_fetch := TRUE ;
2061 END IF ;
2062
2063 IF l_rowid_tbl.COUNT = 0 AND l_last_fetch THEN
2064 EXIT;
2065 END IF ;
2066
2067 FORALL i IN 1..l_rowid_tbl.COUNT
2068 UPDATE cs_incidents_all_b
2069 SET customer_phone_id = decode(customer_phone_id, p_from_fk_id, p_to_fk_id,
2070 customer_phone_id ),
2071 customer_email_id = decode(customer_email_id, p_from_fk_id, p_to_fk_id,
2072 customer_email_id ),
2073 incident_last_modified_date = sysdate ,
2074 last_update_program_code = 'PARTY_MERGE',
2075 object_version_number = object_version_number + 1,
2076 last_update_date = SYSDATE,
2077 last_updated_by = G_USER_ID,
2078 last_update_login = G_LOGIN_ID
2079 WHERE rowid = l_rowid_tbl(i);
2080
2081 l_count := sql%rowcount;
2082
2083 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
2084 arp_message.set_token('NUM_ROWS', to_char(l_count) );
2085
2086 -- create audit record in cs_incidents_audit_b table for each service
2087 -- request for which site_id is updated.
2088
2089 FOR i IN 1..l_incident_id.COUNT
2090 LOOP
2091
2092 CS_Servicerequest_UTIL.Prepare_Audit_Record (
2093 p_api_version => 1,
2094 p_request_id => l_incident_id(i),
2095 x_return_status => x_return_status,
2096 x_msg_count => x_msg_count,
2097 x_msg_data => x_msg_data,
2098 x_audit_vals_rec => l_audit_vals_rec );
2099
2100 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
2101
2102 -- set the customer_email_id/old_customer_email_id of audit record
2103
2104 IF l_customer_email_id(i) = p_from_fk_id THEN
2105 l_audit_vals_rec.customer_email_id := p_to_fk_id ;
2106 l_audit_vals_rec.old_customer_email_id := l_customer_email_id(i);
2107 ELSE
2108 l_audit_vals_rec.customer_email_id := l_customer_email_id(i);
2109 l_audit_vals_rec.old_customer_email_id := l_customer_email_id(i);
2110 END IF;
2111
2112 -- set the customer_phone_id/old_customer_phone_id of audit record
2113
2114 IF l_customer_phone_id(i) = p_from_fk_id THEN
2115 l_audit_vals_rec.customer_phone_id := p_to_fk_id ;
2116 l_audit_vals_rec.old_customer_phone_id := l_customer_phone_id(i);
2117 ELSE
2118 l_audit_vals_rec.customer_phone_id := l_customer_phone_id(i);
2119 l_audit_vals_rec.old_customer_phone_id := l_customer_phone_id(i);
2120 END IF;
2121
2122
2123 -- set the last_program_code/old_last_progream_code of audit record
2124
2125 l_audit_vals_rec. last_update_program_code := 'PARTY_MERGE' ;
2126 l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code (i);
2127 l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
2128 l_audit_vals_rec.updated_entity_id := l_incident_id(i);
2129 l_audit_vals_rec.entity_activity_code := 'U';
2130
2131 END IF;
2132
2133 CS_ServiceRequest_PVT.Create_Audit_Record (
2134 p_api_version => 2.0,
2135 x_return_status => x_return_status,
2136 x_msg_count => x_msg_count,
2137 x_msg_data => x_msg_data,
2138 p_request_id => l_incident_id(i),
2139 p_audit_id => NULL,
2140 p_audit_vals_rec => l_audit_vals_rec ,
2141 p_user_id => G_USER_ID,
2142 p_login_id => G_LOGIN_ID,
2143 p_last_update_date => SYSDATE,
2144 p_creation_date => SYSDATE,
2145 p_comments => NULL,
2146 x_audit_id => l_audit_id);
2147
2148 END LOOP;
2149
2150 IF l_last_fetch THEN
2151 EXIT ;
2152 END IF ;
2153
2154 END LOOP; -- End loop for the batch process.
2155
2156 CLOSE c1 ;
2157
2158 EXCEPTION
2159 WHEN resource_busy THEN
2160 arp_message.set_line(g_proc_name || '.' || l_api_name ||
2161 '; Could not obtain lock for records in table ' ||
2162 'CS_INCIDENTS_ALL_B for customer_phone_id / customer_email_id = '
2163 || p_from_fk_id );
2164 x_return_status := FND_API.G_RET_STS_ERROR;
2165 RAISE;
2166
2167 WHEN others THEN
2168 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
2169 x_return_status := FND_API.G_RET_STS_ERROR;
2170 RAISE;
2171 END;
2172 END IF;
2173 END CS_INC_ALL_MERGE_CONT_POINTS;
2174
2175 END CS_SR_PARTY_MERGE_PKG;
[Home] [Help]