API to Create Course in OLM (ota_activity_category_api)

By | November 19, 2014

API to Upload Courses as Cataloge Objects in OLM

ota_activity_version_api

Purpose of this API is to create the master list of courses in OLM as Course Calaoge Objects. First API Creates Course and API called below includes it under a category.

 

declare

errbuf VARCHAR2(200);
retcode VARCHAR2(200);
p_business_group_id NUMBER:= 83;
p_validate VARCHAR2(10):= ‘FALSE’;
l_activity_version_id NUMBER;
l_object_version_number NUMBER;
l_activity_id NUMBER;
l_error_msg VARCHAR2 (1000);
l_category_usage_id NUMBER;
l_validate BOOLEAN;
l_status VARCHAR2 (40);
l_parent_activity_id NUMBER;

CURSOR c1
IS
SELECT *
FROM lsg_course_details_temp
WHERE 1=1
and (status IS NULL );

BEGIN

FOR i IN c1 LOOP
BEGIN

l_activity_id := NULL;
l_category_usage_id := NULL;
l_activity_version_id := NULL;
l_object_version_number := NULL;
l_error_msg := NULL;

begin
SELECT activity_id, category_usage_id
INTO l_activity_id, l_category_usage_id
FROM ota_activity_definitions
WHERE UPPER (NAME) LIKE UPPER (i.SUB_CATEGORY) || ‘%’;
exception when others then
dbms_output.put_line(‘Category ‘||i.SUB_CATEGORY|| ‘ not found’);
end;

IF p_validate = ‘TRUE’
THEN
l_validate := TRUE;
ELSE
l_validate := FALSE;
END IF;

BEGIN
ota_activity_version_api.create_activity_version

(p_validate => l_validate,
p_effective_date => i.start_date,
p_activity_id => l_activity_id,
p_version_name => initcap(i.course_name),
p_version_code => upper(i.course_code),
p_description => i.course_desc,
p_intended_audience => i.intended_audience,
p_objectives => i.objectives,
p_start_date => NVL
(i.start_date,
’01-JAN-1995′
),
p_business_group_id => p_business_group_id,
p_activity_version_id => l_activity_version_id,
p_object_version_number => l_object_version_number,
p_developer_organization_id => p_business_group_id
);

DBMS_OUTPUT.put_line ( ‘l_activity_version_id:’|| l_activity_version_id||’ Created under Category ‘||i.SUB_CATEGORY);

UPDATE lsg_course_details_temp
SET status = ‘CC’,
error_message = ”
WHERE lineid = i.lineid;
EXCEPTION
WHEN OTHERS  THEN
l_error_msg := l_error_msg || SUBSTR (SQLERRM, 1, 200);

UPDATE lsg_course_details_temp
SET status = ‘Error’,
error_message = ‘CREATING COURSES’ || l_error_msg
WHERE lineid = i.lineid;

DBMS_OUTPUT.put_line ( ‘l_activity_version_id Ex:’|| l_activity_version_id ||’ sqlerrm ‘||sqlerrm
);
END;

l_error_msg := ‘ACTIVITY_VERSION_ID’ || l_activity_version_id;

IF l_activity_version_id IS NOT NULL THEN
BEGIN

ota_activity_category_api.create_act_cat_inclusion

(p_validate => l_validate,
p_effective_date => NVL (i.start_date,
’01-JAN-1995′
),
p_activity_version_id => l_activity_version_id,
p_activity_category => initcap(i.sub_CATEGORY),
p_object_version_number => l_object_version_number,
p_primary_flag => ‘Y’ ,
p_category_usage_id => l_category_usage_id
);

SELECT DECODE (p_validate, ‘TRUE’, ‘Passed’, ‘Uploaded’)
INTO l_status
FROM DUAL;

UPDATE lsg_course_details_temp
SET status = l_status,
error_message = ”
WHERE lineid = i.lineid;

EXCEPTION
WHEN OTHERS
THEN
l_error_msg := l_error_msg || SUBSTR (SQLERRM, 1, 200);

UPDATE lsg_course_details_temp
SET status = ‘Error’,
error_message = ‘INCLUDING COURSES’ || l_error_msg
WHERE lineid = i.lineid;

END;
END IF;
END;
END LOOP;
END ;

For More Technical Topics, Click Here