[Home] [Help]
PACKAGE BODY: APPS.HR_COMP_OUTCOME_PROFILE_SS
Source
1 PACKAGE BODY HR_COMP_OUTCOME_PROFILE_SS AS
2 /* $Header: hrcorwrs.pkb 120.0 2005/05/30 23:23:03 appldev noship $ */
3 --
4 -- Private globals
5 g_package constant varchar2(30) := 'HR_COMP_OUTCOME_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 --
10 g_invalid_entry exception;
11 g_invalid_outcome exception;
12 --
13 --
14 cursor g_csr_get_preupd_cmpocm_rec(p_comp_element_outcome_id in number) is
15 select cvl.name
16 ,cmpe.COMP_ELEMENT_OUTCOME_ID
17 ,cmpe.object_version_number
18 ,cmpe.COMPETENCE_ELEMENT_ID
19 ,cmpe.OUTCOME_ID
20 ,cmpe.date_from
21 ,cmpe.date_to
22 from per_comp_element_outcomes cmpe
23 ,per_competence_outcomes_VL cvl
24 where cmpe.comp_element_outcome_id = p_comp_element_outcome_id
25 and cmpe.outcome_id = cvl.outcome_id
26 and trunc(sysdate) between nvl(cmpe.date_from, trunc(sysdate))
27 and nvl(cmpe.date_to, trunc(sysdate));
28 cursor get_out_name(p_outcome_id in number) is
29 select name
30 from per_competence_outcomes_vl
31 where outcome_id = p_outcome_id;
32 cursor get_prev_start_date(p_comp_element_outcome_id in number) is
33 select date_from
34 from per_comp_element_outcomes
35 where comp_element_outcome_id = p_comp_element_outcome_id;
36 cursor get_mode(p_transaction_step_id in number) is
37 select varchar2_value
38 from hr_api_transaction_values
39 where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
40 --
41 --
42 -- ------------------------------------------------------------------------
43 -- ---------------------<api_validate_compout_record>-------------------
44 -- ------------------------------------------------------------------------
45 -- Purpose: This private signature will validate data entered by calling api's
46 --
47 Procedure api_validate_compout_record
48 (p_validate in boolean default null
49 ,p_competence_element_id in number DEFAULT null
50 ,p_competence_id IN NUMBER DEFAULT null
51 ,p_outcome_id in number default null
52 ,p_change_mode in varchar2 default null
53 ,p_comp_element_outcome_id in number default null
54 ,p_preupd_obj_vers_num in number default null
55 ,p_date_from in varchar2 default null
56 ,p_date_to in varchar2 default null
57 ,p_date_from_date_type out nocopy date
58 ,p_date_to_date_type out nocopy date
59 ,p_person_id IN number
60 ,p_error_message out nocopy long);
61 --
62 --
63 -------------------<check_delete_rec>----------------------------
64 -- purpose: This will delete the delete mark rec in case it is
65 -- selected afterwords--
66 Procedure check_delete_rec(p_item_type IN varchar2
67 ,p_item_key IN varchar2
68 ,p_actid IN varchar2
69 ,p_person_id IN number
70 ,p_outcome_id IN number);
71 -- Purpose
72 --
73 Procedure process_upd_api
74 (p_comp_element_outcome_id IN number
75 ,p_to_date IN DATE
76 ,p_object_version_number IN number
77 ) ;
78 -- ------------------------------------------------------------------------
79 -- ---------------------<check_if_cmptnce_rec_changed>---------------------
80 -- ------------------------------------------------------------------------
81 -- Purpose: This private signature will compare the values of the rec with the
82 -- values before update.
83 -- The caller has made sure that this procedure is called only on an
84 -- update or upgrade to new proficiency level mode.
85 -- IF the proficiency level is the same as pre-update value, it
86 -- will set an output parm to true if the p_change_mode is
87 -- upgrade a proficiency level.
88 -- ------------------------------------------------------------------------
89 Procedure check_if_cmpocm_rec_changed
90 (p_comp_element_outcome_id in number
91 ,p_competence_element_id in number default null
92 ,p_competence_id in number default null
93 ,p_outcome_id in number
94 ,p_date_from in date default null
95 ,p_date_to in date default null
96 ,p_change_mode in varchar2
97 ,p_ignore_warning in varchar2 default null
98 ,p_rec_changed out nocopy boolean);
99 --
100 -- ---------------------------------------------------------------------------
101 --
102 --
103 -- ---------------------------- < writeTo_transTbl > -----------------------------
104 -- ---------------------------------------------------------------------------
105 -- Purpose: This private procedure to write into trans table
106 -- the transaction step is created if one doesn't exits
107 -- ---------------------------------------------------------------------------
108 --
109 Procedure writeTo_transTbl(
110 p_item_type IN wf_items.item_type%type
111 ,p_item_key IN wf_items.item_key%type
112 ,p_actid IN Number
113 ,p_login_person_id IN Number
114 ,p_trans_id IN Number
115 ,p_trans_step_id IN OUT NOCOPY Number
116 ,p_api_name IN Varchar2
117 ,p_comp_element_outcome_id IN Number Default Null
118 ,p_ovn IN Number Default Null
119 ,p_date_from IN Varchar2 Default Null
120 ,p_date_to IN Varchar2 Default Null
121 ,p_comp_element_id IN Number Default Null
122 ,p_outcome_id IN Varchar2 Default Null
123 ,p_outcome_name IN Varchar2 Default Null
124 ,p_change_mode IN Varchar2 Default Null
125 ,p_person_id IN VARCHAR2 DEFAULT null
126 ,p_sys_generated IN Varchar2 DEFAULT null
127 ,p_upg_from_rec_id IN Number Default 'N'
128 ,p_competence_id IN NUMBER DEFAULT null) is
129 --
130 l_trans_tbl transaction_table1;
131 l_count Number:=0;
132 x_trans_ovn Number;
133 l_review_region Varchar2(200);
134 l_comp_element_outcome_id per_comp_element_outcomes.comp_element_outcome_id%type;
135 l_proc varchar2(200);
136 Begin
137 l_proc := g_package || 'writeTo_transTbl';
138 hr_utility.set_location(' Entering:' || l_proc,5);
139 --
140 IF (p_comp_element_outcome_id = -1) Then
141 hr_utility.set_location(' Entering:' || l_proc,10);
142 l_comp_element_outcome_id := null;
143 else
144 hr_utility.set_location(' Entering:' || l_proc,15);
145 l_comp_element_outcome_id := p_comp_element_outcome_id;
146 END if;
147 hr_utility.set_location(l_proc,20);
148 --
149 If p_trans_step_id is null then
150 hr_utility.set_location(l_proc,25);
151 hr_transaction_api.create_transaction_step
152 (p_validate => false
153 ,p_creator_person_id => p_login_person_id
154 ,p_transaction_id => p_trans_id
155 ,p_api_name => p_api_name
156 ,p_item_type => p_item_type
157 ,p_item_key => p_item_key
158 ,p_activity_id => p_actid
159 ,p_transaction_step_id => p_trans_step_id
160 ,p_object_version_number => x_trans_ovn);
161 --
162 End if;
163 hr_utility.set_location(l_proc,30);
164 l_count := 1;
165 l_trans_tbl(l_count).param_name := 'P_COMP_ELEMENT_OUTCOME_ID';
166 l_trans_tbl(l_count).param_value := l_comp_element_outcome_id;
167 l_trans_tbl(l_count).param_data_type := 'NUMBER';
168 --
169 l_count := l_count + 1;
170 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
171 l_trans_tbl(l_count).param_value := p_ovn;
172 l_trans_tbl(l_count).param_data_type := 'NUMBER';
173 --
174 l_count := l_count + 1;
175 l_trans_tbl(l_count).param_name := 'P_DATE_FROM';
176 l_trans_tbl(l_count).param_value := p_date_from;
177 l_trans_tbl(l_count).param_data_type := 'DATE';
178 --
179 l_count := l_count + 1;
180 l_trans_tbl(l_count).param_name := 'P_OUTCOME_ID';
181 l_trans_tbl(l_count).param_value := p_outcome_id;
182 l_trans_tbl(l_count).param_data_type := 'NUMBER';
183 --
184 l_count := l_count + 1;
185 l_trans_tbl(l_count).param_name := 'P_OUTCOME_NAME';
186 l_trans_tbl(l_count).param_value := p_outcome_name;
187 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
188 --
189 l_count := l_count + 1;
190 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ELEMENT_ID';
191 l_trans_tbl(l_count).param_value := p_comp_element_id;
192 l_trans_tbl(l_count).param_data_type := 'NUMBER';
193 --
194 l_count := l_count + 1;
195 l_trans_tbl(l_count).param_name := 'P_DATE_TO';
196 l_trans_tbl(l_count).param_value := p_date_to;
197 l_trans_tbl(l_count).param_data_type := 'DATE';
198 --
199 l_count := l_count + 1;
200 l_trans_tbl(l_count).param_name := 'P_SYSTEM_GENERATED';
201 l_trans_tbl(l_count).param_value := p_sys_generated;
202 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
203 --
204 l_count := l_count + 1;
205 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
206 l_trans_tbl(l_count).param_value := p_person_id;
207 l_trans_tbl(l_count).param_data_type := 'NUMBER';
208 --
209 l_count := l_count + 1;
210 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ID';
211 l_trans_tbl(l_count).param_value := p_competence_id;
212 l_trans_tbl(l_count).param_data_type := 'NUMBER';
213 --
214 If p_upg_from_rec_id <> -1 then
215 hr_utility.set_location(l_proc,35);
216 l_count := l_count + 1;
217 l_trans_tbl(l_count).param_name := 'P_UPGRADED_FROM_REC_ID';
218 l_trans_tbl(l_count).param_value := p_upg_from_rec_id;
219 l_trans_tbl(l_count).param_data_type := 'NUMBER';
220 End if;
221 --
222 l_count := l_count + 1;
223 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
224 l_trans_tbl(l_count).param_value := p_actid;
225 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
226 --
227 l_review_region := wf_engine.GetActivityAttrText
228 (itemtype => p_item_type
229 ,itemkey => p_item_key
230 ,actid => p_actid
231 ,aname => 'HR_REVIEW_REGION_ITEM'
232 ,ignore_notfound => true);
233 --
234 l_count := l_count + 1;
235 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
236 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
237 l_trans_tbl(l_count).param_value := l_review_region;
238 hr_utility.set_location(l_proc,40);
239 save_transaction_step(p_item_type => p_item_type
240 ,p_item_key => p_item_key
241 ,p_actid => p_actid
242 ,p_login_person_id => p_login_person_id
243 ,p_transaction_step_id => p_trans_step_id
244 ,p_api_name => p_api_name
245 ,p_transaction_data => l_trans_tbl);
246 --
247 --
248 End writeTo_transTbl;
249 --
250 -- ---------------------------------------------------------------------------
251 -- ---------------------------- < comp_not_exists > --------------------------
252 -- ---------------------------------------------------------------------------
253 -- Purpose: This function is being used for filtering current session changes
254 -- and pending approval changes
255 -- ---------------------------------------------------------------------------
256 -- ---------------------------------------------------------------------------
257 -- ---------------------------- < process_save > -----------------------------
258 -- ---------------------------------------------------------------------------
259 -- Purpose: This private procedure saves the competence outcome record either
260 -- to the database or to the transaction table depending on the
261 -- workflow setting.
262 -- ---------------------------------------------------------------------------
263 --
264 Procedure process_save
265 (p_item_type in wf_items.item_type%type
266 ,p_item_key in wf_items.item_key%type
267 ,p_actid in varchar2
268 ,p_change_mode in varchar2 default null
269 ,p_comp_element_outcome_id in number default -1
270 ,p_competence_id IN NUMBER DEFAULT null
271 ,p_competence_element_id in number default null
272 ,p_preupd_obj_vers_num in number default null
273 ,p_outcome_id in number default null
274 ,p_outcome_name in varchar2 default null
275 ,p_date_from in varchar2 default null
276 ,p_date_to in varchar2 default null
277 ,p_prev_start_date in varchar2 default null
278 ,p_ignore_warning in varchar2 default null
279 ,p_transaction_step_id in out nocopy number
280 ,p_person_id IN number DEFAULT null
281 ,p_error_message out nocopy long) is
282 CURSOR getobjno (p_comp_element_outcome_id IN number) is
283 SELECT object_version_number FROM per_comp_element_outcomes
284 WHERE comp_element_outcome_id = p_comp_element_outcome_id;
285 --
286 l_transaction_id number default null;
287 l_transaction_step_id number default null;
288 l_result varchar2(100) default null;
289 l_user_date_format varchar2(200) default null;
290 x_date_from date default null;
291 x_date_to date default null;
292 l_preupd_cmpocm_row g_csr_get_preupd_cmpocm_rec%rowtype;
293 --
294 l_date_from date default null;
295 l_obj_ver_num number default null;
296 l_comp_element_outcome_id per_comp_element_outcomes.comp_element_outcome_id%type;
297 l_trans_obj_vers_num NUMBER DEFAULT null;
298 l_proc varchar2(200) ;
299 --
300 ----------------------------------
301 Begin
302 l_obj_ver_num := 1;
303 l_proc := g_package || 'process_save';
304 hr_utility.set_location(' Entering:' || l_proc,5);
305 l_user_date_format := g_date_format ;
306 l_date_from := to_date(p_date_from,l_user_date_format);
307 l_obj_ver_num := p_preupd_obj_vers_num;
308 --
309 IF p_comp_element_outcome_id IS NULL then
310 hr_utility.set_location(' Entering:' || l_proc,10);
311 l_comp_element_outcome_id := -1;
312 Else
313 l_comp_element_outcome_id := p_comp_element_outcome_id;
314 IF p_preupd_obj_vers_num IS NULL then
315 FOR ovn IN getobjno (p_comp_element_outcome_id => p_comp_element_outcome_id)
316 loop
317 l_obj_ver_num := ovn.object_version_number;
318 END loop;
319 END if;
320 END if;
321 hr_utility.set_location(' Entering:' || l_proc,20);
322 api_validate_compout_record
323 (p_validate => TRUE
324 ,p_competence_element_id => p_competence_element_id
328 ,p_preupd_obj_vers_num => l_obj_ver_num
325 ,p_outcome_id => p_outcome_id
326 ,p_change_mode => p_change_mode
327 ,p_comp_element_outcome_id => l_comp_element_outcome_id
329 ,p_date_from => p_date_from
330 ,p_date_to => p_date_to
331 ,p_date_from_date_type => x_date_from
332 ,p_date_to_date_type => x_date_to
333 ,p_person_id => p_person_id
334 ,p_error_message => p_error_message);
335 if p_error_message is not null then
336 Return;
337 end if;
338 hr_utility.set_location(' Entering:' || l_proc,25);
339 l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type => p_item_type
340 ,p_item_key => p_item_key);
341 --
342 IF l_transaction_id is null THEN
343 hr_utility.set_location(' Entering:' || l_proc,30);
344 -- Start a Transaction
345 hr_transaction_ss.start_transaction
346 (itemtype => p_item_type
347 ,itemkey => p_item_key
348 ,actid => to_number(p_actid)
349 ,funmode => 'RUN'
350 ,p_login_person_id => fnd_global.employee_id
351 ,result => l_result);
352 hr_utility.set_location(' Entering:' || l_proc,40);
353 l_transaction_id := hr_transaction_ss.get_transaction_id
354 (p_item_type => p_item_type
355 ,p_item_key => p_item_key);
356 END IF;
357 --
358 IF p_transaction_step_id IS NULL then
359 hr_utility.set_location(' Entering:' || l_proc,50);
360 hr_transaction_api.create_transaction_step
361 (p_validate => false
362 ,p_creator_person_id => fnd_global.employee_id
363 ,p_transaction_id => l_transaction_id
364 ,p_api_name => g_api_name
365 ,p_item_type => p_item_type
366 ,p_item_key => p_item_key
367 ,p_activity_id => to_number(p_actid)
368 ,p_transaction_step_id => p_transaction_step_id
369 ,p_object_version_number => l_trans_obj_vers_num);
370 end if;
371 --
372 hr_utility.set_location(' Entering:' || l_proc,60);
373 writeTo_transTbl (
374 p_item_type => p_item_type
375 ,p_item_key => p_item_key
376 ,p_actid => To_Number(p_actid)
377 ,p_login_person_id => fnd_global.employee_id
378 ,p_trans_id => l_transaction_id
379 ,p_trans_step_id => p_transaction_step_id
380 ,p_api_name => g_api_name
381 ,p_comp_element_outcome_id => l_comp_element_outcome_id
382 ,p_competence_id => p_competence_id
383 ,p_ovn => l_obj_ver_num
384 ,p_date_from => p_date_from
385 ,p_date_to => p_date_to
386 ,p_comp_element_id => p_competence_element_id
387 ,p_outcome_id => p_outcome_id
388 ,p_outcome_name => p_outcome_name
389 ,p_change_mode => p_change_mode
390 ,p_person_id => p_person_id
391 ,p_sys_generated => 'N'
392 ,p_upg_from_rec_id => l_comp_element_outcome_id);
393 --
394 EXCEPTION
395 When g_invalid_entry then
396 hr_utility.set_location(' Entering:' || l_proc,70);
397 raise g_invalid_entry;
398 --
399 WHEN hr_comp_outcome_profile_ss.g_data_err THEN
400 hr_utility.set_location(' Entering:' || l_proc,80);
401 raise hr_utility.hr_error;
402 --
403 WHEN hr_comp_outcome_profile_ss.g_access_violation_err THEN
404 hr_utility.set_location(' Entering:' || l_proc,90);
405 raise hr_utility.hr_error;
406 --
407 When others THEN
408 hr_utility.set_location(' Entering:' || l_proc,100);
409 raise g_invalid_entry ;
410 End process_save;
411 --
412 -- ---------------------------------------------------------------------------
413 --
414 Procedure call_process_api (
415 p_validate in boolean default false
416 ,p_competence_element_id IN number
417 ,p_new_competence_element_id IN number
418 ,p_competence_id IN number
419 ,p_item_type IN hr_api_transaction_steps.item_type%type
420 ,p_item_key IN hr_api_transaction_steps.item_key%type
421 ,p_activity_id IN hr_api_transaction_steps.ACTIVITY_ID%type
422 ,p_person_id IN Number
423 ,p_effective_date IN Date DEFAULT trunc(sysdate)) is
424 --
425 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
426 --
427 CURSOR get_txn_step_id (p_comp_id IN number, p_comp_ele_id IN number,
428 p_eff_date IN DATE DEFAULT null, p_person_id IN number) is
429 Select ts.transaction_step_id
430 FROM hr_api_transaction_steps ts,
431 hr_api_transaction_values tv,
432 hr_api_transaction_values tv1,
433 hr_api_transaction_values tv2,
434 hr_api_transaction_values tv3,
435 hr_api_transaction_values tv4
436 Where ts.transaction_step_id = tv.transaction_step_id
437 AND ts.item_type = p_item_type
438 AND ts.item_key = p_item_key
439 AND ts.activity_id = p_activity_id
440 AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
444 AND tv1.transaction_step_id(+) = ts.transaction_step_id
441 And tv4.transaction_step_id = ts.transaction_step_id
442 And tv4.NAME = 'P_PERSON_ID'
443 AND tv4.Number_Value = p_person_id
445 AND tv.NAME = 'P_COMPETENCE_ID'
446 AND tv.number_value = p_comp_id
447 AND tv1.NAME(+) = 'P_COMPETENCE_ELEMENT_ID'
448 AND tv1.number_value(+) = p_comp_ele_id
449 AND tv2.transaction_step_id = ts.transaction_step_id
450 AND tv2.NAME = 'P_DATE_FROM'
451 AND tv3.transaction_step_id = ts.transaction_step_id
452 AND tv3.NAME = 'P_DATE_TO';
453 -- AND nvl(p_eff_date,sysdate) BETWEEN tv2.date_value AND nvl(tv3.date_value,sysdate);
454 ---
455 Cursor get_enddate_outcome_ids(p_comp_ele_id IN number) is
456 Select ceo.comp_element_outcome_id ,ts.transaction_step_id,ceo.object_version_number
457 FROM
458 per_comp_element_outcomes ceo,
459 hr_api_transaction_steps ts,
460 hr_api_transaction_values tv
461 where
462 ceo.competence_element_id = p_comp_ele_id
463 AND ts.item_type = p_item_type
464 AND ts.item_key = p_item_key
465 AND ts.activity_id = p_activity_id
466 AND ts.transaction_step_id = tv.transaction_step_id
467 AND tv.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
468 AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
469 AND tv.number_value = ceo.COMP_ELEMENT_OUTCOME_ID;
470 --
471 Cursor get_unchngd_outcomes(p_comp_ele_id IN NUMBER ) is
472 Select co.outcome_id
473 ,ceo.DATE_FROM
474 ,ceo.DATE_TO
475 ,ceo.object_version_number
476 From per_Competence_Outcomes co
477 ,Per_comp_element_outcomes ceo
478 Where ceo.COMPETENCE_ELEMENT_ID = p_comp_ele_id
479 AND co.outcome_id = ceo.outcome_id
480 AND NOT EXISTS (Select 1 FROM hr_api_transaction_values tv1,
481 hr_api_transaction_values tv2,
482 hr_api_transaction_values tv3,
483 hr_api_transaction_values tv4,
484 hr_api_transaction_steps s
485 WHERE tv1.transaction_step_id = s.transaction_step_id
486 and s.item_type = p_item_type
487 and s.item_key = p_item_key
488 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
489 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
490 And tv2.transaction_step_id = s.transaction_step_id
491 And tv3.transaction_step_id = s.transaction_step_id
492 AND tv4.transaction_step_id = s.transaction_step_id
493 AND tv4.NAME = 'P_PERSON_ID'
494 AND tv4.number_value = p_person_id
495 AND tv1.name = 'P_OUTCOME_ID'
496 AND tv1.number_value = co.OUTCOME_ID
497 AND tv2.name = 'P_DATE_FROM'
498 AND tv3.name = 'P_DATE_TO'
499 AND tv2.date_Value >= co.date_from
500 AND nvl(tv3.date_Value,trunc(sysdate)) <= nvl(co.date_to,nvl(tv3.date_Value,trunc(sysdate)))
501 and not exists (
502 Select 1 from per_comp_element_outcomes pco
503 Where pco.competence_element_id = ceo.COMPETENCE_ELEMENT_ID
504 and pco.outcome_id = co.OUTCOME_ID
505 and pco.date_from = tv2.date_value
506 and nvl(pco.date_to,to_date('01-01-1001','DD-MM-YYYY')) = nvl(tv3.
507 date_value,to_date('01-01-1001','DD-MM-YYYY')))
508 )
509 And NOT EXISTS (SELECT 1
510 FROM hr_api_transaction_steps S1,
511 hr_api_transaction_values C
512 Where s1.item_type = p_item_type
513 and s1.item_key = p_item_key
514 and s1.activity_id = nvl((p_activity_id),s1.activity_id)
515 and s1.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
516 and c.transaction_step_id = s1.transaction_step_id
517 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
518 AND ceo.comp_element_outcome_id = C.number_value);
519 Cursor get_new_compele_dtls( p_new_comp_ele_id IN number) is
520 Select ce.Effective_date_From,ce.Effective_date_to
521 From per_competence_elements ce
522 Where ce.COMPETENCE_ELEMENT_ID = p_new_comp_ele_id;
523 /* Select ceo.outcome_id,ceo.date_from,ceo.date_to,
524 ce.Effective_date_From,ce.Effective_date_to, ceo.object_version_number
525 From per_comp_element_outcomes ceo,
526 per_competence_elements ce
527 Where ceo.competence_element_id = p_comp_ele_id
528 AND ce.competence_element_id = p_new_comp_ele_id;*/
529 --
530 TYPE new_compele_dtl_rec is RECORD (
531 Effective_date_From per_competence_elements.Effective_date_From%TYPE,
532 Effective_date_to per_competence_elements.Effective_date_to%TYPE
533 );
534 new_compele_dtl_record new_compele_dtl_rec;
535 l_count number;
536 l_Start_Date per_Comp_Element_Outcomes.Date_from%type;
537 l_End_Date per_Comp_Element_Outcomes.Date_to%type;
538 l_comp_ele_outcome_id per_Comp_Element_Outcomes.comp_Element_Outcome_id%type;
539 l_obj_ver_no number;
540 l_proc varchar2(200) ;
541 begin
542 -- hr_utility.trace_on(null,'COMPQUAL');
543 l_proc := g_package || ' call_process_api';
544 hr_utility.set_location(' Entering:' || l_proc,10);
545 --hr_utility.set_location(' p_validate:' || to_char(p_validate),10);
546 hr_utility.set_location(' p_competence_element_id:' || p_competence_element_id,10);
547 hr_utility.set_location(' p_new_competence_element_id:' || p_new_competence_element_id,10);
548 hr_utility.set_location(' p_competence_id:' || p_competence_id,10);
549 hr_utility.set_location(' p_item_type:' || p_item_type,10);
550 hr_utility.set_location(' p_item_key:' || p_item_key,10);
551 hr_utility.set_location(' p_activity_id:' || p_activity_id,10);
552 hr_utility.set_location(' p_person_id:' || p_person_id,10);
553 hr_utility.set_location(' p_effective_date:' || to_char(p_effective_date,'DD-MON-YYYY'),10);
557 FOR outids IN get_enddate_outcome_ids(p_competence_element_id)
554 savepoint save_comp_element_outcome;
555 l_count := 0;
556 --
558 loop
559 l_count := l_count +1;
560 process_upd_api(p_comp_element_outcome_id => outids.comp_element_outcome_id
561 ,p_to_date => trunc(sysdate)
562 ,p_object_version_number => outids.object_version_number );
563 delete_transaction_step_id(outids.transaction_step_id);
564 END loop;
565 hr_utility.set_location(' Entering:' || l_proc,20);
566 --
567 FOR txnids IN get_txn_step_id(
568 p_comp_id => p_competence_id
569 ,p_comp_ele_id => p_competence_element_id
570 ,p_person_id => p_person_id
571 ,p_eff_date => p_effective_date)
572 loop
573 hr_utility.set_location(' Entering:' || l_proc,30);
574 hr_utility.set_location(' p_new_competence_element_id:' || p_new_competence_element_id,30);
575 hr_utility.set_location(' p_effective_date:' || l_proc,30);
576 hr_utility.set_location(' p_transaction_step_id: ' || txnids.transaction_step_id,30);
577 HR_COMP_OUTCOME_PROFILE_SS.process_api(
578 p_validate => p_validate
579 ,p_competence_element_id => p_new_competence_element_id
580 ,p_effective_date => p_effective_date
581 ,p_transaction_step_id => txnids.transaction_step_id);
582 END loop;
583 hr_utility.set_location(' Entering:' || l_proc,40);
584 hr_utility.set_location(' p_competence_element_id: ' || p_competence_element_id,40);
585 hr_utility.set_location(' p_new_competence_element_id: ' || p_new_competence_element_id,40);
586 hr_utility.set_location(' l_count :' || l_count,40);
587 --
588 l_count := 0;
589 IF p_competence_element_id <> p_new_competence_element_id then
590 FOR unchangedRec IN get_unchngd_outcomes(p_competence_element_id)
591 loop
592 IF l_count = 0 then
593 l_count := 1;
594 OPEN get_new_compele_dtls(p_new_competence_element_id);
595 FETCH get_new_compele_dtls INTO new_compele_dtl_record;
596 IF get_new_compele_dtls%NOTFOUND then
597 CLOSE get_new_compele_dtls;
598 RAISE hr_comp_outcome_profile_ss.g_data_err;
599 else
600 CLOSE get_new_compele_dtls;
601 END if;
602 END if;
603 -- IF new_compele_dtl_record.Effective_date_From > unchangedRec.date_From Then
604 l_start_date := unchangedRec.date_From;
605 -- else
606 -- l_start_date := new_compele_dtl_record.Effective_date_From;
607 -- End if;
608 -- IF new_compele_dtl_record.Effective_date_TO IS NOT NULL Then
609 -- IF unchangedRec.date_to IS NOT NULL Then
610 -- IF new_compele_dtl_record.Effective_date_to > unchangedRec.date_to then
611 -- l_End_Date := unchangedRec.date_to;
612 -- Else
613 -- l_End_Date := new_compele_dtl_record.Effective_date_to;
614 -- End if;
615 -- Else
616 -- l_End_Date := new_compele_dtl_record.Effective_date_to;
617 -- End if;
618 -- Else
619 -- IF unchangedRec.date_to IS NOT NULL Then
620 l_End_Date := unchangedRec.date_to;
621 -- End if;
622 -- End if;
623 --
624 hr_utility.set_location(' Entering l_comp_ele_outcome_id:' || l_comp_ele_outcome_id,50);
625 hr_utility.set_location(' Entering p_new_competence_element_id:' || p_new_competence_element_id,50);
626 hr_utility.set_location(' Entering l_obj_ver_no:' || l_obj_ver_no,50);
627 hr_utility.set_location(' Entering unchangedRec.outcome_id:' || unchangedRec.outcome_id,50);
628 hr_utility.set_location(' Entering l_Start_Date:' || l_Start_Date,50);
629 hr_utility.set_location(' Entering l_End_Date:' || l_End_Date,50);
630 hr_utility.set_location(' Entering unchangedRec.date_From:' || unchangedRec.date_From,50);
631 hr_utility.set_location(' Entering unchangedRec.date_to:' || unchangedRec.date_to,50);
632 hr_utility.set_location(' Entering trunc(sysdate) :' || trunc(sysdate),50);
633 per_ceo_ins.ins(
634 p_comp_element_outcome_id => l_comp_ele_outcome_id
635 ,p_competence_element_id => p_new_competence_element_id
636 ,p_object_version_number => l_obj_ver_no
637 ,p_outcome_id => unchangedRec.outcome_id
638 ,p_date_from => unchangedRec.date_From
639 ,p_date_to => unchangedRec.date_to
640 ,p_effective_date => trunc(sysdate));
641 --
642 END loop;
643 hr_utility.set_location(' Entering:' || l_proc,80);
644 END if;
645 -- hr_utility.trace_off;
646 --
647 EXCEPTION
648 WHEN hr_comp_outcome_profile_ss.g_data_err THEN
649 hr_utility.set_location(' Entering:' || l_proc,90);
650 raise hr_utility.hr_error;
651 --
652 WHEN hr_comp_outcome_profile_ss.g_access_violation_err THEN
653 hr_utility.set_location(' Entering:' || l_proc,100);
654 raise hr_utility.hr_error;
655 --
656 When others THEN
657 hr_utility.set_location(' Entering:' || l_proc,110);
658 raise hr_utility.hr_error;
659 --
660 END call_process_api;
661 -- ---------------------------- < process_api > ------------------------------
662 -- ---------------------------------------------------------------------------
663 -- Purpose: This procedure reads the data from transaction table and saves it
664 -- to the database.
665 -- This procedure is called called from HR_COMP_PROFILE_SS.PROCESS_API
666 --
667 -- ---------------------------------------------------------------------------
668 Procedure process_api(p_validate in boolean default false
669 ,p_transaction_step_id in NUMBER
670 ,p_competence_element_id IN number
671 ,p_effective_date in varchar2 default null) is
672 --
673 Cursor getoldrec(p_comp_element_outcome_id IN number) is
674 Select COMPETENCE_ELEMENT_ID ,
675 OUTCOME_ID,
676 DATE_FROM,
677 DATE_TO
678 FROM per_comp_element_outcomes
682 l_upgraded_from_rec_id number DEFAULT Null;
679 Where comp_element_outcome_id = p_comp_element_outcome_id;
680 l_cmpout_element_rec per_comp_element_outcomes%rowtype;
681 l_system_generated varchar2(1) default null;
683 l_preupd_proficy_lvl_id number default NULL;
684 l_outcome_name per_competence_outcomes_vl.name%type;
685 l_txn_step_id hr_api_transaction_steps.transaction_step_id%type;
686 l_count number;
687 l_proc varchar2(200) ;
688 --
689 Begin
690 --
691 l_proc := g_package || 'process_api';
692 hr_utility.set_location(' p_transaction_step_id:' || p_transaction_step_id,10);
693 hr_utility.set_location(' p_competence_element_id:' || p_competence_element_id,10);
694 hr_utility.set_location(' Entering:' || l_proc,10);
695 hr_utility.set_location(' Entering:' || l_proc,10);
696 hr_utility.set_location(' Entering:' || l_proc,10);
697 hr_utility.set_location(' Entering:' || l_proc,10);
698 l_count := 0;
699 l_cmpout_element_rec.comp_element_outcome_id :=
700 hr_transaction_api.get_number_value
701 (p_transaction_step_id => p_transaction_step_id
702 ,p_name => 'P_COMP_ELEMENT_OUTCOME_ID');
703 --
704 l_cmpout_element_rec.competence_element_id :=
705 hr_transaction_api.get_number_value
706 (p_transaction_step_id => p_transaction_step_id
707 ,p_name => 'P_COMPETENCE_ELEMENT_ID');
708 --
709 l_cmpout_element_rec.object_version_number :=
710 hr_transaction_api.get_number_value
711 (p_transaction_step_id => p_transaction_step_id
712 ,p_name => 'P_OBJECT_VERSION_NUMBER');
713 --
714 l_cmpout_element_rec.outcome_id :=
715 hr_transaction_api.get_number_value
716 (p_transaction_step_id => p_transaction_step_id
717 ,p_name => 'P_OUTCOME_ID');
718 --
719 l_outcome_name :=
720 hr_transaction_api.get_varchar2_value
721 (p_transaction_step_id => p_transaction_step_id
722 ,p_name => 'P_OUTCOME_NAME');
723 --
724 l_cmpout_element_rec.date_from :=
725 hr_transaction_api.get_date_value
726 (p_transaction_step_id => p_transaction_step_id
727 ,p_name => 'P_DATE_FROM');
728 --
729 l_cmpout_element_rec.date_to :=
730 hr_transaction_api.get_date_value
731 (p_transaction_step_id => p_transaction_step_id
732 ,p_name => 'P_DATE_TO');
733 --
734 l_upgraded_from_rec_id := NULL;
735 l_upgraded_from_rec_id :=
736 hr_transaction_api.get_number_value
737 (p_transaction_step_id => p_transaction_step_id
738 ,p_name => 'P_UPGRADED_FROM_REC_ID');
739 --
740 /* IF l_system_generated = 'N' AND
741 l_upgraded_from_rec_id is not null THEN
742 hr_utility.set_location(' Entering:' || l_proc,20);
743 l_cmpout_element_rec.comp_element_outcome_id := null;
744 END IF;*/
745 --
746 -- set a savepoint before calling api
747 --
748 IF (l_cmpout_element_rec.comp_element_outcome_id IS NULL OR l_cmpout_element_rec.comp_element_outcome_id = -1) then
749 hr_utility.set_location(' Entering: Inserting new record' || l_proc,30);
753 hr_utility.set_location(' l_cmpout_element_rec.outcome_id :' || l_cmpout_element_rec.outcome_id ,30);
750 hr_utility.set_location(' l_cmpout_element_rec.comp_element_outcome_id:' || l_cmpout_element_rec.comp_element_outcome_id,30);
751 hr_utility.set_location(' l_cmpout_element_rec.object_version_number:' || l_cmpout_element_rec.object_version_number,30);
752 hr_utility.set_location(' l_cmpout_element_rec.date_to:' ||to_char(l_cmpout_element_rec.date_to,'DD-MON-YYYY'),30);
754 hr_utility.set_location(' l_cmpout_element_rec.date_from :' || l_cmpout_element_rec.date_from ,30);
755 hr_utility.set_location(' l_cmpout_element_rec.date_to :' || l_cmpout_element_rec.date_to ,30);
756 hr_utility.set_location(' l_upgraded_from_rec_id :' || l_upgraded_from_rec_id ,30);
757 per_ceo_ins.ins(
758 p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
759 ,p_competence_element_id => p_competence_element_id
760 ,p_object_version_number => l_cmpout_element_rec.object_version_number
761 ,p_outcome_id => l_cmpout_element_rec.outcome_id
762 ,p_date_from => l_cmpout_element_rec.date_from
763 ,p_date_to => l_cmpout_element_rec.date_to
764 ,p_effective_date => trunc(sysdate));
765 else
766 FOR rec IN getoldrec( p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id)
767 loop
768 IF rec.COMPETENCE_ELEMENT_ID= l_cmpout_element_rec.competence_element_id AND
769 rec.OUTCOME_ID = l_cmpout_element_rec.outcome_id and
770 rec.DATE_FROM = l_cmpout_element_rec.date_from and
771 ((rec.DATE_TO IS NULL AND l_cmpout_element_rec.date_to IS null) or
772 (rec.DATE_TO = l_cmpout_element_rec.date_to)) then
773 hr_utility.set_location(' Entering: in if record is same' || l_proc,30);
774 else
775 IF l_system_generated = 'Y' AND
776 l_cmpout_element_rec.competence_element_id = p_competence_element_id THEN
777 hr_utility.set_location(' Entering:' || l_proc,30);
778 hr_utility.set_location(' l_cmpout_element_rec.comp_element_outcome_id:' || l_cmpout_element_rec.comp_element_outcome_id,30);
779 hr_utility.set_location(' l_cmpout_element_rec.object_version_number:' || l_cmpout_element_rec.object_version_number,30);
780 hr_utility.set_location(' l_cmpout_element_rec.date_to:' ||to_char(l_cmpout_element_rec.date_to,'DD-MON-YYYY'),30);
781 hr_utility.set_location(' l_upgraded_from_rec_id :' || l_upgraded_from_rec_id ,30);
782 -- IF rec.DATE_FROM < l_cmpout_element_rec.date_from then
783 process_upd_api(p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
784 ,p_to_date => trunc(sysdate)
785 ,p_object_version_number => l_cmpout_element_rec.object_version_number);
786 per_ceo_ins.ins(
787 p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
788 ,p_competence_element_id => p_competence_element_id
789 ,p_object_version_number => l_cmpout_element_rec.object_version_number
790 ,p_outcome_id => l_cmpout_element_rec.outcome_id
791 ,p_date_from => l_cmpout_element_rec.date_from
792 ,p_date_to => l_cmpout_element_rec.date_to
793 ,p_effective_date => trunc(sysdate));
794 /* else
795 per_ceo_upd.upd (
796 p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
797 ,p_object_version_number => l_upgraded_from_rec_id
798 ,p_date_from => l_cmpout_element_rec.date_from
799 ,p_date_to => l_cmpout_element_rec.date_to
800 ,p_effective_date => trunc(sysdate));
801 END if;
802 --*/
803 ELSIF l_cmpout_element_rec.competence_element_id = p_competence_element_id THEN
804 hr_utility.set_location(' Entering:' || l_proc,40);
805 hr_utility.set_location(' l_cmpout_element_rec.comp_element_outcome_id:' || l_cmpout_element_rec.comp_element_outcome_id,30);
806 hr_utility.set_location(' l_cmpout_element_rec.object_version_number:' || l_cmpout_element_rec.object_version_number,30);
807 hr_utility.set_location(' l_cmpout_element_rec.date_to:' ||to_char(l_cmpout_element_rec.date_to,'DD-MON-YYYY'),30);
808 hr_utility.set_location(' l_cmpout_element_rec.date_from:' || l_cmpout_element_rec.date_from,30);
809 hr_utility.set_location(' l_upgraded_from_rec_id:' || l_upgraded_from_rec_id,30);
810 hr_utility.set_location(' rec.DATE_FROM' || rec.DATE_FROM,40);
811 hr_utility.set_location(' l_cmpout_element_rec.date_from' || l_cmpout_element_rec.date_from,40);
812 -- IF rec.DATE_FROM < l_cmpout_element_rec.date_from then
813 process_upd_api(p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
814 ,p_to_date => trunc(sysdate)
815 ,p_object_version_number => l_cmpout_element_rec.object_version_number);
816 per_ceo_ins.ins(
817 p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
818 ,p_competence_element_id => p_competence_element_id
819 ,p_object_version_number => l_cmpout_element_rec.object_version_number
820 ,p_outcome_id => l_cmpout_element_rec.outcome_id
821 ,p_date_from => l_cmpout_element_rec.date_from
822 ,p_date_to => l_cmpout_element_rec.date_to
823 ,p_effective_date => trunc(sysdate));
824 /* else
825 per_ceo_upd.upd
826 (p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
827 ,p_object_version_number => l_cmpout_element_rec.object_version_number
828 ,p_date_from => l_cmpout_element_rec.date_from
829 ,p_date_to => l_cmpout_element_rec.date_to
830 ,p_effective_date => trunc(sysdate));
831 END if;*/
832 --
833 ELSE
834 hr_utility.set_location(' Entering:' || l_proc,50);
835 per_ceo_ins.ins(
836 p_comp_element_outcome_id => l_cmpout_element_rec.comp_element_outcome_id
840 ,p_date_from => l_cmpout_element_rec.date_from
837 ,p_competence_element_id => p_competence_element_id
838 ,p_object_version_number => l_cmpout_element_rec.object_version_number
839 ,p_outcome_id => l_cmpout_element_rec.outcome_id
841 ,p_date_to => l_cmpout_element_rec.date_to
842 ,p_effective_date => trunc(sysdate));
843 END IF;
844 END if;
845 END loop;
846 END if;
847 --
848 --
849 Exception
850 When hr_utility.hr_error THEN
851 hr_utility.set_location(' Entering:' || l_proc,60);
852 rollback to save_comp_element_outcome;
853 IF NOT (l_upgraded_from_rec_id IS NOT NULL AND
854 hr_message.last_message_name = 'HR_51648_CEL_PER_DATES_OVLAP') THEN
855 hr_utility.set_location(' Entering:' || l_proc,70);
856 raise;
857 END IF;
858 --
859 When others THEN
860 hr_utility.set_location(' Entering:' || l_proc,80);
861 raise;
862 --
863 End process_api;
864 --
865 /* new Procedure for for updatng the old outcomes */
866 --
867 Procedure process_upd_api
868 (p_comp_element_outcome_id IN number
869 ,p_to_date IN DATE
870 ,p_object_version_number IN number
871 ) IS
872 --
873 l_object_version_number number;
874 x_to_date date;
875 l_proc varchar2(200) ;
876 begin
877 l_proc := g_package || 'process_upd_api';
878 hr_utility.set_location(' Entering:' || l_proc,10);
879 l_object_version_number := p_object_version_number;
880 IF p_to_date IS NULL then
881 x_to_date := trunc(sysdate);
882 else
883 x_to_date := p_to_date;
884 END if;
885 per_ceo_del.del (
886 p_comp_element_outcome_id => p_comp_element_outcome_id
887 ,p_object_version_number => l_object_version_number
888 );
889 --
890 END process_upd_api;
891 -----------new procedure for validating and saving record into transaction tables --------------------------
892 ------------------------------------------------------------------------------------------------------------
893 Procedure api_validate_com_out_rec_ss
894 (p_item_type in VARCHAR2
895 ,p_item_key in VARCHAR2
896 ,p_activity_id in varchar2
897 ,p_validate in varchar2
898 ,p_change_mode in varchar2 default null
899 ,p_comp_element_outcome_id in varchar2 default null
900 ,p_competence_element_id in varchar2 default null
901 ,p_competence_id in varchar2 default null
902 ,p_preupd_obj_vers_num in number default null
903 ,p_outcome_id in number default null
904 ,p_date_from in varchar2 default null
905 ,p_date_to in varchar2 default null
906 ,p_transaction_step_id in out nocopy varchar2
907 ,p_comp_from_date IN VARCHAR2 DEFAULT null
908 ,p_comp_to_date IN VARCHAR2 DEFAULT null
909 ,p_person_id IN VARCHAR2 DEFAULT null
910 ,p_error_message out nocopy long) is
911 --
912 l_user_date_format varchar2(200) default null;
913 l_user_date_format_length number default null;
914 l_sample_date varchar2(200) default null;
915 l_date_char varchar2(200) default null;
916 l_rec_changed boolean default null;
917 l_date_error boolean default null;
918 l_msg_text varchar2(2000) default null;
919 l_date_from date default null;
920 l_date_to date default null;
921 l_preupd_date_to date default null;
922 l_new_comp_ele_outcome_id number default null;
923 l_new_obj_vers_num number default null;
924 l_object_version_number number default null;
925 l_validate boolean default null;
926 --
927 o_name varchar2(200) default null;
928 p_outcome_name varchar2(200) default null;
929 l_prev_date date;
930 l_prev_start_date date;
931 l_changed boolean default false;
932 l_warning_exists boolean default false;
933 l_mode_fetch varchar2(20) default null;
934 l_mode varchar2(20) default null;
935 x_comp_ele_out_id number default null;
936 x_comp_ele_id number default null;
937 x_comp_id number default null;
938 x_count number;
939 --
940 Cursor get_out_dates ( p_outcome_id IN Number )is
941 select date_to end_date,
942 date_from start_date
943 from per_competence_outcomes_vl
944 where outcome_id = p_outcome_id;
945 --
946 l_out_dates_cur get_out_dates%RowType;
947 l_proc varchar2(200);
948 --
949 Begin
950 -- hr_utility.trace_on(null,'OUTCOME');
951 l_proc := g_package || 'api_validate_com_out_rec_ss';
952 hr_utility.set_location(' Entering:' || l_proc, 5);
953 IF (p_transaction_step_id = -1 ) then
954 hr_utility.set_location(' Entering:' || l_proc, 10);
955 p_transaction_step_id := Null;
956 ENd IF;
957 --
958 IF p_comp_element_outcome_id IS not null then
959 hr_utility.set_location(' Entering:' || l_proc,15);
960 x_comp_ele_out_Id := to_number(p_comp_element_outcome_id);
961 END if;
962 --
963 if p_competence_element_id IS not NULL then
964 hr_utility.set_location(' Entering:' || l_proc,20);
965 x_comp_ele_id := to_number(p_competence_element_id);
966 END if;
967 --
971 End if;
968 if p_competence_id IS not NULL then
969 hr_utility.set_location(' Entering:' || l_proc,25);
970 x_comp_id := to_number(p_competence_id);
972 l_mode := p_change_mode;
973 IF (p_comp_element_outcome_id is not NULL) THEN
974 hr_utility.set_location(' Entering:' || l_proc,30);
975 hr_comp_outcome_profile_ss.check_if_cmpocm_rec_changed
976 (p_comp_element_outcome_id => x_comp_ele_out_Id
977 ,p_competence_element_id => x_comp_ele_id
978 ,p_competence_id => x_comp_id
979 ,p_outcome_id => p_outcome_id
980 ,p_date_from => to_date(p_date_from,g_date_format)
981 ,p_date_to => to_date(p_date_to,g_date_format)
982 ,p_change_mode => p_change_mode
983 ,p_rec_changed => l_changed);
984 --
985 if l_changed = false THEN
986 hr_utility.set_location(' Entering:' || l_proc,35);
987 IF (p_transaction_step_id IS NOT Null) THEN
988 hr_utility.set_location(' Entering:' || l_proc,40);
989 delete_transaction_step_id(p_transaction_step_id);
990 END IF;
991 return; -- added on 17-Nov-2004
992 end if;
993 l_mode := HR_COMP_OUTCOME_PROFILE_SS.OUTCOME_CHANGED;
994 --
995 END IF;
996 --
997 IF ( p_date_from IS NULL OR to_date(p_date_from,g_date_format) IS null ) then
998 hr_utility.set_location(' Entering:' || l_proc,45);
999 p_error_message := 'HR_OUT_ELMT_DATE_INVL';
1000 --
1001 RETURN;
1002 --
1003 END if;
1004 hr_utility.set_location(' Entering:' || l_proc,50);
1005 IF ( to_date(p_date_from,g_date_format) > trunc(sysdate) ) then
1006 hr_utility.set_location(' Entering:' || l_proc,55);
1007 p_error_message := 'HR_OUT_DT_ACHVD_INVL';
1008 --
1009 RETURN;
1010 --
1011 END if;
1012 --
1013 OPEN get_out_dates(p_outcome_id => p_outcome_id);
1014 FETCH get_out_dates into l_out_dates_cur;
1015 IF get_out_dates%NOTFOUND THEN
1016 hr_utility.set_location(' Entering:' || l_proc,60);
1017 l_date_to := p_date_to;
1018 ELSE
1019 hr_utility.set_location(' Entering:' || l_proc,65);
1020 IF (l_out_dates_cur.start_date IS NOT NULL AND
1021 l_out_dates_cur.start_date > to_date(p_date_from, g_date_format) ) THEN
1022 hr_utility.set_location(' Entering:' || l_proc,70);
1023 p_error_message := 'HR_OUT_DATE_INVL';
1024 CLOSE get_out_dates;
1025 RETURN;
1026 END IF;
1027 IF p_date_to IS NULL THEN
1028 hr_utility.set_location(' Entering:' || l_proc,75);
1029 l_date_to := l_out_dates_cur.end_date;
1030 ELSIF (l_out_dates_cur.end_Date IS NOT NULL AND
1031 l_out_dates_cur.end_date < to_date(p_date_to, g_date_format) ) THEN
1032 p_error_message := 'HR_OUT_DATE_INVL';
1033 CLOSE get_out_dates;
1034 RETURN;
1035 ELSE
1036 hr_utility.set_location(' Entering:' || l_proc,80);
1037 l_date_to := to_date(p_date_to, g_date_format);
1038 END IF;
1039 END IF ;
1040 IF p_comp_from_date IS NOT NULL then
1041 hr_utility.set_location(' Entering:' || l_proc,85);
1042 IF to_date(p_date_from,g_date_format) < to_date(p_comp_from_date,g_date_format) Then
1043 hr_utility.set_location(' Entering:' || l_proc,90);
1044 p_error_message := 'HR_OUT_ACHVD_DT_INVL';
1045 RETURN;
1046 END if;
1047 --
1048 END if;
1049 --
1050 IF p_comp_to_date IS NOT NULL OR trim(p_comp_to_date) <> '' then
1051 hr_utility.set_location(' Entering:' || l_proc,95);
1052 IF to_date(p_date_to,g_date_format) > to_date(p_comp_to_date,g_date_format) Then
1053 hr_utility.set_location(' Entering:' || l_proc,100);
1054 p_error_message := 'HR_OUT_ACHVD_DT_INVL';
1055 RETURN;
1056 END if;
1057 --
1058 END if;
1059 --
1060 IF get_out_dates%ISOPEN then
1061 CLOSE get_out_dates;
1062 END if;
1063 hr_utility.set_location(' Entering:' || l_proc,105);
1064 hr_utility.set_location(' Entering x_comp_ele_id :' || x_comp_ele_id,105);
1065 hr_utility.set_location(' Entering p_outcome_id :' || p_outcome_id,105);
1066 hr_utility.set_location(' Entering l_date_to :' || l_date_to,105);
1067 hr_utility.set_location(' Entering g_date_format :' || g_date_format,105);
1068 hr_utility.set_location(' Entering p_date_from:' || p_date_from,105);
1069 x_count :=0;
1070 IF x_comp_ele_out_id IS NULL then
1071 Select count(*) INTO x_count
1072 from per_comp_element_outcomes ceo
1073 where competence_element_id = x_comp_ele_id
1074 and outcome_id = p_outcome_id and
1075 ceo.date_from <= to_date(p_date_from,g_date_format) and
1076 nvl(ceo.date_to,trunc(sysdate)) >= to_date(p_date_from,g_date_format)
1077 AND NOT exists(
1078 Select 1 FROM
1079 hr_api_transaction_steps S, hr_api_transaction_values C
1080 Where s.item_type = p_item_type
1081 and s.item_key = p_item_key
1082 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
1083 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
1084 and c.transaction_step_id = s.transaction_step_id
1088 IF x_count > 0 then
1085 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
1086 AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);
1087 hr_utility.set_location(' Entering x_count:' || x_count,107);
1089 hr_utility.set_location(' Entering:' || l_proc,107);
1090 -- changed for 4188407
1091 p_error_message := 'HR_449132_QUA_FWK_OUTCM_EXISTS';
1092 --End changes for 4188407
1093 return;
1094 END if;
1095 IF l_date_to IS NULL then
1096 Select count(*) INTO x_count
1097 from per_comp_element_outcomes ceo
1098 where competence_element_id = x_comp_ele_id
1099 and outcome_id = p_outcome_id and
1100 ceo.date_from >= to_date(p_date_from,g_date_format)
1101 AND NOT exists(
1102 Select 1 FROM
1103 hr_api_transaction_steps S, hr_api_transaction_values C
1104 Where s.item_type = p_item_type
1105 and s.item_key = p_item_key
1106 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
1107 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
1108 and c.transaction_step_id = s.transaction_step_id
1109 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
1110 AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);
1111 hr_utility.set_location(' Entering x_count:' || x_count,108);
1112 else
1113 Select count(*) INTO x_count
1114 from per_comp_element_outcomes ceo
1115 where competence_element_id = x_comp_ele_id
1116 and outcome_id = p_outcome_id and
1117 ceo.date_from >= to_date(p_date_from,g_date_format)
1118 AND ceo.date_from <= to_date(p_date_to,g_date_format)
1119 AND NOT exists(
1120 Select 1 FROM
1121 hr_api_transaction_steps S, hr_api_transaction_values C
1122 Where s.item_type = p_item_type
1123 and s.item_key = p_item_key
1124 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
1125 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
1126 and c.transaction_step_id = s.transaction_step_id
1127 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
1128 AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);
1129 hr_utility.set_location(' Entering x_count:' || x_count,109);
1130 END if; -- l_date_to IS NULL
1131 IF x_count > 0 then
1132 hr_utility.set_location(' Entering:' || l_proc,107);
1133 p_error_message := 'HR_OUT_DATE_INVL';
1134 return;
1135 END if;
1136 END IF; -- x_comp_ele_out_id IS NULL
1137 --
1138 if x_comp_ele_out_Id is not null then
1139 hr_utility.set_location(' Entering:' || l_proc,110);
1140 OPEN get_prev_start_date(x_comp_ele_out_Id);
1141 FETCH get_prev_start_date into l_prev_date;
1142 -- IF get_prev_start_date%notfound THEN
1143 -- raise hr_comp_outcome_profile_ss.g_fatal_error;
1144 -- END IF;
1145 close get_prev_start_date;
1146 l_prev_start_date := l_prev_date;
1147 --
1148 end if;
1149 if p_transaction_step_id is not null then
1150 hr_utility.set_location(' Entering:' || l_proc,120);
1151 delete_transaction_step_id(p_transaction_step_id);
1152 p_transaction_step_id := null;
1153 end if;
1154 hr_utility.set_location(' Entering:' || l_proc,130);
1155 process_save(p_item_type => p_item_type
1156 ,p_item_key => p_item_key
1157 ,p_actid => p_activity_id
1158 ,p_change_mode => l_mode
1159 ,p_comp_element_outcome_id => x_comp_ele_out_id
1160 ,p_competence_element_id => x_comp_ele_id
1161 ,p_competence_id => x_comp_id
1162 ,p_preupd_obj_vers_num => p_preupd_obj_vers_num
1163 ,p_outcome_id => p_outcome_id
1164 ,p_outcome_name => p_outcome_name
1165 ,p_date_from => p_date_from
1166 ,p_date_to => to_char(l_date_to,g_date_format)
1167 ,p_prev_start_date => to_char(l_prev_start_date,g_date_format)
1168 ,p_transaction_step_id => p_transaction_step_id
1169 ,p_person_id => p_person_id
1170 ,p_error_message => p_error_message);
1171 hr_utility.set_location(' Entering:' || l_proc,140);
1172 check_delete_rec(p_item_type => p_item_type
1173 ,p_item_key => p_item_key
1174 ,p_actid => p_activity_id
1175 ,p_person_id => p_person_id
1176 ,p_outcome_id => p_outcome_id);
1177 --
1178 Exception
1179 when g_invalid_entry then
1180 hr_utility.set_location(' Entering:' || l_proc,150);
1181 null;
1182 when others then
1183 hr_utility.set_location(' Entering:' || l_proc,160);
1184 raise g_invalid_entry;
1185 End api_validate_com_out_rec_ss;
1186 --
1187 -------------------------------------------------------------------------------
1188 /*Procedure get_pending_addition_ids
1189 (p_item_type IN varchar2
1190 ,p_item_key IN varchar2
1191 ,p_step_values out nocopy varchar2
1192 ,p_rows out nocopy number) is
1193 cursor get_add_ids (p_transaction_id number) is
1194 select step.transaction_step_id
1195 from hr_api_transaction_steps step, hr_api_transaction_values val
1196 where step.transaction_id = p_transaction_id
1197 and val.transaction_step_id = step.transaction_step_id
1198 and val.varchar2_value = 'ADD';
1199 l_index number;
1200 l_transaction_id number;
1201 begin
1202 l_transaction_id:=hr_transaction_ss.get_transaction_id
1203 (p_item_type => p_item_type
1204 ,p_item_key => p_item_key);
1205 l_index := 0;
1206 for l_step_values in get_add_ids(p_transaction_id => l_transaction_id) loop
1207 p_step_values := p_step_values || l_step_values.transaction_step_id || '?';
1208 l_index := l_index + 1;
1209 end loop ;
1210 p_rows := l_index;
1211 end get_pending_addition_ids;
1212 */
1213 --------------DELETE PENDING CURRENT UPDATE IDS ----------------------------------------------
1214 /*Procedure del_pen_currupd_ids(p_item_type IN varchar2
1215 ,p_item_key IN varchar2) is
1216 cursor get_upd_ids(p_transaction_id number) is
1217 select steps.transaction_step_id
1218 from hr_api_transaction_values val, hr_api_transaction_steps steps
1219 where steps.transaction_id = p_transaction_id
1220 and steps.transaction_step_id = val.transaction_step_id
1221 and val.name = 'P_CHANGE_MODE'
1222 and val.varchar2_value IN ('UPDATE_APPLY','UPDATE_HIST');
1223 l_step_values number ;
1224 l_transaction_id number;
1225 begin
1226 l_transaction_id:=hr_transaction_ss.get_transaction_id
1227 (p_item_type => p_item_type
1228 ,p_item_key => p_item_key);
1229 for I in get_upd_ids(l_transaction_id) loop
1230 delete from hr_api_transaction_values
1231 where transaction_step_id = I.transaction_step_id;
1232 delete from hr_api_transaction_steps
1233 where transaction_step_id = I.transaction_step_id;
1234 end loop;
1235 del_add_ids(p_item_type, p_item_key);
1236 commit;
1237 end del_pen_currupd_ids; */
1238 ----------------------------------------------------------------------------------------------------
1239 /*Procedure del_add_ids(p_item_type IN varchar2
1240 ,p_item_key IN varchar2) is
1241 cursor get_add_ids(p_transaction_id number) is
1242 select steps.transaction_step_id
1243 from hr_api_transaction_values val, hr_api_transaction_steps steps
1244 where steps.transaction_id = p_transaction_id
1245 and steps.transaction_step_id = val.transaction_step_id
1246 and val.varchar2_value = 'ADD';
1247 l_step_values number ;
1248 l_transaction_id number;
1249 begin
1250 l_transaction_id:=hr_transaction_ss.get_transaction_id
1251 (p_item_type => p_item_type
1252 ,p_item_key => p_item_key);
1253 for I in get_add_ids(l_transaction_id) loop
1254 delete from hr_api_transaction_values
1255 where transaction_step_id = I.transaction_step_id;
1256 delete from hr_api_transaction_steps
1257 where transaction_id = l_transaction_id
1258 and transaction_step_id = I.transaction_step_id;
1259 end loop ;
1260 end del_add_ids; */
1261 /*------------------------------------------------------------------------------
1265 | Purpose :
1262 |
1263 | Name : save_transaction_step
1264 |
1266 |
1267 | Saves the records into Transaction Tables.
1268 | Created as hr_transaction_ss.save_transaction_step fails when
1269 | value is passed in NULL
1270 |
1271 +-----------------------------------------------------------------------------*/
1272 PROCEDURE save_transaction_step
1273 (p_item_type IN VARCHAR2
1274 ,p_item_key IN VARCHAR2
1275 ,p_actid IN NUMBER
1276 ,p_login_person_id IN NUMBER
1277 ,p_transaction_step_id IN OUT NOCOPY NUMBER
1278 ,p_api_name IN VARCHAR2 default null
1279 ,p_api_display_name IN VARCHAR2 DEFAULT NULL
1280 ,p_transaction_data IN TRANSACTION_TABLE1) AS
1281 l_count Number:=0;
1282 l_proc varchar2(200);
1283 BEGIN
1284 l_proc := g_package || 'save_transaction_step';
1285 hr_utility.set_location(' Entering:' || l_proc,10);
1286 l_count := p_transaction_data.COUNT;
1287 FOR i IN 1..l_count LOOP
1288 BEGIN
1289 IF p_transaction_data(i).param_data_type = 'DATE' THEN
1290 hr_transaction_api.set_date_value(p_transaction_step_id => p_transaction_step_id
1291 ,p_person_id => p_login_person_id
1292 ,p_name => p_transaction_data(i).param_name
1293 ,p_value => to_date(ltrim(rtrim(p_transaction_data(i).param_value)),g_date_format));
1294 ELSIF p_transaction_data(i).param_data_type = 'NUMBER' THEN
1295 hr_transaction_api.set_number_value(p_transaction_step_id => p_transaction_step_id
1296 ,p_person_id => p_login_person_id
1297 ,p_name => p_transaction_data(i).param_name
1298 ,p_value => to_number(ltrim(rtrim(p_transaction_data(i).param_value))));
1299 ELSIF p_transaction_data(i).param_data_type = 'VARCHAR2' THEN
1300 hr_transaction_api.set_varchar2_value(p_transaction_step_id => p_transaction_step_id
1301 ,p_person_id => p_login_person_id
1302 ,p_name => p_transaction_data(i).param_name
1303 ,p_value => p_transaction_data(i).param_value);
1304 END IF;
1305 --
1306 Exception When others then
1307 hr_utility.set_location(' Entering:' || l_proc,100);
1308 RAISE hr_utility.hr_error;
1309 END;
1310 END LOOP;
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 hr_utility.set_location(' Entering:' || l_proc,110);
1314 hr_utility.trace('EXCEPTION SAVE_TRANSACTION_STEP'||'STS#');
1315 raise hr_utility.hr_error;
1316 END save_transaction_step;
1317 --
1318 -----------------------------------
1319 PROCEDURE delete_all_ids
1320 (p_item_type in varchar2
1321 ,p_item_key in varchar2) is
1322 cursor get_all_ids (p_transaction_id number) is
1323 select transaction_step_id
1324 from hr_api_transaction_steps
1325 where transaction_id = p_transaction_id;
1326 --
1327 l_result number ;
1328 l_transaction_id number ;
1329 begin
1330 --
1331 l_transaction_id:=hr_transaction_ss.get_transaction_id
1332 (p_item_type => p_item_type
1333 ,p_item_key => p_item_key);
1334 --
1335 for l_result in get_all_ids(p_transaction_id => l_transaction_id) loop
1336 delete from hr_api_transaction_values
1337 where transaction_step_id = l_result.transaction_step_id;
1338 --
1339 delete from hr_api_transaction_steps
1340 where transaction_id = l_transaction_id
1341 and transaction_step_id = l_result.transaction_step_id;
1342 end loop ;
1343 commit;
1344 --
1345 end delete_all_ids;
1346 --
1347 ------------------------------------------------
1348 PROCEDURE delete_transaction_step_id
1349 (p_transaction_step_id IN number) is
1350 l_transaction_step_id number;
1351 l_txid number;
1352 --
1353 l_mode varchar2(20) default null;
1354 l_comp_element_outcome_id number default null;
1355 l_transaction_id number;
1356 CURSOR get_txn_step_id IS select transaction_id
1357 from hr_api_transaction_steps
1358 where transaction_step_id = p_transaction_step_id
1359 and rownum = 1;
1360 BEGIN
1361 l_transaction_step_id := p_transaction_step_id;
1362 --
1363 FOR rec IN get_txn_step_id
1364 loop
1365 delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1366 delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1367 END loop;
1368 --
1369 END delete_transaction_step_id;
1370 --
1371 ----------------------------------------------------------------------------
1372 ---------------------------------------------------
1373 -- for saving fields from the update details page to tx tables
1374 procedure save_update_details
1375 (p_item_type in varchar2
1376 ,p_item_key in varchar2
1377 ,p_activity_id in varchar2
1378 ,p_outcome_id in number
1379 ,p_competence_element_id in number default null
1380 ,p_comp_element_outcome_id in number default null
1381 ,p_date_from in varchar2 default null
1382 ,p_date_to in varchar2 default null
1383 ,p_preupd_obj_vers_num in number default null
1384 ,p_transaction_step_id in number
1385 ,p_prev_date_from in varchar2 default null
1386 ,p_pre_date_to in varchar2 default null
1387 ,p_person_id IN VARCHAR2 DEFAULT null
1388 ,p_error_message out nocopy long) is
1392 l_date_from date default null;
1389 --
1390 --
1391 l_user_date_format varchar2(20) ;
1393 l_date_to date default null;
1394 l_prev_date date;
1395 l_prev_start_date date;
1396 l_date_error boolean default null;
1397 l_transaction_step_id number;
1398 --
1399 --------------
1400 l_object_version_number number default null;
1401 l_trans_tbl hr_comp_outcome_profile_ss.transaction_table1;
1402 l_count number default 0;
1403 l_action_person_id number default null;
1404 l_proc varchar2(200) ;
1405 -------------
1406 begin
1407 l_proc := g_package || 'save_update_details';
1408 hr_utility.set_location(' Entering:' || l_proc,10);
1409 l_user_date_format := g_date_format;
1410 l_transaction_step_id := p_transaction_step_id;
1411 --
1412 OPEN get_prev_start_date(p_comp_element_outcome_id => p_comp_element_outcome_id);
1413 FETCH get_prev_start_date into l_prev_date;
1414 IF get_prev_start_date%notfound THEN
1415 hr_utility.set_location(' Entering:' || l_proc,20);
1416 close get_out_name;
1417 raise hr_comp_outcome_profile_ss.g_fatal_error;
1418 ELSE
1419 hr_utility.set_location(' Entering:' || l_proc,30);
1420 CLOSE get_prev_start_date;
1421 END IF;
1422 --
1423 l_prev_start_date := l_prev_date;
1424 --
1425 hr_utility.set_location(' Entering:' || l_proc,40);
1426 api_validate_compout_record
1427 (p_validate => true
1428 ,p_competence_element_id => p_competence_element_id
1429 ,p_outcome_id => p_outcome_id
1430 ,p_change_mode => 'UPGRADE'
1431 ,p_comp_element_outcome_id => p_comp_element_outcome_id
1432 ,p_preupd_obj_vers_num => p_preupd_obj_vers_num
1433 ,p_date_from => p_date_from
1434 ,p_date_to => p_date_to
1435 ,p_date_from_date_type => l_date_from
1436 ,p_date_to_date_type => l_date_to
1437 ,p_person_id => p_person_id
1438 ,p_error_message => p_error_message);
1439 --
1440 IF p_error_message is not null then
1441 hr_utility.set_location(' Entering:' || l_proc,50);
1442 Return;
1443 END IF;
1444 hr_utility.set_location(' Entering:' || l_proc,60);
1445 --
1446 l_count := l_count + 1;
1447 l_trans_tbl(l_count).param_name := 'P_PREV_START_DATE';
1448 l_trans_tbl(l_count).param_value := to_char(l_prev_start_date,g_date_format);
1449 l_trans_tbl(l_count).param_data_type := 'DATE';
1450 --
1451 l_count := l_count + 1;
1452 l_trans_tbl(l_count).param_name := 'P_DATE_FROM';
1453 l_trans_tbl(l_count).param_value := p_date_from;
1454 l_trans_tbl(l_count).param_data_type := 'DATE';
1455 --
1456 l_count := l_count + 1;
1457 l_trans_tbl(l_count).param_name := 'P_DATE_TO';
1458 l_trans_tbl(l_count).param_value := p_date_to;
1459 l_trans_tbl(l_count).param_data_type := 'DATE';
1460 --
1461 --
1462 -- added for comp Qual enhancement RPahune
1463 l_count := l_count + 1;
1464 l_trans_tbl(l_count).param_name := 'P_COMPETENCE_ELEMENT_ID';
1465 l_trans_tbl(l_count).param_value := p_competence_element_id;
1466 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1467 --
1468 l_count := l_count + 1;
1469 l_trans_tbl(l_count).param_name := 'P_COMP_ELEMENT_OUTCOME_ID';
1470 l_trans_tbl(l_count).param_value := p_comp_element_outcome_id;
1471 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1472 --
1473 l_count := l_count + 1;
1474 l_trans_tbl(l_count).param_name := 'P_OUTCOME_ID';
1475 l_trans_tbl(l_count).param_value := p_outcome_id;
1476 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1477 --
1478 -- End add
1479 hr_utility.set_location(' Entering:' || l_proc,70);
1480 save_transaction_step
1481 (p_item_type => p_item_type
1482 ,p_item_key => p_item_key
1483 ,p_actid => to_number(p_activity_id)
1484 ,p_login_person_id => fnd_global.employee_id
1485 ,p_transaction_step_id => l_transaction_step_id
1486 ,p_transaction_data => l_trans_tbl);
1487 ------------------
1488 EXCEPTION
1489 when g_invalid_entry then
1490 hr_utility.set_location(' Entering:' || l_proc,80);
1491 raise g_invalid_entry;
1492 --
1493 when others then
1494 hr_utility.set_location(' Entering:' || l_proc,90);
1495 raise g_invalid_entry;
1496 --
1497 end save_update_details;
1498 --
1499 --------------------------------------------------------------------
1500 --
1501 ------------------------------------------------
1502 Procedure delete_add_page
1503 (p_transaction_step_id in number) is
1504 --
1505 CURSOR get_step_ids (txn_step_id IN number) is
1506 Select outcome.transaction_step_id
1507 FROM hr_api_transaction_steps outcome,
1508 hr_api_transaction_values ocomp,
1509 hr_api_transaction_values operson,
1510 hr_api_transaction_values ofrom_dt,
1511 hr_api_transaction_values oto_dt,
1512 hr_api_transaction_steps competence,
1513 hr_api_transaction_values ccomp,
1514 hr_api_transaction_values cperson,
1515 hr_api_transaction_values cfrom_dt,
1516 hr_api_transaction_values cto_dt
1517 Where competence.transaction_step_id = txn_step_id
1518 AND ccomp.transaction_step_id = competence.transaction_step_id
1519 AND cperson.transaction_step_id = competence.transaction_step_id
1520 AND cfrom_dt.transaction_step_id = competence.transaction_step_id
1521 AND cto_dt.transaction_step_id = competence.transaction_step_id
1522 AND ccomp.NAME = 'P_COMPETENCE_ID'
1523 AND cperson.NAME = 'P_PERSON_ID'
1524 AND cfrom_dt.NAME = 'P_EFF_DATE_FROM'
1525 AND cto_dt.NAME = 'P_EFF_DATE_TO'
1526 And outcome.item_key = competence.item_key
1527 And outcome.item_type = competence.item_type
1528 And outcome.activity_id = competence.activity_id
1529 And outcome.api_name = HR_COMP_OUTCOME_PROFILE_SS.g_api_name
1530 AND ocomp.transaction_step_id = competence.transaction_step_id
1531 AND operson.transaction_step_id = competence.transaction_step_id
1532 AND ofrom_dt.transaction_step_id = competence.transaction_step_id
1533 AND oto_dt.transaction_step_id = competence.transaction_step_id
1534 AND ocomp.NAME = 'P_COMPETENCE_ID'
1535 AND operson.NAME = 'P_PERSON_ID'
1536 AND ofrom_dt.NAME = 'P_EFF_DATE_FROM'
1537 AND oto_dt.NAME = 'P_EFF_DATE_TO'
1538 And ocomp.number_value = ccomp.number_value
1539 And operson.number_value = cperson.number_value
1540 AND ofrom_dt.date_value >= cfrom_dt.date_value
1541 AND nvl(oto_dt.date_value,trunc(sysdate)) <= nvl(cto_dt.date_value,trunc(sysdate));
1542 --
1543 l_proc varchar2(200);
1544 begin
1545 --
1546 l_proc := g_package || 'delete_add_page';
1547 hr_utility.set_location(' Entering:' || l_proc,10);
1548 FOR txnStepIds IN get_step_ids(p_transaction_step_id) loop
1549 --
1550 --
1551 delete from hr_api_transaction_values where transaction_step_id = txnStepIds.transaction_step_id;
1552 delete from hr_api_transaction_steps where transaction_step_id = txnStepIds.transaction_step_id;
1553 end loop;
1554 --
1555 commit;
1556 --
1557 end delete_add_page;
1558 ---------------------------------------------------------------
1559 --
1560 --
1561 --
1562 --====================================================================================
1563 Procedure api_validate_compout_record(
1564 p_validate in boolean default null
1565 ,p_competence_element_id in NUMBER DEFAULT null
1566 ,p_competence_id IN NUMBER DEFAULT null
1567 ,p_outcome_id in number default null
1568 ,p_change_mode in varchar2 default null
1569 ,p_comp_element_outcome_id in number default null
1570 ,p_preupd_obj_vers_num in number default null
1571 ,p_date_from in varchar2 default null
1572 ,p_date_to in varchar2 default null
1573 ,p_date_from_date_type out nocopy date
1574 ,p_date_to_date_type out nocopy date
1578 l_user_date_format varchar2(200);
1575 ,p_person_id IN number
1576 ,p_error_message out nocopy long) is
1577 --
1579 l_date_error boolean default false;
1580 l_date_from date default null;
1581 l_date_to date default null;
1582 l_new_comp_element_outcome_id number default null;
1583 l_new_obj_vers_num number default null;
1584 l_object_version_number number default null;
1585 l_proc varchar2(200);
1586 --
1587 Begin
1588 --
1589 l_proc := g_package || 'api_validate_compout_record';
1590 l_user_date_format :=g_date_format;
1591 Begin
1592 hr_utility.set_location(' Entering:' || l_proc,10);
1593 IF p_date_from is not null THEN
1594 hr_utility.set_location(' Entering:' || l_proc,20);
1595 l_date_from := to_date(p_date_from, l_user_date_format);
1596 END IF;
1597 ---
1598 IF l_date_from > trunc(sysdate) THEN
1599 hr_utility.set_location(' Entering:' || l_proc,30);
1600 p_error_message := 'HR_WEB_DISALLOW_FUTURE_STARTDT';
1601 l_date_error := true;
1602 END IF;
1603 --
1604 EXCEPTION
1605 When others then
1606 hr_utility.set_location(' Entering:' || l_proc,40);
1607 p_error_message := hr_java_conv_util_ss.get_formatted_error_message
1608 (p_error_message => p_error_message,
1609 p_attr_name => 'DateFrom',
1610 p_app_short_name => 'PER',
1611 P_SINGLE_ERROR_MESSAGE => SQLERRM ||' '||to_char(SQLCODE));
1612 l_date_error := true;
1613 END;
1614 --
1615 Begin
1616 --
1617 IF p_date_to is not null THEN
1618 hr_utility.set_location(' Entering:' || l_proc,50);
1619 l_date_to := to_date(p_date_to, l_user_date_format);
1620 END IF;
1621 --
1622 IF l_date_from is not null then
1623 hr_utility.set_location(' Entering:' || l_proc,60);
1624 IF l_date_to < l_date_from THEN
1625 hr_utility.set_location(' Entering:' || l_proc,70);
1626 p_error_message := 'HR_WEB_CEL_DATES_INVL';
1627 l_date_error := true;
1628 END IF;
1629 End IF;
1630 --
1631 EXCEPTION
1632 When others then
1633 hr_utility.set_location(' Entering:' || l_proc,80);
1634 p_error_message := hr_java_conv_util_ss.get_formatted_error_message
1635 (p_error_message => p_error_message,
1636 p_attr_name => 'EndDate',
1637 p_app_short_name => 'PER',
1638 P_SINGLE_ERROR_MESSAGE => SQLERRM ||' '||to_char(SQLCODE));
1639 l_date_error := true;
1640 END;
1641 --
1642 --
1643 --------------------------------------------------------------------
1644 -- Now, if no date format error, we proceed to call api for cross
1645 -- validations.
1646 --------------------------------------------------------------------
1647 --
1648 savepoint validate_output_rec;
1649 --
1650 IF l_date_error THEN
1651 hr_utility.set_location(' Entering:' || l_proc,90);
1652 goto finish_processing;
1653 END IF;
1654 --
1655 -- UPGRADE
1656 IF p_comp_element_outcome_id IS NOT null THEN
1657 hr_utility.set_location(' Entering:' || l_proc,100);
1658 l_object_version_number := p_preupd_obj_vers_num;
1659 per_ceo_upd.upd(p_comp_element_outcome_id => p_comp_element_outcome_id
1660 ,p_competence_element_id => p_competence_element_id
1661 ,p_outcome_id => p_outcome_id
1662 ,p_object_version_number => l_object_version_number
1663 ,p_date_from => l_date_from
1664 ,p_date_to => l_date_to
1665 ,p_effective_date => trunc(sysdate));
1666 --
1667 ELSE -- Add New Record Mode
1668 hr_utility.set_location(' Entering:' || l_proc,110);
1669 per_ceo_ins.ins(p_comp_element_outcome_id => l_new_comp_element_outcome_id
1670 ,p_competence_element_id => p_competence_element_id
1671 ,p_object_version_number => l_new_obj_vers_num
1672 ,p_outcome_id => p_outcome_id
1673 ,p_date_from => l_date_from
1674 ,p_date_to => l_date_to
1675 ,p_effective_date => trunc(sysdate));
1676 END IF;
1677 --
1678 IF p_validate = true THEN
1679 hr_utility.set_location(' Entering:' || l_proc,120);
1680 rollback to validate_output_rec;
1681 END IF;
1682 --
1683 --
1684 <<finish_processing>>
1685 --
1686 if l_date_error then
1687 hr_utility.set_location(' Entering:' || l_proc,130);
1688 rollback to validate_output_rec;
1689 end if;
1690 --
1691 p_date_from_date_type := l_date_from;
1692 p_date_to_date_type := l_date_to;
1693 --
1694 EXCEPTION
1695 When g_invalid_entry then
1696 hr_utility.set_location(' Entering:' || l_proc,140);
1697 rollback to validate_output_rec;
1698 When others then
1699 hr_utility.set_location(' Entering:' || l_proc,150);
1700 rollback to validate_output_rec;
1701 --
1702 End api_validate_compout_record;
1703 --
1704 -- ------------------------------------------------------------------------
1705 -- ---------------------<check_if_cmpocm_rec_changed>---------------------
1706 -- ------------------------------------------------------------------------
1707 -- Purpose: This procedure will compare the values of the rec with the
1708 -- values before update.
1712 -- ------------------------------------------------------------------------
1709 -- The caller has made sure that this procedure is called only on an
1710 -- IF the from date/ date to is changed, it
1711 -- will set an output parm to true if the p_change_mode is
1713 Procedure check_if_cmpocm_rec_changed (
1714 p_comp_element_outcome_id in number
1715 ,p_competence_element_id in number default null
1716 ,p_competence_id in number default null
1717 ,p_outcome_id in number
1718 ,p_date_from in date default null
1719 ,p_date_to in date default null
1720 ,p_change_mode in varchar2
1721 ,p_ignore_warning in varchar2 default null
1722 ,p_rec_changed out nocopy boolean) is
1723 --
1724 --
1725 cursor csr_get_preupd_cmpocm_rec is
1726 SELECT comp_element_outcome_id
1727 ,competence_element_id
1728 ,outcome_id
1729 ,date_from
1730 ,date_to
1731 from per_comp_element_outcomes
1732 where comp_element_outcome_id = p_comp_element_outcome_id;
1733 --
1734 l_changed boolean default null;
1735 l_proc varchar2(200);
1736 --
1737 --
1738 Begin
1739 l_proc := g_package || 'check_if_cmpocm_rec_changed';
1740 hr_utility.set_location(' Entering:' || l_proc,10);
1741 l_changed := false;
1742 --
1743 --
1744 FOR l_preupd_rec IN csr_get_preupd_cmpocm_rec LOOP
1745 hr_utility.set_location(' Entering:' || l_proc || ' in loop for ',20);
1746 IF l_preupd_rec.outcome_id = p_outcome_id AND l_preupd_rec.competence_element_id = p_competence_element_id THEN
1747 null;
1748 ELSE
1749 raise hr_comp_outcome_profile_ss.g_fatal_error;
1750 END IF;
1751 --
1752 --
1753 IF l_preupd_rec.date_from is not null THEN
1754 IF l_preupd_rec.date_from = p_date_from THEN
1755 null;
1756 ELSE
1757 l_changed := true;
1758 END IF;
1759 ELSE -- pre-update is null
1760 IF p_date_from is not null THEN
1761 l_changed := true;
1762 END IF;
1763 END IF;
1764 --
1765 IF l_preupd_rec.date_to is not null THEN
1766 ---------------------------------------------------------------------
1767 -- Only issue a warning if the new eff_date_to is different and is
1768 -- not null.
1769 ---------------------------------------------------------------------
1770 IF l_preupd_rec.date_to = p_date_to THEN
1771 null;
1772 ELSE
1773 l_changed := true;
1774 --
1775 END IF;
1776 ELSE -- pre-update is null
1777 IF p_date_to is not null THEN
1778 l_changed := true;
1779 --
1780 END IF;
1781 END IF;
1782 --
1783 --
1784 END LOOP;
1785 hr_utility.set_location(' Entering:' || l_proc || ' out of loop ',90);
1786 --
1787 p_rec_changed := l_changed;
1788 -- p_warning_exists := l_warning_exists;
1789 --
1790 Exception
1791 When others then
1792 hr_utility.set_location(' Entering:' || l_proc ,100);
1793 raise;
1794 --
1795 End check_if_cmpocm_rec_changed;
1796 ----------------
1797 ----------------
1798 PROCEDURE mark_for_delete
1799 (p_item_type in varchar2
1800 ,p_item_key in varchar2
1801 ,p_activity_id in varchar2
1802 ,p_comp_element_outcome_id in number
1803 ,p_transaction_step_id in varchar2 default null
1804 ,p_error_message OUT nocopy long ) IS
1805 l_transaction_id NUMBER DEFAULT null;
1806 x_trans_ovn NUMBER DEFAULT null;
1807 l_count number;
1808 l_trans_tbl transaction_table1;
1809 l_result varchar2(100);
1810 l_transaction_step_id Number DEFAULT null;
1811 l_proc varchar2(200);
1812 begin
1813 l_proc := g_package || 'mark_for_delete';
1814 hr_utility.set_location(' Entering:' || l_proc ,10);
1815 l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type => p_item_type
1816 ,p_item_key => p_item_key);
1817 --
1818 IF l_transaction_id is null THEN
1819 hr_utility.set_location(' Entering:' || l_proc ,20);
1820 -- Start a Transaction
1821 hr_transaction_ss.start_transaction
1822 (itemtype => p_item_type
1823 ,itemkey => p_item_key
1824 ,actid => to_number(p_activity_id)
1825 ,funmode => 'RUN'
1826 ,p_login_person_id => fnd_global.employee_id
1827 ,result => l_result);
1828 hr_utility.set_location(' Entering:' || l_proc ,30);
1829 l_transaction_id := hr_transaction_ss.get_transaction_id
1830 (p_item_type => p_item_type
1831 ,p_item_key => p_item_key);
1832 END IF;
1833 If p_transaction_step_id is NULL OR p_transaction_step_id = '-1' then
1834 hr_utility.set_location(' Entering:' || l_proc ,40);
1835 hr_transaction_api.create_transaction_step
1836 (p_validate => false
1837 ,p_creator_person_id => fnd_global.employee_id
1838 ,p_transaction_id => l_transaction_id
1839 ,p_api_name => 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
1840 ,p_item_type => p_item_type
1841 ,p_item_key => p_item_key
1842 ,p_activity_id => p_activity_id
1843 ,p_transaction_step_id => l_transaction_step_id
1844 ,p_object_version_number => x_trans_ovn);
1845 else
1849 hr_utility.set_location(' Entering:' || l_proc ,60);
1846 hr_utility.set_location(' Entering:' || l_proc ,50);
1847 l_transaction_step_id := p_transaction_step_id;
1848 End if;
1850 l_count := 1;
1851 l_trans_tbl(l_count).param_name := 'P_COMP_ELEMENT_OUTCOME_ID';
1852 l_trans_tbl(l_count).param_value := p_comp_element_outcome_id;
1853 l_trans_tbl(l_count).param_data_type := 'NUMBER';
1854 save_transaction_step(p_item_type => p_item_type
1855 ,p_item_key => p_item_key
1856 ,p_actid => p_activity_id
1857 ,p_login_person_id => fnd_global.employee_id
1858 ,p_transaction_step_id => l_transaction_step_id
1859 ,p_api_name => 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
1860 ,p_transaction_data => l_trans_tbl);
1861 END mark_for_delete;
1862 ----------------------
1863 ---------------------
1864 ----------------------
1865 PROCEDURE check_delete_rec
1866 ( p_item_type IN varchar2
1867 ,p_item_key IN varchar2
1868 ,p_actid IN varchar2
1869 ,p_person_id IN number
1870 ,p_outcome_id IN number) IS
1871 l_txn_step_id hr_api_transaction_steps.transaction_step_id%type;
1872 l_proc varchar2(200);
1873 begin
1874 l_proc := g_package || 'check_delete_rec';
1875 hr_utility.set_location(' Entering:' || l_proc ,10);
1876 SELECT s.transaction_step_id INTO l_txn_step_id
1877 FROM hr_api_transaction_steps S,
1878 hr_api_transaction_values C,
1879 per_comp_element_outcomes ceo,
1880 per_competence_elements pce
1881 Where s.item_type = p_item_type
1882 and s.item_key = p_item_key
1883 and s.activity_id = nvl((p_actid),s.activity_id)
1884 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
1885 and c.transaction_step_id = s.transaction_step_id
1886 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
1887 AND ceo.outcome_id = p_outcome_id
1888 AND ceo.Competence_Element_id = pce.competence_element_id
1889 AND pce.PERSON_ID = p_person_id
1890 AND ceo.comp_element_outcome_id = C.number_value;
1891 IF l_txn_step_id IS NOT NULL then
1892 hr_utility.set_location(' Entering:' || l_proc ,20);
1893 delete_transaction_step_id(l_txn_step_id);
1894 END if;
1895 --
1896 EXCEPTION
1897 When NO_DATA_FOUND then
1898 hr_utility.set_location(' Entering:' || l_proc ,30);
1899 null;
1900 When OTHERS then
1901 hr_utility.set_location(' Entering:' || l_proc ,40);
1902 raise;
1903 /**
1904 --
1905 */
1906 --
1907 End check_delete_rec;
1908 -- Added on 17-Nov-2004
1909 Procedure check_if_cmptnce_rec_changed
1910 (p_item_type IN varchar2
1911 ,p_item_key IN varchar2
1912 ,p_activity_id IN varchar2
1913 ,p_pid in number
1914 ,p_competence_element_id in number
1915 ,p_competence_id in number
1916 ,p_rec_changed out nocopy boolean) is
1917 l_count number;
1918 l_proc varchar2(200);
1919 Begin
1920 p_rec_changed := false;
1921 l_proc := g_package || 'check_if_cmptnce_rec_changed';
1922 hr_utility.set_location(' Entering:' || l_proc ,10);
1923 hr_utility.set_location(' Entering p_item_type :' || p_item_type ,10);
1924 hr_utility.set_location(' Entering p_item_key :' || p_item_key ,10);
1925 hr_utility.set_location(' Entering p_activity_id :' || p_activity_id ,10);
1926 hr_utility.set_location(' Entering p_pid :' || p_pid ,10);
1927 hr_utility.set_location(' Entering p_competence_element_id :' || p_competence_element_id ,10);
1928 hr_utility.set_location(' Entering p_competence_id :' || p_competence_id ,10);
1929 Select Count(*) INTO l_count
1930 FROM hr_api_transaction_steps s, per_competence_outcomes_vl co,
1931 hr_api_transaction_values a, hr_api_transaction_values b,
1932 hr_api_transaction_values c, hr_api_transaction_values p
1933 Where s.item_type = p_item_type
1934 and s.item_key = p_item_key
1935 and s.activity_id = nvl(p_activity_id,s.activity_id)
1936 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
1937 and c.transaction_step_id = s.transaction_step_id
1938 AND CO.COMPETENCE_ID =p_competence_id
1939 AND co.date_from <= trunc(sysdate)
1940 AND nvl(co.date_to,trunc(sysdate)) >= trunc(sysdate)
1941 AND b.date_value >= co.date_from
1942 AND nvl(c.date_Value,trunc(sysdate)) <= nvl(co.date_to, nvl(c.date_Value,trunc(sysdate)))
1943 and a.name = 'P_OUTCOME_ID'
1944 and a.transaction_step_id = s.transaction_step_id
1945 and a.number_value= co.outcome_id
1946 and b.name = 'P_DATE_FROM'
1947 and b.transaction_step_id = s.transaction_step_id
1948 and c.name = 'P_DATE_TO'
1949 and p.transaction_step_id = s.transaction_step_id
1950 and p.name = 'P_PERSON_ID'
1951 and p.Number_Value = p_pid;
1952 IF l_count > 0 then
1953 hr_utility.set_location(' Entering:' || l_proc ,20);
1954 p_rec_changed := true;
1955 END if;
1956 hr_utility.set_location(' Entering:' || l_proc ,30);
1957 SELECT COUNT(*) INTO l_count
1958 FROM hr_api_transaction_steps S,
1959 hr_api_transaction_values C,
1960 per_comp_element_outcomes ceo
1961 Where s.item_type = p_item_type
1962 and s.item_key = p_item_key
1963 and s.activity_id = nvl((p_activity_id),s.activity_id)
1964 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
1965 and c.transaction_step_id = s.transaction_step_id
1966 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
1967 AND ceo.Competence_Element_id = p_competence_element_id
1968 AND ceo.comp_element_outcome_id = C.number_value;
1969 IF l_count > 0 then
1970 hr_utility.set_location(' Entering:' || l_proc ,40);
1971 p_rec_changed := true;
1972 END if;
1973 --
1974 EXCEPTION
1978 END check_if_cmptnce_rec_changed;
1975 When OTHERS then
1976 hr_utility.set_location(' Entering:' || l_proc ,50);
1977 raise;
1979 Procedure delete(p_validate in boolean default false
1980 ,p_transaction_step_id in number
1981 ,p_effective_date in varchar2 default null) is
1982 begin
1983 null;
1984 end;
1985 --
1986 --
1987 Procedure process_api(
1988 p_validate in boolean default false
1989 ,p_transaction_step_id in number
1990 ,p_effective_date in varchar2 default null) IS
1991 begin
1992 null;
1993 end;
1994
1995 Procedure del_correct_rec(
1996 p_item_type IN varchar2
1997 ,p_item_key IN varchar2
1998 ,p_activity_id IN varchar2
1999 ,p_competence_element_id in number) is
2000
2001 CURSOR get_del_tsid is
2002 Select DISTINCT s.transaction_step_id
2003 FROM hr_api_transaction_steps S,
2004 hr_api_transaction_values C,
2005 per_comp_element_outcomes ceo
2006 Where s.item_type = p_item_type
2007 and s.item_key = p_item_key
2008 and s.activity_id = nvl((p_activity_id),s.activity_id)
2009 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
2010 and c.transaction_step_id = s.transaction_step_id
2011 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
2012 AND c.number_value = ceo.comp_element_outcome_id
2013 AND ceo.competence_element_id = p_competence_element_id;
2014
2015 Cursor get_comp_sid is
2016 Select DISTINCT s.transaction_step_id
2017 FROM hr_api_transaction_steps S,
2018 hr_api_transaction_values C
2019 Where s.item_type = p_item_type
2020 and s.item_key = p_item_key
2021 and s.activity_id = nvl((p_activity_id),s.activity_id)
2022 and s.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
2023 AND c.transaction_step_id = s.transaction_step_id
2024 AND c.NAME = 'P_COMPETENCE_ELEMENT_ID'
2025 AND c.number_value = p_competence_element_id;
2026
2027 Cursor get_out_sid is
2028 Select DISTINCT s.transaction_step_id
2029 FROM hr_api_transaction_steps S,
2030 hr_api_transaction_values C
2031 Where s.item_type = p_item_type
2032 and s.item_key = p_item_key
2033 and s.activity_id = nvl((p_activity_id),s.activity_id)
2034 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2035 AND c.transaction_step_id = s.transaction_step_id
2036 AND c.NAME = 'P_COMPETENCE_ELEMENT_ID'
2037 AND c.number_value = p_competence_element_id;
2038 begin
2039 FOR sid_cur IN get_del_tsid
2040 loop
2041 delete_transaction_step_id(sid_cur.transaction_step_id);
2042 END loop;
2043
2044 FOR sid_comp_cur IN get_comp_sid
2045 loop
2046 delete_transaction_step_id(sid_comp_cur.transaction_step_id);
2047 END loop;
2048 FOR sid_out_cur IN get_out_sid
2049 loop
2050 delete_transaction_step_id(sid_out_cur.transaction_step_id);
2051 END loop;
2052 end;
2053 End hr_comp_outcome_profile_ss;