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_LRNG_PATH_MEMBER_UTIL dependencies on OTA_LP_MEMBER_ENROLLMENTS

Line 17: p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE DEFAULT NULL,

13: -- ---------------------------------------------------------------------------
14: FUNCTION get_enrollment_status(p_person_id IN ota_learning_paths.person_id%TYPE,
15: p_contact_id IN ota_learning_paths.contact_id%TYPE,
16: p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE,
17: p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE DEFAULT NULL,
18: p_return_code IN VARCHAR2)
19: RETURN VARCHAR2 IS
20:
21: CURSOR csr_lp_enr IS

Line 25: ota_lp_member_enrollments lme,

21: CURSOR csr_lp_enr IS
22: SELECT DECODE(bst.type,'C','Z',bst.type) status,
23: bst.name
24: FROM ota_learning_path_members lpm,
25: ota_lp_member_enrollments lme,
26: ota_events evt,
27: ota_delegate_bookings tdb,
28: ota_booking_status_types_vl bst
29: WHERE lpm.activity_version_id = evt.activity_version_id

Line 101: p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,

97: --
98: PROCEDURE get_enrollment_status(p_person_id IN ota_learning_paths.person_id%TYPE,
99: p_contact_id IN ota_learning_paths.contact_id%TYPE,
100: p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE,
101: p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
102: p_booking_status_type OUT NOCOPY ota_booking_status_types.type%TYPE,
103: p_date_status_changed OUT NOCOPY ota_delegate_bookings.date_status_changed%TYPE,
104: p_successful_attendance_flag OUT NOCOPY ota_delegate_bookings.successful_attendance_flag%TYPE)
105: IS

Line 113: ota_lp_member_enrollments lme,

109: SELECT DECODE(bst.type,'C','Z',bst.type) status,
110: tdb.date_status_changed,
111: tdb.successful_attendance_flag
112: FROM ota_learning_path_members lpm,
113: ota_lp_member_enrollments lme,
114: ota_events evt,
115: ota_delegate_bookings tdb,
116: ota_booking_status_types bst
117: WHERE lpm.activity_version_id = evt.activity_version_id

Line 342: FROM ota_lp_member_enrollments

338: RETURN NUMBER IS
339:
340: CURSOR csr_lpc_comp IS
341: SELECT count(lp_member_enrollment_id)
342: FROM ota_lp_member_enrollments
343: WHERE learning_path_section_id = p_learning_path_section_id
344: AND member_status_code = 'COMPLETED';
345:
346: l_proc VARCHAR2(72) := g_package|| 'get_lpc_completed_courses';

Line 396: ,p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE

392: -- |---------------------------< get_valid_enroll >-------------------------|
393: -- ----------------------------------------------------------------------------
394: PROCEDURE get_valid_enroll (p_person_id IN ota_learning_paths.person_id%TYPE
395: ,p_contact_id IN ota_learning_paths.contact_id%TYPE
396: ,p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE
397: ,p_return_status OUT nocopy VARCHAR2)
398: IS
399: l_enr_type VARCHAR2(30);
400: l_proc VARCHAR2(72) := g_package|| 'get_valid_enroll';

Line 447: ota_lp_member_enrollments olme,

443: olpe.no_of_mandatory_courses,
444: olme.member_status_code,
445: olme.event_id
446: FROM ota_learning_path_members olpm,
447: ota_lp_member_enrollments olme,
448: ota_lp_enrollments olpe
449: WHERE olpe.learning_path_id = olpm.learning_path_id
450: AND olpm.learning_path_member_id = olme.learning_path_member_id
451: AND olpe.lp_enrollment_id = olme.lp_enrollment_id

Line 461: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;

457: l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
458: l_lp_section_id ota_lp_sections.learning_path_section_id%TYPE;
459: l_completion_type_code ota_lp_sections.completion_type_code%TYPE;
460: l_enroll_type ota_booking_status_types.type%TYPE;
461: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
462: l_completion_date ota_lp_enrollments.completion_date%TYPE;
463: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
464: l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE := 0;
465: l_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;

Line 471: l_old_member_status ota_lp_member_enrollments.member_status_code%TYPE;

467:
468: --variables to store old values
469: l_old_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
470: l_old_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
471: l_old_member_status ota_lp_member_enrollments.member_status_code%TYPE;
472: l_event_id ota_events.event_id%TYPE;
473: l_successful_attendance_flag ota_delegate_bookings.successful_attendance_flag%TYPE;
474:
475: BEGIN

Line 575: Procedure Update_lpe_lme_change( p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE)

