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_LO_UTILITY dependencies on OTA_CERT_PRD_ENROLLMENTS

Line 10: OTA_CERT_PRD_ENROLLMENTS cpe,

6: p_person_id ota_cert_enrollments.contact_id%type) is
7: SELECT cpe.cert_prd_enrollment_id
8: FROM OTA_CERTIFICATIONS_B crt,
9: OTA_CERT_ENROLLMENTS cre,
10: OTA_CERT_PRD_ENROLLMENTS cpe,
11: OTA_CERT_MBR_ENROLLMENTS cme,
12: OTA_CERTIFICATION_MEMBERS cmb,
13: OTA_EVENTS evt
14: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID

Line 41: OTA_CERT_PRD_ENROLLMENTS cpe,

37: p_contact_id ota_cert_enrollments.contact_id%type) is
38: SELECT cpe.cert_prd_enrollment_id
39: FROM OTA_CERTIFICATIONS_B crt,
40: OTA_CERT_ENROLLMENTS cre,
41: OTA_CERT_PRD_ENROLLMENTS cpe,
42: OTA_CERT_MBR_ENROLLMENTS cme,
43: OTA_CERTIFICATION_MEMBERS cmb,
44: OTA_EVENTS evt
45: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID

Line 226: p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is

222: -- This cursor finds all certification member records in the certification
223: -- period that area associated with this learning object.
224: cursor cert_member_enrollments(
225: p_learning_object_id ota_learning_objects.learning_object_id%type,
226: p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
227: select distinct(cme.cert_mbr_enrollment_id),
228: cme.member_status_code,
229: cme.object_version_number,
230: cme.cert_member_id,

Line 235: ota_offerings o, ota_cert_prd_enrollments cpe, ota_activity_versions act

231: cme.cert_prd_enrollment_id,
232: nvl(act.eres_enabled,'N') sign_enabled,
233: cm.object_id
234: from ota_certification_members cm, ota_cert_mbr_enrollments cme,
235: ota_offerings o, ota_cert_prd_enrollments cpe, ota_activity_versions act
236: where
237: cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
238: and cme.cert_member_id = cm.certification_member_id
239: and cm.object_id = o.activity_version_id

Line 336: l_cert_prd_enr_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;

332:
333: l_person_id ota_delegate_bookings.delegate_person_id%type;
334: l_contact_id ota_delegate_bookings.delegate_contact_id%type;
335: l_lo_id ota_offerings.learning_object_id%type;
336: l_cert_prd_enr_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
337: l_member_in varchar2(10);
338: l_booking_successful_att_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
339: l_booking_failure_reason ota_delegate_bookings.failure_reason%Type;
340: begin

Line 1782: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

1778: end user_can_attempt_event;
1779:
1780:
1781: function cert_is_attemptable(
1782: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1783: p_date date,
1784: p_reason out nocopy number) return boolean is
1785:
1786: v_cert_start_date ota_certifications_b.start_date_active%type;

Line 1788: v_cert_prd_enroll_start ota_cert_prd_enrollments.cert_period_start_date%type;

1784: p_reason out nocopy number) return boolean is
1785:
1786: v_cert_start_date ota_certifications_b.start_date_active%type;
1787: v_cert_end_date ota_certifications_b.end_date_active%type;
1788: v_cert_prd_enroll_start ota_cert_prd_enrollments.cert_period_start_date%type;
1789: v_cert_prd_enroll_end ota_cert_prd_enrollments.cert_period_end_date%type;
1790: begin
1791: select c.start_date_active, c.end_date_active, cpe.cert_period_start_date, cpe.cert_period_end_date
1792: into v_cert_start_date, v_cert_end_date, v_cert_prd_enroll_start, v_cert_prd_enroll_end

Line 1789: v_cert_prd_enroll_end ota_cert_prd_enrollments.cert_period_end_date%type;

1785:
1786: v_cert_start_date ota_certifications_b.start_date_active%type;
1787: v_cert_end_date ota_certifications_b.end_date_active%type;
1788: v_cert_prd_enroll_start ota_cert_prd_enrollments.cert_period_start_date%type;
1789: v_cert_prd_enroll_end ota_cert_prd_enrollments.cert_period_end_date%type;
1790: begin
1791: select c.start_date_active, c.end_date_active, cpe.cert_period_start_date, cpe.cert_period_end_date
1792: into v_cert_start_date, v_cert_end_date, v_cert_prd_enroll_start, v_cert_prd_enroll_end
1793: from ota_certifications_b c, ota_cert_prd_enrollments cpe, ota_cert_enrollments ce

Line 1793: from ota_certifications_b c, ota_cert_prd_enrollments cpe, ota_cert_enrollments ce

1789: v_cert_prd_enroll_end ota_cert_prd_enrollments.cert_period_end_date%type;
1790: begin
1791: select c.start_date_active, c.end_date_active, cpe.cert_period_start_date, cpe.cert_period_end_date
1792: into v_cert_start_date, v_cert_end_date, v_cert_prd_enroll_start, v_cert_prd_enroll_end
1793: from ota_certifications_b c, ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
1794: where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
1795: cpe.cert_enrollment_id = ce.cert_enrollment_id and
1796: ce.certification_id = c.certification_id;
1797:

Line 1818: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

1814: return false;
1815: end cert_is_attemptable;
1816:
1817: function user_is_enrolled_in_cert(
1818: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1819: p_user_id fnd_user.user_id%type,
1820: p_user_type ota_attempts.user_type%type,
1821: p_date date,
1822: p_reason out nocopy number) return boolean is

Line 1831: from ota_cert_prd_enrollments cpe, ota_cert_enrollments ce

