[Home] [Help]
PACKAGE BODY: APPS.GHR_RIF_PKG
Source
1 PACKAGE BODY ghr_rif_pkg AS
2 /* $Header: ghrifpkg.pkb 120.1.12010000.2 2008/08/05 15:12:39 ubhat ship $ */
3
4 Procedure return_ratings
5 (p_person_id in number
6 ,p_structure_name in varchar2
7 ,p_effective_date in date
8 ,p_special_info1 out nocopy ghr_api.special_information_type
9 ,p_special_info2 out nocopy ghr_api.special_information_type
10 ,p_special_info3 out nocopy ghr_api.special_information_type
11 )
12 is
13 l_proc varchar2(72) := 'return_special_information ';
14 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
15
16 Cursor c_flex_num is
17 select flx.id_flex_num
18 from fnd_id_flex_structures_tl flx
19 where flx.id_flex_code = 'PEA' --
20 and flx.application_id = 800 --
21 and flx.id_flex_structure_name = p_structure_name
22 and flx.language = 'US';
23
24 Cursor cur_sit is
25 select pea.segment2 segment2,
26 pea.segment3 segment3,
27 pan.date_from date_from
28 from per_analysis_criteria pea,
29 per_person_analyses pan
30 where pan.person_id = p_person_id
31 and decode(pan.id_flex_num,l_id_flex_num,1,2) = 1
32 and pea.analysis_criteria_id = pan.analysis_criteria_id
33 and add_months(p_effective_date,-48) < ghr_general.return_rif_date(pea.segment3)
34 order by 3 desc ;
35
36
37 begin
38
39 for flex_num in c_flex_num loop
40 l_id_flex_num := flex_num.id_flex_num;
41 exit;
42 End loop;
43
44 If l_id_flex_num is null then
45 hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
46 hr_utility.raise_error;
47 End if;
48
49
50 for cur_sit_rec in cur_sit loop
51 if cur_sit%rowcount = 1 then
52 p_special_info1.segment2 := cur_sit_rec.segment2;
53 p_special_info1.segment3 := cur_sit_rec.segment3;
54 elsif cur_sit%rowcount = 2 then
55 p_special_info2.segment2 := cur_sit_rec.segment2;
56 p_special_info2.segment3 := cur_sit_rec.segment3;
57 elsif cur_sit%rowcount = 3 then
58 p_special_info3.segment2 := cur_sit_rec.segment2;
59 p_special_info3.segment3 := cur_sit_rec.segment3;
60 end if;
61 end loop;
62
63 Exception
64 When Others then
65 p_special_info1 := NULL;
66 p_special_info2 := NULL;
67 p_special_info3 := NULL;
68 raise;
69 end return_ratings;
70
71
72
73 procedure purge_register(p_session_id in ghr_rif_registers.session_id%TYPE) is
74 BEGIN
75 delete from ghr_rif_registers
76 where session_id = p_session_id;
77 END;
78
79
80
81 procedure get_grd (
82 p_grade_id in varchar2
83 ,p_pay_plan out nocopy varchar2
84 ,p_grade_or_level out nocopy varchar2
85 )
86 IS
87
88 CURSOR cur_grd IS
89 SELECT gdf.segment1 pay_plan
90 ,gdf.segment2 grade_or_level
91 FROM per_grade_definitions gdf
92 ,per_grades grd
93 WHERE grd.grade_id = p_grade_id
94 AND grd.grade_definition_id = gdf.grade_definition_id;
95
96 BEGIN
97
98 FOR cur_grd_rec IN cur_grd LOOP
99 p_pay_plan := cur_grd_rec.pay_plan;
100 p_grade_or_level := cur_grd_rec.grade_or_level;
101 exit;
102 END LOOP;
103
104 EXCEPTION
105 -- NOCOPY Changes
106 WHEN OTHERS THEN
107 p_pay_plan := NULL;
108 p_grade_or_level := NULL;
109 raise;
110 END ;
111
112
113 function get_entered_grade_date(p_asg_id in number,
114 p_start_date in date )
115
116 return date is
117 cursor cur_egd is
118 select effective_start_date,
119 gdf.segment2 grade
120 from per_assignments_f asg,
121 per_grades grd,
122 per_grade_definitions gdf
123 where grd.grade_id (+) = asg.grade_id
124 and asg.assignment_id = p_asg_id
125 and asg.assignment_type <> 'B'
126 and grd.grade_definition_id = gdf.grade_definition_id (+)
127 and gdf.segment2 is not null
128 and trunc(asg.effective_start_date) <= trunc(p_start_date)
129 order by asg.effective_start_date desc,
130 gdf.segment2;
131
132
133 l_start_date date;
134 l_temp_start_date date;
135 l_temp_grade varchar2(60);
136 l_m_start_date date;
137 l_m_grade varchar2(60);
138 l_temp_id number := 0;
139
140 begin
141
142
143 for cur_rec in cur_egd
144 loop
145 l_temp_start_date := cur_rec.effective_start_date;
146 l_temp_grade := cur_rec.grade;
147 if l_temp_id = 0 then
148 l_m_start_date := l_temp_start_date;
149 l_m_grade := l_temp_grade;
150 l_temp_id := 1;
151 end if;
152 if l_temp_id = 1 then
153 if l_m_grade = l_temp_grade then
154 if trunc(l_m_start_date) = trunc(l_temp_start_date) then
155 null;
156 else
157 l_m_start_date := l_temp_start_date;
158 end if;
159 else
160 exit;
161 end if;
162 end if;
163 end loop;
164 return l_m_start_date;
165 end;
166
167 procedure get_lookup_meaning_desc (
168 p_application_id IN number
169 ,p_lookup_type IN hr_lookups.lookup_type%TYPE
170 ,p_lookup_code IN hr_lookups.lookup_code%TYPE
171 ,p_lookup_meaning OUT NOCOPY hr_lookups.meaning%TYPE
172 ,p_lookup_desc OUT NOCOPY hr_lookups.description%TYPE
173 ) IS
174 CURSOR cur_loc IS
175 --bug 760715 even though application id is passed in no longer need to use when
176 -- using hr_lookups view
177 SELECT loc.meaning,
178 loc.description
179 FROM hr_lookups loc
180 WHERE loc.lookup_type = p_lookup_type
181 AND loc.lookup_code = p_lookup_code;
182
183 BEGIN
184
185 FOR cur_loc_rec IN cur_loc LOOP
186 p_lookup_meaning := cur_loc_rec.meaning;
187 p_lookup_desc := cur_loc_rec.description;
188 exit;
189 END LOOP;
190 -- NOCOPY Changes
191 EXCEPTION
192 WHEN OTHERS THEN
193 p_lookup_meaning := NULL;
194 p_lookup_desc := NULL;
195 raise;
196 END get_lookup_meaning_desc;
197
198 procedure run_register (
199 p_rif_criteria_id IN ghr_rif_criteria.rif_criteria_id%TYPE
200 ,p_organization_id in ghr_rif_criteria.organization_id%TYPE
201 ,p_org_structure_id in ghr_rif_criteria.org_structure_id%TYPE
202 ,p_office_symbol in ghr_rif_criteria.office_symbol%TYPE
203 ,p_agency_code_subelement in ghr_rif_criteria.agency_code_subelement%TYPE
204 ,p_comp_area in ghr_rif_criteria.comp_area%TYPE
205 ,p_comp_level in ghr_rif_criteria.comp_level%TYPE
206 ,p_effective_date in date
207 ) IS
208
209 -- Bug 4377361 included EMP_APL for person type condition
210 cursor cur_people (p_effective_date date) is
211 select per.person_id PERSON_ID,
212 per.first_name FIRST_NAME,
213 per.last_name LAST_NAME,
214 per.full_name FULL_NAME,
215 per.middle_names MIDDLE_NAMES,
216 per.date_of_birth DATE_OF_BIRTH,
217 per.national_identifier NATIONAL_IDENTIFIER,
218 asg.position_id POSITION_ID,
219 asg.assignment_id ASSIGNMENT_ID,
220 asg.grade_id GRADE_ID,
221 asg.job_id JOB_ID,
222 asg.business_group_id BUSINESS_GROUP_ID,
223 asg.organization_id ORGANIZATION_ID,
224 asg.effective_start_date EFFECTIVE_START_DATE
225 from per_assignments_f asg,
226 per_people_f per,
227 per_person_types ppt
228 where per.person_id = asg.person_id
229 and asg.primary_flag = 'Y'
230 and asg.assignment_type <> 'B'
231 and p_effective_date between asg.effective_start_date
232 and asg.effective_end_date
233 and per.person_type_id = ppt.person_type_id
234 and ppt.system_person_type IN ('EMP','EMP_APL')
235 and p_effective_date between per.effective_start_date
236 and per.effective_end_date
237 and asg.position_id is not null;
238
239 cursor cur_criteria(p_rif_criteria_id ghr_rif_criteria.rif_criteria_id%TYPE)
240 is
241 select rif.comp_area comp_area,
242 rif.comp_level comp_level,
243 rif.effective_date,
244 rif.organization_id,
245 rif.org_structure_id,
246 rif.office_symbol,
247 rif.agency_code_subelement
248 from ghr_rif_criteria rif
249 where rif.rif_criteria_id = p_rif_criteria_id;
250
251 cursor job_name(p_job_id per_jobs.job_id%TYPE)
252 is
253 select jobs.name
254 from per_jobs jobs
255 where jobs.job_id = p_job_id;
256
257 cursor position_name(p_position_id hr_positions_f.position_id%TYPE,p_effective_date date )
258 is
259 select name
260 from hr_positions_f pos
261 where pos.position_id = p_position_id
262 and p_effective_date between pos.effective_start_date
263 and pos.effective_end_date;
264
265 cursor cur_rif_reg_seq is
266 Select ghr_rif_registers_s.nextval from dual;
267
268 l_rif_reg ghr_rif_registers%rowtype;
269
270 l_c_comp_area varchar2(30);
271 l_c_comp_level varchar2(30);
272 l_c_effective_date date;
273 l_c_organization_id number(15);
274 l_c_org_structure_id varchar2(20);
275 --Start of Bug # 5632674 changed from varchar2(8) to varchar2(18)
276 l_c_office_symbol varchar2(18);
277 --End of Bug#5632674
278
279 l_c_agency_code_se varchar2(30);
280
281 l_asg_cnt number;
282 l_grd_cnt number;
283
284 l_comp_area varchar2(30);
285 l_comp_level varchar2(30);
286 l_multiple_error_flag boolean;
287 l_dummy_parameter varchar2(30);
288 l_value varchar2(30);
289 l_effective_date date;
290
291 l_rating1 varchar2(80);
292 l_rating2 varchar2(80);
293 l_rating3 varchar2(80);
294
295 l_pos_ei_data1 per_position_extra_info%rowtype;
296 l_pos_ei_data2 per_position_extra_info%rowtype;
297 l_pos_ei_data3 per_position_extra_info%rowtype;
298
299 l_asg_ei_data per_assignment_extra_info%rowtype;
300
301 l_people_ei_data1 per_people_extra_info%rowtype;
302 l_people_ei_data2 per_people_extra_info%rowtype;
303
304 l_perf_appraisal1 ghr_api.special_information_type;
305 l_perf_appraisal2 ghr_api.special_information_type;
306 l_perf_appraisal3 ghr_api.special_information_type;
307
308 BEGIN
309 hr_utility.set_location('Enter Rif' ,1);
310
311 if p_comp_area is null and
312 p_comp_level is null and
313 p_effective_date is null and
314 p_organization_id is null and
315 p_org_structure_id is null and
316 p_office_symbol is null and
317 p_agency_code_subelement is null
318 then
319 open cur_criteria(p_rif_criteria_id);
320 fetch cur_criteria into l_c_comp_area,
321 l_c_comp_level,
322 l_c_effective_date,
323 l_c_organization_id,
324 l_c_org_structure_id,
325 l_c_office_symbol,
326 l_c_agency_code_se;
327 if cur_criteria%NOTFOUND then
328 hr_utility.set_message(8301,'GHR_38485_NULL_RIF_CRITERIA');
329 hr_utility.raise_error;
330 end if;
331
332 close cur_criteria;
333
334 else
335 l_c_comp_area := p_comp_area;
336 l_c_comp_level:= p_comp_level;
337 l_c_effective_date := p_effective_date;
338 l_c_organization_id := p_organization_id;
339 l_c_org_structure_id := p_org_structure_id;
340 l_c_office_symbol := p_office_symbol;
341 l_c_agency_code_se := p_agency_code_subelement;
342 end if;
343
344 /* Comp_area is used to be required field but with Bug 691379 it become optional */
345
346 if l_c_comp_level is null then
347 hr_utility.set_message(8301,'GHR_38484_NO_COMP_AREA_LEVEL');
348 hr_utility.raise_error;
349 end if;
350
351 l_effective_date := trunc(sysdate); -- In the future l_effective_date may set to p_effective_date
352
353 DELETE FROM ghr_rif_registers reg
354 WHERE reg.session_id = userenv('SESSIONID')
355 and rif_criteria_id = p_rif_criteria_id;
356
357 hr_utility.set_location('Purged rif_registers' ,2);
358
359 FOR per_rec IN cur_people (l_effective_date)
360
361 LOOP
362
363 --Get the Person Comp Area, Comp Level,Org Structure Id, Office Symbol
364
365 hr_utility.set_location('Getting position EI',3);
366 ghr_history_fetch.fetch_positionei
367 (p_position_id => per_rec.position_id
368 ,p_information_type => 'GHR_US_POS_GRP1'
369 ,p_date_effective => l_effective_date
370 ,p_pos_ei_data => l_pos_ei_data1
371 );
372
373 l_comp_area := l_pos_ei_data1.poei_information20;
374 l_comp_level := l_pos_ei_data1.poei_information9;
375 l_rif_reg.org_structure_id := l_pos_ei_data1.poei_information5;
376 l_rif_reg.office_symbol := l_pos_ei_data1.poei_information4;
377
378 -- Agency Code
379 hr_utility.set_location('Getting Agency Code',4);
380
381 l_rif_reg.agency_code_subelement := ghr_api.get_position_agency_code_pos
382 (p_position_id => per_rec.position_id
383 ,p_business_group_id => per_rec.business_group_id
384 ,p_effective_date => l_effective_date);
385
386 --Check the Criteria
387
388 hr_utility.set_location('Checking the Criteria',5);
389 if nvl(l_comp_area,hr_api.g_varchar2) = nvl(l_c_comp_area,nvl(l_comp_area,hr_api.g_varchar2)) and
390 nvl(l_comp_level,hr_api.g_varchar2) = l_c_comp_level and
391 nvl(l_rif_reg.org_structure_id,hr_api.g_varchar2 ) = nvl(l_c_org_structure_id,nvl(l_rif_reg.org_structure_id,hr_api.g_varchar2 )) and
392 nvl(l_rif_reg.office_symbol,hr_api.g_varchar2) = nvl(l_c_office_symbol,nvl(l_rif_reg.office_symbol,hr_api.g_varchar2)) and
393 nvl(l_rif_reg.agency_code_subelement,hr_api.g_varchar2 ) = nvl(l_c_agency_code_se,nvl(l_rif_reg.agency_code_subelement,hr_api.g_varchar2)) and
394 nvl(per_rec.organization_id,hr_api.g_number ) = nvl(l_c_organization_id,nvl(per_rec.organization_id,hr_api.g_number))
395 then
396
397 -- Populate the details into ghr_rif_registers
398
399 -- Tenure and Step_or_rate
400 hr_utility.set_location('Getting Tenure and Ster_or_rate',5);
401
402 ghr_history_fetch.fetch_asgei
403 (p_assignment_id => per_rec.assignment_id
404 ,p_information_type => 'GHR_US_ASG_SF52'
405 ,p_date_effective => l_effective_date
406 ,p_asg_ei_data => l_asg_ei_data
407 );
408 l_rif_reg.tenure := l_asg_ei_data.aei_information4;
409 l_rif_reg.step_or_rate := l_asg_ei_data.aei_information3;
410
411 -- Tenure Description and Group
412
413
414 hr_utility.set_location('Getting the Tenure Description and Group',6);
415 get_lookup_meaning_desc (
416 p_application_id => '800',
417 p_lookup_type => 'GHR_US_TENURE',
418 p_lookup_code => l_rif_reg.tenure,
419 p_lookup_meaning => l_rif_reg.tenure_desc,
420 p_lookup_desc => l_rif_reg.tenure_group);
421
422 -- Tenure Group Order
423
424 get_lookup_meaning_desc (
425 p_application_id => '800',
426 p_lookup_type => 'GHR_US_TENURE_GROUP',
427 p_lookup_code => l_rif_reg.tenure_group,
428 p_lookup_meaning => l_rif_reg.tenure_group_desc,
429 p_lookup_desc => l_rif_reg.tenure_group_order);
430
431 -- Vets info
432 hr_utility.set_location('Getting Vets Info',7);
433
434 ghr_history_fetch.fetch_peopleei
435 (p_person_id => per_rec.person_id,
436 p_information_type => 'GHR_US_PER_SF52',
437 p_date_effective => l_effective_date,
438 p_per_ei_data => l_people_ei_data1
439 );
440
441 l_rif_reg.veterans_pref_for_rif := l_people_ei_data1.pei_information5;
442 l_rif_reg.veterans_preference := l_people_ei_data1.pei_information4;
443
444 -- VETERANS DESCRIPTION and SUB GROUP
445
446 get_lookup_meaning_desc (
447 p_application_id => '800',
448 p_lookup_type => 'GHR_US_VETERANS_PREF_FOR_RIF',
449 p_lookup_code => l_rif_reg.veterans_pref_for_rif,
450 p_lookup_meaning => l_dummy_parameter,
451 p_lookup_desc => l_rif_reg.veterans_pref_sub_group);
452
453 -- if veterans_pref_sub_group is null then use GHR_US_VETERANS_PREFERENCE looking type
454
455 if l_rif_reg.veterans_pref_sub_group is null
456 then
457 get_lookup_meaning_desc (
458 p_application_id => '800',
459 p_lookup_type => 'GHR_US_VETERANS_PREF',
460 p_lookup_code => l_rif_reg.veterans_preference,
461 p_lookup_meaning => l_rif_reg.veterans_preference_desc,
462 p_lookup_desc => l_rif_reg.veterans_pref_sub_group);
463 end if;
464
465 -- VETERANS PREFERENCE SUB GROUP DESC AND ORDER
466
467 get_lookup_meaning_desc (
468 p_application_id => '800',
469 p_lookup_type => 'GHR_US_VETERANS_PREF_SUB_GROUP',
470 p_lookup_code => l_rif_reg.veterans_pref_sub_group,
471 p_lookup_meaning => l_rif_reg.veterans_pref_sub_group_desc,
472 p_lookup_desc => l_rif_reg.veterans_pref_sub_group_order);
473
474 -- SCD info
475
476 ghr_history_fetch.fetch_peopleei
477 (p_person_id => per_rec.person_id,
478 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
479 p_date_effective => l_effective_date,
480 p_per_ei_data => l_people_ei_data2
481 );
482
483 l_rif_reg.service_comp_date_rif := fnd_date.canonical_to_date(l_people_ei_data2.pei_information5);
484 l_rif_reg.service_comp_date_civilian := fnd_date.canonical_to_date(l_people_ei_data2.pei_information4);
485
486 -- Series (Occ_code)
487 hr_utility.set_location('Getting OCC Code' ,8);
488
489 l_rif_reg.occ_code := ghr_api.get_job_occ_series_job
490 (p_job_id => per_rec.job_id,
491 p_business_group_id => per_rec.business_group_id
492 );
493
494 -- Series Decription
495
496 get_lookup_meaning_desc (
497 p_application_id => '800',
498 p_lookup_type => 'GHR_US_OCC_SERIES',
499 p_lookup_code => l_rif_reg.occ_code,
500 p_lookup_meaning => l_rif_reg.occ_code_desc,
501 p_lookup_desc => l_dummy_parameter);
502
503 -- Job name - select from per_jobs - in job_id,business_group_id
504
505 if per_rec.job_id is not null then
506 for job_name_rec in job_name(per_rec.job_id) loop
507 l_rif_reg.job_name := job_name_rec.name;
508 exit;
509 end loop;
510 end if;
511
512 -- Pay plan and Grade id
513
514
515 get_grd(p_grade_id => per_rec.grade_id,
516 p_pay_plan => l_rif_reg.pay_plan,
517 p_grade_or_level => l_rif_reg.grade_or_level);
518
519
520 -- Getting Entered Present grade date
521
522 l_rif_reg.entered_grade_date :=
523 get_entered_grade_date(p_asg_id => per_rec.assignment_id,
524 p_start_date => per_rec.effective_start_date);
525
526 --WGI Due Date
527 hr_utility.set_location('Getting WGI Due Date',9);
528
529 ghr_api.retrieve_element_entry_value
530 (p_element_name => 'Within Grade Increase',
531 p_input_value_name => 'Date Due',
532 p_assignment_id => per_rec.assignment_id, p_effective_date => l_effective_date,
533 p_value => l_value,
534 p_multiple_error_flag => l_multiple_error_flag
535 );
536 hr_utility.set_location('After Getting WGI Due Date',9);
537 l_rif_reg.wgi_due_date := fnd_date.canonical_to_date(l_value);
538
539 -- Ratings 1
540
541 hr_utility.set_location('Getting return ratings',10);
542
543 return_ratings
544 (p_person_id => per_rec.person_id,
545 p_structure_name => 'US Fed Perf Appraisal',
546 p_effective_date => l_effective_date,
547 p_special_info1 => l_perf_appraisal1,
548 p_special_info2 => l_perf_appraisal2,
549 p_special_info3 => l_perf_appraisal3
550 );
551
552 l_rif_reg.rating_of_record1 := l_perf_appraisal1.segment2;
553 l_rif_reg.rating_of_record1_date := fnd_date.canonical_to_date(l_perf_appraisal1.segment3);
554
555 l_rif_reg.rating_of_record2 := l_perf_appraisal2.segment2;
556 l_rif_reg.rating_of_record2_date := fnd_date.canonical_to_date(l_perf_appraisal2.segment3);
557
558 l_rif_reg.rating_of_record3 := l_perf_appraisal3.segment2;
559 l_rif_reg.rating_of_record3_date := fnd_date.canonical_to_date(l_perf_appraisal3.segment3);
560
561 -- Rating Description 1
562
563 get_lookup_meaning_desc (
564 p_application_id => '800',
565 p_lookup_type => 'GHR_US_RATING_OF_RECORD',
566 p_lookup_code => l_rif_reg.rating_of_record1,
567 p_lookup_meaning => l_rif_reg.rating_of_record1_desc,
568 p_lookup_desc => l_rating1);
569
570 -- Rating Description 2
571
572 get_lookup_meaning_desc (
573 p_application_id => '800',
574 p_lookup_type => 'GHR_US_RATING_OF_RECORD',
575 p_lookup_code => l_rif_reg.rating_of_record2,
576 p_lookup_meaning => l_rif_reg.rating_of_record2_desc,
577 p_lookup_desc => l_rating2);
578
579
580 -- Rating Description 3
581
582 get_lookup_meaning_desc (
583 p_application_id => '800',
584 p_lookup_type => 'GHR_US_RATING_OF_RECORD',
585 p_lookup_code => l_rif_reg.rating_of_record3,
586 p_lookup_meaning => l_rif_reg.rating_of_record3_desc,
587 p_lookup_desc => l_rating3);
588
589 -- Performance score
590
591 l_rif_reg.performance_score := ceil(((nvl(to_number(l_rating1),12) + nvl(to_number(l_rating2),12) + nvl(to_number(l_rating3),12) ) / 3));
592
593 -- Adjusted SCD
594 hr_utility.set_location('Getting Adjusterd SCD',11);
595
596 if l_rif_reg.service_comp_date_rif is null then
597 l_rif_reg.adjusted_service_comp_date := null;
598 else
599 l_rif_reg.adjusted_service_comp_date := add_months(l_rif_reg.service_comp_date_rif, -12*l_rif_reg.performance_score);
600 end if;
601
602
603
604 -- Position Occupied
605
606 ghr_history_fetch.fetch_positionei
607 (p_position_id => per_rec.position_id,
608 p_information_type => 'GHR_US_POS_GRP2',
609 p_date_effective => l_effective_date,
610 p_pos_ei_data => l_pos_ei_data2
611 );
612
613 l_rif_reg.position_occupied := l_pos_ei_data2.poei_information3;
614
615 -- Position Occupied Description
616
617 get_lookup_meaning_desc (
618 p_application_id => '800',
619 p_lookup_type => 'GHR_US_POSITION_OCCUPIED',
620 p_lookup_code => l_rif_reg.position_occupied,
621 p_lookup_meaning => l_rif_reg.position_occupied_desc,
622 p_lookup_desc => l_dummy_parameter);
623
624 -- Position Title
625 hr_utility.set_location('Getting Position Title',12);
626
627 l_rif_reg.position_title := ghr_api.get_position_title_pos
628 (p_position_id => per_rec.position_id,
629 p_business_group_id => per_rec.business_group_id,
630 p_effective_date => l_effective_date
631 );
632
633 -- Position Name
634
635 for pos_name_rec in position_name(per_rec.position_id,l_effective_date)
636 loop
637 l_rif_reg.position_name := pos_name_rec.name;
638 exit;
639 end loop;
640
641 -- Obligated Position
642
643 ghr_history_fetch.fetch_positionei
644 (p_position_id => per_rec.position_id,
645 p_information_type => 'GHR_US_POS_OBLIG',
646 p_date_effective => l_effective_date,
647 p_pos_ei_data => l_pos_ei_data3
648 );
649
650 l_rif_reg.obligated_posn_type := l_pos_ei_data3.poei_information4;
651 l_rif_reg.obligated_expiration_date := fnd_date.canonical_to_date(l_pos_ei_data3.poei_information3);
652
653 -- Obligated Postition Description
654
655 get_lookup_meaning_desc (
656 p_application_id => '800',
657 p_lookup_type => 'GHR_US_OBLIGATED_POSN_TYPE',
658 p_lookup_code => l_rif_reg.obligated_posn_type,
659 p_lookup_meaning => l_rif_reg.obligated_posn_type_desc,
660 p_lookup_desc => l_dummy_parameter);
661
662
663 -- Organization Name
664
665 l_rif_reg.organization_name := get_org_name(per_rec.organization_id);
666
667
668 -- Agency Code description
669
670 get_lookup_meaning_desc (
671 p_application_id => '800',
672 p_lookup_type => 'GHR_US_AGENCY_CODE',
673 p_lookup_code => l_rif_reg.agency_code_subelement,
674 p_lookup_meaning => l_rif_reg.agency_code_subelement_desc,
675 p_lookup_desc => l_dummy_parameter);
676
677 -- RIF Register ID
678
679 open cur_rif_reg_seq;
680 fetch cur_rif_reg_seq into l_rif_reg.rif_register_id;
681 close cur_rif_reg_seq;
682
683
684 hr_utility.set_location('Inserting into ghr_rif_registers',13);
685 INSERT INTO ghr_rif_registers
686 (rif_register_id
687 ,session_id
688 ,rif_criteria_id
689 ,effective_date
690 ,person_id
691 ,position_id
692 ,full_name
693 ,last_name
694 ,first_name
695 ,middle_names
696 ,national_identifier
697 ,tenure
698 ,tenure_desc
699 ,tenure_group
700 ,tenure_group_desc
701 ,tenure_group_order
702 ,veterans_pref_for_rif
703 ,veterans_preference
704 ,veterans_preference_desc
705 ,veterans_pref_sub_group
706 ,veterans_pref_sub_group_order
707 ,veterans_pref_sub_group_desc
708 ,service_comp_date_rif
709 ,performance_score
710 ,adjusted_service_comp_date
711 ,occ_code
712 ,occ_code_desc
713 ,job_name
714 ,pay_plan
715 ,grade_or_level
716 ,wgi_due_date
717 ,step_or_rate
718 ,service_comp_date_civilian
719 ,rating_of_record1
720 ,rating_of_record1_desc
721 ,rating_of_record1_date
722 ,rating_of_record2
723 ,rating_of_record2_desc
724 ,rating_of_record2_date
725 ,rating_of_record3
726 ,rating_of_record3_desc
727 ,rating_of_record3_date
728 ,position_occupied
729 ,position_occupied_desc
730 ,position_title
731 ,position_name
732 ,obligated_posn_type
733 ,obligated_posn_type_desc
734 ,organization_id
735 ,organization_name
736 ,org_structure_id
737 ,office_symbol
738 ,agency_code_subelement
739 ,agency_code_subelement_desc
740 ,entered_grade_date
741 ,obligated_expiration_date
742 ,comp_area
743 ,comp_level
744 )
745 VALUES
746 (l_rif_reg.rif_register_id
747 ,userenv('SESSIONID')
748 ,p_rif_criteria_id
749 ,l_effective_date
750 ,per_rec.person_id
751 ,per_rec.position_id
752 ,per_rec.full_name
753 ,per_rec.last_name
754 ,per_rec.first_name
755 ,per_rec.middle_names
756 ,per_rec.national_identifier
757 ,l_rif_reg.tenure
758 ,l_rif_reg.tenure_desc
759 ,l_rif_reg.tenure_group
760 ,l_rif_reg.tenure_group_desc
761 ,l_rif_reg.tenure_group_order
762 ,l_rif_reg.veterans_pref_for_rif
763 ,l_rif_reg.veterans_preference
764 ,l_rif_reg.veterans_preference_desc
765 ,l_rif_reg.veterans_pref_sub_group
766 ,l_rif_reg.veterans_pref_sub_group_order
767 ,l_rif_reg.veterans_pref_sub_group_desc
768 ,l_rif_reg.service_comp_date_rif
769 ,l_rif_reg.performance_score
770 ,l_rif_reg.adjusted_service_comp_date
771 ,l_rif_reg.occ_code
772 ,l_rif_reg.occ_code_desc
773 ,l_rif_reg.job_name
774 ,l_rif_reg.pay_plan
775 ,l_rif_reg.grade_or_level
776 ,l_rif_reg.wgi_due_date
777 ,l_rif_reg.step_or_rate
778 ,l_rif_reg.service_comp_date_civilian
779 ,l_rif_reg.rating_of_record1
780 ,l_rif_reg.rating_of_record1_desc
781 ,l_rif_reg.rating_of_record1_date
782 ,l_rif_reg.rating_of_record2
783 ,l_rif_reg.rating_of_record2_desc
784 ,l_rif_reg.rating_of_record2_date
785 ,l_rif_reg.rating_of_record3
786 ,l_rif_reg.rating_of_record3_desc
787 ,l_rif_reg.rating_of_record3_date
788 ,l_rif_reg.position_occupied
789 ,l_rif_reg.position_occupied_desc
790 ,l_rif_reg.position_title
791 ,l_rif_reg.position_name
792 ,l_rif_reg.obligated_posn_type
793 ,l_rif_reg.obligated_posn_type_desc
794 ,per_rec.organization_id
795 ,l_rif_reg.organization_name
796 ,l_rif_reg.org_structure_id
797 ,l_rif_reg.office_symbol
798 ,l_rif_reg.agency_code_subelement
799 ,l_rif_reg.agency_code_subelement_desc
800 ,l_rif_reg.entered_grade_date
801 ,l_rif_reg.obligated_expiration_date
802 ,l_comp_area
803 ,l_comp_level
804 );
805
806 END IF;
807 -- Come here if person doesn't match all criteria and loop to get next person
808
809 END LOOP;
810 hr_utility.set_location('Leaving run_register',50);
811
812
813 END run_register;
814
815
816 PROCEDURE purge_register IS
817 BEGIN
818 DELETE
819 FROM ghr_rif_registers reg
820 WHERE reg.session_id = USERENV('SESSIONID');
821 -- It really doesn't matter if it didn't actually delete any!
822 COMMIT;
823 --
824 END purge_register;
825 --
826 PROCEDURE check_unique_name (p_rif_criteria_id IN ghr_rif_criteria.rif_criteria_id%TYPE
827 ,p_name IN ghr_rif_criteria.name%TYPE) IS
828 --
829 CURSOR cur_rif IS
830 SELECT 1
831 FROM ghr_rif_criteria rif
832 WHERE rif.name = p_name
833 AND rif.rif_criteria_id <> NVL(p_rif_criteria_id,-1);
834 --
835 BEGIN
836 FOR cur_rif_rec IN cur_rif LOOP
837 hr_utility.set_message(8301,'GHR_99999_RIF_NAME_NOT_UNIQUE');
838 hr_utility.raise_error;
839 END LOOP;
840 --
841 END check_unique_name;
842
843
844 function get_org_name(p_organization_id IN per_organization_units.organization_id%TYPE)
845 return varchar2 is
846 cursor org_units is
847 select name
848 from per_organization_units porg
849 where porg.organization_id = p_organization_id;
850
851 l_name per_organization_units.name%TYPE;
852
853 begin
854 for org_units_rec in org_units
855 loop
856 l_name := org_units_rec.name;
857 exit;
858 end loop;
859 return l_name;
860 end;
861
862 FUNCTION num_of_vacancies(
863 p_organization_id in ghr_rif_criteria.organization_id%TYPE
864 ,p_org_structure_id in ghr_rif_criteria.org_structure_id%TYPE
865 ,p_office_symbol in ghr_rif_criteria.office_symbol%TYPE
866 ,p_agency_code_subelement in ghr_rif_criteria.agency_code_subelement%TYPE
867 ,p_comp_area in ghr_rif_criteria.comp_area%TYPE
868 ,p_comp_level in ghr_rif_criteria.comp_level%TYPE
869 ,p_effective_date in date
870 )
871 return number is
872
873
874 cursor unassigned_pos(p_effective_date date ) is
875 select position_id,organization_id,business_group_id
876 from hr_positions_f pos
877 where not exists
878 ( select 1
879 from per_assignments_f asg
880 where asg.position_id = pos.position_id
881 and asg.assignment_type <> 'B'
882 and p_effective_date between asg.effective_start_date
883 and asg.effective_end_date )
884 and organization_id = nvl(p_organization_id,organization_id)
885 and p_effective_date between pos.effective_start_date
886 and pos.effective_end_date;
887
888
889
890
891 l_num_of_vac number := 0;
892 l_pos_ei_data per_position_extra_info%rowtype;
893 l_comp_area varchar2(30);
894 l_comp_level varchar2(30);
895 l_org_structure_id varchar2(30);
896 l_office_symbol varchar2(30);
897 l_agency_code_se varchar2(30);
898 l_effective_date date;
899
900 begin
901
902 l_effective_date := trunc(sysdate); -- In the future l_effective_date may set to p_effective_date
903
904 for vac_rec in unassigned_pos(l_effective_date) loop
905
906 ghr_history_fetch.fetch_positionei
907 (p_position_id => vac_rec.position_id
908 ,p_information_type => 'GHR_US_POS_GRP1'
909 ,p_date_effective => l_effective_date
910 ,p_pos_ei_data => l_pos_ei_data
911 );
912
913 l_comp_area := l_pos_ei_data.poei_information20;
914 l_comp_level := l_pos_ei_data.poei_information9;
915 l_org_structure_id := l_pos_ei_data.poei_information5;
916 l_office_symbol := l_pos_ei_data.poei_information4;
917
918
919 l_agency_code_se := ghr_api.get_position_agency_code_pos
920 (p_position_id => vac_rec.position_id
921 ,p_business_group_id => vac_rec.business_group_id
922 ,p_effective_date => l_effective_date);
923
924 --Check the Criteria
925
926 if nvl(l_comp_area,hr_api.g_varchar2) = nvl(p_comp_area,nvl(l_comp_area,hr_api.g_varchar2)) and
927 nvl(l_comp_level,hr_api.g_varchar2) = nvl(p_comp_level,nvl(l_comp_level,hr_api.g_varchar2)) and
928 nvl(l_org_structure_id,hr_api.g_varchar2) = nvl(p_org_structure_id,nvl(l_org_structure_id,hr_api.g_varchar2) ) and
929 nvl(l_office_symbol,hr_api.g_varchar2) = nvl(p_office_symbol,nvl(l_office_symbol,hr_api.g_varchar2) ) and
930 nvl(l_agency_code_se,hr_api.g_varchar2 ) = nvl(p_agency_code_subelement,nvl(l_agency_code_se,hr_api.g_varchar2 ) )
931 then
932 l_num_of_vac := l_num_of_vac + 1;
933 end if;
934
935 end loop;
936
937 return l_num_of_vac;
938
939 end;
940
941
942 END ghr_rif_pkg;