[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.53.12020000.6 2013/04/23 11:04:16 schowdhu ship $ */
3 -- Package Variables
4 --
5 g_package VARCHAR2 (33) := 'hr_wpm_mass_apr_push.';
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7 g_num_errors NUMBER := 0;
8 g_max_errors NUMBER;
9 --
10 -- Proprietory debugging. Allows for concurrent request output, etc.
11 -- (see procedures "op").
12 --
13 g_dbg BOOLEAN := g_debug;
14 g_dbg_type NUMBER := g_no_debug;
15 g_log_level VARCHAR2 (1) := g_regular_log;
16 --
17 -- Package (private) constants.
18 --
19 LOGGING CONSTANT pay_action_parameters.parameter_name%TYPE
20 := 'LOGGING';
21 max_errors CONSTANT pay_action_parameters.parameter_name%TYPE
22 := '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 --
30 -- Private user-defined types.
31 --
32 -- Used for populating plan appraisal periods
33 TYPE g_plan_aprsl_pds_r IS RECORD (
34 appraisal_period_id per_appraisal_periods.appraisal_period_id%TYPE,
35 appraisal_template_id per_appraisal_periods.appraisal_template_id%TYPE,
36 start_date per_appraisal_periods.start_date%TYPE,
37 end_date per_appraisal_periods.end_date%TYPE,
38 task_start_date per_appraisal_periods.task_start_date%TYPE,
39 task_end_date per_appraisal_periods.task_end_date%TYPE,
40 initiator_code per_appraisal_periods.initiator_code%TYPE,
41 appraisal_system_type per_appraisal_periods.appraisal_system_type%TYPE,
42 auto_conc_process per_appraisal_periods.auto_conc_process%TYPE,
43 days_before_task_st_dt per_appraisal_periods.days_before_task_st_dt%TYPE,
44 appraisal_assmt_status per_appraisal_periods.appraisal_assmt_status%TYPE,
45 appraisal_type per_appraisal_periods.appraisal_type%TYPE,
46 participation_type per_appraisal_periods.participation_type%TYPE,
47 questionnaire_template_id per_appraisal_periods.questionnaire_template_id%TYPE
48 );
49
50 TYPE g_plan_aprsl_pds_t IS TABLE OF g_plan_aprsl_pds_r
51 INDEX BY BINARY_INTEGER;
52
53 g_plan_aprsl_pds_tbl g_plan_aprsl_pds_t;
54
55 --
56 TYPE g_boolean_t IS TABLE OF BOOLEAN
57 INDEX BY BINARY_INTEGER;
58
59 g_plan_pop_known_t g_boolean_t;
60 g_fetched_plan_member_index NUMBER;
61
62 --
63 TYPE scorecard_info IS RECORD (
64 scorecard_id per_personal_scorecards.scorecard_id%TYPE,
65 assignment_id per_personal_scorecards.assignment_id%TYPE,
66 person_id per_personal_scorecards.person_id%TYPE,
67 scorecard_name per_personal_scorecards.scorecard_name%TYPE
68 );
69
70 TYPE assignment_info IS RECORD (
71 assignment_id per_all_assignments_f.assignment_id%TYPE,
72 business_group_id per_all_assignments_f.business_group_id%TYPE,
73 grade_id per_all_assignments_f.grade_id%TYPE,
74 position_id per_all_assignments_f.position_id%TYPE,
75 job_id per_all_assignments_f.job_id%TYPE,
76 org_id per_all_assignments_f.organization_id%TYPE,
77 supervisor_id per_all_assignments_f.supervisor_id%TYPE,
78 effective_state_date per_all_assignments_f.effective_start_date%TYPE,
79 empl_start_date per_all_people_f.effective_start_date%TYPE,
80 empl_end_date per_all_people_f.effective_end_date%TYPE,
81 person_id per_all_people_f.person_id%TYPE
82 );
83
84 --
85 -- Following is added with more details to be captured
86 -- done by tpapired for 115.20 version of this file
87 TYPE appraisal_templ_info IS RECORD (
88 appraisal_template_id per_appraisal_templates.appraisal_template_id%TYPE,
89 assessment_type_id per_appraisal_templates.assessment_type_id%TYPE,
90 objective_asmnt_type_id per_appraisal_templates.objective_asmnt_type_id%TYPE,
91 business_group_id per_appraisal_templates.business_group_id%TYPE,
92 show_competency_ratings per_appraisal_templates.show_competency_ratings%TYPE,
93 show_objective_ratings per_appraisal_templates.show_objective_ratings%TYPE,
94 show_questionnaire_info per_appraisal_templates.show_questionnaire_info%TYPE,
95 show_participant_details per_appraisal_templates.show_participant_details%TYPE,
96 show_participant_ratings per_appraisal_templates.show_participant_ratings%TYPE,
97 show_participant_names per_appraisal_templates.show_participant_names%TYPE,
98 show_overall_ratings per_appraisal_templates.show_overall_ratings%TYPE,
99 disable_provide_feed per_appraisal_templates.provide_overall_feedback%TYPE,
100 --Bug7393131
101 show_overall_comments per_appraisal_templates.show_overall_comments%TYPE
102 );
103
104 TYPE assess_comps_info IS RECORD (
105 competence_id per_competence_elements.competence_id%TYPE,
106 competence_element_id per_competence_elements.competence_element_id%TYPE,
107 TYPE per_competence_elements.TYPE%TYPE,
108 parent_competence_element_id per_competence_elements.parent_competence_element_id%TYPE,
109 NAME per_competences_vl.NAME%TYPE,
110 RANK NUMBER
111 );
112
113 TYPE bus_rules_comps IS RECORD (
114 NAME per_competences_vl.NAME%TYPE,
115 competence_id per_competences.competence_id%TYPE,
116 competence_element_id per_competence_elements.competence_element_id%TYPE,
117 mandatory per_competence_elements.mandatory%TYPE,
118 proficiency_level_id per_competence_elements.proficiency_level_id%TYPE,
119 high_proficiency_level_id per_competence_elements.high_proficiency_level_id%TYPE,
120 organization_id per_competence_elements.organization_id%TYPE,
121 job_id per_competence_elements.job_id%TYPE,
122 position_id per_competence_elements.position_id%TYPE,
123 valid_grade_id per_competence_elements.valid_grade_id%TYPE,
124 business_group_id per_competence_elements.business_group_id%TYPE,
125 enterprise_id per_competence_elements.enterprise_id%TYPE,
126 structure_type hr_lookups.meaning%TYPE,
127 read_only_attr NUMBER,
128 detail_attr NUMBER,
129 competence_alias per_competences.competence_alias%TYPE,
130 GLOBAL VARCHAR2 (1),
131 description per_competences.description%TYPE,
132 date_from per_competences.date_from%TYPE,
133 certification_required per_competences.certification_required%TYPE,
134 behavioural_indicator per_competences.behavioural_indicator%TYPE,
135 low_step_value per_rating_levels_vl.step_value%TYPE,
136 low_step_name per_rating_levels_vl.NAME%TYPE,
137 high_step_value per_rating_levels_vl.step_value%TYPE,
138 high_step_name per_rating_levels_vl.NAME%TYPE,
139 lookup_code hr_lookups.lookup_code%TYPE,
140 minimum_proficiency VARCHAR2 (100),
141 maximum_proficiency VARCHAR2 (100),
142 TYPE per_competence_elements.TYPE%TYPE
143 );
144
145 TYPE sel_comp_tab IS TABLE OF bus_rules_comps
146 INDEX BY BINARY_INTEGER;
147
148 TYPE competences_rc IS RECORD (
149 competence_id per_competences.competence_id%TYPE
150 );
151
152 TYPE competences_tbl IS TABLE OF competences_rc
153 INDEX BY BINARY_INTEGER;
154
155 --
156 -- ----------------------------------------------------------------------------
157 -- |----------------------< initialize_logging >------------------------------|
158 -- ----------------------------------------------------------------------------
159 --
160 PROCEDURE initialize_logging (
161 p_action_parameter_group_id IN NUMBER,
162 p_log_output IN VARCHAR2
163 )
164 IS
165 --
166 -- Gets an action parameter value.
167 --
168 CURSOR csr_get_action_param (p_parameter_name IN VARCHAR2)
169 IS
170 SELECT pap.parameter_value
171 FROM pay_action_parameters pap
172 WHERE pap.parameter_name = p_parameter_name;
173
174 l_logging pay_action_parameters.parameter_value%TYPE;
175 l_max_errors pay_action_parameters.parameter_value%TYPE;
176 l_string VARCHAR2 (500);
177 BEGIN
178 --
179 -- Reset the package globals.
180 --
181 g_errbuf := NULL;
182 g_retcode := success;
183 g_max_errors := 0;
184 g_error_txt := '';
185
186 --
187 -- If the action parameter ID is passed in, the action param group
188 -- is set. Native dynamic PL/SQL is used to eliminate the
189 -- the dependency on the pay package procedure.
190 --
191 IF p_action_parameter_group_id IS NOT NULL
192 THEN
193 l_string :=
194 'BEGIN
195 pay_core_utils.set_pap_group_id(p_pap_group_id => '
196 || TO_CHAR (p_action_parameter_group_id)
197 || ');
198 END;';
199
200 EXECUTE IMMEDIATE l_string;
201 END IF;
202
203 --
204 IF (p_log_output = 'Y' AND fnd_global.conc_request_id > 0)
205 THEN
206 -- Call from concurrent program
207 g_dbg := TRUE;
208 g_dbg_type := g_fnd_log;
209
210 --
211 -- Get the Payroll Action logging parameter
212 --
213 OPEN csr_get_action_param (LOGGING);
214
215 FETCH csr_get_action_param
216 INTO l_logging;
217
218 CLOSE csr_get_action_param;
219
220 --
221 -- If logging is set to General in Payroll Action parameters, enable debugging.
222 --
223 IF (INSTR (NVL (l_logging, 'N'), 'G') <> 0)
224 THEN
225 g_log_level := g_debug_log;
226 ELSE
227 g_log_level := g_regular_log;
228 END IF;
229 ELSIF (p_log_output <> 'Y')
230 THEN
231 -- Call from API
232 IF (g_debug)
233 THEN
234 g_dbg := TRUE;
235 g_dbg_type := g_pipe;
236 g_log_level := g_debug_log;
237 END IF;
238 END IF;
239
240 --
241 -- Set the max number of errors allowed.
242 --
243 OPEN csr_get_action_param (max_errors);
244
245 FETCH csr_get_action_param
246 INTO l_max_errors;
247
248 CLOSE csr_get_action_param;
249
250 g_max_errors := NVL (TO_NUMBER (l_max_errors), 0);
251 END initialize_logging;
252
253 --
254 -- ----------------------------------------------------------------------------
255 -- |----------------------< op >----------------------------------------------|
256 -- ----------------------------------------------------------------------------
257 --
258 PROCEDURE op (
259 p_msg IN VARCHAR2,
260 p_log_level IN NUMBER,
261 p_location IN NUMBER DEFAULT NULL
262 )
263 IS
264 l_msg VARCHAR2 (32000) := p_msg;
265 BEGIN
266 IF ( g_dbg_type IS NOT NULL
267 AND p_msg IS NOT NULL
268 AND p_log_level <= g_log_level
269 )
270 THEN
271 --
272 -- Break the output into chunks of 70 characters.
273 --
274 WHILE LENGTH (l_msg) > 0
275 LOOP
276 IF g_dbg_type = g_pipe OR g_debug
277 THEN
278 IF p_location IS NOT NULL
279 THEN
280 hr_utility.set_location (SUBSTR (l_msg, 1, 70), p_location);
281 ELSE
282 hr_utility.TRACE (SUBSTR (l_msg, 1, 70));
283 END IF;
284 ELSIF g_dbg_type = g_fnd_log
285 THEN
286 IF p_location IS NOT NULL
287 THEN
288 fnd_file.put_line (fnd_file.LOG,
289 SUBSTR (l_msg, 1, 70)
290 || ', '
291 || TO_CHAR (p_location)
292 );
293 ELSE
294 fnd_file.put_line (fnd_file.LOG, SUBSTR (l_msg, 1, 70));
295 END IF;
296 END IF;
297
298 l_msg := SUBSTR (l_msg, 71);
299 END LOOP;
300 END IF;
301 END op;
302
303 --
304 -- ----------------------------------------------------------------------------
305 -- |-------------------------< chk_publishing_status >------------------------|
306 -- ----------------------------------------------------------------------------
307 -- {Start Of Comments}
308 --
309 -- Description:
310 -- Checks that the status code is a valid for Publish o Reverse Publish plan
311 -- action.
312 --
313 -- Prerequisites:
314 -- None.
315 --
316 -- In Arguments:
317 --
318 --
319 -- Post Success:
320 -- Processing continues if the status is valid.
321 --
322 -- Post Failure:
323 -- An application error is raised if the status code is not valid.
324 --
325 -- Access Status:
326 -- Internal Only.
327 --
328 -- {End Of Comments}
329 -- ----------------------------------------------------------------------------
330 PROCEDURE chk_publishing_status (
331 p_reverse_mode IN VARCHAR2,
332 p_status_code IN VARCHAR2
333 )
334 IS
335 -- Declare local variables
336 l_proc VARCHAR2 (72)
337 := g_package || 'chk_publishing_status';
338 e_status_check_failed EXCEPTION;
339 BEGIN
340 --
341 IF g_dbg
342 THEN
343 op ('Entering:' || l_proc, g_regular_log, 10);
344 END IF;
345
346 --
347 IF ( p_reverse_mode = 'N'
348 AND p_status_code NOT IN
349 ('DRAFT', 'UPDATED', 'SUBMITTED', 'RESUBMITTED')
350 )
351 THEN
352 -- Set the message name, so that exception handler can get translated text
353 IF g_dbg
354 THEN
355 op (l_proc, g_debug_log, 20);
356 END IF;
357
358 fnd_message.set_name ('PER', 'HR_50294_WPM_INV_PLAN_STS_PUB');
359 g_error_txt := NVL (fnd_message.get, 'HR_50294_WPM_INV_PLAN_STS_PUB');
360 RAISE e_status_check_failed;
361 ELSIF (p_reverse_mode <> 'N' AND p_status_code NOT IN ('PUBLISHED'))
362 THEN
363 -- Set the message name, so that exception handler can get translated text
364 IF g_dbg
365 THEN
366 op (l_proc, g_debug_log, 30);
367 END IF;
368
369 fnd_message.set_name ('PER', 'HR_50295_WPM_INV_PLAN_STS_RPUB');
370 g_error_txt :=
371 NVL (fnd_message.get, 'HR_50295_WPM_INV_PLAN_STS_RPUB');
372 RAISE e_status_check_failed;
373 END IF;
374
375 --
376 IF g_dbg
377 THEN
378 op ('Leaving:' || l_proc, g_regular_log, 80);
379 END IF;
380 --
381 EXCEPTION
382 WHEN OTHERS
383 THEN
384 IF g_dbg
385 THEN
386 op ('Leaving:' || l_proc, g_regular_log, 90);
387 END IF;
388
389 --
390 g_retcode := warning;
391 g_errbuf := g_error_txt;
392
393 IF g_dbg
394 THEN
395 op (g_error_txt, g_regular_log);
396 END IF;
397
398 IF g_dbg
399 THEN
400 op (SQLERRM, g_regular_log);
401 END IF;
402
403 RAISE;
404 END chk_publishing_status;
405
406 --
407 -- ----------------------------------------------------------------------------
408 -- |---------------------< populate_plan_apprsl_periods >---------------------|
409 -- ----------------------------------------------------------------------------
410 -- {Start Of Comments}
411 --
412 -- Description:
413 -- Populates the appraisal periods for a given plan.
414 --
415 -- Prerequisites:
416 -- None.
417 --
418 -- In Arguments:
419 --
420 --
421 -- Post Success:
422 -- Processing continues on successful population.
423 --
424 -- Post Failure:
425 -- An application error is raised if population fails.
426
427 --
428 -- Access Status:
429 -- Internal Use Only.
430 --
431 -- {End Of Comments}
432 -- ----------------------------------------------------------------------------
433 PROCEDURE populate_plan_apprsl_periods (
434 p_plan_id IN NUMBER,
435 p_appr_period_id IN NUMBER
436 )
437 IS
438 -- Declare local variables
439 l_proc VARCHAR2 (72) := g_package || 'populate_plan_apprsl_periods';
440
441 -- Current scorecard objectives
442 CURSOR csr_plan_apprsl_pds
443 IS
444 SELECT appraisal_period_id, appraisal_template_id, start_date,
445 end_date, task_start_date, task_end_date, initiator_code,
446 appraisal_system_type, auto_conc_process,
447 days_before_task_st_dt, appraisal_assmt_status,
448 appraisal_type, participation_type,
449 questionnaire_template_id
450 FROM per_appraisal_periods pap
451 WHERE pap.plan_id = p_plan_id
452 AND pap.appraisal_period_id = p_appr_period_id;
453 --
454 BEGIN
455 --
456 IF g_dbg
457 THEN
458 op ('Entering:' || l_proc, g_regular_log, 10);
459 END IF;
460
461 --
462
463 --
464 -- Get the plan appraisal periods
465 --
466 OPEN csr_plan_apprsl_pds;
467
468 FETCH csr_plan_apprsl_pds
469 BULK COLLECT INTO g_plan_aprsl_pds_tbl;
470
471 CLOSE csr_plan_apprsl_pds;
472
473 --
474 IF g_dbg
475 THEN
476 op ('Leaving:' || l_proc, g_regular_log, 80);
477 END IF;
478 --
479 EXCEPTION
480 WHEN OTHERS
481 THEN
482 IF g_dbg
483 THEN
484 op ('Leaving:' || l_proc, g_regular_log, 90);
485 END IF;
486
487 --
488 fnd_message.set_name ('PER', 'HR_50297_WPM_CP_ERROR');
489 g_cp_error_txt := NVL (fnd_message.get, 'HR_50297_WPM_CP_ERROR');
490 g_retcode := warning;
491 g_errbuf := g_cp_error_txt;
492 g_num_errors := g_num_errors + 1;
493
494 IF g_dbg
495 THEN
496 op (g_error_txt, g_regular_log);
497 END IF;
498
499 IF g_dbg
500 THEN
501 op (SQLERRM, g_regular_log);
502 END IF;
503
504 --
505 -- If the max number of errors has been exceeded, raise the error and
506 -- terminate processing of this plan.
507 --
508 IF g_num_errors > g_max_errors
509 THEN
510 fnd_message.set_name ('PER', 'HR_50298_WPM_MAX_ERRORS');
511 g_error_txt := NVL (fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
512 g_retcode := error;
513 g_errbuf := g_error_txt;
514 RAISE;
515 END IF;
516 END populate_plan_apprsl_periods;
517
518 --
519 FUNCTION get_appraisal_config_params (
520 p_appr_initiator_code IN per_appraisal_periods.initiator_code%TYPE,
521 p_function_id IN OUT NOCOPY fnd_form_functions.function_id%TYPE,
522 p_function_name IN OUT NOCOPY fnd_form_functions.function_name%TYPE,
523 p_func_parameters IN OUT NOCOPY fnd_form_functions.PARAMETERS%TYPE,
524 p_appraisal_sys_type IN OUT NOCOPY per_appraisals.appraisal_system_status%TYPE
525 )
526 RETURN BOOLEAN
527 IS
528 l_resp_id NUMBER;
529 l_appraisal_mgr_menu fnd_menus.menu_name%TYPE DEFAULT NULL;
530 l_appraisal_empl_menu fnd_menus.menu_name%TYPE DEFAULT NULL;
531 l_selected_menu fnd_menus.menu_name%TYPE DEFAULT NULL;
532 l_function_id fnd_form_functions.function_id%TYPE;
533 l_function_name fnd_form_functions.function_name%TYPE;
534 l_initiation_type VARCHAR2 (10) DEFAULT NULL;
535 l_func_params fnd_form_functions.PARAMETERS%TYPE;
536 l_system_type VARCHAR2 (50);
537 l_menu_id fnd_menus.menu_id%TYPE;
538
539 CURSOR get_appraisal_function (
540 p_menu_name fnd_menus.menu_name%TYPE,
541 p_search_func VARCHAR2
542 )
543 IS
544 SELECT menu_functions.function_id, ff.function_name, ff.PARAMETERS,
545 menu_id
546 FROM fnd_compiled_menu_functions menu_functions,
547 fnd_form_functions ff
548 WHERE menu_id = (SELECT menu_id
549 FROM fnd_menus
550 WHERE menu_name = p_menu_name)
551 AND ff.function_id = menu_functions.function_id
552 AND ff.PARAMETERS LIKE p_search_func;
553 BEGIN
554 -- to be derived from plan id
555
556 /*
557 fnd_global.apps_initialize(user_id =>1922,
558 resp_id =>21540,
559 resp_appl_id=> 800);
560 */
561 IF g_dbg
562 THEN
563 op ('p_appr_initiator_code = ' || p_appr_initiator_code,
564 g_debug_log);
565 END IF;
566
567 IF g_dbg
568 THEN
569 op ('login person = ' || fnd_global.user_id, g_debug_log);
570 END IF;
571
572 IF g_dbg
573 THEN
574 op ('login name = ' || fnd_global.user_name, g_debug_log);
575 END IF;
576
577 IF g_dbg
578 THEN
579 op ('resp id = ' || fnd_global.resp_id, g_debug_log);
580 END IF;
581
582 IF g_dbg
583 THEN
584 op (' p_appraisal_sys_type = ' || p_appraisal_sys_type, g_debug_log);
585 END IF;
586
587 l_initiation_type := 'MGR';
588 l_resp_id := fnd_global.resp_id;
589 l_appraisal_mgr_menu :=
590 fnd_profile.value_specific (NAME => 'HR_MANAGER_APPRAISALS_MENU',
591 responsibility_id => l_resp_id
592 );
593
594 IF g_dbg
595 THEN
596 op ('MGR MENU = ' || fnd_profile.VALUE ('HR_MANAGER_APPRAISALS_MENU'),
597 g_debug_log
598 );
599 END IF;
600
601 IF g_dbg
602 THEN
603 op ('EMP MENU = ' || fnd_profile.VALUE ('HR_WORKER_APPRAISALS_MENU'),
604 g_debug_log
605 );
606 END IF;
607
608 IF g_dbg
609 THEN
610 op ('l_appraisal_mgr_menu = ' || l_appraisal_mgr_menu, g_debug_log);
611 END IF;
612
613 l_appraisal_empl_menu :=
614 fnd_profile.value_specific (NAME => 'HR_WORKER_APPRAISALS_MENU',
615 responsibility_id => l_resp_id
616 );
617
618 IF g_dbg
619 THEN
620 op ('l_appraisal_empl_menu = ' || l_appraisal_empl_menu,
621 g_debug_log);
622 END IF;
623
624 IF (p_appr_initiator_code = 'MGR' AND l_appraisal_mgr_menu IS NOT NULL
625 )
626 THEN
627 l_selected_menu := l_appraisal_mgr_menu;
628 p_appraisal_sys_type :=
629 p_appr_initiator_code || p_appraisal_sys_type;
630 l_system_type := '%' || p_appraisal_sys_type || '%';
631 ELSIF ( p_appr_initiator_code = 'EMP'
632 AND l_appraisal_empl_menu IS NOT NULL
633 )
634 THEN
635 l_selected_menu := l_appraisal_empl_menu;
636 p_appraisal_sys_type :=
637 p_appr_initiator_code || p_appraisal_sys_type;
638 l_system_type := '%' || p_appraisal_sys_type || '%';
639 END IF;
640
641 IF g_dbg
642 THEN
643 op ('l_selected_menu = ' || l_selected_menu, g_debug_log);
644 END IF;
645
646 OPEN get_appraisal_function (l_selected_menu, l_system_type);
647
648 FETCH get_appraisal_function
649 INTO l_function_id, l_function_name, l_func_params, l_menu_id;
650
651 IF get_appraisal_function%NOTFOUND
652 THEN
653 RETURN FALSE;
654 ELSE
655 p_function_id := l_function_id;
656 p_function_name := l_function_name;
657 p_func_parameters :=
658 l_func_params
659 || '&'
660 || 'pFunctionId='
661 || l_function_id
662 || '&'
663 || 'pMenuId='
664 || l_menu_id
665 || '&'
666 || 'OAFunc='
667 || l_function_name;
668 END IF;
669
670 IF g_dbg
671 THEN
672 op ('l_function_id = ' || l_function_id, g_debug_log);
673 END IF;
674
675 IF g_dbg
676 THEN
677 op ('l_function_name = ' || l_function_name, g_debug_log);
678 END IF;
679
680 RETURN TRUE;
681 EXCEPTION
682 WHEN OTHERS
683 THEN
684 IF g_dbg
685 THEN
686 op (SQLERRM, g_regular_log);
687 END IF;
688
689 RAISE;
690 END;
691
692 PROCEDURE apply_overridding_rules (
693 p_enterprise_id IN NUMBER,
694 p_organization_id IN NUMBER,
695 p_job_id IN NUMBER,
696 p_position_id IN NUMBER,
697 p_skip_duplicate BOOLEAN,
698 l_sel_comp_table IN OUT NOCOPY sel_comp_tab
699 )
700 IS
701 --8239025 Modified the cursors
702 CURSOR get_asgn_req_comps_bus (p_enterprise_id NUMBER)
703 IS
704 SELECT pc.NAME competence_name, pce.competence_id,
705 pce.competence_element_id, pce.mandatory,
706 pce.proficiency_level_id, pce.high_proficiency_level_id,
707 pce.organization_id, NVL (pce.job_id, -1) job_id,
708 NVL (pce.position_id, -1) position_id, pce.valid_grade_id,
709 NVL (pce.business_group_id, -1) business_group_id,
710 pce.enterprise_id, hrl.meaning structure_type,
711 DECODE (job_id,
712 NULL, DECODE (position_id, NULL, 1, 0),
713 0
714 ) read_only_attr,
715 0 detail_attr, pc.competence_alias,
716 DECODE (pc.business_group_id, NULL, 'Y', 'N') GLOBAL,
717 pc.description, pc.date_from, pc.certification_required,
718 pc.behavioural_indicator, r1.step_value low_step_value,
719 r1.NAME low_step_name, r2.step_value high_step_value,
720 r2.NAME high_step_name, hrl.lookup_code,
721 DECODE (r1.step_value,
722 NULL, NULL,
723 r1.step_value || ' - ' || r1.NAME
724 ) minimum_proficiency,
725 DECODE (r2.step_value,
726 NULL, NULL,
727 r2.step_value || ' - ' || r2.NAME
728 ) maximum_proficiency,
729 pce.TYPE
730 FROM per_competence_elements pce,
731 per_competences_vl pc,
732 hr_lookups hrl,
733 per_rating_levels_vl r1,
734 per_rating_levels_vl r2
735 WHERE pce.TYPE = 'REQUIREMENT'
736 AND pce.competence_id = pc.competence_id
737 AND TRUNC (SYSDATE) BETWEEN NVL (pce.effective_date_from,
738 TRUNC (SYSDATE)
739 )
740 AND NVL (pce.effective_date_to,
741 TRUNC (SYSDATE)
742 )
743 AND hrl.lookup_type = 'STRUCTURE_TYPE'
744 AND hrl.lookup_code = 'BUS'
745 AND pce.proficiency_level_id = r1.rating_level_id(+)
746 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
747 AND pce.business_group_id = p_enterprise_id
748 AND pce.enterprise_id = p_enterprise_id
749 AND pce.job_id IS NULL
750 AND pce.organization_id IS NULL
751 AND pce.position_id IS NULL;
752
753 CURSOR get_asgn_req_comps_org (p_in_org_id NUMBER, p_enterprise_id NUMBER)
754 IS
755 SELECT pc.NAME competence_name, pce.competence_id,
756 pce.competence_element_id, pce.mandatory,
757 pce.proficiency_level_id, pce.high_proficiency_level_id,
758 pce.organization_id, NVL (pce.job_id, -1) job_id,
759 NVL (pce.position_id, -1) position_id, pce.valid_grade_id,
760 NVL (pce.business_group_id, -1) business_group_id,
761 pce.enterprise_id, hrl.meaning structure_type,
762 DECODE (job_id,
763 NULL, DECODE (position_id, NULL, 1, 0),
764 0
765 ) read_only_attr,
766 0 detail_attr, pc.competence_alias,
767 DECODE (pc.business_group_id, NULL, 'Y', 'N') GLOBAL,
768 pc.description, pc.date_from, pc.certification_required,
769 pc.behavioural_indicator, r1.step_value low_step_value,
770 r1.NAME low_step_name, r2.step_value high_step_value,
771 r2.NAME high_step_name, hrl.lookup_code,
772 DECODE (r1.step_value,
773 NULL, NULL,
774 r1.step_value || ' - ' || r1.NAME
775 ) minimum_proficiency,
776 DECODE (r2.step_value,
777 NULL, NULL,
778 r2.step_value || ' - ' || r2.NAME
779 ) maximum_proficiency,
780 pce.TYPE
781 FROM per_competence_elements pce,
782 per_competences_vl pc,
783 hr_lookups hrl,
784 per_rating_levels_vl r1,
785 per_rating_levels_vl r2
786 WHERE pce.TYPE = 'REQUIREMENT'
787 AND pce.competence_id = pc.competence_id
788 AND TRUNC (SYSDATE) BETWEEN NVL (pce.effective_date_from,
789 TRUNC (SYSDATE)
790 )
791 AND NVL (pce.effective_date_to,
792 TRUNC (SYSDATE)
793 )
794 AND hrl.lookup_type = 'STRUCTURE_TYPE'
795 AND hrl.lookup_code = 'ORG'
796 AND pce.proficiency_level_id = r1.rating_level_id(+)
797 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
798 AND pce.business_group_id = p_enterprise_id
799 AND pce.organization_id = p_in_org_id
800 AND pce.enterprise_id IS NULL
801 AND pce.job_id IS NULL
802 AND pce.position_id IS NULL;
803
804 CURSOR get_asgn_req_comps_pos (p_in_pos_id NUMBER, p_enterprise_id NUMBER)
805 IS
806 SELECT pc.NAME competence_name, pce.competence_id,
807 pce.competence_element_id, pce.mandatory,
808 pce.proficiency_level_id, pce.high_proficiency_level_id,
809 pce.organization_id, NVL (pce.job_id, -1) job_id,
810 NVL (pce.position_id, -1) position_id, pce.valid_grade_id,
811 NVL (pce.business_group_id, -1) business_group_id,
812 pce.enterprise_id, hrl.meaning structure_type,
813 DECODE (job_id,
814 NULL, DECODE (position_id, NULL, 1, 0),
815 0
816 ) read_only_attr,
817 0 detail_attr, pc.competence_alias,
818 DECODE (pc.business_group_id, NULL, 'Y', 'N') GLOBAL,
819 pc.description, pc.date_from, pc.certification_required,
820 pc.behavioural_indicator, r1.step_value low_step_value,
821 r1.NAME low_step_name, r2.step_value high_step_value,
822 r2.NAME high_step_name, hrl.lookup_code,
823 DECODE (r1.step_value,
824 NULL, NULL,
825 r1.step_value || ' - ' || r1.NAME
826 ) minimum_proficiency,
827 DECODE (r2.step_value,
828 NULL, NULL,
829 r2.step_value || ' - ' || r2.NAME
830 ) maximum_proficiency,
831 pce.TYPE
832 FROM per_competence_elements pce,
833 per_competences_vl pc,
834 hr_lookups hrl,
835 per_rating_levels_vl r1,
836 per_rating_levels_vl r2
837 WHERE pce.TYPE = 'REQUIREMENT'
838 AND pce.competence_id = pc.competence_id
839 AND TRUNC (SYSDATE) BETWEEN NVL (pce.effective_date_from,
840 TRUNC (SYSDATE)
841 )
842 AND NVL (pce.effective_date_to,
843 TRUNC (SYSDATE)
844 )
845 AND hrl.lookup_type = 'STRUCTURE_TYPE'
846 AND hrl.lookup_code = 'POS'
847 AND pce.proficiency_level_id = r1.rating_level_id(+)
848 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
849 AND pce.business_group_id = p_enterprise_id
850 AND pce.position_id = p_in_pos_id
851 AND pce.organization_id IS NULL
852 AND pce.job_id IS NULL
853 AND pce.enterprise_id IS NULL;
854
855 CURSOR get_asgn_req_comps_job (p_in_job_id NUMBER, p_enterprise_id NUMBER)
856 IS
857 SELECT pc.NAME competence_name, pce.competence_id,
858 pce.competence_element_id, pce.mandatory,
859 pce.proficiency_level_id, pce.high_proficiency_level_id,
860 pce.organization_id, NVL (pce.job_id, -1) job_id,
861 NVL (pce.position_id, -1) position_id, pce.valid_grade_id,
862 NVL (pce.business_group_id, -1) business_group_id,
863 pce.enterprise_id, hrl.meaning structure_type,
864 DECODE (job_id,
865 NULL, DECODE (position_id, NULL, 1, 0),
866 0
867 ) read_only_attr,
868 0 detail_attr, pc.competence_alias,
869 DECODE (pc.business_group_id, NULL, 'Y', 'N') GLOBAL,
870 pc.description, pc.date_from, pc.certification_required,
871 pc.behavioural_indicator, r1.step_value low_step_value,
872 r1.NAME low_step_name, r2.step_value high_step_value,
873 r2.NAME high_step_name, hrl.lookup_code,
874 DECODE (r1.step_value,
875 NULL, NULL,
876 r1.step_value || ' - ' || r1.NAME
877 ) minimum_proficiency,
878 DECODE (r2.step_value,
879 NULL, NULL,
880 r2.step_value || ' - ' || r2.NAME
881 ) maximum_proficiency,
882 pce.TYPE
883 FROM per_competence_elements pce,
884 per_competences_vl pc,
885 hr_lookups hrl,
886 per_rating_levels_vl r1,
887 per_rating_levels_vl r2
888 WHERE pce.TYPE = 'REQUIREMENT'
889 AND pce.competence_id = pc.competence_id
890 AND TRUNC (SYSDATE) BETWEEN NVL (pce.effective_date_from,
891 TRUNC (SYSDATE)
892 )
893 AND NVL (pce.effective_date_to,
894 TRUNC (SYSDATE)
895 )
896 AND hrl.lookup_type = 'STRUCTURE_TYPE'
897 AND hrl.lookup_code = 'JOB'
898 AND pce.proficiency_level_id = r1.rating_level_id(+)
899 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
900 AND pce.business_group_id = p_enterprise_id
901 AND pce.job_id = p_in_job_id
902 AND pce.organization_id IS NULL
903 AND pce.position_id IS NULL
904 AND pce.enterprise_id IS NULL;
905
906 l_mat_comp_table sel_comp_tab;
907 i INTEGER DEFAULT 0;
908 l_temp_comp_table sel_comp_tab;
909 l_comp_index INTEGER;
910 issamecompetence BOOLEAN DEFAULT FALSE;
911 issamestructuretype BOOLEAN DEFAULT FALSE;
912 isignore BOOLEAN DEFAULT FALSE;
913 isbessential BOOLEAN DEFAULT FALSE;
914 isbdesired BOOLEAN DEFAULT FALSE;
915 markouterrowforignore BOOLEAN;
916 isessentialdesired BOOLEAN;
917 BEGIN
918 IF g_dbg
919 THEN
920 op ('p_enterprise_id = ' || p_enterprise_id, g_debug_log);
921 END IF;
922
923 IF g_dbg
924 THEN
925 op ('p_business_group_id = ' || p_enterprise_id, g_debug_log);
926 END IF;
927
928 IF g_dbg
929 THEN
930 op ('p_organization_id = ' || p_organization_id, g_debug_log);
931 END IF;
932
933 IF g_dbg
934 THEN
935 op ('p_job_id = ' || p_job_id, g_debug_log);
936 END IF;
937
938 IF g_dbg
939 THEN
940 op ('p_position_id = ' || p_position_id, g_debug_log);
941 END IF;
942
943 --8239025 Fix Starts
944 FOR pos_rec IN get_asgn_req_comps_pos (p_position_id, p_enterprise_id)
945 LOOP
946 l_temp_comp_table (pos_rec.competence_id) := pos_rec;
947 END LOOP;
948
949 FOR job_rec IN get_asgn_req_comps_job (p_job_id, p_enterprise_id)
950 LOOP
951 IF NOT l_temp_comp_table.EXISTS (job_rec.competence_id)
952 THEN
953 l_temp_comp_table (job_rec.competence_id) := job_rec;
954 END IF;
955 END LOOP;
956
957 FOR org_rec IN get_asgn_req_comps_org (p_organization_id,
958 p_enterprise_id
959 )
960 LOOP
961 IF NOT l_temp_comp_table.EXISTS (org_rec.competence_id)
962 THEN
963 l_temp_comp_table (org_rec.competence_id) := org_rec;
964 END IF;
965 END LOOP;
966
967 FOR bus_rec IN get_asgn_req_comps_bus (p_enterprise_id)
968 LOOP
969 IF NOT l_temp_comp_table.EXISTS (bus_rec.competence_id)
970 THEN
971 l_temp_comp_table (bus_rec.competence_id) := bus_rec;
972 END IF;
973 END LOOP;
974
975 l_comp_index := l_temp_comp_table.FIRST;
976 i := 1;
977
978 WHILE (l_comp_index IS NOT NULL)
979 LOOP
980 IF g_dbg
981 THEN
982 op ( ' from overriding comp = '
983 || l_temp_comp_table (l_comp_index).NAME,
984 g_debug_log
985 );
986 END IF;
987
988 l_sel_comp_table (i) := l_temp_comp_table (l_comp_index);
989 l_comp_index := l_temp_comp_table.NEXT (l_comp_index);
990 i := i + 1;
991 END LOOP;
992 END apply_overridding_rules;
993
994 --
995 -- ----------------------------------------------------------------------------
996 -- |----------------------< create_appraisal_for_person >---------------------|
997 -- ----------------------------------------------------------------------------
998 -- {Start Of Comments}
999 --
1000 -- Description:
1001 -- Creates Appraisal for a given person when plan is published.
1002 --
1003 -- Prerequisites:
1004 -- None.
1005 --
1006 -- In Arguments:
1007 --
1008 --
1009 -- Post Success:
1010 -- Processing continues if Appraisal is created.
1011 --
1012 -- Post Failure:
1013 -- An application error is raised if scorecard is not created.
1014 --
1015 -- Access Status:
1016 -- Internal Only.
1017 --
1018 -- {End Of Comments}
1019 -- ----------------------------------------------------------------------------
1020 PROCEDURE create_appraisal_for_person (
1021 p_score_card_id per_personal_scorecards.scorecard_id%TYPE,
1022 p_appraisal_templ_id per_appraisal_templates.appraisal_template_id%TYPE,
1023 p_effective_date DATE,
1024 p_appraisal_start_date DATE,
1025 p_appraisal_end_date DATE,
1026 p_appraisal_status per_appraisals.status%TYPE
1027 DEFAULT 'PLANNED',
1028 p_type per_appraisals.TYPE%TYPE
1029 DEFAULT NULL,
1030 p_appraisal_date per_appraisals.appraisal_date%TYPE,
1031 -- p_appraisal_system_status per_appraisals.appraisal_system_status%TYPE,
1032 p_plan_id NUMBER,
1033 p_next_appraisal_date per_appraisals.next_appraisal_date%TYPE
1034 DEFAULT NULL,
1035 p_status per_appraisals.status%TYPE
1036 DEFAULT NULL,
1037 p_comments per_appraisals.comments%TYPE
1038 DEFAULT NULL,
1039 p_appraisee_access per_appraisals.appraisee_access%TYPE
1040 DEFAULT NULL,
1041 p_appraisal_initiator per_appraisal_periods.initiator_code%TYPE,
1042 p_appraisal_system_type IN per_appraisal_periods.appraisal_system_type%TYPE,
1043 p_participation_type IN per_appraisal_periods.participation_type%TYPE
1044 DEFAULT NULL,
1045 p_questionnaire_template_id IN per_appraisal_periods.questionnaire_template_id%TYPE
1046 DEFAULT NULL,
1047 p_return_status OUT NOCOPY VARCHAR2
1048 )
1049 IS
1050 CURSOR get_scorecard_info (
1051 p_scorecard_id per_personal_scorecards.scorecard_id%TYPE
1052 )
1053 IS
1054 SELECT scorecard_id, assignment_id, person_id, scorecard_name
1055 FROM per_personal_scorecards
1056 WHERE scorecard_id = p_scorecard_id;
1057
1058 --changed cursor for fixing 6924829
1059 CURSOR get_assignment_info (
1060 p_person_id per_all_people_f.person_id%TYPE
1061 )
1062 IS
1063 SELECT paf.assignment_id, paf.business_group_id, paf.grade_id,
1064 paf.position_id, paf.job_id, paf.organization_id,
1065 paf.supervisor_id, paf.effective_start_date,
1066 pps.date_start empl_start_date,
1067 ppf.effective_end_date empl_end_date, ppf.person_id
1068 FROM per_all_assignments_f paf,
1069 per_all_people_f ppf,
1070 per_periods_of_service pps
1071 WHERE paf.person_id = p_person_id --8780710 bug fix
1072 AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
1073 AND paf.effective_end_date
1074 AND paf.person_id = ppf.person_id
1075 AND paf.assignment_type = 'E'
1076 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
1077 AND ppf.effective_end_date
1078 AND pps.period_of_service_id = paf.period_of_service_id
1079 AND paf.primary_flag = 'Y'
1080 UNION ALL
1081 SELECT paf.assignment_id, paf.business_group_id, paf.grade_id,
1082 paf.position_id, paf.job_id, paf.organization_id,
1083 paf.supervisor_id, paf.effective_start_date,
1084 pps.date_start empl_start_date,
1085 ppf.effective_end_date empl_end_date, ppf.person_id
1086 FROM per_all_assignments_f paf,
1087 per_all_people_f ppf,
1088 per_periods_of_placement pps
1089 WHERE paf.person_id = p_person_id --8780710 bug fix
1090 AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
1091 AND paf.effective_end_date
1092 AND paf.assignment_type = 'C'
1093 AND paf.person_id = ppf.person_id
1094 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
1095 AND ppf.effective_end_date
1096 AND pps.date_start = paf.period_of_placement_date_start
1097 AND pps.person_id = paf.person_id
1098 AND paf.primary_flag = 'Y'; --8780710 bug fix
1099
1100 -- 8529866 Bug Fix
1101 CURSOR get_ma_start_date (p_ma_person_id per_people_f.person_id%TYPE)
1102 IS
1103 SELECT date_start
1104 FROM per_periods_of_service
1105 WHERE person_id = p_ma_person_id
1106 AND TRUNC (SYSDATE) BETWEEN date_start
1107 AND NVL (actual_termination_date,
1108 TRUNC (SYSDATE)
1109 )
1110 UNION ALL
1111 SELECT date_start
1112 FROM per_periods_of_placement
1113 WHERE person_id = p_ma_person_id
1114 AND TRUNC (SYSDATE) BETWEEN date_start
1115 AND NVL (actual_termination_date,
1116 TRUNC (SYSDATE)
1117 );
1118
1119 CURSOR get_appraisal_templ_info (
1120 p_appraisal_templ_id per_appraisals.appraisal_template_id%TYPE
1121 )
1122 IS
1123 SELECT appraisal_template_id, assessment_type_id,
1124 objective_asmnt_type_id, business_group_id,
1125 show_competency_ratings, show_objective_ratings,
1126 show_questionnaire_info, show_participant_details,
1127 show_participant_ratings, show_participant_names,
1128 show_overall_ratings, provide_overall_feedback,
1129
1130 -- Bug7393131
1131 show_overall_comments
1132 FROM per_appraisal_templates
1133 WHERE appraisal_template_id = p_appraisal_templ_id;
1134
1135 CURSOR get_assess_templ_comps (
1136 p_assess_type_id per_competence_elements.assessment_type_id%TYPE
1137 )
1138 IS
1139 SELECT ce.competence_id, ce.competence_element_id, ce.TYPE,
1140 ce.parent_competence_element_id, c.NAME,
1141 RANK () OVER (PARTITION BY ce.competence_id ORDER BY ce.competence_element_id)
1142 RANK
1143 FROM per_competence_elements a,
1144 per_competence_elements ce,
1145 per_competences_vl c
1146 WHERE a.assessment_type_id = p_assess_type_id
1147 AND a.TYPE = 'ASSESSMENT_GROUP'
1148 AND (NVL (c.date_from, TRUNC (SYSDATE)) <= TRUNC (SYSDATE))
1149 AND NVL (c.date_to, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
1150 AND a.competence_element_id = ce.parent_competence_element_id
1151 AND ce.competence_id = c.competence_id;
1152
1153 CURSOR check_default_job_competency (
1154 p_assessment_type_id per_assessment_types.assessment_type_id%TYPE
1155 )
1156 IS
1157 SELECT default_job_competencies
1158 FROM per_assessment_types
1159 WHERE assessment_type_id = p_assessment_type_id;
1160
1161 CURSOR get_scorecard_objectives (
1162 p_scorecard_id per_objectives.scorecard_id%TYPE
1163 )
1164 IS
1165 SELECT objective_id, scorecard_id, object_version_number, NAME
1166 FROM per_objectives
1167 WHERE scorecard_id = p_scorecard_id
1168 AND appraise_flag = 'Y'
1169 AND appraisal_id IS NULL;
1170
1171 CURSOR get_assess_templ_info (
1172 p_assess_templ per_appraisal_templates.assessment_type_id%TYPE
1173 )
1174 IS
1175 SELECT default_job_competencies, assessment_type_id
1176 FROM per_assessment_types
1177 WHERE assessment_type_id = p_assess_templ;
1178
1179 CURSOR get_sec_asg_supervisors (
1180 p_person_id per_all_people_f.person_id%TYPE
1181 )
1182 IS
1183 SELECT DISTINCT (supervisor_id)
1184 FROM per_all_assignments_f
1185 WHERE person_id = p_person_id AND primary_flag <> 'Y';
1186
1187 CURSOR find_appraisal (
1188 p_plan_id per_perf_mgmt_plans.plan_id%TYPE,
1189 p_appr_prd_st_dt per_appraisals.appraisal_period_start_date%TYPE,
1190 p_appr_prd_ed_dt per_appraisals.appraisal_period_end_date%TYPE,
1191 p_appraisee_person_id per_appraisals.appraisee_person_id%TYPE,
1192 -- p_appraiser_person_id per_appraisals.appraiser_person_id%TYPE,
1193 -- p_main_appraiser_id per_appraisals.main_appraiser_id%TYPE,
1194 p_appr_templ_id per_appraisals.appraisal_template_id%TYPE
1195 )
1196 IS
1197 SELECT appraisal_id, appraisal_system_status
1198 FROM per_appraisals
1199 WHERE plan_id = p_plan_id
1200 AND appraisal_period_start_date = p_appr_prd_st_dt
1201 --5194541 to_date(p_appr_prd_st_dt,'RRRR-MM-DD')
1202 AND appraisal_period_end_date = p_appr_prd_ed_dt
1203 --5194541 to_date(p_appr_prd_ed_dt,'RRRR-MM-DD')
1204 AND appraisee_person_id = p_appraisee_person_id
1205 AND appraisal_system_status <> 'TRANSFER_OUT' -- 7321947
1206 -- and appraiser_person_id = p_appraiser_person_id
1207 -- and main_appraiser_id = p_main_appraiser_id
1208 AND appraisal_template_id = p_appr_templ_id;
1209
1210 l_appraisal_status VARCHAR2 (20);
1211 l_scorecard_info scorecard_info;
1212 no_score_card_with_this_id EXCEPTION;
1213 l_assignment_info assignment_info;
1214 no_assignment_with_this_id EXCEPTION;
1215 l_ma_start_date per_appraisals.appraisal_date%TYPE;
1216 l_appraisal_ovn per_appraisals.object_version_number%TYPE;
1217 l_apprl_return_status VARCHAR2 (10) DEFAULT NULL;
1218 l_assess_comp_return_status VARCHAR2 (10) DEFAULT NULL;
1219 l_assess_obj_return_status VARCHAR2 (10) DEFAULT NULL;
1220 l_apprl_id per_appraisals.appraisal_id%TYPE
1221 DEFAULT NULL;
1222 l_apprl_templ_info appraisal_templ_info;
1223 no_apprl_templ_with_this_id EXCEPTION;
1224 l_assessment_comp_id per_assessments.assessment_id%TYPE;
1225 l_assessment_obj_id per_assessments.assessment_id%TYPE;
1226 l_assessment_comp_ovn per_assessments.object_version_number%TYPE;
1227 l_assessment_obj_ovn per_assessments.object_version_number%TYPE;
1228 l_assess_comps assess_comps_info;
1229 l_check_default_job_comps VARCHAR2 (2) DEFAULT '';
1230 l_comp_ele_id per_competence_elements.competence_element_id%TYPE;
1231 l_comp_ovn per_competence_elements.object_version_number%TYPE;
1232 l_return_status VARCHAR2 (10) DEFAULT '';
1233 l_competence_error VARCHAR2 (1000) DEFAULT NULL;
1234 module_name VARCHAR2 (100)
1235 DEFAULT 'MASS APPRAISAL CREATION';
1236 l_error_message VARCHAR2 (1000) DEFAULT NULL;
1237 appraisal_creation_error EXCEPTION;
1238 assess_comp_error EXCEPTION;
1239 assess_obj_error EXCEPTION;
1240 l_appraisal_sys_type per_appraisal_periods.appraisal_system_type%TYPE;
1241 function_360_exists BOOLEAN DEFAULT FALSE;
1242 appraisal_system_type_error EXCEPTION;
1243 l_weighting_over_100_warning BOOLEAN;
1244 l_weighting_appraisal_warning BOOLEAN;
1245 no_apprl_function_defined EXCEPTION;
1246 l_function_id fnd_form_functions.function_id%TYPE;
1247 l_function_name fnd_form_functions.function_name%TYPE;
1248 l_func_params fnd_form_functions.PARAMETERS%TYPE;
1249 l_object_id NUMBER;
1250 l_assess_comps_processed competences_tbl;
1251 z PLS_INTEGER;
1252 l_def_job_comps sel_comp_tab;
1253 appraisal_setup_issue EXCEPTION;
1254 l_appraiser_id per_people_f.person_id%TYPE;
1255 l_main_appraiser_id per_people_f.person_id%TYPE;
1256 l_found_appraisal_id per_appraisals.appraisal_id%TYPE;
1257 l_found_appraisal BOOLEAN;
1258 -- Declare local variables
1259 l_proc VARCHAR2 (72)
1260 := g_package || 'create_appraisal_for_person';
1261 --
1262 l_scorecard_id NUMBER;
1263 l_object_version_number NUMBER;
1264 --l_status_code varchar2(30);
1265 l_duplicate_name_warning BOOLEAN;
1266 l_found_comp BOOLEAN;
1267 --
1268 l_templ_def_job_comps per_assessment_types.default_job_competencies%TYPE;
1269 l_assess_type_id per_assessment_types.assessment_type_id%TYPE;
1270 l_appraisal_period_start_date per_appraisals.appraisal_period_start_date%TYPE;
1271 l_appraisal_date per_appraisals.appraisal_date%TYPE;
1272 l_participant_id per_participants.participant_id%TYPE;
1273 l_default_participant VARCHAR2 (30)
1274 := NVL (p_participation_type, 'N');
1275
1276 --Bug Fix 12663201
1277 CURSOR csr_comp_ass_exists (
1278 p_appraisal_id IN NUMBER,
1279 p_assessment_type_id IN NUMBER
1280 )
1281 IS
1282 SELECT 'Y'
1283 FROM DUAL
1284 WHERE EXISTS (
1285 SELECT '1'
1286 FROM per_assessments
1287 WHERE appraisal_id = p_appraisal_id
1288 AND assessment_type_id = p_assessment_type_id);
1289
1290 l_comp_ass_exists VARCHAR2 (1);
1291 BEGIN
1292 --
1293 IF g_dbg
1294 THEN
1295 op ('Entering:' || l_proc, g_regular_log, 10);
1296 END IF;
1297
1298 IF g_dbg
1299 THEN
1300 op ('Processing Appraisal for scorecard ' || p_score_card_id,
1301 g_debug_log,
1302 10
1303 );
1304 END IF;
1305
1306 --
1307 IF (log_records_index IS NULL)
1308 THEN
1309 log_records_index := 1;
1310 END IF;
1311
1312 --
1313 -- Call create_scorecard
1314 --
1315 l_scorecard_info.scorecard_id := NULL;
1316 l_scorecard_info.assignment_id := NULL;
1317
1318 OPEN get_scorecard_info (p_score_card_id);
1319
1320 FETCH get_scorecard_info
1321 INTO l_scorecard_info;
1322
1323 CLOSE get_scorecard_info;
1324
1325 IF ( l_scorecard_info.scorecard_id IS NULL
1326 OR l_scorecard_info.assignment_id IS NULL
1327 )
1328 THEN
1329 -- WPM Logging Changes
1330 g_wpm_person_actions (log_records_index).MESSAGE_TYPE := 'E';
1331 g_wpm_person_actions (log_records_index).message_number :=
1332 fnd_message.get_number ('PER', 'NO_SCORE_CARD_WITH_THIS_ID');
1333 -- g_wpm_person_actions(log_records_index).MESSAGE_TEXT := 'NO_SCORE_CARD_WITH_THIS_ID';
1334 -- WPM Logging Changes Post Review
1335 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1336 'THERE IS NO SCORECARD WITH THIS ID';
1337 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
1338 'ERROR';
1339 -- Error
1340 fnd_file.put_line (fnd_file.LOG, 'NO_SCORE_CARD_WITH_THIS_ID');
1341 RAISE no_score_card_with_this_id;
1342 END IF;
1343
1344 IF g_dbg
1345 THEN
1346 op (' get_scorecard_info ' || l_proc, g_debug_log, 20);
1347 END IF;
1348
1349 l_assignment_info.assignment_id := NULL;
1350
1351 OPEN get_assignment_info (l_scorecard_info.person_id);
1352
1353 FETCH get_assignment_info
1354 INTO l_assignment_info;
1355
1356 CLOSE get_assignment_info;
1357
1358 IF (l_assignment_info.assignment_id IS NULL)
1359 THEN
1360 -- WPM Logging Changes
1361 g_wpm_person_actions (log_records_index).MESSAGE_TYPE := 'E';
1362 g_wpm_person_actions (log_records_index).message_number :=
1363 fnd_message.get_number ('PER', 'NO_ASSIGNMENT_WITH_THIS_ID');
1364 -- g_wpm_person_actions(log_records_index).MESSAGE_TEXT := 'NO_ASSIGNMENT_WITH_THIS_ID';
1365 -- WPM Logging Changes Post Review
1366 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1367 'THERE IS NO ASSIGNMENT WITH THIS ID';
1368 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
1369 'ERROR';
1370 -- Error
1371 fnd_file.put_line (fnd_file.LOG, 'NO_ASSIGNMENT_WITH_THIS_ID');
1372 RAISE no_assignment_with_this_id;
1373 END IF;
1374
1375 IF g_dbg
1376 THEN
1377 op (' get_assignment_info ' || l_proc, g_debug_log, 20);
1378 END IF;
1379
1380 OPEN get_appraisal_templ_info (p_appraisal_templ_id);
1381
1382 FETCH get_appraisal_templ_info
1383 INTO l_apprl_templ_info;
1384
1385 CLOSE get_appraisal_templ_info;
1386
1387 IF (l_apprl_templ_info.appraisal_template_id IS NULL)
1388 THEN
1389 -- WPM Logging Changes
1390 g_wpm_person_actions (log_records_index).MESSAGE_TYPE := 'E';
1391 g_wpm_person_actions (log_records_index).message_number :=
1392 fnd_message.get_number ('PER', 'NO_APPRL_TEMPL_WITH_THIS_ID');
1393 -- g_wpm_person_actions(log_records_index).MESSAGE_TEXT := 'NO_APPRL_TEMPL_WITH_THIS_ID';
1394 -- WPM Logging Changes Post Review
1395 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1396 'THERE IS NO APPRAISAL TEMPLATE WITH THIS ID';
1397 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
1398 'ERROR';
1399 -- Error
1400 fnd_file.put_line (fnd_file.LOG, 'NO_APPRL_TEMPL_WITH_THIS_ID');
1401 RAISE no_apprl_templ_with_this_id;
1402 END IF;
1403
1404 IF g_dbg
1405 THEN
1406 op (' get_appraisal_templ_info ' || l_proc, g_debug_log, 20);
1407 END IF;
1408
1409 l_appraisal_sys_type := p_appraisal_system_type;
1410 function_360_exists :=
1411 get_appraisal_config_params
1412 (p_appr_initiator_code => p_appraisal_initiator,
1413 p_function_id => l_function_id,
1414 p_function_name => l_function_name,
1415 p_func_parameters => l_func_params,
1416 p_appraisal_sys_type => l_appraisal_sys_type
1417 );
1418
1419 IF g_dbg
1420 THEN
1421 op ('l_function_id = ' || l_function_id, g_debug_log);
1422 END IF;
1423
1424 IF g_dbg
1425 THEN
1426 op ('l_function_name = ' || l_function_name, g_debug_log);
1427 END IF;
1428
1429 IF g_dbg
1430 THEN
1431 op ('l_func_params = ' || l_func_params, g_debug_log);
1432 END IF;
1433
1434 IF (l_function_id IS NULL OR l_appraisal_sys_type IS NULL)
1435 THEN
1436 IF g_dbg
1437 THEN
1438 op
1439 ('Could not derive Appraisal Function or Appraisal System Type',
1440 g_debug_log
1441 );
1442 END IF;
1443
1444 -- WPM Logging Changes
1445 g_wpm_person_actions (log_records_index).MESSAGE_TYPE := 'E';
1446 g_wpm_person_actions (log_records_index).message_number :=
1447 fnd_message.get_number ('PER', 'APPRAISAL_SETUP_ISSUE');
1448 -- g_wpm_person_actions(log_records_index).MESSAGE_TEXT := 'APPRAISAL_SETUP_ISSUE';
1449 -- WPM Logging Changes Post Review
1450 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1451 'THERE IS AN APPRAISAL SET UP ISSUE';
1452 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
1453 'ERROR';
1454 -- Error
1455 fnd_file.put_line (fnd_file.LOG, 'APPRAISAL_SETUP_ISSUE');
1456 RAISE appraisal_setup_issue;
1457 END IF;
1458
1459 /*
1460 IF (function_360_exists = true) then
1461 IF g_dbg THEN op(' Appraisal System Function ' || l_appraisal_sys_status, g_DEBUG_LOG); END IF;
1462 end if;
1463
1464 -- throw exception as there is no Function
1465 if(function_360_exists = false) then
1466 raise NO_APPRL_FUNCTION_DEFINED;
1467 IF g_dbg THEN op(' Appraisal System Function ' || l_appraisal_sys_status, g_DEBUG_LOG); END IF;
1468
1469 end if;
1470 */
1471
1472 --function_360_exists := true; -- to be changed
1473
1474 /*
1475
1476 if(function_360_exists = false) then
1477 raise APPRAISAL_SYSTEM_TYPE_ERROR;
1478 end if;
1479
1480 */
1481
1482 --fnd_log.string(fnd_log.level_error,module_name,' Appraisal Creation for Score Card ' || l_scorecard_info.scorecard_name);
1483
1484 -- to be, in case of Position Hierarchy we need get the supervisor id using the Plan and
1485 -- position hierarchy cursor.
1486 IF (p_appraisal_initiator = 'MGR')
1487 THEN
1488 l_appraiser_id := l_assignment_info.supervisor_id;
1489 l_main_appraiser_id := l_assignment_info.supervisor_id;
1490 ELSIF (p_appraisal_initiator = 'EMP')
1491 THEN
1492 l_appraiser_id := l_scorecard_info.person_id;
1493 l_main_appraiser_id := l_assignment_info.supervisor_id;
1494 END IF;
1495
1496 -- defaulting with employment start date if new joinee has joined after mass appraisal push
1497 IF (l_assignment_info.empl_start_date > p_appraisal_start_date)
1498 THEN
1499 l_appraisal_period_start_date := l_assignment_info.empl_start_date;
1500 ELSE
1501 l_appraisal_period_start_date := p_appraisal_start_date;
1502 END IF;
1503
1504 -- validate it with MGR joining date as well bug 8529866
1505 OPEN get_ma_start_date (l_main_appraiser_id);
1506
1507 FETCH get_ma_start_date
1508 INTO l_ma_start_date;
1509
1510 CLOSE get_ma_start_date;
1511
1512 /* commenting these changes for 8712025 bug fix
1513 IF (l_ma_start_date > l_appraisal_period_start_date)
1514 THEN
1515 l_appraisal_period_start_date := l_ma_start_date;
1516 END IF;
1517 */
1518
1519 --- same logic goes for appraisal date which is defaulted as task start date in appraisal_push function
1520 IF (l_assignment_info.empl_start_date > p_appraisal_date)
1521 THEN
1522 l_appraisal_date := l_assignment_info.empl_start_date;
1523 ELSE
1524 l_appraisal_date := p_appraisal_date;
1525 END IF;
1526
1527 -- validate it with MGR joining date as well bug 8529866
1528 IF (l_ma_start_date > l_appraisal_date)
1529 THEN
1530 l_appraisal_date := l_ma_start_date;
1531 END IF;
1532
1533 OPEN find_appraisal (p_plan_id,
1534 l_appraisal_period_start_date,
1535 p_appraisal_end_date,
1536 l_scorecard_info.person_id,
1537 -- l_appraiser_id, l_main_appraiser_id,
1538 p_appraisal_templ_id
1539 );
1540
1541 FETCH find_appraisal
1542 INTO l_found_appraisal_id, l_appraisal_status;
1543
1544 IF find_appraisal%FOUND
1545 THEN
1546 l_found_appraisal := TRUE;
1547 -- WPM Logging Changes Post Review
1548 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).transaction_ref_id :=
1549 l_found_appraisal_id;
1550 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
1551 'SUCCESS';
1552
1553 -- Success
1554 IF g_dbg
1555 THEN
1556 op ( ' find_appraisal: Found Appraisal for this Person '
1557 || l_scorecard_info.person_id
1558 || ':'
1559 || l_found_appraisal_id,
1560 g_debug_log,
1561 20
1562 );
1563 END IF;
1564 ELSE
1565 l_found_appraisal := FALSE;
1566 END IF;
1567
1568 CLOSE find_appraisal;
1569
1570 --- added this part of the code to attach appraisal id to objectives
1571 --- of per_objectives records which are populated due to new eligible objectives in scorecards
1572 --- for already created appraisals.If new objectives are added to an existing scorecard
1573 --- that shud also be added to appraisals.Bug no 6015946
1574
1575 -- if( l_found_appraisal and l_appraisal_status <> 'COMPLETED') then
1576 -- 7475464 Bug Fix changes, i.e adding APPRFEEDBACK check inadditon to
1577 -- completed appraisals check
1578 IF ( l_found_appraisal
1579 AND l_appraisal_status NOT IN ('COMPLETED', 'APPRFEEDBACK')
1580 )
1581 THEN
1582 -- added the completed appraisals check because if the plan is republished
1583 -- within the same appraisal period.. appraisals will be found and objectives
1584 -- with scorecard of the person will be updated with the appraisal_id.BUT
1585 -- if the appraisal is already completed, It will have duplicate rows for
1586 -- the same objective( one for appraisal and one for scorecard)
1587 -- if the objectives with scorecard is again updated , then we will see
1588 -- duplicate objectives in completed appraisal
1589 -- we can restrict the view in completed appraisals also by scorecard!=null
1590 -- check BUT this is not tried
1591 -- as if appraisal is reopened we may lead to data issues.
1592 FOR objectives IN get_scorecard_objectives (p_score_card_id)
1593 LOOP
1594 BEGIN
1595 hr_objectives_api.update_objective
1596 (p_validate => FALSE,
1597 p_objective_id => objectives.objective_id,
1598 p_object_version_number => objectives.object_version_number,
1599 p_effective_date => p_effective_date,
1600 p_appraisal_id => l_found_appraisal_id,
1601 -- modified by AM --
1602 p_weighting_over_100_warning => l_weighting_over_100_warning,
1603 p_weighting_appraisal_warning => l_weighting_appraisal_warning
1604 );
1605 EXCEPTION
1606 WHEN OTHERS
1607 THEN
1608 -- WPM Logging Changes
1609 g_wpm_person_actions (log_records_index).MESSAGE_TYPE :=
1610 'E';
1611 g_wpm_person_actions (log_records_index).message_number :=
1612 'OTHER';
1613 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1614 SQLERRM;
1615 hr_wpm_mass_apr_push.g_wpm_person_actions
1616 (log_records_index).processing_status :=
1617 'ERROR';
1618
1619 -- Error
1620
1621 -- to be added a message to identify update objective error
1622 IF g_dbg
1623 THEN
1624 op (SQLERRM, g_regular_log);
1625 END IF;
1626 END;
1627 END LOOP;
1628
1629 -- added for fixing P1 bug#12663201
1630 IF l_apprl_templ_info.assessment_type_id IS NOT NULL
1631 THEN
1632 OPEN csr_comp_ass_exists (l_found_appraisal_id,
1633 l_apprl_templ_info.assessment_type_id
1634 );
1635
1636 FETCH csr_comp_ass_exists
1637 INTO l_comp_ass_exists;
1638
1639 IF csr_comp_ass_exists%NOTFOUND
1640 THEN
1641 hr_assessments_api.create_assessment
1642 (p_assessment_id => l_assessment_comp_id,
1643 p_assessment_type_id => l_apprl_templ_info.assessment_type_id,
1644 p_business_group_id => l_assignment_info.business_group_id,
1645 p_person_id => l_scorecard_info.person_id,
1646 --p_assessment_group_id,
1647 p_assessment_period_start_date => l_appraisal_period_start_date,
1648 p_assessment_period_end_date => p_appraisal_end_date,
1649 p_assessment_date => l_appraisal_date,
1650 p_assessor_person_id => l_appraiser_id,
1651 --to be changed for position
1652 p_appraisal_id => l_found_appraisal_id,
1653 --p_comments,
1654 p_object_version_number => l_assessment_comp_ovn,
1655 p_validate => FALSE,
1656 p_effective_date => p_effective_date
1657 );
1658 END IF;
1659
1660 CLOSE csr_comp_ass_exists;
1661 END IF; -- close l_apprl_templ_info.assessment_type_id not null
1662 END IF;
1663
1664 ---
1665 --- End of code new for Bug no 6015946
1666 ---
1667 IF (NOT l_found_appraisal)
1668 THEN
1669 IF l_main_appraiser_id IS NULL
1670 THEN
1671 -- WPM Logging Changes
1672 g_wpm_person_actions (log_records_index).MESSAGE_TYPE := 'E';
1673 -- g_wpm_person_actions(log_records_index).MESSAGE_NUMBER := fnd_message.GET_NUMBER('PER','HR_50297_WPM_CP_ERROR');
1674 -- g_wpm_person_actions(log_records_index).MESSAGE_TEXT := 'HR_50297_WPM_CP_ERROR';
1675 -- WPM Logging Changes Post Review
1676 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1677 'UNABLE TO CREATE APPRAISAL FOR THIS PERSON AS MAIN APPRAISER COULD NOT BE IDENTIFIED';
1678 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
1679 'ERROR';
1680 -- Error
1681 fnd_file.put_line (fnd_file.LOG,
1682 'Unable to create appraisal for : '
1683 || l_scorecard_info.person_id
1684 || ' as main appraiser could not be identified.'
1685 );
1686 fnd_message.set_name ('PER', 'HR_50297_WPM_CP_ERROR');
1687 g_cp_error_txt := NVL (fnd_message.get, 'HR_50297_WPM_CP_ERROR');
1688 g_retcode := warning;
1689 g_errbuf := g_cp_error_txt;
1690 g_num_errors := g_num_errors + 1;
1691
1692 --
1693 IF g_num_errors > g_max_errors
1694 THEN
1695 fnd_message.set_name ('PER', 'HR_50298_WPM_MAX_ERRORS');
1696 g_error_txt :=
1697 NVL (fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
1698 g_retcode := error;
1699 g_errbuf := g_error_txt;
1700 fnd_message.raise_error; --RAISE;
1701 END IF;
1702
1703 RETURN;
1704 END IF;
1705
1706 IF g_dbg
1707 THEN
1708 op (' Eff Date : ' || p_effective_date, g_debug_log);
1709 END IF;
1710
1711 hr_appraisals_api.create_appraisal
1712 (p_validate => FALSE,
1713 p_open => NULL,
1714 -- Its mandatory to pass null, as the defult is 'Y'.
1715 p_effective_date => p_effective_date,
1716 p_business_group_id => l_assignment_info.business_group_id,
1717 p_appraisal_template_id => p_appraisal_templ_id,
1718 p_show_competency_ratings => l_apprl_templ_info.show_competency_ratings,
1719 p_show_objective_ratings => l_apprl_templ_info.show_objective_ratings,
1720 p_show_questionnaire_info => l_apprl_templ_info.show_questionnaire_info,
1721 p_show_participant_details => l_apprl_templ_info.show_participant_details,
1722 p_show_participant_ratings => l_apprl_templ_info.show_participant_ratings,
1723 p_show_participant_names => l_apprl_templ_info.show_participant_names,
1724 p_show_overall_ratings => l_apprl_templ_info.show_overall_ratings,
1725 p_provide_overall_feedback => l_apprl_templ_info.disable_provide_feed,
1726 --Bug7393131
1727 p_show_overall_comments => l_apprl_templ_info.show_overall_comments,
1728 p_update_appraisal => 'Y',
1729 p_appraisee_person_id => l_scorecard_info.person_id,
1730 p_appraiser_person_id => l_appraiser_id,
1731 --to be changed for position
1732 p_appraisal_date => l_appraisal_date,
1733 p_appraisal_period_start_date => l_appraisal_period_start_date,
1734 p_appraisal_period_end_date => p_appraisal_end_date,
1735 p_type => p_type, -- ANN,
1736 p_next_appraisal_date => p_next_appraisal_date,
1737 p_status => p_status,
1738 -- PLANNED,TRANSFER,RFC,
1739 p_comments => p_comments,
1740 p_system_type => l_appraisal_sys_type,
1741 --MGR360 EMP360
1742 p_system_params => l_func_params,
1743 --p_appraisee_access,
1744 p_main_appraiser_id => l_main_appraiser_id,
1745 --to be changed for position
1746 p_assignment_id => l_assignment_info.assignment_id,
1747 p_assignment_start_date => l_assignment_info.effective_state_date,
1748 p_asg_business_group_id => l_assignment_info.business_group_id,
1749 p_assignment_organization_id => l_assignment_info.org_id,
1750 p_assignment_job_id => l_assignment_info.job_id,
1751 --p_assignment_position_id = l_assignment_info.position_id ,
1752 p_assignment_grade_id => l_assignment_info.grade_id,
1753 p_appraisal_id => l_apprl_id,
1754 p_object_version_number => l_appraisal_ovn,
1755 p_appraisal_system_status => p_appraisal_status,
1756 p_plan_id => p_plan_id
1757 );
1758 -- WPM Logging Changes Post Review
1759 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).transaction_ref_id :=
1760 l_apprl_id;
1761 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
1762 'SUCCESS';
1763
1764 -- Success
1765 IF g_dbg
1766 THEN
1767 op ('Appraisal Id = ' || l_apprl_id, g_debug_log);
1768 END IF;
1769
1770 IF ( l_apprl_id IS NOT NULL
1771 AND l_apprl_templ_info.assessment_type_id IS NOT NULL
1772 )
1773 THEN
1774 hr_assessments_api.create_assessment
1775 (p_assessment_id => l_assessment_comp_id,
1776 p_assessment_type_id => l_apprl_templ_info.assessment_type_id,
1777 p_business_group_id => l_assignment_info.business_group_id,
1778 p_person_id => l_scorecard_info.person_id,
1779 --p_assessment_group_id,
1780 p_assessment_period_start_date => l_appraisal_period_start_date,
1781 p_assessment_period_end_date => p_appraisal_end_date,
1782 p_assessment_date => l_appraisal_date,
1783 p_assessor_person_id => l_appraiser_id,
1784 --to be changed for position
1785 p_appraisal_id => l_apprl_id,
1786 --p_comments,
1787 p_object_version_number => l_assessment_comp_ovn,
1788 p_validate => FALSE,
1789 p_effective_date => p_effective_date
1790 );
1791 END IF;
1792
1793 IF g_dbg
1794 THEN
1795 op ('Competence Assesment Id = ' || l_assessment_comp_id,
1796 g_debug_log
1797 );
1798 END IF;
1799
1800 -- this record is created for final ratings on Objectives.
1801 IF ( l_apprl_id IS NOT NULL
1802 AND l_apprl_templ_info.objective_asmnt_type_id IS NOT NULL
1803 )
1804 THEN
1805 hr_assessments_api.create_assessment
1806 (p_assessment_id => l_assessment_obj_id,
1807 p_assessment_type_id => l_apprl_templ_info.objective_asmnt_type_id,
1808 p_business_group_id => l_assignment_info.business_group_id,
1809 p_person_id => l_scorecard_info.person_id,
1810 --p_assessment_group_id,
1811 p_assessment_period_start_date => l_appraisal_period_start_date,
1812 p_assessment_period_end_date => p_appraisal_end_date,
1813 p_assessment_date => l_appraisal_date,
1814 p_assessor_person_id => l_appraiser_id,
1815 --to be changed for position
1816 p_appraisal_id => l_apprl_id,
1817 --p_comments,
1818 p_object_version_number => l_assessment_obj_ovn,
1819 p_validate => FALSE,
1820 p_effective_date => p_effective_date
1821 );
1822 END IF;
1823
1824 IF g_dbg
1825 THEN
1826 op ('Objective Assessment Id = ' || l_assessment_obj_id,
1827 g_debug_log
1828 );
1829 END IF;
1830
1831 -- to be
1832 IF (p_appraisal_initiator = 'MGR')
1833 THEN
1834 l_object_id := l_assignment_info.supervisor_id;
1835 ELSIF (p_appraisal_initiator = 'EMP')
1836 THEN
1837 l_object_id := l_scorecard_info.person_id;
1838 END IF;
1839
1840 z := 1;
1841
1842 IF (l_assessment_comp_id IS NOT NULL)
1843 THEN
1844 FOR competences IN
1845 get_assess_templ_comps (l_apprl_templ_info.assessment_type_id)
1846 LOOP
1847 BEGIN
1848 l_return_status := '';
1849 l_competence_error := NULL;
1850 l_assess_comps_processed (z).competence_id :=
1851 competences.competence_id;
1852 hr_competence_element_api.create_competence_element
1853 (p_validate => FALSE,
1854 p_competence_element_id => l_comp_ele_id,
1855 p_object_version_number => l_comp_ovn,
1856 p_type => 'ASSESSMENT',
1857 p_business_group_id => l_assignment_info.business_group_id,
1858 p_competence_id => competences.competence_id,
1859 p_assessment_id => l_assessment_comp_id,
1860 p_effective_date_from => l_appraisal_period_start_date,
1861 p_effective_date => p_effective_date,
1862 p_object_name => 'ASSESSOR_ID',
1863 p_object_id => l_object_id,
1864 p_parent_competence_element_id => competences.competence_element_id
1865 );
1866 z := z + 1;
1867
1868 IF g_dbg
1869 THEN
1870 op ('Competence Element Id = ' || l_comp_ele_id,
1871 g_debug_log
1872 );
1873 END IF;
1874 EXCEPTION
1875 WHEN OTHERS
1876 THEN
1877 -- WPM Logging Changes
1878 g_wpm_person_actions (log_records_index).MESSAGE_TYPE :=
1879 'E';
1880 g_wpm_person_actions (log_records_index).message_number :=
1881 'OTHER';
1882 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1883 SQLERRM;
1884 hr_wpm_mass_apr_push.g_wpm_person_actions
1885 (log_records_index).processing_status :=
1886 'ERROR';
1887
1888 -- Error
1889
1890 -- to be added a message to identify competence element error
1891 IF g_dbg
1892 THEN
1893 op (SQLERRM, g_regular_log);
1894 END IF;
1895 END;
1896 END LOOP;
1897 END IF;
1898
1899 OPEN get_assess_templ_info (l_apprl_templ_info.assessment_type_id);
1900
1901 FETCH get_assess_templ_info
1902 INTO l_templ_def_job_comps, l_assess_type_id;
1903
1904 CLOSE get_assess_templ_info;
1905
1906 IF (l_templ_def_job_comps = 'Y')
1907 THEN
1908 apply_overridding_rules
1909 (p_enterprise_id => l_assignment_info.business_group_id,
1910 p_organization_id => l_assignment_info.org_id,
1911 p_job_id => l_assignment_info.job_id,
1912 p_position_id => l_assignment_info.position_id,
1913 p_skip_duplicate => TRUE,
1914 l_sel_comp_table => l_def_job_comps
1915 );
1916
1917 -- create the Job Comps eliminating duplicates
1918 IF (l_assessment_comp_id IS NOT NULL)
1919 THEN
1920 FOR j IN 1 .. l_def_job_comps.COUNT
1921 LOOP
1922 BEGIN
1923 l_found_comp := FALSE;
1924
1925 FOR k IN 1 .. l_assess_comps_processed.COUNT
1926 LOOP
1927 IF ( l_def_job_comps (j).competence_id IS NOT NULL
1928 AND l_def_job_comps (j).competence_id =
1929 l_assess_comps_processed (k).competence_id
1930 )
1931 THEN
1932 l_found_comp := TRUE;
1933 END IF;
1934 END LOOP;
1935
1936 IF ( l_def_job_comps (j).competence_id IS NOT NULL
1937 AND NOT l_found_comp
1938 )
1939 THEN
1940 hr_competence_element_api.create_competence_element
1941 (p_validate => FALSE,
1942 p_competence_element_id => l_comp_ele_id,
1943 p_object_version_number => l_comp_ovn,
1944 p_type => 'ASSESSMENT',
1945 p_business_group_id => l_assignment_info.business_group_id,
1946 p_competence_id => l_def_job_comps
1947 (j).competence_id,
1948 p_assessment_id => l_assessment_comp_id,
1949 p_effective_date_from => l_appraisal_period_start_date,
1950 p_effective_date => p_effective_date,
1951 p_object_name => 'ASSESSOR_ID',
1952 p_object_id => l_object_id,
1953 p_parent_competence_element_id => l_def_job_comps
1954 (j).competence_element_id
1955 );
1956
1957 IF g_dbg
1958 THEN
1959 op ( ' Def Job Competence Id and Element Id = '
1960 || l_comp_ele_id
1961 || l_def_job_comps (j).competence_id,
1962 g_debug_log
1963 );
1964 END IF;
1965 END IF;
1966 EXCEPTION
1967 WHEN OTHERS
1968 THEN
1969 -- WPM Logging Changes
1970 g_wpm_person_actions (log_records_index).MESSAGE_TYPE :=
1971 'E';
1972 g_wpm_person_actions (log_records_index).message_number :=
1973 'OTHER';
1974 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
1975 SQLERRM;
1976 hr_wpm_mass_apr_push.g_wpm_person_actions
1977 (log_records_index).processing_status :=
1978 'ERROR';
1979 -- Error
1980 hr_wpm_mass_apr_push.g_wpm_person_actions
1981 (log_records_index).processing_status :=
1982 'ERROR';
1983
1984 -- Error
1985
1986 -- to be added a message to identify competence element error
1987 IF g_dbg
1988 THEN
1989 op (SQLERRM, g_regular_log);
1990 END IF;
1991 END;
1992 END LOOP;
1993 END IF;
1994 END IF;
1995
1996 IF (l_apprl_id IS NOT NULL)
1997 THEN
1998 FOR objectives IN get_scorecard_objectives (p_score_card_id)
1999 LOOP
2000 BEGIN
2001 hr_objectives_api.update_objective
2002 (p_validate => FALSE,
2003 p_objective_id => objectives.objective_id,
2004 p_object_version_number => objectives.object_version_number,
2005 p_effective_date => p_effective_date,
2006 p_appraisal_id => l_apprl_id,
2007 -- to be changed in SWI,API,RHI
2008 p_weighting_over_100_warning => l_weighting_over_100_warning,
2009 p_weighting_appraisal_warning => l_weighting_appraisal_warning
2010 );
2011
2012 IF g_dbg
2013 THEN
2014 op ( 'Linked objective Id to Appraisal = '
2015 || objectives.objective_id,
2016 g_debug_log
2017 );
2018 END IF;
2019 EXCEPTION
2020 WHEN OTHERS
2021 THEN
2022 -- WPM Logging Changes
2023 g_wpm_person_actions (log_records_index).MESSAGE_TYPE :=
2024 'E';
2025 g_wpm_person_actions (log_records_index).message_number :=
2026 'OTHER';
2027 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
2028 SQLERRM;
2029 hr_wpm_mass_apr_push.g_wpm_person_actions
2030 (log_records_index).processing_status :=
2031 'ERROR';
2032
2033 -- Error
2034
2035 -- to be added a message to identify update objective error
2036 IF g_dbg
2037 THEN
2038 op (SQLERRM, g_regular_log);
2039 END IF;
2040 END;
2041 END LOOP;
2042 END IF;
2043
2044 --schowdhu 8721163 9-Sep-2009
2045 -- Add secondary assignment supervisors as participants
2046 IF (l_apprl_id IS NOT NULL AND l_default_participant <> 'N')
2047 THEN
2048 FOR supervisors IN
2049 get_sec_asg_supervisors (l_scorecard_info.person_id)
2050 LOOP
2051 BEGIN
2052 hr_participants_api.create_participant
2053 (p_effective_date => p_effective_date,
2054 p_business_group_id => l_assignment_info.business_group_id,
2055 p_questionnaire_template_id => p_questionnaire_template_id,
2056 p_participation_in_table => 'PER_APPRAISALS',
2057 p_participation_in_column => 'APPRAISAL_ID',
2058 p_participation_in_id => l_apprl_id,
2059 p_participation_status => 'OPEN',
2060 p_participation_type => p_participation_type,
2061 p_person_id => supervisors.supervisor_id,
2062 p_participant_id => l_participant_id,
2063 p_object_version_number => l_object_version_number
2064 );
2065
2066 IF g_dbg
2067 THEN
2068 op ('Participant Id = ' || l_participant_id,
2069 g_debug_log);
2070 END IF;
2071 EXCEPTION
2072 WHEN OTHERS
2073 THEN
2074 -- WPM Logging Changes
2075 g_wpm_person_actions (log_records_index).MESSAGE_TYPE :=
2076 'E';
2077 g_wpm_person_actions (log_records_index).message_number :=
2078 'OTHER';
2079 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
2080 SQLERRM;
2081 hr_wpm_mass_apr_push.g_wpm_person_actions
2082 (log_records_index).processing_status :=
2083 'ERROR';
2084
2085 -- Error
2086
2087 -- to be added a message to identify participant creation error
2088 IF g_dbg
2089 THEN
2090 op (SQLERRM, g_regular_log);
2091 END IF;
2092 END;
2093 END LOOP;
2094 END IF; -- end of participant creation
2095 END IF;
2096
2097 -- Out parameter
2098
2099 --
2100 IF g_dbg
2101 THEN
2102 op ('Leaving:' || l_proc, g_regular_log, 80);
2103 END IF;
2104 --
2105 EXCEPTION
2106 WHEN OTHERS
2107 THEN
2108 -- WPM Logging Changes
2109 g_wpm_person_actions (log_records_index).MESSAGE_TYPE := 'E';
2110 g_wpm_person_actions (log_records_index).message_number := 'OTHER';
2111 g_wpm_person_actions (log_records_index).MESSAGE_TEXT := SQLERRM;
2112 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
2113 'ERROR';
2114
2115 -- Error
2116 IF g_dbg
2117 THEN
2118 op ('Leaving:' || l_proc, g_regular_log, 90);
2119 END IF;
2120
2121 --
2122 fnd_message.set_name ('PER', 'HR_50297_WPM_CP_ERROR');
2123 g_cp_error_txt := NVL (fnd_message.get, 'HR_50297_WPM_CP_ERROR');
2124 g_retcode := warning;
2125 g_errbuf := g_cp_error_txt;
2126 g_num_errors := g_num_errors + 1;
2127
2128 IF g_dbg
2129 THEN
2130 op (g_error_txt, g_regular_log);
2131 END IF;
2132
2133 IF g_dbg
2134 THEN
2135 op (SQLERRM, g_regular_log);
2136 END IF;
2137
2138 --
2139 -- If the max number of errors has been exceeded, raise the error and
2140 -- terminate processing of this plan.
2141 --
2142 IF g_num_errors > g_max_errors
2143 THEN
2144 fnd_message.set_name ('PER', 'HR_50298_WPM_MAX_ERRORS');
2145 g_error_txt := NVL (fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
2146 g_retcode := error;
2147 g_errbuf := g_error_txt;
2148 RAISE;
2149 END IF;
2150 END create_appraisal_for_person;
2151
2152 --
2153 -- ----------------------------------------------------------------------------
2154 -- |---------------------< submit_apprisal_cp >---------------------------|
2155 -- ----------------------------------------------------------------------------
2156 --
2157 PROCEDURE submit_appraisal_cp (
2158 p_effective_date IN DATE,
2159 p_start_date IN VARCHAR2,
2160 p_plan_id IN NUMBER,
2161 p_appraisal_period_id IN NUMBER,
2162 p_log_output IN VARCHAR2
2163 )
2164 IS
2165 --
2166 l_object_version_number NUMBER;
2167 l_status_code per_perf_mgmt_plans.status_code%TYPE;
2168 l_dummy BOOLEAN;
2169 l_request_id NUMBER;
2170 l_effective_date VARCHAR2 (30)
2171 := fnd_date.date_to_canonical (NVL (p_effective_date,
2172 TRUNC (SYSDATE))
2173 );
2174 --
2175 BEGIN
2176 -- Submit the request
2177 l_request_id :=
2178 fnd_request.submit_request (application => 'PER',
2179 program => 'WPMAPRPUSH',
2180 sub_request => FALSE,
2181 start_time => p_start_date,
2182 argument1 => l_effective_date,
2183 argument2 => p_plan_id,
2184 argument3 => p_appraisal_period_id,
2185 argument4 => p_log_output
2186 );
2187
2188 --
2189 IF l_request_id > 0
2190 THEN
2191 NULL;
2192 END IF;
2193 --
2194 END submit_appraisal_cp;
2195
2196 --
2197 -- ----------------------------------------------------------------------------
2198 -- |---------------------------< appraisal_cp >----------------------------|
2199 -- ----------------------------------------------------------------------------
2200 --
2201 PROCEDURE appraisal_cp (
2202 errbuf OUT NOCOPY VARCHAR2,
2203 retcode OUT NOCOPY NUMBER,
2204 p_effective_date IN VARCHAR2,
2205 p_plan_id IN NUMBER,
2206 p_appraisal_period_id IN NUMBER,
2207 p_log_output IN VARCHAR2 DEFAULT 'N',
2208 p_delete_pending_trans IN VARCHAR2 DEFAULT 'N'
2209 )
2210 IS
2211 --
2212 CURSOR csr_plan_ovn
2213 IS
2214 SELECT object_version_number
2215 FROM per_perf_mgmt_plans
2216 WHERE plan_id = p_plan_id;
2217
2218 l_object_version_number NUMBER;
2219 l_effective_date DATE
2220 := fnd_date.canonical_to_date (NVL (p_effective_date,
2221 TRUNC (SYSDATE))
2222 );
2223
2224 CURSOR csr_pend_trans (p_plan_id IN NUMBER)
2225 IS
2226 SELECT 'x'
2227 FROM DUAL
2228 WHERE EXISTS (
2229 SELECT 'x'
2230 FROM hr_api_transactions t, per_personal_scorecards sc
2231 WHERE t.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
2232 AND t.transaction_ref_id = sc.scorecard_id
2233 AND sc.plan_id = p_plan_id);
2234
2235 l_chk VARCHAR2 (10);
2236 scorecard_pending_transaction EXCEPTION;
2237 --
2238 BEGIN
2239 -- WPM Logging Changes
2240 -- l_current_wpm_batch_action_id := per_wpm_batch_actions_s.NEXTVAL;
2241 INSERT INTO per_wpm_batch_actions
2242 (wpm_batch_action_id,
2243 conc_request_id, conc_program_name, plan_id,
2244 appraisal_period_id, status, start_date, end_date
2245 )
2246 VALUES (per_wpm_batch_actions_s.NEXTVAL,
2247 fnd_global.conc_request_id, 'WPMAPRPUSH', p_plan_id,
2248 p_appraisal_period_id, 'PENDING', SYSDATE,
2249 --p_effective_date, -- trunc(sysdate)
2250 NULL
2251 )
2252 RETURNING wpm_batch_action_id
2253 INTO l_current_wpm_batch_action_id;
2254
2255 COMMIT;
2256 /*
2257 --
2258 -- Derive the object version number of plan record
2259 --
2260 open csr_plan_ovn;
2261 fetch csr_plan_ovn into l_object_version_number;
2262 close csr_plan_ovn;
2263 */-- Initialize return status
2264 retcode := warning;
2265
2266 --
2267 -- Raise an error if there are any pending transactions
2268 --
2269 IF NVL (p_delete_pending_trans, 'N') = 'N'
2270 THEN
2271 OPEN csr_pend_trans (p_plan_id);
2272
2273 FETCH csr_pend_trans
2274 INTO l_chk;
2275
2276 IF csr_pend_trans%FOUND
2277 THEN
2278 CLOSE csr_pend_trans;
2279
2280 RAISE scorecard_pending_transaction;
2281 END IF;
2282
2283 CLOSE csr_pend_trans;
2284 END IF;
2285
2286 -- WPM Logging Changes Post Review
2287 -- to avoid caching issues
2288 g_wpm_person_actions.DELETE;
2289 log_records_index := NULL;
2290 --
2291 -- Call the publish plan
2292 --
2293 appraisal_push (p_effective_date => l_effective_date,
2294 p_plan_id => p_plan_id,
2295 p_appraisal_period_id => p_appraisal_period_id,
2296 p_log_output => 'Y' --p_log_output
2297 );
2298 --
2299 errbuf := g_errbuf;
2300 retcode := g_retcode;
2301 --
2302 -- WPM Logging Changes
2303 print_cache ();
2304
2305 UPDATE per_wpm_batch_actions
2306 SET end_date = SYSDATE,
2307 status = DECODE (g_retcode, 0, 'SUCCESS', 'WARNING')
2308 WHERE wpm_batch_action_id = l_current_wpm_batch_action_id;
2309
2310 COMMIT;
2311 --
2312 EXCEPTION
2313 WHEN scorecard_pending_transaction
2314 THEN
2315 retcode := error;
2316 fnd_message.set_name ('PER', 'HR_SC_PENDING_TXN_ERR');
2317 errbuf := NVL (fnd_message.get, 'HR_SC_PENDING_TXN_ERR');
2318 ROLLBACK;
2319 -- WPM Logging Changes
2320 print_cache ();
2321
2322 UPDATE per_wpm_batch_actions
2323 SET status = 'ERROR',
2324 end_date = SYSDATE
2325 WHERE wpm_batch_action_id = l_current_wpm_batch_action_id;
2326
2327 COMMIT;
2328 WHEN OTHERS
2329 THEN
2330 ROLLBACK;
2331 -- WPM Logging Changes
2332 print_cache ();
2333
2334 UPDATE per_wpm_batch_actions
2335 SET status = 'ERROR',
2336 end_date = SYSDATE
2337 WHERE wpm_batch_action_id = l_current_wpm_batch_action_id;
2338
2339 COMMIT;
2340 --
2341 errbuf := g_errbuf;
2342 retcode := g_retcode;
2343 --
2344 END appraisal_cp;
2345
2346 --
2347 -- ----------------------------------------------------------------------------
2348 -- |-----------------------------< appraisal_push>-----------------------------|
2349 -- ----------------------------------------------------------------------------
2350 --
2351 PROCEDURE appraisal_push (
2352 p_effective_date IN DATE,
2353 p_plan_id IN NUMBER,
2354 p_appraisal_period_id IN NUMBER,
2355 p_log_output IN VARCHAR2
2356 )
2357 IS
2358 --
2359 -- Declare cursors and local variables
2360 --
2361 l_proc VARCHAR2 (72)
2362 := g_package || 'appraisal_push';
2363 l_logging pay_action_parameters.parameter_value%TYPE;
2364 l_debug BOOLEAN := FALSE;
2365 l_effective_date DATE
2366 := TRUNC (NVL (p_effective_date, SYSDATE));
2367 l_object_version_number NUMBER;
2368 l_status_code per_perf_mgmt_plans.status_code%TYPE;
2369 l_dummy BOOLEAN;
2370 --
2371 l_scorecard_id per_personal_scorecards.scorecard_id%TYPE;
2372 --
2373 l_message_count NUMBER := 0;
2374 l_message VARCHAR2 (256);
2375 l_qual_pop_index BINARY_INTEGER;
2376 l_curr_sc_pop_index BINARY_INTEGER;
2377 l_qual_obj_index BINARY_INTEGER;
2378 l_plan_aprsl_pds_index BINARY_INTEGER;
2379 l_curr_sc_obj_index BINARY_INTEGER;
2380 l_appr_ret_status VARCHAR2 (1);
2381
2382 -- Plan record
2383 CURSOR csr_get_plan_rec
2384 IS
2385 SELECT *
2386 FROM per_perf_mgmt_plans
2387 WHERE plan_id = p_plan_id;
2388
2389 /*
2390 -- Scorecard Objectives
2391 CURSOR csr_sc_objectives(p_scorecard_id number) IS
2392 select objective_id, object_version_number
2393 from per_objectives
2394 where scorecard_id = p_scorecard_id;
2395 */
2396 CURSOR csr_plan_appraisals (plan_id per_appraisals.plan_id%TYPE)
2397 IS
2398 SELECT appraisal_id, object_version_number
2399 FROM per_appraisals
2400 WHERE plan_id = plan_id;
2401
2402 --
2403 -- Scorecard cursor modified
2404 -- schowdhu 6156964 23-Jun-2009 Eligibility Profile Enhc.
2405 CURSOR csr_get_scorecards
2406 IS
2407 SELECT pc.scorecard_id, pc.plan_id, pc.object_version_number,
2408 papf.business_group_id, pc.assignment_id, pc.person_id,
2409 pc.status_code, papf.full_name
2410 FROM per_personal_scorecards pc,
2411 per_people_f papf,
2412 per_assignments_f paaf
2413 WHERE pc.plan_id = p_plan_id
2414 AND pc.assignment_id = paaf.assignment_id
2415 AND pc.person_id = papf.person_id
2416 AND p_effective_date BETWEEN paaf.effective_start_date
2417 AND paaf.effective_end_date
2418 AND p_effective_date BETWEEN papf.effective_start_date
2419 AND papf.effective_end_date;
2420
2421 CURSOR csr_get_elig_obj_id (
2422 p_appr_period_id per_appraisal_periods.appraisal_period_id%TYPE
2423 )
2424 IS
2425 SELECT elig.elig_obj_id
2426 FROM ben_elig_obj_f elig
2427 WHERE elig.table_name = 'PER_APPRAISAL_PERIODS'
2428 AND elig.column_name = 'APPRAISAL_PERIOD_ID'
2429 AND elig.COLUMN_VALUE = p_appr_period_id
2430 AND TRUNC (SYSDATE) BETWEEN elig.effective_start_date
2431 AND elig.effective_end_date;
2432
2433 --
2434 l_plan_rec per_perf_mgmt_plans%ROWTYPE;
2435 l_obj_date DATE := TRUNC (SYSDATE);
2436 l_scorecard_status_code VARCHAR2 (30);
2437 l_check_elig VARCHAR2 (1);
2438 l_elig_obj_id ben_elig_obj_f.elig_obj_id%TYPE;
2439
2440 --
2441 -- cursor to select pending transactions for a given scorecard
2442 --
2443 CURSOR c_trx (p_sc_card_id NUMBER)
2444 IS
2445 SELECT transaction_id
2446 FROM hr_api_transactions
2447 WHERE transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
2448 AND transaction_ref_id = p_sc_card_id;
2449
2450 l_sc_ovn NUMBER;
2451 l_trx_id NUMBER;
2452 --
2453 BEGIN
2454 --
2455 -- Initialize logging
2456 --
2457 initialize_logging (p_action_parameter_group_id => NULL,
2458 p_log_output => p_log_output
2459 );
2460
2461 --
2462 IF g_dbg
2463 THEN
2464 op ('Entering:' || l_proc, g_debug_log, 10);
2465 END IF;
2466
2467 --
2468 -- Get Plan record
2469 --
2470 OPEN csr_get_plan_rec;
2471
2472 FETCH csr_get_plan_rec
2473 INTO l_plan_rec;
2474
2475 CLOSE csr_get_plan_rec;
2476
2477 --
2478 IF g_dbg
2479 THEN
2480 op (l_proc, g_debug_log, 20);
2481 END IF;
2482
2483 IF g_dbg
2484 THEN
2485 op ('Plan Name: ' || SUBSTR (l_plan_rec.plan_name, 1, 40),
2486 g_debug_log,
2487 21
2488 );
2489 END IF;
2490
2491 IF g_dbg
2492 THEN
2493 op ('Concurrent Request ID: ' || TO_CHAR (fnd_global.conc_request_id),
2494 g_debug_log,
2495 22
2496 );
2497 END IF;
2498
2499 --
2500 -- Checks that the status is valid for PLAN PUBLISH OR REVERSE PUBLISH
2501 --
2502 --*****************************************
2503 --*********check later for plan-publish status
2504 --*****************************************
2505 --chk_publishing_status(p_reverse_mode, l_plan_rec.status_code);
2506 --
2507 -- If appraisals flag is set then populate appraisala period table
2508 IF (l_plan_rec.include_appraisals_flag = 'Y')
2509 THEN
2510 --
2511 -- Get the qualifying plan periods
2512 -- g_plan_aprsl_pds_tbl is populated with details
2513 --
2514 IF g_dbg
2515 THEN
2516 op (l_proc, g_debug_log, 30);
2517 END IF;
2518
2519 --
2520
2521 -- This needs to be checked, as we no longer need plan_id for populating,
2522 -- as we will be hanling one appraisal period at a time now
2523 populate_plan_apprsl_periods (l_plan_rec.plan_id,
2524 p_appraisal_period_id
2525 );
2526 END IF;
2527
2528 --
2529 IF g_dbg
2530 THEN
2531 op (l_proc, g_debug_log, 40);
2532 END IF;
2533
2534 --
2535 FOR curr_scorecard IN csr_get_scorecards
2536 LOOP
2537 -- WPM Logging Changes
2538 log_records_index := curr_scorecard.assignment_id;
2539
2540 IF NOT hr_wpm_mass_apr_push.g_wpm_person_actions.EXISTS
2541 (curr_scorecard.assignment_id)
2542 THEN
2543 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).wpm_person_action_id :=
2544 -1;
2545 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).wpm_batch_action_id :=
2546 l_current_wpm_batch_action_id;
2547 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).person_id :=
2548 curr_scorecard.person_id;
2549 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).assignment_id :=
2550 curr_scorecard.assignment_id;
2551 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).business_group_id :=
2552 curr_scorecard.business_group_id;
2553 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).processing_status :=
2554 'P';
2555 -- Processing
2556 -- hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).transaction_ref_table := 'PER_PERSONAL_SCORECARDS';
2557 -- hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).transaction_ref_id := curr_scorecard.scorecard_id;
2558 -- WPM Logging Changes Post Review
2559 hr_wpm_mass_apr_push.g_wpm_person_actions (log_records_index).transaction_ref_table :=
2560 'PER_APPRAISALS';
2561 END IF;
2562
2563 IF (curr_scorecard.status_code <> 'TRANSFER_OUT')
2564 THEN
2565 IF g_dbg
2566 THEN
2567 op (l_proc, g_debug_log, 50);
2568 END IF;
2569
2570 --
2571 -- Create Appraisals if flag is set
2572 --
2573 IF (l_plan_rec.include_appraisals_flag = 'Y')
2574 THEN
2575 --
2576 IF g_dbg
2577 THEN
2578 op (l_proc, g_debug_log, 60);
2579 END IF;
2580
2581 l_plan_aprsl_pds_index := g_plan_aprsl_pds_tbl.FIRST;
2582
2583 WHILE (l_plan_aprsl_pds_index IS NOT NULL)
2584 LOOP
2585 --
2586 IF g_dbg
2587 THEN
2588 op (l_proc, g_debug_log, 70);
2589 END IF;
2590
2591 -- schowdhu 6156964 23-Jun-2009 Eligibility Profile Enhc. start
2592 OPEN csr_get_elig_obj_id (p_appraisal_period_id);
2593
2594 FETCH csr_get_elig_obj_id
2595 INTO l_elig_obj_id;
2596
2597 CLOSE csr_get_elig_obj_id;
2598
2599 IF (l_elig_obj_id IS NULL)
2600 THEN
2601 -- WPM Logging Changes
2602 hr_wpm_mass_apr_push.g_wpm_person_actions
2603 (hr_wpm_mass_apr_push.log_records_index).eligibility_status :=
2604 'Y';
2605 END IF;
2606
2607 IF (l_elig_obj_id IS NOT NULL)
2608 THEN
2609 BEGIN
2610 --
2611 ben_env_object.init
2612 (p_business_group_id => curr_scorecard.business_group_id,
2613 p_thread_id => NULL,
2614 p_chunk_size => NULL,
2615 p_threads => NULL,
2616 p_max_errors => NULL,
2617 p_benefit_action_id => NULL,
2618 p_effective_date => l_obj_date
2619 );
2620 --
2621 --
2622 l_check_elig :=
2623 ben_per_asg_elig.eligible
2624 (curr_scorecard.person_id,
2625 curr_scorecard.assignment_id,
2626 l_elig_obj_id,
2627 l_obj_date,
2628 curr_scorecard.business_group_id,
2629 'Y'
2630 ---KMG added to allow CWK's for eligibility check
2631 );
2632 -- WPM Logging Changes
2633 g_wpm_person_actions (log_records_index).eligibility_status :=
2634 l_check_elig;
2635 EXCEPTION
2636 WHEN OTHERS
2637 THEN
2638 l_check_elig := 'N';
2639 -- WPM Logging Changes
2640 g_wpm_person_actions (log_records_index).eligibility_status :=
2641 l_check_elig;
2642 -- WPM Logging Changes
2643 g_wpm_person_actions (log_records_index).MESSAGE_TYPE :=
2644 'E';
2645 g_wpm_person_actions (log_records_index).message_number :=
2646 'OTHER';
2647 g_wpm_person_actions (log_records_index).MESSAGE_TEXT :=
2648 SQLERRM;
2649 hr_wpm_mass_apr_push.g_wpm_person_actions
2650 (log_records_index).processing_status :=
2651 'ERROR';
2652 -- Error
2653 op ('Error while evaluating eligibility for: ',
2654 g_regular_log
2655 );
2656 op ( ' '
2657 || curr_scorecard.full_name
2658 || ' ('
2659 || curr_scorecard.person_id
2660 || ')',
2661 g_regular_log,
2662 108
2663 );
2664 op (SQLERRM, g_regular_log, 108);
2665 NULL;
2666 END;
2667 END IF; -- l_elig_obj_id null check
2668
2669 IF (l_check_elig = 'N')
2670 THEN
2671 op
2672 ('+-------------------------------------------------------------------+',
2673 g_regular_log
2674 );
2675 op ('Not Eligible. Appraisal creation skipped for: ',
2676 g_regular_log
2677 );
2678 op ( ' '
2679 || curr_scorecard.full_name
2680 || ' ('
2681 || curr_scorecard.person_id
2682 || ')',
2683 g_regular_log
2684 );
2685 op
2686 ('+-------------------------------------------------------------------+',
2687 g_regular_log
2688 );
2689 END IF;
2690
2691 IF ( l_check_elig = 'Y'
2692 OR (l_check_elig IS NULL AND l_elig_obj_id IS NULL)
2693 )
2694 THEN
2695 -- schowdhu 6156964 23-Jun-2009 Eligibility Profile Enhc. end
2696 create_appraisal_for_person
2697 (p_score_card_id => curr_scorecard.scorecard_id,
2698 p_appraisal_templ_id => g_plan_aprsl_pds_tbl
2699 (l_plan_aprsl_pds_index
2700 ).appraisal_template_id,
2701 p_effective_date => p_effective_date,
2702 --to be validated
2703 p_appraisal_start_date => g_plan_aprsl_pds_tbl
2704 (l_plan_aprsl_pds_index
2705 ).start_date,
2706 p_appraisal_end_date => g_plan_aprsl_pds_tbl
2707 (l_plan_aprsl_pds_index
2708 ).end_date,
2709 p_appraisal_date => g_plan_aprsl_pds_tbl
2710 (l_plan_aprsl_pds_index
2711 ).task_start_date,
2712 p_appraisal_status => 'PLANNED',
2713 -- decided in the meeting
2714 p_plan_id => p_plan_id,
2715 p_next_appraisal_date => NULL, -- to be
2716 p_appraisal_initiator => g_plan_aprsl_pds_tbl
2717 (l_plan_aprsl_pds_index
2718 ).initiator_code,
2719 p_type => g_plan_aprsl_pds_tbl
2720 (l_plan_aprsl_pds_index
2721 ).appraisal_type,
2722 -- A column to be added to UI and table in per_appraisal_periods
2723 p_appraisal_system_type => g_plan_aprsl_pds_tbl
2724 (l_plan_aprsl_pds_index
2725 ).appraisal_system_type,
2726 p_participation_type => g_plan_aprsl_pds_tbl
2727 (l_plan_aprsl_pds_index
2728 ).participation_type,
2729 p_questionnaire_template_id => g_plan_aprsl_pds_tbl
2730 (l_plan_aprsl_pds_index
2731 ).questionnaire_template_id,
2732 p_return_status => l_appr_ret_status
2733 );
2734 END IF; -- Eligibility Profile Check End
2735
2736 IF g_dbg
2737 THEN
2738 op (l_proc, g_debug_log, 80);
2739 END IF;
2740
2741 --
2742 l_plan_aprsl_pds_index :=
2743 g_plan_aprsl_pds_tbl.NEXT (l_plan_aprsl_pds_index);
2744
2745 -- WPM Logging Changes
2746 IF hr_wpm_mass_apr_push.g_wpm_person_actions
2747 (log_records_index).processing_status =
2748 'P'
2749 THEN
2750 hr_wpm_mass_apr_push.g_wpm_person_actions
2751 (log_records_index).processing_status :=
2752 'SUCCESS';
2753 -- Success
2754 END IF;
2755 --
2756 END LOOP;
2757
2758 --
2759 IF g_dbg
2760 THEN
2761 op (l_proc, g_debug_log, 90);
2762 END IF;
2763
2764 --
2765 -- Delete all pending transactions.. once this appraisal push
2766 -- completes, then these transactions are not needed anymore.
2767 --
2768 BEGIN
2769 OPEN c_trx (curr_scorecard.scorecard_id);
2770
2771 FETCH c_trx
2772 INTO l_trx_id;
2773
2774 CLOSE c_trx;
2775
2776 IF l_trx_id IS NOT NULL
2777 THEN
2778 BEGIN
2779 DELETE FROM hr_api_transaction_steps
2780 WHERE transaction_id = l_trx_id;
2781 EXCEPTION
2782 WHEN OTHERS
2783 THEN
2784 NULL;
2785 END;
2786
2787 --
2788 BEGIN
2789 DELETE FROM hr_api_transactions
2790 WHERE transaction_id = l_trx_id;
2791 EXCEPTION
2792 WHEN OTHERS
2793 THEN
2794 NULL;
2795 END;
2796 --
2797 END IF;
2798 EXCEPTION
2799 WHEN OTHERS
2800 THEN
2801 NULL;
2802 END;
2803
2804 --
2805 -- Need to change the status of the scorecard to 'PUBLISHED'
2806 --
2807 l_sc_ovn := curr_scorecard.object_version_number;
2808
2809 BEGIN
2810 hr_personal_scorecard_api.update_scorecard_status
2811 (p_effective_date => TRUNC (SYSDATE),
2812 p_scorecard_id => curr_scorecard.scorecard_id,
2813 p_object_version_number => l_sc_ovn,
2814 p_status_code => 'PUBLISHED'
2815 );
2816 EXCEPTION
2817 WHEN OTHERS
2818 THEN
2819 NULL;
2820 END;
2821 --
2822 END IF; --include appraisal flag
2823
2824 --
2825 IF g_dbg
2826 THEN
2827 op (l_proc, g_debug_log, 100);
2828 END IF;
2829 END IF;
2830 END LOOP;
2831
2832 --
2833 IF g_dbg
2834 THEN
2835 op ('Number of errors occured:' || TO_CHAR (g_num_errors),
2836 g_regular_log,
2837 108
2838 );
2839 op ('Maximum errors allowed:' || g_max_errors, g_regular_log, 109);
2840 op ('Leaving:' || l_proc, g_regular_log, 110);
2841 END IF;
2842 EXCEPTION
2843 WHEN OTHERS
2844 THEN
2845 IF g_dbg
2846 THEN
2847 op ('Number of errors occured:' || TO_CHAR (g_num_errors),
2848 g_regular_log,
2849 88
2850 );
2851 op ('Maximum errors allowed:' || g_max_errors, g_regular_log, 89);
2852 op ('Leaving:' || l_proc, g_regular_log, 90);
2853 END IF;
2854
2855 --
2856 fnd_message.set_name ('PER', 'HR_50297_WPM_CP_ERROR');
2857 g_cp_error_txt := NVL (fnd_message.get, 'HR_50297_WPM_CP_ERROR');
2858 g_retcode := warning;
2859 g_errbuf := g_cp_error_txt;
2860 g_num_errors := g_num_errors + 1;
2861
2862 IF g_dbg
2863 THEN
2864 op (g_error_txt, g_regular_log);
2865 END IF;
2866
2867 IF g_dbg
2868 THEN
2869 op (SQLERRM, g_regular_log);
2870 END IF;
2871
2872 --
2873 -- If the max number of errors has been exceeded, raise the error and
2874 -- terminate processing of this plan.
2875 --
2876 IF g_num_errors > g_max_errors
2877 THEN
2878 fnd_message.set_name ('PER', 'HR_50298_WPM_MAX_ERRORS');
2879 g_error_txt := NVL (fnd_message.get, 'HR_50298_WPM_MAX_ERRORS');
2880 g_retcode := error;
2881 g_errbuf := g_error_txt;
2882 RAISE;
2883 END IF;
2884 END appraisal_push;
2885
2886 -- WPM Logging Changes
2887 PROCEDURE print_cache
2888 IS
2889 l_proc VARCHAR2 (80) := g_package || 'print_cache';
2890 l_evaluated NUMBER (9) := 0;
2891 l_successful NUMBER (9) := 0;
2892 l_error NUMBER (9) := 0;
2893 l_warning NUMBER (9) := 0;
2894 l_person_name per_people_f.full_name%TYPE;
2895 l_person_index BINARY_INTEGER;
2896
2897 CURSOR get_person_name (p_person_id IN per_all_people_f.person_id%TYPE)
2898 IS
2899 SELECT full_name
2900 FROM per_all_people_f ppf
2901 WHERE ppf.person_id = p_person_id
2902 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
2903 AND ppf.effective_end_date;
2904 BEGIN
2905 op ('Entering:' || l_proc, g_regular_log, 10);
2906 op ( 'Time before printing cache '
2907 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'),
2908 g_regular_log,
2909 10
2910 );
2911 op ('Populating records into reporting table...', g_regular_log, 10);
2912 l_person_index := g_wpm_person_actions.FIRST;
2913
2914 WHILE (l_person_index IS NOT NULL)
2915 LOOP
2916 BEGIN
2917 OPEN get_person_name
2918 (g_wpm_person_actions (l_person_index).person_id
2919 );
2920
2921 FETCH get_person_name
2922 INTO l_person_name;
2923
2924 CLOSE get_person_name;
2925
2926 INSERT INTO per_wpm_person_actions
2927 (wpm_person_action_id,
2928 wpm_batch_action_id,
2929 person_id,
2930 assignment_id,
2931 business_group_id,
2932 processing_status,
2933 eligibility_status,
2934 MESSAGE_TYPE,
2935 message_number,
2936 MESSAGE_TEXT,
2937 transaction_ref_table,
2938 transaction_ref_id,
2939 information_category,
2940 information1,
2941 information2,
2942 information3,
2943 information4,
2944 information5,
2945 information6,
2946 information7,
2947 information8,
2948 information9,
2949 information10,
2950 information11,
2951 information12,
2952 information13,
2953 information14,
2954 information15,
2955 information16,
2956 information17,
2957 information18,
2958 information19,
2959 information20
2960 )
2961 VALUES (per_wpm_person_actions_s.NEXTVAL,
2962 g_wpm_person_actions (l_person_index).wpm_batch_action_id,
2963 g_wpm_person_actions (l_person_index).person_id,
2964 g_wpm_person_actions (l_person_index).assignment_id,
2965 g_wpm_person_actions (l_person_index).business_group_id,
2966 g_wpm_person_actions (l_person_index).processing_status,
2967 g_wpm_person_actions (l_person_index).eligibility_status,
2968 g_wpm_person_actions (l_person_index).MESSAGE_TYPE,
2969 g_wpm_person_actions (l_person_index).message_number,
2970 g_wpm_person_actions (l_person_index).MESSAGE_TEXT,
2971 g_wpm_person_actions (l_person_index).transaction_ref_table,
2972 g_wpm_person_actions (l_person_index).transaction_ref_id,
2973 g_wpm_person_actions (l_person_index).information_category,
2974 g_wpm_person_actions (l_person_index).information1,
2975 g_wpm_person_actions (l_person_index).information2,
2976 g_wpm_person_actions (l_person_index).information3,
2977 g_wpm_person_actions (l_person_index).information4,
2978 g_wpm_person_actions (l_person_index).information5,
2979 g_wpm_person_actions (l_person_index).information6,
2980 g_wpm_person_actions (l_person_index).information7,
2981 g_wpm_person_actions (l_person_index).information8,
2982 g_wpm_person_actions (l_person_index).information9,
2983 g_wpm_person_actions (l_person_index).information10,
2984 g_wpm_person_actions (l_person_index).information11,
2985 g_wpm_person_actions (l_person_index).information12,
2986 g_wpm_person_actions (l_person_index).information13,
2987 g_wpm_person_actions (l_person_index).information14,
2988 g_wpm_person_actions (l_person_index).information15,
2989 g_wpm_person_actions (l_person_index).information16,
2990 g_wpm_person_actions (l_person_index).information17,
2991 g_wpm_person_actions (l_person_index).information18,
2992 g_wpm_person_actions (l_person_index).information19,
2993 g_wpm_person_actions (l_person_index).information20
2994 );
2995
2996 IF (g_wpm_person_actions (l_person_index).MESSAGE_TYPE = 'E')
2997 THEN
2998 l_error := l_error + 1;
2999 ELSIF (g_wpm_person_actions (l_person_index).MESSAGE_TYPE = 'W')
3000 THEN
3001 l_warning := l_warning + 1;
3002 ELSE
3003 l_successful := l_successful + 1;
3004 END IF;
3005 EXCEPTION
3006 WHEN OTHERS
3007 THEN
3008 l_error := l_error + 1;
3009 op (SQLERRM, g_regular_log, 10);
3010 op ( 'Insertion falied for: '
3011 || l_person_name
3012 || '('
3013 || g_wpm_person_actions (l_person_index).person_id
3014 || ').',
3015 g_regular_log,
3016 10
3017 );
3018 END;
3019
3020 l_person_index := g_wpm_person_actions.NEXT (l_person_index);
3021 END LOOP;
3022
3023 op ( 'Time at the end of printing cache '
3024 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'),
3025 g_regular_log,
3026 10
3027 );
3028 --
3029 l_evaluated := l_successful + l_error + l_warning;
3030 op
3031 ('=======================Summary of the run =========================',
3032 g_regular_log,
3033 10
3034 );
3035 op ('No of assignments evaluated in this run.' || l_evaluated,
3036 g_regular_log,
3037 10
3038 );
3039 op ('No of assignments successful in this run. ' || l_successful,
3040 g_regular_log,
3041 10
3042 );
3043 op ('No of assignments with warning in this run. ' || l_warning,
3044 g_regular_log,
3045 10
3046 );
3047 op ('No of assignments errored in this run. ' || l_error,
3048 g_regular_log,
3049 10
3050 );
3051 END print_cache;
3052
3053 --
3054 --
3055 -- ----------------------------------------------------------------------------
3056 -- |---------------------< submit_ma_change_cp >---------------------------|
3057 -- ----------------------------------------------------------------------------
3058 --
3059 PROCEDURE submit_ma_change_cp (
3060 p_batch_action_id IN NUMBER,
3061 p_request_id OUT NOCOPY NUMBER,
3062 p_effective_date IN VARCHAR2 DEFAULT NULL,
3063 p_log_output IN VARCHAR2 DEFAULT 'Y'
3064 )
3065 IS
3066 --
3067 l_proc VARCHAR2 (80) := g_package || 'submit_ma_change_cp';
3068 l_request_id NUMBER := -1;
3069 l_effective_date VARCHAR2 (30)
3070 := fnd_date.date_to_canonical (NVL (p_effective_date,
3071 TRUNC (SYSDATE))
3072 );
3073 --
3074 BEGIN
3075 -- Submit the request
3076 op ('Entering:' || l_proc, g_regular_log, 10);
3077 l_request_id :=
3078 fnd_request.submit_request (application => 'PER',
3079 program => 'WPMMACHANGE',
3080 sub_request => FALSE,
3081 start_time => SYSDATE,
3082 argument1 => l_effective_date,
3083 argument2 => p_batch_action_id,
3084 argument4 => p_log_output
3085 );
3086
3087 --
3088 IF l_request_id > 0
3089 THEN
3090 p_request_id := l_request_id;
3091 END IF;
3092
3093 op ('Request Id: ' || l_request_id, g_regular_log, 10);
3094 --
3095 END submit_ma_change_cp;
3096
3097 --
3098 -- ----------------------------------------------------------------------------
3099 -- |---------------------------< ma_change_cp >----------------------------|
3100 -- ----------------------------------------------------------------------------
3101 --
3102 PROCEDURE ma_change_cp (
3103 errbuf OUT NOCOPY VARCHAR2,
3104 retcode OUT NOCOPY NUMBER,
3105 p_effective_date IN VARCHAR2,
3106 p_batch_action_id IN NUMBER,
3107 p_log_output IN VARCHAR2 DEFAULT 'Y'
3108 )
3109 IS
3110 CURSOR person_action_details (p_batch_action_id IN NUMBER)
3111 IS
3112 SELECT ben.wpm_person_action_id, ben.information1 appraisal_id,
3113 ben.information3 new_main_appraiser_id
3114 FROM per_wpm_person_actions ben
3115 WHERE ben.wpm_batch_action_id = p_batch_action_id
3116 AND ben.processing_status = 'PENDING'
3117 ORDER BY ben.wpm_person_action_id;
3118
3119 CURSOR appraisal_details (p_appraisal_id IN NUMBER)
3120 IS
3121 SELECT pa.plan_id, pa.appraiser_person_id, pa.object_version_number,
3122 usr.user_name, pa.main_appraiser_id, pa.appraisee_person_id,
3123 ppl.full_name
3124 FROM per_appraisals pa, fnd_user usr, per_all_people_f ppl
3125 WHERE appraisal_id = p_appraisal_id
3126 AND ppl.person_id = usr.employee_id(+)
3127 AND ppl.person_id = pa.main_appraiser_id
3128 AND TRUNC (SYSDATE) BETWEEN ppl.effective_start_date
3129 AND ppl.effective_end_date;
3130
3131 CURSOR in_plan (p_plan_id IN NUMBER, p_person_id IN NUMBER)
3132 IS
3133 SELECT 'Y'
3134 FROM per_personal_scorecards
3135 WHERE plan_id = p_plan_id
3136 AND person_id = p_person_id
3137 AND status_code <> 'TRANSFER_OUT';
3138
3139 CURSOR get_wf_details (p_appraisal_id IN NUMBER)
3140 IS
3141 SELECT item_type, item_key
3142 FROM hr_api_transactions
3143 WHERE transaction_ref_table = 'PER_APPRAISALS'
3144 AND transaction_ref_id = p_appraisal_id;
3145
3146 CURSOR get_instance_label (p_block_activity_id IN NUMBER)
3147 IS
3148 SELECT instance_label
3149 FROM wf_process_activities
3150 WHERE instance_id = p_block_activity_id;
3151
3152 CURSOR get_role (p_person_id IN per_all_people_f.person_id%TYPE)
3153 IS
3154 SELECT wf.NAME role_name, ppl.full_name
3155 FROM wf_roles wf, per_all_people_f ppl
3156 WHERE wf.orig_system = 'PER'
3157 AND wf.orig_system_id = p_person_id
3158 AND wf.orig_system_id = ppl.person_id
3159 AND TRUNC (SYSDATE) BETWEEN ppl.effective_start_date
3160 AND ppl.effective_end_date;
3161
3162 l_proc VARCHAR2 (80)
3163 DEFAULT g_package || 'ma_change_cp';
3164 l_object_version_number NUMBER;
3165 l_wpm_person_action_id NUMBER;
3166 l_new_main_appraiser_id NUMBER;
3167 l_appraiser_person_id NUMBER;
3168 l_appraisal_id NUMBER;
3169 blockedactivityid NUMBER;
3170 l_instance_label VARCHAR2 (100);
3171 l_user_name fnd_user.user_name%TYPE;
3172 l_main_appraiser_id per_all_people_f.person_id%TYPE;
3173 l_appraisee_person_id per_all_people_f.person_id%TYPE;
3174 l_full_name per_all_people_f.full_name%TYPE;
3175 l_item_type VARCHAR2 (30);
3176 l_item_key VARCHAR2 (80);
3177 l_effective_date DATE
3178 DEFAULT fnd_date.canonical_to_date (NVL (p_effective_date,
3179 TRUNC (SYSDATE)
3180 )
3181 );
3182 l_no_of_errors NUMBER DEFAULT 0;
3183 l_no_of_processed NUMBER DEFAULT 0;
3184 ecode NUMBER;
3185 emesg VARCHAR2 (4000);
3186 l_to_wkr_role wf_local_roles.NAME%TYPE DEFAULT NULL;
3187 l_to_wkr_name per_all_people_f.full_name%TYPE;
3188 l_to_mgr_role wf_local_roles.NAME%TYPE DEFAULT NULL;
3189 l_to_mgr_name per_all_people_f.full_name%TYPE;
3190 l_to_nma_role wf_local_roles.NAME%TYPE DEFAULT NULL;
3191 l_to_nma_name per_all_people_f.full_name%TYPE;
3192 l_from_role wf_local_roles.NAME%TYPE DEFAULT NULL;
3193 ln_notification_id NUMBER;
3194 l_dummy VARCHAR2 (1) DEFAULT 'N';
3195 l_plan_id per_personal_scorecards.plan_id%TYPE;
3196 BEGIN
3197 initialize_logging (p_action_parameter_group_id => NULL,
3198 p_log_output => p_log_output
3199 );
3200 hr_utility.set_location ('p_batch_action_id: ' || p_batch_action_id, 11);
3201 op ('Entering: ' || l_proc, g_regular_log, 10);
3202 op ('p_batch_action_id: ' || p_batch_action_id, g_regular_log, 20);
3203 errbuf := NULL;
3204 retcode := success;
3205
3206 OPEN person_action_details (p_batch_action_id);
3207
3208 LOOP
3209 FETCH person_action_details
3210 INTO l_wpm_person_action_id, l_appraisal_id,
3211 l_new_main_appraiser_id;
3212
3213 EXIT WHEN person_action_details%NOTFOUND;
3214
3215 OPEN appraisal_details (l_appraisal_id);
3216
3217 FETCH appraisal_details
3218 INTO l_plan_id, l_appraiser_person_id, l_object_version_number,
3219 l_user_name, l_main_appraiser_id, l_appraisee_person_id,
3220 l_full_name;
3221
3222 CLOSE appraisal_details;
3223
3224 OPEN get_role (l_appraisee_person_id);
3225
3226 FETCH get_role
3227 INTO l_to_wkr_role, l_to_wkr_name;
3228
3229 CLOSE get_role;
3230
3231 OPEN get_role (l_main_appraiser_id);
3232
3233 FETCH get_role
3234 INTO l_to_mgr_role, l_to_mgr_name;
3235
3236 CLOSE get_role;
3237
3238 OPEN get_role (l_new_main_appraiser_id);
3239
3240 FETCH get_role
3241 INTO l_to_nma_role, l_to_nma_name;
3242
3243 CLOSE get_role;
3244
3245 op ('l_wpm_person_action_id: ' || l_wpm_person_action_id,
3246 g_regular_log
3247 );
3248 op ('l_appraisal_id: ' || l_appraisal_id, g_regular_log);
3249 op ('l_main_appraiser_id: ' || l_main_appraiser_id, g_regular_log);
3250 op ('l_new_main_appraiser_id: ' || l_new_main_appraiser_id,
3251 g_regular_log
3252 );
3253 op ('l_plan_id: ' || l_plan_id, g_regular_log);
3254 op ('Appraisee: ' || l_to_wkr_name || ' (' || l_to_wkr_role || ').',
3255 g_regular_log
3256 );
3257 op ('Main Appraisee: ' || l_to_mgr_name || ' (' || l_to_mgr_role
3258 || ').',
3259 g_regular_log
3260 );
3261 op ( 'New Main Appraiser: '
3262 || l_to_nma_name
3263 || ' ('
3264 || l_to_nma_role
3265 || ').',
3266 g_regular_log
3267 );
3268 hr_utility.set_location ( 'l_wpm_person_action_id: '
3269 || l_wpm_person_action_id,
3270 11
3271 );
3272 hr_utility.set_location ('l_appraisal_id: ' || l_appraisal_id, 11);
3273 hr_utility.set_location ( 'l_appraisee_person_id: '
3274 || l_appraisee_person_id,
3275 11
3276 );
3277 hr_utility.set_location ( 'l_main_appraiser_id: '
3278 || l_main_appraiser_id,
3279 11
3280 );
3281 hr_utility.set_location ( 'l_new_main_appraiser_id: '
3282 || l_new_main_appraiser_id,
3283 11
3284 );
3285 hr_utility.set_location ('l_to_wkr_role: ' || l_to_wkr_role, 11);
3286 hr_utility.set_location ('l_to_wkr_name: ' || l_to_wkr_name, 11);
3287 hr_utility.set_location ('l_to_mgr_role: ' || l_to_mgr_role, 11);
3288 hr_utility.set_location ('l_to_mgr_name: ' || l_to_mgr_name, 11);
3289 hr_utility.set_location ('l_to_nma_role: ' || l_to_nma_role, 11);
3290 hr_utility.set_location ('l_to_nma_name: ' || l_to_nma_name, 11);
3291
3292 IF (l_plan_id IS NOT NULL)
3293 THEN -- need not check for the standard appraisals.
3294 OPEN in_plan (l_plan_id, l_new_main_appraiser_id);
3295
3296 FETCH in_plan
3297 INTO l_dummy;
3298
3299 IF in_plan%NOTFOUND
3300 THEN
3301 op ( l_to_nma_name
3302 || ' is not part of the plan: '
3303 || l_plan_id
3304 || '. Main Appraiser Change failed.',
3305 g_regular_log
3306 );
3307 EXIT;
3308 END IF;
3309 END IF;
3310
3311 BEGIN
3312 op ('Updating appraisal: ' || l_appraisal_id, g_regular_log);
3313 hr_appraisals_api.update_appraisal
3314 (p_validate => FALSE,
3315 p_effective_date => l_effective_date,
3316 p_appraisal_id => l_appraisal_id,
3317 p_appraiser_person_id => l_appraiser_person_id,
3318 p_main_appraiser_id => l_new_main_appraiser_id,
3319 p_object_version_number => l_object_version_number
3320 );
3321 op ('Successfully updated appraisal: ' || l_appraisal_id,
3322 g_regular_log
3323 );
3324
3325 OPEN get_wf_details (l_appraisal_id);
3326
3327 FETCH get_wf_details
3328 INTO l_item_type, l_item_key;
3329
3330 CLOSE get_wf_details;
3331
3332 op ('l_item_type: ' || l_item_type, g_regular_log);
3333 op ('l_item_key: ' || l_item_key, g_regular_log);
3334
3335 IF (l_item_type IS NOT NULL AND l_item_key IS NOT NULL)
3336 THEN
3337 blockedactivityid :=
3338 wf_engine.getitemattrnumber (l_item_type,
3339 l_item_key,
3340 'HR_APPRAI_MAIN_BLOCK_ID_ATTR'
3341 );
3342
3343 OPEN get_instance_label (blockedactivityid);
3344
3345 FETCH get_instance_label
3346 INTO l_instance_label;
3347
3348 CLOSE get_instance_label;
3349
3350 op ('blockedactivityid: ' || blockedactivityid, g_regular_log);
3351 op ('l_instance_label: ' || l_instance_label, g_regular_log);
3352 hr_utility.set_location ( 'blockedactivityid: '
3353 || blockedactivityid,
3354 11
3355 );
3356 hr_utility.set_location ( 'l_instance_label: '
3357 || l_instance_label,
3358 11
3359 );
3360 wf_engine.setitemattrtext (l_item_type,
3361 l_item_key,
3362 'HR_APPRAISAL_FROM_USER_ATTR',
3363 l_user_name
3364 );
3365 wf_engine.setitemattrtext (l_item_type,
3366 l_item_key,
3367 'HR_APPRAISAL_FROM_NAME_ATTR',
3368 l_full_name
3369 );
3370 wf_engine.setitemattrtext (l_item_type,
3371 l_item_key,
3372 'HR_BLOCK_ATTR',
3373 'N'
3374 );
3375 wf_engine.setitemattrtext (l_item_type,
3376 l_item_key,
3377 'HR_APPRAISAL_NTF_COMMENT_ATTR',
3378 ''
3379 );
3380 blockedactivityid :=
3381 wf_engine.getitemattrnumber (l_item_type,
3382 l_item_key,
3383 'HR_APPRAI_MAIN_BLOCK_ID_ATTR'
3384 );
3385 wf_engine.completeactivity (l_item_type,
3386 l_item_key,
3387 l_instance_label,
3388 'HR_NEW_MAIN_APPRAISER'
3389 );
3390 ELSE
3391 op ( 'Workflow attrs not updated. No transactions found.'
3392 || l_item_key,
3393 g_regular_log
3394 );
3395 END IF;
3396
3397 IF (l_to_wkr_role IS NOT NULL)
3398 THEN
3399 ln_notification_id :=
3400 wf_notification.send (ROLE => l_to_wkr_role,
3401 msg_type => 'HRWPM',
3402 msg_name => 'HR_MA_CHANGE_WKR',
3403 callback => NULL,
3404 CONTEXT => NULL,
3405 send_comment => NULL,
3406 priority => 50
3407 );
3408 wf_notification.setattrtext (ln_notification_id,
3409 'NEW_MGR',
3410 l_to_nma_name
3411 );
3412 op ('1)ln_notification_id: ' || ln_notification_id,
3413 g_regular_log
3414 );
3415 hr_utility.set_location ( '1)ln_notification_id:: '
3416 || ln_notification_id,
3417 11
3418 );
3419 wf_notification.denormalize_notification (ln_notification_id,
3420 NULL,
3421 NULL
3422 );
3423 ELSE
3424 op ('No WF Role defined for: ' || l_to_wkr_name,
3425 g_regular_log);
3426 op ('Not able to send notification.', g_regular_log);
3427 END IF;
3428
3429 IF (l_to_mgr_role IS NOT NULL)
3430 THEN
3431 ln_notification_id :=
3432 wf_notification.send (ROLE => l_to_mgr_role,
3433 msg_type => 'HRWPM',
3434 msg_name => 'HR_MA_CHANGE_CURR_MGR',
3435 callback => NULL,
3436 CONTEXT => NULL,
3437 send_comment => NULL,
3438 priority => 50
3439 );
3440 wf_notification.setattrtext (ln_notification_id,
3441 'NEW_MGR',
3442 l_to_nma_name
3443 );
3444 wf_notification.setattrtext (ln_notification_id,
3445 'WKR_NAME',
3446 l_to_wkr_name
3447 );
3448 op ('2) ln_notification_id: ' || ln_notification_id,
3449 g_regular_log
3450 );
3451 hr_utility.set_location ( '2)ln_notification_id:: '
3452 || ln_notification_id,
3453 11
3454 );
3455 wf_notification.denormalize_notification (ln_notification_id,
3456 NULL,
3457 NULL
3458 );
3459 ELSE
3460 op ('No WF Role defined for: ' || l_to_mgr_name,
3461 g_regular_log);
3462 op ('Not able to send notification.', g_regular_log);
3463 END IF;
3464
3465 IF (l_to_nma_role IS NOT NULL)
3466 THEN
3467 ln_notification_id :=
3468 wf_notification.send (ROLE => l_to_nma_role,
3469 msg_type => 'HRWPM',
3470 msg_name => 'HR_MA_CHANGE_NEW_MGR',
3471 callback => NULL,
3472 CONTEXT => NULL,
3473 send_comment => NULL,
3474 priority => 50
3475 );
3476 wf_notification.setattrtext (ln_notification_id,
3477 'WKR_NAME',
3478 l_to_wkr_name
3479 );
3480 op ('3) ln_notification_id: ' || ln_notification_id,
3481 g_regular_log
3482 );
3483 hr_utility.set_location ( '3)ln_notification_id:: '
3484 || ln_notification_id,
3485 11
3486 );
3487 wf_notification.denormalize_notification (ln_notification_id,
3488 NULL,
3489 NULL
3490 );
3491 ELSE
3492 op ('No WF Role defined for: ' || l_to_nma_name,
3493 g_regular_log);
3494 op ('Not able to send notification.', g_regular_log);
3495 END IF;
3496
3497 UPDATE per_wpm_person_actions
3498 SET processing_status = 'SUCCESS'
3499 WHERE wpm_person_action_id = l_wpm_person_action_id;
3500
3501 l_no_of_processed := l_no_of_processed + 1;
3502 EXCEPTION
3503 WHEN OTHERS
3504 THEN
3505 l_no_of_errors := l_no_of_errors + 1;
3506 ecode := SQLCODE;
3507 emesg := SUBSTR (SQLERRM, 1, 4000);
3508 hr_utility.set_location ('l_no_of_errors:: ' || l_no_of_errors,
3509 11
3510 );
3511 hr_utility.set_location ('ecode: ' || ecode, 11);
3512 hr_utility.set_location ('emesg:: ' || emesg, 11);
3513
3514 UPDATE per_wpm_person_actions
3515 SET processing_status = 'ERROR',
3516 message_number = ecode,
3517 MESSAGE_TEXT = emesg
3518 WHERE wpm_person_action_id = l_wpm_person_action_id;
3519 END;
3520 END LOOP;
3521
3522 CLOSE person_action_details;
3523
3524 CLOSE in_plan;
3525
3526 UPDATE per_wpm_batch_actions
3527 SET status =
3528 DECODE (l_no_of_processed,
3529 0, 'ERROR',
3530 DECODE (l_no_of_errors, 0, 'SUCCESS', 'WARNING')
3531 ),
3532 conc_request_id = fnd_global.conc_request_id,
3533 information19 = TO_CHAR (l_no_of_processed),
3534 information20 = TO_CHAR (l_no_of_errors)
3535 WHERE wpm_batch_action_id = p_batch_action_id;
3536
3537 IF (l_no_of_processed = 0)
3538 THEN
3539 retcode := error;
3540 ELSIF (l_no_of_errors > 0)
3541 THEN
3542 retcode := warning;
3543 END IF;
3544
3545 COMMIT;
3546 EXCEPTION
3547 WHEN OTHERS
3548 THEN
3549 ROLLBACK;
3550
3551 UPDATE per_wpm_batch_actions
3552 SET status = 'ERROR',
3553 conc_request_id = fnd_global.conc_request_id,
3554 end_date = SYSDATE
3555 WHERE wpm_batch_action_id = l_current_wpm_batch_action_id;
3556
3557 op ('Error ' || SQLCODE, g_regular_log);
3558 op ('Error ' || SUBSTR (SQLERRM, 1, 200), g_regular_log);
3559 hr_utility.set_location ('Error ' || SQLCODE, 11);
3560 hr_utility.set_location ('Error ' || SQLERRM, 11);
3561 COMMIT;
3562 errbuf := g_errbuf;
3563 retcode := error;
3564 END ma_change_cp;
3565 END hr_wpm_mass_apr_push;