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