APPS.OTA_LP_NOTIFY_SS dependencies on OTA_LP_ENROLLMENTS

Line 95: ota_lp_enrollments lpe

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

Line 113: ota_lp_enrollments lpe,

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

Line 383: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;

379: ,actid IN NUMBER
380: ,funcmode IN VARCHAR2
381: ,resultout OUT nocopy VARCHAR2 ) AS
382:
383: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
384: l_manager_user_name fnd_user.user_name%TYPE;
385:
386: CURSOR csr_get_manager_name IS
387: SELECT ppf.full_name, ppf.person_id

Line 388: FROM ota_lp_enrollments lpe,

384: l_manager_user_name fnd_user.user_name%TYPE;
385:
386: CURSOR csr_get_manager_name IS
387: SELECT ppf.full_name, ppf.person_id
388: FROM ota_lp_enrollments lpe,
389: ota_learning_paths lps,
390: per_all_people_f ppf,
391: per_all_assignments_f paf
392: WHERE trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date

Line 450: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;

446: ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
447: ,actid IN NUMBER
448: ,funcmode IN VARCHAR2
449: ,resultout OUT nocopy VARCHAR2 ) AS
450: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
451: l_manager_id OTA_LP_ENROLLMENTS.creator_person_id%TYPE;
452: l_manager_user_name fnd_user.user_name%TYPE;
453:
454: CURSOR csr_get_creator_name IS

Line 451: l_manager_id OTA_LP_ENROLLMENTS.creator_person_id%TYPE;

447: ,actid IN NUMBER
448: ,funcmode IN VARCHAR2
449: ,resultout OUT nocopy VARCHAR2 ) AS
450: l_lp_enrollment_id OTA_LP_ENROLLMENTS.lp_enrollment_id%TYPE;
451: l_manager_id OTA_LP_ENROLLMENTS.creator_person_id%TYPE;
452: l_manager_user_name fnd_user.user_name%TYPE;
453:
454: CURSOR csr_get_creator_name IS
455: SELECT ppf.full_name, ppf.person_id

Line 456: FROM ota_lp_enrollments lpe,

452: l_manager_user_name fnd_user.user_name%TYPE;
453:
454: CURSOR csr_get_creator_name IS
455: SELECT ppf.full_name, ppf.person_id
456: FROM ota_lp_enrollments lpe,
457: ota_learning_paths lps,
458: per_all_people_f ppf
459: WHERE trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
460: AND ppf.person_id = lpe.creator_person_id

Line 543: ota_lp_enrollments lpe

539: ,lpe.contact_id
540: ,lpe.creator_person_id
541: ,lps.notify_days_before_target
542: FROM ota_learning_paths lps,
543: ota_lp_enrollments lpe
544: WHERE lps.learning_path_id = lpe.learning_path_id
545: AND lpe.path_status_code = 'ACTIVE'
546: AND trunc(sysdate) + lps.notify_days_before_target = trunc(lpe.completion_target_date)
547: AND lps.business_group_id = ota_general.get_business_group_id;

Line 593: ota_lp_enrollments lpe,

589: ,lpm.notify_days_before_target
590: FROM ota_learning_path_members lpm,
591: ota_lp_member_enrollments lpme,
592: ota_learning_paths lps,
593: ota_lp_enrollments lpe,
594: ota_lp_sections lpc
595: WHERE lpe.learning_path_id = lps.learning_path_id
596: AND lpc.learning_path_id = lps.learning_path_id
597: AND lpc.learning_path_section_id = lpm.learning_path_section_id

Line 619: l_creator_person_id OTA_LP_ENROLLMENTS.CREATOR_PERSON_ID%TYPE;

615: l_proc varchar2(72) := g_package|| ' send_lpm_ct_notifications';
616: l_lpm_notification_type VARCHAR2(30);
617: l_person_id OTA_LEARNING_PATHS.PERSON_ID%TYPE;
618: l_contact_id OTA_LEARNING_PATHS.CONTACT_ID%TYPE;
619: l_creator_person_id OTA_LP_ENROLLMENTS.CREATOR_PERSON_ID%TYPE;
620: l_path_source_code OTA_LEARNING_PATHS.PATH_SOURCE_CODE%TYPE;
621: l_key varchar2(80);
622: BEGIN
623: