[Home] [Help]
PACKAGE BODY: APPS.OTA_AME_ATTRIBUTES
Source
4 --
1 PACKAGE BODY ota_ame_attributes AS
2 /* $Header: otamewkf.pkb 120.1.12010000.2 2009/07/07 06:06:58 pekasi ship $ */
3
5 -- Package Variables
6 --
7 g_package varchar2(33) := 'ota_ame_attributes.';
8 --
9
10 -------------------------------------------------------------------------------
11 --------- function get_item_type --------------------------------------------
12
13 ---------- private function to get item type for current transaction ---------
14 -------------------------------------------------------------------------------
15 function get_item_type
16 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
17 return varchar2 is
18 c_item_type varchar2(50);
19
20 begin
21
22 SELECT DISTINCT ITEM_TYPE
23 INTO c_item_type
24 FROM HR_API_TRANSACTION_STEPS
25 WHERE TRANSACTION_ID=p_transaction_id;
26
27 return nvl(c_item_type, '-1');
28 EXCEPTION
29 WHEN OTHERS THEN
30 WF_CORE.CONTEXT(g_package,'.get_item_key',p_transaction_id);
31 RAISE;
32
33 end get_item_type;
34
35 -------------------------------------------------------------------------------
36 --------- function get_item_key --------------------------------------------
37 ---------- private function to get item key for current transaction ---------
38 -------------------------------------------------------------------------------
39 function get_item_key
40 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
41 return varchar2 is
42 c_item_key varchar2(100);
43
44 begin
45
46 SELECT DISTINCT ITEM_KEY
47 INTO c_item_key
48 FROM HR_API_TRANSACTION_STEPS
49 WHERE TRANSACTION_ID=p_transaction_id;
50
51 return nvl(c_item_key, '-1');
52 EXCEPTION
53 WHEN OTHERS THEN
54 WF_CORE.CONTEXT(g_package,'.get_item_key',p_transaction_id);
55 RAISE;
56 end get_item_key;
57
58
59
60 -- ------------------------------------------------------------------------
61 -- |------------------------< get_class_standard_price >-------------------------|
62 -- ------------------------------------------------------------------------
63 --
64 -- Description
65 --
66 -- Get the class standard price
67 --
68
69 function get_class_standard_price
70 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
71 return number is
72 c_class_standard_price ota_events.standard_price%type;
73 c_event_id ota_events.event_id%type;
74 c_item_type varchar2(50);
75 c_item_key varchar2(100);
76
77 cursor c_get_standard_price is
78 select standard_price
79 from ota_events
80 where event_id = c_event_id;
81
82 begin
83
84 c_item_type := get_item_type(p_transaction_id);
85 c_item_key := get_item_key(p_transaction_id);
86
87 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
88 itemkey => c_item_key,
89 aname => 'OTA_EVENT_ID'
90 , ignore_notfound => TRUE);
91
92 IF c_event_id is null then
93
94 c_class_standard_price := null;
95
96 else
97
98 open c_get_standard_price;
99 fetch c_get_standard_price into c_class_standard_price;
100 close c_get_standard_price;
101 end if;
102 return c_class_standard_price;
103 EXCEPTION
104 WHEN OTHERS THEN
105 WF_CORE.CONTEXT(g_package,'get_class_standard_price',c_item_type,c_item_key);
106 RAISE;
107
108
109 end get_class_standard_price;
110
111 -- ------------------------------------------------------------------------
112 -- |------------------------< get_Learning_path_name >----------------|
113 -- ------------------------------------------------------------------------
114 --
115 -- Description
116 --
117 -- Get the learning path name
118 --
119 function get_Learning_path_name
120 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
121 return varchar2 is
122
123 c_lp_name ota_learning_paths_tl.name%type;
124 c_event_id ota_events.event_id%type;
125 c_item_type varchar2(50);
126 c_item_key varchar2(100);
127
128 l_lp_name ota_learning_paths_tl.name%type;
129
130 /*cursor c_get_lp_name is
131 select olpt.name
132 from ota_events oev,ota_training_plans otp, ota_training_plan_members otpm,
133 ota_learning_paths_tl olpt
134 where oev.activity_version_id = otpm.activity_version_id
135 and otpm.member_status_type_id='OTA_COMPLETED'
136 and otpm.training_plan_id=otp.training_plan_id
137 and otp.plan_status_type_id='OTA_COMPLETED'
138 and otp.learning_path_id= olpt.learning_path_id
139 and olpt.Language=USERENV('LANG')
140 and oev.event_id=c_event_id;*/
141
142 begin
143
144 c_item_type := get_item_type(p_transaction_id);
145 c_item_key := get_item_key(p_transaction_id);
146
147 /*c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
148 itemkey => c_item_key,
149 aname => 'OTA_EVENT_ID'
150 , ignore_notfound => TRUE);
151 */
155 , ignore_notfound => TRUE);
152 l_lp_name := wf_engine.getitemattrText(itemtype => c_item_type ,
153 itemkey => c_item_key,
154 aname => 'OTA_ACTIVITY_VERSION_NAME'
156 c_lp_name := l_lp_name;
157 /*
158 IF c_event_id is null then
159 c_lp_name := null;
160 else
161 for rec in c_get_lp_name
162 Loop
163 if rec.name = l_lp_name then
164 c_lp_name := rec.name;
165 exit;
166 end if;
167
168 end loop;
169 end if;*/
170 return c_lp_name;
171 EXCEPTION
172 WHEN OTHERS THEN
173 WF_CORE.CONTEXT(g_package,'get_Learning_path_name',c_item_type,c_item_key);
174 RAISE;
175 end get_Learning_path_name;
176
177 -- ------------------------------------------------------------------------
178 -- |------------------------< get_course_primary_category >----------------|
179 -- ------------------------------------------------------------------------
180 --
181 -- Description
182 --
183 -- Get the course primary category
184 --
185 function get_course_primary_category
186 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
187 return varchar2 is
188
189 c_course_category ota_category_usages_tl.category%type;
190 c_event_id ota_events.event_id%type;
191 c_item_type varchar2(50);
192 c_item_key varchar2(100);
193
194 cursor c_get_course_category is
195 Select ocu.Category
196 From ota_act_cat_inclusions oaci,
197 ota_category_usages_vl ocu,
198 ota_events oev,
199 ota_offerings ofr
200 Where oev.event_id = c_event_id
201 AND oev.parent_offering_id = ofr.offering_id
202 AND ofr.activity_version_id = oaci.activity_version_id
203 AND ocu.category_usage_id = oaci.category_usage_id
204 AND oaci.primary_flag = 'Y'
205 AND ocu.type = 'C'
206 AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(oaci.start_date_active), TRUNC(sysdate)) AND nvl(TRUNC(oaci.end_date_active), TRUNC(sysdate)))
207 AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(ocu.start_date_active), TRUNC(sysdate)) AND nvl(TRUNC(ocu.end_date_active), TRUNC(sysdate)));
208
209 begin
210
211 c_item_type := get_item_type(p_transaction_id);
212 c_item_key := get_item_key(p_transaction_id);
213
214 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
215 itemkey => c_item_key,
216 aname => 'OTA_EVENT_ID'
217 , ignore_notfound => TRUE);
218
219 IF c_event_id is null then
220
221 c_course_category := null;
222
223 else
224 open c_get_course_category;
225 fetch c_get_course_category into c_course_category;
226 close c_get_course_category;
227 end if;
228 return c_course_category;
229 EXCEPTION
230 WHEN OTHERS THEN
231 WF_CORE.CONTEXT(g_package,'get_course_primary_category',c_item_type,c_item_key);
232 RAISE;
233
234 end get_course_primary_category;
235
236 -- ------------------------------------------------------------------------
237 -- |------------------------< get_enrollment_status >-------------------------|
238 -- ------------------------------------------------------------------------
239 --
240 -- Description
241 --
242 -- Get the enrollment status
243 --
244 function get_enrollment_status
245 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
246 return varchar2 is
247
248 c_enrollment_status ota_booking_status_types.name%type;
249 c_booking_id ota_delegate_bookings.booking_id%type;
250 c_item_type varchar2(50);
251 c_item_key varchar2(100);
252
253 cursor c_get_enrollment_status is
254 select
255 bst.name
256 from
257 ota_delegate_bookings tdb,
258 ota_booking_status_types_tl bst
259 where tdb.booking_id = c_booking_id and
260 bst.language=userenv('LANG') and
261 tdb.booking_status_type_id = bst.booking_status_type_id;
262
263 begin
264
265 c_item_type := get_item_type(p_transaction_id);
266 c_item_key := get_item_key(p_transaction_id);
267
268 c_booking_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
269 itemkey => c_item_key,
270 aname => 'BOOKING_ID'
271 , ignore_notfound => TRUE);
272
273 IF c_booking_id is null then
274
275 c_enrollment_status := null;
276
277 else
278 open c_get_enrollment_status;
279 fetch c_get_enrollment_status into c_enrollment_status;
280 close c_get_enrollment_status;
281 end if;
282 return c_enrollment_status;
283 EXCEPTION
284 WHEN OTHERS THEN
285 WF_CORE.CONTEXT(g_package,'get_enrollment_status',c_item_type,c_item_key);
286 RAISE;
287
288 end get_enrollment_status;
289
290 -- ------------------------------------------------------------------------
291 -- |------------------------< get_ofr_delivery_mode >-------------------------|
292 -- ------------------------------------------------------------------------
293 --
294 -- Description
295 --
296 -- Get the delivery mode for the offering
297 --
298 function get_ofr_delivery_mode
299 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
300 return varchar2 is
301
302 c_delivery_mode ota_category_usages_tl.category%type;
303 c_event_id ota_events.event_id%type;
304 c_offering_id ota_offerings.offering_id%type;
305 c_item_type varchar2(50);
306 c_item_key varchar2(100);
307
311 ota_category_usages_vl ocu
308 cursor c_get_ofr_dm is
309 Select ocu.Category
310 From ota_offerings ofr,
312 Where ofr.offering_id = c_offering_id
313 AND ofr.delivery_mode_id = ocu.category_usage_id
314 AND ocu.type = 'DM'
315 AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(ocu.start_date_active), TRUNC(sysdate))
316 AND nvl(TRUNC(ocu.end_date_active), TRUNC(sysdate)));
317
318 cursor c_get_ofr_id is
319 Select parent_offering_id
320 From ota_events
321 Where event_id = c_event_id;
322 begin
323
324 c_item_type := get_item_type(p_transaction_id);
325 c_item_key := get_item_key(p_transaction_id);
326
327 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
328 itemkey => c_item_key,
329 aname => 'OTA_EVENT_ID'
330 , ignore_notfound => TRUE);
331
332 IF c_event_id is null then
333
334 c_delivery_mode := null;
335
336 else
337 open c_get_ofr_id;
338 fetch c_get_ofr_id into c_offering_id;
339 close c_get_ofr_id;
340
341 open c_get_ofr_dm;
342 fetch c_get_ofr_dm into c_delivery_mode;
343 close c_get_ofr_dm;
344 end if;
345 return c_delivery_mode;
346 EXCEPTION
347 WHEN OTHERS THEN
348 WF_CORE.CONTEXT(g_package,'get_ofr_delivery_mode',c_item_type,c_item_key);
349 RAISE;
350
351 end get_ofr_delivery_mode;
352
353 -- ------------------------------------------------------------------------
354 -- |------------------------< get_course_name >----------------|
355 -- ------------------------------------------------------------------------
356 --
357 -- Description
358 --
359 -- Get the course name
360 --
361 function get_course_name
362 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
363 return varchar2 is
364
365 c_course_name ota_activity_versions_tl.version_name%type;
366 c_event_id ota_events.event_id%type;
367 c_item_type varchar2(50);
368 c_item_key varchar2(100);
369
370 cursor c_get_course_name is
371 Select oav.version_name
372 From ota_events oev,
373 ota_activity_versions_tl oav
374 Where oev.activity_version_id = oav.activity_version_id
375 And oav.language = USERENV ('LANG')
376 And oev.event_id = c_event_id;
377 begin
378
379 c_item_type := get_item_type(p_transaction_id);
380 c_item_key := get_item_key(p_transaction_id);
381
382 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
383 itemkey => c_item_key,
384 aname => 'OTA_EVENT_ID'
385 , ignore_notfound => TRUE);
386
387 IF c_event_id is null then
388 c_course_name := null;
389 else
390 open c_get_course_name;
391 fetch c_get_course_name into c_course_name;
392 close c_get_course_name;
393 end if;
394 return c_course_name;
395 EXCEPTION
396 WHEN OTHERS THEN
397 WF_CORE.CONTEXT(g_package,'get_course_name',c_item_type,c_item_key);
398 RAISE;
399
400 end get_course_name;
401
402 -- ------------------------------------------------------------------------
403 -- |------------------------< get_offering_name >----------------|
404 -- ------------------------------------------------------------------------
405 --
406 -- Description
407 --
408 -- Get the offering name
409 --
410 function get_offering_name
411 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
412 return varchar2 is
413
414 c_offering_name ota_offerings_tl.name%type;
415 c_event_id ota_events.event_id%type;
416 c_item_type varchar2(50);
417 c_item_key varchar2(100);
418
419 cursor c_get_offering_name is
420 Select ofr.name
421 From ota_events oev,
422 ota_offerings_tl ofr
423 Where oev.parent_offering_id = ofr.offering_id
424 And ofr.language = USERENV ('LANG')
425 And oev.event_id = c_event_id;
426 begin
427
428 c_item_type := get_item_type(p_transaction_id);
429 c_item_key := get_item_key(p_transaction_id);
430
431 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
432 itemkey => c_item_key,
433 aname => 'OTA_EVENT_ID'
434 , ignore_notfound => TRUE);
435
436 IF c_event_id is null then
437 c_offering_name := null;
438 else
439 open c_get_offering_name;
440 fetch c_get_offering_name into c_offering_name;
441 close c_get_offering_name;
442 end if;
443 return c_offering_name;
444 EXCEPTION
445 WHEN OTHERS THEN
446 WF_CORE.CONTEXT(g_package,'get_offering_name',c_item_type,c_item_key);
447 RAISE;
448
449 end get_offering_name;
450
451 -- ------------------------------------------------------------------------
452 -- |------------------------< get_class_name >----------------|
453 -- ------------------------------------------------------------------------
454 --
455 -- Description
456 --
457 -- Get the class name
458 --
459 function get_class_name
460 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
461 return varchar2 is
462
463 c_class_name ota_events_tl.title%type;
464 c_event_id ota_events.event_id%type;
468 cursor c_get_class_name is
465 c_item_type varchar2(50);
466 c_item_key varchar2(100);
467
469 Select oev.title
470 From ota_events_tl oev
471 Where oev.language = USERENV ('LANG')
472 And oev.event_id = c_event_id;
473 begin
474
475 c_item_type := get_item_type(p_transaction_id);
476 c_item_key := get_item_key(p_transaction_id);
477
478 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
479 itemkey => c_item_key,
480 aname => 'OTA_EVENT_ID'
481 , ignore_notfound => TRUE);
482
483 IF c_event_id is null then
484 c_class_name := null;
485 else
486 open c_get_class_name;
487 fetch c_get_class_name into c_class_name;
488 close c_get_class_name;
489 end if;
490 return c_class_name;
491 EXCEPTION
492 WHEN OTHERS THEN
493 WF_CORE.CONTEXT(g_package,'get_class_name',c_item_type,c_item_key);
494 RAISE;
495
496 end get_class_name;
497
498 -- ------------------------------------------------------------------------
499 -- |------------------------< get_class_location >----------------|
500 -- ------------------------------------------------------------------------
501 --
502 -- Description
503 --
504 -- Get the class location
505 --
506 function get_class_location
507 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
508 return varchar2 is
509
510 c_class_location hr_locations_all.location_code%type;
511 c_event_id ota_events.event_id%type;
512 c_item_type varchar2(50);
513 c_item_key varchar2(100);
514
515 cursor c_get_class_location is
516 Select ota_general.get_location_code(ota_utility.get_event_location(oev.event_id)) Location_Name
517 From ota_events oev
518 Where oev.event_id = c_event_id;
519
520 begin
521
522 c_item_type := get_item_type(p_transaction_id);
523 c_item_key := get_item_key(p_transaction_id);
524
525 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
526 itemkey => c_item_key,
527 aname => 'OTA_EVENT_ID'
528 , ignore_notfound => TRUE);
529
530 IF c_event_id is null then
531 c_class_location := null;
532 else
533 open c_get_class_location;
534 fetch c_get_class_location into c_class_location;
535 close c_get_class_location;
536 end if;
537 return c_class_location;
538 EXCEPTION
539 WHEN OTHERS THEN
540 WF_CORE.CONTEXT(g_package,'get_class_location',c_item_type,c_item_key);
541 RAISE;
542
543 end get_class_location;
544
545 -- ------------------------------------------------------------------------
546 -- |------------------------< get_certification_name >----------------|
547 -- ------------------------------------------------------------------------
548 --
549 -- Description
550 --
551 -- Get the certification name
552
553 function get_certification_name
554 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
555 return varchar2 is
556
557 c_certification_name ota_certifications_tl.name%type;
558 --c_certification_id ota_events.event_id%type;
559 c_item_type varchar2(50);
560 c_item_key varchar2(100);
561
562
563 begin
564
565 c_item_type := get_item_type(p_transaction_id);
566 c_item_key := get_item_key(p_transaction_id);
567
568 c_certification_name := wf_engine.GetItemAttrText(itemtype => c_item_type ,
569 itemkey => c_item_key,
570 aname => 'OTA_ACTIVITY_VERSION_NAME'
571 , ignore_notfound => TRUE);
572
573
574 return c_certification_name;
575 EXCEPTION
576 WHEN OTHERS THEN
577 WF_CORE.CONTEXT(g_package,'get_certification_name',c_item_type,c_item_key);
578 RAISE;
579
580 end get_certification_name;
581
582
583 -- ------------------------------------------------------------------------
584 -- |------------------------< get_certification_type >----------------|
585 -- ------------------------------------------------------------------------
586 --
587
588 function get_certification_type
589 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
590 return varchar2 is
591
592 c_certification_type varchar2(100);
593 c_certification_id ota_certifications_b.certification_id%type;
594 c_item_type varchar2(50);
595 c_item_key varchar2(100);
596
597 cursor get_certification_type
598 is
599 select renewable_flag from ota_certifications_b
600 where certification_id = c_certification_id;
601
602
603 begin
604
605 c_item_type := get_item_type(p_transaction_id);
606 c_item_key := get_item_key(p_transaction_id);
607
608 c_certification_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
609 itemkey => c_item_key,
610 aname => 'OTA_CERTIFICATION_ID'
611 , ignore_notfound => TRUE);
612
613 if c_certification_id is not null then
614
615 open get_certification_type;
616 fetch get_certification_type into c_certification_type;
617 close get_certification_type;
618
619 end if;
620
621
622 return c_certification_type;
623 EXCEPTION
624 WHEN OTHERS THEN
625 WF_CORE.CONTEXT(g_package,'get_certification_type',c_item_type,c_item_key);
626 RAISE;
627
628 end get_certification_type;
632 -- |------------------------< get_init_cert_dur >----------------|
629
630
631 -- ------------------------------------------------------------------------
633 -- ------------------------------------------------------------------------
634
635 --
636 function get_init_cert_dur
637 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
638 return varchar2 is
639
640 c_cert_dur varchar2(100);
641 c_certification_id ota_certifications_b.certification_id%type;
642 c_item_type varchar2(50);
643 c_item_key varchar2(100);
644
645 cursor get_cert_dur
646 is
647 select initial_completion_duration from ota_certifications_b
648 where certification_id = c_certification_id;
649
650
651 begin
652
653 c_item_type := get_item_type(p_transaction_id);
654 c_item_key := get_item_key(p_transaction_id);
655
656 c_certification_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
657 itemkey => c_item_key,
658 aname => 'OTA_CERTIFICATION_ID'
659 , ignore_notfound => TRUE);
660
661 if c_certification_id is not null then
662
663 open get_cert_dur;
664 fetch get_cert_dur into c_cert_dur;
665 close get_cert_dur;
666
667 end if;
668
669
670 return c_cert_dur;
671 EXCEPTION
672 WHEN OTHERS THEN
673 WF_CORE.CONTEXT(g_package,'get_init_cert_dur',c_item_type,c_item_key);
674 RAISE;
675
676 end get_init_cert_dur;
677
678
679 -- ------------------------------------------------------------------------
680 -- |------------------------< get_renewal_duration >----------------|
681 -- ------------------------------------------------------------------------
682
683 --
684 function get_renewal_duration
685 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
686 return varchar2 is
687
688 c_cert_dur varchar2(100);
689 c_certification_id ota_certifications_b.certification_id%type;
690 c_item_type varchar2(50);
691 c_item_key varchar2(100);
692
693 cursor get_cert_renewal_duration
694 is
695 select renewal_duration from ota_certifications_b
696 where certification_id = c_certification_id;
697
698
699 begin
700
701 c_item_type := get_item_type(p_transaction_id);
702 c_item_key := get_item_key(p_transaction_id);
703
704 c_certification_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
705 itemkey => c_item_key,
706 aname => 'OTA_CERTIFICATION_ID'
707 , ignore_notfound => TRUE);
708
709 if c_certification_id is not null then
710
711 open get_cert_renewal_duration;
712 fetch get_cert_renewal_duration into c_cert_dur;
713 close get_cert_renewal_duration;
714
715 end if;
716
717
718 return c_cert_dur;
719 EXCEPTION
720 WHEN OTHERS THEN
721 WF_CORE.CONTEXT(g_package,'get_renewal_duration',c_item_type,c_item_key);
722 RAISE;
723
724 end get_renewal_duration;
725
726 -- ------------------------------------------------------------------------
727 -- |------------------------< get_validity_duration >----------------|
728 -- ------------------------------------------------------------------------
729
730 --
731 function get_validity_duration
732 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
733 return varchar2 is
734
735 c_cert_dur varchar2(100);
736 c_certification_id ota_certifications_b.certification_id%type;
737 c_item_type varchar2(50);
738 c_item_key varchar2(100);
739
740 cursor get_cert_validity_duration
741 is
742 select validity_duration from ota_certifications_b
743 where certification_id = c_certification_id;
744
745
746 begin
747
748 c_item_type := get_item_type(p_transaction_id);
749 c_item_key := get_item_key(p_transaction_id);
750
751 c_certification_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
752 itemkey => c_item_key,
753 aname => 'OTA_CERTIFICATION_ID'
754 , ignore_notfound => TRUE);
755
756 if c_certification_id is not null then
757
758 open get_cert_validity_duration;
759 fetch get_cert_validity_duration into c_cert_dur;
760 close get_cert_validity_duration;
761
762 --ame_util
763 --versionDateToString
764
765 end if;
766
767
768 return c_cert_dur;
769 EXCEPTION
770 WHEN OTHERS THEN
771 WF_CORE.CONTEXT(g_package,'get_validity_duration',c_item_type,c_item_key);
772 RAISE;
773
774 end get_validity_duration;
775
776
777 -- ------------------------------------------------------------------------
778 -- |------------------------< get_cert_period_start_date >----------------|
779 -- ------------------------------------------------------------------------
780
781 --
782 function get_cert_period_start_date
783 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
784 return varchar2 is
785
786 c_cert_date Date;
787 l_return_value varchar2(100);
788 c_certification_id ota_certifications_b.certification_id%type;
789 c_item_type varchar2(50);
790 c_item_key varchar2(100);
791 c_person_id number(15);
792
793 cursor get_cert_period_date
794 is
795 select cpe.cert_period_start_date
796 from ota_cert_prd_enrollments cpe , ota_cert_enrollments ce
800 and ce.person_id = c_person_id
797 where
798 ce.cert_enrollment_id = cpe.cert_enrollment_id
799 and ce.certification_id = c_certification_id
801 and cpe.period_status_code = 'CANCELLED'
802 and rownum =1
803 order by cert_period_start_date desc;
804
805
806 begin
807
808 c_item_type := get_item_type(p_transaction_id);
809 c_item_key := get_item_key(p_transaction_id);
810
811 c_certification_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
812 itemkey => c_item_key,
813 aname => 'OTA_CERTIFICATION_ID'
814 , ignore_notfound => TRUE);
815
816 c_person_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
817 itemkey => c_item_key,
818 aname => 'CURRENT_PERSON_ID'
819 , ignore_notfound => TRUE);
820
821 if c_certification_id is not null and c_person_id is not null then
822
823 open get_cert_period_date;
824 fetch get_cert_period_date into c_cert_date;
825 close get_cert_period_date;
826
827 if c_Cert_date is not null then
828
829 l_return_value := ame_util.versiondateToString(c_cert_date);
830 end if;
831 end if;
832
833
834 return l_return_value;
835 EXCEPTION
836 WHEN OTHERS THEN
837 WF_CORE.CONTEXT(g_package,'get_cert_period_start_date',c_item_type,c_item_key);
838 RAISE;
839
840 end get_cert_period_start_date;
841
842 -- ------------------------------------------------------------------------
843 -- |------------------------< get_cert_period_end_date >----------------|
844 -- ------------------------------------------------------------------------
845
846 --
847 function get_cert_period_end_date
848 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
849 return varchar2 is
850
851 c_cert_date Date;
852 l_return_value varchar2(100);
853 c_certification_id ota_certifications_b.certification_id%type;
854 c_item_type varchar2(50);
855 c_item_key varchar2(100);
856 c_person_id number(15);
857
858 cursor get_cert_period_date
859 is
860 select cpe.cert_period_end_date
861 from ota_cert_prd_enrollments cpe , ota_cert_enrollments ce
862 where
863 ce.cert_enrollment_id = cpe.cert_enrollment_id
864 and ce.certification_id = c_certification_id
865 and ce.person_id = c_person_id
866 and cpe.period_status_code = 'CANCELLED'
867 and rownum =1
868 order by cert_period_start_date desc;
869
870
871 begin
872
873 c_item_type := get_item_type(p_transaction_id);
874 c_item_key := get_item_key(p_transaction_id);
875
876 c_certification_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
877 itemkey => c_item_key,
878 aname => 'OTA_CERTIFICATION_ID'
879 , ignore_notfound => TRUE);
880
881 c_person_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
882 itemkey => c_item_key,
883 aname => 'CURRENT_PERSON_ID'
884 , ignore_notfound => TRUE);
885
886 if c_certification_id is not null and c_person_id is not null then
887
888 open get_cert_period_date;
889 fetch get_cert_period_date into c_cert_date;
890 close get_cert_period_date;
891
892 if c_Cert_date is not null then
893
894 l_return_value := ame_util.versiondateToString(c_cert_date);
895 end if;
896 end if;
897
898
899 return l_return_value;
900 EXCEPTION
901 WHEN OTHERS THEN
902 WF_CORE.CONTEXT(g_package,'get_cert_period_end_date',c_item_type,c_item_key);
903 RAISE;
904
905 end get_cert_period_end_date;
906
907 -- ------------------------------------------------------------------------
908 -- |------------------------< get_init_cert_comp_date >----------------|
909 -- ------------------------------------------------------------------------
910
911 --
912 function get_init_cert_comp_date
913 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
914 return varchar2 is
915
916 c_cert_date Date;
917 l_return_value varchar2(100);
918 c_certification_id ota_certifications_b.certification_id%type;
919 c_item_type varchar2(50);
920 c_item_key varchar2(100);
921 c_person_id number(15);
922
923 cursor get_cert_comp_date
924 is
925 select INITIAL_COMPLETION_DATE
926 from ota_certifications_b
927 where
928 certification_id = c_certification_id;
929
930
931
932 begin
933
934 c_item_type := get_item_type(p_transaction_id);
935 c_item_key := get_item_key(p_transaction_id);
936
937 c_certification_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
938 itemkey => c_item_key,
939 aname => 'OTA_CERTIFICATION_ID'
940 , ignore_notfound => TRUE);
941
942
943
944 if c_certification_id is not null and c_person_id is not null then
945
946 open get_cert_comp_date;
947 fetch get_cert_comp_date into c_cert_date;
948 close get_cert_comp_date;
949
950 if c_Cert_date is not null then
951
952 l_return_value := ame_util.versiondateToString(c_cert_date);
953 end if;
954 end if;
955
956
957 return l_return_value;
958 EXCEPTION
959 WHEN OTHERS THEN
960 WF_CORE.CONTEXT(g_package,'get_init_cert_comp_date',c_item_type,c_item_key);
964
961 RAISE;
962
963 end get_init_cert_comp_date;
965 END ota_ame_attributes;