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_ENROLLMENTS

Line 13: csr_person_id in ota_cert_enrollments.person_id%TYPE,

9: --
10: CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
11: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
12: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
13: csr_person_id in ota_cert_enrollments.person_id%TYPE,
14: csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
15: SELECT DECODE(bst.type,'C','Z',bst.type) status,
16: evt.event_type,
17: tdb.DATE_STATUS_CHANGED,

Line 14: csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS

10: CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
11: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
12: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
13: csr_person_id in ota_cert_enrollments.person_id%TYPE,
14: csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
15: SELECT DECODE(bst.type,'C','Z',bst.type) status,
16: evt.event_type,
17: tdb.DATE_STATUS_CHANGED,
18: evt.COURSE_START_DATE,

Line 55: FROM ota_cert_enrollments cre,

51: IS
52:
53: CURSOR csr_cert_enrl IS
54: SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
55: FROM ota_cert_enrollments cre,
56: ota_cert_prd_enrollments cpe
57: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
58: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
59:

Line 62: l_person_id ota_cert_enrollments.person_id%TYPE;

58: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
59:
60: l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';
61:
62: l_person_id ota_cert_enrollments.person_id%TYPE;
63: l_contact_id ota_cert_enrollments.contact_id%TYPE;
64: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
65: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
66:

Line 63: l_contact_id ota_cert_enrollments.contact_id%TYPE;

59:
60: l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';
61:
62: l_person_id ota_cert_enrollments.person_id%TYPE;
63: l_contact_id ota_cert_enrollments.contact_id%TYPE;
64: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
65: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
66:
67: l_enroll_status VARCHAR2(30);

Line 112: FROM ota_cert_enrollments cre,

108: IS
109:
110: CURSOR csr_cert_enrl IS
111: SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
112: FROM ota_cert_enrollments cre,
113: ota_cert_prd_enrollments cpe
114: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
115: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
116:

Line 119: l_person_id ota_cert_enrollments.person_id%TYPE;

115: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
116:
117: l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';
118:
119: l_person_id ota_cert_enrollments.person_id%TYPE;
120: l_contact_id ota_cert_enrollments.contact_id%TYPE;
121: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
122: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
123:

Line 120: l_contact_id ota_cert_enrollments.contact_id%TYPE;

116:
117: l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';
118:
119: l_person_id ota_cert_enrollments.person_id%TYPE;
120: l_contact_id ota_cert_enrollments.contact_id%TYPE;
121: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
122: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
123:
124: l_enroll_status VARCHAR2(30);

Line 177: FROM ota_cert_enrollments cre,

173: IS
174:
175: CURSOR csr_cert_enrl IS
176: SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
177: FROM ota_cert_enrollments cre,
178: ota_cert_prd_enrollments cpe
179: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
180: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
181:

Line 191: l_person_id ota_cert_enrollments.person_id%TYPE;

187: where CERT_PRD_ENROLLMENT_ID = p_cert_prd_enrollment_id); --Bug 15938221
188:
189: l_proc VARCHAR2(72) := g_package|| 'calculate_cme_status';
190:
191: l_person_id ota_cert_enrollments.person_id%TYPE;
192: l_contact_id ota_cert_enrollments.contact_id%TYPE;
193: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
194: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
195:

Line 192: l_contact_id ota_cert_enrollments.contact_id%TYPE;

188:
189: l_proc VARCHAR2(72) := g_package|| 'calculate_cme_status';
190:
191: l_person_id ota_cert_enrollments.person_id%TYPE;
192: l_contact_id ota_cert_enrollments.contact_id%TYPE;
193: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;
194: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;
195:
196: l_enroll_status VARCHAR2(30);

Line 307: p_person_id IN ota_cert_enrollments.person_id%TYPE,

303: -- ---------------------------------------------------------------------------
304: -- |----------------------< update_cme_status >-------------------|
305: -- ---------------------------------------------------------------------------
306: PROCEDURE update_cme_status (p_event_id IN ota_events.event_id%TYPE,
307: p_person_id IN ota_cert_enrollments.person_id%TYPE,
308: p_contact_id IN ota_cert_enrollments.contact_id%TYPE,
309: p_cert_prd_enrollment_ids OUT NOCOPY varchar2)
310: IS
311:

Line 308: p_contact_id IN ota_cert_enrollments.contact_id%TYPE,

304: -- |----------------------< update_cme_status >-------------------|
305: -- ---------------------------------------------------------------------------
306: PROCEDURE update_cme_status (p_event_id IN ota_events.event_id%TYPE,
307: p_person_id IN ota_cert_enrollments.person_id%TYPE,
308: p_contact_id IN ota_cert_enrollments.contact_id%TYPE,
309: p_cert_prd_enrollment_ids OUT NOCOPY varchar2)
310: IS
311:
312: l_proc VARCHAR2(72) := g_package|| 'update_cme_status';

