APPS.OTA_CME_UTIL SQL Statements

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

Line: 15

SELECT DECODE(bst.type,'C','Z',bst.type) status,
       evt.event_type,
       tdb.DATE_STATUS_CHANGED,
       evt.COURSE_START_DATE,
       evt.COURSE_END_DATE
  FROM ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types bst
 WHERE evt.event_id = tdb.event_id
   AND bst.booking_status_type_id = tdb.booking_status_type_id
   and (
   --sync sched, online(conf) or offline(ILT)
   --sync always have an end date
      ( evt.event_type = 'SCHEDULED' and
        evt.course_start_date >= csr_cert_period_start_date and
          evt.course_end_date <= csr_cert_period_end_date )
       or
   --async selfpaced, online(selfp) or offline(CBT)
   --async have opt end date
   (event_type ='SELFPACED'  and
     (csr_cert_period_end_date >= evt.course_start_date) AND
       ((evt.course_end_date is null) or
        (evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))))
   AND evt.activity_version_id = csr_activity_version_id
    AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
                   OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
                 )
    order by status;

Line: 45

PROCEDURE get_enrl_status_on_update(p_activity_version_id    IN ota_activity_versions.activity_version_id%TYPE,
                               p_cert_prd_enrollment_id  IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
			       p_booking_status_type     OUT NOCOPY ota_booking_status_types.type%TYPE,
                               p_date_status_changed     OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE)
IS

CURSOR csr_cert_enrl IS
SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
 FROM ota_cert_enrollments cre,
      ota_cert_prd_enrollments cpe
 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
   and cpe.cert_enrollment_id = cre.cert_enrollment_id;

Line: 58

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

Line: 99

END get_enrl_status_on_update;

Line: 112

SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
 FROM ota_cert_enrollments cre,
      ota_cert_prd_enrollments cpe
 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
   and cpe.cert_enrollment_id = cre.cert_enrollment_id;

Line: 211

PROCEDURE update_cme_status (p_event_id           IN ota_events.event_id%TYPE,
                                        p_person_id          IN ota_cert_enrollments.person_id%TYPE,
    				        p_contact_id         IN ota_cert_enrollments.contact_id%TYPE,
                                        p_cert_prd_enrollment_ids  OUT NOCOPY varchar2)
IS

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

Line: 220

    SELECT evt.activity_version_id,
           ocu.online_flag
      FROM ota_events evt,
           ota_offerings ofr,
           ota_category_usages ocu
     WHERE evt.event_id = p_event_id
       AND evt.parent_offering_id = ofr.offering_id
       AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;

Line: 231

SELECT cme.cert_mbr_enrollment_id,
       cpe.cert_prd_enrollment_id,
       cme.object_version_number,
       cmb.certification_member_id,
       cme.member_status_code
  FROM ota_certification_members cmb,
       ota_cert_mbr_enrollments cme,
       ota_cert_prd_enrollments cpe,
       ota_cert_enrollments cre
 WHERE
        cre.cert_enrollment_id = cpe.cert_enrollment_id
    AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
    AND cme.cert_member_id = cmb.certification_member_id
    AND cmb.object_id = csr_activity_version_id
    AND (( p_person_id IS NOT NULL AND cre.person_id = p_person_id)
                OR (p_contact_id IS NOT NULL AND cre.contact_id = p_contact_id))
    AND cme.member_status_code <> 'CANCELLED'
    --pull only curr periods
    AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
    -- don't consider expired prds
    AND cpe.period_status_code <> 'EXPIRED';

Line: 282

              get_enrl_status_on_update(p_activity_version_id     => l_activity_version_id,
                               p_cert_prd_enrollment_id  => rec_cme_info.cert_prd_enrollment_id,
                               p_booking_status_type     => l_enroll_type,
                               p_date_status_changed     => l_date_status_changed);

