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_ENROLLMENTS

Line 227: p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,

223: -- ---------------------------------------------------------------------------
224: -- |----------------------< calculate_lme_status >-----------------------------|
225: -- ---------------------------------------------------------------------------
226: PROCEDURE calculate_lme_status(p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
227: p_lp_enrollment_id IN ota_lp_enrollments.lp_enrollment_id%TYPE,
228: p_member_status_code OUT nocopy VARCHAR2,
229: p_completion_date OUT nocopy DATE)
230: IS
231:

Line 244: FROM ota_lp_enrollments lpe

240: hr_utility.set_location('Entering:'|| l_proc, 10);
241:
242: SELECT lpe.person_id, lpe.contact_id
243: INTO l_person_id , l_contact_id
244: FROM ota_lp_enrollments lpe
245: where lpe.lp_enrollment_id = p_lp_enrollment_id;
246:
247: get_enrollment_status(p_person_id => l_person_id,
248: p_contact_id => l_contact_id,

Line 348: l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;

344: AND member_status_code = 'COMPLETED';
345:
346: l_proc VARCHAR2(72) := g_package|| 'get_lpc_completed_courses';
347:
348: l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
349:
350: BEGIN
351:
352: hr_utility.set_location(' Step:'|| l_proc, 10);

Line 422: p_person_id IN ota_lp_enrollments.person_id%TYPE,

418: -- ---------------------------------------------------------------------------
419: -- |----------------------< update_lme_enroll_status_chg >--------------------------|
420: -- ---------------------------------------------------------------------------
421: PROCEDURE update_lme_enroll_status_chg (p_event_id IN ota_events.event_id%TYPE,
422: p_person_id IN ota_lp_enrollments.person_id%TYPE,
423: p_contact_id IN ota_lp_enrollments.contact_id%TYPE,
424: p_lp_enrollment_ids OUT NOCOPY varchar2)
425: IS
426:

Line 423: p_contact_id IN ota_lp_enrollments.contact_id%TYPE,

419: -- |----------------------< update_lme_enroll_status_chg >--------------------------|
420: -- ---------------------------------------------------------------------------
421: PROCEDURE update_lme_enroll_status_chg (p_event_id IN ota_events.event_id%TYPE,
422: p_person_id IN ota_lp_enrollments.person_id%TYPE,
423: p_contact_id IN ota_lp_enrollments.contact_id%TYPE,
424: p_lp_enrollment_ids OUT NOCOPY varchar2)
425: IS
426:
427: l_proc VARCHAR2(72) := g_package|| 'update_lme_enroll_status_chg';

Line 448: ota_lp_enrollments olpe

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
452: AND (( p_person_id IS NOT NULL AND olpe.person_id = p_person_id)

Line 462: l_completion_date ota_lp_enrollments.completion_date%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;
466: l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;

Line 464: l_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE := 0;

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;
466: l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
467:
468: --variables to store old values

Line 465: l_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%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;
466: l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;
467:
468: --variables to store old values
469: l_old_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;

Line 466: l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%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;
466: l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%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;

Line 469: l_old_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;

465: l_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;
466: l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%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;

Line 470: l_old_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%TYPE;

466: l_section_completed_courses ota_lp_enrollments.no_of_completed_courses%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:

Line 582: FROM ota_lp_enrollments lpe,

578: CURSOR csr_lpe_with_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;

Line 591: FROM ota_lp_enrollments lpe

587:
588: CURSOR csr_lpe_update(csr_lp_enrollment_id number)
589: IS
590: SELECT lpe.object_version_number
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;

Line 596: l_object_version_number ota_lp_enrollments.object_version_number%type;

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:
600: BEGIN

Line 597: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;

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:
600: BEGIN
601: FOR rec1 in csr_lpe_with_lme LOOP

Line 633: p_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE,

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:
637: CURSOR csr_lpe_with_lme

Line 643: FROM ota_lp_enrollments lpe,

639: SELECT lpe.lp_enrollment_id,
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;

Line 653: FROM ota_lp_enrollments lpe

649:
650: CURSOR csr_lpe_update(csr_lp_enrollment_id number)
651: IS
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;

Line 657: l_object_version_number ota_lp_enrollments.object_version_number%type;

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);
661: l_lp_enrollment_ids varchar2(4000) := '';

Line 658: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;

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);
661: l_lp_enrollment_ids varchar2(4000) := '';
662:

