[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_always_insert VARCHAR2(1);
SELECT
EXT.attr_id,
FL_CTX_EXT.attr_group_id,
EXT.application_column_name,
EXT.descriptive_flex_context_code as attr_group_name,
ITA.template_id,
ITA.enabled_flag,
ITA.attribute_name,
ITA.attribute_value
FROM
EGO_FND_DF_COL_USGS_EXT EXT,
EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT,
MTL_ITEM_TEMPL_ATTRIBUTES ITA
WHERE
EXT.descriptive_flex_context_code = FL_CTX_EXT.descriptive_flex_context_code and
EXT.application_id = FL_CTX_EXT.application_id and
EXT.descriptive_flexfield_name = FL_CTX_EXT.descriptive_flexfield_name and
EXT.descriptive_flexfield_name = 'EGO_MASTER_ITEMS' and
ITA.attribute_name = 'MTL_SYSTEM_ITEMS.'|| EXT.application_column_name and
ITA.template_id = p_template_id;
SELECT 'F' INTO l_always_insert
FROM ego_templ_attributes
WHERE template_id = p_template_id;
l_always_insert := 'T';
l_always_insert := 'F';
l_always_insert
);
p_always_insert IN VARCHAR2 := FND_API.G_FALSE
)
IS
--5101284 : Perf issues
CURSOR c_check_template_attribute(cp_template_id NUMBER
,cp_attribute_name VARCHAR2) IS
SELECT 1
FROM fnd_descr_flex_column_usages fl_col ,
ego_fnd_df_col_usgs_ext ext,
ego_templ_attributes eta
WHERE ext.application_id = fl_col.application_id
AND ext.descriptive_flexfield_name = fl_col.descriptive_flexfield_name
AND ext.descriptive_flex_context_code = fl_col.descriptive_flex_context_code
AND ext.application_column_name = fl_col.application_column_name
AND fl_col.descriptive_flexfield_name = 'EGO_MASTER_ITEMS'
AND eta.attribute_id = ext.attr_id
AND 'MTL_SYSTEM_ITEMS.'||fl_col.application_column_name = cp_attribute_name
AND eta.template_id = cp_template_id
AND rownum = 1;
l_insert VARCHAR2(1);
IF FND_API.TO_BOOLEAN(p_always_insert) THEN
l_insert := p_always_insert;
SELECT
1 into l_exists
FROM
dual
WHERE
exists ( select attr_id from ego_templ_attributes eta, ego_attrs_v av
where 'MTL_SYSTEM_ITEMS.'||av.database_column = p_attribute_name
and eta.attribute_id = av.attr_id
and av.attr_group_type = 'EGO_MASTER_ITEMS'
and template_id = p_template_id);
l_insert := FND_API.G_TRUE;
l_insert := FND_API.G_FALSE;
IF FND_API.TO_BOOLEAN(l_insert) THEN
Insert_Template_Attribute( p_template_id,
p_ego_attr_group_id,
p_ego_attr_id,
l_data_level_id,
p_enabled_flag,
p_attribute_value,
p_commit,
x_return_status,
x_message_text
);
Update_Template_Attribute( p_template_id,
p_ego_attr_group_id,
p_ego_attr_id,
l_data_level_id,
p_enabled_flag,
p_attribute_value,
p_commit,
x_return_status,
x_message_text
);
p_always_insert IN VARCHAR2 := FND_API.G_FALSE
)
IS
--5101284 : Perf issues
CURSOR c_check_template_attribute(cp_template_id NUMBER
,cp_attribute_name VARCHAR2) IS
SELECT 1
FROM fnd_descr_flex_column_usages fl_col ,
ego_fnd_df_col_usgs_ext ext,
ego_templ_attributes eta
WHERE ext.application_id = fl_col.application_id
AND ext.descriptive_flexfield_name = fl_col.descriptive_flexfield_name
AND ext.descriptive_flex_context_code = fl_col.descriptive_flex_context_code
AND ext.application_column_name = fl_col.application_column_name
AND fl_col.descriptive_flexfield_name = 'EGO_MASTER_ITEMS'
AND eta.attribute_id = ext.attr_id
AND 'MTL_SYSTEM_ITEMS.'||fl_col.application_column_name = cp_attribute_name
AND eta.template_id = cp_template_id
AND rownum = 1;
l_insert VARCHAR2(1);
SELECT *
INTO r_inv_templ_attribute
FROM mtl_item_templ_attributes mta
WHERE mta.ATTRIBUTE_NAME = p_attribute_name
AND mta.template_id = p_template_id;
IF FND_API.TO_BOOLEAN(p_always_insert) THEN
l_insert := p_always_insert;
SELECT
1 into l_exists
FROM
dual
WHERE
exists ( select attr_id from ego_templ_attributes eta, ego_attrs_v av
where 'MTL_SYSTEM_ITEMS.'||av.database_column = p_attribute_name
and eta.attribute_id = av.attr_id
and av.attr_group_type = 'EGO_MASTER_ITEMS'
and template_id = p_template_id);
l_insert := FND_API.G_TRUE;
l_insert := FND_API.G_FALSE;
IF FND_API.TO_BOOLEAN(l_insert) THEN
Debug_Msg(l_api_name || 'Performing insert.');
Insert_Template_Attribute( r_inv_templ_attribute.template_id,
l_attribute_group_id,
l_attribute_id,
l_data_level_id,
r_inv_templ_attribute.enabled_flag,
r_inv_templ_attribute.attribute_value,
p_commit,
x_return_status,
x_message_text
);
Debug_Msg(l_api_name || 'Performing update.');
Update_Template_Attribute( r_inv_templ_attribute.template_id,
l_attribute_group_id,
l_attribute_id,
l_data_level_id,
r_inv_templ_attribute.enabled_flag,
r_inv_templ_attribute.attribute_value,
p_commit,
x_return_status,
x_message_text
);
Procedure Insert_Template_Attribute
( p_template_id IN NUMBER,
p_attribute_group_id IN NUMBER,
p_attribute_id IN NUMBER,
p_data_level_id IN NUMBER,
p_enabled_flag IN VARCHAR2,
p_attribute_value IN VARCHAR2,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_message_text OUT NOCOPY VARCHAR2
)
IS
l_row_num NUMBER;
l_api_name VARCHAR2(50) := 'INSERT_TEMPLATE_ATTRIBUTE';
select eav.data_type_code into l_data_type_code
from ego_attrs_v eav
where attr_id = p_attribute_id;
select to_number(p_attribute_value) into l_attr_number_value from dual;
select to_date(p_attribute_value, 'DD/MM/YYYY') into l_attr_date_value from dual;
select to_date(p_attribute_value, 'DD/MM/YYYY HH:MM:SS AM') into l_attr_date_value from dual;
insert into ego_templ_attributes(template_id,
attribute_group_id,
attribute_id,
enabled_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
row_number,
attribute_string_value,
attribute_date_value,
attribute_number_value,
attribute_translated_value,
classification_code,
data_level_id
)
values( p_template_id,
p_attribute_group_id,
p_attribute_id,
p_enabled_flag,
sysdate,
g_current_user_id,
sysdate,
g_current_user_id,
l_row_num,
l_attr_string_value,
l_attr_date_value,
l_attr_number_value,
l_attr_translated_value,
l_classification_code,
p_data_level_id
);
x_message_text := 'Failure to insert new row for template attribute';
END Insert_Template_Attribute;
Procedure Update_Template_Attribute
( p_template_id IN NUMBER,
p_attribute_group_id IN NUMBER,
p_attribute_id IN NUMBER,
p_data_level_id IN NUMBER,
p_enabled_flag IN VARCHAR2,
p_attribute_value IN VARCHAR2,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_message_text OUT NOCOPY VARCHAR2
)
IS
l_attr_string_value VARCHAR2(150);
l_api_name VARCHAR2(50) := 'UPDATE_TEMPLATE_ATTRIBUTE';
select eav.data_type_code into l_data_type_code
from ego_attrs_v eav
where attr_id = p_attribute_id;
select to_number(p_attribute_value) into l_attr_number_value from dual;
select to_date(p_attribute_value, 'DD/MM/YYYY') into l_attr_date_value from dual;
select to_date(p_attribute_value, 'DD/MM/YYYY HH:MM:SS AM') into l_attr_date_value from dual;
update ego_templ_attributes
set attribute_string_value = l_attr_string_value,
attribute_number_value = l_attr_number_value,
attribute_date_value = l_attr_date_value,
attribute_translated_value = l_attr_translated_value,
enabled_flag = p_enabled_flag,
created_by = g_current_user_id,
creation_date = sysdate,
last_updated_by = g_current_user_id,
last_update_date = sysdate,
last_update_login = g_current_login_id,
data_level_id = p_data_level_id
where classification_code = '-1'
and attribute_id = p_attribute_id
and attribute_group_id = p_attribute_group_id
and template_id = p_template_id;
x_message_text := 'Failure to update EGO_TEMPL_ATTRIBUTES';
END Update_Template_Attribute;
SELECT data_level_id
INTO x_data_level_id
FROM ego_data_level_b
WHERE application_id = p_application_id AND
attr_group_type = p_attr_group_type AND
data_level_name = p_data_level_name;
SELECT eav.attr_id, eagv.attr_group_id
INTO x_attr_id, x_attr_group_id
FROM ego_attrs_v eav, ego_attr_groups_v eagv
WHERE 'MTL_SYSTEM_ITEMS.'||eav.database_column = p_attr_name
AND eav.attr_group_type = eagv.attr_group_type
AND eav.attr_group_name = eagv.attr_group_name
AND eav.application_id = p_application_id
-- This filtering is added to Supply the Index
-- and thus eliminating full table scan Bug 4926750
AND eav.application_id = eagv.application_id
AND eav.attr_group_type = p_attr_group_type;