1 package ota_bulk_enroll_util AUTHID CURRENT_USER as
2 /* $Header: otblkenr.pkh 120.5.12020000.2 2012/06/29 12:02:36 rpahune ship $ */
3
4 CURSOR csr_get_person_name(p_person_id NUMBER) IS
5 SELECT full_name
6 FROM per_all_people_f
7 WHERE trunc(sysdate) between effective_start_date and effective_end_date
8 AND person_id = p_person_id;
9
10 CURSOR csr_get_request_info(p_enr_request_id NUMBER) IS
11 SELECT ber.object_type
12 , ber.object_id
13 , ber.conc_program_request_id
14 , ber.requestor_id
15 , ber.business_group_id
16 ,ber.parent_object_id
17 FROM ota_bulk_enr_requests ber
18 WHERE ber.bulk_enr_request_id = p_enr_request_id;
19
20 CURSOR csr_get_all_requests(p_enr_request_id NUMBER,p_enr_request_id_end NUMBER) IS
21 SELECT ber.object_type
22 , ber.object_id
23 , ber.conc_program_request_id
24 , ber.requestor_id
25 , ber.business_group_id
26 ,ber.bulk_enr_request_id
27 ,ber.parent_object_id
28 FROM ota_bulk_enr_requests ber
29 WHERE ber.bulk_enr_request_id between p_enr_request_id and p_enr_request_id_end;
30 CURSOR csr_get_request_members(p_enr_request_id NUMBER) IS
31 SELECT person_id, assignment_id, enrollment_status, error_message, booking_id --added bookingid for 12426494
32 FROM ota_bulk_enr_req_members
33 WHERE bulk_enr_request_id = p_enr_request_id;
34
35 Function get_enrollment_status( p_object_type IN VARCHAR2
36 ,p_object_id IN NUMBER
37 ,p_learner_id IN NUMBER
38 ,p_return_mode IN NUMBER
39 ) RETURN VARCHAR2;
40
41 PROCEDURE assign_enrollment_status(p_class_id IN NUMBER
42 , p_booking_status_id OUT NOCOPY NUMBER
43 , p_status_message OUT NOCOPY VARCHAR2);
44
45 FUNCTION get_object_name(p_object_type IN VARCHAR2, p_object_id IN NUMBER)
46 RETURN VARCHAR2;
47
48 PROCEDURE get_enr_request_info(
49 p_bulk_enr_request_id IN NUMBER
50 ,p_selected_learners OUT NOCOPY NUMBER
51 ,p_unfulfil_course_prereqs OUT NOCOPY NUMBER
52 ,p_unfulfil_comp_prereqs OUT NOCOPY NUMBER);
53
54 PROCEDURE get_enr_req_completion_status(
55 p_bulk_enr_request_id IN NUMBER
56 ,p_selected_learners OUT NOCOPY NUMBER
57 ,p_success_enrollments OUT NOCOPY NUMBER
58 ,p_errored_enrollments OUT NOCOPY NUMBER
59 ,p_unfulfil_course_prereqs OUT NOCOPY NUMBER
60 ,p_unfulfil_comp_prereqs OUT NOCOPY NUMBER);
61
62 FUNCTION get_enr_status_from_request(
63 p_object_type IN VARCHAR2
64 ,p_enrollment_status IN VARCHAR2) RETURN VARCHAR2;
65
66 PROCEDURE delete_bulk_enr_request
67 (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE
68 ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
69 ,actid IN NUMBER
70 ,funcmode IN VARCHAR2
71 ,resultout OUT nocopy VARCHAR2 );
72
73 PROCEDURE Create_Enrollment_And_Finance( p_event_id IN VARCHAR2
74 ,p_extra_information IN VARCHAR2 DEFAULT NULL
75 ,p_cost_centers IN VARCHAR2 DEFAULT NULL
76 ,p_assignment_id IN PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE
77 ,p_business_group_id_from IN PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE
78 ,p_organization_id IN PER_ALL_ASSIGNMENTS_F.organization_id%TYPE
79 ,p_person_id IN PER_ALL_PEOPLE_F.person_id%type
80 ,p_delegate_contact_id IN NUMBER
81 ,p_booking_id OUT NOCOPY OTA_DELEGATE_BOOKINGS.Booking_id%type
82 ,p_message_name OUT NOCOPY VARCHAR2
83 ,p_tdb_information_category IN VARCHAR2 DEFAULT NULL
84 ,p_tdb_information1 IN VARCHAR2 DEFAULT NULL
85 ,p_tdb_information2 IN VARCHAR2 DEFAULT NULL
86 ,p_tdb_information3 IN VARCHAR2 DEFAULT NULL
87 ,p_tdb_information4 IN VARCHAR2 DEFAULT NULL
88 ,p_tdb_information5 IN VARCHAR2 DEFAULT NULL
89 ,p_tdb_information6 IN VARCHAR2 DEFAULT NULL
90 ,p_tdb_information7 IN VARCHAR2 DEFAULT NULL
91 ,p_tdb_information8 IN VARCHAR2 DEFAULT NULL
92 ,p_tdb_information9 IN VARCHAR2 DEFAULT NULL
93 ,p_tdb_information10 IN VARCHAR2 DEFAULT NULL
94 ,p_tdb_information11 IN VARCHAR2 DEFAULT NULL
95 ,p_tdb_information12 IN VARCHAR2 DEFAULT NULL
96 ,p_tdb_information13 IN VARCHAR2 DEFAULT NULL
97 ,p_tdb_information14 IN VARCHAR2 DEFAULT NULL
98 ,p_tdb_information15 IN VARCHAR2 DEFAULT NULL
99 ,p_tdb_information16 IN VARCHAR2 DEFAULT NULL
100 ,p_tdb_information17 IN VARCHAR2 DEFAULT NULL
101 ,p_tdb_information18 IN VARCHAR2 DEFAULT NULL
102 ,p_tdb_information19 IN VARCHAR2 DEFAULT NULL
103 ,p_tdb_information20 IN VARCHAR2 DEFAULT NULL
104 ,p_booking_justification_id IN VARCHAR2 DEFAULT NULL
105 ,p_override_prerequisites IN VARCHAR2 DEFAULT 'N'
106 ,p_override_learner_access IN VARCHAR2 DEFAULT 'N'
107 ,p_is_mandatory_enrollment IN VARCHAR2 default 'N');
108
109 Procedure process_bulk_enrollments
110 (ERRBUF OUT NOCOPY VARCHAR2,
111 RETCODE OUT NOCOPY VARCHAR2
112 ,p_enr_request_id IN NUMBER
113 ,p_enr_request_id_end IN NUMBER DEFAULT NULL);
114
115
116 PROCEDURE submit_bulk_enrollments(
117 p_enr_request_id IN NUMBER
118 ,p_enr_request_id_end IN NUMBER DEFAULT NULL
119 ,p_conc_request_id OUT NOCOPY NUMBER
120 ,p_object_type OUT NOCOPY VARCHAR2
121 ,p_object_name OUT NOCOPY VARCHAR2);
122
123 PROCEDURE submit_bulk_enrollment_updates(
124 p_enr_request_id IN NUMBER
125 ,p_book_stat_type_id IN NUMBER
126 ,p_status_change_comments IN varchar2
127 ,p_successful_attendance IN varchar2
128 ,p_failure_reason IN varchar2
129 ,p_attendance_result IN varchar2
130 ,p_event_id IN number
131 ,p_enrollment_justification_id number
132 ,p_enrollment_priority varchar2
133 ,p_auth_person_id number
134 ,p_conc_request_id OUT NOCOPY NUMBER);
135
136
137 Procedure process_bulk_enr_upd
138 (ERRBUF OUT NOCOPY VARCHAR2,
139 RETCODE OUT NOCOPY VARCHAR2
140 ,p_enr_request_id IN NUMBER
141 ,p_book_stat_type_id IN NUMBER
142 ,p_status_change_comments IN VARCHAR2 DEFAULT NULL
143 ,p_successful_attendance IN VARCHAR2 DEFAULT NULL
144 ,p_failure_reason IN VARCHAR2 DEFAULT NULL
145 ,p_attendance_result IN VARCHAR2 DEFAULT NULL
146 ,p_event_id IN NUMBER DEFAULT NULL
147 ,p_enrollment_justification_id IN NUMBER DEFAULT NULL
148 ,p_enrollment_priority IN VARCHAR2 DEFAULT NULL
149 ,p_auth_person_id IN NUMBER DEFAULT NULL
150 ,p_debug IN VARCHAR2 DEFAULT 'N');
151
152 end ota_bulk_enroll_util;
[Home] [Help]