The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_dummy
FROM IMC_RECENT_ACCESSED_OBJ
WHERE USER_ID = p_user_id
-- AND OBJECT_TYPE = p_object_type
AND OBJECT_ID = p_object_id
-- AND OBJECT_NAME = p_object_name
AND ROWNUM = 1;
PROCEDURE Update_Record (
p_old_access_id IN IMC_RECENT_ACCESSED_OBJ.access_id%TYPE,
p_new_access_id IN IMC_RECENT_ACCESSED_OBJ.access_id%TYPE,
p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
p_object_type IN IMC_RECENT_ACCESSED_OBJ.object_type%TYPE,
p_object_id IN IMC_RECENT_ACCESSED_OBJ.object_id%TYPE,
p_object_name IN IMC_RECENT_ACCESSED_OBJ.object_name%TYPE,
p_application_id IN IMC_RECENT_ACCESSED_OBJ.application_id%TYPE,
p_date_created IN IMC_RECENT_ACCESSED_OBJ.date_created%TYPE,
p_additional_value1 IN IMC_RECENT_ACCESSED_OBJ.additional_value1%TYPE,
p_additional_value2 IN IMC_RECENT_ACCESSED_OBJ.additional_value2%TYPE,
p_additional_value3 IN IMC_RECENT_ACCESSED_OBJ.additional_value3%TYPE,
p_additional_value4 IN IMC_RECENT_ACCESSED_OBJ.additional_value4%TYPE,
p_additional_value5 IN IMC_RECENT_ACCESSED_OBJ.additional_value5%TYPE,
p_object_version_number IN IMC_RECENT_ACCESSED_OBJ.object_version_number%TYPE,
p_created_by IN IMC_RECENT_ACCESSED_OBJ.created_by%TYPE,
p_creation_date IN IMC_RECENT_ACCESSED_OBJ.creation_date%TYPE,
p_last_updated_by IN IMC_RECENT_ACCESSED_OBJ.last_updated_by%TYPE,
p_last_update_date IN IMC_RECENT_ACCESSED_OBJ.last_update_date%TYPE,
p_last_update_login IN IMC_RECENT_ACCESSED_OBJ.last_update_login%TYPE
) AS
BEGIN
UPDATE IMC_RECENT_ACCESSED_OBJ SET
ACCESS_ID = p_new_access_id,
OBJECT_TYPE = p_object_type,
OBJECT_ID = p_object_id,
OBJECT_NAME = DECODE(p_object_name, FND_API.G_MISS_CHAR, NULL, p_object_name),
APPLICATION_ID = DECODE(p_application_id, FND_API.G_MISS_NUM, NULL, p_application_id),
DATE_CREATED = p_date_created,
ADDITIONAL_VALUE1 = DECODE(p_additional_value1, FND_API.G_MISS_CHAR, NULL, p_additional_value1),
ADDITIONAL_VALUE2 = DECODE(p_additional_value2, FND_API.G_MISS_CHAR, NULL, p_additional_value2),
ADDITIONAL_VALUE3 = DECODE(p_additional_value3, FND_API.G_MISS_CHAR, NULL, p_additional_value3),
ADDITIONAL_VALUE4 = DECODE(p_additional_value4, FND_API.G_MISS_CHAR, NULL, p_additional_value4),
ADDITIONAL_VALUE5 = DECODE(p_additional_value5, FND_API.G_MISS_CHAR, NULL, p_additional_value5),
OBJECT_VERSION_NUMBER = p_object_version_number,
CREATED_BY = p_created_by,
CREATION_DATE = p_creation_date,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATE_LOGIN = p_last_update_login
WHERE ACCESS_ID = p_old_access_id;
END Update_Record;
PROCEDURE Insert_Record (
p_access_id IN IMC_RECENT_ACCESSED_OBJ.access_id%TYPE,
p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
p_object_type IN IMC_RECENT_ACCESSED_OBJ.object_type%TYPE,
p_object_id IN IMC_RECENT_ACCESSED_OBJ.object_id%TYPE,
p_object_name IN IMC_RECENT_ACCESSED_OBJ.object_name%TYPE,
p_application_id IN IMC_RECENT_ACCESSED_OBJ.application_id%TYPE,
p_date_created IN IMC_RECENT_ACCESSED_OBJ.date_created%TYPE,
p_additional_value1 IN IMC_RECENT_ACCESSED_OBJ.additional_value1%TYPE,
p_additional_value2 IN IMC_RECENT_ACCESSED_OBJ.additional_value2%TYPE,
p_additional_value3 IN IMC_RECENT_ACCESSED_OBJ.additional_value3%TYPE,
p_additional_value4 IN IMC_RECENT_ACCESSED_OBJ.additional_value4%TYPE,
p_additional_value5 IN IMC_RECENT_ACCESSED_OBJ.additional_value5%TYPE,
p_object_version_number IN IMC_RECENT_ACCESSED_OBJ.object_version_number%TYPE,
p_created_by IN IMC_RECENT_ACCESSED_OBJ.created_by%TYPE,
p_creation_date IN IMC_RECENT_ACCESSED_OBJ.creation_date%TYPE,
p_last_updated_by IN IMC_RECENT_ACCESSED_OBJ.last_updated_by%TYPE,
p_last_update_date IN IMC_RECENT_ACCESSED_OBJ.last_update_date%TYPE,
p_last_update_login IN IMC_RECENT_ACCESSED_OBJ.last_update_login%TYPE
) AS
BEGIN
INSERT INTO IMC_RECENT_ACCESSED_OBJ (
ACCESS_ID,
USER_ID,
OBJECT_TYPE,
OBJECT_ID,
OBJECT_NAME,
APPLICATION_ID,
DATE_CREATED,
ADDITIONAL_VALUE1,
ADDITIONAL_VALUE2,
ADDITIONAL_VALUE3,
ADDITIONAL_VALUE4,
ADDITIONAL_VALUE5,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
p_access_id,
p_user_id,
p_object_type,
p_object_id,
DECODE(p_object_name, FND_API.G_MISS_CHAR, NULL, p_object_name),
DECODE(p_application_id, FND_API.G_MISS_NUM, NULL, p_application_id),
p_date_created,
DECODE(p_additional_value1, FND_API.G_MISS_CHAR, NULL, p_additional_value1),
DECODE(p_additional_value2, FND_API.G_MISS_CHAR, NULL, p_additional_value2),
DECODE(p_additional_value3, FND_API.G_MISS_CHAR, NULL, p_additional_value3),
DECODE(p_additional_value4, FND_API.G_MISS_CHAR, NULL, p_additional_value4),
DECODE(p_additional_value5, FND_API.G_MISS_CHAR, NULL, p_additional_value5),
p_object_version_number,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_update_login
);
END Insert_Record;
SELECT *
FROM IMC_RECENT_ACCESSED_OBJ
WHERE USER_ID = p_user_id
ORDER BY access_id;
l_last_update_login IMC_RECENT_ACCESSED_OBJ.last_update_login%TYPE;
/* Record exists; Update details of access. */
SELECT access_id
INTO l_old_access_id
FROM IMC_RECENT_ACCESSED_OBJ
WHERE USER_ID = p_user_id
-- AND OBJECT_TYPE = p_object_type
AND OBJECT_ID = p_object_id
AND ROWNUM = 1;
SELECT IMC_RECENT_ACCESSED_OBJ_S.NEXTVAL INTO l_new_access_id FROM DUAL;
/* init last_update_login */
IF (FND_GLOBAL.conc_login_id = -1) OR (FND_GLOBAL.conc_login_id IS NULL) THEN
l_last_update_login := FND_GLOBAL.login_id;
l_last_update_login := FND_GLOBAL.conc_login_id;
Update_Record (
l_old_access_id,
l_new_access_id,
p_user_id,
p_object_type,
p_object_id,
p_object_name,
p_application_id,
SYSDATE,
p_additional_value1,
p_additional_value2,
p_additional_value3,
p_additional_value4,
p_additional_value5,
l_object_version_number,
nvl(FND_GLOBAL.user_id, -1), /* Created by */
SYSDATE, /* Creation date */
nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
SYSDATE, /* Last update date */
l_last_update_login /* Last update login */
);
SELECT IMC_RECENT_ACCESSED_OBJ_S.NEXTVAL INTO l_new_access_id FROM DUAL;
/* init last_update_login */
IF (FND_GLOBAL.conc_login_id = -1) OR (FND_GLOBAL.conc_login_id IS NULL) THEN
l_last_update_login := FND_GLOBAL.login_id;
l_last_update_login := FND_GLOBAL.conc_login_id;
SELECT count(*)
INTO l_curr_count
FROM IMC_RECENT_ACCESSED_OBJ
WHERE USER_ID = p_user_id;
Insert_Record (
l_new_access_id,
p_user_id,
p_object_type,
p_object_id,
p_object_name,
p_application_id,
SYSDATE,
p_additional_value1,
p_additional_value2,
p_additional_value3,
p_additional_value4,
p_additional_value5,
l_object_version_number,
nvl(FND_GLOBAL.user_id, -1), /* Created by */
SYSDATE, /* Creation date */
nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
SYSDATE, /* Last update date */
l_last_update_login /* Last update login */
);
Update_Record (
l_old_access_id,
l_new_access_id,
p_user_id,
p_object_type,
p_object_id,
p_object_name,
p_application_id,
SYSDATE,
p_additional_value1,
p_additional_value2,
p_additional_value3,
p_additional_value4,
p_additional_value5,
l_object_version_number,
nvl(FND_GLOBAL.user_id, -1), /* Created by */
SYSDATE, /* Creation date */
nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
SYSDATE, /* Last update date */
l_last_update_login /* Last update login */
);
Insert_Record (
l_new_access_id,
p_user_id,
p_object_type,
p_object_id,
p_object_name,
p_application_id,
SYSDATE,
p_additional_value1,
p_additional_value2,
p_additional_value3,
p_additional_value4,
p_additional_value5,
l_object_version_number,
nvl(FND_GLOBAL.user_id, -1), /* Created by */
SYSDATE, /* Creation date */
nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
SYSDATE, /* Last update date */
l_last_update_login /* Last update login */
);
l_query := 'SELECT object_type, object_id, object_name, date_created ' ||
'FROM IMC_RECENT_ACCESSED_OBJ ';
SELECT *
FROM IMC_RECENT_ACCESSED_OBJ
WHERE USER_ID = p_user_id
ORDER BY access_id;
l_num_to_delete NUMBER;
SELECT count(*)
INTO l_curr_count
FROM IMC_RECENT_ACCESSED_OBJ
WHERE USER_ID = p_user_id;
l_num_to_delete := l_curr_count - l_max_records;
WHILE x_flush_count < l_num_to_delete LOOP
FETCH records_for_this_user INTO l_user_record;
DELETE FROM IMC_RECENT_ACCESSED_OBJ
WHERE ACCESS_ID = l_user_record.access_id;
FND_MESSAGE.SET_NAME('IMC', g_could_not_delete_entry);