[Home] [Help]
PACKAGE BODY: APPS.HR_PARTICIPANTS_API
Source
4 -- Package Variables
1 Package Body hr_participants_api as
2 /* $Header: peparapi.pkb 120.1 2007/06/20 07:48:12 rapandi ship $ */
3 --
5 --
6 g_package varchar2(33) := ' hr_participants_api.';
7 --
8 -- ---------------------------------------------------------------------------
9 -- |-----------------------< <create_participant> >--------------------------|
10 -- ---------------------------------------------------------------------------
11 --
12 procedure create_participant
13 (p_validate in boolean default false,
14 p_effective_date in date,
15 p_business_group_id in number,
16 p_questionnaire_template_id in number default null,
17 p_participation_in_table in varchar2,
18 p_participation_in_column in varchar2,
19 p_participation_in_id in number,
20 p_participation_status in varchar2 default 'OPEN',
21 p_participation_type in varchar2 default null,
22 p_last_notified_date in date default null,
23 p_date_completed in date default null,
24 p_comments in varchar2 default null,
25 p_person_id in number,
26 p_attribute_category in varchar2 default null,
27 p_attribute1 in varchar2 default null,
28 p_attribute2 in varchar2 default null,
29 p_attribute3 in varchar2 default null,
30 p_attribute4 in varchar2 default null,
31 p_attribute5 in varchar2 default null,
32 p_attribute6 in varchar2 default null,
33 p_attribute7 in varchar2 default null,
34 p_attribute8 in varchar2 default null,
35 p_attribute9 in varchar2 default null,
36 p_attribute10 in varchar2 default null,
37 p_attribute11 in varchar2 default null,
38 p_attribute12 in varchar2 default null,
39 p_attribute13 in varchar2 default null,
40 p_attribute14 in varchar2 default null,
41 p_attribute15 in varchar2 default null,
42 p_attribute16 in varchar2 default null,
43 p_attribute17 in varchar2 default null,
44 p_attribute18 in varchar2 default null,
45 p_attribute19 in varchar2 default null,
46 p_attribute20 in varchar2 default null,
47 p_participant_usage_status in varchar2 default null,
48 p_participant_id out nocopy number,
49 p_object_version_number out nocopy number
50 )
51 is
52 --
53 -- Declare cursors and local variables
54 --
55 --
56 l_proc varchar2(72) := g_package||'create_participant';
57 l_participant_id per_participants.participant_id%TYPE;
58 l_object_version_number per_participants.object_version_number%TYPE;
59 begin
60 hr_utility.set_location('Entering:'|| l_proc, 5);
61 --
62 -- Issue a savepoint.
63 --
64 savepoint create_participant;
65 hr_utility.set_location(l_proc, 6);
66 --
67 -- Call Before Process User Hook
68 --
69 begin
70 hr_participants_bk1.create_participant_b (
71 p_effective_date => p_effective_date,
72 p_questionnaire_template_id => p_questionnaire_template_id,
73 p_business_group_id => p_business_group_id,
74 p_participation_in_table => p_participation_in_table,
75 p_participation_in_column => p_participation_in_column,
76 p_participation_in_id => p_participation_in_id,
77 p_participation_status => p_participation_status,
78 p_participation_type => p_participation_type,
79 p_last_notified_date => p_last_notified_date,
80 p_date_completed => p_date_completed,
81 p_comments => p_comments,
82 p_person_id => p_person_id,
83 p_attribute_category => p_attribute_category,
84 p_attribute1 => p_attribute1,
85 p_attribute2 => p_attribute2,
86 p_attribute3 => p_attribute3,
87 p_attribute4 => p_attribute4,
88 p_attribute5 => p_attribute5,
89 p_attribute6 => p_attribute6,
90 p_attribute7 => p_attribute7,
91 p_attribute8 => p_attribute8,
92 p_attribute9 => p_attribute9,
93 p_attribute10 => p_attribute10,
94 p_attribute11 => p_attribute11,
95 p_attribute12 => p_attribute12,
96 p_attribute13 => p_attribute13,
97 p_attribute14 => p_attribute14,
98 p_attribute15 => p_attribute15,
99 p_attribute16 => p_attribute16,
100 p_attribute17 => p_attribute17,
101 p_attribute18 => p_attribute18,
102 p_attribute19 => p_attribute19,
103 p_attribute20 => p_attribute20,
104 p_participant_usage_status => p_participant_usage_status
108 hr_api.cannot_find_prog_unit_error
105 );
106 exception
107 when hr_api.cannot_find_prog_unit then
109 (p_module_name => 'create_participant',
110 p_hook_type => 'BP'
111 );
112 end;
113 --
114 -- End of Before Process User Hook call
115 --
116 -- Validation in addition to Table Handlers
117 --
118 hr_utility.set_location(l_proc, 7);
119 --
120 -- Process Logic
121 --
122 per_par_ins.ins
123 (p_validate => p_validate,
124 p_effective_date => p_effective_date,
125 p_questionnaire_template_id => p_questionnaire_template_id,
126 p_business_group_id => p_business_group_id,
127 p_participation_in_table => p_participation_in_table,
128 p_participation_in_column => p_participation_in_column,
129 p_participation_in_id => p_participation_in_id,
130 p_participation_status => p_participation_status,
131 p_participation_type => p_participation_type,
132 p_last_notified_date => p_last_notified_date,
133 p_date_completed => p_date_completed,
134 p_comments => p_comments,
135 p_person_id => p_person_id,
136 p_attribute_category => p_attribute_category,
137 p_attribute1 => p_attribute1,
138 p_attribute2 => p_attribute2,
139 p_attribute3 => p_attribute3,
140 p_attribute4 => p_attribute4,
141 p_attribute5 => p_attribute5,
142 p_attribute6 => p_attribute6,
143 p_attribute7 => p_attribute7,
144 p_attribute8 => p_attribute8,
145 p_attribute9 => p_attribute9,
146 p_attribute10 => p_attribute10,
147 p_attribute11 => p_attribute11,
148 p_attribute12 => p_attribute12,
149 p_attribute13 => p_attribute13,
150 p_attribute14 => p_attribute14,
151 p_attribute15 => p_attribute15,
152 p_attribute16 => p_attribute16,
153 p_attribute17 => p_attribute17,
154 p_attribute18 => p_attribute18,
155 p_attribute19 => p_attribute19,
156 p_attribute20 => p_attribute20,
157 p_participant_usage_status => p_participant_usage_status,
158 p_participant_id => l_participant_id,
159 p_object_version_number => l_object_version_number
160 );
161 --
162 hr_utility.set_location(l_proc, 8);
163 --
164 -- Call After Process User Hook
165 --
166 begin
167 hr_participants_bk1.create_participant_a (
168 p_participant_id => l_participant_id,
169 p_object_version_number => l_object_version_number,
170 p_questionnaire_template_id => p_questionnaire_template_id,
171 p_effective_date => p_effective_date,
172 p_business_group_id => p_business_group_id,
173 p_participation_in_table => p_participation_in_table,
174 p_participation_in_column => p_participation_in_column,
175 p_participation_in_id => p_participation_in_id,
176 p_participation_status => p_participation_status,
177 p_participation_type => p_participation_type,
178 p_last_notified_date => p_last_notified_date,
179 p_date_completed => p_date_completed,
180 p_comments => p_comments,
181 p_person_id => p_person_id,
182 p_attribute_category => p_attribute_category,
183 p_attribute1 => p_attribute1,
184 p_attribute2 => p_attribute2,
185 p_attribute3 => p_attribute3,
186 p_attribute4 => p_attribute4,
187 p_attribute5 => p_attribute5,
188 p_attribute6 => p_attribute6,
189 p_attribute7 => p_attribute7,
190 p_attribute8 => p_attribute8,
191 p_attribute9 => p_attribute9,
192 p_attribute10 => p_attribute10,
193 p_attribute11 => p_attribute11,
194 p_attribute12 => p_attribute12,
195 p_attribute13 => p_attribute13,
196 p_attribute14 => p_attribute14,
197 p_attribute15 => p_attribute15,
198 p_attribute16 => p_attribute16,
199 p_attribute17 => p_attribute17,
200 p_attribute18 => p_attribute18,
201 p_attribute19 => p_attribute19,
202 p_attribute20 => p_attribute20,
203 p_participant_usage_status => p_participant_usage_status
204 );
205 exception
206 when hr_api.cannot_find_prog_unit then
207 hr_api.cannot_find_prog_unit_error
208 (p_module_name => 'create_participant',
209 p_hook_type => 'AP'
210 );
211 end;
212 --
213 -- End of After process user hook
214 --
215 -- When in validation only mode raise the Validate_Enabled exception
216 --
217 if p_validate then
218 raise hr_api.validate_enabled;
219 end if;
220 --
221 -- Set all output arguments
222 --
223 p_participant_id := l_participant_id;
227 exception
224 p_object_version_number := l_object_version_number;
225 --
226 hr_utility.set_location(' Leaving:'||l_proc, 11);
228 when hr_api.validate_enabled then
229 --
230 -- As the Validate_Enabled exception has been raised
231 -- we must rollback to the savepoint
232 --
233 ROLLBACK TO create_participant;
234 --
235 -- Only set output warning arguments
236 -- (Any key or derived arguments must be set to null
237 -- when validation only mode is being used.)
238 --
239 p_participant_id := null;
240 p_object_version_number := null;
241 --
242 when others then
243 --
244 -- A validation or unexpected error has occurred
245 --
246 -- Added as part of fix to bug 632482
247 --
248 ROLLBACK TO create_participant;
249 --
250 -- set in out parameters and set out parameters
251 --
252 p_participant_id := null;
253 p_object_version_number := null;
254 raise;
255 --
256 -- End of fix.
257 --
258 hr_utility.set_location(' Leaving:'||l_proc, 12);
259 end create_participant;
260 --
261 --
262 -- ---------------------------------------------------------------------------
263 -- |-----------------------< <update_participant> >--------------------------|
264 -- ---------------------------------------------------------------------------
265 --
266 procedure update_participant
267 (p_validate in boolean default false,
268 p_effective_date in date,
269 p_participant_id in number,
270 p_object_version_number in out nocopy number,
271 p_questionnaire_template_id in number default hr_api.g_number,
272 p_participation_status in varchar2 default hr_api.g_varchar2,
273 p_participation_type in varchar2 default hr_api.g_varchar2,
274 p_last_notified_date in date default hr_api.g_date,
275 p_date_completed in date default hr_api.g_date,
276 p_comments in varchar2 default hr_api.g_varchar2,
277 p_person_id in number default hr_api.g_number,
278 p_attribute_category in varchar2 default hr_api.g_varchar2,
279 p_attribute1 in varchar2 default hr_api.g_varchar2,
280 p_attribute2 in varchar2 default hr_api.g_varchar2,
281 p_attribute3 in varchar2 default hr_api.g_varchar2,
282 p_attribute4 in varchar2 default hr_api.g_varchar2,
283 p_attribute5 in varchar2 default hr_api.g_varchar2,
284 p_attribute6 in varchar2 default hr_api.g_varchar2,
285 p_attribute7 in varchar2 default hr_api.g_varchar2,
286 p_attribute8 in varchar2 default hr_api.g_varchar2,
287 p_attribute9 in varchar2 default hr_api.g_varchar2,
288 p_attribute10 in varchar2 default hr_api.g_varchar2,
289 p_attribute11 in varchar2 default hr_api.g_varchar2,
290 p_attribute12 in varchar2 default hr_api.g_varchar2,
291 p_attribute13 in varchar2 default hr_api.g_varchar2,
292 p_attribute14 in varchar2 default hr_api.g_varchar2,
293 p_attribute15 in varchar2 default hr_api.g_varchar2,
294 p_attribute16 in varchar2 default hr_api.g_varchar2,
295 p_attribute17 in varchar2 default hr_api.g_varchar2,
296 p_attribute18 in varchar2 default hr_api.g_varchar2,
297 p_attribute19 in varchar2 default hr_api.g_varchar2,
298 p_attribute20 in varchar2 default hr_api.g_varchar2,
299 p_participant_usage_status in varchar2 default hr_api.g_varchar2
300 ) is
301 --
302 -- Declare cursors and local variables
303 --
304
305 l_proc varchar2(72) := g_package||'update_participant';
306 l_object_version_number per_participants.object_version_number%TYPE;
307 l_ovn per_participants.object_version_number%TYPE := p_object_version_number;
308 begin
309 hr_utility.set_location('Entering:'|| l_proc, 5);
310 --
311 -- Issue a savepoint.
312 --
313 savepoint update_participant;
314 hr_utility.set_location(l_proc, 6);
315 --
316 -- Call Before Process User Hook
317 --
318 begin
319 hr_participants_bk2.update_participant_b (
320 p_participant_id => p_participant_id,
321 p_object_version_number => p_object_version_number,
322 p_questionnaire_template_id => p_questionnaire_template_id,
323 p_effective_date => p_effective_date,
324 p_participation_status => p_participation_status,
325 p_participation_type => p_participation_type,
326 p_last_notified_date => p_last_notified_date,
327 p_date_completed => p_date_completed,
328 p_comments => p_comments,
329 p_person_id => p_person_id,
330 p_attribute_category => p_attribute_category,
331 p_attribute1 => p_attribute1,
332 p_attribute2 => p_attribute2,
333 p_attribute3 => p_attribute3,
334 p_attribute4 => p_attribute4,
335 p_attribute5 => p_attribute5,
336 p_attribute6 => p_attribute6,
337 p_attribute7 => p_attribute7,
341 p_attribute11 => p_attribute11,
338 p_attribute8 => p_attribute8,
339 p_attribute9 => p_attribute9,
340 p_attribute10 => p_attribute10,
342 p_attribute12 => p_attribute12,
343 p_attribute13 => p_attribute13,
344 p_attribute14 => p_attribute14,
345 p_attribute15 => p_attribute15,
346 p_attribute16 => p_attribute16,
347 p_attribute17 => p_attribute17,
348 p_attribute18 => p_attribute18,
349 p_attribute19 => p_attribute19,
350 p_attribute20 => p_attribute20,
351 p_participant_usage_status => p_participant_usage_status
352 );
353 exception
354 when hr_api.cannot_find_prog_unit then
355 hr_api.cannot_find_prog_unit_error
356 (p_module_name => 'update_participant',
357 p_hook_type => 'BP'
358 );
359 end;
360 --
361 -- End of Before Process User Hook call
362 --
363 -- Validation in addition to Table Handlers
364 --
365 hr_utility.set_location(l_proc, 7);
366 --
367 -- Process Logic
368 --
369 l_object_version_number := p_object_version_number;
370 --
371 per_par_upd.upd
372 (p_validate => p_validate,
373 p_effective_date => p_effective_date,
374 p_participant_id => p_participant_id,
375 p_object_version_number => l_object_version_number,
376 p_questionnaire_template_id => p_questionnaire_template_id,
377 p_participation_status => p_participation_status,
378 p_participation_type => p_participation_type,
379 p_last_notified_date => p_last_notified_date,
380 p_date_completed => p_date_completed,
381 p_comments => p_comments,
382 -- p_person_id => p_person_id, -- bug 4046867
383 p_attribute_category => p_attribute_category,
384 p_attribute1 => p_attribute1,
385 p_attribute2 => p_attribute2,
386 p_attribute3 => p_attribute3,
387 p_attribute4 => p_attribute4,
388 p_attribute5 => p_attribute5,
389 p_attribute6 => p_attribute6,
390 p_attribute7 => p_attribute7,
391 p_attribute8 => p_attribute8,
392 p_attribute9 => p_attribute9,
393 p_attribute10 => p_attribute10,
394 p_attribute11 => p_attribute11,
395 p_attribute12 => p_attribute12,
396 p_attribute13 => p_attribute13,
397 p_attribute14 => p_attribute14,
398 p_attribute15 => p_attribute15,
399 p_attribute16 => p_attribute16,
400 p_attribute17 => p_attribute17,
401 p_attribute18 => p_attribute18,
402 p_attribute19 => p_attribute19,
403 p_attribute20 => p_attribute20,
404 p_participant_usage_status => p_participant_usage_status
405 );
406 --
407 --
408 hr_utility.set_location(l_proc, 8);
409 --
410 -- Call After Process User Hook
411 --
412 begin
413 hr_participants_bk2.update_participant_a (
414 p_participant_id => p_participant_id,
415 p_object_version_number => l_object_version_number,
416 p_questionnaire_template_id => p_questionnaire_template_id,
417 p_effective_date => p_effective_date,
418 p_participation_status => p_participation_status,
419 p_participation_type => p_participation_type,
420 p_last_notified_date => p_last_notified_date,
421 p_date_completed => p_date_completed,
422 p_comments => p_comments,
423 p_person_id => p_person_id,
424 p_attribute_category => p_attribute_category,
425 p_attribute1 => p_attribute1,
426 p_attribute2 => p_attribute2,
427 p_attribute3 => p_attribute3,
428 p_attribute4 => p_attribute4,
429 p_attribute5 => p_attribute5,
430 p_attribute6 => p_attribute6,
431 p_attribute7 => p_attribute7,
432 p_attribute8 => p_attribute8,
433 p_attribute9 => p_attribute9,
434 p_attribute10 => p_attribute10,
435 p_attribute11 => p_attribute11,
436 p_attribute12 => p_attribute12,
437 p_attribute13 => p_attribute13,
438 p_attribute14 => p_attribute14,
439 p_attribute15 => p_attribute15,
440 p_attribute16 => p_attribute16,
441 p_attribute17 => p_attribute17,
442 p_attribute18 => p_attribute18,
443 p_attribute19 => p_attribute19,
444 p_attribute20 => p_attribute20,
445 p_participant_usage_status => p_participant_usage_status
446 );
447 exception
448 when hr_api.cannot_find_prog_unit then
449 hr_api.cannot_find_prog_unit_error
450 (p_module_name => 'update_participant',
451 p_hook_type => 'AP'
455 -- End After Process User Hook
452 );
453 end;
454 --
456 --
457 -- When in validation only mode raise the Validate_Enabled exception
458 --
459 if p_validate then
460 raise hr_api.validate_enabled;
461 end if;
462 --
463 -- Set all output arguments. l_object_version_number now has the new
464 -- object version number as the update was successful
465 --
466 p_object_version_number := l_object_version_number;
467 --
468 hr_utility.set_location(' Leaving:'||l_proc, 11);
469 exception
470 when hr_api.validate_enabled then
471 --
472 -- As the Validate_Enabled exception has been raised
473 -- we must rollback to the savepoint
474 --
475 ROLLBACK TO update_participant;
476 --
477 -- Only set output warning arguments and in out arguments back
478 -- to their IN value
479 -- (Any key or derived arguments must be set to null
480 -- when validation only mode is being used.)
481 --
482 p_object_version_number := l_object_version_number;
483 --
484 when others then
485 --
486 -- A validation or unexpected error has occurred
487 --
488 -- Added as part of fix to bug 632482
489 --
490 ROLLBACK TO update_participant;
491 --
492 -- set in out parameters and set out parameters
493 --
494 p_object_version_number := l_ovn;
495 raise;
496 --
497 -- End of fix.
498 --
499 hr_utility.set_location(' Leaving:'||l_proc, 12);
500 --
501 end update_participant;
502 --
503 --
504 -- ---------------------------------------------------------------------------
505 -- |-----------------------< <delete_participant> >--------------------------|
506 -- ---------------------------------------------------------------------------
507 --
508 procedure delete_participant
509 (p_validate in boolean default false,
510 p_participant_id in number,
511 p_object_version_number in number
512 ) is
513 --
514 -- Declare cursors and local variables
515 --
516 --
517 --
518 l_proc varchar2(72) := g_package||'delete_participant';
519
520 cursor c_quest_ans_id
521 is
522 select questionnaire_answer_id
523 from hr_quest_answers
524 where hr_quest_answers.type = 'PARTICIPANT' and
525 hr_quest_answers.type_object_id = p_participant_id;
526 -- Update in lines of Bug No.1386826 . Now from Appraisals V5, participant can have a Qnr.
527 l_quest_ans_id hr_quest_answers.questionnaire_answer_id%type;
528
529 --
530 begin
531 hr_utility.set_location('Entering:'|| l_proc, 5);
532 --
533 -- Issue a savepoint.
534 --
535 savepoint delete_participant;
536 hr_utility.set_location(l_proc, 6);
537 --
538 -- Call Before Process User Hook
539 --
540 begin
541 hr_participants_bk3.delete_participant_b
542 (
543 p_participant_id => p_participant_id,
544 p_object_version_number => p_object_version_number
545 );
546 exception
547 when hr_api.cannot_find_prog_unit then
548 hr_api.cannot_find_prog_unit_error
549 (p_module_name => 'delete_participant',
550 p_hook_type => 'BP'
551 );
552 end;
553 --
554 -- End of Before Process User hook
555 --
556 -- Validation in addition to Table Handlers
557 --
558 hr_utility.set_location(l_proc, 7);
559
560 open c_quest_ans_id;
561 fetch c_quest_ans_id into l_quest_ans_id;
562 if c_quest_ans_id%found then
563 -- deleting answers and answer values.
564 hr_quest_perform_web.delete_quest_answer(p_questionnaire_answer_id => l_quest_ans_id);
565 end if;
566 close c_quest_ans_id;
567
568
569 --
570 -- Process Logic
571 --
572 --
573 -- now delete the participant itself
574 --
575 per_par_del.del
576 (p_validate => FALSE
577 ,p_participant_id => p_participant_id
578 ,p_object_version_number => p_object_version_number
579 );
580 --
581 hr_utility.set_location(l_proc, 8);
582 --
583 -- Call After Process User Hook
584 --
585 begin
586 hr_participants_bk3.delete_participant_a (
587 p_participant_id => p_participant_id,
588 p_object_version_number => p_object_version_number
589 );
590 exception
591 when hr_api.cannot_find_prog_unit then
592 hr_api.cannot_find_prog_unit_error
593 (p_module_name => 'delete_participant',
594 p_hook_type => 'AP'
595 );
596 end;
597 --
598 -- End of After Process User hook
599 --
600 -- When in validation only mode raise the Validate_Enabled exception
601 --
602 if p_validate then
603 raise hr_api.validate_enabled;
604 end if;
605 --
606 hr_utility.set_location(' Leaving:'||l_proc, 11);
607 exception
608 when hr_api.validate_enabled then
609 --
610 -- As the Validate_Enabled exception has been raised
611 -- we must rollback to the savepoint
612 --
613 ROLLBACK TO delete_participant;
614 --
615 --
616 when others then
617 --
618 -- A validation or unexpected error has occurred
619 --
620 -- Added as part of fix to bug 632482
621 --
622 ROLLBACK TO delete_participant;
623 raise;
624 --
625 -- End of fix.
626 --
627 hr_utility.set_location(' Leaving:'||l_proc, 12);
628 end delete_participant;
629 --
630 end hr_participants_api;