Line 747: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;

743: -- Variables for IN/OUT parameters
744: --
745: -- Other variables
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:

Line 775: ota_lp_enrollments lpe,

771: SELECT lps.learning_path_id,
772: lpe.lp_enrollment_id,
773: lpc.learning_path_section_id
774: FROM ota_learning_paths lps,
775: ota_lp_enrollments lpe,
776: ota_lp_sections lpc
777: WHERE lps.learning_path_id = lpe.learning_path_id
778: AND lpc.learning_path_id = lps.learning_path_id
779: AND lps.path_source_code = 'TALENT_MGMT'

Line 786: FROM ota_lp_enrollments

782: AND (p_assignment_id IS NULL OR (p_assignment_id IS NOT NULL AND lps.assignment_id = p_assignment_id));
783:
784: CURSOR csr_get_lpe IS
785: SELECT lp_enrollment_id
786: FROM ota_lp_enrollments
787: WHERE learning_path_id = l_learning_path_id
788: AND person_id = p_person_id;
789:
790: CURSOR csr_get_lpc IS

Line 1041: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;

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

Line 1045: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;

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

Line 1047: l_no_of_mandatory_courses ota_lp_enrollments.no_of_mandatory_courses%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:
1051: l_lp_ovn number;

Line 1048: l_no_of_completed_courses ota_lp_enrollments.no_of_completed_courses%TYPE;

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:
1051: l_lp_ovn number;
1052: l_lpe_ovn number;

Line 1049: l_completion_date ota_lp_enrollments.completion_date%TYPE := null;

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:
1051: l_lp_ovn number;
1052: l_lpe_ovn number;
1053: l_lme_ovn number;

Line 1067: ota_lp_enrollments lpe

1063: lps.object_version_number lps_ovn,
1064: lpe.lp_enrollment_id,
1065: lpe.object_version_number lpe_ovn
1066: FROM ota_learning_paths lps,
1067: ota_lp_enrollments lpe
1068: WHERE lps.learning_path_id = lpe.learning_path_id
1069: AND lps.path_source_code = 'TALENT_MGMT'
1070: AND lps.source_function_code = p_source_function_code
1071: AND (p_source_id IS NULL OR (p_source_id IS NOT NULL AND lps.source_id = p_source_id))

Line 1507: FUNCTION get_lp_completion_date(p_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE)

1503: RETURN l_completion_date;
1504:
1505: END get_class_completion_date;
1506:
1507: FUNCTION get_lp_completion_date(p_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE)
1508: RETURN DATE IS
1509:
1510: CURSOR get_completion_date IS
1511: SELECT max(ota_lrng_path_member_util.get_class_completion_date(oev.event_id,lpe.person_id, lpe.contact_id)) completion_date

Line 1512: FROM ota_lp_enrollments lpe,

1508: RETURN DATE IS
1509:
1510: CURSOR get_completion_date IS
1511: SELECT max(ota_lrng_path_member_util.get_class_completion_date(oev.event_id,lpe.person_id, lpe.contact_id)) completion_date
1512: FROM ota_lp_enrollments lpe,
1513: ota_learning_path_members lpm,
1514: ota_events oev
1515: WHERE lpe.learning_path_id = lpm.learning_path_id
1516: AND oev.activity_version_id = lpm.activity_version_id

Line 1534: p_person_id ota_lp_enrollments.person_id%TYPE,

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
1537:
1538: CURSOR get_lpm_completion_date IS

Line 1535: p_contact_id ota_lp_enrollments.contact_id%TYPE)

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
1537:
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

Line 1540: FROM ota_lp_enrollments lpe,

1536: RETURN DATE IS
1537:
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