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: IS
105:

Line 111: ota_lp_member_enrollments lme,

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

Line 331: FROM ota_lp_member_enrollments

327: RETURN NUMBER IS
328:
329: CURSOR csr_lpc_comp IS
330: SELECT count(lp_member_enrollment_id)
331: FROM ota_lp_member_enrollments
332: WHERE learning_path_section_id = p_learning_path_section_id
333: AND member_status_code = 'COMPLETED';
334:
335: l_proc VARCHAR2(72) := g_package|| 'get_lpc_completed_courses';

Line 385: ,p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE

381: -- |---------------------------< get_valid_enroll >-------------------------|
382: -- ----------------------------------------------------------------------------
383: PROCEDURE get_valid_enroll (p_person_id IN ota_learning_paths.person_id%TYPE
384: ,p_contact_id IN ota_learning_paths.contact_id%TYPE
385: ,p_lp_member_enrollment_id IN ota_lp_member_enrollments.lp_member_enrollment_id%TYPE
386: ,p_return_status OUT nocopy VARCHAR2)
387: IS
388: l_enr_type VARCHAR2(30);
389: l_proc VARCHAR2(72) := g_package|| 'get_valid_enroll';

Line 435: ota_lp_member_enrollments olme,

431: olpe.no_of_completed_courses,
432: olpe.no_of_mandatory_courses,
433: olme.member_status_code
434: FROM ota_learning_path_members olpm,
435: ota_lp_member_enrollments olme,
436: ota_lp_enrollments olpe
437: WHERE olpe.learning_path_id = olpm.learning_path_id
438: AND olpm.learning_path_member_id = olme.learning_path_member_id
439: AND olpe.lp_enrollment_id = olme.lp_enrollment_id

Line 449: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;

445: l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
446: l_lp_section_id ota_lp_sections.learning_path_section_id%TYPE;
447: l_completion_type_code ota_lp_sections.completion_type_code%TYPE;
448: l_enroll_type ota_booking_status_types.type%TYPE;
449: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
450: l_completion_date ota_lp_enrollments.completion_date%TYPE;
451: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
452: l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE := 0;
453: l_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;

Line 459: l_old_member_status ota_lp_member_enrollments.member_status_code%TYPE;

455:
456: --variables to store old values
457: l_old_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
458: l_old_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
459: l_old_member_status ota_lp_member_enrollments.member_status_code%TYPE;
460:
461:
462: BEGIN
463:

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

550: -- |----------------------< Update_lpe_lme_change >--------------------------|
551: -- ---------------------------------------------------------------------------
552: --
553: -- This procedure will get called only when a tpc is Cancelled
554: Procedure Update_lpe_lme_change( p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE)
555: is
556:
557: CURSOR csr_lpe_with_lme
558: IS

Line 562: ota_lp_member_enrollments lme

558: IS
559: SELECT lpe.lp_enrollment_id,
560: lpe.path_status_code
561: FROM ota_lp_enrollments lpe,
562: ota_lp_member_enrollments lme
563: WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
564: AND lpe.path_status_code <> 'CANCELLED'
565: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
566:

Line 574: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

570: FROM ota_lp_enrollments lpe
571: WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
572:
573:
574: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
575: l_object_version_number ota_lp_enrollments.object_version_number%type;
576: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
577: l_complete_ok varchar2(1);
578:

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

607: END LOOP;
608: END Update_lpe_lme_change;
609:
610:
611: Procedure Update_lpe_lme_change (p_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE,
612: p_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE,
613: p_lp_enrollment_ids OUT NOCOPY VARCHAR2)
614: is
615:

Line 623: ota_lp_member_enrollments lme

619: lpe.path_status_code,
620: lpe.learning_path_id,
621: lpe.no_of_mandatory_courses
622: FROM ota_lp_enrollments lpe,
623: ota_lp_member_enrollments lme
624: WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
625: AND lpe.path_status_code <> 'CANCELLED'
626: AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
627:

Line 635: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

631: SELECT lpe.object_version_number
632: FROM ota_lp_enrollments lpe
633: WHERE lpe.lp_enrollment_id = csr_lp_enrollment_id;
634:
635: l_exists ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
636: l_object_version_number ota_lp_enrollments.object_version_number%type;
637: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
638: l_completion_date DATE;
639: l_complete_ok varchar2(1);

Line 728: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

724: l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
725: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
726: l_learning_path_section_id ota_lp_sections.learning_path_section_id%TYPE;
727: l_learning_path_member_id ota_learning_path_members.learning_path_member_id%TYPE;
728: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
729:
730: l_lp_ovn number;
731: l_lpe_ovn number;
732: l_lpc_ovn number;

Line 1020: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;

1016: -- Variables for IN/OUT parameters
1017: --
1018: -- Other variables
1019: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
1020: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
1021:
1022: l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
1023: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1024: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

Line 1024: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;

1020: l_member_status_code ota_lp_member_enrollments.member_status_code%TYPE;
1021:
1022: l_learning_path_id ota_learning_paths.learning_path_id%TYPE := p_learning_path_id;
1023: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1024: l_lp_member_enrollment_id ota_lp_member_enrollments.lp_member_enrollment_id%TYPE;
1025: l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
1026: l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
1027: l_completion_date ota_lp_enrollments.completion_date%TYPE := null;
1028:

Line 1057: FROM ota_lp_member_enrollments lme,

1053: SELECT lme.lp_member_enrollment_id,
1054: lme.object_version_number,
1055: lme.learning_path_member_id,
1056: lme.lp_enrollment_id
1057: FROM ota_lp_member_enrollments lme,
1058: ota_learning_path_members lpm
1059: WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1060: AND lpm.learning_path_id = l_learning_path_id
1061: AND lme.member_status_code = 'AWAITING_APPROVAL';

Line 1066: FROM ota_lp_member_enrollments lme,

1062:
1063: CURSOR csr_get_cncl_lme IS
1064: SELECT lme.lp_member_enrollment_id,
1065: lme.object_version_number
1066: FROM ota_lp_member_enrollments lme,
1067: ota_learning_path_members lpm
1068: WHERE lme.learning_path_member_id = lpm.learning_path_member_id
1069: AND lpm.learning_path_id = l_learning_path_id
1070: AND lme.member_status_code <> 'CANCELLED';