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