1 Package Body ben_cty_upd as
2 /* $Header: bectyrhi.pkb 120.2 2006/03/30 23:42:52 gsehgal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cty_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------------< dt_update_dml >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the execution of dml from the datetrack mode
17 -- of CORRECTION only. It is important to note that the object version
18 -- number is only increment by 1 because the datetrack correction is
19 -- soley for one datetracked row.
20 -- This procedure controls the actual dml update logic. The functions of
21 -- this procedure are as follows:
22 -- 1) Get the next object_version_number.
23 -- 2) To set and unset the g_api_dml status as required (as we are about to
24 -- perform dml).
25 -- 3) To update the specified row in the schema using the primary key in
26 -- the predicates.
27 -- 4) To trap any constraint violations that may have occurred.
28 -- 5) To raise any other errors.
29 --
30 -- Prerequisites:
31 -- This is an internal private procedure which must be called from the
32 -- update_dml procedure.
33 --
34 -- In Parameters:
35 -- A Pl/Sql record structure.
36 --
37 -- Post Success:
38 -- The specified row will be updated in the schema.
39 --
40 -- Post Failure:
41 -- On the update dml failure it is important to note that we always reset the
42 -- g_api_dml status to false.
43 -- If a check or unique integrity constraint violation is raised the
44 -- constraint_error procedure will be called.
45 -- If any other error is reported, the error will be raised after the
46 -- g_api_dml status is reset.
47 --
48 -- Developer Implementation Notes:
49 -- The update 'set' arguments list should be modified if any of your
50 -- attributes are not updateable.
51 --
52 -- Access Status:
53 -- Internal Row Handler Use Only.
54 --
55 -- {End Of Comments}
56 -- ----------------------------------------------------------------------------
57 Procedure dt_update_dml
58 (p_rec in out nocopy ben_cty_shd.g_rec_type
59 ,p_effective_date in date
60 ,p_datetrack_mode in varchar2
61 ,p_validation_start_date in date
62 ,p_validation_end_date in date
63 ) is
64 --
65 l_proc varchar2(72) := g_package||'dt_update_dml';
66 --
67 Begin
68 hr_utility.set_location('Entering:'||l_proc, 5);
69 --
70 If (p_datetrack_mode = hr_api.g_correction) then
71 hr_utility.set_location(l_proc, 10);
72 --
73 -- Because we are updating a row we must get the next object
74 -- version number.
75 --
76 p_rec.object_version_number :=
77 dt_api.get_object_version_number
78 (p_base_table_name => 'ben_comptncy_rt_f'
79 ,p_base_key_column => 'comptncy_rt_id'
80 ,p_base_key_value => p_rec.comptncy_rt_id
81 );
82 --
83 --
84 --
85 -- Update the ben_comptncy_rt_f Row
86 --
87 update ben_comptncy_rt_f
88 set
89 comptncy_rt_id = p_rec.comptncy_rt_id
90 ,competence_id = p_rec.competence_id
91 ,rating_level_id = p_rec.rating_level_id
92 ,excld_flag = p_rec.excld_flag
93 ,business_group_id = p_rec.business_group_id
94 ,vrbl_rt_prfl_id = p_rec.vrbl_rt_prfl_id
95 ,object_version_number = p_rec.object_version_number
96 ,ordr_num = p_rec.ordr_num
97 ,cty_attribute_category = p_rec.cty_attribute_category
98 ,cty_attribute1 = p_rec.cty_attribute1
99 ,cty_attribute2 = p_rec.cty_attribute2
100 ,cty_attribute3 = p_rec.cty_attribute3
101 ,cty_attribute4 = p_rec.cty_attribute4
102 ,cty_attribute5 = p_rec.cty_attribute5
103 ,cty_attribute6 = p_rec.cty_attribute6
104 ,cty_attribute7 = p_rec.cty_attribute7
105 ,cty_attribute8 = p_rec.cty_attribute8
106 ,cty_attribute9 = p_rec.cty_attribute9
107 ,cty_attribute10 = p_rec.cty_attribute10
108 ,cty_attribute11 = p_rec.cty_attribute11
109 ,cty_attribute12 = p_rec.cty_attribute12
110 ,cty_attribute13 = p_rec.cty_attribute13
111 ,cty_attribute14 = p_rec.cty_attribute14
112 ,cty_attribute15 = p_rec.cty_attribute15
113 ,cty_attribute16 = p_rec.cty_attribute16
114 ,cty_attribute17 = p_rec.cty_attribute17
115 ,cty_attribute18 = p_rec.cty_attribute18
116 ,cty_attribute19 = p_rec.cty_attribute19
117 ,cty_attribute20 = p_rec.cty_attribute20
118 ,cty_attribute21 = p_rec.cty_attribute21
119 ,cty_attribute22 = p_rec.cty_attribute22
120 ,cty_attribute23 = p_rec.cty_attribute23
121 ,cty_attribute24 = p_rec.cty_attribute24
122 ,cty_attribute25 = p_rec.cty_attribute25
123 ,cty_attribute26 = p_rec.cty_attribute26
124 ,cty_attribute27 = p_rec.cty_attribute27
125 ,cty_attribute28 = p_rec.cty_attribute28
126 ,cty_attribute29 = p_rec.cty_attribute29
127 ,cty_attribute30 = p_rec.cty_attribute30
128 where comptncy_rt_id = p_rec.comptncy_rt_id
129 and effective_start_date = p_validation_start_date
130 and effective_end_date = p_validation_end_date;
131 --
132 --
133 --
134 -- Set the effective start and end dates
135 --
136 p_rec.effective_start_date := p_validation_start_date;
137 p_rec.effective_end_date := p_validation_end_date;
138 End If;
139 --
140 hr_utility.set_location(' Leaving:'||l_proc, 15);
141 Exception
142 When hr_api.check_integrity_violated Then
143 -- A check constraint has been violated
144 --
145 ben_cty_shd.constraint_error
146 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
147 When hr_api.unique_integrity_violated Then
148 -- Unique integrity has been violated
149 --
150 ben_cty_shd.constraint_error
151 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
152 When Others Then
153 --
154 Raise;
155 End dt_update_dml;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |------------------------------< update_dml >------------------------------|
159 -- ----------------------------------------------------------------------------
160 -- {Start Of Comments}
161 --
162 -- Description:
163 -- This procedure calls the dt_update_dml control logic which handles
164 -- the actual datetrack dml.
165 --
166 -- Prerequisites:
167 -- This is an internal private procedure which must be called from the upd
168 -- procedure.
169 --
170 -- In Parameters:
171 -- A Pl/Sql record structre.
172 --
173 -- Post Success:
174 -- Processing contines.
175 --
176 -- Post Failure:
177 -- No specific error handling is required within this procedure.
178 --
179 -- Developer Implementation Notes:
180 -- The update 'set' arguments list should be modified if any of your
181 -- attributes are not updateable.
182 --
183 -- Access Status:
184 -- Internal Row Handler Use Only.
185 --
186 -- {End Of Comments}
187 -- ----------------------------------------------------------------------------
188 Procedure update_dml
189 (p_rec in out nocopy ben_cty_shd.g_rec_type
190 ,p_effective_date in date
191 ,p_datetrack_mode in varchar2
192 ,p_validation_start_date in date
193 ,p_validation_end_date in date
194 ) is
195 --
196 l_proc varchar2(72) := g_package||'update_dml';
197 --
198 Begin
199 hr_utility.set_location('Entering:'||l_proc, 5);
200 --
201 ben_cty_upd.dt_update_dml
202 (p_rec => p_rec
203 ,p_effective_date => p_effective_date
204 ,p_datetrack_mode => p_datetrack_mode
205 ,p_validation_start_date => p_validation_start_date
206 ,p_validation_end_date => p_validation_end_date
207 );
208 --
209 hr_utility.set_location(' Leaving:'||l_proc, 10);
210 End update_dml;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |----------------------------< dt_pre_update >-----------------------------|
214 -- ----------------------------------------------------------------------------
215 -- {Start Of Comments}
216 --
217 -- Description:
218 -- The dt_pre_update procedure controls the execution
219 -- of dml for the datetrack modes of: UPDATE, UPDATE_OVERRIDE
220 -- and UPDATE_CHANGE_INSERT only. The execution required is as
221 -- follows:
222 --
223 -- 1) Providing the datetrack update mode is not 'CORRECTION'
224 -- then set the effective end date of the current row (this
225 -- will be the validation_start_date - 1).
226 -- 2) If the datetrack mode is 'UPDATE_OVERRIDE' then call the
227 -- corresponding delete_dml process to delete any future rows
228 -- where the effective_start_date is greater than or equal to
229 -- the validation_start_date.
230 -- 3) Call the insert_dml process to insert the new updated row
231 -- details.
232 --
233 -- Prerequisites:
234 -- This is an internal procedure which is called from the
235 -- pre_update procedure.
236 --
237 -- In Parameters:
238 --
239 -- Post Success:
240 -- Processing continues.
241 --
242 -- Post Failure:
243 -- If an error has occurred, an error message and exception will be raised
244 -- but not handled.
245 --
246 -- Developer Implementation Notes:
247 -- This is an internal procedure which is required by Datetrack. Don't
248 -- remove or modify.
249 --
250 -- Access Status:
251 -- Internal Row Handler Use Only.
252 --
253 -- {End Of Comments}
254 -- ----------------------------------------------------------------------------
255 Procedure dt_pre_update
256 (p_rec in out nocopy ben_cty_shd.g_rec_type
257 ,p_effective_date in date
258 ,p_datetrack_mode in varchar2
259 ,p_validation_start_date in date
260 ,p_validation_end_date in date
261 ) is
262 --
263 l_proc varchar2(72) := g_package||'dt_pre_update';
264 l_dummy_version_number number;
265 --
266 Begin
267 hr_utility.set_location('Entering:'||l_proc, 5);
268 If (p_datetrack_mode <> hr_api.g_correction) then
269 --
270 -- Update the current effective end date
271 --
272 ben_cty_shd.upd_effective_end_date
273 (p_effective_date => p_effective_date
274 ,p_base_key_value => p_rec.comptncy_rt_id
275 ,p_new_effective_end_date => (p_validation_start_date - 1)
276 ,p_validation_start_date => p_validation_start_date
277 ,p_validation_end_date => p_validation_end_date
278 ,p_object_version_number => l_dummy_version_number
279 );
280 --
281 If (p_datetrack_mode = hr_api.g_update_override) then
282 --
283 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
284 -- delete any future rows
285 --
286 ben_cty_del.delete_dml
287 (p_rec => p_rec
288 ,p_effective_date => p_effective_date
289 ,p_datetrack_mode => p_datetrack_mode
290 ,p_validation_start_date => p_validation_start_date
291 ,p_validation_end_date => p_validation_end_date
292 );
293 End If;
294 --
295 -- We must now insert the updated row
296 --
297 ben_cty_ins.insert_dml
298 (p_rec => p_rec
299 ,p_effective_date => p_effective_date
300 ,p_datetrack_mode => p_datetrack_mode
301 ,p_validation_start_date => p_validation_start_date
302 ,p_validation_end_date => p_validation_end_date
303 );
304 End If;
305 hr_utility.set_location(' Leaving:'||l_proc, 20);
306 End dt_pre_update;
307 --
308 -- ----------------------------------------------------------------------------
309 -- |------------------------------< pre_update >------------------------------|
310 -- ----------------------------------------------------------------------------
311 -- {Start Of Comments}
312 --
313 -- Description:
314 -- This private procedure contains any processing which is required before
315 -- the update dml.
316 --
317 -- Prerequisites:
318 -- This is an internal procedure which is called from the upd procedure.
319 --
320 -- In Parameters:
321 -- A Pl/Sql record structure.
322 --
323 -- Post Success:
324 -- Processing continues.
325 --
326 -- Post Failure:
327 -- If an error has occurred, an error message and exception will be raised
328 -- but not handled.
329 -- Developer Implementation Notes:
330 -- Any pre-processing required before the update dml is issued should be
331 -- coded within this procedure. It is important to note that any 3rd party
332 -- maintenance should be reviewed before placing in this procedure. The call
333 -- to the dt_update_dml procedure should NOT be removed.
334 --
335 -- Access Status:
336 -- Internal Row Handler Use Only.
337 --
338 -- {End Of Comments}
339 -- ----------------------------------------------------------------------------
340 Procedure pre_update
341 (p_rec in out nocopy ben_cty_shd.g_rec_type
342 ,p_effective_date in date
343 ,p_datetrack_mode in varchar2
344 ,p_validation_start_date in date
345 ,p_validation_end_date in date
346 ) is
347 --
348 l_proc varchar2(72) := g_package||'pre_update';
349 --
350 Begin
351 hr_utility.set_location('Entering:'||l_proc, 5);
352 --
353 --
354 --
355 dt_pre_update
356 (p_rec => p_rec
357 ,p_effective_date => p_effective_date
358 ,p_datetrack_mode => p_datetrack_mode
359 ,p_validation_start_date => p_validation_start_date
360 ,p_validation_end_date => p_validation_end_date
361 );
362 --
363 hr_utility.set_location(' Leaving:'||l_proc, 10);
364 End pre_update;
365 --
366 -- ----------------------------------------------------------------------------
367 -- |----------------------------< post_update >-------------------------------|
368 -- ----------------------------------------------------------------------------
369 -- {Start Of Comments}
370 --
371 -- Description:
372 -- This private procedure contains any processing which is required after
373 -- the update dml.
374 --
375 -- Prerequisites:
376 -- This is an internal procedure which is called from the upd procedure.
377 --
378 -- In Parameters:
379 -- A Pl/Sql record structure.
380 --
381 -- Post Success:
382 -- Processing continues.
383 --
384 -- Post Failure:
385 -- If an error has occurred, an error message and exception will be raised
386 -- but not handled.
387 --
388 -- Developer Implementation Notes:
389 -- Any post-processing required after the update dml is issued should be
390 -- coded within this procedure. It is important to note that any 3rd party
391 -- maintenance should be reviewed before placing in this procedure.
392 --
393 -- Access Status:
394 -- Internal Row Handler Use Only.
395 --
396 -- {End Of Comments}
397 -- ----------------------------------------------------------------------------
398 Procedure post_update
399 (p_rec in ben_cty_shd.g_rec_type
400 ,p_effective_date in date
401 ,p_datetrack_mode in varchar2
402 ,p_validation_start_date in date
403 ,p_validation_end_date in date
404 ) is
405 --
406 l_proc varchar2(72) := g_package||'post_update';
407 --
408 Begin
409 hr_utility.set_location('Entering:'||l_proc, 5);
410 begin
411 --
412 ben_cty_rku.after_update
413 (p_effective_date
414 => p_effective_date
415 ,p_datetrack_mode
416 => p_datetrack_mode
417 ,p_validation_start_date
418 => p_validation_start_date
419 ,p_validation_end_date
420 => p_validation_end_date
421 ,p_comptncy_rt_id
422 => p_rec.comptncy_rt_id
423 ,p_effective_start_date
424 => p_rec.effective_start_date
425 ,p_effective_end_date
426 => p_rec.effective_end_date
427 ,p_competence_id
428 => p_rec.competence_id
429 ,p_rating_level_id
430 => p_rec.rating_level_id
431 ,p_excld_flag
432 => p_rec.excld_flag
433 ,p_business_group_id
434 => p_rec.business_group_id
435 ,p_vrbl_rt_prfl_id
436 => p_rec.vrbl_rt_prfl_id
437 ,p_object_version_number
438 => p_rec.object_version_number
439 ,p_ordr_num
440 => p_rec.ordr_num
441 ,p_cty_attribute_category
442 => p_rec.cty_attribute_category
443 ,p_cty_attribute1
444 => p_rec.cty_attribute1
445 ,p_cty_attribute2
446 => p_rec.cty_attribute2
447 ,p_cty_attribute3
448 => p_rec.cty_attribute3
449 ,p_cty_attribute4
450 => p_rec.cty_attribute4
451 ,p_cty_attribute5
452 => p_rec.cty_attribute5
453 ,p_cty_attribute6
454 => p_rec.cty_attribute6
455 ,p_cty_attribute7
456 => p_rec.cty_attribute7
457 ,p_cty_attribute8
458 => p_rec.cty_attribute8
459 ,p_cty_attribute9
460 => p_rec.cty_attribute9
461 ,p_cty_attribute10
462 => p_rec.cty_attribute10
463 ,p_cty_attribute11
464 => p_rec.cty_attribute11
465 ,p_cty_attribute12
466 => p_rec.cty_attribute12
467 ,p_cty_attribute13
468 => p_rec.cty_attribute13
469 ,p_cty_attribute14
470 => p_rec.cty_attribute14
471 ,p_cty_attribute15
472 => p_rec.cty_attribute15
473 ,p_cty_attribute16
474 => p_rec.cty_attribute16
475 ,p_cty_attribute17
476 => p_rec.cty_attribute17
477 ,p_cty_attribute18
478 => p_rec.cty_attribute18
479 ,p_cty_attribute19
480 => p_rec.cty_attribute19
481 ,p_cty_attribute20
482 => p_rec.cty_attribute20
483 ,p_cty_attribute21
484 => p_rec.cty_attribute21
485 ,p_cty_attribute22
486 => p_rec.cty_attribute22
487 ,p_cty_attribute23
488 => p_rec.cty_attribute23
489 ,p_cty_attribute24
490 => p_rec.cty_attribute24
491 ,p_cty_attribute25
492 => p_rec.cty_attribute25
493 ,p_cty_attribute26
494 => p_rec.cty_attribute26
495 ,p_cty_attribute27
496 => p_rec.cty_attribute27
497 ,p_cty_attribute28
498 => p_rec.cty_attribute28
499 ,p_cty_attribute29
500 => p_rec.cty_attribute29
501 ,p_cty_attribute30
502 => p_rec.cty_attribute30
503 ,p_effective_start_date_o
504 => ben_cty_shd.g_old_rec.effective_start_date
505 ,p_effective_end_date_o
506 => ben_cty_shd.g_old_rec.effective_end_date
507 ,p_competence_id_o
508 => ben_cty_shd.g_old_rec.competence_id
509 ,p_rating_level_id_o
510 => ben_cty_shd.g_old_rec.rating_level_id
511 ,p_excld_flag_o
512 => ben_cty_shd.g_old_rec.excld_flag
513 ,p_business_group_id_o
514 => ben_cty_shd.g_old_rec.business_group_id
515 ,p_vrbl_rt_prfl_id_o
516 => ben_cty_shd.g_old_rec.vrbl_rt_prfl_id
517 ,p_object_version_number_o
518 => ben_cty_shd.g_old_rec.object_version_number
519 ,p_ordr_num_o
520 => ben_cty_shd.g_old_rec.ordr_num
521 ,p_cty_attribute_category_o
522 => ben_cty_shd.g_old_rec.cty_attribute_category
523 ,p_cty_attribute1_o
524 => ben_cty_shd.g_old_rec.cty_attribute1
525 ,p_cty_attribute2_o
526 => ben_cty_shd.g_old_rec.cty_attribute2
527 ,p_cty_attribute3_o
528 => ben_cty_shd.g_old_rec.cty_attribute3
529 ,p_cty_attribute4_o
530 => ben_cty_shd.g_old_rec.cty_attribute4
531 ,p_cty_attribute5_o
532 => ben_cty_shd.g_old_rec.cty_attribute5
533 ,p_cty_attribute6_o
534 => ben_cty_shd.g_old_rec.cty_attribute6
535 ,p_cty_attribute7_o
536 => ben_cty_shd.g_old_rec.cty_attribute7
537 ,p_cty_attribute8_o
538 => ben_cty_shd.g_old_rec.cty_attribute8
539 ,p_cty_attribute9_o
540 => ben_cty_shd.g_old_rec.cty_attribute9
541 ,p_cty_attribute10_o
542 => ben_cty_shd.g_old_rec.cty_attribute10
543 ,p_cty_attribute11_o
544 => ben_cty_shd.g_old_rec.cty_attribute11
545 ,p_cty_attribute12_o
546 => ben_cty_shd.g_old_rec.cty_attribute12
547 ,p_cty_attribute13_o
548 => ben_cty_shd.g_old_rec.cty_attribute13
549 ,p_cty_attribute14_o
550 => ben_cty_shd.g_old_rec.cty_attribute14
551 ,p_cty_attribute15_o
552 => ben_cty_shd.g_old_rec.cty_attribute15
553 ,p_cty_attribute16_o
554 => ben_cty_shd.g_old_rec.cty_attribute16
555 ,p_cty_attribute17_o
556 => ben_cty_shd.g_old_rec.cty_attribute17
557 ,p_cty_attribute18_o
558 => ben_cty_shd.g_old_rec.cty_attribute18
559 ,p_cty_attribute19_o
560 => ben_cty_shd.g_old_rec.cty_attribute19
561 ,p_cty_attribute20_o
562 => ben_cty_shd.g_old_rec.cty_attribute20
563 ,p_cty_attribute21_o
564 => ben_cty_shd.g_old_rec.cty_attribute21
565 ,p_cty_attribute22_o
566 => ben_cty_shd.g_old_rec.cty_attribute22
567 ,p_cty_attribute23_o
568 => ben_cty_shd.g_old_rec.cty_attribute23
569 ,p_cty_attribute24_o
570 => ben_cty_shd.g_old_rec.cty_attribute24
571 ,p_cty_attribute25_o
572 => ben_cty_shd.g_old_rec.cty_attribute25
573 ,p_cty_attribute26_o
574 => ben_cty_shd.g_old_rec.cty_attribute26
575 ,p_cty_attribute27_o
576 => ben_cty_shd.g_old_rec.cty_attribute27
577 ,p_cty_attribute28_o
578 => ben_cty_shd.g_old_rec.cty_attribute28
579 ,p_cty_attribute29_o
580 => ben_cty_shd.g_old_rec.cty_attribute29
581 ,p_cty_attribute30_o
582 => ben_cty_shd.g_old_rec.cty_attribute30
583 );
584 --
585 exception
586 --
587 when hr_api.cannot_find_prog_unit then
588 --
589 hr_api.cannot_find_prog_unit_error
590 (p_module_name => 'BEN_comptncy_rt_F'
591 ,p_hook_type => 'AU');
592 --
593 end;
594 --
595 hr_utility.set_location(' Leaving:'||l_proc, 10);
596 End post_update;
597 --
598 -- ----------------------------------------------------------------------------
599 -- |-----------------------------< convert_defs >-----------------------------|
600 -- ----------------------------------------------------------------------------
601 -- {Start Of Comments}
602 --
603 -- Description:
604 -- The Convert_Defs procedure has one very important function:
605 -- It must return the record structure for the row with all system defaulted
606 -- values converted into its corresponding parameter value for update. When
607 -- we attempt to update a row through the Upd process , certain
608 -- parameters can be defaulted which enables flexibility in the calling of
609 -- the upd process (e.g. only attributes which need to be updated need to be
610 -- specified). For the upd process to determine which attributes
611 -- have NOT been specified we need to check if the parameter has a reserved
612 -- system default value. Therefore, for all parameters which have a
613 -- corresponding reserved system default mechanism specified we need to
614 -- check if a system default is being used. If a system default is being
615 -- used then we convert the defaulted value into its corresponding attribute
616 -- value held in the g_old_rec data structure.
617 --
618 -- Prerequisites:
619 -- This private function can only be called from the upd process.
620 --
621 -- In Parameters:
622 -- A Pl/Sql record structure.
623 --
624 -- Post Success:
625 -- The record structure will be returned with all system defaulted parameter
626 -- values converted into its current row attribute value.
627 --
628 -- Post Failure:
629 -- No direct error handling is required within this function. Any possible
630 -- errors within this procedure will be a PL/SQL value error due to
631 -- conversion of datatypes or data lengths.
632 --
633 -- Developer Implementation Notes:
634 -- None.
635 --
636 -- Access Status:
637 -- Internal Row Handler Use Only.
638 --
639 -- {End Of Comments}
640 -- ----------------------------------------------------------------------------
641 Procedure convert_defs
642 (p_rec in out nocopy ben_cty_shd.g_rec_type
643 ) is
644 --
645 Begin
646 --
647 -- We must now examine each argument value in the
648 -- p_rec plsql record structure
649 -- to see if a system default is being used. If a system default
650 -- is being used then we must set to the 'current' argument value.
651 --
652 If (p_rec.competence_id = hr_api.g_number) then
653 p_rec.competence_id := ben_cty_shd.g_old_rec.competence_id;
654 End If;
655
656 If (p_rec.rating_level_id = hr_api.g_number) then
657 p_rec.rating_level_id := ben_cty_shd.g_old_rec.rating_level_id;
658 End If;
659
660 If (p_rec.excld_flag = hr_api.g_varchar2) then
661 p_rec.excld_flag :=
662 ben_cty_shd.g_old_rec.excld_flag;
663 End If;
664 If (p_rec.business_group_id = hr_api.g_number) then
665 p_rec.business_group_id :=
666 ben_cty_shd.g_old_rec.business_group_id;
667 End If;
668 If (p_rec.vrbl_rt_prfl_id = hr_api.g_number) then
669 p_rec.vrbl_rt_prfl_id :=
670 ben_cty_shd.g_old_rec.vrbl_rt_prfl_id;
671 End If;
672 If (p_rec.ordr_num = hr_api.g_number) then
673 p_rec.ordr_num :=
674 ben_cty_shd.g_old_rec.ordr_num;
675 End If;
676 If (p_rec.cty_attribute_category = hr_api.g_varchar2) then
677 p_rec.cty_attribute_category :=
678 ben_cty_shd.g_old_rec.cty_attribute_category;
679 End If;
680 If (p_rec.cty_attribute1 = hr_api.g_varchar2) then
681 p_rec.cty_attribute1 :=
682 ben_cty_shd.g_old_rec.cty_attribute1;
683 End If;
684 If (p_rec.cty_attribute2 = hr_api.g_varchar2) then
685 p_rec.cty_attribute2 :=
686 ben_cty_shd.g_old_rec.cty_attribute2;
687 End If;
688 If (p_rec.cty_attribute3 = hr_api.g_varchar2) then
689 p_rec.cty_attribute3 :=
690 ben_cty_shd.g_old_rec.cty_attribute3;
691 End If;
692 If (p_rec.cty_attribute4 = hr_api.g_varchar2) then
693 p_rec.cty_attribute4 :=
694 ben_cty_shd.g_old_rec.cty_attribute4;
695 End If;
696 If (p_rec.cty_attribute5 = hr_api.g_varchar2) then
697 p_rec.cty_attribute5 :=
698 ben_cty_shd.g_old_rec.cty_attribute5;
699 End If;
700 If (p_rec.cty_attribute6 = hr_api.g_varchar2) then
701 p_rec.cty_attribute6 :=
702 ben_cty_shd.g_old_rec.cty_attribute6;
703 End If;
704 If (p_rec.cty_attribute7 = hr_api.g_varchar2) then
705 p_rec.cty_attribute7 :=
706 ben_cty_shd.g_old_rec.cty_attribute7;
707 End If;
708 If (p_rec.cty_attribute8 = hr_api.g_varchar2) then
709 p_rec.cty_attribute8 :=
710 ben_cty_shd.g_old_rec.cty_attribute8;
711 End If;
712 If (p_rec.cty_attribute9 = hr_api.g_varchar2) then
713 p_rec.cty_attribute9 :=
714 ben_cty_shd.g_old_rec.cty_attribute9;
715 End If;
716 If (p_rec.cty_attribute10 = hr_api.g_varchar2) then
717 p_rec.cty_attribute10 :=
718 ben_cty_shd.g_old_rec.cty_attribute10;
719 End If;
720 If (p_rec.cty_attribute11 = hr_api.g_varchar2) then
721 p_rec.cty_attribute11 :=
722 ben_cty_shd.g_old_rec.cty_attribute11;
723 End If;
724 If (p_rec.cty_attribute12 = hr_api.g_varchar2) then
725 p_rec.cty_attribute12 :=
726 ben_cty_shd.g_old_rec.cty_attribute12;
727 End If;
728 If (p_rec.cty_attribute13 = hr_api.g_varchar2) then
729 p_rec.cty_attribute13 :=
730 ben_cty_shd.g_old_rec.cty_attribute13;
731 End If;
732 If (p_rec.cty_attribute14 = hr_api.g_varchar2) then
733 p_rec.cty_attribute14 :=
734 ben_cty_shd.g_old_rec.cty_attribute14;
735 End If;
736 If (p_rec.cty_attribute15 = hr_api.g_varchar2) then
737 p_rec.cty_attribute15 :=
738 ben_cty_shd.g_old_rec.cty_attribute15;
739 End If;
740 If (p_rec.cty_attribute16 = hr_api.g_varchar2) then
741 p_rec.cty_attribute16 :=
742 ben_cty_shd.g_old_rec.cty_attribute16;
743 End If;
744 If (p_rec.cty_attribute17 = hr_api.g_varchar2) then
745 p_rec.cty_attribute17 :=
746 ben_cty_shd.g_old_rec.cty_attribute17;
747 End If;
748 If (p_rec.cty_attribute18 = hr_api.g_varchar2) then
749 p_rec.cty_attribute18 :=
750 ben_cty_shd.g_old_rec.cty_attribute18;
751 End If;
752 If (p_rec.cty_attribute19 = hr_api.g_varchar2) then
753 p_rec.cty_attribute19 :=
754 ben_cty_shd.g_old_rec.cty_attribute19;
755 End If;
756 If (p_rec.cty_attribute20 = hr_api.g_varchar2) then
757 p_rec.cty_attribute20 :=
758 ben_cty_shd.g_old_rec.cty_attribute20;
759 End If;
760 If (p_rec.cty_attribute21 = hr_api.g_varchar2) then
761 p_rec.cty_attribute21 :=
762 ben_cty_shd.g_old_rec.cty_attribute21;
763 End If;
764 If (p_rec.cty_attribute22 = hr_api.g_varchar2) then
765 p_rec.cty_attribute22 :=
766 ben_cty_shd.g_old_rec.cty_attribute22;
767 End If;
768 If (p_rec.cty_attribute23 = hr_api.g_varchar2) then
769 p_rec.cty_attribute23 :=
770 ben_cty_shd.g_old_rec.cty_attribute23;
771 End If;
772 If (p_rec.cty_attribute24 = hr_api.g_varchar2) then
773 p_rec.cty_attribute24 :=
774 ben_cty_shd.g_old_rec.cty_attribute24;
775 End If;
776 If (p_rec.cty_attribute25 = hr_api.g_varchar2) then
777 p_rec.cty_attribute25 :=
778 ben_cty_shd.g_old_rec.cty_attribute25;
779 End If;
780 If (p_rec.cty_attribute26 = hr_api.g_varchar2) then
781 p_rec.cty_attribute26 :=
782 ben_cty_shd.g_old_rec.cty_attribute26;
783 End If;
784 If (p_rec.cty_attribute27 = hr_api.g_varchar2) then
785 p_rec.cty_attribute27 :=
786 ben_cty_shd.g_old_rec.cty_attribute27;
787 End If;
788 If (p_rec.cty_attribute28 = hr_api.g_varchar2) then
789 p_rec.cty_attribute28 :=
790 ben_cty_shd.g_old_rec.cty_attribute28;
791 End If;
792 If (p_rec.cty_attribute29 = hr_api.g_varchar2) then
793 p_rec.cty_attribute29 :=
794 ben_cty_shd.g_old_rec.cty_attribute29;
795 End If;
796 If (p_rec.cty_attribute30 = hr_api.g_varchar2) then
797 p_rec.cty_attribute30 :=
798 ben_cty_shd.g_old_rec.cty_attribute30;
799 End If;
800 --
801 End convert_defs;
802 --
803 -- ----------------------------------------------------------------------------
804 -- |---------------------------------< upd >----------------------------------|
805 -- ----------------------------------------------------------------------------
806 Procedure upd
807 (p_effective_date in date
808 ,p_datetrack_mode in varchar2
809 ,p_rec in out nocopy ben_cty_shd.g_rec_type
810 ) is
811 --
812 l_proc varchar2(72) := g_package||'upd';
813 l_validation_start_date date;
814 l_validation_end_date date;
815 --
816 Begin
817 hr_utility.set_location('Entering:'||l_proc, 5);
818 --
819 -- Ensure that the DateTrack update mode is valid
820 --
821 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
822 --
823 -- We must lock the row which we need to update.
824 --
825 ben_cty_shd.lck
826 (p_effective_date => p_effective_date
827 ,p_datetrack_mode => p_datetrack_mode
828 ,p_comptncy_rt_id => p_rec.comptncy_rt_id
829 ,p_object_version_number => p_rec.object_version_number
830 ,p_validation_start_date => l_validation_start_date
831 ,p_validation_end_date => l_validation_end_date
832 );
833 --
834 -- 1. During an update system defaults are used to determine if
835 -- arguments have been defaulted or not. We must therefore
836 -- derive the full record structure values to be updated.
837 --
838 -- 2. Call the supporting update validate operations.
839 --
840 ben_cty_upd.convert_defs(p_rec);
841 --
842 ben_cty_bus.update_validate
843 (p_rec => p_rec
844 ,p_effective_date => p_effective_date
845 ,p_datetrack_mode => p_datetrack_mode
846 ,p_validation_start_date => l_validation_start_date
847 ,p_validation_end_date => l_validation_end_date
848 );
849 --
850 -- Call to raise any errors on multi-message list
851 hr_multi_message.end_validation_set;
852 --
853 -- Call the supporting pre-update operation
854 --
855 pre_update
856 (p_rec => p_rec
857 ,p_effective_date => p_effective_date
858 ,p_datetrack_mode => p_datetrack_mode
859 ,p_validation_start_date => l_validation_start_date
860 ,p_validation_end_date => l_validation_end_date
861 );
862 --
863 -- Update the row.
864 --
865 update_dml
866 (p_rec => p_rec
867 ,p_effective_date => p_effective_date
868 ,p_datetrack_mode => p_datetrack_mode
869 ,p_validation_start_date => l_validation_start_date
870 ,p_validation_end_date => l_validation_end_date
871 );
872 --
873 -- Call the supporting post-update operation
874 --
875 post_update
876 (p_rec => p_rec
877 ,p_effective_date => p_effective_date
878 ,p_datetrack_mode => p_datetrack_mode
879 ,p_validation_start_date => l_validation_start_date
880 ,p_validation_end_date => l_validation_end_date
881 );
882 --
883 -- Call to raise any errors on multi-message list
884 hr_multi_message.end_validation_set;
885 End upd;
886 --
887 -- ----------------------------------------------------------------------------
888 -- |------------------------------< upd >-------------------------------------|
889 -- ----------------------------------------------------------------------------
890 Procedure upd
891 (p_effective_date in date
892 ,p_datetrack_mode in varchar2
893 ,p_comptncy_rt_id in number
894 ,p_object_version_number in out nocopy number
895 ,p_competence_id in number default hr_api.g_number
896 ,p_rating_level_id in number default hr_api.g_number
897 ,p_excld_flag in varchar2 default hr_api.g_varchar2
898 ,p_business_group_id in number default hr_api.g_number
899 ,p_vrbl_rt_prfl_id in number default hr_api.g_number
900 ,p_ordr_num in number default hr_api.g_number
901 ,p_cty_attribute_category in varchar2 default hr_api.g_varchar2
902 ,p_cty_attribute1 in varchar2 default hr_api.g_varchar2
903 ,p_cty_attribute2 in varchar2 default hr_api.g_varchar2
904 ,p_cty_attribute3 in varchar2 default hr_api.g_varchar2
905 ,p_cty_attribute4 in varchar2 default hr_api.g_varchar2
906 ,p_cty_attribute5 in varchar2 default hr_api.g_varchar2
907 ,p_cty_attribute6 in varchar2 default hr_api.g_varchar2
908 ,p_cty_attribute7 in varchar2 default hr_api.g_varchar2
909 ,p_cty_attribute8 in varchar2 default hr_api.g_varchar2
910 ,p_cty_attribute9 in varchar2 default hr_api.g_varchar2
911 ,p_cty_attribute10 in varchar2 default hr_api.g_varchar2
912 ,p_cty_attribute11 in varchar2 default hr_api.g_varchar2
913 ,p_cty_attribute12 in varchar2 default hr_api.g_varchar2
914 ,p_cty_attribute13 in varchar2 default hr_api.g_varchar2
915 ,p_cty_attribute14 in varchar2 default hr_api.g_varchar2
916 ,p_cty_attribute15 in varchar2 default hr_api.g_varchar2
917 ,p_cty_attribute16 in varchar2 default hr_api.g_varchar2
918 ,p_cty_attribute17 in varchar2 default hr_api.g_varchar2
919 ,p_cty_attribute18 in varchar2 default hr_api.g_varchar2
920 ,p_cty_attribute19 in varchar2 default hr_api.g_varchar2
921 ,p_cty_attribute20 in varchar2 default hr_api.g_varchar2
922 ,p_cty_attribute21 in varchar2 default hr_api.g_varchar2
923 ,p_cty_attribute22 in varchar2 default hr_api.g_varchar2
924 ,p_cty_attribute23 in varchar2 default hr_api.g_varchar2
925 ,p_cty_attribute24 in varchar2 default hr_api.g_varchar2
926 ,p_cty_attribute25 in varchar2 default hr_api.g_varchar2
927 ,p_cty_attribute26 in varchar2 default hr_api.g_varchar2
928 ,p_cty_attribute27 in varchar2 default hr_api.g_varchar2
929 ,p_cty_attribute28 in varchar2 default hr_api.g_varchar2
930 ,p_cty_attribute29 in varchar2 default hr_api.g_varchar2
931 ,p_cty_attribute30 in varchar2 default hr_api.g_varchar2
932 ,p_effective_start_date out nocopy date
933 ,p_effective_end_date out nocopy date
934 ) is
935 --
936 l_rec ben_cty_shd.g_rec_type;
937 l_proc varchar2(72) := g_package||'upd';
938 --
939 Begin
940 hr_utility.set_location('Entering:'||l_proc, 5);
941 --
942 -- Call conversion function to turn arguments into the
943 -- l_rec structure.
944 --
945 l_rec :=
946 ben_cty_shd.convert_args
947 (p_comptncy_rt_id
948 ,null
949 ,null
950 ,p_competence_id
951 ,p_rating_level_id
952 ,p_excld_flag
953 ,p_business_group_id
954 ,p_vrbl_rt_prfl_id
955 ,p_object_version_number
956 ,p_ordr_num
957 ,p_cty_attribute_category
958 ,p_cty_attribute1
959 ,p_cty_attribute2
960 ,p_cty_attribute3
961 ,p_cty_attribute4
962 ,p_cty_attribute5
963 ,p_cty_attribute6
964 ,p_cty_attribute7
965 ,p_cty_attribute8
966 ,p_cty_attribute9
967 ,p_cty_attribute10
968 ,p_cty_attribute11
969 ,p_cty_attribute12
970 ,p_cty_attribute13
971 ,p_cty_attribute14
972 ,p_cty_attribute15
973 ,p_cty_attribute16
974 ,p_cty_attribute17
975 ,p_cty_attribute18
976 ,p_cty_attribute19
977 ,p_cty_attribute20
978 ,p_cty_attribute21
979 ,p_cty_attribute22
980 ,p_cty_attribute23
981 ,p_cty_attribute24
982 ,p_cty_attribute25
983 ,p_cty_attribute26
984 ,p_cty_attribute27
985 ,p_cty_attribute28
986 ,p_cty_attribute29
987 ,p_cty_attribute30
988 );
989 --
990 -- Having converted the arguments into the
991 -- plsql record structure we call the corresponding record
992 -- business process.
993 --
994 ben_cty_upd.upd
995 (p_effective_date
996 ,p_datetrack_mode
997 ,l_rec
998 );
999 --
1000 -- Set the out parameters
1001 --
1002 p_object_version_number := l_rec.object_version_number;
1003 p_effective_start_date := l_rec.effective_start_date;
1004 p_effective_end_date := l_rec.effective_end_date;
1005 --
1006 --
1007 hr_utility.set_location(' Leaving:'||l_proc, 10);
1008 End upd;
1009 --
1010 end ben_cty_upd;