[Home] [Help]
146: l_date ota_events.course_end_date%type;
147: l_conc_request_id OTA_MAND_MULTI_ENR_REQUESTS.conc_program_request_id%type;
148: l_event_title ota_events_tl.title%type;
149: l_request_id number;
150: l_person_name per_all_people_f.full_name%type;
151: l_mand_multi_conc_exist number;
152: l_chunk_size number := nvl(p_chunk_size,1000);
153: l_threads number := nvl(p_thread_count,3);
154: OTA_INCOMP_MAND_MULTI_EXIST exception;
373:
374:
375:
376:
377: FUNCTION learner_is_notSelected_inClass(p_person_id IN per_all_people_f.person_id%type
378: ,p_assignment_id per_all_assignments_f.assignment_id%type
379: ,p_event_id IN ota_events.event_id%type)
380: RETURN Boolean IS
381:
387: AND event_id = p_event_id
388: AND create_enrollment = 'Y';
389:
390: l_lrnr_assignment_id per_all_assignments_f.assignment_id%type;
391: l_person_name per_all_people_f.full_name%type;
392: l_proc varchar2(72) := g_package||'learner_is_notSelected_inClass';
393:
394: BEGIN
395:
424:
425:
426: END learner_is_notSelected_inClass;
427:
428: FUNCTION lrn_is_notSelected_inClass_mul(p_person_id IN per_all_people_f.person_id%type
429: ,p_assignment_id per_all_assignments_f.assignment_id%type
430: ,p_event_id IN ota_events.event_id%type)
431: RETURN Boolean IS
432:
438: AND event_id = p_event_id
439: AND create_enrollment = 'Y';
440:
441: l_lrnr_assignment_id per_all_assignments_f.assignment_id%type;
442: l_person_name per_all_people_f.full_name%type;
443: l_proc varchar2(72) := g_package||'lrn_is_notSelected_inClass_mul';
444:
445: BEGIN
446:
476:
477: END lrn_is_notSelected_inClass_mul;
478:
479:
480: FUNCTION learner_is_notSelected_inCert(p_person_id IN per_all_people_f.person_id%type
481: ,p_assignment_id per_all_assignments_f.assignment_id%type
482: ,p_certification_id IN ota_certifications_b.certification_id%type default NULL)
483: RETURN Boolean IS
484:
491: AND create_enrollment = 'Y';
492:
493:
494: l_lrnr_assignment_id per_all_assignments_f.assignment_id%type;
495: l_person_name per_all_people_f.full_name%type;
496: l_proc varchar2(72) := g_package||'learner_is_notSelected_inCert';
497:
498: BEGIN
499:
623: paf.assignment_id
624: FROM
625: per_all_assignments_f paf,
626: per_person_types ppt,
627: per_all_people_f perp,
628: per_person_type_usages_f ptu
629: WHERE
630: paf.person_id = l_person_id
631: AND perp.person_id =paf.person_id
716: paf.assignment_id
717: FROM
718: per_all_assignments_f paf,
719: per_person_types ppt,
720: per_all_people_f perp,
721: per_person_type_usages_f ptu
722: WHERE
723: paf.person_id = l_person_id
724: AND perp.person_id =paf.person_id
812: TYPE learners_in_usergroup IS REF CURSOR;
813: csr_get_lrnr_in_ug learners_in_usergroup;
814:
815: TYPE learner_rec IS RECORD(
816: person_id per_all_people_f.person_id%type,
817: job_id per_jobs_tl.job_id%type,
818: position_id per_all_positions.position_id%type,
819: completed_crs_prereq varchar2(1),
820: completed_comp_prereq varchar2(1),
836: ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
837: ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
838: , paf.organization_id
839: , paf.assignment_id
840: FROM per_all_people_f ppf
841: ,per_all_assignments_f paf
842: ,per_person_type_usages_f ptu
843: ,per_person_types pts
844: ,per_business_groups pbg
893: ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
894: ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
895: , paf.organization_id
896: , paf.assignment_id
897: FROM per_all_people_f ppf
898: ,per_all_assignments_f paf
899: ,per_jobs_tl pjt
900: ,per_all_positions pps
901: ,per_person_type_usages_f ptu
945: l_numberof_records_processed NUMBER:= 0;
946: l_create_enrollment varchar2(1);
947: l_completed_crs_prereq varchar2(1);
948: l_completed_comp_prereq varchar2(1);
949: l_person_name per_all_people_f.full_name%type;
950: l_event_start_date date;
951:
952: l_allow_future_end_prof_val varchar2(1);
953: l_per_bg_grp_prof_val number;
1016: ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,''E'',:1 ) completed_crs_prereq
1017: ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,:2) completed_comp_prereq
1018: , paf.organization_id organization_id
1019: , paf.assignment_id assignment_id
1020: FROM per_all_people_f ppf
1021: ,per_all_assignments_f paf
1022: ,per_jobs_tl pjt
1023: ,per_all_positions pps
1024: ,per_person_type_usages_f ptu
1195: TYPE learners_in_usergroup IS REF CURSOR;
1196: csr_get_lrnr_in_ug learners_in_usergroup;
1197:
1198: TYPE learner_rec IS RECORD(
1199: person_id per_all_people_f.person_id%type,
1200: job_id per_jobs_tl.job_id%type,
1201: position_id per_all_positions.position_id%type,
1202: completed_crs_prereq varchar2(1),
1203: completed_comp_prereq varchar2(1),
1219: ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
1220: ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
1221: , paf.organization_id
1222: , paf.assignment_id
1223: FROM per_all_people_f ppf
1224: ,per_all_assignments_f paf
1225: ,per_person_type_usages_f ptu
1226: ,per_person_types pts
1227: ,per_business_groups pbg
1276: ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
1277: ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
1278: , paf.organization_id
1279: , paf.assignment_id
1280: FROM per_all_people_f ppf
1281: ,per_all_assignments_f paf
1282: ,per_jobs_tl pjt
1283: ,per_all_positions pps
1284: ,per_person_type_usages_f ptu
1328: l_numberof_records_processed NUMBER:= 0;
1329: l_create_enrollment varchar2(1);
1330: l_completed_crs_prereq varchar2(1);
1331: l_completed_comp_prereq varchar2(1);
1332: l_person_name per_all_people_f.full_name%type;
1333: l_event_start_date date;
1334:
1335: l_allow_future_end_prof_val varchar2(1);
1336: l_per_bg_grp_prof_val number;
1471: ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,''E'',:1 ) completed_crs_prereq
1472: ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,:2) completed_comp_prereq
1473: , paf.organization_id organization_id
1474: , paf.assignment_id assignment_id
1475: FROM per_all_people_f ppf
1476: ,per_all_assignments_f paf
1477: ,per_jobs_tl pjt
1478: ,per_all_positions pps
1479: ,per_person_type_usages_f ptu
1765:
1766:
1767: l_cost_center_info csr_get_cost_center_info%rowtype;
1768:
1769: l_person_name per_all_people_f.full_name%type;
1770: l_class_name ota_events_tl.title%type;
1771: l_proc varchar2(72) := g_package||'create_enrollments';
1772:
1773: BEGIN
1925:
1926:
1927: l_cost_center_info csr_get_cost_center_info%rowtype;
1928:
1929: l_person_name per_all_people_f.full_name%type;
1930: l_class_name ota_events_tl.title%type;
1931: l_proc varchar2(72) := g_package||'create_enrollments_multi';
1932:
1933: BEGIN
2053: l_item_type wf_items.item_type%type := 'OTWF';
2054: l_item_key wf_items.item_key%type;
2055:
2056: l_user_name varchar2(80);
2057: l_person_full_name per_all_people_f.FULL_NAME%TYPE;
2058: l_role_name wf_roles.name%type;
2059: l_role_display_name wf_roles.display_name%type;
2060:
2061: l_process_display_name varchar2(240);
2065: and trunc(sysdate) between trunc(start_date) and trunc(nvl(end_date, sysdate+1));
2066:
2067:
2068: CURSOR csr_get_person_name(p_person_id IN number) IS
2069: SELECT ppf.full_name FROM per_all_people_f ppf WHERE person_id = p_person_id;
2070:
2071: BEGIN
2072: hr_utility.set_location('Entering:'||l_proc, 5);
2073:
2246: l_cert_name ota_certifications_tl.name%type;
2247: l_chunk_size number := nvl(p_chunk_size,1000);
2248: l_threads number := nvl(p_thread_count,3);
2249: l_request_id number;
2250: l_person_name per_all_people_f.full_name%type;
2251: l_proc varchar2(72) := g_package||'process_automatic_cert_subscr';
2252:
2253:
2254: BEGIN
2395: TYPE learners_in_usergroup IS REF CURSOR;
2396: csr_get_lrnr_in_ug learners_in_usergroup;
2397:
2398: TYPE learner_rec IS RECORD(
2399: person_id per_all_people_f.person_id%type,
2400: job_id per_jobs_tl.job_id%type,
2401: position_id per_all_positions.position_id%type,
2402: organization_id per_all_assignments_f.organization_id%type,
2403: assignment_id per_all_assignments_f.assignment_id%type
2411: ) IS
2412: SELECT ppf.person_id
2413: , paf.organization_id
2414: , paf.assignment_id
2415: FROM per_all_people_f ppf
2416: ,per_all_assignments_f paf
2417: ,per_person_type_usages_f ptu
2418: ,per_person_types pts
2419: ,per_business_groups pbg
2454: , pjt.job_id Job_Id
2455: , pps.position_id
2456: , paf.organization_id
2457: , paf.assignment_id
2458: FROM per_all_people_f ppf
2459: ,per_all_assignments_f paf
2460: ,per_jobs_tl pjt
2461: ,per_all_positions pps
2462: ,per_person_type_usages_f ptu
2500:
2501:
2502:
2503: l_numberof_records_processed NUMBER:= 0;
2504: l_person_name per_all_people_f.full_name%type;
2505:
2506:
2507: l_per_bg_grp_prof_val number;
2508: l_ota_global_bg_prof_val number;
2678: , pjt.job_id job_id
2679: , pps.position_id position_id
2680: , paf.organization_id organization_id
2681: , paf.assignment_id assignment_id
2682: FROM per_all_people_f ppf
2683: ,per_all_assignments_f paf
2684: ,per_jobs_tl pjt
2685: ,per_all_positions pps
2686: ,per_person_type_usages_f ptu
3025: paf.assignment_id
3026: FROM
3027: per_all_assignments_f paf,
3028: per_person_types ppt,
3029: per_all_people_f perp,
3030: per_person_type_usages_f ptu
3031: WHERE
3032: paf.person_id = l_person_id
3033: AND perp.person_id =paf.person_id
3129: l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;
3130: l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;
3131: l_request_rec get_all_mandatory_enr_requests%rowtype;
3132: l_req_member_rec csr_get_request_members%rowtype;
3133: l_person_name per_all_people_f.full_name%type;
3134: l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;
3135: l_certification_status_code ota_cert_enrollments.CERTIFICATION_STATUS_CODE%TYPE;
3136: l_certification_name ota_certifications_tl.name%type;
3137: