The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION seed_needs_update(
l_user_status_rec IN User_Status_Rec_Type
) RETURN VARCHAR2; -- FND_API.g_true/g_false
SELECT ams_user_statuses_b_s.NEXTVAL
FROM dual;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM ams_user_statuses_vl
WHERE user_status_id = x_id);
AMS_Utility_PVT.debug_message (l_full_name || ': Insert');
INSERT INTO ams_user_statuses_b (
user_status_id,
-- standard who columns
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
system_status_type,
system_status_code,
enabled_flag,
default_flag,
seeded_flag,
start_date_active,
end_date_active,
application_id
)
VALUES (
l_user_status_rec.user_status_id,
-- standard who columns
SYSDATE,
FND_GLOBAL.User_Id,
SYSDATE,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
1, -- object_version_number
l_user_status_rec.system_status_type,
l_user_status_rec.system_status_code,
NVL (l_user_status_rec.enabled_flag, 'Y'), -- Default is 'Y'
NVL (l_user_status_rec.default_flag, 'N'), -- Default is 'N'
NVL (l_user_status_rec.seeded_flag, 'N'), -- Default is 'N'
l_user_status_rec.start_date_active,
l_user_status_rec.end_date_active,
fnd_global.resp_appl_id -- added to capture application_id
);
INSERT INTO ams_user_statuses_tl (
user_status_id,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
name,
description
)
SELECT l_user_status_rec.user_status_id,
l.language_code,
-- standard who columns
SYSDATE,
FND_GLOBAL.User_Id,
SYSDATE,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
USERENV('LANG'),
l_user_status_rec.name,
l_user_status_rec.description
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS (SELECT NULL
FROM ams_user_statuses_tl t
WHERE t.user_status_id = l_user_status_rec.user_status_id
AND t.language = l.language_code);
PROCEDURE Update_User_Status (
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_user_status_rec IN User_Status_Rec_Type
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_User_Status';
SAVEPOINT Update_User_Status;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
UPDATE ams_user_statuses_b
SET
default_flag = 'N'
WHERE system_status_type = l_user_status_rec.system_status_type
AND system_status_code = l_user_status_rec.system_status_code;
seed_needs_update(l_user_status_rec) = FND_API.g_true THEN
-------------------------- update --------------------
IF (AMS_DEBUG_HIGH_ON) THEN
AMS_Utility_PVT.debug_message (l_full_name || ': Update');
UPDATE ams_user_statuses_b
SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_Id,
last_update_login = FND_GLOBAL.Conc_Login_Id,
object_version_number = object_version_number + 1,
system_status_type = l_user_status_rec.system_status_type,
system_status_code = l_user_status_rec.system_status_code,
enabled_flag = NVL (l_user_status_rec.enabled_flag, 'Y'),
default_flag = NVL (l_user_status_rec.default_flag, 'N'),
seeded_flag = NVL (l_user_status_rec.seeded_flag, 'N'),
start_date_active = l_user_status_rec.start_date_active,
end_date_active = l_user_status_rec.end_date_active
WHERE user_status_id = l_user_status_rec.user_status_id
AND object_version_number = l_user_status_rec.object_version_number;
UPDATE ams_user_statuses_tl
SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_Id,
last_update_login = FND_GLOBAL.Conc_Login_Id,
source_lang = USERENV('LANG'),
name = l_user_status_rec.name,
description = l_user_status_rec.description
WHERE user_status_id = l_user_status_rec.user_status_id
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
ROLLBACK TO Update_User_Status;
ROLLBACK TO Update_User_Status;
ROLLBACK TO Update_User_Status;
END Update_User_Status;
PROCEDURE Delete_User_Status (
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_user_status_id IN NUMBER,
p_object_version IN NUMBER
)
IS
CURSOR c_user_status IS
SELECT *
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_User_Status';
SAVEPOINT Delete_User_Status;
AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
DELETE FROM ams_user_statuses_tl
WHERE user_status_id = p_user_status_id;
DELETE FROM ams_user_statuses_b
WHERE user_status_id = p_user_status_id
AND object_version_number = p_object_version;
ROLLBACK TO Delete_User_Status;
ROLLBACK TO Delete_User_Status;
ROLLBACK TO Delete_User_Status;
END Delete_User_Status;
SELECT object_version_number
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
x_user_status_rec.last_update_date := FND_API.g_miss_date;
x_user_status_rec.last_updated_by := FND_API.g_miss_num;
x_user_status_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_rec.user_status_id;
select 1 into l_count
from ams_user_statuses_vl
where user_status_id = l_user_status_rec.user_status_id
and name = l_user_status_rec.name
and start_date_active = l_user_status_rec.start_date_active
and end_date_active = l_user_status_rec.end_Date_active
and system_status_type = l_user_status_rec.system_status_type
and system_status_code = l_user_status_rec.system_status_code
and seeded_flag = 'Y';
select 1 into l_count
from ams_user_statuses_vl
where user_status_id = l_user_status_rec.user_status_id
and name = l_user_status_rec.name
and start_date_active = l_user_status_rec.start_date_active
and system_status_type = l_user_status_rec.system_status_type
and system_status_code = l_user_status_rec.system_status_code
and seeded_flag = 'Y';
select 1 into l_count
from ams_user_statuses_vl
where user_status_id = l_user_status_rec.user_status_id
and name = l_user_status_rec.name
and system_status_type = l_user_status_rec.system_status_type
and system_status_code = l_user_status_rec.system_status_code
and seeded_flag = 'Y';
FUNCTION seed_needs_update(
l_user_status_rec IN User_Status_Rec_Type
)
RETURN VARCHAR2
IS
l_count NUMBER := 0;
select 1 into l_count
from ams_user_statuses_vl
where user_status_id = l_user_status_rec.user_status_id
and enabled_flag = l_user_status_rec.enabled_flag
and default_flag = l_user_status_rec.default_flag
and seeded_flag = 'Y';
RETURN FND_API.g_true; -- needs update
RETURN FND_API.g_false; -- doesnt need update
END seed_needs_update;
l_stmt := 'SELECT count(1) from '||l_table||' where user_status_id = :b1';
l_stmt := 'SELECT count(1) from '||l_table||' where user_status_id = :b1';