The following lines contain the word 'select', 'insert', 'update' or 'delete':
fnd_msg_pub.delete_msg();
select enum_type_uuid into l_enum_uuid
from ieu_uwq_sel_enumerators
where sel_enum_id = p_enum_id;
SELECT count(*) INTO l_duplicate
FROM ieu_wp_action_maps
where action_param_set_id = p_param_set_id
/*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
--and application_id = p_mapping_application
/*********************************************************************************/
AND responsibility_id = -1
AND action_map_code = l_enum_uuid;
select max(m.action_map_sequence) into l_temp_map_sequence
from ieu_wp_action_maps m,
ieu_uwq_maction_defs_b db,
ieu_wp_act_param_sets_b sb
-- where m.application_id = p_mapping_application
where m.action_map_type_code = 'NODE'
and m.action_map_code = l_enum_uuid
-- and m.application_id = db.application_id
and db.maction_def_type_flag = p_maction_def_type_flag
and db.maction_def_id = sb.wp_action_def_id
and sb.action_param_set_id = m.action_param_set_id
and m.responsibility_id = -1;
update IEU_UWQ_SEL_ENUMERATORS set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
WORK_PANEL_REGISTERED_FLAG = 'Y'
where SEL_ENUM_ID = p_enum_id;
select count(*) into l_count
from IEU_WP_NODE_SECTION_MAPS
where ENUM_TYPE_UUID = l_enum_uuid
and APPLICATION_ID = p_mapping_application
AND SECTION_ID = l_section_id;
update IEU_WP_NODE_SECTION_MAPS set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
RESPONSIBILITY_ID = null,
SECTION_MAP_SEQUENCE = l_section_map_sequence
where ENUM_TYPE_UUID = l_enum_uuid
and APPLICATION_ID = p_mapping_application
and SECTION_ID = l_section_id;
select IEU_WP_NODE_SECTION_MAPS_S1.nextval into l_wp_node_section_map_id from sys.dual;
insert INTO IEU_WP_NODE_SECTION_MAPS
(WP_NODE_SECTION_MAP_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
APPLICATION_ID,
RESPONSIBILITY_ID,
ENUM_TYPE_UUID,
SECTION_ID,
SECTION_MAP_SEQUENCE,
NOT_VALID_FLAG
) values
(l_wp_node_section_map_id,
0,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
NULL,
p_mapping_application,
null,
l_enum_uuid,
l_section_id,
l_section_map_sequence,
null);
fnd_msg_pub.delete_msg();
select count(b.maction_def_id) into l_act_usr_lbl_count
from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl,
ieu_wp_act_param_sets_b s
where
b.maction_def_id = tl.maction_def_id
and b.maction_def_type_flag = (select maction_def_type_flag
from ieu_uwq_maction_defs_b
where maction_def_id = (select wp_action_def_id
from ieu_wp_act_param_sets_b
where action_param_set_id = p_param_set_id))
and s.wp_action_def_id = b.maction_def_id
and tl.language = l_language
and lower(tl.action_user_label) = lower(temp_act_user_label);
select count(b.maction_def_id) into l_set_id_count
from ieu_uwq_maction_defs_b b,
ieu_wp_act_param_sets_b s,
ieu_wp_act_param_sets_tl stl
where
b.maction_def_type_flag = (select maction_def_type_flag
from ieu_uwq_maction_defs_b
where maction_def_id = (select wp_action_def_id
from ieu_wp_act_param_sets_b
where action_param_set_id = p_param_set_id))
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and lower(stl.action_param_set_label) = lower(temp_act_user_label);
select IEU_wp_action_maps_S1.NEXTVAL into l_action_map_id from sys.dual;
insert INTO IEU_wp_action_mapS
(WP_ACTION_MAP_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_PARAM_SET_ID,
APPLICATION_ID,
RESPONSIBILITY_ID,
ACTION_MAP_TYPE_CODE,
ACTION_MAP_CODE,
ACTION_MAP_SEQUENCE,
PANEL_SEC_CAT_CODE,
NOT_VALID_FLAG,
DEV_DATA_FLAG
)
values (
l_action_map_id,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
rec_obj.action_param_set_id,
rec_obj.application_id,
rec_obj.responsibility_id,
rec_obj.action_map_type_code,
rec_obj.action_map_code,
rec_obj.action_map_sequence,
rec_obj.panel_sec_cat_code,
rec_obj.not_valid_flag,
rec_obj.dev_data_flag
);
fnd_msg_pub.delete_msg();
select IEU_UWQ_MACTION_DEFS_B_S1.NEXTVAL into l_maction_def_id from sys.dual;
insert INTO IEU_UWQ_MACTION_DEFS_B
(MACTION_DEF_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_PROC,
APPLICATION_ID,
MACTION_DEF_TYPE_FLAG,
MACTION_DEF_KEY,
OBJECT_VERSION_NUMBER,
MULTI_SELECT_FLAG
)
values(
l_maction_def_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(rec_obj.action_proc)),
rec_obj.application_id,
null,
LTRIM(RTRIM(rec_obj.maction_def_key)),
0,
rec_obj.multi_select_flag
);
insert INTO IEU_UWQ_MACTION_DEFS_B
(MACTION_DEF_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_PROC,
APPLICATION_ID,
MACTION_DEF_TYPE_FLAG,
MACTION_DEF_KEY,
OBJECT_VERSION_NUMBER,
MULTI_SELECT_FLAG
)
values(
l_maction_def_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(rec_obj.action_proc)),
rec_obj.application_id,
p_maction_def_type_flag,
LTRIM(RTRIM(rec_obj.maction_def_key)),
0,
rec_obj.multi_select_flag
);
insert INTO IEU_UWQ_MACTION_DEFS_TL
(MACTION_DEF_ID,
LANGUAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_USER_LABEL,
SOURCE_LANG,
ACTION_DESCRIPTION,
OBJECT_VERSION_NUMBER
) values (
l_maction_def_id,
l_language,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(rec_obj.action_user_label)),
l_source_lang,
LTRIM(RTRIM(rec_obj.action_description)),
0
);
select IEU_WP_ACT_PARAM_SETS_B_S1.NEXTVAL into l_action_param_set_id from sys.dual;
insert INTO IEU_WP_ACT_PARAM_SETS_B
(ACTION_PARAM_SET_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
WP_ACTION_DEF_ID,
OBJECT_VERSION_NUMBER
) values (
l_action_param_set_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_maction_def_id,
0
);
insert INTO IEU_WP_ACT_PARAM_SETS_TL
(ACTION_PARAM_SET_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_PARAM_SET_LABEL,
LANGUAGE,
SOURCE_LANG,
ACTION_PARAM_SET_DESC,
OBJECT_VERSION_NUMBER
) values (
l_action_param_set_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(rec_obj.action_user_label)),
l_language,
l_source_lang,
LTRIM(RTRIM(rec_obj.action_description)),
0
);
select max(m.action_map_sequence) into l_temp_map_sequence
from ieu_wp_action_maps m, ieu_uwq_maction_defs_b db,
ieu_wp_act_param_sets_b sb
--where m.application_id = rec_obj.application_id
where m.action_map_type_code = 'NODE'
--and m.application_id = db.application_id
and db.maction_def_type_flag = p_maction_def_type_flag
and db.maction_def_id = sb.wp_action_def_id
and sb.action_param_set_id = m.action_param_set_id
and m.responsibility_id = -1;
update IEU_UWQ_SEL_ENUMERATORS set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
WORK_PANEL_REGISTERED_FLAG = 'Y'
where SEL_ENUM_ID = rec_obj.enum_id;
SELECT
PARAM_ID, PROPERTY_ID,property_value, value_override_flag,not_valid_flag
FROM ieu_wp_param_props_b
WHERE action_param_set_id in
(select a.action_param_set_id
from ieu_wp_act_param_sets_b a, ieu_wp_act_param_sets_tl b, ieu_uwq_maction_defs_b c
where a.action_param_set_id = b.action_param_set_id(+)
and b.action_param_set_id = r_param_set_id
and c.maction_def_id = r_maction_def_id
and b.language = r_language
and c.maction_def_id = a.wp_action_def_id
);
fnd_msg_pub.delete_msg();
select IEU_wp_act_param_sets_b_S1.NEXTVAL into l_new_param_set_id from sys.dual;
insert into IEU_WP_ACT_PARAM_SETS_B
( ACTION_PARAM_SET_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
WP_ACTION_DEF_ID,
OBJECT_VERSION_NUMBER)
values( l_new_param_set_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
r_maction_def_id,
1);
INSERT INTO ieu_WP_ACT_PARAM_SETS_tl
( ACTION_PARAM_SET_ID,
language,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
ACTION_PARAM_SET_LABEL,
ACTION_PARAM_SET_DESC,
source_lang,
OBJECT_VERSION_NUMBER)
values( l_new_param_set_id,
r_language,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(r_label)),
LTRIM(RTRIM(r_desc)),
l_source_lang,
1);
select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
insert INTO IEU_WP_PARAM_PROPS_B
(PARAM_PROPERTY_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_PARAM_SET_ID,
PARAM_ID,
PROPERTY_ID,
PROPERTY_VALUE,
VALUE_OVERRIDE_FLAG,
NOT_VALID_FLAG,
OBJECT_VERSION_NUMBER)
VALUES (l_param_property_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_new_param_set_id,
c_rec.param_id,
c_rec.property_id,
c_rec.property_value,
c_rec.value_override_flag,
c_rec.not_valid_flag,
1
);
select VALUE_TRANSLATABLE_FLAG into l_trans_flag
from ieu_wp_properties_b
where property_id = c_rec.property_id;
insert INTO IEU_WP_PARAM_PROPS_TL
(PARAM_PROPERTY_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROPERTY_VALUE,
LANGUAGE,
SOURCE_LANG,
OBJECT_VERSION_NUMBER
) VALUES (
l_param_property_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
c_rec.property_value,
l_language,
l_source_lang,
1
);
PROCEDURE Delete_Action_From_Node (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_param_set_id IN NUMBER,
x_node_id IN NUMBER,
x_maction_id IN NUMBER,
x_maction_def_flag IN VARCHAR2
) is
l_language VARCHAR2(4);
SELECT db.maction_def_type_flag into l_def_type_flag
FROM ieu_uwq_maction_defs_b db,
ieu_wp_act_param_sets_b sb
WHERE db.maction_def_id = sb.wp_action_def_id
AND sb.action_param_set_id = x_param_set_id;
SELECT count(unique(action_map_code))
INTO l_num_map_entries
FROM ieu_wp_action_maps
WHERE action_map_type_code = 'NODE' AND action_param_set_id = x_param_set_id;
SELECT count(unique(action_map_code))
INTO l_num_map_entries
FROM ieu_wp_action_maps
WHERE action_map_type_code = 'NODE_DS' AND action_param_set_id = x_param_set_id;
DELETE FROM ieu_wp_action_maps
WHERE action_param_set_id = x_param_set_id AND
action_map_type_code = 'NODE' AND
action_map_code IN
(SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
WHERE sel_enum_id = x_node_id);
select count(distinct action_param_set_id) into l_count_map
from ieu_wp_action_maps
where action_map_type_code = 'NODE'
AND action_map_code IN
(SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
WHERE sel_enum_id = x_node_id)
and action_param_set_id in
(SELECT action_param_set_id
FROM ieu_wp_act_param_sets_b
WHERE wp_action_def_id in
(select maction_def_id
from ieu_uwq_maction_defs_b
where maction_def_type_flag = l_def_type_flag
));
delete from IEU_WP_NODE_SECTION_MAPS
where ENUM_TYPE_UUID IN
(SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
WHERE sel_enum_id = x_node_id)
and SECTION_ID = l_section_id;
DELETE FROM ieu_wp_action_maps
WHERE action_param_set_id = x_param_set_id AND
action_map_type_code = 'NODE_DS' AND
action_map_code IN
(SELECT ds.NODE_DS_ID FROM ieu_uwq_sel_enumerators e, ieu_uwq_node_ds ds
WHERE e.sel_enum_id = x_node_id
and e.ENUM_TYPE_UUID = ds.ENUM_TYPE_UUID);
SELECT wp_action_def_id, COUNT(*)
INTO l_maction_def_id, l_num_set_entries
FROM ieu_wp_act_param_sets_b
WHERE wp_action_def_id IN
(SELECT wp_action_def_id FROM ieu_wp_act_param_sets_b
WHERE action_param_set_id = x_param_set_id)
GROUP BY wp_action_def_id;
DELETE FROM ieu_wp_param_props_tl
WHERE param_property_id IN
(SELECT param_property_id FROM ieu_wp_param_props_b
WHERE
action_param_set_id = x_param_set_id);
DELETE FROM ieu_wp_param_props_b
WHERE action_param_set_id = x_param_set_id;
DELETE FROM ieu_wp_act_param_sets_tl
WHERE action_param_set_id = x_param_set_id;
DELETE FROM ieu_wp_act_param_sets_b
WHERE action_param_set_id = x_param_set_id;
DELETE FROM ieu_wp_param_defs_tl
WHERE param_id IN
(SELECT param_id FROM ieu_wp_action_params
WHERE wp_action_def_id = l_maction_def_id);
DELETE FROM ieu_wp_param_defs_b
WHERE param_id IN
(SELECT param_id FROM ieu_wp_action_params
WHERE wp_action_def_id = l_maction_def_id);
DELETE FROM ieu_wp_action_params
WHERE wp_action_def_id = l_maction_def_id;
DELETE FROM ieu_uwq_maction_defs_tl
WHERE maction_def_id = l_maction_def_id;
DELETE FROM ieu_uwq_maction_defs_b
WHERE maction_def_id = l_maction_def_id;
DELETE FROM ieu_uwq_maction_defs_tl
WHERE maction_def_id = x_maction_id;
DELETE FROM ieu_uwq_maction_defs_b
WHERE maction_def_id = x_maction_id;
select count(m.WP_ACTION_MAP_ID) into l_count_map
from IEU_WP_ACTION_MAPS m
where m.ACTION_MAP_CODE = (select ENUM_TYPE_UUID from
ieu_uwq_sel_enumerators where SEL_ENUM_ID = x_node_id)
and m.ACTION_MAP_TYPE_CODE = 'NODE';
update IEU_UWQ_SEL_ENUMERATORS set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
WORK_PANEL_REGISTERED_FLAG = null
where SEL_ENUM_ID = x_node_id;
END Delete_Action_From_Node;