[Home] [Help]
PACKAGE BODY: APPS.HR_SP_PLACEMENT_API
Source
1 Package Body hr_sp_placement_api as
2 /* $Header: pesppapi.pkb 120.2 2005/12/12 21:13:50 vbanner noship $ */
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 );
362 --
363 hr_utility.set_location('Entering: call - create_spinal_point_placement_a', 50);
364 --
365
366 begin
367 hr_sp_placement_api_bk1.create_sp_placement_a
368 (p_effective_date => l_effective_date
369 ,p_business_group_id => p_business_group_id
370 ,p_assignment_id => p_assignment_id
371 ,p_step_id => p_step_id
372 ,p_auto_increment_flag => p_auto_increment_flag
373 ,p_reason => p_reason
374 ,p_request_id => p_request_id
375 ,p_program_application_id => p_program_application_id
376 ,p_program_id => p_program_id
377 ,p_program_update_date => p_program_update_date
378 ,p_increment_number => p_increment_number
379 ,p_placement_id => l_placement_id
380 ,p_object_version_number => l_object_version_number
381 ,p_effective_start_date => l_effective_start_date
382 ,p_effective_end_date => l_effective_end_date
383 ,p_information1 => p_information1
384 ,p_information2 => p_information2
385 ,p_information3 => p_information3
386 ,p_information4 => p_information4
387 ,p_information5 => p_information5
388 ,p_information6 => p_information6
389 ,p_information7 => p_information7
390 ,p_information8 => p_information8
391 ,p_information9 => p_information9
392 ,p_information10 => p_information10
393 ,p_information11 => p_information11
394 ,p_information12 => p_information12
395 ,p_information13 => p_information13
396 ,p_information14 => p_information14
397 ,p_information15 => p_information15
398 ,p_information16 => p_information16
399 ,p_information17 => p_information17
400 ,p_information18 => p_information18
401 ,p_information19 => p_information19
402 ,p_information20 => p_information20
403 ,p_information21 => p_information21
404 ,p_information22 => p_information22
405 ,p_information23 => p_information23
406 ,p_information24 => p_information24
407 ,p_information25 => p_information25
408 ,p_information26 => p_information26
409 ,p_information27 => p_information27
410 ,p_information28 => p_information28
411 ,p_information29 => p_information29
412 ,p_information30 => p_information30
413 ,p_information_category => p_information_category
414 );
415
416 exception
417 when hr_api.cannot_find_prog_unit then
418 hr_api.cannot_find_prog_unit_error
419 (p_module_name => 'CREATE_SPINAL_POINT_PLACEMENT'
420 ,p_hook_type => 'AP'
421 );
422 end;
423
424 --
425 -- call pqh post process procedure -- bug 2999562
426 --
427 pqh_gsp_post_process.call_pp_from_assignments(
428 p_effective_date => l_effective_date
429 ,p_assignment_id => p_assignment_id
430 ,p_date_track_mode => NULL
431 ,p_warning_mesg => l_gsp_post_process_warning
432 );
433
434 --
435 -- When in validation only mode raise the Validate_Enabled exception
436 --
437 if p_validate then
438 raise hr_api.validate_enabled;
439 end if;
440 --
441 -- Set all output arguments
442 --
443 p_placement_id := l_placement_id;
444 p_object_version_number := l_object_version_number;
445 p_effective_start_date := l_effective_start_date;
446 p_effective_end_date := l_effective_end_date;
447 p_gsp_post_process_warning := l_gsp_post_process_warning;
448 --
449 hr_utility.set_location(' Leaving:'||l_proc, 70);
450 exception
451 when hr_api.validate_enabled then
452 --
453 -- As the Validate_Enabled exception has been raised
454 -- we must rollback to the savepoint
455 --
456 rollback to create_spinal_point_placement;
457 --
458 -- Only set output warning arguments
459 -- (Any key or derived arguments must be set to null
460 -- when validation only mode is being used.)
461 --
462 p_placement_id := null;
463 p_object_version_number := null;
464 p_effective_start_date := null;
465 p_effective_end_date := null;
466 p_gsp_post_process_warning := null;
467 --
468 hr_utility.set_location(' Leaving:'||l_proc, 80);
469 when others then
470 --
471 -- A validation or unexpected error has occured
472 --
473 p_placement_id := null;
474 p_object_version_number := null;
475 p_effective_start_date := null;
476 p_effective_end_date := null;
477 p_gsp_post_process_warning := null;
478 rollback to create_spinal_point_placement;
479 hr_utility.set_location(' Leaving:'||l_proc, 90);
480 raise;
481 end create_spp;
482 --
483
484 -- ----------------------------------------------------------------------------
485 -- |--------------------< update_spinal_point_placement >---------------------|
486 -- ----------------------------------------------------------------------------
487 --
488 procedure update_spp
489 (p_validate in boolean default false
490 ,p_effective_date in date
491 ,p_datetrack_mode in varchar2 default hr_api.g_update
492 ,p_placement_id in number
493 ,p_object_version_number in out nocopy number
494 ,p_step_id in number default hr_api.g_number
495 ,p_auto_increment_flag in varchar2 default hr_api.g_varchar2
496 ,p_reason in varchar2 default hr_api.g_varchar2
497 ,p_request_id in number default hr_api.g_number
498 ,p_program_application_id in number default hr_api.g_number
499 ,p_program_id in number default hr_api.g_number
500 ,p_program_update_date in date default hr_api.g_date
501 ,p_increment_number in number default hr_api.g_number
502 ,p_information1 in varchar2 default hr_api.g_varchar2
503 ,p_information2 in varchar2 default hr_api.g_varchar2
504 ,p_information3 in varchar2 default hr_api.g_varchar2
505 ,p_information4 in varchar2 default hr_api.g_varchar2
506 ,p_information5 in varchar2 default hr_api.g_varchar2
507 ,p_information6 in varchar2 default hr_api.g_varchar2
508 ,p_information7 in varchar2 default hr_api.g_varchar2
509 ,p_information8 in varchar2 default hr_api.g_varchar2
510 ,p_information9 in varchar2 default hr_api.g_varchar2
511 ,p_information10 in varchar2 default hr_api.g_varchar2
512 ,p_information11 in varchar2 default hr_api.g_varchar2
513 ,p_information12 in varchar2 default hr_api.g_varchar2
514 ,p_information13 in varchar2 default hr_api.g_varchar2
515 ,p_information14 in varchar2 default hr_api.g_varchar2
516 ,p_information15 in varchar2 default hr_api.g_varchar2
517 ,p_information16 in varchar2 default hr_api.g_varchar2
518 ,p_information17 in varchar2 default hr_api.g_varchar2
519 ,p_information18 in varchar2 default hr_api.g_varchar2
520 ,p_information19 in varchar2 default hr_api.g_varchar2
521 ,p_information20 in varchar2 default hr_api.g_varchar2
522 ,p_information21 in varchar2 default hr_api.g_varchar2
523 ,p_information22 in varchar2 default hr_api.g_varchar2
524 ,p_information23 in varchar2 default hr_api.g_varchar2
525 ,p_information24 in varchar2 default hr_api.g_varchar2
526 ,p_information25 in varchar2 default hr_api.g_varchar2
527 ,p_information26 in varchar2 default hr_api.g_varchar2
528 ,p_information27 in varchar2 default hr_api.g_varchar2
529 ,p_information28 in varchar2 default hr_api.g_varchar2
530 ,p_information29 in varchar2 default hr_api.g_varchar2
531 ,p_information30 in varchar2 default hr_api.g_varchar2
532 ,p_information_category in varchar2 default hr_api.g_varchar2
533 ,p_effective_start_date in out nocopy date
534 ,p_effective_end_date in out nocopy date
535 ) is
536 --
537 -- Declare cursors and local variables
538 --
539 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
540 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
541 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
542 l_sequence_number number;
543 l_next_sequence number;
544 l_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
545 l_next_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
546 l_step_id per_spinal_point_placements_f.step_id%TYPE;
547 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
548 l_assignment_id per_spinal_point_placements_f.assignment_id%TYPE;
549 l_business_group_id per_spinal_point_placements_f.business_group_id%TYPE;
550 l_datetrack_mode varchar2(30);
551 l_temp_ovn number := p_object_version_number;
552 l_effective_end_date_temp per_spinal_point_placements_f.effective_end_date%TYPE
553 := trunc(p_effective_end_date);
554 l_effective_start_date_temp per_spinal_point_placements_f.effective_start_date%TYPE
555 := trunc(p_effective_start_date);
556 l_gsp_post_process_warning varchar2(30);
557 --
558
559 --
560 -- Declare out parameters
561 l_effective_date date;
562 --
563 l_proc varchar2(72) := g_package||'update_spinal_point_placement';
564 --
565 begin
566 hr_utility.set_location('Entering:'|| l_proc, 10);
567
568 l_effective_date := trunc(p_effective_date);
569 l_effective_start_date := p_effective_start_date;
570 l_effective_end_date := p_effective_end_date;
571 l_object_version_number := p_object_version_number;
572
573 hr_sp_placement_api.update_spp
574 (p_validate => p_validate
575 ,p_effective_date => l_effective_date
576 ,p_datetrack_mode => p_datetrack_mode
577 ,p_placement_id => p_placement_id
578 ,p_object_version_number => l_object_version_number
579 ,p_step_id => p_step_id
580 ,p_auto_increment_flag => p_auto_increment_flag
581 ,p_reason => p_reason
582 ,p_request_id => p_request_id
583 ,p_program_application_id => p_program_application_id
584 ,p_program_id => p_program_id
585 ,p_program_update_date => p_program_update_date
586 ,p_increment_number => p_increment_number
587 ,p_information1 => p_information1
588 ,p_information2 => p_information2
589 ,p_information3 => p_information3
590 ,p_information4 => p_information4
591 ,p_information5 => p_information5
592 ,p_information6 => p_information6
593 ,p_information7 => p_information7
594 ,p_information8 => p_information8
595 ,p_information9 => p_information9
596 ,p_information10 => p_information10
597 ,p_information11 => p_information11
598 ,p_information12 => p_information12
599 ,p_information13 => p_information13
600 ,p_information14 => p_information14
601 ,p_information15 => p_information15
602 ,p_information16 => p_information16
603 ,p_information17 => p_information17
604 ,p_information18 => p_information18
605 ,p_information19 => p_information19
606 ,p_information20 => p_information20
607 ,p_information21 => p_information21
608 ,p_information22 => p_information22
609 ,p_information23 => p_information23
610 ,p_information24 => p_information24
611 ,p_information25 => p_information25
612 ,p_information26 => p_information26
613 ,p_information27 => p_information27
614 ,p_information28 => p_information28
615 ,p_information29 => p_information29
616 ,p_information30 => p_information30
617 ,p_information_category => p_information_category
618 ,p_effective_start_date => l_effective_start_date
619 ,p_effective_end_date => l_effective_end_date
620 ,p_gsp_post_process_warning => l_gsp_post_process_warning
621 );
622
623 p_effective_start_date := l_effective_start_date;
624 p_effective_end_date := l_effective_end_date;
625 p_object_version_number := l_object_version_number;
626
627 hr_utility.set_location(' Leaving:'||l_proc, 70);
628 --
629 end update_spp;
630
631 --
632 -- ----------------------------------------------------------------------------
633 -- |--------------------< update_spinal_point_placement OVERLOAD>------------|
634 -- ----------------------------------------------------------------------------
635 --
636 procedure update_spp
637 (p_validate in boolean default false
638 ,p_effective_date in date
639 ,p_datetrack_mode in varchar2 default hr_api.g_update
640 ,p_placement_id in number
641 ,p_object_version_number in out nocopy number
642 ,p_step_id in number default hr_api.g_number
643 ,p_auto_increment_flag in varchar2 default hr_api.g_varchar2
644 ,p_reason in varchar2 default hr_api.g_varchar2
645 ,p_request_id in number default hr_api.g_number
646 ,p_program_application_id in number default hr_api.g_number
647 ,p_program_id in number default hr_api.g_number
648 ,p_program_update_date in date default hr_api.g_date
649 ,p_increment_number in number default hr_api.g_number
650 ,p_information1 in varchar2 default hr_api.g_varchar2
651 ,p_information2 in varchar2 default hr_api.g_varchar2
652 ,p_information3 in varchar2 default hr_api.g_varchar2
653 ,p_information4 in varchar2 default hr_api.g_varchar2
654 ,p_information5 in varchar2 default hr_api.g_varchar2
655 ,p_information6 in varchar2 default hr_api.g_varchar2
656 ,p_information7 in varchar2 default hr_api.g_varchar2
657 ,p_information8 in varchar2 default hr_api.g_varchar2
658 ,p_information9 in varchar2 default hr_api.g_varchar2
659 ,p_information10 in varchar2 default hr_api.g_varchar2
660 ,p_information11 in varchar2 default hr_api.g_varchar2
661 ,p_information12 in varchar2 default hr_api.g_varchar2
662 ,p_information13 in varchar2 default hr_api.g_varchar2
663 ,p_information14 in varchar2 default hr_api.g_varchar2
664 ,p_information15 in varchar2 default hr_api.g_varchar2
665 ,p_information16 in varchar2 default hr_api.g_varchar2
666 ,p_information17 in varchar2 default hr_api.g_varchar2
667 ,p_information18 in varchar2 default hr_api.g_varchar2
668 ,p_information19 in varchar2 default hr_api.g_varchar2
669 ,p_information20 in varchar2 default hr_api.g_varchar2
670 ,p_information21 in varchar2 default hr_api.g_varchar2
671 ,p_information22 in varchar2 default hr_api.g_varchar2
672 ,p_information23 in varchar2 default hr_api.g_varchar2
673 ,p_information24 in varchar2 default hr_api.g_varchar2
674 ,p_information25 in varchar2 default hr_api.g_varchar2
675 ,p_information26 in varchar2 default hr_api.g_varchar2
676 ,p_information27 in varchar2 default hr_api.g_varchar2
677 ,p_information28 in varchar2 default hr_api.g_varchar2
678 ,p_information29 in varchar2 default hr_api.g_varchar2
679 ,p_information30 in varchar2 default hr_api.g_varchar2
680 ,p_information_category in varchar2 default hr_api.g_varchar2
681 ,p_effective_start_date in out nocopy date
682 ,p_effective_end_date in out nocopy date
683 ,p_gsp_post_process_warning out nocopy varchar2
684 ) is
685 --
686 -- Declare cursors and local variables
687 --
688 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
689 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
690 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
691 l_sequence_number number;
692 l_next_sequence number;
693 l_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
694 l_next_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
695 l_step_id per_spinal_point_placements_f.step_id%TYPE;
696 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
697 l_assignment_id per_spinal_point_placements_f.assignment_id%TYPE;
698 l_business_group_id per_spinal_point_placements_f.business_group_id%TYPE;
699 l_datetrack_mode varchar2(30);
700 l_temp_ovn number := p_object_version_number;
701 l_effective_end_date_temp per_spinal_point_placements_f.effective_end_date%TYPE
702 := trunc(p_effective_end_date);
703 l_effective_start_date_temp per_spinal_point_placements_f.effective_start_date%TYPE
704 := trunc(p_effective_start_date);
705 l_gsp_post_process_warning varchar2(30);
706 --
707
708 --
709 -- Declare out parameters
710 l_effective_date date;
711 --
712 l_proc varchar2(72) := g_package||'update_spinal_point_placement';
713 --
714 begin
715 hr_utility.set_location('Entering:'|| l_proc, 10);
716 --
717 -- Issue a savepoint
718 --
719 savepoint update_spinal_point_placement;
720 --
721 -- Check that all required parameters are not null
722 --
723 hr_utility.set_location('Entering mandatory arg check', 20);
724 --
725 hr_api.mandatory_arg_error
726 (p_api_name => l_proc,
727 p_argument => 'placement_id',
728 p_argument_value => p_placement_id);
729 --
730 hr_api.mandatory_arg_error
731 (p_api_name => l_proc,
732 p_argument => 'effective_date',
733 p_argument_value => p_effective_date);
734 --
735 hr_api.mandatory_arg_error
736 (p_api_name => l_proc,
737 p_argument => 'object_version_number',
738 p_argument_value => p_object_version_number);
739 --
740 l_effective_date := trunc(p_effective_date);
741 l_effective_start_date := p_effective_start_date;
742 l_effective_end_date := p_effective_end_date;
743 l_object_version_number := p_object_version_number;
744 l_datetrack_mode := p_datetrack_mode;
745 --
746 hr_utility.set_location('Entering: call - update_spinal_point_placement_b ', 30);
747 hr_utility.set_location('Increment Number:'||p_increment_number,1);
748 hr_utility.set_location('Effective_date:'||l_effective_date,9);
749 hr_utility.set_location('EFFECTIVE_END_DATE : '||p_effective_end_date,9);
750 --
751
752 --
753 -- Get the non passed parameters
754 --
755
756 hr_utility.set_location(p_placement_id,33);
757 --
758 hr_utility.set_location(l_proc,20);
759 select parent_spine_id, business_group_id, assignment_id
760 into l_parent_spine_id, l_business_group_id, l_assignment_id
761 from per_spinal_point_placements_f
762 where placement_id = p_placement_id
763 and l_effective_date between effective_start_date
764 and effective_end_date;
765 --
766 -- Call the user hook before update process
767 --
768 hr_utility.set_location(l_assignment_id,22);
769
770 begin
771
772 hr_sp_placement_api_bk2.update_sp_placement_b
773 (p_effective_date => l_effective_date
774 ,p_datetrack_mode => p_datetrack_mode
775 ,p_placement_id => p_placement_id
776 ,p_object_version_number => p_object_version_number
777 ,p_business_group_id => l_business_group_id
778 ,p_assignment_id => l_assignment_id
779 ,p_step_id => p_step_id
780 ,p_auto_increment_flag => p_auto_increment_flag
781 ,p_parent_spine_id => l_parent_spine_id
782 ,p_reason => p_reason
783 ,p_request_id => p_request_id
784 ,p_program_application_id => p_program_application_id
785 ,p_program_id => p_program_id
786 ,p_program_update_date => p_program_update_date
787 ,p_increment_number => p_increment_number
788 ,p_information1 => p_information1
789 ,p_information2 => p_information2
790 ,p_information3 => p_information3
791 ,p_information4 => p_information4
792 ,p_information5 => p_information5
793 ,p_information6 => p_information6
794 ,p_information7 => p_information7
795 ,p_information8 => p_information8
796 ,p_information9 => p_information9
797 ,p_information10 => p_information10
798 ,p_information11 => p_information11
799 ,p_information12 => p_information12
800 ,p_information13 => p_information13
801 ,p_information14 => p_information14
802 ,p_information15 => p_information15
803 ,p_information16 => p_information16
804 ,p_information17 => p_information17
805 ,p_information18 => p_information18
806 ,p_information19 => p_information19
807 ,p_information20 => p_information20
808 ,p_information21 => p_information21
809 ,p_information22 => p_information22
810 ,p_information23 => p_information23
811 ,p_information24 => p_information24
812 ,p_information25 => p_information25
813 ,p_information26 => p_information26
814 ,p_information27 => p_information27
815 ,p_information28 => p_information28
816 ,p_information29 => p_information29
817 ,p_information30 => p_information30
818 ,p_information_category => p_information_category
819 -- ,p_effective_start_date => l_effective_start_date
820 -- ,p_effetcive_end_date => l_effective_end_date
821 );
822 exception
823 when hr_api.cannot_find_prog_unit then
824 hr_api.cannot_find_prog_unit_error
825 (p_module_name => 'UPDATE_SPINAL_POINT_PLACEMENT'
826 ,p_hook_type => 'BP'
827 );
828
829 end;
830 hr_utility.set_location(l_proc,21);
831 hr_utility.set_location('Entering: call - per_spp_upd.upd ', 40);
832 --
833
834 per_spp_upd.upd
835 (p_effective_date => l_effective_date
836 ,p_datetrack_mode => l_datetrack_mode
837 ,p_placement_id => p_placement_id
838 ,p_object_version_number => l_object_version_number
839 ,p_step_id => p_step_id
840 ,p_auto_increment_flag => p_auto_increment_flag
841 ,p_assignment_id => l_assignment_id
842 ,p_reason => p_reason
843 ,p_request_id => p_request_id
844 ,p_program_application_id => p_program_application_id
845 ,p_program_id => p_program_id
846 ,p_program_update_date => p_program_update_date
847 ,p_increment_number => p_increment_number
848 ,p_effective_start_date => l_effective_start_date
849 ,p_effective_end_date => l_effective_end_date
850 ,p_information1 => p_information1
851 ,p_information2 => p_information2
852 ,p_information3 => p_information3
853 ,p_information4 => p_information4
854 ,p_information5 => p_information5
855 ,p_information6 => p_information6
856 ,p_information7 => p_information7
857 ,p_information8 => p_information8
858 ,p_information9 => p_information9
859 ,p_information10 => p_information10
860 ,p_information11 => p_information11
861 ,p_information12 => p_information12
862 ,p_information13 => p_information13
863 ,p_information14 => p_information14
864 ,p_information15 => p_information15
865 ,p_information16 => p_information16
866 ,p_information17 => p_information17
867 ,p_information18 => p_information18
868 ,p_information19 => p_information19
869 ,p_information20 => p_information20
870 ,p_information21 => p_information21
871 ,p_information22 => p_information22
872 ,p_information23 => p_information23
873 ,p_information24 => p_information24
874 ,p_information25 => p_information25
875 ,p_information26 => p_information26
876 ,p_information27 => p_information27
877 ,p_information28 => p_information28
878 ,p_information29 => p_information29
879 ,p_information30 => p_information30
880 ,p_information_category => p_information_category
881 );
882
883 --
884 hr_utility.set_location('l_placement_id'||l_placement_id,52);
885 --
886 p_effective_start_date := l_effective_start_date;
887 p_effective_end_date := l_effective_end_date;
888 p_object_version_number := l_object_version_number;
889 -- p_datetrack_mode := l_datetrack_mode;
890 --
891 hr_utility.set_location('p_effective_start_date'||p_effective_start_date,55);
892 hr_utility.set_location('Entering: call - update_spinal_point_placement_a', 50);
893 --
894
895 begin
896 --
897 hr_sp_placement_api_bk2.update_sp_placement_a
898 (p_effective_date => l_effective_date
899 ,p_datetrack_mode => p_datetrack_mode
900 ,p_placement_id => p_placement_id
901 ,p_object_version_number => p_object_version_number
902 ,p_business_group_id => l_business_group_id
903 ,p_assignment_id => l_assignment_id
904 ,p_step_id => p_step_id
905 ,p_auto_increment_flag => p_auto_increment_flag
906 ,p_parent_spine_id => l_parent_spine_id
907 ,p_reason => p_reason
908 ,p_request_id => p_request_id
909 ,p_program_application_id => p_program_application_id
910 ,p_program_id => p_program_id
911 ,p_program_update_date => p_program_update_date
912 ,p_increment_number => p_increment_number
913 ,p_effective_start_date => l_effective_start_date
914 ,p_effective_end_date => l_effective_end_date
915 ,p_information1 => p_information1
916 ,p_information2 => p_information2
917 ,p_information3 => p_information3
918 ,p_information4 => p_information4
919 ,p_information5 => p_information5
920 ,p_information6 => p_information6
921 ,p_information7 => p_information7
922 ,p_information8 => p_information8
923 ,p_information9 => p_information9
924 ,p_information10 => p_information10
925 ,p_information11 => p_information11
926 ,p_information12 => p_information12
927 ,p_information13 => p_information13
928 ,p_information14 => p_information14
929 ,p_information15 => p_information15
930 ,p_information16 => p_information16
931 ,p_information17 => p_information17
932 ,p_information18 => p_information18
933 ,p_information19 => p_information19
934 ,p_information20 => p_information20
935 ,p_information21 => p_information21
936 ,p_information22 => p_information22
937 ,p_information23 => p_information23
938 ,p_information24 => p_information24
939 ,p_information25 => p_information25
940 ,p_information26 => p_information26
941 ,p_information27 => p_information27
942 ,p_information28 => p_information28
943 ,p_information29 => p_information29
944 ,p_information30 => p_information30
945 ,p_information_category => p_information_category
946 );
947
948 exception
949 when hr_api.cannot_find_prog_unit then
950 hr_api.cannot_find_prog_unit_error
951 (p_module_name => 'UPDATE_SPINAL_POINT_PLACEMENT'
952 ,p_hook_type => 'AP'
953 );
954 end;
955
956 hr_utility.set_location(' Leaving:'||l_proc, 60);
957
958 --
959 -- call pqh post process procedure -- bug 2999562
960 --
961 pqh_gsp_post_process.call_pp_from_assignments(
962 p_effective_date => l_effective_date
963 ,p_assignment_id => l_assignment_id
964 ,p_date_track_mode => p_datetrack_mode
965 ,p_warning_mesg => l_gsp_post_process_warning
966 );
967
968 hr_utility.set_location(' Leaving:'||l_proc, 65);
969
970 --
971 -- When in validation only mode raise the Validate_Enabled exception
972 --
973 if p_validate then
974 raise hr_api.validate_enabled;
975 end if;
976 --
977 --
978 p_gsp_post_process_warning := l_gsp_post_process_warning;
979 --
980 --
981 hr_utility.set_location(' Leaving:'||l_proc, 70);
982 exception
983 when hr_api.validate_enabled then
984 --
985 -- As the Validate_Enabled exception has been raised
986 -- we must rollback to the savepoint
987 --
988 rollback to update_spinal_point_placement;
989 --
990 -- Only set output warning arguments
991 -- (Any key or derived arguments must be set to null
992 -- when validation only mode is being used.)
993 --
994 p_object_version_number := l_object_version_number;
995 --
996 hr_utility.set_location(' Leaving:'||l_proc, 80);
997 when others then
998 --
999 -- A validation or unexpected error has occured
1000 --
1001 p_object_version_number := l_temp_ovn;
1002 p_effective_start_date := l_effective_start_date_temp;
1003 p_effective_end_date := l_effective_end_date_temp;
1004 p_gsp_post_process_warning := l_gsp_post_process_warning;
1005 rollback to update_spinal_point_placement;
1006 hr_utility.set_location(' Leaving:'||l_proc, 90);
1007 raise;
1008 end update_spp;
1009 --
1010 -- ----------------------------------------------------------------------------
1011 -- |----------------------< delete_spinal_point_placement >-------------------|
1012 -- ----------------------------------------------------------------------------
1013 --
1014 procedure delete_spp
1015 (p_validate in boolean default false
1016 ,p_effective_date in date
1017 ,p_datetrack_mode in varchar2 default hr_api.g_delete
1018 ,p_placement_id in number
1019 ,p_object_version_number in out nocopy number
1020 ,p_effective_start_date out nocopy date
1021 ,p_effective_end_date out nocopy date
1022 ) is
1023 --
1024 -- Declare cursors and local variables
1025 --
1026 l_proc varchar2(72) := (g_package||'delete_spinal_point_placement');
1027 l_effective_date date := trunc(p_effective_date);
1028 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
1029 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
1030 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
1031 l_temp_ovn number := p_object_version_number;
1032 --
1033 begin
1034 --
1035 hr_api.mandatory_arg_error
1036 (p_api_name => l_proc,
1037 p_argument => 'placement_id',
1038 p_argument_value => p_placement_id);
1039 --
1040 hr_api.mandatory_arg_error
1041 (p_api_name => l_proc,
1042 p_argument => 'effective_date',
1043 p_argument_value => p_effective_date);
1044 --
1045 hr_utility.set_location('Entering:'|| l_proc, 10);
1046 --
1047 -- Issue a savepoint if operating in validation only mode
1048 --
1049 savepoint delete_spinal_point_placement;
1050 --
1051 hr_utility.set_location(l_proc, 20);
1052 --
1053 l_object_version_number := p_object_version_number;
1054 -- Process Logic
1055 --
1056 --
1057
1058 begin
1059 --
1060 -- Start of API User Hook for the before hook of delete_spinal_point_placement
1061 --
1062 hr_sp_placement_api_bk3.delete_sp_placement_b
1063 (p_effective_date => l_effective_date
1064 ,p_datetrack_mode => p_datetrack_mode
1065 ,p_placement_id => p_placement_id
1066 ,p_object_version_number => p_object_version_number
1067 );
1068 exception
1069 when hr_api.cannot_find_prog_unit then
1070 hr_api.cannot_find_prog_unit_error
1071 (p_module_name => 'delete_spinal_point_placement'
1072 ,p_hook_type => 'BP'
1073 );
1074 --
1075 -- End of API User Hook for the before hook of delete_spinal_point_placement
1076 --
1077 end;
1078
1079 --
1080 per_spp_del.del
1081 (p_effective_date => l_effective_date
1082 ,p_datetrack_mode => p_datetrack_mode
1083 ,p_placement_id => p_placement_id
1084 ,p_object_version_number => p_object_version_number
1085 ,p_effective_start_date => l_effective_start_date
1086 ,p_effective_end_date => l_effective_end_date
1087 );
1088 --
1089
1090 begin
1091 --
1092 -- Start of API User Hook for the after hook of delete_spinal_point_placement
1093 --
1094 hr_sp_placement_api_bk3.delete_sp_placement_a
1095 (p_effective_date => l_effective_date
1096 ,p_datetrack_mode => p_datetrack_mode
1097 ,p_placement_id => p_placement_id
1098 ,p_object_version_number => p_object_version_number
1099 ,p_effective_start_date => l_effective_start_date
1100 ,p_effective_end_date => l_effective_end_date
1101 );
1102 exception
1103 when hr_api.cannot_find_prog_unit then
1104 hr_api.cannot_find_prog_unit_error
1105 (p_module_name => 'delete_spinal_point_placement'
1106 ,p_hook_type => 'AP'
1107 );
1108 --
1109 -- End of API User Hook for the after hook of delete_spinal_point_placement
1110 --
1111 end;
1112
1113 --
1114 hr_utility.set_location(l_proc, 60);
1115 --
1116 -- When in validation only mode raise the Validate_Enabled exception
1117 --
1118 if p_validate then
1119 raise hr_api.validate_enabled;
1120 end if;
1121 --
1122 hr_utility.set_location(' Leaving:'||l_proc, 70);
1123 --
1124 exception
1125 --
1126 when hr_api.validate_enabled then
1127 --
1128 -- As the Validate_Enabled exception has been raised
1129 -- we must rollback to the savepoint
1130 --
1131 ROLLBACK TO delete_spinal_point_placement;
1132 --
1133 -- Only set output warning arguments
1134 -- (Any key or derived arguments must be set to null
1135 -- when validation only mode is being used.)
1136 --
1137 --
1138 when others then
1139 --
1140 -- A validation or unexpected error has occured
1141 --
1142 p_object_version_number := l_temp_ovn;
1143 p_effective_start_date := null;
1144 p_effective_end_date := null;
1145 ROLLBACK TO delete_spinal_point_placement;
1146 raise;
1147 --
1148 end delete_spp;
1149 --
1150 --
1151 end hr_sp_placement_api;