[Home] [Help]
PACKAGE BODY: APPS.OE_CUST_MERGE_DATA_FIX
Source
1 PACKAGE BODY OE_CUST_MERGE_DATA_FIX AS
2 /* $Header: OEXCMDFB.pls 120.0 2005/06/01 01:15:27 appldev noship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10 /*------------------------------------------------*/
11 /*--- PRIVATE Procedure OE_Merge_Headers ---*/
12 /*------------------------------------------------*/
13
14 Procedure OE_Merge_Headers (Req_Id IN NUMBER,
15 Set_Num IN NUMBER,
16 Process_Mode IN VARCHAR2)
17 IS
18 CURSOR c1 is
19 select HEADER_ID
20 from oe_order_headers_all
21 where ship_to_org_id in
22 (select m.duplicate_site_id
23 from ra_customer_merges m
24 where m.process_flag = 'Y'
25 and m.request_id = req_id
26 and m.set_number = set_num)
27 for update nowait;
28
29
30 CURSOR c2 is
31 select HEADER_ID
32 from oe_order_headers_all
33 where invoice_to_org_id in
34 (select m.duplicate_site_id
35 from ra_customer_merges m
36 where m.process_flag = 'Y'
37 and m.request_id = req_id
38 and m.set_number = set_num)
39 for update nowait;
40
41
42 CURSOR c3 is
43 select HEADER_ID
44 from oe_order_headers_all
45 where deliver_to_org_id in
46 (select m.duplicate_site_id
47 from ra_customer_merges m
48 where m.process_flag = 'Y'
49 and m.request_id = req_id
50 and m.set_number = set_num)
51 for update nowait;
52
53 CURSOR c4 is
54 select HEADER_ID
55 from oe_order_headers_all
56 where sold_to_org_id in
57 (select m.duplicate_id
58 from ra_customer_merges m
59 where m.process_flag = 'Y'
60 and m.request_id = req_id
61 and m.set_number = set_num)
62 for update nowait;
63
64
65
66
67 --
68 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
69 --
70 BEGIN
71 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers()+' );
72 IF l_debug_level > 0 THEN
73 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADERS' ) ;
74 END IF;
75
76 /* both customer and site level */
77
78 IF( process_mode = 'LOCK' ) THEN
79
80 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
81 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
82 IF l_debug_level > 0 THEN
83 oe_debug_pub.add( 'LOCKING TABLE OE_ORDER_HEADERS_ALL' ) ;
84 END IF;
85
86 open c1;
87 close c1;
88
89 open c2;
90 close c2;
91
92 open c3;
93 close c3;
94
95 open c4;
96 close c4;
97
98
99 ELSE
100
101 /* site level update */
102 IF l_debug_level > 0 THEN
103 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
104 END IF;
105 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
106 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
107 IF l_debug_level > 0 THEN
108 oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.SHIP_TO_ORG_ID' ) ;
109 END IF;
110
111 UPDATE oe_order_headers_all a
112 set ship_to_org_id = (select distinct m.customer_site_id
113 from ra_customer_merges m
114 where a.ship_to_org_id = m.duplicate_site_id
115 and m.request_id = req_id
116 and m.process_flag = 'Y'
117 and m.set_number = set_num),
118 last_update_date = sysdate,
119 last_updated_by = fnd_global.user_id,
120 last_update_login = fnd_global.login_id,
121 request_id = req_id,
122 program_application_id =fnd_global.prog_appl_id,
123 program_id = fnd_global.conc_program_id,
124 program_update_date = sysdate
125 where ship_to_org_id in (select m.duplicate_site_id
126 from ra_customer_merges m
127 where m.process_flag = 'Y'
128 and m.request_id = req_id
129 and m.set_number = set_num);
130 g_count := sql%rowcount;
131 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
132 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
133 IF l_debug_level > 0 THEN
134 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
135 END IF;
136
137 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
138 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
139
140 IF l_debug_level > 0 THEN
141 oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.INVOICE_TO_ORG_ID' ) ;
142 END IF;
143
144 UPDATE oe_order_headers_all a
145 set invoice_to_org_id = (select distinct m.customer_site_id
146 from ra_customer_merges m
147 where a.invoice_to_org_id = m.duplicate_site_id
148 and m.request_id = req_id
149 and m.process_flag = 'Y'
150 and m.set_number = set_num),
151 last_update_date = sysdate,
152 last_updated_by = fnd_global.user_id,
153 last_update_login = fnd_global.login_id,
154 request_id = req_id,
155 program_application_id =fnd_global.prog_appl_id,
156 program_id = fnd_global.conc_program_id,
157 program_update_date = sysdate
158 where invoice_to_org_id in (select m.duplicate_site_id
159 from ra_customer_merges m
160 where m.process_flag = 'Y'
161 and m.request_id = req_id
162 and m.set_number = set_num);
163 g_count := sql%rowcount;
164 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
165 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
166 IF l_debug_level > 0 THEN
167 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
168 END IF;
169
170 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
171 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
172
173 IF l_debug_level > 0 THEN
174 oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.DELIVER_TO_ORG_ID' ) ;
175 END IF;
176
177 UPDATE oe_order_headers_all a
178 set deliver_to_org_id = (select distinct m.customer_site_id
179 from ra_customer_merges m
180 where a.deliver_to_org_id = m.duplicate_site_id
181 and m.request_id = req_id
182 and m.process_flag = 'Y'
183 and m.set_number = set_num),
184 last_update_date = sysdate,
185 last_updated_by = fnd_global.user_id,
186 last_update_login = fnd_global.login_id,
187 request_id = req_id,
188 program_application_id =fnd_global.prog_appl_id,
189 program_id = fnd_global.conc_program_id,
190 program_update_date = sysdate
191 where deliver_to_org_id in (select m.duplicate_site_id
192 from ra_customer_merges m
193 where m.process_flag = 'Y'
194 and m.request_id = req_id
195 and m.set_number = set_num);
196 g_count := sql%rowcount;
197 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
198 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
199 IF l_debug_level > 0 THEN
200 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
201 END IF;
202 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
203 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
204
205
206 /* customer level update */
207 IF l_debug_level > 0 THEN
208 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
209 END IF;
210 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
211 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID' ) ;
214 END IF;
215
216 UPDATE oe_order_headers_all a
217 set sold_to_org_id = (select distinct m.customer_id
218 from ra_customer_merges m
219 where a.sold_to_org_id = m.duplicate_id
220 and m.process_flag = 'Y'
221 and m.request_id = req_id
222 and m.set_number = set_num),
223 last_update_date = sysdate,
224 last_updated_by = fnd_global.user_id,
225 last_update_login = fnd_global.login_id,
226 request_id = req_id,
227 program_application_id =fnd_global.prog_appl_id,
228 program_id = fnd_global.conc_program_id,
229 program_update_date = sysdate
230 where sold_to_org_id in (select m.duplicate_id
231 from ra_customer_merges m
232 where m.process_flag = 'Y'
233 and m.request_id = req_id
234 and m.set_number = set_num);
235
236 g_count := sql%rowcount;
237
238 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
239 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
240 IF l_debug_level > 0 THEN
241 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
242 END IF;
243
244
245 END IF;
246
247 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers()-' );
248 IF l_debug_level > 0 THEN
249 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADERS' ) ;
250 END IF;
251
252 EXCEPTION
253 When others then
254 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers-' );
255 IF l_debug_level > 0 THEN
256 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADERS' ) ;
257 END IF;
258 IF l_debug_level > 0 THEN
259 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
260 END IF;
261 raise;
262
263 END OE_Merge_Headers;
264
265
266 /*-------------------------------------------------*/
267 /*--- PRIVATE Procedure OE_Merge_Header_History ---*/
268 /*-------------------------------------------------*/
269
270 /* ---- To be released with the Audit Trail Project -------
271 Procedure OE_Merge_Header_History (Req_Id IN NUMBER,
272 Set_Num IN NUMBER,
273 Process_Mode IN VARCHAR2)
274 IS
275 CURSOR c1 is
276 select HEADER_ID
277 from oe_order_header_history
278 where ship_to_org_id in
279 (select m.duplicate_site_id
280 from ra_customer_merges m
281 where m.process_flag = 'Y'
282 and m.request_id = req_id
283 and m.set_number = set_num)
284 for update nowait;
285
286
287 CURSOR c2 is
288 select HEADER_ID
289 from oe_order_header_history
290 where invoice_to_org_id in
291 (select m.duplicate_site_id
292 from ra_customer_merges m
293 where m.process_flag = 'Y'
294 and m.request_id = req_id
295 and m.set_number = set_num)
296 for update nowait;
297
298
299 CURSOR c3 is
300 select HEADER_ID
301 from oe_order_header_history
302 where deliver_to_org_id in
303 (select m.duplicate_site_id
304 from ra_customer_merges m
305 where m.process_flag = 'Y'
306 and m.request_id = req_id
307 and m.set_number = set_num)
308 for update nowait;
309
310 CURSOR c4 is
311 select HEADER_ID
312 from oe_order_header_history
313 where sold_to_org_id in
314 (select m.duplicate_id
315 from ra_customer_merges m
316 where m.process_flag = 'Y'
317 and m.request_id = req_id
318 and m.set_number = set_num)
319 for update nowait;
320
321
322
323
324 BEGIN
325 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_History()+' );
326
327 -- both customer and site level
328
329 IF( process_mode = 'LOCK' ) THEN
330
331 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
332 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
333
334 open c1;
335 close c1;
336
337 open c2;
338 close c2;
339
340 open c3;
341 close c3;
342
343 open c4;
344 close c4;
345
346
347 ELSE
348
349 -- site level update
350 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
351 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
352
353 UPDATE oe_order_header_history a
354 set ship_to_org_id = (select distinct m.customer_site_id
355 from ra_customer_merges m
356 where a.ship_to_org_id = m.duplicate_site_id
357 and m.request_id = req_id
358 and m.process_flag = 'Y'
359 and m.set_number = set_num),
360 last_update_date = sysdate,
361 last_updated_by = fnd_global.user_id,
362 last_update_login = fnd_global.login_id,
363 request_id = req_id,
364 program_application_id =fnd_global.prog_appl_id,
365 program_id = fnd_global.conc_program_id,
366 program_update_date = sysdate
367 where ship_to_org_id in (select m.duplicate_site_id
368 from ra_customer_merges m
369 where m.process_flag = 'Y'
370 and m.request_id = req_id
371 and m.set_number = set_num);
372 g_count := sql%rowcount;
373 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
374 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
375
376 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
377 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
378
379 UPDATE oe_order_header_history a
380 set invoice_to_org_id = (select distinct m.customer_site_id
381 from ra_customer_merges m
382 where a.invoice_to_org_id = m.duplicate_site_id
383 and m.request_id = req_id
384 and m.process_flag = 'Y'
385 and m.set_number = set_num),
386 last_update_date = sysdate,
387 last_updated_by = fnd_global.user_id,
388 last_update_login = fnd_global.login_id,
389 request_id = req_id,
390 program_application_id =fnd_global.prog_appl_id,
391 program_id = fnd_global.conc_program_id,
392 program_update_date = sysdate
393 where invoice_to_org_id in (select m.duplicate_site_id
394 from ra_customer_merges m
395 where m.process_flag = 'Y'
396 and m.request_id = req_id
397 and m.set_number = set_num);
398 g_count := sql%rowcount;
399 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
400 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
401
402 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
403 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
404
405 UPDATE oe_order_header_history a
406 set deliver_to_org_id = (select distinct m.customer_site_id
407 from ra_customer_merges m
411 and m.set_number = set_num),
408 where a.deliver_to_org_id = m.duplicate_site_id
409 and m.request_id = req_id
410 and m.process_flag = 'Y'
412 last_update_date = sysdate,
413 last_updated_by = fnd_global.user_id,
414 last_update_login = fnd_global.login_id,
415 request_id = req_id,
416 program_application_id =fnd_global.prog_appl_id,
417 program_id = fnd_global.conc_program_id,
418 program_update_date = sysdate
419 where deliver_to_org_id in (select m.duplicate_site_id
420 from ra_customer_merges m
421 where m.process_flag = 'Y'
422 and m.request_id = req_id
423 and m.set_number = set_num);
424 g_count := sql%rowcount;
425 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
426 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
427
428 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
429 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
430
431
432 -- customer level update --
433
434 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
435 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
436
437 UPDATE oe_order_header_history a
438 set sold_to_org_id = (select distinct m.customer_id
439 from ra_customer_merges m
440 where a.sold_to_org_id = m.duplicate_id
441 and m.process_flag = 'Y'
442 and m.request_id = req_id
443 and m.set_number = set_num),
444 last_update_date = sysdate,
445 last_updated_by = fnd_global.user_id,
446 last_update_login = fnd_global.login_id,
447 request_id = req_id,
448 program_application_id =fnd_global.prog_appl_id,
449 program_id = fnd_global.conc_program_id,
450 program_update_date = sysdate
451 where sold_to_org_id in (select m.duplicate_id
452 from ra_customer_merges m
453 where m.process_flag = 'Y'
454 and m.request_id = req_id
455 and m.set_number = set_num);
456
457 g_count := sql%rowcount;
458
459 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
460 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
461
462 END IF;
463
464 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_History()-' );
465
466 EXCEPTION
467 When others then
468 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_History-' );
469 raise;
470
471 END OE_Merge_Header_History;
472
473 To be released with the Audit Trail project */
474
475 /*------------------------------------------------*/
476 /*--- PRIVATE PROCEDURE OE_Merge_Headers_IFACE ---*/
477 /*------------------------------------------------*/
478
479 /* -- Interface tables need not be updated
480
481 Procedure OE_Merge_Headers_IFACE (Req_Id IN NUMBER,
482 Set_Num IN NUMBER,
483 Process_Mode IN VARCHAR2)
484 IS
485 CURSOR c1 is
486 select HEADER_ID
487 from oe_headers_iface_all
488 where ship_to_org_id in
489 (select m.duplicate_site_id
490 from ra_customer_merges m
491 where m.process_flag = 'Y'
492 and m.request_id = req_id
493 and m.set_number = set_num)
494 for update nowait;
495
496
497 CURSOR c2 is
498 select HEADER_ID
499 from oe_headers_iface_all
500 where invoice_to_org_id in
501 (select m.duplicate_site_id
502 from ra_customer_merges m
503 where m.process_flag = 'Y'
504 and m.request_id = req_id
505 and m.set_number = set_num)
506 for update nowait;
507
508
509 CURSOR c3 is
510 select HEADER_ID
511 from oe_headers_iface_all
512 where deliver_to_org_id in
513 (select m.duplicate_site_id
514 from ra_customer_merges m
515 where m.process_flag = 'Y'
516 and m.request_id = req_id
517 and m.set_number = set_num)
518 for update nowait;
519
520 CURSOR c4 is
521 select HEADER_ID
522 from oe_headers_iface_all
523 where sold_to_org_id in
524 (select m.duplicate_id
525 from ra_customer_merges m
526 where m.process_flag = 'Y'
527 and m.request_id = req_id
528 and m.set_number = set_num)
529 for update nowait;
530
531
532
533
534 BEGIN
535 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers_IFACE()+' );
536
537 -- both customer and site level
538
539 IF( process_mode = 'LOCK' ) THEN
540
541 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
542 -- arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
543
544 open c1;
545 close c1;
546
547 open c2;
548 close c2;
549
550 open c3;
551 close c3;
552
553 open c4;
554 close c4;
555
556
557 ELSE
558
559 -- site level update
560 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
564 set ship_to_org_id = (select distinct m.customer_site_id
561 -- arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
562
563 UPDATE oe_headers_iface_all a
565 from ra_customer_merges m
566 where a.ship_to_org_id = m.duplicate_site_id
567 and m.request_id = req_id
568 and m.process_flag = 'Y'
569 and m.set_number = set_num),
570 last_update_date = sysdate,
571 last_updated_by = fnd_global.user_id,
572 last_update_login = fnd_global.login_id,
573 request_id = req_id,
574 program_application_id =fnd_global.prog_appl_id,
575 program_id = fnd_global.conc_program_id,
576 program_update_date = sysdate
577 where ship_to_org_id in (select m.duplicate_site_id
578 from ra_customer_merges m
579 where m.process_flag = 'Y'
580 and m.request_id = req_id
581 and m.set_number = set_num);
582 g_count := sql%rowcount;
583 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
584 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
585
586 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
587 -- arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
588
589 UPDATE oe_headers_iface_all a
590 set invoice_to_org_id = (select distinct m.customer_site_id
591 from ra_customer_merges m
592 where a.invoice_to_org_id = m.duplicate_site_id
593 and m.request_id = req_id
594 and m.process_flag = 'Y'
595 and m.set_number = set_num),
596 last_update_date = sysdate,
597 last_updated_by = fnd_global.user_id,
598 last_update_login = fnd_global.login_id,
599 request_id = req_id,
600 program_application_id =fnd_global.prog_appl_id,
601 program_id = fnd_global.conc_program_id,
602 program_update_date = sysdate
603 where invoice_to_org_id in (select m.duplicate_site_id
604 from ra_customer_merges m
605 where m.process_flag = 'Y'
606 and m.request_id = req_id
607 and m.set_number = set_num);
608 g_count := sql%rowcount;
609 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
610 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
611
612 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
613 -- arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
614
615 UPDATE oe_headers_iface_all a
616 set deliver_to_org_id = (select distinct m.customer_site_id
617 from ra_customer_merges m
618 where a.deliver_to_org_id = m.duplicate_site_id
619 and m.request_id = req_id
620 and m.process_flag = 'Y'
621 and m.set_number = set_num),
622 last_update_date = sysdate,
623 last_updated_by = fnd_global.user_id,
624 last_update_login = fnd_global.login_id,
625 request_id = req_id,
626 program_application_id =fnd_global.prog_appl_id,
627 program_id = fnd_global.conc_program_id,
628 program_update_date = sysdate
629 where deliver_to_org_id in (select m.duplicate_site_id
630 from ra_customer_merges m
631 where m.process_flag = 'Y'
632 and m.request_id = req_id
633 and m.set_number = set_num);
634 g_count := sql%rowcount;
635 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
636 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
637
638 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
639 -- arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
640
641
642 -- customer level update
643 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
644 -- arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
645
646 UPDATE oe_headers_iface_all a
647 set sold_to_org_id = (select distinct m.customer_id
648 from ra_customer_merges m
649 where a.sold_to_org_id = m.duplicate_id
650 and m.process_flag = 'Y'
651 and m.request_id = req_id
652 and m.set_number = set_num),
653 last_update_date = sysdate,
654 last_updated_by = fnd_global.user_id,
655 last_update_login = fnd_global.login_id,
656 request_id = req_id,
657 program_application_id =fnd_global.prog_appl_id,
658 program_id = fnd_global.conc_program_id,
659 program_update_date = sysdate
660 where sold_to_org_id in (select m.duplicate_id
661 from ra_customer_merges m
662 where m.process_flag = 'Y'
663 and m.request_id = req_id
664 and m.set_number = set_num);
665
666 g_count := sql%rowcount;
667
668 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
669 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
670
671 END IF;
672
673 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers_IFACE()-' );
674
675 EXCEPTION
676 When others then
677 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers_IFACE-' );
678 raise;
679
680 END OE_Merge_Headers_IFACE;
681
682 Interface tables need not be updated */
683
687
684 /*------------------------------------------------*/
685 /*--- PRIVATE PROCEDURE OE_Merge_Header_ACKS ---*/
686 /*------------------------------------------------*/
688
689 Procedure OE_Merge_Header_ACKS (Req_Id IN NUMBER,
690 Set_Num IN NUMBER,
691 Process_Mode IN VARCHAR2)
692 IS
693 CURSOR c1 is
694 select HEADER_ID
695 from OE_HEADER_ACKS
696 where ship_to_org_id in
697 (select m.duplicate_site_id
698 from ra_customer_merges m
699 where m.process_flag = 'Y'
700 and m.request_id = req_id
701 and m.set_number = set_num)
702 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
703 for update nowait;
704
705
706 CURSOR c2 is
707 select HEADER_ID
708 from OE_HEADER_ACKS
709 where invoice_to_org_id in
710 (select m.duplicate_site_id
711 from ra_customer_merges m
712 where m.process_flag = 'Y'
713 and m.request_id = req_id
714 and m.set_number = set_num)
715 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
716 for update nowait;
717
718
719 CURSOR c3 is
720 select HEADER_ID
721 from OE_HEADER_ACKS
722 where deliver_to_org_id in
723 (select m.duplicate_site_id
724 from ra_customer_merges m
725 where m.process_flag = 'Y'
726 and m.request_id = req_id
727 and m.set_number = set_num)
728 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
729 for update nowait;
730
731 CURSOR c4 is
732 select HEADER_ID
733 from OE_HEADER_ACKS
734 where sold_to_org_id in
735 (select m.duplicate_id
736 from ra_customer_merges m
737 where m.process_flag = 'Y'
738 and m.request_id = req_id
739 and m.set_number = set_num)
740 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
741 for update nowait;
742
743
744
745
746 --
747 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
748 --
749 BEGIN
750 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_ACKS()+' );
751 IF l_debug_level > 0 THEN
752 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADER_ACKS' ) ;
753 END IF;
754
755 /* both customer and site level */
756
757 IF( process_mode = 'LOCK' ) THEN
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.add( 'LOCKING TABLE OE_HEADER_ACKS' ) ;
760 END IF;
761 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
762 -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
763
764 open c1;
765 close c1;
766
767 open c2;
768 close c2;
769
770 open c3;
771 close c3;
772
773 open c4;
774 close c4;
775
776
777 ELSE
778
779 /* site level update */
780 IF l_debug_level > 0 THEN
781 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
782 END IF;
783
784 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
785 -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
786 IF l_debug_level > 0 THEN
787 oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.SHIP_TO_ORG_ID' ) ;
788 END IF;
789
790 UPDATE OE_HEADER_ACKS a
791 set ship_to_org_id = (select distinct m.customer_site_id
792 from ra_customer_merges m
793 where a.ship_to_org_id = m.duplicate_site_id
794 and m.request_id = req_id
795 and m.process_flag = 'Y'
796 and m.set_number = set_num),
797 last_update_date = sysdate,
798 last_updated_by = fnd_global.user_id,
799 last_update_login = fnd_global.login_id,
800 request_id = req_id,
801 program_application_id =fnd_global.prog_appl_id,
802 program_id = fnd_global.conc_program_id,
803 program_update_date = sysdate
804 where ship_to_org_id in (select m.duplicate_site_id
805 from ra_customer_merges m
806 where m.process_flag = 'Y'
807 and m.request_id = req_id
808 and m.set_number = set_num)
809 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
810 g_count := sql%rowcount;
811 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
812 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
813 IF l_debug_level > 0 THEN
814 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
815 END IF;
816
817 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
818 -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
819 IF l_debug_level > 0 THEN
820 oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.INVOICE_TO_ORG_ID' ) ;
821 END IF;
822
823 UPDATE OE_HEADER_ACKS a
824 set invoice_to_org_id = (select distinct m.customer_site_id
825 from ra_customer_merges m
826 where a.invoice_to_org_id = m.duplicate_site_id
827 and m.request_id = req_id
828 and m.process_flag = 'Y'
829 and m.set_number = set_num),
830 last_update_date = sysdate,
831 last_updated_by = fnd_global.user_id,
832 last_update_login = fnd_global.login_id,
833 request_id = req_id,
834 program_application_id =fnd_global.prog_appl_id,
838 from ra_customer_merges m
835 program_id = fnd_global.conc_program_id,
836 program_update_date = sysdate
837 where invoice_to_org_id in (select m.duplicate_site_id
839 where m.process_flag = 'Y'
840 and m.request_id = req_id
841 and m.set_number = set_num)
842 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
843 g_count := sql%rowcount;
844 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
845 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
846 IF l_debug_level > 0 THEN
847 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
848 END IF;
849
850 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
851 -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
852 IF l_debug_level > 0 THEN
853 oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.DELIVER_TO_ORG_ID' ) ;
854 END IF;
855
856 UPDATE OE_HEADER_ACKS a
857 set deliver_to_org_id = (select distinct m.customer_site_id
858 from ra_customer_merges m
859 where a.deliver_to_org_id = m.duplicate_site_id
860 and m.request_id = req_id
861 and m.process_flag = 'Y'
862 and m.set_number = set_num),
863 last_update_date = sysdate,
864 last_updated_by = fnd_global.user_id,
865 last_update_login = fnd_global.login_id,
866 request_id = req_id,
867 program_application_id =fnd_global.prog_appl_id,
868 program_id = fnd_global.conc_program_id,
869 program_update_date = sysdate
870 where deliver_to_org_id in (select m.duplicate_site_id
871 from ra_customer_merges m
872 where m.process_flag = 'Y'
873 and m.request_id = req_id
874 and m.set_number = set_num)
875 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
876 g_count := sql%rowcount;
877 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
878 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
879 IF l_debug_level > 0 THEN
880 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
881 END IF;
882
883 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
884 -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
885
886
887 /* customer level update */
888 IF l_debug_level > 0 THEN
889 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
890 END IF;
891 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
892 -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
893
894 IF l_debug_level > 0 THEN
895 oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.SOLD_TO_ORG_ID' ) ;
896 END IF;
897
898 UPDATE OE_HEADER_ACKS a
899 set sold_to_org_id = (select distinct m.customer_id
900 from ra_customer_merges m
901 where a.sold_to_org_id = m.duplicate_id
902 and m.process_flag = 'Y'
903 and m.request_id = req_id
904 and m.set_number = set_num),
905 last_update_date = sysdate,
906 last_updated_by = fnd_global.user_id,
907 last_update_login = fnd_global.login_id,
908 request_id = req_id,
909 program_application_id =fnd_global.prog_appl_id,
910 program_id = fnd_global.conc_program_id,
911 program_update_date = sysdate
912 where sold_to_org_id in (select m.duplicate_id
913 from ra_customer_merges m
914 where m.process_flag = 'Y'
915 and m.request_id = req_id
916 and m.set_number = set_num)
917 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
918
919 g_count := sql%rowcount;
920
921 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
922 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
923 IF l_debug_level > 0 THEN
924 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
925 END IF;
926
927 END IF;
928
929 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_ACKS()-' );
930 IF l_debug_level > 0 THEN
931 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADER_ACKS' ) ;
932 END IF;
933
934 EXCEPTION
935 When others then
936 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_ACKS-' );
937 IF l_debug_level > 0 THEN
938 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADER_ACKS' ) ;
939 END IF;
940 IF l_debug_level > 0 THEN
941 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
942 END IF;
943 raise;
944
945 END OE_Merge_Header_ACKS;
946
947 /*------------------------------------------------*/
948 /*--- PRIVATE PROCEDURE OE_Merge_Lines ---*/
949 /*------------------------------------------------*/
950
951 Procedure OE_Merge_Lines (Req_Id IN NUMBER,
952 Set_Num IN NUMBER,
953 Process_Mode IN VARCHAR2)
954 IS
955 CURSOR c1 is
956 select line_id
957 from oe_order_lines_all
958 where ship_to_org_id in
959 (select m.duplicate_site_id
960 from ra_customer_merges m
961 where m.process_flag = 'Y'
962 and m.request_id = req_id
963 and m.set_number = set_num)
964 for update nowait;
965
966
967 CURSOR c2 is
971 (select m.duplicate_site_id
968 select line_id
969 from oe_order_lines_all
970 where invoice_to_org_id in
972 from ra_customer_merges m
973 where m.process_flag = 'Y'
974 and m.request_id = req_id
975 and m.set_number = set_num)
976 for update nowait;
977
978
979 CURSOR c3 is
980 select line_id
981 from oe_order_lines_all
982 where deliver_to_org_id in
983 (select m.duplicate_site_id
984 from ra_customer_merges m
985 where m.process_flag = 'Y'
986 and m.request_id = req_id
987 and m.set_number = set_num)
988 for update nowait;
989
990
991 CURSOR c4 is
992 select line_id
993 from oe_order_lines_all
994 where intmed_ship_to_org_id in
995 (select m.duplicate_site_id
996 from ra_customer_merges m
997 where m.process_flag = 'Y'
998 and m.request_id = req_id
999 and m.set_number = set_num)
1000 for update nowait;
1001
1002 CURSOR c5 is
1003 select line_id
1004 from oe_order_lines_all
1005 where sold_to_org_id in
1006 (select m.duplicate_id
1007 from ra_customer_merges m
1008 where m.process_flag = 'Y'
1009 and m.request_id = req_id
1010 and m.set_number = set_num)
1011 for update nowait;
1012
1013 --
1014 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1015 --
1016 BEGIN
1017 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines()+' );
1018 IF l_debug_level > 0 THEN
1019 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES' ) ;
1020 END IF;
1021
1022 /* both customer and site level */
1023
1024 IF( process_mode = 'LOCK' ) THEN
1025
1026 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1027 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1028 IF l_debug_level > 0 THEN
1029 oe_debug_pub.add( 'LOCKING TABLE OE_ORDER_LINES_ALL' ) ;
1030 END IF;
1031
1032 open c1;
1033 close c1;
1034
1035 open c2;
1036 close c2;
1037
1038 open c3;
1039 close c3;
1040
1041 open c4;
1042 close c4;
1043
1044 open c5;
1045 close c5;
1046
1047 ELSE
1048 /* site level update */
1049 IF l_debug_level > 0 THEN
1050 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
1051 END IF;
1052
1053 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1054 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1055 IF l_debug_level > 0 THEN
1056 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.SHIP_TO_ORG_ID' ) ;
1057 END IF;
1058
1059 UPDATE oe_order_lines_all a
1060 set ship_to_org_id = (select distinct m.customer_site_id
1061 from ra_customer_merges m
1062 where a.ship_to_org_id = m.duplicate_site_id
1063 and m.request_id = req_id
1064 and m.process_flag = 'Y'
1065 and m.set_number = set_num),
1066 last_update_date = sysdate,
1067 last_updated_by = fnd_global.user_id,
1068 last_update_login = fnd_global.login_id,
1069 request_id = req_id,
1070 program_application_id =fnd_global.prog_appl_id,
1071 program_id = fnd_global.conc_program_id,
1072 program_update_date = sysdate
1073 where ship_to_org_id in (select m.duplicate_site_id
1074 from ra_customer_merges m
1075 where m.process_flag = 'Y'
1076 and m.request_id = req_id
1077 and m.set_number = set_num);
1078 g_count := sql%rowcount;
1079 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1080 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1081 IF l_debug_level > 0 THEN
1082 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1083 END IF;
1084 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1085 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1086 IF l_debug_level > 0 THEN
1087 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.INVOICE_TO_ORG_ID' ) ;
1088 END IF;
1089
1090 UPDATE oe_order_lines_all a
1091 set invoice_to_org_id = (select distinct m.customer_site_id
1092 from ra_customer_merges m
1093 where a.invoice_to_org_id = m.duplicate_site_id
1094 and m.request_id = req_id
1095 and m.process_flag = 'Y'
1096 and m.set_number = set_num),
1097 last_update_date = sysdate,
1098 last_updated_by = fnd_global.user_id,
1099 last_update_login = fnd_global.login_id,
1100 request_id = req_id,
1101 program_application_id =fnd_global.prog_appl_id,
1102 program_id = fnd_global.conc_program_id,
1103 program_update_date = sysdate
1104 where invoice_to_org_id in (select m.duplicate_site_id
1105 from ra_customer_merges m
1106 where m.process_flag = 'Y'
1107 and m.request_id = req_id
1108 and m.set_number = set_num);
1109 g_count := sql%rowcount;
1110 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1111 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1112 IF l_debug_level > 0 THEN
1113 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1114 END IF;
1115
1119 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.DELIVER_TO_ORG_ID' ) ;
1116 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1117 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1118 IF l_debug_level > 0 THEN
1120 END IF;
1121
1122 UPDATE oe_order_lines_all a
1123 set deliver_to_org_id = (select distinct m.customer_site_id
1124 from ra_customer_merges m
1125 where a.deliver_to_org_id = m.duplicate_site_id
1126 and m.request_id = req_id
1127 and m.process_flag = 'Y'
1128 and m.set_number = set_num),
1129 last_update_date = sysdate,
1130 last_updated_by = fnd_global.user_id,
1131 last_update_login = fnd_global.login_id,
1132 request_id = req_id,
1133 program_application_id =fnd_global.prog_appl_id,
1134 program_id = fnd_global.conc_program_id,
1135 program_update_date = sysdate
1136 where deliver_to_org_id in (select m.duplicate_site_id
1137 from ra_customer_merges m
1138 where m.process_flag = 'Y'
1139 and m.request_id = req_id
1140 and m.set_number = set_num);
1141 g_count := sql%rowcount;
1142 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1143 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1144 IF l_debug_level > 0 THEN
1145 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1146 END IF;
1147
1148 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1149 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1150 IF l_debug_level > 0 THEN
1151 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.INTMED_TO_ORG_ID' ) ;
1152 END IF;
1153
1154 UPDATE oe_order_lines_all a
1155 set intmed_ship_to_org_id = (select distinct m.customer_site_id
1156 from ra_customer_merges m
1157 where a.intmed_ship_to_org_id = m.duplicate_site_id
1158 and m.request_id = req_id
1159 and m.process_flag = 'Y'
1160 and m.set_number = set_num),
1161 last_update_date = sysdate,
1162 last_updated_by = fnd_global.user_id,
1163 last_update_login = fnd_global.login_id,
1164 request_id = req_id,
1165 program_application_id =fnd_global.prog_appl_id,
1166 program_id = fnd_global.conc_program_id,
1167 program_update_date = sysdate
1168 where intmed_ship_to_org_id in (select m.duplicate_site_id
1169 from ra_customer_merges m
1170 where m.process_flag = 'Y'
1171 and m.request_id = req_id
1172 and m.set_number = set_num);
1173 g_count := sql%rowcount;
1174 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1175 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1176 IF l_debug_level > 0 THEN
1177 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1178 END IF;
1179
1180 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1181 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1182
1183 /* customer level update */
1184 IF l_debug_level > 0 THEN
1185 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
1186 END IF;
1187 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1188 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1189 IF l_debug_level > 0 THEN
1190 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.SOLD_TO_ORG_ID' ) ;
1191 END IF;
1192
1193 UPDATE oe_order_lines_all a
1194 set sold_to_org_id = (select distinct m.customer_id
1195 from ra_customer_merges m
1196 where a.sold_to_org_id = m.duplicate_id
1197 and m.process_flag = 'Y'
1198 and m.request_id = req_id
1199 and m.set_number = set_num),
1200 last_update_date = sysdate,
1201 last_updated_by = fnd_global.user_id,
1202 last_update_login = fnd_global.login_id,
1203 request_id = req_id,
1204 program_application_id =fnd_global.prog_appl_id,
1205 program_id = fnd_global.conc_program_id,
1206 program_update_date = sysdate
1207 where sold_to_org_id in (select m.duplicate_id
1208 from ra_customer_merges m
1209 where m.process_flag = 'Y'
1210 and m.request_id = req_id
1211 and m.set_number = set_num);
1212
1213 g_count := sql%rowcount;
1214
1215 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1216 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1217 IF l_debug_level > 0 THEN
1218 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1219 END IF;
1220
1221 END IF;
1222
1223 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines()-' );
1224 IF l_debug_level > 0 THEN
1225 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES' ) ;
1226 END IF;
1227
1228 EXCEPTION
1229 When others then
1230 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines-' );
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES' ) ;
1233 END IF;
1234 IF l_debug_level > 0 THEN
1235 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
1236 END IF;
1237 raise;
1238
1239 END OE_Merge_Lines;
1240
1241 /*-------------------------------------------------*/
1245 Procedure OE_Merge_Lines_History (Req_Id IN NUMBER,
1242 /*--- PRIVATE PROCEDURE OE_Merge_Lines_History ---*/
1243 /*-------------------------------------------------*/
1244
1246 Set_Num IN NUMBER,
1247 Process_Mode IN VARCHAR2)
1248 IS
1249 CURSOR c1 is
1250 select line_id
1251 from oe_order_lines_history
1252 where ship_to_org_id in
1253 (select m.duplicate_site_id
1254 from ra_customer_merges m
1255 where m.process_flag = 'Y'
1256 and m.request_id = req_id
1257 and m.set_number = set_num)
1258 for update nowait;
1259
1260
1261 CURSOR c2 is
1262 select line_id
1263 from oe_order_lines_history
1264 where invoice_to_org_id in
1265 (select m.duplicate_site_id
1266 from ra_customer_merges m
1267 where m.process_flag = 'Y'
1268 and m.request_id = req_id
1269 and m.set_number = set_num)
1270 for update nowait;
1271
1272
1273 CURSOR c3 is
1274 select line_id
1275 from oe_order_lines_history
1276 where deliver_to_org_id in
1277 (select m.duplicate_site_id
1278 from ra_customer_merges m
1279 where m.process_flag = 'Y'
1280 and m.request_id = req_id
1281 and m.set_number = set_num)
1282 for update nowait;
1283
1284
1285 CURSOR c4 is
1286 select line_id
1287 from oe_order_lines_history
1288 where intmed_ship_to_org_id in
1289 (select m.duplicate_site_id
1290 from ra_customer_merges m
1291 where m.process_flag = 'Y'
1292 and m.request_id = req_id
1293 and m.set_number = set_num)
1294 for update nowait;
1295
1296 CURSOR c5 is
1297 select line_id
1298 from oe_order_lines_history
1299 where sold_to_org_id in
1300 (select m.duplicate_id
1301 from ra_customer_merges m
1302 where m.process_flag = 'Y'
1303 and m.request_id = req_id
1304 and m.set_number = set_num)
1305 for update nowait;
1306
1307 --
1308 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1309 --
1310 BEGIN
1311 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_History()+' );
1312 IF l_debug_level > 0 THEN
1313 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES_HISTORY' ) ;
1314 END IF;
1315
1316 /* both customer and site level */
1317
1318 IF( process_mode = 'LOCK' ) THEN
1319
1320 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1321 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1322 IF l_debug_level > 0 THEN
1323 oe_debug_pub.add( 'LOCKING TABLE OE_ORDER_LINES_HISTORY' ) ;
1324 END IF;
1325
1326 open c1;
1327 close c1;
1328
1329 open c2;
1330 close c2;
1331
1332 open c3;
1333 close c3;
1334
1335 open c4;
1336 close c4;
1337
1338 open c5;
1339 close c5;
1340
1341 ELSE
1342 /* site level update */
1343 IF l_debug_level > 0 THEN
1344 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
1345 END IF;
1346
1347 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1348 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1349 IF l_debug_level > 0 THEN
1350 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.SHIP_TO_ORG_ID' ) ;
1351 END IF;
1352
1353 UPDATE oe_order_lines_history a
1354 set ship_to_org_id = (select distinct m.customer_site_id
1355 from ra_customer_merges m
1356 where a.ship_to_org_id = m.duplicate_site_id
1357 and m.request_id = req_id
1358 and m.process_flag = 'Y'
1359 and m.set_number = set_num),
1360 last_update_date = sysdate,
1361 last_updated_by = fnd_global.user_id,
1362 last_update_login = fnd_global.login_id,
1363 request_id = req_id,
1364 program_application_id =fnd_global.prog_appl_id,
1365 program_id = fnd_global.conc_program_id,
1366 program_update_date = sysdate
1367 where ship_to_org_id in (select m.duplicate_site_id
1368 from ra_customer_merges m
1369 where m.process_flag = 'Y'
1370 and m.request_id = req_id
1371 and m.set_number = set_num);
1372 g_count := sql%rowcount;
1373 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1374 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1377 END IF;
1378
1379 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1380 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1381 IF l_debug_level > 0 THEN
1382 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.INVOICE_TO_ORG_ID' ) ;
1383 END IF;
1384
1385 UPDATE oe_order_lines_history a
1386 set invoice_to_org_id = (select distinct m.customer_site_id
1387 from ra_customer_merges m
1388 where a.invoice_to_org_id = m.duplicate_site_id
1389 and m.request_id = req_id
1390 and m.process_flag = 'Y'
1391 and m.set_number = set_num),
1392 last_update_date = sysdate,
1396 program_application_id =fnd_global.prog_appl_id,
1393 last_updated_by = fnd_global.user_id,
1394 last_update_login = fnd_global.login_id,
1395 request_id = req_id,
1397 program_id = fnd_global.conc_program_id,
1398 program_update_date = sysdate
1399 where invoice_to_org_id in (select m.duplicate_site_id
1400 from ra_customer_merges m
1401 where m.process_flag = 'Y'
1402 and m.request_id = req_id
1403 and m.set_number = set_num);
1404 g_count := sql%rowcount;
1405 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1406 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1407 IF l_debug_level > 0 THEN
1408 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1409 END IF;
1410
1411 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1412 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1413 IF l_debug_level > 0 THEN
1414 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.DELIVER_TO_ORG_ID' ) ;
1415 END IF;
1416
1417 UPDATE oe_order_lines_history a
1418 set deliver_to_org_id = (select distinct m.customer_site_id
1419 from ra_customer_merges m
1420 where a.deliver_to_org_id = m.duplicate_site_id
1421 and m.request_id = req_id
1422 and m.process_flag = 'Y'
1423 and m.set_number = set_num),
1424 last_update_date = sysdate,
1425 last_updated_by = fnd_global.user_id,
1426 last_update_login = fnd_global.login_id,
1427 request_id = req_id,
1428 program_application_id =fnd_global.prog_appl_id,
1429 program_id = fnd_global.conc_program_id,
1430 program_update_date = sysdate
1431 where deliver_to_org_id in (select m.duplicate_site_id
1432 from ra_customer_merges m
1433 where m.process_flag = 'Y'
1434 and m.request_id = req_id
1435 and m.set_number = set_num);
1436 g_count := sql%rowcount;
1437 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1438 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1439 IF l_debug_level > 0 THEN
1440 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1441 END IF;
1442
1443 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1444 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1445 IF l_debug_level > 0 THEN
1446 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.INTMED_TO_ORG_ID' ) ;
1447 END IF;
1448
1449
1450 UPDATE oe_order_lines_history a
1451 set intmed_ship_to_org_id = (select distinct m.customer_site_id
1452 from ra_customer_merges m
1453 where a.intmed_ship_to_org_id = m.duplicate_site_id
1454 and m.request_id = req_id
1455 and m.process_flag = 'Y'
1456 and m.set_number = set_num),
1457 last_update_date = sysdate,
1458 last_updated_by = fnd_global.user_id,
1459 last_update_login = fnd_global.login_id,
1460 request_id = req_id,
1461 program_application_id =fnd_global.prog_appl_id,
1462 program_id = fnd_global.conc_program_id,
1463 program_update_date = sysdate
1464 where intmed_ship_to_org_id in (select m.duplicate_site_id
1465 from ra_customer_merges m
1466 where m.process_flag = 'Y'
1467 and m.request_id = req_id
1468 and m.set_number = set_num);
1469 g_count := sql%rowcount;
1470 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1471 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1472 IF l_debug_level > 0 THEN
1473 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1474 END IF;
1475
1476 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1477 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1478
1479
1480 /* customer level update */
1481 IF l_debug_level > 0 THEN
1482 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
1483 END IF;
1484 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1485 -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1486 IF l_debug_level > 0 THEN
1487 oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.SOLD_TO_ORG_ID' ) ;
1488 END IF;
1489
1490 UPDATE oe_order_lines_history a
1491 set sold_to_org_id = (select distinct m.customer_id
1492 from ra_customer_merges m
1493 where a.sold_to_org_id = m.duplicate_id
1494 and m.process_flag = 'Y'
1495 and m.request_id = req_id
1496 and m.set_number = set_num),
1497 last_update_date = sysdate,
1498 last_updated_by = fnd_global.user_id,
1499 last_update_login = fnd_global.login_id,
1500 request_id = req_id,
1501 program_application_id =fnd_global.prog_appl_id,
1502 program_id = fnd_global.conc_program_id,
1503 program_update_date = sysdate
1504 where sold_to_org_id in (select m.duplicate_id
1505 from ra_customer_merges m
1506 where m.process_flag = 'Y'
1507 and m.request_id = req_id
1508 and m.set_number = set_num);
1509
1510 g_count := sql%rowcount;
1511
1512 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1513 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1517
1514 IF l_debug_level > 0 THEN
1515 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1516 END IF;
1518 END IF;
1519
1520 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_History()-' );
1521 IF l_debug_level > 0 THEN
1522 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES_HISTORY' ) ;
1523 END IF;
1524
1525 EXCEPTION
1526 When others then
1527 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_History-' );
1528 IF l_debug_level > 0 THEN
1529 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES_HISTORY' ) ;
1530 END IF;
1531 IF l_debug_level > 0 THEN
1532 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
1533 END IF;
1534 raise;
1535
1536 END OE_Merge_Lines_History;
1537
1538 /*-------------------------------------------------*/
1539 /*--- PRIVATE PROCEDURE OE_Merge_Lines_IFACE ---*/
1540 /*-------------------------------------------------*/
1541
1542 /* Interface tables need not be updated
1543
1544 Procedure OE_Merge_Lines_IFACE (Req_Id IN NUMBER,
1545 Set_Num IN NUMBER,
1546 Process_Mode IN VARCHAR2)
1547 IS
1548 CURSOR c1 is
1549 select line_id
1550 from oe_lines_iface_all
1551 where ship_to_org_id in
1552 (select m.duplicate_site_id
1553 from ra_customer_merges m
1554 where m.process_flag = 'Y'
1555 and m.request_id = req_id
1556 and m.set_number = set_num)
1557 for update nowait;
1558
1559
1560 CURSOR c2 is
1561 select line_id
1562 from oe_lines_iface_all
1563 where invoice_to_org_id in
1564 (select m.duplicate_site_id
1565 from ra_customer_merges m
1566 where m.process_flag = 'Y'
1567 and m.request_id = req_id
1568 and m.set_number = set_num)
1569 for update nowait;
1570
1571
1572 CURSOR c3 is
1573 select line_id
1574 from oe_lines_iface_all
1575 where deliver_to_org_id in
1576 (select m.duplicate_site_id
1577 from ra_customer_merges m
1578 where m.process_flag = 'Y'
1579 and m.request_id = req_id
1580 and m.set_number = set_num)
1581 for update nowait;
1582
1583
1584 CURSOR c4 is
1585 select line_id
1586 from oe_lines_iface_all
1587 where sold_to_org_id in
1588 (select m.duplicate_id
1589 from ra_customer_merges m
1590 where m.process_flag = 'Y'
1591 and m.request_id = req_id
1592 and m.set_number = set_num)
1593 for update nowait;
1594
1595 BEGIN
1596 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_IFACE()+' );
1597
1598 -- both customer and site level
1599
1600 IF( process_mode = 'LOCK' ) THEN
1601
1602 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1603 -- arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
1604
1605 open c1;
1606 close c1;
1607
1608 open c2;
1609 close c2;
1610
1611 open c3;
1612 close c3;
1613
1614 open c4;
1615 close c4;
1616
1617 ELSE
1618 -- site level update
1619 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1620 -- arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
1621
1622 UPDATE oe_lines_iface_all a
1623 set ship_to_org_id = (select distinct m.customer_site_id
1624 from ra_customer_merges m
1625 where a.ship_to_org_id = m.duplicate_site_id
1626 and m.request_id = req_id
1627 and m.process_flag = 'Y'
1628 and m.set_number = set_num),
1629 last_update_date = sysdate,
1630 last_updated_by = fnd_global.user_id,
1631 last_update_login = fnd_global.login_id,
1632 request_id = req_id,
1633 program_application_id =fnd_global.prog_appl_id,
1634 program_id = fnd_global.conc_program_id,
1635 program_update_date = sysdate
1636 where ship_to_org_id in (select m.duplicate_site_id
1637 from ra_customer_merges m
1638 where m.process_flag = 'Y'
1639 and m.request_id = req_id
1640 and m.set_number = set_num);
1641 g_count := sql%rowcount;
1642 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1643 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1644
1645 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1646 -- arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
1647
1648 UPDATE oe_lines_iface_all a
1649 set invoice_to_org_id = (select distinct m.customer_site_id
1650 from ra_customer_merges m
1651 where a.invoice_to_org_id = m.duplicate_site_id
1652 and m.request_id = req_id
1653 and m.process_flag = 'Y'
1654 and m.set_number = set_num),
1655 last_update_date = sysdate,
1656 last_updated_by = fnd_global.user_id,
1657 last_update_login = fnd_global.login_id,
1658 request_id = req_id,
1659 program_application_id =fnd_global.prog_appl_id,
1660 program_id = fnd_global.conc_program_id,
1661 program_update_date = sysdate
1662 where invoice_to_org_id in (select m.duplicate_site_id
1663 from ra_customer_merges m
1667 g_count := sql%rowcount;
1664 where m.process_flag = 'Y'
1665 and m.request_id = req_id
1666 and m.set_number = set_num);
1668 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1669 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1670
1671 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1672 -- arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
1673
1674 UPDATE oe_lines_iface_all a
1675 set deliver_to_org_id = (select distinct m.customer_site_id
1676 from ra_customer_merges m
1677 where a.deliver_to_org_id = m.duplicate_site_id
1678 and m.request_id = req_id
1679 and m.process_flag = 'Y'
1680 and m.set_number = set_num),
1681 last_update_date = sysdate,
1682 last_updated_by = fnd_global.user_id,
1683 last_update_login = fnd_global.login_id,
1684 request_id = req_id,
1685 program_application_id =fnd_global.prog_appl_id,
1686 program_id = fnd_global.conc_program_id,
1687 program_update_date = sysdate
1688 where deliver_to_org_id in (select m.duplicate_site_id
1689 from ra_customer_merges m
1690 where m.process_flag = 'Y'
1691 and m.request_id = req_id
1692 and m.set_number = set_num);
1693 g_count := sql%rowcount;
1694 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1695 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1696
1697 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1698 -- arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
1699
1700 -- customer level update
1701 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1702 -- arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
1703
1704 UPDATE oe_lines_iface_all a
1705 set sold_to_org_id = (select distinct m.customer_id
1706 from ra_customer_merges m
1707 where a.sold_to_org_id = m.duplicate_id
1708 and m.process_flag = 'Y'
1709 and m.request_id = req_id
1710 and m.set_number = set_num),
1711 last_update_date = sysdate,
1712 last_updated_by = fnd_global.user_id,
1713 last_update_login = fnd_global.login_id,
1714 request_id = req_id,
1715 program_application_id =fnd_global.prog_appl_id,
1716 program_id = fnd_global.conc_program_id,
1717 program_update_date = sysdate
1718 where sold_to_org_id in (select m.duplicate_id
1719 from ra_customer_merges m
1720 where m.process_flag = 'Y'
1721 and m.request_id = req_id
1722 and m.set_number = set_num);
1723
1724 g_count := sql%rowcount;
1725
1726 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1727 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1728
1729 END IF;
1730
1731 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_IFACE()-' );
1732
1733 EXCEPTION
1734 When others then
1735 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_IFACE-' );
1736 raise;
1737
1738 END OE_Merge_Lines_IFACE;
1739
1740 Interface tables need not be updated */
1741
1742 /*-------------------------------------------------*/
1743 /*--- PRIVATE PROCEDURE OE_Merge_Line_ACKS ---*/
1744 /*-------------------------------------------------*/
1745 Procedure OE_Merge_Line_ACKS (Req_Id IN NUMBER,
1746 Set_Num IN NUMBER,
1747 Process_Mode IN VARCHAR2)
1748 IS
1749 CURSOR c1 is
1750 select line_id
1751 from oe_line_acks
1752 where ship_to_org_id in
1753 (select m.duplicate_site_id
1754 from ra_customer_merges m
1755 where m.process_flag = 'Y'
1756 and m.request_id = req_id
1757 and m.set_number = set_num)
1758 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1759 for update nowait;
1760
1761
1762 CURSOR c2 is
1763 select line_id
1764 from oe_line_acks
1765 where invoice_to_org_id in
1766 (select m.duplicate_site_id
1767 from ra_customer_merges m
1768 where m.process_flag = 'Y'
1769 and m.request_id = req_id
1770 and m.set_number = set_num)
1771 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1772 for update nowait;
1773
1774
1775 CURSOR c3 is
1776 select line_id
1777 from oe_line_acks
1778 where deliver_to_org_id in
1779 (select m.duplicate_site_id
1780 from ra_customer_merges m
1781 where m.process_flag = 'Y'
1782 and m.request_id = req_id
1783 and m.set_number = set_num)
1784 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1785 for update nowait;
1786
1787
1788 CURSOR c4 is
1789 select line_id
1790 from oe_line_acks
1791 where intmed_ship_to_org_id in
1792 (select m.duplicate_site_id
1793 from ra_customer_merges m
1794 where m.process_flag = 'Y'
1795 and m.request_id = req_id
1796 and m.set_number = set_num)
1797 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1798 for update nowait;
1799
1800 CURSOR c5 is
1801 select line_id
1802 from oe_line_acks
1803 where sold_to_org_id in
1807 and m.request_id = req_id
1804 (select m.duplicate_id
1805 from ra_customer_merges m
1806 where m.process_flag = 'Y'
1808 and m.set_number = set_num)
1809 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1810 for update nowait;
1811
1812 --
1813 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1814 --
1815 BEGIN
1816 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Line_ACKS()+' );
1817 IF l_debug_level > 0 THEN
1818 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINE_ACKS' ) ;
1819 END IF;
1820
1821 /* both customer and site level */
1822
1823 IF( process_mode = 'LOCK' ) THEN
1824 IF l_debug_level > 0 THEN
1825 oe_debug_pub.add( 'LOCKING TABLE OE_LINE_ACKS' ) ;
1826 END IF;
1827 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1828 -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1829
1830 open c1;
1831 close c1;
1832
1833 open c2;
1834 close c2;
1835
1836 open c3;
1837 close c3;
1838
1839 open c4;
1840 close c4;
1841
1842 open c5;
1843 close c5;
1844
1845 ELSE
1846 /* site level update */
1847 IF l_debug_level > 0 THEN
1848 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
1849 END IF;
1850
1851 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1852 -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1853 IF l_debug_level > 0 THEN
1854 oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.SHIP_TO_ORG_ID' ) ;
1855 END IF;
1856
1857 UPDATE oe_line_acks a
1858 set ship_to_org_id = (select distinct m.customer_site_id
1859 from ra_customer_merges m
1860 where a.ship_to_org_id = m.duplicate_site_id
1861 and m.request_id = req_id
1862 and m.process_flag = 'Y'
1863 and m.set_number = set_num),
1864 last_update_date = sysdate,
1865 last_updated_by = fnd_global.user_id,
1866 last_update_login = fnd_global.login_id,
1867 request_id = req_id,
1868 program_application_id =fnd_global.prog_appl_id,
1869 program_id = fnd_global.conc_program_id,
1870 program_update_date = sysdate
1871 where ship_to_org_id in (select m.duplicate_site_id
1872 from ra_customer_merges m
1873 where m.process_flag = 'Y'
1874 and m.request_id = req_id
1875 and m.set_number = set_num)
1876 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1877 g_count := sql%rowcount;
1878 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1879 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1880 IF l_debug_level > 0 THEN
1881 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1882 END IF;
1883 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1884 -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1885 IF l_debug_level > 0 THEN
1886 oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.INVOICE_TO_ORG_ID' ) ;
1887 END IF;
1888
1889 UPDATE oe_line_acks a
1890 set invoice_to_org_id = (select distinct m.customer_site_id
1891 from ra_customer_merges m
1892 where a.invoice_to_org_id = m.duplicate_site_id
1893 and m.request_id = req_id
1894 and m.process_flag = 'Y'
1895 and m.set_number = set_num),
1896 last_update_date = sysdate,
1897 last_updated_by = fnd_global.user_id,
1898 last_update_login = fnd_global.login_id,
1899 request_id = req_id,
1900 program_application_id =fnd_global.prog_appl_id,
1901 program_id = fnd_global.conc_program_id,
1902 program_update_date = sysdate
1903 where invoice_to_org_id in (select m.duplicate_site_id
1904 from ra_customer_merges m
1905 where m.process_flag = 'Y'
1906 and m.request_id = req_id
1907 and m.set_number = set_num)
1908 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1909 g_count := sql%rowcount;
1910 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1911 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1912 IF l_debug_level > 0 THEN
1913 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1914 END IF;
1915 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1916 -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1917 IF l_debug_level > 0 THEN
1918 oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.DELIVER_TO_ORG_ID' ) ;
1919 END IF;
1920
1921 UPDATE oe_line_acks a
1922 set deliver_to_org_id = (select distinct m.customer_site_id
1923 from ra_customer_merges m
1924 where a.deliver_to_org_id = m.duplicate_site_id
1925 and m.request_id = req_id
1926 and m.process_flag = 'Y'
1927 and m.set_number = set_num),
1928 last_update_date = sysdate,
1929 last_updated_by = fnd_global.user_id,
1930 last_update_login = fnd_global.login_id,
1931 request_id = req_id,
1932 program_application_id =fnd_global.prog_appl_id,
1933 program_id = fnd_global.conc_program_id,
1934 program_update_date = sysdate
1935 where deliver_to_org_id in (select m.duplicate_site_id
1936 from ra_customer_merges m
1937 where m.process_flag = 'Y'
1938 and m.request_id = req_id
1942 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1939 and m.set_number = set_num)
1940 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1941 g_count := sql%rowcount;
1943 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1944 IF l_debug_level > 0 THEN
1945 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1946 END IF;
1947 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1948 -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1949 IF l_debug_level > 0 THEN
1950 oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.INTMED_TO_ORG_ID' ) ;
1951 END IF;
1952
1953 UPDATE oe_line_acks a
1954 set intmed_ship_to_org_id = (select distinct m.customer_site_id
1955 from ra_customer_merges m
1956 where a.intmed_ship_to_org_id = m.duplicate_site_id
1957 and m.request_id = req_id
1958 and m.process_flag = 'Y'
1959 and m.set_number = set_num),
1960 last_update_date = sysdate,
1961 last_updated_by = fnd_global.user_id,
1962 last_update_login = fnd_global.login_id,
1963 request_id = req_id,
1964 program_application_id =fnd_global.prog_appl_id,
1965 program_id = fnd_global.conc_program_id,
1966 program_update_date = sysdate
1967 where intmed_ship_to_org_id in (select m.duplicate_site_id
1968 from ra_customer_merges m
1969 where m.process_flag = 'Y'
1970 and m.request_id = req_id
1971 and m.set_number = set_num)
1972 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1973 g_count := sql%rowcount;
1974 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1975 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1976 IF l_debug_level > 0 THEN
1977 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1978 END IF;
1979 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1980 -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1981
1982
1983 /* customer level update */
1984 IF l_debug_level > 0 THEN
1985 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
1986 END IF;
1987 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1988 -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1989 IF l_debug_level > 0 THEN
1990 oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.SOLD_TO_ORG_ID' ) ;
1991 END IF;
1992
1993 UPDATE oe_line_acks a
1994 set sold_to_org_id = (select distinct m.customer_id
1995 from ra_customer_merges m
1996 where a.sold_to_org_id = m.duplicate_id
1997 and m.process_flag = 'Y'
1998 and m.request_id = req_id
1999 and m.set_number = set_num),
2000 last_update_date = sysdate,
2001 last_updated_by = fnd_global.user_id,
2002 last_update_login = fnd_global.login_id,
2003 request_id = req_id,
2004 program_application_id =fnd_global.prog_appl_id,
2005 program_id = fnd_global.conc_program_id,
2006 program_update_date = sysdate
2007 where sold_to_org_id in (select m.duplicate_id
2008 from ra_customer_merges m
2009 where m.process_flag = 'Y'
2010 and m.request_id = req_id
2011 and m.set_number = set_num)
2012 and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
2013
2014 g_count := sql%rowcount;
2015
2016 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2017 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2018 IF l_debug_level > 0 THEN
2019 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2020 END IF;
2021
2022 END IF;
2023
2024 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Line_ACKS()-' );
2025 IF l_debug_level > 0 THEN
2026 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINE_ACKS' ) ;
2027 END IF;
2028
2029 EXCEPTION
2030 When others then
2031 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Line_ACKS-' );
2032 IF l_debug_level > 0 THEN
2033 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINE_ACKS' ) ;
2034 END IF;
2035 IF l_debug_level > 0 THEN
2036 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2037 END IF;
2038 raise;
2039
2040 END OE_Merge_Line_ACKS;
2041 -----------------------------------------------------------------
2042 --
2043 -- MAIN PROCEDURE
2044 --
2045 -- Procedure Name: Merge
2046 -- Parameter: Req_id, Set_Num, Process_Mode
2047 --
2048 -- This is the main procedure to do customer merge for ONT product.
2049 -- This procedure will call other internal procedures to process
2050 -- the merging based on the functional areas. Please see the HLD for
2051 -- Customer Merge for detail information (cmerge_hld.rtf).
2052 --
2053 --------------------------------------------------------------------
2054
2055 Procedure Merge (Req_Id IN NUMBER,
2056 Set_Num IN NUMBER,
2057 Process_Mode IN VARCHAR2)
2058 IS
2059
2060 --
2061 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2062 --
2063 BEGIN
2064
2065 /* this part will be calling other internal procedures */
2066 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Merg()+' );
2067 IF l_debug_level > 0 THEN
2068 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.MERGE' ) ;
2069 END IF;
2070
2074 OE_CUST_MERGE_DATA_FIX.OE_Constraints_Merge (Req_Id, Set_Num, Process_Mode);
2071 OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge (Req_Id, Set_Num, Process_Mode);
2072 OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge (Req_Id, Set_Num, Process_Mode);
2073 OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge (Req_Id, Set_Num, Process_Mode);
2075 OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge (Req_Id, Set_Num, Process_Mode);
2076 -- OE_CUST_MERGE_DATA_FIX.OE_Drop_Ship_Merge (Req_Id, Set_Num, Process_Mode);
2077 OE_CUST_MERGE_DATA_FIX.OE_Ship_Tolerance_Merge (Req_Id, Set_Num, Process_Mode);
2078 OE_CUST_MERGE_DATA_FIX.OE_Order_Merge (Req_Id, Set_Num, Process_Mode);
2079
2080 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Merg()-' );
2081 IF l_debug_level > 0 THEN
2082 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.MERGE' ) ;
2083 END IF;
2084
2085 EXCEPTION
2086 When others then
2087 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.Merg-' );
2088 IF l_debug_level > 0 THEN
2089 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.MERGE' ) ;
2090 END IF;
2091 IF l_debug_level > 0 THEN
2092 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2093 END IF;
2094 raise;
2095
2096
2097 END Merge;
2098
2099
2100 Procedure OE_Attachment_Merge(Req_Id IN NUMBER,
2101 Set_Num IN NUMBER,
2102 Process_Mode IN VARCHAR2)
2103 IS
2104 CURSOR c1 is
2105 select RULE_ELEMENT_ID
2106 from oe_attachment_rule_elements
2107 where attribute_value in (select to_char(m.duplicate_site_id)
2108 from ra_customer_merges m
2109 where m.process_flag = 'Y'
2110 and m.request_id = req_id
2111 and m.set_number = set_num)
2112 and attribute_code = 'SHIP_TO_ORG_ID'
2113 for update nowait;
2114
2115 CURSOR c2 is
2116 select RULE_ELEMENT_ID
2117 from oe_attachment_rule_elements
2118 where attribute_value in (select to_char(m.duplicate_site_id)
2119 from ra_customer_merges m
2120 where m.process_flag = 'Y'
2121 and m.request_id = req_id
2122 and m.set_number = set_num)
2123 and attribute_code = 'INVOICE_TO_ORG_ID'
2124 for update nowait;
2125
2126 CURSOR c3 is
2127 select RULE_ELEMENT_ID
2128 from oe_attachment_rule_elements
2129 where attribute_value in (select to_char(m.duplicate_id)
2130 from ra_customer_merges m
2131 where m.process_flag = 'Y'
2132 and m.request_id = req_id
2133 and m.set_number = set_num)
2134 and attribute_code = 'SOLD_TO_ORG_ID'
2135 for update nowait;
2136
2137
2138 --
2139 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2140 --
2141 BEGIN
2142 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge()+' );
2143 IF l_debug_level > 0 THEN
2144 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_ATTACHMENT_MERGE' ) ;
2145 END IF;
2146
2147 /*-----------------------------+
2148 | OE_ATTACHMENTS_RULE_ELEMENTS|
2149 +-----------------------------*/
2150 /* both customer and site level */
2151
2152 IF( process_mode = 'LOCK' ) THEN
2153 IF l_debug_level > 0 THEN
2154 oe_debug_pub.add( 'LOCKING TABLE OE_ATTACHMENT_RULE_ELEMENTS' ) ;
2155 END IF;
2156 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2157 -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2158
2159 open c1;
2160 close c1;
2161
2162 open c2;
2163 close c2;
2164
2165 open c3;
2166 close c3;
2167
2168
2169 ELSE
2170
2171
2172 /* site level update */
2173 IF l_debug_level > 0 THEN
2174 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2175 END IF;
2176
2177 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2178 -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2179 IF l_debug_level > 0 THEN
2180 oe_debug_pub.add( 'UPDATING OE_ATTACHMENT_RULE_ELEMENTS.ATTRIBUTE_VALUE FOR ATTRIBUTE_CODE SHIP_TO_ORG_ID' ) ;
2181 END IF;
2182 UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
2183 set (attribute_value) = (select distinct to_char(m.customer_site_id)
2184 from ra_customer_merges m
2185 where a.attribute_value =
2186 to_char(m.duplicate_site_id)
2187 and m.request_id = req_id
2188 and m.process_flag = 'Y'
2189 and m.set_number = set_num),
2190 last_update_date = sysdate,
2191 last_updated_by = fnd_global.user_id,
2192 last_update_login = fnd_global.login_id
2193 where attribute_value in (select to_char(m.duplicate_site_id)
2194 from ra_customer_merges m
2195 where m.process_flag = 'Y'
2196 and m.request_id = req_id
2197 and m.set_number = set_num)
2198 and attribute_code = 'SHIP_TO_ORG_ID';
2199
2200 g_count := sql%rowcount;
2201
2202 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2203 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2204 IF l_debug_level > 0 THEN
2205 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2206 END IF;
2207
2208 /* site level update */
2209 IF l_debug_level > 0 THEN
2210 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2211 END IF;
2212
2216 oe_debug_pub.add( 'UPDATING OE_ATTACHMENT_RULE_ELEMENTS.ATTRIBUTE_VALUE FOR ATTRIBUTE_CODE INVOICE_TO_ORG_ID' ) ;
2213 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2214 -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2215 IF l_debug_level > 0 THEN
2217 END IF;
2218 UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
2219 set (attribute_value) = (select distinct to_char(m.customer_site_id)
2220 from ra_customer_merges m
2221 where a.attribute_value =
2222 to_char(m.duplicate_site_id)
2223 and m.request_id = req_id
2224 and m.process_flag = 'Y'
2225 and m.set_number = set_num),
2226 last_update_date = sysdate,
2227 last_updated_by = fnd_global.user_id,
2228 last_update_login = fnd_global.login_id
2229 where attribute_value in (select to_char(m.duplicate_site_id)
2230 from ra_customer_merges m
2231 where m.process_flag = 'Y'
2232 and m.request_id = req_id
2233 and m.set_number = set_num)
2234 and attribute_code = 'INVOICE_TO_ORG_ID';
2235
2236 g_count := sql%rowcount;
2237
2238 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2239 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2240 IF l_debug_level > 0 THEN
2241 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2242 END IF;
2243
2244
2245 /* customer level update */
2246 IF l_debug_level > 0 THEN
2247 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2248 END IF;
2249 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2250 -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2251 IF l_debug_level > 0 THEN
2252 oe_debug_pub.add( 'UPDATING OE_ATTACHMENT_RULE_ELEMENTS.ATTRIBUTE_VALUE FOR ATTRIBUTE_CODE SOLD_TO_ORG_ID' ) ;
2253 END IF;
2254
2255 UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
2256 set (attribute_value) = (select distinct to_char(m.customer_id)
2257 from ra_customer_merges m
2258 where a.attribute_value =
2259 to_char(m.duplicate_id)
2260 and m.request_id = req_id
2261 and m.process_flag = 'Y'
2262 and m.set_number = set_num),
2263 last_update_date = sysdate,
2264 last_updated_by = fnd_global.user_id,
2265 last_update_login = fnd_global.login_id
2266 where attribute_value in (select to_char(m.duplicate_id)
2267 from ra_customer_merges m
2268 where m.process_flag = 'Y'
2269 and m.request_id = req_id
2270 and m.set_number = set_num)
2271 and attribute_code = 'SOLD_TO_ORG_ID';
2272
2273
2274 g_count := sql%rowcount;
2275
2276 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2277 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2278 IF l_debug_level > 0 THEN
2279 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2280 END IF;
2281
2282 END IF;
2283
2284 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge()-' );
2285 IF l_debug_level > 0 THEN
2286 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_ATTACHMENT_MERGE' ) ;
2287 END IF;
2288
2289
2290 EXCEPTION
2291 when others then
2292 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge' );
2293 IF l_debug_level > 0 THEN
2294 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_ATTACHMENT_MERGE' ) ;
2295 END IF;
2296 IF l_debug_level > 0 THEN
2297 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2298 END IF;
2299 raise;
2300
2301
2302 END OE_Attachment_Merge;
2303
2304
2305 Procedure OE_Defaulting_Merge (Req_Id IN NUMBER,
2306 Set_Num IN NUMBER,
2307 Process_Mode IN VARCHAR2)
2308 IS
2309 CURSOR c1 is
2310 select CONDITION_ELEMENT_ID
2311 from oe_def_condn_elems
2312 where value_string in (select to_char(m.duplicate_site_id)
2313 from ra_customer_merges m
2314 where m.process_flag = 'Y'
2315 and m.request_id = req_id
2316 and m.set_number = set_num)
2317 and attribute_code = 'SHIP_TO_ORG_ID'
2318 for update nowait;
2319
2320 CURSOR c2 is
2321 select CONDITION_ELEMENT_ID
2322 from oe_def_condn_elems
2323 where value_string in (select to_char(m.duplicate_site_id)
2324 from ra_customer_merges m
2325 where m.process_flag = 'Y'
2326 and m.request_id = req_id
2327 and m.set_number = set_num)
2328 and attribute_code = 'INVOICE_TO_ORG_ID'
2329 for update nowait;
2330
2331 CURSOR c4 is
2332 select CONDITION_ELEMENT_ID
2333 from oe_def_condn_elems
2334 where value_string in (select to_char(m.duplicate_site_id)
2335 from ra_customer_merges m
2336 where m.process_flag = 'Y'
2337 and m.request_id = req_id
2338 and m.set_number = set_num)
2339 and attribute_code = 'INTMED_SHIP_TO_ORG_ID'
2340 for update nowait;
2341
2342 CURSOR c3 is
2343 select CONDITION_ELEMENT_ID
2344 from oe_def_condn_elems
2348 and m.request_id = req_id
2345 where value_string in (select to_char(m.duplicate_id)
2346 from ra_customer_merges m
2347 where m.process_flag = 'Y'
2349 and m.set_number = set_num)
2350 and attribute_code = 'SOLD_TO_ORG_ID'
2351 for update nowait;
2352
2353 CURSOR c5 is
2354 select ATTR_DEF_RULE_ID
2355 from oe_def_attr_def_rules
2356 where src_constant_value in (select to_char(m.duplicate_site_id)
2357 from ra_customer_merges m
2358 where m.process_flag = 'Y'
2359 and m.request_id = req_id
2360 and m.set_number = set_num)
2361 and attribute_code = 'SHIP_TO_ORG_ID'
2362 for update nowait;
2363
2364 CURSOR c6 is
2365 select ATTR_DEF_RULE_ID
2366 from oe_def_attr_def_rules
2367 where src_constant_value in (select to_char(m.duplicate_site_id)
2368 from ra_customer_merges m
2369 where m.process_flag = 'Y'
2370 and m.request_id = req_id
2371 and m.set_number = set_num)
2372 and attribute_code = 'INVOICE_TO_ORG_ID'
2373 for update nowait;
2374
2375 CURSOR c7 is
2376 select ATTR_DEF_RULE_ID
2377 from oe_def_attr_def_rules
2378 where src_constant_value in (select to_char(m.duplicate_site_id)
2379 from ra_customer_merges m
2380 where m.process_flag = 'Y'
2381 and m.request_id = req_id
2382 and m.set_number = set_num)
2383 and attribute_code = 'INTMED_SHIP_TO_ORG_ID'
2384 for update nowait;
2385
2386 CURSOR c8 is
2387 select ATTR_DEF_RULE_ID
2388 from oe_def_attr_def_rules
2389 where src_constant_value in (select to_char(m.duplicate_id)
2390 from ra_customer_merges m
2391 where m.process_flag = 'Y'
2392 and m.request_id = req_id
2393 and m.set_number = set_num)
2394 and attribute_code = 'SOLD_TO_ORG_ID'
2395 for update nowait;
2396
2397 --
2398 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2399 --
2400 BEGIN
2401 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge()+' );
2402 IF l_debug_level > 0 THEN
2403 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_DEFAULTING_MERGE' ) ;
2404 END IF;
2405
2406 /*-----------------------------+
2407 | OE_DEF_CONDN_ELEMS|
2408 +-----------------------------*/
2409 /* both customer and site level */
2410
2411 IF( process_mode = 'LOCK' ) THEN
2412 IF l_debug_level > 0 THEN
2413 oe_debug_pub.add( 'LOCKING TABLE OE_DEF_CONDN_ELEMENTS' ) ;
2414 END IF;
2415 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2416 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2417
2418
2419 open c1;
2420 close c1;
2421
2422 open c2;
2423 close c2;
2424
2425 open c3;
2426 close c3;
2427
2428 open c4;
2429 close c4;
2430
2431 open c5;
2432 close c5;
2433
2434 open c6;
2435 close c6;
2436
2437 open c7;
2438 close c7;
2439
2440 open c8;
2441 close c8;
2442
2443
2444 ELSE
2445
2446
2447 /* site level update */
2448 IF l_debug_level > 0 THEN
2449 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2450 END IF;
2451
2452 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2453 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2454 IF l_debug_level > 0 THEN
2455 oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE SHIP_TO_ORG_ID' ) ;
2456 END IF;
2457 UPDATE OE_DEF_CONDN_ELEMS a
2458 set value_string = (select distinct to_char(m.customer_site_id)
2459 from ra_customer_merges m
2460 where a.value_string =
2461 to_char(m.duplicate_site_id)
2462
2463 and m.request_id = req_id
2464 and m.process_flag = 'Y'
2465 and m.set_number = set_num),
2466 last_update_date = sysdate,
2467 last_updated_by = fnd_global.user_id,
2468 last_update_login = fnd_global.login_id
2469 where value_string in (select to_char(m.duplicate_site_id)
2470 from ra_customer_merges m
2471 where m.process_flag = 'Y'
2472 and m.request_id = req_id
2473 and m.set_number = set_num)
2474 and attribute_code = 'SHIP_TO_ORG_ID';
2475
2476 g_count := sql%rowcount;
2477
2478 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2479 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2480 IF l_debug_level > 0 THEN
2481 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2482 END IF;
2483
2484 /* site level update */
2485 IF l_debug_level > 0 THEN
2486 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2487 END IF;
2488 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2489 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2490 IF l_debug_level > 0 THEN
2491 oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE INVOICE_TO_ORG_ID' ) ;
2492 END IF;
2496 where a.value_string =
2493 UPDATE OE_DEF_CONDN_ELEMS a
2494 set value_string = (select distinct to_char(m.customer_site_id)
2495 from ra_customer_merges m
2497 to_char(m.duplicate_site_id)
2498
2499 and m.request_id = req_id
2500 and m.process_flag = 'Y'
2501 and m.set_number = set_num),
2502 last_update_date = sysdate,
2503 last_updated_by = fnd_global.user_id,
2504 last_update_login = fnd_global.login_id
2505 where value_string in (select to_char(m.duplicate_site_id)
2506 from ra_customer_merges m
2507 where m.process_flag = 'Y'
2508 and m.request_id = req_id
2509 and m.set_number = set_num)
2510 and attribute_code = 'INVOICE_TO_ORG_ID';
2511
2512 g_count := sql%rowcount;
2513
2514 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2515 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2516 IF l_debug_level > 0 THEN
2517 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2518 END IF;
2519
2520 /* site level update */
2521 IF l_debug_level > 0 THEN
2522 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2523 END IF;
2524 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2525 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2526 IF l_debug_level > 0 THEN
2527 oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE INTMED_TO_ORG_ID' ) ;
2528 END IF;
2529 UPDATE OE_DEF_CONDN_ELEMS a
2530 set value_string = (select distinct to_char(m.customer_site_id)
2531 from ra_customer_merges m
2532 where a.value_string =
2533 to_char(m.duplicate_site_id)
2534
2535 and m.request_id = req_id
2536 and m.process_flag = 'Y'
2537 and m.set_number = set_num),
2538 last_update_date = sysdate,
2539 last_updated_by = fnd_global.user_id,
2540 last_update_login = fnd_global.login_id
2541 where value_string in (select to_char(m.duplicate_site_id)
2542 from ra_customer_merges m
2543 where m.process_flag = 'Y'
2544 and m.request_id = req_id
2545 and m.set_number = set_num)
2546 and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
2547
2548 g_count := sql%rowcount;
2549
2550 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2551 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2552 IF l_debug_level > 0 THEN
2553 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2554 END IF;
2555
2556 /* customer level update */
2557 IF l_debug_level > 0 THEN
2558 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2559 END IF;
2560 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2561 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2562 IF l_debug_level > 0 THEN
2563 oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE SOLD_TO_ORG_ID' ) ;
2564 END IF;
2565
2566 UPDATE OE_DEF_CONDN_ELEMS a
2567 set value_string = (select distinct to_char(m.customer_id)
2568 from ra_customer_merges m
2569 where a.value_string =
2570 to_char(m.duplicate_id)
2571
2572 and m.request_id = req_id
2573 and m.process_flag = 'Y'
2574 and m.set_number = set_num),
2575 last_update_date = sysdate,
2576 last_updated_by = fnd_global.user_id,
2577 last_update_login = fnd_global.login_id
2578 where value_string in (select to_char(m.duplicate_id)
2579 from ra_customer_merges m
2580 where m.process_flag = 'Y'
2581 and m.request_id = req_id
2582 and m.set_number = set_num)
2583 and attribute_code = 'SOLD_TO_ORG_ID';
2584
2585
2586 g_count := sql%rowcount;
2587
2588 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2589 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2590 IF l_debug_level > 0 THEN
2591 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2592 END IF;
2593
2594 /* site level update */
2595 IF l_debug_level > 0 THEN
2596 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2597 END IF;
2598 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2599 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2600
2601 IF l_debug_level > 0 THEN
2602 oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE SHIP_TO_ORG_ID' ) ;
2603 END IF;
2604
2605 UPDATE OE_DEF_ATTR_DEF_RULES a
2606 set src_constant_value = (select distinct to_char(m.customer_site_id)
2607 from ra_customer_merges m
2608 where a.src_constant_value =
2609 to_char(m.duplicate_site_id)
2610
2611 and m.request_id = req_id
2612 and m.process_flag = 'Y'
2613 and m.set_number = set_num),
2614 last_update_date = sysdate,
2615 last_updated_by = fnd_global.user_id,
2616 last_update_login = fnd_global.login_id
2620 and m.request_id = req_id
2617 where src_constant_value in (select to_char(m.duplicate_site_id)
2618 from ra_customer_merges m
2619 where m.process_flag = 'Y'
2621 and m.set_number = set_num)
2622 and attribute_code = 'SHIP_TO_ORG_ID';
2623
2624 g_count := sql%rowcount;
2625
2626 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2627 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2628 IF l_debug_level > 0 THEN
2629 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2630 END IF;
2631
2632 /* site level update */
2633 IF l_debug_level > 0 THEN
2634 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2635 END IF;
2636 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2637 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2638 IF l_debug_level > 0 THEN
2639 oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE INVOICE_TO_ORG_ID' ) ;
2640 END IF;
2641
2642 UPDATE OE_DEF_ATTR_DEF_RULES a
2643 set src_constant_value = (select distinct to_char(m.customer_site_id)
2644 from ra_customer_merges m
2645 where a.src_constant_value =
2646 to_char(m.duplicate_site_id)
2647
2648 and m.request_id = req_id
2649 and m.process_flag = 'Y'
2650 and m.set_number = set_num),
2651 last_update_date = sysdate,
2652 last_updated_by = fnd_global.user_id,
2653 last_update_login = fnd_global.login_id
2654 where src_constant_value in (select to_char(m.duplicate_site_id)
2655 from ra_customer_merges m
2656 where m.process_flag = 'Y'
2657 and m.request_id = req_id
2658 and m.set_number = set_num)
2659 and attribute_code = 'INVOICE_TO_ORG_ID';
2660
2661 g_count := sql%rowcount;
2662
2663 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2664 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2665 IF l_debug_level > 0 THEN
2666 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2667 END IF;
2668
2669 /* site level update */
2670 IF l_debug_level > 0 THEN
2671 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2672 END IF;
2673 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2674 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2675 IF l_debug_level > 0 THEN
2676 oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE INTMED_TO_ORG_ID' ) ;
2677 END IF;
2678
2679 UPDATE OE_DEF_ATTR_DEF_RULES a
2680 set src_constant_value = (select distinct to_char(m.customer_site_id)
2681 from ra_customer_merges m
2682 where a.src_constant_value =
2683 to_char(m.duplicate_site_id)
2684
2685 and m.request_id = req_id
2686 and m.process_flag = 'Y'
2687 and m.set_number = set_num),
2688 last_update_date = sysdate,
2689 last_updated_by = fnd_global.user_id,
2690 last_update_login = fnd_global.login_id
2691 where src_constant_value in (select to_char(m.duplicate_site_id)
2692 from ra_customer_merges m
2693 where m.process_flag = 'Y'
2694 and m.request_id = req_id
2695 and m.set_number = set_num)
2696 and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
2697
2698 g_count := sql%rowcount;
2699
2700 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2701 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2702 IF l_debug_level > 0 THEN
2703 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2704 END IF;
2705
2706 /* customer level update */
2707 IF l_debug_level > 0 THEN
2708 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2709 END IF;
2710 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2711 -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2712 IF l_debug_level > 0 THEN
2713 oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE SOLD_TO_ORG_ID' ) ;
2714 END IF;
2715
2716 UPDATE OE_DEF_ATTR_DEF_RULES a
2717 set src_constant_value = (select distinct to_char(m.customer_id)
2718 from ra_customer_merges m
2719 where a.src_constant_value =
2720 to_char(m.duplicate_id)
2721
2722 and m.request_id = req_id
2723 and m.process_flag = 'Y'
2724 and m.set_number = set_num),
2725 last_update_date = sysdate,
2726 last_updated_by = fnd_global.user_id,
2727 last_update_login = fnd_global.login_id
2728 where src_constant_value in (select to_char(m.duplicate_id)
2729 from ra_customer_merges m
2730 where m.process_flag = 'Y'
2731 and m.request_id = req_id
2732 and m.set_number = set_num)
2733 and attribute_code = 'SOLD_TO_ORG_ID';
2734
2735
2736 g_count := sql%rowcount;
2737
2738 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2739 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2740 IF l_debug_level > 0 THEN
2744
2741 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2742 END IF;
2743
2745
2746 END IF;
2747
2748 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge()-' );
2749 IF l_debug_level > 0 THEN
2750 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_DEFAULTING_MERGE' ) ;
2751 END IF;
2752
2753
2754 EXCEPTION
2755 when others then
2756 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge' );
2757 IF l_debug_level > 0 THEN
2758 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_DEFAULTING_MERGE' ) ;
2759 END IF;
2760 IF l_debug_level > 0 THEN
2761 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2762 END IF;
2763 raise;
2764
2765
2766
2767 END OE_Defaulting_Merge;
2768
2769 Procedure OE_Constraints_Merge (Req_Id IN NUMBER,
2770 Set_Num IN NUMBER,
2771 Process_Mode IN VARCHAR2)
2772 IS
2773 CURSOR c1 is
2774 select VALIDATION_TMPLT_ID
2775 from oe_pc_vtmplt_cols
2776 where value_string in (select to_char(m.duplicate_site_id)
2777 from ra_customer_merges m
2778 where m.process_flag = 'Y'
2779 and m.request_id = req_id
2780 and m.set_number = set_num)
2781 and column_name = 'SHIP_TO_ORG_ID'
2782 for update nowait;
2783
2784 CURSOR c2 is
2785 select VALIDATION_TMPLT_ID
2786 from oe_pc_vtmplt_cols
2787 where value_string in (select to_char(m.duplicate_site_id)
2788 from ra_customer_merges m
2789 where m.process_flag = 'Y'
2790 and m.request_id = req_id
2791 and m.set_number = set_num)
2792 and column_name = 'INVOICE_TO_ORG_ID'
2793 for update nowait;
2794
2795 CURSOR c4 is
2796 select VALIDATION_TMPLT_ID
2797 from oe_pc_vtmplt_cols
2798 where value_string in (select to_char(m.duplicate_site_id)
2799 from ra_customer_merges m
2800 where m.process_flag = 'Y'
2801 and m.request_id = req_id
2802 and m.set_number = set_num)
2803 and column_name = 'INTMED_SHIP_TO_ORG_ID'
2804 for update nowait;
2805
2806 CURSOR c3 is
2807 select VALIDATION_TMPLT_ID
2808 from oe_pc_vtmplt_cols
2809 where value_string in (select to_char(m.duplicate_id)
2810 from ra_customer_merges m
2811 where m.process_flag = 'Y'
2812 and m.request_id = req_id
2813 and m.set_number = set_num)
2814 and column_name = 'SOLD_TO_ORG_ID'
2815 for update nowait;
2816
2817
2818 --
2819 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2820 --
2821 BEGIN
2822 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Constraints_Merge()+' );
2823 IF l_debug_level > 0 THEN
2824 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_CONSTRAINTS_MERGE' ) ;
2825 END IF;
2826
2827 /*-----------------------------+
2828 | oe_pc_vtmplt_cols|
2829 +-----------------------------*/
2830 /* both customer and site level */
2831
2832 IF( process_mode = 'LOCK' ) THEN
2833
2834 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2835 -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2836 IF l_debug_level > 0 THEN
2837 oe_debug_pub.add( 'LOCKING TABLE OE_PC_VTMPLT_COLS' ) ;
2838 END IF;
2839
2840 open c1;
2841 close c1;
2842
2843 open c2;
2844 close c2;
2845
2846 open c3;
2847 close c3;
2848
2849 open c4;
2850 close c4;
2851
2852
2853 ELSE
2854
2855
2856 /* site level update */
2857 IF l_debug_level > 0 THEN
2858 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2859 END IF;
2860 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2861 -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2862 IF l_debug_level > 0 THEN
2863 oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME SHIP_TO_ORG_ID' ) ;
2864 END IF;
2865
2866 UPDATE OE_PC_VTMPLT_COLS a
2867 set value_string = (select distinct to_char(m.customer_site_id)
2868 from ra_customer_merges m
2869 where a.value_string =
2870 to_char(m.duplicate_site_id)
2871
2872 and m.request_id = req_id
2873 and m.process_flag = 'Y'
2874 and m.set_number = set_num),
2875 last_update_date = sysdate,
2876 last_updated_by = fnd_global.user_id,
2877 last_update_login = fnd_global.login_id
2878 where value_string in (select to_char(m.duplicate_site_id)
2879 from ra_customer_merges m
2880 where m.process_flag = 'Y'
2881 and m.request_id = req_id
2882 and m.set_number = set_num)
2883 and column_name = 'SHIP_TO_ORG_ID';
2884
2885 g_count := sql%rowcount;
2886
2887 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2888 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2889 IF l_debug_level > 0 THEN
2890 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2891 END IF;
2892
2893 /* site level update */
2897 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2894 IF l_debug_level > 0 THEN
2895 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2896 END IF;
2898 -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2899 IF l_debug_level > 0 THEN
2900 oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME INVOICE_TO_ORG_ID' ) ;
2901 END IF;
2902
2903 UPDATE OE_PC_VTMPLT_COLS a
2904 set value_string = (select distinct to_char(m.customer_site_id)
2905 from ra_customer_merges m
2906 where a.value_string =
2907 to_char(m.duplicate_site_id)
2908
2909 and m.request_id = req_id
2910 and m.process_flag = 'Y'
2911 and m.set_number = set_num),
2912 last_update_date = sysdate,
2913 last_updated_by = fnd_global.user_id,
2914 last_update_login = fnd_global.login_id
2915 where value_string in (select to_char(m.duplicate_site_id)
2916 from ra_customer_merges m
2917 where m.process_flag = 'Y'
2918 and m.request_id = req_id
2919 and m.set_number = set_num)
2920 and column_name = 'INVOICE_TO_ORG_ID';
2921
2922 g_count := sql%rowcount;
2923
2924 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2925 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2926 IF l_debug_level > 0 THEN
2927 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2928 END IF;
2929
2930 /* site level update */
2931 IF l_debug_level > 0 THEN
2932 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2933 END IF;
2934 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2935 -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2936 IF l_debug_level > 0 THEN
2937 oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME INTMED_TO_ORG_ID' ) ;
2938 END IF;
2939
2940 UPDATE OE_PC_VTMPLT_COLS a
2941 set value_string = (select distinct to_char(m.customer_site_id)
2942 from ra_customer_merges m
2943 where a.value_string =
2944 to_char(m.duplicate_site_id)
2945
2946 and m.request_id = req_id
2947 and m.process_flag = 'Y'
2948 and m.set_number = set_num),
2949 last_update_date = sysdate,
2950 last_updated_by = fnd_global.user_id,
2951 last_update_login = fnd_global.login_id
2952 where value_string in (select to_char(m.duplicate_site_id)
2953 from ra_customer_merges m
2954 where m.process_flag = 'Y'
2955 and m.request_id = req_id
2956 and m.set_number = set_num)
2957 and column_name = 'INTMED_SHIP_TO_ORG_ID';
2958
2959 g_count := sql%rowcount;
2960
2961 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2962 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2963 IF l_debug_level > 0 THEN
2964 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2965 END IF;
2966
2967
2968 /* customer level update */
2969 IF l_debug_level > 0 THEN
2970 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2971 END IF;
2972 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2973 -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2974 IF l_debug_level > 0 THEN
2975 oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME SOLD_TO_ORG_ID' ) ;
2976 END IF;
2977
2978 UPDATE OE_PC_VTMPLT_COLS a
2979 set value_string = (select distinct to_char(m.customer_id)
2980 from ra_customer_merges m
2981 where a.value_string =
2982 to_char(m.duplicate_id)
2983
2984 and m.request_id = req_id
2985 and m.process_flag = 'Y'
2986 and m.set_number = set_num),
2987 last_update_date = sysdate,
2988 last_updated_by = fnd_global.user_id,
2989 last_update_login = fnd_global.login_id
2990 where value_string in (select to_char(m.duplicate_id)
2991 from ra_customer_merges m
2992 where m.process_flag = 'Y'
2993 and m.request_id = req_id
2994 and m.set_number = set_num)
2995 and column_name = 'SOLD_TO_ORG_ID';
2996
2997
2998 g_count := sql%rowcount;
2999
3000 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3001 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3002 IF l_debug_level > 0 THEN
3003 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3004 END IF;
3005
3006 END IF;
3007
3008 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Constraints_Merge()-' );
3009 IF l_debug_level > 0 THEN
3010 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_CONSTRAINTS_MERGE' ) ;
3011 END IF;
3012
3013
3014 EXCEPTION
3015 when others then
3016 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Constraints_Merge' );
3017 IF l_debug_level > 0 THEN
3018 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_CONSTRAINTS_MERGE' ) ;
3019 END IF;
3020 IF l_debug_level > 0 THEN
3021 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3022 END IF;
3023 raise;
3024
3028
3025 END OE_Constraints_Merge;
3026
3027
3029
3030 Procedure OE_Hold_Merge (Req_Id IN NUMBER,
3031 Set_Num IN NUMBER,
3032 Process_Mode IN VARCHAR2)
3033 IS
3034 CURSOR c1 is
3035 select hold_source_id
3036 from oe_hold_sources
3037 where hold_entity_id in (select m.duplicate_site_id
3038 from ra_customer_merges m
3039 where m.process_flag = 'Y'
3040 and m.request_id = req_id
3041 and m.set_number = set_num)
3042 and hold_entity_code = 'S'
3043 for update nowait;
3044
3045 CURSOR c2 is
3046 select hold_source_id
3047 from oe_hold_sources
3048 where hold_entity_id in (select m.duplicate_site_id
3049 from ra_customer_merges m
3050 where m.process_flag = 'Y'
3051 and m.request_id = req_id
3052 and m.set_number = set_num)
3053 and hold_entity_code = 'B'
3054 for update nowait;
3055
3056
3057 CURSOR c3 is
3058 select hold_source_id
3059 from oe_hold_sources
3060 where hold_entity_id in (select m.duplicate_id
3061 from ra_customer_merges m
3062 where m.process_flag = 'Y'
3063 and m.request_id = req_id
3064 and m.set_number = set_num)
3065 and hold_entity_code = 'C'
3066 for update nowait;
3067
3068
3069
3070 --
3071 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3072 --
3073 BEGIN
3074
3075 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge()+' );
3076 IF l_debug_level > 0 THEN
3077 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3078 END IF;
3079 /*-----------------------------+
3080 | OE_HOLD_SOURCES |
3081 +-----------------------------*/
3082 /* both customer and site level */
3083
3084 IF( process_mode = 'LOCK' ) THEN
3085 IF l_debug_level > 0 THEN
3086 oe_debug_pub.add( 'LOCKING TABLE OE_HOLD_SOURCES' ) ;
3087 END IF;
3088 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3089 -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3090
3091 open c1;
3092 close c1;
3093
3094 open c2;
3095 close c2;
3096
3097 open c3;
3098 close c3;
3099
3100 ELSE
3101
3102
3103 /* site level update */
3104 IF l_debug_level > 0 THEN
3105 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3106 END IF;
3107 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3108 -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3109 IF l_debug_level > 0 THEN
3110 oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE S' ) ;
3111 END IF;
3112
3113 UPDATE OE_HOLD_SOURCES a
3114 set (hold_entity_id) = (select distinct m.customer_site_id
3115 from ra_customer_merges m
3116 where a.hold_entity_id =
3117 m.duplicate_site_id
3118 and m.request_id = req_id
3119 and m.process_flag = 'Y'
3120 and m.set_number = set_num),
3121 last_update_date = sysdate,
3122
3123 last_updated_by = fnd_global.user_id,
3124 last_update_login = fnd_global.login_id
3125 where hold_entity_id in (select m.duplicate_site_id
3126 from ra_customer_merges m
3127 where m.process_flag = 'Y'
3128 and m.request_id = req_id
3129 and m.set_number = set_num)
3130 and hold_entity_code = 'S';
3131
3132 g_count := sql%rowcount;
3133
3134 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3135 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3136 IF l_debug_level > 0 THEN
3137 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3138 END IF;
3139
3140
3141 /* site level update */
3142 IF l_debug_level > 0 THEN
3143 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3144 END IF;
3145 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3146 -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3147 IF l_debug_level > 0 THEN
3148 oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE B' ) ;
3149 END IF;
3150
3151 UPDATE OE_HOLD_SOURCES a
3152 set (hold_entity_id) = (select distinct m.customer_site_id
3153 from ra_customer_merges m
3154 where a.hold_entity_id =
3155
3156 m.duplicate_site_id
3157 and m.request_id = req_id
3158 and m.process_flag = 'Y'
3159 and m.set_number = set_num),
3160 last_update_date = sysdate,
3161 last_updated_by = fnd_global.user_id,
3162 last_update_login = fnd_global.login_id
3163 where hold_entity_id in (select m.duplicate_site_id
3164 from ra_customer_merges m
3165 where m.process_flag = 'Y'
3166 and m.request_id = req_id
3167 and m.set_number = set_num)
3168 and hold_entity_code = 'B';
3169
3170 g_count := sql%rowcount;
3171
3172 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3176 END IF;
3173 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3174 IF l_debug_level > 0 THEN
3175 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3177
3178
3179
3180 /* customer level update */
3181 IF l_debug_level > 0 THEN
3182 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
3183 END IF;
3184 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3185 -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3186 IF l_debug_level > 0 THEN
3187 oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE C' ) ;
3188 END IF;
3189
3190 UPDATE OE_HOLD_SOURCES a
3191 set hold_entity_id = (select distinct m.customer_id
3192 from ra_customer_merges m
3193 where a.hold_entity_id =
3194 m.duplicate_id
3195 and m.process_flag = 'Y'
3196 and m.request_id = req_id
3197 and m.set_number = set_num),
3198 last_update_date = sysdate,
3199 last_updated_by = fnd_global.user_id,
3200 last_update_login = fnd_global.login_id
3201 where hold_entity_id in (select m.duplicate_id
3202 from ra_customer_merges m
3203 where m.process_flag = 'Y'
3204 and m.request_id = req_id
3205 and m.set_number = set_num)
3206 and hold_entity_code = 'C';
3207
3208 g_count := sql%rowcount;
3209
3210 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3211 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3212 IF l_debug_level > 0 THEN
3213 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3214 END IF;
3215
3216 END IF;
3217
3218 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge()-' );
3219 IF l_debug_level > 0 THEN
3220 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3221 END IF;
3222
3223
3224 EXCEPTION
3225 when others then
3226 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge' );
3227 IF l_debug_level > 0 THEN
3228 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3229 END IF;
3230 IF l_debug_level > 0 THEN
3231 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3232 END IF;
3233 raise;
3234
3235 END OE_Hold_Merge;
3236
3237 Procedure OE_Drop_SHip_Merge (Req_Id IN NUMBER,
3238 Set_Num IN NUMBER,
3239 Process_Mode IN VARCHAR2)
3240 IS
3241 CURSOR c1 is
3242 select DROP_SHIP_SOURCE_ID
3243 from oe_drop_ship_sources
3244 where LINE_LOCATION_ID in
3245 (select m.duplicate_site_id
3246 from ra_customer_merges m
3247 where m.process_flag = 'Y'
3248 and m.request_id = req_id
3249 and m.set_number = set_num)
3250 for update nowait;
3251
3252
3253 --
3254 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3255 --
3256 BEGIN
3257 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Drop_SHip_Merge()+' );
3258 IF l_debug_level > 0 THEN
3259 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_DROP_SHIP_MERGE' ) ;
3260 END IF;
3261
3262 /* both customer and site level */
3263
3264 IF( process_mode = 'LOCK' ) THEN
3265
3266 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3267 -- arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
3268 IF l_debug_level > 0 THEN
3269 oe_debug_pub.add( 'LOCKING TABLE OE_DROP_SHIP_SOURCES' ) ;
3270 END IF;
3271
3272 open c1;
3273 close c1;
3274
3275 ELSE
3276
3277 /* site level update */
3278 IF l_debug_level > 0 THEN
3279 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3280 END IF;
3281 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3282 -- arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
3283 IF l_debug_level > 0 THEN
3284 oe_debug_pub.add( 'UPDATING OE_DROP_SHIP_SOURCES.LINE_LOCATION_ID' ) ;
3285 END IF;
3286
3287 UPDATE OE_DROP_SHIP_SOURCES a
3288 set line_location_id = (select distinct m.customer_site_id
3289 from ra_customer_merges m
3290 where a.line_location_id = m.duplicate_site_id
3291 and m.request_id = req_id
3292 and m.process_flag = 'Y'
3293 and m.set_number = set_num),
3294 last_update_date = sysdate,
3295 last_updated_by = fnd_global.user_id,
3296 last_update_login = fnd_global.login_id
3297 where line_location_id in (select m.duplicate_site_id
3298 from ra_customer_merges m
3299 where m.process_flag = 'Y'
3300 and m.request_id = req_id
3301 and m.set_number = set_num);
3302 g_count := sql%rowcount;
3303 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3304 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3305 IF l_debug_level > 0 THEN
3306 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3307 END IF;
3308
3309 END IF;
3310
3311 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Drop_SHip_Merge()-' );
3312 IF l_debug_level > 0 THEN
3313 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_DROP_SHIP_MERGE' ) ;
3314 END IF;
3315
3316 EXCEPTION
3317 When others then
3321 END IF;
3318 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Drop_SHip_Merge-' );
3319 IF l_debug_level > 0 THEN
3320 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_DROP_SHIP_MERGE' ) ;
3322 IF l_debug_level > 0 THEN
3323 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3324 END IF;
3325 raise;
3326
3327 END OE_Drop_Ship_Merge;
3328
3329 Procedure OE_Ship_Tolerance_Merge (Req_Id IN NUMBER,
3330 Set_Num IN NUMBER,
3331 Process_Mode IN VARCHAR2)
3332 IS
3333 CURSOR c1 is
3334 select CUST_ITEM_SETTING_ID
3335 from oe_cust_item_settings
3336 where site_use_id in
3337 (select m.duplicate_site_id
3338 from ra_customer_merges m
3339 where m.process_flag = 'Y'
3340 and m.request_id = req_id
3341 and m.set_number = set_num)
3342 for update nowait;
3343
3344 CURSOR c2 is
3345 select CUST_ITEM_SETTING_ID
3346 from oe_cust_item_settings
3347 where customer_id in
3348 (select m.duplicate_id
3349 from ra_customer_merges m
3350 where m.process_flag = 'Y'
3351 and m.request_id = req_id
3352 and m.set_number = set_num)
3353 for update nowait;
3354
3355
3356
3357 --
3358 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3359 --
3360 BEGIN
3361 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_ship_tolerance_merge()+' );
3362 IF l_debug_level > 0 THEN
3363 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_SHIP_TOLERANCE_MERGE' ) ;
3364 END IF;
3365
3366 /* both customer and site level */
3367
3368 IF( process_mode = 'LOCK' ) THEN
3369
3370 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3371 -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3372 IF l_debug_level > 0 THEN
3373 oe_debug_pub.add( 'LOCKING TABLE OE_CUST_ITEM_SETTINGS' ) ;
3374 END IF;
3375
3376 open c1;
3377 close c1;
3378
3379 open c2;
3380 close c2;
3381
3382
3383
3384 ELSE
3385
3386 /* site level update */
3387 IF l_debug_level > 0 THEN
3388 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3389 END IF;
3390 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3391 -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3392 IF l_debug_level > 0 THEN
3393 oe_debug_pub.add( 'UPDATING OE_CUST_ITEM_SETTINGS.SITE_USE_ID' ) ;
3394 END IF;
3395
3396 UPDATE OE_CUST_ITEM_SETTINGS a
3397 set site_use_id = (select distinct m.customer_site_id
3398 from ra_customer_merges m
3399 where a.site_use_id = m.duplicate_site_id
3400 and m.request_id = req_id
3401 and m.process_flag = 'Y'
3402 and m.set_number = set_num),
3403 last_update_date = sysdate,
3404 last_updated_by = fnd_global.user_id,
3405 last_update_login = fnd_global.login_id
3406 where site_use_id in (select m.duplicate_site_id
3407 from ra_customer_merges m
3408 where m.process_flag = 'Y'
3409 and m.request_id = req_id
3410 and m.set_number = set_num);
3411 g_count := sql%rowcount;
3412 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3413 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3414 IF l_debug_level > 0 THEN
3415 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3416 END IF;
3417
3418 /* customer level update */
3419 IF l_debug_level > 0 THEN
3420 oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
3421 END IF;
3422 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3423 -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3424 IF l_debug_level > 0 THEN
3425 oe_debug_pub.add( 'UPDATING OE_CUST_ITEM_SETTINGS.CUSTOMER_ID' ) ;
3426 END IF;
3427
3428 UPDATE OE_CUST_ITEM_SETTINGS a
3429 set customer_id = (select distinct m.customer_id
3430 from ra_customer_merges m
3431 where a.customer_id = m.duplicate_id
3432 and m.request_id = req_id
3433 and m.process_flag = 'Y'
3434 and m.set_number = set_num),
3435 last_update_date = sysdate,
3436 last_updated_by = fnd_global.user_id,
3437 last_update_login = fnd_global.login_id
3438 where customer_id in (select m.duplicate_id
3439 from ra_customer_merges m
3440 where m.process_flag = 'Y'
3441 and m.request_id = req_id
3442 and m.set_number = set_num);
3443 g_count := sql%rowcount;
3444 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3445 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3446 IF l_debug_level > 0 THEN
3447 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3448 END IF;
3449
3450 END IF;
3451
3452 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Ship_Tolerance_Merge()-' );
3453 IF l_debug_level > 0 THEN
3454 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_SHIP_TOLERANCE_MERGE' ) ;
3455 END IF;
3456
3457
3458
3459 EXCEPTION
3460 When others then
3461 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Ship_Tolerance_Merge-' );
3465 IF l_debug_level > 0 THEN
3462 IF l_debug_level > 0 THEN
3463 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_SHIP_TOLERANCE_MERGE' ) ;
3464 END IF;
3466 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3467 END IF;
3468 raise;
3469
3470 END OE_Ship_Tolerance_Merge;
3471
3472
3473 Procedure OE_Sets_Merge (Req_Id IN NUMBER,
3474 Set_Num IN NUMBER,
3475 Process_Mode IN VARCHAR2)
3476 IS
3477 CURSOR c1 is
3478 select Set_Id
3479 from oe_sets
3480 where ship_to_org_id in
3481 (select m.duplicate_site_id
3482 from ra_customer_merges m
3483 where m.process_flag = 'Y'
3484 and m.request_id = req_id
3485 and m.set_number = set_num)
3486 for update nowait;
3487
3488
3489 --
3490 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3491 --
3492 BEGIN
3493 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge()+' );
3494 IF l_debug_level > 0 THEN
3495 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_SETS_MERGE' ) ;
3496 END IF;
3497
3498 /* both customer and site level */
3499
3500 IF( process_mode = 'LOCK' ) THEN
3501
3502 -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3503 -- arp_message.set_token( 'TABLE_NAME', 'OE_SETS', FALSE );
3504 IF l_debug_level > 0 THEN
3505 oe_debug_pub.add( 'LOCKING TABLE OE_SETS' ) ;
3506 END IF;
3507
3508 open c1;
3509 close c1;
3510
3511 ELSE
3512
3513 /* site level update */
3514 IF l_debug_level > 0 THEN
3515 oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3516 END IF;
3517 -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3518 -- arp_message.set_token( 'TABLE_NAME', 'OE_SETS', FALSE );
3519 IF l_debug_level > 0 THEN
3520 oe_debug_pub.add( 'UPDATING OE_SETS.SHIP_TO_ORG_ID' ) ;
3521 END IF;
3522
3523 UPDATE oe_sets a
3524 set ship_to_org_id = (select distinct m.customer_site_id
3525 from ra_customer_merges m
3526 where a.ship_to_org_id = m.duplicate_site_id
3527 and m.request_id = req_id
3528 and m.process_flag = 'Y'
3529 and m.set_number = set_num),
3530 update_date = sysdate,
3531 updated_by = fnd_global.user_id,
3532 update_login = fnd_global.login_id
3533 where ship_to_org_id in (select m.duplicate_site_id
3534 from ra_customer_merges m
3535 where m.process_flag = 'Y'
3536 and m.request_id = req_id
3537 and m.set_number = set_num);
3538 g_count := sql%rowcount;
3539
3540 -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3541 -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3542 IF l_debug_level > 0 THEN
3543 oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3544 END IF;
3545
3546 END IF;
3547
3548 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge()-' );
3549 IF l_debug_level > 0 THEN
3550 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_SETS_MERGE' ) ;
3551 END IF;
3552
3553 EXCEPTION
3554 when others then
3555 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge-' );
3556 IF l_debug_level > 0 THEN
3557 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_SETS_MERGE' ) ;
3558 END IF;
3559 IF l_debug_level > 0 THEN
3560 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3561 END IF;
3562
3563 raise;
3564
3565 END OE_Sets_merge;
3566
3567
3568
3569 Procedure OE_Order_Merge (Req_Id IN NUMBER,
3570 Set_Num IN NUMBER,
3571 Process_Mode IN VARCHAR2)
3572 IS
3573
3574 --
3575 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3576 --
3577 BEGIN
3578 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Order_Merge()+' );
3579 IF l_debug_level > 0 THEN
3580 oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_ORDER_MERGE' ) ;
3581 END IF;
3582
3583 OE_Merge_Headers(Req_Id, Set_Num, Process_Mode);
3584 OE_Merge_Lines(Req_Id, Set_Num, Process_Mode);
3585 -- OE_Merge_Header_History(Req_Id, Set_Num, Process_Mode); -- To be released with the Audit Trail project
3586 OE_Merge_Lines_History(Req_Id, Set_Num, Process_Mode);
3587 -- OE_Merge_Headers_IFACE(Req_Id, Set_Num, Process_Mode); -- Interface tables need not be updated
3588 -- OE_Merge_Lines_IFACE(Req_Id, Set_Num, Process_Mode); -- Interface tables need not be updated
3589 OE_Merge_Header_ACKS(Req_Id, Set_Num, Process_Mode);
3590 OE_Merge_Line_ACKS(Req_Id, Set_Num, Process_Mode);
3591
3592 -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Order_Merge()-' );
3593 IF l_debug_level > 0 THEN
3594 oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_ORDER_MERGE' ) ;
3595 END IF;
3596
3597 EXCEPTION
3598 When others then
3599 -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Order_Merge-' );
3600 IF l_debug_level > 0 THEN
3601 oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_ORDER_MERGE' ) ;
3602 END IF;
3603 IF l_debug_level > 0 THEN
3604 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3605 END IF;
3606
3607 raise;
3608
3609 END OE_Order_Merge;
3610
3611
3612 Procedure OE_Workflow_Merge (Req_Id IN NUMBER,
3616
3613 Set_Num IN NUMBER,
3614 Process_Mode IN VARCHAR2)
3615 IS
3617 --
3618 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3619 --
3620 BEGIN
3621
3622 NULL;
3623
3624 END OE_Workflow_Merge;
3625
3626
3627 END OE_CUST_MERGE_DATA_FIX;
3628