[Home] [Help]
PACKAGE BODY: APPS.PER_SPP_INS
Source
1 Package Body per_spp_ins as
2 /* $Header: pespprhi.pkb 120.1.12000000.2 2007/08/30 07:19:59 ande noship $ */
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
514 --
511 and p_effective_date between spp.effective_start_date and spp.effective_end_date;
512 --
513 --
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 hr_utility.set_location(l_proc, 20);
556 hr_sp_placement_api.delete_spp
557 (p_validate => false
558 ,p_effective_date => l_end_date
559 ,p_datetrack_mode => 'DELETE'
560 ,p_placement_id => l_placement_id
561 ,p_object_version_number => l_object_version_number
562 ,p_effective_start_date => l_effective_start_date
563 ,p_effective_end_date => l_effective_end_date
564 );
565 --
566 end if;
567 close spp_details;
568 --
569 end if;
570 close spp_end_date;
571 --
572 end if;
573 close future_spps;
574 --
575 hr_utility.set_location(l_proc, 30);
576 --
577 -- Keep the SPPs in synchronous with Assignment updates.
578 --
579 l_validation_start_date := p_effective_date+1;
580 l_validation_end_date := nvl(l_end_date,hr_api.g_eot);
581 --
582 FOR asg_rec in asg_updates(p_assignment_id, l_validation_start_date, l_validation_end_date) LOOP
583 --
584 l_asg_eff_start_date := asg_rec.effective_start_date;
585 l_asg_eff_end_date := asg_rec.effective_end_date;
586 l_grade_id := asg_rec.grade_id;
587 l_dummy := 'N';
588 l_placement_id := null;
589 l_object_version_number := null;
590
591 hr_utility.set_location('Assignment ID '||l_assignment_id, 40);
592 hr_utility.set_location('Effective start date '||l_asg_eff_start_date, 40);
593 hr_utility.set_location('Effective end date '||l_asg_eff_end_date, 40);
594 hr_utility.set_location('Grade ID '||l_grade_id, 40);
595 --
596 -- Start of 3335915
597 /*
598 open same_grade_update(l_assignment_id, l_asg_eff_start_date, l_grade_id);
599 fetch same_grade_update into l_dummy;
600 if same_grade_update%found and l_dummy ='Y' then
601 hr_utility.set_location('Assignment update is not a Grade Update', 50);
602 --
603 open spp_details(l_assignment_id, l_asg_eff_start_date);
604 fetch spp_details into l_placement_id, l_spp_eff_start_date,
605 l_spp_eff_end_date, l_object_version_number, l_step_id;
606 --
607 if spp_details%found then
608 hr_utility.set_location('SPP found', 60);
609 hr_utility.set_location('Placement ID: '||l_placement_id, 60);
610 hr_utility.set_location('Effective start date: '||l_spp_eff_start_date, 60);
611 hr_utility.set_location('Effective end date: '||l_spp_eff_end_date, 60);
612 hr_utility.set_location('Object version number: '||l_object_version_number, 60);
613 hr_utility.set_location('Step ID: '||l_step_id, 60);
614 --
615 BEGIN
616 hr_utility.set_location(l_proc, 70);
617 hr_sp_placement_api.update_spp
618 (p_effective_date => l_asg_eff_start_date
619 ,p_datetrack_mode => 'UPDATE'
620 ,p_placement_id => l_placement_id
621 ,p_object_version_number => l_object_version_number
622 ,p_step_id => l_step_id
623 ,p_effective_start_date => l_effective_start_date
624 ,p_effective_end_date => l_effective_end_date);
625 EXCEPTION
626 when others then
627 hr_utility.trace('Cannot process Placement id '||to_char(l_placement_id));
628 hr_utility.trace('Effective start date '||to_char(l_spp_eff_start_date));
629 hr_utility.trace('Encountered error - ORA: '||to_char(SQLCODE));
630 END;
631 --
632 else
633 hr_utility.set_location(l_proc||' No SPP found.', 80);
634 end if;
635 close spp_details;
636 end if;
637 close same_grade_update;
638 --
642 --
639 */
640 -- End of 3335915
641 --
643 open change_grade_to_null(l_assignment_id, l_asg_eff_start_date, l_grade_id);
644 fetch change_grade_to_null into l_dummy;
645 if change_grade_to_null%found and l_dummy = 'Y' then
646
647 hr_utility.set_location('Updation on Assignment was grade to null ', 90);
648 hr_utility.set_location('End date the SPP', 90);
649
650 open spp_details(l_assignment_id, l_asg_eff_start_date);
651 fetch spp_details into l_placement_id, l_spp_eff_start_date,
652 l_spp_eff_end_date, l_object_version_number, l_step_id;
653 if spp_details%found then
654 hr_utility.set_location('SPP found', 100);
655 hr_utility.set_location('Placement ID: '||l_placement_id, 100);
656 hr_utility.set_location('Effective start date: '||l_spp_eff_start_date, 100);
657 hr_utility.set_location('Effective end date: '||l_spp_eff_end_date, 100);
658 hr_utility.set_location('Object version number: '||l_object_version_number, 100);
659 hr_utility.set_location('Step ID: '||l_step_id, 100);
660 --
661 BEGIN
662 hr_sp_placement_api.delete_spp
663 (p_validate => false
664 ,p_effective_date => l_asg_eff_start_date-1
665 ,p_datetrack_mode => 'DELETE'
666 ,p_placement_id => l_placement_id
667 ,p_object_version_number => l_object_version_number
668 ,p_effective_start_date => l_effective_start_date
669 ,p_effective_end_date => l_effective_end_date
670 );
671 hr_utility.set_location('SPP end dated with '||
672 (to_char((l_asg_eff_start_date-1),'DD-MON-RRRR')), 110);
673
674 EXCEPTION
675 when others then
676 hr_utility.trace('Cannot process Placement id '||to_char(l_placement_id));
677 hr_utility.trace('Effective start date '||to_char(l_spp_eff_start_date));
678 hr_utility.trace('Encountered error - ORA: '||to_char(SQLCODE));
679 END;
680 --
681 else
682 hr_utility.set_location('No SPP found.', 120);
683 end if;
684 close spp_details;
685
686 end if;
687 close change_grade_to_null;
688 hr_utility.set_location(l_proc, 125);
689 --
690 --
691 --
692 open change_grade_update(l_assignment_id, l_asg_eff_start_date, l_grade_id);
693 fetch change_grade_update into l_dummy;
694 if change_grade_update%found and l_dummy = 'Y' then
695
696 hr_utility.set_location('Updation on Assignment was Grade1 to Grade2 ', 130);
697 hr_utility.set_location('So if any SPP found then update with lowest step_id', 130);
698
699 open spp_details(l_assignment_id, l_asg_eff_start_date);
700 fetch spp_details into l_placement_id, l_spp_eff_start_date,
701 l_spp_eff_end_date, l_object_version_number,l_step_id;
702
703 if spp_details%found then
704
705 hr_utility.set_location('SPP found', 140);
706 hr_utility.set_location('Placement ID: '||l_placement_id, 140);
707 hr_utility.set_location('Effective start date: '||l_spp_eff_start_date, 140);
708 hr_utility.set_location('Effective end date: '||l_spp_eff_end_date, 140);
709 hr_utility.set_location('Object version number: '||l_object_version_number, 140);
710 hr_utility.set_location('Step ID: '||l_step_id, 140);
711
712 hr_assignment_internal.maintain_spp_asg
713 (p_assignment_id => l_assignment_id
714 ,p_datetrack_mode => 'UPDATE'
715 ,p_validation_start_date => l_asg_eff_start_date
716 ,p_validation_end_date => l_asg_eff_end_date
717 ,p_grade_id => l_grade_id
718 ,p_spp_delete_warning => l_spp_delete_warning);
719 else
720
721 hr_utility.set_location('No SPP found.', 160);
722
723 end if;
724 close spp_details;
725
726
727 end if;
728 close change_grade_update;
729 hr_utility.set_location(l_proc, 170);
730 --
731 END LOOP;
732 hr_utility.set_location('Leaving '||l_proc, 180);
733 --
734 END Sync_spp_asg;
735 --
736 -- Bug 2977842 ends here.
737 --
738 -------------------------------------------------------------------------------------------------
739 --
740 -- ----------------------------------------------------------------------------
741 -- |----------------------------< post_insert >-------------------------------|
742 -- ----------------------------------------------------------------------------
743 -- {Start Of Comments}
744 --
745 -- Description:
746 -- This private procedure contains any processing which is required after the
747 -- insert dml.
748 --
749 -- Prerequisites:
750 -- This is an internal procedure which is called from the ins procedure.
751 --
752 -- In Parameters:
753 -- A Pl/Sql record structure.
754 --
755 -- Post Success:
756 -- Processing continues.
757 --
758 -- Post Failure:
759 -- If an error has occurred, an error message and exception will be raised
760 -- but not handled.
761 --
762 -- Developer Implementation Notes:
766 --
763 -- Any post-processing required after the insert dml is issued should be
764 -- coded within this procedure. It is important to note that any 3rd party
765 -- maintenance should be reviewed before placing in this procedure.
767 -- Access Status:
768 -- Internal Row Handler Use Only.
769 --
770 -- {End Of Comments}
771 -- ----------------------------------------------------------------------------
772 Procedure post_insert
773 (p_rec in per_spp_shd.g_rec_type
774 ,p_effective_date in date
775 ,p_datetrack_mode in varchar2
776 ,p_validation_start_date in date
777 ,p_validation_end_date in date
778 ,p_replace_future_spp in boolean -- Added for bug 2977842.
779 ) is
780 --
781 l_proc varchar2(72) := g_package||'post_insert';
782 --
783 Begin
784 hr_utility.set_location('Entering:'||l_proc, 5);
785 begin
786 --
787 per_spp_rki.after_insert
788 (p_effective_date => p_effective_date
789 ,p_validation_start_date => p_validation_start_date
790 ,p_validation_end_date => p_validation_end_date
791 ,p_placement_id => p_rec.placement_id
792 ,p_effective_start_date => p_rec.effective_start_date
793 ,p_effective_end_date => p_rec.effective_end_date
794 ,p_business_group_id => p_rec.business_group_id
795 ,p_assignment_id => p_rec.assignment_id
796 ,p_step_id => p_rec.step_id
797 ,p_auto_increment_flag => p_rec.auto_increment_flag
798 ,p_parent_spine_id => p_rec.parent_spine_id
799 ,p_reason => p_rec.reason
800 ,p_request_id => p_rec.request_id
801 ,p_program_application_id => p_rec.program_application_id
802 ,p_program_id => p_rec.program_id
803 ,p_program_update_date => p_rec.program_update_date
804 ,p_increment_number => p_rec.increment_number
805 ,p_object_version_number => p_rec.object_version_number
806 ,p_information1 => p_rec.information1
807 ,p_information2 => p_rec.information2
808 ,p_information3 => p_rec.information3
809 ,p_information4 => p_rec.information4
810 ,p_information5 => p_rec.information5
811 ,p_information6 => p_rec.information6
812 ,p_information7 => p_rec.information7
813 ,p_information8 => p_rec.information8
814 ,p_information9 => p_rec.information9
815 ,p_information10 => p_rec.information10
816 ,p_information11 => p_rec.information11
817 ,p_information12 => p_rec.information12
818 ,p_information13 => p_rec.information13
819 ,p_information14 => p_rec.information14
820 ,p_information15 => p_rec.information15
821 ,p_information16 => p_rec.information16
822 ,p_information17 => p_rec.information17
823 ,p_information18 => p_rec.information18
824 ,p_information19 => p_rec.information19
825 ,p_information20 => p_rec.information20
826 ,p_information21 => p_rec.information21
827 ,p_information22 => p_rec.information22
828 ,p_information23 => p_rec.information23
829 ,p_information24 => p_rec.information24
830 ,p_information25 => p_rec.information25
831 ,p_information26 => p_rec.information26
832 ,p_information27 => p_rec.information27
833 ,p_information28 => p_rec.information28
834 ,p_information29 => p_rec.information29
835 ,p_information30 => p_rec.information30
836 ,p_information_category => p_rec.information_category
837 );
838 --
839 exception
840 --
841 when hr_api.cannot_find_prog_unit then
842 --
843 hr_api.cannot_find_prog_unit_error
844 (p_module_name => 'PER_SPINAL_POINT_PLACEMENTS_F'
845 ,p_hook_type => 'AI');
846 --
847 end;
848 --
849 -- Bug 2977842 starts here.
850 if p_replace_future_spp then
851 --
852 per_spp_ins.delete_future_spps(p_assignment_id => p_rec.assignment_id
853 ,p_effective_date => p_effective_date);
854 --
855 end if;
856 --
857 per_spp_ins.sync_spp_asg(p_assignment_id => p_rec.assignment_id
858 ,p_effective_date => p_effective_date);
859 --
860 --Bug 2977842 ends here.
861 --
862 hr_utility.set_location(' Leaving:'||l_proc, 10);
863 End post_insert;
864 --
865 -- ----------------------------------------------------------------------------
866 -- |-------------------------------< ins_lck >--------------------------------|
867 -- ----------------------------------------------------------------------------
868 -- {Start Of Comments}
869 --
870 -- Description:
871 -- The ins_lck process has one main function to perform. When inserting
872 -- a datetracked row, we must validate the DT mode.
873 --
874 -- Prerequisites:
875 -- This procedure can only be called for the datetrack mode of INSERT.
876 --
877 -- In Parameters:
878 --
879 -- Post Success:
880 -- On successful completion of the ins_lck process the parental
884 -- parential rows are not locked.
881 -- datetracked rows will be locked providing the p_enforce_foreign_locking
882 -- argument value is TRUE.
883 -- If the p_enforce_foreign_locking argument value is FALSE then the
885 --
886 -- Post Failure:
887 -- The Lck process can fail for:
888 -- 1) When attempting to lock the row the row could already be locked by
889 -- another user. This will raise the HR_Api.Object_Locked exception.
890 -- 2) When attempting to the lock the parent which doesn't exist.
891 -- For the entity to be locked the parent must exist!
892 --
893 -- Developer Implementation Notes:
894 -- None.
895 --
896 -- Access Status:
897 -- Internal Row Handler Use Only.
898 --
899 -- {End Of Comments}
900 -- ----------------------------------------------------------------------------
901 Procedure ins_lck
902 (p_effective_date in date
903 ,p_datetrack_mode in varchar2
904 ,p_rec in per_spp_shd.g_rec_type
905 ,p_validation_start_date out nocopy date
906 ,p_validation_end_date out nocopy date
907 ) is
908 --
909 l_proc varchar2(72) := g_package||'ins_lck';
910 l_validation_start_date date;
911 l_validation_end_date date;
912 --bug 3158554 starts here.
913 -- Additional local variables
914 l_validation_start_date1 date;
915 l_validation_start_date2 date;
916 l_validation_end_date1 date;
917 l_validation_end_date2 date;
918 l_enforce_foreign_locking boolean;
919 -- bug 3158554 ends here.
920 --
921 Begin
922 hr_utility.set_location('Entering:'||l_proc, 5);
923 --
924 -- Validate the datetrack mode mode getting the validation start
925 -- and end dates for the specified datetrack operation.
926 --
927 -- bug 3158554 start here.
928 --
929 -- Always perform locking for set-up data parent
930 -- tables, unless this is a Data Pump session AND
931 -- the 'PUMP_DT_ENFORCE_FOREIGN_LOCKS' switch
932 -- has been set to no.
933 if hr_pump_utils.current_session_running then
934 l_enforce_foreign_locking := hr_pump_utils.dt_enforce_foreign_locks;
935 else
936 l_enforce_foreign_locking := true;
937 end if;
938
939 dt_api.validate_dt_mode
940 (p_effective_date => p_effective_date
941 ,p_datetrack_mode => p_datetrack_mode
942 ,p_base_table_name => 'per_spinal_point_placements_f'
943 ,p_base_key_column => 'placement_id'
944 ,p_base_key_value => p_rec.placement_id
945 ,p_parent_table_name1 => 'per_spinal_point_steps_f'
946 ,p_parent_key_column1 => 'step_id'
947 ,p_parent_key_value1 => p_rec.step_id
948 -- ,p_parent_table_name2 => 'per_all_assignments_f'
949 -- ,p_parent_key_column2 => 'assignment_id'
950 -- ,p_parent_key_value2 => p_rec.assignment_id
951 ,p_enforce_foreign_locking => l_enforce_foreign_locking
952 ,p_validation_start_date => l_validation_start_date1
953 ,p_validation_end_date => l_validation_end_date1
954 );
955 --
956 -- Always perform locking for transaction data parent tables.
957 --
958 dt_api.validate_dt_mode
959 (p_effective_date => p_effective_date
960 ,p_datetrack_mode => p_datetrack_mode
961 ,p_base_table_name => 'per_spinal_point_placements_f'
962 ,p_base_key_column => 'placement_id'
963 ,p_base_key_value => p_rec.placement_id
964 --,p_parent_table_name1 => 'per_spinal_point_steps_f'
965 --,p_parent_key_column1 => 'step_id'
966 --,p_parent_key_value1 => p_rec.step_id
967 ,p_parent_table_name2 => 'per_all_assignments_f'
968 ,p_parent_key_column2 => 'assignment_id'
969 ,p_parent_key_value2 => p_rec.assignment_id
970 ,p_enforce_foreign_locking => true
971 ,p_validation_start_date => l_validation_start_date2
972 ,p_validation_end_date => l_validation_end_date2
973 );
974
975 --
976 -- Set the validation start and end date OUT arguments
977 -- taking the most restrictive replies from the two calls
978 -- to dt_api.validate_dt_mode. i.e. The latest VSD and the
979 -- earliest VED.
980 --
981 if l_validation_start_date1 > l_validation_start_date2 then
982 p_validation_start_date := l_validation_start_date1;
983 else
984 p_validation_start_date := l_validation_start_date2;
985 end if;
986 --
987 if l_validation_end_date1 > l_validation_end_date2 then
988 p_validation_end_date := l_validation_end_date2;
989 else
990 p_validation_end_date := l_validation_end_date1;
991 end if;
992
993 --p_validation_start_date := l_validation_start_date;
994 --p_validation_end_date := l_validation_end_date;
995 --
996 -- bug 3158554 ends here..
997 hr_utility.set_location(' Leaving:'||l_proc, 10);
998 --
999 End ins_lck;
1000 --
1001 -- ----------------------------------------------------------------------------
1002 -- |---------------------------------< ins >----------------------------------|
1003 -- ----------------------------------------------------------------------------
1004 Procedure ins
1005 (p_effective_date in date
1006 ,p_rec in out nocopy per_spp_shd.g_rec_type
1010 l_proc varchar2(72) := g_package||'ins';
1007 ,p_replace_future_spp in boolean -- Added for bug 2977842.
1008 ) is
1009 --
1011 l_datetrack_mode varchar2(30) := hr_api.g_insert;
1012 l_validation_start_date date;
1013 l_validation_end_date date;
1014 --
1015 Begin
1016 hr_utility.set_location('Entering:'||l_proc, 5);
1017 --
1018 -- Call the supporting insert validate operations
1019 --
1020 per_spp_bus.insert_validate
1021 (p_rec => p_rec
1022 ,p_effective_date => p_effective_date
1023 ,p_datetrack_mode => l_datetrack_mode
1024 ,p_validation_start_date => l_validation_start_date
1025 ,p_validation_end_date => l_validation_end_date
1026 );
1027 --
1028 -- Call the lock operation
1029 --
1030 per_spp_ins.ins_lck
1031 (p_effective_date => p_effective_date
1032 ,p_datetrack_mode => l_datetrack_mode
1033 ,p_rec => p_rec
1034 ,p_validation_start_date => l_validation_start_date
1035 ,p_validation_end_date => l_validation_end_date
1036 );
1037 --
1038 -- Call the supporting insert validate operations
1039 --
1040 per_spp_bus.insert_validate
1041 (p_rec => p_rec
1042 ,p_effective_date => p_effective_date
1043 ,p_datetrack_mode => l_datetrack_mode
1044 ,p_validation_start_date => l_validation_start_date
1045 ,p_validation_end_date => l_validation_end_date
1046 );
1047
1048 --
1049 -- Call the supporting pre-insert operation
1050 --
1051 per_spp_ins.pre_insert
1052 (p_rec => p_rec
1053 ,p_effective_date => p_effective_date
1054 ,p_datetrack_mode => l_datetrack_mode
1055 ,p_validation_start_date => l_validation_start_date
1056 ,p_validation_end_date => l_validation_end_date
1057 );
1058 --
1059 -- Insert the row
1060 --
1061 per_spp_ins.insert_dml
1062 (p_rec => p_rec
1063 ,p_effective_date => p_effective_date
1064 ,p_datetrack_mode => l_datetrack_mode
1065 ,p_validation_start_date => l_validation_start_date
1066 ,p_validation_end_date => l_validation_end_date
1067 );
1068 --
1069 -- Call the supporting post-insert operation
1070 --
1071
1072 per_spp_ins.post_insert
1073 (p_rec => p_rec
1074 ,p_effective_date => p_effective_date
1075 ,p_datetrack_mode => l_datetrack_mode
1076 ,p_validation_start_date => l_validation_start_date
1077 ,p_validation_end_date => l_validation_end_date
1078 ,p_replace_future_spp => p_replace_future_spp --Added for bug 2977842.
1079 );
1080
1081 --
1082 hr_utility.set_location('Leaving:'||l_proc,10);
1083 end ins;
1084 --
1085 -- ----------------------------------------------------------------------------
1086 -- |---------------------------------< ins >----------------------------------|
1087 -- ----------------------------------------------------------------------------
1088 Procedure ins
1089 (p_effective_date in date
1090 ,p_business_group_id in number
1091 ,p_assignment_id in number
1092 ,p_step_id in number
1093 ,p_auto_increment_flag in varchar2
1094 -- ,p_parent_spine_id in number
1095 ,p_reason in varchar2
1096 ,p_request_id in number
1097 ,p_program_application_id in number
1098 ,p_program_id in number
1099 ,p_program_update_date in date
1100 ,p_increment_number in number
1101 ,p_information1 in varchar2
1102 ,p_information2 in varchar2
1103 ,p_information3 in varchar2
1104 ,p_information4 in varchar2
1105 ,p_information5 in varchar2
1106 ,p_information6 in varchar2
1107 ,p_information7 in varchar2
1108 ,p_information8 in varchar2
1109 ,p_information9 in varchar2
1110 ,p_information10 in varchar2
1111 ,p_information11 in varchar2
1112 ,p_information12 in varchar2
1113 ,p_information13 in varchar2
1114 ,p_information14 in varchar2
1115 ,p_information15 in varchar2
1116 ,p_information16 in varchar2
1117 ,p_information17 in varchar2
1118 ,p_information18 in varchar2
1119 ,p_information19 in varchar2
1120 ,p_information20 in varchar2
1121 ,p_information21 in varchar2
1122 ,p_information22 in varchar2
1123 ,p_information23 in varchar2
1124 ,p_information24 in varchar2
1125 ,p_information25 in varchar2
1126 ,p_information26 in varchar2
1127 ,p_information27 in varchar2
1128 ,p_information28 in varchar2
1129 ,p_information29 in varchar2
1130 ,p_information30 in varchar2
1131 ,p_information_category in varchar2
1132 ,p_placement_id out nocopy number
1136 ,p_replace_future_spp in boolean -- Added bug 2977842.
1133 ,p_object_version_number out nocopy number
1134 ,p_effective_start_date out nocopy date
1135 ,p_effective_end_date out nocopy date
1137 ) is
1138 --
1139 l_rec per_spp_shd.g_rec_type;
1140 l_proc varchar2(72) := g_package||'ins';
1141 l_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
1142 --
1143 cursor chk_step_valid_for_grade is
1144 select parent_spine_id
1145 from per_grade_spines_f pgs,
1146 per_all_assignments_f paa
1147 where paa.grade_id = pgs.grade_id
1148 and paa.assignment_id = p_assignment_id
1149 and p_effective_date between paa.effective_start_date
1150 and paa.effective_end_date
1151 and p_effective_date between pgs.effective_start_date
1152 and pgs.effective_end_date;
1153 Begin
1154 --
1155 hr_utility.set_location('Entering:'||l_proc, 10);
1156 --
1157 -- Get the parent_spine_id (Not passed in api as updated in the assignment
1158 -- form)
1159 --
1160 open chk_step_valid_for_grade;
1161 fetch chk_step_valid_for_grade into l_parent_spine_id;
1162 --
1163 if chk_step_valid_for_grade%notfound then
1164 --
1165 close chk_step_valid_for_grade;
1166 --
1167 fnd_message.set_name('PER', 'HR_289834_STEP_INV_FOR_GRADE');
1168 hr_utility.raise_error;
1169 --
1170 end if;
1171 --
1172 close chk_step_valid_for_grade;
1173 --
1174 -- Call conversion function to turn arguments into the
1175 -- p_rec structure.
1176 --
1177 l_rec :=
1178 per_spp_shd.convert_args
1179 (null
1180 ,null
1181 ,null
1182 ,p_business_group_id
1183 ,p_assignment_id
1184 ,p_step_id
1185 ,p_auto_increment_flag
1186 ,l_parent_spine_id
1187 ,p_reason
1191 ,p_program_update_date
1188 ,p_request_id
1189 ,p_program_application_id
1190 ,p_program_id
1192 ,p_increment_number
1193 ,p_information1
1194 ,p_information2
1195 ,p_information3
1196 ,p_information4
1197 ,p_information5
1198 ,p_information6
1199 ,p_information7
1200 ,p_information8
1201 ,p_information9
1202 ,p_information10
1203 ,p_information11
1204 ,p_information12
1205 ,p_information13
1206 ,p_information14
1207 ,p_information15
1208 ,p_information16
1209 ,p_information17
1210 ,p_information18
1211 ,p_information19
1212 ,p_information20
1213 ,p_information21
1214 ,p_information22
1215 ,p_information23
1216 ,p_information24
1217 ,p_information25
1218 ,p_information26
1219 ,p_information27
1220 ,p_information28
1221 ,p_information29
1222 ,p_information30
1223 ,p_information_category
1224 ,null
1225 );
1226 --
1227 -- Having converted the arguments into the per_spp_rec
1228 -- plsql record structure we call the corresponding record
1229 -- business process.
1230 --
1231 per_spp_ins.ins
1232 (p_effective_date
1233 ,l_rec
1234 ,p_replace_future_spp --Added bug2977842.
1235 );
1236 --
1237 -- Set the OUT arguments.
1238 --
1239 p_placement_id := l_rec.placement_id;
1240 p_effective_start_date := l_rec.effective_start_date;
1241 p_effective_end_date := l_rec.effective_end_date;
1242 p_object_version_number := l_rec.object_version_number;
1243 --
1244 --
1245 hr_utility.set_location(' Leaving:'||l_proc, 999);
1246 --
1247 End ins;
1248 --
1249 end per_spp_ins;