[Home] [Help]
PACKAGE BODY: APPS.RLM_CUST_MERGE
Source
1 PACKAGE BODY RLM_CUST_MERGE AS
2 /* $Header: RLMCMRGB.pls 120.1 2005/07/17 18:24:51 rlanka noship $ */
3
4 --
5 --
6
7 l_merge_hdrs_lines_msg VARCHAR2(2000) := getMessage('RLM_MERGE_HDRS_LINES');
8 l_hdrs_lines_msg_length NUMBER(5) := LENGTHB(l_merge_hdrs_lines_msg);
9 l_column_size NUMBER(5) := 4000;
10 /*2447493*/
11 l_profile_val VARCHAR2(30):= FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
12
13 /*==========================================================================
14 PROCEDURE Merge
15
16 ===========================================================================*/
17
18
19 PROCEDURE Merge(REQ_ID NUMBER, SET_NUM NUMBER, PROCESS_MODE VARCHAR2)
20 --
21 IS
22 --
23 l_duplicateIdTab g_number_tbl_type;
24 l_customerIdTab g_number_tbl_type;
25 l_duplicateAddressIdTab g_number_tbl_type;
26 l_customerAddressIdTab g_number_tbl_type;
27 l_duplicateSiteIdTab g_number_tbl_type;
28 l_customerSiteIdTab g_number_tbl_type;
29
30 BEGIN
31 --
32
33 arp_message.set_line('RLM_CUST_MERGE.Merge_RLM()+' || getTimeStamp);
34 --
35 --
36 IF Process_Mode <> 'LOCK'
37 THEN
38 SELECT duplicate_id, customer_id,
39 duplicate_address_id, customer_address_id,
40 duplicate_site_id, customer_site_id
41 BULK COLLECT INTO l_duplicateIdTab, l_customerIdTab,
42 l_duplicateAddressIdTab, l_customerAddressIdTab,
43 l_duplicateSiteIdTab, l_customerSiteIdTab
44 FROM ra_customer_merges
45 WHERE process_flag = 'N'
46 AND request_id = Req_Id
47 AND set_number = Set_Num;
48 END IF;
49 --
50 --
51 IF NOT( RLM_CUST_MERGE.IS_RLM_INSTALLED() )
52 THEN
53 RETURN;
54 END IF;
55 --
56 /* Calls to other internal procedures for customer Merge */
57 --
58 RLM_CUST_MERGE.Cust_Item_Cum_Keys(Req_Id, Set_Num, Process_Mode);
59 --
60 RLM_CUST_MERGE.Interface_Lines(Req_Id, Set_Num, Process_Mode);
61 --
62 RLM_CUST_MERGE.Interface_Headers(Req_Id, Set_Num, Process_Mode);
63 --
64 RLM_CUST_MERGE.Schedule_Lines(Req_Id, Set_Num, Process_Mode);
65 --
66 RLM_CUST_MERGE.Schedule_Headers(Req_Id, Set_Num, Process_Mode);
67 --
68 RLM_CUST_MERGE.Cust_Shipto_Terms(l_duplicateAddressIdTab, l_customerAddressIdTab, l_duplicateIdTab, l_customerIdTab, Req_Id, Set_Num, Process_Mode);
69 --
70 RLM_CUST_MERGE.Cust_Item_Terms(l_duplicateAddressIdTab, l_customerAddressIdTab, l_duplicateIdTab, l_customerIdTab, Req_Id, Set_Num, Process_Mode);
71 --
72 arp_message.set_line('RLM_CUST_MERGE.Merge_RLM()-' || getTimeStamp);
73
74 END Merge;
75
76
77 /*===========================================================================
78
79 PROCEDURE Cust_Item_Cum_Keys
80
81 ===========================================================================*/
82 PROCEDURE Cust_Item_Cum_Keys(REQ_ID NUMBER,
83 SET_NUM NUMBER,
84 PROCESS_MODE VARCHAR2)
85 --
86 IS
87 --
88 l_cumKeyIdTab g_number_tbl_type;
89 l_cumNotesTab g_varchar_tbl_type;
90 l_duplicateAddressIdTab g_number_tbl_type;
91 l_customerAddressIdTab g_number_tbl_type;
92 --
93 l_max_message_size NUMBER(5);
94 l_merge_cum_keys_msg VARCHAR2(2000) := getMessage('RLM_MERGE_CUM_KEYS');
95 l_cum_keys_msg_length NUMBER(5) := LENGTHB(l_merge_cum_keys_msg);
96 --
97 CURSOR cust_merge_cur IS
98 SELECT ck.cum_key_id,
99 ck.cum_note_text,
100 m.duplicate_address_id,
101 m.customer_address_id
102 FROM RLM_CUST_ITEM_CUM_KEYS ck,
103 RA_CUSTOMER_MERGES m
104 WHERE m.process_flag = 'N'
105 AND m.request_id = req_id
106 AND m.set_number = set_num
107 AND (ck.intrmd_ship_to_id = m.duplicate_address_id
108 OR ck.ship_to_address_id = m.duplicate_address_id
109 OR ck.bill_to_address_id = m.duplicate_address_id)
110 FOR update of ck.intrmd_ship_to_id,
111 ck.ship_to_address_id,
112 ck.bill_to_address_id,
113 ck.cum_note_text
114 nowait;
115 --
116 --
117 i NUMBER;
118 --
119 --
120 BEGIN
121 --
122 arp_message.set_line('RLM_CUST_MERGE.Cust_Item_Cum_Keys()+' || getTimeStamp );
123 --
124 IF (process_mode = 'LOCK') THEN
125 --
126 setARMessageLockTable('RLM_CUST_ITEM_CUM_KEYS');
127 --
128 open cust_merge_cur;
129 close cust_merge_cur;
130 --
131 ELSE
132 --
133 --
134 l_max_message_size := l_column_size - ( l_cum_keys_msg_length + 1 );
135 --
136 open cust_merge_cur;
137 --
138 FETCH cust_merge_cur BULK COLLECT INTO l_cumKeyIdTab,
139 l_cumNotesTab,
140 l_duplicateAddressIdTab,
141 l_customerAddressIdTab;
142 --
143 close cust_merge_cur;
144 --
145 IF l_cumKeyIdTab.COUNT <> 0 THEN
146
147 FOR i IN l_cumKeyIdTab.FIRST..l_cumKeyIdTab.LAST LOOP
148 --
149 IF ( LENGTHB(l_cumNotesTab(i)) <= l_max_message_size ) THEN
150 --
151 l_cumNotesTab(i) := l_cumNotesTab(i) || ' ' || l_merge_cum_keys_msg;
152 --
153 ELSE
154 --
155 l_cumNotesTab(i) := SUBSTRB(l_cumNotesTab(i), 1, l_max_message_size) || ' '
156 || l_merge_cum_keys_msg;
157 --
158 END IF;
159 --
160 END LOOP;
161 --
162 /*2447493*/
163 RLM_CUST_ITEM_CUM_KEYS_LOG (
164 req_id => req_id,
165 set_num=>set_num,
166 process_mode=>process_mode) ;
167 /*2447493*/
168 --
169 setARMessageUpdateTable('RLM_CUST_ITEM_CUM_KEYS');
170 --
171 FORALL i IN l_cumKeyIdTab.FIRST..l_cumKeyIdTab.LAST
172 --
173 UPDATE RLM_CUST_ITEM_CUM_KEYS
174 SET intrmd_ship_to_id = DECODE(intrmd_ship_to_id,
175 l_duplicateAddressIdTab(i),
176 l_customerAddressIdTab(i),
177 intrmd_ship_to_id
178 ),
179 ship_to_address_id = DECODE(ship_to_address_id,
180 l_duplicateAddressIdTab(i),
181 l_customerAddressIdTab(i),
182 ship_to_address_id
183 ),
184 bill_to_address_id = DECODE(bill_to_address_id,
185 l_duplicateAddressIdTab(i),
186 l_customerAddressIdTab(i),
187 bill_to_address_id
188 ),
189 inactive_flag = 'Y',
190 cum_note_text = l_cumNotesTab(i),
191 last_update_date = SYSDATE,
192 last_updated_by = arp_standard.profile.user_id,
193 last_update_login = arp_standard.profile.last_update_login,
194 request_id = req_id,
195 program_application_id = arp_standard.profile.program_application_id,
196 program_id = arp_standard.profile.program_id,
197 program_update_date = SYSDATE
198 WHERE cum_key_id = l_cumKeyIdTab(i);
199 --
200 setARMessageRowCount( SQL%ROWCOUNT );
201 --
202 END IF;
203 --
204 END IF;
205 --
206 arp_message.set_line('RLM_CUST_MERGE.Cust_Item_Cum_Keys()-' || getTimeStamp );
207 --
208 EXCEPTION
209 --
210 WHEN OTHERS THEN
211 --
212 arp_message.set_error('RLM_CUST_MERGE.Cust_Item_Cum_Keys');
213 RAISE;
214 --
215 END Cust_Item_Cum_Keys;
216
217 /*===========================================================================
218
219 PROCEDURE Interface_Headers
220
221 ===========================================================================*/
222 PROCEDURE Interface_Headers(REQ_ID NUMBER,
223 SET_NUM NUMBER,
224 PROCESS_MODE VARCHAR2)
225 IS
226 --
227 l_headerIdTab g_number_tbl_type;
228 l_headerNotesTab g_varchar_tbl_type;
229 l_duplicateIdTab g_number_tbl_type;
230 l_customerIdTab g_number_tbl_type;
231 l_duplicateAddressIdTab g_number_tbl_type;
232 l_customerAddressIdTab g_number_tbl_type;
233 l_customerNameTab t_CuatomerNameTbl;
234 --
235 l_max_message_size NUMBER(5);
236 --
237 CURSOR cust_merge_cur IS
238 SELECT ih.header_id,
239 ih.header_note_text,
240 m.duplicate_id,
241 m.customer_id,
242 m.duplicate_address_id,
243 m.customer_address_id,
244 --bug 2171856
245 m.customer_name
246 FROM RLM_INTERFACE_HEADERS ih,
247 RA_CUSTOMER_MERGES m
248 WHERE m.process_flag = 'N'
249 AND m.request_id = req_id
250 AND m.set_number = set_num
251 AND (ih.customer_id = m.duplicate_id
252 OR ih.ece_primary_address_id = m.duplicate_address_id)
253 FOR update of ih.header_id,
254 ih.customer_id,
255 ih.ece_primary_address_id,
256 ih.header_note_text
257 nowait;
258
259 --
260 --
261 i NUMBER;
262 --
263 --
264 BEGIN
265 --
266 arp_message.set_line('RLM_CUST_MERGE.Interface_Headers()+' || getTimeStamp);
267 --
268
269 IF (process_mode = 'LOCK') THEN
270 --
271 setARMessageLockTable('RLM_INTERFACE_HEADERS');
272 --
273 open cust_merge_cur;
274 close cust_merge_cur;
275 --
276 ELSE
277 --
278 l_max_message_size := l_column_size - ( l_hdrs_lines_msg_length + 1 );
279 --
280 open cust_merge_cur;
281 --
282 FETCH cust_merge_cur BULK COLLECT INTO l_headerIdTab,
283 l_headerNotesTab,
284 l_duplicateIdTab,
285 l_customerIdTab,
286 l_duplicateAddressIdTab,
287 l_customerAddressIdTab,
288 l_customerNameTab;
289 --
290 close cust_merge_cur;
291 --
292 IF l_headerIdTab.COUNT <> 0 THEN
293 --
294 FOR i IN l_headerIdTab.FIRST..l_headerIdTab.LAST LOOP
295 --
296 IF ( LENGTHB(l_headerNotesTab(i)) <= l_max_message_size ) THEN
297 --
298 l_headerNotesTab(i) := l_headerNotesTab(i) || ' ' || l_merge_hdrs_lines_msg;
299 --
300 ELSE
301 --
302 l_headerNotesTab(i) := SUBSTRB(l_headerNotesTab(i), 1, l_max_message_size) || ' ' || l_merge_hdrs_lines_msg;
303 --
304 END IF;
305 --
306 END LOOP;
307 --
308 /*2447493*/
309 RLM_INTERFACE_HEADERS_LOG (
310 req_id=>req_id,
311 set_num=>set_num,
312 process_mode=>process_mode);
313 /*2447493*/
314 setARMessageUpdateTable('RLM_INTERFACE_HEADERS');
315 --
316 FORALL i IN l_headerIdTab.FIRST..l_headerIdTab.LAST
317 --
318 UPDATE RLM_INTERFACE_HEADERS
319 SET customer_id = DECODE(customer_id,
320 l_duplicateIdTab(i),
321 l_customerIdTab(i),
322 customer_id
323 ),
324 cust_name_ext = DECODE(customer_id,
325 l_duplicateIdTab(i),
326 l_customerNameTab(i),
327 cust_name_ext
328 ),
329 ece_primary_address_id = DECODE(ece_primary_address_id,
330 l_duplicateAddressIdTab(i),
331 l_customerAddressIdTab(i),
332 ece_primary_address_id
333 ),
334 header_note_text = l_headerNotesTab(i),
335 last_update_date = SYSDATE,
336 last_updated_by = arp_standard.profile.user_id,
337 last_update_login = arp_standard.profile.last_update_login,
338 request_id = req_id,
339 program_application_id = arp_standard.profile.program_application_id,
340 program_id = arp_standard.profile.program_id,
341 program_update_date = SYSDATE
342 WHERE header_id = l_headerIdTab(i);
343 --
344 setARMessageRowCount( SQL%ROWCOUNT );
345 --
346 END IF;
347 --
348 END IF;
349 --
350 arp_message.set_line('RLM_CUST_MERGE.Interface_Headers()-' || getTimeStamp);
351
352 EXCEPTION
353 --
354 WHEN OTHERS THEN
355 --
356 arp_message.set_error('RLM_CUST_MERGE.Interface_Headers');
357 RAISE;
358 --
359 END Interface_Headers;
360
361 /*===========================================================================
362
363 PROCEDURE Interface_Lines
364
365 ===========================================================================*/
366 PROCEDURE Interface_Lines(REQ_ID NUMBER,
367 SET_NUM NUMBER,
368 PROCESS_MODE VARCHAR2)
369 IS
370 --
371 l_lineIdTab g_number_tbl_type;
372 l_lineNotesTab g_varchar_tbl_type;
373 l_duplicateAddressIdTab g_number_tbl_type;
374 l_customerAddressIdTab g_number_tbl_type;
375 l_duplicateSiteIdTab g_number_tbl_type;
376 l_customerSiteIdTab g_number_tbl_type;
377 l_DuplicateIdTab g_number_tbl_type;
378 l_ShipToCustomerIdTab g_number_tbl_type;
379 --
380 l_max_message_size NUMBER(5);
381 --
382 CURSOR cust_merge_cur IS
383 SELECT il.line_id,
384 il.item_note_text,
385 m.duplicate_address_id,
386 m.customer_address_id,
387 m.duplicate_site_id,
388 m.customer_site_id,
389 m.duplicate_id,
390 m.customer_id
391 FROM RLM_INTERFACE_LINES il,
392 RA_CUSTOMER_MERGES m
393 WHERE m.process_flag = 'N'
394 AND m.request_id = req_id
395 AND m.set_number = set_num
396 AND (il.bill_to_address_id = m.duplicate_address_id
397 OR il.intrmd_ship_to_id = m.duplicate_address_id
398 OR il.ship_to_address_id = m.duplicate_address_id
399 OR il.ship_to_customer_id = m.duplicate_id)
400 FOR update of il.bill_to_address_id,
401 il.intrmd_ship_to_id,
402 il.ship_to_address_id,
403 il.item_note_text,
404 il.ship_to_customer_id
405 nowait;
406 --
407 i NUMBER;
408 --
409 --
410 BEGIN
411 --
412 arp_message.set_line('RLM_CUST_MERGE.Interface_Lines()+' || getTimeStamp);
413 --
414 IF (process_mode = 'LOCK') THEN
415 --
416 setARMessageLockTable('RLM_INTERFACE_LINES');
417 --
418 open cust_merge_cur;
419 --
420 close cust_merge_cur;
424 l_max_message_size := l_column_size - ( l_hdrs_lines_msg_length + 1 );
421 --
422 ELSE
423 --
425 --
426 open cust_merge_cur;
427 --
428 FETCH cust_merge_cur BULK COLLECT INTO l_lineIdTab,
429 l_lineNotesTab,
430 l_duplicateAddressIdTab,
431 l_customerAddressIdTab,
432 l_duplicateSiteIdTab,
433 l_customerSiteIdTab,
434 l_duplicateIdTab,
435 l_ShipToCustomerIdTab;
436 --
437 close cust_merge_cur;
438 --
439 IF l_lineIdTab.COUNT <> 0 THEN
440 --
441 FOR i IN l_lineIdTab.FIRST..l_lineIdTab.LAST LOOP
442 --
443 IF ( LENGTHB(l_lineNotesTab(i)) <= l_max_message_size ) THEN
444 --
445 l_lineNotesTab(i) := l_lineNotesTab(i) || ' ' || l_merge_hdrs_lines_msg;
446 --
447 ELSE
448 --
449 l_lineNotesTab(i) := SUBSTRB(l_lineNotesTab(i), 1, l_max_message_size) || ' ' || l_merge_hdrs_lines_msg;
450 --
451 END IF;
452 --
453 END LOOP;
454 --
455 /*2447493*/
456 RLM_INTERFACE_LINES_LOG (
457 req_id => req_id,
458 set_num=>set_num,
459 process_mode=>process_mode) ;
460 /*2447493*/
461 setARMessageUpdateTable('RLM_INTERFACE_LINES');
462 --
463 FORALL i IN l_lineIdTab.FIRST..l_lineIdTab.LAST
464 --
465 UPDATE RLM_INTERFACE_LINES
466 SET
467 ship_to_customer_id = DECODE(ship_to_customer_id,
468 l_duplicateIdTab(i),
469 l_ShiptoCustomerIdTab(i),
470 ship_to_customer_id
471 ),
472 invoice_to_org_id = DECODE(invoice_to_org_id,
473 l_duplicateSiteIdTab(i),
474 l_customerSiteIdTab(i),
475 invoice_to_org_id
476 ),
477 bill_to_address_id = DECODE(bill_to_address_id,
478 l_duplicateAddressIdTab(i),
479 l_customerAddressIdTab(i),
480 bill_to_address_id
481 ),
482 intmed_ship_to_org_id = DECODE(intmed_ship_to_org_id,
483 l_duplicateSiteIdTab(i),
484 l_customerSiteIdTab(i),
485 intmed_ship_to_org_id
486 ),
487 intrmd_ship_to_id = DECODE(intrmd_ship_to_id,
488 l_duplicateAddressIdTab(i),
489 l_customerAddressIdTab(i),
490 intrmd_ship_to_id
491 ),
492 ship_to_address_id = DECODE(ship_to_address_id,
493 l_duplicateAddressIdTab(i),
494 l_customerAddressIdTab(i),
495 ship_to_address_id
496 ),
497 ship_to_org_id = DECODE(ship_to_org_id,
498 l_duplicateSiteIdTab(i),
499 l_customerSiteIdTab(i),
500 ship_to_org_id
501 ),
502 item_note_text = l_lineNotesTab(i),
503 last_update_date = SYSDATE,
504 last_updated_by = arp_standard.profile.user_id,
505 last_update_login = arp_standard.profile.last_update_login,
506 request_id = req_id,
507 program_application_id= arp_standard.profile.program_application_id,
508 program_id = arp_standard.profile.program_id,
509 program_update_date = SYSDATE
510 WHERE line_id = l_lineIdTab(i);
511 --
512 setARMessageRowCount( SQL%ROWCOUNT );
513 --
514 END IF;
515 --
516 END IF;
517 --
518 arp_message.set_line('RLM_CUST_MERGE.Interface_Lines()-' || getTimeStamp);
519 --
520 EXCEPTION
521 --
522 WHEN others THEN
523 --
524 arp_message.set_error('RLM_CUST_MERGE.Interface_Lines');
525 RAISE;
526 --
527 END Interface_Lines;
528 /*===========================================================================
529
530 PROCEDURE Schedule_Headers
531
532 ===========================================================================*/
533 PROCEDURE Schedule_Headers(REQ_ID NUMBER,
534 SET_NUM NUMBER,
538 l_headerIdTab g_number_tbl_type;
535 PROCESS_MODE VARCHAR2)
536 IS
537 --
539 l_headerNotesTab g_varchar_tbl_type;
540 l_duplicateIdTab g_number_tbl_type;
541 l_customerIdTab g_number_tbl_type;
542 l_duplicateAddressIdTab g_number_tbl_type;
543 l_customerAddressIdTab g_number_tbl_type;
544 l_customerNameTab t_CuatomerNameTbl;
545 --
546 l_max_message_size NUMBER(5);
547 --
548 CURSOR cust_merge_cur IS
549 SELECT sh.header_id,
550 sh.header_note_text,
551 m.duplicate_id,
552 m.customer_id,
553 m.duplicate_address_id,
554 m.customer_address_id,
555 m.customer_name
556 FROM RLM_SCHEDULE_HEADERS sh,
557 RA_CUSTOMER_MERGES m
558 WHERE m.process_flag = 'N'
559 AND m.request_id = req_id
560 AND m.set_number = set_num
561 AND (sh.customer_id = m.duplicate_id
562 OR sh.ece_primary_address_id = m.duplicate_address_id)
563 FOR update of sh.header_id,
564 sh.customer_id,
565 sh.ece_primary_address_id,
566 sh.header_note_text nowait;
567
568 --
569 --
570 i NUMBER;
571 --
572 --
573 BEGIN
574 --
575 arp_message.set_line('RLM_CUST_MERGE.Schedule_Headers()+' || getTimeStamp);
576 --
577 IF (process_mode = 'LOCK') THEN
578 --
579 setARMessageLockTable('RLM_SCHEDULE_HEADERS');
580 open cust_merge_cur;
581 close cust_merge_cur;
582 --
583 ELSE
584 --
585 l_max_message_size := l_column_size - ( l_hdrs_lines_msg_length + 1 );
586 --
587 open cust_merge_cur;
588 --
589 FETCH cust_merge_cur BULK COLLECT INTO l_headerIdTab,
590 l_headerNotesTab,
591 l_duplicateIdTab,
592 l_customerIdTab,
593 l_duplicateAddressIdTab,
594 l_customerAddressIdTab,
595 l_customerNameTab ;
596 --
597 close cust_merge_cur;
598 --
599 IF l_headerIdTab.COUNT <> 0 THEN
600 --
601 FOR i IN l_headerIdTab.FIRST..l_headerIdTab.LAST LOOP
602 --
603 IF ( LENGTHB(l_headerNotesTab(i)) <= l_max_message_size ) THEN
604 --
605 l_headerNotesTab(i) := l_headerNotesTab(i) || ' ' || l_merge_hdrs_lines_msg;
606 --
607 ELSE
608 --
609 l_headerNotesTab(i) := SUBSTRB(l_headerNotesTab(i), 1, l_max_message_size) || ' ' || l_merge_hdrs_lines_msg;
610 --
611 END IF;
612 --
613 END LOOP;
614 --
615 /*2447493*/
616 RLM_SCHEDULE_HEADERS_LOG (
617 req_id=>req_id,
618 set_num=>set_num,
619 process_mode=>process_mode);
620 /*2447493*/
621 setARMessageUpdateTable('RLM_SCHEDULE_HEADERS');
622 --
623 FORALL i IN l_headerIdTab.FIRST..l_headerIdTab.LAST
624 --
625 UPDATE RLM_SCHEDULE_HEADERS
626 SET customer_id = DECODE(customer_id,
627 l_duplicateIdTab(i),
628 l_customerIdTab(i),
629 customer_id
630 ),
631 cust_name_ext = DECODE(customer_id,
632 l_duplicateIdTab(i),
633 l_customerNameTab(i),
634 cust_name_ext
635 ),
636 ece_primary_address_id= DECODE(ece_primary_address_id,
637 l_duplicateAddressIdTab(i),
638 l_customerAddressIdTab(i),
639 ece_primary_address_id
640 ),
641 header_note_text = l_headerNotesTab(i),
642 last_update_date = SYSDATE,
643 last_updated_by = arp_standard.profile.user_id,
644 last_update_login = arp_standard.profile.last_update_login,
645 request_id = req_id,
646 program_application_id= arp_standard.profile.program_application_id,
647 program_id = arp_standard.profile.program_id,
648 program_update_date = SYSDATE
649 WHERE header_id = l_headerIdTab(i);
650 --
651 setARMessageRowCount( SQL%ROWCOUNT );
652 --
653 END IF;
654 --
655 END IF;
656 --
657 arp_message.set_line('RLM_CUST_MERGE.Schedule_Headers()-' || getTimeStamp);
658
659 EXCEPTION
660 --
661 WHEN others THEN
662 --
663 arp_message.set_error('RLM_CUST_MERGE.Schedule_Headers');
664 RAISE;
665 --
666 END Schedule_Headers;
667
668 /*===========================================================================
669
670 PROCEDURE Schedule_Lines
671
672 ===========================================================================*/
676 IS
673 PROCEDURE Schedule_Lines(REQ_ID NUMBER,
674 SET_NUM NUMBER,
675 PROCESS_MODE VARCHAR2)
677 --
678 l_lineIdTab g_number_tbl_type;
679 l_lineNotesTab g_varchar_tbl_type;
680 l_duplicateAddressIdTab g_number_tbl_type;
681 l_customerAddressIdTab g_number_tbl_type;
682 l_duplicateSiteIdTab g_number_tbl_type;
683 l_customerSiteIdTab g_number_tbl_type;
684 l_duplicateIdTab g_number_tbl_type;
685 l_customerShiptoIdTab g_number_tbl_type;
686 --
687 l_max_message_size NUMBER(5);
688 --
689 CURSOR cust_merge_cur IS
690 SELECT il.line_id,
691 il.item_note_text,
692 m.duplicate_address_id,
693 m.customer_address_id,
694 m.duplicate_site_id,
695 m.customer_site_id,
696 m.duplicate_id,
697 m.customer_id
698 FROM RLM_SCHEDULE_LINES il,
699 RA_CUSTOMER_MERGES m
700 WHERE m.process_flag = 'N'
701 AND m.request_id = req_id
702 AND m.set_number = set_num
703 AND (il.bill_to_address_id = m.duplicate_address_id
704 OR il.intrmd_ship_to_id = m.duplicate_address_id
705 OR il.ship_to_address_id = m.duplicate_address_id
706 OR il.ship_to_customer_id = m.duplicate_id)
707 FOR update of il.bill_to_address_id,
708 il.intrmd_ship_to_id,
709 il.ship_to_address_id,
710 il.item_note_text,
711 il.ship_to_customer_id
712 nowait;
713 --
714 --
715 i NUMBER;
716 --
717 --
718 BEGIN
719 --
720 arp_message.set_line('RLM_CUST_MERGE.Schedule_Lines()+' || getTimeStamp);
721 --
722 IF (process_mode = 'LOCK') THEN
723 --
724 setARMessageLockTable('RLM_SCHEDULE_LINES');
725 --
726 open cust_merge_cur;
727 --
728 close cust_merge_cur;
729 --
730 ELSE
731 --
732 l_max_message_size := l_column_size - ( l_hdrs_lines_msg_length + 1 );
733 --
734 open cust_merge_cur;
735 --
736 FETCH cust_merge_cur BULK COLLECT INTO l_lineIdTab,
737 l_lineNotesTab,
738 l_duplicateAddressIdTab,
739 l_customerAddressIdTab,
740 l_duplicateSiteIdTab,
741 l_customerSiteIdTab,
742 l_duplicateIdTab,
743 l_customerShiptoIdTab;
744 --
745 close cust_merge_cur;
746 --
747 IF l_lineIdTab.COUNT <> 0 THEN
748 --
749 FOR i IN l_lineIdTab.FIRST..l_lineIdTab.LAST LOOP
750 --
751 IF ( LENGTHB(l_lineNotesTab(i)) <= l_max_message_size ) THEN
752 --
753 l_lineNotesTab(i) := l_lineNotesTab(i) || ' ' || l_merge_hdrs_lines_msg;
754 --
755 ELSE
756 --
757 l_lineNotesTab(i) := SUBSTRB(l_lineNotesTab(i), 1, l_max_message_size) || ' ' || l_merge_hdrs_lines_msg;
758 --
759 END IF;
760 --
761 END LOOP;
762 --
763 /*2447493*/
764 RLM_SCHEDULE_LINES_LOG (
765 req_id => req_id,
766 set_num=>set_num,
767 process_mode=>process_mode) ;
768 /*2447493*/
769 setARMessageUpdateTable('RLM_SCHEDULE_LINES');
770 --
771 FORALL i IN l_lineIdTab.FIRST..l_lineIdTab.LAST
772 --
773 UPDATE RLM_SCHEDULE_LINES
774 SET invoice_to_org_id = DECODE(invoice_to_org_id,
775 l_duplicateSiteIdTab(i),
776 l_customerSiteIdTab(i),
777 invoice_to_org_id
778 ),
779 ship_to_customer_id = DECODE(ship_to_customer_id,
780 l_duplicateIdTab(i),
781 l_customerShiptoIdTab(i),
782 ship_to_customer_id
783 ),
784 bill_to_address_id = DECODE(bill_to_address_id,
785 l_duplicateAddressIdTab(i),
786 l_customerAddressIdTab(i),
787 bill_to_address_id
788 ),
789 intmed_ship_to_org_id = DECODE(intmed_ship_to_org_id,
790 l_duplicateSiteIdTab(i),
791 l_customerSiteIdTab(i),
792 intmed_ship_to_org_id
793 ),
794 intrmd_ship_to_id = DECODE(intrmd_ship_to_id,
795 l_duplicateAddressIdTab(i),
796 l_customerAddressIdTab(i),
800 l_duplicateAddressIdTab(i),
797 intrmd_ship_to_id
798 ),
799 ship_to_address_id = DECODE(ship_to_address_id,
801 l_customerAddressIdTab(i),
802 ship_to_address_id
803 ),
804 ship_to_org_id = DECODE(ship_to_org_id,
805 l_duplicateSiteIdTab(i),
806 l_customerSiteIdTab(i),
807 ship_to_org_id
808 ),
809 item_note_text = l_lineNotesTab(i),
810 last_update_date = SYSDATE,
811 last_updated_by = arp_standard.profile.user_id,
812 last_update_login = arp_standard.profile.last_update_login,
813 request_id = req_id,
814 program_application_id= arp_standard.profile.program_application_id,
815 program_id = arp_standard.profile.program_id,
816 program_update_date = SYSDATE
817 WHERE line_id = l_lineIdTab(i);
818 --
819 setARMessageRowCount( SQL%ROWCOUNT );
820 --
821 END IF;
822 --
823 END IF;
824 --
825 arp_message.set_line('RLM_CUST_MERGE.Schedule_Lines()-' || getTimeStamp);
826 --
827 EXCEPTION
828 --
829 WHEN OTHERS THEN
830 --
831 arp_message.set_error('RLM_CUST_MERGE.Schedule_Lines');
832 RAISE;
833 --
834 END Schedule_Lines;
835
836 /*===========================================================================
837
838 PROCEDURE Cust_Shipto_Terms
839
840 ===========================================================================*/
841 PROCEDURE Cust_Shipto_Terms(p_duplicateAddressIdTab g_number_tbl_type,
842 p_customerAddressIdTab g_number_tbl_type,
843 p_duplicateIdTab g_number_tbl_type,
844 p_customerIdTab g_number_tbl_type,
845 REQ_ID NUMBER,
846 SET_NUM NUMBER,
847 PROCESS_MODE VARCHAR2)
848 --
849 IS
850 --
851 CURSOR cust_merge_cur IS
852 SELECT address_id,
853 customer_id
854 FROM RLM_CUST_SHIPTO_TERMS
855 WHERE customer_id in
856 (select m.duplicate_id
857 from ra_customer_merges m
858 where m.process_flag = 'N'
859 and m.request_id = req_id
860 and m.set_number = set_num)
861 OR address_id in
862 (select m.duplicate_address_id
863 from ra_customer_merges m
864 where m.process_flag = 'N'
865 and m.request_id = req_id
866 and m.set_number = set_num)
867 FOR update nowait;
868
869 /*2447493*/
870 CURSOR merge_records_cur IS
871 SELECT distinct CUSTOMER_MERGE_HEADER_ID
872 FROM rlm_cust_shipto_terms yt,ra_customer_merges m
873 where yt.customer_id = m.duplicate_id
874 AND m.process_flag = 'N'
875 AND m.request_id = req_id
876 AND m.set_number = set_num;
877
878 CURSOR cust_shipto_terms_cur is
879 SELECT * from rlm_cust_shipto_terms
880 where customer_id in
881 (select m.duplicate_id
882 from ra_customer_merges m
883 where m.process_flag = 'N'
884 and m.request_id = req_id
885 and m.set_number = set_num)
886 OR address_id in
887 (select m.duplicate_address_id
888 from ra_customer_merges m
889 where m.process_flag = 'N'
890 and m.request_id = req_id
891 and m.set_number = set_num)
892 FOR update nowait;
893
894
895 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
896 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
897 INDEX BY BINARY_INTEGER;
898 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
899
900 --
901 i NUMBER;
902 l_last_fetch BOOLEAN := FALSE;
903 --
904 --
905 BEGIN
906 --
907 arp_message.set_line('RLM_CUST_MERGE.Cust_Shipto_Terms()+' || getTimeStamp);
908 --
909 IF (process_mode = 'LOCK') THEN
910 --
911 setARMessageLockTable('RLM_CUST_SHIPTO_TERMS');
912 --
913 open cust_merge_cur;
914 close cust_merge_cur;
915 --
916 ELSE
917 --
918 open merge_records_cur;
919
920 FETCH merge_records_cur BULK COLLECT INTO
921 MERGE_HEADER_ID_LIST
922 limit 1000;
923
924
925 IF merge_records_cur%NOTFOUND THEN
926 l_last_fetch := TRUE;
927 END IF;
928 close merge_records_cur;
929
930 /*IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
931 exit;
935 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT
932 END IF;*/
933 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
934 --
936 LOOP
937 --
938 FOR cust_shipto_terms in cust_shipto_terms_cur
939 --
940 LOOP
941
942 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
943 MERGE_LOG_ID,
944 TABLE_NAME,
945 MERGE_HEADER_ID,
946 DEL_COL1,
947 DEL_COL2,
948 DEL_COL3 ,
949 DEL_COL4 ,
950 DEL_COL5,
951 DEL_COL6,
952 DEL_COL7,
953 DEL_COL8,
954 DEL_COL9,
955 DEL_COL10,
956 DEL_COL11,
957 DEL_COL12,
958 DEL_COL13,
959 DEL_COL14,
960 DEL_COL15,
961 DEL_COL16,
962 DEL_COL17,
963 DEL_COL18,
964 DEL_COL19,
965 DEL_COL20,
966 DEL_COL21,
967 DEL_COL22,
968 DEL_COL23,
969 DEL_COL24,
970 DEL_COL25,
971 DEL_COL26,
972 DEL_COL27,
973 DEL_COL28,
974 DEL_COL29,
975 DEL_COL30,
976 DEL_COL31,
977 DEL_COL32,
978 DEL_COL33,
979 DEL_COL34 ,
980 DEL_COL35,
981 DEL_COL36,
982 DEL_COL37,
983 DEL_COL38,
984 DEL_COL39,
985 DEL_COL40,
986 DEL_COL41,
987 DEL_COL42,
988 DEL_COL43,
989 DEL_COL44,
990 DEL_COL45,
991 DEL_COL46,
992 DEL_COL47,
993 DEL_COL48,
994 DEL_COL49,
995 DEL_COL50,
996 DEL_COL51,
997 DEL_COL52,
998 DEL_COL53,
999 DEL_COL54,
1000 DEL_COL55,
1001 DEL_COL56,
1002 DEL_COL57,
1003 DEL_COL58,
1004 DEL_COL59,
1005 DEL_COL60,
1006 DEL_COL61,
1007 DEL_COL62,
1008 DEL_COL63,
1009 DEL_COL64,
1010 DEL_COL65,
1011 DEL_COL66,
1012 DEL_COL67,
1013 DEL_COL68,
1014 DEL_COL69,
1015 DEL_COL70,
1016 DEL_COL71,
1017 DEL_COL72,
1018 DEL_COL73,
1019 DEL_COL74,
1020 DEL_COL75,
1021 DEL_COL76,
1022 DEL_COL77,
1023 DEL_COL78,
1024 DEL_COL79,
1025 DEL_COL80,
1026 DEL_COL81,
1027 DEL_COL82,
1028 DEL_COL83,
1029 DEL_COL84,
1030 DEL_COL85,
1031 DEL_COL86,
1032 DEL_COL87,
1033 DEL_COL88,
1034 DEL_COL89,
1035 DEL_COL90,
1036 DEL_COL91,
1037 DEL_COL92,
1038 DEL_COL93,
1039 DEL_COL94,
1040 DEL_COL95,
1041 DEL_COL96,
1042 DEL_COL97,
1043 DEL_COL98,
1044 DEL_COL99,
1045 DEL_COL100,
1046 DEL_COL101,
1047 DEL_COL102,
1048 DEL_COL103,
1049 DEL_COL104,
1050 DEL_COL105,
1051 DEL_COL106,
1052 DEL_COL107,
1053 DEL_COL108,
1054 DEL_COL109,
1055 DEL_COL110,
1056 DEL_COL111,
1057 DEL_COL112,
1058 DEL_COL113,
1059 DEL_COL114,
1060 DEL_COL115,
1061 DEL_COL116,
1062 DEL_COL117,
1063 DEL_COL118,
1064 DEL_COL119,
1065 ACTION_FLAG,
1066 REQUEST_ID,
1067 CREATED_BY,
1068 CREATION_DATE,
1069 LAST_UPDATE_LOGIN,
1070 LAST_UPDATE_DATE,
1071 LAST_UPDATED_BY)
1072 Values
1073 ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1074 'RLM_CUST_SHIPTO_TERMS',
1075 MERGE_HEADER_ID_LIST(I),
1076 cust_shipto_terms.CUST_SHIPTO_TERMS_ID,
1077 cust_shipto_terms.ADDRESS_ID,
1078 cust_shipto_terms.CUSTOMER_ID,
1079 cust_shipto_terms.SHIP_FROM_ORG_ID,
1080 cust_shipto_terms.CUM_CONTROL_CODE,
1081 cust_shipto_terms.SHIP_METHOD,
1082 cust_shipto_terms.INTRANSIT_TIME,
1083 cust_shipto_terms.TIME_UOM_CODE,
1084 cust_shipto_terms.SHIP_DELIVERY_RULE_NAME,
1085 cust_shipto_terms.CUM_CURRENT_RECORD_YEAR,
1086 cust_shipto_terms.CUM_PREVIOUS_RECORD_YEAR,
1087 cust_shipto_terms.CUM_CURRENT_START_DATE,
1088 cust_shipto_terms.CUM_PREVIOUS_START_DATE,
1089 cust_shipto_terms.CUM_ORG_LEVEL_CODE,
1090 cust_shipto_terms.CUM_SHIPMENT_RULE_CODE,
1091 cust_shipto_terms.CUM_YESTERD_TIME_CUTOFF,
1092 cust_shipto_terms.CUST_ASSIGN_SUPPLIER_CD,
1093 cust_shipto_terms.CUSTOMER_RCV_CALENDAR_CD,
1094 cust_shipto_terms.FREIGHT_CODE,
1095 cust_shipto_terms.SUPPLIER_SHP_CALENDAR_CD,
1096 cust_shipto_terms.UNSHIP_FIRM_CUTOFF_DAYS,
1097 cust_shipto_terms.UNSHIPPED_FIRM_DISP_CD,
1098 cust_shipto_terms.USE_EDI_SDP_CODE_FLAG,
1099 cust_shipto_terms.DEMAND_TOLERANCE_ABOVE,
1100 cust_shipto_terms.DEMAND_TOLERANCE_BELOW,
1101 cust_shipto_terms.INACTIVE_DATE,
1102 cust_shipto_terms.HEADER_ID,
1103 cust_shipto_terms.PRICE_LIST_ID,
1104 cust_shipto_terms.CRITICAL_ATTRIBUTE_KEY,
1105 cust_shipto_terms.CUSTOMER_CONTACT_ID,
1106 cust_shipto_terms.PLN_FIRM_DAY_FROM,
1107 cust_shipto_terms.PLN_FIRM_DAY_TO,
1108 cust_shipto_terms.PLN_FORECAST_DAY_FROM,
1109 cust_shipto_terms.PLN_FORECAST_DAY_TO,
1110 cust_shipto_terms.PLN_FROZEN_DAY_TO,
1111 cust_shipto_terms.PLN_FROZEN_DAY_FROM,
1112 cust_shipto_terms.SEQ_FIRM_DAY_FROM,
1113 cust_shipto_terms.SEQ_FIRM_DAY_TO,
1114 cust_shipto_terms.SEQ_FORECAST_DAY_TO,
1118 cust_shipto_terms.SHP_FIRM_DAY_FROM,
1115 cust_shipto_terms.SEQ_FORECAST_DAY_FROM,
1116 cust_shipto_terms.SEQ_FROZEN_DAY_FROM,
1117 cust_shipto_terms.SEQ_FROZEN_DAY_TO,
1119 cust_shipto_terms.SHP_FIRM_DAY_TO,
1120 cust_shipto_terms.SHP_FROZEN_DAY_TO,
1121 cust_shipto_terms.SHP_FROZEN_DAY_FROM,
1122 cust_shipto_terms.SHP_FORECAST_DAY_TO,
1123 cust_shipto_terms.ROUND_TO_STD_PACK_FLAG,
1124 cust_shipto_terms.SUPPLIER_CONTACT_ID,
1125 cust_shipto_terms.AGREEMENT_NAME,
1126 cust_shipto_terms.STD_PACK_QTY,
1127 cust_shipto_terms.FUTURE_AGREEMENT_NAME,
1128 cust_shipto_terms.SHP_FORECAST_DAY_FROM,
1129 cust_shipto_terms.SCHEDULE_HIERARCHY_CODE,
1130 cust_shipto_terms.COMMENTS,
1131 cust_shipto_terms.LAST_UPDATED_BY,
1132 cust_shipto_terms.LAST_UPDATE_DATE,
1133 cust_shipto_terms.CREATION_DATE,
1134 cust_shipto_terms.CREATED_BY,
1135 cust_shipto_terms.ATTRIBUTE_CATEGORY,
1136 cust_shipto_terms.ATTRIBUTE1,
1137 cust_shipto_terms.ATTRIBUTE2,
1138 cust_shipto_terms.ATTRIBUTE3,
1139 cust_shipto_terms.ATTRIBUTE4,
1140 cust_shipto_terms.ATTRIBUTE5,
1141 cust_shipto_terms.ATTRIBUTE6,
1142 cust_shipto_terms.ATTRIBUTE7,
1143 cust_shipto_terms.ATTRIBUTE8,
1144 cust_shipto_terms.ATTRIBUTE9,
1145 cust_shipto_terms.ATTRIBUTE10,
1146 cust_shipto_terms.ATTRIBUTE11,
1147 cust_shipto_terms.ATTRIBUTE12,
1148 cust_shipto_terms.ATTRIBUTE13,
1149 cust_shipto_terms.ATTRIBUTE14,
1150 cust_shipto_terms.ATTRIBUTE15,
1151 cust_shipto_terms.LAST_UPDATE_LOGIN,
1152 cust_shipto_terms.REQUEST_ID,
1153 cust_shipto_terms.PROGRAM_APPLICATION_ID,
1154 cust_shipto_terms.PROGRAM_ID,
1155 cust_shipto_terms.PROGRAM_UPDATE_DATE,
1156 cust_shipto_terms.TP_ATTRIBUTE1,
1157 cust_shipto_terms.TP_ATTRIBUTE2,
1158 cust_shipto_terms.TP_ATTRIBUTE3,
1159 cust_shipto_terms.TP_ATTRIBUTE4,
1160 cust_shipto_terms.TP_ATTRIBUTE5,
1161 cust_shipto_terms.TP_ATTRIBUTE6,
1162 cust_shipto_terms.TP_ATTRIBUTE7,
1163 cust_shipto_terms.TP_ATTRIBUTE8,
1164 cust_shipto_terms.TP_ATTRIBUTE9,
1165 cust_shipto_terms.TP_ATTRIBUTE10,
1166 cust_shipto_terms.TP_ATTRIBUTE11,
1167 cust_shipto_terms.TP_ATTRIBUTE12,
1168 cust_shipto_terms.TP_ATTRIBUTE13,
1169 cust_shipto_terms.TP_ATTRIBUTE14,
1170 cust_shipto_terms.TP_ATTRIBUTE15,
1171 cust_shipto_terms.TP_ATTRIBUTE_CATEGORY,
1172 cust_shipto_terms.MATCH_ACROSS_KEY,
1173 cust_shipto_terms.MATCH_WITHIN_KEY,
1174 cust_shipto_terms.PLN_MRP_FORECAST_DAY_FROM,
1175 cust_shipto_terms.PLN_MRP_FORECAST_DAY_TO,
1176 cust_shipto_terms.SHP_MRP_FORECAST_DAY_FROM,
1177 cust_shipto_terms.SHP_MRP_FORECAST_DAY_TO,
1178 cust_shipto_terms.SEQ_MRP_FORECAST_DAY_FROM,
1179 cust_shipto_terms.SEQ_MRP_FORECAST_DAY_TO,
1180 cust_shipto_terms.INTRANSIT_CALC_BASIS,
1181 cust_shipto_terms.DEFAULT_SHIP_FROM,
1182 cust_shipto_terms.PLN_FROZEN_FLAG,
1183 cust_shipto_terms.SHP_FROZEN_FLAG,
1184 cust_shipto_terms.SEQ_FROZEN_FLAG,
1185 cust_shipto_terms.ISSUE_WARNING_DROP_PARTS_FLAG,
1186 cust_shipto_terms.ORG_ID,
1187 cust_shipto_terms.BLANKET_NUMBER,
1188 cust_shipto_terms.RELEASE_RULE,
1189 cust_shipto_terms.RELEASE_TIME_FRAME,
1190 cust_shipto_terms.RELEASE_TIME_FRAME_UOM,
1191 cust_shipto_terms.AGREEMENT_ID,
1192 cust_shipto_terms.FUTURE_AGREEMENT_ID,
1193 cust_shipto_terms.EXCLUDE_NON_WORKDAYS_FLAG,
1194 cust_shipto_terms.DISABLE_CREATE_CUM_KEY_FLAG,
1195 'D',
1196 req_id,
1197 hz_utility_pub.CREATED_BY,
1198 hz_utility_pub.CREATION_DATE,
1199 hz_utility_pub.LAST_UPDATE_LOGIN,
1200 hz_utility_pub.LAST_UPDATE_DATE,
1201 hz_utility_pub.LAST_UPDATED_BY);
1202
1203 END LOOP;
1204 --
1205 END LOOP;
1206 --
1207 END IF;
1208
1209 IF p_duplicateIdTab.COUNT <> 0 THEN
1210 --
1211 setARMessageDeleteTable('RLM_CUST_SHIPTO_TERMS');
1212
1213 --
1214 FORALL i IN p_duplicateIdTab.FIRST..p_duplicateIdTab.LAST
1215
1216 DELETE RLM_CUST_SHIPTO_TERMS
1217 WHERE (address_id = p_duplicateAddressIdTab(i)
1218 OR customer_id = DECODE(p_duplicateIdTab(i),
1219 p_customerIdTab(i),
1220 -3.1428571,
1221 p_duplicateIdTab(i)
1222 ));
1223 --
1224 setARMessageRowCount( SQL%ROWCOUNT );
1225 --
1226 END IF;
1227 --
1228 END IF;
1229 --
1230 arp_message.set_line('RLM_CUST_MERGE.Cust_Shipto_Terms()-' || getTimeStamp);
1231 --
1232 EXCEPTION
1233 --
1234 WHEN others THEN
1235 --
1236 arp_message.set_error('RLM_CUST_MERGE.Cust_Shipto_Terms');
1237 RAISE;
1238 --
1239 END Cust_Shipto_Terms;
1240
1241 /*==========================================================================
1242
1243 PROCEDURE Cust_Item_Terms
1244
1245 ===========================================================================*/
1246 PROCEDURE Cust_Item_Terms(p_duplicateAddressIdTab g_number_tbl_type,
1247 p_customerAddressIdTab g_number_tbl_type,
1248 p_duplicateIdTab g_number_tbl_type,
1249 p_customerIdTab g_number_tbl_type,
1250 REQ_ID NUMBER,
1251 SET_NUM NUMBER,
1252 PROCESS_MODE VARCHAR2)
1256 CURSOR cust_merge_cur IS
1253 --
1254 IS
1255 --
1257 SELECT address_id,
1258 customer_id
1259 FROM RLM_CUST_ITEM_TERMS
1260 WHERE customer_id in
1261 (select m.duplicate_id
1262 from ra_customer_merges m
1263 where m.process_flag = 'N'
1264 and m.request_id = req_id
1265 and m.set_number = set_num)
1266 OR address_id in
1267 (select m.duplicate_address_id
1268 from ra_customer_merges m
1269 where m.process_flag = 'N'
1270 and m.request_id = req_id
1271 and m.set_number = set_num)
1272 FOR update nowait;
1273
1274 /*2447493*/
1275 CURSOR merge_records_cur IS
1276 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1277 FROM rlm_cust_shipto_terms yt,ra_customer_merges m
1278 where yt.customer_id = m.duplicate_id
1279 AND m.process_flag = 'N'
1280 AND m.request_id = req_id
1281 AND m.set_number = set_num;
1282
1283 CURSOR cust_item_terms_cur is
1284 SELECT * from rlm_cust_item_terms
1285 where customer_id in
1286 (select m.duplicate_id
1287 from ra_customer_merges m
1288 where m.process_flag = 'N'
1289 and m.request_id = req_id
1290 and m.set_number = set_num)
1291 OR address_id in
1292 (select m.duplicate_address_id
1293 from ra_customer_merges m
1294 where m.process_flag = 'N'
1295 and m.request_id = req_id
1296 and m.set_number = set_num)
1297 FOR update nowait;
1298
1299
1300 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1301 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1302 INDEX BY BINARY_INTEGER;
1303 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1304
1305
1306 --
1307 --
1308 i NUMBER;
1309 l_last_fetch BOOLEAN := FALSE;
1310 --
1311 --
1312 BEGIN
1313 --
1314 arp_message.set_line('RLM_CUST_MERGE.Cust_Item_Terms()+' || getTimeStamp);
1315 --
1316 IF (process_mode = 'LOCK') THEN
1317 --
1318 setARMessageLockTable('RLM_CUST_ITEM_TERMS');
1319 --
1320 open cust_merge_cur;
1321 --
1322 close cust_merge_cur;
1323 --
1324 ELSE
1325 --
1326 open merge_records_cur;
1327
1328 FETCH merge_records_cur BULK COLLECT INTO
1329 MERGE_HEADER_ID_LIST
1330 limit 1000;
1331
1332 IF merge_records_cur%NOTFOUND THEN
1333 l_last_fetch := TRUE;
1334 END IF;
1335
1336 close merge_records_cur;
1337
1338 /* IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1339 exit;
1340 END IF;*/
1341
1342 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1343 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT
1344 LOOP
1345 --
1346 FOR cust_item_terms in cust_item_terms_cur
1347 --
1348 LOOP
1349
1350 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1351 MERGE_LOG_ID,
1352 TABLE_NAME,
1353 MERGE_HEADER_ID,
1354 DEL_COL1,
1355 DEL_COL2,
1356 DEL_COL3 ,
1357 DEL_COL4 ,
1358 DEL_COL5,
1359 DEL_COL6,
1360 DEL_COL7,
1361 DEL_COL8,
1362 DEL_COL9,
1363 DEL_COL10,
1364 DEL_COL11,
1365 DEL_COL12,
1366 DEL_COL13,
1367 DEL_COL14,
1368 DEL_COL15,
1369 DEL_COL16,
1370 DEL_COL17,
1371 DEL_COL18,
1372 DEL_COL19,
1373 DEL_COL20,
1374 DEL_COL21,
1375 DEL_COL22,
1376 DEL_COL23,
1377 DEL_COL24,
1378 DEL_COL25,
1379 DEL_COL26,
1380 DEL_COL27,
1381 DEL_COL28,
1382 DEL_COL29,
1383 DEL_COL30,
1384 DEL_COL31,
1385 DEL_COL32,
1386 DEL_COL33,
1387 DEL_COL34 ,
1388 DEL_COL35,
1389 DEL_COL36,
1390 DEL_COL37,
1391 DEL_COL38,
1392 DEL_COL39,
1393 DEL_COL40,
1394 DEL_COL41,
1395 DEL_COL42,
1396 DEL_COL43,
1397 DEL_COL44,
1398 DEL_COL45,
1399 DEL_COL46,
1400 DEL_COL47,
1401 DEL_COL48,
1402 DEL_COL49,
1403 DEL_COL50,
1404 DEL_COL51,
1405 DEL_COL52,
1406 DEL_COL53,
1407 DEL_COL54,
1408 DEL_COL55,
1409 DEL_COL56,
1410 DEL_COL57,
1411 DEL_COL58,
1412 DEL_COL59,
1413 DEL_COL60,
1414 DEL_COL61,
1415 DEL_COL62,
1416 DEL_COL63,
1417 DEL_COL64,
1418 DEL_COL65,
1419 DEL_COL66,
1420 DEL_COL67,
1421 DEL_COL68,
1422 DEL_COL69,
1423 DEL_COL70,
1424 DEL_COL71,
1425 DEL_COL72,
1426 DEL_COL73,
1427 DEL_COL74,
1428 DEL_COL75,
1429 DEL_COL76,
1430 DEL_COL77,
1431 DEL_COL78,
1432 DEL_COL79,
1433 DEL_COL80,
1434 DEL_COL81,
1435 DEL_COL82,
1436 DEL_COL83,
1437 DEL_COL84,
1438 DEL_COL85,
1439 DEL_COL86,
1440 DEL_COL87,
1441 DEL_COL88,
1442 DEL_COL89,
1443 DEL_COL90,
1447 DEL_COL94,
1444 DEL_COL91,
1445 DEL_COL92,
1446 DEL_COL93,
1448 DEL_COL95,
1449 DEL_COL96,
1450 DEL_COL97,
1451 DEL_COL98,
1452 DEL_COL99,
1453 DEL_COL100,
1454 DEL_COL101,
1455 DEL_COL102,
1456 DEL_COL103,
1457 DEL_COL104,
1458 DEL_COL105,
1459 DEL_COL106,
1460 ACTION_FLAG,
1461 REQUEST_ID,
1462 CREATED_BY,
1463 CREATION_DATE,
1464 LAST_UPDATE_LOGIN,
1465 LAST_UPDATE_DATE,
1466 LAST_UPDATED_BY)
1467 Values
1468 ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1469 'RLM_CUST_ITEM_TERMS',
1470 MERGE_HEADER_ID_LIST(I),
1471 cust_item_terms.CUST_ITEM_TERMS_ID,
1472 cust_item_terms.CUSTOMER_ITEM_ID,
1473 cust_item_terms.SHIP_FROM_ORG_ID,
1474 cust_item_terms.ADDRESS_ID,
1475 cust_item_terms.HEADER_ID,
1476 cust_item_terms.AGREEMENT_NAME,
1477 cust_item_terms.FUTURE_AGREEMENT_NAME,
1478 cust_item_terms.CALC_CUM_FLAG,
1479 cust_item_terms.CUM_CURRENT_START_DATE,
1480 cust_item_terms.CUM_PREVIOUS_START_DATE,
1481 cust_item_terms.CUST_ITEM_STATUS_CODE,
1482 cust_item_terms.ROUND_TO_STD_PACK_FLAG,
1483 cust_item_terms.SHIP_DELIVERY_RULE_NAME,
1484 cust_item_terms.SHIP_METHOD,
1485 cust_item_terms.INTRANSIT_TIME,
1486 cust_item_terms.STD_PACK_QTY,
1487 cust_item_terms.TIME_UOM_CODE,
1488 cust_item_terms.PRICE_LIST_ID,
1489 cust_item_terms.DEMAND_TOLERANCE_ABOVE,
1490 cust_item_terms.USE_EDI_SDP_CODE_FLAG,
1491 cust_item_terms.LAST_UPDATE_DATE,
1492 cust_item_terms.LAST_UPDATED_BY,
1493 cust_item_terms.CREATION_DATE,
1494 cust_item_terms.CREATED_BY,
1495 cust_item_terms.PLN_FIRM_DAY_TO,
1496 cust_item_terms.ATTRIBUTE_CATEGORY,
1497 cust_item_terms.PLN_FORECAST_DAY_FROM,
1498 cust_item_terms.PLN_FORECAST_DAY_TO,
1499 cust_item_terms.PLN_FROZEN_DAY_TO,
1500 cust_item_terms.PLN_FROZEN_DAY_FROM,
1501 cust_item_terms.ATTRIBUTE1,
1502 cust_item_terms.SEQ_FIRM_DAY_FROM,
1503 cust_item_terms.SEQ_FIRM_DAY_TO,
1504 cust_item_terms.ATTRIBUTE2,
1505 cust_item_terms.SEQ_FORECAST_DAY_TO,
1506 cust_item_terms.SEQ_FORECAST_DAY_FROM,
1507 cust_item_terms.ATTRIBUTE3,
1508 cust_item_terms.SEQ_FROZEN_DAY_FROM,
1509 cust_item_terms.SEQ_FROZEN_DAY_TO,
1510 cust_item_terms.ATTRIBUTE4,
1511 cust_item_terms.SHP_FIRM_DAY_FROM,
1512 cust_item_terms.SHP_FIRM_DAY_TO,
1513 cust_item_terms.ATTRIBUTE5,
1514 cust_item_terms.SHP_FROZEN_DAY_TO,
1515 cust_item_terms.SHP_FROZEN_DAY_FROM,
1516 cust_item_terms.ATTRIBUTE6,
1517 cust_item_terms.SHP_FORECAST_DAY_TO,
1518 cust_item_terms.SHP_FORECAST_DAY_FROM,
1519 cust_item_terms.ATTRIBUTE7,
1520 cust_item_terms.ATTRIBUTE8,
1521 cust_item_terms.ATTRIBUTE9,
1522 cust_item_terms.ATTRIBUTE10,
1523 cust_item_terms.ATTRIBUTE11,
1524 cust_item_terms.ATTRIBUTE12,
1525 cust_item_terms.ATTRIBUTE13,
1526 cust_item_terms.ATTRIBUTE14,
1527 cust_item_terms.ATTRIBUTE15,
1528 cust_item_terms.LAST_UPDATE_LOGIN,
1529 cust_item_terms.REQUEST_ID,
1530 cust_item_terms.PROGRAM_APPLICATION_ID,
1531 cust_item_terms.PROGRAM_ID,
1532 cust_item_terms.PROGRAM_UPDATE_DATE,
1533 cust_item_terms.DEMAND_TOLERANCE_BELOW,
1534 cust_item_terms.CUSTOMER_CONTACT_ID,
1535 cust_item_terms.CUSTOMER_ID,
1536 cust_item_terms.FREIGHT_CODE,
1537 cust_item_terms.PLN_FIRM_DAY_FROM,
1538 cust_item_terms.SUPPLIER_CONTACT_ID,
1539 cust_item_terms.TP_ATTRIBUTE1,
1540 cust_item_terms.TP_ATTRIBUTE2,
1541 cust_item_terms.TP_ATTRIBUTE3,
1542 cust_item_terms.TP_ATTRIBUTE4,
1543 cust_item_terms.TP_ATTRIBUTE5,
1544 cust_item_terms.TP_ATTRIBUTE6,
1545 cust_item_terms.TP_ATTRIBUTE7,
1546 cust_item_terms.TP_ATTRIBUTE8,
1547 cust_item_terms.TP_ATTRIBUTE9,
1548 cust_item_terms.TP_ATTRIBUTE10,
1549 cust_item_terms.TP_ATTRIBUTE11,
1550 cust_item_terms.TP_ATTRIBUTE12,
1551 cust_item_terms.TP_ATTRIBUTE13,
1552 cust_item_terms.TP_ATTRIBUTE14,
1553 cust_item_terms.TP_ATTRIBUTE15,
1554 cust_item_terms.TP_ATTRIBUTE_CATEGORY,
1555 cust_item_terms.INACTIVE_DATE,
1556 cust_item_terms.COMMENTS,
1557 cust_item_terms.DEFAULT_SHIP_FROM,
1558 cust_item_terms.PLN_MRP_FORECAST_DAY_FROM,
1559 cust_item_terms.PLN_MRP_FORECAST_DAY_TO,
1560 cust_item_terms.SHP_MRP_FORECAST_DAY_FROM,
1561 cust_item_terms.SHP_MRP_FORECAST_DAY_TO,
1562 cust_item_terms.SEQ_MRP_FORECAST_DAY_FROM,
1563 cust_item_terms.SEQ_MRP_FORECAST_DAY_TO,
1564 cust_item_terms.PLN_FROZEN_FLAG,
1565 cust_item_terms.SHP_FROZEN_FLAG,
1566 cust_item_terms.SEQ_FROZEN_FLAG,
1567 cust_item_terms.ISSUE_WARNING_DROP_PARTS_FLAG,
1568 cust_item_terms.ORG_ID,
1569 cust_item_terms.BLANKET_NUMBER,
1570 cust_item_terms.RELEASE_RULE,
1571 cust_item_terms.RELEASE_TIME_FRAME,
1572 cust_item_terms.RELEASE_TIME_FRAME_UOM,
1573 cust_item_terms.AGREEMENT_ID,
1574 cust_item_terms.FUTURE_AGREEMENT_ID,
1575 cust_item_terms.EXCLUDE_NON_WORKDAYS_FLAG,
1576 cust_item_terms.DISABLE_CREATE_CUM_KEY_FLAG,
1577 'D',
1578 req_id,
1579 hz_utility_pub.CREATED_BY,
1580 hz_utility_pub.CREATION_DATE,
1581 hz_utility_pub.LAST_UPDATE_LOGIN,
1582 hz_utility_pub.LAST_UPDATE_DATE,
1583 hz_utility_pub.LAST_UPDATED_BY);
1584 END LOOP;
1588 END IF;
1585 --
1586 END LOOP;
1587 --
1589
1590 IF p_duplicateIdTab.COUNT <> 0 THEN
1591 --
1592 setARMessageDeleteTable('RLM_CUST_ITEM_TERMS');
1593 --
1594 FORALL i IN p_duplicateIdTab.FIRST..p_duplicateIdTab.LAST
1595 --
1596 DELETE RLM_CUST_ITEM_TERMS
1597 WHERE (address_id = p_duplicateAddressIdTab(i)
1598 OR customer_id = DECODE(p_duplicateIdTab(i),
1599 p_customerIdTab(i),
1600 -3.1428571,
1601 p_duplicateIdTab(i)
1602 ));
1603 --
1604 setARMessageRowCount( SQL%ROWCOUNT );
1605 --
1606 END IF;
1607 --
1608 END IF;
1609 --
1610 arp_message.set_line('RLM_CUST_MERGE.Cust_Item_Terms()-' || getTimeStamp);
1611 --
1612 EXCEPTION
1613 --
1614 WHEN others THEN
1615 --
1616 arp_message.set_error('RLM_CUST_MERGE.Cust_Item_Terms');
1617 RAISE;
1618 --
1619 END Cust_Item_Terms;
1620 /*============================================================================
1621
1622 FUNCTION getTimeStamp
1623
1624 ============================================================================*/
1625 FUNCTION getTimeStamp RETURN VARCHAR2
1626 --
1627 IS
1628 --
1629 BEGIN
1630 --
1631 RETURN TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS');
1632 --
1633 EXCEPTION
1634 --
1635 WHEN OTHERS THEN
1636 --
1637 RAISE;
1638 --
1639 END getTimeStamp;
1640
1641 /*============================================================================
1642
1643 PROCEDURE setARMessageUpdateTable
1644
1645 ============================================================================*/
1646 PROCEDURE setARMessageUpdateTable(p_tableName IN VARCHAR2)
1647 --
1648 IS
1649 --
1650 BEGIN
1651 --
1652 arp_message.set_name('AR','AR_UPDATING_TABLE');
1653 arp_message.set_token('TABLE_NAME', p_tableName, FALSE);
1654 --
1655 EXCEPTION
1656 --
1657 WHEN OTHERS THEN
1658 --
1659 RAISE;
1660 --
1661 END setARMessageUpdateTable;
1662 /*============================================================================
1663
1664 PROCEDURE setARMessageDeleteTable
1665
1666 ============================================================================*/
1667 PROCEDURE setARMessageDeleteTable(p_tableName IN VARCHAR2)
1668 --
1669 IS
1670 --
1671 BEGIN
1672 --
1673 arp_message.set_name('AR','AR_DELETING_TABLE');
1674 arp_message.set_token('TABLE_NAME', p_tableName, FALSE);
1675 --
1676 EXCEPTION
1677 --
1678 WHEN OTHERS THEN
1679 --
1680 RAISE;
1681 --
1682 END setARMessageDeleteTable;
1683 /*============================================================================
1684
1685 PROCEDURE setARMessageLockTable
1686
1687 ============================================================================*/
1688 PROCEDURE setARMessageLockTable(p_tableName IN VARCHAR2)
1689 --
1690 IS
1691 --
1692 BEGIN
1693 --
1694 arp_message.set_name('AR','AR_LOCKING_TABLE');
1695 arp_message.set_token('TABLE_NAME', p_tableName, FALSE);
1696 --
1697 EXCEPTION
1698 --
1699 WHEN OTHERS THEN
1700 --
1701 RAISE;
1702 --
1703 END setARMessageLockTable;
1704
1705 /*============================================================================
1706
1707 PROCEDURE setARMessageRowCount
1708
1709 ============================================================================*/
1710 PROCEDURE setARMessageRowCount(p_rowCount IN NUMBER)
1711 --
1712 IS
1713 --
1714 BEGIN
1715 --
1716 arp_message.set_name('AR','AR_ROWS_UPDATED');
1717 arp_message.set_token('NUM_ROWS', to_char(p_rowCount));
1718 --
1719 EXCEPTION
1720 --
1721 WHEN OTHERS THEN
1722 --
1723 RAISE;
1724 --
1725 END setARMessageRowCount;
1726
1727 /*============================================================================
1728
1729 FUNCTION setARMessageRowCount
1730
1731 ============================================================================*/
1732 FUNCTION getMessage
1733 (
1734 p_messageName IN VARCHAR2,
1735 p_token1 IN VARCHAR2,
1736 p_value1 IN VARCHAR2,
1737 p_token2 IN VARCHAR2,
1738 p_value2 IN VARCHAR2,
1739 p_token3 IN VARCHAR2,
1740 p_value3 IN VARCHAR2
1741 )
1742 RETURN VARCHAR2
1743 --
1744 IS
1745 --
1746 BEGIN
1747 --
1748 FND_MESSAGE.SET_NAME('RLM',p_messageName);
1749 --
1750 --
1751 --
1752 IF p_token1 IS NOT NULL
1753 AND p_value1 IS NOT NULL
1754 THEN
1755 FND_MESSAGE.SET_TOKEN(p_token1, p_value1);
1756 END IF;
1757 --
1758 --
1759 IF p_token2 IS NOT NULL
1760 AND p_value2 IS NOT NULL
1761 THEN
1762 FND_MESSAGE.SET_TOKEN(p_token2, p_value2);
1763 END IF;
1764 --
1765 --
1766 IF p_token3 IS NOT NULL
1767 AND p_value3 IS NOT NULL
1768 THEN
1772 --
1769 FND_MESSAGE.SET_TOKEN(p_token3, p_value3);
1770 END IF;
1771 --
1773 RETURN( FND_MESSAGE.GET );
1774 --
1775 EXCEPTION
1776 --
1777 WHEN OTHERS THEN
1778 --
1779 RAISE;
1780 --
1781 END getMessage;
1782
1783 /*---------------------------------------------------------------------------
1784 NOTE Values for installation status are
1785 I - Product is installed
1786 S - Product is partially installed
1787 N - Product is not installed
1788 L - Product is a local (custom) application
1789 ----------------------------------------------------------------------------*/
1790 /*===========================================================================
1791
1792 FUNCTION IS_RLM_INSTALLED
1793
1794 ===========================================================================*/
1795 FUNCTION IS_RLM_INSTALLED
1796 RETURN BOOLEAN
1797 --
1798 IS
1799 --
1800 x_install BOOLEAN;
1801 rlm_status VARCHAR2(1);
1802 x_org VARCHAR2(1);
1803 BEGIN
1804 --
1805 x_install := fnd_installation.get(662,662,rlm_status,x_org);
1806 --
1807 IF rlm_status = 'I'
1808 THEN
1809 --
1810 RETURN TRUE;
1811 --
1812 ELSE
1813 --
1814 arp_message.set_line(getMessage('RLM_INSTALL_STATUS'));
1815 RETURN FALSE;
1816 --
1817 END IF;
1818 --
1819 END IS_RLM_INSTALLED;
1820
1821 /*2447493*/
1822
1823 PROCEDURE RLM_CUST_ITEM_CUM_KEYS_LOG (
1824 req_id NUMBER,
1825 set_num NUMBER,
1826 process_mode VARCHAR2) IS
1827
1828 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1829 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1830 INDEX BY BINARY_INTEGER;
1831 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1832
1833 TYPE CUM_KEY_ID_LIST_TYPE IS TABLE OF
1834 RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE
1835 INDEX BY BINARY_INTEGER;
1836 PRIMARY_KEY_ID_LIST CUM_KEY_ID_LIST_TYPE;
1837
1838 TYPE INTRMD_SHIP_TO_ID_LIST_TYPE IS TABLE OF
1839 RLM_CUST_ITEM_CUM_KEYS.INTRMD_SHIP_TO_ID%TYPE
1840 INDEX BY BINARY_INTEGER;
1841 NUM_COL1_ORIG_LIST INTRMD_SHIP_TO_ID_LIST_TYPE;
1842 NUM_COL1_NEW_LIST INTRMD_SHIP_TO_ID_LIST_TYPE;
1843
1844 TYPE SHIP_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
1845 RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE
1846 INDEX BY BINARY_INTEGER;
1847 NUM_COL2_ORIG_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
1848 NUM_COL2_NEW_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
1849
1850 TYPE BILL_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
1851 RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE
1852 INDEX BY BINARY_INTEGER;
1853 NUM_COL3_ORIG_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
1854 NUM_COL3_NEW_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
1855
1856 TYPE CUM_NOTE_TEXT_LIST_TYPE IS TABLE OF
1857 RLM_CUST_ITEM_CUM_KEYS.CUM_NOTE_TEXT%TYPE
1858 INDEX BY BINARY_INTEGER;
1859 VCHAR_COL1_ORIG_LIST CUM_NOTE_TEXT_LIST_TYPE;
1860 VCHAR_COL1_NEW_LIST CUM_NOTE_TEXT_LIST_TYPE;
1861
1862 TYPE INACTIVE_FLAG_LIST_TYPE IS TABLE OF
1863 RLM_CUST_ITEM_CUM_KEYS.INACTIVE_FLAG%TYPE
1864 INDEX BY BINARY_INTEGER;
1865 VCHAR_COL2_ORIG_LIST INACTIVE_FLAG_LIST_TYPE;
1866 VCHAR_COL2_NEW_LIST INACTIVE_FLAG_LIST_TYPE;
1867
1868 CURSOR merged_records IS
1869 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1870 ,CUM_KEY_ID
1871 ,INTRMD_SHIP_TO_ID
1872 ,SHIP_TO_ADDRESS_ID
1873 ,BILL_TO_ADDRESS_ID
1874 ,CUM_NOTE_TEXT
1875 ,INACTIVE_FLAG
1876 FROM RLM_CUST_ITEM_CUM_KEYS yt, ra_customer_merges m
1877 WHERE (
1878 yt.INTRMD_SHIP_TO_ID = m.DUPLICATE_ADDRESS_ID
1879 OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
1880 OR yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
1881 ) AND m.process_flag = 'N'
1882 AND m.request_id = req_id
1883 AND m.set_number = set_num;
1884 l_last_fetch BOOLEAN := FALSE;
1885
1886 BEGIN
1887 IF process_mode='LOCK' THEN
1888 NULL;
1889 ELSE
1890 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1891 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RLM_CUST_ITEM_CUM_KEYS',FALSE);
1892 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1893
1894 open merged_records;
1895 LOOP
1896 FETCH merged_records BULK COLLECT INTO
1897 MERGE_HEADER_ID_LIST
1898 , PRIMARY_KEY_ID_LIST
1899 , NUM_COL1_ORIG_LIST
1900 , NUM_COL2_ORIG_LIST
1901 , NUM_COL3_ORIG_LIST
1902 , VCHAR_COL1_ORIG_LIST
1903 , VCHAR_COL2_ORIG_LIST
1904 limit 1000
1905 ;
1906 IF merged_records%NOTFOUND THEN
1907 l_last_fetch := TRUE;
1908 END IF;
1909 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1910 exit;
1911 END IF;
1912 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1913 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
1914
1918
1915 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
1916
1917 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL3_ORIG_LIST(I));
1919 VCHAR_COL1_NEW_LIST(I) := VCHAR_COL1_ORIG_LIST(I);
1920 VCHAR_COL2_NEW_LIST(I) := VCHAR_COL2_ORIG_LIST(I);
1921 END LOOP;
1922 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1923 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1924 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1925 MERGE_LOG_ID,
1926 TABLE_NAME,
1927 MERGE_HEADER_ID,
1928 PRIMARY_KEY_ID,
1929 NUM_COL1_ORIG,
1930 NUM_COL1_NEW,
1931 NUM_COL2_ORIG,
1932 NUM_COL2_NEW,
1933 NUM_COL3_ORIG,
1934 NUM_COL3_NEW,
1935 VCHAR_COL1_ORIG,
1936 VCHAR_COL1_NEW,
1937 VCHAR_COL2_ORIG,
1938 VCHAR_COL2_NEW,
1939 ACTION_FLAG,
1940 REQUEST_ID,
1941 CREATED_BY,
1942 CREATION_DATE,
1943 LAST_UPDATE_LOGIN,
1944 LAST_UPDATE_DATE,
1945 LAST_UPDATED_BY
1946 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1947 'RLM_CUST_ITEM_CUM_KEYS',
1948 MERGE_HEADER_ID_LIST(I),
1949 PRIMARY_KEY_ID_LIST(I),
1950 NUM_COL1_ORIG_LIST(I),
1951 NUM_COL1_NEW_LIST(I),
1952 NUM_COL2_ORIG_LIST(I),
1953 NUM_COL2_NEW_LIST(I),
1954 NUM_COL3_ORIG_LIST(I),
1955 NUM_COL3_NEW_LIST(I),
1956 VCHAR_COL1_ORIG_LIST(I),
1957 VCHAR_COL1_NEW_LIST(I),
1958 VCHAR_COL2_ORIG_LIST(I),
1959 VCHAR_COL2_NEW_LIST(I),
1960 'U',
1961 req_id,
1962 hz_utility_pub.CREATED_BY,
1963 hz_utility_pub.CREATION_DATE,
1964 hz_utility_pub.LAST_UPDATE_LOGIN,
1965 hz_utility_pub.LAST_UPDATE_DATE,
1966 hz_utility_pub.LAST_UPDATED_BY
1967 );
1968
1969 END IF;
1970 IF l_last_fetch THEN
1971 EXIT;
1972 END IF;
1973 END LOOP;
1974
1975 END IF;
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 arp_message.set_line( 'RLM_CUST_ITEM_CUM_KEYS_LOG');
1979 RAISE;
1980 END RLM_CUST_ITEM_CUM_KEYS_LOG;
1981
1982 PROCEDURE RLM_INTERFACE_HEADERS_LOG (
1983 req_id NUMBER,
1984 set_num NUMBER,
1985 process_mode VARCHAR2) IS
1986
1987 cust_name varchar2(100);
1988
1989 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1990 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1991 INDEX BY BINARY_INTEGER;
1992 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1993
1994 TYPE HEADER_ID_LIST_TYPE IS TABLE OF
1995 RLM_INTERFACE_HEADERS.HEADER_ID%TYPE
1996 INDEX BY BINARY_INTEGER;
1997 PRIMARY_KEY_ID_LIST HEADER_ID_LIST_TYPE;
1998
1999 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
2000 RLM_INTERFACE_HEADERS.CUSTOMER_ID%TYPE
2001 INDEX BY BINARY_INTEGER;
2002 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
2003 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
2004
2005 TYPE ECE_PRIMARY_ADD_ID_LIST_TYPE IS TABLE OF
2006 RLM_INTERFACE_HEADERS.ECE_PRIMARY_ADDRESS_ID%TYPE
2007 INDEX BY BINARY_INTEGER;
2008 NUM_COL2_ORIG_LIST ECE_PRIMARY_ADD_ID_LIST_TYPE;
2009 NUM_COL2_NEW_LIST ECE_PRIMARY_ADD_ID_LIST_TYPE;
2010
2011 TYPE CUST_NAME_EXT_LIST_TYPE IS TABLE OF
2012 RLM_INTERFACE_HEADERS.CUST_NAME_EXT%TYPE
2013 INDEX BY BINARY_INTEGER;
2014 VCHAR_COL1_ORIG_LIST CUST_NAME_EXT_LIST_TYPE;
2015 VCHAR_COL1_NEW_LIST CUST_NAME_EXT_LIST_TYPE;
2016
2017 TYPE HEADER_NOTE_TEXT_LIST_TYPE IS TABLE OF
2018 RLM_INTERFACE_HEADERS.HEADER_NOTE_TEXT%TYPE
2019 INDEX BY BINARY_INTEGER;
2020 VCHAR_COL2_ORIG_LIST HEADER_NOTE_TEXT_LIST_TYPE;
2021 VCHAR_COL2_NEW_LIST HEADER_NOTE_TEXT_LIST_TYPE;
2022
2023 CURSOR merged_records IS
2024 SELECT distinct CUSTOMER_MERGE_HEADER_ID
2025 ,HEADER_ID
2026 ,yt.CUSTOMER_ID
2027 ,ECE_PRIMARY_ADDRESS_ID
2028 ,CUST_NAME_EXT
2029 ,HEADER_NOTE_TEXT
2030 FROM RLM_INTERFACE_HEADERS yt, ra_customer_merges m
2031 WHERE (
2032 yt.CUSTOMER_ID = m.DUPLICATE_ID
2033 OR yt.ECE_PRIMARY_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
2034 ) AND m.process_flag = 'N'
2035 AND m.request_id = req_id
2036 AND m.set_number = set_num;
2037 l_last_fetch BOOLEAN := FALSE;
2038 BEGIN
2039 IF process_mode='LOCK' THEN
2040 NULL;
2041 ELSE
2042 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
2043 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RLM_INTERFACE_HEADERS',FALSE);
2044 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2045
2046 open merged_records;
2047 LOOP
2048 FETCH merged_records BULK COLLECT INTO
2049 MERGE_HEADER_ID_LIST
2050 , PRIMARY_KEY_ID_LIST
2051 , NUM_COL1_ORIG_LIST
2052 , NUM_COL2_ORIG_LIST
2053 , VCHAR_COL1_ORIG_LIST
2054 , VCHAR_COL2_ORIG_LIST
2055 limit 1000
2056 ;
2057 IF merged_records%NOTFOUND THEN
2058 l_last_fetch := TRUE;
2059 END IF;
2063 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
2060 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
2061 exit;
2062 END IF;
2064 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
2065 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
2066
2067 select distinct customer_name into cust_name
2068 from ra_customer_merges
2069 where customer_id = NUM_COL1_NEW_LIST(I)
2070 and request_id = req_id;
2071
2072 VCHAR_COL1_NEW_LIST(I) := cust_name;
2073 VCHAR_COL2_NEW_LIST(I) := VCHAR_COL2_ORIG_LIST(I);
2074 END LOOP;
2075 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2076 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
2077 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2078 MERGE_LOG_ID,
2079 TABLE_NAME,
2080 MERGE_HEADER_ID,
2081 PRIMARY_KEY_ID,
2082 NUM_COL1_ORIG,
2083 NUM_COL1_NEW,
2084 NUM_COL2_ORIG,
2085 NUM_COL2_NEW,
2086 VCHAR_COL1_ORIG,
2087 VCHAR_COL1_NEW,
2088 VCHAR_COL2_ORIG,
2089 VCHAR_COL2_NEW,
2090 ACTION_FLAG,
2091 REQUEST_ID,
2092 CREATED_BY,
2093 CREATION_DATE,
2094 LAST_UPDATE_LOGIN,
2095 LAST_UPDATE_DATE,
2096 LAST_UPDATED_BY
2097 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
2098 'RLM_INTERFACE_HEADERS',
2099 MERGE_HEADER_ID_LIST(I),
2100 PRIMARY_KEY_ID_LIST(I),
2101 NUM_COL1_ORIG_LIST(I),
2102 NUM_COL1_NEW_LIST(I),
2103 NUM_COL2_ORIG_LIST(I),
2104 NUM_COL2_NEW_LIST(I),
2105 VCHAR_COL1_ORIG_LIST(I),
2106 VCHAR_COL1_NEW_LIST(I),
2107 VCHAR_COL2_ORIG_LIST(I),
2108 VCHAR_COL2_NEW_LIST(I),
2109 'U',
2110 req_id,
2111 hz_utility_pub.CREATED_BY,
2112 hz_utility_pub.CREATION_DATE,
2113 hz_utility_pub.LAST_UPDATE_LOGIN,
2114 hz_utility_pub.LAST_UPDATE_DATE,
2115 hz_utility_pub.LAST_UPDATED_BY
2116 );
2117
2118 END IF;
2119 IF l_last_fetch THEN
2120 EXIT;
2121 END IF;
2122 END LOOP;
2123 END IF;
2124 EXCEPTION
2125 WHEN OTHERS THEN
2126 arp_message.set_line( 'RLM_INTERFACE_HEADERS_LOG');
2127 RAISE;
2128 END RLM_INTERFACE_HEADERS_LOG;
2129
2130
2131 PROCEDURE RLM_INTERFACE_LINES_LOG (
2132 req_id NUMBER,
2133 set_num NUMBER,
2134 process_mode VARCHAR2) IS
2135
2136 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
2137 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
2138 INDEX BY BINARY_INTEGER;
2139 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
2140
2141 TYPE LINE_ID_LIST_TYPE IS TABLE OF
2142 RLM_INTERFACE_LINES.LINE_ID%TYPE
2143 INDEX BY BINARY_INTEGER;
2144 PRIMARY_KEY_ID_LIST LINE_ID_LIST_TYPE;
2145
2146 TYPE INTRMD_SHIP_TO_ID_LIST_TYPE IS TABLE OF
2147 RLM_INTERFACE_LINES.INTRMD_SHIP_TO_ID%TYPE
2148 INDEX BY BINARY_INTEGER;
2149 NUM_COL1_ORIG_LIST INTRMD_SHIP_TO_ID_LIST_TYPE;
2150 NUM_COL1_NEW_LIST INTRMD_SHIP_TO_ID_LIST_TYPE;
2151
2152 TYPE SHIP_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
2153 RLM_INTERFACE_LINES.SHIP_TO_ADDRESS_ID%TYPE
2154 INDEX BY BINARY_INTEGER;
2155 NUM_COL2_ORIG_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
2156 NUM_COL2_NEW_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
2157
2158 TYPE BILL_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
2159 RLM_INTERFACE_LINES.BILL_TO_ADDRESS_ID%TYPE
2160 INDEX BY BINARY_INTEGER;
2161 NUM_COL3_ORIG_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
2162 NUM_COL3_NEW_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
2163
2164 TYPE SHIP_TO_ORG_ID_LIST_TYPE IS TABLE OF
2165 RLM_INTERFACE_LINES.SHIP_TO_ORG_ID%TYPE
2166 INDEX BY BINARY_INTEGER;
2167 NUM_COL4_ORIG_LIST SHIP_TO_ORG_ID_LIST_TYPE;
2168 NUM_COL4_NEW_LIST SHIP_TO_ORG_ID_LIST_TYPE;
2169
2170 TYPE INVOICE_TO_ORG_ID_LIST_TYPE IS TABLE OF
2171 RLM_INTERFACE_LINES.INVOICE_TO_ORG_ID%TYPE
2172 INDEX BY BINARY_INTEGER;
2173 NUM_COL5_ORIG_LIST INVOICE_TO_ORG_ID_LIST_TYPE;
2174 NUM_COL5_NEW_LIST INVOICE_TO_ORG_ID_LIST_TYPE;
2175
2176 TYPE INT_SHIP_TO_ORG_ID_LIST_TYPE IS TABLE OF
2177 RLM_INTERFACE_LINES.INTMED_SHIP_TO_ORG_ID%TYPE
2178 INDEX BY BINARY_INTEGER;
2179 NUM_COL6_ORIG_LIST INT_SHIP_TO_ORG_ID_LIST_TYPE;
2180 NUM_COL6_NEW_LIST INT_SHIP_TO_ORG_ID_LIST_TYPE;
2181
2182 TYPE ITEM_NOTE_TEXT_LIST_TYPE IS TABLE OF
2183 RLM_INTERFACE_LINES.ITEM_NOTE_TEXT%TYPE
2184 INDEX BY BINARY_INTEGER;
2185 VCHAR_COL1_ORIG_LIST ITEM_NOTE_TEXT_LIST_TYPE;
2186 VCHAR_COL1_NEW_LIST ITEM_NOTE_TEXT_LIST_TYPE;
2187
2188 CURSOR merged_records IS
2189 SELECT distinct CUSTOMER_MERGE_HEADER_ID
2190 ,LINE_ID
2191 ,INTRMD_SHIP_TO_ID
2192 ,SHIP_TO_ADDRESS_ID
2193 ,BILL_TO_ADDRESS_ID
2194 ,SHIP_TO_ORG_ID
2195 ,INVOICE_TO_ORG_ID
2196 ,INTMED_SHIP_TO_ORG_ID
2197 ,ITEM_NOTE_TEXT
2201 OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
2198 FROM RLM_INTERFACE_LINES yt, ra_customer_merges m
2199 WHERE (
2200 yt.INTRMD_SHIP_TO_ID = m.DUPLICATE_ADDRESS_ID
2202 OR yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
2203 OR yt.SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
2204 OR yt.INVOICE_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
2205 OR yt.INTMED_SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
2206 ) AND m.process_flag = 'N'
2207 AND m.request_id = req_id
2208 AND m.set_number = set_num;
2209 l_last_fetch BOOLEAN := FALSE;
2210 BEGIN
2211 IF process_mode='LOCK' THEN
2212 NULL;
2213 ELSE
2214 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
2215 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RLM_INTERFACE_LINES',FALSE);
2216 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2217
2218 open merged_records;
2219 LOOP
2220 FETCH merged_records BULK COLLECT INTO
2221 MERGE_HEADER_ID_LIST
2222 , PRIMARY_KEY_ID_LIST
2223 , NUM_COL1_ORIG_LIST
2224 , NUM_COL2_ORIG_LIST
2225 , NUM_COL3_ORIG_LIST
2226 , NUM_COL4_ORIG_LIST
2227 , NUM_COL5_ORIG_LIST
2228 , NUM_COL6_ORIG_LIST
2229 , VCHAR_COL1_ORIG_LIST
2230 limit 1000
2231 ;
2232 IF merged_records%NOTFOUND THEN
2233 l_last_fetch := TRUE;
2234 END IF;
2235 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
2236 exit;
2237 END IF;
2238 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
2239 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
2240
2241 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
2242
2243 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL3_ORIG_LIST(I));
2244
2245 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL4_ORIG_LIST(I));
2246
2247 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL5_ORIG_LIST(I));
2248
2249 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL6_ORIG_LIST(I));
2250
2251 VCHAR_COL1_NEW_LIST(I) := VCHAR_COL1_ORIG_LIST(I);
2252 END LOOP;
2253 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2254 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
2255 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2256 MERGE_LOG_ID,
2257 TABLE_NAME,
2258 MERGE_HEADER_ID,
2259 PRIMARY_KEY_ID,
2260 NUM_COL1_ORIG,
2261 NUM_COL1_NEW,
2262 NUM_COL2_ORIG,
2263 NUM_COL2_NEW,
2264 NUM_COL3_ORIG,
2265 NUM_COL3_NEW,
2266 NUM_COL4_ORIG,
2267 NUM_COL4_NEW,
2268 NUM_COL5_ORIG,
2269 NUM_COL5_NEW,
2270 NUM_COL6_ORIG,
2271 NUM_COL6_NEW,
2272 VCHAR_COL1_ORIG,
2273 VCHAR_COL1_NEW,
2274 ACTION_FLAG,
2275 REQUEST_ID,
2276 CREATED_BY,
2277 CREATION_DATE,
2278 LAST_UPDATE_LOGIN,
2279 LAST_UPDATE_DATE,
2280 LAST_UPDATED_BY
2281 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
2282 'RLM_INTERFACE_LINES',
2283 MERGE_HEADER_ID_LIST(I),
2284 PRIMARY_KEY_ID_LIST(I),
2285 NUM_COL1_ORIG_LIST(I),
2286 NUM_COL1_NEW_LIST(I),
2287 NUM_COL2_ORIG_LIST(I),
2288 NUM_COL2_NEW_LIST(I),
2289 NUM_COL3_ORIG_LIST(I),
2290 NUM_COL3_NEW_LIST(I),
2291 NUM_COL4_ORIG_LIST(I),
2292 NUM_COL4_NEW_LIST(I),
2293 NUM_COL5_ORIG_LIST(I),
2294 NUM_COL5_NEW_LIST(I),
2295 NUM_COL6_ORIG_LIST(I),
2296 NUM_COL6_NEW_LIST(I),
2297 VCHAR_COL1_ORIG_LIST(I),
2298 VCHAR_COL1_NEW_LIST(I),
2299 'U',
2300 req_id,
2301 hz_utility_pub.CREATED_BY,
2302 hz_utility_pub.CREATION_DATE,
2303 hz_utility_pub.LAST_UPDATE_LOGIN,
2304 hz_utility_pub.LAST_UPDATE_DATE,
2305 hz_utility_pub.LAST_UPDATED_BY
2306 );
2307
2308 END IF;
2309 IF l_last_fetch THEN
2310 EXIT;
2311 END IF;
2312 END LOOP;
2313
2314 END IF;
2315 EXCEPTION
2316 WHEN OTHERS THEN
2317 arp_message.set_line( 'RLM_INTERFACE_LINES_LOG');
2318 RAISE;
2319 END RLM_INTERFACE_LINES_LOG;
2320
2321 PROCEDURE RLM_SCHEDULE_HEADERS_LOG (
2322 req_id NUMBER,
2323 set_num NUMBER,
2324 process_mode VARCHAR2) IS
2325
2326 cust_name varchar2(100);
2327
2328 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
2329 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
2330 INDEX BY BINARY_INTEGER;
2331 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
2332
2333 TYPE HEADER_ID_LIST_TYPE IS TABLE OF
2334 RLM_SCHEDULE_HEADERS.HEADER_ID%TYPE
2335 INDEX BY BINARY_INTEGER;
2336 PRIMARY_KEY_ID_LIST HEADER_ID_LIST_TYPE;
2337
2338 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
2339 RLM_SCHEDULE_HEADERS.CUSTOMER_ID%TYPE
2343
2340 INDEX BY BINARY_INTEGER;
2341 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
2342 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
2344 TYPE ECE_PRIMARY_ADD_ID_LIST_TYPE IS TABLE OF
2345 RLM_SCHEDULE_HEADERS.ECE_PRIMARY_ADDRESS_ID%TYPE
2346 INDEX BY BINARY_INTEGER;
2347 NUM_COL2_ORIG_LIST ECE_PRIMARY_ADD_ID_LIST_TYPE;
2348 NUM_COL2_NEW_LIST ECE_PRIMARY_ADD_ID_LIST_TYPE;
2349
2350 TYPE CUST_NAME_EXT_LIST_TYPE IS TABLE OF
2351 RLM_SCHEDULE_HEADERS.CUST_NAME_EXT%TYPE
2352 INDEX BY BINARY_INTEGER;
2353 VCHAR_COL1_ORIG_LIST CUST_NAME_EXT_LIST_TYPE;
2354 VCHAR_COL1_NEW_LIST CUST_NAME_EXT_LIST_TYPE;
2355
2356 TYPE HEADER_NOTE_TEXT_LIST_TYPE IS TABLE OF
2357 RLM_SCHEDULE_HEADERS.HEADER_NOTE_TEXT%TYPE
2358 INDEX BY BINARY_INTEGER;
2359 VCHAR_COL2_ORIG_LIST HEADER_NOTE_TEXT_LIST_TYPE;
2360 VCHAR_COL2_NEW_LIST HEADER_NOTE_TEXT_LIST_TYPE;
2361
2362 CURSOR merged_records IS
2363 SELECT distinct CUSTOMER_MERGE_HEADER_ID
2364 ,HEADER_ID
2365 ,yt.CUSTOMER_ID
2366 ,ECE_PRIMARY_ADDRESS_ID
2367 ,CUST_NAME_EXT
2368 ,HEADER_NOTE_TEXT
2369 FROM RLM_SCHEDULE_HEADERS yt, ra_customer_merges m
2370 WHERE (
2371 yt.CUSTOMER_ID = m.DUPLICATE_ID
2372 OR yt.ECE_PRIMARY_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
2373 ) AND m.process_flag = 'N'
2374 AND m.request_id = req_id
2375 AND m.set_number = set_num;
2376 l_last_fetch BOOLEAN := FALSE;
2377 BEGIN
2378 IF process_mode='LOCK' THEN
2379 NULL;
2380 ELSE
2381 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
2382 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RLM_SCHEDULE_HEADERS',FALSE);
2383 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2384
2385 open merged_records;
2386 LOOP
2387 FETCH merged_records BULK COLLECT INTO
2388 MERGE_HEADER_ID_LIST
2389 , PRIMARY_KEY_ID_LIST
2390 , NUM_COL1_ORIG_LIST
2391 , NUM_COL2_ORIG_LIST
2392 , VCHAR_COL1_ORIG_LIST
2393 , VCHAR_COL2_ORIG_LIST
2394 limit 1000
2395 ;
2396 IF merged_records%NOTFOUND THEN
2397 l_last_fetch := TRUE;
2398 END IF;
2399 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
2400 exit;
2401 END IF;
2402 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
2403 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
2404 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
2405
2406 select distinct customer_name into cust_name
2407 from ra_customer_merges
2408 where customer_id = NUM_COL1_NEW_LIST(I)
2409 and request_id = req_id;
2410
2411 VCHAR_COL1_NEW_LIST(I) := cust_name;
2412 VCHAR_COL2_NEW_LIST(I) := VCHAR_COL2_ORIG_LIST(I);
2413 END LOOP;
2414 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2415 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
2416 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2417 MERGE_LOG_ID,
2418 TABLE_NAME,
2419 MERGE_HEADER_ID,
2420 PRIMARY_KEY_ID,
2421 NUM_COL1_ORIG,
2422 NUM_COL1_NEW,
2423 NUM_COL2_ORIG,
2424 NUM_COL2_NEW,
2425 VCHAR_COL1_ORIG,
2426 VCHAR_COL1_NEW,
2427 VCHAR_COL2_ORIG,
2428 VCHAR_COL2_NEW,
2429 ACTION_FLAG,
2430 REQUEST_ID,
2431 CREATED_BY,
2432 CREATION_DATE,
2433 LAST_UPDATE_LOGIN,
2434 LAST_UPDATE_DATE,
2435 LAST_UPDATED_BY
2436 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
2437 'RLM_SCHEDULE_HEADERS',
2438 MERGE_HEADER_ID_LIST(I),
2439 PRIMARY_KEY_ID_LIST(I),
2440 NUM_COL1_ORIG_LIST(I),
2441 NUM_COL1_NEW_LIST(I),
2442 NUM_COL2_ORIG_LIST(I),
2443 NUM_COL2_NEW_LIST(I),
2444 VCHAR_COL1_ORIG_LIST(I),
2445 VCHAR_COL1_NEW_LIST(I),
2446 VCHAR_COL2_ORIG_LIST(I),
2447 VCHAR_COL2_NEW_LIST(I),
2448 'U',
2449 req_id,
2450 hz_utility_pub.CREATED_BY,
2451 hz_utility_pub.CREATION_DATE,
2452 hz_utility_pub.LAST_UPDATE_LOGIN,
2453 hz_utility_pub.LAST_UPDATE_DATE,
2454 hz_utility_pub.LAST_UPDATED_BY
2455 );
2456
2457 END IF;
2458 IF l_last_fetch THEN
2459 EXIT;
2460 END IF;
2461 END LOOP;
2462
2463 END IF;
2464 EXCEPTION
2465 WHEN OTHERS THEN
2466 arp_message.set_line( 'RLM_SCHEDULE_HEADERS_LOG');
2467 RAISE;
2468 END RLM_SCHEDULE_HEADERS_LOG;
2469
2470
2471 PROCEDURE RLM_SCHEDULE_LINES_LOG (
2472 req_id NUMBER,
2473 set_num NUMBER,
2474 process_mode VARCHAR2) IS
2475
2476 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
2477 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
2478 INDEX BY BINARY_INTEGER;
2479 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
2480
2481 TYPE LINE_ID_LIST_TYPE IS TABLE OF
2482 RLM_SCHEDULE_LINES.LINE_ID%TYPE
2483 INDEX BY BINARY_INTEGER;
2484 PRIMARY_KEY_ID_LIST LINE_ID_LIST_TYPE;
2485
2486 TYPE INTRMD_SHIP_TO_ID_LIST_TYPE IS TABLE OF
2487 RLM_SCHEDULE_LINES.INTRMD_SHIP_TO_ID%TYPE
2488 INDEX BY BINARY_INTEGER;
2489 NUM_COL1_ORIG_LIST INTRMD_SHIP_TO_ID_LIST_TYPE;
2490 NUM_COL1_NEW_LIST INTRMD_SHIP_TO_ID_LIST_TYPE;
2491
2492 TYPE SHIP_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
2493 RLM_SCHEDULE_LINES.SHIP_TO_ADDRESS_ID%TYPE
2494 INDEX BY BINARY_INTEGER;
2495 NUM_COL2_ORIG_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
2496 NUM_COL2_NEW_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
2497
2498 TYPE BILL_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
2499 RLM_SCHEDULE_LINES.BILL_TO_ADDRESS_ID%TYPE
2500 INDEX BY BINARY_INTEGER;
2501 NUM_COL3_ORIG_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
2502 NUM_COL3_NEW_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
2503
2504 TYPE SHIP_TO_ORG_ID_LIST_TYPE IS TABLE OF
2505 RLM_SCHEDULE_LINES.SHIP_TO_ORG_ID%TYPE
2506 INDEX BY BINARY_INTEGER;
2507 NUM_COL4_ORIG_LIST SHIP_TO_ORG_ID_LIST_TYPE;
2508 NUM_COL4_NEW_LIST SHIP_TO_ORG_ID_LIST_TYPE;
2509
2510 TYPE INVOICE_TO_ORG_ID_LIST_TYPE IS TABLE OF
2511 RLM_SCHEDULE_LINES.INVOICE_TO_ORG_ID%TYPE
2512 INDEX BY BINARY_INTEGER;
2513 NUM_COL5_ORIG_LIST INVOICE_TO_ORG_ID_LIST_TYPE;
2514 NUM_COL5_NEW_LIST INVOICE_TO_ORG_ID_LIST_TYPE;
2515
2516 TYPE INT_SHIP_TO_ORG_ID_LIST_TYPE IS TABLE OF
2517 RLM_SCHEDULE_LINES.INTMED_SHIP_TO_ORG_ID%TYPE
2518 INDEX BY BINARY_INTEGER;
2519 NUM_COL6_ORIG_LIST INT_SHIP_TO_ORG_ID_LIST_TYPE;
2520 NUM_COL6_NEW_LIST INT_SHIP_TO_ORG_ID_LIST_TYPE;
2521
2522 TYPE ITEM_NOTE_TEXT_LIST_TYPE IS TABLE OF
2523 RLM_SCHEDULE_LINES.ITEM_NOTE_TEXT%TYPE
2524 INDEX BY BINARY_INTEGER;
2525 VCHAR_COL1_ORIG_LIST ITEM_NOTE_TEXT_LIST_TYPE;
2526 VCHAR_COL1_NEW_LIST ITEM_NOTE_TEXT_LIST_TYPE;
2527
2528 CURSOR merged_records IS
2529 SELECT distinct CUSTOMER_MERGE_HEADER_ID
2530 ,LINE_ID
2531 ,INTRMD_SHIP_TO_ID
2532 ,SHIP_TO_ADDRESS_ID
2533 ,BILL_TO_ADDRESS_ID
2534 ,SHIP_TO_ORG_ID
2535 ,INVOICE_TO_ORG_ID
2536 ,INTMED_SHIP_TO_ORG_ID
2537 ,ITEM_NOTE_TEXT
2538 FROM RLM_SCHEDULE_LINES yt, ra_customer_merges m
2539 WHERE (
2540 yt.INTRMD_SHIP_TO_ID = m.DUPLICATE_ADDRESS_ID
2541 OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
2542 OR yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
2543 OR yt.SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
2544 OR yt.INVOICE_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
2545 OR yt.INTMED_SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
2546 ) AND m.process_flag = 'N'
2547 AND m.request_id = req_id
2548 AND m.set_number = set_num;
2549 l_last_fetch BOOLEAN := FALSE;
2550 BEGIN
2551 IF process_mode='LOCK' THEN
2552 NULL;
2553 ELSE
2554 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
2555 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RLM_SCHEDULE_LINES',FALSE);
2556 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2557
2558 open merged_records;
2559 LOOP
2560 FETCH merged_records BULK COLLECT INTO
2561 MERGE_HEADER_ID_LIST
2562 , PRIMARY_KEY_ID_LIST
2563 , NUM_COL1_ORIG_LIST
2564 , NUM_COL2_ORIG_LIST
2565 , NUM_COL3_ORIG_LIST
2566 , NUM_COL4_ORIG_LIST
2567 , NUM_COL5_ORIG_LIST
2568 , NUM_COL6_ORIG_LIST
2569 , VCHAR_COL1_ORIG_LIST
2570 limit 1000
2571 ;
2572 IF merged_records%NOTFOUND THEN
2573 l_last_fetch := TRUE;
2574 END IF;
2575 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
2576 exit;
2577 END IF;
2578 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
2579 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
2580
2581 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
2582
2583 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL3_ORIG_LIST(I));
2584
2585 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL4_ORIG_LIST(I));
2586
2587 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL5_ORIG_LIST(I));
2588
2589 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL6_ORIG_LIST(I));
2590
2591 VCHAR_COL1_NEW_LIST(I) := VCHAR_COL1_ORIG_LIST(I);
2592 END LOOP;
2593 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2594 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
2595 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2596 MERGE_LOG_ID,
2597 TABLE_NAME,
2598 MERGE_HEADER_ID,
2599 PRIMARY_KEY_ID,
2600 NUM_COL1_ORIG,
2601 NUM_COL1_NEW,
2602 NUM_COL2_ORIG,
2603 NUM_COL2_NEW,
2604 NUM_COL3_ORIG,
2605 NUM_COL3_NEW,
2606 NUM_COL4_ORIG,
2607 NUM_COL4_NEW,
2608 NUM_COL5_ORIG,
2609 NUM_COL5_NEW,
2610 NUM_COL6_ORIG,
2611 NUM_COL6_NEW,
2612 VCHAR_COL1_ORIG,
2613 VCHAR_COL1_NEW,
2614 ACTION_FLAG,
2615 REQUEST_ID,
2616 CREATED_BY,
2617 CREATION_DATE,
2618 LAST_UPDATE_LOGIN,
2619 LAST_UPDATE_DATE,
2620 LAST_UPDATED_BY
2621 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
2622 'RLM_SCHEDULE_LINES',
2623 MERGE_HEADER_ID_LIST(I),
2624 PRIMARY_KEY_ID_LIST(I),
2625 NUM_COL1_ORIG_LIST(I),
2626 NUM_COL1_NEW_LIST(I),
2627 NUM_COL2_ORIG_LIST(I),
2628 NUM_COL2_NEW_LIST(I),
2629 NUM_COL3_ORIG_LIST(I),
2630 NUM_COL3_NEW_LIST(I),
2631 NUM_COL4_ORIG_LIST(I),
2632 NUM_COL4_NEW_LIST(I),
2633 NUM_COL5_ORIG_LIST(I),
2634 NUM_COL5_NEW_LIST(I),
2635 NUM_COL6_ORIG_LIST(I),
2636 NUM_COL6_NEW_LIST(I),
2637 VCHAR_COL1_ORIG_LIST(I),
2638 VCHAR_COL1_NEW_LIST(I),
2639 'U',
2640 req_id,
2641 hz_utility_pub.CREATED_BY,
2642 hz_utility_pub.CREATION_DATE,
2643 hz_utility_pub.LAST_UPDATE_LOGIN,
2644 hz_utility_pub.LAST_UPDATE_DATE,
2645 hz_utility_pub.LAST_UPDATED_BY
2646 );
2647
2648 END IF;
2649 IF l_last_fetch THEN
2650 EXIT;
2651 END IF;
2652 END LOOP;
2653 END IF;
2654 EXCEPTION
2655
2656 WHEN OTHERS THEN
2657 arp_message.set_line( 'RLM_SCHEDULE_LINES_LOG');
2658 RAISE;
2659 END RLM_SCHEDULE_LINES_LOG;
2660
2661 /*2447493*/
2662
2663 END RLM_CUST_MERGE;