[Home] [Help]
PACKAGE BODY: APPS.OTA_ILEARNING
Source
1 package body OTA_ILEARNING as
2 /* $Header: otilncnt.pkb 120.2 2008/01/17 11:50:07 smahanka 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 HDSHAH Created
14 23-Jan-02 115.2 HDSHAH 2193880 Cursor csr_get_date_time updated.
15 25-Jan-02 115.3 HDSHAH 2200017 Timezone included in event creation and update procedure calls.
16 28-Jan-02 115.4 HDSHAH 2201416 book_independent_flag parameter missing for create event procedure call.
17 29-Jan-02 115.5 HDSHAH 2201416 trunc() included for course_start_date, course_end_date,
18 enrollment_start_date,enrollment_end_date in included for
19 ota_evt_ins.ins and ota_evt_upd.upd procedure calls
20 29-Jan-02 115.6 DHMULIA 2201416 Added Trunc() to sysdate before calling ota_tav_ins.
21 30-Jan-02 115.7 HDSHAH 2201416 Added Trunc() to l_course_date parameter for ota_tav_upd procedure calls.
22 15-Feb-02 115.8 HDSHAH 2209467 p_start_date and p_end_date parameter type changed to varchar2 in
23 crt_or_chk_xml_prcs_tbl and upd_xml_prcs_tbl procedure.
24 21-Feb-02 115.9 HDSHAH 2236928 Log messages modified.
25 21-Feb-02 115.10 HDSHAH 2236928 Log messages modified.
26 16-APR-02 115.11 HDSHAH 2324698 Modified cur_get_date_time cursor in crt_or_upd_event procedure.
27 26-NOV-02 115.12 ARKASHYA 2684733 Included the NOCOPY directive in OUT and IN OUT parameters for procedures.
28 25-Mar-03 115.13 Pbhasin MLS changes added.
29 30-May-03 115.14 Arkashya 2984480 MLS Changes (Additional) Added calls to insert and update functions on _TL
30 tables for activity version and events.
31 24-Dec-03 115.15 arkashya Modified for eBS changed the call to ota_aci_api.ins to ota_aci_ins.ins
32 10-Jan-04 115.16 arkashya Modified for eBS changed the call to ota_tcu_api.ins to ota_ctu_ins.ins
33 Also added the call to ota_ctt_ins.ins_tl and defaulted synchronous_flag
34 and online_flag.
35 28-jun-04 115.17 ssur 3725560 Restricted import of newly created RCO and Offerings.
36 07-Jan-08 120.1 aabalakr 6683076, modified to include the new enrollment status, 'E'(Pending Evaluation)
37 */
38 --------------------------------------------------------------------------------
39 g_package varchar2(33) := ' ota_ilearning.'; -- Global package name
40 --
41
42 --
43 -- ----------------------------------------------------------------------------
44 -- |------------------------< create_or_update_activity_version >----------------------------|
45 -- ----------------------------------------------------------------------------
46 --
47 -- PUBLIC
48 --
49 -- Description : Create or update activity version based on input data.
50 --
51 Procedure crt_or_upd_activity
52 (
53 p_update in varchar2
54 ,p_rco_id in number
55 ,p_language_code in varchar2
56 ,p_activity_version_name in varchar2
57 ,p_description in varchar2
58 ,p_objectives in varchar2
59 ,p_audience in varchar2
60 ,p_business_group_id in number
61 ,p_activity_definition_name in varchar2
62 ,p_activity_version_id out nocopy number
63 ,p_language_id out nocopy number
64 ,p_status out nocopy varchar2
65 ,p_message out nocopy varchar2
66 ) is
67
68 no_language_id_found EXCEPTION;
69 l_proc varchar2(72) := g_package||'crt_or_upd_activity';
70 l_activity_version_id OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID%TYPE;
71 l_object_version_number OTA_ACTIVITY_VERSIONS.OBJECT_VERSION_NUMBER%TYPE;
72 l_activity_version_name OTA_ACTIVITY_VERSIONS_TL.VERSION_NAME%TYPE; --MLS change _TL added
73 l_activity_id OTA_ACTIVITY_DEFINITIONS.ACTIVITY_ID%TYPE;
74 l2_language_id FND_LANGUAGES.LANGUAGE_ID%TYPE;
75 l_sysdate date;
76
77 cursor cur_get_activity_version_id is
78 select
79 activity_version_id,
80 language_id,
81 object_version_number
82 from
83 ota_activity_versions OAV
84 where
85 OAV.rco_id = p_rco_id and
86 OAV.developer_organization_id = p_business_group_id;
87
88
89 cursor cur_get_activity_version_name is
90 select
91 version_name
92 from
93 ota_activity_versions_vl OAV -- MLS change _vl added
94 where
95 OAV.version_name = p_activity_version_name and
96 OAV.developer_organization_id = p_business_group_id;
97
98 /*
99 cursor cur_get_activity_id is
100 select
101 OAD.activity_id
102 from
103 ota_activity_definitions OAD
104 where
105 OAD.name = p_activity_definition_name and
106 OAD.business_group_id = p_business_group_id;
107 */
108
109 cursor cur_get_event_id is
110 select
111 event_id,
112 object_version_number
113 from
114 ota_events
115 where
116 activity_version_id = l_activity_version_id and
117 business_group_id = p_business_group_id;
118
119
120 cursor cur_get_language_id is
121 select
122 language_id
123 from
124 fnd_languages
125 where
126 language_code = p_language_code;
127
128
129 begin
130 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
131
132
133 if p_update is null then -- if not only update
134
135 open cur_get_activity_version_id;
136 fetch cur_get_activity_version_id into l_activity_version_id,p_language_id,l_object_version_number;
137
138 if cur_get_activity_version_id%NOTFOUND then --if activity_version_id
139 close cur_get_activity_version_id;
140
141 l_activity_id := FND_PROFILE.VALUE('OTA_ILEARNING_DEFAULT_ACTIVITY');
142 /*
143 open cur_get_activity_id;
144 fetch cur_get_activity_id into l_activity_id;
145 if cur_get_activity_id%NOTFOUND then --if activity_id
146 close cur_get_activity_id;
147 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Activity_Id found corresponding to - ' ||
148 p_activity_definition_name);
149 p_message := 'No Activity_Id found corresponding to ';
150 -- dbms_output.put_line(p_message);
151 p_activity_version_id := NULL;
152 p_status := 'F';
153 return;
154 end if; --if activity_id
155 close cur_get_activity_id;
156 */
157
158 open cur_get_language_id;
159 fetch cur_get_language_id into p_language_id;
160 if cur_get_language_id%NOTFOUND then --if language_id
161 close cur_get_language_id;
162 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application did not create an Activity version for the RCO ' ||
163 p_activity_version_name || ' because the Language code '|| p_language_code || ' does not exist. Please return to OiL and correct the Language Name in the RCO.');
164 p_message := 'No language found corresponding to ';
165 -- dbms_output.put_line(p_message);
166 p_activity_version_id := NULL;
167 -- p_status := 'F';
168 p_status := 'W';
169 raise no_language_id_found;
170 -- return;
171 end if; --if language_id
172 close cur_get_language_id;
173
174 open cur_get_activity_version_name;
175 fetch cur_get_activity_version_name into l_activity_version_name;
176
177 if cur_get_activity_version_name%FOUND then --if activity_version_id
178 close cur_get_activity_version_name;
179 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Activity Version Name ' || p_activity_version_name|| ' already exists. You must rename all but one RCO of that name.');
180 p_message := 'Activity_version_name already exist ';
181 -- dbms_output.put_line(p_message);
182 p_activity_version_id := NULL;
183 -- p_status := 'F';
184 p_status := 'E';
185 return;
186
187 end if;
188 close cur_get_activity_version_name;
189
190 /* for Bug 2201416 Added Trunc when selecting sysdate */
191 select trunc(sysdate) into l_sysdate from dual;
192
193 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert Activity with rco_id - '|| p_rco_id);
194 -- Create activity_version
195
196 BEGIN
197 -- clear message before calling API
198 hr_utility.clear_message;
199 /* change for eBS
200 ota_tav_ins.Ins
201 (
202 P_activity_version_id => l_activity_version_id -- (Output)
203 ,P_activity_id => l_activity_id -- (Input)
204 ,P_developer_organization_id => p_business_group_id -- (Input)
205 ,P_description => p_description -- (Input)
206 ,P_language_id => p_language_id -- (Input)
207 ,P_start_date => l_sysdate -- (Input)
208 ,P_version_name => p_activity_version_name -- (Input)
209 ,P_intended_audience => p_audience -- (Input)
210 ,P_objectives => p_objectives -- (Input)
211 ,P_object_version_number => l_object_version_number -- (Output)
212 ,P_RCO_ID => p_rco_id -- (Input)
213 ,P_VALIDATE => false); -- (Input)
214
215 --Bug 2984480 - arkashya MLS Changes calls to _TL row handler for Insert
216 ota_avt_ins.ins_tl
217 (
218 P_effective_date => l_sysdate
219 ,P_language_code => USERENV('LANG')
220 ,P_activity_version_id => l_activity_version_id
221 ,P_version_name => p_activity_version_name
222 ,P_description => p_description
223 ,P_intended_audience => p_audience
224 ,P_objectives => p_objectives
225 );
226 */
227 FND_FILE.PUT_LINE(FND_FILE.LOG,'The RCO ' || p_activity_version_name||' cannot be transferred into iLearning 11i. You must launch iLearning 11i and create the new course there. ');
228 -- dbms_output.put_line(p_message);
229 -- p_activity_version_id := l_activity_version_id;
230 p_status := 'S';
231 return;
232 /* change for eBS
233 EXCEPTION
234 when others then
235 fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not create an Activity Version for RCO '||
236 p_activity_version_name || '. Reason:' || hr_utility.get_message);
237 p_message := 'Error in creating Activity_version found corresponding to ';
238 -- dbms_output.put_line(p_message);
239 p_activity_version_id := NULL;
240 -- p_status := 'F';
241 p_status := 'W';
242 return;
243 */
244 END;
245
246
247
248 else --if activity_version_id
249 close cur_get_activity_version_id;
250
251 open cur_get_language_id;
252 fetch cur_get_language_id into l2_language_id;
253 if cur_get_language_id%NOTFOUND then --if language_id
254 close cur_get_language_id;
255 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application did not create Activity version for RCO ' ||
256 p_activity_version_name || ' because the Language code '|| p_language_code || ' does not exist. Please return to OiL and correct the Language Name in the RCO.');
257 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'No language_id found corresponding to - ' ||
258 -- p_language_code);
259 p_message := 'No language found corresponding to ';
260 -- dbms_output.put_line(p_message);
261 p_activity_version_id := NULL;
262 -- p_status := 'F';
263 p_status := 'W';
264 return;
265 end if; --if language_id
266 close cur_get_language_id;
267
268 --- update activity_version
269 BEGIN
270 -- clear message before calling API
271 hr_utility.clear_message;
272
273 ota_tav_upd.Upd
274 (
275 P_activity_version_id => l_activity_version_id -- (Input)
276 ,P_version_name => p_activity_version_name -- (Input)
277 ,P_description => p_description -- (Input)
278 ,P_intended_audience => p_audience -- (Input)
279 ,P_language_id => p_language_id -- (Input)
280 ,P_objectives => p_objectives -- (Input)
281 ,P_object_version_number => l_object_version_number -- (Input Output)
282 -- ,p_rco_id => p_rco_id -- (Input)
283 ,P_validate => false); -- (Input)
284
285 --Bug 2984480 - arkashya MLS Changes calls to _TL row handler for insert
286 select trunc(sysdate) into l_sysdate from dual;
287 ota_avt_upd.upd_tl
288 (
289 P_effective_date => l_sysdate
290 ,P_language_code => USERENV('LANG')
291 ,P_activity_version_id => l_activity_version_id
292 ,P_version_name => p_activity_version_name
293 ,P_description => p_description
294 ,P_intended_audience => p_audience
295 ,P_objectives => p_objectives
296 );
297 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Activity Version for RCO ' ||
298 p_activity_version_name||'.');
299 p_message := 'updated successfully ';
300 -- dbms_output.put_line(p_message);
301 p_activity_version_id := l_activity_version_id;
302
303 EXCEPTION
304 when others then
305 fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the Activity Version for RCO '||
306 p_activity_version_name || '. Reason:' || hr_utility.get_message);
307 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:In updating Activity version for rco_id - ' ||
308 -- p_rco_id || '. REASON:' || hr_utility.get_message);
309 p_message := 'updated successfully ';
310 -- dbms_output.put_line(p_message);
311 -- p_status := 'F';
312 p_status := 'W';
313 return;
314 END;
315
316
317 if p_language_id <> l2_language_id then -- if language changed for activity
318 --update all events with new activity_language;
319
320
321 for cur_evt in cur_get_event_id
322 LOOP
323 BEGIN
324 -- clear message before calling API
325 hr_utility.clear_message;
326
327 OTA_EVT_UPD.UPD
328 (
329 P_EVENT_ID => cur_evt.event_id -- (Input)
330 ,P_BUSINESS_GROUP_ID => p_business_group_id -- (Input)
331 ,P_LANGUAGE_ID => l2_language_id -- (Input)
332 ,P_OBJECT_VERSION_NUMBER => cur_evt.object_version_number -- (Output)
333 ,P_VALIDATE => false -- (Input)
334 );
335
336
337 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Language ID for Event ID '|| cur_evt.event_id ||
338 '.');
339 p_message := 'Event updated successfully.Event id is -'||cur_evt.event_id||
340 ' for Language id - '||l2_language_id;
341 -- dbms_output.put_line(p_message);
342 EXCEPTION
343 when others then
344 fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the language ID for Event ID '||
345 cur_evt.event_id || '. Reason:' || hr_utility.get_message);
346 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:In updating Event.Event id is - '|| cur_evt.event_id ||
347 -- ' for Language id - ' || l2_language_id || '. REASON:' || hr_utility.get_message);
348 p_message := 'ERROR:In updating Event.Event id is -'||cur_evt.event_id||
349 ' for Language id - '||l2_language_id;
350 -- dbms_output.put_line(p_message);
351 -- p_status := 'F';
352 p_status := 'W';
353 return;
354 END;
355
356
357 END LOOP;
358
359 p_language_id := l2_language_id;
360 end if; -- if language changed for activity
361
362 p_status := 'S';
363 return;
364
365
366 end if; ---if activity_version_id
367
368 else -- if not only update
369
370 open cur_get_activity_version_id;
371 fetch cur_get_activity_version_id into l_activity_version_id,p_language_id,l_object_version_number;
372
373 if cur_get_activity_version_id%NOTFOUND then --if activity_version_id
374 close cur_get_activity_version_id;
375 FND_FILE.PUT_LINE(FND_FILE.LOG,'The RCO ' || p_activity_version_name || ' update failed because no activity version exists. Please return to OiL and update any Offering of this RCO.');
376 p_message := 'ERROR:no activity_version_id found for update corresponding to Rco_Id - '|| p_rco_id;
377 -- dbms_output.put_line(p_message);
378 p_activity_version_id := NULL;
379 -- p_status := 'F';
380 p_status := 'W';
381 return;
382 end if; --if activity_id
383 close cur_get_activity_version_id;
384
385 open cur_get_language_id;
386 fetch cur_get_language_id into l2_language_id;
387 if cur_get_language_id%NOTFOUND then --if language_id
388 close cur_get_language_id;
389 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application did not update the Activity version for the RCO '||
390 p_activity_version_name || ' because the Language code '|| p_language_code || ' does not exist. Please return to OiL and correct the Language Name in the RCO.');
391 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:No language_id found corresponding to - ' ||
392 -- p_language_code);
393 p_message := 'ERROR:No language found corresponding to - '|| p_language_code;
394 -- dbms_output.put_line(p_message);
395 p_activity_version_id := NULL;
396 -- p_status := 'F';
397 p_status := 'W';
398 return;
399 end if; --if language_id
400 close cur_get_language_id;
401
402 --- update activity_version
403 BEGIN
404 -- clear message before calling API
405 hr_utility.clear_message;
406
407 ota_tav_upd.Upd
408 (
409 P_activity_version_id => l_activity_version_id -- (Input)
410 ,P_version_name => p_activity_version_name -- (Input)
411 ,P_description => p_description -- (Input)
412 ,P_intended_audience => p_audience -- (Input)
413 ,P_language_id => l2_language_id -- (Input)
414 ,P_objectives => p_objectives -- (Input)
415 ,P_object_version_number => l_object_version_number -- (Input Output)
416 -- ,p_rco_id => p_rco_id -- (Input)
417 ,P_validate => false); -- (Input)
418
419 --Bug 2984480 - arkashya MLS Changes calls to _TL row handler for Update
420 select trunc(sysdate) into l_sysdate from dual;
421 ota_avt_upd.upd_tl
422 (
423 P_effective_date => l_sysdate
424 ,P_language_code => USERENV('LANG')
425 ,P_activity_version_id => l_activity_version_id
426 ,P_version_name => p_activity_version_name
427 ,P_description => p_description
428 ,P_intended_audience => p_audience
429 ,P_objectives => p_objectives
430 );
431
432 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Activity Version for RCO ' ||
433 p_activity_version_name || '.');
434 p_message := 'updated successfully ';
435 -- dbms_output.put_line(p_message);
436 p_activity_version_id := l_activity_version_id;
437
438 EXCEPTION
439 when others then
440 fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the Activity Version for RCO '||
441 p_activity_version_name || '. Reason:' || hr_utility.get_message);
442 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:In updating Activity version for rco_id - ' ||
443 -- p_rco_id || '.REASON:' || hr_utility.get_message);
444 p_message := 'ERROR:In updating Activity version for rco_id - ' || p_rco_id;
445 -- dbms_output.put_line(p_message);
446 -- p_status := 'F';
447 p_status := 'W';
448 return;
449 END;
450
451
452 if p_language_id <> l2_language_id then -- if language changed for activity
453 --update all events with new activity_language;
454
455 for cur_evt in cur_get_event_id
456 LOOP
457 BEGIN
458 -- clear message before calling API
459 hr_utility.clear_message;
460
461 OTA_EVT_UPD.UPD
462 (
463 P_EVENT_ID => cur_evt.event_id -- (Input)
464 ,P_BUSINESS_GROUP_ID => p_business_group_id -- (Input)
465 ,P_LANGUAGE_ID => l2_language_id -- (Input)
466 ,P_OBJECT_VERSION_NUMBER => cur_evt.object_version_number -- (Output)
467 ,P_VALIDATE => false -- (Input)
468 );
469
470 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Language ID for Event ID '|| cur_evt.event_id || '.');
471 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated .Event id is - '|| cur_evt.event_id ||
472 -- ' for Language id - ' || l2_language_id);
473 p_message := 'Event updated successfully.Event id is -'||cur_evt.event_id||
474 ' for Language id - '||l2_language_id;
475 -- dbms_output.put_line(p_message);
476 EXCEPTION
477 when others then
478 fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the language ID for Event ID '||
479 cur_evt.event_id || '. Reason:' || hr_utility.get_message);
480 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:In updating Event.Event id is - '|| cur_evt.event_id ||
481 -- ' for Language id - ' || l2_language_id || '. REASON:' || hr_utility.get_message);
482 p_message := 'ERROR:In updating Event.Event id is -'||cur_evt.event_id||
483 ' for Language id - '||l2_language_id;
484 -- dbms_output.put_line(p_message);
485 -- p_status := 'F';
486 p_status := 'W';
487 return;
488 END;
489
490
491 END LOOP;
492
493 p_language_id := l2_language_id;
494 end if; -- if language changed for activity
495
496 p_status := 'S';
497 return;
498
499
500
501 end if; -- if not only update
502
503 exception
504 when no_language_id_found then
505 null;
506 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: No Language Id Found ');
507
508 when others then
509
510 FND_FILE.PUT_LINE(FND_FILE.LOG,'An error occurred while processing RCO '||
511 p_activity_version_name ||'. SQLERRM:'|| SQLERRM);
512 p_message := 'ERROR:In when others exception for Rco_Id - ' || p_rco_id;
513 -- dbms_output.put_line(p_message);
514 p_activity_version_id := NULL;
515 -- p_status := 'F';
516 p_status := 'W';
517 return;
518
519
520 end crt_or_upd_activity;
521
522
523
524 Procedure crt_or_upd_event
525 (
526 p_transaction in number
527 ,p_offering_title in varchar2
528 ,p_offering_id in number
529 ,p_offering_start_date in date
530 ,p_offering_end_date in date
531 ,p_offering_timezone in varchar2
532 ,p_enrollment_start_date in date
533 ,p_enrollment_end_date in date
534 ,p_offering_max_attendees in number
535 ,p_offering_type in varchar2
536 ,p_offering_ispublished in varchar2
537 ,p_language_id in number
538 ,p_activity_version_id in number
539 ,p_business_group_id in number
540 ,p_status out nocopy varchar2
541 ,p_message out nocopy varchar2
542 ) is
543
544 l_proc varchar2(72) := g_package||'crt_or_upd_event';
545 l_activity_start_date OTA_ACTIVITY_VERSIONS.START_DATE%TYPE;
546 l_activity_ovn OTA_ACTIVITY_VERSIONS.OBJECT_VERSION_NUMBER%TYPE;
547 l_event_type OTA_EVENTS.EVENT_TYPE%TYPE;
548 l2_event_type OTA_EVENTS.EVENT_TYPE%TYPE;
549 l_event_status OTA_EVENTS.EVENT_STATUS%TYPE;
550 l_event_id OTA_EVENTS.EVENT_ID%TYPE;
551 l_course_start_date OTA_EVENTS.COURSE_START_DATE%TYPE;
552 l_course_end_date OTA_EVENTS.COURSE_END_DATE%TYPE;
553 l2_course_end_date OTA_EVENTS.COURSE_END_DATE%TYPE;
554 l3_course_end_date OTA_EVENTS.COURSE_END_DATE%TYPE;
555 l_course_start_time OTA_EVENTS.COURSE_START_TIME%TYPE;
556 l_course_end_time OTA_EVENTS.COURSE_END_TIME%TYPE;
557 l_enrollment_start_date OTA_EVENTS.ENROLMENT_START_DATE%TYPE;
558 l_enrollment_end_date OTA_EVENTS.ENROLMENT_END_DATE%TYPE;
559 l2_enrollment_end_date OTA_EVENTS.ENROLMENT_END_DATE%TYPE;
560 l3_enrollment_end_date OTA_EVENTS.ENROLMENT_END_DATE%TYPE;
561 l_default_event_owner_id OTA_EVENTS.OWNER_ID%TYPE;
562 l_owner_id OTA_EVENTS.OWNER_ID%TYPE;
563 l_maximum_attendees OTA_EVENTS.MAXIMUM_ATTENDEES%TYPE;
564 l_event_ovn OTA_EVENTS.OBJECT_VERSION_NUMBER%TYPE;
565 l_category OTA_CATEGORY_USAGES.CATEGORY%TYPE;
566 l_category_usage_id OTA_CATEGORY_USAGES.CATEGORY_USAGE_ID%TYPE;
567 l_cat_usages_ovn OTA_CATEGORY_USAGES.OBJECT_VERSION_NUMBER%TYPE;
568 l_cat_inc_ovn OTA_ACT_CAT_INCLUSIONS.OBJECT_VERSION_NUMBER%TYPE;
569 l_primary_flag OTA_ACT_CAT_INCLUSIONS.PRIMARY_FLAG%TYPE;
570 l_dummy varchar2(6);
571 l_primary_count number(1);
572 l_sysdate date;
573 l_no_of_enrollments number(9);
574 l_waitlist_size number(3);
575 l_course_start_date_time date;
576 l_waitlist_hours number;
577 l_diff_hours number;
578 l_call_auto_enroll varchar2(1) := 'N';
579 l_auto_enroll_status varchar2(1);
580 l_total_placed number;
581 l_sysdatetime varchar2(30);
582 l_user_name fnd_user.user_name%TYPE;
583 l_event_title OTA_EVENTS_TL.TITLE%TYPE; -- MLS change _TL added
584 l_synchronous_flag ota_category_usages.synchronous_flag%type;
585 l_online_flag ota_category_usages.synchronous_flag%type;
586
587 cursor cur_get_event_id is
588 select
589 event_id,
590 object_version_number,
591 event_type,
592 course_end_date,
593 enrolment_end_date,
594 event_status,
595 maximum_attendees,
596 -- to_date(to_char(Course_start_date,'DD-MON-YYYY')||Course_start_time,'DD-MON-YYYYHH24:MI'),
597 to_date(to_char(Course_start_date,'DD/MM/YYYY')||Course_start_time,'DD/MM/YYYYHH24:MI'),
598 owner_id
599 from
600 ota_events
601 where
602 offering_id = p_offering_id and
603 business_group_id = p_business_group_id;
604
605
606
607 cursor cur_get_cat_usage_id is
608 select
609 category_usage_id
610 from
611 ota_category_usages
612 where
613 business_group_id = p_business_group_id and
614 type = 'DM' and
615 category = l_category;
616
617
618
619 cursor cur_get_date_time (l_date in date) is
620 select l_date,
621 -- select to_date(to_char(l_date,'DD/MM/YYYY')),
622 -- select to_date(to_char(l_date,'DD-MON-RRRR')),
623 --Bug#2324698 hdshah changed to MI instead of MM
624 -- to_char(l_date,'HH24:MM')
625 to_char(l_date,'HH24:MI')
626 from
627 dual;
628
629
630 cursor cur_check_cat_inclusions is
631 select
632 'dummy'
633 from
634 ota_act_cat_inclusions
635 where
636 activity_version_id = p_activity_version_id and
637 category_usage_id = l_category_usage_id;
638
639
640 cursor cur_get_primary_count is
641 select
642 count(*)
643 from
644 ota_category_usages OCU,
645 ota_act_cat_inclusions OAC
646 where
647 OAC.activity_version_id = p_activity_version_id and
648 OAC.category_usage_id = OCU.category_usage_id and
649 OCU.type = 'DM' and
650 OAC.primary_flag = 'Y';
651
652
653 cursor cur_get_activity_start_date is
654 select
655 start_date,
656 object_version_number
657 from
658 ota_activity_versions
659 where
660 activity_version_id = p_activity_version_id;
661
662
663 cursor cur_check_enroll_exist is
664 select
665 count(*)
666 from
667 ota_delegate_bookings
668 where
669 event_id = l_event_id;
670
671 cursor cur_waitlist_size is
672 select count(*)
673 from ota_delegate_bookings
674 where booking_status_type_id in (SELECT booking_status_type_id
675 FROM ota_booking_status_types
676 WHERE type = 'W')
677 and event_id = l_event_id;
678
679
680 cursor cur_get_total_placed is
681 select count(*)
682 from ota_delegate_bookings
683 where booking_status_type_id in (SELECT booking_status_type_id
684 FROM ota_booking_status_types
685 WHERE type in ('A','P','E'))
686 and event_id = l_event_id;
687
688
689 cursor cur_user (l_owner_id OTA_EVENTS.OWNER_ID%TYPE) is
690 select user_name
691 from fnd_user
692 where employee_id = l_owner_id;
693
694 cursor cur_get_event_title is
695 select title
696 from ota_events_vl -- MLS change _vl added
697 where title = p_offering_title and
698 business_group_id = p_business_group_id;
699
700 begin
701 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
702
703
704 IF p_offering_type <> 'C' then --if not INCLASS EVENT
705
706
707 if p_offering_type in ('W','B') then --if offering type
708 l_event_type := 'SCHEDULED';
709 else
710 l_event_type := 'SELFPACED';
711 end if; -- if offering type
712
713 if p_offering_end_date is NULL then --if offering end date is null
714 select to_date('31/12/4712','DD/MM/YYYY') into l_course_end_date from dual;
715 l_course_end_time := '23:59';
716 else
717 open cur_get_date_time(p_offering_end_date);
718 fetch cur_get_date_time into l_course_end_date, l_course_end_time;
719 close cur_get_date_time;
720 end if; --if offering end date is null
721
722 if p_offering_start_date is NULL then --if offering start date is null
723 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application did not create or update the Event for the Offering '||
724 p_offering_title || '. You must return to OiL and enter a Start Date for the Offering.');
725 p_message := 'ERROR:Offering start date is null for offering id - '|| p_offering_id;
726 -- dbms_output.put_line(p_message);
727 -- p_status := 'F';
728 p_status := 'W';
729 return;
730 else
731 open cur_get_date_time(p_offering_start_date);
732 fetch cur_get_date_time into l_course_start_date, l_course_start_time;
733 close cur_get_date_time;
734 end if; --if offering start date is null
735
736 open cur_get_activity_start_date;
737 fetch cur_get_activity_start_date into l_activity_start_date, l_activity_ovn;
738 close cur_get_activity_start_date;
739
740 if p_enrollment_start_date is null then --if enrollment start date is null
741 if l_activity_start_date > l_course_start_date then --if event start date is earlier
742 l_enrollment_start_date := l_course_start_date;
743 else
744 l_enrollment_start_date := l_activity_start_date;
745 end if; --if event start date is earlier
746 else
747 l_enrollment_start_date := p_enrollment_start_date;
748 end if; --if enrollment start date is null
749
750
751 if trunc(l_enrollment_start_date) > trunc(l_course_start_date) then
752 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Enrollment Start Date must be earlier than the Offering Start Date. Please return to OiL and correct the Enrollment Start Date for the Offering '||
753 p_offering_title ||'.');
754 p_status := 'W';
755 return;
756 end if;
757
758 if p_enrollment_end_date is null then --if enrollment end date is null use event end date
759 l_enrollment_end_date := l_course_end_date;
760 else
761 l_enrollment_end_date := p_enrollment_end_date;
762 end if; --if enrollment end date is null use event end date
763
764 l_default_event_owner_id := FND_PROFILE.VALUE('OTA_DEFAULT_EVENT_OWNER');
765
766 open cur_get_event_id;
767 fetch cur_get_event_id into l_event_id,l_event_ovn,l2_event_type,l2_course_end_date,
768 l2_enrollment_end_date,l_event_status,l_maximum_attendees,l_course_start_date_time,l_owner_id;
769 if cur_get_event_id%NOTFOUND then -- if event does not exist
770 close cur_get_event_id;
771
772 if p_offering_ispublished = 'N' then --if offering is unpublished and event does not exist
773 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not create an Event for Offering '|| p_offering_title ||
774 ', because the Offering is Unpublished.');
775 p_message := 'Concurrent program will not process offering id - ' || p_offering_id ||
776 ', Because Event id does not exist and Offering is Unpublished.';
777 -- dbms_output.put_line(p_message);
778 -- p_status := 'F';
779 p_status := 'W';
780 return;
781 end if; --if offering is unpublished and event does not exist
782
783 open cur_get_event_title;
784 fetch cur_get_event_title into l_event_title;
785
786 if cur_get_event_title%FOUND then
787 close cur_get_event_title;
788 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Event Title ' || p_offering_title
789 || ' already exists. You must rename all but one Offering of that name.');
790 p_message := 'Event Title already exist ';
791 -- dbms_output.put_line(p_message);
792 -- p_status := 'F';
793 p_status := 'E';
794 return;
795
796 end if;
797 close cur_get_event_title;
798
799
800
801 if p_offering_type = 'B' then --if offering type
802 l_category := 'ESEMINAR_SCHEDULED';
803 l_synchronous_flag := 'Y';
804 l_online_flag := 'Y';
805 elsif p_offering_type = 'O' then
806 l_category := 'OFFLINE_SELF_PACED';
807 l_synchronous_flag := 'N';
808 l_online_flag := 'N';
809 elsif p_offering_type = 'Q' then
810 l_category := 'ESEMINAR_SELF_PACED';
811 l_synchronous_flag := 'N';
812 l_online_flag := 'Y';
813 elsif p_offering_type = 'R' then
814 l_category := 'ECLASS_SELF_PACED';
815 l_synchronous_flag := 'N';
816 l_online_flag := 'Y';
817 elsif p_offering_type = 'S' then
818 l_category := 'ESTUDY_SELF_PACED';
819 l_synchronous_flag := 'N';
820 l_online_flag := 'Y';
821 elsif p_offering_type = 'W' then
822 l_category := 'ECLASS';
823 l_synchronous_flag := 'N';
824 l_online_flag := 'Y';
825 end if; -- if offering type
826
827 open cur_get_cat_usage_id ;
828 fetch cur_get_cat_usage_id into l_category_usage_id;
829 if cur_get_cat_usage_id%NOTFOUND then --if category usage does not exist
830 close cur_get_cat_usage_id;
831
832 BEGIN
833 -- clear message before calling API
834 hr_utility.clear_message;
835
836 ota_ctu_ins.ins
837 (
838 p_category_usage_id => l_category_usage_id --(Output)
839 ,p_business_group_id => p_business_group_id --(Input)
840 ,p_category => l_category --(Input)
841 ,p_object_version_number => l_cat_usages_ovn --(Output)
842 ,p_type => 'DM'
843 ,p_synchronous_flag => l_synchronous_flag
844 ,p_online_flag => l_online_flag --(Input)
845 -- ,p_start_date_active => l_start_date_active --(Input)
846 -- ,p_end_date_active => l_end_date_active --(Input)
847 ,p_effective_date => trunc(sysdate) --(Input)
848 );
849
850 ota_ctt_ins. ins_tl
851 (p_effective_date =>trunc(sysdate)
852 ,p_language_code =>USERENV('LANG')
853 ,p_category_usage_id =>l_category_usage_id
854 ,p_category => l_category);
855
856 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully created the Delivery Method for Category ' || l_category||'.');
857 p_message := 'Category usage id - ' || l_category_usage_id || ' created successfully.';
858 -- dbms_output.put_line(p_message);
859
860 EXCEPTION
861 when others then
862
863 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not create the Delivery Method for Category ' || l_category
864 || '. Reason:' || hr_utility.get_message);
865
866 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:Unable to create category usage for category - '||
867 -- l_category || '. REASON:' || hr_utility.get_message);
868 p_message := 'ERROR:Unable to create category usage for category - '|| l_category;
869 -- dbms_output.put_line(p_message);
870 -- p_status := 'F';
871 p_status := 'W';
872 return;
873 END;
874
875 else
876 close cur_get_cat_usage_id;
877 end if;--if category usage does not exist
878
879 open cur_check_cat_inclusions;
880 fetch cur_check_cat_inclusions into l_dummy;
881 if cur_check_cat_inclusions%NOTFOUND then --if cat inclusion does not exist
882 close cur_check_cat_inclusions;
883 open cur_get_primary_count;
884 fetch cur_get_primary_count into l_primary_count;
885 close cur_get_primary_count;
886 if (l_primary_count = 0) then -- if primary count
887 l_primary_flag := 'Y';
888 else
889 l_primary_flag := 'N';
890 end if; -- if primary count
891 -- create category inclusions
892
893 BEGIN
894 -- clear message before calling API
895 hr_utility.clear_message;
896 /* OTA_ACI_API.INS
897 (
898 p_activity_version_id => p_activity_version_id -- (Input)
899 ,p_activity_category => l_category -- (Input)
900 ,p_object_version_number => l_cat_inc_ovn -- (Output)
901 -- ,p_start_date_active => l_start_date_active -- (Input)
902 -- ,p_end_date_active => l_end_date_active -- (Input)
903 ,p_primary_flag => l_primary_flag -- (Input)
904 ,p_category_usage_id => l_category_usage_id -- (Input)
905 ,p_validate => false -- (Input)
906 );*/
907
908 OTA_ACI_INS.INS
909 (
910 p_activity_version_id => p_activity_version_id -- (Input)
911 ,p_activity_category => l_category -- (Input)
912 ,p_object_version_number => l_cat_inc_ovn -- (Output)
913 -- ,p_start_date_active => l_start_date_active -- (Input)
914 -- ,p_end_date_active => l_end_date_active -- (Input)
915 ,p_primary_flag => l_primary_flag -- (Input)
916 ,p_category_usage_id => l_category_usage_id -- (Input)
917 ,p_effective_date => trunc(sysdate) -- (Input)
918 );
919
920 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully included Delivery Method in Activity Version ID '||
921 p_activity_version_id ||'.');
922 p_message := 'Successfully created category inclusion for category usage id - '|| l_category_usage_id;
923 -- dbms_output.put_line(p_message);
924
925 EXCEPTION
926 when others then
927 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not include Delivery Method in Activity Version ID '||
928 p_activity_version_id || '. Reason:' || hr_utility.get_message);
929 p_message := 'ERROR:Unable to create category inclusion for category usage id - '|| l_category_usage_id;
930 -- dbms_output.put_line(p_message);
931 -- dbms_output.put_line('p_activity_version_id - ' || p_activity_version_id);
932 -- dbms_output.put_line('l_category - ' || l_category);
933 -- dbms_output.put_line('l_cat_inc_ovn - ' || l_cat_inc_ovn);
934 -- dbms_output.put_line('l_start_date_active - ' || l_start_date_active);
935 -- dbms_output.put_line('l_end_date_active - ' || l_end_date_active);
936 -- dbms_output.put_line('l_primary_flag - ' || l_primary_flag);
937 -- dbms_output.put_line('l_category_usage_id - ' || l_category_usage_id);
938 -- p_status := 'F';
939 p_status := 'W';
940 return;
941
942 END;
943 else
944 close cur_check_cat_inclusions;
945 end if; --if cat inclusion does not exist
946
947 if l_activity_start_date > l_course_start_date then --if event start date is earlier then activity start date
948
949 BEGIN
950 -- clear message before calling API
951 hr_utility.clear_message;
952 -- Bug#2201416 trunc included for l_course_start_date parameter.
953 ota_tav_upd.Upd
954 (
955 p_activity_version_id => p_activity_version_id -- (Input)
956 ,p_start_date => trunc(l_course_start_date) -- (Input)
957 ,p_object_version_number => l_activity_ovn -- (Input Output)
958 -- ,p_rco_id => p_rco_id -- (Input)
959 ,p_validate => false -- (Input)
960 );
961
962 EXCEPTION
963 when others then
964 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Activity Start Date for Activity Version ID '||
965 p_activity_version_id || '. REASON:' || hr_utility.get_message);
966 p_message := 'ERROR:Unable to update Activity start date for activity_version_id - '||
967 p_activity_version_id || ' And offering Id - ' ||p_offering_id;
968 -- dbms_output.put_line(p_message);
969 -- p_status := 'F';
970 p_status := 'W';
971 return;
972
973 END;
974 end if;--if event start date is earlier then activity start date
975
976
977 -- Create Event
978
979 BEGIN
980 -- clear message before calling API
981 hr_utility.clear_message;
982 /* change for eBS
983 OTA_EVT_INS.INS
984 (
985 P_EVENT_ID => l_event_id -- (Output)
986 ,P_BUSINESS_GROUP_ID => p_business_group_id -- (Input)
987 ,P_EVENT_TYPE => l_event_type -- (Input)
988 ,P_OBJECT_VERSION_NUMBER => l_event_ovn -- (Output)
989 ,P_TITLE => p_offering_title -- (Input)
990 ,P_LANGUAGE_ID => p_language_id -- (Input)
991 ,P_PRICE_BASIS => 'N' -- (Input)
992 ,P_ACTIVITY_VERSION_ID => p_activity_version_id -- (Input)
993 ,P_COURSE_START_DATE => trunc(l_course_start_date) -- (Input)
994 ,P_COURSE_START_TIME => l_course_start_time -- (Input)
995 ,P_COURSE_END_DATE => trunc(l_course_end_date) -- (Input)
996 ,P_COURSE_END_TIME => l_course_end_time -- (Input)
997 ,P_ENROLMENT_START_DATE => trunc(l_enrollment_start_date) -- (Input)
998 ,P_ENROLMENT_END_DATE => trunc(l_enrollment_end_date) -- (Input)
999 ,P_EVENT_STATUS => 'N' -- (Input)
1000 ,P_MAXIMUM_ATTENDEES => p_offering_max_attendees -- (Input)
1001 ,P_MAXIMUM_INTERNAL_ATTENDEES => p_offering_max_attendees -- (Input)
1002 ,P_PUBLIC_EVENT_FLAG => 'Y' -- (Input)
1003 ,P_SECURE_EVENT_FLAG => 'N' -- (Input)
1004 ,P_OWNER_ID => l_default_event_owner_id -- (Input)
1005 ,P_OFFERING_ID => p_offering_id -- (Input)
1006 --Bug#2200017 timezone included.
1007 ,P_TIMEZONE => p_offering_timezone -- (Input)
1008 -- Bug#2201416 book_independent_flag included.
1009 ,P_BOOK_INDEPENDENT_FLAG => 'N' -- (Input)
1010 ,P_VALIDATE => false -- (Input)
1011 );
1012
1013 --Bug 2984480 - arkashya MLS Changes calls to _TL row handler for insert
1014 select trunc(sysdate) into l_sysdate from dual;
1015 OTA_ENT_INS.INS_TL
1016 (
1017 p_effective_date => l_sysdate
1018 ,p_language_code => USERENV('LANG')
1019 ,p_event_id => l_event_id
1020 ,p_title => p_offering_title
1021 );
1022
1023 */
1024 FND_FILE.PUT_LINE(FND_FILE.LOG,'The offering '|| p_offering_title || ' cannot be transferred into iLearning 11i. You must launch iLearning 11i and create the new class there. ');
1025 -- p_message := 'Event created successfully.Event id is -'||l_event_id||' for Offering id - '||p_offering_id;
1026 -- dbms_output.put_line(p_message);
1027 p_status := 'S';
1028 return;
1029 /*
1030 EXCEPTION
1031 when others then
1032 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not create an Event for the Offering '|| p_offering_title
1033 || '. Reason:' || hr_utility.get_message);
1034 p_message := 'ERROR:Unable to create Event for Offering id - '||p_offering_id;
1035
1036 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_event_id -' || l_event_id);
1037 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_business_group_id - ' || p_business_group_id);
1038 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_event_type - ' || l_event_type);
1039 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_event_ovn - ' || l_event_ovn);
1040 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_offering_title - ' || p_offering_title);
1041 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_language_id - ' || p_language_id);
1042 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_activity_version_id - ' || p_activity_version_id);
1043 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_course_start_date - ' || l_course_start_date);
1044 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_course_end_date - ' || l_course_end_date);
1045 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_course_start_time - ' || l_course_start_time);
1046 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_course_end_time - ' || l_course_end_time);
1047 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_enrollment_start_date - ' || l_enrollment_start_date);
1048 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_enrollment_end_date - ' || l_enrollment_end_date);
1049 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_offering_max_attendees - ' || p_offering_max_attendees);
1050 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_default_event_owner_id - ' || l_default_event_owner_id);
1051 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_offering_id - ' || p_offering_id);
1052
1053 -- dbms_output.put_line(p_message);
1054 -- p_status := 'F';
1055 p_status := 'W';
1056 return;
1057 */
1058 END;
1059
1060
1061 else -- if event does not exist
1062 close cur_get_event_id;
1063
1064 -- if event type changed from self-paced to schedule or schedule to self-paced
1065 if l_event_type <> l2_event_type then --if event type changed
1066 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Offering '|| p_offering_title ||
1067 ', because you cannot change Offerings from Self-Paced to Scheduled or Scheduled to Self-Paced ');
1068 p_message := 'ERROR:Event type changed from Self-Paced to Schedule or Schedule to Self-Paced ' ||
1069 'for offering Id - ' || p_offering_id;
1070 -- dbms_output.put_line(p_message);
1071 -- p_status := 'F';
1072 p_status := 'W';
1073 return;
1074 end if; --if event type changed
1075
1076 /* if p_offering_type = 'B' then --if offering type
1077 l_category := 'ESEMINAR_SCHEDULED';
1078 elsif p_offering_type = 'O' then
1079 l_category := 'OFFLINE_SELF_PACED';
1080 elsif p_offering_type = 'Q' then
1081 l_category := 'ESEMINAR_SELF_PACED';
1082 elsif p_offering_type = 'R' then
1083 l_category := 'ECLASS_SELF_PACED';
1084 elsif p_offering_type = 'S' then
1085 l_category := 'ESTUDY_SELF_PACED';
1086 elsif p_offering_type = 'W' then
1087 l_category := 'ECLASS';
1088 end if; -- if offering type*/
1089
1090
1091
1092 if p_offering_type = 'B' then --if offering type
1093 l_category := 'ESEMINAR_SCHEDULED';
1094 l_synchronous_flag := 'Y';
1095 l_online_flag := 'Y';
1096 elsif p_offering_type = 'O' then
1097 l_category := 'OFFLINE_SELF_PACED';
1098 l_synchronous_flag := 'N';
1099 l_online_flag := 'N';
1100 elsif p_offering_type = 'Q' then
1101 l_category := 'ESEMINAR_SELF_PACED';
1102 l_synchronous_flag := 'N';
1103 l_online_flag := 'Y';
1104 elsif p_offering_type = 'R' then
1105 l_category := 'ECLASS_SELF_PACED';
1106 l_synchronous_flag := 'N';
1107 l_online_flag := 'Y';
1108 elsif p_offering_type = 'S' then
1109 l_category := 'ESTUDY_SELF_PACED';
1110 l_synchronous_flag := 'N';
1111 l_online_flag := 'Y';
1112 elsif p_offering_type = 'W' then
1113 l_category := 'ECLASS';
1114 l_synchronous_flag := 'N';
1115 l_online_flag := 'Y';
1116 end if; -- if offering type
1117
1118 open cur_get_cat_usage_id ;
1119 fetch cur_get_cat_usage_id into l_category_usage_id;
1120 if cur_get_cat_usage_id%NOTFOUND then --if category usage does not exist
1121 close cur_get_cat_usage_id;
1122
1123 BEGIN
1124 -- clear message before calling API
1125 hr_utility.clear_message;
1126
1127 ota_ctu_ins.ins
1128 (
1129 p_category_usage_id => l_category_usage_id --(Output)
1130 ,p_business_group_id => p_business_group_id --(Input)
1131 ,p_category => l_category --(Input)
1132 ,p_object_version_number => l_cat_usages_ovn --(Output)
1133 ,p_type => 'DM'
1134 ,p_synchronous_flag => l_synchronous_flag
1135 , p_online_flag => l_online_flag --(Input)
1136 -- ,p_start_date_active => l_start_date_active --(Input)
1137 -- ,p_end_date_active => l_end_date_active --(Input)
1138 ,p_effective_date => trunc(sysdate) --(Input)
1139 );
1140
1141 ota_ctt_ins. ins_tl
1142 (p_effective_date =>trunc(sysdate)
1143 ,p_language_code =>USERENV('LANG')
1144 ,p_category_usage_id =>l_category_usage_id
1145 ,p_category => l_category);
1146
1147
1148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully created the Delivery Method for Category ' || l_category||'.');
1149 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Category usage id - ' || l_category_usage_id ||
1150 -- ' created successfully ');
1151 p_message := 'Category usage id - ' || l_category_usage_id || ' created successfully.';
1152 -- dbms_output.put_line(p_message);
1153
1154 EXCEPTION
1155 when others then
1156 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not create the Delivery Method for Category ' || l_category
1157 || '. Reason:' || hr_utility.get_message);
1158 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:Unable to create category usage for category - '||
1159 -- l_category || '. REASON:' || hr_utility.get_message);
1160 p_message := 'ERROR:Unable to create category usage for category - '|| l_category;
1161 -- dbms_output.put_line(p_message);
1162 -- p_status := 'F';
1163 p_status := 'W';
1164 return;
1165 END;
1166
1167 else
1168 close cur_get_cat_usage_id;
1169 end if;--if category usage does not exist
1170
1171 open cur_check_cat_inclusions;
1172 fetch cur_check_cat_inclusions into l_dummy;
1173 if cur_check_cat_inclusions%NOTFOUND then --if cat inclusion does not exist
1174 close cur_check_cat_inclusions;
1175 open cur_get_primary_count;
1176 fetch cur_get_primary_count into l_primary_count;
1177 close cur_get_primary_count;
1178 if (l_primary_count = 0) then -- if primary count
1179 l_primary_flag := 'Y';
1180 else
1181 l_primary_flag := 'N';
1182 end if; -- if primary count
1183 -- create category inclusions
1184
1185 BEGIN
1186 -- clear message before calling API
1187 hr_utility.clear_message;
1188 /* OTA_ACI_API.INS
1189 (
1190 p_activity_version_id => p_activity_version_id -- (Input)
1191 ,p_activity_category => l_category -- (Input)
1192 ,p_object_version_number => l_cat_inc_ovn -- (Output)
1193 -- ,p_start_date_active => l_start_date_active -- (Input)
1194 -- ,p_end_date_active => l_end_date_active -- (Input)
1195 ,p_primary_flag => l_primary_flag -- (Input)
1196 ,p_category_usage_id => l_category_usage_id -- (Input)
1197 ,p_validate => false -- (Input)
1198 );*/
1199
1200 OTA_ACI_INS.INS
1201 (
1202 p_activity_version_id => p_activity_version_id -- (Input)
1203 ,p_activity_category => l_category -- (Input)
1204 ,p_object_version_number => l_cat_inc_ovn -- (Output)
1205 -- ,p_start_date_active => l_start_date_active -- (Input)
1206 -- ,p_end_date_active => l_end_date_active -- (Input)
1207 ,p_primary_flag => l_primary_flag -- (Input)
1208 ,p_category_usage_id => l_category_usage_id -- (Input)
1209 ,p_effective_date => trunc(sysdate) -- (Input)
1210 );
1211
1212 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully included Delivery Method in Activity Version ID '||
1213 p_activity_version_id ||'.');
1214 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully created category inclusion for category usage id- '||
1215 -- l_category_usage_id);
1216 p_message := 'Successfully created category inclusion for category usage id - '|| l_category_usage_id;
1217 -- dbms_output.put_line(p_message);
1218
1219 EXCEPTION
1220 when others then
1221 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not include Delivery Method in Activity Version ID '||
1222 p_activity_version_id || '. Reason:' || hr_utility.get_message);
1223 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:Unable to create category inclusion for category usage id- '||
1224 -- l_category_usage_id || '. REASON:' || hr_utility.get_message);
1225 p_message := 'ERROR:Unable to create category inclusion for category usage id - '|| l_category_usage_id;
1226 -- dbms_output.put_line(p_message);
1227 -- dbms_output.put_line('p_activity_version_id - ' || p_activity_version_id);
1228 -- dbms_output.put_line('l_category - ' || l_category);
1229 -- dbms_output.put_line('l_cat_inc_ovn - ' || l_cat_inc_ovn);
1230 -- dbms_output.put_line('l_start_date_active - ' || l_start_date_active);
1231 -- dbms_output.put_line('l_end_date_active - ' || l_end_date_active);
1232 -- dbms_output.put_line('l_primary_flag - ' || l_primary_flag);
1233 -- dbms_output.put_line('l_category_usage_id - ' || l_category_usage_id);
1234 -- p_status := 'F';
1235 p_status := 'W';
1236 return;
1237
1238 END;
1239 else
1240 close cur_check_cat_inclusions;
1241 end if; --if cat inclusion does not exist
1242
1243
1244 if l_activity_start_date > l_course_start_date then --if event start date is earlier then activity start date
1245
1246 BEGIN
1247 -- clear message before calling API
1248 hr_utility.clear_message;
1249 -- Bug#2201416 trunc included for l_course_start_date parameter.
1250 ota_tav_upd.Upd
1251 (
1252 p_activity_version_id => p_activity_version_id -- (Input)
1253 ,p_start_date => trunc(l_course_start_date) -- (Input)
1254 ,p_object_version_number => l_activity_ovn -- (Input Output)
1255 -- ,p_rco_id => p_rco_id -- (Input)
1256 ,p_validate => false -- (Input)
1257 );
1258 EXCEPTION
1259 when others then
1260 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Activity Start Date for Activity Version ID '||
1261 p_activity_version_id || '. REASON:' || hr_utility.get_message);
1262 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:Unable to update Activity start date for activity_version_id - '||
1263 -- p_activity_version_id || ' And offering Id - ' ||p_offering_id
1264 -- || '. REASON:' || hr_utility.get_message);
1265 p_message := 'ERROR:Unable to update Activity start date for activity_version_id - '||
1266 p_activity_version_id || ' And offering Id - ' ||p_offering_id;
1267 -- dbms_output.put_line(p_message);
1268 -- p_status := 'F';
1269 p_status := 'W';
1270 return;
1271 END;
1272
1273 end if;--if event start date is earlier then activity start date
1274
1275 if l_course_end_date is null then
1276 l3_course_end_date := l2_course_end_date;
1277 else
1278 l3_course_end_date := l_course_end_date;
1279 end if;
1280
1281 if l_enrollment_end_date is null then
1282 l3_enrollment_end_date := l2_enrollment_end_date;
1283 else
1284 l3_enrollment_end_date := l_enrollment_end_date;
1285 end if;
1286
1287 select sysdate into l_sysdate from dual;
1288
1289 open cur_check_enroll_exist;
1290 fetch cur_check_enroll_exist into l_no_of_enrollments;
1291 close cur_check_enroll_exist;
1292
1293 -- if offering changed from published to unpublished
1294 if p_offering_ispublished = 'N' then --if
1295 if (l3_course_end_date < l_sysdate) or (l_no_of_enrollments >0) then
1296 if l3_enrollment_end_date > l_sysdate then
1297 l_enrollment_end_date := l_sysdate;
1298 end if;
1299 else
1300 if (l3_course_end_date > l_sysdate) and (l_no_of_enrollments = 0 ) then
1301 l_event_status := 'A';
1302 end if;
1303 end if;
1304
1305 -- if offering changed from unpublished to published.
1306 elsif p_offering_ispublished = 'Y' then
1307
1308 NULL;
1309 -- change the event status to Normal
1310
1311 end if;
1312
1313 -- if maximum attendees changed on iLearning side then
1314 if l_maximum_attendees <> p_offering_max_attendees then
1315
1316 -- if max attendees increased
1317 if l_maximum_attendees < p_offering_max_attendees then
1318
1319 open cur_waitlist_size;
1320 fetch cur_waitlist_size into l_waitlist_size;
1321 close cur_waitlist_size;
1322
1323
1324 -- if Auto Waitlist Active profile is turned ON
1325 if ('Y' = FND_PROFILE.VALUE('OTA_AUTO_WAITLIST_ACTIVE')) then
1326
1327
1328 select sysdate into l_sysdate from dual;
1329 l_diff_hours := l_course_start_date_time - l_sysdate ;
1330 l_diff_hours := l_diff_hours * 24 ;
1331 l_waitlist_hours := FND_PROFILE.VALUE('OTA_AUTO_WAITLIST_DAYS');
1332
1333 IF l_diff_hours <= nvl(l_waitlist_hours,0) THEN -- if not enough time then
1334 -- update max attendees and notify Event Owner
1335 l_maximum_attendees := p_offering_max_attendees;
1336
1337 -- send notification to event owner if waitlist candidates > 0
1338 if l_waitlist_size > 0 then
1339 -- send notification
1340 SELECT to_char(sysdate,'DD/MM/YYYY:HH24:MI:SS') INTO l_sysdatetime FROM dual;
1341 -- SELECT to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') INTO l_sysdatetime FROM dual;
1342
1343 if l_owner_id is null then
1344 OPEN CUR_USER(l_default_event_owner_id);
1345 else
1346 OPEN CUR_USER(l_owner_id);
1347 end if;
1348 FETCH CUR_USER INTO l_user_name;
1349 CLOSE CUR_USER;
1350
1351 OTA_INITIALIZATION_WF.MANUAL_WAITLIST(p_itemtype => 'OTWF',
1352 p_process => 'OTA_MANUAL_WAITLIST',
1353 p_event_title => p_offering_title,
1354 p_event_id => l_event_id,
1355 p_item_key => l_sysdatetime,
1356 p_user_name => l_user_name);
1357 end if;
1358
1359 ELSE -- if enough time
1360 l_maximum_attendees := p_offering_max_attendees;
1361
1362 -- if waitlist candidates > 0 call auto enroll
1363 if l_waitlist_size > 0 then
1364 l_call_auto_enroll := 'Y';
1365 end if;
1366
1367
1368 END IF;
1369
1370
1371 else -- if Auto Waitlist Active profile is turned OFF
1372 -- update max attendees
1373 l_maximum_attendees := p_offering_max_attendees;
1374
1375 -- send notification to event owner if waitlist candidates > 0
1376 if l_waitlist_size > 0 then
1377 -- send notification
1378 SELECT to_char(sysdate,'DD/MM/YYYY:HH24:MI:SS') INTO l_sysdatetime FROM dual;
1379 -- SELECT to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') INTO l_sysdatetime FROM dual;
1380
1381 if l_owner_id is null then
1382 OPEN CUR_USER(l_default_event_owner_id);
1383 else
1384 OPEN CUR_USER(l_owner_id);
1385 end if;
1386 FETCH CUR_USER INTO l_user_name;
1387 CLOSE CUR_USER;
1388
1389 OTA_INITIALIZATION_WF.MANUAL_WAITLIST(p_itemtype => 'OTWF',
1390 p_process => 'OTA_MANUAL_WAITLIST',
1391 p_event_title => p_offering_title,
1392 p_event_id => l_event_id,
1393 p_item_key => l_sysdatetime,
1394 p_user_name => l_user_name);
1395 end if;
1396
1397 end if;
1398
1399
1400
1401 else -- if max attendees decreased
1402
1403 open cur_get_total_placed;
1404 fetch cur_get_total_placed into l_total_placed;
1405 close cur_get_total_placed;
1406
1407 if l_total_placed < p_offering_max_attendees then
1408
1409 l_maximum_attendees := p_offering_max_attendees;
1410
1411 else
1412 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Maximum attendees to '||
1413 p_offering_max_attendees || 'for the Event ' ||
1414 p_offering_title || ' , because '|| l_total_placed ||
1415 ' students have already enrolled in the event.');
1416 p_message := 'ERROR:Maximum attendees for event id - ' ||
1417 l_event_id || ' cannot be updated to '|| p_offering_max_attendees ||
1418 ' because '|| l_total_placed || ' students are already enrolled.';
1419 -- dbms_output.put_line(p_message);
1420 -- p_status := 'F';
1421 p_status := 'W';
1422 return;
1423 end if;
1424
1425 end if;
1426
1427
1428 end if;
1429
1430
1431
1432
1433
1434 -- Update event
1435
1436 BEGIN
1437 -- clear message before calling API
1438 hr_utility.clear_message;
1439
1440 OTA_EVT_UPD.UPD
1441 (
1442 P_EVENT_ID => l_event_id -- (Input)
1443 ,P_BUSINESS_GROUP_ID => p_business_group_id -- (Input)
1444 ,P_EVENT_TYPE => l_event_type -- (Input)
1445 ,P_OBJECT_VERSION_NUMBER => l_event_ovn -- (Output)
1446 ,P_TITLE => p_offering_title -- (Input)
1447 ,P_COURSE_START_DATE => trunc(l_course_start_date) -- (Input)
1448 ,P_COURSE_START_TIME => l_course_start_time -- (Input)
1449 ,P_COURSE_END_DATE => trunc(l_course_end_date) -- (Input)
1450 ,P_COURSE_END_TIME => l_course_end_time -- (Input)
1451 ,P_ENROLMENT_START_DATE => trunc(l_enrollment_start_date) -- (Input)
1452 ,P_ENROLMENT_END_DATE => trunc(l_enrollment_end_date) -- (Input)
1453 ,P_MAXIMUM_ATTENDEES => l_maximum_attendees -- (Input)
1454 ,P_MAXIMUM_INTERNAL_ATTENDEES => l_maximum_attendees -- (Input)
1455 ,P_EVENT_STATUS => l_event_status -- (Input)
1456 --Bug#2200017 timezone included.
1457 ,P_TIMEZONE => p_offering_timezone -- (Input)
1458 ,P_VALIDATE => false -- (Input)
1459 );
1460
1461
1462 --Bug 2984480 - arkashya MLS Changes calls to _TL row handler for
1463 select trunc(sysdate) into l_sysdate from dual;
1464 OTA_ENT_UPD.UPD_TL
1465 (
1466 p_effective_date => l_sysdate
1467 ,p_language_code => USERENV('LANG')
1468 ,p_event_id => l_event_id
1469 ,p_title => p_offering_title
1470 );
1471
1472
1473 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Event '|| p_offering_title || '.');
1474 p_message := 'Event updated successfully.Event id is -'||l_event_id||' for Offering id - '||p_offering_id;
1475 -- dbms_output.put_line(p_message);
1476 -- p_status := 'S';
1477 -- return;
1478
1479 EXCEPTION
1480 when others then
1481 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the Event '|| p_offering_title ||
1482 '. Reason:' || hr_utility.get_message);
1483 p_message := 'ERROR:Unable to update Event.Event id is -'||l_event_id||' for Offering id - '||p_offering_id;
1484 -- dbms_output.put_line(p_message);
1485 -- dbms_output.put_line(l_event_id);
1486 -- dbms_output.put_line(p_business_group_id);
1487 -- dbms_output.put_line(l_event_type);
1488 -- dbms_output.put_line('object_version_number - '|| l_event_ovn);
1489 -- dbms_output.put_line(p_offering_title);
1490 -- dbms_output.put_line(l_course_start_date);
1491 -- dbms_output.put_line(l_course_end_date);
1492 -- dbms_output.put_line(l_course_start_time);
1493 -- dbms_output.put_line(l_course_end_time);
1494 -- dbms_output.put_line(l_enrollment_start_date);
1495 -- dbms_output.put_line(l_enrollment_end_date);
1496 -- dbms_output.put_line(p_offering_max_attendees);
1497
1498 -- p_status := 'F';
1499 p_status := 'W';
1500 return;
1501
1502 END;
1503
1504
1505 IF (l_call_auto_enroll = 'Y') then
1506
1507 OTA_OM_TDB_WAITLIST_API.AUTO_ENROLL_FROM_WAITLIST
1508 (
1509 p_validate => false
1510 ,p_business_group_id => p_business_group_id
1511 ,p_event_id => l_event_id
1512 ,p_return_status => l_auto_enroll_status
1513 );
1514
1515 if l_auto_enroll_status = 'F' then
1516
1517 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not automatically enroll students from the waitlists in the Event ' || p_offering_title || '.');
1518 p_message := 'ERROR:Error in Auto_Enroll_From_Waitlist procedure for event id - ' ||
1519 l_event_id || '.';
1520
1521 -- dbms_output.put_line(p_message);
1522 -- p_status := 'F';
1523 p_status := 'W';
1524 return;
1525 else
1526 p_status := 'S';
1527 return;
1528 end if;
1529
1530 ELSE
1531 p_status := 'S';
1532 return;
1533 END IF;
1534
1535
1536 end if; -- if event does not exist
1537
1538 ELSE
1539
1540 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Offering ' || p_offering_title ||
1541 ' is an inClass Offering. The application does not import inClass Offerings.');
1542 p_message := 'Concurrent program will not process Offering id - ' || p_offering_id ||
1543 ' due to offering type - ' || p_offering_type;
1544 -- dbms_output.put_line(p_message);
1545 p_status := 'F';
1546 return;
1547
1548 END IF; --if not INCLASS EVENT
1549
1550
1551 exception
1552 when others then
1553
1554 FND_FILE.PUT_LINE(FND_FILE.LOG,'An error occurred while processing the Offering ' || p_offering_title ||'. SQLERRM:'
1555 || SQLERRM);
1556 p_message := 'ERROR:In when others exception for Offering Id - '|| p_offering_id;
1557 -- dbms_output.put_line(p_message);
1558 -- p_status := 'F';
1559 p_status := 'W';
1560 return;
1561
1562
1563
1564
1565 end crt_or_upd_event;
1566
1567
1568
1569 procedure offering_rco_import (
1570 p_array in OTA_OFFERING_STRUCT_TAB
1571 ,p_business_group_id in varchar2
1572 ,p_activity_definition_name in varchar2
1573 ,p_status out nocopy varchar2
1574 ) is
1575
1576 l_proc varchar2(72) := g_package||'offering_rco_import';
1577 l_activity_version_id OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID%TYPE;
1578 l_language_id FND_LANGUAGES.LANGUAGE_ID%TYPE;
1579 l_rco_status varchar2(1);
1580 l_rco_message varchar2(100);
1581 l_offering_status varchar2(1);
1582 l_offering_message varchar2(100);
1583 l_update varchar2(10);
1584 l_activity_success number(10) := 0;
1585 l_activity_fail number(10) := 0;
1586 l_activity_warning number(10) := 0;
1587 l_event_success number(10) := 0;
1588 l_event_fail number(10) := 0;
1589 l_event_warning number(10) := 0;
1590
1591 begin
1592 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
1593
1594
1595 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of p_array.LAST is:' || p_array.LAST);
1596
1597
1598
1599 FOR p_array_idx IN p_array.FIRST..p_array.LAST LOOP
1600
1601
1602 IF p_array(p_array_idx).rco_id is null then
1603
1604 FND_FILE.PUT_LINE(FND_FILE.LOG,'The RCO ID is missing for the Offering ' || p_array(p_array_idx).offering_title ||
1605 '.');
1606
1607 ELSE
1608
1609 if p_array(p_array_idx).offering_ispublished = 'N' then --if
1610 l_update := 'true';
1611 else
1612 l_update := NULL;
1613 end if;
1614 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'creating/updating activity' || 'RCO-ID:' || to_number(p_array(p_array_idx).rco_id) );
1615
1616
1617 -- Issue SavePoint
1618 SAVEPOINT save_activity;
1619
1620 FND_FILE.PUT_LINE(FND_FILE.LOG,'**-----------------------------------------------------------**');
1621
1622 crt_or_upd_activity
1623 (
1624 p_update => l_update -- (Input)
1625 ,p_rco_id => to_number(p_array(p_array_idx).rco_id) -- (Input)
1626 ,p_language_code => p_array(p_array_idx).rco_language -- (Input)
1627 ,p_activity_version_name => p_array(p_array_idx).rco_title -- (Input)
1628 ,p_description => p_array(p_array_idx).rco_description -- (Input)
1629 ,p_objectives => p_array(p_array_idx).rco_objective -- (Input)
1630 ,p_audience => p_array(p_array_idx).rco_audience -- (Input)
1631 ,p_business_group_id => to_number(p_business_group_id) -- (Input)
1632 ,p_activity_definition_name => p_activity_definition_name -- (Input)
1633 ,p_activity_version_id => l_activity_version_id -- (Output)
1634 ,p_language_id => l_language_id -- (Output)
1635 ,p_status => l_rco_status -- (Output)
1636 ,p_message => l_rco_message -- (Output)
1637 );
1638
1639
1640 if l_rco_status = 'S' then
1641 l_activity_success := l_activity_success +1;
1642 -- do commit
1643 commit;
1644 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Activity Insert/Update commited.');
1645
1646 elsif l_rco_status = 'W' then
1647 l_activity_warning := l_activity_warning +1;
1648 ROLLBACK TO save_activity;
1649 FND_FILE.PUT_LINE(FND_FILE.LOG,'The import could not create an activity for the RCO:' || p_array(p_array_idx).rco_title);
1650 else
1651 l_activity_fail := l_activity_fail +1;
1652 -- rollback to save_activity
1653 ROLLBACK TO save_activity;
1654 FND_FILE.PUT_LINE(FND_FILE.LOG,'The import could not create an activity for the RCO:' || p_array(p_array_idx).rco_title);
1655 end if;
1656
1657
1658 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'creating/updating Event');
1659
1660 if (to_number(p_array(p_array_idx).offering_id) <> -1 and l_activity_version_id is not null) then
1661
1662 -- Issue Savepoint
1663 SAVEPOINT save_event;
1664
1665 crt_or_upd_event
1666 (
1667 p_transaction => to_number(p_array(p_array_idx).offering_transaction) -- (Input)
1668 ,p_offering_title => p_array(p_array_idx).offering_title -- (Input)
1669 ,p_offering_id => to_number(p_array(p_array_idx).offering_id) -- (Input)
1670 ,p_offering_start_date => to_date(p_array(p_array_idx).offering_start_date,'yyyy-mm-dd hh24:mi:ss') -- (Input)
1671 ,p_offering_end_date => to_date(p_array(p_array_idx).offering_end_date,'yyyy-mm-dd hh24:mi:ss') -- (Input)
1672 ,p_offering_timezone => p_array(p_array_idx).offering_timezone -- (Input)
1673 ,p_enrollment_start_date => to_date(p_array(p_array_idx).enrollment_start_date,'yyyy-mm-dd hh24:mi:ss') -- (Input)
1674 ,p_enrollment_end_date => to_date(p_array(p_array_idx).enrollment_end_date,'yyyy-mm-dd hh24:mi:ss') -- (Input)
1675 ,p_offering_max_attendees => to_number(p_array(p_array_idx).offering_max_attendees) -- (Input)
1676 ,p_offering_type => p_array(p_array_idx).offering_type -- (Input)
1677 ,p_offering_ispublished => p_array(p_array_idx).offering_ispublished -- (Input)
1678 ,p_language_id => l_language_id -- (Input)
1679 ,p_activity_version_id => l_activity_version_id -- (Input)
1680 ,p_business_group_id => to_number(p_business_group_id) -- (Input)
1681 ,p_status => l_offering_status -- (Output)
1682 ,p_message => l_offering_message -- (Output)
1683 );
1684
1685
1686 if l_offering_status = 'S' then
1687 l_event_success := l_event_success + 1;
1688 -- do commit;
1689 commit;
1690 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Event Insert/Update commited.');
1691 elsif l_offering_status = 'W' then
1692 l_event_warning := l_event_warning + 1;
1693 -- rollback to save_event
1694 ROLLBACK TO save_event;
1695 FND_FILE.PUT_LINE(FND_FILE.LOG,'The import could not create an event for the Offering:' || to_number(p_array(p_array_idx).offering_id) );
1696 else
1697 l_event_fail := l_event_fail + 1;
1698 -- rollback to save_event
1699 ROLLBACK TO save_event;
1700 FND_FILE.PUT_LINE(FND_FILE.LOG,'The import could not create an event for the Offering:' || to_number(p_array(p_array_idx).offering_id) );
1701 end if;
1702
1703 elsif (to_number(p_array(p_array_idx).offering_id) <> -1 and l_activity_version_id is null) then
1704
1705 l_event_warning := l_event_warning + 1;
1706 FND_FILE.PUT_LINE(FND_FILE.LOG,'The application did not create an event for the offering '||
1707 p_array(p_array_idx).offering_title ||
1708 ', because it could not create an activity version from its associated RCO, '||
1709 p_array(p_array_idx).rco_title||'.');
1710
1711 end if;
1712
1713
1714 END IF;
1715
1716 END LOOP;
1717
1718 if ( l_activity_fail > 0 or l_event_fail > 0) then
1719 p_status := 'F';
1720 elsif (l_activity_warning > 0 or l_event_warning > 0) then
1721 p_status := 'W';
1722 else
1723 p_status := 'S';
1724 end if;
1725
1726
1727 l_event_fail := l_event_fail + l_event_warning;
1728 l_activity_fail := l_activity_fail + l_activity_warning;
1729
1730 FND_FILE.PUT_LINE(FND_FILE.LOG,'**-----------------------------------------------------------**');
1731 FND_FILE.PUT_LINE(FND_FILE.LOG,' IMPORT RESULTS FOR OFFERINGS');
1732 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------------------------');
1733 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of Activities Processed Successfully:' || l_activity_success);
1734 FND_FILE.PUT_LINE(FND_FILE.LOG,' Number of Activities Not Processed:' || l_activity_fail);
1735 -- FND_FILE.PUT_LINE(FND_FILE.LOG,' Number of Events Processed Successfully:' || l_event_success);
1736 FND_FILE.PUT_LINE(FND_FILE.LOG,' Number of Events Not Processed:' || l_event_fail );
1737 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------------');
1738 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Exiting:' || l_proc);
1739
1740
1741 end offering_rco_import;
1742
1743
1744
1745
1746
1747 procedure rco_import (
1748 p_array in OTA_RCO_STRUCT_TAB
1749 ,p_business_group_id in varchar2
1750 ,p_activity_definition_name in varchar2
1751 ) is
1752
1753 l_proc varchar2(72) := g_package||'rco_import';
1754 l_activity_version_id OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID%TYPE;
1755 l_language_id FND_LANGUAGES.LANGUAGE_ID%TYPE;
1756 l_rco_status varchar2(1);
1757 l_message varchar2(100);
1758 l_update varchar2(10);
1759 l_activity_success number(10) := 0;
1760 l_activity_fail number(10) := 0;
1761
1762 begin
1763 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc || 'p_array_LAST-' ||p_array.LAST);
1764
1765
1766 FOR p_array_idx IN p_array.FIRST..p_array.LAST LOOP
1767
1768 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
1769
1770 -- Issue Savepoint
1771 SAVEPOINT save_activity;
1772
1773 crt_or_upd_activity
1774 (
1775 p_update => 'false' -- (Input)
1776 ,p_rco_id => to_number(p_array(p_array_idx).rco_id) -- (Input)
1777 ,p_language_code => p_array(p_array_idx).rco_language -- (Input)
1778 ,p_activity_version_name => p_array(p_array_idx).rco_title -- (Input)
1779 ,p_description => p_array(p_array_idx).rco_description -- (Input)
1780 ,p_objectives => p_array(p_array_idx).rco_objective -- (Input)
1781 ,p_audience => p_array(p_array_idx).rco_audience -- (Input)
1782 ,p_business_group_id => to_number(p_business_group_id) -- (Input)
1783 ,p_activity_definition_name => p_activity_definition_name -- (Input)
1784 ,p_activity_version_id => l_activity_version_id -- (Output)
1785 ,p_language_id => l_language_id -- (Output)
1786 ,p_status => l_rco_status -- (Output)
1787 ,p_message => l_message -- (Output)
1788 );
1789
1790
1791 if l_rco_status = 'S' then
1792 l_activity_success := l_activity_success +1;
1793 -- do commit;
1794 commit;
1795 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Activity Update commited.');
1796 else
1797 l_activity_fail := l_activity_fail +1;
1798 -- rollback to save_activity
1799 ROLLBACK TO save_activity;
1800 FND_FILE.PUT_LINE(FND_FILE.LOG,'The import could not create an activity for the RCO:' || to_number(p_array(p_array_idx).rco_id) );
1801 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Activity Update rolled back.');
1802 end if;
1803
1804 END LOOP;
1805
1806 FND_FILE.PUT_LINE(FND_FILE.LOG,' IMPORT RESULTS FOR RCOS');
1807 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------------------------');
1808 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of Activities Processed Successfully:' || l_activity_success);
1809 FND_FILE.PUT_LINE(FND_FILE.LOG,' Number of Activities Not Processed:' || l_activity_fail);
1810 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------------------------');
1811 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Exiting:' || l_proc);
1812
1813 end rco_import;
1814
1815
1816
1817
1818 procedure crt_or_chk_xml_prcs_tbl (
1819 p_site_id in varchar2
1820 ,p_business_group_id in varchar2
1821 ,p_process_name in varchar2
1822 -- ,p_start_date in date
1823 -- ,p_end_date in date
1824 ,p_start_date in varchar2
1825 ,p_end_date in varchar2
1826 ,p_status out nocopy varchar2
1827 ,p_process_type out nocopy varchar2
1828 ) is
1829
1830 l_proc varchar2(72) := g_package||'crt_or_upd_xml_prcs_tbl';
1831 l_business_group_id number := to_number(p_business_group_id);
1832 l_site_id number := to_number(p_site_id);
1833 l_end_date date;
1834 l_2_site_id number;
1835 l_2_business_group_id number;
1836 l_status varchar2(1) := 'F';
1837 l_process_type varchar2(1) := 'A'; ---'A' for Automatic 'M' for Manual
1838 l_sysdate date;
1839
1840 pl_start_date date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1841 pl_end_date date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS');
1842
1843 cursor cur_get_record1 is
1844 select
1845 to_date
1846 from
1847 ota_iln_xml_processes
1848 where
1849 executable_name = p_process_name and
1850 business_group_id = l_business_group_id and
1851 site_id = l_site_id;
1852
1853
1854 cursor cur_get_record2 is
1855 select
1856 site_id
1857 from
1858 ota_iln_xml_processes
1859 where
1860 executable_name = p_process_name and
1861 business_group_id = l_business_group_id;
1862
1863
1864 cursor cur_get_record3 is
1865 select
1866 business_group_id
1867 from
1868 ota_iln_xml_processes
1869 where
1870 executable_name = p_process_name and
1871 site_id = l_site_id;
1872
1873 begin
1874 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
1875
1876 select sysdate into l_sysdate from dual;
1877
1878 -- if (p_start_date is null) or (p_end_date is null) or (p_start_date > p_end_date) or (p_end_date > l_sysdate) then
1879 if (pl_start_date is null) or (pl_end_date is null) or (pl_start_date > pl_end_date) or (pl_end_date > l_sysdate) then
1880 l_status := 'F';
1881 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Start Date must be earlier than the End Date and the End Date cannot be later than the Current date and time.');
1882
1883 else
1884
1885
1886 open cur_get_record1;
1887 fetch cur_get_record1 into l_end_date;
1888 if cur_get_record1%NOTFOUND then
1889 close cur_get_record1;
1890
1891 open cur_get_record2;
1892 fetch cur_get_record2 into l_2_site_id;
1893 if cur_get_record2%NOTFOUND then
1894 close cur_get_record2;
1895
1896 open cur_get_record3;
1897 fetch cur_get_record3 into l_2_business_group_id;
1898 if cur_get_record3%NOTFOUND then
1899 close cur_get_record3;
1900 insert into ota_iln_xml_processes
1901 (executable_name,
1902 business_group_id,
1903 site_id,
1904 from_date,
1905 to_date)
1906 values (p_process_name,
1907 l_business_group_id,
1908 l_site_id,
1909 pl_start_date,
1910 pl_end_date);
1911
1912 l_status := 'S';
1913
1914 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Record created in ota_iln_xml_processes table for ' ||
1915 -- ' process '|| p_process_name ||
1916 -- ' business group id ' || l_business_group_id ||
1917 -- ' and site id ' || l_site_id || '.');
1918 else
1919 close cur_get_record3;
1920 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Process '|| p_process_name || ' for site id ' ||
1921 -- l_site_id || ' is attached to business group id ' || l_business_group_id ||
1922 -- ' in ota_iln_xml_processes table. Where as concurrent program is using ' ||
1923 -- ' business group id ' || l_2_business_group_id || '. Please Correct.');
1924
1925 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Site ID '|| l_site_id || ' is already mapped to the Business Group ID '||
1926 l_2_business_group_id || '.');
1927
1928 end if;
1929
1930 else
1931 close cur_get_record2;
1932 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Process '|| p_process_name || ' for business group id ' ||
1933 -- l_business_group_id || ' is attached to site id ' || l_site_id ||
1934 -- ' in ota_iln_xml_processes table. Where as concurrent program is using site id ' ||
1935 -- l_2_site_id || '. Please Correct.');
1936
1937 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Business Group ID '|| l_business_group_id || ' is already mapped to the Site ID '||
1938 l_2_site_id || '.');
1939 end if;
1940
1941 else
1942 close cur_get_record1;
1943
1944 l_status := 'S';
1945
1946
1947 -- if p_start_date = l_end_date then
1948 if pl_start_date = l_end_date then
1949
1950 /* --Do not need to update now. Created new procedure to update the table
1951 update
1952 ota_iln_xml_processes
1953 set
1954 from_date = p_start_date,
1955 to_date = p_end_date
1956 where
1957 executable_name = p_process_name and
1958 business_group_id = l_business_group_id and
1959 site_id = l_site_id;
1960
1961 */
1962
1963
1964 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
1965 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent program will update ota_iln_xml_processes table' ||
1966 -- ' with start and end date. ');
1967 else
1968 l_process_type := 'M';
1969 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent program will not update ota_iln_xml_processes' ||
1970 -- ' with start and end date. ');
1971 end if;
1972
1973 end if;
1974
1975 end if;
1976
1977 p_status := l_status;
1978 p_process_type := l_process_type;
1979
1980 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_status:' || p_status);
1981 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Exiting:' || l_proc);
1982
1983
1984 EXCEPTION
1985 when others then
1986 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:When others exception in procedure crt_or_chk_xml_prcs_tbl.');
1987 p_status := 'F';
1988
1989 end crt_or_chk_xml_prcs_tbl;
1990
1991
1992 procedure upd_xml_prcs_tbl (
1993 p_site_id in varchar2
1994 ,p_business_group_id in varchar2
1995 ,p_process_name in varchar2
1996 -- ,p_start_date in date
1997 -- ,p_end_date in date
1998 ,p_start_date in varchar2
1999 ,p_end_date in varchar2
2000 ,p_status out nocopy varchar2
2001 ) is
2002
2003 l_proc varchar2(72) := g_package||'upd_xml_prcs_tbl';
2004 l_business_group_id number := to_number(p_business_group_id);
2005 l_site_id number := to_number(p_site_id);
2006 l_start_date date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
2007 l_end_date date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS');
2008
2009 begin
2010 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering:' || l_proc);
2011
2012
2013 update
2014 ota_iln_xml_processes
2015 set
2016 from_date = l_start_date,
2017 to_date = l_end_date
2018 where
2019 executable_name = p_process_name and
2020 business_group_id = l_business_group_id and
2021 site_id = l_site_id;
2022
2023
2024 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ota_iln_xml_processes table updated successfully' ||
2025 -- ' with start and end date. ');
2026 p_status := 'S';
2027
2028 EXCEPTION
2029 when others then
2030 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR:When others exception in procedure upd_xml_prcs_tbl.');
2031 p_status := 'F';
2032
2033 end upd_xml_prcs_tbl;
2034
2035
2036 end OTA_ILEARNING;