1 Package Body ota_aci_ins as
2 /* $Header: otacirhi.pkb 120.0 2005/05/29 06:51:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_aci_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_activity_version_id_i number default null;
14 g_category_usage_id_i number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< set_base_key_value >----------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure set_base_key_value
20 (p_activity_version_id in number
21 ,p_category_usage_id in number) is
22 --
23 l_proc varchar2(72) := g_package||'set_base_key_value';
24 --
25 Begin
26 hr_utility.set_location('Entering:'||l_proc, 10);
27 --
28 ota_aci_ins.g_activity_version_id_i := p_activity_version_id;
29 ota_aci_ins.g_category_usage_id_i := p_category_usage_id;
30 --
31 hr_utility.set_location(' Leaving:'||l_proc, 20);
32 End set_base_key_value;
33 --
34 --
35 -- ----------------------------------------------------------------------------
36 -- |------------------------------< insert_dml >------------------------------|
37 -- ----------------------------------------------------------------------------
38 -- {Start Of Comments}
39 --
40 -- Description:
41 -- This procedure controls the actual dml insert logic. The processing of
42 -- this procedure are as follows:
43 -- 1) Initialise the object_version_number to 1 if the object_version_number
44 -- is defined as an attribute for this entity.
45 -- 2) To set and unset the g_api_dml status as required (as we are about to
46 -- perform dml).
47 -- 3) To insert the row into the schema.
48 -- 4) To trap any constraint violations that may have occurred.
49 -- 5) To raise any other errors.
50 --
51 -- Prerequisites:
52 -- This is an internal private procedure which must be called from the ins
53 -- procedure and must have all mandatory attributes set (except the
54 -- object_version_number which is initialised within this procedure).
55 --
56 -- In Parameters:
57 -- A Pl/Sql record structre.
58 --
59 -- Post Success:
60 -- The specified row will be inserted into the schema.
61 --
62 -- Post Failure:
63 -- On the insert dml failure it is important to note that we always reset the
64 -- g_api_dml status to false.
65 -- If a check, unique or parent integrity constraint violation is raised the
66 -- constraint_error procedure will be called.
67 -- If any other error is reported, the error will be raised after the
68 -- g_api_dml status is reset.
69 --
70 -- Developer Implementation Notes:
71 -- None.
72 --
73 -- Access Status:
74 -- Internal Row Handler Use Only.
75 --
76 -- {End Of Comments}
77 -- ----------------------------------------------------------------------------
78 Procedure insert_dml
79 (p_rec in out nocopy ota_aci_shd.g_rec_type
80 ) is
81 --
82 l_proc varchar2(72) := g_package||'insert_dml';
83 --
84 Begin
85 hr_utility.set_location('Entering:'||l_proc, 5);
86 p_rec.object_version_number := 1; -- Initialise the object version
87 --
88 ota_aci_shd.g_api_dml := true; -- Set the api dml status
89 --
90 -- Insert the row into: ota_act_cat_inclusions
91 --
92 insert into ota_act_cat_inclusions
93 (activity_version_id
94 ,activity_category
95 ,object_version_number
96 ,event_id
97 ,comments
98 ,aci_information_category
99 ,aci_information1
100 ,aci_information2
101 ,aci_information3
102 ,aci_information4
103 ,aci_information5
104 ,aci_information6
105 ,aci_information7
106 ,aci_information8
107 ,aci_information9
108 ,aci_information10
109 ,aci_information11
110 ,aci_information12
111 ,aci_information13
112 ,aci_information14
113 ,aci_information15
114 ,aci_information16
115 ,aci_information17
116 ,aci_information18
117 ,aci_information19
118 ,aci_information20
119 ,start_date_active
120 ,end_date_active
121 ,primary_flag
122 ,category_usage_id
123 )
124 Values
125 (p_rec.activity_version_id
126 ,p_rec.activity_category
127 ,p_rec.object_version_number
128 ,p_rec.event_id
129 ,p_rec.comments
130 ,p_rec.aci_information_category
131 ,p_rec.aci_information1
132 ,p_rec.aci_information2
133 ,p_rec.aci_information3
134 ,p_rec.aci_information4
135 ,p_rec.aci_information5
136 ,p_rec.aci_information6
137 ,p_rec.aci_information7
138 ,p_rec.aci_information8
139 ,p_rec.aci_information9
140 ,p_rec.aci_information10
141 ,p_rec.aci_information11
142 ,p_rec.aci_information12
143 ,p_rec.aci_information13
144 ,p_rec.aci_information14
145 ,p_rec.aci_information15
146 ,p_rec.aci_information16
147 ,p_rec.aci_information17
148 ,p_rec.aci_information18
149 ,p_rec.aci_information19
150 ,p_rec.aci_information20
151 ,p_rec.start_date_active
152 ,p_rec.end_date_active
153 ,p_rec.primary_flag
154 ,p_rec.category_usage_id
155 );
156 --
157 ota_aci_shd.g_api_dml := false; -- Unset the api dml status
158 --
159 hr_utility.set_location(' Leaving:'||l_proc, 10);
160 Exception
161 When hr_api.check_integrity_violated Then
162 -- A check constraint has been violated
163 ota_aci_shd.g_api_dml := false; -- Unset the api dml status
164 ota_aci_shd.constraint_error
165 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
166 When hr_api.parent_integrity_violated Then
167 -- Parent integrity has been violated
168 ota_aci_shd.g_api_dml := false; -- Unset the api dml status
169 ota_aci_shd.constraint_error
170 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
171 When hr_api.unique_integrity_violated Then
172 -- Unique integrity has been violated
173 ota_aci_shd.g_api_dml := false; -- Unset the api dml status
174 ota_aci_shd.constraint_error
175 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
176 When Others Then
177 ota_aci_shd.g_api_dml := false; -- Unset the api dml status
178 Raise;
179 End insert_dml;
180 --
181 -- ----------------------------------------------------------------------------
182 -- |------------------------------< pre_insert >------------------------------|
183 -- ----------------------------------------------------------------------------
184 -- {Start Of Comments}
185 --
186 -- Description:
187 -- This private procedure contains any processing which is required before
188 -- the insert dml. Presently, if the entity has a corresponding primary
189 -- key which is maintained by an associating sequence, the primary key for
190 -- the entity will be populated with the next sequence value in
191 -- preparation for the insert dml.
192 --
193 -- Prerequisites:
194 -- This is an internal procedure which is called from the ins procedure.
195 --
196 -- In Parameters:
197 -- A Pl/Sql record structure.
198 --
199 -- Post Success:
200 -- Processing continues.
201 --
202 -- Post Failure:
203 -- If an error has occurred, an error message and exception will be raised
204 -- but not handled.
205 --
206 -- Developer Implementation Notes:
207 -- Any pre-processing required before the insert dml is issued should be
208 -- coded within this procedure. As stated above, a good example is the
209 -- generation of a primary key number via a corresponding sequence.
210 -- It is important to note that any 3rd party maintenance should be reviewed
211 -- before placing in this procedure.
212 --
213 -- Access Status:
214 -- Internal Row Handler Use Only.
215 --
216 -- {End Of Comments}
217 -- ----------------------------------------------------------------------------
218 Procedure pre_insert
219 (p_rec in out nocopy ota_aci_shd.g_rec_type
220 ) is
221
222 /*
223 --
224 Cursor C_Sel1 is select ota_act_cat_inclusions_s.nextval from sys.dual;
225 --
226 Cursor C_Sel2 is
227 Select null
228 from ota_act_cat_inclusions
229 where activity_version_id =
230 ota_aci_ins.g_activity_version_id_i
231 or category_usage_id =
232 ota_aci_ins.g_category_usage_id_i;
233 --
234 */
235 l_proc varchar2(72) := g_package||'pre_insert';
236 l_exists varchar2(1);
237 --
238 Begin
239 hr_utility.set_location('Entering:'||l_proc, 5);
240 /*
241 --
242 If (ota_aci_ins.g_activity_version_id_i is not null or
243 ota_aci_ins.g_category_usage_id_i is not null) Then
244 --
245 -- Verify registered primary key values not already in use
246 --
247 Open C_Sel2;
248 Fetch C_Sel2 into l_exists;
249 If C_Sel2%found Then
250 Close C_Sel2;
251 --
252 -- The primary key values are already in use.
253 --
254 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
255 fnd_message.set_token('TABLE_NAME','ota_act_cat_inclusions');
256 fnd_message.raise_error;
257 End If;
258 Close C_Sel2;
259 --
260 -- Use registered key values and clear globals
261 --
262 p_rec.activity_version_id :=
263 ota_aci_ins.g_activity_version_id_i;
264 ota_aci_ins.g_activity_version_id_i := null;
265 p_rec.category_usage_id :=
266 ota_aci_ins.g_category_usage_id_i;
267 ota_aci_ins.g_category_usage_id_i := null;
268 Else
269 --
270 -- No registerd key values, so select the next sequence number
271 --
272 --
273 -- Select the next sequence number
274 --
275 Open C_Sel1;
276 Fetch C_Sel1 Into p_rec.category_usage_id;
277 Close C_Sel1;
278 End If;
279 --
280 */
281 hr_utility.set_location(' Leaving:'||l_proc, 10);
282 End pre_insert;
283 --
284 -- ----------------------------------------------------------------------------
285 -- |-----------------------------< post_insert >------------------------------|
286 -- ----------------------------------------------------------------------------
287 -- {Start Of Comments}
288 --
289 -- Description:
290 -- This private procedure contains any processing which is required after
291 -- the insert dml.
292 --
293 -- Prerequisites:
294 -- This is an internal procedure which is called from the ins procedure.
295 --
296 -- In Parameters:
297 -- A Pl/Sql record structre.
298 --
299 -- Post Success:
300 -- Processing continues.
301 --
302 -- Post Failure:
303 -- If an error has occurred, an error message and exception will be raised
304 -- but not handled.
305 --
306 -- Developer Implementation Notes:
307 -- Any post-processing required after the insert dml is issued should be
308 -- coded within this procedure. It is important to note that any 3rd party
309 -- maintenance should be reviewed before placing in this procedure.
310 --
311 -- Access Status:
312 -- Internal Row Handler Use Only.
313 --
314 -- {End Of Comments}
315 -- ----------------------------------------------------------------------------
316 Procedure post_insert
317 (p_effective_date in date
318 ,p_rec in ota_aci_shd.g_rec_type
319 ) is
320 --
321 l_proc varchar2(72) := g_package||'post_insert';
322 --
323 Begin
324 hr_utility.set_location('Entering:'||l_proc, 5);
325 begin
326 --
327 ota_aci_rki.after_insert
328 (p_effective_date => p_effective_date
329 ,p_activity_version_id
330 => p_rec.activity_version_id
331 ,p_activity_category
332 => p_rec.activity_category
333 ,p_object_version_number
334 => p_rec.object_version_number
335 ,p_event_id
336 => p_rec.event_id
337 ,p_comments
338 => p_rec.comments
339 ,p_aci_information_category
340 => p_rec.aci_information_category
341 ,p_aci_information1
342 => p_rec.aci_information1
343 ,p_aci_information2
344 => p_rec.aci_information2
345 ,p_aci_information3
346 => p_rec.aci_information3
347 ,p_aci_information4
348 => p_rec.aci_information4
349 ,p_aci_information5
350 => p_rec.aci_information5
351 ,p_aci_information6
352 => p_rec.aci_information6
353 ,p_aci_information7
354 => p_rec.aci_information7
355 ,p_aci_information8
356 => p_rec.aci_information8
357 ,p_aci_information9
358 => p_rec.aci_information9
359 ,p_aci_information10
360 => p_rec.aci_information10
361 ,p_aci_information11
362 => p_rec.aci_information11
363 ,p_aci_information12
364 => p_rec.aci_information12
365 ,p_aci_information13
366 => p_rec.aci_information13
367 ,p_aci_information14
368 => p_rec.aci_information14
369 ,p_aci_information15
370 => p_rec.aci_information15
371 ,p_aci_information16
372 => p_rec.aci_information16
373 ,p_aci_information17
374 => p_rec.aci_information17
375 ,p_aci_information18
376 => p_rec.aci_information18
377 ,p_aci_information19
378 => p_rec.aci_information19
379 ,p_aci_information20
380 => p_rec.aci_information20
381 ,p_start_date_active
382 => p_rec.start_date_active
383 ,p_end_date_active
384 => p_rec.end_date_active
385 ,p_primary_flag
386 => p_rec.primary_flag
387 ,p_category_usage_id
388 => p_rec.category_usage_id
389 );
390 --
391 exception
392 --
393 when hr_api.cannot_find_prog_unit then
394 --
395 hr_api.cannot_find_prog_unit_error
396 (p_module_name => 'OTA_ACT_CAT_INCLUSIONS'
397 ,p_hook_type => 'AI');
398 --
399 end;
400 --
401 hr_utility.set_location(' Leaving:'||l_proc, 10);
402 End post_insert;
403 --
404 -- ----------------------------------------------------------------------------
405 -- |---------------------------------< ins >----------------------------------|
406 -- ----------------------------------------------------------------------------
407 Procedure ins
408 (p_effective_date in date
409 ,p_rec in out nocopy ota_aci_shd.g_rec_type
410 ) is
411 --
412 l_proc varchar2(72) := g_package||'ins';
413 l_activity_version_id ota_act_cat_inclusions.activity_version_id%TYPE;
414 l_category_usage_id ota_act_cat_inclusions.category_usage_id%TYPE;
415 --
416 Begin
417 hr_utility.set_location('Entering:'||l_proc, 5);
418
419 l_activity_version_id := p_rec.activity_version_id;
420 l_category_usage_id := p_rec.category_usage_id;
421
422 /*p_rec.activity_version_id := null;
423 p_rec.category_usage_id := null;*/
424
425 ota_aci_ins.set_base_key_value( p_activity_version_id => l_activity_version_id
426 ,p_category_usage_id => l_category_usage_id
427 );
428 --
429 -- Call the supporting insert validate operations
430 --
431 ota_aci_bus.insert_validate
432 (p_effective_date
433 ,p_rec
434 ,l_activity_version_id
435 ,l_category_usage_id
436 );
437 --
438 -- Call to raise any errors on multi-message list
439 hr_multi_message.end_validation_set;
440 --
441 -- Call the supporting pre-insert operation
442 --
443 ota_aci_ins.pre_insert(p_rec);
444 --
445 -- Insert the row
446 --
447 ota_aci_ins.insert_dml(p_rec);
448 --
449 -- Call the supporting post-insert operation
450 --
451 ota_aci_ins.post_insert
452 (p_effective_date
453 ,p_rec
454 );
455 --
456 -- Call to raise any errors on multi-message list
457 hr_multi_message.end_validation_set;
458 --
459 hr_utility.set_location('Leaving:'||l_proc, 20);
460 end ins;
461 --
462 -- ----------------------------------------------------------------------------
463 -- |---------------------------------< ins >----------------------------------|
464 -- ----------------------------------------------------------------------------
465 Procedure ins
466 (p_effective_date in date
467 ,p_activity_category in varchar2
468 ,p_activity_version_id in number
469 ,p_category_usage_id in number
470 ,p_event_id in number default null
471 ,p_comments in varchar2 default null
472 ,p_aci_information_category in varchar2 default null
473 ,p_aci_information1 in varchar2 default null
474 ,p_aci_information2 in varchar2 default null
475 ,p_aci_information3 in varchar2 default null
476 ,p_aci_information4 in varchar2 default null
477 ,p_aci_information5 in varchar2 default null
478 ,p_aci_information6 in varchar2 default null
479 ,p_aci_information7 in varchar2 default null
480 ,p_aci_information8 in varchar2 default null
481 ,p_aci_information9 in varchar2 default null
482 ,p_aci_information10 in varchar2 default null
483 ,p_aci_information11 in varchar2 default null
484 ,p_aci_information12 in varchar2 default null
485 ,p_aci_information13 in varchar2 default null
486 ,p_aci_information14 in varchar2 default null
487 ,p_aci_information15 in varchar2 default null
488 ,p_aci_information16 in varchar2 default null
489 ,p_aci_information17 in varchar2 default null
490 ,p_aci_information18 in varchar2 default null
491 ,p_aci_information19 in varchar2 default null
492 ,p_aci_information20 in varchar2 default null
493 ,p_start_date_active in date default null
494 ,p_end_date_active in date default null
495 ,p_primary_flag in varchar2 default null
496 -- ,p_activity_version_id out nocopy number
497 -- ,p_category_usage_id out nocopy number
498 ,p_object_version_number out nocopy number
499 ) is
500 --
501 l_rec ota_aci_shd.g_rec_type;
502 l_proc varchar2(72) := g_package||'ins';
503 --
504 Begin
505 hr_utility.set_location('Entering:'||l_proc, 5);
506 --
507 -- Call conversion function to turn arguments into the
508 -- p_rec structure.
509 --
510 l_rec :=
511 ota_aci_shd.convert_args
512 (--null
513 p_activity_version_id
514 ,p_activity_category
515 ,null
516 ,p_event_id
517 ,p_comments
518 ,p_aci_information_category
519 ,p_aci_information1
520 ,p_aci_information2
521 ,p_aci_information3
522 ,p_aci_information4
523 ,p_aci_information5
524 ,p_aci_information6
525 ,p_aci_information7
526 ,p_aci_information8
527 ,p_aci_information9
528 ,p_aci_information10
529 ,p_aci_information11
530 ,p_aci_information12
531 ,p_aci_information13
532 ,p_aci_information14
533 ,p_aci_information15
534 ,p_aci_information16
535 ,p_aci_information17
536 ,p_aci_information18
537 ,p_aci_information19
538 ,p_aci_information20
539 ,p_start_date_active
540 ,p_end_date_active
541 ,p_primary_flag
542 -- ,null
543 ,p_category_usage_id
544 );
545 --
546 -- Having converted the arguments into the ota_aci_rec
547 -- plsql record structure we call the corresponding record business process.
548 --
549 ota_aci_ins.ins
550 (p_effective_date
551 ,l_rec
552 );
553 --
554 -- As the primary key argument(s)
555 -- are specified as an OUT's we must set these values.
556 --
557 -- p_activity_version_id := l_rec.activity_version_id;
558 -- p_category_usage_id := l_rec.category_usage_id;
559 p_object_version_number := l_rec.object_version_number;
560 --
561 hr_utility.set_location(' Leaving:'||l_proc, 10);
562 End ins;
563 --
564 end ota_aci_ins;
[Home] [Help]