[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,
693: end get_event_start_date;
694:
695:
696: function person_matches_assoc(
697: p_person_id in per_people_f.person_id%type,
698: p_as_of in date,
699: p_assoc_person_id in ota_event_associations.person_id%type,
700: p_assoc_organization_id in ota_event_associations.organization_id%type,
701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
731: end person_matches_assoc;
732:
733:
734: function emp_matches_org(
735: p_person_id in per_people_f.person_id%type,
736: p_event_id in ota_events.event_id%type,
737: p_organization_id in ota_events.organization_id%type) return varchar2 is
738:
739: cursor csr_assignments(
736: p_event_id in ota_events.event_id%type,
737: p_organization_id in ota_events.organization_id%type) return varchar2 is
738:
739: cursor csr_assignments(
740: p_person_id per_people_f.person_id%type,
741: p_organization_id ota_event_associations.organization_id%type,
742: p_course_start_date otv_scheduled_events.course_start_date%type,
743: p_now date) is
744: select asg.assignment_id
767: end emp_matches_org;
768:
769: FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
770: p_organization_id IN ota_event_associations.organization_id%type,
771: p_person_id IN per_people_f.person_id%type)
772: RETURN VARCHAR2 IS
773:
774: CURSOR csr_lrnr_belongs_to_org IS
775: SELECT asg.assignment_id
810:
811: END learner_belongs_to_child_org;
812:
813: function emp_has_access(
814: p_person_id in per_people_f.person_id%type,
815: p_event_id in ota_events.event_id%type,
816: p_self_enroll_only in varchar2) return varchar2 is
817:
818: l_event_start_date ota_events.course_start_date%type;
953: END emp_has_valid_dates;
954:
955:
956: function employee_can_enroll(
957: p_person_id in per_people_f.person_id%type,
958: p_event_id in ota_events.event_id%type,
959: p_public_event_flag ota_events.public_event_flag%type,
960: p_max_internal in ota_events.maximum_internal_attendees%type,
961: p_event_start_date in otv_scheduled_events.course_start_date%type,
1014: end employee_can_enroll;
1015:
1016:
1017: function employee_can_self_enroll(
1018: p_person_id in per_people_f.person_id%type,
1019: p_event_id in ota_events.event_id%type,
1020: p_public_event_flag ota_events.public_event_flag%type,
1021: p_max_internal in ota_events.maximum_internal_attendees%type,
1022: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1075: end ext_learner_can_self_enroll;
1076:
1077:
1078: function learner_can_enroll(
1079: p_person_id in per_people_f.person_id%type,
1080: p_party_id in hz_parties.party_id%type,
1081: p_event_id in ota_events.event_id%type,
1082: p_public_event_flag in ota_events.public_event_flag%type,
1083: p_max_internal in ota_events.maximum_internal_attendees%type,
1092: end learner_can_enroll;
1093:
1094:
1095: function learner_can_enroll(
1096: p_person_id in per_people_f.person_id%type,
1097: p_party_id in hz_parties.party_id%type,
1098: p_event_id in ota_events.event_id%type,
1099: p_event_start_date in otv_scheduled_events.course_start_date%type,
1100: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1111: end learner_can_enroll;
1112:
1113:
1114: function learner_can_enroll(
1115: p_person_id in per_people_f.person_id%type,
1116: p_party_id in hz_parties.party_id%type,
1117: p_event_id in ota_events.event_id%type) return varchar2 is
1118:
1119: v_event_start_date ota_events.course_start_date%type;
1130: end learner_can_enroll;
1131:
1132:
1133: function learner_can_self_enroll(
1134: p_person_id in per_people_f.person_id%type,
1135: p_party_id in hz_parties.party_id%type,
1136: p_event_id in ota_events.event_id%type,
1137: p_public_event_flag in ota_events.public_event_flag%type,
1138: p_max_internal in ota_events.maximum_internal_attendees%type,
1147: end learner_can_self_enroll;
1148:
1149:
1150: function learner_can_self_enroll(
1151: p_person_id in per_people_f.person_id%type,
1152: p_party_id in hz_parties.party_id%type,
1153: p_event_id in ota_events.event_id%type,
1154: p_event_start_date in otv_scheduled_events.course_start_date%type,
1155: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1165: 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);
1166: end learner_can_self_enroll;
1167:
1168: function learner_can_self_enroll(
1169: p_person_id in per_people_f.person_id%type,
1170: p_party_id in hz_parties.party_id%type,
1171: p_event_id in ota_events.event_id%type) return varchar2 is
1172:
1173: v_event_start_date ota_events.course_start_date%type;
1184: end learner_can_self_enroll;
1185:
1186:
1187: function chk_delegate_ok_for_event(
1188: p_delegate_id in per_people_f.person_id%type,
1189: p_event_id in ota_events.event_id%type,
1190: p_event_start_date in otv_scheduled_events.course_start_date%type,
1191: p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1192: begin
1194: end chk_delegate_ok_for_event;
1195:
1196:
1197: function learner_can_see_category(
1198: p_person_id in per_people_f.person_id%type,
1199: p_party_id in hz_parties.party_id%type,
1200: p_category_usage_id in ota_category_usages.category_usage_id%type) return varchar2 is
1201:
1202: begin
1207: end learner_can_see_category;
1208:
1209:
1210: function learner_can_see_course(
1211: p_person_id in per_people_f.person_id%type,
1212: p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2 is
1213: begin
1214: return 'Y';
1215: end learner_can_see_course;
1215: end learner_can_see_course;
1216:
1217:
1218: function learner_can_see_offering(
1219: p_person_id in per_people_f.person_id%type,
1220: p_offering_id in ota_offerings.offering_id%type) return varchar2 is
1221: begin
1222: return 'Y';
1223: end learner_can_see_offering;
1223: end learner_can_see_offering;
1224:
1225:
1226: function emp_has_access_to_path(
1227: p_person_id in per_people_f.person_id%type,
1228: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1229: p_self_enroll_only in varchar2) return varchar2 is
1230:
1231: l_now date := sysdate;
1310: end ext_has_access_to_path;
1311:
1312:
1313: function emp_can_enroll_in_path(
1314: p_person_id in per_people_f.person_id%type,
1315: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1316: p_public_flag ota_learning_paths.public_flag%type,
1317: p_start_date_active in ota_learning_paths.start_date_active%type,
1318: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1325: end emp_can_enroll_in_path;
1326:
1327:
1328: function emp_can_self_enroll_in_path(
1329: p_person_id in per_people_f.person_id%type,
1330: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1331: p_public_flag ota_learning_paths.public_flag%type,
1332: p_start_date_active in ota_learning_paths.start_date_active%type,
1333: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1376: end ext_can_self_enroll_in_path;
1377:
1378:
1379: function learner_can_enroll_in_path(
1380: p_person_id in per_people_f.person_id%type,
1381: p_party_id in hz_parties.party_id%type,
1382: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1383: p_public_flag ota_learning_paths.public_flag%type,
1384: p_start_date_active in ota_learning_paths.start_date_active%type,
1411: end learner_can_enroll_in_path;
1412:
1413:
1414: function lrn_can_self_enroll_in_path(
1415: p_person_id in per_people_f.person_id%type,
1416: p_party_id in hz_parties.party_id%type,
1417: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1418: p_public_flag ota_learning_paths.public_flag%type,
1419: p_start_date_active in ota_learning_paths.start_date_active%type,
1447:
1448: --functions for forum(open) enrollments
1449:
1450: function emp_has_access_to_forum(
1451: p_person_id in per_people_f.person_id%type,
1452: p_forum_id in ota_forums_b.forum_id%type,
1453: p_self_enroll_only in varchar2) return varchar2 is
1454:
1455: l_now date := sysdate;
1534: end ext_has_access_to_forum;
1535:
1536:
1537: function emp_can_enroll_in_forum(
1538: p_person_id in per_people_f.person_id%type,
1539: p_forum_id in ota_forums_b.forum_id%type,
1540: p_public_flag ota_forums_b.public_flag%type,
1541: p_start_date_active in ota_forums_b.start_date_active%type,
1542: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1549: end emp_can_enroll_in_forum;
1550:
1551:
1552: function emp_can_self_enroll_in_forum(
1553: p_person_id in per_people_f.person_id%type,
1554: p_forum_id in ota_forums_b.forum_id%type,
1555: p_public_flag ota_forums_b.public_flag%type,
1556: p_start_date_active in ota_forums_b.start_date_active%type,
1557: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1600: end ext_can_self_enroll_in_forum;
1601:
1602:
1603: function learner_can_enroll_in_forum(
1604: p_person_id in per_people_f.person_id%type,
1605: p_party_id in hz_parties.party_id%type,
1606: p_forum_id in ota_forums_b.forum_id%type,
1607: p_public_flag ota_forums_b.public_flag%type,
1608: p_start_date_active in ota_forums_b.start_date_active%type,
1635: end learner_can_enroll_in_forum;
1636:
1637:
1638: function lrn_can_self_enroll_in_forum(
1639: p_person_id in per_people_f.person_id%type,
1640: p_party_id in hz_parties.party_id%type,
1641: p_forum_id in ota_forums_b.forum_id%type,
1642: p_public_flag ota_forums_b.public_flag%type,
1643: p_start_date_active in ota_forums_b.start_date_active%type,
1672:
1673: --functions for chats(open) enrollments
1674:
1675: function emp_has_access_to_chat(
1676: p_person_id in per_people_f.person_id%type,
1677: p_chat_id in ota_chats_b.chat_id%type,
1678: p_self_enroll_only in varchar2) return varchar2 is
1679:
1680: l_now date := sysdate;
1759: end ext_has_access_to_chat;
1760:
1761:
1762: function emp_can_enroll_in_chat(
1763: p_person_id in per_people_f.person_id%type,
1764: p_chat_id in ota_chats_b.chat_id%type,
1765: p_public_flag ota_chats_b.public_flag%type,
1766: p_start_date_active in ota_chats_b.start_date_active%type,
1767: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1774: end emp_can_enroll_in_chat;
1775:
1776:
1777: function emp_can_self_enroll_in_chat(
1778: p_person_id in per_people_f.person_id%type,
1779: p_chat_id in ota_chats_b.chat_id%type,
1780: p_public_flag ota_chats_b.public_flag%type,
1781: p_start_date_active in ota_chats_b.start_date_active%type,
1782: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1825: end ext_can_self_enroll_in_chat;
1826:
1827:
1828: function learner_can_enroll_in_chat(
1829: p_person_id in per_people_f.person_id%type,
1830: p_party_id in hz_parties.party_id%type,
1831: p_chat_id in ota_chats_b.chat_id%type,
1832: p_public_flag ota_chats_b.public_flag%type,
1833: p_start_date_active in ota_chats_b.start_date_active%type,
1860: end learner_can_enroll_in_chat;
1861:
1862:
1863: function lrn_can_self_enroll_in_chat(
1864: p_person_id in per_people_f.person_id%type,
1865: p_party_id in hz_parties.party_id%type,
1866: p_chat_id in ota_chats_b.chat_id%type,
1867: p_public_flag ota_chats_b.public_flag%type,
1868: p_start_date_active in ota_chats_b.start_date_active%type,
1896: --end of utilities for chats
1897:
1898:
1899: function emp_has_access_to_cert(
1900: p_person_id in per_people_f.person_id%type,
1901: p_certification_id in ota_certifications_b.certification_id%type,
1902: p_self_enroll_only in varchar2) return varchar2 is
1903:
1904: l_now date := sysdate;
1983: end ext_has_access_to_cert;
1984:
1985:
1986: function emp_can_enroll_in_cert(
1987: p_person_id in per_people_f.person_id%type,
1988: p_certification_id in ota_certifications_b.certification_id%type,
1989: p_public_flag ota_certifications_b.public_flag%type,
1990: p_start_date_active in ota_certifications_b.start_date_active%type,
1991: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1998: end emp_can_enroll_in_cert;
1999:
2000:
2001: function emp_can_self_enroll_in_cert(
2002: p_person_id in per_people_f.person_id%type,
2003: p_certification_id in ota_certifications_b.certification_id%type,
2004: p_public_flag ota_certifications_b.public_flag%type,
2005: p_start_date_active in ota_certifications_b.start_date_active%type,
2006: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2049: end ext_can_self_enroll_in_cert;
2050:
2051:
2052: function learner_can_enroll_in_cert(
2053: p_person_id in per_people_f.person_id%type,
2054: p_party_id in hz_parties.party_id%type,
2055: p_certification_id in ota_certifications_b.certification_id%type,
2056: p_public_flag ota_certifications_b.public_flag%type,
2057: p_start_date_active in ota_certifications_b.start_date_active%type,
2084: end learner_can_enroll_in_cert;
2085:
2086:
2087: function lrn_can_self_enroll_in_cert(
2088: p_person_id in per_people_f.person_id%type,
2089: p_party_id in hz_parties.party_id%type,
2090: p_certification_id in ota_certifications_b.certification_id%type,
2091: p_public_flag ota_certifications_b.public_flag%type,
2092: p_start_date_active in ota_certifications_b.start_date_active%type,
2175: END IF;
2176: END learner_can_enroll_in_cert;
2177:
2178: function learner_has_access_to_course(
2179: p_person_id in per_people_f.person_id%type,
2180: p_party_id in hz_parties.party_id%type,
2181: p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2 is
2182:
2183: begin
2198: If there is any change in this procedure make sure, the changes are
2199: done in the procedure get_ug_whereclause
2200: */
2201: function is_learner_in_user_group(
2202: p_person_id in per_people_f.person_id%type,
2203: p_user_group_id in ota_user_groups_b.user_group_id%type,
2204: p_business_group_id number) return varchar2 is
2205:
2206: is_avail BOOLEAN default false;
2234:
2235: end is_learner_in_user_group;
2236:
2237: function is_learner_matches_user_group(
2238: p_person_id in per_people_f.person_id%type,
2239: p_user_group_id in ota_user_groups_b.user_group_id%type,
2240: p_business_group_id number) return boolean is
2241:
2242: l_user_group_operator ota_user_groups_b.user_group_operator%type;
2239: p_user_group_id in ota_user_groups_b.user_group_id%type,
2240: p_business_group_id number) return boolean is
2241:
2242: l_user_group_operator ota_user_groups_b.user_group_operator%type;
2243: l_person_id per_people_f.person_id%type;
2244: l_user_group_id ota_user_groups_b.user_group_id%type;
2245: l_is_avail BOOLEAN default false;
2246:
2247: begin
2463: or business_group_id = p_business_group_id);
2464:
2465: SELECT count(a.user_group_element_id) into restricted_elements_count
2466: FROM OTA_USER_GROUP_ELEMENTS a,
2467: PER_PEOPLE_F e,
2468: PER_JOBS_VL job,
2469: HR_ORGANIZATION_UNITS org,
2470: PER_POSITIONS pos
2471: WHERE a.user_group_id in ( SELECT child_user_group_id