APPS.OTA_LRNG_PATH_MEMBER_UTIL SQL Statements

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

Line: 22

SELECT DECODE(bst.type,'C','Z',bst.type) status,
       bst.name
  FROM ota_learning_path_members lpm,
       ota_lp_member_enrollments lme,
       ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types_vl bst
 WHERE lpm.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 lme.learning_path_member_id = lpm.learning_path_member_id
   AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id
   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)
		 )
 ORDER BY status, evt.course_start_date;

Line: 41

SELECT DECODE(bst.type,'C','Z',bst.type) status,
       bst.name
  FROM ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types_vl bst
 WHERE evt.event_id = tdb.event_id
   AND bst.booking_status_type_id = tdb.booking_status_type_id
   AND evt.activity_version_id = p_activity_version_id
    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)
		 )
 ORDER BY status, evt.course_start_date;

Line: 108

SELECT DECODE(bst.type,'C','Z',bst.type) status,
       tdb.date_status_changed
  FROM ota_learning_path_members lpm,
       ota_lp_member_enrollments lme,
       ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types bst
 WHERE lpm.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 lme.learning_path_member_id = lpm.learning_path_member_id
   AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id
   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)
		 )
 ORDER BY status, evt.course_start_date;

Line: 126

SELECT DECODE(bst.type,'C','Z',bst.type) status,
       tdb.date_status_changed
  FROM ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types_vl bst
 WHERE evt.event_id = tdb.event_id
   AND bst.booking_status_type_id = tdb.booking_status_type_id
   AND evt.activity_version_id = p_activity_version_id
    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)
		 )
 ORDER BY status, evt.course_start_date;

Line: 179

SELECT NULL
  FROM ota_events e,
       ota_activity_versions a,
       ota_delegate_bookings b,
       ota_booking_status_types s,
       ota_learning_path_members lpm
 WHERE e.event_id = b.event_id
   AND lpm.activity_version_id = a.activity_version_id
    AND e.activity_version_id = a.activity_version_id
    AND b.booking_status_type_id = s.booking_status_type_id
    AND ((p_person_id IS NOT NULL AND b.delegate_person_id = p_person_id)
                   OR (p_contact_id IS NOT NULL AND b.delegate_contact_id = p_contact_id)
		 )
    AND lpm.learning_path_member_id = p_learning_path_member_id;

Line: 236

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

SELECT count(lp_member_enrollment_id)
  FROM ota_lp_member_enrollments
 WHERE learning_path_section_id = p_learning_path_section_id
   AND member_status_code = 'COMPLETED';

Line: 358

SELECT completion_type_code
  FROM ota_lp_sections lpc,
       ota_learning_path_members lpm
 WHERE lpc.learning_path_section_id = lpm.learning_path_section_id
   AND lpm.learning_path_member_id = p_learning_path_member_id;

Line: 410

PROCEDURE update_lme_enroll_status_chg (p_event_id           IN ota_events.event_id%TYPE,
                                        p_person_id          IN ota_lp_enrollments.person_id%TYPE,
    				        p_contact_id         IN ota_lp_enrollments.contact_id%TYPE,
                                        p_lp_enrollment_ids  OUT NOCOPY varchar2)
IS

l_proc  VARCHAR2(72) :=      g_package|| 'update_lme_enroll_status_chg';

Line: 420

  SELECT activity_version_id
    FROM ota_events
   WHERE event_id = p_event_id;

Line: 426

  SELECT olme.lp_member_enrollment_id,
         olpe.lp_enrollment_id,
         olme.object_version_number,
         olpm.learning_path_section_id,
         olpm.learning_path_member_id,
         olpe.no_of_completed_courses,
         olpe.no_of_mandatory_courses,
         olme.member_status_code
    FROM ota_learning_path_members olpm,
         ota_lp_member_enrollments olme,
         ota_lp_enrollments olpe
   WHERE olpe.learning_path_id = olpm.learning_path_id
     AND olpm.learning_path_member_id = olme.learning_path_member_id
     AND olpe.lp_enrollment_id = olme.lp_enrollment_id
     AND (( p_person_id IS NOT NULL AND olpe.person_id = p_person_id)
                OR (p_contact_id IS NOT NULL AND olpe.contact_id = p_contact_id))
     AND olpm.activity_version_id = csr_activity_version_id
     AND olme.member_status_code <> 'CANCELLED';

Line: 497

		 ota_lp_member_enrollment_api.update_lp_member_enrollment
                        (p_effective_date           => sysdate
                        ,p_object_version_number    => rec.object_version_number
                        ,p_learning_path_member_id  => rec.learning_path_member_id
                        ,p_lp_enrollment_id         => rec.lp_enrollment_id
                        ,p_lp_member_enrollment_id  => rec.lp_member_enrollment_id
                        ,p_member_status_code       => l_member_status_code
                        ,p_completion_date          => l_completion_date);

