[Home] [Help]
PACKAGE BODY: APPS.OTA_TPS_INS
Source
1 PACKAGE BODY OTA_TPS_INS as
2 /* $Header: ottpsrhi.pkb 120.2 2005/12/14 15:17:58 asud noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tps_ins.'; -- Global package name
9 --
10 g_training_plan_id_i number default null;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------< set_base_key_value >----------------------------|
14 -- ----------------------------------------------------------------------------
15 procedure set_base_key_value
16 (p_training_plan_id in number) is
17 --
18 l_proc varchar2(72) := g_package||'set_base_key_value';
19 --
20 Begin
21 hr_utility.set_location('Entering:'||l_proc, 10);
22 --
23 ota_tps_ins.g_training_plan_id_i := p_training_plan_id;
24 --
25 hr_utility.set_location(' Leaving:'||l_proc, 20);
26 End set_base_key_value;
27 -- ----------------------------------------------------------------------------
28 -- |------------------------------< insert_dml >------------------------------|
29 -- ----------------------------------------------------------------------------
30 -- {Start Of Comments}
31 --
32 -- Description:
33 -- This procedure controls the actual dml insert logic. The processing of
34 -- this procedure are as follows:
35 -- 1) Initialise the object_version_number to 1 if the object_version_number
36 -- is defined as an attribute for this entity.
37 -- 2) To set and unset the g_api_dml status as required (as we are about to
38 -- perform dml).
39 -- 3) To insert the row into the schema.
40 -- 4) To trap any constraint violations that may have occurred.
41 -- 5) To raise any other errors.
42 --
43 -- Prerequisites:
44 -- This is an internal private procedure which must be called from the ins
45 -- procedure and must have all mandatory attributes set (except the
46 -- object_version_number which is initialised within this procedure).
47 --
48 -- In Parameters:
49 -- A Pl/Sql record structre.
50 --
51 -- Post Success:
52 -- The specified row will be inserted into the schema.
53 --
54 -- Post Failure:
55 -- On the insert dml failure it is important to note that we always reset the
56 -- g_api_dml status to false.
57 -- If a check, unique or parent integrity constraint violation is raised the
58 -- constraint_error procedure will be called.
59 -- If any other error is reported, the error will be raised after the
60 -- g_api_dml status is reset.
61 --
62 -- Developer Implementation Notes:
63 -- None.
64 --
65 -- Access Status:
66 -- Internal Row Handler Use Only.
67 --
68 -- {End Of Comments}
69 -- ----------------------------------------------------------------------------
70 Procedure insert_dml
71 (p_rec in out nocopy ota_tps_shd.g_rec_type
72 ) is
73 --
74 l_proc varchar2(72) := g_package||'insert_dml';
75 --
76 Begin
77 hr_utility.set_location('Entering:'||l_proc, 5);
78 p_rec.object_version_number := 1; -- Initialise the object version
79 --
80 ota_tps_shd.g_api_dml := true; -- Set the api dml status
81 --
82 -- Insert the row into: ota_training_plans
83 --
84 insert into ota_training_plans
85 (training_plan_id
86 ,time_period_id
87 ,plan_status_type_id
88 ,organization_id
89 ,person_id
90 ,budget_currency
91 ,name
92 ,description
93 ,business_group_id
94 ,object_version_number
95 ,attribute_category
96 ,attribute1
97 ,attribute2
98 ,attribute3
99 ,attribute4
100 ,attribute5
101 ,attribute6
102 ,attribute7
103 ,attribute8
104 ,attribute9
105 ,attribute10
106 ,attribute11
107 ,attribute12
108 ,attribute13
109 ,attribute14
110 ,attribute15
111 ,attribute16
112 ,attribute17
113 ,attribute18
114 ,attribute19
115 ,attribute20
116 ,attribute21
117 ,attribute22
118 ,attribute23
119 ,attribute24
120 ,attribute25
121 ,attribute26
122 ,attribute27
123 ,attribute28
124 ,attribute29
125 ,attribute30
126 ,plan_source
127 ,start_date
128 ,end_date
129 ,creator_person_id
130 ,additional_member_flag
131 ,learning_path_id
132 ,contact_id
133 )
134 Values
135 (p_rec.training_plan_id
136 ,p_rec.time_period_id
137 ,p_rec.plan_status_type_id
138 ,p_rec.organization_id
139 ,p_rec.person_id
140 ,p_rec.budget_currency
141 ,p_rec.name
142 ,p_rec.description
143 ,p_rec.business_group_id
144 ,p_rec.object_version_number
145 ,p_rec.attribute_category
146 ,p_rec.attribute1
147 ,p_rec.attribute2
148 ,p_rec.attribute3
149 ,p_rec.attribute4
150 ,p_rec.attribute5
151 ,p_rec.attribute6
152 ,p_rec.attribute7
153 ,p_rec.attribute8
154 ,p_rec.attribute9
155 ,p_rec.attribute10
156 ,p_rec.attribute11
157 ,p_rec.attribute12
158 ,p_rec.attribute13
159 ,p_rec.attribute14
160 ,p_rec.attribute15
161 ,p_rec.attribute16
162 ,p_rec.attribute17
163 ,p_rec.attribute18
164 ,p_rec.attribute19
165 ,p_rec.attribute20
166 ,p_rec.attribute21
167 ,p_rec.attribute22
168 ,p_rec.attribute23
169 ,p_rec.attribute24
170 ,p_rec.attribute25
171 ,p_rec.attribute26
172 ,p_rec.attribute27
173 ,p_rec.attribute28
174 ,p_rec.attribute29
175 ,p_rec.attribute30
176 ,p_rec.plan_source
177 ,p_rec.start_date
178 ,p_rec.end_date
179 ,p_rec.creator_person_id
180 ,p_rec.additional_member_flag
181 ,p_rec.learning_path_id
182 ,p_rec.contact_id
183 );
184 --
185 ota_tps_shd.g_api_dml := false; -- Unset the api dml status
186 --
187 hr_utility.set_location(' Leaving:'||l_proc, 10);
188 Exception
189 When hr_api.check_integrity_violated Then
190 -- A check constraint has been violated
191 ota_tps_shd.g_api_dml := false; -- Unset the api dml status
192 ota_tps_shd.constraint_error
193 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
194 When hr_api.parent_integrity_violated Then
195 -- Parent integrity has been violated
196 ota_tps_shd.g_api_dml := false; -- Unset the api dml status
197 ota_tps_shd.constraint_error
198 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
199 When hr_api.unique_integrity_violated Then
200 -- Unique integrity has been violated
201 ota_tps_shd.g_api_dml := false; -- Unset the api dml status
202 ota_tps_shd.constraint_error
203 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
204 When Others Then
205 ota_tps_shd.g_api_dml := false; -- Unset the api dml status
206 Raise;
207 End insert_dml;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |------------------------------< pre_insert >------------------------------|
211 -- ----------------------------------------------------------------------------
212 -- {Start Of Comments}
213 --
214 -- Description:
215 -- This private procedure contains any processing which is required before
216 -- the insert dml. Presently, if the entity has a corresponding primary
217 -- key which is maintained by an associating sequence, the primary key for
218 -- the entity will be populated with the next sequence value in
219 -- preparation for the insert dml.
220 --
221 -- Prerequisites:
222 -- This is an internal procedure which is called from the ins procedure.
223 --
224 -- In Parameters:
225 -- A Pl/Sql record structre.
226 --
227 -- Post Success:
228 -- Processing continues.
229 --
230 -- Post Failure:
231 -- If an error has occurred, an error message and exception will be raised
232 -- but not handled.
233 --
234 -- Developer Implementation Notes:
235 -- Any pre-processing required before the insert dml is issued should be
236 -- coded within this procedure. As stated above, a good example is the
237 -- generation of a primary key number via a corresponding sequence.
238 -- It is important to note that any 3rd party maintenance should be reviewed
239 -- before placing in this procedure.
240 --
241 -- Access Status:
242 -- Internal Row Handler Use Only.
243 --
244 -- {End Of Comments}
245 -- ----------------------------------------------------------------------------
246 Procedure pre_insert
247 (p_rec in out nocopy ota_tps_shd.g_rec_type
248 ) is
249 --
250 l_proc varchar2(72) := g_package||'pre_insert';
251 --
252 Cursor C_Sel1 is select ota_training_plans_s.nextval from sys.dual;
253 --
254 Cursor C_Sel2 is
255 Select null
256 from ota_training_plans
257 where training_plan_id =
258 ota_tps_ins.g_training_plan_id_i;
259 --
260 l_exists varchar2(1);
261
262 Begin
263 hr_utility.set_location('Entering:'||l_proc, 5);
264 If (ota_tps_ins.g_training_plan_id_i is not null) Then
265 --
266 -- Verify registered primary key values not already in use
267 --
268 Open C_Sel2;
269 Fetch C_Sel2 into l_exists;
270 If C_Sel2%found Then
271 Close C_Sel2;
272 --
273 -- The primary key values are already in use.
274 --
275 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
276 fnd_message.set_token('TABLE_NAME','irc_documents');
277 fnd_message.raise_error;
278 End If;
279 Close C_Sel2;
280 --
281 -- Use registered key values and clear globals
282 --
283 p_rec.training_plan_id :=
284 ota_tps_ins.g_training_plan_id_i;
285 ota_tps_ins.g_training_plan_id_i := null;
286 Else
287 --
288 -- No registerd key values, so select the next sequence number
289 --
290 --
291 -- Select the next sequence number
292 Open C_Sel1;
293 Fetch C_Sel1 Into p_rec.training_plan_id;
294 Close C_Sel1;
295
296 END IF;
297 --
298 hr_utility.set_location(' Leaving:'||l_proc, 10);
299 End pre_insert;
300 --
301 -- ----------------------------------------------------------------------------
302 -- |-----------------------------< post_insert >------------------------------|
303 -- ----------------------------------------------------------------------------
304 -- {Start Of Comments}
305 --
306 -- Description:
307 -- This private procedure contains any processing which is required after the
308 -- insert dml.
309 --
310 -- Prerequisites:
311 -- This is an internal procedure which is called from the ins procedure.
312 --
313 -- In Parameters:
314 -- A Pl/Sql record structre.
315 --
316 -- Post Success:
317 -- Processing continues.
318 --
319 -- Post Failure:
320 -- If an error has occurred, an error message and exception will be raised
321 -- but not handled.
322 --
323 -- Developer Implementation Notes:
324 -- Any post-processing required after the insert dml is issued should be
325 -- coded within this procedure. It is important to note that any 3rd party
326 -- maintenance should be reviewed before placing in this procedure.
327 --
328 -- Access Status:
329 -- Internal Row Handler Use Only.
330 --
331 -- {End Of Comments}
332 -- ----------------------------------------------------------------------------
333 Procedure post_insert
334 (p_effective_date in date
335 ,p_rec in ota_tps_shd.g_rec_type
336 ) is
337 --
338 l_proc varchar2(72) := g_package||'post_insert';
339 --
340 Begin
341 hr_utility.set_location('Entering:'||l_proc, 5);
342 begin
343 --
344 ota_tps_rki.after_insert
345 (p_effective_date => p_effective_date
346 ,p_training_plan_id
347 => p_rec.training_plan_id
348 ,p_time_period_id
349 => p_rec.time_period_id
350 ,p_plan_status_type_id
351 => p_rec.plan_status_type_id
352 ,p_organization_id
353 => p_rec.organization_id
354 ,p_person_id
355 => p_rec.person_id
356 ,p_budget_currency
357 => p_rec.budget_currency
358 ,p_name
359 => p_rec.name
360 ,p_description
361 => p_rec.description
362 ,p_business_group_id
363 => p_rec.business_group_id
364 ,p_object_version_number
365 => p_rec.object_version_number
366 ,p_attribute_category
367 => p_rec.attribute_category
368 ,p_attribute1
369 => p_rec.attribute1
370 ,p_attribute2
371 => p_rec.attribute2
372 ,p_attribute3
373 => p_rec.attribute3
374 ,p_attribute4
375 => p_rec.attribute4
376 ,p_attribute5
377 => p_rec.attribute5
378 ,p_attribute6
379 => p_rec.attribute6
380 ,p_attribute7
381 => p_rec.attribute7
382 ,p_attribute8
383 => p_rec.attribute8
384 ,p_attribute9
385 => p_rec.attribute9
386 ,p_attribute10
387 => p_rec.attribute10
388 ,p_attribute11
389 => p_rec.attribute11
390 ,p_attribute12
391 => p_rec.attribute12
392 ,p_attribute13
393 => p_rec.attribute13
394 ,p_attribute14
395 => p_rec.attribute14
396 ,p_attribute15
397 => p_rec.attribute15
398 ,p_attribute16
399 => p_rec.attribute16
400 ,p_attribute17
401 => p_rec.attribute17
402 ,p_attribute18
403 => p_rec.attribute18
404 ,p_attribute19
405 => p_rec.attribute19
406 ,p_attribute20
407 => p_rec.attribute20
408 ,p_attribute21
409 => p_rec.attribute21
410 ,p_attribute22
411 => p_rec.attribute22
412 ,p_attribute23
413 => p_rec.attribute23
414 ,p_attribute24
415 => p_rec.attribute24
416 ,p_attribute25
417 => p_rec.attribute25
418 ,p_attribute26
419 => p_rec.attribute26
420 ,p_attribute27
421 => p_rec.attribute27
422 ,p_attribute28
423 => p_rec.attribute28
424 ,p_attribute29
425 => p_rec.attribute29
426 ,p_attribute30
427 => p_rec.attribute30
428 ,p_plan_source => p_rec.plan_source --changed
429 ,p_start_date => p_rec.start_date
430 ,p_end_date => p_rec.end_date
431 ,p_creator_person_id => p_rec.creator_person_id
432 ,p_additional_member_flag => p_rec.additional_member_flag
433 ,p_learning_path_id => p_rec.learning_path_id
434 ,p_contact_id => p_rec.contact_id
435 );
436 --
437 exception
438 --
439 when hr_api.cannot_find_prog_unit then
440 --
441 hr_api.cannot_find_prog_unit_error
442 (p_module_name => 'OTA_TRAINING_PLANS'
443 ,p_hook_type => 'AI');
444 --
445 end;
446 --
447 hr_utility.set_location(' Leaving:'||l_proc, 10);
448 End post_insert;
449 --
450 -- ----------------------------------------------------------------------------
451 -- |---------------------------------< ins >----------------------------------|
452 -- ----------------------------------------------------------------------------
453 Procedure ins
454 (p_effective_date in date
455 ,p_rec in out nocopy ota_tps_shd.g_rec_type
456 ) is
457 --
458 l_proc varchar2(72) := g_package||'ins';
459 --
460 Begin
461 hr_utility.set_location('Entering:'||l_proc, 5);
462 --
463 -- Call the supporting insert validate operations
464 --
465 ota_tps_bus.insert_validate
466 (p_effective_date
467 ,p_rec
468 );
469 --
470 --CALL TO RAISE ANY ERRORS ON MULTI MESSAGE LIST
471 hr_multi_message.end_validation_set;
472
473 -- Call the supporting pre-insert operation
474 --
475 ota_tps_ins.pre_insert(p_rec);
476 --
477 -- Insert the row
478 --
479 ota_tps_ins.insert_dml(p_rec);
480 --
481 -- Call the supporting post-insert operation
482 --
483 ota_tps_ins.post_insert
484 (p_effective_date
485 ,p_rec
486 );
487 --
488 --CALL TO RAISE ANY ERRORS ON MULTI MESSAGE LIST
489 hr_multi_message.end_validation_set;
490
491 hr_utility.set_location('Leaving:'||l_proc, 20);
492 end ins;
493 --
494 -- ----------------------------------------------------------------------------
495 -- |---------------------------------< ins >----------------------------------|
496 -- ----------------------------------------------------------------------------
497 Procedure ins
498 (p_effective_date in date
499 ,p_time_period_id in number default null
500 ,p_plan_status_type_id in varchar2
501 ,p_budget_currency in varchar2
502 ,p_name in varchar2
503 ,p_business_group_id in number
504 ,p_organization_id in number default null
505 ,p_person_id in number default null
506 ,p_description in varchar2 default null
507 ,p_attribute_category in varchar2 default null
508 ,p_attribute1 in varchar2 default null
509 ,p_attribute2 in varchar2 default null
510 ,p_attribute3 in varchar2 default null
511 ,p_attribute4 in varchar2 default null
512 ,p_attribute5 in varchar2 default null
513 ,p_attribute6 in varchar2 default null
514 ,p_attribute7 in varchar2 default null
515 ,p_attribute8 in varchar2 default null
516 ,p_attribute9 in varchar2 default null
517 ,p_attribute10 in varchar2 default null
518 ,p_attribute11 in varchar2 default null
519 ,p_attribute12 in varchar2 default null
520 ,p_attribute13 in varchar2 default null
521 ,p_attribute14 in varchar2 default null
522 ,p_attribute15 in varchar2 default null
523 ,p_attribute16 in varchar2 default null
524 ,p_attribute17 in varchar2 default null
525 ,p_attribute18 in varchar2 default null
526 ,p_attribute19 in varchar2 default null
527 ,p_attribute20 in varchar2 default null
528 ,p_attribute21 in varchar2 default null
529 ,p_attribute22 in varchar2 default null
530 ,p_attribute23 in varchar2 default null
531 ,p_attribute24 in varchar2 default null
532 ,p_attribute25 in varchar2 default null
533 ,p_attribute26 in varchar2 default null
534 ,p_attribute27 in varchar2 default null
535 ,p_attribute28 in varchar2 default null
536 ,p_attribute29 in varchar2 default null
537 ,p_attribute30 in varchar2 default null
538 ,p_plan_source in varchar2 default null
539 ,p_start_date in date default null
540 ,p_end_date in date default null
541 ,p_creator_person_id in number default null
542 ,p_additional_member_flag in varchar2 default null
543 ,p_learning_path_id in number default null
544 ,p_contact_id in number default null
545 ,p_training_plan_id out nocopy number
546 ,p_object_version_number out nocopy number
547 ) is
548 --
549 l_rec ota_tps_shd.g_rec_type;
550 l_proc varchar2(72) := g_package||'ins';
551 --
552 Begin
553 hr_utility.set_location('Entering:'||l_proc, 5);
554 --
555 -- Call conversion function to turn arguments into the
556 -- p_rec structure.
557 --
558 l_rec :=
559 ota_tps_shd.convert_args
560 (null
561 ,p_time_period_id
562 ,p_plan_status_type_id
563 ,p_organization_id
564 ,p_person_id
565 ,p_budget_currency
566 ,p_name
567 ,p_description
568 ,p_business_group_id
569 ,null
570 ,p_attribute_category
571 ,p_attribute1
572 ,p_attribute2
573 ,p_attribute3
574 ,p_attribute4
575 ,p_attribute5
576 ,p_attribute6
577 ,p_attribute7
578 ,p_attribute8
579 ,p_attribute9
580 ,p_attribute10
581 ,p_attribute11
582 ,p_attribute12
583 ,p_attribute13
584 ,p_attribute14
585 ,p_attribute15
586 ,p_attribute16
587 ,p_attribute17
588 ,p_attribute18
589 ,p_attribute19
590 ,p_attribute20
591 ,p_attribute21
592 ,p_attribute22
593 ,p_attribute23
594 ,p_attribute24
595 ,p_attribute25
596 ,p_attribute26
597 ,p_attribute27
598 ,p_attribute28
599 ,p_attribute29
600 ,p_attribute30
601 ,p_plan_source
602 ,p_start_date
603 ,p_end_date
604 ,p_creator_person_id
605 ,p_additional_member_flag
606 ,p_learning_path_id
607 ,p_contact_id
608 );
609 --
610 -- Having converted the arguments into the ota_tps_rec
611 -- plsql record structure we call the corresponding record business process.
612 --
613 ota_tps_ins.ins
614 (p_effective_date
615 ,l_rec
616 );
617 --
618 -- As the primary key argument(s)
619 -- are specified as an OUT's we must set these values.
620 --
621 p_training_plan_id := l_rec.training_plan_id;
622 p_object_version_number := l_rec.object_version_number;
623 --
624 hr_utility.set_location(' Leaving:'||l_proc, 10);
625 End ins;
626 --
627 end ota_tps_ins;