[Home] [Help]
PACKAGE BODY: APPS.OTA_COMPETENCE_SS
Source
1 PACKAGE BODY OTA_COMPETENCE_SS as
2 /* $Header: otcmpupd.pkb 120.3.12010000.5 2008/09/10 10:44:32 shwnayak ship $ */
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
1013 (p_transaction_step_id => p_transaction_step_id
1014 ,p_name => 'P_CERTNEXT');
1015
1016 l_comments := hr_transaction_api.get_varchar2_value
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;
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
1181 hr_utility.set_location('BEFORE call to competence element api', 100);
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'
1515 AND trunc(sysdate)
1516 BETWEEN asg.effective_start_date AND asg.effective_end_date
1517 AND trunc(sysdate)
1518 BETWEEN per.effective_start_date AND per.effective_end_date;
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 l_business_group_id per_all_people_f.business_group_id%type;
1555
1556
1557 BEGIN
1558 hr_utility.set_location('Entering:'||l_proc, 5);
1559
1560 OPEN chk_person_business_grp;
1561 FETCH chk_person_business_grp INTO l_business_group_id;
1562 if chk_person_business_grp%found then
1563 CLOSE chk_person_business_grp;
1564
1565
1566 --Retrieve comp. info for the course first to be stored in tt table
1567 COMP_RETREIVE ( p_event_id => p_eventid
1568 , p_learning_path_ids => p_learningpath_ids
1569 , p_certification_id => p_certification_id
1570 , p_person_id => p_person_id
1571 , p_comp_ids => l_comp_ids
1572 , p_level_ids =>l_level_ids
1573 ,p_eff_date_from => l_eff_date_from
1574 ,p_eff_date_to => l_eff_date_to);
1575
1576 --Retrieve comp. info for each of the LP's to be stored in tt table ,
1577 --create separate worklfow process for each LP.
1578 hr_utility.trace ('l_LP_idsssss ' ||l_LP_ids);
1579
1580 -- code required for object type to be set in notifications
1581 if p_learningpath_ids is not null then
1582 l_LP_lookup_meaning := ota_utility.get_lookup_meaning(p_lookup_type => 'OTA_CATALOG_OBJECT_TYPE',
1583 p_lookup_code =>'CLP',
1584 p_application_id =>810);
1585 elsif p_eventid is not null then
1586 l_CRS_lookup_meaning := ota_utility.get_lookup_meaning(p_lookup_type => 'OTA_CATALOG_OBJECT_TYPE',
1587 p_lookup_code =>'H',
1588 p_application_id =>810);
1589 else ---Batra to revisit**********************
1590 l_CERT_lookup_meaning := ota_utility.get_lookup_meaning(p_lookup_type => 'OTA_CATALOG_OBJECT_TYPE',
1591 p_lookup_code =>'CER',
1592 p_application_id =>810);
1593 end if;
1594 /*Loop
1595
1596 if l_loop_counter > 0 and (l_LP_ids <>'' or l_LP_ids is not null) then
1597
1598 get_value(inString =>l_LP_ids,
1599 startPos =>l_LP_startPos,
1600 endPos => l_LP_endPos,
1601 retValue => l_LP_retValue);
1602 hr_utility.trace ('l_LP_retValue ' ||l_LP_retValue);
1603
1604 l_LP_startPos := l_LP_endPos+1;
1605
1606 --Retrieve comp. info for the course first , to be stored in tt table
1607 COMP_RETREIVE ( p_event_id => p_eventid
1608 , p_learning_path_ids =>l_LP_retValue
1609 , p_comp_ids => l_comp_ids
1610 , p_level_ids =>l_level_ids
1611 ,p_eff_date_from => l_eff_date_from);
1612
1613 end if;
1614 hr_utility.trace ('l_comp_idssssssssss ' ||l_comp_ids);
1615 --l_current_user_Id := 12725;
1616 */
1617 --Start transaction and workflow only when competencies are attached
1618 /*if l_comp_ids is not null then
1619 l_will_comp_update := chk_comp_level(l_comp_ids,l_level_ids,p_person_id);
1620 end if;*/
1621 --if (l_comp_ids is not null and l_will_comp_update='UPDATE')then
1622 if (l_comp_ids is not null) then
1623 --if l_comp_ids is not null then
1624
1625
1626 OPEN get_display_name;
1627 FETCH get_display_name INTO l_process_display_name;
1628 CLOSE get_display_name;
1629
1630
1631 OPEN curr_per_info;
1632 FETCH curr_per_info INTO l_current_user_id, l_current_username;
1633 CLOSE curr_per_info;
1634
1635 OPEN C_USER;
1636 FETCH C_USER INTO l_creator_person_id;
1637 CLOSE C_USER;
1638
1639 open C_Assignment;
1640 Fetch C_Assignment into l_assignment;
1641 close C_Assignment;
1642
1643 --added for bug 7308160
1644 if l_assignment is null then
1645 FOR assg in C_Ex_Assignment LOOP
1646 l_assignment := assg.assignment_id;
1647 END LOOP;
1648 end if;
1649
1650 open get_person_full_name;
1651 Fetch get_person_full_name into l_person_full_name;
1652 close get_person_full_name;
1653
1654
1655 if p_eventid is not null then
1656 open csr_name;
1657 Fetch csr_name into l_event_name ,l_course_name;
1658 close csr_name;
1659
1660 elsif p_learningpath_ids is not null then
1661 OPEN LP_name(p_learningpath_ids);
1662 FETCH LP_name INTO l_course_name;
1663 CLOSE LP_name;
1664
1665 else
1666 OPEN certification_name;
1667 FETCH certification_name INTO l_course_name;
1668 CLOSE certification_name;
1669 end if;
1670
1671 hr_utility.set_location('Entering:'||l_proc, 10);
1672 -- Get the next item key from the sequence
1673 select hr_workflow_item_key_s.nextval
1674 into l_item_key
1675 from sys.dual;
1676
1677
1678 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1679
1680 if p_Learningpath_ids is not null then
1681 hr_utility.set_location('before OTA_OBJECT_TYPE'||l_proc, 140);
1682 WF_ENGINE.setitemattrtext(l_item_type,
1683 l_item_key,
1684 'REVIEW_OBJECT',
1685 l_LP_lookup_meaning);
1686 hr_utility.set_location('after OTA_OBJECT_TYPE'||l_proc, 240);
1687 elsif p_eventid is not null then
1688 hr_utility.set_location('before OTA_OBJECT_TYPE 2'||l_proc, 340);
1689 WF_ENGINE.setitemattrtext(l_item_type,
1690 l_item_key,
1691 'REVIEW_OBJECT',
1692 l_CRS_lookup_meaning);
1693 hr_utility.set_location('after OTA_OBJECT_TYPE 2'||l_proc, 440);
1694 else
1695 WF_ENGINE.setitemattrtext(l_item_type,
1696 l_item_key,
1697 'REVIEW_OBJECT',
1698 l_CERT_lookup_meaning);
1699 end if;
1700
1701 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CURRENT_PERSON_ID', p_person_id);
1702 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CURRENT_PERSON_USERNAME', l_current_username);
1703 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_USERNAME', l_current_username);
1704 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_ID', p_person_id);
1705 --WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_USERNAME', l_creator_username);
1706 --WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'CREATOR_PERSON_ID', l_creator_person_id);
1707 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'PROCESS_DISPLAY_NAME', l_process_display_name);
1708 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key, 'PROCESS_NAME',p_process );
1709 --hard coded date format required by pqh
1710 WF_ENGINE.setitemattrtext(p_itemtype, l_item_key,'P_EFFECTIVE_DATE',to_char(trunc(sysdate),'RRRR-MM-DD'));
1711 --WF_ENGINE.setitemattrtext(p_itemtype, l_item_key,'P_EFFECTIVE_DATE',trunc(sysdate));
1712 WF_ENGINE.setitemattrDate(p_itemtype, l_item_key,'CURRENT_EFFECTIVE_DATE',trunc(sysdate));
1713 -- Get and set owner role
1714
1715 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
1716
1717 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
1718 p_orig_system_id => l_creator_person_id,
1719 p_name =>l_role_name,
1720 p_display_name =>l_role_display_name);
1721
1722
1723 WF_ENGINE.SetItemOwner(
1724 itemtype => l_item_type,
1725 itemkey =>l_item_key,
1726 owner =>l_role_name);
1727
1728 hr_utility.set_location('After Setting Owner'||l_proc, 10);
1729
1730 --modified for bug 7308160
1731
1732 /*l_person_details := ota_learner_enroll_ss.Get_Person_To_Enroll_Details(p_person_id => p_person_id);
1733
1734 IF l_person_details.full_name is not null then
1735 WF_ENGINE.setitemattrtext(l_item_type,
1736 l_item_key,
1737 'CURRENT_PERSON_DISPLAY_NAME',
1738 l_person_details.full_name);
1739 WF_ENGINE.setitemattrtext(l_item_type,
1740 l_item_key,
1741 'CREATOR_PERSON_DISPLAY_NAME',
1742 l_person_details.full_name);
1743 END IF;*/
1744
1745 IF l_person_full_name is not null then
1746 WF_ENGINE.setitemattrtext(l_item_type,
1747 l_item_key,
1748 'CURRENT_PERSON_DISPLAY_NAME',
1749 l_person_full_name);
1750 WF_ENGINE.setitemattrtext(l_item_type,
1751 l_item_key,
1752 'CREATOR_PERSON_DISPLAY_NAME',
1753 l_person_full_name);
1754 END IF;
1755
1756
1757 HR_UTILITY.TRACE ('item key: ' || l_item_key);
1758
1759
1760
1761
1762 --start a transaction and save data to transaction tables
1763 save_Comptence_info(
1764 p_person_id =>p_person_id ,
1765 p_item_type => l_item_type,
1766 p_item_key => l_item_key,
1767 p_Competence_id => l_comp_ids,
1768 p_level_id => l_level_ids,
1769 p_date_from =>l_eff_date_from,
1770 p_date_to => l_eff_date_to);
1771
1772
1773 hr_utility.set_location('before supervisor'||l_proc, 30);
1774 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_CUSTOM_RETURN_FOR_CORR','Y');
1775
1776 --always set to Y
1777 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_RUNTIME_APPROVAL_REQ_FLAG', 'YES');
1778 hr_utility.set_location('before supervisor'||l_proc, 40);
1779 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'P_ASSIGNMENT_ID', l_assignment);
1780 hr_utility.set_location('before supervisor'||l_proc, 50);
1781 WF_ENGINE.setitemattrNumber(l_item_type, l_item_key, 'CURRENT_ASSIGNMENT_ID', to_number(l_assignment));
1782
1783 if p_eventId is not null then
1784 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'OTA_EVENT_TITLE', l_event_name);
1785 end if;
1786
1787 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'OTA_ACTIVITY_VERSION_NAME', l_course_name);
1788 --WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_AME_TRAN_TYPE_ATTR','SSHRMS');
1789 -- bug 3483960
1790 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_AME_TRAN_TYPE_ATTR','OTA');
1791 WF_ENGINE.setitemattrNumber(l_item_type, l_item_key, 'HR_AME_APP_ID_ATTR', 810);
1792 --WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'TRAN_SUBMIT','Y');
1793 --WF_ENGINE.SetItemattrtext(p_itemtype,p_item_key, 'EVENT_OWNER',l_user_name);
1794 hr_utility.set_location('before supervisor'||l_proc, 20);
1795
1796 FOR a IN csr_supervisor_id LOOP
1797 l_supervisor_id := a.supervisor_id;
1798 l_supervisor_full_name := a.full_name;
1799 END LOOP;
1800
1801
1802 FOR b IN csr_supervisor_user LOOP
1803 l_supervisor_username := b.user_name;
1804 END LOOP;
1805
1806 hr_utility.set_location('after supervisor cursor'||l_proc, 20);
1807
1808 wf_engine.setitemattrtext
1809 (l_item_type,
1810 l_item_key,
1811 'SUPERVISOR_USERNAME',
1812 l_supervisor_username);
1813 hr_utility.set_location('after supervisor username'||l_proc, 20);
1814
1815 wf_engine.setitemattrtext
1816 (l_item_type,
1817 l_item_key,
1818 'SUPERVISOR_DISPLAY_NAME',
1819 l_supervisor_full_name);
1820 hr_utility.set_location('after supervisor disp name'||l_proc, 20);
1821 wf_engine.setitemattrtext
1822 (l_item_type,
1823 l_item_key,
1824 'SUPERVISOR_ID',
1825 l_supervisor_id);
1826 hr_utility.set_location('before start process'||l_proc, 20);
1827 if p_learningpath_ids is not null then
1828
1829 /*hr_approval_wf.create_item_attrib_if_notexist
1830 (p_item_type => l_item_type
1831 ,p_item_key => l_item_key
1832 ,p_name => 'OTA_LP_ID');*/
1833
1834 WF_ENGINE.setitemattrnumber(l_item_type,
1835 l_item_key,
1836 'BOOKING_ID',
1837 p_learningpath_ids);
1838
1839 elsif p_eventId is not null then
1840
1841
1842 WF_ENGINE.setitemattrnumber(l_item_type,
1843 l_item_key,
1844 'EVENT_ID',
1845 p_eventid);
1846 else
1847
1848 WF_ENGINE.setitemattrnumber(l_item_type,
1849 l_item_key,
1850 'RECRUITER_ID',
1851 p_certification_id);
1852 end if;
1853 WF_ENGINE.STARTPROCESS(p_itemtype,l_item_key);
1854
1855 end if;
1856 else
1857 CLOSE chk_person_business_grp;
1858 end if;--chk_person_business_grp
1859
1860 p_itemkey:=l_item_key;
1861
1862
1863 hr_utility.set_location('leaving:'||l_proc, 20);
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1867 -- Raise;
1868
1869
1870
1871 end create_wf_process;
1872
1873 -- ---------------------------------------------------------------------------
1874 -- |----------------------< generate_url >--------------------------|
1875 -- ---------------------------------------------------------------------------
1876 --
1877
1878 Function generate_url(p_func varchar2) return varchar2
1879 is
1880
1881 l_proc varchar2(72) := g_package||'generate_url';
1882 l_jsp_apps_agent varchar2(2000);
1883 l_url varchar2(4000);
1884 l_amp varchar2(2) := '&';
1885 l_func varchar2(80);
1886
1887 begin
1888 hr_utility.set_location('Entering'||l_proc, 5);
1889
1890 --l_jsp_apps_agent := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1891 --HR_UTILITY.TRACE ('Agent :: ' || l_jsp_apps_agent);
1892 if p_func='APPROVAL' then
1893 l_func:='OTA_ADMIN_COMPETENCE_UPDATE';
1894 else
1895 l_func:='OTA_ADMIN_COMPETENCE_VIEW';
1896
1897 end if;
1898
1899 l_url := 'JSP:' || '/OA_HTML/OA.jsp?OAFunc=' || l_func || l_amp || 'NtfId=-&#NID-' || l_amp || 'retainAM=Y';
1900
1901 HR_UTILITY.TRACE ('URL :: ' || l_url);
1902
1903 return l_url;
1904
1905 hr_utility.set_location('Leaving'||l_proc, 5);
1906 end generate_url;
1907
1908 -- ---------------------------------------------------------------------------
1909 -- |----------------------< COMP_RETREIVE >--------------------------|
1910 -- ---------------------------------------------------------------------------
1911 --
1912 PROCEDURE COMP_RETREIVE ( p_event_id IN NUMBER
1913 , p_learning_path_ids IN VARCHAR2
1914 , p_certification_id IN Number
1915 , p_person_id in number
1916 , p_comp_ids OUT NOCOPY VARCHAR2
1917 , p_level_ids OUT NOCOPY VARCHAR2
1918 ,p_eff_date_from out nocopy varchar2
1919 ,p_eff_date_to out nocopy varchar2) IS
1920
1921
1922 l_learning_path_id NUMBER;
1923 l_leftpos NUMBER := 1;
1924 l_rightpos NUMBER ;
1925 l_count NUMBER := 1;
1926 l_learning_path_ids VARCHAR2(4000);
1927 l_eff_date_from varchar2(4000);
1928 l_expiry_date ota_cert_enrollments.expiration_date%type;
1929 l_eff_date_to varchar2(4000);
1930
1931 l_proc varchar2(72) := g_package||'COMP_RETREIVE';
1932
1933 CURSOR csr_get_crs_comps IS
1934 SELECT pce.competence_id CompetenceId
1935 ,nvl(pce.proficiency_level_id,-1) LevelId
1936 FROM per_competence_elements pce
1937 , ota_offerings OFF
1938 , ota_events EVT
1939 WHERE off.activity_version_id = pce.activity_version_id
1940 AND evt.parent_offering_id = off.offering_id
1941 AND evt.event_id = p_event_id;
1942
1943 CURSOR csr_get_lp_comps IS
1944 SELECT pce.competence_id CompetenceId
1945 ,nvl(pce.proficiency_level_id,-1) LevelId
1946 FROM per_competence_elements pce
1947 WHERE pce.type = 'OTA_LEARNING_PATH'
1948 AND pce.object_id = l_learning_path_id;
1949
1950 CURSOR csr_get_cert_comps IS
1951 SELECT pce.competence_id CompetenceId
1952 ,nvl(pce.proficiency_level_id,-1) LevelId
1953 FROM per_competence_elements pce
1954 WHERE pce.type = 'OTA_CERTIFICATION' ---Batra to revisit*********************
1955 AND pce.object_id = p_certification_id;
1956
1957 Cursor get_cert_expiry is
1958 select cenr.expiration_date
1959 from ota_cert_enrollments cenr
1960 where certification_id = p_certification_id
1961 and person_id = p_person_id;
1962 -- and certification_status_code ='CERTIFIED';--not ok?//////////////
1963
1964
1965 BEGIN
1966
1967 hr_utility.set_location('Entering:'||l_proc, 5);
1968 IF p_learning_path_ids = '' or p_learning_path_ids is null THEN
1969 l_learning_path_ids := NULL;
1970 ELSE
1971 l_learning_path_ids:= p_learning_path_ids;
1972 END IF;
1973 IF p_event_id IS not NULL THEN
1974 hr_utility.set_location('l_:'||l_proc, 10);
1975 FOR crs_comp_rec IN csr_get_crs_comps LOOP
1976 IF p_comp_ids IS NULL OR p_comp_ids = '' THEN
1977 p_comp_ids := crs_comp_rec.CompetenceId;
1978 p_level_ids := crs_comp_rec.LevelId;
1979 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) ;
1980 ELSE
1981 p_comp_ids := p_comp_ids || '^' || crs_comp_rec.CompetenceId;
1982 p_level_ids := p_level_ids || '^' || crs_comp_rec.LevelId;
1983 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) ;
1984 END IF;
1985 END LOOP;
1986 END IF;
1987
1988 IF l_learning_path_ids IS NOT NULL THEN
1989 hr_utility.set_location('Entering:'||l_proc, 15);
1990 /* LOOP
1991 l_rightpos := INSTR(p_learning_path_ids,'^',1,l_count);
1992 IF l_rightpos = 0 THEN
1993 l_learning_path_id := to_number(SUBSTR(l_learning_path_ids,l_leftpos,length(p_learning_path_ids) - l_leftpos +1));
1994 ELSE
1995 l_learning_path_id := to_number(SUBSTR(l_learning_path_ids,l_leftpos,l_rightpos - l_leftpos));
1996 END IF;
1997 l_leftpos := l_rightpos + 1;
1998 l_count := l_count +1; */
1999
2000 l_learning_path_id := to_number(l_learning_path_ids);
2001 hr_utility.trace ('l_learning_path_id ' ||l_learning_path_id);
2002 FOR lps_comp_rec IN csr_get_lp_comps LOOP
2003 hr_utility.set_location('Entering:'||l_proc, 20);
2004 IF p_comp_ids IS NULL OR p_comp_ids = '' THEN
2005 hr_utility.set_location('Entering:'||l_proc, 25);
2006 p_comp_ids := lps_comp_rec.CompetenceId;
2007 p_level_ids := lps_comp_rec.LevelId;
2008 hr_utility.set_location('Entering:'||l_proc, 35);
2009 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) ;
2010 hr_utility.set_location('Entering:'||l_proc, 45);
2011
2012 ELSE
2013 p_comp_ids := p_comp_ids || '^' || lps_comp_rec.CompetenceId;
2014 p_level_ids := p_level_ids || '^' || lps_comp_rec.LevelId;
2015 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) ;
2016
2017 END IF;
2018 END LOOP;
2019 -- dbms_output.put_line('Learning Path Id ' || l_learning_path_id);
2020 -- EXIT WHEN l_rightpos = 0;
2021 --END LOOP;
2022 END IF;
2023
2024 IF p_certification_id IS not NULL THEN ---Batra to revisst ***************888888
2025
2026 open get_cert_expiry;
2027 fetch get_cert_expiry into l_expiry_date;
2028 close get_cert_expiry;
2029
2030
2031
2032 FOR crs_cert_rec IN csr_get_cert_comps LOOP
2033 IF p_comp_ids IS NULL OR p_comp_ids = '' THEN
2034 p_comp_ids := crs_cert_rec.CompetenceId;
2035 p_level_ids := crs_cert_rec.LevelId;
2036 l_eff_date_from := to_char(trunc(sysdate),g_date_format);
2037 if l_expiry_date is not null then
2038 l_eff_date_to := to_char(l_expiry_date,g_date_format );
2039 end if;
2040 ELSE
2041 p_comp_ids := p_comp_ids || '^' || crs_cert_rec.CompetenceId;
2042 p_level_ids := p_level_ids || '^' || crs_cert_rec.LevelId;
2043 -- start date would be the date certification is completed
2044 l_eff_date_from :=l_eff_date_from || '^' ||to_char(trunc(sysdate),g_date_format);
2045 if l_expiry_date is not null then
2046 l_eff_date_to := l_eff_date_to || '^' ||to_char(l_expiry_date,g_date_format );
2047 end if;
2048 END IF;
2049 END LOOP;
2050 END IF;
2051 p_eff_date_from :=l_eff_date_from;
2052 p_eff_date_to := l_eff_date_to;
2053
2054 hr_utility.set_location('Leaving:'||l_proc, 5);
2055
2056 Exception
2057
2058 when others then
2059 raise;
2060
2061 END comp_retreive;
2062
2063
2064 end ota_Competence_ss;
2065
2066