[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_external_update_text VARCHAR2(2000);
SELECT distinct version
FROM PV_ENTY_ATTR_VALUES
WHERE attribute_id = cv_attribute_id and
entity_id = cv_entity_id and
entity = cv_entity and
latest_flag = 'Y';
SELECT *
FROM PV_ENTY_ATTR_VALUES
WHERE attribute_id = cv_attribute_id and
entity_id = cv_entity_id and
entity = cv_entity and
latest_flag = 'Y';
SELECT attribute_type,display_style,DECIMAL_POINTS,name, character_width
FROM PV_ATTRIBUTES_VL
WHERE attribute_id = cv_attribute_id
;
SELECT external_update_text, attr_data_type, require_validation_flag, lov_string
FROM PV_ENTITY_ATTRS
WHERE attribute_id = pc_attribute_id AND
entity= pc_entity
;
l_external_update_text := x.external_update_text;
PVX_Utility_PVT.debug_message('external update text:' || l_external_update_text);
-- In this case we just simply insert and update
if(p_entity='LEAD') then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - entity is opportunity');
PV_Enty_Attr_Value_PVT.Delete_attr_value(
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_enty_attr_val_id => x.enty_attr_val_id
,p_object_version_number => x.object_version_number
);
PVX_Utility_PVT.debug_message('public API: '||l_full_name||' -update act');
--if you find any rows, firsdt delete and add the new ones
for x in c_get_enty_attr_value( cv_attribute_id => p_attribute_id,
cv_entity_id => p_entity_id,
cv_entity => p_entity
)
loop
PV_Enty_Attr_Value_PVT.Delete_attr_value(
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_enty_attr_val_id => x.enty_attr_val_id
,p_object_version_number => x.object_version_number
);
-- If attribute type is EXTERNAL and EXT_INT, look for external_update_text and
-- see if it is not null. if it is not null, execute it .
-- In all other cases insert in to pv_enty_attr_values.
IF(l_attr_data_type in ('EXTERNAL','EXT_INT') AND
l_external_update_text is not null
) THEN
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - attr_data_type is :'|| l_attr_data_type);
--if update text is not null , then call update API for each attribute
--IF (l_external_update_text is not null) THEN
IF (l_attr_val_tbl IS NOT NULL AND l_attr_val_tbl.count <> 0
) THEN
FOR l_curr_row IN l_attr_val_tbl.first..l_attr_val_tbl.last LOOP
l_attr_val_rec := l_attr_val_tbl(l_curr_row);
EXECUTE IMMEDIATE l_external_update_text USING p_api_version_number
,p_init_msg_list
,p_commit
,p_validation_level
,out x_return_status
,out x_msg_count
,out x_msg_data
,p_entity
,p_entity_id
,l_value;
FND_MESSAGE.set_token('TEXT', l_external_update_text);
-- In all other cases than l_attr_data_type in ('EXTERNAL','EXT_INT') and l_external_update_text is not null
-- this else block will end at last line
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - attr_data_type is :'|| l_attr_data_type);
--Update attribute_values with latest_flag = 'Y' before inserting new rows by changing latest_flag to 'N'
for x in c_get_enty_attr_value(cv_attribute_id => p_attribute_id,
cv_entity_id => p_entity_id,
cv_entity => p_entity
)
loop
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - Updating loop');
--get all values from table and assign it to record and call update_attr_value of pv_enty_attr_value_pvt
l_enty_attr_val_rec.enty_attr_val_id := x.enty_attr_val_id;
--update latest flag with 'N'
l_enty_attr_val_rec.latest_flag := 'N';
PVX_Utility_PVT.debug_message('public API: '||l_full_name||' - before calling pvt update method');
PV_Enty_Attr_Value_PVT.Update_Attr_Value(
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_enty_attr_val_rec => l_enty_attr_val_rec
,x_object_version_number => x_object_version_number
);
l_attr_val_tbl.delete(l_curr_row);
/*l_enty_attr_val_rec.last_update_date := SYSDATE;
l_enty_attr_val_rec.last_updated_by := G_USER_ID;
l_enty_attr_val_rec.last_update_login := G_LOGIN_ID;
-- attribute type = Drop Down and style= Percentage, Multi-Select, check-Box, External_LOV
if(l_attr_val_tbl.count >= 2
and not (l_attribute_type = 'DROPDOWN'
and l_DISPLAY_STYLE in ('EXTERNAL_LOV','MULTI','CHECK','PERCENTAGE')
)
and not (l_attribute_type = 'FUNCTION')
) then
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
Fnd_Message.set_name('PV', 'PV_ENTY_ATTR_VAL_MULTI_ERROR');
FOR x IN (select meaning from pv_lookups
where lookup_type = 'PV_ATTRIBUTE_TYPE'
and lookup_code = l_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_display_style
) LOOP
l_meaning := x.meaning;
l_enty_attr_val_rec.last_update_date := SYSDATE;
l_enty_attr_val_rec.last_updated_by := G_USER_ID;
l_enty_attr_val_rec.last_update_login := G_LOGIN_ID;
SELECT distinct enty.attr_data_type, ENTY.SQL_TEXT -- VAL.version,
FROM PV_ATTRIBUTES_VL ATTR, PV_ENTITY_ATTRS ENTY, PV_ENTY_ATTR_VALUES VAL
WHERE ATTR.attribute_id = cv_attribute_id AND
ENTY.ATTRIBUTE_ID = ATTR.attribute_id AND
ENTY.ENTITY = 'PARTNER' AND
VAL.ATTRIBUTE_ID (+) = ENTY.ATTRIBUTE_ID AND
VAL.ENTITY (+) = ENTY.ENTITY AND
VAL.ENTITY_ID (+) = cv_entity_id
;
select enty.attr_value, enty.Attr_Value_Extn
from pv_enty_attr_values enty
where enty.attribute_id = cv_attribute_id
and enty.entity = 'PARTNER' and
enty.entity_id = cv_entity_id AND
enty.LATEST_FLAG = 'Y' AND
enty.ATTR_VALUE is not null
;
SELECT distinct version
FROM PV_ENTY_ATTR_VALUES
WHERE attribute_id = cv_attribute_id and
entity_id = cv_entity_id and
entity = cv_entity and
latest_flag = 'Y';
PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - inserting Internal ...');
PVX_Utility_PVT.debug_message('Public API: '||l_full_name||' - inserting External ...');
PVX_Utility_PVT.debug_message('inserting it as additional partner type:'|| l_attr_value);
--The Additional Partner Type selected has already been selected as Primary Partner Type.
--Please select different partner type
IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
PVX_Utility_PVT.debug_message('One of the the additional partner types is primary partner type');