APPS.OTA_CME_UTIL dependencies on OTA_CERTIFICATION_MEMBERS

Line 236: FROM ota_certification_members cmb,

232: cpe.cert_prd_enrollment_id,
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

Line 449: FROM ota_certification_members cmb,

445: cme.object_version_number,
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';

Line 520: (p_cmb_id IN ota_certification_members.certification_member_id%TYPE

516: -- |-----------------------< chk_if_cme_exists >------------------------------|
517: -- ----------------------------------------------------------------------------
518: --
519: PROCEDURE chk_if_cme_exists
520: (p_cmb_id IN ota_certification_members.certification_member_id%TYPE
521: , p_return_status OUT NOCOPY VARCHAR2)
522: IS
523: --
524: --

Line 587: from ota_certification_members cmb

583: , cmb.OBJECT_TYPE
584: , cmb.MEMBER_SEQUENCE
585: , cmb.START_DATE_ACTIVE
586: , cmb.END_DATE_ACTIVE
587: from ota_certification_members cmb
588: where cmb.certification_id = p_certification_id
589: and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
590: and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'))
591: and cmb.OBJECT_TYPE = 'H'

Line 595: ota_certification_members cmb2

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
599: and cmb2.OBJECT_TYPE = 'H');

Line 608: FROM ota_certification_members cmb,

604: cme.object_version_number,
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

Line 617: from ota_certification_members cmb2

613: AND cme.member_status_code <> 'CANCELLED'
614: AND cmb.object_type = 'H'
615: and not exists (select
616: null
617: from ota_certification_members cmb2
618: where cmb2.OBJECT_ID = cmb.object_id
619: and cmb2.OBJECT_type = 'H'
620: and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
621: and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD')));

Line 784: ota_certification_members cmb,

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
788: AND cre.cert_enrollment_id = cpe.cert_enrollment_id