The sample query can be used to extract the Training history report for an employee in Oracle OLM(Learning Management)
Query : Oracle Learning Management – Training History report
SELECT full_name,
employee_number,
job_id,
job_name,
departmnt,
g_name,
organization_id,
trng_title,
start_date,
end_date,
provider,
duration,
duration_units,
TYPE,
date_start
FROM (SELECT DISTINCT
emp.full_name,
emp.employee_number,
paf.job_id,
(SELECT name
FROM per_jobs
WHERE job_id = paf.job_id)
job_name,
(SELECT name
FROM hr_all_organization_units
WHERE organization_id = odb.organization_id)
departmnt,
(SELECT name
FROM hr_all_organization_units
WHERE organization_id =
(SELECT ose.organization_id_parent
FROM per_org_structure_versions posv,
per_org_structure_elements ose,
per_organization_structures pos
WHERE ose.organization_id_child =
odb.organization_id
AND TRUNC (SYSDATE) BETWEEN posv.date_from
AND NVL (
posv.date_to,
TRUNC (
SYSDATE))
AND ose.org_structure_version_id =
posv.org_structure_version_id
AND posv.organization_structure_id =
pos.organization_structure_id
AND pos.name = ‘XX Primary Hierarchy’
AND primary_structure_flag = ‘Y’))
g_name,
paf.organization_id,
oe.title trng_title,
TO_CHAR (oe.course_start_date, ‘DD-MON-YYYY’) start_date,
oe.course_start_date date_start,
TO_CHAR (oe.course_end_date, ‘DD-MON-YYYY’) end_date,
(SELECT orbv.resource_name
FROM ota_resource_bookings_v orbv,
ota_activity_versions oav
WHERE orbv.activity_version_id = oav.activity_version_id
AND resource_type = ‘T’
AND status = ‘C’
AND event_id(+) = odb.event_id)
provider,
oe.duration,
oe.duration_units,
obst.TYPE
FROM ota_delegate_bookings odb,
ota_events oe,
ota_booking_status_types obst,
per_all_people_f emp,
per_all_assignments_f paf
WHERE odb.event_id = oe.event_id
AND odb.booking_status_type_id = obst.booking_status_type_id
AND odb.business_group_id = oe.business_group_id
AND oe.business_group_id = obst.business_group_id
AND obst.booking_status_type_id = odb.booking_status_type_id
AND emp.person_id = odb.delegate_person_id
AND emp.person_id = paf.person_id
AND NVL (oe.evt_information4, ‘YES’) != ‘NO’
AND paf.primary_flag = ‘Y’
AND emp.current_employee_flag = ‘Y’
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND SYSDATE BETWEEN emp.effective_start_date
AND emp.effective_end_date
AND paf.person_id = :p_emp_num
UNION ALL
SELECT full_name,
papf.employee_number,
paaf.job_id,
(SELECT name
FROM per_jobs
WHERE job_id = paaf.job_id)
job_name,
(SELECT name
FROM hr_all_organization_units
WHERE organization_id = onhv.organization_id)
departmnt,
(SELECT name
FROM hr_all_organization_units
WHERE organization_id =
(SELECT ose.organization_id_parent
FROM per_org_structure_versions posv,
per_org_structure_elements ose,
per_organization_structures pos
WHERE ose.organization_id_child =
onhv.organization_id
AND TRUNC (SYSDATE) BETWEEN posv.date_from
AND NVL (
posv.date_to,
TRUNC (
SYSDATE))
AND ose.org_structure_version_id =
posv.org_structure_version_id
AND posv.organization_structure_id =
pos.organization_structure_id
AND pos.name = ‘XX Primary Hierarchy’
AND primary_structure_flag = ‘Y’))
g_name,
paaf.organization_id,
trng_title,
TO_CHAR ( (completion_date – duration), ‘DD-MON-YYYY’)
start_date,
(completion_date – duration) date_start,
TO_CHAR (completion_date, ‘DD-MON-YYYY’) end_date,
provider,
duration,
duration_units,
TYPE
FROM per_all_people_f papf,
per_all_assignments_f paaf,
ota_notrng_histories_v onhv
WHERE papf.person_id = onhv.person_id
AND papf.person_id = paaf.person_id
AND paaf.primary_flag = ‘Y’
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.person_id = :p_emp_num)
ORDER BY date_start
;
