APPS.OTA_LP_NOTIFY_SS dependencies on OTA_LP_ENROLLMENTS

Line 96: ota_lp_enrollments lpe

92: lps.notify_days_before_target,
93: lpe.creator_person_id
94: FROM ota_learning_paths lps,
95: ota_learning_paths_tl lpt,
96: ota_lp_enrollments lpe
97: WHERE lpt.language = USERENV('LANG')
98: AND lps.learning_path_id = lpt.learning_path_id
99: AND lpe.lp_enrollment_id = p_lp_enrollment_id
100: AND lpt.learning_path_id = lpe.learning_path_id;

Line 114: ota_lp_enrollments lpe,

110: lpm.notify_days_before_target
111: FROM ota_lp_member_enrollments lpme,
112: ota_learning_path_members lpm,
113: ota_activity_versions_tl avt,
114: ota_lp_enrollments lpe,
115: ota_learning_paths lps,
116: ota_learning_paths_tl lpt
117: WHERE lpme.lp_member_enrollment_id = p_lp_member_enrollment_id
118: AND lps.learning_path_id = lpt.learning_path_id

Line 404: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;

400: ,actid IN NUMBER
401: ,funcmode IN VARCHAR2
402: ,resultout OUT nocopy VARCHAR2 ) AS
403:
404: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
405: l_manager_user_name fnd_user.user_name%TYPE;
406:
407: CURSOR csr_get_manager_name IS
408: SELECT ppf.full_name, ppf.person_id

Line 409: FROM ota_lp_enrollments lpe,

405: l_manager_user_name fnd_user.user_name%TYPE;
406:
407: CURSOR csr_get_manager_name IS
408: SELECT ppf.full_name, ppf.person_id
409: FROM ota_lp_enrollments lpe,
410: ota_learning_paths lps,
411: per_all_people_f ppf,
412: per_all_assignments_f paf
413: WHERE trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date

Line 471: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;

467: ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
468: ,actid IN NUMBER
469: ,funcmode IN VARCHAR2
470: ,resultout OUT nocopy VARCHAR2 ) AS
471: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
472: l_manager_id OTA_LP_ENROLLMENTS.creator_person_id%TYPE;
473: l_manager_user_name fnd_user.user_name%TYPE;
474:
475: CURSOR csr_get_creator_name IS

Line 472: l_manager_id OTA_LP_ENROLLMENTS.creator_person_id%TYPE;

468: ,actid IN NUMBER
469: ,funcmode IN VARCHAR2
470: ,resultout OUT nocopy VARCHAR2 ) AS
471: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
472: l_manager_id OTA_LP_ENROLLMENTS.creator_person_id%TYPE;
473: l_manager_user_name fnd_user.user_name%TYPE;
474:
475: CURSOR csr_get_creator_name IS
476: SELECT ppf.full_name, ppf.person_id

Line 477: FROM ota_lp_enrollments lpe,

473: l_manager_user_name fnd_user.user_name%TYPE;
474:
475: CURSOR csr_get_creator_name IS
476: SELECT ppf.full_name, ppf.person_id
477: FROM ota_lp_enrollments lpe,
478: ota_learning_paths lps,
479: per_all_people_f ppf
480: WHERE trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
481: AND ppf.person_id = lpe.creator_person_id

Line 564: ota_lp_enrollments lpe

560: ,lpe.contact_id
561: ,lpe.creator_person_id
562: ,lps.notify_days_before_target
563: FROM ota_learning_paths lps,
564: ota_lp_enrollments lpe
565: WHERE lps.learning_path_id = lpe.learning_path_id
566: AND lpe.path_status_code = 'ACTIVE'
567: AND trunc(sysdate) + lps.notify_days_before_target = trunc(lpe.completion_target_date)
568: AND lps.business_group_id = ota_general.get_business_group_id;

Line 614: ota_lp_enrollments lpe,

610: ,lpm.notify_days_before_target
611: FROM ota_learning_path_members lpm,
612: ota_lp_member_enrollments lpme,
613: ota_learning_paths lps,
614: ota_lp_enrollments lpe,
615: ota_lp_sections lpc
616: WHERE lpe.learning_path_id = lps.learning_path_id
617: AND lpc.learning_path_id = lps.learning_path_id
618: AND lpc.learning_path_section_id = lpm.learning_path_section_id

Line 640: l_creator_person_id OTA_LP_ENROLLMENTS.CREATOR_PERSON_ID%TYPE;

636: l_proc varchar2(72) := g_package|| ' send_lpm_ct_notifications';
637: l_lpm_notification_type VARCHAR2(30);
638: l_person_id OTA_LEARNING_PATHS.PERSON_ID%TYPE;
639: l_contact_id OTA_LEARNING_PATHS.CONTACT_ID%TYPE;
640: l_creator_person_id OTA_LP_ENROLLMENTS.CREATOR_PERSON_ID%TYPE;
641: l_path_source_code OTA_LEARNING_PATHS.PATH_SOURCE_CODE%TYPE;
642: l_key varchar2(80);
643: BEGIN
644: