APPS.OTA_LRNG_PATH_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19

SELECT 1
  FROM ota_lp_enrollments lpe
 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id
   AND lpe.no_of_mandatory_courses = lpe.no_of_completed_courses;

Line: 25

SELECT lme.lp_member_enrollment_id
  FROM ota_lp_member_enrollments lme,
       ota_lp_enrollments lpe
 WHERE lme.member_status_code = 'COMPLETED'
   AND lpe.lp_enrollment_id = lme.lp_enrollment_id
   AND lpe.lp_enrollment_id = p_lp_enrollment_id
   AND rownum = 1;

Line: 35

   SELECT lps.path_source_code, lps.source_function_code, lpe.path_status_code
   FROM ota_learning_paths lps,
        ota_lp_enrollments lpe
   WHERE lpe.lp_enrollment_id = p_lp_enrollment_id
      AND lpe.learning_path_id = lps.learning_path_id;

Line: 44

   SELECT lps.completion_type_code, lps.learning_path_section_id, lps.no_of_mandatory_courses
   FROM ota_lp_sections lps, ota_lp_enrollments lpe
   WHERE lpe.lp_enrollment_id = p_lp_enrollment_id AND
         lpe.learning_path_id = lps.learning_path_id;

Line: 50

   SELECT  member_status_code FROM ota_lp_member_enrollments
   WHERE learning_path_section_id = p_lp_section_id AND
         lp_enrollment_id = p_lp_enrollment_id;

Line: 148

   SELECT lpe.person_id,
          lpe.contact_id
     INTO l_person_id,
          l_contact_id
     FROM ota_lp_enrollments lpe
    WHERE lpe.lp_enrollment_id = p_lp_enrollment_id;

Line: 155

    SELECT employee_id,
           customer_id
      INTO l_login_person,
           l_login_customer
      FROM fnd_user
     WHERE user_id = fnd_profile.value('USER_ID');

Line: 187

SELECT person_id
  FROM ota_lp_enrollments
 WHERE lp_enrollment_id = p_lp_enrollment_id;

Line: 213

CURSOR csr_lpe_update(csr_lp_enrollment_id number)
    IS
    SELECT lpe.lp_enrollment_id,
           lpe.object_version_number
     FROM ota_lp_enrollments lpe
     WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;

Line: 227

        OPEN csr_lpe_update(p_lp_enrollment_id);

Line: 228

        FETCH csr_lpe_update into l_lp_enrollment_id,l_object_version_number;

Line: 229

        IF csr_lpe_update%FOUND then
           CLOSE csr_lpe_update;

Line: 231

           ota_lp_enrollment_api.update_lp_enrollment
                       (p_effective_date               => sysdate
                       ,p_lp_enrollment_id             => p_lp_enrollment_id
                       ,p_object_version_number        => l_object_version_number
                       ,p_path_status_code             => l_path_status_code
                       ,p_completion_date              => sysdate);

Line: 238

          CLOSE csr_lpe_update;

Line: 250

SELECT sum(no_of_mandatory_courses)
  FROM ota_lp_sections
 WHERE learning_path_id = p_learning_path_id
   AND completion_type_code = 'S';

Line: 256

SELECT count(lpm.learning_path_member_id)
  FROM ota_lp_sections lpc,
       ota_learning_path_members lpm
 WHERE lpc.learning_path_id = p_learning_path_id
   and lpm.learning_path_section_id = lpc.learning_path_section_id
   AND completion_type_code = 'M';

Line: 331

SELECT lpc.no_of_mandatory_courses,
       lpc.learning_path_section_id
  FROM ota_lp_enrollments lme,
       ota_lp_sections lpc
 WHERE lpc.learning_path_id = lme.learning_path_id
   AND lpc.completion_type_code = 'S'
   AND lme.lp_enrollment_id = p_lp_enrollment_id;

Line: 340

SELECT count(lp_member_enrollment_id)
  FROM ota_lp_member_enrollments lme
 WHERE lme.learning_path_section_id = l_learning_path_section_id
   AND lme.member_status_code = 'COMPLETED'
   AND lme.lp_enrollment_id = p_lp_enrollment_id;

Line: 347

SELECT count(lp_member_enrollment_id)
  FROM ota_lp_member_enrollments lme,
       ota_lp_sections lpc
 WHERE lpc.learning_path_section_id = lme.learning_path_section_id
   AND lpc.completion_type_code = 'M'
   AND lme.member_status_code = 'COMPLETED'
   AND lme.lp_enrollment_id = p_lp_enrollment_id;

