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_PRD_ENROLLMENTS

Line 11: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,

7: --
8: g_package VARCHAR2(33) := ' OTA_CME_UTIL.'; -- Global package name
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,

Line 12: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,

8: g_package VARCHAR2(33) := ' OTA_CME_UTIL.'; -- Global package name
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,

Line 48: p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,

44: order by status;
45: --
46:
47: PROCEDURE get_enrl_status_on_update(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
48: p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
49: p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
50: p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE)
51: IS
52:

Line 56: ota_cert_prd_enrollments cpe

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:
60: l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';

Line 64: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;

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);
68: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;

Line 65: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;

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);
68: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
69:

Line 104: p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,

100:
101: END get_enrl_status_on_update;
102:
103: PROCEDURE get_enrl_status_on_update(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
104: p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
105: p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
106: p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE,
107: p_successful_attendance_flag OUT NOCOPY ota_delegate_bookings.successful_attendance_flag%TYPE)
108: IS

Line 113: ota_cert_prd_enrollments cpe

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:
117: l_proc VARCHAR2(72) := g_package|| 'get_enrl_status_on_update';

Line 121: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;

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);
125: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;

Line 122: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;

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);
125: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
126: l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;

Line 168: p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,

164: -- ---------------------------------------------------------------------------
165: -- |----------------------< calculate_cme_status >-----------------------------|
166: -- ---------------------------------------------------------------------------
167: PROCEDURE calculate_cme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
168: p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
169: p_mode in varchar2,
170: p_member_status_code OUT nocopy VARCHAR2,
171: p_completion_date OUT nocopy DATE,
172: p_is_recert IN VARCHAR2 default 'N') --need to be passed only when p_mode is 'C'

Line 178: ota_cert_prd_enrollments cpe

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:
182: CURSOR csr_cert_prd_count IS

Line 184: from ota_cert_prd_enrollments

180: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
181:
182: CURSOR csr_cert_prd_count IS
183: select count(cert_prd_enrollment_id)
184: from ota_cert_prd_enrollments
185: where CERT_ENROLLMENT_ID = (select CERT_ENROLLMENT_ID
186: from ota_cert_prd_enrollments
187: where CERT_PRD_ENROLLMENT_ID = p_cert_prd_enrollment_id); --Bug 15938221
188:

Line 186: from ota_cert_prd_enrollments

182: CURSOR csr_cert_prd_count IS
183: select count(cert_prd_enrollment_id)
184: from ota_cert_prd_enrollments
185: where CERT_ENROLLMENT_ID = (select CERT_ENROLLMENT_ID
186: from ota_cert_prd_enrollments
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:

Line 193: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%type;

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);
197: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;

Line 194: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_start_date%type;

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);
197: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
198: l_event_type ota_events.event_type%type;

Line 333: ota_cert_prd_enrollments cpe,

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

Line 358: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%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;
361:
362: BEGIN

Line 465: ota_cert_prd_enrollments cpe,

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
469: AND cpe.period_status_code not in ('CANCELLED', 'EXPIRED')

Line 476: FROM ota_cert_prd_enrollments cpe

472:
473: CURSOR csr_cpe_status(csr_cert_prd_enrollment_id number)
474: IS
475: SELECT cpe.period_status_code
476: FROM ota_cert_prd_enrollments cpe
477: WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
478:
479: /*
480: CURSOR csr_cpe_update(csr_cert_prd_enrollment_id number)

Line 483: FROM ota_cert_prd_enrollments cpe

479: /*
480: CURSOR csr_cpe_update(csr_cert_prd_enrollment_id number)
481: IS
482: SELECT cpe.object_version_number
483: FROM ota_cert_prd_enrollments cpe
484: WHERE cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
485:
486: CURSOR csr_cre_update(csr_cert_enrollment_id number)
487: IS

Line 493: -- l_exists ota_cert_prd_enrollments.cert_mbr_enrollment_id%TYPE;

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;
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;

Line 495: cpe_object_version_number ota_cert_prd_enrollments.object_version_number%type;

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);
499: l_completion_date date;

Line 496: l_period_status_code ota_cert_prd_enrollments.period_status_code%TYPE;

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);
499: l_completion_date date;
500: */

Line 502: l_period_status_code ota_cert_prd_enrollments.period_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;
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:

Line 566: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%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:
569: rec_cme_info csr_cme_info%ROWTYPE;
570: l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;

Line 682: ota_cert_prd_enrollments cpe,

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
686: and cpe.cert_enrollment_id = cre.cert_enrollment_id;

Line 894: ota_cert_prd_enrollments cpe,

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,
898: ota_category_usages c

Line 953: OTA_CERT_PRD_ENROLLMENTS cpe,

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
957: WHERE cre.person_id = p_person_id

Line 971: OTA_CERT_PRD_ENROLLMENTS cpe,

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
975: WHERE cre.contact_id = p_contact_id

Line 1008: return ota_cert_prd_enrollments.cert_prd_enrollment_id%type IS

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
1012: FROM OTA_CERT_ENROLLMENTS cre,

Line 1013: OTA_CERT_PRD_ENROLLMENTS cpe,

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
1017: WHERE cre.person_id = p_person_id

Line 1031: OTA_CERT_PRD_ENROLLMENTS cpe,

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
1035: WHERE cre.contact_id = p_contact_id

Line 1046: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;

1042: AND cmb.OBJECT_TYPE = 'H'
1043: AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
1044: AND cme.cert_member_id = cmb.certification_member_id;
1045:
1046: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
1047:
1048: BEGIN
1049:
1050: if p_person_id is not null then