[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_selectStmt VARCHAR2(32767);
v_selectStmt := ' select b.MEDIA_TYPE_ID, b.TEL_REQD_FLAG, tl.MEDIA_TYPE_NAME, tl.MEDIA_TYPE_DESCRIPTION, cl.CLI_PLUGIN_ID,' ||
' cl.CLI_PLUGIN_CLASS, svm.SVR_TYPE_ID, svp.SVR_MPS_PLUGIN_ID, svp.SVR_PLUGIN_CLASS, b.MEDIA_TYPE_UUID, ' ||
' b.SVR_LOGIN_RULE_ID, b.APPLICATION_ID,b.CLASSIFICATION_QUERY_PROC from IEU_UWQ_MEDIA_TYPES_B b, IEU_UWQ_MEDIA_TYPES_TL tl, IEU_UWQ_CLI_MED_PLUGINS cl, ' ||
' IEU_UWQ_SVR_MPS_MMAPS svm, IEU_UWQ_SVR_MPS_PLUGINS svp ' ||
' where b.MEDIA_TYPE_ID = tl.MEDIA_TYPE_ID and b.MEDIA_TYPE_ID = cl.MEDIA_TYPE_ID ' ||
' and tl.LANGUAGE = '||''''||p_language||''''||' and b.MEDIA_TYPE_ID = svm.MEDIA_TYPE_ID ' ||
' and svm.SVR_TYPE_ID = svp.SVR_TYPE_ID order by ' || p_order_by || ' ' || p_asc;
DBMS_SQL.PARSE(v_cursorID, v_selectStmt, DBMS_SQL.V7);
fnd_msg_pub.delete_msg();
select count(*) into media_name_count from IEU_UWQ_MEDIA_TYPES_TL where lower(MEDIA_TYPE_NAME) like lower(temp_media_name);
select count(*) into media_uuid_count from IEU_UWQ_MEDIA_TYPES_B where lower(MEDIA_TYPE_UUID) like lower(temp_uuid);
select count(*) into application_name_count from FND_APPLICATION_TL T, FND_APPLICATION B
where B.APPLICATION_ID = T.APPLICATION_ID and T.LANGUAGE = l_language and lower(T.APPLICATION_NAME) like lower(rec_obj.application_name);
select count(*) into cli_class_name_count from IEU_UWQ_CLI_MED_PLUGINS
where lower(CLI_PLUGIN_CLASS) like lower(temp_cli_class);
select count(*) into class_name_count from IEU_UWQ_SVR_MPS_PLUGINS
where lower(SVR_PLUGIN_CLASS) like lower(temp_svr_class);
select count(*) into class_name_count from IEU_CLI_PROV_PLUGINS
where lower(PLUGIN_CLASS_NAME) like lower(temp_svr_class);
select count(*) into l_temp_class_count from all_objects where owner = 'APPS' and object_type in('PACKAGE', 'PACKAGE BODY') and status='VALID'and object_name = l_temp_pkg_name;
select IEU_UWQ_MEDIA_TYPES_B_S2.NEXTVAL into l_media_type_id from sys.dual;
insert INTO IEU_UWQ_MEDIA_TYPES_B
(MEDIA_TYPE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
SIMPLE_BLENDING_ORDER,
MEDIA_TYPE_UUID,
TEL_REQD_FLAG,
APPLICATION_ID,
SVR_LOGIN_RULE_ID,
CLASSIFICATION_QUERY_PROC,
SH_CATEGORY_TYPE ,
IMAGE_FILE_NAME,
BLENDED_FLAG,
BLENDED_DIR
)
values (
l_media_type_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
null,
1,
null,
LTRIM(RTRIM(rec_obj.media_type_uuid)),
rec_obj.tel_reqd_flag,
rec_obj.application_id,
rec_obj.svr_login_rule_id,
rec_obj.classfn_query_proc,
null,
null,
null,
null
);
insert into IEU_UWQ_MEDIA_TYPES_TL
( MEDIA_TYPE_ID,
LANGUAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MEDIA_TYPE_NAME,
SOURCE_LANG,
MEDIA_TYPE_DESCRIPTION,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
)
values ( l_media_type_id,
l_language,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(rec_obj.media_type_name)),
l_language,
rec_obj.media_type_description,
null,
1
);
select IEU_UWQ_CLI_MED_PLUGINS_S2.NEXTVAL into l_cli_plugin_id from sys.dual;
insert into IEU_UWQ_CLI_MED_PLUGINS
( CLI_PLUGIN_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MEDIA_TYPE_ID,
CLI_PLUGIN_CLASS,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER,
APPLICATION_ID
)
values ( l_cli_plugin_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_media_type_id,
LTRIM(RTRIM(rec_obj.cli_plugin_class)),
null,
1,
rec_obj.application_id
);
select IEU_UWQ_SVR_MPS_MMAPS_S2.NEXTVAL into l_svr_mps_mmap_id from sys.dual;
select IEU_UWQ_SVR_MPS_PLUGINS_S2.NEXTVAL into l_svr_mps_plugin_id from sys.dual;
insert into IEU_UWQ_SVR_MPS_MMAPS
( SVR_MPS_MMAP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MEDIA_TYPE_ID,
SVR_TYPE_ID,
MEDIA_TYPE_MAP,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
)
values ( l_svr_mps_mmap_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_media_type_id,
rec_obj.svr_type_id,
null,
null,
1
);
insert into IEU_UWQ_SVR_MPS_PLUGINS
( SVR_MPS_PLUGIN_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SVR_TYPE_ID,
SVR_PLUGIN_CLASS,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
APPLICATION_ID
)
values ( l_svr_mps_plugin_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
rec_obj.svr_type_id,
LTRIM(RTRIM(rec_obj.svr_plugin_class)),
null,
1,
rec_obj.application_id
);
select svr_type_id into temp_svr_type_id from ieu_uwq_svr_mps_plugins where svr_mps_plugin_id = rec_obj.svr_mps_plugin_id;
select IEU_UWQ_SVR_MPS_MMAPS_S2.NEXTVAL into l_svr_mps_mmap_id from sys.dual;
insert into IEU_UWQ_SVR_MPS_MMAPS
( SVR_MPS_MMAP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MEDIA_TYPE_ID,
SVR_TYPE_ID,
MEDIA_TYPE_MAP,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
)
values ( l_svr_mps_mmap_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_media_type_id,
temp_svr_type_id,
null,
null,
1
);
select IEU_CLI_PROV_PLUGINS_S1.NEXTVAL into l_plugin_id from sys.dual;
insert into IEU_CLI_PROV_PLUGINS
(PLUGIN_ID,
PLUGIN_CLASS_NAME,
IS_ACTIVE_FLAG,
APPLICATION_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (l_plugin_id,
LTRIM(RTRIM(rec_obj.svr_plugin_class)),
'Y',
rec_obj.application_id,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
select IEU_CLI_PROV_PLUGIN_MED_MAP_S1.NEXTVAL into l_plugin_med_map_id from sys.dual;
insert into IEU_CLI_PROV_PLUGIN_MED_MAPS
(PLUGIN_MED_MAP_ID,
PLUGIN_ID,
MEDIA_TYPE_ID,
CONDITIONAL_FUNC,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values (l_plugin_med_map_id,
l_plugin_id,
l_media_type_id,
null,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
);
PROCEDURE UPDATE_MEDIA_TYPE (x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
rec_obj IN SYSTEM.IEU_MEDIA_TYPE_OBJ
) AS
l_cli_plugin_id NUMBER(15);
update IEU_UWQ_MEDIA_TYPES_B set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID,
TEL_REQD_FLAG = rec_obj.tel_reqd_flag,
APPLICATION_ID = rec_obj.application_id,
SVR_LOGIN_RULE_ID = rec_obj.svr_login_rule_id,
CLASSIFICATION_QUERY_PROC = rec_obj.classfn_query_proc
where MEDIA_TYPE_ID = rec_obj.media_type_id;
update IEU_UWQ_MEDIA_TYPES_TL set
LANGUAGE = l_language,
LAST_UPDATED_BY =FND_GLOBAL.USER_ID ,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
MEDIA_TYPE_NAME = rec_obj.media_type_name,
SOURCE_LANG = l_language,
MEDIA_TYPE_DESCRIPTION = rec_obj.media_type_description
where MEDIA_TYPE_ID = rec_obj.media_type_id
and l_language IN (language, source_lang);
select cli_plugin_id into temp_cli_plugin_id from IEU_UWQ_CLI_MED_PLUGINS where media_type_id = rec_obj.media_type_id;
update IEU_UWQ_CLI_MED_PLUGINS set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID,
CLI_PLUGIN_CLASS = rec_obj.cli_plugin_class,
MEDIA_TYPE_ID = rec_obj.media_type_id,
APPLICATION_ID = rec_obj.application_id
where CLI_PLUGIN_ID = temp_cli_plugin_id;
select IEU_UWQ_CLI_MED_PLUGINS_S2.NEXTVAL into l_cli_plugin_id from sys.dual;
insert into IEU_UWQ_CLI_MED_PLUGINS
( CLI_PLUGIN_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MEDIA_TYPE_ID,
CLI_PLUGIN_CLASS,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
APPLICATION_ID
)
values ( l_cli_plugin_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
rec_obj.media_type_id,
rec_obj.cli_plugin_class,
null,
1,
rec_obj.application_id
);
select cli_plugin_id into temp_cli_plugin_id from IEU_UWQ_CLI_MED_PLUGINS where media_type_id = rec_obj.media_type_id;
delete from IEU_UWQ_CLI_MED_PLUGINS where cli_plugin_id = temp_cli_plugin_id;
select IEU_UWQ_SVR_MPS_PLUGINS_S2.NEXTVAL into l_svr_mps_plugin_id from sys.dual;
update IEU_UWQ_SVR_MPS_MMAPS set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
SVR_TYPE_ID = rec_obj.svr_type_id
where MEDIA_TYPE_ID = rec_obj.media_type_id;
insert into IEU_UWQ_SVR_MPS_PLUGINS
( SVR_MPS_PLUGIN_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SVR_TYPE_ID,
SVR_PLUGIN_CLASS,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
APPLICATION_ID
)
values ( l_svr_mps_plugin_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
rec_obj.svr_type_id,
rec_obj.svr_plugin_class,
null,
1,
rec_obj.application_id
);
select svr_type_id into temp_svr_type_id from ieu_uwq_svr_mps_plugins where svr_mps_plugin_id = rec_obj.svr_mps_plugin_id;
update IEU_UWQ_SVR_MPS_MMAPS set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
SVR_TYPE_ID = temp_svr_type_id
where MEDIA_TYPE_ID = rec_obj.media_type_id;
select IEU_UWQ_SVR_MPS_MMAPS_S2.NEXTVAL into l_svr_mps_mmap_id from sys.dual;
insert into IEU_UWQ_SVR_MPS_MMAPS
( SVR_MPS_MMAP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MEDIA_TYPE_ID,
SVR_TYPE_ID,
MEDIA_TYPE_MAP,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
)
values ( l_svr_mps_mmap_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
rec_obj.media_type_id,
temp_svr_type_id,
null,
null,
1
);
delete from IEU_CLI_PROV_PLUGIN_MED_MAPS where media_type_id = rec_obj.media_type_id;
update IEU_CLI_PROV_PLUGIN_MED_MAPS set
PLUGIN_ID = rec_obj.svr_mps_plugin_id,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where MEDIA_TYPE_ID = rec_obj.media_type_id;
select IEU_CLI_PROV_PLUGIN_MED_MAP_S1.NEXTVAL into l_plugin_med_map_id from sys.dual;
insert into IEU_CLI_PROV_PLUGIN_MED_MAPS
(PLUGIN_MED_MAP_ID,
PLUGIN_ID,
MEDIA_TYPE_ID,
CONDITIONAL_FUNC,
OBJECT_VERSION_NUMBER ,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values (l_plugin_med_map_id,
rec_obj.svr_mps_plugin_id,
rec_obj.media_type_id,
null,
null,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
);
select IEU_CLI_PROV_PLUGINS_S1.NEXTVAL into l_plugin_id from sys.dual;
insert into IEU_CLI_PROV_PLUGINS
(PLUGIN_ID,
PLUGIN_CLASS_NAME,
IS_ACTIVE_FLAG,
APPLICATION_ID,
OBJECT_VERSION_NUMBER ,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (l_plugin_id,
LTRIM(RTRIM(rec_obj.svr_plugin_class)),
'Y',
rec_obj.application_id,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
select IEU_CLI_PROV_PLUGIN_MED_MAP_S1.NEXTVAL into l_plugin_med_map_id from sys.dual;
insert into IEU_CLI_PROV_PLUGIN_MED_MAPS
(PLUGIN_MED_MAP_ID,
PLUGIN_ID,
MEDIA_TYPE_ID,
CONDITIONAL_FUNC,
OBJECT_VERSION_NUMBER ,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values (l_plugin_med_map_id,
l_plugin_id,
rec_obj.media_type_id,
null,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
);
delete from IEU_UWQ_SVR_MPS_MMAPS where MEDIA_TYPE_ID = rec_obj.media_type_id;
END UPDATE_MEDIA_TYPE;
PROCEDURE DELETE_MEDIA_TYPE (
x_media_type_id IN NUMBER
) is
media_count NUMBER(15);
delete from IEU_UWQ_MEDIA_TYPES_TL
where MEDIA_TYPE_ID = x_media_type_id;
delete from IEU_UWQ_MEDIA_TYPES_B
where MEDIA_TYPE_ID = x_media_type_id;
delete from IEU_UWQ_CLI_MED_PLUGINS
where MEDIA_TYPE_ID = x_media_type_id;
select svr_type_id into temp_svr_type_id from IEU_UWQ_SVR_MPS_MMAPS where MEDIA_TYPE_ID = x_media_type_id;
select count(MEDIA_TYPE_ID) into media_count from IEU_UWQ_SVR_MPS_MMAPS where svr_type_id = temp_svr_type_id;
delete from IEU_UWQ_SVR_MPS_MMAPS
where MEDIA_TYPE_ID = x_media_type_id;
delete from IEU_UWQ_SVR_MPS_PLUGINS
where svr_type_id = temp_svr_type_id;
END DELETE_MEDIA_TYPE;