1 Package Body irc_iid_ins as
2 /* $Header: iriidrhi.pkb 120.3.12010000.2 2008/11/06 13:49:47 mkjayara ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iid_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_interview_details_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_interview_details_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_iid_ins.g_interview_details_id_i := p_interview_details_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |----------------------------< dt_insert_dml >-----------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic for datetrack. The
39 -- functions of this procedure are as follows:
40 -- 1) Get the object_version_number.
41 -- 2) To set the effective start and end dates to the corresponding
42 -- validation start and end dates. Also, the object version number
43 -- record attribute is set.
44 -- 3) To set and unset the g_api_dml status as required (as we are about to
45 -- perform dml).
46 -- 4) To insert the row into the schema with the derived effective start
47 -- and end dates and the object version number.
48 -- 5) To trap any constraint violations that may have occurred.
49 -- 6) To raise any other errors.
50 --
51 -- Prerequisites:
52 -- This is an internal private procedure which must be called from the
53 -- insert_dml and pre_update (logic permitting) procedure and must have
54 -- all mandatory arguments set.
55 --
56 -- In Parameters:
57 -- A Pl/Sql record structure.
58 --
59 -- Post Success:
60 -- The specified row will be inserted into the schema.
61 --
62 -- Post Failure:
63 -- On the insert dml failure it is important to note that we always reset the
64 -- g_api_dml status to false.
65 -- If a check or unique integrity constraint violation is raised the
66 -- constraint_error procedure will be called.
67 -- If any other error is reported, the error will be raised after the
68 -- g_api_dml status is reset.
69 --
70 -- Developer Implementation Notes:
71 -- This is an internal datetrack maintenance procedure which should
72 -- not be modified in anyway.
73 --
74 -- Access Status:
75 -- Internal Row Handler Use Only.
76 --
77 -- {End Of Comments}
78 -- ----------------------------------------------------------------------------
79 Procedure dt_insert_dml
80 (p_rec in out nocopy irc_iid_shd.g_rec_type
81 ,p_effective_date in date
82 ,p_datetrack_mode in varchar2
83 ,p_validation_start_date in date
84 ,p_validation_end_date in date
85 ) is
86 -- Cursor to select 'old' created AOL who column values
87 --
88 Cursor C_Sel1 Is
89 select t.creation_date
90 from irc_interview_details t
91 where t.interview_details_id = p_rec.interview_details_id
92 and t.start_date =
93 irc_iid_shd.g_old_rec.start_date
94 and t.end_date = p_validation_start_date;
95 --
96 --
97 l_proc varchar2(72) := g_package||'dt_insert_dml';
98 l_creation_date irc_interview_details.creation_date%TYPE;
99 l_current_user_id integer;
100 l_temp_count integer;
101 --
102 Begin
103 hr_utility.set_location('Entering:'||l_proc, 5);
104 --
105 -- Set the effective start and end dates to the corresponding
106 -- validation start and end dates
107 --
108 p_rec.start_date := p_validation_start_date;
109 p_rec.end_date := p_validation_end_date;
110 l_current_user_id := fnd_global.user_id;
111 --
112 -- If the datetrack_mode is not INSERT then we must populate the WHO
113 -- columns with the 'old' creation values and 'new' updated values.
114 --
115 If (p_datetrack_mode <> hr_api.g_insert) then
116 hr_utility.set_location(l_proc, 10);
117 --
118 -- Get the object version number for the insert
119 --
120 p_rec.object_version_number :=
121 irc_iid_shd.get_object_version_number
122 (p_interview_details_id => p_rec.interview_details_id
123 );
124 --
125 -- Select the 'old' created values
126 --
127 Open C_Sel1;
128 Fetch C_Sel1 Into l_creation_date;
129 If C_Sel1%notfound Then
130 --
131 -- The previous 'old' created row has not been found. We need
132 -- to error as an internal datetrack problem exists.
133 --
134 Close C_Sel1;
135 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
136 fnd_message.set_token('PROCEDURE', l_proc);
137 fnd_message.set_token('STEP','10');
138 fnd_message.raise_error;
139 End If;
140 Close C_Sel1;
141 --
142 --
143 -- Set the AOL updated WHO values
144 --
145 Else
146 p_rec.object_version_number := 1; -- Initialise the object version
147 End If;
148 --
149 --
150 --
151 -- Insert the row into: irc_interview_details
152 --
153 insert into irc_interview_details
154 (interview_details_id
155 ,status
156 ,feedback
157 ,notes
158 ,notes_to_candidate
159 ,category
160 ,result
161 ,iid_information_category
162 ,iid_information1
163 ,iid_information2
164 ,iid_information3
165 ,iid_information4
166 ,iid_information5
167 ,iid_information6
168 ,iid_information7
169 ,iid_information8
170 ,iid_information9
171 ,iid_information10
172 ,iid_information11
173 ,iid_information12
174 ,iid_information13
175 ,iid_information14
176 ,iid_information15
177 ,iid_information16
178 ,iid_information17
179 ,iid_information18
180 ,iid_information19
181 ,iid_information20
182 ,start_date
183 ,end_date
184 ,event_id
185 ,object_version_number
186 )
187 Values
188 (p_rec.interview_details_id
189 ,p_rec.status
190 ,p_rec.feedback
191 ,p_rec.notes
192 ,p_rec.notes_to_candidate
193 ,p_rec.category
194 ,p_rec.result
195 ,p_rec.iid_information_category
196 ,p_rec.iid_information1
197 ,p_rec.iid_information2
198 ,p_rec.iid_information3
199 ,p_rec.iid_information4
200 ,p_rec.iid_information5
201 ,p_rec.iid_information6
202 ,p_rec.iid_information7
203 ,p_rec.iid_information8
204 ,p_rec.iid_information9
205 ,p_rec.iid_information10
206 ,p_rec.iid_information11
207 ,p_rec.iid_information12
208 ,p_rec.iid_information13
209 ,p_rec.iid_information14
210 ,p_rec.iid_information15
211 ,p_rec.iid_information16
212 ,p_rec.iid_information17
213 ,p_rec.iid_information18
214 ,p_rec.iid_information19
215 ,p_rec.iid_information20
216 ,p_rec.start_date
217 ,p_rec.end_date
218 ,p_rec.event_id
219 ,p_rec.object_version_number
220 );
221 --
222 --
223 hr_utility.set_location(' Leaving:'||l_proc, 15);
224 --
225 Exception
226 When hr_api.check_integrity_violated Then
227 -- A check constraint has been violated
228 --
229 irc_iid_shd.constraint_error
230 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
231 When hr_api.unique_integrity_violated Then
232 -- Unique integrity has been violated
233 --
234 irc_iid_shd.constraint_error
235 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
236 When Others Then
237 --
238 Raise;
239 End dt_insert_dml;
240 --
241 -- ----------------------------------------------------------------------------
242 -- |------------------------------< insert_dml >------------------------------|
243 -- ----------------------------------------------------------------------------
244 Procedure insert_dml
245 (p_rec in out nocopy irc_iid_shd.g_rec_type
246 ,p_effective_date in date
247 ,p_datetrack_mode in varchar2
248 ,p_validation_start_date in date
249 ,p_validation_end_date in date
250 ) is
251 --
252 l_proc varchar2(72) := g_package||'insert_dml';
253 --
254 Begin
255 hr_utility.set_location('Entering:'||l_proc, 5);
256 --
257 irc_iid_ins.dt_insert_dml
258 (p_rec => p_rec
259 ,p_effective_date => p_effective_date
260 ,p_datetrack_mode => p_datetrack_mode
261 ,p_validation_start_date => p_validation_start_date
262 ,p_validation_end_date => p_validation_end_date
263 );
264 --
265 hr_utility.set_location(' Leaving:'||l_proc, 10);
266 End insert_dml;
267 --
268 -- ----------------------------------------------------------------------------
269 -- |------------------------------< pre_insert >------------------------------|
270 -- ----------------------------------------------------------------------------
271 -- {Start Of Comments}
272 --
273 -- Description:
274 -- This private procedure contains any processing which is required before
275 -- the insert dml. Presently, if the entity has a corresponding primary
276 -- key which is maintained by an associating sequence, the primary key for
277 -- the entity will be populated with the next sequence value in
278 -- preparation for the insert dml.
279 -- Also, if comments are defined for this entity, the comments insert
280 -- logic will also be called, generating a comment_id if required.
281 --
282 -- Prerequisites:
283 -- This is an internal procedure which is called from the ins procedure.
284 --
285 -- In Parameters:
286 -- A Pl/Sql record structure.
287 --
288 -- Post Success:
289 -- Processing continues.
290 --
291 -- Post Failure:
292 -- If an error has occurred, an error message and exception will be raised
293 -- but not handled.
294 --
295 -- Developer Implementation Notes:
296 -- Any pre-processing required before the insert dml is issued should be
297 -- coded within this procedure. As stated above, a good example is the
298 -- generation of a primary key number via a corresponding sequence.
299 -- It is important to note that any 3rd party maintenance should be reviewed
300 -- before placing in this procedure.
301 --
302 -- Access Status:
303 -- Internal Row Handler Use Only.
304 --
305 -- {End Of Comments}
306 -- ----------------------------------------------------------------------------
307 Procedure pre_insert
308 (p_rec in out nocopy irc_iid_shd.g_rec_type
309 ,p_effective_date in date
310 ,p_datetrack_mode in varchar2
311 ,p_validation_start_date in date
312 ,p_validation_end_date in date
313 ) is
314 --
315 Cursor C_Sel1 is select irc_interview_details_s.nextval from sys.dual;
316 --
317 Cursor C_Sel2 is
318 Select null
319 from irc_interview_details
320 where interview_details_id =
321 irc_iid_ins.g_interview_details_id_i
322 and p_effective_date between start_date
323 and end_date;
324 --
325 l_proc varchar2(72) := g_package||'pre_insert';
326 l_exists varchar2(1);
327 --
328 Begin
329 hr_utility.set_location('Entering:'||l_proc, 5);
330 --
331 If (irc_iid_ins.g_interview_details_id_i is not null) Then
332 --
333 -- Verify registered primary key values not already in use
334 --
335 Open C_Sel2;
336 Fetch C_Sel2 into l_exists;
337 If C_Sel2%found Then
338 Close C_Sel2;
339 --
340 -- The primary key values are already in use.
341 --
342 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
343 fnd_message.set_token('TABLE_NAME','irc_interview_details');
344 fnd_message.raise_error;
345 End If;
346 Close C_Sel2;
347 --
348 -- Use registered key values and clear globals
349 --
350 p_rec.interview_details_id :=
351 irc_iid_ins.g_interview_details_id_i;
352 irc_iid_ins.g_interview_details_id_i := null;
353 Else
354 --
355 -- No registerd key values, so select the next sequence number
356 --
357 --
358 -- Select the next sequence number
359 --
360 Open C_Sel1;
361 Fetch C_Sel1 Into p_rec.interview_details_id;
362 Close C_Sel1;
363 End If;
364 --
365 --
366 hr_utility.set_location(' Leaving:'||l_proc, 10);
367 End pre_insert;
368 --
369 -- ----------------------------------------------------------------------------
370 -- |----------------------------< post_insert >-------------------------------|
371 -- ----------------------------------------------------------------------------
372 -- {Start Of Comments}
373 --
374 -- Description:
375 -- This private procedure contains any processing which is required after
376 -- the insert dml.
377 --
378 -- Prerequisites:
379 -- This is an internal procedure which is called from the ins procedure.
380 --
381 -- In Parameters:
382 -- A Pl/Sql record structure.
383 --
384 -- Post Success:
385 -- Processing continues.
386 --
387 -- Post Failure:
388 -- If an error has occurred, an error message and exception will be raised
389 -- but not handled.
390 --
391 -- Developer Implementation Notes:
392 -- Any post-processing required after the insert dml is issued should be
393 -- coded within this procedure. It is important to note that any 3rd party
394 -- maintenance should be reviewed before placing in this procedure.
395 --
396 -- Access Status:
397 -- Internal Row Handler Use Only.
398 --
399 -- {End Of Comments}
400 -- ----------------------------------------------------------------------------
401 Procedure post_insert
402 (p_rec in irc_iid_shd.g_rec_type
403 ,p_effective_date in date
404 ,p_datetrack_mode in varchar2
405 ,p_validation_start_date in date
406 ,p_validation_end_date in date
407 ) is
408 --
409 l_proc varchar2(72) := g_package||'post_insert';
410 --
411 Begin
412 hr_utility.set_location('Entering:'||l_proc, 5);
413 begin
414 --
415 irc_iid_rki.after_insert
416 (p_effective_date => p_effective_date
417 ,p_validation_start_date => p_validation_start_date
418 ,p_validation_end_date => p_validation_end_date
419 ,p_interview_details_id => p_rec.interview_details_id
420 ,p_status => p_rec.status
421 ,p_feedback => p_rec.feedback
422 ,p_notes => p_rec.notes
423 ,p_notes_to_candidate => p_rec.notes_to_candidate
424 ,p_category => p_rec.category
425 ,p_result => p_rec.result
426 ,p_iid_information_category => p_rec.iid_information_category
427 ,p_iid_information1 => p_rec.iid_information1
428 ,p_iid_information2 => p_rec.iid_information2
429 ,p_iid_information3 => p_rec.iid_information3
430 ,p_iid_information4 => p_rec.iid_information4
431 ,p_iid_information5 => p_rec.iid_information5
432 ,p_iid_information6 => p_rec.iid_information6
433 ,p_iid_information7 => p_rec.iid_information7
434 ,p_iid_information8 => p_rec.iid_information8
435 ,p_iid_information9 => p_rec.iid_information9
436 ,p_iid_information10 => p_rec.iid_information10
437 ,p_iid_information11 => p_rec.iid_information11
438 ,p_iid_information12 => p_rec.iid_information12
439 ,p_iid_information13 => p_rec.iid_information13
440 ,p_iid_information14 => p_rec.iid_information14
441 ,p_iid_information15 => p_rec.iid_information15
442 ,p_iid_information16 => p_rec.iid_information16
443 ,p_iid_information17 => p_rec.iid_information17
444 ,p_iid_information18 => p_rec.iid_information18
445 ,p_iid_information19 => p_rec.iid_information19
446 ,p_iid_information20 => p_rec.iid_information20
447 ,p_start_date => p_rec.start_date
448 ,p_end_date => p_rec.end_date
449 ,p_event_id => p_rec.event_id
450 ,p_object_version_number => p_rec.object_version_number
451 );
452 --
453 exception
454 --
455 when hr_api.cannot_find_prog_unit then
456 --
457 hr_api.cannot_find_prog_unit_error
458 (p_module_name => 'IRC_INTERVIEW_DETAILS'
459 ,p_hook_type => 'AI');
460 --
461 end;
462 --
463 hr_utility.set_location(' Leaving:'||l_proc, 10);
464 End post_insert;
465 --
466 -- ----------------------------------------------------------------------------
467 -- |-------------------------------< ins_lck >--------------------------------|
468 -- ----------------------------------------------------------------------------
469 -- {Start Of Comments}
470 --
471 -- Description:
472 -- The ins_lck process has one main function to perform. When inserting
473 -- a datetracked row, we must validate the DT mode.
474 --
475 -- Prerequisites:
476 -- This procedure can only be called for the datetrack mode of INSERT.
477 --
478 -- In Parameters:
479 --
480 -- Post Success:
481 -- On successful completion of the ins_lck process the parental
482 -- datetracked rows will be locked providing the p_enforce_foreign_locking
483 -- argument value is TRUE.
484 -- If the p_enforce_foreign_locking argument value is FALSE then the
485 -- parential rows are not locked.
486 --
487 -- Post Failure:
488 -- The Lck process can fail for:
489 -- 1) When attempting to lock the row the row could already be locked by
490 -- another user. This will raise the HR_Api.Object_Locked exception.
491 -- 2) When attempting to the lock the parent which doesn't exist.
492 -- For the entity to be locked the parent must exist!
493 --
494 -- Developer Implementation Notes:
495 -- None.
496 --
497 -- Access Status:
498 -- Internal Row Handler Use Only.
499 --
500 -- {End Of Comments}
501 -- ----------------------------------------------------------------------------
502 Procedure ins_lck
503 (p_effective_date in date
504 ,p_datetrack_mode in varchar2
505 ,p_rec in irc_iid_shd.g_rec_type
506 ,p_validation_start_date out nocopy date
507 ,p_validation_end_date out nocopy date
508 ) is
509 --
510 l_proc varchar2(72) := g_package||'ins_lck';
511 l_validation_start_date date;
512 l_validation_end_date date;
513 --
514 Begin
515 hr_utility.set_location('Entering:'||l_proc, 5);
516 --
517 -- Validate the datetrack mode mode getting the validation start
518 -- and end dates for the specified datetrack operation.
519 --
520 --
521 -- Set the validation start and end date OUT arguments
522 --
523 -- p_validation_start_date := l_validation_start_date;
524 -- p_validation_end_date := l_validation_end_date;
525 -- MURTHY_CHANGES
526 p_validation_start_date := sysdate;
527 p_validation_end_date := hr_general.end_of_time;
528 --
529 hr_utility.set_location(' Leaving:'||l_proc, 10);
530 --
531 End ins_lck;
532 --
533 -- ----------------------------------------------------------------------------
534 -- |---------------------------------< ins >----------------------------------|
535 -- ----------------------------------------------------------------------------
536 Procedure ins
537 (p_effective_date in date
538 ,p_rec in out nocopy irc_iid_shd.g_rec_type
539 ) is
540 --
541 l_proc varchar2(72) := g_package||'ins';
542 l_datetrack_mode varchar2(30) := hr_api.g_insert;
543 l_validation_start_date date;
544 l_validation_end_date date;
545 --
546 Begin
547 hr_utility.set_location('Entering:'||l_proc, 5);
548 --
549 -- Call the lock operation
550 --
551 irc_iid_ins.ins_lck
552 (p_effective_date => p_effective_date
553 ,p_datetrack_mode => l_datetrack_mode
554 ,p_rec => p_rec
555 ,p_validation_start_date => l_validation_start_date
556 ,p_validation_end_date => l_validation_end_date
557 );
558 --
559 -- Call the supporting insert validate operations
560 --
561 irc_iid_bus.insert_validate
562 (p_rec => p_rec
563 ,p_effective_date => p_effective_date
564 ,p_datetrack_mode => l_datetrack_mode
565 ,p_validation_start_date => l_validation_start_date
566 ,p_validation_end_date => l_validation_end_date
567 );
568 --
569 -- Call to raise any errors on multi-message list
570 hr_multi_message.end_validation_set;
571 --
572 -- Call the supporting pre-insert operation
573 --
574 irc_iid_ins.pre_insert
575 (p_rec => p_rec
576 ,p_effective_date => p_effective_date
577 ,p_datetrack_mode => l_datetrack_mode
578 ,p_validation_start_date => l_validation_start_date
579 ,p_validation_end_date => l_validation_end_date
580 );
581 --
582 -- Insert the row
583 --
584 irc_iid_ins.insert_dml
585 (p_rec => p_rec
586 ,p_effective_date => p_effective_date
587 ,p_datetrack_mode => l_datetrack_mode
588 ,p_validation_start_date => l_validation_start_date
589 ,p_validation_end_date => l_validation_end_date
590 );
591 --
592 -- Call the supporting post-insert operation
593 --
594 irc_iid_ins.post_insert
595 (p_rec => p_rec
596 ,p_effective_date => p_effective_date
597 ,p_datetrack_mode => l_datetrack_mode
598 ,p_validation_start_date => l_validation_start_date
599 ,p_validation_end_date => l_validation_end_date
600 );
601 --
602 -- Call to raise any errors on multi-message list
603 hr_multi_message.end_validation_set;
604 --
605 hr_utility.set_location('Leaving:'||l_proc,10);
606 end ins;
607 --
608 -- ----------------------------------------------------------------------------
609 -- |---------------------------------< ins >----------------------------------|
610 -- ----------------------------------------------------------------------------
611 Procedure ins
612 (p_effective_date in date
613 ,p_status in varchar2
614 ,p_feedback in varchar2
615 ,p_notes in varchar2
616 ,p_notes_to_candidate in varchar2
617 ,p_category in varchar2
618 ,p_result in varchar2
619 ,p_iid_information_category in varchar2
620 ,p_iid_information1 in varchar2
621 ,p_iid_information2 in varchar2
622 ,p_iid_information3 in varchar2
623 ,p_iid_information4 in varchar2
624 ,p_iid_information5 in varchar2
625 ,p_iid_information6 in varchar2
626 ,p_iid_information7 in varchar2
627 ,p_iid_information8 in varchar2
628 ,p_iid_information9 in varchar2
629 ,p_iid_information10 in varchar2
630 ,p_iid_information11 in varchar2
631 ,p_iid_information12 in varchar2
632 ,p_iid_information13 in varchar2
633 ,p_iid_information14 in varchar2
634 ,p_iid_information15 in varchar2
635 ,p_iid_information16 in varchar2
636 ,p_iid_information17 in varchar2
637 ,p_iid_information18 in varchar2
638 ,p_iid_information19 in varchar2
639 ,p_iid_information20 in varchar2
640 ,p_event_id in number default null
641 ,p_interview_details_id out nocopy number
642 ,p_object_version_number out nocopy number
643 ,p_start_date out nocopy date
644 ,p_end_date out nocopy date
645 ) is
646 --
647 l_rec irc_iid_shd.g_rec_type;
648 l_proc varchar2(72) := g_package||'ins';
649 --
650 Begin
651 hr_utility.set_location('Entering:'||l_proc, 5);
652 --
653 -- Call conversion function to turn arguments into the
654 -- p_rec structure.
655 --
656 l_rec :=
657 irc_iid_shd.convert_args
658 (null
659 ,p_status
660 ,p_feedback
661 ,p_notes
662 ,p_notes_to_candidate
663 ,p_category
664 ,p_result
665 ,p_iid_information_category
666 ,p_iid_information1
667 ,p_iid_information2
668 ,p_iid_information3
669 ,p_iid_information4
670 ,p_iid_information5
671 ,p_iid_information6
672 ,p_iid_information7
673 ,p_iid_information8
674 ,p_iid_information9
675 ,p_iid_information10
676 ,p_iid_information11
677 ,p_iid_information12
678 ,p_iid_information13
679 ,p_iid_information14
680 ,p_iid_information15
681 ,p_iid_information16
682 ,p_iid_information17
683 ,p_iid_information18
684 ,p_iid_information19
685 ,p_iid_information20
686 ,p_start_date
687 ,p_end_date
688 ,p_event_id
689 ,null
690 );
691 --
692 -- Having converted the arguments into the irc_iid_rec
693 -- plsql record structure we call the corresponding record
694 -- business process.
695 --
696 irc_iid_ins.ins
697 (p_effective_date
698 ,l_rec
699 );
700 --
701 -- Set the OUT arguments.
702 --
703 p_interview_details_id := l_rec.interview_details_id;
704 p_start_date := l_rec.start_date;
705 p_end_date := l_rec.end_date;
706 p_object_version_number := l_rec.object_version_number;
707 --
708 --
709 hr_utility.set_location(' Leaving:'||l_proc, 10);
710 End ins;
711 --
712 end irc_iid_ins;
[Home] [Help]