571: -- |----------------------< Update_lpe_lme_change >--------------------------|
572: -- ---------------------------------------------------------------------------
573: --
574: -- This procedure will get called only when a tpc is Cancelled
575: Procedure Update_lpe_lme_change( p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE)
576: is
577:
578: CURSOR csr_lpe_with_lme
579: IS

Line 583: ota_lp_member_enrollments lme

579: IS
580: SELECT lpe.lp_enrollment_id,
581: lpe.path_status_code
582: FROM ota_lp_enrollments lpe,
583: ota_lp_member_enrollments lme
584: WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
585: AND lpe.path_status_code <> 'CANCELLED'
586: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
587:

Line 595: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

591: FROM ota_lp_enrollments lpe
592: WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
593:
594:
595: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
596: l_object_version_number ota_lp_enrollments.object_version_number%type;
597: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
598: l_complete_ok varchar2(1);
599:

Line 632: Procedure Update_lpe_lme_change (p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,

628: END LOOP;
629: END Update_lpe_lme_change;
630:
631:
632: Procedure Update_lpe_lme_change (p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
633: p_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE,
634: p_lp_enrollment_ids OUT NOCOPY VARCHAR2)
635: is
636:

Line 644: ota_lp_member_enrollments lme

640: lpe.path_status_code,
641: lpe.learning_path_id,
642: lpe.no_of_mandatory_courses
643: FROM ota_lp_enrollments lpe,
644: ota_lp_member_enrollments lme
645: WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
646: AND lpe.path_status_code <> 'CANCELLED'
647: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
648:

Line 656: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

652: SELECT lpe.object_version_number
653: FROM ota_lp_enrollments lpe
654: WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
655:
656: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
657: l_object_version_number ota_lp_enrollments.object_version_number%type;
658: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
659: l_completion_date DATE;
660: l_complete_ok varchar2(1);

Line 750: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

746: l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
747: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
748: l_learning_path_section_id ota_lp_sections.learning_path_section_id%TYPE;
749: l_learning_path_member_id ota_learning_path_members.learning_path_member_id%TYPE;
750: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
751:
752: l_lp_ovn number;
753: l_lpe_ovn number;
754: l_lpc_ovn number;

Line 1042: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;

1038: -- Variables for IN/OUT parameters
1039: --
1040: -- Other variables
1041: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
1042: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
1043:
1044: l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
1045: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1046: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

Line 1046: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

1042: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
1043:
1044: l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
1045: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1046: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
1047: l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
1048: l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
1049: l_completion_date ota_lp_enrollments.completion_date%TYPE := null;
1050:

Line 1079: FROM ota_lp_member_enrollments lme,

1075: SELECT lme.lp_member_enrollment_id,
1076: lme.object_version_number,
1077: lme.learning_path_member_id,
1078: lme.lp_enrollment_id
1079: FROM ota_lp_member_enrollments lme,
1080: ota_learning_path_members lpm
1081: WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1082: AND lpm.learning_path_id = l_learning_path_id
1083: AND lme.member_status_code = 'AWAITING_APPROVAL';

Line 1088: FROM ota_lp_member_enrollments lme,

1084:
1085: CURSOR csr_get_cncl_lme IS
1086: SELECT lme.lp_member_enrollment_id,
1087: lme.object_version_number
1088: FROM ota_lp_member_enrollments lme,
1089: ota_learning_path_members lpm
1090: WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1091: AND lpm.learning_path_id = l_learning_path_id
1092: AND lme.member_status_code <> 'CANCELLED';

Line 1532: FUNCTION get_lpm_completion_date(p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,

1528: RETURN l_completion_date;
1529:
1530: END get_lp_completion_date;
1531:
1532: FUNCTION get_lpm_completion_date(p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
1533: p_activity_version_id ota_activity_versions.activity_version_id%TYPE,
1534: p_person_id ota_lp_enrollments.person_id%TYPE,
1535: p_contact_id ota_lp_enrollments.contact_id%TYPE)
1536: RETURN DATE IS

Line 1542: ota_lp_member_enrollments lpme,

1538: CURSOR get_lpm_completion_date IS
1539: SELECT max(ota_lrng_path_member_util.get_class_completion_date(oev.event_id,lpe.person_id, lpe.contact_id)) completion_date
1540: FROM ota_lp_enrollments lpe,
1541: ota_learning_path_members lpm,
1542: ota_lp_member_enrollments lpme,
1543: ota_events oev
1544: WHERE lpe.learning_path_id = lpm.learning_path_id
1545: AND oev.activity_version_id = lpm.activity_version_id
1546: AND lpe.lp_enrollment_id = lpme.lp_enrollment_id