1 Package Body ota_ocl_ins as
2 /* $Header: otoclrhi.pkb 120.1.12000000.2 2007/02/07 09:19:37 niarora noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_ocl_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To insert the row into the schema.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the ins
28 -- procedure and must have all mandatory attributes set (except the
29 -- object_version_number which is initialised within this procedure).
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be inserted into the schema.
36 --
37 -- Post Failure:
38 -- On the insert dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- None.
47 --
48 -- Access Status:
49 -- Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure insert_dml
57 l_proc varchar2(72) := g_package||'insert_dml';
54 (p_rec in out nocopy ota_ocl_shd.g_rec_type
55 ) is
56 --
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc, 5);
61 p_rec.object_version_number := 1; -- Initialise the object version
62 --
63 --
64 --
65 -- Insert the row into: ota_competence_languages
66 --
67 insert into ota_competence_languages
68 (competence_language_id
69 ,competence_id
70 ,language_code
71 ,min_proficiency_level_id
72 ,business_group_id
73 ,object_version_number
74 ,ocl_information_category
75 ,ocl_information1
76 ,ocl_information2
77 ,ocl_information3
78 ,ocl_information4
79 ,ocl_information5
80 ,ocl_information6
81 ,ocl_information7
82 ,ocl_information8
83 ,ocl_information9
84 ,ocl_information10
85 ,ocl_information11
86 ,ocl_information12
87 ,ocl_information13
88 ,ocl_information14
89 ,ocl_information15
90 ,ocl_information16
91 ,ocl_information17
92 ,ocl_information18
93 ,ocl_information19
94 ,ocl_information20
95 )
96 Values
97 (p_rec.competence_language_id
98 ,p_rec.competence_id
99 ,p_rec.language_code
100 ,p_rec.min_proficiency_level_id
101 ,p_rec.business_group_id
102 ,p_rec.object_version_number
103 ,p_rec.ocl_information_category
104 ,p_rec.ocl_information1
105 ,p_rec.ocl_information2
106 ,p_rec.ocl_information3
107 ,p_rec.ocl_information4
108 ,p_rec.ocl_information5
109 ,p_rec.ocl_information6
110 ,p_rec.ocl_information7
111 ,p_rec.ocl_information8
112 ,p_rec.ocl_information9
113 ,p_rec.ocl_information10
114 ,p_rec.ocl_information11
115 ,p_rec.ocl_information12
116 ,p_rec.ocl_information13
117 ,p_rec.ocl_information14
118 ,p_rec.ocl_information15
119 ,p_rec.ocl_information16
120 ,p_rec.ocl_information17
121 ,p_rec.ocl_information18
122 ,p_rec.ocl_information19
123 ,p_rec.ocl_information20
124 );
125 --
126 --
127 --
128 hr_utility.set_location(' Leaving:'||l_proc, 10);
129 Exception
130 When hr_api.check_integrity_violated Then
131 -- A check constraint has been violated
132 --
133 ota_ocl_shd.constraint_error
134 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
135 When hr_api.parent_integrity_violated Then
136 -- Parent integrity has been violated
137 --
138 ota_ocl_shd.constraint_error
139 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
140 When hr_api.unique_integrity_violated Then
141 -- Unique integrity has been violated
142 --
143 ota_ocl_shd.constraint_error
144 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
145 When Others Then
146 --
147 Raise;
148 End insert_dml;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |------------------------------< pre_insert >------------------------------|
152 -- ----------------------------------------------------------------------------
153 -- {Start Of Comments}
154 --
155 -- Description:
156 -- This private procedure contains any processing which is required before
157 -- the insert dml. Presently, if the entity has a corresponding primary
158 -- key which is maintained by an associating sequence, the primary key for
159 -- the entity will be populated with the next sequence value in
160 -- preparation for the insert dml.
161 --
162 -- Prerequisites:
163 -- This is an internal procedure which is called from the ins procedure.
164 --
165 -- In Parameters:
166 -- A Pl/Sql record structre.
167 --
168 -- Post Success:
169 -- Processing continues.
170 --
171 -- Post Failure:
172 -- If an error has occurred, an error message and exception will be raised
173 -- but not handled.
174 --
175 -- Developer Implementation Notes:
176 -- Any pre-processing required before the insert dml is issued should be
177 -- coded within this procedure. As stated above, a good example is the
178 -- generation of a primary key number via a corresponding sequence.
179 -- It is important to note that any 3rd party maintenance should be reviewed
180 -- before placing in this procedure.
181 --
182 -- Access Status:
183 -- Internal Row Handler Use Only.
184 --
185 -- {End Of Comments}
186 -- ----------------------------------------------------------------------------
187 Procedure pre_insert
188 (p_rec in out nocopy ota_ocl_shd.g_rec_type
189 ) is
190 --
191 l_proc varchar2(72) := g_package||'pre_insert';
192 --
193 Cursor C_Sel1 is select ota_competence_languages_s.nextval from sys.dual;
194 --
195 Begin
196 hr_utility.set_location('Entering:'||l_proc, 5);
197 --
198 --
199 -- Select the next sequence number
200 --
201 Open C_Sel1;
202 Fetch C_Sel1 Into p_rec.competence_language_id;
203 Close C_Sel1;
204 --
205 hr_utility.set_location(' Leaving:'||l_proc, 10);
206 End pre_insert;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |-----------------------------< post_insert >------------------------------|
213 -- Description:
210 -- ----------------------------------------------------------------------------
211 -- {Start Of Comments}
212 --
214 -- This private procedure contains any processing which is required after the
215 -- insert dml.
216 --
217 -- Prerequisites:
218 -- This is an internal procedure which is called from the ins procedure.
219 --
220 -- In Parameters:
221 -- A Pl/Sql record structre.
222 --
223 -- Post Success:
224 -- Processing continues.
225 --
226 -- Post Failure:
227 -- If an error has occurred, an error message and exception will be raised
228 -- but not handled.
229 --
230 -- Developer Implementation Notes:
231 -- Any post-processing required after the insert dml is issued should be
232 -- coded within this procedure. It is important to note that any 3rd party
233 -- maintenance should be reviewed before placing in this procedure.
234 --
235 -- Access Status:
236 -- Internal Row Handler Use Only.
237 --
238 -- {End Of Comments}
239 -- ----------------------------------------------------------------------------
240 Procedure post_insert
241 (p_effective_date in date
242 ,p_rec in ota_ocl_shd.g_rec_type
243 ) is
244 --
245 l_proc varchar2(72) := g_package||'post_insert';
246 --
247 Begin
248 hr_utility.set_location('Entering:'||l_proc, 5);
249 begin
250 --
251 ota_ocl_rki.after_insert
252 (p_effective_date => p_effective_date
253 ,p_competence_language_id
254 => p_rec.competence_language_id
255 ,p_competence_id
256 => p_rec.competence_id
257 ,p_language_code
258 => p_rec.language_code
259 ,p_min_proficiency_level_id
260 => p_rec.min_proficiency_level_id
261 ,p_business_group_id
262 => p_rec.business_group_id
263 ,p_object_version_number
264 => p_rec.object_version_number
265 ,p_ocl_information_category
266 => p_rec.ocl_information_category
267 ,p_ocl_information1
268 => p_rec.ocl_information1
269 ,p_ocl_information2
270 => p_rec.ocl_information2
271 ,p_ocl_information3
272 => p_rec.ocl_information3
273 ,p_ocl_information4
274 => p_rec.ocl_information4
275 ,p_ocl_information5
276 => p_rec.ocl_information5
277 ,p_ocl_information6
278 => p_rec.ocl_information6
279 ,p_ocl_information7
280 => p_rec.ocl_information7
281 ,p_ocl_information8
282 => p_rec.ocl_information8
283 ,p_ocl_information9
284 => p_rec.ocl_information9
285 ,p_ocl_information10
286 => p_rec.ocl_information10
287 ,p_ocl_information11
288 => p_rec.ocl_information11
289 ,p_ocl_information12
290 => p_rec.ocl_information12
291 ,p_ocl_information13
292 => p_rec.ocl_information13
293 ,p_ocl_information14
294 => p_rec.ocl_information14
295 ,p_ocl_information15
296 => p_rec.ocl_information15
297 ,p_ocl_information16
298 => p_rec.ocl_information16
299 ,p_ocl_information17
300 => p_rec.ocl_information17
301 ,p_ocl_information18
302 => p_rec.ocl_information18
303 ,p_ocl_information19
304 => p_rec.ocl_information19
305 ,p_ocl_information20
306 => p_rec.ocl_information20
307 );
308 --
309 exception
310 --
311 when hr_api.cannot_find_prog_unit then
312 --
313 hr_api.cannot_find_prog_unit_error
314 (p_module_name => 'OTA_COMPETENCE_LANGUAGES'
315 ,p_hook_type => 'AI');
316 --
317 end;
318 --
319 hr_utility.set_location(' Leaving:'||l_proc, 10);
320 End post_insert;
321 --
322 -- ----------------------------------------------------------------------------
323 -- |---------------------------------< ins >----------------------------------|
324 -- ----------------------------------------------------------------------------
325 Procedure ins
326 (p_effective_date in date
327 ,p_rec in out nocopy ota_ocl_shd.g_rec_type
328 ) is
329 --
330 l_proc varchar2(72) := g_package||'ins';
331 --
332 Begin
333 hr_utility.set_location('Entering:'||l_proc, 5);
334 --
335 -- Call the supporting insert validate operations
336 --
337 ota_ocl_bus.insert_validate
338 (p_effective_date
339 ,p_rec
340 );
341 --
342 -- Call the supporting pre-insert operation
343 --
344 ota_ocl_ins.pre_insert(p_rec);
345 --
346 -- Insert the row
347 --
348 ota_ocl_ins.insert_dml(p_rec);
349 --
350 -- Call the supporting post-insert operation
351 --
352 ota_ocl_ins.post_insert
353 (p_effective_date
354 ,p_rec
355 );
356 --
357 hr_utility.set_location('Leaving:'||l_proc, 20);
358 end ins;
359 --
360 -- ----------------------------------------------------------------------------
361 -- |---------------------------------< ins >----------------------------------|
362 -- ----------------------------------------------------------------------------
363 Procedure ins
364 (p_effective_date in date
365 ,p_competence_id in number
366 ,p_language_code in varchar2
367 ,p_business_group_id in number
368 ,p_min_proficiency_level_id in number default null
369 ,p_ocl_information_category in varchar2 default null
370 ,p_ocl_information1 in varchar2 default null
371 ,p_ocl_information2 in varchar2 default null
372 ,p_ocl_information3 in varchar2 default null
373 ,p_ocl_information4 in varchar2 default null
374 ,p_ocl_information5 in varchar2 default null
375 ,p_ocl_information6 in varchar2 default null
376 ,p_ocl_information7 in varchar2 default null
377 ,p_ocl_information8 in varchar2 default null
378 ,p_ocl_information9 in varchar2 default null
379 ,p_ocl_information10 in varchar2 default null
380 ,p_ocl_information11 in varchar2 default null
381 ,p_ocl_information12 in varchar2 default null
382 ,p_ocl_information13 in varchar2 default null
383 ,p_ocl_information14 in varchar2 default null
384 ,p_ocl_information15 in varchar2 default null
385 ,p_ocl_information16 in varchar2 default null
386 ,p_ocl_information17 in varchar2 default null
387 ,p_ocl_information18 in varchar2 default null
388 ,p_ocl_information19 in varchar2 default null
389 ,p_ocl_information20 in varchar2 default null
390 ,p_competence_language_id out nocopy number
391 ,p_object_version_number out nocopy number
392 ) is
393 --
394 l_rec ota_ocl_shd.g_rec_type;
395 l_proc varchar2(72) := g_package||'ins';
396 --
397 Begin
398 hr_utility.set_location('Entering:'||l_proc, 5);
399 --
400 -- Call conversion function to turn arguments into the
401 -- p_rec structure.
402 --
403 l_rec :=
404 ota_ocl_shd.convert_args
405 (null
406 ,p_competence_id
407 ,p_language_code
408 ,p_min_proficiency_level_id
409 ,p_business_group_id
410 ,null
411 ,p_ocl_information_category
412 ,p_ocl_information1
413 ,p_ocl_information2
414 ,p_ocl_information3
415 ,p_ocl_information4
416 ,p_ocl_information5
417 ,p_ocl_information6
418 ,p_ocl_information7
419 ,p_ocl_information8
420 ,p_ocl_information9
421 ,p_ocl_information10
422 ,p_ocl_information11
423 ,p_ocl_information12
424 ,p_ocl_information13
425 ,p_ocl_information14
426 ,p_ocl_information15
427 ,p_ocl_information16
428 ,p_ocl_information17
429 ,p_ocl_information18
430 ,p_ocl_information19
431 ,p_ocl_information20
432 );
433 --
434 -- Having converted the arguments into the ota_ocl_rec
435 -- plsql record structure we call the corresponding record business process.
436 --
437 ota_ocl_ins.ins
438 (p_effective_date
439 ,l_rec
440 );
441 --
442 -- As the primary key argument(s)
443 -- are specified as an OUT's we must set these values.
444 --
445 p_competence_language_id := l_rec.competence_language_id;
446 p_object_version_number := l_rec.object_version_number;
447 --
448 hr_utility.set_location(' Leaving:'||l_proc, 10);
449 End ins;
450 --
451 end ota_ocl_ins;