1 package body pqh_gsp_ben_validations as
2 /* $Header: pqgspben.pkb 120.1 2008/06/04 11:29:54 brsinha noship $ */
3 -- dml_operation can be I(Insert)/ U(Update)/ D(Delete)
4 -- call to this validation routine is to be made after the actual event i.e.
5 -- insert update or delete should already have happened
6 -- we will try to check here that the event has not failed any GSP data
7 -- if any, we will be raising the error.
8
9 g_package Varchar2(30) := 'pqh_gsp_ben_validations.';
10 g_debug boolean := hr_utility.debug_enabled;
11
12 --
13 ---------------------------get_pl_type-----------------------------
14 --
15 Function get_pl_type(p_pl_id In number,
16 p_effective_date In date,
17 p_Business_Group_Id In Number)
18 Return Varchar2 IS
19 l_proc Varchar2(72) := g_package||'get_pl_type';
20 l_type BEN_PL_TYP.OPT_TYP_CD%Type := NULL;
21 l_type_id Number;
22
23 Cursor csr_pl_type_id
24 IS
25 Select pl.Pl_Typ_Id
26 From Ben_PL_F pl
27 Where pl.Pl_Id = p_pl_id
28 And pl.Business_Group_id = p_Business_Group_Id
29 And p_effective_date BETWEEN Pl.effective_start_date
30 And nvl(Pl.effective_end_date,hr_general.end_of_time);
31
32 Cursor csr_pl_type (l_pl_Typ_Id IN Number)
33 IS
34 Select OPT_TYP_CD
35 From BEN_PL_TYP_F Type
36 Where Pl_Typ_Id = l_pl_Typ_Id
37 And Business_Group_id = p_Business_Group_Id
38 And p_effective_date BETWEEN effective_start_date
39 And nvl(effective_end_date,hr_general.end_of_time);
40
41 Begin
42
43 if g_debug then
44 hr_utility.set_location('Entering'||l_proc,5);
45 hr_utility.set_location('p_pl_id'||p_pl_id,10);
46 hr_utility.set_location('p_effective_date'||p_effective_date,15);
47 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
48 end if;
49
50 Open csr_pl_type_id;
51 Fetch csr_pl_type_id into l_type_id;
52 Close csr_pl_type_id;
53
54 if g_debug then
55 hr_utility.set_location('Plan Type Id '||l_type_id,25);
56 end if;
57 If l_type_id IS NOT NULL Then
58 Open csr_pl_type(l_type_id);
59 Fetch csr_pl_type into l_type;
60 Close csr_pl_type;
61 End If;
62
63 if g_debug then
64 hr_utility.set_location('Plan Type '||l_type,35);
65 hr_utility.set_location('Leaving'||l_proc,100);
66 end if;
67 return l_type;
68 Exception
69 When Others then
70 return null;
71
72 End get_pl_type;
73
74 --
75 ---------------------------get_plip_type-----------------------------
76 --
77 Function get_plip_type(p_plip_id In number,
78 p_effective_date In date,
79 p_Business_Group_Id In Number)
80 Return Varchar2 IS
81 l_proc Varchar2(72) := g_package||'get_plip_type';
82 l_pl_id Number;
83 l_type BEN_PL_TYP.OPT_TYP_CD%Type := NULL;
84 l_pgm_id Number;
85 l_is_pgm_type_gsp Varchar2(1) :='N';
86
87 Cursor csr_pl_id
88 IS
89 Select plip.Pl_Id,plip.Pgm_Id
90 From Ben_PLIP_F plip
91 Where plip.Plip_Id = p_plip_id
92 And plip.Business_Group_id = p_Business_Group_Id
93 And p_effective_date BETWEEN Plip.effective_start_date
94 And nvl(Plip.effective_end_date,hr_general.end_of_time);
95
96
97 Begin
98 if g_debug then
99 hr_utility.set_location('Entering'||l_proc,5);
100 hr_utility.set_location('p_plip_id'||p_plip_id,10);
101 hr_utility.set_location('p_effective_date'||p_effective_date,15);
102 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
103 end if;
104
105 Open csr_pl_id;
106 Fetch csr_pl_id into l_pl_id,l_pgm_id;
107 Close csr_pl_id;
108
109 if g_debug then
110 hr_utility.set_location('Plan Id '||l_pl_id,25);
111 hr_utility.set_location('PGM Id'||l_pgm_id,26);
112 end if;
113
114 If l_pgm_id IS NOT NULL Then
115
116 l_is_pgm_type_gsp := is_pgm_type_gsp(p_Pgm_Id => l_pgm_id,
117 p_Business_Group_Id => p_business_group_id,
118 p_Effective_Date => p_effective_date
119 );
120 End If;
121
122 if g_debug then
123 hr_utility.set_location('Is PGM Type is GSP (Y/N)'||l_is_pgm_type_gsp,27);
124 end if;
125
126
127 If l_is_pgm_type_gsp = 'Y' And l_pl_id IS NOT NULL Then
128 l_type := get_pl_type( p_pl_id => l_pl_id,
129 p_effective_date => p_effective_date,
130 p_Business_Group_Id => p_Business_Group_Id);
131 End If;
132
133 if g_debug then
134 hr_utility.set_location('Plan Type '||l_type,35);
135 hr_utility.set_location('Leaving'||l_proc,100);
136 end if;
137 return l_type;
138
139 Exception
140 When Others then
141 return null;
142 if g_debug then
143 hr_utility.set_location('Leaving'||l_proc,100);
144 end if;
145 End get_plip_type;
146
147 --
148 ---------------------------chk_plip_emp_assign-----------------------------
149 --
150 Function chk_plip_emp_assign(p_plip_id In number,
151 p_effective_date In date,
152 p_business_group_id In Number)
153 Return Varchar2 IS
154 /*
155 Purpose : This Function returns 'Y' if emp assignments exists for a PLIP
156 Otherwise returns 'N'
157
158 */
159 l_proc Varchar2(72) := g_package||'chk_plip_emp_assign';
160 l_grade_id Number;
161 l_dummy Char(1);
162 l_plip_type_cd BEN_PL_TYP.OPT_TYP_CD%Type := NULL;
163 l_exists Varchar2(1) :='N';
164
165 Cursor csr_grade_id
166 IS
167 SELECT MAPPING_TABLE_PK_ID -- GRADE_ID
168 FROM BEN_PLIP_F PLIP,
169 BEN_PL_F PL
170 WHERE PLIP.PLIP_ID = p_plip_id
171 And PLIP.PLIP_STAT_CD ='A'
172 AND PLIP.PL_ID =PL.PL_ID
173 AND PL.MAPPING_TABLE_NAME = 'PER_GRADES'
174 AND PL.BUSINESS_GROUP_ID = p_business_group_id
175 AND p_effective_date BETWEEN PLIP.EFFECTIVE_START_DATE
176 And nvl(PLIP.Effective_End_Date, hr_general.end_of_time)
177 AND p_effective_date BETWEEN PL.EFFECTIVE_START_DATE
178 And nvl(PL.Effective_End_Date, hr_general.end_of_time);
179
180 Cursor csr_emp_placemnets (l_grade_id IN NUMBER)
181 IS
182 Select NULL
183 From Per_All_Assignments_F Assgt
184 Where Grade_Id = l_grade_id
185 AND p_Effective_Date BETWEEN Assgt.effective_start_date
186 AND nvl(Assgt.effective_end_date,hr_general.end_of_time)
187 AND Assgt.business_group_id = p_Business_Group_Id;
188
189
190 Begin
191 if g_debug then
192 hr_utility.set_location('Entering'||l_proc,5);
193 hr_utility.set_location('p_plip_id'||p_plip_id,10);
194 hr_utility.set_location('p_effective_date'||p_effective_date,15);
195 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
196 end if;
197
198 -- Check PLIP Rec Type is GSP
199 l_plip_type_cd := get_plip_type( p_plip_id => p_plip_id,
200 p_effective_date => p_effective_date,
201 p_Business_Group_Id => p_Business_Group_Id);
202 if g_debug then
203 hr_utility.set_location('PLIP Type '||l_plip_type_cd,30);
204 end if;
205
206 If Nvl(l_plip_type_cd,'PPP') = 'GSP' Then
207 --
208 --
209 Open csr_grade_id;
210 Fetch csr_grade_id into l_grade_id;
211 Close csr_grade_id;
212 --
213 --
214 if g_debug then
215 hr_utility.set_location('Grade Id '||l_grade_id,40);
216 end if;
217 If l_grade_id IS NOT NULL Then
218 Open csr_emp_placemnets(l_grade_id);
219 Fetch csr_emp_placemnets into l_dummy;
220 If csr_emp_placemnets%Found Then
221 if g_debug then
222 hr_utility.set_location('Emp Placements are exists',50);
223 end if;
224 l_exists := 'Y';
225 End If;
226 Close csr_emp_placemnets;
227 End If;
228 --
229 --
230 End If;
231 if g_debug then
232 hr_utility.set_location('Leaving'||l_proc,100);
233 end if;
234 return l_exists;
235
236 Exception
237 When others then
238 return 'N';
239
240 end chk_plip_emp_assign;
241
242 --
243 ---------------------------chk_opt_has_gsp_type-----------------------------
244 --
245 Function chk_opt_has_gsp_type(p_opt_id In number,
246 p_effective_date In date,
247 p_business_group_id In Number)
248 Return Varchar2 IS
249 /*
250 Purpose : This Function returns 'Y' if Option has GSP Plan Type.
251 Otherwise returns 'N'
252
253 */
254 l_proc varchar2(72) := g_package||'chk_opt_has_gsp_type';
255 l_dummy Char(1);
256 l_type Varchar2(10) :='N';
257
258 Cursor csr_opt_type
259 IS
260 Select NULL
261 From BEN_PL_TYP_OPT_TYP_F
262 Where OPT_ID = p_opt_id
263 AND BUSINESS_GROUP_ID = p_business_group_id
264 AND p_effective_date BETWEEN EFFECTIVE_START_DATE
265 And nvl(Effective_End_Date, hr_general.end_of_time)
266 And PL_TYP_OPT_TYP_CD = 'GSP';
267
268 Begin
269 if g_debug then
270 hr_utility.set_location('Entering'||l_proc,10);
271 hr_utility.set_location('p_opt_id:'||p_opt_id, 15);
272 hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
273 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 25);
274 end if;
275
276
277 Open csr_opt_type;
278 Fetch csr_opt_type into l_dummy;
279 If csr_opt_type%Found Then
280 l_type := 'Y';
281 End If;
282 Close csr_opt_type;
283 if g_debug then
284 hr_utility.set_location('Option has GSP Type (Y/N) :'||l_type, 45);
285 hr_utility.set_location('Leaving:'||l_proc, 100);
286 end if;
287 return l_type;
288 Exception
289 When others then
290 return 'N';
291 End chk_opt_has_gsp_type;
292
293
294 --
295 ---------------------------chk_oipl_has_gsp_type-----------------------------
296 --
297 Function chk_oipl_has_gsp_type(p_oipl_id In number,
298 p_effective_date In date,
299 p_business_group_id In Number)
300 Return Varchar2 IS
301 /*
302 Purpose : This Function returns 'Y' if OIPL has GSP Plan Type.
303 Otherwise returns 'N'
304
305 */
306 l_proc Varchar2(72) := g_package||'chk_oipl_has_gsp_type';
307 l_dummy Char(1);
308 l_type Varchar2(10) :='N';
309 l_opt_id Number;
310
311 Cursor csr_opt_id
312 IS
313 Select Opt_Id
314 From BEN_OIPL_F
315 Where OIPL_ID = p_oipl_id
316 And OIPL_STAT_CD = 'A'
317 AND BUSINESS_GROUP_ID = p_business_group_id
318 AND p_effective_date BETWEEN EFFECTIVE_START_DATE
319 And nvl(Effective_End_Date, hr_general.end_of_time);
320
321 Begin
322 if g_debug then
323 hr_utility.set_location('Entering'||l_proc,10);
324 hr_utility.set_location('p_oipl_id:'||p_oipl_id, 15);
325 hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
326 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 25);
327 end if;
328
329
330 Open csr_opt_id;
331 Fetch csr_opt_id into l_opt_id;
332 Close csr_opt_id;
333
334 if g_debug then
335 hr_utility.set_location('Opt Id:'||l_opt_id, 35);
336 end if;
337
338 If l_opt_id IS NOT NULL Then
339 l_type := chk_opt_has_gsp_type(p_opt_id => l_opt_id ,
340 p_effective_date => p_effective_date,
341 p_business_group_id => p_business_group_id);
342 End If;
343
344 if g_debug then
345 hr_utility.set_location('OIPL has GSP Type (Y/N) :'||l_type, 45);
346 hr_utility.set_location('Leaving:'||l_proc, 100);
347 end if;
348
349 return l_type;
350 Exception
351 When others then
352 return 'N';
353 End chk_oipl_has_gsp_type;
354
355 --
356 ---------------------------get_step_id-----------------------------
357 --
358 Function get_step_id(p_spinal_point_Id In number,
359 p_Grade_Id IN Number,
360 p_effective_date In date,
361 p_business_group_id In Number)
362 Return Number IS
363 /*
364 Purpose : This Function returns 'Y' if OIPL has GSP Plan Type.
365 Otherwise returns 'N'
366
367 */
368 l_proc Varchar2(72) := g_package||'get_step_id';
369 l_step_id Number := NULL;
370
371 Cursor csr_step_id
372 IS
373 Select Step.step_id
374 From per_spinal_points point,
375 per_parent_spines Scale,
376 per_grade_spines_f spine,
377 per_spinal_point_steps_f step
378 Where point.SPINAL_POINT_ID = p_spinal_point_Id
379 And point.Business_Group_Id = p_business_group_id
380 And Scale.PARENT_SPINE_ID = point.PARENT_SPINE_ID
381 And Scale.PARENT_SPINE_ID = spine.PARENT_SPINE_ID
382 And spine.GRADE_SPINE_ID = step.GRADE_SPINE_ID
383 And step.SPINAL_POINT_ID = point.SPINAL_POINT_ID
384 And spine.Grade_Id = p_Grade_Id
385 AND p_effective_date BETWEEN spine.EFFECTIVE_START_DATE
386 And nvl(spine.Effective_End_Date, hr_general.end_of_time)
387 AND p_effective_date BETWEEN step.EFFECTIVE_START_DATE
388 And nvl(step.Effective_End_Date, hr_general.end_of_time);
389
390 Begin
391 if g_debug then
392 hr_utility.set_location('Entering'||l_proc,10);
393 hr_utility.set_location('p_spinal_point_Id:'||p_spinal_point_Id, 15);
394 hr_utility.set_location('p_Grade_Id:'||p_Grade_Id, 20);
395 hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
396 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 30);
397 end if;
398
399 Open csr_step_id;
400 Fetch csr_step_id into l_step_id;
401 Close csr_step_id;
402
403 if g_debug then
404 hr_utility.set_location('Step Id:'||l_step_id, 35);
405 hr_utility.set_location('Leaving:'||l_proc, 100);
406 end if;
407
408 return l_step_id;
409 Exception
410 When others then
411 return NULL;
412 End get_step_id;
413
414 --
415 ---------------------------chk_oipl_emp_assign-----------------------------
416 --
417 Function chk_oipl_emp_assign(p_oipl_id In number,
418 p_effective_date In date,
419 p_business_group_id In Number)
420 Return Varchar2 IS
421 /*
422 Purpose : This Function returns 'Y' if emp assignments exists for a OIPL
423 Otherwise returns 'N'
424
425 */
426 l_proc Varchar2(72) := g_package||'chk_oipl_emp_assign';
427 l_grade_id Number;
428 l_spinal_point_id Number;
429 l_step_id Number;
430 l_gsp_type Varchar2(1) :='N';
431 l_steps_exists_on_oipl Varchar2(1) :='N';
432 l_dummy Char(1);
433 l_plcmt_Esd Date;
434 l_plcmt_Eed Date;
435 l_Asgt_Esd Date;
436 l_Asgt_Eed Date;
437
438 Cursor csr_grade_point_id
439 IS
440 Select pl.MAPPING_TABLE_PK_ID, -- Grade_Id
441 opt.MAPPING_TABLE_PK_ID -- spinal_point_id
442 From BEN_OIPL_F oipl,
443 BEN_OPT_F opt,
444 BEN_PL_F pl
445 Where oipl.Oipl_Id = p_oipl_id
446 And oipl.OIPL_STAT_CD = 'A'
447 And oipl.Pl_id = pl.PL_ID
448 And oipl.Business_Group_Id= p_business_group_id
449 And oipl.opt_id = opt.opt_id
450 and opt.MAPPING_TABLE_NAME= 'PER_SPINAL_POINTS'
451 And pl.MAPPING_TABLE_NAME = 'PER_GRADES'
452 AND p_effective_date BETWEEN oipl.EFFECTIVE_START_DATE
453 And nvl(oipl.Effective_End_Date, hr_general.end_of_time)
454 AND p_effective_date BETWEEN PL.EFFECTIVE_START_DATE
455 And nvl(PL.Effective_End_Date, hr_general.end_of_time);
456
457 cursor csr_point_placement(l_step_id IN Number) is
458 select Effective_Start_date, Effective_End_Date
459 from per_spinal_point_placements_f
460 where step_id = l_step_id
461 And Business_Group_Id= p_business_group_id
462 and p_effective_date between effective_start_date
463 and nvl(effective_end_date,hr_general.end_of_time);
464 --
465 cursor csr_emp_assign (l_step_id IN Number) is
466 select Effective_Start_Date, Effective_End_Date
467 from per_all_assignments_f
468 where special_ceiling_step_id = l_step_id
469 And Business_Group_Id= p_business_group_id
470 and p_effective_date between effective_start_date
471 and nvl(effective_end_date,hr_general.end_of_time);
472
473 Begin
474 if g_debug then
475 hr_utility.set_location('Entering'||l_proc,5);
476 hr_utility.set_location('p_oipl_id'||p_oipl_id,10);
477 hr_utility.set_location('p_effective_date'||p_effective_date,15);
478 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
479 end if;
480
481 -- Check OIPL has GSP Plan Type
482 l_gsp_type := chk_oipl_has_gsp_type(p_oipl_id => p_oipl_id,
483 p_effective_date => p_effective_date,
484 p_business_group_id => p_business_group_id);
485
486 If l_gsp_type = 'Y' Then
487 if g_debug then
488 hr_utility.set_location('OIPL has GSP Plan Type',35);
489 end if;
490 --
491 -- Get Grade Id, spinal_point_id
492 --
493 Open csr_grade_point_id;
494 Fetch csr_grade_point_id into l_grade_id,l_spinal_point_id;
495 Close csr_grade_point_id;
496
497 if g_debug then
498 hr_utility.set_location('Grade Id :'||l_grade_id,40);
499 hr_utility.set_location('Spinal Point Id : '||l_spinal_point_id,50);
500 end if;
501
502 l_step_Id := get_step_id(p_spinal_point_Id => l_spinal_point_id,
503 p_Grade_Id => l_grade_id,
504 p_effective_date => p_effective_date,
505 p_business_group_id => p_business_group_id);
506 if g_debug then
507 hr_utility.set_location('Step Id : '||l_step_Id,55);
508 end if;
509
510 /* Check step is
511 1) Special Ceiling Step
512 2) Employee Placements on this step
513 */
514 if l_step_Id IS NOT NULL Then
515 open csr_point_placement(l_step_id);
516 fetch csr_point_placement into l_plcmt_Esd, l_plcmt_Eed;
517 IF csr_point_placement%found THEN
518 If P_Effective_Date >= l_plcmt_Eed Then
519 l_steps_exists_on_oipl := 'N';
520 Else
521 if g_debug then
522 hr_utility.set_location('Step has Point Placement ',55);
523 end if;
524 l_steps_exists_on_oipl := 'Y';
525 End if;
526 End if;
527 close csr_point_placement;
528
529 open csr_emp_assign(l_step_id);
530 fetch csr_emp_assign into l_Asgt_Esd, l_Asgt_Eed;
531
532 IF csr_emp_assign%found THEN
533 If P_Effective_Date >= l_Asgt_Eed Then
534 l_steps_exists_on_oipl := 'N';
535 Else
536 if g_debug then
537 hr_utility.set_location('Step has Emp Placement ',65);
538 end if;
539 l_steps_exists_on_oipl := 'Y';
540 End If;
541 End if;
542 close csr_emp_assign;
543
544 end if; -- Step Id Not null
545 End If;
546 if g_debug then
547 hr_utility.set_location('Leaving'||l_proc,100);
548 end if;
549 return l_steps_exists_on_oipl;
550
551 Exception
552 When others then
553 return 'N';
554
555 end chk_oipl_emp_assign;
556
557 --
558 ---------------------------is_pgm_type_gsp-----------------------------
559 --
560 Function is_pgm_type_gsp(p_Pgm_Id in Number,
561 p_Business_Group_Id in Number,
562 p_Effective_Date in Date
563 )
564 Return Varchar2 IS
565 /*
566 Purpose : This function returns 'Y' If Pgm Type is GSP else 'returns 'N'
567 */
568
569 l_proc varchar2(72) := g_package||'is_pgm_type_gsp';
570 l_dummy char(1);
571 l_type Varchar2(1) := 'N';
572
573 Cursor csr_pgm_typ
574 IS
575 Select Null
576 From Ben_Pgm_F
577 Where Pgm_id = p_Pgm_Id
578 And business_group_id = p_business_group_id
579 And p_effective_date Between Effective_Start_Date
580 And nvl(Effective_End_Date, hr_general.end_of_time)
581 And PGM_TYP_CD = 'GSP'
582 And PGM_STAT_CD = 'A';
583
584
585 Begin
586 if g_debug then
587 hr_utility.set_location('Entering '||l_proc,5);
588 hr_utility.set_location('p_Pgm_Id :'||p_Pgm_Id ,10);
589 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 15);
590 hr_utility.set_location('p_Effective_Date:'||p_Effective_Date, 20);
591 end if;
592
593 Open csr_pgm_typ;
594 Fetch csr_pgm_typ into l_dummy;
595 If csr_pgm_typ%Found Then
596 l_type := 'Y';
597 End If;
598 Close csr_pgm_typ;
599
600 if g_debug then
601 hr_utility.set_location('Pgm Type is GSP (Y/N):'||l_type, 25);
602 hr_utility.set_location('Leaving '||l_proc,100);
603 end if;
604
605 return l_type;
606 Exception
607 when others then
608 return 'N';
609 End is_pgm_type_gsp;
610
611
612 --
613 ---------------------------chk_pgm_emp_assign-----------------------------
614 --
615 Function chk_pgm_emp_assign(p_Pgm_Id in Number,
616 p_Business_Group_Id in Number,
617 p_Effective_Date in Date
618 )
619 Return Varchar IS
620 /*
621 Purpose : This function returns 'Y' If Pgm has emp placements else 'returns 'N'
622 */
623
624 l_proc varchar2(72) := g_package||'chk_pgm_emp_assign';
625 l_dummy char(1);
626 l_pgm_type Varchar2(10);
627 l_exists Varchar2(1) := 'N';
628
629 Cursor csr_pgm_typ
630 IS
631 Select PGM_TYP_CD
632 From Ben_Pgm_F
633 Where Pgm_id = p_Pgm_Id
634 And business_group_id = p_business_group_id
635 And p_effective_date Between Effective_Start_Date
636 And nvl(Effective_End_Date, hr_general.end_of_time)
637 And PGM_STAT_CD = 'A';
638
639 Cursor csr_emp_placemnets
640 IS
641 Select NULL
642 From Per_All_Assignments_F Assgt
643 Where Grade_Ladder_Pgm_Id = p_Pgm_Id
644 AND p_Effective_Date BETWEEN Assgt.effective_start_date
645 AND nvl(Assgt.effective_end_date,hr_general.end_of_time)
646 AND Assgt.business_group_id = p_Business_Group_Id;
647
648 Begin
649 if g_debug then
650 hr_utility.set_location('Entering '||l_proc,5);
651 hr_utility.set_location('p_Pgm_Id :'||p_Pgm_Id ,10);
652 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 15);
653 hr_utility.set_location('p_Effective_Date:'||p_Effective_Date, 20);
654 end if;
655
656 Open csr_pgm_typ;
657 Fetch csr_pgm_typ into l_pgm_type;
658 Close csr_pgm_typ;
659
660 if g_debug then
661 hr_utility.set_location('Program Type :'||l_pgm_type, 25);
662 end if;
663
664 If l_pgm_type = 'GSP' Then
665 Open csr_emp_placemnets;
666 Fetch csr_emp_placemnets into l_dummy;
667 If csr_emp_placemnets%Found then
668 if g_debug then
669 hr_utility.set_location('Emp Placemnts exist on Program ',35);
670 end if;
671 l_exists := 'Y';
672 End If;
673 Close csr_emp_placemnets;
674 End If;
675
676 if g_debug then
677 hr_utility.set_location('Leaving '||l_proc,100);
678 end if;
679 return l_exists;
680 Exception
681 when others then
682 return 'N';
683 End chk_pgm_emp_assign;
684
685 --
686 ---------------------------chk_pgm_has_grades-----------------------------
687 --
688 Function chk_pgm_has_grades(p_Pgm_Id in Number,
689 p_Business_Group_Id in Number,
690 p_Effective_Date in Date
691 )
692 Return Varchar IS
693 /*
694 Purpose : This function returns 'Y' If Pgm has Grades else 'returns 'N'
695 */
696
697 l_proc varchar2(72) := g_package||'chk_pgm_has_grades';
698 l_dummy char(1);
699 l_pgm_type Varchar2(10);
700 l_exists Varchar2(1) := 'N';
701
702 Cursor csr_grades
703 IS
704 Select NULL
705 From BEN_PGM_F PGM,
706 BEN_PLIP_F PLIP,
707 BEN_PL_F PL
708 WHERE PGM.PGM_ID = p_Pgm_Id
709 AND PGM.PGM_TYP_CD = 'GSP'
710 AND PGM.BUSINESS_GROUP_ID = p_Business_Group_Id
711 AND PGM.PGM_ID = PLIP.PGM_ID
712 AND PLIP.PL_ID = PL.PL_ID
713 AND PL.MAPPING_TABLE_NAME = 'PER_GRADES'
714 AND PL.MAPPING_TABLE_PK_ID IS NOT NULL
715 And PGM.PGM_STAT_CD = 'A'
716 AND PLIP.PLIP_STAT_CD = 'A'
717 AND PL.PL_STAT_CD ='A'
718 AND p_Effective_Date BETWEEN pgm.effective_start_date
719 AND nvl(pgm.effective_end_date,hr_general.end_of_time)
720 AND p_Effective_Date BETWEEN plip.effective_start_date
721 AND nvl(plip.effective_end_date,hr_general.end_of_time)
722 AND p_Effective_Date BETWEEN pl.effective_start_date
723 AND nvl(pl.effective_end_date,hr_general.end_of_time);
724
725
726 Begin
727 if g_debug then
728 hr_utility.set_location('Entering '||l_proc,5);
729 hr_utility.set_location('p_Pgm_Id :'||p_Pgm_Id ,10);
730 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 15);
731 hr_utility.set_location('p_Effective_Date:'||p_Effective_Date, 20);
732 end if;
733
734 Open csr_grades;
735 Fetch csr_grades into l_dummy;
736 If csr_grades%Found Then
737 l_exists := 'Y';
738 End If;
739 Close csr_grades;
740
741 if g_debug then
742 hr_utility.set_location('Program has Grades (Y/N) :'||l_exists, 25);
743 hr_utility.set_location('Leaving '||l_proc,100);
744 end if;
745
746 return l_exists;
747 Exception
748 when others then
749 return 'N';
750 End chk_pgm_has_grades;
751
752 --
753 ---------------------------chk_pl_exists_in_grdldr-----------------------------
754 --
755 Function chk_pl_exists_in_grdldr(p_pl_id In number,
756 p_effective_date In date,
757 p_Business_Group_Id In Number)
758 Return Varchar2 IS
759 l_proc varchar2(72) := g_package||'chk_pl_exists_in_grdldr';
760 l_dummy Char(1);
761 l_exists Varchar2(10) :='N';
762
763 Cursor csr_pl_in_grdldr
764 IS
765 Select Null
766 From Ben_PL_F pl,
767 Ben_Plip_F plip,
768 Ben_Pgm_F Pgm
769 Where pl.pl_id = p_pl_id
770 And pl.MAPPING_TABLE_NAME = 'PER_GRADES'
771 And pl.MAPPING_TABLE_PK_ID IS NOT NULL
772 And pl.pl_id = plip.pl_id
773 And plip.pgm_id = pgm.pgm_id
774 And pgm.Business_Group_Id = p_Business_Group_Id
775 And pgm.Pgm_Typ_Cd = 'GSP'
776 And pl.PL_STAT_CD = 'A'
777 And plip.PLIP_STAT_CD = 'A'
778 And pgm.PGM_STAT_CD = 'A'
779 And p_effective_date BETWEEN Pl.effective_start_date
780 and nvl(Pl.effective_end_date,hr_general.end_of_time)
781 And p_effective_date BETWEEN Plip.effective_start_date
782 and nvl(Plip.effective_end_date,hr_general.end_of_time)
783 And p_effective_date BETWEEN Pgm.effective_start_date
784 and nvl(Pgm.effective_end_date,hr_general.end_of_time);
785
786 Begin
787 if g_debug then
788 hr_utility.set_location('Entering'||l_proc,5);
789 hr_utility.set_location('p_pl_id'||p_pl_id,10);
790 hr_utility.set_location('p_effective_date:'||p_effective_date, 15);
791 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 20);
792 end if;
793
794
795 Open csr_pl_in_grdldr;
796 Fetch csr_pl_in_grdldr into l_dummy;
797 If csr_pl_in_grdldr%Found Then
798 l_exists := 'Y';
799 End If;
800 Close csr_pl_in_grdldr;
801 --
802 --
803 if g_debug then
804 hr_utility.set_location('Leaving'||l_proc,100);
805 end if;
806 return l_exists;
807
808 Exception
809 when others then
810 return 'N';
811 End chk_pl_exists_in_grdldr;
812
813
814 --
815 ---------------------------chk_opt_exists_in_gsp_pl-----------------------------
816 --
817 Function chk_opt_exists_in_gsp_pl(p_opt_id In number,
818 p_effective_date In date,
819 p_Business_Group_Id In Number)
820 Return Varchar2 IS
821
822 /* To check OPT is in GSP Plan perform following checks
823 1) OPT has GSP Type
824 2) OPT is in GSP Plan through PLIP
825
826 */
827 l_proc Varchar2(72) := g_package||'chk_opt_exists_in_gsp_pl';
828 l_dummy Char(1);
829 l_exists Varchar2(10) :='N';
830 l_opt_has_gsp_type Varchar2(1) := 'N';
831
832 Cursor csr_opt_in_pl
833 IS
834 Select Null
835 FROM BEN_OPT_F opt,
836 BEN_OIPL_F oipl,
837 BEN_PL_F pl
838 WHERE opt.opt_id = p_opt_id
839 AND opt.Business_group_id = p_Business_Group_Id
840 AND opt.opt_id = oipl.opt_id
841 AND oipl.pl_id = pl.pl_id
842 AND pl.MAPPING_TABLE_NAME = 'PER_GRADES'
843 AND pl.MAPPING_TABLE_PK_ID IS NOT NULL
844 And oipl.OIPL_STAT_CD = 'A'
845 AND pl.PL_STAT_CD = 'A'
846 AND p_effective_date BETWEEN opt.effective_start_date
847 AND nvl(opt.effective_end_date,hr_general.end_of_time)
848 AND p_effective_date BETWEEN oipl.effective_start_date
849 AND nvl(oipl.effective_end_date,hr_general.end_of_time)
850 AND p_effective_date BETWEEN pl.effective_start_date
851 AND nvl(pl.effective_end_date,hr_general.end_of_time);
852
853 Begin
854 if g_debug then
855 hr_utility.set_location('Entering'||l_proc,5);
856 hr_utility.set_location('p_opt_id'||p_opt_id,10);
857 hr_utility.set_location('p_effective_date:'||p_effective_date, 15);
858 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 20);
859 end if;
860
861 l_opt_has_gsp_type := chk_opt_has_gsp_type(p_opt_id => p_opt_id ,
862 p_effective_date => p_effective_date,
863 p_business_group_id => p_business_group_id);
864 if g_debug then
865 hr_utility.set_location('Is OPT has GSP Type (Y/N) '||l_opt_has_gsp_type,40);
866 end if;
867
868 If l_opt_has_gsp_type = 'Y' Then
869 Open csr_opt_in_pl;
870 Fetch csr_opt_in_pl into l_dummy;
871 If csr_opt_in_pl%Found Then
872 l_exists := 'Y';
873 End If;
874 Close csr_opt_in_pl;
875 End if;
876 if g_debug then
877 hr_utility.set_location('OPT is in GSP Plan (Y/N) '||l_exists,90);
878 hr_utility.set_location('Leaving'||l_proc,100);
879 end if;
880 return l_exists;
881
882 Exception
883 when others then
884 return 'N';
885 End chk_opt_exists_in_gsp_pl;
886
887
888 --
889 ---------------------------chk_opt_exists_in_gsp_pgm-----------------------------
890 --
891 Function chk_opt_exists_in_gsp_pgm(p_opt_id In number,
892 p_effective_date In date,
893 p_Business_Group_Id In Number)
894 Return Varchar2 IS
895
896 /*
897 To check opt is in PGM check the following
898 1) Check OPT has GSP Type
899 2) Check OPT is in GSP Plan through PLIP
900 3) Check GSP Plan is in GSP Program
901
902 */
903 l_proc Varchar2(72) := g_package||'chk_opt_exists_in_gsp_pgm';
904 l_dummy Char(1);
905 l_opt_exist_in_plan Varchar2(1) := 'N';
906 l_pl_exists_in_grdldr Varchar2(1) := 'N';
907 l_pl_id Number;
908
909 Cursor csr_pl_id
910 IS
911 Select oipl.PL_ID
912 FROM BEN_OPT_F opt,
913 BEN_OIPL_F oipl
914 WHERE opt.opt_id = p_opt_id
915 AND opt.Business_group_id = p_Business_Group_Id
916 AND opt.opt_id = oipl.opt_id
917 And oipl.OIPL_STAT_CD = 'A'
918 AND p_effective_date BETWEEN opt.effective_start_date
919 AND nvl(opt.effective_end_date,hr_general.end_of_time)
920 AND p_effective_date BETWEEN oipl.effective_start_date
921 AND nvl(oipl.effective_end_date,hr_general.end_of_time);
922
923
924 Begin
925 if g_debug then
926 hr_utility.set_location('Entering'||l_proc,5);
927 hr_utility.set_location('p_opt_id'||p_opt_id,10);
928 hr_utility.set_location('p_effective_date:'||p_effective_date, 15);
929 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 20);
930 end if;
931
932 -- 1) Check OPT has GSP Type
933 -- 2) Check OPT is in GSP Plan through PLIP
934
935 l_opt_exist_in_plan := chk_opt_exists_in_gsp_pl(p_opt_id => p_opt_id ,
936 p_effective_date => p_effective_date,
937 p_business_group_id => p_business_group_id);
938 if g_debug then
939 hr_utility.set_location('Is OPT has GSP Type (Y/N) '||l_opt_exist_in_plan,40);
940 end if;
941
942 If l_opt_exist_in_plan = 'Y' Then
943
944 -- Get PL Id
945 Open csr_pl_id;
946 Fetch csr_pl_id into l_pl_id;
947 Close csr_pl_id;
948 if g_debug then
949 hr_utility.set_location('Plan Id : '||l_pl_id,45);
950 end if;
951
952
953 If l_pl_id IS NOT NULL Then
954 -- Check plan exists in GSP Program
955
956 l_pl_exists_in_grdldr := chk_pl_exists_in_grdldr(p_pl_id =>l_pl_id,
957 p_effective_date =>p_effective_date,
958 p_Business_Group_Id =>p_business_group_id);
959
960 if g_debug then
961 hr_utility.set_location(' Plan Exists in Grdldr (Y/N) : '||l_pl_exists_in_grdldr,55);
962 end if;
963
964
965 End if;
966
967 End if;
968 if g_debug then
969 hr_utility.set_location('Finally Opt Exists in Gsp Pgm (Y/N)'||l_pl_exists_in_grdldr,90);
970 hr_utility.set_location('Leaving'||l_proc,100);
971 end if;
972
973 return l_pl_exists_in_grdldr;
974
975 Exception
976 when others then
977 return 'N';
978 End chk_opt_exists_in_gsp_pgm;
979
980
981
982 --
983 ---------------------------pgm_ins_val-----------------------------
984 --
985 procedure pgm_ins_val(p_Pgm_Id in Number,
986 p_Business_Group_Id in Number,
987 p_Short_Name in Varchar2,
988 p_Short_Code in Varchar2,
989 p_Effective_Date in Date,
990 p_Dflt_Pgm_Flag in Varchar2) IS
991 --
992 --
993 /*
994 Purpose : This proedure performs these validataions
995 1) Grade Ladder Short Name, Short Code (only if entered) must be unique
996 with in the business group.
997 2) Check there is only one Default Grade Ladder for a Business group.
998 */
999 --
1000 --
1001 l_proc varchar2(72) := g_package||'pgm_ins_val';
1002 l_dummy Char(1);
1003
1004 Cursor csr_short_name
1005 IS
1006 Select null
1007 From Ben_Pgm_F
1008 Where Pgm_Id <> nvl(p_pgm_id,-1)
1009 And Short_Name = nvl(p_short_name,-1)
1010 And Business_Group_Id = p_business_group_id
1011 And PGM_TYP_CD ='GSP' ;
1012
1013
1014 Cursor csr_short_code
1015 IS
1016 Select null
1017 From Ben_Pgm_F
1018 Where Pgm_Id <> nvl(p_pgm_id,-1)
1019 And Short_Code = nvl(p_short_code,-1)
1020 And Business_Group_Id = p_business_group_id
1021 And PGM_TYP_CD ='GSP' ;
1022
1023
1024 Cursor csr_default_grdldr IS
1025 Select Null
1026 From ben_pgm_f
1027 Where pgm_id <> nvl(p_pgm_id,-1)
1028 And PGM_TYP_CD ='GSP'
1029 And Dflt_Pgm_Flag = 'Y'
1030 And business_group_id = p_business_group_id
1031 And effective_end_date>= p_effective_date;
1032
1033 Begin
1034 if g_debug then
1035 hr_utility.set_location('Entering '||l_proc,5);
1036 hr_utility.set_location('p_Pgm_Id :'||p_Pgm_Id ,10);
1037 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 15);
1038 hr_utility.set_location('p_Short_Name:'||p_Short_Name, 20);
1039 hr_utility.set_location('p_Short_Code:'||p_Short_Code, 25);
1040 hr_utility.set_location('p_Dflt_Pgm_Flag:'||p_Dflt_Pgm_Flag, 30);
1041 end if;
1042
1043
1044 --
1045 --
1046 if g_debug then
1047 hr_utility.set_location('Chk Short Name...', 35);
1048 end if;
1049 --
1050 --
1051 If p_Short_Name IS NOT NULL Then
1052 Open csr_short_name;
1053 Fetch csr_short_name into l_dummy;
1054 If csr_short_name%Found then
1055 Close csr_short_name;
1056 hr_utility.set_message(8302,'PQH_GSP_GL_SHT_NAME_UNQ');
1057 hr_utility.raise_error;
1058 End If;
1059 Close csr_short_name;
1060 End If;
1061 --
1062 --
1063 if g_debug then
1064 hr_utility.set_location('Chk Short Code....', 40);
1065 end if;
1066 --
1067 --
1068 If p_Short_Code IS NOT NULL Then
1069 Open csr_short_code;
1070 Fetch csr_short_code into l_dummy;
1071 If csr_short_code%Found then
1072 Close csr_short_code;
1073 hr_utility.set_message(8302,'PQH_GSP_GL_SHT_CODE_UNQ');
1074 hr_utility.raise_error;
1075 End If;
1076 Close csr_short_code;
1077 End If;
1078 --
1079 --
1080 if g_debug then
1081 hr_utility.set_location('Chk Default Grade Ladder....', 45);
1082 end if;
1083 --
1084 --
1085 If p_Dflt_Pgm_Flag ='Y' Then
1086 Open csr_default_grdldr;
1087 Fetch csr_default_grdldr into l_dummy;
1088 If csr_default_grdldr%Found then
1089 Close csr_default_grdldr;
1090 hr_utility.set_message(8302,'PQH_GSP_BEN_DFLT_GRDLDR');
1091 hr_utility.raise_error;
1092 End If;
1093 Close csr_default_grdldr;
1094 End If;
1095 --
1096 --
1097 if g_debug then
1098 hr_utility.set_location('Leaving:'||l_proc, 100);
1099 end if;
1100
1101 End;
1102
1103 --
1104 ---------------------------pgm_upd_val-----------------------------
1105 --
1106 procedure pgm_upd_val(p_Pgm_Id in Number,
1107 p_Business_Group_Id in Number,
1108 p_Short_Name in Varchar2,
1109 p_Short_Code in Varchar2,
1110 p_Effective_Date in Date,
1111 p_Dflt_Pgm_Flag in Varchar2,
1112 p_Pgm_Typ_Cd in Varchar2,
1113 p_pgm_Stat_cd in Varchar2,
1114 p_Use_Prog_Points_Flag In Varchar2,
1115 p_Acty_Ref_Perd_Cd In Varchar2 ,
1116 p_Pgm_Uom In Varchar2) IS
1117
1118
1119 /*
1120 Purpose : This procedure perorms these Validations.
1121 1) Check there is only one Default Grade Ladder for a Business group.
1122 2) Grade Ladder Short Name, Short Code (only if entered) must be unique with in the business group.
1123 3) If Employee Placements on this Grade Ladder Or Grades are attached to Program then
1124 *** 1. Cannot Change Currency, Rate Period
1125 *** 2. Cannot Change Use Progression Points flag
1126 *** 3. Cannot Change Program Type
1127 *** 4. Cannot Change Program Status
1128
1129 */
1130 l_proc Varchar2(72) := g_package||'pgm_upd_val';
1131 l_dummy Char(1);
1132 l_pgm_type Ben_Pgm_F.PGM_TYP_CD%Type;
1133 l_pgm_status Ben_Pgm_F.PGM_STAT_CD%Type;
1134 l_pgm_points_flag Ben_Pgm_F.USE_PROG_POINTS_FLAG%Type;
1135 l_rate_period Ben_Pgm_F.ACTY_REF_PERD_CD%Type;
1136 l_currency Ben_Pgm_F.PGM_UOM%Type;
1137 l_exists Varchar2(1) := 'N';
1138 l_grd_exists Varchar2(1) := 'N';
1139
1140
1141
1142 Begin
1143 if g_debug then
1144 hr_utility.set_location('Entering '||l_proc,5);
1145 hr_utility.set_location('p_Pgm_Id :'||p_Pgm_Id ,10);
1146 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 15);
1147 hr_utility.set_location('p_Short_Name:'||p_Short_Name, 20);
1148 hr_utility.set_location('p_Short_Code:'||p_Short_Code, 25);
1149 hr_utility.set_location('p_Dflt_Pgm_Flag:'||p_Dflt_Pgm_Flag, 30);
1150 hr_utility.set_location('p_Pgm_Typ_Cd:'||p_Pgm_Typ_Cd, 35);
1151 hr_utility.set_location('p_pgm_Stat_cd:'||p_pgm_Stat_cd, 40);
1152 hr_utility.set_location('p_Acty_Ref_Perd_Cd:'||p_Acty_Ref_Perd_Cd, 41);
1153 hr_utility.set_location('p_Pgm_Uom:'||p_Pgm_Uom, 42);
1154 end if;
1155
1156 pqh_gsp_ben_validations.pgm_ins_val( p_Pgm_Id => p_pgm_id,
1157 p_Business_Group_Id => p_business_group_id,
1158 p_Short_Name => p_short_name,
1159 p_Short_Code => p_short_code,
1160 p_Effective_Date => p_effective_date,
1161 p_Dflt_Pgm_Flag => p_Dflt_Pgm_Flag);
1162
1163
1164 l_exists := chk_pgm_emp_assign(p_Pgm_Id => p_Pgm_Id,
1165 p_Business_Group_Id => p_Business_Group_Id,
1166 p_Effective_Date => p_Effective_Date);
1167
1168 if g_debug then
1169 hr_utility.set_location('Emp Exists on Pgm (Y/N) :'||l_exists, 95);
1170 end if;
1171
1172 l_grd_exists := chk_pgm_has_grades(p_Pgm_Id => p_Pgm_Id,
1173 p_Business_Group_Id => p_Business_Group_Id,
1174 p_Effective_Date => p_Effective_Date);
1175
1176 if g_debug then
1177 hr_utility.set_location('Pgm has Grades (Y/N) :'||l_grd_exists, 100);
1178 end if;
1179
1180 -- changed for Bug 7114098. Check for l_grd_exists has been removed as this check has already been
1181 -- done earlier in pqh_gsp_utility.chk_grdldr_grd_curreny_rate, which is called well before the
1182 -- current procedure.
1183
1184 -- If (l_exists = 'Y' OR l_grd_exists = 'Y') then
1185 IF (l_exists = 'Y') THEN -- bug 7114098
1186
1187 if g_debug then
1188 hr_utility.set_location('Old Pgm Type :'||ben_pgm_shd.g_old_rec.pgm_typ_cd, 100);
1189 hr_utility.set_location('Old Pgm Status :'||ben_pgm_shd.g_old_rec.pgm_Stat_cd, 110);
1190 hr_utility.set_location('Old Points Flag :'||ben_pgm_shd.g_old_rec.Use_Prog_Points_Flag, 120);
1191 hr_utility.set_location('Old Rate Period :'||ben_pgm_shd.g_old_rec.Acty_Ref_Perd_Cd, 125);
1192 hr_utility.set_location('Old Currency :'||ben_pgm_shd.g_old_rec.Pgm_Uom, 130);
1193 end if;
1194
1195
1196 -- Program Type Cannot be Changed.
1197 if (ben_pgm_shd.g_old_rec.pgm_typ_cd <> p_Pgm_Typ_Cd) then
1198 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_PGM_TYPE');
1199 hr_utility.raise_error;
1200 end if;
1201
1202 -- Program Status Cannot be Changed.
1203 if (ben_pgm_shd.g_old_rec.pgm_Stat_cd = 'A' And (ben_pgm_shd.g_old_rec.pgm_Stat_cd <> p_pgm_Stat_cd)) then
1204 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_PGM_STATUS');
1205 hr_utility.raise_error;
1206 end if;
1207
1208 -- Progression Points flag Cannot be Changed.
1209 if (ben_pgm_shd.g_old_rec.use_prog_points_flag <> p_Use_Prog_Points_Flag) then
1210 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_PGM_PRG_FLAG');
1211 hr_utility.raise_error;
1212 end if;
1213
1214
1215 -- Rate Period Cannot be Changed.
1216 if (ben_pgm_shd.g_old_rec.Acty_Ref_Perd_Cd <> p_Acty_Ref_Perd_Cd) then
1217 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_PGM_RATE_PRD');
1218 hr_utility.raise_error;
1219 end if;
1220
1221 -- Currency Cannot be Changed.
1222 if (ben_pgm_shd.g_old_rec.Pgm_Uom <> p_Pgm_Uom) then
1223 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_PGM_CURRENCY');
1224 hr_utility.raise_error;
1225 end if;
1226
1227 End if; -- emp Exists
1228
1229 if g_debug then
1230 hr_utility.set_location('Leaving '||l_proc,100);
1231 end if;
1232
1233 End;
1234
1235
1236
1237 --
1238 ---------------------------pgm_del_val-----------------------------
1239 --
1240 procedure pgm_del_val(p_Pgm_Id in Number,
1241 p_Business_Group_Id in Number,
1242 p_Effective_Date in Date
1243 ) IS
1244 /*
1245 Purpose : This procedure perorms these Validations.
1246 1) Cannot Delete a Grade Ladder if Employee Placements exists on this Grade Ladder.
1247 */
1248
1249 l_proc varchar2(72) := g_package||'pgm_del_val';
1250 l_exists Varchar2(1) := 'N';
1251
1252 Begin
1253 if g_debug then
1254 hr_utility.set_location('Entering '||l_proc,5);
1255 hr_utility.set_location('p_Pgm_Id :'||p_Pgm_Id ,10);
1256 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 15);
1257 end if;
1258
1259 l_exists := chk_pgm_emp_assign(p_Pgm_Id => p_Pgm_Id,
1260 p_Business_Group_Id => p_Business_Group_Id,
1261 p_Effective_Date => p_Effective_Date);
1262
1263 if g_debug then
1264 hr_utility.set_location('Emp Exists on Pgm (Y/N) :'||l_exists, 25);
1265 end if;
1266
1267 If l_exists = 'Y' then
1268 hr_utility.set_message(8302,'PQH_GSP_BEN_NOT_DEL_PGM');
1269 hr_utility.raise_error;
1270 End If;
1271
1272 if g_debug then
1273 hr_utility.set_location('Leaving '||l_proc,100);
1274 end if;
1275
1276 End pgm_del_val;
1277
1278
1279 --
1280 ---------------------------pgm_validations-----------------------------
1281 --
1282 procedure pgm_validations(p_pgm_id in Number,
1283 p_dml_operation in Varchar2,
1284 p_effective_date in Date,
1285 p_business_group_id in Number Default hr_general.GET_BUSINESS_GROUP_ID,
1286 p_short_name in Varchar2 Default NULL,
1287 p_short_code in Varchar2 Default NULL,
1288 p_Dflt_Pgm_Flag in Varchar2 Default 'N',
1289 p_Pgm_Typ_Cd in Varchar2 Default NULL,
1290 p_pgm_Stat_cd in Varchar2 Default 'I',
1291 p_Use_Prog_Points_Flag in Varchar2 Default 'N',
1292 p_Acty_Ref_Perd_Cd In Varchar2 Default NULL,
1293 p_Pgm_Uom In Varchar2 Default NULL)
1294
1295
1296
1297 IS
1298 l_proc Varchar2(72) := g_package||'pgm_validations';
1299 l_pgm_type_is_gsp Varchar2(1) := 'N';
1300
1301 begin
1302 -- validations to be performed in this are
1303 -- people shouldnot be assigned to the deleted grade ladder
1304 -- only one default grade ladder
1305 -- currency periodicity is not changed, which doesnot match with other setup
1306 -- use points flag is not changed, which doesnot match with other setup
1307 -- etc
1308
1309
1310 --
1311 --
1312 if g_debug then
1313 hr_utility.set_location('Entering '||l_proc,5);
1314 hr_utility.set_location('p_Pgm_Id :'||p_Pgm_Id ,10);
1315 hr_utility.set_location('p_Business_Group_Id:'||p_Business_Group_Id, 15);
1316 hr_utility.set_location('p_Short_Name:'||p_Short_Name, 20);
1317 hr_utility.set_location('p_Short_Code:'||p_Short_Code, 25);
1318 hr_utility.set_location('p_Dflt_Pgm_Flag:'||p_Dflt_Pgm_Flag, 30);
1319 hr_utility.set_location('p_pgm_Stat_cd:'||p_pgm_Stat_cd, 35);
1320 hr_utility.set_location('p_Use_Prog_Points_Flag:'||p_Use_Prog_Points_Flag, 40);
1321 hr_utility.set_location('p_Acty_Ref_Perd_Cd:'||p_Acty_Ref_Perd_Cd, 45);
1322 hr_utility.set_location('p_Pgm_Uom:'||p_Pgm_Uom, 50);
1323
1324 end if;
1325
1326 l_pgm_type_is_gsp := is_pgm_type_gsp(p_Pgm_Id => p_pgm_id,
1327 p_Business_Group_Id => p_business_group_id,
1328 p_Effective_Date => p_effective_date );
1329
1330 if g_debug then
1331 hr_utility.set_location('Is PGM Type is GSP (Y/N):'||l_pgm_type_is_gsp, 85);
1332 end if;
1333
1334 If l_pgm_type_is_gsp = 'Y' Then
1335
1336 --
1337 -- Perform Insert Validations
1338 --
1339 If p_dml_operation = 'I' then
1340
1341 if g_debug then
1342 hr_utility.set_location(' PGM Insert Validations ', 90);
1343 end if;
1344 pqh_gsp_ben_validations.pgm_ins_val( p_Pgm_Id => p_pgm_id,
1345 p_Business_Group_Id => p_business_group_id,
1346 p_Short_Name => p_short_name,
1347 p_Short_Code => p_short_code,
1348 p_Effective_Date => p_effective_date,
1349 p_Dflt_Pgm_Flag => p_Dflt_Pgm_Flag);
1350 End if;
1351 --
1352 -- Perform Update Validations
1353 --
1354 If p_dml_operation = 'U' then
1355
1356 if g_debug then
1357 hr_utility.set_location(' PGM Update Validations ', 95);
1358 end if;
1359 pqh_gsp_ben_validations.pgm_upd_val( p_Pgm_Id => p_pgm_id,
1360 p_Business_Group_Id => p_business_group_id,
1361 p_Short_Name => p_short_name,
1362 p_Short_Code => p_short_code,
1363 p_Effective_Date => p_effective_date,
1364 p_Dflt_Pgm_Flag => p_Dflt_Pgm_Flag,
1365 p_Pgm_Typ_Cd => p_Pgm_Typ_Cd,
1366 p_pgm_Stat_cd => p_pgm_Stat_cd,
1367 p_Use_Prog_Points_Flag => p_Use_Prog_Points_Flag,
1368 p_Acty_Ref_Perd_Cd => p_Acty_Ref_Perd_Cd,
1369 p_Pgm_Uom => p_Pgm_Uom);
1370 End if;
1371
1372 --
1373 -- Perform Delete Validations
1374 --
1375
1376 If p_dml_operation = 'D' then
1377
1378 if g_debug then
1379 hr_utility.set_location(' PGM Delete Validations ', 100);
1380 end if;
1381 pqh_gsp_ben_validations.pgm_del_val( p_Pgm_Id => p_pgm_id,
1382 p_Business_Group_Id => p_business_group_id,
1383 p_Effective_Date => p_effective_date);
1384 End if;
1385
1386 End If;
1387
1388 if g_debug then
1389 hr_utility.set_location('Leaving'||l_proc,150);
1390 end if;
1391
1392 end pgm_validations;
1393
1394
1395 --
1396 ---------------------------pl_ins_val-----------------------------
1397 --
1398 procedure pl_ins_val(p_pl_id in number,
1399 p_effective_date in date)
1400 IS
1401 /*
1402 Purpose : To Perform these Validations.
1403 */
1404 l_proc varchar2(72) := g_package||'pl_ins_val';
1405 Begin
1406 if g_debug then
1407 hr_utility.set_location('Entering'||l_proc,10);
1408 end if;
1409
1410 if g_debug then
1411 hr_utility.set_location('Leaving'||l_proc,100);
1412 end if;
1413
1414 end pl_ins_val;
1415
1416 --
1417 ---------------------------pl_upd_val-----------------------------
1418 --
1419 procedure pl_upd_val(p_pl_id In number,
1420 p_effective_date In date,
1421 p_Business_Group_Id In Number,
1422 p_pl_Typ_Id In Number,
1423 p_Mapping_Table_PK_ID In Number,
1424 p_pl_stat_cd IN Varchar2)
1425 IS
1426 /*
1427 Purpose : To Perform these Validations.
1428 1) Once the plan is attached to Grade Ladder through PLIP.
1429 *** 1. Cannot Change Plan Type
1430 *** 2. Cannot Update Plan Status from Activate to Inactive/Closed/Pending
1431
1432 2) User Cannot change Grade which is mapped to Plan if
1433 Plan is mapped to Grade and
1434 Plan is attached to Grade Ladder through PLIP and
1435 PLIP has Employee placements
1436
1437 */
1438 l_proc Varchar2(72) := g_package||'pl_upd_val';
1439 l_exists Varchar2(10);
1440 l_exist_pl_type_cd BEN_PL_TYP.OPT_TYP_CD%Type;
1441 l_new_pl_type_cd BEN_PL_TYP.OPT_TYP_CD%Type;
1442 l_grade_id BEN_PL_F.Mapping_Table_PK_ID%Type;
1443 l_status BEN_PL_F.PL_STAT_CD%Type;
1444 l_plip_id Number;
1445 l_emp_exists Varchar2(1) :='N';
1446 l_plan_Typ_Id Number;
1447
1448
1449
1450 Cursor csr_pl_type (l_plan_Typ_Id IN Number)
1451 IS
1452 Select OPT_TYP_CD
1453 From BEN_PL_TYP_F Type
1454 Where Pl_Typ_Id = l_plan_Typ_Id
1455 And Business_Group_id = p_business_group_id
1456 And p_effective_date BETWEEN effective_start_date
1457 And nvl(effective_end_date,hr_general.end_of_time);
1458
1459
1460 Cursor csr_plip_id
1461 IS
1462 Select PLIP_ID
1463 From BEN_PLIP_F
1464 Where Pl_Id = p_pl_Id
1465 And business_group_id = p_business_group_id
1466 And p_effective_date Between Effective_Start_Date
1467 And nvl(Effective_End_Date, hr_general.end_of_time)
1468 And PLIP_STAT_CD = 'A';
1469
1470
1471 Begin
1472 if g_debug then
1473 hr_utility.set_location('Entering'||l_proc,5);
1474 hr_utility.set_location('p_pl_id'||p_pl_id,10);
1475 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1476 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1477 hr_utility.set_location('p_pl_Typ_Id'||p_pl_Typ_Id,25);
1478 hr_utility.set_location('Old pl_Typ_Id'||ben_pln_shd.g_old_rec.PL_TYP_ID,30);
1479 hr_utility.set_location('p_Mapping_Table_PK_ID'||p_Mapping_Table_PK_ID,35);
1480 hr_utility.set_location('Old Mapping_Table_PK_ID'||ben_pln_shd.g_old_rec.Mapping_Table_PK_ID,40);
1481 hr_utility.set_location('p_pl_stat_cd'||p_pl_stat_cd,45);
1482 hr_utility.set_location('Old pl_stat_cd'||ben_pln_shd.g_old_rec.pl_stat_cd,60);
1483 end if;
1484
1485 l_exists := chk_pl_exists_in_grdldr(p_pl_id => p_pl_id,
1486 p_effective_date => p_effective_date,
1487 p_Business_Group_Id => p_Business_Group_Id);
1488 --
1489 --
1490 if g_debug then
1491 hr_utility.set_location('Plan Exists in Program : '||l_exists,90);
1492 end if;
1493 If l_exists = 'Y' Then
1494
1495 Open csr_pl_type(ben_pln_shd.g_old_rec.PL_TYP_ID);
1496 Fetch csr_pl_type into l_exist_pl_type_cd;
1497 Close csr_pl_type;
1498
1499
1500 if g_debug then
1501 hr_utility.set_location('Old Plan Type : '||l_exist_pl_type_cd,100);
1502 end if;
1503
1504 If (l_exist_pl_type_cd = 'GSP' ) Then
1505
1506 --
1507 -- Cannot Update Plan Type
1508 --
1509
1510 Open csr_pl_type(p_pl_Typ_Id);
1511 Fetch csr_pl_type into l_new_pl_type_cd;
1512 Close csr_pl_type;
1513 if g_debug then
1514 hr_utility.set_location('New Plan Type : '||l_new_pl_type_cd,145);
1515 end if;
1516
1517
1518 if g_debug then
1519 hr_utility.set_location('New Plan Type : '||l_new_pl_type_cd,155);
1520 end if;
1521 If (l_exist_pl_type_cd <> l_new_pl_type_cd ) Then
1522 hr_utility.set_message(8302,'PQH_GSP_NOT_PL_UPD_PGM_TYPE');
1523 hr_utility.raise_error;
1524 End If;
1525
1526 --
1527 -- Cannot Update Status
1528 --
1529
1530 if g_debug then
1531 hr_utility.set_location('Old Plan Status : '||ben_pln_shd.g_old_rec.pl_stat_cd,165);
1532 end if;
1533
1534
1535 If (ben_pln_shd.g_old_rec.pl_stat_cd = 'A' And ben_pln_shd.g_old_rec.pl_stat_cd <> p_pl_stat_cd)Then
1536 hr_utility.set_message(8302,'PQH_GSP_NOT_PL_UPD_PGM_STATUS');
1537 hr_utility.raise_error;
1538 End If;
1539
1540 --
1541 -- Cannot Update Grade Id if Employees are placed on Grade
1542 --
1543
1544 -- Get PLIP Id
1545
1546 Open csr_plip_id;
1547 Fetch csr_plip_id into l_plip_id;
1548 Close csr_plip_id;
1549 if g_debug then
1550 hr_utility.set_location('PLIP Id : '||l_plip_id,175);
1551 end if;
1552
1553
1554 if l_plip_id IS NOT NULL Then
1555 l_emp_exists := chk_plip_emp_assign(p_plip_id => l_plip_id,
1556 p_effective_date => p_effective_date,
1557 p_business_group_id =>p_Business_Group_Id);
1558 if g_debug then
1559 hr_utility.set_location('Emp Placements exists (Y/N) : '||l_emp_exists,190);
1560 end if;
1561
1562 if l_emp_exists = 'Y' Then
1563
1564 if g_debug then
1565 hr_utility.set_location('Old Grade Id : '||ben_pln_shd.g_old_rec.Mapping_Table_PK_ID,200);
1566 end if;
1567
1568
1569 If (ben_pln_shd.g_old_rec.Mapping_Table_PK_ID <> p_Mapping_Table_PK_ID)Then
1570 hr_utility.set_message(8302,'PQH_GSP_NOT_PL_UPD_EMP_GRD_ID');
1571 hr_utility.raise_error;
1572 End If;
1573 end if;
1574
1575 end if;-- plip id is not null
1576
1577 end if; -- GSP
1578
1579 End If; -- Pl Exists in PGM
1580
1581 if g_debug then
1582 hr_utility.set_location('Leaving'||l_proc,300);
1583 end if;
1584
1585 end pl_upd_val;
1586
1587 --
1588 ---------------------------pl_del_val-----------------------------
1589 --
1590 procedure pl_del_val(p_pl_id in number,
1591 p_effective_date in date)
1592 IS
1593 /*
1594 Purpose : To Perform these Validations.
1595 */
1596 l_proc varchar2(72) := g_package||'pl_del_val';
1597 Begin
1598 if g_debug then
1599 hr_utility.set_location('Entering'||l_proc,10);
1600 end if;
1601
1602 if g_debug then
1603 hr_utility.set_location('Leaving'||l_proc,100);
1604 end if;
1605
1606 end pl_del_val;
1607
1608
1609 --
1610 ---------------------------pl_validations-----------------------------
1611 --
1612 procedure pl_validations(p_pl_id In number,
1613 p_effective_date In date,
1614 p_Business_Group_Id In Number Default hr_general.GET_BUSINESS_GROUP_ID,
1615 p_dml_operation In varchar2,
1616 p_pl_Typ_Id In Number Default NULL,
1617 p_Mapping_Table_PK_ID In Number Default NULL,
1618 p_pl_stat_cd IN Varchar2 Default 'I')
1619 IS
1620
1621 l_proc varchar2(72) := g_package||'pl_validations';
1622 begin
1623 -- validations to be performed in this routine are
1624 -- there should not be any assignment on the grade linked if getting deleted or disabled
1625 -- this should be the only plan linked to the grade
1626 -- etc
1627 if g_debug then
1628 hr_utility.set_location('Entering'||l_proc,5);
1629 hr_utility.set_location('p_pl_id'||p_pl_id,10);
1630 hr_utility.set_location('p_dml_operation'||p_dml_operation,11);
1631 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1632 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1633 hr_utility.set_location('p_pl_Typ_Id'||p_pl_Typ_Id,24);
1634 hr_utility.set_location('p_Mapping_Table_PK_ID'||p_Mapping_Table_PK_ID,26);
1635 hr_utility.set_location('p_pl_stat_cd'||p_pl_stat_cd,28);
1636 end if;
1637
1638 If p_dml_operation = 'I' Then
1639 if g_debug then
1640 hr_utility.set_location('Plan Insert Validations',40);
1641 end if;
1642 pl_ins_val(p_pl_id => p_pl_id,
1643 p_effective_date => p_effective_date);
1644 end if;
1645
1646 If p_dml_operation = 'U' Then
1647 if g_debug then
1648 hr_utility.set_location('Plan Update Validations',45);
1649 end if;
1650 pl_upd_val(p_pl_id => p_pl_id,
1651 p_effective_date => p_effective_date,
1652 p_Business_Group_Id => p_Business_Group_Id,
1653 p_pl_Typ_Id => p_pl_Typ_Id,
1654 p_Mapping_Table_PK_ID => p_Mapping_Table_PK_ID,
1655 p_pl_stat_cd => p_pl_stat_cd);
1656 end if;
1657
1658 If p_dml_operation = 'D' Then
1659 if g_debug then
1660 hr_utility.set_location('Plan Delete Validations',50);
1661 end if;
1662 pl_del_val(p_pl_id => p_pl_id,
1663 p_effective_date => p_effective_date);
1664 end if;
1665
1666 if g_debug then
1667 hr_utility.set_location('Leaving'||l_proc,100);
1668 end if;
1669
1670 end pl_validations;
1671
1672 --
1673 ---------------------------plip_ins_val-----------------------------
1674 --
1675 procedure plip_ins_val( p_plip_id In number,
1676 p_effective_date In date,
1677 p_business_group_id In Number)
1678 IS
1679 /*
1680 Purpose : To Perform these Validations.
1681 */
1682 l_proc varchar2(72) := g_package||'plip_ins_val';
1683 Begin
1684 if g_debug then
1685 hr_utility.set_location('Entering'||l_proc,5);
1686 hr_utility.set_location('p_plip_id'||p_plip_id,10);
1687 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1688 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1689 end if;
1690
1691 if g_debug then
1692 hr_utility.set_location('Leaving'||l_proc,100);
1693 end if;
1694
1695 end plip_ins_val;
1696
1697 --
1698 ---------------------------plip_upd_val-----------------------------
1699 --
1700 procedure plip_upd_val( p_plip_id In number,
1701 p_effective_date In date,
1702 p_business_group_id In Number,
1703 p_Plip_Stat_Cd In Varchar)
1704 IS
1705 /*
1706 Purpose : To Perform these Validations.
1707 1) PLIP Record Status cannot be changed from Activate to Inactive/Closed/Pending
1708 if employee placemnents exists on this PLIP.
1709
1710 */
1711 l_proc Varchar2(72) := g_package||'plip_upd_val';
1712 l_exists Varchar2(1) :='N';
1713 l_status Ben_PlIP_F.PLIP_STAT_CD%Type;
1714
1715
1716 Begin
1717 if g_debug then
1718 hr_utility.set_location('Entering'||l_proc,5);
1719 hr_utility.set_location('p_plip_id'||p_plip_id,10);
1720 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1721 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1722 hr_utility.set_location('p_Plip_Stat_Cd'||p_Plip_Stat_Cd,20);
1723 hr_utility.set_location('Old Plip_Stat_Cd'||ben_cpp_shd.g_old_rec.PLIP_STAT_CD,25);
1724 end if;
1725
1726 -- Check Emp Placements Exists on PLIP Rec
1727
1728 -- If Y then emp exists on PLIP Rec else Not Exists
1729
1730 l_exists := chk_plip_emp_assign(p_plip_id => p_plip_id,
1731 p_effective_date => p_effective_date ,
1732 p_business_group_id => p_business_group_id);
1733 if g_debug then
1734 hr_utility.set_location('Emp Placements Exists on PLIP Rec (Y/N) :'||l_exists,30);
1735 end if;
1736 If l_exists = 'Y' Then
1737 --
1738
1739
1740 if (ben_cpp_shd.g_old_rec.PLIP_STAT_CD = 'A' And ben_cpp_shd.g_old_rec.PLIP_STAT_CD <> p_Plip_Stat_Cd) Then
1741 hr_utility.set_location('Cannot Change Status ',55);
1742 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_PLIP_STATUS');
1743 hr_utility.raise_error;
1744 end if;
1745
1746 End If;
1747
1748 if g_debug then
1749 hr_utility.set_location('Leaving'||l_proc,100);
1750 end if;
1751
1752 end plip_upd_val;
1753
1754 --
1755 ---------------------------plip_del_val-----------------------------
1756 --
1757 procedure plip_del_val(p_plip_id In number,
1758 p_effective_date In date,
1759 p_business_group_id In Number)
1760 IS
1761 /*
1762 Purpose : To Perform these Validations.
1763 1) PLIP Record cannot be deleted if employee placemnents exists on this PLIP
1764 */
1765 l_proc Varchar2(72) := g_package||'plip_del_val';
1766 l_exists Varchar2(1) :='N';
1767
1768 Begin
1769 if g_debug then
1770 hr_utility.set_location('Entering'||l_proc,5);
1771 hr_utility.set_location('p_plip_id'||p_plip_id,10);
1772 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1773 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1774 end if;
1775
1776 -- Check Emp Placements Exists on PLIP Rec if Y then emp exists on PLIP Rec else Not Exists
1777
1778 l_exists := chk_plip_emp_assign(p_plip_id => p_plip_id,
1779 p_effective_date => p_effective_date ,
1780 p_business_group_id => p_business_group_id);
1781
1782 if g_debug then
1783 hr_utility.set_location('Emp Placements Exists on PLIP Rec (Y/N) :'||l_exists,30);
1784 end if;
1785 If l_exists = 'Y' Then
1786
1787 if g_debug then
1788 hr_utility.set_location('Emp Placements are exists',50);
1789 end if;
1790 hr_utility.set_message(8302,'PQH_GSP_NOT_DEL_PLIP');
1791 hr_utility.raise_error;
1792 end if;
1793
1794 if g_debug then
1795 hr_utility.set_location('Leaving'||l_proc,100);
1796 end if;
1797 end plip_del_val;
1798
1799
1800 --
1801 ---------------------------plip_validations-----------------------------
1802 --
1803
1804 procedure plip_validations(p_plip_id In Number,
1805 p_effective_date In Date,
1806 p_dml_operation In Varchar2,
1807 p_business_group_id In Number Default hr_general.GET_BUSINESS_GROUP_ID,
1808 p_Plip_Stat_Cd In Varchar Default 'I')
1809 IS
1810 l_proc Varchar2(72) := g_package||'plip_validations';
1811 l_is_pgm_type_gsp Varchar2(1) := 'N';
1812
1813
1814 begin
1815 -- validations to be performed in this routine are
1816 -- there should not be any assignment on the grade linked if getting deleted or
1817 -- disabled etc
1818 if g_debug then
1819 hr_utility.set_location('Entering'||l_proc,10);
1820 hr_utility.set_location('p_plip_id'||p_plip_id,10);
1821 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1822 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1823 hr_utility.set_location('p_Plip_Stat_Cd'||p_Plip_Stat_Cd,25);
1824 hr_utility.set_location('Old Plip_Stat_Cd'||ben_cpp_shd.g_old_rec.PLIP_STAT_CD,30);
1825 hr_utility.set_location('p_dml_operation'||p_dml_operation,35);
1826 end if;
1827
1828
1829
1830
1831 If p_dml_operation = 'I' Then
1832 if g_debug then
1833 hr_utility.set_location('PLIP Insert Validations',45);
1834 end if;
1835
1836 plip_ins_val( p_plip_id => p_plip_id,
1837 p_effective_date => p_effective_date,
1838 p_business_group_id => p_business_group_id);
1839 end if;
1840
1841 If p_dml_operation = 'U' Then
1842 if g_debug then
1843 hr_utility.set_location('PLIP Update Validations',55);
1844 end if;
1845 plip_upd_val( p_plip_id => p_plip_id,
1846 p_effective_date => p_effective_date,
1847 p_business_group_id => p_business_group_id,
1848 p_Plip_Stat_Cd => p_Plip_Stat_Cd
1849 );
1850 end if;
1851
1852 If p_dml_operation = 'D' Then
1853 if g_debug then
1854 hr_utility.set_location('PLIP Delete Validations',65);
1855 end if;
1856 plip_del_val( p_plip_id => p_plip_id,
1857 p_effective_date => p_effective_date,
1858 p_business_group_id => p_business_group_id);
1859 end if;
1860
1861
1862
1863 if g_debug then
1864 hr_utility.set_location('Leaving'||l_proc,100);
1865 end if;
1866
1867 end plip_validations;
1868
1869
1870 --
1871 ---------------------------opt_ins_val-----------------------------
1872 --
1873 procedure opt_ins_val(p_opt_id in number,
1874 p_effective_date in date,
1875 p_business_group_id in number)
1876 IS
1877 /*
1878 Purpose : To Perform these Validations.
1879 */
1880 l_proc varchar2(72) := g_package||'opt_ins_val';
1881 Begin
1882 if g_debug then
1883 hr_utility.set_location('Entering'||l_proc,5);
1884 hr_utility.set_location('p_opt_id'||p_opt_id,10);
1885 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1886 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1887 end if;
1888
1889 if g_debug then
1890 hr_utility.set_location('Leaving'||l_proc,100);
1891 end if;
1892
1893 end opt_ins_val;
1894
1895 --
1896 ---------------------------opt_upd_val-----------------------------
1897 --
1898 procedure opt_upd_val(p_opt_id in number,
1899 p_effective_date in date,
1900 p_business_group_id in number,
1901 p_mapping_table_pk_id in Number)
1902 IS
1903 /*
1904 Purpose : To Perform these Validations.
1905 1) If Option exists in Program through OIPL then
1906 1. Cannot Update Option Status from Activate to Inactive/Closed/Pending
1907 2. Cannot Update Option Type.
1908 2) User Cannot change Step which is mapped to Option if
1909 Option is mapped to Step and
1910 Option is attached to Grade Ladder through OIPL and
1911 OIPL has Employee placements
1912
1913 */
1914 l_proc Varchar2(72) := g_package||'opt_upd_val';
1915 l_opt_exist_in_pgm Varchar2(1) := 'N';
1916 l_oipl_has_emp_assign Varchar2(1) := 'N';
1917 l_oipl_id Number;
1918 l_point_id Number;
1919
1920 Cursor csr_oipl_id
1921 IS
1922 Select oipl_id
1923 From BEN_OIPL_F
1924 Where opt_id = p_opt_id
1925 AND Business_group_id = p_business_group_id
1926 AND p_effective_date BETWEEN effective_start_date
1927 AND nvl(effective_end_date,hr_general.end_of_time)
1928 And OIPL_STAT_CD = 'A';
1929
1930 Begin
1931 if g_debug then
1932 hr_utility.set_location('Entering'||l_proc,5);
1933 hr_utility.set_location('p_opt_id'||p_opt_id,10);
1934 hr_utility.set_location('p_effective_date'||p_effective_date,15);
1935 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
1936 hr_utility.set_location('p_mapping_table_pk_id'||p_mapping_table_pk_id,25);
1937 hr_utility.set_location('Old mapping_table_pk_id'||ben_opt_shd.g_old_rec.MAPPING_TABLE_PK_ID,30);
1938 end if;
1939
1940
1941 -- Checks OPT has GSP Type and OPT exists in GSP Program
1942 l_opt_exist_in_pgm := chk_opt_exists_in_gsp_pgm(p_opt_id => p_opt_id,
1943 p_effective_date => p_effective_date,
1944 p_Business_Group_Id => p_business_group_id);
1945
1946 if g_debug then
1947 hr_utility.set_location('OPT exists in GSP Program (Y/N) :'||l_opt_exist_in_pgm,55);
1948 end if;
1949
1950 If (l_opt_exist_in_pgm = 'Y') Then
1951
1952 -- Get OIPL Id
1953 Open csr_oipl_id;
1954 Fetch csr_oipl_id into l_oipl_id;
1955 Close csr_oipl_id;
1956 if g_debug then
1957 hr_utility.set_location('OIPL Id :'||l_oipl_id,60);
1958 end if;
1959
1960 If l_oipl_id IS NOT NULL Then
1961
1962 -- Check OIPL has Emp Placements
1963 l_oipl_has_emp_assign := chk_oipl_emp_assign(p_oipl_id => l_oipl_id,
1964 p_effective_date => p_effective_date,
1965 p_business_group_id => p_business_group_id);
1966 if g_debug then
1967 hr_utility.set_location('OIPL has Emp Placements (Y/N) :'||l_oipl_has_emp_assign,70);
1968 end if;
1969
1970 If l_oipl_has_emp_assign = 'Y' Then
1971
1972
1973 if g_debug then
1974 hr_utility.set_location('Old Point Id :'||ben_opt_shd.g_old_rec.MAPPING_TABLE_PK_ID,80);
1975 end if;
1976
1977 -- Cannot change Step which is mapped to Option
1978 If (ben_opt_shd.g_old_rec.MAPPING_TABLE_PK_ID <> p_mapping_table_pk_id) Then
1979 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_OPT_STEP_ID');
1980 hr_utility.raise_error;
1981 End if;
1982
1983
1984 End if; -- l_oipl_has_emp_assign
1985
1986 End if; --l_oipl_id IS NOT NULL
1987
1988
1989 end if; --l_opt_exist_in_pgm
1990
1991 if g_debug then
1992 hr_utility.set_location('Leaving'||l_proc,100);
1993 end if;
1994
1995 end opt_upd_val;
1996
1997 --
1998 ---------------------------opt_del_val-----------------------------
1999 --
2000 procedure opt_del_val(p_opt_id in number,
2001 p_effective_date in date,
2002 p_business_group_id in number)
2003 IS
2004 /*
2005 Purpose : To Perform these Validations.
2006 */
2007 l_proc varchar2(72) := g_package||'opt_del_val';
2008 Begin
2009 if g_debug then
2010 hr_utility.set_location('Entering'||l_proc,5);
2011 hr_utility.set_location('p_opt_id'||p_opt_id,10);
2012 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2013 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
2014 end if;
2015
2016 if g_debug then
2017 hr_utility.set_location('Leaving'||l_proc,100);
2018 end if;
2019
2020 end opt_del_val;
2021
2022
2023 --
2024 ---------------------------opt_validations-----------------------------
2025 --
2026
2027 procedure opt_validations(p_opt_id in number,
2028 p_effective_date in date,
2029 p_dml_operation in varchar2,
2030 p_Business_Group_Id in Number Default hr_general.GET_BUSINESS_GROUP_ID,
2031 p_mapping_table_pk_id in Number Default NULL)
2032 IS
2033 l_proc varchar2(72) := g_package||'plip_validations';
2034 begin
2035 -- validations to be performed in this routine are
2036 -- there should not be any assignment on the step linked if getting deleted or disabled
2037 -- this should be only option linked to the point
2038 -- etc
2039 if g_debug then
2040 hr_utility.set_location('Entering'||l_proc,5);
2041 hr_utility.set_location('p_opt_id'||p_opt_id,10);
2042 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2043 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
2044 hr_utility.set_location('p_dml_operation'||p_dml_operation,25);
2045 hr_utility.set_location('p_mapping_table_pk_id'||p_mapping_table_pk_id,35);
2046 hr_utility.set_location('Old mapping_table_pk_id'||ben_opt_shd.g_old_rec.MAPPING_TABLE_PK_ID,40);
2047 end if;
2048
2049
2050 if p_dml_operation = 'I' Then
2051 if g_debug then
2052 hr_utility.set_location('Perform OPT Insert Validations',45);
2053 end if;
2054
2055 opt_ins_val(p_opt_id => p_opt_id,
2056 p_effective_date => p_effective_date,
2057 p_business_group_id => p_Business_Group_Id);
2058
2059 end if;
2060
2061 if p_dml_operation = 'U' Then
2062 if g_debug then
2063 hr_utility.set_location('Perform OPT Update Validations',50);
2064 end if;
2065
2066 opt_upd_val(p_opt_id => p_opt_id,
2067 p_effective_date => p_effective_date,
2068 p_business_group_id => p_Business_Group_Id,
2069 p_mapping_table_pk_id => p_mapping_table_pk_id);
2070
2071 end if;
2072
2073 if p_dml_operation = 'D' Then
2074 if g_debug then
2075 hr_utility.set_location('Perform OPT Delete Validations',55);
2076 end if;
2077
2078 opt_del_val(p_opt_id => p_opt_id,
2079 p_effective_date => p_effective_date,
2080 p_business_group_id => p_Business_Group_Id);
2081 end if;
2082
2083
2084
2085 if g_debug then
2086 hr_utility.set_location('Leaving'||l_proc,100);
2087 end if;
2088
2089 end opt_validations;
2090
2091 --
2092 ---------------------------oipl_ins_val-----------------------------
2093 --
2094 procedure oipl_ins_val(p_oipl_id in number,
2095 p_effective_date in date)
2096 IS
2097 /*
2098 Purpose : To Perform these Validations.
2099 */
2100 l_proc varchar2(72) := g_package||'oipl_ins_val';
2101 Begin
2102 if g_debug then
2103 hr_utility.set_location('Entering'||l_proc,10);
2104 end if;
2105
2106 if g_debug then
2107 hr_utility.set_location('Leaving'||l_proc,100);
2108 end if;
2109
2110 end oipl_ins_val;
2111
2112 --
2113 ---------------------------oipl_upd_val-----------------------------
2114 --
2115 procedure oipl_upd_val(p_oipl_id in number,
2116 p_effective_date in date,
2117 p_Business_Group_Id in Number,
2118 p_oipl_stat_cd In Varchar2)
2119 IS
2120 /*
2121 Purpose : To Perform these Validations.
2122 1) OIPL Status cannot be changed from Activate to Inactive/Closed/Pending
2123 If OIPL is mapped to Step and the step have employee assignments.
2124
2125 */
2126 l_proc Varchar2(72) := g_package||'oipl_upd_val';
2127 l_status BEN_OIPL_F.OIPL_STAT_CD%Type;
2128 l_emp_exists Varchar2(1) := 'N';
2129 l_oipl_has_gsp_type Varchar2(1) := 'N';
2130
2131 Begin
2132 if g_debug then
2133 hr_utility.set_location('Entering'||l_proc,5);
2134 hr_utility.set_location('p_oipl_id'||p_oipl_id,10);
2135 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2136 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
2137 hr_utility.set_location('p_oipl_stat_cd'||p_oipl_stat_cd,25);
2138 end if;
2139
2140
2141 l_emp_exists := chk_oipl_emp_assign(p_oipl_id => p_oipl_id,
2142 p_effective_date => p_effective_date,
2143 p_business_group_id => p_business_group_id);
2144
2145 if g_debug then
2146 hr_utility.set_location('Step Has Emp Placements (Y/N) : '||l_emp_exists ,30);
2147 end if;
2148
2149 l_oipl_has_gsp_type := chk_oipl_has_gsp_type(p_oipl_id => p_oipl_id,
2150 p_effective_date => p_effective_date,
2151 p_business_group_id=> p_business_group_id);
2152 if g_debug then
2153 hr_utility.set_location('OIPL has GSP Type (Y/N) : '||l_oipl_has_gsp_type ,40);
2154 end if;
2155
2156 if (l_oipl_has_gsp_type = 'Y' And l_emp_exists = 'Y') Then
2157
2158
2159 if g_debug then
2160 hr_utility.set_location('Old OIPL Status : '||ben_cop_shd.g_old_rec.OIPL_STAT_CD,35);
2161 end if;
2162
2163 if (ben_cop_shd.g_old_rec.OIPL_STAT_CD IS NOT NULL And
2164 (ben_cop_shd.g_old_rec.OIPL_STAT_CD = 'A' And l_status <> p_oipl_stat_cd)) Then
2165 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_PLIP_STAT');
2166 hr_utility.raise_error;
2167 End if;
2168
2169
2170 end if;
2171
2172 if g_debug then
2173 hr_utility.set_location('Leaving'||l_proc,100);
2174 end if;
2175
2176 end oipl_upd_val;
2177
2178 --
2179 ---------------------------oipl_del_val-----------------------------
2180 --
2181 procedure oipl_del_val(p_oipl_id in number,
2182 p_effective_date in date,
2183 p_business_group_id In Number)
2184 IS
2185 /*
2186 Purpose : To Perform these Validations.
2187 1) OIPL cannot be deleted if the OIPL is mapped to Step and the Steps have employee assignments.
2188 */
2189 l_proc Varchar2(72) := g_package||'oipl_del_val';
2190 l_emp_exists Varchar2(1):= 'N';
2191 l_oipl_has_gsp_type Varchar2(1):= 'N';
2192 Begin
2193
2194 if g_debug then
2195 hr_utility.set_location('Entering'||l_proc,5);
2196 hr_utility.set_location('p_oipl_id'||p_oipl_id,10);
2197 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2198 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
2199 end if;
2200
2201
2202 l_emp_exists := chk_oipl_emp_assign(p_oipl_id => p_oipl_id,
2203 p_effective_date => p_effective_date,
2204 p_business_group_id => p_business_group_id);
2205
2206 if g_debug then
2207 hr_utility.set_location('Step Has Emp Placements (Y/N) : '||l_emp_exists ,30);
2208 end if;
2209
2210 l_oipl_has_gsp_type := chk_oipl_has_gsp_type(p_oipl_id => p_oipl_id,
2211 p_effective_date => p_effective_date,
2212 p_business_group_id=> p_business_group_id);
2213 if g_debug then
2214 hr_utility.set_location('OIPL has GSP Type (Y/N) : '||l_oipl_has_gsp_type ,40);
2215 end if;
2216
2217 if (l_oipl_has_gsp_type = 'Y' AND l_emp_exists = 'Y') Then
2218 hr_utility.set_message(8302,'PQH_GSP_NOT_DEL_OIPL');
2219 hr_utility.raise_error;
2220 end if;
2221
2222 if g_debug then
2223 hr_utility.set_location('Leaving'||l_proc,100);
2224 end if;
2225
2226 end oipl_del_val;
2227
2228
2229 --
2230 ---------------------------oipl_validations-----------------------------
2231 --
2232
2233 procedure oipl_validations(p_oipl_id in number,
2234 p_dml_operation in varchar2,
2235 p_effective_date in date,
2236 p_Business_Group_Id in Number Default hr_general.GET_BUSINESS_GROUP_ID,
2237 p_oipl_stat_cd in Varchar2 Default 'I')
2238 IS
2239 l_proc varchar2(72) := g_package||'oipl_validations';
2240 begin
2241 -- validations to be performed in this routine are
2242 -- there should not be any assignment on the step linked if getting deleted or disabled
2243 -- this should be only option linked to the point
2244 -- etc
2245
2246 if g_debug then
2247 hr_utility.set_location('Entering'||l_proc,5);
2248 hr_utility.set_location('p_oipl_id'||p_oipl_id,10);
2249 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2250 hr_utility.set_location('p_Business_Group_Id'||p_Business_Group_Id,20);
2251 hr_utility.set_location('p_oipl_stat_cd'||p_oipl_stat_cd,25);
2252 hr_utility.set_location('Old oipl_stat_cd'||ben_cop_shd.g_old_rec.OIPL_STAT_CD,30);
2253 hr_utility.set_location('p_dml_operation'||p_dml_operation,45);
2254 end if;
2255
2256 If p_dml_operation = 'I' Then
2257 if g_debug then
2258 hr_utility.set_location('OIPL Insert Validations',55);
2259 end if;
2260 oipl_ins_val(p_oipl_id => p_oipl_id,
2261 p_effective_date => p_effective_date);
2262 End if;
2263
2264 If p_dml_operation = 'U' Then
2265 if g_debug then
2266 hr_utility.set_location('OIPL Update Validations',65);
2267 end if;
2268 oipl_upd_val(p_oipl_id => p_oipl_id,
2269 p_effective_date => p_effective_date,
2270 p_Business_Group_Id => p_Business_Group_Id,
2271 p_oipl_stat_cd => p_oipl_stat_cd);
2272 End if;
2273
2274 If p_dml_operation = 'D' Then
2275 if g_debug then
2276 hr_utility.set_location('OIPL Delete Validations',75);
2277 end if;
2278 oipl_del_val(p_oipl_id => p_oipl_id,
2279 p_effective_date => p_effective_date,
2280 p_business_group_id => p_business_group_id);
2281 End if;
2282
2283 if g_debug then
2284 hr_utility.set_location('Leaving'||l_proc,100);
2285 end if;
2286
2287 end oipl_validations;
2288
2289 --
2290 ---------------------------abr_ins_val-----------------------------
2291 --
2292 procedure abr_ins_val(p_abr_id in number,
2293 p_effective_date in date,
2294 p_business_group_id IN Number)
2295 IS
2296 /*
2297 Purpose : To Perform these Validations.
2298 */
2299 l_proc varchar2(72) := g_package||'abr_ins_val';
2300 Begin
2301 if g_debug then
2302 hr_utility.set_location('Entering'||l_proc,5);
2303 hr_utility.set_location('p_abr_id'||p_abr_id,10);
2304 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2305 hr_utility.set_location('p_business_group_id'||p_business_group_id,20);
2306 end if;
2307
2308
2309
2310 if g_debug then
2311 hr_utility.set_location('Leaving'||l_proc,100);
2312 end if;
2313
2314 end abr_ins_val;
2315
2316 --
2317 ---------------------------abr_upd_val-----------------------------
2318 --
2319
2320 procedure abr_upd_val(p_abr_id in number,
2321 p_effective_date in date,
2322 p_business_group_id IN Number,
2323 p_pl_id In Number Default NULL,
2324 p_opt_id In Number Default NULL,
2325 p_acty_typ_cd In Varchar2 Default NULL,
2326 p_Acty_Base_RT_Stat_Cd In Varchar2 Default 'I')
2327
2328
2329 IS
2330 /*
2331 Purpose : To Perform these Validations.
2332 1) If Rate is attached to Plan/Option and employees placed on PLIP/OIPL.
2333 1. Cannot Update Standard Rate Status
2334 2. Cannot Update Activity Type
2335
2336 */
2337 l_proc Varchar2(72) := g_package||'abr_upd_val';
2338 l_status BEN_ACTY_BASE_RT_F.ACTY_BASE_RT_STAT_CD%Type;
2339 l_activity BEN_ACTY_BASE_RT_F.ACTY_TYP_CD%Type;
2340 l_plip_id Number;
2341 l_oipl_id Number;
2342
2343 l_pl_exists_in_grdldr Varchar2(1) := 'N';
2344 l_plip_emp_assign Varchar2(1) := 'N';
2345 l_pl_exists Varchar2(1) := 'N';
2346
2347 l_oipl_emp_assign Varchar2(1) := 'N';
2348 l_opt_exists_in_gsp_pgm Varchar2(1) := 'N';
2349 l_opt_exists Varchar2(1) := 'N';
2350
2351
2352
2353
2354 Cursor csr_plip_id
2355 IS
2356 Select PLIP_ID
2357 From BEN_PLIP_F
2358 Where PL_ID = p_pl_id
2359 AND PLIP_STAT_CD = 'A'
2360 And Business_Group_Id = p_business_group_id
2361 And p_effective_date BETWEEN effective_start_date
2362 AND nvl(effective_end_date,hr_general.end_of_time);
2363
2364
2365 Cursor csr_oipl_id
2366 IS
2367 Select OIPL_ID
2368 From BEN_OIPL_F
2369 Where OPT_ID = p_opt_id
2370 And Business_Group_Id = p_business_group_id
2371 And p_effective_date BETWEEN effective_start_date
2372 AND nvl(effective_end_date,hr_general.end_of_time);
2373
2374
2375 Begin
2376 if g_debug then
2377 hr_utility.set_location('Entering'||l_proc,5);
2378 hr_utility.set_location('p_abr_id'||p_abr_id,10);
2379 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2380 hr_utility.set_location('p_business_group_id'||p_business_group_id,20);
2381 hr_utility.set_location('p_pl_id'||p_pl_id,35);
2382 hr_utility.set_location('p_opt_id'||p_opt_id,40);
2383 hr_utility.set_location('p_acty_typ_cd'||p_acty_typ_cd,45);
2384 hr_utility.set_location('p_Acty_Base_RT_Stat_Cd'||p_Acty_Base_RT_Stat_Cd,55);
2385 end if;
2386
2387 -- Check PL Type is GSP
2388 -- And PL exists in PGM through PLIP
2389 -- And PLIP has Emp Placements
2390
2391 If p_pl_id IS NOT NULL Then
2392
2393 l_pl_exists_in_grdldr := chk_pl_exists_in_grdldr(p_pl_id => p_pl_id,
2394 p_effective_date => p_effective_date,
2395 p_Business_Group_Id => p_business_group_id);
2396
2397 if g_debug then
2398 hr_utility.set_location('PL exists in GSP Pgm (Y/N) '||l_pl_exists_in_grdldr,65);
2399 end if;
2400
2401
2402 Open csr_plip_id;
2403 Fetch csr_plip_id into l_plip_id;
2404 Close csr_plip_id;
2405 if g_debug then
2406 hr_utility.set_location('PLIP Id '||l_plip_id,75);
2407 end if;
2408
2409 if l_pl_exists_in_grdldr = 'Y' And l_plip_id IS NOT NULL Then
2410
2411 l_plip_emp_assign := chk_plip_emp_assign(p_plip_id =>l_plip_id,
2412 p_effective_date => p_effective_date,
2413 p_business_group_id => p_business_group_id);
2414 end if;
2415
2416 if g_debug then
2417 hr_utility.set_location('PLIP has Emp Assignments (Y/N) '||l_plip_emp_assign,85);
2418 end if;
2419 End If;
2420 l_pl_exists := l_plip_emp_assign;
2421
2422
2423
2424 -- Check OPT has GSP
2425 -- And OPT exists in PGM through OIPL
2426 -- And OIPL has Emp Placements
2427
2428
2429 If p_opt_id IS NOT NULL Then
2430
2431 l_opt_exists_in_gsp_pgm := chk_opt_exists_in_gsp_pgm(p_opt_id => p_opt_id,
2432 p_effective_date => p_effective_date,
2433 p_Business_Group_Id => p_business_group_id);
2434
2435 if g_debug then
2436 hr_utility.set_location('OPT exists in GSP Pgm (Y/N) '||l_opt_exists_in_gsp_pgm,95);
2437 end if;
2438
2439
2440 Open csr_oipl_id;
2441 Fetch csr_oipl_id into l_oipl_id;
2442 Close csr_oipl_id;
2443 if g_debug then
2444 hr_utility.set_location('OIPL Id '||l_oipl_id,100);
2445 end if;
2446
2447 if l_opt_exists_in_gsp_pgm = 'Y' And l_plip_id IS NOT NULL Then
2448
2449 l_oipl_emp_assign := chk_oipl_emp_assign(p_oipl_id =>l_oipl_id,
2450 p_effective_date => p_effective_date,
2451 p_business_group_id => p_business_group_id);
2452 end if;
2453
2454 if g_debug then
2455 hr_utility.set_location('OIPL has Emp Assignments (Y/N) '||l_oipl_emp_assign,105);
2456 end if;
2457
2458 End If;
2459 l_opt_exists := l_oipl_emp_assign;
2460
2461
2462 If (l_pl_exists = 'Y' OR l_opt_exists = 'Y' ) Then
2463
2464
2465
2466 if g_debug then
2467 hr_utility.set_location('Old Status : '||ben_abr_shd.g_old_rec.ACTY_BASE_RT_STAT_CD,115);
2468 hr_utility.set_location('Old Activity : '||ben_abr_shd.g_old_rec.ACTY_TYP_CD,120);
2469 end if;
2470
2471 -- Cannot Change Status
2472 if ( ben_abr_shd.g_old_rec.ACTY_BASE_RT_STAT_CD='A' And
2473 ben_abr_shd.g_old_rec.ACTY_BASE_RT_STAT_CD <> p_Acty_Base_RT_Stat_Cd) Then
2474 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_ABR_STATUS');
2475 hr_utility.raise_error;
2476 end if;
2477
2478 -- Cannot Change Activity
2479 if ( ben_abr_shd.g_old_rec.ACTY_TYP_CD <> p_acty_typ_cd ) Then
2480 hr_utility.set_message(8302,'PQH_GSP_NOT_UPD_ABR_ACTIVITY');
2481 hr_utility.raise_error;
2482 end if;
2483
2484 End If;
2485
2486 if g_debug then
2487 hr_utility.set_location('Leaving'||l_proc,100);
2488 end if;
2489
2490 end abr_upd_val;
2491
2492 --
2493 ---------------------------abr_del_val-----------------------------
2494 --
2495 procedure abr_del_val(p_abr_id in number,
2496 p_effective_date in date,
2497 p_business_group_id IN Number)
2498 IS
2499 /*
2500 Purpose : To Perform these Validations.
2501 */
2502 l_proc varchar2(72) := g_package||'abr_del_val';
2503 Begin
2504 if g_debug then
2505 hr_utility.set_location('Entering'||l_proc,5);
2506 hr_utility.set_location('p_abr_id'||p_abr_id,10);
2507 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2508 hr_utility.set_location('p_business_group_id'||p_business_group_id,20);
2509 end if;
2510
2511 if g_debug then
2512 hr_utility.set_location('Leaving'||l_proc,100);
2513 end if;
2514
2515 end abr_del_val;
2516
2517
2518 --
2519 ---------------------------abr_validations-----------------------------
2520 --
2521 procedure abr_validations(p_abr_id in number,
2522 p_dml_operation in varchar2,
2523 p_effective_date in date,
2524 p_business_group_id IN Number Default hr_general.GET_BUSINESS_GROUP_ID,
2525 p_pl_id In Number Default NULL,
2526 p_opt_id In Number Default NULL,
2527 p_acty_typ_cd In Varchar2 Default NULL,
2528 p_Acty_Base_RT_Stat_Cd In Varchar2 Default 'I')
2529 IS
2530 /*
2531 Purpose : To Perform these Validations.
2532 */
2533 l_proc Varchar2(72) := g_package||'abr_validations';
2534 l_activity_type BEN_ACTY_BASE_RT_F.ACTY_TYP_CD%Type;
2535
2536 Cursor csr_activity_type
2537 IS
2538 Select ACTY_TYP_CD
2539 From BEN_ACTY_BASE_RT_F
2540 Where ACTY_BASE_RT_ID = p_abr_id
2541 And Business_Group_Id = p_business_group_id
2542 And p_effective_date BETWEEN effective_start_date
2543 AND nvl(effective_end_date,hr_general.end_of_time);
2544
2545
2546 begin
2547 -- validations to be performed in this routine are
2548 -- this should be only rate linked to the point or grade
2549 -- etc
2550 if g_debug then
2551 hr_utility.set_location('Entering'||l_proc,5);
2552 hr_utility.set_location('p_abr_id'||p_abr_id,10);
2553 hr_utility.set_location('p_effective_date'||p_effective_date,15);
2554 hr_utility.set_location('p_business_group_id'||p_business_group_id,20);
2555 hr_utility.set_location('p_dml_operation'||p_dml_operation,25);
2556 hr_utility.set_location('p_pl_id'||p_pl_id,35);
2557 hr_utility.set_location('p_opt_id'||p_opt_id,40);
2558 hr_utility.set_location('p_acty_typ_cd'||p_acty_typ_cd,45);
2559 hr_utility.set_location('Old acty_typ_cd'||ben_abr_shd.g_old_rec.ACTY_TYP_CD,46);
2560 hr_utility.set_location('p_Acty_Base_RT_Stat_Cd'||p_Acty_Base_RT_Stat_Cd,55);
2561 hr_utility.set_location('Old Acty_Base_RT_Stat_Cd'||ben_abr_shd.g_old_rec.ACTY_BASE_RT_STAT_CD,65);
2562 end if;
2563
2564 -- Get Activity Type
2565 Open csr_activity_type;
2566 Fetch csr_activity_type into l_activity_type;
2567 Close csr_activity_type;
2568 if g_debug then
2569 hr_utility.set_location('Activity Type'||l_activity_type,55);
2570 end if;
2571
2572 if l_activity_type = 'GSPSA' Then
2573
2574 if p_dml_operation = 'I' then
2575 if g_debug then
2576 hr_utility.set_location('Perform ABR Insert Validations',60);
2577 end if;
2578 abr_ins_val(p_abr_id => p_abr_id,
2579 p_effective_date => p_effective_date,
2580 p_business_group_id => p_business_group_id);
2581 end if;
2582
2583
2584 if p_dml_operation = 'U' then
2585 if g_debug then
2586 hr_utility.set_location('Perform ABR Update Validations',70);
2587 end if;
2588
2589
2590 abr_upd_val(p_abr_id => p_abr_id,
2591 p_effective_date => p_effective_date,
2592 p_business_group_id => p_business_group_id,
2593 p_pl_id => p_pl_id,
2594 p_opt_id => p_opt_id,
2595 p_acty_typ_cd => p_acty_typ_cd,
2596 p_Acty_Base_RT_Stat_Cd => p_Acty_Base_RT_Stat_Cd);
2597 end if;
2598
2599
2600
2601 if p_dml_operation = 'D' then
2602 if g_debug then
2603 hr_utility.set_location('Perform ABR Delete Validations',80);
2604 end if;
2605 abr_del_val(p_abr_id => p_abr_id,
2606 p_effective_date => p_effective_date,
2607 p_business_group_id => p_business_group_id);
2608 end if;
2609
2610 end if;
2611 if g_debug then
2612 hr_utility.set_location('Leaving'||l_proc,100);
2613 end if;
2614
2615 end abr_validations;
2616 end pqh_gsp_ben_validations;
[Home] [Help]