Course Delete API in OLM (ota_activity_version_api)

By | November 19, 2014

Course Delete API in OLM (ota_activity_version_api)

Purpose of this API is to delete the courses in OLM as Course Cataoge Objects. First API removes the inclusion of a course in one or more categories and then its removes this course.

l_activity_version_id number;
l_act_vers_usages_del_flag number:= 1;
–Get all courses that are to be deleted
cursor c1 is
select activity_version_id,OBJECT_VERSION_NUMBER,version_name from ota_activity_versions
where version_name in
(select version_name from lsg_course_details_temp)

–get inclusion information

cursor c2 is
select CATEGORY_USAGE_ID,object_version_number from ota_act_cat_inclusions
where activity_version_id = l_activity_version_id;

for r in c1 loop
l_activity_version_id := null;
l_act_vers_usages_del_flag := 1;
l_activity_version_id := r.activity_version_id;

–deleting the inclusions of a course in one or multiple categories
for r2 in c2 loop

( p_activity_version_id =>r.activity_version_id,
p_category_usage_id =>r2.category_usage_id,
p_object_version_number =>r2.object_version_number,
p_validate =>FALSE
) ;

end loop;
dbms_output.put_line(‘Activity Version ID ‘||r.activity_version_id || ‘ Inclusion Deleted’);
exception when others then
l_act_vers_usages_del_flag := 0;
dbms_output.put_line(‘Error in Deleting Inclusion for Activity Version ID ‘||r.activity_version_id ||’ Name ‘||r.version_name||’ EERM ‘||SQLERRM);

if l_act_vers_usages_del_flag =1 then
ota_activity_version_api.delete_activity_version (

p_activity_version_id => r.activity_version_id,
p_object_version_number => r.OBJECT_VERSION_NUMBER,
p_validate => FALSE
) ;

dbms_output.put_line(‘Activity Version ID ‘||r.activity_version_id ||’ Name ‘||r.version_name ||’ Deleted’);
end if;
exception when others then
dbms_output.put_line(‘Exception For Activity Version ID ‘||r.activity_version_id || ‘ Name ‘||r.version_name||’ SQLERRM ‘||SQLERRM);
end loop;

exception when others then dbms_output.put_line(‘Main Exception ‘||SQLERRM);



For More Technical Topics, Click Here