Line: 306

	        ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
                        (p_effective_date           => sysdate
                        ,p_object_version_number    => rec_cme_info.object_version_number
                        ,p_cert_member_id           => rec_cme_info.certification_member_id
                        ,p_cert_prd_enrollment_id   => rec_cme_info.cert_prd_enrollment_id
                        ,p_cert_mbr_enrollment_id   => rec_cme_info.cert_mbr_enrollment_id
                        ,p_member_status_code       => l_member_status_code
                        ,p_completion_date          => l_completion_date);

Line: 316

                Update_cpe_status(rec_cme_info.cert_mbr_enrollment_id, l_cert_prd_enrollment_id);

Line: 343

END update_cme_status;

Line: 350

Procedure Update_cpe_status( p_cert_mbr_enrollment_id    IN ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE
                             ,p_cert_prd_enrollment_id    OUT NOCOPY varchar2
                             ,p_completion_date in date default sysdate)
is

CURSOR csr_cpe_cme
    IS
    SELECT cre.certification_id,
           cpe.cert_enrollment_id,
           cpe.cert_prd_enrollment_id,
           cpe.period_status_code
      FROM ota_cert_enrollments cre,
           ota_cert_prd_enrollments cpe,
           ota_cert_mbr_enrollments cme
     WHERE cre.cert_enrollment_id = cpe.cert_enrollment_id
       AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
       AND cpe.period_status_code not in ('CANCELLED', 'EXPIRED')
       AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
       AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date);

Line: 372

    SELECT cpe.period_status_code
      FROM ota_cert_prd_enrollments cpe
     WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;

Line: 377

CURSOR csr_cpe_update(csr_cert_prd_enrollment_id number)
    IS
    SELECT cpe.object_version_number
      FROM ota_cert_prd_enrollments cpe
     WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;

Line: 383

CURSOR csr_cre_update(csr_cert_enrollment_id number)
    IS
    SELECT cre.object_version_number
      FROM ota_cert_enrollments cre
     where cre.cert_enrollment_id = csr_cert_enrollment_id;

Line: 402

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

Line: 404

l_child_update_flag varchar2(1) := 'N';

Line: 411

         ota_cpe_util.update_cpe_status(rec_cpe_cme.cert_prd_enrollment_id, l_certification_status_code, null, null, l_child_update_flag, p_completion_date);

Line: 431

END Update_cpe_status;

Line: 436

