[Home] [Help]
PACKAGE BODY: APPS.PER_GRADES_PKG
Source
1 package body PER_GRADES_PKG as
2 /* $Header: pegrd01t.pkb 120.0 2005/05/31 09:29:18 appldev noship $ */
3
4 -- Standard insert procedure
5
6 procedure insert_row(
7 p_row_id in out nocopy varchar2,
8 p_grade_id in out nocopy number,
9 p_business_group_id number,
10 p_grade_definition_id number,
11 p_date_from date,
12 p_sequence number,
13 p_comments varchar2,
14 p_date_to date,
15 p_name varchar2,
16 p_request_id number,
17 p_program_application_id number,
18 p_program_id number,
19 p_program_update_date date,
20 p_attribute_category varchar2,
21 p_attribute1 varchar2,
22 p_attribute2 varchar2,
23 p_attribute3 varchar2,
24 p_attribute4 varchar2,
25 p_attribute5 varchar2,
26 p_attribute6 varchar2,
27 p_attribute7 varchar2,
28 p_attribute8 varchar2,
29 p_attribute9 varchar2,
30 p_attribute10 varchar2,
31 p_attribute11 varchar2,
32 p_attribute12 varchar2,
33 p_attribute13 varchar2,
34 p_attribute14 varchar2,
35 p_attribute15 varchar2,
36 p_attribute16 varchar2,
37 p_attribute17 varchar2,
38 p_attribute18 varchar2,
39 p_attribute19 varchar2,
40 p_attribute20 varchar2,
41 p_language_code varchar2 default hr_api.userenv_lang) IS
42 cursor c1 is
43 select per_grades_s.nextval
44 from sys.dual;
45 cursor c2 is
46 select rowid
47 from per_grades
48 where grade_id = p_grade_id;
49 begin
50 open c1;
51 fetch c1 into p_grade_id;
52 close c1;
53
54
55 BEGIn
56 insert into per_grades (
57 grade_id,
58 business_group_id,
59 grade_definition_id,
60 date_from,
61 sequence,
62 comments,
63 date_to,
64 name,
65 request_id,
66 program_application_id,
67 program_id,
68 program_update_date,
69 attribute_category,
70 attribute1,
71 attribute2,
72 attribute3,
73 attribute4,
74 attribute5,
75 attribute6,
76 attribute7,
77 attribute8,
78 attribute9,
79 attribute10,
80 attribute11,
81 attribute12,
82 attribute13,
83 attribute14,
84 attribute15,
85 attribute16,
86 attribute17,
87 attribute18,
88 attribute19,
89 attribute20)
90 values (
91 p_grade_id,
92 p_business_group_id,
93 p_grade_definition_id,
94 p_date_from,
95 p_sequence,
96 p_comments,
97 p_date_to,
98 p_name,
99 p_request_id,
100 p_program_application_id,
101 p_program_id,
102 p_program_update_date,
103 p_attribute_category,
104 p_attribute1,
105 p_attribute2,
106 p_attribute3,
107 p_attribute4,
108 p_attribute5,
109 p_attribute6,
110 p_attribute7,
111 p_attribute8,
112 p_attribute9,
113 p_attribute10,
114 p_attribute11,
115 p_attribute12,
116 p_attribute13,
117 p_attribute14,
118 p_attribute15,
119 p_attribute16,
120 p_attribute17,
121 p_attribute18,
122 p_attribute19,
123 p_attribute20);
124
125 end;
126
127 open c2;
128 fetch c2 into P_ROW_ID;
129 close c2;
130
131 --
132 -- MLS Processing
133 --
134 per_gdt_ins.ins_tl
135 (p_language_code => p_language_code
136 ,p_grade_id => p_grade_id
137 ,p_name => p_name
138 );
139
140 end insert_row;
141
142
143 --standard delete procedure
144
145 procedure delete_row(p_row_id varchar2,
146 p_grd_id in number) is
147 begin
148 stbdelvl(p_grd_id);
149 begin
150 delete from per_grades g
151 where g.rowid = chartorowid(P_ROW_ID);
152 end;
153
154 --
155 -- MLS Processing
156 --
157 per_gdt_del.del_tl(p_grade_id => p_grd_id);
158 --
159
160 end delete_row;
161
162
163 --standard lock procedure
164
165 procedure lock_row (
166 p_row_id varchar2,
167 p_grade_id number,
168 p_business_group_id number,
169 p_grade_definition_id number,
170 p_date_from date,
171 p_sequence number,
172 p_comments varchar2,
173 p_date_to date,
174 p_name varchar2,
175 p_request_id number,
176 p_program_application_id number,
177 p_program_id number,
178 p_program_update_date date,
179 p_attribute_category varchar2,
180 p_attribute1 varchar2,
181 p_attribute2 varchar2,
182 p_attribute3 varchar2,
183 p_attribute4 varchar2,
184 p_attribute5 varchar2,
185 p_attribute6 varchar2,
186 p_attribute7 varchar2,
187 p_attribute8 varchar2,
188 p_attribute9 varchar2,
189 p_attribute10 varchar2,
190 p_attribute11 varchar2,
191 p_attribute12 varchar2,
192 p_attribute13 varchar2,
193 p_attribute14 varchar2,
194 p_attribute15 varchar2,
195 p_attribute16 varchar2,
196 p_attribute17 varchar2,
197 p_attribute18 varchar2,
198 p_attribute19 varchar2,
199 p_attribute20 varchar2,
200 p_language_code varchar2 default hr_api.userenv_lang) IS
201
202 cursor OPM_CUR is
203 select *
204 from per_grades_vl g
205 where g.row_id = chartorowid(P_ROW_ID)
206 for update of grade_id nowait;
207
208 OPM_REC OPM_CUR%rowtype;
209
210 begin
211 open OPM_CUR;
212 fetch OPM_CUR into OPM_REC;
213 close OPM_CUR;
214
215 opm_rec.attribute13 := rtrim(opm_rec.attribute13);
216 opm_rec.attribute14 := rtrim(opm_rec.attribute14);
217 opm_rec.attribute15 := rtrim(opm_rec.attribute15);
218 opm_rec.attribute16 := rtrim(opm_rec.attribute16);
219 opm_rec.attribute17 := rtrim(opm_rec.attribute17);
220 opm_rec.attribute18 := rtrim(opm_rec.attribute18);
221 opm_rec.attribute19 := rtrim(opm_rec.attribute19);
222 opm_rec.attribute20 := rtrim(opm_rec.attribute20);
223 opm_rec.comments := rtrim(opm_rec.comments);
224 opm_rec.name := rtrim(opm_rec.name);
225 opm_rec.attribute_category := rtrim(opm_rec.attribute_category);
226 opm_rec.attribute1 := rtrim(opm_rec.attribute1);
227 opm_rec.attribute2 := rtrim(opm_rec.attribute2);
228 opm_rec.attribute3 := rtrim(opm_rec.attribute3);
229 opm_rec.attribute4 := rtrim(opm_rec.attribute4);
230 opm_rec.attribute5 := rtrim(opm_rec.attribute5);
231 opm_rec.attribute6 := rtrim(opm_rec.attribute6);
232 opm_rec.attribute7 := rtrim(opm_rec.attribute7);
233 opm_rec.attribute8 := rtrim(opm_rec.attribute8);
234 opm_rec.attribute9 := rtrim(opm_rec.attribute9);
235 opm_rec.attribute10 := rtrim(opm_rec.attribute10);
236 opm_rec.attribute11 := rtrim(opm_rec.attribute11);
237 opm_rec.attribute12 := rtrim(opm_rec.attribute12);
238
239 IF (((opm_rec.grade_id = p_grade_id)
240 or (opm_rec.grade_id is null
241 and (p_grade_id is null)))
242 and ((opm_rec.business_group_id = p_business_group_id)
243 or (opm_rec.business_group_id is null
244 and (p_business_group_id is null)))
245 and ((opm_rec.grade_definition_id = p_grade_definition_id)
246 or (opm_rec.grade_definition_id is null
247 and (p_grade_definition_id is null)))
248 and ((opm_rec.date_from = p_date_from)
249 or (opm_rec.date_from is null
250 and (p_date_from is null)))
251 and ((opm_rec.sequence = p_sequence)
252 or (opm_rec.sequence is null
253 and (p_sequence is null)))
254 and ((opm_rec.comments = p_comments)
255 or (opm_rec.comments is null
256 and (p_comments is null)))
257 and ((opm_rec.date_to = p_date_to)
258 or (opm_rec.date_to is null
259 and (p_date_to is null)))
260 and ((opm_rec.name = p_name)
261 or (opm_rec.name is null
262 and (p_name is null)))
263 and ((opm_rec.request_id = p_request_id)
264 or (opm_rec.request_id is null
265 and (p_request_id is null)))
266 and ((opm_rec.program_application_id = p_program_application_id)
267 or (opm_rec.program_application_id is null
268 and (p_program_application_id is null)))
269 and ((opm_rec.program_id = p_program_id)
270 or (opm_rec.program_id is null
271 and (p_program_id is null)))
272 and ((opm_rec.program_update_date = p_program_update_date)
273 or (opm_rec.program_update_date is null
274 and (p_program_update_date is null)))
275 and ((opm_rec.attribute_category = p_attribute_category)
276 or (opm_rec.attribute_category is null
277 and (p_attribute_category is null)))
278 and ((opm_rec.attribute1 = p_attribute1)
279 or (opm_rec.attribute1 is null
280 and (p_attribute1 is null)))
281 and ((opm_rec.attribute2 = p_attribute2)
282 or (opm_rec.attribute2 is null
283 and (p_attribute2 is null)))
284 and ((opm_rec.attribute3 = p_attribute3)
285 or (opm_rec.attribute3 is null
286 and (p_attribute3 is null)))
287 and ((opm_rec.attribute4 = p_attribute4)
288 or (opm_rec.attribute4 is null
289 and (p_attribute4 is null)))
290 and ((opm_rec.attribute5 = p_attribute5)
291 or (opm_rec.attribute5 is null
292 and (p_attribute5 is null)))
293 and ((opm_rec.attribute6 = p_attribute6)
294 or (opm_rec.attribute6 is null
295 and (p_attribute6 is null)))
296 and ((opm_rec.attribute7 = p_attribute7)
297 or (opm_rec.attribute7 is null
298 and (p_attribute7 is null)))
299 and ((opm_rec.attribute8 = p_attribute8)
300 or (opm_rec.attribute8 is null
301 and (p_attribute8 is null)))
302 and ((opm_rec.attribute9 = p_attribute9)
303 or (opm_rec.attribute9 is null
304 and (p_attribute9 is null)))
305 and ((opm_rec.attribute10 = p_attribute10)
306 or (opm_rec.attribute10 is null
307 and (p_attribute10 is null)))
308 and ((opm_rec.attribute11 = p_attribute11)
309 or (opm_rec.attribute11 is null
310 and (p_attribute11 is null)))
311 and ((opm_rec.attribute12 = p_attribute12)
312 or (opm_rec.attribute12 is null
313 and (p_attribute12 is null)))
314 and ((opm_rec.attribute13 = p_attribute13)
315 or (opm_rec.attribute13 is null
316 and (p_attribute13 is null)))
317 and ((opm_rec.attribute14 = p_attribute14)
318 or (opm_rec.attribute14 is null
319 and (p_attribute14 is null)))
320 and ((opm_rec.attribute15 = p_attribute15)
321 or (opm_rec.attribute15 is null
322 and (p_attribute15 is null)))
323 and ((opm_rec.attribute16 = p_attribute16)
324 or (opm_rec.attribute16 is null
325 and (p_attribute16 is null)))
326 and ((opm_rec.attribute17 = p_attribute17)
327 or (opm_rec.attribute17 is null
328 and (p_attribute17 is null)))
329 and ((opm_rec.attribute18 = p_attribute18)
330 or (opm_rec.attribute18 is null
331 and (p_attribute18 is null)))
332 and ((opm_rec.attribute19 = p_attribute19)
333 or (opm_rec.attribute19 is null
334 and (p_attribute19 is null)))
335 and ((opm_rec.attribute20 = p_attribute20)
336 or (opm_rec.attribute20 is null
337 and (p_attribute20 is null))) )
338 THEN
339 --
340 -- MLS Processing
341 --
342 per_gdt_shd.lck (p_grade_id => p_grade_id
343 ,p_language => p_language_code);
344
345 return;
346 END IF;
347
348 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
349 app_exception.raise_exception;
350
351 end lock_row;
352
353
354 --standard update procedure
355
356 procedure update_row(
357 p_row_id varchar2,
358 p_grade_id number,
359 p_business_group_id number,
360 p_grade_definition_id number,
361 p_date_from date,
362 p_sequence number,
363 p_comments varchar2,
364 p_date_to date,
365 p_name varchar2,
366 p_request_id number,
367 p_program_application_id number,
368 p_program_id number,
369 p_program_update_date date,
370 p_attribute_category varchar2,
371 p_attribute1 varchar2,
372 p_attribute2 varchar2,
373 p_attribute3 varchar2,
374 p_attribute4 varchar2,
375 p_attribute5 varchar2,
376 p_attribute6 varchar2,
377 p_attribute7 varchar2,
378 p_attribute8 varchar2,
379 p_attribute9 varchar2,
380 p_attribute10 varchar2,
381 p_attribute11 varchar2,
382 p_attribute12 varchar2,
383 p_attribute13 varchar2,
384 p_attribute14 varchar2,
385 p_attribute15 varchar2,
386 p_attribute16 varchar2,
387 p_attribute17 varchar2,
388 p_attribute18 varchar2,
389 p_attribute19 varchar2,
390 p_attribute20 varchar2,
391 p_language_code varchar2 default hr_api.userenv_lang) is
392
393 begin
394 update per_grades g
395 set
396 g.grade_id = p_grade_id,
397 g.business_group_id = p_business_group_id,
398 g.grade_definition_id = p_grade_definition_id,
399 g.date_from = p_date_from,
400 g.sequence = p_sequence,
401 g.comments = p_comments,
402 g.date_to = p_date_to,
403 g.name = p_name,
404 g.request_id = p_request_id,
405 g.program_application_id = p_program_application_id,
406 g.program_id = p_program_id,
407 g.program_update_date = p_program_update_date,
408 g.attribute_category = p_attribute_category,
409 g.attribute1 = p_attribute1,
410 g.attribute2 = p_attribute2,
411 g.attribute3 = p_attribute3,
412 g.attribute4 = p_attribute4,
413 g.attribute5 = p_attribute5,
414 g.attribute6 = p_attribute6,
415 g.attribute7 = p_attribute7,
416 g.attribute8 = p_attribute8,
417 g.attribute9 = p_attribute9,
418 g.attribute10 = p_attribute10,
419 g.attribute11 = p_attribute11,
420 g.attribute12 = p_attribute12,
421 g.attribute13 = p_attribute13,
422 g.attribute14 = p_attribute14,
423 g.attribute15 = p_attribute15,
424 g.attribute16 = p_attribute16,
425 g.attribute17 = p_attribute17,
426 g.attribute18 = p_attribute18,
427 g.attribute19 = p_attribute19,
428 g.attribute20 = p_attribute20
429 where g.rowid = chartorowid(P_ROW_ID);
430
431 --
432 -- MLS Processing
433 --
434 per_gdt_upd.upd_tl
435 (p_language_code => p_language_code
436 ,p_grade_id => p_grade_id
437 ,p_name => p_name
438 );
439
440 end update_row;
441
442
443 --deletion validation coding for block level trigger (STB_DEL_VALIDATION)
444 --block GRD1
445 --KLS 4/11/93
446
447 PROCEDURE stbdelvl (p_grd_id IN NUMBER) IS
448 l_exists VARCHAR2(1);
449
450 cursor c1 is
451 select 'x'
452 FROM per_all_assignments_f
453 WHERE grade_id = p_grd_id;
454 --
455 cursor c2 is
456 select 'x'
457 FROM per_valid_grades
458 WHERE grade_id = p_grd_id;
459 --
460 cursor c3 is
461 select 'x'
462 FROM per_vacancies
463 WHERE grade_id = p_grd_id;
464 --
465 cursor c4 is
466 select 'x'
467 FROM pay_element_links
468 WHERE grade_id = p_grd_id;
469 --
470 cursor c5 is
471 select 'x'
472 FROM per_budget_elements
473 WHERE grade_id = p_grd_id;
474 --
475 cursor c6 is
476 select 'x'
477 FROM per_grade_spines
478 WHERE grade_id = p_grd_id;
479 --
480 cursor c7 is
481 select 'x'
482 FROM pay_grade_rules
483 WHERE grade_or_spinal_point_id = p_grd_id
484 AND rate_type = 'G';
485 --
486 begin
487 --
488 hr_utility.set_location('per_grades_pkg.stbdelvl',1);
489 --
490 open c1;
491 --
492 fetch c1 into l_exists;
493 IF c1%found THEN
494 hr_utility.set_message(801, 'PER_7834_DEF_GRADE_DEL_ASSIGN');
495 close c1;
496 hr_utility.raise_error;
497 END IF;
498 --
499 close c1;
500 --
501 hr_utility.set_location('per_grades_pkg.stbdelvl',2);
502 --
503 open c2;
504 --
505 fetch c2 into l_exists;
506 IF c2%found THEN
507 hr_utility.set_message(801, 'HR_6443_GRADE_DEL_VALID_GRADES');
508 close c2;
509 hr_utility.raise_error;
510 END IF;
511 --
512 close c2;
513 --
514 hr_utility.set_location('per_grades_pkg.stbdelvl',3);
515 --
516 open c3;
517 --
518 fetch c3 into l_exists;
519 IF c3%found THEN
520 hr_utility.set_message(801, 'HR_6444_GRADE_DEL_VACANCIES');
521 close c3;
522 hr_utility.raise_error;
523 END IF;
524 --
525 close c3;
526 --
527 hr_utility.set_location('per_grades_pkg.stbdelvl',4);
528 --
529 open c4;
530 --
531 fetch c4 into l_exists;
532 IF c4%found THEN
533 hr_utility.set_message(801, 'HR_6446_DEL_ELE_LINKS');
534 close c4;
535 hr_utility.raise_error;
536 END IF;
537 --
538 close c4;
539 --
540 hr_utility.set_location('per_grades_pkg.stbdelvl',5);
541 --
542 open c5;
543 --
544 fetch c5 into l_exists;
545 IF c5%found THEN
546 hr_utility.set_message(801, 'HR_6447_GRADE_DEL_BUDGET_ELE');
547 close c5;
548 hr_utility.raise_error;
549 END IF;
550 --
551 close c5;
552 --
553 hr_utility.set_location('per_grades_pkg.stbdelvl',6);
554 --
555 open c6;
556 --
557 fetch c6 into l_exists;
558 IF c6%found THEN
559 hr_utility.set_message(801, 'HR_6448_GRADE_DEL_GRADE_SPINES');
560 close c6;
561 hr_utility.raise_error;
562 END IF;
563 --
564 close c6;
565 --
566 hr_utility.set_location('per_grades_pkg.stbdelvl',7);
567 --
568 open c7;
569 --
570 fetch c7 into l_exists;
571 IF c7%found THEN
572 hr_utility.set_message(801, 'HR_6684_GRADE_RULES');
573 close c7;
574 hr_utility.raise_error;
575 END IF;
576 --
577 close c7;
578 --
579 END stbdelvl;
580
581
582
583 --procedure to deal with all post update activities on block grd1
584 PROCEDURE postup1 (p_seq IN NUMBER,
585 p_s_seq IN OUT NOCOPY NUMBER,
586 p_lastup IN NUMBER,
587 p_login IN NUMBER,
588 p_grd_id IN NUMBER,
589 p_bgroup IN NUMBER,
590 l_exists OUT NOCOPY VARCHAR2) IS
591
592 BEGIN
593 l_exists := 'N';
594 IF p_seq <> p_s_seq THEN
595 BEGIN
596 UPDATE pay_grade_rules_f
597 SET sequence = p_seq,
598 last_update_date = SYSDATE,
599 last_updated_by = p_lastup,
600 last_update_login = p_login
601 WHERE grade_or_spinal_point_id = p_grd_id
602 AND rate_type = 'G';
603 END;
604 END IF;
605 p_s_seq := p_seq;
606 BEGIN
607 SELECT 'Y'
608 INTO l_exists
609 FROM sys.dual
610 WHERE EXISTS
611 (select 'x'
612 FROM per_valid_grades vg
613 WHERE vg.business_group_id = p_bgroup
614 AND vg.grade_id = p_grd_id);
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN null;
617 END;
618 END postup1;
619
620
621 PROCEDURE postup2(p_grd_id IN NUMBER,
622 p_bgroup IN NUMBER,
623 p_date_from IN DATE,
624 p_date_to IN DATE,
625 p_eot IN DATE,
626 p_date_to_old IN DATE) IS
627
628 BEGIN
629
630 DELETE FROM per_valid_grades vg
631 WHERE vg.business_group_id + 0 = p_bgroup
632 AND vg.grade_id = p_grd_id
633 AND vg.date_from > p_date_to;
634 --
635 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions) Date tracked position req.
636 --
637 UPDATE per_valid_grades vg
638 SET vg.date_to = (select least(
639 nvl(p.date_end,p_eot),
640 nvl(j.date_to,p_eot),
641 nvl(p_date_to,p_eot))
642 from hr_positions p,
643 per_jobs_v j,
644 per_valid_grades v
645 where v.valid_grade_id = vg.valid_grade_id
646 and v.position_id = p.position_id(+)
647 and v.job_id = j.job_id(+)
648 )
649 WHERE vg.business_group_id + 0 = p_bgroup
650 AND vg.grade_id = p_grd_id
651 AND (NVL(vg.date_to,p_eot) > p_date_to
652 OR vg.date_to = p_date_to_old);
653
654 UPDATE per_valid_grades vg
655 SET vg.date_to = null
656 WHERE vg.date_to = p_eot
657 AND vg.grade_id = p_grd_id;
658
659
660 END postup2;
661
662
663
664 --This procedure contains locates the grade structure that is in use by the
665 --current business group
666 --KLS 3/11/93
667 PROCEDURE gstruct (p_b_group IN NUMBER,
668 p_s_def_col IN OUT NOCOPY VARCHAR2) IS
669 l_g_str VARCHAR2(150);
670
671 cursor c10 is
672 SELECT a.grade_structure
673 FROM per_business_groups a,
674 fnd_compiled_id_flexs fcf,
675 fnd_id_flex_structures fif
676 WHERE a.business_group_id + 0 = p_b_group
677 AND fcf.id_flex_code = 'GRD'
678 AND fcf.application_id = fif.application_id
679 /* FIX for WWBUG 1523904 */
680 AND to_char(fif.id_flex_num) = a.grade_structure
681 /* End of fix for 1523904 */
682 AND UPPER(fif.dynamic_inserts_allowed_flag) = 'Y'
683 AND rownum = 1;
684 --
685 begin
686 --
687 hr_utility.set_location('per_grades_pkg.gstruct',1);
688 --
689 open c10;
690 --
691 fetch c10 into l_g_str;
692 IF c10%notfound THEN
693 hr_utility.set_message(801,'HR_6788_GR_NO_DYN_INS');
694 close c10;
695 hr_utility.raise_error;
696 END IF;
697 --
698 close c10;
699 --
700 p_s_def_col := l_g_str;
701 --
702 END gstruct;
703
704
705
706 --called from post_change,pre_insert,pre_update on GRD1.DATE_FROM
707 --makes sure date_from of grade is not after date_from on any existing
708 --valid grades
709 PROCEDURE b_check_grade_date_from (p_grd_id IN NUMBER,p_date_from IN DATE) IS
710 l_exists VARCHAR2(1);
711
712 cursor c11 is
713 SELECT 'x'
714 FROM per_valid_grades
715 WHERE grade_id = p_grd_id
716 AND p_date_from > date_from;
717 --
718 begin
719 --
720 hr_utility.set_location('per_grades_pkg.b_check_grade_date_from',1);
721 --
722 open c11;
723 --
724 fetch c11 into l_exists;
725 IF c11%found THEN
726 hr_utility.set_message(801, 'PER_7808_DEF_GRADE_FIRST_VALID');
727 close c11;
728 hr_utility.raise_error;
729 END IF;
730 --
731 close c11;
732 --
733 END b_check_grade_date_from;
734
735
736
737 PROCEDURE chk_flex_def (p_rwid IN VARCHAR2,
738 p_grd_id IN NUMBER,
739 p_bgroup_id IN NUMBER,
740 p_grdef_id IN NUMBER) IS
741 l_exists VARCHAR2(1);
742
743 cursor c12 is
744 select 'x'
745 FROM per_grades p
746 WHERE (p.ROWID <> p_rwid OR p_rwid IS NULL)
747 AND (p.grade_id <> p_grd_id OR p_grd_id IS NULL)
748 AND p.business_group_id + 0 = p_bgroup_id
749 AND p.grade_definition_id = p_grdef_id;
750 --
751 begin
752 --
753 hr_utility.set_location('per_grades_pkg.chk_flex_def',1);
754 --
755 open c12;
756 --
757 fetch c12 into l_exists;
758 IF c12%found THEN
759 hr_utility.set_message(801, 'PER_7830_DEF_GRADE_EXISTS');
760 close c12;
761 hr_utility.raise_error;
762 END IF;
763 --
764 close c12;
765 --
766 END chk_flex_def;
767
768
769
770 --uniqueness check on grade name, within the business group.
771
772 PROCEDURE chk_grade (p_rwid IN VARCHAR2,
773 p_bgroup_id IN NUMBER,
774 p_seg IN VARCHAR2,
775 p_popid OUT NOCOPY VARCHAR2,
776 p_fail OUT NOCOPY BOOLEAN) IS
777
778 cursor c14 is
779 SELECT 'Y'
780 FROM per_grades p
781 WHERE (p_rwid IS NULL OR p_rwid <> p.ROWID)
782 AND p_bgroup_id = p.business_group_id + 0
783 AND p_seg = p.name;
784 --
785 begin
786 --
787 hr_utility.set_location('per_grades_pkg.chk_grade',1);
788 --
789 open c14;
790 --
791 fetch c14 into p_popid;
792 IF c14%found THEN
793 p_fail := TRUE;
794 END IF;
795 --
796 close c14;
797 --
798 END chk_grade;
799
800
801 procedure old_date_to(p_grd_id IN NUMBER,
802 p_old_date IN OUT NOCOPY DATE) is
803
804 cursor c15 is
805 select date_to
806 from per_grades
807 where grade_id = p_grd_id;
808 --
809 begin
810 --
811 hr_utility.set_location('per_grades_pkg.old_date_to',1);
812 --
813 open c15;
814 --
815 fetch c15 into p_old_date;
816 --
817 close c15;
818 --
819 end old_date_to;
820
821
822
823 procedure chk_seq(p_rwid in varchar2,
824 p_bgroup in number,
825 p_seq in number) is
826 l_exists varchar2(1);
827
828 cursor c16 is
829 select 'x'
830 from per_grades g
831 where g.sequence = p_seq
832 and g.business_group_id + 0 = p_bgroup
833 and (p_rwid is null or chartorowid(p_rwid) <> g.rowid);
834 --
835 begin
836 --
837 hr_utility.set_location('per_grades_pkg.chk_seq',1);
838 --
839 open c16;
840 --
841 fetch c16 into l_exists;
842 IF c16%found THEN
843 hr_utility.set_message(801,'HR_7127_GRADE_DUP_SEQ');
844 close c16;
845 hr_utility.raise_error;
846 END IF;
847 --
848 close c16;
849 --
850 end chk_seq;
851
852 procedure chk_date_from(p_grade_id in number,
853 p_date_from in date) is
854 l_exists varchar2(1);
855
856 -- bug 4024588.
857 -- cursor modified to fetch only grade rate records from
858 -- pay_grade_rules_f table.
859
860 cursor chk_grd_dt_from is
861 select 'x' from pay_grade_rules_f p
862 where p.grade_or_spinal_point_id = p_grade_id
863 and p.rate_type='G' -- Bug fix 4024588
864 and p.effective_start_date < nvl(p_date_from, hr_api.g_sot);
865 --
866 begin
867 --
868 hr_utility.set_location('per_grades_pkg.chk_date_from', 1);
869 --
870 open chk_grd_dt_from;
871 fetch chk_grd_dt_from into l_exists;
872 IF chk_grd_dt_from%found THEN
873 hr_utility.set_message(800, 'PER_289567_GRADE_DATE_FROM');
874 close chk_grd_dt_from;
875 hr_utility.raise_error;
876 END IF;
877 --
878 close chk_grd_dt_from;
879 --
880 end chk_date_from;
881
882
883 procedure chk_end_date(p_grade_id in number,
884 p_date_to in date) is
885 l_exists varchar2(1);
886
887 cursor chk_grd_dt_to is
888 select 'x' from pay_grade_rules_f p
889 where p.grade_or_spinal_point_id = p_grade_id
890 and p.rate_type='G' -- Bug fix 3360504. Type check added.
891 and p.effective_end_date > nvl(p_date_to, hr_api.g_eot);
892
893 -- Bug fix 3360504 starts here
894 -- cursor to check whether any grade steps exist after the
895 -- end date.
896 cursor chk_grade_scale is
897 select 'x' from per_grade_spines_f pgs
898 where pgs.grade_id = p_grade_id
899 and pgs.effective_end_date > nvl(p_date_to, hr_api.g_eot);
900
901
902 --
903 begin
904 --
905 hr_utility.set_location('per_grades_pkg.chk_end_date', 1);
906 --
907 open chk_grd_dt_to;
908 fetch chk_grd_dt_to into l_exists;
909 IF chk_grd_dt_to%found THEN
910 hr_utility.set_message(800, 'PER_289568_GRADE_DATE_TO');
911 close chk_grd_dt_to;
912 hr_utility.raise_error;
913 END IF;
914 --
915 close chk_grd_dt_to;
916 --
917 -- Bug fix 3360504 starts here.
918 -- code to check whether grade scale exist.
919 open chk_grade_scale;
920 fetch chk_grade_scale into l_exists;
921 if chk_grade_scale%found then
922 hr_utility.set_message(800, 'PER_449143_GRADE_SCALE_DATE_TO');
923 close chk_grade_scale;
924 hr_utility.raise_error;
925 end if;
926 close chk_grade_scale;
927 --
928 -- bug fix 3360504 ends here
929
930 end chk_end_date;
931
932 end PER_GRADES_PKG;