The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_act_categories_s.NEXTVAL
FROM dual;
INSERT INTO AMS_ACT_CATEGORIES
(activity_category_id,
-- standard who columns
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
-- other columns
object_version_number,
act_category_used_by_id,
arc_act_category_used_by,
category_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
(
l_act_category_rec.activity_category_id,
-- standard who columns
sysdate,
FND_GLOBAL.User_Id,
sysdate,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
1, -- object_version_number
l_act_category_rec.act_category_used_by_id,
l_act_category_rec.arc_act_category_used_by,
l_act_category_rec.category_id,
l_act_category_rec.attribute_category,
l_act_category_rec.attribute1,
l_act_category_rec.attribute2,
l_act_category_rec.attribute3,
l_act_category_rec.attribute4,
l_act_category_rec.attribute5,
l_act_category_rec.attribute6,
l_act_category_rec.attribute7,
l_act_category_rec.attribute8,
l_act_category_rec.attribute9,
l_act_category_rec.attribute10,
l_act_category_rec.attribute11,
l_act_category_rec.attribute12,
l_act_category_rec.attribute13,
l_act_category_rec.attribute14,
l_act_category_rec.attribute15
);
PROCEDURE Update_Act_Category
( 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_act_category_rec IN act_category_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Act_Category';
SAVEPOINT Update_Act_Category_PVT;
update AMS_ACT_CATEGORIES
set
last_update_date = sysdate
,last_updated_by = FND_GLOBAL.User_Id
,last_update_login = FND_GLOBAL.Conc_Login_Id
,object_version_number = l_act_category_rec.object_version_number+1
,act_category_used_by_id = l_act_category_rec.act_category_used_by_id
,arc_act_category_used_by = l_act_category_rec.arc_act_category_used_by
,category_id = l_act_category_rec.activity_category_id
,attribute_category = l_act_category_rec.attribute_category
,attribute1 = l_act_category_rec.attribute1
,attribute2 = l_act_category_rec.attribute2
,attribute3 = l_act_category_rec.attribute3
,attribute4 = l_act_category_rec.attribute4
,attribute5 = l_act_category_rec.attribute5
,attribute6 = l_act_category_rec.attribute6
,attribute7 = l_act_category_rec.attribute7
,attribute8 = l_act_category_rec.attribute8
,attribute9 = l_act_category_rec.attribute9
,attribute10 = l_act_category_rec.attribute10
,attribute11 = l_act_category_rec.attribute11
,attribute12 = l_act_category_rec.attribute12
,attribute13 = l_act_category_rec.attribute13
,attribute14 = l_act_category_rec.attribute14
,attribute15 = l_act_category_rec.attribute15
where activity_category_id = l_act_category_rec.activity_category_id
and object_version_number = l_act_category_rec.object_version_number;
ROLLBACK TO Update_Act_Category_PVT;
ROLLBACK TO Update_Act_Category_PVT;
ROLLBACK TO Update_Act_Category_PVT;
END Update_Act_Category;
PROCEDURE Delete_Act_Category
( 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_act_category_id IN NUMBER,
p_object_version IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Act_Category';
SAVEPOINT Delete_Act_Category_PVT;
-- Delete header data
DELETE FROM AMS_ACT_CATEGORIES
WHERE activity_category_id = l_act_category_id
and object_version_number = p_object_version;
ROLLBACK TO Delete_Act_Category_PVT;
ROLLBACK TO Delete_Act_Category_PVT;
ROLLBACK TO Delete_Act_Category_PVT;
END Delete_Act_Category;
SELECT activity_category_id
FROM AMS_ACT_CATEGORIES
WHERE activity_category_id = p_act_category_id
and object_version_number = p_object_version
FOR UPDATE of activity_category_id NOWAIT;
SELECT 1 from dual WHERE EXISTS(select 1 FROM AMS_ACT_CATEGORIES
WHERE category_id = ctg_id_in
and ARC_ACT_CATEGORY_USED_BY = arc_used_by
and ACT_CATEGORY_USED_BY_ID = arc_used_id_in);
SELECT 1 from dual WHERE EXISTS(select 1 FROM AMS_ACT_CATEGORIES
WHERE category_id = ctg_id_in
and object_version_number = obj_ver_in
and ARC_ACT_CATEGORY_USED_BY = arc_used_by
and ACT_CATEGORY_USED_BY_ID = arc_used_id_in);
SELECT *
FROM ams_act_categories
WHERE activity_category_id = p_act_category_rec.activity_category_id;
PROCEDURE Unit_Test_Insert
IS
-- local variables
l_act_category_rec AMS_CATEGORIES_VL%ROWTYPE;
END Unit_Test_Insert;
PROCEDURE Unit_Test_Delete
IS
-- local variables
l_category_rec AMS_CATEGORIES_VL%ROWTYPE;
AMS_ActCategory_PVT.Delete_Category (
p_api_version => 1.0 -- p_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_category_rec => l_category_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END Unit_Test_Delete;
PROCEDURE Unit_Test_Update
IS
-- local variables
l_category_rec AMS_CATEGORIES_VL%ROWTYPE;
select *
from AMS_CATEGORIES_VL
WHERE CATEGORY_ID = my_category_id;
l_category_rec.NOTES := 'NOTES UPDATED1';
AMS_ActCategory_PVT.Update_Category (
p_api_version => 1.0 -- p_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_category_rec => l_category_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END Unit_Test_Update;
select * from AMS_CATEGORIES_B WHERE CATEGORY_ID = my_category_id;
PROCEDURE Unit_Test_Act_Insert
is
-- local variables
l_act_category_rec act_category_rec_type;
END Unit_Test_Act_Insert;
PROCEDURE Unit_Test_Act_Delete
is
-- local variables
l_act_category_rec act_category_rec_type;
AMS_ActCategory_PVT.Delete_Act_Category (
p_api_version => 1.0 -- p_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_category_rec => l_act_category_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END Unit_Test_Act_Delete;
PROCEDURE Unit_Test_Act_Update
is
-- local variables
l_act_category_rec act_category_rec_type;
SELECT *
FROM AMS_ACT_CATEGORIES
WHERE ACTIVITY_CATEGORY_ID = my_act_category_id;
l_act_category_rec.ATTRIBUTE1 := 'ATTRIBUTE1 UPDATED1';
AMS_ActCategory_PVT.Update_Act_Category (
p_api_version => 1.0 -- p_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_category_rec => l_act_category_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END Unit_Test_Act_Update;