eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_CME_UTIL dependencies on OTA_CERT_MBR_ENROLLMENTS

Line 332: ota_cert_mbr_enrollments cme,

328: cme.object_version_number,
329: cmb.certification_member_id,
330: cme.member_status_code
331: FROM ota_certification_members cmb,
332: ota_cert_mbr_enrollments cme,
333: ota_cert_prd_enrollments cpe,
334: ota_cert_enrollments cre
335: WHERE
336: cre.cert_enrollment_id = cpe.cert_enrollment_id

Line 351: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;

347:
348: l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
349: l_online_flag ota_category_usages.online_flag%type;
350: l_enroll_type ota_booking_status_types.type%TYPE;
351: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
352: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
353: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
354:
355: --variables to store old values

Line 352: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;

348: l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
349: l_online_flag ota_category_usages.online_flag%type;
350: l_enroll_type ota_booking_status_types.type%TYPE;
351: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
352: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
353: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
354:
355: --variables to store old values
356: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

Line 356: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

352: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
353: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
354:
355: --variables to store old values
356: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
357:
358: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
359: l_cert_prd_enrollment_ids varchar2(4000) := '';
360: l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;

Line 453: Procedure Update_cpe_status( p_cert_mbr_enrollment_id IN ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE

449: -- |----------------------< Update_cpe_status >--------------------------|
450: -- ---------------------------------------------------------------------------
451: --
452: -- This procedure will get called when a cme is Updated or Cancelled
453: Procedure Update_cpe_status( p_cert_mbr_enrollment_id IN ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE
454: ,p_cert_prd_enrollment_id OUT NOCOPY varchar2
455: ,p_completion_date in date default sysdate)
456: is
457:

Line 466: ota_cert_mbr_enrollments cme

462: cpe.cert_prd_enrollment_id,
463: cpe.period_status_code
464: FROM ota_cert_enrollments cre,
465: ota_cert_prd_enrollments cpe,
466: ota_cert_mbr_enrollments cme
467: WHERE cre.cert_enrollment_id = cpe.cert_enrollment_id
468: AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
469: AND cpe.period_status_code not in ('CANCELLED', 'EXPIRED')
470: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id

Line 539: PROCEDURE update_cme_status (p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)

535:
536: -- ---------------------------------------------------------------------------
537: -- |----------------------< update_cme_status >------------------------------|
538: -- ---------------------------------------------------------------------------
539: PROCEDURE update_cme_status (p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
540: IS
541:
542: l_proc VARCHAR2(72) := g_package|| 'update_cme_status';
543:

Line 553: ota_cert_mbr_enrollments cme

549: cmb.object_id,
550: cmb.certification_member_id,
551: cme.member_status_code
552: FROM ota_certification_members cmb,
553: ota_cert_mbr_enrollments cme
554: WHERE cme.cert_member_id = cmb.certification_member_id
555: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
556: --AND cme.member_status_code <> 'CANCELLED';
557:

Line 559: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;

555: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
556: --AND cme.member_status_code <> 'CANCELLED';
557:
558: l_enroll_type ota_booking_status_types.type%TYPE;
559: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
560: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
561: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
562:
563: --variables to store old values

Line 560: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;

556: --AND cme.member_status_code <> 'CANCELLED';
557:
558: l_enroll_type ota_booking_status_types.type%TYPE;
559: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
560: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
561: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
562:
563: --variables to store old values
564: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

Line 564: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

560: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
561: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
562:
563: --variables to store old values
564: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
565:
566: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
567: l_cert_prd_enrollment_ids varchar2(4000) := '';
568:

Line 641: from ota_cert_mbr_enrollments cme

637: v_proc varchar2(72) := g_package||'chk_if_cme_exists';
638: --
639: cursor sel_cme_exists is
640: select 'Y'
641: from ota_cert_mbr_enrollments cme
642: where cme.cert_member_id = p_cmb_id;
643: --
644: Begin
645: --

Line 674: procedure refresh_cme(p_cert_prd_enrollment_id in ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) IS

670: -- ----------------------------------------------------------------------------
671: -- |-----------------------< refresh_cme >------------------------------|
672: -- ----------------------------------------------------------------------------
673: --
674: procedure refresh_cme(p_cert_prd_enrollment_id in ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) IS
675:
676: --cpe csr
677: CURSOR csr_cpe IS
678: select

Line 705: from ota_cert_mbr_enrollments cme2,

701: and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'))
702: and cmb.OBJECT_TYPE = 'H'
703: and not exists (select
704: null
705: from ota_cert_mbr_enrollments cme2,
706: ota_certification_members cmb2
707: where cme2.cert_member_id = cmb2.certification_member_id
708: and cme2.cert_prd_enrollment_id = p_cert_prd_enrollment_id
709: and cmb2.object_id = cmb.object_id

Line 720: ota_cert_mbr_enrollments cme

716: cmb.certification_member_id,
717: cme.member_status_code,
718: cmb.object_id
719: FROM ota_certification_members cmb,
720: ota_cert_mbr_enrollments cme
721: WHERE
722: cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
723: AND cme.cert_member_id = cmb.certification_member_id
724: AND cme.member_status_code <> 'CANCELLED'

Line 781: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;

777: p_attribute19 VARCHAR2(150) := NULL;
778: p_attribute20 VARCHAR2(150) := NULL;
779:
780: rec_cpe csr_cpe%rowtype;
781: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
782: l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;
783:
784: Begin
785: hr_utility.set_location(' Entering:'||l_proc,10);

Line 782: l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;

778: p_attribute20 VARCHAR2(150) := NULL;
779:
780: rec_cpe csr_cpe%rowtype;
781: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
782: l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;
783:
784: Begin
785: hr_utility.set_location(' Entering:'||l_proc,10);
786:

Line 800: ,flex_field_name => 'OTA_CERT_MBR_ENROLLMENTS'

796: hr_utility.set_location(' Step:'||l_proc,20);
797:
798: ota_utility.Get_Default_Value_Dff(
799: appl_short_name => 'OTA'
800: ,flex_field_name => 'OTA_CERT_MBR_ENROLLMENTS'
801: ,p_attribute_category => l_attribute_category
802: ,p_attribute1 => l_attribute1
803: ,p_attribute2 => l_attribute2
804: ,p_attribute3 => l_attribute3

Line 881: Function chk_active_cme_enrl(p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)

877: WHEN others THEN
878: hr_utility.set_location('Leaving :'||l_proc,60);
879: END refresh_cme;
880:
881: Function chk_active_cme_enrl(p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
882: return varchar2
883: IS
884:
885: cursor csr_active_enrl is

Line 895: ota_cert_mbr_enrollments cme,

891: ota_delegate_bookings b,
892: ota_booking_status_types_VL s,
893: ota_cert_enrollments cre,
894: ota_cert_prd_enrollments cpe,
895: ota_cert_mbr_enrollments cme,
896: ota_certification_members cmb,
897: ota_offerings ofr,
898: ota_category_usages c
899: WHERE e.event_id = b.event_id

Line 948: return ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type IS

944:
945: function get_cert_mbr_enroll_id(p_event_id IN ota_events.event_id%type,
946: p_person_id IN ota_cert_enrollments.person_id%type,
947: p_contact_id ota_cert_enrollments.contact_id%type)
948: return ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type IS
949:
950: CURSOR csr_cert_mbr_enrl_id_person IS
951: SELECT max(cme.cert_mbr_enrollment_id) cert_mbr_enrollment_id
952: FROM OTA_CERT_ENROLLMENTS cre,

Line 954: OTA_CERT_MBR_ENROLLMENTS cme,

950: CURSOR csr_cert_mbr_enrl_id_person IS
951: SELECT max(cme.cert_mbr_enrollment_id) cert_mbr_enrollment_id
952: FROM OTA_CERT_ENROLLMENTS cre,
953: OTA_CERT_PRD_ENROLLMENTS cpe,
954: OTA_CERT_MBR_ENROLLMENTS cme,
955: OTA_CERTIFICATION_MEMBERS cmb,
956: OTA_EVENTS evt
957: WHERE cre.person_id = p_person_id
958: AND cre.cert_enrollment_id = cpe.cert_enrollment_id

Line 972: OTA_CERT_MBR_ENROLLMENTS cme,

968: CURSOR csr_cert_mbr_enrl_id_contact IS
969: SELECT max(cme.cert_mbr_enrollment_id) cert_mbr_enrollment_id
970: FROM OTA_CERT_ENROLLMENTS cre,
971: OTA_CERT_PRD_ENROLLMENTS cpe,
972: OTA_CERT_MBR_ENROLLMENTS cme,
973: OTA_CERTIFICATION_MEMBERS cmb,
974: OTA_EVENTS evt
975: WHERE cre.contact_id = p_contact_id
976: AND cre.cert_enrollment_id = cpe.cert_enrollment_id

Line 986: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;

982: AND cmb.OBJECT_TYPE = 'H'
983: AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
984: AND cme.cert_member_id = cmb.certification_member_id;
985:
986: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
987:
988: BEGIN
989:
990: if p_person_id is not null then

Line 1014: OTA_CERT_MBR_ENROLLMENTS cme,

1010: CURSOR csr_cert_prd_enrl_id_person IS
1011: SELECT max(cme.cert_prd_enrollment_id) cert_prd_enrollment_id
1012: FROM OTA_CERT_ENROLLMENTS cre,
1013: OTA_CERT_PRD_ENROLLMENTS cpe,
1014: OTA_CERT_MBR_ENROLLMENTS cme,
1015: OTA_CERTIFICATION_MEMBERS cmb,
1016: OTA_EVENTS evt
1017: WHERE cre.person_id = p_person_id
1018: AND cre.cert_enrollment_id = cpe.cert_enrollment_id

Line 1032: OTA_CERT_MBR_ENROLLMENTS cme,

1028: CURSOR csr_cert_prd_enrl_id_contact IS
1029: SELECT max(cme.cert_prd_enrollment_id) cert_prd_enrollment_id
1030: FROM OTA_CERT_ENROLLMENTS cre,
1031: OTA_CERT_PRD_ENROLLMENTS cpe,
1032: OTA_CERT_MBR_ENROLLMENTS cme,
1033: OTA_CERTIFICATION_MEMBERS cmb,
1034: OTA_EVENTS evt
1035: WHERE cre.contact_id = p_contact_id
1036: AND cre.cert_enrollment_id = cpe.cert_enrollment_id