1 Package Body ff_fgl_ins as
2 /* $Header: fffglrhi.pkb 120.0.12000000.1 2007/03/20 11:52:47 ckesanap noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ff_fgl_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_global_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_global_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 ff_fgl_ins.g_global_id_i := p_global_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 ff_fgl_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.created_by,
90 t.creation_date
91 from ff_globals_f t
92 where t.global_id = p_rec.global_id
93 and t.effective_start_date =
94 ff_fgl_shd.g_old_rec.effective_start_date
95 and t.effective_end_date = (p_validation_start_date - 1);
96 --
97 l_proc varchar2(72) := g_package||'dt_insert_dml';
98 l_created_by ff_globals_f.created_by%TYPE;
99 l_creation_date ff_globals_f.creation_date%TYPE;
100 l_last_update_date ff_globals_f.last_update_date%TYPE;
101 l_last_updated_by ff_globals_f.last_updated_by%TYPE;
102 l_last_update_login ff_globals_f.last_update_login%TYPE;
103 --
104 Begin
105 hr_utility.set_location('Entering:'||l_proc, 5);
106 --
107 -- Set the effective start and end dates to the corresponding
108 -- validation start and end dates
109 --
110 p_rec.effective_start_date := p_validation_start_date;
111 p_rec.effective_end_date := p_validation_end_date;
112 --
113 -- If the datetrack_mode is not INSERT then we must populate the WHO
114 -- columns with the 'old' creation values and 'new' updated values.
115 --
116 If (p_datetrack_mode <> hr_api.g_insert) then
117 hr_utility.set_location(l_proc, 10);
118 --
119 -- Get the object version number for the insert
120 --
121 p_rec.object_version_number :=
122 dt_api.get_object_version_number
123 (p_base_table_name => 'ff_globals_f'
124 ,p_base_key_column => 'global_id'
125 ,p_base_key_value => p_rec.global_id
126 );
127 --
128 -- Select the 'old' created values
129 --
130 Open C_Sel1;
131 Fetch C_Sel1 Into l_created_by, l_creation_date;
132 If C_Sel1%notfound Then
133 --
134 -- The previous 'old' created row has not been found. We need
135 -- to error as an internal datetrack problem exists.
136 --
137 Close C_Sel1;
138 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
139 fnd_message.set_token('PROCEDURE', l_proc);
140 fnd_message.set_token('STEP','10');
141 fnd_message.raise_error;
142 End If;
143 Close C_Sel1;
144 --
145 -- Set the AOL updated WHO values
146 --
147 l_last_update_date := sysdate;
148 l_last_updated_by := fnd_global.user_id;
149 l_last_update_login := fnd_global.login_id;
150 Else
151 p_rec.object_version_number := 1; -- Initialise the object version
152 End If;
153 --
154 ff_fgl_shd.g_api_dml := true; -- Set the api dml status
155 --
156 -- Insert the row into: ff_globals_f
157 --
158 insert into ff_globals_f
159 (global_id
160 ,effective_start_date
161 ,effective_end_date
162 ,business_group_id
163 ,legislation_code
164 ,data_type
165 ,global_name
166 ,global_description
167 ,global_value
168 ,object_version_number
169 ,created_by
170 ,creation_date
171 ,last_update_date
172 ,last_updated_by
173 ,last_update_login
174 )
175 Values
176 (p_rec.global_id
177 ,p_rec.effective_start_date
178 ,p_rec.effective_end_date
179 ,p_rec.business_group_id
180 ,p_rec.legislation_code
181 ,p_rec.data_type
182 ,p_rec.global_name
183 ,p_rec.global_description
184 ,p_rec.global_value
185 ,p_rec.object_version_number
186 ,l_created_by
187 ,l_creation_date
188 ,l_last_update_date
189 ,l_last_updated_by
190 ,l_last_update_login
191 );
192 --
193 ff_fgl_shd.g_api_dml := false; -- Unset the api dml status
194 hr_utility.set_location(' Leaving:'||l_proc, 15);
195 --
196 Exception
197 When hr_api.check_integrity_violated Then
198 -- A check constraint has been violated
199 ff_fgl_shd.g_api_dml := false; -- Unset the api dml status
200 ff_fgl_shd.constraint_error
201 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
202 When hr_api.unique_integrity_violated Then
203 -- Unique integrity has been violated
204 ff_fgl_shd.g_api_dml := false; -- Unset the api dml status
205 ff_fgl_shd.constraint_error
206 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
207 When Others Then
208 ff_fgl_shd.g_api_dml := false; -- Unset the api dml status
209 Raise;
210 End dt_insert_dml;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |-----------------------< create_app_ownerships >--------------------------|
214 -- ----------------------------------------------------------------------------
215 --
216 -- Description:
217 -- This procedure inserts a row into the HR_APPLICATION_OWNERSHIPS table
218 -- when the row handler is called in the appropriate mode.
219 --
220 -- ----------------------------------------------------------------------------
221 PROCEDURE create_app_ownerships(p_pk_column IN varchar2
222 ,p_pk_value IN varchar2) IS
223 --
224 CURSOR csr_definition IS
225 SELECT product_short_name
226 FROM hr_owner_definitions
227 WHERE session_id = hr_startup_data_api_support.g_session_id;
228 --
229 BEGIN
230 --
231 IF (hr_startup_data_api_support.return_startup_mode IN
232 ('STARTUP','GENERIC')) THEN
233 --
234 FOR c1 IN csr_definition LOOP
235 --
236 INSERT INTO hr_application_ownerships
237 (key_name
238 ,key_value
239 ,product_name
240 )
241 VALUES
242 (p_pk_column
243 ,fnd_number.number_to_canonical(p_pk_value)
244 ,c1.product_short_name
245 );
246 END LOOP;
247 END IF;
248 END create_app_ownerships;
249 --
250 -- ----------------------------------------------------------------------------
251 -- |-----------------------< create_app_ownerships >--------------------------|
252 -- ----------------------------------------------------------------------------
253 PROCEDURE create_app_ownerships(p_pk_column IN varchar2
254 ,p_pk_value IN number) IS
255 --
256 BEGIN
257 create_app_ownerships(p_pk_column, to_char(p_pk_value));
258 END create_app_ownerships;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |------------------------------< insert_dml >------------------------------|
262 -- ----------------------------------------------------------------------------
263 Procedure insert_dml
264 (p_rec in out nocopy ff_fgl_shd.g_rec_type
265 ,p_effective_date in date
266 ,p_datetrack_mode in varchar2
267 ,p_validation_start_date in date
268 ,p_validation_end_date in date
269 ) is
270 --
271 l_proc varchar2(72) := g_package||'insert_dml';
272 --
273 Begin
274 hr_utility.set_location('Entering:'||l_proc, 5);
275 --
276 ff_fgl_ins.dt_insert_dml
277 (p_rec => p_rec
278 ,p_effective_date => p_effective_date
279 ,p_datetrack_mode => p_datetrack_mode
280 ,p_validation_start_date => p_validation_start_date
281 ,p_validation_end_date => p_validation_end_date
282 );
283 --
284 hr_utility.set_location(' Leaving:'||l_proc, 10);
285 End insert_dml;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |------------------------------< pre_insert >------------------------------|
289 -- ----------------------------------------------------------------------------
290 -- {Start Of Comments}
291 --
292 -- Description:
293 -- This private procedure contains any processing which is required before
294 -- the insert dml. Presently, if the entity has a corresponding primary
295 -- key which is maintained by an associating sequence, the primary key for
296 -- the entity will be populated with the next sequence value in
297 -- preparation for the insert dml.
298 -- Also, if comments are defined for this entity, the comments insert
299 -- logic will also be called, generating a comment_id if required.
300 --
301 -- Prerequisites:
302 -- This is an internal procedure which is called from the ins procedure.
303 --
304 -- In Parameters:
305 -- A Pl/Sql record structure.
306 --
307 -- Post Success:
308 -- Processing continues.
309 --
310 -- Post Failure:
311 -- If an error has occurred, an error message and exception will be raised
312 -- but not handled.
313 --
314 -- Developer Implementation Notes:
315 -- Any pre-processing required before the insert dml is issued should be
316 -- coded within this procedure. As stated above, a good example is the
317 -- generation of a primary key number via a corresponding sequence.
318 -- It is important to note that any 3rd party maintenance should be reviewed
319 -- before placing in this procedure.
320 --
321 -- Access Status:
322 -- Internal Row Handler Use Only.
323 --
324 -- {End Of Comments}
325 -- ----------------------------------------------------------------------------
326 Procedure pre_insert
327 (p_rec in out nocopy ff_fgl_shd.g_rec_type
328 ,p_effective_date in date
329 ,p_datetrack_mode in varchar2
330 ,p_validation_start_date in date
331 ,p_validation_end_date in date
332 ) is
333 --
334 Cursor C_Sel1 is select ff_globals_s.nextval from sys.dual;
335 --
336 Cursor C_Sel2 is
337 Select null
338 from ff_globals_f
339 where global_id =
340 ff_fgl_ins.g_global_id_i;
341 --
342 l_proc varchar2(72) := g_package||'pre_insert';
343 l_exists varchar2(1);
344 --
345 Begin
346 hr_utility.set_location('Entering:'||l_proc, 5);
347 --
348 If (ff_fgl_ins.g_global_id_i is not null) Then
349 --
350 -- Verify registered primary key values not already in use
351 --
352 Open C_Sel2;
353 Fetch C_Sel2 into l_exists;
354 If C_Sel2%found Then
355 Close C_Sel2;
356 --
357 -- The primary key values are already in use.
358 --
359 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
360 fnd_message.set_token('TABLE_NAME','ff_globals_f');
361 fnd_message.raise_error;
362 End If;
363 Close C_Sel2;
364 --
365 -- Use registered key values and clear globals
366 --
367 p_rec.global_id :=
368 ff_fgl_ins.g_global_id_i;
369 ff_fgl_ins.g_global_id_i := null;
370 Else
371 --
372 -- No registerd key values, so select the next sequence number
373 --
374 --
375 -- Select the next sequence number
376 --
377 Open C_Sel1;
378 Fetch C_Sel1 Into p_rec.global_id;
379 Close C_Sel1;
380 End If;
381 --
382 --
383 hr_utility.set_location(' Leaving:'||l_proc, 10);
384 End pre_insert;
385 --
386 -- ----------------------------------------------------------------------------
387 -- |----------------------------< post_insert >-------------------------------|
388 -- ----------------------------------------------------------------------------
389 -- {Start Of Comments}
390 --
391 -- Description:
392 -- This private procedure contains any processing which is required after
393 -- the insert dml.
394 --
395 -- Prerequisites:
396 -- This is an internal procedure which is called from the ins procedure.
397 --
398 -- In Parameters:
399 -- A Pl/Sql record structure.
400 --
401 -- Post Success:
402 -- Processing continues.
403 --
404 -- Post Failure:
405 -- If an error has occurred, an error message and exception will be raised
406 -- but not handled.
407 --
408 -- Developer Implementation Notes:
409 -- Any post-processing required after the insert dml is issued should be
410 -- coded within this procedure. It is important to note that any 3rd party
411 -- maintenance should be reviewed before placing in this procedure.
412 --
413 -- Access Status:
414 -- Internal Row Handler Use Only.
415 --
416 -- {End Of Comments}
417 -- ----------------------------------------------------------------------------
418 Procedure post_insert
419 (p_rec in ff_fgl_shd.g_rec_type
420 ,p_effective_date in date
421 ,p_datetrack_mode in varchar2
422 ,p_validation_start_date in date
423 ,p_validation_end_date in date
424 ) is
425 --
426 l_proc varchar2(72) := g_package||'post_insert';
427 --
428 Begin
429 hr_utility.set_location('Entering:'||l_proc, 5);
430 begin
431 --
432 -- insert ownerships if applicable
433 create_app_ownerships
434 ('GLOBAL_ID', p_rec.global_id
435 );
436 --
437 --
438 ff_fgl_rki.after_insert
439 (p_effective_date
440 => p_effective_date
441 ,p_validation_start_date
442 => p_validation_start_date
443 ,p_validation_end_date
444 => p_validation_end_date
445 ,p_global_id
446 => p_rec.global_id
447 ,p_effective_start_date
448 => p_rec.effective_start_date
449 ,p_effective_end_date
450 => p_rec.effective_end_date
451 ,p_business_group_id
452 => p_rec.business_group_id
453 ,p_legislation_code
454 => p_rec.legislation_code
455 ,p_data_type
456 => p_rec.data_type
457 ,p_global_name
458 => p_rec.global_name
459 ,p_global_description
460 => p_rec.global_description
461 ,p_global_value
462 => p_rec.global_value
463 ,p_object_version_number
464 => p_rec.object_version_number
465 );
466 --
467 exception
468 --
469 when hr_api.cannot_find_prog_unit then
470 --
471 hr_api.cannot_find_prog_unit_error
472 (p_module_name => 'FF_GLOBALS_F'
473 ,p_hook_type => 'AI');
474 --
475 end;
476 --
477 hr_utility.set_location(' Leaving:'||l_proc, 10);
478 End post_insert;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |-------------------------------< ins_lck >--------------------------------|
482 -- ----------------------------------------------------------------------------
483 -- {Start Of Comments}
484 --
485 -- Description:
486 -- The ins_lck process has one main function to perform. When inserting
487 -- a datetracked row, we must validate the DT mode.
488 --
489 -- Prerequisites:
490 -- This procedure can only be called for the datetrack mode of INSERT.
491 --
492 -- In Parameters:
493 --
494 -- Post Success:
495 -- On successful completion of the ins_lck process the parental
496 -- datetracked rows will be locked providing the p_enforce_foreign_locking
497 -- argument value is TRUE.
498 -- If the p_enforce_foreign_locking argument value is FALSE then the
499 -- parential rows are not locked.
500 --
501 -- Post Failure:
502 -- The Lck process can fail for:
503 -- 1) When attempting to lock the row the row could already be locked by
504 -- another user. This will raise the HR_Api.Object_Locked exception.
505 -- 2) When attempting to the lock the parent which doesn't exist.
506 -- For the entity to be locked the parent must exist!
507 --
508 -- Developer Implementation Notes:
509 -- None.
510 --
511 -- Access Status:
512 -- Internal Row Handler Use Only.
513 --
514 -- {End Of Comments}
515 -- ----------------------------------------------------------------------------
516 Procedure ins_lck
517 (p_effective_date in date
518 ,p_datetrack_mode in varchar2
519 ,p_rec in ff_fgl_shd.g_rec_type
520 ,p_validation_start_date out nocopy date
521 ,p_validation_end_date out nocopy date
522 ) is
523 --
524 l_proc varchar2(72) := g_package||'ins_lck';
525 l_validation_start_date date;
526 l_validation_end_date date;
527 --
528 Begin
529 hr_utility.set_location('Entering:'||l_proc, 5);
530 --
531 -- Validate the datetrack mode mode getting the validation start
532 -- and end dates for the specified datetrack operation.
533 --
534 dt_api.validate_dt_mode
535 (p_effective_date => p_effective_date
536 ,p_datetrack_mode => p_datetrack_mode
537 ,p_base_table_name => 'ff_globals_f'
538 ,p_base_key_column => 'global_id'
539 ,p_base_key_value => p_rec.global_id
540 ,p_enforce_foreign_locking => true
541 ,p_validation_start_date => l_validation_start_date
542 ,p_validation_end_date => l_validation_end_date
543 );
544 --
545 -- Set the validation start and end date OUT arguments
546 --
547 p_validation_start_date := l_validation_start_date;
548 p_validation_end_date := l_validation_end_date;
549 --
550 hr_utility.set_location(' Leaving:'||l_proc, 10);
551 --
552 End ins_lck;
553 --
554 -- ----------------------------------------------------------------------------
555 -- |---------------------------------< ins >----------------------------------|
556 -- ----------------------------------------------------------------------------
557 Procedure ins
558 (p_effective_date in date
559 ,p_rec in out nocopy ff_fgl_shd.g_rec_type
560 ) is
561 --
562 l_proc varchar2(72) := g_package||'ins';
563 l_datetrack_mode varchar2(30) := hr_api.g_insert;
564 l_validation_start_date date;
565 l_validation_end_date date;
566 --
567 Begin
568 hr_utility.set_location('Entering:'||l_proc, 5);
569 --
570 -- Call the lock operation
571 --
572 ff_fgl_ins.ins_lck
573 (p_effective_date => p_effective_date
574 ,p_datetrack_mode => l_datetrack_mode
575 ,p_rec => p_rec
576 ,p_validation_start_date => l_validation_start_date
577 ,p_validation_end_date => l_validation_end_date
578 );
579 --
580 -- Call the supporting insert validate operations
581 --
582 ff_fgl_bus.insert_validate
583 (p_rec => p_rec
584 ,p_effective_date => p_effective_date
585 ,p_datetrack_mode => l_datetrack_mode
586 ,p_validation_start_date => l_validation_start_date
587 ,p_validation_end_date => l_validation_end_date
588 );
589 --
590 -- Call to raise any errors on multi-message list
591 hr_multi_message.end_validation_set;
592 --
593 -- Call the supporting pre-insert operation
594 --
595 ff_fgl_ins.pre_insert
596 (p_rec => p_rec
597 ,p_effective_date => p_effective_date
598 ,p_datetrack_mode => l_datetrack_mode
599 ,p_validation_start_date => l_validation_start_date
600 ,p_validation_end_date => l_validation_end_date
601 );
602 --
603 -- Insert the row
604 --
605 ff_fgl_ins.insert_dml
606 (p_rec => p_rec
607 ,p_effective_date => p_effective_date
608 ,p_datetrack_mode => l_datetrack_mode
609 ,p_validation_start_date => l_validation_start_date
610 ,p_validation_end_date => l_validation_end_date
611 );
612 --
613 -- Call the supporting post-insert operation
614 --
615 ff_fgl_ins.post_insert
616 (p_rec => p_rec
617 ,p_effective_date => p_effective_date
618 ,p_datetrack_mode => l_datetrack_mode
619 ,p_validation_start_date => l_validation_start_date
620 ,p_validation_end_date => l_validation_end_date
621 );
622 --
623 -- Call to raise any errors on multi-message list
624 hr_multi_message.end_validation_set;
625 --
626 hr_utility.set_location('Leaving:'||l_proc,10);
627 end ins;
628 --
629 -- ----------------------------------------------------------------------------
630 -- |---------------------------------< ins >----------------------------------|
631 -- ----------------------------------------------------------------------------
632 Procedure ins
633 (p_effective_date in date
634 ,p_data_type in varchar2
635 ,p_global_name in varchar2
636 ,p_business_group_id in number default null
637 ,p_legislation_code in varchar2 default null
638 ,p_global_description in varchar2 default null
639 ,p_global_value in varchar2 default null
640 ,p_global_id out nocopy number
641 ,p_object_version_number out nocopy number
642 ,p_effective_start_date out nocopy date
643 ,p_effective_end_date out nocopy date
644 ) is
645 --
646 l_rec ff_fgl_shd.g_rec_type;
647 l_proc varchar2(72) := g_package||'ins';
648 --
649 Begin
650 hr_utility.set_location('Entering:'||l_proc, 5);
651 --
652 -- Call conversion function to turn arguments into the
653 -- p_rec structure.
654 --
655 l_rec :=
656 ff_fgl_shd.convert_args
657 (null
658 ,null
659 ,null
660 ,p_business_group_id
661 ,p_legislation_code
662 ,p_data_type
663 ,p_global_name
664 ,p_global_description
665 ,p_global_value
666 ,null
667 );
668 --
669 -- Having converted the arguments into the ff_pay_rec
670 -- plsql record structure we call the corresponding record
671 -- business process.
672 --
673 ff_fgl_ins.ins
674 (p_effective_date
675 ,l_rec
676 );
677 --
678 -- Set the OUT arguments.
679 --
680 p_global_id := l_rec.global_id;
681 p_effective_start_date := l_rec.effective_start_date;
682 p_effective_end_date := l_rec.effective_end_date;
683 p_object_version_number := l_rec.object_version_number;
684 --
685 --
686 hr_utility.set_location(' Leaving:'||l_proc, 10);
687 End ins;
688 --
689 end ff_fgl_ins;
[Home] [Help]