Line: 440

PROCEDURE Update_lpe_lpm_changes( p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
				 --Modified for Bug#3891087
                                  --p_path_source_code IN ota_learning_paths.path_source_code%TYPE,
                                  p_completion_target_date IN ota_lp_enrollments.completion_target_date%TYPE,
                                  p_return_status OUT NOCOPY VARCHAR2)
is

CURSOR one_child_completed IS
SELECT lme.lp_member_enrollment_id
  FROM ota_lp_member_enrollments lme,
       ota_lp_enrollments lpe
 WHERE lme.member_status_code = 'COMPLETED'
   AND lpe.lp_enrollment_id = lme.lp_enrollment_id
   AND lpe.lp_enrollment_id = p_lp_enrollment_id
   AND rownum = 1;

Line: 456

CURSOR csr_lpe_update(csr_lp_enrollment_id number)
    IS
    SELECT lpe.lp_enrollment_id,
           lpe.learning_path_id,
           lpe.completion_target_date,
           lpe.object_version_number
     FROM ota_lp_enrollments lpe
     WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;

Line: 469

    SELECT lps.path_source_code, lps.source_function_code, lpe.path_status_code
    FROM ota_learning_paths lps, ota_lp_enrollments lpe
    WHERE lps.learning_path_id = lpe.learning_path_id
	        AND lpe.lp_enrollment_id = p_lp_enrollment_id;

Line: 493

    OPEN csr_lpe_update(p_lp_enrollment_id);

Line: 494

    FETCH csr_lpe_update into l_lp_enrollment_id,l_learning_path_id, l_completion_target_date, l_object_version_number;

Line: 495

    CLOSE csr_lpe_update;

Line: 529

           ota_lp_enrollment_api.update_lp_enrollment
	                          (p_effective_date               => sysdate
	                          ,p_lp_enrollment_id             => p_lp_enrollment_id
	                          ,p_object_version_number        => l_object_version_number
				              ,p_no_of_completed_courses      => l_no_of_completed_courses
            				  ,p_no_of_mandatory_courses      => l_no_of_mandatory_courses
                              ,p_completion_target_date       => l_completion_target_date
	                          ,p_path_status_code             => l_path_status_code
                        	  ,p_completion_date              => trunc(sysdate));

Line: 541

    	   ota_lp_enrollment_api.update_lp_enrollment
	                          (p_effective_date               => sysdate
	                          ,p_lp_enrollment_id             => p_lp_enrollment_id
	                          ,p_object_version_number        => l_object_version_number
	                          ,p_no_of_completed_courses      => l_no_of_completed_courses
				              ,p_no_of_mandatory_courses      => l_no_of_mandatory_courses
                              ,p_completion_target_date       => l_completion_target_date);

Line: 554

END Update_lpe_lpm_changes;

Line: 617

SELECT learning_path_id
  FROM ota_learning_paths
 WHERE source_function_code = p_source_function_code
   AND business_group_id = p_business_group_id
   AND person_id = p_person_id
   AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND source_id = p_source_id))
   AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND assignment_id = p_assignment_id));

Line: 679

SELECT learning_path_id,
       no_of_mandatory_courses,
       completion_type_code
  FROM ota_lp_sections
 WHERE learning_path_section_id = p_learning_path_section_id;

Line: 686

SELECT count(learning_path_member_id)
  FROM ota_learning_path_members lpm,
       ota_lp_sections lpc
 WHERE lpc.learning_path_section_id = p_learning_path_section_id
   AND lpm.learning_path_section_id = lpc.learning_path_section_id
   AND lpc.completion_type_code = 'M';

Line: 694

select count(*)
from ota_lp_member_enrollments lme,
     ota_lp_enrollments lpe
where
     lpe.learning_path_id = l_learning_path_id
     and lme.learning_path_section_id = p_learning_path_section_id
     and (lpe.person_id = p_person_id or lpe.contact_id = p_contact_id)
     and lpe.PATH_STATUS_CODE in ('ACTIVE', 'COMPLETED')
     and lme.member_status_code like 'COMPLETED'
     and lme.LP_ENROLLMENT_ID = lpe.LP_ENROLLMENT_ID;

Line: 759

 select lpe.learning_path_id,lpe.path_status_code, lpe.person_id, lpe.contact_id
 FROM ota_lp_enrollments lpe
 where lpe.lp_enrollment_id = p_lp_enrollment_id;

Line: 764