Line 334: ota_cert_enrollments cre

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
337: AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
338: AND cme.cert_member_id = cmb.certification_member_id

Line 464: FROM ota_cert_enrollments cre,

460: SELECT cre.certification_id,
461: cpe.cert_enrollment_id,
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

Line 489: FROM ota_cert_enrollments cre

485:
486: CURSOR csr_cre_update(csr_cert_enrollment_id number)
487: IS
488: SELECT cre.object_version_number
489: FROM ota_cert_enrollments cre
490: where cre.cert_enrollment_id = csr_cert_enrollment_id;
491:
492:
493: -- l_exists ota_cert_prd_enrollments.cert_mbr_enrollment_id%TYPE;

Line 494: cre_object_version_number ota_cert_enrollments.object_version_number%type;

490: where cre.cert_enrollment_id = csr_cert_enrollment_id;
491:
492:
493: -- l_exists ota_cert_prd_enrollments.cert_mbr_enrollment_id%TYPE;
494: cre_object_version_number ota_cert_enrollments.object_version_number%type;
495: cpe_object_version_number ota_cert_prd_enrollments.object_version_number%type;
496: l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;
497: l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;
498: l_chk_cert_prd_compl varchar2(1);

Line 497: l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;

493: -- l_exists ota_cert_prd_enrollments.cert_mbr_enrollment_id%TYPE;
494: cre_object_version_number ota_cert_enrollments.object_version_number%type;
495: cpe_object_version_number ota_cert_prd_enrollments.object_version_number%type;
496: l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;
497: l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;
498: l_chk_cert_prd_compl varchar2(1);
499: l_completion_date date;
500: */
501: rec_cpe_status csr_cpe_status%rowtype;

Line 503: l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;

499: l_completion_date date;
500: */
501: rec_cpe_status csr_cpe_status%rowtype;
502: l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;
503: l_certification_status_code ota_cert_enrollments.certification_status_code%TYPE;
504:
505: l_proc VARCHAR2(72) := g_package|| 'Update_cpe_status';
506:
507: l_child_update_flag varchar2(1) := 'N';

Line 681: FROM ota_cert_enrollments cre,

677: CURSOR csr_cpe IS
678: select
679: cre.certification_id,
680: cpe.business_group_id
681: FROM ota_cert_enrollments cre,
682: ota_cert_prd_enrollments cpe,
683: ota_certifications_b crt
684: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
685: and cre.certification_id = crt.certification_id

Line 893: ota_cert_enrollments cre,

889: ota_events_tl et,
890: ota_activity_versions a,
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,

Line 946: p_person_id IN ota_cert_enrollments.person_id%type,

942: RETURN l_return_flag;
943: end chk_active_cme_enrl;
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

Line 947: p_contact_id ota_cert_enrollments.contact_id%type)

943: end chk_active_cme_enrl;
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

Line 952: FROM OTA_CERT_ENROLLMENTS cre,

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,
953: OTA_CERT_PRD_ENROLLMENTS cpe,
954: OTA_CERT_MBR_ENROLLMENTS cme,
955: OTA_CERTIFICATION_MEMBERS cmb,
956: OTA_EVENTS evt

Line 970: FROM OTA_CERT_ENROLLMENTS cre,

966: AND cme.cert_member_id = cmb.certification_member_id;
967:
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

Line 1006: p_person_id IN ota_cert_enrollments.person_id%type,

1002:
1003: END get_cert_mbr_enroll_id;
1004:
1005: function get_cert_prd_enroll_id(p_event_id IN ota_events.event_id%type,
1006: p_person_id IN ota_cert_enrollments.person_id%type,
1007: p_contact_id ota_cert_enrollments.contact_id%type)
1008: return ota_cert_prd_enrollments.cert_prd_enrollment_id%type IS
1009:
1010: CURSOR csr_cert_prd_enrl_id_person IS

Line 1007: p_contact_id ota_cert_enrollments.contact_id%type)

1003: END get_cert_mbr_enroll_id;
1004:
1005: function get_cert_prd_enroll_id(p_event_id IN ota_events.event_id%type,
1006: p_person_id IN ota_cert_enrollments.person_id%type,
1007: p_contact_id ota_cert_enrollments.contact_id%type)
1008: return ota_cert_prd_enrollments.cert_prd_enrollment_id%type IS
1009:
1010: CURSOR csr_cert_prd_enrl_id_person IS
1011: SELECT max(cme.cert_prd_enrollment_id) cert_prd_enrollment_id

Line 1012: FROM OTA_CERT_ENROLLMENTS cre,

1008: return ota_cert_prd_enrollments.cert_prd_enrollment_id%type IS
1009:
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

Line 1030: FROM OTA_CERT_ENROLLMENTS cre,

1026: AND cme.cert_member_id = cmb.certification_member_id;
1027:
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