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