[Home] [Help]
PACKAGE BODY: APPS.HR_CAGR_GRADES_API
Source
1 Package Body hr_cagr_grades_api as
2 /* $Header: pegraapi.pkb 115.9 2004/04/20 03:11:15 adudekul ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_cagr_grades_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< update_concat_segs >---------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 PROCEDURE update_concat_segs
13 (p_cagr_grade_def_id IN per_cagr_grades_def.cagr_grade_def_id%TYPE
14 ,p_concatenated_segments IN per_cagr_grades_def.concatenated_segments%TYPE) is
15 --
16 l_proc VARCHAR2(72) := g_package||'update_concat_segs';
17 l_concat_segments per_cagr_grades_def.concatenated_segments%TYPE;
18 l_cagr_found varchar2(1) := 'N';
19 --
20 CURSOR get_concat_segs IS
21 SELECT concatenated_segments
22 FROM per_cagr_grades_def
23 WHERE cagr_grade_def_id = p_cagr_grade_def_id;
24 --
25 procedure update_concat_segs_auto
26 (p_cagr_grade_def_id in number) is
27 PRAGMA AUTONOMOUS_TRANSACTION;
28 --
29 CURSOR csr_cagr_lock is
30 SELECT null
31 FROM per_cagr_grades_def
32 where cagr_grade_def_id = p_cagr_grade_def_id
33 for update nowait;
34 --
35 l_exists varchar2(30);
36 l_proc varchar2(72) := g_package||'update_concat_segs_auto';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 open csr_cagr_lock;
43 fetch csr_cagr_lock into l_exists;
44 if csr_cagr_lock%found then
45 close csr_cagr_lock;
46 --
47 hr_utility.set_location(l_proc, 20);
48 --
49 UPDATE per_cagr_grades_def
50 SET concatenated_segments = p_concatenated_segments
51 WHERE cagr_grade_def_id = p_cagr_grade_def_id;
52 --
53 commit;
54 else
55 close csr_cagr_lock;
56 rollback; -- Added for bug 3578845.
57 end if;
58 --
59 hr_utility.set_location('Leaving:'|| l_proc, 30);
60 --
61 Exception
62 When HR_Api.Object_Locked then
63 rollback; -- Added for bug 3578845.
64 hr_utility.set_location('Leaving:'|| l_proc, 40);
65 end update_concat_segs_auto;
66 --
67 BEGIN
68 --
69 hr_utility.set_location('Entering:'|| l_proc, 10);
70 --
71 OPEN get_concat_segs;
72 FETCH get_concat_segs INTO l_concat_segments;
73 if get_concat_segs%found then
74 l_cagr_found := 'Y';
75 else
76 l_cagr_found := 'N';
77 end if;
78 CLOSE get_concat_segs;
79 --
80 hr_utility.set_location(l_proc,20);
81 --
82 IF (l_concat_segments <> p_concatenated_segments) OR
83 (l_concat_segments IS NULL AND p_concatenated_segments IS NOT NULL) THEN
84 --
85 hr_utility.set_location(l_proc, 30);
86 --
87 if l_cagr_found = 'Y' then
88 update_concat_segs_auto(p_cagr_grade_def_id);
89 end if;
90 --
91 END IF;
92 --
93 hr_utility.set_location('Leaving:'|| l_proc, 999);
94 --
95 end update_concat_segs;
96 --
97 -- ----------------------------------------------------------------------------
98 -- |--------------------------< create_cagr_grades >--------------------------|
99 -- ----------------------------------------------------------------------------
100 --
101 procedure create_cagr_grades
102 (p_validate in boolean default false
103 ,p_cagr_grade_id out nocopy number
104 ,p_cagr_grade_structure_id in number default null
105 ,p_segment1 in varchar2 default null
106 ,p_segment2 in varchar2 default null
107 ,p_segment3 in varchar2 default null
108 ,p_segment4 in varchar2 default null
109 ,p_segment5 in varchar2 default null
110 ,p_segment6 in varchar2 default null
111 ,p_segment7 in varchar2 default null
112 ,p_segment8 in varchar2 default null
113 ,p_segment9 in varchar2 default null
114 ,p_segment10 in varchar2 default null
115 ,p_segment11 in varchar2 default null
116 ,p_segment12 in varchar2 default null
117 ,p_segment13 in varchar2 default null
118 ,p_segment14 in varchar2 default null
119 ,p_segment15 in varchar2 default null
120 ,p_segment16 in varchar2 default null
121 ,p_segment17 in varchar2 default null
122 ,p_segment18 in varchar2 default null
123 ,p_segment19 in varchar2 default null
124 ,p_segment20 in varchar2 default null
125 ,p_concat_segments in varchar2 default null
126 ,p_sequence in number default null
127 ,p_cagr_grade_def_id in out nocopy number
128 ,p_object_version_number out nocopy number
129 ,p_name out nocopy varchar2
130 ,p_effective_date in date) is
131 --
132 -- Declare cursors and local variables
133 --
134 l_cagr_grade_id per_cagr_grades.cagr_grade_id%TYPE;
135 l_temp_grade_def_id per_cagr_grades.cagr_grade_def_id%TYPE := p_cagr_grade_def_id;
136 l_proc varchar2(72) := g_package||'create_cagr_grades';
137 l_object_version_number per_cagr_grades.object_version_number%TYPE;
138 l_id_flex_num per_cagr_grade_structures.id_flex_num%TYPE;
139 l_cagr_grade_def_id per_cagr_grades.cagr_grade_def_id%TYPE
140 := p_cagr_grade_def_id;
141 l_name varchar2(240) := p_concat_segments;
142 --
143 -- bug 2284889 set up and initialize local segment values for use when
144 -- definition id is known, also create new variable to indicate
145 -- whether key flex id parameter enters the program with a value.
146 --
147 l_segment1 varchar2(60) := p_segment1;
148 l_segment2 varchar2(60) := p_segment2;
149 l_segment3 varchar2(60) := p_segment3;
150 l_segment4 varchar2(60) := p_segment4;
151 l_segment5 varchar2(60) := p_segment5;
152 l_segment6 varchar2(60) := p_segment6;
153 l_segment7 varchar2(60) := p_segment7;
154 l_segment8 varchar2(60) := p_segment8;
155 l_segment9 varchar2(60) := p_segment9;
156 l_segment10 varchar2(60) := p_segment10;
157 l_segment11 varchar2(60) := p_segment11;
158 l_segment12 varchar2(60) := p_segment12;
159 l_segment13 varchar2(60) := p_segment13;
160 l_segment14 varchar2(60) := p_segment14;
161 l_segment15 varchar2(60) := p_segment15;
162 l_segment16 varchar2(60) := p_segment16;
163 l_segment17 varchar2(60) := p_segment17;
164 l_segment18 varchar2(60) := p_segment18;
165 l_segment19 varchar2(60) := p_segment19;
166 l_segment20 varchar2(60) := p_segment20;
167 l_null_ind number(1) := 0;
168 --
169 cursor csr_id_flex_num is
170 select pcg.id_flex_num
171 from per_cagr_grade_structures pcg
172 where pcg.cagr_grade_structure_id = p_cagr_grade_structure_id;
173 --
174 -- bug 2284889 get per_competence_definition segment values where
175 -- competence_definition_id is known
176 --
177 cursor c_segments is
178 select segment1,
179 segment2,
180 segment3,
181 segment4,
182 segment5,
183 segment6,
184 segment7,
185 segment8,
186 segment9,
187 segment10,
188 segment11,
189 segment12,
190 segment13,
191 segment14,
192 segment15,
193 segment16,
194 segment17,
195 segment18,
196 segment19,
197 segment20
198 from per_cagr_grades_def
199 where cagr_grade_def_id = l_cagr_grade_def_id;
200 --
201 begin
202 --
203 hr_utility.set_location('Entering:'|| l_proc, 10);
204 --
205 -- Issue a savepoint if operating in validation only mode
206 --
207 savepoint create_cagr_grades;
208 --
209 hr_utility.set_location(l_proc, 20);
210 --
211 if l_cagr_grade_def_id is not null
212 --
213 then
214 --
215 hr_utility.set_location(l_proc, 15);
216 --
217 -- set indicator to show p_cagr_grade_def_id did not enter pgm null
218 --
219 l_null_ind := 1;
220 --
221 open c_segments;
222 fetch c_segments into
223 l_segment1,
224 l_segment2,
225 l_segment3,
226 l_segment4,
227 l_segment5,
228 l_segment6,
229 l_segment7,
230 l_segment8,
231 l_segment9,
232 l_segment10,
233 l_segment11,
234 l_segment12,
235 l_segment13,
236 l_segment14,
237 l_segment15,
238 l_segment16,
239 l_segment17,
240 l_segment18,
241 l_segment19,
242 l_segment20;
243 if c_segments%NOTFOUND OR c_segments%NOTFOUND IS NULL
244 then
245 l_cagr_grade_def_id := NULL;
246 l_null_ind := 0;
247 hr_utility.set_location(l_proc, 27);
248 end if;
249 close c_segments;
250 end if;
251 --
252 -- Process Logic
253 --
254 begin
255 --
256 -- Start of API User Hook for the before hook of create_cagr_grades
257 --
258 hr_cagr_grades_bk1.create_cagr_grades_b
259 (
260 p_cagr_grade_structure_id => p_cagr_grade_structure_id
261 ,p_sequence => p_sequence
262 ,p_segment1 => l_segment1
263 ,p_segment2 => l_segment2
264 ,p_segment3 => l_segment3
265 ,p_segment4 => l_segment4
266 ,p_segment5 => l_segment5
267 ,p_segment6 => l_segment6
268 ,p_segment7 => l_segment7
269 ,p_segment8 => l_segment8
270 ,p_segment9 => l_segment9
271 ,p_segment10 => l_segment10
272 ,p_segment11 => l_segment11
273 ,p_segment12 => l_segment12
274 ,p_segment13 => l_segment13
275 ,p_segment14 => l_segment14
276 ,p_segment15 => l_segment15
277 ,p_segment16 => l_segment16
278 ,p_segment17 => l_segment17
279 ,p_segment18 => l_segment18
280 ,p_segment19 => l_segment19
281 ,p_segment20 => l_segment20
282 ,p_concat_segments => p_concat_segments
283 ,p_effective_date => trunc(p_effective_date)
284 );
285 exception
286 when hr_api.cannot_find_prog_unit then
287 hr_api.cannot_find_prog_unit_error
288 (
289 p_module_name => 'CREATE_CAGR_GRADES'
290 ,p_hook_type => 'BP'
291 );
292 --
293 -- End of API User Hook for the before hook of create_cagr_grades
294 --
295 end;
296 --
297 -- Populate l_id_flex_num.
298 --
299 open csr_id_flex_num;
300 fetch csr_id_flex_num
301 into l_id_flex_num;
302 if csr_id_flex_num%NOTFOUND then
303 close csr_id_flex_num;
304 hr_utility.set_message(800,'PER_52810_INVALID_STRUCTURE');
305 hr_utility.raise_error;
306 end if;
307 close csr_id_flex_num;
308 --
309 hr_utility.set_location(l_proc, 30);
310 --
311 if l_cagr_grade_def_id is null then
312 l_null_ind := 0;
313 --
314 hr_utility.set_location(l_proc, 37);
315 --
316 --
317 -- Determine the Grade defintion by calling ins_or_sel
318 --
319 hr_kflex_utility.ins_or_sel_keyflex_comb
320 (p_appl_short_name => 'PER'
321 ,p_flex_code => 'CAGR'
322 ,p_flex_num => l_id_flex_num
323 ,p_segment1 => l_segment1
324 ,p_segment2 => l_segment2
325 ,p_segment3 => l_segment3
326 ,p_segment4 => l_segment4
327 ,p_segment5 => l_segment5
328 ,p_segment6 => l_segment6
329 ,p_segment7 => l_segment7
330 ,p_segment8 => l_segment8
331 ,p_segment9 => l_segment9
332 ,p_segment10 => l_segment10
333 ,p_segment11 => l_segment11
334 ,p_segment12 => l_segment12
335 ,p_segment13 => l_segment13
336 ,p_segment14 => l_segment14
337 ,p_segment15 => l_segment15
338 ,p_segment16 => l_segment16
339 ,p_segment17 => l_segment17
340 ,p_segment18 => l_segment18
341 ,p_segment19 => l_segment19
342 ,p_segment20 => l_segment20
343 ,p_concat_segments_in => p_concat_segments
344 ,p_ccid => l_cagr_grade_def_id
345 ,p_concat_segments_out => l_name
346 );
347 --
348 hr_utility.set_location(l_proc, 40);
349 --
350 -- Added as part of fix for bug 2126247
351 --
352 -- Updates the concatenated segments for the new
353 -- record on the per_cagr_grades_def table.
354 --
355 update_concat_segs
356 (p_cagr_grade_def_id => l_cagr_grade_def_id
357 ,p_concatenated_segments => l_name);
358 --
359 hr_utility.set_location(l_proc, 50);
360 --
361 end if;
362 --
363 per_gra_ins.ins
364 (
365 p_cagr_grade_id => l_cagr_grade_id
366 ,p_cagr_grade_structure_id => p_cagr_grade_structure_id
367 ,p_cagr_grade_def_id => l_cagr_grade_def_id
368 ,p_sequence => p_sequence
369 ,p_object_version_number => l_object_version_number
370 ,p_effective_date => trunc(p_effective_date)
371 );
372 --
373 begin
374 --
375 -- Start of API User Hook for the after hook of create_cagr_grades
376 --
377 hr_cagr_grades_bk1.create_cagr_grades_a
378 (p_cagr_grade_structure_id => p_cagr_grade_structure_id
379 ,p_cagr_grade_id => l_cagr_grade_id
380 ,p_cagr_grade_def_id => l_cagr_grade_def_id
381 ,p_sequence => p_sequence
382 ,p_segment1 => l_segment1
383 ,p_segment2 => l_segment2
384 ,p_segment3 => l_segment3
385 ,p_segment4 => l_segment4
386 ,p_segment5 => l_segment5
387 ,p_segment6 => l_segment6
388 ,p_segment7 => l_segment7
389 ,p_segment8 => l_segment8
390 ,p_segment9 => l_segment9
391 ,p_segment10 => l_segment10
392 ,p_segment11 => l_segment11
393 ,p_segment12 => l_segment12
394 ,p_segment13 => l_segment13
395 ,p_segment14 => l_segment14
396 ,p_segment15 => l_segment15
397 ,p_segment16 => l_segment16
398 ,p_segment17 => l_segment17
399 ,p_segment18 => l_segment18
400 ,p_segment19 => l_segment19
401 ,p_segment20 => l_segment20
402 ,p_concat_segments => p_concat_segments
403 ,p_name => l_name
404 ,p_object_version_number => l_object_version_number
405 ,p_effective_date => trunc(p_effective_date));
406 --
407 exception
408 when hr_api.cannot_find_prog_unit then
409 hr_api.cannot_find_prog_unit_error
410 (p_module_name => 'CREATE_CAGR_GRADES'
411 ,p_hook_type => 'AP'
412 );
413 end;
414 --
415 -- End of API User Hook for the after hook of create_cagr_grades
416 --
417 hr_utility.set_location(l_proc, 60);
418 --
419 -- When in validation only mode raise the Validate_Enabled exception
420 --
421 if p_validate then
422 raise hr_api.validate_enabled;
423 end if;
424 --
425 -- Set all output arguments
426 --
427 p_cagr_grade_id := l_cagr_grade_id;
428 p_cagr_grade_def_id := l_cagr_grade_def_id;
429 p_object_version_number := l_object_version_number;
430 p_name := l_name;
431 --
432 hr_utility.set_location(' Leaving:'||l_proc, 70);
433 --
434 exception
435 --
436 when hr_api.validate_enabled then
437 --
438 -- As the Validate_Enabled exception has been raised
439 -- we must rollback to the savepoint
440 --
441 ROLLBACK TO create_cagr_grades;
442 --
443 -- Only set output warning arguments
444 -- (Any key or derived arguments must be set to null
445 -- when validation only mode is being used.)
446 --
447 p_cagr_grade_id := null;
448 if l_null_ind = 0
449 then
450 p_cagr_grade_def_id := null;
451 end if;
452 p_object_version_number := null;
453 p_name := null;
454 hr_utility.set_location(' Leaving:'||l_proc, 80);
455 --
456 when others then
457 --
458 -- A validation or unexpected error has occured
459 --
460 ROLLBACK TO create_cagr_grades;
461 --
462 -- set in out parameters and set out parameters
463 --
464 p_cagr_grade_def_id := null;
465 p_object_version_number := null;
466 p_name := null;
467 p_cagr_grade_def_id := l_temp_grade_def_id;
468 raise;
469 --
470 end create_cagr_grades;
471 --
472 -- ----------------------------------------------------------------------------
473 -- |------------------------< update_cagr_grades >----------------------------|
474 -- ----------------------------------------------------------------------------
475 --
476 procedure update_cagr_grades
477 (p_validate in boolean default false
478 ,p_cagr_grade_id in number
479 ,p_sequence in number default hr_api.g_number
480 ,p_segment1 in varchar2 default hr_api.g_varchar2
481 ,p_segment2 in varchar2 default hr_api.g_varchar2
482 ,p_segment3 in varchar2 default hr_api.g_varchar2
483 ,p_segment4 in varchar2 default hr_api.g_varchar2
484 ,p_segment5 in varchar2 default hr_api.g_varchar2
485 ,p_segment6 in varchar2 default hr_api.g_varchar2
486 ,p_segment7 in varchar2 default hr_api.g_varchar2
487 ,p_segment8 in varchar2 default hr_api.g_varchar2
488 ,p_segment9 in varchar2 default hr_api.g_varchar2
489 ,p_segment10 in varchar2 default hr_api.g_varchar2
490 ,p_segment11 in varchar2 default hr_api.g_varchar2
491 ,p_segment12 in varchar2 default hr_api.g_varchar2
492 ,p_segment13 in varchar2 default hr_api.g_varchar2
493 ,p_segment14 in varchar2 default hr_api.g_varchar2
494 ,p_segment15 in varchar2 default hr_api.g_varchar2
495 ,p_segment16 in varchar2 default hr_api.g_varchar2
496 ,p_segment17 in varchar2 default hr_api.g_varchar2
497 ,p_segment18 in varchar2 default hr_api.g_varchar2
498 ,p_segment19 in varchar2 default hr_api.g_varchar2
499 ,p_segment20 in varchar2 default hr_api.g_varchar2
500 ,p_concat_segments in varchar2 default hr_api.g_varchar2
501 ,p_object_version_number in out nocopy number
502 ,p_effective_date in date
503 ,p_name out nocopy varchar2
504 ,p_cagr_grade_def_id in out nocopy number) is
505 --
506 -- Declare cursors and local variables
507 --
508 l_proc varchar2(72) := g_package||'update_cagr_grades';
509 l_segments_changed BOOLEAN;
510 l_object_version_number per_cagr_grades.object_version_number%TYPE;
511 l_ovn per_cagr_grades.object_version_number%TYPE := p_object_version_number;
512 l_id_flex_num per_cagr_grade_structures.id_flex_num%TYPE;
513 l_name varchar2(240);
514 l_cagr_grade_def_id
515 per_cagr_grades.cagr_grade_def_id%TYPE := p_cagr_grade_def_id;
516 l_temp_grade_def_id per_cagr_grades.cagr_grade_def_id%TYPE := p_cagr_grade_def_id;
517 --
518 -- bug 2284889 initialize l_cagr_grade_def_id and segment variables with
519 -- values where these are passed into program.
520 --
521 l_segment1 varchar2(60) := p_segment1;
522 l_segment2 varchar2(60) := p_segment2;
523 l_segment3 varchar2(60) := p_segment3;
524 l_segment4 varchar2(60) := p_segment4;
525 l_segment5 varchar2(60) := p_segment5;
526 l_segment6 varchar2(60) := p_segment6;
527 l_segment7 varchar2(60) := p_segment7;
528 l_segment8 varchar2(60) := p_segment8;
529 l_segment9 varchar2(60) := p_segment9;
530 l_segment10 varchar2(60) := p_segment10;
531 l_segment11 varchar2(60) := p_segment11;
532 l_segment12 varchar2(60) := p_segment12;
533 l_segment13 varchar2(60) := p_segment13;
534 l_segment14 varchar2(60) := p_segment14;
535 l_segment15 varchar2(60) := p_segment15;
536 l_segment16 varchar2(60) := p_segment16;
537 l_segment17 varchar2(60) := p_segment17;
538 l_segment18 varchar2(60) := p_segment18;
539 l_segment19 varchar2(60) := p_segment19;
540 l_segment20 varchar2(60) := p_segment20;
541 l_null_ind number(1) := 0;
542 --
543 cursor csr_id_flex_num is
544 select pcs.id_flex_num
545 from per_cagr_grade_structures pcs
546 where pcs.cagr_grade_structure_id in
547 (select pcg.cagr_grade_structure_id
548 from per_cagr_grades pcg
549 where pcg.cagr_grade_id = p_cagr_grade_id);
550 --
551 -- bug 2284889 get per_cagr_grades_def segment values where
552 -- cagr_grade_def_id is known
553 --
554 cursor c_segments is
555 select segment1,
556 segment2,
557 segment3,
558 segment4,
559 segment5,
560 segment6,
561 segment7,
562 segment8,
563 segment9,
564 segment10,
565 segment11,
566 segment12,
567 segment13,
568 segment14,
569 segment15,
570 segment16,
571 segment17,
572 segment18,
573 segment19,
574 segment20
575 from per_cagr_grades_def
576 where cagr_grade_def_id = l_cagr_grade_def_id;
577 --
578 BEGIN
579 --
580 hr_utility.set_location('Entering:'|| l_proc, 10);
581 --
582 --
583 -- Issue a savepoint if operating in validation only mode
584 --
585 savepoint update_cagr_grades;
586 --
587 hr_utility.set_location(l_proc, 20);
588 --
589 -- Process Logic
590 --
591 l_object_version_number := p_object_version_number;
592 --
593 -- 2284889
594 -- get segment values if p_cagr_grade_def_id entered with a value
595 -- set indicator to show p_cagr_grade_def_id did not enter pgm null
596 --
597 if l_cagr_grade_def_id is not null then
598 --
599 hr_utility.set_location(l_proc, 30);
600 --
601 -- set indicator to show p_competence_definition_id did not enter pgm null
602 --
603 l_null_ind := 1;
604 --
605 open c_segments;
606 fetch c_segments into
607 l_segment1,
608 l_segment2,
609 l_segment3,
610 l_segment4,
611 l_segment5,
612 l_segment6,
613 l_segment7,
614 l_segment8,
615 l_segment9,
616 l_segment10,
617 l_segment11,
618 l_segment12,
619 l_segment13,
620 l_segment14,
621 l_segment15,
622 l_segment16,
623 l_segment17,
624 l_segment18,
625 l_segment19,
626 l_segment20;
627 --
628 if c_segments%NOTFOUND OR c_segments%NOTFOUND IS NULL then
629 --
630 l_cagr_grade_def_id := NULL;
631 l_null_ind := 0;
632 --
633 hr_utility.set_location(l_proc, 40);
634 --
635 end if;
636 --
637 close c_segments;
638 --
639 end if;
640 --
641 begin
642 --
643 -- Start of API User Hook for the before hook of update_cagr_grades
644 --
645 hr_cagr_grades_bk2.update_cagr_grades_b
646 (p_cagr_grade_id => p_cagr_grade_id
647 ,p_sequence => p_sequence
648 ,p_segment1 => l_segment1
649 ,p_segment2 => l_segment2
650 ,p_segment3 => l_segment3
651 ,p_segment4 => l_segment4
652 ,p_segment5 => l_segment5
653 ,p_segment6 => l_segment6
654 ,p_segment7 => l_segment7
655 ,p_segment8 => l_segment8
656 ,p_segment9 => l_segment9
657 ,p_segment10 => l_segment10
658 ,p_segment11 => l_segment11
659 ,p_segment12 => l_segment12
660 ,p_segment13 => l_segment13
661 ,p_segment14 => l_segment14
662 ,p_segment15 => l_segment15
663 ,p_segment16 => l_segment16
664 ,p_segment17 => l_segment17
665 ,p_segment18 => l_segment18
666 ,p_segment19 => l_segment19
667 ,p_segment20 => l_segment20
668 ,p_concat_segments => p_concat_segments
669 ,p_object_version_number => p_object_version_number
670 ,p_effective_date => trunc(p_effective_date));
671 --
672 exception
673 --
674 when hr_api.cannot_find_prog_unit then
675 hr_api.cannot_find_prog_unit_error
676 (p_module_name => 'UPDATE_CAGR_GRADES'
677 ,p_hook_type => 'BP'
678 );
679 --
680 -- End of API User Hook for the before hook of update_cagr_grades
681 --
682 end;
683 --
684 hr_utility.set_location(l_proc, 50);
685 --
686 -- Populate l_id_flex_num.
687 --
688 open csr_id_flex_num;
689 fetch csr_id_flex_num into l_id_flex_num;
690 --
691 if csr_id_flex_num%NOTFOUND then
692 --
693 close csr_id_flex_num;
694 --
695 hr_utility.set_message(800, 'PER_52617_INVALID_GRADE');
696 hr_utility.raise_error;
697 --
698 end if;
699 --
700 close csr_id_flex_num;
701 --
702 hr_utility.set_location(l_proc, 60);
703 --
704 -- 2284889
705 -- update cagr grades definitions in per_cagr_grades_def if
706 -- p_cagr_grade_def_id had no value when passed into program
707 --
708 if l_null_ind = 0 OR l_segments_changed = TRUE then
709 --
710 hr_utility.set_location(l_proc, 70);
711 --
712 -- Maintain the collective agreement key flexfields.
713 --
714 hr_kflex_utility.upd_or_sel_keyflex_comb
715 (p_appl_short_name => 'PER'
716 ,p_flex_code => 'CAGR'
717 ,p_flex_num => l_id_flex_num
718 ,p_segment1 => l_segment1
719 ,p_segment2 => l_segment2
720 ,p_segment3 => l_segment3
721 ,p_segment4 => l_segment4
722 ,p_segment5 => l_segment5
723 ,p_segment6 => l_segment6
724 ,p_segment7 => l_segment7
725 ,p_segment8 => l_segment8
726 ,p_segment9 => l_segment9
727 ,p_segment10 => l_segment10
728 ,p_segment11 => l_segment11
729 ,p_segment12 => l_segment12
730 ,p_segment13 => l_segment13
731 ,p_segment14 => l_segment14
732 ,p_segment15 => l_segment15
733 ,p_segment16 => l_segment16
734 ,p_segment17 => l_segment17
735 ,p_segment18 => l_segment18
736 ,p_segment19 => l_segment19
737 ,p_segment20 => l_segment20
738 ,p_concat_segments_in => p_concat_segments
739 ,p_ccid => l_cagr_grade_def_id
740 ,p_concat_segments_out => l_name);
741 --
742 hr_utility.set_location(l_proc, 80);
743 --
744 end if;
745 --
746 -- Added as part of fix for bug 2126247
747 --
748 -- Updates the concatenated segments for the record
749 -- on the per_cagr_grades_def table.
750 --
751 update_concat_segs
752 (p_cagr_grade_def_id => l_cagr_grade_def_id
753 ,p_concatenated_segments => l_name);
754 --
755 hr_utility.set_location(l_proc, 90);
756 --
757 per_gra_upd.upd
758 (p_cagr_grade_id => p_cagr_grade_id
759 ,p_cagr_grade_def_id => l_cagr_grade_def_id
760 ,p_sequence => p_sequence
761 ,p_object_version_number => l_object_version_number
762 ,p_effective_date => trunc(p_effective_date));
763 --
764 begin
765 --
766 -- Start of API User Hook for the after hook of update_cagr_grades
767 --
768 hr_cagr_grades_bk2.update_cagr_grades_a
769 (p_cagr_grade_id => p_cagr_grade_id
770 ,p_sequence => p_sequence
771 ,p_segment1 => l_segment1
772 ,p_segment2 => l_segment2
773 ,p_segment3 => l_segment3
774 ,p_segment4 => l_segment4
775 ,p_segment5 => l_segment5
776 ,p_segment6 => l_segment6
777 ,p_segment7 => l_segment7
778 ,p_segment8 => l_segment8
779 ,p_segment9 => l_segment9
780 ,p_segment10 => l_segment10
781 ,p_segment11 => l_segment11
782 ,p_segment12 => l_segment12
783 ,p_segment13 => l_segment13
784 ,p_segment14 => l_segment14
785 ,p_segment15 => l_segment15
786 ,p_segment16 => l_segment16
787 ,p_segment17 => l_segment17
788 ,p_segment18 => l_segment18
789 ,p_segment19 => l_segment19
790 ,p_segment20 => l_segment20
791 ,p_concat_segments => p_concat_segments
792 ,p_effective_date => trunc(p_effective_date)
793 ,p_name => l_name
794 ,p_cagr_grade_def_id => l_cagr_grade_def_id
795 ,p_object_version_number => l_object_version_number);
796 --
797 exception
798 --
799 when hr_api.cannot_find_prog_unit then
800 --
801 hr_api.cannot_find_prog_unit_error
802 (p_module_name => 'UPDATE_CAGR_GRADES'
803 ,p_hook_type => 'AP');
804 --
805 -- End of API User Hook for the after hook of update_cagr_grades
806 --
807 end;
808 --
809 hr_utility.set_location(l_proc, 100);
810 --
811 -- When in validation only mode raise the Validate_Enabled exception
812 --
813 if p_validate then
814 raise hr_api.validate_enabled;
815 end if;
816 --
817 -- Set all output arguments
818 --
819 p_object_version_number := l_object_version_number;
820 p_name := l_name;
821 p_cagr_grade_def_id := l_cagr_grade_def_id;
822 --
823 hr_utility.set_location(' Leaving:'||l_proc, 997);
824 --
825 exception
826 --
827 when hr_api.validate_enabled then
828 --
829 hr_utility.set_location(' Leaving:'||l_proc, 998);
830 --
831 -- As the Validate_Enabled exception has been raised
832 -- we must rollback to the savepoint
833 --
834 ROLLBACK TO update_cagr_grades;
835 --
836 -- Only set output warning arguments
837 -- (Any key or derived arguments must be set to null
838 -- when validation only mode is being used.)
839 --
840 p_object_version_number := l_object_version_number;
841 p_name := null;
842 if l_null_ind = 0
843 then
844 p_cagr_grade_def_id := l_cagr_grade_def_id;
845 end if;
846 --
847 when others then
848 --
849 hr_utility.set_location(' Leaving:'||l_proc, 999);
850 --
851 -- A validation or unexpected error has occured
852 --
853 ROLLBACK TO update_cagr_grades;
854 --
855 -- set in out parameters and set out parameters
856 --
857 p_object_version_number := l_ovn;
858 p_name := null;
859 p_cagr_grade_def_id := l_temp_grade_def_id;
860 raise;
861 --
862 end update_cagr_grades;
863 -- ----------------------------------------------------------------------------
864 -- |----------------------------< delete_cagr_grades >------------------------|
865 -- ----------------------------------------------------------------------------
866 --
867 procedure delete_cagr_grades
868 (p_validate in boolean default false
869 ,p_cagr_grade_id in number
870 ,p_object_version_number in out nocopy number
871 ,p_effective_date in date
872 ) is
873 --
874 -- Declare cursors and local variables
875 --
876 l_proc varchar2(72) := g_package||'update_cagr_grades';
877 l_object_version_number per_cagr_grades.object_version_number%TYPE;
878 l_ovn per_cagr_grades.object_version_number%TYPE := p_object_version_number;
879 --
880 begin
881 --
882 hr_utility.set_location('Entering:'|| l_proc, 10);
883 --
884 -- Issue a savepoint if operating in validation only mode
885 --
886 savepoint delete_cagr_grades;
887 --
888 hr_utility.set_location(l_proc, 20);
889 --
890 -- Process Logic
891 --
892 l_object_version_number := p_object_version_number;
893 --
894 --
895 begin
896 --
897 -- Start of API User Hook for the before hook of delete_cagr_grades
898 --
899 hr_cagr_grades_bk3.delete_cagr_grades_b
900 (
901 p_cagr_grade_id => p_cagr_grade_id
902 ,p_object_version_number => p_object_version_number
903 ,p_effective_date => trunc(p_effective_date)
904 );
905 exception
906 when hr_api.cannot_find_prog_unit then
907 hr_api.cannot_find_prog_unit_error
908 (p_module_name => 'DELETE_CAGR_GRADES'
909 ,p_hook_type => 'BP'
910 );
911 --
912 -- End of API User Hook for the before hook of delete_cagr_grades
913 --
914 end;
915 --
916 per_gra_del.del
917 (
918 p_cagr_grade_id => p_cagr_grade_id,
919 p_object_version_number => l_object_version_number,
920 p_effective_date => trunc(p_effective_date)
921 );
922 begin
923 --
924 -- Start of API User Hook for the after hook of delete_cagr_grades
925 --
926 hr_cagr_grades_bk3.delete_cagr_grades_a
927 (
928 p_cagr_grade_id => p_cagr_grade_id
929 ,p_object_version_number => l_object_version_number
930 ,p_effective_date => trunc(p_effective_date)
931 );
932 exception
933 when hr_api.cannot_find_prog_unit then
934 hr_api.cannot_find_prog_unit_error
935 (p_module_name => 'DELETE_CAGR_GRADES'
936 ,p_hook_type => 'AP'
937 );
938 --
939 -- End of API User Hook for the after hook of delete_cagr_grades
940 --
941 end;
942 --
943 hr_utility.set_location(l_proc, 60);
944 --
945 -- When in validation only mode raise the Validate_Enabled exception
946 --
947 if p_validate then
948 raise hr_api.validate_enabled;
949 end if;
950 --
951 -- Set all output arguments
952 --
953 p_object_version_number := l_object_version_number;
954 --
955 hr_utility.set_location(' Leaving:'||l_proc, 70);
956 --
957 exception
958 --
959 when hr_api.validate_enabled then
960 --
961 -- As the Validate_Enabled exception has been raised
962 -- we must rollback to the savepoint
963 --
964 ROLLBACK TO delete_cagr_grades;
965 --
966 -- Only set output warning arguments
967 -- (Any key or derived arguments must be set to null
968 -- when validation only mode is being used.)
969 --
970 --
971 when others then
972 --
973 -- A validation or unexpected error has occured
974 --
975 ROLLBACK TO delete_cagr_grades;
976 --
977 -- set in out parameters and set out parameters
978 --
979 p_object_version_number := l_ovn;
980 raise;
981 --
982 end delete_cagr_grades;
983 --
984 -- ----------------------------------------------------------------------------
985 -- |-------------------------------< lck >------------------------------------|
986 -- ----------------------------------------------------------------------------
987 --
988 procedure lck
989 (
990 p_cagr_grade_id in number
991 ,p_object_version_number in number
992 ,p_effective_date in date
993 ) is
994 --
995 --
996 -- Declare cursors and local variables
997 --
998 l_proc varchar2(72) := g_package||'lck';
999 --
1000 begin
1001 --
1002 hr_utility.set_location('Entering:'|| l_proc, 10);
1003 --
1004 per_gra_shd.lck
1005 (
1006 p_cagr_grade_id => p_cagr_grade_id
1007 ,p_object_version_number => p_object_version_number
1008 ,p_effective_date => trunc(p_effective_date)
1009 );
1010 --
1011 hr_utility.set_location(' Leaving:'||l_proc, 70);
1012 --
1013 end lck;
1014 --
1015 end hr_cagr_grades_api;