[Home] [Help]
164: l_count Number:=0;
165: x_trans_ovn Number;
166: l_review_region Varchar2(200);
167: Begin
168: hr_utility.set_location(' Entering:' || l_proc,5);
169: If p_trans_step_id is null then
170: hr_utility.set_location(l_proc,10);
171: hr_transaction_api.create_transaction_step
172: (p_validate => false
166: l_review_region Varchar2(200);
167: Begin
168: hr_utility.set_location(' Entering:' || l_proc,5);
169: If p_trans_step_id is null then
170: hr_utility.set_location(l_proc,10);
171: hr_transaction_api.create_transaction_step
172: (p_validate => false
173: ,p_creator_person_id => p_login_person_id
174: ,p_transaction_id => p_trans_id
178: ,p_activity_id => p_actid
179: ,p_transaction_step_id => p_trans_step_id
180: ,p_object_version_number => x_trans_ovn);
181: End if;
182: hr_utility.set_location(l_proc,15);
183: l_count := 1;
184: l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
185: l_trans_tbl(l_count).param_value := p_person_id;
186: l_trans_tbl(l_count).param_data_type := 'NUMBER';
264: l_trans_tbl(l_count).param_name := 'P_SYSTEM_GENERATED';
265: l_trans_tbl(l_count).param_value := p_sys_generated;
266: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
267: If p_upg_from_rec_id <> -1 then
268: hr_utility.set_location(l_proc,20);
269: l_count := l_count + 1;
270: l_trans_tbl(l_count).param_name := 'P_UPGRADED_FROM_REC_ID';
271: l_trans_tbl(l_count).param_value := p_upg_from_rec_id;
272: l_trans_tbl(l_count).param_data_type := 'NUMBER';
270: l_trans_tbl(l_count).param_name := 'P_UPGRADED_FROM_REC_ID';
271: l_trans_tbl(l_count).param_value := p_upg_from_rec_id;
272: l_trans_tbl(l_count).param_data_type := 'NUMBER';
273: End if;
274: hr_utility.set_location(l_proc,25);
275: l_count := l_count + 1;
276: l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
277: l_trans_tbl(l_count).param_value := p_actid;
278: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
276: l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
277: l_trans_tbl(l_count).param_value := p_actid;
278: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
279: /* start code for competence Qualification link enhancement */
280: hr_utility.set_location(l_proc,26);
281: l_count := l_count + 1;
282: l_trans_tbl(l_count).param_name := 'P_STATUS';
283: l_trans_tbl(l_count).param_value := p_status;
284: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
281: l_count := l_count + 1;
282: l_trans_tbl(l_count).param_name := 'P_STATUS';
283: l_trans_tbl(l_count).param_value := p_status;
284: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
285: hr_utility.set_location(l_proc,27);
286: l_count := l_count + 1;
287: l_trans_tbl(l_count).param_name := 'P_PREV_STATUS';
288: l_trans_tbl(l_count).param_value := p_prev_status;
289: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
293: ,itemkey => p_item_key
294: ,actid => p_actid
295: ,aname => 'HR_REVIEW_REGION_ITEM'
296: ,ignore_notfound => true);
297: hr_utility.set_location(l_proc,30);
298: l_count := l_count + 1;
299: l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
300: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
301: l_trans_tbl(l_count).param_value := l_review_region;
305: ,p_login_person_id => p_login_person_id
306: ,p_transaction_step_id => p_trans_step_id
307: ,p_api_name => p_api_name
308: ,p_transaction_data => l_trans_tbl);
309: hr_utility.set_location(' Leaving:' || l_proc,35);
310: End writeTo_transTbl;
311: -- ***** Start new code for bug 2743410 **************
312: -- ---------------------------------------------------------------------------
313: -- ---------------------------- < comp_not_exists > --------------------------
342: Where c.transaction_step_id = ts.transaction_step_id
343: and c.name = 'P_COMPETENCE_ID'
344: and c.number_value = p_competence_id);
345: begin
346: hr_utility.set_location(' Entering:' || l_proc,5);
347: For I in c1 Loop
348: hr_utility.set_location( l_proc,10);
349: If (I.status = 'F') then
350: l_retStatus := I.status;
344: and c.number_value = p_competence_id);
345: begin
346: hr_utility.set_location(' Entering:' || l_proc,5);
347: For I in c1 Loop
348: hr_utility.set_location( l_proc,10);
349: If (I.status = 'F') then
350: l_retStatus := I.status;
351: hr_utility.set_location(' Leaving:' || l_proc,15);
352: Exit;
347: For I in c1 Loop
348: hr_utility.set_location( l_proc,10);
349: If (I.status = 'F') then
350: l_retStatus := I.status;
351: hr_utility.set_location(' Leaving:' || l_proc,15);
352: Exit;
353: End If;
354: End Loop;
355: hr_utility.set_location(' Leaving:' || l_proc,20);
351: hr_utility.set_location(' Leaving:' || l_proc,15);
352: Exit;
353: End If;
354: End Loop;
355: hr_utility.set_location(' Leaving:' || l_proc,20);
356: return l_retStatus;
357: Exception when others then
358: hr_utility.set_location(' Leaving:' || l_proc,555);
359: return 'T';
354: End Loop;
355: hr_utility.set_location(' Leaving:' || l_proc,20);
356: return l_retStatus;
357: Exception when others then
358: hr_utility.set_location(' Leaving:' || l_proc,555);
359: return 'T';
360: end comp_not_exists;
361: -- ***** End new code for bug 2743410 **************
362: -- ---------------------------------------------------------------------------
410: l_obj_ver_num number default null;
411: l_mode_value varchar2(20) default null;
412: ----------------------------------
413: Begin
414: hr_utility.set_location(' Entering:' || l_proc,5);
415: l_user_date_format := g_date_format ;
416: g_business_group_id := hr_util_misc_web.get_business_group_id(p_person_id => p_person_id);
417: l_mode_value := p_change_mode;
418: l_eff_date_from := to_date(p_eff_date_from,l_user_date_format);
418: l_eff_date_from := to_date(p_eff_date_from,l_user_date_format);
419: l_competence_element_id := p_competence_element_id;
420: l_obj_ver_num := p_preupd_obj_vers_num;
421: if p_transaction_step_id is not null then
422: hr_utility.set_location(l_proc,10);
423: select varchar2_value into l_mode_value
424: from hr_api_transaction_values
425: where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
426: end if;
423: select varchar2_value into l_mode_value
424: from hr_api_transaction_values
425: where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
426: end if;
427: hr_utility.set_location(l_proc,15);
428: If l_mode_value = 'UPGRADE' and p_transaction_step_id is not null then
429: hr_utility.set_location(l_proc,20);
430: select max(effective_date_from)
431: into l_eff_date_from
425: where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
426: end if;
427: hr_utility.set_location(l_proc,15);
428: If l_mode_value = 'UPGRADE' and p_transaction_step_id is not null then
429: hr_utility.set_location(l_proc,20);
430: select max(effective_date_from)
431: into l_eff_date_from
432: from per_competence_elements
433: where person_id = to_number(p_person_id)
438: where person_id = to_number(p_person_id)
439: and effective_date_from = l_eff_date_from
440: and competence_id = p_competence_id;
441: End If;
442: hr_utility.set_location(l_proc,25);
443: api_validate_competence_record
444: (p_validate => true
445: ,p_person_id => p_person_id
446: ,p_business_group_id => g_business_group_id
460: ,p_eff_date_to_date_type => x_eff_date_to
461: ,p_certifctn_date_type => x_certification_date
462: ,p_next_certifctn_date_type => x_next_certifctn_date
463: ,p_error_message => p_error_message);
464: hr_utility.set_location(l_proc,30);
465: if p_error_message is not null then
466: hr_utility.set_location(' Leaving:' || l_proc,35);
467: Return;
468: end if;
462: ,p_next_certifctn_date_type => x_next_certifctn_date
463: ,p_error_message => p_error_message);
464: hr_utility.set_location(l_proc,30);
465: if p_error_message is not null then
466: hr_utility.set_location(' Leaving:' || l_proc,35);
467: Return;
468: end if;
469: hr_utility.set_location(l_proc,40);
470: l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type => p_item_type
465: if p_error_message is not null then
466: hr_utility.set_location(' Leaving:' || l_proc,35);
467: Return;
468: end if;
469: hr_utility.set_location(l_proc,40);
470: l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type => p_item_type
471: ,p_item_key => p_item_key);
472: IF l_transaction_id is null THEN
473: hr_utility.set_location(l_proc,45);
469: hr_utility.set_location(l_proc,40);
470: l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type => p_item_type
471: ,p_item_key => p_item_key);
472: IF l_transaction_id is null THEN
473: hr_utility.set_location(l_proc,45);
474: -- Start a Transaction
475: hr_transaction_ss.start_transaction
476: (itemtype => p_item_type
477: ,itemkey => p_item_key
482: l_transaction_id := hr_transaction_ss.get_transaction_id
483: (p_item_type => p_item_type
484: ,p_item_key => p_item_key);
485: END IF;
486: hr_utility.set_location(l_proc,50);
487: IF p_change_mode = 'UPGRADE' THEN
488: hr_utility.set_location(l_proc,55);
489: open g_csr_get_preupd_cmptnce_rec(p_competence_element_id);
490: Fetch g_csr_get_preupd_cmptnce_rec into l_preupd_cmptnce_row;
484: ,p_item_key => p_item_key);
485: END IF;
486: hr_utility.set_location(l_proc,50);
487: IF p_change_mode = 'UPGRADE' THEN
488: hr_utility.set_location(l_proc,55);
489: open g_csr_get_preupd_cmptnce_rec(p_competence_element_id);
490: Fetch g_csr_get_preupd_cmptnce_rec into l_preupd_cmptnce_row;
491: IF g_csr_get_preupd_cmptnce_rec%notfound THEN
492: close g_csr_get_preupd_cmptnce_rec;
491: IF g_csr_get_preupd_cmptnce_rec%notfound THEN
492: close g_csr_get_preupd_cmptnce_rec;
493: raise hr_comp_profile_ss.g_fatal_error;
494: END IF;
495: hr_utility.set_location(l_proc,60);
496: close g_csr_get_preupd_cmptnce_rec;
497: writeTo_transTbl(p_item_type => p_item_type
498: ,p_item_key => p_item_key
499: ,p_actid => to_number(p_actid)
525: ,p_upg_from_rec_id => -1
526: ,p_prev_status => l_preupd_cmptnce_row.status
527: ,p_status => p_competence_status);
528: l_transaction_step_id := Null;
529: hr_utility.set_location(l_proc,65);
530: writeTo_transTbl(p_item_type => p_item_type
531: ,p_item_key => p_item_key
532: ,p_actid => to_number(p_actid)
533: ,p_login_person_id => fnd_global.employee_id
558: ,p_upg_from_rec_id => p_competence_element_id
559: ,p_prev_status => null
560: ,p_status => p_competence_status);
561: ELSIF p_change_mode = hr_comp_profile_ss.g_upd_mode THEN
562: hr_utility.set_location(l_proc,70);
563: writeTo_transTbl(p_item_type => p_item_type
564: ,p_item_key => p_item_key
565: ,p_actid => to_number(p_actid)
566: ,p_login_person_id => fnd_global.employee_id
590: ,p_sys_generated => 'N'
591: ,p_upg_from_rec_id => -1
592: ,p_status => p_competence_status);
593: if l_mode_value = 'UPGRADE' then
594: hr_utility.set_location(l_proc,75);
595: select number_value
596: into l_competence_element_id
597: from hr_api_transaction_values
598: where transaction_step_id = p_transaction_step_id
614: set date_value = (x_eff_date_from - 1)
615: where transaction_step_id = l_transaction_step_id
616: and name = 'P_EFF_DATE_TO';
617: end if;
618: hr_utility.set_location(l_proc,80);
619: ELSIF p_change_mode = 'UPDATE_UPDATE' THEN
620: hr_utility.set_location(l_proc,85);
621: writeTo_transTbl(p_item_type => p_item_type
622: ,p_item_key => p_item_key
616: and name = 'P_EFF_DATE_TO';
617: end if;
618: hr_utility.set_location(l_proc,80);
619: ELSIF p_change_mode = 'UPDATE_UPDATE' THEN
620: hr_utility.set_location(l_proc,85);
621: writeTo_transTbl(p_item_type => p_item_type
622: ,p_item_key => p_item_key
623: ,p_actid => to_number(p_actid)
624: ,p_login_person_id => fnd_global.employee_id
648: ,p_sys_generated => 'N'
649: ,p_upg_from_rec_id => -1
650: ,p_status => p_competence_status);
651: ELSE
652: hr_utility.set_location(l_proc,90);
653: writeTo_transTbl(p_item_type => p_item_type
654: ,p_item_key => p_item_key
655: ,p_actid => to_number(p_actid)
656: ,p_login_person_id => fnd_global.employee_id
680: ,p_sys_generated => 'N'
681: ,p_upg_from_rec_id => -1
682: ,p_status => p_competence_status);
683: END IF;
684: hr_utility.set_location(' Leaving:' || l_proc,95);
685: EXCEPTION
686: When g_invalid_entry then
687: hr_utility.set_location(' Leaving:' || l_proc,555);
688: raise g_invalid_entry;
683: END IF;
684: hr_utility.set_location(' Leaving:' || l_proc,95);
685: EXCEPTION
686: When g_invalid_entry then
687: hr_utility.set_location(' Leaving:' || l_proc,555);
688: raise g_invalid_entry;
689: WHEN hr_comp_profile_ss.g_data_err THEN
690: hr_utility.set_location(' Leaving:' || l_proc,560);
691: raise hr_utility.hr_error;
686: When g_invalid_entry then
687: hr_utility.set_location(' Leaving:' || l_proc,555);
688: raise g_invalid_entry;
689: WHEN hr_comp_profile_ss.g_data_err THEN
690: hr_utility.set_location(' Leaving:' || l_proc,560);
691: raise hr_utility.hr_error;
692: WHEN hr_comp_profile_ss.g_access_violation_err THEN
693: hr_utility.set_location(' Leaving:' || l_proc,565);
694: raise hr_utility.hr_error;
687: hr_utility.set_location(' Leaving:' || l_proc,555);
688: raise g_invalid_entry;
689: WHEN hr_comp_profile_ss.g_data_err THEN
690: hr_utility.set_location(' Leaving:' || l_proc,560);
691: raise hr_utility.hr_error;
692: WHEN hr_comp_profile_ss.g_access_violation_err THEN
693: hr_utility.set_location(' Leaving:' || l_proc,565);
694: raise hr_utility.hr_error;
695: When others THEN
689: WHEN hr_comp_profile_ss.g_data_err THEN
690: hr_utility.set_location(' Leaving:' || l_proc,560);
691: raise hr_utility.hr_error;
692: WHEN hr_comp_profile_ss.g_access_violation_err THEN
693: hr_utility.set_location(' Leaving:' || l_proc,565);
694: raise hr_utility.hr_error;
695: When others THEN
696: hr_utility.set_location(' Leaving:' || l_proc,570);
697: raise g_invalid_entry ;
690: hr_utility.set_location(' Leaving:' || l_proc,560);
691: raise hr_utility.hr_error;
692: WHEN hr_comp_profile_ss.g_access_violation_err THEN
693: hr_utility.set_location(' Leaving:' || l_proc,565);
694: raise hr_utility.hr_error;
695: When others THEN
696: hr_utility.set_location(' Leaving:' || l_proc,570);
697: raise g_invalid_entry ;
698: End process_save;
692: WHEN hr_comp_profile_ss.g_access_violation_err THEN
693: hr_utility.set_location(' Leaving:' || l_proc,565);
694: raise hr_utility.hr_error;
695: When others THEN
696: hr_utility.set_location(' Leaving:' || l_proc,570);
697: raise g_invalid_entry ;
698: End process_save;
699: --
700: -- ---------------------------------------------------------------------------
753: l_preupd_proficy_lvl_id number := null;
754: l_prev_step_value number := null;
755: ---
756: Begin
757: hr_utility.set_location(' Entering:' || l_proc,5);
758: if (p_preupd_proficy_lvl_id <> -1) then
759: hr_utility.set_location(l_proc,10);
760: l_preupd_proficy_lvl_id := p_preupd_proficy_lvl_id;
761: end if;
755: ---
756: Begin
757: hr_utility.set_location(' Entering:' || l_proc,5);
758: if (p_preupd_proficy_lvl_id <> -1) then
759: hr_utility.set_location(l_proc,10);
760: l_preupd_proficy_lvl_id := p_preupd_proficy_lvl_id;
761: end if;
762: hr_utility.set_location(l_proc,15);
763: if (p_prev_step_value <> -1) then
758: if (p_preupd_proficy_lvl_id <> -1) then
759: hr_utility.set_location(l_proc,10);
760: l_preupd_proficy_lvl_id := p_preupd_proficy_lvl_id;
761: end if;
762: hr_utility.set_location(l_proc,15);
763: if (p_prev_step_value <> -1) then
764: hr_utility.set_location(l_proc,20);
765: l_prev_step_value := p_prev_step_value;
766: end if;
760: l_preupd_proficy_lvl_id := p_preupd_proficy_lvl_id;
761: end if;
762: hr_utility.set_location(l_proc,15);
763: if (p_prev_step_value <> -1) then
764: hr_utility.set_location(l_proc,20);
765: l_prev_step_value := p_prev_step_value;
766: end if;
767: hr_utility.set_location(l_proc,25);
768: l_action_person_id := p_person_id;
763: if (p_prev_step_value <> -1) then
764: hr_utility.set_location(l_proc,20);
765: l_prev_step_value := p_prev_step_value;
766: end if;
767: hr_utility.set_location(l_proc,25);
768: l_action_person_id := p_person_id;
769: g_business_group_id := hr_util_misc_web.get_business_group_id
770: (p_person_id => l_action_person_id);
771: --
772: l_user_date_format := g_date_format;
773: OPEN get_prev_start_date(p_competence_element_id);
774: FETCH get_prev_start_date into l_prev_date;
775: IF get_prev_start_date%notfound THEN
776: hr_utility.set_location(l_proc,30);
777: close get_comp_name;
778: raise hr_comp_profile_ss.g_fatal_error;
779: END IF;
780: close get_prev_start_date;
778: raise hr_comp_profile_ss.g_fatal_error;
779: END IF;
780: close get_prev_start_date;
781: l_prev_start_date := l_prev_date;
782: hr_utility.set_location(l_proc,35);
783: IF p_eff_date_from is not null THEN
784: hr_utility.set_location(l_proc,40);
785: l_eff_date_from := to_date(p_eff_date_from, l_user_date_format);
786: END IF;
780: close get_prev_start_date;
781: l_prev_start_date := l_prev_date;
782: hr_utility.set_location(l_proc,35);
783: IF p_eff_date_from is not null THEN
784: hr_utility.set_location(l_proc,40);
785: l_eff_date_from := to_date(p_eff_date_from, l_user_date_format);
786: END IF;
787: IF p_eff_date_to is not null THEN
788: hr_utility.set_location(l_proc,45);
784: hr_utility.set_location(l_proc,40);
785: l_eff_date_from := to_date(p_eff_date_from, l_user_date_format);
786: END IF;
787: IF p_eff_date_to is not null THEN
788: hr_utility.set_location(l_proc,45);
789: l_eff_date_to := to_date(p_eff_date_to, l_user_date_format);
790: END IF;
791: IF p_certification_date is not null THEN
792: hr_utility.set_location(l_proc,50);
788: hr_utility.set_location(l_proc,45);
789: l_eff_date_to := to_date(p_eff_date_to, l_user_date_format);
790: END IF;
791: IF p_certification_date is not null THEN
792: hr_utility.set_location(l_proc,50);
793: l_certification_date := to_date(p_certification_date, l_user_date_format);
794: END IF;
795: IF p_next_certifctn_date is not null THEN
796: hr_utility.set_location(l_proc,55);
792: hr_utility.set_location(l_proc,50);
793: l_certification_date := to_date(p_certification_date, l_user_date_format);
794: END IF;
795: IF p_next_certifctn_date is not null THEN
796: hr_utility.set_location(l_proc,55);
797: l_next_certifctn_date := to_date(p_next_certifctn_date, l_user_date_format);
798: END IF;
799: hr_utility.set_location(l_proc,60);
800: l_transaction_id := hr_transaction_ss.get_transaction_id
795: IF p_next_certifctn_date is not null THEN
796: hr_utility.set_location(l_proc,55);
797: l_next_certifctn_date := to_date(p_next_certifctn_date, l_user_date_format);
798: END IF;
799: hr_utility.set_location(l_proc,60);
800: l_transaction_id := hr_transaction_ss.get_transaction_id
801: (p_item_type => p_item_type
802: ,p_item_key => p_item_key);
803: IF l_transaction_id is null THEN
800: l_transaction_id := hr_transaction_ss.get_transaction_id
801: (p_item_type => p_item_type
802: ,p_item_key => p_item_key);
803: IF l_transaction_id is null THEN
804: hr_utility.set_location(l_proc,65);
805: hr_transaction_ss.start_transaction
806: (itemtype => p_item_type
807: ,itemkey => p_item_key
808: ,actid => to_number(p_actid)
814: ,p_item_key => p_item_key);
815: END IF;
816: --
817: IF transaction_step_id is null then
818: hr_utility.set_location(l_proc,70);
819: hr_transaction_api.create_transaction_step
820: (p_validate => false
821: ,p_creator_person_id => fnd_global.employee_id
822: ,p_transaction_id => l_transaction_id
830: l_transaction_step_id := transaction_step_id;
831: end if;
832: --
833: IF p_change_mode = 'UPDATE_APPLY' THEN
834: hr_utility.set_location(l_proc,75);
835: Open g_csr_get_preupd_cmptnce_rec
836: (p_competence_element_id);
837: Fetch g_csr_get_preupd_cmptnce_rec into l_preupd_cmptnce_row;
838: IF g_csr_get_preupd_cmptnce_rec%notfound THEN
835: Open g_csr_get_preupd_cmptnce_rec
836: (p_competence_element_id);
837: Fetch g_csr_get_preupd_cmptnce_rec into l_preupd_cmptnce_row;
838: IF g_csr_get_preupd_cmptnce_rec%notfound THEN
839: hr_utility.set_location(l_proc,80);
840: close g_csr_get_preupd_cmptnce_rec;
841: raise hr_comp_profile_ss.g_fatal_error;
842: END IF;
843: --
841: raise hr_comp_profile_ss.g_fatal_error;
842: END IF;
843: --
844: close g_csr_get_preupd_cmptnce_rec;
845: hr_utility.set_location(l_proc,85);
846: l_preupd_date_to := l_eff_date_from - 1;
847: writeTo_transTbl(p_item_type => p_item_type
848: ,p_item_key => p_item_key
849: ,p_actid => to_number(p_actid)
874: ,p_sys_generated => 'Y'
875: ,p_upg_from_rec_id => -1
876: ,p_prev_status => l_preupd_cmptnce_row.status
877: ,p_status => p_competence_status);
878: hr_utility.set_location(l_proc,90);
879: hr_transaction_api.create_transaction_step
880: (p_validate => false
881: ,p_creator_person_id => fnd_global.employee_id
882: ,p_transaction_id => l_transaction_id
916: ,p_sys_generated => 'N'
917: ,p_upg_from_rec_id => p_competence_element_id
918: ,p_status => p_competence_status);
919: ELSIF p_change_mode = 'UPDATE_UPDATE' THEN
920: hr_utility.set_location(l_proc,95);
921: writeTo_transTbl(p_item_type => p_item_type
922: ,p_item_key => p_item_key
923: ,p_actid => to_number(p_actid)
924: ,p_login_person_id => fnd_global.employee_id
948: ,p_sys_generated => 'N'
949: ,p_upg_from_rec_id => -1
950: ,p_status => p_competence_status);
951: ELSE
952: hr_utility.set_location(l_proc,100);
953: writeTo_transTbl(p_item_type => p_item_type
954: ,p_item_key => p_item_key
955: ,p_actid => to_number(p_actid)
956: ,p_login_person_id => fnd_global.employee_id
980: ,p_sys_generated => 'N'
981: ,p_upg_from_rec_id => -1
982: ,p_status => p_competence_status);
983: END IF;
984: hr_utility.set_location(' Leaving:' || l_proc,105);
985: --
986: EXCEPTION
987: WHEN hr_comp_profile_ss.g_data_err THEN
988: hr_utility.set_location(' Leaving:' || l_proc,555);
984: hr_utility.set_location(' Leaving:' || l_proc,105);
985: --
986: EXCEPTION
987: WHEN hr_comp_profile_ss.g_data_err THEN
988: hr_utility.set_location(' Leaving:' || l_proc,555);
989: raise hr_utility.hr_error;
990: --
991: WHEN hr_comp_profile_ss.g_access_violation_err THEN
992: hr_utility.set_location(' Leaving:' || l_proc,560);
985: --
986: EXCEPTION
987: WHEN hr_comp_profile_ss.g_data_err THEN
988: hr_utility.set_location(' Leaving:' || l_proc,555);
989: raise hr_utility.hr_error;
990: --
991: WHEN hr_comp_profile_ss.g_access_violation_err THEN
992: hr_utility.set_location(' Leaving:' || l_proc,560);
993: raise hr_utility.hr_error;
988: hr_utility.set_location(' Leaving:' || l_proc,555);
989: raise hr_utility.hr_error;
990: --
991: WHEN hr_comp_profile_ss.g_access_violation_err THEN
992: hr_utility.set_location(' Leaving:' || l_proc,560);
993: raise hr_utility.hr_error;
994: --
995: When others THEN
996: hr_utility.set_location(' Leaving:' || l_proc,565);
989: raise hr_utility.hr_error;
990: --
991: WHEN hr_comp_profile_ss.g_access_violation_err THEN
992: hr_utility.set_location(' Leaving:' || l_proc,560);
993: raise hr_utility.hr_error;
994: --
995: When others THEN
996: hr_utility.set_location(' Leaving:' || l_proc,565);
997: raise hr_utility.hr_error;
992: hr_utility.set_location(' Leaving:' || l_proc,560);
993: raise hr_utility.hr_error;
994: --
995: When others THEN
996: hr_utility.set_location(' Leaving:' || l_proc,565);
997: raise hr_utility.hr_error;
998: End process_save_currentupdate;
999: -- ---------------------------- < process_api > ------------------------------
1000: -- ---------------------------------------------------------------------------
993: raise hr_utility.hr_error;
994: --
995: When others THEN
996: hr_utility.set_location(' Leaving:' || l_proc,565);
997: raise hr_utility.hr_error;
998: End process_save_currentupdate;
999: -- ---------------------------- < process_api > ------------------------------
1000: -- ---------------------------------------------------------------------------
1001: -- Purpose: This procedure reads the data from transaction table and saves it
1023: l_status per_competence_elements.status%type;
1024: --
1025: Begin
1026: --
1027: hr_utility.set_location(' Entering:' || l_proc,5);
1028: l_cmp_element_rec.type := 'PERSONAL';
1029: --
1030: l_cmp_element_rec.business_group_id :=
1031: hr_transaction_api.get_number_value
1146: l_cmp_element_rec.achieved_date := null;
1147: END if;
1148: IF l_system_generated = 'N' AND
1149: l_upgraded_from_rec_id is not null THEN
1150: hr_utility.set_location(l_proc,10);
1151: l_cmp_element_rec.competence_element_id := null;
1152: END IF;
1153: --
1154: -- set a savepoint before calling api
1161: IF l_cmp_element_rec.competence_element_id IS NOT NULL then
1162: l_cmp_element_id := l_cmp_element_rec.competence_element_id;
1163: END if;
1164: END if;
1165: hr_utility.set_location(l_proc,15);
1166: IF l_system_generated = 'Y' AND
1167: l_cmp_element_rec.competence_element_id is not null THEN
1168: hr_utility.set_location(l_proc,20);
1169: -- for comp qual link enahancement
1164: END if;
1165: hr_utility.set_location(l_proc,15);
1166: IF l_system_generated = 'Y' AND
1167: l_cmp_element_rec.competence_element_id is not null THEN
1168: hr_utility.set_location(l_proc,20);
1169: -- for comp qual link enahancement
1170: -- Adding the Status field.
1171:
1172: per_cel_upd.upd
1192: ,p_person_id => l_cmp_element_rec.person_id
1193: ,p_effective_date => trunc(sysdate)); -- for comp qual link enahancement
1194: END if;
1195: ELSIF l_cmp_element_rec.competence_element_id is not null THEN
1196: hr_utility.set_location(l_proc,25);
1197: Select pce.status, Achieved_date INTO l_status, l_achieved_date
1198: FROM
1199: per_competence_elements pce
1200: Where pce.competence_element_id = l_cmp_element_rec.competence_element_id;
1233: ,p_person_id => l_cmp_element_rec.person_id
1234: ,p_effective_date => trunc(sysdate)); -- for comp qual link enahancement
1235: END if;
1236: ELSE
1237: hr_utility.set_location(l_proc,30);
1238: per_cel_ins.ins
1239: (p_validate => False
1240: ,p_competence_element_id => l_cmp_element_rec.competence_element_id
1241: ,p_business_group_id => l_cmp_element_rec.business_group_id
1271: ,p_effective_date => trunc(sysdate)); -- for comp qual link enahancement
1272: END IF;
1273: --
1274: --
1275: hr_utility.set_location(l_proc,35);
1276: IF p_validate = true THEN
1277: hr_utility.set_location(l_proc,40);
1278: rollback to save_competence_element;
1279: END IF;
1273: --
1274: --
1275: hr_utility.set_location(l_proc,35);
1276: IF p_validate = true THEN
1277: hr_utility.set_location(l_proc,40);
1278: rollback to save_competence_element;
1279: END IF;
1280: hr_utility.set_location(' Leaving:' || l_proc,45);
1281: --
1276: IF p_validate = true THEN
1277: hr_utility.set_location(l_proc,40);
1278: rollback to save_competence_element;
1279: END IF;
1280: hr_utility.set_location(' Leaving:' || l_proc,45);
1281: --
1282: Exception
1283: When hr_utility.hr_error THEN
1284: hr_utility.set_location(' Leaving:' || l_proc,555);
1279: END IF;
1280: hr_utility.set_location(' Leaving:' || l_proc,45);
1281: --
1282: Exception
1283: When hr_utility.hr_error THEN
1284: hr_utility.set_location(' Leaving:' || l_proc,555);
1285: rollback to save_competence_element;
1286: IF NOT (l_upgraded_from_rec_id IS NOT NULL AND
1287: hr_message.last_message_name = 'HR_51648_CEL_PER_DATES_OVLAP') THEN
1280: hr_utility.set_location(' Leaving:' || l_proc,45);
1281: --
1282: Exception
1283: When hr_utility.hr_error THEN
1284: hr_utility.set_location(' Leaving:' || l_proc,555);
1285: rollback to save_competence_element;
1286: IF NOT (l_upgraded_from_rec_id IS NOT NULL AND
1287: hr_message.last_message_name = 'HR_51648_CEL_PER_DATES_OVLAP') THEN
1288: hr_utility.set_location(' Leaving:' || l_proc,560);
1284: hr_utility.set_location(' Leaving:' || l_proc,555);
1285: rollback to save_competence_element;
1286: IF NOT (l_upgraded_from_rec_id IS NOT NULL AND
1287: hr_message.last_message_name = 'HR_51648_CEL_PER_DATES_OVLAP') THEN
1288: hr_utility.set_location(' Leaving:' || l_proc,560);
1289: RAISE;
1290: END IF;
1291: --
1292: When others THEN
1289: RAISE;
1290: END IF;
1291: --
1292: When others THEN
1293: hr_utility.set_location(' Leaving:' || l_proc,565);
1294: raise;
1295: --
1296: End process_api;
1297: -----------new procedure for validating and saving record into transaction tables --------------------------
1389: from per_competences_vl
1390: where competence_id = p_competence_id;
1391: l_comp_dates_cur get_comp_dates%RowType;
1392: Begin
1393: hr_utility.set_location(' Entering:' || l_proc,5);
1394: IF (p_transaction_step_id = -1 ) then
1395: hr_utility.set_location(l_proc,10);
1396: p_transaction_step_id := Null;
1397: ENd IF;
1391: l_comp_dates_cur get_comp_dates%RowType;
1392: Begin
1393: hr_utility.set_location(' Entering:' || l_proc,5);
1394: IF (p_transaction_step_id = -1 ) then
1395: hr_utility.set_location(l_proc,10);
1396: p_transaction_step_id := Null;
1397: ENd IF;
1398: IF (p_competence_element_id is not NULL) THEN
1399: hr_utility.set_location(l_proc,15);
1395: hr_utility.set_location(l_proc,10);
1396: p_transaction_step_id := Null;
1397: ENd IF;
1398: IF (p_competence_element_id is not NULL) THEN
1399: hr_utility.set_location(l_proc,15);
1400: hr_comp_profile_ss.check_if_cmptnce_rec_changed
1401: (p_competence_element_id => p_competence_element_id
1402: ,p_competence_id => p_competence_id
1403: ,p_proficiency_level_id => p_proficiency_level_id
1411: ,p_comments => p_comments
1412: ,p_change_mode => p_change_mode
1413: ,p_rec_changed => l_changed);
1414: if l_changed = false THEN
1415: hr_utility.set_location(l_proc,20);
1416: HR_COMP_OUTCOME_PROFILE_SS.check_if_cmptnce_rec_changed
1417: (p_item_type => p_item_type
1418: ,p_item_key => p_item_key
1419: ,p_activity_id => p_activity_id
1423: ,p_rec_changed => l_changed
1424: );
1425: IF l_changed = FALSE then
1426: IF (p_transaction_step_id IS NOT Null) THEN
1427: hr_utility.set_location(l_proc,25);
1428: delete_transaction_step_id(p_transaction_step_id);
1429: END IF;
1430: hr_utility.set_location(' Leaving:' || l_proc,30);
1431: return;
1426: IF (p_transaction_step_id IS NOT Null) THEN
1427: hr_utility.set_location(l_proc,25);
1428: delete_transaction_step_id(p_transaction_step_id);
1429: END IF;
1430: hr_utility.set_location(' Leaving:' || l_proc,30);
1431: return;
1432: END if;
1433: end if;
1434: END IF;
1431: return;
1432: END if;
1433: end if;
1434: END IF;
1435: hr_utility.set_location(l_proc,35);
1436: OPEN get_curr_step_value(p_proficiency_level_id);
1437: FETCH get_curr_step_value into l_current_step_val;
1438: IF get_curr_step_value%notfound THEN
1439: hr_utility.set_location(l_proc,40);
1435: hr_utility.set_location(l_proc,35);
1436: OPEN get_curr_step_value(p_proficiency_level_id);
1437: FETCH get_curr_step_value into l_current_step_val;
1438: IF get_curr_step_value%notfound THEN
1439: hr_utility.set_location(l_proc,40);
1440: l_current_step_val := Null;
1441: END IF;
1442: hr_utility.set_location(l_proc,45);
1443: close get_curr_step_value;
1438: IF get_curr_step_value%notfound THEN
1439: hr_utility.set_location(l_proc,40);
1440: l_current_step_val := Null;
1441: END IF;
1442: hr_utility.set_location(l_proc,45);
1443: close get_curr_step_value;
1444: l_current_step_value := l_current_step_val;
1445: OPEN get_comp_name(p_competence_id => p_competence_id);
1446: FETCH get_comp_name into c_name;
1444: l_current_step_value := l_current_step_val;
1445: OPEN get_comp_name(p_competence_id => p_competence_id);
1446: FETCH get_comp_name into c_name;
1447: IF get_comp_name%notfound THEN
1448: hr_utility.set_location(l_proc,50);
1449: close get_comp_name;
1450: raise hr_comp_profile_ss.g_fatal_error;
1451: END IF;
1452: close get_comp_name;
1450: raise hr_comp_profile_ss.g_fatal_error;
1451: END IF;
1452: close get_comp_name;
1453: p_competence_name := c_name;
1454: hr_utility.set_location(l_proc,55);
1455: OPEN get_comp_alias(p_competence_id => p_competence_id);
1456: FETCH get_comp_alias into c_comp_alias;
1457: IF get_comp_alias%notfound THEN
1458: hr_utility.set_location(l_proc,60);
1454: hr_utility.set_location(l_proc,55);
1455: OPEN get_comp_alias(p_competence_id => p_competence_id);
1456: FETCH get_comp_alias into c_comp_alias;
1457: IF get_comp_alias%notfound THEN
1458: hr_utility.set_location(l_proc,60);
1459: close get_comp_name;
1460: raise hr_comp_profile_ss.g_fatal_error;
1461: END IF;
1462: close get_comp_alias;
1460: raise hr_comp_profile_ss.g_fatal_error;
1461: END IF;
1462: close get_comp_alias;
1463: p_competence_alias := c_comp_alias;
1464: hr_utility.set_location(l_proc,65);
1465: OPEN get_comp_dates(p_competence_id => p_competence_id);
1466: FETCH get_comp_dates into l_comp_dates_cur;
1467: IF get_comp_dates%NOTFOUND THEN
1468: hr_utility.set_location(l_proc,70);
1464: hr_utility.set_location(l_proc,65);
1465: OPEN get_comp_dates(p_competence_id => p_competence_id);
1466: FETCH get_comp_dates into l_comp_dates_cur;
1467: IF get_comp_dates%NOTFOUND THEN
1468: hr_utility.set_location(l_proc,70);
1469: l_eff_date_to := p_eff_date_to;
1470: ELSE
1471: hr_utility.set_location(l_proc,75);
1472: IF (l_comp_dates_cur.start_date IS NOT NULL AND
1467: IF get_comp_dates%NOTFOUND THEN
1468: hr_utility.set_location(l_proc,70);
1469: l_eff_date_to := p_eff_date_to;
1470: ELSE
1471: hr_utility.set_location(l_proc,75);
1472: IF (l_comp_dates_cur.start_date IS NOT NULL AND
1473: l_comp_dates_cur.start_date > to_date(p_eff_date_from, g_date_format) ) THEN
1474: hr_utility.set_location(l_proc,80);
1475: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1470: ELSE
1471: hr_utility.set_location(l_proc,75);
1472: IF (l_comp_dates_cur.start_date IS NOT NULL AND
1473: l_comp_dates_cur.start_date > to_date(p_eff_date_from, g_date_format) ) THEN
1474: hr_utility.set_location(l_proc,80);
1475: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1476: p_error_message => p_error_message,
1477: p_attr_name => 'CurrStartDate',
1478: p_app_short_name => 'PER',
1477: p_attr_name => 'CurrStartDate',
1478: p_app_short_name => 'PER',
1479: P_SINGLE_ERROR_MESSAGE => 'HR_52339_COMP_ELMT_DATE_INVL');
1480: CLOSE get_comp_dates;
1481: hr_utility.set_location(' Leaving:' || l_proc,85);
1482: RETURN;
1483: END IF;
1484: IF p_eff_date_to IS NULL THEN
1485: hr_utility.set_location(l_proc,90);
1481: hr_utility.set_location(' Leaving:' || l_proc,85);
1482: RETURN;
1483: END IF;
1484: IF p_eff_date_to IS NULL THEN
1485: hr_utility.set_location(l_proc,90);
1486: l_eff_date_to := l_comp_dates_cur.end_date;
1487: ELSIF (l_comp_dates_cur.end_Date IS NOT NULL AND
1488: l_comp_dates_cur.end_date < to_date(p_eff_date_to, g_date_format) ) THEN
1489: hr_utility.set_location(l_proc,95);
1485: hr_utility.set_location(l_proc,90);
1486: l_eff_date_to := l_comp_dates_cur.end_date;
1487: ELSIF (l_comp_dates_cur.end_Date IS NOT NULL AND
1488: l_comp_dates_cur.end_date < to_date(p_eff_date_to, g_date_format) ) THEN
1489: hr_utility.set_location(l_proc,95);
1490: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1491: p_error_message => p_error_message,
1492: p_attr_name => 'EndDate',
1493: p_app_short_name => 'PER',
1492: p_attr_name => 'EndDate',
1493: p_app_short_name => 'PER',
1494: P_SINGLE_ERROR_MESSAGE => 'HR_52339_COMP_ELMT_DATE_INVL');
1495: CLOSE get_comp_dates;
1496: hr_utility.set_location(' Leaving:' || l_proc,100);
1497: RETURN;
1498: ELSE
1499: hr_utility.set_location(l_proc,105);
1500: l_eff_date_to := to_date(p_eff_date_to, g_date_format);
1495: CLOSE get_comp_dates;
1496: hr_utility.set_location(' Leaving:' || l_proc,100);
1497: RETURN;
1498: ELSE
1499: hr_utility.set_location(l_proc,105);
1500: l_eff_date_to := to_date(p_eff_date_to, g_date_format);
1501: END IF;
1502: END IF ;
1503: CLOSE get_comp_dates;
1500: l_eff_date_to := to_date(p_eff_date_to, g_date_format);
1501: END IF;
1502: END IF ;
1503: CLOSE get_comp_dates;
1504: hr_utility.set_location(l_proc,110);
1505: if p_competence_element_id is not null then
1506: hr_utility.set_location(l_proc,115);
1507: OPEN get_previous_step_value(p_competence_element_id);
1508: FETCH get_previous_step_value into l_prev_step_val;
1502: END IF ;
1503: CLOSE get_comp_dates;
1504: hr_utility.set_location(l_proc,110);
1505: if p_competence_element_id is not null then
1506: hr_utility.set_location(l_proc,115);
1507: OPEN get_previous_step_value(p_competence_element_id);
1508: FETCH get_previous_step_value into l_prev_step_val;
1509: IF get_previous_step_value%notfound THEN
1510: hr_utility.set_location(l_proc,120);
1506: hr_utility.set_location(l_proc,115);
1507: OPEN get_previous_step_value(p_competence_element_id);
1508: FETCH get_previous_step_value into l_prev_step_val;
1509: IF get_previous_step_value%notfound THEN
1510: hr_utility.set_location(l_proc,120);
1511: close get_previous_step_value;
1512: END IF;
1513: close get_previous_step_value;
1514: hr_utility.set_location(l_proc,125);
1510: hr_utility.set_location(l_proc,120);
1511: close get_previous_step_value;
1512: END IF;
1513: close get_previous_step_value;
1514: hr_utility.set_location(l_proc,125);
1515: l_prev_step_value := l_prev_step_val;
1516: OPEN get_prev_prof_lvl_id(p_competence_element_id);
1517: FETCH get_prev_prof_lvl_id into l_prev_prof_lvl_val;
1518: IF get_prev_prof_lvl_id%notfound THEN
1515: l_prev_step_value := l_prev_step_val;
1516: OPEN get_prev_prof_lvl_id(p_competence_element_id);
1517: FETCH get_prev_prof_lvl_id into l_prev_prof_lvl_val;
1518: IF get_prev_prof_lvl_id%notfound THEN
1519: hr_utility.set_location(l_proc,130);
1520: close get_prev_prof_lvl_id;
1521: END IF;
1522: close get_prev_prof_lvl_id;
1523: hr_utility.set_location(l_proc,135);
1519: hr_utility.set_location(l_proc,130);
1520: close get_prev_prof_lvl_id;
1521: END IF;
1522: close get_prev_prof_lvl_id;
1523: hr_utility.set_location(l_proc,135);
1524: l_prev_prof_lvl_value := l_prev_prof_lvl_val;
1525: OPEN get_prev_start_date(p_competence_element_id);
1526: FETCH get_prev_start_date into l_prev_date;
1527: IF get_prev_start_date%notfound THEN
1524: l_prev_prof_lvl_value := l_prev_prof_lvl_val;
1525: OPEN get_prev_start_date(p_competence_element_id);
1526: FETCH get_prev_start_date into l_prev_date;
1527: IF get_prev_start_date%notfound THEN
1528: hr_utility.set_location(l_proc,140);
1529: close get_comp_name;
1530: raise hr_comp_profile_ss.g_fatal_error;
1531: END IF;
1532: close get_prev_start_date;
1529: close get_comp_name;
1530: raise hr_comp_profile_ss.g_fatal_error;
1531: END IF;
1532: close get_prev_start_date;
1533: hr_utility.set_location(l_proc,145);
1534: l_prev_start_date := l_prev_date;
1535: if p_transaction_step_id is not null then
1536: hr_utility.set_location(l_proc,150);
1537: select varchar2_value into l_mode_fetch
1532: close get_prev_start_date;
1533: hr_utility.set_location(l_proc,145);
1534: l_prev_start_date := l_prev_date;
1535: if p_transaction_step_id is not null then
1536: hr_utility.set_location(l_proc,150);
1537: select varchar2_value into l_mode_fetch
1538: from hr_api_transaction_values
1539: where transaction_step_id = p_transaction_step_id
1540: and name = 'P_CHANGE_MODE';
1538: from hr_api_transaction_values
1539: where transaction_step_id = p_transaction_step_id
1540: and name = 'P_CHANGE_MODE';
1541: if l_mode_fetch = 'UPGRADE' or l_mode_fetch = 'UPDATE_APPLY' then
1542: hr_utility.set_location(l_proc,155);
1543: if l_current_step_val = l_prev_step_val then
1544: hr_utility.set_location(l_proc,160);
1545: raise g_invalid_entry;
1546: end if;
1540: and name = 'P_CHANGE_MODE';
1541: if l_mode_fetch = 'UPGRADE' or l_mode_fetch = 'UPDATE_APPLY' then
1542: hr_utility.set_location(l_proc,155);
1543: if l_current_step_val = l_prev_step_val then
1544: hr_utility.set_location(l_proc,160);
1545: raise g_invalid_entry;
1546: end if;
1547: end if;
1548: end if;
1546: end if;
1547: end if;
1548: end if;
1549: end if;
1550: hr_utility.set_location(l_proc,165);
1551: if p_transaction_step_id is not null then
1552: hr_utility.set_location(l_proc,170);
1553: select varchar2_value into m_mode from hr_api_transaction_values
1554: where transaction_step_id = p_transaction_step_id
1548: end if;
1549: end if;
1550: hr_utility.set_location(l_proc,165);
1551: if p_transaction_step_id is not null then
1552: hr_utility.set_location(l_proc,170);
1553: select varchar2_value into m_mode from hr_api_transaction_values
1554: where transaction_step_id = p_transaction_step_id
1555: and name = 'P_CHANGE_MODE';
1556: if m_mode = 'UPGRADE' then
1553: select varchar2_value into m_mode from hr_api_transaction_values
1554: where transaction_step_id = p_transaction_step_id
1555: and name = 'P_CHANGE_MODE';
1556: if m_mode = 'UPGRADE' then
1557: hr_utility.set_location(l_proc,175);
1558: select number_value into l_prev_step_value
1559: from hr_api_transaction_values
1560: where transaction_step_id = p_transaction_step_id
1561: and name = 'P_PREV_STEP_VALUE';
1569: and name = 'P_PREV_START_DATE';
1570: end if;
1571: end if;
1572: IF p_change_mode = 'CORRECT' then
1573: hr_utility.set_location('In p_change_mode = CORRECT' || l_proc,170);
1574: FOR rec IN get_old_txn_ids ( p_item_type => p_item_type
1575: ,p_item_key => p_item_key
1576: ,p_activity_id => p_activity_id
1577: ,p_person_id => p_pid
1578: ,p_competence_element_id => p_competence_element_id
1579: ,p_competence_id => p_competence_id
1580: ,p_change_mode => p_change_mode )
1581: loop
1582: hr_utility.set_location('In loop rec.transaction_step_id=' || rec.transaction_step_id,175);
1583: IF (p_transaction_step_id = rec.transaction_step_id) then
1584: hr_utility.set_location('In p_transaction_step_id = rec.transaction_step_id ',180);
1585: null;
1586: Else
1580: ,p_change_mode => p_change_mode )
1581: loop
1582: hr_utility.set_location('In loop rec.transaction_step_id=' || rec.transaction_step_id,175);
1583: IF (p_transaction_step_id = rec.transaction_step_id) then
1584: hr_utility.set_location('In p_transaction_step_id = rec.transaction_step_id ',180);
1585: null;
1586: Else
1587: hr_utility.set_location('In else of p_transaction_step_id = rec.transaction_step_id ',185);
1588: delete_transaction_step_id(rec.transaction_step_id);
1583: IF (p_transaction_step_id = rec.transaction_step_id) then
1584: hr_utility.set_location('In p_transaction_step_id = rec.transaction_step_id ',180);
1585: null;
1586: Else
1587: hr_utility.set_location('In else of p_transaction_step_id = rec.transaction_step_id ',185);
1588: delete_transaction_step_id(rec.transaction_step_id);
1589: hr_utility.set_location('after calling delete_transaction_step_id ',190);
1590: END if;
1591: END loop;
1585: null;
1586: Else
1587: hr_utility.set_location('In else of p_transaction_step_id = rec.transaction_step_id ',185);
1588: delete_transaction_step_id(rec.transaction_step_id);
1589: hr_utility.set_location('after calling delete_transaction_step_id ',190);
1590: END if;
1591: END loop;
1592: END if;
1593: hr_utility.set_location(l_proc,180);
1589: hr_utility.set_location('after calling delete_transaction_step_id ',190);
1590: END if;
1591: END loop;
1592: END if;
1593: hr_utility.set_location(l_proc,180);
1594: process_save(p_item_type => p_item_type
1595: ,p_item_key => p_item_key
1596: ,p_actid => p_activity_id
1597: ,p_person_id => p_pid
1615: ,p_prev_start_date => to_char(l_prev_start_date,g_date_format)
1616: ,p_transaction_step_id => p_transaction_step_id
1617: ,p_error_message => p_error_message
1618: ,p_competence_status => p_competence_status);
1619: hr_utility.set_location(' Leaving:' || l_proc,185);
1620: Exception
1621: when g_invalid_entry then
1622: hr_utility.set_location(' Leaving:' || l_proc,555);
1623: null;
1618: ,p_competence_status => p_competence_status);
1619: hr_utility.set_location(' Leaving:' || l_proc,185);
1620: Exception
1621: when g_invalid_entry then
1622: hr_utility.set_location(' Leaving:' || l_proc,555);
1623: null;
1624: when others then
1625: hr_utility.set_location(' Leaving:' || l_proc,560);
1626: raise g_invalid_entry;
1621: when g_invalid_entry then
1622: hr_utility.set_location(' Leaving:' || l_proc,555);
1623: null;
1624: when others then
1625: hr_utility.set_location(' Leaving:' || l_proc,560);
1626: raise g_invalid_entry;
1627: End api_validate_competence_rec_ss;
1628: -------------------------------------------------------------------------------
1629: Procedure get_pending_addition_ids
1640: and val.varchar2_value = 'ADD';
1641: l_index number;
1642: l_transaction_id number;
1643: begin
1644: hr_utility.set_location(' Entering:' || l_proc,5);
1645: l_transaction_id:=hr_transaction_ss.get_transaction_id
1646: (p_item_type => p_item_type
1647: ,p_item_key => p_item_key);
1648: l_index := 0;
1646: (p_item_type => p_item_type
1647: ,p_item_key => p_item_key);
1648: l_index := 0;
1649: for l_step_values in get_add_ids(p_transaction_id => l_transaction_id) loop
1650: hr_utility.set_location(l_proc || 'LOOP' ,10);
1651: p_step_values := p_step_values || l_step_values.transaction_step_id || '?';
1652: l_index := l_index + 1;
1653: end loop ;
1654: p_rows := l_index;
1651: p_step_values := p_step_values || l_step_values.transaction_step_id || '?';
1652: l_index := l_index + 1;
1653: end loop ;
1654: p_rows := l_index;
1655: hr_utility.set_location(' Leaving:' || l_proc,15);
1656: end get_pending_addition_ids;
1657: --------------DELETE PENDING CURRENT UPDATE IDS ----------------------------------------------
1658: Procedure del_pen_currupd_ids(p_item_type IN varchar2
1659: ,p_item_key IN varchar2) is
1667: and val.varchar2_value IN ('UPDATE_APPLY','UPDATE_HIST');
1668: l_step_values number ;
1669: l_transaction_id number;
1670: begin
1671: hr_utility.set_location(' Entering:' || l_proc,5);
1672: l_transaction_id:=hr_transaction_ss.get_transaction_id
1673: (p_item_type => p_item_type
1674: ,p_item_key => p_item_key);
1675: for I in get_upd_ids(l_transaction_id) loop
1672: l_transaction_id:=hr_transaction_ss.get_transaction_id
1673: (p_item_type => p_item_type
1674: ,p_item_key => p_item_key);
1675: for I in get_upd_ids(l_transaction_id) loop
1676: hr_utility.set_location(l_proc || 'LOOP' ,10);
1677: -- Added for competence Qualification link enhancement
1678: -- HR_COMP_OUTCOME_PROFILE_SS.delete_add_page(
1679: -- p_transaction_step_id => I.transaction_step_id);
1680: -- End for competence Qualification link enhancement
1684: where transaction_step_id = I.transaction_step_id;
1685: end loop;
1686: del_add_ids(p_item_type, p_item_key);
1687: commit;
1688: hr_utility.set_location(' Leaving:' || l_proc,15);
1689: end del_pen_currupd_ids;
1690: ----------------------------------------------------------------------------------------------------
1691: Procedure del_add_ids(p_item_type IN varchar2
1692: ,p_item_key IN varchar2) is
1699: and val.varchar2_value = 'ADD';
1700: l_step_values number ;
1701: l_transaction_id number;
1702: begin
1703: hr_utility.set_location(' Entering:' || l_proc,5);
1704: l_transaction_id:=hr_transaction_ss.get_transaction_id
1705: (p_item_type => p_item_type
1706: ,p_item_key => p_item_key);
1707: for I in get_add_ids(l_transaction_id) loop
1704: l_transaction_id:=hr_transaction_ss.get_transaction_id
1705: (p_item_type => p_item_type
1706: ,p_item_key => p_item_key);
1707: for I in get_add_ids(l_transaction_id) loop
1708: hr_utility.set_location(l_proc || 'LOOP' ,10);
1709: delete from hr_api_transaction_values
1710: where transaction_step_id = I.transaction_step_id;
1711: delete from hr_api_transaction_steps
1712: where transaction_id = l_transaction_id
1711: delete from hr_api_transaction_steps
1712: where transaction_id = l_transaction_id
1713: and transaction_step_id = I.transaction_step_id;
1714: end loop ;
1715: hr_utility.set_location(' Leaving:' || l_proc,15);
1716: end del_add_ids;
1717: /*------------------------------------------------------------------------------
1718: |
1719: | Name : save_transaction_step
1736: ,p_transaction_data IN TRANSACTION_TABLE1) AS
1737: l_proc varchar2(200) := g_package || 'save_transaction_step';
1738: l_count Number:=0;
1739: BEGIN
1740: hr_utility.set_location(' Entering:' || l_proc,5);
1741: l_count := p_transaction_data.COUNT;
1742: FOR i IN 1..l_count LOOP
1743: BEGIN
1744: hr_utility.set_location(l_proc,10);
1740: hr_utility.set_location(' Entering:' || l_proc,5);
1741: l_count := p_transaction_data.COUNT;
1742: FOR i IN 1..l_count LOOP
1743: BEGIN
1744: hr_utility.set_location(l_proc,10);
1745: IF p_transaction_data(i).param_data_type = 'DATE' THEN
1746: hr_utility.set_location(l_proc,15);
1747: hr_transaction_api.set_date_value(p_transaction_step_id => p_transaction_step_id
1748: ,p_person_id => p_login_person_id
1742: FOR i IN 1..l_count LOOP
1743: BEGIN
1744: hr_utility.set_location(l_proc,10);
1745: IF p_transaction_data(i).param_data_type = 'DATE' THEN
1746: hr_utility.set_location(l_proc,15);
1747: hr_transaction_api.set_date_value(p_transaction_step_id => p_transaction_step_id
1748: ,p_person_id => p_login_person_id
1749: ,p_name => p_transaction_data(i).param_name
1750: ,p_value => to_date(ltrim(rtrim(p_transaction_data(i).param_value)),g_date_format));
1748: ,p_person_id => p_login_person_id
1749: ,p_name => p_transaction_data(i).param_name
1750: ,p_value => to_date(ltrim(rtrim(p_transaction_data(i).param_value)),g_date_format));
1751: ELSIF p_transaction_data(i).param_data_type = 'NUMBER' THEN
1752: hr_utility.set_location(l_proc,20);
1753: hr_transaction_api.set_number_value(p_transaction_step_id => p_transaction_step_id
1754: ,p_person_id => p_login_person_id
1755: ,p_name => p_transaction_data(i).param_name
1756: ,p_value => to_number(ltrim(rtrim(p_transaction_data(i).param_value))));
1754: ,p_person_id => p_login_person_id
1755: ,p_name => p_transaction_data(i).param_name
1756: ,p_value => to_number(ltrim(rtrim(p_transaction_data(i).param_value))));
1757: ELSIF p_transaction_data(i).param_data_type = 'VARCHAR2' THEN
1758: hr_utility.set_location(l_proc,25);
1759: hr_transaction_api.set_varchar2_value(p_transaction_step_id => p_transaction_step_id
1760: ,p_person_id => p_login_person_id
1761: ,p_name => p_transaction_data(i).param_name
1762: ,p_value => p_transaction_data(i).param_value);
1761: ,p_name => p_transaction_data(i).param_name
1762: ,p_value => p_transaction_data(i).param_value);
1763: END IF;
1764: Exception When others then
1765: hr_utility.set_location(l_proc,555);
1766: RAISE hr_utility.hr_error;
1767: END;
1768: END LOOP;
1769: hr_utility.set_location(' Leaving:' || l_proc,30);
1762: ,p_value => p_transaction_data(i).param_value);
1763: END IF;
1764: Exception When others then
1765: hr_utility.set_location(l_proc,555);
1766: RAISE hr_utility.hr_error;
1767: END;
1768: END LOOP;
1769: hr_utility.set_location(' Leaving:' || l_proc,30);
1770: EXCEPTION
1765: hr_utility.set_location(l_proc,555);
1766: RAISE hr_utility.hr_error;
1767: END;
1768: END LOOP;
1769: hr_utility.set_location(' Leaving:' || l_proc,30);
1770: EXCEPTION
1771: WHEN OTHERS THEN
1772: hr_utility.set_location(l_proc,560);
1773: hr_utility.trace('EXCEPTION SAVE_TRANSACTION_STEP'||'STS#');
1768: END LOOP;
1769: hr_utility.set_location(' Leaving:' || l_proc,30);
1770: EXCEPTION
1771: WHEN OTHERS THEN
1772: hr_utility.set_location(l_proc,560);
1773: hr_utility.trace('EXCEPTION SAVE_TRANSACTION_STEP'||'STS#');
1774: raise hr_utility.hr_error;
1775: END save_transaction_step;
1776: -----------------------------------
1769: hr_utility.set_location(' Leaving:' || l_proc,30);
1770: EXCEPTION
1771: WHEN OTHERS THEN
1772: hr_utility.set_location(l_proc,560);
1773: hr_utility.trace('EXCEPTION SAVE_TRANSACTION_STEP'||'STS#');
1774: raise hr_utility.hr_error;
1775: END save_transaction_step;
1776: -----------------------------------
1777: PROCEDURE delete_all_ids
1770: EXCEPTION
1771: WHEN OTHERS THEN
1772: hr_utility.set_location(l_proc,560);
1773: hr_utility.trace('EXCEPTION SAVE_TRANSACTION_STEP'||'STS#');
1774: raise hr_utility.hr_error;
1775: END save_transaction_step;
1776: -----------------------------------
1777: PROCEDURE delete_all_ids
1778: (p_item_type in varchar2
1784: where transaction_id = p_transaction_id;
1785: l_result number ;
1786: l_transaction_id number ;
1787: begin
1788: hr_utility.set_location(' Entering:' || l_proc,5);
1789: l_transaction_id:=hr_transaction_ss.get_transaction_id
1790: (p_item_type => p_item_type
1791: ,p_item_key => p_item_key);
1792: for l_result in get_all_ids(p_transaction_id => l_transaction_id) loop
1789: l_transaction_id:=hr_transaction_ss.get_transaction_id
1790: (p_item_type => p_item_type
1791: ,p_item_key => p_item_key);
1792: for l_result in get_all_ids(p_transaction_id => l_transaction_id) loop
1793: hr_utility.set_location(l_proc || 'LOOP',10);
1794: delete from hr_api_transaction_values
1795: where transaction_step_id = l_result.transaction_step_id;
1796: delete from hr_api_transaction_steps
1797: where transaction_id = l_transaction_id
1797: where transaction_id = l_transaction_id
1798: and transaction_step_id = l_result.transaction_step_id;
1799: end loop ;
1800: commit;
1801: hr_utility.set_location(' Leaving:' || l_proc,15);
1802: end delete_all_ids;
1803: ------------------------------------------------
1804: PROCEDURE delete_transaction_step_id
1805: (p_transaction_step_id IN number) is
1809: l_mode varchar2(20) default null;
1810: l_competence_element_id number default null;
1811: l_transaction_id number;
1812: BEGIN
1813: hr_utility.set_location(' Entering:' || l_proc,5);
1814: l_transaction_step_id := p_transaction_step_id;
1815: select transaction_id into l_transaction_id
1816: from hr_api_transaction_steps
1817: where transaction_step_id = l_transaction_step_id
1821: from hr_api_transaction_values
1822: where transaction_step_id = l_transaction_step_id
1823: and name = 'P_CHANGE_MODE';
1824: if l_mode = 'UPGRADE' then
1825: hr_utility.set_location(l_proc,10);
1826: select number_value
1827: into l_competence_element_id
1828: from hr_api_transaction_values
1829: where transaction_step_id = l_transaction_step_id
1847: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1848: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1849: end if;
1850: commit ;
1851: hr_utility.set_location(' Leaving:' || l_proc,15);
1852: END;
1853: ----------------------------------------------------------------------------
1854: -- for saving fields from update main page to tx tables
1855: PROCEDURE process_save_update_details
1870: l_action_person_id number;
1871: l_transaction_step_id number;
1872: l_prev_step_value number := null;
1873: BEGIN
1874: hr_utility.set_location(' Entering:' || l_proc,5);
1875: if (p_prev_step_value <> -1) then
1876: hr_utility.set_location(l_proc,10);
1877: l_prev_step_value := p_prev_step_value;
1878: end if;
1872: l_prev_step_value number := null;
1873: BEGIN
1874: hr_utility.set_location(' Entering:' || l_proc,5);
1875: if (p_prev_step_value <> -1) then
1876: hr_utility.set_location(l_proc,10);
1877: l_prev_step_value := p_prev_step_value;
1878: end if;
1879: l_action_person_id := p_person_id;
1880: l_transaction_step_id := transaction_step_id;
1909: ,p_actid => to_number(p_actid)
1910: ,p_login_person_id => fnd_global.employee_id
1911: ,p_transaction_step_id => l_transaction_step_id
1912: ,p_transaction_data => l_trans_tbl);
1913: hr_utility.set_location(' Leaving:' || l_proc,15);
1914: END process_save_update_details;
1915: ---------------------------------------------------
1916: -- for saving fields from the update details page to tx tables
1917: procedure save_update_details
1957: l_count number default 0;
1958: l_action_person_id number default null;
1959: -------------
1960: begin
1961: hr_utility.set_location(' Entering:' || l_proc,5);
1962: l_user_date_format := g_date_format;
1963: l_transaction_step_id := p_transaction_step_id;
1964: l_action_person_id := p_pid;
1965: g_business_group_id := hr_util_misc_web.get_business_group_id(p_person_id => l_action_person_id);
1965: g_business_group_id := hr_util_misc_web.get_business_group_id(p_person_id => l_action_person_id);
1966: OPEN get_curr_step_value(p_proficiency_level_id => p_proficiency_level_id);
1967: FETCH get_curr_step_value into l_current_step_val;
1968: IF get_curr_step_value%notfound THEN
1969: hr_utility.set_location(l_proc,10);
1970: close get_curr_step_value;
1971: ELSE
1972: hr_utility.set_location(l_proc,15);
1973: CLOSE get_curr_step_value;
1968: IF get_curr_step_value%notfound THEN
1969: hr_utility.set_location(l_proc,10);
1970: close get_curr_step_value;
1971: ELSE
1972: hr_utility.set_location(l_proc,15);
1973: CLOSE get_curr_step_value;
1974: END IF ;
1975: hr_utility.set_location(l_proc,20);
1976: l_current_step_value := l_current_step_val;
1971: ELSE
1972: hr_utility.set_location(l_proc,15);
1973: CLOSE get_curr_step_value;
1974: END IF ;
1975: hr_utility.set_location(l_proc,20);
1976: l_current_step_value := l_current_step_val;
1977: OPEN get_prev_start_date(p_competence_element_id => p_competence_element_id);
1978: FETCH get_prev_start_date into l_prev_date;
1979: IF get_prev_start_date%notfound THEN
1976: l_current_step_value := l_current_step_val;
1977: OPEN get_prev_start_date(p_competence_element_id => p_competence_element_id);
1978: FETCH get_prev_start_date into l_prev_date;
1979: IF get_prev_start_date%notfound THEN
1980: hr_utility.set_location(l_proc,25);
1981: close get_comp_name;
1982: raise hr_comp_profile_ss.g_fatal_error;
1983: ELSE
1984: hr_utility.set_location(l_proc,30);
1980: hr_utility.set_location(l_proc,25);
1981: close get_comp_name;
1982: raise hr_comp_profile_ss.g_fatal_error;
1983: ELSE
1984: hr_utility.set_location(l_proc,30);
1985: CLOSE get_prev_start_date;
1986: END IF;
1987: l_prev_start_date := l_prev_date;
1988: hr_utility.set_location(l_proc,35);
1984: hr_utility.set_location(l_proc,30);
1985: CLOSE get_prev_start_date;
1986: END IF;
1987: l_prev_start_date := l_prev_date;
1988: hr_utility.set_location(l_proc,35);
1989: OPEN get_previous_step_value(p_competence_element_id => p_competence_element_id);
1990: FETCH get_previous_step_value into l_prev_step_val;
1991: IF get_previous_step_value%notfound THEN
1992: hr_utility.set_location(l_proc,40);
1988: hr_utility.set_location(l_proc,35);
1989: OPEN get_previous_step_value(p_competence_element_id => p_competence_element_id);
1990: FETCH get_previous_step_value into l_prev_step_val;
1991: IF get_previous_step_value%notfound THEN
1992: hr_utility.set_location(l_proc,40);
1993: close get_previous_step_value;
1994: ELSE
1995: hr_utility.set_location(l_proc,45);
1996: close get_previous_step_value;
1991: IF get_previous_step_value%notfound THEN
1992: hr_utility.set_location(l_proc,40);
1993: close get_previous_step_value;
1994: ELSE
1995: hr_utility.set_location(l_proc,45);
1996: close get_previous_step_value;
1997: END IF;
1998: hr_utility.set_location(l_proc,50);
1999: l_prev_step_value := l_prev_step_val;
1994: ELSE
1995: hr_utility.set_location(l_proc,45);
1996: close get_previous_step_value;
1997: END IF;
1998: hr_utility.set_location(l_proc,50);
1999: l_prev_step_value := l_prev_step_val;
2000: if l_prev_step_value = l_current_step_value then
2001: hr_utility.set_location(l_proc,55);
2002: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1997: END IF;
1998: hr_utility.set_location(l_proc,50);
1999: l_prev_step_value := l_prev_step_val;
2000: if l_prev_step_value = l_current_step_value then
2001: hr_utility.set_location(l_proc,55);
2002: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2003: p_error_message => p_error_message,
2004: p_attr_name => 'ProfLevel',
2005: p_app_short_name => 'PER',
2003: p_error_message => p_error_message,
2004: p_attr_name => 'ProfLevel',
2005: p_app_short_name => 'PER',
2006: P_SINGLE_ERROR_MESSAGE => 'HR_COMP_INV_LEVEL_SS');
2007: hr_utility.set_location(' Leaving:' || l_proc,60);
2008: Return;
2009: end if;
2010: hr_utility.set_location(l_proc,65);
2011: api_validate_competence_record
2006: P_SINGLE_ERROR_MESSAGE => 'HR_COMP_INV_LEVEL_SS');
2007: hr_utility.set_location(' Leaving:' || l_proc,60);
2008: Return;
2009: end if;
2010: hr_utility.set_location(l_proc,65);
2011: api_validate_competence_record
2012: (p_validate => true
2013: ,p_person_id => p_pid
2014: ,p_business_group_id => g_business_group_id
2029: ,p_certifctn_date_type => l_certification_date
2030: ,p_next_certifctn_date_type => l_next_certifctn_date
2031: ,p_error_message => p_error_message);
2032: IF p_error_message is not null then
2033: hr_utility.set_location(' Leaving:' || l_proc,70);
2034: Return;
2035: END IF;
2036: l_count := l_count + 1;
2037: l_trans_tbl(l_count).param_name := 'P_PROFICIENCY_LEVEL_ID';
2093: ,p_actid => to_number(p_activity_id)
2094: ,p_login_person_id => fnd_global.employee_id
2095: ,p_transaction_step_id => l_transaction_step_id
2096: ,p_transaction_data => l_trans_tbl);
2097: hr_utility.set_location(' Leaving:' || l_proc,75);
2098: ------------------
2099: EXCEPTION
2100: when g_invalid_entry then
2101: hr_utility.set_location(' Leaving:' || l_proc,555);
2097: hr_utility.set_location(' Leaving:' || l_proc,75);
2098: ------------------
2099: EXCEPTION
2100: when g_invalid_entry then
2101: hr_utility.set_location(' Leaving:' || l_proc,555);
2102: raise g_invalid_entry;
2103: when others then
2104: hr_utility.set_location(' Leaving:' || l_proc,560);
2105: raise g_invalid_entry;
2100: when g_invalid_entry then
2101: hr_utility.set_location(' Leaving:' || l_proc,555);
2102: raise g_invalid_entry;
2103: when others then
2104: hr_utility.set_location(' Leaving:' || l_proc,560);
2105: raise g_invalid_entry;
2106: end save_update_details;
2107: --------------------------------------------------------------------
2108: PROCEDURE final_update_save
2128: l_tx_step_id number default null;
2129: l_preupd_date_to date;
2130: l_transaction_id number;
2131: begin
2132: hr_utility.set_location(' Entering:' || l_proc,5);
2133: l_transaction_id := hr_transaction_ss.get_transaction_id
2134: (p_item_type => p_item_type
2135: ,p_item_key => p_item_key);
2136: l_eff_date_from := to_date(p_eff_date_from,g_date_format);
2139: l_transaction_step_id := p_transaction_step_id;
2140: OPEN get_prev_start_date(p_competence_element_id => p_competence_element_id);
2141: FETCH get_prev_start_date into l_prev_date;
2142: IF get_prev_start_date%notfound THEN
2143: hr_utility.set_location(l_proc,10);
2144: close get_comp_name;
2145: raise hr_comp_profile_ss.g_fatal_error;
2146: END IF;
2147: close get_prev_start_date;
2205: and b.name = 'P_COMPETENCE_ELEMENT_ID'
2206: and b.number_value = l_competence_element_id
2207: and a.transaction_step_id <> l_transaction_step_id;
2208: if l_tx_step_id is not null then
2209: hr_utility.set_location(l_proc,15);
2210: update hr_api_transaction_values
2211: set date_value = l_preupd_date_to
2212: where transaction_step_id = l_tx_step_id
2213: and name = 'P_EFF_DATE_TO';
2218: ,p_actid => to_number(p_activity_id)
2219: ,p_login_person_id => fnd_global.employee_id
2220: ,p_transaction_step_id => l_transaction_step_id
2221: ,p_transaction_data => l_trans_tbl);
2222: hr_utility.set_location(' Leaving:' || l_proc,20);
2223: exception
2224: when others then
2225: hr_utility.set_location(' Leaving:' || l_proc,555);
2226: raise g_invalid_entry;
2221: ,p_transaction_data => l_trans_tbl);
2222: hr_utility.set_location(' Leaving:' || l_proc,20);
2223: exception
2224: when others then
2225: hr_utility.set_location(' Leaving:' || l_proc,555);
2226: raise g_invalid_entry;
2227: end final_update_save;
2228: -----------------------------------------------------
2229: procedure get_comp_name_alias(
2232: ,p_competence_id out nocopy varchar2
2233: ,p_business_group_id in varchar2) is
2234: l_proc varchar2(200) := g_package || 'get_comp_name_alias';
2235: begin
2236: hr_utility.set_location(' Entering:' || l_proc,5);
2237: select upper(rtrim(ltrim(p_competence_name))), upper(rtrim(ltrim(p_competence_alias)))
2238: into p_competence_name, p_competence_alias
2239: from dual;
2240: --
2242: from per_competences_vl
2243: where upper(name) = p_competence_name or upper(competence_alias) = p_competence_alias
2244: and (business_group_id+0 = p_business_group_id
2245: or business_group_id is null);
2246: hr_utility.set_location(' Leaving:' || l_proc,10);
2247: exception
2248: when g_invalid_competence then
2249: hr_utility.set_location(' Leaving:' || l_proc,555);
2250: raise g_invalid_competence;
2245: or business_group_id is null);
2246: hr_utility.set_location(' Leaving:' || l_proc,10);
2247: exception
2248: when g_invalid_competence then
2249: hr_utility.set_location(' Leaving:' || l_proc,555);
2250: raise g_invalid_competence;
2251: when others then
2252: hr_utility.set_location(' Leaving:' || l_proc,560);
2253: raise ;
2248: when g_invalid_competence then
2249: hr_utility.set_location(' Leaving:' || l_proc,555);
2250: raise g_invalid_competence;
2251: when others then
2252: hr_utility.set_location(' Leaving:' || l_proc,560);
2253: raise ;
2254: end get_comp_name_alias;
2255: -----------------------------
2256: procedure get_lov_comp_id
2261: l_name varchar2(100);
2262: l_alias varchar2(100);
2263: l_comp_id varchar2(100);
2264: begin
2265: hr_utility.set_location(' Entering:' || l_proc,5);
2266: if p_competence_name is null and p_competence_alias is not null then
2267: hr_utility.set_location(l_proc,10);
2268: select competence_id into l_competence_id from per_competences_vl
2269: where trim(upper(competence_alias)) = trim(upper(p_competence_alias));
2263: l_comp_id varchar2(100);
2264: begin
2265: hr_utility.set_location(' Entering:' || l_proc,5);
2266: if p_competence_name is null and p_competence_alias is not null then
2267: hr_utility.set_location(l_proc,10);
2268: select competence_id into l_competence_id from per_competences_vl
2269: where trim(upper(competence_alias)) = trim(upper(p_competence_alias));
2270: if sql%notfound then
2271: hr_utility.set_location(l_proc,15);
2267: hr_utility.set_location(l_proc,10);
2268: select competence_id into l_competence_id from per_competences_vl
2269: where trim(upper(competence_alias)) = trim(upper(p_competence_alias));
2270: if sql%notfound then
2271: hr_utility.set_location(l_proc,15);
2272: raise g_invalid_competence;
2273: end if;
2274: end if;
2275: hr_utility.set_location(l_proc,20);
2271: hr_utility.set_location(l_proc,15);
2272: raise g_invalid_competence;
2273: end if;
2274: end if;
2275: hr_utility.set_location(l_proc,20);
2276: if p_competence_alias is null and p_competence_name is not null then
2277: hr_utility.set_location(l_proc,25);
2278: select competence_id into l_competence_id from per_competences_vl
2279: where trim(upper(name)) = trim(upper(p_competence_name));
2273: end if;
2274: end if;
2275: hr_utility.set_location(l_proc,20);
2276: if p_competence_alias is null and p_competence_name is not null then
2277: hr_utility.set_location(l_proc,25);
2278: select competence_id into l_competence_id from per_competences_vl
2279: where trim(upper(name)) = trim(upper(p_competence_name));
2280: if sql%notfound then
2281: hr_utility.set_location(l_proc,30);
2277: hr_utility.set_location(l_proc,25);
2278: select competence_id into l_competence_id from per_competences_vl
2279: where trim(upper(name)) = trim(upper(p_competence_name));
2280: if sql%notfound then
2281: hr_utility.set_location(l_proc,30);
2282: raise g_invalid_competence;
2283: end if;
2284: end if;
2285: hr_utility.set_location(l_proc,35);
2281: hr_utility.set_location(l_proc,30);
2282: raise g_invalid_competence;
2283: end if;
2284: end if;
2285: hr_utility.set_location(l_proc,35);
2286: if ((p_competence_name is not null) and (p_competence_alias is not null)) then
2287: hr_utility.set_location(l_proc,40);
2288: select competence_id into l_competence_id from per_competences_vl
2289: where ( (trim(upper(name))) = (trim(upper(p_competence_name)))) or ((trim(upper(competence_alias))) = (trim(upper(p_competence_alias))) );
2283: end if;
2284: end if;
2285: hr_utility.set_location(l_proc,35);
2286: if ((p_competence_name is not null) and (p_competence_alias is not null)) then
2287: hr_utility.set_location(l_proc,40);
2288: select competence_id into l_competence_id from per_competences_vl
2289: where ( (trim(upper(name))) = (trim(upper(p_competence_name)))) or ((trim(upper(competence_alias))) = (trim(upper(p_competence_alias))) );
2290: if sql%notfound then
2291: hr_utility.set_location(l_proc,45);
2287: hr_utility.set_location(l_proc,40);
2288: select competence_id into l_competence_id from per_competences_vl
2289: where ( (trim(upper(name))) = (trim(upper(p_competence_name)))) or ((trim(upper(competence_alias))) = (trim(upper(p_competence_alias))) );
2290: if sql%notfound then
2291: hr_utility.set_location(l_proc,45);
2292: raise g_invalid_competence;
2293: end if;
2294: end if;
2295: hr_utility.set_location(' Leaving:' || l_proc,50);
2291: hr_utility.set_location(l_proc,45);
2292: raise g_invalid_competence;
2293: end if;
2294: end if;
2295: hr_utility.set_location(' Leaving:' || l_proc,50);
2296: exception
2297: when g_invalid_competence then
2298: hr_utility.set_location(' Leaving:' || l_proc,555);
2299: raise g_invalid_competence;
2294: end if;
2295: hr_utility.set_location(' Leaving:' || l_proc,50);
2296: exception
2297: when g_invalid_competence then
2298: hr_utility.set_location(' Leaving:' || l_proc,555);
2299: raise g_invalid_competence;
2300: when others then
2301: hr_utility.set_location(' Leaving:' || l_proc,560);
2302: raise g_invalid_competence;
2297: when g_invalid_competence then
2298: hr_utility.set_location(' Leaving:' || l_proc,555);
2299: raise g_invalid_competence;
2300: when others then
2301: hr_utility.set_location(' Leaving:' || l_proc,560);
2302: raise g_invalid_competence;
2303: end get_lov_comp_id;
2304: -------------------------------------
2305: PROCEDURE write_add_transaction(
2334: l_result varchar2(100) default null;
2335: l_trans_obj_vers_num number default null;
2336: l_action_person_id number default null;
2337: begin
2338: hr_utility.set_location(' Entering:' || l_proc,5);
2339: if p_pid is not null then
2340: hr_utility.set_location(' Entering:' || l_proc,10);
2341: x_person_id := to_number(p_pid);
2342: end if ;
2336: l_action_person_id number default null;
2337: begin
2338: hr_utility.set_location(' Entering:' || l_proc,5);
2339: if p_pid is not null then
2340: hr_utility.set_location(' Entering:' || l_proc,10);
2341: x_person_id := to_number(p_pid);
2342: end if ;
2343: if p_competence_id is not null then
2344: hr_utility.set_location(' Entering:' || l_proc,15);
2340: hr_utility.set_location(' Entering:' || l_proc,10);
2341: x_person_id := to_number(p_pid);
2342: end if ;
2343: if p_competence_id is not null then
2344: hr_utility.set_location(' Entering:' || l_proc,15);
2345: x_competence_id := to_number(p_competence_id);
2346: end if;
2347: if p_proficiency_level_id is not null then
2348: hr_utility.set_location(' Entering:' || l_proc,20);
2344: hr_utility.set_location(' Entering:' || l_proc,15);
2345: x_competence_id := to_number(p_competence_id);
2346: end if;
2347: if p_proficiency_level_id is not null then
2348: hr_utility.set_location(' Entering:' || l_proc,20);
2349: x_prof_level_id := to_number(p_proficiency_level_id);
2350: end if;
2351: if p_step_value is not null then
2352: hr_utility.set_location(' Entering:' || l_proc,25);
2348: hr_utility.set_location(' Entering:' || l_proc,20);
2349: x_prof_level_id := to_number(p_proficiency_level_id);
2350: end if;
2351: if p_step_value is not null then
2352: hr_utility.set_location(' Entering:' || l_proc,25);
2353: x_step_value := to_number(p_step_value);
2354: end if;
2355: hr_utility.set_location(' Entering:' || l_proc,30);
2356: l_action_person_id := x_person_id;
2351: if p_step_value is not null then
2352: hr_utility.set_location(' Entering:' || l_proc,25);
2353: x_step_value := to_number(p_step_value);
2354: end if;
2355: hr_utility.set_location(' Entering:' || l_proc,30);
2356: l_action_person_id := x_person_id;
2357: g_business_group_id := hr_util_misc_web.get_business_group_id
2358: (p_person_id => l_action_person_id);
2359: l_user_date_format := g_date_format;
2357: g_business_group_id := hr_util_misc_web.get_business_group_id
2358: (p_person_id => l_action_person_id);
2359: l_user_date_format := g_date_format;
2360: if p_eff_date_from is not null then
2361: hr_utility.set_location(' Entering:' || l_proc,40);
2362: l_eff_date_from := to_date(p_eff_date_from, l_user_date_format);
2363: end if;
2364: l_transaction_id := hr_transaction_ss.get_transaction_id
2365: (p_item_type => p_item_type
2364: l_transaction_id := hr_transaction_ss.get_transaction_id
2365: (p_item_type => p_item_type
2366: ,p_item_key => p_item_key);
2367: IF l_transaction_id is null THEN
2368: hr_utility.set_location(' Entering:' || l_proc,45);
2369: -- Start a Transaction
2370: hr_transaction_ss.start_transaction
2371: (itemtype => p_item_type
2372: ,itemkey => p_item_key
2378: (p_item_type => p_item_type
2379: ,p_item_key => p_item_key);
2380: END IF;
2381: -- Create a transaction step
2382: hr_utility.set_location(' Entering:' || l_proc,50);
2383: IF p_transaction_step_id is null then
2384: hr_utility.set_location(' Entering:' || l_proc,55);
2385: hr_transaction_api.create_transaction_step
2386: (p_validate => false
2380: END IF;
2381: -- Create a transaction step
2382: hr_utility.set_location(' Entering:' || l_proc,50);
2383: IF p_transaction_step_id is null then
2384: hr_utility.set_location(' Entering:' || l_proc,55);
2385: hr_transaction_api.create_transaction_step
2386: (p_validate => false
2387: ,p_creator_person_id => fnd_global.employee_id
2388: ,p_transaction_id => l_transaction_id
2393: ,p_transaction_step_id => p_transaction_step_id
2394: ,p_object_version_number => l_trans_obj_vers_num);
2395: end if;
2396: --
2397: hr_utility.set_location(' Entering:' || l_proc,60);
2398: -- ---------------------------
2399: -- insert the new rec here
2400: -- ---------------------------
2401: l_count := l_count + 1;
2495: ,p_login_person_id => fnd_global.employee_id
2496: ,p_transaction_step_id => p_transaction_step_id
2497: ,p_api_name => g_api_name
2498: ,p_transaction_data => l_trans_tbl);
2499: hr_utility.set_location(' Leaving:' || l_proc,65);
2500: exception
2501: when others then
2502: hr_utility.set_location(' Leaving:' || l_proc,555);
2503: raise g_invalid_entry;
2498: ,p_transaction_data => l_trans_tbl);
2499: hr_utility.set_location(' Leaving:' || l_proc,65);
2500: exception
2501: when others then
2502: hr_utility.set_location(' Leaving:' || l_proc,555);
2503: raise g_invalid_entry;
2504: end write_add_transaction;
2505: --------------------------------------------
2506: -- to check valid proposed level entered manually by user on update main page
2523: x_cer_date date;
2524: x_next_cer_date date;
2525: l_count number;
2526: begin
2527: hr_utility.set_location(' Entering:' || l_proc,5);
2528: l_count := 0;
2529: update_date_validate (p_person_id => p_person_id
2530: ,p_competence_id => p_competence_id
2531: ,p_eff_date_from => p_eff_date_from
2539: and ratl.step_value = p_step_value;
2540: r_step_value := l_step_value ;
2541: r_new_prof_level := l_new_prof_level;
2542: END if;
2543: hr_utility.set_location(' Leaving:' || l_proc,10);
2544: -- added for competency start date > outcome start date
2545: Select count(*) INTO l_count
2546: FROM hr_api_transaction_steps S,
2547: hr_api_transaction_values A,
2572:
2573: --
2574: exception
2575: when no_data_found then
2576: hr_utility.set_location(' Leaving:' || l_proc,555);
2577: If p_step_value is not null then
2578: hr_utility.set_location(' Leaving:' || l_proc,560);
2579: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2580: p_error_message => p_error_message,
2574: exception
2575: when no_data_found then
2576: hr_utility.set_location(' Leaving:' || l_proc,555);
2577: If p_step_value is not null then
2578: hr_utility.set_location(' Leaving:' || l_proc,560);
2579: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2580: p_error_message => p_error_message,
2581: p_attr_name => 'PropLevel',
2582: p_app_short_name => 'PER',
2611: END if;
2612: End If;
2613: r_step_value := p_step_value;
2614: when others then
2615: hr_utility.set_location(' Leaving:' || l_proc,565);
2616: raise ;
2617: end validate_updated_row;
2618: -------------------------------------------------
2619: procedure set_name_alias
2621: ,l_competence_name out nocopy varchar2
2622: ,l_competence_alias out nocopy varchar2) is
2623: l_proc varchar2(200) := g_package || 'set_name_alias';
2624: begin
2625: hr_utility.set_location(' Entering:' || l_proc,5);
2626: select name,competence_alias
2627: into l_competence_name,l_competence_alias from per_competences_vl
2628: where competence_id = p_competence_id;
2629: hr_utility.set_location(' Leaving:' || l_proc,10);
2625: hr_utility.set_location(' Entering:' || l_proc,5);
2626: select name,competence_alias
2627: into l_competence_name,l_competence_alias from per_competences_vl
2628: where competence_id = p_competence_id;
2629: hr_utility.set_location(' Leaving:' || l_proc,10);
2630: exception
2631: when others then
2632: hr_utility.set_location(' Leaving:' || l_proc,555);
2633: null ;
2628: where competence_id = p_competence_id;
2629: hr_utility.set_location(' Leaving:' || l_proc,10);
2630: exception
2631: when others then
2632: hr_utility.set_location(' Leaving:' || l_proc,555);
2633: null ;
2634: raise ;
2635: end set_name_alias;
2636: --
2694: l_competence_name per_competences_vl.name%TYPE;
2695: l_competence_alias per_competences_vl.competence_alias%TYPE;
2696: --
2697: begin
2698: hr_utility.set_location(' Entering:' || l_proc,5);
2699: l_count := 0;
2700: select rtrim(ltrim(p_competence_name)), rtrim(ltrim(p_competence_alias)), rtrim(ltrim(p_competence_id)), rtrim(ltrim(p_step_value))
2701: into p_competence_name, p_competence_alias, p_competence_id, p_step_value
2702: from dual;
2700: select rtrim(ltrim(p_competence_name)), rtrim(ltrim(p_competence_alias)), rtrim(ltrim(p_competence_id)), rtrim(ltrim(p_step_value))
2701: into p_competence_name, p_competence_alias, p_competence_id, p_step_value
2702: from dual;
2703: If p_competence_id is not null and p_competence_name is null Then
2704: hr_utility.set_location(l_proc,10);
2705: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2706: p_error_message => p_error_message,
2707: p_attr_name => 'Name',
2708: p_app_short_name => 'PER',
2708: p_app_short_name => 'PER',
2709: P_SINGLE_ERROR_MESSAGE => 'HR_ADD_COMP_IS_NULL_SS');
2710: raise g_invalid_entry;
2711: End If;
2712: hr_utility.set_location(l_proc,15);
2713: If p_competence_id is not null then
2714: hr_utility.set_location(l_proc,20);
2715: select name, competence_alias
2716: into l_competence_name, l_competence_alias
2710: raise g_invalid_entry;
2711: End If;
2712: hr_utility.set_location(l_proc,15);
2713: If p_competence_id is not null then
2714: hr_utility.set_location(l_proc,20);
2715: select name, competence_alias
2716: into l_competence_name, l_competence_alias
2717: from per_competences_vl
2718: where competence_id = p_competence_id;
2716: into l_competence_name, l_competence_alias
2717: from per_competences_vl
2718: where competence_id = p_competence_id;
2719: End if;
2720: hr_utility.set_location(l_proc,25);
2721: If (l_competence_name <> p_competence_name or l_competence_alias <> p_competence_alias) Or
2722: ((p_competence_id is null) And (p_competence_name is not null or p_competence_alias is not null)) Then
2723: Begin
2724: hr_utility.set_location(l_proc,30);
2720: hr_utility.set_location(l_proc,25);
2721: If (l_competence_name <> p_competence_name or l_competence_alias <> p_competence_alias) Or
2722: ((p_competence_id is null) And (p_competence_name is not null or p_competence_alias is not null)) Then
2723: Begin
2724: hr_utility.set_location(l_proc,30);
2725: select name, competence_alias, competence_id
2726: into p_competence_name,p_competence_alias,p_competence_id
2727: from per_competences_vl
2728: where upper(name) = nvl(upper(p_competence_name), upper(name))
2728: where upper(name) = nvl(upper(p_competence_name), upper(name))
2729: and nvl(upper(competence_alias),'#') = nvl(upper(p_competence_alias), nvl(upper(competence_alias),'#'))
2730: and (business_group_id+0 = p_business_group_id or business_group_id is null);
2731: Exception when OTHERS then
2732: hr_utility.set_location(l_proc,555);
2733: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2734: p_error_message => p_error_message,
2735: p_attr_name => 'Name',
2736: p_app_short_name => 'PER',
2737: P_SINGLE_ERROR_MESSAGE => 'HR_COMP_INVALID_NAME_ALIAS_SS');
2738: raise g_invalid_entry;
2739: End;
2740: End If;
2741: hr_utility.set_location(l_proc,35);
2742: If p_step_value is not null then
2743: Begin
2744: hr_utility.set_location(l_proc,40);
2745: select per_rating_levels.step_value ,per_rating_levels.rating_level_id
2740: End If;
2741: hr_utility.set_location(l_proc,35);
2742: If p_step_value is not null then
2743: Begin
2744: hr_utility.set_location(l_proc,40);
2745: select per_rating_levels.step_value ,per_rating_levels.rating_level_id
2746: into p_step_value,p_prof_level_id
2747: from per_competences_vl, per_rating_levels
2748: where ((per_rating_levels.competence_id = per_competences_vl.competence_id
2750: and (per_competences_vl.competence_id = p_competence_id) and (per_rating_levels.step_value = p_step_value))
2751: and (per_competences_vl.business_group_id+0 = p_business_group_id
2752: or per_competences_vl.business_group_id is null);
2753: Exception when OTHERS then
2754: hr_utility.set_location(l_proc,560);
2755: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2756: p_error_message => p_error_message,
2757: p_attr_name => 'ProfLevel',
2758: p_app_short_name => 'PER',
2812: END if;
2813: -- end for bug fix 4136402
2814: END if;
2815:
2816: hr_utility.set_location(l_proc,45);
2817: -- bug 2946360 fix
2818: For I in c1 Loop
2819: hr_utility.set_location(l_proc || 'LOOP' ,50);
2820: If (I.status = 'F') then
2815:
2816: hr_utility.set_location(l_proc,45);
2817: -- bug 2946360 fix
2818: For I in c1 Loop
2819: hr_utility.set_location(l_proc || 'LOOP' ,50);
2820: If (I.status = 'F') then
2821: hr_utility.set_location(l_proc,55);
2822: l_dup_comp_not_exists := I.status;
2823: Exit;
2817: -- bug 2946360 fix
2818: For I in c1 Loop
2819: hr_utility.set_location(l_proc || 'LOOP' ,50);
2820: If (I.status = 'F') then
2821: hr_utility.set_location(l_proc,55);
2822: l_dup_comp_not_exists := I.status;
2823: Exit;
2824: End If;
2825: End Loop;
2822: l_dup_comp_not_exists := I.status;
2823: Exit;
2824: End If;
2825: End Loop;
2826: hr_utility.set_location(l_proc,60);
2827: IF l_dup_comp_not_exists IS NOT NULL
2828: THEN
2829: hr_utility.set_location(l_proc,65);
2830: p_dup_comp_not_exists := l_dup_comp_not_exists;
2825: End Loop;
2826: hr_utility.set_location(l_proc,60);
2827: IF l_dup_comp_not_exists IS NOT NULL
2828: THEN
2829: hr_utility.set_location(l_proc,65);
2830: p_dup_comp_not_exists := l_dup_comp_not_exists;
2831: ELSE
2832: hr_utility.set_location(l_proc,70);
2833: p_dup_comp_not_exists := 'T';
2828: THEN
2829: hr_utility.set_location(l_proc,65);
2830: p_dup_comp_not_exists := l_dup_comp_not_exists;
2831: ELSE
2832: hr_utility.set_location(l_proc,70);
2833: p_dup_comp_not_exists := 'T';
2834: END IF;
2835: -- bug 2946360 fix
2836: hr_utility.set_location(' Leaving:' || l_proc,75);
2832: hr_utility.set_location(l_proc,70);
2833: p_dup_comp_not_exists := 'T';
2834: END IF;
2835: -- bug 2946360 fix
2836: hr_utility.set_location(' Leaving:' || l_proc,75);
2837: Exception
2838: when g_invalid_entry then
2839: hr_utility.set_location(' Leaving:' || l_proc,565);
2840: null;
2835: -- bug 2946360 fix
2836: hr_utility.set_location(' Leaving:' || l_proc,75);
2837: Exception
2838: when g_invalid_entry then
2839: hr_utility.set_location(' Leaving:' || l_proc,565);
2840: null;
2841: when others then
2842: hr_utility.set_location(' Leaving:' || l_proc,570);
2843: raise;
2838: when g_invalid_entry then
2839: hr_utility.set_location(' Leaving:' || l_proc,565);
2840: null;
2841: when others then
2842: hr_utility.set_location(' Leaving:' || l_proc,570);
2843: raise;
2844: end set_parameters;
2845: ------------------------------------------------
2846: ------------------------------------------------
2850: l_proc varchar2(200) := g_package || 'set_upd_parameters';
2851: l_step_value varchar2(20);
2852: l_prof_level_id varchar2(20);
2853: begin
2854: hr_utility.set_location(' Entering:' || l_proc,5);
2855: if p_step_value is not null then
2856: hr_utility.set_location(l_proc,10);
2857: select per_rating_levels.step_value ,per_rating_levels.rating_level_id
2858: into l_step_value,l_prof_level_id
2852: l_prof_level_id varchar2(20);
2853: begin
2854: hr_utility.set_location(' Entering:' || l_proc,5);
2855: if p_step_value is not null then
2856: hr_utility.set_location(l_proc,10);
2857: select per_rating_levels.step_value ,per_rating_levels.rating_level_id
2858: into l_step_value,l_prof_level_id
2859: from per_competences_vl, per_rating_levels
2860: where ((per_rating_levels.competence_id = per_competences_vl.competence_id or per_competences_vl.rating_scale_id = per_rating_levels.rating_scale_id)
2859: from per_competences_vl, per_rating_levels
2860: where ((per_rating_levels.competence_id = per_competences_vl.competence_id or per_competences_vl.rating_scale_id = per_rating_levels.rating_scale_id)
2861: and (per_competences_vl.competence_id = p_competence_id) and (per_rating_levels.step_value = p_step_value) );
2862: if sql%notfound then
2863: hr_utility.set_location(l_proc,15);
2864: raise g_invalid_competence;
2865: end if;
2866: end if;
2867: hr_utility.set_location(' Leaving:' || l_proc,20);
2863: hr_utility.set_location(l_proc,15);
2864: raise g_invalid_competence;
2865: end if;
2866: end if;
2867: hr_utility.set_location(' Leaving:' || l_proc,20);
2868: exception
2869: when g_invalid_competence then
2870: hr_utility.set_location(' Leaving:' || l_proc,555);
2871: raise g_invalid_competence;
2866: end if;
2867: hr_utility.set_location(' Leaving:' || l_proc,20);
2868: exception
2869: when g_invalid_competence then
2870: hr_utility.set_location(' Leaving:' || l_proc,555);
2871: raise g_invalid_competence;
2872: when others then
2873: hr_utility.set_location(' Leaving:' || l_proc,560);
2874: raise g_invalid_competence;
2869: when g_invalid_competence then
2870: hr_utility.set_location(' Leaving:' || l_proc,555);
2871: raise g_invalid_competence;
2872: when others then
2873: hr_utility.set_location(' Leaving:' || l_proc,560);
2874: raise g_invalid_competence;
2875: end set_upd_parameters;
2876: ---------------------------------------------------------------
2877: Procedure delete_add_page
2879: l_proc varchar2(200) := g_package || 'delete_add_page';
2880: len number;
2881: j varchar(10) ;
2882: begin
2883: hr_utility.set_location(' Entering:' || l_proc,5);
2884: j := ' ';
2885: len := length(transaction_step_ids);
2886: for i in 1..len loop
2887: hr_utility.set_location(l_proc || 'LOOP' , 10);
2883: hr_utility.set_location(' Entering:' || l_proc,5);
2884: j := ' ';
2885: len := length(transaction_step_ids);
2886: for i in 1..len loop
2887: hr_utility.set_location(l_proc || 'LOOP' , 10);
2888: if substr(transaction_step_ids,i,1) = ',' then
2889: hr_utility.set_location(l_proc , 15);
2890: -- Calling the procedure to delete related transaction step ids for outcome.
2891: /* HR_COMP_OUTCOME_PROFILE_SS.delete_add_page(
2885: len := length(transaction_step_ids);
2886: for i in 1..len loop
2887: hr_utility.set_location(l_proc || 'LOOP' , 10);
2888: if substr(transaction_step_ids,i,1) = ',' then
2889: hr_utility.set_location(l_proc , 15);
2890: -- Calling the procedure to delete related transaction step ids for outcome.
2891: /* HR_COMP_OUTCOME_PROFILE_SS.delete_add_page(
2892: p_transaction_step_id => to_number(j)); */
2893: delete from hr_api_transaction_values where transaction_step_id = to_number(j);
2893: delete from hr_api_transaction_values where transaction_step_id = to_number(j);
2894: delete from hr_api_transaction_steps where transaction_step_id = to_number(j);
2895: j := '' ;
2896: else
2897: hr_utility.set_location(l_proc , 20);
2898: j := j || substr(transaction_step_ids,i,1);
2899: end if;
2900: end loop;
2901: commit;
2898: j := j || substr(transaction_step_ids,i,1);
2899: end if;
2900: end loop;
2901: commit;
2902: hr_utility.set_location(' Leaving:' || l_proc,25);
2903: end delete_add_page;
2904: ---------------------------------------------------------------
2905: procedure add_to_addition(p_item_type in varchar2
2906: ,p_item_key in varchar2) is
2906: ,p_item_key in varchar2) is
2907: l_proc varchar2(200) := g_package || 'add_to_addition';
2908: l_transaction_id number;
2909: begin
2910: hr_utility.set_location(' Entering:' || l_proc,5);
2911: l_transaction_id := hr_transaction_ss.get_transaction_id
2912: (p_item_type => p_item_type
2913: ,p_item_key => p_item_key);
2914: update hr_api_transaction_values val
2916: where transaction_step_id in (select transaction_step_id
2917: from hr_api_transaction_steps steps
2918: where steps.transaction_id = l_transaction_id)
2919: and val.name = 'P_CHANGE_MODE' and val.varchar2_value = 'ADD';
2920: hr_utility.set_location(' Leaving:' || l_proc,10);
2921: end add_to_addition;
2922: -------------------------------------------------------------
2923: procedure update_date_validate
2924: (p_person_id in varchar2 default null
2928: l_proc varchar2(200) := g_package || 'update_date_validate';
2929: l_eff_date_from date default null;
2930: l_start_date date;
2931: begin
2932: hr_utility.set_location(' Entering:' || l_proc,5);
2933: l_start_date := to_date(p_eff_date_from,g_date_format) ;
2934: if l_start_date > trunc(sysdate) then
2935: hr_utility.set_location( l_proc,10);
2936: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2931: begin
2932: hr_utility.set_location(' Entering:' || l_proc,5);
2933: l_start_date := to_date(p_eff_date_from,g_date_format) ;
2934: if l_start_date > trunc(sysdate) then
2935: hr_utility.set_location( l_proc,10);
2936: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2937: p_error_message => p_error_message,
2938: p_attr_name => 'PropStartDate',
2939: p_app_short_name => 'PER',
2939: p_app_short_name => 'PER',
2940: P_SINGLE_ERROR_MESSAGE => 'HR_COMP_ADD_ERR_START_DATE_SS');
2941: Else
2942: begin
2943: hr_utility.set_location( l_proc,15);
2944: select max(effective_date_from)
2945: into l_eff_date_from
2946: from per_competence_elements
2947: where person_id = to_number(p_person_id)
2946: from per_competence_elements
2947: where person_id = to_number(p_person_id)
2948: and competence_id = to_number(p_competence_id) group by competence_id;
2949: if l_start_date <= l_eff_date_from then
2950: hr_utility.set_location( l_proc,20);
2951: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2952: p_error_message => p_error_message,
2953: p_attr_name => 'PropStartDate',
2954: p_app_short_name => 'PER',
2956: P_SINGLE_ERROR_MESSAGE => 'HR_WEB_CEL_START_DATES_INVL');
2957: end if;
2958: end;
2959: end if;
2960: hr_utility.set_location(' Leaving:' || l_proc,25);
2961: exception
2962: when others then
2963: hr_utility.set_location(' Leaving:' || l_proc,555);
2964: null;
2959: end if;
2960: hr_utility.set_location(' Leaving:' || l_proc,25);
2961: exception
2962: when others then
2963: hr_utility.set_location(' Leaving:' || l_proc,555);
2964: null;
2965: raise g_invalid_entry;
2966: end update_date_validate;
2967: -------------------------------
2972: l_proc varchar2(200) := g_package || 'ex_comp_date_validation';
2973: l_eff_date_from date default null;
2974: m_eff_date_to date default null;
2975: begin
2976: hr_utility.set_location(' Entering:' || l_proc,5);
2977: l_eff_date_from := to_date(p_eff_date_from,g_date_format);
2978: select effective_date_to
2979: into m_eff_date_to
2980: from per_competence_elements
2981: where person_id = p_person_id
2982: and competence_id = p_competence_id
2983: and effective_date_to < trunc(sysdate);
2984: if SQL%FOUND then
2985: hr_utility.set_location(l_proc,10);
2986: if l_eff_date_from < m_eff_date_to then
2987: hr_utility.set_location(l_proc,15);
2988: raise g_invalid_competence;
2989: end if;
2983: and effective_date_to < trunc(sysdate);
2984: if SQL%FOUND then
2985: hr_utility.set_location(l_proc,10);
2986: if l_eff_date_from < m_eff_date_to then
2987: hr_utility.set_location(l_proc,15);
2988: raise g_invalid_competence;
2989: end if;
2990: end if;
2991: hr_utility.set_location(' Leaving:' || l_proc,20);
2987: hr_utility.set_location(l_proc,15);
2988: raise g_invalid_competence;
2989: end if;
2990: end if;
2991: hr_utility.set_location(' Leaving:' || l_proc,20);
2992: end ex_comp_date_validation;
2993: --------------------------------------------------------
2994: ----------------------------------------------------------------------------
2995: Procedure write_proc_actid
3010: l_count number := 0;
3011: l_trans_tbl hr_comp_profile_ss.transaction_table1;
3012: l_transaction_step_id number;
3013: begin
3014: hr_utility.set_location(' Entering:' || l_proc,5);
3015: l_index := 0;
3016: open c1 ;
3017: loop
3018: hr_utility.set_location(l_proc || 'LOOP' ,10);
3014: hr_utility.set_location(' Entering:' || l_proc,5);
3015: l_index := 0;
3016: open c1 ;
3017: loop
3018: hr_utility.set_location(l_proc || 'LOOP' ,10);
3019: fetch c1 into l_step_values;
3020: exit when c1%notfound;
3021: l_count := 1;
3022: l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
3037: ,p_api_name => g_api_name
3038: ,p_transaction_data => l_trans_tbl);
3039: end loop ;
3040: close c1;
3041: hr_utility.set_location(' Leaving:' || l_proc,15);
3042: end write_proc_actid;
3043: --====================================================================================
3044: Procedure api_validate_competence_record(
3045: p_validate in boolean default null
3076: l_object_version_number number default null;
3077: l_action_person_id number default null;
3078: --
3079: Begin
3080: hr_utility.set_location(' Entering:' || l_proc,5);
3081: l_action_person_id := p_person_id;
3082: Begin
3083: hr_utility.set_location( l_proc,10);
3084: IF p_eff_date_from is not null THEN
3079: Begin
3080: hr_utility.set_location(' Entering:' || l_proc,5);
3081: l_action_person_id := p_person_id;
3082: Begin
3083: hr_utility.set_location( l_proc,10);
3084: IF p_eff_date_from is not null THEN
3085: hr_utility.set_location(l_proc,15);
3086: l_eff_date_from := to_date(p_eff_date_from, l_user_date_format);
3087: END IF;
3081: l_action_person_id := p_person_id;
3082: Begin
3083: hr_utility.set_location( l_proc,10);
3084: IF p_eff_date_from is not null THEN
3085: hr_utility.set_location(l_proc,15);
3086: l_eff_date_from := to_date(p_eff_date_from, l_user_date_format);
3087: END IF;
3088: IF l_eff_date_from > trunc(sysdate) THEN
3089: hr_utility.set_location(l_proc,20);
3085: hr_utility.set_location(l_proc,15);
3086: l_eff_date_from := to_date(p_eff_date_from, l_user_date_format);
3087: END IF;
3088: IF l_eff_date_from > trunc(sysdate) THEN
3089: hr_utility.set_location(l_proc,20);
3090: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
3091: p_error_message => p_error_message,
3092: p_attr_name => 'CurrStartDate',
3093: p_app_short_name => 'PER',
3093: p_app_short_name => 'PER',
3094: P_SINGLE_ERROR_MESSAGE => 'HR_WEB_DISALLOW_FUTURE_STARTDT');
3095: l_date_error := true;
3096: END IF;
3097: hr_utility.set_location(l_proc,25);
3098: EXCEPTION
3099: When others then
3100: hr_utility.set_location(l_proc,555);
3101: IF hr_message.last_message_name = 'HR_51648_CEL_PER_DATES_OVLAP' then
3096: END IF;
3097: hr_utility.set_location(l_proc,25);
3098: EXCEPTION
3099: When others then
3100: hr_utility.set_location(l_proc,555);
3101: IF hr_message.last_message_name = 'HR_51648_CEL_PER_DATES_OVLAP' then
3102: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3103: (p_error_message => p_error_message,
3104: p_attr_name => 'CurrStartDate',
3114: END if;
3115: l_date_error := true;
3116: END;
3117: Begin
3118: hr_utility.set_location(l_proc,30);
3119: IF p_eff_date_to is not null THEN
3120: hr_utility.set_location(l_proc,35);
3121: l_eff_date_to := to_date(p_eff_date_to, l_user_date_format);
3122: END IF;
3116: END;
3117: Begin
3118: hr_utility.set_location(l_proc,30);
3119: IF p_eff_date_to is not null THEN
3120: hr_utility.set_location(l_proc,35);
3121: l_eff_date_to := to_date(p_eff_date_to, l_user_date_format);
3122: END IF;
3123: IF l_eff_date_from is not null then
3124: hr_utility.set_location(l_proc,40);
3120: hr_utility.set_location(l_proc,35);
3121: l_eff_date_to := to_date(p_eff_date_to, l_user_date_format);
3122: END IF;
3123: IF l_eff_date_from is not null then
3124: hr_utility.set_location(l_proc,40);
3125: IF l_eff_date_to < l_eff_date_from THEN
3126: hr_utility.set_location(l_proc,45);
3127: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
3128: p_error_message => p_error_message,
3122: END IF;
3123: IF l_eff_date_from is not null then
3124: hr_utility.set_location(l_proc,40);
3125: IF l_eff_date_to < l_eff_date_from THEN
3126: hr_utility.set_location(l_proc,45);
3127: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
3128: p_error_message => p_error_message,
3129: p_attr_name => 'EndDate',
3130: p_app_short_name => 'PER',
3133: END IF;
3134: End IF;
3135: EXCEPTION
3136: When others then
3137: hr_utility.set_location(l_proc,560);
3138: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3139: (p_error_message => p_error_message,
3140: p_attr_name => 'EndDate',
3141: p_app_short_name => 'PER',
3142: P_SINGLE_ERROR_MESSAGE => SQLERRM ||' '||to_char(SQLCODE));
3143: l_date_error := true;
3144: END;
3145: Begin
3146: hr_utility.set_location(l_proc,50);
3147: IF p_certification_date is not null THEN
3148: hr_utility.set_location(l_proc,55);
3149: l_certification_date :=to_date(p_certification_date, l_user_date_format);
3150: IF p_certification_mthd is null THEN
3144: END;
3145: Begin
3146: hr_utility.set_location(l_proc,50);
3147: IF p_certification_date is not null THEN
3148: hr_utility.set_location(l_proc,55);
3149: l_certification_date :=to_date(p_certification_date, l_user_date_format);
3150: IF p_certification_mthd is null THEN
3151: hr_utility.set_location(l_proc,60);
3152: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
3147: IF p_certification_date is not null THEN
3148: hr_utility.set_location(l_proc,55);
3149: l_certification_date :=to_date(p_certification_date, l_user_date_format);
3150: IF p_certification_mthd is null THEN
3151: hr_utility.set_location(l_proc,60);
3152: p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
3153: p_error_message => p_error_message,
3154: p_attr_name => 'MeasuredBy',
3155: p_app_short_name => 'PER',
3159: END IF;
3160: --
3161: EXCEPTION
3162: When others then
3163: hr_utility.set_location(l_proc,565);
3164: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3165: (p_error_message => p_error_message,
3166: p_attr_name => 'CertificationDate',
3167: p_app_short_name => 'PER',
3170: END;
3171: --
3172: -- next_certification_date
3173: Begin
3174: hr_utility.set_location(l_proc,65);
3175: IF p_next_certifctn_date is not null THEN
3176: hr_utility.set_location(l_proc,70);
3177: l_next_certification_date := to_date(p_next_certifctn_date, l_user_date_format);
3178: END IF;
3172: -- next_certification_date
3173: Begin
3174: hr_utility.set_location(l_proc,65);
3175: IF p_next_certifctn_date is not null THEN
3176: hr_utility.set_location(l_proc,70);
3177: l_next_certification_date := to_date(p_next_certifctn_date, l_user_date_format);
3178: END IF;
3179: --
3180: EXCEPTION
3178: END IF;
3179: --
3180: EXCEPTION
3181: When others then
3182: hr_utility.set_location(l_proc,570);
3183: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3184: (p_error_message => p_error_message,
3185: p_attr_name => 'NextReviewDate',
3186: p_app_short_name => 'PER',
3192: -- Now, if no date format error, we proceed to call api for cross
3193: -- validations.
3194: --------------------------------------------------------------------
3195: savepoint validate_competence_rec;
3196: hr_utility.set_location(l_proc,75);
3197: IF l_date_error THEN
3198: hr_utility.set_location(l_proc,80);
3199: goto finish_processing;
3200: END IF;
3194: --------------------------------------------------------------------
3195: savepoint validate_competence_rec;
3196: hr_utility.set_location(l_proc,75);
3197: IF l_date_error THEN
3198: hr_utility.set_location(l_proc,80);
3199: goto finish_processing;
3200: END IF;
3201: --
3202: -- UPGRADE
3200: END IF;
3201: --
3202: -- UPGRADE
3203: IF upper(p_change_mode) = hr_comp_profile_ss.g_upgrade_proficiency_mode THEN
3204: hr_utility.set_location(l_proc,85);
3205: l_preupd_date_to := l_eff_date_from - 1;
3206: l_object_version_number := p_preupd_obj_vers_num;
3207: --
3208: per_cel_upd.upd(p_validate => false
3228: ,p_status => p_competence_status
3229: ,p_effective_date => trunc(sysdate));
3230: -- CORRECT
3231: ELSIF upper(p_change_mode) = hr_comp_profile_ss.g_upd_mode THEN
3232: hr_utility.set_location(l_proc,90);
3233: l_object_version_number := p_preupd_obj_vers_num;
3234: per_cel_upd.upd(p_validate => false
3235: ,p_competence_element_id => p_competence_element_id
3236: ,p_object_version_number => l_object_version_number
3243: ,p_next_certification_date => l_next_certification_date
3244: ,p_status => p_competence_status
3245: ,p_effective_date => trunc(sysdate));
3246: ELSE -- Add New Record Mode
3247: hr_utility.set_location(l_proc,95);
3248: per_cel_ins.ins(p_validate => false
3249: ,p_competence_element_id => l_new_competence_element_id
3250: ,p_business_group_id => p_business_group_id
3251: ,p_object_version_number => l_new_obj_vers_num
3262: ,p_status => p_competence_status
3263: ,p_effective_date => trunc(sysdate));
3264: END IF;
3265: --
3266: hr_utility.set_location(l_proc,100);
3267: IF p_validate = true THEN
3268: hr_utility.set_location(l_proc,105);
3269: rollback to validate_competence_rec;
3270: END IF;
3264: END IF;
3265: --
3266: hr_utility.set_location(l_proc,100);
3267: IF p_validate = true THEN
3268: hr_utility.set_location(l_proc,105);
3269: rollback to validate_competence_rec;
3270: END IF;
3271: --
3272: --
3271: --
3272: --
3273: <
3274: --
3275: hr_utility.set_location(l_proc,110);
3276: if l_date_error then
3277: hr_utility.set_location(l_proc,115);
3278: rollback to validate_competence_rec;
3279: end if;
3273: <
3274: --
3275: hr_utility.set_location(l_proc,110);
3276: if l_date_error then
3277: hr_utility.set_location(l_proc,115);
3278: rollback to validate_competence_rec;
3279: end if;
3280: p_eff_date_from_date_type := l_eff_date_from;
3281: p_eff_date_to_date_type := l_eff_date_to;
3281: p_eff_date_to_date_type := l_eff_date_to;
3282: p_certifctn_date_type := l_certification_date;
3283: p_next_certifctn_date_type := l_next_certification_date;
3284: --
3285: hr_utility.set_location(' Leaving:' || l_proc,120);
3286: EXCEPTION
3287: When g_invalid_entry then
3288: hr_utility.set_location(' Leaving:' || l_proc,575);
3289: rollback to validate_competence_rec;
3284: --
3285: hr_utility.set_location(' Leaving:' || l_proc,120);
3286: EXCEPTION
3287: When g_invalid_entry then
3288: hr_utility.set_location(' Leaving:' || l_proc,575);
3289: rollback to validate_competence_rec;
3290: When others then
3291: hr_utility.set_location(' Leaving:' || l_proc,580);
3292: rollback to validate_competence_rec;
3287: When g_invalid_entry then
3288: hr_utility.set_location(' Leaving:' || l_proc,575);
3289: rollback to validate_competence_rec;
3290: When others then
3291: hr_utility.set_location(' Leaving:' || l_proc,580);
3292: rollback to validate_competence_rec;
3293: hr_message.provide_error;
3294: IF hr_message.last_message_name = 'HR_51612_CEL_BUS_GROUP_ID_INVL' THEN
3295: hr_utility.set_location(' Leaving:' || l_proc,585);
3291: hr_utility.set_location(' Leaving:' || l_proc,580);
3292: rollback to validate_competence_rec;
3293: hr_message.provide_error;
3294: IF hr_message.last_message_name = 'HR_51612_CEL_BUS_GROUP_ID_INVL' THEN
3295: hr_utility.set_location(' Leaving:' || l_proc,585);
3296: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3297: (p_error_message => p_error_message
3298: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3299: ELSIF hr_message.last_message_name = 'HR_51615_CEL_PROF_ID_INVL' THEN
3296: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3297: (p_error_message => p_error_message
3298: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3299: ELSIF hr_message.last_message_name = 'HR_51615_CEL_PROF_ID_INVL' THEN
3300: hr_utility.set_location(' Leaving:' || l_proc,590);
3301: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3302: (p_error_message => p_error_message
3303: ,p_attr_name => 'ProfLevel'
3304: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3302: (p_error_message => p_error_message
3303: ,p_attr_name => 'ProfLevel'
3304: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3305: ELSIF hr_message.last_message_name = 'HR_51636_CEL_CERTIF_INVL' THEN
3306: hr_utility.set_location(' Leaving:' || l_proc,595);
3307: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3308: (p_error_message => p_error_message
3309: ,p_attr_name => 'MeasuredBy'
3310: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3308: (p_error_message => p_error_message
3309: ,p_attr_name => 'MeasuredBy'
3310: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3311: ELSIF hr_message.last_message_name = 'HR_51637_CEL_CERF_DATE_METHOD' THEN
3312: hr_utility.set_location(' Leaving:' || l_proc,600);
3313: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3314: (p_error_message => p_error_message
3315: ,p_attr_name => 'CertificationDate'
3316: ,p_app_short_name => 'PER'
3315: ,p_attr_name => 'CertificationDate'
3316: ,p_app_short_name => 'PER'
3317: ,p_message_name => 'HR_WEB_CEL_CERF_DATE_METHOD');
3318: ELSIF hr_message.last_message_name = 'HR_51639_CEL_SOURCE_PROF_LVL' THEN
3319: hr_utility.set_location(' Leaving:' || l_proc,605);
3320: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3321: (p_error_message => p_error_message
3322: ,p_attr_name => 'AcquiredBy'
3323: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3321: (p_error_message => p_error_message
3322: ,p_attr_name => 'AcquiredBy'
3323: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3324: ELSIF hr_message.last_message_name = 'HR_51615_CEL_PROF_ID_INVL' THEN
3325: hr_utility.set_location(' Leaving:' || l_proc,610);
3326: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3327: (p_error_message => p_error_message
3328: ,p_attr_name => 'ProfLevel'
3329: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3327: (p_error_message => p_error_message
3328: ,p_attr_name => 'ProfLevel'
3329: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3330: ELSIF hr_message.last_message_name = 'HR_51642_COMP_ID_MANDATORY' THEN
3331: hr_utility.set_location(' Leaving:' || l_proc,615);
3332: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3333: (p_error_message => p_error_message
3334: ,p_attr_name => 'CompId'
3335: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3333: (p_error_message => p_error_message
3334: ,p_attr_name => 'CompId'
3335: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3336: ELSIF hr_message.last_message_name = 'HR_51647_CEL_DATES_INVL' THEN
3337: hr_utility.set_location(' Leaving:' || l_proc,620);
3338: IF upper(p_change_mode) =
3339: hr_comp_profile_ss.g_upgrade_proficiency_mode
3340: and (p_eff_date_to is null or
3341: to_date(p_eff_date_from, l_user_date_format) <=
3339: hr_comp_profile_ss.g_upgrade_proficiency_mode
3340: and (p_eff_date_to is null or
3341: to_date(p_eff_date_from, l_user_date_format) <=
3342: to_date(p_eff_date_to, l_user_date_format))THEN
3343: hr_utility.set_location(' Leaving:' || l_proc,625);
3344: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3345: (p_error_message => p_error_message
3346: ,p_attr_name => 'CurrStartDate'
3347: ,p_app_short_name => 'PER'
3346: ,p_attr_name => 'CurrStartDate'
3347: ,p_app_short_name => 'PER'
3348: ,p_message_name => 'HR_WEB_CEL_START_DATES_INVL');
3349: ELSE
3350: hr_utility.set_location(' Leaving:' || l_proc,630);
3351: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3352: (p_error_message => p_error_message
3353: ,p_attr_name => 'EndDate'
3354: ,p_app_short_name => 'PER'
3354: ,p_app_short_name => 'PER'
3355: ,p_message_name => 'HR_WEB_CEL_DATES_INVL');
3356: END IF;
3357: ELSIF hr_message.last_message_name = 'HR_51648_CEL_PER_DATES_OVLAP' THEN
3358: hr_utility.set_location(' Leaving:' || l_proc,635);
3359: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3360: (p_error_message => p_error_message
3361: ,p_attr_name => 'CurrStartDate'
3362: ,p_app_short_name => 'PER'
3363: ,p_message_name => 'HR_51648_CEL_PER_DATES_OVLAP');
3364: -- modified for 4142672 rpahune
3365: -- ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3366: ELSIF hr_message.last_message_name = 'HR_51670_CEL_PER_TYPE_ERROR' THEN
3367: hr_utility.set_location(' Leaving:' || l_proc,640);
3368: IF p_competence_id is null THEN
3369: hr_utility.set_location(' Leaving:' || l_proc,645);
3370: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3371: (p_error_message => p_error_message
3365: -- ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3366: ELSIF hr_message.last_message_name = 'HR_51670_CEL_PER_TYPE_ERROR' THEN
3367: hr_utility.set_location(' Leaving:' || l_proc,640);
3368: IF p_competence_id is null THEN
3369: hr_utility.set_location(' Leaving:' || l_proc,645);
3370: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3371: (p_error_message => p_error_message
3372: ,p_attr_name => 'CompId'
3373: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3371: (p_error_message => p_error_message
3372: ,p_attr_name => 'CompId'
3373: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3374: ELSE
3375: hr_utility.set_location(' Leaving:' || l_proc,650);
3376: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3377: (p_error_message => p_error_message
3378: ,p_attr_name => 'CurrStartDate'
3379: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3377: (p_error_message => p_error_message
3378: ,p_attr_name => 'CurrStartDate'
3379: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3380: END IF;
3381: hr_utility.set_location(' Leaving:' || l_proc,655);
3382: ELSIF hr_message.last_message_name = 'HR_52268_CEL_UNIQUE_PERSONAL' THEN
3383: hr_utility.set_location(' Leaving:' || l_proc,660);
3384: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3385: (p_error_message => p_error_message
3379: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3380: END IF;
3381: hr_utility.set_location(' Leaving:' || l_proc,655);
3382: ELSIF hr_message.last_message_name = 'HR_52268_CEL_UNIQUE_PERSONAL' THEN
3383: hr_utility.set_location(' Leaving:' || l_proc,660);
3384: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3385: (p_error_message => p_error_message
3386: ,p_attr_name => 'CompId'
3387: ,p_app_short_name => 'PER'
3386: ,p_attr_name => 'CompId'
3387: ,p_app_short_name => 'PER'
3388: ,p_message_name => 'HR_WEB_CEL_UNIQUE_PERSONAL');
3389: ELSIF hr_message.last_message_name = 'HR_52339_COMP_ELMT_DATE_INVL' THEN
3390: hr_utility.set_location(' Leaving:' || l_proc,665);
3391: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3392: (p_error_message => p_error_message
3393: ,p_attr_name => 'CurrStartDate'
3394: ,p_app_short_name => 'PER'
3393: ,p_attr_name => 'CurrStartDate'
3394: ,p_app_short_name => 'PER'
3395: ,p_message_name => 'HR_52339_COMP_ELMT_DATE_INVL');
3396: ELSIF hr_message.last_message_name = 'PER_52861_CHK_NEXT_CERT_DATE' THEN
3397: hr_utility.set_location(' Leaving:' || l_proc,670);
3398: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3399: (p_error_message => p_error_message
3400: ,p_attr_name => 'NextReviewDate'
3401: ,p_app_short_name => 'PER'
3400: ,p_attr_name => 'NextReviewDate'
3401: ,p_app_short_name => 'PER'
3402: ,p_message_name => 'PER_52861_CHK_NEXT_CERT_DATE');
3403: ELSE
3404: hr_utility.set_location(' Leaving:' || l_proc,675);
3405: p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3406: (p_error_message => p_error_message
3407: ,p_attr_name => null
3408: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3406: (p_error_message => p_error_message
3407: ,p_attr_name => null
3408: ,p_message_name => hr_message.get_message_text ||' '||hr_message.last_message_number);
3409: END IF;
3410: hr_utility.set_location(' Leaving:' || l_proc,680);
3411: End api_validate_competence_record;
3412: --
3413: -- ------------------------------------------------------------------------
3414: -- ---------------------
3456: l_changed boolean default null;
3457: --
3458: --
3459: Begin
3460: hr_utility.set_location(' Entering:' || l_proc,5);
3461: l_changed := false;
3462: --
3463: FOR l_preupd_rec IN csr_get_preupd_cmptnce_rec LOOP
3464: hr_utility.set_location( l_proc || 'LOOP' , 10);
3460: hr_utility.set_location(' Entering:' || l_proc,5);
3461: l_changed := false;
3462: --
3463: FOR l_preupd_rec IN csr_get_preupd_cmptnce_rec LOOP
3464: hr_utility.set_location( l_proc || 'LOOP' , 10);
3465: IF l_preupd_rec.competence_id = p_competence_id THEN
3466: hr_utility.set_location( l_proc , 15);
3467: null;
3468: ELSE
3462: --
3463: FOR l_preupd_rec IN csr_get_preupd_cmptnce_rec LOOP
3464: hr_utility.set_location( l_proc || 'LOOP' , 10);
3465: IF l_preupd_rec.competence_id = p_competence_id THEN
3466: hr_utility.set_location( l_proc , 15);
3467: null;
3468: ELSE
3469: hr_utility.set_location( l_proc , 20);
3470: raise hr_comp_profile_ss.g_fatal_error;
3465: IF l_preupd_rec.competence_id = p_competence_id THEN
3466: hr_utility.set_location( l_proc , 15);
3467: null;
3468: ELSE
3469: hr_utility.set_location( l_proc , 20);
3470: raise hr_comp_profile_ss.g_fatal_error;
3471: END IF;
3472: --
3473: IF nvl(l_preupd_rec.proficiency_level_id,'-99') = nvl(p_proficiency_level_id,'-99') THEN
3470: raise hr_comp_profile_ss.g_fatal_error;
3471: END IF;
3472: --
3473: IF nvl(l_preupd_rec.proficiency_level_id,'-99') = nvl(p_proficiency_level_id,'-99') THEN
3474: hr_utility.set_location( l_proc , 25);
3475: null;
3476: ELSE
3477: hr_utility.set_location( l_proc , 30);
3478: l_changed := true;
3473: IF nvl(l_preupd_rec.proficiency_level_id,'-99') = nvl(p_proficiency_level_id,'-99') THEN
3474: hr_utility.set_location( l_proc , 25);
3475: null;
3476: ELSE
3477: hr_utility.set_location( l_proc , 30);
3478: l_changed := true;
3479: END IF;
3480: --
3481: IF l_preupd_rec.effective_date_from is not null THEN
3478: l_changed := true;
3479: END IF;
3480: --
3481: IF l_preupd_rec.effective_date_from is not null THEN
3482: hr_utility.set_location( l_proc , 35);
3483: IF l_preupd_rec.effective_date_from = p_eff_date_from THEN
3484: hr_utility.set_location( l_proc , 40);
3485: null;
3486: ELSE
3480: --
3481: IF l_preupd_rec.effective_date_from is not null THEN
3482: hr_utility.set_location( l_proc , 35);
3483: IF l_preupd_rec.effective_date_from = p_eff_date_from THEN
3484: hr_utility.set_location( l_proc , 40);
3485: null;
3486: ELSE
3487: hr_utility.set_location( l_proc , 45);
3488: l_changed := true;
3483: IF l_preupd_rec.effective_date_from = p_eff_date_from THEN
3484: hr_utility.set_location( l_proc , 40);
3485: null;
3486: ELSE
3487: hr_utility.set_location( l_proc , 45);
3488: l_changed := true;
3489: END IF;
3490: ELSE -- pre-update is null
3491: hr_utility.set_location( l_proc , 50);
3487: hr_utility.set_location( l_proc , 45);
3488: l_changed := true;
3489: END IF;
3490: ELSE -- pre-update is null
3491: hr_utility.set_location( l_proc , 50);
3492: IF p_eff_date_from is not null THEN
3493: hr_utility.set_location( l_proc , 55);
3494: l_changed := true;
3495: END IF;
3489: END IF;
3490: ELSE -- pre-update is null
3491: hr_utility.set_location( l_proc , 50);
3492: IF p_eff_date_from is not null THEN
3493: hr_utility.set_location( l_proc , 55);
3494: l_changed := true;
3495: END IF;
3496: END IF;
3497: --
3495: END IF;
3496: END IF;
3497: --
3498: IF l_preupd_rec.effective_date_to is not null THEN
3499: hr_utility.set_location( l_proc , 60);
3500: ---------------------------------------------------------------------
3501: -- Only issue a warning if the new eff_date_to is different and is
3502: -- not null.
3503: ---------------------------------------------------------------------
3501: -- Only issue a warning if the new eff_date_to is different and is
3502: -- not null.
3503: ---------------------------------------------------------------------
3504: IF l_preupd_rec.effective_date_to = p_eff_date_to THEN
3505: hr_utility.set_location( l_proc , 65);
3506: null;
3507: ELSE
3508: hr_utility.set_location( l_proc , 70);
3509: l_changed := true;
3504: IF l_preupd_rec.effective_date_to = p_eff_date_to THEN
3505: hr_utility.set_location( l_proc , 65);
3506: null;
3507: ELSE
3508: hr_utility.set_location( l_proc , 70);
3509: l_changed := true;
3510: --
3511: END IF;
3512: ELSE -- pre-update is null
3509: l_changed := true;
3510: --
3511: END IF;
3512: ELSE -- pre-update is null
3513: hr_utility.set_location( l_proc , 75);
3514: IF p_eff_date_to is not null THEN
3515: hr_utility.set_location( l_proc , 80);
3516: l_changed := true;
3517: --
3511: END IF;
3512: ELSE -- pre-update is null
3513: hr_utility.set_location( l_proc , 75);
3514: IF p_eff_date_to is not null THEN
3515: hr_utility.set_location( l_proc , 80);
3516: l_changed := true;
3517: --
3518: END IF;
3519: END IF;
3518: END IF;
3519: END IF;
3520: --
3521: IF l_preupd_rec.comments is not null THEN
3522: hr_utility.set_location( l_proc , 85);
3523: IF l_preupd_rec.comments = p_comments THEN
3524: hr_utility.set_location( l_proc , 90);
3525: null;
3526: ELSE
3520: --
3521: IF l_preupd_rec.comments is not null THEN
3522: hr_utility.set_location( l_proc , 85);
3523: IF l_preupd_rec.comments = p_comments THEN
3524: hr_utility.set_location( l_proc , 90);
3525: null;
3526: ELSE
3527: hr_utility.set_location( l_proc , 95);
3528: l_changed := true;
3523: IF l_preupd_rec.comments = p_comments THEN
3524: hr_utility.set_location( l_proc , 90);
3525: null;
3526: ELSE
3527: hr_utility.set_location( l_proc , 95);
3528: l_changed := true;
3529: END IF;
3530: ELSE -- pre-update is null
3531: hr_utility.set_location( l_proc , 100);
3527: hr_utility.set_location( l_proc , 95);
3528: l_changed := true;
3529: END IF;
3530: ELSE -- pre-update is null
3531: hr_utility.set_location( l_proc , 100);
3532: IF p_comments is not null THEN
3533: hr_utility.set_location( l_proc , 105);
3534: l_changed := true;
3535: END IF;
3529: END IF;
3530: ELSE -- pre-update is null
3531: hr_utility.set_location( l_proc , 100);
3532: IF p_comments is not null THEN
3533: hr_utility.set_location( l_proc , 105);
3534: l_changed := true;
3535: END IF;
3536: END IF;
3537: --
3535: END IF;
3536: END IF;
3537: --
3538: IF l_preupd_rec.certification_date is not null THEN
3539: hr_utility.set_location( l_proc , 110);
3540: IF l_preupd_rec.certification_date = p_certification_date THEN
3541: hr_utility.set_location( l_proc , 115);
3542: null;
3543: ELSE
3537: --
3538: IF l_preupd_rec.certification_date is not null THEN
3539: hr_utility.set_location( l_proc , 110);
3540: IF l_preupd_rec.certification_date = p_certification_date THEN
3541: hr_utility.set_location( l_proc , 115);
3542: null;
3543: ELSE
3544: hr_utility.set_location( l_proc , 120);
3545: l_changed := true;
3540: IF l_preupd_rec.certification_date = p_certification_date THEN
3541: hr_utility.set_location( l_proc , 115);
3542: null;
3543: ELSE
3544: hr_utility.set_location( l_proc , 120);
3545: l_changed := true;
3546: END IF;
3547: ELSE -- pre-update is null
3548: hr_utility.set_location( l_proc , 125);
3544: hr_utility.set_location( l_proc , 120);
3545: l_changed := true;
3546: END IF;
3547: ELSE -- pre-update is null
3548: hr_utility.set_location( l_proc , 125);
3549: IF p_certification_date is not null THEN
3550: hr_utility.set_location( l_proc , 130);
3551: l_changed := true;
3552: END IF;
3546: END IF;
3547: ELSE -- pre-update is null
3548: hr_utility.set_location( l_proc , 125);
3549: IF p_certification_date is not null THEN
3550: hr_utility.set_location( l_proc , 130);
3551: l_changed := true;
3552: END IF;
3553: END IF;
3554: --
3552: END IF;
3553: END IF;
3554: --
3555: IF l_preupd_rec.certification_method is not null THEN
3556: hr_utility.set_location( l_proc , 135);
3557: IF l_preupd_rec.certification_method = p_certification_mthd THEN
3558: hr_utility.set_location( l_proc , 140);
3559: null;
3560: ELSE
3554: --
3555: IF l_preupd_rec.certification_method is not null THEN
3556: hr_utility.set_location( l_proc , 135);
3557: IF l_preupd_rec.certification_method = p_certification_mthd THEN
3558: hr_utility.set_location( l_proc , 140);
3559: null;
3560: ELSE
3561: hr_utility.set_location( l_proc , 145);
3562: l_changed := true;
3557: IF l_preupd_rec.certification_method = p_certification_mthd THEN
3558: hr_utility.set_location( l_proc , 140);
3559: null;
3560: ELSE
3561: hr_utility.set_location( l_proc , 145);
3562: l_changed := true;
3563: END IF;
3564: ELSE -- pre-update is null
3565: hr_utility.set_location( l_proc , 150);
3561: hr_utility.set_location( l_proc , 145);
3562: l_changed := true;
3563: END IF;
3564: ELSE -- pre-update is null
3565: hr_utility.set_location( l_proc , 150);
3566: IF p_certification_mthd is not null THEN
3567: hr_utility.set_location( l_proc , 155);
3568: l_changed := true;
3569: END IF;
3563: END IF;
3564: ELSE -- pre-update is null
3565: hr_utility.set_location( l_proc , 150);
3566: IF p_certification_mthd is not null THEN
3567: hr_utility.set_location( l_proc , 155);
3568: l_changed := true;
3569: END IF;
3570: END IF;
3571: --
3568: l_changed := true;
3569: END IF;
3570: END IF;
3571: --
3572: hr_utility.set_location( l_proc , 160);
3573: IF l_preupd_rec.next_certification_date is not null THEN
3574: hr_utility.set_location( l_proc , 165);
3575: IF l_preupd_rec.next_certification_date = p_next_certifctn_date THEN
3576: hr_utility.set_location( l_proc , 170);
3570: END IF;
3571: --
3572: hr_utility.set_location( l_proc , 160);
3573: IF l_preupd_rec.next_certification_date is not null THEN
3574: hr_utility.set_location( l_proc , 165);
3575: IF l_preupd_rec.next_certification_date = p_next_certifctn_date THEN
3576: hr_utility.set_location( l_proc , 170);
3577: null;
3578: ELSE
3572: hr_utility.set_location( l_proc , 160);
3573: IF l_preupd_rec.next_certification_date is not null THEN
3574: hr_utility.set_location( l_proc , 165);
3575: IF l_preupd_rec.next_certification_date = p_next_certifctn_date THEN
3576: hr_utility.set_location( l_proc , 170);
3577: null;
3578: ELSE
3579: hr_utility.set_location( l_proc , 175);
3580: l_changed := true;
3575: IF l_preupd_rec.next_certification_date = p_next_certifctn_date THEN
3576: hr_utility.set_location( l_proc , 170);
3577: null;
3578: ELSE
3579: hr_utility.set_location( l_proc , 175);
3580: l_changed := true;
3581: END IF;
3582: ELSE -- pre-update is null
3583: hr_utility.set_location( l_proc , 180);
3579: hr_utility.set_location( l_proc , 175);
3580: l_changed := true;
3581: END IF;
3582: ELSE -- pre-update is null
3583: hr_utility.set_location( l_proc , 180);
3584: IF p_next_certifctn_date is not null THEN
3585: hr_utility.set_location( l_proc , 185);
3586: l_changed := true;
3587: END IF;
3581: END IF;
3582: ELSE -- pre-update is null
3583: hr_utility.set_location( l_proc , 180);
3584: IF p_next_certifctn_date is not null THEN
3585: hr_utility.set_location( l_proc , 185);
3586: l_changed := true;
3587: END IF;
3588: END IF;
3589: --
3586: l_changed := true;
3587: END IF;
3588: END IF;
3589: --
3590: hr_utility.set_location( l_proc , 190);
3591: IF l_preupd_rec.source_of_proficiency_level is not null THEN
3592: hr_utility.set_location( l_proc , 195);
3593: IF l_preupd_rec.source_of_proficiency_level = p_proficy_lvl_source THEN
3594: hr_utility.set_location( l_proc , 200);
3588: END IF;
3589: --
3590: hr_utility.set_location( l_proc , 190);
3591: IF l_preupd_rec.source_of_proficiency_level is not null THEN
3592: hr_utility.set_location( l_proc , 195);
3593: IF l_preupd_rec.source_of_proficiency_level = p_proficy_lvl_source THEN
3594: hr_utility.set_location( l_proc , 200);
3595: null;
3596: ELSE
3590: hr_utility.set_location( l_proc , 190);
3591: IF l_preupd_rec.source_of_proficiency_level is not null THEN
3592: hr_utility.set_location( l_proc , 195);
3593: IF l_preupd_rec.source_of_proficiency_level = p_proficy_lvl_source THEN
3594: hr_utility.set_location( l_proc , 200);
3595: null;
3596: ELSE
3597: hr_utility.set_location( l_proc , 205);
3598: l_changed := true;
3593: IF l_preupd_rec.source_of_proficiency_level = p_proficy_lvl_source THEN
3594: hr_utility.set_location( l_proc , 200);
3595: null;
3596: ELSE
3597: hr_utility.set_location( l_proc , 205);
3598: l_changed := true;
3599: END IF;
3600: ELSE -- pre-update is null
3601: hr_utility.set_location( l_proc , 210);
3597: hr_utility.set_location( l_proc , 205);
3598: l_changed := true;
3599: END IF;
3600: ELSE -- pre-update is null
3601: hr_utility.set_location( l_proc , 210);
3602: IF p_proficy_lvl_source is not null THEN
3603: hr_utility.set_location( l_proc , 215);
3604: l_changed := true;
3605: END IF;
3599: END IF;
3600: ELSE -- pre-update is null
3601: hr_utility.set_location( l_proc , 210);
3602: IF p_proficy_lvl_source is not null THEN
3603: hr_utility.set_location( l_proc , 215);
3604: l_changed := true;
3605: END IF;
3606: END IF;
3607: /* Start Competence Qualification Link enhancement */
3606: END IF;
3607: /* Start Competence Qualification Link enhancement */
3608: /* commented for bug no 4188501 */
3609: /* IF l_preupd_rec.status IS NOT NULL then
3610: hr_utility.set_location( l_proc , 216);
3611: IF l_preupd_rec.status = p_competence_status THEN
3612: hr_utility.set_location( l_proc , 217);
3613: null;
3614: ELSE
3608: /* commented for bug no 4188501 */
3609: /* IF l_preupd_rec.status IS NOT NULL then
3610: hr_utility.set_location( l_proc , 216);
3611: IF l_preupd_rec.status = p_competence_status THEN
3612: hr_utility.set_location( l_proc , 217);
3613: null;
3614: ELSE
3615: hr_utility.set_location( l_proc , 218);
3616: l_changed := true;
3611: IF l_preupd_rec.status = p_competence_status THEN
3612: hr_utility.set_location( l_proc , 217);
3613: null;
3614: ELSE
3615: hr_utility.set_location( l_proc , 218);
3616: l_changed := true;
3617: END if;
3618: ELSE
3619: hr_utility.set_location( l_proc , 219);
3615: hr_utility.set_location( l_proc , 218);
3616: l_changed := true;
3617: END if;
3618: ELSE
3619: hr_utility.set_location( l_proc , 219);
3620: IF p_competence_status is not null THEN
3621: hr_utility.set_location( l_proc , 220);
3622: l_changed := true;
3623: END IF;
3617: END if;
3618: ELSE
3619: hr_utility.set_location( l_proc , 219);
3620: IF p_competence_status is not null THEN
3621: hr_utility.set_location( l_proc , 220);
3622: l_changed := true;
3623: END IF;
3624: END if; */
3625: /* End Competence Qualification Link enhancement */
3627: --
3628: p_rec_changed := l_changed;
3629: -- p_warning_exists := l_warning_exists;
3630: --
3631: hr_utility.set_location(' Leaving:' || l_proc,220);
3632: Exception
3633: When others then
3634: hr_utility.set_location(' Leaving:' || l_proc,555);
3635: raise;
3630: --
3631: hr_utility.set_location(' Leaving:' || l_proc,220);
3632: Exception
3633: When others then
3634: hr_utility.set_location(' Leaving:' || l_proc,555);
3635: raise;
3636: --
3637: End check_if_cmptnce_rec_changed;
3638: --
3649: ,p_ceritification_date out nocopy varchar2
3650: ,p_next_review_date out nocopy varchar2) is
3651: l_proc varchar2(200) := g_package || 'get_correction_trans_values';
3652: Begin
3653: hr_utility.set_location(' Entering:' || l_proc,5);
3654: null;
3655: hr_utility.set_location(' Leaving:' || l_proc,10);
3656: End;
3657: --
3651: l_proc varchar2(200) := g_package || 'get_correction_trans_values';
3652: Begin
3653: hr_utility.set_location(' Entering:' || l_proc,5);
3654: null;
3655: hr_utility.set_location(' Leaving:' || l_proc,10);
3656: End;
3657: --
3658: -- ***** Start new code for bug 2719381 **************
3659: /*==============================================================
3730: And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
3731: And proficiency_level_id = rl1.rating_level_id (+)
3732: And high_proficiency_level_id = rl2.rating_level_id (+);
3733: Begin
3734: hr_utility.set_location(' Entering:' || l_proc,5);
3735: -- If the input parameters are null, return null
3736: If ( p_person_id is null OR p_competence_id is null ) Then
3737: hr_utility.set_location(' Leaving:' || l_proc,10);
3738: return null;
3733: Begin
3734: hr_utility.set_location(' Entering:' || l_proc,5);
3735: -- If the input parameters are null, return null
3736: If ( p_person_id is null OR p_competence_id is null ) Then
3737: hr_utility.set_location(' Leaving:' || l_proc,10);
3738: return null;
3739: End If;
3740: For I in csr_pos_profrange Loop
3741: hr_utility.set_location(l_proc || 'LOOP' ,15);
3737: hr_utility.set_location(' Leaving:' || l_proc,10);
3738: return null;
3739: End If;
3740: For I in csr_pos_profrange Loop
3741: hr_utility.set_location(l_proc || 'LOOP' ,15);
3742: if (I.minprof is not null Or I.maxprof is not null) then
3743: hr_utility.set_location(' Leaving:' || l_proc,20);
3744: return I.minprof ||' -- ' || I.maxprof;
3745: end if;
3739: End If;
3740: For I in csr_pos_profrange Loop
3741: hr_utility.set_location(l_proc || 'LOOP' ,15);
3742: if (I.minprof is not null Or I.maxprof is not null) then
3743: hr_utility.set_location(' Leaving:' || l_proc,20);
3744: return I.minprof ||' -- ' || I.maxprof;
3745: end if;
3746: End Loop;
3747: hr_utility.set_location( l_proc,25);
3743: hr_utility.set_location(' Leaving:' || l_proc,20);
3744: return I.minprof ||' -- ' || I.maxprof;
3745: end if;
3746: End Loop;
3747: hr_utility.set_location( l_proc,25);
3748: For I in csr_job_profrange Loop
3749: hr_utility.set_location(l_proc || 'LOOP' ,30);
3750: if (I.minprof is not null Or I.maxprof is not null) then
3751: hr_utility.set_location(' Leaving:' || l_proc,35);
3745: end if;
3746: End Loop;
3747: hr_utility.set_location( l_proc,25);
3748: For I in csr_job_profrange Loop
3749: hr_utility.set_location(l_proc || 'LOOP' ,30);
3750: if (I.minprof is not null Or I.maxprof is not null) then
3751: hr_utility.set_location(' Leaving:' || l_proc,35);
3752: return I.minprof ||' -- ' || I.maxprof;
3753: end if;
3747: hr_utility.set_location( l_proc,25);
3748: For I in csr_job_profrange Loop
3749: hr_utility.set_location(l_proc || 'LOOP' ,30);
3750: if (I.minprof is not null Or I.maxprof is not null) then
3751: hr_utility.set_location(' Leaving:' || l_proc,35);
3752: return I.minprof ||' -- ' || I.maxprof;
3753: end if;
3754: End Loop;
3755: hr_utility.set_location( l_proc,40);
3751: hr_utility.set_location(' Leaving:' || l_proc,35);
3752: return I.minprof ||' -- ' || I.maxprof;
3753: end if;
3754: End Loop;
3755: hr_utility.set_location( l_proc,40);
3756: For I in csr_org_profrange Loop
3757: hr_utility.set_location(l_proc || 'LOOP' ,45);
3758: if (I.minprof is not null Or I.maxprof is not null) then
3759: hr_utility.set_location(' Leaving:' || l_proc,50);
3753: end if;
3754: End Loop;
3755: hr_utility.set_location( l_proc,40);
3756: For I in csr_org_profrange Loop
3757: hr_utility.set_location(l_proc || 'LOOP' ,45);
3758: if (I.minprof is not null Or I.maxprof is not null) then
3759: hr_utility.set_location(' Leaving:' || l_proc,50);
3760: return I.minprof ||' -- ' || I.maxprof;
3761: end if;
3755: hr_utility.set_location( l_proc,40);
3756: For I in csr_org_profrange Loop
3757: hr_utility.set_location(l_proc || 'LOOP' ,45);
3758: if (I.minprof is not null Or I.maxprof is not null) then
3759: hr_utility.set_location(' Leaving:' || l_proc,50);
3760: return I.minprof ||' -- ' || I.maxprof;
3761: end if;
3762: End Loop;
3763: hr_utility.set_location( l_proc,55);
3759: hr_utility.set_location(' Leaving:' || l_proc,50);
3760: return I.minprof ||' -- ' || I.maxprof;
3761: end if;
3762: End Loop;
3763: hr_utility.set_location( l_proc,55);
3764: For I in csr_bg_profrange Loop
3765: hr_utility.set_location(l_proc || 'LOOP' ,60);
3766: if (I.minprof is not null Or I.maxprof is not null) then
3767: hr_utility.set_location(' Leaving:' || l_proc,65);
3761: end if;
3762: End Loop;
3763: hr_utility.set_location( l_proc,55);
3764: For I in csr_bg_profrange Loop
3765: hr_utility.set_location(l_proc || 'LOOP' ,60);
3766: if (I.minprof is not null Or I.maxprof is not null) then
3767: hr_utility.set_location(' Leaving:' || l_proc,65);
3768: return I.minprof ||' -- ' || I.maxprof;
3769: end if;
3763: hr_utility.set_location( l_proc,55);
3764: For I in csr_bg_profrange Loop
3765: hr_utility.set_location(l_proc || 'LOOP' ,60);
3766: if (I.minprof is not null Or I.maxprof is not null) then
3767: hr_utility.set_location(' Leaving:' || l_proc,65);
3768: return I.minprof ||' -- ' || I.maxprof;
3769: end if;
3770: End Loop;
3771: hr_utility.set_location(' Leaving:' || l_proc,70);
3767: hr_utility.set_location(' Leaving:' || l_proc,65);
3768: return I.minprof ||' -- ' || I.maxprof;
3769: end if;
3770: End Loop;
3771: hr_utility.set_location(' Leaving:' || l_proc,70);
3772: return null;
3773: Exception
3774: when others then
3775: hr_utility.set_location(' Leaving:' || l_proc,555);
3771: hr_utility.set_location(' Leaving:' || l_proc,70);
3772: return null;
3773: Exception
3774: when others then
3775: hr_utility.set_location(' Leaving:' || l_proc,555);
3776: return null;
3777: End;
3778: -- ***** End new code for bug 2719381 **************
3779: --
3817: Or pce.enterprise_id = paaf.business_group_id);
3818: l_rec prof_rec%RowType;
3819: l_return varchar2(10) default null;
3820: Begin
3821: hr_utility.set_location(' Entering:' || l_proc,5);
3822: -- If the input parameters are null, return null
3823: If ( p_person_id is null OR p_competence_id is null ) Then
3824: hr_utility.set_location(' Leaving:' || l_proc,10);
3825: Return l_return;
3820: Begin
3821: hr_utility.set_location(' Entering:' || l_proc,5);
3822: -- If the input parameters are null, return null
3823: If ( p_person_id is null OR p_competence_id is null ) Then
3824: hr_utility.set_location(' Leaving:' || l_proc,10);
3825: Return l_return;
3826: End If;
3827: Open prof_rec;
3828: Fetch prof_rec Into l_rec;
3831: Else
3832: l_return := 'Yes';
3833: End If;
3834: Close prof_rec;
3835: hr_utility.set_location(' Leaving:' || l_proc,15);
3836: Return l_return;
3837: Exception
3838: When OTHERS Then
3839: hr_utility.set_location(' Leaving:' || l_proc,555);
3835: hr_utility.set_location(' Leaving:' || l_proc,15);
3836: Return l_return;
3837: Exception
3838: When OTHERS Then
3839: hr_utility.set_location(' Leaving:' || l_proc,555);
3840: l_return := 'Yes';
3841: If prof_rec%ISOPEN Then
3842: hr_utility.set_location(' Leaving:' || l_proc,560);
3843: Close prof_rec;
3838: When OTHERS Then
3839: hr_utility.set_location(' Leaving:' || l_proc,555);
3840: l_return := 'Yes';
3841: If prof_rec%ISOPEN Then
3842: hr_utility.set_location(' Leaving:' || l_proc,560);
3843: Close prof_rec;
3844: End If;
3845: Return l_return;
3846: End;