OLM-Oracle Learning Management List of APIs

Conference Server OTA_CONFERENCE_SERVER_API Learning Management PL/SQL Oracle Active This package contains the Conference server APIs.
Course Prerequisite OTA_COURSE_PREREQUISITE_API Learning Management PL/SQL Oracle Active This package contains course prerequisite APIs.
Create Delegate Booking/update/Delete
Enrollment OTA_DELEGATE_BOOKING_API Learning Management PL/SQL Oracle Active This package manages a learner enrollment in a class.
Announcement OTA_ANNOUNCEMENT_API Learning Management PL/SQL Oracle Active This package contains the Announcement APIs.

Course Category OTA_ACTIVITY_CATEGORY_API Learning Management PL/SQL Oracle Active This package contains the Course Category API.
Category Usage OTA_CATEGORY_USAGE_API Learning Management PL/SQL Oracle Active This package contains the category usage APIs that create or update a Category or Delivery Mode.

Learning Certification
Certification Category Inclusion OTA_CERT_CATEGORY_API Learning Management PL/SQL Oracle Active This package contains Certification Category Inclusion APIs.
Certification Enrollment OTA_CERT_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains Learning Certification Enrollment APIs.
Certification Member OTA_CERT_MEMBER_API Learning Management PL/SQL Oracle Active This package contains learning certification member APIs.
Certification Member Enrollment OTA_CERT_MBR_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains Certification Member Enrollment APIs.
Certification Period Enrollment OTA_CERT_PRD_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains Learning Certification Period Enrollment APIs.
Learning Certification OTA_CERTIFICATION_API Learning Management PL/SQL Oracle Active This package contains learning certification APIs.
OTA_TRAINING_RECORD OTA_TRAINING_RECORD Learning Management PL/SQL Oracle Active This is the source file to query certification and course details

Learning Chat
Chat Inclusion APIs OTA_CHAT_OBJ_INCLUSION_API Learning Management PL/SQL Oracle Active This package contains Category Chat and Class Chat association-related APIs.
Chat Message OTA_CHAT_MESSAGE_API Learning Management PL/SQL Oracle Active This package contains chat messages related APIs.
Chat Users APIs OTA_CHAT_USER_API Learning Management PL/SQL Oracle Active This package contains chat user-related APIs.
Chats OTA_CHAT_API Learning Management PL/SQL Oracle Active This package contains chat related APIs.

Learning Class
Class OTA_EVENT_API Learning Management PL/SQL Oracle Active This package contains the class APIs.

Learning Courses
Course OTA_ACTIVITY_VERSION_API Learning Management PL/SQL Oracle Active This package contains the course APIs.
Course Other Information OTA_SKILL_PROVISION_API Learning Management PL/SQL Oracle Active This package contains the Course Other Information APIs.

Learning Cross Charge Setup
Cross Charge OTA_TCC_API Learning Management PL/SQL Oracle Active This package contains the APIs to create and update a cross charge.
General Ledger Flexfield OTA_THG_API Learning Management PL/SQL Oracle Active This API maintains the detail mappings between Oracle General Ledger's Chart of Account keyflex segments and Oracle Human Resources Cost Allocation keyflex segments.

Learning Enrollment Justification
Booking Justification OTA_BKNG_JUSTIFICATION_API Learning Management PL/SQL Oracle Active This package contains Booking Justification APIs.

Learning Enrollment Status Type
Enrollment Status OTA_BOOKING_STATUS_TYPE_API Learning Management PL/SQL Oracle Active This package contains Enrollment status APIs.

Learning External Record
External Learning OTA_NHS_API Learning Management PL/SQL Oracle Active This package creates and updates a user's external learning.

Learning Finance Header
Finance Header OTA_FINANCE_HEADER_API Learning Management PL/SQL Oracle Active This package contains finance header APIs.
Learning Finance line
Finance Lines. OTA_FINANCE_LINE_API Learning Management PL/SQL Oracle Active This package contains Finance Lines APIs.

