[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_MASTER
Source
1 PACKAGE BODY ARP_CMERGE_MASTER as
2 /* $Header: ARHCMSTB.pls 120.34 2011/10/05 12:25:55 vsegu 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 IF p_process_flag = 'PROCESSING' THEN
1103 UPDATE ra_customer_merge_headers
1104 SET process_flag = p_process_flag
1105 WHERE request_id = req_id;
1106 END IF;
1107
1108 IF p_process_flag = 'SAVED' THEN
1109 --3897822
1110 SELECT customer_merge_header_id, process_flag
1111 BULK COLLECT INTO l_header_id_t,l_process_flag_t
1112 FROM ra_customer_merge_headers mh
1113 WHERE process_flag IN ('PROCESSING', 'N')
1114 AND priority = p_priority;
1115
1116 OPEN c_requests(p_priority);
1117 LOOP
1118 FETCH c_requests INTO l_request_id, l_process_flag;
1119 EXIT WHEN c_requests%NOTFOUND;
1120
1121 l_pickup := FALSE;
1122
1123 IF l_request_id IS NOT NULL THEN
1124 IF ( FND_CONCURRENT.GET_REQUEST_STATUS(
1125 request_id => l_request_id,
1126 phase => l_conc_phase,
1127 status => l_conc_status,
1128 dev_phase => l_conc_dev_phase,
1129 dev_status => l_conc_dev_status,
1130 message => l_message ) )
1131 THEN
1132 IF (l_process_flag = 'PROCESSING' AND
1133 l_conc_dev_phase <> 'PENDING') OR
1134 (l_process_flag = 'N' AND
1135 l_conc_dev_phase <> 'RUNNING' )
1136 THEN
1137 l_pickup := TRUE;
1138 END IF;
1139 ELSE
1140 l_pickup := TRUE;
1141 END IF;
1142
1143 IF l_pickup THEN
1144 UPDATE ra_customer_merge_headers
1145 SET process_flag = l_new_process_flag
1146 WHERE request_id = l_request_id
1147 AND process_flag = l_process_flag
1148 AND priority = p_priority; --3897822
1149 END IF;
1150 ELSE
1151 UPDATE ra_customer_merge_headers
1152 SET process_flag = l_new_process_flag
1153 WHERE request_id IS NULL
1154 AND process_flag = l_process_flag
1155 AND priority = p_priority; --3897822
1156 END IF;
1157 END LOOP;
1158 CLOSE c_requests;
1159
1160 ELSIF p_process_flag = 'FAILED' THEN
1161 UPDATE ra_customer_merge_headers
1162 SET process_flag = l_new_process_flag
1163 WHERE process_flag LIKE 'ERROR%';
1164 END IF;
1165
1166 IF p_merge_rule = 'OLD' THEN
1167 UPDATE ra_customer_merge_headers
1168 SET process_flag = l_new_process_flag
1169 WHERE process_flag = p_process_flag;
1170 END IF;
1171
1172 IF p_process_flag<> 'PROCESSING' THEN
1173 UPDATE ra_customer_merge_headers mh
1174 SET request_id = req_id ,
1175 merge_fail_msg = NULL
1176 WHERE process_flag = l_new_process_flag
1177 AND customer_merge_header_id IN
1178 (SELECT customer_merge_header_id
1179 FROM
1180 (SELECT customer_merge_header_id
1181 FROM ra_customer_merge_headers mh1
1182 WHERE mh1.priority = p_priority
1183 AND mh1.process_flag = l_new_process_flag
1184 AND NOT EXISTS (
1185 SELECT 'Y' FROM ra_customer_merges m
1186 WHERE m.customer_merge_header_id = mh1.customer_merge_header_id
1187 AND mo_global.check_access(m.org_id) <> 'Y'
1188 AND ROWNUM =1
1189 )
1190
1191 AND ( EXISTS (
1192 SELECT 'Y'
1193 FROM ra_customer_merges m, hz_cust_acct_sites site
1194 WHERE m.customer_merge_header_id = mh1.customer_merge_header_id
1195 AND m.duplicate_address_id = site.cust_acct_site_id
1196 AND ROWNUM = 1)
1197 OR EXISTS(
1198 SELECT 'Y'
1199 FROM ra_customer_merges m
1200 WHERE m.customer_merge_header_id = mh1.customer_merge_header_id
1201 AND m.duplicate_address_id = -1)
1202 )
1203
1204 ORDER BY last_update_date
1205 )
1206 WHERE ROWNUM <= p_number_of_merges);
1207
1208 FORALL i IN 1..l_header_id_t.count
1209 UPDATE ra_customer_merge_headers mh
1210 SET process_flag = l_process_flag_t(i)
1211 WHERE request_id <> req_id
1212 AND mh.customer_merge_header_id = l_header_id_t(i);
1213 END IF;
1214
1215 --N/A --Bug 1519688: Set request_id for merge headers.
1216
1217 --Bug 1725662: Set request_id from ra_customer_merges. Rewrite sql to
1218 --use index.
1219
1220 UPDATE ra_customer_merge_headers mh
1221 SET process_flag = 'Y'
1222 WHERE request_id = req_id
1223 AND process_flag = l_new_process_flag
1224 AND priority = p_priority --3897822
1225 AND EXISTS (
1226 SELECT 'Y'
1227 FROM ra_customer_merges m
1228 WHERE m.customer_merge_header_id = mh.customer_merge_header_id
1229 AND m.process_flag = 'Y'
1230 AND ROWNUM = 1 );
1231
1232 UPDATE ra_customer_merges m
1233 SET (request_id,
1234 process_flag) = (
1235 SELECT mh.request_id, mh.process_flag
1236 FROM ra_customer_merge_headers mh
1237 WHERE mh.customer_merge_header_id = m.customer_merge_header_id
1238 AND mh.process_flag = l_new_process_flag )
1239 WHERE m.customer_merge_header_id IN (
1240 SELECT mh.customer_merge_header_id
1241 FROM ra_customer_merge_headers mh
1242 WHERE mh.process_flag = l_new_process_flag
1243 AND request_id = req_id
1244 AND mh.priority = p_priority); --3897822
1245
1246 arp_message.set_line( 'ARP_CMERGE_MASTER.mark_merge_rows()-' );
1247
1248 EXCEPTION
1249
1250 WHEN OTHERS THEN
1251 arp_message.set_error( 'ARP_CMERGE_MASTER.mark_merge_rows');
1252 RAISE;
1253
1254 END mark_merge_rows;
1255
1256 /*===========================================================================+
1257 | PROCEDURE
1258 | add_request
1259 |
1260 | DESCRIPTION
1261 | For handling terminated requests, add request to
1262 | the AR concurrent request table.
1263 |
1264 | SCOPE - PUBLIC
1265 |
1266 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1267 |
1268 | ARGUMENTS : IN:
1269 | req_id
1270 | program_name
1271 | OUT:
1272 | IN/ OUT:
1273 |
1274 | RETURNS : NONE
1275 |
1276 | NOTES
1277 |
1278 | MODIFICATION HISTORY
1279 |
1280 +===========================================================================*/
1281
1282 PROCEDURE add_request (
1283 req_id NUMBER,
1284 program_name VARCHAR2
1285 ) IS
1286
1287 BEGIN
1288
1289 arp_message.set_line( 'ARP_CMERGE_MASTER.add_request()+' );
1290
1291 INSERT into ar_conc_process_requests
1292 (request_id, concurrent_program_name)
1293 VALUES
1294 (req_id, program_name);
1295
1296 arp_message.set_line( 'ARP_CMERGE_MASTER.add_request()-' );
1297
1298 EXCEPTION
1299
1300 WHEN OTHERS THEN
1301 arp_message.set_error( 'ARP_CMERGE_MASTER.add_request' );
1302 RAISE;
1303
1304 END add_request;
1305
1306 /*===========================================================================+
1307 | PROCEDURE
1308 | validate_merges
1309 |
1310 | DESCRIPTION
1311 | Validate merge candidate.
1312 |
1313 | SCOPE - PUBLIC
1314 |
1315 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1316 |
1317 | ARGUMENTS : IN:
1318 | req_id
1319 | p_process_flag
1320 | IN/ OUT:
1321 |
1322 | RETURNS : NONE
1323 |
1324 | NOTES
1325 | Procedure to validate the RA_CUSTOMER_MERGE records prior to
1326 | using them as the source for the merge process
1327 |
1328 | MODIFICATION HISTORY
1329 | Jianying Huang 07-DEC-00 Bug 1391134: We modified createSites as
1330 | set-based procedure and call if before merge products
1331 | for each set. We also set not-null columns
1332 | customer_address_id and customer_site_id in table
1333 | ra_customer_merges to -1, so merge form can submit
1334 | the merges. To accommodate that changes, we donot
1335 | validate those records with customer_createsame = 'Y'
1336 | Jianying Huang 08-MAR-01 Bug 1610924: Modified the procedure to allow
1337 | merging all of the site uses.
1338 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
1339 | the new added indexes for performance improvements.
1340 | Ramesh Ch 21-Nov-03 Bug 3186432.Added duplicate_site_id <> -99
1341 | condition in check_invalid_merges cursor.
1342 | S V Sowjanya 10-AUG-04 Bug 3705423: Commented (duplicate_site_id <> -99 and
1343 | customer_site_id <> -99), duplicate_site_id <> -99 conditions
1344 | and added code to join address_id of the customers in
1345 | check_invalid_merges cursor.
1346 | S V Sowjanya 16-NOV-05 Bug 4693912: Modified cursor check_invalid_merges to
1347 | exclude records with duplicate_address_id '-1'
1348 +===========================================================================*/
1349
1350 PROCEDURE validate_merges (
1351 req_id NUMBER,
1352 p_process_flag VARCHAR2
1353 ) IS
1354
1355 --Select statements to select invalid merges
1356
1357 CURSOR val_all_sites_merged IS
1358 /** N/A
1359 If merging to a different customer,
1360 all SHIP_TO, BILL_TO and MARKET sites must be merged */
1361
1362 /** Bug 1610924
1363 If merging to a different customer,
1364 all of the site uses much be merged.
1365
1366 Select
1367 all sites that must be merged
1368 MINUS
1369 all sites specified in ra_customer_merges
1370 Migration to new customer model.
1371 -------------------------------
1372 With the new cust. model, cust acct and sites are already
1373 striped by ou. The cust accts are no longer global.
1374 Because the sites will not be referenced in other ou,
1375 the tables that will replace RA_ADDRESSES and RA_SITE_USES
1376 will be HZ_CUST_ACCT_SITES and HZ_CUST_SITE_USES.
1377 Columns will be changed correspondingly.
1378 */
1379
1380 --Bug 1725662: rewrite query to use index on
1381 --ra_customer_merge_headers.(request_id, process_flag);
1382
1383 SELECT su.site_use_id site_use_id,
1384 mh.duplicate_id duplicate_id
1385 FROM hz_cust_acct_sites addr,
1386 hz_cust_site_uses su,
1387 ra_customer_merge_headers mh
1388 WHERE mh.request_id = req_id
1389 AND mh.process_flag = p_process_flag
1390 AND (mh.org_id = -1 OR (mh.org_id <> -1 AND addr.org_id = mh.org_id)) --SSUptake
1391 AND mh.duplicate_id <> mh.customer_id
1392 AND su.cust_acct_site_id = addr.cust_acct_site_id
1393 AND addr.cust_account_id = mh.duplicate_id
1394 AND NOT EXISTS (
1395 SELECT 'same site in merge detail'
1396 FROM ra_customer_merges m
1397 WHERE m.customer_merge_header_id = mh.customer_merge_header_id
1398 AND m.duplicate_site_id = su.site_use_id
1399 AND m.org_id = su.org_id
1400 );
1401
1402 --Bug 1391134: ignore those records with customer_createsame = 'Y'
1403 --in ra_customer_merges table.
1404
1405 CURSOR check_invalid_merges IS
1406 /**
1407 Merge is INVALID if:
1408 - customer_site_id = duplicate_site_id of another row
1409 - duplicate_site_id = customer_site_id of another row
1410 - duplicate_site_id = duplicate_site_id of another row
1411 */
1412
1413 SELECT m.duplicate_id duplicate_id
1414 FROM ra_customer_merges m
1415 WHERE m.process_flag = p_process_flag
1416 AND m.request_id = req_id
1417 AND duplicate_address_id <> -1 --4693912
1418 -- AND (m.duplicate_site_id <> -99 AND m.customer_site_id <> -99)
1419 AND ((m.customer_createsame <> 'Y'
1420 AND (m.customer_site_id IN (
1421 SELECT m2.duplicate_site_id
1422 FROM ra_customer_merges m2
1423 WHERE m2.rowid <> m.rowid
1424 AND m2.process_flag = p_process_flag
1425 AND m2.duplicate_address_id = m.customer_address_id )
1426 OR m.duplicate_site_id IN (
1427 SELECT m2.customer_site_id
1428 FROM ra_customer_merges m2
1429 WHERE m2.rowid <> m.rowid
1430 AND m2.process_flag = p_process_flag
1431 AND m2.customer_address_id = m.duplicate_address_id )))
1432 OR m.duplicate_site_id IN (
1433 SELECT m2.duplicate_site_id
1434 FROM ra_customer_merges m2
1435 WHERE m2.rowid <> m.rowid
1436 AND m2.process_flag = p_process_flag
1437 --AND m2.duplicate_site_id <> -99
1438 AND m2.duplicate_address_id = m.duplicate_address_id));
1439
1440 missing_sites val_all_sites_merged%ROWTYPE;
1441 invalid_merges check_invalid_merges%ROWTYPE;
1442 error_msg VARCHAR2(240); -- Bug No: 3743818
1443 BEGIN
1444
1445 arp_message.set_line( 'ARP_CMERGE_MASTER.validate_merges()+' );
1446
1447 FOR missing_sites IN val_all_sites_merged LOOP
1448
1449 --Mark Invalid Merges
1450 UPDATE ra_customer_merges
1451 SET process_flag = 'ERROR 1'
1452 WHERE duplicate_id = missing_sites.duplicate_id
1453 AND request_id = req_id
1454 AND process_flag = p_process_flag;
1455
1456 --Bug 1725662: Add the following sql to save performance in
1457 --clear_error_merge_rows.
1458
1459 UPDATE ra_customer_merge_headers
1460 SET process_flag = 'ERROR 1'
1461 WHERE duplicate_id = missing_sites.duplicate_id
1462 AND request_id = req_id
1463 AND process_flag = p_process_flag;
1464 ---Start of Bug No : 3743818
1465 fnd_message.set_name('AR', 'AR_CMERGE_MISSING_SITES');
1466 error_msg := fnd_message.get();
1467 arp_message.set_line('Duplicate Id : '|| missing_sites.duplicate_id ||' -- '||error_msg);
1468 --End Of Bug No: 3743818
1469
1470 END LOOP;
1471
1472 FOR invalid_merges IN check_invalid_merges LOOP
1473
1474 --Mark Invalid Merges
1475 UPDATE ra_customer_merges
1476 SET process_flag = 'ERROR 2'
1477 WHERE duplicate_id = invalid_merges.duplicate_id
1478 AND request_id = req_id
1479 AND process_flag = p_process_flag;
1480
1481 --Bug 1725662: Add the following sql to save performance in
1482 --clear_error_merge_rows.
1483
1484 UPDATE ra_customer_merge_headers
1485 SET process_flag = 'ERROR 2'
1486 WHERE duplicate_id = invalid_merges.duplicate_id
1487 AND request_id = req_id
1488 AND process_flag = p_process_flag;
1489
1490 ---Start of Bug No : 3743818
1491 fnd_message.set_name('AR', 'AR_CMERGE_DUPLICATE_SITE');
1492 error_msg := fnd_message.get();
1493 arp_message.set_line( ' Duplicate Id : '||invalid_merges.duplicate_id ||' -- '||error_msg);
1494 --End Of Bug No: 3743818
1495
1496 END LOOP;
1497
1498 arp_message.set_line( 'ARP_CMERGE_MASTER.validate_merges()-' );
1499
1500 EXCEPTION
1501
1502 WHEN OTHERS THEN
1503 arp_message.set_error( 'ARP_CMERGE_MASTER.validate_merges' );
1504 RAISE;
1505
1506 END validate_merges;
1507
1508 /*===========================================================================+
1509 | PROCEDURE
1510 | partiton_merge_data
1511 |
1512 | DESCRIPTION
1513 | Partition merge sets
1514 |
1515 | SCOPE - PUBLIC
1516 |
1517 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1518 |
1519 | ARGUMENTS : IN:
1520 | req_id
1521 | p_process_flag
1522 | OUT:
1523 | last_set
1524 | IN/ OUT:
1525 |
1526 | RETURNS : NONE
1527 |
1528 | NOTES
1529 |
1530 | MODIFICATION HISTORY
1531 |
1532 +===========================================================================*/
1533
1534 PROCEDURE partiton_merge_data(
1535 req_id NUMBER,
1536 last_set OUT NOCOPY NUMBER,
1537 p_process_flag VARCHAR2
1538 ) IS
1539
1540 CURSOR partition is
1541 SELECT rowid, duplicate_id
1542 FROM ra_customer_merges
1543 WHERE request_id = req_id
1544 AND process_flag = p_process_flag
1545 ORDER BY duplicate_id;
1546
1547 v_rowid VARCHAR2(24);
1548 v_prev_customer_id NUMBER := null;
1549 v_customer_id NUMBER := null;
1550 v_count NUMBER := 0;
1551 v_set_size NUMBER;
1552 v_last_set NUMBER := 1;
1553
1554 BEGIN
1555
1556 arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()+' );
1557
1558 v_set_size := fnd_profile.value('AR_CMERGE_SET_SIZE');
1559
1560 OPEN partition;
1561
1562 LOOP
1563 FETCH partition into v_rowid, v_customer_id;
1564 EXIT when partition%notfound;
1565
1566 --always group by duplicate_id, even if set limit has been
1567 --exceeded
1568
1569 --check if first or new customer
1570 IF ( v_prev_customer_id is null or
1571 v_prev_customer_id <> v_customer_id ) then
1572
1573 --check if set limit has been reached
1574 IF ( v_count >= v_set_size ) then
1575 v_last_set := v_last_set + 1;
1576 v_count := 0;
1577 END IF;
1578
1579 v_prev_customer_id := v_customer_id;
1580
1581 END IF;
1582
1583 v_count := v_count + 1;
1584
1585 UPDATE ra_customer_merges
1586 SET set_number = v_last_set
1587 WHERE rowid = v_rowid;
1588
1589 END loop;
1590
1591 CLOSE partition;
1592
1593 last_set := v_last_set;
1594
1595
1596 arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()-' );
1597
1598 EXCEPTION
1599
1600 WHEN OTHERS THEN
1601 arp_message.set_error( 'ARP_CMERGE_MASTER.partition_merge_date' );
1602 RAISE;
1603
1604 END partiton_merge_data;
1605
1606 /*===========================================================================+
1607 | PROCEDURE
1608 | unpartiton_merge_data
1609 |
1610 | DESCRIPTION
1611 | Partition merge sets to a set size of 1 inorder to
1612 | identify the bad merge records.
1613 |
1614 | SCOPE - PUBLIC
1615 |
1616 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1617 |
1618 | ARGUMENTS : IN:
1619 | req_id
1620 | set_num
1621 | OUT:
1622 | last_set
1623 | IN/ OUT:
1624 |
1625 | RETURNS : NONE
1626 |
1627 | NOTES
1628 |
1629 | MODIFICATION HISTORY
1630 | Rajeshwari 04-02-2003 Bug 2669389 Created.
1631 | Rajeshwari P 04-07-2003 Modified code to set
1632 | the set_number starting
1633 | from 1001 to avoid conflict.
1634 +===========================================================================*/
1635
1636 PROCEDURE unpartiton_merge_data(
1637 req_id NUMBER,
1638 last_set OUT NOCOPY NUMBER,
1639 set_num NUMBER
1640 ) IS
1641
1642 CURSOR partition is
1643 SELECT rowid, duplicate_id
1644 FROM ra_customer_merges
1645 WHERE request_id = req_id
1646 AND set_number = set_num
1647 ORDER BY duplicate_id;
1648
1649 v_rowid VARCHAR2(24);
1650 v_prev_customer_id NUMBER := null;
1651 v_customer_id NUMBER := null;
1652 v_count NUMBER := 1000;
1653 v_set_size NUMBER;
1654 v_last_set NUMBER := 1001;
1655 l_count number;
1656
1657 BEGIN
1658
1659 arp_message.set_line( 'ARP_CMERGE_MASTER.unpartiton_merge_data()+' );
1660
1661 v_set_size := 1001;
1662
1663 OPEN partition;
1664
1665 LOOP
1666 FETCH partition into v_rowid, v_customer_id;
1667 EXIT when partition%notfound;
1668
1669 --always group by duplicate_id, even if set limit has been
1670 --exceeded
1671
1672 --check if first or new customer
1673 IF ( v_prev_customer_id is null or
1674 v_prev_customer_id <> v_customer_id ) then
1675
1676 --check if set limit has been reached
1677 IF ( v_count >= v_set_size ) then
1678 v_last_set := v_last_set + 1;
1679 v_count := 1000;
1680 END IF;
1681
1682 v_prev_customer_id := v_customer_id;
1683
1684 END IF;
1685
1686 v_count := v_count + 1;
1687
1688 UPDATE ra_customer_merges
1689 SET set_number = v_last_set
1690 WHERE request_id = req_id
1691 AND rowid = v_rowid;
1692
1693 begin
1694 select set_number into l_count
1695 from ra_customer_merges
1696 where request_id = req_id
1697 AND set_number = v_last_set
1698 AND rowid = v_rowid;
1699 exception
1700 when no_data_found then
1701 NULL;
1702 end;
1703
1704 END loop;
1705
1706 CLOSE partition;
1707
1708 last_set := v_last_set;
1709
1710
1711 arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()-' );
1712
1713 EXCEPTION
1714
1715 WHEN OTHERS THEN
1716 arp_message.set_error( 'ARP_CMERGE_MASTER.partition_merge_date' );
1717 RAISE;
1718
1719 END unpartiton_merge_data;
1720 /*===========================================================================+
1721 | PROCEDURE
1722 | done_merge_rows
1723 |
1724 | DESCRIPTION
1725 | Sets process flag to 'Y' in the header and merges table
1726 | for successful merges
1727 |
1728 | SCOPE - PUBLIC
1729 |
1730 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1731 |
1732 | ARGUMENTS : IN:
1733 | req_id
1734 | set_num
1735 | OUT:
1736 | IN/ OUT:
1737 |
1738 | RETURNS : NONE
1739 |
1740 | NOTES
1741 |
1742 | MODIFICATION HISTORY
1743 | Jianying Huang 29-NOV-00 Bug 1519688: Modified procedure
1744 | based on the change of merge form:
1745 | 'display request id instantly after hit merge button'
1746 | Jianying Huang 21-DEC-00 Call read_temporary_table for debug purpose.
1747 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
1748 | the new added indexes for performance improvements.
1749 |
1750 +===========================================================================*/
1751
1752 PROCEDURE done_merge_rows (
1753 req_id NUMBER,
1754 set_num NUMBER
1755 ) IS
1756
1757 BEGIN
1758
1759 arp_message.set_line( 'ARP_CMERGE_MASTER.done_merge_rows()+' );
1760
1761 --delete customer alternative names
1762 arp_cmerge_master.delete_customer_alt_names ( req_id , set_num ) ;
1763
1764 UPDATE ra_customer_merges
1765 SET process_flag = 'Y',
1766 last_update_date = sysdate,
1767 last_updated_by = hz_utility_v2pub.user_id,
1768 last_update_login = hz_utility_v2pub.last_update_login,
1769 program_application_id = hz_utility_v2pub.program_application_id,
1770 program_id = hz_utility_v2pub.program_id,
1771 program_update_date = sysdate
1772 WHERE request_id = req_id
1773 AND set_number = set_num
1774 AND process_flag = 'N';
1775
1776 --Bug 1519688: Do not need to set request_id.
1777 --Bug 1725662: replace 'EXISTS' with 'IN' to use index.
1778
1779 UPDATE ra_customer_merge_headers mh
1780 SET (process_flag,
1781 -- request_id,
1782 last_update_date,
1783 last_updated_by,
1784 last_update_login,
1785 program_application_id,
1786 program_id,
1787 program_update_date) = (
1788 SELECT
1789 m.process_flag,
1790 -- m.request_id,
1791 sysdate,
1792 m.last_updated_by,
1793 m.last_update_login,
1794 m.program_application_id,
1795 m.program_id,
1796 sysdate
1797 FROM ra_customer_merges m
1798 WHERE m.request_id = req_id
1799 AND m.set_number = set_num
1800 AND m.process_flag = 'Y'
1801 AND mh.customer_merge_header_id =
1802 m.customer_merge_header_id
1803 AND ROWNUM = 1)
1804 WHERE mh.customer_merge_header_id IN (
1805 SELECT m.customer_merge_header_id
1806 FROM ra_customer_merges m
1807 WHERE m.request_id = req_id
1808 AND m.process_flag = 'Y'
1809 AND m.set_number = set_num );
1810
1811 --Added for debug purpose, should be always commented out.
1812 --read_temporary_table;
1813
1814 arp_message.set_line( 'ARP_CMERGE_MASTER.done_merge_rows()-' );
1815
1816 EXCEPTION
1817
1818 WHEN OTHERS THEN
1819 arp_message.set_error( 'ARP_CMERGE_MASTER.done_merge_rows' );
1820 RAISE;
1821
1822 END done_merge_rows;
1823
1824 /*===========================================================================+
1825 | PROCEDURE
1826 | reset_merge_rows
1827 |
1828 | DESCRIPTION
1829 | Reset rows for reprocessing
1830 |
1831 | SCOPE - PUBLIC
1832 |
1833 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1834 |
1835 | ARGUMENTS : IN:
1836 | req_id
1837 | set_num
1838 | OUT:
1839 | IN/ OUT:
1840 |
1841 | RETURNS : NONE
1842 |
1843 | NOTES
1844 |
1845 | MODIFICATION HISTORY
1846 | Jianying Huang 29-NOV-00 Bug 1519688: Modified procedure
1847 | based on the change of merge form:
1848 | 'display request id instantly after hit merge button'
1849 | Jianying Huang 07-DEC-00 Reset merge header table.
1850 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
1851 | the new added indexes for performance improvements.
1852 | Jianying Huang 07-JUN-01 Should not update set_number to NULL when
1853 | update ra_customer_merges. Commented the statement
1854 | out.
1855 |
1856 +===========================================================================*/
1857
1858 PROCEDURE reset_merge_rows (
1859 req_id NUMBER,
1860 set_num NUMBER,
1861 p_process_flag VARCHAR2
1862 ) IS
1863
1864 BEGIN
1865
1866 --arp_message.set_line( 'ARP_CMERGE_MASTER.reset_merge_rows()+' );
1867
1868 --If its database problem, set back status to 'SAVED'
1869 --Bug fix 2669389
1870 if (p_process_flag = 'SAVED') then
1871
1872 arp_message.set_line('in resetmerge flag is saved');
1873 UPDATE ra_customer_merges
1874 SET
1875 -- set_number = null,
1876 -- request_id = null,
1877 process_flag = 'SAVED',
1878 last_update_date = sysdate,
1879 last_updated_by = hz_utility_v2pub.user_id,
1880 last_update_login = hz_utility_v2pub.last_update_login,
1881 program_application_id = hz_utility_v2pub.program_application_id,
1882 program_id = hz_utility_v2pub.program_id,
1883 program_update_date = sysdate
1884 WHERE request_id = req_id
1885 AND set_number = set_num
1886 AND process_flag = p_process_flag;
1887
1888
1889 UPDATE ra_customer_merge_headers mh
1890 SET (process_flag,
1891 last_update_date,
1892 last_updated_by,
1893 last_update_login,
1894 program_application_id,
1895 program_id,
1896 program_update_date) = (
1897 SELECT
1898 m.process_flag,
1899 sysdate,
1900 m.last_updated_by,
1901 m.last_update_login,
1902 m.program_application_id,
1903 m.program_id,
1904 sysdate
1905 FROM ra_customer_merges m
1906 WHERE m.request_id = req_id
1907 AND m.set_number = set_num
1908 AND m.process_flag = 'SAVED'
1909 AND mh.customer_merge_header_id =
1910 m.customer_merge_header_id
1911 AND ROWNUM = 1)
1912 WHERE mh.customer_merge_header_id IN (
1913 SELECT m.customer_merge_header_id
1914 FROM ra_customer_merges m
1915 WHERE m.request_id = req_id
1916 AND m.process_flag = 'SAVED'
1917 AND m.set_number = set_num );
1918 --Bug Fix 2669389
1919 --If business validation failure set process flag to 'FAILED'
1920
1921 else
1922 --Bug 1519688: should not reset request_id to NULL.
1923 --Set process_flag = 'FAILED' indicate this is a failed merge, we need
1924 --to pick it up next time.
1925
1926 UPDATE ra_customer_merges
1927 SET
1928 -- set_number = null,
1929 -- request_id = null,
1930 process_flag = 'FAILED',
1931 last_update_date = sysdate,
1932 last_updated_by = hz_utility_v2pub.user_id,
1933 last_update_login = hz_utility_v2pub.last_update_login,
1934 program_application_id = hz_utility_v2pub.program_application_id,
1935 program_id = hz_utility_v2pub.program_id,
1936 program_update_date = sysdate
1937 WHERE request_id = req_id
1938 AND set_number = set_num
1939 AND process_flag = p_process_flag;
1940
1941 --reset merge header table.
1942
1943 --Bug 1725662: replace 'EXISTS' with 'IN' to use index.
1944
1945 UPDATE ra_customer_merge_headers mh
1946 SET (process_flag,
1947 last_update_date,
1948 last_updated_by,
1949 last_update_login,
1950 program_application_id,
1951 program_id,
1952 program_update_date) = (
1953 SELECT
1954 m.process_flag,
1955 sysdate,
1956 m.last_updated_by,
1957 m.last_update_login,
1958 m.program_application_id,
1959 m.program_id,
1960 sysdate
1961 FROM ra_customer_merges m
1962 WHERE m.request_id = req_id
1963 AND m.set_number = set_num
1964 AND m.process_flag = 'FAILED'
1965 AND mh.customer_merge_header_id =
1966 m.customer_merge_header_id
1967 AND ROWNUM = 1)
1968 WHERE mh.customer_merge_header_id IN (
1969 SELECT m.customer_merge_header_id
1970 FROM ra_customer_merges m
1971 WHERE m.request_id = req_id
1972 AND m.process_flag = 'FAILED'
1973 AND m.set_number = set_num );
1974
1975 end if;
1976
1977 --arp_message.set_line( 'ARP_CMERGE_MASTER.reset_merge_rows()-' );
1978
1979 EXCEPTION
1980
1981 WHEN OTHERS THEN
1982 --arp_message.set_error( 'ARP_CMERGE_MASTER.reset_merge_rows' );
1983 RAISE;
1984
1985 END reset_merge_rows;
1986
1987 /*===========================================================================+
1988 | PROCEDURE
1989 | clear_error_merge_rows
1990 |
1991 | DESCRIPTION
1992 | Remove error status from records that failed validation
1993 | so that they maybe re-submitted
1994 |
1995 | SCOPE - PUBLIC
1996 |
1997 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1998 |
1999 | ARGUMENTS : IN:
2000 | req_id
2001 | OUT:
2002 | IN/ OUT:
2003 |
2004 | RETURNS : NONE
2005 |
2006 | NOTES
2007 |
2008 | MODIFICATION HISTORY
2009 | Jianying Huang 29-NOV-00 Bug 1519688: Modified procedure
2010 | based on the change of merge form:
2011 | 'display request id instantly after hit merge button'
2012 | Jianying Huang 07-DEC-00 Reset merge header table.
2013 | Jianying Huang 07-APR-01 Bug 1725662: Rewrite some queries based on
2014 | the new added indexes for performance improvements.
2015 |
2016 +===========================================================================*/
2017
2018 PROCEDURE clear_error_merge_rows (
2019 req_id NUMBER
2020 ) IS
2021
2022 BEGIN
2023
2024 --arp_message.set_line( 'ARP_CMERGE_MASTER.clear_error_merge_rows()+' );
2025
2026 --Bug 1518688: should not reset request_id to NULL.
2027 --Set process_flag = 'FAILED' indicate this is a failed merge, we need
2028 --to pick it up next time.
2029
2030 --Bug 1725662: rewrite query to use new index.
2031
2032 UPDATE ra_customer_merge_headers
2033 SET process_flag = decode(process_flag,
2034 'ERROR 1', 'FAILED',
2035 'ERROR 2', 'FAILED'),
2036 -- request_id = null,
2037 last_update_date = sysdate,
2038 last_updated_by = hz_utility_v2pub.user_id,
2039 last_update_login = hz_utility_v2pub.last_update_login,
2040 program_application_id = hz_utility_v2pub.program_application_id,
2041 program_id = hz_utility_v2pub.program_id,
2042 program_update_date = sysdate
2043 WHERE process_flag in ('ERROR 1', 'ERROR 2')
2044 AND request_id = req_id;
2045
2046 UPDATE ra_customer_merges m
2047 SET (process_flag,
2048 last_update_date,
2049 last_updated_by,
2050 last_update_login,
2051 program_application_id,
2052 program_id,
2053 program_update_date) = (
2054 SELECT
2055 mh.process_flag,
2056 sysdate,
2057 mh.last_updated_by,
2058 mh.last_update_login,
2059 mh.program_application_id,
2060 mh.program_id,
2061 sysdate
2062 FROM ra_customer_merge_headers mh
2063 WHERE mh.request_id = req_id
2064 AND mh.process_flag = 'FAILED'
2065 AND mh.customer_merge_header_id =
2066 m.customer_merge_header_id
2067 AND ROWNUM = 1)
2068 WHERE m.customer_merge_header_id IN (
2069 SELECT mh.customer_merge_header_id
2070 FROM ra_customer_merge_headers mh
2071 WHERE mh.request_id = req_id
2072 AND mh.process_flag = 'FAILED' )
2073 AND process_flag in ('ERROR 1', 'ERROR 2');
2074
2075 --arp_message.set_line( 'ARP_CMERGE_MASTER.clear_error_merge_rows()-' );
2076
2077 EXCEPTION
2078
2079 WHEN OTHERS THEN
2080 --arp_message.set_error( 'ARP_CMERGE_MASTER.clear_error_merge_rows' );
2081 RAISE;
2082
2083 END clear_error_merge_rows;
2084
2085 /*===========================================================================+
2086 | PROCEDURE
2087 | remove_request
2088 |
2089 | DESCRIPTION
2090 | For handling terminated requests, remove request to the AR
2091 | concurrent request table
2092 |
2093 | SCOPE - PUBLIC
2094 |
2095 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2096 |
2097 | ARGUMENTS : IN:
2098 | req_id
2099 | OUT:
2100 | IN/ OUT:
2101 |
2102 | RETURNS : NONE
2103 |
2104 | NOTES
2105 |
2106 | MODIFICATION HISTORY
2107 |
2108 +===========================================================================*/
2109
2110 PROCEDURE remove_request (
2111 req_id NUMBER
2112 ) IS
2113
2114 BEGIN
2115
2116 --arp_message.set_line( 'ARP_CMERGE_MASTER.remove_request()+' );
2117
2118 DELETE FROM ar_conc_process_requests
2119 WHERE request_id = req_id;
2120
2121 --arp_message.set_line( 'ARP_CMERGE_MASTER.remove_request()-' );
2122
2123 EXCEPTION
2124
2125 WHEN OTHERS THEN
2126 --arp_message.set_error( 'ARP_CMERGE_MASTER.remove_request' );
2127 RAISE;
2128
2129 END remove_request;
2130
2131 /*===========================================================================+
2132 | PROCEDURE
2133 | delete_customer_alt_names
2134 |
2135 | DESCRIPTION
2136 | Deletes customer alternative names.
2137 |
2138 | SCOPE - PRIVATE
2139 |
2140 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2141 |
2142 | ARGUMENTS : IN:
2143 | req_id
2144 | set_num
2145 | IN/ OUT:
2146 |
2147 | RETURNS : NONE
2148 |
2149 | NOTES
2150 |
2151 | MODIFICATION HISTORY
2152 |
2153 +===========================================================================*/
2154
2155 PROCEDURE delete_customer_alt_names(
2156 req_id NUMBER,
2157 set_num NUMBER
2158 ) IS
2159
2160 v_prev_duplicate_id NUMBER := null ;
2161 v_duplicate_id NUMBER;
2162 v_customer_id NUMBER;
2163 v_duplicate_site_id NUMBER;
2164 v_lock_status NUMBER;
2165
2166 CURSOR alt_names_deletion IS
2167 SELECT duplicate_id , customer_id , duplicate_site_id
2168 FROM ra_customer_merges
2169 WHERE request_id = req_id
2170 AND set_number = set_num
2171 AND process_flag = 'N'
2172 ORDER BY duplicate_id ;
2173
2174 BEGIN
2175
2176 arp_message.set_line( 'ARP_CMERGE_MASTER.alt_name_deletion()+' );
2177
2178 -- Bug 2092530: Removed condition that checked the now-obsolete
2179 -- 'AR_ALT_NAME_SEARCH' profile option before executing the delete.
2180
2181 OPEN alt_names_deletion;
2182 LOOP
2183 FETCH alt_names_deletion INTO v_duplicate_id, v_customer_id,
2184 v_duplicate_site_id;
2185 EXIT WHEN alt_names_deletion%notfound;
2186
2187 IF ( v_duplicate_id <> v_customer_id ) THEN
2188 IF ( ( v_prev_duplicate_id IS NULL ) OR
2189 ( v_prev_duplicate_id <> v_duplicate_id ) )
2190 THEN
2191 arp_cust_alt_match_pkg.lock_match (
2192 v_duplicate_id, NULL, v_lock_status );
2193
2194 IF ( v_lock_status = 1 ) THEN
2195 arp_cust_alt_match_pkg.delete_match ( v_duplicate_id, NULL, NULL );
2196 END IF;
2197
2198 v_prev_duplicate_id := v_duplicate_id ;
2199
2200 END IF ;
2201 ELSE
2202 arp_cust_alt_match_pkg.lock_match (
2203 v_duplicate_id, v_duplicate_site_id, v_lock_status );
2204
2205 IF ( v_lock_status = 1 ) THEN
2206 arp_cust_alt_match_pkg.delete_match (
2207 v_duplicate_id, v_duplicate_site_id, NULL );
2208 END IF;
2209
2210 END IF;
2211
2212 END LOOP;
2213 CLOSE alt_names_deletion;
2214
2215 arp_message.set_line( 'ARP_CMERGE_MASTER.alt_name_deletion()-' );
2216
2217 EXCEPTION
2218
2219 WHEN OTHERS THEN
2220 arp_message.set_error( 'ARP_CMERGE_MASTER.delete_customer_alt_names' );
2221 RAISE;
2222
2223 END delete_customer_alt_names;
2224
2225 /*===========================================================================+
2226 | PROCEDURE
2227 | read_temporary_table
2228 |
2229 | DESCRIPTION
2230 | Read mapping info. from temporary table. Created for debug purpose.
2231 |
2232 | SCOPE - PUBLIC
2233 |
2234 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2235 |
2236 | ARGUMENTS : IN:
2237 | OUT:
2238 | IN/ OUT:
2239 |
2240 | RETURNS : NONE
2241 |
2242 | NOTES
2243 |
2244 | MODIFICATION HISTORY
2245 | Jianying Huang 21-DEC-00 Created for debug purpose.
2246 |
2247 +===========================================================================*/
2248
2249 PROCEDURE read_temporary_table
2250 IS
2251
2252 l_sql VARCHAR2(1000);
2253
2254 BEGIN
2255
2256 arp_message.set_line('read temporary table');
2257
2258 l_sql :=
2259 'DECLARE ' ||
2260 'CURSOR c IS ' ||
2261 'SELECT type, old_id, new_id ' ||
2262 'FROM ' || arp_cmerge_arcus.g_table_name || ';' ||
2263 'l_old_id NUMBER; ' ||
2264 'l_new_id NUMBER; ' ||
2265 'l_type VARCHAR2(30); ' ||
2266 'BEGIN ' ||
2267 'arp_message.set_line( ''ORG CONTACT MAPPING : '' );' ||
2268 'OPEN c; ' ||
2269 'LOOP ' ||
2270 'FETCH c INTO l_type, l_old_id, l_new_id; ' ||
2271 'EXIT WHEN c%NOTFOUND; ' ||
2272 'arp_message.set_line( ' ||
2273 '''type = '' || l_type || '', '' || ' ||
2274 '''old = '' || to_char(l_old_id) || '', '' || ' ||
2275 '''new = '' || to_char(l_new_id) );' ||
2276 'END LOOP;' ||
2277 'CLOSE c; ' ||
2278 'END; ';
2279
2280 EXECUTE IMMEDIATE l_sql;
2281
2282 END;
2283 /*===========================================================================+
2284 | PROCEDURE
2285 | veto_delete
2286 |
2287 | DESCRIPTION
2288 | For preventing the delete of accounts and other records off accounts
2289 |
2290 |
2291 | SCOPE - PUBLIC
2292 |
2293 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2294 |
2295 | ARGUMENTS : IN: req_id NUMBER , set_num NUMBER, from_customer_id NUMBER
2296 | veto_reason VARCHAR2
2297 |
2298 | OUT:
2299 | IN/ OUT:
2300 |
2301 | RETURNS : NONE
2302 |
2303 | NOTES
2304 |
2305 | MODIFICATION HISTORY -
2306 | Jyoti Pandey 02-10-2002 Created.
2307 |
2308 +===========================================================================*/
2309 PROCEDURE veto_delete(req_id NUMBER,
2310 set_num NUMBER,
2311 from_customer_id NUMBER ,
2312 veto_reason VARCHAR2,
2313 part_delete VARCHAR2 DEFAULT 'N') IS
2314
2315 BEGIN
2316
2317 arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()+' );
2318
2319 /*--Unset the delete_duplicate_flag in ra_customer_merges --*/
2320 UPDATE ra_customer_merges m
2321 SET delete_duplicate_flag = 'N'
2322 WHERE m.duplicate_id = from_customer_id
2323 AND m.process_flag = 'N'
2324 AND m.request_id = req_id
2325 AND m.set_number = set_num
2326 AND part_delete = 'N'; --5747129
2327
2328 /*--Also unset the delete duplicate flag in merge header table --*/
2329 UPDATE ra_customer_merge_headers
2330 SET delete_duplicate_flag = 'N'
2331 WHERE customer_merge_header_id in
2332 (select customer_merge_header_id
2333 from ra_customer_merges m
2334 where m.duplicate_id = from_customer_id
2335 AND m.process_flag = 'N'
2336 AND m.request_id = req_id
2337 AND m.set_number = set_num)
2338 AND part_delete = 'N'; --5747129
2339
2340 /*--Unset the status to 'I'for HZ_CUST_SITE_USES which was set to 'D'--*/
2341 --undo
2342 UPDATE HZ_CUST_SITE_USES_ALL su --SSUptake
2343 SET status = 'I',
2344 last_update_date = sysdate,
2345 last_updated_by = hz_utility_v2pub.user_id,
2346 last_update_login = hz_utility_v2pub.last_update_login,
2347 request_id = req_id,
2348 program_application_id = hz_utility_v2pub.program_application_id,
2349 program_id = hz_utility_v2pub.program_id,
2350 program_update_date = sysdate
2351 WHERE EXISTS
2352 ( select 'Y'
2353 from hz_cust_acct_sites_ALL site,ra_customer_merges m --SSUptake
2354 where site.cust_account_id = from_customer_id
2355 and m.duplicate_address_id = site.cust_acct_site_id
2356 and su.cust_acct_site_id = site.cust_acct_site_id
2357 and m.request_id = req_id
2358 and m.process_flag = 'N'
2359 and m.set_number = set_num
2360 and m.duplicate_id = from_customer_id
2361 and m.org_id = site.org_id --SSUptake
2362 and su.org_id = site.org_id --SSUptake
2363 )
2364 AND status = 'D'
2365 AND part_delete = 'N'; --5747129
2366
2367 arp_message.set_line(SQL%ROWCOUNT||' '||'Row(s) updated in HZ_CUST_SITE_USES');
2368
2369
2370 /*--Unset the status to 'I'for HZ_CUST_ACCT_SITES which was set to 'D' --*/
2371 UPDATE HZ_CUST_ACCT_SITES_ALL addr --SSUptake
2372 set status = 'I',
2373 last_update_date = sysdate,
2374 last_updated_by = hz_utility_v2pub.user_id,
2375 last_update_login = hz_utility_v2pub.last_update_login,
2376 request_id = req_id,
2377 program_application_id = hz_utility_v2pub.program_application_id,
2378 program_id = hz_utility_v2pub.program_id,
2379 program_update_date = sysdate
2380 where addr.cust_account_id = from_customer_id
2381 AND EXISTS (select 'Y' from ra_customer_merges m
2382 where m.request_id = req_id
2383 and m.process_flag = 'N'
2384 and m.set_number = set_num
2385 and m.duplicate_id = from_customer_id
2386 and m.duplicate_address_id = addr.cust_acct_site_id
2387 and m.org_id = addr.org_id --SSUptake
2388 )
2389 AND addr.status = 'D'
2390 AND part_delete = 'N'; --5747129
2391
2392 arp_message.set_line(SQL%ROWCOUNT||' '||'Row(s) updated in HZ_CUST_ACCT_SITES');
2393
2394 /*--Unset the status to 'I' for HZ_CUST_ACCOUNTS which was set to 'D'*/
2395 UPDATE HZ_CUST_ACCOUNTS acct
2396 set status = decode(part_delete,'N','I','A'),
2397 last_update_date = sysdate,
2398 last_updated_by = hz_utility_v2pub.user_id,
2399 last_update_login = hz_utility_v2pub.last_update_login,
2400 request_id = req_id,
2401 program_application_id = hz_utility_v2pub.program_application_id,
2402 program_id = hz_utility_v2pub.program_id,
2403 program_update_date = sysdate
2404 where acct.cust_account_id = from_customer_id
2405 and status ='D';
2406
2407 arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUST_ACCOUNTS');
2408
2409 /*--Unset the status to 'I' for HZ_CUST_ACCT_RELATE cust_account_id */
2410 UPDATE HZ_CUST_ACCT_RELATE_ALL rel
2411 SET status = 'I',
2412 last_update_date = sysdate,
2413 last_updated_by = hz_utility_v2pub.user_id,
2414 last_update_login = hz_utility_v2pub.last_update_login,
2415 request_id = req_id,
2416 program_application_id =hz_utility_v2pub.program_application_id,
2417 program_id = hz_utility_v2pub.program_id,
2418 program_update_date = sysdate
2419 WHERE rel.cust_account_id = from_customer_id
2420 AND EXISTS (select 'Y' from ra_customer_merges m
2421 where m.request_id = req_id
2422 and m.process_flag = 'N'
2423 and m.set_number = set_num
2424 and m.duplicate_id = rel.cust_account_id
2425 and m.org_id = rel.org_id --SSUptake
2426 )
2427 AND status ='D'
2428 AND part_delete = 'N'; --5747129
2429
2430 arp_message.set_line( SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUST_ACCT_RELATE for cust_account_id' );
2431
2432 /*--Unset the status to 'I' for HZ_CUST_ACCT_RELATE
2433 --related_cust_account_id*/
2434 UPDATE HZ_CUST_ACCT_RELATE_ALL rel2 --SSUptake
2435 SET status = 'I',
2436 last_update_date = sysdate,
2437 last_updated_by = hz_utility_v2pub.user_id,
2438 last_update_login = hz_utility_v2pub.last_update_login,
2439 request_id = req_id,
2440 program_application_id =hz_utility_v2pub.program_application_id,
2441 program_id = hz_utility_v2pub.program_id,
2442 program_update_date = sysdate
2443 WHERE related_cust_account_id = from_customer_id
2444 AND EXISTS (select 'Y' from ra_customer_merges m
2445 where m.request_id = req_id
2446 and m.process_flag = 'N'
2447 and m.set_number = set_num
2448 and m.duplicate_id = rel2.related_cust_account_id
2449 and m.org_id = rel2.org_id --SSUptake
2450 )
2451 AND status ='D'
2452 AND part_delete = 'N'; --5747129
2453
2454 arp_message.set_line( SQL%ROWCOUNT||' '|| 'Rows updated in HZ_CUST_ACCT_RELATE for related_cust_account_id' );
2455
2456 /*--unset the end_date of RA_CUST_RECEIPT_METHODS at account level
2457 *--not required at site level as all the site uses get merged for an acct
2458 *--delete is prevented by delete_duplicate_flag='N' */
2459
2460 UPDATE RA_CUST_RECEIPT_METHODS yt
2461 set end_date = null,
2462 last_update_date = sysdate,
2463 last_updated_by = hz_utility_v2pub.user_id,
2464 last_update_login = hz_utility_v2pub.last_update_login,
2465 request_id = req_id,
2466 program_application_id = hz_utility_v2pub.program_application_id,
2467 program_id = hz_utility_v2pub.program_id,
2468 program_update_date = sysdate
2469 WHERE customer_id = from_customer_id
2470 AND customer_id IN (
2471 SELECT m.duplicate_id
2472 FROM ra_customer_merges m
2473 WHERE m.process_flag = 'N'
2474 AND m.request_id = req_id
2475 AND m.set_number = set_num
2476 AND m.delete_duplicate_flag = 'N' )
2477 AND site_use_id IS NULL
2478 AND NOT EXISTS (
2479 SELECT 'active accounts exist'
2480 FROM hz_cust_accounts acct
2481 WHERE acct.cust_account_id = yt.customer_id
2482 AND acct.status = 'A' );
2483
2484 arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in RA_CUST_RECEIPT_METHODS');
2485
2486 /* ---Unset status to Inactive for HZ_CUSTOMER_PROFILES for both
2487 * ---acct and site level */
2488 UPDATE hz_customer_profiles yt
2489 SET status = 'I',
2490 last_update_date = sysdate,
2491 last_updated_by = hz_utility_v2pub.user_id,
2492 last_update_login = hz_utility_v2pub.last_update_login,
2493 request_id = req_id,
2494 program_application_id = hz_utility_v2pub.program_application_id,
2495 program_id = hz_utility_v2pub.program_id,
2496 program_update_date = sysdate
2497 WHERE yt.cust_account_id = from_customer_id
2498 AND status = 'D'
2499 AND part_delete = 'N'; --5747129
2500
2501 UPDATE hz_customer_profiles yt --5634398
2502 SET status = 'A',
2503 last_update_date = sysdate,
2504 last_updated_by = arp_standard.profile.user_id,
2505 last_update_login = arp_standard.profile.last_update_login,
2506 request_id = req_id,
2507 program_application_id = arp_standard.profile.program_application_id,
2508 program_id = arp_standard.profile.program_id,
2509 program_update_date = sysdate
2510 WHERE part_delete = 'Y'
2511 AND status = 'D'
2512 AND yt.cust_account_id = from_customer_id
2513 AND site_use_id is NULL ;
2514
2515
2516 arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUSTOMER_PROFILES');
2517
2518
2519 /* ---Profile amts in ar_cpa are not getting Inactivated
2520 ---so delete duplicate flag in merge table can handle that */
2521
2522 ---Updating the column MERGE_FAIL_MSG with veto reason
2523 UPDATE RA_CUSTOMER_MERGE_HEADERS
2524 SET MERGE_FAIL_MSG= veto_reason
2525 WHERE customer_merge_header_id in
2526 (select customer_merge_header_id
2527 from ra_customer_merges m
2528 where m.duplicate_id = from_customer_id
2529 AND m.process_flag = 'N'
2530 AND m.request_id = req_id
2531 AND m.set_number = set_num);
2532
2533 arp_message.set_line( SQL%ROWCOUNT || ' '|| 'Rows updated in RA_CUSTOMER_MERGE_HEADERS with veto reason :' ||' '|| veto_reason );
2534
2535 arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()-' );
2536
2537 END veto_delete;
2538
2539 --4230396
2540 PROCEDURE raise_events(p_req_id NUMBER) IS
2541 l_key VARCHAR2(240);
2542 l_list WF_PARAMETER_LIST_T;
2543 l_header_id NUMBER;
2544 CURSOR merges IS
2545 SELECT customer_merge_header_id
2546 FROM ra_customer_merge_headers
2547 WHERE request_id = p_req_id
2548 AND process_flag = 'Y';
2549 BEGIN
2550 arp_message.set_line( 'ARP_CMERGE_MASTER.raise_events() +');
2551
2552 OPEN merges;
2553 LOOP
2554 FETCH merges into l_header_id;
2555 EXIT WHEN merges%NOTFOUND;
2556 l_key := HZ_EVENT_PKG.item_key('oracle.apps.ar.hz.CustAccount.merge');
2557 -- initialization of object variables
2558 l_list := WF_PARAMETER_LIST_T();
2559 wf_event.addParameterToList(p_name => 'customer_merge_header_id',
2560 p_value => l_header_id,
2561 p_parameterlist => l_list);
2562 wf_event.addParameterToList(p_name => 'Q_CORRELATION_ID',
2563 p_value => 'oracle.apps.ar.hz.CustAccount.merge',
2564 p_parameterlist => l_list);
2565 -- Raise Event
2566 HZ_EVENT_PKG.raise_event(
2567 p_event_name => 'oracle.apps.ar.hz.CustAccount.merge',
2568 p_event_key => l_key,
2569 p_parameters => l_list );
2570 l_list.DELETE;
2571 END LOOP;
2572 CLOSE merges;
2573 arp_message.set_line( 'ARP_CMERGE_MASTER.raise_events() -');
2574 EXCEPTION
2575 WHEN OTHERS THEN
2576 arp_message.set_error( 'ARP_CMERGE_MASTER.raise_events' );
2577 RAISE;
2578 END raise_events;
2579
2580 END ARP_CMERGE_MASTER;