[Home] [Help]
PACKAGE BODY: APPS.BEN_CSO_INS
Source
1 Package Body ben_cso_ins as
2 /* $Header: becsorhi.pkb 115.0 2003/03/17 13:37:07 csundar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cso_ins.'; -- Global package name
9 --
10 -- The following global variables are only to be used by
11 -- the set_base_key_value and pre_insert procedures.
12 --
13 g_cwb_stock_optn_dtls_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_cwb_stock_optn_dtls_id in number) is
20 --
21 l_proc varchar2(72) := g_package||'set_base_key_value';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 ben_cso_ins.g_cwb_stock_optn_dtls_id_i := p_cwb_stock_optn_dtls_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |------------------------------< insert_dml >------------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic. The processing of
39 -- this procedure are as follows:
40 -- 1) Initialise the object_version_number to 1 if the object_version_number
41 -- is defined as an attribute for this entity.
42 -- 2) To set and unset the g_api_dml status as required (as we are about to
43 -- perform dml).
44 -- 3) To insert the row into the schema.
45 -- 4) To trap any constraint violations that may have occurred.
46 -- 5) To raise any other errors.
47 --
48 -- Prerequisites:
49 -- This is an internal private procedure which must be called from the ins
50 -- procedure and must have all mandatory attributes set (except the
51 -- object_version_number which is initialised within this procedure).
52 --
53 -- In Parameters:
54 -- A Pl/Sql record structre.
55 --
56 -- Post Success:
57 -- The specified row will be inserted into the schema.
58 --
59 -- Post Failure:
60 -- On the insert dml failure it is important to note that we always reset the
61 -- g_api_dml status to false.
62 -- If a check, unique or parent integrity constraint violation is raised the
63 -- constraint_error procedure will be called.
64 -- If any other error is reported, the error will be raised after the
65 -- g_api_dml status is reset.
66 --
67 -- Developer Implementation Notes:
68 -- None.
69 --
70 -- Access Status:
71 -- Internal Row Handler Use Only.
72 --
73 -- {End Of Comments}
74 -- ----------------------------------------------------------------------------
75 Procedure insert_dml
76 (p_rec in out nocopy ben_cso_shd.g_rec_type
77 ) is
78 --
79 l_proc varchar2(72) := g_package||'insert_dml';
80 --
81 Begin
82 hr_utility.set_location('Entering:'||l_proc, 5);
83 p_rec.object_version_number := 1; -- Initialise the object version
84 --
85 --
86 --
87 -- Insert the row into: ben_cwb_stock_optn_dtls
88 --
89 insert into ben_cwb_stock_optn_dtls
90 (cwb_stock_optn_dtls_id
91 ,grant_id
92 ,grant_number
93 ,grant_name
94 ,grant_type
95 ,grant_date
96 ,grant_shares
97 ,grant_price
98 ,value_at_grant
99 ,current_share_price
100 ,current_shares_outstanding
101 ,vested_shares
102 ,unvested_shares
103 ,exercisable_shares
104 ,exercised_shares
105 ,cancelled_shares
106 ,trading_symbol
107 ,expiration_date
108 ,reason_code
109 ,class
110 ,misc
111 ,employee_number
112 ,person_id
113 ,business_group_id
114 ,prtt_rt_val_id
115 ,object_version_number
116 ,cso_attribute_category
117 ,cso_attribute1
118 ,cso_attribute2
119 ,cso_attribute3
120 ,cso_attribute4
121 ,cso_attribute5
122 ,cso_attribute6
123 ,cso_attribute7
124 ,cso_attribute8
125 ,cso_attribute9
126 ,cso_attribute10
127 ,cso_attribute11
128 ,cso_attribute12
129 ,cso_attribute13
130 ,cso_attribute14
131 ,cso_attribute15
132 ,cso_attribute16
133 ,cso_attribute17
134 ,cso_attribute18
135 ,cso_attribute19
136 ,cso_attribute20
137 ,cso_attribute21
138 ,cso_attribute22
139 ,cso_attribute23
140 ,cso_attribute24
141 ,cso_attribute25
142 ,cso_attribute26
143 ,cso_attribute27
144 ,cso_attribute28
145 ,cso_attribute29
146 ,cso_attribute30
147 )
148 Values
149 (p_rec.cwb_stock_optn_dtls_id
150 ,p_rec.grant_id
151 ,p_rec.grant_number
152 ,p_rec.grant_name
153 ,p_rec.grant_type
154 ,p_rec.grant_date
155 ,p_rec.grant_shares
156 ,p_rec.grant_price
157 ,p_rec.value_at_grant
158 ,p_rec.current_share_price
159 ,p_rec.current_shares_outstanding
160 ,p_rec.vested_shares
161 ,p_rec.unvested_shares
162 ,p_rec.exercisable_shares
163 ,p_rec.exercised_shares
164 ,p_rec.cancelled_shares
165 ,p_rec.trading_symbol
166 ,p_rec.expiration_date
167 ,p_rec.reason_code
168 ,p_rec.class
169 ,p_rec.misc
170 ,p_rec.employee_number
171 ,p_rec.person_id
172 ,p_rec.business_group_id
173 ,p_rec.prtt_rt_val_id
174 ,p_rec.object_version_number
175 ,p_rec.cso_attribute_category
176 ,p_rec.cso_attribute1
177 ,p_rec.cso_attribute2
178 ,p_rec.cso_attribute3
179 ,p_rec.cso_attribute4
180 ,p_rec.cso_attribute5
181 ,p_rec.cso_attribute6
182 ,p_rec.cso_attribute7
183 ,p_rec.cso_attribute8
184 ,p_rec.cso_attribute9
185 ,p_rec.cso_attribute10
186 ,p_rec.cso_attribute11
187 ,p_rec.cso_attribute12
188 ,p_rec.cso_attribute13
189 ,p_rec.cso_attribute14
190 ,p_rec.cso_attribute15
191 ,p_rec.cso_attribute16
192 ,p_rec.cso_attribute17
193 ,p_rec.cso_attribute18
194 ,p_rec.cso_attribute19
195 ,p_rec.cso_attribute20
196 ,p_rec.cso_attribute21
197 ,p_rec.cso_attribute22
198 ,p_rec.cso_attribute23
199 ,p_rec.cso_attribute24
200 ,p_rec.cso_attribute25
201 ,p_rec.cso_attribute26
202 ,p_rec.cso_attribute27
203 ,p_rec.cso_attribute28
204 ,p_rec.cso_attribute29
205 ,p_rec.cso_attribute30
206 );
207 --
208 --
209 --
210 hr_utility.set_location(' Leaving:'||l_proc, 10);
211 Exception
212 When hr_api.check_integrity_violated Then
213 -- A check constraint has been violated
214 --
215 ben_cso_shd.constraint_error
216 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
217 When hr_api.parent_integrity_violated Then
218 -- Parent integrity has been violated
219 --
220 ben_cso_shd.constraint_error
221 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
222 When hr_api.unique_integrity_violated Then
223 -- Unique integrity has been violated
224 --
225 ben_cso_shd.constraint_error
226 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
227 When Others Then
228 --
229 Raise;
230 End insert_dml;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |------------------------------< pre_insert >------------------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- This private procedure contains any processing which is required before
239 -- the insert dml. Presently, if the entity has a corresponding primary
240 -- key which is maintained by an associating sequence, the primary key for
241 -- the entity will be populated with the next sequence value in
242 -- preparation for the insert dml.
243 --
244 -- Prerequisites:
245 -- This is an internal procedure which is called from the ins procedure.
246 --
247 -- In Parameters:
248 -- A Pl/Sql record structure.
249 --
250 -- Post Success:
251 -- Processing continues.
252 --
253 -- Post Failure:
254 -- If an error has occurred, an error message and exception will be raised
255 -- but not handled.
256 --
257 -- Developer Implementation Notes:
258 -- Any pre-processing required before the insert dml is issued should be
259 -- coded within this procedure. As stated above, a good example is the
260 -- generation of a primary key number via a corresponding sequence.
261 -- It is important to note that any 3rd party maintenance should be reviewed
262 -- before placing in this procedure.
263 --
264 -- Access Status:
265 -- Internal Row Handler Use Only.
266 --
267 -- {End Of Comments}
268 -- ----------------------------------------------------------------------------
269 Procedure pre_insert
270 (p_rec in out nocopy ben_cso_shd.g_rec_type
271 ) is
272 --
273 Cursor C_Sel1 is select ben_cwb_stock_optn_dtls_s.nextval from sys.dual;
274 --
275 Cursor C_Sel2 is
276 Select null
277 from ben_cwb_stock_optn_dtls
278 where cwb_stock_optn_dtls_id =
279 ben_cso_ins.g_cwb_stock_optn_dtls_id_i;
280 --
281 l_proc varchar2(72) := g_package||'pre_insert';
282 l_exists varchar2(1);
283 --
284 Begin
285 hr_utility.set_location('Entering:'||l_proc, 5);
286 --
287 If (ben_cso_ins.g_cwb_stock_optn_dtls_id_i is not null) Then
288 --
289 -- Verify registered primary key values not already in use
290 --
291 Open C_Sel2;
292 Fetch C_Sel2 into l_exists;
293 If C_Sel2%found Then
294 Close C_Sel2;
295 --
296 -- The primary key values are already in use.
297 --
298 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
299 fnd_message.set_token('TABLE_NAME','ben_cwb_stock_optn_dtls');
300 fnd_message.raise_error;
301 End If;
302 Close C_Sel2;
303 --
304 -- Use registered key values and clear globals
305 --
306 p_rec.cwb_stock_optn_dtls_id :=
307 ben_cso_ins.g_cwb_stock_optn_dtls_id_i;
308 ben_cso_ins.g_cwb_stock_optn_dtls_id_i := null;
309 Else
310 --
311 -- No registerd key values, so select the next sequence number
312 --
313 --
314 -- Select the next sequence number
315 --
316 Open C_Sel1;
317 Fetch C_Sel1 Into p_rec.cwb_stock_optn_dtls_id;
318 Close C_Sel1;
319 End If;
320 --
321 hr_utility.set_location(' Leaving:'||l_proc, 10);
322 End pre_insert;
323 --
324 -- ----------------------------------------------------------------------------
325 -- |-----------------------------< post_insert >------------------------------|
326 -- ----------------------------------------------------------------------------
327 -- {Start Of Comments}
328 --
329 -- Description:
330 -- This private procedure contains any processing which is required after
331 -- the insert dml.
332 --
333 -- Prerequisites:
334 -- This is an internal procedure which is called from the ins procedure.
335 --
336 -- In Parameters:
337 -- A Pl/Sql record structre.
338 --
339 -- Post Success:
340 -- Processing continues.
341 --
342 -- Post Failure:
343 -- If an error has occurred, an error message and exception will be raised
344 -- but not handled.
345 --
346 -- Developer Implementation Notes:
347 -- Any post-processing required after the insert dml is issued should be
348 -- coded within this procedure. It is important to note that any 3rd party
349 -- maintenance should be reviewed before placing in this procedure.
350 --
351 -- Access Status:
352 -- Internal Row Handler Use Only.
353 --
354 -- {End Of Comments}
355 -- ----------------------------------------------------------------------------
356 Procedure post_insert
357 (p_effective_date in date
358 ,p_rec in ben_cso_shd.g_rec_type
359 ) is
360 --
361 l_proc varchar2(72) := g_package||'post_insert';
362 --
363 Begin
364 hr_utility.set_location('Entering:'||l_proc, 5);
365 begin
366 --
367 ben_cso_rki.after_insert
368 (p_effective_date => p_effective_date
369 ,p_cwb_stock_optn_dtls_id
370 => p_rec.cwb_stock_optn_dtls_id
371 ,p_grant_id
372 => p_rec.grant_id
373 ,p_grant_number
374 => p_rec.grant_number
375 ,p_grant_name
376 => p_rec.grant_name
377 ,p_grant_type
378 => p_rec.grant_type
379 ,p_grant_date
380 => p_rec.grant_date
381 ,p_grant_shares
382 => p_rec.grant_shares
383 ,p_grant_price
384 => p_rec.grant_price
385 ,p_value_at_grant
386 => p_rec.value_at_grant
387 ,p_current_share_price
388 => p_rec.current_share_price
389 ,p_current_shares_outstanding
390 => p_rec.current_shares_outstanding
391 ,p_vested_shares
392 => p_rec.vested_shares
393 ,p_unvested_shares
394 => p_rec.unvested_shares
395 ,p_exercisable_shares
396 => p_rec.exercisable_shares
397 ,p_exercised_shares
398 => p_rec.exercised_shares
399 ,p_cancelled_shares
400 => p_rec.cancelled_shares
401 ,p_trading_symbol
402 => p_rec.trading_symbol
403 ,p_expiration_date
404 => p_rec.expiration_date
405 ,p_reason_code
406 => p_rec.reason_code
407 ,p_class
408 => p_rec.class
409 ,p_misc
410 => p_rec.misc
411 ,p_employee_number
412 => p_rec.employee_number
413 ,p_person_id
414 => p_rec.person_id
415 ,p_business_group_id
416 => p_rec.business_group_id
417 ,p_prtt_rt_val_id
418 => p_rec.prtt_rt_val_id
419 ,p_object_version_number
420 => p_rec.object_version_number
421 ,p_cso_attribute_category
422 => p_rec.cso_attribute_category
423 ,p_cso_attribute1
424 => p_rec.cso_attribute1
425 ,p_cso_attribute2
426 => p_rec.cso_attribute2
427 ,p_cso_attribute3
428 => p_rec.cso_attribute3
429 ,p_cso_attribute4
430 => p_rec.cso_attribute4
431 ,p_cso_attribute5
432 => p_rec.cso_attribute5
433 ,p_cso_attribute6
434 => p_rec.cso_attribute6
435 ,p_cso_attribute7
436 => p_rec.cso_attribute7
437 ,p_cso_attribute8
438 => p_rec.cso_attribute8
439 ,p_cso_attribute9
440 => p_rec.cso_attribute9
441 ,p_cso_attribute10
442 => p_rec.cso_attribute10
443 ,p_cso_attribute11
444 => p_rec.cso_attribute11
445 ,p_cso_attribute12
446 => p_rec.cso_attribute12
447 ,p_cso_attribute13
448 => p_rec.cso_attribute13
449 ,p_cso_attribute14
450 => p_rec.cso_attribute14
451 ,p_cso_attribute15
452 => p_rec.cso_attribute15
453 ,p_cso_attribute16
454 => p_rec.cso_attribute16
455 ,p_cso_attribute17
456 => p_rec.cso_attribute17
457 ,p_cso_attribute18
458 => p_rec.cso_attribute18
459 ,p_cso_attribute19
460 => p_rec.cso_attribute19
461 ,p_cso_attribute20
462 => p_rec.cso_attribute20
463 ,p_cso_attribute21
464 => p_rec.cso_attribute21
465 ,p_cso_attribute22
466 => p_rec.cso_attribute22
467 ,p_cso_attribute23
468 => p_rec.cso_attribute23
469 ,p_cso_attribute24
470 => p_rec.cso_attribute24
471 ,p_cso_attribute25
472 => p_rec.cso_attribute25
473 ,p_cso_attribute26
474 => p_rec.cso_attribute26
475 ,p_cso_attribute27
476 => p_rec.cso_attribute27
477 ,p_cso_attribute28
478 => p_rec.cso_attribute28
479 ,p_cso_attribute29
480 => p_rec.cso_attribute29
481 ,p_cso_attribute30
482 => p_rec.cso_attribute30
483 );
484 --
485 exception
486 --
487 when hr_api.cannot_find_prog_unit then
488 --
489 hr_api.cannot_find_prog_unit_error
490 (p_module_name => 'BEN_CWB_STOCK_OPTN_DTLS'
491 ,p_hook_type => 'AI');
492 --
493 end;
494 --
495 hr_utility.set_location(' Leaving:'||l_proc, 10);
496 End post_insert;
497 --
498 -- ----------------------------------------------------------------------------
499 -- |---------------------------------< ins >----------------------------------|
500 -- ----------------------------------------------------------------------------
501 Procedure ins
502 (p_effective_date in date
503 ,p_rec in out nocopy ben_cso_shd.g_rec_type
504 ) is
505 --
506 l_proc varchar2(72) := g_package||'ins';
507 --
508 Begin
509 hr_utility.set_location('Entering:'||l_proc, 5);
510 --
511 -- Call the supporting insert validate operations
512 --
513 ben_cso_bus.insert_validate
514 (p_effective_date
515 ,p_rec
516 );
517 --
518 -- Call to raise any errors on multi-message list
519 hr_multi_message.end_validation_set;
520 --
521 -- Call the supporting pre-insert operation
522 --
523 ben_cso_ins.pre_insert(p_rec);
524 --
525 -- Insert the row
526 --
527 ben_cso_ins.insert_dml(p_rec);
528 --
529 -- Call the supporting post-insert operation
530 --
531 ben_cso_ins.post_insert
532 (p_effective_date
533 ,p_rec
534 );
535 --
536 -- Call to raise any errors on multi-message list
537 hr_multi_message.end_validation_set;
538 --
539 hr_utility.set_location('Leaving:'||l_proc, 20);
540 end ins;
541 --
542 -- ----------------------------------------------------------------------------
543 -- |---------------------------------< ins >----------------------------------|
544 -- ----------------------------------------------------------------------------
545 Procedure ins
546 (p_effective_date in date
547 ,p_grant_id in number default null
548 ,p_grant_number in varchar2 default null
549 ,p_grant_name in varchar2 default null
550 ,p_grant_type in varchar2 default null
551 ,p_grant_date in date default null
552 ,p_grant_shares in number default null
553 ,p_grant_price in number default null
554 ,p_value_at_grant in number default null
555 ,p_current_share_price in number default null
556 ,p_current_shares_outstanding in number default null
557 ,p_vested_shares in number default null
558 ,p_unvested_shares in number default null
559 ,p_exercisable_shares in number default null
560 ,p_exercised_shares in number default null
561 ,p_cancelled_shares in number default null
562 ,p_trading_symbol in varchar2 default null
563 ,p_expiration_date in date default null
564 ,p_reason_code in varchar2 default null
565 ,p_class in varchar2 default null
566 ,p_misc in varchar2 default null
567 ,p_employee_number in varchar2 default null
568 ,p_person_id in number default null
569 ,p_business_group_id in number default null
570 ,p_prtt_rt_val_id in number default null
571 ,p_cso_attribute_category in varchar2 default null
572 ,p_cso_attribute1 in varchar2 default null
573 ,p_cso_attribute2 in varchar2 default null
574 ,p_cso_attribute3 in varchar2 default null
575 ,p_cso_attribute4 in varchar2 default null
576 ,p_cso_attribute5 in varchar2 default null
577 ,p_cso_attribute6 in varchar2 default null
578 ,p_cso_attribute7 in varchar2 default null
579 ,p_cso_attribute8 in varchar2 default null
580 ,p_cso_attribute9 in varchar2 default null
581 ,p_cso_attribute10 in varchar2 default null
582 ,p_cso_attribute11 in varchar2 default null
583 ,p_cso_attribute12 in varchar2 default null
584 ,p_cso_attribute13 in varchar2 default null
585 ,p_cso_attribute14 in varchar2 default null
586 ,p_cso_attribute15 in varchar2 default null
587 ,p_cso_attribute16 in varchar2 default null
588 ,p_cso_attribute17 in varchar2 default null
589 ,p_cso_attribute18 in varchar2 default null
590 ,p_cso_attribute19 in varchar2 default null
591 ,p_cso_attribute20 in varchar2 default null
592 ,p_cso_attribute21 in varchar2 default null
593 ,p_cso_attribute22 in varchar2 default null
594 ,p_cso_attribute23 in varchar2 default null
595 ,p_cso_attribute24 in varchar2 default null
596 ,p_cso_attribute25 in varchar2 default null
597 ,p_cso_attribute26 in varchar2 default null
598 ,p_cso_attribute27 in varchar2 default null
599 ,p_cso_attribute28 in varchar2 default null
600 ,p_cso_attribute29 in varchar2 default null
601 ,p_cso_attribute30 in varchar2 default null
602 ,p_cwb_stock_optn_dtls_id out nocopy number
603 ,p_object_version_number out nocopy number
604 ) is
605 --
606 l_rec ben_cso_shd.g_rec_type;
607 l_proc varchar2(72) := g_package||'ins';
608 --
609 Begin
610 hr_utility.set_location('Entering:'||l_proc, 5);
611 --
612 -- Call conversion function to turn arguments into the
613 -- p_rec structure.
614 --
615 l_rec :=
616 ben_cso_shd.convert_args
617 (null
618 ,p_grant_id
619 ,p_grant_number
620 ,p_grant_name
621 ,p_grant_type
622 ,p_grant_date
623 ,p_grant_shares
624 ,p_grant_price
625 ,p_value_at_grant
626 ,p_current_share_price
627 ,p_current_shares_outstanding
628 ,p_vested_shares
629 ,p_unvested_shares
630 ,p_exercisable_shares
631 ,p_exercised_shares
632 ,p_cancelled_shares
633 ,p_trading_symbol
634 ,p_expiration_date
635 ,p_reason_code
636 ,p_class
637 ,p_misc
638 ,p_employee_number
639 ,p_person_id
640 ,p_business_group_id
641 ,p_prtt_rt_val_id
642 ,null
643 ,p_cso_attribute_category
644 ,p_cso_attribute1
645 ,p_cso_attribute2
646 ,p_cso_attribute3
647 ,p_cso_attribute4
648 ,p_cso_attribute5
649 ,p_cso_attribute6
650 ,p_cso_attribute7
651 ,p_cso_attribute8
652 ,p_cso_attribute9
653 ,p_cso_attribute10
654 ,p_cso_attribute11
655 ,p_cso_attribute12
656 ,p_cso_attribute13
657 ,p_cso_attribute14
658 ,p_cso_attribute15
659 ,p_cso_attribute16
660 ,p_cso_attribute17
661 ,p_cso_attribute18
662 ,p_cso_attribute19
663 ,p_cso_attribute20
664 ,p_cso_attribute21
665 ,p_cso_attribute22
666 ,p_cso_attribute23
667 ,p_cso_attribute24
668 ,p_cso_attribute25
669 ,p_cso_attribute26
670 ,p_cso_attribute27
671 ,p_cso_attribute28
672 ,p_cso_attribute29
673 ,p_cso_attribute30
674 );
675 --
676 -- Having converted the arguments into the ben_cso_rec
677 -- plsql record structure we call the corresponding record business process.
678 --
679 ben_cso_ins.ins
680 (p_effective_date
681 ,l_rec
682 );
683 --
684 -- As the primary key argument(s)
685 -- are specified as an OUT's we must set these values.
686 --
687 p_cwb_stock_optn_dtls_id := l_rec.cwb_stock_optn_dtls_id;
688 p_object_version_number := l_rec.object_version_number;
689 --
690 hr_utility.set_location(' Leaving:'||l_proc, 10);
691 End ins;
692 --
693 end ben_cso_ins;