Learning Forum
Forum OTA_FORUM_API Learning Management PL/SQL Oracle Active This package contains forum-related APIs.
Forum Inclusion APIs OTA_FRM_OBJ_INCLUSION_API Learning Management PL/SQL Oracle Active This package contains Category Forum and Class Forum association-related APIs.
Forum Messages OTA_FORUM_MESSAGE_API Learning Management PL/SQL Oracle Active This package contains forum message-related APIs.
Forum Notification Subscribers OTA_FRM_NOTIF_SUBSCRIBER_API Learning Management PL/SQL Oracle Active This package contains forum notification subscriber-related APIs.
Forum Threads OTA_FORUM_THREAD_API Learning Management PL/SQL Oracle Active This package contains forum thread-related APIs.
Open Forum Chat Enrollments OTA_OPEN_FC_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains category-forum and category-chat enrollments related APIs.
Private Forum Thread Users OTA_PVT_FRM_THREAD_USERS_API Learning Management PL/SQL Oracle Active This package contains APIs related to private forum thread users.

Learning Offer
Offering OTA_OFFERING_API Learning Management PL/SQL Oracle Active This package contains the offering APIs.

Learning Offering Resource Checklist
Resource Usage OTA_RESOURCE_USAGE_API Learning Management PL/SQL Oracle Active This package creates, updates, and deletes resource associations at the offering level.

Learning Paths
Learning Path OTA_LEARNING_PATH_API Learning Management PL/SQL Oracle Active This package contains the Learning Path APIs.
Learning Path Section OTA_LP_SECTION_API Learning Management PL/SQL Oracle Active This package contains learning path section APIs.

Learning Path Category
Learning Path Category Inclusion OTA_LP_CATEGORY_API Learning Management PL/SQL Oracle Active This package contains Learning Path Category Inclusion APIs.

Learning Path Component
Learning Path Component OTA_LP_MEMBER_API Learning Management PL/SQL Oracle Active This package contains Learning Path Component APIs.

Learning Path Component
Learning Path Component Enrollment OTA_LP_MEMBER_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains learning path component enrollment APIs.
Learning Path Enrollment OTA_LP_ENROLLMENT_API Learning Management PL/SQL Oracle Active This package contains learning path enrollment APIs.

Learning Resource
Resource Definition OTA_RESOURCE_DEFINITION_API Learning Management PL/SQL Oracle Active This package contains Resource definition section APIs.

Learning Resource Booking
Resource Booking OTA_RESOURCE_BOOKING_API Learning Management PL/SQL Oracle Active This package contains the Resource Booking APIs.

Training Plan
Measurement Type OTA_TMT_API Learning Management PL/SQL Oracle Active This package contains the measurement type APIs for use by Organization Training Plans.
Training Plan OTA_TPS_API Learning Management PL/SQL Oracle Active The APIs in this package create, update, and delete personal or organization training plans.
Training Plan Component OTA_TPM_API Learning Management PL/SQL Oracle Active The APIs in this package create, update, and delete Personal and Organization Training Plan components.
Training Plan Cost OTA_TPC_API Learning Management PL/SQL Oracle Active This package contains the Organization Training Plan Cost APIs.

FND-Query To get Fnd Attched Documents

SELECT fl.file_name, fl.file_data
  FROM fnd_lobs fl, fnd_attached_documents fad, fnd_documents_tl fdl
  WHERE fad.pk1_value = TO_CHAR (:l_document_id)
   AND fad.pk2_value = TO_CHAR (l_revision_number)
   AND fdl.document_id = fad.document_id
   AND fdl.media_id = fl.file_id
   AND fad.entity_name = :l_entity_name;

