[Home] [Help]
PACKAGE BODY: APPS.PER_SP_PLAN_API
Source
1 Package Body per_sp_plan_api as
2 /* $Header: pesphapi.pkb 120.2 2011/03/30 06:07:46 vkodedal noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' per_sp_plan_api.';
7 --
11 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< create_sp_plan >--------------------------|
10 -- ----------------------------------------------------------------------------
12 procedure create_sp_plan
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_plan_type in varchar2
16 ,p_plan_name in varchar2
17 ,p_successee_id in number
18 ,p_business_group_id in number
19 ,p_status in varchar2
20 ,p_plan_owner in number
21 ,p_start_date in date
22 ,p_end_date in date default null
23 ,p_description in varchar2 default null
24 ,p_filled_from_plan in varchar2 default null
25 ,p_filled_by in number default null
26 ,p_filled_on in date default null
27 ,p_next_review_date in date default null
28 ,p_criteria_set_id in number default null
29 ,p_attribute_category in varchar2 default null
30 ,p_attribute1 in varchar2 default null
31 ,p_attribute2 in varchar2 default null
32 ,p_attribute3 in varchar2 default null
33 ,p_attribute4 in varchar2 default null
34 ,p_attribute5 in varchar2 default null
35 ,p_attribute6 in varchar2 default null
36 ,p_attribute7 in varchar2 default null
37 ,p_attribute8 in varchar2 default null
38 ,p_attribute9 in varchar2 default null
39 ,p_attribute10 in varchar2 default null
40 ,p_attribute11 in varchar2 default null
41 ,p_attribute12 in varchar2 default null
42 ,p_attribute13 in varchar2 default null
43 ,p_attribute14 in varchar2 default null
44 ,p_attribute15 in varchar2 default null
45 ,p_attribute16 in varchar2 default null
46 ,p_attribute17 in varchar2 default null
47 ,p_attribute18 in varchar2 default null
48 ,p_attribute19 in varchar2 default null
49 ,p_attribute20 in varchar2 default null
50 ,p_plan_id out nocopy number
51 ,p_object_version_number out nocopy number) is
52 --
53 -- Declare cursors and local variables
54 --
55 l_effective_date date;
56 l_proc varchar2(72) := g_package||'create_sp_plan';
57 l_plan_id per_sp_plan.plan_id%TYPE;
58 l_object_version_number per_sp_plan.object_version_number%TYPE;
59 begin
60 hr_utility.set_location('Entering:'|| l_proc, 10);
61 --
62 -- Issue a savepoint
63 --
64 savepoint create_sp_plan;
65
66 --
67 -- Truncate the time portion from all IN date parameters
68 --
69 l_effective_date := trunc(p_effective_date);
70 hr_utility.set_location(l_proc, 20);
71
72 --
73 -- Call Before Process User Hook
74 --
75 begin
76 per_sp_plan_bk1.create_sp_plan_b
77 (p_effective_date => l_effective_date
78 ,p_plan_type => p_plan_type
79 ,p_plan_name => p_plan_name
80 ,p_successee_id => p_successee_id
81 ,p_business_group_id => p_business_group_id
82 ,p_status => p_status
83 ,p_plan_owner => p_plan_owner
84 ,p_start_date => p_start_date
85 ,p_end_date => p_end_date
86 ,p_description => p_description
87 ,p_filled_from_plan => p_filled_from_plan
88 ,p_filled_by => p_filled_by
89 ,p_filled_on => p_filled_on
90 ,p_next_review_date => p_next_review_date
91 ,p_criteria_set_id => p_criteria_set_id
92 ,p_attribute_category => p_attribute_category
93 ,p_attribute1 => p_attribute1
94 ,p_attribute2 => p_attribute2
95 ,p_attribute3 => p_attribute3
96 ,p_attribute4 => p_attribute4
97 ,p_attribute5 => p_attribute5
98 ,p_attribute6 => p_attribute6
99 ,p_attribute7 => p_attribute7
100 ,p_attribute8 => p_attribute8
101 ,p_attribute9 => p_attribute9
102 ,p_attribute10 => p_attribute10
103 ,p_attribute11 => p_attribute11
104 ,p_attribute12 => p_attribute12
105 ,p_attribute13 => p_attribute13
106 ,p_attribute14 => p_attribute14
107 ,p_attribute15 => p_attribute15
108 ,p_attribute16 => p_attribute16
109 ,p_attribute17 => p_attribute17
110 ,p_attribute18 => p_attribute18
111 ,p_attribute19 => p_attribute19
112 ,p_attribute20 => p_attribute20 );
113 exception
114 when hr_api.cannot_find_prog_unit then
115 hr_api.cannot_find_prog_unit_error
116 (p_module_name => 'create_sp_plan_b'
117 ,p_hook_type => 'BP'
118 );
119 end;
120 --
121 -- Validation in addition to Row Handlers
122 --
123
124
125 hr_utility.set_location(l_proc, 30);
126
127 --
128 -- Process Logic
129 --
130
131 hr_utility.set_location(l_proc, 40);
132 per_sph_ins.ins(p_effective_date => l_effective_date
133 ,p_plan_id => l_plan_id
134 ,p_plan_type => p_plan_type
135 ,p_plan_name => p_plan_name
136 ,p_successee_id => p_successee_id
137 ,p_business_group_id => p_business_group_id
138 ,p_status => p_status
139 ,p_plan_owner => p_plan_owner
140 ,p_start_date => p_start_date
141 ,p_end_date => p_end_date
145 ,p_filled_on => p_filled_on
142 ,p_description => p_description
143 ,p_filled_from_plan => p_filled_from_plan
144 ,p_filled_by => p_filled_by
146 ,p_next_review_date => p_next_review_date
147 ,p_criteria_set_id => p_criteria_set_id
148 ,p_attribute_category => p_attribute_category
149 ,p_attribute1 => p_attribute1
150 ,p_attribute2 => p_attribute2
151 ,p_attribute3 => p_attribute3
152 ,p_attribute4 => p_attribute4
153 ,p_attribute5 => p_attribute5
154 ,p_attribute6 => p_attribute6
155 ,p_attribute7 => p_attribute7
156 ,p_attribute8 => p_attribute8
157 ,p_attribute9 => p_attribute9
158 ,p_attribute10 => p_attribute10
159 ,p_attribute11 => p_attribute11
160 ,p_attribute12 => p_attribute12
161 ,p_attribute13 => p_attribute13
162 ,p_attribute14 => p_attribute14
163 ,p_attribute15 => p_attribute15
164 ,p_attribute16 => p_attribute16
165 ,p_attribute17 => p_attribute17
166 ,p_attribute18 => p_attribute18
167 ,p_attribute19 => p_attribute19
168 ,p_attribute20 => p_attribute20
169 ,p_object_version_number => l_object_version_number);
170
171 --
172 -- Call After Process User Hook
173 --
174 begin
175 per_sp_plan_bk1.create_sp_plan_a
176 (p_effective_date => l_effective_date
177 ,p_plan_id => l_plan_id
178 ,p_plan_type => p_plan_type
179 ,p_plan_name => p_plan_name
180 ,p_successee_id => p_successee_id
181 ,p_business_group_id => p_business_group_id
182 ,p_status => p_status
183 ,p_plan_owner => p_plan_owner
184 ,p_start_date => p_start_date
185 ,p_end_date => p_end_date
186 ,p_description => p_description
187 ,p_filled_from_plan => p_filled_from_plan
188 ,p_filled_by => p_filled_by
189 ,p_filled_on => p_filled_on
190 ,p_next_review_date => p_next_review_date
191 ,p_criteria_set_id => p_criteria_set_id
192 ,p_attribute_category => p_attribute_category
193 ,p_attribute1 => p_attribute1
194 ,p_attribute2 => p_attribute2
195 ,p_attribute3 => p_attribute3
196 ,p_attribute4 => p_attribute4
197 ,p_attribute5 => p_attribute5
198 ,p_attribute6 => p_attribute6
199 ,p_attribute7 => p_attribute7
200 ,p_attribute8 => p_attribute8
201 ,p_attribute9 => p_attribute9
202 ,p_attribute10 => p_attribute10
203 ,p_attribute11 => p_attribute11
204 ,p_attribute12 => p_attribute12
205 ,p_attribute13 => p_attribute13
206 ,p_attribute14 => p_attribute14
207 ,p_attribute15 => p_attribute15
208 ,p_attribute16 => p_attribute16
209 ,p_attribute17 => p_attribute17
210 ,p_attribute18 => p_attribute18
211 ,p_attribute19 => p_attribute19
212 ,p_attribute20 => p_attribute20 );
216 (p_module_name => 'create_sp_plan_b'
213 exception
214 when hr_api.cannot_find_prog_unit then
215 hr_api.cannot_find_prog_unit_error
217 ,p_hook_type => 'AP'
218 );
219 end;
220 hr_utility.set_location(l_proc, 50);
221
222 --
223 -- When in validation only mode raise the Validate_Enabled exception
224 --
225 if p_validate then
226 raise hr_api.validate_enabled;
227 end if;
228 --
229 -- Set all IN OUT and OUT parameters with out values
230 --
231 p_plan_id := l_plan_id;
232 p_object_version_number := l_object_version_number;
233 --
234 hr_utility.set_location(' Leaving:'||l_proc, 70);
235 exception
236 when hr_api.validate_enabled then
237 --
238 -- As the Validate_Enabled exception has been raised
239 -- we must rollback to the savepoint
240 --
241 rollback to create_sp_plan;
242 --
243 -- Reset IN OUT parameters and set OUT parameters
244 -- (Any key or derived arguments must be set to null
245 -- when validation only mode is being used.)
246 --
247 p_plan_id := null;
248 p_object_version_number := null;
249 hr_utility.set_location(' Leaving:'||l_proc, 80);
250 when others then
251 --
252 -- A validation or unexpected error has occured
253 --
254 rollback to create_sp_plan;
255 --
256 -- Reset IN OUT parameters and set all
257 -- OUT parameters, including warnings, to null
258 --
259 p_plan_id := null;
260 p_object_version_number := null;
261 hr_utility.set_location(' Leaving:'||l_proc, 90);
262 raise;
263 end create_sp_plan;
264 --
265 --
266 -- ----------------------------------------------------------------------------
267 -- |--------------------------< update_sp_plan >--------------------------|
268 -- ----------------------------------------------------------------------------
269 --
270 procedure update_sp_plan
271 (p_validate in boolean default false
272 ,p_effective_date in date
273 ,p_plan_id in number
274 ,p_object_version_number in out nocopy number
275 ,p_plan_type in varchar2 default hr_api.g_varchar2
276 ,p_plan_name in varchar2 default hr_api.g_varchar2
277 ,p_successee_id in number default hr_api.g_number
278 ,p_business_group_id in number default hr_api.g_number
279 ,p_status in varchar2 default hr_api.g_varchar2
280 ,p_plan_owner in number default hr_api.g_number
281 ,p_start_date in date default hr_api.g_date
282 ,p_end_date in date default hr_api.g_date
283 ,p_description in varchar2 default hr_api.g_varchar2
284 ,p_filled_from_plan in varchar2 default hr_api.g_varchar2
285 ,p_filled_by in number default hr_api.g_number
286 ,p_filled_on in date default hr_api.g_date
287 ,p_next_review_date in date default hr_api.g_date
288 ,p_criteria_set_id in number default hr_api.g_number
289 ,p_attribute_category in varchar2 default hr_api.g_varchar2
290 ,p_attribute1 in varchar2 default hr_api.g_varchar2
291 ,p_attribute2 in varchar2 default hr_api.g_varchar2
292 ,p_attribute3 in varchar2 default hr_api.g_varchar2
293 ,p_attribute4 in varchar2 default hr_api.g_varchar2
294 ,p_attribute5 in varchar2 default hr_api.g_varchar2
295 ,p_attribute6 in varchar2 default hr_api.g_varchar2
296 ,p_attribute7 in varchar2 default hr_api.g_varchar2
297 ,p_attribute8 in varchar2 default hr_api.g_varchar2
298 ,p_attribute9 in varchar2 default hr_api.g_varchar2
299 ,p_attribute10 in varchar2 default hr_api.g_varchar2
300 ,p_attribute11 in varchar2 default hr_api.g_varchar2
301 ,p_attribute12 in varchar2 default hr_api.g_varchar2
302 ,p_attribute13 in varchar2 default hr_api.g_varchar2
303 ,p_attribute14 in varchar2 default hr_api.g_varchar2
304 ,p_attribute15 in varchar2 default hr_api.g_varchar2
305 ,p_attribute16 in varchar2 default hr_api.g_varchar2
306 ,p_attribute17 in varchar2 default hr_api.g_varchar2
307 ,p_attribute18 in varchar2 default hr_api.g_varchar2
308 ,p_attribute19 in varchar2 default hr_api.g_varchar2
309 ,p_attribute20 in varchar2 default hr_api.g_varchar2) is
310 --
311 -- Declare cursors and local variables
312 --
313 l_effective_date date;
314 l_proc varchar2(72) := g_package||'update_sp_plan';
315 l_object_version_number per_sp_plan.object_version_number%TYPE;
316 begin
317 hr_utility.set_location('Entering:'|| l_proc, 10);
318 --
319 -- Issue a savepoint
320 --
321 savepoint update_sp_plan;
322 --
323 --
324 l_object_version_number := p_object_version_number;
325 --
326 -- Truncate the time portion from all IN date parameters
327 --
328 l_effective_date := trunc(p_effective_date);
329 hr_utility.set_location(l_proc, 20);
330
331 --
332 -- Call Before Process User Hook
333 --
334 begin
335 per_sp_plan_bk2.update_sp_plan_b
339 ,p_plan_name => p_plan_name
336 (p_effective_date => l_effective_date
337 ,p_plan_id => p_plan_id
338 ,p_plan_type => p_plan_type
340 ,p_successee_id => p_successee_id
341 ,p_business_group_id => p_business_group_id
342 ,p_status => p_status
343 ,p_plan_owner => p_plan_owner
344 ,p_start_date => p_start_date
345 ,p_end_date => p_end_date
346 ,p_description => p_description
347 ,p_filled_from_plan => p_filled_from_plan
348 ,p_filled_by => p_filled_by
349 ,p_filled_on => p_filled_on
350 ,p_next_review_date => p_next_review_date
351 ,p_criteria_set_id => p_criteria_set_id
352 ,p_attribute_category => p_attribute_category
353 ,p_attribute1 => p_attribute1
354 ,p_attribute2 => p_attribute2
355 ,p_attribute3 => p_attribute3
356 ,p_attribute4 => p_attribute4
357 ,p_attribute5 => p_attribute5
358 ,p_attribute6 => p_attribute6
359 ,p_attribute7 => p_attribute7
360 ,p_attribute8 => p_attribute8
361 ,p_attribute9 => p_attribute9
362 ,p_attribute10 => p_attribute10
363 ,p_attribute11 => p_attribute11
364 ,p_attribute12 => p_attribute12
365 ,p_attribute13 => p_attribute13
366 ,p_attribute14 => p_attribute14
367 ,p_attribute15 => p_attribute15
368 ,p_attribute16 => p_attribute16
369 ,p_attribute17 => p_attribute17
370 ,p_attribute18 => p_attribute18
371 ,p_attribute19 => p_attribute19
372 ,p_attribute20 => p_attribute20 );
373 exception
374 when hr_api.cannot_find_prog_unit then
375 hr_api.cannot_find_prog_unit_error
376 (p_module_name => 'update_sp_plan_b'
377 ,p_hook_type => 'BP'
378 );
379 end;
380 --
381 -- Validation in addition to Row Handlers
382 --
383
384
385 hr_utility.set_location(l_proc, 30);
386
387 --
388 -- Process Logic
389 --
390
391 hr_utility.set_location(l_proc, 40);
392 per_sph_upd.upd(p_effective_date => l_effective_date
393 ,p_plan_id => p_plan_id
394 ,p_plan_type => p_plan_type
395 ,p_plan_name => p_plan_name
396 ,p_successee_id => p_successee_id
397 ,p_business_group_id => p_business_group_id
398 ,p_status => p_status
399 ,p_plan_owner => p_plan_owner
400 ,p_start_date => p_start_date
401 ,p_end_date => p_end_date
402 ,p_description => p_description
403 ,p_filled_from_plan => p_filled_from_plan
404 ,p_filled_by => p_filled_by
405 ,p_filled_on => p_filled_on
406 ,p_next_review_date => p_next_review_date
407 ,p_criteria_set_id => p_criteria_set_id
408 ,p_attribute_category => p_attribute_category
409 ,p_attribute1 => p_attribute1
410 ,p_attribute2 => p_attribute2
411 ,p_attribute3 => p_attribute3
412 ,p_attribute4 => p_attribute4
413 ,p_attribute5 => p_attribute5
414 ,p_attribute6 => p_attribute6
415 ,p_attribute7 => p_attribute7
416 ,p_attribute8 => p_attribute8
417 ,p_attribute9 => p_attribute9
418 ,p_attribute10 => p_attribute10
419 ,p_attribute11 => p_attribute11
420 ,p_attribute12 => p_attribute12
421 ,p_attribute13 => p_attribute13
422 ,p_attribute14 => p_attribute14
423 ,p_attribute15 => p_attribute15
424 ,p_attribute16 => p_attribute16
425 ,p_attribute17 => p_attribute17
426 ,p_attribute18 => p_attribute18
427 ,p_attribute19 => p_attribute19
428 ,p_attribute20 => p_attribute20
429 ,p_object_version_number => l_object_version_number);
430
431 --
432 -- Call After Process User Hook
433 --
434 begin
435 per_sp_plan_bk2.update_sp_plan_a
436 (p_effective_date => l_effective_date
437 ,p_plan_id => p_plan_id
438 ,p_plan_type => p_plan_type
439 ,p_plan_name => p_plan_name
440 ,p_successee_id => p_successee_id
441 ,p_business_group_id => p_business_group_id
442 ,p_status => p_status
443 ,p_plan_owner => p_plan_owner
444 ,p_start_date => p_start_date
445 ,p_end_date => p_end_date
446 ,p_description => p_description
447 ,p_filled_from_plan => p_filled_from_plan
448 ,p_filled_by => p_filled_by
449 ,p_filled_on => p_filled_on
450 ,p_next_review_date => p_next_review_date
451 ,p_criteria_set_id => p_criteria_set_id
452 ,p_attribute_category => p_attribute_category
453 ,p_attribute1 => p_attribute1
454 ,p_attribute2 => p_attribute2
455 ,p_attribute3 => p_attribute3
456 ,p_attribute4 => p_attribute4
457 ,p_attribute5 => p_attribute5
458 ,p_attribute6 => p_attribute6
459 ,p_attribute7 => p_attribute7
460 ,p_attribute8 => p_attribute8
461 ,p_attribute9 => p_attribute9
462 ,p_attribute10 => p_attribute10
463 ,p_attribute11 => p_attribute11
464 ,p_attribute12 => p_attribute12
465 ,p_attribute13 => p_attribute13
466 ,p_attribute14 => p_attribute14
467 ,p_attribute15 => p_attribute15
468 ,p_attribute16 => p_attribute16
469 ,p_attribute17 => p_attribute17
470 ,p_attribute18 => p_attribute18
471 ,p_attribute19 => p_attribute19
472 ,p_attribute20 => p_attribute20 );
473 exception
474 when hr_api.cannot_find_prog_unit then
475 hr_api.cannot_find_prog_unit_error
476 (p_module_name => 'update_sp_plan_b'
477 ,p_hook_type => 'AP'
478 );
479 end;
480 hr_utility.set_location(l_proc, 50);
481
482 --
483 -- When in validation only mode raise the Validate_Enabled exception
484 --
485 if p_validate then
486 raise hr_api.validate_enabled;
487 end if;
488 --
489 -- Set all IN OUT and OUT parameters with out values
490 --
491 p_object_version_number := l_object_version_number;
492 --
493 hr_utility.set_location(' Leaving:'||l_proc, 70);
494 exception
495 when hr_api.validate_enabled then
496 --
497 -- As the Validate_Enabled exception has been raised
498 -- we must rollback to the savepoint
499 --
500 rollback to update_sp_plan;
501 --
502 -- Reset IN OUT parameters and set OUT parameters
503 -- (Any key or derived arguments must be set to null
504 -- when validation only mode is being used.)
505 --
506 p_object_version_number := l_object_version_number;
507 hr_utility.set_location(' Leaving:'||l_proc, 80);
508 when others then
509 --
510 -- A validation or unexpected error has occured
511 --
512 rollback to update_sp_plan;
513 --
514 -- Reset IN OUT parameters and set all
515 -- OUT parameters, including warnings, to null
516 --
517 p_object_version_number := l_object_version_number;
518 hr_utility.set_location(' Leaving:'||l_proc, 90);
519 raise;
520 end update_sp_plan;
521 --
522 --
523 procedure delete_sp_plan
524 (p_validate in boolean default false
525 ,p_plan_id in number
526 ,p_object_version_number in number) is
527 --
528 -- Declare cursors and local variables
529 --
530 l_proc varchar2(72) := g_package||'delete_sp_plan';
531 begin
532 hr_utility.set_location('Entering:'|| l_proc, 5);
533 --
534 -- Issue a savepoint.
535 --
536 savepoint delete_sp_plan;
537 hr_utility.set_location(l_proc, 10);
538 per_sp_plan_bk3.delete_sp_plan_b(p_plan_id => p_plan_id);
539 hr_utility.set_location(l_proc, 20);
540 per_sph_del.del(p_plan_id => p_plan_id
541 ,p_object_version_number => p_object_version_number);
542 hr_utility.set_location(l_proc, 30);
543 per_sp_plan_bk3.delete_sp_plan_a(p_plan_id => p_plan_id);
544 hr_utility.set_location(l_proc, 50);
545 --
546 -- When in validation only mode raise the Validate_Enabled exception
547 --
548 if p_validate then
549 raise hr_api.validate_enabled;
550 end if;
551 --
552 -- Set all IN OUT and OUT parameters with out values
553 --
554 --
555 hr_utility.set_location(' Leaving:'||l_proc, 70);
556 exception
557 when hr_api.validate_enabled then
558 --
559 -- As the Validate_Enabled exception has been raised
560 -- we must rollback to the savepoint
561 --
562 rollback to delete_sp_plan;
563 --
564 -- Reset IN OUT parameters and set OUT parameters
565 -- (Any key or derived arguments must be set to null
566 -- when validation only mode is being used.)
567 --
568 hr_utility.set_location(' Leaving:'||l_proc, 80);
569 when others then
570 --
571 -- A validation or unexpected error has occured
572 --
573 rollback to delete_sp_plan;
574 --
575 -- Reset IN OUT parameters and set all
576 -- OUT parameters, including warnings, to null
577 --
578 hr_utility.set_location(' Leaving:'||l_proc, 90);
579 raise;
580
581 end delete_sp_plan;
582 --
583 end per_sp_plan_api;