[Home] [Help]
PACKAGE BODY: APPS.GHR_MASS_AWARDS_ELIG
Source
1 Package Body ghr_mass_awards_elig as
2 /* $Header: ghmawelg.pkb 120.1.12010000.3 2008/10/15 10:35:17 utokachi ship $ */
3
4 Procedure get_eligible_employees
5 (p_mass_award_id in number ,
6 p_action_type in varchar2, -- PREVIEW, FINAL
7 p_errbuf out nocopy varchar2,
8 p_retcode out nocopy varchar2,
9 p_status in out nocopy varchar2,
10 p_maxcheck out nocopy number
11 )
12
13 --Note : NAF Employees not to be considered for mass awards just like other areas like reports etc.
14
15 is
16
17 l_cursor_id number;
18 l_cursor_kff_id number;
19 l_cursor_pos_grp1_id number;
20 l_cursor_pos_grp2_id number;
21 l_cursor_pos_grd_id number;
22 l_cursor_loc_ddf_id number;
23 l_cursor_rating_id number;
24 l_business_group_id number;
25 l_per_id number;
26 l_asg_id number;
27 l_pos_id number;
28 l_job_id number;
29 l_loc_id number;
30 l_grd_id number;
31 l_sit ghr_api.special_information_type;
32 l_asg_select long;
33 l_numrows number;
34 l_numrows_kff number;
35 l_numrows_pos_grp1 number;
36 l_numrows_pos_grp2 number;
37 l_numrows_pos_grd number;
38 l_numrows_loc_ddf number;
39 l_numrows_rating number;
40 l_pos_exists boolean;
41 l_job_exists boolean;
42 l_lei_exists boolean;
43 l_rating_exists boolean;
44 l_kff_select long;
45 l_rating_select long;
46 l_pos_grp1_select long;
47 l_pos_grp2_select long;
48 l_pos_grd_select long;
49 l_loc_ddf_select long;
50 l_poi_exists boolean;
51 l_poc_exists boolean;
52 l_ofs_exists boolean;
53 l_ors_exists boolean;
54 l_pay_plan_exists boolean;
55 l_grade_exists boolean;
56 l_count number := 0;
57 l_pos_ei_data per_position_extra_info%rowtype;
58 l_effective_date date;
59 l_duty_station_code ghr_duty_stations_f.duty_station_code%type;
60 l_errbuf varchar2(2000);
61 l_retcode number;
62 l_status varchar2(30);
63 l_nc_status varchar2(30); --Added for nocopy changes.
64 l_old_retcode number;
65 l_succ_ctr number := 0;
66 l_err_man_ctr number := 0;
67 l_err_mass_ctr number := 0;
68 l_desel_ctr number := 0;
69 l_pos_grade_ei_id number;
70
71 l_new_line varchar2(1) := substr('
72 ',1,1);
73
74 Cursor c_eff_date is
75 Select effective_date
76 from ghr_mass_awards
77 where mass_award_id = p_mass_award_id;
78
79 Cursor c_grade_kff(c_grade_id number) is
80 select gdf.segment1 pay_plan,
81 gdf.segment2 grade_or_level
82 from per_grades grd,
83 per_grade_definitions gdf
84 where grd.grade_id = c_grade_id
85 and grd.grade_definition_id = gdf.grade_definition_id;
86
87
88 Cursor c_business_group_id is
89 Select ppf.business_group_id
90 from per_people_f ppf
91 where ppf.person_id = l_per_id
92 and l_effective_date
93 between ppf.effective_start_date
94 and ppf.effective_end_date;
95
96
97 Cursor c_duty_station_code is
98 Select dsf.duty_station_code
99 from ghr_duty_stations_f dsf
100 where dsf.duty_station_id =
101 (select lei.lei_information3
102 from hr_location_extra_info lei
103 where lei.location_id = l_loc_id
104 )
105 and l_effective_date
106 between dsf.effective_start_date
107 and dsf.effective_end_date;
108
109 --Bug#2459352
110 cursor c_fed_employee(c_position_id IN NUMBER) is
111 select pos.position_extra_info_id
112 from per_position_extra_info pos
113 where pos.position_id = c_position_id
114 and pos.information_type = 'GHR_US_POS_VALID_GRADE';
115
116
117
118 Procedure build_asg_sel
119 (p_mass_award_id in number,
120 p_asg_select out NOCOPY long) is
121
122
123 Cursor c_eff_date is
124 Select n.code
125 from ghr_mass_awards m,
126 ghr_nature_of_actions n
127 where mass_award_id = p_mass_award_id
128 and n.nature_of_action_id = m.nature_of_action_id;
129
130 Cursor c_asg_values is
131 select val.relational_operator,
132 val.value
133 from ghr_mass_award_criteria_cols col,
134 ghr_mass_award_criteria_vals val
135 Where col.table_name = 'ASSIGNMENT'
136 and col.column_name = 'Organization'
137 and val.mass_award_id = p_mass_award_id
138 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
139 order by val.relational_operator;
140
141 l_select long;
142 l_pre varchar2(50);
143 l_suf varchar2(50);
144 l_operator varchar2(50);
145 l_old_operator varchar2(50);
146 l_new_operator varchar2(50);
147 l_asg_exists boolean := FALSE;
148 l_noa_code NUMBER;
149
150
151 begin
152 l_select := ' Select asg1.person_id,asg1.assignment_id,asg1.position_id , asg1.location_id,' ||
153 ' asg1.job_id , asg1.grade_id , org.name ' ||
154 ' from per_assignments_f asg1, ' ||
155 ' hr_organization_units org ' ||
156 ' where ' || 'to_date(' || '''' || l_effective_date || '''' ||
157 ',' || '''' || 'DD-MON-YY' || ''''|| ')' ||
158 ' between asg1.effective_start_date and asg1.effective_end_date ' ||
159 ' and asg1.organization_id = org.organization_id ' ||
160 ' and asg1.assignment_type = ' ||
161 '''' || 'E' || '''' ||
162 ' and asg1.position_id is not null ' ;
163 l_old_operator := null;
164 for asg_values in c_asg_values loop
165 l_asg_exists := TRUE;
166 l_new_operator := asg_values.relational_operator;
167 ghr_mass_awards_elig.derive_rel_operator
168 (p_in_rel_operator => asg_values.relational_operator,
169 p_out_rel_operator => l_operator,
170 p_prefix => l_pre,
171 p_suffix => l_suf
172 );
173
174 If l_new_operator = 'NOT EQUALS'
175 or nvl(l_old_operator,'NOT EQUALS') = 'NOT EQUALS' then
176 If nvl(l_old_operator,'NOT EQUALS') = 'NOT EQUALS' and
177 l_new_operator <> 'NOT EQUALS' then
178 l_select := l_select || ' and (' ;
179 Else
180 l_select := l_select || ' and ';
181 End if;
182 Else
183 l_select := l_select || ' or ' ;
184 End if;
185
186
187 l_select := l_select ||
188 'upper(org.name)' || ' ' || l_operator || ' ' ||
189 'upper(' || l_pre || asg_values.value || l_suf ||
190 ')' ;
191 l_old_operator := l_new_operator;
192
193 end loop;
194 If l_asg_exists then
195 l_Select := l_select || ') or 1 = 0 ' ;
196 --l_Select := l_select || ' or 1 = 0 ' ;
197 Else
198 l_select := l_select || ' and 1 = 1 ';
199 End if;
200
201 for i in c_eff_date loop
202 l_noa_code := i.code;
203 end loop;
204
205 --bug 5482191
206 if l_noa_code in ('885','886','887') then
207
208 l_select := l_select ||' and ghr_pa_requests_pkg.get_personnel_system_indicator ('||
209 ' asg1.position_id,'||
210 'to_date(' || '''' || l_effective_date || '''' ||
211 ',' || '''' || 'DD-MON-YY' || ''''|| '))'||
212 '<>'||''''||'00'||'''';
213
214
215 elsif l_noa_code in ('878','879') then
216
217 l_select := l_select ||' and ghr_pa_requests_pkg.get_personnel_system_indicator ('||
218 ' asg1.position_id,'||
219 'to_date(' || '''' || l_effective_date || '''' ||
220 ',' || '''' || 'DD-MON-YY' || ''''|| '))'||
221 '='||''''||'00'||'''';
222
223
224
225 end if;
226
227
228
229
230 p_asg_select := l_select;
231 end build_asg_sel;
232
233 Procedure build_pos_job_kff_sel
234 (p_mass_award_id in number,
235 p_kff_select out nocopy long,
236 p_pos_exists out nocopy boolean,
237 p_job_exists out nocopy boolean
238 ) is
239
240 l_select long;
241 l_pre varchar2(50);
242 l_suf varchar2(50);
243 l_operator varchar2(50);
244 l_old_operator varchar2(50);
245 l_table_name varchar2(30);
246 l_pos_kff_exists boolean := FALSE;
247 l_pos_exists boolean := FALSE;
248 l_job_exists boolean := FALSE;
249 l_curr_name varchar2(50);
250 l_old_name varchar2(50);
251 l_col_name varchar2(150);
252
253
254 cursor c_pos_kff_values is
255 select val.relational_operator,
256 val.value,
257 col.table_name,
258 col.column_name
259 from ghr_mass_award_criteria_cols col,
260 ghr_mass_award_criteria_vals val
261 Where val.mass_award_id = p_mass_award_id
262 and col.table_name = 'POSITION_KFF'
263 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
264 union all
265 Select val.relational_operator,
266 val.value,
267 col.table_name,
268 col.column_name
269 from ghr_mass_award_criteria_cols col,
270 ghr_mass_award_criteria_vals val
271 Where val.mass_award_id = p_mass_award_id
272 and col.table_name = 'JOB_KFF'
273 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
274 order by 3,1 ;
275
276
277
278 Begin
279 l_select := 'Select 1 from dual where ( 1 = 1 ' ;
280
281 for pos_kff_values in c_pos_kff_values loop
282 -- l_pos_kff_exists := TRUE;
283
284 ghr_mass_awards_elig.derive_rel_operator
285 (p_in_rel_operator => pos_kff_values.relational_operator,
286 p_out_rel_operator => l_operator,
287 p_prefix => l_pre,
288 p_suffix => l_suf
289 );
290
291 l_curr_name := pos_kff_values.column_name;
292 l_table_name := pos_kff_values.table_name;
293 -- l_pos_kff_exists := true;
294
295
296 If
297 (nvl(l_curr_name,hr_api.g_varchar2) <> nvl(l_old_name,hr_api.g_varchar2))
298 or
299 l_operator = ' <> ' or l_old_operator is null or l_old_operator = ' <> ' then
300 l_old_name := l_curr_name;
301 If l_curr_name = 'Position Title' then
302 l_pos_exists := true;
303 l_col_name := 'ghr_api.get_position_title_pos';
304
305 Elsif l_curr_name = 'Agency/Subelement Code' then
306 l_pos_exists := true;
307 l_col_name := 'ghr_api.get_position_agency_code_pos';
308
309 /*Elsif l_curr_name = 'Agency Code' then
310 l_pos_exists := true;
311 l_col_name := 'substr(ghr_api.get_position_agency_code_pos,1,2)';
312 */
313 Elsif l_curr_name = 'Occupational Series' then
314 l_job_exists := true;
315 l_col_name := 'ghr_api.get_job_occ_series_job';
316 End if;
317 If l_table_name = 'POSITION_KFF' then
318 l_pos_exists := true;
319 If l_curr_name = 'Agency Code' then
320 l_pos_exists := true;
321 l_select := l_select || ' ) ' || ' and ( '
322 || 'substr(ghr_api.get_position_agency_code_pos('
323 || ':pos_id'
324 || ',' || ':business_group_id'
325 || ',' || ':effective_date'
326 || '),1,2 )'
327 || l_operator || ' ' || l_pre || pos_kff_values.value || l_suf;
328 Else
329
330 l_select := l_select || ' ) ' || ' and ( '
331 || l_col_name || '(:pos_id' || ', ' || ':business_group_id' || ',' || ':effective_date' || ' ) ' || ' '
332 || l_operator || ' ' || l_pre || pos_kff_values.value || l_suf;
333 End if;
334
335
336 Elsif l_table_name = 'JOB_KFF' then
337 l_job_exists := true;
338 l_select := l_select || ' )' || ' and ( '
339 || l_col_name || '(:job_id' || ', ' || ':business_group_id' || ' ) ' || ' '
340 || l_operator || ' ' || l_pre || pos_kff_values.value || l_suf;
341 End if;
342
343 Else
344 If l_table_name = 'POSITION_KFF' then
345 l_pos_exists := true;
346 If l_curr_name = 'Agency Code' then
347 l_pos_exists := true;
348 l_select := l_select || ' or '
349 || 'substr(ghr_api.get_position_agency_code_pos('
350 || ':pos_id'
351 || ', ' || ':business_group_id'
352 || ', '
353 || ' :effective_date' ||'),1,2 )'
354 || l_operator || ' ' || l_pre || pos_kff_values.value || l_suf ;
355 Else
356 l_select := l_select || ' or '
357 || l_col_name || '(:pos_id' || ', ' || ':business_group_id' || ',' || ':effective_date' || ' ) ' || ' '
358 || l_operator || ' ' || l_pre || pos_kff_values.value || l_suf ;
359 End if;
360
361 Elsif l_table_name = 'JOB_KFF' then
362 l_job_exists := true;
363 l_select := l_select || ' or '
364 || l_col_name || '(:job_id' || ', ' || ':business_group_id' || ' ) ' || ' '
365 || l_operator || ' ' || l_pre || pos_kff_values.value || l_suf ;
366 End if;
367 End if;
368 l_old_operator := l_operator;
369 End loop;
370 l_select := l_select || ' ) ';
371 l_old_name := Null;
372 l_curr_name := Null;
373 p_pos_exists := l_pos_exists;
374 p_job_exists := l_job_exists;
375 p_kff_select := l_select;
376 EXCEPTION
377 when others then
378 -- NOCOPY changes
379 -- Reset IN OUT params and set OUT params
380 p_kff_select := null;
381 p_pos_exists := null;
382 p_job_exists := null;
383 raise;
384 End build_pos_job_kff_sel;
385
386
387 Procedure build_pos_grp2_sel
388 (p_mass_award_id in number,
389 p_pos_grp2_select out nocopy long,
390 p_poc_exists out nocopy boolean
391 ) is
392
393
394 l_select long;
395 l_curr_name varchar2(50);
396 l_old_name varchar2(50);
397 l_pre varchar2(50);
398 l_suf varchar2(50);
399 l_operator varchar2(50);
400 l_old_operator varchar2(50);
401
402
403 Cursor c_pos_grp2_values is
404 Select val.relational_operator,
405 val.value,
406 col.column_name
407 from ghr_mass_award_criteria_cols col,
408 ghr_mass_award_criteria_vals val
409 Where val.mass_award_id = p_mass_award_id
410 and col.table_name = 'POSITION_EXTRA_INFO'
411 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
412 and ( col.column_name = 'Position Occupied'
413 ) order by 3,1;
414
415 Begin
416 l_select := 'select 1 from dual where (1 = 1';
417
418 for pos_grp2_rec in c_pos_grp2_values loop
419
420 ghr_mass_awards_elig.derive_rel_operator
421 (p_in_rel_operator => pos_grp2_rec.relational_operator,
422 p_out_rel_operator => l_operator,
423 p_prefix => l_pre,
424 p_suffix => l_suf
425 );
426
427 l_curr_name := pos_grp2_rec.column_name;
428 If
429 (nvl(l_curr_name,hr_api.g_varchar2) <> nvl(l_old_name,hr_api.g_varchar2))
430 or l_old_operator is null or l_operator = ' <> ' or l_old_operator = ' <> ' then
431 l_old_name := l_curr_name;
432 If l_curr_name = 'Position Occupied' then
433 p_poc_exists := TRUE;
434 l_select := l_select || ' ) and (' ||
435 ':POC' || l_operator || ' ' || l_pre || pos_grp2_rec.value || l_suf ;
436 End if;
437 Else
438 If l_curr_name = 'Position Occupied' then
439 p_poc_exists := TRUE;
440 l_select := l_select || ' or ' ||
441 ':POC' || l_operator || ' ' || l_pre || pos_grp2_rec.value || l_suf ;
442 End if;
443 End if;
444 l_old_operator := l_operator;
445 End loop;
446 l_select := l_select || ' ) ';
447
448 p_pos_grp2_select := l_select;
449 EXCEPTION
450 when others then
451 -- NOCOPY changes
452 -- Reset IN OUT params and set OUT params
453 p_pos_grp2_select := null;
454 p_poc_exists := null;
455 raise;
456 end build_pos_grp2_sel;
457
458 Procedure build_pos_grp1_sel
459 (p_mass_award_id in number,
460 p_pos_grp1_select out nocopy long,
461 p_poi_exists out nocopy boolean,
462 p_ofs_exists out nocopy boolean,
463 p_ors_exists out nocopy boolean
464 ) is
465
466
467 l_select long;
468 l_curr_name varchar2(50);
469 l_old_name varchar2(50);
470 l_pre varchar2(50);
471 l_suf varchar2(50);
472 l_operator varchar2(50);
473 l_old_operator varchar2(50);
474
475
476 Cursor c_pos_grp1_values is
477 Select val.relational_operator,
478 val.value,
479 col.column_name
480 from ghr_mass_award_criteria_cols col,
481 ghr_mass_award_criteria_vals val
482 Where val.mass_award_id = p_mass_award_id
483 and col.table_name = 'POSITION_EXTRA_INFO'
484 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
485 and ( col.column_name = 'Personnel Office ID'
486 or col.column_name = 'Office Symbol'
487 or col.column_name = 'Organization Structure ID'
488 ) order by 3,1;
489
490 Begin
491 l_select := 'select 1 from dual where (1 = 1';
492
493 for pos_grp1_rec in c_pos_grp1_values loop
494
495 ghr_mass_awards_elig.derive_rel_operator
496 (p_in_rel_operator => pos_grp1_rec.relational_operator,
497 p_out_rel_operator => l_operator,
498 p_prefix => l_pre,
499 p_suffix => l_suf
500 );
501 l_curr_name := pos_grp1_rec.column_name;
502 If
503 (nvl(l_curr_name,hr_api.g_varchar2) <> nvl(l_old_name,hr_api.g_varchar2))
504 or nvl(l_old_operator,' <> ' ) = ' <> ' or l_operator = ' <> ' then
505 l_old_name := l_curr_name;
506 If l_curr_name = 'Personnel Office ID' then
507 p_poi_exists := TRUE;
508
509 l_select := l_select || ' ) and (' ||
510 ':POI' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
511 Elsif l_curr_name = 'Office Symbol' then
512 p_ofs_exists := TRUE;
513 l_select := l_select || ' ) and (' ||
514 ':OFS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
515 Elsif l_curr_name = 'Organization Structure ID' then
516 p_ors_exists := TRUE;
517 l_select := l_select || ' ) and (' ||
518 ':ORS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
519 End if;
520 Else
521 If l_curr_name = 'Personnel Office ID' then
522 p_poi_exists := TRUE;
523 l_select := l_select || ' or ' ||
524 ':POI' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
525 Elsif l_curr_name = 'Office Symbol' then
526 p_ofs_exists := TRUE;
527 l_select := l_select || ' or ' ||
528 ':OFS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
529 Elsif l_curr_name = 'Organization Structure ID' then
530 p_ors_exists := TRUE;
531 l_select := l_select || ' or ' ||
532 ':ORS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
533 End if;
534 End if;
535 l_old_operator := l_operator;
536 End loop;
537 l_select := l_select || ' ) ';
538
539 p_pos_grp1_select := l_select;
540
541 EXCEPTION
542 when others then
543 -- NOCOPY changes
544 -- Reset IN OUT params and set OUT params
545
546 p_pos_grp1_select := null;
547 p_poi_exists := null;
548 p_ofs_exists := null;
549 p_ors_exists := null;
550 raise;
551 End build_pos_grp1_sel;
552
553 Procedure build_pos_grd_sel
554 (p_mass_award_id in number,
555 p_pos_grd_select out nocopy long,
556 p_pay_plan_exists out nocopy boolean,
557 p_grade_exists out nocopy boolean
558 ) is
559
560
561 l_select long;
562 l_curr_name varchar2(50);
563 l_old_name varchar2(50);
564 l_pre varchar2(50);
565 l_suf varchar2(50);
566 l_operator varchar2(50);
567 l_old_operator varchar2(50);
568
569
570 Cursor c_pos_grp1_values is
571 Select val.relational_operator,
572 val.value,
573 col.column_name
574 from ghr_mass_award_criteria_cols col,
575 ghr_mass_award_criteria_vals val
576 Where val.mass_award_id = p_mass_award_id
577 and col.table_name = 'POSITION_EXTRA_INFO'
578 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
579 and ( col.column_name = 'Pay Plan'
580 or col.column_name = 'Grade Or Level'
581 ) order by 3,1;
582
583 Begin
584 l_select := 'select 1 from dual where (1 = 1';
585
586 for pos_grp1_rec in c_pos_grp1_values loop
587
588 ghr_mass_awards_elig.derive_rel_operator
589 (p_in_rel_operator => pos_grp1_rec.relational_operator,
590 p_out_rel_operator => l_operator,
591 p_prefix => l_pre,
592 p_suffix => l_suf
593 );
594
595 l_curr_name := pos_grp1_rec.column_name;
596 l_old_operator := Null;
597 If
598 (nvl(l_curr_name,hr_api.g_varchar2) <> nvl(l_old_name,hr_api.g_varchar2))
599 or
600 l_operator = ' <> ' and nvl(l_old_operator,hr_api.g_varchar2) = ' <> ' then
601
602 l_old_name := l_curr_name;
603 If l_curr_name = 'Pay Plan' then
604 p_pay_plan_exists := TRUE;
605 l_select := l_select || ' ) and (' ||
606 ':PP' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
607 Elsif l_curr_name = 'Grade Or Level' then
608 p_grade_exists := TRUE;
609 l_select := l_select || ' ) and (' ||
610 ':GRD' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
611 End if;
612 Else
613 If l_curr_name = 'Pay Plan' then
614 p_pay_plan_exists := TRUE;
615 l_select := l_select || ' or ' ||
616 ':PP' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
617 Elsif l_curr_name = 'Grade Or Level' then
618 p_grade_exists := TRUE;
619 l_select := l_select || ' or ' ||
620 ':GRD' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
621 End if;
622 End if;
623 l_old_operator := l_operator;
624 End loop;
625 l_select := l_select || ' ) ';
626
627 p_pos_grd_select := l_select;
628 EXCEPTION
629 when others then
630 -- NOCOPY changes
631 -- Reset IN OUT params and set OUT params
632 p_pos_grd_select := null;
633 p_pay_plan_exists := null;
634 p_grade_exists := null;
635 raise;
636
637 End build_pos_grd_sel;
638
639 Procedure build_loc_ddf_sel
640 (p_mass_award_id in number,
641 p_loc_ddf_select out nocopy long,
642 p_lei_exists out nocopy boolean
643 )
644 is
645
646 l_select long;
647 l_pre varchar2(50);
648 l_suf varchar2(50);
649 l_operator varchar2(50);
650 l_old_operator varchar2(50);
651 l_new_operator varchar2(50);
652 l_lei_exists boolean;
653 l_exists boolean;
654
655
656
657 Cursor c_loc_ddf_values is
658 Select val.relational_operator,
659 val.value,
660 col.column_name
661 from ghr_mass_award_criteria_cols col,
662 ghr_mass_award_criteria_vals val
663 Where val.mass_award_id = p_mass_award_id
664 and col.table_name = 'LOCATION_EXTRA_INFO'
665 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
666 order by 3,1;
667
668
669 Begin
670 l_select := 'Select 1 from dual where 1 = 1 ' ;
671
672 l_old_operator := null;
673 for loc_ddf_values in c_loc_ddf_values loop
674 l_lei_exists := TRUE;
675 l_new_operator := loc_ddf_values.relational_operator;
676 ghr_mass_awards_elig.derive_rel_operator
677 (p_in_rel_operator => loc_ddf_values.relational_operator,
678 p_out_rel_operator => l_operator,
679 p_prefix => l_pre,
680 p_suffix => l_suf
681 );
682 If nvl(l_old_operator,'NOT EQUALS') = 'NOT EQUALS' or l_operator = 'NOT EQUALS ' then
683
684 l_select := l_select || ' and ' ;
685 Else
686 l_select := l_select || ' or ' ;
687 End if;
688
689
690 l_select := l_select ||
691 'upper( ' || ':DSC' || ')' || ' ' || l_operator || ' ' ||
692 'upper(' || l_pre || loc_ddf_values.value || l_suf ||
693 ')' ;
694 l_old_operator := l_new_operator;
695 end loop;
696
697 If l_lei_exists then
698 l_Select := l_select || ' or 1 = 0 ' ;
699 Else
700 l_select := l_select || ' and 1 = 1 ';
701 End if;
702
703 p_loc_ddf_select := l_select;
704 p_lei_exists := l_lei_exists;
705 EXCEPTION
706 when others then
707 -- NOCOPY changes
708 -- Reset IN OUT params and set OUT params
709 p_loc_ddf_select := null;
710 p_lei_exists := null;
711 raise;
712 end build_loc_ddf_sel;
713
714
715 Procedure build_rating_sel
716 (p_mass_award_id in number,
717 p_rating_select out nocopy long,
718 p_rating_exists out nocopy boolean
719 )
720 is
721
722 l_select long;
723 l_pre varchar2(50);
724 l_suf varchar2(50);
725 l_operator varchar2(50);
726 l_new_operator varchar2(50);
727 l_old_operator varchar2(50);
728 l_rating_exists boolean;
729
730
731 Cursor c_rating_values is
732 Select val.relational_operator,
733 val.value,
734 col.column_name
735 from ghr_mass_award_criteria_cols col,
736 ghr_mass_award_criteria_vals val
737 Where val.mass_award_id = p_mass_award_id
738 and col.table_name = 'PERSON_SIT'
739 and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
740 order by 3,1;
741
742
743
744 Begin
745 l_select := 'Select 1 from dual where 1 = 1 ' ;
746
747 l_old_operator := null;
748 for rating_values in c_rating_values loop
749 l_rating_exists := TRUE;
750 l_new_operator := rating_values.relational_operator;
751 ghr_mass_awards_elig.derive_rel_operator
752 (p_in_rel_operator => rating_values.relational_operator,
753 p_out_rel_operator => l_operator,
754 p_prefix => l_pre,
755 p_suffix => l_suf
756 );
757 If nvl(l_old_operator,'NOT EQUALS') = 'NOT EQUALS' or l_operator = 'NOT EQUALS ' then
758 l_select := l_select || ' and ' ;
759 Else
760 l_select := l_select || ' or ' ;
761 End if;
762
763 l_select := l_select ||
764 'upper( ' || ':RATING' || ')' || ' ' || l_operator || ' ' ||
765 'upper(' || l_pre || rating_values.value || l_suf ||
766 ')' ;
767 l_old_operator := l_operator;
768 end loop;
769
770 If l_rating_exists then
771 l_Select := l_select || ' or 1 = 0 ' ;
772 Else
773 l_select := l_select || ' and 1 = 1 ';
774 End if;
775
776 p_rating_select := l_select;
777 p_rating_exists := l_rating_exists;
778 EXCEPTION
779 when others then
780 -- NOCOPY changes
781 -- Reset IN OUT params and set OUT params
782 p_rating_select := null;
783 p_rating_exists := null;
784 raise;
785 end build_rating_sel;
786
787
788 --Build Performance Rating SIT Select
789
790 Begin
791 l_nc_status := p_status;
792 for eff_date in c_eff_date loop
793 l_effective_date := eff_date.effective_date;
794 end loop;
795
796 build_asg_sel(p_mass_award_id => p_mass_award_id,
797 p_asg_select => l_asg_select
798 );
799 build_pos_job_kff_sel(p_mass_award_id => p_mass_award_id,
800 p_kff_select => l_kff_select,
801 p_pos_exists => l_pos_exists,
802 p_job_exists => l_job_exists
803 );
804
805 build_pos_grp1_sel(p_mass_award_id => p_mass_award_id,
806 p_pos_grp1_select => l_pos_grp1_select,
807 p_poi_exists => l_poi_exists,
808 p_ofs_exists => l_ofs_exists,
809 p_ors_exists => l_ors_exists
810 );
811
812
813 build_pos_grp2_sel(p_mass_award_id => p_mass_award_id,
814 p_pos_grp2_select => l_pos_grp2_select,
815 p_poc_exists => l_poc_exists
816 );
817
818 build_pos_grd_sel(p_mass_award_id => p_mass_award_id,
819 p_pos_grd_select => l_pos_grd_select,
820 p_pay_plan_exists => l_pay_plan_exists,
821 p_grade_exists => l_grade_exists
822 );
823
824 build_loc_ddf_sel(p_mass_award_id => p_mass_award_id,
825 p_loc_ddf_select => l_loc_ddf_select,
826 p_lei_exists => l_lei_exists
827 );
828
829
830
831 build_rating_sel(p_mass_award_id => p_mass_award_id,
832 p_rating_select => l_rating_select,
833 p_rating_exists => l_rating_exists);
834
835
836
837 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
838 DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
839 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_per_id);
840 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_asg_id);
841 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_pos_id);
842 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loc_id);
843 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_pos_id);
844 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_grd_id);
845 l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);
846
847 Loop
848 If DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
849 exit;
850 Else
851 dbms_sql.column_value(l_cursor_id,1,l_per_id);
852 dbms_sql.column_value(l_cursor_id,2,l_asg_id);
853 dbms_sql.column_value(l_cursor_id,3,l_pos_id);
854 dbms_sql.column_value(l_cursor_id,4,l_loc_id);
855 dbms_sql.column_value(l_cursor_id,5,l_job_id);
856 dbms_sql.column_value(l_cursor_id,6,l_grd_id);
857 -- Bug#2459352 Checking whether the employee belongs to federal org or not.
858
859 open c_fed_employee(l_pos_id);
860 fetch c_fed_employee into l_pos_grade_ei_id;
861 IF c_fed_employee%found then
862
863 l_cursor_kff_id := DBMS_SQL.OPEN_CURSOR;
864
865 DBMS_SQL.PARSE(l_cursor_kff_id,l_kff_select,DBMS_SQL.NATIVE);
866 If l_pos_exists then
867 For bus_gp_rec in c_business_group_id loop
868 l_business_group_id := bus_gp_rec.business_group_id;
869 End loop;
870
871 DBMS_SQL.bind_variable(l_cursor_kff_id,'pos_id',l_pos_id);
872 DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
873 DBMS_SQL.bind_variable(l_cursor_kff_id,'effective_date',l_effective_date);
874 End if;
875 If l_job_exists then
876 For bus_gp_rec in c_business_group_id loop
877 l_business_group_id := bus_gp_rec.business_group_id;
878 End loop;
879 DBMS_SQL.bind_variable(l_cursor_kff_id,'job_id',l_job_id);
880 DBMS_SQL.bind_variable(l_cursor_kff_id,'business_group_id',l_business_group_id);
881 End if;
882 l_numrows_kff := DBMS_SQL.EXECUTE(l_cursor_kff_id);
883 Loop
884 If DBMS_SQL.FETCH_ROWS(l_cursor_kff_id) = 0 then
885 exit;
886 Else
887 l_cursor_pos_grp1_id := DBMS_SQL.OPEN_CURSOR;
888 DBMS_SQL.PARSE(l_cursor_pos_grp1_id,l_pos_grp1_select,DBMS_SQL.NATIVE);
889
890 -- Get POS gRP1 from history
891 ghr_history_fetch.fetch_positionei
892 (p_position_id => l_pos_id,
893 p_information_type => 'GHR_US_POS_GRP1',
894 p_date_effective => l_effective_date,
895 p_pos_ei_data => l_pos_ei_data
896 );
897 If l_poi_exists then
898 DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'POI',l_pos_ei_data.poei_information3);
899 End if;
900 If l_ofs_exists then
901 DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'OFS',l_pos_ei_data.poei_information4);
902 End if;
903 If l_ors_exists then
904 DBMS_SQL.bind_variable(l_cursor_pos_grp1_id,'ORS',l_pos_ei_data.poei_information5);
905 End if;
906 l_pos_ei_data := Null;
907 l_numrows_pos_grp1 := DBMS_SQL.EXECUTE(l_cursor_pos_grp1_id);
908 Loop
909 If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp1_id) = 0 then
910 exit;
911 Else
912 l_cursor_pos_grp2_id := DBMS_SQL.OPEN_CURSOR;
913 DBMS_SQL.PARSE(l_cursor_pos_grp2_id,l_pos_grp2_select,DBMS_SQL.NATIVE);
914
915 -- Get POS gRP2 from history
916 ghr_history_fetch.fetch_positionei
917 (p_position_id => l_pos_id,
918 p_information_type => 'GHR_US_POS_GRP2',
919 p_date_effective => l_effective_date,
920 p_pos_ei_data => l_pos_ei_data
921 );
922 If l_poc_exists then
923 DBMS_SQL.bind_variable(l_cursor_pos_grp2_id,'POC',l_pos_ei_data.poei_information3);
924 End if;
925
926 --l_pos_ei_data := Null;
927 l_numrows_pos_grp2 := DBMS_SQL.EXECUTE(l_cursor_pos_grp2_id);
928 Loop
929 If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grp2_id) = 0 then
930 exit;
931 Else
932
933 -- Open cursor
934 l_cursor_pos_grd_id := DBMS_SQL.OPEN_CURSOR;
935 DBMS_SQL.PARSE(l_cursor_pos_grd_id,l_pos_grd_select,DBMS_SQL.NATIVE);
936
937 If l_pay_plan_exists or l_grade_exists then
938
939 -- Get POSITION VALID GRADE from history
940 ghr_history_fetch.fetch_positionei
941 (p_position_id => l_pos_id,
942 p_information_type => 'GHR_US_POS_VALID_GRADE',
943 p_date_effective => l_effective_date,
944 p_pos_ei_data => l_pos_ei_data
945 );
946 If l_pay_plan_exists then
947 DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'PP',hr_api.g_varchar2);
948 End if;
949 If l_grade_exists then
950 DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'GRD',hr_api.g_varchar2);
951 End if;
952
953 If l_pos_ei_data.poei_information3 is not null then
954 For grade_kff_rec in c_grade_kff(to_number(l_pos_ei_data.poei_information3)) loop
955 If l_pay_plan_exists then
956 If grade_kff_rec.pay_plan is not null then
957 DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'PP',grade_kff_rec.pay_plan);
958 End if;
959 End if;
960
961 If l_grade_exists then
962 If grade_kff_rec.grade_or_level is not null then
963 DBMS_SQL.bind_variable(l_cursor_pos_grd_id,'GRD',grade_kff_rec.grade_or_level);
964 End if;
965 End if;
966
967 End loop;
968
969 End if;
970 End if;
971
972 l_numrows_pos_grd := DBMS_SQL.EXECUTE(l_cursor_pos_grd_id);
973 Loop
974 If DBMS_SQL.FETCH_ROWS(l_cursor_pos_grd_id) = 0 then
975 exit;
976 Else
977 l_cursor_loc_ddf_id := DBMS_SQL.OPEN_CURSOR;
978 DBMS_SQL.PARSE(l_cursor_loc_ddf_id,l_loc_ddf_select,DBMS_SQL.NATIVE);
979 If l_lei_exists then
980 for duty_station_code_rec in c_duty_station_code loop
981 l_duty_Station_code := duty_station_code_rec.duty_Station_code;
982 end loop;
983 DBMS_SQL.bind_variable(l_cursor_loc_ddf_id,'DSC',l_duty_station_code);
984 End if;
985 l_numrows_loc_ddf := DBMS_SQL.EXECUTE(l_cursor_loc_ddf_id);
986 Loop
987 If DBMS_SQL.FETCH_ROWS(l_cursor_loc_ddf_id) = 0 then
988 exit;
989 Else
990 l_cursor_rating_id := DBMS_SQL.OPEN_CURSOR;
991 DBMS_SQL.PARSE(l_cursor_rating_id,l_rating_select,DBMS_SQL.NATIVE);
992 If l_rating_exists then
993 --get rating record
994 ghr_api.return_special_information
995 (p_person_id => l_per_id,
996 p_structure_name => 'US Fed Perf Appraisal',
997 p_effective_date => l_effective_date,
998 p_special_info => l_sit
999 );
1000 DBMS_SQL.bind_variable(l_cursor_rating_id,'RATING',l_sit.segment2);
1001 End if;
1002 l_numrows_rating := DBMS_SQL.EXECUTE(l_cursor_rating_id);
1003 Loop
1004 If DBMS_SQL.FETCH_ROWS(l_cursor_rating_id) = 0 then
1005 exit;
1006 Else
1007 l_count := l_count + 1;
1008
1009
1010 -- Call appropriate procedure to Populate RPA/ RPA EI segments.
1011 hr_utility.set_location('Eff. date in elig ' || l_effective_date,1);
1012 hr_utility.set_location('p_mass_award_id in elig' || p_mass_award_id,1);
1013
1014 ghr_mass_awards_pkg.build_rpa_for_mass_awards
1015 (p_mass_award_id => p_mass_award_id,
1016 p_action_type => p_action_type,
1017 p_rpa_type => 'A',
1018 p_effective_date => l_effective_date,
1019 p_person_id => l_per_id,
1020 p_assignment_id => l_asg_id,
1021 p_position_id => l_pos_id,
1022 p_job_id => l_job_id,
1023 p_location_id => l_loc_id,
1024 p_grade_id => l_grd_id,
1025 p_errbuf => l_errbuf,
1026 p_retcode => l_retcode,
1027 p_status => l_status,
1028 p_maxcheck => p_maxcheck
1029 );
1030 -- Set counter for success, failure cases.
1031 -- only in case where p_action_type = 'FINAL'
1032 -- based on value in l_status.
1033 -- l_success_ctr
1034 -- l_err_man_ctr
1035 -- l_err_mass_ctr
1036 -- l_desel_ctr
1037
1038 If l_old_retcode is null then
1039 l_old_retcode := l_retcode;
1040 End if;
1041 If l_old_retcode = '1' then
1042 l_retcode := l_old_retcode;
1043 Else
1044 l_old_retcode := l_retcode;
1045 End if;
1046 hr_utility.set_location('retcode interm ' || l_retcode,1);
1047 If p_action_type = 'FINAL' then
1048 If l_status = 'SUCCESS' or l_status = 'PROCESSED' then
1049 l_succ_ctr := l_succ_ctr + 1;
1050 Elsif l_status = 'MANUAL' or l_status = 'FAILURE'
1051 or l_status = 'OTHER' then
1052 l_err_man_ctr := l_err_man_ctr + 1;
1053 Elsif l_status = 'GROUPBOX' then
1054 l_err_mass_ctr := l_err_mass_ctr + 1;
1055 --Bug 4065700 added deselected prg: to the desel counter count.
1056 Elsif l_status = 'DESELECTED' OR l_status = 'DESELECTED PRG:' then
1057 l_desel_ctr := l_desel_ctr + 1;
1058 End if;
1059 End if;
1060
1061
1062 End if;
1063 l_duty_station_code := Null; -- Note : Move with appropriate loop
1064 End loop;
1065 DBMS_SQL.CLOSE_CURSOR(l_cursor_rating_id);
1066 End if;
1067 End loop;
1068 DBMS_SQL.CLOSE_CURSOR(l_cursor_loc_ddf_id);
1069 End if;
1070 End loop;
1071 DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grd_id);
1072 End if;
1073 End loop;
1074 DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp2_id);
1075 End if;
1076 End loop;
1077 DBMS_SQL.CLOSE_CURSOR(l_cursor_pos_grp1_id);
1078 End if;
1079 End loop;
1080 DBMS_SQL.CLOSE_CURSOR(l_cursor_kff_id);
1081 End If;
1082 close c_fed_employee;
1083 End if;
1084 End loop;
1085 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1086
1087 /* Still to do */
1088 -- Set out parameters
1089 -- p_errbuf := ????
1090 -- p_retcode := ????
1091 p_retcode := l_retcode; ----AVR
1092 If p_action_type = 'FINAL' then
1093 /*If ( p_retcode = 0 and l_succ_ctr <> 0 ) then
1094 p_errbuf := 'Final Process executed successfully for all ' ||
1095 l_succ_ctr || 'Employees' ;
1096 Else
1097 */ -- Commented this for the bug #4065700. No need of a special message when all employees executed successfully.
1098 p_errbuf := ' ' || l_new_line ||
1099 'Final Process completed ' || l_new_line ||
1100 'Successful : ' || to_char(l_succ_ctr) || ' ' || l_new_line ||
1101 'Deselected : ' || to_char(l_desel_ctr) || ' ' || l_new_line ||
1102 'Failure : ' || to_char(l_err_man_ctr) || ' ' || l_new_line ||
1103 'Failure - Retained for Resubmission : ' || to_char(l_err_mass_ctr);
1104 --End if;
1105 End if;
1106
1107
1108 hr_utility.set_location('End of get eligible employees' ,1);
1109 hr_utility.set_location('retcode ' || p_retcode,2);
1110 hr_utility.set_location('errbuf ' || p_errbuf,3);
1111
1112 EXCEPTION
1113
1114 WHEN OTHERS THEN
1115 -- Reset IN OUT parameters and set OUT parameters
1116 --Added for NOCOPY CHanges.
1117 p_errbuf := NULL;
1118 p_retcode := NULL;
1119 p_status := l_nc_status;
1120 p_maxcheck := NULL;
1121
1122
1123 end get_eligible_employees;
1124
1125
1126 Procedure derive_rel_operator
1127 (p_in_rel_operator in varchar2,
1128 p_out_rel_operator out nocopy varchar2,
1129 p_prefix out nocopy varchar2,
1130 p_suffix out nocopy varchar2
1131 ) is
1132 Begin
1133 If p_in_rel_operator = 'CONTAINS' then
1134 p_out_rel_operator := ' LIKE ';
1135 p_prefix := '''' || '%' ;
1136 p_suffix := '%' || '''';
1137
1138 Elsif p_in_rel_operator = 'BEGINS WITH' then -- STARTS WITH
1139 p_out_rel_operator := ' LIKE ';
1140 p_prefix := '''';
1141 p_suffix := '%' || '''';
1142
1143 Elsif p_in_rel_operator = 'ENDS WITH' then
1144 p_out_rel_operator := ' LIKE ';
1145 p_prefix := '''' || '%' ;
1146 p_suffix := '''';
1147
1148 Elsif p_in_rel_operator = 'EQUALS' then
1149 p_out_rel_operator := ' = ';
1150 p_prefix := '''' ;
1151 p_suffix := '''';
1152
1153 Elsif p_in_rel_operator = 'NOT EQUALS' then
1154 p_out_rel_operator := ' <> ';
1155 p_prefix := '''' ;
1156 p_suffix := '''';
1157
1158 Else
1159 p_out_rel_operator := ' = ';
1160 p_prefix := '''' ;
1161 p_suffix := '''';
1162
1163
1164 End if;
1165
1166 EXCEPTION
1167
1168 WHEN OTHERS THEN
1169 p_out_rel_operator := NULL;
1170 p_prefix := NULL;
1171 p_suffix := NULL;
1172
1173 End derive_rel_operator;
1174
1175 End ghr_mass_awards_elig;
1176