The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_rec.action_used_by_id
AND arc_action_used_by = p_action_rec.arc_action_used_by
AND list_select_action_id <> p_action_rec.list_select_action_id
AND incl_object_id = p_action_rec.incl_object_id
AND arc_incl_object_from = p_action_rec.arc_incl_object_from;
SELECT Count(*)
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_rec.action_used_by_id
AND arc_action_used_by = p_action_rec.arc_action_used_by
AND list_select_action_id <> p_action_rec.list_select_action_id
AND order_number = p_action_rec.order_number;
SELECT count(*)
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_rec.action_used_by_id
AND arc_action_used_by = p_action_rec.arc_action_used_by
AND list_select_action_id <> p_action_rec.list_select_action_id;
SELECT nvl(Min(order_number),-1)
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_rec.action_used_by_id
AND arc_action_used_by = p_action_rec.arc_action_used_by
AND list_select_action_id <> p_action_rec.list_select_action_id;
Cursor C_Min_List_Selection_Type IS
SELECT list_action_type
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_rec.action_used_by_id
AND arc_action_used_by = p_action_rec.arc_action_used_by
and order_number = l_min_order;
OPEN C_Min_List_Selection_Type;
FETCH C_Min_List_Selection_Type INTO l_action_type;
CLOSE C_Min_List_Selection_Type;
AND p_action_rec.list_select_action_id IS NOT NULL
THEN
IF AMS_Utility_PVT.check_uniqueness(
'ams_list_select_actions',
'list_select_action_id = ' || p_action_rec.list_select_action_id
) = FND_API.g_false
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_DUPE_ACTION');
'ams_list_select_actions',
'order_number = ' || p_action_rec.order_number||
' and action_used_by_id = '||p_action_rec.action_used_by_id
||' and arc_action_used_by = '||p_action_rec.arc_action_used_by
) = FND_API.g_false
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_BAD_ORD_NUM');
'ams_list_select_actions',
'rank = ' || p_action_rec.rank||
' and action_used_by_id = '||
p_action_rec.action_used_by_id
||' and arc_action_used_by = '||
p_action_rec.arc_action_used_by
) = FND_API.g_false
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_RANK_NUM');
p_lookup_type => 'AMS_SELECT_ACTION_USED_BY',
p_lookup_code => p_action_rec.arc_action_used_by
) = FND_API.g_false
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACTION_USEDBY_INVALID');
p_lookup_type => 'AMS_LIST_SELECT_ACTION',
p_lookup_code => p_action_rec.list_action_type
) = FND_API.g_false
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_BAD_TYPE');
p_lookup_type => 'AMS_LIST_SELECT_TYPE',
p_lookup_code => p_action_rec.arc_incl_object_from
) = FND_API.g_false
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_SRC_INVALID');
l_list_select_action_id AMS_LIST_SELECT_ACTIONS.list_select_action_id%TYPE;
SELECT ams_list_select_actions_s.NEXTVAL
FROM DUAL;
SELECT COUNT(*)
FROM ams_list_select_actions
WHERE list_select_action_id = action_id;
IF l_action_rec.list_select_action_id IS NULL OR
l_action_rec.list_select_action_id = FND_API.g_miss_num THEN
LOOP
OPEN c_action_seq;
FETCH c_action_seq INTO l_action_rec.list_select_action_id;
OPEN c_action_count(l_action_rec.list_select_action_id);
INSERT into AMS_LIST_SELECT_ACTIONS
(LIST_SELECT_ACTION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,ORDER_NUMBER
,LIST_ACTION_TYPE
--,INCL_OBJECT_NAME
,ARC_INCL_OBJECT_FROM
,INCL_OBJECT_ID
--,INCL_OBJECT_WB_SHEET
--,INCL_OBJECT_WB_OWNER
--,INCL_OBJECT_CELL_CODE
,RANK
,NO_OF_ROWS_AVAILABLE
,NO_OF_ROWS_REQUESTED
,NO_OF_ROWS_USED
,DISTRIBUTION_PCT
,RESULT_TEXT
,DESCRIPTION
,ARC_ACTION_USED_BY
,ACTION_USED_BY_ID
, incl_control_group
,NO_OF_ROWS_TARGETED
)
VALUES
( l_action_rec.list_select_action_id
-- standard who columns
,sysdate
,FND_GLOBAL.User_Id
,sysdate
,FND_GLOBAL.User_Id
,FND_GLOBAL.Conc_Login_Id
,1--object_version_number
,l_action_rec.order_number
,l_action_rec.list_action_type
-- ,l_action_rec.incl_object_name
,l_action_rec.arc_incl_object_from
,l_action_rec.incl_object_id
-- ,l_action_rec.incl_object_wb_sheet
-- ,l_action_rec.incl_object_wb_owner
-- ,l_action_rec.incl_object_cell_code
,l_action_rec.rank
,NVL(l_action_rec.no_of_rows_available,0)
,NVL(l_action_rec.no_of_rows_requested,0)
,NVL(l_action_rec.no_of_rows_used,0)
,l_action_rec.distribution_pct
,l_action_rec.result_text
,l_action_rec.description
,l_action_rec.arc_action_used_by
,l_action_rec.action_used_by_id
,l_action_rec.incl_control_group
,l_action_rec.no_of_rows_targeted
);
x_action_id := l_action_rec.list_select_action_id;
AMS_DM_MODEL_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
AMS_DM_SCORE_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
PROCEDURE Update_ListAction
( p_api_version 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_action_rec IN action_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_ListAction';
SAVEPOINT Update_ListAction_PVT;
FND_MESSAGE.Set_Token('ROW','AMS_ListAction_PVT.Update_ListAction: Start', TRUE);
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status);
p_log_used_by_id => TO_CHAR(l_action_rec.list_select_action_id),
p_msg_data => G_PKG_NAME || ' - Update ams_list_actions'
);
UPDATE ams_list_select_actions
SET
last_update_date = sysdate
,last_updated_by = FND_GLOBAL.User_Id
,last_update_login = FND_GLOBAL.Conc_Login_Id
,object_version_number = l_action_rec.object_version_number + 1
,order_number = l_action_rec.order_number
,list_action_type = l_action_rec.list_action_type
-- ,incl_object_name = l_action_rec.incl_object_name
,arc_incl_object_from = l_action_rec.arc_incl_object_from
,incl_object_id = l_action_rec.incl_object_id
-- ,incl_object_wb_sheet = l_action_rec.incl_object_wb_sheet
-- ,incl_object_wb_owner = l_action_rec.incl_object_wb_owner
,rank = l_action_rec.rank
,no_of_rows_available = l_action_rec.no_of_rows_available
,no_of_rows_requested = l_action_rec.no_of_rows_requested
,no_of_rows_used = l_action_rec.no_of_rows_used
,distribution_pct = l_action_rec.distribution_pct
,result_text = l_action_rec.result_text
,description = l_action_rec.description
,incl_control_group = l_action_rec.incl_control_group
,no_of_rows_targeted = l_action_rec.no_of_rows_targeted
WHERE
list_select_action_id = l_action_rec.list_select_action_id
AND
object_version_number = l_action_rec.object_version_number;
FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Update_ListAction API', TRUE);
AMS_DM_MODEL_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
AMS_DM_SCORE_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Update_ListAction', TRUE);
FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Update_ListAction: END', TRUE);
ROLLBACK TO Update_ListAction_PVT;
ROLLBACK TO Update_ListAction_PVT;
ROLLBACK TO Update_ListAction_PVT;
End Update_ListAction;
PROCEDURE Delete_ListAction
( p_api_version 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_action_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_ListAction';
SAVEPOINT Delete_ListAction_PVT;
FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Delete_ListAction: Start', TRUE);
/* Delete from ams_list_entries
where list_select_action_id = p_action_id;
l_init_action_rec.list_select_action_id := p_action_id;
DELETE FROM ams_list_select_actions
WHERE list_select_action_id = p_action_id;
DELETE FROM ams_list_select_actions
WHERE list_select_action_id = p_action_id;
AMS_DM_MODEL_PVT.handle_data_selection_changes(l_complete_action_rec.action_used_by_id);
/* call the plugin to update the model status ??*/
DELETE FROM ams_list_select_actions
WHERE list_select_action_id = p_action_id;
AMS_DM_SCORE_PVT.handle_data_selection_changes(l_complete_action_rec.action_used_by_id);
FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Delete_ListAction', TRUE);
FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Delete_ListAction: END', TRUE);
ROLLBACK TO Delete_ListAction_PVT;
ROLLBACK TO Delete_ListAction_PVT;
ROLLBACK TO Delete_ListAction_PVT;
END Delete_ListAction;
SELECT list_select_action_id
FROM ams_list_select_actions
WHERE list_select_action_id = p_action_id
AND object_version_number = p_object_version
FOR UPDATE OF list_select_action_id NOWAIT;
l_list_select_action_id number;
FETCH c_list_actions INTO l_list_select_action_id;
x_action_rec.list_select_action_id := FND_API.g_miss_num;
x_action_rec.last_update_date := FND_API.g_miss_date;
x_action_rec.last_updated_by := FND_API.g_miss_num;
x_action_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_list_select_actions
WHERE list_select_action_id = p_action_rec.list_select_action_id;
IF p_action_rec.list_select_action_id = FND_API.g_miss_num THEN
x_complete_rec.list_select_action_id := l_action_rec.list_select_action_id;
IF p_action_rec.last_update_date = FND_API.g_miss_date THEN
x_complete_rec.last_update_date := l_action_rec.last_update_date;
IF p_action_rec.last_updated_by = FND_API.g_miss_num THEN
x_complete_rec.last_updated_by := l_action_rec.last_updated_by;
IF p_action_rec.last_update_login = FND_API.g_miss_num THEN
x_complete_rec.last_update_login := l_action_rec.last_update_login;
SELECT *
FROM ams_list_select_actions
WHERE list_select_action_id = p_action_rec.list_select_action_id;