PROCEDURE update_cme_status (p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
IS

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

Line: 443

SELECT cme.cert_mbr_enrollment_id,
       cme.cert_prd_enrollment_id,
       cme.object_version_number,
       cmb.object_id,
       cmb.certification_member_id,
       cme.member_status_code
  FROM ota_certification_members cmb,
       ota_cert_mbr_enrollments cme
 WHERE cme.cert_member_id = cmb.certification_member_id
    AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;

Line: 476

        get_enrl_status_on_update(p_activity_version_id     => rec_cme_info.object_id,
                               p_cert_prd_enrollment_id  => rec_cme_info.cert_prd_enrollment_id,
                               p_booking_status_type     => l_enroll_type,
                               p_date_status_changed     => l_date_status_changed);

Line: 496

	        ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
                        (p_effective_date           => sysdate
                        ,p_object_version_number    => rec_cme_info.object_version_number
                        ,p_cert_member_id           => rec_cme_info.certification_member_id
                        ,p_cert_prd_enrollment_id   => rec_cme_info.cert_prd_enrollment_id
                        ,p_cert_mbr_enrollment_id   => rec_cme_info.cert_mbr_enrollment_id
                        ,p_member_status_code       => l_member_status_code
                        ,p_completion_date          => l_completion_date);

Line: 513

END update_cme_status;

Line: 529

    select 'Y'
      from ota_cert_mbr_enrollments cme
     where cme.cert_member_id = p_cmb_id;

Line: 567

select
       cre.certification_id,
       cpe.business_group_id
FROM ota_cert_enrollments cre,
     ota_cert_prd_enrollments cpe,
     ota_certifications_b crt
where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
  and cre.certification_id = crt.certification_id
  and cpe.cert_enrollment_id = cre.cert_enrollment_id;

Line: 579

select
  cmb.CERTIFICATION_MEMBER_ID
, cmb.CERTIFICATION_ID
, cmb.OBJECT_ID
, cmb.OBJECT_TYPE
, cmb.MEMBER_SEQUENCE
, cmb.START_DATE_ACTIVE
, cmb.END_DATE_ACTIVE
 from ota_certification_members cmb
where cmb.certification_id = p_certification_id
and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'))
and cmb.OBJECT_TYPE = 'H'
and not exists (select
                  null
                  from ota_cert_mbr_enrollments cme2,
                       ota_certification_members cmb2
                 where cme2.cert_member_id = cmb2.certification_member_id
                   and cme2.cert_prd_enrollment_id = p_cert_prd_enrollment_id
                   and cmb2.object_id = cmb.object_id
                   and cmb2.OBJECT_TYPE = 'H');

Line: 603

SELECT cme.cert_mbr_enrollment_id,
       cme.object_version_number,
       cmb.certification_member_id,
       cme.member_status_code,
       cmb.object_id
  FROM ota_certification_members cmb,
       ota_cert_mbr_enrollments cme
 WHERE
        cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
    AND cme.cert_member_id = cmb.certification_member_id
    AND cme.member_status_code <> 'CANCELLED'
    AND cmb.object_type = 'H'
    and not exists (select
              null
              from ota_certification_members cmb2
             where cmb2.OBJECT_ID = cmb.object_id
               and cmb2.OBJECT_type = 'H'
               and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
               and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD')));

Line: 753

      ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
			    (p_effective_date           => sysdate
			    ,p_object_version_number    => end_crs.object_version_number
			    ,p_cert_member_id           => end_crs.certification_member_id
			    ,p_cert_prd_enrollment_id   => p_cert_prd_enrollment_id
			    ,p_cert_mbr_enrollment_id   => end_crs.cert_mbr_enrollment_id
			    ,p_member_status_code       => 'CANCELLED');

Line: 774

SELECT
           s.type                          Enrollment_Status_Type
FROM       ota_events e,
           ota_events_tl et,
           ota_activity_versions a,
           ota_delegate_bookings b,
           ota_booking_status_types_VL s,
           ota_cert_enrollments cre,
           ota_cert_prd_enrollments cpe,
           ota_cert_mbr_enrollments cme,
           ota_certification_members cmb,
           ota_offerings ofr,
           ota_category_usages c
WHERE   e.event_id = b.event_id
    AND cre.cert_enrollment_id = cpe.cert_enrollment_id
    AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
    AND e.event_id= et.event_id
    AND s.type <> 'C'
    AND et.language = USERENV('LANG')
    AND cme.cert_member_id = cmb.certification_member_id
    AND cmb.object_id = a.activity_version_id
    AND cmb.object_type = 'H'
    AND e.parent_offering_id = ofr.offering_id
    AND e.activity_version_id = a.activity_version_id
    AND b.booking_status_type_id = s.booking_status_type_id
    AND ((cre.person_id IS NOT NULL AND b.delegate_person_id = cre.person_id) OR (cre.CONTACT_ID IS NOT NULL AND b.delegate_contact_id = cre.contact_id))
    AND E.PARENT_OFFERING_ID=OFR.OFFERING_ID
    AND OFR.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
    AND      cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
    AND ((cre.person_id IS NOT NULL AND b.delegate_person_id = cre.person_id) OR (cre.CONTACT_ID IS NOT NULL AND b.delegate_contact_id = cre.contact_id))
    AND ( ( e.course_start_date >= cert_period_start_date
            and nvl(e.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= cert_period_end_date )
           or (event_type ='SELFPACED'  and    ((cert_period_end_date >= e.course_start_date)
           AND     ((e.course_end_date is null) or     (e.course_end_date IS NOT NULL AND e.course_end_date >= cert_period_start_date)))));