[Home] [Help]
PACKAGE BODY: APPS.PER_SPP_INS
Source
1 Package Body per_spp_ins as
2 /* $Header: pespprhi.pkb 120.6 2010/01/12 06:37:43 skura ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_spp_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------------< dt_insert_dml >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic for datetrack. The
17 -- functions of this procedure are as follows:
18 -- 1) Get the object_version_number.
19 -- 2) To set the effective start and end dates to the corresponding
20 -- validation start and end dates. Also, the object version number
21 -- record attribute is set.
22 -- 3) To set and unset the g_api_dml status as required (as we are about to
23 -- perform dml).
24 -- 4) To insert the row into the schema with the derived effective start
25 -- and end dates and the object version number.
26 -- 5) To trap any constraint violations that may have occurred.
27 -- 6) To raise any other errors.
28 --
29 -- Prerequisites:
30 -- This is an internal private procedure which must be called from the
31 -- insert_dml and pre_update (logic permitting) procedure and must have
32 -- all mandatory arguments set.
33 --
34 -- In Parameters:
35 -- A Pl/Sql record structure.
36 --
37 -- Post Success:
38 -- The specified row will be inserted into the schema.
39 --
40 -- Post Failure:
41 -- On the insert dml failure it is important to note that we always reset the
42 -- g_api_dml status to false.
43 -- If a check or unique integrity constraint violation is raised the
44 -- constraint_error procedure will be called.
45 -- If any other error is reported, the error will be raised after the
46 -- g_api_dml status is reset.
47 --
48 -- Developer Implementation Notes:
49 -- This is an internal datetrack maintenance procedure which should
50 -- not be modified in anyway.
51 --
52 -- Access Status:
53 -- Internal Row Handler Use Only.
54 --
55 -- {End Of Comments}
56 -- ----------------------------------------------------------------------------
57 Procedure dt_insert_dml
58 (p_rec in out nocopy per_spp_shd.g_rec_type
59 ,p_effective_date in date
60 ,p_datetrack_mode in varchar2
61 ,p_validation_start_date in date
62 ,p_validation_end_date in date
63 ) is
64 -- Cursor to select 'old' created AOL who column values
65 --
66 Cursor C_Sel1 Is
67 select t.created_by,
68 t.creation_date
69 from per_spinal_point_placements_f t
70 where t.placement_id = p_rec.placement_id
71 and t.effective_start_date =
72 per_spp_shd.g_old_rec.effective_start_date
73 and t.effective_end_date = (p_validation_start_date - 1);
74 --
75 l_proc varchar2(72) := g_package||'dt_insert_dml';
76 l_created_by per_spinal_point_placements_f.created_by%TYPE;
77 l_creation_date per_spinal_point_placements_f.creation_date%TYPE;
78 l_last_update_date per_spinal_point_placements_f.last_update_date%TYPE;
79 l_last_updated_by per_spinal_point_placements_f.last_updated_by%TYPE;
80 l_last_update_login per_spinal_point_placements_f.last_update_login%TYPE;
81 --
82 Begin
83 hr_utility.set_location('Entering:'||l_proc, 5);
84 --
85 -- Get the object version number for the insert
86 --
87 p_rec.object_version_number :=
88 dt_api.get_object_version_number
89 (p_base_table_name => 'per_spinal_point_placements_f'
90 ,p_base_key_column => 'placement_id'
91 ,p_base_key_value => p_rec.placement_id
92 );
93 --
94 -- Set the effective start and end dates to the corresponding
95 -- validation start and end dates
96 --
97 p_rec.effective_start_date := p_validation_start_date;
98 p_rec.effective_end_date := p_validation_end_date;
99 --
100 -- If the datetrack_mode is not INSERT then we must populate the WHO
101 -- columns with the 'old' creation values and 'new' updated values.
102 --
103 If (p_datetrack_mode <> hr_api.g_insert) then
104 hr_utility.set_location(l_proc, 10);
105 --
106 -- Select the 'old' created values
107 --
108 Open C_Sel1;
109 Fetch C_Sel1 Into l_created_by, l_creation_date;
110 If C_Sel1%notfound Then
111 --
112 -- The previous 'old' created row has not been found. We need
113 -- to error as an internal datetrack problem exists.
114 --
115 Close C_Sel1;
116 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
117 fnd_message.set_token('PROCEDURE', l_proc);
118 fnd_message.set_token('STEP','10');
119 fnd_message.raise_error;
120 End If;
121 Close C_Sel1;
122 --
123 -- Set the AOL updated WHO values
124 --
125 l_last_update_date := sysdate;
126 l_last_updated_by := fnd_global.user_id;
127 l_last_update_login := fnd_global.login_id;
128 End If;
129 --
130 per_spp_shd.g_api_dml := true; -- Set the api dml status
131 --
132 -- Insert the row into: per_spinal_point_placements_f
133 --
134 insert into per_spinal_point_placements_f
135 (placement_id
136 ,effective_start_date
137 ,effective_end_date
138 ,business_group_id
139 ,assignment_id
140 ,step_id
141 ,auto_increment_flag
142 ,parent_spine_id
143 ,reason
144 ,request_id
145 ,program_application_id
146 ,program_id
147 ,program_update_date
148 ,increment_number
149 ,object_version_number
150 ,information1
151 ,information2
152 ,information3
153 ,information4
154 ,information5
155 ,information6
156 ,information7
157 ,information8
158 ,information9
159 ,information10
160 ,information11
161 ,information12
162 ,information13
163 ,information14
164 ,information15
165 ,information16
166 ,information17
167 ,information18
168 ,information19
169 ,information20
170 ,information21
171 ,information22
172 ,information23
173 ,information24
174 ,information25
175 ,information26
176 ,information27
177 ,information28
178 ,information29
179 ,information30
180 ,information_category
181 ,created_by
182 ,creation_date
183 ,last_update_date
184 ,last_updated_by
185 ,last_update_login
186 )
187 Values
188 (p_rec.placement_id
189 ,p_rec.effective_start_date
190 ,p_rec.effective_end_date
191 ,p_rec.business_group_id
192 ,p_rec.assignment_id
193 ,p_rec.step_id
194 ,p_rec.auto_increment_flag
195 ,p_rec.parent_spine_id
196 ,p_rec.reason
197 ,p_rec.request_id
198 ,p_rec.program_application_id
199 ,p_rec.program_id
200 ,p_rec.program_update_date
201 ,p_rec.increment_number
202 ,p_rec.object_version_number
203 ,p_rec.information1
204 ,p_rec.information2
205 ,p_rec.information3
206 ,p_rec.information4
207 ,p_rec.information5
208 ,p_rec.information6
209 ,p_rec.information7
210 ,p_rec.information8
211 ,p_rec.information9
212 ,p_rec.information10
213 ,p_rec.information11
214 ,p_rec.information12
215 ,p_rec.information13
216 ,p_rec.information14
217 ,p_rec.information15
218 ,p_rec.information16
219 ,p_rec.information17
220 ,p_rec.information18
221 ,p_rec.information19
222 ,p_rec.information20
223 ,p_rec.information21
224 ,p_rec.information22
225 ,p_rec.information23
226 ,p_rec.information24
227 ,p_rec.information25
228 ,p_rec.information26
229 ,p_rec.information27
230 ,p_rec.information28
231 ,p_rec.information29
232 ,p_rec.information30
233 ,p_rec.information_category
234 ,l_created_by
235 ,l_creation_date
236 ,l_last_update_date
237 ,l_last_updated_by
238 ,l_last_update_login
239 );
240 --
241 per_spp_shd.g_api_dml := false; -- Unset the api dml status
242 hr_utility.set_location(' Leaving:'||l_proc, 15);
243 --
244 Exception
245 When hr_api.check_integrity_violated Then
246 -- A check constraint has been violated
247 per_spp_shd.g_api_dml := false; -- Unset the api dml status
248 per_spp_shd.constraint_error
249 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
250 When hr_api.unique_integrity_violated Then
251 -- Unique integrity has been violated
252 per_spp_shd.g_api_dml := false; -- Unset the api dml status
253 per_spp_shd.constraint_error
254 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
255 When Others Then
256 per_spp_shd.g_api_dml := false; -- Unset the api dml status
257 Raise;
258 End dt_insert_dml;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |------------------------------< insert_dml >------------------------------|
262 -- ----------------------------------------------------------------------------
263 Procedure insert_dml
264 (p_rec in out nocopy per_spp_shd.g_rec_type
265 ,p_effective_date in date
266 ,p_datetrack_mode in varchar2
267 ,p_validation_start_date in date
268 ,p_validation_end_date in date
269 ) is
270 --
271 l_proc varchar2(72) := g_package||'insert_dml';
272 --
273 Begin
274 hr_utility.set_location('Entering:'||l_proc, 5);
275 --
276 per_spp_ins.dt_insert_dml
277 (p_rec => p_rec
278 ,p_effective_date => p_effective_date
279 ,p_datetrack_mode => p_datetrack_mode
280 ,p_validation_start_date => p_validation_start_date
281 ,p_validation_end_date => p_validation_end_date
282 );
283 --
284 hr_utility.set_location(' Leaving:'||l_proc, 10);
285 End insert_dml;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |------------------------------< pre_insert >------------------------------|
289 -- ----------------------------------------------------------------------------
290 -- {Start Of Comments}
291 --
292 -- Description:
293 -- This private procedure contains any processing which is required before
294 -- the insert dml. Presently, if the entity has a corresponding primary
295 -- key which is maintained by an associating sequence, the primary key for
296 -- the entity will be populated with the next sequence value in
297 -- preparation for the insert dml.
298 -- Also, if comments are defined for this entity, the comments insert
299 -- logic will also be called, generating a comment_id if required.
300 --
301 -- Prerequisites:
302 -- This is an internal procedure which is called from the ins procedure.
303 --
304 -- In Parameters:
305 -- A Pl/Sql record structure.
306 --
307 -- Post Success:
308 -- Processing continues.
309 --
310 -- Post Failure:
311 -- If an error has occurred, an error message and exception will be raised
312 -- but not handled.
313 --
314 -- Developer Implementation Notes:
315 -- Any pre-processing required before the insert dml is issued should be
316 -- coded within this procedure. As stated above, a good example is the
317 -- generation of a primary key number via a corresponding sequence.
318 -- It is important to note that any 3rd party maintenance should be reviewed
319 -- before placing in this procedure.
320 --
321 -- Access Status:
322 -- Internal Row Handler Use Only.
323 --
324 -- {End Of Comments}
325 -- ----------------------------------------------------------------------------
326 Procedure pre_insert
327 (p_rec in out nocopy per_spp_shd.g_rec_type
328 ,p_effective_date in date
329 ,p_datetrack_mode in varchar2
330 ,p_validation_start_date in date
331 ,p_validation_end_date in date
332 ) is
333 --
334 l_proc varchar2(72) := g_package||'pre_insert';
335 --
336 Cursor C_Sel1 is select per_spinal_point_placements_s.nextval from sys.dual;
337 --
338 Begin
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 --
342 -- Select the next sequence number
343 --
344 Open C_Sel1;
345 Fetch C_Sel1 Into p_rec.placement_id;
346 Close C_Sel1;
347 --
348 --
349 hr_utility.set_location(' Leaving:'||l_proc, 10);
350 End pre_insert;
351 --
352 -- ----------------------------------------------------------------------------
353 -- |---------------------------< delete_future_spp >---------------------------|
354 -- ----------------------------------------------------------------------------
355 -- Bug 2977842 starts here. Added new procedure to delete all the future spps
356 -- for the assignment.
357 --
358 PROCEDURE delete_future_spps(p_assignment_id number
359 ,p_effective_date date ) IS
360
361 --
362 --
363 -- Fetch future SPP_Records
364 --
365 CURSOR csr_future_spp_records(p_assignment_id number, p_effective_date date) IS
366 SELECT spp.placement_id,
367 spp.object_version_number,
368 spp.effective_start_date
369 FROM per_spinal_point_placements_f spp
370 WHERE spp.assignment_id = p_assignment_id
371 AND spp.effective_start_date > p_effective_date;
372 -- ORDER BY placement_id; Bug fix: 3648542
373 --
374 --
375 l_previous_id per_spinal_point_placements_f.placement_id%type;
376 l_object_version_number NUMBER;
377 l_effective_start_date DATE;
378 l_effective_end_date DATE;
379 l_proc VARCHAR2(72) := g_package||'delete_future_spps';
380 --
381 --
382 BEGIN
383
384 hr_utility.set_location('Entering : '||l_proc, 5);
385 --
386 l_previous_id := -1;
387 --
388 -- Delete all future SPP records.
389 FOR c_future_spp IN csr_future_spp_records(p_assignment_id, p_effective_date) LOOP
390 --
391 hr_utility.set_location(l_proc||'/ pl_id = '||c_future_spp.placement_id, 10);
392 hr_utility.set_location(l_proc||'/ ovn = '||c_future_spp.object_version_number, 10);
393 --
394 -- If the record retrieved has a different placement id
395 -- then perform a ZAP on this record. If the ID is the same
396 -- as the previous id then do nothing as this record has already
397 -- been deleted.
398 --
399 IF l_previous_id <> c_future_spp.placement_id THEN
400 --
401 hr_utility.set_location(l_proc, 20);
402 --
403 l_previous_id := c_future_spp.placement_id;
404 l_object_version_number := c_future_spp.object_version_number;
405 --
406 hr_sp_placement_api.delete_spp
407 (p_effective_date => c_future_spp.effective_start_date
408 ,p_datetrack_mode => hr_api.g_zap
409 ,p_placement_id => c_future_spp.placement_id
410 ,p_object_version_number => l_object_version_number
411 ,p_effective_start_date => l_effective_start_date
412 ,p_effective_end_date => l_effective_end_date);
413 --
414 END IF;
415 --
416 END LOOP;
417 --
418 hr_utility.set_location('Leaving : '||l_proc, 30);
419
420 END delete_future_spps;
421 --
422 -- ----------------------------------------------------------------------------
423 -- |------------------------------< sync_spp_asg >-----------------------------|
424 -- ----------------------------------------------------------------------------
425 -- Bug 2977842. Added new procedure to synchronise the spp
426 -- with the corresponding Assignment updates.
427 --
428 PROCEDURE Sync_spp_asg(p_assignment_id number
429 ,p_effective_date date) IS
430
431 --
432 --
433 --Select placements for the Assignment after the effective date.
434 --
435 Cursor future_spps(p_assignment_id number, p_effective_date date) IS
436 select spp.placement_id
437 -- ,spp.effective_start_date
438 -- ,spp.effective_end_date
439 -- ,spp.step_id
440 from per_spinal_point_placements_f spp
441 where spp.assignment_id = p_assignment_id
442 and spp.effective_start_date > p_effective_date;
443 --
444 -- select the earliest of (effective_start_date-1) for existing spps.
445 --
446 Cursor spp_end_date(p_assignment_id number, p_effective_date date) IS
447 select min(spp.effective_start_date-1)
448 from per_spinal_point_placements_f spp
449 where spp.assignment_id = p_assignment_id
450 and spp.effective_start_date > p_effective_date;
451 --
452 --
453 --
454 Cursor Asg_updates(p_assignment_id number, p_start_date date, p_end_date date) IS
455 select paa.assignment_id
456 ,paa.effective_start_date
457 ,paa.effective_end_date
458 ,paa.grade_id
459 from per_all_assignments_f paa
460 where paa.assignment_id = p_assignment_id
461 and paa.effective_start_date between p_start_date and p_end_date
462 order by paa.effective_start_date;
463 --
464 --
465 --
466 Cursor change_grade_update(p_assignment_id number, p_asg_eff_start_date date,
467 p_grade_id number) IS
468 select 'Y'
469 from per_all_assignments_f paa
470 where paa.assignment_id = p_assignment_id
471 and paa.effective_end_date = (p_asg_eff_start_date-1)
472 and paa.grade_id <> p_grade_id
473 and p_grade_id is not null
474 and paa.grade_id is not null;
475 --
476 -- Start of 3335915
477 /*
478 --check for assignment update with same grade.
479 --
480 Cursor same_grade_update(p_assignment_id number, p_asg_eff_start_date date,
481 p_grade_id number) IS
482 select 'Y'
483 from per_all_assignments_f paa
484 where paa.assignment_id = p_assignment_id
485 and paa.effective_end_date = (p_asg_eff_start_date-1)
486 and paa.grade_id = p_grade_id;
487 --
488 */
489 -- End of 3335915
490 --
491 --
492 Cursor change_grade_to_null(p_assignment_id number, p_asg_eff_start_date date,
493 p_grade_id number) IS
494 select 'Y'
495 from per_all_assignments_f paa
496 where paa.assignment_id = p_assignment_id
497 and paa.effective_end_date = (p_asg_eff_start_date-1)
498 and paa.grade_id is not null
499 and p_grade_id is null;
500 --
501 -- Placement details.
502 --
503 Cursor spp_details(p_assignment_id number, p_effective_date date) IS
504 select spp.placement_id
505 ,spp.effective_start_date
506 ,spp.effective_end_date
507 ,spp.object_version_number
508 ,spp.step_id
509 from per_spinal_point_placements_f spp
510 where spp.assignment_id = p_assignment_id
511 and p_effective_date between spp.effective_start_date and spp.effective_end_date;
512 --
513 --
514 --
515 l_proc varchar2(72) := g_package||'sync_spp_asg';
516 l_assignment_id per_all_assignments_f.assignment_id%type;
517 l_placement_id per_spinal_point_placements_f.placement_id%type;
518 l_future_spp_id per_spinal_point_placements_f.placement_id%type;
519 l_end_date DATE;
520 l_validation_start_date DATE;
521 l_validation_end_date DATE;
522 l_spp_delete_warning BOOLEAN;
523 l_asg_eff_start_date date;
524 l_asg_eff_end_date date;
525 l_spp_eff_start_date date;
526 l_spp_eff_end_date date;
527 l_object_version_number number;
528 l_grade_id per_grades.grade_id%type;
529 l_step_id per_spinal_point_steps_f.step_id%type;
530 l_dummy varchar2(2);
531 l_effective_start_date date;
532 l_effective_end_date date;
533 --
534 BEGIN
535 --
536 hr_utility.set_location('Entering '||l_proc, 10);
537 --
538 l_end_date := null;
539 l_assignment_id := p_assignment_id;
540
541 -- End date SPP.
542 open future_spps(p_assignment_id, p_effective_date);
543 fetch future_spps into l_future_spp_id;
544 if future_spps%found then
545 --
546 open spp_end_date(p_assignment_id, p_effective_date);
547 fetch spp_end_date into l_end_date;
548 if spp_end_date%found and l_end_date is not null then
549 --
550 open spp_details(p_assignment_id, p_effective_date);
551 fetch spp_details into l_placement_id, l_spp_eff_start_date, l_spp_eff_end_date,
552 l_object_version_number, l_step_id;
553 if spp_details%found then
554 --
555 if ( l_end_date < l_spp_eff_end_date) then --Fixed for bug9157053
556 hr_utility.set_location(l_proc, 20);
557 hr_sp_placement_api.delete_spp
558 (p_validate => false
559 ,p_effective_date => l_end_date
560 ,p_datetrack_mode => 'DELETE'
561 ,p_placement_id => l_placement_id
562 ,p_object_version_number => l_object_version_number
563 ,p_effective_start_date => l_effective_start_date
564 ,p_effective_end_date => l_effective_end_date
565 );
566 --
567 end if;
568 end if;
569 close spp_details;
570 --
571 end if;
572 close spp_end_date;
573 --
574 end if;
575 close future_spps;
576 --
577 hr_utility.set_location(l_proc, 30);
578 --
579 -- Keep the SPPs in synchronous with Assignment updates.
580 --
581 l_validation_start_date := p_effective_date+1;
582 l_validation_end_date := nvl(l_end_date,hr_api.g_eot);
583 --
584 FOR asg_rec in asg_updates(p_assignment_id, l_validation_start_date, l_validation_end_date) LOOP
585 --
586 l_asg_eff_start_date := asg_rec.effective_start_date;
587 l_asg_eff_end_date := asg_rec.effective_end_date;
588 l_grade_id := asg_rec.grade_id;
589 l_dummy := 'N';
590 l_placement_id := null;
591 l_object_version_number := null;
592
593 hr_utility.set_location('Assignment ID '||l_assignment_id, 40);
594 hr_utility.set_location('Effective start date '||l_asg_eff_start_date, 40);
595 hr_utility.set_location('Effective end date '||l_asg_eff_end_date, 40);
596 hr_utility.set_location('Grade ID '||l_grade_id, 40);
597 --
598 -- Start of 3335915
599 /*
600 open same_grade_update(l_assignment_id, l_asg_eff_start_date, l_grade_id);
601 fetch same_grade_update into l_dummy;
602 if same_grade_update%found and l_dummy ='Y' then
603 hr_utility.set_location('Assignment update is not a Grade Update', 50);
604 --
605 open spp_details(l_assignment_id, l_asg_eff_start_date);
606 fetch spp_details into l_placement_id, l_spp_eff_start_date,
607 l_spp_eff_end_date, l_object_version_number, l_step_id;
608 --
609 if spp_details%found then
610 hr_utility.set_location('SPP found', 60);
611 hr_utility.set_location('Placement ID: '||l_placement_id, 60);
612 hr_utility.set_location('Effective start date: '||l_spp_eff_start_date, 60);
613 hr_utility.set_location('Effective end date: '||l_spp_eff_end_date, 60);
614 hr_utility.set_location('Object version number: '||l_object_version_number, 60);
615 hr_utility.set_location('Step ID: '||l_step_id, 60);
616 --
617 BEGIN
618 hr_utility.set_location(l_proc, 70);
619 hr_sp_placement_api.update_spp
620 (p_effective_date => l_asg_eff_start_date
621 ,p_datetrack_mode => 'UPDATE'
622 ,p_placement_id => l_placement_id
623 ,p_object_version_number => l_object_version_number
624 ,p_step_id => l_step_id
625 ,p_effective_start_date => l_effective_start_date
626 ,p_effective_end_date => l_effective_end_date);
627 EXCEPTION
628 when others then
629 hr_utility.trace('Cannot process Placement id '||to_char(l_placement_id));
630 hr_utility.trace('Effective start date '||to_char(l_spp_eff_start_date));
631 hr_utility.trace('Encountered error - ORA: '||to_char(SQLCODE));
632 END;
633 --
634 else
635 hr_utility.set_location(l_proc||' No SPP found.', 80);
636 end if;
637 close spp_details;
638 end if;
639 close same_grade_update;
640 --
641 */
642 -- End of 3335915
643 --
644 --
645 open change_grade_to_null(l_assignment_id, l_asg_eff_start_date, l_grade_id);
646 fetch change_grade_to_null into l_dummy;
647 if change_grade_to_null%found and l_dummy = 'Y' then
648
649 hr_utility.set_location('Updation on Assignment was grade to null ', 90);
650 hr_utility.set_location('End date the SPP', 90);
651
652 open spp_details(l_assignment_id, l_asg_eff_start_date);
653 fetch spp_details into l_placement_id, l_spp_eff_start_date,
654 l_spp_eff_end_date, l_object_version_number, l_step_id;
655 if spp_details%found then
656 hr_utility.set_location('SPP found', 100);
657 hr_utility.set_location('Placement ID: '||l_placement_id, 100);
658 hr_utility.set_location('Effective start date: '||l_spp_eff_start_date, 100);
659 hr_utility.set_location('Effective end date: '||l_spp_eff_end_date, 100);
660 hr_utility.set_location('Object version number: '||l_object_version_number, 100);
661 hr_utility.set_location('Step ID: '||l_step_id, 100);
662 --
663 BEGIN
664 hr_sp_placement_api.delete_spp
665 (p_validate => false
666 ,p_effective_date => l_asg_eff_start_date-1
667 ,p_datetrack_mode => 'DELETE'
668 ,p_placement_id => l_placement_id
669 ,p_object_version_number => l_object_version_number
670 ,p_effective_start_date => l_effective_start_date
671 ,p_effective_end_date => l_effective_end_date
672 );
673 hr_utility.set_location('SPP end dated with '||
674 (to_char((l_asg_eff_start_date-1),'DD-MON-RRRR')), 110);
675
676 EXCEPTION
677 when others then
678 hr_utility.trace('Cannot process Placement id '||to_char(l_placement_id));
679 hr_utility.trace('Effective start date '||to_char(l_spp_eff_start_date));
680 hr_utility.trace('Encountered error - ORA: '||to_char(SQLCODE));
681 END;
682 --
683 else
684 hr_utility.set_location('No SPP found.', 120);
685 end if;
686 close spp_details;
687
688 end if;
689 close change_grade_to_null;
690 hr_utility.set_location(l_proc, 125);
691 --
692 --
693 --
694 open change_grade_update(l_assignment_id, l_asg_eff_start_date, l_grade_id);
695 fetch change_grade_update into l_dummy;
696 if change_grade_update%found and l_dummy = 'Y' then
697
698 hr_utility.set_location('Updation on Assignment was Grade1 to Grade2 ', 130);
699 hr_utility.set_location('So if any SPP found then update with lowest step_id', 130);
700
701 open spp_details(l_assignment_id, l_asg_eff_start_date);
702 fetch spp_details into l_placement_id, l_spp_eff_start_date,
703 l_spp_eff_end_date, l_object_version_number,l_step_id;
704
705 if spp_details%found then
706
707 hr_utility.set_location('SPP found', 140);
708 hr_utility.set_location('Placement ID: '||l_placement_id, 140);
709 hr_utility.set_location('Effective start date: '||l_spp_eff_start_date, 140);
710 hr_utility.set_location('Effective end date: '||l_spp_eff_end_date, 140);
711 hr_utility.set_location('Object version number: '||l_object_version_number, 140);
712 hr_utility.set_location('Step ID: '||l_step_id, 140);
713
714 hr_assignment_internal.maintain_spp_asg
715 (p_assignment_id => l_assignment_id
716 ,p_datetrack_mode => 'UPDATE'
717 ,p_validation_start_date => l_asg_eff_start_date
718 ,p_validation_end_date => l_asg_eff_end_date
719 ,p_grade_id => l_grade_id
720 ,p_spp_delete_warning => l_spp_delete_warning);
721 else
722
723 hr_utility.set_location('No SPP found.', 160);
724
725 end if;
726 close spp_details;
727
728
729 end if;
730 close change_grade_update;
731 hr_utility.set_location(l_proc, 170);
732 --
733 END LOOP;
734 hr_utility.set_location('Leaving '||l_proc, 180);
735 --
736 END Sync_spp_asg;
737 --
738 -- Bug 2977842 ends here.
739 --
740 -------------------------------------------------------------------------------------------------
741 --
742 -- ----------------------------------------------------------------------------
743 -- |----------------------------< post_insert >-------------------------------|
744 -- ----------------------------------------------------------------------------
745 -- {Start Of Comments}
746 --
747 -- Description:
748 -- This private procedure contains any processing which is required after the
749 -- insert dml.
750 --
751 -- Prerequisites:
752 -- This is an internal procedure which is called from the ins procedure.
753 --
754 -- In Parameters:
755 -- A Pl/Sql record structure.
756 --
757 -- Post Success:
758 -- Processing continues.
759 --
760 -- Post Failure:
761 -- If an error has occurred, an error message and exception will be raised
762 -- but not handled.
763 --
764 -- Developer Implementation Notes:
765 -- Any post-processing required after the insert dml is issued should be
766 -- coded within this procedure. It is important to note that any 3rd party
767 -- maintenance should be reviewed before placing in this procedure.
768 --
769 -- Access Status:
770 -- Internal Row Handler Use Only.
771 --
772 -- {End Of Comments}
773 -- ----------------------------------------------------------------------------
774 Procedure post_insert
775 (p_rec in per_spp_shd.g_rec_type
776 ,p_effective_date in date
777 ,p_datetrack_mode in varchar2
778 ,p_validation_start_date in date
779 ,p_validation_end_date in date
780 ,p_replace_future_spp in boolean -- Added for bug 2977842.
781 ) is
782 --
783 l_proc varchar2(72) := g_package||'post_insert';
784 -- Code changes start for bug 7457065
785 l_step_end_date per_spinal_point_steps_f.effective_end_date%type;
786
787 cursor c_step_end_date is
788 select max(effective_end_date)
789 from per_spinal_point_steps_f
790 where step_id = p_rec.step_id;
791 -- Code changes end for bug 7457065
792
793 Begin
794 hr_utility.set_location('Entering:'||l_proc, 5);
795
796 -- Code changes start for bug 7457065
797 open c_step_end_date;
798 fetch c_step_end_date into l_step_end_date;
799 close c_step_end_date;
800 --
801 hr_utility.set_location(l_proc, 6);
802 --
803 if l_step_end_date < p_rec.effective_end_date then
804
805 update per_spinal_point_placements_f
806 set effective_end_date = l_step_end_date
807 where placement_id = p_rec.placement_id
808 and effective_start_date = p_rec.effective_start_date;
809
810 end if;
811 hr_utility.set_location(l_proc, 7);
812
813 -- Code changes end for bug 7457065
814
815 begin
816 --
817 per_spp_rki.after_insert
818 (p_effective_date => p_effective_date
819 ,p_validation_start_date => p_validation_start_date
820 ,p_validation_end_date => p_validation_end_date
821 ,p_placement_id => p_rec.placement_id
822 ,p_effective_start_date => p_rec.effective_start_date
823 ,p_effective_end_date => p_rec.effective_end_date
824 ,p_business_group_id => p_rec.business_group_id
825 ,p_assignment_id => p_rec.assignment_id
826 ,p_step_id => p_rec.step_id
827 ,p_auto_increment_flag => p_rec.auto_increment_flag
828 ,p_parent_spine_id => p_rec.parent_spine_id
829 ,p_reason => p_rec.reason
830 ,p_request_id => p_rec.request_id
831 ,p_program_application_id => p_rec.program_application_id
832 ,p_program_id => p_rec.program_id
833 ,p_program_update_date => p_rec.program_update_date
834 ,p_increment_number => p_rec.increment_number
835 ,p_object_version_number => p_rec.object_version_number
836 ,p_information1 => p_rec.information1
837 ,p_information2 => p_rec.information2
838 ,p_information3 => p_rec.information3
839 ,p_information4 => p_rec.information4
840 ,p_information5 => p_rec.information5
841 ,p_information6 => p_rec.information6
842 ,p_information7 => p_rec.information7
843 ,p_information8 => p_rec.information8
844 ,p_information9 => p_rec.information9
845 ,p_information10 => p_rec.information10
846 ,p_information11 => p_rec.information11
847 ,p_information12 => p_rec.information12
848 ,p_information13 => p_rec.information13
849 ,p_information14 => p_rec.information14
850 ,p_information15 => p_rec.information15
851 ,p_information16 => p_rec.information16
852 ,p_information17 => p_rec.information17
853 ,p_information18 => p_rec.information18
854 ,p_information19 => p_rec.information19
855 ,p_information20 => p_rec.information20
856 ,p_information21 => p_rec.information21
857 ,p_information22 => p_rec.information22
858 ,p_information23 => p_rec.information23
859 ,p_information24 => p_rec.information24
860 ,p_information25 => p_rec.information25
861 ,p_information26 => p_rec.information26
862 ,p_information27 => p_rec.information27
863 ,p_information28 => p_rec.information28
864 ,p_information29 => p_rec.information29
865 ,p_information30 => p_rec.information30
866 ,p_information_category => p_rec.information_category
867 );
868 --
869 exception
870 --
871 when hr_api.cannot_find_prog_unit then
872 --
873 hr_api.cannot_find_prog_unit_error
874 (p_module_name => 'PER_SPINAL_POINT_PLACEMENTS_F'
875 ,p_hook_type => 'AI');
876 --
877 end;
878 --
879 -- Bug 2977842 starts here.
880 if p_replace_future_spp then
881 --
882 per_spp_ins.delete_future_spps(p_assignment_id => p_rec.assignment_id
883 ,p_effective_date => p_effective_date);
884 --
885 end if;
886 --
887 per_spp_ins.sync_spp_asg(p_assignment_id => p_rec.assignment_id
888 ,p_effective_date => p_effective_date);
889 --
890 --Bug 2977842 ends here.
891 --
892 hr_utility.set_location(' Leaving:'||l_proc, 10);
893 End post_insert;
894 --
895 -- ----------------------------------------------------------------------------
896 -- |-------------------------------< ins_lck >--------------------------------|
897 -- ----------------------------------------------------------------------------
898 -- {Start Of Comments}
899 --
900 -- Description:
901 -- The ins_lck process has one main function to perform. When inserting
902 -- a datetracked row, we must validate the DT mode.
903 --
904 -- Prerequisites:
905 -- This procedure can only be called for the datetrack mode of INSERT.
906 --
907 -- In Parameters:
908 --
909 -- Post Success:
910 -- On successful completion of the ins_lck process the parental
911 -- datetracked rows will be locked providing the p_enforce_foreign_locking
912 -- argument value is TRUE.
913 -- If the p_enforce_foreign_locking argument value is FALSE then the
914 -- parential rows are not locked.
915 --
916 -- Post Failure:
917 -- The Lck process can fail for:
918 -- 1) When attempting to lock the row the row could already be locked by
919 -- another user. This will raise the HR_Api.Object_Locked exception.
920 -- 2) When attempting to the lock the parent which doesn't exist.
921 -- For the entity to be locked the parent must exist!
922 --
923 -- Developer Implementation Notes:
924 -- None.
925 --
926 -- Access Status:
927 -- Internal Row Handler Use Only.
928 --
929 -- {End Of Comments}
930 -- ----------------------------------------------------------------------------
931 Procedure ins_lck
932 (p_effective_date in date
933 ,p_datetrack_mode in varchar2
934 ,p_rec in per_spp_shd.g_rec_type
935 ,p_validation_start_date out nocopy date
936 ,p_validation_end_date out nocopy date
937 ) is
938 --
939 l_proc varchar2(72) := g_package||'ins_lck';
940 l_validation_start_date date;
941 l_validation_end_date date;
942 --bug 3158554 starts here.
943 -- Additional local variables
944 l_validation_start_date1 date;
945 l_validation_start_date2 date;
946 l_validation_end_date1 date;
947 l_validation_end_date2 date;
948 l_enforce_foreign_locking boolean;
949 -- bug 3158554 ends here.
950 --
951 Begin
952 hr_utility.set_location('Entering:'||l_proc, 5);
953 --
954 -- Validate the datetrack mode mode getting the validation start
955 -- and end dates for the specified datetrack operation.
956 --
957 -- bug 3158554 start here.
958 --
959 -- Always perform locking for set-up data parent
960 -- tables, unless this is a Data Pump session AND
961 -- the 'PUMP_DT_ENFORCE_FOREIGN_LOCKS' switch
962 -- has been set to no.
963 if hr_pump_utils.current_session_running then
964 l_enforce_foreign_locking := hr_pump_utils.dt_enforce_foreign_locks;
965 else
966 l_enforce_foreign_locking := true;
967 end if;
968
969 dt_api.validate_dt_mode
970 (p_effective_date => p_effective_date
971 ,p_datetrack_mode => p_datetrack_mode
972 ,p_base_table_name => 'per_spinal_point_placements_f'
973 ,p_base_key_column => 'placement_id'
974 ,p_base_key_value => p_rec.placement_id
975 ,p_parent_table_name1 => 'per_spinal_point_steps_f'
976 ,p_parent_key_column1 => 'step_id'
977 ,p_parent_key_value1 => p_rec.step_id
978 -- ,p_parent_table_name2 => 'per_all_assignments_f'
979 -- ,p_parent_key_column2 => 'assignment_id'
980 -- ,p_parent_key_value2 => p_rec.assignment_id
981 ,p_enforce_foreign_locking => l_enforce_foreign_locking
982 ,p_validation_start_date => l_validation_start_date1
983 ,p_validation_end_date => l_validation_end_date1
984 );
985 --
986 -- Always perform locking for transaction data parent tables.
987 --
988 dt_api.validate_dt_mode
989 (p_effective_date => p_effective_date
990 ,p_datetrack_mode => p_datetrack_mode
991 ,p_base_table_name => 'per_spinal_point_placements_f'
992 ,p_base_key_column => 'placement_id'
993 ,p_base_key_value => p_rec.placement_id
994 --,p_parent_table_name1 => 'per_spinal_point_steps_f'
995 --,p_parent_key_column1 => 'step_id'
996 --,p_parent_key_value1 => p_rec.step_id
997 ,p_parent_table_name2 => 'per_all_assignments_f'
998 ,p_parent_key_column2 => 'assignment_id'
999 ,p_parent_key_value2 => p_rec.assignment_id
1000 ,p_enforce_foreign_locking => true
1001 ,p_validation_start_date => l_validation_start_date2
1002 ,p_validation_end_date => l_validation_end_date2
1003 );
1004
1005 --
1006 -- Set the validation start and end date OUT arguments
1007 -- taking the most restrictive replies from the two calls
1008 -- to dt_api.validate_dt_mode. i.e. The latest VSD and the
1009 -- earliest VED.
1010 --
1011 if l_validation_start_date1 > l_validation_start_date2 then
1012 p_validation_start_date := l_validation_start_date1;
1013 else
1014 p_validation_start_date := l_validation_start_date2;
1015 end if;
1016 --
1017 if l_validation_end_date1 > l_validation_end_date2 then
1018 p_validation_end_date := l_validation_end_date2;
1019 else
1020 p_validation_end_date := l_validation_end_date1;
1021 end if;
1022
1023 --p_validation_start_date := l_validation_start_date;
1024 --p_validation_end_date := l_validation_end_date;
1025 --
1026 -- bug 3158554 ends here..
1027 hr_utility.set_location(' Leaving:'||l_proc, 10);
1028 --
1029 End ins_lck;
1030 --
1031 -- ----------------------------------------------------------------------------
1032 -- |---------------------------------< ins >----------------------------------|
1033 -- ----------------------------------------------------------------------------
1034 Procedure ins
1035 (p_effective_date in date
1036 ,p_rec in out nocopy per_spp_shd.g_rec_type
1037 ,p_replace_future_spp in boolean -- Added for bug 2977842.
1038 ) is
1039 --
1040 l_proc varchar2(72) := g_package||'ins';
1041 l_datetrack_mode varchar2(30) := hr_api.g_insert;
1042 l_validation_start_date date;
1043 l_validation_end_date date;
1044 --
1045 Begin
1046 hr_utility.set_location('Entering:'||l_proc, 5);
1047 --
1048 -- Call the supporting insert validate operations
1049 --
1050 per_spp_bus.insert_validate
1051 (p_rec => p_rec
1052 ,p_effective_date => p_effective_date
1053 ,p_datetrack_mode => l_datetrack_mode
1054 ,p_validation_start_date => l_validation_start_date
1055 ,p_validation_end_date => l_validation_end_date
1056 );
1057 --
1058 -- Call the lock operation
1059 --
1060 per_spp_ins.ins_lck
1061 (p_effective_date => p_effective_date
1062 ,p_datetrack_mode => l_datetrack_mode
1063 ,p_rec => p_rec
1064 ,p_validation_start_date => l_validation_start_date
1065 ,p_validation_end_date => l_validation_end_date
1066 );
1067 --
1068 -- Call the supporting insert validate operations
1069 --
1070 per_spp_bus.insert_validate
1071 (p_rec => p_rec
1072 ,p_effective_date => p_effective_date
1073 ,p_datetrack_mode => l_datetrack_mode
1074 ,p_validation_start_date => l_validation_start_date
1075 ,p_validation_end_date => l_validation_end_date
1076 );
1077
1078 --
1079 -- Call the supporting pre-insert operation
1080 --
1081 per_spp_ins.pre_insert
1082 (p_rec => p_rec
1083 ,p_effective_date => p_effective_date
1084 ,p_datetrack_mode => l_datetrack_mode
1085 ,p_validation_start_date => l_validation_start_date
1086 ,p_validation_end_date => l_validation_end_date
1087 );
1088 --
1089 -- Insert the row
1090 --
1091 per_spp_ins.insert_dml
1092 (p_rec => p_rec
1093 ,p_effective_date => p_effective_date
1094 ,p_datetrack_mode => l_datetrack_mode
1095 ,p_validation_start_date => l_validation_start_date
1096 ,p_validation_end_date => l_validation_end_date
1097 );
1098 --
1099 -- Call the supporting post-insert operation
1100 --
1101
1102 per_spp_ins.post_insert
1103 (p_rec => p_rec
1104 ,p_effective_date => p_effective_date
1105 ,p_datetrack_mode => l_datetrack_mode
1106 ,p_validation_start_date => l_validation_start_date
1107 ,p_validation_end_date => l_validation_end_date
1108 ,p_replace_future_spp => p_replace_future_spp --Added for bug 2977842.
1109 );
1110
1111 --
1112 hr_utility.set_location('Leaving:'||l_proc,10);
1113 end ins;
1114 --
1115 -- ----------------------------------------------------------------------------
1116 -- |---------------------------------< ins >----------------------------------|
1117 -- ----------------------------------------------------------------------------
1118 Procedure ins
1119 (p_effective_date in date
1120 ,p_business_group_id in number
1121 ,p_assignment_id in number
1122 ,p_step_id in number
1123 ,p_auto_increment_flag in varchar2
1124 -- ,p_parent_spine_id in number
1125 ,p_reason in varchar2
1126 ,p_request_id in number
1127 ,p_program_application_id in number
1128 ,p_program_id in number
1129 ,p_program_update_date in date
1130 ,p_increment_number in number
1131 ,p_information1 in varchar2
1132 ,p_information2 in varchar2
1133 ,p_information3 in varchar2
1134 ,p_information4 in varchar2
1135 ,p_information5 in varchar2
1136 ,p_information6 in varchar2
1137 ,p_information7 in varchar2
1138 ,p_information8 in varchar2
1139 ,p_information9 in varchar2
1140 ,p_information10 in varchar2
1141 ,p_information11 in varchar2
1142 ,p_information12 in varchar2
1143 ,p_information13 in varchar2
1144 ,p_information14 in varchar2
1145 ,p_information15 in varchar2
1146 ,p_information16 in varchar2
1147 ,p_information17 in varchar2
1148 ,p_information18 in varchar2
1149 ,p_information19 in varchar2
1150 ,p_information20 in varchar2
1151 ,p_information21 in varchar2
1152 ,p_information22 in varchar2
1153 ,p_information23 in varchar2
1154 ,p_information24 in varchar2
1155 ,p_information25 in varchar2
1156 ,p_information26 in varchar2
1157 ,p_information27 in varchar2
1158 ,p_information28 in varchar2
1159 ,p_information29 in varchar2
1160 ,p_information30 in varchar2
1161 ,p_information_category in varchar2
1162 ,p_placement_id out nocopy number
1163 ,p_object_version_number out nocopy number
1164 ,p_effective_start_date out nocopy date
1165 ,p_effective_end_date out nocopy date
1166 ,p_replace_future_spp in boolean -- Added bug 2977842.
1167 ) is
1168 --
1169 l_rec per_spp_shd.g_rec_type;
1170 l_proc varchar2(72) := g_package||'ins';
1171 l_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
1172 --
1173 cursor chk_step_valid_for_grade is
1174 select parent_spine_id
1175 from per_grade_spines_f pgs,
1176 per_all_assignments_f paa
1177 where paa.grade_id = pgs.grade_id
1178 and paa.assignment_id = p_assignment_id
1179 and p_effective_date between paa.effective_start_date
1180 and paa.effective_end_date
1181 and p_effective_date between pgs.effective_start_date
1182 and pgs.effective_end_date;
1183 Begin
1184 --
1185 hr_utility.set_location('Entering:'||l_proc, 10);
1186 --
1187 -- Get the parent_spine_id (Not passed in api as updated in the assignment
1188 -- form)
1189 --
1190 open chk_step_valid_for_grade;
1191 fetch chk_step_valid_for_grade into l_parent_spine_id;
1192 --
1193 if chk_step_valid_for_grade%notfound then
1194 --
1195 close chk_step_valid_for_grade;
1196 --
1197 fnd_message.set_name('PER', 'HR_289834_STEP_INV_FOR_GRADE');
1198 hr_utility.raise_error;
1199 --
1200 end if;
1201 --
1202 close chk_step_valid_for_grade;
1203 --
1204 -- Call conversion function to turn arguments into the
1205 -- p_rec structure.
1206 --
1207 l_rec :=
1208 per_spp_shd.convert_args
1209 (null
1210 ,null
1211 ,null
1212 ,p_business_group_id
1213 ,p_assignment_id
1214 ,p_step_id
1215 ,p_auto_increment_flag
1216 ,l_parent_spine_id
1217 ,p_reason
1218 ,p_request_id
1219 ,p_program_application_id
1220 ,p_program_id
1221 ,p_program_update_date
1222 ,p_increment_number
1223 ,p_information1
1224 ,p_information2
1225 ,p_information3
1226 ,p_information4
1227 ,p_information5
1228 ,p_information6
1229 ,p_information7
1230 ,p_information8
1231 ,p_information9
1232 ,p_information10
1233 ,p_information11
1234 ,p_information12
1235 ,p_information13
1236 ,p_information14
1237 ,p_information15
1238 ,p_information16
1239 ,p_information17
1240 ,p_information18
1241 ,p_information19
1242 ,p_information20
1243 ,p_information21
1244 ,p_information22
1245 ,p_information23
1246 ,p_information24
1247 ,p_information25
1248 ,p_information26
1249 ,p_information27
1250 ,p_information28
1251 ,p_information29
1252 ,p_information30
1253 ,p_information_category
1254 ,null
1255 );
1256 --
1257 -- Having converted the arguments into the per_spp_rec
1258 -- plsql record structure we call the corresponding record
1259 -- business process.
1260 --
1261 per_spp_ins.ins
1262 (p_effective_date
1263 ,l_rec
1264 ,p_replace_future_spp --Added bug2977842.
1265 );
1266 --
1267 -- Set the OUT arguments.
1268 --
1269 p_placement_id := l_rec.placement_id;
1270 p_effective_start_date := l_rec.effective_start_date;
1271 p_effective_end_date := l_rec.effective_end_date;
1272 p_object_version_number := l_rec.object_version_number;
1273 --
1274 --
1275 hr_utility.set_location(' Leaving:'||l_proc, 999);
1276 --
1277 End ins;
1278 --
1279 end per_spp_ins;