[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_SYNC_COMPENSATION_OBJ
Source
1 Package Body pqh_gsp_sync_compensation_obj as
2 /* $Header: pqgspwiz.pkb 120.2 2007/11/13 11:10:12 deyadav noship $ */
3 --
4 Function delete_plan_for_grade(p_grade_id in number) RETURN varchar2 is
5 Cursor Pl is
6 Select PL_Id, Effective_Start_Date, Effective_End_Date, Object_version_number
7 From Ben_Pl_F
8 Where MAPPING_TABLE_NAME = 'PER_GRADES'
9 and MAPPING_TABLE_PK_ID = p_grade_id
10 and Hr_general.Effective_Date
11 between Effective_Start_Date and Effective_End_Date;
12
13 Cursor Plip (P_Pl_id in Number) Is
14 Select plip_Id, Effective_Start_Date, Effective_End_Date, Object_version_number
15 From Ben_plip_F
16 Where Pl_Id = P_Pl_Id
17 and Hr_general.Effective_Date
18 between Effective_Start_Date and Effective_End_Date;
19
20 L_plip_Ovn_No Ben_Plip_F.Object_Version_Number%TYPE;
21 L_Plan_Ovn_No Ben_Pl_F.Object_Version_Number%TYPE;
22 L_Effective_Start_Date Ben_Pl_F.Effective_Start_date%TYPE;
23 L_Effective_End_Date Ben_Pl_F.Effective_End_date%TYPE;
24 l_datetrack_mode varchar2(30);
25 l_eot date;
26 Begin
27 --
28 l_eot := to_date('31/12/4712','dd/mm/RRRR');
29 -- 1. Fetch the plan for the grade id
30 -- 2. Fetch the programs to which the plan is added.
31 -- 3. Delete plan in program records.
32 -- 4. Delete the Plan
33 For Pl_rec in Pl loop
34 hr_utility.set_location('deleting plips for pl '||Pl_Rec.Pl_Id,10);
35 For Plip_Rec in Plip(Pl_Rec.Pl_Id) Loop
36 hr_utility.set_location('deleting plip '||PLip_rec.Plip_Id,15);
37 L_plip_Ovn_No := Plip_Rec.Object_version_number;
38 if plip_rec.effective_end_date <> l_eot then
39 hr_utility.set_location('not on last row ',19);
40 l_datetrack_mode := 'FUTURE_CHANGE';
41 else
42 hr_utility.set_location('on last row ',18);
43 l_datetrack_mode := 'DELETE';
44 end if;
45 begin
46 ben_Plan_in_Program_api.delete_Plan_in_Program
47 (p_plip_id => PLip_rec.Plip_Id
48 ,p_effective_start_date => L_Effective_Start_Date
49 ,p_effective_end_date => L_Effective_End_Date
50 ,p_object_version_number => L_plip_Ovn_No
51 ,p_effective_date => Hr_general.Effective_Date
52 ,p_datetrack_mode => l_datetrack_mode);
53 exception
54 when others then
55 hr_utility.set_location('issues in deleting plip ',30);
56 Return 'FAILURE';
57 End;
58 End Loop;
59 hr_utility.set_location('deleting pl '||Pl_Rec.Pl_Id,10);
60 L_plan_Ovn_No := Pl_Rec.Object_version_number;
61 if pl_rec.effective_end_date <> l_eot then
62 hr_utility.set_location('not on last row ',19);
63 l_datetrack_mode := 'FUTURE_CHANGE';
64 else
65 hr_utility.set_location('on last row ',18);
66 l_datetrack_mode := 'DELETE';
67 end if;
68 ben_plan_api.delete_Plan
69 (p_pl_id => Pl_Rec.Pl_Id
70 ,p_effective_start_date => L_Effective_Start_Date
71 ,p_effective_end_date => L_Effective_End_Date
72 ,p_object_version_number => L_Plan_Ovn_No
73 ,p_effective_date => Hr_general.Effective_Date
74 ,p_datetrack_mode => l_datetrack_mode);
75 End Loop;
76 --
77 Return 'SUCCESS';
78 Exception
79 When Others Then
80 hr_utility.set_location('issues in deleting pl ',30);
81 Return 'FAILURE';
82 End;
83 --
84 --------------------------------------------------------------------------------------
85 --
86 Function delete_std_rt_for_grade_rule(p_rate_type in varchar2 ,
87 p_grade_or_spinal_point_id in number,
88 p_grade_rule_id in number,
89 p_effective_date in date,
90 p_datetrack_mode in varchar2)
91 RETURN varchar2 is
92
93 l_Business_group_id Ben_Acty_base_Rt_F.business_group_id%TYPE;
94
95 Cursor csr_business_group_id
96 is
97 select business_group_id
98 from pay_grade_rules_f
99 where grade_rule_id =p_grade_rule_id
100 AND grade_or_spinal_point_id = p_grade_or_spinal_point_id
101 AND p_effective_date between effective_start_date
102 and effective_end_date;
103
104
105 Cursor Rates Is
106 Select ACTY_BASE_RT_ID, Effective_Start_Date,
107 Effective_End_Date, Object_version_number
108 From Ben_Acty_base_Rt_F
109 Where PAY_RATE_GRADE_RULE_ID = p_grade_rule_id
110 and p_effective_date
111 Between Effective_Start_Date and Effective_End_Date
112 and business_group_id = l_business_group_id;
113
114 Cursor Csr_Var_rt (P_Acty_Base_Rt_Id IN Number) is
115 Select Acty_Vrbl_Rt_Id,
116 Object_Version_Number
117 From Ben_Acty_Vrbl_Rt_F
118 Where Acty_base_rt_Id = P_Acty_Base_rt_Id
119 and P_Effective_Date
120 Between Effective_Start_Date
121 and Effective_End_Date;
122
123 L_Effective_Start_Date Ben_Acty_base_Rt_F.Effective_Start_Date%TYPE;
124 L_Effective_End_Date Ben_Acty_base_Rt_F.Effective_End_Date%TYPE;
125 l_Object_version_Number Ben_Acty_base_Rt_F.Object_version_Number%TYPE;
126
127 L_Vrbl_Esd Ben_Acty_Vrbl_Rt_F.Effective_Start_Date%TYPE;
128 L_Vrbl_Eed Ben_Acty_Vrbl_Rt_F.Effective_End_Date%TYPE;
129 l_Vrbl_ovn Ben_Acty_Vrbl_Rt_F.Object_version_Number%TYPE;
130
131 Begin
132 --
133 -- 1. Find the Standard rate which references the grade rule.
134 -- 2. Delete the Standard rate.
135
136 hr_utility.set_location('grade rule id is '||p_grade_rule_id,10);
137 hr_utility.set_location('rate type is '||p_rate_type,11);
138 hr_utility.set_location('grade /point id is '||p_grade_or_spinal_point_id,12);
139 hr_utility.set_location('effdt is '||to_char(p_effective_date,'dd-mm-RRRR:hh-mm-ss'),13);
140
141 OPEN csr_business_group_id;
142 FETCH csr_business_group_id INTO l_Business_group_id;
143 CLOSE csr_business_group_id;
144
145 For Rates_Rec in Rates Loop
146
147 For Var_Rec in Csr_Var_Rt (Rates_Rec.ACTY_BASE_RT_ID)
148 Loop
149
150 hr_utility.set_location('Delete VAR Rate' || Var_Rec.ACTY_VRBL_RT_ID ,15);
151 If Var_Rec.Acty_Vrbl_rt_Id is NOT NULL then
152
153 l_Vrbl_ovn := Var_rec.Object_Version_Number;
154
155 BEN_ACTY_VRBL_RATE_API.DELETE_ACTY_VRBL_RATE
156 (P_ACTY_VRBL_RT_ID => Var_Rec.ACTY_VRBL_RT_ID
157 ,P_EFFECTIVE_START_DATE => L_Vrbl_esd
158 ,P_EFFECTIVE_END_DATE => l_Vrbl_Eed
159 ,P_OBJECT_VERSION_NUMBER => l_Vrbl_Ovn
160 ,P_EFFECTIVE_DATE => P_Effective_Date
161 ,P_DATETRACK_MODE => p_datetrack_mode);
162
163 End If;
164 End Loop;
165
166 hr_utility.set_location('abr to be deleted is '||Rates_Rec.ACTY_BASE_RT_ID,20);
167 l_Object_version_Number := Rates_Rec.Object_version_number;
168 ben_acty_base_rate_api.delete_acty_base_rate
169 (p_acty_base_rt_id => Rates_Rec.ACTY_BASE_RT_ID
170 ,p_effective_start_date => L_Effective_Start_Date
171 ,p_effective_end_date => L_Effective_End_Date
172 ,p_object_version_number => l_Object_version_Number
173 ,p_effective_date => p_effective_date
174 ,p_datetrack_mode => p_datetrack_mode);
175
176 End Loop;
177 --
178 Return 'SUCCESS';
179 --
180 Exception
181 When Others Then
182 hr_utility.set_location('issues in deleting abr ',30);
183 Return 'FAILURE';
184 End;
185 --
186 --------------------------------------------------------------------------------------
187 --
188 Function delete_option_for_point(p_spinal_point_id in number)
189 RETURN varchar2 is
190 l_effective_date date;
191 Cursor Opts Is
192 Select Opt_id , Effective_Start_Date,
193 Effective_End_Date, Object_version_number
194 From Ben_Opt_F
195 Where MAPPING_TABLE_NAME = 'PER_SPINAL_POINTS'
196 and MAPPING_TABLE_PK_ID = p_spinal_point_id
197 and l_Effective_Date
198 between Effective_Start_Date and Effective_End_Date;
199
200 Cursor csr_Pl_Opt_Type (P_Opt_Id IN Number) is
201 Select Pl_typ_opt_Typ_Id,
202 Effective_Start_Date,
203 Effective_End_Date,
204 Object_Version_Number
205 from Ben_Pl_Typ_Opt_Typ_F
206 Where Opt_Id = P_Opt_id
207 and Pl_Typ_Opt_Typ_Cd = 'GSP';
208
209 L_Ovn_No Ben_Opt_F.Object_version_Number%TYPE;
210 L_Effective_Start_Date Ben_Opt_F.Effective_Start_Date%TYPE;
211 L_Effective_End_Date Ben_Opt_F.Effective_End_Date%TYPE;
212 l_datetrack_mode varchar2(30);
213 l_eot date;
214
215 l_Pl_Typ_Opt_Typ_Id Ben_Pl_Typ_Opt_Typ_F.Pl_Typ_Opt_Typ_Id%TYPE;
216 l_opt_typ_Esd Ben_Pl_Typ_Opt_Typ_F.Effective_Start_Date%TYPE;
217 l_Opt_Typ_Eed Ben_Pl_Typ_Opt_Typ_F.Effective_End_Date%TYPE;
218 l_Opt_typ_Ovn Ben_Pl_Typ_Opt_Typ_F.Object_Version_Number%TYPE;
219 Begin
220 --
221 l_effective_date := Hr_general.Effective_Date;
222 l_eot := to_date('31/12/4712','dd/mm/RRRR');
223 --
224 --1. Find the option corresponding to the spinal point.
225 --2. Delete the option. No need to delete option in plan records
226 -- as it will not be able to delete the spinal point if steps are there.
227
228 hr_utility.set_location('spinal point is '||p_spinal_point_id,15);
229 hr_utility.set_location('effdt is '||to_char(l_effective_date,'dd-mm-RRRR:hh-mm-ss'),18);
230 For Opts_Rec in Opts Loop
231 hr_utility.set_location('opt to be deleted is'||Opts_Rec.Opt_id,20);
232 L_ovn_No := opts_rec.Object_version_number;
233
234 /* if opts_rec.effective_end_date <> l_eot then
235 hr_utility.set_location('not on last row ',19);
236 l_datetrack_mode := 'FUTURE_CHANGE';
237 else
238 hr_utility.set_location('on last row ',18);
239 l_datetrack_mode := 'DELETE';
240 end if; */
241 l_datetrack_mode := 'ZAP';
242 Open csr_Pl_Opt_Type(Opts_Rec.Opt_id);
243 Fetch csr_Pl_Opt_Type into l_Pl_Typ_Opt_Typ_Id, l_opt_typ_Esd, l_Opt_Typ_Eed, l_Opt_typ_Ovn;
244 Close csr_Pl_Opt_Type;
245
246 hr_utility.set_location('PLOptTyp to be deleted is'||l_Pl_Typ_Opt_Typ_Id,30);
247
248 ben_plan_type_option_type_api.Delete_Plan_Type_Option_Type
249 (P_PL_TYP_OPT_TYP_ID => l_Pl_Typ_Opt_Typ_Id
250 ,P_EFFECTIVE_START_DATE => l_opt_typ_Esd
251 ,P_EFFECTIVE_END_DATE => l_Opt_Typ_Eed
252 ,P_OBJECT_VERSION_NUMBER => l_Opt_typ_Ovn
253 ,P_EFFECTIVE_DATE => l_Effective_Date
254 ,P_DATETRACK_MODE => l_datetrack_mode);
255
256 hr_utility.set_location('PLOptTyp Deleted',40);
257
258 ben_option_definition_api.delete_option_definition
259 (p_opt_id => Opts_Rec.Opt_id
260 ,p_effective_start_date => L_Effective_Start_Date
261 ,p_effective_end_date => L_Effective_End_Date
262 ,p_object_version_number => L_ovn_No
263 ,p_effective_date => l_Effective_Date
264 ,p_datetrack_mode => l_datetrack_mode);
265 End Loop;
266 Return 'SUCCESS';
267 --
268 Exception
269 When Others Then
270 hr_utility.set_location('issues in deleting opt ',30);
271 Return 'FAILURE';
272 End;
273 --
274 --------------------------------------------------------------------------------
275 --
276 Function delete_oipl_for_step(p_grade_id in number,
277 p_spinal_point_id in number,
278 p_step_id in number,
279 p_effective_date in date,
280 p_datetrack_mode in varchar2)
281 RETURN varchar2 is
282
283 Cursor Oipl Is
284 Select Oipl.Oipl_id , Oipl.Effective_Start_Date,
285 Oipl.Effective_End_Date, Oipl.Object_version_number
286 From Ben_Oipl_F Oipl, Ben_Opt_F opt, Ben_pl_F Pl
287 Where Opt.MAPPING_TABLE_NAME = 'PER_SPINAL_POINTS'
288 and Opt.MAPPING_TABLE_PK_ID = p_spinal_point_id
289 and p_effective_date
290 between Opt.Effective_Start_Date and Opt.Effective_End_Date
291 and Pl.MAPPING_TABLE_NAME = 'PER_GRADES'
292 and Pl.MAPPING_TABLE_PK_ID = p_grade_id
293 and p_effective_date
294 between Pl.Effective_Start_Date and Pl.Effective_End_Date
295 and Opt.Opt_id = Oipl.Opt_id
296 and Pl.Pl_Id = Oipl.Pl_Id
297 and p_effective_date
298 between Oipl.Effective_Start_Date and Oipl.Effective_End_Date;
299
300 L_Effective_Start_Date Ben_Oipl_F.Effective_Start_Date%TYPE;
301 L_Effective_End_Date Ben_Oipl_F.Effective_End_Date%TYPE;
302 l_Object_version_Number Ben_Oipl_F.Object_version_Number%TYPE;
303
304 Begin
305 --
306 -- 1. Find the option in plan record corresponding to the step.
307 -- 2. Do not allow deleting the step, if it is the last step in the
308 -- grade and the grade ladder is setup for 'Step' or 'Grade-Step' progression
309 -- 3. Delete the option in plan records.
310
311 hr_utility.set_location('grade is '||p_grade_id,10);
312 hr_utility.set_location('spinal point is '||p_spinal_point_id,15);
313 hr_utility.set_location('effdt is '||to_char(p_effective_date,'dd-mm-RRRR:hh-mi-ss'),18);
314 For Oipl_Rec in Oipl Loop
315 hr_utility.set_location('oipl for deletion is '||Oipl_Rec.Oipl_Id,30);
316 l_Object_version_Number := Oipl_Rec.Object_Version_Number;
317 ben_Option_in_Plan_api.delete_Option_in_Plan
318 (p_oipl_id => Oipl_Rec.Oipl_Id
319 ,p_effective_start_date => L_Effective_Start_Date
320 ,p_effective_end_date => L_Effective_End_Date
321 ,p_object_version_number => l_Object_version_Number
322 ,p_effective_date => p_effective_date
323 ,p_datetrack_mode => p_datetrack_mode);
324 End Loop;
325 Return 'SUCCESS';
326 --
327 Exception
328 When Others Then
329 hr_utility.set_location('issues in deleting oipl ',30);
330 Return 'FAILURE';
331 End;
332
333 --
334 ----------------------------------------------------------------------------------------
335 --
336 function chk_oipl_for_step(p_pl_id in number,
337 p_opt_id in number,
338 p_effective_date in date) return boolean is
339 l_oipl_id number;
340 begin
341 if p_pl_id is not null and p_opt_id is not null then
342 begin
343 select oipl_id
344 into l_oipl_id
345 from ben_oipl_f
346 where pl_id = p_pl_id
347 and opt_id = p_opt_id
348 and p_effective_date between effective_start_date and effective_end_date;
349 hr_utility.set_location('oipl is '||l_oipl_id,30);
350 return true;
351 exception
352 when no_data_found then
353 hr_utility.set_location('invalid oipl for pl'||p_pl_id,100);
354 return false;
355 when others then
356 hr_utility.set_location('issues in selecting oipl detail',120);
357 return false;
358 end;
359 else
360 hr_utility.set_location('either plan or opt is null',150);
361 return false;
362 end if;
363 end chk_oipl_for_step;
364 --
365 ----------------------------------------------------------------------------------------
366 --
367 function get_max_oipl_seq(p_pl_id in number,
368 p_opt_id in number,
369 p_effective_date in date) return number is
370 l_max_seq number;
371 begin
372 if p_pl_id is not null and p_opt_id is not null then
373 begin
374 select max(ordr_num)
375 into l_max_seq
376 from ben_oipl_f
377 where pl_id = p_pl_id
378 and p_effective_date between effective_start_date and effective_end_date;
379 hr_utility.set_location('max seq is '||l_max_seq,10);
380 l_max_seq := nvl(l_max_seq,0) + 1;
381 return l_max_seq;
382 exception
383 when no_data_found then
384 hr_utility.set_location('invalid oipl for pl'||p_pl_id,100);
385 return 1;
386 when others then
387 hr_utility.set_location('issues in selecting oipl detail',120);
388 raise;
389 end;
390 else
391 hr_utility.set_location('either plan or opt is null',150);
392 return 1;
393 end if;
394 end get_max_oipl_seq;
395 --
396 ----------------------------------------------------------------------------------------
397 --
398 ------------------------------------------------------------------------------
399 --
400 Function create_option_for_point(p_spinal_point_id in number,
401 p_pay_scale_name in varchar2,
402 p_business_group_id in number,
403 p_spinal_point_name in varchar2)
404 RETURN varchar2 is
405
406 l_effective_date date;
407
408 Cursor csr_opt_exists is
409 Select opt_id
410 From ben_opt_f
411 Where mapping_table_name = 'PER_SPINAL_POINTS'
412 and mapping_table_pk_id = p_spinal_point_id;
413 --
414 --
415 Cursor get_pl_typ is
416 Select pl_typ_id
417 From ben_pl_typ_f
418 Where opt_typ_cd = 'GSP'
419 and business_group_id = p_business_group_id
420 and l_effective_date between effective_start_date and effective_end_date;
421
422 L_Effective_Start_Date Ben_Opt_F.Effective_Start_Date%TYPE;
423 L_Effective_End_Date Ben_Opt_F.Effective_End_Date%TYPE;
424 l_ovn_no ben_Opt_F.Object_Version_Number%TYPE;
425 L_opt_id ben_opt_F.Opt_Id%TYPE;
426 l_opt_exists varchar2(10);
427 --
428 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
429 l_pl_typ_opt_typ_id ben_pl_typ_opt_typ_f.pl_typ_opt_typ_id%type;
430 l_continue boolean;
431 --
432 Begin
433 --
434 l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id, null);
435 l_opt_exists := 'N';
436 l_continue := true;
437 -- Get the plan type that is to be linked.Assuming that there can be only
438 -- one plan type of option type 'GSP'.
439 --
440 hr_utility.set_location('Entering create_option',5);
441 Open get_pl_typ;
442 Fetch get_pl_typ into l_pl_typ_id;
443 If get_pl_typ%notfound then
444 Close get_pl_typ;
445 hr_utility.set_location('No plan type found ',5);
446 return 'FAILURE';
447 End if;
448 Close get_pl_typ;
449 hr_utility.set_location('plan type found '|| to_char(l_pl_typ_id),5);
450 --
451 -- Check if the option exists for the point
452 --
453 Open csr_opt_exists;
454 Fetch csr_opt_exists into L_opt_id;
455 If csr_opt_exists%notfound then
456 l_opt_exists := 'N';
457 Else
458 l_opt_exists := 'Y';
459 End if;
460 Close csr_opt_exists;
461 -- 1. Generate the name for the option.
462 -- 2. Create the option if it does not already exist for the point.
463 --
464 If l_opt_exists = 'N' then
465 --
466 hr_utility.set_location('Creating option for '|| p_spinal_point_name,5);
467 ben_option_definition_api.create_option_definition
468 (P_OPT_ID => L_opt_id
469 ,P_EFFECTIVE_START_DATE => L_Effective_Start_Date
470 ,P_EFFECTIVE_END_DATE => L_Effective_End_Date
471 ,P_NAME => p_pay_scale_name||':'||p_spinal_point_name
472 ,P_BUSINESS_GROUP_ID => p_Business_Group_id
473 ,P_OBJECT_VERSION_NUMBER => l_ovn_no
474 ,P_MAPPING_TABLE_NAME => 'PER_SPINAL_POINTS'
475 ,P_MAPPING_TABLE_PK_ID => P_Spinal_point_Id
476 ,P_EFFECTIVE_DATE => l_effective_date );
477 --
478 -- Also create plan type option type link.
479 --
480 hr_utility.set_location('plan type option type'|| p_spinal_point_name,5);
481 ben_plan_type_option_type_api.create_plan_type_option_type
482 (
483 p_validate => false
484 ,p_pl_typ_opt_typ_id => l_pl_typ_opt_typ_id
485 ,p_effective_start_date => l_effective_start_date
486 ,p_effective_end_date => l_effective_end_date
487 ,p_pl_typ_opt_typ_cd => 'GSP'
488 ,p_opt_id => l_opt_id
489 ,p_pl_typ_id => l_pl_typ_id
490 ,p_business_group_id => P_Business_Group_id
491 ,p_object_version_number => l_ovn_no
492 ,p_effective_date => l_effective_date
493 );
494
495 End if;
496 --
497 hr_utility.set_location('Leaving create_option',10);
498 Return 'SUCCESS';
499 --
500 End;
501 --
502 Function create_oipl_for_step(p_grade_id in number,
503 p_spinal_point_id in number,
504 p_step_id in number,
505 p_effective_date in date,
506 p_datetrack_mode in varchar2)
507 RETURN varchar2 is
508
509 Cursor Pl is
510 Select PL_Id, Effective_Start_Date, Effective_End_Date,business_group_id
511 From Ben_Pl_F
512 Where MAPPING_TABLE_NAME = 'PER_GRADES'
513 and MAPPING_TABLE_PK_ID = p_grade_id
514 and p_effective_date between Effective_Start_Date and Effective_End_Date;
515
516 cursor point is
517 select psp.spinal_point_id spinal_point_id,psp.spinal_point spinal_point,pps.name scale_name
518 from per_spinal_points psp, per_parent_spines pps
519 where psp.spinal_point_id = p_spinal_point_id
520 and psp.parent_spine_id = pps.parent_spine_id;
521
522 Cursor Opt Is
523 Select opt.Opt_id , opt.Effective_Start_Date,
524 opt.Effective_End_Date,step.sequence ordr_num
525 From Ben_Opt_F opt, Per_Spinal_POint_Steps_F Step
526 Where Opt.MAPPING_TABLE_NAME = 'PER_SPINAL_POINTS'
527 and Opt.MAPPING_TABLE_PK_ID = p_spinal_point_id
528 and p_effective_date
529 between Opt.Effective_Start_Date and Opt.Effective_End_Date
530 and Step.Step_id = P_Step_id
531 and p_effective_date
532 between Step.Effective_Start_Date and Step.Effective_End_Date
533 and Step.Spinal_Point_id = p_spinal_point_id;
534
535 l_Oipl_id Ben_oipl_f.oipl_Id%TYPE;
536 l_effective_Start_Date Ben_Oipl_F.Effective_Start_Date%TYPE;
537 L_Effective_End_Date Ben_Oipl_F.Effective_End_Date%TYPE;
538 l_bg_id Ben_opt_F.Business_Group_id%TYPE;
539 l_ovn_no ben_oipl_f.Object_Version_Number%TYPE;
540 l_oipl_exists boolean;
541 l_max_oipl_seq number;
542 l_continue varchar2(30);
543 Begin
544 --
545 --
546 l_continue := 'SUCCESS';
547 -- Effective Date 1951
548 -- 1. Find the plan and option corresponding to the grade and spinal point.
549 -- 2. Create a option in plan record.
550 --
551 For Pl_Rec in Pl Loop
552 l_bg_id := pl_Rec.Business_group_id;
553 hr_utility.set_location('pl id is'||pl_rec.pl_id,10);
554 for pt_rec in point loop
555 hr_utility.set_location('pt is '||pt_rec.spinal_point_id,15);
556 begin
557 l_continue := create_option_for_point(p_spinal_point_id => pt_rec.spinal_point_id,
558 p_pay_scale_name => pt_rec.scale_name,
559 p_business_group_id => l_bg_id,
560 p_spinal_point_name => pt_rec.spinal_point);
561 exception
562 when others then
563 hr_utility.set_location('issues in creating option',15);
564 raise;
565 End;
566 end loop;
567 if l_continue <> 'SUCCESS' then
568 return 'FAILURE';
569 else
570 For Opt_Rec in OPt Loop
571 hr_utility.set_location('opt id is'||opt_rec.opt_id,20);
572 l_oipl_exists := chk_oipl_for_step(p_pl_id => pl_rec.pl_id,
573 p_opt_id => opt_rec.opt_id,
574 p_effective_date => p_effective_date);
575 if not l_oipl_exists and pl_rec.pl_id is not null and opt_rec.opt_id is not null then
576 hr_utility.set_location('going for oipl create',30);
577 --start bug fix 6239174
578 /* if l_max_oipl_seq is null then
579 l_max_oipl_seq := get_max_oipl_seq(p_pl_id => pl_rec.pl_id,
580 p_opt_id => opt_rec.opt_id,
581 p_effective_date => p_effective_date);
582 else
583 l_max_oipl_seq := l_max_oipl_seq + 1;
584 end if;*/
585 if Opt_Rec.ordr_num is null then
586 l_max_oipl_seq := get_max_oipl_seq(p_pl_id => pl_rec.pl_id,
587 p_opt_id => opt_rec.opt_id,
588 p_effective_date => p_effective_date);
589 else
590 l_max_oipl_seq := Opt_Rec.ordr_num;
591 end if;
592 --end bug fix 6239174
593 hr_utility.set_location('seq is '||l_max_oipl_seq,31);
594 ben_Option_in_Plan_api.create_Option_in_Plan
595 (p_oipl_id => l_Oipl_id
596 ,p_effective_start_date => l_effective_Start_Date
597 ,p_effective_end_date => l_Effective_End_Date
598 ,p_opt_id => Opt_Rec.Opt_id
599 ,p_business_group_id => l_bg_id
600 ,p_pl_id => pl_Rec.Pl_Id
601 ,p_oipl_stat_cd => 'A'
602 ,p_auto_enrt_flag => 'N'
603 ,p_ordr_num => l_max_oipl_seq
604 ,p_object_version_number => l_ovn_no
605 ,p_effective_date => p_effective_date);
606 else
607 hr_utility.set_location('not enough details exists for oipl creation',40);
608 end if;
609 --
610 End Loop;
611 end if;
612 End loop;
613 hr_utility.set_location('going out ',50);
614 Return 'SUCCESS';
615 --
616 Exception
617 When Others Then
618 hr_utility.set_location('issues in creating oipl',40);
619 raise;
620 End;
621 --
622 --
623 END pqh_gsp_sync_compensation_obj;