[Home] [Help]
386: /*Bug6679100-As exemployees have 2 records in per_all_assignments_f
387: modified csr_asg_details and csr_asg_details_recursive to use system_person_type while
388: validating the active assignment record.
389: cursor csr_asg_details(
390: p_person_id per_people_f.person_id%type,
391: p_organization_id ota_event_associations.organization_id%type,
392: p_job_id ota_event_associations.job_id%type,
393: p_position_id ota_event_associations.position_id%type,
394: p_as_of date) is
405: asg.assignment_type in ('E','A','C');
406:
407:
408: cursor csr_asg_details_recursive(
409: p_person_id per_people_f.person_id%type,
410: p_organization_id ota_event_associations.organization_id%type,
411: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
412: p_job_id ota_event_associations.job_id%type,
413: p_position_id ota_event_associations.position_id%type,
441: nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
442: asg.assignment_type in ('E','A','C');*/
443:
444: cursor csr_asg_details(
445: p_person_id per_people_f.person_id%type,
446: p_organization_id ota_event_associations.organization_id%type,
447: p_job_id ota_event_associations.job_id%type,
448: p_position_id ota_event_associations.position_id%type,
449: p_as_of date) is
476:
477:
478:
479: cursor csr_asg_details_recursive(
480: p_person_id per_people_f.person_id%type,
481: p_organization_id ota_event_associations.organization_id%type,
482: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
483: p_job_id ota_event_associations.job_id%type,
484: p_position_id ota_event_associations.position_id%type,
675: end get_event_start_date;
676:
677:
678: function person_matches_assoc(
679: p_person_id in per_people_f.person_id%type,
680: p_as_of in date,
681: p_assoc_person_id in ota_event_associations.person_id%type,
682: p_assoc_organization_id in ota_event_associations.organization_id%type,
683: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
713: end person_matches_assoc;
714:
715:
716: function emp_matches_org(
717: p_person_id in per_people_f.person_id%type,
718: p_event_id in ota_events.event_id%type,
719: p_organization_id in ota_events.organization_id%type) return varchar2 is
720:
721: cursor csr_assignments(
718: p_event_id in ota_events.event_id%type,
719: p_organization_id in ota_events.organization_id%type) return varchar2 is
720:
721: cursor csr_assignments(
722: p_person_id per_people_f.person_id%type,
723: p_organization_id ota_event_associations.organization_id%type,
724: p_course_start_date otv_scheduled_events.course_start_date%type,
725: p_now date) is
726: select asg.assignment_id
749: end emp_matches_org;
750:
751: FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
752: p_organization_id IN ota_event_associations.organization_id%type,
753: p_person_id IN per_people_f.person_id%type)
754: RETURN VARCHAR2 IS
755:
756: CURSOR csr_lrnr_belongs_to_org IS
757: SELECT asg.assignment_id
819: end is_user_enrolled;
820:
821:
822: function emp_has_access(
823: p_person_id in per_people_f.person_id%type,
824: p_event_id in ota_events.event_id%type,
825: p_self_enroll_only in varchar2) return varchar2 is
826:
827: l_event_start_date ota_events.course_start_date%type;
966: END emp_has_valid_dates;
967:
968:
969: function employee_can_enroll(
970: p_person_id in per_people_f.person_id%type,
971: p_event_id in ota_events.event_id%type,
972: p_public_event_flag ota_events.public_event_flag%type,
973: p_max_internal in ota_events.maximum_internal_attendees%type,
974: p_event_start_date in otv_scheduled_events.course_start_date%type,
1027: end employee_can_enroll;
1028:
1029:
1030: function employee_can_self_enroll(
1031: p_person_id in per_people_f.person_id%type,
1032: p_event_id in ota_events.event_id%type,
1033: p_public_event_flag ota_events.public_event_flag%type,
1034: p_max_internal in ota_events.maximum_internal_attendees%type,
1035: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1088: end ext_learner_can_self_enroll;
1089:
1090:
1091: function learner_can_enroll(
1092: p_person_id in per_people_f.person_id%type,
1093: p_party_id in hz_parties.party_id%type,
1094: p_event_id in ota_events.event_id%type,
1095: p_public_event_flag in ota_events.public_event_flag%type,
1096: p_max_internal in ota_events.maximum_internal_attendees%type,
1105: end learner_can_enroll;
1106:
1107:
1108: function learner_can_enroll(
1109: p_person_id in per_people_f.person_id%type,
1110: p_party_id in hz_parties.party_id%type,
1111: p_event_id in ota_events.event_id%type,
1112: p_event_start_date in otv_scheduled_events.course_start_date%type,
1113: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1124: end learner_can_enroll;
1125:
1126:
1127: function learner_can_enroll(
1128: p_person_id in per_people_f.person_id%type,
1129: p_party_id in hz_parties.party_id%type,
1130: p_event_id in ota_events.event_id%type) return varchar2 is
1131:
1132: v_event_start_date ota_events.course_start_date%type;
1143: end learner_can_enroll;
1144:
1145:
1146: function learner_can_self_enroll(
1147: p_person_id in per_people_f.person_id%type,
1148: p_party_id in hz_parties.party_id%type,
1149: p_event_id in ota_events.event_id%type,
1150: p_public_event_flag in ota_events.public_event_flag%type,
1151: p_max_internal in ota_events.maximum_internal_attendees%type,
1160: end learner_can_self_enroll;
1161:
1162:
1163: function learner_can_self_enroll(
1164: p_person_id in per_people_f.person_id%type,
1165: p_party_id in hz_parties.party_id%type,
1166: p_event_id in ota_events.event_id%type,
1167: p_event_start_date in otv_scheduled_events.course_start_date%type,
1168: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1178: return learner_can_self_enroll(p_person_id, p_party_id, p_event_id, v_public_event_flag, v_max_internal, p_event_start_date, p_parent_offering_id);
1179: end learner_can_self_enroll;
1180:
1181: function learner_can_self_enroll(
1182: p_person_id in per_people_f.person_id%type,
1183: p_party_id in hz_parties.party_id%type,
1184: p_event_id in ota_events.event_id%type) return varchar2 is
1185:
1186: v_event_start_date ota_events.course_start_date%type;
1197: end learner_can_self_enroll;
1198:
1199:
1200: function chk_delegate_ok_for_event(
1201: p_delegate_id in per_people_f.person_id%type,
1202: p_event_id in ota_events.event_id%type,
1203: p_event_start_date in otv_scheduled_events.course_start_date%type,
1204: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1205: begin
1207: end chk_delegate_ok_for_event;
1208:
1209:
1210: function learner_can_see_category(
1211: p_person_id in per_people_f.person_id%type,
1212: p_party_id in hz_parties.party_id%type,
1213: p_category_usage_id in ota_category_usages.category_usage_id%type) return varchar2 is
1214:
1215: begin
1220: end learner_can_see_category;
1221:
1222:
1223: function learner_can_see_course(
1224: p_person_id in per_people_f.person_id%type,
1225: p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2 is
1226: begin
1227: return 'Y';
1228: end learner_can_see_course;
1228: end learner_can_see_course;
1229:
1230:
1231: function learner_can_see_offering(
1232: p_person_id in per_people_f.person_id%type,
1233: p_offering_id in ota_offerings.offering_id%type) return varchar2 is
1234: begin
1235: return 'Y';
1236: end learner_can_see_offering;
1236: end learner_can_see_offering;
1237:
1238:
1239: function emp_has_access_to_path(
1240: p_person_id in per_people_f.person_id%type,
1241: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1242: p_self_enroll_only in varchar2) return varchar2 is
1243:
1244: l_now date := sysdate;
1323: end ext_has_access_to_path;
1324:
1325:
1326: function emp_can_enroll_in_path(
1327: p_person_id in per_people_f.person_id%type,
1328: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1329: p_public_flag ota_learning_paths.public_flag%type,
1330: p_start_date_active in ota_learning_paths.start_date_active%type,
1331: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1338: end emp_can_enroll_in_path;
1339:
1340:
1341: function emp_can_self_enroll_in_path(
1342: p_person_id in per_people_f.person_id%type,
1343: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1344: p_public_flag ota_learning_paths.public_flag%type,
1345: p_start_date_active in ota_learning_paths.start_date_active%type,
1346: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1389: end ext_can_self_enroll_in_path;
1390:
1391:
1392: function learner_can_enroll_in_path(
1393: p_person_id in per_people_f.person_id%type,
1394: p_party_id in hz_parties.party_id%type,
1395: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1396: p_public_flag ota_learning_paths.public_flag%type,
1397: p_start_date_active in ota_learning_paths.start_date_active%type,
1424: end learner_can_enroll_in_path;
1425:
1426:
1427: function lrn_can_self_enroll_in_path(
1428: p_person_id in per_people_f.person_id%type,
1429: p_party_id in hz_parties.party_id%type,
1430: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1431: p_public_flag ota_learning_paths.public_flag%type,
1432: p_start_date_active in ota_learning_paths.start_date_active%type,
1460:
1461: --functions for forum(open) enrollments
1462:
1463: function emp_has_access_to_forum(
1464: p_person_id in per_people_f.person_id%type,
1465: p_forum_id in ota_forums_b.forum_id%type,
1466: p_self_enroll_only in varchar2) return varchar2 is
1467:
1468: l_now date := sysdate;
1547: end ext_has_access_to_forum;
1548:
1549:
1550: function emp_can_enroll_in_forum(
1551: p_person_id in per_people_f.person_id%type,
1552: p_forum_id in ota_forums_b.forum_id%type,
1553: p_public_flag ota_forums_b.public_flag%type,
1554: p_start_date_active in ota_forums_b.start_date_active%type,
1555: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1562: end emp_can_enroll_in_forum;
1563:
1564:
1565: function emp_can_self_enroll_in_forum(
1566: p_person_id in per_people_f.person_id%type,
1567: p_forum_id in ota_forums_b.forum_id%type,
1568: p_public_flag ota_forums_b.public_flag%type,
1569: p_start_date_active in ota_forums_b.start_date_active%type,
1570: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1613: end ext_can_self_enroll_in_forum;
1614:
1615:
1616: function learner_can_enroll_in_forum(
1617: p_person_id in per_people_f.person_id%type,
1618: p_party_id in hz_parties.party_id%type,
1619: p_forum_id in ota_forums_b.forum_id%type,
1620: p_public_flag ota_forums_b.public_flag%type,
1621: p_start_date_active in ota_forums_b.start_date_active%type,
1648: end learner_can_enroll_in_forum;
1649:
1650:
1651: function lrn_can_self_enroll_in_forum(
1652: p_person_id in per_people_f.person_id%type,
1653: p_party_id in hz_parties.party_id%type,
1654: p_forum_id in ota_forums_b.forum_id%type,
1655: p_public_flag ota_forums_b.public_flag%type,
1656: p_start_date_active in ota_forums_b.start_date_active%type,
1685:
1686: --functions for chats(open) enrollments
1687:
1688: function emp_has_access_to_chat(
1689: p_person_id in per_people_f.person_id%type,
1690: p_chat_id in ota_chats_b.chat_id%type,
1691: p_self_enroll_only in varchar2) return varchar2 is
1692:
1693: l_now date := sysdate;
1772: end ext_has_access_to_chat;
1773:
1774:
1775: function emp_can_enroll_in_chat(
1776: p_person_id in per_people_f.person_id%type,
1777: p_chat_id in ota_chats_b.chat_id%type,
1778: p_public_flag ota_chats_b.public_flag%type,
1779: p_start_date_active in ota_chats_b.start_date_active%type,
1780: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1787: end emp_can_enroll_in_chat;
1788:
1789:
1790: function emp_can_self_enroll_in_chat(
1791: p_person_id in per_people_f.person_id%type,
1792: p_chat_id in ota_chats_b.chat_id%type,
1793: p_public_flag ota_chats_b.public_flag%type,
1794: p_start_date_active in ota_chats_b.start_date_active%type,
1795: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1838: end ext_can_self_enroll_in_chat;
1839:
1840:
1841: function learner_can_enroll_in_chat(
1842: p_person_id in per_people_f.person_id%type,
1843: p_party_id in hz_parties.party_id%type,
1844: p_chat_id in ota_chats_b.chat_id%type,
1845: p_public_flag ota_chats_b.public_flag%type,
1846: p_start_date_active in ota_chats_b.start_date_active%type,
1873: end learner_can_enroll_in_chat;
1874:
1875:
1876: function lrn_can_self_enroll_in_chat(
1877: p_person_id in per_people_f.person_id%type,
1878: p_party_id in hz_parties.party_id%type,
1879: p_chat_id in ota_chats_b.chat_id%type,
1880: p_public_flag ota_chats_b.public_flag%type,
1881: p_start_date_active in ota_chats_b.start_date_active%type,
1909: --end of utilities for chats
1910:
1911:
1912: function emp_has_access_to_cert(
1913: p_person_id in per_people_f.person_id%type,
1914: p_certification_id in ota_certifications_b.certification_id%type,
1915: p_self_enroll_only in varchar2) return varchar2 is
1916:
1917: l_now date := sysdate;
1996: end ext_has_access_to_cert;
1997:
1998:
1999: function emp_can_enroll_in_cert(
2000: p_person_id in per_people_f.person_id%type,
2001: p_certification_id in ota_certifications_b.certification_id%type,
2002: p_public_flag ota_certifications_b.public_flag%type,
2003: p_start_date_active in ota_certifications_b.start_date_active%type,
2004: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2011: end emp_can_enroll_in_cert;
2012:
2013:
2014: function emp_can_self_enroll_in_cert(
2015: p_person_id in per_people_f.person_id%type,
2016: p_certification_id in ota_certifications_b.certification_id%type,
2017: p_public_flag ota_certifications_b.public_flag%type,
2018: p_start_date_active in ota_certifications_b.start_date_active%type,
2019: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2062: end ext_can_self_enroll_in_cert;
2063:
2064:
2065: function learner_can_enroll_in_cert(
2066: p_person_id in per_people_f.person_id%type,
2067: p_party_id in hz_parties.party_id%type,
2068: p_certification_id in ota_certifications_b.certification_id%type,
2069: p_public_flag ota_certifications_b.public_flag%type,
2070: p_start_date_active in ota_certifications_b.start_date_active%type,
2097: end learner_can_enroll_in_cert;
2098:
2099:
2100: function lrn_can_self_enroll_in_cert(
2101: p_person_id in per_people_f.person_id%type,
2102: p_party_id in hz_parties.party_id%type,
2103: p_certification_id in ota_certifications_b.certification_id%type,
2104: p_public_flag ota_certifications_b.public_flag%type,
2105: p_start_date_active in ota_certifications_b.start_date_active%type,
2188: END IF;
2189: END learner_can_enroll_in_cert;
2190:
2191: function learner_has_access_to_course(
2192: p_person_id in per_people_f.person_id%type,
2193: p_party_id in hz_parties.party_id%type,
2194: p_activity_version_id in ota_activity_versions.activity_version_id%type,
2195: showLPOnlyClasses in varchar2 default 'N') return varchar2 is
2196:
2279: If there is any change in this procedure make sure, the changes are
2280: done in the procedure get_ug_whereclause
2281: */
2282: function is_learner_in_user_group(
2283: p_person_id in per_people_f.person_id%type,
2284: p_user_group_id in ota_user_groups_b.user_group_id%type,
2285: p_business_group_id number,
2286: p_ignore_ug_date_check varchar2 default 'N') return varchar2 is
2287:
2318:
2319: end is_learner_in_user_group;
2320:
2321: function is_learner_matches_user_group(
2322: p_person_id in per_people_f.person_id%type,
2323: p_user_group_id in ota_user_groups_b.user_group_id%type,
2324: p_business_group_id number,
2325: p_ignore_ug_date_check varchar2 default 'N') return boolean is
2326:
2324: p_business_group_id number,
2325: p_ignore_ug_date_check varchar2 default 'N') return boolean is
2326:
2327: l_user_group_operator ota_user_groups_b.user_group_operator%type;
2328: l_person_id per_people_f.person_id%type;
2329: l_user_group_id ota_user_groups_b.user_group_id%type;
2330: l_is_avail BOOLEAN default false;
2331:
2332: begin
2598: and( person_id is null or elig_prfl_id is null);
2599:
2600: SELECT count(a.user_group_element_id) into restricted_elements_count
2601: FROM OTA_USER_GROUP_ELEMENTS a,
2602: PER_PEOPLE_F e,
2603: PER_JOBS_VL job,
2604: HR_ORGANIZATION_UNITS org,
2605: PER_POSITIONS pos,
2606: BEN_ELIGY_PRFL_F elig_profile