1 package body pqh_gsp_default as
2 /* $Header: pqgspdef.pkb 120.0 2005/05/29 01:58 appldev noship $ */
3 function get_asg_for_pil(p_per_in_ler_id in number,
4 p_effective_date in date) return number is
5 l_assignment_id number;
6 begin
7 select asg.assignment_id
8 into l_assignment_id
9 from ben_per_in_ler pil,per_all_assignments_f asg
10 where p_effective_date between asg.effective_start_date and asg.effective_end_date
11 and pil.per_in_ler_id = p_per_in_ler_id
12 and pil.person_id = asg.person_id
13 and asg.assignment_type = 'E'
14 and asg.primary_flag ='Y';
15 return l_assignment_id;
16 exception
17 when no_data_found then
18 hr_utility.set_location('no asg for pil person '||p_per_in_ler_id,10);
19 raise ;
20 when others then
21 hr_utility.set_location('issue in getting pil person asg '||p_per_in_ler_id,20);
22 raise;
23 end get_asg_for_pil;
24 procedure get_def_auto_code(p_per_in_ler_id in number,
25 p_effective_date in date,
26 p_return_code out nocopy varchar2,
27 p_electbl_chc_id out nocopy number) is
28 l_error_message varchar2(2000);
29 L_PIL_OVN NUMBER;
30 l_procd_dt date;
31 l_strtd_dt date;
32 l_voidd_dt date;
33 l_Assignment_id Per_All_Assignments_F.Assignment_Id%TYPE;
34 l_legislation_code per_business_groups.legislation_code%type;
35 l_return_status varchar2(1);
36
37 l_ptnl_ler_for_per_id ben_ptnl_ler_for_per.ptnl_ler_for_per_id%type;
38 l_ptnl_ler_for_per_ovn ben_ptnl_ler_for_per.object_version_number%type;
39
40 cursor csr_ptnl_ler_dtls(cp_per_in_ler_id in number)
41 is
42 select ptnl.ptnl_ler_for_per_id, ptnl.object_version_number
43 from ben_per_in_ler per
44 ,ben_ptnl_ler_for_per ptnl
45 where per.per_in_ler_id = cp_per_in_ler_id
46 and per.ptnl_ler_for_per_id = ptnl.ptnl_ler_for_per_id;
47
48 cursor lesgislation_info is
49 Select legislation_code
50 from per_business_groups bg, ben_per_in_ler pil
51 where pil.business_group_id = bg.business_group_id
52 and pil.per_in_ler_id = p_per_in_ler_id;
53 begin
54 -- This routine will be called by benmngle run to determine whether out of electable choice progressions
55 -- any one which is to be marked default or automatic.
56 -- if none is found to be found to be def or auto then electble_chc_id will be null and return_code ='NONE'
57
58 open csr_ptnl_ler_dtls(p_per_in_ler_id);
59 fetch csr_ptnl_ler_dtls
60 into l_ptnl_ler_for_per_id
61 ,l_ptnl_ler_for_per_ovn ;
62 close csr_ptnl_ler_dtls ;
63
64 open lesgislation_info;
65 fetch lesgislation_info into l_legislation_code;
66 close lesgislation_info;
67
68 if l_legislation_code = 'FR' then
69 PQH_FR_CR_PATH_ENGINE_PKG.get_elctbl_chc_career_path (p_per_in_ler_id => p_per_in_ler_id,
70 p_effective_date => p_effective_date,
71 P_Elig_Per_Elctbl_Chc_Id => p_electbl_chc_id,
72 p_return_code => p_return_code,
73 p_return_status => l_return_status);
74 end if;
75
76 hr_utility.set_location('p_return_code'||p_return_code,10);
77 hr_utility.set_location('p_electbl_chc_id'||p_electbl_chc_id,10);
78 hr_utility.set_location('l_return_status'||l_return_status,10);
79
80 if nvl(l_return_status,'N') = 'N' then
81 get_default_progression(p_per_in_ler_id => p_per_in_ler_id,
82 p_effective_date => p_effective_date,
83 p_electbl_chc_id => p_electbl_chc_id,
84 p_return_code => p_return_code,
85 p_error_message => l_error_message);
86 if p_electbl_chc_id is null then
87 L_PIL_OVN := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'BEN_PER_IN_LER',
88 p_key_column_name => 'PER_IN_LER_ID',
89 p_key_column_value => p_per_in_ler_id);
90 Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
91 (P_PER_IN_LER_ID => P_PER_IN_LER_ID
92 ,P_PER_IN_LER_STAT_CD => 'VOIDD'
93 ,P_PROCD_DT => l_procd_dt
94 ,P_STRTD_DT => l_strtd_dt
95 ,P_VOIDD_DT => l_voidd_dt
96 ,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
97 ,P_EFFECTIVE_DATE => P_Effective_Date);
98
99 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
100 (p_ptnl_ler_for_per_id => l_PTNL_LER_FOR_PER_ID
101 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
102 ,p_voidd_dt => p_effective_date
103 ,p_object_version_number => l_PTNL_LER_FOR_PER_OVN
104 ,p_effective_date => p_effective_date);
105
106 end if;
107 end if;
108 Exception
109 When others then
110 l_Assignment_id := get_asg_for_pil(P_PER_IN_LER_ID, P_Effective_Date);
111
112 Pqh_Gsp_process_Log.Log_process_Dtls
113 (P_Master_txn_Id => l_Assignment_id
114 ,P_Txn_Id => l_Assignment_id
115 ,p_module_cd => 'PQH_GSP_DFLT_ENRL'
116 ,p_message_type_cd => 'E'
117 ,p_message_text => Nvl(fnd_Message.Get,sqlerrm)
118 ,P_Effective_Date => P_Effective_Date);
119
120 L_PIL_OVN := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'BEN_PER_IN_LER',
121 p_key_column_name => 'PER_IN_LER_ID',
122 p_key_column_value => p_per_in_ler_id);
123
124 Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
125 (P_PER_IN_LER_ID => P_PER_IN_LER_ID
126 ,P_PER_IN_LER_STAT_CD => 'VOIDD'
127 ,P_PROCD_DT => l_procd_dt
128 ,P_STRTD_DT => l_strtd_dt
129 ,P_VOIDD_DT => l_voidd_dt
130 ,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
131 ,P_EFFECTIVE_DATE => P_Effective_Date);
132
133 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
134 (p_ptnl_ler_for_per_id => l_PTNL_LER_FOR_PER_ID
135 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
136 ,p_voidd_dt => p_effective_date
137 ,p_object_version_number => l_PTNL_LER_FOR_PER_OVN
138 ,p_effective_date => p_effective_date);
139
140 end get_def_auto_code;
141 function get_oipl_elect(p_per_in_ler_id in number,
142 p_oipl_id in number) return number is
143 l_electbl_chc_id number;
144 begin
145 select epe.ELIG_PER_ELCTBL_CHC_ID
146 into l_electbl_chc_id
147 from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil
148 where pil.per_in_ler_id = epe.per_in_ler_id
149 and pil.per_in_ler_id = p_per_in_ler_id
150 and pil.per_in_ler_stat_cd = 'STRTD'
151 and epe.comp_lvl_cd ='OIPL'
152 and epe.oipl_id = p_oipl_id;
153 hr_utility.set_location('pil oipl elect chc is '||l_electbl_chc_id,10);
154 return l_electbl_chc_id;
155 exception
156 when no_data_found then
157 hr_utility.set_location('no step elect '||p_oipl_id,10);
158 return l_electbl_chc_id;
159 when others then
160 hr_utility.set_location('issue in getting step elect '||p_oipl_id,10);
161 raise;
162 end;
163 function get_pl_elect(p_per_in_ler_id in number,
164 p_pl_id in number) return number is
165 l_electbl_chc_id number;
166 begin
167 select epe.ELIG_PER_ELCTBL_CHC_ID
168 into l_electbl_chc_id
169 from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil
170 where pil.per_in_ler_id = epe.per_in_ler_id
171 and pil.per_in_ler_id = p_per_in_ler_id
172 and pil.per_in_ler_stat_cd = 'STRTD'
173 and epe.comp_lvl_cd ='PLAN'
174 and epe.pl_id = p_pl_id;
175 hr_utility.set_location('pil pl elect chc is '||l_electbl_chc_id,10);
176 return l_electbl_chc_id;
177 exception
178 when no_data_found then
179 hr_utility.set_location('no plan elect '||p_pl_id,10);
180 return l_electbl_chc_id;
181 when others then
182 hr_utility.set_location('issue in getting plan elect '||p_pl_id,10);
183 raise;
184 end;
185 procedure get_electbl_chc(p_per_in_ler_id in number,
186 p_effective_date in date,
187 p_grade_id in number,
188 p_step_id in number,
189 p_electbl_chc_id out nocopy number) is
190 l_oipl_id number;
191 l_pl_id number;
192 begin
193 hr_utility.set_location('inside get elect chc',10);
194 if p_step_id is not null then
195 l_oipl_id := pqh_gsp_hr_to_stage.get_oipl_for_step
196 (p_step_id => p_step_id,
197 p_effective_date => p_effective_date);
198 hr_utility.set_location('oipl id is '||l_oipl_id,20);
199 end if;
200 if p_grade_id is not null then
201 l_pl_id := pqh_gsp_hr_to_stage.get_plan_for_grade
202 (p_grade_id => p_grade_id,
203 p_effective_date => p_effective_date);
204 hr_utility.set_location('pl id is '||l_pl_id,30);
205 end if;
206 if l_pl_id is null and l_oipl_id is null then
207 hr_utility.set_location('issue in getting plan and oipl '||l_pl_id,40);
208 else
209 if l_oipl_id is null then
210 p_electbl_chc_id := get_pl_elect(p_per_in_ler_id => p_per_in_ler_id,
211 p_pl_id => l_pl_id);
212 hr_utility.set_location('pl elect chc is '||p_electbl_chc_id,50);
213 else
214 p_electbl_chc_id := get_oipl_elect(p_per_in_ler_id => p_per_in_ler_id,
215 p_oipl_id => l_oipl_id);
216 hr_utility.set_location('oipl elect chc is '||p_electbl_chc_id,60);
217 end if;
218 end if;
219 end get_electbl_chc;
220 procedure get_step_seq(p_step_id in number,
221 p_effective_date in date,
222 p_step_seq out nocopy number,
223 p_grade_spine_id out nocopy number) is
224 begin
225 select sequence,grade_spine_id
226 into p_step_seq,p_grade_spine_id
227 from per_spinal_point_steps_f
228 where p_effective_date between effective_start_date and effective_end_date
229 and step_id = p_step_id;
230 hr_utility.set_location('step seq # is '||p_step_seq,10);
231 exception
232 when others then
233 hr_utility.set_location('issue in getting step seq '||p_step_id,12);
234 raise;
235 end get_step_seq;
236 procedure get_result_step(p_step_id in number,
237 p_effective_date in date,
238 p_future_step_id in number,
239 p_ceiling_step_id in number,
240 p_called_from in varchar2 default 'SP',
241 p_num_incr in number,
242 p_steps_left out nocopy number,
243 p_next_step_id out nocopy number) is
244 l_step_seq number;
245 l_grade_spine_id number;
246 l_fut_grade_spine_id number;
247 l_max_grade_spine_id number;
248 l_dest_step_seq number;
249 l_max_step_seq number;
250 l_fut_step_seq number;
251 l_Incr Number := 1;
252
253 Cursor Step(P_Grade_Spine_Id in Number, P_Seq In Number) is
254 Select Sequence, Step_Id
255 From Per_Spinal_Point_steps_F a
256 Where Grade_Spine_id = P_Grade_Spine_id
257 and P_Effective_Date Between Effective_Start_Date and effective_End_Date
258 and Sequence > P_Seq
259 Order By Sequence asc;
260
261 begin
262 -- this procedure will return the step which will add current step level
263 -- to num_incr and give us the step to which person can be progressed
264 hr_utility.set_location('inside get_result_step'||p_step_id,10);
265 get_step_seq(p_step_id => p_step_id,
266 p_effective_date => p_effective_date,
267 p_step_seq => l_dest_step_seq,
268 p_grade_spine_id => l_grade_spine_id);
269 hr_utility.set_location('inside get_result_step'|| l_grade_spine_id,20);
270 hr_utility.set_location('curr step seq # is '|| l_dest_step_seq,30);
271 if p_future_step_id is not null then
272 get_step_seq(p_step_id => p_future_step_id,
273 p_effective_date => p_effective_date,
274 p_step_seq => l_fut_step_seq,
275 p_grade_spine_id => l_fut_grade_spine_id);
276 End If;
277 get_step_seq(p_step_id => p_ceiling_step_id,
278 p_effective_date => p_effective_date,
279 p_step_seq => l_max_step_seq,
280 p_grade_spine_id => l_max_grade_spine_id);
281 hr_utility.set_location('Max Grade Spine'|| l_Max_grade_spine_id,40);
282 hr_utility.set_location('Max step seq # is '|| l_max_step_seq,50);
283 For Step_rec in Step(l_Grade_spine_Id, l_dest_step_seq) Loop
284 hr_utility.set_location(' Inside Step Loop ', 60);
285
286 l_dest_step_seq := Step_Rec.Sequence;
287 p_next_step_id := Step_Rec.Step_Id;
288 hr_utility.set_location('Next Step '|| p_next_step_id,70);
289 hr_utility.set_location('Dest Step Seq '|| l_dest_step_seq,80);
290 if l_dest_step_seq >l_max_step_seq then
291 hr_utility.set_location('Inside Max Step IF',90);
292 if p_called_from = 'GSP' then
293 hr_utility.set_location('steps left to use ',85);
294 -- p_steps_left := l_dest_step_seq - l_max_step_seq;
295 p_steps_left := Nvl(P_Num_Incr,1) - l_incr;
296 Return;
297 else
298 hr_utility.set_location('max step is marked next',90);
299 l_dest_step_seq := l_max_step_seq;
300 p_next_step_id := p_ceiling_step_id;
301 Return;
302 end if;
303 end if;
304 if l_dest_step_seq > l_fut_step_seq and l_fut_grade_spine_id = l_grade_spine_id then
305 hr_utility.set_location('future step is marked next',50);
306 l_dest_step_seq := l_fut_step_seq;
307 p_next_step_id := Step_Rec.Step_Id;
308 Return;
309 end if;
310 If l_Incr >= Nvl(P_Num_Incr,1) Then
311 hr_utility.set_location(' Incr == Return',100);
312 Return;
313 Else
314 l_Incr := L_Incr + 1;
315 End If;
316 End Loop;
317 -- l_dest_step_seq := l_step_seq + nvl(p_num_incr,1);
318 If P_next_step_id is NULL and p_called_from = 'SP' then
319 fnd_message.set_name('PQH','PQH_GSP_LAST_STEP');
320 fnd_message.raise_error;
321 End If;
322 hr_utility.set_location(' Next Step ' || p_next_step_id ,110);
323 If p_next_step_id is NULL Then
324 p_steps_left := Nvl(P_Num_Incr,1);
325 End If;
326 /* if p_steps_left is null then
327 begin
328 select step_id
329 into p_next_step_id
330 from per_spinal_point_steps_f
331 where p_effective_date between effective_start_date and effective_end_date
332 and grade_spine_id = l_grade_spine_id
333 and sequence = l_dest_step_seq;
334 exception
335 when others then
336 hr_utility.set_location('issue in getting step '||l_dest_step_seq,100);
337 raise;
338 end;
339 hr_utility.set_location('next step is'||p_next_step_id,120);
340 else
341 hr_utility.set_location('left step '||p_steps_left,130);
342 end if; */
343 end get_result_step;
344
345 procedure step_progression(p_effective_date in date,
346 p_step_id in number,
347 p_num_incr in number,
348 p_ceiling_step_id in number,
349 p_future_step_id in number,
350 p_next_step_id out nocopy number) is
351 l_continue boolean := TRUE;
352 l_steps_left number;
353 begin
354 if p_step_id is null then
355 hr_utility.set_location('emp not on step'||p_step_id,5);
356 l_continue := FALSE;
357 fnd_message.set_name('PQH','PQH_GSP_NO_STEP');
358 fnd_message.raise_error;
359 elsif p_step_id = p_ceiling_step_id then
360 hr_utility.set_location('emp on ceiling no step prog'||p_ceiling_step_id,5);
361 l_continue := FALSE;
362 fnd_message.set_name('PQH','PQH_GSP_CIEL_STEP');
363 fnd_message.raise_error;
364 else
365 hr_utility.set_location('step id is '||p_step_id,10);
366 hr_utility.set_location('# of incr for person are '||p_num_incr,20);
367 end if;
368 if l_continue then
369 get_result_step(p_step_id => p_step_id,
370 p_effective_date => p_effective_date,
371 p_future_step_id => p_future_step_id,
372 p_ceiling_step_id => p_ceiling_step_id,
373 p_num_incr => p_num_incr,
374 p_called_from => 'SP',
375 p_next_step_id => p_next_step_id,
376 p_steps_left => l_steps_left);
377 hr_utility.set_location('next step id is '||p_next_step_id,30);
378 end if;
379 end step_progression;
380 function is_grade_in_gl(p_grade_id in number,
381 p_gl_id in number,
382 p_effective_date in date) return number is
383 l_ordr_num number;
384 l_pl_id number;
385 begin
386 -- grade should be mapped to a plan which should be linked to a pgm as plip
387 l_pl_id := pqh_gsp_hr_to_stage.get_plan_for_grade
388 (p_grade_id => p_grade_id,
389 p_effective_date => p_effective_date);
390 if l_pl_id is not null then
391 hr_utility.set_location('pl id is '||l_pl_id,10);
392 begin
393 select ordr_num
394 into l_ordr_num
395 from ben_plip_f
396 where pgm_id = p_gl_id
397 and p_effective_date between effective_start_date and effective_end_date
398 and pl_id = l_pl_id;
399 hr_utility.set_location('ordr num is '||l_ordr_num,20);
400 return l_ordr_num;
401 exception
402 when no_data_found then
403 hr_utility.set_location('plan is not in GL'||l_pl_id,10);
404 return l_ordr_num;
405 when others then
406 hr_utility.set_location('issue in getting plips'||l_pl_id,10);
407 raise;
408 end;
409 else
410 return l_ordr_num;
411 end if;
412 end is_grade_in_gl;
413 function is_next_step_higher(p_cur_step_id in number,
414 p_next_step_id in number,
415 p_effective_date in date) return varchar2 is
416 l_cur_step_seq number;
417 l_cur_grade_spine_id number;
418 l_next_step_seq number;
419 l_next_grade_spine_id number;
420 begin
421 -- the idea of this function is to check whether current step is higher than next step
422 -- if both the steps belong to the grade spine then only we will be returning something
423 -- otherwise
424 hr_utility.set_location('checking step ',10);
425 if p_cur_step_id = p_next_step_id then
426 hr_utility.set_location('same step being compared',15);
427 return 'SAME';
428 else
429 hr_utility.set_location('different steps ',18);
430 get_step_seq(p_step_id => p_cur_step_id,
431 p_effective_date => p_effective_date,
432 p_step_seq => l_cur_step_seq,
433 p_grade_spine_id => l_cur_grade_spine_id);
434 hr_utility.set_location('cur step seq is '||l_cur_step_seq,20);
435 hr_utility.set_location('cur step GS is '||l_cur_grade_spine_id,25);
436 get_step_seq(p_step_id => p_next_step_id,
437 p_effective_date => p_effective_date,
438 p_step_seq => l_next_step_seq,
439 p_grade_spine_id => l_next_grade_spine_id);
440 hr_utility.set_location('next step seq is '||l_next_step_seq,30);
441 hr_utility.set_location('next step GS is '||l_next_grade_spine_id,35);
442 if l_cur_grade_spine_id <> l_next_grade_spine_id then
443 hr_utility.set_location('steps grade spine not same',40);
444 return 'NO';
445 else
446 hr_utility.set_location('same grade spine ',50);
447 if l_next_step_seq > l_cur_step_seq then
448 hr_utility.set_location('next step is higher',60);
449 return 'YES';
450 else
451 hr_utility.set_location('cur step is higher',70);
452 return 'NO_LOWER';
453 end if;
454 end if;
455 end if;
456 end is_next_step_higher;
457 procedure next_asg_grade_step(p_assignment_id in number,
458 p_cur_asg_eed in date,
459 p_future_grade_id out nocopy number,
460 p_future_step_id out nocopy number) is
461 l_asg_check_date date;
462 cursor csr_asgs is
463 select grade_id
464 from per_all_assignments_f
465 where assignment_id = p_assignment_id
466 and l_asg_check_date between effective_start_date and effective_end_date;
467 cursor csr_spps is
468 select step_id
469 from per_spinal_point_placements_f
470 where assignment_id = p_assignment_id
471 and l_asg_check_date between effective_start_date and effective_end_date;
472 begin
473 hr_utility.set_location('inside next_asg_grade_step',10);
474 l_asg_check_date := p_cur_asg_eed + 1;
475 open csr_asgs;
476 fetch csr_asgs into p_future_grade_id;
477 if csr_asgs%found then
478 hr_utility.set_location('future grd found '||p_future_grade_id,20);
479 close csr_asgs;
480 open csr_spps;
481 fetch csr_spps into p_future_step_id;
482 if csr_spps%found then
483 hr_utility.set_location('future step found '||p_future_step_id,40);
484 close csr_spps;
485 else
486 hr_utility.set_location('no step found ',50);
487 close csr_spps;
488 end if;
489 else
490 hr_utility.set_location('no grade found ',60);
491 close csr_asgs;
492 end if;
493 end next_asg_grade_step;
494 function get_default_step(p_next_grade_id in number,
495 p_assignment_id in number,
496 p_dflt_step_cd in varchar2,
497 p_effective_date in date) return number is
498 l_next_step_id number;
499 l_cur_sal number;
500 begin
501 -- next garde is passed and we have to get step which meets the code
502 hr_utility.set_location('inside get_default_step',10);
503 if p_dflt_step_cd = 'MINSTEP' then
504 hr_utility.set_location('1st step is required',20);
505 l_next_step_id := get_next_step(p_grade_id => p_next_grade_id,
506 p_effective_date => p_effective_date);
507 hr_utility.set_location('1st step is '||l_next_step_id,30);
508 elsif p_dflt_step_cd = 'MINSALINCR' then
509 hr_utility.set_location('min sal incr step is required',40);
510 l_cur_sal := get_annual_sal(p_assignment_id => p_assignment_id,
511 p_effective_date => p_effective_date);
512 hr_utility.set_location('cur sal is '||l_cur_sal,45);
513 l_next_step_id := get_lowest_sal_incr_step(p_cur_sal => l_cur_sal,
514 p_grade_id => p_next_grade_id,
515 p_effective_date => p_effective_date,
516 P_Assignment_id => P_Assignment_id);
517 hr_utility.set_location('step is '||l_next_step_id,50);
518 elsif p_dflt_step_cd = 'NOSTEP' then
519 hr_utility.set_location('no step is required,pass null',60);
520 l_next_step_id := get_next_step(p_grade_id => p_next_grade_id,
521 p_effective_date => p_effective_date);
522 If l_next_step_id is NOT NULL then
523 l_next_step_id := NULL;
524 Else
525 Fnd_message.set_name('PQH','PQH_GSP_LAST_STEP');
526 fnd_message.raise_error;
527 End If;
528
529 else
530 hr_utility.set_location('invalid step_cd ',70);
531 l_next_step_id := -1;
532 end if;
533 return l_next_step_id;
534 end get_default_step;
535 function get_sal_for_step(p_step_id in number,
536 p_effective_date in date) return number is
537 l_point_id number;
538 l_option_id number;
539 l_step_rate number;
540 begin
541 hr_utility.set_location('inside get_sal_for_step',10);
542 l_point_id := pqh_gsp_hr_to_stage.get_point_for_step
543 (p_step_id => p_step_id,
544 p_effective_date => p_effective_date);
545 hr_utility.set_location('point is '||l_point_id,20);
546 if l_point_id is not null then
547 l_option_id := pqh_gsp_hr_to_stage.get_opt_for_point
548 (p_point_id => l_point_id,
549 p_effective_date => p_effective_date);
550 hr_utility.set_location('opt is '||l_option_id,30);
551 if l_option_id is not null then
552 hr_utility.set_location('going for point rate '||l_option_id,35);
553 pqh_gsp_hr_to_stage.get_point_rate_values
554 (p_effective_date => p_effective_date,
555 p_opt_id => l_option_id,
556 p_point_id => l_point_id,
557 p_point_value => l_step_rate);
558 hr_utility.set_location('step sal is '||l_step_rate,40);
559 else
560 hr_utility.set_location('opt is null ',50);
561 end if;
562 else
563 hr_utility.set_location('point is null ',60);
564 end if;
565 return l_step_rate;
566 end get_sal_for_step;
567 function get_next_oipl(p_oipl_id in number,
568 p_effective_date in date) return number is
569 l_step_id number;
570 l_next_step_id number;
571 l_next_oipl_id number;
572 begin
573 hr_utility.set_location('getting next oipl'||p_oipl_id,10);
574 l_step_id := pqh_gsp_hr_to_stage.get_step_for_oipl(p_oipl_id => p_oipl_id,
575 p_effective_date => p_effective_date);
576 hr_utility.set_location('step is'||l_step_id,20);
577 l_next_step_id := get_next_step(p_step_id => l_step_id,
578 p_effective_date => p_effective_date);
579 hr_utility.set_location('next step is'||l_next_step_id,30);
580 if nvl(l_next_step_id,0) > 0 then
581 l_next_oipl_id := pqh_gsp_hr_to_stage.get_oipl_for_step(p_step_id => l_next_step_id,
582 p_effective_date => p_effective_date);
583 else
584 l_next_oipl_id := l_next_step_id;
585 end if;
586 hr_utility.set_location('next oipl is'||l_next_oipl_id,40);
587 return l_next_oipl_id;
588 end get_next_oipl;
589 function get_next_step(p_grade_id in number default null,
590 p_step_id in number default null,
591 p_effective_date in date) return number is
592 l_seq number;
593 l_grade_spine_id number;
594 l_next_seq number;
595 l_next_step_id number;
596 cursor csr_steps is
597 select step_id,sequence
598 from per_spinal_point_steps_f
599 where grade_spine_id = l_grade_spine_id
600 and p_effective_date between effective_start_date and effective_end_date
601 and sequence > l_seq
602 order by sequence;
603 begin
604 --
605 -- this routine will return null if the step is the topmost
606 -- (-1) if the step is not valid
607 -- else next step for the grade will be returned
608 --
609 hr_utility.set_location('getting next step',10);
610 if p_step_id is null and p_grade_id is null then
611 hr_utility.set_location('grade and step passed null'||p_grade_id,12);
612 return l_next_step_id;
613 end if;
614 if p_step_id is null then
615 hr_utility.set_location('getting 1st step of grade'||p_grade_id,20);
616 begin
617 select grade_spine_id
618 into l_grade_spine_id
619 from per_grade_spines_f
620 where grade_id = p_grade_id
621 and p_effective_date between effective_start_date and effective_end_date;
622 hr_utility.set_location('grade spine is '||l_grade_spine_id,25);
623 l_seq := 0;
624 exception
625 when no_data_found then
626 hr_utility.set_location('grade doesnot have spine',30);
627 return l_next_step_id;
628 when others then
629 hr_utility.set_location('issues in getting gradespine ',35);
630 raise;
631 end;
632 else
633 hr_utility.set_location('p_step_id is '||p_step_id,36);
634 get_step_seq(p_step_id => p_step_id,
635 p_effective_date => p_effective_date,
636 p_step_seq => l_seq,
637 p_grade_spine_id => l_grade_spine_id);
638 end if;
639 hr_utility.set_location('seq is '||l_seq,40);
640 begin
641 open csr_steps;
642 fetch csr_steps into l_next_step_id,l_next_seq;
643 if csr_steps%notfound then
644 close csr_steps;
645 hr_utility.set_location('current step was on top',50);
646 else
647 close csr_steps;
648 hr_utility.set_location('next step found '||l_next_step_id,60);
649 hr_utility.set_location('next step seq '||l_next_seq,70);
650 end if;
651 exception
652 when others then
653 hr_utility.set_location('issues in getting step seq',80);
654 raise;
655 end;
656 hr_utility.set_location('next step is'||l_next_step_id,100);
657 return l_next_step_id;
658 end get_next_step;
659 function get_next_grade(p_grade_id in number,
660 p_gl_id in number,
661 p_effective_date in date) return number is
662 l_pl_id number;
663 l_next_pl_id number;
664 l_next_grade_id number;
665 begin
666 hr_utility.set_location('getting next grade'||p_grade_id,10);
667 l_pl_id := pqh_gsp_hr_to_stage.get_plan_for_grade
668 (p_grade_id => p_grade_id,
669 p_effective_date => p_effective_date);
670 hr_utility.set_location('plan is'||l_pl_id,20);
671 if l_pl_id is null then
672 hr_utility.set_location('grade not linked '||p_grade_id,20);
673 return l_next_grade_id;
674 else
675 l_next_pl_id := get_next_plan(p_pl_id => l_pl_id,
676 p_gl_id => p_gl_id,
677 p_effective_date => p_effective_date);
678 hr_utility.set_location('next plan is'||l_next_pl_id,30);
679 if nvl(l_next_pl_id,0) > 0 then
680 l_next_grade_id := pqh_gsp_hr_to_stage.get_grade_for_plan
681 (p_plan_id => l_next_pl_id,
682 p_effective_date => p_effective_date);
683 else
684 l_next_grade_id := l_next_pl_id;
685 end if;
686 end if;
687 hr_utility.set_location('next grade is'||l_next_grade_id,40);
688 return l_next_grade_id;
689 end get_next_grade;
690 function get_next_plan(p_pl_id in number,
691 p_gl_id in number,
692 p_effective_date in date) return number is
693 l_ordr_num number;
694 l_plip_id number;
695 l_next_ordr_num number;
696 l_next_pl_id number;
697 cursor csr_plips is
698 select pl_id,ordr_num
699 from ben_plip_f
700 where pgm_id = p_gl_id
701 and p_effective_date between effective_start_date and effective_end_date
702 and plip_stat_cd ='A'
703 and ordr_num > l_ordr_num
704 order by ordr_num;
705 begin
706 --
707 -- this routine will return null if the plan is the topmost
708 -- (-1) if the pl is not in GL
709 -- else next plan will be returned
710 --
711 hr_utility.set_location('getting next plan',10);
712 begin
713 select plip_id,ordr_num
714 into l_plip_id,l_ordr_num
715 from ben_plip_f
716 where pgm_id = p_gl_id
717 and p_effective_date between effective_start_date and effective_end_date
718 and plip_stat_cd ='A'
719 and pl_id = p_pl_id;
720 hr_utility.set_location('ordr num is '||l_ordr_num,20);
721 exception
722 when no_data_found then
723 hr_utility.set_location('plan is not linked to pgm',30);
724 l_next_pl_id := -1;
725 when others then
726 hr_utility.set_location('issues in getting plan ordr_num',10);
727 raise;
728 end;
729 hr_utility.set_location('ordr num is '||l_ordr_num,40);
730 begin
731 open csr_plips;
732 fetch csr_plips into l_next_pl_id,l_next_ordr_num;
733 if csr_plips%notfound then
734 close csr_plips;
735 hr_utility.set_location('current pl was on top',50);
736 else
737 close csr_plips;
738 hr_utility.set_location('next pl found '||l_next_pl_id,60);
739 hr_utility.set_location('next pl ordr_num '||l_next_ordr_num,70);
740 end if;
741 exception
742 when others then
743 hr_utility.set_location('issues in getting plan ordr_num',10);
744 raise;
745 end;
746 hr_utility.set_location('next plan is'||l_next_pl_id,100);
747 return l_next_pl_id;
748 end get_next_plan;
749 procedure get_default_progression(p_per_in_ler_id in number,
750 p_effective_date in date,
751 p_electbl_chc_id out nocopy number,
752 p_return_code out nocopy varchar2,
753 p_error_message out nocopy varchar2) is
754 l_assignment_id number;
755 cursor csr_asg_rec is
756 select effective_start_date,effective_end_date,grade_ladder_pgm_id,grade_id,
757 special_ceiling_step_id,business_group_id
758 from per_all_assignments_f
759 where assignment_id = l_assignment_id
760 and p_effective_date between effective_start_date and effective_end_date;
761 l_asg_esd date;
762 l_asg_eed date;
763 l_gl_id number;
764 l_bg_id number;
765 l_plan_id number;
766 l_def_gl number;
767 l_grade_id number;
768 l_spl_ceiling_id number;
769 l_prog_style_cd varchar2(30);
770 l_post_style_cd varchar2(30);
771 l_gl_name ben_pgm_f.name%type;
772 l_dflt_step_cd ben_pgm_f.dflt_step_cd%type;
773 l_dflt_step_rl ben_pgm_f.dflt_step_rl%type;
774 l_continue boolean := TRUE;
775 l_scale_id number;
776 l_scale_ovn number;
777 l_scale_name per_parent_spines.name%type;
778 l_grade_spine_ovn number;
779 l_grade_spine_id number;
780 l_ceiling_step_id number;
781 l_step_id number;
782 l_next_grade_id number;
783 l_next_step_id number;
784 l_eot date := to_date('31/12/4712','dd/mm/RRRR');
785 l_future_grade_id number;
786 l_future_step_id number;
787 l_next_step_higher_cd varchar2(30);
788 l_num_incr number;
789 l_curr_grd_gl_ordr_num number;
790 l_electbl_chc_id number;
791 l_starting_step number;
792 begin
793 hr_utility.set_location('inside def prog'||p_per_in_ler_id,5);
794 l_assignment_id := get_asg_for_pil(p_per_in_ler_id => p_per_in_ler_id,
795 p_effective_date => p_effective_date);
796 hr_utility.set_location('asg is '||l_assignment_id,6);
797 open csr_asg_rec;
798 fetch csr_asg_rec into l_asg_esd,l_asg_eed,l_gl_id,l_grade_id,l_spl_ceiling_id,l_bg_id;
799 if csr_asg_rec%notfound then
800 close csr_asg_rec;
801 l_continue := FALSE;
802 hr_utility.set_location('Assignment invalid'||l_assignment_id,10);
803 else
804 close csr_asg_rec;
805 hr_utility.set_location('Assignment is valid'||l_assignment_id,11);
806 if l_grade_id is not null then
807 hr_utility.set_location('grade is'||l_grade_id,20);
808 l_plan_id := pqh_gsp_hr_to_stage.get_plan_for_grade
809 (p_grade_id => l_grade_id,
810 p_effective_date => p_effective_date);
811 hr_utility.set_location('plan is'||l_plan_id,30);
812 if l_plan_id is null then
813 l_continue := FALSE;
814 fnd_message.set_name('PQH','PQH_GSP_PLN_NOTLNKD_TO_GRD');
815 fnd_message.raise_error;
816 end if;
817 else
818 hr_utility.set_location('grade is'||l_grade_id,40);
819 l_continue := FALSE;
820 fnd_message.set_name('PQH','PQH_GSP_GRDNOTLNKD_ASSGT');
821 fnd_message.raise_error;
822 end if;
823 if l_continue then
824 if l_gl_id is not null then
825 hr_utility.set_location('Assignment on GL'||l_gl_id,50);
826 else
827 hr_utility.set_location('Assignment not on GL'||l_assignment_id,60);
828 -- is assignment on default GL.
829 l_def_gl := get_default_gl(p_effective_date => p_effective_date,
830 p_business_group_id => l_bg_id);
831 if l_def_gl is not null then
832 hr_utility.set_location('def GL'||l_def_gl,70);
833 l_curr_grd_gl_ordr_num := is_grade_in_gl(p_grade_id => l_grade_id,
834 p_gl_id => l_def_gl,
835 p_effective_date => p_effective_date);
836 if l_curr_grd_gl_ordr_num is not null then
837 hr_utility.set_location('asg on def GL',80);
838 l_gl_id := l_def_gl;
839 else
840 l_continue := FALSE;
841 fnd_message.set_name('PQH','PQH_GSP_GRD_ORDNUM_NOTFND');
842 fnd_message.raise_error;
843 end if;
844 else
845 hr_utility.set_location('def GL not there',80);
846 l_continue := FALSE;
847 fnd_message.set_name('PQH','PQH_GSP_NO_GRDLDR');
848 fnd_message.raise_error;
849 end if;
850 end if;
851 end if;
852 if l_continue then
853 get_gl_details(p_gl_id => l_gl_id,
854 p_effective_date => p_effective_date,
855 p_prog_style_cd => l_prog_style_cd,
856 p_post_style_cd => l_post_style_cd,
857 p_gl_name => l_gl_name,
858 p_dflt_step_cd => l_dflt_step_cd,
859 p_dflt_step_rl => l_dflt_step_rl);
860 hr_utility.set_location('Assignment on GL'||l_gl_name,55);
861 if l_prog_style_cd in ('PQH_GSP_SP','PQH_GSP_GSP') then
862 get_emp_step_placement(p_assignment_id => l_assignment_id,
863 p_effective_date => p_effective_date,
864 p_emp_step_id => l_step_id,
865 p_num_incr => l_num_incr);
866 hr_utility.set_location('step id is '||l_step_id,55);
867 pqh_gsp_hr_to_stage.get_grd_scale_details
868 (p_grade_id => l_grade_id,
869 p_effective_date => p_effective_date,
870 p_scale_id => l_scale_id,
871 p_ceiling_step_id => l_ceiling_step_id,
872 p_grade_spine_ovn => l_grade_spine_ovn,
873 p_grade_spine_id => l_grade_spine_id ,
874 p_scale_ovn => l_scale_ovn,
875 p_scale_name => l_scale_name,
876 p_starting_step => l_starting_step);
877 hr_utility.set_location('grade is linked to scale'||l_scale_name,95);
878 hr_utility.set_location('ceiling step id is '||l_ceiling_step_id,96);
879 if l_spl_ceiling_id is not null then
880 l_ceiling_step_id := l_spl_ceiling_id;
881 hr_utility.set_location('ceiling step id is '||l_ceiling_step_id,100);
882 end if;
883 end if;
884 if l_asg_eed < l_eot then
885 hr_utility.set_location('get future placement ',150);
886 next_asg_grade_step(p_assignment_id => l_assignment_id,
887 p_cur_asg_eed => l_asg_eed,
888 p_future_grade_id => l_future_grade_id,
889 p_future_step_id => l_future_step_id);
890 hr_utility.set_location('future grade is '||l_future_grade_id,160);
891 hr_utility.set_location('future step is '||l_future_step_id,170);
892 else
893 hr_utility.set_location('asg is till eot',180);
894 end if;
895 hr_utility.set_location('progr style is '||l_prog_style_cd,190);
896 if l_prog_style_cd ='PQH_GSP_GSP' then
897 grd_step_progression_result(p_grade_id => l_grade_id,
898 p_step_id => l_step_id,
899 p_gl_id => l_gl_id,
900 p_assignment_id => l_assignment_id,
901 p_effective_date => p_effective_date,
902 p_ceiling_step_id => l_ceiling_step_id,
903 p_num_incr => l_num_incr,
904 p_dflt_step_cd => l_dflt_step_cd,
905 p_future_step_id => l_future_step_id,
906 p_next_grade_id => l_next_grade_id,
907 p_next_step_id => l_next_step_id);
908 elsif l_prog_style_cd = 'PQH_GSP_GP' then
909 grade_progression(p_assignment_id => l_assignment_id,
910 p_effective_date => p_effective_date,
911 p_grade_id => l_grade_id,
912 p_gl_id => l_gl_id,
913 p_next_grade_id => l_next_grade_id);
914 elsif l_prog_style_cd = 'PQH_GSP_SP' then
915 step_progression(p_effective_date => p_effective_date,
916 p_step_id => l_step_id,
917 p_num_incr => l_num_incr,
918 p_ceiling_step_id => l_ceiling_step_id,
919 p_future_step_id => l_future_step_id,
920 p_next_step_id => l_next_step_id);
921 else
922 hr_utility.set_location('invalid prog_style'||l_prog_style_cd,260);
923 l_continue := FALSE;
924 fnd_message.set_name('PQH','PQH_GSP_PRGSTYLE_NOT_SET');
925 fnd_message.raise_error;
926 end if;
927 hr_utility.set_location('cur grade is'||l_grade_id,260);
928 hr_utility.set_location('cur step is'||l_step_id,260);
929 hr_utility.set_location('next grade is'||l_next_grade_id,260);
930 hr_utility.set_location('next step is'||l_next_step_id,260);
931 hr_utility.set_location('future grade is'||l_future_grade_id,260);
932 hr_utility.set_location('future step is'||l_future_step_id,260);
933 end if;
934 if l_next_step_id is null and l_next_grade_id is null then
935 l_continue := False;
936 end if;
937 if l_continue then
938 hr_utility.set_location('checking elc chc',260);
939 get_electbl_chc(p_per_in_ler_id => p_per_in_ler_id,
940 p_effective_date => p_effective_date,
941 p_grade_id => l_next_grade_id,
942 p_step_id => l_next_step_id,
943 p_electbl_chc_id => l_electbl_chc_id);
944 if l_electbl_chc_id is not null then
945 if l_post_style_cd ='A' then
946 p_return_code := 'A';
947 p_electbl_chc_id := l_electbl_chc_id;
948 elsif l_post_style_cd ='E' then
949 p_return_code := 'D';
950 p_electbl_chc_id := l_electbl_chc_id;
951 else
952 hr_utility.set_location('invalid posting style'||l_post_style_cd,260);
953 end if;
954 else
955 hr_utility.set_location('no electbl chc found ',260);
956 fnd_message.set_name('PQH','PQH_GSP_EMP_NOT_ELGBL');
957 fnd_message.raise_error;
958 l_continue := False;
959 end if;
960 end if;
961 end if;
962 end get_default_progression;
963
964 procedure grade_progression(p_assignment_id in number,
965 p_effective_date in date,
966 p_grade_id in number,
967 p_gl_id in number,
968 p_next_grade_id out nocopy number) is
969 begin
970 p_next_grade_id := get_next_grade(p_grade_id => p_grade_id,
971 p_gl_id => p_gl_id,
972 p_effective_date => p_effective_date);
973 if p_next_grade_id is null then
974 hr_utility.set_location('topmost grade',260);
975 fnd_message.set_name('PQH','PQH_GSP_LAST_GRADE');
976 fnd_message.raise_error;
977 elsif p_next_grade_id = -1 then
978 hr_utility.set_location('invalid grd for GL',270);
979 else
980 hr_utility.set_location('next grade is'||p_next_grade_id,260);
981 end if;
982 end;
983
984 procedure get_emp_step_placement(p_assignment_id in number,
985 p_effective_date in date,
986 p_emp_step_id out nocopy number,
987 p_num_incr out nocopy number) is
988 begin
989 hr_utility.set_location('assignment is'||p_assignment_id,10);
990 select step_id,increment_number
991 into p_emp_step_id,p_num_incr
992 from per_spinal_point_placements_f
993 where assignment_id = p_assignment_id
994 and p_effective_date between effective_start_date and effective_end_date;
995 hr_utility.set_location('step is'||p_emp_step_id,15);
996 exception
997 when no_data_found then
998 hr_utility.set_location('assignment doesnot have step'||p_assignment_id,20);
999 when others then
1000 hr_utility.set_location('issues in getting assignment step'||p_assignment_id,30);
1001 raise;
1002 end get_emp_step_placement;
1003 function get_default_gl(p_effective_date in date,
1004 p_business_group_id in number) return number is
1005 l_gl_id number;
1006 begin
1007 hr_utility.set_location('bg is'||p_business_group_id,5);
1008 select pgm_id
1009 into l_gl_id
1010 from ben_pgm_f
1011 where p_effective_date between effective_start_date and effective_end_date
1012 and pgm_stat_cd ='A' -- active program
1013 and pgm_typ_cd ='GSP' -- context should be GSP
1014 and dflt_pgm_flag = 'Y' -- default
1015 and business_group_id = p_business_group_id;
1016 hr_utility.set_location('def gl is'||l_gl_id,10);
1017 return l_gl_id;
1018 exception
1019 when no_data_found then
1020 hr_utility.set_location('no pgm exists matching crit',20);
1021 return l_gl_id;
1022 when too_many_rows then
1023 hr_utility.set_location('more than 1 pgm marked dflt ',25);
1024 raise;
1025 when others then
1026 hr_utility.set_location('issues in getting def gl ',30);
1027 raise;
1028 end get_default_gl;
1029 procedure get_gl_details(p_gl_id in number,
1030 p_effective_date in date,
1031 p_prog_style_cd out nocopy varchar2,
1032 p_post_style_cd out nocopy varchar2,
1033 p_gl_name out nocopy varchar2,
1034 p_dflt_step_cd out nocopy varchar2,
1035 p_dflt_step_rl out nocopy varchar2) is
1036 begin
1037 select enrt_mthd_cd,name,dflt_step_cd,dflt_step_rl
1038 into p_post_style_cd,p_gl_name,p_dflt_step_cd,p_dflt_step_rl
1039 from ben_pgm_f
1040 where pgm_id = p_gl_id
1041 and pgm_stat_cd ='A' -- program should be active
1042 and p_effective_date between effective_start_date and effective_end_date
1043 and pgm_typ_cd ='GSP' ;-- should be Grade ladder
1044 If p_dflt_step_cd in ('MINSALINCR','MINSTEP','NOSTEP') then
1045 p_prog_style_cd := 'PQH_GSP_GSP';
1046 Else
1047 p_prog_style_cd := p_dflt_step_cd;
1048 End If;
1049 exception
1050 when no_data_found then
1051 hr_utility.set_location('no pgm exists ',10);
1052 raise;
1053 when others then
1054 hr_utility.set_location('issues in getting gl detls',20);
1055 raise;
1056 end get_gl_details;
1057
1058 function get_cur_sal(p_assignment_id in number,
1059 p_effective_date in date) return number Is
1060 L_Cur_Sal Per_pay_Proposals.PROPOSED_SALARY_N%TYPE;
1061 l_input_value_id pay_input_values_f.Input_Value_id%TYPE;
1062 Cursor Sal is
1063 select pev.screen_entry_value
1064 from pay_element_entries_f pee
1065 ,pay_element_entry_values_f pev
1066 where pev.element_entry_id = pee.element_entry_id
1067 and p_Effective_Date between pev.Effective_Start_Date and pev.Effective_End_Date
1068 and pee.assignment_id = p_assignment_id
1069 and p_Effective_Date between pee.Effective_Start_Date and pee.Effective_End_Date
1070 and pev.Input_Value_id = l_input_value_id;
1071 Cursor Pay_Bases_Element is
1072 Select input_value_id
1073 From Per_Pay_Bases ppb,
1074 Per_All_Assignments_f paf
1075 Where paf.Assignment_Id = p_Assignment_Id
1076 and p_Effective_Date Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1077 and paf.pay_basis_id = ppb.pay_basis_id;
1078 Cursor GrdLdr_Element is
1079 Select DFLT_INPUT_VALUE_ID
1080 from Ben_Pgm_f pgm,
1081 Per_All_Assignments_f paf
1082 Where paf.Assignment_Id = p_Assignment_Id
1083 and p_Effective_Date
1084 Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1085 and paf.GRADE_LADDER_PGM_ID = pgm.pgm_id
1086 and p_Effective_Date
1087 Between pgm.Effective_Start_date and pgm.Effective_End_Date;
1088 Begin
1089 Open Pay_Bases_Element;
1090 Fetch Pay_Bases_Element into l_input_Value_id;
1091 Close Pay_Bases_Element;
1092 If l_input_Value_id is NULL Then
1093 Open GrdLdr_Element;
1094 Fetch GrdLdr_Element into l_input_Value_id;
1095 Close GrdLdr_Element;
1096 End If;
1097 if l_Input_Value_id is Not NULL Then
1098 Open Sal;
1099 Fetch Sal into L_Cur_Sal;
1100 Close Sal;
1101 Else
1102 l_Cur_Sal := 0;
1103 End If;
1104 Return L_Cur_Sal;
1105 End Get_Cur_Sal;
1106 procedure grd_step_progression_result(p_grade_id in number,
1107 p_step_id in number,
1108 p_gl_id in number,
1109 p_assignment_id in number,
1110 p_effective_date in date,
1111 p_ceiling_step_id in number,
1112 p_dflt_step_cd in varchar2,
1113 p_num_incr in number,
1114 p_future_step_id in number,
1115 p_next_grade_id out nocopy number,
1116 p_next_step_id out nocopy number) is
1117 l_next_step_id number;
1118 l_next_step_higher_cd varchar2(30);
1119 l_steps_left number;
1120 l_continue boolean := TRUE;
1121 begin
1122 -- we will try for step progression, if you reach the ceiling and still some point left, we will go for GSP
1123 if p_grade_id is null then
1124 hr_utility.set_location('emp doesnot have grade'||p_grade_id,10);
1125 l_continue := false;
1126 fnd_message.set_name('PQH','PQH_GSP_GRDNOTLNKD_ASSGT');
1127 fnd_message.raise_error;
1128 end if;
1129 if p_step_id is null then
1130 hr_utility.set_location('emp doesnot have step'||p_step_id,10);
1131 l_continue := false;
1132 fnd_message.set_name('PQH','PQH_GSP_NO_STEP');
1133 fnd_message.raise_error;
1134 end if;
1135 if l_continue then
1136 get_result_step(p_step_id => p_step_id,
1137 p_effective_date => p_effective_date,
1138 p_future_step_id => p_future_step_id,
1139 p_ceiling_step_id => p_ceiling_step_id,
1140 p_num_incr => p_num_incr,
1141 p_called_from => 'GSP',
1142 p_next_step_id => l_next_step_id,
1143 p_steps_left => l_steps_left);
1144 hr_utility.set_location('next step id is '||l_next_step_id,10);
1145 hr_utility.set_location('steps left is '||l_steps_left,20);
1146 if l_steps_left is not null then
1147 hr_utility.set_location('steps left is '||l_steps_left,20);
1148 hr_utility.set_location('current grade id is '||p_grade_id,110);
1149 p_next_grade_id := get_next_grade(p_grade_id => p_grade_id,
1150 p_gl_id => p_gl_id,
1151 p_effective_date => p_effective_date);
1152 hr_utility.set_location('next grade is '||p_next_grade_id,125);
1153 l_next_step_id := get_default_step(p_next_grade_id => p_next_grade_id,
1154 p_dflt_step_cd => p_dflt_step_cd,
1155 p_assignment_id => p_assignment_id,
1156 p_effective_date => p_effective_date);
1157 hr_utility.set_location('next step is '||l_next_step_id,140);
1158 if l_next_step_id is not null then
1159 if p_future_step_id is not null then
1160 hr_utility.set_location('chk next step with future step',142);
1161 l_next_step_higher_cd := is_next_step_higher(p_cur_step_id => l_next_step_id,
1162 p_next_step_id => p_future_step_id,
1163 p_effective_date => p_effective_date);
1164 if l_next_step_higher_cd in ('YES','SAME','NO') then
1165 hr_utility.set_location('future step is higher',142);
1166 p_next_step_id := l_next_step_id;
1167 elsif l_next_step_higher_cd ='NO_LOWER' then
1168 hr_utility.set_location('future step is lower than next step',142);
1169 p_next_step_id := p_future_step_id;
1170 else
1171 hr_utility.set_location('different code returned ',143);
1172 l_continue := FALSE;
1173 end if;
1174 hr_utility.set_location('identified step for progression is'||l_next_step_id,144);
1175 hr_utility.set_location('identified grade for progression is'||p_next_grade_id,144);
1176 else
1177 hr_utility.set_location('next step is '||l_next_step_id,144);
1178 p_next_step_id := l_next_step_id;
1179 end if;
1180 else
1181 If p_dflt_step_cd = 'NOSTEP' Then
1182 Return;
1183 Else
1184 hr_utility.set_location('next step is '||l_next_step_id,144);
1185 l_continue := FALSE;
1186 fnd_message.set_name('PQH','PQH_GSP_LAST_STEP');
1187 fnd_message.raise_error;
1188 End If;
1189 end if;
1190 else
1191 hr_utility.set_location('step progr was sufficient',200);
1192 p_next_step_id := l_next_step_id;
1193 end if;
1194 end if;
1195 end grd_step_progression_result;
1196 function get_lowest_sal_incr_step(p_cur_sal in number,
1197 p_grade_id in number,
1198 p_effective_date in date,
1199 P_Assignment_id in Number) return number is
1200 l_min_incr_sal number;
1201 l_min_incr_step_id number;
1202 l_next_sal number;
1203
1204 Cursor Csr_Step is
1205 Select pqh_gsp_hr_to_stage.get_step_for_oipl(Elctbl.oipl_id, p_effective_date) Step_Id
1206 from Per_all_Assignments_F Asgt,
1207 Ben_Per_In_Ler Pler,
1208 Ben_Ler_F Ler,
1209 Ben_Elig_Per_Elctbl_Chc ELctbl,
1210 Ben_Enrt_Rt Rt
1211 Where Asgt.Assignment_Id = P_Assignment_id
1212 and P_Effective_Date
1213 Between Asgt.Effective_Start_Date
1214 and Asgt.Effective_End_Date
1215 and Pler.Person_id = Asgt.Person_Id
1216 and Pler.Per_In_Ler_Stat_cd = 'STRTD'
1217 and Ler.Ler_id = Pler.Ler_id
1218 and ler.typ_Cd = 'GSP'
1219 and P_Effective_Date
1220 Between ler.Effective_Start_Date
1221 and Ler.Effective_End_Date
1222 and Elctbl.Per_In_Ler_id = Pler.Per_In_ler_id
1223 and Elctbl.Pl_Id = pqh_gsp_hr_to_stage.get_plan_for_grade
1224 (p_grade_id, p_effective_date)
1225 and Elctbl.Oipl_id is NOT NULL
1226 and Rt.ELig_Per_Elctbl_Chc_Id = Elctbl.Elig_Per_Elctbl_Chc_id
1227 and Nvl(rt.ann_Val,0) > P_Cur_Sal
1228 Order by Rt.ann_Val Asc;
1229 begin
1230
1231 hr_utility.set_location('p_cur_sal ' ||p_cur_sal ,99);
1232 hr_utility.set_location('p_grade_id '|| p_grade_id ,199);
1233 hr_utility.set_location('p_effective_date' ||p_effective_date ,299);
1234 hr_utility.set_location('P_Assignment_id '|| P_Assignment_id ,399);
1235
1236 Open Csr_Step;
1237 Fetch Csr_Step into l_Min_Incr_Step_Id;
1238 Close Csr_Step;
1239
1240 If p_grade_id is NOT NULL and l_Min_Incr_Step_Id is NULL then
1241 fnd_message.set_name('PQH','PQH_GSP_EMP_NOT_ELGBL');
1242 fnd_message.raise_error;
1243 End If;
1244 hr_utility.set_location('min sal is '||l_min_incr_sal,85);
1245 hr_utility.set_location('min step is '||l_min_incr_step_id,90);
1246 return l_min_incr_step_id;
1247 end get_lowest_sal_incr_step;
1248 function get_annual_sal(p_assignment_id in number,
1249 p_effective_date in date) return number Is
1250
1251 L_Cur_Sal Per_pay_Proposals.PROPOSED_SALARY_N%TYPE;
1252 l_input_value_id pay_input_values_f.Input_Value_id%TYPE;
1253 l_annualization_factor Per_pay_bases.pay_annualization_factor%TYPE;
1254 L_Payroll_name pay_all_payrolls_f.Payroll_name%TYPE;
1255
1256 Cursor Sal is
1257 select pev.screen_entry_value*l_annualization_factor
1258 from pay_element_entries_f pee
1259 ,pay_element_entry_values_f pev
1260 where pev.element_entry_id = pee.element_entry_id
1261 and p_Effective_Date between pev.Effective_Start_Date and pev.Effective_End_Date
1262 and pee.assignment_id = p_assignment_id
1263 and p_Effective_Date between pee.Effective_Start_Date and pee.Effective_End_Date
1264 and pev.Input_Value_id = l_input_value_id;
1265
1266 Cursor Pay_Bases_Element is
1267 Select input_value_id,pay_annualization_factor
1268 From Per_Pay_Bases ppb,
1269 Per_All_Assignments_f paf
1270 Where paf.Assignment_Id = p_Assignment_Id
1271 and p_Effective_Date Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1272 and paf.pay_basis_id = ppb.pay_basis_id;
1273
1274 Cursor GrdLdr_Element is
1275 Select DFLT_INPUT_VALUE_ID
1276 from Ben_Pgm_f pgm,
1277 Per_All_Assignments_f paf
1278 Where paf.Assignment_Id = p_Assignment_Id
1279 and p_Effective_Date
1280 Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1281 and paf.GRADE_LADDER_PGM_ID = pgm.pgm_id
1282 and p_Effective_Date
1283 Between pgm.Effective_Start_date and pgm.Effective_End_Date;
1284
1285 Begin
1286 Open Pay_Bases_Element;
1287 Fetch Pay_Bases_Element into l_input_Value_id,l_annualization_factor;
1288 Close Pay_Bases_Element;
1289 If l_input_Value_id is NULL Then
1290 Open GrdLdr_Element;
1291 Fetch GrdLdr_Element into l_input_Value_id;
1292 Close GrdLdr_Element;
1293 per_pay_proposals_populate.get_payroll(p_assignment_id
1294 ,p_effective_date
1295 ,l_Payroll_name
1296 ,l_annualization_factor);
1297
1298 End If;
1299 if l_Input_Value_id is Not NULL Then
1300 Open Sal;
1301 Fetch Sal into L_Cur_Sal;
1302 Close Sal;
1303 Else
1304 l_Cur_Sal := 0;
1305 End If;
1306 Return L_Cur_Sal;
1307 End Get_annual_Sal;
1308 end pqh_gsp_default;
[Home] [Help]