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