APPS.OTA_CME_UTIL dependencies on OTA_CERTIFICATION_MEMBERS

Line 331: FROM ota_certification_members cmb,

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

Line 552: FROM ota_certification_members cmb,

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

Line 631: (p_cmb_id IN ota_certification_members.certification_member_id%TYPE

627: -- |-----------------------< chk_if_cme_exists >------------------------------|
628: -- ----------------------------------------------------------------------------
629: --
630: PROCEDURE chk_if_cme_exists
631: (p_cmb_id IN ota_certification_members.certification_member_id%TYPE
632: , p_return_status OUT NOCOPY VARCHAR2)
633: IS
634: --
635: --

Line 698: from ota_certification_members cmb

694: , cmb.OBJECT_TYPE
695: , cmb.MEMBER_SEQUENCE
696: , cmb.START_DATE_ACTIVE
697: , cmb.END_DATE_ACTIVE
698: from ota_certification_members cmb
699: where cmb.certification_id = p_certification_id
700: and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
701: and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'))
702: and cmb.OBJECT_TYPE = 'H'

Line 706: ota_certification_members cmb2

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

Line 719: FROM ota_certification_members cmb,

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

Line 728: from ota_certification_members cmb2

724: AND cme.member_status_code <> 'CANCELLED'
725: AND cmb.object_type = 'H'
726: and not exists (select
727: null
728: from ota_certification_members cmb2
729: where cmb2.OBJECT_ID = cmb.object_id
730: and cmb2.OBJECT_type = 'H'
731: and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE)
732: and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD')));

Line 896: ota_certification_members cmb,

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

Line 955: OTA_CERTIFICATION_MEMBERS cmb,

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
959: AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID

Line 973: OTA_CERTIFICATION_MEMBERS cmb,

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
977: AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID

Line 1015: OTA_CERTIFICATION_MEMBERS cmb,

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
1019: AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID

Line 1033: OTA_CERTIFICATION_MEMBERS cmb,

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
1037: AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID