API to Create External Learning – OTA_NHS_API

By | December 14, 2014

Background of External Learning Create API


In previous article we covered to create the courses through API and in this article we shall cover external learning for an employee. In OLM when an employees attends a course or training from outside of the organization, it is called external learning. We can use following API to create the external learning records for employees.

Create External Learning
Delete External Learning

OTA_NHS_API

External Learning Create API – OTA_NHS_API

 

Create External Learning

 

When We create External Learning through application, In business group_id field, it takes the bsuiness_group_id of the responsibility with which you are logged in.

At the time of loading data through API, you should use FND_GLOBAL.APPS_INITIALIZE so that business group id could be set according to Learning Administrator’s Responsibility Id. Otherwise, API will give the error if you will try to set business group id of Learning Admin.

In this example, I have set the credentials of a user that has Learning Admin Responsibility.

DECLARE

errbuf VARCHAR2(200);
retcode VARCHAR2(200);
p_business_group_id NUMBER:= 83;
p_validate VARCHAR2(10):= ‘FALSE’;
p_from_date DATE;
p_to_date DATE;
———-Local Variables————————-

l_business_group_id NUMBER := p_business_group_id;
l_person_id NUMBER;
l_organization_id NUMBER;
l_location_id NUMBER;
l_duration NUMBER;
l_training_type_code VARCHAR2 (10);
l_status VARCHAR2 (10) := ‘True’;
validate_cnt NUMBER;
error_msg VARCHAR2 (2000);
l_validate BOOLEAN:=FALSE;
l_effective_date DATE := TRUNC (SYSDATE);
l_some_warning BOOLEAN;
l_nota_history_id NUMBER;
l_object_version_number NUMBER;
l_total_records NUMBER := 0;
l_success_records NUMBER := 0;
l_failure_records NUMBER := 0;
l_activity_version_id NUMBER := 0;
l_training_type VARCHAR2 (50);
l_status_code VARCHAR2 (40);
l_stat VARCHAR2 (20);
l_v_course_name VARCHAR2 (500);
l_error_message VARCHAR2 (2000);
p_Cnt_Records NUMBER;
p_Status VARCHAR2 (20);
l_v_location VARCHAR2 (100);
l_v_supplier VARCHAR2 (100);
l_v_course_for VARCHAR2 (100);
l_v_person_type VARCHAR2 (100);
l_v_category VARCHAR2 (100);
l_v_sub_category VARCHAR2 (100);
user_exception EXCEPTION;

/* get temp records */
CURSOR ota_history_csr
IS
SELECT *
FROM LSG_TRAINING_DATA_UPLOAD_STG
WHERE STATUS is null ;

BEGIN

fnd_global.APPS_INITIALIZE (45274,50637,810);

FOR sel IN ota_history_csr LOOP

—————Validation for values of API Starts here———

l_status := ‘True’;
error_msg := NULL;
l_activity_version_id := NULL;
l_v_person_type := ‘No Employee’;
l_person_id := Null;
l_business_group_id := Null;
l_organization_id := Null;
l_v_category := sel.category;
l_v_sub_category := sel.sub_category;

Begin

select person_id, pt.user_person_type
into l_person_id,l_v_person_type
from per_all_people_f papf, per_person_types pt
where papf.employee_number = sel.employee_number
and papf.PERSON_TYPE_ID = pt.person_type_id
and papf.business_group_id = pt.business_group_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date;

exception when others then

error_msg := error_msg || ‘ Invalid PF No ‘||sel.employee_number;
l_status := ‘False’;

END;

/* If Person Exists then Get More Person Details */
if l_v_person_type = ‘Employee’ then

BEGIN

SELECT paaf.business_group_id,paaf.organization_id,l_location_id
INTO l_business_group_id,l_organization_id,l_location_id
FROM per_all_assignments_f paaf
WHERE 1=1
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and paaf.person_id = l_person_id;

EXCEPTION WHEN OTHERS THEN

error_msg := error_msg || ‘ Invalid PF No ‘||sel.employee_number||’. Please make sure employee and its organization are active.’||sqlerrm;
l_status := ‘False’;

END;

elsif l_v_person_type = ‘Ex-employee’ then

error_msg := ‘Resigned Employee’;–. PF No’||sel.employee_number;
l_status := ‘False’;

else

