1 Package Body ota_tsp_ins as
2 /* $Header: ottsp01t.pkb 120.0 2005/05/29 07:54:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tsp_ins.'; -- Global package name
9 g_skill_provision_id_i number default null;
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------------------------< set_base_key_value >----------------------------|
13 -- ----------------------------------------------------------------------------
14 procedure set_base_key_value
15 (p_skill_provision_id in number) is
16 --
17 l_proc varchar2(72) := g_package||'set_base_key_value';
18 --
19 Begin
20 hr_utility.set_location('Entering:'||l_proc, 10);
21 --
22 ota_tsp_ins.g_skill_provision_id_i := p_skill_provision_id;
23 --
24 hr_utility.set_location(' Leaving:'||l_proc, 20);
25 End set_base_key_value;
26 -- ----------------------------------------------------------------------------
27 -- |------------------------------< insert_dml >------------------------------|
28 -- ----------------------------------------------------------------------------
29 -- {Start Of Comments}
30 --
31 -- Description:
32 -- This procedure controls the actual dml insert logic. The functions of this
33 -- procedure are as follows:
34 -- 1) Initialise the object_version_number to 1 if the object_version_number
35 -- is defined as an attribute for this entity.
36 -- 2) To set and unset the g_api_dml status as required (as we are about to
37 -- perform dml).
38 -- 3) To insert the row into the schema.
39 -- 4) To trap any constraint violations that may have occurred.
40 -- 5) To raise any other errors.
41 --
42 -- Pre Conditions:
43 -- This is an internal private procedure which must be called from the ins
44 -- procedure and must have all mandatory arguments set (except the
45 -- object_version_number which is initialised within this procedure).
46 --
47 -- In Arguments:
48 -- A Pl/Sql record structre.
49 --
50 -- Post Success:
51 -- The specified row will be inserted into the schema.
52 --
53 -- Post Failure:
54 -- On the insert dml failure it is important to note that we always reset the
55 -- g_api_dml status to false.
56 -- If a check, unique or parent integrity constraint violation is raised the
57 -- constraint_error procedure will be called.
58 -- If any other error is reported, the error will be raised after the
59 -- g_api_dml status is reset.
60 --
61 -- Developer Implementation Notes:
62 -- None.
63 --
64 -- Access Status:
65 -- Internal Development Use Only.
66 --
67 -- {End Of Comments}
68 -- ----------------------------------------------------------------------------
69 Procedure insert_dml(p_rec in out nocopy ota_tsp_shd.g_rec_type) is
70 --
71 l_proc varchar2(72) := g_package||'insert_dml';
72 --
73 Begin
74 hr_utility.set_location('Entering:'||l_proc, 5);
75 p_rec.object_version_number := 1; -- Initialise the object version
76 --
77 ota_tsp_shd.g_api_dml := true; -- Set the api dml status
78 --
79 -- Insert the row into: ota_skill_provisions
80 --
81 insert into ota_skill_provisions
82 ( skill_provision_id,
83 activity_version_id,
84 object_version_number,
85 type,
86 comments,
87 tsp_information_category,
88 tsp_information1,
89 tsp_information2,
90 tsp_information3,
91 tsp_information4,
92 tsp_information5,
93 tsp_information6,
94 tsp_information7,
95 tsp_information8,
96 tsp_information9,
97 tsp_information10,
98 tsp_information11,
99 tsp_information12,
100 tsp_information13,
101 tsp_information14,
102 tsp_information15,
103 tsp_information16,
104 tsp_information17,
105 tsp_information18,
106 tsp_information19,
107 tsp_information20,
108 analysis_criteria_id
109 )
110 Values
111 ( p_rec.skill_provision_id,
112 p_rec.activity_version_id,
113 p_rec.object_version_number,
114 p_rec.type,
115 p_rec.comments,
116 p_rec.tsp_information_category,
117 p_rec.tsp_information1,
118 p_rec.tsp_information2,
119 p_rec.tsp_information3,
120 p_rec.tsp_information4,
121 p_rec.tsp_information5,
122 p_rec.tsp_information6,
123 p_rec.tsp_information7,
124 p_rec.tsp_information8,
125 p_rec.tsp_information9,
126 p_rec.tsp_information10,
127 p_rec.tsp_information11,
128 p_rec.tsp_information12,
129 p_rec.tsp_information13,
130 p_rec.tsp_information14,
131 p_rec.tsp_information15,
132 p_rec.tsp_information16,
133 p_rec.tsp_information17,
134 p_rec.tsp_information18,
135 p_rec.tsp_information19,
136 p_rec.tsp_information20,
137 p_rec.analysis_criteria_id
138 );
139 --
140 ota_tsp_shd.g_api_dml := false; -- Unset the api dml status
141 --
142 hr_utility.set_location(' Leaving:'||l_proc, 10);
143 Exception
144 When hr_api.check_integrity_violated Then
145 -- A check constraint has been violated
146 ota_tsp_shd.g_api_dml := false; -- Unset the api dml status
147 ota_tsp_shd.g_api_dml := true; -- Set the api dml status
148 ota_tsp_shd.constraint_error
149 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
150 When hr_api.parent_integrity_violated Then
151 -- Parent integrity has been violated
152 ota_tsp_shd.g_api_dml := false; -- Unset the api dml status
153 ota_tsp_shd.constraint_error
154 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
155 When hr_api.unique_integrity_violated Then
156 -- Unique integrity has been violated
157 ota_tsp_shd.g_api_dml := false; -- Unset the api dml status
158 ota_tsp_shd.constraint_error
159 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
160 When Others Then
161 ota_tsp_shd.g_api_dml := false; -- Unset the api dml status
162 Raise;
163 End insert_dml;
164 --
165 -- ----------------------------------------------------------------------------
166 -- |------------------------------< pre_insert >------------------------------|
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 -- This private procedure contains any processing which is required before
172 -- the insert dml. Presently, if the entity has a corresponding primary
173 -- key which is maintained by an associating sequence, the primary key for
174 -- the entity will be populated with the next sequence value in
175 -- preparation for the insert dml.
176 --
177 -- Pre Conditions:
178 -- This is an internal procedure which is called from the ins procedure.
179 --
180 -- In Arguments:
181 -- A Pl/Sql record structre.
182 --
183 -- Post Success:
184 -- Processing continues.
185 --
186 -- Post Failure:
187 -- If an error has occurred, an error message and exception will be raised
188 -- but not handled.
189 --
190 -- Developer Implementation Notes:
191 -- Any pre-processing required before the insert dml is issued should be
192 -- coded within this procedure. As stated above, a good example is the
193 -- generation of a primary key number via a corresponding sequence.
194 -- It is important to note that any 3rd party maintenance should be reviewed
195 -- before placing in this procedure.
196 --
197 -- Access Status:
198 -- Internal Development Use Only.
199 --
200 -- {End Of Comments}
201 -- ----------------------------------------------------------------------------
202 Procedure pre_insert
203 (p_rec in out nocopy ota_tsp_shd.g_rec_type
204 ) is
205 --
206 l_proc varchar2(72) := g_package||'pre_insert';
207 --
208 Cursor C_Sel1 is select ota_skill_provisions_s.nextval from sys.dual;
209 --
210 Cursor C_Sel2 is
211 Select null
212 from ota_skill_provisions
213 where skill_provision_id =
214 ota_tsp_ins.g_skill_provision_id_i;
215 --
216 l_exists varchar2(1);
217
218 Begin
219 hr_utility.set_location('Entering:'||l_proc, 5);
220 If (ota_tsp_ins.g_skill_provision_id_i is not null) Then
221 --
222 -- Verify registered primary key values not already in use
223 --
224 Open C_Sel2;
225 Fetch C_Sel2 into l_exists;
226 If C_Sel2%found Then
227 Close C_Sel2;
228 --
229 -- The primary key values are already in use.
230 --
231 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
232 fnd_message.set_token('TABLE_NAME','irc_documents');
233 fnd_message.raise_error;
234 End If;
235 Close C_Sel2;
236 --
237 -- Use registered key values and clear globals
238 --
239 p_rec.skill_provision_id :=
240 ota_tsp_ins.g_skill_provision_id_i;
241 ota_tsp_ins.g_skill_provision_id_i := null;
242 Else
243 --
244 -- No registerd key values, so select the next sequence number
245 --
246 --
247 -- Select the next sequence number
248 Open C_Sel1;
249 Fetch C_Sel1 Into p_rec.skill_provision_id;
250 Close C_Sel1;
251
252 END IF;
253 --
254 hr_utility.set_location(' Leaving:'||l_proc, 10);
255 End pre_insert;
256 ---- ----------------------------------------------------------------------------
257 -- |-----------------------------< post_insert >------------------------------|
258 -- ----------------------------------------------------------------------------
259 -- {Start Of Comments}
260 --
261 -- Description:
262 -- This private procedure contains any processing which is required after the
263 -- insert dml.
264 --
265 -- Pre Conditions:
266 -- This is an internal procedure which is called from the ins procedure.
267 --
268 -- In Arguments:
269 -- A Pl/Sql record structre.
270 --
271 -- Post Success:
272 -- Processing continues.
273 --
274 -- Post Failure:
275 -- If an error has occurred, an error message and exception will be raised
276 -- but not handled.
277 --
278 -- Developer Implementation Notes:
279 -- Any post-processing required after the insert dml is issued should be
280 -- coded within this procedure. It is important to note that any 3rd party
281 -- maintenance should be reviewed before placing in this procedure.
282 --
283 -- Access Status:
284 -- Internal Development Use Only.
285 --
286 -- {End Of Comments}
287 -- ----------------------------------------------------------------------------
288 Procedure post_insert(p_rec in ota_tsp_shd.g_rec_type) is
289 --
290 l_proc varchar2(72) := g_package||'post_insert';
291 --
292 Begin
293 hr_utility.set_location('Entering:'||l_proc, 5);
294 --
295 hr_utility.set_location(' Leaving:'||l_proc, 10);
296 End post_insert;
297 --
298 -- ----------------------------------------------------------------------------
299 -- |---------------------------------< ins >----------------------------------|
300 -- ----------------------------------------------------------------------------
301 Procedure ins
302 (
303 p_rec in out nocopy ota_tsp_shd.g_rec_type,
304 p_validate in boolean default false
305 ) is
306 --
307 l_proc varchar2(72) := g_package||'ins';
308 --
309 Begin
310 hr_utility.set_location('Entering:'||l_proc, 5);
311 --
312 -- Determine if the business process is to be validated.
313 --
314 If p_validate then
315 --
316 -- Issue the savepoint.
317 --
318 SAVEPOINT ins_tsp;
319 End If;
320 --
321 -- Call the supporting insert validate operations
322 --
323 ota_tsp_bus.insert_validate(p_rec);
324 --
325 -- Call the supporting pre-insert operation
326 --
327 pre_insert(p_rec);
328 --
329 -- Insert the row
330 --
331 insert_dml(p_rec);
332 --
333 -- Call the supporting post-insert operation
334 --
335 post_insert(p_rec);
336 --
337 -- If we are validating then raise the Validate_Enabled exception
338 --
339 If p_validate then
340 Raise HR_Api.Validate_Enabled;
341 End If;
342 --
343 hr_utility.set_location(' Leaving:'||l_proc, 10);
344 Exception
345 When HR_Api.Validate_Enabled Then
346 --
347 -- As the Validate_Enabled exception has been raised
348 -- we must rollback to the savepoint
349 --
350 ROLLBACK TO ins_tsp;
351 end ins;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------------------< ins >----------------------------------|
355 -- ----------------------------------------------------------------------------
356 Procedure ins
357 (
358 p_skill_provision_id out nocopy number,
359 p_activity_version_id in number,
360 p_object_version_number out nocopy number,
361 p_type in varchar2,
362 p_comments in varchar2 default null,
363 p_tsp_information_category in varchar2 default null,
364 p_tsp_information1 in varchar2 default null,
365 p_tsp_information2 in varchar2 default null,
366 p_tsp_information3 in varchar2 default null,
367 p_tsp_information4 in varchar2 default null,
368 p_tsp_information5 in varchar2 default null,
369 p_tsp_information6 in varchar2 default null,
370 p_tsp_information7 in varchar2 default null,
371 p_tsp_information8 in varchar2 default null,
372 p_tsp_information9 in varchar2 default null,
373 p_tsp_information10 in varchar2 default null,
374 p_tsp_information11 in varchar2 default null,
375 p_tsp_information12 in varchar2 default null,
376 p_tsp_information13 in varchar2 default null,
377 p_tsp_information14 in varchar2 default null,
378 p_tsp_information15 in varchar2 default null,
379 p_tsp_information16 in varchar2 default null,
380 p_tsp_information17 in varchar2 default null,
381 p_tsp_information18 in varchar2 default null,
382 p_tsp_information19 in varchar2 default null,
383 p_tsp_information20 in varchar2 default null,
384 p_analysis_criteria_id in number,
385 p_validate in boolean default false
386 ) is
387 --
388 l_rec ota_tsp_shd.g_rec_type;
389 l_proc varchar2(72) := g_package||'ins';
390 --
391 Begin
392 hr_utility.set_location('Entering:'||l_proc, 5);
393 --
394 -- Call conversion function to turn arguments into the
395 -- p_rec structure.
396 --
397 l_rec :=
398 ota_tsp_shd.convert_args
399 (
400 null,
401 p_activity_version_id,
402 null,
403 p_type,
404 p_comments,
405 p_tsp_information_category,
406 p_tsp_information1,
407 p_tsp_information2,
408 p_tsp_information3,
409 p_tsp_information4,
410 p_tsp_information5,
411 p_tsp_information6,
412 p_tsp_information7,
413 p_tsp_information8,
414 p_tsp_information9,
415 p_tsp_information10,
416 p_tsp_information11,
417 p_tsp_information12,
418 p_tsp_information13,
419 p_tsp_information14,
420 p_tsp_information15,
421 p_tsp_information16,
422 p_tsp_information17,
423 p_tsp_information18,
424 p_tsp_information19,
425 p_tsp_information20,
426 p_analysis_criteria_id
427 );
428 --
429 -- Having converted the arguments into the tsp_rec
430 -- plsql record structure we call the corresponding record business process.
431 --
432 ins(l_rec, p_validate);
433 --
434 -- As the primary key argument(s)
435 -- are specified as an OUT's we must set these values.
436 --
437 p_skill_provision_id := l_rec.skill_provision_id;
438 p_object_version_number := l_rec.object_version_number;
439 --
440 hr_utility.set_location(' Leaving:'||l_proc, 10);
441 End ins;
442 --
443 end ota_tsp_ins;
[Home] [Help]