IREC-Query to get List of all interview schedules and which are not cancelled

 SELECT PerEventsEO.EVENT_ID,
       PerEventsEO.ASSIGNMENT_ID,
       papf.full_name Applicant_name,
       paaf.person_id,
       paaf.vacancy_id,
       pav.name Vacancy_Name,
       haou.name Org_name,
       pj.name Job_name,
       paaf.effective_start_date,
       paaf.effective_end_date,
       PerEventsEO.DATE_START,
       VL.MEANING TYPE,
       PerEventsEO.DATE_END,
       PerEventsEO.EVENT_OR_INTERVIEW,
       PerEventsEO.TIME_END,
       PerEventsEO.TIME_START
       --pav.*    
  FROM PER_EVENTS PerEventsEO,
  per_all_assignments_f paaf,
  per_all_vacancies pav,
  per_all_people_f papf,
  hr_all_organization_units haou,
  per_jobs pj,
  FND_LOOKUP_TYPES_VL tl,
  FND_LOOKUP_VALUES_VL vl
  where --paaf.ASSIGNMENT_ID=C_AssignID
  paaf.person_id =(Select Distinct papf.person_id
  from per_all_assignments_f paaf,  per_all_people_f papf
  where
  papf.person_id=paaf.person_id
  and sysdate between paaf.effective_start_date and paaf.effective_end_date
  and sysdate between papf.effective_start_date and papf.effective_end_date
  and assignment_id=C_AssignID)
  and paaf.assignment_id=PerEventsEO.assignment_id
  and papf.person_id=paaf.person_id
  and pav.vacancy_id=paaf.vacancy_id
  and haou.ORGANIZATION_ID=pav.ORGANIZATION_ID
  and pj.job_id=pav.job_id
  AND TL.LOOKUP_TYPE=VL.LOOKUP_TYPE
  and tl.LOOKUP_TYPE='IRC_INTERVIEW_TYPE'
  AND PerEventsEO.TYPE=VL.LOOKUP_CODE
  and sysdate between paaf.effective_start_date and paaf.effective_end_date
  and sysdate between papf.effective_start_date and papf.effective_end_date
  and PerEventsEO.event_id not in (
  select event_id from IRC_INTERVIEW_DETAILS a where STATUS='CANCELLED' and a.event_id=PerEventsEO.event_id)
  and PerEventsEO.event_id not in (C_event_id);
 

VALUE SET DYNAMIC-Query To create Dynamic List of year and months

SELECT a.months
  ||'/'
  ||b.years credit_card_expiration_date
FROM
  (SELECT TO_CHAR(add_months(TRUNC(SYSDATE, 'YYYY'), LEVEL - 1), 'MM') months
  FROM dual
    CONNECT BY LEVEL <= 12
  ) a,
  (SELECT SUBSTR(EXTRACT(YEAR FROM SYSDATE) + (LEVEL-1),3) years
  FROM dual
    CONNECT BY LEVEL <=10
  ) b
WHERE to_date(a.months
  ||'/'
  ||b.years,'mm/yy')>=SYSDATE
ORDER BY b.years,
  a.months;

OTL-Query To get assignment time information

SELECT aai.ROWID row_id, aai.ID, aai.effective_start_date,
       aai.effective_end_date, peo.full_name, aai.assignment_id,
       asg.assignment_number, asg.business_group_id asg_bus_grp_id,
       aai.autogen_hours_yn, hrlk.meaning autogen_hours_yn_meaning,
       aai.rotation_plan, rot.NAME rotation_plan_name, aai.earning_policy,
       erp.NAME earning_policy_name, aai.shift_differential_policy,
       sdp.NAME shift_differential_policy_name, aai.hour_deduction_policy,
       hdp.NAME hour_deduction_policy_name, aai.created_by, aai.creation_date,
       aai.last_update_date, aai.last_update_login, aai.last_updated_by,
       aai.attribute_category, aai.attribute1, aai.attribute2, aai.attribute3,
       aai.attribute4, aai.attribute5, aai.attribute6, aai.attribute7,
       aai.attribute8, aai.attribute9, aai.attribute10, aai.attribute11,
       aai.attribute12, aai.attribute13, aai.attribute14, aai.attribute15,
       aai.attribute16, aai.attribute17, aai.attribute18, aai.attribute19,
       aai.attribute20, aai.attribute21, aai.attribute22, aai.attribute23,
       aai.attribute24, aai.attribute25, aai.attribute26, aai.attribute27,
       aai.attribute28, aai.attribute29, aai.attribute30
  FROM hxt_hour_deduct_policies hdp,
       hxt_shift_diff_policies sdp,
       hxt_earning_policies erp,
       hxt_rotation_plans rot,
       per_people_f peo,
       per_assignments_f asg,
       hr_lookups hrlk,
       hxt_add_assign_info_f aai,
       fnd_sessions fss
 WHERE fss.session_id = USERENV ('SESSIONID')
   AND fss.effective_date BETWEEN aai.effective_start_date
                              AND aai.effective_end_date
   AND aai.assignment_id = asg.assignment_id
   AND fss.effective_date BETWEEN asg.effective_start_date
                              AND asg.effective_end_date
   AND asg.person_id = peo.person_id
   AND fss.effective_date BETWEEN peo.effective_start_date
                              AND peo.effective_end_date
   AND aai.autogen_hours_yn = hrlk.lookup_code
   AND hrlk.application_id = 800
   AND hrlk.lookup_type = 'YES_NO'
   AND aai.rotation_plan = rot.ID(+)
   AND aai.earning_policy = erp.ID
   AND aai.shift_differential_policy = sdp.ID(+)
   AND aai.hour_deduction_policy = hdp.ID(+);