select
     sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
     max(lpc.no_of_mandatory_courses) no_of_courses
  from ota_lp_sections lpc
    , ota_learning_path_members lpm
    , ota_events evt
    , ota_delegate_bookings tdb
    , ota_lp_enrollments lpe
    , ota_booking_status_types bst
 where lpc.learning_path_id         = lpe.learning_path_id
   and lpm.learning_path_section_id = lpc.learning_path_section_id
   and lpm.activity_version_id      = evt.activity_version_id
   and tdb.event_id                 = evt.event_id
   and lpc.completion_type_code     = 'S'
   and tdb.delegate_person_id       = lpe.person_id
   and lpe.lp_enrollment_id         = p_lp_enrollment_id
   and lpe.path_status_code         = 'COMPLETED'
   and tdb.booking_status_type_id = bst.booking_status_type_id
   and bst.type = 'A'
 group by lpc.learning_path_section_id

UNION ALL

  select
     sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
     sum(1) no_of_courses
  from ota_lp_sections lpc
    , ota_learning_path_members lpm
    , ota_events evt
    , ota_delegate_bookings tdb
    , ota_lp_enrollments lpe
    , ota_booking_status_types bst
 where lpc.learning_path_id         = lpe.learning_path_id
   and lpm.learning_path_section_id = lpc.learning_path_section_id
   and lpm.activity_version_id      = evt.activity_version_id
   and tdb.event_id                 = evt.event_id
   and lpc.completion_type_code     = 'M'
   and tdb.delegate_person_id       = lpe.person_id
   and lpe.lp_enrollment_id         = p_lp_enrollment_id
   and lpe.path_status_code         = 'COMPLETED'
   and tdb.booking_status_type_id = bst.booking_status_type_id
   and bst.type = 'A'
 group by lpc.learning_path_section_id;

Line: 809

select
     sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
     max(lpc.no_of_mandatory_courses) no_of_courses
  from ota_lp_sections lpc
    , ota_learning_path_members lpm
    , ota_events evt
    , ota_delegate_bookings tdb
    , ota_lp_enrollments lpe
    , ota_booking_status_types bst
 where lpc.learning_path_id         = lpe.learning_path_id
   and lpm.learning_path_section_id = lpc.learning_path_section_id
   and lpm.activity_version_id      = evt.activity_version_id
   and tdb.event_id                 = evt.event_id
   and lpc.completion_type_code     = 'S'
   and tdb.delegate_contact_id      = lpe.contact_id
   and lpe.lp_enrollment_id         = p_lp_enrollment_id
   and lpe.path_status_code         = 'COMPLETED'
   and tdb.booking_status_type_id = bst.booking_status_type_id
   and bst.type = 'A'
 group by lpc.learning_path_section_id

UNION ALL

  select
     sum( decode(tdb.successful_attendance_flag, 'Y',1,0)) completed_courses,
     sum(1) no_of_courses
  from ota_lp_sections lpc
    , ota_learning_path_members lpm
    , ota_events evt
    , ota_delegate_bookings tdb
    , ota_lp_enrollments lpe
    , ota_booking_status_types bst
 where lpc.learning_path_id         = lpe.learning_path_id
   and lpm.learning_path_section_id = lpc.learning_path_section_id
   and lpm.activity_version_id      = evt.activity_version_id
   and tdb.event_id                 = evt.event_id
   and lpc.completion_type_code     = 'M'
   and tdb.delegate_contact_id      = lpe.contact_id
   and lpe.lp_enrollment_id         = p_lp_enrollment_id
   and lpe.path_status_code         = 'COMPLETED'
   and tdb.booking_status_type_id = bst.booking_status_type_id
   and bst.type = 'A'
 group by lpc.learning_path_section_id;

Line: 887

select lp_enrollment_id, lpe.learning_path_id
From  ota_learning_path_members lpm
    , ota_events evt
        , ota_lp_enrollments lpe
        where evt.activity_version_id = lpm.activity_version_id
    and lpm.learning_path_id = lpe.learning_path_id
    and evt.event_id = p_event_id
    and lpe.person_id = p_person_id;

Line: 909

    ota_competence_ss.create_wf_process(p_process		=> 'OTA_COMPETENCE_UPDATE_JSP_PRC',
                                                  p_itemtype		=> 'HRSSA',
                                                  p_person_id 		=> p_person_id,
                                                  p_eventid		=> null,
                                                  p_learningpath_ids	=> to_char(rec.learning_path_id),
                                                  p_itemkey		=> l_item_key);