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