OTL-List of Rotation Plan updates as on sysdate

SELECT NAME,
       (CASE
           WHEN TRUNC (SYSDATE) = TRUNC (last_update_date)
              THEN 'YES'
           ELSE 'NO'
        END) "CHANGE", TO_CHAR (last_update_date, 'DD-MON-RRRR') "Last Update Date"
  FROM hxt_rotation_plans
 WHERE 1 = 1
 ORDER BY 2 desc

OTL-Query to get details of Timekeeper,Timesheet organization by passing FACILITY name

select timekeepr_Group_Name,timekeeper_name ,Timekeeper_corporation_number,employee_corporation_Number, Employee_Name,a.name org_name
 from (select employee_number,npw_number,full_name,paaf.organization_id,haou.name
from per_all_people_f papf,per_all_assignments_f paaf,hr_all_organization_units haou
where paaf.person_id=papf.person_id
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and current_employee_flag='Y'
and haou.organization_id=paaf.organization_id
and  paaf.organization_id in
(SELECT
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND org.organization_id = pose.organization_id_child
START WITH
pose.organization_id_parent =(select organization_id from hr_all_organization_units where  type like 'FACILITY%' and name=nvl(:Fac_name,name))
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
))a,
(select tg.TK_GROUP_NAME timekeepr_Group_Name,papf2.full_name timekeeper_name ,papf2.employee_Number Timekeeper_corporation_number,
papf1.employee_Number employee_corporation_Number,papf1.full_name Employee_Name
from hxc_tk_groups tg,hxc_tk_group_queries tkgq,hxc_tk_group_query_criteria tc,per_all_people_f papf1,per_all_people_f papf2,
hxt_rotation_plans hrp
where tg.tk_group_id=tkgq.tk_group_id
and tc.TK_GROUP_QUERY_ID=tkgq.TK_GROUP_QUERY_ID
and tc.CRITERIA_ID=papf1.person_id
and tg.TK_RESOURCE_ID=papf2.person_id
and hrp.name(+)=papf1.employee_number
and sysdate between papf1.effective_start_date and papf1.effective_end_date
and sysdate between papf2.effective_start_date and papf2.effective_end_date)b
where nvl(a.employee_number,a.npw_number)=b.employee_corporation_Number;

select org name by using below query.
select * from hr_all_organization_units  where name like 'Womens%' and type like 'FACILITY%'

OTL-Query To get Employee TimeCard details with elements name.

SELECT   hts.timecard_id, hts.resource_id, hts.start_time,hta.attribute_category,petf.element_name,(SUBSTR(hta.attribute_category,11,LENGTH(hta.attribute_category))) Element_ID,
                  hts.stop_time, hts.submission_date,
                  htb1.start_time each_day, hta.attribute1 project_id,
                  hta.attribute2 task_id, htb2.measure--, pt.task_name
             FROM hxc_time_building_blocks htb,
                  hxc_time_building_blocks htb1,
                  hxc_time_building_blocks htb2,
                  hxc_time_attribute_usages htau,
                  hxc_time_attributes hta,
                  --pa_projects_all papa,
                  hxc_timecard_summary hts,
                  pay_element_types_f petf
                  --pa_tasks pt
            WHERE htb1.parent_building_block_id = htb.time_building_block_id
              AND htb1.parent_building_block_ovn = htb.object_version_number
              AND htb.date_to = hr_general.end_of_time
              AND htb.SCOPE = 'TIMECARD'
              AND htb1.SCOPE = 'DAY'
              AND htb1.date_to = hr_general.end_of_time
              AND htb2.parent_building_block_id = htb1.time_building_block_id
              AND htb2.parent_building_block_ovn = htb1.object_version_number
              AND htb2.SCOPE = 'DETAIL'
              AND htb2.date_to = hr_general.end_of_time
              AND htau.time_building_block_id = htb2.time_building_block_id
              AND htau.time_building_block_ovn = htb2.object_version_number
              AND htau.time_attribute_id = hta.time_attribute_id
              --AND papa.project_id = hta.attribute1
              AND hts.start_time = htb.start_time
              AND hts.resource_id = htb.resource_id
              AND to_char(petf.element_type_id) =(SUBSTR(hta.attribute_category,11,LENGTH(hta.attribute_category)))
           --AND htb.resource_id = p_resource_id
              AND hts.timecard_id = :p_timecard_id
              --AND hta.attribute_category = 'PROJECTS'
            --  AND hts.approval_status = 'WORKING'
         --     AND hta.attribute2 = pt.task_id
           --   AND hta.attribute1 = pt.project_id
              --and hts.timecard_id=38673
             and  hta.attribute_category is not null
             and hta.attribute_category not like 'SEC%'
         ORDER BY htb1.start_time;

