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_ENROLLMENTS

Line 6: p_person_id ota_cert_enrollments.contact_id%type) is

2: /* $Header: otloutil.pkb 120.106.12020000.10 2013/03/29 12:58:08 jaysridh ship $ */
3:
4: g_package varchar2(33) := ' ota_lo_utility.'; -- Global package name
5: cursor csr_active_cert_prd_person(p_event_id ota_events.event_id%type,
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,

Line 9: OTA_CERT_ENROLLMENTS cre,

5: cursor csr_active_cert_prd_person(p_event_id ota_events.event_id%type,
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

Line 37: p_contact_id ota_cert_enrollments.contact_id%type) is

33: NVL(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= cpe.cert_period_start_date)
34: );
35:
36: cursor csr_active_cert_prd_contact(p_event_id ota_events.event_id%type,
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,

Line 40: OTA_CERT_ENROLLMENTS cre,

36: cursor csr_active_cert_prd_contact(p_event_id ota_events.event_id%type,
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

Line 741: p_person_id ota_cert_enrollments.person_id%type) is

737:
738: -- Added following two cursors in order to get the certification status of the
739: -- learner in case he is either a person or a contact.
740: Cursor csr_person_cert_status(p_event_id ota_events.event_id%type,
741: p_person_id ota_cert_enrollments.person_id%type) is
742: SELECT cre.CERTIFICATION_STATUS_CODE
743: FROM OTA_CERTIFICATIONS_B crt,
744: OTA_CERT_ENROLLMENTS cre,
745: OTA_CERT_MBR_ENROLLMENTS cme,

Line 744: OTA_CERT_ENROLLMENTS cre,

740: Cursor csr_person_cert_status(p_event_id ota_events.event_id%type,
741: p_person_id ota_cert_enrollments.person_id%type) is
742: SELECT cre.CERTIFICATION_STATUS_CODE
743: FROM OTA_CERTIFICATIONS_B crt,
744: OTA_CERT_ENROLLMENTS cre,
745: OTA_CERT_MBR_ENROLLMENTS cme,
746: OTA_CERTIFICATION_MEMBERS cmb,
747: OTA_EVENTS evt
748: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID

Line 760: p_contact_id ota_cert_enrollments.contact_id%type) is

756: AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
757: AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
758:
759: Cursor csr_contact_cert_status(p_event_id ota_events.event_id%type,
760: p_contact_id ota_cert_enrollments.contact_id%type) is
761: SELECT cre.CERTIFICATION_STATUS_CODE
762: FROM OTA_CERTIFICATIONS_B crt,
763: OTA_CERT_ENROLLMENTS cre,
764: OTA_CERT_MBR_ENROLLMENTS cme,

Line 763: OTA_CERT_ENROLLMENTS cre,

759: Cursor csr_contact_cert_status(p_event_id ota_events.event_id%type,
760: p_contact_id ota_cert_enrollments.contact_id%type) is
761: SELECT cre.CERTIFICATION_STATUS_CODE
762: FROM OTA_CERTIFICATIONS_B crt,
763: OTA_CERT_ENROLLMENTS cre,
764: OTA_CERT_MBR_ENROLLMENTS cme,
765: OTA_CERTIFICATION_MEMBERS cmb,
766: OTA_EVENTS evt
767: WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID

Line 790: l_cert_status OTA_CERT_ENROLLMENTS.CERTIFICATION_STATUS_CODE%type;

786: l_status varchar2(80) :='ATTENDED';
787: l_user_type ota_attempts.user_type%type;
788: l_new_sign_eval_status varchar2(2):= null;
789: l_member_in varchar2(30);
790: l_cert_status OTA_CERT_ENROLLMENTS.CERTIFICATION_STATUS_CODE%type;
791: begin
792: hr_utility.set_location('Entering:'|| l_proc, 10);
793: if p_user_type = 'E' then
794: for a_booking in person_bookings(p_lo_id, p_user_id) loop

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 1824: v_cert_unsubscribe_date ota_cert_enrollments.unenrollment_date%type;

1820: p_user_type ota_attempts.user_type%type,
1821: p_date date,
1822: p_reason out nocopy number) return boolean is
1823:
1824: v_cert_unsubscribe_date ota_cert_enrollments.unenrollment_date%type;
1825: --v_cert_enr_user_id ota_cert_enrollments.person_id%type;
1826: v_cert_enr_user_id number;
1827: l_user_id number;
1828: begin

Line 1825: --v_cert_enr_user_id ota_cert_enrollments.person_id%type;

1821: p_date date,
1822: p_reason out nocopy number) return boolean is
1823:
1824: v_cert_unsubscribe_date ota_cert_enrollments.unenrollment_date%type;
1825: --v_cert_enr_user_id ota_cert_enrollments.person_id%type;
1826: v_cert_enr_user_id number;
1827: l_user_id number;
1828: begin
1829: select ce.unenrollment_date, nvl(ce.person_id, ce.contact_id)

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 2476: p_contact_id ota_cert_enrollments.contact_id%type default null,

2472: p_course_start_date ota_events.course_start_date%type,
2473: p_course_end_date ota_events.course_end_date%type,
2474: p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
2475: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
2476: p_contact_id ota_cert_enrollments.contact_id%type default null,
2477: p_chk_active_cert_flag varchar2 default 'N') return varchar2 is
2478:
2479:
2480: v_number_of_los number(10);

