4 /* --------------- Local Procedures ----------------------------------------*/
1 PACKAGE BODY csp_cmerge_bb8 AS
2 /* $Header: cscm108b.pls 115.3 99/07/16 08:48:11 porting ship $ */
3
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
19 PROCEDURE CS_MERGE_CUSTOMER_ID ( req_id IN NUMBER,
20 set_number IN NUMBER,
21 process_mode IN VARCHAR2 );
22
23 PROCEDURE CS_CHECK_MERGE_DATA ( req_id IN NUMBER,
24 set_number IN NUMBER,
25 process_mode IN VARCHAR2 );
26
27 /* -------------- End Local Procedures ------------------------------- */
28
29 /* This procedure handles the merge process for the CS_INCIDENTS.
30 It calls 4 seperate procedures to accomplish the task. The tasks are listed
31 below:
32 1) Update the bill_to_site_use_id
33 2) Update the install_site_use_id
34 3) Update the ship_to_site_use_id
35 4) Update the customer_id
36
37 ---------------------------------------------------------------------- */
38
39 PROCEDURE MERGE ( req_id IN NUMBER,
40 set_number IN NUMBER,
41 process_mode IN VARCHAR2 ) IS
42
43 /* used to store a free form text to be written to the log file */
44
45 message_text varchar2(255);
46
47 /* number of rows updated */
48
49 number_of_rows NUMBER;
50
51 BEGIN
52
53 /* Put the header in the report to identify the block to be run */
54
55 arp_message.set_line('CP_CMERGE_BB8.MERGE()+');
56
57 IF ( process_mode = 'LOCK' ) Then
58 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
59 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS',FALSE );
63 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
60 message_text := 'The locking is done in block CSP_CMERGE_BB8';
61 arp_message.set_line(message_text);
62 ELSE
64 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS',FALSE );
65 message_text := 'The merge is done in block CSP_CMERGE_BB8';
66 arp_message.set_line(message_text);
67 END IF;
68
69 /* merge the CS_INCIDENTS table update bill_to_site_use_id */
70
71 message_text := '***-- Procedure CS_MERGE_BILL_TO_SITE_ID --**';
72 arp_message.set_line(message_text);
73
74 CS_MERGE_BILL_TO_SITE_ID( req_id, set_number, process_mode );
75
76 message_text := '***-- End CS_MERGE_BILL_TO_SITE_ID --**';
77 arp_message.set_line(message_text);
78
79 /* merge the CS_INCIDENTS table update install_site_use_id */
80
81 message_text := '***-- Procedure CS_MERGE_INSTALL_SITE_ID --**';
82 arp_message.set_line(message_text);
83
84 CS_MERGE_INSTALL_SITE_ID( req_id, set_number, process_mode );
85
86 message_text := '***-- End CS_MERGE_INSTALL_SITE_ID --**';
87 arp_message.set_line(message_text);
88
89 /* merge the CS_INCIDENTS table update ship_to_site_use_id */
90
91 message_text := '***-- Procedure CS_MERGE_SHIP_TO_SITE_ID --**';
92 arp_message.set_line(message_text);
93
94 CS_MERGE_SHIP_TO_SITE_ID( req_id, set_number, process_mode );
95
96 message_text := '***-- End CS_MERGE_SHIP_TO_SITE_ID --**';
97 arp_message.set_line(message_text);
98
99
100 /* merge the CS_INCIDENTS table update the customer_id */
101
102 message_text := '***-- Procedure CS_MERGE_CUSTOMER_ID --**';
103 arp_message.set_line(message_text);
104
105 CS_MERGE_CUSTOMER_ID( req_id, set_number, process_mode );
106
107 message_text := '***-- End CS_MERGE_CUSTOMER_ID --**';
108 arp_message.set_line(message_text);
109
110 /* The merge of CS_INCIDENTS is complete, use a cursor to
111 check to make sure all data has been updated. If not report it to the
112 log file. */
113
114 CS_CHECK_MERGE_DATA ( req_id, set_number, process_mode );
115
116 /* Report that the process for CS_INCIDENTS is complete */
117
118 IF ( process_mode = 'LOCK' ) Then
119 message_text := '** LOCKING completed for table CS_INCIDENTS **';
120 arp_message.set_line(message_text);
121 ELSE
122 message_text := '** MERGE completed for table CS_INCIDENTS **';
123 arp_message.set_line(message_text);
124 END IF;
125
126 arp_message.set_line('CP_CMERGE_BB8.MERGE()-');
127
128 END MERGE;
129
130 /* ------------------------------------------------------------------------*/
131
132 /* Update the bill to site use id of CS_INCIDENTS */
133
134 PROCEDURE CS_MERGE_BILL_TO_SITE_ID ( req_id IN NUMBER,
135 set_number IN NUMBER,
136 process_mode IN VARCHAR2 ) IS
137
138 /* used to store a free form text to be written to the log file */
139
140 message_text varchar2(255);
141
142 /* number of rows updated */
143
144 number_of_rows NUMBER;
145
146 Cursor LOCK_BILL_TO_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
147 SELECT bill_to_site_use_id
148 FROM CS_INCIDENTS yt, RA_CUSTOMER_MERGES RACM
149 WHERE
150 yt.bill_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
151 FROM RA_CUSTOMER_MERGES RACM
152 WHERE RACM.PROCESS_FLAG = 'N'
153 AND RACM.REQUEST_ID = req_id
154 AND RACM.SET_NUMBER = set_number )
155 AND yt.customer_id <> RACM.DUPLICATE_ID
156 FOR UPDATE NOWAIT;
157
158
159 BEGIN
160
161 IF ( process_mode = 'LOCK' ) Then
162
163 message_text := 'LOCKING the bill_to_site_use_id ( 1/6 )';
164 arp_message.set_line(message_text);
165
166 OPEN LOCK_BILL_TO_SITE_ID ( req_id, set_number );
167 CLOSE LOCK_BILL_TO_SITE_ID;
168
169 message_text := 'Locked the bill_to_site_use_id';
170 arp_message.set_line(message_text);
171
172 ELSE
173
174 message_text := 'Updating the bill_to_site_use_id ( 1/6 )';
175 arp_message.set_line(message_text);
176
177 UPDATE CS_INCIDENTS yt
178 SET
179 yt.bill_to_site_use_id =
180 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
181 FROM RA_CUSTOMER_MERGES RACM
182 WHERE yt.bill_to_site_use_id
183 = RACM.DUPLICATE_SITE_ID
184 AND RACM.PROCESS_FLAG = 'N'
185 AND RACM.REQUEST_ID = req_id
186 AND RACM.SET_NUMBER = set_number ),
187 yt.LAST_UPDATE_DATE = SYSDATE,
188 yt.LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
189 yt.LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
190 WHERE
191 yt.bill_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
192 FROM RA_CUSTOMER_MERGES RACM
193 WHERE RACM.PROCESS_FLAG = 'N'
197 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
194 AND RACM.REQUEST_ID = req_id
195 AND RACM.SET_NUMBER = set_number );
196
198 number_of_rows := sql%rowcount;
199 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
200 message_text := 'Done with the update of bill_to_site_use_id';
201 arp_message.set_line(message_text);
202
203 END IF;
204
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207
208 message_text :=
209 'Bill_to_site_use_id NOT found -- proceeding *** ';
210 arp_message.set_line(message_text);
211 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
212 number_of_rows := sql%rowcount;
213 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
214 message_text := 'Done with the update of bill_to_site_use_id';
215 arp_message.set_line(message_text);
216
217 WHEN OTHERS THEN
218
219 message_text := SUBSTR(SQLERRM,1,70);
220 arp_message.set_error('CS_MERGE_BILL_TO_SITE_ID',
221 message_text);
222 raise;
223
224 END CS_MERGE_BILL_TO_SITE_ID;
225
226 /* Update the install site use id of CS_INCIDENTS */
227
228 PROCEDURE CS_MERGE_INSTALL_SITE_ID ( req_id IN NUMBER,
229 set_number IN NUMBER,
230 process_mode IN VARCHAR2 ) IS
231
232 /* used to store a free form text to be written to the log file */
233
234 message_text varchar2(255);
235
236 /* number of rows updated */
237
238 number_of_rows NUMBER;
239
240 Cursor LOCK_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
241 SELECT install_site_use_id
242 FROM CS_INCIDENTS yt, RA_CUSTOMER_MERGES RACM
243 WHERE
244 yt.install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
245 FROM RA_CUSTOMER_MERGES RACM
246 WHERE RACM.PROCESS_FLAG = 'N'
247 AND RACM.REQUEST_ID = req_id
248 AND RACM.SET_NUMBER = set_number )
249 AND yt.customer_id <> RACM.DUPLICATE_ID
250 FOR UPDATE NOWAIT;
251
252 BEGIN
253 IF ( process_mode = 'LOCK' ) Then
254
255 message_text := 'LOCKING the install_site_use_id ( 2/6 )';
256 arp_message.set_line(message_text);
257
258 OPEN LOCK_INSTALL_SITE_ID ( req_id, set_number );
259 CLOSE LOCK_INSTALL_SITE_ID;
260
261 message_text := 'Done locking Install_site_use_id';
262 arp_message.set_line(message_text);
263
264 ELSE
265
266 message_text :=
267 'Starting to update the install_site_use_id ( 2/6 )';
268 arp_message.set_line(message_text);
269
270 UPDATE CS_INCIDENTS yt
271 SET
272 yt.install_site_use_id =
273 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
274 FROM RA_CUSTOMER_MERGES RACM
275 WHERE yt.install_site_use_id
276 = DUPLICATE_SITE_ID
277 AND RACM.PROCESS_FLAG = 'N'
278 AND RACM.REQUEST_ID = req_id
279 AND RACM.SET_NUMBER = set_number ),
280 LAST_UPDATE_DATE = SYSDATE,
281 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
282 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
283 WHERE
284 yt.install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
285 FROM RA_CUSTOMER_MERGES RACM
286 WHERE RACM.PROCESS_FLAG = 'N'
287 AND RACM.REQUEST_ID = req_id
288 AND RACM.SET_NUMBER = set_number );
289
290 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
291 number_of_rows := sql%rowcount;
292 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
293 message_text := 'Done with the update of Install_site_use_id';
294 arp_message.set_line(message_text);
295
296 END IF;
297
298 EXCEPTION
299 WHEN NO_DATA_FOUND THEN
300
301 message_text :=
302 'Install_site_use_id NOT found -- proceeding ***';
303 arp_message.set_line(message_text);
304 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
305 number_of_rows := sql%rowcount;
306 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
307 message_text := 'Done with the update of Install_site_use_id';
308 arp_message.set_line(message_text);
309
310 WHEN OTHERS THEN
311
312 message_text := SUBSTR(SQLERRM,1,70);
313 arp_message.set_error('CS_MERGE_INSTALL_SITE_ID',
314 message_text);
315 raise;
316
317 END CS_MERGE_INSTALL_SITE_ID;
318
319 /* Update the ship to site id of CS_INCIDENTS */
320
324
321 PROCEDURE CS_MERGE_SHIP_TO_SITE_ID( req_id IN NUMBER,
322 set_number IN NUMBER,
323 process_mode IN VARCHAR2 ) IS
325 /* used to store a free form text to be written to the log file */
326
327 message_text varchar2(255);
328
329 /* number of rows updated */
330
331 number_of_rows NUMBER;
332
333 Cursor LOCK_SHIP_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
334 SELECT ship_to_site_use_id
335 FROM CS_INCIDENTS yt, RA_CUSTOMER_MERGES RACM
336 WHERE
337 yt.ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
338 FROM RA_CUSTOMER_MERGES RACM
339 WHERE RACM.PROCESS_FLAG = 'N'
340 AND RACM.REQUEST_ID = req_id
341 AND RACM.SET_NUMBER = set_number )
342 AND yt.customer_id <> RACM.DUPLICATE_ID
343 FOR UPDATE NOWAIT;
344
345 BEGIN
346 IF ( process_mode = 'LOCK' ) Then
347
348 message_text := 'LOCKING the ship_to_site_use_id ( 3/6 )';
349 arp_message.set_line(message_text);
350
351 OPEN LOCK_SHIP_SITE_ID ( req_id, set_number );
352 CLOSE LOCK_SHIP_SITE_ID;
353
354 message_text := 'Done locking ship_to_site_use_id';
355 arp_message.set_line(message_text);
356
357 ELSE
358
359 message_text := 'Starting to update the ship_to_site_use_id ( 3/6 )';
360 arp_message.set_line(message_text);
361
362 UPDATE CS_INCIDENTS yt
363 SET
364 yt.ship_to_site_use_id =
365 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
366 FROM RA_CUSTOMER_MERGES RACM
367 WHERE yt.ship_to_site_use_id
368 = DUPLICATE_SITE_ID
369 AND RACM.PROCESS_FLAG = 'N'
370 AND RACM.REQUEST_ID = req_id
371 AND RACM.SET_NUMBER = set_number ),
372 LAST_UPDATE_DATE = SYSDATE,
373 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
374 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
375 WHERE
376 yt.ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
377 FROM RA_CUSTOMER_MERGES RACM
378 WHERE RACM.PROCESS_FLAG = 'N'
379 AND RACM.REQUEST_ID = req_id
380 AND RACM.SET_NUMBER = set_number );
381
382 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
383 number_of_rows := sql%rowcount;
384 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
385 message_text := 'Done with the update of ship_to_site_use_id';
386 arp_message.set_line(message_text);
387
388 END IF;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392
393 message_text := 'ship_to_site_use_id NOT found -- proceeding ***';
394 arp_message.set_line(message_text);
395 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
396 number_of_rows := sql%rowcount;
397 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
398 message_text := 'Done with the update of ship_to_site_use_id';
399 arp_message.set_line(message_text);
400
401 WHEN OTHERS THEN
402
403 message_text := SUBSTR(SQLERRM,1,70);
404 arp_message.set_error('CS_MERGE_SHIP_TO_SITE_ID',
405 message_text);
406 raise;
407
408 END CS_MERGE_SHIP_TO_SITE_ID;
409
410 /* This process updates the customer_id of the CS_INCIDENTS table */
411
412 PROCEDURE CS_MERGE_CUSTOMER_ID (req_id IN NUMBER,
413 set_number IN NUMBER,
414 process_mode IN VARCHAR2 ) IS
415
416 /* used to store a free form text to be written to the log file */
417
418 message_text varchar2(255);
419
420 /* number of rows updated */
421
422 number_of_rows NUMBER;
423
424 Cursor LOCK_CUSTOMER_ID ( req_id NUMBER, set_number NUMBER ) IS
425 SELECT yt.customer_id
426 FROM CS_INCIDENTS yt, RA_CUSTOMER_MERGES RACM
427 WHERE
428 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
429 FROM RA_CUSTOMER_MERGES RACM
430 WHERE RACM.PROCESS_FLAG = 'N'
431 AND RACM.REQUEST_ID = req_id
432 AND RACM.SET_NUMBER = set_number )
433 FOR UPDATE NOWAIT;
434
435 BEGIN
436 IF ( process_mode = 'LOCK' ) Then
437
438 message_text := 'LOCKING the customer_id ( 6/6 )';
439 arp_message.set_line(message_text);
440
441 OPEN LOCK_CUSTOMER_ID ( req_id, set_number );
442 CLOSE LOCK_CUSTOMER_ID;
443
444 message_text := 'Done locking customer_id';
445 arp_message.set_line(message_text);
446
447 ELSE
448
449 message_text := 'Starting to update the customer_id ( 6/6 )';
450 arp_message.set_line(message_text);
454 yt.customer_id = ( SELECT DISTINCT RACM.CUSTOMER_ID
451
452 UPDATE CS_INCIDENTS yt
453 SET
455 FROM RA_CUSTOMER_MERGES RACM
456 WHERE yt.customer_id = DUPLICATE_ID
457 AND RACM.PROCESS_FLAG = 'N'
458 AND RACM.REQUEST_ID = req_id
459 AND RACM.SET_NUMBER = set_number ),
460 LAST_UPDATE_DATE = SYSDATE,
461 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
462 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
463 WHERE
464 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
465 FROM RA_CUSTOMER_MERGES RACM
466 WHERE RACM.PROCESS_FLAG = 'N'
467 AND RACM.REQUEST_ID = req_id
468 AND RACM.SET_NUMBER = set_number );
469
470 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
471 number_of_rows := sql%rowcount;
472 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
473 message_text := 'Done with the update of customer_id';
474 arp_message.set_line(message_text);
475
476 END IF;
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480
481 message_text := 'Customer_id NOT found -- proceeding *** ';
482 arp_message.set_line(message_text);
483 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
484 number_of_rows := sql%rowcount;
485 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
486 message_text := 'Done with the update of customer_id';
487 arp_message.set_line(message_text);
488
489 WHEN OTHERS THEN
490
491 message_text := SUBSTR(SQLERRM,1,70);
492 arp_message.set_error('CS_MERGE_CUSTOMER_ID',
493 message_text);
494 raise;
495
496 END CS_MERGE_CUSTOMER_ID;
497
498 /* Loop through using a cursor and try to identify for a request_Id un merged
499 records. For each record that should have been merged report it to the
500 log file.*/
501
502 PROCEDURE CS_CHECK_MERGE_DATA ( req_id IN NUMBER,
503 set_number IN NUMBER,
504 process_mode IN VARCHAR2 ) IS
505
506 /* templaray storage location for identifing the record in error */
507
508 current_serial_number varchar2(10);
509
510 /* used to store a free form text to be written to the log file */
511
512 message_text varchar2(255);
513
514 /* number of rows updated */
515
516 number_of_rows NUMBER;
517
518
519 CURSOR CS_CHECK IS
520 SELECT
521 DISTINCT
522 cs.incident_id
523 FROM CS_INCIDENTS CS,
524 RA_CUSTOMER_MERGES RACM
525 WHERE
526 RACM.PROCESS_FLAG = 'N' AND
527 RACM.REQUEST_ID = req_id AND
528 RACM.SET_NUMBER = set_number AND
529 ((( cs.customer_id = RACM.CUSTOMER_ID AND
530 cs.bill_to_site_use_id <> racm.customer_site_id AND
531 cs.bill_to_site_use_id IS NOT NULL ) AND
532 ( cs.customer_id NOT IN ( select racm.customer_id
533 from CS_INCIDENTS CS,
534 RA_CUSTOMER_MERGES RACM
535 where cs.customer_id = RACM.CUSTOMER_ID AND
536 cs.bill_to_site_use_id = racm.customer_site_id OR
537 cs.bill_to_site_use_id IS NULL ))) AND
538 (( cs.customer_id = RACM.CUSTOMER_ID AND
539 cs.install_site_use_id <> racm.customer_site_id AND
540 cs.install_site_use_id IS NOT NULL ) AND
541 ( cs.customer_id NOT IN ( select racm.customer_id
542 from CS_INCIDENTS CS,
543 RA_CUSTOMER_MERGES RACM
544 WHERE cs.customer_id = RACM.CUSTOMER_ID AND
545 cs.install_site_use_id = racm.customer_site_id OR
546 cs.install_site_use_id IS NULL ))) AND
547 (( cs.customer_id = RACM.CUSTOMER_ID AND
548 cs.ship_to_site_use_id <> racm.customer_site_id AND
549 cs.ship_to_site_use_id IS NOT NULL ) AND
550 ( cs.customer_id NOT IN ( select racm.customer_id
551 from CS_INCIDENTS CS,
552 RA_CUSTOMER_MERGES RACM
553 WHERE cs.customer_id = RACM.CUSTOMER_ID AND
554 cs.ship_to_site_use_id = racm.customer_site_id OR
555 cs.ship_to_site_use_id IS NULL ))));
556
557 BEGIN
558 IF ( process_mode <> 'LOCK' ) Then
559
560 message_text := '***-- Procedure CS_CHECK_MERGE_DATA --**';
561 arp_message.set_line(message_text);
562
563
564 OPEN CS_CHECK;
565
566 LOOP
567 FETCH CS_CHECK
568 INTO
569 current_serial_number;
570
571 EXIT WHEN CS_CHECK%NOTFOUND;
572 message_text :=
573 'WARNING, Following Product Serial Num. has address(s) not merged ';
574 arp_message.set_line(message_text);
575 message_text := current_serial_number;
576 arp_message.set_line(message_text);
577
581
578 END LOOP;
579
580 CLOSE CS_CHECK;
582 message_text := '***-- End CS_CHECK_MERGE_DATA --**';
583 arp_message.set_line(message_text);
584
585 END IF;
586
587 EXCEPTION
588
589 WHEN OTHERS THEN
590
591 message_text := SUBSTR(SQLERRM,1,70);
592 arp_message.set_error('CS_CHECK_MERGE_DATA',
593 message_text);
594 raise;
595
596 END CS_CHECK_MERGE_DATA;
597
598
599 END CSP_CMERGE_BB8;
600
[Home] [Help]