[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARCPF
Source
1 PACKAGE BODY ARP_CMERGE_ARCPF as
2 /* $Header: ARPLCPFB.pls 120.3.12010000.2 2009/01/27 10:36:37 vsegu ship $ */
3
4 g_count NUMBER := 0;
5
6 --merge ar_credit_histories
7 procedure ar_ch (
8 req_id NUMBER,
9 set_num NUMBER,
10 process_mode VARCHAR2
11 );
12
13 --merge hz_customer_profiles
14 procedure ar_cp (
15 req_id NUMBER,
16 set_num NUMBER,
17 process_mode VARCHAR2
18 );
19
20 --merge hz_customer_profile_amts
21 procedure ar_cpa (
22 req_id NUMBER,
23 set_num NUMBER,
24 process_mode VARCHAR2
25 );
26
27 /*===========================================================================+
28 | PROCEDURE
29 | merge
30 |
31 | DESCRIPTION
32 | main merge routine.
33 |
34 | SCOPE - PUBLIC
35 |
36 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
37 |
38 | ARGUMENTS : IN:
39 | req_id
40 | set_num
41 | process_mod
42 | IN/ OUT:
43 |
44 | RETURNS : NONE
45 |
46 | NOTES
47 |
48 | MODIFICATION HISTORY
49 | Jianying Huang 20-DEC-00 Bug 1535542: ar_ch procedure works only in
50 | delete mode. Move the call to 'delete_rows'.
51 |
52 +===========================================================================*/
53
54 PROCEDURE merge (
55 req_id NUMBER,
56 set_num NUMBER,
57 process_mode VARCHAR2
58 ) IS
59
60 BEGIN
61
62 arp_message.set_line( 'ARP_CMERGE_ARCPF.MERGE()+' );
63
64 --merge hz_customer_profiles
65 ar_cp( req_id, set_num, process_mode );
66
67 --merge hz_customer_profile_amts
68 ar_cpa( req_id, set_num, process_mode );
69
70 arp_message.set_line( 'ARP_CMERGE_ARCPF.MERGE()-' );
71
72 END merge;
73
74 /*===========================================================================+
75 | PROCEDURE
76 | ar_ch
77 |
78 | DESCRIPTION
79 | merge in AR_CREDIT_HISTORIES
80 |
81 | SCOPE - PRIVATE
82 |
83 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
84 |
85 | ARGUMENTS : IN:
86 | req_id
87 | set_num
88 | process_mod
89 | IN/ OUT:
90 |
91 | RETURNS : NONE
92 |
93 | NOTES
94 |
95 | MODIFICATION HISTORY
96 | Jianying Huang 20-DEC-00 Bug 1535542: ar_ch procedure works only in
97 | delete mode. Move the call to 'delete_rows'.
98 | Jianying Huang 09-APR-00 Bug 1725662: Modified 'ar_ch' to use index.
99 |
100 +===========================================================================*/
101
102 PROCEDURE ar_ch (
103 req_id NUMBER,
104 set_num NUMBER,
105 process_mode VARCHAR2
106 ) IS
107
108 CURSOR c1 is
109 SELECT CREDIT_HISTORY_ID
110 FROM AR_CREDIT_HISTORIES yt, ra_customer_merges m
111 WHERE yt.customer_id = m.duplicate_id
112 AND yt.site_use_id = m.duplicate_site_id
113 AND m.process_flag = 'N'
114 AND m.request_id = req_id
115 AND m.set_number = set_num
116 AND m.delete_duplicate_flag = 'Y'
117 FOR UPDATE NOWAIT;
118
119 CURSOR c2 is
120 SELECT CREDIT_HISTORY_ID
121 FROM AR_CREDIT_HISTORIES yt, ra_customer_merges m
122 WHERE yt.customer_id = m.duplicate_id
123 AND m.process_flag = 'N'
124 AND m.request_id = req_id
125 AND m.set_number = set_num
126 AND m.delete_duplicate_flag = 'Y'
127 AND site_use_id IS NULL
128 AND NOT EXISTS (
129 SELECT 'accounts exist'
130 FROM hz_cust_accounts acct
131 WHERE acct.cust_account_id = yt.customer_id
132 AND acct.status <> 'D' )
133 FOR UPDATE NOWAIT;
134
135 BEGIN
136
137 arp_message.set_line( 'ARP_CMERGE_ARCPF.AR_CH()+' );
138
139 --delete only if delete = 'Y', otherwise leave in as historical data
140 --lock table
141 OPEN c1;
142 CLOSE c1;
143
144 OPEN c2;
145 CLOSE c2;
146
147 --site level delete
148
149 arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
150 arp_message.set_token( 'TABLE_NAME', 'AR_CREDIT_HISTORIES', FALSE );
151
152 --Bug 1725662: Modified 'ar_ch' to use index.
153
154 DELETE FROM AR_CREDIT_HISTORIES yt
155 WHERE (customer_id, site_use_id) IN (
156 SELECT m.duplicate_id, m.duplicate_site_id
157 FROM ra_customer_merges m
158 WHERE m.process_flag = 'N'
159 AND m.request_id = req_id
160 AND m.set_number = set_num
161 AND m.delete_duplicate_flag = 'Y');
162
163 g_count := sql%rowcount;
164
165 arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
166 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
167
168 --customer level delete
169
170 arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
171 arp_message.set_token( 'TABLE_NAME', 'AR_CREDIT_HISTORIES', FALSE );
172
173 DELETE FROM AR_CREDIT_HISTORIES yt
174 WHERE customer_id IN (
175 SELECT m.duplicate_id
176 FROM ra_customer_merges m
177 WHERE m.process_flag = 'N'
178 AND m.request_id = req_id
179 AND m.set_number = set_num
180 AND m.delete_duplicate_flag = 'Y' )
181 AND site_use_id IS NULL
182 AND NOT EXISTS (
183 SELECT 'accounts exist'
184 FROM hz_cust_accounts acct
185 WHERE acct.cust_account_id = yt.customer_id
186 AND acct.status <> 'D' );
187
188 g_count := sql%rowcount;
189
190 arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
191 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
192
193 arp_message.set_line( 'ARP_CMERGE_ARCPF.AR_CH()-' );
194
195 EXCEPTION
196
197 WHEN OTHERS THEN
198 arp_message.set_error( 'ARP_CMERGE_ARCPF.AR_CH');
199 RAISE;
200
201 END ar_ch;
202
203 /*===========================================================================+
204 | PROCEDURE
205 | ar_cp
206 |
207 | DESCRIPTION
208 | merge in HZ_CUSTOMER_PROFILES
209 |
210 | SCOPE - PRIVATE
211 |
212 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
213 |
214 | ARGUMENTS : IN:
215 | req_id
216 | set_num
217 | process_mod
218 | IN/ OUT:
219 |
220 | RETURNS : NONE
221 |
222 | NOTES
223 |
224 | MODIFICATION HISTORY
225 | Jianying Huang 20-DEC-00 Bug 1535542: Since we need to change
226 | the merging order, merge HZ tables before merging
227 | products, we need to mark deleted rows here
228 | first and physically delete them after merging one
229 | set in 'delete_rows'.
230 |
231 +===========================================================================*/
232
233 PROCEDURE ar_cp (
234 req_id NUMBER,
235 set_num NUMBER,
236 process_mode VARCHAR2
237 ) IS
238
239 --cursor c1 and c2 work in inactive mode.
240 CURSOR c1 is
241 SELECT cust_account_profile_id
242 FROM hz_customer_profiles, ra_customer_merges m
243 WHERE site_use_id = m.duplicate_site_id
244 AND m.process_flag = 'N'
245 AND m.request_id = req_id
246 AND m.set_number = set_num
247 FOR UPDATE NOWAIT;
248
249 CURSOR c2 is
250 SELECT cust_account_profile_id
251 FROM hz_customer_profiles yt, ra_customer_merges m
252 WHERE cust_account_id = m.duplicate_id
253 AND m.process_flag = 'N'
254 AND m.request_id = req_id
255 AND m.set_number = set_num
256 AND m.delete_duplicate_flag = 'N'
257 AND site_use_id IS NULL
258 AND NOT EXISTS (
259 SELECT 'active accounts exist'
260 FROM hz_cust_accounts acct
261 WHERE acct.cust_account_id = yt.cust_account_id
262 AND acct.status = 'A')
263 FOR UPDATE NOWAIT;
264
265 --cursor c3 work in 'delete' mode.
266 CURSOR c3 is
267 SELECT cust_account_profile_id
268 FROM hz_customer_profiles yt, ra_customer_merges m
269 WHERE cust_account_id = m.duplicate_id
270 AND m.process_flag = 'N'
271 AND m.request_id = req_id
272 AND m.set_number = set_num
273 AND m.delete_duplicate_flag = 'Y'
274 AND site_use_id IS NULL
275 AND NOT EXISTS (
276 SELECT 'accounts exist'
277 FROM hz_cust_accounts acct
278 WHERE acct.cust_account_id = yt.cust_account_id
279 AND acct.status <> 'D' )
280 FOR UPDATE NOWAIT;
281
282 BEGIN
283
284 arp_message.set_line( 'ARP_CMERGE_ARCPF.AR_CP()+' );
285
286 IF process_mode = 'LOCK' THEN
287
288 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
289 arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
290
291 OPEN c1;
292 CLOSE c1;
293
294 OPEN c2;
295 CLOSE c2;
296
297 OPEN c3;
298 CLOSE c3;
299
300 ELSE
301
302 /*************** 'inactivate' mode ***************/
303
304 --site level inactivate
305 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
306 arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
307
308 UPDATE hz_customer_profiles yt
309 SET status = 'I',
310 last_update_date = sysdate,
311 last_updated_by = hz_utility_v2pub.user_id,
312 last_update_login = hz_utility_v2pub.last_update_login,
313 request_id = req_id,
314 program_application_id = hz_utility_v2pub.program_application_id,
315 program_id = hz_utility_v2pub.program_id,
316 program_update_date = sysdate
317 WHERE site_use_id IN (
318 SELECT m.duplicate_site_id
319 FROM ra_customer_merges m
320 WHERE m.process_flag = 'N'
321 AND m.request_id = req_id
322 AND m.set_number = set_num
323 AND m.delete_duplicate_flag = 'N' ) ;
324
325 g_count := sql%rowcount;
326
327 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
328 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
329
330 --customer level inactivate
331 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
332 arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
333
334 UPDATE hz_customer_profiles yt
335 SET status = 'I',
336 last_update_date = sysdate,
337 last_updated_by = hz_utility_v2pub.user_id,
338 last_update_login = hz_utility_v2pub.last_update_login,
339 request_id = req_id,
340 program_application_id = hz_utility_v2pub.program_application_id,
341 program_id = hz_utility_v2pub.program_id,
342 program_update_date = sysdate
343 WHERE cust_account_id IN (
344 SELECT m.duplicate_id
345 FROM ra_customer_merges m
346 WHERE m.process_flag = 'N'
347 AND m.request_id = req_id
348 AND m.set_number = set_num
349 AND m.delete_duplicate_flag = 'N' )
350 AND site_use_id IS NULL
351 AND NOT EXISTS (
352 SELECT 'active accounts exist'
353 FROM hz_cust_accounts acct
354 WHERE acct.cust_account_id = yt.cust_account_id
355 AND acct.status = 'A');
356
357 g_count := sql%rowcount;
358
359 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
360 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
361
362 /*************** 'delete' mode ***************/
363 --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
364 --Physically delete them after merge.
365
366 --site level 'delete'
367 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
368 arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
369
370 UPDATE hz_customer_profiles
371 SET status = 'D'
372 WHERE site_use_id IN (
373 SELECT m.duplicate_site_id
374 FROM ra_customer_merges m
375 WHERE m.process_flag = 'N'
376 AND m.request_id = req_id
377 AND m.set_number = set_num
378 AND m.delete_duplicate_flag = 'Y' ) ;
379
380 g_count := sql%rowcount;
381
382 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
386 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
383 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
384
385 --customer level 'delete'
387 arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
388
389 UPDATE hz_customer_profiles yt
390 SET status = 'D'
391 WHERE cust_account_id IN (
392 SELECT m.duplicate_id
393 FROM ra_customer_merges m
394 WHERE m.process_flag = 'N'
395 AND m.request_id = req_id
396 AND m.set_number = set_num
397 AND m.delete_duplicate_flag = 'Y' )
398 AND site_use_id IS NULL
399 AND NOT EXISTS (
400 SELECT 'accounts exist'
401 FROM hz_cust_accounts acct
402 WHERE acct.cust_account_id = yt.cust_account_id
403 AND acct.status <> 'D' );
404
405 g_count := sql%rowcount;
406
407 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
408 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
409
410 END IF;
411
412 arp_message.set_line( 'ARP_CMERGE_ARCPF.AR_CP()-' );
413
414 EXCEPTION
415
416 WHEN OTHERS THEN
417 arp_message.set_error( 'ARP_CMERGE_ARCPF.AR_CP');
418 RAISE;
419
420 END ar_cp;
421
422 /*===========================================================================+
423 | PROCEDURE
424 | ar_cpa
425 |
426 | DESCRIPTION
427 | merge in HZ_CUSTOMER_PROFILE_AMTS
428 |
429 | SCOPE - PRIVATE
430 |
431 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
432 |
433 | ARGUMENTS : IN:
434 | req_id
435 | set_num
436 | process_mod
437 | IN/ OUT:
438 |
439 | RETURNS : NONE
440 |
441 | NOTES
442 |
443 | MODIFICATION HISTORY
444 | Jianying Huang 20-DEC-00 Bug 1535542: Since we need to change
445 | the merging order, merge HZ tables before merging
446 | products, we need to move the delete part to
447 | 'delete rows' in which we do physically delete after
448 | merging one set.
449 |
450 +===========================================================================*/
451
452 PROCEDURE ar_cpa (
453 req_id NUMBER,
454 set_num NUMBER,
455 process_mode VARCHAR2
456 ) IS
457
458 --cursor c1 and c2 work in inactive mode.
459 CURSOR c1 is
460 SELECT CUST_ACCT_PROFILE_AMT_ID
461 FROM HZ_CUST_PROFILE_AMTS, ra_customer_merges m
462 WHERE site_use_id = m.duplicate_site_id
463 AND m.process_flag = 'N'
464 AND m.request_id = req_id
465 AND m.set_number = set_num
466 AND m.delete_duplicate_flag = 'N'
467 FOR UPDATE NOWAIT;
468
469 CURSOR c2 is
470 SELECT CUST_ACCT_PROFILE_AMT_ID
471 FROM HZ_CUST_PROFILE_AMTS yt, ra_customer_merges m
472 WHERE cust_account_id = m.duplicate_id
473 AND m.process_flag = 'N'
474 AND m.request_id = req_id
475 AND m.set_number = set_num
476 AND m.delete_duplicate_flag = 'N'
477 AND site_use_id IS NULL
478 AND NOT EXISTS (
479 SELECT 'active accounts exist'
480 FROM hz_cust_accounts acct
481 WHERE acct.cust_account_id = yt.cust_account_id
482 AND acct.status = 'A')
483 FOR UPDATE NOWAIT;
484
485 BEGIN
486
487 arp_message.set_line( 'ARP_CMERGE_ARCPF.AR_CPA()+' );
488
489 IF process_mode = 'LOCK' THEN
490
491 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
492 arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_PROFILE_AMTS', FALSE );
493
494 OPEN c1;
495 CLOSE c1;
496
497 OPEN c2;
498 CLOSE c2;
499
500 ELSE
501
502 /*************** 'inactivate' mode ***************/
503
504 --site level inactivate
505 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
506 arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_PROFILE_AMTS', FALSE );
507
508 UPDATE HZ_CUST_PROFILE_AMTS yt
509 SET last_update_date = sysdate,
510 last_updated_by = hz_utility_v2pub.user_id,
511 last_update_login = hz_utility_v2pub.last_update_login,
512 request_id = req_id,
513 program_application_id = hz_utility_v2pub.program_application_id,
514 program_id = hz_utility_v2pub.program_id,
515 program_update_date = sysdate
516 WHERE site_use_id IN (
517 SELECT m.duplicate_site_id
518 FROM ra_customer_merges m
519 WHERE m.process_flag = 'N'
520 AND m.request_id = req_id
521 AND m.set_number = set_num
522 AND m.delete_duplicate_flag = 'N' );
523
524 g_count := sql%rowcount;
525
526 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
527 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
528
529 --customer level inactivate
530 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
531 arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_PROFILE_AMTS', FALSE );
532
533 UPDATE HZ_CUST_PROFILE_AMTS yt
534 SET last_update_date = sysdate,
535 last_updated_by = hz_utility_v2pub.user_id,
539 program_id = hz_utility_v2pub.program_id,
536 last_update_login = hz_utility_v2pub.last_update_login,
537 request_id = req_id,
538 program_application_id = hz_utility_v2pub.program_application_id,
540 program_update_date = sysdate
541 WHERE cust_account_id IN (
542 SELECT m.duplicate_id
543 FROM ra_customer_merges m
544 WHERE m.process_flag = 'N'
545 AND m.request_id = req_id
546 AND m.set_number = set_num
547 AND m.delete_duplicate_flag = 'N' )
548 AND site_use_id IS NULL
549 AND NOT EXISTS (
550 SELECT 'active accounts exist'
551 FROM hz_cust_accounts acct
552 WHERE acct.cust_account_id = yt.cust_account_id
553 AND acct.status = 'A') ;
554
555 g_count := sql%rowcount;
556
557 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
558 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
559
560 END IF;
561
562 arp_message.set_line( 'ARP_CMERGE_ARCPF.AR_CPA()-' );
563
564 EXCEPTION
565
566 WHEN OTHERS THEN
567 arp_message.set_error( 'ARP_CMERGE_ARCPF.AR_CPA');
568 RAISE;
569
570 END ar_cpa;
571
572 /*===========================================================================+
573 | PROCEDURE
574 | delete_rows
575 |
576 | DESCRIPTION physically delete the rows we marked in customer tables after
577 | we merging each set.
578 |
579 | SCOPE - PUBLIC
580 |
581 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
582 |
583 | ARGUMENTS : IN:
584 | OUT:
585 | IN/ OUT:
586 |
587 | RETURNS : NONE
588 |
589 | NOTES
590 |
591 | MODIFICATION HISTORY
592 | Jianying Huang 20-DEC-00 Created for bug 1535542: physically delete
593 | rows in customer tables after merging each set.
594 | Jianying Huang 29-DEC-00 Modified 'delete_rows' for performance issue.
595 |
596 +===========================================================================*/
597
598 PROCEDURE delete_rows(
599 req_id NUMBER,
600 set_num NUMBER
601 ) IS
602
603 CURSOR profiles IS
604 SELECT cust_account_profile_id
605 FROM HZ_CUSTOMER_PROFILES, ra_customer_merges m
606 WHERE cust_account_id = m.duplicate_id
607 AND m.process_flag = 'N'
608 AND m.request_id = req_id
609 AND m.set_number = set_num
610 AND m.delete_duplicate_flag = 'Y'
611 AND status = 'D'
612 FOR UPDATE NOWAIT;
613
614 CURSOR profile_amt_site IS
615 SELECT CUST_ACCT_PROFILE_AMT_ID
616 FROM HZ_CUST_PROFILE_AMTS, ra_customer_merges m
617 WHERE site_use_id = m.duplicate_site_id
618 AND m.process_flag = 'N'
619 AND m.request_id = req_id
620 AND m.set_number = set_num
621 AND m.delete_duplicate_flag = 'Y'
622 FOR UPDATE NOWAIT;
623
624 CURSOR profile_amt_acct IS
625 SELECT CUST_ACCT_PROFILE_AMT_ID
626 FROM HZ_CUST_PROFILE_AMTS yt, ra_customer_merges m
627 WHERE cust_account_id = m.duplicate_id
628 AND m.process_flag = 'N'
629 AND m.request_id = req_id
630 AND m.set_number = set_num
631 AND m.delete_duplicate_flag = 'Y'
632 AND site_use_id IS NULL
633 AND NOT EXISTS (
634 SELECT 'accounts exist'
635 FROM hz_cust_acct_sites_all acct --SSUptake
636 WHERE acct.cust_account_id = yt.cust_account_id
637 AND acct.org_id = m.org_id --SSUptake
638 AND status <> 'D' )
639
640 FOR UPDATE NOWAIT;
641
642 BEGIN
643
644 arp_message.set_line( 'ARP_CMERGE_ARCPF.delete_rows()+' );
645
646 /*****************************************************/
647
648 --Bug 1535542: Because ar_ch procedure works only in delete mode, we call it here
649 --instead of in 'merge' procedure.
650
651 ar_ch( req_id, set_num, 'DUMMY' );
652
653 /*****************************************************/
654
655 arp_message.set_name( 'AR', 'AR_DELETING_TABLE');
656 arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
657
658 OPEN profiles;
659 CLOSE profiles;
660
661 DELETE FROM HZ_CUSTOMER_PROFILES
662 WHERE cust_account_id IN (
663 SELECT m.duplicate_id
664 FROM ra_customer_merges m
665 WHERE m.process_flag = 'N'
666 AND m.request_id = req_id
667 AND m.set_number = set_num
668 AND m.delete_duplicate_flag = 'Y' )
669 AND status = 'D';
670
671 g_count := sql%rowcount;
672
673 arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
674 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
675
676 /*****************************************************/
677
678 arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
679 arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_PROFILE_AMTS', FALSE );
680
681 OPEN profile_amt_site;
682 CLOSE profile_amt_site;
683
684 OPEN profile_amt_acct;
685 CLOSE profile_amt_acct;
686
687 --site level
688 DELETE FROM HZ_CUST_PROFILE_AMTS yt
689 WHERE site_use_id IN (
690 SELECT m.duplicate_site_id
691 FROM ra_customer_merges m
695 AND m.delete_duplicate_flag = 'Y' );
692 WHERE m.process_flag = 'N'
693 AND m.request_id = req_id
694 AND m.set_number = set_num
696
697 g_count := sql%rowcount;
698
699 arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
700 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
701
702 arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
703 arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_PROFILE_AMTS', FALSE );
704
705 --account level
706 DELETE FROM HZ_CUST_PROFILE_AMTS yt
707 WHERE cust_account_id IN (
708 SELECT m.duplicate_id
709 FROM ra_customer_merges m
710 WHERE m.process_flag = 'N'
711 AND m.request_id = req_id
712 AND m.set_number = set_num
713 AND m.delete_duplicate_flag = 'Y' )
714 AND site_use_id IS NULL
715 AND NOT EXISTS (
716 SELECT 'accounts exist'
717 FROM hz_cust_accounts acct
718 WHERE acct.cust_account_id = yt.cust_account_id
719 AND acct.status <> 'D' );
720
721 g_count := sql%rowcount;
722
723 arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
724 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
725
726 arp_message.set_line( 'ARP_CMERGE_ARCPF.delete_rows()-' );
727
728 EXCEPTION
729
730 WHEN OTHERS THEN
731 arp_message.set_error( 'ARP_CMERGE_ARCPF.delete_rows' );
732 RAISE;
733
734 END delete_rows;
735
736 END ARP_CMERGE_ARCPF;