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