error_msg := ‘Employee Not Found’;–. PF No’||sel.employee_number;
l_status := ‘False’;

end if;

IF sel.unique_course_code is not null and l_status = ‘True’then

l_v_category := null;
l_v_sub_category := null;

–Get Course Name from Unique Course Code
BEGIN

select activity_version_id,version_name
into l_activity_version_id,l_v_course_name
from OTA_ACTIVITY_VERSIONS
where upper(version_code) = upper(sel.unique_course_code);

EXCEPTION WHEN OTHERS THEN

error_msg := error_msg || ‘ Invalid Unique Course Code. ‘;–||upper(sel.unique_course_code);
l_status := ‘False’;
NULL;

END;

END IF;

—————Validation for values of API Ends here———

UPDATE LSG_TRAINING_DATA_UPLOAD_STG
SET status = l_status, error_message =error_msg,upload_time = sysdate
WHERE lineid = sel.lineid;

IF (l_status = ‘True’) THEN

BEGIN

l_total_records := l_total_records + 1;

ota_nhs_api.create_non_ota_histories

(p_validate => l_validate,
p_effective_date => trunc(sysdate),
p_person_id => l_person_id,
p_trng_title => nvl(l_v_course_name,sel.course_name),
p_activity_version_id => l_activity_version_id,
p_provider => sel.provider,–Mandatory Column for External Learning
p_type => ‘ILT’,
p_centre => sel.TRAINING_CENTER,
p_completion_date => sel.completion_date,
p_nth_information1 => sel.course_code,
p_nth_information2 => sel.instructor_name,
p_nth_information3 => sel.start_date,
p_nth_information4 => sel.supplier,
p_duration => sel.DURATION,
p_duration_units => ‘D’,
p_status => ‘ATT’,–Attended
p_organization_id => l_organization_id, –Organization of the employee who attended the course
p_business_group_id => l_business_group_id, –BG of the employee who attended the course
p_nota_history_id => l_nota_history_id,
p_object_version_number => l_object_version_number,
p_some_warning => l_some_warning
);

UPDATE LSG_TRAINING_DATA_UPLOAD_STG
SET status = ‘Success’,upload_time = sysdate
WHERE lineid = sel.lineid;
DBMS_OUTPUT.put_line ( ‘Success- notaid ‘||l_nota_history_id);
l_success_records := l_success_records + 1;
l_activity_version_id := NULL;

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( ’11… Exception ‘||SQLERRM);
l_failure_records := l_failure_records + 1;
error_msg := SUBSTR (SQLERRM, 1, 200);

UPDATE XXALB_TRAINING_DATA_UPLOAD_STG
SET status = ‘Error’,error_message = error_msg,upload_time = sysdate
WHERE lineid = sel.lineid;

END;

ELSE

UPDATE XXALB_TRAINING_DATA_UPLOAD_STG
SET status = ‘E’,
error_message = error_msg
,upload_time = sysdate
WHERE lineid = sel.lineid;

END IF;

END LOOP;

exception when others then
DBMS_OUTPUT.put_line ( ’14… Exception ‘||SQLERRM);

END ;Top

Note: Exceptions Error Message Can be found in following Package   ota_nhs_bus.chk_non_updateable_args

Delete External Learning


DECLARE

cursor c1 is
select * from ota_notrng_histories
where 1=1
order by person_id;

BEGIN

FOR r in c1 LOOP

begin

ota_nhs_api.delete_external_learning

(p_validate =>FALSE
,p_nota_history_id =>r.nota_history_id
,p_object_version_number =>r.object_version_number
) ;

dbms_output.put_line(‘notad_id ‘||r.nota_history_id ||’ Deleted’);

exception when others then

dbms_output.put_line(‘Exception in notad_id ‘||r.nota_history_id ||’ SQLERRM ‘||SQLERRM);

end;

END LOOP;

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

END;Top

Prev: Create Course for OLM Catalog
Next: OLM Tables Structure

Related Posts:

Oracle HRMS Interview Questions
Question of the Day
9 Steps to become Oracle Apps Consultant
How to create backend logging mechanism
Steps for creating custom form in Oracle Apps
Clear Global Cache in Oracle Apps
Profile Values in Oracle Forms Personalization
2 simple Steps to make a responsibility read only
Step to Register Discoverer Report
Hierarchy of OLM Tables