The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_SELECTION_CRITERIA_ID OUT NOCOPY NUMBER,
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) := 'Create_selcrit';
IF (p_SELCRIT_REC.SELECTION_TYPE_CODE = 'MONITOR_SCOPE') THEN
-- --------------------------------------------------------------
-- Check the database for the "before" image. We need to compare
-- the before image to the after image.
-- --------------------------------------------------------------
FOR x IN (SELECT status_code FROM pv_process_rules_b
WHERE process_rule_id = p_SELCRIT_rec.PROCESS_RULE_ID)
LOOP
l_previous_status := x.status_code;
PV_ENTY_SELECT_CRITERIA_PKG.Insert_Row(
px_SELECTION_CRITERIA_ID => x_SELECTION_CRITERIA_ID
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => FND_GLOBAL.USER_ID
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
,p_OBJECT_VERSION_NUMBER => p_SELCRIT_rec.OBJECT_VERSION_NUMBER
,p_REQUEST_ID => p_SELCRIT_rec.REQUEST_ID
,p_PROGRAM_APPLICATION_ID => p_SELCRIT_rec.PROGRAM_APPLICATION_ID
,p_PROGRAM_ID => p_SELCRIT_rec.PROGRAM_ID
,p_PROGRAM_UPDATE_DATE => p_SELCRIT_rec.PROGRAM_UPDATE_DATE
,p_PROCESS_RULE_ID => p_SELCRIT_rec.PROCESS_RULE_ID
,p_ATTRIBUTE_ID => p_SELCRIT_rec.ATTRIBUTE_ID
,p_SELECTION_TYPE_CODE => p_SELCRIT_rec.SELECTION_TYPE_CODE
,p_OPERATOR => p_SELCRIT_rec.OPERATOR
,p_RANK => p_SELCRIT_rec.RANK
,p_ATTRIBUTE_CATEGORY => p_SELCRIT_rec.ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => p_SELCRIT_rec.ATTRIBUTE1
,p_ATTRIBUTE2 => p_SELCRIT_rec.ATTRIBUTE2
,p_ATTRIBUTE3 => p_SELCRIT_rec.ATTRIBUTE3
,p_ATTRIBUTE4 => p_SELCRIT_rec.ATTRIBUTE4
,p_ATTRIBUTE5 => p_SELCRIT_rec.ATTRIBUTE5
,p_ATTRIBUTE6 => p_SELCRIT_rec.ATTRIBUTE6
,p_ATTRIBUTE7 => p_SELCRIT_rec.ATTRIBUTE7
,p_ATTRIBUTE8 => p_SELCRIT_rec.ATTRIBUTE8
,p_ATTRIBUTE9 => p_SELCRIT_rec.ATTRIBUTE9
,p_ATTRIBUTE10 => p_SELCRIT_rec.ATTRIBUTE10
,p_ATTRIBUTE11 => p_SELCRIT_rec.ATTRIBUTE11
,p_ATTRIBUTE12 => p_SELCRIT_rec.ATTRIBUTE12
,p_ATTRIBUTE13 => p_SELCRIT_rec.ATTRIBUTE13
,p_ATTRIBUTE14 => p_SELCRIT_rec.ATTRIBUTE14
,p_ATTRIBUTE15 => p_SELCRIT_rec.ATTRIBUTE15
); -- Hint: Primary key should be returned.
PROCEDURE Update_selcrit(
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,
P_Identity_Resource_Id IN NUMBER,
P_SELCRIT_Rec IN PV_RULE_RECTYPE_PUB.SELCRIT_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
Cursor C_Get_selcrit(pc_SELECTION_CRITERIA_ID Number) IS
Select object_version_number
From PV_ENTY_SELECT_CRITERIA
where selection_criteria_id = pc_selection_criteria_id
For Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_selcrit';
SAVEPOINT UPDATE_SELCRIT_PVT;
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Open Cursor to Select');
Open C_Get_selcrit( l_tar_SELCRIT_rec.SELECTION_CRITERIA_ID);
FND_MESSAGE.Set_Name('PV', 'API_MISSING_UPDATE_TARGET');
p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
P_SELCRIT_Rec => P_SELCRIT_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF (p_SELCRIT_REC.SELECTION_TYPE_CODE = 'MONITOR_SCOPE') THEN
-- --------------------------------------------------------------
-- Check the database for the "before" image. We need to compare
-- the before image to the after image.
-- --------------------------------------------------------------
FOR x IN (SELECT status_code FROM pv_process_rules_b
WHERE process_rule_id = p_SELCRIT_rec.PROCESS_RULE_ID)
LOOP
l_previous_status := x.status_code;
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
PV_ENTY_SELECT_CRITERIA_PKG.Update_Row(
p_SELECTION_CRITERIA_ID => p_SELCRIT_rec.SELECTION_CRITERIA_ID
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => FND_API.G_MISS_DATE
,p_CREATED_BY => FND_API.G_MISS_NUM
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
,p_OBJECT_VERSION_NUMBER => p_SELCRIT_rec.OBJECT_VERSION_NUMBER
,p_REQUEST_ID => p_SELCRIT_rec.REQUEST_ID
,p_PROGRAM_APPLICATION_ID => p_SELCRIT_rec.PROGRAM_APPLICATION_ID
,p_PROGRAM_ID => p_SELCRIT_rec.PROGRAM_ID
,p_PROGRAM_UPDATE_DATE => p_SELCRIT_rec.PROGRAM_UPDATE_DATE
,p_PROCESS_RULE_ID => p_SELCRIT_rec.PROCESS_RULE_ID
,p_ATTRIBUTE_ID => p_SELCRIT_rec.ATTRIBUTE_ID
,p_SELECTION_TYPE_CODE => p_SELCRIT_rec.SELECTION_TYPE_CODE
,p_OPERATOR => p_SELCRIT_rec.OPERATOR
,p_RANK => p_SELCRIT_rec.RANK
,p_ATTRIBUTE_CATEGORY => p_SELCRIT_rec.ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => p_SELCRIT_rec.ATTRIBUTE1
,p_ATTRIBUTE2 => p_SELCRIT_rec.ATTRIBUTE2
,p_ATTRIBUTE3 => p_SELCRIT_rec.ATTRIBUTE3
,p_ATTRIBUTE4 => p_SELCRIT_rec.ATTRIBUTE4
,p_ATTRIBUTE5 => p_SELCRIT_rec.ATTRIBUTE5
,p_ATTRIBUTE6 => p_SELCRIT_rec.ATTRIBUTE6
,p_ATTRIBUTE7 => p_SELCRIT_rec.ATTRIBUTE7
,p_ATTRIBUTE8 => p_SELCRIT_rec.ATTRIBUTE8
,p_ATTRIBUTE9 => p_SELCRIT_rec.ATTRIBUTE9
,p_ATTRIBUTE10 => p_SELCRIT_rec.ATTRIBUTE10
,p_ATTRIBUTE11 => p_SELCRIT_rec.ATTRIBUTE11
,p_ATTRIBUTE12 => p_SELCRIT_rec.ATTRIBUTE12
,p_ATTRIBUTE13 => p_SELCRIT_rec.ATTRIBUTE13
,p_ATTRIBUTE14 => p_SELCRIT_rec.ATTRIBUTE14
,p_ATTRIBUTE15 => p_SELCRIT_rec.ATTRIBUTE15
); --
End Update_selcrit;
PROCEDURE Delete_selcrit(
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,
P_Identity_Resource_Id IN NUMBER,
P_SELCRIT_Rec IN PV_RULE_RECTYPE_PUB.SELCRIT_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) := 'Delete_selcrit';
select attr_value_id from pv_selected_attr_values
where selection_criteria_id = pc_criteria_id;
SAVEPOINT DELETE_SELCRIT_PVT;
open lc_value_rows (pc_criteria_id => p_SELCRIT_rec.SELECTION_CRITERIA_ID);
PV_selattval_PVT.Delete_selattval(
P_Api_Version_Number => 2.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => p_commit,
P_Validation_Level => p_Validation_Level,
P_Identity_Resource_Id => P_Identity_Resource_Id,
P_SELATTVAL_Rec => l_SELATTVAL_Rec,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
'Private API: Calling PV_ENTY_SELECT_CRITERIA_PKG.Delete_Row');
PV_ENTY_SELECT_CRITERIA_PKG.Delete_Row(
p_SELECTION_CRITERIA_ID => p_SELCRIT_rec.SELECTION_CRITERIA_ID);
End Delete_selcrit;
PROCEDURE Validate_SELECTION_CRITERIA_ID (
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Validation_mode IN VARCHAR2,
P_SELECTION_CRITERIA_ID IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_selection_criteria_id_exists (pc_selection_criteria_id NUMBER) IS
SELECT 'X'
FROM pv_enty_select_criteria
WHERE selection_criteria_id = pc_selection_criteria_id;
IF (p_selection_criteria_id IS NOT NULL) AND
(p_selection_criteria_id <> FND_API.G_MISS_NUM)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_INVALID_ID',
p_token1 => 'selection_criteria_id',
p_token1_value => p_selection_criteria_id);
ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
THEN
-- validate NOT NULL column
IF (p_selection_criteria_id IS NULL) OR
(p_selection_criteria_id = FND_API.G_MISS_NUM)
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_MISSING_LEAD_ID');
OPEN C_selection_criteria_id_exists (p_selection_criteria_id);
FETCH C_selection_criteria_id_exists into l_val;
IF C_selection_criteria_id_exists%NOTFOUND
THEN
AS_UTILITY_PVT.Set_Message(
p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
p_msg_name => 'API_INVALID_ID',
p_token1 => 'selection_criteria_id',
p_token1_value => p_selection_criteria_id );
CLOSE C_selection_criteria_id_exists;
END Validate_SELECTION_CRITERIA_ID;
select rule.process_rule_name, attr.name
from
pv_process_rules_vl rule, pv_attributes_vl attr, pv_enty_select_criteria crit
where
crit.process_rule_id = pc_rule_id
and crit.attribute_id = pc_attribute_id
and crit.selection_type_code IN ('INPUT_FILTER', 'MONITOR_SCOPE')
and crit.process_rule_id = rule.process_rule_id
and crit.attribute_id = attr.attribute_id;
select lookup_code
from pv_lookups where lookup_type = 'PV_TIE_BREAKING_OPERATOR'
and lookup_code = pc_lookup_code;
Validate_SELECTION_CRITERIA_ID(
p_init_msg_list => FND_API.G_FALSE,
p_validation_mode => p_validation_mode,
p_SELECTION_CRITERIA_ID => P_SELCRIT_Rec.SELECTION_CRITERIA_ID,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_TABLE_NAME => 'PV_ENTY_SELECT_CRITERIA',
p_COLUMN_NAME => 'SELECTION_TYPE_CODE',
p_lookup_type => 'PV_SELECTION_TYPE_CODE',
p_lookup_code => P_SELCRIT_Rec.SELECTION_TYPE_CODE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
if p_SELCRIT_REC.SELECTION_TYPE_CODE = 'TIE_BREAKING' then
begin
if p_SELCRIT_REC.rank is null then
FND_MESSAGE.Set_Name('PV', 'PV_NULLCHECK_TIEBR_RANK');
select rank into l_rank from pv_enty_select_criteria
where process_rule_id = p_SELCRIT_REC.process_rule_id and
rank = p_SELCRIT_REC.rank;
if p_SELCRIT_REC.SELECTION_TYPE_CODE = 'TIE_BREAKING' then
begin
select attribute_id into l_attribute_id from pv_enty_select_criteria
where process_rule_id = p_SELCRIT_REC.process_rule_id and
attribute_id = p_SELCRIT_REC.attribute_id;
if p_SELCRIT_REC.SELECTION_TYPE_CODE = 'TIE_BREAKING' then
open lc_tie_break_operator( pc_lookup_code => p_SELCRIT_REC.OPERATOR);
p_token1_value => 'PV_ENTY_SELECT_CRITERIA',
p_token2 => 'COLUMN_NAME',
p_token2_value => 'OPERATOR',
p_token3 => 'LOOKUP_TYPE',
p_token3_value => 'PV_TIE_BREAKING_OPERATOR',
p_token4 => 'LOOKUP_CODE',
p_token4_value => p_SELCRIT_REC.OPERATOR);
p_TABLE_NAME => 'PV_ENTY_SELECT_CRITERIA',
p_COLUMN_NAME => 'OPERATOR',
p_attribute_id => P_SELCRIT_REC.ATTRIBUTE_ID,
p_OPERATOR_CODE => P_SELCRIT_Rec.OPERATOR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_SELCRIT_REC.SELECTION_TYPE_CODE IN ('INPUT_FILTER', 'MONITOR_SCOPE') then
open lc_chk_input_filter_dups (pc_rule_id => p_SELCRIT_REC.process_rule_id,
pc_attribute_id => p_SELCRIT_REC.attribute_id);