[Home] [Help]
PACKAGE BODY: APPS.HR_SP_PLACEMENT_API
Source
1 Package Body hr_sp_placement_api as
2 /* $Header: pesppapi.pkb 120.3 2009/08/25 06:35:08 ghshanka ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' HR_SP_PLACEMENT_API .';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------< create_spinal_point_placement >---------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_spp
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_business_group_id in number
16 ,p_assignment_id in number
17 ,p_step_id in number
18 ,p_auto_increment_flag in varchar2 default 'N'
19 ,p_reason in varchar2 default null
20 ,p_request_id in number default null
21 ,p_program_application_id in number default null
22 ,p_program_id in number default null
23 ,p_program_update_date in date default null
24 ,p_increment_number in number default null
25 ,p_information1 in varchar2 default null
26 ,p_information2 in varchar2 default null
27 ,p_information3 in varchar2 default null
28 ,p_information4 in varchar2 default null
29 ,p_information5 in varchar2 default null
30 ,p_information6 in varchar2 default null
31 ,p_information7 in varchar2 default null
32 ,p_information8 in varchar2 default null
33 ,p_information9 in varchar2 default null
34 ,p_information10 in varchar2 default null
35 ,p_information11 in varchar2 default null
36 ,p_information12 in varchar2 default null
37 ,p_information13 in varchar2 default null
38 ,p_information14 in varchar2 default null
39 ,p_information15 in varchar2 default null
40 ,p_information16 in varchar2 default null
41 ,p_information17 in varchar2 default null
42 ,p_information18 in varchar2 default null
43 ,p_information19 in varchar2 default null
44 ,p_information20 in varchar2 default null
45 ,p_information21 in varchar2 default null
46 ,p_information22 in varchar2 default null
47 ,p_information23 in varchar2 default null
48 ,p_information24 in varchar2 default null
49 ,p_information25 in varchar2 default null
50 ,p_information26 in varchar2 default null
51 ,p_information27 in varchar2 default null
52 ,p_information28 in varchar2 default null
53 ,p_information29 in varchar2 default null
54 ,p_information30 in varchar2 default null
55 ,p_information_category in varchar2 default null
56 ,p_placement_id out nocopy number
57 ,p_object_version_number out nocopy number
58 ,p_effective_start_date out nocopy date
59 ,p_effective_end_date out nocopy date
60 ,p_replace_future_spp in boolean default false --Bug 2977842.
61 ) is
62 --
63 -- Declare cursors and local variables
64 --
65 l_effective_date date;
66 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
67 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
68 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
69 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
70 l_gsp_post_process_warning varchar2(30);
71 --
72 l_proc varchar2(72) := g_package||'create_spinal_point_placement';
73 --
74 begin
75
76 hr_utility.set_location('Entering:'|| l_proc, 12);
77
78 hr_sp_placement_api.create_spp
79 (p_validate => p_validate
80 ,p_effective_date => p_effective_date
81 ,p_business_group_id => p_business_group_id
82 ,p_assignment_id => p_assignment_id
83 ,p_step_id => p_step_id
84 ,p_auto_increment_flag => p_auto_increment_flag
85 ,p_reason => p_reason
86 ,p_request_id => p_request_id
87 ,p_program_application_id => p_program_application_id
88 ,p_program_id => p_program_id
89 ,p_program_update_date => p_program_update_date
90 ,p_increment_number => p_increment_number
91 ,p_information1 => p_information1
92 ,p_information2 => p_information2
93 ,p_information3 => p_information3
94 ,p_information4 => p_information4
95 ,p_information5 => p_information5
96 ,p_information6 => p_information6
97 ,p_information7 => p_information7
98 ,p_information8 => p_information8
99 ,p_information9 => p_information9
100 ,p_information10 => p_information10
101 ,p_information11 => p_information11
102 ,p_information12 => p_information12
103 ,p_information13 => p_information13
104 ,p_information14 => p_information14
105 ,p_information15 => p_information15
106 ,p_information16 => p_information16
107 ,p_information17 => p_information17
108 ,p_information18 => p_information18
109 ,p_information19 => p_information19
110 ,p_information20 => p_information20
111 ,p_information21 => p_information21
112 ,p_information22 => p_information22
113 ,p_information23 => p_information23
114 ,p_information24 => p_information24
115 ,p_information25 => p_information25
116 ,p_information26 => p_information26
117 ,p_information27 => p_information27
118 ,p_information28 => p_information28
119 ,p_information29 => p_information29
120 ,p_information30 => p_information30
121 ,p_information_category => p_information_category
122 ,p_placement_id => l_placement_id
123 ,p_object_version_number => l_object_version_number
124 ,p_effective_start_date => l_effective_start_date
125 ,p_effective_end_date => l_effective_end_date
126 ,p_replace_future_spp => p_replace_future_spp --Bug 2977842.
127 ,p_gsp_post_process_warning => l_gsp_post_process_warning
128 );
129 --
130 -- Set all output arguments
131 --
132 p_placement_id := l_placement_id;
133 p_object_version_number := l_object_version_number;
134 p_effective_start_date := l_effective_start_date;
135 p_effective_end_date := l_effective_end_date;
136 --
137 hr_utility.set_location(' Leaving:'||l_proc, 70);
138 end create_spp;
139 -- ----------------------------------------------------------------------------
140 -- |--------------------< create_spinal_point_placement OVERLOAD>-------------|
141 -- ----------------------------------------------------------------------------
142 --
143 procedure create_spp
144 (p_validate in boolean default false
145 ,p_effective_date in date
146 ,p_business_group_id in number
147 ,p_assignment_id in number
148 ,p_step_id in number
149 ,p_auto_increment_flag in varchar2 default 'N'
150 ,p_reason in varchar2 default null
151 ,p_request_id in number default null
152 ,p_program_application_id in number default null
153 ,p_program_id in number default null
154 ,p_program_update_date in date default null
155 ,p_increment_number in number default null
156 ,p_information1 in varchar2 default null
157 ,p_information2 in varchar2 default null
158 ,p_information3 in varchar2 default null
159 ,p_information4 in varchar2 default null
160 ,p_information5 in varchar2 default null
161 ,p_information6 in varchar2 default null
162 ,p_information7 in varchar2 default null
163 ,p_information8 in varchar2 default null
164 ,p_information9 in varchar2 default null
165 ,p_information10 in varchar2 default null
166 ,p_information11 in varchar2 default null
167 ,p_information12 in varchar2 default null
168 ,p_information13 in varchar2 default null
169 ,p_information14 in varchar2 default null
170 ,p_information15 in varchar2 default null
171 ,p_information16 in varchar2 default null
172 ,p_information17 in varchar2 default null
173 ,p_information18 in varchar2 default null
174 ,p_information19 in varchar2 default null
175 ,p_information20 in varchar2 default null
176 ,p_information21 in varchar2 default null
177 ,p_information22 in varchar2 default null
178 ,p_information23 in varchar2 default null
179 ,p_information24 in varchar2 default null
180 ,p_information25 in varchar2 default null
181 ,p_information26 in varchar2 default null
182 ,p_information27 in varchar2 default null
183 ,p_information28 in varchar2 default null
184 ,p_information29 in varchar2 default null
185 ,p_information30 in varchar2 default null
186 ,p_information_category in varchar2 default null
187 ,p_placement_id out nocopy number
188 ,p_object_version_number out nocopy number
189 ,p_effective_start_date out nocopy date
190 ,p_effective_end_date out nocopy date
191 ,p_replace_future_spp in boolean default false --Bug 2977842.
192 ,p_gsp_post_process_warning out nocopy varchar2
193 ) is
194 --
195 -- Declare cursors and local variables
196 --
197 l_effective_date date;
198 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
199 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
200 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
201 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
202 l_gsp_post_process_warning varchar2(30);
203 --
204 l_proc varchar2(72) := g_package||'create_spinal_point_placement';
205 --
206 begin
207
208 hr_utility.set_location('Entering:'|| l_proc, 12);
209 hr_utility.set_location('Entering:'|| p_auto_increment_flag, 15);
210 --
211 -- Issue a savepoint
212 --
213 savepoint create_spinal_point_placement;
214 --
215 -- Check that all not null parameters have a variable passed in
216 --
217 hr_utility.set_location('Entering mandatory arg check', 20);
218 --
219 hr_api.mandatory_arg_error
220 (p_api_name => l_proc,
221 p_argument => 'assignment_id',
222 p_argument_value => p_assignment_id);
223 --
224 hr_api.mandatory_arg_error
225 (p_api_name => l_proc,
226 p_argument => 'business_group_id',
227 p_argument_value => p_business_group_id);
228 --
229 hr_api.mandatory_arg_error
230 (p_api_name => l_proc,
231 p_argument => 'effective_date',
232 p_argument_value => p_effective_date);
233 --
234 hr_api.mandatory_arg_error
235 (p_api_name => l_proc,
236 p_argument => 'step_id',
237 p_argument_value => p_step_id);
238 --
239 hr_api.mandatory_arg_error
240 (p_api_name => l_proc,
241 p_argument => 'auto_increment_flag',
242 p_argument_value => p_auto_increment_flag);
243 --
244 /*
245 hr_api.mandatory_arg_error
246 (p_api_name => l_proc,
247 p_argument => 'parent_spine_id',
248 p_argument_value => p_parent_spine_id);
249 */
250 --
251 l_effective_date := trunc(p_effective_date);
252 --
253 hr_utility.set_location('Entering: call - create_spinal_point_placement_b ', 30);
254 --
255
256 begin
257
258 hr_sp_placement_api_bk1.create_sp_placement_b
259 (p_effective_date => l_effective_date
260 ,p_business_group_id => p_business_group_id
261 ,p_assignment_id => p_assignment_id
262 ,p_step_id => p_step_id
263 ,p_auto_increment_flag => p_auto_increment_flag
264 ,p_reason => p_reason
265 ,p_request_id => p_request_id
266 ,p_program_application_id => p_program_application_id
267 ,p_program_id => p_program_id
268 ,p_program_update_date => p_program_update_date
269 ,p_increment_number => p_increment_number
270 ,p_information1 => p_information1
271 ,p_information2 => p_information2
272 ,p_information3 => p_information3
273 ,p_information4 => p_information4
274 ,p_information5 => p_information5
275 ,p_information6 => p_information6
276 ,p_information7 => p_information7
277 ,p_information8 => p_information8
278 ,p_information9 => p_information9
279 ,p_information10 => p_information10
280 ,p_information11 => p_information11
281 ,p_information12 => p_information12
282 ,p_information13 => p_information13
283 ,p_information14 => p_information14
284 ,p_information15 => p_information15
285 ,p_information16 => p_information16
286 ,p_information17 => p_information17
287 ,p_information18 => p_information18
288 ,p_information19 => p_information19
289 ,p_information20 => p_information20
290 ,p_information21 => p_information21
291 ,p_information22 => p_information22
292 ,p_information23 => p_information23
293 ,p_information24 => p_information24
294 ,p_information25 => p_information25
295 ,p_information26 => p_information26
296 ,p_information27 => p_information27
297 ,p_information28 => p_information28
298 ,p_information29 => p_information29
299 ,p_information30 => p_information30
300 ,p_information_category => p_information_category
301 );
302 exception
303 when hr_api.cannot_find_prog_unit then
304 hr_api.cannot_find_prog_unit_error
305 (p_module_name => 'CREATE_SPINAL_POINT_PLACEMENT'
306 ,p_hook_type => 'BP'
307 );
308
309 end;
310
311 --
312 hr_utility.set_location('Entering: call - per_spp_ins.ins ', 40);
313 --
314 per_spp_ins.ins
315 (p_effective_date => l_effective_date
316 ,p_business_group_id => p_business_group_id
317 ,p_assignment_id => p_assignment_id
318 ,p_step_id => p_step_id
319 ,p_auto_increment_flag => p_auto_increment_flag
320 ,p_reason => p_reason
321 ,p_request_id => p_request_id
322 ,p_program_application_id => p_program_application_id
323 ,p_program_id => p_program_id
324 ,p_program_update_date => p_program_update_date
325 ,p_increment_number => p_increment_number
326 ,p_placement_id => l_placement_id
327 ,p_object_version_number => l_object_version_number
328 ,p_effective_start_date => l_effective_start_date
329 ,p_effective_end_date => l_effective_end_date
330 ,p_information1 => p_information1
331 ,p_information2 => p_information2
332 ,p_information3 => p_information3
333 ,p_information4 => p_information4
334 ,p_information5 => p_information5
335 ,p_information6 => p_information6
336 ,p_information7 => p_information7
337 ,p_information8 => p_information8
338 ,p_information9 => p_information9
339 ,p_information10 => p_information10
340 ,p_information11 => p_information11
341 ,p_information12 => p_information12
342 ,p_information13 => p_information13
343 ,p_information14 => p_information14
344 ,p_information15 => p_information15
345 ,p_information16 => p_information16
346 ,p_information17 => p_information17
347 ,p_information18 => p_information18
348 ,p_information19 => p_information19
349 ,p_information20 => p_information20
350 ,p_information21 => p_information21
351 ,p_information22 => p_information22
352 ,p_information23 => p_information23
353 ,p_information24 => p_information24
354 ,p_information25 => p_information25
355 ,p_information26 => p_information26
356 ,p_information27 => p_information27
357 ,p_information28 => p_information28
358 ,p_information29 => p_information29
359 ,p_information30 => p_information30
360 ,p_information_category => p_information_category
361 ,p_replace_future_spp => p_replace_future_spp -- bug 8762555
362 );
363 --
364 hr_utility.set_location('Entering: call - create_spinal_point_placement_a', 50);
365 --
366
367 begin
368 hr_sp_placement_api_bk1.create_sp_placement_a
369 (p_effective_date => l_effective_date
370 ,p_business_group_id => p_business_group_id
371 ,p_assignment_id => p_assignment_id
372 ,p_step_id => p_step_id
373 ,p_auto_increment_flag => p_auto_increment_flag
374 ,p_reason => p_reason
375 ,p_request_id => p_request_id
376 ,p_program_application_id => p_program_application_id
377 ,p_program_id => p_program_id
378 ,p_program_update_date => p_program_update_date
379 ,p_increment_number => p_increment_number
380 ,p_placement_id => l_placement_id
381 ,p_object_version_number => l_object_version_number
382 ,p_effective_start_date => l_effective_start_date
383 ,p_effective_end_date => l_effective_end_date
384 ,p_information1 => p_information1
385 ,p_information2 => p_information2
386 ,p_information3 => p_information3
387 ,p_information4 => p_information4
388 ,p_information5 => p_information5
389 ,p_information6 => p_information6
390 ,p_information7 => p_information7
391 ,p_information8 => p_information8
392 ,p_information9 => p_information9
393 ,p_information10 => p_information10
394 ,p_information11 => p_information11
395 ,p_information12 => p_information12
396 ,p_information13 => p_information13
397 ,p_information14 => p_information14
398 ,p_information15 => p_information15
399 ,p_information16 => p_information16
400 ,p_information17 => p_information17
401 ,p_information18 => p_information18
402 ,p_information19 => p_information19
403 ,p_information20 => p_information20
404 ,p_information21 => p_information21
405 ,p_information22 => p_information22
406 ,p_information23 => p_information23
407 ,p_information24 => p_information24
408 ,p_information25 => p_information25
409 ,p_information26 => p_information26
410 ,p_information27 => p_information27
411 ,p_information28 => p_information28
412 ,p_information29 => p_information29
413 ,p_information30 => p_information30
414 ,p_information_category => p_information_category
415 );
416
417 exception
418 when hr_api.cannot_find_prog_unit then
419 hr_api.cannot_find_prog_unit_error
420 (p_module_name => 'CREATE_SPINAL_POINT_PLACEMENT'
421 ,p_hook_type => 'AP'
422 );
423 end;
424
425 --
426 -- call pqh post process procedure -- bug 2999562
427 --
428 pqh_gsp_post_process.call_pp_from_assignments(
429 p_effective_date => l_effective_date
430 ,p_assignment_id => p_assignment_id
431 ,p_date_track_mode => NULL
432 ,p_warning_mesg => l_gsp_post_process_warning
433 );
434
435 --
436 -- When in validation only mode raise the Validate_Enabled exception
437 --
438 if p_validate then
439 raise hr_api.validate_enabled;
440 end if;
441 --
442 -- Set all output arguments
443 --
444 p_placement_id := l_placement_id;
445 p_object_version_number := l_object_version_number;
446 p_effective_start_date := l_effective_start_date;
447 p_effective_end_date := l_effective_end_date;
448 p_gsp_post_process_warning := l_gsp_post_process_warning;
449 --
450 hr_utility.set_location(' Leaving:'||l_proc, 70);
451 exception
452 when hr_api.validate_enabled then
453 --
454 -- As the Validate_Enabled exception has been raised
455 -- we must rollback to the savepoint
456 --
457 rollback to create_spinal_point_placement;
458 --
459 -- Only set output warning arguments
460 -- (Any key or derived arguments must be set to null
461 -- when validation only mode is being used.)
462 --
463 p_placement_id := null;
464 p_object_version_number := null;
465 p_effective_start_date := null;
466 p_effective_end_date := null;
467 p_gsp_post_process_warning := null;
468 --
469 hr_utility.set_location(' Leaving:'||l_proc, 80);
470 when others then
471 --
472 -- A validation or unexpected error has occured
473 --
474 p_placement_id := null;
475 p_object_version_number := null;
476 p_effective_start_date := null;
477 p_effective_end_date := null;
478 p_gsp_post_process_warning := null;
479 rollback to create_spinal_point_placement;
480 hr_utility.set_location(' Leaving:'||l_proc, 90);
481 raise;
482 end create_spp;
483 --
484
485 -- ----------------------------------------------------------------------------
486 -- |--------------------< update_spinal_point_placement >---------------------|
487 -- ----------------------------------------------------------------------------
488 --
489 procedure update_spp
490 (p_validate in boolean default false
491 ,p_effective_date in date
492 ,p_datetrack_mode in varchar2 default hr_api.g_update
493 ,p_placement_id in number
494 ,p_object_version_number in out nocopy number
495 ,p_step_id in number default hr_api.g_number
496 ,p_auto_increment_flag in varchar2 default hr_api.g_varchar2
497 ,p_reason in varchar2 default hr_api.g_varchar2
498 ,p_request_id in number default hr_api.g_number
499 ,p_program_application_id in number default hr_api.g_number
500 ,p_program_id in number default hr_api.g_number
501 ,p_program_update_date in date default hr_api.g_date
502 ,p_increment_number in number default hr_api.g_number
503 ,p_information1 in varchar2 default hr_api.g_varchar2
504 ,p_information2 in varchar2 default hr_api.g_varchar2
505 ,p_information3 in varchar2 default hr_api.g_varchar2
506 ,p_information4 in varchar2 default hr_api.g_varchar2
507 ,p_information5 in varchar2 default hr_api.g_varchar2
508 ,p_information6 in varchar2 default hr_api.g_varchar2
509 ,p_information7 in varchar2 default hr_api.g_varchar2
510 ,p_information8 in varchar2 default hr_api.g_varchar2
511 ,p_information9 in varchar2 default hr_api.g_varchar2
512 ,p_information10 in varchar2 default hr_api.g_varchar2
513 ,p_information11 in varchar2 default hr_api.g_varchar2
514 ,p_information12 in varchar2 default hr_api.g_varchar2
515 ,p_information13 in varchar2 default hr_api.g_varchar2
516 ,p_information14 in varchar2 default hr_api.g_varchar2
517 ,p_information15 in varchar2 default hr_api.g_varchar2
518 ,p_information16 in varchar2 default hr_api.g_varchar2
519 ,p_information17 in varchar2 default hr_api.g_varchar2
520 ,p_information18 in varchar2 default hr_api.g_varchar2
521 ,p_information19 in varchar2 default hr_api.g_varchar2
522 ,p_information20 in varchar2 default hr_api.g_varchar2
523 ,p_information21 in varchar2 default hr_api.g_varchar2
524 ,p_information22 in varchar2 default hr_api.g_varchar2
525 ,p_information23 in varchar2 default hr_api.g_varchar2
526 ,p_information24 in varchar2 default hr_api.g_varchar2
527 ,p_information25 in varchar2 default hr_api.g_varchar2
528 ,p_information26 in varchar2 default hr_api.g_varchar2
529 ,p_information27 in varchar2 default hr_api.g_varchar2
530 ,p_information28 in varchar2 default hr_api.g_varchar2
531 ,p_information29 in varchar2 default hr_api.g_varchar2
532 ,p_information30 in varchar2 default hr_api.g_varchar2
533 ,p_information_category in varchar2 default hr_api.g_varchar2
534 ,p_effective_start_date in out nocopy date
535 ,p_effective_end_date in out nocopy date
536 ) is
537 --
538 -- Declare cursors and local variables
539 --
540 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
541 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
542 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
543 l_sequence_number number;
544 l_next_sequence number;
545 l_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
546 l_next_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
547 l_step_id per_spinal_point_placements_f.step_id%TYPE;
548 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
549 l_assignment_id per_spinal_point_placements_f.assignment_id%TYPE;
550 l_business_group_id per_spinal_point_placements_f.business_group_id%TYPE;
551 l_datetrack_mode varchar2(30);
552 l_temp_ovn number := p_object_version_number;
553 l_effective_end_date_temp per_spinal_point_placements_f.effective_end_date%TYPE
554 := trunc(p_effective_end_date);
555 l_effective_start_date_temp per_spinal_point_placements_f.effective_start_date%TYPE
556 := trunc(p_effective_start_date);
557 l_gsp_post_process_warning varchar2(30);
558 --
559
560 --
561 -- Declare out parameters
562 l_effective_date date;
563 --
564 l_proc varchar2(72) := g_package||'update_spinal_point_placement';
565 --
566 begin
567 hr_utility.set_location('Entering:'|| l_proc, 10);
568
569 l_effective_date := trunc(p_effective_date);
570 l_effective_start_date := p_effective_start_date;
571 l_effective_end_date := p_effective_end_date;
572 l_object_version_number := p_object_version_number;
573
574 hr_sp_placement_api.update_spp
575 (p_validate => p_validate
576 ,p_effective_date => l_effective_date
577 ,p_datetrack_mode => p_datetrack_mode
578 ,p_placement_id => p_placement_id
579 ,p_object_version_number => l_object_version_number
580 ,p_step_id => p_step_id
581 ,p_auto_increment_flag => p_auto_increment_flag
582 ,p_reason => p_reason
583 ,p_request_id => p_request_id
584 ,p_program_application_id => p_program_application_id
585 ,p_program_id => p_program_id
586 ,p_program_update_date => p_program_update_date
587 ,p_increment_number => p_increment_number
588 ,p_information1 => p_information1
589 ,p_information2 => p_information2
590 ,p_information3 => p_information3
591 ,p_information4 => p_information4
592 ,p_information5 => p_information5
593 ,p_information6 => p_information6
594 ,p_information7 => p_information7
595 ,p_information8 => p_information8
596 ,p_information9 => p_information9
597 ,p_information10 => p_information10
598 ,p_information11 => p_information11
599 ,p_information12 => p_information12
600 ,p_information13 => p_information13
601 ,p_information14 => p_information14
602 ,p_information15 => p_information15
603 ,p_information16 => p_information16
604 ,p_information17 => p_information17
605 ,p_information18 => p_information18
606 ,p_information19 => p_information19
607 ,p_information20 => p_information20
608 ,p_information21 => p_information21
609 ,p_information22 => p_information22
610 ,p_information23 => p_information23
611 ,p_information24 => p_information24
612 ,p_information25 => p_information25
613 ,p_information26 => p_information26
614 ,p_information27 => p_information27
615 ,p_information28 => p_information28
616 ,p_information29 => p_information29
617 ,p_information30 => p_information30
618 ,p_information_category => p_information_category
619 ,p_effective_start_date => l_effective_start_date
620 ,p_effective_end_date => l_effective_end_date
621 ,p_gsp_post_process_warning => l_gsp_post_process_warning
622 );
623
624 p_effective_start_date := l_effective_start_date;
625 p_effective_end_date := l_effective_end_date;
626 p_object_version_number := l_object_version_number;
627
628 hr_utility.set_location(' Leaving:'||l_proc, 70);
629 --
630 end update_spp;
631
632 --
633 -- ----------------------------------------------------------------------------
634 -- |--------------------< update_spinal_point_placement OVERLOAD>------------|
635 -- ----------------------------------------------------------------------------
636 --
637 procedure update_spp
638 (p_validate in boolean default false
639 ,p_effective_date in date
640 ,p_datetrack_mode in varchar2 default hr_api.g_update
641 ,p_placement_id in number
642 ,p_object_version_number in out nocopy number
643 ,p_step_id in number default hr_api.g_number
644 ,p_auto_increment_flag in varchar2 default hr_api.g_varchar2
645 ,p_reason in varchar2 default hr_api.g_varchar2
646 ,p_request_id in number default hr_api.g_number
647 ,p_program_application_id in number default hr_api.g_number
648 ,p_program_id in number default hr_api.g_number
649 ,p_program_update_date in date default hr_api.g_date
650 ,p_increment_number in number default hr_api.g_number
651 ,p_information1 in varchar2 default hr_api.g_varchar2
652 ,p_information2 in varchar2 default hr_api.g_varchar2
653 ,p_information3 in varchar2 default hr_api.g_varchar2
654 ,p_information4 in varchar2 default hr_api.g_varchar2
655 ,p_information5 in varchar2 default hr_api.g_varchar2
656 ,p_information6 in varchar2 default hr_api.g_varchar2
657 ,p_information7 in varchar2 default hr_api.g_varchar2
658 ,p_information8 in varchar2 default hr_api.g_varchar2
659 ,p_information9 in varchar2 default hr_api.g_varchar2
660 ,p_information10 in varchar2 default hr_api.g_varchar2
661 ,p_information11 in varchar2 default hr_api.g_varchar2
662 ,p_information12 in varchar2 default hr_api.g_varchar2
663 ,p_information13 in varchar2 default hr_api.g_varchar2
664 ,p_information14 in varchar2 default hr_api.g_varchar2
665 ,p_information15 in varchar2 default hr_api.g_varchar2
666 ,p_information16 in varchar2 default hr_api.g_varchar2
667 ,p_information17 in varchar2 default hr_api.g_varchar2
668 ,p_information18 in varchar2 default hr_api.g_varchar2
669 ,p_information19 in varchar2 default hr_api.g_varchar2
670 ,p_information20 in varchar2 default hr_api.g_varchar2
671 ,p_information21 in varchar2 default hr_api.g_varchar2
672 ,p_information22 in varchar2 default hr_api.g_varchar2
673 ,p_information23 in varchar2 default hr_api.g_varchar2
674 ,p_information24 in varchar2 default hr_api.g_varchar2
675 ,p_information25 in varchar2 default hr_api.g_varchar2
676 ,p_information26 in varchar2 default hr_api.g_varchar2
677 ,p_information27 in varchar2 default hr_api.g_varchar2
678 ,p_information28 in varchar2 default hr_api.g_varchar2
679 ,p_information29 in varchar2 default hr_api.g_varchar2
680 ,p_information30 in varchar2 default hr_api.g_varchar2
681 ,p_information_category in varchar2 default hr_api.g_varchar2
682 ,p_effective_start_date in out nocopy date
683 ,p_effective_end_date in out nocopy date
684 ,p_gsp_post_process_warning out nocopy varchar2
685 ) is
686 --
687 -- Declare cursors and local variables
688 --
689 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
690 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
691 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
692 l_sequence_number number;
693 l_next_sequence number;
694 l_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
695 l_next_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
696 l_step_id per_spinal_point_placements_f.step_id%TYPE;
697 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
698 l_assignment_id per_spinal_point_placements_f.assignment_id%TYPE;
699 l_business_group_id per_spinal_point_placements_f.business_group_id%TYPE;
700 l_datetrack_mode varchar2(30);
701 l_temp_ovn number := p_object_version_number;
702 l_effective_end_date_temp per_spinal_point_placements_f.effective_end_date%TYPE
703 := trunc(p_effective_end_date);
704 l_effective_start_date_temp per_spinal_point_placements_f.effective_start_date%TYPE
705 := trunc(p_effective_start_date);
706 l_gsp_post_process_warning varchar2(30);
707 --
708
709 --
710 -- Declare out parameters
711 l_effective_date date;
712 --
713 l_proc varchar2(72) := g_package||'update_spinal_point_placement';
714 --
715 begin
716 hr_utility.set_location('Entering:'|| l_proc, 10);
717 --
718 -- Issue a savepoint
719 --
720 savepoint update_spinal_point_placement;
721 --
722 -- Check that all required parameters are not null
723 --
724 hr_utility.set_location('Entering mandatory arg check', 20);
725 --
726 hr_api.mandatory_arg_error
727 (p_api_name => l_proc,
728 p_argument => 'placement_id',
729 p_argument_value => p_placement_id);
730 --
731 hr_api.mandatory_arg_error
732 (p_api_name => l_proc,
733 p_argument => 'effective_date',
734 p_argument_value => p_effective_date);
735 --
736 hr_api.mandatory_arg_error
737 (p_api_name => l_proc,
738 p_argument => 'object_version_number',
739 p_argument_value => p_object_version_number);
740 --
741 l_effective_date := trunc(p_effective_date);
742 l_effective_start_date := p_effective_start_date;
743 l_effective_end_date := p_effective_end_date;
744 l_object_version_number := p_object_version_number;
745 l_datetrack_mode := p_datetrack_mode;
746 --
747 hr_utility.set_location('Entering: call - update_spinal_point_placement_b ', 30);
748 hr_utility.set_location('Increment Number:'||p_increment_number,1);
749 hr_utility.set_location('Effective_date:'||l_effective_date,9);
750 hr_utility.set_location('EFFECTIVE_END_DATE : '||p_effective_end_date,9);
751 --
752
753 --
754 -- Get the non passed parameters
755 --
756
757 hr_utility.set_location(p_placement_id,33);
758 --
759 hr_utility.set_location(l_proc,20);
760 select parent_spine_id, business_group_id, assignment_id
761 into l_parent_spine_id, l_business_group_id, l_assignment_id
762 from per_spinal_point_placements_f
763 where placement_id = p_placement_id
764 and l_effective_date between effective_start_date
765 and effective_end_date;
766 --
767 -- Call the user hook before update process
768 --
769 hr_utility.set_location(l_assignment_id,22);
770
771 begin
772
773 hr_sp_placement_api_bk2.update_sp_placement_b
774 (p_effective_date => l_effective_date
775 ,p_datetrack_mode => p_datetrack_mode
776 ,p_placement_id => p_placement_id
777 ,p_object_version_number => p_object_version_number
778 ,p_business_group_id => l_business_group_id
779 ,p_assignment_id => l_assignment_id
780 ,p_step_id => p_step_id
781 ,p_auto_increment_flag => p_auto_increment_flag
782 ,p_parent_spine_id => l_parent_spine_id
783 ,p_reason => p_reason
784 ,p_request_id => p_request_id
785 ,p_program_application_id => p_program_application_id
786 ,p_program_id => p_program_id
787 ,p_program_update_date => p_program_update_date
788 ,p_increment_number => p_increment_number
789 ,p_information1 => p_information1
790 ,p_information2 => p_information2
791 ,p_information3 => p_information3
792 ,p_information4 => p_information4
793 ,p_information5 => p_information5
794 ,p_information6 => p_information6
795 ,p_information7 => p_information7
796 ,p_information8 => p_information8
797 ,p_information9 => p_information9
798 ,p_information10 => p_information10
799 ,p_information11 => p_information11
800 ,p_information12 => p_information12
801 ,p_information13 => p_information13
802 ,p_information14 => p_information14
803 ,p_information15 => p_information15
804 ,p_information16 => p_information16
805 ,p_information17 => p_information17
806 ,p_information18 => p_information18
807 ,p_information19 => p_information19
808 ,p_information20 => p_information20
809 ,p_information21 => p_information21
810 ,p_information22 => p_information22
811 ,p_information23 => p_information23
812 ,p_information24 => p_information24
813 ,p_information25 => p_information25
814 ,p_information26 => p_information26
815 ,p_information27 => p_information27
816 ,p_information28 => p_information28
817 ,p_information29 => p_information29
818 ,p_information30 => p_information30
819 ,p_information_category => p_information_category
820 -- ,p_effective_start_date => l_effective_start_date
821 -- ,p_effetcive_end_date => l_effective_end_date
822 );
823 exception
824 when hr_api.cannot_find_prog_unit then
825 hr_api.cannot_find_prog_unit_error
826 (p_module_name => 'UPDATE_SPINAL_POINT_PLACEMENT'
827 ,p_hook_type => 'BP'
828 );
829
830 end;
831 hr_utility.set_location(l_proc,21);
832 hr_utility.set_location('Entering: call - per_spp_upd.upd ', 40);
833 --
834
835 per_spp_upd.upd
836 (p_effective_date => l_effective_date
837 ,p_datetrack_mode => l_datetrack_mode
838 ,p_placement_id => p_placement_id
839 ,p_object_version_number => l_object_version_number
840 ,p_step_id => p_step_id
841 ,p_auto_increment_flag => p_auto_increment_flag
842 ,p_assignment_id => l_assignment_id
843 ,p_reason => p_reason
844 ,p_request_id => p_request_id
845 ,p_program_application_id => p_program_application_id
846 ,p_program_id => p_program_id
847 ,p_program_update_date => p_program_update_date
848 ,p_increment_number => p_increment_number
849 ,p_effective_start_date => l_effective_start_date
850 ,p_effective_end_date => l_effective_end_date
851 ,p_information1 => p_information1
852 ,p_information2 => p_information2
853 ,p_information3 => p_information3
854 ,p_information4 => p_information4
855 ,p_information5 => p_information5
856 ,p_information6 => p_information6
857 ,p_information7 => p_information7
858 ,p_information8 => p_information8
859 ,p_information9 => p_information9
860 ,p_information10 => p_information10
861 ,p_information11 => p_information11
862 ,p_information12 => p_information12
863 ,p_information13 => p_information13
864 ,p_information14 => p_information14
865 ,p_information15 => p_information15
866 ,p_information16 => p_information16
867 ,p_information17 => p_information17
868 ,p_information18 => p_information18
869 ,p_information19 => p_information19
870 ,p_information20 => p_information20
871 ,p_information21 => p_information21
872 ,p_information22 => p_information22
873 ,p_information23 => p_information23
874 ,p_information24 => p_information24
875 ,p_information25 => p_information25
876 ,p_information26 => p_information26
877 ,p_information27 => p_information27
878 ,p_information28 => p_information28
879 ,p_information29 => p_information29
880 ,p_information30 => p_information30
881 ,p_information_category => p_information_category
882 );
883
884 --
885 hr_utility.set_location('l_placement_id'||l_placement_id,52);
886 --
887 p_effective_start_date := l_effective_start_date;
888 p_effective_end_date := l_effective_end_date;
889 p_object_version_number := l_object_version_number;
890 -- p_datetrack_mode := l_datetrack_mode;
891 --
892 hr_utility.set_location('p_effective_start_date'||p_effective_start_date,55);
893 hr_utility.set_location('Entering: call - update_spinal_point_placement_a', 50);
894 --
895
896 begin
897 --
898 hr_sp_placement_api_bk2.update_sp_placement_a
899 (p_effective_date => l_effective_date
900 ,p_datetrack_mode => p_datetrack_mode
901 ,p_placement_id => p_placement_id
902 ,p_object_version_number => p_object_version_number
903 ,p_business_group_id => l_business_group_id
904 ,p_assignment_id => l_assignment_id
905 ,p_step_id => p_step_id
906 ,p_auto_increment_flag => p_auto_increment_flag
907 ,p_parent_spine_id => l_parent_spine_id
908 ,p_reason => p_reason
909 ,p_request_id => p_request_id
910 ,p_program_application_id => p_program_application_id
911 ,p_program_id => p_program_id
912 ,p_program_update_date => p_program_update_date
913 ,p_increment_number => p_increment_number
914 ,p_effective_start_date => l_effective_start_date
915 ,p_effective_end_date => l_effective_end_date
916 ,p_information1 => p_information1
917 ,p_information2 => p_information2
918 ,p_information3 => p_information3
919 ,p_information4 => p_information4
920 ,p_information5 => p_information5
921 ,p_information6 => p_information6
922 ,p_information7 => p_information7
923 ,p_information8 => p_information8
924 ,p_information9 => p_information9
925 ,p_information10 => p_information10
926 ,p_information11 => p_information11
927 ,p_information12 => p_information12
928 ,p_information13 => p_information13
929 ,p_information14 => p_information14
930 ,p_information15 => p_information15
931 ,p_information16 => p_information16
932 ,p_information17 => p_information17
933 ,p_information18 => p_information18
934 ,p_information19 => p_information19
935 ,p_information20 => p_information20
936 ,p_information21 => p_information21
937 ,p_information22 => p_information22
938 ,p_information23 => p_information23
939 ,p_information24 => p_information24
940 ,p_information25 => p_information25
941 ,p_information26 => p_information26
942 ,p_information27 => p_information27
943 ,p_information28 => p_information28
944 ,p_information29 => p_information29
945 ,p_information30 => p_information30
946 ,p_information_category => p_information_category
947 );
948
949 exception
950 when hr_api.cannot_find_prog_unit then
951 hr_api.cannot_find_prog_unit_error
952 (p_module_name => 'UPDATE_SPINAL_POINT_PLACEMENT'
953 ,p_hook_type => 'AP'
954 );
955 end;
956
957 hr_utility.set_location(' Leaving:'||l_proc, 60);
958
959 --
960 -- call pqh post process procedure -- bug 2999562
961 --
962 pqh_gsp_post_process.call_pp_from_assignments(
963 p_effective_date => l_effective_date
964 ,p_assignment_id => l_assignment_id
965 ,p_date_track_mode => p_datetrack_mode
966 ,p_warning_mesg => l_gsp_post_process_warning
967 );
968
969 hr_utility.set_location(' Leaving:'||l_proc, 65);
970
971 --
972 -- When in validation only mode raise the Validate_Enabled exception
973 --
974 if p_validate then
975 raise hr_api.validate_enabled;
976 end if;
977 --
978 --
979 p_gsp_post_process_warning := l_gsp_post_process_warning;
980 --
981 --
982 hr_utility.set_location(' Leaving:'||l_proc, 70);
983 exception
984 when hr_api.validate_enabled then
985 --
986 -- As the Validate_Enabled exception has been raised
987 -- we must rollback to the savepoint
988 --
989 rollback to update_spinal_point_placement;
990 --
991 -- Only set output warning arguments
992 -- (Any key or derived arguments must be set to null
993 -- when validation only mode is being used.)
994 --
995 p_object_version_number := l_object_version_number;
996 --
997 hr_utility.set_location(' Leaving:'||l_proc, 80);
998 when others then
999 --
1000 -- A validation or unexpected error has occured
1001 --
1002 p_object_version_number := l_temp_ovn;
1003 p_effective_start_date := l_effective_start_date_temp;
1004 p_effective_end_date := l_effective_end_date_temp;
1005 p_gsp_post_process_warning := l_gsp_post_process_warning;
1006 rollback to update_spinal_point_placement;
1007 hr_utility.set_location(' Leaving:'||l_proc, 90);
1008 raise;
1009 end update_spp;
1010 --
1011 -- ----------------------------------------------------------------------------
1012 -- |----------------------< delete_spinal_point_placement >-------------------|
1013 -- ----------------------------------------------------------------------------
1014 --
1015 procedure delete_spp
1016 (p_validate in boolean default false
1017 ,p_effective_date in date
1018 ,p_datetrack_mode in varchar2 default hr_api.g_delete
1019 ,p_placement_id in number
1020 ,p_object_version_number in out nocopy number
1021 ,p_effective_start_date out nocopy date
1022 ,p_effective_end_date out nocopy date
1023 ) is
1024 --
1025 -- Declare cursors and local variables
1026 --
1027 l_proc varchar2(72) := (g_package||'delete_spinal_point_placement');
1028 l_effective_date date := trunc(p_effective_date);
1029 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
1030 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
1031 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
1032 l_temp_ovn number := p_object_version_number;
1033 --
1034 begin
1035 --
1036 hr_api.mandatory_arg_error
1037 (p_api_name => l_proc,
1038 p_argument => 'placement_id',
1039 p_argument_value => p_placement_id);
1040 --
1041 hr_api.mandatory_arg_error
1042 (p_api_name => l_proc,
1043 p_argument => 'effective_date',
1044 p_argument_value => p_effective_date);
1045 --
1046 hr_utility.set_location('Entering:'|| l_proc, 10);
1047 --
1048 -- Issue a savepoint if operating in validation only mode
1049 --
1050 savepoint delete_spinal_point_placement;
1051 --
1052 hr_utility.set_location(l_proc, 20);
1053 --
1054 l_object_version_number := p_object_version_number;
1055 -- Process Logic
1056 --
1057 --
1058
1059 begin
1060 --
1061 -- Start of API User Hook for the before hook of delete_spinal_point_placement
1062 --
1063 hr_sp_placement_api_bk3.delete_sp_placement_b
1064 (p_effective_date => l_effective_date
1065 ,p_datetrack_mode => p_datetrack_mode
1066 ,p_placement_id => p_placement_id
1067 ,p_object_version_number => p_object_version_number
1068 );
1069 exception
1070 when hr_api.cannot_find_prog_unit then
1071 hr_api.cannot_find_prog_unit_error
1072 (p_module_name => 'delete_spinal_point_placement'
1073 ,p_hook_type => 'BP'
1074 );
1075 --
1076 -- End of API User Hook for the before hook of delete_spinal_point_placement
1077 --
1078 end;
1079
1080 --
1081 per_spp_del.del
1082 (p_effective_date => l_effective_date
1083 ,p_datetrack_mode => p_datetrack_mode
1084 ,p_placement_id => p_placement_id
1085 ,p_object_version_number => p_object_version_number
1086 ,p_effective_start_date => l_effective_start_date
1087 ,p_effective_end_date => l_effective_end_date
1088 );
1089 --
1090
1091 begin
1092 --
1093 -- Start of API User Hook for the after hook of delete_spinal_point_placement
1094 --
1095 hr_sp_placement_api_bk3.delete_sp_placement_a
1096 (p_effective_date => l_effective_date
1097 ,p_datetrack_mode => p_datetrack_mode
1098 ,p_placement_id => p_placement_id
1099 ,p_object_version_number => p_object_version_number
1100 ,p_effective_start_date => l_effective_start_date
1101 ,p_effective_end_date => l_effective_end_date
1102 );
1103 exception
1104 when hr_api.cannot_find_prog_unit then
1105 hr_api.cannot_find_prog_unit_error
1106 (p_module_name => 'delete_spinal_point_placement'
1107 ,p_hook_type => 'AP'
1108 );
1109 --
1110 -- End of API User Hook for the after hook of delete_spinal_point_placement
1111 --
1112 end;
1113
1114 --
1115 hr_utility.set_location(l_proc, 60);
1116 --
1117 -- When in validation only mode raise the Validate_Enabled exception
1118 --
1119 if p_validate then
1120 raise hr_api.validate_enabled;
1121 end if;
1122 --
1123 hr_utility.set_location(' Leaving:'||l_proc, 70);
1124 --
1125 exception
1126 --
1127 when hr_api.validate_enabled then
1128 --
1129 -- As the Validate_Enabled exception has been raised
1130 -- we must rollback to the savepoint
1131 --
1132 ROLLBACK TO delete_spinal_point_placement;
1133 --
1134 -- Only set output warning arguments
1135 -- (Any key or derived arguments must be set to null
1136 -- when validation only mode is being used.)
1137 --
1138 --
1139 when others then
1140 --
1141 -- A validation or unexpected error has occured
1142 --
1143 p_object_version_number := l_temp_ovn;
1144 p_effective_start_date := null;
1145 p_effective_end_date := null;
1146 ROLLBACK TO delete_spinal_point_placement;
1147 raise;
1148 --
1149 end delete_spp;
1150 --
1151 --
1152 end hr_sp_placement_api;