OTL-Query to get Timekeeper group name ,Timekeeper and Respective Employees

select tg.TK_GROUP_NAME timekeepr_Group_Name,papf2.full_name timekeeper_name ,papf2.employee_Number Timekeeper_corporation_number,
papf1.employee_Number employee_corporation_Number,papf1.full_name Employee_Name
from hxc_tk_groups tg,hxc_tk_group_queries tkgq,hxc_tk_group_query_criteria tc,per_all_people_f papf1,per_all_people_f papf2,
hxt_rotation_plans hrp
where tg.tk_group_id=tkgq.tk_group_id
and tc.TK_GROUP_QUERY_ID=tkgq.TK_GROUP_QUERY_ID
and tc.CRITERIA_ID=papf1.person_id
and tg.TK_RESOURCE_ID=papf2.person_id
and hrp.name(+)=papf1.employee_number
and sysdate between papf1.effective_start_date and papf1.effective_end_date
and sysdate between papf2.effective_start_date and papf2.effective_end_date
order by papf1.employee_number

Absence -API to Delete Specific Leaves from the month

DECLARE
   CURSOR c1
   IS
      SELECT a.absence_attendance_id, a.object_version_number,
             b.employee_number, b.npw_number,
             DECODE (b.employee_number,
                     NULL, 'Contigent Worker',
                     'Employee'
                    ) person_type,
             full_name, NAME leave_type, date_start leave_start_date,
             date_end leave_end_date
        FROM (SELECT paa.absence_attendance_id, paa.object_version_number,
                     NAME, person_id,
                     TO_CHAR (date_start, 'dd-mon-yyyy') date_start,
                     TO_CHAR (paa.date_end, 'dd-mon-yyyy') date_end
                FROM per_absence_attendances paa,
                     per_absence_attendance_types paat
               WHERE paa.absence_attendance_type_id =
                                               paat.absence_attendance_type_id
                 AND (   (    TRUNC (date_start) >=
                                 TRUNC (TO_DATE ('01-Apr-2013', 'dd-Mon-yyyy'))
                          AND TRUNC (paa.date_end) <=
                                 TRUNC (TO_DATE ('30-Apr-2013', 'dd-Mon-yyyy'))
                         )
                      OR (TO_DATE ('01-Apr-2013', 'dd-Mon-yyyy')
                             BETWEEN date_start
                                 AND paa.date_end
                         )
                      OR (TO_DATE ('30-Apr-2013', 'dd-Mon-yyyy')
                             BETWEEN date_start
                                 AND paa.date_end
                         )
                     )) a,
             per_all_people_f b
       WHERE a.person_id = b.person_id
         AND SYSDATE BETWEEN b.effective_start_date AND b.effective_end_date;
       
     l_count number := 0;
BEGIN
   dbms_output.ENABLE(100000000);    ----to remove the buffer overflow error on toad
 
   fnd_global.apps_initialize(1110,50637,800);     --for calling api on Toad initialise session
 
   FOR v_rec IN c1
   LOOP
      begin
      hr_person_absence_api.delete_person_absence
                     (p_validate                   => FALSE,
                      p_absence_attendance_id      => v_rec.absence_attendance_id,
                      p_object_version_number      => v_rec.object_version_number,
                      p_called_from                => 800
                     );
      l_count := l_count +1;
      exception
         when others
         then
            dbms_output.put_line('Error in for loop: absence_attendance_id: ' || v_rec.absence_attendance_id || ' object_version_number: '
            || v_rec.object_version_number || ' ' || sqlerrm);
      end;
   END LOOP;
 
   dbms_output.put_line('Successful Count: ' || l_count);
 
EXCEPTION
    when others
    then
        dbms_output.put_line('Error occured: ' || sqlerrm);
END;