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: 152

   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: 159

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

Line: 191

SELECT person_id
  FROM ota_lp_enrollments
 WHERE lp_enrollment_id = p_lp_enrollment_id;

Line: 217

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: 231

        OPEN csr_lpe_update(p_lp_enrollment_id);

Line: 232

        FETCH csr_lpe_update into l_lp_enrollment_id,l_object_version_number;

Line: 233

        IF csr_lpe_update%FOUND then
           CLOSE csr_lpe_update;

Line: 235

           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              => ota_lrng_path_member_util.get_lp_completion_date(p_lp_enrollment_id));

Line: 242

          CLOSE csr_lpe_update;

Line: 254

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

Line: 260

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: 335

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: 344

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: 351

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: 444

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: 460

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: 473

    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: 497

    OPEN csr_lpe_update(p_lp_enrollment_id);

Line: 498

    FETCH csr_lpe_update into l_lp_enrollment_id,l_learning_path_id, l_completion_target_date, l_object_version_number;

Line: 499

    CLOSE csr_lpe_update;

Line: 533

           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              => ota_lrng_path_member_util.get_lp_completion_date(p_lp_enrollment_id));

Line: 545

    	   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: 558

END Update_lpe_lpm_changes;

Line: 621

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: 683

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: 690

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: 698

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: 763

 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: 768

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: 813

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: 891

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: 913

    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);

Line: 930

select sum(OTA_LRNG_PATH_UTIL.get_no_of_mand_compl_courses(ols.learning_path_section_id, ole.person_id, ole.contact_id))
from ota_lp_enrollments ole, ota_lp_sections ols
where ole.learning_path_id = ols.learning_path_id
and ole.lp_enrollment_id = p_lp_enrollment_id;

Line: 950

	SELECT lpe.lp_enrollment_id,
	       lpe.person_id,
	       lpe.contact_id,
	       lpe.learning_path_id,
	       lpe.path_status_code,
	       lpm.learning_path_member_id,
	       lpm.activity_version_id,
	       lpm.business_group_id,
	       lpm.learning_path_section_id,
	       lpme.lp_member_enrollment_id
	 FROM ota_lp_enrollments lpe,
	      ota_learning_path_members lpm,
	      ota_lp_member_enrollments lpme
	 WHERE lpe.lp_enrollment_id = p_lp_enrollment_id
	       AND lpe.learning_path_id = lpm.learning_path_id
	       --AND lpe.path_status_code <> 'CANCELLED'
	       AND lpe.lp_enrollment_id = lpme.lp_enrollment_id
	       AND lpm.learning_path_member_id = lpme.learning_path_member_id ;

Line: 972

	SELECT oav.activity_version_id,
	       evt.event_id,
	       evt.course_start_date,
	       evt.course_start_time,
	       evt.course_end_date,
	       evt.course_end_time,
	       tdb.date_status_changed,
	       tdb.booking_status_type_id,
	       tdb.delegate_person_id,
	       tdb.delegate_contact_id
	  FROM ota_activity_versions oav,
	       ota_events evt,
	       ota_delegate_bookings tdb,
	       ota_booking_status_types bst
	 WHERE oav.activity_version_id = p_activity_version_id
	   AND oav.activity_version_id = evt.activity_version_id
	   AND evt.event_id = tdb.event_id
	   AND bst.booking_status_type_id = tdb.booking_status_type_id
	   AND bst.type = 'P'
	   AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
	                   OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id));

Line: 1037

SELECT lpe.lp_enrollment_id
FROM ota_lp_enrollments lpe,
     ota_events oev,
     ota_learning_path_members lpm
WHERE lpe.person_id =  p_person_id
      AND oev.event_id = p_event_id
      AND NVL(lpe.is_history_flag, 'N') = 'N'
      AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
      AND lpe.business_group_id = ota_general.get_business_group_id
      AND oev.activity_version_id = lpm.activity_version_id
      AND lpe.learning_path_id = lpm.learning_path_id;

Line: 1050

SELECT lpe.lp_enrollment_id
FROM ota_lp_enrollments lpe,
     ota_events oev,
     ota_learning_path_members lpm
WHERE lpe.contact_id =  p_contact_id
      AND oev.event_id = p_event_id
      AND NVL(lpe.is_history_flag, 'N') = 'N'
      AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
      AND lpe.business_group_id = ota_general.get_business_group_id
      AND oev.activity_version_id = lpm.activity_version_id
      AND lpe.learning_path_id = lpm.learning_path_id;