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