[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PV_ATTRIBUTES_B_s.NEXTVAL
FROM dual;
SELECT 1
FROM PV_ATTRIBUTES_B
WHERE ATTRIBUTE_ID = l_id;
l_attribute_rec.last_update_date := SYSDATE;
l_attribute_rec.last_updated_by := G_USER_ID;
l_attribute_rec.last_update_login := G_LOGIN_ID;
FOR x IN (select meaning from pv_lookups
where lookup_type = 'PV_ATTRIBUTE_TYPE'
and lookup_code = l_attribute_rec.attribute_type
) LOOP
l_meaning := x.meaning;
FOR x IN (select meaning from pv_lookups
where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
and lookup_code = l_attribute_rec.display_style
) LOOP
l_meaning := x.meaning;
PV_ATTRIBUTE_PKG.Insert_Row(
px_attribute_id => l_attribute_rec.attribute_id,
p_last_update_date => l_attribute_rec.last_update_date,
p_last_updated_by => l_attribute_rec.last_updated_by,
p_creation_date => l_attribute_rec.creation_date,
p_created_by => l_attribute_rec.created_by,
p_last_update_login => l_attribute_rec.last_update_login,
px_object_version_number => l_attribute_rec.object_version_number,
--p_security_group_id => l_attribute_rec.security_group_id,
p_enabled_flag => l_attribute_rec.enabled_flag,
p_attribute_type => l_attribute_rec.attribute_type,
p_attribute_category => l_attribute_rec.attribute_category,
p_seeded_flag => l_attribute_rec.seeded_flag,
p_lov_function_name => l_attribute_rec.lov_function_name,
p_return_type => l_attribute_rec.return_type,
p_max_value_flag => l_attribute_rec.max_value_flag,
p_name => l_attribute_rec.name,
p_description => l_attribute_rec.description,
p_short_name => l_attribute_rec.short_name,
p_display_style => l_attribute_rec.display_style,
p_character_width => l_attribute_rec.character_width,
p_decimal_points => l_attribute_rec.decimal_points,
p_no_of_lines => l_attribute_rec.no_of_lines,
p_expose_to_partner_flag => l_attribute_rec.expose_to_partner_flag,
p_value_extn_return_type => l_attribute_rec.value_extn_return_type,
p_enable_matching_flag => l_attribute_rec.enable_matching_flag,
p_performance_flag => l_attribute_rec.performance_flag,
p_additive_flag => l_attribute_rec.additive_flag,
p_sequence_number => l_attribute_rec.sequence_number
);
PROCEDURE Update_Attribute(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_attribute_rec IN attribute_rec_type
,x_object_version_number OUT NOCOPY NUMBER
)
IS
CURSOR c_get_attribute(cv_attribute_id NUMBER) IS
SELECT *
FROM PV_ATTRIBUTES_B
where attribute_id = cv_attribute_id;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Attribute';
l_delete_flag VARCHAR2(1):='Y';
select distinct rules.process_rule_name
from pv_enty_select_criteria criteria,pv_process_rules_vl rules
where criteria.attribute_id= pc_attribute_id
and criteria.process_rule_id= rules.process_rule_id;
select resp.RESPONSIBILITY_NAME
from pv_attrib_resp_mappings val, FND_RESPONSIBILITY_VL resp
where
val.attribute_id= pc_attribute_id and
resp.application_id = 691 and
val.entity_type = 'MANUAL_MATCHING' and
resp.RESPONSIBILITY_ID = val.responsibility_id;
SAVEPOINT UPDATE_Attribute_PVT;
PVX_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
FND_MESSAGE.set_token('MODE','Update');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
FND_MESSAGE.Set_Name('PV', 'PV_ATTR_NOTDISABLED_RULE');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
FND_MESSAGE.Set_Name('PV', 'PV_ATTR_NOTDISABLED_MATCHING');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
FND_MESSAGE.Set_Name('PV', 'PV_VALUE_TYPE_REFERENCED_RULE');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
FND_MESSAGE.Set_Name('PV', 'PV_MATCHING_REFERENCED_RULE');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
FND_MESSAGE.Set_Name('PV', 'PV_MATCHING_NOTDISABLED');
-- FOR x IN (select meaning from pv_lookups
-- where lookup_type = 'PV_ATTRIBUTE_TYPE'
-- and lookup_code = p_attribute_rec.attribute_type
-- ) LOOP
-- l_meaning := x.meaning;
-- FOR x IN (select meaning from pv_lookups
-- where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
-- and lookup_code = l_ref_attribute_rec.display_style
-- ) LOOP
-- l_meaning := x.meaning;
-- FOR x IN (select meaning from pv_lookups
-- where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
-- and lookup_code = p_attribute_rec.display_style
-- ) LOOP
-- l_meaning := x.meaning;
FOR x IN (select meaning from pv_lookups
where lookup_type = 'PV_ATTRIBUTE_TYPE'
and lookup_code = p_attribute_rec.attribute_type
) LOOP
l_meaning := x.meaning;
FOR x IN (select meaning from pv_lookups
where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
and lookup_code = l_ref_attribute_rec.display_style
) LOOP
l_meaning := x.meaning;
FOR x IN (select meaning from pv_lookups
where lookup_type = 'PV_ATTR_DISPLAY_STYLE'
and lookup_code = p_attribute_rec.display_style
) LOOP
l_meaning := x.meaning;
FND_MESSAGE.set_token('COLUMN', TO_CHAR(l_tar_attribute_rec.last_update_date));
p_validation_mode => JTF_PLSQL_API.g_update,
p_attribute_rec => p_attribute_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PVX_UTILITY_PVT.debug_message('Private API: '||l_full_name||' - Calling update table handler');
PV_ATTRIBUTE_PKG.Update_Row(
p_attribute_id => p_attribute_rec.attribute_id,
p_last_update_date => SYSDATE,
p_last_updated_by => G_USER_ID,
--p_creation_date => SYSDATE,
--p_created_by => G_USER_ID,
p_last_update_login => G_LOGIN_ID,
p_object_version_number => p_attribute_rec.object_version_number,
--p_security_group_id => p_attribute_rec.security_group_id,
p_enabled_flag => p_attribute_rec.enabled_flag,
p_attribute_type => p_attribute_rec.attribute_type,
p_attribute_category => p_attribute_rec.attribute_category,
p_seeded_flag => p_attribute_rec.seeded_flag,
p_lov_function_name => p_attribute_rec.lov_function_name,
p_return_type => p_attribute_rec.return_type,
p_max_value_flag => p_attribute_rec.max_value_flag,
p_name => p_attribute_rec.name,
p_description => p_attribute_rec.description,
p_short_name => p_attribute_rec.short_name,
p_display_style => p_attribute_rec.display_style,
p_character_width => p_attribute_rec.character_width,
p_decimal_points => p_attribute_rec.decimal_points,
p_no_of_lines => p_attribute_rec.no_of_lines,
p_expose_to_partner_flag => p_attribute_rec.expose_to_partner_flag,
p_value_extn_return_type => p_attribute_rec.value_extn_return_type,
p_enable_matching_flag => p_attribute_rec.enable_matching_flag,
p_performance_flag => p_attribute_rec.performance_flag,
p_additive_flag => p_attribute_rec.additive_flag,
p_sequence_number => l_sequence_number
);
ROLLBACK TO UPDATE_Attribute_PVT;
ROLLBACK TO UPDATE_Attribute_PVT;
ROLLBACK TO UPDATE_Attribute_PVT;
End Update_Attribute;
PROCEDURE Delete_Attribute(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_attribute_id IN NUMBER
,p_object_version_number IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Attribute';
l_delete_flag VARCHAR2(1):='Y';
select name
from pv_attributes_vl
where attribute_id = pc_attribute_id;
select distinct entity
from pv_enty_attr_values
where attribute_id = pc_attribute_id;
select distinct rules.process_rule_name
from pv_enty_select_criteria criteria,pv_process_rules_vl rules
where criteria.attribute_id= pc_attribute_id
and criteria.process_rule_id= rules.process_rule_id;
select resp.RESPONSIBILITY_NAME
from pv_attrib_resp_mappings val, FND_RESPONSIBILITY_VL resp
where
val.attribute_id= pc_attribute_id and
resp.application_id = 691 and
val.entity_type = 'MANUAL_MATCHING' and
resp.RESPONSIBILITY_ID = val.responsibility_id;
select resp.RESPONSIBILITY_NAME, lkp.meaning
from pv_attrib_resp_mappings val,
FND_RESPONSIBILITY_VL resp,
pv_lookups lkp
where
val.attribute_id= pc_attribute_id and
resp.RESPONSIBILITY_ID = val.responsibility_id and
resp.application_id = 691 and
val.entity_type = lkp.lookup_code and
lkp.lookup_type='PV_ATTR_RESP_MAPPING_ENTITIES';
select seeded_flag
from pv_attributes_vl
where attribute_id = pc_attribute_id;
select entity_attr_id
from pv_entity_attrs
where attribute_id = pc_attribute_id;
select attribute_usage_id
from pv_attribute_usages
where attribute_id = pc_attribute_id;
select attr_code_id
from PV_ATTRIBUTE_CODES_vl
where attribute_id = pc_attribute_id;
SAVEPOINT DELETE_Attribute_PVT;
l_delete_flag :='N';
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
THEN
FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_SEEDED');
l_delete_flag := 'N';
FOR y IN (select meaning from pv_lookups
where lookup_type = 'PV_VALID_ENTY_VALUE_TYPES'
and lookup_code = x.entity
) LOOP
l_meaning := y.meaning;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
THEN
FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_ENTITY');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
THEN
FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_RULE');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
THEN
FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_MATCHING');
l_delete_flag := 'N' ;
if(l_delete_flag = 'N') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR))
THEN
FND_MESSAGE.Set_Name('PV', 'PV_ATTR_REFERENCED_RESP_MAP');
PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
PVX_UTILITY_PVT.debug_message( 'Calling PV_ATTRIBUTE_CODE_PVT.Delete_Attribute_Code for : ' ||x.attr_code_id );
PV_ATTRIBUTE_CODE_PVT.Delete_Attribute_Code(
p_api_version_number => p_api_version_number
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => p_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_attr_code_id => x.attr_code_id
,p_object_version_number=> p_object_version_number
);
PVX_UTILITY_PVT.debug_message( 'Calling PV_Attribute_Usage_PVT.Delete_Attribute_Usage for : ' ||x.attribute_usage_id );
PV_Attribute_Usage_PVT.Delete_Attribute_Usage(
p_api_version_number => p_api_version_number
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => p_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_attribute_usage_id => x.attribute_usage_id
,p_object_version_number=> p_object_version_number
);
PVX_UTILITY_PVT.debug_message( 'Calling PV_Entity_Attribute_Pvt.Delete_Entity_Attr for : ' ||x.entity_attr_id );
PV_Entity_Attribute_Pvt.Delete_Entity_Attr(
p_api_version_number => p_api_version_number
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => p_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_entity_attr_id => x.entity_attr_id
,p_object_version_number=> p_object_version_number
);
PV_ATTRIBUTE_PKG.Delete_Row(
p_ATTRIBUTE_ID => p_ATTRIBUTE_ID);
ROLLBACK TO DELETE_Attribute_PVT;
ROLLBACK TO DELETE_Attribute_PVT;
ROLLBACK TO DELETE_Attribute_PVT;
End Delete_Attribute;
SELECT ATTRIBUTE_ID
FROM PV_ATTRIBUTES_B
WHERE ATTRIBUTE_ID = p_ATTRIBUTE_ID
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
select name from pv_attributes_tl;
IF p_attribute_rec.last_update_date = FND_API.g_miss_date OR p_attribute_rec.last_update_date IS NULL THEN
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
FND_MESSAGE.set_token('COLUMN','last_update_date');
IF p_attribute_rec.last_updated_by = FND_API.g_miss_num OR p_attribute_rec.last_updated_by IS NULL THEN
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
FND_MESSAGE.set_token('COLUMN','last_updated_by');
IF p_attribute_rec.last_update_login = FND_API.g_miss_num OR p_attribute_rec.last_update_login IS NULL THEN
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
FND_MESSAGE.set_token('COLUMN','last_update_login');
IF p_attribute_rec.last_update_date IS NULL THEN
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
FND_MESSAGE.set_token('COLUMN','last_update_date');
IF p_attribute_rec.last_updated_by IS NULL THEN
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
FND_MESSAGE.set_token('COLUMN','last_updated_by');
IF p_attribute_rec.last_update_login IS NULL THEN
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
FND_MESSAGE.set_token('COLUMN','last_update_login');
SELECT *
FROM pv_attributes_vl
WHERE attribute_id = p_attribute_rec.attribute_id;
IF p_attribute_rec.last_update_date = FND_API.g_miss_date THEN
x_complete_rec.last_update_date := l_attribute_rec.last_update_date;
IF p_attribute_rec.last_updated_by = FND_API.g_miss_num THEN
x_complete_rec.last_updated_by := l_attribute_rec.last_updated_by;
IF p_attribute_rec.last_update_login = FND_API.g_miss_num THEN
x_complete_rec.last_update_login := l_attribute_rec.last_update_login;
,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_update
,p_attribute_rec IN attribute_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Attribute';
,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_UPDATE
)
IS
BEGIN
--DBMS_output.put_line('validate_attribute_rec');