Line 2502: l_person_id ota_cert_enrollments.person_id%type := null;

2498: From ota_events
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:

Line 3490: l_person_id ota_cert_enrollments.person_id%type := null;

3486: -- Bug 3725560
3487: l_imported_offering ota_events.offering_id%TYPE := null;
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;

Line 3491: l_contact_id ota_cert_enrollments.contact_id%type := null;

3487: l_imported_offering ota_events.offering_id%TYPE := null;
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;

Line 4406: FROM ota_cert_enrollments cre,

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

Line 4822: FROM ota_cert_enrollments cre,

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

Line 5422: p_person_id ota_cert_enrollments.contact_id%type,

5418: End get_lme_onl_evt_count;
5419:
5420: procedure get_active_cert_prds(
5421: p_event_id ota_events.event_id%type,
5422: p_person_id ota_cert_enrollments.contact_id%type,
5423: p_contact_id ota_cert_enrollments.contact_id%type,
5424: p_cert_prd_enrollment_ids OUT NOCOPY varchar2) is
5425:
5426: l_proc VARCHAR2(72) := g_package|| 'get_active_cert_prds';

Line 5423: p_contact_id ota_cert_enrollments.contact_id%type,

5419:
5420: procedure get_active_cert_prds(
5421: p_event_id ota_events.event_id%type,
5422: p_person_id ota_cert_enrollments.contact_id%type,
5423: p_contact_id ota_cert_enrollments.contact_id%type,
5424: p_cert_prd_enrollment_ids OUT NOCOPY varchar2) is
5425:
5426: l_proc VARCHAR2(72) := g_package|| 'get_active_cert_prds';
5427:

Line 5517: p_person_id ota_cert_enrollments.person_id%type,

5513: --
5514: End Lo_has_cld_and_no_strt_url;
5515:
5516: FUNCTION get_member_in(p_event_id ota_events.event_id%type,
5517: p_person_id ota_cert_enrollments.person_id%type,
5518: p_contact_id ota_cert_enrollments.contact_id%type) RETURN VARCHAR2 IS
5519:
5520: CURSOR csr_is_lp_mbr_person IS
5521: SELECT lpe.lp_enrollment_id

Line 5518: p_contact_id ota_cert_enrollments.contact_id%type) RETURN VARCHAR2 IS

5514: End Lo_has_cld_and_no_strt_url;
5515:
5516: FUNCTION get_member_in(p_event_id ota_events.event_id%type,
5517: p_person_id ota_cert_enrollments.person_id%type,
5518: p_contact_id ota_cert_enrollments.contact_id%type) RETURN VARCHAR2 IS
5519:
5520: CURSOR csr_is_lp_mbr_person IS
5521: SELECT lpe.lp_enrollment_id
5522: FROM ota_lp_enrollments lpe,

Line 5548: FROM ota_cert_enrollments cre,

5544: AND lpe.learning_path_id = lpm.learning_path_id;
5545:
5546: CURSOR csr_is_cert_mbr_person IS
5547: SELECT cre.cert_enrollment_id
5548: FROM ota_cert_enrollments cre,
5549: ota_certification_members cmb,
5550: ota_events oev
5551: WHERE cre.person_id = p_person_id
5552: AND oev.event_id = p_event_id

Line 5562: FROM ota_cert_enrollments cre,

5558: AND cre.certification_id = cmb.certification_id;
5559:
5560: CURSOR csr_is_cert_mbr_contact IS
5561: SELECT cre.cert_enrollment_id
5562: FROM ota_cert_enrollments cre,
5563: ota_certification_members cmb,
5564: ota_events oev
5565: WHERE cre.contact_id = p_contact_id
5566: AND oev.event_id = p_event_id

Line 5575: l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type;

5571: AND cmb.object_type = 'H'
5572: AND cre.certification_id = cmb.certification_id;
5573:
5574: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%type;
5575: l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type;
5576: l_return_value VARCHAR2(30);
5577: BEGIN
5578: if(p_person_id is not null) then
5579: open csr_is_lp_mbr_person;

Line 5684: l_person_id ota_cert_enrollments.person_id%type := null;

5680:
5681: l_trackingtype ota_learning_objects.tracking_type %TYPE;
5682: l_perf_rec_count INTEGER := 0;
5683: l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type;
5684: l_person_id ota_cert_enrollments.person_id%type := null;
5685: l_contact_id ota_cert_enrollments.contact_id%type := null;
5686: BEGIN
5687:
5688: hr_utility.set_location(' Step:'|| l_proc, 10);

Line 5685: l_contact_id ota_cert_enrollments.contact_id%type := null;

5681: l_trackingtype ota_learning_objects.tracking_type %TYPE;
5682: l_perf_rec_count INTEGER := 0;
5683: l_cert_prd_enr_id ota_performances.cert_prd_enrollment_id%type;
5684: l_person_id ota_cert_enrollments.person_id%type := null;
5685: l_contact_id ota_cert_enrollments.contact_id%type := null;
5686: BEGIN
5687:
5688: hr_utility.set_location(' Step:'|| l_proc, 10);
5689: if p_chk_active_cert_flag is not null and p_chk_active_cert_flag = 'Y' then