1 Package Body hr_icx_ins as
2 /* $Header: hricxrhi.pkb 115.5 2003/10/23 01:44:08 bsubrama noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_icx_ins.'; -- Global package name
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------------< ins_or_sel >-----------------------------|
11 -- ----------------------------------------------------------------------------
12 procedure ins_or_sel
13 (p_segment1 in varchar2 default null,
14 p_segment2 in varchar2 default null,
15 p_segment3 in varchar2 default null,
16 p_segment4 in varchar2 default null,
17 p_segment5 in varchar2 default null,
18 p_segment6 in varchar2 default null,
19 p_segment7 in varchar2 default null,
20 p_segment8 in varchar2 default null,
21 p_segment9 in varchar2 default null,
22 p_segment10 in varchar2 default null,
23 p_segment11 in varchar2 default null,
24 p_segment12 in varchar2 default null,
25 p_segment13 in varchar2 default null,
26 p_segment14 in varchar2 default null,
27 p_segment15 in varchar2 default null,
28 p_segment16 in varchar2 default null,
29 p_segment17 in varchar2 default null,
30 p_segment18 in varchar2 default null,
31 p_segment19 in varchar2 default null,
32 p_segment20 in varchar2 default null,
33 p_segment21 in varchar2 default null,
34 p_segment22 in varchar2 default null,
35 p_segment23 in varchar2 default null,
36 p_segment24 in varchar2 default null,
37 p_segment25 in varchar2 default null,
38 p_segment26 in varchar2 default null,
39 p_segment27 in varchar2 default null,
40 p_segment28 in varchar2 default null,
41 p_segment29 in varchar2 default null,
42 p_segment30 in varchar2 default null,
43 p_context_type in varchar2 default null,
44 p_item_context_id out nocopy number,
45 p_concatenated_segments out nocopy varchar2
46 ) is
47 --
48 l_item_context_id hr_item_contexts.item_context_id%type;
49 l_concatenated_segments varchar2(2000);
50 l_id_flex_num hr_item_contexts.id_flex_num%type;
51 l_segs_changed boolean;
52 --
53 CURSOR cur_id_flex
54 IS
55 SELECT id_flex_num
56 FROM fnd_id_flex_structures
57 WHERE id_flex_structure_code = p_context_type
58 AND application_id = 800
59 AND id_flex_code = 'ICX';
60
61 l_proc varchar2(72) := g_package||'ins_or_sel';
62 --
63 begin
64 hr_utility.set_location('Entering:'||l_proc, 5);
65 --
66 -- Getting id_flex_num
67 --
68 OPEN cur_id_flex;
69 FETCH cur_id_flex INTO l_id_flex_num;
70 CLOSE cur_id_flex;
71 -- on insert, if any segments are set, but there is no ID_FLEX_NUM,
72 -- raise an error.
73 --
74 IF (p_segment1 is not null) or
75 (p_segment2 is not null) or
76 (p_segment3 is not null) or
77 (p_segment4 is not null) or
78 (p_segment5 is not null) or
79 (p_segment6 is not null) or
80 (p_segment7 is not null) or
81 (p_segment8 is not null) or
82 (p_segment9 is not null) or
83 (p_segment10 is not null) or
84 (p_segment11 is not null) or
85 (p_segment12 is not null) or
86 (p_segment13 is not null) or
87 (p_segment14 is not null) or
88 (p_segment15 is not null) or
89 (p_segment16 is not null) or
90 (p_segment17 is not null) or
91 (p_segment18 is not null) or
92 (p_segment19 is not null) or
93 (p_segment20 is not null) or
94 (p_segment21 is not null) or
95 (p_segment22 is not null) or
96 (p_segment23 is not null) or
97 (p_segment24 is not null) or
98 (p_segment25 is not null) or
99 (p_segment26 is not null) or
100 (p_segment27 is not null) or
101 (p_segment28 is not null) or
102 (p_segment29 is not null) or
103 (p_segment30 is not null) THEN
104 l_segs_changed := true;
105 Else
106 l_segs_changed := false;
107 End if;
108 --
109 IF l_segs_changed and l_id_flex_num is null THEN
110 hr_utility.set_location(l_proc, 20);
111 -- error message
112 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
113 fnd_message.set_token('PROCEDURE', l_proc);
114 fnd_message.set_token('STEP','10');
115 fnd_message.raise_error;
116 End if;
117 --
118 IF l_id_flex_num is null and l_segs_changed = false THEN
119 null;
120 hr_utility.set_location(l_proc, 30);
121 ELSE
122 -- id_flex_num is true at this point.
123 -- Always call AOL code here, as user may be only changing id_flex_num and
124 -- not any segments, so we must ensure this is still validated.
125 hr_utility.set_location(l_proc, 40);
126 --
127 hr_utility.set_location(l_proc, 100);
128 hr_kflex_utility.ins_or_sel_keyflex_comb
129 (p_appl_short_name => 'PER',
130 p_flex_code => 'ICX',
131 p_flex_num => l_id_flex_num,
132 p_segment1 => p_segment1,
133 p_segment2 => p_segment2,
134 p_segment3 => p_segment3,
135 p_segment4 => p_segment4,
136 p_segment5 => p_segment5,
137 p_segment6 => p_segment6,
138 p_segment7 => p_segment7,
139 p_segment8 => p_segment8,
140 p_segment9 => p_segment9,
141 p_segment10 => p_segment10,
142 p_segment11 => p_segment11,
143 p_segment12 => p_segment12,
144 p_segment13 => p_segment13,
145 p_segment14 => p_segment14,
146 p_segment15 => p_segment15,
147 p_segment16 => p_segment16,
148 p_segment17 => p_segment17,
149 p_segment18 => p_segment18,
150 p_segment19 => p_segment19,
151 p_segment20 => p_segment20,
152 p_segment21 => p_segment21,
153 p_segment22 => p_segment22,
154 p_segment23 => p_segment23,
155 p_segment24 => p_segment24,
156 p_segment25 => p_segment25,
157 p_segment26 => p_segment26,
158 p_segment27 => p_segment27,
159 p_segment28 => p_segment28,
160 p_segment29 => p_segment29,
161 p_segment30 => p_segment30,
162 p_concat_segments_in => null,
163 p_ccid => l_item_context_id,
164 p_concat_segments_out => l_concatenated_segments );
165 --
166 p_item_context_id := l_item_context_id;
167 p_concatenated_segments := l_concatenated_segments;
168 End if;
169 hr_utility.set_location(' Leaving:'||l_proc, 60);
170 end ins_or_sel;
171 --
172 -- ----------------------------------------------------------------------------
173 -- |------------------------------< insert_dml >------------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This procedure controls the actual dml insert logic. The processing of
179 -- this procedure are as follows:
180 -- 1) Initialise the object_version_number to 1 if the object_version_number
181 -- is defined as an attribute for this entity.
182 -- 2) To set and unset the g_api_dml status as required (as we are about to
183 -- perform dml).
184 -- 3) To insert the row into the schema.
185 -- 4) To trap any constraint violations that may have occurred.
186 -- 5) To raise any other errors.
187 --
188 -- Prerequisites:
189 -- This is an internal private procedure which must be called from the ins
190 -- procedure and must have all mandatory attributes set (except the
191 -- object_version_number which is initialised within this procedure).
192 --
193 -- In Parameters:
194 -- A Pl/Sql record structre.
195 --
196 -- Post Success:
197 -- The specified row will be inserted into the schema.
198 --
199 -- Post Failure:
200 -- On the insert dml failure it is important to note that we always reset the
201 -- g_api_dml status to false.
202 -- If a check, unique or parent integrity constraint violation is raised the
203 -- constraint_error procedure will be called.
204 -- If any other error is reported, the error will be raised after the
205 -- g_api_dml status is reset.
206 --
207 -- Developer Implementation Notes:
208 -- None.
209 --
210 -- Access Status:
211 -- Internal Row Handler Use Only.
212 --
213 -- {End Of Comments}
214 -- ----------------------------------------------------------------------------
215 Procedure insert_dml
216 (p_rec in out nocopy hr_icx_shd.g_rec_type
217 ) is
218 --
219 l_proc varchar2(72) := g_package||'insert_dml';
220 --
221 Begin
222 hr_utility.set_location('Entering:'||l_proc, 5);
223 p_rec.object_version_number := 1; -- Initialise the object version
224 --
225 --
226 --
227 -- Insert the row into: hr_item_contexts
228 --
229 insert into hr_item_contexts
230 (item_context_id
231 ,object_version_number
232 ,id_flex_num
233 ,summary_flag
234 ,enabled_flag
235 ,start_date_active
236 ,end_date_active
237 ,segment1
238 ,segment2
239 ,segment3
240 ,segment4
241 ,segment5
242 ,segment6
243 ,segment7
244 ,segment8
245 ,segment9
246 ,segment10
247 ,segment11
248 ,segment12
249 ,segment13
250 ,segment14
251 ,segment15
252 ,segment16
253 ,segment17
254 ,segment18
255 ,segment19
256 ,segment20
257 ,segment21
258 ,segment22
259 ,segment23
260 ,segment24
261 ,segment25
262 ,segment26
263 ,segment27
264 ,segment28
265 ,segment29
266 ,segment30
267 )
268 Values
269 (p_rec.item_context_id
270 ,p_rec.object_version_number
271 ,p_rec.id_flex_num
272 ,p_rec.summary_flag
273 ,p_rec.enabled_flag
274 ,p_rec.start_date_active
275 ,p_rec.end_date_active
276 ,p_rec.segment1
277 ,p_rec.segment2
278 ,p_rec.segment3
279 ,p_rec.segment4
280 ,p_rec.segment5
281 ,p_rec.segment6
282 ,p_rec.segment7
283 ,p_rec.segment8
284 ,p_rec.segment9
285 ,p_rec.segment10
286 ,p_rec.segment11
287 ,p_rec.segment12
288 ,p_rec.segment13
289 ,p_rec.segment14
290 ,p_rec.segment15
291 ,p_rec.segment16
292 ,p_rec.segment17
293 ,p_rec.segment18
294 ,p_rec.segment19
295 ,p_rec.segment20
296 ,p_rec.segment21
297 ,p_rec.segment22
298 ,p_rec.segment23
299 ,p_rec.segment24
300 ,p_rec.segment25
301 ,p_rec.segment26
302 ,p_rec.segment27
303 ,p_rec.segment28
304 ,p_rec.segment29
305 ,p_rec.segment30
306 );
307 --
308 --
309 --
310 hr_utility.set_location(' Leaving:'||l_proc, 10);
311 Exception
312 When hr_api.check_integrity_violated Then
313 -- A check constraint has been violated
314 --
315 hr_icx_shd.constraint_error
316 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
317 When hr_api.parent_integrity_violated Then
318 -- Parent integrity has been violated
319 --
320 hr_icx_shd.constraint_error
321 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
322 When hr_api.unique_integrity_violated Then
323 -- Unique integrity has been violated
324 --
325 hr_icx_shd.constraint_error
326 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
327 When Others Then
328 --
329 Raise;
330 End insert_dml;
331 --
332 -- ----------------------------------------------------------------------------
333 -- |------------------------------< pre_insert >------------------------------|
334 -- ----------------------------------------------------------------------------
335 -- {Start Of Comments}
336 --
337 -- Description:
338 -- This private procedure contains any processing which is required before
339 -- the insert dml. Presently, if the entity has a corresponding primary
340 -- key which is maintained by an associating sequence, the primary key for
341 -- the entity will be populated with the next sequence value in
342 -- preparation for the insert dml.
343 --
344 -- Prerequisites:
345 -- This is an internal procedure which is called from the ins procedure.
346 --
347 -- In Parameters:
348 -- A Pl/Sql record structre.
349 --
350 -- Post Success:
351 -- Processing continues.
352 --
353 -- Post Failure:
354 -- If an error has occurred, an error message and exception will be raised
355 -- but not handled.
356 --
357 -- Developer Implementation Notes:
358 -- Any pre-processing required before the insert dml is issued should be
359 -- coded within this procedure. As stated above, a good example is the
360 -- generation of a primary key number via a corresponding sequence.
361 -- It is important to note that any 3rd party maintenance should be reviewed
362 -- before placing in this procedure.
363 --
364 -- Access Status:
365 -- Internal Row Handler Use Only.
366 --
367 -- {End Of Comments}
368 -- ----------------------------------------------------------------------------
369 Procedure pre_insert
370 (p_rec in out nocopy hr_icx_shd.g_rec_type
371 ) is
372 --
373 l_proc varchar2(72) := g_package||'pre_insert';
374 --
375 Cursor C_Sel1 is select hr_item_contexts_s.nextval from sys.dual;
376 --
377 Begin
378 hr_utility.set_location('Entering:'||l_proc, 5);
379 --
380 --
381 -- Select the next sequence number
382 --
383 Open C_Sel1;
384 Fetch C_Sel1 Into p_rec.item_context_id;
385 Close C_Sel1;
386 --
387 hr_utility.set_location(' Leaving:'||l_proc, 10);
388 End pre_insert;
389 --
390 -- ----------------------------------------------------------------------------
391 -- |-----------------------------< post_insert >------------------------------|
392 -- ----------------------------------------------------------------------------
393 -- {Start Of Comments}
394 --
395 -- Description:
396 -- This private procedure contains any processing which is required after the
397 -- insert dml.
398 --
399 -- Prerequisites:
400 -- This is an internal procedure which is called from the ins procedure.
401 --
402 -- In Parameters:
403 -- A Pl/Sql record structre.
404 --
405 -- Post Success:
406 -- Processing continues.
407 --
408 -- Post Failure:
409 -- If an error has occurred, an error message and exception will be raised
410 -- but not handled.
411 --
412 -- Developer Implementation Notes:
413 -- Any post-processing required after the insert dml is issued should be
414 -- coded within this procedure. It is important to note that any 3rd party
415 -- maintenance should be reviewed before placing in this procedure.
416 --
417 -- Access Status:
418 -- Internal Row Handler Use Only.
419 --
420 -- {End Of Comments}
421 -- ----------------------------------------------------------------------------
422 Procedure post_insert
423 (p_effective_date in date
424 ,p_rec in hr_icx_shd.g_rec_type
425 ) is
426 --
427 l_proc varchar2(72) := g_package||'post_insert';
428 --
429 Begin
430 hr_utility.set_location('Entering:'||l_proc, 5);
431 begin
432 --
433 hr_icx_rki.after_insert
434 (p_effective_date => p_effective_date
435 ,p_object_version_number
436 => p_rec.object_version_number
437 ,p_item_context_id
438 => p_rec.item_context_id
439 ,p_id_flex_num
440 => p_rec.id_flex_num
441 ,p_summary_flag
442 => p_rec.summary_flag
443 ,p_enabled_flag
444 => p_rec.enabled_flag
445 ,p_start_date_active
446 => p_rec.start_date_active
447 ,p_end_date_active
448 => p_rec.end_date_active
449 ,p_segment1
450 => p_rec.segment1
451 ,p_segment2
452 => p_rec.segment2
453 ,p_segment3
454 => p_rec.segment3
455 ,p_segment4
456 => p_rec.segment4
457 ,p_segment5
458 => p_rec.segment5
459 ,p_segment6
460 => p_rec.segment6
461 ,p_segment7
462 => p_rec.segment7
463 ,p_segment8
464 => p_rec.segment8
465 ,p_segment9
466 => p_rec.segment9
467 ,p_segment10
468 => p_rec.segment10
469 ,p_segment11
470 => p_rec.segment11
471 ,p_segment12
472 => p_rec.segment12
473 ,p_segment13
474 => p_rec.segment13
475 ,p_segment14
476 => p_rec.segment14
477 ,p_segment15
478 => p_rec.segment15
479 ,p_segment16
480 => p_rec.segment16
481 ,p_segment17
482 => p_rec.segment17
483 ,p_segment18
484 => p_rec.segment18
485 ,p_segment19
486 => p_rec.segment19
487 ,p_segment20
488 => p_rec.segment20
489 ,p_segment21
490 => p_rec.segment21
491 ,p_segment22
492 => p_rec.segment22
493 ,p_segment23
494 => p_rec.segment23
495 ,p_segment24
496 => p_rec.segment24
497 ,p_segment25
498 => p_rec.segment25
499 ,p_segment26
500 => p_rec.segment26
501 ,p_segment27
502 => p_rec.segment27
503 ,p_segment28
504 => p_rec.segment28
505 ,p_segment29
506 => p_rec.segment29
507 ,p_segment30
508 => p_rec.segment30
509 );
510 --
511 exception
512 --
513 when hr_api.cannot_find_prog_unit then
514 --
515 hr_api.cannot_find_prog_unit_error
516 (p_module_name => 'HR_ITEM_CONTEXTS'
517 ,p_hook_type => 'AI');
518 --
519 end;
520 --
521 hr_utility.set_location(' Leaving:'||l_proc, 10);
522 End post_insert;
523 --
524 -- ----------------------------------------------------------------------------
525 -- |---------------------------------< ins >----------------------------------|
526 -- ----------------------------------------------------------------------------
527 Procedure ins
528 (p_effective_date in date
529 ,p_rec in out nocopy hr_icx_shd.g_rec_type
530 ) is
531 --
532 l_proc varchar2(72) := g_package||'ins';
533 --
534 Begin
535 hr_utility.set_location('Entering:'||l_proc, 5);
536 --
537 -- Call the supporting insert validate operations
538 --
539 hr_icx_bus.insert_validate
540 (p_effective_date
541 ,p_rec
542 );
543 --
544 -- Call the supporting pre-insert operation
545 --
546 hr_icx_ins.pre_insert(p_rec);
547 --
548 -- Insert the row
549 --
550 hr_icx_ins.insert_dml(p_rec);
551 --
552 -- Call the supporting post-insert operation
553 --
554 hr_icx_ins.post_insert
555 (p_effective_date
556 ,p_rec
557 );
558 --
559 hr_utility.set_location('Leaving:'||l_proc, 20);
560
561 end ins;
562 --
563 -- ----------------------------------------------------------------------------
564 -- |---------------------------------< ins >----------------------------------|
565 -- ----------------------------------------------------------------------------
566 Procedure ins
567 (p_effective_date in date
568 ,p_id_flex_num in number
569 ,p_summary_flag in varchar2
570 ,p_enabled_flag in varchar2
571 ,p_start_date_active in date default null
572 ,p_end_date_active in date default null
573 ,p_segment1 in varchar2 default null
574 ,p_segment2 in varchar2 default null
575 ,p_segment3 in varchar2 default null
576 ,p_segment4 in varchar2 default null
577 ,p_segment5 in varchar2 default null
578 ,p_segment6 in varchar2 default null
579 ,p_segment7 in varchar2 default null
580 ,p_segment8 in varchar2 default null
581 ,p_segment9 in varchar2 default null
582 ,p_segment10 in varchar2 default null
583 ,p_segment11 in varchar2 default null
584 ,p_segment12 in varchar2 default null
585 ,p_segment13 in varchar2 default null
586 ,p_segment14 in varchar2 default null
587 ,p_segment15 in varchar2 default null
588 ,p_segment16 in varchar2 default null
589 ,p_segment17 in varchar2 default null
590 ,p_segment18 in varchar2 default null
591 ,p_segment19 in varchar2 default null
592 ,p_segment20 in varchar2 default null
593 ,p_segment21 in varchar2 default null
594 ,p_segment22 in varchar2 default null
595 ,p_segment23 in varchar2 default null
596 ,p_segment24 in varchar2 default null
597 ,p_segment25 in varchar2 default null
598 ,p_segment26 in varchar2 default null
599 ,p_segment27 in varchar2 default null
600 ,p_segment28 in varchar2 default null
601 ,p_segment29 in varchar2 default null
602 ,p_segment30 in varchar2 default null
603 ,p_item_context_id out nocopy number
604 ,p_object_version_number out nocopy number
605 ) is
606 --
607 l_rec hr_icx_shd.g_rec_type;
608 l_proc varchar2(72) := g_package||'ins';
609 --
610 Begin
611 hr_utility.set_location('Entering:'||l_proc, 5);
612 --
613 -- Call conversion function to turn arguments into the
614 -- p_rec structure.
615 --
616 l_rec :=
617 hr_icx_shd.convert_args
618 (null
619 ,p_id_flex_num
620 ,p_object_version_number
621 ,p_summary_flag
622 ,p_enabled_flag
623 ,p_start_date_active
624 ,p_end_date_active
625 ,p_segment1
626 ,p_segment2
627 ,p_segment3
628 ,p_segment4
629 ,p_segment5
630 ,p_segment6
631 ,p_segment7
632 ,p_segment8
633 ,p_segment9
634 ,p_segment10
635 ,p_segment11
636 ,p_segment12
637 ,p_segment13
638 ,p_segment14
639 ,p_segment15
640 ,p_segment16
641 ,p_segment17
642 ,p_segment18
643 ,p_segment19
644 ,p_segment20
645 ,p_segment21
646 ,p_segment22
647 ,p_segment23
648 ,p_segment24
649 ,p_segment25
650 ,p_segment26
651 ,p_segment27
652 ,p_segment28
653 ,p_segment29
654 ,p_segment30
655 );
656 --
657 -- Having converted the arguments into the hr_icx_rec
658 -- plsql record structure we call the corresponding record business process.
659 --
660 hr_icx_ins.ins
661 (p_effective_date
662 ,l_rec
663 );
664 --
665 -- As the primary key argument(s)
666 -- are specified as an OUT's we must set these values.
667 --
668 p_item_context_id := l_rec.item_context_id;
669 p_object_version_number := l_rec.object_version_number;
670 --
671 --
672 hr_utility.set_location(' Leaving:'||l_proc, 10);
673 End ins;
674 --
675 end hr_icx_ins;