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 237: ota_cert_mbr_enrollments cme,

233: cme.object_version_number,
234: cmb.certification_member_id,
235: cme.member_status_code
236: FROM ota_certification_members cmb,
237: ota_cert_mbr_enrollments cme,
238: ota_cert_prd_enrollments cpe,
239: ota_cert_enrollments cre
240: WHERE
241: cre.cert_enrollment_id = cpe.cert_enrollment_id

Line 256: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;

252:
253: l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
254: l_online_flag ota_category_usages.online_flag%type;
255: l_enroll_type ota_booking_status_types.type%TYPE;
256: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
257: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
258: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
259:
260: --variables to store old values

Line 257: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;

253: l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
254: l_online_flag ota_category_usages.online_flag%type;
255: l_enroll_type ota_booking_status_types.type%TYPE;
256: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
257: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
258: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
259:
260: --variables to store old values
261: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

Line 261: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

257: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
258: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
259:
260: --variables to store old values
261: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
262:
263: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
264: l_cert_prd_enrollment_ids varchar2(4000) := '';
265:

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

346: -- |----------------------< Update_cpe_status >--------------------------|
347: -- ---------------------------------------------------------------------------
348: --
349: -- This procedure will get called when a cme is Updated or Cancelled
350: Procedure Update_cpe_status( p_cert_mbr_enrollment_id IN ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE
351: ,p_cert_prd_enrollment_id OUT NOCOPY varchar2
352: ,p_completion_date in date default sysdate)
353: is
354:

Line 363: ota_cert_mbr_enrollments cme

359: cpe.cert_prd_enrollment_id,
360: cpe.period_status_code
361: FROM ota_cert_enrollments cre,
362: ota_cert_prd_enrollments cpe,
363: ota_cert_mbr_enrollments cme
364: WHERE cre.cert_enrollment_id = cpe.cert_enrollment_id
365: AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
366: AND cpe.period_status_code not in ('CANCELLED', 'EXPIRED')
367: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id

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

432:
433: -- ---------------------------------------------------------------------------
434: -- |----------------------< update_cme_status >------------------------------|
435: -- ---------------------------------------------------------------------------
436: PROCEDURE update_cme_status (p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
437: IS
438:
439: l_proc VARCHAR2(72) := g_package|| 'update_cme_status';
440:

Line 450: ota_cert_mbr_enrollments cme

446: cmb.object_id,
447: cmb.certification_member_id,
448: cme.member_status_code
449: FROM ota_certification_members cmb,
450: ota_cert_mbr_enrollments cme
451: WHERE cme.cert_member_id = cmb.certification_member_id
452: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
453: --AND cme.member_status_code <> 'CANCELLED';
454:

Line 456: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;

452: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
453: --AND cme.member_status_code <> 'CANCELLED';
454:
455: l_enroll_type ota_booking_status_types.type%TYPE;
456: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
457: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
458: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
459:
460: --variables to store old values

Line 457: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;

453: --AND cme.member_status_code <> 'CANCELLED';
454:
455: l_enroll_type ota_booking_status_types.type%TYPE;
456: l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE;
457: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
458: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
459:
460: --variables to store old values
461: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

Line 461: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;

457: l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE;
458: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
459:
460: --variables to store old values
461: l_old_member_status ota_cert_mbr_enrollments.member_status_code%TYPE;
462:
463: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
464: l_cert_prd_enrollment_ids varchar2(4000) := '';
465:

Line 530: from ota_cert_mbr_enrollments cme

526: v_proc varchar2(72) := g_package||'chk_if_cme_exists';
527: --
528: cursor sel_cme_exists is
529: select 'Y'
530: from ota_cert_mbr_enrollments cme
531: where cme.cert_member_id = p_cmb_id;
532: --
533: Begin
534: --

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

559: -- ----------------------------------------------------------------------------
560: -- |-----------------------< refresh_cme >------------------------------|
561: -- ----------------------------------------------------------------------------
562: --
563: procedure refresh_cme(p_cert_prd_enrollment_id in ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) IS
564:
565: --cpe csr
566: CURSOR csr_cpe IS
567: select

Line 594: from ota_cert_mbr_enrollments cme2,

590: and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'))
591: and cmb.OBJECT_TYPE = 'H'
592: and not exists (select
593: null
594: from ota_cert_mbr_enrollments cme2,
595: ota_certification_members cmb2
596: where cme2.cert_member_id = cmb2.certification_member_id
597: and cme2.cert_prd_enrollment_id = p_cert_prd_enrollment_id
598: and cmb2.object_id = cmb.object_id

Line 609: ota_cert_mbr_enrollments cme

605: cmb.certification_member_id,
606: cme.member_status_code,
607: cmb.object_id
608: FROM ota_certification_members cmb,
609: ota_cert_mbr_enrollments cme
610: WHERE
611: cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
612: AND cme.cert_member_id = cmb.certification_member_id
613: AND cme.member_status_code <> 'CANCELLED'

Line 670: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;

666: p_attribute19 VARCHAR2(150) := NULL;
667: p_attribute20 VARCHAR2(150) := NULL;
668:
669: rec_cpe csr_cpe%rowtype;
670: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
671: l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;
672:
673: Begin
674: hr_utility.set_location(' Entering:'||l_proc,10);

Line 671: l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;

667: p_attribute20 VARCHAR2(150) := NULL;
668:
669: rec_cpe csr_cpe%rowtype;
670: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
671: l_object_version_number ota_cert_mbr_enrollments.object_version_number%type;
672:
673: Begin
674: hr_utility.set_location(' Entering:'||l_proc,10);
675:

Line 689: ,flex_field_name => 'OTA_CERT_MBR_ENROLLMENTS'

685: hr_utility.set_location(' Step:'||l_proc,20);
686:
687: ota_utility.Get_Default_Value_Dff(
688: appl_short_name => 'OTA'
689: ,flex_field_name => 'OTA_CERT_MBR_ENROLLMENTS'
690: ,p_attribute_category => l_attribute_category
691: ,p_attribute1 => l_attribute1
692: ,p_attribute2 => l_attribute2
693: ,p_attribute3 => l_attribute3

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

765: WHEN others THEN
766: hr_utility.set_location('Leaving :'||l_proc,60);
767: END refresh_cme;
768:
769: Function chk_active_cme_enrl(p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
770: return varchar2
771: IS
772:
773: cursor csr_active_enrl is

Line 783: ota_cert_mbr_enrollments cme,

779: ota_delegate_bookings b,
780: ota_booking_status_types_VL s,
781: ota_cert_enrollments cre,
782: ota_cert_prd_enrollments cpe,
783: ota_cert_mbr_enrollments cme,
784: ota_certification_members cmb,
785: ota_offerings ofr,
786: ota_category_usages c
787: WHERE e.event_id = b.event_id