1 Package Body ame_aty_ins as
2 /* $Header: amatyrhi.pkb 120.4 2005/11/22 03:14 santosin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_aty_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_action_type_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_action_type_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 ame_aty_ins.g_action_type_id_i := p_action_type_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 ame_aty_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 ame_action_types t
91 where t.action_type_id = p_rec.action_type_id
92 and t.start_date = ame_aty_shd.g_old_rec.start_date
93 and t.end_date = p_validation_start_date;
94 --
95 Cursor C_Sel2 Is
96 select created_by
97 from ame_action_types t
98 where t.action_type_id = p_rec.action_type_id
99 and ame_utility_pkg.is_seed_user(created_by) = ame_util.seededDataCreatedById
100 and rownum<2;
101 --
102 l_proc varchar2(72) := g_package||'dt_insert_dml';
103 l_created_by ame_action_types.created_by%TYPE;
104 l_creation_date ame_action_types.creation_date%TYPE;
105 l_last_update_date ame_action_types.last_update_date%TYPE;
106 l_last_updated_by ame_action_types.last_updated_by%TYPE;
107 l_last_update_login ame_action_types.last_update_login%TYPE;
108 l_current_user_id integer;
109 l_temp_count integer;
110 --
111 Begin
112 hr_utility.set_location('Entering:'||l_proc, 5);
113 --
114 -- Set the effective start and end dates to the corresponding
115 -- validation start and end dates
116 --
117 p_rec.start_date := p_validation_start_date;
118 p_rec.end_date := p_validation_end_date;
119 l_current_user_id := fnd_global.user_id;
120 --
121 -- If the datetrack_mode is not INSERT then we must populate the WHO
122 -- columns with the 'old' creation values and 'new' updated values.
123 --
124 If (p_datetrack_mode <> hr_api.g_insert) then
125 hr_utility.set_location(l_proc, 10);
126 --
127 -- Get the object version number for the insert
128 --
129 p_rec.object_version_number :=
130 ame_aty_shd.get_object_version_number
131 (p_action_type_id => p_rec.action_type_id
132 );
133 --
134 -- Select the 'old' created values
135 --
136 Open C_Sel1;
137 Fetch C_Sel1 Into l_creation_date;
138 If C_Sel1%notfound Then
139 --
140 -- The previous 'old' created row has not been found. We need
141 -- to error as an internal datetrack problem exists.
142 --
143 Close C_Sel1;
144 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
145 fnd_message.set_token('PROCEDURE', l_proc);
146 fnd_message.set_token('STEP','10');
147 fnd_message.raise_error;
148 End If;
149 Close C_Sel1;
150 --
151 --
152 Open C_Sel2;
153 Fetch C_Sel2 Into l_temp_count;
154 if C_Sel2%found then
155 l_created_by := l_temp_count;
156 else
157 l_created_by := l_current_user_id;
158 end if;
159 Close C_Sel2;
160 --
161 -- Set the AOL updated WHO values
162 --
163 l_last_update_date := sysdate;
164 l_last_updated_by := l_current_user_id;
165 l_last_update_login := l_current_user_id;
166 Else
167 p_rec.object_version_number := 1; -- Initialise the object version
168 -- EDIT_HERE -- need to be modified to ame_util.seededDataCreatedById the
169 -- user has developer responsibility according to AME standards.
170 -- If the current user logged in using AME Developer responsibility
171 -- then the created_by value should be ame_util.seededDataCreatedById
172 --
173 if fnd_global.resp_name = 'AME Developer' then
174 l_created_by := ame_util.seededDataCreatedById;
175 else
176 l_created_by := l_current_user_id;
177 end if;
178
179 l_creation_date := sysdate;
180 l_last_update_date := sysdate;
181 l_last_updated_by := l_current_user_id;
182 l_last_update_login := l_current_user_id;
183 End If;
184 --
185 --
186 --
187 -- Insert the row into: ame_action_types
188 --
189 insert into ame_action_types
190 (action_type_id
191 ,name
192 ,procedure_name
193 ,start_date
194 ,end_date
195 ,description
196 ,security_group_id
197 ,dynamic_description
198 ,description_query
199 ,object_version_number
200 ,created_by
201 ,creation_date
202 ,last_update_date
203 ,last_updated_by
204 ,last_update_login
205 )
206 Values
207 (p_rec.action_type_id
208 ,p_rec.name
209 ,p_rec.procedure_name
210 ,p_rec.start_date
211 ,p_rec.end_date
212 ,p_rec.description
213 ,p_rec.security_group_id
214 ,p_rec.dynamic_description
215 ,p_rec.description_query
216 ,p_rec.object_version_number
217 ,l_created_by
218 ,l_creation_date
219 ,l_last_update_date
220 ,l_last_updated_by
221 ,l_last_update_login
222 );
223 --
224 --
225 hr_utility.set_location(' Leaving:'||l_proc, 15);
226 --
227 Exception
228 When hr_api.check_integrity_violated Then
229 -- A check constraint has been violated
230 --
231 ame_aty_shd.constraint_error
232 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
233 When hr_api.unique_integrity_violated Then
234 -- Unique integrity has been violated
235 --
236 ame_aty_shd.constraint_error
237 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
238 When Others Then
239 --
240 Raise;
241 End dt_insert_dml;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |------------------------------< insert_dml >------------------------------|
245 -- ----------------------------------------------------------------------------
246 Procedure insert_dml
247 (p_rec in out nocopy ame_aty_shd.g_rec_type
248 ,p_effective_date in date
249 ,p_datetrack_mode in varchar2
250 ,p_validation_start_date in date
251 ,p_validation_end_date in date
252 ) is
253 --
254 l_proc varchar2(72) := g_package||'insert_dml';
255 --
256 Begin
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 ame_aty_ins.dt_insert_dml
260 (p_rec => p_rec
261 ,p_effective_date => p_effective_date
262 ,p_datetrack_mode => p_datetrack_mode
263 ,p_validation_start_date => p_validation_start_date
264 ,p_validation_end_date => p_validation_end_date
265 );
266 --
267 hr_utility.set_location(' Leaving:'||l_proc, 10);
268 End insert_dml;
269 --
270 -- ----------------------------------------------------------------------------
271 -- |------------------------------< pre_insert >------------------------------|
272 -- ----------------------------------------------------------------------------
273 -- {Start Of Comments}
274 --
275 -- Description:
276 -- This private procedure contains any processing which is required before
277 -- the insert dml. Presently, if the entity has a corresponding primary
278 -- key which is maintained by an associating sequence, the primary key for
279 -- the entity will be populated with the next sequence value in
280 -- preparation for the insert dml.
281 -- Also, if comments are defined for this entity, the comments insert
282 -- logic will also be called, generating a comment_id if required.
283 --
284 -- Prerequisites:
285 -- This is an internal procedure which is called from the ins procedure.
286 --
287 -- In Parameters:
288 -- A Pl/Sql record structure.
289 --
290 -- Post Success:
291 -- Processing continues.
292 --
293 -- Post Failure:
294 -- If an error has occurred, an error message and exception will be raised
295 -- but not handled.
296 --
297 -- Developer Implementation Notes:
298 -- Any pre-processing required before the insert dml is issued should be
299 -- coded within this procedure. As stated above, a good example is the
300 -- generation of a primary key number via a corresponding sequence.
301 -- It is important to note that any 3rd party maintenance should be reviewed
302 -- before placing in this procedure.
303 --
304 -- Access Status:
305 -- Internal Row Handler Use Only.
306 --
307 -- {End Of Comments}
308 -- ----------------------------------------------------------------------------
309 Procedure pre_insert
310 (p_rec in out nocopy ame_aty_shd.g_rec_type
311 ,p_effective_date in date
312 ,p_datetrack_mode in varchar2
313 ,p_validation_start_date in date
314 ,p_validation_end_date in date
315 ) is
316 --
317 Cursor C_Sel1 is select ame_action_types_s.nextval from sys.dual;
318 --
319 Cursor C_Sel2 is
320 Select null
321 from ame_action_types
322 where action_type_id =
323 ame_aty_ins.g_action_type_id_i;
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 (ame_aty_ins.g_action_type_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','ame_action_types');
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.action_type_id :=
351 ame_aty_ins.g_action_type_id_i;
352 ame_aty_ins.g_action_type_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.action_type_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 ame_aty_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 ame_aty_rki.after_insert
416 (p_effective_date
417 => p_effective_date
418 ,p_validation_start_date
419 => p_validation_start_date
420 ,p_validation_end_date
421 => p_validation_end_date
422 ,p_action_type_id
423 => p_rec.action_type_id
424 ,p_name
425 => p_rec.name
426 ,p_procedure_name
427 => p_rec.procedure_name
428 ,p_start_date
429 => p_rec.start_date
430 ,p_end_date
431 => p_rec.end_date
432 ,p_description
433 => p_rec.description
434 ,p_security_group_id
435 => p_rec.security_group_id
436 ,p_dynamic_description
437 => p_rec.dynamic_description
438 ,p_description_query
439 => p_rec.description_query
440 ,p_object_version_number
441 => p_rec.object_version_number
442 );
443 --
444 exception
445 --
446 when hr_api.cannot_find_prog_unit then
447 --
448 hr_api.cannot_find_prog_unit_error
449 (p_module_name => 'AME_ACTION_TYPES'
450 ,p_hook_type => 'AI');
451 --
452 end;
453 --
454 hr_utility.set_location(' Leaving:'||l_proc, 10);
455 End post_insert;
456 --
457 -- ----------------------------------------------------------------------------
458 -- |-------------------------------< ins_lck >--------------------------------|
459 -- ----------------------------------------------------------------------------
460 -- {Start Of Comments}
461 --
462 -- Description:
463 -- The ins_lck process has one main function to perform. When inserting
464 -- a datetracked row, we must validate the DT mode.
465 --
466 -- Prerequisites:
467 -- This procedure can only be called for the datetrack mode of INSERT.
468 --
469 -- In Parameters:
470 --
471 -- Post Success:
472 -- On successful completion of the ins_lck process the parental
473 -- datetracked rows will be locked providing the p_enforce_foreign_locking
474 -- argument value is TRUE.
475 -- If the p_enforce_foreign_locking argument value is FALSE then the
476 -- parential rows are not locked.
477 --
478 -- Post Failure:
479 -- The Lck process can fail for:
480 -- 1) When attempting to lock the row the row could already be locked by
481 -- another user. This will raise the HR_Api.Object_Locked exception.
482 -- 2) When attempting to the lock the parent which doesn't exist.
483 -- For the entity to be locked the parent must exist!
484 --
485 -- Developer Implementation Notes:
486 -- None.
487 --
488 -- Access Status:
489 -- Internal Row Handler Use Only.
490 --
491 -- {End Of Comments}
492 -- ----------------------------------------------------------------------------
493 Procedure ins_lck
494 (p_effective_date in date
495 ,p_datetrack_mode in varchar2
496 ,p_rec in ame_aty_shd.g_rec_type
497 ,p_validation_start_date out nocopy date
498 ,p_validation_end_date out nocopy date
499 ) is
500 --
501 l_proc varchar2(72) := g_package||'ins_lck';
502 l_validation_start_date date;
503 l_validation_end_date date;
504 --
505 Begin
506 hr_utility.set_location('Entering:'||l_proc, 5);
507 --
508 -- Validate the datetrack mode mode getting the validation start
509 -- and end dates for the specified datetrack operation.
510 --
511 --
512 -- Set the validation start and end date OUT arguments
513 --
514 -- p_validation_start_date := l_validation_start_date;
515 -- p_validation_end_date := l_validation_end_date;
516 -- MURTHY_CHANGES
517 p_validation_start_date := sysdate;
518 p_validation_end_date := ame_utility_pkg.endOfTime;
519 --
520 hr_utility.set_location(' Leaving:'||l_proc, 10);
521 --
522 End ins_lck;
523 --
524 -- ----------------------------------------------------------------------------
525 -- |---------------------------------< ins >----------------------------------|
526 -- ----------------------------------------------------------------------------
527 Procedure ins
528 (p_effective_date in date
529 ,p_rec in out nocopy ame_aty_shd.g_rec_type
530 ) is
531 --
532 l_proc varchar2(72) := g_package||'ins';
533 l_datetrack_mode varchar2(30) := hr_api.g_insert;
534 l_validation_start_date date;
535 l_validation_end_date date;
536 --
537 Begin
538 hr_utility.set_location('Entering:'||l_proc, 5);
539 --
540 -- Call the lock operation
541 --
542 ame_aty_ins.ins_lck
543 (p_effective_date => p_effective_date
544 ,p_datetrack_mode => l_datetrack_mode
545 ,p_rec => p_rec
546 ,p_validation_start_date => l_validation_start_date
547 ,p_validation_end_date => l_validation_end_date
548 );
549 --
550 -- Call the supporting insert validate operations
551 --
552 ame_aty_bus.insert_validate
553 (p_rec => p_rec
554 ,p_effective_date => p_effective_date
555 ,p_datetrack_mode => l_datetrack_mode
556 ,p_validation_start_date => l_validation_start_date
557 ,p_validation_end_date => l_validation_end_date
558 );
559 --
560 -- Call to raise any errors on multi-message list
561 hr_multi_message.end_validation_set;
562 --
563 -- Call the supporting pre-insert operation
564 --
565 ame_aty_ins.pre_insert
566 (p_rec => p_rec
567 ,p_effective_date => p_effective_date
568 ,p_datetrack_mode => l_datetrack_mode
569 ,p_validation_start_date => l_validation_start_date
570 ,p_validation_end_date => l_validation_end_date
571 );
572 --
573 -- Insert the row
574 --
575 ame_aty_ins.insert_dml
576 (p_rec => p_rec
577 ,p_effective_date => p_effective_date
578 ,p_datetrack_mode => l_datetrack_mode
579 ,p_validation_start_date => l_validation_start_date
580 ,p_validation_end_date => l_validation_end_date
581 );
582 --
583 -- Call the supporting post-insert operation
584 --
585 ame_aty_ins.post_insert
586 (p_rec => p_rec
587 ,p_effective_date => p_effective_date
588 ,p_datetrack_mode => l_datetrack_mode
589 ,p_validation_start_date => l_validation_start_date
590 ,p_validation_end_date => l_validation_end_date
591 );
592 --
593 -- Call to raise any errors on multi-message list
594 hr_multi_message.end_validation_set;
595 --
596 hr_utility.set_location('Leaving:'||l_proc,10);
597 end ins;
598 --
599 -- ----------------------------------------------------------------------------
600 -- |---------------------------------< ins >----------------------------------|
601 -- ----------------------------------------------------------------------------
602 Procedure ins
603 (p_effective_date in date
604 ,p_name in varchar2
605 ,p_procedure_name in varchar2
606 ,p_description in varchar2
607 ,p_dynamic_description in varchar2
608 ,p_security_group_id in number default null
609 ,p_description_query in varchar2 default null
610 ,p_action_type_id out nocopy number
611 ,p_object_version_number out nocopy number
612 ,p_start_date out nocopy date
613 ,p_end_date out nocopy date
614 ) is
615 --
616 l_rec ame_aty_shd.g_rec_type;
617 l_proc varchar2(72) := g_package||'ins';
618 --
619 Begin
620 hr_utility.set_location('Entering:'||l_proc, 5);
621 --
622 -- Call conversion function to turn arguments into the
623 -- p_rec structure.
624 --
625 l_rec :=
626 ame_aty_shd.convert_args
627 (null
628 ,p_name
629 ,p_procedure_name
630 ,null
631 ,null
632 ,p_description
633 ,p_security_group_id
634 ,p_dynamic_description
635 ,p_description_query
636 ,null
637 );
638 --
639 -- Having converted the arguments into the ame_aty_rec
640 -- plsql record structure we call the corresponding record
641 -- business process.
642 --
643 ame_aty_ins.ins
644 (p_effective_date
645 ,l_rec
646 );
647 --
648 -- Set the OUT arguments.
649 --
650 p_action_type_id := l_rec.action_type_id;
651 p_start_date := l_rec.start_date;
652 p_end_date := l_rec.end_date;
653 p_object_version_number := l_rec.object_version_number;
654 --
655 --
656 hr_utility.set_location(' Leaving:'||l_proc, 10);
657 End ins;
658 --
659 end ame_aty_ins;
[Home] [Help]