[Home] [Help]
PACKAGE BODY: APPS.OTA_ILEARNING2
Source
1 package body OTA_ILEARNING2 as
2 /* $Header: otilnprf.pkb 115.4 2002/11/26 12:47:33 arkashya noship $ */
3 /*
4 ===========================================================================
5 | Copyright (c) 1996 Oracle Corporation |
6 | All rights reserved. |
7 ===========================================================================
8 Name
9 General Oracle iLearning utilities
10 Purpose
11 To provide procedures/functions for iLearning integration
12 History
13 15-Jan-02 115.0 HDSHAH Created
14 21-Feb-02 115.1 HDSHAH 2236928 Modified log messages.
15 Used OTA_ILEARNING_DEFAULT_ATTENDED
16 profile instead of reading from cursor.
17 27-Feb-02 115.2 DHMULIA 2242840 Modified upd_history to call
18 ota_tfh_api_upd.upd instead of Finance line
19 api.
20 28-Feb-02 115.3 HDSHAH 2246791 Need to lock record before calling update_enrollment procedure.
21 26-nov-02 115.4 ARKASHYA 2684733 Included NOCOPY directive in the OUT and IN OUT parameters.
22 */
23 --------------------------------------------------------------------------------
24 g_package varchar2(33) := ' ota_ilearning2.'; -- Global package name
25 --
26 --
27 -- ----------------------------------------------------------------------------
28 -- |---------------< create_or_update_activity_version >----------------------|
29 -- ----------------------------------------------------------------------------
30 --
31 -- PUBLIC
32 --
33 -- Description : Update history based on input data.
34
35 --
36 Procedure upd_history
37 (
38 p_person_id in number
39 ,p_rco_id in number
40 ,p_isroot in varchar2
41 ,p_status in varchar2
42 ,p_score in number
43 ,p_time in varchar2
44 ,p_complete in number
45 ,p_total in number
46 ,p_business_group_id in number
47 ,p_history_status out nocopy varchar2
48 ,p_message out nocopy varchar2
49 ) is
50
51 l_proc varchar2(72) := g_package||'upd_history.';
52 l_booking_id OTA_DELEGATE_BOOKINGS.BOOKING_ID%TYPE;
53 l_ovn OTA_DELEGATE_BOOKINGS.OBJECT_VERSION_NUMBER%TYPE;
54 l_finance_line_id OTA_FINANCE_LINES.FINANCE_LINE_ID%TYPE;
55 l_tfl_finance_line_id OTA_FINANCE_LINES.FINANCE_LINE_ID%TYPE;
56 l_sequence_number OTA_FINANCE_LINES.SEQUENCE_NUMBER%TYPE;
57 l_fl_ovn OTA_FINANCE_LINES.OBJECT_VERSION_NUMBER%TYPE;
58 l_tfl_ovn OTA_FINANCE_LINES.OBJECT_VERSION_NUMBER%TYPE;
59 l_date_raised OTA_FINANCE_LINES.DATE_RAISED%TYPE;
60 l_booking_status_type_id OTA_BOOKING_STATUS_TYPES.BOOKING_STATUS_TYPE_ID%TYPE;
61
62 l_cur_booking_status_type_id OTA_BOOKING_STATUS_TYPES.BOOKING_STATUS_TYPE_ID%TYPE;
63 l_cur_content_player_status OTA_DELEGATE_BOOKINGS.CONTENT_PLAYER_STATUS%TYPE;
64 l_cur_tdb_ovn OTA_DELEGATE_BOOKINGS.OBJECT_VERSION_NUMBER%TYPE;
65 l_cur_event_id OTA_DELEGATE_BOOKINGS.EVENT_ID%TYPE;
66 l_status_type OTA_BOOKING_STATUS_TYPES.TYPE%type;
67 l_date_booking_placed OTA_DELEGATE_BOOKINGS.DATE_BOOKING_PLACED%TYPE;
68 l_auto_transfer varchar2(4) := FND_PROFILE.VALUE('OTA_SSHR_AUTO_GL_TRANSFER');
69 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
70 l_finance_header_ovn OTA_FINANCE_HEADERS.OBJECT_VERSION_NUMBER%TYPE;
71 l_finance_header_id OTA_FINANCE_HEADERS.FINANCE_HEADER_ID%TYPE;
72
73 cursor cur_get_booking_id is
74 select
75 max(booking_id) booking_id
76 from
77 ota_delegate_bookings TDB,
78 ota_events EVT,
79 ota_activity_versions OAV
80 where
81 OAV.rco_id = p_rco_id and
82 OAV.activity_version_id = EVT.activity_version_id and
83 EVT.event_id = TDB.event_id and
84 TDB.delegate_person_id = p_person_id and
85 TDB.business_group_id = p_business_group_id;
86
87
88 cursor cur_get_tdb_details is
89 select
90 booking_status_type_id,
91 content_player_status,
92 object_version_number,
93 event_id,
94 date_booking_placed
95 from
96 ota_delegate_bookings
97 where
98 booking_id = l_booking_id;
99
100
101
102 cursor cur_get_finance_line_id is
103 select
104 finance_line_id,
105 object_version_number,
106 sequence_number,
107 date_raised,
108 finance_header_id
109 from
110 ota_finance_lines
111 where
112 booking_id = l_booking_id and
113 cancelled_flag = 'N' and
114 transfer_status = 'NT';
115
116
117 cursor cur_get_status_type_id is
118 select
119 booking_status_type_id
120 from
121 ota_booking_status_types
122 where
123 type = 'A' and
124 default_flag = 'Y' and
125 active_flag = 'Y' and
126 business_group_id = p_business_group_id;
127
128 cursor csr_booking_status(p_status_type_id number) is
129 Select type
130 from ota_booking_status_types
131 where booking_status_type_id = p_status_type_id;
132
133 /* for Bug 2242840 */
134 cursor csr_finance_header(p_finance_header_id IN number) IS
135 select object_version_number from
136 ota_finance_headers
137 where finance_header_id = p_finance_header_id;
138
139
140
141
142 begin
143 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
144 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_person_id:' || p_person_id);
145 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_rco_id:' || p_rco_id);
146 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_business_group_id:' || p_business_group_id);
147
148
149
150 FOR cur_booking_id IN cur_get_booking_id
151 LOOP
152
153 l_booking_id := cur_booking_id.booking_id;
154
155 open cur_get_tdb_details;
156 fetch cur_get_tdb_details into l_cur_booking_status_type_id,
157 l_cur_content_player_status,
158 l_cur_tdb_ovn,
159 l_cur_event_id,
160 l_date_booking_placed;
161 close cur_get_tdb_details;
162
163 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_booking_id:' || l_booking_id);
164
165 open cur_get_finance_line_id;
166 fetch cur_get_finance_line_id into l_finance_line_id,
167 l_fl_ovn,
168 l_sequence_number,
169 l_date_raised,
170 l_finance_header_id;
171
172 if cur_get_finance_line_id%NOTFOUND then
173 close cur_get_finance_line_id;
174 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Finance Line ID not found for rco id ' || p_rco_id ||
175 -- ' and person id ' || p_person_id );
176 else
177 close cur_get_finance_line_id;
178
179 if l_cur_content_player_status is NULL then
180
181
182 BEGIN
183
184 -- clear message before calling API
185 hr_utility.clear_message;
186 if l_auto_transfer = 'Y' then /* Start for Bug 2242840 */
187 for finance_rec in csr_finance_header (l_finance_header_id)
188 LOOP
189 exit when csr_finance_header%notfound ;
190 l_finance_header_ovn := finance_rec.object_version_number;
191 END LOOP;
192
193 ota_tfh_api_shd.lck(l_finance_header_id, l_finance_header_ovn );
194
195 ota_tfh_api_upd.upd(p_finance_header_id => l_finance_header_id,
196 p_authorizer_person_id => l_user_id,
197 p_object_version_number => l_finance_header_ovn ,
198 p_transfer_status => 'AT');
199
200 /* End for Bug 2242840 */
201
202
203 /* ota_tfl_api_upd.upd ---- ottfl01t.pkb
204 (p_finance_line_id => l_finance_line_id -- (Input)
205 ,p_date_raised => l_date_raised -- (In Out)
206 ,p_object_version_number => l_fl_ovn -- (Output)
207 ,p_sequence_number => l_sequence_number -- (In Out)
208 ,p_transfer_status => 'AT' -- (Input) Awaiting Transfer
209 ,p_validate => false -- (Input)
210 ,p_transaction_type => 'UPDATE' -- (Input)
211 ); */
212
213 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated finance line for rco_id - '|| p_rco_id ||
214 -- ' person id-' || p_person_id || ' and booking ID-' ||
215 -- cur_booking_id.booking_id || ' and finance_line_id-' || l_finance_line_id);
216 p_message := 'Successfully updated finance line ';
217 -- dbms_output.put_line(p_message);
218
219 end if;
220
221 EXCEPTION
222 when others then
223 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the finance line for RCO ID '|| p_rco_id ||
224 ', Person ID ' || p_person_id || ', Booking ID-' ||
225 cur_booking_id.booking_id || ', and Finance Line ID-' || l_finance_line_id
226 || '. Reason:' || hr_utility.get_message);
227 p_message := 'Error in updating finance line ';
228 -- dbms_output.put_line(p_message);
229 END;
230
231
232 else
233 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
234 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'concurrent program will not update finance line for rco_id - '|| p_rco_id ||
235 -- ' person id-' || p_person_id || ' and booking ID-' ||
236 -- cur_booking_id.booking_id || ' and finance_line_id-' || l_finance_line_id ||
237 -- ' because content player status is not null.');
238
239 end if;
240
241 end if;
242
243
244 if p_status in ('C','P') then
245 /*open cur_get_status_type_id;
246 fetch cur_get_status_type_id into l_booking_status_type_id;
247 if cur_get_status_type_id%NOTFOUND then
248 close cur_get_status_type_id;
249 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:Default booking_status_type_id not found for Attended status' ||
250 ' for business group id ' || p_business_group_id);
251 p_message := 'ERROR: Default booking_status_type_id not found ';
252 -- dbms_output.put_line(p_message);
253 p_history_status := 'F';
254 return;
255 end if;
256 close cur_get_status_type_id;
257 */
258
259 FOR status_type in csr_booking_status(l_cur_booking_status_type_id)
260 LOOP
261 if status_type.type <> 'A' then
262 l_booking_status_type_id := FND_PROFILE.VALUE('OTA_ILEARNING_DEFAULT_ATTENDED');
263 if l_booking_status_type_id is null then
264 FND_FILE.PUT_LINE(FND_FILE.LOG,'You must enter a default Attended Enrollment Status for the profile OTA:Default Attended Enrollment Status.');
265 p_message := 'ERROR: Default booking_status_type_id not found ';
266 -- dbms_output.put_line(p_message);
267 p_history_status := 'F';
268
269 end if;
270
271 else
272 l_booking_status_type_id := l_cur_booking_status_type_id;
273
274 end if;
275
276 END LOOP;
277
278 else
279 l_booking_status_type_id := l_cur_booking_status_type_id;
280 end if;
281
282 l_ovn := l_cur_tdb_ovn;
283
284 --Bug#2246791 hdshah lock record before calling update_enrollment procedure.
285 ota_tdb_shd.lck(cur_booking_id.booking_id,l_ovn);
286
287 BEGIN
288 -- clear message before calling API
289 hr_utility.clear_message;
290
291 if l_booking_status_type_id = l_cur_booking_status_type_id then
292 ota_tdb_api_upd2.update_enrollment
293 (p_booking_id => cur_booking_id.booking_id -- (Input)
294 ,p_booking_status_type_id => l_booking_status_type_id -- (Input)
295 ,p_object_version_number => l_ovn -- (In Out)
296 ,p_event_id => l_cur_event_id -- (Input)
297 ,p_content_player_status => p_status -- (Input)
298 ,p_score => p_score -- (Input)
299 ,p_total_training_time => p_time -- (Input)
300 ,p_completed_content => p_complete -- (Input)
301 ,p_total_content => p_total -- (Input)
302 ,p_tfl_object_version_number => l_tfl_ovn -- (In Out)
303 ,p_finance_line_id => l_tfl_finance_line_id -- (In Out)
304 );
305 else
306 ota_tdb_api_upd2.update_enrollment
307 (p_booking_id => cur_booking_id.booking_id -- (Input)
308 ,p_booking_status_type_id => l_booking_status_type_id -- (Input)
309 ,p_object_version_number => l_ovn -- (In Out)
310 ,p_event_id => l_cur_event_id -- (Input)
311 ,p_content_player_status => p_status -- (Input)
312 ,p_score => p_score -- (Input)
313 ,p_total_training_time => p_time -- (Input)
314 ,p_completed_content => p_complete -- (Input)
315 ,p_total_content => p_total -- (Input)
316 ,p_tfl_object_version_number => l_tfl_ovn -- (In Out)
317 ,p_finance_line_id => l_tfl_finance_line_id -- (In Out)
318 ,p_date_status_changed => sysdate
319 ,p_date_booking_placed => l_date_booking_placed);
320
321 end if;
322
323
324 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated history for RCO ID - '|| p_rco_id ||
325 ', Person ID ' || p_person_id || ', and Booking ID ' ||
326 cur_booking_id.booking_id ||'.');
327 p_message := 'Successfully updated history ';
328 -- dbms_output.put_line(p_message);
329 p_history_status := 'S';
330 return;
331
332 EXCEPTION
333 when others then
334 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update history for RCO ID - '|| p_rco_id ||
335 ', Person ID ' || p_person_id || ', and Booking ID ' ||
336 cur_booking_id.booking_id || '. REASON:' || hr_utility.get_message);
337 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in updating history for rco_id - '|| p_rco_id ||
338 -- ' person id-' || p_person_id || ' and booking ID-' ||
339 -- cur_booking_id.booking_id || '. REASON:' || hr_utility.get_message);
340
341 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_booking_id:' || cur_booking_id.booking_id);
342 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_booking_status_type_id:' || l_booking_status_type_id);
343 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_object_version_number:' || l_ovn );
344 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_content_player_status:' || p_status);
345 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_score:' || p_score);
346 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_total_training_time:' || p_time );
347 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_completed_content:' || p_complete );
348 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_total_content:' || p_total );
349 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_tfl_object_version_number:' || l_tfl_ovn );
350 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_finance_line_id:' || l_tfl_finance_line_id );
351
352 p_message := 'Error in updating history ';
353 -- dbms_output.put_line(p_message);
354 p_history_status := 'F';
355 return;
356 END;
357
358 END LOOP;
359
360 exception
361 when others then
362
363 FND_FILE.PUT_LINE(FND_FILE.LOG,'An error occurred while updating RCO ID '|| p_rco_id ||
364 ', Person ID ' || p_person_id || ', and Booking ID ' ||
365 l_booking_id ||'.');
366 p_message := 'upd_history:ERROR:In when others exception for Rco_Id - ' || p_rco_id;
367 -- dbms_output.put_line(p_message);
368 p_history_status := 'F';
369 return;
370
371
372 end upd_history;
373
374
375
376
377 procedure history_import (
378 p_array in OTA_HISTORY_STRUCT_TAB
379 ,p_business_group_id in varchar2
380 ) is
381
382 l_proc varchar2(72) := g_package||'history_import';
383 l_history_status varchar2(1);
384 l_message varchar2(100);
385 l_update varchar2(10);
386 l_history_success number(10) := 0;
387 l_history_fail number(10) := 0;
388
389 begin
390 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
391
392
393 FOR p_array_idx IN p_array.FIRST..p_array.LAST LOOP
394
395 -- Issue Savepoint
396 SAVEPOINT save_history;
397
398 upd_history
399 (
400 p_person_id => to_number(p_array(p_array_idx).history_personid) -- (Input)
401 ,p_rco_id => to_number(p_array(p_array_idx).history_rco_id) -- (Input)
402 ,p_isroot => p_array(p_array_idx).history_isroot -- (Input)
403 ,p_status => p_array(p_array_idx).history_status -- (Input)
404 ,p_score => to_number(p_array(p_array_idx).history_score) -- (Input)
405 ,p_time => p_array(p_array_idx).history_time -- (Input)
406 ,p_complete => to_number(p_array(p_array_idx).history_complete) -- (Input)
407 ,p_total => to_number(p_array(p_array_idx).history_total) -- (Input)
408 ,p_business_group_id => to_number(p_business_group_id) -- (Input)
409 ,p_history_status => l_history_status -- (Output)
410 ,p_message => l_message -- (Output)
411 );
412
413
414 if l_history_status = 'S' then
415 l_history_success := l_history_success +1;
416 -- do commit;
417 commit;
418 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'History Update committed.');
419 else
420 l_history_fail := l_history_fail +1;
421 -- rollback to save_activity
422 ROLLBACK TO save_history;
423 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'History Update rolled back.');
424 end if;
425
426 END LOOP;
427
428 FND_FILE.PUT_LINE(FND_FILE.LOG,' IMPORT RESULTS FOR TRAINING HISTORIES ');
429 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------------------------');
430 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of Histories Processed Successfully:' || l_history_success);
431 FND_FILE.PUT_LINE(FND_FILE.LOG,' Number of Histories Not Processed:' || l_history_fail);
432 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------------------------');
433 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Exiting:' || l_proc);
434
435 end history_import;
436
437 end OTA_ILEARNING2;