[Home] [Help]
PACKAGE BODY: APPS.HZ_BATCH_ACTION_PUB
Source
1 PACKAGE BODY HZ_BATCH_ACTION_PUB AS
2 /*$Header: ARHBATAB.pls 120.16 2006/05/03 09:03:27 vravicha noship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 G_DEBUG_COUNT NUMBER := 0;
9 --G_DEBUG BOOLEAN := FALSE;
10
11 TYPE sel_cur IS REF CURSOR;
12
13 --------------------------------------
14 -- declaration of private procedures and functions
15 --------------------------------------
16 /*PROCEDURE enable_debug;
17
18 PROCEDURE disable_debug;
19 */
20
21 PROCEDURE action_on_entities ( p_batch_id IN NUMBER,
22 p_entity IN VARCHAR2,
23 p_action_on_entity IN VARCHAR2
24 );
25
26 /* Commented out for bug 4673725
27 PROCEDURE update_int_tables ( p_batch_id IN NUMBER,
28 p_tab_name IN VARCHAR2,
29 p_orig_system IN VARCHAR2,
30 p_orig_system_osr IN VARCHAR2,
31 p_dup_os_val IN VARCHAR2,
32 p_dup_osr_val IN VARCHAR2,
33 p_action_on_entity IN VARCHAR2
34 );
35 */
36
37 PROCEDURE reg_action_on_party ( p_batch_id IN NUMBER,
38 p_action_new_parties IN VARCHAR2,
39 p_action_existing_parties IN VARCHAR2,
40 p_action_dup_parties IN VARCHAR2,
41 p_action_pot_dup_parties IN VARCHAR2,
42 x_return_status OUT NOCOPY VARCHAR2
43 );
44
45 PROCEDURE reg_action_on_sites ( p_batch_id IN NUMBER,
46 p_action_new_addrs IN VARCHAR2,
47 p_action_existing_addrs IN VARCHAR2,
48 p_action_pot_dup_addrs IN VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2
50 );
51
52 PROCEDURE reg_action_on_cont ( p_batch_id IN NUMBER,
53 p_action_new_contacts IN VARCHAR2,
54 p_action_existing_contacts IN VARCHAR2,
55 p_action_pot_dup_contacts IN VARCHAR2,
56 x_return_status OUT NOCOPY VARCHAR2
57 ) ;
58
59 PROCEDURE reg_action_on_cpts ( p_batch_id IN NUMBER,
60 p_action_new_cpts IN VARCHAR2,
61 p_action_existing_cpts IN VARCHAR2,
62 p_action_pot_dup_cpts IN VARCHAR2,
63 x_return_status OUT NOCOPY VARCHAR2
64 );
65
66
67 PROCEDURE reg_action_on_supents (x_return_status OUT NOCOPY VARCHAR2) ;
68
69
70 PROCEDURE reg_action_on_finents (x_return_status OUT NOCOPY VARCHAR2) ;
71
72 PROCEDURE action_on_parties(p_sql IN VARCHAR2,
73 p_batch_id IN NUMBER,
74 p_action_new_parties IN VARCHAR2,
75 p_action_existing_parties IN VARCHAR2,
76 p_action_dup_parties IN VARCHAR2,
77 p_action_pot_dup_parties IN VARCHAR2,
78 x_return_status OUT NOCOPY VARCHAR2);
79
80 PROCEDURE action_on_sites(p_sql IN VARCHAR2,
81 p_batch_id IN NUMBER,
82 p_action_new_addrs IN VARCHAR2,
83 p_action_existing_addrs IN VARCHAR2,
84 p_action_pot_dup_addrs IN VARCHAR2,
85 x_return_status OUT NOCOPY VARCHAR2);
86
87 PROCEDURE action_on_contacts(p_sql IN VARCHAR2,
88 p_batch_id IN NUMBER,
89 p_action_new_contacts IN VARCHAR2,
90 p_action_existing_contacts IN VARCHAR2,
91 p_action_pot_dup_contacts IN VARCHAR2,
92 x_return_status OUT NOCOPY VARCHAR2);
93
94 PROCEDURE action_on_contactpts(p_sql IN VARCHAR2,
95 p_batch_id IN NUMBER,
96 p_action_new_cpts IN VARCHAR2,
97 p_action_existing_cpts IN VARCHAR2,
98 p_action_pot_dup_cpts VARCHAR2,
99 x_return_status OUT NOCOPY VARCHAR2);
100
101 --------------------------------------
102 -- private procedures and functions
103 --------------------------------------
104
105 /**
106 * PRIVATE PROCEDURE enable_debug
107 *
108 * DESCRIPTION
109 * Turn on debug mode.
110 *
111 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
112 * HZ_UTILITY_V2PUB.enable_debug
113 *
114 * MODIFICATION HISTORY
115 *
116 * 08-18-2003 Rajeshwari P o Created.
117 *
118 */
119
120 /*PROCEDURE enable_debug IS
121
122 BEGIN
123
124 G_DEBUG_COUNT := G_DEBUG_COUNT + 1;
125
126 IF G_DEBUG_COUNT = 1 THEN
127 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
128 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
129 THEN
130 HZ_UTILITY_V2PUB.enable_debug;
131 G_DEBUG := TRUE;
132 END IF;
133 END IF;
134
135 END enable_debug;
136 */
137
138 /**
139 * PRIVATE PROCEDURE disable_debug
140 *
141 * DESCRIPTION
142 * Turn off debug mode.
143 *
144 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
145 * HZ_UTILITY_V2PUB.disable_debug
146 *
147 * MODIFICATION HISTORY
148 *
149 * 08-18-2003 Rajeshwari P o Created.
150 *
151 */
152
153 /*PROCEDURE disable_debug IS
154
155 BEGIN
156
157 IF G_DEBUG THEN
158 G_DEBUG_COUNT := G_DEBUG_COUNT - 1;
159
160 IF G_DEBUG_COUNT = 0 THEN
161 HZ_UTILITY_V2PUB.disable_debug;
162 G_DEBUG := FALSE;
163 END IF;
164 END IF;
165
166 END disable_debug;
167 */
168
169 /**
170 * PRIVATE PROCEDURE action_on_entities
171 *
172 * DESCRIPTION
173 * private procedure to implement the actions on entities.
174 *
175 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
176 *
177 * ARGUMENTS
178 * IN:
179 * p_batch_id Interface Batch ID.
180 * p_entity PARTY, PARTY_SITES, CONTACTS and
181 * CONTACT POINTS.a
182 * p_action_on_entity Action to be taken on entities.
183 *
184 * MODIFICATION HISTORY
185 *
186 * 08-17-2003 Rajeshwari P o Created.
187 *
188 */
189
190 PROCEDURE action_on_entities (
191 p_batch_id IN NUMBER,
192 p_entity IN VARCHAR2,
193 p_action_on_entity IN VARCHAR2
194 ) IS
195
196
197 TYPE L_DUP_RECORD_OSList is TABLE OF HZ_IMP_INT_DEDUP_RESULTS.dup_record_os%TYPE;
198 l_dup_record_os L_DUP_RECORD_OSList;
199 l_winner_record_os L_DUP_RECORD_OSList;
200 l_record_os L_DUP_RECORD_OSList;
201 TYPE L_DUP_RECORD_OSRList is TABLE OF HZ_IMP_INT_DEDUP_RESULTS.dup_record_osr%TYPE;
202 l_dup_record_osr L_DUP_RECORD_OSRList ;
203 l_winner_record_osr L_DUP_RECORD_OSRList;
204 l_record_osr L_DUP_RECORD_OSRList;
205
206 l_rec_os HZ_IMP_INT_DEDUP_RESULTS.dup_record_os%TYPE;
207 l_rec_osr HZ_IMP_INT_DEDUP_RESULTS.dup_record_osr%TYPE;
208
209 CURSOR tobe_removed_rec( p_batch_id IN NUMBER, p_entity IN VARCHAR2) IS
210 SELECT dup_record_os, dup_record_osr
211 FROM HZ_IMP_INT_DEDUP_RESULTS
212 WHERE batch_id = p_batch_id
213 AND entity = p_entity ;
214
215 CURSOR sel_dup_set (p_batch_id IN NUMBER, p_entity IN VARCHAR2 ) IS
216 select distinct winner_record_os, winner_record_osr
217 from hz_imp_int_dedup_results
218 where batch_id = p_batch_id
219 and entity = p_entity
220 group by winner_record_os,winner_record_osr;
221
222 --//Choose the latest updated record
223 CURSOR max_last_date ( p_batch_id in NUMBER , p_entity in VARCHAR2,p_winner_record_os IN VARCHAR2, p_winner_record_osr IN VARCHAR2) IS
224 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
225 FROM HZ_IMP_INT_DEDUP_RESULTS
226 WHERE batch_id = p_batch_id
227 AND entity = p_entity
228 AND nvl(dup_last_update_date,sysdate) = ( SELECT MAX( nvl(DUP_LAST_UPDATE_DATE,sysdate))
229 FROM HZ_IMP_INT_DEDUP_RESULTS
230 WHERE batch_id =p_batch_id
231 and entity = p_entity
232 AND winner_record_os = p_winner_record_os
233 AND winner_record_osr = p_winner_record_osr )
234 AND winner_record_os = p_winner_record_os
235 AND winner_record_osr = p_winner_record_osr
236 AND rownum =1;
237
238 --//Choose the latest created record
239 CURSOR max_created_date ( p_batch_id in NUMBER , p_entity in VARCHAR2,p_winner_record_os IN VARCHAR2, p_winner_record_osr IN VARCHAR2) IS
240 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
241 FROM HZ_IMP_INT_DEDUP_RESULTS
242 WHERE batch_id = p_batch_id
243 AND entity = p_entity
244 AND nvl(dup_creation_date,sysdate) = ( SELECT MAX( nvl(DUP_CREATION_DATE,sysdate))
245 FROM HZ_IMP_INT_DEDUP_RESULTS
246 WHERE batch_id =p_batch_id
247 and entity = p_entity
248 AND winner_record_os = p_winner_record_os
249 AND winner_record_osr = p_winner_record_osr )
250 AND winner_record_os = p_winner_record_os
251 AND winner_record_osr = p_winner_record_osr
252 AND rownum =1;
253
254 --//Choose the earliest created record
255 CURSOR min_created_date ( p_batch_id in NUMBER , p_entity in VARCHAR2,p_winner_record_os IN VARCHAR2, p_winner_record_osr IN VARCHAR2) IS
256 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
257 FROM HZ_IMP_INT_DEDUP_RESULTS
258 WHERE batch_id = p_batch_id
259 AND entity = p_entity
260 AND nvl(dup_creation_date,sysdate) = ( SELECT MIN( nvl(DUP_CREATION_DATE,sysdate))
261 FROM HZ_IMP_INT_DEDUP_RESULTS
262 WHERE batch_id =p_batch_id
263 and entity = p_entity
264 AND winner_record_os = p_winner_record_os
265 AND winner_record_osr = p_winner_record_osr )
266 AND winner_record_os = p_winner_record_os
267 AND winner_record_osr = p_winner_record_osr
268 AND rownum =1;
269
270
271 --// Cursor to select the child entities of Party entity
272 CURSOR get_party_rec(p_batch_id IN NUMBER) is
273 SELECT party_orig_system, party_orig_system_reference
274 FROM HZ_IMP_PARTIES_INT
275 WHERE batch_id = p_batch_id
276 AND interface_status = 'R'
277 ;
278
279 --//Cursor to select the child entities of Site entity
280 CURSOR get_site_rec(p_batch_id IN NUMBER) is
281 SELECT site_orig_system, site_orig_system_reference
282 FROM HZ_IMP_ADDRESSES_INT
283 WHERE batch_id = p_batch_id
284 AND interface_status = 'R';
285
286 --//Cursor to select the child entities of contact entity
287 CURSOR get_contacts_rec(p_batch_id IN NUMBER) is
288 SELECT contact_orig_system, contact_orig_system_reference
289 FROM HZ_IMP_CONTACTS_INT
290 WHERE batch_id = p_batch_id
291 AND interface_status = 'R';
292
293 l_last_fetch BOOLEAN;
294 l_last_fetch_result BOOLEAN;
295 i NUMBER;
296 j NUMBER;
297 commit_counter NUMBER;
298 l_debug_prefix VARCHAR2(30) := '';
299
300 BEGIN
301 -- Check if API is called in debug mode. If yes, enable debug.
302 --enable_debug;
303
304 -- Debug info.
305 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
306 hz_utility_v2pub.debug(p_message=>'update interface tables for action=remove_all (+)',
307 p_prefix=>l_debug_prefix,
308 p_msg_level=>fnd_log.level_procedure);
309 END IF;
310
311 commit_counter := 1000;
312
313 if p_action_on_entity = 'REMOVE_ALL' THEN
314 IF p_entity = 'PARTY' THEN
315 --Mark all the records to be removed
316 UPDATE HZ_IMP_PARTIES_INT
317 SET interface_status = 'R'
318 WHERE batch_id = p_batch_id
319 AND( party_orig_system, party_orig_system_reference ) in
320 ( select dup_record_os, dup_record_osr
321 FROM HZ_IMP_INT_DEDUP_RESULTS result
322 WHERE result.batch_id = p_batch_id
323 --AND result.dup_record_os = result.winner_record_os --Bug3339642.
324 --AND result.dup_record_osr <> result.winner_record_osr --Bug3339642.
325 AND result.entity = 'PARTY');
326
327 ELSIF p_entity = 'PARTY_SITES' THEN
328 --Mark all the site records to be removed
329 UPDATE HZ_IMP_ADDRESSES_INT
330 SET interface_status = 'R'
331 WHERE batch_id = p_batch_id
332 AND( site_orig_system, site_orig_system_reference ) in
333 ( select dup_record_os, dup_record_osr
334 FROM HZ_IMP_INT_DEDUP_RESULTS result
335 WHERE result.batch_id = p_batch_id
336 --AND result.dup_record_os = result.winner_record_os --Bug3339642.
337 --AND result.dup_record_osr <> result.winner_record_osr --Bug3339642.
338 AND result.entity = 'PARTY_SITES');
339
340 ELSIF p_entity = 'CONTACTS' THEN
341 --Mark all the contact records to be removed
342 UPDATE HZ_IMP_CONTACTS_INT
343 SET interface_status = 'R'
344 WHERE batch_id = p_batch_id
345 AND( contact_orig_system,contact_orig_system_reference) in
346 ( select dup_record_os, dup_record_osr
347 FROM HZ_IMP_INT_DEDUP_RESULTS result
348 WHERE result.batch_id = p_batch_id
349 --AND result.dup_record_os = result.winner_record_os --Bug3339642.
350 --AND result.dup_record_osr <> result.winner_record_osr --Bug3339642.
351 AND result.entity = 'CONTACTS');
352
353 ELSIF p_entity = 'CONTACT_POINTS' THEN
354 --Mark all the contact point records to be removed
355 UPDATE HZ_IMP_CONTACTPTS_INT
356 SET interface_status = 'R'
357 WHERE batch_id = p_batch_id
358 AND(cp_orig_system ,cp_orig_system_reference) in
359 ( select dup_record_os, dup_record_osr
360 FROM HZ_IMP_INT_DEDUP_RESULTS result
361 WHERE result.batch_id = p_batch_id
362 --AND result.dup_record_os = result.winner_record_os --Bug3339642.
363 --AND result.dup_record_osr <> result.winner_record_osr --Bug3339642.
364 AND result.entity = 'CONTACT_POINTS');
365
366 END IF;
367 --// The other actions, keep_latest_updated,keep_latest_created, keep_earliest_created
368 else
369
370 BEGIN
371 --Pick the duplicate set
372 OPEN sel_dup_set(p_batch_id, p_entity );
373 LOOP
374 FETCH sel_dup_set BULK COLLECT INTO
375 l_winner_record_os, l_winner_record_osr
376 LIMIT commit_counter;
377
378 IF sel_dup_set%NOTFOUND THEN
379 l_last_fetch_result := TRUE;
380 END IF;
381
382 IF l_winner_record_osr.COUNT = 0 AND l_last_fetch_result THEN
386 FOR i in l_winner_record_osr.FIRST..l_winner_record_osr.LAST
383 EXIT;
384 END IF;
385
387 LOOP
388 BEGIN
389
390 IF p_action_on_entity = 'KEEP_LATEST_UPDATED' THEN
391 --Select the record with max dup_last_update_date
392
393 OPEN max_last_date(p_batch_id, p_entity, l_winner_record_os(i), l_winner_record_osr(i));
394 FETCH max_last_date INTO
395 l_rec_os, l_rec_osr ;
396 CLOSE max_last_date;
397
398 ELSIF p_action_on_entity = 'KEEP_LATEST_CREATED' THEN
399 --Select the record with max dup_creation_date
400
401 OPEN max_created_date(p_batch_id, p_entity, l_winner_record_os(i), l_winner_record_osr(i));
402 FETCH max_created_date INTO
403 l_rec_os, l_rec_osr ;
404 CLOSE max_created_date;
405
406 ELSIF p_action_on_entity = 'KEEP_EARLIEST_CREATED' THEN
407 --Select the record with earliest creation date
408
409 OPEN min_created_date(p_batch_id, p_entity, l_winner_record_os(i), l_winner_record_osr(i));
410 FETCH min_created_date INTO
411 l_rec_os, l_rec_osr ;
412 CLOSE min_created_date;
413
414 END IF;
415
416 --Set the winner record
417
418 UPDATE HZ_IMP_INT_DEDUP_RESULTS
419 SET WINNER_RECORD_OS = l_rec_os ,WINNER_RECORD_OSR = l_rec_osr
420 WHERE batch_id = p_batch_id
421 AND entity = p_entity
422 AND WINNER_RECORD_OS = l_winner_record_os(i)
423 AND WINNER_RECORD_OSR = l_winner_record_osr(i) ;
424
425 --Remove the duplicate records
426 --Hz_parties
427 IF p_entity = 'PARTY' THEN
428
429 UPDATE HZ_IMP_PARTIES_INT party
430 SET INTERFACE_STATUS = 'R'
431 WHERE batch_id = p_batch_id
432 AND( party_orig_system, party_orig_system_reference ) in
433 ( select dup_record_os, dup_record_osr
434 FROM HZ_IMP_INT_DEDUP_RESULTS result
435 WHERE result.batch_id = p_batch_id
436 AND result.entity = 'PARTY'
437 AND result.dup_record_osr <> l_rec_osr
438 AND WINNER_RECORD_OS = l_rec_os
439 AND WINNER_RECORD_OSR = l_rec_osr );
440
441 ELSIF p_entity = 'PARTY_SITES' THEN
442 UPDATE HZ_IMP_ADDRESSES_INT
443 SET INTERFACE_STATUS = 'R'
444 WHERE batch_id = p_batch_id
445 AND( site_orig_system, site_orig_system_reference ) in
446 ( select dup_record_os, dup_record_osr
447 FROM HZ_IMP_INT_DEDUP_RESULTS result
448 WHERE result.batch_id = p_batch_id
449 AND result.entity = 'PARTY_SITES'
450 AND result.dup_record_osr <> l_rec_osr
451 AND WINNER_RECORD_OS = l_rec_os
452 AND WINNER_RECORD_OSR = l_rec_osr ) ;
453
454 ELSIF p_entity = 'CONTACTS' THEN
455 UPDATE HZ_IMP_CONTACTS_INT
456 SET interface_status = 'R'
457 WHERE batch_id = p_batch_id
458 AND ( contact_orig_system,contact_orig_system_reference ) in
459 ( select dup_record_os, dup_record_osr
460 FROM HZ_IMP_INT_DEDUP_RESULTS result
461 WHERE result.batch_id = p_batch_id
462 AND result.entity = 'CONTACTS'
463 AND result.dup_record_osr <> l_rec_osr
464 AND WINNER_RECORD_OS = l_rec_os
465 AND WINNER_RECORD_OSR = l_rec_osr ) ;
466
467 ELSIF p_entity = 'CONTACT_POINTS' THEN
468 UPDATE HZ_IMP_CONTACTPTS_INT
469 SET interface_status = 'R'
470 WHERE batch_id = p_batch_id
471 AND ( cp_orig_system, cp_orig_system_reference ) in
472 ( select dup_record_os, dup_record_osr
473 FROM HZ_IMP_INT_DEDUP_RESULTS result
474 WHERE result.batch_id = p_batch_id
475 AND result.entity = 'CONTACT_POINTS'
476 AND result.dup_record_osr <> l_rec_osr
477 AND WINNER_RECORD_OS = l_rec_os
478 AND WINNER_RECORD_OSR = l_rec_osr ) ;
479
480 END IF;
481
482 END;
483 END LOOP; -- End of For loop
484
485 IF l_last_fetch_result = TRUE THEN
486 EXIT;
487 END IF;
488
489 COMMIT;
490
491 END LOOP;
492 CLOSE sel_dup_set;
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 NULL;
497 END;
498
499 END IF; -- //End of actions
500
501 IF p_entity = 'PARTY' THEN
502
503 --Mark the child records to be removed
504 Begin
505 OPEN get_party_rec(p_batch_id ) ;
506 LOOP
510
507 FETCH get_party_rec BULK COLLECT INTO
508 l_record_os, l_record_osr
509 LIMIT commit_counter;
511 IF get_party_rec%NOTFOUND THEN
512 l_last_fetch := TRUE;
513 END IF;
514
515 IF l_record_osr.COUNT = 0 AND l_last_fetch THEN
516 EXIT;
517 END IF;
518
519 -- Start of Bug No: 3770319
520 --Update site records
521 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
522 UPDATE HZ_IMP_ADDRESSES_INT
523 SET interface_status = 'R'
524 WHERE batch_id = p_batch_id
525 AND party_orig_system = l_record_os(i)
526 AND party_orig_system_reference = l_record_osr(i);
527
528 --Update site uses
529 /*FORALL i in l_record_osr.FIRST..l_record_osr.LAST
530 UPDATE HZ_IMP_ADDRESSUSES_INT
531 SET interface_status = 'R'
532 WHERE batch_id = p_batch_id
533 AND party_orig_system = l_record_os(i)
534 AND party_orig_system_reference = l_record_osr(i);
535 */
536 -- Doing this here is redundant as it will be done during p_entity = 'PARTY_SITES' call.
537 --Update contact records
538 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
539 UPDATE HZ_IMP_CONTACTS_INT
540 SET interface_status = 'R'
541 WHERE batch_id = p_batch_id
542 AND ((sub_orig_system = l_record_os(i)
543 AND sub_orig_system_reference = l_record_osr(i))
544 OR
545 (obj_orig_system = l_record_os(i)
546 AND obj_orig_system_reference = l_record_osr(i))
547 );
548 -- Update contact roles
549 -- Doing this here is redundant as it will be done during p_entity = 'CONTACTS' call.
550 -- Update contact point records
551 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
552 UPDATE HZ_IMP_CONTACTPTS_INT
553 SET interface_status = 'R'
554 WHERE batch_id = p_batch_id
555 AND party_orig_system = l_record_os(i)
556 AND party_orig_system_reference = l_record_osr(i);
557
558 -- Update relationship records
559 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
560 UPDATE HZ_IMP_RELSHIPS_INT
561 SET interface_status = 'R'
562 WHERE batch_id = p_batch_id
563 AND ((sub_orig_system = l_record_os(i)
564 AND sub_orig_system_reference = l_record_osr(i))
565 OR
566 (obj_orig_system = l_record_os(i)
567 AND obj_orig_system_reference = l_record_osr(i))
568 );
569
570 -- End of Bug No: 3770319
571
572 --Update Classifications
573 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
574 UPDATE HZ_IMP_CLASSIFICS_INT
575 SET interface_status = 'R'
576 WHERE batch_id = p_batch_id
577 AND party_orig_system = l_record_os(i)
578 AND party_orig_system_reference = l_record_osr(i);
579
580 --Update Credit Ratings
581 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
582 UPDATE HZ_IMP_CREDITRTNGS_INT
583 SET interface_status = 'R'
584 WHERE batch_id = p_batch_id
585 AND party_orig_system = l_record_os(i)
586 AND party_orig_system_reference = l_record_osr(i);
587
588 --Update Financial Numbers
589 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
590 UPDATE HZ_IMP_FINNUMBERS_INT
591 SET interface_status = 'R'
592 WHERE batch_id = p_batch_id
593 AND party_orig_system = l_record_os(i)
594 AND party_orig_system_reference = l_record_osr(i);
595
596 --Update Financial Reports
597 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
598 UPDATE HZ_IMP_FINREPORTS_INT
599 SET interface_status = 'R'
600 WHERE batch_id = p_batch_id
601 AND party_orig_system = l_record_os(i)
602 AND party_orig_system_reference = l_record_osr(i);
603
604 IF l_last_fetch = TRUE THEN
605 EXIT;
606 END IF;
607
608 COMMIT;
612
609
610 END LOOP; --Hz_parties
611 CLOSE get_party_rec;
613 EXCEPTION
614 WHEN OTHERS THEN
615 NULL;
616 END;
617
618 ELSIF p_entity = 'PARTY_SITES' THEN
619
620 --Mark the child records to be removed
621 BEGIN
622 OPEN get_site_rec(p_batch_id );
623 LOOP
624 FETCH get_site_rec BULK COLLECT INTO
625 l_record_os, l_record_osr
626 LIMIT commit_counter;
627
628 IF get_site_rec%NOTFOUND THEN
629 l_last_fetch := TRUE;
630 END IF;
631
632 IF l_record_osr.COUNT = 0 AND l_last_fetch THEN
633 EXIT;
634 END IF;
635
636 --Update Site Uses
637 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
638 UPDATE HZ_IMP_ADDRESSUSES_INT
639 SET interface_status = 'R'
640 WHERE batch_id = p_batch_id
641 AND site_orig_system = l_record_os(i)
642 AND site_orig_system_reference = l_record_osr(i);
643
644 IF l_last_fetch = TRUE THEN
645 EXIT;
646 END IF;
647
648 commit;
649 END LOOP;
650 close get_site_rec;
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 NULL;
655 END;
656
657
658 ELSIF p_entity = 'CONTACTS' THEN
659
660 --Mark the child records to be removed
661 BEGIN
662 OPEN get_contacts_rec(p_batch_id );
663 LOOP
664 FETCH get_contacts_rec BULK COLLECT INTO
665 l_record_os, l_record_osr
666 LIMIT commit_counter;
667
668 IF get_contacts_rec%NOTFOUND THEN
669 l_last_fetch := TRUE;
670 END IF;
671
672 IF l_record_osr.COUNT = 0 AND l_last_fetch THEN
673 EXIT;
674 END IF;
675
676 --//Update contact roles
677 FORALL i in l_record_osr.FIRST..l_record_osr.LAST
678 UPDATE HZ_IMP_CONTACTROLES_INT
679 SET interface_status = 'R'
680 WHERE batch_id = p_batch_id
681 AND contact_orig_system = l_record_os(i)
682 AND contact_orig_system_reference = l_record_osr(i);
683
684 IF l_last_fetch = TRUE THEN
685 EXIT;
686 END IF;
687
688 commit;
689 END LOOP;
690 close get_contacts_rec;
691
692 EXCEPTION
693 WHEN OTHERS THEN
694 NULL;
695 END;
696 END IF;
697
698 END action_on_entities;
699
700 /**
701 * PRIVATE PROCEDURE update_int_tables
702 *
703 * DESCRIPTION
704 * private procedure to update the interface_status of
705 * interface tables.
706 *
707 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
708 *
709 * ARGUMENTS
710 * IN:
711 * p_batch_id Interface Batch ID.
712 * p_tab_name Interface table name.
713 * p_orig_system Orig System of the duplicate record in
714 * Interface table.
715 * p_orig_system_reference Orig system reference of the duplicate
716 * record in Interface table.
717 * p_dup_os_val Orig System Value
718 * p_dup_osr_val Orig system reference Value.
719 * p_action_on_entity Action on entities.
720 *
721 * MODIFICATION HISTORY
722 *
723 * 08-17-2003 Rajeshwari P o Created.
724 *
725 */
726 /* Commented out for bug 4673725. Also removed the lines that caused
727 SQL literal problem to avoid false positive.
728 PROCEDURE update_int_tables (
729 p_batch_id IN NUMBER,
730 p_tab_name IN VARCHAR2,
731 p_orig_system IN VARCHAR2,
732 p_orig_system_osr IN VARCHAR2,
733 p_dup_os_val IN VARCHAR2,
734 p_dup_osr_val IN VARCHAR2,
735 p_action_on_entity IN VARCHAR2
736 ) IS
737 l_debug_prefix VARCHAR2(30) := '';
738 BEGIN
739
740 -- Check if API is called in debug mode. If yes, enable debug.
741 --enable_debug;
742
743 -- Debug info.
744 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
745 hz_utility_v2pub.debug(p_message=>'update interface tables (+)',
746 p_prefix=>l_debug_prefix,
747 p_msg_level=>fnd_log.level_procedure);
748 END IF;
749
750 -- Debug info.
751 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
752 hz_utility_v2pub.debug(p_message=>'update interface tables (-)',
753 p_prefix=>l_debug_prefix,
754 p_msg_level=>fnd_log.level_procedure);
755 END IF;
756
757 -- Check if API is called in debug mode. If yes, disable debug.
758 --disable_debug;
759
760 EXCEPTION
761 WHEN OTHERS THEN
765 */
762 NULL;
763
764 END update_int_tables;
766
767 /**
768 * PRIVATE PROCEDURE reg_action_on_party
769 *
770 * DESCRIPTION
771 * private procedure to update the interface tables with
772 * appropriate actions after DQM has performed registry
773 * de-duplication.
774 *
775 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
776 *
777 * ARGUMENTS
778 * IN:
779 * p_batch_id Interface Batch ID.
780 * p_action_new_parties Action on new parties.
781 * Insert - Default
782 * Remove - remove from parties interface table
783 * and all its child entities.
784 * p_action__existing_parties Action on existing parties.
785 * Update - Default
786 * Remove - remove from parties interface table
787 * and all its child entities.
788 * p_action_dup_parties Action on duplicate parties.
789 * Auto Merge - Default
790 * Request Merge
791 * Insert
792 * Remove
793 * p_action_pot_dup_parties Action on potential duplicates.
794 * Request Merge - Default
795 * Insert
796 * Remove
797 *
798 * OUT:
799 * x_return_status Return status after the call.
800 *
801 * MODIFICATION HISTORY
802 *
803 * 08-25-2003 Rajeshwari P o Created.
804 *
805 */
806
807 PROCEDURE reg_action_on_party( p_batch_id IN NUMBER,
808 p_action_new_parties IN VARCHAR2,
809 p_action_existing_parties IN VARCHAR2,
810 p_action_dup_parties IN VARCHAR2,
811 p_action_pot_dup_parties IN VARCHAR2,
812 x_return_status OUT NOCOPY VARCHAR2
813 ) IS
814
815 new_party_sql VARCHAR2(4000);
816 existing_party_sql VARCHAR2(4000);
817 dup_party_sql VARCHAR2(4000);
818 pot_dup_party_sql VARCHAR2(4000);
819 cur_sql VARCHAR2(4000);
820
821
822 BEGIN
823
824 -- Initialize API return status to success.
825 x_return_status := FND_API.G_RET_STS_SUCCESS;
826
827 -- Choose the action to be performed
828
829 new_party_sql := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
830 'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
831 'where int.batch_id = :p_batch_id ' ||
832 'and int.batch_id = stage.batch_id '||
833 'and int.rowid = stage.int_row_id '||
834 'and int.dqm_action_flag IS NULL '||
835 'AND stage.action_flag = ''I'' ';
836
837 existing_party_sql := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
838 'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
839 'where int.batch_id = :p_batch_id '||
840 'and int.batch_id = stage.batch_id '||
841 'and int.rowid = stage.int_row_id '||
842 'AND stage.action_flag = ''U'' ';
843
844 dup_party_sql := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
845 'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
846 'where int.batch_id = :p_batch_id '||
847 'and int.batch_id = stage.batch_id '||
848 'and int.rowid = stage.int_row_id '||
849 'and int.dqm_action_flag = ''D'' ' ||
850 'AND stage.action_flag = ''I'' ';
851
852 pot_dup_party_sql := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
853 'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
854 'where int.batch_id = :p_batch_id '||
855 'and int.batch_id = stage.batch_id '||
856 'and int.rowid = stage.int_row_id '||
857 'and int.dqm_action_flag = ''P'' '||
858 'AND stage.action_flag = ''I'' ';
859
860 IF ( p_action_new_parties IS NOT NULL ) THEN
861 action_on_parties(new_party_sql,p_batch_id,p_action_new_parties,NULL,NULL,NULL,x_return_status);
862 END IF;
863
864 IF ( p_action_existing_parties IS NOT NULL ) THEN
865 action_on_parties(existing_party_sql,p_batch_id,NULL,p_action_existing_parties,NULL,NULL,x_return_status);
866 END IF;
867
868 IF ( p_action_dup_parties IS NOT NULL ) THEN
869 action_on_parties(dup_party_sql,p_batch_id,NULL,NULL,p_action_dup_parties,NULL,x_return_status);
870 END IF;
871
872 IF ( p_action_pot_dup_parties IS NOT NULL ) THEN
873 action_on_parties(pot_dup_party_sql,p_batch_id,NULL,NULL,NULL,p_action_pot_dup_parties,x_return_status);
874 END IF;
875
876 EXCEPTION
877 WHEN OTHERS THEN
881
878 x_return_status := FND_API.G_RET_STS_ERROR;
879
880 END reg_action_on_party;
882 /**
883 * PRIVATE PROCEDURE reg_action_on_sites
884 *
885 * DESCRIPTION
886 * private procedure to update the interface tables with
887 * appropriate actions after DQM has performed registry
888 * de-duplication.
889 *
890 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
891 *
892 * ARGUMENTS
893 * IN:
894 * p_batch_id Interface Batch ID.
895 * p_action_new_addrs Action on new sites.
896 * Insert - Default
897 * Remove - remove from Address interface table
898 * and all its child entities.
899 * p_action__existing_addrs Action on existing sites.
900 * Update - Default
901 * Remove - remove from parties interface table
902 * and all its child entities.
903 * p_action_pot_dup_addrs Action on potential duplicates.
904 * Request Merge - Default
905 * Insert
906 * Remove
907 *
908 * OUT:
909 * x_return_status Return status after the call.
910 *
911 * MODIFICATION HISTORY
912 *
913 * 08-25-2003 Rajeshwari P o Created.
914 *
915 */
916
917 PROCEDURE reg_action_on_sites ( p_batch_id IN NUMBER,
918 p_action_new_addrs IN VARCHAR2,
919 p_action_existing_addrs IN VARCHAR2,
920 p_action_pot_dup_addrs IN VARCHAR2,
921 x_return_status OUT NOCOPY VARCHAR2
922 ) IS
923
924 new_site_sql VARCHAR2(4000);
925 existing_site_sql VARCHAR2(4000);
926 pot_dup_site_sql VARCHAR2(4000);
927 cur_sql VARCHAR2(4000);
928
929 BEGIN
930
931 -- Initialize API return status to success.
932 x_return_status := FND_API.G_RET_STS_SUCCESS;
933
934 -- Choose the action to be performed on sites
935
936 new_site_sql := 'select int.site_orig_system,int.site_orig_system_reference '||
937 'from hz_imp_addresses_int int, hz_imp_addresses_sg stage '||
938 'where int.batch_id = :p_batch_id ' ||
939 'and int.batch_id = stage.batch_id '||
940 'and int.rowid = stage.int_row_id '||
941 'and int.dqm_action_flag IS NULL '||
942 'AND stage.action_flag = ''I'' ';
943
944 existing_site_sql := 'select int.site_orig_system,int.site_orig_system_reference '||
945 'from hz_imp_addresses_int int, hz_imp_addresses_sg stage '||
946 'where int.batch_id = :p_batch_id '||
947 'and int.batch_id = stage.batch_id '||
948 'and int.rowid = stage.int_row_id '||
949 'AND stage.action_flag = ''U'' ';
950
951 pot_dup_site_sql := 'select int.site_orig_system,int.site_orig_system_reference '||
952 'from hz_imp_addresses_int int, hz_imp_addresses_sg stage '||
953 'where int.batch_id = :p_batch_id '||
954 'and int.batch_id = stage.batch_id '||
955 'and int.rowid = stage.int_row_id '||
956 'and int.dqm_action_flag = ''P'' '||
957 'AND stage.action_flag = ''I'' ';
958
959 IF ( p_action_new_addrs IS NOT NULL ) THEN
960 action_on_sites(new_site_sql,p_batch_id,p_action_new_addrs,NULL,NULL,x_return_status);
961 END IF;
962
963 IF ( p_action_existing_addrs IS NOT NULL ) THEN
964 action_on_sites(existing_site_sql,p_batch_id,NULL,p_action_existing_addrs,NULL,x_return_status);
965 END IF;
966
967 IF ( p_action_pot_dup_addrs IS NOT NULL ) THEN
968 action_on_sites(pot_dup_site_sql,p_batch_id, NULL,NULL,p_action_pot_dup_addrs,x_return_status);
969 END IF;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 x_return_status := FND_API.G_RET_STS_ERROR;
974 END reg_action_on_sites;
975
976 /**
977 * PRIVATE PROCEDURE reg_action_on_cont
978 *
979 * DESCRIPTION
980 * private procedure to update the interface tables with
981 * appropriate actions after DQM has performed registry
982 * de-duplication.
983 *
984 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
985 *
986 * ARGUMENTS
987 * IN:
988 * p_batch_id Interface Batch ID.
989 * p_action_new_contacts Action on new contacts.
990 * Insert - Default
991 * Remove - remove from Contact interface table
992 * and all its child entities.
993 * p_action__existing_contacts Action on existing contacts.
994 * Update - Default
995 * Remove - remove from parties interface table
996 * and all its child entities.
997 * p_action_pot_dup_contacts Action on potential duplicates.
998 * Request Merge - Default
1002 * OUT:
999 * Insert
1000 * Remove
1001 *
1003 * x_return_status Return status after the call.
1004 *
1005 * MODIFICATION HISTORY
1006 *
1007 * 08-25-2003 Rajeshwari P o Created.
1008 *
1009 */
1010
1011 PROCEDURE reg_action_on_cont ( p_batch_id IN NUMBER,
1012 p_action_new_contacts IN VARCHAR2,
1013 p_action_existing_contacts IN VARCHAR2,
1014 p_action_pot_dup_contacts IN VARCHAR2,
1015 x_return_status OUT NOCOPY VARCHAR2
1016 ) IS
1017
1018 new_cont_sql VARCHAR2(4000);
1019 existing_cont_sql VARCHAR2(4000);
1020 pot_dup_cont_sql VARCHAR2(4000);
1021 cur_sql VARCHAR2(4000);
1022
1023 BEGIN
1024
1025 -- Initialize API return status to success.
1026 x_return_status := FND_API.G_RET_STS_SUCCESS;
1027
1028 -- Choose the action to be performed on sites
1029
1030 new_cont_sql := 'select int.contact_orig_system,int.contact_orig_system_reference '||
1031 'from hz_imp_contacts_int int, hz_imp_contacts_sg stage '||
1032 'where int.batch_id = :p_batch_id ' ||
1033 'and int.batch_id = stage.batch_id '||
1034 'and int.rowid= stage.int_row_id '||
1035 'and int.dqm_action_flag IS NULL '||
1036 'AND stage.action_flag = ''I'' ';
1037
1038 existing_cont_sql := 'select int.contact_orig_system,int.contact_orig_system_reference '||
1039 'from hz_imp_contacts_int int, hz_imp_contacts_sg stage '||
1040 'where int.batch_id = :p_batch_id '||
1041 'and int.batch_id = stage.batch_id '||
1042 'and int.rowid= stage.int_row_id '||
1043 'AND stage.action_flag = ''U'' ';
1044
1045 pot_dup_cont_sql := 'select int.contact_orig_system,int.contact_orig_system_reference '||
1046 'from hz_imp_contacts_int int, hz_imp_contacts_sg stage '||
1047 'where int.batch_id = :p_batch_id '||
1048 'and int.batch_id = stage.batch_id '||
1049 'and int.rowid= stage.int_row_id '||
1050 'and int.dqm_action_flag = ''P'' '||
1051 'AND stage.action_flag = ''I'' ';
1052
1053 IF ( p_action_new_contacts IS NOT NULL ) THEN
1054 action_on_contacts(new_cont_sql,p_batch_id,p_action_new_contacts,NULL,NULL,x_return_status);
1055 END IF;
1056
1057 IF ( p_action_existing_contacts IS NOT NULL ) THEN
1058 action_on_contacts(existing_cont_sql,p_batch_id,NULL,p_action_existing_contacts,NULL,x_return_status);
1059 END IF;
1060
1061 IF ( p_action_pot_dup_contacts IS NOT NULL ) THEN
1062 action_on_contacts(pot_dup_cont_sql,p_batch_id,NULL,NULL,p_action_pot_dup_contacts,x_return_status);
1063 END IF;
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 END reg_action_on_cont ;
1069
1070
1071 /**
1072 * PRIVATE PROCEDURE reg_action_on_cont
1073 *
1074 * DESCRIPTION
1075 * private procedure to update the interface tables with
1076 * appropriate actions after DQM has performed registry
1077 * de-duplication.
1078 *
1079 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1080 *
1081 * ARGUMENTS
1082 * IN:
1083 * p_batch_id Interface Batch ID.
1084 * p_action_new_cpts Action on new contacts.
1085 * Insert - Default
1086 * Remove - remove from Contact interface table
1087 * and all its child entities.
1088 * p_action__existing_cpts Action on existing contacts.
1089 * Update - Default
1090 * Remove - remove from parties interface table
1091 * and all its child entities.
1092 * p_action_pot_dup_cpts Action on potential duplicates.
1093 * Request Merge - Default
1094 * Insert
1095 * Remove
1096 *
1097 * OUT:
1098 * x_return_status Return status after the call.
1099 *
1100 * MODIFICATION HISTORY
1101 *
1102 * 08-25-2003 Rajeshwari P o Created.
1103 *
1104 */
1105 PROCEDURE reg_action_on_cpts ( p_batch_id IN NUMBER,
1106 p_action_new_cpts IN VARCHAR2,
1107 p_action_existing_cpts IN VARCHAR2,
1108 p_action_pot_dup_cpts IN VARCHAR2,
1109 x_return_status OUT NOCOPY VARCHAR2
1110 ) IS
1111
1112 new_cpts_sql VARCHAR2(4000);
1113 existing_cpts_sql VARCHAR2(4000);
1114 pot_dup_cpts_sql VARCHAR2(4000);
1115 cur_sql VARCHAR2(4000);
1116
1117 BEGIN
1118
1119 -- Initialize API return status to success.
1120 x_return_status := FND_API.G_RET_STS_SUCCESS;
1121
1122 -- Choose the action to be performed on sites
1123
1127 'and int.batch_id = stage.batch_id '||
1124 new_cpts_sql := 'select int.cp_orig_system,int.cp_orig_system_reference '||
1125 'from hz_imp_contactpts_int int, hz_imp_contactpts_sg stage '||
1126 'where int.batch_id = :p_batch_id ' ||
1128 'and int.rowid = stage.int_row_id '||
1129 'and int.dqm_action_flag IS NULL '||
1130 'AND stage.action_flag = ''I'' ';
1131
1132 existing_cpts_sql := 'select int.cp_orig_system,int.cp_orig_system_reference '||
1133 'from hz_imp_contactpts_int int, hz_imp_contactpts_sg stage '||
1134 'where int.batch_id = :p_batch_id '||
1135 'and int.batch_id = stage.batch_id '||
1136 'and int.rowid = stage.int_row_id '||
1137 'AND stage.action_flag = ''U'' ';
1138
1139 pot_dup_cpts_sql := 'select int.cp_orig_system,int.cp_orig_system_reference '||
1140 'from hz_imp_contactpts_int int, hz_imp_contactpts_sg stage '||
1141 'where int.batch_id = :p_batch_id '||
1142 'and int.batch_id = stage.batch_id '||
1143 'and int.rowid = stage.int_row_id '||
1144 'and int.dqm_action_flag = ''P'' '||
1145 'AND stage.action_flag = ''I'' ';
1146
1147 IF ( p_action_new_cpts IS NOT NULL ) THEN
1148 action_on_contactpts(new_cpts_sql,p_batch_id,p_action_new_cpts,NULL,NULL,x_return_status);
1149 END IF;
1150
1151 IF ( p_action_existing_cpts IS NOT NULL ) THEN
1152 action_on_contactpts(existing_cpts_sql,p_batch_id,NULL,p_action_existing_cpts,NULL,x_return_status);
1153 END IF;
1154
1155 IF ( p_action_pot_dup_cpts IS NOT NULL ) THEN
1156 action_on_contactpts(pot_dup_cpts_sql,p_batch_id,NULL,NULL,p_action_pot_dup_cpts,x_return_status);
1157 END IF;
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 x_return_status := FND_API.G_RET_STS_ERROR;
1162 END reg_action_on_cpts ;
1163
1164 /**
1165 * PRIVATE PROCEDURE reg_action_on_supents
1166 *
1167 * DESCRIPTION
1168 * private procedure to update the interface tables with
1169 * appropriate actions after DQM has performed registry
1170 * de-duplication.
1171 *
1172 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1173 *
1174 * ARGUMENTS
1175 * IN:
1176 *
1177 *
1178 * OUT:
1179 * x_return_status Return status after the call.
1180 *
1181 * MODIFICATION HISTORY
1182 *
1183 * 08-26-2003 Rajeshwari P o Created.
1184 *
1185 */
1186
1187 PROCEDURE reg_action_on_supents (x_return_status OUT NOCOPY VARCHAR2) is
1188
1189 TYPE sel_cur IS REF CURSOR;
1190 sql_stmt sel_cur;
1191
1192 sel_use VARCHAR2(4000);
1193 sel_class VARCHAR2(4000);
1194 sel_roles VARCHAR2(4000);
1195 sel_rel VARCHAR2(4000);
1196
1197 TYPE INT_ROWIDList IS TABLE OF VARCHAR2(1000);
1198 l_int_rowid INT_ROWIDList;
1199 commit_counter NUMBER;
1200 l_last_fetch BOOLEAN;
1201 i NUMBER;
1202 j NUMBER;
1203
1204
1205 BEGIN
1206
1207 -- Initialize API return status to success.
1208 x_return_status := FND_API.G_RET_STS_SUCCESS;
1209
1210 commit_counter := 1000;
1211
1212 --// Site uses
1213 BEGIN
1214
1215 FOR j in 1..2 LOOP
1216
1217 if j=0 then
1218 sel_use := 'SELECT int_row_id FROM HZ_IMP_ADDRESSUSES_SG WHERE action_flag = ''I'' ';
1219 else
1220 sel_use := 'SELECT int_row_id FROM HZ_IMP_ADDRESSUSES_SG WHERE action_flag = ''U'' ';
1221 end if;
1222
1223 OPEN sql_stmt FOR sel_use;
1224 LOOP
1225 FETCH sql_stmt BULK COLLECT INTO
1226 l_int_rowid
1227 LIMIT commit_Counter;
1228
1229 IF sql_stmt%NOTFOUND THEN
1230 l_last_fetch := TRUE ;
1231 END IF;
1232
1233 IF l_int_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1234 EXIT;
1235 END IF;
1236
1237 FORALL i in l_int_rowid.FIRST..l_int_rowid.LAST
1238 UPDATE HZ_IMP_ADDRESSUSES_INT
1239 SET interface_status = 'R'
1240 WHERE rowid = l_int_rowid(i) ;
1241
1242 IF l_last_fetch = TRUE THEN
1243 EXIT;
1244 END IF;
1245
1246 commit;
1247 END LOOP;
1248 close sql_stmt ;
1249
1250 END LOOP ;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 x_return_status := FND_API.G_RET_STS_ERROR;
1255 END;
1256
1257 --//CLASSIFICATIONS
1258
1259 BEGIN
1260
1261 FOR j in 1..2 LOOP
1262
1263 if j=0 then
1264 sel_class := 'SELECT int_row_id FROM HZ_IMP_CLASSIFICS_SG WHERE action_flag = ''I'' ';
1265 else
1266 sel_class := 'SELECT int_row_id FROM HZ_IMP_CLASSIFICS_SG WHERE action_flag = ''U'' ';
1267 end if;
1268
1269 OPEN sql_stmt FOR sel_class;
1270 LOOP
1271 FETCH sql_stmt BULK COLLECT INTO
1272 l_int_rowid
1273 LIMIT commit_Counter;
1274
1275 IF sql_stmt%NOTFOUND THEN
1276 l_last_fetch := TRUE ;
1277 END IF;
1278
1282
1279 IF l_int_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1280 EXIT;
1281 END IF;
1283 FORALL i in l_int_rowid.FIRST..l_int_rowid.LAST
1284 UPDATE HZ_IMP_CLASSIFICS_INT
1285 SET interface_status = 'R'
1286 WHERE rowid = l_int_rowid(i) ;
1287
1288 IF l_last_fetch = TRUE THEN
1289 EXIT;
1290 END IF;
1291
1292 commit;
1293 END LOOP;
1294 close sql_stmt ;
1295
1296 END LOOP ;
1297
1298 EXCEPTION
1299 WHEN OTHERS THEN
1300 x_return_status := FND_API.G_RET_STS_ERROR;
1301 END;
1302
1303
1304 --//Contact Roles
1305
1306 BEGIN
1307
1308 FOR j in 1..2 LOOP
1309
1310 if j=0 then
1311 sel_roles := 'SELECT int_row_id FROM HZ_IMP_CONTACTROLES_SG WHERE action_flag = ''I'' ';
1312 else
1313 sel_roles := 'SELECT int_row_id FROM HZ_IMP_CONTACTROLES_SG WHERE action_flag = ''U'' ';
1314 end if;
1315
1316 OPEN sql_stmt FOR sel_roles;
1317 LOOP
1318 FETCH sql_stmt BULK COLLECT INTO
1319 l_int_rowid
1320 LIMIT commit_Counter;
1321
1322 IF sql_stmt%NOTFOUND THEN
1323 l_last_fetch := TRUE ;
1324 END IF;
1325
1326 IF l_int_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1327 EXIT;
1328 END IF;
1329
1330 FORALL i in l_int_rowid.FIRST..l_int_rowid.LAST
1331 UPDATE HZ_IMP_CONTACTROLES_INT
1332 SET interface_status = 'R'
1333 WHERE rowid = l_int_rowid(i) ;
1334
1335 IF l_last_fetch = TRUE THEN
1336 EXIT;
1337 END IF;
1338
1339 commit;
1340 END LOOP;
1341 close sql_stmt ;
1342
1343 END LOOP ;
1344
1345 EXCEPTION
1346 WHEN OTHERS THEN
1347 x_return_status := FND_API.G_RET_STS_ERROR;
1348 END;
1349
1350 --//Relationships
1351
1352 BEGIN
1353
1354 FOR j in 1..2 LOOP
1355
1356 if j=0 then
1357 sel_rel := 'SELECT int_row_id FROM HZ_IMP_RELSHIPS_SG WHERE action_flag = ''I'' ';
1358 else
1359 sel_rel := 'SELECT int_row_id FROM HZ_IMP_RELSHIPS_SG WHERE action_flag = ''U'' ';
1360 end if;
1361
1362 OPEN sql_stmt FOR sel_roles;
1363 LOOP
1364 FETCH sql_stmt BULK COLLECT INTO
1365 l_int_rowid
1366 LIMIT commit_Counter;
1367
1368 IF sql_stmt%NOTFOUND THEN
1369 l_last_fetch := TRUE ;
1370 END IF;
1371
1372 IF l_int_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1373 EXIT;
1374 END IF;
1375
1376 FORALL i in l_int_rowid.FIRST..l_int_rowid.LAST
1377 UPDATE HZ_IMP_RELSHIPS_INT
1378 SET interface_status = 'R'
1379 WHERE rowid = l_int_rowid(i) ;
1380
1381 IF l_last_fetch = TRUE THEN
1382 EXIT;
1383 END IF;
1384
1385 commit;
1386 END LOOP;
1387 close sql_stmt ;
1388
1389 END LOOP ;
1390
1391 EXCEPTION
1392 WHEN OTHERS THEN
1393 x_return_status := FND_API.G_RET_STS_ERROR;
1394 END;
1395
1396
1397 END reg_action_on_supents;
1398
1399
1400 /**
1401 * PRIVATE PROCEDURE reg_action_on_finents
1402 *
1403 * DESCRIPTION
1404 * private procedure to update the interface tables with
1405 * appropriate actions after DQM has performed registry
1406 * de-duplication.
1407 *
1408 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1409 *
1410 * ARGUMENTS
1411 * IN:
1412 *
1413 *
1414 * OUT:
1415 * x_return_status Return status after the call.
1416 *
1417 * MODIFICATION HISTORY
1418 *
1419 * 08-25-2003 Rajeshwari P o Created.
1420 *
1421 */
1422
1423 PROCEDURE reg_action_on_finents (
1424 x_return_status OUT NOCOPY VARCHAR2
1425 ) is
1426
1427 TYPE sel_cur IS REF CURSOR;
1428 sql_stmt sel_cur;
1429
1430 sel_finreports VARCHAR2(4000);
1431 sel_finnumbers VARCHAR2(4000);
1432 sel_credit VARCHAR2(4000);
1433
1434 TYPE INT_ROWIDList IS TABLE OF VARCHAR2(1000);
1435 l_int_rowid INT_ROWIDList;
1436 commit_counter NUMBER;
1437 l_last_fetch BOOLEAN;
1438 i NUMBER;
1439 j NUMBER;
1440
1441 BEGIN
1442
1443 -- Initialize API return status to success.
1444 x_return_status := FND_API.G_RET_STS_SUCCESS;
1445
1446 commit_counter := 1000;
1447
1448 --// Financial Reports
1449 BEGIN
1450
1451 -- Run the loop twice, once for Insert and another time for Update
1452 FOR j in 1..2 LOOP
1453
1454 if j=1 then
1455 sel_finreports := 'SELECT int_row_id FROM HZ_IMP_FINREPORTS_SG WHERE action_flag = ''I'' ';
1456 else
1457 sel_finreports := 'SELECT int_row_id FROM HZ_IMP_FINREPORTS_SG WHERE action_flag = ''U'' ';
1458 end if;
1459
1460 OPEN sql_stmt FOR sel_finreports;
1461 LOOP
1462 FETCH sql_stmt BULK COLLECT INTO
1466 IF sql_stmt%NOTFOUND THEN
1463 l_int_rowid
1464 LIMIT commit_Counter;
1465
1467 l_last_fetch := TRUE ;
1468 END IF;
1469
1470 IF l_int_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1471 EXIT;
1472 END IF;
1473
1474 FORALL i in l_int_rowid.FIRST..l_int_rowid.LAST
1475 UPDATE HZ_IMP_FINREPORTS_INT
1476 SET interface_status = 'R'
1477 WHERE rowid = l_int_rowid(i) ;
1478
1479 IF l_last_fetch = TRUE THEN
1480 EXIT;
1481 END IF;
1482
1483 commit;
1484 END LOOP;
1485 close sql_stmt ;
1486
1487 END LOOP ;
1488
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 x_return_status := FND_API.G_RET_STS_ERROR;
1492 END;
1493
1494 --//Financial Numbers
1495 BEGIN
1496
1497 FOR j in 1..2 LOOP
1498
1499 if j=0 then
1500 sel_finnumbers := 'SELECT int_row_id FROM HZ_IMP_FINNUMBERS_SG WHERE action_flag = ''I'' ';
1501 else
1502 sel_finnumbers := 'SELECT int_row_id FROM HZ_IMP_FINNUMBERS_SG WHERE action_flag = ''U'' ';
1503 end if;
1504
1505 OPEN sql_stmt FOR sel_finnumbers;
1506 LOOP
1507 FETCH sql_stmt BULK COLLECT INTO
1508 l_int_rowid
1509 LIMIT commit_Counter;
1510
1511 IF sql_stmt%NOTFOUND THEN
1512 l_last_fetch := TRUE ;
1513 END IF;
1514
1515 IF l_int_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1516 EXIT;
1517 END IF;
1518
1519 FORALL i in l_int_rowid.FIRST..l_int_rowid.LAST
1520 UPDATE HZ_IMP_FINNUMBERS_INT
1521 SET interface_status = 'R'
1522 WHERE rowid = l_int_rowid(i) ;
1523
1524 IF l_last_fetch = TRUE THEN
1525 EXIT;
1526 END IF;
1527
1528 commit;
1529 END LOOP;
1530 close sql_stmt ;
1531
1532 END LOOP ;
1533
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 x_return_status := FND_API.G_RET_STS_ERROR;
1537 END;
1538
1539
1540 --//Credit Ratings
1541
1542 BEGIN
1543
1544 FOR j in 1..2 LOOP
1545
1546 if j=0 then
1547 sel_credit := 'SELECT int_row_id FROM HZ_IMP_CREDITRTNGS_SG WHERE action_flag = ''I'' ';
1548 else
1549 sel_credit := 'SELECT int_row_id FROM HZ_IMP_CREDITRTNGS_SG WHERE action_flag = ''U'' ';
1550 end if;
1551
1552 OPEN sql_stmt FOR sel_credit;
1553 LOOP
1554 FETCH sql_stmt BULK COLLECT INTO
1555 l_int_rowid
1556 LIMIT commit_Counter;
1557
1558 IF sql_stmt%NOTFOUND THEN
1559 l_last_fetch := TRUE ;
1560 END IF;
1561
1562 IF l_int_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1563 EXIT;
1564 END IF;
1565
1566 FORALL i in l_int_rowid.FIRST..l_int_rowid.LAST
1567 UPDATE HZ_IMP_CREDITRTNGS_INT
1568 SET interface_status = 'R'
1569 WHERE rowid = l_int_rowid(i) ;
1570
1571 IF l_last_fetch = TRUE THEN
1572 EXIT;
1573 END IF;
1574
1575 commit;
1576 END LOOP;
1577 close sql_stmt ;
1578
1579 END LOOP ;
1580
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 x_return_status := FND_API.G_RET_STS_ERROR;
1584 END;
1585
1586 END reg_action_on_finents;
1587
1588 --------------------------------------
1589 -- declaration of public procedures and functions
1590 --------------------------------------
1591 /**
1592 * PROCEDURE clear_status
1593 *
1594 * DESCRIPTION
1595 * Clear the interface_status and dqm_action_flag of the interface
1596 * tables.
1597 *
1598 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1599 *
1600 * ARGUMENTS
1601 * IN:
1602 * p_batch_id Interface Batch ID.
1603 *
1604 * OUT:
1605 * x_return_status Return status after the call. The status can
1606 * be FND_API.G_RET_STS_SUCCESS (success),
1607 * FND_API.G_RET_STS_ERROR (error).
1608 * NOTES
1609 *
1610 * MODIFICATION HISTORY
1611 *
1612 * 08-17-2003 Rajeshwari P o Created.
1613 *
1614 */
1615
1616 PROCEDURE clear_status (
1617 p_batch_id IN NUMBER,
1618 x_return_status OUT NOCOPY VARCHAR2
1619 ) IS
1620 Cursor select_party_rec(p_batch_id IN NUMBER ,p_entity IN VARCHAR2 ) is
1621 select dup_record_os,dup_record_osr
1622 FROM HZ_IMP_INT_DEDUP_RESULTS
1623 WHERE batch_id = p_batch_id
1624 AND ENTITY = p_entity
1625 UNION
1626 SELECT party_osr,party_os
1627 FROM HZ_IMP_DUP_PARTIES
1628 WHERE batch_id= p_batch_id
1629 ;
1630
1631 Cursor select_detail_rec(p_batch_id IN NUMBER ,p_entity IN VARCHAR2 ) is
1632 select dup_record_os,dup_record_osr
1633 FROM HZ_IMP_INT_DEDUP_RESULTS
1634 WHERE batch_id = p_batch_id
1635 AND ENTITY = p_entity
1636 UNION
1637 SELECT record_os,record_osr
1638 FROM HZ_IMP_DUP_DETAILS
1639 WHERE batch_id= p_batch_id
1640 AND entity= p_entity
1641 ;
1642
1646 l_dup_osr_party L_DUP_OSRPartyList ;
1643 TYPE L_DUP_OSPartyList is TABLE OF HZ_IMP_PARTIES_INT.party_orig_system%TYPE;
1644 l_dup_os_party L_DUP_OSPartyList;
1645 TYPE L_DUP_OSRPartyList is TABLE OF HZ_IMP_PARTIES_INT.party_orig_system_reference%TYPE;
1647 TYPE L_DUP_OSSitesList is TABLE OF HZ_IMP_ADDRESSES_INT.site_orig_system%TYPE;
1648 l_dup_os_sites L_DUP_OSSitesList;
1649 TYPE L_DUP_OSRSitesList is TABLE OF HZ_IMP_ADDRESSES_INT.site_orig_system_reference%TYPE;
1650 l_dup_osr_sites L_DUP_OSRSitesList;
1651 TYPE L_DUP_OSContList is TABLE OF HZ_IMP_CONTACTS_INT.contact_orig_system%TYPE;
1652 l_dup_os_cont L_DUP_OSContList;
1653 TYPE L_DUP_OSRContList is TABLE OF HZ_IMP_CONTACTS_INT.contact_orig_system_reference%TYPE;
1654 l_dup_osr_cont L_DUP_OSRContList;
1655 TYPE L_DUP_OSCptsList is TABLE OF HZ_IMP_CONTACTPTS_INT.cp_orig_system%TYPE;
1656 l_dup_os_cp L_DUP_OSCptsList;
1657 TYPE L_DUP_OSRCptsList is TABLE OF HZ_IMP_CONTACTPTS_INT.cp_orig_system_reference%TYPE;
1658 l_dup_osr_cp L_DUP_OSRCptsList;
1659
1660 l_last_fetch BOOLEAN;
1661 commit_counter NUMBER;
1662 l_debug_prefix VARCHAR2(30) := '';
1663 BEGIN
1664
1665 commit_counter := 1000;
1666
1667 ---Check if API is called in debug mode. If yes, enable debug.
1668 --enable_debug;
1669
1670 -- Debug info.
1671 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1672 hz_utility_v2pub.debug(p_message=>'clear status of interface tables (+) ',
1673 p_prefix=>l_debug_prefix,
1674 p_msg_level=>fnd_log.level_procedure);
1675 END IF;
1676
1677 -- Initialize API return status to success.
1678 x_return_status := FND_API.G_RET_STS_SUCCESS;
1679
1680 -- For entity= Party
1681 Begin
1682 Open select_party_rec( p_batch_id , 'PARTY' ) ;
1683 LOOP
1684
1685 Fetch select_party_rec BULK COLLECT into
1686 l_dup_os_party,l_dup_osr_party
1687 LIMIT commit_counter;
1688
1689 IF select_party_rec%NOTFOUND THEN
1690 l_last_fetch := TRUE;
1691 END IF;
1692
1693 IF l_dup_osr_party.COUNT = 0 AND l_last_fetch THEN
1694 EXIT;
1695 END IF;
1696
1697
1698 FORALL i in l_dup_osr_party.FIRST..l_dup_osr_party.LAST
1699 UPDATE HZ_IMP_PARTIES_INT
1700 SET interface_status = NULL,dqm_action_flag = NULL
1701 WHERE batch_id = p_batch_id
1702 AND party_orig_system = l_dup_os_party(i)
1703 AND party_orig_system_reference = l_dup_osr_party(i)
1704 AND ( interface_status = 'R' OR dqm_action_flag is not NULL );
1705
1706 --Classifications
1707 FORALL i in l_dup_osr_party.FIRST..l_dup_osr_party.LAST
1708 UPDATE HZ_IMP_CLASSIFICS_INT
1709 SET interface_status = NULL
1710 WHERE batch_id = p_batch_id
1711 AND party_orig_system = l_dup_os_party(i)
1712 AND party_orig_system_reference = l_dup_osr_party(i)
1713 AND interface_status = 'R' ;
1714
1715 --Credit Ratings
1716 FORALL i in l_dup_osr_party.FIRST..l_dup_osr_party.LAST
1717 UPDATE HZ_IMP_CREDITRTNGS_INT
1718 SET interface_status = NULL
1719 WHERE batch_id = p_batch_id
1720 AND party_orig_system = l_dup_os_party(i)
1721 AND party_orig_system_reference = l_dup_osr_party(i)
1722 AND interface_status = 'R' ;
1723
1724 --Financial Numbers
1725 FORALL i in l_dup_osr_party.FIRST..l_dup_osr_party.LAST
1726 UPDATE HZ_IMP_FINNUMBERS_INT
1727 SET interface_status = NULL
1728 WHERE batch_id = p_batch_id
1729 AND party_orig_system = l_dup_os_party(i)
1730 AND party_orig_system_reference = l_dup_osr_party(i)
1731 AND interface_status = 'R' ;
1732
1733 --Financial Reports
1734 FORALL i in l_dup_osr_party.FIRST..l_dup_osr_party.LAST
1735 UPDATE HZ_IMP_FINREPORTS_INT
1736 SET interface_status = NULL
1737 WHERE batch_id = p_batch_id
1738 AND party_orig_system = l_dup_os_party(i)
1739 AND party_orig_system_reference = l_dup_osr_party(i)
1740 AND interface_status = 'R' ;
1741
1742 IF l_last_fetch = TRUE THEN
1743 EXIT;
1744 END IF;
1745
1746 commit;
1747 END LOOP;
1748 close select_party_rec;
1749
1750 END;
1751
1752 --For entity= PARTY_SITES
1753 BEGIN
1754 l_last_fetch := FALSE;
1755
1756 Open select_detail_rec ( p_batch_id ,'PARTY_SITES' );
1757 LOOP
1758
1759 FETCH select_detail_rec BULK COLLECT INTO
1760 l_dup_os_sites,l_dup_osr_sites
1761 LIMIT commit_counter;
1762
1763 IF select_detail_rec%NOTFOUND THEN
1764 l_last_fetch := TRUE;
1765 END IF;
1766
1767 IF l_dup_osr_sites.COUNT = 0 AND l_last_fetch THEN
1768 EXIT;
1769 END IF;
1770
1771 FORALL i in l_dup_osr_sites.FIRST..l_dup_osr_sites.LAST
1772 UPDATE HZ_IMP_ADDRESSES_INT
1773 SET interface_status = NULL,dqm_action_flag = NULL
1774 WHERE batch_id = p_batch_id
1778
1775 AND site_orig_system = l_dup_os_sites(i)
1776 AND site_orig_system_reference = l_dup_osr_sites(i)
1777 AND ( interface_status = 'R' OR dqm_action_flag is not NULL );
1779 --Party site Uses
1780
1781 FORALL i in l_dup_osr_sites.FIRST..l_dup_osr_sites.LAST
1782 UPDATE HZ_IMP_ADDRESSUSES_INT
1783 SET interface_status = NULL
1784 WHERE batch_id = p_batch_id
1785 AND site_orig_system = l_dup_os_sites(i)
1786 AND site_orig_system_reference = l_dup_osr_sites(i)
1787 AND interface_status = 'R' ;
1788
1789 IF l_last_fetch = TRUE THEN
1790 EXIT;
1791 END IF;
1792
1793 commit;
1794 END LOOP;
1795 close select_detail_rec;
1796
1797 END;
1798
1799 --For entity=Contacts
1800 BEGIN
1801 l_last_fetch := FALSE;
1802
1803 Open select_detail_rec ( p_batch_id ,'CONTACTS' );
1804 LOOP
1805
1806 FETCH select_detail_rec BULK COLLECT INTO
1807 l_dup_os_cont,l_dup_osr_cont
1808 LIMIT commit_counter;
1809
1810 IF select_detail_rec%NOTFOUND THEN
1811 l_last_fetch := TRUE;
1812 END IF;
1813
1814 IF l_dup_osr_cont.COUNT = 0 AND l_last_fetch THEN
1815 EXIT;
1816 END IF;
1817
1818 FORALL i in l_dup_osr_cont.FIRST..l_dup_osr_cont.LAST
1819 UPDATE HZ_IMP_CONTACTS_INT
1820 SET interface_status = NULL,dqm_action_flag = NULL
1821 WHERE batch_id = p_batch_id
1822 AND contact_orig_system = l_dup_os_cont(i)
1823 AND contact_orig_system_reference = l_dup_osr_cont(i)
1824 AND ( interface_status = 'R' OR dqm_action_flag is not NULL );
1825
1826 --Contact Roles
1827 FORALL i in l_dup_osr_cont.FIRST..l_dup_osr_cont.LAST
1828 UPDATE HZ_IMP_CONTACTROLES_INT
1829 SET interface_status = NULL
1830 WHERE batch_id = p_batch_id
1831 AND contact_orig_system = l_dup_os_cont(i)
1832 AND contact_orig_system_reference = l_dup_osr_cont(i)
1833 AND interface_status = 'R' ;
1834
1835 IF l_last_fetch = TRUE THEN
1836 EXIT;
1837 END IF;
1838
1839 commit;
1840 END LOOP;
1841 close select_detail_rec;
1842
1843 END;
1844
1845 --For entity=Contact points
1846
1847 BEGIN
1848 l_last_fetch := FALSE;
1849
1850 Open select_detail_rec ( p_batch_id ,'CONTACT_POINTS' );
1851 LOOP
1852
1853 FETCH select_detail_rec BULK COLLECT INTO
1854 l_dup_os_cp,l_dup_osr_cp
1855 LIMIT commit_counter;
1856
1857 IF select_detail_rec%NOTFOUND THEN
1858 l_last_fetch := TRUE;
1859 END IF;
1860
1861 IF l_dup_osr_cp.COUNT = 0 AND l_last_fetch THEN
1862 EXIT;
1863 END IF;
1864
1865 FORALL i in l_dup_osr_cp.FIRST..l_dup_osr_cp.LAST
1866 UPDATE HZ_IMP_CONTACTPTS_INT
1867 SET interface_status = NULL,dqm_action_flag = NULL
1868 WHERE batch_id = p_batch_id
1869 AND cp_orig_system = l_dup_os_cp(i)
1870 AND cp_orig_system_reference = l_dup_osr_cp(i)
1871 AND ( interface_status = 'R' OR dqm_action_flag is not NULL );
1872
1873 IF l_last_fetch = TRUE THEN
1874 EXIT;
1875 END IF;
1876
1877 commit;
1878 END LOOP;
1879 close select_detail_rec;
1880
1881 END;
1882
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 x_return_status := FND_API.G_RET_STS_ERROR;
1886
1887 END clear_status;
1888
1889 /**
1890 *PROCEDURE batch_dedup_action
1891 *
1892 * DESCRIPTION
1893 * Mark the interface_status in the interface tables
1894 * with 'R' to indicate which records should be removed from
1895 * processing by Data Load program.
1896 *
1897 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1898 *
1899 * ARGUMENTS
1900 * IN:
1901 * p_batch_id Batch ID from batch summary table.
1902 * p_action_on_parties Action to be taken on duplicate party records
1903 * in the interface tables.
1904 * p_action_on_addresses Action to be taken on duplicate site records
1905 * in the interface tables.
1906 * p_action_on_contacts Action to be taken on duplicate contact records
1907 * in the interface tables.
1908 * p_action_on_contact_points Action to be taken on duplicate contact point
1909 * records in the interface tables.
1910 *
1911 * OUT:
1912 * x_return_status Return status after the call. The status can
1913 * be FND_API.G_RET_STS_SUCCESS (success),
1914 * FND_API.G_RET_STS_ERROR (error),
1915 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1916 * x_msg_count Number of messages in message stack.
1917 * x_msg_data Message text if x_msg_count is 1..
1918 * NOTES
1919 *
1920 * MODIFICATION HISTORY
1921 *
1922 * 08-17-2003 Rajeshwari P o Created.
1923 *
1927 p_batch_id IN NUMBER,
1924 */
1925
1926 PROCEDURE batch_dedup_action (
1928 p_action_on_parties IN VARCHAR2,
1929 p_action_on_addresses IN VARCHAR2,
1930 p_action_on_contacts IN VARCHAR2,
1931 p_action_on_contact_points IN VARCHAR2,
1932 x_return_status OUT NOCOPY VARCHAR2,
1933 x_msg_count OUT NOCOPY NUMBER,
1934 x_msg_data OUT NOCOPY VARCHAR2
1935 ) IS
1936
1937 /*Cursor action( p_batch_id IN NUMBER ) IS
1938 SELECT BD_ACTION_ON_PARTIES,BD_ACTION_ON_ADDRESSES,
1939 BD_ACTION_ON_CONTACTS,BD_ACTION_ON_CONTACT_POINTS
1940 FROM hz_imp_batch_summary
1941 WHERE batch_id = p_batch_id;
1942
1943 l_action_on_parties hz_imp_batch_summary.BD_ACTION_ON_PARTIES%TYPE;
1944 l_action_on_addresses hz_imp_batch_summary.BD_ACTION_ON_ADDRESSES%TYPE;
1945 l_action_on_contacts hz_imp_batch_summary.BD_ACTION_ON_CONTACTS%TYPE;
1946 l_action_on_cont_points hz_imp_batch_summary.BD_ACTION_ON_CONTACT_POINTS%TYPE;
1947 */
1948 l_debug_prefix VARCHAR2(30) := '';
1949
1950
1951 BEGIN
1952
1953
1954 ---Check if API is called in debug mode. If yes, enable debug.
1955 --enable_debug;
1956 -- Debug info.
1957 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1958 hz_utility_v2pub.debug(p_message=>'update the interface tables after batch deduplication (+) ',
1959 p_prefix=>l_debug_prefix,
1960 p_msg_level=>fnd_log.level_procedure);
1961 END IF;
1962
1963 -- Initialize API return status to success.
1964 x_return_status := FND_API.G_RET_STS_SUCCESS;
1965 --3585887 Commented the code to fetch the previous actions.
1966 /*OPEN action( p_batch_id );
1967 FETCH action INTO
1968 l_action_on_parties,l_action_on_addresses,
1969 l_action_on_contacts,l_action_on_cont_points ;
1970 CLOSE action;*/
1971
1972
1973 action_on_entities ( p_batch_id , 'PARTY' , p_action_on_parties );
1974
1975 -- Update hz_imp_batch_summary table
1976 UPDATE HZ_IMP_BATCH_SUMMARY
1977 SET BD_ACTION_ON_PARTIES = p_action_on_parties
1978 WHERE batch_id = p_batch_id;
1979
1980
1981
1982
1983 action_on_entities ( p_batch_id , 'PARTY_SITES', p_action_on_addresses ) ;
1984
1985 --Update hz_imp_batch_summary table
1986 UPDATE HZ_IMP_BATCH_SUMMARY
1987 SET BD_ACTION_ON_ADDRESSES = p_action_on_addresses
1988 WHERE batch_id = p_batch_id;
1989
1990
1991
1992
1993 action_on_entities ( p_batch_id , 'CONTACTS',p_action_on_contacts );
1994
1995 --Update hz_imp_batch_summary table
1996 UPDATE HZ_IMP_BATCH_SUMMARY
1997 SET BD_ACTION_ON_CONTACTS = p_action_on_contacts
1998 WHERE batch_id = p_batch_id;
1999
2000
2001
2002
2003 action_on_entities ( p_batch_id , 'CONTACT_POINTS' , p_action_on_contact_points);
2004
2005 --Update hz_imp_batch_summary table
2006 UPDATE HZ_IMP_BATCH_SUMMARY
2007 SET BD_ACTION_ON_CONTACT_POINTS = p_action_on_contact_points
2008 WHERE batch_id = p_batch_id;
2009
2010
2011
2012 EXCEPTION
2013 WHEN OTHERS THEN
2014 x_return_status := FND_API.G_RET_STS_ERROR;
2015
2016 END batch_dedup_action;
2017
2018 /**
2019 *PROCEDURE registry_dedup_action
2020 *
2021 * DESCRIPTION
2022 * This API will be called to reflect the user defined
2023 * options into the interface tables after DQM has performed
2024 * registry de-duplication.
2025 *
2026 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2027 *
2028 * ARGUMENTS
2029 * IN:
2030 * p_batch_id Interface Batch ID
2031 * p_action_new_parties New Parties,
2032 * p_action_existing_parties Existing parties,
2033 * p_action_dup_parties Dup parties,
2034 * p_action_pot_dup_parties Potential duplicate parties,
2035 * p_action_new_addrs New Address,
2036 * p_action_existing_addrs Existing Address,
2037 * p_action_pot_dup_addrs Potential Duplicate address,
2038 * p_action_new_contacts New Contacts,
2039 * p_action_existing_contacts Existing Contacts,
2040 * p_action_pot_dup_contacts Potential duplicate Contacts,
2041 * p_action_new_cpts New Contact Points,
2042 * p_action_existing_cpts Existing Contact Points,
2043 * p_action_pot_dup_cpts Potential Duplicate Contact Points,
2044 * p_action_new_supents New Supents,
2045 * p_action_existing_supents Existing Supents,
2046 * p_action_new_finents New Finents,
2047 * p_action_existing_finents Existing Finents,
2048 *
2049 * OUT:
2050 * x_return_status Return status after the call. The status can
2051 * be FND_API.G_RET_STS_SUCCESS (success),
2052 * FND_API.G_RET_STS_ERROR (error),
2053 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2054 * x_msg_count Number of messages in message stack.
2055 * x_msg_data Message text if x_msg_count is 1..
2056 * NOTES
2057 *
2061 *
2058 * MODIFICATION HISTORY
2059 *
2060 * 08-17-2003 Rajeshwari P o Created.
2062 */
2063
2064 PROCEDURE registry_dedup_action (
2065 p_batch_id IN NUMBER,
2066 p_action_new_parties IN VARCHAR2,
2067 p_action_existing_parties IN VARCHAR2,
2068 p_action_dup_parties IN VARCHAR2,
2069 p_action_pot_dup_parties IN VARCHAR2,
2070 p_action_new_addrs IN VARCHAR2,
2071 p_action_existing_addrs IN VARCHAR2,
2072 p_action_pot_dup_addrs IN VARCHAR2,
2073 p_action_new_contacts IN VARCHAR2,
2074 p_action_existing_contacts IN VARCHAR2,
2075 p_action_pot_dup_contacts IN VARCHAR2,
2076 p_action_new_cpts IN VARCHAR2,
2077 p_action_existing_cpts IN VARCHAR2,
2078 p_action_pot_dup_cpts IN VARCHAR2,
2079 p_action_new_supents IN VARCHAR2,
2080 p_action_existing_supents IN VARCHAR2,
2081 p_action_new_finents IN VARCHAR2,
2082 p_action_existing_finents IN VARCHAR2,
2083 x_return_status OUT NOCOPY VARCHAR2,
2084 x_msg_count OUT NOCOPY NUMBER,
2085 x_msg_data OUT NOCOPY VARCHAR2
2086 ) IS
2087 l_debug_prefix VARCHAR2(30) := '';
2088
2089 BEGIN
2090
2091 --Check if API is called in debug mode. If yes, enable debug.
2092 --enable_debug;
2093
2094 --Debug info.
2095 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2096 hz_utility_v2pub.debug(p_message=>'update the interface tables after batch deduplication (+) ',
2097 p_prefix=>l_debug_prefix,
2098 p_msg_level=>fnd_log.level_procedure);
2099 END IF;
2100
2101 -- Initialize API return status to success.
2102 x_return_status := FND_API.G_RET_STS_SUCCESS;
2103
2104 -- Choose the action to be performed on parties after registry de-duplication
2105 -- is done by DQM.
2106
2107 reg_action_on_party ( p_batch_id,
2108 p_action_new_parties,
2109 p_action_existing_parties,
2110 p_action_dup_parties,
2111 p_action_pot_dup_parties,
2112 x_return_status
2113 );
2114
2115 --///Action to be performed on Sites
2116
2117 reg_action_on_sites ( p_batch_id ,
2118 p_action_new_addrs ,
2119 p_action_existing_addrs ,
2120 p_action_pot_dup_addrs ,
2121 x_return_status
2122 );
2123
2124 --//Action to be performed on Contacts
2125
2126 reg_action_on_cont ( p_batch_id ,
2127 p_action_new_contacts ,
2128 p_action_existing_contacts ,
2129 p_action_pot_dup_contacts ,
2130 x_return_status
2131 ) ;
2132
2133 --//Action to be performed on Contact points
2134
2135 reg_action_on_cpts ( p_batch_id ,
2136 p_action_new_cpts ,
2137 p_action_existing_cpts ,
2138 p_action_pot_dup_cpts ,
2139 x_return_status
2140 );
2141
2142 --//Action to be performed on Site Uses, Classification, Contact Roles and Relationships
2143
2144 reg_action_on_supents (x_return_status ) ;
2145
2146 --//Action to be performed on Financial Reports, Financial numbers and Credit ratings
2147
2148 reg_action_on_finents (x_return_status ) ;
2149
2150
2151 EXCEPTION
2152 WHEN OTHERS THEN
2153 x_return_status := FND_API.G_RET_STS_ERROR;
2154
2155 END registry_dedup_action;
2156
2157 /**
2158 *FUNCTION GET_DEDUP_BATCH_STATUS
2159 *
2160 * DESCRIPTION
2161 * This API will be called to get the
2162 * status (Import/Remove) of records in
2163 * dedup results based on the action
2164 * in batch summary.
2165 *
2166 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2167 *
2168 * ARGUMENTS
2169 * IN:
2170 * p_batch_id Interface Batch ID
2171 * p_entity Entity Name in Dedup Results,
2172 * p_action_on_entity Action on entity in Batch Summary,
2173 * p_winner_record_os Winner record Orig System in Dedup Results
2174 * p_winner_record_osr Winner record Orig System Reference in Dedup Results
2175 * p_dup_record_os Dup record Orig System in Dedup Results
2176 * p_dup_record_osr Dup record Orig System Reference in Dedup Results
2177 *
2178 *
2179 * NOTES
2180 *
2181 * MODIFICATION HISTORY
2182 *
2183 * 09-30-2003 Ramesh Ch o Created.
2184 *
2185 */
2186
2187 FUNCTION GET_DEDUP_BATCH_STATUS(p_batch_id IN NUMBER,
2188 p_entity IN VARCHAR2,
2189 p_action_on_entity IN VARCHAR2,
2193 p_dup_record_osr IN VARCHAR2
2190 p_winner_record_os IN VARCHAR2,
2191 p_winner_record_osr IN VARCHAR2,
2192 p_dup_record_os IN VARCHAR2,
2194 )
2195 RETURN VARCHAR2
2196 IS
2197 --//Choose the latest updated record
2198 CURSOR max_last_date IS
2199 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
2200 FROM HZ_IMP_INT_DEDUP_RESULTS
2201 WHERE batch_id = p_batch_id
2202 AND entity = p_entity
2203 AND nvl(dup_last_update_date,sysdate) = ( SELECT MAX( nvl(DUP_LAST_UPDATE_DATE,sysdate))
2204 FROM HZ_IMP_INT_DEDUP_RESULTS
2205 WHERE batch_id =p_batch_id
2206 and entity = p_entity
2207 AND winner_record_os = p_winner_record_os
2208 AND winner_record_osr = p_winner_record_osr)
2209 AND winner_record_os = p_winner_record_os
2210 AND winner_record_osr = p_winner_record_osr
2211 AND rownum =1;
2212
2213 --//Choose the latest created record
2214 CURSOR max_created_date IS
2215 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
2216 FROM HZ_IMP_INT_DEDUP_RESULTS
2217 WHERE batch_id = p_batch_id
2218 AND entity = p_entity
2219 AND nvl(dup_creation_date,sysdate) = ( SELECT MAX( nvl(DUP_CREATION_DATE,sysdate))
2220 FROM HZ_IMP_INT_DEDUP_RESULTS
2221 WHERE batch_id =p_batch_id
2222 AND entity = p_entity
2223 AND winner_record_os = p_winner_record_os
2224 AND winner_record_osr = p_winner_record_osr)
2225 AND winner_record_os = p_winner_record_os
2226 AND winner_record_osr = p_winner_record_osr
2227 AND rownum =1;
2228
2229 --//Choose the earliest created record
2230 CURSOR min_created_date IS
2231 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
2232 FROM HZ_IMP_INT_DEDUP_RESULTS
2233 WHERE batch_id = p_batch_id
2234 AND entity = p_entity
2235 AND nvl(dup_creation_date,sysdate) = ( SELECT MIN( nvl(DUP_CREATION_DATE,sysdate))
2236 FROM HZ_IMP_INT_DEDUP_RESULTS
2237 WHERE batch_id =p_batch_id
2238 AND entity = p_entity
2239 AND winner_record_os = p_winner_record_os
2240 AND winner_record_osr = p_winner_record_osr)
2241 AND winner_record_os = p_winner_record_os
2242 AND winner_record_osr = p_winner_record_osr
2243 AND rownum =1;
2244
2245 --//Get the Status for the action
2246 CURSOR c_status(p_lkp_code VARCHAR2) IS
2247 SELECT MEANING FROM FND_LOOKUP_VALUES lkp
2248 WHERE lkp.lookup_code=p_lkp_code
2249 AND lkp.lookup_type='HZ_IMP_BATCH_DEDUP_STATUS'
2250 AND lkp.language = userenv('LANG')
2251 AND lkp.view_application_id = 222
2252 AND lkp.security_group_id =fnd_global.lookup_security_group('HZ_IMP_BATCH_DEDUP_STATUS', 222)
2253 AND rownum=1;
2254
2255 --local variables
2256 l_lkp_code VARCHAR2(30):=NULL;
2257 l_status VARCHAR2(80):=NULL;
2258 l_dup_record_osr VARCHAR2(255):=NULL;
2259 l_dup_record_os VARCHAR2(30):=NULL;
2260
2261 BEGIN
2262
2263 IF p_action_on_entity='KEEP_LATEST_UPDATED' THEN
2264 OPEN max_last_date;
2265 FETCH max_last_date INTO l_dup_record_os,l_dup_record_osr;
2266 CLOSE max_last_date;
2267 ELSIF p_action_on_entity='KEEP_LATEST_CREATED' THEN
2268 OPEN max_created_date;
2269 FETCH max_created_date INTO l_dup_record_os,l_dup_record_osr;
2270 CLOSE max_created_date;
2271 ELSIF p_action_on_entity='KEEP_EARLIEST_CREATED' THEN
2272 OPEN min_created_date;
2273 FETCH min_created_date INTO l_dup_record_os,l_dup_record_osr;
2274 CLOSE min_created_date;
2275 END IF;
2276 IF p_action_on_entity='REMOVE_ALL' THEN
2277 l_lkp_code:='REMOVE';
2278 ELSIF p_action_on_entity='KEEP_ALL' THEN
2279 l_lkp_code:='IMPORT';
2280 ELSE
2281 IF(l_dup_record_os=p_dup_record_os AND l_dup_record_osr=p_dup_record_osr) THEN
2282 l_lkp_code:='IMPORT';
2283 ELSE
2284 l_lkp_code:='REMOVE';
2285 END IF;
2286 END IF;
2287 OPEN c_status(l_lkp_code);
2288 FETCH c_status INTO l_status;
2289 CLOSE c_status;
2290 RETURN l_status;
2291 EXCEPTION WHEN OTHERS THEN
2292 RETURN NULL;
2293 END GET_DEDUP_BATCH_STATUS;
2294
2295 PROCEDURE action_on_parties(p_sql IN VARCHAR2,
2296 p_batch_id IN NUMBER,
2297 p_action_new_parties IN VARCHAR2,
2298 p_action_existing_parties IN VARCHAR2,
2299 p_action_dup_parties IN VARCHAR2,
2300 p_action_pot_dup_parties IN VARCHAR2,
2301 x_return_status OUT NOCOPY VARCHAR2)
2302 IS
2303 sel_parties sel_cur;
2304
2305 TYPE L_PARTY_ORIG_SYSList IS TABLE OF HZ_IMP_PARTIES_INT.PARTY_ORIG_SYSTEM%TYPE;
2306 l_party_orig_os L_PARTY_ORIG_SYSList;
2307 TYPE L_PARTY_ORIG_SYS_REFList IS TABLE OF HZ_IMP_PARTIES_INT.PARTY_ORIG_SYSTEM_REFERENCE%TYPE;
2308 l_party_orig_osr L_PARTY_ORIG_SYS_REFList;
2309 TYPE L_PARTY_IDList IS TABLE OF HZ_IMP_PARTIES_INT.PARTY_ID%TYPE;
2310 l_party_id L_PARTY_IDList;
2311
2312 TYPE L_SITE_ORIG_SYSList IS TABLE OF HZ_IMP_ADDRESSES_INT.SITE_ORIG_SYSTEM%TYPE;
2313 l_site_orig_os L_SITE_ORIG_SYSList;
2314 TYPE L_SITE_ORIG_SYS_REFList IS TABLE OF HZ_IMP_ADDRESSES_INT.SITE_ORIG_SYSTEM_REFERENCE%TYPE;
2315 l_site_orig_osr L_SITE_ORIG_SYS_REFList;
2316 TYPE L_CONT_ORIG_SYSList IS TABLE OF HZ_IMP_CONTACTS_INT.CONTACT_ORIG_SYSTEM%TYPE;
2320
2317 l_cont_orig_os L_CONT_ORIG_SYSList;
2318 TYPE L_CONT_ORIG_SYS_REFList IS TABLE OF HZ_IMP_CONTACTS_INT.CONTACT_ORIG_SYSTEM_REFERENCE%TYPE;
2319 l_cont_orig_osr L_CONT_ORIG_SYS_REFList;
2321 commit_counter NUMBER;
2322 l_last_fetch BOOLEAN;
2323 i NUMBER;
2324
2325 BEGIN
2326 commit_counter := 1000;
2327 OPEN sel_parties FOR p_sql USING p_batch_id;
2328 LOOP
2329 FETCH sel_parties BULK COLLECT INTO
2330 l_party_id, l_party_orig_os, l_party_orig_osr
2331 LIMIT commit_counter;
2332
2333 IF sel_parties%NOTFOUND THEN
2334 l_last_fetch := TRUE ;
2335 END IF;
2336
2337 IF l_party_orig_osr.COUNT = 0 AND l_last_fetch = TRUE THEN
2338 EXIT;
2339 END IF;
2340
2341 IF (p_action_new_parties||p_action_existing_parties||
2342 p_action_dup_parties||p_action_pot_dup_parties
2343 ='REMOVE')
2344 THEN
2345 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2346
2347 --//Update parties interface table
2348 UPDATE HZ_IMP_PARTIES_INT party
2349 SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
2350 WHERE batch_id = p_batch_id
2351 AND ((party_id IS NULL and l_party_id(i) IS NULL)
2352 OR (party_id IS NOT NULL and l_party_id(i) IS NOT NULL and party_id=l_party_id(i)))
2353 AND party_orig_system = l_party_orig_os(i)
2354 AND party_orig_system_reference = l_party_orig_osr(i) ;
2355
2356 --Child entities for Party
2357 -- Classifications
2358
2359 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2360 UPDATE HZ_IMP_CLASSIFICS_INT
2361 SET interface_status = 'R'
2362 WHERE batch_id = p_batch_id
2363 AND party_orig_system = l_party_orig_os(i)
2364 AND party_orig_system_reference = l_party_orig_osr(i) ;
2365
2366 --Credit Ratings
2367
2368 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2369 UPDATE HZ_IMP_CREDITRTNGS_INT
2370 SET interface_status = 'R'
2371 WHERE batch_id = p_batch_id
2372 AND party_orig_system = l_party_orig_os(i)
2373 AND party_orig_system_reference = l_party_orig_osr(i) ;
2374
2375 --Financial Numbers
2376
2377 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2378 UPDATE HZ_IMP_FINNUMBERS_INT
2379 SET interface_status = 'R'
2380 WHERE batch_id = p_batch_id
2381 AND party_orig_system = l_party_orig_os(i)
2382 AND party_orig_system_reference = l_party_orig_osr(i) ;
2383
2384 --Financial Reports
2385
2386 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2387 UPDATE HZ_IMP_FINREPORTS_INT
2388 SET interface_status = 'R'
2389 WHERE batch_id = p_batch_id
2390 AND party_orig_system = l_party_orig_os(i)
2391 AND party_orig_system_reference = l_party_orig_osr(i) ;
2392
2393 IF (p_action_dup_parties <> NULL and
2394 (p_action_dup_parties = 'REMOVE' or p_action_dup_parties = 'INSERT' ) )
2395 or
2396 (p_action_pot_dup_parties <> NULL and
2397 (p_action_pot_dup_parties = 'REMOVE' or p_action_pot_dup_parties = 'INSERT' ) )
2398 THEN
2399
2400 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2401 UPDATE HZ_IMP_DUP_PARTIES
2402 SET auto_merge_flag = 'R'
2403 WHERE batch_id = p_batch_id
2404 AND party_id = l_party_id(i)
2405 AND party_os = l_party_orig_os(i)
2406 AND party_osr = l_party_orig_osr(i) ;
2407
2408 END IF;
2409
2410 -- Addresses
2411
2412 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2413 UPDATE HZ_IMP_ADDRESSES_INT
2414 SET interface_status = 'R'
2415 WHERE batch_id = p_batch_id
2416 AND party_orig_system = l_party_orig_os(i)
2417 AND party_orig_system_reference = l_party_orig_osr(i)
2418 RETURNING site_orig_system,site_orig_system_Reference BULK COLLECT into l_site_orig_os,l_site_orig_osr;
2419
2420 -- Child entitites for Addresses
2421 FORALL i in l_site_orig_osr.FIRST..l_site_orig_osr.LAST
2422 UPDATE HZ_IMP_ADDRESSUSES_INT
2423 SET INTERFACE_STATUS = 'R'
2424 WHERE batch_id = p_batch_id
2425 AND site_orig_system = l_site_orig_os(i)
2426 AND site_orig_system_reference = l_site_orig_osr(i) ;
2427
2428 -- Contact points
2429
2430 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2431 UPDATE HZ_IMP_CONTACTPTS_INT
2432 SET interface_status = 'R'
2433 WHERE batch_id = p_batch_id
2434 AND party_orig_system = l_party_orig_os(i)
2435 AND party_orig_system_reference = l_party_orig_osr(i) ;
2436
2437 -- Relationships
2438
2439 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2440 UPDATE HZ_IMP_RELSHIPS_INT
2441 SET interface_status = 'R'
2442 WHERE batch_id = p_batch_id
2443 AND (sub_orig_system = l_party_orig_os(i)
2444 AND sub_orig_system_reference = l_party_orig_osr(i))
2445 OR
2446 (obj_orig_system = l_party_orig_os(i)
2447 AND obj_orig_system_reference = l_party_orig_osr(i));
2448
2449
2450 -- Contacts
2451 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2452 UPDATE HZ_IMP_CONTACTS_INT
2453 SET interface_status = 'R'
2454 WHERE batch_id = p_batch_id
2458 (obj_orig_system = l_party_orig_os(i)
2455 AND (sub_orig_system = l_party_orig_os(i)
2456 AND sub_orig_system_reference = l_party_orig_osr(i))
2457 OR
2459 AND obj_orig_system_reference = l_party_orig_osr(i))
2463 FORALL i in l_cont_orig_osr.FIRST..l_cont_orig_osr.LAST
2460 RETURNING contact_orig_system,contact_orig_system_reference BULK COLLECT into l_cont_orig_os,l_cont_orig_osr;
2461
2462 --Child entities for Contact
2464 UPDATE HZ_IMP_CONTACTROLES_INT
2465 SET INTERFACE_STATUS = 'R'
2466 WHERE batch_id = p_batch_id
2467 AND contact_orig_system = l_cont_orig_os(i)
2468 AND contact_orig_system_reference = l_cont_orig_osr(i) ;
2469
2470
2471 END IF;
2472
2473 IF ( (p_action_dup_parties <> NULL and p_action_dup_parties = 'INSERT' )
2474 OR
2475 (p_action_pot_dup_parties <> NULL and p_action_pot_dup_parties = 'INSERT' )
2476 )
2477 THEN
2478
2479 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2480 UPDATE HZ_IMP_PARTIES_INT
2481 SET dqm_action_flag = NULL
2482 WHERE batch_id = p_batch_id
2483 AND party_id = l_party_id(i)
2484 AND party_orig_system = l_party_orig_os(i)
2485 AND party_orig_system_reference = l_party_orig_osr(i) ;
2486
2487 ELSIF (p_action_dup_parties <> NULL and p_action_dup_parties = 'REQUEST_MERGE' )
2488 THEN
2489
2490 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2491 UPDATE HZ_IMP_PARTIES_INT
2492 SET dqm_action_flag = 'P'
2493 WHERE batch_id = p_batch_id
2494 AND party_id = l_party_id(i)
2495 AND party_orig_system = l_party_orig_os(i)
2496 AND party_orig_system_reference = l_party_orig_osr(i) ;
2497
2498
2499 FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
2500 UPDATE HZ_IMP_DUP_PARTIES
2501 SET auto_merge_flag = 'N'
2502 WHERE batch_id = p_batch_id
2503 AND party_id = l_party_id(i)
2504 AND party_os = l_party_orig_os(i)
2505 AND party_osr = l_party_orig_osr(i) ;
2506
2507 END IF;
2508
2509 IF l_last_fetch = TRUE THEN
2510 EXIT;
2511 END IF;
2512
2513 COMMIT;
2514
2515 END LOOP;
2516 CLOSE sel_parties;
2517
2518 EXCEPTION
2519 WHEN OTHERS THEN
2520 x_return_status := FND_API.G_RET_STS_ERROR;
2521 END action_on_parties;
2522
2523 PROCEDURE action_on_sites(p_sql IN VARCHAR2,
2524 p_batch_id IN NUMBER,
2525 p_action_new_addrs IN VARCHAR2,
2526 p_action_existing_addrs IN VARCHAR2,
2527 p_action_pot_dup_addrs IN VARCHAR2,
2528 x_return_status OUT NOCOPY VARCHAR2)
2529 IS
2530 sel_addrs sel_cur;
2531
2532 TYPE L_SITE_ORIG_SYSList IS TABLE OF HZ_IMP_ADDRESSES_INT.SITE_ORIG_SYSTEM%TYPE;
2533 l_site_orig_os L_SITE_ORIG_SYSList;
2534 TYPE L_SITE_ORIG_SYS_REFList IS TABLE OF HZ_IMP_ADDRESSES_INT.SITE_ORIG_SYSTEM_REFERENCE%TYPE;
2535 l_site_orig_osr L_SITE_ORIG_SYS_REFList;
2536
2537 commit_counter NUMBER;
2538 l_last_fetch BOOLEAN;
2539 i NUMBER;
2540
2541 BEGIN
2542 commit_counter := 1000;
2543
2544 OPEN sel_addrs FOR p_sql USING p_batch_id;
2545 LOOP
2546 FETCH sel_addrs BULK COLLECT INTO
2547 l_site_orig_os, l_site_orig_osr
2548 LIMIT commit_counter;
2549
2550 IF sel_addrs%NOTFOUND THEN
2551 l_last_fetch := TRUE ;
2552 END IF;
2553
2554 IF l_site_orig_osr.COUNT = 0 AND l_last_fetch = TRUE THEN
2555 EXIT;
2556 END IF;
2557
2558 IF (p_action_new_addrs||p_action_existing_addrs||p_action_pot_dup_addrs='REMOVE')
2559 THEN
2560 FORALL i in l_site_orig_osr.FIRST..l_site_orig_osr.LAST
2561 --//Update addresses interface table
2562 UPDATE HZ_IMP_ADDRESSES_INT
2563 SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
2564 WHERE batch_id = p_batch_id
2565 AND site_orig_system = l_site_orig_os(i)
2566 AND site_orig_system_reference = l_site_orig_osr(i) ;
2567
2568 --Child entities for Party
2569 FORALL i in l_site_orig_osr.FIRST..l_site_orig_osr.LAST
2570 UPDATE HZ_IMP_ADDRESSUSES_INT
2571 SET INTERFACE_STATUS = 'R'
2572 WHERE batch_id = p_batch_id
2573 AND site_orig_system = l_site_orig_os(i)
2574 AND site_orig_system_reference = l_site_orig_osr(i) ;
2575 END IF;
2576
2577 IF (p_action_pot_dup_addrs <> NULL and
2578 (p_action_pot_dup_addrs = 'REMOVE' or p_action_pot_dup_addrs = 'INSERT' ) )
2579 THEN
2580
2581 FORALL i in l_site_orig_osr.FIRST..l_site_orig_osr.LAST
2582 DELETE FROM HZ_IMP_DUP_DETAILS
2583 WHERE batch_id = p_batch_id
2584 AND record_os = l_site_orig_os(i)
2585 AND record_osr = l_site_orig_osr(i) ;
2586
2587 END IF;
2588
2589 IF (p_action_pot_dup_addrs <> NULL and p_action_pot_dup_addrs = 'INSERT' )
2590 THEN
2591
2592 FORALL i in l_site_orig_osr.FIRST..l_site_orig_osr.LAST
2593 UPDATE HZ_IMP_ADDRESSES_INT
2594 SET dqm_action_flag = NULL
2595 WHERE batch_id = p_batch_id
2596 AND site_orig_system = l_site_orig_os(i)
2597 AND site_orig_system_reference = l_site_orig_osr(i) ;
2598
2599 END IF;
2600
2601 IF l_last_fetch = TRUE THEN
2602 EXIT;
2603 END IF;
2604
2605 COMMIT;
2606
2607 END LOOP;
2608 CLOSE sel_addrs;
2609
2610
2611 EXCEPTION
2612 WHEN OTHERS THEN
2613 x_return_status := FND_API.G_RET_STS_ERROR;
2614 END action_on_sites;
2615
2616 PROCEDURE action_on_contacts(p_sql IN VARCHAR2,
2620 p_action_pot_dup_contacts IN VARCHAR2,
2617 p_batch_id IN NUMBER,
2618 p_action_new_contacts IN VARCHAR2,
2619 p_action_existing_contacts IN VARCHAR2,
2621 x_return_status OUT NOCOPY VARCHAR2)
2622 IS
2623 sel_cont sel_cur;
2624
2625 TYPE L_CONT_ORIG_SYSList IS TABLE OF HZ_IMP_CONTACTS_INT.CONTACT_ORIG_SYSTEM%TYPE;
2626 l_cont_orig_os L_CONT_ORIG_SYSList;
2627 TYPE L_CONT_ORIG_SYS_REFList IS TABLE OF HZ_IMP_CONTACTS_INT.CONTACT_ORIG_SYSTEM_REFERENCE%TYPE;
2628 l_cont_orig_osr L_CONT_ORIG_SYS_REFList;
2629
2630 commit_counter NUMBER;
2631 l_last_fetch BOOLEAN;
2632 i NUMBER;
2633
2634 BEGIN
2635 commit_counter := 1000;
2636
2637 OPEN sel_cont FOR p_sql USING p_batch_id;
2638 LOOP
2639 FETCH sel_cont BULK COLLECT INTO
2640 l_cont_orig_os, l_cont_orig_osr
2641 LIMIT commit_counter;
2642
2643 IF sel_cont%NOTFOUND THEN
2644 l_last_fetch := TRUE ;
2645 END IF;
2646
2647 IF l_cont_orig_osr.COUNT = 0 AND l_last_fetch = TRUE THEN
2648 EXIT;
2649 END IF;
2650
2651 IF (p_action_new_contacts||p_action_existing_contacts||p_action_pot_dup_contacts='REMOVE')
2652 THEN
2653 FORALL i in l_cont_orig_osr.FIRST..l_cont_orig_osr.LAST
2654 --//Update contact interface table
2655 UPDATE HZ_IMP_CONTACTS_INT
2656 SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
2657 WHERE batch_id = p_batch_id
2658 AND contact_orig_system = l_cont_orig_os(i)
2659 AND contact_orig_system_reference = l_cont_orig_osr(i) ;
2660
2661 --Child entities for Contact
2662 FORALL i in l_cont_orig_osr.FIRST..l_cont_orig_osr.LAST
2663 UPDATE HZ_IMP_CONTACTROLES_INT
2664 SET INTERFACE_STATUS = 'R'
2665 WHERE batch_id = p_batch_id
2666 AND contact_orig_system = l_cont_orig_os(i)
2667 AND contact_orig_system_reference = l_cont_orig_osr(i) ;
2668 END IF;
2669
2670 IF (p_action_pot_dup_contacts <> NULL and
2671 (p_action_pot_dup_contacts = 'REMOVE' or p_action_pot_dup_contacts = 'INSERT' ) )
2672 THEN
2673
2674 FORALL i in l_cont_orig_osr.FIRST..l_cont_orig_osr.LAST
2675 DELETE FROM HZ_IMP_DUP_DETAILS
2676 WHERE batch_id = p_batch_id
2677 AND record_os = l_cont_orig_os(i)
2678 AND record_osr = l_cont_orig_osr(i) ;
2679
2680 END IF;
2681
2682 IF (p_action_pot_dup_contacts <> NULL and p_action_pot_dup_contacts = 'INSERT' )
2683 THEN
2684
2685 FORALL i in l_cont_orig_osr.FIRST..l_cont_orig_osr.LAST
2686 UPDATE HZ_IMP_CONTACTS_INT
2687 SET dqm_action_flag = NULL
2688 WHERE batch_id = p_batch_id
2689 AND contact_orig_system = l_cont_orig_os(i)
2690 AND contact_orig_system_reference = l_cont_orig_osr(i) ;
2691
2692 END IF;
2693
2694 IF l_last_fetch = TRUE THEN
2695 EXIT;
2696 END IF;
2697
2698 COMMIT;
2699
2700 END LOOP;
2701 CLOSE sel_cont;
2702
2703
2704 EXCEPTION
2705 WHEN OTHERS THEN
2706 x_return_status := FND_API.G_RET_STS_ERROR;
2707 END action_on_contacts;
2708
2709 PROCEDURE action_on_contactpts(p_sql IN VARCHAR2,
2710 p_batch_id IN NUMBER,
2711 p_action_new_cpts IN VARCHAR2,
2712 p_action_existing_cpts IN VARCHAR2,
2713 p_action_pot_dup_cpts IN VARCHAR2,
2714 x_return_status OUT NOCOPY VARCHAR2)
2715 IS
2716 sel_cpts sel_cur;
2717
2718 TYPE L_CPTS_ORIG_SYSList IS TABLE OF HZ_IMP_CONTACTPTS_INT.CP_ORIG_SYSTEM%TYPE;
2719 l_cpts_orig_os L_CPTS_ORIG_SYSList;
2720 TYPE L_CPTS_ORIG_SYS_REFList IS TABLE OF HZ_IMP_CONTACTPTS_INT.CP_ORIG_SYSTEM_REFERENCE%TYPE;
2721 l_cpts_orig_osr L_CPTS_ORIG_SYS_REFList;
2722
2723 commit_counter NUMBER;
2724 l_last_fetch BOOLEAN;
2725 i NUMBER;
2726
2727 BEGIN
2728 commit_counter := 1000;
2729
2730 OPEN sel_cpts FOR p_sql USING p_batch_id;
2731 LOOP
2732 FETCH sel_cpts BULK COLLECT INTO
2733 l_cpts_orig_os, l_cpts_orig_osr
2734 LIMIT commit_counter;
2735
2736 IF sel_cpts%NOTFOUND THEN
2737 l_last_fetch := TRUE ;
2738 END IF;
2739
2740 IF l_cpts_orig_osr.COUNT = 0 AND l_last_fetch THEN
2741 EXIT;
2742 END IF;
2743
2744 IF (p_action_new_cpts||p_action_existing_cpts||p_action_pot_dup_cpts='REMOVE')
2745 THEN
2746 FORALL i in l_cpts_orig_osr.FIRST..l_cpts_orig_osr.LAST
2747 --//Update contact points interface table
2748 UPDATE HZ_IMP_CONTACTPTS_INT
2749 SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
2750 WHERE batch_id = p_batch_id
2751 AND cp_orig_system = l_cpts_orig_os(i)
2752 AND cp_orig_system_reference = l_cpts_orig_osr(i) ;
2753 END IF;
2754
2755 IF (p_action_pot_dup_cpts <> NULL and
2756 (p_action_pot_dup_cpts = 'REMOVE' or p_action_pot_dup_cpts = 'INSERT' ) )
2757 THEN
2758
2759 FORALL i in l_cpts_orig_osr.FIRST..l_cpts_orig_osr.LAST
2760 DELETE FROM HZ_IMP_DUP_DETAILS
2761 WHERE batch_id = p_batch_id
2762 AND record_os = l_cpts_orig_os(i)
2763 AND record_osr = l_cpts_orig_osr(i) ;
2764
2765 END IF;
2766
2767 IF (p_action_pot_dup_cpts <> NULL and p_action_pot_dup_cpts = 'INSERT' )
2768 THEN
2769
2773 WHERE batch_id = p_batch_id
2770 FORALL i in l_cpts_orig_osr.FIRST..l_cpts_orig_osr.LAST
2771 UPDATE HZ_IMP_CONTACTPTS_INT
2772 SET dqm_action_flag = NULL
2774 AND cp_orig_system = l_cpts_orig_os(i)
2775 AND cp_orig_system_reference = l_cpts_orig_osr(i) ;
2776
2777 END IF;
2778
2779 IF l_last_fetch = TRUE THEN
2780 EXIT;
2781 END IF;
2782
2783 COMMIT;
2784
2785 END LOOP;
2786 CLOSE sel_cpts;
2787
2788
2789 EXCEPTION
2790 WHEN OTHERS THEN
2791 x_return_status := FND_API.G_RET_STS_ERROR;
2792 END action_on_contactpts ;
2793
2794 END HZ_BATCH_ACTION_PUB;