Line: 537

        Update_lpe_lme_change(rec.lp_member_enrollment_id, l_completed_courses, p_lp_enrollment_ids);

Line: 547

END update_lme_enroll_status_chg;

Line: 554

Procedure Update_lpe_lme_change( p_lp_member_enrollment_id    ota_lp_member_enrollments.lp_member_enrollment_id%TYPE)
is

CURSOR csr_lpe_with_lme
    IS
    SELECT lpe.lp_enrollment_id,
           lpe.path_status_code
      FROM ota_lp_enrollments lpe,
           ota_lp_member_enrollments lme
     WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
       AND lpe.path_status_code <> 'CANCELLED'
       AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;

Line: 567

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

Line: 593

              OPEN csr_lpe_update(rec1.lp_enrollment_id);

Line: 594

              FETCH csr_lpe_update into l_object_version_number;

Line: 595

              IF csr_lpe_update%FOUND then
			     CLOSE csr_lpe_update;

Line: 597

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

Line: 604

                  CLOSE csr_lpe_update;

Line: 608

END Update_lpe_lme_change;

Line: 611

Procedure Update_lpe_lme_change (p_lp_member_enrollment_id  ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
                                 p_no_of_completed_courses  ota_lp_enrollments.no_of_completed_courses%TYPE,
                                 p_lp_enrollment_ids        OUT NOCOPY VARCHAR2)
is

CURSOR csr_lpe_with_lme
    IS
    SELECT lpe.lp_enrollment_id,
           lpe.path_status_code,
           lpe.learning_path_id,
           lpe.no_of_mandatory_courses
      FROM ota_lp_enrollments lpe,
           ota_lp_member_enrollments lme
     WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
       AND lpe.path_status_code <> 'CANCELLED'
       AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;

Line: 629

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

Line: 664

              OPEN csr_lpe_update(rec1.lp_enrollment_id);

Line: 665

              FETCH csr_lpe_update into l_object_version_number;

Line: 666

              IF csr_lpe_update%FOUND then
			     CLOSE csr_lpe_update;

Line: 673

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

Line: 682

                  CLOSE csr_lpe_update;

Line: 686

END Update_lpe_lme_change;

Line: 749

  SELECT lps.learning_path_id,
         lpe.lp_enrollment_id,
         lpc.learning_path_section_id
    FROM ota_learning_paths  lps,
         ota_lp_enrollments lpe,
         ota_lp_sections lpc
   WHERE lps.learning_path_id = lpe.learning_path_id
     AND lpc.learning_path_id = lps.learning_path_id
     AND lps.path_source_code = 'TALENT_MGMT'
     AND lps.source_function_code = p_source_function_code
     AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND lps.source_id = p_source_id))
     AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND lps.assignment_id = p_assignment_id));

Line: 763

  SELECT lp_enrollment_id
    FROM ota_lp_enrollments
   WHERE learning_path_id = l_learning_path_id
     AND person_id = p_person_id;

Line: 769

  SELECT learning_path_section_id
    FROM ota_lp_sections
   WHERE learning_path_id = l_learning_path_id
     AND completion_type_code = 'M';

Line: 775

  SELECT learning_path_member_id
    FROM ota_learning_path_members
   WHERE learning_path_section_id = l_learning_path_section_id
     AND activity_version_id = p_activity_version_id;

Line: 997

PROCEDURE update_talent_mgmt_lp
  (p_validate                     IN     NUMBER    DEFAULT hr_api.g_false_num
  ,p_effective_date               IN     DATE
  ,p_mode                         IN     VARCHAR2
  ,p_learning_path_id             IN     NUMBER    DEFAULT NULL
  ,p_lp_enrollment_id             IN     NUMBER    DEFAULT NULL
  ,p_source_function_code	  IN     VARCHAR2
  ,p_assignment_id		  IN 	 NUMBER    DEFAULT NULL
  ,p_source_id		   	  IN 	 NUMBER    DEFAULT NULL
  ,p_person_id			  IN     NUMBER
  ,p_display_to_learner_flag      IN     VARCHAR2
  ,p_lps_ovn                      IN OUT NOCOPY NUMBER
  ,p_lpe_ovn                      IN OUT NOCOPY NUMBER
  ,p_return_status                OUT NOCOPY VARCHAR2
  ) IS
  --
  -- Variables for API Boolean parameters
  l_validate                      boolean;

Line: 1037

  l_proc                         varchar2(72) := g_package ||'update_talent_mgmt_lp';

Line: 1040

  SELECT lps.learning_path_id,
         lps.object_version_number lps_ovn,
         lpe.lp_enrollment_id,
         lpe.object_version_number lpe_ovn
    FROM ota_learning_paths  lps,
         ota_lp_enrollments lpe
   WHERE lps.learning_path_id = lpe.learning_path_id
     AND lps.path_source_code = 'TALENT_MGMT'
     AND lps.source_function_code = p_source_function_code
     AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND lps.source_id = p_source_id))
     AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND lps.assignment_id = p_assignment_id));

