[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_QUOTA_CHECK
Source
1 PACKAGE BODY PQH_FR_QUOTA_CHECK as
2 /* $Header: pqqutchk.pkb 120.1 2005/06/07 05:21 sankjain noship $ */
3
4 g_package Varchar2(30) := 'PQH_FR_QUOTA_CHECK.';
5 g_business_group_id number;
6
7 TYPE quota_rec IS
8 RECORD (corp_id number,
9 corp_name pqh_corps_definitions.name%type,
10 grade_id number,
11 grade_name per_grades.name%type,
12 cur_per_app number,
13 effective_date date);
14
15 TYPE quota_grid_table IS TABLE OF quota_rec
16 INDEX BY BINARY_INTEGER;
17
18
19 g_quota_grid quota_grid_table;
20
21 procedure delete_rows is
22 l_proc varchar2(60) := g_package||'delete_rows';
23 begin
24 hr_utility.set_location('Entering into '||l_proc,10);
25 g_quota_grid.delete;
26 hr_utility.set_location('Leaving '||l_proc,10);
27 end ;
28
29 Procedure Update_grid(p_corp_id in number,
30 p_corp_name in varchar2,
31 p_grade_id in number,
32 p_grade_name in varchar2,
33 p_effective_date in date) is
34
35 l_number_of_rows number;
36 l_flag varchar2(1);
37 l_next_row number;
38 l_proc varchar2(60) := g_package||'Update_grid';
39 Begin
40 hr_utility.set_location('Entering into '||l_proc,10);
41
42 if NVL(g_quota_grid.LAST, 0) = 0 then
43 hr_utility.set_location('First row in the grid',10);
44 g_quota_grid(1).corp_id := p_corp_id;
45 g_quota_grid(1).corp_name := p_corp_name;
46 g_quota_grid(1).grade_id := p_grade_id;
47 g_quota_grid(1).grade_name := p_grade_name;
48 g_quota_grid(1).cur_per_app := 1;
49 g_quota_grid(1).effective_date := p_effective_date;
50 else
51 l_number_of_rows := g_quota_grid.COUNT;
52 FOR table_row IN 1 .. l_number_of_rows
53 LOOP
54 if ( g_quota_grid(table_row).corp_id = p_corp_id and
55 g_quota_grid(table_row).grade_id = p_grade_id ) then
56
57 g_quota_grid(table_row).cur_per_app := g_quota_grid(table_row).cur_per_app + 1;
58 if g_quota_grid(table_row).effective_date > p_effective_date then
59 g_quota_grid(table_row).effective_date := p_effective_date;
60 end if;
61 l_flag := 'Y';
62 exit;
63 else
64
65 l_flag := 'N';
66
67 end if;
68
69 END LOOP;
70 if l_flag = 'N' then
71 l_next_row := NVL(g_quota_grid.LAST, 0) + 1;
72 g_quota_grid(l_next_row).corp_id := p_corp_id;
73 g_quota_grid(l_next_row).corp_name := p_corp_name;
74 g_quota_grid(l_next_row).grade_id := p_grade_id;
75 g_quota_grid(l_next_row).grade_name := p_grade_name;
76 g_quota_grid(l_next_row).cur_per_app := 1;
77 g_quota_grid(l_next_row).effective_date := p_effective_date;
78 end if;
79
80 end if;
81 hr_utility.set_location('Leaving '||l_proc,10);
82 end Update_grid;
83
84 Function population_corp(p_corp_id number,
85 p_business_group_id number,
86 p_effective_date date) return number is
87
88 l_proc varchar2(60) := g_package||'population_corp';
89 l_corp_population number;
90 l_effective_date date;
91 l_corp_id number;
92
93 Begin
94 hr_utility.set_location('Entering into '||l_proc,5);
95 l_effective_date := p_effective_date;
96 l_corp_id := p_corp_id;
97
98 Select count(asg.assignment_id)
99 into l_corp_population
100 From Per_all_assignments_f asg,
101 pqh_corps_definitions corp,
102 per_all_people_f per
103 Where asg.primary_flag = 'Y'
104 And asg.business_group_id = p_business_group_id
105 And asg.assignment_status_type_id in (1,2)
106 And l_effective_date between asg.effective_start_date And asg.effective_end_date
107 And asg.grade_ladder_pgm_id = corp.ben_pgm_id
108 And corp.corps_definition_id = l_corp_id
109 And per.person_id = asg.person_id
110 And l_effective_date between per.effective_start_date And per.effective_end_date
111 And per.per_information15 = '01';
112
113 hr_utility.set_location('Poplation for Corp'||to_char(l_corp_id)||': '||to_char(l_corp_population),5);
114 hr_utility.set_location('Leaving from '||l_proc,5);
115 Return l_corp_population;
116
117 End population_corp;
118
119 Function population_grade(p_corp_id number,
120 p_grade_id number,
121 p_business_group_id number,
122 p_effective_date date) return number is
123
124 l_grade_population number;
125 l_proc varchar2(60) := g_package||'population_grade';
126 l_effective_date date;
127 l_corp_id number;
128 l_grade_id number;
129 Begin
130 hr_utility.set_location('Entering into '||l_proc,5);
131
132 Select count(asg.assignment_id)
133 into l_grade_population
134 From Per_all_assignments_f asg,
135 pqh_corps_definitions corp,
136 per_all_people_f per
137 Where asg.primary_flag = 'Y'
138 And asg.business_group_id = p_business_group_id
139 And asg.assignment_status_type_id in (1,2)
140 And p_effective_date between asg.effective_start_date And asg.effective_end_date
141 And asg.grade_ladder_pgm_id = corp.ben_pgm_id
142 And corp.corps_definition_id = p_corp_id
143 And grade_id = p_grade_id
144 And per.person_id = asg.person_id
145 And p_effective_date between per.effective_start_date And per.effective_end_date
146 And per.per_information15 = '01';
147
148 hr_utility.set_location('Poplation for Grade '||to_char(p_grade_id)||' in corp '||to_char(p_corp_id)||': '||to_char(l_grade_population),5);
149
150 hr_utility.set_location('Leaving from '||l_proc,5);
151 Return l_grade_population;
152 End population_grade;
153
154 Function Quota_applicable (p_corp_id number,
155 p_effective_date date) return varchar2 is
156 l_proc varchar2(60) := g_package||'Quota_applicable';
157 l_quota_flag varchar2(1);
158 Begin
159
160 hr_utility.set_location('Entering into '||l_proc,5);
161
162 select nvl(pei.pgi_information1,'N') Quota_flag
163 into l_quota_flag
164 from ben_pgm_extra_info pei,
165 pqh_corps_definitions corps
166 where pei.information_type = 'PQH_FR_CORP_INFO'
167 and pei.pgm_id = corps.ben_pgm_id
168 and corps.corps_definition_id = p_corp_id;
169
170
171 hr_utility.set_location('Leaving from '||l_proc,5);
172
173 return l_quota_flag;
174 exception
175 when no_data_found then
176 return 'N';
177 End Quota_applicable;
178
179 Function Quota_occupancy (p_corp_id number,
180 p_grade_id number,
181 p_business_group_id number,
182 p_effective_date date) return number is
183 Cursor grade_condition is
184 Select Information4 Percentage,
185 Information8 cond_type,
186 Information30 Grade_ids
187 From pqh_corps_extra_info
188 Where corps_definition_id = p_corp_id
189 And information3 = p_grade_id
190 And information_type = 'GRADE';
191
192 l_percentage pqh_corps_extra_info.information30%type;
193 l_cond_type pqh_corps_extra_info.information30%type;
194 l_grade_ids pqh_corps_extra_info.information30%type;
195 l_Quota_occupancy number;
196 l_corps_population number;
197 l_grades_population number;
198 l_start number;
199 l_occurance number;
200 l_comma_pos number;
201 l_cond_grade_id number;
202 l_proc varchar2(60) := g_package||'Quota_occupancy';
203 Begin
204 hr_utility.set_location('Entering into '||l_proc,5);
205 Open grade_condition;
206 fetch grade_condition into l_percentage,l_cond_type,l_grade_ids;
207 Close grade_condition;
208
209 if l_percentage is null then
210 hr_utility.set_location('No Quota defined for the Grade '||to_char(p_grade_id),5);
211 l_Quota_occupancy := null;
212 elsif l_cond_type = 'CORPS' then
213 hr_utility.set_location('Quota defined in percentage of Corp for Grade '||to_char(p_grade_id),5);
214 l_corps_population := population_corp(p_corp_id,
215 p_business_group_id ,
216 p_effective_date);
217 l_quota_occupancy := floor(((l_percentage * l_corps_population)/100));
218 elsif l_cond_type = 'COMB_GRADES' then
219 hr_utility.set_location('Quota defined in percentage of combination of Grades for Grade '||to_char(p_grade_id),5);
220 l_start:= 1;
221 l_occurance := 1;
222 l_comma_pos := instr(l_grade_ids,',',l_start,l_occurance);
223 l_grades_population := 0;
224 while l_comma_pos > 0
225 loop
226
227 l_cond_grade_id := to_number(substr(l_grade_ids,l_start,(l_comma_pos-l_start)));
228
229 l_start := l_comma_pos+1;
230 l_comma_pos := instr(l_grade_ids,',',l_start,l_occurance);
231 l_grades_population := l_grades_population + population_grade(p_corp_id,
232 l_cond_grade_id,
233 p_business_group_id,
234 p_effective_date );
235
236 end loop;
237 l_cond_grade_id := to_number(substr(l_grade_ids,l_start));
238 l_grades_population := l_grades_population + population_grade(p_corp_id,
239 l_cond_grade_id,
240 p_business_group_id,
241 p_effective_date );
242 l_quota_occupancy := round(((l_percentage * l_grades_population)/100),0);
243
244 end if;
245 hr_utility.set_location('Leaving from '||l_proc,5);
246 return l_Quota_occupancy;
247 end Quota_occupancy;
248
249 procedure quota_grid_formation(p_elctbl_chc_id in number,
250 p_effective_date date) is
251
252 l_proc varchar2(60) := g_package||'quota_grid_formation';
253 l_elctbl_chc_id number;
254 l_person_id number;
255 l_prop_prom_date date;
256
257 cursor elctbl_chc_det is
258 select pil.person_id person_id,
259 corp.corps_definition_id corp_id,
260 corp.name corp_name,
261 elc.pgm_id program_id,
262 grades.grade_id grade_id,
263 grades.name grade_name,
264 pil.business_group_id business_group_id
265 From ben_elig_per_elctbl_chc elc, ben_per_in_ler pil,
266 ben_pl_f pl, per_grades grades,
267 pqh_corps_definitions corp
268 Where elc.elig_per_elctbl_chc_id = l_elctbl_chc_id
269 and elc.per_in_ler_id = pil.per_in_ler_id
270 And grades.grade_id = pl.mapping_table_pk_id
271 And pl.mapping_table_name = 'PER_GRADES'
272 and p_effective_date between pl.effective_start_date and pl.effective_end_date
273 And corp.ben_pgm_id = elc.pgm_id
274 And pl.pl_id = elc.pl_id
275 and PER_IN_LER_STAT_CD = 'STRTD';
276
277 l_elctbl_chc_det_rec elctbl_chc_det%rowtype;
278
279 cursor person_cur_info is
280 select grade_ladder_pgm_id pgm_id,
281 grade_id grade_id,
282 per.per_information15 agent_type
283 from per_all_assignments_f asg,
284 per_all_people_f per
285 where asg.person_id = l_elctbl_chc_det_rec.person_id
286 and primary_flag = 'Y'
287 and p_effective_date between asg.effective_start_date and asg.effective_end_date
288 and p_effective_date between per.effective_start_date and per.effective_end_date
289 and per.person_id = asg.person_id;
290
291 Cursor business_group_info is
292 Select business_group_id, legislation_code
293 from per_business_groups
294 where business_group_id = l_elctbl_chc_det_rec.business_group_id;
295
296 l_person_info person_cur_info%rowtype;
297 l_business_group_info business_group_info%rowtype;
298
299 begin
300 hr_utility.set_location('Entering into '||l_proc,5);
301 l_elctbl_chc_id := p_elctbl_chc_id;
302
303 if l_elctbl_chc_id is not null then
304
305 open elctbl_chc_det;
306 fetch elctbl_chc_det into l_elctbl_chc_det_rec;
307 close elctbl_chc_det;
308
309 end if;
310
311 open business_group_info;
312 fetch business_group_info into l_business_group_info;
313 close business_group_info;
314
315 if l_business_group_info.legislation_code = 'FR' then
316
317 if Quota_applicable(l_elctbl_chc_det_rec.corp_id,p_effective_date) = 'Y' then
318
319 open person_cur_info;
320 fetch person_cur_info into l_person_info;
321 close person_cur_info;
322
323 if l_person_info.agent_type = '01' then
324 if (l_person_info.pgm_id <> l_elctbl_chc_det_rec.program_id
325 or l_person_info.grade_id <> l_elctbl_chc_det_rec.grade_id ) then
326 hr_utility.set_location('Before Update Grid Call',5);
327 Update_grid(p_corp_id => l_elctbl_chc_det_rec.corp_id,
328 p_corp_name => l_elctbl_chc_det_rec.corp_name,
329 p_grade_id => l_elctbl_chc_det_rec.grade_id,
330 p_grade_name => l_elctbl_chc_det_rec.grade_name,
331 p_effective_date => p_effective_date );
332
333 else
334 hr_utility.set_location('No change in Grade',5);
335 end if;
336 end if;
337
338 end if;
339 end if;
340 hr_utility.set_location('Leaving from '||l_proc,5);
341 end quota_grid_formation;
342
343 procedure check_quota(p_business_group_id in number, p_return_status out nocopy varchar2) is
344
345 l_proc varchar2(60) := g_package||'check_quota';
346 l_number_of_rows number;
347 l_grade_cur_pop number;
348 l_quota_allowed number;
349 l_already_app number;
350 l_quota_allowed_char varchar2(10);
351 --l_return_status varchar2(1) := 'Y';
352 begin
353 hr_utility.set_location('Entering into '||l_proc, 10);
354 hr_multi_message.enable_message_list;
355 l_number_of_rows := g_quota_grid.COUNT;
356
357 if l_number_of_rows > 0 then
358 For table_row in 1..l_number_of_rows
359 loop
360 if Quota_applicable(g_quota_grid(table_row).corp_id,g_quota_grid(table_row).effective_date) = 'Y' then
361
362 hr_utility.set_location('checking Quota for Grade'||g_quota_grid(table_row).grade_name, 10);
363
364 l_grade_cur_pop := population_grade(p_corp_id => g_quota_grid(table_row).corp_id ,
365 p_grade_id => g_quota_grid(table_row).grade_id ,
366 p_business_group_id => p_business_group_id,
367 p_effective_date => g_quota_grid(table_row).effective_date);
368 l_quota_allowed := Quota_occupancy (p_corp_id => g_quota_grid(table_row).corp_id ,
369 p_grade_id => g_quota_grid(table_row).grade_id ,
370 p_business_group_id => p_business_group_id,
371 p_effective_date => g_quota_grid(table_row).effective_date);
372
373 select count(elc.elig_per_elctbl_chc_id )
374 into l_already_app
375 from ben_elig_per_elctbl_chc elc,
376 ben_per_in_ler pil,
377 ben_pl_f pl,
378 pqh_corps_definitions corp
379 where nvl(elc.approval_status_cd,'PQH_GSP_NP') = 'PQH_GSP_NP'
380 and elc.in_pndg_wkflow_flag = 'Y'
381 and pil.per_in_ler_id = elc.per_in_ler_id
382 and pil.per_in_ler_stat_cd = 'STRTD'
383 and corp.corps_definition_id = g_quota_grid(table_row).corp_id
384 and elc.pgm_id = corp.ben_pgm_id
385 and pl.mapping_table_name = 'PER_GRADES'
386 and pl.mapping_table_pk_id = g_quota_grid(table_row).grade_id
387 and g_quota_grid(table_row).effective_date between pl.effective_start_date and pl.effective_end_date
388 and elc.pl_id = pl.pl_id
389 and elc.business_group_id = p_business_group_id;
390
391
392 if l_quota_allowed is not null then
393 if ((l_quota_allowed - (l_grade_cur_pop + l_already_app + g_quota_grid(table_row).cur_per_app)) < 0 ) then
394
395 if sign((l_quota_allowed - (l_grade_cur_pop + l_already_app ))) = -1 then
396 l_quota_allowed_char := '0';
397 else
398 l_quota_allowed_char:= to_char((l_quota_allowed - (l_grade_cur_pop + l_already_app )));
399 end if;
400
401 fnd_message.set_name('PQH','PQH_FR_QUOTA_CHK_FAIL');
402 fnd_message.set_token('CORP', g_quota_grid(table_row).corp_name);
403 fnd_message.set_token('GRADE', g_quota_grid(table_row).grade_name);
404 fnd_message.set_token('EFFDATE',to_char(g_quota_grid(table_row).effective_date));
405 fnd_message.set_token('QUOTA_ALLOWED', l_quota_allowed_char);
406 fnd_message.set_token('APPROVED', g_quota_grid(table_row).cur_per_app);
407 hr_multi_message.add();
408 -- l_return_status := 'N';
409 end if;
410 end if;
411 end if;
412 end loop;
413 end if;
414 p_return_status := hr_multi_message.get_return_status_disable;
415 hr_utility.set_location('Leaving '||l_proc, 10);
416 exception
417 when hr_multi_message.error_message_exist then
418 --
419 -- Catch the Multiple Message List exception which
420 -- indicates Quota check failed.
421 --
422 p_return_status := hr_multi_message.get_return_status_disable;
423 hr_utility.set_location(' Leaving:' || l_proc, 30);
424 when others then
425 --
426 -- When Multiple Message Detection is enabled catch
427 -- any Application specific or other unexpected
428 -- exceptions. Adding appropriate details to the
429 -- Multiple Message List. Otherwise re-raise the
430 -- error.
431 --
432 if hr_multi_message.unexpected_error_add(l_proc) then
433 hr_utility.set_location(' Leaving:' || l_proc,40);
434 raise;
435 end if;
436 --
437 -- Reset IN OUT and set OUT parameters
438 --
439 p_return_status := hr_multi_message.get_return_status_disable;
440 hr_utility.set_location(' Leaving:' || l_proc,50);
441 end check_quota;
442
443 procedure check_quota(p_business_group_id in number,
444 p_effective_date in date,
445 p_corp_id in number,
446 p_grade_id in number,
447 p_return_status out nocopy varchar2) is
448
449 l_grade_cur_pop number;
450 l_quota_allowed number;
451 l_already_app number;
452 l_corp_name pqh_corps_definitions.name%type;
453 l_grade_name per_grades.name%type;
454 l_proc varchar2(60) := g_package||'check_quota 2';
455 l_return_status varchar2(1) := 'Y';
456 l_corp_id number;
457 Begin
458 hr_utility.set_location('Entering into '||l_proc, 10);
459
460 select name, corps_definition_id into l_corp_name, l_corp_id
461 from pqh_corps_definitions
462 where ben_pgm_id = p_corp_id;
463
464 if Quota_applicable(l_corp_id,p_effective_date) = 'Y' then
465 l_grade_cur_pop := population_grade(p_corp_id => l_corp_id ,
466 p_grade_id => p_grade_id ,
467 p_business_group_id => p_business_group_id,
468 p_effective_date => p_effective_date);
469 l_quota_allowed := Quota_occupancy (p_corp_id => l_corp_id ,
470 p_grade_id => p_grade_id ,
471 p_business_group_id => p_business_group_id,
472 p_effective_date => p_effective_date);
473
474 select count(elc.elig_per_elctbl_chc_id )
475 into l_already_app
476 from ben_elig_per_elctbl_chc elc,
477 ben_per_in_ler pil,
478 ben_pl_f pl,
479 pqh_corps_definitions corp
480 where elc.approval_status_cd = 'PQH_GSP_A'
481 and pil.per_in_ler_id = elc.per_in_ler_id
482 and pil.per_in_ler_stat_cd = 'STRTD'
483 and corp.corps_definition_id = l_corp_id
484 and elc.pgm_id = corp.ben_pgm_id
485 and pl.mapping_table_name = 'PER_GRADES'
486 and pl.mapping_table_pk_id = p_grade_id
487 and p_effective_date between pl.effective_start_date and pl.effective_end_date
488 and elc.pl_id = pl.pl_id
489 and elc.business_group_id = p_business_group_id;
490
491 select name into l_grade_name
492 from per_grades where
493 grade_id = p_grade_id;
494
495 if l_quota_allowed is not null then
496 if ((l_quota_allowed - (l_grade_cur_pop + l_already_app + 1)) < 0 ) then
497
498 /* fnd_message.set_name('PQH','PQH_FR_CAR_QUOTA_CHK_FAIL');
499 fnd_message.set_token('CORP', l_corp_name);
500 fnd_message.set_token('GRADE', l_grade_name);
501 fnd_message.set_token('EFFDATE',p_effective_date);
502 hr_multi_message.add(); */
503 l_return_status := 'N';
504 end if;
505 end if;
506 end if;
507 p_return_status := l_return_status;
508 hr_utility.set_location('Leaving '||l_proc, 10);
509 End check_quota;
510 End PQH_FR_QUOTA_CHECK;