The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
where e. sel_enum_id = rec_obj.enum_id
-- and e.application_id = m.application_id
and e.enum_type_uuid = m.action_map_code
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE'
and b.maction_def_type_flag = p_maction_def_type_flag
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
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_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
where e. sel_enum_id = rec_obj.enum_id
--and e.application_id = m.application_id
and e.enum_type_uuid = m.action_map_code
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE'
and b.maction_def_type_flag = p_maction_def_type_flag
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
and lower(stl.action_param_set_label) = lower(temp_act_user_label);
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_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e,
ieu_wp_act_param_sets_tl stl, ieu_uwq_node_ds ds
where e. sel_enum_id = rec_obj.enum_id
--and e.application_id = m.application_id
and e.enum_type_uuid = ds.ENUM_TYPE_UUID
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE_DS'
and b.maction_def_type_flag = 'F'
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
and lower(tl.action_user_label) = lower(temp_act_user_label)
and to_char(ds.NODE_DS_ID) = m.ACTION_MAP_CODE;
select count(b.maction_def_id) into l_set_id_count
from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e,
ieu_wp_act_param_sets_tl stl, ieu_uwq_node_ds ds
where e. sel_enum_id = rec_obj.enum_id
--and e.application_id = m.application_id
and e.enum_type_uuid = ds.ENUM_TYPE_UUID
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE_DS'
and b.maction_def_type_flag = 'F'
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
and lower(stl.action_param_set_label) = lower(temp_act_user_label)
and to_char(ds.NODE_DS_ID) = m.ACTION_MAP_CODE;
select action_param_set_label into l_temp_act_label
from ieu_wp_act_param_sets_tl stl, ieu_wp_act_param_sets_b s
where s.action_param_set_id = stl.action_param_set_id
and s.wp_action_def_id = rec_obj.maction_def_id
and stl.language = l_language
and stl.action_param_set_id = p_param_set_id;
select count(mb.maction_def_id) into l_set_id_count
from ieu_uwq_maction_defs_tl mtl,
ieu_uwq_maction_defs_b mb
where mb.maction_def_id = mtl.maction_def_id and
mb.maction_def_id <> nvl(rec_obj.maction_def_id, -1) and -- ignore current action's record
nvl(mb.maction_def_type_flag, 'M') = p_maction_def_type_flag and
mtl.action_user_label = rec_obj.action_user_label and --
mtl.language = l_language;
select count(*) into l_act_def_key_count from IEU_UWQ_MACTION_DEFS_B
where lower(MACTION_DEF_KEY) = lower(temp_act_key)
and maction_def_type_flag = p_maction_def_type_flag;
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_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
where e. sel_enum_id = p_enum_id
-- and e.application_id = m.application_id
and e.enum_type_uuid = m.action_map_code
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE'
and b.maction_def_type_flag = p_maction_def_type_flag
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
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_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
where e. sel_enum_id = p_enum_id
--and e.application_id = m.application_id
and e.enum_type_uuid = m.action_map_code
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE'
and b.maction_def_type_flag = p_maction_def_type_flag
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
and lower(stl.action_param_set_label) = lower(temp_act_user_label);
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_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl,
IEU_UWQ_NODE_DS ds
where e. sel_enum_id = p_enum_id
--and e.application_id = m.application_id
and e.enum_type_uuid = ds.ENUM_TYPE_UUID
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE_DS'
and b.maction_def_type_flag = 'F'
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
and m.action_map_code = to_char(ds.NODE_DS_ID)
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_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl, IEU_UWQ_NODE_DS ds
where e. sel_enum_id = p_enum_id
--and e.application_id = m.application_id
and e.enum_type_uuid = ds.ENUM_TYPE_UUID
and b.maction_def_id = tl.maction_def_id
and tl.language = l_language
and s.wp_action_def_id = b.maction_def_id
and s.action_param_set_id = m.action_param_set_id
and m.action_map_type_code = 'NODE_DS'
and b.maction_def_type_flag = 'F'
and s.action_param_set_id = stl.action_param_set_id
and stl.language = l_language
and m.responsibility_id = -1
and m.action_map_code = to_char(ds.NODE_DS_ID)
and lower(stl.action_param_set_label) = lower(temp_act_user_label);
fnd_msg_pub.delete_msg();
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,
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 enum_type_uuid into l_enum_uuid
from ieu_uwq_sel_enumerators
where sel_enum_id = rec_obj.enum_id;
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.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 = rec_obj.enum_id;
select count(*) into l_count
from IEU_WP_NODE_SECTION_MAPS
where ENUM_TYPE_UUID = l_enum_uuid
and APPLICATION_ID = rec_obj.application_id
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 = rec_obj.application_id
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,
APPLICATION_ID,
RESPONSIBILITY_ID,
ENUM_TYPE_UUID,
SECTION_ID,
SECTION_MAP_SEQUENCE
) values
(l_wp_node_section_map_id,
0,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
rec_obj.application_id,
null,
l_enum_uuid,
l_section_id,
l_section_map_sequence);
PROCEDURE Update_MAction (x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
p_param_set_id IN NUMBER,
p_maction_def_type_flag IN VARCHAR2) AS
l_language VARCHAR2(4);
fnd_msg_pub.delete_msg();
update IEU_UWQ_MACTION_DEFS_B set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
ACTION_PROC = LTRIM(RTRIM(rec_obj.action_proc)),
MULTI_SELECT_FLAG = rec_obj.multi_select_flag
where MACTION_DEF_ID = p_param_set_id
and nvl(MACTION_DEF_TYPE_FLAG,'M') = p_maction_def_type_flag;
update IEU_UWQ_MACTION_DEFS_tl set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
action_description = LTRIM(RTRIM(rec_obj.action_description)),
action_user_label = LTRIM(RTRIM(rec_obj.action_user_label))
where MACTION_DEF_ID = p_param_set_id
and l_language IN (language, source_lang);
update IEU_UWQ_MACTION_DEFS_B set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
ACTION_PROC = LTRIM(RTRIM(rec_obj.action_proc)),
MULTI_SELECT_FLAG = rec_obj.multi_select_flag
where MACTION_DEF_ID = rec_obj.maction_def_id
and MACTION_DEF_TYPE_FLAG = p_maction_def_type_flag;
update IEU_WP_ACT_PARAM_SETS_TL set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
ACTION_PARAM_SET_LABEL = LTRIM(RTRIM(rec_obj.action_user_label)),
ACTION_PARAM_SET_DESC = LTRIM(RTRIM(rec_obj.action_description))
where ACTION_PARAM_SET_ID = p_param_set_id
and l_language IN (language, source_lang);
END Update_MAction;
PROCEDURE Delete_MAction (
x_action_def_id IN NUMBER
) is
l_language VARCHAR2(4);
select ACTION_PARAM_SET_ID into l_action_param_set_id
from IEU_WP_ACT_PARAM_SETS_B
where WP_ACTION_DEF_ID = x_action_def_id;
delete from IEU_UWQ_MACTION_DEFS_B
where MACTION_DEF_ID = x_action_def_id;
delete from IEU_UWQ_MACTION_DEFS_TL
where MACTION_DEF_ID = x_action_def_id and language= l_language;
delete from IEU_WP_ACT_PARAM_SETS_B
where WP_ACTION_DEF_ID = x_action_def_id;
delete from IEU_WP_ACT_PARAM_SETS_TL
where ACTION_PARAM_SET_ID = l_action_param_set_id and language= l_language;
END Delete_MAction;
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
) 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 to_char(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;
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;
fnd_msg_pub.delete_msg();
select count(*) into l_param_name_count
from ieu_wp_action_params p,
ieu_wp_param_defs_b b
where p.PARAM_ID = b.PARAM_ID
and p.WP_ACTION_DEF_ID = rec_obj.wp_action_def_id
and lower(b.PARAM_NAME) = lower(temp_param_name);
select count(*) into l_param_usr_lbl_count
from ieu_wp_action_params p,
ieu_wp_param_defs_b b,
ieu_wp_param_defs_tl tl
where p.PARAM_ID = b.PARAM_ID
and p.WP_ACTION_DEF_ID = rec_obj.wp_action_def_id
and b.PARAM_ID = tl.PARAM_ID
and tl.LANGUAGE = l_language
and lower(tl.PARAM_USER_LABEL) = lower(temp_param_user_label);
fnd_msg_pub.delete_msg();
select IEU_WP_PARAM_DEFS_B_S1.NEXTVAL into l_param_id from sys.dual;
insert INTO IEU_WP_PARAM_DEFS_B
(PARAM_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PARAM_NAME,
DATA_TYPE,
OBJECT_VERSION_NUMBER,
APPLICATION_ID
) values
(l_param_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(rec_obj.param_name)),
rec_obj.data_type,
0,
rec_obj.application_id
);
insert INTO IEU_WP_PARAM_DEFS_TL
(PARAM_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PARAM_USER_LABEL,
PARAM_DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
OBJECT_VERSION_NUMBER
) VALUES (
l_param_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
LTRIM(RTRIM(rec_obj.param_user_label)),
LTRIM(RTRIM(rec_obj.param_description)),
l_language,
l_source_lang,
0
);
select IEU_WP_ACTION_PARAMS_S1.NEXTVAL into l_action_param_map_id from sys.dual;
insert INTO IEU_WP_ACTION_PARAMS
(PARAM_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
WP_ACTION_DEF_ID,
ACTION_PARAM_MAP_ID,
NOT_VALID_FLAG,
OBJECT_VERSION_NUMBER
) VALUES (
l_param_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
rec_obj.wp_action_def_id,
l_action_param_map_id,
null,
0
);
PROCEDURE Update_Param_Defs ( x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
rec_obj IN SYSTEM.IEU_WP_ACT_PARAM_OBJ
) AS
l_language VARCHAR2(4);
fnd_msg_pub.delete_msg();
update IEU_WP_PARAM_DEFS_B set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
DATA_TYPE = rec_obj.data_type
where PARAM_ID = rec_obj.param_id;
update IEU_WP_PARAM_DEFS_TL set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
PARAM_USER_LABEL = LTRIM(RTRIM(rec_obj.param_user_label)),
PARAM_DESCRIPTION = LTRIM(RTRIM(rec_obj.param_description))
where PARAM_ID = rec_obj.param_id
and l_language IN (language, source_lang);
END Update_Param_Defs;
fnd_msg_pub.delete_msg();
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,
OBJECT_VERSION_NUMBER
) VALUES (
l_param_property_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
p_action_param_set_id,
p_param_id,
p_property_id,
p_property_value,
'F',
0
);
select VALUE_TRANSLATABLE_FLAG into l_trans_flag
from ieu_wp_properties_b
where property_id = p_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,
p_property_value,
l_language,
l_source_lang,
0
);
PROCEDURE Update_Param_Props ( x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_param_id IN NUMBER,
p_property_id IN NUMBER,
p_property_value IN VARCHAR2,
p_action_param_set_id IN NUMBER)AS
l_language VARCHAR2(4);
fnd_msg_pub.delete_msg();
' update IEU_WP_PARAM_PROPS_B set '||
' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
' LAST_UPDATE_DATE = SYSDATE, '||
' LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, '||
' PROPERTY_VALUE = :1, '||
' PROPERTY_ID = :2 '||
' where PARAM_ID= :3 '||
' and ACTION_PARAM_SET_ID = :4 ' ||
' and PROPERTY_ID in ( 10010, 10021, 10011, 10022, 10003)'
USING p_property_value, p_property_id,p_param_id,p_action_param_set_id;
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, '||
' OBJECT_VERSION_NUMBER '||
' ) VALUES ( ' ||
' :1, '||
' :2, '||
' :3, '||
' :4, '||
' :5, '||
' :6, '||
' :7, '||
' :8, '||
' :9, '||
' :10, '||
' :11, '||
' :12 '||
' ) '
USING l_param_property_id,FND_GLOBAL.USER_ID,SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE,FND_GLOBAL.LOGIN_ID, p_action_param_set_id,p_param_id,p_property_id,
p_property_value, 'F', '0';
' update IEU_WP_PARAM_PROPS_B set '||
' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
' LAST_UPDATE_DATE = SYSDATE, '||
' LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, '||
' PROPERTY_VALUE = :1 '||
' where PARAM_ID= :2 '||
' and ACTION_PARAM_SET_ID = :3 '||
' and PROPERTY_ID = :4 '
USING p_property_value,p_param_id, p_action_param_set_id, p_property_id ;
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, '||
' OBJECT_VERSION_NUMBER '||
' ) VALUES ( '||
' :1, '||
' :2, '||
' :3, '||
' :4, '||
' :5, '||
' :6, '||
' :7, '||
' :8, '||
' :9, '||
' :10, '||
' :11, '||
' :12) '
USING l_param_property_id,FND_GLOBAL.USER_ID,SYSDATE,
FND_GLOBAL.USER_ID, SYSDATE,FND_GLOBAL.LOGIN_ID,
p_action_param_set_id, p_param_id, p_property_id,
p_property_value, 'F','0' ;
' select VALUE_TRANSLATABLE_FLAG '||
' from ieu_wp_properties_b '||
' where property_id = :1 '
into l_trans_flag USING p_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 ( '||
' :1, '||
' :2, '||
' :3,'||
' :4, '||
' :5, '||
' :6, '||
' :7, '||
' :8, '||
' :9, '||
' :10 '||
') '
USING l_param_property_id, FND_GLOBAL.USER_ID, SYSDATE,
FND_GLOBAL.USER_ID, SYSDATE,FND_GLOBAL.LOGIN_ID,
p_property_value, l_language, l_source_lang , '0' ;
' select PARAM_PROPERTY_ID '||
' from IEU_WP_PARAM_PROPS_B '||
' where PARAM_ID = :1 '||
' and ACTION_PARAM_SET_ID = :2 ' ||
' and PROPERTY_ID = :3 '
into l_param_property_id USING p_param_id,p_action_param_set_id, p_property_id ;
' update IEU_WP_PARAM_PROPS_TL set '||
' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
' LAST_UPDATE_DATE = SYSDATE, '||
' LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, '||
' PROPERTY_VALUE = :1 ' ||
' where PARAM_PROPERTY_ID = :2 '
USING p_property_value, l_param_property_id;
END Update_Param_Props;
PROCEDURE Update_Column_Props ( x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_param_id IN NUMBER,
p_property_id IN NUMBER,
p_property_value IN VARCHAR2,
p_action_param_set_id IN NUMBER)AS
l_language VARCHAR2(4);
fnd_msg_pub.delete_msg();
update IEU_WP_PARAM_PROPS_B set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
PROPERTY_VALUE = p_property_value,
PROPERTY_ID = p_property_id
where PARAM_ID= p_param_id
and ACTION_PARAM_SET_ID = p_action_param_set_id
/*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
and PROPERTY_ID in (10022, 10003);
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,
OBJECT_VERSION_NUMBER
) VALUES (
l_param_property_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
p_action_param_set_id,
p_param_id,
p_property_id,
p_property_value,
'F',
0
);
update IEU_WP_PARAM_PROPS_B set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
PROPERTY_VALUE = p_property_value
where PARAM_ID= p_param_id
and ACTION_PARAM_SET_ID = p_action_param_set_id
and PROPERTY_ID = p_property_id;
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,
OBJECT_VERSION_NUMBER
) VALUES (
l_param_property_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
p_action_param_set_id,
p_param_id,
p_property_id,
p_property_value,
'F',
0
);
select VALUE_TRANSLATABLE_FLAG into l_trans_flag
from ieu_wp_properties_b
where property_id = p_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,
p_property_value,
l_language,
l_source_lang,
0
);
select PARAM_PROPERTY_ID into l_param_property_id
from IEU_WP_PARAM_PROPS_B
where PARAM_ID = p_param_id
and ACTION_PARAM_SET_ID = p_action_param_set_id
and PROPERTY_ID = p_property_id;
update IEU_WP_PARAM_PROPS_TL set
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
PROPERTY_VALUE = p_property_value
where PARAM_PROPERTY_ID = l_param_property_id;
END Update_Column_Props;
PROCEDURE Delete_Parameter (x_param_id IN NUMBER, x_param_set_id IN NUMBER) AS
l_language VARCHAR2(4);
' delete from ieu_wp_action_params where param_id = :1 '
USING x_param_id;
' delete from IEU_WP_PARAM_DEFS_TL where PARAM_ID = :1 '
USING x_param_id;
' delete from IEU_WP_PARAM_DEFS_B where PARAM_ID = :1 '
USING x_param_id;
' delete from IEU_WP_PARAM_PROPS_B ' ||
' where PARAM_ID = :1 '||
' and ACTION_PARAM_SET_ID = :2 '
USING x_param_id, x_param_set_id;
' select PARAM_PROPERTY_ID '||
' from IEU_WP_PARAM_PROPS_B '||
' where PARAM_ID = :1 ' ||
' and ACTION_PARAM_SET_ID= :2 '
INTO l_param_property_id USING x_param_id, x_param_set_id;
delete from IEU_WP_PARAM_PROPS_TL
where PARAM_PROPERTY_ID = l_param_property_id;
delete from ieu_wp_param_props_b where param_property_id in
(select param_property_id
from ieu_wp_param_props_b
where param_id not in (select param_id from ieu_wp_param_defs_b));
delete from ieu_wp_param_props_tl where param_property_id in
(select param_property_id
from ieu_wp_param_props_b
where param_id not in (select param_id from ieu_wp_param_defs_b));
END Delete_Parameter;
fnd_msg_pub.delete_msg();
fnd_msg_pub.delete_msg();
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 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_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_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_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,
APPLICATION_ID,
RESPONSIBILITY_ID,
ENUM_TYPE_UUID,
SECTION_ID,
SECTION_MAP_SEQUENCE
) values
(l_wp_node_section_map_id,
0,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
p_application,
null,
l_enum_uuid,
l_section_id,
l_section_map_sequence);
PROCEDURE Update_Data_Type ( x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_wp_action_def_id IN NUMBER,
p_param_id IN NUMBER)
AS
l_language VARCHAR2(4);
select distinct ppb.action_param_set_id, ap.param_id
from
ieu_wp_action_params ap,
ieu_wp_param_props_b ppb
where
ap.wp_action_def_id=p_wp_action_def_id
and ap.param_id = ppb.param_id
and ap.param_id=p_param_id;
fnd_msg_pub.delete_msg();
delete from ieu_wp_param_props_b
where action_param_set_id in ( select distinct ppb.ACTION_PARAM_SET_ID
from
ieu_wp_action_params ap,
ieu_wp_param_props_b ppb
where
ap.WP_ACTION_DEF_ID=p_wp_action_def_id
and ap.PARAM_ID = ppb.PARAM_ID)
and param_id =p_param_id
and property_id in (10002, 10013, 10014, 10015, 10016, 10017, 10018, 10019, 10020, 10010, 10003, 10021, 10011,10022);
END Update_Data_Type;
PROCEDURE Update_Multi_Select_Flag ( x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_wp_action_def_id IN NUMBER)
as
l_language VARCHAR2(4);
fnd_msg_pub.delete_msg();
delete from ieu_wp_param_props_b
where property_id in(10010, 10003, 10021, 10011)
and action_param_set_id in (select distinct ppb.ACTION_PARAM_SET_ID
from
ieu_wp_action_params ap,
ieu_wp_param_props_b ppb
where
ap.WP_ACTION_DEF_ID= p_wp_action_def_id
and ap.PARAM_ID = ppb.PARAM_ID);
END Update_Multi_Select_Flag;
select ppb.PARAM_PROPERTY_ID, pdb.PARAM_ID, ppb.PROPERTY_VALUE
from ieu_wp_action_params p,
ieu_wp_param_defs_b pdb,
ieu_wp_param_props_b ppb
where p.WP_ACTION_DEF_ID = p_wp_action_def_id
and p.PARAM_ID = pdb.PARAM_ID
and pdb.PARAM_ID = ppb.PARAM_ID
and ppb.ACTION_PARAM_SET_ID = p_action_param_set_id
and ppb.PROPERTY_ID = 10000
order by to_number(ppb.PROPERTY_VALUE);
fnd_msg_pub.delete_msg();
update ieu_wp_param_props_b
set property_value = l_count
where param_id = c_rec.param_id
and param_property_id = c_rec.param_property_id
and property_value = c_rec.property_value
and property_id = 10000
and action_param_set_id = p_action_param_set_id;