1827: l_user_id number;
1828: begin
1829: select ce.unenrollment_date, nvl(ce.person_id, ce.contact_id)
1830: into v_cert_unsubscribe_date, v_cert_enr_user_id
1831: from ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
1832: where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
1833: cpe.cert_enrollment_id = ce.cert_enrollment_id;
1834:
1835: --bug 4725513

Line 1858: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

1854: return false;
1855: end user_is_enrolled_in_cert;
1856:
1857: function user_can_attempt_cert(
1858: p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1859: p_user_id fnd_user.user_id%type,
1860: p_user_type ota_attempts.user_type%type,
1861: p_reason out nocopy number) return varchar2 is
1862:

Line 2503: l_cert_prd_enrollment_id OTA_CERT_PRD_ENROLLMENTS.cert_prd_enrollment_id%type;

2499: Where event_id = p_event_id;
2500:
2501: l_cert_prd_enrollment_ids varchar2(4000) := null;
2502: l_person_id ota_cert_enrollments.person_id%type := null;
2503: l_cert_prd_enrollment_id OTA_CERT_PRD_ENROLLMENTS.cert_prd_enrollment_id%type;
2504: l_cert_mbr_enrollment_id ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type;
2505: begin
2506:
2507: if p_chk_active_cert_flag = 'Y' then

Line 3492: l_cert_prd_enrollment_id OTA_CERT_PRD_ENROLLMENTS.cert_prd_enrollment_id%type;

3488:
3489: l_cert_prd_enrollment_ids varchar2(4000) := null;
3490: l_person_id ota_cert_enrollments.person_id%type := null;
3491: l_contact_id ota_cert_enrollments.contact_id%type := null;
3492: l_cert_prd_enrollment_id OTA_CERT_PRD_ENROLLMENTS.cert_prd_enrollment_id%type;
3493: l_sign_eval_status OTA_DELEGATE_BOOKINGS.sign_eval_status%type;
3494: l_trackingtype ota_learning_objects.tracking_type %TYPE;
3495: l_perf_rec_count INTEGER := 0;
3496: l_multiple boolean:=false;

Line 4051: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

4047: p_user_type IN ota_attempts.user_type%type,
4048: p_event_id IN ota_events.event_id%TYPE,
4049: p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
4050: p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
4051: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
4052: p_mode IN number default null)
4053: RETURN VARCHAR2 IS
4054:
4055:

Line 4399: ota_cert_prd_enrollments cpe

4395: cmb.certification_member_id,
4396: cme.member_status_code
4397: FROM ota_certification_members cmb,
4398: ota_cert_mbr_enrollments cme,
4399: ota_cert_prd_enrollments cpe
4400: WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
4401: AND cme.cert_member_id = cmb.certification_member_id
4402: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4403:

Line 4404: CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS

4400: WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
4401: AND cme.cert_member_id = cmb.certification_member_id
4402: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4403:
4404: CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4405: SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
4406: FROM ota_cert_enrollments cre,
4407: ota_cert_prd_enrollments cpe
4408: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id

Line 4407: ota_cert_prd_enrollments cpe

4403:
4404: CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4405: SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
4406: FROM ota_cert_enrollments cre,
4407: ota_cert_prd_enrollments cpe
4408: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4409: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
4410:
4411:

Line 4674: csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS

4670: where cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4671:
4672:
4673: CURSOR c_prd_lo_status(l_lo_id in ota_offerings.learning_object_id%TYPE,
4674: csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4675: SELECT lesson_status,
4676: hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
4677: FROM ota_performances
4678: WHERE user_id = p_user_id

Line 4694: csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS

4690:
4691: CURSOR csr_prf_ord(p_activity_version_id in ota_activity_versions.activity_version_id%type,
4692: p_user_id in number,
4693: p_user_type in varchar2,
4694: csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4695: SELECT prf.user_id,
4696: lo.learning_object_id,
4697: decode(prf.lesson_status, 'P', '1',
4698: 'C', '2',

Line 4721: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;

4717: l_proc VARCHAR2(72) := g_package||'get_cert_lo_status';
4718: rec csr_event%rowtype;
4719: l_online_event_id ota_events.event_id%type;
4720:
4721: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
4722:
4723: l_status VARCHAR2(30) := null;
4724: l_status_name ota_booking_status_types_tl.name%TYPE := null;
4725:

Line 4815: ota_cert_prd_enrollments cpe

4811: cmb.certification_member_id,
4812: cme.member_status_code
4813: FROM ota_certification_members cmb,
4814: ota_cert_mbr_enrollments cme,
4815: ota_cert_prd_enrollments cpe
4816: WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
4817: AND cme.cert_member_id = cmb.certification_member_id
4818: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4819:

Line 4820: CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS

4816: WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
4817: AND cme.cert_member_id = cmb.certification_member_id
4818: AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
4819:
4820: CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4821: SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
4822: FROM ota_cert_enrollments cre,
4823: ota_cert_prd_enrollments cpe
4824: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id

Line 4823: ota_cert_prd_enrollments cpe

4819:
4820: CURSOR csr_cert_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
4821: SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
4822: FROM ota_cert_enrollments cre,
4823: ota_cert_prd_enrollments cpe
4824: where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
4825: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
4826:
4827:

Line 5429: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;

5425:
5426: l_proc VARCHAR2(72) := g_package|| 'get_active_cert_prds';
5427:
5428: l_activity_version_id ota_events.activity_version_id%type;
5429: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
5430: l_cert_prd_enrollment_ids varchar2(4000);
5431:
5432:
5433: begin