[Home] [Help]
PACKAGE BODY: APPS.HR_WPM_MASS_APR_PUSH
Source
1 Package Body HR_WPM_MASS_APR_PUSH as
2 /* $Header: pewpmaprpush.pkb 120.20.12010000.9 2008/12/02 08:29:16 rvagvala ship $ */
3 -- Package Variables
4 --
5
6 g_package VARCHAR2(33) := 'hr_wpm_mass_apr_push.';
7 g_debug BOOLEAN := hr_utility.debug_enabled;
8 g_num_errors NUMBER := 0;
9 g_max_errors NUMBER;
10
11 --
12 -- Proprietory debugging. Allows for concurrent request output, etc.
13 -- (see procedures "op").
14 --
15 g_dbg BOOLEAN := g_debug;
16 g_dbg_type NUMBER := g_NO_DEBUG;
17 g_log_level VARCHAR2(1) := g_REGULAR_LOG;
18 --
19 -- Package (private) constants.
20 --
21 LOGGING CONSTANT pay_action_parameters.parameter_name%TYPE := 'LOGGING';
22 MAX_ERRORS CONSTANT pay_action_parameters.parameter_name%TYPE := 'MAX_ERRORS_ALLOWED';
23 NEWLINE CONSTANT VARCHAR2(10) := fnd_global.newline;
24 TAB CONSTANT VARCHAR2(30) := fnd_global.tab;
25 --
26 g_error_txt VARCHAR2(32000);
27 g_cp_error_txt VARCHAR2(32000);
28 --
29 -- Private user-defined types.
30 --
31 -- Used for populating plan appraisal periods
32 TYPE g_plan_aprsl_pds_r IS RECORD (appraisal_period_id per_appraisal_periods.appraisal_period_id%TYPE,
33 appraisal_template_id per_appraisal_periods.appraisal_template_id%TYPE,
34 start_date per_appraisal_periods.start_date%TYPE,
35 end_date per_appraisal_periods.end_date%TYPE,
36 task_start_date per_appraisal_periods.task_start_date%TYPE,
37 task_end_date per_appraisal_periods.task_end_date%TYPE,
38 initiator_code per_appraisal_periods.initiator_code%TYPE,
39 appraisal_system_type per_appraisal_periods.appraisal_system_type%TYPE,
40 auto_conc_process per_appraisal_periods.auto_conc_process%TYPE,
41 days_before_task_st_dt per_appraisal_periods.days_before_task_st_dt%TYPE,
42 appraisal_assmt_status per_appraisal_periods.appraisal_assmt_status%TYPE,
43 appraisal_type per_appraisal_periods.appraisal_type%TYPE);
44
45 TYPE g_plan_aprsl_pds_t IS TABLE OF g_plan_aprsl_pds_r INDEX BY binary_integer;
46 g_plan_aprsl_pds_tbl g_plan_aprsl_pds_t;
47 --
48 TYPE g_boolean_t IS TABLE OF BOOLEAN INDEX BY binary_integer;
49 g_plan_pop_known_t g_boolean_t;
50 g_fetched_plan_member_index number;
51 --
52
53 TYPE scorecard_info is RECORD (
54 scorecard_id per_personal_scorecards.scorecard_id%TYPE,
55 assignment_id per_personal_scorecards.assignment_id%TYPE,
56 person_id per_personal_scorecards.person_id%TYPE,
57 scorecard_name per_personal_scorecards.scorecard_name%TYPE);
58
59 TYPE assignment_info is RECORD (
60 assignment_id per_all_assignments_f.assignment_id%TYPE,
61 business_group_id per_all_assignments_f.business_group_id%TYPE,
62 grade_id per_all_assignments_f.grade_id%TYPE,
63 position_id per_all_assignments_f.position_id%TYPE,
64 job_id per_all_assignments_f.job_id%TYPE,
65 org_id per_all_assignments_f.organization_id%TYPE,
66 supervisor_id per_all_assignments_f.supervisor_id%TYPE,
67 effective_state_date per_all_assignments_f.effective_start_date%TYPE,
68 empl_start_date per_all_people_f.effective_start_date%TYPE,
69 empl_end_date per_all_people_f.effective_end_date%TYPE,
70 person_id per_all_people_f.person_id%TYPE
71 );
72 --
73 -- Following is added with more details to be captured
74 -- done by tpapired for 115.20 version of this file
75 TYPE appraisal_templ_info is RECORD (
76 appraisal_template_id per_appraisal_templates.appraisal_template_id%TYPE,
77 assessment_type_id per_appraisal_templates.assessment_type_id%TYPE,
78 objective_asmnt_type_id per_appraisal_templates.objective_asmnt_type_id%TYPE,
79 business_group_id per_appraisal_templates.business_group_id%TYPE,
80 show_competency_ratings per_appraisal_templates.show_competency_ratings%TYPE,
81 show_objective_ratings per_appraisal_templates.show_objective_ratings%TYPE,
82 show_questionnaire_info per_appraisal_templates.show_questionnaire_info%TYPE,
83 show_participant_details per_appraisal_templates.show_participant_details%TYPE,
84 show_participant_ratings per_appraisal_templates.show_participant_ratings%TYPE,
85 show_participant_names per_appraisal_templates.show_participant_names%TYPE,
86 show_overall_ratings per_appraisal_templates.show_overall_ratings%TYPE,
87 disable_provide_feed per_appraisal_templates.provide_overall_feedback%TYPE, --Bug7393131
88 show_overall_comments per_appraisal_templates.show_overall_comments%TYPE
89 );
90
91 TYPE assess_comps_info is RECORD (
92 competence_id per_competence_elements.competence_id%TYPE,
93 competence_element_id per_competence_elements.competence_element_id%TYPE,
94 type per_competence_elements.type%TYPE,
95 parent_competence_element_id per_competence_elements.parent_competence_element_id%TYPE,
96 name per_competences_vl.name%TYPE,
97 rank number );
98
99 TYPE bus_rules_comps IS RECORD(
100 name per_competences_vl.name%TYPE,
101 competence_id per_competences.competence_id%TYPE,
102 competence_element_id per_competence_elements.competence_element_id%TYPE,
103 mandatory per_competence_elements.mandatory%TYPE,
104 proficiency_level_id per_competence_elements.proficiency_level_id%TYPE,
105 high_proficiency_level_id per_competence_elements.high_proficiency_level_id%TYPE,
106 organization_id per_competence_elements.organization_id%TYPE,
107 job_id per_competence_elements.job_id%TYPE,
108 position_id per_competence_elements.position_id%TYPE,
109 valid_grade_id per_competence_elements.valid_grade_id%TYPE,
110 business_group_id per_competence_elements.business_group_id%TYPE,
111 enterprise_id per_competence_elements.enterprise_id%TYPE,
112 structure_type hr_lookups.meaning%TYPE,
113 read_only_attr number,
114 detail_attr number,
115 competence_alias per_competences.competence_alias%TYPE,
116 global varchar2(1),
117 description per_competences.description%TYPE,
118 date_from per_competences.date_from%TYPE,
119 certification_required per_competences.certification_required%TYPE,
120 behavioural_indicator per_competences.behavioural_indicator%TYPE,
121 low_step_value per_rating_levels_vl.step_value%TYPE,
122 low_step_name per_rating_levels_vl.name%TYPE,
123 high_step_value per_rating_levels_vl.step_value%TYPE,
124 high_step_name per_rating_levels_vl.name%TYPE,
125 lookup_code hr_lookups.lookup_code%TYPE,
126 minimum_proficiency varchar2(100),
127 maximum_proficiency varchar2(100),
128 type per_competence_elements.type%TYPE);
129
130 TYPE sel_comp_tab IS TABLE OF bus_rules_comps INDEX BY BINARY_INTEGER ;
131
132 type competences_rc is RECORD (
133 competence_id per_competences.competence_id%TYPE );
134 type competences_tbl is TABLE OF competences_rc INDEX BY BINARY_INTEGER;
135
136 --
137 -- ----------------------------------------------------------------------------
138 -- |----------------------< initialize_logging >------------------------------|
139 -- ----------------------------------------------------------------------------
140 --
141 PROCEDURE initialize_logging
142 (p_action_parameter_group_id in number,
143 p_log_output in varchar2)
144 IS
145
146 --
147 -- Gets an action parameter value.
148 --
149 CURSOR csr_get_action_param
150 (p_parameter_name IN VARCHAR2) IS
151 SELECT pap.parameter_value
152 FROM pay_action_parameters pap
153 WHERE pap.parameter_name = p_parameter_name;
154
155 l_logging pay_action_parameters.parameter_value%TYPE;
156 l_max_errors pay_action_parameters.parameter_value%TYPE;
157 l_string VARCHAR2(500);
158
159 BEGIN
160
161 --
162 -- Reset the package globals.
163 --
164 g_errbuf := NULL;
165 g_retcode := SUCCESS;
166 g_max_errors := 0;
167 g_error_txt := '';
168
169 --
170 -- If the action parameter ID is passed in, the action param group
171 -- is set. Native dynamic PL/SQL is used to eliminate the
172 -- the dependency on the pay package procedure.
173 --
174 IF p_action_parameter_group_id IS NOT NULL THEN
175
176 l_string :=
177 'BEGIN
178 pay_core_utils.set_pap_group_id(p_pap_group_id => ' ||
179 to_char(p_action_parameter_group_id) || ');
180 END;';
181
182 EXECUTE IMMEDIATE l_string;
183
184 END IF;
185
186 --
187 IF (p_log_output = 'Y' and FND_GLOBAL.CONC_REQUEST_ID > 0) THEN
188
189 -- Call from concurrent program
190 g_dbg := True;
191 g_dbg_type := g_FND_LOG;
192
193 --
194 -- Get the Payroll Action logging parameter
195 --
196 OPEN csr_get_action_param (LOGGING);
197 FETCH csr_get_action_param INTO l_logging;
198 CLOSE csr_get_action_param;
199
200 --
201 -- If logging is set to General in Payroll Action parameters, enable debugging.
202 --
203 IF (instr(NVL(l_logging, 'N'), 'G') <> 0) THEN
204 g_log_level := g_DEBUG_LOG;
205 ELSE
206 g_log_level := g_REGULAR_LOG;
207 END IF;
208
209 ELSIF (p_log_output <> 'Y') THEN
210
211 -- Call from API
212 IF (g_debug) THEN
213 g_dbg := True;
214 g_dbg_type := g_PIPE;
215 g_log_level := g_DEBUG_LOG;
216 END IF;
217 END IF;
218
219 --
220 -- Set the max number of errors allowed.
221 --
222 OPEN csr_get_action_param (MAX_ERRORS);
223 FETCH csr_get_action_param INTO l_max_errors;
224 CLOSE csr_get_action_param;
225
226 g_max_errors := NVL(to_number(l_max_errors), 0);
227
228 END initialize_logging;
229
230
231 --
232 -- ----------------------------------------------------------------------------
233 -- |----------------------< op >----------------------------------------------|
234 -- ----------------------------------------------------------------------------
235 --
236 PROCEDURE op
237 (p_msg IN VARCHAR2
238 ,p_log_level IN NUMBER
239 ,p_location IN NUMBER default null
240 )
241 IS
242
243 l_msg VARCHAR2(32000) := p_msg;
244
245 BEGIN
246
247 IF (g_dbg_type IS NOT NULL AND p_msg IS NOT NULL and p_log_level <= g_log_level) THEN
248 --
249 -- Break the output into chunks of 70 characters.
250 --
251 WHILE LENGTH(l_msg) > 0
252 LOOP
253 IF g_dbg_type = g_PIPE OR g_debug THEN
254 IF p_location IS NOT NULL THEN
255 hr_utility.set_location(SUBSTR(l_msg, 1, 70), p_location);
256 ELSE
257 hr_utility.trace(SUBSTR(l_msg, 1, 70));
258 END IF;
259 ELSIF g_dbg_type = g_FND_LOG THEN
260 IF p_location IS NOT NULL THEN
261 fnd_file.put_line(FND_FILE.log, SUBSTR(l_msg, 1, 70)
262 ||', '||to_char(p_location));
263 ELSE
264 fnd_file.put_line(FND_FILE.log, SUBSTR(l_msg, 1, 70));
265 END IF;
266 END IF;
267
268 l_msg := SUBSTR(l_msg, 71);
269 END LOOP;
270 END IF;
271
272 END op;
273 --
274 -- ----------------------------------------------------------------------------
275 -- |-------------------------< chk_publishing_status >------------------------|
276 -- ----------------------------------------------------------------------------
277 -- {Start Of Comments}
278 --
279 -- Description:
280 -- Checks that the status code is a valid for Publish o Reverse Publish plan
281 -- action.
282 --
283 -- Prerequisites:
284 -- None.
285 --
286 -- In Arguments:
287 --
288 --
289 -- Post Success:
290 -- Processing continues if the status is valid.
291 --
292 -- Post Failure:
293 -- An application error is raised if the status code is not valid.
294 --
295 -- Access Status:
296 -- Internal Only.
297 --
298 -- {End Of Comments}
299 -- ----------------------------------------------------------------------------
300 procedure chk_publishing_status
301 (p_reverse_mode in varchar2
302 ,p_status_code in varchar2
303 ) is
304
305 -- Declare local variables
306 l_proc varchar2(72) := g_package||'chk_publishing_status';
307 e_status_check_failed exception;
308
309 Begin
310 --
311 IF g_dbg THEN op('Entering:'|| l_proc, g_REGULAR_LOG, 10); END IF;
312 --
313 IF (p_reverse_mode = 'N' and p_status_code NOT IN ('DRAFT', 'UPDATED','SUBMITTED','RESUBMITTED')) THEN
314
315 -- Set the message name, so that exception handler can get translated text
316 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 20); END IF;
317 fnd_message.set_name('PER','HR_50294_WPM_INV_PLAN_STS_PUB');
318 g_error_txt := nvl(fnd_message.get, 'HR_50294_WPM_INV_PLAN_STS_PUB');
319 raise e_status_check_failed;
320
321 ELSIF (p_reverse_mode <> 'N' and p_status_code NOT IN ('PUBLISHED')) THEN
322
323 -- Set the message name, so that exception handler can get translated text
324 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 30); END IF;
325 fnd_message.set_name('PER','HR_50295_WPM_INV_PLAN_STS_RPUB');
326 g_error_txt := nvl(fnd_message.get, 'HR_50295_WPM_INV_PLAN_STS_RPUB');
327 raise e_status_check_failed;
328
329 END IF;
330 --
331 IF g_dbg THEN op('Leaving:'||l_proc, g_REGULAR_LOG, 80); END IF;
332 --
333
334 exception
335 when others then
336 IF g_dbg THEN op('Leaving:'||l_proc, g_REGULAR_LOG, 90); END IF;
337 --
338 g_retcode := WARNING;
339 g_errbuf := g_error_txt;
340 IF g_dbg THEN op(g_error_txt, g_REGULAR_LOG); END IF;
341 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
342 RAISE;
343
344 End chk_publishing_status;
345 --
346 -- ----------------------------------------------------------------------------
347 -- |---------------------< populate_plan_apprsl_periods >---------------------|
348 -- ----------------------------------------------------------------------------
349 -- {Start Of Comments}
350 --
351 -- Description:
352 -- Populates the appraisal periods for a given plan.
353 --
354 -- Prerequisites:
355 -- None.
356 --
357 -- In Arguments:
358 --
359 --
360 -- Post Success:
361 -- Processing continues on successful population.
362 --
363 -- Post Failure:
364 -- An application error is raised if population fails.
365
366 --
367 -- Access Status:
368 -- Internal Use Only.
369 --
370 -- {End Of Comments}
371 -- ----------------------------------------------------------------------------
372 procedure populate_plan_apprsl_periods
373 (p_plan_id in number
374 ,p_appr_period_id in number
375 ) is
376
377 -- Declare local variables
378 l_proc varchar2(72) := g_package||'populate_plan_apprsl_periods';
379
380 -- Current scorecard objectives
381 CURSOR csr_plan_apprsl_pds IS
382 select appraisal_period_id, appraisal_template_id, start_date, end_date, task_start_date,
383 task_end_date, initiator_code, appraisal_system_type, auto_conc_process, days_before_task_st_dt,
384 appraisal_assmt_status, appraisal_type
385 from per_appraisal_periods pap
386 where pap.plan_id = p_plan_id
387 and pap.appraisal_period_id = p_appr_period_id;
388 --
389 Begin
390 --
391 IF g_dbg THEN op('Entering:'|| l_proc, g_REGULAR_LOG, 10); END IF;
392 --
393
394 --
395 -- Get the plan appraisal periods
396 --
397 OPEN csr_plan_apprsl_pds;
398 FETCH csr_plan_apprsl_pds BULK COLLECT INTO g_plan_aprsl_pds_tbl;
399 CLOSE csr_plan_apprsl_pds;
400
401 --
402 IF g_dbg THEN op('Leaving:'|| l_proc, g_REGULAR_LOG, 80); END IF;
403 --
404 exception
405 when others then
406 IF g_dbg THEN op('Leaving:'|| l_proc, g_REGULAR_LOG, 90); END IF;
407 --
408 fnd_message.set_name('PER','HR_50297_WPM_CP_ERROR');
409 g_cp_error_txt := nvl(fnd_message.get, 'HR_50297_WPM_CP_ERROR');
410 g_retcode := WARNING;
411 g_errbuf := g_cp_error_txt;
412 g_num_errors := g_num_errors + 1;
413 IF g_dbg THEN op(g_error_txt, g_REGULAR_LOG); END IF;
414 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
415
416 --
417 -- If the max number of errors has been exceeded, raise the error and
418 -- terminate processing of this plan.
419 --
420 IF g_num_errors > g_max_errors THEN
421 fnd_message.set_name('PER','HR_50298_WPM_MAX_ERRORS');
422 g_error_txt := nvl(fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
423 g_retcode := ERROR;
424 g_errbuf := g_error_txt;
425 RAISE;
426 END IF;
427
428 End populate_plan_apprsl_periods;
429 --
430
431 function get_appraisal_config_params(p_appr_initiator_code in per_appraisal_periods.initiator_code%TYPE,
432 p_function_id in out nocopy fnd_form_functions.function_id%TYPE,
433 p_function_name in out nocopy fnd_form_functions.function_name%TYPE,
434 p_func_parameters in out nocopy fnd_form_functions.parameters%TYPE,
435 p_appraisal_sys_type in out nocopy per_appraisals.appraisal_system_status%TYPE
436 )
437 return boolean
438 is
439 l_resp_id number;
440 l_appraisal_mgr_menu fnd_menus.menu_name%TYPE default null;
441 l_appraisal_empl_menu fnd_menus.menu_name%TYPE default null;
442 l_selected_menu fnd_menus.menu_name%TYPE default null;
443 l_function_id fnd_form_functions.function_id%TYPE;
444 l_function_name fnd_form_functions.function_name%TYPE;
445 l_initiation_type varchar2(10) default null;
446 l_func_params fnd_form_functions.parameters%TYPE;
447 l_system_type varchar2(50);
448 l_menu_id fnd_menus.menu_id%TYPE;
449
450
451 cursor get_appraisal_function(p_menu_name fnd_menus.menu_name%TYPE, p_search_func varchar2)
452 is
453 select menu_functions.function_id, ff.function_name, ff.parameters, menu_id from FND_COMPILED_MENU_FUNCTIONS menu_functions, fnd_form_functions ff
454 where menu_id = (select menu_id from fnd_menus where menu_name = p_menu_name)
455 and ff.function_id = menu_functions.function_id
456 and ff.parameters like p_search_func;
457
458 begin
459 -- to be derived from plan id
460
461 /*
462 fnd_global.apps_initialize(user_id =>1922,
463 resp_id =>21540,
464 resp_appl_id=> 800);
465 */
466
467 IF g_dbg THEN op('p_appr_initiator_code = ' || p_appr_initiator_code, g_DEBUG_LOG); END IF;
468 IF g_dbg THEN op('login person = ' || fnd_global.user_id, g_DEBUG_LOG); END IF;
469 IF g_dbg THEN op('login name = ' || fnd_global.user_name, g_DEBUG_LOG); END IF;
470 IF g_dbg THEN op('resp id = ' || fnd_global.resp_id, g_DEBUG_LOG); END IF;
471 IF g_dbg THEN op(' p_appraisal_sys_type = ' || p_appraisal_sys_type, g_DEBUG_LOG); END IF;
472
473
474 l_initiation_type := 'MGR';
475 l_resp_id := fnd_global.resp_id;
476 l_appraisal_mgr_menu := fnd_profile.value_specific(name => 'HR_MANAGER_APPRAISALS_MENU',
477 responsibility_id => l_resp_id);
478
479 IF g_dbg THEN op('MGR MENU = ' || fnd_profile.value('HR_MANAGER_APPRAISALS_MENU'), g_DEBUG_LOG); END IF;
480 IF g_dbg THEN op('EMP MENU = ' || fnd_profile.value('HR_WORKER_APPRAISALS_MENU'), g_DEBUG_LOG); END IF;
481
482
483 IF g_dbg THEN op('l_appraisal_mgr_menu = ' || l_appraisal_mgr_menu, g_DEBUG_LOG); END IF;
484
485 l_appraisal_empl_menu := fnd_profile.value_specific(name => 'HR_WORKER_APPRAISALS_MENU',
486 responsibility_id => l_resp_id);
487 IF g_dbg THEN op('l_appraisal_empl_menu = ' || l_appraisal_empl_menu, g_DEBUG_LOG); END IF;
488
489
490 if ( p_appr_initiator_code = 'MGR' and l_appraisal_mgr_menu is not null ) then
491 l_selected_menu := l_appraisal_mgr_menu;
492 p_appraisal_sys_type := p_appr_initiator_code || p_appraisal_sys_type;
493 l_system_type := '%' || p_appraisal_sys_type ||'%';
494 elsif ( p_appr_initiator_code = 'EMP' and l_appraisal_empl_menu is not null ) then
495 l_selected_menu := l_appraisal_empl_menu;
496 p_appraisal_sys_type := p_appr_initiator_code || p_appraisal_sys_type;
497 l_system_type := '%' || p_appraisal_sys_type ||'%';
498 end if;
499
500 IF g_dbg THEN op('l_selected_menu = ' || l_selected_menu, g_DEBUG_LOG); END IF;
501
502 open get_appraisal_function(l_selected_menu, l_system_type);
503 fetch get_appraisal_function into l_function_id, l_function_name, l_func_params,l_menu_id;
504 if get_appraisal_function%NOTFOUND then
505 return false;
506 else
507 p_function_id := l_function_id;
508 p_function_name := l_function_name;
509 p_func_parameters := l_func_params||'&'||'pFunctionId='||l_function_id||'&'||'pMenuId='||l_menu_id||'&'||'OAFunc='||l_function_name;
510 end if;
511
512 IF g_dbg THEN op('l_function_id = ' || l_function_id, g_DEBUG_LOG); END IF;
513 IF g_dbg THEN op('l_function_name = ' || l_function_name, g_DEBUG_LOG); END IF;
514
515
516 return true;
517
518 exception when others then
519 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
520
521 raise;
522 end;
523
524
525
526 procedure apply_overridding_rules(
527 p_enterprise_id in number
528 ,p_organization_id in number
529 ,p_job_id in number
530 ,p_position_id in number
531 ,p_skip_duplicate boolean
532 ,l_sel_comp_table in out nocopy sel_comp_tab
533 )
534 is
535 cursor get_asgn_req_comps(enter_price_id number,
536 p_in_org_id number,
537 p_in_job_id number,
538 p_in_pos_id number
539 )
540 is
541 SELECT pc.name competence_name,
542 pce.competence_id,
543 pce.competence_element_id,
544 pce.mandatory,
545 pce.proficiency_level_id,
546 pce.high_proficiency_level_id,
547 pce.organization_id,
548 nvl(pce.job_id, -1) job_id,
549 nvl(pce.position_id, -1) position_id,
550 pce.valid_grade_id,
551 nvl(pce.business_group_id, -1) business_group_id,
552 pce.enterprise_id,
553 hrl.meaning structure_type,
554 decode(job_id, null, decode(position_id, null, 1, 0), 0) read_only_attr,
555 0 detail_attr,
556 pc.competence_alias,
557 decode(pc.business_group_id, null, 'Y', 'N') global,
558 pc.description,
559 pc.date_from,
560 pc.certification_required,
561 pc.behavioural_indicator,
562 r1.step_value low_step_value,
563 r1.name low_step_name,
564 r2.step_value high_step_value,
565 r2.name high_step_name,
566 hrl.lookup_code,
567 decode(r1.step_value, null, null, r1.step_value||' - '||r1.name) minimum_proficiency,
568 decode(r2.step_value, null, null, r2.step_value||' - '||r2.name) maximum_proficiency,
569 pce.type
570 FROM per_competence_elements pce,
571 per_competences_vl pc,
572 hr_lookups hrl,
573 per_rating_levels_vl r1,
574 per_rating_levels_vl r2
575 WHERE pce.type = 'REQUIREMENT'
576 AND pce.competence_id = pc.competence_id
577 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
578 and nvl(pce.effective_date_to, trunc(sysdate))
579 AND hrl.lookup_type (+) = 'STRUCTURE_TYPE'
580 AND hrl.lookup_code (+) = decode(pce.organization_id, null, (decode(pce.job_id, null, (decode(pce.position_id, null, 'BUS', 'POS')), 'JOB')), 'ORG')
581 AND pce.proficiency_level_id = r1.rating_level_id(+)
582 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
583 AND pce.business_group_id = enter_price_id
584 and( pce.enterprise_id = nvl(enter_price_id, -1) or pce.organization_id = nvl(p_in_org_id, -1) or pce.job_id = nvl(p_in_job_id, -1) or pce.position_id = nvl(p_in_pos_id,-1) );
585
586 l_mat_comp_table sel_comp_tab;
587
588 I integer default 0;
589
590 isSameCompetence boolean default false;
591 isSameStructureType boolean default false;
592 isIgnore boolean default false;
593 isBEssential boolean default false;
594 isBDesired boolean default false;
595 markOuterRowForIgnore boolean;
596 isEssentialDesired boolean;
597
598 begin
599
600 IF g_dbg THEN op('p_enterprise_id = ' ||p_enterprise_id, g_DEBUG_LOG); END IF;
601 IF g_dbg THEN op('p_business_group_id = ' ||p_enterprise_id, g_DEBUG_LOG); END IF;
602 IF g_dbg THEN op('p_organization_id = ' ||p_organization_id, g_DEBUG_LOG); END IF;
603 IF g_dbg THEN op('p_job_id = ' ||p_job_id, g_DEBUG_LOG); END IF;
604 IF g_dbg THEN op('p_position_id = ' ||p_position_id, g_DEBUG_LOG); END IF;
605
606 OPEN get_asgn_req_comps(p_enterprise_id, p_organization_id, p_job_id, p_position_id);
607 FETCH get_asgn_req_comps BULK COLLECT INTO l_sel_comp_table;
608 CLOSE get_asgn_req_comps;
609
610 l_mat_comp_table := l_sel_comp_table;
611
612 -- execute the cursor and apply the overriding rules
613 FOR J IN 1 ..l_sel_comp_table.count LOOP
614 markOuterRowForIgnore := false;
615 FOR K IN 1 ..l_mat_comp_table.count LOOP
616 BEGIN
617 isSameCompetence := (l_sel_comp_table(J).competence_id = l_mat_comp_table(K).competence_id);
618 isSameStructureType := (l_sel_comp_table(J).lookup_code = l_mat_comp_table(K).lookup_code);
619 isIgnore := ('I' = l_mat_comp_table(K).mandatory);
620
621 if(not isIgnore and isSameCompetence and not isSameStructureType)
622 then
623
624 if('POS' = l_mat_comp_table(K).lookup_code) then
625 markOuterRowForIgnore := true;
626 goto end_block;
627 end if;
628
629 if( ('POS' = l_sel_comp_table(J).lookup_code) and (not 'POS' = l_mat_comp_table(K).lookup_code)) then
630 l_mat_comp_table(K).mandatory := 'I';
631 goto end_block;
632 end if;
633
634 isBEssential := ('Y' = l_mat_comp_table(K).mandatory and
635 'Y' = l_sel_comp_table(J).mandatory);
636 isBDesired := ('N' = l_mat_comp_table(K).mandatory and
637 'N' = l_sel_comp_table(J).mandatory);
638
639 if(isBEssential and 'ORG' = l_sel_comp_table(J).lookup_code and
640 'JOB' = l_mat_comp_table(K).lookup_code)
641 then
642 l_mat_comp_table(K).mandatory := 'I';
643 goto end_block;
644 end if;
645
646 if(isBEssential and 'JOB' = l_sel_comp_table(J).lookup_code and
647 'ORG' = l_mat_comp_table(K).lookup_code)
648 then
649 markOuterRowForIgnore := true;
650 goto end_block;
651 end if;
652
653 if(isBDesired and (('ORG' = l_sel_comp_table(J).lookup_code and
654 'JOB' = l_mat_comp_table(K).lookup_code) or
655 ('JOB' = l_sel_comp_table(J).lookup_code and
656 'ORG' = l_mat_comp_table(K).lookup_code)) )
657 then
658
659 if((l_mat_comp_table(K).low_step_value is not null) and (l_sel_comp_table(J).low_step_value is not null))
660 then
661 --fix for bug 3063145.
662 if(l_mat_comp_table(K).low_step_value >= l_sel_comp_table(J).low_step_value)
663 then
664 l_sel_comp_table(J).low_step_value := l_mat_comp_table(K).low_step_value;
665 l_mat_comp_table(K).mandatory := 'I';
666 end if;
667 elsif(l_mat_comp_table(K).low_step_value is not null and l_sel_comp_table(J).low_step_value is null ) then
668 l_sel_comp_table(J).low_step_value := l_mat_comp_table(K).low_step_value;
669 l_mat_comp_table(K).mandatory := 'I';
670 else
671 l_mat_comp_table(K).mandatory := 'I';
672 end if;
673
674
675 if((l_mat_comp_table(K).high_step_value is not null) and (l_sel_comp_table(J).high_step_value is not null))
676 then
677 --fix for bug 3063145.
678 if( l_mat_comp_table(K).high_step_value <= l_sel_comp_table(J).high_step_value ) then
679 l_sel_comp_table(J).high_step_value := l_mat_comp_table(K).high_step_value;
680 l_mat_comp_table(K).mandatory := 'I';
681 end if;
682 elsif((l_mat_comp_table(K).high_step_value is not null) and (l_sel_comp_table(J).high_step_value is null))
683 then
684 l_sel_comp_table(J).high_step_value := l_mat_comp_table(K).high_step_value;
685 l_mat_comp_table(K).mandatory := 'I';
686 else
687 l_mat_comp_table(K).mandatory := 'I';
688 end if;
689
690 end if;
691
692
693
694 if ( p_skip_duplicate ) then
695 isEssentialDesired := (('Y' = l_sel_comp_table(J).mandatory and 'N' = l_mat_comp_table(K).mandatory ) or
696 ('N' = l_sel_comp_table(J).mandatory and 'Y' = l_mat_comp_table(K).mandatory ) );
697
698 if( isEssentialDesired and (( 'ORG' = l_sel_comp_table(J).lookup_code and 'JOB' = l_mat_comp_table(K).lookup_code )) or
699 (( 'ORG' = l_mat_comp_table(K).lookup_code and 'JOB' = l_sel_comp_table(J).lookup_code)) )
700 then
701 l_mat_comp_table(K).mandatory := 'I';
702 end if;
703
704 end if;
705
706 if ( 'BUS' = l_sel_comp_table(J).lookup_code and 'BUS' = l_mat_comp_table(K).lookup_code )
707 then
708 l_mat_comp_table(K).mandatory := 'I';
709 end if;
710
711
712
713 end if;
714 <<end_block>>
715 null;
716 END;
717
718 END LOOP;
719
720 if(markOuterRowForIgnore)
721 then
722 l_sel_comp_table(J).mandatory := 'I';
723 end if;
724
725
726 FOR I IN 1 ..l_sel_comp_table.count LOOP
727 if( l_sel_comp_table(I).mandatory = 'I') then
728 l_sel_comp_table(I) := null;
729 end if;
730 END LOOP;
731
732
733 END LOOP;
734
735 FOR J IN 1 ..l_sel_comp_table.count LOOP
736 IF g_dbg THEN op(' from overriding comp = ' ||l_sel_comp_table(J).competence_id || ' ' || l_sel_comp_table(J).name, g_DEBUG_LOG); END IF;
737 END LOOP;
738
739 end apply_overridding_rules;
740 --
741 -- ----------------------------------------------------------------------------
742 -- |----------------------< create_appraisal_for_person >---------------------|
743 -- ----------------------------------------------------------------------------
744 -- {Start Of Comments}
745 --
746 -- Description:
747 -- Creates Appraisal for a given person when plan is published.
748 --
749 -- Prerequisites:
750 -- None.
751 --
752 -- In Arguments:
753 --
754 --
755 -- Post Success:
756 -- Processing continues if Appraisal is created.
757 --
758 -- Post Failure:
759 -- An application error is raised if scorecard is not created.
760 --
761 -- Access Status:
762 -- Internal Only.
763 --
764 -- {End Of Comments}
765 -- ----------------------------------------------------------------------------
766 PROCEDURE create_appraisal_for_person
767 ( p_score_card_id per_personal_scorecards.scorecard_id%TYPE,
768 p_appraisal_templ_id per_appraisal_templates.appraisal_template_id%TYPE,
769 p_effective_date DATE,
770 p_appraisal_start_date DATE,
771 p_appraisal_end_date DATE,
772 p_appraisal_status per_appraisals.status%TYPE default 'PLANNED',
773 p_type per_appraisals.type%TYPE default null,
774 p_appraisal_date per_appraisals.appraisal_date%TYPE,
775 -- p_appraisal_system_status per_appraisals.appraisal_system_status%TYPE,
776 p_plan_id number,
777 p_next_appraisal_date per_appraisals.next_appraisal_date%TYPE default null,
778 p_status per_appraisals.status%TYPE default null,
779 p_comments per_appraisals.comments%TYPE default null,
780 p_appraisee_access per_appraisals.appraisee_access%TYPE default null,
781 p_appraisal_initiator per_appraisal_periods.initiator_code%TYPE,
782 p_appraisal_system_type in per_appraisal_periods.appraisal_system_type%TYPE,
783 p_return_status out nocopy VARCHAR2
784 )
785 IS
786
787 cursor get_scorecard_info(p_scorecard_id per_personal_scorecards.scorecard_id%TYPE)
788 IS
789 SELECT scorecard_id, assignment_id, person_id, scorecard_name FROM per_personal_scorecards
790 WHERE scorecard_id = p_scorecard_id;
791 --changed cursor for fixing 6924829
792 cursor get_assignment_info(p_assignment_id per_all_assignments_f.assignment_id%TYPE)
793 IS
794 SELECT paf.assignment_id, paf.business_group_id, paf.grade_id, paf.position_id,
795 paf.job_id,
796 paf.organization_id, paf.supervisor_id, paf.effective_start_date,
797 pps.date_start empl_start_date,
798 ppf.effective_end_date empl_end_date, ppf.person_id
799 FROM per_all_assignments_f paf, per_all_people_f ppf, per_periods_of_service
800 pps
801 WHERE paf.assignment_id = p_assignment_id
802 and trunc(sysdate) between paf.effective_start_date and
803 paf.effective_end_date
804 and paf.person_id = ppf.person_id
805 and paf.assignment_type = 'E'
806 and trunc(sysdate) between ppf.effective_start_date and
807 ppf.effective_end_date
808 and pps.period_of_service_id = paf.period_of_service_id
809 UNION ALL
810 SELECT paf.assignment_id, paf.business_group_id, paf.grade_id, paf.position_id,
811 paf.job_id,
812 paf.organization_id, paf.supervisor_id, paf.effective_start_date,
813 pps.date_start empl_start_date,
814 ppf.effective_end_date empl_end_date, ppf.person_id
815 FROM per_all_assignments_f paf, per_all_people_f ppf,
816 per_periods_of_placement pps
817 WHERE paf.assignment_id = p_assignment_id
818 and trunc(sysdate) between paf.effective_start_date and
819 paf.effective_end_date
820 and paf.assignment_type = 'C'
821 and paf.person_id = ppf.person_id
822 and trunc(sysdate) between ppf.effective_start_date and
823 ppf.effective_end_date
824 and pps.date_start= paf.PERIOD_OF_PLACEMENT_DATE_START
825 and pps.person_id = paf.person_id;
826
827 cursor get_appraisal_templ_info (p_appraisal_templ_id per_appraisals.appraisal_template_id%TYPE)
828 IS
829 SELECT appraisal_template_id, assessment_type_id, objective_asmnt_type_id, business_group_id,
830 show_competency_ratings ,
831 show_objective_ratings ,
832 show_questionnaire_info ,
833 show_participant_details,
834 show_participant_ratings,
835 show_participant_names ,
836 show_overall_ratings ,
837 provide_overall_feedback, -- Bug7393131
838 show_overall_comments
839 FROM per_appraisal_templates
840 WHERE appraisal_template_id = p_appraisal_templ_id;
841
842
843 cursor get_assess_templ_comps(p_assess_type_id per_competence_elements.assessment_type_id%TYPE)
844 IS
845 select ce.competence_id, ce.competence_element_id, ce.type, ce.parent_competence_element_id, c.name,
846 rank() over (partition by ce.competence_id order by ce.competence_element_id) rank
847 from per_competence_elements a, per_competence_elements ce, per_competences_vl c
848 where a.assessment_type_id = p_assess_type_id
849 and a.type = 'ASSESSMENT_GROUP'
850 and (nvl(c.date_from,trunc(sysdate)) <= trunc(sysdate))
851 and nvl(c.date_to,trunc(sysdate)) >= trunc(sysdate)
852 and a.competence_element_id = ce.parent_competence_element_id
853 and ce.competence_id = c.competence_id;
854
855
856 cursor check_default_job_competency(p_assessment_type_id per_assessment_types.assessment_type_id%TYPE)
857 IS
858 select DEFAULT_JOB_COMPETENCIES from per_assessment_types
859 where assessment_type_id = p_assessment_type_id;
860
861 cursor get_scorecard_objectives(p_scorecard_id per_objectives.scorecard_id%TYPE)
862 is
863 select objective_id, scorecard_id, object_version_number, name from per_objectives
864 where scorecard_id = p_scorecard_id
865 and APPRAISE_FLAG = 'Y';
866
867 cursor get_assess_templ_info (p_assess_templ per_appraisal_templates.assessment_type_id%TYPE)
868 IS
869 select DEFAULT_JOB_COMPETENCIES, ASSESSMENT_TYPE_ID from per_assessment_types
870 where ASSESSMENT_TYPE_ID = p_assess_templ;
871
872 cursor find_appraisal(p_plan_id per_perf_mgmt_plans.plan_id%TYPE,
873 p_appr_prd_st_dt per_appraisals.appraisal_period_start_date%TYPE,
874 p_appr_prd_ed_dt per_appraisals.appraisal_period_end_date%TYPE,
875 p_appraisee_person_id per_appraisals.appraisee_person_id%TYPE,
876 -- p_appraiser_person_id per_appraisals.appraiser_person_id%TYPE,
877 -- p_main_appraiser_id per_appraisals.main_appraiser_id%TYPE,
878 p_appr_templ_id per_appraisals.appraisal_template_id%TYPE
879 )
880 is
881 select appraisal_id,appraisal_system_status from per_appraisals
882 where plan_id = p_plan_id
883 and appraisal_period_start_date = p_appr_prd_st_dt --5194541 to_date(p_appr_prd_st_dt,'RRRR-MM-DD')
884 and appraisal_period_end_date = p_appr_prd_ed_dt --5194541 to_date(p_appr_prd_ed_dt,'RRRR-MM-DD')
885 and appraisee_person_id = p_appraisee_person_id
886 and appraisal_system_status <> 'TRANSFER_OUT' -- 7321947
887 -- and appraiser_person_id = p_appraiser_person_id
888 -- and main_appraiser_id = p_main_appraiser_id
889 and appraisal_template_id = p_appr_templ_id;
890
891 l_appraisal_status VARCHAR2(20);
892 l_scorecard_info scorecard_info;
893 NO_SCORE_CARD_WITH_THIS_ID EXCEPTION;
894 l_assignment_info assignment_info;
895 NO_ASSIGNMENT_WITH_THIS_ID EXCEPTION;
896 l_appraisal_ovn per_appraisals.object_version_number%TYPE;
897 l_apprl_return_status VARCHAR2(10) default null;
898 l_assess_comp_return_status VARCHAR2(10) default null;
899 l_assess_obj_return_status VARCHAR2(10) default null;
900 l_apprl_id per_appraisals.appraisal_id%TYPE default null;
901 l_apprl_templ_info appraisal_templ_info;
902 NO_APPRL_TEMPL_WITH_THIS_ID EXCEPTION;
903 l_assessment_comp_id per_assessments.assessment_id%TYPE;
904 l_assessment_obj_id per_assessments.assessment_id%TYPE;
905 l_assessment_comp_ovn per_assessments.object_version_number%TYPE;
906 l_assessment_obj_ovn per_assessments.object_version_number%TYPE;
907 l_assess_comps assess_comps_info;
908 l_check_default_job_comps varchar2(2) default '';
909
910 l_comp_ele_id per_competence_elements.competence_element_id%TYPE;
911 l_comp_ovn per_competence_elements.object_version_number%TYPE;
912 l_return_status varchar2(10) default '';
913 l_competence_error varchar2(1000) default null;
914 module_name varchar2(100) default 'MASS APPRAISAL CREATION';
915 l_error_message varchar2(1000) default null;
916 APPRAISAL_CREATION_ERROR EXCEPTION;
917 ASSESS_COMP_ERROR EXCEPTION;
918 ASSESS_OBJ_ERROR EXCEPTION;
919 l_appraisal_sys_type per_appraisal_periods.appraisal_system_type%TYPE;
920 function_360_exists boolean default false;
921 APPRAISAL_SYSTEM_TYPE_ERROR EXCEPTION;
922 l_weighting_over_100_warning boolean;
923 l_weighting_appraisal_warning boolean;
924 NO_APPRL_FUNCTION_DEFINED EXCEPTION;
925 l_function_id fnd_form_functions.function_id%TYPE;
926 l_function_name fnd_form_functions.function_name%TYPE;
927 l_func_params fnd_form_functions.parameters%TYPE;
928 l_object_id NUMBER;
929 l_assess_comps_processed competences_tbl;
930 z PLS_INTEGER;
931 l_def_job_comps sel_comp_tab;
932 APPRAISAL_SETUP_ISSUE EXCEPTION;
933 l_appraiser_id per_people_f.person_id%TYPE;
934 l_main_appraiser_id per_people_f.person_id%TYPE;
935 l_found_appraisal_id per_appraisals.appraisal_id%TYPE;
936 l_found_appraisal boolean;
937
938
939 -- Declare local variables
940 l_proc varchar2(72) := g_package||'create_appraisal_for_person';
941 --
942 l_scorecard_id number;
943 l_object_version_number number;
944 --l_status_code varchar2(30);
945 l_duplicate_name_warning boolean;
946 l_found_comp boolean;
947 --
948 l_templ_def_job_comps per_assessment_types.default_job_competencies%TYPE;
949 l_assess_type_id per_assessment_types.assessment_type_id%TYPE;
950 l_appraisal_period_start_date per_appraisals.appraisal_period_start_date%TYPE;
951 l_appraisal_date per_appraisals.appraisal_date%TYPE;
952 Begin
953 --
954 IF g_dbg THEN op('Entering:'|| l_proc, g_REGULAR_LOG, 10); END IF;
955 IF g_dbg THEN op('Processing Appraisal for scorecard '|| p_score_card_id, g_DEBUG_LOG, 10); END IF;
956
957 --
958
959 --
960 -- Call create_scorecard
961 --
962 l_scorecard_info.scorecard_id := null;
963 l_scorecard_info.assignment_id := null;
964
965
966 OPEN get_scorecard_info(p_score_card_id);
967 FETCH get_scorecard_info INTO l_scorecard_info;
968 CLOSE get_scorecard_info;
969 if(l_scorecard_info.scorecard_id is null or l_scorecard_info.assignment_id is null) then
970 fnd_file.put_line(FND_FILE.log,'NO_SCORE_CARD_WITH_THIS_ID');
971 RAISE NO_SCORE_CARD_WITH_THIS_ID;
972 end if;
973
974 IF g_dbg THEN op(' get_scorecard_info '|| l_proc, g_DEBUG_LOG, 20); END IF;
975
976
977 l_assignment_info.assignment_id := null;
978 OPEN get_assignment_info(l_scorecard_info.assignment_id);
979 FETCH get_assignment_info INTO l_assignment_info;
980 CLOSE get_assignment_info;
981 if(l_assignment_info.assignment_id is null) then
982 fnd_file.put_line(FND_FILE.log,'NO_ASSIGNMENT_WITH_THIS_ID');
983 RAISE NO_ASSIGNMENT_WITH_THIS_ID;
984 end if;
985
986 IF g_dbg THEN op(' get_assignment_info '|| l_proc, g_DEBUG_LOG, 20); END IF;
987
988
989 OPEN get_appraisal_templ_info(p_appraisal_templ_id);
990 FETCH get_appraisal_templ_info INTO l_apprl_templ_info;
991 CLOSE get_appraisal_templ_info;
992 if(l_apprl_templ_info.appraisal_template_id is null) then
993 fnd_file.put_line(FND_FILE.log,'NO_APPRL_TEMPL_WITH_THIS_ID');
994 RAISE NO_APPRL_TEMPL_WITH_THIS_ID;
995 end if;
996
997 IF g_dbg THEN op(' get_appraisal_templ_info '|| l_proc, g_DEBUG_LOG, 20); END IF;
998
999
1000 l_appraisal_sys_type := p_appraisal_system_type;
1001
1002 function_360_exists := get_appraisal_config_params(p_appr_initiator_code => p_appraisal_initiator,
1003 p_function_id => l_function_id,
1004 p_function_name => l_function_name,
1005 p_func_parameters => l_func_params,
1006 p_appraisal_sys_type => l_appraisal_sys_type);
1007 IF g_dbg THEN op('l_function_id = ' || l_function_id, g_DEBUG_LOG); END IF;
1008 IF g_dbg THEN op('l_function_name = ' || l_function_name, g_DEBUG_LOG); END IF;
1009 IF g_dbg THEN op('l_func_params = ' || l_func_params, g_DEBUG_LOG); END IF;
1010
1011 IF(l_function_id is null or l_appraisal_sys_type is null) then
1012 IF g_dbg THEN
1013 op('Could not derive Appraisal Function or Appraisal System Type', g_DEBUG_LOG);
1014 END IF;
1015 fnd_file.put_line(FND_FILE.log,'APPRAISAL_SETUP_ISSUE');
1016 raise APPRAISAL_SETUP_ISSUE;
1017 END IF;
1018
1019
1020 /*
1021 IF (function_360_exists = true) then
1022 IF g_dbg THEN op(' Appraisal System Function ' || l_appraisal_sys_status, g_DEBUG_LOG); END IF;
1023 end if;
1024
1025 -- throw exception as there is no Function
1026 if(function_360_exists = false) then
1027 raise NO_APPRL_FUNCTION_DEFINED;
1028 IF g_dbg THEN op(' Appraisal System Function ' || l_appraisal_sys_status, g_DEBUG_LOG); END IF;
1029
1030 end if;
1031 */
1032
1033
1034 --function_360_exists := true; -- to be changed
1035
1036
1037 /*
1038
1039 if(function_360_exists = false) then
1040 raise APPRAISAL_SYSTEM_TYPE_ERROR;
1041 end if;
1042
1043 */
1044
1045 --fnd_log.string(fnd_log.level_error,module_name,' Appraisal Creation for Score Card ' || l_scorecard_info.scorecard_name);
1046
1047 -- to be, in case of Position Hierarchy we need get the supervisor id using the Plan and
1048 -- position hierarchy cursor.
1049
1050 if(p_appraisal_initiator = 'MGR')then
1051 l_appraiser_id := l_assignment_info.supervisor_id;
1052 l_main_appraiser_id := l_assignment_info.supervisor_id;
1053 elsif(p_appraisal_initiator = 'EMP') then
1054 l_appraiser_id := l_scorecard_info.person_id;
1055 l_main_appraiser_id := l_assignment_info.supervisor_id;
1056 end if;
1057 -- defaulting with employment start date if new joinee has joined after mass appraisal push
1058 if(l_assignment_info.empl_start_date > p_appraisal_start_date) then
1059 l_appraisal_period_start_date := l_assignment_info.empl_start_date;
1060 else
1061 l_appraisal_period_start_date := p_appraisal_start_date;
1062 end if;
1063 --- same logic goes for appraisal date which is defaulted as task start date in appraisal_push function
1064 if(l_assignment_info.empl_start_date > p_appraisal_date) then
1065 l_appraisal_date := l_assignment_info.empl_start_date;
1066 else
1067 l_appraisal_date := p_appraisal_date;
1068 end if;
1069 open find_appraisal(p_plan_id, l_appraisal_period_start_date, p_appraisal_end_date,
1070 l_scorecard_info.person_id,
1071 -- l_appraiser_id, l_main_appraiser_id,
1072 p_appraisal_templ_id);
1073 fetch find_appraisal into l_found_appraisal_id,l_appraisal_status;
1074 if find_appraisal%FOUND then
1075 l_found_appraisal := true;
1076 IF g_dbg THEN op(' find_appraisal: Found Appraisal for this Person '|| l_scorecard_info.person_id || ':' || l_found_appraisal_id, g_DEBUG_LOG, 20); END IF;
1077 else
1078 l_found_appraisal := false;
1079 end if;
1080 close find_appraisal;
1081
1082 --- added this part of the code to attach appraisal id to objectives
1083 --- of per_objectives records which are populated due to new eligible objectives in scorecards
1084 --- for already created appraisals.If new objectives are added to an existing scorecard
1085 --- that shud also be added to appraisals.Bug no 6015946
1086
1087 -- if( l_found_appraisal and l_appraisal_status <> 'COMPLETED') then
1088 -- 7475464 Bug Fix changes, i.e adding APPRFEEDBACK check inadditon to
1089 -- completed appraisals check
1090 if( l_found_appraisal and l_appraisal_status not in( 'COMPLETED','APPRFEEDBACK')) then
1091
1092 -- added the completed appraisals check because if the plan is republished
1093 -- within the same appraisal period.. appraisals will be found and objectives
1094 -- with scorecard of the person will be updated with the appraisal_id.BUT
1095 -- if the appraisal is already completed, It will have duplicate rows for
1096 -- the same objective( one for appraisal and one for scorecard)
1097 -- if the objectives with scorecard is again updated , then we will see
1098 -- duplicate objectives in completed appraisal
1099 -- we can restrict the view in completed appraisals also by scorecard!=null
1100 -- check BUT this is not tried
1101 -- as if appraisal is reopened we may lead to data issues.
1102 for objectives in get_scorecard_objectives( p_score_card_id )
1103 loop
1104 begin
1105 hr_objectives_api.update_objective(p_validate => false,
1106 p_objective_id => objectives.objective_id,
1107 p_object_version_number => objectives.object_version_number,
1108 p_effective_date => p_effective_date,
1109 p_appraisal_id => l_found_appraisal_id,-- modified by AM --
1110 p_weighting_over_100_warning => l_weighting_over_100_warning,
1111 p_weighting_appraisal_warning => l_weighting_appraisal_warning
1112 );
1113 exception when others then
1114 -- to be added a message to identify update objective error
1115 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
1116 end;
1117 end loop;
1118 end if;
1119
1120 ---
1121 --- End of code new for Bug no 6015946
1122 ---
1123
1124 if(not l_found_appraisal) then
1125
1126 IF l_main_appraiser_id IS NULL THEN
1127 fnd_file.put_line(fnd_file.log,'Unable to create appraisal for : '||l_scorecard_info.person_id||' as main appraiser could not be identified.');
1128 fnd_message.set_name('PER','HR_50297_WPM_CP_ERROR');
1129 g_cp_error_txt := nvl(fnd_message.get, 'HR_50297_WPM_CP_ERROR');
1130 g_retcode := WARNING;
1131 g_errbuf := g_cp_error_txt;
1132 g_num_errors := g_num_errors + 1;
1133 --
1134 IF g_num_errors > g_max_errors THEN
1135 fnd_message.set_name('PER','HR_50298_WPM_MAX_ERRORS');
1136 g_error_txt := nvl(fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
1137 g_retcode := ERROR;
1138 g_errbuf := g_error_txt;
1139 fnd_message.raise_error; --RAISE;
1140 END IF;
1141 RETURN;
1142 END IF;
1143
1144 IF g_dbg THEN op(' Eff Date : ' || p_effective_date, g_DEBUG_LOG); END IF;
1145 hr_appraisals_api.create_appraisal(
1146 p_validate => false,
1147 p_open => null, -- Its mandatory to pass null, as the defult is 'Y'.
1148 p_effective_date => p_effective_date,
1149 p_business_group_id => l_assignment_info.business_group_id,
1150 p_appraisal_template_id => p_appraisal_templ_id,
1151
1152 p_show_competency_ratings => l_apprl_templ_info.show_competency_ratings,
1153 p_show_objective_ratings => l_apprl_templ_info.show_objective_ratings,
1154 p_show_questionnaire_info => l_apprl_templ_info.show_questionnaire_info,
1155 p_show_participant_details => l_apprl_templ_info.show_participant_details,
1156 p_show_participant_ratings => l_apprl_templ_info.show_participant_ratings,
1157 p_show_participant_names => l_apprl_templ_info.show_participant_names,
1158 p_show_overall_ratings => l_apprl_templ_info.show_overall_ratings,
1159 p_provide_overall_feedback => l_apprl_templ_info.disable_provide_feed, --Bug7393131
1160 p_show_overall_comments => l_apprl_templ_info.show_overall_comments,
1161 p_update_appraisal => 'Y',
1162
1163 p_appraisee_person_id => l_scorecard_info.person_id,
1164 p_appraiser_person_id => l_appraiser_id, --to be changed for position
1165 p_appraisal_date => l_appraisal_date,
1166 p_appraisal_period_start_date => l_appraisal_period_start_date ,
1167 p_appraisal_period_end_date => p_appraisal_end_date,
1168 p_type => p_type, -- ANN,
1169 p_next_appraisal_date => p_next_appraisal_date,
1170 p_status => p_status, -- PLANNED,TRANSFER,RFC,
1171 p_comments => p_comments,
1172 p_system_type => l_appraisal_sys_type, --MGR360 EMP360
1173 p_system_params => l_func_params,
1174 --p_appraisee_access,
1175 p_main_appraiser_id => l_main_appraiser_id, --to be changed for position
1176 p_assignment_id => l_assignment_info.assignment_id ,
1177 p_assignment_start_date => l_assignment_info.effective_state_date,
1178 p_asg_business_group_id => l_assignment_info.business_group_id ,
1179 p_assignment_organization_id => l_assignment_info.org_id ,
1180 p_assignment_job_id => l_assignment_info.job_id ,
1181 --p_assignment_position_id = l_assignment_info.position_id ,
1182 p_assignment_grade_id => l_assignment_info.grade_id ,
1183 p_appraisal_id => l_apprl_id,
1184 p_object_version_number => l_appraisal_ovn ,
1185 p_appraisal_system_status => p_appraisal_status ,
1186 p_plan_id => p_plan_id);
1187
1188 IF g_dbg THEN op('Appraisal Id = ' || l_apprl_id, g_DEBUG_LOG); END IF;
1189
1190
1191 if( l_apprl_id is not null and l_apprl_templ_info.assessment_type_id is not null) then
1192 hr_assessments_api.create_assessment
1193 (p_assessment_id => l_assessment_comp_id,
1194 p_assessment_type_id => l_apprl_templ_info.assessment_type_id,
1195 p_business_group_id => l_assignment_info.business_group_id,
1196 p_person_id => l_scorecard_info.person_id,
1197 --p_assessment_group_id,
1198 p_assessment_period_start_date => l_appraisal_period_start_date,
1199 p_assessment_period_end_date => p_appraisal_end_date,
1200 p_assessment_date => l_appraisal_date,
1201 p_assessor_person_id => l_appraiser_id, --to be changed for position
1202 p_appraisal_id => l_apprl_id,
1203 --p_comments,
1204 p_object_version_number => l_assessment_comp_ovn,
1205 p_validate => false,
1206 p_effective_date => p_effective_date);
1207 end if;
1208
1209 IF g_dbg THEN op('Competence Assesment Id = ' || l_assessment_comp_id, g_DEBUG_LOG); END IF;
1210
1211
1212 -- this record is created for final ratings on Objectives.
1213
1214 if(l_apprl_id is not null and l_apprl_templ_info.objective_asmnt_type_id is not null) then
1215 hr_assessments_api.create_assessment
1216 (p_assessment_id => l_assessment_obj_id,
1217 p_assessment_type_id => l_apprl_templ_info.objective_asmnt_type_id,
1218 p_business_group_id => l_assignment_info.business_group_id,
1219 p_person_id => l_scorecard_info.person_id,
1220 --p_assessment_group_id,
1221 p_assessment_period_start_date => l_appraisal_period_start_date,
1222 p_assessment_period_end_date => p_appraisal_end_date,
1223 p_assessment_date => l_appraisal_date,
1224 p_assessor_person_id => l_appraiser_id, --to be changed for position
1225 p_appraisal_id => l_apprl_id,
1226 --p_comments,
1227 p_object_version_number => l_assessment_obj_ovn,
1228 p_validate => false,
1229 p_effective_date => p_effective_date);
1230
1231 end if;
1232
1233 IF g_dbg THEN op('Objective Assessment Id = ' || l_assessment_obj_id, g_DEBUG_LOG); END IF;
1234
1235 -- to be
1236
1237 IF ( p_appraisal_initiator = 'MGR') then
1238 l_object_id := l_assignment_info.supervisor_id;
1239 elsif (p_appraisal_initiator = 'EMP') then
1240 l_object_id := l_scorecard_info.person_id;
1241 end if;
1242
1243 z := 1;
1244 if( l_assessment_comp_id is not null ) then
1245 for competences in get_assess_templ_comps(l_apprl_templ_info.assessment_type_id)
1246 loop
1247 begin
1248 l_return_status := '';
1249 l_competence_error := null;
1250
1251 l_assess_comps_processed(z).competence_id := competences.competence_id;
1252 hr_competence_element_api.create_competence_element(
1253 p_validate => false,
1254 p_competence_element_id => l_comp_ele_id,
1255 p_object_version_number => l_comp_ovn,
1256 p_type => 'ASSESSMENT',
1257 p_business_group_id => l_assignment_info.business_group_id,
1258 p_competence_id => competences.competence_id,
1259 p_assessment_id => l_assessment_comp_id,
1260 p_effective_date_from => l_appraisal_period_start_date,
1261 p_effective_date =>p_effective_date,
1262 p_object_name => 'ASSESSOR_ID',
1263 p_object_id => l_object_id,
1264 p_parent_competence_element_id => competences.competence_element_id
1265 );
1266 z := z+1;
1267 IF g_dbg THEN op('Competence Element Id = ' || l_comp_ele_id, g_DEBUG_LOG); END IF;
1268
1269 exception when others then
1270 -- to be added a message to identify competence element error
1271 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
1272 end;
1273 end loop;
1274 end if;
1275
1276 open get_assess_templ_info(l_apprl_templ_info.assessment_type_id);
1277 fetch get_assess_templ_info into l_templ_def_job_comps, l_assess_type_id;
1278 close get_assess_templ_info;
1279
1280 if(l_templ_def_job_comps = 'Y') then
1281 apply_overridding_rules(p_enterprise_id => l_assignment_info.business_group_id,
1282 p_organization_id => l_assignment_info.org_id,
1283 p_job_id => l_assignment_info.job_id,
1284 p_position_id => l_assignment_info.position_id,
1285 p_skip_duplicate => true,
1286 l_sel_comp_table => l_def_job_comps);
1287
1288 -- create the Job Comps eliminating duplicates
1289 if( l_assessment_comp_id is not null ) then
1290 FOR J IN 1 ..l_def_job_comps.count LOOP
1291 begin
1292 l_found_comp := false;
1293 for k in 1..l_assess_comps_processed.count LOOP
1294 if( l_def_job_comps(j).competence_id is not null and l_def_job_comps(j).competence_id = l_assess_comps_processed(k).competence_id) then
1295 l_found_comp := true;
1296 end if;
1297 end loop;
1298
1299 if(l_def_job_comps(j).competence_id is not null and not l_found_comp ) then
1300 hr_competence_element_api.create_competence_element(
1301 p_validate => false,
1302 p_competence_element_id => l_comp_ele_id,
1303 p_object_version_number => l_comp_ovn,
1304 p_type => 'ASSESSMENT',
1305 p_business_group_id => l_assignment_info.business_group_id,
1306 p_competence_id => l_def_job_comps(J).competence_id,
1307 p_assessment_id => l_assessment_comp_id,
1308 p_effective_date_from => l_appraisal_period_start_date,
1309 p_effective_date =>p_effective_date,
1310 p_object_name => 'ASSESSOR_ID',
1311 p_object_id => l_object_id,
1312 p_parent_competence_element_id => l_def_job_comps(J).competence_element_id
1313 );
1314 IF g_dbg THEN op(' Def Job Competence Id and Element Id = ' || l_comp_ele_id || l_def_job_comps(J).competence_id, g_DEBUG_LOG); END IF;
1315
1316 end if;
1317
1318 exception when others then
1319 -- to be added a message to identify competence element error
1320 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
1321 end;
1322 end loop;
1323 end if;
1324 end if;
1325
1326 if( l_apprl_id is not null) then
1327 for objectives in get_scorecard_objectives( p_score_card_id )
1328 loop
1329 begin
1330 hr_objectives_api.update_objective(p_validate => false,
1331 p_objective_id => objectives.objective_id,
1332 p_object_version_number => objectives.object_version_number,
1333 p_effective_date => p_effective_date,
1334 p_appraisal_id => l_apprl_id, -- to be changed in SWI,API,RHI
1335 p_weighting_over_100_warning => l_weighting_over_100_warning,
1336 p_weighting_appraisal_warning => l_weighting_appraisal_warning
1337 );
1338
1339 IF g_dbg THEN op('Linked objective Id to Appraisal = ' || objectives.objective_id, g_DEBUG_LOG); END IF;
1340
1341 exception when others then
1342 -- to be added a message to identify update objective error
1343 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
1344 end;
1345 end loop;
1346 end if;
1347
1348 end if;
1349
1350 -- Out parameter
1351
1352 --
1353 IF g_dbg THEN op('Leaving:'||l_proc, g_REGULAR_LOG, 80); END IF;
1354 --
1355 exception
1356 when others then
1357 IF g_dbg THEN op('Leaving:'||l_proc, g_REGULAR_LOG, 90); END IF;
1358 --
1359 fnd_message.set_name('PER','HR_50297_WPM_CP_ERROR');
1360 g_cp_error_txt := nvl(fnd_message.get, 'HR_50297_WPM_CP_ERROR');
1361 g_retcode := WARNING;
1362 g_errbuf := g_cp_error_txt;
1363 g_num_errors := g_num_errors + 1;
1364 IF g_dbg THEN op(g_error_txt, g_REGULAR_LOG); END IF;
1365 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
1366
1367 --
1368 -- If the max number of errors has been exceeded, raise the error and
1369 -- terminate processing of this plan.
1370 --
1371 IF g_num_errors > g_max_errors THEN
1372 fnd_message.set_name('PER','HR_50298_WPM_MAX_ERRORS');
1373 g_error_txt := nvl(fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
1374 g_retcode := ERROR;
1375 g_errbuf := g_error_txt;
1376 RAISE;
1377 END IF;
1378
1379 End create_appraisal_for_person;
1380 --
1381 -- ----------------------------------------------------------------------------
1382 -- |---------------------< submit_apprisal_cp >---------------------------|
1383 -- ----------------------------------------------------------------------------
1384 --
1385 procedure submit_appraisal_cp
1386 (p_effective_date in date
1387 ,p_start_date in varchar2
1388 ,p_plan_id in number
1389 ,p_appraisal_period_id in number
1390 ,p_log_output in varchar2
1391 )
1392 is
1393 --
1394 l_object_version_number number ;
1395 l_status_code per_perf_mgmt_plans.status_code%TYPE;
1396 l_dummy boolean;
1397 l_request_id number;
1398 l_effective_date varchar2(30) := fnd_date.date_to_canonical(nvl(p_effective_date,trunc(sysdate)));
1399
1400 --
1401 begin
1402 -- Submit the request
1403 l_request_id := fnd_request.submit_request(
1404 application => 'PER'
1405 ,program => 'WPMAPRPUSH'
1406 ,sub_request => FALSE
1407 ,start_time => p_start_date
1408 ,argument1 => l_effective_date
1409 ,argument2 => p_plan_id
1410 ,argument3 => p_appraisal_period_id
1411 ,argument4 => p_log_output
1412 );
1413 --
1414 if l_request_id > 0 then
1415 null;
1416 end if;
1417 --
1418 end submit_appraisal_cp;
1419 --
1420 -- ----------------------------------------------------------------------------
1421 -- |---------------------------< appraisal_cp >----------------------------|
1422 -- ----------------------------------------------------------------------------
1423 --
1424 procedure appraisal_cp
1425 (errbuf out nocopy varchar2
1426 ,retcode out nocopy number
1427 ,p_effective_date in varchar2
1428 ,p_plan_id in number
1429 ,p_appraisal_period_id in number
1430 ,p_log_output in varchar2 default 'N'
1431 ,p_delete_pending_trans IN VARCHAR2 DEFAULT 'N'
1432 ) is
1433
1434 --
1435 cursor csr_plan_ovn is
1436 select object_version_number
1437 from per_perf_mgmt_plans
1438 where plan_id = p_plan_id;
1439
1440 l_object_version_number number;
1441 l_effective_date date := fnd_date.canonical_to_date(nvl(p_effective_date,trunc(sysdate)));
1442 CURSOR csr_pend_trans(p_plan_id IN NUMBER) IS
1443 SELECT 'x'
1444 FROM DUAL
1445 WHERE EXISTS (SELECT 'x'
1446 FROM hr_api_transactions t
1447 ,per_personal_scorecards sc
1448 WHERE t.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
1449 AND t.transaction_ref_id = sc.scorecard_id
1450 AND sc.plan_id = p_plan_id);
1451 l_chk VARCHAR2(10);
1452 SCORECARD_PENDING_TRANSACTION exception;
1453
1454
1455 --
1456 begin
1457 /*
1458 --
1459 -- Derive the object version number of plan record
1460 --
1461 open csr_plan_ovn;
1462 fetch csr_plan_ovn into l_object_version_number;
1463 close csr_plan_ovn;
1464 */
1465 -- Initialize return status
1466 retcode := WARNING;
1467
1468 --
1469 -- Raise an error if there are any pending transactions
1470 --
1471 IF nvl(p_delete_pending_trans,'N') ='N' THEN
1472 OPEN csr_pend_trans(p_plan_id);
1473 FETCH csr_pend_trans INTO l_chk;
1474 IF csr_pend_trans%FOUND THEN
1475 CLOSE csr_pend_trans;
1476 RAISE SCORECARD_PENDING_TRANSACTION;
1477 END IF;
1478 CLOSE csr_pend_trans;
1479 END IF;
1480 --
1481 -- Call the publish plan
1482 --
1483 appraisal_push
1484 (p_effective_date => l_effective_date
1485 ,p_plan_id => p_plan_id
1486 ,p_appraisal_period_id => p_appraisal_period_id
1487 ,p_log_output => 'Y' --p_log_output
1488 );
1489
1490 --
1491 errbuf := g_errbuf;
1492 retcode:= g_retcode;
1493 --
1494 commit;
1495 --
1496 exception
1497 when SCORECARD_PENDING_TRANSACTION then
1498 retcode := ERROR;
1499 fnd_message.set_name('PER','HR_SC_PENDING_TXN_ERR');
1500 errbuf := nvl(fnd_message.get, 'HR_SC_PENDING_TXN_ERR');
1501 rollback;
1502 when others then
1503 rollback;
1504 --
1505 errbuf := g_errbuf;
1506 retcode:= g_retcode;
1507 --
1508 end appraisal_cp;
1509 --
1510 -- ----------------------------------------------------------------------------
1511 -- |-----------------------------< appraisal_push>-----------------------------|
1512 -- ----------------------------------------------------------------------------
1513 --
1514 procedure appraisal_push
1515 (p_effective_date in date
1516 ,p_plan_id in number
1517 ,p_appraisal_period_id in number
1518 ,p_log_output in varchar2
1519 ) is
1520
1521 --
1522 -- Declare cursors and local variables
1523 --
1524 l_proc varchar2(72) := g_package||'appraisal_push';
1525 l_logging pay_action_parameters.parameter_value%type;
1526 l_debug boolean := FALSE;
1527 l_effective_date date := TRUNC(nvl(p_effective_date, sysdate));
1528 l_object_version_number number;
1529 l_status_code per_perf_mgmt_plans.status_code%TYPE;
1530 l_dummy boolean;
1531
1532 --
1533 l_scorecard_id per_personal_scorecards.scorecard_id%TYPE;
1534 --
1535 l_message_count number := 0;
1536 l_message varchar2(256);
1537 l_qual_pop_index binary_integer;
1538 l_curr_sc_pop_index binary_integer;
1539 l_qual_obj_index binary_integer;
1540 l_plan_aprsl_pds_index binary_integer;
1541 l_curr_sc_obj_index binary_integer;
1542 l_appr_ret_status varchar2(1);
1543
1544 -- Plan record
1545 CURSOR csr_get_plan_rec IS
1546 SELECT *
1547 FROM per_perf_mgmt_plans
1548 WHERE plan_id = p_plan_id;
1549 /*
1550 -- Scorecard Objectives
1551 CURSOR csr_sc_objectives(p_scorecard_id number) IS
1552 select objective_id, object_version_number
1553 from per_objectives
1554 where scorecard_id = p_scorecard_id;
1555 */
1556 CURSOR csr_plan_appraisals(plan_id per_appraisals.plan_id%TYPE) IS
1557 select appraisal_id, object_version_number from per_appraisals
1558 where plan_id = plan_id;
1559
1560 --
1561 -- Scorecard cursor
1562 CURSOR csr_get_scorecards IS
1563 SELECT scorecard_id,object_version_number,status_code
1564 FROM per_personal_scorecards
1565 WHERE plan_id = p_plan_id;
1566 --
1567
1568 l_plan_rec per_perf_mgmt_plans%ROWTYPE;
1569 l_obj_date date;
1570 l_scorecard_status_code varchar2(30);
1571 --
1572 -- cursor to select pending transactions for a given scorecard
1573 --
1574 CURSOR c_trx (p_sc_card_id number)
1575 is
1576 SELECT transaction_id
1577 FROM hr_api_transactions
1578 WHERE transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
1579 AND transaction_ref_id = p_sc_card_id;
1580 l_sc_ovn number;
1581 l_trx_id number;
1582 --
1583 begin
1584 --
1585 -- Initialize logging
1586 --
1587 initialize_logging(p_action_parameter_group_id => null,
1588 p_log_output => p_log_output);
1589 --
1590 IF g_dbg THEN op('Entering:'|| l_proc, g_DEBUG_LOG, 10); END IF;
1591 --
1592 -- Get Plan record
1593 --
1594 OPEN csr_get_plan_rec;
1595 FETCH csr_get_plan_rec INTO l_plan_rec;
1596 CLOSE csr_get_plan_rec;
1597 --
1598 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 20); END IF;
1599 IF g_dbg THEN op('Plan Name: '|| substr(l_plan_rec.plan_name,1,40), g_DEBUG_LOG, 21); END IF;
1600 IF g_dbg THEN op('Concurrent Request ID: '|| to_char(fnd_global.conc_request_id), g_DEBUG_LOG, 22); END IF;
1601 --
1602 -- Checks that the status is valid for PLAN PUBLISH OR REVERSE PUBLISH
1603 --
1604 --*****************************************
1605 --*********check later for plan-publish status
1606 --*****************************************
1607 --chk_publishing_status(p_reverse_mode, l_plan_rec.status_code);
1608 --
1609 -- If appraisals flag is set then populate appraisala period table
1610 IF (l_plan_rec.include_appraisals_flag = 'Y') THEN
1611 --
1612 -- Get the qualifying plan periods
1613 -- g_plan_aprsl_pds_tbl is populated with details
1614 --
1615 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 30); END IF;
1616 --
1617
1618 -- This needs to be checked, as we no longer need plan_id for populating,
1619 -- as we will be hanling one appraisal period at a time now
1620 populate_plan_apprsl_periods(l_plan_rec.plan_id,p_appraisal_period_id);
1621 END IF;
1622 --
1623 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 40); END IF;
1624 --
1625 FOR curr_scorecard in csr_get_scorecards
1626 LOOP
1627 IF (curr_scorecard.status_code <> 'TRANSFER_OUT') THEN
1628 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 50); END IF;
1629 --
1630 -- Create Appraisals if flag is set
1631 --
1632 IF (l_plan_rec.include_appraisals_flag = 'Y') THEN
1633 --
1634 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 60); END IF;
1635 l_plan_aprsl_pds_index := g_plan_aprsl_pds_tbl.FIRST;
1636 WHILE (l_plan_aprsl_pds_index IS NOT NULL)
1637 LOOP
1638 --
1639 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 70); END IF;
1640 create_appraisal_for_person
1641 ( p_score_card_id => curr_scorecard.scorecard_id,
1642 p_appraisal_templ_id => g_plan_aprsl_pds_tbl(l_plan_aprsl_pds_index).appraisal_template_id,
1643 p_effective_date => p_effective_date, --to be validated
1644 p_appraisal_start_date => g_plan_aprsl_pds_tbl(l_plan_aprsl_pds_index).start_date,
1645 p_appraisal_end_date => g_plan_aprsl_pds_tbl(l_plan_aprsl_pds_index).end_date,
1646 p_appraisal_date => g_plan_aprsl_pds_tbl(l_plan_aprsl_pds_index).task_start_date,
1647 p_appraisal_status => 'PLANNED', -- decided in the meeting
1648 p_plan_id => p_plan_id,
1649 p_next_appraisal_date => null, -- to be
1650 p_appraisal_initiator => g_plan_aprsl_pds_tbl(l_plan_aprsl_pds_index).initiator_code,
1651 p_type => g_plan_aprsl_pds_tbl(l_plan_aprsl_pds_index).appraisal_type, -- A column to be added to UI and table in per_appraisal_periods
1652 p_appraisal_system_type => g_plan_aprsl_pds_tbl(l_plan_aprsl_pds_index).appraisal_system_type,
1653 p_return_status => l_appr_ret_status
1654 );
1655 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 80); END IF;
1656 --
1657 l_plan_aprsl_pds_index := g_plan_aprsl_pds_tbl.NEXT(l_plan_aprsl_pds_index);
1658 --
1659 END LOOP;
1660 --
1661 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 90); END IF;
1662 --
1663 -- Delete all pending transactions.. once this appraisal push
1664 -- completes, then these transactions are not needed anymore.
1665 --
1666 begin
1667 open c_trx(curr_scorecard.scorecard_id);
1668 fetch c_trx into l_trx_id;
1669 close c_trx;
1670 if l_trx_id is not null then
1671 begin
1672 delete from hr_api_transaction_steps
1673 where transaction_id = l_trx_id;
1674 exception
1675 when others then
1676 null;
1677 end;
1678 --
1679 begin
1680 delete from hr_api_transactions
1681 where transaction_id = l_trx_id;
1682 exception
1683 when others then
1684 null;
1685 end;
1686 --
1687 end if;
1688 exception
1689 when others then
1690 null;
1691 end;
1692 --
1693 -- Need to change the status of the scorecard to 'PUBLISHED'
1694 --
1695 l_sc_ovn := curr_scorecard.object_version_number;
1696 begin
1697 hr_personal_scorecard_api.update_scorecard_status
1698 (p_effective_date => trunc(sysdate)
1699 ,p_scorecard_id => curr_scorecard.scorecard_id
1700 ,p_object_version_number => l_sc_ovn
1701 ,p_status_code => 'PUBLISHED'
1702 );
1703 exception
1704 when others then
1705 null;
1706 end;
1707 --
1708 END IF; --include appraisal flag
1709 --
1710 IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 100); END IF;
1711 END IF;
1712 END LOOP;
1713 --
1714 IF g_dbg THEN
1715 op('Number of errors occured:'|| to_char(g_num_errors), g_REGULAR_LOG, 108);
1716 op('Maximum errors allowed:'|| g_max_errors, g_REGULAR_LOG, 109);
1717 op('Leaving:'|| l_proc, g_REGULAR_LOG, 110);
1718 END IF;
1719 exception
1720 when others then
1721 IF g_dbg THEN
1722 op('Number of errors occured:'|| to_char(g_num_errors), g_REGULAR_LOG, 88);
1723 op('Maximum errors allowed:'|| g_max_errors, g_REGULAR_LOG, 89);
1724 op('Leaving:'||l_proc, g_REGULAR_LOG, 90);
1725 END IF;
1726 --
1727 fnd_message.set_name('PER','HR_50297_WPM_CP_ERROR');
1728 g_cp_error_txt := nvl(fnd_message.get, 'HR_50297_WPM_CP_ERROR');
1729 g_retcode := WARNING;
1730 g_errbuf := g_cp_error_txt;
1731 g_num_errors := g_num_errors + 1;
1732 IF g_dbg THEN op(g_error_txt, g_REGULAR_LOG); END IF;
1733 IF g_dbg THEN op(SQLERRM, g_REGULAR_LOG); END IF;
1734
1735 --
1736 -- If the max number of errors has been exceeded, raise the error and
1737 -- terminate processing of this plan.
1738 --
1739 IF g_num_errors > g_max_errors THEN
1740 fnd_message.set_name('PER','HR_50298_WPM_MAX_ERRORS');
1741 g_error_txt := nvl(fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
1742 g_retcode := ERROR;
1743 g_errbuf := g_error_txt;
1744 RAISE;
1745 END IF;
1746 end appraisal_push;
1747 --
1748
1749 end HR_WPM_MASS_APR_PUSH;