[Home] [Help]
PACKAGE BODY: APPS.HR_COMP_PROFILE_SS
Source
1 PACKAGE BODY HR_COMP_PROFILE_SS AS
2 /* $Header: hrcprwrs.pkb 120.1.12000000.2 2007/02/19 14:25:36 kgowripe ship $ */
3 --
4 -- Private globals
5 g_package varchar2(30) := 'HR_COMP_PROFILE_SS';
6 g_person_id per_all_people_f.person_id%type;
7 g_business_group_id per_all_people_f.business_group_id%type;
8 g_language_code varchar2(5) default null;
9 g_invalid_entry exception;
10 g_invalid_competence exception;
11 --
12 cursor g_csr_get_preupd_cmptnce_rec(p_competence_element_id in number) is
13 select cvl.name
14 ,cmpe.competence_element_id
15 ,cmpe.object_version_number
16 ,cmpe.competence_id
17 ,cmpe.proficiency_level_id
18 ,ratl.step_value
19 ,cmpe.effective_date_from
20 ,cmpe.effective_date_to
21 ,cmpe.certification_date
22 ,cmpe.certification_method
23 ,hr_general.decode_lookup ('CERTIFICATION_METHOD' ,cmpe.certification_method) certification_method_desc
24 ,cmpe.next_certification_date
25 ,cmpe.source_of_proficiency_level
26 ,hr_general.decode_lookup ('PROFICIENCY_SOURCE' ,cmpe.source_of_proficiency_level) src_proficiency_lvl_desc
27 ,cmpe.comments
28 ,cvl.competence_alias
29 ,cmpe.status -- added for Competnce Qualification link enahncement
30 from per_rating_levels ratl
31 ,per_competence_elements cmpe
32 ,per_competences_vl cvl
33 where cmpe.competence_element_id = p_competence_element_id
34 and cmpe.competence_id = cvl.competence_id
35 and cmpe.proficiency_level_id = ratl.rating_level_id (+)
36 and trunc(sysdate) between nvl(cmpe.effective_date_from, trunc(sysdate))
37 and nvl(cmpe.effective_date_to, trunc(sysdate));
38 cursor get_curr_step_value(p_proficiency_level_id in number) is
39 select step_value from per_rating_levels
40 where rating_level_id = p_proficiency_level_id;
41 cursor get_previous_step_value(p_competence_element_id in number) is
42 select a.step_value
43 from per_rating_levels a,per_competence_elements b
44 where b.proficiency_level_id = a.rating_level_id(+)
45 and b.competence_element_id = p_competence_element_id;
46 cursor get_prev_prof_lvl_id(p_competence_element_id in number) is
47 select proficiency_level_id
48 from per_competence_elements
49 where competence_element_id = p_competence_element_id;
50 cursor get_comp_name(p_competence_id in number) is
51 select name
52 from per_competences_vl
53 where competence_id = p_competence_id;
54 cursor get_prev_start_date(p_competence_element_id in number) is
55 select effective_date_from
56 from per_competence_elements
57 where competence_element_id = p_competence_element_id;
58 cursor get_comp_alias(p_competence_id in number) is
59 select competence_alias
60 from per_competences_vl
61 where competence_id = p_competence_id;
62 cursor get_mode(p_transaction_step_id in number) is
63 select varchar2_value
64 from hr_api_transaction_values
65 where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
66 CURSOR get_prev_status(p_competence_element_id IN number) is
67 SELECT status
68 FROM per_competence_elements
69 WHERE competence_element_id = p_competence_element_id;
70 --
71 -- ------------------------------------------------------------------------
72 -- ---------------------<api_validate_competence_record>-------------------
73 -- ------------------------------------------------------------------------
74 -- Purpose: This private signature will validate data entered by calling api's
75 --
76 Procedure api_validate_competence_record
77 (p_validate in boolean default null
78 ,p_person_id in number
79 ,p_business_group_id in number default null
80 ,p_change_mode in varchar2 default null
81 ,p_competence_element_id in number default null
82 ,p_preupd_obj_vers_num in number default null
83 ,p_competence_id in number default null
84 ,p_proficiency_level_id in number default null
85 ,p_eff_date_from in varchar2 default null
86 ,p_eff_date_to in varchar2 default null
87 ,p_proficy_lvl_source in varchar2 default null
88 ,p_certification_mthd in varchar2 default null
89 ,p_certification_date in varchar2 default null
90 ,p_next_certifctn_date in varchar2 default null
91 ,p_competence_status IN VARCHAR2 DEFAULT NULL -- Competence Qualification Link Enh.
92 ,p_eff_date_from_date_type out nocopy date
93 ,p_eff_date_to_date_type out nocopy date
94 ,p_certifctn_date_type out nocopy date
95 ,p_next_certifctn_date_type out nocopy date
96 ,p_error_message out nocopy long);
97 --
98 -- ------------------------------------------------------------------------
99 -- ---------------------<check_if_cmptnce_rec_changed>---------------------
100 -- ------------------------------------------------------------------------
101 -- Purpose: This private signature will compare the values of the rec with the
102 -- values before update.
103 -- The caller has made sure that this procedure is called only on an
104 -- update or upgrade to new proficiency level mode.
105 -- IF the proficiency level is the same as pre-update value, it
106 -- will set an output parm to true if the p_change_mode is
107 -- upgrade a proficiency level.
108 -- It will also check for any outcome changes are there.
109 -- ------------------------------------------------------------------------
110 Procedure check_if_cmptnce_rec_changed
111 (p_competence_element_id in number
112 ,p_competence_id in number
113 ,p_proficiency_level_id in number default null
114 ,p_eff_date_from in date default null
115 ,p_eff_date_to in date default null
116 ,p_proficy_lvl_source in varchar2 default null
117 ,p_certification_mthd in varchar2 default null
118 ,p_certification_date in date default null
119 ,p_next_certifctn_date in date default null
120 ,p_change_mode in varchar2
121 ,p_ignore_warning in varchar2 default null
122 ,p_comments in varchar2 default null
123 ,p_competence_status in varchar2 default NULL -- Competence Qualification link
124 ,p_rec_changed out nocopy boolean);
125 -- ---------------------------------------------------------------------------
126 -- ---------------------------- < writeTo_transTbl > -----------------------------
127 -- ---------------------------------------------------------------------------
128 -- Purpose: This private procedure to write into trans table
129 -- the transaction step is created if one doesn't exits
130 -- ---------------------------------------------------------------------------
131 Procedure writeTo_transTbl(p_item_type IN wf_items.item_type%type
132 ,p_item_key IN wf_items.item_key%type
133 ,p_actid IN Number
134 ,p_login_person_id IN Number
135 ,p_trans_id IN Number
136 ,p_trans_step_id IN OUT NOCOPY Number
137 ,p_api_name IN Varchar2
138 ,p_person_id IN Number Default Null
139 ,p_business_group_id IN Number Default Null
140 ,p_comp_element_id IN Number Default Null
141 ,p_ovn IN Number Default Null
142 ,p_eff_date_from IN Varchar2 Default Null
143 ,p_eff_date_to IN Varchar2 Default Null
144 ,p_comp_id IN Number Default Null
145 ,p_comp_name IN Varchar2 Default Null
146 ,p_comp_alias IN Varchar2 Default Null
147 ,p_profy_level_id IN Number Default Null
148 ,p_profy_level_source IN Varchar2 Default Null
149 ,p_step_value IN Number Default Null
150 ,p_comments IN Varchar2 Default Null
151 ,p_cert_date IN Varchar2 Default Null
152 ,p_cert_method IN Varchar2 Default Null
153 ,p_next_cert_date IN Varchar2 Default Null
154 ,p_prev_start_date IN Varchar2 Default Null
155 ,p_prev_step_value IN Number Default Null
156 ,p_prev_profy_level_id IN Number Default Null
157 ,p_change_mode IN Varchar2 Default Null
158 ,p_status IN VARCHAR2 DEFAULT NULL -- added for competence Qualification
159 ,p_prev_status IN VARCHAR2 DEFAULT NULL -- added for competence Qualification
160 ,p_sys_generated IN Varchar2 Default 'N'
161 ,p_upg_from_rec_id IN Number Default -1) is
162 l_proc varchar2(200) := g_package || 'writeTo_transTbl';
163 l_trans_tbl transaction_table1;
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
173 ,p_creator_person_id => p_login_person_id
174 ,p_transaction_id => p_trans_id
175 ,p_api_name => p_api_name
176 ,p_item_type => p_item_type
177 ,p_item_key => p_item_key
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';
187 l_count := l_count + 1;
188 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
189 l_trans_tbl(l_count).param_value := p_business_group_id;
190 l_trans_tbl(l_count).param_data_type := 'NUMBER';
191 l_count := l_count + 1;
192 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ELEMENT_ID';
193 l_trans_tbl(l_count).param_value := p_comp_element_id;
194 l_trans_tbl(l_count).param_data_type := 'NUMBER';
195 l_count := l_count + 1;
196 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
197 l_trans_tbl(l_count).param_value := p_ovn;
198 l_trans_tbl(l_count).param_data_type := 'NUMBER';
199 l_count := l_count + 1;
200 l_trans_tbl(l_count).param_name := 'P_EFF_DATE_FROM';
201 l_trans_tbl(l_count).param_value := p_eff_date_from;
202 l_trans_tbl(l_count).param_data_type := 'DATE';
203 l_count := l_count + 1;
204 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ID';
205 l_trans_tbl(l_count).param_value := p_comp_id;
206 l_trans_tbl(l_count).param_data_type := 'NUMBER';
207 l_count := l_count + 1;
208 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_NAME';
209 l_trans_tbl(l_count).param_value := p_comp_name;
210 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
211 l_count := l_count + 1;
212 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ALIAS';
213 l_trans_tbl(l_count).param_value := p_comp_alias;
214 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
215 l_count := l_count + 1;
216 l_trans_tbl(l_count).param_name := 'P_PROFICIENCY_LEVEL_ID';
217 l_trans_tbl(l_count).param_value := p_profy_level_id;
218 l_trans_tbl(l_count).param_data_type := 'NUMBER';
219 l_count := l_count + 1;
220 l_trans_tbl(l_count).param_name := 'P_PROFICY_LVL_SOURCE';
221 l_trans_tbl(l_count).param_value := p_profy_level_source;
222 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
223 l_count := l_count + 1;
224 l_trans_tbl(l_count).param_name := 'P_STEP_VALUE';
225 l_trans_tbl(l_count).param_value := p_step_value;
226 l_trans_tbl(l_count).param_data_type := 'NUMBER';
227 l_count := l_count + 1;
228 l_trans_tbl(l_count).param_name := 'P_EFF_DATE_TO';
229 l_trans_tbl(l_count).param_value := p_eff_date_to;
230 l_trans_tbl(l_count).param_data_type := 'DATE';
231 l_count := l_count + 1;
232 l_trans_tbl(l_count).param_name := 'P_COMMENTS';
233 l_trans_tbl(l_count).param_value := p_comments;
234 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
235 l_count := l_count + 1;
236 l_trans_tbl(l_count).param_name := 'P_CERTIFICATION_DATE';
237 l_trans_tbl(l_count).param_value := p_cert_date;
238 l_trans_tbl(l_count).param_data_type := 'DATE';
239 l_count := l_count + 1;
240 l_trans_tbl(l_count).param_name := 'P_CERTIFICATION_MTHD';
241 l_trans_tbl(l_count).param_value := p_cert_method;
242 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
243 l_count := l_count + 1;
244 l_trans_tbl(l_count).param_name := 'P_NEXT_CERTIFCTN_DATE';
245 l_trans_tbl(l_count).param_value := p_next_cert_date;
246 l_trans_tbl(l_count).param_data_type := 'DATE';
247 l_count := l_count + 1;
248 l_trans_tbl(l_count).param_name := 'P_PREV_START_DATE';
249 l_trans_tbl(l_count).param_value := p_prev_start_date;
250 l_trans_tbl(l_count).param_data_type := 'DATE';
251 l_count := l_count + 1;
252 l_trans_tbl(l_count).param_name := 'P_PREV_STEP_VALUE';
253 l_trans_tbl(l_count).param_value := p_prev_step_value;
254 l_trans_tbl(l_count).param_data_type := 'NUMBER';
255 l_count := l_count + 1;
256 l_trans_tbl(l_count).param_name := 'P_PREUPD_PROFICY_LVL_ID';
257 l_trans_tbl(l_count).param_value := p_prev_profy_level_id;
258 l_trans_tbl(l_count).param_data_type := 'NUMBER';
259 l_count := l_count + 1;
260 l_trans_tbl(l_count).param_name := 'P_CHANGE_MODE';
261 l_trans_tbl(l_count).param_value := p_change_mode;
262 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
263 l_count := l_count + 1;
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';
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';
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';
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';
290 /* end code for competence Qualification link enhancement */
291 l_review_region := wf_engine.GetActivityAttrText
292 (itemtype => p_item_type
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;
302 save_transaction_step(p_item_type => p_item_type
303 ,p_item_key => p_item_key
304 ,p_actid => p_actid
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 > --------------------------
314 -- ---------------------------------------------------------------------------
315 -- Purpose: This function is being used for filtering current session changes
316 -- and pending approval changes
317 -- ---------------------------------------------------------------------------
318 Function comp_not_exists
319 (p_item_type in varchar2
320 ,p_item_key in varchar2
321 ,p_person_id in number
322 ,p_competence_id in number
323 ) Return varchar2 is
324 l_proc varchar2(200) := g_package || 'comp_not_exists';
325 l_retStatus varchar2(1) := 'T';
326 cursor c1 is
327 select 'F' status
328 from hr_api_transaction_steps s, hr_api_transaction_values a,
329 hr_api_transaction_values b
330 Where s.item_type = p_item_type
331 and s.item_key = p_item_key
332 and s.transaction_step_id = a.transaction_step_id
333 and s.transaction_step_id = b.transaction_step_id
334 and a.name = 'P_COMPETENCE_ID' and a.number_value = p_competence_id
335 and b.name = 'P_PERSON_ID' and b.number_value = p_person_id
336 union
337 select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
338 where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
339 and ts.transaction_id = t.transaction_id
340 and t.selected_person_id = p_person_id and t.status = 'Y'
341 and exists (Select 'e' From hr_api_transaction_values c
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;
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';
360 end comp_not_exists;
361 -- ***** End new code for bug 2743410 **************
362 -- ---------------------------------------------------------------------------
363 -- ---------------------------- < process_save > -----------------------------
364 -- ---------------------------------------------------------------------------
365 -- Purpose: This private procedure saves the competence profile record either to the
366 -- database or to the transaction table depending on the workflow
367 -- setting.
368 -- ---------------------------------------------------------------------------
369 Procedure process_save
370 (p_item_type in wf_items.item_type%type
371 ,p_item_key in wf_items.item_key%type
372 ,p_actid in varchar2
373 ,p_person_id in number
374 ,p_change_mode in varchar2 default null
375 ,p_competence_element_id in number default null
376 ,p_preupd_obj_vers_num in number default null
377 ,p_competence_id in number default null
378 ,p_competence_name in varchar2 default null
379 ,p_competence_alias in varchar2 default null
380 ,p_proficiency_level_id in number default null
381 ,p_step_value in number default null
382 ,p_preupd_proficy_lvl_id in number default null
383 ,p_certification_mthd in varchar2 default null
384 ,p_proficy_lvl_source in varchar2 default null
385 ,p_eff_date_from in varchar2 default null
386 ,p_eff_date_to in varchar2 default null
387 ,p_certification_date in varchar2 default null
388 ,p_next_certifctn_date in varchar2 default null
389 ,p_comments in varchar2 default null
390 ,p_prev_step_value in number default null
391 ,p_prev_start_date in varchar2 default null
392 ,p_ignore_warning in varchar2 default null
393 ,p_competence_status IN VARCHAR2 DEFAULT NULL --Competence Qualification link
394 ,p_transaction_step_id in out nocopy number
395 ,p_error_message out nocopy long) is
396 --
397 l_proc varchar2(200) := g_package || 'process_save';
398 l_transaction_id number default null;
399 l_transaction_step_id number default null;
400 l_result varchar2(100) default null;
401 l_user_date_format varchar2(200) default null;
402 x_eff_date_from date default null;
403 x_eff_date_to date default null;
404 x_certification_date date default null;
405 x_next_certifctn_date date default null;
406 l_preupd_cmptnce_row g_csr_get_preupd_cmptnce_rec%rowtype;
407 -- when upgraded record updated from current session changes
408 l_eff_date_from date default null;
409 l_competence_element_id number default null;
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);
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;
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)
434 and competence_id = p_competence_id group by competence_id;
435 select competence_element_id, object_version_number
436 into l_competence_element_id, l_obj_ver_num
437 from per_competence_elements
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
447 ,p_change_mode => l_mode_value
448 ,p_competence_element_id => l_competence_element_id
449 ,p_preupd_obj_vers_num => l_obj_ver_num
450 ,p_competence_id => p_competence_id
451 ,p_proficiency_level_id => p_proficiency_level_id
452 ,p_eff_date_from => p_eff_date_from
453 ,p_eff_date_to => p_eff_date_to
454 ,p_proficy_lvl_source => p_proficy_lvl_source
455 ,p_certification_mthd => p_certification_mthd
456 ,p_certification_date => p_certification_date
457 ,p_next_certifctn_date => p_next_certifctn_date
458 ,p_competence_status => p_competence_status
459 ,p_eff_date_from_date_type => x_eff_date_from
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;
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
478 ,actid => to_number(p_actid)
479 ,funmode => 'RUN'
480 ,p_login_person_id => fnd_global.employee_id
481 ,result => l_result);
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;
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)
500 ,p_login_person_id => fnd_global.employee_id
501 ,p_trans_id => l_transaction_id
502 ,p_trans_step_id => p_transaction_step_id
503 ,p_api_name => g_api_name
504 ,p_person_id => p_person_id
505 ,p_business_group_id => g_business_group_id
506 ,p_comp_element_id => p_competence_element_id
507 ,p_ovn => l_preupd_cmptnce_row.object_version_number
508 ,p_eff_date_from => to_char(l_preupd_cmptnce_row.effective_date_from,l_user_date_format)
509 ,p_eff_date_to => to_char(x_eff_date_from-1,l_user_date_format)
510 ,p_comp_id => l_preupd_cmptnce_row.competence_id
511 ,p_comp_name => l_preupd_cmptnce_row.name
512 ,p_comp_alias => l_preupd_cmptnce_row.competence_alias
513 ,p_profy_level_id => l_preupd_cmptnce_row.proficiency_level_id
514 ,p_profy_level_source => l_preupd_cmptnce_row.source_of_proficiency_level
515 ,p_step_value => l_preupd_cmptnce_row.step_value
516 ,p_comments => l_preupd_cmptnce_row.comments
517 ,p_cert_date => to_char(l_preupd_cmptnce_row.certification_date,l_user_date_format)
518 ,p_cert_method => l_preupd_cmptnce_row.certification_method
519 ,p_next_cert_date => to_char(l_preupd_cmptnce_row.next_certification_date,l_user_date_format)
520 ,p_prev_start_date => Null
521 ,p_prev_step_value => Null
522 ,p_prev_profy_level_id => Null
523 ,p_change_mode => p_change_mode
524 ,p_sys_generated => 'Y'
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
534 ,p_trans_id => l_transaction_id
535 ,p_trans_step_id => l_transaction_step_id
536 ,p_api_name => g_api_name
537 ,p_person_id => p_person_id
538 ,p_business_group_id => g_business_group_id
539 ,p_comp_element_id => Null
540 ,p_ovn => Null
541 ,p_eff_date_from => p_eff_date_from
542 ,p_eff_date_to => p_eff_date_to
543 ,p_comp_id => p_competence_id
544 ,p_comp_name => p_competence_name
545 ,p_comp_alias => p_competence_alias
546 ,p_profy_level_id => p_proficiency_level_id
547 ,p_profy_level_source => p_proficy_lvl_source
548 ,p_step_value => p_step_value
549 ,p_comments => p_comments
550 ,p_cert_date => p_certification_date
551 ,p_cert_method => p_certification_mthd
552 ,p_next_cert_date => p_next_certifctn_date
553 ,p_prev_start_date => Null
554 ,p_prev_step_value => Null
555 ,p_prev_profy_level_id => p_preupd_proficy_lvl_id
556 ,p_change_mode => p_change_mode
557 ,p_sys_generated => 'N'
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
567 ,p_trans_id => l_transaction_id
568 ,p_trans_step_id => p_transaction_step_id
569 ,p_api_name => g_api_name
570 ,p_person_id => p_person_id
571 ,p_business_group_id => g_business_group_id
572 ,p_comp_element_id => p_competence_element_id
573 ,p_ovn => p_preupd_obj_vers_num
574 ,p_eff_date_from => p_eff_date_from
575 ,p_eff_date_to => p_eff_date_to
576 ,p_comp_id => p_competence_id
577 ,p_comp_name => p_competence_name
578 ,p_comp_alias => p_competence_alias
579 ,p_profy_level_id => p_proficiency_level_id
580 ,p_profy_level_source => p_proficy_lvl_source
581 ,p_step_value => p_step_value
582 ,p_comments => p_comments
583 ,p_cert_date => p_certification_date
584 ,p_cert_method => p_certification_mthd
585 ,p_next_cert_date => p_next_certifctn_date
586 ,p_prev_start_date => p_prev_start_date
587 ,p_prev_step_value => p_prev_step_value
588 ,p_prev_profy_level_id => p_preupd_proficy_lvl_id
589 ,p_change_mode => l_mode_value
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
599 and NAME = 'P_UPGRADED_FROM_REC_ID';
600 select a.transaction_step_id
601 into l_transaction_step_id
602 from hr_api_transaction_values a,
603 hr_api_transaction_values b,
604 hr_api_transaction_steps steps
605 where steps.transaction_id = l_transaction_id
606 and steps.transaction_step_id = a.transaction_step_id
607 and a.name = 'P_CHANGE_MODE'
608 and a.varchar2_value = 'UPGRADE'
609 and steps.transaction_step_id = b.transaction_step_id
610 and b.name = 'P_COMPETENCE_ELEMENT_ID'
611 and b.number_value = l_competence_element_id
612 and a.transaction_step_id <> l_competence_element_id;
613 update hr_api_transaction_values
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
623 ,p_actid => to_number(p_actid)
624 ,p_login_person_id => fnd_global.employee_id
625 ,p_trans_id => l_transaction_id
626 ,p_trans_step_id => p_transaction_step_id
627 ,p_api_name => g_api_name
628 ,p_person_id => p_person_id
629 ,p_business_group_id => g_business_group_id
630 ,p_comp_element_id => p_competence_element_id
631 ,p_ovn => p_preupd_obj_vers_num
632 ,p_eff_date_from => p_eff_date_from
633 ,p_eff_date_to => p_eff_date_to
634 ,p_comp_id => p_competence_id
635 ,p_comp_name => p_competence_name
636 ,p_comp_alias => p_competence_alias
637 ,p_profy_level_id => p_proficiency_level_id
638 ,p_profy_level_source => p_proficy_lvl_source
639 ,p_step_value => p_step_value
640 ,p_comments => p_comments
641 ,p_cert_date => p_certification_date
642 ,p_cert_method => p_certification_mthd
643 ,p_next_cert_date => p_next_certifctn_date
644 ,p_prev_start_date => p_prev_start_date
645 ,p_prev_step_value => p_prev_step_value
646 ,p_prev_profy_level_id => Null
647 ,p_change_mode => 'UPDATE_APPLY'
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
657 ,p_trans_id => l_transaction_id
658 ,p_trans_step_id => p_transaction_step_id
659 ,p_api_name => g_api_name
660 ,p_person_id => p_person_id
661 ,p_business_group_id => g_business_group_id
662 ,p_comp_element_id => Null
663 ,p_ovn => Null
664 ,p_eff_date_from => p_eff_date_from
665 ,p_eff_date_to => p_eff_date_to
666 ,p_comp_id => p_competence_id
667 ,p_comp_name => p_competence_name
668 ,p_comp_alias => p_competence_alias
669 ,p_profy_level_id => p_proficiency_level_id
670 ,p_profy_level_source => p_proficy_lvl_source
671 ,p_step_value => p_step_value
672 ,p_comments => p_comments
673 ,p_cert_date => p_certification_date
674 ,p_cert_method => p_certification_mthd
675 ,p_next_cert_date => p_next_certifctn_date
676 ,p_prev_start_date => p_prev_start_date
677 ,p_prev_step_value => p_prev_step_value
678 ,p_prev_profy_level_id => Null
679 ,p_change_mode => p_change_mode
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;
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 ;
698 End process_save;
699 --
700 -- ---------------------------------------------------------------------------
701 Procedure process_save_currentupdate
702 (p_item_type in wf_items.item_type%type
703 ,p_item_key in wf_items.item_key%type
704 ,p_actid in varchar2
705 ,p_person_id in number
706 ,p_change_mode in varchar2 default null
707 ,p_preupd_obj_vers_num in number default null
708 ,p_competence_id in number default null
709 ,p_competence_element_id in number default null
710 ,p_competence_name in varchar2 default null
711 ,p_competence_alias in varchar2 default null
712 ,p_proficiency_level_id in number default null
713 ,p_step_value in number default null
714 ,p_preupd_proficy_lvl_id in number default null
715 ,p_certification_mthd in varchar2 default null
716 ,p_proficy_lvl_source in varchar2 default null
717 ,p_eff_date_from in varchar2 default null
718 ,p_eff_date_to in varchar2 default null
719 ,p_certification_date in varchar2 default null
720 ,p_next_certifctn_date in varchar2 default null
721 ,p_comments in varchar2 default null
722 ,p_prev_step_value in number default null
723 ,p_prev_start_date in varchar2 default null
724 ,p_competence_status IN VARCHAR2 DEFAULT null
725 ,transaction_step_id in number default null) is
726 --
727 l_proc varchar2(200) := g_package || 'process_save_currentupdate';
728 l_new_competence_element_id number default null;
729 l_new_obj_vers_num number default null;
730 l_object_version_number number default null;
731 --
732 l_line_manager_mode boolean default null;
733 l_wf_update_mode varchar2(100) default null;
734 l_date_to date default null;
735 l_transaction_id number default null;
736 l_transaction_step_id number default null;
737 l_trans_obj_vers_num number default null;
738 l_result varchar2(100) default null;
739 l_trans_tbl hr_comp_profile_ss.transaction_table1;
740 l_user_date_format varchar2(200) default null;
741 l_preupd_date_to date default null;
742 l_eff_date_from date default null;
743 l_eff_date_to date default null;
744 l_certification_date date default null;
745 l_next_certifctn_date date default null;
746 l_count number default 0;
747 l_preupd_cmptnce_row g_csr_get_preupd_cmptnce_rec%rowtype;
748 l_action_person_id number default null;
749 --
750 l_prev_step_val number default null;
751 l_prev_date date;
752 l_prev_start_date date;
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;
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;
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;
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);
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);
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
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)
809 ,funmode => 'RUN'
810 ,p_login_person_id => fnd_global.employee_id
811 ,result => l_result);
812 l_transaction_id := hr_transaction_ss.get_transaction_id
813 (p_item_type => p_item_type
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
823 ,p_api_name => g_api_name
824 ,p_item_type => p_item_type
825 ,p_item_key => p_item_key
826 ,p_activity_id => to_number(p_actid)
827 ,p_transaction_step_id => l_transaction_step_id
828 ,p_object_version_number => l_trans_obj_vers_num);
829 else
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
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 --
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)
850 ,p_login_person_id => fnd_global.employee_id
851 ,p_trans_id => l_transaction_id
852 ,p_trans_step_id => l_transaction_step_id
853 ,p_api_name => g_api_name
854 ,p_person_id => p_person_id
855 ,p_business_group_id => g_business_group_id
856 ,p_comp_element_id => p_competence_element_id
857 ,p_ovn => l_preupd_cmptnce_row.object_version_number
858 ,p_eff_date_from => to_char(l_preupd_cmptnce_row.effective_date_from,g_date_format)
859 ,p_eff_date_to => to_char(l_preupd_date_to,g_date_format)
860 ,p_comp_id => l_preupd_cmptnce_row.competence_id
861 ,p_comp_name => l_preupd_cmptnce_row.name
862 ,p_comp_alias => l_preupd_cmptnce_row.competence_alias
863 ,p_profy_level_id => l_preupd_cmptnce_row.proficiency_level_id
864 ,p_profy_level_source => l_preupd_cmptnce_row.source_of_proficiency_level
865 ,p_step_value => l_preupd_cmptnce_row.step_value
866 ,p_comments => l_preupd_cmptnce_row.comments
867 ,p_cert_date => to_char(l_preupd_cmptnce_row.certification_date,g_date_format)
868 ,p_cert_method => l_preupd_cmptnce_row.certification_method
869 ,p_next_cert_date => to_char(l_preupd_cmptnce_row.next_certification_date,g_date_format)
870 ,p_prev_start_date => null
871 ,p_prev_step_value => null
872 ,p_prev_profy_level_id => Null
873 ,p_change_mode => 'UPDATE_HIST'
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
883 ,p_api_name => g_api_name
884 ,p_item_type => p_item_type
885 ,p_item_key => p_item_key
886 ,p_activity_id => to_number(p_actid)
887 ,p_transaction_step_id => l_transaction_step_id
888 ,p_object_version_number => l_trans_obj_vers_num);
889 writeTo_transTbl(p_item_type => p_item_type
890 ,p_item_key => p_item_key
891 ,p_actid => to_number(p_actid)
892 ,p_login_person_id => fnd_global.employee_id
893 ,p_trans_id => l_transaction_id
894 ,p_trans_step_id => l_transaction_step_id
895 ,p_api_name => g_api_name
896 ,p_person_id => p_person_id
897 ,p_business_group_id => g_business_group_id
898 ,p_comp_element_id => p_competence_element_id
899 ,p_ovn => Null
900 ,p_eff_date_from => p_eff_date_from
901 ,p_eff_date_to => p_eff_date_to
902 ,p_comp_id => p_competence_id
903 ,p_comp_name => l_preupd_cmptnce_row.name
904 ,p_comp_alias => l_preupd_cmptnce_row.competence_alias
905 ,p_profy_level_id => l_PREUPD_PROFICY_LVL_ID
906 ,p_profy_level_source => p_proficy_lvl_source
907 ,p_step_value => p_step_value
908 ,p_comments => p_comments
909 ,p_cert_date => p_certification_date
910 ,p_cert_method => p_certification_mthd
911 ,p_next_cert_date => p_next_certifctn_date
912 ,p_prev_start_date => to_char(l_prev_start_date,g_date_format)
913 ,p_prev_step_value => l_prev_step_value
914 ,p_prev_profy_level_id => null
915 ,p_change_mode => 'UPDATE_APPLY'
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
925 ,p_trans_id => l_transaction_id
926 ,p_trans_step_id => l_transaction_step_id
927 ,p_api_name => g_api_name
928 ,p_person_id => p_person_id
929 ,p_business_group_id => g_business_group_id
930 ,p_comp_element_id => p_competence_element_id
931 ,p_ovn => p_preupd_obj_vers_num
932 ,p_eff_date_from => to_char(l_eff_date_from, g_date_format)
933 ,p_eff_date_to => to_char(l_eff_date_to, g_date_format)
934 ,p_comp_id => p_competence_id
935 ,p_comp_name => p_competence_name
936 ,p_comp_alias => p_competence_alias
937 ,p_profy_level_id => p_proficiency_level_id
938 ,p_profy_level_source => p_proficy_lvl_source
939 ,p_step_value => p_step_value
940 ,p_comments => p_comments
941 ,p_cert_date => p_certification_date
942 ,p_cert_method => p_certification_mthd
943 ,p_next_cert_date => p_next_certifctn_date
944 ,p_prev_start_date => p_prev_start_date
945 ,p_prev_step_value => l_prev_step_value
946 ,p_prev_profy_level_id => null
947 ,p_change_mode => 'UPDATE_APPLY'
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
957 ,p_trans_id => l_transaction_id
958 ,p_trans_step_id => l_transaction_step_id
959 ,p_api_name => g_api_name
960 ,p_person_id => p_person_id
961 ,p_business_group_id => g_business_group_id
962 ,p_comp_element_id => p_competence_element_id
963 ,p_ovn => null
964 ,p_eff_date_from => to_char(l_eff_date_from, g_date_format)
965 ,p_eff_date_to => to_char(l_eff_date_to, g_date_format)
966 ,p_comp_id => p_competence_id
967 ,p_comp_name => p_competence_name
968 ,p_comp_alias => p_competence_alias
969 ,p_profy_level_id => p_proficiency_level_id
970 ,p_profy_level_source => p_proficy_lvl_source
971 ,p_step_value => p_step_value
972 ,p_comments => p_comments
973 ,p_cert_date => p_certification_date
974 ,p_cert_method => p_certification_mthd
975 ,p_next_cert_date => p_next_certifctn_date
976 ,p_prev_start_date => p_prev_start_date
977 ,p_prev_step_value => l_prev_step_value
978 ,p_prev_profy_level_id => l_PREUPD_PROFICY_LVL_ID
979 ,p_change_mode => 'UPDATE_APPLY'
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);
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;
998 End process_save_currentupdate;
999 -- ---------------------------- < process_api > ------------------------------
1000 -- ---------------------------------------------------------------------------
1001 -- Purpose: This procedure reads the data from transaction table and saves it
1002 -- to the database.
1003 -- This procedure is called after Workflow Approval or the user
1004 -- chooses "Update" without approval in workflow.
1005 -- ---------------------------------------------------------------------------
1006 Procedure process_api(p_validate in boolean default false
1007 ,p_transaction_step_id in number
1008 ,p_effective_date in varchar2 default null) is
1009 --
1010 l_proc varchar2(200) := g_package || 'process_api';
1011 l_cmp_element_rec per_competence_elements%rowtype;
1012 l_system_generated varchar2(1) default null;
1013 l_upgraded_from_rec_id number DEFAULT Null;
1014 l_preupd_proficy_lvl_id number default NULL;
1015 l_competence_name per_competences_vl.name%type;
1016 l_step_value per_rating_levels.step_value%type;
1017 l_item_type hr_api_transaction_steps.item_type%type;
1018 l_item_key hr_api_transaction_steps.item_key%type;
1019 l_activity_id hr_api_transaction_steps.ACTIVITY_ID%type;
1020 l_cmp_element_id per_competence_elements.competence_element_id%type;
1021 l_achieved_date per_competence_elements.ACHIEVED_DATE%type;
1022 l_change_mode varchar2(80);
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
1032 (p_transaction_step_id => p_transaction_step_id
1033 ,p_name => 'P_BUSINESS_GROUP_ID');
1034 --
1035 l_cmp_element_rec.comments :=
1036 hr_transaction_api.get_varchar2_value
1037 (p_transaction_step_id => p_transaction_step_id
1038 ,p_name => 'P_COMMENTS');
1039 --
1040 l_cmp_element_rec.person_id:=
1041 hr_transaction_api.get_number_value
1042 (p_transaction_step_id => p_transaction_step_id
1043 ,p_name => 'P_PERSON_ID');
1044 --
1045 l_cmp_element_rec.competence_element_id :=
1046 hr_transaction_api.get_number_value
1047 (p_transaction_step_id => p_transaction_step_id
1048 ,p_name => 'P_COMPETENCE_ELEMENT_ID');
1049 --
1050 l_cmp_element_rec.object_version_number :=
1051 hr_transaction_api.get_number_value
1052 (p_transaction_step_id => p_transaction_step_id
1053 ,p_name => 'P_OBJECT_VERSION_NUMBER');
1054 --
1055 l_cmp_element_rec.competence_id :=
1056 hr_transaction_api.get_number_value
1057 (p_transaction_step_id => p_transaction_step_id
1058 ,p_name => 'P_COMPETENCE_ID');
1059 --
1060 l_competence_name :=
1061 hr_transaction_api.get_varchar2_value
1062 (p_transaction_step_id => p_transaction_step_id
1063 ,p_name => 'P_COMPETENCE_NAME');
1064 --
1065 l_cmp_element_rec.proficiency_level_id:=
1066 hr_transaction_api.get_number_value
1067 (p_transaction_step_id => p_transaction_step_id
1068 ,p_name => 'P_PROFICIENCY_LEVEL_ID');
1069 --
1070 l_step_value :=
1071 hr_transaction_api.get_number_value
1072 (p_transaction_step_id => p_transaction_step_id
1073 ,p_name => 'P_STEP_VALUE');
1074 --
1075 l_preupd_proficy_lvl_id:=
1076 hr_transaction_api.get_number_value
1077 (p_transaction_step_id => p_transaction_step_id
1078 ,p_name => 'P_PREUPD_PROFICY_LVL_ID');
1079 --
1080 l_cmp_element_rec.certification_method:=
1081 hr_transaction_api.get_varchar2_value
1082 (p_transaction_step_id => p_transaction_step_id
1083 ,p_name => 'P_CERTIFICATION_MTHD');
1084 --
1085 l_cmp_element_rec.source_of_proficiency_level:=
1086 hr_transaction_api.get_varchar2_value
1087 (p_transaction_step_id => p_transaction_step_id
1088 ,p_name => 'P_PROFICY_LVL_SOURCE');
1089 --
1090 l_cmp_element_rec.effective_date_from :=
1091 hr_transaction_api.get_date_value
1092 (p_transaction_step_id => p_transaction_step_id
1093 ,p_name => 'P_EFF_DATE_FROM');
1094 --
1095 l_cmp_element_rec.effective_date_to :=
1096 hr_transaction_api.get_date_value
1097 (p_transaction_step_id => p_transaction_step_id
1098 ,p_name => 'P_EFF_DATE_TO');
1099 --
1100 l_cmp_element_rec.certification_date :=
1101 hr_transaction_api.get_date_value
1102 (p_transaction_step_id => p_transaction_step_id
1103 ,p_name => 'P_CERTIFICATION_DATE');
1104 --
1105 l_cmp_element_rec.next_certification_date :=
1106 hr_transaction_api.get_date_value
1107 (p_transaction_step_id => p_transaction_step_id
1108 ,p_name => 'P_NEXT_CERTIFCTN_DATE');
1109 --
1110 l_system_generated :=
1111 hr_transaction_api.get_varchar2_value
1112 (p_transaction_step_id => p_transaction_step_id
1113 ,p_name => 'P_SYSTEM_GENERATED');
1114 --
1115 l_upgraded_from_rec_id := NULL;
1116 l_upgraded_from_rec_id :=
1117 hr_transaction_api.get_number_value
1118 (p_transaction_step_id => p_transaction_step_id
1119 ,p_name => 'P_UPGRADED_FROM_REC_ID');
1120 -- for comp qual link enahancement
1121 l_change_mode := NULL;
1122 l_change_mode :=
1123 hr_transaction_api.get_varchar2_value
1124 (p_transaction_step_id => p_transaction_step_id
1125 ,p_name => 'P_CHANGE_MODE');
1126
1127 l_cmp_element_rec.status :=
1128 hr_transaction_api.get_varchar2_value
1129 (p_transaction_step_id => p_transaction_step_id
1130 ,p_name => 'P_STATUS');
1131 Select ITEM_TYPE,ITEM_KEY,ACTIVITY_ID
1132 INTO l_item_type, l_item_key, l_activity_id
1133 FROM hr_api_transaction_steps
1134 WHERE transaction_step_id = p_transaction_step_id;
1135 l_cmp_element_rec.status := PerCompStatus.Get_Competence_Status(
1136 p_item_type =>l_item_type
1137 ,p_item_key =>l_item_key
1138 ,p_activity_id =>l_activity_id
1139 ,p_competence_id => l_cmp_element_rec.competence_id
1140 ,p_competence_element_id => l_cmp_element_rec.competence_element_id
1141 ,p_person_id => l_cmp_element_rec.person_id
1142 ,p_eff_date => trunc(sysdate));
1143 IF l_cmp_element_rec.status = 'ACHIEVED' Then
1144 l_cmp_element_rec.achieved_date := trunc(sysdate);
1145 else
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
1155 savepoint save_competence_element;
1156 --
1157 l_cmp_element_id := null;
1158 IF l_upgraded_from_rec_id IS NOT NULL then
1159 l_cmp_element_id := l_upgraded_from_rec_id;
1160 else
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
1170 -- Adding the Status field.
1171
1172 per_cel_upd.upd
1173 (p_validate => False
1174 ,p_competence_element_id => l_cmp_element_rec.competence_element_id
1175 ,p_object_version_number => l_cmp_element_rec.object_version_number
1176 ,p_effective_date_to => l_cmp_element_rec.effective_date_to
1177 -- ,p_status => l_cmp_element_rec.status
1178 -- ,p_achieved_date => l_achieved_date
1179 ,p_effective_date => trunc(sysdate));
1180 --
1181 If l_change_mode LIKE 'UPGRADE%' then
1182 null;
1183 else
1184 HR_COMP_OUTCOME_PROFILE_SS.call_process_api(
1185 p_validate => p_validate
1186 ,p_competence_id => l_cmp_element_rec.competence_id
1187 ,p_competence_element_id => l_cmp_element_id
1188 ,p_new_competence_element_id => l_cmp_element_rec.competence_element_id
1189 ,p_item_type => l_item_type
1190 ,p_item_key => l_item_key
1191 ,p_activity_id => l_activity_id
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;
1201 IF l_status = l_cmp_element_rec.status then
1202 l_cmp_element_rec.achieved_date := l_achieved_date;
1203 END if;
1204 per_cel_upd.upd
1205 (p_validate => False
1206 ,p_competence_element_id => l_cmp_element_rec.competence_element_id
1207 ,p_comments => l_cmp_element_rec.comments
1208 ,p_object_version_number => l_cmp_element_rec.object_version_number
1209 ,p_effective_date_from => l_cmp_element_rec.effective_date_from
1210 ,p_effective_date_to => l_cmp_element_rec.effective_date_to
1211 ,p_proficiency_level_id => l_cmp_element_rec.proficiency_level_id
1212 ,p_source_of_proficiency_level =>
1213 l_cmp_element_rec.source_of_proficiency_level
1214 ,p_certification_method => l_cmp_element_rec.certification_method
1215 ,p_certification_date => l_cmp_element_rec.certification_date
1216 ,p_next_certification_date =>
1217 l_cmp_element_rec.next_certification_date
1218 ,p_status => l_cmp_element_rec.status -- for comp qual link enahancement
1219 ,p_achieved_date => l_cmp_element_rec.achieved_date -- for comp qual link enahancement
1220 ,p_effective_date => trunc(sysdate));
1221 --
1222 If l_change_mode LIKE 'UPGRADE%' then
1223 null;
1224 else
1225 HR_COMP_OUTCOME_PROFILE_SS.call_process_api(
1226 p_validate => p_validate
1227 ,p_competence_id => l_cmp_element_rec.competence_id
1228 ,p_competence_element_id => l_cmp_element_id
1229 ,p_new_competence_element_id => l_cmp_element_rec.competence_element_id
1230 ,p_item_type => l_item_type
1231 ,p_item_key => l_item_key
1232 ,p_activity_id => l_activity_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
1242 ,p_comments => l_cmp_element_rec.comments
1243 ,p_object_version_number => l_cmp_element_rec.object_version_number
1244 ,p_type => l_cmp_element_rec.type
1245 ,p_person_id => l_cmp_element_rec.person_id
1246 ,p_competence_id => l_cmp_element_rec.competence_id
1247 ,p_effective_date_from => l_cmp_element_rec.effective_date_from
1248 ,p_effective_date_to => l_cmp_element_rec.effective_date_to
1249 ,p_proficiency_level_id => l_cmp_element_rec.proficiency_level_id
1250 ,p_source_of_proficiency_level =>
1251 l_cmp_element_rec.source_of_proficiency_level
1252 ,p_certification_method => l_cmp_element_rec.certification_method
1253 ,p_certification_date => l_cmp_element_rec.certification_date
1254 ,p_next_certification_date =>
1255 l_cmp_element_rec.next_certification_date
1256 ,p_status => l_cmp_element_rec.status -- for comp qual link enahancement
1257 ,p_achieved_date => l_cmp_element_rec.achieved_date -- for comp qual link enahancement
1258 ,p_effective_date => trunc(sysdate));
1259 IF l_cmp_element_id IS NULL then
1260 l_cmp_element_id := l_cmp_element_rec.competence_element_id;
1261 END if;
1262 HR_COMP_OUTCOME_PROFILE_SS.call_process_api(
1263 p_validate => p_validate
1264 ,p_competence_id => l_cmp_element_rec.competence_id
1265 ,p_competence_element_id => l_cmp_element_id
1266 ,p_new_competence_element_id => l_cmp_element_rec.competence_element_id
1267 ,p_item_type => l_item_type
1268 ,p_item_key => l_item_key
1269 ,p_activity_id => l_activity_id
1270 ,p_person_id => l_cmp_element_rec.person_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;
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);
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 --------------------------
1298 ------------------------------------------------------------------------------------------------------------
1299 Procedure api_validate_competence_rec_ss
1300 (p_item_type in varchar2
1301 ,p_item_key in varchar2
1302 ,p_activity_id in varchar2
1303 ,p_pid in number
1304 ,p_validate in varchar2
1305 ,p_business_group_id in number default null
1306 ,p_change_mode in varchar2 default null
1307 ,p_competence_element_id in number default null
1308 ,p_preupd_obj_vers_num in number default null
1309 ,p_competence_id in number default null
1310 ,p_proficiency_level_id in number default null
1311 ,p_eff_date_from in varchar2 default null
1312 ,p_comments in varchar2 default null
1313 ,p_eff_date_to in varchar2 default null
1314 ,p_proficy_lvl_source in varchar2 default null
1315 ,p_certification_mthd in varchar2 default null
1316 ,p_certification_date in varchar2 default null
1317 ,p_next_certifctn_date in varchar2 default null
1318 ,p_competence_status in varchar2 default null
1319 ,p_transaction_step_id in out nocopy number
1320 ,p_error_message out nocopy long) is
1321 --
1322 l_proc varchar2(200) := g_package || 'api_validate_competence_rec_ss';
1323 l_user_date_format varchar2(200) default null;
1324 l_user_date_format_length number default null;
1325 l_sample_date varchar2(200) default null;
1326 l_date_char varchar2(200) default null;
1327 l_rec_changed boolean default null;
1328 l_date_error boolean default null;
1329 l_msg_text varchar2(2000) default null;
1330 l_eff_date_from date default null;
1331 l_eff_date_to date default null;
1332 l_preupd_date_to date default null;
1333 l_new_competence_element_id number default null;
1334 l_new_obj_vers_num number default null;
1335 l_next_certification_date date default null;
1336 l_certification_date date default null;
1337 l_object_version_number number default null;
1338 l_action_person_id number default null;
1339 l_validate boolean default null;
1340 l_comments varchar2(200) default null;
1341 --
1342 l_current_step_value number default null;
1343 l_current_step_val number default null;
1344 l_prev_step_value number default null;
1345 l_prev_step_val number default null;
1346 l_prev_prof_lvl_value number default null;
1347 l_prev_prof_lvl_val number default null;
1348 c_name per_competences.name%TYPE default null;
1349 p_competence_name per_competences.name%TYPE default null;
1350 c_comp_alias varchar2(200) default null;
1351 p_competence_alias varchar2(200) default null;
1352 l_prev_date date;
1353 l_prev_start_date date;
1354 l_changed boolean default false;
1355 l_warning_exists boolean default false;
1356 l_mode_fetch varchar2(20) default null;
1357 m_mode varchar2(20) default null;
1358 CURSOR get_old_txn_ids ( p_item_type in varchar2
1359 ,p_item_key in varchar2
1360 ,p_activity_id IN varchar2
1361 ,p_person_id IN number
1362 ,p_competence_element_id IN number
1363 ,p_competence_id IN number
1364 ,p_change_mode IN varchar2) is
1365 Select s.transaction_step_id
1366 FROM hr_api_transaction_steps s,
1367 hr_api_transaction_values c, hr_api_transaction_values d,
1368 hr_api_transaction_values e, hr_api_transaction_values f
1369 WHERE
1370 s.item_type = p_item_type
1371 and s.item_key = p_item_key
1372 and s.activity_id = p_activity_id
1373 AND s.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
1374 and c.transaction_step_id = s.transaction_step_id
1375 and d.transaction_step_id = s.transaction_step_id
1376 and e.transaction_step_id = s.transaction_step_id
1377 and f.transaction_step_id = s.transaction_step_id
1378 AND c.NAME = 'P_PERSON_ID'
1379 AND d.NAME = 'P_COMPETENCE_ELEMENT_ID'
1380 AND e.NAME = 'P_COMPETENCE_ID'
1381 AND f.NAME = 'P_CHANGE_MODE'
1382 AND c.number_value = p_person_id
1383 AND d.number_value = p_competence_element_id
1384 AND e.number_value = p_competence_id
1385 AND f.varchar2_value = p_change_mode ;
1386 Cursor get_comp_dates ( p_competence_id IN Number )is
1387 select date_to end_date,
1388 date_from start_date
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;
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
1404 ,p_eff_date_from => to_date(p_eff_date_from,g_date_format)
1405 ,p_eff_date_to => to_date(p_eff_date_to,g_date_format)
1406 ,p_proficy_lvl_source => p_proficy_lvl_source
1407 ,p_certification_mthd => p_certification_mthd
1408 ,p_certification_date => to_date(p_certification_date,g_date_format)
1409 ,p_next_certifctn_date => to_date(p_next_certifctn_date,g_date_format)
1410 ,p_competence_status => p_competence_status
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
1420 ,p_pid => p_pid
1421 ,p_competence_element_id => p_competence_element_id
1422 ,p_competence_id => p_competence_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;
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);
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;
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;
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);
1459 close get_comp_name;
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);
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(
1476 p_error_message => p_error_message,
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);
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',
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);
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;
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);
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);
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;
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';
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;
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
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';
1562 select number_value into l_prev_prof_lvl_value
1563 from hr_api_transaction_values
1564 where transaction_step_id = p_transaction_step_id
1565 and name = 'P_PREUPD_PROFICY_LVL_ID';
1566 select date_value into l_prev_start_date
1567 from hr_api_transaction_values
1568 where transaction_step_id = p_transaction_step_id
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
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);
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
1598 ,p_change_mode => p_change_mode
1599 ,p_competence_element_id => p_competence_element_id
1600 ,p_preupd_obj_vers_num => p_preupd_obj_vers_num
1601 ,p_competence_id => p_competence_id
1602 ,p_competence_name => p_competence_name
1603 ,p_competence_alias => p_competence_alias
1604 ,p_proficiency_level_id => p_proficiency_level_id
1605 ,p_step_value => l_current_step_value
1606 ,p_preupd_proficy_lvl_id => l_prev_prof_lvl_value
1607 ,p_certification_mthd => p_certification_mthd
1608 ,p_proficy_lvl_source => p_proficy_lvl_source
1609 ,p_eff_date_from => p_eff_date_from
1610 ,p_eff_date_to => to_char(l_eff_date_to,g_date_format)
1611 ,p_certification_date => p_certification_date
1612 ,p_next_certifctn_date => p_next_certifctn_date
1613 ,p_comments => p_comments
1614 ,p_prev_step_value => l_prev_step_value
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;
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
1630 (p_item_type IN varchar2
1631 ,p_item_key IN varchar2
1632 ,p_step_values out nocopy varchar2
1633 ,p_rows out nocopy number) is
1634 l_proc varchar2(200) := g_package || 'get_pending_addition_ids';
1635 cursor get_add_ids (p_transaction_id number) is
1636 select step.transaction_step_id
1637 from hr_api_transaction_steps step, hr_api_transaction_values val
1638 where step.transaction_id = p_transaction_id
1639 and val.transaction_step_id = step.transaction_step_id
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;
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;
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
1660 l_proc varchar2(200) := g_package || 'del_pen_currupd_ids';
1661 cursor get_upd_ids(p_transaction_id number) is
1662 select steps.transaction_step_id
1663 from hr_api_transaction_values val, hr_api_transaction_steps steps
1664 where steps.transaction_id = p_transaction_id
1665 and steps.transaction_step_id = val.transaction_step_id
1666 and val.name = 'P_CHANGE_MODE'
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
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
1681 delete from hr_api_transaction_values
1682 where transaction_step_id = I.transaction_step_id;
1683 delete from hr_api_transaction_steps
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
1693 l_proc varchar2(200) := g_package || 'del_add_ids';
1694 cursor get_add_ids(p_transaction_id number) is
1695 select steps.transaction_step_id
1696 from hr_api_transaction_values val, hr_api_transaction_steps steps
1697 where steps.transaction_id = p_transaction_id
1698 and steps.transaction_step_id = val.transaction_step_id
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
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
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
1720 |
1721 | Purpose :
1722 |
1723 | Saves the records into Transaction Tables.
1724 | Created as hr_transaction_ss.save_transaction_step fails when
1725 | value is passed in NULL
1726 |
1727 +-----------------------------------------------------------------------------*/
1728 PROCEDURE save_transaction_step
1729 (p_item_type IN VARCHAR2
1730 ,p_item_key IN VARCHAR2
1731 ,p_actid IN NUMBER
1732 ,p_login_person_id IN NUMBER
1733 ,p_transaction_step_id IN OUT NOCOPY NUMBER
1734 ,p_api_name IN VARCHAR2 default null
1735 ,p_api_display_name IN VARCHAR2 DEFAULT NULL
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);
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));
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))));
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);
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
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
1779 ,p_item_key in varchar2) is
1780 l_proc varchar2(200) := g_package || 'delete_all_ids';
1781 cursor get_all_ids (p_transaction_id number) is
1782 select transaction_step_id
1783 from hr_api_transaction_steps
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
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
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
1806 l_proc varchar2(200) := g_package || 'delete_transaction_step_id';
1807 l_transaction_step_id number;
1808 l_txid number;
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
1818 and rownum = 1;
1819 select varchar2_value
1820 into l_mode
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
1830 and NAME = 'P_UPGRADED_FROM_REC_ID';
1831 select a.transaction_step_id into l_txid
1832 from hr_api_transaction_values a,hr_api_transaction_values b,
1833 hr_api_transaction_steps steps
1834 where steps.transaction_id = l_transaction_id
1835 and steps.transaction_step_id = a.transaction_step_id
1836 and a.name = 'P_CHANGE_MODE'
1837 and a.varchar2_value = 'UPGRADE'
1838 and steps.transaction_step_id = b.transaction_step_id
1839 and b.name = 'P_COMPETENCE_ELEMENT_ID'
1840 and b.number_value = l_competence_element_id
1841 and a.transaction_step_id <> l_transaction_step_id;
1842 delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1843 delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1844 delete from hr_api_transaction_values where transaction_step_id = l_txid ;
1845 delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
1846 else
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
1856 (p_item_type in wf_items.item_type%type
1857 ,p_item_key in wf_items.item_key%type
1858 ,p_actid in varchar2
1859 ,p_person_id in number
1860 ,p_proficiency_level_id in number default null
1861 ,p_step_value in number default null
1862 ,p_eff_date_from in varchar2 default null
1863 ,p_prev_step_value in number default null
1864 ,p_competence_status IN VARCHAR2 DEFAULT null
1865 ,transaction_step_id in number ) is
1866 l_proc varchar2(200) := g_package || 'process_save_update_details';
1867 l_eff_date_from date default null;
1868 l_count number default 0;
1869 l_trans_tbl hr_comp_profile_ss.transaction_table1;
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;
1879 l_action_person_id := p_person_id;
1880 l_transaction_step_id := transaction_step_id;
1881 l_count := l_count + 1;
1882 l_trans_tbl(l_count).param_name := 'P_PROFICIENCY_LEVEL_ID';
1883 l_trans_tbl(l_count).param_value := p_proficiency_level_id;
1884 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1885 --
1886 l_count := l_count + 1;
1887 l_trans_tbl(l_count).param_name := 'P_STEP_VALUE';
1888 l_trans_tbl(l_count).param_value := p_step_value;
1889 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1890 --
1891 l_count := l_count + 1;
1892 l_trans_tbl(l_count).param_name := 'P_EFF_DATE_FROM';
1893 l_trans_tbl(l_count).param_value := p_eff_date_from;
1894 l_trans_tbl(l_count).param_data_type := 'DATE';
1895 ---------------
1896 l_count := l_count + 1;
1897 l_trans_tbl(l_count).param_name := 'P_CHANGE_MODE';
1898 l_trans_tbl(l_count).param_value := 'UPDATE_APPLY';
1899 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1900 ---------------
1901 l_count := l_count + 1;
1902 l_trans_tbl(l_count).param_name := 'P_STATUS';
1903 l_trans_tbl(l_count).param_value := p_competence_status;
1904 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
1905 ----
1906 save_transaction_step
1907 (p_item_type => p_item_type
1908 ,p_item_key => p_item_key
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
1918 (p_item_type in varchar2
1919 ,p_item_key in varchar2
1920 ,p_activity_id in varchar2
1921 ,p_pid in number
1922 ,p_competence_id in number
1923 ,p_competence_element_id in number default null
1924 ,p_proficiency_level_id in number default null
1925 ,p_eff_date_from in varchar2 default null
1926 ,p_comments in varchar2 default null
1927 ,p_eff_date_to in varchar2 default null
1928 ,p_proficy_lvl_source in varchar2 default null
1929 ,p_certification_mthd in varchar2 default null
1930 ,p_certification_date in varchar2 default null
1931 ,p_next_certifctn_date in varchar2 default null
1932 ,p_preupd_obj_vers_num in number default null
1933 ,p_transaction_step_id in number
1934 ,p_prev_eff_date_from in varchar2 default null
1935 ,p_pre_eff_date_to in varchar2 default null
1936 ,p_competence_status in varchar2 default null
1937 ,p_error_message out nocopy long) is
1938 l_proc varchar2(200) := g_package || 'save_update_details';
1939 l_user_date_format varchar2(20) ;
1940 l_eff_date_from date default null;
1941 l_eff_date_to date default null;
1942 l_certification_date date default null;
1943 l_next_certification_date date default null;
1944 l_next_certifctn_date date default null;
1945 l_current_step_value number;
1946 l_current_step_val number;
1947 l_prev_date date;
1948 l_prev_start_date date;
1949 l_date_error boolean default null;
1950 l_transaction_step_id number;
1951 g_business_group_id number default null;
1952 l_prev_step_value number default null;
1953 l_prev_step_val number default null;
1954 --------------
1955 l_object_version_number number default null;
1956 l_trans_tbl hr_comp_profile_ss.transaction_table1;
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);
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;
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
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);
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;
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',
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
2015 ,p_change_mode => 'UPGRADE'
2016 ,p_competence_element_id => p_competence_element_id
2017 ,p_preupd_obj_vers_num => p_preupd_obj_vers_num
2018 ,p_competence_id => p_competence_id
2019 ,p_proficiency_level_id => p_proficiency_level_id
2020 ,p_eff_date_from => p_eff_date_from
2021 ,p_eff_date_to => p_eff_date_to
2022 ,p_proficy_lvl_source => p_proficy_lvl_source
2023 ,p_certification_mthd => p_certification_mthd
2024 ,p_certification_date => p_certification_date
2025 ,p_next_certifctn_date => p_next_certifctn_date
2026 ,p_competence_status => p_competence_status -- Competence Qualification link Enh.
2027 ,p_eff_date_from_date_type => l_eff_date_from
2028 ,p_eff_date_to_date_type => l_eff_date_to
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';
2038 l_trans_tbl(l_count).param_value := p_proficiency_level_id;
2039 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2040 --
2041 l_count := l_count + 1;
2042 l_trans_tbl(l_count).param_name := 'P_PREV_START_DATE';
2043 l_trans_tbl(l_count).param_value := to_char(l_prev_start_date,g_date_format);
2044 l_trans_tbl(l_count).param_data_type := 'DATE';
2045 l_count := l_count + 1;
2046 l_trans_tbl(l_count).param_name := 'P_STEP_VALUE';
2047 l_trans_tbl(l_count).param_value := l_current_step_value;
2048 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2049 --
2050 l_count := l_count + 1;
2051 l_trans_tbl(l_count).param_name := 'P_CERTIFICATION_MTHD';
2052 l_trans_tbl(l_count).param_value := p_certification_mthd;
2053 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2054 --
2055 l_count := l_count + 1;
2056 l_trans_tbl(l_count).param_name := 'P_PROFICY_LVL_SOURCE';
2057 l_trans_tbl(l_count).param_value := p_proficy_lvl_source;
2058 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2059 --
2060 l_count := l_count + 1;
2061 l_trans_tbl(l_count).param_name := 'P_EFF_DATE_FROM';
2062 l_trans_tbl(l_count).param_value := p_eff_date_from;
2063 l_trans_tbl(l_count).param_data_type := 'DATE';
2064 --
2065 l_count := l_count + 1;
2066 l_trans_tbl(l_count).param_name := 'P_EFF_DATE_TO';
2067 l_trans_tbl(l_count).param_value := p_eff_date_to;
2068 l_trans_tbl(l_count).param_data_type := 'DATE';
2069 --
2070 l_count := l_count + 1;
2071 l_trans_tbl(l_count).param_name := 'P_CERTIFICATION_DATE';
2072 l_trans_tbl(l_count).param_value := p_certification_date;
2073 l_trans_tbl(l_count).param_data_type := 'DATE';
2074 --
2075 l_count := l_count + 1;
2076 l_trans_tbl(l_count).param_name := 'P_NEXT_CERTIFCTN_DATE';
2077 l_trans_tbl(l_count).param_value := p_next_certifctn_date;
2078 l_trans_tbl(l_count).param_data_type := 'DATE';
2079 --
2080 l_count := l_count + 1;
2081 l_trans_tbl(l_count).param_name := 'P_COMMENTS';
2082 l_trans_tbl(l_count).param_value := p_comments;
2083 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2084 -- Competence Qualification link enhancement
2085 l_count := l_count + 1;
2086 l_trans_tbl(l_count).param_name := 'P_STATUS';
2087 l_trans_tbl(l_count).param_value := p_competence_status;
2088 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2089 --
2090 save_transaction_step
2091 (p_item_type => p_item_type
2092 ,p_item_key => p_item_key
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);
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
2109 (p_item_type in varchar2
2110 ,p_item_key in varchar2
2111 ,p_activity_id in varchar2
2112 ,p_competence_element_id in number default null
2113 ,p_pid in number
2114 ,p_proficiency_level_id in number default null
2115 ,p_eff_date_from in varchar2 default null
2116 ,p_step_value in number
2117 ,p_transaction_step_id in number
2118 ,p_competence_status IN VARCHAR2 ) is
2119 l_proc varchar2(200) := g_package || 'final_update_save';
2120 l_eff_date_from date ;
2121 l_trans_tbl hr_comp_profile_ss.transaction_table1;
2122 l_action_person_id number;
2123 l_count number default 0;
2124 l_transaction_step_id number;
2125 l_prev_date date;
2126 l_prev_start_date date;
2127 l_competence_element_id number default null;
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);
2137 l_preupd_date_to := l_eff_date_from - 1 ;
2138 l_action_person_id := p_pid;
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;
2148 l_prev_start_date := l_prev_date;
2149 update hr_api_transaction_values val
2150 set val.varchar2_value = 'UPGRADE'
2151 where val.transaction_step_id in (select val1.transaction_step_id
2152 from hr_api_transaction_values val1,
2153 hr_api_transaction_steps steps
2154 where steps.transaction_id = l_transaction_id
2155 and steps.transaction_step_id = val1.transaction_step_id
2156 and val1.name = 'P_COMPETENCE_ELEMENT_ID'
2157 and val1.number_value = p_competence_element_id)
2158 and val.name = 'P_CHANGE_MODE'
2159 and val.varchar2_value = 'UPDATE_HIST';
2160 l_count := l_count + 1;
2161 l_trans_tbl(l_count).param_name := 'P_PROFICIENCY_LEVEL_ID';
2162 l_trans_tbl(l_count).param_value := p_proficiency_level_id;
2163 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2164 --
2165 l_count := l_count + 1;
2166 l_trans_tbl(l_count).param_name := 'P_STEP_VALUE';
2167 l_trans_tbl(l_count).param_value := p_step_value;
2168 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2169 --
2170 l_count := l_count + 1;
2171 l_trans_tbl(l_count).param_name := 'P_CHANGE_MODE';
2172 l_trans_tbl(l_count).param_value := 'UPGRADE';
2173 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2174 --
2175 l_count := l_count + 1;
2176 l_trans_tbl(l_count).param_name := 'P_EFF_DATE_FROM';
2177 l_trans_tbl(l_count).param_value := p_eff_date_from;
2178 l_trans_tbl(l_count).param_data_type := 'DATE';
2179 --
2180 l_count := l_count + 1;
2181 l_trans_tbl(l_count).param_name := 'P_PREV_START_DATE';
2182 l_trans_tbl(l_count).param_value := to_char(l_prev_start_date,g_date_format);
2183 l_trans_tbl(l_count).param_data_type := 'DATE';
2184 l_count := l_count + 1;
2185 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ELEMENT_ID';
2186 l_trans_tbl(l_count).param_value := p_competence_element_id ;
2187 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2188 -- Competenece Qualification link enhancement
2189 l_count := l_count + 1;
2190 l_trans_tbl(l_count).param_name := 'P_STATUS';
2191 l_trans_tbl(l_count).param_value := p_competence_status ;
2192 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2193 select number_value into l_competence_element_id
2194 from hr_api_transaction_values
2195 where transaction_step_id = l_transaction_step_id
2196 and NAME = 'P_UPGRADED_FROM_REC_ID';
2197 select a.transaction_step_id into l_tx_step_id
2198 from hr_api_transaction_values a,hr_api_transaction_values b,
2199 hr_api_transaction_steps steps
2200 where steps.transaction_id = l_transaction_id
2201 and steps.transaction_step_id = a.transaction_step_id
2202 and a.name = 'P_CHANGE_MODE'
2203 and a.varchar2_value = 'UPGRADE'
2204 and steps.transaction_step_id = b.transaction_step_id
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';
2214 end if;
2215 save_transaction_step
2216 (p_item_type => p_item_type
2217 ,p_item_key => p_item_key
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;
2227 end final_update_save;
2228 -----------------------------------------------------
2229 procedure get_comp_name_alias(
2230 p_competence_name in out nocopy varchar2
2231 ,p_competence_alias in out nocopy varchar2
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 --
2241 select name,competence_alias,competence_id into p_competence_name,p_competence_alias,p_competence_id
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;
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
2257 (p_competence_name in varchar2 default null
2258 ,p_competence_alias in varchar2 default null
2259 ,l_competence_id out nocopy varchar2) is
2260 l_proc varchar2(200) := g_package || '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));
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);
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);
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))) );
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);
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;
2303 end get_lov_comp_id;
2304 -------------------------------------
2305 PROCEDURE write_add_transaction(
2306 p_item_type in varchar2 default null
2307 ,p_item_key in varchar2 default null
2308 ,p_activity_id in varchar2 default null
2309 ,p_pid in varchar2 default null
2310 ,p_competence_id in varchar2 default null
2311 ,p_competence_name in varchar2 default null
2312 ,p_competence_alias in varchar2 default null
2313 ,p_proficiency_level_id in varchar2 default null
2314 ,p_step_value in varchar2 default null
2315 ,p_eff_date_from in varchar2 default null
2316 ,p_change_mode in varchar2 default null
2317 ,p_row_index in number default null
2318 ,p_transaction_step_id in out nocopy varchar2
2319 ,p_competence_status IN VARCHAR2 DEFAULT NULL ) -- Competence Qualification link
2320 is
2321 l_proc varchar2(200) := g_package || 'write_add_transaction';
2322 x_person_id number default null;
2323 x_competence_id number default null;
2324 x_prof_level_id number default null;
2325 x_step_value number default null;
2326 l_transaction_id number default null;
2327 l_trans_tbl hr_comp_profile_ss.transaction_table1;
2328 l_user_date_format varchar2(200) default null;
2329 l_eff_date_from date default null;
2330 l_eff_date_to date default null;
2331 l_certification_date date default null;
2332 l_next_certifctn_date date default null;
2333 l_count number default 0;
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 ;
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);
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;
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
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
2373 ,actid => to_number(p_activity_id)
2374 ,funmode => 'RUN'
2375 ,p_login_person_id => fnd_global.employee_id
2376 ,result => l_result);
2377 l_transaction_id := hr_transaction_ss.get_transaction_id
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
2387 ,p_creator_person_id => fnd_global.employee_id
2388 ,p_transaction_id => l_transaction_id
2389 ,p_api_name => g_api_name
2390 ,p_item_type => p_item_type
2391 ,p_item_key => p_item_key
2392 ,p_activity_id => to_number(p_activity_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;
2402 l_trans_tbl(l_count).param_name := 'P_ROW_INDEX';
2403 l_trans_tbl(l_count).param_value := p_row_index;
2404 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2405 l_count := 1;
2406 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
2407 l_trans_tbl(l_count).param_value := l_action_person_id;
2408 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2409 --
2410 l_count := l_count + 1;
2411 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
2412 l_trans_tbl(l_count).param_value := g_business_group_id;
2413 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2414 --
2415 l_count := l_count + 1;
2416 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ELEMENT_ID';
2417 l_trans_tbl(l_count).param_value := null;
2418 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2419 --
2420 l_count := l_count + 1;
2421 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
2422 l_trans_tbl(l_count).param_value := null;
2423 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2424 --
2425 l_count := l_count + 1;
2426 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ID';
2427 l_trans_tbl(l_count).param_value := x_competence_id;
2428 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2429 --
2430 l_count := l_count + 1;
2431 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_NAME';
2432 l_trans_tbl(l_count).param_value := p_competence_name;
2433 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2434 --
2435 l_count := l_count + 1;
2436 l_trans_tbl(l_count).param_name := 'P_PROFICIENCY_LEVEL_ID';
2437 l_trans_tbl(l_count).param_value := x_prof_level_id;
2438 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2439 --
2440 l_count := l_count + 1;
2441 l_trans_tbl(l_count).param_name := 'P_STEP_VALUE';
2442 l_trans_tbl(l_count).param_value := x_step_value;
2443 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2444 --
2445 l_count := l_count + 1;
2446 l_trans_tbl(l_count).param_name := 'P_PREUPD_PROFICY_LVL_ID';
2447 l_trans_tbl(l_count).param_value := null;
2448 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2449 --
2450 l_count := l_count + 1;
2451 l_trans_tbl(l_count).param_name := 'P_EFF_DATE_FROM';
2452 l_trans_tbl(l_count).param_value := p_eff_date_from;
2453 l_trans_tbl(l_count).param_data_type := 'DATE';
2454 --
2455 l_count := l_count + 1;
2456 l_trans_tbl(l_count).param_name := 'P_SYSTEM_GENERATED';
2457 l_trans_tbl(l_count).param_value := 'N';
2458 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2459 --
2460 l_count := l_count + 1;
2461 l_trans_tbl(l_count).param_name := 'P_UPGRADED_FROM_REC_ID';
2462 l_trans_tbl(l_count).param_value := null;
2463 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2464 --
2465 l_count := l_count + 1;
2466 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ALIAS';
2467 l_trans_tbl(l_count).param_value := p_competence_alias ;
2468 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2469 l_count := l_count + 1;
2470 l_trans_tbl(l_count).param_name := 'P_CHANGE_MODE';
2471 l_trans_tbl(l_count).param_value := p_change_mode;
2472 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2473 l_count := l_count + 1;
2474 l_trans_tbl(l_count).param_name := 'P_PREV_STEP_VALUE';
2475 l_trans_tbl(l_count).param_value := null;
2476 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2477 l_count := l_count + 1;
2478 l_trans_tbl(l_count).param_name := 'P_PREV_START_DATE';
2479 l_trans_tbl(l_count).param_value := null;
2480 l_trans_tbl(l_count).param_data_type := 'DATE';
2481 -- Start for Competence Qualification link enhancement
2482 l_count := l_count + 1;
2483 l_trans_tbl(l_count).param_name := 'P_PREV_STATUS';
2484 l_trans_tbl(l_count).param_value := null;
2485 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2486 l_count := l_count + 1;
2487 l_trans_tbl(l_count).param_name := 'P_STATUS';
2488 l_trans_tbl(l_count).param_value := p_competence_status;
2489 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2490 -- End for Competence Qualification link enhancement
2491 save_transaction_step
2492 (p_item_type => p_item_type
2493 ,p_item_key => p_item_key
2494 ,p_actid => to_number(p_activity_id)
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;
2504 end write_add_transaction;
2505 --------------------------------------------
2506 -- to check valid proposed level entered manually by user on update main page
2507 procedure validate_updated_row
2508 (p_competence_id in varchar2
2509 ,p_step_value in varchar2
2510 ,p_person_id in varchar2
2511 ,p_eff_date_from in varchar2 default null
2512 ,r_step_value out nocopy varchar2
2513 ,r_new_prof_level out nocopy varchar2
2514 ,p_item_type in varchar2 default null
2515 ,p_item_key in varchar2 default null
2516 ,p_activity_id in varchar2 default null
2517 ,p_error_message out nocopy varchar2) is
2518 l_proc varchar2(200) := g_package || 'validate_updated_row';
2519 l_step_value varchar2(10):= Null;
2520 l_new_prof_level varchar2(10):= Null ;
2521 x_eff_date_from date;
2522 x_eff_date_to date;
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
2532 ,p_error_message => p_error_message);
2533 IF p_step_value IS NOT NULL Then
2534 select ratl.step_value,ratl.rating_level_id
2535 into l_step_value,l_new_prof_level
2536 from per_competences_vl cvl, per_rating_levels ratl
2537 where ((ratl.competence_id = cvl.competence_id and cvl.competence_id = p_competence_id)
2538 or (cvl.rating_scale_id = ratl.rating_scale_id and cvl.competence_id = p_competence_id))
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,
2548 hr_api_transaction_values C,
2549 hr_api_transaction_values D
2550 Where s.item_type = p_item_type
2551 and s.item_key = p_item_key
2552 and s.activity_id = nvl((p_activity_id),s.activity_id)
2553 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2554 AND c.transaction_step_id = s.transaction_step_id
2555 AND c.NAME = 'P_COMPETENCE_ID'
2556 AND c.number_value = p_competence_id
2557 AND a.transaction_step_id = s.transaction_step_id
2558 AND a.NAME = 'P_DATE_FROM'
2559 AND d.transaction_step_id = s.transaction_step_id
2560 AND d.NAME = 'P_PERSON_ID'
2561 AND d.number_value = p_person_id
2562 AND a.date_value < to_date(p_eff_date_from, g_date_format);
2563
2564 IF (l_count > 0) Then
2565 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2566 p_error_message => p_error_message,
2567 p_attr_name => 'PropStartDate',
2568 p_app_short_name => 'PER',
2569 P_SINGLE_ERROR_MESSAGE => 'HR_OUT_ACHVD_DT_INVL');
2570 return;
2571 END if;
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,
2581 p_attr_name => 'PropLevel',
2582 p_app_short_name => 'PER',
2583 P_SINGLE_ERROR_MESSAGE => 'HR_COMP_INV_LEVEL_SS');
2584 else
2585 Select count(*) INTO l_count
2586 FROM hr_api_transaction_steps S,
2587 hr_api_transaction_values A,
2588 hr_api_transaction_values C,
2589 hr_api_transaction_values D
2590 Where s.item_type = p_item_type
2591 and s.item_key = p_item_key
2592 and s.activity_id = nvl((p_activity_id),s.activity_id)
2593 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2594 AND c.transaction_step_id = s.transaction_step_id
2595 AND c.NAME = 'P_COMPETENCE_ID'
2596 AND c.number_value = p_competence_id
2597 AND a.transaction_step_id = s.transaction_step_id
2598 AND a.NAME = 'P_DATE_FROM'
2599 AND d.transaction_step_id = s.transaction_step_id
2600 AND d.NAME = 'P_PERSON_ID'
2601 AND d.number_value = p_person_id
2602 AND a.date_value < to_date(p_eff_date_from, g_date_format);
2603
2604 IF (l_count > 0) Then
2605 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
2606 p_error_message => p_error_message,
2607 p_attr_name => 'PropStartDate',
2608 p_app_short_name => 'PER',
2609 P_SINGLE_ERROR_MESSAGE => 'HR_OUT_ACHVD_DT_INVL');
2610 return;
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
2620 (p_competence_id in varchar2 default null
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);
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 --
2637 procedure set_parameters(
2638 p_competence_id in out nocopy varchar2
2639 ,p_competence_name in out nocopy varchar2
2640 ,p_competence_alias in out nocopy varchar2
2641 ,p_step_value in out nocopy varchar2
2642 ,p_prof_level_id in out nocopy varchar2
2643 ,p_business_group_id in varchar2
2644 -- bug 2946360 fix
2645 ,p_item_type in varchar2 default null
2646 ,p_item_key in varchar2 default null
2647 ,p_person_id in number default null
2648 ,p_dup_comp_not_exists out nocopy varchar2
2649 -- bug 2946360 fix
2650 -- bug fix 4136402
2651 ,p_eff_date_from in varchar2 default null
2652 ,p_eff_date_to in varchar2 default null
2653 ,p_activity_id in varchar2 default null
2654 -- bug fix 4136402
2655 ,p_error_message out nocopy long) is
2656 l_proc varchar2(200) := g_package || 'set_parameters';
2657 l_count number;
2658 Cursor get_comp_dates ( p_competence_id IN Number )is
2659 select to_date(date_from, g_date_format) start_date
2660 from per_competences_vl
2661 where competence_id = p_competence_id;
2662 --bug 2946360 Fix
2663 cursor c1 is
2664 select 'F' status
2665 from hr_api_transaction_steps s, hr_api_transaction_values a,
2666 hr_api_transaction_values b, hr_api_transaction_values d
2667 Where s.item_type = p_item_type
2668 and s.item_key = p_item_key
2669 and s.transaction_step_id = a.transaction_step_id
2670 and s.transaction_step_id = b.transaction_step_id
2671 and s.transaction_step_id = d.transaction_step_id
2672 and a.name = 'P_COMPETENCE_ID' and a.number_value = p_competence_id
2673 and b.name = 'P_PERSON_ID' and b.number_value = p_person_id
2674 and d.name = 'P_CHANGE_MODE' and d.varchar2_value <> 'ADD'
2675 union
2676 select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
2677 where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
2678 and ts.transaction_id = t.transaction_id
2679 and t.selected_person_id = p_person_id and t.status = 'Y'
2680 and exists (Select 'e' From hr_api_transaction_values c
2681 Where c.transaction_step_id = ts.transaction_step_id
2682 and c.name = 'P_COMPETENCE_ID'
2683 and c.number_value = p_competence_id)
2684 union
2685 Select 'F' status from per_competence_elements pce
2686 where pce.person_id = p_person_id
2687 and pce.type = 'PERSONAL'
2688 and trunc(sysdate) between nvl(pce.effective_date_from,sysdate)
2689 and nvl(pce.effective_date_to,sysdate)
2690 and pce.competence_id = p_competence_id;
2691 l_dup_comp_not_exists varchar(1);
2692 --bug 2946360 Fix
2693 l_comp_dates_cur get_comp_dates%RowType;
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;
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',
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
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);
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))
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
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
2749 or per_competences_vl.rating_scale_id = per_rating_levels.rating_scale_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',
2759 P_SINGLE_ERROR_MESSAGE => 'HR_COMP_INV_LEVEL_SS');
2760 raise g_invalid_entry;
2761 End;
2762
2763 --FOR bug fix 4136402
2764 End If;
2765 Select count(*) INTO l_count
2766 FROM hr_api_transaction_steps S,
2767 hr_api_transaction_values A,
2768 hr_api_transaction_values C,
2769 hr_api_transaction_values D
2770 Where s.item_type = p_item_type
2771 and s.item_key = p_item_key
2772 and s.activity_id = nvl((p_activity_id),s.activity_id)
2773 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2774 AND c.transaction_step_id = s.transaction_step_id
2775 AND c.NAME = 'P_COMPETENCE_ID'
2776 AND c.number_value = p_competence_id
2777 AND a.transaction_step_id = s.transaction_step_id
2778 AND a.NAME = 'P_DATE_FROM'
2779 AND d.transaction_step_id = s.transaction_step_id
2780 AND d.NAME = 'P_PERSON_ID'
2781 AND d.number_value = p_person_id
2782 AND a.date_value < to_date(p_eff_date_from, g_date_format);
2783
2784 IF (l_count > 0) Then
2785 p_error_message := 'HR_OUT_ACHVD_DT_INVL';
2786 return;
2787 END if;
2788
2789 IF (p_eff_date_to IS NOT null) then
2790 Select count(*) INTO l_count
2791 FROM hr_api_transaction_steps S,
2792 hr_api_transaction_values A,
2793 hr_api_transaction_values C,
2794 hr_api_transaction_values D
2795 Where s.item_type = p_item_type
2796 and s.item_key = p_item_key
2797 and s.activity_id = nvl((p_activity_id),s.activity_id)
2798 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2799 AND c.transaction_step_id = s.transaction_step_id
2800 AND c.NAME = 'P_COMPETENCE_ID'
2801 AND c.number_value = p_competence_id
2802 AND a.transaction_step_id = s.transaction_step_id
2803 AND a.NAME = 'P_DATE_TO'
2804 AND d.transaction_step_id = s.transaction_step_id
2805 AND d.NAME = 'P_PERSON_ID'
2806 AND d.number_value = p_person_id
2807 AND nvl(a.date_value,to_date(p_eff_date_to, g_date_format)) > to_date(p_eff_date_to, g_date_format);
2808
2809 IF (l_count > 0) Then
2810 p_error_message := 'HR_OUT_ACHVD_DT_INVL';
2811 return;
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
2821 hr_utility.set_location(l_proc,55);
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;
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);
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;
2844 end set_parameters;
2845 ------------------------------------------------
2846 ------------------------------------------------
2847 procedure set_upd_parameters
2848 (p_competence_id in varchar2 default null
2849 ,p_step_value in varchar2 default null) is
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
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);
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;
2875 end set_upd_parameters;
2876 ---------------------------------------------------------------
2877 Procedure delete_add_page
2878 (transaction_step_ids in varchar2) is
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);
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);
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;
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
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
2915 set val.varchar2_value = 'ADDITION'
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
2925 ,p_competence_id in varchar2 default null
2926 ,p_eff_date_from in varchar2 default null
2927 ,p_error_message out nocopy varchar2) is
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(
2937 p_error_message => p_error_message,
2938 p_attr_name => 'PropStartDate',
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)
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',
2955 --'HR_COMP_UPD_ERR_START_DATE_SS'
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;
2965 raise g_invalid_entry;
2966 end update_date_validate;
2967 -------------------------------
2968 procedure ex_comp_date_validation
2969 (p_person_id in varchar2
2970 ,p_competence_id in varchar2
2971 ,p_eff_date_from in varchar2) is
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;
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
2996 (p_item_type in varchar2
2997 ,p_item_key in varchar2
2998 ,p_activity_id in varchar2
2999 ,p_person_id in varchar2
3000 ,p_review_proc_call in varchar2) is
3001 l_proc varchar2(200) := g_package || 'write_proc_actid';
3002 cursor c1 is
3003 select transaction_step_id
3004 from hr_api_transaction_steps
3005 where item_type = p_item_type
3006 and item_key = p_item_key
3007 and activity_id = p_activity_id;
3008 l_step_values c1%rowtype;
3009 l_index number;
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);
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';
3023 l_trans_tbl(l_count).param_value := p_review_proc_call;
3024 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3025 --
3026 l_count := l_count + 1;
3027 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
3028 l_trans_tbl(l_count).param_value := p_activity_id;
3029 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3030 -- l_transaction_step_id := l_step_values.transaction_step_id;
3031 save_transaction_step
3032 (p_item_type => p_item_type
3033 ,p_item_key => p_item_key
3034 ,p_actid => to_number(p_activity_id)
3035 ,p_login_person_id => fnd_global.employee_id
3036 ,p_transaction_step_id => l_step_values.transaction_step_id
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
3046 ,p_person_id in number
3047 ,p_business_group_id in number default null
3048 ,p_change_mode in varchar2 default null
3049 ,p_competence_element_id in number default null
3050 ,p_preupd_obj_vers_num in number default null
3051 ,p_competence_id in number default null
3052 ,p_proficiency_level_id in number default null
3053 ,p_eff_date_from in varchar2 default null
3054 ,p_eff_date_to in varchar2 default null
3055 ,p_proficy_lvl_source in varchar2 default null
3056 ,p_certification_mthd in varchar2 default null
3057 ,p_certification_date in varchar2 default null
3058 ,p_next_certifctn_date in varchar2 default null
3059 ,p_competence_status IN VARCHAR2 DEFAULT null
3060 ,p_eff_date_from_date_type out nocopy date
3061 ,p_eff_date_to_date_type out nocopy date
3062 ,p_certifctn_date_type out nocopy date
3063 ,p_next_certifctn_date_type out nocopy date
3064 ,p_error_message out nocopy long) is
3065 --
3066 l_proc varchar2(200) := g_package || 'api_validate_competence_record';
3067 l_user_date_format varchar2(200):=g_date_format;
3068 l_date_error boolean default false;
3069 l_eff_date_from date default null;
3070 l_eff_date_to date default null;
3071 l_preupd_date_to date default null;
3072 l_new_competence_element_id number default null;
3073 l_new_obj_vers_num number default null;
3074 l_next_certification_date date default null;
3075 l_certification_date date 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
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',
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
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',
3105 p_app_short_name => 'PER',
3106 P_SINGLE_ERROR_MESSAGE => 'HR_51648_CEL_PER_DATES_OVLAP');
3107 else
3108 p_error_message := hr_java_conv_util_ss.get_formatted_error_message
3109 (p_error_message => p_error_message,
3110 p_attr_name => 'CurrStartDate',
3111 p_app_short_name => 'PER',
3112 P_SINGLE_ERROR_MESSAGE => SQLERRM ||' '||to_char(SQLCODE));
3113
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;
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',
3131 P_SINGLE_ERROR_MESSAGE => 'HR_WEB_CEL_DATES_INVL');
3132 l_date_error := true;
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
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',
3156 P_SINGLE_ERROR_MESSAGE => 'HR_WEB_CERTFCTN_METHOD_NULL');
3157 l_date_error := true;
3158 END IF;
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',
3168 P_SINGLE_ERROR_MESSAGE => SQLERRM ||' '||to_char(SQLCODE));
3169 l_date_error := true;
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;
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',
3187 P_SINGLE_ERROR_MESSAGE => SQLERRM ||' '||to_char(SQLCODE));
3188 l_date_error := true;
3189 END;
3190 --
3191 --------------------------------------------------------------------
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;
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
3209 ,p_competence_element_id => p_competence_element_id
3210 ,p_object_version_number => l_object_version_number
3211 ,p_effective_date_to => l_preupd_date_to
3212 ,p_status => p_competence_status
3213 ,p_effective_date => trunc(sysdate));
3214 per_cel_ins.ins(p_validate => false
3215 ,p_competence_element_id => l_new_competence_element_id
3216 ,p_business_group_id => p_business_group_id
3217 ,p_object_version_number => l_new_obj_vers_num
3218 ,p_type => 'PERSONAL'
3219 ,p_person_id => l_action_person_id
3220 ,p_competence_id => p_competence_id
3221 ,p_effective_date_from => l_eff_date_from
3222 ,p_effective_date_to => l_eff_date_to
3223 ,p_proficiency_level_id => p_proficiency_level_id
3224 ,p_source_of_proficiency_level => p_proficy_lvl_source
3225 ,p_certification_method => p_certification_mthd
3226 ,p_certification_date => l_certification_date
3227 ,p_next_certification_date => l_next_certification_date
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
3237 ,p_effective_date_from => l_eff_date_from
3238 ,p_effective_date_to => l_eff_date_to
3239 ,p_proficiency_level_id => p_proficiency_level_id
3240 ,p_source_of_proficiency_level => p_proficy_lvl_source
3241 ,p_certification_method => p_certification_mthd
3242 ,p_certification_date => l_certification_date
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
3252 ,p_type => 'PERSONAL'
3253 ,p_person_id => l_action_person_id
3254 ,p_competence_id => p_competence_id
3255 ,p_effective_date_from => l_eff_date_from
3256 ,p_effective_date_to => l_eff_date_to
3257 ,p_proficiency_level_id => p_proficiency_level_id
3258 ,p_source_of_proficiency_level => p_proficy_lvl_source
3259 ,p_certification_method => p_certification_mthd
3260 ,p_certification_date => l_certification_date
3261 ,p_next_certification_date => l_next_certification_date
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;
3271 --
3272 --
3273 <<finish_processing>>
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;
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;
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);
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);
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);
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'
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);
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);
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);
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) <=
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'
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'
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
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);
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'
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'
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'
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);
3409 END IF;
3410 hr_utility.set_location(' Leaving:' || l_proc,680);
3411 End api_validate_competence_record;
3412 --
3413 -- ------------------------------------------------------------------------
3414 -- ---------------------<check_if_cmptnce_rec_changed>---------------------
3415 -- ------------------------------------------------------------------------
3416 -- Purpose: This procedure will compare the values of the rec with the
3417 -- values before update.
3418 -- The caller has made sure that this procedure is called only on an
3419 -- update or upgrade to new proficiency level mode.
3420 -- IF the proficiency level is the same as pre-update value, it
3421 -- will set an output parm to true if the p_change_mode is
3422 -- upgrade a proficiency level.
3423 -- ------------------------------------------------------------------------
3424 Procedure check_if_cmptnce_rec_changed
3425 (p_competence_element_id in number
3426 ,p_competence_id in number
3427 ,p_proficiency_level_id in number default null
3428 ,p_eff_date_from in date default null
3429 ,p_eff_date_to in date default null
3430 ,p_proficy_lvl_source in varchar2 default null
3431 ,p_certification_mthd in varchar2 default null
3432 ,p_certification_date in date default null
3433 ,p_next_certifctn_date in date default null
3434 ,p_change_mode in varchar2
3435 ,p_ignore_warning in varchar2 default null
3436 ,p_comments in varchar2 default null
3437 ,p_competence_status in varchar2 default null
3438 ,p_rec_changed out nocopy boolean) is
3439 --
3440 l_proc varchar2(200) := g_package || 'check_if_cmptnce_rec_changed';
3441 cursor csr_get_preupd_cmptnce_rec is
3442 select competence_element_id
3443 ,competence_id
3444 ,proficiency_level_id
3445 ,effective_date_from
3446 ,effective_date_to
3447 ,certification_date
3448 ,certification_method
3449 ,next_certification_date
3450 ,source_of_proficiency_level
3451 ,comments
3452 ,status
3453 from per_competence_elements
3454 where competence_element_id = p_competence_element_id;
3455 --
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);
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
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
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;
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 --
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 ---------------------------------------------------------------------
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
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;
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;
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 --
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;
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 --
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;
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 --
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;
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 --
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;
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 */
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;
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 */
3626 END LOOP;
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;
3636 --
3637 End check_if_cmptnce_rec_changed;
3638 --
3639 Procedure get_correction_trans_values
3640 (p_item_type in varchar2
3641 ,p_item_key in varchar2
3642 ,p_competence_element_id in number
3643 ,p_proficiency_level_id out nocopy number
3644 ,p_start_date out nocopy date
3645 ,p_end_date out nocopy date
3646 ,p_justification out nocopy varchar2
3647 ,p_acquired_by out nocopy varchar2
3648 ,p_measured_by out nocopy varchar2
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 --
3658 -- ***** Start new code for bug 2719381 **************
3659 /*==============================================================
3660 | PUBLIC function get_preferred_prof_range
3661 |
3662 | DESCRIPTION
3663 | This function will get the proficiency range for a given
3664 | competence and person id.
3665 |
3666 | PARAMETERS
3667 | p_person_id Person Id
3668 | p_competence_name Competence Name
3669 |
3670 | RETURNS
3671 | Proficiency Range
3672 |
3673 | MODIFICATION HISTORY
3674 | Date Author Description of Changes
3675 | 10-OCT-2001 Krmenon Created
3676 *==============================================================*/
3677 Function get_preferred_prof_range
3678 (p_person_id in varchar2
3679 ,p_competence_id in number) Return VARCHAR2 is
3680 l_proc varchar2(200) := g_package || 'get_preferred_prof_range';
3681 Cursor csr_bg_profrange is
3682 Select rl1.step_value || decode(rl1.name, '', '', ' ' || rl1.name) minprof,
3683 rl2.step_value || decode(rl2.name, '', '', ' ' || rl2.name) maxprof
3684 From per_competence_elements pce, per_all_assignments_f paaf,
3685 per_rating_levels rl1, per_rating_levels rl2
3686 Where paaf.person_id = p_person_id
3687 And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
3688 And pce.enterprise_id = paaf.business_group_id
3689 And pce.competence_id = p_competence_id
3690 And pce.type = 'REQUIREMENT'
3691 And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
3692 And proficiency_level_id = rl1.rating_level_id (+)
3693 And high_proficiency_level_id = rl2.rating_level_id (+);
3694 Cursor csr_job_profrange is
3695 Select rl1.step_value || decode(rl1.name, '', '', ', ' || rl1.name) minprof,
3696 rl2.step_value || decode(rl2.name, '', '', ', ' || rl2.name) maxprof
3697 From per_competence_elements pce, per_all_assignments_f paaf,
3698 per_rating_levels rl1, per_rating_levels rl2
3699 Where paaf.person_id = p_person_id
3700 And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
3701 And pce.job_id = paaf.job_id
3702 And pce.competence_id = p_competence_id
3703 And pce.type = 'REQUIREMENT'
3704 And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
3705 And proficiency_level_id = rl1.rating_level_id (+)
3706 And high_proficiency_level_id = rl2.rating_level_id (+);
3707 Cursor csr_pos_profrange is
3708 Select rl1.step_value || decode(rl1.name, '', '', ', ' || rl1.name) minprof,
3709 rl2.step_value || decode(rl2.name, '', '', ', ' || rl2.name) maxprof
3710 From per_competence_elements pce, per_all_assignments_f paaf,
3711 per_rating_levels rl1, per_rating_levels rl2
3712 Where paaf.person_id = p_person_id
3713 And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
3714 And pce.position_id = paaf.position_id
3715 And pce.competence_id = p_competence_id
3716 And pce.type = 'REQUIREMENT'
3717 And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
3718 And proficiency_level_id = rl1.rating_level_id (+)
3719 And high_proficiency_level_id = rl2.rating_level_id (+);
3720 Cursor csr_org_profrange is
3721 Select rl1.step_value || decode(rl1.name, '', '', ', ' || rl1.name) minprof,
3722 rl2.step_value || decode(rl2.name, '', '', ', ' || rl2.name) maxprof
3723 From per_competence_elements pce, per_all_assignments_f paaf,
3724 per_rating_levels rl1, per_rating_levels rl2
3725 Where paaf.person_id = p_person_id
3726 And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
3727 And pce.organization_id = paaf.organization_id
3728 And pce.competence_id = p_competence_id
3729 And pce.type = 'REQUIREMENT'
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;
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);
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);
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);
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);
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 --
3780 /*==============================================================
3781 | PUBLIC function is_proficiency_requred
3782 |
3783 | This function will check if the proficiency is a necessity
3784 |
3785 | PARAMETERS
3786 | p_person_id Person Id
3787 | p_competence_name Competence Name
3788 |
3789 | RETURNS
3790 | Y/N
3791 |
3792 | MODIFICATION HISTORY
3793 | Date Author Description of Changes
3794 | 10-OCT-2001 Krmenon Created
3795 *==============================================================*/
3796 Function is_proficiency_required
3797 (p_person_id in varchar2
3798 ,p_competence_id in number) Return VARCHAR2 is
3799 l_proc varchar2(200) := g_package || 'is_proficiency_required';
3800 /*------------------------------------------------------------+
3801 | Cursor to fetch the preffered proficiency range for a |
3802 | given person and competence. |
3803 +------------------------------------------------------------*/
3804 Cursor prof_rec is
3805 Select 'Y'
3806 From per_competence_elements pce, per_all_assignments_f paaf, per_competences pc
3807 Where paaf.person_id = p_person_id
3808 And trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
3809 And pc.competence_id = p_competence_id
3810 And pce.competence_id = pc.competence_id
3811 And pce.type = 'REQUIREMENT'
3812 And pce.mandatory = 'Y'
3813 And trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate)
3814 And (pce.job_id = paaf.job_id
3815 Or pce.organization_id = paaf.organization_id
3816 Or pce.position_id = paaf.position_id
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;
3826 End If;
3827 Open prof_rec;
3828 Fetch prof_rec Into l_rec;
3829 If prof_rec%NOTFOUND Then
3830 l_return := 'No';
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);
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;
3847 --
3848 End hr_comp_profile_ss;