1 PACKAGE BODY OTA_TAD_API as
2 /* $Header: ottad01t.pkb 115.10 2004/02/10 07:10:30 hwinsor 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) := ' ota_tad_api.';
11 --
12 -- Global api dml status
13 --
14 g_api_dml boolean;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< return_api_dml_status >-------------------------|
18 -- ----------------------------------------------------------------------------
19 -- {Start Of Comments}
20 --
21 -- Description:
22 -- This function will return the current g_api_dml private global
23 -- boolean status.
24 -- The g_api_dml status determines if at the time of the function
25 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
26 -- is being issued from within an api.
27 -- If the status is TRUE then a dml statement is being issued from
28 -- within this entity api.
29 -- This function is primarily to support database triggers which
30 -- need to maintain the object_version_number for non-supported
31 -- dml statements (i.e. dml statement issued outside of the api layer).
32 --
33 -- Pre Conditions:
34 -- None.
35 --
36 -- In Arguments:
37 -- None.
38 --
39 -- Post Success:
40 -- Processing continues.
41 -- If the function returns a TRUE value then, dml is being executed from
42 -- within this api.
43 --
44 -- Post Failure:
45 -- None.
46 --
47 -- {End Of Comments}
48 -- ----------------------------------------------------------------------------
49 Function return_api_dml_status Return Boolean Is
50 --
51 l_proc varchar2(72) := g_package||'return_api_dml_status';
52 --
53 Begin
54 hr_utility.set_location('Entering:'||l_proc, 5);
55 --
56 Return (nvl(g_api_dml, false));
57 --
58 hr_utility.set_location(' Leaving:'||l_proc, 10);
59 End return_api_dml_status;
60 --
61 -- ----------------------------------------------------------------------------
62 -- |---------------------------< constraint_error >---------------------------|
63 -- ----------------------------------------------------------------------------
64 -- {Start Of Comments}
65 --
66 -- Description:
67 -- This procedure is called when a constraint has been violated (i.e.
68 -- The exception hr_api.check_integrity_violated,
69 -- hr_api.parent_integrity_violated or hr_api.child_integrity_violated has
70 -- been raised).
71 -- The exceptions can only be raised as follows:
72 -- 1) A check constraint can only be violated during an INSERT or UPDATE
73 -- dml operation.
74 -- 2) A parent integrity constraint can only be violated during an
75 -- INSERT or UPDATE dml operation.
76 -- 3) A child integrity constraint can only be violated during an
77 -- DELETE dml operation.
78 --
79 -- Pre Conditions:
80 -- Either hr_api.check_integrity_violated, hr_api.parent_integrity_violated
81 -- or hr_api.child_integrity_violated has been raised with the subsequent
82 -- stripping of the constraint name from the generated error message text.
83 --
84 -- In Arguments:
85 -- p_constraint_name is in upper format and is just the constraint name
86 -- (e.g. not prefixed by brackets, schema owner etc).
87 --
88 -- Post Success:
89 -- Development dependant.
90 --
91 -- Post Failure:
92 -- Developement dependant.
93 --
94 -- Developer Implementation Notes:
95 -- For each constraint being checked the hr system package failure message
96 -- has been generated as a template only. These system error messages should
97 -- be modified as required (i.e. change the system failure message to a user
98 -- friendly defined error message).
99 --
100 -- {End Of Comments}
101 -- ----------------------------------------------------------------------------
102 Procedure constraint_error
103 (p_constraint_name in varchar2) Is
104 --
105 l_proc varchar2(72) := g_package||'constraint_error';
106 --
107 Begin
108 hr_utility.set_location('Entering:'||l_proc, 5);
109 --
110 If (p_constraint_name = 'OTA_ACTIVITY_DEFINITIONS_FK1') Then
111 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
112 hr_utility.set_message_token('PROCEDURE', l_proc);
113 hr_utility.set_message_token('STEP','5');
114 hr_utility.raise_error;
115 ElsIf (p_constraint_name = 'OTA_TAD_MULTIPLE_CON_VERSI_CHK') Then
116 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
117 hr_utility.set_message_token('PROCEDURE', l_proc);
118 hr_utility.set_message_token('STEP','10');
119 hr_utility.raise_error;
120 Else
121 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
122 hr_utility.set_message_token('PROCEDURE', l_proc);
123 hr_utility.set_message_token('STEP','15');
124 hr_utility.raise_error;
125 End If;
126 --
127 hr_utility.set_location(' Leaving:'||l_proc, 10);
128 End constraint_error;
129 --
130 -- ----------------------------------------------------------------------------
131 -- |--------------------------< call_error_message >--------------------------|
132 -- ----------------------------------------------------------------------------
133 --
134 -- Description:
135 -- Passes the error information to the procedure set_message of package
136 -- hr_utility.
137 --
138 Procedure call_error_message
139 (
140 p_error_appl varchar2
141 ,p_error_txt varchar2
142 ) is
143 --
144 v_proc varchar2(72) := g_package||'call_error_message';
145 --
146 Begin
147 --
148 hr_utility.set_location('Entering:'|| v_proc, 5);
149 --
150 -- ** TEMP ** Add error message with the following text.
151 --
152 fnd_message.set_name ( p_error_appl ,p_error_txt);
153 fnd_message.raise_error;
154 --
155 hr_utility.set_location(' Leaving:'|| v_proc, 10);
156 --
157 End call_error_message;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |---------------------------< check_unique_name >--------------------------|
161 -- ----------------------------------------------------------------------------
162 --
163 -- PUBLIC
164 -- Description:
165 -- The activity name is mandantory and must be unique within business group.
166 --
167 Procedure check_unique_name
168 (
169 p_name in varchar2
170 ,p_business_group_id in number
171 ,p_activity_id in number
172 ) is
173 --
174 v_exists varchar2(1);
175 v_proc varchar2(72) := g_package||'check_unique_name';
176 --
177 cursor sel_unique_name is
178 select 'Y'
179 from ota_activity_definitions_vl tad
180 where tad.name = p_name
181 and tad.business_group_id = p_business_group_id
182 and (p_activity_id is null or tad.activity_id <> p_activity_id);
183 --
184 Begin
185 --
186 hr_utility.set_location('Entering:'|| v_proc, 5);
187 --
188 Open sel_unique_name;
189 fetch sel_unique_name into v_exists;
190 --
191 if sel_unique_name%found then
192 --
193 close sel_unique_name;
194 --
195 -- ** TEMP ** Add error message with the following text.
196 --
197 call_error_message( p_error_appl => 'OTA'
198 , p_error_txt => 'OTA_13331_TAD_DUPLICATE'
199 );
200 --
201 end if;
202 --
203 close sel_unique_name;
204 --
205 hr_utility.set_location(' Leaving:'|| v_proc, 10);
206 --
207 End check_unique_name;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |---------------------------< get_activity_id >----------------------------|
211 -- ----------------------------------------------------------------------------
212 --
213 -- PUBLIC
214 -- Description:
215 -- Return the surrogate key from a passed parameter of name or code.
216 --
217 Function get_activity_id
218 (
219 p_name in varchar2
220 ,p_business_group_id in number
221 )
222 Return number is
223 --
224 v_activity_id ota_activity_definitions.activity_id%TYPE ;
225 v_proc varchar2(72) := g_package||'get_activity_id';
226 --
227 cursor sel_activity_id is
228 select activity_id
229 from ota_activity_definitions_vl tad
230 where tad.name = p_name
231 and tad.business_group_id = p_business_group_id;
232 --
233 Begin
234 --
235 hr_utility.set_location('Entering:'|| v_proc, 5);
236 --
237 If p_name is not null Then
238 --
239 Open sel_activity_id;
240 fetch sel_activity_id into v_activity_id;
241 --
242 if sel_activity_id%notfound then
243 --
244 close sel_activity_id;
245 --
246 -- ** TEMP ** Add error message with the following text.
247 --
248 call_error_message( p_error_appl => 'OTA'
249 , p_error_txt => 'OTA_13332_TAD_NOT_EXISTS'
250 );
251 --
252 end if;
253 --
254 close sel_activity_id;
255 --
256 return( v_activity_id);
257 --
258 Else
259 --
260 -- ** TEMP ** Add error message with the following text.
261 --
262 call_error_message( p_error_appl => 'OTA'
263 , p_error_txt => 'OTA_13332_TAD_NOT_EXISTS'
264 );
265 --
266 End if;
267 --
268 hr_utility.set_location(' Leaving:'|| v_proc, 10);
269 --
270 End get_activity_id;
271 --
272 -- ----------------------------------------------------------------------------
273 -- |----------------------------< check_tav_exist >---------------------------|
274 -- ----------------------------------------------------------------------------
275 --
276 -- PUBLIC
277 -- Description:
278 -- Delete validation
279 -- Check whether any child rows in ota_activity_versions exist for
280 -- this activity. If they do exist then this activity may not be deleted.
281 --
282 Procedure check_tav_exist
283 (
284 p_activity_id in number
285 ) is
286 --
287 v_exists varchar2(1);
288 v_proc varchar2(72) := g_package||'check_tav_exist';
289 --
290 cursor sel_tav_exist is
291 select 'Y'
292 from ota_activity_versions tav
293 where tav.activity_id = p_activity_id;
294 --
295 Begin
296 --
297 hr_utility.set_location('Entering:'|| v_proc, 5);
298 --
299 Open sel_tav_exist;
300 fetch sel_tav_exist into v_exists;
301 --
302 if sel_tav_exist%found then
303 --
304 close sel_tav_exist;
305 --
306 -- ** TEMP ** Add error message with the following text.
307 --
308 call_error_message( p_error_appl => 'OTA'
309 , p_error_txt => 'OTA_13333_TAD_DEL_TAV_EXISTS'
310 );
311 --
312 end if;
313 --
314 close sel_tav_exist;
315 --
316 hr_utility.set_location(' Leaving:'|| v_proc, 10);
317 --
318 End check_tav_exist;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |-------------------------< check_if_tpm_exist >---------------------------|
322 -- ----------------------------------------------------------------------------
323 --
324 -- PUBLIC
325 -- Description:
326 -- Delete validation
327 -- Check whether any child rows in training_plan_members exist for
328 -- this activity. If they do exist then this activity may not be deleted.
329 --
330 Procedure check_if_tpm_exist
331 (
332 p_activity_id in number
333 ) is
334 --
335 v_proc varchar2(72) := g_package||'check_if_tpm_exist';
336 --
337 -- cursor to check if the activity version is referenced in training plan members
338 --
339 Cursor c_get_tpm_rows is
340 select 'Y'
341 from OTA_TRAINING_PLAN_MEMBERS
342 where activity_definition_id = p_activity_id;
343 --
344 l_dyn_curs integer;
345 l_dyn_rows integer;
346 l_dummy varchar2(1);
347 --
348 Begin
349 --
350 hr_utility.set_location('Entering:'|| v_proc, 5);
351 --
352 -- Check if the activity definition is referenced in training plan members table
353 open c_get_tpm_rows;
354 fetch c_get_tpm_rows into l_dummy;
355 if c_get_tpm_rows%found then
356 close c_get_tpm_rows;
357 call_error_message( p_error_appl => 'OTA'
358 , p_error_txt => 'OTA_13821_TAD_NO_DEL_TPM_EXIST');
359 else
360 close c_get_tpm_rows;
361 end if;
362 --
363 hr_utility.set_location(' Leaving:'|| v_proc, 10);
364 --
365 End check_if_tpm_exist;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |---------------------< check_overlapping_dates >--------------------------|
369 -- ----------------------------------------------------------------------------
370 --
371 -- PRIVATE
372 -- Description:
373 -- Validate whether two datepairs are overlapping.
374 --
375 Function check_overlapping_dates
376 (
377 p_first_start_date in date
378 ,p_first_end_date in date
379 ,p_second_start_date in date
380 ,p_second_end_date in date
381 ) Return boolean Is
382 --
383 v_proc varchar2(72) := g_package||'check_overlapping_dates';
384 --
385 Begin
386 --
387 hr_utility.set_location('Entering:'||v_proc, 5);
388 --
389 -- Check whether the first startdate is between the second startdate and
390 -- the second enddate => Overlapping dates
391 --
392 If ( nvl( p_first_start_date ,hr_api.g_sot)
393 Between nvl( p_second_start_date ,hr_api.g_sot)
394 And nvl( p_second_end_date ,hr_api.g_eot) ) OR
395 --
396 -- Check whether the first enddate is between the second startdate and
397 -- the second enddate => Overlapping dates
398 --
399 ( nvl( p_first_end_date ,hr_api.g_eot)
400 Between nvl( p_second_start_date ,hr_api.g_sot)
401 And nvl( p_second_end_date ,hr_api.g_eot) ) OR
402 --
403 -- Check whether the first datepair includes the second datepair
404 -- => Overlapping dates
405 --
406 (( nvl( p_second_start_date ,hr_api.g_sot)
407 Between nvl( p_first_start_date ,hr_api.g_sot)
408 And nvl( p_first_end_date ,hr_api.g_eot) ) AND
409 ( nvl( p_second_end_date ,hr_api.g_eot)
410 Between nvl( p_first_start_date ,hr_api.g_sot)
411 And nvl( p_first_end_date ,hr_api.g_eot) ) ) OR
412 --
413 -- Check whether the second datepair includes the first datepair
414 -- => Overlapping dates
415 --
416 (( nvl( p_first_start_date ,hr_api.g_sot)
417 Between nvl( p_second_start_date ,hr_api.g_sot)
418 And nvl( p_second_end_date ,hr_api.g_eot) ) AND
419 ( nvl( p_first_end_date ,hr_api.g_eot)
420 Between nvl( p_second_start_date ,hr_api.g_sot)
421 And nvl( p_second_end_date ,hr_api.g_eot) ) ) THEN
422 --
423 -- The pair of dates (first,second) are overlapped
424 --
425 Return( true);
426 --
427 Else
428 --
429 -- The pair of dates (first,second) are NOT overlapped
430 --
431 Return( false);
432 --
433 End if;
434 --
435 hr_utility.set_location(' Leaving:'||v_proc, 10);
436 --
437 End check_overlapping_dates;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |----------------------< exist_multiple_versions >-------------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 -- PUBLIC
444 -- Description:
445 -- Checks whether multiple activity versions exist.
446 --
447 Function exist_multiple_versions
448 (
449 p_activity_id in number
450 ) Return boolean is
451 --
452 v_proc varchar2(72) := g_package||'exist_multiple_versions';
453 --
454 v_version_id ota_activity_versions.activity_version_id%TYPE;
455 v_start_date date;
456 v_end_date date;
457 --
458 v_compared_version_id ota_activity_versions.activity_version_id%TYPE;
459 v_compared_start_date date;
460 v_compared_end_date date;
461 --
462 cursor sel_versions is
463 select tav.activity_version_id
464 , tav.start_date
465 , tav.end_date
466 from ota_activity_versions tav
467 where tav.activity_id = p_activity_id
468 order by activity_version_id;
469 --
470 cursor sel_compared_versions( pcurs_version_id IN NUMBER) is
471 select tav.activity_version_id
472 , tav.start_date
473 , tav.end_date
474 from ota_activity_versions tav
475 where tav.activity_id = p_activity_id
476 and tav.activity_version_id > pcurs_version_id
477 order by activity_version_id;
478 --
479 Begin
480 --
481 hr_utility.set_location('Entering:'|| v_proc, 5);
482 --
483 Open sel_versions;
484 fetch sel_versions into v_version_id
485 , v_start_date
486 , v_end_date ;
487 --
488 -- Each row of the SET 1 is compared with every row of the SET 2.
489 -- SET 2 is equal to SET 1. Both are sorted.
490 -- Startrow of SET 2 is the next row of the selected row of SET 1.
491 --
492 -- first row --> -------- --------
493 -- | | | |<-- second row
494 -- | | | | .
495 -- | SET 1 | == COMPARE ==> | SET 2 | .
496 -- | | | | .
497 -- | | | | .
498 -- -------- -------- <-- last row
499 --
500 Loop
501 --
502 Exit When sel_versions%notfound;
503 --
504 Open sel_compared_versions( v_version_id);
505 fetch sel_compared_versions into v_compared_version_id
506 , v_compared_start_date
507 , v_compared_end_date ;
508 --
509 Loop
510 --
511 Exit When sel_compared_versions%notfound;
512 --
513 If check_overlapping_dates( v_start_date
514 , v_end_date
515 , v_compared_start_date
516 , v_compared_end_date ) Then
517 --
518 -- Multiple activity versions exist
519 --
520 Return( True);
521 --
522 End if;
523 --
524 fetch sel_compared_versions into v_compared_version_id
525 , v_compared_start_date
526 , v_compared_end_date ;
527 --
528 End Loop;
529 --
530 close sel_compared_versions;
531 --
532 fetch sel_versions into v_version_id
533 , v_start_date
534 , v_end_date ;
535 --
536 End Loop;
537 --
538 close sel_versions;
539 --
540 -- Multiple activity versions do not exist
541 --
542 Return( false);
543 --
544 hr_utility.set_location(' Leaving:'|| v_proc, 10);
545 --
546 End exist_multiple_versions;
547 --
548 -- ----------------------------------------------------------------------------
549 -- |--------------------< check_concurrent_versions >-------------------------|
550 -- ----------------------------------------------------------------------------
551 --
552 -- PUBLIC
553 -- Description:
554 -- The MULTIPLE_CON_VERSIONS_FLAG must be set to 'Y' if any concurrent
555 -- Activity Versions exist.
556 --
557 Procedure check_concurrent_versions
558 (
559 p_activity_id in number
560 ,p_flag in varchar2
561 ) is
562 --
563 v_proc varchar2(72) := g_package||'check_concurrent_versions';
564 --
565 Begin
566 --
567 hr_utility.set_location('Entering:'|| v_proc, 5);
568 --
569 If p_flag <> 'Y' AND exist_multiple_versions( p_activity_id) THEN
570 --
571 -- ** TEMP ** Add error message with the following text.
572 --
573 call_error_message( p_error_appl => 'OTA'
574 , p_error_txt => 'OTA_13335_TAD_CON_VERSIONS'
575 );
576 --
577 End if;
578 --
579 hr_utility.set_location(' Leaving:'|| v_proc, 10);
580 --
581 End check_concurrent_versions;
582 --
583 -- ----------------------------------------------------------------------------
584 -- |------------------------------< insert_dml >------------------------------|
585 -- ----------------------------------------------------------------------------
586 -- {Start Of Comments}
587 --
588 -- Description:
589 -- This procedure controls the actual dml insert logic. The functions of this
590 -- procedure are as follows:
591 -- 1. Initialise the object_version_number to 1 if the object_version_number
592 -- is defined as an attribute for this entity.
593 -- 2. To set and unset the g_api_dml status as required (as we are about to
594 -- perform dml).
595 -- 3. To insert the row into the schema.
596 -- 4. To trap any constraint violations that may have occurred.
597 -- 5. To raise any other errors.
598 --
599 -- Pre Conditions:
600 -- This is an internal private procedure which must be called from the ins
601 -- procedure and must have all mandatory arguments set (except the
602 -- object_version_number which is initialised within this procedure).
603 --
604 -- In Arguments:
605 -- A Pl/Sql record structre.
606 --
607 -- Post Success:
608 -- The specified row will be inserted into the schema.
609 --
610 -- Post Failure:
611 -- On the insert dml failure it is important to note that we always reset the
612 -- g_api_dml status to false.
613 -- If a check or parent integrity constraint violation is raised the
614 -- constraint_error procedure will be called.
615 -- If any other error is reported, the error will be raised after the
616 -- g_api_dml status is reset.
617 --
618 -- Developer Implementation Notes:
619 -- None.
620 --
621 -- {End Of Comments}
622 -- ----------------------------------------------------------------------------
623 Procedure insert_dml(p_rec in out nocopy g_rec_type) is
624 --
625 l_proc varchar2(72) := g_package||'insert_dml';
626 --
627 Begin
628 hr_utility.set_location('Entering:'||l_proc, 5);
629 p_rec.object_version_number := 1; -- Initialise the object version
630 --
631 g_api_dml := true; -- Set the api dml status
632 --
633 -- Insert the row into: ota_activity_definitions
634 --
635 insert into ota_activity_definitions
636 ( activity_id,
637 business_group_id,
638 name,
639 comments,
640 description,
641 multiple_con_versions_flag,
642 object_version_number,
643 tad_information_category,
644 tad_information1,
645 tad_information2,
646 tad_information3,
647 tad_information4,
648 tad_information5,
649 tad_information6,
650 tad_information7,
651 tad_information8,
652 tad_information9,
653 tad_information10,
654 tad_information11,
655 tad_information12,
656 tad_information13,
657 tad_information14,
658 tad_information15,
659 tad_information16,
660 tad_information17,
661 tad_information18,
662 tad_information19,
663 tad_information20,
664 category_usage_id
665 )
666 Values
667 ( p_rec.activity_id,
668 p_rec.business_group_id,
669 p_rec.name,
670 p_rec.comments,
671 p_rec.description,
672 p_rec.multiple_con_versions_flag,
673 p_rec.object_version_number,
674 p_rec.tad_information_category,
675 p_rec.tad_information1,
676 p_rec.tad_information2,
677 p_rec.tad_information3,
678 p_rec.tad_information4,
679 p_rec.tad_information5,
680 p_rec.tad_information6,
681 p_rec.tad_information7,
682 p_rec.tad_information8,
683 p_rec.tad_information9,
684 p_rec.tad_information10,
685 p_rec.tad_information11,
686 p_rec.tad_information12,
687 p_rec.tad_information13,
688 p_rec.tad_information14,
689 p_rec.tad_information15,
690 p_rec.tad_information16,
691 p_rec.tad_information17,
692 p_rec.tad_information18,
693 p_rec.tad_information19,
694 p_rec.tad_information20,
695 p_rec.category_usage_id
696 );
697 --
698 g_api_dml := false; -- Unset the api dml status
699 --
700 hr_utility.set_location(' Leaving:'||l_proc, 10);
701 Exception
702 When hr_api.check_integrity_violated Then
703 -- A check constraint has been violated
704 g_api_dml := false; -- Unset the api dml status
705 constraint_error
706 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
707 When hr_api.parent_integrity_violated then
708 -- Parent integrity has been violated
709 g_api_dml := false; -- Unset the api dml status
710 constraint_error
711 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
712 When Others Then
713 g_api_dml := false; -- Unset the api dml status
714 Raise;
715 End insert_dml;
716 --
717 -- ----------------------------------------------------------------------------
718 -- |------------------------------< update_dml >------------------------------|
719 -- ----------------------------------------------------------------------------
720 -- {Start Of Comments}
721 --
722 -- Description:
723 -- This procedure controls the actual dml update logic. The functions of this
724 -- procedure are as follows:
725 -- 1. Increment the object_version_number by 1 if the object_version_number
726 -- is defined as an attribute for this entity.
727 -- 2. To set and unset the g_api_dml status as required (as we are about to
728 -- perform dml).
729 -- 3. To update the specified row in the schema using the primary key in
730 -- the predicates.
731 -- 4. To trap any constraint violations that may have occurred.
732 -- 5. To raise any other errors.
733 --
734 -- Pre Conditions:
735 -- This is an internal private procedure which must be called from the upd
736 -- procedure.
737 --
738 -- In Arguments:
739 -- A Pl/Sql record structre.
740 --
741 -- Post Success:
742 -- The specified row will be updated in the schema.
743 --
744 -- Post Failure:
745 -- On the update dml failure it is important to note that we always reset the
746 -- g_api_dml status to false.
747 -- If a check or parent integrity constraint violation is raised the
748 -- constraint_error procedure will be called.
749 -- If any other error is reported, the error will be raised after the
750 -- g_api_dml status is reset.
751 --
752 -- Developer Implementation Notes:
753 -- The update 'set' arguments list should be modified if any of your
754 -- attributes are not updateable.
755 --
756 -- {End Of Comments}
757 -- ----------------------------------------------------------------------------
758 Procedure update_dml(p_rec in out nocopy g_rec_type) is
759 --
760 l_proc varchar2(72) := g_package||'update_dml';
761 --
762 Begin
763 hr_utility.set_location('Entering:'||l_proc, 5);
764 --
765 -- Increment the object version
766 --
767 p_rec.object_version_number := p_rec.object_version_number + 1;
768 --
769 g_api_dml := true; -- Set the api dml status
770 --
771 -- Update the ota_activity_definitions Row
772 --
773 update ota_activity_definitions
774 set
775 activity_id = p_rec.activity_id,
776 business_group_id = p_rec.business_group_id,
777 name = p_rec.name,
778 comments = p_rec.comments,
779 description = p_rec.description,
780 multiple_con_versions_flag = p_rec.multiple_con_versions_flag,
781 object_version_number = p_rec.object_version_number,
782 tad_information_category = p_rec.tad_information_category,
783 tad_information1 = p_rec.tad_information1,
784 tad_information2 = p_rec.tad_information2,
785 tad_information3 = p_rec.tad_information3,
786 tad_information4 = p_rec.tad_information4,
787 tad_information5 = p_rec.tad_information5,
788 tad_information6 = p_rec.tad_information6,
789 tad_information7 = p_rec.tad_information7,
790 tad_information8 = p_rec.tad_information8,
791 tad_information9 = p_rec.tad_information9,
792 tad_information10 = p_rec.tad_information10,
793 tad_information11 = p_rec.tad_information11,
794 tad_information12 = p_rec.tad_information12,
795 tad_information13 = p_rec.tad_information13,
796 tad_information14 = p_rec.tad_information14,
797 tad_information15 = p_rec.tad_information15,
798 tad_information16 = p_rec.tad_information16,
799 tad_information17 = p_rec.tad_information17,
800 tad_information18 = p_rec.tad_information18,
801 tad_information19 = p_rec.tad_information19,
802 tad_information20 = p_rec.tad_information20,
803 category_usage_id = p_rec.category_usage_id
804 where activity_id = p_rec.activity_id;
805 --
806 g_api_dml := false; -- Unset the api dml status
807 --
808 hr_utility.set_location(' Leaving:'||l_proc, 10);
809 --
810 Exception
811 When hr_api.check_integrity_violated Then
812 -- A check constraint has been violated
813 g_api_dml := false; -- Unset the api dml status
814 constraint_error
815 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
816 When hr_api.parent_integrity_violated then
817 -- Parent integrity has been violated
818 g_api_dml := false; -- Unset the api dml status
819 constraint_error
820 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
821 When Others Then
822 g_api_dml := false; -- Unset the api dml status
823 Raise;
824 End update_dml;
825 --
826 -- ----------------------------------------------------------------------------
827 -- |------------------------------< delete_dml >------------------------------|
828 -- ----------------------------------------------------------------------------
829 -- {Start Of Comments}
830 --
831 -- Description:
832 -- This procedure controls the actual dml delete logic. The functions of this
833 -- procedure are as follows:
834 -- 1. To set and unset the g_api_dml status as required (as we are about to
835 -- perform dml).
836 -- 2. To delete the specified row from the schema using the primary key in
837 -- the predicates.
838 -- 3. To ensure that the row was deleted.
839 -- 4. To trap any constraint violations that may have occurred.
840 -- 5. To raise any other errors.
841 --
842 -- Pre Conditions:
843 -- This is an internal private procedure which must be called from the del
844 -- procedure.
845 --
846 -- In Arguments:
847 -- A Pl/Sql record structre.
848 --
849 -- Post Success:
850 -- The specified row will be delete from the schema.
851 --
852 -- Post Failure:
853 -- On the delete dml failure it is important to note that we always reset the
854 -- g_api_dml status to false.
855 -- If a child integrity constraint violation is raised the
856 -- constraint_error procedure will be called.
857 -- If any other error is reported, the error will be raised after the
858 -- g_api_dml status is reset.
859 --
860 -- Developer Implementation Notes:
861 -- None.
862 --
863 -- {End Of Comments}
864 -- ----------------------------------------------------------------------------
865 Procedure delete_dml(p_rec in g_rec_type) is
866 --
867 l_proc varchar2(72) := g_package||'delete_dml';
868 --
869 Begin
870 hr_utility.set_location('Entering:'||l_proc, 5);
871 --
872 g_api_dml := true; -- Set the api dml status
873 --
874 -- Delete the ota_activity_definitions row.
875 --
876 delete from ota_activity_definitions
877 where activity_id = p_rec.activity_id;
878 --
879 g_api_dml := false; -- Unset the api dml status
880 --
881 If sql%NOTFOUND then
882 --
883 -- The row to be deleted was NOT found therefore a serious
884 -- error has occurred which MUST be reported.
885 --
886 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
887 hr_utility.set_message_token('PROCEDURE', l_proc);
888 hr_utility.set_message_token('STEP','5');
889 hr_utility.raise_error;
890 End If;
891 --
892 hr_utility.set_location(' Leaving:'||l_proc, 10);
893 --
894 Exception
895 When hr_api.child_integrity_violated then
896 -- Child integrity has been violated
897 g_api_dml := false; -- Unset the api dml status
898 constraint_error
899 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
900 When Others Then
901 g_api_dml := false; -- Unset the api dml status
902 Raise;
903 End delete_dml;
904 --
905 -- ----------------------------------------------------------------------------
906 -- |------------------------------< pre_insert >------------------------------|
907 -- ----------------------------------------------------------------------------
908 -- {Start Of Comments}
909 --
910 -- Description:
911 -- This private procedure contains any processing which is required before
912 -- the insert dml. Presently, if the entity has a corresponding primary
913 -- key which is maintained by an associating sequence, the primary key for
914 -- the entity will be populated with the next sequence value in
915 -- preparation for the insert dml.
916 --
917 -- Pre Conditions:
918 -- This is an internal procedure which is called from the ins procedure.
919 --
920 -- In Arguments:
921 -- A Pl/Sql record structre.
922 --
923 -- Post Success:
924 -- Processing continues.
925 --
926 -- Post Failure:
927 -- If an error has occurred, an error message and exception will be raised
928 -- but not handled.
929 --
930 -- Developer Implementation Notes:
931 -- Any pre-processing required before the insert dml is issued should be
932 -- coded within this procedure. As stated above, a good example is the
933 -- generation of a primary key number via a corresponding sequence.
934 -- It is important to note that any 3rd party maintenance should be reviewed
935 -- before placing in this procedure.
936 --
937 -- {End Of Comments}
938 -- ----------------------------------------------------------------------------
939 Procedure pre_insert(p_rec in out nocopy g_rec_type) is
940 --
941 l_proc varchar2(72) := g_package||'pre_insert';
942 --
943 Cursor C_Sel1 is select ota_activity_definitions_s.nextval from sys.dual;
944 --
945 Begin
946 hr_utility.set_location('Entering:'||l_proc, 5);
947 --
948 --
949 -- Select the next sequence number
950 --
951 Open C_Sel1;
952 Fetch C_Sel1 Into p_rec.activity_id;
953 Close C_Sel1;
954 --
955 hr_utility.set_location(' Leaving:'||l_proc, 10);
956 End pre_insert;
957 --
958 -- ----------------------------------------------------------------------------
959 -- |------------------------------< pre_update >------------------------------|
960 -- ----------------------------------------------------------------------------
961 -- {Start Of Comments}
962 --
963 -- Description:
964 -- This private procedure contains any processing which is required before
965 -- the update dml.
966 --
967 -- Pre Conditions:
968 -- This is an internal procedure which is called from the upd procedure.
969 --
970 -- In Arguments:
971 -- A Pl/Sql record structre.
972 --
973 -- Post Success:
974 -- Processing continues.
975 --
976 -- Post Failure:
977 -- If an error has occurred, an error message and exception will be raised
978 -- but not handled.
979 --
980 -- Developer Implementation Notes:
981 -- Any pre-processing required before the update dml is issued should be
982 -- coded within this procedure. It is important to note that any 3rd party
983 -- maintenance should be reviewed before placing in this procedure.
984 --
985 -- {End Of Comments}
986 -- ----------------------------------------------------------------------------
987 Procedure pre_update(p_rec in g_rec_type) is
988 --
989 l_proc varchar2(72) := g_package||'pre_update';
990 --
991 Begin
992 hr_utility.set_location('Entering:'||l_proc, 5);
993 --
994 hr_utility.set_location(' Leaving:'||l_proc, 10);
995 End pre_update;
996 --
997 -- ----------------------------------------------------------------------------
998 -- |------------------------------< pre_delete >------------------------------|
999 -- ----------------------------------------------------------------------------
1000 -- {Start Of Comments}
1001 --
1002 -- Description:
1003 -- This private procedure contains any processing which is required before
1004 -- the delete dml.
1005 --
1006 -- Pre Conditions:
1007 -- This is an internal procedure which is called from the del procedure.
1008 --
1009 -- In Arguments:
1010 -- A Pl/Sql record structre.
1011 --
1012 -- Post Success:
1013 -- Processing continues.
1014 --
1015 -- Post Failure:
1016 -- If an error has occurred, an error message and exception will be raised
1017 -- but not handled.
1018 --
1019 -- Developer Implementation Notes:
1020 -- Any pre-processing required before the delete dml is issued should be
1021 -- coded within this procedure. It is important to note that any 3rd party
1022 -- maintenance should be reviewed before placing in this procedure.
1023 --
1024 -- {End Of Comments}
1025 -- ----------------------------------------------------------------------------
1026 Procedure pre_delete(p_rec in g_rec_type) is
1027 --
1028 l_proc varchar2(72) := g_package||'pre_delete';
1029 --
1030 Begin
1031 hr_utility.set_location('Entering:'||l_proc, 5);
1032 --
1033 hr_utility.set_location(' Leaving:'||l_proc, 10);
1034 End pre_delete;
1035 --
1036 -- ----------------------------------------------------------------------------
1037 -- |-----------------------------< post_insert >------------------------------|
1038 -- ----------------------------------------------------------------------------
1039 -- {Start Of Comments}
1040 --
1041 -- Description:
1042 -- This private procedure contains any processing which is required after the
1043 -- insert dml.
1044 --
1045 -- Pre Conditions:
1046 -- This is an internal procedure which is called from the ins procedure.
1047 --
1048 -- In Arguments:
1049 -- A Pl/Sql record structre.
1050 --
1051 -- Post Success:
1052 -- Processing continues.
1053 --
1054 -- Post Failure:
1055 -- If an error has occurred, an error message and exception will be raised
1056 -- but not handled.
1057 --
1058 -- Developer Implementation Notes:
1059 -- Any post-processing required after the insert dml is issued should be
1060 -- coded within this procedure. It is important to note that any 3rd party
1061 -- maintenance should be reviewed before placing in this procedure.
1062 --
1063 -- {End Of Comments}
1064 -- ----------------------------------------------------------------------------
1065 Procedure post_insert(p_rec in g_rec_type) is
1066 --
1067 l_proc varchar2(72) := g_package||'post_insert';
1068 --
1069 Begin
1070 hr_utility.set_location('Entering:'||l_proc, 5);
1071 --
1072 hr_utility.set_location(' Leaving:'||l_proc, 10);
1073 End post_insert;
1074 --
1075 -- ----------------------------------------------------------------------------
1076 -- |-----------------------------< post_update >------------------------------|
1077 -- ----------------------------------------------------------------------------
1078 -- {Start Of Comments}
1079 --
1080 -- Description:
1081 -- This private procedure contains any processing which is required after the
1082 -- update dml.
1083 --
1084 -- Pre Conditions:
1085 -- This is an internal procedure which is called from the upd procedure.
1086 --
1087 -- In Arguments:
1088 -- A Pl/Sql record structre.
1089 --
1090 -- Post Success:
1091 -- Processing continues.
1092 --
1093 -- Post Failure:
1094 -- If an error has occurred, an error message and exception will be raised
1095 -- but not handled.
1096 --
1097 -- Developer Implementation Notes:
1098 -- Any post-processing required after the update dml is issued should be
1099 -- coded within this procedure. It is important to note that any 3rd party
1100 -- maintenance should be reviewed before placing in this procedure.
1101 --
1102 -- {End Of Comments}
1103 -- ----------------------------------------------------------------------------
1104 Procedure post_update(p_rec in g_rec_type) is
1105 --
1106 l_proc varchar2(72) := g_package||'post_update';
1107 --
1108 Begin
1109 hr_utility.set_location('Entering:'||l_proc, 5);
1110 --
1111 hr_utility.set_location(' Leaving:'||l_proc, 10);
1112 End post_update;
1113 --
1114 -- ----------------------------------------------------------------------------
1115 -- |-----------------------------< post_delete >------------------------------|
1116 -- ----------------------------------------------------------------------------
1117 -- {Start Of Comments}
1118 --
1119 -- Description:
1120 -- This private procedure contains any processing which is required after the
1121 -- delete dml.
1122 --
1123 -- Pre Conditions:
1124 -- This is an internal procedure which is called from the del procedure.
1125 --
1126 -- In Arguments:
1127 -- A Pl/Sql record structre.
1128 --
1129 -- Post Success:
1130 -- Processing continues.
1131 --
1132 -- Post Failure:
1133 -- If an error has occurred, an error message and exception will be raised
1134 -- but not handled.
1135 --
1136 -- Developer Implementation Notes:
1137 -- Any post-processing required after the delete dml is issued should be
1138 -- coded within this procedure. It is important to note that any 3rd party
1139 -- maintenance should be reviewed before placing in this procedure.
1140 --
1141 -- {End Of Comments}
1142 -- ----------------------------------------------------------------------------
1143 Procedure post_delete(p_rec in g_rec_type) is
1144 --
1145 l_proc varchar2(72) := g_package||'post_delete';
1146 --
1147 Begin
1148 hr_utility.set_location('Entering:'||l_proc, 5);
1149 --
1150 hr_utility.set_location(' Leaving:'||l_proc, 10);
1151 End post_delete;
1152 --
1153 -- ----------------------------------------------------------------------------
1154 -- |---------------------------------< lck >----------------------------------|
1155 -- ----------------------------------------------------------------------------
1156 -- {Start Of Comments}
1157 --
1158 -- Description:
1159 -- The Lck process has two main functions to perform. Firstly, the row to be
1160 -- updated or deleted must be locked. The locking of the row will only be
1161 -- successful if the row is not currently locked by another user and the
1162 -- specified object version number match. Secondly, during the locking of
1163 -- the row, the row is selected into the g_old_rec data structure which
1164 -- enables the current row values from the server to be available to the api.
1165 --
1166 -- Pre Conditions:
1167 -- When attempting to call the lock the object version number (if defined)
1168 -- is mandatory.
1169 --
1170 -- In Arguments:
1171 -- The arguments to the Lck process are the primary key(s) which uniquely
1172 -- identify the row and the object version number of row.
1173 --
1174 -- Post Success:
1175 -- On successful completion of the Lck process the row to be updated or
1176 -- deleted will be locked and selected into the global data structure
1177 -- g_old_rec.
1178 --
1179 -- Post Failure:
1180 -- The Lck process can fail for three reasons:
1181 -- 1) When attempting to lock the row the row could already be locked by
1182 -- another user. This will raise the HR_Api.Object_Locked exception.
1183 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
1184 -- This error is trapped and reported using the message name
1185 -- 'HR_7155_OBJECT_INVALID'.
1186 -- 3) The row although existing in the HR Schema has a different object
1187 -- version number than the object version number specified.
1188 -- This error is trapped and reported using the message name
1189 -- 'HR_7155_OBJECT_INVALID'.
1190 --
1191 -- Developer Implementation Notes:
1192 -- For each primary key and the object version number arguments add a
1193 -- call to hr_api.mandatory_arg_error procedure to ensure that these
1194 -- argument values are not null.
1195 --
1196 -- {End Of Comments}
1197 -- ----------------------------------------------------------------------------
1198 Procedure lck
1199 (
1200 p_activity_id in number,
1201 p_object_version_number in number
1202 ) is
1203 --
1204 -- Cursor selects the 'current' row from the HR Schema
1205 --
1206 Cursor C_Sel1 is
1207 select activity_id,
1208 business_group_id,
1209 name,
1210 comments,
1211 description,
1212 multiple_con_versions_flag,
1213 object_version_number,
1214 tad_information_category,
1215 tad_information1,
1216 tad_information2,
1217 tad_information3,
1218 tad_information4,
1219 tad_information5,
1220 tad_information6,
1221 tad_information7,
1222 tad_information8,
1223 tad_information9,
1224 tad_information10,
1225 tad_information11,
1226 tad_information12,
1227 tad_information13,
1228 tad_information14,
1229 tad_information15,
1230 tad_information16,
1231 tad_information17,
1232 tad_information18,
1233 tad_information19,
1234 tad_information20,
1235 category_usage_id
1236 from ota_activity_definitions
1237 where activity_id = p_activity_id
1238 and object_version_number + 0 = p_object_version_number
1239 for update nowait;
1240 --
1241 l_proc varchar2(72) := g_package||'lck';
1242 --
1243 Begin
1244 hr_utility.set_location('Entering:'||l_proc, 5);
1245 --
1246 -- Add any mandatory argument checking here:
1247 -- Example:
1248 -- hr_api.check_mandatory_arg_error
1249 -- (p_api_name => l_proc,
1250 -- p_argument => 'object_version_number',
1251 -- p_argument_value => p_object_version_number);
1252 --
1253 Open C_Sel1;
1254 Fetch C_Sel1 Into g_old_rec;
1255 If C_Sel1%notfound then
1256 --
1257 -- If the row wasn't returned then either:
1258 -- a) The row does NOT exist.
1259 -- b) The row is NOT current (i.e. failure on the object version).
1260 --
1261 Close C_Sel1;
1262 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
1263 hr_utility.set_message_token('TABLE_NAME', 'ota_activity_definitions');
1264 hr_utility.raise_error;
1265 End If;
1266 Close C_Sel1;
1267 --
1268 hr_utility.set_location(' Leaving:'||l_proc, 10);
1269 --
1270 -- We need to trap the ORA LOCK exception
1271 --
1272 Exception
1273 When HR_Api.Object_Locked then
1274 --
1275 -- The object is locked therefore we need to supply a meaningful
1276 -- error message.
1277 --
1278 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
1279 hr_utility.set_message_token('TABLE_NAME', 'ota_activity_definitions');
1280 hr_utility.raise_error;
1281 End lck;
1282 --
1283 -- ----------------------------------------------------------------------------
1284 -- |-----------------------------< convert_args >-----------------------------|
1285 -- ----------------------------------------------------------------------------
1286 -- {Start Of Comments}
1287 --
1288 -- Description:
1289 -- This function is used to turn attribute arguments into the record
1290 -- structure g_rec_type.
1291 --
1292 -- Pre Conditions:
1293 -- This is a private function and can only be called from the ins or upd
1294 -- attribute processes.
1295 --
1296 -- In Arguments:
1297 --
1298 -- Post Success:
1299 -- A returning record structure will be returned.
1300 --
1301 -- Post Failure:
1302 -- No direct error handling is required within this function. Any possible
1303 -- errors within this function will be a PL/SQL value error due to conversion
1304 -- of datatypes or data lengths.
1305 --
1306 -- Developer Implementation Notes:
1307 --
1308 -- {End Of Comments}
1309 -- ----------------------------------------------------------------------------
1310 Function convert_args
1311 (
1312 p_activity_id in number,
1313 p_business_group_id in number,
1314 p_name in varchar2,
1315 p_comments in varchar2,
1316 p_description in varchar2,
1317 p_multiple_con_versions_flag in varchar2,
1318 p_object_version_number in number,
1319 p_tad_information_category in varchar2,
1320 p_tad_information1 in varchar2,
1321 p_tad_information2 in varchar2,
1322 p_tad_information3 in varchar2,
1323 p_tad_information4 in varchar2,
1324 p_tad_information5 in varchar2,
1325 p_tad_information6 in varchar2,
1326 p_tad_information7 in varchar2,
1327 p_tad_information8 in varchar2,
1328 p_tad_information9 in varchar2,
1329 p_tad_information10 in varchar2,
1330 p_tad_information11 in varchar2,
1331 p_tad_information12 in varchar2,
1332 p_tad_information13 in varchar2,
1333 p_tad_information14 in varchar2,
1334 p_tad_information15 in varchar2,
1335 p_tad_information16 in varchar2,
1336 p_tad_information17 in varchar2,
1337 p_tad_information18 in varchar2,
1338 p_tad_information19 in varchar2,
1339 p_tad_information20 in varchar2,
1340 p_category_usage_id in number
1341 )
1342 Return g_rec_type is
1343 --
1344 l_rec g_rec_type;
1345 l_proc varchar2(72) := g_package||'convert_args';
1346 --
1347 Begin
1348 --
1349 hr_utility.set_location('Entering:'||l_proc, 5);
1350 --
1351 -- Convert arguments into local l_rec structure.
1352 --
1353 l_rec.activity_id := p_activity_id;
1354 l_rec.business_group_id := p_business_group_id;
1355 l_rec.name := p_name;
1356 l_rec.comments := p_comments;
1357 l_rec.description := p_description;
1358 l_rec.multiple_con_versions_flag := p_multiple_con_versions_flag;
1359 l_rec.object_version_number := p_object_version_number;
1360 l_rec.tad_information_category := p_tad_information_category;
1361 l_rec.tad_information1 := p_tad_information1;
1362 l_rec.tad_information2 := p_tad_information2;
1363 l_rec.tad_information3 := p_tad_information3;
1364 l_rec.tad_information4 := p_tad_information4;
1365 l_rec.tad_information5 := p_tad_information5;
1366 l_rec.tad_information6 := p_tad_information6;
1367 l_rec.tad_information7 := p_tad_information7;
1368 l_rec.tad_information8 := p_tad_information8;
1369 l_rec.tad_information9 := p_tad_information9;
1370 l_rec.tad_information10 := p_tad_information10;
1371 l_rec.tad_information11 := p_tad_information11;
1372 l_rec.tad_information12 := p_tad_information12;
1373 l_rec.tad_information13 := p_tad_information13;
1374 l_rec.tad_information14 := p_tad_information14;
1375 l_rec.tad_information15 := p_tad_information15;
1376 l_rec.tad_information16 := p_tad_information16;
1377 l_rec.tad_information17 := p_tad_information17;
1378 l_rec.tad_information18 := p_tad_information18;
1379 l_rec.tad_information19 := p_tad_information19;
1380 l_rec.tad_information20 := p_tad_information20;
1381 l_rec.category_usage_id := p_category_usage_id;
1382 --
1383 -- Return the plsql record structure.
1384 --
1385 hr_utility.set_location(' Leaving:'||l_proc, 10);
1386 Return(l_rec);
1387 --
1388 End convert_args;
1389 --
1390 -- ----------------------------------------------------------------------------
1391 -- |-----------------------------< convert_defs >-----------------------------|
1392 -- ----------------------------------------------------------------------------
1393 -- {Start Of Comments}
1394 --
1395 -- Description:
1396 -- The Convert_Defs function has one very important function:
1397 -- It must return the record structure for the row with all system defaulted
1398 -- values converted into its corresponding argument value for update. When
1399 -- we attempt to update a row through the Upd business process , certain
1400 -- arguments can be defaulted which enables flexibility in the calling of
1401 -- the upd process (e.g. only attributes which need to be updated need to be
1402 -- specified). For the upd business process to determine which attributes
1403 -- have NOT been specified we need to check if the argument has a reserved
1404 -- system default value. Therefore, for all attributes which have a
1405 -- corresponding reserved system default mechanism specified we need to
1406 -- check if a system default is being used. If a system default is being
1407 -- used then we convert the defaulted value into its corresponding attribute
1408 -- value held in the g_old_rec data structure.
1409 --
1410 -- Pre Conditions:
1411 -- This private function can only be called from the upd process.
1412 --
1413 -- In Arguments:
1414 -- A Pl/Sql record structre.
1415 --
1416 -- Post Success:
1417 -- The record structure will be returned with all system defaulted argument
1418 -- values converted into its current row attribute value.
1419 --
1420 -- Post Failure:
1421 -- No direct error handling is required within this function. Any possible
1422 -- errors within this function will be a PL/SQL value error due to conversion
1423 -- of datatypes or data lengths.
1424 --
1425 -- Developer Implementation Notes:
1426 --
1427 -- {End Of Comments}
1428 -- ----------------------------------------------------------------------------
1429 Function convert_defs(p_rec in out nocopy g_rec_type)
1430 Return g_rec_type is
1431 --
1432 l_proc varchar2(72) := g_package||'convert_defs';
1433 --
1434 Begin
1435 --
1436 hr_utility.set_location('Entering:'||l_proc, 5);
1437 --
1438 -- We must now examine each argument value in the
1439 -- p_rec plsql record structure
1440 -- to see if a system default is being used. If a system default
1441 -- is being used then we must set to the 'current' argument value.
1442 --
1443 If (p_rec.business_group_id = hr_api.g_number) then
1444 p_rec.business_group_id := g_old_rec.business_group_id;
1445 End If;
1446 If (p_rec.name = hr_api.g_varchar2) then
1447 p_rec.name := g_old_rec.name;
1448 End If;
1449 If (p_rec.comments = hr_api.g_varchar2) then
1450 p_rec.comments := g_old_rec.comments;
1451 End If;
1452 If (p_rec.description = hr_api.g_varchar2) then
1453 p_rec.description := g_old_rec.description;
1454 End If;
1455 If (p_rec.multiple_con_versions_flag = hr_api.g_varchar2) then
1456 p_rec.multiple_con_versions_flag :=
1457 g_old_rec.multiple_con_versions_flag;
1458 End If;
1459 If (p_rec.tad_information_category = hr_api.g_varchar2) then
1460 p_rec.tad_information_category := g_old_rec.tad_information_category;
1461 End If;
1462 If (p_rec.tad_information1 = hr_api.g_varchar2) then
1463 p_rec.tad_information1 := g_old_rec.tad_information1;
1464 End If;
1465 If (p_rec.tad_information2 = hr_api.g_varchar2) then
1466 p_rec.tad_information2 := g_old_rec.tad_information2;
1467 End If;
1468 If (p_rec.tad_information3 = hr_api.g_varchar2) then
1469 p_rec.tad_information3 := g_old_rec.tad_information3;
1470 End If;
1471 If (p_rec.tad_information4 = hr_api.g_varchar2) then
1472 p_rec.tad_information4 := g_old_rec.tad_information4;
1473 End If;
1474 If (p_rec.tad_information5 = hr_api.g_varchar2) then
1475 p_rec.tad_information5 := g_old_rec.tad_information5;
1476 End If;
1477 If (p_rec.tad_information6 = hr_api.g_varchar2) then
1478 p_rec.tad_information6 := g_old_rec.tad_information6;
1479 End If;
1480 If (p_rec.tad_information7 = hr_api.g_varchar2) then
1481 p_rec.tad_information7 := g_old_rec.tad_information7;
1482 End If;
1483 If (p_rec.tad_information8 = hr_api.g_varchar2) then
1484 p_rec.tad_information8 := g_old_rec.tad_information8;
1485 End If;
1486 If (p_rec.tad_information9 = hr_api.g_varchar2) then
1487 p_rec.tad_information9 := g_old_rec.tad_information9;
1488 End If;
1489 If (p_rec.tad_information10 = hr_api.g_varchar2) then
1490 p_rec.tad_information10 := g_old_rec.tad_information10;
1491 End If;
1492 If (p_rec.tad_information11 = hr_api.g_varchar2) then
1493 p_rec.tad_information11 := g_old_rec.tad_information11;
1494 End If;
1495 If (p_rec.tad_information12 = hr_api.g_varchar2) then
1496 p_rec.tad_information12 := g_old_rec.tad_information12;
1497 End If;
1498 If (p_rec.tad_information13 = hr_api.g_varchar2) then
1499 p_rec.tad_information13 := g_old_rec.tad_information13;
1500 End If;
1501 If (p_rec.tad_information14 = hr_api.g_varchar2) then
1502 p_rec.tad_information14 := g_old_rec.tad_information14;
1503 End If;
1504 If (p_rec.tad_information15 = hr_api.g_varchar2) then
1505 p_rec.tad_information15 := g_old_rec.tad_information15;
1506 End If;
1507 If (p_rec.tad_information16 = hr_api.g_varchar2) then
1508 p_rec.tad_information16 := g_old_rec.tad_information16;
1509 End If;
1510 If (p_rec.tad_information17 = hr_api.g_varchar2) then
1511 p_rec.tad_information17 := g_old_rec.tad_information17;
1512 End If;
1513 If (p_rec.tad_information18 = hr_api.g_varchar2) then
1514 p_rec.tad_information18 := g_old_rec.tad_information18;
1515 End If;
1516 If (p_rec.tad_information19 = hr_api.g_varchar2) then
1517 p_rec.tad_information19 := g_old_rec.tad_information19;
1518 End If;
1519 If (p_rec.tad_information20 = hr_api.g_varchar2) then
1520 p_rec.tad_information20 := g_old_rec.tad_information20;
1521 End If;
1522 If (p_rec.category_usage_id = hr_api.g_number) then
1523 p_rec.category_usage_id := g_old_rec.category_usage_id;
1524 End If;
1525 --
1526 -- Return the plsql record structure.
1527 --
1528 hr_utility.set_location(' Leaving:'||l_proc, 10);
1529 Return(p_rec);
1530 --
1531 End convert_defs;
1532 --
1533 -- ----------------------------------------------------------------------------
1534 -- |---------------------------< insert_validate >----------------------------|
1535 -- ----------------------------------------------------------------------------
1536 -- {Start Of Comments}
1537 --
1538 -- Description:
1539 -- This procedure controls the execution of all insert business rules
1540 -- validation.
1541 --
1542 -- Pre Conditions:
1543 -- This private procedure is called from ins procedure.
1544 --
1545 -- In Arguments:
1546 -- A Pl/Sql record structre.
1547 --
1548 -- Post Success:
1549 -- Processing continues.
1550 --
1551 -- Post Failure:
1552 -- If a business rules fails the error will not be handled by this procedure
1553 -- unless explicity coded.
1554 --
1555 -- Developer Implementation Notes:
1556 -- For insert, your business rules should be coded within this procedure and
1557 -- should ideally (unless really necessary) just be straight procedure or
1558 -- function calls. Try and avoid using conditional branching logic.
1559 --
1560 -- {End Of Comments}
1561 -- ----------------------------------------------------------------------------
1562 Procedure insert_validate(p_rec in g_rec_type) is
1563 --
1564 l_proc varchar2(72) := g_package||'insert_validate';
1565 --
1566 Begin
1567 hr_utility.set_location('Entering:'||l_proc, 5);
1568 --
1569 -- Call all supporting business operations
1570 --
1571 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1572 --
1573 check_unique_name( p_rec.name
1574 , p_rec.business_group_id
1575 , p_rec.activity_id );
1576 --
1577 hr_utility.set_location(' Leaving:'||l_proc, 10);
1578 End insert_validate;
1579 --
1580 -- ----------------------------------------------------------------------------
1581 -- |---------------------------< update_validate >----------------------------|
1582 -- ----------------------------------------------------------------------------
1583 -- {Start Of Comments}
1584 --
1585 -- Description:
1586 -- This procedure controls the execution of all update business rules
1587 -- validation.
1588 --
1589 -- Pre Conditions:
1590 -- This private procedure is called from upd procedure.
1591 --
1592 -- In Arguments:
1593 -- A Pl/Sql record structre.
1594 --
1595 -- Post Success:
1596 -- Processing continues.
1597 --
1598 -- Post Failure:
1599 -- If a business rules fails the error will not be handled by this procedure
1600 -- unless explicity coded.
1601 --
1602 -- Developer Implementation Notes:
1603 -- For update, your business rules should be coded within this procedure and
1604 -- should ideally (unless really necessary) just be straight procedure or
1605 -- function calls. Try and avoid using conditional branching logic.
1606 --
1607 -- {End Of Comments}
1608 -- ----------------------------------------------------------------------------
1609 Procedure update_validate(p_rec in g_rec_type) is
1610 --
1611 l_proc varchar2(72) := g_package||'update_validate';
1612 --
1613 l_name_changed boolean
1614 := ota_general.value_changed( g_old_rec.name
1615 , p_rec.name );
1616 --
1617 l_multiple_con_flag_changed boolean
1618 := ota_general.value_changed( g_old_rec.multiple_con_versions_flag
1619 , p_rec.multiple_con_versions_flag );
1620 --
1621 Begin
1622 hr_utility.set_location('Entering:'||l_proc, 5);
1623 --
1624 -- Call all supporting business operations
1625 --
1626 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1627 --
1628 If l_name_changed Then
1629 --
1630 check_unique_name( p_rec.name
1631 , p_rec.business_group_id
1632 , p_rec.activity_id );
1633 --
1634 End if;
1635 --
1636 If l_multiple_con_flag_changed Then
1637 --
1638 check_concurrent_versions( p_rec.activity_id
1639 , p_rec.multiple_con_versions_flag );
1640 --
1641 End if;
1642 --
1643 hr_utility.set_location(' Leaving:'||l_proc, 10);
1644 --
1645 End update_validate;
1646 --
1647 -- ----------------------------------------------------------------------------
1648 -- |---------------------------< delete_validate >----------------------------|
1649 -- ----------------------------------------------------------------------------
1650 -- {Start Of Comments}
1651 --
1652 -- Description:
1653 -- This procedure controls the execution of all delete business rules
1654 -- validation.
1655 --
1656 -- Pre Conditions:
1657 -- This private procedure is called from del procedure.
1658 --
1659 -- In Arguments:
1660 -- A Pl/Sql record structre.
1661 --
1662 -- Post Success:
1663 -- Processing continues.
1664 --
1665 -- Post Failure:
1666 -- If a business rules fails the error will not be handled by this procedure
1667 -- unless explicity coded.
1668 --
1669 -- Developer Implementation Notes:
1670 -- For delete, your business rules should be coded within this procedure and
1671 -- should ideally (unless really necessary) just be straight procedure or
1672 -- function calls. Try and avoid using conditional branching logic.
1673 --
1674 -- {End Of Comments}
1675 -- ----------------------------------------------------------------------------
1676 Procedure delete_validate(p_rec in g_rec_type) is
1677 --
1678 l_proc varchar2(72) := g_package||'delete_validate';
1679 --
1680 Begin
1681 hr_utility.set_location('Entering:'||l_proc, 5);
1682 --
1683 -- Call all supporting business operations
1684
1685 check_if_tpm_exist (p_rec.activity_id);
1686 check_tav_exist ( p_rec.activity_id );
1687 --
1688 hr_utility.set_location(' Leaving:'||l_proc, 10);
1689 End delete_validate;
1690 --
1691 -- ----------------------------------------------------------------------------
1692 -- |---------------------------------< ins >----------------------------------|
1693 -- ----------------------------------------------------------------------------
1694 -- {Start Of Comments}
1695 --
1696 -- Description:
1697 -- This procedure is the record interface for the insert business process
1698 -- for the specified entity. The role of this process is to insert a fully
1699 -- validated row, into the HR schema passing back to the calling process,
1700 -- any system generated values (e.g. primary and object version number
1701 -- attributes). This process is the main backbone of the ins business
1702 -- process. The processing of this procedure is as follows:
1703 -- 1) If the p_validate argument has been set to true then a savepoint is
1704 -- issued.
1705 -- 2) The controlling validation process insert_validate is then executed
1706 -- which will execute all private and public validation business rule
1707 -- processes.
1708 -- 3) The pre_insert business process is then executed which enables any
1709 -- logic to be processed before the insert dml process is executed.
1710 -- 4) The insert_dml process will physical perform the insert dml into the
1711 -- specified entity.
1712 -- 5) The post_insert business process is then executed which enables any
1713 -- logic to be processed after the insert dml process.
1714 -- 6) If the p_validate argument has been set to true an exception is raised
1715 -- which is handled and processed by performing a rollback to the
1716 -- savepoint which was issued at the beginning of the Ins process.
1717 --
1718 -- Pre Conditions:
1719 -- The main arguments to the business process have to be in the record
1720 -- format.
1721 --
1722 -- In Arguments:
1723 -- p_validate
1724 -- Determines if the business process is to be validated. Setting this
1725 -- boolean value to true will invoke the process to be validated. The
1726 -- default is false. The validation is controlled by a savepoint and
1727 -- rollback mechanism. The savepoint is issued at the beginning of the
1728 -- business process and is rollbacked at the end of the business process
1729 -- when all the processing has been completed. The rollback is controlled
1730 -- by raising and handling the exception hr_api.validate_enabled. We use
1731 -- the exception because, by raising the exception with the business
1732 -- process, we can exit successfully without having any of the 'OUT'
1733 -- arguments being set.
1734 --
1735 -- Post Success:
1736 -- A fully validated row will be inserted into the specified entity
1737 -- without being committed. If the p_validate argument has been set to true
1738 -- then all the work will be rolled back.
1739 --
1740 -- Post Failure:
1741 -- If an error has occurred, an error message will be supplied with the work
1742 -- rolled back.
1743 --
1744 -- Developer Implementation Notes:
1745 --
1746 -- {End Of Comments}
1747 -- ----------------------------------------------------------------------------
1748 Procedure ins
1749 (
1750 p_rec in out nocopy g_rec_type,
1751 p_validate in boolean default false
1752 ) is
1753 --
1754 l_proc varchar2(72) := g_package||'ins';
1755 --
1756 Begin
1757 hr_utility.set_location('Entering:'||l_proc, 5);
1758 --
1759 -- Determine if the business process is to be validated.
1760 --
1761 If p_validate then
1762 --
1763 -- Issue the savepoint.
1764 --
1765 SAVEPOINT ins_ota_tad;
1766 End If;
1767 --
1768 -- Call the supporting insert validate operations
1769 --
1770 insert_validate(p_rec);
1771 --
1772 -- Call the supporting pre-insert operation
1773 --
1774 pre_insert(p_rec);
1775 --
1776 -- Insert the row
1777 --
1778 insert_dml(p_rec);
1779 --
1780 -- Call the supporting post-insert operation
1781 --
1782 post_insert(p_rec);
1783 --
1784 -- If we are validating then raise the Validate_Enabled exception
1785 --
1786 If p_validate then
1787 Raise HR_Api.Validate_Enabled;
1788 End If;
1789 --
1790 hr_utility.set_location(' Leaving:'||l_proc, 10);
1791 Exception
1792 When HR_Api.Validate_Enabled Then
1793 --
1794 -- As the Validate_Enabled exception has been raised
1795 -- we must rollback to the savepoint
1796 --
1797 ROLLBACK TO ins_ota_tad;
1798 end ins;
1799 --
1800 -- ----------------------------------------------------------------------------
1801 -- |---------------------------------< ins >----------------------------------|
1802 -- ----------------------------------------------------------------------------
1803 -- {Start Of Comments}
1804 --
1805 -- Description:
1806 -- This procedure is the attribute interface for the insert business
1807 -- process for the specified entity and is the outermost layer. The role
1808 -- of this process is to insert a fully validated row into the HR schema
1809 -- passing back to the calling process, any system generated values
1810 -- (e.g. object version number attributes).The processing of this
1811 -- procedure is as follows:
1812 -- 1) The attributes are converted into a local record structure by
1813 -- calling the convert_defs function.
1814 -- 2) After the conversion has taken place, the corresponding record ins
1815 -- interface business process is executed.
1816 -- 3) OUT arguments are then set to their corresponding record arguments.
1817 --
1818 -- Pre Conditions:
1819 --
1820 -- In Arguments:
1821 -- p_validate
1822 -- Determines if the business process is to be validated. Setting this
1823 -- Boolean value to true will invoke the process to be validated.
1824 -- The default is false.
1825 --
1826 -- Post Success:
1827 -- A fully validated row will be inserted for the specified entity
1828 -- without being committed (or rollbacked depending on the p_validate
1829 -- status).
1830 --
1831 -- Post Failure:
1832 -- If an error has occurred, an error message will be supplied with the work
1833 -- rolled back.
1834 --
1835 -- Developer Implementation Notes:
1836 --
1837 -- {End Of Comments}
1838 -- ----------------------------------------------------------------------------
1839 Procedure ins
1840 (
1841 p_activity_id out nocopy number,
1842 p_business_group_id in number,
1843 p_name in varchar2,
1844 p_comments in varchar2 default null,
1845 p_description in varchar2 default null,
1846 p_multiple_con_versions_flag in varchar2 default null,
1847 p_object_version_number out nocopy number,
1848 p_tad_information_category in varchar2 default null,
1849 p_tad_information1 in varchar2 default null,
1850 p_tad_information2 in varchar2 default null,
1851 p_tad_information3 in varchar2 default null,
1852 p_tad_information4 in varchar2 default null,
1853 p_tad_information5 in varchar2 default null,
1854 p_tad_information6 in varchar2 default null,
1855 p_tad_information7 in varchar2 default null,
1856 p_tad_information8 in varchar2 default null,
1857 p_tad_information9 in varchar2 default null,
1858 p_tad_information10 in varchar2 default null,
1859 p_tad_information11 in varchar2 default null,
1860 p_tad_information12 in varchar2 default null,
1861 p_tad_information13 in varchar2 default null,
1862 p_tad_information14 in varchar2 default null,
1863 p_tad_information15 in varchar2 default null,
1864 p_tad_information16 in varchar2 default null,
1865 p_tad_information17 in varchar2 default null,
1866 p_tad_information18 in varchar2 default null,
1867 p_tad_information19 in varchar2 default null,
1868 p_tad_information20 in varchar2 default null,
1869 p_category_usage_id in number default null,
1870 p_validate in boolean default false
1871 ) is
1872 --
1873 l_rec g_rec_type;
1874 l_proc varchar2(72) := g_package||'ins';
1875 --
1876 Begin
1877 hr_utility.set_location('Entering:'||l_proc, 5);
1878 --
1879 -- Call conversion function to turn arguments into the
1880 -- p_rec structure.
1881 --
1882 l_rec :=
1883 convert_args
1884 (
1885 null,
1886 p_business_group_id,
1887 p_name,
1888 p_comments,
1889 p_description,
1890 p_multiple_con_versions_flag,
1891 null,
1892 p_tad_information_category,
1893 p_tad_information1,
1894 p_tad_information2,
1895 p_tad_information3,
1896 p_tad_information4,
1897 p_tad_information5,
1898 p_tad_information6,
1899 p_tad_information7,
1900 p_tad_information8,
1901 p_tad_information9,
1902 p_tad_information10,
1903 p_tad_information11,
1904 p_tad_information12,
1905 p_tad_information13,
1906 p_tad_information14,
1907 p_tad_information15,
1908 p_tad_information16,
1909 p_tad_information17,
1910 p_tad_information18,
1911 p_tad_information19,
1912 p_tad_information20,
1913 p_category_usage_id
1914 );
1915 --
1916 -- Having converted the arguments into the ota_tad_rec
1917 -- plsql record structure we call the corresponding record business process.
1918 --
1919 ins(l_rec, p_validate);
1920 --
1921 -- As the primary key argument(s)
1922 -- are specified as an OUT's we must set these values.
1923 --
1924 p_activity_id := l_rec.activity_id;
1925 p_object_version_number := l_rec.object_version_number;
1926 --
1927 hr_utility.set_location(' Leaving:'||l_proc, 10);
1928 End ins;
1929 --
1930 -- ----------------------------------------------------------------------------
1931 -- |---------------------------------< upd >----------------------------------|
1932 -- ----------------------------------------------------------------------------
1933 -- {Start Of Comments}
1934 --
1935 -- Description:
1936 -- This procedure is the record interface for the update business
1937 -- process for the specified entity. The role of this process is
1938 -- to update a fully validated row for the HR schema passing back
1939 -- to the calling process, any system generated values (e.g.
1940 -- object version number attribute). This process is the main
1941 -- backbone of the upd business process. The processing of this
1942 -- procedure is as follows:
1943 -- 1) If the p_validate argument has been set to true then a savepoint
1944 -- is issued.
1945 -- 2) The row to be updated is then locked and selected into the record
1946 -- structure g_old_rec.
1947 -- 3) Because on update arguments which are not part of the update do not
1948 -- have to be defaulted, we need to build up the updated row by
1949 -- converting any system defaulted arguments to their corresponding
1950 -- value.
1951 -- 4) The controlling validation process update_validate is then executed
1952 -- which will execute all private and public validation business rule
1953 -- processes.
1954 -- 5) The pre_update business process is then executed which enables any
1955 -- logic to be processed before the update dml process is executed.
1956 -- 6) The update_dml process will physical perform the update dml into the
1957 -- specified entity.
1958 -- 7) The post_update business process is then executed which enables any
1959 -- logic to be processed after the update dml process.
1960 -- 8) If the p_validate argument has been set to true an exception is
1961 -- raised which is handled and processed by performing a rollback to
1962 -- the savepoint which was issued at the beginning of the upd process.
1963 --
1964 -- Pre Conditions:
1965 -- The main arguments to the business process have to be in the record
1966 -- format.
1967 --
1968 -- In Arguments:
1969 -- p_validate
1970 -- Determines if the business process is to be validated. Setting this
1971 -- boolean value to true will invoke the process to be validated. The
1972 -- default is false. The validation is controlled by a savepoint and
1973 -- rollback mechanism. The savepoint is issued at the beginning of the
1974 -- business process and is rollbacked at the end of the business process
1975 -- when all the processing has been completed. The rollback is controlled
1976 -- by raising and handling the exception hr_api.validate_enabled. We use
1977 -- the exception because, by raising the exception with the business
1978 -- process, we can exit successfully without having any of the 'OUT'
1979 -- arguments being set.
1980 --
1981 -- Post Success:
1982 -- The specified row will be fully validated and updated for the specified
1983 -- entity without being committed. If the p_validate argument has been set
1984 -- to true then all the work will be rolled back.
1985 --
1986 -- Post Failure:
1987 -- If an error has occurred, an error message will be supplied with the work
1988 -- rolled back.
1989 --
1990 -- Developer Implementation Notes:
1991 --
1992 -- {End Of Comments}
1993 -- ----------------------------------------------------------------------------
1994 Procedure upd
1995 (
1996 p_rec in out nocopy g_rec_type,
1997 p_validate in boolean default false
1998 ) is
1999 --
2000 l_proc varchar2(72) := g_package||'upd';
2001 --
2002 Begin
2003 hr_utility.set_location('Entering:'||l_proc, 5);
2004 --
2005 -- Determine if the business process is to be validated.
2006 --
2007 If p_validate then
2008 --
2009 -- Issue the savepoint.
2010 --
2011 SAVEPOINT upd_ota_tad;
2012 End If;
2013 --
2014 -- We must lock the row which we need to update.
2015 --
2016 lck
2017 (
2018 p_rec.activity_id,
2019 p_rec.object_version_number
2020 );
2021 --
2022 -- 1. During an update system defaults are used to determine if
2023 -- arguments have been defaulted or not. We must therefore
2024 -- derive the full record structure values to be updated.
2025 --
2026 -- 2. Call the supporting update validate operations.
2027 --
2028 update_validate(convert_defs(p_rec));
2029 --
2030 -- Call the supporting pre-update operation
2031 --
2032 pre_update(p_rec);
2033 --
2034 -- Update the row.
2035 --
2036 update_dml(p_rec);
2037 --
2038 -- Call the supporting post-update operation
2039 --
2040 post_update(p_rec);
2041 --
2042 -- If we are validating then raise the Validate_Enabled exception
2043 --
2044 If p_validate then
2045 Raise HR_Api.Validate_Enabled;
2046 End If;
2047 --
2048 hr_utility.set_location(' Leaving:'||l_proc, 10);
2049 Exception
2050 When HR_Api.Validate_Enabled Then
2051 --
2052 -- As the Validate_Enabled exception has been raised
2053 -- we must rollback to the savepoint
2054 --
2055 ROLLBACK TO upd_ota_tad;
2056 End upd;
2057 --
2058 -- ----------------------------------------------------------------------------
2059 -- |---------------------------------< upd >----------------------------------|
2060 -- ----------------------------------------------------------------------------
2061 -- {Start Of Comments}
2062 --
2063 -- Description:
2064 -- This procedure is the attribute interface for the update business
2065 -- process for the specified entity and is the outermost layer. The role
2066 -- of this process is to update a fully validated row into the HR schema
2067 -- passing back to the calling process, any system generated values
2068 -- (e.g. object version number attributes).The processing of this
2069 -- procedure is as follows:
2070 -- 1) The attributes are converted into a local record structure by
2071 -- calling the convert_defs function.
2072 -- 2) After the conversion has taken place, the corresponding record upd
2073 -- interface business process is executed.
2074 -- 3) OUT arguments are then set to their corresponding record arguments.
2075 --
2076 -- Pre Conditions:
2077 --
2078 -- In Arguments:
2079 -- p_validate
2080 -- Determines if the business process is to be validated. Setting this
2081 -- Boolean value to true will invoke the process to be validated.
2082 -- The default is false.
2083 --
2084 -- Post Success:
2085 -- A fully validated row will be updated for the specified entity
2086 -- without being committed (or rollbacked depending on the p_validate
2087 -- status).
2088 --
2089 -- Post Failure:
2090 -- If an error has occurred, an error message will be supplied with the work
2091 -- rolled back.
2092 --
2093 -- Developer Implementation Notes:
2094 --
2095 -- {End Of Comments}
2096 -- ----------------------------------------------------------------------------
2097 Procedure upd
2098 (
2099 p_activity_id in number,
2100 p_business_group_id in number default hr_api.g_number,
2101 p_name in varchar2 default hr_api.g_varchar2,
2102 p_comments in varchar2 default hr_api.g_varchar2,
2103 p_description in varchar2 default hr_api.g_varchar2,
2104 p_multiple_con_versions_flag in varchar2 default hr_api.g_varchar2,
2105 p_object_version_number in out nocopy number,
2106 p_tad_information_category in varchar2 default hr_api.g_varchar2,
2107 p_tad_information1 in varchar2 default hr_api.g_varchar2,
2108 p_tad_information2 in varchar2 default hr_api.g_varchar2,
2109 p_tad_information3 in varchar2 default hr_api.g_varchar2,
2110 p_tad_information4 in varchar2 default hr_api.g_varchar2,
2111 p_tad_information5 in varchar2 default hr_api.g_varchar2,
2112 p_tad_information6 in varchar2 default hr_api.g_varchar2,
2113 p_tad_information7 in varchar2 default hr_api.g_varchar2,
2114 p_tad_information8 in varchar2 default hr_api.g_varchar2,
2115 p_tad_information9 in varchar2 default hr_api.g_varchar2,
2116 p_tad_information10 in varchar2 default hr_api.g_varchar2,
2117 p_tad_information11 in varchar2 default hr_api.g_varchar2,
2118 p_tad_information12 in varchar2 default hr_api.g_varchar2,
2119 p_tad_information13 in varchar2 default hr_api.g_varchar2,
2120 p_tad_information14 in varchar2 default hr_api.g_varchar2,
2121 p_tad_information15 in varchar2 default hr_api.g_varchar2,
2122 p_tad_information16 in varchar2 default hr_api.g_varchar2,
2123 p_tad_information17 in varchar2 default hr_api.g_varchar2,
2124 p_tad_information18 in varchar2 default hr_api.g_varchar2,
2125 p_tad_information19 in varchar2 default hr_api.g_varchar2,
2126 p_tad_information20 in varchar2 default hr_api.g_varchar2,
2127 p_category_usage_id in number default hr_api.g_number,
2128 p_validate in boolean default false
2129 ) is
2130 --
2131 l_rec g_rec_type;
2132 l_proc varchar2(72) := g_package||'upd';
2133 --
2134 Begin
2135 hr_utility.set_location('Entering:'||l_proc, 5);
2136 --
2137 -- Call conversion function to turn arguments into the
2138 -- l_rec structure.
2139 --
2140 l_rec :=
2141 convert_args
2142 (
2143 p_activity_id,
2144 p_business_group_id,
2145 p_name,
2146 p_comments,
2147 p_description,
2148 p_multiple_con_versions_flag,
2149 p_object_version_number,
2150 p_tad_information_category,
2151 p_tad_information1,
2152 p_tad_information2,
2153 p_tad_information3,
2154 p_tad_information4,
2155 p_tad_information5,
2156 p_tad_information6,
2157 p_tad_information7,
2158 p_tad_information8,
2159 p_tad_information9,
2160 p_tad_information10,
2161 p_tad_information11,
2162 p_tad_information12,
2163 p_tad_information13,
2164 p_tad_information14,
2165 p_tad_information15,
2166 p_tad_information16,
2167 p_tad_information17,
2168 p_tad_information18,
2169 p_tad_information19,
2170 p_tad_information20,
2171 p_category_usage_id
2172 );
2173 --
2174 -- Having converted the arguments into the
2175 -- plsql record structure we call the corresponding record
2176 -- business process.
2177 --
2178 upd(l_rec, p_validate);
2179 p_object_version_number := l_rec.object_version_number;
2180 --
2181 hr_utility.set_location(' Leaving:'||l_proc, 10);
2182 End upd;
2183 --
2184 -- ----------------------------------------------------------------------------
2185 -- |---------------------------------< del >----------------------------------|
2186 -- ----------------------------------------------------------------------------
2187 -- {Start Of Comments}
2188 --
2189 -- Description:
2190 -- This procedure is the record interface for the delete business process
2191 -- for the specified entity. The role of this process is to delete the
2192 -- row from the HR schema. This process is the main backbone of the del
2193 -- business process. The processing of this procedure is as follows:
2194 -- 1) If the p_validate argument has been set to true then a savepoint is
2195 -- issued.
2196 -- 2) The controlling validation process delete_validate is then executed
2197 -- which will execute all private and public validation business rule
2198 -- processes.
2199 -- 3) The pre_delete business process is then executed which enables any
2200 -- logic to be processed before the delete dml process is executed.
2201 -- 4) The delete_dml process will physical perform the delete dml for the
2202 -- specified row.
2203 -- 5) The post_delete business process is then executed which enables any
2204 -- logic to be processed after the delete dml process.
2205 -- 6) If the p_validate argument has been set to true an exception is raised
2206 -- which is handled and processed by performing a rollback to the
2207 -- savepoint which was issued at the beginning of the del process.
2208 --
2209 -- Pre Conditions:
2210 -- The main arguments to the business process have to be in the record
2211 -- format.
2212 --
2213 -- In Arguments:
2214 -- p_validate
2215 -- Determines if the business process is to be validated. Setting this
2216 -- boolean value to true will invoke the process to be validated. The
2217 -- default is false. The validation is controlled by a savepoint and
2218 -- rollback mechanism. The savepoint is issued at the beginning of the
2219 -- business process and is rollbacked at the end of the business process
2220 -- when all the processing has been completed. The rollback is controlled
2221 -- by raising and handling the exception hr_api.validate_enabled. We use
2222 -- the exception because, by raising the exception with the business
2223 -- process, we can exit successfully without having any of the 'OUT'
2224 -- arguments being set.
2225 --
2226 -- Post Success:
2227 -- The specified row will be fully validated and deleted for the specified
2228 -- entity without being committed. If the p_validate argument has been set
2229 -- to true then all the work will be rolled back.
2230 --
2231 -- Post Failure:
2232 -- If an error has occurred, an error message will be supplied with the work
2233 -- rolled back.
2234 --
2235 -- Developer Implementation Notes:
2236 --
2237 -- {End Of Comments}
2238 -- ----------------------------------------------------------------------------
2239 Procedure del
2240 (
2241 p_rec in g_rec_type,
2242 p_validate in boolean default false
2243 ) is
2244 --
2245 l_proc varchar2(72) := g_package||'del';
2246 --
2247 Begin
2248 hr_utility.set_location('Entering:'||l_proc, 5);
2249 --
2250 -- Determine if the business process is to be validated.
2251 --
2252 If p_validate then
2253 --
2254 -- Issue the savepoint.
2255 --
2256 SAVEPOINT del_ota_tad;
2257 End If;
2258 --
2259 -- We must lock the row which we need to delete.
2260 --
2261 lck
2262 (
2263 p_rec.activity_id,
2264 p_rec.object_version_number
2265 );
2266 --
2267 -- Call the supporting delete validate operation
2268 --
2269 delete_validate(p_rec);
2270 --
2271 -- Call the supporting pre-delete operation
2272 --
2273 pre_delete(p_rec);
2274 --
2275 -- Delete the row.
2276 --
2277 delete_dml(p_rec);
2278 --
2279 -- Call the supporting post-delete operation
2280 --
2281 post_delete(p_rec);
2282 --
2283 -- If we are validating then raise the Validate_Enabled exception
2284 --
2285 If p_validate then
2286 Raise HR_Api.Validate_Enabled;
2287 End If;
2288 --
2289 hr_utility.set_location(' Leaving:'||l_proc, 10);
2290 Exception
2291 When HR_Api.Validate_Enabled Then
2292 --
2293 -- As the Validate_Enabled exception has been raised
2294 -- we must rollback to the savepoint
2295 --
2296 ROLLBACK TO del_ota_tad;
2297 End del;
2298 --
2299 -- ----------------------------------------------------------------------------
2300 -- |---------------------------------< del >----------------------------------|
2301 -- ----------------------------------------------------------------------------
2302 -- {Start Of Comments}
2303 --
2304 -- Description:
2305 -- This procedure is the attribute interface for the delete business
2306 -- process for the specified entity and is the outermost layer. The role
2307 -- of this process is to validate and delete the specified row from the
2308 -- HR schema. The processing of this procedure is as follows:
2309 -- 1) The attributes are converted into a local record structure by
2310 -- explicitly coding the attribute arguments into the g_rec_type
2311 -- datatype.
2312 -- 2) After the conversion has taken place, the corresponding record del
2313 -- interface business process is executed.
2314 --
2315 -- Pre Conditions:
2316 --
2317 -- In Arguments:
2318 -- p_validate
2319 -- Determines if the business process is to be validated. Setting this
2320 -- Boolean value to true will invoke the process to be validated.
2321 -- The default is false.
2322 --
2323 -- Post Success:
2324 -- The specified row will be fully validated and deleted for the specified
2325 -- entity without being committed (or rollbacked depending on the
2326 -- p_validate status).
2327 --
2328 -- Post Failure:
2329 -- If an error has occurred, an error message will be supplied with the work
2330 -- rolled back.
2331 --
2332 -- Developer Implementation Notes:
2333 -- The attrbute in arguments should be modified as to the business process
2334 -- requirements.
2335 --
2336 -- {End Of Comments}
2337 -- ----------------------------------------------------------------------------
2338 Procedure del
2339 (
2340 p_activity_id in number,
2341 p_object_version_number in number,
2342 p_validate in boolean default false
2343 ) is
2344 --
2345 l_rec g_rec_type;
2346 l_proc varchar2(72) := g_package||'del';
2347 --
2348 Begin
2349 hr_utility.set_location('Entering:'||l_proc, 5);
2350 --
2351 -- As the delete procedure accepts a plsql record structure we do need to
2352 -- convert the arguments into the record structure.
2353 -- We don't need to call the supplied conversion argument routine as we
2354 -- only need a few attributes.
2355 --
2356 l_rec.activity_id:= p_activity_id;
2357 l_rec.object_version_number := p_object_version_number;
2358 --
2359 -- Having converted the arguments into the ota_tad_rec
2360 -- plsql record structure we must call the corresponding entity
2361 -- business process
2362 --
2363 del(l_rec, p_validate);
2364 --
2365 hr_utility.set_location(' Leaving:'||l_proc, 10);
2366 End del;
2367 --
2368 end ota_tad_api;
[Home] [Help]