4 -- ----------------------------------------------------------------------------
1 Package Body ota_ctu_ins as
2 /* $Header: otcturhi.pkb 120.2.12010000.2 2009/07/24 10:53:50 shwnayak ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_ctu_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_category_usage_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_category_usage_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 ota_ctu_ins.g_category_usage_id_i := p_category_usage_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 ota_ctu_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: ota_category_usages
88 --
89
90 insert into ota_category_usages
91 (category_usage_id
92 ,business_group_id
93 ,category
94 ,object_version_number
95 ,type
96 ,start_date_active
97 ,end_date_active
98 ,parent_cat_usage_id
99 ,synchronous_flag
100 ,online_flag
101 ,attribute_category
102 ,attribute1
103 ,attribute2
104 ,attribute3
105 ,attribute4
106 ,attribute5
107 ,attribute6
108 ,attribute7
109 ,attribute8
110 ,attribute9
111 ,attribute10
112 ,attribute11
113 ,attribute12
114 ,attribute13
115 ,attribute14
116 ,attribute15
117 ,attribute16
118 ,attribute17
119 ,attribute18
120 ,attribute19
121 ,attribute20
122 ,data_source
123 ,comments
124 ,user_group_id
125 )
126 Values
127 (p_rec.category_usage_id
128 ,p_rec.business_group_id
129 ,p_rec.category
130 ,p_rec.object_version_number
131 ,p_rec.type
132 ,p_rec.start_date_active
133 ,p_rec.end_date_active
134 ,p_rec.parent_cat_usage_id
135 ,p_rec.synchronous_flag
136 ,p_rec.online_flag
137 ,p_rec.attribute_category
138 ,p_rec.attribute1
139 ,p_rec.attribute2
140 ,p_rec.attribute3
141 ,p_rec.attribute4
142 ,p_rec.attribute5
143 ,p_rec.attribute6
144 ,p_rec.attribute7
148 ,p_rec.attribute11
145 ,p_rec.attribute8
146 ,p_rec.attribute9
147 ,p_rec.attribute10
149 ,p_rec.attribute12
150 ,p_rec.attribute13
151 ,p_rec.attribute14
152 ,p_rec.attribute15
153 ,p_rec.attribute16
154 ,p_rec.attribute17
155 ,p_rec.attribute18
156 ,p_rec.attribute19
157 ,p_rec.attribute20
158 ,p_rec.data_source
159 ,p_rec.comments
160 ,p_rec.user_group_id
161 );
162 --
163 --
164 --
165 hr_utility.set_location(' Leaving:'||l_proc, 10);
166 Exception
167 When hr_api.check_integrity_violated Then
168 -- A check constraint has been violated
169 --
170 ota_ctu_shd.constraint_error
171 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
172 When hr_api.parent_integrity_violated Then
173 -- Parent integrity has been violated
174 --
175 ota_ctu_shd.constraint_error
176 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
177 When hr_api.unique_integrity_violated Then
178 -- Unique integrity has been violated
179 --
180 ota_ctu_shd.constraint_error
181 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
182 When Others Then
183 --
184 Raise;
185 End insert_dml;
186 --
187 -- ----------------------------------------------------------------------------
188 -- |------------------------------< pre_insert >------------------------------|
189 -- ----------------------------------------------------------------------------
190 -- {Start Of Comments}
191 --
192 -- Description:
193 -- This private procedure contains any processing which is required before
194 -- the insert dml. Presently, if the entity has a corresponding primary
195 -- key which is maintained by an associating sequence, the primary key for
196 -- the entity will be populated with the next sequence value in
197 -- preparation for the insert dml.
198 --
199 -- Prerequisites:
200 -- This is an internal procedure which is called from the ins procedure.
201 --
202 -- In Parameters:
203 -- A Pl/Sql record structure.
204 --
205 -- Post Success:
206 -- Processing continues.
207 --
208 -- Post Failure:
209 -- If an error has occurred, an error message and exception will be raised
210 -- but not handled.
211 --
212 -- Developer Implementation Notes:
213 -- Any pre-processing required before the insert dml is issued should be
214 -- coded within this procedure. As stated above, a good example is the
215 -- generation of a primary key number via a corresponding sequence.
216 -- It is important to note that any 3rd party maintenance should be reviewed
217 -- before placing in this procedure.
218 --
219 -- Access Status:
220 -- Internal Row Handler Use Only.
221 --
222 -- {End Of Comments}
223 -- ----------------------------------------------------------------------------
224 Procedure pre_insert
225 (p_rec in out nocopy ota_ctu_shd.g_rec_type
226 ) is
227 --
228 Cursor C_Sel1 is select ota_category_usages_s.nextval from sys.dual;
229 --
230 Cursor C_Sel2 is
231 Select null
232 from ota_category_usages
233 where category_usage_id =
234 ota_ctu_ins.g_category_usage_id_i;
235 --
236 l_proc varchar2(72) := g_package||'pre_insert';
237 l_exists varchar2(1);
238 --
239 Begin
240 hr_utility.set_location('Entering:'||l_proc, 5);
241 --
242 If (ota_ctu_ins.g_category_usage_id_i is not null) Then
243 --
244 -- Verify registered primary key values not already in use
245 --
246 Open C_Sel2;
247 Fetch C_Sel2 into l_exists;
248 If C_Sel2%found Then
249 Close C_Sel2;
250 --
251 -- The primary key values are already in use.
252 --
253 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
254 fnd_message.set_token('TABLE_NAME','ota_category_usages');
255 fnd_message.raise_error;
256 End If;
257 Close C_Sel2;
258 --
259 -- Use registered key values and clear globals
260 --
261 p_rec.category_usage_id :=
262 ota_ctu_ins.g_category_usage_id_i;
263 ota_ctu_ins.g_category_usage_id_i := null;
264 Else
265 --
266 -- No registerd key values, so select the next sequence number
267 --
268 --
269 -- Select the next sequence number
270 --
271 Open C_Sel1;
272 Fetch C_Sel1 Into p_rec.category_usage_id;
273 Close C_Sel1;
274 End If;
275 --
276 hr_utility.set_location(' Leaving:'||l_proc, 10);
277 End pre_insert;
278 --
279 -- ----------------------------------------------------------------------------
280 -- |-----------------------------< post_insert >------------------------------|
281 -- ----------------------------------------------------------------------------
282 -- {Start Of Comments}
283 --
284 -- Description:
285 -- This private procedure contains any processing which is required after
286 -- the insert dml.
287 --
288 -- Prerequisites:
289 -- This is an internal procedure which is called from the ins procedure.
290 --
291 -- In Parameters:
292 -- A Pl/Sql record structre.
293 --
294 -- Post Success:
295 -- Processing continues.
296 --
297 -- Post Failure:
298 -- If an error has occurred, an error message and exception will be raised
299 -- but not handled.
300 --
301 -- Developer Implementation Notes:
305 --
302 -- Any post-processing required after the insert dml is issued should be
303 -- coded within this procedure. It is important to note that any 3rd party
304 -- maintenance should be reviewed before placing in this procedure.
306 -- Access Status:
307 -- Internal Row Handler Use Only.
308 --
309 -- {End Of Comments}
310 -- ----------------------------------------------------------------------------
311 Procedure post_insert
312 (p_effective_date in date
313 ,p_rec in ota_ctu_shd.g_rec_type
314 ) is
315 --
316 l_proc varchar2(72) := g_package||'post_insert';
317 --
318 Begin
319 hr_utility.set_location('Entering:'||l_proc, 5);
320 begin
321 --
322 ota_ctu_rki.after_insert
323 (p_effective_date => p_effective_date
324 ,p_category_usage_id
325 => p_rec.category_usage_id
326 ,p_business_group_id
327 => p_rec.business_group_id
328 ,p_category
329 => p_rec.category
330 ,p_object_version_number
331 => p_rec.object_version_number
332 ,p_type
333 => p_rec.type
334 ,p_start_date_active
335 => p_rec.start_date_active
336 ,p_end_date_active
337 => p_rec.end_date_active
338 ,p_parent_cat_usage_id
339 => p_rec.parent_cat_usage_id
340 ,p_synchronous_flag
341 => p_rec.synchronous_flag
342 ,p_online_flag
343 => p_rec.online_flag
344 ,p_attribute_category
345 => p_rec.attribute_category
346 ,p_attribute1
347 => p_rec.attribute1
348 ,p_attribute2
349 => p_rec.attribute2
350 ,p_attribute3
351 => p_rec.attribute3
352 ,p_attribute4
353 => p_rec.attribute4
354 ,p_attribute5
355 => p_rec.attribute5
356 ,p_attribute6
357 => p_rec.attribute6
358 ,p_attribute7
359 => p_rec.attribute7
360 ,p_attribute8
361 => p_rec.attribute8
362 ,p_attribute9
363 => p_rec.attribute9
364 ,p_attribute10
365 => p_rec.attribute10
366 ,p_attribute11
367 => p_rec.attribute11
368 ,p_attribute12
369 => p_rec.attribute12
370 ,p_attribute13
371 => p_rec.attribute13
372 ,p_attribute14
373 => p_rec.attribute14
374 ,p_attribute15
375 => p_rec.attribute15
376 ,p_attribute16
377 => p_rec.attribute16
378 ,p_attribute17
379 => p_rec.attribute17
380 ,p_attribute18
381 => p_rec.attribute18
382 ,p_attribute19
383 => p_rec.attribute19
384 ,p_attribute20
385 => p_rec.attribute20
386 ,p_data_source
387 => p_rec.data_source
388 ,p_comments
389 =>p_rec.comments
390 );
391 --
392 exception
393 --
394 when hr_api.cannot_find_prog_unit then
395 --
396 hr_api.cannot_find_prog_unit_error
397 (p_module_name => 'OTA_CATEGORY_USAGES'
398 ,p_hook_type => 'AI');
399 --
400 end;
401 --
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 End post_insert;
404 --
405 -- ----------------------------------------------------------------------------
406 -- |---------------------------------< ins >----------------------------------|
407 -- ----------------------------------------------------------------------------
408 Procedure ins
409 (p_effective_date in date
410 ,p_rec in out nocopy ota_ctu_shd.g_rec_type
411 ) is
412 --
413 l_proc varchar2(72) := g_package||'ins';
414 --
415 Begin
416 hr_utility.set_location('Entering:'||l_proc, 5);
417 --
418 -- Call the supporting insert validate operations
419 --
420 ota_ctu_bus.insert_validate
421 (p_effective_date
422 ,p_rec
423 );
424 --
425 -- Call to raise any errors on multi-message list
426 hr_multi_message.end_validation_set;
427 --
428 -- Call the supporting pre-insert operation
429 --
430 ota_ctu_ins.pre_insert(p_rec);
431 --
432 -- Insert the row
433 --
434 ota_ctu_ins.insert_dml(p_rec);
435 --
436 -- Call the supporting post-insert operation
437 --
438 ota_ctu_ins.post_insert
439 (p_effective_date
440 ,p_rec
441 );
442 --
443 -- Call to raise any errors on multi-message list
444 hr_multi_message.end_validation_set;
445 --
446 hr_utility.set_location('Leaving:'||l_proc, 20);
447 end ins;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |---------------------------------< ins >----------------------------------|
451 -- ----------------------------------------------------------------------------
452 Procedure ins
453 (p_effective_date in date
454 ,p_business_group_id in number
455 ,p_category in varchar2
456 ,p_type in varchar2
457 ,p_start_date_active in date default null
458 ,p_end_date_active in date default null
459 ,p_parent_cat_usage_id in number default null
460 ,p_synchronous_flag in varchar2 default null
461 ,p_online_flag in varchar2 default null
465 ,p_attribute3 in varchar2 default null
462 ,p_attribute_category in varchar2 default null
463 ,p_attribute1 in varchar2 default null
464 ,p_attribute2 in varchar2 default null
466 ,p_attribute4 in varchar2 default null
467 ,p_attribute5 in varchar2 default null
468 ,p_attribute6 in varchar2 default null
469 ,p_attribute7 in varchar2 default null
470 ,p_attribute8 in varchar2 default null
471 ,p_attribute9 in varchar2 default null
472 ,p_attribute10 in varchar2 default null
473 ,p_attribute11 in varchar2 default null
474 ,p_attribute12 in varchar2 default null
475 ,p_attribute13 in varchar2 default null
476 ,p_attribute14 in varchar2 default null
477 ,p_attribute15 in varchar2 default null
478 ,p_attribute16 in varchar2 default null
479 ,p_attribute17 in varchar2 default null
480 ,p_attribute18 in varchar2 default null
481 ,p_attribute19 in varchar2 default null
482 ,p_attribute20 in varchar2 default null
483 ,p_data_source in varchar2 default null
484 ,p_category_usage_id out nocopy number
485 ,p_object_version_number out nocopy number
486 ,p_comments in varchar2 default null
487 ,p_user_group_id in number default null
488 ) is
489 --
490 l_rec ota_ctu_shd.g_rec_type;
491 l_proc varchar2(72) := g_package||'ins';
492 --
493 Begin
494 hr_utility.set_location('Entering:'||l_proc, 5);
495 --
496 -- Call conversion function to turn arguments into the
497 -- p_rec structure.
498 --
499 l_rec :=
500 ota_ctu_shd.convert_args
501 (null
502 ,p_business_group_id
503 ,p_category
504 ,null
505 ,p_type
506 ,p_start_date_active
507 ,p_end_date_active
508 ,p_parent_cat_usage_id
509 ,p_synchronous_flag
510 ,p_online_flag
511 ,p_attribute_category
512 ,p_attribute1
513 ,p_attribute2
514 ,p_attribute3
515 ,p_attribute4
516 ,p_attribute5
517 ,p_attribute6
518 ,p_attribute7
519 ,p_attribute8
520 ,p_attribute9
521 ,p_attribute10
522 ,p_attribute11
523 ,p_attribute12
524 ,p_attribute13
525 ,p_attribute14
526 ,p_attribute15
527 ,p_attribute16
528 ,p_attribute17
529 ,p_attribute18
530 ,p_attribute19
531 ,p_attribute20
532 ,p_data_source
533 ,p_comments
534 ,p_user_group_id
535 );
536 --
537 -- Having converted the arguments into the ota_ctu_rec
538 -- plsql record structure we call the corresponding record business process.
539 --
540 ota_ctu_ins.ins
541 (p_effective_date
542 ,l_rec
543 );
544 --
545 -- As the primary key argument(s)
546 -- are specified as an OUT's we must set these values.
547 --
548 p_category_usage_id := l_rec.category_usage_id;
549 p_object_version_number := l_rec.object_version_number;
550 --
551 hr_utility.set_location(' Leaving:'||l_proc, 10);
552 End ins;
553 --
554 end ota_ctu_ins;
[Home] [Help]