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