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