[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_selectStmt VARCHAR2(5000);
v_selectStmt := 'select WBSVR_ID, WB_SERVER_NAME, LOG_FILE_NAME,
LOG_DBC_FILE_NAME, CCI_DBC_FILE_NAME, STAT_DUMP_SUNDAY_Y_N,
STAT_DUMP_MONDAY_Y_N, STAT_DUMP_TUESDAY_Y_N,
STAT_DUMP_WEDNESDAY_Y_N, DESCRIPTION
from IEB_WB_SERVERS
order by ' || p_order_by || ' ' || p_asc;
DBMS_SQL.PARSE(v_cursorID, v_selectStmt, DBMS_SQL.V7);
select *
from IEO_SVR_SERVERS
where type_id = 10020
and not exists (select 'x' from IEB_WB_SERVERS SRV, IEB_WB_SVC_CATS SVC
where SRV.WB_SERVER_NAME = SERVER_NAME AND
SVC.WBSVR_WBSVR_ID = SRV.WBSVR_ID );
DELETE FROM ieb_wb_servers WHERE wb_server_name NOT IN
(SELECT server_name FROM ieo_svr_servers WHERE type_id = 10020);
DELETE FROM ieb_wb_svc_cats WHERE wbsvr_wbsvr_id NOT IN
(SELECT wbsvr_id FROM ieb_wb_servers);
DELETE FROM ieb_wb_svc_cat_rules WHERE wbsc_wbsc_id NOT IN
(SELECT wbsc_id FROM ieb_wb_svc_cats);
select wbsvr_id, ieo_server_id into l_wbsvr_id, l_ieo_svr_id
from IEB_WB_SERVERS
where WB_server_name = c1_rec.server_name;
select IEB_WB_SERVER_S1.NEXTVAL into l_wbsvr_id from sys.dual;
insert into IEB_WB_SERVERS
(wbsvr_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
wb_server_name,
log_file_name,
log_dbc_file_name,
cci_dbc_file_name,
stat_dump_sunday_y_n,
stat_dump_monday_y_n,
stat_dump_tuesday_y_n,
stat_dump_wednesday_y_n,
stat_dump_thursday_y_n,
stat_dump_friday_y_n,
stat_dump_saturday_y_n,
stat_dump_beg_time_hhmm,
stat_dump_end_time_hhmm,
daily_cleanup_y_n,
cleanup_time_hhmm,
auto_shut_down_y_n,
auto_shut_down_time_hhmm,
virtual_q_cleanup_size,
work_queue_cache_size,
trace_file_name,
wb_server_type,
description,
communication_method,
com_object_name,
com_dns_name,
com_ip_address,
com_port_number,
com_param1,
com_param2,
com_param3,
com_param4,
object_version_number,
security_group_id,
check_new_sc_entry,
ieo_server_id)
VALUES (l_wbsvr_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
c1_rec.server_name,
NULL, NULL, NULL,
'N','N','N','N','N','N','N',
NULL, NULL,
'N',
NULL,
'N',
NULL,
0,
0,
NULL,
'N',
c1_rec.description,
'RMI',
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
0,
0,
NULL,
c1_rec.server_id);
Update IEB_WB_SERVERS
Set IEO_SERVER_ID = c1_rec.server_id
Where WB_server_name = c1_rec.server_name;
IEB_SYNC_SERVER.INSERT_SVC_CAT_ENTRIES(p_language, l_wbsvr_id, x_return_status);
update ieb_wb_servers a
set a.IEO_SERVER_ID = (select b.server_id from ieo_svr_servers b where a.WB_SERVER_NAME = b.SERVER_NAME);
PROCEDURE INSERT_SVC_CAT_ENTRIES (
p_language IN varchar2,
p_wbsvr_id IN number,
x_return_status OUT NOCOPY varchar2 )
IS
cursor c1 is
select a.direction, b.created_by, b.creation_date, b.last_updated_by,
b.last_update_date, b.last_update_login,
tl.service_category_name, b.media_type_id,
b.active_y_n, b.media_type, tl.description,
b.depth, b.svcpln_svcpln_id, b.object_version_number,
b.security_group_id
from ieb_service_plans a, ieb_svc_cat_temps_tl tl,
ieb_svc_cat_temps_b b
where b.wbsc_id = tl.wbsc_id
and svcpln_svcpln_id = svcpln_id
and language = p_language
order by depth, direction;
select IEB_SVC_CATS_S1.NEXTVAL into l_wbsc_id from sys.dual;
insert into IEB_WB_SVC_CATS
(wbsc_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
service_category_name,
campaign_server_name,
campaign_name,
active_y_n,
media_type,
description,
priority,
depth,
wbsvr_wbsvr_id,
parent_id,
svcpln_svcpln_id,
media_type_id,
default_flag,
object_version_number,
security_group_id)
values (l_wbsc_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
c1_rec.service_category_name,
NULL, NULL,
c1_rec.active_y_n,
c1_rec.media_type,
c1_rec.description,
0,
c1_rec.depth,
p_wbsvr_id,
l_parent_id,
c1_rec.svcpln_svcpln_id,
c1_rec.media_type_id,
'Y',
0,
0);
END INSERT_SVC_CAT_ENTRIES;
select * from ieb_wb_servers WHERE wb_server_name IN
(SELECT server_name FROM ieo_svr_servers WHERE type_id = 10020);
select a.direction, b.created_by, b.creation_date, b.last_updated_by,
b.last_update_date, b.last_update_login,
tl.service_category_name, b.media_type_id,
b.active_y_n, b.media_type, tl.description,
b.depth, b.svcpln_svcpln_id, b.object_version_number,
b.security_group_id
from ieb_service_plans a, ieb_svc_cat_temps_tl tl,
ieb_svc_cat_temps_b b
where b.wbsc_id = tl.wbsc_id
and svcpln_svcpln_id = svcpln_id
and language = 'US'
order by depth, direction;
select WBSC_ID, depth into l_wbsc_id ,l_depth from ieb_wb_svc_cats
where service_category_name = c1_rec2.service_category_name
and wbsvr_wbsvr_id =c1_rec.wbsvr_id;
update IEB_WB_SVC_CATS set
media_type_id = c1_rec2.media_type_id,
default_flag = 'Y'
where
wbsvr_wbsvr_id =c1_rec.wbsvr_id
and service_category_name = c1_rec2.service_category_name
and media_type = c1_rec2.media_type;
select IEB_SVC_CATS_S1.NEXTVAL into l_wbsc_id from sys.dual;
insert into IEB_WB_SVC_CATS
(wbsc_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
service_category_name,
campaign_server_name,
campaign_name,
active_y_n,
media_type,
description,
priority,
depth,
wbsvr_wbsvr_id,
parent_id,
svcpln_svcpln_id,
media_type_id,
default_flag,
object_version_number,
security_group_id)
values (l_wbsc_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
c1_rec2.service_category_name,
NULL, NULL,
c1_rec2.active_y_n,
c1_rec2.media_type,
c1_rec2.description,
0,
c1_rec2.depth,
c1_rec.wbsvr_id,
l_parent_id,
c1_rec2.svcpln_svcpln_id,
c1_rec2.media_type_id,
'Y',
0,
0);