[Home] [Help]
PACKAGE BODY: APPS.OTA_COMPETENCE_SS
Source
1 PACKAGE BODY OTA_COMPETENCE_SS as
2 /* $Header: otcmpupd.pkb 120.11 2011/12/22 12:21:08 shwnayak noship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package VARCHAR2(33) := ' ota_Competence_ss'; -- Global package name
8
9
10
11
12 procedure get_review_data_from_tt
13 (p_transaction_step_id in number
14 ,p_review_data out nocopy Long
15 ,p_from out nocopy varchar2
16 )
17 is
18
19 l_competence_id varchar2(4000);
20 l_level_id varchar2(4000);
21 l_level_override varchar2(4000);
22 l_date_from varchar2(4000);
23 l_date_to varchar2(4000);
24 l_source varchar2(4000);
25 l_certification_date varchar2(4000);
26 l_certification_method varchar2(4000);
27 l_certification_next varchar2(4000);
28 l_comments varchar2(4000);
29
30 l_from varchar2(100);
31
32 begin
33
34
35 l_competence_id := hr_transaction_api.get_varchar2_value
36 (p_transaction_step_id => p_transaction_step_id
37 ,p_name => 'P_COMPETENCE');
38
39
40 l_level_id := hr_transaction_api.get_varchar2_value
41 (p_transaction_step_id => p_transaction_step_id
42 ,p_name => 'P_LEVEL');
43
44 l_level_override := hr_transaction_api.get_varchar2_value
45 (p_transaction_step_id => p_transaction_step_id
46 ,p_name => 'P_LEVELOVERRIDE');
47
48
49 l_date_from := hr_transaction_api.get_varchar2_value
50 (p_transaction_step_id => p_transaction_step_id
51 ,p_name => 'P_DATEFROM');
52
53 l_date_to := hr_transaction_api.get_varchar2_value
54 (p_transaction_step_id => p_transaction_step_id
55 ,p_name => 'P_DATETO');
56
57
58 l_source := hr_transaction_api.get_varchar2_value
59 (p_transaction_step_id => p_transaction_step_id
60 ,p_name => 'P_SOURCE');
61
62 l_certification_date := hr_transaction_api.get_varchar2_value
63 (p_transaction_step_id => p_transaction_step_id
64 ,p_name => 'P_CERTDATE');
65
66
67 l_certification_method := hr_transaction_api.get_varchar2_value
68 (p_transaction_step_id => p_transaction_step_id
69 ,p_name => 'P_CERTMETHOD');
70
71 l_certification_next := hr_transaction_api.get_varchar2_value
72 (p_transaction_step_id => p_transaction_step_id
73 ,p_name => 'P_CERTNEXT');
74
75 -- l_certification_next := to_char(to_date(l_certification_next),fnd_profile.value('ICX_DATE_FORMAT_MASK'));
76
77 l_comments := hr_transaction_api.get_varchar2_value
78 (p_transaction_step_id => p_transaction_step_id
79 ,p_name => 'P_COMMENTS');
80
81 l_from := hr_transaction_api.get_varchar2_value
82 (p_transaction_step_id => p_transaction_step_id
83 ,p_name => 'P_FROM');
84
85 p_from := l_from;
86
87 --
88 -- Now string all the retreived items into p_review_data
89
90 if l_from is not null then
91
92 p_review_data := nvl(l_competence_id,0)
93 ||'#'||nvl(l_level_id,'null')
94 ||'#'||nvl(l_level_override,'null')
95 ||'#'||nvl(l_date_from,'null')
96 ||'#'||nvl(l_date_to,'null')
97 ||'#'||nvl(l_source,'null')
98 ||'#'||nvl(l_certification_date,'null')
99 ||'#'||nvl(l_certification_method,'null')
100 ||'#'||nvl(l_certification_next,'null')
101 ||'#'||nvl(l_comments,'null');
102
103 else
104 p_review_data := nvl(l_competence_id,0)||'#'||nvl(l_level_id,'null')
105 ||'#'||nvl(l_date_from,'null');
106 end if;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 RAISE;
111
112 END get_review_data_from_tt;
113
114
115 -- ---------------------------------------------------------------------------
116 -- |----------------------< get_review_data_from_tt >--------------------------|
117 -- ---------------------------------------------------------------------------
118 --
119 PROCEDURE get_review_data
120 (p_item_type in varchar2
121 ,p_item_key in varchar2
122 ,p_review_data out nocopy long
123 ,p_from out nocopy varchar2
124 ) is
125
126
127 /* l_trans_step_ids hr_util_web.g_varchar2_tab_type;
128 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
129 ln_index number default 0;
130 l_trans_step_rows NUMBER ; */
131 l_review_data long(32000);
132 l_from varchar2(100);
133 l_trans_step_id number;
134
135
136 BEGIN
137
138 /* hr_transaction_api.get_transaction_step_info
139 (p_item_type => p_item_type
140 ,p_item_key => p_item_key
141 ,p_activity_id => p_activity_id
142 ,p_transaction_step_id => l_trans_step_ids
143 ,p_object_version_number => l_trans_obj_vers_nums
144 ,p_rows => l_trans_step_rows);
145 */
146 --added new
147 l_trans_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
148 ,itemkey => p_item_key
149 ,aname => 'OTA_TRANSACTION_STEP_ID');
150 get_review_data_from_tt(
151 p_transaction_step_id => l_trans_step_id
152 ,p_review_data => l_review_data
153 ,p_from => l_from );
154 /*
155 get_review_data_from_tt(
156 p_transaction_step_id => l_trans_step_ids(ln_index)
157 ,p_review_data => l_review_data);
158 */
159
160 p_review_data := l_review_data;
161 p_from := l_from;
162
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 RAISE;
167
168 END get_review_data;
169
170
171
172
173 -- ---------------------------------------------------------------------------
174 -- |----------------------< get_Comptence_eff_date >--------------------------|
175 -- ---------------------------------------------------------------------------
176 --
177 Function get_Competence_eff_date(
178 p_comp_id in per_competence_elements.competence_id%type,
179 p_id in ota_events.event_id%type,
180 p_obj_type in varchar2
181 ) return date
182 is
183
184
185 l_proc varchar2(72) := g_package||'get_Competence_eff_date';
186
187 l_eff_date date;
188
189 Cursor course_eff_date is
190 select oev.course_end_date
191 from ota_events oev,
192 ota_offerings off, ota_category_usages ocu
193 where oev.event_id=p_id
194 and (oev.parent_offering_id = off.offering_id or oev.offering_id = off.offering_id)
195 and off.delivery_mode_id = ocu.category_usage_id
196 and ocu.synchronous_flag = 'Y';
197 /*select pce.effective_date_from
198 from per_competence_elements pce , ota_offerings off,ota_events oev
199 where oev.parent_offering_id=off.offering_id
200 and off.activity_version_id=pce.activity_version_id
201 and oev.event_id= p_id
202 and pce.competence_id =p_comp_id
203 and type='DELIVERY'; */
204
205
206
207 /*Cursor lp_eff_date is
208 select pce.effective_date_from
209 from per_competence_elements pce
210 where pce.object_id= p_id
211 and pce.competence_id =p_comp_id
212 and type='OTA_LEARNING_PATH';*/
213
214 begin
215
216 hr_utility.set_location('Entering:'||l_proc, 5);
217 if p_obj_type='COURSE' then
218 OPEN course_eff_date;
219 FETCH course_eff_date INTO l_eff_date;
220 if course_eff_date%notfound then
221 select trunc(sysdate) into l_eff_date from dual;
222 end if;
223 CLOSE course_eff_date;
224 else
225 select trunc(sysdate) into l_eff_date from dual;
226 end if;
227 hr_utility.set_location('Leaving:'||l_proc, 5);
228 return l_eff_date;
229
230 Exception
231
232 when others then
233
234 raise;
235
236
237
238 end get_Competence_eff_date;
239
240
241
242 -- ---------------------------------------------------------------------------
243 -- |----------------------< save_Comptence_info >--------------------------|
244 -- ---------------------------------------------------------------------------
245 --
246 Procedure save_Comptence_info(
247 p_person_id in number ,
248 p_item_type in wf_items.item_type%type,
249 p_item_key in wf_items.item_key%type,
250 p_Competence_id in varchar2,
251 p_level_id in varchar2,
252 p_level_override in varchar2,
253 p_date_from in varchar2,
254 p_date_to in varchar2,
255 p_source in varchar2,
256 p_certification_date in varchar2,
257 p_certification_method in varchar2,
258 p_certification_next in varchar2,
259 p_comments in varchar2,
260 p_from in varchar2)
261
262 is
263
264 l_proc varchar2(72) := g_package||'save_Comptence_info';
265 l_transaction_id number default null;
266 l_transaction_step_id number default null;
267 l_trans_obj_vers_num number default null;
268 l_count integer default 0;
269 l_transaction_table hr_transaction_ss.transaction_table;
270 l_review_item_name varchar2(50);
271 l_message_number VARCHAR2(10);
272 l_result varchar2(100) default null;
273 l_old_transaction_step_id number;
274 l_old_object_version_number number;
275 l_activity_id number :=0;
276 l_business_group_id per_all_people_f.business_group_id%type;
277
278 cursor get_person_business_grp is ----Bug#6869342
279 select business_group_id from per_all_people_f
280 where person_id= p_person_id
281 and trunc(sysdate) between trunc(effective_start_date) and trunc(nvl(effective_end_date,sysdate+1));
282
283 begin
284
285 -- First, check if transaction id exists or not
286 l_transaction_id := hr_transaction_ss.get_transaction_id
287 (p_item_type => p_item_type
288 ,p_item_key => p_item_key);
289 --
290 IF l_transaction_id is null THEN
291 -- Start a Transaction
292 hr_transaction_ss.start_transaction
293 (itemtype => p_item_type
294 ,itemkey => p_item_key
295 ,actid => l_activity_id --not used
296 ,funmode => 'RUN'
297 ,p_login_person_id => p_person_id
298 ,p_function_id => 0--not available in api
299 ,result => l_result);
300
301 l_transaction_id := hr_transaction_ss.get_transaction_id
302 (p_item_type => p_item_type
303 ,p_item_key => p_item_key);
304
305 END IF;
306
307 hr_utility.set_location('Before chk transaction step'||l_proc, 5);
308
309 if (hr_transaction_api.transaction_step_exist (p_item_type => p_item_type
310 ,p_item_key => p_item_key
311 ,p_activity_id => l_activity_id) and p_from is null ) then
312
313 hr_transaction_api.get_transaction_step_info(p_item_type => p_item_type
314 ,p_item_key => p_item_key
315 ,p_activity_id => l_activity_id
316 ,p_transaction_step_id => l_old_transaction_step_id
317 ,p_object_version_number => l_old_object_version_number);
318
319
320
321
322 -- if l_old_transaction_step_id is not null then
323 hr_transaction_api.delete_transaction_step(p_validate => false
324 ,p_transaction_step_id => l_old_transaction_step_id
325 ,p_person_id => p_person_id
326 ,p_object_version_number => l_old_object_version_number);
327
328 end if;
329
330 --
331 -- Create a transaction step
332 --
333 hr_utility.set_location('Me Entering Create transaction step'||l_proc, 5);
334
335 if p_from ='SS' then
336 l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
337 ,itemkey => p_item_key
338 ,aname => 'OTA_TRANSACTION_STEP_ID');
339
340 l_business_group_id := hr_transaction_api.get_varchar2_value
341 (p_transaction_step_id => l_transaction_step_id
342 ,p_name => 'P_BUSINESS_GROUP_ID');
343
344 else
345
346 hr_transaction_api.create_transaction_step
347 (p_validate => false
348 ,p_creator_person_id => p_person_id
349 ,p_transaction_id => l_transaction_id
350 ,p_api_name => g_package || '.PROCESS_API'
351 ,p_item_type => p_item_type
352 ,p_item_key => p_item_key
353 ,p_activity_id => l_activity_id
354 ,p_transaction_step_id => l_transaction_step_id
355 ,p_object_version_number => l_trans_obj_vers_num);
356
357 OPEN get_person_business_grp; ----Bug#6869342
358 FETCH get_person_business_grp INTO l_business_group_id;
359 CLOSE get_person_business_grp;
360 --l_business_group_id := ota_general.get_business_group_id;
361
362
363 end if;
364 --
365 hr_utility.set_location('out of Create transaction step'||l_proc, 5);
366 HR_UTILITY.TRACE ('tranasction step id: ' || to_char (l_transaction_step_id));
367 l_count := 1;
368 l_transaction_table(l_count).param_name := 'P_COMPETENCE';
369 l_transaction_table(l_count).param_value := p_competence_id;
370 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
371
372 l_count := l_count + 1;
373 l_transaction_table(l_count).param_name := 'P_LEVEL';
374 l_transaction_table(l_count).param_value := p_level_id;
375 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
376
377 l_count := l_count + 1;
378 l_transaction_table(l_count).param_name := 'P_PERSON';
379 l_transaction_table(l_count).param_value := p_person_id;
380 l_transaction_table(l_count).param_data_type := 'NUMBER';
381
382 l_count := l_count + 1;
383 l_transaction_table(l_count).param_name := 'P_LEVELOVERRIDE';
384 l_transaction_table(l_count).param_value := p_level_override;
385 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
386
387 l_count := l_count + 1;
388 l_transaction_table(l_count).param_name := 'P_DATEFROM';
389 l_transaction_table(l_count).param_value := p_date_from;
390 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
391
392 l_count := l_count + 1;
393 l_transaction_table(l_count).param_name := 'P_DATETO';
394 l_transaction_table(l_count).param_value := p_date_to;
395 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
396
397 l_count := l_count + 1;
398 l_transaction_table(l_count).param_name := 'P_SOURCE';
399 l_transaction_table(l_count).param_value := p_source;
400 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
401
402 l_count := l_count + 1;
403 l_transaction_table(l_count).param_name := 'P_CERTDATE';
404 l_transaction_table(l_count).param_value := p_certification_date;
405 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
406
407 l_count := l_count + 1;
408 l_transaction_table(l_count).param_name := 'P_CERTMETHOD';
409 l_transaction_table(l_count).param_value := p_certification_method;
410 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
411
412 l_count := l_count + 1;
413 l_transaction_table(l_count).param_name := 'P_CERTNEXT';
414 l_transaction_table(l_count).param_value := p_certification_next;
415 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
416
417 l_count := l_count + 1;
418 l_transaction_table(l_count).param_name := 'P_COMMENTS';
419 l_transaction_table(l_count).param_value := p_comments;
420 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
421
422 l_count := l_count + 1;
423 l_transaction_table(l_count).param_name := 'P_FROM';
424 l_transaction_table(l_count).param_value := p_from;
425 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
426
427 l_count := l_count + 1;
428 l_transaction_table(l_count).param_name := 'P_BUSINESS_GROUP_ID';
429 l_transaction_table(l_count).param_value := l_business_group_id;
430 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
431
432
433
434
435 hr_approval_wf.create_item_attrib_if_notexist
436 (p_item_type => p_item_type
437 ,p_item_key => p_item_key
438 ,p_name => 'OTA_TRANSACTION_STEP_ID');
439
440 WF_ENGINE.setitemattrnumber(p_item_type,
441 p_item_key,
442 'OTA_TRANSACTION_STEP_ID',
443 l_transaction_step_id);
444
445 hr_utility.set_location('Before save transaction step'||l_proc, 5);
446 hr_transaction_ss.save_transaction_step
447 (p_item_type => p_item_type
448 ,p_item_key => p_item_key
449 ,p_actid => l_activity_id
450 ,p_login_person_id => p_person_id
451 ,p_transaction_step_id => l_transaction_step_id
452 ,p_api_name => g_package || '.PROCESS_API'
453 ,p_function_id => 0 ---not used
454 ,p_transaction_data => l_transaction_table);
455
456 hr_utility.set_location('After save transaction step'||l_proc, 50);
457
458 if p_from ='SS' then
459 hr_transaction_api.update_transaction
460 (p_transaction_id => l_transaction_id
461 -- ,p_status => lv_status
462 ,p_transaction_state => null
463 -- ,p_transaction_effective_date => ld_trans_effec_date
464 );
465 end if;
466
467 EXCEPTION
468 /* WHEN hr_utility.hr_error THEN
469 -- -------------------------------------------
470 -- an application error has been raised so we must
471 -- redisplay the web form to display the error
472 -- --------------------------------------------
473 hr_message.provide_error;
474 l_message_number := hr_message.last_message_number;
475 IF l_message_number = 'APP-7165' OR
476 l_message_number = 'APP-7155' THEN
477 --populate the p_error_message out variable
478 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
479 p_error_message => p_error_message,
480 p_attr_name => 'Page',
481 p_app_short_name => 'PER',
482 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
483 ELSE
484 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
485 p_error_message => p_error_message);
486 END IF; */
487 WHEN OTHERS THEN
488 /* p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
489 p_error_message => p_error_message);
490 */
491 raise;
492
493 end save_Comptence_info;
494
495
496 -- ---------------------------------------------------------------------------
497 -- |----------------------< Update_competence >--------------------------|
498 -- ---------------------------------------------------------------------------
499 --
500 PROCEDURE Update_competence ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
501 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
502 actid IN NUMBER,
503 funcmode IN VARCHAR2,
504 resultout OUT nocopy VARCHAR2 )
505 is
506
507 l_transaction_step_id number(15);
508
509 l_error_text varchar2(2000);
510 l_result varchar2(25);
511
512 begin
513 hr_utility.set_location('ENTERING Update Competence', 10);
514
515 hr_multi_message.disable_message_list;
516
517 IF (funcmode='RUN') THEN
518 savepoint commit_transaction;
519
520 l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
521 ,itemkey => itemkey
522 ,aname => 'OTA_TRANSACTION_STEP_ID');
523
524
525
526 process_api(false,l_transaction_step_id);
527 l_result := wf_engine.GetItemAttrText(itemtype => itemtype
528 ,itemkey => itemkey
529 ,aname => 'HR_FLOW_NAME_ATTR');
530 if l_result = 'PROCEED' then
531 resultout := 'COMPLETE:Y' ;
532 else
533 resultout := 'COMPLETE:N';
534 end if;
535 return;
536
537 end if;
538
539
540
541 IF (funcmode='CANCEL') THEN
542 resultout:='COMPLETE';
543 RETURN;
544 END IF;
545
546 hr_multi_message.enable_message_list;
547
548
549 EXCEPTION
550
551 When others then
552
553 rollback to commit_transaction;
554 --
555 hr_utility.set_location('ERROR Update Competence', 10);
556
557 l_error_text := hr_utility.get_message;
558 if l_error_text is null then
559 l_error_text := fnd_message.get;
560 end if;
561 -- 1903606
562 wf_engine.setitemattrtext
563 (itemtype => itemtype
564 ,itemkey => itemkey
565 ,aname => 'TRAN_SUBMIT'
566 ,avalue => 'E');
567
568 -- set the ERROR_MESSAGE_TEXT
569 wf_engine.setitemattrtext
570 (itemtype => itemtype
571 ,itemkey => itemkey
572 ,aname => 'ERROR_MESSAGE_TEXT'
573 ,avalue => nvl(l_error_text, sqlerrm));
574 hr_utility.trace('l_error_text' || nvl(l_error_text, sqlerrm));
575 -- update the transaction table status
576 hr_transaction_api.update_transaction(
577 p_transaction_id => hr_transaction_ss.get_transaction_id
578 (p_item_type => itemtype
579 ,p_item_key => itemkey),
580 p_status => 'E');
581
582 -- an application error or warning has been set
583 resultout := 'COMPLETE:E';
584
585 hr_multi_message.enable_message_list;
586 end Update_competence;
587
588 -- ---------------------------------------------------------------------------
589 -- |----------------------< check_Update_competence >--------------------------|
590 -- ---------------------------------------------------------------------------
591 --
592 PROCEDURE check_Update_competence ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
593 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
594 actid IN NUMBER,
595 funcmode IN VARCHAR2,
596 resultout OUT nocopy VARCHAR2 )
597 is
598
599 l_transaction_step_id number(15);
600
601 l_error_text varchar2(2000);
602 l_result varchar2(25);
603
604 begin
605 hr_utility.set_location('ENTERING check Update Competence', 10);
606 hr_multi_message.disable_message_list;
607 IF (funcmode='RUN') THEN
608 -- savepoint commit_transaction;
609
610 l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
611 ,itemkey => itemkey
612 ,aname => 'OTA_TRANSACTION_STEP_ID');
613
614
615
616 process_api(true,l_transaction_step_id);
617 l_result := wf_engine.GetItemAttrText(itemtype => itemtype
618 ,itemkey => itemkey
619 ,aname => 'HR_FLOW_NAME_ATTR');
620 hr_utility.trace('l_result chk_proceed' || l_result);
621 if l_result = 'PROCEED' then
622 resultout := 'COMPLETE:Y' ;
623 else
624 resultout := 'COMPLETE:N';
625 end if;
626 return;
627
628 end if;
629
630
631
632 IF (funcmode='CANCEL') THEN
633 resultout:='COMPLETE';
634 RETURN;
635 END IF;
636
637 hr_multi_message.enable_message_list;
638
639 EXCEPTION
640
641 When others then
642
643 -- rollback to commit_transaction;
644 --
645 hr_utility.set_location('ERROR Update Competence', 10);
646
647 l_error_text := hr_utility.get_message;
648 if l_error_text is null then
649 l_error_text := fnd_message.get;
650 end if;
651 -- 1903606
652 wf_engine.setitemattrtext
653 (itemtype => itemtype
654 ,itemkey => itemkey
655 ,aname => 'TRAN_SUBMIT'
656 ,avalue => 'E');
657
658 -- set the ERROR_MESSAGE_TEXT
659 wf_engine.setitemattrtext
660 (itemtype => itemtype
661 ,itemkey => itemkey
662 ,aname => 'ERROR_MESSAGE_TEXT'
663 ,avalue => nvl(l_error_text, sqlerrm));
664 hr_utility.trace('l_error_text' || nvl(l_error_text, sqlerrm));
665 -- update the transaction table status
666 hr_transaction_api.update_transaction(
667 p_transaction_id => hr_transaction_ss.get_transaction_id
668 (p_item_type => itemtype
669 ,p_item_key => itemkey),
670 p_status => 'E');
671
672 -- an application error or warning has been set
673 resultout := 'COMPLETE:E';
674
675 hr_multi_message.enable_message_list;
676
677 end check_Update_competence;
678
679
680 -- ---------------------------------------------------------------------------
681 -- |----------------------< get_approval_req >--------------------------|
682 -- ---------------------------------------------------------------------------
683 --
684 PROCEDURE get_approval_req ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
685 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
686 actid IN NUMBER,
687 funcmode IN VARCHAR2,
688 resultout OUT nocopy VARCHAR2 )
689 IS
690
691 l_item_value varchar2(200);
692 l_ntf_url varchar2(4000);
693 l_item_value_crs varchar2(200);
694 l_item_value_off varchar2(200);
695 l_event_id number(15);
696 l_LP_id number(15);
697 l_cert_id number(15);
698 l_person_id varchar2(30);
699 l_active_assignment number(15) := -1;
700
701
702
703 Cursor getActAttrValue is
704 SELECT WAAV.TEXT_VALUE Value
705 FROM WF_ACTIVITY_ATTR_VALUES WAAV
706 WHERE WAAV.PROCESS_ACTIVITY_ID = actid
707 AND WAAV.NAME = 'HR_APPROVAL_REQ_FLAG';
708
709 cursor get_cert_setting(l_id number) is
710 select competency_update_level
711 from ota_certifications_b
712 where certification_id = l_id;
713
714 cursor get_LP_setting(l_id number) is
715 select competency_update_level
716 from ota_learning_paths
717 where learning_path_id = l_id;
718
719 cursor get_course_off_setting(l_id number) is
720 select oav.competency_update_level,off.competency_update_level
721 from ota_activity_versions oav,ota_offerings off,ota_events oev
722 where oav.activity_version_id = off.activity_version_id
723 and off.offering_id = oev.parent_offering_id
724 and
725 oev.event_id = l_id;
726
727 --added for bug 7308160
728 cursor C_Assignment(p_person_id varchar2) is
729 SELECT paf.ASSIGNMENT_ID
730 FROM per_all_assignments_f paf
731 WHERE paf.person_id = p_person_id
732 AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
733 AND paf.primary_flag ='Y'
734 AND paf.assignment_type in ('E','A', 'C');
735
736 BEGIN
737 hr_utility.set_location('ENTERING get_approval_req', 10);
738 IF (funcmode='RUN') THEN
739
740 l_cert_id := wf_engine.getItemAttrNumber(itemtype => itemtype
741 ,itemkey => itemkey
742 , aname => 'RECRUITER_ID');
743 l_LP_id := wf_engine.getItemAttrNumber(itemtype => itemtype
744 ,itemkey => itemkey
745 , aname => 'BOOKING_ID');
746 l_event_id := wf_engine.getItemAttrNumber(itemtype => itemtype
747 ,itemkey => itemkey
748 , aname => 'EVENT_ID');
749 l_person_id := wf_engine.GetItemAttrText(itemtype => itemtype
750 ,itemkey => itemkey
751 ,aname => 'CURRENT_PERSON_ID');
752
753 --added for bug 7308160
754 if l_person_id is not null then
755 open C_Assignment(l_person_id);
756 fetch C_Assignment into l_active_assignment;
757 close C_Assignment;
758 end if;
759
760 if (l_active_assignment = -1 and l_person_id is not null) then
761 --learner is ex-employee so automatic update without approval
762 l_item_value:='NOTIFYUPDATE';
763 else
764 if l_cert_id is not null then
765 open get_cert_setting(l_cert_id);
766 fetch get_cert_setting into l_item_value;
767 close get_cert_setting;
768
769 elsif l_LP_id is not null then
770 open get_LP_setting(l_LP_id);
771 fetch get_LP_setting into l_item_value;
772 close get_LP_setting;
773 else
774 open get_course_off_setting(l_event_id);
775 fetch get_course_off_setting into l_item_value_crs,l_item_value_off;
776 close get_course_off_setting;
777
778 if l_item_value_off is not null then
779 l_item_value := l_item_value_off;
780 else
781 l_item_value:= l_item_value_crs;
782 end if;
783
784 end if;
785 end if;
786
787 if l_item_value is null then
788
789 OPEN getActAttrValue;
790 FETCH getActAttrValue INTO l_item_value;
791 hr_utility.trace('l_item_value' || l_item_value);
792 close getActAttrValue;
793
794 end if;
795 -- if getActAttrValue%FOUND then
796
797 /*
798 l_item_value := wf_engine.getItemAttrText(itemtype => itemtype
799 ,itemkey => itemkey
800 , aname => 'HR_APPROVAL_REQ_FLAG');
801
802 wf_engine.*/
803
804 if l_item_value = 'NONOTIFY' then
805
806 resultout:='COMPLETE:NONOTIFY';
807
808 elsif l_item_value = 'NOTIFYUPDATE' then
809
810 resultout:='COMPLETE:NOTIFYUPDATE';
811
812 elsif l_item_value = 'APPROVAL' then
813
814 resultout:='COMPLETE:APPROVAL';
815 elsif l_item_value = 'NOTIFYONLY' then
816
817 resultout:='COMPLETE:NOTIFYONLY';
818 else
819
820 resultout:='COMPLETE';
821
822
823 end if;
824 hr_utility.trace('l_resultout' || resultout);
825 l_ntf_url := generate_url(l_item_value);
826 WF_ENGINE.setitemattrtext(itemtype, itemkey, 'APPROVAL_GENERIC_URL', l_ntf_url);
827 WF_ENGINE.setitemattrtext(itemtype, itemkey, 'HR_REVIEW_TEMPLATE_RN_ATTR', 'OTA_LIST_COMP_NTF');
828 /* END IF; -- cursor
829 CLOSE getActAttrValue;*/
830 RETURN;
831 END IF; --RUN
832
833 IF (funcmode='CANCEL') THEN
834 resultout:='COMPLETE';
835 RETURN;
836 END IF;
837 Exception
838
839 when others then
840 hr_utility.set_location('ENTERING exception get_approval_req', 10);
841
842
843
844 end get_approval_req;
845 -- ---------------------------------------------------------------------------
846 -- |----------------------< get_Value >--------------------------|
847 -- ---------------------------------------------------------------------------
848 --
849
850 Procedure get_Value( inString varchar2, startPos number, endPos out nocopy number , retValue out nocopy varchar2)
851 is
852
853 l_value varchar2(1000);
854 l_posFound number(15);
855
856 begin
857
858 select INSTR(inString,'^',startPos) into l_posFound from dual;
859
860 if l_posFound=0 then --implies last string token
861 select SUBSTR(inString,startPos,((length(inString)+1)-startPos)) into l_value from dual;
862 else
863 select SUBSTR(inString,startPos,(l_posFound-startPos)) into l_value from dual;
864 end if;
865
866
867 endPos :=l_posFound ;
868 retValue := l_value;
869
870 Exception
871 when others then
872 raise;
873
874 end get_Value;
875
876
877 PROCEDURE process_api
878 (p_validate IN BOOLEAN ,p_transaction_step_id IN NUMBER) IS
879
880 /*l_transaction_mode VARCHAR2(10);
881 l_from VARCHAR2(20);
882 l_tran_submitted VARCHAR2(1);*/
883
884 l_item_type HR_API_TRANSACTION_STEPS.ITEM_TYPE%TYPE;
885 l_item_key HR_API_TRANSACTION_STEPS.ITEM_KEY%TYPE;
886 l_activity_id HR_API_TRANSACTION_STEPS.ACTIVITY_ID%TYPE;
887
888 l_proc varchar2(72) := g_package||'process_api';
889 l_from VARCHAR2(20);
890
891 l_person_id number(15);
892
893 l_Comp_ids varchar2(4000);
894 l_level_ids varchar2(4000);
895
896 l_comp_startPos number(15):=1;
897 l_comp_endPos number(15);
898 l_comp_retValue varchar2(100);
899
900 l_level_startPos number(15) :=1;
901 l_level_endPos number(15);
902 l_level_retValue varchar2(100);
903
904 l_override_ids varchar2(4000);
905
906 l_override_startPos number(15):=1;
907 l_override_endPos number(15);
908 l_override_retValue varchar2(100);
909
910 l_dtFrom varchar2(4000);
911
912 l_dtFrom_startPos number(15) :=1;
913 l_dtFrom_endPos number(15);
914 l_dtFrom_retValue varchar2(100);
915
916 l_dtTo varchar2(4000);
917 l_source varchar2(4000);
918
919 l_dtTo_startPos number(15):=1;
920 l_dtTo_endPos number(15);
921 l_dtTo_retValue varchar2(100);
922
923 l_source_startPos number(15) :=1;
924 l_source_endPos number(15);
925 l_source_retValue varchar2(100);
926
927 l_certDate varchar2(4000);
928 l_certMethod varchar2(4000);
929
930 l_certDate_startPos number(15):=1;
931 l_certDate_endPos number(15);
932 l_certDate_retValue varchar2(100);
933
934 l_certMethod_startPos number(15) :=1;
935 l_certMethod_endPos number(15);
936 l_certMethod_retValue varchar2(100);
937
938 l_certNext varchar2(4000);
939 l_comments varchar2(4000);
940
941 l_certNext_startPos number(15):=1;
942 l_certNext_endPos number(15);
943 l_certNext_retValue varchar2(100);
944
945 l_comments_startPos number(15) :=1;
946 l_comments_endPos number(15);
947 l_comments_retValue varchar2(1000);
948
949 l_competence_created number(10);
950
951 l_level_value_to_use number(10);
952
953 l_business_group_id per_all_people_f.business_group_id%type;
954
955 l_old_level_id number(15);
956 l_attr_chk boolean := TRUE;
957 l_fwd_to_username varchar2(1000);
958 l_msg_name varchar2(1000);
959
960 Cursor Comp_level(CompId number,personId number) is
961 Select Proficiency_level_id
962 from per_competence_elements
963 where Competence_id = CompId
964 and person_id = personId;
965
966
967 BEGIN
968
969 /* l_Comp_ids := hr_transaction_api.get_varchar2_value
970 (p_transaction_step_id => p_transaction_step_id
971 ,p_name => 'P_COMPETENCE');*/
972
973 SAVEPOINT validate_competence;
974
975 l_person_id := hr_transaction_api.get_number_value
976 (p_transaction_step_id => p_transaction_step_id
977 ,p_name => 'P_PERSON');
978
979 l_Comp_ids := hr_transaction_api.get_varchar2_value
980 (p_transaction_step_id => p_transaction_step_id
981 ,p_name => 'P_COMPETENCE');
982
983 l_level_ids := hr_transaction_api.get_varchar2_value
984 (p_transaction_step_id => p_transaction_step_id
985 ,p_name => 'P_LEVEL');
986
987 l_override_ids := hr_transaction_api.get_varchar2_value
988 (p_transaction_step_id => p_transaction_step_id
989 ,p_name => 'P_LEVELOVERRIDE');
990
991 l_dtFrom := hr_transaction_api.get_varchar2_value
992 (p_transaction_step_id => p_transaction_step_id
993 ,p_name => 'P_DATEFROM');
994
995 l_dtTo := hr_transaction_api.get_varchar2_value
996 (p_transaction_step_id => p_transaction_step_id
997 ,p_name => 'P_DATETO');
998
999 l_source := hr_transaction_api.get_varchar2_value
1000 (p_transaction_step_id => p_transaction_step_id
1001 ,p_name => 'P_SOURCE');
1002
1003 l_certDate := hr_transaction_api.get_varchar2_value
1004 (p_transaction_step_id => p_transaction_step_id
1005 ,p_name => 'P_CERTDATE');
1006
1007 l_certMethod := hr_transaction_api.get_varchar2_value
1008 (p_transaction_step_id => p_transaction_step_id
1009 ,p_name => 'P_CERTMETHOD');
1010
1011
1012 l_certNext := hr_transaction_api.get_varchar2_value
1016 l_comments := hr_transaction_api.get_varchar2_value
1013 (p_transaction_step_id => p_transaction_step_id
1014 ,p_name => 'P_CERTNEXT');
1015
1017 (p_transaction_step_id => p_transaction_step_id
1018 ,p_name => 'P_COMMENTS');
1019
1020 l_business_group_id := hr_transaction_api.get_varchar2_value
1021 (p_transaction_step_id => p_transaction_step_id
1022 ,p_name => 'P_BUSINESS_GROUP_ID');
1023
1024 --GET item type and key
1025 hr_transaction_api.get_transaction_step_info
1026 (p_transaction_step_id => p_transaction_step_id
1027 ,p_item_type => l_item_type
1028 ,p_item_key => l_item_key
1029 ,p_activity_id => l_activity_id);
1030
1031 -- no need if no competency is attached
1032 if l_comp_ids is not null then
1033 Loop
1034
1035 get_value(inString =>l_comp_ids,
1036 startPos =>l_comp_startPos,
1037 endPos => l_comp_endPos,
1038 retValue => l_comp_retValue);
1039 hr_utility.trace ('l_comp_retValue ' ||l_comp_retValue);
1040
1041 l_comp_startPos := l_comp_endPos+1;
1042
1043 get_value(inString =>l_level_ids,
1044 startPos =>l_level_startPos,
1045 endPos => l_level_endPos,
1046 retValue => l_level_retValue);
1047
1048 l_level_startPos := l_level_endPos+1;
1049
1050 if l_level_retValue ='-1' then
1051
1052 l_level_retValue := null;
1053
1054 end if;
1055
1056 get_value(inString =>l_override_ids,
1057 startPos =>l_override_startPos,
1058 endPos => l_override_endPos,
1059 retValue => l_override_retValue);
1060
1061 l_override_startPos := l_override_endPos+1;
1062
1063 if l_override_retValue ='-1' then
1064
1065 l_override_retValue := null;
1066
1067 end if;
1068
1069
1070
1071 get_value(inString =>l_dtFrom,
1072 startPos =>l_dtFrom_startPos,
1073 endPos => l_dtFrom_endPos,
1074 retValue => l_dtFrom_retValue);
1075 hr_utility.trace ('l_dtFrom_retValue ' ||l_dtFrom_retValue);
1076 l_dtFrom_startPos := l_dtFrom_endPos+1;
1077
1078 if l_dtFrom_retValue ='-1' then
1079
1080 l_dtFrom_retValue := null;
1081
1082 end if;
1083
1084 if l_dtFrom_retValue is null then
1085
1086 l_dtFrom_retValue := trunc(sysdate);
1087 end if;
1088
1089
1090 get_value(inString =>l_dtTo,
1091 startPos =>l_dtTo_startPos,
1092 endPos => l_dtTo_endPos,
1093 retValue => l_dtTo_retValue);
1094 hr_utility.trace ('l_dtTo_retValue ' ||l_dtTo_retValue);
1095 l_dtTo_startPos := l_dtTo_endPos+1;
1096
1097 if l_dtTo_retValue ='-1' then
1098
1099 l_dtTo_retValue := null;
1100
1101 end if;
1102
1103 get_value(inString =>l_source,
1104 startPos =>l_source_startPos,
1105 endPos => l_source_endPos,
1106 retValue => l_source_retValue);
1107
1108 l_source_startPos := l_source_endPos+1;
1109
1110 if l_source_retValue ='-1' then
1111
1112 l_source_retValue := null;
1113
1114 end if;
1115
1116 get_value(inString =>l_certDate,
1117 startPos =>l_certDate_startPos,
1118 endPos => l_certDate_endPos,
1119 retValue => l_certDate_retValue);
1120
1121 l_certDate_startPos := l_certDate_endPos+1;
1122
1123 if l_certDate_retValue ='-1' then
1124
1125 l_certDate_retValue := null;
1126
1127 end if;
1128
1129 get_value(inString =>l_certMethod,
1130 startPos =>l_certMethod_startPos,
1131 endPos => l_certMethod_endPos,
1132 retValue => l_certMethod_retValue);
1133
1134 l_certMethod_startPos := l_certMethod_endPos+1;
1135
1136 if l_certMethod_retValue ='-1' then
1137
1138 l_certMethod_retValue := null;
1139
1140 end if;
1141
1142 get_value(inString =>l_certNext,
1143 startPos =>l_certNext_startPos,
1144 endPos => l_certNext_endPos,
1145 retValue => l_certNext_retValue);
1146
1147 l_certNext_startPos := l_certNext_endPos+1;
1148
1149 if l_certNext_retValue ='-1' then
1150
1151 l_certNext_retValue := null;
1152
1153 end if;
1154
1155 get_value(inString =>l_comments,
1156 startPos =>l_comments_startPos,
1157 endPos => l_comments_endPos,
1158 retValue => l_comments_retValue);
1159
1160 l_comments_startPos := l_comments_endPos+1;
1161
1162 if l_comments_retValue ='-1' then
1163
1164 l_comments_retValue := null;
1165
1166 end if;
1167
1168 if l_override_retValue is not null then
1169 l_level_value_to_use := l_override_retValue;
1170 else
1171 l_level_value_to_use := l_level_retValue;
1172 end if;
1173 -- bug 3433361
1174 l_old_level_id := null;
1175 OPEN Comp_level(l_comp_retValue,l_person_id);
1176 FETCH Comp_level INTO l_old_level_id;
1177 CLOSE Comp_level;
1181 hr_utility.set_location('BEFORE call to competence element api', 100);
1178
1179 --if ((l_level_value_to_use is not null and l_old_level_id <= l_level_value_to_use) or l_old_level_id is null) then
1180
1182
1183 hr_competence_element_api.maintain_student_comp_element
1184 (p_person_id => l_person_id
1185 ,p_competence_id => l_comp_retValue
1186 ,p_proficiency_level_id => l_level_value_to_use
1187 ,p_business_group_id => l_business_group_id --2798
1188 ,p_effective_date_from => to_date(l_dtFrom_retValue,g_date_format)
1189 ,p_effective_date_to => to_date(l_dtTo_retValue,g_date_format)
1190 ,p_certification_date => to_date(l_certDate_retValue,g_date_format)
1191 ,p_certification_method => l_certMethod_retValue
1192 ,p_next_certification_date => to_date(l_certNext_retValue,g_date_format)
1193 ,p_source_of_proficiency_level => l_source_retValue
1194 ,p_comments => l_comments_retValue
1195 ,p_effective_date => trunc(sysdate)
1196 -- ,p_validate => p_validate
1197 ,p_competence_created => l_competence_created);
1198 hr_utility.set_location('After call to competence element api', 100);
1199 hr_utility.trace ('l_competence_created ' ||l_competence_created);
1200
1201
1202
1203 if l_competence_created <> 0 and (l_attr_chk) then
1204 l_attr_chk := false;
1205 -- WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_FLOW_NAME_ATTR', 'PROCEED');
1206 -- elsif (l_attr_chk) then
1207 -- WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_FLOW_NAME_ATTR', 'STOP');
1208 end if;
1209 --end if; --level_id
1210
1211 if l_comp_endPos =0 then --implies end of string has been reached
1212
1213 Exit ;
1214 end if;
1215
1216
1217
1218 end Loop;
1219
1220 end if;
1221
1222 if p_validate then
1223 rollback to validate_competence;
1224 end if;
1225 if l_attr_chk then
1226 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_FLOW_NAME_ATTR', 'STOP');
1227 else
1228 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_FLOW_NAME_ATTR', 'PROCEED');
1229 end if;
1230 hr_utility.set_location('Leaving'||l_proc, 5);
1231
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 hr_utility.set_location('Leaving with error:'||l_proc, 25);
1235 rollback to validate_competence;
1236 -- In case of Approvla required Error notification shud go to approving manager
1237 -- In case of auto competence update error notification shud go to learner
1238 l_fwd_to_username:= nvl(wf_engine.getitemattrtext
1239 (l_item_type,
1240 l_item_key,
1241 'FORWARD_TO_USERNAME'
1242 ),
1243 wf_engine.getitemattrtext
1244 (l_item_type,
1245 l_item_key,
1246 'CURRENT_PERSON_USERNAME'
1247 )
1248 );
1249 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'APPROVAL_CREATOR_USERNAME', l_fwd_to_username);
1250 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_REVIEW_TEMPLATE_RN_ATTR', 'HR_CHKERRORSRN_NTF');
1251 /*wf_engine.SetItemAttrText ( l_item_type, l_item_key,
1252 'APPROVAL_CREATOR_DISPLAY_NAME',l_fwd_to_username
1253 );
1254 */
1255 -- change date message to a more meaningful one.
1256 l_msg_name:= sqlerrm;
1257 hr_utility.trace('l_msg_name' || sqlerrm);
1258 if instr(l_msg_name,'_51647_')>0 then
1259 hr_utility.set_location('MESSAGE CHANGED', 10);
1260 fnd_message.set_name('OTA', 'OTA_443335_COMP_UPD_FAIL');
1261 --fnd_message.raise_error;
1262 end if;
1263 RAISE;
1264 --null;
1265 END process_api;
1266
1267 -- ---------------------------------------------------------------------------
1268 -- |----------------------< validate_competence_update >--------------------------|
1269 -- ---------------------------------------------------------------------------
1270 --
1271
1272 procedure validate_competence_update
1273 (p_item_type in varchar2,
1274 p_item_key in varchar2,
1275 p_message out nocopy varchar2) is
1276
1277 l_transaction_step_id number(15);
1278
1279
1280 begin
1281
1282 l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
1283 ,itemkey => p_item_key
1284 ,aname => 'OTA_TRANSACTION_STEP_ID');
1285
1286
1287
1288 process_api(true,l_transaction_step_id);
1289
1290 p_message := 'S' ;
1291
1292 EXCEPTION
1293 When OTHERS Then
1294 p_message := fnd_message.get();
1295 If p_message is NULL then
1296 p_message := substr(SQLERRM,11,(length(SQLERRM)-10));
1297 End If;
1298
1299 end validate_competence_update;
1300
1301
1302 -- ---------------------------------------------------------------------------
1303 -- |----------------------< chk_comp_level >--------------------------|
1304 -- ---------------------------------------------------------------------------
1305 --
1306 function chk_comp_level(p_comp_id in varchar2,
1307 p_level_id in varchar2,
1308 p_person_id in number)
1309 return varchar2 is
1310
1311 Cursor Comp_level(CompId number,personId number) is
1312 Select Proficiency_level_id
1313 from per_competence_elements
1314 where Competence_id = CompId
1315 and person_id = personId;
1316
1317 l_person_id number(15):= p_person_id;
1318
1319 l_Comp_ids varchar2(4000):=p_comp_id;
1320 l_level_ids varchar2(4000):= p_level_id;
1321
1322 l_comp_startPos number(15):=1;
1323 l_comp_endPos number(15);
1324 l_comp_retValue varchar2(100);
1325
1326 l_level_startPos number(15) :=1;
1327 l_level_endPos number(15);
1328 l_level_retValue varchar2(100);
1329
1330 l_old_level_id number(15);
1331 l_flag Boolean := TRUE;
1332
1333
1334 begin
1335
1336 Loop
1337
1338 get_value(inString =>l_comp_ids,
1339 startPos =>l_comp_startPos,
1340 endPos => l_comp_endPos,
1341 retValue => l_comp_retValue);
1342 hr_utility.trace ('l_comp_retValue ' ||l_comp_retValue);
1343
1344 l_comp_startPos := l_comp_endPos+1;
1345
1346 get_value(inString =>l_level_ids,
1347 startPos =>l_level_startPos,
1348 endPos => l_level_endPos,
1349 retValue => l_level_retValue);
1350
1351 l_level_startPos := l_level_endPos+1;
1352
1353
1354
1355 l_old_level_id := null;
1356 OPEN Comp_level(l_comp_retValue,l_person_id);
1357 FETCH Comp_level INTO l_old_level_id;
1358 -- CLOSE Comp_level;
1359 if Comp_level%notfound then --implies competence itself doesn't exist
1360 CLOSE Comp_level;
1361 l_flag:=false;
1362 exit;
1363
1364 else
1365 CLOSE Comp_level;
1366 end if;
1367
1368 if (nvl(l_old_level_id,-1) <= nvl(l_level_retValue,-1)) then
1369
1370 l_flag:= FALSE;
1371 exit;
1372
1373
1374 end if;
1375
1376 if l_comp_endPos =0 then --implies end of string has been reached
1377
1378 Exit ;
1379 end if;
1380
1381
1382
1383 end Loop;
1384
1385 if l_flag then
1386 return 'NOUPDATE';
1387 else
1388
1389 return 'UPDATE';
1390 end if;
1391
1392 end chk_comp_level;
1393
1394
1395 -- ---------------------------------------------------------------------------
1396 -- |----------------------< create_wf_process >--------------------------|
1397 -- ---------------------------------------------------------------------------
1398 --
1399
1400 Procedure create_wf_process(p_process in wf_process_activities.process_name%type,
1401 p_itemtype in wf_items.item_type%type,
1402 p_person_id in number ,
1403 p_eventid in ota_Events.event_id%type,
1404 p_learningpath_ids in varchar2 ,
1405 p_certification_Id in number default null,
1406 p_itemkey out nocopy wf_items.item_key%type)
1407 is
1408
1409 l_proc varchar2(72) := g_package||'create_wf_process';
1410 l_process wf_activities.name%type := upper(p_process);
1411 l_item_type wf_items.item_type%type := upper(p_itemtype);
1412 l_item_key wf_items.item_key%type;
1413
1414 l_LP_ids varchar2(4000) := p_learningpath_ids;
1415
1416 l_LP_startPos number(15):=1;
1417 l_LP_endPos number(15);
1418 l_LP_retValue varchar2(100);
1419
1420 l_user_name varchar2(80);
1421 l_current_username varchar2(80);
1422 --:= fnd_profile.value('USERNAME');
1423 l_current_user_Id number ;
1424 --:= fnd_profile.value('USER_ID');
1425
1426 l_creator_username varchar2(80):= fnd_profile.value('USERNAME');
1427 l_creator_user_Id number := fnd_profile.value('USER_ID');
1428
1429 l_creator_person_id per_all_people_f.person_id%type;
1430 l_ntf_url varchar2(4000);
1431 l_comp_ids varchar2(4000);
1432 l_level_ids varchar2(4000);
1433 l_eff_date_from varchar2(4000);
1434 l_eff_date_to varchar2(4000);
1435
1436 --l_person_details ota_learner_enroll_ss.csr_person_to_enroll_details%ROWTYPE;
1437 l_person_full_name per_all_people_f.full_name%TYPE;
1438 l_role_name wf_roles.name%type;
1439 l_role_display_name wf_roles.display_name%type;
1440
1441 l_assignment varchar2(100);
1442
1443 l_supervisor_id per_all_people_f.person_id%Type;
1444 l_supervisor_username fnd_user.user_name%TYPE;
1445 l_supervisor_full_name per_all_people_f.full_name%TYPE;
1446
1447 l_event_name ota_events.title%type;
1448
1449 l_course_name ota_activity_versions.version_name%type;
1450 l_process_display_name varchar2(240);
1451
1452 l_loop_counter number(15) := 0;
1453 l_will_comp_update varchar2(50);
1454 l_LP_lookup_meaning varchar2(100);
1455 l_CRS_lookup_meaning varchar2(100);
1456 l_CERT_lookup_meaning varchar2(100);
1457
1458 Cursor get_display_name is
1459 SELECT wrpv.display_name displayName
1460 FROM wf_runnable_processes_v wrpv
1461 WHERE wrpv.item_type = p_itemtype
1462 AND wrpv.process_name = p_process;
1463
1464 cursor curr_per_info
1465 is
1466 Select user_id ,user_name
1467 from
1468 fnd_user
1469 where employee_id=p_person_id;
1470
1471 CURSOR C_USER IS
1472 SELECT
1473 EMPLOYEE_ID
1474 FROM
1475 FND_USER
1476 WHERE
1477 user_id = l_creator_user_id ;
1478
1479 Cursor C_Assignment is
1480 SELECT
1481 paf.ASSIGNMENT_ID
1482 FROM per_all_assignments_f paf
1483 WHERE paf.person_id = p_person_id
1484 AND TRUNC(SYSDATE) BETWEEN
1485 paf.effective_start_date
1486 AND paf.effective_end_date
1487 AND paf.primary_flag ='Y'
1488 AND paf.assignment_type in ('E','A', 'C');
1489
1490 --added for bug 7308160
1491 Cursor C_Ex_Assignment is
1492 SELECT
1493 paf.ASSIGNMENT_ID
1494 FROM per_all_assignments_f paf
1495 WHERE paf.person_id = p_person_id
1496 AND paf.primary_flag ='Y'
1497 AND paf.assignment_type in ('E','A', 'C')
1498 order by paf.effective_end_date;
1499
1500 Cursor get_person_full_name is
1501 Select ppf.full_name
1502 FROM per_all_people_f ppf
1503 where ppf.person_id = p_person_id
1504 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
1505 AND ppf.effective_end_date;
1506
1507
1508 CURSOR csr_supervisor_id IS
1509 SELECT asg.supervisor_id, per.full_name
1510 FROM per_all_assignments_f asg,
1511 per_all_people_f per
1512 WHERE asg.person_id = p_person_id
1513 AND per.person_id = asg.supervisor_id
1514 AND asg.primary_flag = 'Y'
1518 BETWEEN per.effective_start_date AND per.effective_end_date;
1515 AND trunc(sysdate)
1516 BETWEEN asg.effective_start_date AND asg.effective_end_date
1517 AND trunc(sysdate)
1519
1520 CURSOR csr_supervisor_user IS
1521 SELECT user_name
1522 FROM fnd_user
1523 WHERE employee_id= l_supervisor_id;
1524
1525 Cursor csr_name is
1526 select oev.title,oav.version_name
1527 from ota_Events_vl oev ,ota_activity_versions_tl oav
1528 --,ota_offerings off
1529 where
1530 --oev.parent_offering_id=off.offering_id and
1531 oev.activity_version_id= oav.activity_version_id
1532 and oev.event_id= p_eventid
1533 and Language= USERENV('LANG');
1534
1535 cursor LP_name (csr_lp_id varchar2) is
1536 select name from ota_learning_paths_TL
1537 where learning_path_id =csr_lp_id
1538 and Language= USERENV('LANG');
1539
1540 cursor chk_person_business_grp
1541 is
1542 Select 1 from
1543 per_all_people_f
1544 where person_id= p_person_id
1545 and (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null or --Bug#6869342
1546 business_group_id = ota_general.get_business_group_id);
1547
1548 -- get certification name
1549 cursor certification_name
1550 is select name from ota_certifications_tl
1551 where certification_id = p_certification_id
1552 and Language= USERENV('LANG');
1553
1554 cursor getFYINtfParamVal(param varchar2) is
1555 SELECT decode(instr(web_html_call, param), 0, '-1',
1556 substr(substr(web_html_call, instr(web_html_call, param),
1557 (decode(instr(web_html_call, '&', instr(web_html_call, param), 1), 0, (length(web_html_call)+1),
1558 instr(web_html_call, '&', instr(web_html_call, param), 1))-instr(web_html_call, param))),
1559 instr(substr(web_html_call, instr(web_html_call, param),
1560 (decode(instr(web_html_call, '&', instr(web_html_call, param), 1), 0, (length(web_html_call)+1),
1561 instr(web_html_call, '&', instr(web_html_call, param), 1))-instr(web_html_call, param))), '=')+1)) "paramvalue"
1562 FROM fnd_form_functions
1563 WHERE function_name = 'OTA_LEARNER_HOME_SS';
1564
1565 l_business_group_id per_all_people_f.business_group_id%type;
1566 l_ntfFyiParamVal varchar2(5) := 'N';
1567
1568
1569 BEGIN
1570 hr_utility.set_location('Entering:'||l_proc, 5);
1571
1572 OPEN chk_person_business_grp;
1573 FETCH chk_person_business_grp INTO l_business_group_id;
1574 if chk_person_business_grp%found then
1575 CLOSE chk_person_business_grp;
1576
1577
1578 --Retrieve comp. info for the course first to be stored in tt table
1579 COMP_RETREIVE ( p_event_id => p_eventid
1580 , p_learning_path_ids => p_learningpath_ids
1581 , p_certification_id => p_certification_id
1582 , p_person_id => p_person_id
1583 , p_comp_ids => l_comp_ids
1584 , p_level_ids =>l_level_ids
1585 ,p_eff_date_from => l_eff_date_from
1586 ,p_eff_date_to => l_eff_date_to);
1587
1588 --Retrieve comp. info for each of the LP's to be stored in tt table ,
1589 --create separate worklfow process for each LP.
1590 hr_utility.trace ('l_LP_idsssss ' ||l_LP_ids);
1591
1592 -- code required for object type to be set in notifications
1593 if p_learningpath_ids is not null then
1594 l_LP_lookup_meaning := ota_utility.get_lookup_meaning(p_lookup_type => 'OTA_CATALOG_OBJECT_TYPE',
1595 p_lookup_code =>'CLP',
1596 p_application_id =>810);
1597 elsif p_eventid is not null then
1598 l_CRS_lookup_meaning := ota_utility.get_lookup_meaning(p_lookup_type => 'OTA_CATALOG_OBJECT_TYPE',
1599 p_lookup_code =>'H',
1600 p_application_id =>810);
1601 else ---Batra to revisit**********************
1602 l_CERT_lookup_meaning := ota_utility.get_lookup_meaning(p_lookup_type => 'OTA_CATALOG_OBJECT_TYPE',
1603 p_lookup_code =>'CER',
1604 p_application_id =>810);
1605 end if;
1606 /*Loop
1607
1608 if l_loop_counter > 0 and (l_LP_ids <>'' or l_LP_ids is not null) then
1609
1610 get_value(inString =>l_LP_ids,
1611 startPos =>l_LP_startPos,
1612 endPos => l_LP_endPos,
1613 retValue => l_LP_retValue);
1614 hr_utility.trace ('l_LP_retValue ' ||l_LP_retValue);
1615
1616 l_LP_startPos := l_LP_endPos+1;
1617
1618 --Retrieve comp. info for the course first , to be stored in tt table
1619 COMP_RETREIVE ( p_event_id => p_eventid
1620 , p_learning_path_ids =>l_LP_retValue
1621 , p_comp_ids => l_comp_ids
1622 , p_level_ids =>l_level_ids
1623 ,p_eff_date_from => l_eff_date_from);
1624
1625 end if;
1626 hr_utility.trace ('l_comp_idssssssssss ' ||l_comp_ids);
1627 --l_current_user_Id := 12725;
1628 */
1629 --Start transaction and workflow only when competencies are attached
1630 /*if l_comp_ids is not null then
1631 l_will_comp_update := chk_comp_level(l_comp_ids,l_level_ids,p_person_id);
1632 end if;*/
1633 --if (l_comp_ids is not null and l_will_comp_update='UPDATE')then
1634 if (l_comp_ids is not null) then
1635 --if l_comp_ids is not null then
1636
1637
1638 OPEN get_display_name;
1639 FETCH get_display_name INTO l_process_display_name;
1640 CLOSE get_display_name;
1641
1642
1643 OPEN curr_per_info;
1644 FETCH curr_per_info INTO l_current_user_id, l_current_username;
1645 CLOSE curr_per_info;
1646
1647 OPEN C_USER;
1648 FETCH C_USER INTO l_creator_person_id;
1649 CLOSE C_USER;
1650
1651 open C_Assignment;
1655 --added for bug 7308160
1652 Fetch C_Assignment into l_assignment;
1653 close C_Assignment;
1654
1656 if l_assignment is null then
1657 FOR assg in C_Ex_Assignment LOOP
1658 l_assignment := assg.assignment_id;
1659 END LOOP;
1660 end if;
1661
1662 open get_person_full_name;
1663 Fetch get_person_full_name into l_person_full_name;
1664 close get_person_full_name;
1665
1666
1667 if p_eventid is not null then
1668 open csr_name;
1669 Fetch csr_name into l_event_name ,l_course_name;
1670 close csr_name;
1671
1672 elsif p_learningpath_ids is not null then
1673 OPEN LP_name(p_learningpath_ids);
1674 FETCH LP_name INTO l_course_name;
1675 CLOSE LP_name;
1676
1677 else
1678 OPEN certification_name;
1679 FETCH certification_name INTO l_course_name;
1680 CLOSE certification_name;
1681 end if;
1682
1683 hr_utility.set_location('Entering:'||l_proc, 10);
1684 -- Get the next item key from the sequence
1685 select hr_workflow_item_key_s.nextval
1686 into l_item_key
1687 from sys.dual;
1688
1689
1690 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1691
1692 if p_Learningpath_ids is not null then
1693 hr_utility.set_location('before OTA_OBJECT_TYPE'||l_proc, 140);
1694 WF_ENGINE.setitemattrtext(l_item_type,
1695 l_item_key,
1696 'REVIEW_OBJECT',
1697 l_LP_lookup_meaning);
1698 hr_utility.set_location('after OTA_OBJECT_TYPE'||l_proc, 240);
1699 elsif p_eventid is not null then
1700 hr_utility.set_location('before OTA_OBJECT_TYPE 2'||l_proc, 340);
1701 WF_ENGINE.setitemattrtext(l_item_type,
1702 l_item_key,
1703 'REVIEW_OBJECT',
1704 l_CRS_lookup_meaning);
1705 hr_utility.set_location('after OTA_OBJECT_TYPE 2'||l_proc, 440);
1706 else
1707 WF_ENGINE.setitemattrtext(l_item_type,
1708 l_item_key,
1709 'REVIEW_OBJECT',
1710 l_CERT_lookup_meaning);
1711 end if;
1712
1713 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CURRENT_PERSON_ID', p_person_id);
1714 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CURRENT_PERSON_USERNAME', l_current_username);
1715 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_USERNAME', l_current_username);
1716 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_ID', p_person_id);
1717 --WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_USERNAME', l_creator_username);
1718 --WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_ID', l_creator_person_id);
1719 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'PROCESS_DISPLAY_NAME', l_process_display_name);
1720 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'PROCESS_NAME',p_process );
1721 --hard coded date format required by pqh
1722 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key,'P_EFFECTIVE_DATE',to_char(trunc(sysdate),'RRRR-MM-DD'));
1723 --WF_ENGINE.setitemattrtext(p_itemtype, l_item_key,'P_EFFECTIVE_DATE',trunc(sysdate));
1724 WF_ENGINE.setitemattrDate(p_itemtype, l_item_key,'CURRENT_EFFECTIVE_DATE',trunc(sysdate));
1725
1726 open getFYINtfParamVal('pFyiNtfDetails');
1727 fetch getFYINtfParamVal into l_ntfFyiParamVal;
1728 close getFYINtfParamVal;
1729
1730 if(trim(l_ntfFyiParamVal) = 'Y') then
1731 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key,'FYI_NTF_DETAILS','Y');
1732 end if;
1733
1734 -- Get and set owner role
1735
1736 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
1737
1738 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
1739 p_orig_system_id => l_creator_person_id,
1740 p_name =>l_role_name,
1741 p_display_name =>l_role_display_name);
1742
1743
1744 WF_ENGINE.SetItemOwner(
1745 itemtype => l_item_type,
1746 itemkey =>l_item_key,
1747 owner =>l_role_name);
1748
1749 hr_utility.set_location('After Setting Owner'||l_proc, 10);
1750
1751 --modified for bug 7308160
1752
1753 /*l_person_details := ota_learner_enroll_ss.Get_Person_To_Enroll_Details(p_person_id => p_person_id);
1754
1755 IF l_person_details.full_name is not null then
1756 WF_ENGINE.setitemattrtext(l_item_type,
1757 l_item_key,
1758 'CURRENT_PERSON_DISPLAY_NAME',
1759 l_person_details.full_name);
1760 WF_ENGINE.setitemattrtext(l_item_type,
1761 l_item_key,
1762 'CREATOR_PERSON_DISPLAY_NAME',
1763 l_person_details.full_name);
1764 END IF;*/
1765
1766 IF l_person_full_name is not null then
1767 WF_ENGINE.setitemattrtext(l_item_type,
1768 l_item_key,
1769 'CURRENT_PERSON_DISPLAY_NAME',
1770 l_person_full_name);
1771 WF_ENGINE.setitemattrtext(l_item_type,
1772 l_item_key,
1773 'CREATOR_PERSON_DISPLAY_NAME',
1774 l_person_full_name);
1775 END IF;
1776
1777
1778 HR_UTILITY.TRACE ('item key: ' || l_item_key);
1779
1780
1781
1782
1783 --start a transaction and save data to transaction tables
1784 save_Comptence_info(
1785 p_person_id =>p_person_id ,
1786 p_item_type => l_item_type,
1787 p_item_key => l_item_key,
1791 p_date_to => l_eff_date_to);
1788 p_Competence_id => l_comp_ids,
1789 p_level_id => l_level_ids,
1790 p_date_from =>l_eff_date_from,
1792
1793
1794 hr_utility.set_location('before supervisor'||l_proc, 30);
1795 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_CUSTOM_RETURN_FOR_CORR','Y');
1796
1797 --always set to Y
1798 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_RUNTIME_APPROVAL_REQ_FLAG', 'YES');
1799 hr_utility.set_location('before supervisor'||l_proc, 40);
1800 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'P_ASSIGNMENT_ID', l_assignment);
1801 hr_utility.set_location('before supervisor'||l_proc, 50);
1802 WF_ENGINE.setitemattrNumber(l_item_type, l_item_key, 'CURRENT_ASSIGNMENT_ID', to_number(l_assignment));
1803
1804 if p_eventId is not null then
1805 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'OTA_EVENT_TITLE', l_event_name);
1806 end if;
1807
1808 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'OTA_ACTIVITY_VERSION_NAME', l_course_name);
1809 --WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_AME_TRAN_TYPE_ATTR','SSHRMS');
1810 -- bug 3483960
1811 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_AME_TRAN_TYPE_ATTR','OTA');
1812 WF_ENGINE.setitemattrNumber(l_item_type, l_item_key, 'HR_AME_APP_ID_ATTR', 810);
1813 --WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'TRAN_SUBMIT','Y');
1814 --WF_ENGINE.SetItemattrtext(p_itemtype,p_item_key, 'EVENT_OWNER',l_user_name);
1815 hr_utility.set_location('before supervisor'||l_proc, 20);
1816
1817 FOR a IN csr_supervisor_id LOOP
1818 l_supervisor_id := a.supervisor_id;
1819 l_supervisor_full_name := a.full_name;
1820 END LOOP;
1821
1822
1823 FOR b IN csr_supervisor_user LOOP
1824 l_supervisor_username := b.user_name;
1825 END LOOP;
1826
1827 hr_utility.set_location('after supervisor cursor'||l_proc, 20);
1828
1829 wf_engine.setitemattrtext
1830 (l_item_type,
1831 l_item_key,
1832 'SUPERVISOR_USERNAME',
1833 l_supervisor_username);
1834 hr_utility.set_location('after supervisor username'||l_proc, 20);
1835
1836 wf_engine.setitemattrtext
1837 (l_item_type,
1838 l_item_key,
1839 'SUPERVISOR_DISPLAY_NAME',
1840 l_supervisor_full_name);
1841 hr_utility.set_location('after supervisor disp name'||l_proc, 20);
1842 wf_engine.setitemattrtext
1843 (l_item_type,
1844 l_item_key,
1845 'SUPERVISOR_ID',
1846 l_supervisor_id);
1847 hr_utility.set_location('before start process'||l_proc, 20);
1848 if p_learningpath_ids is not null then
1849
1850 /*hr_approval_wf.create_item_attrib_if_notexist
1851 (p_item_type => l_item_type
1852 ,p_item_key => l_item_key
1853 ,p_name => 'OTA_LP_ID');*/
1854
1855 WF_ENGINE.setitemattrnumber(l_item_type,
1856 l_item_key,
1857 'BOOKING_ID',
1858 p_learningpath_ids);
1859
1860 elsif p_eventId is not null then
1861
1862
1863 WF_ENGINE.setitemattrnumber(l_item_type,
1864 l_item_key,
1865 'EVENT_ID',
1866 p_eventid);
1867 else
1868
1869 WF_ENGINE.setitemattrnumber(l_item_type,
1870 l_item_key,
1871 'RECRUITER_ID',
1872 p_certification_id);
1873 end if;
1874 WF_ENGINE.STARTPROCESS(p_itemtype,l_item_key);
1875
1876 end if;
1877 else
1878 CLOSE chk_person_business_grp;
1879 end if;--chk_person_business_grp
1880
1881 p_itemkey:=l_item_key;
1882
1883
1884 hr_utility.set_location('leaving:'||l_proc, 20);
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1888 -- Raise;
1889
1890
1891
1892 end create_wf_process;
1893
1894 -- ---------------------------------------------------------------------------
1895 -- |----------------------< generate_url >--------------------------|
1896 -- ---------------------------------------------------------------------------
1897 --
1898
1899 Function generate_url(p_func varchar2) return varchar2
1900 is
1901
1902 l_proc varchar2(72) := g_package||'generate_url';
1903 l_jsp_apps_agent varchar2(2000);
1904 l_url varchar2(4000);
1905 l_amp varchar2(2) := '&';
1906 l_func varchar2(80);
1907
1908 begin
1909 hr_utility.set_location('Entering'||l_proc, 5);
1910
1911 --l_jsp_apps_agent := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1912 --HR_UTILITY.TRACE ('Agent :: ' || l_jsp_apps_agent);
1913 if p_func='APPROVAL' then
1914 l_func:='OTA_ADMIN_COMPETENCE_UPDATE';
1915 else
1916 l_func:='OTA_ADMIN_COMPETENCE_VIEW';
1917
1918 end if;
1919
1920 l_url := 'JSP:' || '/OA_HTML/OA.jsp?OAFunc=' || l_func || l_amp || 'NtfId=-&#NID-' || l_amp || 'retainAM=Y';
1921
1922 HR_UTILITY.TRACE ('URL :: ' || l_url);
1923
1924 return l_url;
1925
1926 hr_utility.set_location('Leaving'||l_proc, 5);
1927 end generate_url;
1928
1929 -- ---------------------------------------------------------------------------
1930 -- |----------------------< COMP_RETREIVE >--------------------------|
1931 -- ---------------------------------------------------------------------------
1932 --
1933 PROCEDURE COMP_RETREIVE ( p_event_id IN NUMBER
1934 , p_learning_path_ids IN VARCHAR2
1935 , p_certification_id IN Number
1936 , p_person_id in number
1937 , p_comp_ids OUT NOCOPY VARCHAR2
1938 , p_level_ids OUT NOCOPY VARCHAR2
1939 ,p_eff_date_from out nocopy varchar2
1943 l_learning_path_id NUMBER;
1940 ,p_eff_date_to out nocopy varchar2) IS
1941
1942
1944 l_leftpos NUMBER := 1;
1945 l_rightpos NUMBER ;
1946 l_count NUMBER := 1;
1947 l_learning_path_ids VARCHAR2(4000);
1948 l_eff_date_from varchar2(4000);
1949 l_expiry_date ota_cert_enrollments.expiration_date%type;
1950 l_eff_date_to varchar2(4000):=NULL;
1951
1952 l_comp_id NUMBER;
1953 l_renewable_period_frequency NUMBER;
1954 l_renewable_period_units VARCHAR2(4000);
1955 p_active_days NUMBER;
1956 l_start_date Date;
1957 l_end_date Date;
1958 allow_comp_renewal_period varchar2(30):= 'N';
1959
1960 l_proc varchar2(72) := g_package||'COMP_RETREIVE';
1961
1962 --13076307:Modified cursors fetching competencies:Added date clause to fetch only
1963 --active competencies(i.e to not retrieve end dated competencies)
1964 CURSOR csr_get_crs_comps IS
1965 SELECT pce.competence_id CompetenceId
1966 ,nvl(pce.proficiency_level_id,-1) LevelId
1967 FROM per_competence_elements pce
1968 , ota_offerings OFF
1969 , ota_events EVT
1970 , per_competences pc
1971 WHERE off.activity_version_id = pce.activity_version_id
1972 AND evt.parent_offering_id = off.offering_id
1973 AND evt.event_id = p_event_id
1974 and pce.competence_id = pc.competence_id
1975 and trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
1976
1977 --Added for 8403115 Allow competency update with renewal period for class enrollments
1978
1979 CURSOR csr_get_renewable_period_units(p_competence_id NUMBER) IS
1980 SELECT
1981 pc.competence_id,
1982 pc.renewal_period_frequency,
1983 pc.renewal_period_units
1984 FROM
1985 per_competences pc
1986 where
1987 pc.competence_id = p_competence_id
1988 AND pc.renewal_period_frequency IS NOT NULL
1989 --AND pc.renewal_period_units IS NOT NULL modified for bug8410902
1990 AND pc.renewal_period_units IN ('Y','D','H','MIN','M','Q','W');
1991
1992 Cursor get_comp_profile is
1993 Select nvl(fnd_profile.value('OTA_ALLOW_COMPETENCY_UPDATE_WITH_RENEWAL_PERIOD'),'N') from dual;
1994
1995 CURSOR csr_get_lp_comps IS
1996 SELECT pce.competence_id CompetenceId
1997 ,nvl(pce.proficiency_level_id,-1) LevelId
1998 FROM per_competence_elements pce
1999 , per_competences pc
2000 WHERE pce.type = 'OTA_LEARNING_PATH'
2001 AND pce.object_id = l_learning_path_id
2002 AND pce.competence_id = pc.competence_id
2003 AND trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
2004
2005 CURSOR csr_get_cert_comps IS
2006 SELECT pce.competence_id CompetenceId
2007 ,nvl(pce.proficiency_level_id,-1) LevelId
2008 FROM per_competence_elements pce
2009 , per_competences pc
2010 WHERE pce.type = 'OTA_CERTIFICATION' ---Batra to revisit*********************
2011 AND pce.object_id = p_certification_id
2012 AND pce.competence_id = pc.competence_id
2013 AND trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
2014
2015 Cursor get_cert_expiry is
2016 select cenr.expiration_date
2017 from ota_cert_enrollments cenr
2018 where certification_id = p_certification_id
2019 and person_id = p_person_id;
2020 -- and certification_status_code ='CERTIFIED';--not ok?//////////////
2021
2022
2023 BEGIN
2024
2025 hr_utility.set_location('Entering:'||l_proc, 5);
2026 IF p_learning_path_ids = '' or p_learning_path_ids is null THEN
2027 l_learning_path_ids := NULL;
2028 ELSE
2029 l_learning_path_ids:= p_learning_path_ids;
2030 END IF;
2031 IF p_event_id IS not NULL THEN
2032 hr_utility.set_location('l_:'||l_proc, 10);
2033 OPEN get_comp_profile;
2034 FETCH get_comp_profile into allow_comp_renewal_period;
2035 CLOSE get_comp_profile;
2036 FOR crs_comp_rec IN csr_get_crs_comps LOOP
2037 IF p_comp_ids IS NULL OR p_comp_ids = '' THEN
2038 p_comp_ids := crs_comp_rec.CompetenceId;
2039 p_level_ids := crs_comp_rec.LevelId;
2040 --Modified for 8403115 Allow competency update with renewal period for class enrollments
2041 -- l_eff_date_from := to_char(get_competence_eff_date(p_comp_id=> crs_comp_rec.CompetenceId,p_id=>p_event_id, p_obj_type=>'COURSE'),g_date_format) ;
2042 p_active_days := 0;
2043 l_start_date := get_competence_eff_date(p_comp_id=> crs_comp_rec.CompetenceId,p_id=>p_event_id, p_obj_type=>'COURSE');
2044 l_eff_date_from := to_char(l_start_date,g_date_format) ;
2045 l_eff_date_to := NULL;
2046
2047 if(allow_comp_renewal_period = 'Y') then
2048 OPEN csr_get_renewable_period_units(crs_comp_rec.CompetenceId);
2049 FETCH csr_get_renewable_period_units into l_comp_id,l_renewable_period_frequency,l_renewable_period_units;
2050 IF csr_get_renewable_period_units % FOUND then
2051
2052 if(l_renewable_period_units='D') then
2053 --renewable unit is Day
2054 p_active_days := l_renewable_period_frequency;
2055
2056 elsif(l_renewable_period_units='H') then
2057 --renewable unit is Hour
2058 if(l_renewable_period_frequency <= 24 ) then
2059 p_active_days := 0;
2060 else
2061 p_active_days := (l_renewable_period_frequency/24);
2062 end if;
2063
2064 elsif(l_renewable_period_units='M') then
2065 --renewable unit is Month
2066 p_active_days := l_renewable_period_frequency * 30;
2067
2068 elsif(l_renewable_period_units='MIN') then
2069 --renewable unit is Minute
2070 if(l_renewable_period_frequency <= 1440 ) then
2071 p_active_days := 0;
2072 else
2076 elsif(l_renewable_period_units='Q') then
2073 p_active_days := (l_renewable_period_frequency/1440);
2074 end if;
2075
2077 --renewable unit is Quarter Hour
2078 if(l_renewable_period_frequency <= 96 ) then
2079 p_active_days := 0;
2080 else
2081 p_active_days := (l_renewable_period_frequency/96);
2082 end if;
2083
2084 elsif(l_renewable_period_units='W') then
2085 --renewable unit is Week
2086 p_active_days := l_renewable_period_frequency * 7;
2087
2088 elsif(l_renewable_period_units='Y') then
2089 --renewable unit is Year
2090 p_active_days := l_renewable_period_frequency * 365;
2091 end if;
2092
2093 l_end_date:= l_start_date+round(p_active_days);-- bug8410988
2094 l_eff_date_to := to_char(l_end_date,g_date_format);
2095
2096
2097 CLOSE csr_get_renewable_period_units;
2098 ELSE
2099 CLOSE csr_get_renewable_period_units;
2100 END IF;
2101
2102
2103 end if;
2104
2105
2106
2107 ELSE
2108 p_comp_ids := p_comp_ids || '^' || crs_comp_rec.CompetenceId;
2109 p_level_ids := p_level_ids || '^' || crs_comp_rec.LevelId;
2110 --l_eff_date_from :=l_eff_date_from || '^' ||to_char(get_competence_eff_date(p_comp_id=> crs_comp_rec.CompetenceId,p_id=>p_event_id,p_obj_type=>'COURSE' ),g_date_format) ;
2111 p_active_days := 0;
2112 l_start_date := get_competence_eff_date(p_comp_id=> crs_comp_rec.CompetenceId,p_id=>p_event_id, p_obj_type=>'COURSE');
2113 l_eff_date_from :=l_eff_date_from || '^' ||to_char(l_start_date,g_date_format) ;
2114
2115
2116 if(allow_comp_renewal_period = 'Y') then
2117 OPEN csr_get_renewable_period_units(crs_comp_rec.CompetenceId);
2118 FETCH csr_get_renewable_period_units into l_comp_id,l_renewable_period_frequency,l_renewable_period_units;
2119 IF csr_get_renewable_period_units % FOUND then
2120
2121 if(l_renewable_period_units='D') then
2122 --renewable unit is Day
2123 p_active_days := l_renewable_period_frequency;
2124
2125 elsif(l_renewable_period_units='H') then
2126 --renewable unit is Hour
2127 if(l_renewable_period_frequency <= 24 ) then
2128 p_active_days := 0;
2129 else
2130 p_active_days := (l_renewable_period_frequency/24);
2131 end if;
2132
2133 elsif(l_renewable_period_units='M') then
2134 --renewable unit is Month
2135 p_active_days := l_renewable_period_frequency * 30;
2136
2137 elsif(l_renewable_period_units='MIN') then
2138 --renewable unit is Minute
2139 if(l_renewable_period_frequency <= 1440 ) then
2140 p_active_days := 0;
2141 else
2142 p_active_days := (l_renewable_period_frequency/1440);
2143 end if;
2144
2145 elsif(l_renewable_period_units='Q') then
2146 --renewable unit is Quarter Hour
2147 if(l_renewable_period_frequency <= 96 ) then
2148 p_active_days := 0;
2149 else
2150 p_active_days := (l_renewable_period_frequency/96);
2151 end if;
2152
2153 elsif(l_renewable_period_units='W') then
2154 --renewable unit is Week
2155 p_active_days := l_renewable_period_frequency * 7;
2156
2157 elsif(l_renewable_period_units='Y') then
2158 --renewable unit is Year
2159 p_active_days := l_renewable_period_frequency * 365;
2160 end if;
2161
2162 l_end_date:= l_start_date+round(p_active_days);-- bug8410988
2163 l_eff_date_to :=l_eff_date_to || '^' ||to_char(l_end_date,g_date_format) ;
2164
2165 CLOSE csr_get_renewable_period_units;
2166 ELSE
2167 --no/non-seeded duration units specified for the competence
2168 l_eff_date_to :=l_eff_date_to || '^' ||NULL;
2169 CLOSE csr_get_renewable_period_units;
2170 END IF;
2171
2172 end if;
2173 END IF;
2174 END LOOP;
2175 END IF;
2176
2177 IF l_learning_path_ids IS NOT NULL THEN
2178 hr_utility.set_location('Entering:'||l_proc, 15);
2179 /* LOOP
2180 l_rightpos := INSTR(p_learning_path_ids,'^',1,l_count);
2181 IF l_rightpos = 0 THEN
2182 l_learning_path_id := to_number(SUBSTR(l_learning_path_ids,l_leftpos,length(p_learning_path_ids) - l_leftpos +1));
2183 ELSE
2184 l_learning_path_id := to_number(SUBSTR(l_learning_path_ids,l_leftpos,l_rightpos - l_leftpos));
2185 END IF;
2186 l_leftpos := l_rightpos + 1;
2187 l_count := l_count +1; */
2188
2189 l_learning_path_id := to_number(l_learning_path_ids);
2190 hr_utility.trace ('l_learning_path_id ' ||l_learning_path_id);
2191 FOR lps_comp_rec IN csr_get_lp_comps LOOP
2192 hr_utility.set_location('Entering:'||l_proc, 20);
2193 IF p_comp_ids IS NULL OR p_comp_ids = '' THEN
2194 hr_utility.set_location('Entering:'||l_proc, 25);
2195 p_comp_ids := lps_comp_rec.CompetenceId;
2196 p_level_ids := lps_comp_rec.LevelId;
2197 hr_utility.set_location('Entering:'||l_proc, 35);
2198 l_eff_date_from := to_char(get_competence_eff_date(p_comp_id=> lps_comp_rec.CompetenceId,p_id=>l_learning_path_id,p_obj_type=>'LP'),g_date_format) ;
2199 hr_utility.set_location('Entering:'||l_proc, 45);
2200
2201 ELSE
2202 p_comp_ids := p_comp_ids || '^' || lps_comp_rec.CompetenceId;
2203 p_level_ids := p_level_ids || '^' || lps_comp_rec.LevelId;
2204 l_eff_date_from :=l_eff_date_from || '^' ||to_char(get_competence_eff_date(p_comp_id=> lps_comp_rec.CompetenceId,p_id=>l_learning_path_id,p_obj_type=>'LP'),g_date_format) ;
2205
2206 END IF;
2207 END LOOP;
2208 -- dbms_output.put_line('Learning Path Id ' || l_learning_path_id);
2209 -- EXIT WHEN l_rightpos = 0;
2210 --END LOOP;
2211 END IF;
2212
2213 IF p_certification_id IS not NULL THEN ---Batra to revisst ***************888888
2214
2215 open get_cert_expiry;
2216 fetch get_cert_expiry into l_expiry_date;
2217 close get_cert_expiry;
2218
2219
2220
2221 FOR crs_cert_rec IN csr_get_cert_comps LOOP
2222 IF p_comp_ids IS NULL OR p_comp_ids = '' THEN
2223 p_comp_ids := crs_cert_rec.CompetenceId;
2224 p_level_ids := crs_cert_rec.LevelId;
2225 l_eff_date_from := to_char(trunc(sysdate),g_date_format);
2226 if l_expiry_date is not null then
2227 l_eff_date_to := to_char(l_expiry_date,g_date_format );
2228 end if;
2229 ELSE
2230 p_comp_ids := p_comp_ids || '^' || crs_cert_rec.CompetenceId;
2231 p_level_ids := p_level_ids || '^' || crs_cert_rec.LevelId;
2232 -- start date would be the date certification is completed
2233 l_eff_date_from :=l_eff_date_from || '^' ||to_char(trunc(sysdate),g_date_format);
2234 if l_expiry_date is not null then
2235 l_eff_date_to := l_eff_date_to || '^' ||to_char(l_expiry_date,g_date_format );
2236 end if;
2237 END IF;
2238 END LOOP;
2239 END IF;
2240 p_eff_date_from :=l_eff_date_from;
2241 p_eff_date_to := l_eff_date_to;
2242
2243 hr_utility.set_location('Leaving:'||l_proc, 5);
2244
2245 Exception
2246
2247 when others then
2248 raise;
2249
2250 END comp_retreive;
2251
2252
2253 end ota_Competence_ss;
2254
2255