1 PACKAGE BODY csp_cmerge_bb1 AS
2 /* $Header: cscm101b.pls 115.2 99/07/16 08:47:22 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_ACCESS_CONTROL_TEMPLATES.
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_BB1.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_ACCESS_CONTROL_TEMPLATES',FALSE );
64 message_text := 'The locking is done in block CSP_CMERGE_BB1';
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_ACCESS_CONTROL_TEMPLATES',FALSE );
69 message_text := 'The merge is done in block CSP_CMERGE_BB1';
70 arp_message.set_line(message_text);
71 END IF;
72
73 /* merge the CS_ACCESS_CONTROL_TEMPLATES 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_ACCESS_CONTROL_TEMPLATES 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_ACCESS_CONTROL_TEMPLATES 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_ACCESS_CONTROL_TEMPLATES 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_ACCESS_CONTROL_TEMPLATES 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_ACCESS_CONTROL_TEMPLATES 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_ACCESS_CONTROL_TEMPLATES is complete */
130
131 IF ( process_mode = 'LOCK' ) Then
132 message_text := '** LOCKING completed for table CS_ACCESS_CONTROL_TEMPLATES **';
133 arp_message.set_line(message_text);
134 ELSE
135 message_text := '** MERGE completed for table CS_ACCESS_CONTROL_TEMPLATES **';
136 arp_message.set_line(message_text);
137 END IF;
138
139 arp_message.set_line('CP_CMERGE_BB1.MERGE()-');
140
141 END MERGE;
142
143 /* -----------------------------------------------------------------------------*/
144
145 /* Update the ship use site id of cs_access_control_templates */
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_ACCESS_CONTROL_TEMPLATES 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_ACCESS_CONTROL_TEMPLATES 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 yt.LAST_UPDATE_DATE = SYSDATE,
201 yt.LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
202 yt.LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN,
203 yt.CONCURRENT_REQUEST_ID = req_id
204 WHERE
205 yt.system_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
206 FROM RA_CUSTOMER_MERGES RACM
207 WHERE RACM.PROCESS_FLAG = 'N'
208 AND RACM.REQUEST_ID = req_id
209 AND RACM.SET_NUMBER = set_number );
210
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 system_ship_to_site_use_id';
215 arp_message.set_line(message_text);
216
217 END IF;
218
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221
222 message_text :=
223 'System_ship_to_site_use_id NOT found -- proceeding *** ';
224 arp_message.set_line(message_text);
225 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
226 number_of_rows := sql%rowcount;
227 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
228 message_text := 'Done with the update of system_ship_to_site_use_id';
229 arp_message.set_line(message_text);
230
231 WHEN OTHERS THEN
232
233 message_text := SUBSTR(SQLERRM,1,70);
234 arp_message.set_error('CS_MERGE_SYS_SHIP_SITE_ID',
235 message_text);
236 raise;
237
238 END CS_MERGE_SYS_SHIP_SITE_ID;
239
240 /* Update the ship use site id of CS_ACCESS_CONTROL_TEMPLATES */
241
242 PROCEDURE CS_MERGE_SYS_INSTALL_SITE_ID ( req_id IN NUMBER,
243 set_number IN NUMBER,
244 process_mode IN VARCHAR2 ) IS
245
246 /* used to store a free form text to be written to the log file */
247
248 message_text char(80);
249
250 /* number of rows updated */
251
252 number_of_rows NUMBER;
253
254 Cursor LOCK_SYS_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
255 SELECT system_install_site_use_id
256 FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
257 WHERE
258 yt.system_install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
259 FROM RA_CUSTOMER_MERGES RACM
260 WHERE RACM.PROCESS_FLAG = 'N'
261 AND RACM.REQUEST_ID = req_id
262 AND RACM.SET_NUMBER = set_number )
263 AND yt.customer_id <> RACM.DUPLICATE_ID
264 FOR UPDATE NOWAIT;
265
266 BEGIN
267 IF ( process_mode = 'LOCK' ) Then
268
269 message_text := 'LOCKING the system_install_site_use_id ( 2/5 )';
270 arp_message.set_line(message_text);
271
272 OPEN LOCK_SYS_INSTALL_SITE_ID ( req_id, set_number );
273 CLOSE LOCK_SYS_INSTALL_SITE_ID;
274
275 message_text := 'Done locking system_install_site_use_id';
276 arp_message.set_line(message_text);
277
278 ELSE
279
280 message_text :=
281 'Starting to update the system_install_site_use_id ( 2/5 )';
282 arp_message.set_line(message_text);
283
284 UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
285 SET
286 yt.system_install_site_use_id =
287 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
288 FROM RA_CUSTOMER_MERGES RACM
289 WHERE yt.system_install_site_use_id
290 = DUPLICATE_SITE_ID
291 AND RACM.PROCESS_FLAG = 'N'
292 AND RACM.REQUEST_ID = req_id
293 AND RACM.SET_NUMBER = set_number ),
294 LAST_UPDATE_DATE = SYSDATE,
295 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
296 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN,
297 CONCURRENT_REQUEST_ID = req_id
298 WHERE
299 yt.system_install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
300 FROM RA_CUSTOMER_MERGES RACM
301 WHERE RACM.PROCESS_FLAG = 'N'
302 AND RACM.REQUEST_ID = req_id
303 AND RACM.SET_NUMBER = set_number );
304
305 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
306 number_of_rows := sql%rowcount;
307 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
308 message_text := 'Done with the update of system_install_site_use_id';
309 arp_message.set_line(message_text);
310
311 END IF;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315
316 message_text :=
317 'System_install_site_use_id NOT found -- proceeding ***';
318 arp_message.set_line(message_text);
319 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
320 number_of_rows := sql%rowcount;
321 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
322 message_text := 'Done with the update of system_install_site_use_id';
323 arp_message.set_line(message_text);
324
325 WHEN OTHERS THEN
326
327 message_text := SUBSTR(SQLERRM,1,70);
328 arp_message.set_error('CS_MERGE_SYS_INSTALL_SITE_ID',
329 message_text);
330 raise;
331
332 END CS_MERGE_SYS_INSTALL_SITE_ID;
333
334 /* Update the ship use site id of CS_ACCESS_CONTROL_TEMPLATES */
335
336 PROCEDURE CS_MERGE_CP_SHIP_SITE_ID( req_id IN NUMBER,
337 set_number IN NUMBER,
338 process_mode IN VARCHAR2 ) IS
339
340 /* used to store a free form text to be written to the log file */
341
342 message_text char(80);
343
344 /* number of rows updated */
345
346 number_of_rows NUMBER;
347
348 Cursor LOCK_CP_SHIP_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
349 SELECT cp_ship_to_site_use_id
350 FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
351 WHERE
352 yt.cp_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
353 FROM RA_CUSTOMER_MERGES RACM
354 WHERE RACM.PROCESS_FLAG = 'N'
355 AND RACM.REQUEST_ID = req_id
356 AND RACM.SET_NUMBER = set_number )
357 AND yt.customer_id <> RACM.DUPLICATE_ID
358 FOR UPDATE NOWAIT;
359
360 BEGIN
361 IF ( process_mode = 'LOCK' ) Then
362
363 message_text := 'LOCKING the cp_ship_to_site_use_id ( 3/5 )';
364 arp_message.set_line(message_text);
365
366 OPEN LOCK_CP_SHIP_SITE_ID ( req_id, set_number );
367 CLOSE LOCK_CP_SHIP_SITE_ID;
368
369 message_text := 'Done locking cp_ship_to_site_use_id';
370 arp_message.set_line(message_text);
371
372 ELSE
373
374 message_text := 'Starting to update the cp_ship_to_site_use_id ( 3/5 )';
375 arp_message.set_line(message_text);
376
377 UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
378 SET
379 yt.cp_ship_to_site_use_id =
380 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
381 FROM RA_CUSTOMER_MERGES RACM
382 WHERE yt.cp_ship_to_site_use_id
383 = DUPLICATE_SITE_ID
384 AND RACM.PROCESS_FLAG = 'N'
385 AND RACM.REQUEST_ID = req_id
386 AND RACM.SET_NUMBER = set_number ),
387 LAST_UPDATE_DATE = SYSDATE,
388 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
389 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN,
390 CONCURRENT_REQUEST_ID = req_id
391 WHERE
392 yt.cp_ship_to_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
393 FROM RA_CUSTOMER_MERGES RACM
394 WHERE RACM.PROCESS_FLAG = 'N'
395 AND RACM.REQUEST_ID = req_id
396 AND RACM.SET_NUMBER = set_number );
397
398 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
399 number_of_rows := sql%rowcount;
400 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
401 message_text := 'Done with the update of cp_ship_to_site_use_id';
402 arp_message.set_line(message_text);
403
404 END IF;
405
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408
409 message_text := 'Cp_ship_to_site_use_id NOT found -- proceeding ***';
410 arp_message.set_line(message_text);
411 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
412 number_of_rows := sql%rowcount;
413 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
414 message_text := 'Done with the update of cp_ship_to_site_use_id';
415 arp_message.set_line(message_text);
416
417 WHEN OTHERS THEN
418
419 message_text := SUBSTR(SQLERRM,1,70);
420 arp_message.set_error('CS_MERGE_CP_SHIP_SITE_ID',
421 message_text);
422 raise;
423
424 END CS_MERGE_CP_SHIP_SITE_ID;
425
426 /* Update the ship use site id of CS_ACCESS_CONTROL_TEMPLATES */
427
428 PROCEDURE CS_MERGE_CP_INSTALL_SITE_ID ( req_id IN NUMBER,
429 set_number IN NUMBER,
430 process_mode IN VARCHAR2 ) IS
431
432 /* used to store a free form text to be written to the log file */
433
434 message_text char(80);
435
436 /* number of rows updated */
437
438 number_of_rows NUMBER;
439
440 Cursor LOCK_CP_INSTALL_SITE_ID ( req_id NUMBER, set_number NUMBER ) IS
441 SELECT cp_install_site_use_id
442 FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
443 WHERE
444 yt.cp_install_site_use_id IN ( SELECT RACM.DUPLICATE_SITE_ID
445 FROM RA_CUSTOMER_MERGES RACM
446 WHERE RACM.PROCESS_FLAG = 'N'
447 AND RACM.REQUEST_ID = req_id
448 AND RACM.SET_NUMBER = set_number )
449 AND yt.customer_id <> RACM.DUPLICATE_ID
450 FOR UPDATE NOWAIT;
451
452 BEGIN
453 IF ( process_mode = 'LOCK' ) Then
454
455 message_text := 'LOCKING the cp_install_site_use_id ( 4/5 )';
456 arp_message.set_line(message_text);
457
458 OPEN LOCK_CP_INSTALL_SITE_ID ( req_id, set_number );
459 CLOSE LOCK_CP_INSTALL_SITE_ID;
460
461 message_text := 'Done locking cp_install_site_use_id';
462 arp_message.set_line(message_text);
463
464 ELSE
465
466 message_text :=
467 'Starting to update the cp_install_site_use_id ( 4/5 )';
468 arp_message.set_line(message_text);
469
470 UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
471 SET
472 yt.cp_install_site_use_id =
473 ( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
474 FROM RA_CUSTOMER_MERGES RACM
475 WHERE yt.cp_install_site_use_id
476 = DUPLICATE_SITE_ID
477 AND RACM.PROCESS_FLAG = 'N'
478 AND RACM.REQUEST_ID = req_id
479 AND RACM.SET_NUMBER = set_number ),
480 LAST_UPDATE_DATE = SYSDATE,
481 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
482 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN,
483 CONCURRENT_REQUEST_ID = req_id
484 WHERE
485 yt.cp_install_site_use_Id IN ( SELECT RACM.DUPLICATE_SITE_ID
486 FROM RA_CUSTOMER_MERGES RACM
487 WHERE RACM.PROCESS_FLAG = 'N'
488 AND RACM.REQUEST_ID = req_id
489 AND RACM.SET_NUMBER = set_number );
490
491 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
492 number_of_rows := sql%rowcount;
493 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
494 message_text := 'Done with the update of cp_install_site_use_id';
495 arp_message.set_line(message_text);
496
497 END IF;
498
499 EXCEPTION
500 WHEN NO_DATA_FOUND THEN
501
502 message_text := 'cp_install_site_id NOT found -- proceeding *** ';
503 arp_message.set_line(message_text);
504 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
505 number_of_rows := sql%rowcount;
506 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
507 message_text := 'Done with the update of cp_install_site_use_id';
508 arp_message.set_line(message_text);
509
510 WHEN OTHERS THEN
511
512 message_text := SUBSTR(SQLERRM,1,70);
513 arp_message.set_error('CS_MERGE_CP_INSTALL_SITE_ID',
514 message_text);
515 raise;
516
517 END CS_MERGE_CP_INSTALL_SITE_ID;
518
519 /* This process updates the customer_id of the CS_ACCESS_CONTROL_TEMPLATES table */
520
521 PROCEDURE CS_MERGE_CUSTOMER_ID (req_id IN NUMBER,
522 set_number IN NUMBER,
523 process_mode IN VARCHAR2 ) IS
524
525 /* used to store a free form text to be written to the log file */
526
527 message_text char(80);
528
529 /* number of rows updated */
530
531 number_of_rows NUMBER;
532
533 Cursor LOCK_CUSTOMER_ID ( req_id NUMBER, set_number NUMBER ) IS
534 SELECT yt.customer_id
535 FROM CS_ACCESS_CONTROL_TEMPLATES yt, RA_CUSTOMER_MERGES RACM
536 WHERE
537 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
538 FROM RA_CUSTOMER_MERGES RACM
539 WHERE RACM.PROCESS_FLAG = 'N'
540 AND RACM.REQUEST_ID = req_id
541 AND RACM.SET_NUMBER = set_number )
542 FOR UPDATE NOWAIT;
543
544 BEGIN
545 IF ( process_mode = 'LOCK' ) Then
546
547 message_text := 'LOCKING the customer_id ( 5/5 )';
548 arp_message.set_line(message_text);
549
550 OPEN LOCK_CUSTOMER_ID ( req_id, set_number );
551 CLOSE LOCK_CUSTOMER_ID;
552
553 message_text := 'Done locking customer_id';
554 arp_message.set_line(message_text);
555
556 ELSE
557
558 message_text := 'Starting to update the customer_id ( 5/5 )';
559 arp_message.set_line(message_text);
560
561 UPDATE CS_ACCESS_CONTROL_TEMPLATES yt
562 SET
563 yt.customer_id = ( SELECT DISTINCT RACM.CUSTOMER_ID
564 FROM RA_CUSTOMER_MERGES RACM
565 WHERE yt.customer_id = DUPLICATE_ID
566 AND RACM.PROCESS_FLAG = 'N'
567 AND RACM.REQUEST_ID = req_id
568 AND RACM.SET_NUMBER = set_number ),
569 LAST_UPDATE_DATE = SYSDATE,
570 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
571 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN,
572 CONCURRENT_REQUEST_ID = req_id
573 WHERE
574 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
575 FROM RA_CUSTOMER_MERGES RACM
576 WHERE RACM.PROCESS_FLAG = 'N'
577 AND RACM.REQUEST_ID = req_id
578 AND RACM.SET_NUMBER = set_number );
579
580 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
581 number_of_rows := sql%rowcount;
582 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
583 message_text := 'Done with the update of customer_id';
584 arp_message.set_line(message_text);
585
586 END IF;
587
588 EXCEPTION
589 WHEN NO_DATA_FOUND THEN
590
591 message_text := 'Request id NOT found -- proceeding *** ';
592 arp_message.set_line(message_text);
593 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
594 number_of_rows := sql%rowcount;
595 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
596 message_text := 'Done with the update of customer_id';
597 arp_message.set_line(message_text);
598
599 WHEN OTHERS THEN
600
601 message_text := SUBSTR(SQLERRM,1,70);
602 arp_message.set_error('CS_MERGE_CUSTOMER_ID',
603 message_text);
604 raise;
605
606 END CS_MERGE_CUSTOMER_ID;
607
608 /* Loop through using a cursor and try to identify for a request_Id un merged
609 records. For each record that should have been merged report it to the
610 log file.*/
611
612 PROCEDURE CS_CHECK_MERGE_DATA ( req_id IN NUMBER,
613 set_number IN NUMBER,
614 process_mode IN VARCHAR2 ) IS
615
616 /* templaray storage location for identifing the record in error */
617
618 access_control_template_id NUMBER;
619
620 /* used to store a free form text to be written to the log file */
621
622 message_text char(80);
623
624 /* number of rows updated */
625
626 number_of_rows NUMBER;
627
628
629 CURSOR CS_CHECK IS
630 SELECT
631 DISTINCT
632 cs.access_control_template_id
633 FROM CS_ACCESS_CONTROL_TEMPLATES CS,
634 RA_CUSTOMER_MERGES RACM
635 WHERE
636 RACM.PROCESS_FLAG = 'N' AND
637 RACM.REQUEST_ID = req_id AND
638 RACM.SET_NUMBER = set_number AND
639 ( (( cs.customer_id = RACM.CUSTOMER_ID AND
640 cs.system_ship_to_site_use_id <> racm.customer_site_id AND
641 cs.system_ship_to_site_use_id IS NOT NULL ) AND
642 ( cs.customer_id NOT IN ( select racm.customer_id
643 from CS_ACCESS_CONTROL_TEMPLATES CS,
644 RA_CUSTOMER_MERGES RACM
645 where cs.customer_id = RACM.CUSTOMER_ID AND
646 cs.system_ship_to_site_use_id = racm.customer_site_id or
647 cs.system_ship_to_site_use_id IS NULL ))) AND
648 (( cs.customer_id = RACM.CUSTOMER_ID AND
649 cs.system_install_site_use_id <> racm.customer_site_id AND
650 cs.system_install_site_use_id IS NOT NULL ) AND
651 ( cs.customer_id NOT IN ( select racm.customer_id
652 from CS_ACCESS_CONTROL_TEMPLATES CS,
653 RA_CUSTOMER_MERGES RACM
654 where cs.customer_id = RACM.CUSTOMER_ID AND
655 cs.system_install_site_use_id = racm.customer_site_id or
656 cs.system_install_site_use_id IS NULL ))) AND
657 (( cs.customer_id = RACM.CUSTOMER_ID AND
658 cs.cp_install_site_use_id <> racm.customer_site_id AND
659 cs.cp_install_site_use_id IS NOT NULL ) AND
660 ( cs.customer_id NOT IN ( select racm.customer_id
661 from CS_ACCESS_CONTROL_TEMPLATES CS,
662 RA_CUSTOMER_MERGES RACM
663 where cs.customer_id = RACM.CUSTOMER_ID AND
664 cs.cp_install_site_use_id = racm.customer_site_id or
665 cs.cp_install_site_use_id IS NULL ))) AND
666 (( cs.customer_id = RACM.CUSTOMER_ID AND
667 cs.cp_ship_to_site_use_id <> racm.customer_site_id AND
668 cs.cp_ship_to_site_use_id IS NOT NULL ) AND
669 ( cs.customer_id NOT IN ( select racm.customer_id
670 from CS_ACCESS_CONTROL_TEMPLATES CS,
671 RA_CUSTOMER_MERGES RACM
672 where cs.customer_id = RACM.CUSTOMER_ID AND
673 cs.cp_ship_to_site_use_id = racm.customer_site_id or
674 cs.cp_ship_to_site_use_id IS NULL )) ));
675
676 BEGIN
677 IF ( process_mode <> 'LOCK' ) Then
678
679 message_text := '***-- Procedure CS_CHECK_MERGE_DATA --**';
680 arp_message.set_line(message_text);
681
682
683 OPEN CS_CHECK;
684
685 LOOP
686 FETCH CS_CHECK
687 INTO
688 access_control_template_id;
689
690 EXIT WHEN CS_CHECK%NOTFOUND;
691 message_text :=
692 'WARNING, Following access control template id has address(s) not merged ';
693 arp_message.set_line(message_text);
694 message_text := access_control_template_id;
695 arp_message.set_line(message_text);
696
697 END LOOP;
698
699 CLOSE CS_CHECK;
700
701 message_text := '***-- End CS_CHECK_MERGE_DATA --**';
702 arp_message.set_line(message_text);
703
704 END IF;
705
706 EXCEPTION
707
708 WHEN OTHERS THEN
709
710 message_text := SUBSTR(SQLERRM,1,70);
711 arp_message.set_error('CS_CHECK_MERGE_DATA',
712 message_text);
713 raise;
714
715 END CS_CHECK_MERGE_DATA;
716
717 END CSP_CMERGE_BB1;
[Home] [Help]