[Home] [Help]
PACKAGE BODY: APPS.IEB_SYNC_SERVER
Source
1 PACKAGE BODY IEB_SYNC_SERVER AS
2 /* $Header: IEBSVRB.pls 115.28 2004/02/10 19:35:48 gpagadal noship $ */
3
4 PROCEDURE GET_WB_SERVER_LIST (
5 p_language IN varchar2,
6 p_order_by IN varchar2,
7 p_asc IN varchar2,
8 x_wb_servers_list OUT NOCOPY SYSTEM.IEB_WB_SERVERS_DATA_NST,
9 x_return_status OUT NOCOPY VARCHAR2)
10 AS
11
12 v_cursorID INTEGER;
13 v_selectStmt VARCHAR2(5000);
14 v_dummy INTEGER;
15
16 v_serverId NUMBER(15);
17 v_serverName VARCHAR2(80);
18 v_fileName VARCHAR2(256);
19 v_logDbcFileName VARCHAR2(256);
20 v_cciDbcFileName VARCHAR2(256);
21 v_sunday VARCHAR2(1);
22 v_monday VARCHAR2(1);
23 v_tuesday VARCHAR2(1);
24 v_wednesday VARCHAR2(1);
25 v_thursday VARCHAR2(1);
26 v_friday VARCHAR2(1);
27 v_saturday VARCHAR2(1);
28 v_beginTime NUMBER(4);
29 v_endTime NUMBER(4);
30 v_cleanUp VARCHAR2(1);
31 v_cleanUpTime NUMBER(4);
32 v_shutdown VARCHAR2(1);
33 v_shutdownTime NUMBER(4);
34 v_cleanupSize NUMBER(8);
35 v_cacheSize NUMBER(8);
36 v_traceFileName VARCHAR2(256);
37 v_serverType VARCHAR2(20);
38 v_desc VARCHAR2(240);
39 v_method VARCHAR2(32);
40 v_objName VARCHAR2(32);
41 v_dnsName VARCHAR2(32);
42 v_ipAddress VARCHAR2(15);
43 v_portNumber NUMBER(15);
44 v_param1 VARCHAR2(32);
45 v_param2 VARCHAR2(32);
46 v_param3 VARCHAR2(32);
47 v_param4 VARCHAR2(32);
48 v_checkNew VARCHAR2(1);
49
50 BEGIN
51 x_return_status := fnd_api.g_ret_sts_success;
52
53 IEB_SYNC_SERVER.SYNC_SERVER(p_language, x_return_status);
54
55
56 v_cursorID := DBMS_SQL.OPEN_CURSOR;
57
58 v_selectStmt := 'select WBSVR_ID, WB_SERVER_NAME, LOG_FILE_NAME,
59 LOG_DBC_FILE_NAME, CCI_DBC_FILE_NAME, STAT_DUMP_SUNDAY_Y_N,
60 STAT_DUMP_MONDAY_Y_N, STAT_DUMP_TUESDAY_Y_N,
61 STAT_DUMP_WEDNESDAY_Y_N, DESCRIPTION
62 from IEB_WB_SERVERS
63 order by ' || p_order_by || ' ' || p_asc;
64
65 DBMS_SQL.PARSE(v_cursorID, v_selectStmt, DBMS_SQL.V7);
66
67 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 1, v_serverId);
68 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 2, v_serverName, 80);
69 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 3, v_fileName, 256);
70 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 4, v_logDbcFileName, 256);
71 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 5, v_cciDbcFileName, 256);
72 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 6, v_sunday, 1);
73 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 7, v_monday, 1);
74 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 8, v_tuesday, 1);
75 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 9, v_wednesday,1);
76 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 10, v_desc, 240);
77
78 v_dummy := DBMS_SQL.EXECUTE(v_cursorID);
79
80 x_wb_servers_list := SYSTEM.IEB_WB_SERVERS_DATA_NST();
81 loop
82 if DBMS_SQL.FETCH_ROWS(v_cursorID) = 0 then
83 exit;
84 end if;
85
86 DBMS_SQL.COLUMN_VALUE(v_cursorID, 1, v_serverId);
87 DBMS_SQL.COLUMN_VALUE(v_cursorID, 2, v_serverName);
88 DBMS_SQL.COLUMN_VALUE(v_cursorID, 3, v_fileName);
89 DBMS_SQL.COLUMN_VALUE(v_cursorID, 4, v_logDbcFileName);
90 DBMS_SQL.COLUMN_VALUE(v_cursorID, 5, v_cciDbcFileName);
91 DBMS_SQL.COLUMN_VALUE(v_cursorID, 6, v_sunday);
92 DBMS_SQL.COLUMN_VALUE(v_cursorID, 7, v_monday);
93 DBMS_SQL.COLUMN_VALUE(v_cursorID, 8, v_tuesday);
94 DBMS_SQL.COLUMN_VALUE(v_cursorID, 9, v_wednesday);
95 DBMS_SQL.COLUMN_VALUE(v_cursorID, 10, v_desc);
96
97 x_wb_servers_list.EXTEND;
98 x_wb_servers_list(x_wb_servers_list.LAST) := SYSTEM.IEB_WB_SERVERS_DATA_OBJ(v_serverId,
99 v_serverName,
100 v_fileName, v_logDbcFileName,
101 v_cciDbcFileName, v_sunday,
102 v_monday, v_tuesday, v_wednesday, v_thursday,
103 v_friday, v_saturday,
104 v_beginTime,
105 v_endTime,
106 v_cleanUp, v_cleanUpTime,
107 v_shutdown, v_shutdownTime,
108 v_cleanupSize,
109 v_cacheSize,
110 v_traceFileName, v_serverType,
111 v_desc, v_method,
112 v_objName, v_dnsName,
113 v_ipAddress, v_portNumber,
114 v_param1, v_param2,
115 v_param3, v_param4,
116 v_checkNew);
117 end loop;
118 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
119 EXCEPTION
120 WHEN fnd_api.g_exc_error THEN
121 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
122 x_return_status := fnd_api.g_ret_sts_error;
123
124 WHEN fnd_api.g_exc_unexpected_error THEN
125 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
126 x_return_status := fnd_api.g_ret_sts_unexp_error;
127
128 WHEN OTHERS THEN
129 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
130 x_return_status := fnd_api.g_ret_sts_unexp_error;
131 END GET_WB_SERVER_LIST;
132
133 PROCEDURE SYNC_SERVER (
134 p_language IN varchar2,
135 x_return_status OUT NOCOPY VARCHAR2 )
136 IS
137 cursor c1 is
138 select *
139 from IEO_SVR_SERVERS
140 where type_id = 10020
141 and not exists (select 'x' from IEB_WB_SERVERS SRV, IEB_WB_SVC_CATS SVC
142 where SRV.WB_SERVER_NAME = SERVER_NAME AND
143 SVC.WBSVR_WBSVR_ID = SRV.WBSVR_ID );
144 l_wbsvr_id number(15,0);
145 l_ieo_svr_id number(15,0);
146
147 BEGIN
148 x_return_status := fnd_api.g_ret_sts_success;
149
150 -- there are 2 parts to syncing the schema - one is to delete data for
151 -- servers that no longer exist in ieo schema, and the other is to
152 -- create data for new servers created in ieo schema
153
154 -- this takes care of deleting data from ieb schema
155 DELETE FROM ieb_wb_servers WHERE wb_server_name NOT IN
156 (SELECT server_name FROM ieo_svr_servers WHERE type_id = 10020);
157
158 if (sql%notfound) then
159 null; -- not finding any such iebs is good
160 else
161 -- delete the service categories of the deleted servers
162 DELETE FROM ieb_wb_svc_cats WHERE wbsvr_wbsvr_id NOT IN
163 (SELECT wbsvr_id FROM ieb_wb_servers);
164
165 IF (sql%notfound) then
166 null; -- not finding any data is not too good because there should be
167 -- default service categories here; but no problem, may be the
168 -- data is messed up
169 else
170 -- delete any service category rules for the deleted service categories
171 DELETE FROM ieb_wb_svc_cat_rules WHERE wbsc_wbsc_id NOT IN
172 (SELECT wbsc_id FROM ieb_wb_svc_cats);
173
174 IF (sql%notfound) then
175 null; -- is ok if no data found here
176 END if;
177
178 END if;
179
180 commit; -- got to save the changes
181
182 END if;
183
184 -- this takes care of creating data for new servers.
185 for c1_rec in c1 loop
186
187 l_wbsvr_id := NULL;
188 l_ieo_svr_id := NULL;
189 begin
190 select wbsvr_id, ieo_server_id into l_wbsvr_id, l_ieo_svr_id
191 from IEB_WB_SERVERS
192 where WB_server_name = c1_rec.server_name;
193 exception
194 when others then
195 l_wbsvr_id := NULL;
196 l_ieo_svr_id := NULL;
197 end;
198
199 if ( l_wbsvr_id IS NULL ) then
200 select IEB_WB_SERVER_S1.NEXTVAL into l_wbsvr_id from sys.dual;
201 insert into IEB_WB_SERVERS
202 (wbsvr_id,
203 created_by,
204 creation_date,
205 last_updated_by,
206 last_update_date,
207 last_update_login,
208 wb_server_name,
209 log_file_name,
210 log_dbc_file_name,
211 cci_dbc_file_name,
212 stat_dump_sunday_y_n,
213 stat_dump_monday_y_n,
214 stat_dump_tuesday_y_n,
215 stat_dump_wednesday_y_n,
216 stat_dump_thursday_y_n,
217 stat_dump_friday_y_n,
218 stat_dump_saturday_y_n,
219 stat_dump_beg_time_hhmm,
220 stat_dump_end_time_hhmm,
221 daily_cleanup_y_n,
222 cleanup_time_hhmm,
223 auto_shut_down_y_n,
224 auto_shut_down_time_hhmm,
225 virtual_q_cleanup_size,
226 work_queue_cache_size,
227 trace_file_name,
228 wb_server_type,
229 description,
230 communication_method,
231 com_object_name,
232 com_dns_name,
233 com_ip_address,
234 com_port_number,
235 com_param1,
236 com_param2,
237 com_param3,
238 com_param4,
239 object_version_number,
240 security_group_id,
241 check_new_sc_entry,
242 ieo_server_id)
243 VALUES (l_wbsvr_id,
244 FND_GLOBAL.USER_ID,
245 sysdate,
246 FND_GLOBAL.USER_ID,
247 sysdate,
248 FND_GLOBAL.LOGIN_ID,
249 c1_rec.server_name,
250 NULL, NULL, NULL,
251 'N','N','N','N','N','N','N',
252 NULL, NULL,
253 'N',
254 NULL,
255 'N',
256 NULL,
257 0,
258 0,
259 NULL,
260 'N',
261 c1_rec.description,
262 'RMI',
263 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
264 0,
265 0,
266 NULL,
267 c1_rec.server_id);
268 else if ( l_ieo_svr_id IS NULL ) then
269 Update IEB_WB_SERVERS
270 Set IEO_SERVER_ID = c1_rec.server_id
271 Where WB_server_name = c1_rec.server_name;
272 end if;
273 end if;
274
275 IEB_SYNC_SERVER.INSERT_SVC_CAT_ENTRIES(p_language, l_wbsvr_id, x_return_status);
276
277 exit when c1%notfound;
278 commit;
279 end loop;
280
281 update ieb_wb_servers a
282 set a.IEO_SERVER_ID = (select b.server_id from ieo_svr_servers b where a.WB_SERVER_NAME = b.SERVER_NAME);
283 if (sql%notfound) then
284 null;
285 end if;
286 commit;
287
288 IEB_SYNC_SERVER.SYNC_CAT_ENTRIES(p_language, x_return_status);
289
290 EXCEPTION
291 WHEN fnd_api.g_exc_error THEN
292 x_return_status := fnd_api.g_ret_sts_error;
293
294 WHEN fnd_api.g_exc_unexpected_error THEN
295 x_return_status := fnd_api.g_ret_sts_unexp_error;
296
297 WHEN OTHERS THEN
298 x_return_status := fnd_api.g_ret_sts_unexp_error;
299
300 END SYNC_SERVER;
301
302 PROCEDURE INSERT_SVC_CAT_ENTRIES (
303 p_language IN varchar2,
304 p_wbsvr_id IN number,
305 x_return_status OUT NOCOPY varchar2 )
306 IS
307 cursor c1 is
308 select a.direction, b.created_by, b.creation_date, b.last_updated_by,
309 b.last_update_date, b.last_update_login,
313 b.security_group_id
310 tl.service_category_name, b.media_type_id,
311 b.active_y_n, b.media_type, tl.description,
312 b.depth, b.svcpln_svcpln_id, b.object_version_number,
314 from ieb_service_plans a, ieb_svc_cat_temps_tl tl,
315 ieb_svc_cat_temps_b b
316 where b.wbsc_id = tl.wbsc_id
317 and svcpln_svcpln_id = svcpln_id
318 and language = p_language
319 order by depth, direction;
320 l_wbsc_id number(15,0);
321 l_parent_id number(15,0);
322 l_parent_id_ib number(15,0);
323 l_parent_id_ob number(15,0);
324 begin
325 x_return_status := fnd_api.g_ret_sts_success;
326
327 for c1_rec in c1 loop
328 select IEB_SVC_CATS_S1.NEXTVAL into l_wbsc_id from sys.dual;
329 if c1_rec.depth = 0 then
330 l_parent_id := NULL;
331 l_parent_id_ib := l_wbsc_id;
332 l_parent_id_ob := l_wbsc_id;
333 end if;
334
335 if c1_rec.depth = 1 and c1_rec.direction = 'INBOUND' then
336 l_parent_id := l_parent_id_ib;
337 l_parent_id_ib := l_wbsc_id;
338 elsif c1_rec.depth = 1 and c1_rec.direction = 'OUTBOUND' then
339 l_parent_id := l_parent_id_ob;
340 l_parent_id_ob := l_wbsc_id;
341 end if;
342
343 if c1_rec.depth = 2 and c1_rec.direction = 'INBOUND' then
344 l_parent_id := l_parent_id_ib;
345 elsif c1_rec.depth = 2 and c1_rec.direction = 'OUTBOUND' then
346 l_parent_id := l_parent_id_ob;
347 end if;
348
349 insert into IEB_WB_SVC_CATS
350 (wbsc_id,
351 created_by,
352 creation_date,
353 last_updated_by,
354 last_update_date,
355 last_update_login,
356 service_category_name,
357 campaign_server_name,
358 campaign_name,
359 active_y_n,
360 media_type,
361 description,
362 priority,
363 depth,
364 wbsvr_wbsvr_id,
365 parent_id,
366 svcpln_svcpln_id,
367 media_type_id,
368 default_flag,
369 object_version_number,
370 security_group_id)
371 values (l_wbsc_id,
372 FND_GLOBAL.USER_ID,
373 sysdate,
374 FND_GLOBAL.USER_ID,
375 sysdate,
376 FND_GLOBAL.LOGIN_ID,
377 c1_rec.service_category_name,
378 NULL, NULL,
379 c1_rec.active_y_n,
380 c1_rec.media_type,
381 c1_rec.description,
382 0,
383 c1_rec.depth,
384 p_wbsvr_id,
385 l_parent_id,
386 c1_rec.svcpln_svcpln_id,
387 c1_rec.media_type_id,
388 'Y',
389 0,
390 0);
391
392 exit when c1%notfound;
393 end loop;
394 EXCEPTION
395 WHEN fnd_api.g_exc_error THEN
396 x_return_status := fnd_api.g_ret_sts_error;
397
398 WHEN fnd_api.g_exc_unexpected_error THEN
399 x_return_status := fnd_api.g_ret_sts_unexp_error;
400
401 WHEN OTHERS THEN
402 x_return_status := fnd_api.g_ret_sts_unexp_error;
403
404 END INSERT_SVC_CAT_ENTRIES;
405
406
407
408 PROCEDURE SYNC_CAT_ENTRIES (
409 p_language IN varchar2,
410 x_return_status OUT NOCOPY VARCHAR2 )
411 IS
412 l_default_count number(5);
413 l_count number(5);
414
415 cursor c1 is
416 select * from ieb_wb_servers WHERE wb_server_name IN
417 (SELECT server_name FROM ieo_svr_servers WHERE type_id = 10020);
418
419
420 cursor c2 is
421 select a.direction, b.created_by, b.creation_date, b.last_updated_by,
422 b.last_update_date, b.last_update_login,
423 tl.service_category_name, b.media_type_id,
424 b.active_y_n, b.media_type, tl.description,
425 b.depth, b.svcpln_svcpln_id, b.object_version_number,
426 b.security_group_id
427 from ieb_service_plans a, ieb_svc_cat_temps_tl tl,
428 ieb_svc_cat_temps_b b
429 where b.wbsc_id = tl.wbsc_id
430 and svcpln_svcpln_id = svcpln_id
431 and language = 'US'
432 order by depth, direction;
433
434 l_wbsc_id number(15,0);
435 l_parent_id number(15,0);
436 l_root_id_ib number(15,0);
437 l_root_id_ob number(15,0);
438 l_root_id number(15,0);
439
440 l_depth number(15,0);
441
442
443
444
445 BEGIN
446 x_return_status := fnd_api.g_ret_sts_success;
447 l_default_count := 0;
448 l_count := 0;
449
450
451
452 for c1_rec in c1 loop
453
454 for c1_rec2 in c2 loop
455
456
457 begin
458
459 select WBSC_ID, depth into l_wbsc_id ,l_depth from ieb_wb_svc_cats
460 where service_category_name = c1_rec2.service_category_name
461 and wbsvr_wbsvr_id =c1_rec.wbsvr_id;
462
463
464
465
466 if (l_depth = 0) then
470
467 l_parent_id := NULL;
468 l_root_id_ib := l_wbsc_id;
469 l_root_id_ob := l_wbsc_id;
471 end if;
472
473 if (l_depth = 1) then
474 if (c1_rec2.direction = 'INBOUND')then
475 l_parent_id := l_root_id_ib;
476 l_root_id_ib := l_wbsc_id;
477 elsif (c1_rec2.direction = 'OUTBOUND') then
478 l_parent_id := l_root_id_ob;
479 l_root_id_ob := l_wbsc_id;
480 end if;
481 end if;
482
483 if (l_depth = 2) then
484 if (c1_rec2.direction = 'INBOUND') then
485 l_parent_id := l_root_id_ib;
486 -- l_root_id_ib := l_wbsc_id;
487 elsif (c1_rec2.direction = 'OUTBOUND') then
488 l_parent_id := l_root_id_ob;
489 -- l_root_id_ob := l_wbsc_id;
490 end if;
491 end if;
492
493 EXCEPTION
494 when NO_DATA_FOUND then
495 null;
496
497 end;
498
499
500
501 update IEB_WB_SVC_CATS set
502 media_type_id = c1_rec2.media_type_id,
503 default_flag = 'Y'
504 where
505 wbsvr_wbsvr_id =c1_rec.wbsvr_id
506 and service_category_name = c1_rec2.service_category_name
507 and media_type = c1_rec2.media_type;
508
509 -- commit;
510
511 if (sql%notfound) then
512
513 select IEB_SVC_CATS_S1.NEXTVAL into l_wbsc_id from sys.dual;
514 if c1_rec2.depth = 0 then
515 l_parent_id := NULL;
516 l_root_id_ib := l_wbsc_id;
517 l_root_id_ob := l_wbsc_id;
518
519 end if;
520 if c1_rec2.depth = 1 then
521 if (c1_rec2.direction = 'INBOUND') then
522 l_parent_id := l_root_id_ib;
523 l_root_id_ib := l_wbsc_id;
524
525 elsif (c1_rec2.direction = 'OUTBOUND') then
526 l_parent_id := l_root_id_ob;
527 l_root_id_ob := l_wbsc_id;
528 end if;
529
530 end if ;
531 if c1_rec2.depth = 2 then
532 if (c1_rec2.direction = 'INBOUND') then
533 l_parent_id := l_root_id_ib;
534 -- l_root_id_ib := l_wbsc_id;
535 elsif (c1_rec2.direction = 'OUTBOUND') then
536 l_parent_id := l_root_id_ob;
537 -- l_root_id_ob := l_wbsc_id;
538 end if;
539
540 end if;
541
542 insert into IEB_WB_SVC_CATS
543 (wbsc_id,
544 created_by,
545 creation_date,
546 last_updated_by,
547 last_update_date,
548 last_update_login,
549 service_category_name,
550 campaign_server_name,
551 campaign_name,
552 active_y_n,
553 media_type,
554 description,
555 priority,
556 depth,
557 wbsvr_wbsvr_id,
558 parent_id,
559 svcpln_svcpln_id,
560 media_type_id,
561 default_flag,
562 object_version_number,
563 security_group_id)
564 values (l_wbsc_id,
565 FND_GLOBAL.USER_ID,
566 sysdate,
567 FND_GLOBAL.USER_ID,
568 sysdate,
569 FND_GLOBAL.LOGIN_ID,
570 c1_rec2.service_category_name,
571 NULL, NULL,
572 c1_rec2.active_y_n,
573 c1_rec2.media_type,
574 c1_rec2.description,
575 0,
576 c1_rec2.depth,
577 c1_rec.wbsvr_id,
578 l_parent_id,
579 c1_rec2.svcpln_svcpln_id,
580 c1_rec2.media_type_id,
581 'Y',
582 0,
583 0);
584
585
586 -- commit;
587
588
589 end if;
590
591 exit when c2%notfound;
592 --commit;
593 end loop;
594
595
596 exit when c1%notfound;
597 commit;
598 end loop;
599
600 EXCEPTION
601 WHEN fnd_api.g_exc_error THEN
602 x_return_status := fnd_api.g_ret_sts_error;
603 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
604
605 WHEN fnd_api.g_exc_unexpected_error THEN
606 x_return_status := fnd_api.g_ret_sts_unexp_error;
607 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
608
609 WHEN OTHERS THEN
610 x_return_status := fnd_api.g_ret_sts_unexp_error;
611 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
612
613
614
615 END SYNC_CAT_ENTRIES;
616
617 END IEB_SYNC_SERVER;