Line: 1053

  SELECT lme.lp_member_enrollment_id,
         lme.object_version_number,
         lme.learning_path_member_id,
         lme.lp_enrollment_id
    FROM ota_lp_member_enrollments lme,
         ota_learning_path_members lpm
   WHERE lme.learning_path_member_id = lpm.learning_path_member_id
     AND lpm.learning_path_id = l_learning_path_id
     AND lme.member_status_code = 'AWAITING_APPROVAL';

Line: 1064

  SELECT lme.lp_member_enrollment_id,
         lme.object_version_number
    FROM ota_lp_member_enrollments lme,
         ota_learning_path_members lpm
   WHERE lme.learning_path_member_id = lpm.learning_path_member_id
     AND lpm.learning_path_id = l_learning_path_id
     AND lme.member_status_code <> 'CANCELLED';

Line: 1073

   SELECT ota_lrng_path_util.get_no_of_mandatory_courses(lp_id,'TALENT_MGMT') mandatory_courses,
          ota_lrng_path_util.get_no_of_completed_courses(lpe_id,'TALENT_MGMT') completed_courses
     FROM dual;

Line: 1083

  SAVEPOINT update_talent_mgmt_lp;

Line: 1115

         ota_learning_path_swi.update_learning_path
          (p_effective_date               => 	p_effective_date
          ,p_learning_path_id             =>    l_learning_path_id
          ,p_object_version_number        =>    l_lp_ovn
          ,p_display_to_learner_flag      =>    p_display_to_learner_flag
          ,p_validate                     =>	p_validate
          ,p_return_status                =>    l_lp_rtn_status
          );

Line: 1127

              ROLLBACK TO update_talent_mgmt_lp;

Line: 1132

         ota_lp_enrollment_swi.update_lp_enrollment
          (p_effective_date               => 	p_effective_date
          ,p_lp_enrollment_id             =>    l_lp_enrollment_id
          ,p_object_version_number        =>    l_lpe_ovn
          ,p_path_status_code             =>    l_path_status_code
          ,p_return_status                =>    l_lpe_rtn_status
          );

Line: 1142

              ROLLBACK TO update_talent_mgmt_lp;

Line: 1155

              ota_lp_member_enrollment_swi.update_lp_member_enrollment
               (p_effective_date               =>    p_effective_date
               ,p_lp_member_enrollment_id      =>    appr_rec.lp_member_enrollment_id
               ,p_lp_enrollment_id             =>    appr_rec.lp_enrollment_id
               ,p_learning_path_member_id      =>    appr_rec.learning_path_member_id
               ,p_object_version_number        =>    l_lme_ovn
               ,p_validate                     =>    p_validate
               ,p_member_status_code           =>    l_member_status_code
               ,p_return_status                =>    l_lme_rtn_status
               );

Line: 1167

                  ROLLBACK TO update_talent_mgmt_lp;

Line: 1185

         ota_lp_enrollment_swi.update_lp_enrollment
          (p_effective_date               => 	p_effective_date
          ,p_lp_enrollment_id             =>    l_lp_enrollment_id
          ,p_object_version_number        =>    l_lpe_ovn
          ,p_no_of_mandatory_courses      =>    l_no_of_mandatory_courses
          ,p_no_of_completed_courses      =>    l_no_of_completed_courses
          ,p_path_status_code             =>    l_path_status_code
          ,p_completion_date              =>    l_completion_date
          ,p_return_status                =>    l_lpe_rtn_status
          );

Line: 1199

              ROLLBACK TO update_talent_mgmt_lp;

Line: 1214

              ota_lp_member_enrollment_swi.update_lp_member_enrollment
               (p_effective_date               =>    p_effective_date
               ,p_lp_member_enrollment_id      =>    cncl_rec.lp_member_enrollment_id
               ,p_object_version_number        =>    l_lme_ovn
               ,p_validate                     =>    p_validate
               ,p_member_status_code           =>    l_member_status_code
               ,p_return_status                =>    l_lme_rtn_status
               );

Line: 1224

                  ROLLBACK TO update_talent_mgmt_lp;

Line: 1246

    ROLLBACK TO update_talent_mgmt_lp;

Line: 1261

    ROLLBACK TO update_talent_mgmt_lp;

Line: 1271

END update_talent_mgmt_lp;

Line: 1288

SELECT lpm.learning_path_section_id,
       lpc.no_of_mandatory_courses
  FROM ota_learning_path_members lpm,
       ota_lp_sections lpc
 WHERE lpm.learning_path_section_id = lpc.learning_path_section_id
   AND lpc.completion_type_code = 'S'
   AND lpm.learning_path_member_id = p_learning_path_member_id;

Line: 1297

SELECT count(learning_path_member_id)
  FROM ota_learning_path_members
 WHERE learning_path_section_id = l_lpc_id;