[Home] [Help]
PACKAGE BODY: APPS.HR_COMM_API
Source
1 Package Body hr_comm_api as
2 /* $Header: hrcomrhi.pkb 115.1 2002/12/18 13:22:35 hjonnala ship $ */
3 --
4 -- Private package current record structure definition
5 --
6 g_old_rec g_rec_type;
7 --
8 -- Global package name
9 --
10 g_package varchar2(33) := ' hr_comm_api.';
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------------< insert_dml >------------------------------|
14 -- ----------------------------------------------------------------------------
15 --
16 -- PRIVATE
17 -- Description: Base table hr_comments insert dml.
18 --
19 Procedure insert_dml(p_rec in out nocopy g_rec_type) is
20 --
21 l_proc varchar2(72) := g_package||'insert_dml';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 5);
25 --
26 -- Insert the row into: hr_comments
27 --
28 insert into hr_comments
29 ( comment_id,
30 source_table_name,
31 comment_text
32 )
33 Values
34 ( p_rec.comment_id,
35 p_rec.source_table_name,
36 p_rec.comment_text
37 );
38 --
39 hr_utility.set_location(' Leaving:'||l_proc, 10);
40 End insert_dml;
41 --
42 -- ----------------------------------------------------------------------------
43 -- |------------------------------< update_dml >------------------------------|
44 -- ----------------------------------------------------------------------------
45 --
46 -- PRIVATE
47 -- Description: Base table hr_comments update dml.
48 --
49 Procedure update_dml(p_rec in out nocopy g_rec_type) is
50 --
51 l_proc varchar2(72) := g_package||'update_dml';
52 --
53 Begin
54 hr_utility.set_location('Entering:'||l_proc, 5);
55 --
56 --
57 -- Update the hr_comments Row
58 --
59 update hr_comments
60 set
61 comment_id = p_rec.comment_id,
62 source_table_name = p_rec.source_table_name,
63 comment_text = p_rec.comment_text
64 where comment_id = p_rec.comment_id;
65 --
66 hr_utility.set_location(' Leaving:'||l_proc, 10);
67 End update_dml;
68 --
69 -- ----------------------------------------------------------------------------
70 -- |------------------------------< delete_dml >------------------------------|
71 -- ----------------------------------------------------------------------------
72 --
73 -- PRIVATE
74 -- Description: Base table dml.
75 --
76 Procedure delete_dml(p_rec in g_rec_type) is
77 --
78 l_proc varchar2(72) := g_package||'delete_dml';
79 --
80 Begin
81 hr_utility.set_location('Entering:'||l_proc, 5);
82 --
83 -- Delete the hr_comments row.
84 --
85 delete from hr_comments
86 where comment_id = p_rec.comment_id;
87 --
88 hr_utility.set_location(' Leaving:'||l_proc, 10);
89 End delete_dml;
90 --
91 -- ----------------------------------------------------------------------------
92 -- |------------------------------< pre_insert >------------------------------|
93 -- ----------------------------------------------------------------------------
94 --
95 -- PRIVATE
96 -- Description: Pre insert
97 --
98 Procedure pre_insert(p_rec in out nocopy g_rec_type) is
99 --
100 l_proc varchar2(72) := g_package||'pre_insert';
101 --
102 Cursor C_Sel1 is select hr_comments_s.nextval from sys.dual;
103 --
104 Begin
105 hr_utility.set_location('Entering:'||l_proc, 5);
106 --
107 --
108 -- Select the next sequence number
109 --
110 Open C_Sel1;
111 Fetch C_Sel1 Into p_rec.comment_id;
112 Close C_Sel1;
113 --
114 hr_utility.set_location(' Leaving:'||l_proc, 10);
115 End pre_insert;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |------------------------------< pre_update >------------------------------|
119 -- ----------------------------------------------------------------------------
120 --
121 -- PRIVATE
122 -- Description: Pre update
123 --
124 Procedure pre_update(p_rec in g_rec_type) is
125 --
126 l_proc varchar2(72) := g_package||'pre_update';
127 --
128 Begin
129 hr_utility.set_location('Entering:'||l_proc, 5);
130 --
131 hr_utility.set_location(' Leaving:'||l_proc, 10);
132 End pre_update;
133 --
134 -- ----------------------------------------------------------------------------
135 -- |------------------------------< pre_delete >------------------------------|
136 -- ----------------------------------------------------------------------------
137 --
138 -- PRIVATE
139 -- Description: Pre delete
140 --
141 Procedure pre_delete(p_rec in g_rec_type) is
142 --
143 l_proc varchar2(72) := g_package||'pre_delete';
144 --
145 Begin
146 hr_utility.set_location('Entering:'||l_proc, 5);
147 --
148 hr_utility.set_location(' Leaving:'||l_proc, 10);
149 End pre_delete;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |-----------------------------< post_insert >------------------------------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- PRIVATE
156 -- Description: Post insert
157 --
158 Procedure post_insert(p_rec in g_rec_type) is
159 --
160 l_proc varchar2(72) := g_package||'post_insert';
161 --
162 Begin
163 hr_utility.set_location('Entering:'||l_proc, 5);
164 --
165 hr_utility.set_location(' Leaving:'||l_proc, 10);
166 End post_insert;
167 --
168 -- ----------------------------------------------------------------------------
169 -- |-----------------------------< post_update >------------------------------|
170 -- ----------------------------------------------------------------------------
171 --
172 -- PRIVATE
173 -- Description: Post update
174 --
175 Procedure post_update(p_rec in g_rec_type) is
176 --
177 l_proc varchar2(72) := g_package||'post_update';
178 --
179 Begin
180 hr_utility.set_location('Entering:'||l_proc, 5);
181 --
182 hr_utility.set_location(' Leaving:'||l_proc, 10);
183 End post_update;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |-----------------------------< post_delete >------------------------------|
187 -- ----------------------------------------------------------------------------
188 --
189 -- PRIVATE
190 -- Description: Post delete
191 --
192 Procedure post_delete(p_rec in g_rec_type) is
193 --
194 l_proc varchar2(72) := g_package||'post_delete';
195 --
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 --
199 hr_utility.set_location(' Leaving:'||l_proc, 10);
200 End post_delete;
201 --
202 -- ----------------------------------------------------------------------------
203 -- |---------------------------------< lck >----------------------------------|
204 -- ----------------------------------------------------------------------------
205 --
206 -- PUBLIC
207 -- Description: Locks the required rows. If the object version attribute
208 -- is specified then the object version control also is checked.
209 --
210 Procedure lck
211 (
212 p_comment_id in number
213 ) is
214 --
215 -- Cursor selects the 'current' row from the HR Schema
216 --
217 Cursor C_Sel1 is
218 select comment_id,
219 source_table_name,
220 comment_text
221 from hr_comments
222 where comment_id = p_comment_id
223 for update nowait;
224 --
225 l_proc varchar2(72) := g_package||'lck';
226 --
227 Begin
228 hr_utility.set_location('Entering:'||l_proc, 5);
229 --
230 -- The primary key exists therefore we must now attempt to lock the
231 -- row.
232 --
233 Open C_Sel1;
234 Fetch C_Sel1 Into g_old_rec;
235 If C_Sel1%notfound then
236 --
237 -- If the row wasn't returned then:
238 -- a) The row does NOT exist.
239 --
240 Close C_Sel1;
241 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
242 hr_utility.set_message_token('TABLE_NAME', 'hr_comments');
243 hr_utility.raise_error;
244 End If;
245 Close C_Sel1;
246 --
247 hr_utility.set_location(' Leaving:'||l_proc, 10);
248 --
249 -- We need to trap the ORA LOCK exception
250 --
251 Exception
252 When HR_Api.Object_Locked then
253 --
254 -- The object is locked therefore we need to supply a meaningful
255 -- error message.
256 --
257 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
258 hr_utility.set_message_token('TABLE_NAME', 'hr_comments');
259 hr_utility.raise_error;
260 End lck;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |-----------------------------< convert_args >-----------------------------|
264 -- ----------------------------------------------------------------------------
265 --
266 -- PRIVATE
267 -- Description: Convert the attribute arguments which have been
268 -- passed through the attribute interface to the
269 -- record structure.
270 --
271 Function convert_args
272 (
273 p_comment_id in number,
274 p_source_table_name in varchar2,
275 p_comment_text in varchar2
276 )
277 Return g_rec_type is
278 --
279 l_rec g_rec_type;
280 l_proc varchar2(72) := g_package||'convert_args';
281 --
282 Begin
283 --
284 hr_utility.set_location('Entering:'||l_proc, 5);
285 --
286 -- Convert arguments into local l_rec structure.
287 --
288 l_rec.comment_id := p_comment_id;
289 l_rec.source_table_name := p_source_table_name;
290 l_rec.comment_text := p_comment_text;
291 --
292 -- Return the plsql record structure.
293 --
294 hr_utility.set_location(' Leaving:'||l_proc, 10);
295 Return(l_rec);
296 --
297 End convert_args;
298 --
299 -- ----------------------------------------------------------------------------
300 -- |-----------------------------< convert_defs >-----------------------------|
301 -- ----------------------------------------------------------------------------
302 --
303 -- PRIVATE
304 -- Description: Converts system defaulted values into corresponding row
305 -- attribute values.
306 --
307 Function convert_defs(p_rec in out nocopy g_rec_type)
308 Return g_rec_type is
309 --
310 l_proc varchar2(72) := g_package||'convert_defs';
311 --
312 Begin
313 --
314 hr_utility.set_location('Entering:'||l_proc, 5);
315 --
316 -- We must now examine each argument value in the
317 -- p_rec plsql record structure
318 -- to see if a system default is being used. If a system default
319 -- is being used then we must set to the 'current' argument value.
320 --
321 If (p_rec.source_table_name = hr_api.g_varchar2) then
322 p_rec.source_table_name := g_old_rec.source_table_name;
323 End If;
324 If (p_rec.comment_text = hr_api.g_varchar2) then
325 p_rec.comment_text := g_old_rec.comment_text;
326 End If;
327 --
328 -- Return the plsql record structure.
329 --
330 hr_utility.set_location(' Leaving:'||l_proc, 10);
331 Return(p_rec);
332 --
333 End convert_defs;
334 --
335 -- ----------------------------------------------------------------------------
336 -- |---------------------------< insert_validate >----------------------------|
337 -- ----------------------------------------------------------------------------
338 --
339 -- PRIVATE
340 -- Description: Controls the validation execution on insert.
341 --
342 Procedure insert_validate(p_rec in g_rec_type) is
343 --
344 l_proc varchar2(72) := g_package||'insert_validate';
345 --
346 Begin
347 hr_utility.set_location('Entering:'||l_proc, 5);
348 --
349 -- Call all supporting business operations
350 --
351 hr_utility.set_location(' Leaving:'||l_proc, 10);
352 End insert_validate;
353 --
354 -- ----------------------------------------------------------------------------
355 -- |---------------------------< update_validate >----------------------------|
356 -- ----------------------------------------------------------------------------
357 --
358 -- PRIVATE
359 -- Description: Controls the validation execution on update.
360 --
361 Procedure update_validate(p_rec in g_rec_type) is
362 --
363 l_proc varchar2(72) := g_package||'update_validate';
364 --
365 Begin
366 hr_utility.set_location('Entering:'||l_proc, 5);
367 --
368 -- Call all supporting business operations
369 --
370 hr_utility.set_location(' Leaving:'||l_proc, 10);
371 End update_validate;
372 --
373 -- ----------------------------------------------------------------------------
374 -- |---------------------------< delete_validate >----------------------------|
375 -- ----------------------------------------------------------------------------
376 --
377 -- PRIVATE
378 -- Description: Controls the validation execution on delete.
379 --
380 Procedure delete_validate(p_rec in g_rec_type) is
381 --
382 l_proc varchar2(72) := g_package||'delete_validate';
383 --
384 Begin
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 --
387 -- Call all supporting business operations
388 --
389 hr_utility.set_location(' Leaving:'||l_proc, 10);
390 End delete_validate;
391 --
392 -- ----------------------------------------------------------------------------
393 -- |---------------------------------< ins >----------------------------------|
394 -- ----------------------------------------------------------------------------
395 --
396 -- PUBLIC
397 -- Description: Insert entity interface
398 --
399 -- hr_comm_api.ins entity business process model
400 -- --------------------------------------------------------------------------
401 --
402 -- ins
403 -- |
404 -- |-- insert_validate
405 -- | |-- <validation operations>
406 -- |
407 -- |-- pre_insert
408 -- |-- insert_dml
409 -- |-- post_insert
410 --
411 -- --------------------------------------------------------------------------
412 Procedure ins
413 (
414 p_rec in out nocopy g_rec_type,
415 p_validate in boolean default false
416 ) is
417 --
418 l_proc varchar2(72) := g_package||'ins';
419 --
420 Begin
421 hr_utility.set_location('Entering:'||l_proc, 5);
422 --
423 -- Determine if the business process is to be validated.
424 --
425 If p_validate then
426 --
427 -- Issue the savepoint.
428 --
429 SAVEPOINT ins_hr_comm;
430 End If;
431 --
432 -- Call the supporting insert validate operations
433 --
434 insert_validate(p_rec);
435 --
436 -- Call the supporting pre-insert operation
437 --
438 pre_insert(p_rec);
439 --
440 -- Insert the row
441 --
442 insert_dml(p_rec);
443 --
444 -- Call the supporting post-insert operation
445 --
446 post_insert(p_rec);
447 --
448 -- If we are validating then raise the Validate_Enabled exception
449 --
450 If p_validate then
451 Raise HR_Api.Validate_Enabled;
452 End If;
453 --
454 hr_utility.set_location(' Leaving:'||l_proc, 10);
455 Exception
456 When HR_Api.Validate_Enabled Then
457 --
458 -- As the Validate_Enabled exception has been raised
459 -- we must rollback to the savepoint
460 --
461 ROLLBACK TO ins_hr_comm;
462 end ins;
463 --
464 -- ----------------------------------------------------------------------------
465 -- |---------------------------------< ins >----------------------------------|
466 -- ----------------------------------------------------------------------------
467 --
468 -- PUBLIC
469 -- Description: Insert attribute interface
470 --
471 -- hr_comm_api.ins attribute business process model
472 -- --------------------------------------------------------------------------
473 --
474 -- ins
475 -- |
476 -- |-- convert_args
477 -- |-- ins
478 -- |
479 -- |-- insert_validate
480 -- | |-- <validation operations>
481 -- |
482 -- |-- pre_insert
483 -- |-- insert_dml
484 -- |-- post_insert
485 --
486 -- --------------------------------------------------------------------------
487 Procedure ins
488 (
489 p_comment_id out nocopy number,
490 p_source_table_name in varchar2,
491 p_comment_text in varchar2 default null,
492 p_validate in boolean default false
493 ) is
494 --
495 l_rec g_rec_type;
496 l_proc varchar2(72) := g_package||'ins';
497 --
498 Begin
499 hr_utility.set_location('Entering:'||l_proc, 5);
500 --
501 -- Call conversion function to turn arguments into the
502 -- p_rec structure.
503 --
504 l_rec :=
505 convert_args
506 (
507 null,
508 p_source_table_name,
509 p_comment_text
510 );
511 --
512 -- Having converted the arguments into the hr_comm_rec
513 -- plsql record structure we call the corresponding record business process.
514 --
515 ins(l_rec, p_validate);
516 --
517 -- As the primary key argument(s)
518 -- are specified as an OUT's we must set these values.
519 --
520 p_comment_id := l_rec.comment_id;
521 --
522 hr_utility.set_location(' Leaving:'||l_proc, 10);
523 End ins;
524 --
525 -- ----------------------------------------------------------------------------
526 -- |---------------------------------< upd >----------------------------------|
527 -- ----------------------------------------------------------------------------
528 --
529 -- PUBLIC
530 -- Description: Update entity interface
531 --
532 -- hr_comm_api.upd entity business process model
533 -- --------------------------------------------------------------------------
534 --
535 -- upd
536 -- |
537 -- |-- lck
538 -- |-- convert_defs
539 -- |-- update_validate
540 -- | |-- <validation operations>
541 -- |
542 -- |-- pre_update
543 -- |-- update_dml
544 -- |-- post_update
545 --
546 -- --------------------------------------------------------------------------
547 Procedure upd
548 (
549 p_rec in out nocopy g_rec_type,
550 p_validate in boolean default false
551 ) is
552 --
553 l_proc varchar2(72) := g_package||'upd';
554 --
555 Begin
556 hr_utility.set_location('Entering:'||l_proc, 5);
557 --
558 -- We are updating using the primary key therefore
559 -- we must ensure that the argument value is NOT null.
560 --
561 If (p_rec.comment_id is null) then
562 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
563 hr_utility.set_message_token('PROCEDURE', l_proc);
564 hr_utility.set_message_token('STEP','5');
565 hr_utility.raise_error;
566 Else
567 --
568 -- Determine if the business process is to be validated.
569 --
570 If p_validate then
571 --
572 -- Issue the savepoint.
573 --
574 SAVEPOINT upd_hr_comm;
575 End If;
576 --
577 -- We must lock the row which we need to update.
578 --
579 lck(p_rec.comment_id);
580 --
581 -- 1. During an update system defaults are used to determine if
582 -- arguments have been defaulted or not. We must therefore
583 -- derive the full record structure values to be updated.
584 --
585 -- 2. Call the supporting update validate operations.
586 --
587 update_validate(convert_defs(p_rec));
588 --
589 -- Call the supporting pre-update operation
590 --
591 pre_update(p_rec);
592 --
593 -- Update the row.
594 --
595 update_dml(p_rec);
596 --
597 -- Call the supporting post-update operation
598 --
599 post_update(p_rec);
600 --
601 -- If we are validating then raise the Validate_Enabled exception
602 --
603 If p_validate then
604 Raise HR_Api.Validate_Enabled;
605 End If;
606 --
607 End If;
608 hr_utility.set_location(' Leaving:'||l_proc, 10);
609 Exception
610 When HR_Api.Validate_Enabled Then
611 --
612 -- As the Validate_Enabled exception has been raised
613 -- we must rollback to the savepoint
614 --
615 ROLLBACK TO upd_hr_comm;
616 End upd;
617 --
618 -- ----------------------------------------------------------------------------
619 -- |---------------------------------< upd >----------------------------------|
620 -- ----------------------------------------------------------------------------
621 --
622 -- PUBLIC
623 -- Description: Update attribute interface
624 --
625 -- hr_comm_api.upd attribute business process model
626 -- --------------------------------------------------------------------------
627 --
628 -- upd
629 -- |
630 -- |-- convert_args
631 -- |-- upd
632 -- |
633 -- |-- lck
634 -- |-- convert_defs
635 -- |-- update_validate
636 -- | |-- <validation operations>
637 -- |
638 -- |-- pre_update
639 -- |-- update_dml
640 -- |-- post_update
641 --
642 -- --------------------------------------------------------------------------
643 Procedure upd
644 (
645 p_comment_id in out nocopy number,
646 p_source_table_name in varchar2 default hr_api.g_varchar2,
647 p_comment_text in varchar2 default hr_api.g_varchar2,
648 p_validate in boolean default false
649 ) is
650 --
651 l_rec g_rec_type;
652 l_proc varchar2(72) := g_package||'upd';
653 --
654 Begin
655 hr_utility.set_location('Entering:'||l_proc, 5);
656 --
657 -- Call conversion function to turn arguments into the
658 -- l_rec structure.
659 --
660 l_rec :=
661 convert_args
662 (
663 p_comment_id,
664 p_source_table_name,
665 p_comment_text
666 );
667 --
668 -- Having converted the arguments into the
669 -- plsql record structure we call the corresponding record
670 -- business process.
671 --
672 upd(l_rec, p_validate);
673 --
674 hr_utility.set_location(' Leaving:'||l_proc, 10);
675 End upd;
676 --
677 -- ----------------------------------------------------------------------------
678 -- |---------------------------------< del >----------------------------------|
679 -- ----------------------------------------------------------------------------
680 --
681 -- PUBLIC
682 -- Description: Delete entity interface
683 --
684 -- hr_comm_api.del entity business process model
685 -- --------------------------------------------------------------------------
686 --
687 -- del
688 -- |
689 -- |-- lck
690 -- |-- delete_validate
691 -- | |-- <validation operations>
692 -- |
693 -- |-- pre_delete
694 -- |-- delete_dml
695 -- |-- post_delete
696 --
697 -- --------------------------------------------------------------------------
698 Procedure del
699 (
700 p_rec in g_rec_type,
701 p_validate in boolean default false
702 ) is
703 --
704 l_proc varchar2(72) := g_package||'del';
705 --
706 Begin
707 hr_utility.set_location('Entering:'||l_proc, 5);
708 --
709 -- We are deleting using the primary key therefore
710 -- we must ensure that the argument value is NOT null.
711 --
712 If (p_rec.comment_id is null) then
713 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
714 hr_utility.set_message_token('PROCEDURE', l_proc);
715 hr_utility.set_message_token('STEP','5');
716 hr_utility.raise_error;
717 Else
718 --
719 -- Determine if the business process is to be validated.
720 --
721 If p_validate then
722 --
723 -- Issue the savepoint.
724 --
725 SAVEPOINT del_hr_comm;
726 End If;
727 --
728 -- We must lock the row which we need to delete.
729 --
730 lck(p_rec.comment_id);
731 --
732 -- Call the supporting delete validate operation
733 --
734 delete_validate(p_rec);
735 --
736 -- Call the supporting pre-delete operation
737 --
738 pre_delete(p_rec);
739 --
740 -- Delete the row.
741 --
742 delete_dml(p_rec);
743 --
744 -- Call the supporting post-delete operation
745 --
746 post_delete(p_rec);
747 --
748 -- If we are validating then raise the Validate_Enabled exception
749 --
750 If p_validate then
751 Raise HR_Api.Validate_Enabled;
752 End If;
753 End If;
754 --
755 hr_utility.set_location(' Leaving:'||l_proc, 10);
756 Exception
757 When HR_Api.Validate_Enabled Then
758 --
759 -- As the Validate_Enabled exception has been raised
760 -- we must rollback to the savepoint
761 --
762 ROLLBACK TO del_hr_comm;
763 End del;
764 --
765 -- ----------------------------------------------------------------------------
766 -- |---------------------------------< del >----------------------------------|
767 -- ----------------------------------------------------------------------------
768 --
769 -- PUBLIC
770 -- Description: Delete attribute interface
771 --
772 -- hr_comm_api.del attribute business process model
773 -- --------------------------------------------------------------------------
774 --
775 -- del
776 -- |
777 -- |-- del
778 -- |
779 -- |-- lck
780 -- |-- delete_validate
781 -- | |-- <validation operations>
782 -- |
783 -- |-- pre_delete
784 -- |-- delete_dml
785 -- |-- post_delete
786 --
787 -- --------------------------------------------------------------------------
788 Procedure del
789 (
790 p_comment_id in number,
791 p_validate in boolean default false
792 ) is
793 --
794 l_rec g_rec_type;
795 l_proc varchar2(72) := g_package||'del';
796 --
797 Begin
798 hr_utility.set_location('Entering:'||l_proc, 5);
799 --
800 -- As the delete procedure accepts a plsql record structure we do need to
801 -- convert the arguments into the record structure.
802 -- We don't need to call the supplied conversion argument routine as we
803 -- only need a few attributes.
804 --
805 l_rec.comment_id := p_comment_id;
806 --
807 --
808 -- Having converted the arguments into the hr_comm_rec
809 -- plsql record structure we must call the corresponding entity
810 -- business process
811 --
812 del(l_rec, p_validate);
813 --
814 hr_utility.set_location(' Leaving:'||l_proc, 10);
815 End del;
816 --
817 end hr_comm_api;