[Home] [Help]
PACKAGE: APPS.OTA_LO_UTILITY
Source
1 package ota_lo_utility as
2 /* $Header: otloutil.pkh 120.21.12010000.4 2008/12/02 08:43:08 smahanka ship $ */
3
4
5 EVENT_REASON_NO_REASON constant number := 0;
6 EVENT_REASON_NO_SUCH_EVENT constant number := 1;
7 EVENT_REASON_EXPIRED constant number := 2;
8 EVENT_REASON_NOT_STARTED constant number := 3;
9 EVENT_REASON_NOT_ENROLLED constant number := 4;
10 EVENT_REASON_NOT_PUBLISHED constant number := 5;
11 EVENT_REASON_NOT_INSTRUCTOR constant number := 6;
12
13 LO_REASON_NO_REASON constant number := 0;
14 LO_REASON_NO_SUCH_LO constant number := 1;
15 LO_REASON_NO_STARTING_URL constant number := 2;
16 LO_REASON_PREREQS_NOT_MET constant number := 3;
17 LO_REASON_ATTEMPTS_EXCEEDED constant number := 4;
18 LO_REASON_NOT_IN_EVENT constant number := 5;
19 LO_REASON_NOT_PUBLISHED constant number := 6;
20 LO_REASON_UNKNOWN constant number := 7;
21 LO_REASON_EXPIRED constant number := 8;
22 LO_REASON_NOT_STARTED constant number := 9;
23 LO_REASON_LO_NOT_IN_CERT constant number := 10;
24 LO_REASON_DURATION_NOT_MET constant number := 11;
25
26 CERT_REASON_NO_REASON constant number := 0;
27 CERT_REASON_NOT_STARTED constant number := 1;
28 CERT_REASON_EXPIRED constant number := 2;
29 CERT_PRD_REASON_NOT_STARTED constant number := 3;
30 CERT_PRD_REASON_EXPIRED constant number := 4;
31 CERT_REASON_NO_SUCH_CERT constant number := 5;
32 CERT_REASON_UNSUBSCRIBED constant number := 6;
33 CERT_REASON_INVALID_USER constant number := 7;
34
35
36 procedure set_performance_lesson_status(
37 p_lo_id ota_learning_objects.learning_object_id%type,
38 p_user_id fnd_user.user_id%type,
39 p_user_type ota_attempts.user_type%type,
40 p_lesson_status ota_performances.lesson_status%type,
41 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type);
42
43
44 procedure set_performance_lesson_status(
45 p_lo_id ota_learning_objects.learning_object_id%type,
46 p_user_id fnd_user.user_id%type,
47 p_user_type ota_attempts.user_type%type,
48 p_lesson_status ota_performances.lesson_status%type,
49 p_date date,
50 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type);
51
52 procedure set_performance_time(
53 p_lo_id ota_learning_objects.learning_object_id%type,
54 p_user_id fnd_user.user_id%type,
55 p_user_type ota_attempts.user_type%type,
56 p_time ota_performances.time%type,
57 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type);
58
59
60 procedure set_performance_time(
61 p_lo_id ota_learning_objects.learning_object_id%type,
62 p_user_id fnd_user.user_id%type,
63 p_user_type ota_attempts.user_type%type,
64 p_time ota_performances.time%type,
65 p_date date,
66 p_cert_prd_enroll_id ota_performances.cert_prd_enrollment_id%type);
67
68
69 function get_previous_event_lo_id(
70 p_event_id ota_events.event_id%type,
71 p_starting_lo_id ota_learning_objects.learning_object_id%type,
72 p_user_id fnd_user.user_id%type,
73 p_user_type ota_attempts.user_type%type,
74 p_launch_type ota_attempts.launch_type%type,
75 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type;
76
77
78 function get_next_event_lo_id(
79 p_event_id ota_events.event_id%type,
80 p_starting_lo_id ota_learning_objects.learning_object_id%type,
81 p_user_id fnd_user.user_id%type,
82 p_user_type ota_attempts.user_type%type,
83 p_launch_type ota_attempts.launch_type%type,
84 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type;
85
86
87 function get_previous_lo_id(
88 p_root_lo_id ota_learning_objects.learning_object_id%type,
89 p_starting_lo_id ota_learning_objects.learning_object_id%type,
90 p_user_id fnd_user.user_id%type,
91 p_user_type ota_attempts.user_type%type,
92 p_launch_type ota_attempts.launch_type%type,
93 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type;
94
95
96 function get_next_lo_id(
97 p_root_lo_id ota_learning_objects.learning_object_id%type,
98 p_starting_lo_id ota_learning_objects.learning_object_id%type,
99 p_user_id fnd_user.user_id%type,
100 p_user_type ota_attempts.user_type%type,
101 p_launch_type ota_attempts.launch_type%type,
102 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type;
103
104
105 function get_next_lo_id(
106 p_root_lo_id ota_learning_objects.learning_object_id%type,
107 p_root_starting_url ota_learning_objects.starting_url%type,
108 p_starting_lo_id ota_learning_objects.learning_object_id%type,
109 p_user_id fnd_user.user_id%type,
110 p_user_type ota_attempts.user_type%type,
111 p_launch_type ota_attempts.launch_type%type,
112 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type;
113
114
115 function get_first_lo_id(
116 p_event_id ota_events.event_id%type,
117 p_user_id fnd_user.user_id%type,
118 p_user_type ota_attempts.user_type%type,
119 p_launch_type ota_attempts.launch_type%type,
120 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return ota_learning_objects.learning_object_id%type;
121
122
123 function get_most_recent_lo_id(
124 p_event_id ota_events.event_id%type,
125 p_user_id fnd_user.user_id%type,
126 p_user_type ota_attempts.user_type%type,
127 p_launch_type ota_attempts.launch_type%type,
128 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type) return ota_learning_objects.learning_object_id%type;
129
130
131 function get_jump_lo_id(
132 p_lo_id ota_learning_objects.learning_object_id%type,
133 p_event_id ota_events.event_id%type,
134 p_user_id fnd_user.user_id%type,
135 p_user_type ota_attempts.user_type%type,
136 p_launch_type ota_attempts.launch_type%type,
137 p_cert_prd_enroll_id ota_attempts.cert_prd_enrollment_id%type,
138 p_reason out nocopy number) return ota_learning_objects.learning_object_id%type;
139
140 function get_lo_type(
141 p_lo_id ota_learning_objects.learning_object_id%type) return varchar2;
142
143 function user_can_attempt_event(
144 p_event_id ota_events.event_id%type,
145 p_user_id fnd_user.user_id%type,
146 p_user_type ota_attempts.user_type%type,
147 p_launch_type ota_attempts.launch_type%type,
148 p_reason out nocopy number) return varchar2;
149
150
151 function user_can_attempt_event(
152 p_event_id ota_events.event_id%type,
153 p_user_id fnd_user.user_id%type,
154 p_user_type ota_attempts.user_type%type,
155 p_launch_type ota_attempts.launch_type%type) return varchar2;
156
157
158 function user_can_attempt_event(
159 p_event_id ota_events.event_id%type,
160 p_user_id fnd_user.user_id%type,
161 p_user_type ota_attempts.user_type%type) return varchar2;
162
163
164 function user_can_attempt_lo(
165 p_lo_id ota_learning_objects.learning_object_id%type,
166 p_user_id fnd_user.user_id%type,
167 p_user_type ota_attempts.user_type%type,
168 p_launch_type ota_attempts.launch_type%type default '',
169 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2;
170
171
172 function user_can_attempt_cert(
173 p_cert_prd_enroll_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
174 p_user_id fnd_user.user_id%type,
175 p_user_type ota_attempts.user_type%type,
176 p_reason out nocopy number) return varchar2;
177
178 -- Author: sbhullar
179 -- ----------------------------------------------------------------
180 -- ------------------<get_lo_title_for_tree >--------------------
181 -- ----------------------------------------------------------------
182 -- {Start of Comments}
183 --
184 -- Description:
185 -- This function will be used to show get lo name, status and time
186 -- in the format lo_name [Status: status, Time: hh:mm:ss] if p_mode
187 -- is 1 else it gives the lo status icon
188 -- IN
189 -- p_lo_id
190 -- p_user_id
191 -- p_user_type
192 -- p_mode
193 --
194 -- Post Failure:
195 -- None.
196 -- Access Status
197 -- Public
198 -- {End of Comments}
199 ------------------------------------------------------------------
200 FUNCTION get_lo_title_for_tree(p_lo_id IN NUMBER,
201 p_user_id IN NUMBER,
202 p_user_type IN ota_attempts.user_type%type,
203 p_mode IN NUMBER default 1,
204 p_active_cert_flag varchar2 default 'N')
205 RETURN varchar2;
206
207
208
209 function get_play_button(
210 p_user_id fnd_user.user_id%type,
211 p_user_type ota_attempts.user_type%type,
212 p_is_manager varchar2,
213 p_event_id ota_events.event_id%type,
214 p_event_type ota_events.event_type%type,
215 p_synchronous_flag ota_category_usages.synchronous_flag%type,
216 p_online_flag ota_category_usages.online_flag%type,
217 p_course_start_date ota_events.course_start_date%type,
218 p_course_end_date ota_events.course_end_date%type,
219 p_enrollment_status_type ota_booking_status_types.type%TYPE DEFAULT NULL,
220 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null,
221 p_contact_id ota_cert_enrollments.contact_id%type default null,
222 p_chk_active_cert_flag varchar2 default 'N') return varchar2;
223
224
225 function get_play_button_for_test(
226 p_user_id fnd_user.user_id%type,
227 p_user_type ota_attempts.user_type%type,
228 p_event_id ota_events.event_id%type,
229 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2;
230
231 --
232 -- ----------------------------------------------------------------------------
233 -- |-------------------------< get_wait_duration_for_test >--------------------
234 -- ----------------------------------------------------------------------------
235 -- Author: smanjuna
236 -- This function is used to get the timestamp until which the learner has to
237 -- wait before playing the test again. This is displayed as flyover text.
238 -- [End of Comments]
239 -- ----------------------------------------------------------------------------
240 function get_wait_duration_for_test(
241 p_user_id fnd_user.user_id%type,
242 p_user_type ota_attempts.user_type%type,
243 p_event_id ota_events.event_id%type,
244 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2;
245
246 --
247 -- ----------------------------------------------------------------------------
248 -- |-------------------------< get_wait_duration_for_lo >------------------
249 -- ----------------------------------------------------------------------------
250 -- Author: gdhutton
251 -- This function is used to get the date until which the learner has to
252 -- wait before playing the LO again.
253 -- [End of Comments]
254 -- ---------------------------------------------------------------------------
255 function get_wait_duration_for_lo(
256 p_user_id fnd_user.user_id%type,
257 p_user_type ota_attempts.user_type%type,
258 p_lo_id ota_learning_objects.learning_object_id%type,
259 p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return varchar2;
260
261 --
262 -- ----------------------------------------------------------------------------
263 -- |-------------------------< root_folder_exists>----------------------------|
264 -- ----------------------------------------------------------------------------
265 -- {Start Of Comments}
266 --
267 -- Description:
268 -- This function will check if root folder is existing for a business group.
269 --
270 --
271 -- Pre Conditions:
272 -- None.
273 --
274 -- Out Arguments:
275 -- p_folder_id
276 -- p_business_group_id
277 --
278 -- Post Success:
279 -- Processing continues.
280 --
281 --
282 -- Post Failure:
283 -- None.
284 --
285 -- Access Status:
286 -- Public.
287 --
288 -- {End Of Comments}
289 ----------------------------------------------------------------------------
290 --
291 function root_folder_exists
292 (p_folder_id in number default hr_api.g_number
293 ,p_business_group_id in number default ota_general.get_business_group_id
294 )
295 return varchar2;
296 --
297
298 -- ----------------------------------------------------------------------------
299 -- |-------------------------< get_enroll_lo_status >--------------------------|
300 -- ----------------------------------------------------------------------------
301 -- {Start Of Comments}
302 --
303 -- Description:
304 -- This function will fetch the player status for online classes and enrollment
305 -- status for offline classes.
306 --
307 -- Pre Conditions:
308 -- None.
309 --
310 -- Out Arguments:
311 -- p_user_id
312 -- p_user_type
313 -- p_event_id
314 -- p_booking_status_type_id
315 -- p_booking_id
316 -- p_mode
317 --
318 -- Post Success:
319 -- Processing continues.
320 --
321 --
322 -- Post Failure:
323 -- None.
324 --
325 -- Access Status:
326 -- Public.
327 --
328 -- {End Of Comments}
329 ----------------------------------------------------------------------------
330 --
331
332 FUNCTION get_enroll_lo_status(p_user_id IN NUMBER,
333 p_user_type IN ota_attempts.user_type%type,
334 p_event_id IN ota_events.event_id%TYPE,
335 p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
336 p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
337 p_mode IN number default null,
338 p_chk_active_cert_flag varchar2 default 'N')
339 RETURN VARCHAR2;
340 --
341 -- ----------------------------------------------------------------------------
342 -- |---------------------< get_history_button >-----------------------|
343 -- ----------------------------------------------------------------------------
344 -- {Start Of Comments}
345 --
346 -- Description:
347 -- This function will return a value based on which the Move to History
348 -- Button will be enabled. it will be enabled for online classes with a
349 -- performance status of Completed, Passed or Failed.
350 --
351 -- Pre Conditions:
352 -- None.
353 --
354 -- Out Arguments:
355 -- p_user_id
356 -- p_lo_id
357 -- p_event_id
358 -- p_booking_id
359 --
360 -- Post Success:
361 -- Processing continues.
362 --
363 --
364 -- Post Failure:
365 -- None.
366 --
367 -- Access Status:
368 -- Public.
369 --
370 -- {End Of Comments}
371 ----------------------------------------------------------------------------
372
373 FUNCTION get_history_button(p_user_id fnd_user.user_id%TYPE,
374 p_lo_id ota_learning_objects.learning_object_id%TYPE,
375 p_event_id ota_events.event_id%TYPE,
376 p_booking_id ota_delegate_bookings.booking_id%TYPE)
377 RETURN VARCHAR2;
378
379 FUNCTION get_lo_completion_date(p_event_id IN ota_events.event_id%type,
380 p_user_id IN NUMBER,
381 p_user_type IN ota_attempts.user_type%type,
382 p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
383 p_module_name IN VARCHAR2 default 'LEARNER')
384 RETURN varchar2;
385
386 -- ----------------------------------------------------------------------------
387 -- |-------------------------< get_cert_lo_status >--------------------------|
388 -- ----------------------------------------------------------------------------
389 -- {Start Of Comments}
390 --
391 -- Description:
392 -- This function will fetch the player status for online classes and enrollment
393 -- status for offline classes within the certification details.
394 --
395 -- Pre Conditions:
396 -- None.
397 --
398 -- Out Arguments:
399 -- p_user_id
400 -- p_user_type
401 -- p_event_id
402 -- p_booking_status_type_id
403 -- p_booking_id
404 -- p_cert_prd_enrollment_id
405 -- p_mode
406 --
407 -- Post Success:
408 -- Processing continues.
409 --
410 --
411 -- Post Failure:
412 -- None.
413 --
414 -- Access Status:
415 -- Public.
416 --
417 -- {End Of Comments}
418 ----------------------------------------------------------------------------
419
420 FUNCTION get_cert_lo_status(p_user_id IN NUMBER,
421 p_user_type IN ota_attempts.user_type%type,
422 p_event_id IN ota_events.event_id%TYPE,
423 p_booking_status_type_id IN ota_booking_status_types.booking_status_type_id%TYPE,
424 p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
425 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
426 p_mode IN number default null)
427 RETURN varchar2;
428
429 -- ----------------------------------------------------------------
430 -- ------------------<get_cert_lo_title_for_tree >--------------------
431 -- ----------------------------------------------------------------
432 -- {Start of Comments}
433 --
434 -- Description:
435 -- This function will be used within the certifications to show get
436 -- lo name, status and time in the format
437 -- lo_name [Status: status, Time: hh:mm:ss]
438 -- if p_mode is 1 else it gives the lo status icon
439 -- IN
440 -- p_lo_id
441 -- p_user_id
442 -- p_user_type
443 -- p_cert_prd_enrollment_id
444 -- p_mode
445 --
446 -- Post Failure:
447 -- None.
448 -- Access Status
449 -- Public
450 -- {End of Comments}
451 ------------------------------------------------------------------
452
453 FUNCTION get_cert_lo_title_for_tree(p_lo_id IN NUMBER,
454 p_user_id IN NUMBER,
455 p_user_type IN ota_attempts.user_type%type,
456 p_cert_prd_enrollment_id IN ota_performances.cert_prd_enrollment_id%type,
457 p_mode IN NUMBER default 1)
458 RETURN varchar2;
459
460 -- ----------------------------------------------------------------
461 -- ------------------<get_cme_online_event_id >--------------------
462 -- ----------------------------------------------------------------
463 -- {Start of Comments}
464 --
465 -- Description:
466 -- This function will be used within the certifications enrollment
467 -- details page to enable the player launch btn
468
469 -- IN
470 -- p_user_id
471 -- p_user_type
472 -- p_cert_mbr_enrollment_id
473 --
474 -- Post Failure:
475 -- None.
476 -- Access Status
477 -- Public
478 -- {End of Comments}
479 ------------------------------------------------------------------
480 function get_cme_online_event_id(p_user_id in fnd_user.user_id%type,
481 p_user_type in ota_attempts.user_type%type,
482 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
483 return varchar2;
484
485 -- ----------------------------------------------------------------
486 -- ------------------<get_cme_play_button >--------------------
487 -- ----------------------------------------------------------------
488 -- {Start of Comments}
489 --
490 -- Description:
491 -- This function will be used within the certifications enrollment
492 -- details page to enable the player launch btn
493
494 -- IN
495 -- p_user_id
496 -- p_user_type
497 -- p_is_manager
498 -- p_cert_mbr_enrollment_id
499 --
500 -- Post Failure:
501 -- None.
502 -- Access Status
503 -- Public
504 -- {End of Comments}
505 ------------------------------------------------------------------
506 function get_cme_play_button(p_user_id in fnd_user.user_id%type,
507 p_user_type in ota_attempts.user_type%type,
508 p_is_manager in varchar2,
509 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
510 return varchar2;
511
512 -- ----------------------------------------------------------------
513 -- ------------------<get_cme_player_toolbar_flag >--------------------
514 -- ----------------------------------------------------------------
515 -- {Start of Comments}
516 --
517 -- Description:
518 -- This function will be used within the certifications enrollment
519 -- details page to enable the player launch btn
520
521 -- IN
522 -- p_user_id
523 -- p_user_type
524 -- p_cert_mbr_enrollment_id
525 --
526 -- Post Failure:
527 -- None.
528 -- Access Status
529 -- Public
530 -- {End of Comments}
531 ------------------------------------------------------------------
532 function get_cme_player_toolbar_flag(p_user_id in fnd_user.user_id%type,
533 p_user_type in ota_attempts.user_type%type,
534 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
535 return varchar2;
536
537 -- ----------------------------------------------------------------
538 -- ------------------<get_cert_lo_status >--------------------
539 -- ----------------------------------------------------------------
540 -- {Start of Comments}
541 --
542 -- Description:
543 -- This function will be used within the certifications enrollment
544 -- details page to enable the player launch btn and show the perf status
545
546 -- IN
547 -- p_user_id
548 -- p_user_type
549 -- p_cert_mbr_enrollment_id
550 --
551 -- Post Failure:
552 -- None.
553 -- Access Status
554 -- Public
555 -- {End of Comments}
556 ------------------------------------------------------------------
557 function get_cert_lo_status(p_user_id in fnd_user.user_id%type,
558 p_user_type in ota_attempts.user_type%type,
559 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
560 return varchar2;
561
562
563 -- ----------------------------------------------------------------
564 -- ------------------<get_cme_onl_evt_count >--------------------
565 -- ----------------------------------------------------------------
566 -- {Start of Comments}
567 --
568 -- Description:
569 -- This function will be used within the certifications enrollment
570 -- details page to enable the player launch btn and show the perf status
571
572 -- IN
573 -- p_user_id
574 -- p_user_type
575 -- p_cert_mbr_enrollment_id
576 --
577 -- Post Failure:
578 -- None.
579 -- Access Status
580 -- Public
581 -- {End of Comments}
582 ------------------------------------------------------------------
583 function get_cme_onl_evt_count(p_user_id in fnd_user.user_id%type,
584 p_user_type in ota_attempts.user_type%type,
585 p_cert_mbr_enrollment_id in ota_cert_mbr_enrollments.cert_mbr_enrollment_id%type)
586 return varchar2;
587
588 -- ----------------------------------------------------------------
589 -- -----------------------< format_lo_time >-----------------------
590 -- ----------------------------------------------------------------
591 -- {Start of Comments}
592 --
593 -- Description:
594 -- This function formats time in HH:MM:SS format
595 --
596 -- IN
597 -- pTime
598 --
599 -- Post Failure:
600 -- None.
601 -- Access Status
602 -- Public
603 -- {End of Comments}
604 ------------------------------------------------------------------
605 Function format_lo_time(pTime ota_performances.time%type)
606 return varchar2;
607
608 -- ----------------------------------------------------------------
609 -- ------------------<get_lme_online_event_id >--------------------
610 -- ----------------------------------------------------------------
611 -- {Start of Comments}
612 --
613 -- Description:
614 -- This function will be used within the lp member enrollment
615 -- details page to enable the player launch btn
616
617 -- IN
618 -- p_user_id
619 -- p_user_type
620 -- p_lp_member_enrollment_id
621 --
622 -- Post Failure:
623 -- None.
624 -- Access Status
625 -- Public
626 -- {End of Comments}
627 ------------------------------------------------------------------
628 function get_lme_online_event_id(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
629 return varchar2;
630
631 -- ----------------------------------------------------------------
632 -- ------------------<get_lme_play_button >--------------------
633 -- ----------------------------------------------------------------
634 -- {Start of Comments}
635 --
636 -- Description:
637 -- This function will be used within the learning path enrollment
638 -- details page to enable the player launch btn
639
640 -- IN
641 -- p_user_id
642 -- p_user_type
643 -- p_is_manager
644 -- p_lp_member_enrollment_id
645 --
646 -- Post Failure:
647 -- None.
648 -- Access Status
649 -- Public
650 -- {End of Comments}
651 ------------------------------------------------------------------
652 function get_lme_play_button(p_user_id in fnd_user.user_id%type,
653 p_user_type in ota_attempts.user_type%type,
654 p_is_manager in varchar2,
655 p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
656 return varchar2;
657
658 -- ----------------------------------------------------------------
659 -- ------------------<get_lme_player_toolbar_flag >--------------------
660 -- ----------------------------------------------------------------
661 -- {Start of Comments}
662 --
663 -- Description:
664 -- This function will be used within the learning path enrollment
665 -- details page to enable the player launch btn
666
667 -- IN
668 -- p_lp_member_enrollment_id
669 --
670 -- Post Failure:
671 -- None.
672 -- Access Status
673 -- Public
674 -- {End of Comments}
675 ------------------------------------------------------------------
676 function get_lme_player_toolbar_flag(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
677 return varchar2;
678
679 -- ----------------------------------------------------------------
680 -- ------------------<get_lp_lo_status >--------------------
681 -- ----------------------------------------------------------------
682 -- {Start of Comments}
683 --
684 -- Description:
685 -- This function will be used within the learning path enrollment
686 -- details page to enable the player launch btn and show the perf status
687
688 -- IN
689 -- p_user_id
690 -- p_user_type
691 -- p_lp_member_enrollment_id
692 --
693 -- Post Failure:
694 -- None.
695 -- Access Status
696 -- Public
697 -- {End of Comments}
698 ------------------------------------------------------------------
699 function get_lpe_lo_status(p_user_id in fnd_user.user_id%type,
700 p_user_type in ota_attempts.user_type%type,
701 p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
702 return varchar2;
703
704
705 -- ----------------------------------------------------------------
706 -- ------------------<get_lme_onl_evt_count >--------------------
707 -- ----------------------------------------------------------------
708 -- {Start of Comments}
709 --
710 -- Description:
711 -- This function will be used within the learning path enrollment
712 -- details page to enable the player launch btn and show the perf status
713
714 -- IN
715 -- p_user_id
716 -- p_user_type
717 -- p_lp_member_enrollment_id
718 --
719 -- Post Failure:
720 -- None.
721 -- Access Status
722 -- Public
723 -- {End of Comments}
724 ------------------------------------------------------------------
725 function get_lme_onl_evt_count(p_lp_member_enrollment_id in ota_lp_member_enrollments.lp_member_enrollment_id%type)
726 return varchar2;
727
728 procedure get_active_cert_prds(
729 p_event_id ota_events.event_id%type,
730 p_person_id ota_cert_enrollments.contact_id%type,
731 p_contact_id ota_cert_enrollments.contact_id%type,
732 p_cert_prd_enrollment_ids OUT NOCOPY varchar2);
733
734 -- ----------------------------------------------------------------------------
735 -- |--------------------< LO_has_cld_and_no_strt_url>-------------------------|
736 -- ----------------------------------------------------------------------------
737 --
738 FUNCTION Lo_has_cld_and_no_strt_url
739 (p_learning_object_id in number default hr_api.g_number)
740 RETURN varchar2;
741
742 -- ----------------------------------------------------------------------------
743 -- |--------------------< GET_LO_COMPLETION_DATE_TIME>-------------------------|
744 -- ----------------------------------------------------------------------------
745
746 --Added for 6768606:COMPLETION DATE COLUMN SORT NUMERIC AND NOT BY ACTUAL DATE SORT
747
748 FUNCTION get_lo_completion_date_time(p_event_id IN ota_events.event_id%type,
749 p_user_id IN NUMBER,
750 p_user_type IN ota_attempts.user_type%type,
751 p_cert_prd_enroll_id IN ota_performances.cert_prd_enrollment_id%type default NULL,
752 p_module_name IN VARCHAR2 default 'LEARNER')
753 RETURN date;
754 -- ----------------------------------------------------------------------------
755 -- |-------------------------< get_enroll_lo_score >--------------------------|
756 -- ----------------------------------------------------------------------------
757 -- {Start Of Comments}
758 --
759 -- Description:
760 -- This function will fetch the player score for online classes and enrollment
761 -- score for offline classes.
762 --
763 -- Pre Conditions:
764 -- None.
765 --
766 -- Out Arguments:
767 -- p_user_id
768 -- p_user_type
769 -- p_event_id
770 -- p_booking_id
771 --
772 -- Post Success:
773 -- Processing continues.
774 --
775 --
776 -- Post Failure:
777 -- None.
778 --
779 -- Access Status:
780 -- Public.
781 --
782 -- {End Of Comments}
783 ----------------------------------------------------------------------------
784 --
785 FUNCTION get_enroll_lo_score(p_user_id IN NUMBER,
786 p_user_type IN ota_attempts.user_type%type,
787 p_event_id IN ota_events.event_id%TYPE,
788 p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
789 RETURN VARCHAR2;
790 --
791 -- ----------------------------------------------------------------------------
792 -- |-------------------------< get_enroll_lo_time >--------------------------|
793 -- ----------------------------------------------------------------------------
794 -- {Start Of Comments}
795 --
796 -- Description:
797 -- This function will fetch the player time for online classes and enrollment
798 -- training time for offline classes.
799 --
800 -- Pre Conditions:
801 -- None.
802 --
803 -- Out Arguments:
804 -- p_user_id
805 -- p_user_type
806 -- p_event_id
807 -- p_booking_id
808 --
809 -- Post Success:
810 -- Processing continues.
811 --
812 --
813 -- Post Failure:
814 -- None.
815 --
816 -- Access Status:
817 -- Public.
818 --
819 -- {End Of Comments}
820 ----------------------------------------------------------------------------
821 --
822 FUNCTION get_enroll_lo_time(p_user_id IN NUMBER,
823 p_user_type IN ota_attempts.user_type%type,
824 p_event_id IN ota_events.event_id%TYPE,
825 p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
826 RETURN VARCHAR2;
827
828 -- ----------------------------------------------------------------------------
829 -- |-------------------------< get_player_status >----------------------------|
830 -- ----------------------------------------------------------------------------
831 -- {Start Of Comments}
832 --
833 -- Description:
834 -- This function will fetch the player staus code for online classes and enrollment
835 -- content player status for offline classes.
836 --
837 -- Pre Conditions:
838 -- None.
839 --
840 -- Out Arguments:
841 -- p_user_id
842 -- p_user_type
843 -- p_event_id
844 -- p_booking_id
845 --
846 -- Post Success:
847 -- Processing continues.
848 --
849 --
850 -- Post Failure:
851 -- None.
852 --
853 -- Access Status:
854 -- Public.
855 --
856 -- {End Of Comments}
857 ----------------------------------------------------------------------------
858 --
859 FUNCTION get_player_status(p_user_id IN NUMBER,
860 p_user_type IN ota_attempts.user_type%type,
861 p_event_id IN ota_events.event_id%TYPE,
862 p_booking_id IN ota_delegate_bookings.booking_id%TYPE)
863 RETURN VARCHAR2;
864
865 function get_play_eval_button(
866 p_event_id OTA_EVENTS.EVENT_ID%TYPE,
867 p_user_id fnd_user.user_id%type,
868 p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
869 p_object_id OTA_EVALUATIONS.OBJECT_ID%TYPE,
870 p_object_type OTA_EVALUATIONS.OBJECT_TYPE%TYPE,
871 p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
872 p_test_id OTA_TESTS.TEST_ID%TYPE)
873 return varchar2;
874
875 procedure update_enrollment_status(
876 p_user_id fnd_user.user_id%type,
877 p_user_type ota_attempts.user_type%type,
878 p_event_id ota_events.event_id%type);
879
880 procedure update_enroll_status_for_lo(
881 p_lo_id ota_learning_objects.learning_object_id%type,
882 p_user_id fnd_user.user_id%type,
883 p_user_type ota_attempts.user_type%type,
884 p_date date,
885 p_failed varchar2);
886
887 procedure update_enrollment(
888 p_booking_id ota_delegate_bookings.booking_id%type,
889 p_event_id ota_events.event_id%type,
890 p_business_group_id ota_delegate_bookings.business_group_id%type,
891 p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
892 p_object_version_number ota_delegate_bookings.object_version_number%type,
893 p_date_status_changed ota_delegate_bookings.date_status_changed%type,
894 p_new_status varchar2,
895 p_failed varchar2);
896
897 --Enhancement: 7310093 SIP: A NEW FIELD WHICH CAN GIVE THE STATUS OF THE COURSE / CLASS EVALUATION
898 function get_admin_eval_status(
899 p_event_id OTA_EVENTS.EVENT_ID%TYPE,
900 p_user_id fnd_user.user_id%type,
901 p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
902 p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
903 p_test_id OTA_TESTS.TEST_ID%TYPE)
904 return varchar2;
905
906 end ota_lo_utility;