[Home] [Help]
PACKAGE BODY: APPS.PQH_RANKING
Source
1 PACKAGE BODY pqh_ranking AS
2 /* $Header: pqrnkpkg.pkb 120.8 2006/01/23 10:57:25 nsanghal noship $ */
3 --
4 --
5 g_package constant varchar2(20) := ' pqh_ranking.';
6
7 PGI_SETUP_NOT_FOUND EXCEPTION;
8
9 -- ---------------------------------------------------------------------------
10 -- --------------------- <write_log> -----------------------------------------
11 -- ---------------------------------------------------------------------------
12 PROCEDURE write_log (p_write_log in varchar2, p_log_text in varchar2) IS
13 Begin
14 If (p_write_log = 'Y') then
15 --
16 fnd_file.put_line (
17 which => fnd_file.log,
18 buff => p_log_text);
19 --
20 end if;
21 End write_log;
22 --
23 -- ---------------------------------------------------------------------------
24 -- ------------- <Handle_Error> ----------------------------------------------
25 -- ---------------------------------------------------------------------------
26 PROCEDURE handle_error ( p_error_code in varchar2) IS
27 l_proc constant varchar2(72):= g_package||'handle_error';
28 BEGIN
29
30 hr_utility.set_location('Entering:'||l_proc, 5);
31 IF (p_error_code = 'PGI_SETUP_NOT_FOUND') THEN
32 raise PGI_SETUP_NOT_FOUND;
33 END IF;
34
35 hr_utility.set_location(' Leaving:'||l_proc, 10);
36 Exception
37 WHEN PGI_SETUP_NOT_FOUND THEN
38 raise;
39
40 END;
41 --
42 --
43 -- ---------------------------------------------------------------------------
44 -- ------------- <get_emp_count_on_pgm> --------------------------------------
45 -- ---------------------------------------------------------------------------
46 FUNCTION get_emp_count_on_pgm (p_benefit_action_id in number, p_pgm_id in number)
47 RETURN number is
48 l_count number;
49 l_proc constant varchar2(72):= g_package||'get_emp_count_on_pgm';
50 BEGIN
51
52 hr_utility.set_location('Entering:'||l_proc, 5);
53 SELECT count(1)
54 INTO l_count
55 FROM pqh_rank_processes
56 WHERE pgm_id = p_pgm_id
57 AND (p_benefit_action_id IS NULL or benefit_action_id = p_benefit_action_id);
58
59 RETURN l_count;
60
61 hr_utility.set_location(' Leaving:'||l_proc, 10);
62 Exception
63 WHEN Others THEN
64 raise;
65 END get_emp_count_on_pgm ;
66 --
67 -- ---------------------------------------------------------------------------
68 -- ------------- <create_update_rank_approvals> ------------------------------
69 -- ---------------------------------------------------------------------------
70 PROCEDURE create_update_rank_approvals (
71 p_rank_process_id in NUMBER,
72 p_rank in number,
73 p_population_count in number) IS
74
75 Cursor csr_ranks is
76 Select rank_process_approval_id,object_version_number
77 From pqh_rank_process_approvals
78 Where rank_process_id = p_rank_process_id;
79 --
80 l_rank_process_approval_id number;
81 l_ovn number;
82 isFound boolean ;
83 --
84 BEGIN
85 isFound := false;
86 For rec_ranks in csr_ranks Loop
87 pqh_rank_process_approval_api.update_rank_process_approval (
88 p_effective_date => sysdate
89 ,p_rank_process_approval_id => rec_ranks.rank_process_approval_id
90 ,p_rank_process_id => p_rank_process_id
91 ,p_approval_date => sysdate
92 ,p_system_rank => p_rank
93 ,p_population_count => p_population_count
94 ,p_proposed_rank => p_rank
95 ,p_object_version_number => rec_ranks.object_version_number );
96 isFound := true;
97 End Loop;
98
99 if NOT(isFound) then
100 select pqh_rank_process_approvals_s.nextval
101 into l_rank_process_approval_id from dual;
102 -- Create Ranks
103 pqh_rank_process_approval_api.create_rank_process_approval (
104 p_effective_date => sysdate
105 ,p_rank_process_approval_id => l_rank_process_approval_id
106 ,p_rank_process_id => p_rank_process_id
107 ,p_approval_date => sysdate
108 ,p_system_rank => p_rank
109 ,p_population_count => p_population_count
110 ,p_proposed_rank => p_rank
111 ,p_object_version_number => l_ovn );
112 END IF; -- Rank Exists
113 --
114 END create_update_rank_approvals;
115
116 -- ---------------------------------------------------------------------------
117 -- ------------- <is_workflow_enabled> ---------------------------------------
118 -- ---------------------------------------------------------------------------
119 FUNCTION is_workflow_enabled ( p_pgm_id in number) return varchar2 IS
120 l_rank_enabled varchar2(10);
121 l_handle_dup varchar2(10);
122 l_group_score varchar2(10);
123 l_wf_enabled varchar2(10);
124 l_result varchar2(240);
125 l_proc constant varchar2(72):= g_package||'is_workflow_enabled';
126 BEGIN
127
128 hr_utility.set_location('Entering:'||l_proc, 5);
129 get_pgi_info (
130 p_pgm_id => p_pgm_id
131 ,p_workflow_enabled => l_wf_enabled
132 ,p_rank_enabled => l_rank_enabled
133 ,p_handle_duplicate => l_handle_dup
134 ,p_group_score => l_group_score
135 ,p_result => l_result );
136
137 return l_wf_enabled;
138
139 hr_utility.set_location(' Leaving:'||l_proc, 10);
140 Exception
141 WHEN Others THEN
142 raise;
143 END;
144 --
145 -- ---------------------------------------------------------------------------
146 -- ------------- <Get_Pgi_Info> ----------------------------------------------
147 -- ---------------------------------------------------------------------------
148 PROCEDURE get_pgi_info (
149 p_pgm_id in number
150 ,p_workflow_enabled out nocopy varchar2
151 ,p_rank_enabled out nocopy varchar2
152 ,p_handle_duplicate out nocopy varchar2
153 ,p_group_score out nocopy varchar2
154 ,p_result out nocopy varchar2) IS
155 Cursor csr_pgi IS
156 SELECT pgi_information1 wf_enabled,
157 pgi_information2 rank_enabled,
158 NVL(pgi_information4,'SUM') duplicate_handling,
159 pgi_information3 group_score
160 FROM ben_pgm_extra_info
161 WHERE pgm_id = p_pgm_id
162 AND information_type = 'PQH_GSP_EXTRA_INFO';
163
164 l_proc constant varchar2(72):= g_package||'get_pgi_info';
165 BEGIN
166
167 hr_utility.set_location('Entering:'||l_proc, 5);
168 open csr_pgi;
169 FETCH csr_pgi INTO p_workflow_enabled, p_rank_enabled, p_handle_duplicate, p_group_score;
170 IF (csr_pgi%NOTFOUND ) THEN
171 --
172 p_result := 'PGI_SETUP_NOT_FOUND';
173 --
174 END IF;
175 CLOSE csr_pgi;
176
177 hr_utility.set_location(' Leaving:'||l_proc, 10);
178 Exception
179 WHEN Others THEN
180 raise;
181 END;
182
183 -- ---------------------------------------------------------------------------
184 -- ------------- <Compute_Rank_For_GSP> --------------------------------------
185 -- ---------------------------------------------------------------------------
186 PROCEDURE compute_rank_for_GSP (
187 errbuf out nocopy VARCHAR2,
188 retcode out nocopy NUMBER,
189 p_business_group_id in NUMBER,
190 p_pgm_id in NUMBER,
191 p_pl_id in NUMBER,
192 p_process_dt_start in VARCHAR2,
193 p_process_dt_end in VARCHAR2,
194 p_benefit_action_id in NUMBER,
195 p_audit_log in VARCHAR2,
196 p_commit_data in VARCHAR2,
197 p_rank_wf_pending in VARCHAR2
198 ) IS
199 /**
200 * Cursor to find grade ladders for a given
201 * benefit action
202 */
203 CURSOR csr_pgm IS
204 SELECT distinct pgm_id
205 FROM pqh_rank_processes
206 WHERE benefit_action_id = p_benefit_action_id;
207
208 /*
209 * Cursor to find grade ladders within a given time
210 * period and/or the grade
211 */
212 Cursor csr_pgm_no_ba (c_dt_start in date, c_dt_end date) is
213 select distinct rnk.pgm_id
214 FROM pqh_rank_processes rnk,
215 ben_per_in_ler pil
216 where (p_pl_id is null OR rnk.pl_id = p_pl_id)
217 and rnk.per_in_ler_id = pil.per_in_ler_id
218 and pil.business_group_id = p_business_group_id
219 and pil.per_in_ler_stat_cd = 'STRTD'
220 and pil.strtd_dt between c_dt_start and c_dt_end ;
221
222 /*
223 * Cursor to find grade and population cound for a given grade ladder
224 * and benefit action
225 */
226 CURSOR csr_gr (c_pgm_id in number) IS
227 SELECT pl_id, count(1) emp_cnt
228 FROM pqh_rank_processes
229 WHERE (p_benefit_action_id is null or benefit_action_id = p_benefit_action_id)
230 AND pgm_id = c_pgm_id
231 GROUP BY pl_id;
232
233 /*
234 * Cursor to find grade and population cound for a given grade ladder
235 * when benefit action is not specified
236 */
237 cursor csr_gr_no_ba (c_pgm_id in number, c_pl_id in number,
238 c_dt_start in date, c_dt_end in date ) is
239 select rnk.pl_id, count(1) emp_cnt
240 FROM pqh_rank_processes rnk,
241 ben_per_in_ler pil
242 where rnk.pgm_id = c_pgm_id
243 and (c_pl_id is null OR rnk.pl_id = c_pl_id)
244 and rnk.per_in_ler_id = pil.per_in_ler_id
245 and pil.business_group_id = p_business_group_id
246 and pil.per_in_ler_stat_cd = 'STRTD'
247 and pil.strtd_dt between c_dt_start and c_dt_end
248 group by pl_id;
249
250 /*
251 * Cursor to find records to be ranked for the given grade ladder
252 * and benefit action
253 */
254 Cursor csr_rnk_pgm (c_pgm_id in number) Is
255 SELECT rank_process_id, total_score
256 FROM pqh_rank_processes
257 WHERE benefit_action_id = p_benefit_action_id
258 AND pgm_id = c_pgm_id
259 ORDER BY total_score DESC;
260
261 /*
262 * Cursor to find records to be ranked for the given grade ladder
263 * when benefit action is not specified
264 */
265 Cursor csr_rnk_pgm_no_ba (c_pgm_id in number,
266 c_dt_start in date, c_dt_end in date) Is
267 SELECT rnk.rank_process_id,rnk.total_score
268 FROM pqh_rank_processes rnk,
269 ben_per_in_ler pil,
270 ben_elig_per_elctbl_chc epec
271 WHERE rnk.pgm_id = c_pgm_id
272 AND rnk.per_in_ler_id = pil.per_in_ler_id
273 AND per_in_ler_stat_cd = 'STRTD'
274 AND pil.business_group_id = p_business_group_id
275 AND epec.per_in_ler_id = pil.per_in_ler_id
276 AND epec.business_group_id = pil.business_group_id
277 AND epec.pgm_id = c_pgm_id
278 AND epec.dflt_flag = 'Y'
279 AND approval_status_cd IS NULL
280 AND ( nvl(p_rank_wf_pending,'N') = 'Y' OR
281 in_pndg_wkflow_flag <> 'Y')
282 AND pil.strtd_dt between c_dt_start and c_dt_end
283 ORDER BY total_score DESC;
284 /*
285 * Cursor to find records to be ranked for a given grade,
286 * grade ladder and benefit action
287 */
288 Cursor csr_rnk_pl( c_pgm_id in number, c_pl_id in number ) Is
289 SELECT rank_process_id, total_score
290 FROM pqh_rank_processes
291 WHERE benefit_action_id = p_benefit_action_id
292 AND pgm_id = c_pgm_id
293 AND pl_id = c_pl_id
294 ORDER BY total_score DESC;
295
296 /*
297 * Cursor to find records to be ranked for a given grade,
298 * grade ladder when benefit action is not specified
299 */
300 Cursor csr_rnk_pl_no_ba( c_pgm_id in number, c_pl_id in number ,
301 c_dt_start in date, c_dt_end in date ) Is
302 SELECT rnk.rank_process_id,rnk.total_score
303 FROM pqh_rank_processes rnk,
304 ben_per_in_ler pil,
305 ben_elig_per_elctbl_chc epec
306 WHERE rnk.pgm_id = c_pgm_id
307 AND rnk.pl_id = c_pl_id
308 AND rnk.per_in_ler_id = pil.per_in_ler_id
309 AND per_in_ler_stat_cd = 'STRTD'
310 AND pil.business_group_id = p_business_group_id
311 AND epec.per_in_ler_id = pil.per_in_ler_id
312 AND epec.business_group_id = pil.business_group_id
313 AND epec.pgm_id = c_pgm_id
314 AND epec.pl_id = c_pl_id
315 AND epec.dflt_flag = 'Y'
316 AND approval_status_cd IS NULL
317 AND ( nvl(p_rank_wf_pending,'N') = 'Y' OR
318 in_pndg_wkflow_flag <> 'Y')
319 and pil.strtd_dt between c_dt_start and c_dt_end
320 ORDER BY total_score DESC;
321
322 --
323 l_cnt NUMBER := 0;
324 --
325 l_rank_enabled varchar2(30);
326 l_wf_enabled varchar2(10);
327 l_handle_duplicate varchar2(30);
328 l_group_score varchar2(30);
329 l_result varchar2(30);
330 l_pgm_id number;
331 --
332 l_ovn number(15);
333 l_proc constant varchar2(72):= g_package||'compute_rank_for_gsp';
334 --
335 l_rank number;
336 l_prev_score number;
337 l_dummy number;
338 --
339 l_dt_start date;
340 l_dt_end date;
341 BEGIN
342 hr_utility.set_location('Entering:'||l_proc, 5);
343 l_dt_start := trunc(fnd_date.canonical_to_date(p_process_dt_start));
344 l_dt_end := trunc(fnd_date.canonical_to_date(p_process_dt_end ));
345
346 l_dt_end := nvl(l_dt_end , hr_general.end_of_time );
347 l_dt_start := nvl(l_dt_start, hr_general.start_of_time);
348
349 write_log(p_audit_log, 'BEGIN RANKING PROCESS');
350 write_log(p_audit_log, 'Parameter List');
351 write_log(p_audit_log, '------------------------------');
352 write_log(p_audit_log, 'Benefit Action Id: '||p_benefit_action_id);
353 write_log(p_audit_log, 'Grade Ladder Id: '||p_pgm_id);
354 write_log(p_audit_log, 'Grade Id: '||p_pl_id);
355 write_log(p_audit_log, 'Process Start Date: '||l_dt_start);
356 write_log(p_audit_log, 'Process End Date: '||l_dt_end);
357
358 --
359 /**
360 * CASE 1: BENEFIT_ACTION_ID is specified.
361 * ignore all other parameters, except audit_log and pending wf
362 */
363 IF (p_benefit_action_id IS NOT NULL) THEN
364 write_log(p_audit_log, 'Benefit Action Id: ' || p_benefit_action_id);
365 FOR rec_pgm in csr_pgm
366 Loop
367 write_log(p_audit_log, '-------------------------------------------------------');
368 write_log(p_audit_log, 'Ranking For Grade Ladder Id: ' || rec_pgm.pgm_id);
369 -- Get Grade ladder setup options
370 get_pgi_info (
371 p_pgm_id => rec_pgm.pgm_id,
372 p_workflow_enabled => l_wf_enabled,
373 p_rank_enabled => l_rank_enabled,
374 p_handle_duplicate => l_handle_duplicate,
375 p_group_score => l_group_score,
376 p_result => l_result );
377
378 -- If ranking is not enabled for the grade ladder
379 -- then skip to the next grade ladder.
380 IF ( l_rank_enabled <> 'Y') Then
381 write_log(p_audit_log, 'Ranking NOT enabled, skipping to next Grade Ladder' );
382 GOTO NEXT_RECORD_SET;
383 END IF;
384 --
385 IF (l_group_score = 'GL') then
386 write_log(p_audit_log, 'Ranking by Grade ladder');
387 l_cnt := get_emp_count_on_pgm (p_benefit_action_id, rec_pgm.pgm_id);
388 write_log(p_audit_log, 'Number of employees to be ranked on the grade ladder: ' || l_cnt);
389 -- Reset variables before starting
390 l_rank := 0;
391 l_prev_score := 0;
392 For rec_rnk In csr_rnk_pgm(rec_pgm.pgm_id) Loop
393 --
394 if ( l_prev_score <> rec_rnk.total_score) then
395 l_rank := l_rank + 1;
396 l_prev_score := rec_rnk.total_score;
397 end if;
398 --
399 create_update_rank_approvals(rec_rnk.rank_process_id
400 , l_rank
401 , l_cnt );
402 --
403 End Loop;
404 --
405 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
406 --
407 ELSIF l_group_score = 'GRADE' then
408 write_log(p_audit_log, 'Ranking by Grade');
409 --
410 for rec in csr_gr(rec_pgm.pgm_id) Loop
411 write_log(p_audit_log, 'Ranking For Grade Id: ' || rec.pl_id);
412 write_log(p_audit_log, 'Number of employees to be ranked on the grade: ' || rec.emp_cnt);
413 -- Reset variables for each grade before starting
414 l_rank := 0;
415 l_prev_score := 0;
416 For rec_rnk In csr_rnk_pl(rec_pgm.pgm_id, rec.pl_id) Loop
417 --
418 if ( l_prev_score <> rec_rnk.total_score) then
419 l_rank := l_rank + 1;
420 l_prev_score := rec_rnk.total_score;
421 end if;
422 --
423 create_update_rank_approvals(rec_rnk.rank_process_id
424 , l_rank
425 , rec.emp_cnt );
426 --
427 End Loop;
428 --
429 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
430 --
431 End Loop;
432 END IF;
433
434 <<NEXT_RECORD_SET>>
435 null;
436 END LOOP; -- PGMs
437 --
438 /**
439 * CASE 2: Grade Ladder (pgm_id) specified
440 *
441 */
442 ELSIF p_pgm_id is not null then
443 write_log(p_audit_log, 'Ranking For Grade Ladder Id: ' || p_pgm_id);
444 get_pgi_info (
445 p_pgm_id => p_pgm_id,
446 p_workflow_enabled => l_wf_enabled,
447 p_rank_enabled => l_rank_enabled,
448 p_handle_duplicate => l_handle_duplicate,
449 p_group_score => l_group_score,
450 p_result => l_result );
451
452 -- If ranking is not enabled for the grade ladder
453 -- then return with no further processing
454 IF ( l_rank_enabled <> 'Y') Then
455 write_log(p_audit_log, 'Ranking NOT enabled, no records to rank');
456 RETURN;
457 END IF;
458 --
459 --
460 IF (l_group_score = 'GL') then
461 write_log(p_audit_log, 'Ranking by Grade ladder');
462 l_cnt := get_emp_count_on_pgm (null, p_pgm_id);
463 write_log(p_audit_log, 'Number of employees to be ranked on the grade ladder:' || l_cnt);
464 --
465 -- Reset variables for each grade before starting
466 l_rank := 0;
467 l_prev_score := 0;
468 For rec_rnk In csr_rnk_pgm_no_ba(p_pgm_id, l_dt_start, l_dt_end ) Loop
469 --
470 if ( l_prev_score <> rec_rnk.total_score) then
471 l_rank := l_rank + 1;
472 l_prev_score := rec_rnk.total_score;
473 end if;
474 --
475 create_update_rank_approvals(rec_rnk.rank_process_id
476 , l_rank
477 , l_cnt );
478 --
479 End Loop;
480 --
481 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
482 --
483 ELSIF l_group_score = 'GRADE' then
484 --
485 write_log(p_audit_log, 'Ranking by Grade');
486 --
487 if (p_pl_id is null) then -- Rank all grades
488 for rec in csr_gr_no_ba(p_pgm_id,null, l_dt_start, l_dt_end ) Loop
489 write_log(p_audit_log, 'Ranking For Grade Id: ' || rec.pl_id);
490 write_log(p_audit_log, 'Number of employees to be ranked on the grade: ' || rec.emp_cnt);
491 -- Reset variables for each grade before starting
492 l_rank := 0;
493 l_prev_score := 0;
494 For rec_rnk In csr_rnk_pl_no_ba(p_pgm_id, rec.pl_id,
495 l_dt_start, l_dt_end ) Loop
496 --
497 if ( l_prev_score <> rec_rnk.total_score) then
498 l_rank := l_rank + 1;
499 l_prev_score := rec_rnk.total_score;
500 end if;
501 --
502 create_update_rank_approvals(rec_rnk.rank_process_id
503 , l_rank
504 , rec.emp_cnt );
505 --
506 End Loop;
507 --
508 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
509 --
510 End Loop;
511 else -- Rank specified grades
512 write_log(p_audit_log, 'Ranking For Grade Id: ' || p_pl_id);
513 open csr_gr_no_ba(p_pgm_id, p_pl_id, l_dt_start, l_dt_end );
514 fetch csr_gr_no_ba into l_dummy, l_cnt;
515 close csr_gr_no_ba;
516 write_log(p_audit_log, 'Number of employees to be ranked on the grade: ' || l_cnt);
517 -- Reset variables for each grade before starting
518 l_rank := 0;
519 l_prev_score := 0;
520 For rec_rnk In csr_rnk_pl_no_ba(p_pgm_id, p_pl_id,
521 l_dt_start, l_dt_end ) Loop
522 --
523 if ( l_prev_score <> rec_rnk.total_score) then
524 l_rank := l_rank + 1;
525 l_prev_score := rec_rnk.total_score;
526 end if;
527 --
528 create_update_rank_approvals(rec_rnk.rank_process_id
529 , l_rank
530 , l_cnt );
531 --
532 End Loop;
533 --
534 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
535 --
536 end if;
537 END IF;
538 /**
539 * CASE 3: Grade Ladder Not specified
540 * Fetch All grade ladders first and then
541 * do the ranking
542 */
543 ELSIF p_pgm_id is null then
544 --
545 For rec_pgm in csr_pgm_no_ba (l_dt_start, l_dt_end) Loop
546 write_log(p_audit_log, '-------------------------------------------------------');
547 write_log(p_audit_log, 'Ranking For Grade Ladder Id: ' || rec_pgm.pgm_id);
548 -- Get Grade ladder setup options
549 get_pgi_info (
550 p_pgm_id => rec_pgm.pgm_id,
551 p_workflow_enabled => l_wf_enabled,
552 p_rank_enabled => l_rank_enabled,
553 p_handle_duplicate => l_handle_duplicate,
554 p_group_score => l_group_score,
555 p_result => l_result );
556
557 -- If ranking is not enabled for the grade ladder
558 -- then skip to the next grade ladder.
559 IF ( l_rank_enabled <> 'Y') Then
560 write_log(p_audit_log, 'Ranking NOT enabled, skipping to next Grade Ladder' );
561 GOTO NEXT_RECORD_SET;
562 END IF;
563 --
564 IF (l_group_score = 'GL') then
565 write_log(p_audit_log, 'Ranking by Grade ladder');
566 l_cnt := get_emp_count_on_pgm (null, rec_pgm.pgm_id);
567 write_log(p_audit_log, 'Number of employees to be ranked on the grade ladder:' || l_cnt);
568 -- Reset variables before starting
569 l_rank := 0;
570 l_prev_score := 0;
571 --
572 For rec_rnk In csr_rnk_pgm_no_ba(rec_pgm.pgm_id , l_dt_start, l_dt_end ) Loop
573 --
574 if ( l_prev_score <> rec_rnk.total_score) then
575 l_rank := l_rank + 1;
576 l_prev_score := rec_rnk.total_score;
577 end if;
578 --
579 create_update_rank_approvals(rec_rnk.rank_process_id
580 , l_rank
581 , l_cnt );
582 --
583 End Loop;
584 --
585 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
586 --
587 ELSIF l_group_score = 'GRADE' then
588 --
589 write_log(p_audit_log, 'Ranking by Grade');
590 --
591 if (p_pl_id is null) then -- Rank all grades
592 for rec in csr_gr_no_ba(rec_pgm.pgm_id,null, l_dt_start, l_dt_end ) Loop
593 --
594 write_log(p_audit_log, 'Ranking For Grade Id: ' || rec.pl_id);
595 write_log(p_audit_log, 'Number of employees to be ranked on the grade: ' || rec.emp_cnt);
596 -- Reset variables for each grade before starting
597 l_rank := 0;
598 l_prev_score := 0;
599 For rec_rnk In csr_rnk_pl_no_ba(rec_pgm.pgm_id, rec.pl_id,
600 l_dt_start, l_dt_end ) Loop
601 --
602 if ( l_prev_score <> rec_rnk.total_score) then
603 l_rank := l_rank + 1;
604 l_prev_score := rec_rnk.total_score;
605 end if;
606 --
607 create_update_rank_approvals(rec_rnk.rank_process_id
608 , l_rank
609 , rec.emp_cnt );
610 --
611 End Loop;
612 --
613 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
614 --
615 End Loop;
616 else -- Rank specified grades
617 open csr_gr_no_ba(rec_pgm.pgm_id, p_pl_id, l_dt_start, l_dt_end );
618 fetch csr_gr_no_ba into l_dummy, l_cnt;
619 close csr_gr_no_ba;
620 write_log(p_audit_log, 'Ranking For Grade Id: ' || p_pl_id);
621 write_log(p_audit_log, 'Number of employees to be ranked on the grade: ' || l_cnt);
622 -- Reset variables for each grade before starting
623 l_rank := 0;
624 l_prev_score := 0;
625 For rec_rnk In csr_rnk_pl_no_ba(rec_pgm.pgm_id, p_pl_id,
626 l_dt_start, l_dt_end ) Loop
627 --
628 if ( l_prev_score <> rec_rnk.total_score) then
629 l_rank := l_rank + 1;
630 l_prev_score := rec_rnk.total_score;
631 end if;
632 --
633 create_update_rank_approvals(rec_rnk.rank_process_id
634 , l_rank
635 , l_cnt );
636 --
637 End Loop;
638 --
639 write_log(p_audit_log, 'Unique ranks assigned: ' || l_rank);
640 --
641 end if;
642 END IF;
643
644 <<NEXT_RECORD_SET>>
645 null;
646 End Loop;
647 --
648 END IF; -- Benefit action ID
649 --
650 write_log(p_audit_log, '-------------------------------------------------------');
651 --
652 -- This flag is changed to Validate, please read the opposite.
653 -- So Validate = Y means do not commit likewise Validate=N
654 -- means commit (not validating)
655 if (p_commit_data = 'N') then
656 --
657 write_log(p_audit_log, 'Commiting the Changes');
658 commit;
659 --
660 else
661 --
662 write_log(p_audit_log, 'Rolling back the Changes');
663 rollback;
664 --
665 end if;
666 --
667 write_log(p_audit_log, 'RANKING PROCESS COMPLETE');
668 hr_utility.set_location(' Leaving:'||l_proc, 10);
669 --
670 Exception
671 WHEN Others THEN
672 raise;
673 END;
674 --
675 -- ---------------------------------------------------------------------------
676 -- ------------- <Compute_Score_For_GSP> -------------------------------------
677 -- ---------------------------------------------------------------------------
678 PROCEDURE compute_score_for_GSP (
679 p_benefit_action_id in number
680 ,p_per_in_ler_id in number
681 ,p_person_id in number
682 ,p_effective_date in date
683 ,p_pgm_id in number
684 ,p_handle_duplicate in varchar2
685 ,p_group_score in varchar2 ) IS
686
687 --
688 l_proc constant varchar2(72):= g_package||'compute_score_for_gsp';
689 --
690 cursor per_asg_cur is
691 select assignment_id
692 from per_all_assignments_f
693 where person_id = p_person_id
694 And primary_flag = 'Y'
695 and p_effective_date between effective_start_date and effective_end_date;
696
697 l_assignment_id per_all_assignments_f.assignment_id%type;
698
699 BEGIN
700
701 hr_utility.set_location('Entering:'||l_proc, 5);
702 hr_utility.set_location('Grouping by: '||p_group_score,5);
703 hr_utility.set_location('Duplicate Handling: '||p_handle_duplicate,5);
704 /*
705 write_log('Y','$$$$$$$$$$$$$$$$ Begin Computing Total Score $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
706 write_log('Y','Grouping by: '||p_group_score);
707 write_log('Y','Duplicate Handling: '||p_handle_duplicate);
708 */
709 --
710 IF ( p_group_score = 'GRADE') THEN
711 --
712 hr_utility.set_location('Grouping by Grade',5);
713 IF ( p_handle_duplicate = 'SUM') THEN
714 --
715 --
716 INSERT INTO pqh_rank_processes (
717 rank_process_id, process_cd, pgm_id, pl_id,
718 benefit_action_id, process_date, person_id,
719 per_in_ler_id, total_score, object_version_number)
720 Select pqh_rank_processes_s.nextval, 'GSP', p_pgm_id, pl_id,
721 p_benefit_action_id, lf_evt_ocrd_dt,
722 person_id, per_in_ler_id, total_score, 1
723 FROM (
724 select ec.pl_id, ec.per_in_ler_id,
725 ep.person_id, pil.lf_evt_ocrd_dt,
726 sum(sr.computed_score) total_score
727 from ben_elig_per_elctbl_chc ec,
728 ben_elig_scre_wtg_f sr,
729 ben_elig_per_f ep,
730 ben_per_in_ler pil
731 where ec. elig_flag = 'Y'
732 and ec.dflt_flag = 'Y'
733 and ec.pgm_id = p_pgm_id
734 and ec.per_in_ler_id = p_per_in_ler_id
735 --
736 and ep.per_in_ler_id = ec.per_in_ler_id
737 and ep.pgm_id = ec.pgm_id
738 and (ep.pl_id is null or ep.pl_id = ec.pl_id )
739 and (ep.plip_id is null or ep.plip_id = ec.plip_id )
740 --
741 and sr.elig_per_id = ep.elig_per_id
742 --
743 and pil.per_in_ler_id = ec.per_in_ler_id
744 and pil.per_in_ler_stat_cd = 'STRTD'
745 group by ec.pl_id, ec.per_in_ler_id,ep.person_id,
746 pil.lf_evt_ocrd_dt
747 );
748 --
749 ELSE
750 ---
751 INSERT INTO pqh_rank_processes (
752 rank_process_id, process_cd, pgm_id,pl_id,
753 benefit_action_id,process_date, person_id,
754 per_in_ler_id, total_score, object_version_number)
755 Select pqh_rank_processes_s.nextval, 'GSP', p_pgm_id, pl_id,
756 p_benefit_action_id, lf_evt_ocrd_dt ,
757 person_id, per_in_ler_id, total_score,1
758 FROM ( select pl_id, per_in_ler_id, person_id,
759 lf_evt_ocrd_dt, sum(GROUP_score) total_score
760 from (
761 select ec.pl_id, ec.per_in_ler_id, ep.person_id,
762 pil.lf_evt_ocrd_dt, crit_tab_short_name,
763 decode(p_handle_duplicate,'MAX', MAX(sr.computed_score)
764 ,'MIN', MIN(sr.computed_score)
765 ,'AVG', AVG(sr.computed_score) ) GROUP_score
766 from ben_elig_per_elctbl_chc ec,
767 ben_elig_scre_wtg_f sr,
768 ben_elig_per_f ep,
769 ben_per_in_ler pil
770 where ec. elig_flag = 'Y'
771 and ec.dflt_flag = 'Y'
772 and ec.pgm_id = p_pgm_id
773 and ec.per_in_ler_id = p_per_in_ler_id
774 --
775 and ep.per_in_ler_id = ec.per_in_ler_id
776 and ep.pgm_id = ec.pgm_id
777 and (ep.pl_id is null or ep.pl_id = ec.pl_id )
778 and (ep.plip_id is null or ep.plip_id = ec.plip_id )
779 --
780 and sr.elig_per_id = ep.elig_per_id
781 --
782 and pil.per_in_ler_id = ec.per_in_ler_id
783 and pil.per_in_ler_stat_cd = 'STRTD'
784 group by ec.pl_id, ec.per_in_ler_id, ep.person_id ,
785 pil.lf_evt_ocrd_dt, crit_tab_short_name
786 ) GRP
787 GROUP BY pl_id, per_in_ler_id, person_id , lf_evt_ocrd_dt
788 );
789 --
790 --
791 END IF;
792 --
793 ELSIF (p_group_score = 'GL' ) THEN
794 --
795 IF ( p_handle_duplicate = 'SUM') THEN
796 --
797 INSERT INTO pqh_rank_processes (
798 rank_process_id, process_cd, pgm_id,
799 benefit_action_id, process_date, person_id,
800 per_in_ler_id, total_score,object_version_number)
801 Select pqh_rank_processes_s.nextval, 'GSP', pgm_id, p_benefit_action_id,
802 lf_evt_ocrd_dt, person_id, per_in_ler_id, total_score,1
803 FROM (
804 select ec.pgm_id, ec.per_in_ler_id, ep.person_id,
805 pil.lf_evt_ocrd_dt, sum(sr.computed_score) total_score
806 from ben_elig_per_elctbl_chc ec,
807 ben_elig_scre_wtg_f sr,
808 ben_elig_per_f ep,
809 ben_per_in_ler pil
810 where ec. elig_flag = 'Y'
811 and ec.dflt_flag = 'Y'
812 and ec.pgm_id = p_pgm_id
813 --
814 and ec.per_in_ler_id = p_per_in_ler_id
815 --
816 and ep.per_in_ler_id = ec.per_in_ler_id
817 and ep.pgm_id = ec.pgm_id
818 -- NS: 05/12/2005: Don't need this join, for it to work when elpro is attached
819 -- to the program
820 --and ep.pl_id = ec.pl_id
821 --and ep.plip_id = ec.plip_id
822 --
823 and sr.elig_per_id = ep.elig_per_id
824 --
825 and pil.per_in_ler_id = ec.per_in_ler_id
826 and pil.per_in_ler_stat_cd = 'STRTD'
827 group by ec.pgm_id, ec.per_in_ler_id, ep.person_id,
828 pil.lf_evt_ocrd_dt
829 ) ;
830 --
831 ELSE
832 ---
833 INSERT INTO pqh_rank_processes (
834 rank_process_id, process_cd, pgm_id,
835 benefit_action_id, process_date, person_id,
836 per_in_ler_id, total_score, object_version_number)
837 Select pqh_rank_processes_s.nextval, 'GSP', pgm_id,p_benefit_action_id,
838 lf_evt_ocrd_dt, person_id, per_in_ler_id, total_score,1
839 FROM ( select pgm_id, per_in_ler_id, person_id, lf_evt_ocrd_dt,
840 sum(GROUP_score) total_score
841 from (
842 select ec.pgm_id, ec.per_in_ler_id, ep.person_id,
843 pil.lf_evt_ocrd_dt, crit_tab_short_name,
844 decode(p_handle_duplicate,'MAX', MAX(sr.computed_score)
845 ,'MIN', MIN(sr.computed_score)
846 ,'AVG', AVG(sr.computed_score) ) GROUP_score
847 from ben_elig_per_elctbl_chc ec,
848 ben_elig_scre_wtg_f sr,
849 ben_elig_per_f ep,
850 ben_per_in_ler pil
851 where ec. elig_flag = 'Y'
852 and ec.dflt_flag = 'Y'
853 and ec.pgm_id = p_pgm_id
854 and ec.per_in_ler_id = p_per_in_ler_id
855 --
856 and ep.per_in_ler_id = ec.per_in_ler_id
857 and ep.pgm_id = ec.pgm_id
858 --and ep.pl_id = ec.pl_id
859 -- and ep.plip_id = ec.plip_id
860 --
861 and sr.elig_per_id = ep.elig_per_id
862 --
863 and pil.per_in_ler_id = ec.per_in_ler_id
864 and pil.per_in_ler_stat_cd = 'STRTD'
865 group by ec.pgm_id, ec.per_in_ler_id, ep.person_id ,
866 pil.lf_evt_ocrd_dt, crit_tab_short_name
867 ) GRP
868 GROUP BY pgm_id, per_in_ler_id, person_id, lf_evt_ocrd_dt
869 ) ;
870 --
871 END IF; -- duplicate handling
872 --
873 END IF; -- group score
874 --
875 for i in per_asg_cur loop
876
877 update pqh_rank_processes
878 set assignment_id = i.assignment_id
879 where person_id = p_person_id
880 and benefit_action_id = p_benefit_action_id
881 and pgm_id = p_pgm_id;
882
883 end loop;
884
885 hr_utility.set_location(' Leaving:'||l_proc, 10);
886 /*
887 write_log('Y','LEAVING Compute Total Score ');
888 write_log('Y','$$$$$$$$$$$$$$$$ End Computing Total Score $$$$$$$$$$$$$$$$$$$');
889 */
890 Exception
891 WHEN Others THEN
892 hr_utility.set_location(' Error in '||l_proc||' : '||sqlerrm, 10);
893 write_log('Y','Error in '||l_proc||': '||sqlerrm);
894 /*
895 write_log('Y','$$$$$$$$$$$$$$$$ End Computing Total Score $$$$$$$$$$$$$$$$$$$');
896 */
897 raise;
898 END compute_score_for_GSP;
899
900 -- -----------------------------------------------------------------------
901 -- Compute Score For Compensation workbench
902 --
903 --
904 -- -----------------------------------------------------------------------
905
906 -- ---------------------------------------------------------------------------
907 -- ------------- <Compute_Score_for_CWB> -------------------------------------
908 -- ---------------------------------------------------------------------------
909 PROCEDURE compute_score_for_CWB (
910 p_benefit_action_id IN NUMBER) IS
911
912 --
913 --
914 l_proc constant varchar2(72):= g_package||'compute_score_for_cwb';
915 BEGIN
916
917 hr_utility.set_location('Entering:'||l_proc, 5);
918
919 --
920 --
921 /** NS: 04/26/2005: Commenting as CWB Ranking is not going in FP.K
922 Will uncomment it when CWB is ready to ship system ranking
923 Insert into pqh_rank_processes (
924 Rank_process_id, process_cd, pl_id, person_id, assignment_id,
925 Process_date, total_score)
926 SELECT pqh_rank_processes_s.nextval,'CWB', group_pl_id, person_id, assignment_id,
927 lf_evt_ocrd_dt, total_score
928 FROM (
929 Select pr.group_pl_id, pr.person_id, pr.assignment_id,
930 pr.lf_evt_ocrd_dt, sum(sr.score) total_score
931 From ben_cwb_person_rates pr,
932 ben_benefit_actions ba,
933 ben_elig_per_opt_f epo,
934 ben_elig_per_f ep,
935 ben_elig_scre_wtg_f sr
936 Where ba.benefit_action_id = p_benefit_action_id
937 And pr.group_pl_id = ba.pl_id
938 And pr.pl_id = pr.group_pl_id
939 --
940 And ep.per_in_ler_id = pr.group_per_in_ler_id
941 And ep.request_id = ba.request_id
942 And ep.pl_id = pr.group_pl_id
943 And ep.person_id = pr.person_id
944 And pr.lf_evt_ocrd_dt between ep.effective_start_date and ep.effective_end_date
945 --
946 And epo.elig_per_id = ep.elig_per_id
947 And epo.request_id = ba.request_id
948 And pr.lf_evt_ocrd_dt between epo.effective_start_date and epo.effective_end_date
949 --
950 And sr.benefit_action_id = ba.benefit_action_id
951 And sr.elig_per_opt_id = epo.elig_per_opt_id
952 And pr.lf_evt_ocrd_dt between sr.effective_start_date and sr.effective_end_date
953 --
954 Group by pr.group_pl_id, pr.lf_evt_ocrd_dt,pr.person_id, pr.assignment_id);
955 --
956 hr_utility.set_location(' Leaving:'||l_proc, 10);
957 Exception
958 WHEN Others THEN
959 raise;
960 **/
961 END compute_score_for_CWB;
962 --
963
964 -- ---------------------------------------------------------------------------
965 -- ------------- <Compute_Total_Score> ---------------------------------------
966 -- ---------------------------------------------------------------------------
967 PROCEDURE compute_total_score (
968 p_benefit_action_id IN NUMBER,
969 p_module IN VARCHAR2,
970 p_per_in_ler_id IN NUMBER,
971 p_person_id IN NUMBER,
972 p_effective_date IN DATE ) IS
973
974 l_proc constant varchar2(72):= g_package||'compute_total_score';
975 BEGIN
976
977 hr_utility.set_location('Entering:'||l_proc, 5);
978 --
979 if (p_module = 'GSP') then
980 --
981 Declare
982 CURSOR csr_pgm IS
983 SELECT pgm_id
984 FROM ben_elig_per_elctbl_chc
985 WHERE per_in_ler_id = p_per_in_ler_id
986 AND dflt_flag = 'Y'
987 AND rownum < 2;
988 --
989 l_rank_enabled varchar2(30);
990 l_wf_enabled varchar2(10);
991 l_handle_duplicate varchar2(30);
992 l_group_score varchar2(30);
993 l_result varchar2(30);
994 l_pgm_id number;
995 --
996 Begin
997 OPEN csr_pgm;
998 FETCH csr_pgm INTO l_pgm_id;
999 CLOSE csr_pgm;
1000
1001 if ( l_pgm_id is null) then
1002 return;
1003 end if;
1004
1005 -- Get Grade ladder setup options
1006 get_pgi_info (
1007 p_pgm_id => l_pgm_id,
1008 p_workflow_enabled => l_wf_enabled,
1009 p_rank_enabled => l_rank_enabled,
1010 p_handle_duplicate => l_handle_duplicate,
1011 p_group_score => l_group_score,
1012 p_result => l_result );
1013 --
1014 -- Error Handling
1015 --handle_error(l_result);
1016 --SR: 4626343.992, if grade created manually, or old grades, won't
1017 -- have extra information record, return without performaing any
1018 -- action in such cases.
1019 --
1020 -- No computing required if ranking is not enabled.
1021 IF ( l_result = 'PGI_SETUP_NOT_FOUND' OR l_rank_enabled <> 'Y') THEN
1022 Return;
1023 END IF;
1024 --
1025 compute_score_for_GSP(
1026 p_benefit_action_id => p_benefit_action_id
1027 ,p_per_in_ler_id => p_per_in_ler_id
1028 ,p_person_id => p_person_id
1029 ,p_effective_date => p_effective_date
1030 ,p_pgm_id => l_pgm_id
1031 ,p_handle_duplicate => l_handle_duplicate
1032 ,p_group_score => l_group_score
1033 );
1034 --
1035 End;
1036
1037 end if;
1038 --
1039 hr_utility.set_location(' Leaving:'||l_proc, 10);
1040 Exception
1041 WHEN Others THEN
1042 raise;
1043 END compute_total_score;
1044
1045 --
1046 Procedure assign_ranks_for_CWB (
1047 p_benefit_action_id IN NUMBER) IS
1048 --
1049 CURSOR csr_rnk IS
1050 SELECT mgr_per_in_ler_id, pi.person_id supervisor_id
1051 FROM ben_cwb_group_hrchy gh,
1052 ben_cwb_person_info pi
1053 WHERE gh.lvl_num = 0
1054 AND gh.mgr_per_in_ler_id = pi.group_per_in_ler_id;
1055 --
1056 l_proc constant varchar2(72):= g_package||'assign_ranks_for_cwb';
1057 BEGIN
1058
1059 hr_utility.set_location('Entering:'||l_proc, 5);
1060 --
1061 FOR rec_rnk in csr_rnk
1062 LOOP
1063 --
1064 INSERT into pqh_rank_process_approvals (
1065 rank_process_approval_id, rank_process_id, supervisor_id,system_rank,
1066 proposed_rank )
1067 SELECT pqh_rank_process_approvals_s.NEXTVAL, rank_process_id, supervisor_id, system_rank,
1068 system_rank
1069 FROM (
1070 select rp.rank_process_id, rec_rnk.supervisor_id, rownum system_rank
1071 from pqh_rank_processes rp,
1072 ben_cwb_group_hrchy gh,
1073 ben_cwb_person_rates pr
1074 where gh.mgr_per_in_ler_id = rec_rnk.mgr_per_in_ler_id
1075 and gh.lvl_num > 0 -- don't include self
1076 and pr.group_per_in_ler_id = gh.emp_per_in_ler_id
1077 and rp.benefit_action_id = p_benefit_action_id
1078 and rp.assignment_id = pr.assignment_id
1079 and rp.process_date = pr.lf_evt_ocrd_dt
1080 and rp.pl_id = pr.group_pl_id
1081 order by rp.total_score desc);
1082 --
1083 END LOOP;
1084 --
1085 hr_utility.set_location(' Leaving:'||l_proc, 10);
1086 Exception
1087 WHEN Others THEN
1088 raise;
1089 END assign_ranks_for_CWB;
1090 --
1091 --
1092 -- ---------------------------------------------------------------------------
1093 -- ------------- <get_criteria_name> -----------------------------------------
1094 -- ---------------------------------------------------------------------------
1095 FUNCTION get_criteria_name (p_tab_short_name in varchar2) RETURN VARCHAR2 IS
1096 --
1097 CURSOR csr_crit_nm IS
1098 select trtl.display_name
1099 from pqh_table_route_tl trtl,
1100 pqh_table_route tr
1101 where trtl.table_route_id = tr.table_route_id
1102 and from_clause = 'OAB'
1103 and language = userenv('lang')
1104 and tr.table_alias = p_tab_short_name;
1105 --
1106 l_crit_name pqh_table_route_tl.display_name%Type;
1107 --
1108
1109 l_proc constant varchar2(72):= g_package||'get_criteria_name';
1110 BEGIN
1111
1112 hr_utility.set_location('Entering:'||l_proc, 5);
1113 --
1114 OPEN csr_crit_nm;
1115 FETCH csr_crit_nm INTO l_crit_name;
1116 CLOSE csr_crit_nm;
1117 --
1118 RETURN l_crit_name;
1119 --
1120 --
1121 hr_utility.set_location(' Leaving:'||l_proc, 10);
1122 Exception
1123 WHEN Others THEN
1124 raise;
1125 END get_criteria_name;
1126
1127
1128 END; -- Package Body PQH_RANKING