PACKAGE: APPS.OTA_MANDATORY_ENROLL_UTIL

Source

1 package ota_mandatory_enroll_util as
2 /* $Header: otmandatoryenr.pkh 120.3 2008/01/25 11:48:11 shwnayak noship $ */
3 
4 
5 CURSOR csr_get_person_name(p_person_id NUMBER) IS
6     SELECT full_name
7     FROM per_all_people_f
8     WHERE trunc(sysdate) between effective_start_date and effective_end_date
9     AND person_id = p_person_id;
10 
11 CURSOR get_class_name(p_event_id IN NUMBER) IS
12     SELECT title
13     FROM OTA_EVENTS_TL
14     WHERE event_id = p_event_id
15     AND language=userenv('LANG');
16 
17 CURSOR get_all_mandatory_enr_requests(p_conc_request_id IN NUMBER) IS
18     SELECT
19     MANDATORY_ENR_REQUEST_ID,
20     REQUESTOR_ID,
21     EVENT_ID,
22     ENR_PREREQ_TYPE,
23     PERSON_ID,
24     ORGANIZATION_ID,
25     ORG_STRUCTURE_VERSION_ID,
26     JOB_ID,
27     POSITION_ID,
28     USERGROUP_ID
29     FROM ota_mandatory_enr_requests
30     WHERE conc_program_request_id = p_conc_request_id;
31 
32 CURSOR unprocessed_enrollments is
33 	 SELECT distinct
34 	 evt_tl.event_id event_id,
35 	 evt_tl.title title,
36 	 reqm.person_id person_id,
37 	 reqm.completed_course_prereq completed_course_prereq,
38 	 reqm.completed_competence_prereq completed_competence_prereq,
39 	 reqm.create_enrollment create_enrollment,
40 	 reqm.error_message error_message
41 	 FROM
42 	 ota_mandatory_enr_req_members  reqm,
43 	 ota_events_tl evt_tl
44 	WHERE
45 	evt_tl.event_id = reqm.event_id
46 	AND(reqm.create_enrollment  = 'N' or reqm.error_message IS NOT NULL)
47 	AND evt_tl.language=userenv('LANG');
48 
49 FUNCTION learner_can_enroll_in_class(p_event_id IN NUMBER
50 			                         ,p_learner_id IN NUMBER
51 	                                 )RETURN VARCHAR2;
52 
53 
54 FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
55                                       p_organization_id IN ota_event_associations.organization_id%type,
56                                       p_person_id IN per_people_f.person_id%type)
57                                       RETURN VARCHAR2;
58 
59 FUNCTION learner_is_notSelected(p_person_id IN per_all_people_f.person_id%type
60                                 ,p_assignment_id per_all_assignments_f.assignment_id%type
61                                 ,p_event_id IN ota_events.event_id%type)
62                           RETURN Boolean;
63 
64 PROCEDURE process_mandatory_event_assoc(ERRBUF OUT NOCOPY  VARCHAR2
65                                         ,RETCODE OUT NOCOPY VARCHAR2
66                                         ,p_event_id in NUMBER);
67 
68 
69 PROCEDURE process_mandatory_enr_requests(p_conc_request_id IN NUMBER);
70 
71 PROCEDURE create_enrollments(p_conc_reqId IN NUMBER);
72 
73 PROCEDURE notify_mandatory_request(p_person_id in NUMBER,
74                                    p_conc_program_request_id in NUMBER,
75                                    p_object_type in VARCHAR2,
76                                    p_object_id in NUMBER,
77                                    p_error_learners in NUMBER,
78                                    p_success_learners in NUMBER);
79 
80 
81 PROCEDURE notify_class_owners(p_conc_reqId IN NUMBER);
82 
83 
84 END ota_mandatory_enroll_util;