[Home] [Help]
PACKAGE BODY: APPS.CSP_CMERGE_BB2
Source
1 PACKAGE BODY CSP_CMERGE_BB2 AS
2 /* $Header: cscm102b.pls 115.6 2003/04/11 10:56:46 axsubram ship $ */
3
4 /* ----------------- Local Procedures ----------------------------------------*/
5
6 /* %%PROCEDURE CS_MERGE_BILL_TO_SITE_ID ( req_id IN NUMBER,
7 set_number IN NUMBER,
8 process_mode IN VARCHAR2 );
9
10 PROCEDURE CS_MERGE_INSTALL_SITE_ID ( req_id IN NUMBER,
11 set_number IN NUMBER,
12 process_mode IN VARCHAR2 );
13
14 PROCEDURE CS_MERGE_SHIP_TO_SITE_ID ( req_id IN NUMBER,
15 set_number IN NUMBER,
16 process_mode IN VARCHAR2 );
17
18 PROCEDURE CS_MERGE_ORDER_BILL_TO_SITE_ID ( req_id IN NUMBER,
19 set_number IN NUMBER,
20 process_mode IN VARCHAR2 );
21
22 PROCEDURE CS_MERGE_ORDER_SHIP_TO_SITE_ID ( req_id IN NUMBER,
23 set_number IN NUMBER,
24 process_mode IN VARCHAR2 );
25
26 PROCEDURE CS_MERGE_CUSTOMER_ID ( req_id IN NUMBER,
27 set_number IN NUMBER,
28 process_mode IN VARCHAR2 );
29
30 PROCEDURE CS_CHECK_MERGE_DATA ( req_id IN NUMBER,
31 set_number IN NUMBER,
32 process_mode IN VARCHAR2 );%% */
33
34 /* ------------------- End Local Procedures ------------------------------------ */
35
36 /* This procedure handles the merge process for the CS_CUSTOMER_PRODUCTS.
37 It calls 6 seperate procedures to accomplish the task. The tasts are listed
38 below:
39 1) Update the bill_to_site_use_id
40 2) Update the install_site_use_id
41 3) Update the ship_to_site_use_id
42 4) Update the order_bill_to_site_use_id
43 5) Update the order_ship_to_site_use_id
44 5) Update the customer_id
45
46 ---------------------------------------------------------------------------- */
47
48 PROCEDURE MERGE ( req_id IN NUMBER,
49 set_number IN NUMBER,
50 process_mode IN VARCHAR2 ) IS
51
52 /* used to store a free form text to be written to the log file */
53
54 message_text char(80);
55
56 /* number of rows updated */
57
58 number_of_rows NUMBER;
59
60 BEGIN
61 -- Put the header in the report to identify the block to be run
62 null;
63
64 /* %% arp_message.set_line('CP_CMERGE_BB2.MERGE()+');
65
66 IF ( process_mode = 'LOCK' ) Then
67 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
68 arp_message.set_token('TABLE_NAME', 'CS_CUSTOMER_PRODUCTS',FALSE );
69 message_text := 'The locking is done in block CSP_CMERGE_BB2';
70 arp_message.set_line(message_text);
71 ELSE
72 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
73 arp_message.set_token('TABLE_NAME', 'CS_CUSTOMER_PRODUCTS',FALSE );
74 message_text := 'The merge is done in block CSP_CMERGE_BB2';
75 arp_message.set_line(message_text);
76 END IF;
77
78 -- merge the CS_CUSTOMER_PRODUCTS table update bill_to_site_use_id
79
80 message_text := '***-- Procedure CS_MERGE_BILL_TO_SITE_ID --**';
81 arp_message.set_line(message_text);
82
83 CS_MERGE_BILL_TO_SITE_ID( req_id, set_number, process_mode );
84
85 message_text := '***-- End CS_MERGE_BILL_TO_SITE_ID --**';
86 arp_message.set_line(message_text);
87
88 -- merge the CS_CUSTOMER_PRODUCTS table update install_site_use_id
89
90 message_text := '***-- Procedure CS_MERGE_INSTALL_SITE_ID --**';
91 arp_message.set_line(message_text);
92
93 CS_MERGE_INSTALL_SITE_ID( req_id, set_number, process_mode );
94
95 message_text := '***-- End CS_MERGE_INSTALL_SITE_ID --**';
96 arp_message.set_line(message_text);
97
98 -- merge the CS_CUSTOMER_PRODUCTS table update ship_to_site_use_id
99
100 message_text := '***-- Procedure CS_MERGE_SHIP_TO_SITE_ID --**';
101 arp_message.set_line(message_text);
102
103 CS_MERGE_SHIP_TO_SITE_ID( req_id, set_number, process_mode );
104
105 message_text := '***-- End CS_MERGE_SHIP_TO_SITE_ID --**';
106 arp_message.set_line(message_text);
107
108 -- merge the CS_CUSTOMER_PRODUCTS table update order_bill_to_site_use_id
109
110 message_text := '***-- Procedure CS_MERGE_ORDER_BILL_TO_SITE_ID --**';
111 arp_message.set_line(message_text);
112
113 CS_MERGE_ORDER_BILL_TO_SITE_ID( req_id, set_number, process_mode );
114
115 message_text := '***-- End CS_MERGE_ORDER_BILL_TO_SITE_ID --**';
116 arp_message.set_line(message_text);
117
118
119 message_text := '***-- Procedure CS_MERGE_ORDER_SHIP_TO_SITE_ID --**';
120 arp_message.set_line(message_text);
121
122 CS_MERGE_ORDER_SHIP_TO_SITE_ID( req_id, set_number, process_mode );
123
124 message_text := '***-- End CS_MERGE_ORDER_SHIP_TO_SITE_ID --**';
125 arp_message.set_line(message_text);
126
127 -- merge the CS_CUSTOMER_PRODUCTS table update the customer_id
128
129 message_text := '***-- Procedure CS_MERGE_CUSTOMER_ID --**';
130 arp_message.set_line(message_text);
131
132 CS_MERGE_CUSTOMER_ID( req_id, set_number, process_mode );
133
134 message_text := '***-- End CS_MERGE_CUSTOMER_ID --**';
135 arp_message.set_line(message_text);
136
137 -- The merge of CS_CUSTOMER_PRODUCTS is complete, use a cursor to
138 -- check to make sure all data has been updated. If not report it to the
139 -- log file.
140
141 -- CS_CHECK_MERGE_DATA ( req_id, set_number, process_mode );
142
143 -- Report that the process for CS_CUSTOMER_PRODUCTS is complete
144
145 IF ( process_mode = 'LOCK' ) Then
146 message_text := '** LOCKING completed for table CS_CUSTOMER_PRODUCTS **';
147 arp_message.set_line(message_text);
148 ELSE
149 message_text := '** MERGE completed for table CS_CUSTOMER_PRODUCTS **';
150 arp_message.set_line(message_text);
151 END IF;
152
153 arp_message.set_line('CP_CMERGE_BB2.MERGE()-');
154 */
155
156 END MERGE;
157
158 /* -----------------------------------------------------------------------------*/
159
160 /* Update the ship use site id of CS_CUSTOMER_PRODUCTS
161
162 PROCEDURE CS_MERGE_BILL_TO_SITE_ID ( req_id IN NUMBER,
163 set_number IN NUMBER,
164 process_mode IN VARCHAR2 ) IS
165
166 used to store a free form text to be written to the log file
167
168 message_text char(80);
169
170 -- number of rows updated
171
172 number_of_rows NUMBER;
173
174 Cursor LOCK_BILL_TO_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
175 SELECT bill_to_site_use_id
176 FROM CS_CUSTOMER_PRODUCTS yt
177 WHERE
178 yt.bill_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
179 FROM RA_CUSTOMER_MERGES RACM
180 WHERE RACM.PROCESS_FLAG = 'N'
181 AND RACM.REQUEST_ID = req_id
182 AND RACM.SET_NUMBER = set_number )
183 FOR UPDATE NOWAIT;
184
185
186 BEGIN
187
188 IF ( process_mode = 'LOCK' ) Then
189
190 message_text := 'LOCKING the bill_to_site_use_id ( 1/6 )';
191 arp_message.set_line(message_text);
192
193 OPEN LOCK_BILL_TO_SITE_ID ( req_id, set_number );
194 CLOSE LOCK_BILL_TO_SITE_ID;
195
196 message_text := 'Locked the bill_to_site_use_id';
197 arp_message.set_line(message_text);
198
199 ELSE
200
201 message_text := 'Updating the bill_to_site_use_id ( 1/6 )';
202 arp_message.set_line(message_text);
203
204 UPDATE CS_CUSTOMER_PRODUCTS yt
205 SET
206 yt.bill_to_site_use_id =
207 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
208 FROM RA_CUSTOMER_MERGES RACM
209 WHERE yt.bill_to_site_use_id
210 = RACM.DUPLICATE_SITE_ID
211 AND RACM.PROCESS_FLAG = 'N'
212 AND RACM.REQUEST_ID = req_id
213 AND RACM.SET_NUMBER = set_number ),
214 yt.LAST_UPDATE_DATE = SYSDATE,
215 yt.LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
216 yt.LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
217 WHERE
218 yt.bill_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
219 FROM RA_CUSTOMER_MERGES RACM
220 WHERE RACM.PROCESS_FLAG = 'N'
221 AND RACM.REQUEST_ID = req_id
222 AND RACM.SET_NUMBER = set_number );
223
224 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
225 number_of_rows := sql%rowcount;
226 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
227 message_text := 'Done with the update of bill_to_site_use_id';
228 arp_message.set_line(message_text);
229
230 END IF;
231
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234
235 message_text :=
236 'Bill_to_site_use_id NOT found -- proceeding *** ';
237 arp_message.set_line(message_text);
238 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
239 number_of_rows := sql%rowcount;
240 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
241 message_text := 'Done with the update of bill_to_site_use_id';
242 arp_message.set_line(message_text);
243
244 WHEN OTHERS THEN
245
246 message_text := SUBSTR(SQLERRM,1,70);
247 arp_message.set_error('CS_MERGE_BILL_TO_SITE_ID',
248 message_text);
249 raise;
250
251 END CS_MERGE_BILL_TO_SITE_ID;
252
253 -- Update the ship use site id of CS_CUSTOMER_PRODUCTS
254
255 PROCEDURE CS_MERGE_INSTALL_SITE_ID ( req_id IN NUMBER,
256 set_number IN NUMBER,
257 process_mode IN VARCHAR2 ) IS
258
259 -- used to store a free form text to be written to the log file
260
261 message_text char(80);
262
263 -- number of rows updated
264
265 number_of_rows NUMBER;
266
267 Cursor LOCK_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
268 SELECT install_site_use_id
269 FROM CS_CUSTOMER_PRODUCTS yt
270 WHERE
271 yt.install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
272 FROM RA_CUSTOMER_MERGES RACM
273 WHERE RACM.PROCESS_FLAG = 'N'
274 AND RACM.REQUEST_ID = req_id
275 AND RACM.SET_NUMBER = set_number )
276 FOR UPDATE NOWAIT;
277
278 BEGIN
279 IF ( process_mode = 'LOCK' ) Then
280
281 message_text := 'LOCKING the install_site_use_id ( 2/6 )';
282 arp_message.set_line(message_text);
283
284 OPEN LOCK_INSTALL_SITE_ID ( req_id, set_number );
285 CLOSE LOCK_INSTALL_SITE_ID;
286
287 message_text := 'Done locking Install_site_use_id';
288 arp_message.set_line(message_text);
289
290 ELSE
291
292 message_text :=
293 'Starting to update the install_site_use_id ( 2/6 )';
294 arp_message.set_line(message_text);
295
296 UPDATE CS_CUSTOMER_PRODUCTS yt
297 SET
298 yt.install_site_use_id =
299 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
300 FROM RA_CUSTOMER_MERGES RACM
301 WHERE yt.install_site_use_id
302 = DUPLICATE_SITE_ID
303 AND RACM.PROCESS_FLAG = 'N'
304 AND RACM.REQUEST_ID = req_id
305 AND RACM.SET_NUMBER = set_number ),
306 LAST_UPDATE_DATE = SYSDATE,
307 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
308 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
309 WHERE
310 yt.install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
311 FROM RA_CUSTOMER_MERGES RACM
312 WHERE RACM.PROCESS_FLAG = 'N'
313 AND RACM.REQUEST_ID = req_id
314 AND RACM.SET_NUMBER = set_number );
315
316 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
317 number_of_rows := sql%rowcount;
318 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
319 message_text := 'Done with the update of Install_site_use_id';
320 arp_message.set_line(message_text);
321
322 END IF;
323
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326
327 message_text :=
328 'Install_site_use_id NOT found -- proceeding ***';
329 arp_message.set_line(message_text);
330 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
331 number_of_rows := sql%rowcount;
332 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
333 message_text := 'Done with the update of Install_site_use_id';
334 arp_message.set_line(message_text);
335
336 WHEN OTHERS THEN
337
338 message_text := SUBSTR(SQLERRM,1,70);
339 arp_message.set_error('CS_MERGE_INSTALL_SITE_ID',
340 message_text);
341 raise;
342
343 END CS_MERGE_INSTALL_SITE_ID;
344
345 -- Update the ship to site id of CS_CUSTOMER_PRODUCTS
346
347 PROCEDURE CS_MERGE_SHIP_TO_SITE_ID( req_id IN NUMBER,
348 set_number IN NUMBER,
349 process_mode IN VARCHAR2 ) IS
350
351 -- used to store a free form text to be written to the log file
352
353 message_text char(80);
354
355 -- number of rows updated
356
357 number_of_rows NUMBER;
358
359 Cursor LOCK_SHIP_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
360 SELECT ship_to_site_use_id
361 FROM CS_CUSTOMER_PRODUCTS yt
362 WHERE
363 yt.ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
364 FROM RA_CUSTOMER_MERGES RACM
365 WHERE RACM.PROCESS_FLAG = 'N'
366 AND RACM.REQUEST_ID = req_id
367 AND RACM.SET_NUMBER = set_number )
368 FOR UPDATE NOWAIT;
369
370 BEGIN
371 IF ( process_mode = 'LOCK' ) Then
372
373 message_text := 'LOCKING the ship_to_site_use_id ( 3/6 )';
374 arp_message.set_line(message_text);
375
376 OPEN LOCK_SHIP_SITE_ID ( req_id, set_number );
377 CLOSE LOCK_SHIP_SITE_ID;
378
379 message_text := 'Done locking ship_to_site_use_id';
380 arp_message.set_line(message_text);
381
382 ELSE
383
384 message_text := 'Starting to update the ship_to_site_use_id ( 3/6 )';
385 arp_message.set_line(message_text);
386
387 UPDATE CS_CUSTOMER_PRODUCTS yt
388 SET
389 yt.ship_to_site_use_id =
390 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
391 FROM RA_CUSTOMER_MERGES RACM
392 WHERE yt.ship_to_site_use_id
393 = DUPLICATE_SITE_ID
394 AND RACM.PROCESS_FLAG = 'N'
395 AND RACM.REQUEST_ID = req_id
396 AND RACM.SET_NUMBER = set_number ),
397 LAST_UPDATE_DATE = SYSDATE,
398 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
399 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
400 WHERE
401 yt.ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
402 FROM RA_CUSTOMER_MERGES RACM
403 WHERE RACM.PROCESS_FLAG = 'N'
404 AND RACM.REQUEST_ID = req_id
405 AND RACM.SET_NUMBER = set_number );
406
407 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
408 number_of_rows := sql%rowcount;
409 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
410 message_text := 'Done with the update of ship_to_site_use_id';
411 arp_message.set_line(message_text);
412
413 END IF;
414
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417
418 message_text := 'ship_to_site_use_id NOT found -- proceeding ***';
419 arp_message.set_line(message_text);
420 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
421 number_of_rows := sql%rowcount;
422 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
423 message_text := 'Done with the update of ship_to_site_use_id';
424 arp_message.set_line(message_text);
425
426 WHEN OTHERS THEN
427
428 message_text := SUBSTR(SQLERRM,1,70);
429 arp_message.set_error('CS_MERGE_SHIP_TO_SITE_ID',
430 message_text);
431 raise;
432
433 END CS_MERGE_SHIP_TO_SITE_ID;
434
435 -- Update the order bill to site use id of CS_CUSTOMER_PRODUCTS
436
437 PROCEDURE CS_MERGE_ORDER_BILL_TO_SITE_ID ( req_id IN NUMBER,
438 set_number IN NUMBER,
439 process_mode IN VARCHAR2 ) IS
440
441 -- used to store a free form text to be written to the log file
442
443 message_text char(80);
444
445 -- number of rows updated
446
447 number_of_rows NUMBER;
448
449 Cursor LOCK_ORDER_BILL_SITE_USE_ID ( req_id NUMBER, set_number NUMBER ) IS
450 SELECT Order_bill_to_site_use_id
451 FROM CS_CUSTOMER_PRODUCTS yt
452 WHERE
453 yt.Order_bill_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
454 FROM RA_CUSTOMER_MERGES RACM
455 WHERE RACM.PROCESS_FLAG = 'N'
456 AND RACM.REQUEST_ID = req_id
457 AND RACM.SET_NUMBER = set_number )
458 FOR UPDATE NOWAIT;
459
460 BEGIN
461 IF ( process_mode = 'LOCK' ) Then
462
463 message_text := 'LOCKING the Order_bill_to_site_use_id ( 4/6 )';
464 arp_message.set_line(message_text);
465
466 OPEN LOCK_ORDER_BILL_SITE_USE_ID ( req_id, set_number );
467 CLOSE LOCK_ORDER_BILL_SITE_USE_ID;
468
469 message_text := 'Done locking Order_bill_to_site_use_id';
470 arp_message.set_line(message_text);
474 message_text :=
471
472 ELSE
473
475 'Starting to update the Order_bill_to_site_use_id ( 4/6 )';
476 arp_message.set_line(message_text);
477
478 UPDATE CS_CUSTOMER_PRODUCTS yt
479 SET
480 yt.Order_bill_to_site_use_id =
481 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
482 FROM RA_CUSTOMER_MERGES RACM
483 WHERE yt.Order_bill_to_site_use_id
484 = DUPLICATE_SITE_ID
485 AND RACM.PROCESS_FLAG = 'N'
486 AND RACM.REQUEST_ID = req_id
487 AND RACM.SET_NUMBER = set_number ),
488 LAST_UPDATE_DATE = SYSDATE,
489 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
490 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
491 WHERE
492 yt.Order_bill_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
493 FROM RA_CUSTOMER_MERGES RACM
494 WHERE RACM.PROCESS_FLAG = 'N'
495 AND RACM.REQUEST_ID = req_id
496 AND RACM.SET_NUMBER = set_number );
497
498 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
499 number_of_rows := sql%rowcount;
500 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
501 message_text := 'Done with the update of Order_bill_to_site_use_id';
502 arp_message.set_line(message_text);
503
504 END IF;
505
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508
509 message_text := 'Order_bill_to_site_use_id NOT found -- proceeding *** ';
510 arp_message.set_line(message_text);
511 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
512 number_of_rows := sql%rowcount;
513 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
514 message_text := 'Done with the update of Order_bill_to_site_use_id';
515 arp_message.set_line(message_text);
516
517 WHEN OTHERS THEN
518
519 message_text := SUBSTR(SQLERRM,1,70);
520 arp_message.set_error('CS_MERGE_ORDER_BILL_TO_SITE_ID',
521 message_text);
522 raise;
523
524 END CS_MERGE_ORDER_BILL_TO_SITE_ID
525
526 -- This procedure handles the order_ship_to_site_use_id update in CS_CUSTOMER_PRODUCTS
527
528 PROCEDURE CS_MERGE_ORDER_SHIP_TO_SITE_ID ( req_id IN NUMBER,
529 set_number IN NUMBER,
530 process_mode IN VARCHAR2 ) IS
531
532 -- used to store a free form text to be written to the log file
533
534 message_text char(80);
535
536 -- number of rows updated
537
538 number_of_rows NUMBER;
539
540 Cursor LOCK_ORDER_SHIP_SITE_USE_ID ( req_id NUMBER, set_number NUMBER ) IS
541 SELECT Order_ship_to_site_use_id
542 FROM CS_CUSTOMER_PRODUCTS yt
543 WHERE
544 yt.Order_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
545 FROM RA_CUSTOMER_MERGES RACM
546 WHERE RACM.PROCESS_FLAG = 'N'
547 AND RACM.REQUEST_ID = req_id
548 AND RACM.SET_NUMBER = set_number )
549 FOR UPDATE NOWAIT;
550
551 BEGIN
552 IF ( process_mode = 'LOCK' ) Then
553
554 message_text := 'LOCKING the Order_ship_to_site_use_id ( 5/6 )';
555 arp_message.set_line(message_text);
556
557 OPEN LOCK_ORDER_SHIP_SITE_USE_ID ( req_id, set_number );
558 CLOSE LOCK_ORDER_SHIP_SITE_USE_ID;
559
560 message_text := 'Done locking Order_ship_to_site_use_id';
561 arp_message.set_line(message_text);
562
563 ELSE
564
565 message_text :=
566 'Starting to update the Order_ship_to_site_use_id ( 5/6 )';
567 arp_message.set_line(message_text);
568
569 UPDATE CS_CUSTOMER_PRODUCTS yt
570 SET
571 yt.Order_ship_to_site_use_id =
572 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
573 FROM RA_CUSTOMER_MERGES RACM
574 WHERE yt.Order_ship_to_site_use_id
575 = DUPLICATE_SITE_ID
576 AND RACM.PROCESS_FLAG = 'N'
577 AND RACM.REQUEST_ID = req_id
578 AND RACM.SET_NUMBER = set_number ),
579 LAST_UPDATE_DATE = SYSDATE,
580 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
581 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
582 WHERE
583 yt.Order_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
584 FROM RA_CUSTOMER_MERGES RACM
588
585 WHERE RACM.PROCESS_FLAG = 'N'
586 AND RACM.REQUEST_ID = req_id
587 AND RACM.SET_NUMBER = set_number );
589 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
590 number_of_rows := sql%rowcount;
591 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
592 message_text := 'Done with the update of Order_ship_to_site_use_id';
593 arp_message.set_line(message_text);
594
595 END IF;
596
597 EXCEPTION
598 WHEN NO_DATA_FOUND THEN
599
600 message_text := 'Order_ship_to_site_use_id NOT found -- proceeding *** ';
601 arp_message.set_line(message_text);
602 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
603 number_of_rows := sql%rowcount;
604 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
605 message_text := 'Done with the update of Order_ship_to_site_use_id';
606 arp_message.set_line(message_text);
607
608 WHEN OTHERS THEN
609
610 message_text := SUBSTR(SQLERRM,1,70);
611 arp_message.set_error('CS_MERGE_ORDER_SHIP_TO_SITE_ID',
612 message_text);
613 raise;
614
615 END CS_MERGE_ORDER_SHIP_TO_SITE_ID;
616
617 -- This process updates the customer_id of the CS_CUSTOMER_PRODUCTS table
618
619 PROCEDURE CS_MERGE_CUSTOMER_ID (req_id IN NUMBER,
620 set_number IN NUMBER,
621 process_mode IN VARCHAR2 ) IS
622
623 -- used to store a free form text to be written to the log file
624
625 message_text char(80);
626
627 -- number of rows updated
628
629 number_of_rows NUMBER;
630
631 Cursor LOCK_CUSTOMER_ID ( req_id NUMBER, set_number NUMBER ) IS
632 SELECT yt.customer_id
633 FROM CS_CUSTOMER_PRODUCTS yt
634 WHERE
635 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
636 FROM RA_CUSTOMER_MERGES RACM
637 WHERE RACM.PROCESS_FLAG = 'N'
638 AND RACM.REQUEST_ID = req_id
639 AND RACM.SET_NUMBER = set_number )
640 FOR UPDATE NOWAIT;
641
642 BEGIN
643 IF ( process_mode = 'LOCK' ) Then
644
645 message_text := 'LOCKING the customer_id ( 6/6 )';
646 arp_message.set_line(message_text);
647
648 OPEN LOCK_CUSTOMER_ID ( req_id, set_number );
649 CLOSE LOCK_CUSTOMER_ID;
650
651 message_text := 'Done locking customer_id';
652 arp_message.set_line(message_text);
653
654 ELSE
655
656 message_text := 'Starting to update the customer_id ( 6/6 )';
657 arp_message.set_line(message_text);
658
659 UPDATE CS_CUSTOMER_PRODUCTS yt
660 SET
661 yt.customer_id = ( SELECT DISTINCT RACM.CUSTOMER_ID
662 FROM RA_CUSTOMER_MERGES RACM
663 WHERE yt.customer_id = DUPLICATE_ID
664 AND RACM.PROCESS_FLAG = 'N'
665 AND RACM.REQUEST_ID = req_id
666 AND RACM.SET_NUMBER = set_number ),
667 LAST_UPDATE_DATE = SYSDATE,
668 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
669 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
670 WHERE
671 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
672 FROM RA_CUSTOMER_MERGES RACM
673 WHERE RACM.PROCESS_FLAG = 'N'
674 AND RACM.REQUEST_ID = req_id
675 AND RACM.SET_NUMBER = set_number );
676
677 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
678 number_of_rows := sql%rowcount;
679 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
680 message_text := 'Done with the update of customer_id';
681 arp_message.set_line(message_text);
682
683 END IF;
684
685 EXCEPTION
686 WHEN NO_DATA_FOUND THEN
687
688 message_text := 'Customer_id NOT found -- proceeding *** ';
689 arp_message.set_line(message_text);
690 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
691 number_of_rows := sql%rowcount;
695
692 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
693 message_text := 'Done with the update of customer_id';
694 arp_message.set_line(message_text);
696 WHEN OTHERS THEN
697
698 message_text := SUBSTR(SQLERRM,1,70);
699 arp_message.set_error('CS_MERGE_CUSTOMER_ID',
700 message_text);
701 raise;
702
703 END CS_MERGE_CUSTOMER_ID;
704
705 -- Loop through using a cursor and try to identify for a request_Id un merged
706 -- records. For each record that should have been merged report it to the
707 -- log file.
708
709 PROCEDURE CS_CHECK_MERGE_DATA ( req_id IN NUMBER,
710 set_number IN NUMBER,
711 process_mode IN VARCHAR2 ) IS
712
713 -- templaray storage location for identifing the record in error
714
715 current_serial_number varchar2(10);
716
717 -- used to store a free form text to be written to the log file
718
719 message_text char(80);
720
721 -- number of rows updated
722
723 number_of_rows NUMBER;
724
725
726 CURSOR CS_CHECK IS
727 SELECT
728 DISTINCT
729 cs.current_serial_number
730 FROM CS_CUSTOMER_PRODUCTS CS,
731 RA_CUSTOMER_MERGES RACM
732 WHERE
733 RACM.PROCESS_FLAG = 'N' AND
734 RACM.REQUEST_ID = req_id AND
735 RACM.SET_NUMBER = set_number AND
736 ((( cs.customer_id = RACM.CUSTOMER_ID AND
737 cs.bill_to_site_use_id != racm.customer_site_id AND
738 cs.bill_to_site_use_id IS NOT NULL ) AND
739 ( cs.customer_id NOT IN ( select racm.customer_id
740 from CS_CUSTOMER_PRODUCTS CS,
741 RA_CUSTOMER_MERGES RACM
742 where cs.customer_id = RACM.CUSTOMER_ID AND
743 cs.bill_to_site_use_id = racm.customer_site_id or
744 cs.bill_to_site_use_id IS NULL ))) AND
745 (( cs.customer_id = RACM.CUSTOMER_ID AND
746 cs.install_site_use_id != racm.customer_site_id AND
747 cs.install_site_use_id IS NOT NULL ) AND
748 ( cs.customer_id NOT IN ( select racm.customer_id
749 from CS_CUSTOMER_PRODUCTS CS,
750 RA_CUSTOMER_MERGES RACM
751 where cs.customer_id = RACM.CUSTOMER_ID AND
752 cs.install_site_use_id = racm.customer_site_id or
753 cs.install_site_use_id IS NULL ))) AND
754 (( cs.customer_id = RACM.CUSTOMER_ID AND
755 cs.ship_to_site_use_id != racm.customer_site_id AND
756 cs.ship_to_site_use_id IS NOT NULL ) AND
757 ( cs.customer_id NOT IN ( select racm.customer_id
758 from CS_CUSTOMER_PRODUCTS CS,
759 RA_CUSTOMER_MERGES RACM
760 where cs.customer_id = RACM.CUSTOMER_ID AND
761 cs.ship_to_site_use_id = racm.customer_site_id or
762 cs.ship_to_site_use_id IS NULL ))) AND
763 (( cs.customer_id = RACM.CUSTOMER_ID AND
764 cs.order_bill_to_site_use_id != racm.customer_site_id AND
765 cs.order_bill_to_site_use_id IS NOT NULL ) AND
766 ( cs.customer_id NOT IN ( select racm.customer_id
767 from CS_CUSTOMER_PRODUCTS CS,
768 RA_CUSTOMER_MERGES RACM
769 where cs.customer_id = RACM.CUSTOMER_ID AND
770 cs.order_bill_to_site_use_id = racm.customer_site_id or
771 cs.order_bill_to_site_use_id IS NULL ))) AND
772 (( cs.customer_id = RACM.CUSTOMER_ID AND
773 cs.order_ship_to_site_use_id != racm.customer_site_id AND
774 cs.order_ship_to_site_use_id IS NOT NULL ) AND
775 ( cs.customer_id NOT IN ( select racm.customer_id
776 from CS_CUSTOMER_PRODUCTS CS,
777 RA_CUSTOMER_MERGES RACM
778 where cs.customer_id = RACM.CUSTOMER_ID AND
779 cs.order_ship_to_site_use_id = racm.customer_site_id or
780 cs.order_ship_to_site_use_id IS NULL ))));
781
782 BEGIN
783 IF ( process_mode != 'LOCK' ) Then
784
785 message_text := '***-- Procedure CS_CHECK_MERGE_DATA --**';
786 arp_message.set_line(message_text);
787
788
789 OPEN CS_CHECK;
790
791 LOOP
792 FETCH CS_CHECK
793 INTO
794 current_serial_number;
795
796 EXIT WHEN CS_CHECK%NOTFOUND;
797 message_text :=
798 'WARNING, Following Product Serial Num. has address(s) not merged ';
799 arp_message.set_line(message_text);
800 message_text := current_serial_number;
801 arp_message.set_line(message_text);
802
803 END LOOP;
804
805 CLOSE CS_CHECK;
806
807 message_text := '***-- End CS_CHECK_MERGE_DATA --**';
808 arp_message.set_line(message_text);
809
810 END IF;
811
812 EXCEPTION
813
814 WHEN OTHERS THEN
815
816 message_text := SUBSTR(SQLERRM,1,70);
817 arp_message.set_error('CS_CHECK_MERGE_DATA',
818 message_text);
819 raise;
820
821 END CS_CHECK_MERGE_DATA;
822 */
823 END CSP_CMERGE_BB2;