1 Package Body VSP_API as
2 /* $Header: otvsp01t.pkb 115.2 99/07/16 00:56:52 porting 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) := ' VSP_API.';
11 --
12 -- Global api dml status
13 --
14 g_api_dml boolean;
15 --
16 -- ----------------------------------------------------------------------------
17 -- -------------------------< CHECK_TAV >--------------------------------------
18 -- ----------------------------------------------------------------------------
19 --
20 -- The Activity Version must exist as a valid foreign key.
21 --
22 procedure CHECK_TAV
23 (
24 P_ACTIVITY_VERSION_ID in number
25 ) is
26 --
27 W_PROC varchar2 (72)
28 := G_PACKAGE || 'CHECK_TAV';
29 --
30 W_OK varchar2 (3);
31 --
32 cursor C1 is
33 select 'YES'
34 from OTA_ACTIVITY_VERSIONS TAV
35 where TAV.ACTIVITY_VERSION_ID = P_ACTIVITY_VERSION_ID;
36 --
37 begin
38 --
39 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROC, '1');
40 --
41 open C1;
42 fetch C1
43 into W_OK;
44 if (C1%notfound) then
45 W_OK := 'NO';
46 end if;
47 close C1;
48 --
49 if (W_OK <> 'YES') then
50 HR_UTILITY.SET_MESSAGE (801, 'HR_6153_ALL_PROCEDURE_FAIL');
51 HR_UTILITY.SET_MESSAGE_TOKEN ('PROCEDURE', W_PROC);
52 HR_UTILITY.SET_MESSAGE_TOKEN ('STEP','1');
53 HR_UTILITY.RAISE_ERROR;
54 end if;
55 --
56 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROC, 1);
57 --
58 end;
59 --
60 -- ----------------------------------------------------------------------------
61 -- -------------------------< CHECK_VEN >--------------------------------------
62 -- ----------------------------------------------------------------------------
63 --
64 -- The Vendor must exist as a valid foreign key.
65 --
66 procedure CHECK_VEN
67 (
68 P_VENDOR_ID in number
69 ) is
70 --
71 W_PROC varchar2 (72)
72 := G_PACKAGE || 'CHECK_VEN';
73 --
74 W_OK varchar2 (3);
75 --
76 cursor C1 is
77 select 'YES'
78 from PO_VENDORS VEN
79 where VEN.VENDOR_ID = P_VENDOR_ID;
80 --
81 begin
82 --
83 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROC, '1');
84 --
85 open C1;
86 fetch C1
87 into W_OK;
88 if (C1%notfound) then
89 W_OK := 'NO';
90 end if;
91 close C1;
92 --
93 if (W_OK <> 'YES') then
94 HR_UTILITY.SET_MESSAGE (801, 'HR_6153_ALL_PROCEDURE_FAIL');
95 HR_UTILITY.SET_MESSAGE_TOKEN ('PROCEDURE', W_PROC);
96 HR_UTILITY.SET_MESSAGE_TOKEN ('STEP','1');
97 HR_UTILITY.RAISE_ERROR;
98 end if;
99 --
100 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROC, 1);
101 --
102 end;
103 --
104 -- ----------------------------------------------------------------------------
105 -- -------------------------< CHECK_UNIQUE_TAV_VEN >---------------------------
106 -- ----------------------------------------------------------------------------
107 --
108 -- The combination of Activity Version ID and Vendor ID must be unique.
109 --
110 procedure CHECK_UNIQUE_TAV_VEN
111 (
112 P_ACTIVITY_VERSION_ID in number,
113 P_VENDOR_ID in number
114 ) is
115 --
116 W_PROC varchar2 (72)
117 := G_PACKAGE || 'CHECK_UNIQUE_TAV_VEN';
118 --
119 W_OK varchar2 (3);
120 --
121 cursor C1 is
122 select 'NO'
123 from OTA_VENDOR_SUPPLIES VSP
124 where VSP.ACTIVITY_VERSION_ID = P_ACTIVITY_VERSION_ID
125 and VSP.VENDOR_ID = P_VENDOR_ID;
126 --
127 begin
128 --
129 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROC, '1');
130 --
131 open C1;
132 fetch C1
133 into W_OK;
134 if (C1%notfound) then
135 W_OK := 'YES';
136 end if;
137 close C1;
138 --
139 if (W_OK <> 'YES') then
140 HR_UTILITY.SET_MESSAGE (801, 'HR_6153_ALL_PROCEDURE_FAIL');
141 HR_UTILITY.SET_MESSAGE_TOKEN ('PROCEDURE', W_PROC);
142 HR_UTILITY.SET_MESSAGE_TOKEN ('STEP','1');
143 HR_UTILITY.RAISE_ERROR;
144 end if;
145 --
146 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROC, 1);
147 --
148 end;
149 --
150 -- ----------------------------------------------------------------------------
151 -- -------------------------< CHECK_SINGLE_PRIMARY >---------------------------
152 -- ----------------------------------------------------------------------------
153 --
154 -- There may only be a single Primary vendor for each Activity Version
155 --
156 procedure CHECK_SINGLE_PRIMARY
157 (
158 P_ACTIVITY_VERSION_ID in number,
159 P_VENDOR_ID in number,
160 P_PRIMARY_SUPPLIER_FLAG in varchar
161 ) is
162 --
163 W_PROC varchar2 (72)
164 := G_PACKAGE || 'CHECK_SINGLE_PRIMARY';
165 --
166 W_OK varchar2 (3);
167 --
168 cursor C1 is
169 select 'NO'
170 from OTA_VENDOR_SUPPLIES VSP
171 where VSP.ACTIVITY_VERSION_ID = P_ACTIVITY_VERSION_ID
172 and VSP.PRIMARY_SUPPLIER_FLAG = P_PRIMARY_SUPPLIER_FLAG
173 and VSP.VENDOR_ID <> P_VENDOR_ID;
174 --
175 begin
176 --
177 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROC, '1');
178 --
179 if (P_PRIMARY_SUPPLIER_FLAG = 'Y') then
180 --
181 open C1;
182 fetch C1
183 into W_OK;
184 if (C1%notfound) then
185 W_OK := 'YES';
186 end if;
187 close C1;
188 --
189 if (W_OK <> 'YES') then
190 HR_UTILITY.SET_MESSAGE (801, 'HR_6153_ALL_PROCEDURE_FAIL');
191 HR_UTILITY.SET_MESSAGE_TOKEN ('PROCEDURE', W_PROC);
192 HR_UTILITY.SET_MESSAGE_TOKEN ('STEP','1');
193 HR_UTILITY.RAISE_ERROR;
194 end if;
195 --
196 end if;
197 --
198 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROC, 1);
199 --
200 end;
201 --
202 -- ----------------------------------------------------------------------------
203 -- -------------------------< CHECK_NON_TRANSFER >-----------------------------
204 -- ----------------------------------------------------------------------------
205 --
206 -- The Vendor and Activity Version are non-updatable on the
207 -- Vendor Supply row. The row must be deleted and re-entered.
208 --
209 procedure CHECK_NON_TRANSFER
210 (
211 P_ACTIVITY_VERSION_ID in number,
212 P_VENDOR_ID in number
213 ) is
214 --
215 W_PROC varchar2 (72)
216 := G_PACKAGE || 'CHECK_NON_TRANSFER';
217 --
218 begin
219 --
220 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROC, '1');
221 --
222 if ( (nvl (P_ACTIVITY_VERSION_ID, G_OLD_REC.ACTIVITY_VERSION_ID)
223 <> G_OLD_REC.ACTIVITY_VERSION_ID)
224 or (nvl (P_VENDOR_ID, G_OLD_REC.VENDOR_ID )
225 <> G_OLD_REC.VENDOR_ID )) then
226 HR_UTILITY.SET_MESSAGE (801, 'HR_6153_ALL_PROCEDURE_FAIL');
227 HR_UTILITY.SET_MESSAGE_TOKEN ('PROCEDURE', W_PROC);
228 HR_UTILITY.SET_MESSAGE_TOKEN ('STEP','1');
229 HR_UTILITY.RAISE_ERROR;
230 end if;
231 --
232 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROC, 1);
233 --
234 end;
235 --
236 -- ----------------------------------------------------------------------------
237 -- |-------------------------< copy_vendor_supply >---------------------------|
238 -- ----------------------------------------------------------------------------
239 --
240 -- PUBLIC
241 -- Description:
242 -- Copies all vendor supply information from a given activity version to
243 -- another activity version.
244 --
245 Procedure copy_vendor_supply
246 (
247 p_activity_version_from in number
248 ,p_activity_version_to in number
249 ) is
250 --
251 l_rec g_rec_type;
252 v_proc varchar2(72) := g_package||'copy_vendor_supply';
253 --
254 cursor sel_vendor_supply is
255 select vsp.vendor_id
256 , vsp.primary_supplier_flag
257 , vsp.comments
258 from ota_vendor_supplies vsp
259 where vsp.activity_version_id = p_activity_version_from;
260 --
261 Begin
262 --
263 hr_utility.set_location('Entering:'|| v_proc, 5);
264 --
265 Open sel_vendor_supply;
266 fetch sel_vendor_supply into l_rec.vendor_id
267 , l_rec.primary_supplier_flag
268 , l_rec.comments ;
269 --
270 Loop
271 --
272 Exit When sel_vendor_supply%notfound;
273 --
274 ins( l_rec.vendor_supply_id
275 , l_rec.vendor_id
276 , p_activity_version_to
277 , l_rec.primary_supplier_flag
278 , l_rec.comments
279 , false );
280 --
281 fetch sel_vendor_supply into l_rec.vendor_id
282 , l_rec.primary_supplier_flag
283 , l_rec.comments ;
284 --
285 End Loop;
286 --
287 close sel_vendor_supply;
288 --
289 hr_utility.set_location(' Leaving:'|| v_proc, 10);
290 --
291 End copy_vendor_supply;
292 --
293 -- ----------------------------------------------------------------------------
294 -- |------------------------< return_api_dml_status >-------------------------|
295 -- ----------------------------------------------------------------------------
296 -- {Start Of Comments}
297 --
298 -- Description:
299 -- This function will return the current g_api_dml private global
300 -- boolean status.
301 -- The g_api_dml status determines if at the time of the function
302 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
303 -- is being issued from within an api.
304 -- If the status is TRUE then a dml statement is being issued from
305 -- within this entity api.
306 -- This function is primarily to support database triggers which
307 -- need to maintain the object_version_number for non-supported
308 -- dml statements (i.e. dml statement issued outside of the api layer).
309 --
310 -- Pre Conditions:
311 -- None.
312 --
313 -- In Arguments:
314 -- None.
315 --
316 -- Post Success:
317 -- Processing continues.
318 -- If the function returns a TRUE value then, dml is being executed from
319 -- within this api.
320 --
321 -- Post Failure:
322 -- None.
323 --
324 -- {End Of Comments}
325 -- ----------------------------------------------------------------------------
326 Function return_api_dml_status Return Boolean Is
327 --
328 l_proc varchar2(72) := g_package||'return_api_dml_status';
329 --
330 Begin
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 --
333 Return (nvl(g_api_dml, false));
334 --
335 hr_utility.set_location(' Leaving:'||l_proc, 10);
336 End return_api_dml_status;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |---------------------------< constraint_error >---------------------------|
340 -- ----------------------------------------------------------------------------
341 -- {Start Of Comments}
342 --
343 -- Description:
344 -- This procedure is called when a constraint has been violated (i.e.
345 -- The exception hr_api.check_integrity_violated,
346 -- hr_api.parent_integrity_violated or hr_api.child_integrity_violated has
347 -- been raised).
348 -- The exceptions can only be raised as follows:
349 -- 1) A check constraint can only be violated during an INSERT or UPDATE
350 -- dml operation.
351 -- 2) A parent integrity constraint can only be violated during an
352 -- INSERT or UPDATE dml operation.
353 -- 3) A child integrity constraint can only be violated during an
354 -- DELETE dml operation.
355 --
356 -- Pre Conditions:
357 -- Either hr_api.check_integrity_violated, hr_api.parent_integrity_violated
358 -- or hr_api.child_integrity_violated has been raised with the subsequent
359 -- stripping of the constraint name from the generated error message text.
360 --
361 -- In Arguments:
362 -- p_constraint_name is in upper format and is just the constraint name
363 -- (e.g. not prefixed by brackets, schema owner etc).
364 --
365 -- Post Success:
366 -- Development dependant.
367 --
368 -- Post Failure:
369 -- Developement dependant.
370 --
371 -- Developer Implementation Notes:
372 -- For each constraint being checked the hr system package failure message
373 -- has been generated as a template only. These system error messages should
374 -- be modified as required (i.e. change the system failure message to a user
375 -- friendly defined error message).
376 --
377 -- {End Of Comments}
378 -- ----------------------------------------------------------------------------
379 Procedure constraint_error
380 (p_constraint_name in varchar2) Is
381 --
382 l_proc varchar2(72) := g_package||'constraint_error';
383 --
384 Begin
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 --
387 If (p_constraint_name = 'OTA_VENDOR_SUPPLIES_FK1') Then
388 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
389 hr_utility.set_message_token('PROCEDURE', l_proc);
390 hr_utility.set_message_token('STEP','5');
391 hr_utility.raise_error;
392 ElsIf (p_constraint_name = 'OTA_VSP_PRIMARY_SUPPLIER_F_CHK') Then
393 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
394 hr_utility.set_message_token('PROCEDURE', l_proc);
395 hr_utility.set_message_token('STEP','10');
396 hr_utility.raise_error;
397 Else
398 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
399 hr_utility.set_message_token('PROCEDURE', l_proc);
400 hr_utility.set_message_token('STEP','15');
401 hr_utility.raise_error;
402 End If;
403 --
404 hr_utility.set_location(' Leaving:'||l_proc, 10);
405 End constraint_error;
406 --
407 -- ----------------------------------------------------------------------------
408 -- |------------------------------< insert_dml >------------------------------|
409 -- ----------------------------------------------------------------------------
410 -- {Start Of Comments}
411 --
412 -- Description:
413 -- This procedure controls the actual dml insert logic. The functions of this
414 -- procedure are as follows:
415 -- 1. Initialise the object_version_number to 1 if the object_version_number
416 -- is defined as an attribute for this entity.
417 -- 2. To set and unset the g_api_dml status as required (as we are about to
418 -- perform dml).
419 -- 3. To insert the row into the schema.
420 -- 4. To trap any constraint violations that may have occurred.
421 -- 5. To raise any other errors.
422 --
423 -- Pre Conditions:
424 -- This is an internal private procedure which must be called from the ins
425 -- procedure and must have all mandatory arguments set (except the
426 -- object_version_number which is initialised within this procedure).
427 --
428 -- In Arguments:
429 -- A Pl/Sql record structre.
430 --
431 -- Post Success:
432 -- The specified row will be inserted into the schema.
433 --
434 -- Post Failure:
435 -- On the insert dml failure it is important to note that we always reset the
436 -- g_api_dml status to false.
437 -- If a check or parent integrity constraint violation is raised the
438 -- constraint_error procedure will be called.
439 -- If any other error is reported, the error will be raised after the
440 -- g_api_dml status is reset.
441 --
442 -- Developer Implementation Notes:
443 -- None.
444 --
445 -- {End Of Comments}
446 -- ----------------------------------------------------------------------------
447 Procedure insert_dml(p_rec in out g_rec_type) is
448 --
449 l_proc varchar2(72) := g_package||'insert_dml';
450 --
451 Begin
452 hr_utility.set_location('Entering:'||l_proc, 5);
453 --
454 --
455 g_api_dml := true; -- Set the api dml status
456 --
457 -- Insert the row into: ota_vendor_supplies
458 --
459 insert into ota_vendor_supplies
460 ( vendor_supply_id,
461 vendor_id,
462 activity_version_id,
463 primary_supplier_flag,
464 comments
465 )
466 Values
467 ( p_rec.vendor_supply_id,
468 p_rec.vendor_id,
469 p_rec.activity_version_id,
470 p_rec.primary_supplier_flag,
471 p_rec.comments
472 );
473 --
474 g_api_dml := false; -- Unset the api dml status
475 --
476 hr_utility.set_location(' Leaving:'||l_proc, 10);
477 Exception
478 When hr_api.check_integrity_violated Then
479 -- A check constraint has been violated
480 g_api_dml := false; -- Unset the api dml status
481 constraint_error
482 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
483 When hr_api.parent_integrity_violated then
484 -- Parent integrity has been violated
485 g_api_dml := false; -- Unset the api dml status
486 constraint_error
487 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
488 When Others Then
489 g_api_dml := false; -- Unset the api dml status
490 Raise;
491 End insert_dml;
492 --
493 -- ----------------------------------------------------------------------------
494 -- |------------------------------< update_dml >------------------------------|
495 -- ----------------------------------------------------------------------------
496 -- {Start Of Comments}
497 --
498 -- Description:
499 -- This procedure controls the actual dml update logic. The functions of this
500 -- procedure are as follows:
501 -- 1. Increment the object_version_number by 1 if the object_version_number
502 -- is defined as an attribute for this entity.
503 -- 2. To set and unset the g_api_dml status as required (as we are about to
504 -- perform dml).
505 -- 3. To update the specified row in the schema using the primary key in
506 -- the predicates.
507 -- 4. To trap any constraint violations that may have occurred.
508 -- 5. To raise any other errors.
509 --
510 -- Pre Conditions:
511 -- This is an internal private procedure which must be called from the upd
512 -- procedure.
513 --
514 -- In Arguments:
515 -- A Pl/Sql record structre.
516 --
517 -- Post Success:
518 -- The specified row will be updated in the schema.
519 --
520 -- Post Failure:
521 -- On the update dml failure it is important to note that we always reset the
522 -- g_api_dml status to false.
523 -- If a check or parent integrity constraint violation is raised the
524 -- constraint_error procedure will be called.
525 -- If any other error is reported, the error will be raised after the
526 -- g_api_dml status is reset.
527 --
528 -- Developer Implementation Notes:
529 -- The update 'set' arguments list should be modified if any of your
530 -- attributes are not updateable.
531 --
532 -- {End Of Comments}
533 -- ----------------------------------------------------------------------------
534 Procedure update_dml(p_rec in out g_rec_type) is
535 --
536 l_proc varchar2(72) := g_package||'update_dml';
537 --
538 Begin
539 hr_utility.set_location('Entering:'||l_proc, 5);
540 --
541 --
542 g_api_dml := true; -- Set the api dml status
543 --
544 -- Update the ota_vendor_supplies Row
545 --
546 update ota_vendor_supplies
547 set
548 vendor_supply_id = p_rec.vendor_supply_id,
549 vendor_id = p_rec.vendor_id,
550 activity_version_id = p_rec.activity_version_id,
551 primary_supplier_flag = p_rec.primary_supplier_flag,
552 comments = p_rec.comments
553 where vendor_supply_id = p_rec.vendor_supply_id;
554 --
555 g_api_dml := false; -- Unset the api dml status
556 --
557 hr_utility.set_location(' Leaving:'||l_proc, 10);
558 --
559 Exception
560 When hr_api.check_integrity_violated Then
561 -- A check constraint has been violated
562 g_api_dml := false; -- Unset the api dml status
563 constraint_error
564 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
565 When hr_api.parent_integrity_violated then
566 -- Parent integrity has been violated
567 g_api_dml := false; -- Unset the api dml status
568 constraint_error
569 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
570 When Others Then
571 g_api_dml := false; -- Unset the api dml status
572 Raise;
573 End update_dml;
574 --
575 -- ----------------------------------------------------------------------------
576 -- |------------------------------< delete_dml >------------------------------|
577 -- ----------------------------------------------------------------------------
578 -- {Start Of Comments}
579 --
580 -- Description:
581 -- This procedure controls the actual dml delete logic. The functions of this
582 -- procedure are as follows:
583 -- 1. To set and unset the g_api_dml status as required (as we are about to
584 -- perform dml).
585 -- 2. To delete the specified row from the schema using the primary key in
586 -- the predicates.
587 -- 3. To ensure that the row was deleted.
588 -- 4. To trap any constraint violations that may have occurred.
589 -- 5. To raise any other errors.
590 --
591 -- Pre Conditions:
592 -- This is an internal private procedure which must be called from the del
593 -- procedure.
594 --
595 -- In Arguments:
596 -- A Pl/Sql record structre.
597 --
598 -- Post Success:
599 -- The specified row will be delete from the schema.
600 --
601 -- Post Failure:
602 -- On the delete dml failure it is important to note that we always reset the
603 -- g_api_dml status to false.
604 -- If a child integrity constraint violation is raised the
605 -- constraint_error procedure will be called.
606 -- If any other error is reported, the error will be raised after the
607 -- g_api_dml status is reset.
608 --
609 -- Developer Implementation Notes:
610 -- None.
611 --
612 -- {End Of Comments}
613 -- ----------------------------------------------------------------------------
614 Procedure delete_dml(p_rec in g_rec_type) is
615 --
616 l_proc varchar2(72) := g_package||'delete_dml';
617 --
618 Begin
619 hr_utility.set_location('Entering:'||l_proc, 5);
620 --
621 g_api_dml := true; -- Set the api dml status
622 --
623 -- Delete the ota_vendor_supplies row.
624 --
625 delete from ota_vendor_supplies
626 where vendor_supply_id = p_rec.vendor_supply_id;
627 --
628 g_api_dml := false; -- Unset the api dml status
629 --
630 If sql%NOTFOUND then
631 --
632 -- The row to be deleted was NOT found therefore a serious
633 -- error has occurred which MUST be reported.
634 --
635 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
636 hr_utility.set_message_token('PROCEDURE', l_proc);
637 hr_utility.set_message_token('STEP','5');
638 hr_utility.raise_error;
639 End If;
640 --
641 hr_utility.set_location(' Leaving:'||l_proc, 10);
642 --
643 Exception
644 When hr_api.child_integrity_violated then
645 -- Child integrity has been violated
646 g_api_dml := false; -- Unset the api dml status
647 constraint_error
648 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
649 When Others Then
650 g_api_dml := false; -- Unset the api dml status
651 Raise;
652 End delete_dml;
653 --
654 -- ----------------------------------------------------------------------------
655 -- |------------------------------< pre_insert >------------------------------|
656 -- ----------------------------------------------------------------------------
657 -- {Start Of Comments}
658 --
659 -- Description:
660 -- This private procedure contains any processing which is required before
661 -- the insert dml. Presently, if the entity has a corresponding primary
662 -- key which is maintained by an associating sequence, the primary key for
663 -- the entity will be populated with the next sequence value in
664 -- preparation for the insert dml.
665 --
666 -- Pre Conditions:
667 -- This is an internal procedure which is called from the ins procedure.
668 --
669 -- In Arguments:
670 -- A Pl/Sql record structre.
671 --
672 -- Post Success:
673 -- Processing continues.
674 --
675 -- Post Failure:
676 -- If an error has occurred, an error message and exception will be raised
677 -- but not handled.
678 --
679 -- Developer Implementation Notes:
680 -- Any pre-processing required before the insert dml is issued should be
681 -- coded within this procedure. As stated above, a good example is the
682 -- generation of a primary key number via a corresponding sequence.
683 -- It is important to note that any 3rd party maintenance should be reviewed
684 -- before placing in this procedure.
685 --
686 -- {End Of Comments}
687 -- ----------------------------------------------------------------------------
688 Procedure pre_insert(p_rec in out g_rec_type) is
689 --
690 l_proc varchar2(72) := g_package||'pre_insert';
691 --
692 Cursor C_Sel1 is select ota_vendor_supplies_s.nextval from sys.dual;
693 --
694 Begin
695 hr_utility.set_location('Entering:'||l_proc, 5);
696 --
697 --
698 -- Select the next sequence number
699 --
700 Open C_Sel1;
701 Fetch C_Sel1 Into p_rec.vendor_supply_id;
702 Close C_Sel1;
703 --
704 hr_utility.set_location(' Leaving:'||l_proc, 10);
705 End pre_insert;
706 --
707 -- ----------------------------------------------------------------------------
708 -- |------------------------------< pre_update >------------------------------|
709 -- ----------------------------------------------------------------------------
710 -- {Start Of Comments}
711 --
712 -- Description:
713 -- This private procedure contains any processing which is required before
714 -- the update dml.
715 --
716 -- Pre Conditions:
717 -- This is an internal procedure which is called from the upd procedure.
718 --
719 -- In Arguments:
720 -- A Pl/Sql record structre.
721 --
722 -- Post Success:
723 -- Processing continues.
724 --
725 -- Post Failure:
726 -- If an error has occurred, an error message and exception will be raised
727 -- but not handled.
728 --
729 -- Developer Implementation Notes:
730 -- Any pre-processing required before the update dml is issued should be
731 -- coded within this procedure. It is important to note that any 3rd party
732 -- maintenance should be reviewed before placing in this procedure.
733 --
734 -- {End Of Comments}
735 -- ----------------------------------------------------------------------------
736 Procedure pre_update(p_rec in g_rec_type) is
737 --
738 l_proc varchar2(72) := g_package||'pre_update';
739 --
740 Begin
741 hr_utility.set_location('Entering:'||l_proc, 5);
742 --
743 hr_utility.set_location(' Leaving:'||l_proc, 10);
744 End pre_update;
745 --
746 -- ----------------------------------------------------------------------------
747 -- |------------------------------< pre_delete >------------------------------|
748 -- ----------------------------------------------------------------------------
749 -- {Start Of Comments}
750 --
751 -- Description:
752 -- This private procedure contains any processing which is required before
753 -- the delete dml.
754 --
755 -- Pre Conditions:
756 -- This is an internal procedure which is called from the del procedure.
757 --
758 -- In Arguments:
759 -- A Pl/Sql record structre.
760 --
761 -- Post Success:
762 -- Processing continues.
763 --
764 -- Post Failure:
765 -- If an error has occurred, an error message and exception will be raised
766 -- but not handled.
767 --
768 -- Developer Implementation Notes:
769 -- Any pre-processing required before the delete dml is issued should be
770 -- coded within this procedure. It is important to note that any 3rd party
771 -- maintenance should be reviewed before placing in this procedure.
772 --
773 -- {End Of Comments}
774 -- ----------------------------------------------------------------------------
775 Procedure pre_delete(p_rec in g_rec_type) is
776 --
777 l_proc varchar2(72) := g_package||'pre_delete';
778 --
779 Begin
780 hr_utility.set_location('Entering:'||l_proc, 5);
781 --
782 hr_utility.set_location(' Leaving:'||l_proc, 10);
783 End pre_delete;
784 --
785 -- ----------------------------------------------------------------------------
786 -- |-----------------------------< post_insert >------------------------------|
787 -- ----------------------------------------------------------------------------
788 -- {Start Of Comments}
789 --
790 -- Description:
791 -- This private procedure contains any processing which is required after the
792 -- insert dml.
793 --
794 -- Pre Conditions:
795 -- This is an internal procedure which is called from the ins procedure.
796 --
797 -- In Arguments:
798 -- A Pl/Sql record structre.
799 --
800 -- Post Success:
801 -- Processing continues.
802 --
803 -- Post Failure:
804 -- If an error has occurred, an error message and exception will be raised
805 -- but not handled.
806 --
807 -- Developer Implementation Notes:
808 -- Any post-processing required after the insert dml is issued should be
809 -- coded within this procedure. It is important to note that any 3rd party
810 -- maintenance should be reviewed before placing in this procedure.
811 --
812 -- {End Of Comments}
813 -- ----------------------------------------------------------------------------
814 Procedure post_insert(p_rec in g_rec_type) is
815 --
816 l_proc varchar2(72) := g_package||'post_insert';
817 --
818 Begin
819 hr_utility.set_location('Entering:'||l_proc, 5);
820 --
821 hr_utility.set_location(' Leaving:'||l_proc, 10);
822 End post_insert;
823 --
824 -- ----------------------------------------------------------------------------
825 -- |-----------------------------< post_update >------------------------------|
826 -- ----------------------------------------------------------------------------
827 -- {Start Of Comments}
828 --
829 -- Description:
830 -- This private procedure contains any processing which is required after the
831 -- update dml.
832 --
833 -- Pre Conditions:
834 -- This is an internal procedure which is called from the upd procedure.
835 --
836 -- In Arguments:
837 -- A Pl/Sql record structre.
838 --
839 -- Post Success:
840 -- Processing continues.
841 --
842 -- Post Failure:
843 -- If an error has occurred, an error message and exception will be raised
844 -- but not handled.
845 --
846 -- Developer Implementation Notes:
847 -- Any post-processing required after the update dml is issued should be
848 -- coded within this procedure. It is important to note that any 3rd party
849 -- maintenance should be reviewed before placing in this procedure.
850 --
851 -- {End Of Comments}
852 -- ----------------------------------------------------------------------------
853 Procedure post_update(p_rec in g_rec_type) is
854 --
855 l_proc varchar2(72) := g_package||'post_update';
856 --
857 Begin
858 hr_utility.set_location('Entering:'||l_proc, 5);
859 --
860 hr_utility.set_location(' Leaving:'||l_proc, 10);
861 End post_update;
862 --
863 -- ----------------------------------------------------------------------------
864 -- |-----------------------------< post_delete >------------------------------|
865 -- ----------------------------------------------------------------------------
866 -- {Start Of Comments}
867 --
868 -- Description:
869 -- This private procedure contains any processing which is required after the
870 -- delete dml.
871 --
872 -- Pre Conditions:
873 -- This is an internal procedure which is called from the del procedure.
874 --
875 -- In Arguments:
876 -- A Pl/Sql record structre.
877 --
878 -- Post Success:
879 -- Processing continues.
880 --
881 -- Post Failure:
882 -- If an error has occurred, an error message and exception will be raised
883 -- but not handled.
884 --
885 -- Developer Implementation Notes:
886 -- Any post-processing required after the delete dml is issued should be
887 -- coded within this procedure. It is important to note that any 3rd party
888 -- maintenance should be reviewed before placing in this procedure.
889 --
890 -- {End Of Comments}
891 -- ----------------------------------------------------------------------------
892 Procedure post_delete(p_rec in g_rec_type) is
893 --
894 l_proc varchar2(72) := g_package||'post_delete';
895 --
896 Begin
897 hr_utility.set_location('Entering:'||l_proc, 5);
898 --
899 hr_utility.set_location(' Leaving:'||l_proc, 10);
900 End post_delete;
901 --
902 -- ----------------------------------------------------------------------------
903 -- |---------------------------------< lck >----------------------------------|
904 -- ----------------------------------------------------------------------------
905 -- {Start Of Comments}
906 --
907 -- Description:
908 -- The Lck process has two main functions to perform. Firstly, the row to be
909 -- updated or deleted must be locked. The locking of the row will only be
910 -- successful if the row is not currently locked by another user and the
911 -- specified object version number match. Secondly, during the locking of
912 -- the row, the row is selected into the g_old_rec data structure which
913 -- enables the current row values from the server to be available to the api.
914 --
915 -- Pre Conditions:
916 -- When attempting to call the lock the object version number (if defined)
917 -- is mandatory.
918 --
919 -- In Arguments:
920 -- The arguments to the Lck process are the primary key(s) which uniquely
921 -- identify the row and the object version number of row.
922 --
923 -- Post Success:
924 -- On successful completion of the Lck process the row to be updated or
925 -- deleted will be locked and selected into the global data structure
926 -- g_old_rec.
927 --
928 -- Post Failure:
929 -- The Lck process can fail for three reasons:
930 -- 1) When attempting to lock the row the row could already be locked by
931 -- another user. This will raise the HR_Api.Object_Locked exception.
932 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
933 -- This error is trapped and reported using the message name
934 -- 'HR_7155_OBJECT_INVALID'.
935 -- 3) The row although existing in the HR Schema has a different object
936 -- version number than the object version number specified.
937 -- This error is trapped and reported using the message name
938 -- 'HR_7155_OBJECT_INVALID'.
939 --
940 -- Developer Implementation Notes:
941 -- For each primary key and the object version number arguments add a
942 -- call to hr_api.mandatory_arg_error procedure to ensure that these
943 -- argument values are not null.
944 --
945 -- {End Of Comments}
946 -- ----------------------------------------------------------------------------
947 Procedure lck
948 (
949 p_vendor_supply_id in number
950 ) is
951 --
952 -- Cursor selects the 'current' row from the HR Schema
953 --
954 Cursor C_Sel1 is
955 select vendor_supply_id,
956 vendor_id,
957 activity_version_id,
958 primary_supplier_flag,
959 comments
960 from ota_vendor_supplies
961 where vendor_supply_id = p_vendor_supply_id
962 for update nowait;
963 --
964 l_proc varchar2(72) := g_package||'lck';
965 --
966 Begin
967 hr_utility.set_location('Entering:'||l_proc, 5);
968 --
969 -- Add any mandatory argument checking here:
970 -- Example:
971 -- hr_api.check_mandatory_arg_error
972 -- (p_api_name => l_proc,
973 -- p_argument => 'object_version_number',
974 -- p_argument_value => p_object_version_number);
975 --
976 Open C_Sel1;
977 Fetch C_Sel1 Into g_old_rec;
978 If C_Sel1%notfound then
979 --
980 -- If the row wasn't returned then either:
981 -- a) The row does NOT exist.
982 -- b) The row is NOT current (i.e. failure on the object version).
983 --
984 Close C_Sel1;
985 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
986 hr_utility.set_message_token('TABLE_NAME', 'ota_vendor_supplies');
987 hr_utility.raise_error;
988 End If;
989 Close C_Sel1;
990 --
991 hr_utility.set_location(' Leaving:'||l_proc, 10);
992 --
993 -- We need to trap the ORA LOCK exception
994 --
995 Exception
996 When HR_Api.Object_Locked then
997 --
998 -- The object is locked therefore we need to supply a meaningful
999 -- error message.
1000 --
1001 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
1002 hr_utility.set_message_token('TABLE_NAME', 'ota_vendor_supplies');
1003 hr_utility.raise_error;
1004 End lck;
1005 --
1006 -- ----------------------------------------------------------------------------
1007 -- |-----------------------------< convert_args >-----------------------------|
1008 -- ----------------------------------------------------------------------------
1009 -- {Start Of Comments}
1010 --
1011 -- Description:
1012 -- This function is used to turn attribute arguments into the record
1013 -- structure g_rec_type.
1014 --
1015 -- Pre Conditions:
1016 -- This is a private function and can only be called from the ins or upd
1017 -- attribute processes.
1018 --
1019 -- In Arguments:
1020 --
1021 -- Post Success:
1022 -- A returning record structure will be returned.
1023 --
1024 -- Post Failure:
1025 -- No direct error handling is required within this function. Any possible
1026 -- errors within this function will be a PL/SQL value error due to conversion
1027 -- of datatypes or data lengths.
1028 --
1029 -- Developer Implementation Notes:
1030 --
1031 -- {End Of Comments}
1032 -- ----------------------------------------------------------------------------
1033 Function convert_args
1034 (
1035 p_vendor_supply_id in number,
1036 p_vendor_id in number,
1037 p_activity_version_id in number,
1038 p_primary_supplier_flag in varchar2,
1039 p_comments in varchar2
1040 )
1041 Return g_rec_type is
1042 --
1043 l_rec g_rec_type;
1044 l_proc varchar2(72) := g_package||'convert_args';
1045 --
1046 Begin
1047 --
1048 hr_utility.set_location('Entering:'||l_proc, 5);
1049 --
1050 -- Convert arguments into local l_rec structure.
1051 --
1052 l_rec.vendor_supply_id := p_vendor_supply_id;
1053 l_rec.vendor_id := p_vendor_id;
1054 l_rec.activity_version_id := p_activity_version_id;
1055 l_rec.primary_supplier_flag := p_primary_supplier_flag;
1056 l_rec.comments := p_comments;
1057 --
1058 -- Return the plsql record structure.
1059 --
1060 hr_utility.set_location(' Leaving:'||l_proc, 10);
1061 Return(l_rec);
1062 --
1063 End convert_args;
1064 --
1065 -- ----------------------------------------------------------------------------
1066 -- |-----------------------------< convert_defs >-----------------------------|
1067 -- ----------------------------------------------------------------------------
1068 -- {Start Of Comments}
1069 --
1070 -- Description:
1071 -- The Convert_Defs function has one very important function:
1072 -- It must return the record structure for the row with all system defaulted
1073 -- values converted into its corresponding argument value for update. When
1074 -- we attempt to update a row through the Upd business process , certain
1075 -- arguments can be defaulted which enables flexibility in the calling of
1076 -- the upd process (e.g. only attributes which need to be updated need to be
1077 -- specified). For the upd business process to determine which attributes
1078 -- have NOT been specified we need to check if the argument has a reserved
1079 -- system default value. Therefore, for all attributes which have a
1080 -- corresponding reserved system default mechanism specified we need to
1081 -- check if a system default is being used. If a system default is being
1082 -- used then we convert the defaulted value into its corresponding attribute
1083 -- value held in the g_old_rec data structure.
1084 --
1085 -- Pre Conditions:
1086 -- This private function can only be called from the upd process.
1087 --
1088 -- In Arguments:
1089 -- A Pl/Sql record structre.
1090 --
1091 -- Post Success:
1092 -- The record structure will be returned with all system defaulted argument
1093 -- values converted into its current row attribute value.
1094 --
1095 -- Post Failure:
1096 -- No direct error handling is required within this function. Any possible
1097 -- errors within this function will be a PL/SQL value error due to conversion
1098 -- of datatypes or data lengths.
1099 --
1100 -- Developer Implementation Notes:
1101 --
1102 -- {End Of Comments}
1103 -- ----------------------------------------------------------------------------
1104 Function convert_defs(p_rec in out g_rec_type)
1105 Return g_rec_type is
1106 --
1107 l_proc varchar2(72) := g_package||'convert_defs';
1108 --
1109 Begin
1110 --
1111 hr_utility.set_location('Entering:'||l_proc, 5);
1112 --
1113 -- We must now examine each argument value in the
1114 -- p_rec plsql record structure
1115 -- to see if a system default is being used. If a system default
1116 -- is being used then we must set to the 'current' argument value.
1117 --
1118 If (p_rec.vendor_id = hr_api.g_number) then
1119 p_rec.vendor_id := g_old_rec.vendor_id;
1120 End If;
1121 If (p_rec.activity_version_id = hr_api.g_number) then
1122 p_rec.activity_version_id := g_old_rec.activity_version_id;
1123 End If;
1124 If (p_rec.primary_supplier_flag = hr_api.g_varchar2) then
1125 p_rec.primary_supplier_flag := g_old_rec.primary_supplier_flag;
1126 End If;
1127 If (p_rec.comments = hr_api.g_varchar2) then
1128 p_rec.comments := g_old_rec.comments;
1129 End If;
1130 --
1131 -- Return the plsql record structure.
1132 --
1133 hr_utility.set_location(' Leaving:'||l_proc, 10);
1134 Return(p_rec);
1135 --
1136 End convert_defs;
1137 --
1138 -- ----------------------------------------------------------------------------
1139 -- |---------------------------< insert_validate >----------------------------|
1140 -- ----------------------------------------------------------------------------
1141 -- {Start Of Comments}
1142 --
1143 -- Description:
1144 -- This procedure controls the execution of all insert business rules
1145 -- validation.
1146 --
1147 -- Pre Conditions:
1148 -- This private procedure is called from ins procedure.
1149 --
1150 -- In Arguments:
1151 -- A Pl/Sql record structre.
1152 --
1153 -- Post Success:
1154 -- Processing continues.
1155 --
1156 -- Post Failure:
1157 -- If a business rules fails the error will not be handled by this procedure
1158 -- unless explicity coded.
1159 --
1160 -- Developer Implementation Notes:
1161 -- For insert, your business rules should be coded within this procedure and
1162 -- should ideally (unless really necessary) just be straight procedure or
1163 -- function calls. Try and avoid using conditional branching logic.
1164 --
1165 -- {End Of Comments}
1166 -- ----------------------------------------------------------------------------
1167 Procedure insert_validate(p_rec in g_rec_type) is
1168 --
1169 l_proc varchar2(72) := g_package||'insert_validate';
1170 --
1171 Begin
1172 hr_utility.set_location('Entering:'||l_proc, 5);
1173 --
1174 -- Call all supporting business operations
1175 --
1176 CHECK_TAV
1177 (
1178 P_ACTIVITY_VERSION_ID => P_REC.ACTIVITY_VERSION_ID
1179 );
1180 --
1181 CHECK_VEN
1182 (
1183 P_VENDOR_ID => P_REC.VENDOR_ID
1184 );
1185 --
1186 CHECK_UNIQUE_TAV_VEN
1187 (
1188 P_ACTIVITY_VERSION_ID => P_REC.ACTIVITY_VERSION_ID,
1189 P_VENDOR_ID => P_REC.VENDOR_ID
1190 );
1191 --
1192 CHECK_SINGLE_PRIMARY
1193 (
1194 P_ACTIVITY_VERSION_ID => P_REC.ACTIVITY_VERSION_ID,
1195 P_VENDOR_ID => P_REC.VENDOR_ID,
1196 P_PRIMARY_SUPPLIER_FLAG => P_REC.PRIMARY_SUPPLIER_FLAG
1197 );
1198 --
1199 hr_utility.set_location(' Leaving:'||l_proc, 10);
1200 End insert_validate;
1201 --
1202 -- ----------------------------------------------------------------------------
1203 -- |---------------------------< update_validate >----------------------------|
1204 -- ----------------------------------------------------------------------------
1205 -- {Start Of Comments}
1206 --
1207 -- Description:
1208 -- This procedure controls the execution of all update business rules
1209 -- validation.
1210 --
1211 -- Pre Conditions:
1212 -- This private procedure is called from upd procedure.
1213 --
1214 -- In Arguments:
1215 -- A Pl/Sql record structre.
1216 --
1217 -- Post Success:
1218 -- Processing continues.
1219 --
1220 -- Post Failure:
1221 -- If a business rules fails the error will not be handled by this procedure
1222 -- unless explicity coded.
1223 --
1224 -- Developer Implementation Notes:
1225 -- For update, your business rules should be coded within this procedure and
1226 -- should ideally (unless really necessary) just be straight procedure or
1227 -- function calls. Try and avoid using conditional branching logic.
1228 --
1229 -- {End Of Comments}
1230 -- ----------------------------------------------------------------------------
1231 Procedure update_validate(p_rec in g_rec_type) is
1232 --
1233 l_proc varchar2(72) := g_package||'update_validate';
1234 --
1235 Begin
1236 hr_utility.set_location('Entering:'||l_proc, 5);
1237 --
1238 -- Call all supporting business operations
1239 --
1240 CHECK_NON_TRANSFER
1241 (
1242 P_ACTIVITY_VERSION_ID => P_REC.ACTIVITY_VERSION_ID,
1243 P_VENDOR_ID => P_REC.VENDOR_ID
1244 );
1245 --
1246 CHECK_SINGLE_PRIMARY
1247 (
1248 P_ACTIVITY_VERSION_ID => P_REC.ACTIVITY_VERSION_ID,
1249 P_VENDOR_ID => P_REC.VENDOR_ID,
1250 P_PRIMARY_SUPPLIER_FLAG => P_REC.PRIMARY_SUPPLIER_FLAG
1251 );
1252 --
1253 hr_utility.set_location(' Leaving:'||l_proc, 10);
1254 End update_validate;
1255 --
1256 -- ----------------------------------------------------------------------------
1257 -- |---------------------------< delete_validate >----------------------------|
1258 -- ----------------------------------------------------------------------------
1259 -- {Start Of Comments}
1260 --
1261 -- Description:
1262 -- This procedure controls the execution of all delete business rules
1263 -- validation.
1264 --
1265 -- Pre Conditions:
1266 -- This private procedure is called from del procedure.
1267 --
1268 -- In Arguments:
1269 -- A Pl/Sql record structre.
1270 --
1271 -- Post Success:
1272 -- Processing continues.
1273 --
1274 -- Post Failure:
1275 -- If a business rules fails the error will not be handled by this procedure
1276 -- unless explicity coded.
1277 --
1278 -- Developer Implementation Notes:
1279 -- For delete, your business rules should be coded within this procedure and
1280 -- should ideally (unless really necessary) just be straight procedure or
1281 -- function calls. Try and avoid using conditional branching logic.
1282 --
1283 -- {End Of Comments}
1284 -- ----------------------------------------------------------------------------
1285 Procedure delete_validate(p_rec in g_rec_type) is
1286 --
1287 l_proc varchar2(72) := g_package||'delete_validate';
1288 --
1289 Begin
1290 hr_utility.set_location('Entering:'||l_proc, 5);
1291 --
1292 -- Call all supporting business operations
1293 --
1294 hr_utility.set_location(' Leaving:'||l_proc, 10);
1295 End delete_validate;
1296 --
1297 -- ----------------------------------------------------------------------------
1298 -- |---------------------------------< ins >----------------------------------|
1299 -- ----------------------------------------------------------------------------
1300 -- {Start Of Comments}
1301 --
1302 -- Description:
1303 -- This procedure is the record interface for the insert business process
1304 -- for the specified entity. The role of this process is to insert a fully
1305 -- validated row, into the HR schema passing back to the calling process,
1306 -- any system generated values (e.g. primary and object version number
1307 -- attributes). This process is the main backbone of the ins business
1308 -- process. The processing of this procedure is as follows:
1309 -- 1) If the p_validate argument has been set to true then a savepoint is
1310 -- issued.
1311 -- 2) The controlling validation process insert_validate is then executed
1312 -- which will execute all private and public validation business rule
1313 -- processes.
1314 -- 3) The pre_insert business process is then executed which enables any
1315 -- logic to be processed before the insert dml process is executed.
1316 -- 4) The insert_dml process will physical perform the insert dml into the
1317 -- specified entity.
1318 -- 5) The post_insert business process is then executed which enables any
1319 -- logic to be processed after the insert dml process.
1320 -- 6) If the p_validate argument has been set to true an exception is raised
1321 -- which is handled and processed by performing a rollback to the
1322 -- savepoint which was issued at the beginning of the Ins process.
1323 --
1324 -- Pre Conditions:
1325 -- The main arguments to the business process have to be in the record
1326 -- format.
1327 --
1328 -- In Arguments:
1329 -- p_validate
1330 -- Determines if the business process is to be validated. Setting this
1331 -- boolean value to true will invoke the process to be validated. The
1332 -- default is false. The validation is controlled by a savepoint and
1333 -- rollback mechanism. The savepoint is issued at the beginning of the
1334 -- business process and is rollbacked at the end of the business process
1335 -- when all the processing has been completed. The rollback is controlled
1336 -- by raising and handling the exception hr_api.validate_enabled. We use
1337 -- the exception because, by raising the exception with the business
1338 -- process, we can exit successfully without having any of the 'OUT'
1339 -- arguments being set.
1340 --
1341 -- Post Success:
1342 -- A fully validated row will be inserted into the specified entity
1343 -- without being committed. If the p_validate argument has been set to true
1344 -- then all the work will be rolled back.
1345 --
1346 -- Post Failure:
1347 -- If an error has occurred, an error message will be supplied with the work
1348 -- rolled back.
1349 --
1350 -- Developer Implementation Notes:
1351 --
1352 -- {End Of Comments}
1353 -- ----------------------------------------------------------------------------
1354 Procedure ins
1355 (
1356 p_rec in out g_rec_type,
1357 p_validate in boolean default false
1358 ) is
1359 --
1360 l_proc varchar2(72) := g_package||'ins';
1361 --
1362 Begin
1363 hr_utility.set_location('Entering:'||l_proc, 5);
1364 --
1365 -- Determine if the business process is to be validated.
1366 --
1367 If p_validate then
1368 --
1369 -- Issue the savepoint.
1370 --
1371 SAVEPOINT ins_vsp;
1372 End If;
1373 --
1374 -- Call the supporting insert validate operations
1375 --
1376 insert_validate(p_rec);
1377 --
1378 -- Call the supporting pre-insert operation
1379 --
1380 pre_insert(p_rec);
1381 --
1382 -- Insert the row
1383 --
1384 insert_dml(p_rec);
1385 --
1386 -- Call the supporting post-insert operation
1387 --
1388 post_insert(p_rec);
1389 --
1390 -- If we are validating then raise the Validate_Enabled exception
1391 --
1392 If p_validate then
1393 Raise HR_Api.Validate_Enabled;
1394 End If;
1395 --
1396 hr_utility.set_location(' Leaving:'||l_proc, 10);
1397 Exception
1398 When HR_Api.Validate_Enabled Then
1399 --
1400 -- As the Validate_Enabled exception has been raised
1401 -- we must rollback to the savepoint
1402 --
1403 ROLLBACK TO ins_vsp;
1404 end ins;
1405 --
1406 -- ----------------------------------------------------------------------------
1407 -- |---------------------------------< ins >----------------------------------|
1408 -- ----------------------------------------------------------------------------
1409 -- {Start Of Comments}
1410 --
1411 -- Description:
1412 -- This procedure is the attribute interface for the insert business
1413 -- process for the specified entity and is the outermost layer. The role
1414 -- of this process is to insert a fully validated row into the HR schema
1415 -- passing back to the calling process, any system generated values
1416 -- (e.g. object version number attributes).The processing of this
1417 -- procedure is as follows:
1418 -- 1) The attributes are converted into a local record structure by
1419 -- calling the convert_defs function.
1420 -- 2) After the conversion has taken place, the corresponding record ins
1421 -- interface business process is executed.
1422 -- 3) OUT arguments are then set to their corresponding record arguments.
1423 --
1424 -- Pre Conditions:
1425 --
1426 -- In Arguments:
1427 -- p_validate
1428 -- Determines if the business process is to be validated. Setting this
1429 -- Boolean value to true will invoke the process to be validated.
1430 -- The default is false.
1431 --
1432 -- Post Success:
1433 -- A fully validated row will be inserted for the specified entity
1434 -- without being committed (or rollbacked depending on the p_validate
1435 -- status).
1436 --
1437 -- Post Failure:
1438 -- If an error has occurred, an error message will be supplied with the work
1439 -- rolled back.
1440 --
1441 -- Developer Implementation Notes:
1442 --
1443 -- {End Of Comments}
1444 -- ----------------------------------------------------------------------------
1445 Procedure ins
1446 (
1447 p_vendor_supply_id out number,
1448 p_vendor_id in number,
1449 p_activity_version_id in number,
1450 p_primary_supplier_flag in varchar2,
1451 p_comments in varchar2 default null,
1452 p_validate in boolean default false
1453 ) is
1454 --
1455 l_rec g_rec_type;
1456 l_proc varchar2(72) := g_package||'ins';
1457 --
1458 Begin
1459 hr_utility.set_location('Entering:'||l_proc, 5);
1460 --
1461 -- Call conversion function to turn arguments into the
1462 -- p_rec structure.
1463 --
1464 l_rec :=
1465 convert_args
1466 (
1467 null,
1468 p_vendor_id,
1469 p_activity_version_id,
1470 p_primary_supplier_flag,
1471 p_comments
1472 );
1473 --
1474 -- Having converted the arguments into the vsp_rec
1475 -- plsql record structure we call the corresponding record business process.
1476 --
1477 ins(l_rec, p_validate);
1478 --
1479 -- As the primary key argument(s)
1480 -- are specified as an OUT's we must set these values.
1481 --
1482 p_vendor_supply_id := l_rec.vendor_supply_id;
1483 --
1484 --
1485 hr_utility.set_location(' Leaving:'||l_proc, 10);
1486 End ins;
1487 --
1488 -- ----------------------------------------------------------------------------
1489 -- |---------------------------------< upd >----------------------------------|
1490 -- ----------------------------------------------------------------------------
1491 -- {Start Of Comments}
1492 --
1493 -- Description:
1494 -- This procedure is the record interface for the update business
1495 -- process for the specified entity. The role of this process is
1496 -- to update a fully validated row for the HR schema passing back
1497 -- to the calling process, any system generated values (e.g.
1498 -- object version number attribute). This process is the main
1499 -- backbone of the upd business process. The processing of this
1500 -- procedure is as follows:
1501 -- 1) If the p_validate argument has been set to true then a savepoint
1502 -- is issued.
1503 -- 2) The row to be updated is then locked and selected into the record
1504 -- structure g_old_rec.
1505 -- 3) Because on update arguments which are not part of the update do not
1506 -- have to be defaulted, we need to build up the updated row by
1507 -- converting any system defaulted arguments to their corresponding
1508 -- value.
1509 -- 4) The controlling validation process update_validate is then executed
1510 -- which will execute all private and public validation business rule
1511 -- processes.
1512 -- 5) The pre_update business process is then executed which enables any
1513 -- logic to be processed before the update dml process is executed.
1514 -- 6) The update_dml process will physical perform the update dml into the
1515 -- specified entity.
1516 -- 7) The post_update business process is then executed which enables any
1517 -- logic to be processed after the update dml process.
1518 -- 8) If the p_validate argument has been set to true an exception is
1519 -- raised which is handled and processed by performing a rollback to
1520 -- the savepoint which was issued at the beginning of the upd process.
1521 --
1522 -- Pre Conditions:
1523 -- The main arguments to the business process have to be in the record
1524 -- format.
1525 --
1526 -- In Arguments:
1527 -- p_validate
1528 -- Determines if the business process is to be validated. Setting this
1529 -- boolean value to true will invoke the process to be validated. The
1530 -- default is false. The validation is controlled by a savepoint and
1531 -- rollback mechanism. The savepoint is issued at the beginning of the
1532 -- business process and is rollbacked at the end of the business process
1533 -- when all the processing has been completed. The rollback is controlled
1534 -- by raising and handling the exception hr_api.validate_enabled. We use
1535 -- the exception because, by raising the exception with the business
1536 -- process, we can exit successfully without having any of the 'OUT'
1537 -- arguments being set.
1538 --
1539 -- Post Success:
1540 -- The specified row will be fully validated and updated for the specified
1541 -- entity without being committed. If the p_validate argument has been set
1542 -- to true then all the work will be rolled back.
1543 --
1544 -- Post Failure:
1545 -- If an error has occurred, an error message will be supplied with the work
1546 -- rolled back.
1547 --
1548 -- Developer Implementation Notes:
1549 --
1550 -- {End Of Comments}
1551 -- ----------------------------------------------------------------------------
1552 Procedure upd
1553 (
1554 p_rec in out g_rec_type,
1555 p_validate in boolean default false
1556 ) is
1557 --
1558 l_proc varchar2(72) := g_package||'upd';
1559 --
1560 Begin
1561 hr_utility.set_location('Entering:'||l_proc, 5);
1562 --
1563 -- Determine if the business process is to be validated.
1564 --
1565 If p_validate then
1566 --
1567 -- Issue the savepoint.
1568 --
1569 SAVEPOINT upd_vsp;
1570 End If;
1571 --
1572 -- We must lock the row which we need to update.
1573 --
1574 lck
1575 (
1576 p_rec.vendor_supply_id
1577 );
1578 --
1579 -- 1. During an update system defaults are used to determine if
1580 -- arguments have been defaulted or not. We must therefore
1581 -- derive the full record structure values to be updated.
1582 --
1583 -- 2. Call the supporting update validate operations.
1584 --
1585 update_validate(convert_defs(p_rec));
1586 --
1587 -- Call the supporting pre-update operation
1588 --
1589 pre_update(p_rec);
1590 --
1591 -- Update the row.
1592 --
1593 update_dml(p_rec);
1594 --
1595 -- Call the supporting post-update operation
1596 --
1597 post_update(p_rec);
1598 --
1599 -- If we are validating then raise the Validate_Enabled exception
1600 --
1601 If p_validate then
1602 Raise HR_Api.Validate_Enabled;
1603 End If;
1604 --
1605 hr_utility.set_location(' Leaving:'||l_proc, 10);
1606 Exception
1607 When HR_Api.Validate_Enabled Then
1608 --
1609 -- As the Validate_Enabled exception has been raised
1610 -- we must rollback to the savepoint
1611 --
1612 ROLLBACK TO upd_vsp;
1613 End upd;
1614 --
1615 -- ----------------------------------------------------------------------------
1616 -- |---------------------------------< upd >----------------------------------|
1617 -- ----------------------------------------------------------------------------
1618 -- {Start Of Comments}
1619 --
1620 -- Description:
1621 -- This procedure is the attribute interface for the update business
1622 -- process for the specified entity and is the outermost layer. The role
1623 -- of this process is to update a fully validated row into the HR schema
1624 -- passing back to the calling process, any system generated values
1625 -- (e.g. object version number attributes).The processing of this
1626 -- procedure is as follows:
1627 -- 1) The attributes are converted into a local record structure by
1628 -- calling the convert_defs function.
1629 -- 2) After the conversion has taken place, the corresponding record upd
1630 -- interface business process is executed.
1631 -- 3) OUT arguments are then set to their corresponding record arguments.
1632 --
1633 -- Pre Conditions:
1634 --
1635 -- In Arguments:
1636 -- p_validate
1637 -- Determines if the business process is to be validated. Setting this
1638 -- Boolean value to true will invoke the process to be validated.
1639 -- The default is false.
1640 --
1641 -- Post Success:
1642 -- A fully validated row will be updated for the specified entity
1643 -- without being committed (or rollbacked depending on the p_validate
1644 -- status).
1645 --
1646 -- Post Failure:
1647 -- If an error has occurred, an error message will be supplied with the work
1648 -- rolled back.
1649 --
1650 -- Developer Implementation Notes:
1651 --
1652 -- {End Of Comments}
1653 -- ----------------------------------------------------------------------------
1654 Procedure upd
1655 (
1656 p_vendor_supply_id in number,
1657 p_vendor_id in number default hr_api.g_number,
1658 p_activity_version_id in number default hr_api.g_number,
1659 p_primary_supplier_flag in varchar2 default hr_api.g_varchar2,
1660 p_comments in varchar2 default hr_api.g_varchar2,
1661 p_validate in boolean default false
1662 ) is
1663 --
1664 l_rec g_rec_type;
1665 l_proc varchar2(72) := g_package||'upd';
1666 --
1667 Begin
1668 hr_utility.set_location('Entering:'||l_proc, 5);
1669 --
1670 -- Call conversion function to turn arguments into the
1671 -- l_rec structure.
1672 --
1673 l_rec :=
1674 convert_args
1675 (
1676 p_vendor_supply_id,
1677 p_vendor_id,
1678 p_activity_version_id,
1679 p_primary_supplier_flag,
1680 p_comments
1681 );
1682 --
1683 -- Having converted the arguments into the
1684 -- plsql record structure we call the corresponding record
1685 -- business process.
1686 --
1687 upd(l_rec, p_validate);
1688 --
1689 --
1690 hr_utility.set_location(' Leaving:'||l_proc, 10);
1691 End upd;
1692 --
1693 -- ----------------------------------------------------------------------------
1694 -- |---------------------------------< del >----------------------------------|
1695 -- ----------------------------------------------------------------------------
1696 -- {Start Of Comments}
1697 --
1698 -- Description:
1699 -- This procedure is the record interface for the delete business process
1700 -- for the specified entity. The role of this process is to delete the
1701 -- row from the HR schema. This process is the main backbone of the del
1702 -- business 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 delete_validate is then executed
1706 -- which will execute all private and public validation business rule
1707 -- processes.
1708 -- 3) The pre_delete business process is then executed which enables any
1709 -- logic to be processed before the delete dml process is executed.
1710 -- 4) The delete_dml process will physical perform the delete dml for the
1711 -- specified row.
1712 -- 5) The post_delete business process is then executed which enables any
1713 -- logic to be processed after the delete 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 del 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 -- The specified row will be fully validated and deleted for the specified
1737 -- entity without being committed. If the p_validate argument has been set
1738 -- to true 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 del
1749 (
1750 p_rec in g_rec_type,
1751 p_validate in boolean default false
1752 ) is
1753 --
1754 l_proc varchar2(72) := g_package||'del';
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 del_vsp;
1766 End If;
1767 --
1768 -- We must lock the row which we need to delete.
1769 --
1770 lck
1771 (
1772 p_rec.vendor_supply_id
1773 );
1774 --
1775 -- Call the supporting delete validate operation
1776 --
1777 delete_validate(p_rec);
1778 --
1779 -- Call the supporting pre-delete operation
1780 --
1781 pre_delete(p_rec);
1782 --
1783 -- Delete the row.
1784 --
1785 delete_dml(p_rec);
1786 --
1787 -- Call the supporting post-delete operation
1788 --
1789 post_delete(p_rec);
1790 --
1791 -- If we are validating then raise the Validate_Enabled exception
1792 --
1793 If p_validate then
1794 Raise HR_Api.Validate_Enabled;
1795 End If;
1796 --
1797 hr_utility.set_location(' Leaving:'||l_proc, 10);
1798 Exception
1799 When HR_Api.Validate_Enabled Then
1800 --
1801 -- As the Validate_Enabled exception has been raised
1802 -- we must rollback to the savepoint
1803 --
1804 ROLLBACK TO del_vsp;
1805 End del;
1806 --
1807 -- ----------------------------------------------------------------------------
1808 -- |---------------------------------< del >----------------------------------|
1809 -- ----------------------------------------------------------------------------
1810 -- {Start Of Comments}
1811 --
1812 -- Description:
1813 -- This procedure is the attribute interface for the delete business
1814 -- process for the specified entity and is the outermost layer. The role
1815 -- of this process is to validate and delete the specified row from the
1816 -- HR schema. The processing of this procedure is as follows:
1817 -- 1) The attributes are converted into a local record structure by
1818 -- explicitly coding the attribute arguments into the g_rec_type
1819 -- datatype.
1820 -- 2) After the conversion has taken place, the corresponding record del
1821 -- interface business process is executed.
1822 --
1823 -- Pre Conditions:
1824 --
1825 -- In Arguments:
1826 -- p_validate
1827 -- Determines if the business process is to be validated. Setting this
1828 -- Boolean value to true will invoke the process to be validated.
1829 -- The default is false.
1830 --
1831 -- Post Success:
1832 -- The specified row will be fully validated and deleted for the specified
1833 -- entity without being committed (or rollbacked depending on the
1834 -- p_validate status).
1835 --
1836 -- Post Failure:
1837 -- If an error has occurred, an error message will be supplied with the work
1838 -- rolled back.
1839 --
1840 -- Developer Implementation Notes:
1841 -- The attrbute in arguments should be modified as to the business process
1842 -- requirements.
1843 --
1844 -- {End Of Comments}
1845 -- ----------------------------------------------------------------------------
1846 Procedure del
1847 (
1848 p_vendor_supply_id in number,
1849 p_validate in boolean default false
1850 ) is
1851 --
1852 l_rec g_rec_type;
1853 l_proc varchar2(72) := g_package||'del';
1854 --
1855 Begin
1856 hr_utility.set_location('Entering:'||l_proc, 5);
1857 --
1858 -- As the delete procedure accepts a plsql record structure we do need to
1859 -- convert the arguments into the record structure.
1860 -- We don't need to call the supplied conversion argument routine as we
1861 -- only need a few attributes.
1862 --
1863 l_rec.vendor_supply_id:= p_vendor_supply_id;
1864 --
1865 --
1866 -- Having converted the arguments into the vsp_rec
1867 -- plsql record structure we must call the corresponding entity
1868 -- business process
1869 --
1870 del(l_rec, p_validate);
1871 --
1872 hr_utility.set_location(' Leaving:'||l_proc, 10);
1873 End del;
1874 --
1875 end VSP_API;
[Home] [Help]