[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_MASTER
Source
1 PACKAGE BODY ARP_CMERGE_MASTER as
2 /* $Header: ARHCMSTB.pls 120.27.12000000.5 2007/10/12 11:15:14 kguggila ship $ */
3
4 --Global variable to catch the name of product raising exception
5 G_PRODUCT_RAISING_EXCEPTION varchar2(255) ;
6
7 --delete customer's alternative names
8 procedure delete_customer_alt_names(
9 req_id NUMBER,
10 set_num NUMBER
11 );
12
13 --merge crm products.
14 procedure merge_crm_products(
15 req_id NUMBER,
16 set_num NUMBER,
17 process_mode VARCHAR2
18 );
19
20 -- dynamically call ERP merge rountine.
21 procedure merge_product (
22 package_name VARCHAR2,
23 api_name VARCHAR2,
24 req_id NUMBER,
25 set_num NUMBER,
26 process_mode VARCHAR2,
27 product_code VARCHAR2
28 );
29
30 g_excluded_apps VARCHAR2(200);
31
32 --added for debug
33 procedure read_temporary_table;
34
35 --For identifying the bad merge records
36 procedure update_merge_as_failed(
37 p_request_id NUMBER,
38 p_set_num NUMBER,
39 p_customer_merge_header_id NUMBER,
40 p_error_text VARCHAR2
41 );
42
43 -- Called from the Customer Merger Concurrent program Parameter(Operating unit)
44 FUNCTION operating_unit RETURN VARCHAR2 IS
45
46 l_operating_unit VARCHAR2(2000):= NULL;
47 l_count NUMBER:=0;
48 l_exit_count NUMBER:=0;
49 l_temp_variable VARCHAR2(2000):=NULL;
50
51 CURSOR get_operating_unit_csr IS
52 SELECT hr.name
53 FROM hr_operating_units hr
54 WHERE mo_global.check_access(hr.organization_id) = 'Y'
55 ORDER BY hr.name;
56
57 BEGIN
58
59 BEGIN
60 SELECT COUNT(*) INTO l_count
61 FROM hr_operating_units hr
62 WHERE mo_global.check_access(hr.organization_id) = 'Y';
63 EXCEPTION
64 WHEN OTHERS THEN
65 RETURN NULL;
66 END;
67 l_exit_count:=0;
68 l_operating_unit:=NULL;
69 l_temp_variable:=NULL;
70 IF l_count > 5 THEN
71 FOR get_operating_unit_rec IN get_operating_unit_csr LOOP
72 l_operating_unit:=l_operating_unit||get_operating_unit_rec.name;
73 l_exit_count:=l_exit_count+1;
74 IF l_exit_count = 5 THEN
75 l_operating_unit:=l_operating_unit||'...';
76 EXIT;
77 END IF;
78 l_operating_unit:=l_operating_unit||',';
79 END LOOP;
80
81 ELSE
82 OPEN get_operating_unit_csr;
83 LOOP
84 FETCH get_operating_unit_csr INTO l_temp_variable;
85 EXIT WHEN get_operating_unit_csr%NOTFOUND;
86 l_operating_unit:=l_operating_unit||l_temp_variable;
87 l_operating_unit:=l_operating_unit||',';
88 END LOOP;
89 CLOSE get_operating_unit_csr;
90 l_operating_unit:=SUBSTRB(l_operating_unit,1,LENGTH(l_operating_unit)-1);
91
92 END IF;
93
94 IF LENGTH(l_operating_unit) > 240 THEN
95 l_operating_unit:=SUBSTRB(l_operating_unit,1,237)||'...';
96 END IF;
97
98 RETURN l_operating_unit;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 RETURN NULL;
103 END;
104
105 /*===========================================================================+
106 | PROCEDURE
107 | merge_products
108 |
109 | DESCRIPTION
110 | Merges ERP and CRM's account related products.
111 |
112 | SCOPE - PUBLIC
113 |
114 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
115 |
116 | ARGUMENTS : IN:
117 | req_id
118 | set_num
119 | process_mode
120 | OUT:
121 | status
122 | IN/ OUT:
123 |
124 | RETURNS : NONE
125 |
126 | NOTES : For backward compatibility the original signature without
127 | the out NOCOPY parameter error_text calls the modified procedure
128 | with the out NOCOPY parameter for error_text
129 |
130 | MODIFICATION HISTORY: 02-14-2002 Jyoti Pandey Created.
131 +===========================================================================*/
132
133 PROCEDURE merge_products (
134 req_id NUMBER,
135 set_num NUMBER,
136 process_mode VARCHAR2,
137 status OUT NOCOPY NUMBER
138
139 ) IS
140
141 x_message_text varchar2(1000);
142
143 BEGIN
144
145 merge_products(req_id,set_num,process_mode,status,x_message_text);
146
147 END merge_products;
148
149 /*===========================================================================+
150 | PROCEDURE
151 | merge_products
152 |
153 | DESCRIPTION
154 | Merges ERP and CRM's account related products.
155 |
156 | SCOPE - PUBLIC
157 |
158 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
159 |
160 | ARGUMENTS : IN:
161 | req_id
162 | set_num
163 | process_mode
164 | OUT:
165 | status
166 | IN/ OUT:
167 |
168 | RETURNS : NONE
169 |
170 | NOTES
171 |
172 | MODIFICATION HISTORY
173 | Rashmi Goyal 02-APR-00 Commented out NOCOPY call to asp_cmerge because
174 | product Oracle Sales (AS) is not expected to work
175 | in 11i. Commented out NOCOPY call to rla_cmerge because
176 | product RLA (Automotive) is obsoleted in 11i.
177 | Jianying Huang 13-OCT-00 Bug 1410555: added calls to CRM products.
178 | Jianying Huang 19-OCT-00 Added calls to Property Manager's merge.
179 | Jianying Huang 07-DEC-00 Bug 1391134: Changed procedure createSites to
180 | set-based and call it here.
181 | Jianying Huang 20-DEC-00 Bug 1535542: Since we will call customer merge
182 | before merging products, we should move 'createSites'
183 | in merge_customers. However, to avoid later calling order
184 | change, I rename it to 'create_same_sites', move it to
185 | arplbcus.sql(because it is related to customer tables),
186 | make it public and call it from merge report.
187 | Jianying Huang 10-JAN-01 Added callout to merge pricing.
188 | Jianying Huang 23-JAN-01 Added callout to merge shipping.
189 | Jianying Huang 26-MAR-01 Bug 1706718: Added callout to OM (Order
190 | Management) merge routines.
191 | Jianying Huang 10-APR-01 Bug 1730028: Add GMS customer merge callout
192 | (gms_cmerge.merge).
193 | Jianying Huang 10-APR-01 Bug 1730649: Add RLM customer merge callout
194 | (rlm_cust_merge.merge).
195 | Govind Jayanth 21-JUN-01 Bug 1838214: Added Exchange Billing customer
196 | merge callout (arp_exchange_merge.cmerge).
197 | Jianying Huang 02-JUL-01 Added callout to jl_cmerge (Bug 1848339).
198 | Jianying Huang 06-AUG-01 Call merge_product to check if product
199 | merge rountine exist. Only call these routines
200 | when they exist.
201 | Jianying Huang 06-AUG-01 Added callout to qa_customer.merge (bug 1848406)
202 | Jianying Huang 10-AUG-01 Added callout to csp_merge_pkg.merge_cust_account (bug 1848401).
203 | Jianying Huang 14-AUG-01 Added callout to igi_cmerge.merge (bug 1887139).
204 | Jianying Huang 30-AUG-01 Added callout to fv_cmerge.merge (bug 1870383).
205 | Jianying Huang 19-OCT-01 Removed the callout to Spares Management.
206 | It is part of CRM (csp_merge_pkg).
207 |
208 | Jyoti Pandey 14-FEB-02 Bug: 2228450 Added an OUT NOCOPY parameter error_text
209 | to catch exceptions raised by merge_products
210 | Ramesh Ch 22-OCT-03 Bug#3178951.Added calls to disable and enable
211 | Commit in merge_products procedure.
212 | S V Sowjanya 19-NOV-04 Bug 3897822: Commented the statements
213 | execute immediate 'alter session disable commit in procedure'
214 | execute immediate 'alter session enable commit in procedure'
215 | in the procedure merge_products.
216 |
217 +===========================================================================*/
218
219 PROCEDURE merge_products (
220 req_id NUMBER,
221 set_num NUMBER,
222 process_mode VARCHAR2,
223 status OUT NOCOPY NUMBER,
224 error_text OUT NOCOPY VARCHAR2
225
226 ) IS
227
228 v_message_text varchar2(1000);
229 success varchar2(1) := 'N' ;
230 l_str_exe VARCHAR2(2000);
231
232 BEGIN
233
234 -- execute immediate 'alter session disable commit in procedure'; bug 3897822
235
236 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_products()+' );
237
238
239 g_excluded_apps := FND_PROFILE.VALUE('HZ_CMERGE_EXCLUDED_APPS');
240
241 IF g_excluded_apps IS NULL THEN
242 g_excluded_apps := 'NONE';
243 END IF;
244
245 IF g_excluded_apps = 'ALL' THEN
246 status := 0;
247 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_products()-' );
248 RETURN;
249 ELSE
250 g_excluded_apps := replace(g_excluded_apps,' ')||',';
251 END IF;
252
253 --Oracle Account Receivables
254 --arp_cmerge.merge ( req_id, set_num, process_mode );
255 merge_product (
256 'ARP_CMERGE', 'merge',
257 req_id, set_num, process_mode,'AR' );
258
259 --Oracle Account Payables
260 --app_cmerge.merge ( req_id, set_num, process_mode );
261 merge_product (
262 'APP_CMERGE', 'merge',
263 req_id, set_num, process_mode,'AP' );
264
265 --commented out NOCOPY call to asp_cmerge because product
266 --Oracle Sales (AS) is not expected to work in 11i.
267 --asp_cmerge.merge ( req_id, set_num, process_mode );
268
269 --Oracle Service
270 --csp_cmerge.merge ( req_id, set_num, process_mode );
271 merge_product (
272 'CSP_CMERGE', 'merge',
273 req_id, set_num, process_mode,'CSP' );
274
275 --Oracle Inventory
276 --invp_cmerge.merge ( req_id, set_num, process_mode );
277 merge_product (
278 'INVP_CMERGE', 'merge',
279 req_id, set_num, process_mode,'INV' );
280
281 merge_product (
282 'INV_CMERGE_ITEMS', 'merge',
283 req_id, set_num, process_mode,'INV');
284
285 --Oracle Master Scheduling/MRP
286 --mrpp_cmerge.merge ( req_id, set_num, process_mode );
287 merge_product (
288 'MRPP_CMERGE', 'merge',
289 req_id, set_num, process_mode,'MRP' );
290
291 --Commented out NOCOPY the call out NOCOPY to Order Entry.
292 --It has been replaced by Order Managment's merge package.
293 --Oracle Order Entry
294 --oep_cmerge.merge ( req_id, set_num, process_mode );
295
296 --Oracle Projects
297 --pap_cmerge.merge ( req_id, set_num, process_mode );
298 merge_product (
299 'PAP_CMERGE', 'merge',
300 req_id, set_num, process_mode,'PA' );
301
302 --Oracle Purchasing
303 --pop_cmerge.merge ( req_id, set_num, process_mode );
304 merge_product (
305 'POP_CMERGE', 'merge',
306 req_id, set_num, process_mode,'PO' );
307
308 --Oracle Training Administration
309 --otap_cmerge.merge ( req_id, set_num, process_mode );
310 merge_product (
311 'OTAP_CMERGE', 'merge',
312 req_id, set_num, process_mode,'OTA' );
313
314 --Commented out NOCOPY call to rla_cmerge because product RLA
315 --(Automotive) is obsoleted in 11i.
316 --rla_cmerge.merge ( req_id, set_num, process_mode );
317
318 --Oracle Property Manager
319 --pnp_cmerge.merge( req_id, set_num, process_mode );
320 merge_product (
321 'PNP_CMERGE', 'merge',
322 req_id, set_num, process_mode,'PN' );
323
324 --Pricing
325 --qp_cust_merge.merge( req_id, set_num, process_mode );
326 merge_product (
327 'QP_CUST_MERGE', 'merge',
328 req_id, set_num, process_mode,'QP' );
329
330 --Shipping
331 --wsh_cust_merge.merge( req_id, set_num, process_mode );
332 merge_product (
333 'WSH_CUST_MERGE', 'merge',
334 req_id, set_num, process_mode,'WSH' );
335
336 --Grants Accounting (GMS)
337 --Bug 1730028: Add callout to GMS. Only a stub version
338 --has been provided for now.
339 --gms_cmerge.merge( req_id, set_num, process_mode );
340 merge_product (
341 'GMS_CMERGE', 'merge',
342 req_id, set_num, process_mode,'GMS' );
343
344 --Oracle Release Management (RLM)
345 --Bug 1730649: Add callout to RLM. Only a stub version
346 --has been provided for now.
347 --rlm_cust_merge.merge( req_id, set_num, process_mode );
348 merge_product (
349 'RLM_CUST_MERGE', 'merge',
350 req_id, set_num, process_mode,'RLM' );
351
352 --Oracle Exchange Billing (currently in AR source control).
353 -- customer merge routine.
354 --arp_exchange_merge.cmerge( req_id, set_num, process_mode );
355 merge_product (
356 'ARP_EXCHANGE_MERGE', 'cmerge',
357 req_id, set_num, process_mode,'AR_EXCHANGE' );
358
359 -- JL customer merge callout.
360 --jl_cmerge.merge ( req_id, set_num, process_mode );
361 merge_product (
362 'JL_CMERGE', 'merge',
363 req_id, set_num, process_mode,'JL' );
364
365 -- Bug 1848406: Oracle Quality
366 merge_product (
367 'QA_CUSTOMER', 'merge',
368 req_id, set_num, process_mode,'QA' );
369
370 /* remove the callout to Spares Management. It is part of CRM.
371 -- Bug 1848401: Spares Management
372 merge_product (
373 'CSP_MERGE_PKG', 'merge_cust_account',
374 req_id, set_num, process_mode );
375 */
376
377 -- Bug 1887139 : Public sector financials
378 merge_product (
379 'IGI_CMERGE', 'merge',
380 req_id, set_num, process_mode,'IGI' );
381
382 -- Bug 1870383: Federal Financials
383
384 merge_product (
385 'FV_CMERGE', 'merge',
386 req_id, set_num, process_mode,'FV' );
387
388 -- Bug 2057511: Multi Currency Credit Checking
389 merge_product (
390 'OE_MGD_MCC_MERGE', 'customer_merge',
391 req_id, set_num, process_mode,'MCC' );
392
393 -- Bug 2177889: Added AX Merge Procedure
394 -- Bug 4661029: AX is obsoleted.Remove it
395 /* merge_product (
396 'AX_SC_MERGE_PKG','MergeTCA',
397 req_id, set_num, process_mode,'AX' );
398 */
399
400 --Bug 2236975:Oracle Student System Product(IGS)
401 merge_product (
402 'IGS_FI_MERGE_CUST', 'MERGE',
403 req_id, set_num, process_mode,'IGS' );
404
405 --Bug 2469023: JG
406 -- Bug 4778792 - JG_ZZ_MERGE_CUSTOMERS is obsoleted
407 /* merge_product (
408 'JG_ZZ_MERGE_CUSTOMERS', 'MERGE',
409 req_id, set_num, process_mode,'JG' );
410 */
411
412 IF instrb(g_excluded_apps,'CRM,') = 0 THEN
413
414 --Bug 1410555: Added calls to CRM products.
415 merge_crm_products( req_id, set_num, process_mode );
416 END IF;
417
418 --Bug Fix 2669389
419 --Oracle Order Managment
420 --oe_cust_merge.merge( req_id, set_num, process_mode );
421 merge_product (
422 'OE_CUST_MERGE', 'merge',
423 req_id, set_num, process_mode,'ONT' );
424
425 --Generic merge package. (see arplbst9.sql)
426 -- arp_generic_cmerge.merge ( req_id, set_num, process_mode );
427 merge_product (
428 'ARP_GENERIC_CMERGE', 'merge',
429 req_id, set_num, process_mode,'GENERIC');
430
431
432 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_products()-' );
433
434 -- execute immediate 'alter session enable commit in procedure'; bug 3897822
435
436 EXCEPTION
437
438 WHEN OTHERS THEN
439 begin
440 status := -1;
441 v_message_text := arp_message.get_last_few_messages(2);
442 error_text := G_PRODUCT_RAISING_EXCEPTION ||' '||v_message_text;
443 arp_message.set_line(v_message_text);
444 exception
445 when others then
446 null;
447 end;
448
449 END merge_products;
450
451 /*===========================================================================+
452 | PROCEDURE
453 | update_merge_as_failed
454 |
455 | DESCRIPTION
456 | Update a bad merge record as failed.
457 | SCOPE - PRIVATE
458 |
459 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
460 |
461 | ARGUMENTS : IN:
462 | req_id
463 | set_num
464 | customer_merge_header_id
465 | p_error_text
466 | OUT:
467 | IN/ OUT:
468 |
469 | RETURNS : NONE
470 |
471 | NOTES
472 |
473 | MODIFICATION HISTORY
474 | 04-02-2003 Rajeshwari Bug 2669389 Created.
475 | 04-07-2003 Rajeshwari Removed the update of set_number
476 | to -1.
477 +===========================================================================*/
478
479 procedure update_merge_as_failed(
480 p_request_id IN NUMBER,
481 p_set_num IN NUMBER,
482 p_customer_merge_header_id IN NUMBER,
483 p_error_text IN VARCHAR2
484 ) IS
485
486 BEGIN
487 UPDATE ra_customer_merges set process_flag = 'FAILED'
488 WHERE request_id = p_request_id
489 AND customer_merge_header_id = p_customer_merge_header_id
490 ;
491
492 UPDATE ra_customer_merge_headers
493 SET process_flag = 'FAILED',merge_fail_msg = p_error_text
494 WHERE request_id = p_request_id
495 AND customer_merge_header_id = p_customer_merge_header_id
496 ;
497
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 NULL;
502 END update_merge_as_failed;
503
504 /*===========================================================================+
505 | PROCEDURE
506 | merge_product
507 |
508 | DESCRIPTION
509 | Merges a perticular product using dynamic sql to lower the
510 | dependencies.
511 |
512 | SCOPE - PUBLIC
513 |
514 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
515 |
516 | ARGUMENTS : IN:
517 | package_name
518 | api_name
519 | req_id
520 | set_num
521 | process_mode
522 |
523 | RETURNS : NONE
524 |
525 | NOTES
526 |
527 | MODIFICATION HISTORY
528 |
529 | Jianying Huang 06-AUG-01 Created.
530 | Rajeshwari P 02-APR-03 Modified the code to identify bad merge
531 | records by running each record in the set
532 | individually for a product call which failed.
533 | Ramesh Ch 22-OCT-03 Bug#3178951.Changed savepoint name from start
534 | start_merge_product.
535 | S V Sowjanya 28-OCT-04 Bug 3751217 : In merge_product procedure, removed variable ok.
536 | Replaced literals with bind variables in dynamic sqls.
537 | Removed exception handling code at parse time.
538 | Exception ORA-6550 is handled.
539 +===========================================================================*/
540
541 PROCEDURE merge_product (
542 package_name VARCHAR2,
543 api_name VARCHAR2,
544 req_id NUMBER,
545 set_num NUMBER,
546 process_mode VARCHAR2,
547 product_code VARCHAR2
548 ) IS
549
550 --raji
551 l_dummy VARCHAR2(1):= 'N' ;
552 success VARCHAR2(1):= 'N' ;
553 v_last_set NUMBER;
554 OTHERS EXCEPTION;
555 error_text VARCHAR2(2000);
556 l_customer_merge_header_id NUMBER;
557
558 l_sql VARCHAR2(200);
559 c NUMBER;
560 n NUMBER;
561 l_count NUMBER;
562
563 BEGIN
564 --raji
565 while ( success <> 'Y' ) LOOP
566
567 SAVEPOINT "start_merge_product";
568
569 IF l_dummy = 'N' then
570
571
572 IF instrb(g_excluded_apps,product_code||',') > 0 THEN
573 RETURN;
574 END IF;
575
576 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_product(' ||
577 package_name || ')+' );
578
579 BEGIN
580 c := dbms_sql.open_cursor;
581 l_sql := 'BEGIN ' ||
582 package_name || '.' || api_name ||
583 '(:req_id, :set_num, :process_mode ); END; ';
584
585 dbms_sql.parse(c,l_sql,2);
586 dbms_sql.bind_variable(c, 'req_id',to_char(req_id));
587 dbms_sql.bind_variable(c, 'set_num',to_char(set_num));
588 dbms_sql.bind_variable(c, 'process_mode',process_mode);
589 n := dbms_sql.execute(c);
590 dbms_sql.close_cursor(c);
591 -- EXECUTE IMMEDIATE l_sql;
592 --raji
593 success := 'Y' ;
594
595 EXCEPTION
596 WHEN NO_DATA_FOUND THEN
597 ROLLBACK to "start_merge_product";
598 l_dummy := 'Y' ;
599 success := 'N' ;
600 WHEN OTHERS THEN
601
602 IF SQLCODE = -6550 THEN
603 success := 'Y' ;
604 dbms_sql.close_cursor(c);
605 ELSIF SQLCODE <> -6550 THEN
606 --Bug fix 2669389,rollback to run the records individually to identify the bad record.
607
608 ROLLBACK to "start_merge_product";
609 l_dummy := 'Y' ;
610 success := 'N' ;
611 END IF;
612 END ;
613
614 --raji
615 elsif l_dummy = 'Y' then
616
617 -- Partition the set into single batch(set size=1)
618
619 unpartiton_merge_data(req_id,v_last_set,set_num);
620
621 FOR v_current_set in 1001..v_last_set LOOP
622 BEGIN
623
624 c := dbms_sql.open_cursor;
625
626 l_sql := 'BEGIN ' ||
627 package_name || '.' || api_name ||
628 '(:req_id, :v_current_set, :process_mode ); END; ';
629
630 dbms_sql.parse(c,l_sql,2);
631 dbms_sql.bind_variable(c, 'req_id',to_char(req_id));
632 dbms_sql.bind_variable(c, 'v_current_set',to_char(v_current_set));
633 dbms_sql.bind_variable(c, 'process_mode',process_mode);
634 n := dbms_sql.execute(c);
635 dbms_sql.close_cursor(c);
636 -- EXECUTE IMMEDIATE l_sql;
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 IF SQLCODE = -6550 THEN
641 dbms_sql.close_cursor(c);
642 ELSIF SQLCODE <> -6550 THEN
643 G_PRODUCT_RAISING_EXCEPTION := product_code||'.'|| package_name || '.' || api_name ;
644 error_text := G_PRODUCT_RAISING_EXCEPTION || ' ' || arp_message.get_last_few_messages(2);
645
646 --raji
647
648 BEGIN
649 --Get the bad merge records and update them as failed.
650
651 select customer_merge_header_id into l_customer_merge_header_id
652 from ra_customer_merges
653 where request_id = req_id
654 AND set_number = v_current_set
655 AND process_flag = 'N'
656 AND ROWNUM = 1
657 FOR UPDATE NOWAIT;
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 NULL;
661 END;
662
663 update_merge_as_failed (
664 req_id,
665 v_current_set,
666 l_customer_merge_header_id,
667 error_text
668 );
669 END IF;
670 END ;
671
672 --reset back the original set number
673
674 update ra_customer_merges
675 set set_number = set_num
676 WHERE request_id = req_id
677 AND (process_flag = 'N' or process_flag = 'FAILED')
678 AND set_number = v_current_set;
679
680 END LOOP;
681
682 --Finished processing the set in set_size of 1
683 BEGIN
684 SELECT count(*) INTO l_count
685 FROM ra_customer_merges
686 WHERE request_id = req_id
687 AND set_number = set_num
688 AND process_flag = 'FAILED'
689 ;
690
691
692 IF l_count > 0 then
693 success := 'Y' ;
694 --Since atleast one record has failed we will ROLLBACK the whole set
695 RAISE OTHERS;
696 ELSE
697 --getting errors from FV_Cmerge
698 success := 'Y' ;
699
700 END IF;
701
702 end;
703
704 END IF;
705
706 END LOOP;
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 -- G_PRODUCT_RAISING_EXCEPTION := product_code||'.'|| package_name || '.' || api_name ;
711 raise;
712
713 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_product(' || package_name || ')-' );
714
715 END merge_product;
716
717 /*===========================================================================+
718 | PROCEDURE
719 | merge_crm_products
720 |
721 | DESCRIPTION
722 | Merges CRM products.
723 |
724 | SCOPE - PRIVATE
725 |
726 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
727 |
728 | ARGUMENTS : IN:
729 | req_id
730 | set_num
731 | process_mode
732 | OUT:
733 | IN/ OUT:
734 |
735 | RETURNS : NONE
736 |
737 | NOTES
738 |
739 | MODIFICATION HISTORY
740 | Jianying Huang 13-OCT-00 Bug 1410555: the procedure is created to
741 | call crm customer merge procedure.
742 | Jianying Huang 26-OCT-00 Add exception handler to populate exception
743 | to caller. Add calls to write log file.
744 | Jianying Huang 14-NOV-00 Remove comparision: product_code = 'AR'
745 | because CRM register their products using
746 | different code.
747 | Jianying Huang 15-NOV-00 Modify merge_crm_products. Add condition
748 | EXECUTE_FLAG = 'Y'.
749 | Jianying Huang 29-MAR-01 Bug 1706869: Modified the procedure to:
750 | 1. simplify the dynamic sql.
751 | 2. add cursor 'ISOPEN' checking in excepion handler.
752 | Rajeshwari P 02-APR-2003 Bug fix2669389.Modified code to call merge_product.
753 |
754 +===========================================================================*/
755
756 PROCEDURE merge_crm_products(
757 req_id NUMBER,
758 set_num NUMBER,
759 process_mode VARCHAR2
760 ) IS
761
762 TYPE crm_cursor_type IS REF CURSOR;
763 crm_products crm_cursor_type;
764
765 l_hook_package VARCHAR2(40);
766 l_hook_api VARCHAR2(40);
767
768 cur_sql VARCHAR2(400);
769 l_sql VARCHAR2(200);
770 l_exist VARCHAR2(1);
771 l_product_code VARCHAR2(10);
772 l_bool BOOLEAN;
773 l_status VARCHAR2(255);
774 l_schema VARCHAR2(255);
775 l_tmp VARCHAR2(2000);
776 BEGIN
777
778 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_crm_products()+' );
779
780 --To minimize dependency, we need to call CRM's merge procedure
781 --dynanmically. First we need to check if table jtf_hooks_data
782 --exists, if yes, we need to see if there are crm's merge procedures
783 --we need to call and call them sequencially (order by execution order)
784
785 BEGIN
786
787 l_bool := fnd_installation.get_app_info('JTF',l_status,l_tmp,l_schema);
788
789 SELECT 'Y' INTO l_exist
790 FROM sys.all_tables
791 WHERE table_name = 'JTF_HOOKS_DATA'
792 AND ROWNUM = 1 and owner = l_schema;
793
794 EXCEPTION
795
796 WHEN NO_DATA_FOUND THEN
797 l_exist := 'N';
798 END;
799
800 IF l_exist = 'Y' THEN
801
802 cur_sql := 'SELECT hook_package, hook_api,product_code ' ||
803 'FROM jtf_hooks_data '||
804 'WHERE package_name = ''ARP_CMERGE_MASTER'' ' ||
805 'AND api_name = ''MERGE_PRODUCTS'' ' ||
806 'AND execute_flag = ''Y'' ' ||
807 'ORDER BY execution_order ';
808
809 OPEN crm_products FOR cur_sql;
810 LOOP
811
812 FETCH crm_products INTO l_hook_package, l_hook_api, l_product_code;
813 EXIT WHEN crm_products%NOTFOUND;
814
815 IF instrb(g_excluded_apps,l_product_code||',') = 0 THEN
816 begin
817 --Bug fix 2669389
818 -- Call Merge_product
819
820 merge_product(l_hook_package,l_hook_api,req_id,set_num,process_mode,l_product_code);
821
822 EXCEPTION
823 WHEN OTHERS THEN
824 G_PRODUCT_RAISING_EXCEPTION := l_product_code||'.'||l_hook_package || '.' || l_hook_api ;
825 raise;
826 end;
827 END IF;
828
829 END LOOP;
830 CLOSE crm_products;
831
832 END IF;
833
834 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_crm_products()-' );
835
836 EXCEPTION
837
838 WHEN OTHERS THEN
839 arp_message.set_error( 'ARP_CMERGE_MASTER.merge_crm_products');
840
841 IF crm_products%ISOPEN THEN
842 CLOSE crm_products;
843 END IF;
844
845 RAISE;
846
847 END merge_crm_products;
848
849 /*===========================================================================+
850 | PROCEDURE
851 | merge_customers
852 |
853 | DESCRIPTION
854 | Merges customer tables.
855 |
856 | SCOPE - PUBLIC
857 |
858 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
859 |
860 | ARGUMENTS : IN:
861 | req_id
862 | set_num
863 | process_mode
864 | OUT:
865 | status
866 | IN/ OUT:
867 |
868 | RETURNS : NONE
869 |
870 | NOTES
871 |
872 | MODIFICATION HISTORY
873 |
874 +===========================================================================*/
875
876 PROCEDURE merge_customers (
877 req_id NUMBER,
878 set_num NUMBER,
879 process_mode VARCHAR2,
880 status OUT NOCOPY NUMBER
881 ) IS
882
883 BEGIN
884
885 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_customers()+' );
886
887 --merge accout site uses, sites, account etc.
888 arp_cmerge_arcus.merge(req_id, set_num, process_mode);
889
890 --merge customer profiles, credit histories, etc.
891 arp_cmerge_arcpf.merge(req_id, set_num, process_mode);
892
893 status := 0;
894
895 arp_message.set_line( 'ARP_CMERGE_MASTER.merge_customers()-' );
896
897 EXCEPTION
898
899 WHEN OTHERS THEN
900 arp_message.set_error( 'ARP_CMERGE_MASTER.merge_customers');
901 status := -1;
902
903 END merge_customers;
904
905 /*===========================================================================+
906 | PROCEDURE
907 | delete_rows
908 |
909 | DESCRIPTION
910 | Delete marked rows in customer tables.
911 |
912 | SCOPE - PUBLIC
913 |
914 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
915 |
916 | ARGUMENTS : IN:
917 | req_id
918 | set_num
919 | OUT:
920 | status
921 | IN/ OUT:
922 |
923 | RETURNS : NONE
924 |
925 | NOTES
926 |
927 | MODIFICATION HISTORY
928 | Jianying Huang 20-DEC-00 Created for bug 1535542.
929 |
930 +===========================================================================*/
931
932 PROCEDURE delete_rows (
933 req_id NUMBER,
934 set_num NUMBER,
935 status OUT NOCOPY NUMBER
936 ) IS
937
938 BEGIN
939
940 arp_message.set_line( 'ARP_CMERGE_MASTER.delete_rows()+' );
941
942 --delete customer tables
943 arp_cmerge_arcus.delete_rows( req_id, set_num );
944
945 --delete customer related tables
946 arp_cmerge_arcpf.delete_rows( req_id, set_num );
947
948 status := 0;
949
950 arp_message.set_line( 'ARP_CMERGE_MASTER.delete_rows()-' );
951
952 EXCEPTION
953
954 WHEN OTHERS THEN
955 arp_message.set_error( 'ARP_CMERGE_MASTER.delete_rows');
956 status := -1;
957
958 END delete_rows;
959
960 /*===========================================================================+
961 | PROCEDURE
962 | mark_merge_rows
963 |
964 | DESCRIPTION
965 | Mark rows with request_id. The rows include those
966 | ones which errored out NOCOPY previously.
967 |
968 | SCOPE - PUBLIC
969 |
970 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
971 |
972 | ARGUMENTS : IN:
973 | req_id
974 | p_process_flag
975 | OUT:
976 | IN/ OUT:
977 |
978 | RETURNS : NONE
979 |
980 | NOTES
981 |
982 | MODIFICATION HISTORY
983 | Jianying Huang 29-NOV-00 Bug 1519688: Modified procedure based on
984 | the change of merge form:
985 | 'display request id instantly after hit merge button'
986 | Jianying Huang 24-JAN-01 Bug 1611300: Marked merge rows based on
987 | the process_flag passed in.
988 | Jianying Huang 30-MAR-01 Bug 1706869: Modified 'mark_merge_rows'
989 | to differential the merge processes submited through
990 | form and submited as a concurrent request.
991 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
992 | the new added indexes for performance improvements.
993 | Jianying Huang 22-JUL-01 Modified 'mark_merge_rows' to mark only the
994 | records with current request id if process_flag='PROCESSING'.
995 | S V Sowjanya 19-NOV-04 Bug 3897822: Added parameters p_priority,
996 | p_number_of_merges to procedure mark_merge_rows and merge rows
997 | are marked based on parameters p_priority and p_number_of_merges.
998 | S V Sowjanya 16-NOV-04 Bug 4693912: Modified update statement in mark_merge_rows
999 |
1000 +===========================================================================*/
1001
1002 PROCEDURE mark_merge_rows (
1003 req_id NUMBER,
1004 p_process_flag VARCHAR2,
1005 p_merge_rule VARCHAR2,
1006 p_priority VARCHAR2,
1007 p_number_of_merges NUMBER
1008 ) IS
1009
1010 CURSOR c_requests(c_priority varchar2) IS
1011 SELECT distinct request_id, process_flag
1012 FROM ra_customer_merge_headers
1013 WHERE process_flag IN ('PROCESSING', 'N')
1014 AND priority = c_priority;
1015
1016 l_request_id NUMBER;
1017 l_process_flag VARCHAR2(30);
1018 l_pickup BOOLEAN;
1019 l_new_process_flag VARCHAR2(30);
1020
1021 l_conc_phase VARCHAR2(80);
1022 l_conc_status VARCHAR2(80);
1023 l_conc_dev_phase VARCHAR2(30);
1024 l_conc_dev_status VARCHAR2(30);
1025 l_message VARCHAR2(240);
1026 --3897822
1027 TYPE customer_merge_header_id_tab IS TABLE OF RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
1028 TYPE process_flag_tab IS TABLE OF RA_CUSTOMER_MERGE_HEADERS.PROCESS_FLAG%TYPE INDEX BY BINARY_INTEGER;
1029 l_header_id_t customer_merge_header_id_tab;
1030 l_process_flag_t process_flag_tab;
1031 BEGIN
1032
1033 arp_message.set_line( 'ARP_CMERGE_MASTER.mark_merge_rows()+' );
1034
1035 /* >>>MODIFY: need to pick up terminated rows as well */
1036
1037 /** N/A
1038 --Bug 1519688: remove condition 'request_id is NULL'. The
1039 --request_id is unset by 'reset_merge_rows' if there are
1040 --some error occur during merge. If we need to display/query
1041 --the request id even the merge has not been sucessfully done,
1042 --we should not unset request id. So when we pick up the
1043 --previously errored out NOCOPY merge process, we should not use
1044 --'request_id is NULL', instead, we can re-use 'process_flag'
1045 --to indicate those 'merges' are being processed and those
1046 --we need to pick up. (process_flag = 'N' means there is
1047 --another concurrent program who is processing this merge.
1048 --We set process_flag = 'SAVED' in merge form to indicate
1049 --it is a new submitted merge and set process_flag = 'FAILED'
1050 --to indicate those merges were failed by last concurrent
1051 --program)
1052
1053 --Bug 1611300: Marked the merge rows which process_flag is either FAILED
1054 --or equals to the passin process_flag. Right now the passin process_flag
1055 --has 2 values:
1056 --PROCESSING means the merge has been submitted from merge
1057 --form which we should merge during this merge run.
1058 --SAVED means the merge has been saved and submitted along with
1059 --other processes as a batch.
1060
1061 --Bug 1706869:
1062 --If end-user submits merge from form, i.e. process_flag is
1063 --PROCESSING, only the submited one will be processed.
1064 --If end-user submits a concurrent request of customer merge
1065 --directly, all of the saved and failed merge processes,
1066 --i.e. process_flag is 'SAVED' or 'FAILED', will be processed.
1067
1068 --Bug 1725622:Merge header table has much less rows then merge detail table.
1069 --It's better to update header table first, then update merge detail table
1070 --using index.
1071 **/
1072
1073 -- The process_flag can have 5 different value
1074 -- PROCESSING merge request is in pending. The flag is used only for
1075 -- merge submitted from form.
1076 -- SAVED merge is saved for later use.
1077 -- N merge is being processed.
1078 -- FAILED merge failed
1079 -- Y merge finished successfully.
1080 -- The process_flag in header table and process_flag in merge detail table
1081 -- must be in sync otherwise, user will not see merge details in merge form
1082 -- when they do query.
1083 -- This procedure (i.e. mark_merge_rows) is changed to handle:
1084 -- 1. update process_flag in header and detail tables to make them in sync.
1085 -- Details:
1086 -- Select all of request_id from header table. If process_flag = N, but
1087 -- request is not in running or if process_flag = 'PROCESSING', but
1088 -- request is not in pending, update the process_flag = 'SAVED'.
1089
1090 -- if there exists one merge detail which has process_flag = 'Y', update
1091 -- merge table's process_flag to 'Y'.
1092 -- 2. mark merge rows by updating process_flag and request_id in detail table.
1093 -- 3. exclude those addresses in other operating units by checking if address
1094 -- exists in hz_cust_acct_sites.
1095
1096 -- Added l_new_process_flag for backward compatible.
1097 IF p_merge_rule = 'NEW' THEN
1098 l_new_process_flag := p_process_flag;
1099 ELSE
1100 l_new_process_flag := 'N';
1101 END IF;
1102
1103 IF p_process_flag = 'SAVED' THEN
1104 --3897822
1105 SELECT customer_merge_header_id, process_flag
1106 BULK COLLECT INTO l_header_id_t,l_process_flag_t
1107 FROM ra_customer_merge_headers mh
1108 WHERE process_flag IN ('PROCESSING', 'N')
1109 AND priority = p_priority;
1110
1111 OPEN c_requests(p_priority);
1112 LOOP
1113 FETCH c_requests INTO l_request_id, l_process_flag;
1114 EXIT WHEN c_requests%NOTFOUND;
1115
1116 l_pickup := FALSE;
1117
1118 IF l_request_id IS NOT NULL THEN
1119 IF ( FND_CONCURRENT.GET_REQUEST_STATUS(
1120 request_id => l_request_id,
1121 phase => l_conc_phase,
1122 status => l_conc_status,
1123 dev_phase => l_conc_dev_phase,
1124 dev_status => l_conc_dev_status,
1125 message => l_message ) )
1126 THEN
1127 IF (l_process_flag = 'PROCESSING' AND
1128 l_conc_dev_phase <> 'PENDING') OR
1129 (l_process_flag = 'N' AND
1130 l_conc_dev_phase <> 'RUNNING' )
1131 THEN
1132 l_pickup := TRUE;
1133 END IF;
1134 ELSE
1135 l_pickup := TRUE;
1136 END IF;
1137
1138 IF l_pickup THEN
1139 UPDATE ra_customer_merge_headers
1140 SET process_flag = l_new_process_flag
1141 WHERE request_id = l_request_id
1142 AND process_flag = l_process_flag
1143 AND priority = p_priority; --3897822
1144 END IF;
1145 ELSE
1146 UPDATE ra_customer_merge_headers
1147 SET process_flag = l_new_process_flag
1148 WHERE request_id IS NULL
1149 AND process_flag = l_process_flag
1150 AND priority = p_priority; --3897822
1151 END IF;
1152 END LOOP;
1153 CLOSE c_requests;
1154
1155 ELSIF p_process_flag = 'FAILED' THEN
1156 UPDATE ra_customer_merge_headers
1157 SET process_flag = l_new_process_flag
1158 WHERE process_flag LIKE 'ERROR%';
1159 END IF;
1160
1161 IF p_merge_rule = 'OLD' THEN
1162 UPDATE ra_customer_merge_headers
1163 SET process_flag = l_new_process_flag
1164 WHERE process_flag = p_process_flag;
1165 END IF;
1166
1167 IF p_process_flag<> 'PROCESSING' THEN
1168 UPDATE ra_customer_merge_headers mh
1169 SET request_id = req_id ,
1170 merge_fail_msg = null
1171 WHERE process_flag = l_new_process_flag
1172 --Start of SSUptake
1173 AND NOT EXISTS (
1174 select 'Y' from ra_customer_merges m
1175 where m.customer_merge_header_id = mh.customer_merge_header_id
1176 and mo_global.check_access(m.org_id) <> 'Y'
1177 and rownum =1
1178 )
1179 --End of SSUptake
1180 AND ( EXISTS (
1181 SELECT 'Y'
1182 FROM ra_customer_merges m, hz_cust_acct_sites site
1183 WHERE m.customer_merge_header_id = mh.customer_merge_header_id
1184 AND m.duplicate_address_id = site.cust_acct_site_id
1185 AND ROWNUM = 1)
1186 OR EXISTS( --4693912
1187 SELECT 'Y'
1188 FROM ra_customer_merges m
1189 WHERE m.customer_merge_header_id = mh.customer_merge_header_id
1190 AND m.duplicate_address_id = -1)
1191 )
1192 AND customer_merge_header_id in (SELECT customer_merge_header_id --3897822
1193 FROM (SELECT customer_merge_header_id
1194 FROM ra_customer_merge_headers
1195 WHERE process_flag = l_new_process_flag
1196 AND priority = p_priority
1197 ORDER BY last_update_date)
1198 WHERE ROWNUM <= p_number_of_merges)
1199 AND mh.priority = p_priority;
1200
1201 FORALL i IN 1..l_header_id_t.count
1202 UPDATE ra_customer_merge_headers mh
1203 SET process_flag = l_process_flag_t(i)
1204 WHERE request_id <> req_id
1205 AND mh.customer_merge_header_id = l_header_id_t(i);
1206 END IF;
1207
1208 --N/A --Bug 1519688: Set request_id for merge headers.
1209
1210 --Bug 1725662: Set request_id from ra_customer_merges. Rewrite sql to
1211 --use index.
1212
1213 UPDATE ra_customer_merge_headers mh
1214 SET process_flag = 'Y'
1215 WHERE request_id = req_id
1216 AND process_flag = l_new_process_flag
1217 AND priority = p_priority --3897822
1218 AND EXISTS (
1219 SELECT 'Y'
1220 FROM ra_customer_merges m
1221 WHERE m.customer_merge_header_id = mh.customer_merge_header_id
1222 AND m.process_flag = 'Y'
1223 AND ROWNUM = 1 );
1224
1225 UPDATE ra_customer_merges m
1226 SET (request_id,
1227 process_flag) = (
1228 SELECT mh.request_id, mh.process_flag
1229 FROM ra_customer_merge_headers mh
1230 WHERE mh.customer_merge_header_id = m.customer_merge_header_id
1231 AND mh.process_flag = l_new_process_flag )
1232 WHERE m.customer_merge_header_id IN (
1233 SELECT mh.customer_merge_header_id
1234 FROM ra_customer_merge_headers mh
1235 WHERE mh.process_flag = l_new_process_flag
1236 AND request_id = req_id
1237 AND mh.priority = p_priority); --3897822
1238
1239 arp_message.set_line( 'ARP_CMERGE_MASTER.mark_merge_rows()-' );
1240
1241 EXCEPTION
1242
1243 WHEN OTHERS THEN
1244 arp_message.set_error( 'ARP_CMERGE_MASTER.mark_merge_rows');
1245 RAISE;
1246
1247 END mark_merge_rows;
1248
1249 /*===========================================================================+
1250 | PROCEDURE
1251 | add_request
1252 |
1253 | DESCRIPTION
1254 | For handling terminated requests, add request to
1255 | the AR concurrent request table.
1256 |
1257 | SCOPE - PUBLIC
1258 |
1259 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1260 |
1261 | ARGUMENTS : IN:
1262 | req_id
1263 | program_name
1264 | OUT:
1265 | IN/ OUT:
1266 |
1267 | RETURNS : NONE
1268 |
1269 | NOTES
1270 |
1271 | MODIFICATION HISTORY
1272 |
1273 +===========================================================================*/
1274
1275 PROCEDURE add_request (
1276 req_id NUMBER,
1277 program_name VARCHAR2
1278 ) IS
1279
1280 BEGIN
1281
1282 arp_message.set_line( 'ARP_CMERGE_MASTER.add_request()+' );
1283
1284 INSERT into ar_conc_process_requests
1285 (request_id, concurrent_program_name)
1286 VALUES
1287 (req_id, program_name);
1288
1289 arp_message.set_line( 'ARP_CMERGE_MASTER.add_request()-' );
1290
1291 EXCEPTION
1292
1293 WHEN OTHERS THEN
1294 arp_message.set_error( 'ARP_CMERGE_MASTER.add_request' );
1295 RAISE;
1296
1297 END add_request;
1298
1299 /*===========================================================================+
1300 | PROCEDURE
1301 | validate_merges
1302 |
1303 | DESCRIPTION
1304 | Validate merge candidate.
1305 |
1306 | SCOPE - PUBLIC
1307 |
1308 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1309 |
1310 | ARGUMENTS : IN:
1311 | req_id
1312 | p_process_flag
1313 | IN/ OUT:
1314 |
1315 | RETURNS : NONE
1316 |
1317 | NOTES
1318 | Procedure to validate the RA_CUSTOMER_MERGE records prior to
1319 | using them as the source for the merge process
1320 |
1321 | MODIFICATION HISTORY
1322 | Jianying Huang 07-DEC-00 Bug 1391134: We modified createSites as
1323 | set-based procedure and call if before merge products
1324 | for each set. We also set not-null columns
1325 | customer_address_id and customer_site_id in table
1326 | ra_customer_merges to -1, so merge form can submit
1327 | the merges. To accommodate that changes, we donot
1328 | validate those records with customer_createsame = 'Y'
1329 | Jianying Huang 08-MAR-01 Bug 1610924: Modified the procedure to allow
1330 | merging all of the site uses.
1331 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
1332 | the new added indexes for performance improvements.
1333 | Ramesh Ch 21-Nov-03 Bug 3186432.Added duplicate_site_id <> -99
1334 | condition in check_invalid_merges cursor.
1335 | S V Sowjanya 10-AUG-04 Bug 3705423: Commented (duplicate_site_id <> -99 and
1336 | customer_site_id <> -99), duplicate_site_id <> -99 conditions
1337 | and added code to join address_id of the customers in
1338 | check_invalid_merges cursor.
1339 | S V Sowjanya 16-NOV-05 Bug 4693912: Modified cursor check_invalid_merges to
1340 | exclude records with duplicate_address_id '-1'
1341 +===========================================================================*/
1342
1343 PROCEDURE validate_merges (
1344 req_id NUMBER,
1345 p_process_flag VARCHAR2
1346 ) IS
1347
1348 --Select statements to select invalid merges
1349
1350 CURSOR val_all_sites_merged IS
1351 /** N/A
1352 If merging to a different customer,
1353 all SHIP_TO, BILL_TO and MARKET sites must be merged */
1354
1355 /** Bug 1610924
1356 If merging to a different customer,
1357 all of the site uses much be merged.
1358
1359 Select
1360 all sites that must be merged
1361 MINUS
1362 all sites specified in ra_customer_merges
1363 Migration to new customer model.
1364 -------------------------------
1365 With the new cust. model, cust acct and sites are already
1366 striped by ou. The cust accts are no longer global.
1367 Because the sites will not be referenced in other ou,
1368 the tables that will replace RA_ADDRESSES and RA_SITE_USES
1369 will be HZ_CUST_ACCT_SITES and HZ_CUST_SITE_USES.
1370 Columns will be changed correspondingly.
1371 */
1372
1373 --Bug 1725662: rewrite query to use index on
1374 --ra_customer_merge_headers.(request_id, process_flag);
1375
1376 SELECT su.site_use_id site_use_id,
1377 mh.duplicate_id duplicate_id
1378 FROM hz_cust_acct_sites addr,
1379 hz_cust_site_uses su,
1380 ra_customer_merge_headers mh
1381 WHERE mh.request_id = req_id
1382 AND mh.process_flag = p_process_flag
1383 AND (mh.org_id = -1 OR (mh.org_id <> -1 AND addr.org_id = mh.org_id)) --SSUptake
1384 AND mh.duplicate_id <> mh.customer_id
1385 AND su.cust_acct_site_id = addr.cust_acct_site_id
1386 AND addr.cust_account_id = mh.duplicate_id
1387 AND NOT EXISTS (
1388 SELECT 'same site in merge detail'
1389 FROM ra_customer_merges m
1390 WHERE m.customer_merge_header_id = mh.customer_merge_header_id
1391 AND m.duplicate_site_id = su.site_use_id
1392 AND m.org_id = su.org_id
1393 );
1394
1395 --Bug 1391134: ignore those records with customer_createsame = 'Y'
1396 --in ra_customer_merges table.
1397
1398 CURSOR check_invalid_merges IS
1399 /**
1400 Merge is INVALID if:
1401 - customer_site_id = duplicate_site_id of another row
1402 - duplicate_site_id = customer_site_id of another row
1403 - duplicate_site_id = duplicate_site_id of another row
1404 */
1405
1406 SELECT m.duplicate_id duplicate_id
1407 FROM ra_customer_merges m
1408 WHERE m.process_flag = p_process_flag
1409 AND m.request_id = req_id
1410 AND duplicate_address_id <> -1 --4693912
1411 -- AND (m.duplicate_site_id <> -99 AND m.customer_site_id <> -99)
1412 AND ((m.customer_createsame <> 'Y'
1413 AND (m.customer_site_id IN (
1414 SELECT m2.duplicate_site_id
1415 FROM ra_customer_merges m2
1416 WHERE m2.rowid <> m.rowid
1417 AND m2.process_flag = p_process_flag
1418 AND m2.duplicate_address_id = m.customer_address_id )
1419 OR m.duplicate_site_id IN (
1420 SELECT m2.customer_site_id
1421 FROM ra_customer_merges m2
1422 WHERE m2.rowid <> m.rowid
1423 AND m2.process_flag = p_process_flag
1424 AND m2.customer_address_id = m.duplicate_address_id )))
1425 OR m.duplicate_site_id IN (
1426 SELECT m2.duplicate_site_id
1427 FROM ra_customer_merges m2
1428 WHERE m2.rowid <> m.rowid
1429 AND m2.process_flag = p_process_flag
1430 --AND m2.duplicate_site_id <> -99
1431 AND m2.duplicate_address_id = m.duplicate_address_id));
1432
1433 missing_sites val_all_sites_merged%ROWTYPE;
1434 invalid_merges check_invalid_merges%ROWTYPE;
1435 error_msg VARCHAR2(240); -- Bug No: 3743818
1436 BEGIN
1437
1438 arp_message.set_line( 'ARP_CMERGE_MASTER.validate_merges()+' );
1439
1440 FOR missing_sites IN val_all_sites_merged LOOP
1441
1442 --Mark Invalid Merges
1443 UPDATE ra_customer_merges
1444 SET process_flag = 'ERROR 1'
1445 WHERE duplicate_id = missing_sites.duplicate_id
1446 AND request_id = req_id
1447 AND process_flag = p_process_flag;
1448
1449 --Bug 1725662: Add the following sql to save performance in
1450 --clear_error_merge_rows.
1451
1452 UPDATE ra_customer_merge_headers
1453 SET process_flag = 'ERROR 1'
1454 WHERE duplicate_id = missing_sites.duplicate_id
1455 AND request_id = req_id
1456 AND process_flag = p_process_flag;
1457 ---Start of Bug No : 3743818
1458 fnd_message.set_name('AR', 'AR_CMERGE_MISSING_SITES');
1459 error_msg := fnd_message.get();
1460 arp_message.set_line('Duplicate Id : '|| missing_sites.duplicate_id ||' -- '||error_msg);
1461 --End Of Bug No: 3743818
1462
1463 END LOOP;
1464
1465 FOR invalid_merges IN check_invalid_merges LOOP
1466
1467 --Mark Invalid Merges
1468 UPDATE ra_customer_merges
1469 SET process_flag = 'ERROR 2'
1470 WHERE duplicate_id = invalid_merges.duplicate_id
1471 AND request_id = req_id
1472 AND process_flag = p_process_flag;
1473
1474 --Bug 1725662: Add the following sql to save performance in
1475 --clear_error_merge_rows.
1476
1477 UPDATE ra_customer_merge_headers
1478 SET process_flag = 'ERROR 2'
1479 WHERE duplicate_id = invalid_merges.duplicate_id
1480 AND request_id = req_id
1481 AND process_flag = p_process_flag;
1482
1483 ---Start of Bug No : 3743818
1484 fnd_message.set_name('AR', 'AR_CMERGE_DUPLICATE_SITE');
1485 error_msg := fnd_message.get();
1486 arp_message.set_line( ' Duplicate Id : '||invalid_merges.duplicate_id ||' -- '||error_msg);
1487 --End Of Bug No: 3743818
1488
1489 END LOOP;
1490
1491 arp_message.set_line( 'ARP_CMERGE_MASTER.validate_merges()-' );
1492
1493 EXCEPTION
1494
1495 WHEN OTHERS THEN
1496 arp_message.set_error( 'ARP_CMERGE_MASTER.validate_merges' );
1497 RAISE;
1498
1499 END validate_merges;
1500
1501 /*===========================================================================+
1502 | PROCEDURE
1503 | partiton_merge_data
1504 |
1505 | DESCRIPTION
1506 | Partition merge sets
1507 |
1508 | SCOPE - PUBLIC
1509 |
1510 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1511 |
1512 | ARGUMENTS : IN:
1513 | req_id
1514 | p_process_flag
1515 | OUT:
1516 | last_set
1517 | IN/ OUT:
1518 |
1519 | RETURNS : NONE
1520 |
1521 | NOTES
1522 |
1523 | MODIFICATION HISTORY
1524 |
1525 +===========================================================================*/
1526
1527 PROCEDURE partiton_merge_data(
1528 req_id NUMBER,
1529 last_set OUT NOCOPY NUMBER,
1530 p_process_flag VARCHAR2
1531 ) IS
1532
1533 CURSOR partition is
1534 SELECT rowid, duplicate_id
1535 FROM ra_customer_merges
1536 WHERE request_id = req_id
1537 AND process_flag = p_process_flag
1538 ORDER BY duplicate_id;
1539
1540 v_rowid VARCHAR2(24);
1541 v_prev_customer_id NUMBER := null;
1542 v_customer_id NUMBER := null;
1543 v_count NUMBER := 0;
1544 v_set_size NUMBER;
1545 v_last_set NUMBER := 1;
1546
1547 BEGIN
1548
1549 arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()+' );
1550
1551 v_set_size := fnd_profile.value('AR_CMERGE_SET_SIZE');
1552
1553 OPEN partition;
1554
1555 LOOP
1556 FETCH partition into v_rowid, v_customer_id;
1557 EXIT when partition%notfound;
1558
1559 --always group by duplicate_id, even if set limit has been
1560 --exceeded
1561
1562 --check if first or new customer
1563 IF ( v_prev_customer_id is null or
1564 v_prev_customer_id <> v_customer_id ) then
1565
1566 --check if set limit has been reached
1567 IF ( v_count >= v_set_size ) then
1568 v_last_set := v_last_set + 1;
1569 v_count := 0;
1570 END IF;
1571
1572 v_prev_customer_id := v_customer_id;
1573
1574 END IF;
1575
1576 v_count := v_count + 1;
1577
1578 UPDATE ra_customer_merges
1579 SET set_number = v_last_set
1580 WHERE rowid = v_rowid;
1581
1582 END loop;
1583
1584 CLOSE partition;
1585
1586 last_set := v_last_set;
1587
1588
1589 arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()-' );
1590
1591 EXCEPTION
1592
1593 WHEN OTHERS THEN
1594 arp_message.set_error( 'ARP_CMERGE_MASTER.partition_merge_date' );
1595 RAISE;
1596
1597 END partiton_merge_data;
1598
1599 /*===========================================================================+
1600 | PROCEDURE
1601 | unpartiton_merge_data
1602 |
1603 | DESCRIPTION
1604 | Partition merge sets to a set size of 1 inorder to
1605 | identify the bad merge records.
1606 |
1607 | SCOPE - PUBLIC
1608 |
1609 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1610 |
1611 | ARGUMENTS : IN:
1612 | req_id
1613 | set_num
1614 | OUT:
1615 | last_set
1616 | IN/ OUT:
1617 |
1618 | RETURNS : NONE
1619 |
1620 | NOTES
1621 |
1622 | MODIFICATION HISTORY
1623 | Rajeshwari 04-02-2003 Bug 2669389 Created.
1624 | Rajeshwari P 04-07-2003 Modified code to set
1625 | the set_number starting
1626 | from 1001 to avoid conflict.
1627 +===========================================================================*/
1628
1629 PROCEDURE unpartiton_merge_data(
1630 req_id NUMBER,
1631 last_set OUT NOCOPY NUMBER,
1632 set_num NUMBER
1633 ) IS
1634
1635 CURSOR partition is
1636 SELECT rowid, duplicate_id
1637 FROM ra_customer_merges
1638 WHERE request_id = req_id
1639 AND set_number = set_num
1640 ORDER BY duplicate_id;
1641
1642 v_rowid VARCHAR2(24);
1643 v_prev_customer_id NUMBER := null;
1644 v_customer_id NUMBER := null;
1645 v_count NUMBER := 1000;
1646 v_set_size NUMBER;
1647 v_last_set NUMBER := 1001;
1648 l_count number;
1649
1650 BEGIN
1651
1652 arp_message.set_line( 'ARP_CMERGE_MASTER.unpartiton_merge_data()+' );
1653
1654 v_set_size := 1001;
1655
1656 OPEN partition;
1657
1658 LOOP
1659 FETCH partition into v_rowid, v_customer_id;
1660 EXIT when partition%notfound;
1661
1662 --always group by duplicate_id, even if set limit has been
1663 --exceeded
1664
1665 --check if first or new customer
1666 IF ( v_prev_customer_id is null or
1667 v_prev_customer_id <> v_customer_id ) then
1668
1669 --check if set limit has been reached
1670 IF ( v_count >= v_set_size ) then
1671 v_last_set := v_last_set + 1;
1672 v_count := 1000;
1673 END IF;
1674
1675 v_prev_customer_id := v_customer_id;
1676
1677 END IF;
1678
1679 v_count := v_count + 1;
1680
1681 UPDATE ra_customer_merges
1682 SET set_number = v_last_set
1683 WHERE request_id = req_id
1684 AND rowid = v_rowid;
1685
1686 begin
1687 select set_number into l_count
1688 from ra_customer_merges
1689 where request_id = req_id
1690 AND set_number = v_last_set
1691 AND rowid = v_rowid;
1692 exception
1693 when no_data_found then
1694 NULL;
1695 end;
1696
1697 END loop;
1698
1699 CLOSE partition;
1700
1701 last_set := v_last_set;
1702
1703
1704 arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()-' );
1705
1706 EXCEPTION
1707
1708 WHEN OTHERS THEN
1709 arp_message.set_error( 'ARP_CMERGE_MASTER.partition_merge_date' );
1710 RAISE;
1711
1712 END unpartiton_merge_data;
1713 /*===========================================================================+
1714 | PROCEDURE
1715 | done_merge_rows
1716 |
1717 | DESCRIPTION
1718 | Sets process flag to 'Y' in the header and merges table
1719 | for successful merges
1720 |
1721 | SCOPE - PUBLIC
1722 |
1723 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1724 |
1725 | ARGUMENTS : IN:
1726 | req_id
1727 | set_num
1728 | OUT:
1729 | IN/ OUT:
1730 |
1731 | RETURNS : NONE
1732 |
1733 | NOTES
1734 |
1735 | MODIFICATION HISTORY
1736 | Jianying Huang 29-NOV-00 Bug 1519688: Modified procedure
1737 | based on the change of merge form:
1738 | 'display request id instantly after hit merge button'
1739 | Jianying Huang 21-DEC-00 Call read_temporary_table for debug purpose.
1740 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
1741 | the new added indexes for performance improvements.
1742 |
1743 +===========================================================================*/
1744
1745 PROCEDURE done_merge_rows (
1746 req_id NUMBER,
1747 set_num NUMBER
1748 ) IS
1749
1750 BEGIN
1751
1752 arp_message.set_line( 'ARP_CMERGE_MASTER.done_merge_rows()+' );
1753
1754 --delete customer alternative names
1755 arp_cmerge_master.delete_customer_alt_names ( req_id , set_num ) ;
1756
1757 UPDATE ra_customer_merges
1758 SET process_flag = 'Y',
1759 last_update_date = sysdate,
1760 last_updated_by = hz_utility_v2pub.user_id,
1761 last_update_login = hz_utility_v2pub.last_update_login,
1762 program_application_id = hz_utility_v2pub.program_application_id,
1763 program_id = hz_utility_v2pub.program_id,
1764 program_update_date = sysdate
1765 WHERE request_id = req_id
1766 AND set_number = set_num
1767 AND process_flag = 'N';
1768
1769 --Bug 1519688: Do not need to set request_id.
1770 --Bug 1725662: replace 'EXISTS' with 'IN' to use index.
1771
1772 UPDATE ra_customer_merge_headers mh
1773 SET (process_flag,
1774 -- request_id,
1775 last_update_date,
1776 last_updated_by,
1777 last_update_login,
1778 program_application_id,
1779 program_id,
1780 program_update_date) = (
1781 SELECT
1782 m.process_flag,
1783 -- m.request_id,
1784 sysdate,
1785 m.last_updated_by,
1786 m.last_update_login,
1787 m.program_application_id,
1788 m.program_id,
1789 sysdate
1790 FROM ra_customer_merges m
1791 WHERE m.request_id = req_id
1792 AND m.set_number = set_num
1793 AND m.process_flag = 'Y'
1794 AND mh.customer_merge_header_id =
1795 m.customer_merge_header_id
1796 AND ROWNUM = 1)
1797 WHERE mh.customer_merge_header_id IN (
1798 SELECT m.customer_merge_header_id
1799 FROM ra_customer_merges m
1800 WHERE m.request_id = req_id
1801 AND m.process_flag = 'Y'
1802 AND m.set_number = set_num );
1803
1804 --Added for debug purpose, should be always commented out.
1805 --read_temporary_table;
1806
1807 arp_message.set_line( 'ARP_CMERGE_MASTER.done_merge_rows()-' );
1808
1809 EXCEPTION
1810
1811 WHEN OTHERS THEN
1812 arp_message.set_error( 'ARP_CMERGE_MASTER.done_merge_rows' );
1813 RAISE;
1814
1815 END done_merge_rows;
1816
1817 /*===========================================================================+
1818 | PROCEDURE
1819 | reset_merge_rows
1820 |
1821 | DESCRIPTION
1822 | Reset rows for reprocessing
1823 |
1824 | SCOPE - PUBLIC
1825 |
1826 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1827 |
1828 | ARGUMENTS : IN:
1829 | req_id
1830 | set_num
1831 | OUT:
1832 | IN/ OUT:
1833 |
1834 | RETURNS : NONE
1835 |
1836 | NOTES
1837 |
1838 | MODIFICATION HISTORY
1839 | Jianying Huang 29-NOV-00 Bug 1519688: Modified procedure
1840 | based on the change of merge form:
1841 | 'display request id instantly after hit merge button'
1842 | Jianying Huang 07-DEC-00 Reset merge header table.
1843 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
1844 | the new added indexes for performance improvements.
1845 | Jianying Huang 07-JUN-01 Should not update set_number to NULL when
1846 | update ra_customer_merges. Commented the statement
1847 | out.
1848 |
1849 +===========================================================================*/
1850
1851 PROCEDURE reset_merge_rows (
1852 req_id NUMBER,
1853 set_num NUMBER,
1854 p_process_flag VARCHAR2
1855 ) IS
1856
1857 BEGIN
1858
1859 --arp_message.set_line( 'ARP_CMERGE_MASTER.reset_merge_rows()+' );
1860
1861 --If its database problem, set back status to 'SAVED'
1862 --Bug fix 2669389
1863 if (p_process_flag = 'SAVED') then
1864
1865 arp_message.set_line('in resetmerge flag is saved');
1866 UPDATE ra_customer_merges
1867 SET
1868 -- set_number = null,
1869 -- request_id = null,
1870 process_flag = 'SAVED',
1871 last_update_date = sysdate,
1872 last_updated_by = hz_utility_v2pub.user_id,
1873 last_update_login = hz_utility_v2pub.last_update_login,
1874 program_application_id = hz_utility_v2pub.program_application_id,
1875 program_id = hz_utility_v2pub.program_id,
1876 program_update_date = sysdate
1877 WHERE request_id = req_id
1878 AND set_number = set_num
1879 AND process_flag = p_process_flag;
1880
1881
1882 UPDATE ra_customer_merge_headers mh
1883 SET (process_flag,
1884 last_update_date,
1885 last_updated_by,
1886 last_update_login,
1887 program_application_id,
1888 program_id,
1889 program_update_date) = (
1890 SELECT
1891 m.process_flag,
1892 sysdate,
1893 m.last_updated_by,
1894 m.last_update_login,
1895 m.program_application_id,
1896 m.program_id,
1897 sysdate
1898 FROM ra_customer_merges m
1899 WHERE m.request_id = req_id
1900 AND m.set_number = set_num
1901 AND m.process_flag = 'SAVED'
1902 AND mh.customer_merge_header_id =
1903 m.customer_merge_header_id
1904 AND ROWNUM = 1)
1905 WHERE mh.customer_merge_header_id IN (
1906 SELECT m.customer_merge_header_id
1907 FROM ra_customer_merges m
1908 WHERE m.request_id = req_id
1909 AND m.process_flag = 'SAVED'
1910 AND m.set_number = set_num );
1911 --Bug Fix 2669389
1912 --If business validation failure set process flag to 'FAILED'
1913
1914 else
1915 --Bug 1519688: should not reset request_id to NULL.
1916 --Set process_flag = 'FAILED' indicate this is a failed merge, we need
1917 --to pick it up next time.
1918
1919 UPDATE ra_customer_merges
1920 SET
1921 -- set_number = null,
1922 -- request_id = null,
1923 process_flag = 'FAILED',
1924 last_update_date = sysdate,
1925 last_updated_by = hz_utility_v2pub.user_id,
1926 last_update_login = hz_utility_v2pub.last_update_login,
1927 program_application_id = hz_utility_v2pub.program_application_id,
1928 program_id = hz_utility_v2pub.program_id,
1929 program_update_date = sysdate
1930 WHERE request_id = req_id
1931 AND set_number = set_num
1932 AND process_flag = p_process_flag;
1933
1934 --reset merge header table.
1935
1936 --Bug 1725662: replace 'EXISTS' with 'IN' to use index.
1937
1938 UPDATE ra_customer_merge_headers mh
1939 SET (process_flag,
1940 last_update_date,
1941 last_updated_by,
1942 last_update_login,
1943 program_application_id,
1944 program_id,
1945 program_update_date) = (
1946 SELECT
1947 m.process_flag,
1948 sysdate,
1949 m.last_updated_by,
1950 m.last_update_login,
1951 m.program_application_id,
1952 m.program_id,
1953 sysdate
1954 FROM ra_customer_merges m
1955 WHERE m.request_id = req_id
1956 AND m.set_number = set_num
1957 AND m.process_flag = 'FAILED'
1958 AND mh.customer_merge_header_id =
1959 m.customer_merge_header_id
1960 AND ROWNUM = 1)
1961 WHERE mh.customer_merge_header_id IN (
1962 SELECT m.customer_merge_header_id
1963 FROM ra_customer_merges m
1964 WHERE m.request_id = req_id
1965 AND m.process_flag = 'FAILED'
1966 AND m.set_number = set_num );
1967
1968 end if;
1969
1970 --arp_message.set_line( 'ARP_CMERGE_MASTER.reset_merge_rows()-' );
1971
1972 EXCEPTION
1973
1974 WHEN OTHERS THEN
1975 --arp_message.set_error( 'ARP_CMERGE_MASTER.reset_merge_rows' );
1976 RAISE;
1977
1978 END reset_merge_rows;
1979
1980 /*===========================================================================+
1981 | PROCEDURE
1982 | clear_error_merge_rows
1983 |
1984 | DESCRIPTION
1985 | Remove error status from records that failed validation
1986 | so that they maybe re-submitted
1987 |
1988 | SCOPE - PUBLIC
1989 |
1990 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1991 |
1992 | ARGUMENTS : IN:
1993 | req_id
1994 | OUT:
1995 | IN/ OUT:
1996 |
1997 | RETURNS : NONE
1998 |
1999 | NOTES
2000 |
2001 | MODIFICATION HISTORY
2002 | Jianying Huang 29-NOV-00 Bug 1519688: Modified procedure
2003 | based on the change of merge form:
2004 | 'display request id instantly after hit merge button'
2005 | Jianying Huang 07-DEC-00 Reset merge header table.
2006 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
2007 | the new added indexes for performance improvements.
2008 |
2009 +===========================================================================*/
2010
2011 PROCEDURE clear_error_merge_rows (
2012 req_id NUMBER
2013 ) IS
2014
2015 BEGIN
2016
2017 --arp_message.set_line( 'ARP_CMERGE_MASTER.clear_error_merge_rows()+' );
2018
2019 --Bug 1518688: should not reset request_id to NULL.
2020 --Set process_flag = 'FAILED' indicate this is a failed merge, we need
2021 --to pick it up next time.
2022
2023 --Bug 1725662: rewrite query to use new index.
2024
2025 UPDATE ra_customer_merge_headers
2026 SET process_flag = decode(process_flag,
2027 'ERROR 1', 'FAILED',
2028 'ERROR 2', 'FAILED'),
2029 -- request_id = null,
2030 last_update_date = sysdate,
2031 last_updated_by = hz_utility_v2pub.user_id,
2032 last_update_login = hz_utility_v2pub.last_update_login,
2033 program_application_id = hz_utility_v2pub.program_application_id,
2034 program_id = hz_utility_v2pub.program_id,
2035 program_update_date = sysdate
2036 WHERE process_flag in ('ERROR 1', 'ERROR 2')
2037 AND request_id = req_id;
2038
2039 UPDATE ra_customer_merges m
2040 SET (process_flag,
2041 last_update_date,
2042 last_updated_by,
2043 last_update_login,
2044 program_application_id,
2045 program_id,
2046 program_update_date) = (
2047 SELECT
2048 mh.process_flag,
2049 sysdate,
2050 mh.last_updated_by,
2051 mh.last_update_login,
2052 mh.program_application_id,
2053 mh.program_id,
2054 sysdate
2055 FROM ra_customer_merge_headers mh
2056 WHERE mh.request_id = req_id
2057 AND mh.process_flag = 'FAILED'
2058 AND mh.customer_merge_header_id =
2059 m.customer_merge_header_id
2060 AND ROWNUM = 1)
2061 WHERE m.customer_merge_header_id IN (
2062 SELECT mh.customer_merge_header_id
2063 FROM ra_customer_merge_headers mh
2064 WHERE mh.request_id = req_id
2065 AND mh.process_flag = 'FAILED' )
2066 AND process_flag in ('ERROR 1', 'ERROR 2');
2067
2068 --arp_message.set_line( 'ARP_CMERGE_MASTER.clear_error_merge_rows()-' );
2069
2070 EXCEPTION
2071
2072 WHEN OTHERS THEN
2073 --arp_message.set_error( 'ARP_CMERGE_MASTER.clear_error_merge_rows' );
2074 RAISE;
2075
2076 END clear_error_merge_rows;
2077
2078 /*===========================================================================+
2079 | PROCEDURE
2080 | remove_request
2081 |
2082 | DESCRIPTION
2083 | For handling terminated requests, remove request to the AR
2084 | concurrent request table
2085 |
2086 | SCOPE - PUBLIC
2087 |
2088 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2089 |
2090 | ARGUMENTS : IN:
2091 | req_id
2092 | OUT:
2093 | IN/ OUT:
2094 |
2095 | RETURNS : NONE
2096 |
2097 | NOTES
2098 |
2099 | MODIFICATION HISTORY
2100 |
2101 +===========================================================================*/
2102
2103 PROCEDURE remove_request (
2104 req_id NUMBER
2105 ) IS
2106
2107 BEGIN
2108
2109 --arp_message.set_line( 'ARP_CMERGE_MASTER.remove_request()+' );
2110
2111 DELETE FROM ar_conc_process_requests
2112 WHERE request_id = req_id;
2113
2114 --arp_message.set_line( 'ARP_CMERGE_MASTER.remove_request()-' );
2115
2116 EXCEPTION
2117
2118 WHEN OTHERS THEN
2119 --arp_message.set_error( 'ARP_CMERGE_MASTER.remove_request' );
2120 RAISE;
2121
2122 END remove_request;
2123
2124 /*===========================================================================+
2125 | PROCEDURE
2126 | delete_customer_alt_names
2127 |
2128 | DESCRIPTION
2129 | Deletes customer alternative names.
2130 |
2131 | SCOPE - PRIVATE
2132 |
2133 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2134 |
2135 | ARGUMENTS : IN:
2136 | req_id
2137 | set_num
2138 | IN/ OUT:
2139 |
2140 | RETURNS : NONE
2141 |
2142 | NOTES
2143 |
2144 | MODIFICATION HISTORY
2145 |
2146 +===========================================================================*/
2147
2148 PROCEDURE delete_customer_alt_names(
2149 req_id NUMBER,
2150 set_num NUMBER
2151 ) IS
2152
2153 v_prev_duplicate_id NUMBER := null ;
2154 v_duplicate_id NUMBER;
2155 v_customer_id NUMBER;
2156 v_duplicate_site_id NUMBER;
2157 v_lock_status NUMBER;
2158
2159 CURSOR alt_names_deletion IS
2160 SELECT duplicate_id , customer_id , duplicate_site_id
2161 FROM ra_customer_merges
2162 WHERE request_id = req_id
2163 AND set_number = set_num
2164 AND process_flag = 'N'
2165 ORDER BY duplicate_id ;
2166
2167 BEGIN
2168
2169 arp_message.set_line( 'ARP_CMERGE_MASTER.alt_name_deletion()+' );
2170
2171 -- Bug 2092530: Removed condition that checked the now-obsolete
2172 -- 'AR_ALT_NAME_SEARCH' profile option before executing the delete.
2173
2174 OPEN alt_names_deletion;
2175 LOOP
2176 FETCH alt_names_deletion INTO v_duplicate_id, v_customer_id,
2177 v_duplicate_site_id;
2178 EXIT WHEN alt_names_deletion%notfound;
2179
2180 IF ( v_duplicate_id <> v_customer_id ) THEN
2181 IF ( ( v_prev_duplicate_id IS NULL ) OR
2182 ( v_prev_duplicate_id <> v_duplicate_id ) )
2183 THEN
2184 arp_cust_alt_match_pkg.lock_match (
2185 v_duplicate_id, NULL, v_lock_status );
2186
2187 IF ( v_lock_status = 1 ) THEN
2188 arp_cust_alt_match_pkg.delete_match ( v_duplicate_id, NULL, NULL );
2189 END IF;
2190
2191 v_prev_duplicate_id := v_duplicate_id ;
2192
2193 END IF ;
2194 ELSE
2195 arp_cust_alt_match_pkg.lock_match (
2196 v_duplicate_id, v_duplicate_site_id, v_lock_status );
2197
2198 IF ( v_lock_status = 1 ) THEN
2199 arp_cust_alt_match_pkg.delete_match (
2200 v_duplicate_id, v_duplicate_site_id, NULL );
2201 END IF;
2202
2203 END IF;
2204
2205 END LOOP;
2206 CLOSE alt_names_deletion;
2207
2208 arp_message.set_line( 'ARP_CMERGE_MASTER.alt_name_deletion()-' );
2209
2210 EXCEPTION
2211
2212 WHEN OTHERS THEN
2213 arp_message.set_error( 'ARP_CMERGE_MASTER.delete_customer_alt_names' );
2214 RAISE;
2215
2216 END delete_customer_alt_names;
2217
2218 /*===========================================================================+
2219 | PROCEDURE
2220 | read_temporary_table
2221 |
2222 | DESCRIPTION
2223 | Read mapping info. from temporary table. Created for debug purpose.
2224 |
2225 | SCOPE - PUBLIC
2226 |
2227 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2228 |
2229 | ARGUMENTS : IN:
2230 | OUT:
2231 | IN/ OUT:
2232 |
2233 | RETURNS : NONE
2234 |
2235 | NOTES
2236 |
2237 | MODIFICATION HISTORY
2238 | Jianying Huang 21-DEC-00 Created for debug purpose.
2239 |
2240 +===========================================================================*/
2241
2242 PROCEDURE read_temporary_table
2243 IS
2244
2245 l_sql VARCHAR2(1000);
2246
2247 BEGIN
2248
2249 arp_message.set_line('read temporary table');
2250
2251 l_sql :=
2252 'DECLARE ' ||
2253 'CURSOR c IS ' ||
2254 'SELECT type, old_id, new_id ' ||
2255 'FROM ' || arp_cmerge_arcus.g_table_name || ';' ||
2256 'l_old_id NUMBER; ' ||
2257 'l_new_id NUMBER; ' ||
2258 'l_type VARCHAR2(30); ' ||
2259 'BEGIN ' ||
2260 'arp_message.set_line( ''ORG CONTACT MAPPING : '' );' ||
2261 'OPEN c; ' ||
2262 'LOOP ' ||
2263 'FETCH c INTO l_type, l_old_id, l_new_id; ' ||
2264 'EXIT WHEN c%NOTFOUND; ' ||
2265 'arp_message.set_line( ' ||
2266 '''type = '' || l_type || '', '' || ' ||
2267 '''old = '' || to_char(l_old_id) || '', '' || ' ||
2268 '''new = '' || to_char(l_new_id) );' ||
2269 'END LOOP;' ||
2270 'CLOSE c; ' ||
2271 'END; ';
2272
2273 EXECUTE IMMEDIATE l_sql;
2274
2275 END;
2276 /*===========================================================================+
2277 | PROCEDURE
2278 | veto_delete
2279 |
2280 | DESCRIPTION
2281 | For preventing the delete of accounts and other records off accounts
2282 |
2283 |
2284 | SCOPE - PUBLIC
2285 |
2286 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2287 |
2288 | ARGUMENTS : IN: req_id NUMBER , set_num NUMBER, from_customer_id NUMBER
2289 | veto_reason VARCHAR2
2290 |
2291 | OUT:
2292 | IN/ OUT:
2293 |
2294 | RETURNS : NONE
2295 |
2296 | NOTES
2297 |
2298 | MODIFICATION HISTORY -
2299 | Jyoti Pandey 02-10-2002 Created.
2300 |
2301 +===========================================================================*/
2302 PROCEDURE veto_delete(req_id NUMBER,
2303 set_num NUMBER,
2304 from_customer_id NUMBER ,
2305 veto_reason VARCHAR2,
2306 part_delete VARCHAR2 DEFAULT 'N') IS
2307
2308 BEGIN
2309
2310 arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()+' );
2311
2312 /*--Unset the delete_duplicate_flag in ra_customer_merges --*/
2313 UPDATE ra_customer_merges m
2314 SET delete_duplicate_flag = 'N'
2315 WHERE m.duplicate_id = from_customer_id
2316 AND m.process_flag = 'N'
2317 AND m.request_id = req_id
2318 AND m.set_number = set_num
2319 AND part_delete = 'N'; --5747129
2320
2321 /*--Also unset the delete duplicate flag in merge header table --*/
2322 UPDATE ra_customer_merge_headers
2323 SET delete_duplicate_flag = 'N'
2324 WHERE customer_merge_header_id in
2325 (select customer_merge_header_id
2326 from ra_customer_merges m
2327 where m.duplicate_id = from_customer_id
2328 AND m.process_flag = 'N'
2329 AND m.request_id = req_id
2330 AND m.set_number = set_num)
2331 AND part_delete = 'N'; --5747129
2332
2333 /*--Unset the status to 'I'for HZ_CUST_SITE_USES which was set to 'D'--*/
2334 --undo
2335 UPDATE HZ_CUST_SITE_USES_ALL su --SSUptake
2336 SET status = 'I',
2337 last_update_date = sysdate,
2338 last_updated_by = hz_utility_v2pub.user_id,
2339 last_update_login = hz_utility_v2pub.last_update_login,
2340 request_id = req_id,
2341 program_application_id = hz_utility_v2pub.program_application_id,
2342 program_id = hz_utility_v2pub.program_id,
2343 program_update_date = sysdate
2344 WHERE EXISTS
2345 ( select 'Y'
2346 from hz_cust_acct_sites_ALL site,ra_customer_merges m --SSUptake
2347 where site.cust_account_id = from_customer_id
2348 and m.duplicate_address_id = site.cust_acct_site_id
2349 and su.cust_acct_site_id = site.cust_acct_site_id
2350 and m.request_id = req_id
2351 and m.process_flag = 'N'
2352 and m.set_number = set_num
2353 and m.duplicate_id = from_customer_id
2354 and m.org_id = site.org_id --SSUptake
2355 and su.org_id = site.org_id --SSUptake
2356 )
2357 AND status = 'D'
2358 AND part_delete = 'N'; --5747129
2359
2360 arp_message.set_line(SQL%ROWCOUNT||' '||'Row(s) updated in HZ_CUST_SITE_USES');
2361
2362
2363 /*--Unset the status to 'I'for HZ_CUST_ACCT_SITES which was set to 'D' --*/
2364 UPDATE HZ_CUST_ACCT_SITES_ALL addr --SSUptake
2365 set status = 'I',
2366 last_update_date = sysdate,
2367 last_updated_by = hz_utility_v2pub.user_id,
2368 last_update_login = hz_utility_v2pub.last_update_login,
2369 request_id = req_id,
2370 program_application_id = hz_utility_v2pub.program_application_id,
2371 program_id = hz_utility_v2pub.program_id,
2372 program_update_date = sysdate
2373 where addr.cust_account_id = from_customer_id
2374 AND EXISTS (select 'Y' from ra_customer_merges m
2375 where m.request_id = req_id
2376 and m.process_flag = 'N'
2377 and m.set_number = set_num
2378 and m.duplicate_id = from_customer_id
2379 and m.duplicate_address_id = addr.cust_acct_site_id
2380 and m.org_id = addr.org_id --SSUptake
2381 )
2382 AND addr.status = 'D'
2383 AND part_delete = 'N'; --5747129
2384
2385 arp_message.set_line(SQL%ROWCOUNT||' '||'Row(s) updated in HZ_CUST_ACCT_SITES');
2386
2387 /*--Unset the status to 'I' for HZ_CUST_ACCOUNTS which was set to 'D'*/
2388 UPDATE HZ_CUST_ACCOUNTS acct
2389 set status = decode(part_delete,'N','I','A'),
2390 last_update_date = sysdate,
2391 last_updated_by = hz_utility_v2pub.user_id,
2392 last_update_login = hz_utility_v2pub.last_update_login,
2393 request_id = req_id,
2394 program_application_id = hz_utility_v2pub.program_application_id,
2395 program_id = hz_utility_v2pub.program_id,
2396 program_update_date = sysdate
2397 where acct.cust_account_id = from_customer_id
2398 and status ='D';
2399
2400 arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUST_ACCOUNTS');
2401
2402 /*--Unset the status to 'I' for HZ_CUST_ACCT_RELATE cust_account_id */
2403 UPDATE HZ_CUST_ACCT_RELATE_ALL rel
2404 SET status = 'I',
2405 last_update_date = sysdate,
2406 last_updated_by = hz_utility_v2pub.user_id,
2407 last_update_login = hz_utility_v2pub.last_update_login,
2408 request_id = req_id,
2409 program_application_id =hz_utility_v2pub.program_application_id,
2410 program_id = hz_utility_v2pub.program_id,
2411 program_update_date = sysdate
2412 WHERE rel.cust_account_id = from_customer_id
2413 AND EXISTS (select 'Y' from ra_customer_merges m
2414 where m.request_id = req_id
2415 and m.process_flag = 'N'
2416 and m.set_number = set_num
2417 and m.duplicate_id = rel.cust_account_id
2418 and m.org_id = rel.org_id --SSUptake
2419 )
2420 AND status ='D'
2421 AND part_delete = 'N'; --5747129
2422
2423 arp_message.set_line( SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUST_ACCT_RELATE for cust_account_id' );
2424
2425 /*--Unset the status to 'I' for HZ_CUST_ACCT_RELATE
2426 --related_cust_account_id*/
2427 UPDATE HZ_CUST_ACCT_RELATE_ALL rel2 --SSUptake
2428 SET status = 'I',
2429 last_update_date = sysdate,
2430 last_updated_by = hz_utility_v2pub.user_id,
2431 last_update_login = hz_utility_v2pub.last_update_login,
2432 request_id = req_id,
2433 program_application_id =hz_utility_v2pub.program_application_id,
2434 program_id = hz_utility_v2pub.program_id,
2435 program_update_date = sysdate
2436 WHERE related_cust_account_id = from_customer_id
2437 AND EXISTS (select 'Y' from ra_customer_merges m
2438 where m.request_id = req_id
2439 and m.process_flag = 'N'
2440 and m.set_number = set_num
2441 and m.duplicate_id = rel2.related_cust_account_id
2442 and m.org_id = rel2.org_id --SSUptake
2443 )
2444 AND status ='D'
2445 AND part_delete = 'N'; --5747129
2446
2447 arp_message.set_line( SQL%ROWCOUNT||' '|| 'Rows updated in HZ_CUST_ACCT_RELATE for related_cust_account_id' );
2448
2449 /*--unset the end_date of RA_CUST_RECEIPT_METHODS at account level
2450 *--not required at site level as all the site uses get merged for an acct
2451 *--delete is prevented by delete_duplicate_flag='N' */
2452
2453 UPDATE RA_CUST_RECEIPT_METHODS yt
2454 set end_date = null,
2455 last_update_date = sysdate,
2456 last_updated_by = hz_utility_v2pub.user_id,
2457 last_update_login = hz_utility_v2pub.last_update_login,
2458 request_id = req_id,
2459 program_application_id = hz_utility_v2pub.program_application_id,
2460 program_id = hz_utility_v2pub.program_id,
2461 program_update_date = sysdate
2462 WHERE customer_id = from_customer_id
2463 AND customer_id IN (
2464 SELECT m.duplicate_id
2465 FROM ra_customer_merges m
2466 WHERE m.process_flag = 'N'
2467 AND m.request_id = req_id
2468 AND m.set_number = set_num
2469 AND m.delete_duplicate_flag = 'N' )
2470 AND site_use_id IS NULL
2471 AND NOT EXISTS (
2472 SELECT 'active accounts exist'
2473 FROM hz_cust_accounts acct
2474 WHERE acct.cust_account_id = yt.customer_id
2475 AND acct.status = 'A' );
2476
2477 arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in RA_CUST_RECEIPT_METHODS');
2478
2479 /* ---Unset status to Inactive for HZ_CUSTOMER_PROFILES for both
2480 * ---acct and site level */
2481 UPDATE hz_customer_profiles yt
2482 SET status = 'I',
2483 last_update_date = sysdate,
2484 last_updated_by = hz_utility_v2pub.user_id,
2485 last_update_login = hz_utility_v2pub.last_update_login,
2486 request_id = req_id,
2487 program_application_id = hz_utility_v2pub.program_application_id,
2488 program_id = hz_utility_v2pub.program_id,
2489 program_update_date = sysdate
2490 WHERE yt.cust_account_id = from_customer_id
2491 AND status = 'D'
2492 AND part_delete = 'N'; --5747129
2493
2494 UPDATE hz_customer_profiles yt --5634398
2495 SET status = 'A',
2496 last_update_date = sysdate,
2497 last_updated_by = arp_standard.profile.user_id,
2498 last_update_login = arp_standard.profile.last_update_login,
2499 request_id = req_id,
2500 program_application_id = arp_standard.profile.program_application_id,
2501 program_id = arp_standard.profile.program_id,
2502 program_update_date = sysdate
2503 WHERE part_delete = 'Y'
2504 AND status = 'D'
2505 AND yt.cust_account_id = from_customer_id
2506 AND site_use_id is NULL ;
2507
2508
2509 arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUSTOMER_PROFILES');
2510
2511
2512 /* ---Profile amts in ar_cpa are not getting Inactivated
2513 ---so delete duplicate flag in merge table can handle that */
2514
2515 ---Updating the column MERGE_FAIL_MSG with veto reason
2516 UPDATE RA_CUSTOMER_MERGE_HEADERS
2517 SET MERGE_FAIL_MSG= veto_reason
2518 WHERE customer_merge_header_id in
2519 (select customer_merge_header_id
2520 from ra_customer_merges m
2521 where m.duplicate_id = from_customer_id
2522 AND m.process_flag = 'N'
2523 AND m.request_id = req_id
2524 AND m.set_number = set_num);
2525
2526 arp_message.set_line( SQL%ROWCOUNT || ' '|| 'Rows updated in RA_CUSTOMER_MERGE_HEADERS with veto reason :' ||' '|| veto_reason );
2527
2528 arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()-' );
2529
2530 END veto_delete;
2531
2532 --4230396
2533 PROCEDURE raise_events(p_req_id NUMBER) IS
2534 l_key VARCHAR2(240);
2535 l_list WF_PARAMETER_LIST_T;
2536 l_header_id NUMBER;
2537 CURSOR merges IS
2538 SELECT customer_merge_header_id
2539 FROM ra_customer_merge_headers
2540 WHERE request_id = p_req_id
2541 AND process_flag = 'Y';
2542 BEGIN
2543 arp_message.set_line( 'ARP_CMERGE_MASTER.raise_events() +');
2544
2545 OPEN merges;
2546 LOOP
2547 FETCH merges into l_header_id;
2548 EXIT WHEN merges%NOTFOUND;
2549 l_key := HZ_EVENT_PKG.item_key('oracle.apps.ar.hz.CustAccount.merge');
2550 -- initialization of object variables
2551 l_list := WF_PARAMETER_LIST_T();
2552 wf_event.addParameterToList(p_name => 'customer_merge_header_id',
2553 p_value => l_header_id,
2554 p_parameterlist => l_list);
2555 wf_event.addParameterToList(p_name => 'Q_CORRELATION_ID',
2556 p_value => 'oracle.apps.ar.hz.CustAccount.merge',
2557 p_parameterlist => l_list);
2558 -- Raise Event
2559 HZ_EVENT_PKG.raise_event(
2560 p_event_name => 'oracle.apps.ar.hz.CustAccount.merge',
2561 p_event_key => l_key,
2562 p_parameters => l_list );
2563 l_list.DELETE;
2564 END LOOP;
2565 CLOSE merges;
2566 arp_message.set_line( 'ARP_CMERGE_MASTER.raise_events() -');
2567 EXCEPTION
2568 WHEN OTHERS THEN
2569 arp_message.set_error( 'ARP_CMERGE_MASTER.raise_events' );
2570 RAISE;
2571 END raise_events;
2572
2573 END ARP_CMERGE_MASTER;