1 Package Body pay_pel_upd as
2 /* $Header: pypelrhi.pkb 120.7.12010000.3 2008/10/03 08:41:56 ankagarw ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pel_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 pay_pel_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 => 'pay_element_links_f'
79 ,p_base_key_column => 'element_link_id'
80 ,p_base_key_value => p_rec.element_link_id
81 );
82 --
83 pay_pel_shd.g_api_dml := true; -- Set the api dml status
84 --
85 -- Update the pay_element_links_f Row
86 --
87 update pay_element_links_f
88 set
89 element_link_id = p_rec.element_link_id
90 ,cost_allocation_keyflex_id = p_rec.cost_allocation_keyflex_id
91 ,element_type_id = p_rec.element_type_id
92 ,balancing_keyflex_id = p_rec.balancing_keyflex_id
93 ,business_group_id = p_rec.business_group_id
94 ,element_set_id = p_rec.element_set_id
95 ,costable_type = p_rec.costable_type
96 ,multiply_value_flag = p_rec.multiply_value_flag
97 ,standard_link_flag = p_rec.standard_link_flag
98 ,transfer_to_gl_flag = p_rec.transfer_to_gl_flag
99 ,comment_id = p_rec.comment_id
100 ,employment_category = p_rec.employment_category
101 ,qualifying_age = p_rec.qualifying_age
102 ,qualifying_length_of_service = p_rec.qualifying_length_of_service
103 ,qualifying_units = p_rec.qualifying_units
104 ,attribute_category = p_rec.attribute_category
105 ,attribute1 = p_rec.attribute1
106 ,attribute2 = p_rec.attribute2
107 ,attribute3 = p_rec.attribute3
108 ,attribute4 = p_rec.attribute4
109 ,attribute5 = p_rec.attribute5
110 ,attribute6 = p_rec.attribute6
111 ,attribute7 = p_rec.attribute7
112 ,attribute8 = p_rec.attribute8
113 ,attribute9 = p_rec.attribute9
114 ,attribute10 = p_rec.attribute10
115 ,attribute11 = p_rec.attribute11
116 ,attribute12 = p_rec.attribute12
117 ,attribute13 = p_rec.attribute13
118 ,attribute14 = p_rec.attribute14
119 ,attribute15 = p_rec.attribute15
120 ,attribute16 = p_rec.attribute16
121 ,attribute17 = p_rec.attribute17
122 ,attribute18 = p_rec.attribute18
123 ,attribute19 = p_rec.attribute19
124 ,attribute20 = p_rec.attribute20
125 ,object_version_number = p_rec.object_version_number
126 where element_link_id = p_rec.element_link_id
127 and effective_start_date = p_validation_start_date
128 and effective_end_date = p_validation_end_date;
129 --
130 pay_pel_shd.g_api_dml := false; -- Unset the api dml status
131 --
132 -- Set the effective start and end dates
133 --
134 p_rec.effective_start_date := p_validation_start_date;
135 p_rec.effective_end_date := p_validation_end_date;
136 End If;
137 --
138 hr_utility.set_location(' Leaving:'||l_proc, 15);
139 Exception
140 When hr_api.check_integrity_violated Then
141 -- A check constraint has been violated
142 pay_pel_shd.g_api_dml := false; -- Unset the api dml status
143 pay_pel_shd.constraint_error
144 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
145 When hr_api.unique_integrity_violated Then
146 -- Unique integrity has been violated
147 pay_pel_shd.g_api_dml := false; -- Unset the api dml status
148 pay_pel_shd.constraint_error
149 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
150 When Others Then
151 pay_pel_shd.g_api_dml := false; -- Unset the api dml status
152 Raise;
153 End dt_update_dml;
154 --
155 -- ----------------------------------------------------------------------------
156 -- |------------------------------< update_dml >------------------------------|
157 -- ----------------------------------------------------------------------------
158 -- {Start Of Comments}
159 --
160 -- Description:
161 -- This procedure calls the dt_update_dml control logic which handles
162 -- the actual datetrack dml.
163 --
164 -- Prerequisites:
165 -- This is an internal private procedure which must be called from the upd
166 -- procedure.
167 --
168 -- In Parameters:
169 -- A Pl/Sql record structre.
170 --
171 -- Post Success:
172 -- Processing contines.
173 --
174 -- Post Failure:
175 -- No specific error handling is required within this procedure.
176 --
177 -- Developer Implementation Notes:
178 -- The update 'set' arguments list should be modified if any of your
179 -- attributes are not updateable.
180 --
181 -- Access Status:
182 -- Internal Row Handler Use Only.
183 --
184 -- {End Of Comments}
185 -- ----------------------------------------------------------------------------
186 Procedure update_dml
187 (p_rec in out nocopy pay_pel_shd.g_rec_type
188 ,p_effective_date in date
189 ,p_datetrack_mode in varchar2
190 ,p_validation_start_date in date
191 ,p_validation_end_date in date
192 ) is
193 --
194 l_proc varchar2(72) := g_package||'update_dml';
195 --
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 --
199 pay_pel_upd.dt_update_dml
200 (p_rec => p_rec
201 ,p_effective_date => p_effective_date
202 ,p_datetrack_mode => p_datetrack_mode
203 ,p_validation_start_date => p_validation_start_date
204 ,p_validation_end_date => p_validation_end_date
205 );
206 --
207 hr_utility.set_location(' Leaving:'||l_proc, 10);
208 End update_dml;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |----------------------------< dt_pre_update >-----------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- The dt_pre_update procedure controls the execution
217 -- of dml for the datetrack modes of: UPDATE, UPDATE_OVERRIDE
218 -- and UPDATE_CHANGE_INSERT only. The execution required is as
219 -- follows:
220 --
221 -- 1) Providing the datetrack update mode is not 'CORRECTION'
222 -- then set the effective end date of the current row (this
223 -- will be the validation_start_date - 1).
224 -- 2) If the datetrack mode is 'UPDATE_OVERRIDE' then call the
225 -- corresponding delete_dml process to delete any future rows
226 -- where the effective_start_date is greater than or equal to
227 -- the validation_start_date.
228 -- 3) Call the insert_dml process to insert the new updated row
229 -- details.
230 --
231 -- Prerequisites:
232 -- This is an internal procedure which is called from the
233 -- pre_update procedure.
234 --
235 -- In Parameters:
236 --
237 -- Post Success:
238 -- Processing continues.
239 --
240 -- Post Failure:
241 -- If an error has occurred, an error message and exception will be raised
242 -- but not handled.
243 --
244 -- Developer Implementation Notes:
245 -- This is an internal procedure which is required by Datetrack. Don't
246 -- remove or modify.
247 --
248 -- Access Status:
249 -- Internal Row Handler Use Only.
250 --
251 -- {End Of Comments}
252 -- ----------------------------------------------------------------------------
253 Procedure dt_pre_update
254 (p_rec in out nocopy pay_pel_shd.g_rec_type
255 ,p_effective_date in date
256 ,p_datetrack_mode in varchar2
257 ,p_validation_start_date in date
258 ,p_validation_end_date in date
259 ) is
260 --
261 l_proc varchar2(72) := g_package||'dt_pre_update';
262 l_dummy_version_number number;
263 --
264 Begin
265 hr_utility.set_location('Entering:'||l_proc, 5);
266 If (p_datetrack_mode <> hr_api.g_correction) then
267 --
268 -- Update the current effective end date
269 --
270
271 pay_pel_shd.upd_effective_end_date
272 (p_effective_date => p_effective_date
273 ,p_base_key_value => p_rec.element_link_id
274 ,p_new_effective_end_date => (p_validation_start_date - 1)
275 ,p_validation_start_date => p_validation_start_date
276 ,p_validation_end_date => p_validation_end_date
277 ,p_object_version_number => l_dummy_version_number
278 );
279 --
280 If (p_datetrack_mode = hr_api.g_update_override) then
281 --
282 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
283 -- delete any future rows
284 --
285 pay_pel_del.delete_dml
286 (p_rec => p_rec
287 ,p_effective_date => p_effective_date
288 ,p_datetrack_mode => p_datetrack_mode
289 ,p_validation_start_date => p_validation_start_date
290 ,p_validation_end_date => p_validation_end_date
291 );
292 End If;
293 --
294 -- We must now insert the updated row
295 --
296 pay_pel_ins.insert_dml
297 (p_rec => p_rec
298 ,p_effective_date => p_effective_date
299 ,p_datetrack_mode => p_datetrack_mode
300 ,p_validation_start_date => p_validation_start_date
301 ,p_validation_end_date => p_validation_end_date
302 );
303 End If;
304 hr_utility.set_location(' Leaving:'||l_proc, 20);
305 End dt_pre_update;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |------------------------------< pre_update >------------------------------|
309 -- ----------------------------------------------------------------------------
310 -- {Start Of Comments}
311 --
312 -- Description:
313 -- This private procedure contains any processing which is required before
314 -- the update dml.
315 --
316 -- Prerequisites:
317 -- This is an internal procedure which is called from the upd procedure.
318 --
319 -- In Parameters:
320 -- A Pl/Sql record structure.
321 --
322 -- Post Success:
323 -- Processing continues.
324 --
325 -- Post Failure:
326 -- If an error has occurred, an error message and exception will be raised
327 -- but not handled.
328 -- Developer Implementation Notes:
329 -- Any pre-processing required before the update dml is issued should be
330 -- coded within this procedure. It is important to note that any 3rd party
331 -- maintenance should be reviewed before placing in this procedure. The call
332 -- to the dt_update_dml procedure should NOT be removed.
333 --
334 -- Access Status:
335 -- Internal Row Handler Use Only.
336 --
337 -- {End Of Comments}
338 -- ----------------------------------------------------------------------------
339 Procedure pre_update
340 (p_rec in out nocopy pay_pel_shd.g_rec_type
341 ,p_effective_date in date
342 ,p_datetrack_mode in varchar2
343 ,p_validation_start_date in date
344 ,p_validation_end_date in date
345 ) is
346 --
347 l_proc varchar2(72) := g_package||'pre_update';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 --
353 -- Insert the comment text if comments exist
354 --
355 If (p_rec.comments is not null and p_rec.comment_id is null) then
356 hr_comm_api.ins(p_comment_id => p_rec.comment_id
357 ,p_source_table_name => 'PAY_ELEMENT_LINKS_F'
358 ,p_comment_text => p_rec.comments
359 );
360 -- Update the comments if they have changed
361 ElsIf (p_rec.comment_id is not null and p_rec.comments <>
362 pay_pel_shd.g_old_rec.comments) then
363 hr_comm_api.upd(p_comment_id => p_rec.comment_id
364 ,p_source_table_name => 'PAY_ELEMENT_LINKS_F'
365 ,p_comment_text => p_rec.comments
366 );
367 End If;
368 --
369 dt_pre_update
370 (p_rec => p_rec
371 ,p_effective_date => p_effective_date
372 ,p_datetrack_mode => p_datetrack_mode
373 ,p_validation_start_date => p_validation_start_date
374 ,p_validation_end_date => p_validation_end_date
375 );
376 --
377 hr_utility.set_location(' Leaving:'||l_proc, 10);
378 End pre_update;
379 --
380 -- ----------------------------------------------------------------------------
381 -- |----------------------------< post_update >-------------------------------|
382 -- ----------------------------------------------------------------------------
383 -- {Start Of Comments}
384 --
385 -- Description:
386 -- This private procedure contains any processing which is required after
387 -- the update dml.
388 --
389 -- Prerequisites:
390 -- This is an internal procedure which is called from the upd procedure.
391 --
392 -- In Parameters:
393 -- A Pl/Sql record structure.
394 --
395 -- Post Success:
396 -- Processing continues.
397 --
398 -- Post Failure:
399 -- If an error has occurred, an error message and exception will be raised
400 -- but not handled.
401 --
402 -- Developer Implementation Notes:
403 -- Any post-processing required after the update dml is issued should be
404 -- coded within this procedure. It is important to note that any 3rd party
405 -- maintenance should be reviewed before placing in this procedure.
406 --
407 -- Access Status:
408 -- Internal Row Handler Use Only.
409 --
410 -- {End Of Comments}
411 -- ----------------------------------------------------------------------------
412 Procedure post_update
413 (p_rec in pay_pel_shd.g_rec_type
414 ,p_effective_date in date
415 ,p_datetrack_mode in varchar2
416 ,p_validation_start_date in date
417 ,p_validation_end_date in date
418 ) is
419 --
420 l_proc varchar2(72) := g_package||'post_update';
421 --
422 Begin
423 hr_utility.set_location('Entering:'||l_proc, 5);
424 begin
425 --
426 pay_pel_rku.after_update
427 (p_effective_date
428 => p_effective_date
429 ,p_datetrack_mode
430 => p_datetrack_mode
431 ,p_validation_start_date
432 => p_validation_start_date
433 ,p_validation_end_date
434 => p_validation_end_date
435 ,p_element_link_id
436 => p_rec.element_link_id
437 ,p_effective_start_date
438 => p_rec.effective_start_date
439 ,p_effective_end_date
440 => p_rec.effective_end_date
441 ,p_payroll_id
442 => p_rec.payroll_id
443 ,p_job_id
444 => p_rec.job_id
445 ,p_position_id
446 => p_rec.position_id
447 ,p_people_group_id
448 => p_rec.people_group_id
449 ,p_cost_allocation_keyflex_id
450 => p_rec.cost_allocation_keyflex_id
451 ,p_organization_id
452 => p_rec.organization_id
453 ,p_element_type_id
454 => p_rec.element_type_id
455 ,p_location_id
456 => p_rec.location_id
457 ,p_grade_id
458 => p_rec.grade_id
459 ,p_balancing_keyflex_id
460 => p_rec.balancing_keyflex_id
461 ,p_business_group_id
462 => p_rec.business_group_id
463 ,p_element_set_id
464 => p_rec.element_set_id
465 ,p_pay_basis_id
466 => p_rec.pay_basis_id
467 ,p_costable_type
468 => p_rec.costable_type
469 ,p_link_to_all_payrolls_flag
470 => p_rec.link_to_all_payrolls_flag
471 ,p_multiply_value_flag
472 => p_rec.multiply_value_flag
473 ,p_standard_link_flag
474 => p_rec.standard_link_flag
475 ,p_transfer_to_gl_flag
476 => p_rec.transfer_to_gl_flag
477 ,p_comment_id
478 => p_rec.comment_id
479 ,p_comments
480 => p_rec.comments
481 ,p_employment_category
482 => p_rec.employment_category
483 ,p_qualifying_age
484 => p_rec.qualifying_age
485 ,p_qualifying_length_of_service
486 => p_rec.qualifying_length_of_service
487 ,p_qualifying_units
488 => p_rec.qualifying_units
489 ,p_attribute_category
490 => p_rec.attribute_category
491 ,p_attribute1
492 => p_rec.attribute1
493 ,p_attribute2
494 => p_rec.attribute2
495 ,p_attribute3
496 => p_rec.attribute3
497 ,p_attribute4
498 => p_rec.attribute4
499 ,p_attribute5
500 => p_rec.attribute5
501 ,p_attribute6
502 => p_rec.attribute6
503 ,p_attribute7
504 => p_rec.attribute7
505 ,p_attribute8
506 => p_rec.attribute8
507 ,p_attribute9
508 => p_rec.attribute9
509 ,p_attribute10
510 => p_rec.attribute10
511 ,p_attribute11
512 => p_rec.attribute11
513 ,p_attribute12
514 => p_rec.attribute12
515 ,p_attribute13
516 => p_rec.attribute13
517 ,p_attribute14
518 => p_rec.attribute14
519 ,p_attribute15
520 => p_rec.attribute15
521 ,p_attribute16
522 => p_rec.attribute16
523 ,p_attribute17
524 => p_rec.attribute17
525 ,p_attribute18
526 => p_rec.attribute18
527 ,p_attribute19
528 => p_rec.attribute19
529 ,p_attribute20
530 => p_rec.attribute20
531 ,p_object_version_number
532 => p_rec.object_version_number
533 ,p_effective_start_date_o
534 => pay_pel_shd.g_old_rec.effective_start_date
535 ,p_effective_end_date_o
536 => pay_pel_shd.g_old_rec.effective_end_date
537 ,p_payroll_id_o
538 => pay_pel_shd.g_old_rec.payroll_id
539 ,p_job_id_o
540 => pay_pel_shd.g_old_rec.job_id
541 ,p_position_id_o
542 => pay_pel_shd.g_old_rec.position_id
543 ,p_people_group_id_o
544 => pay_pel_shd.g_old_rec.people_group_id
545 ,p_cost_allocation_keyflex_id_o
546 => pay_pel_shd.g_old_rec.cost_allocation_keyflex_id
547 ,p_organization_id_o
548 => pay_pel_shd.g_old_rec.organization_id
549 ,p_element_type_id_o
550 => pay_pel_shd.g_old_rec.element_type_id
551 ,p_location_id_o
552 => pay_pel_shd.g_old_rec.location_id
553 ,p_grade_id_o
554 => pay_pel_shd.g_old_rec.grade_id
555 ,p_balancing_keyflex_id_o
556 => pay_pel_shd.g_old_rec.balancing_keyflex_id
557 ,p_business_group_id_o
558 => pay_pel_shd.g_old_rec.business_group_id
559 ,p_element_set_id_o
560 => pay_pel_shd.g_old_rec.element_set_id
561 ,p_pay_basis_id_o
562 => pay_pel_shd.g_old_rec.pay_basis_id
563 ,p_costable_type_o
564 => pay_pel_shd.g_old_rec.costable_type
565 ,p_link_to_all_payrolls_flag_o
566 => pay_pel_shd.g_old_rec.link_to_all_payrolls_flag
567 ,p_multiply_value_flag_o
568 => pay_pel_shd.g_old_rec.multiply_value_flag
569 ,p_standard_link_flag_o
570 => pay_pel_shd.g_old_rec.standard_link_flag
571 ,p_transfer_to_gl_flag_o
572 => pay_pel_shd.g_old_rec.transfer_to_gl_flag
573 ,p_comment_id_o
574 => pay_pel_shd.g_old_rec.comment_id
575 ,p_comments_o
576 => pay_pel_shd.g_old_rec.comments
577 ,p_employment_category_o
578 => pay_pel_shd.g_old_rec.employment_category
579 ,p_qualifying_age_o
580 => pay_pel_shd.g_old_rec.qualifying_age
581 ,p_qualifying_length_of_servi_o
582 => pay_pel_shd.g_old_rec.qualifying_length_of_service
583 ,p_qualifying_units_o
584 => pay_pel_shd.g_old_rec.qualifying_units
585 ,p_attribute_category_o
586 => pay_pel_shd.g_old_rec.attribute_category
587 ,p_attribute1_o
588 => pay_pel_shd.g_old_rec.attribute1
589 ,p_attribute2_o
590 => pay_pel_shd.g_old_rec.attribute2
591 ,p_attribute3_o
592 => pay_pel_shd.g_old_rec.attribute3
593 ,p_attribute4_o
594 => pay_pel_shd.g_old_rec.attribute4
595 ,p_attribute5_o
596 => pay_pel_shd.g_old_rec.attribute5
597 ,p_attribute6_o
598 => pay_pel_shd.g_old_rec.attribute6
599 ,p_attribute7_o
600 => pay_pel_shd.g_old_rec.attribute7
601 ,p_attribute8_o
602 => pay_pel_shd.g_old_rec.attribute8
603 ,p_attribute9_o
604 => pay_pel_shd.g_old_rec.attribute9
605 ,p_attribute10_o
606 => pay_pel_shd.g_old_rec.attribute10
607 ,p_attribute11_o
608 => pay_pel_shd.g_old_rec.attribute11
609 ,p_attribute12_o
610 => pay_pel_shd.g_old_rec.attribute12
611 ,p_attribute13_o
612 => pay_pel_shd.g_old_rec.attribute13
613 ,p_attribute14_o
614 => pay_pel_shd.g_old_rec.attribute14
615 ,p_attribute15_o
616 => pay_pel_shd.g_old_rec.attribute15
617 ,p_attribute16_o
618 => pay_pel_shd.g_old_rec.attribute16
619 ,p_attribute17_o
620 => pay_pel_shd.g_old_rec.attribute17
621 ,p_attribute18_o
622 => pay_pel_shd.g_old_rec.attribute18
623 ,p_attribute19_o
624 => pay_pel_shd.g_old_rec.attribute19
625 ,p_attribute20_o
626 => pay_pel_shd.g_old_rec.attribute20
627 ,p_object_version_number_o
628 => pay_pel_shd.g_old_rec.object_version_number
629 );
630 --
631 exception
632 --
633 when hr_api.cannot_find_prog_unit then
634 --
635 hr_api.cannot_find_prog_unit_error
636 (p_module_name => 'PAY_ELEMENT_LINKS_F'
637 ,p_hook_type => 'AU');
638 --
639 end;
640 --
641 hr_utility.set_location(' Leaving:'||l_proc, 10);
642 End post_update;
643 --
644 -- ----------------------------------------------------------------------------
645 -- |-----------------------------< convert_defs >-----------------------------|
646 -- ----------------------------------------------------------------------------
647 -- {Start Of Comments}
648 --
649 -- Description:
650 -- The Convert_Defs procedure has one very important function:
651 -- It must return the record structure for the row with all system defaulted
652 -- values converted into its corresponding parameter value for update. When
653 -- we attempt to update a row through the Upd process , certain
654 -- parameters can be defaulted which enables flexibility in the calling of
655 -- the upd process (e.g. only attributes which need to be updated need to be
656 -- specified). For the upd process to determine which attributes
657 -- have NOT been specified we need to check if the parameter has a reserved
658 -- system default value. Therefore, for all parameters which have a
659 -- corresponding reserved system default mechanism specified we need to
660 -- check if a system default is being used. If a system default is being
661 -- used then we convert the defaulted value into its corresponding attribute
662 -- value held in the g_old_rec data structure.
663 --
664 -- Prerequisites:
665 -- This private function can only be called from the upd process.
666 --
667 -- In Parameters:
668 -- A Pl/Sql record structure.
669 --
670 -- Post Success:
671 -- The record structure will be returned with all system defaulted parameter
672 -- values converted into its current row attribute value.
673 --
674 -- Post Failure:
675 -- No direct error handling is required within this function. Any possible
676 -- errors within this procedure will be a PL/SQL value error due to
677 -- conversion of datatypes or data lengths.
678 --
679 -- Developer Implementation Notes:
680 -- None.
681 --
682 -- Access Status:
683 -- Internal Row Handler Use Only.
684 --
685 -- {End Of Comments}
686 -- ----------------------------------------------------------------------------
687 Procedure convert_defs
688 (p_rec in out nocopy pay_pel_shd.g_rec_type
689 ) is
690 --
691 Begin
692 --
693 -- We must now examine each argument value in the
694 -- p_rec plsql record structure
695 -- to see if a system default is being used. If a system default
696 -- is being used then we must set to the 'current' argument value.
697 --
698 If (p_rec.payroll_id = hr_api.g_number) then
699 p_rec.payroll_id :=
700 pay_pel_shd.g_old_rec.payroll_id;
701 End If;
702 If (p_rec.job_id = hr_api.g_number) then
703 p_rec.job_id :=
704 pay_pel_shd.g_old_rec.job_id;
705 End If;
706 If (p_rec.position_id = hr_api.g_number) then
707 p_rec.position_id :=
708 pay_pel_shd.g_old_rec.position_id;
709 End If;
710 If (p_rec.people_group_id = hr_api.g_number) then
711 p_rec.people_group_id :=
712 pay_pel_shd.g_old_rec.people_group_id;
713 End If;
714 If (p_rec.cost_allocation_keyflex_id = hr_api.g_number) then
715 p_rec.cost_allocation_keyflex_id :=
716 pay_pel_shd.g_old_rec.cost_allocation_keyflex_id;
717 End If;
718 If (p_rec.organization_id = hr_api.g_number) then
719 p_rec.organization_id :=
720 pay_pel_shd.g_old_rec.organization_id;
721 End If;
722 If (p_rec.element_type_id = hr_api.g_number) then
723 p_rec.element_type_id :=
724 pay_pel_shd.g_old_rec.element_type_id;
725 End If;
726 If (p_rec.location_id = hr_api.g_number) then
727 p_rec.location_id :=
728 pay_pel_shd.g_old_rec.location_id;
729 End If;
730 If (p_rec.grade_id = hr_api.g_number) then
731 p_rec.grade_id :=
732 pay_pel_shd.g_old_rec.grade_id;
733 End If;
734 If (p_rec.balancing_keyflex_id = hr_api.g_number) then
735 p_rec.balancing_keyflex_id :=
736 pay_pel_shd.g_old_rec.balancing_keyflex_id;
737 End If;
738 If (p_rec.business_group_id = hr_api.g_number) then
739 p_rec.business_group_id :=
740 pay_pel_shd.g_old_rec.business_group_id;
741 End If;
742 If (p_rec.element_set_id = hr_api.g_number) then
743 p_rec.element_set_id :=
744 pay_pel_shd.g_old_rec.element_set_id;
745 End If;
746 If (p_rec.pay_basis_id = hr_api.g_number) then
747 p_rec.pay_basis_id :=
748 pay_pel_shd.g_old_rec.pay_basis_id;
749 End If;
750 If (p_rec.costable_type = hr_api.g_varchar2) then
751 p_rec.costable_type :=
752 pay_pel_shd.g_old_rec.costable_type;
753 End If;
754 If (p_rec.link_to_all_payrolls_flag = hr_api.g_varchar2) then
755 p_rec.link_to_all_payrolls_flag :=
756 pay_pel_shd.g_old_rec.link_to_all_payrolls_flag;
757 End If;
758 If (p_rec.multiply_value_flag = hr_api.g_varchar2) then
759 p_rec.multiply_value_flag :=
760 pay_pel_shd.g_old_rec.multiply_value_flag;
761 End If;
762 If (p_rec.standard_link_flag = hr_api.g_varchar2) then
763 p_rec.standard_link_flag :=
764 pay_pel_shd.g_old_rec.standard_link_flag;
765 End If;
766 If (p_rec.transfer_to_gl_flag = hr_api.g_varchar2) then
767 p_rec.transfer_to_gl_flag :=
768 pay_pel_shd.g_old_rec.transfer_to_gl_flag;
769 End If;
770 If (p_rec.comment_id = hr_api.g_number) then
771 p_rec.comment_id :=
772 pay_pel_shd.g_old_rec.comment_id;
773 End If;
774 If (p_rec.comments = hr_api.g_varchar2) then
775 p_rec.comments :=
776 pay_pel_shd.g_old_rec.comments;
777 End If;
778 If (p_rec.employment_category = hr_api.g_varchar2) then
779 p_rec.employment_category :=
780 pay_pel_shd.g_old_rec.employment_category;
781 End If;
782 If (p_rec.qualifying_age = hr_api.g_number) then
783 p_rec.qualifying_age :=
784 pay_pel_shd.g_old_rec.qualifying_age;
785 End If;
786 If (p_rec.qualifying_length_of_service = hr_api.g_number) then
787 p_rec.qualifying_length_of_service :=
788 pay_pel_shd.g_old_rec.qualifying_length_of_service;
789 End If;
790 If (p_rec.qualifying_units = hr_api.g_varchar2) then
791 p_rec.qualifying_units :=
792 pay_pel_shd.g_old_rec.qualifying_units;
793 End If;
794 If (p_rec.attribute_category = hr_api.g_varchar2) then
795 p_rec.attribute_category :=
796 pay_pel_shd.g_old_rec.attribute_category;
797 End If;
798 If (p_rec.attribute1 = hr_api.g_varchar2) then
799 p_rec.attribute1 :=
800 pay_pel_shd.g_old_rec.attribute1;
801 End If;
802 If (p_rec.attribute2 = hr_api.g_varchar2) then
803 p_rec.attribute2 :=
804 pay_pel_shd.g_old_rec.attribute2;
805 End If;
806 If (p_rec.attribute3 = hr_api.g_varchar2) then
807 p_rec.attribute3 :=
808 pay_pel_shd.g_old_rec.attribute3;
809 End If;
810 If (p_rec.attribute4 = hr_api.g_varchar2) then
811 p_rec.attribute4 :=
812 pay_pel_shd.g_old_rec.attribute4;
813 End If;
814 If (p_rec.attribute5 = hr_api.g_varchar2) then
815 p_rec.attribute5 :=
816 pay_pel_shd.g_old_rec.attribute5;
817 End If;
818 If (p_rec.attribute6 = hr_api.g_varchar2) then
819 p_rec.attribute6 :=
820 pay_pel_shd.g_old_rec.attribute6;
821 End If;
822 If (p_rec.attribute7 = hr_api.g_varchar2) then
823 p_rec.attribute7 :=
824 pay_pel_shd.g_old_rec.attribute7;
825 End If;
826 If (p_rec.attribute8 = hr_api.g_varchar2) then
827 p_rec.attribute8 :=
828 pay_pel_shd.g_old_rec.attribute8;
829 End If;
830 If (p_rec.attribute9 = hr_api.g_varchar2) then
831 p_rec.attribute9 :=
832 pay_pel_shd.g_old_rec.attribute9;
833 End If;
834 If (p_rec.attribute10 = hr_api.g_varchar2) then
835 p_rec.attribute10 :=
836 pay_pel_shd.g_old_rec.attribute10;
837 End If;
838 If (p_rec.attribute11 = hr_api.g_varchar2) then
839 p_rec.attribute11 :=
840 pay_pel_shd.g_old_rec.attribute11;
841 End If;
842 If (p_rec.attribute12 = hr_api.g_varchar2) then
843 p_rec.attribute12 :=
844 pay_pel_shd.g_old_rec.attribute12;
845 End If;
846 If (p_rec.attribute13 = hr_api.g_varchar2) then
847 p_rec.attribute13 :=
848 pay_pel_shd.g_old_rec.attribute13;
849 End If;
850 If (p_rec.attribute14 = hr_api.g_varchar2) then
851 p_rec.attribute14 :=
852 pay_pel_shd.g_old_rec.attribute14;
853 End If;
854 If (p_rec.attribute15 = hr_api.g_varchar2) then
855 p_rec.attribute15 :=
856 pay_pel_shd.g_old_rec.attribute15;
857 End If;
858 If (p_rec.attribute16 = hr_api.g_varchar2) then
859 p_rec.attribute16 :=
860 pay_pel_shd.g_old_rec.attribute16;
861 End If;
862 If (p_rec.attribute17 = hr_api.g_varchar2) then
863 p_rec.attribute17 :=
864 pay_pel_shd.g_old_rec.attribute17;
865 End If;
866 If (p_rec.attribute18 = hr_api.g_varchar2) then
867 p_rec.attribute18 :=
868 pay_pel_shd.g_old_rec.attribute18;
869 End If;
870 If (p_rec.attribute19 = hr_api.g_varchar2) then
871 p_rec.attribute19 :=
872 pay_pel_shd.g_old_rec.attribute19;
873 End If;
874 If (p_rec.attribute20 = hr_api.g_varchar2) then
875 p_rec.attribute20 :=
876 pay_pel_shd.g_old_rec.attribute20;
877 End If;
878 --
879 End convert_defs;
880 --
881 -- ----------------------------------------------------------------------------
882 -- |---------------------------------< upd >----------------------------------|
883 -- ----------------------------------------------------------------------------
884 Procedure upd
885 (p_effective_date in date
886 ,p_datetrack_mode in varchar2
887 ,p_rec in out nocopy pay_pel_shd.g_rec_type
888 ) is
889 --
890 l_proc varchar2(72) := g_package||'upd';
891 l_validation_start_date date;
892 l_validation_end_date date;
893 --
894 Begin
895 hr_utility.set_location('Entering:'||l_proc, 5);
896
897 p_rec.link_to_all_payrolls_flag := hr_api.g_varchar2;
898 p_rec.payroll_id := hr_api.g_number;
899 p_rec.job_id := hr_api.g_number;
900 p_rec.position_id := hr_api.g_number;
901 p_rec.people_group_id := hr_api.g_number;
902 p_rec.organization_id := hr_api.g_number;
903 p_rec.location_id := hr_api.g_number;
904 p_rec.grade_id := hr_api.g_number;
905 p_rec.pay_basis_id := hr_api.g_number;
906 --
907 -- Ensure that the DateTrack update mode is valid
908 --
909 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
910 --
911 -- We must lock the row which we need to update.
912 --
913 pay_pel_shd.lck
914 (p_effective_date => p_effective_date
915 ,p_datetrack_mode => p_datetrack_mode
916 ,p_element_link_id => p_rec.element_link_id
917 ,p_object_version_number => p_rec.object_version_number
918 ,p_validation_start_date => l_validation_start_date
919 ,p_validation_end_date => l_validation_end_date
920 ,p_enforce_foreign_locking => true
921 );
922 --
923 -- 1. During an update system defaults are used to determine if
924 -- arguments have been defaulted or not. We must therefore
925 -- derive the full record structure values to be updated.
926 --
927 -- 2. Call the supporting update validate operations.
928 --
929 pay_pel_upd.convert_defs(p_rec);
930 --
931 pay_pel_bus.update_validate
932 (p_rec => p_rec
933 ,p_effective_date => p_effective_date
934 ,p_datetrack_mode => p_datetrack_mode
935 ,p_validation_start_date => l_validation_start_date
936 ,p_validation_end_date => l_validation_end_date
937 );
938 --
939 -- Call to raise any errors on multi-message list
940 hr_multi_message.end_validation_set;
941 --
942 -- Call the supporting pre-update operation
943 --
944 pre_update
945 (p_rec => p_rec
946 ,p_effective_date => p_effective_date
947 ,p_datetrack_mode => p_datetrack_mode
948 ,p_validation_start_date => l_validation_start_date
949 ,p_validation_end_date => l_validation_end_date
950 );
951 --
952 -- Update the row.
953 --
954 update_dml
955 (p_rec => p_rec
956 ,p_effective_date => p_effective_date
957 ,p_datetrack_mode => p_datetrack_mode
958 ,p_validation_start_date => l_validation_start_date
959 ,p_validation_end_date => l_validation_end_date
960 );
961 --
962 -- Call the supporting post-update operation
963 --
964 post_update
965 (p_rec => p_rec
966 ,p_effective_date => p_effective_date
967 ,p_datetrack_mode => p_datetrack_mode
968 ,p_validation_start_date => l_validation_start_date
969 ,p_validation_end_date => l_validation_end_date
970 );
971 --
972 -- Call to raise any errors on multi-message list
973 hr_multi_message.end_validation_set;
974 End upd;
975 --
976 -- ----------------------------------------------------------------------------
977 -- |------------------------------< upd >-------------------------------------|
978 -- ----------------------------------------------------------------------------
979 Procedure upd
980 (p_effective_date in date default hr_api.g_date
981 ,p_element_type_id in number default hr_api.g_number
982 ,p_business_group_id in number default hr_api.g_number
983 ,p_costable_type in varchar2 default hr_api.g_varchar2
984 ,p_multiply_value_flag in varchar2 default hr_api.g_varchar2
985 ,p_standard_link_flag in varchar2 default hr_api.g_varchar2
986 ,p_transfer_to_gl_flag in varchar2 default hr_api.g_varchar2
987 ,p_cost_allocation_keyflex_id in number default hr_api.g_number
988 ,p_balancing_keyflex_id in number default hr_api.g_number
989 ,p_element_set_id in number default hr_api.g_number
990 ,p_comments in varchar2 default hr_api.g_varchar2
991 ,p_employment_category in varchar2 default hr_api.g_varchar2
992 ,p_qualifying_age in varchar2 default hr_api.g_varchar2
993 ,p_qualifying_length_of_service in varchar2 default hr_api.g_varchar2
994 ,p_qualifying_units in varchar2 default hr_api.g_varchar2
995 ,p_attribute_category in varchar2 default hr_api.g_varchar2
996 ,p_attribute1 in varchar2 default hr_api.g_varchar2
997 ,p_attribute2 in varchar2 default hr_api.g_varchar2
998 ,p_attribute3 in varchar2 default hr_api.g_varchar2
999 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1000 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1001 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1002 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1003 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1004 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1005 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1006 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1007 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1008 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1009 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1010 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1011 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1012 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1013 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1014 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1015 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1016 ,p_element_link_id in number default hr_api.g_number
1017 ,p_datetrack_mode in varchar2 default hr_api.g_varchar2
1018 ,p_object_version_number in out nocopy varchar2
1019 ,p_effective_start_date out nocopy date
1020 ,p_effective_end_date out nocopy date
1021 ) is
1022 --
1023 l_rec pay_pel_shd.g_rec_type;
1024 l_proc varchar2(72) := g_package||'upd';
1025 --
1026 Begin
1027 hr_utility.set_location('Entering:'||l_proc, 5);
1028 --
1029 -- Call conversion function to turn arguments into the
1030 -- l_rec structure.
1031 --
1032 l_rec :=
1033 pay_pel_shd.convert_args
1034 (p_element_link_id
1035 ,null
1036 ,null
1037 ,null
1038 ,null
1039 ,null
1040 ,null
1041 ,p_cost_allocation_keyflex_id
1042 ,null
1043 ,p_element_type_id
1044 ,null
1045 ,null
1046 ,p_balancing_keyflex_id
1047 ,p_business_group_id
1048 ,p_element_set_id
1049 ,null
1050 ,p_costable_type
1051 ,null
1052 ,p_multiply_value_flag
1053 ,p_standard_link_flag
1054 ,p_transfer_to_gl_flag
1055 ,hr_api.g_number
1056 ,p_comments
1057 ,p_employment_category
1058 ,p_qualifying_age
1059 ,p_qualifying_length_of_service
1060 ,p_qualifying_units
1061 ,p_attribute_category
1062 ,p_attribute1
1063 ,p_attribute2
1064 ,p_attribute3
1065 ,p_attribute4
1066 ,p_attribute5
1067 ,p_attribute6
1068 ,p_attribute7
1069 ,p_attribute8
1070 ,p_attribute9
1071 ,p_attribute10
1072 ,p_attribute11
1073 ,p_attribute12
1074 ,p_attribute13
1075 ,p_attribute14
1076 ,p_attribute15
1077 ,p_attribute16
1078 ,p_attribute17
1079 ,p_attribute18
1080 ,p_attribute19
1081 ,p_attribute20
1082 ,p_object_version_number
1083 );
1084 --
1085 -- Having converted the arguments into the
1086 -- plsql record structure we call the corresponding record
1087 -- business process.
1088 --
1089 pay_pel_upd.upd
1090 (p_effective_date
1091 ,p_datetrack_mode
1092 ,l_rec
1093 );
1094 --
1095 -- Set the out parameters
1096 --
1097 p_object_version_number := l_rec.object_version_number;
1098 p_effective_start_date := l_rec.effective_start_date;
1099 p_effective_end_date := l_rec.effective_end_date;
1100 --
1101 hr_utility.set_location(' Leaving:'||l_proc, 10);
1102 End upd;
1103 --
1104 end pay_pel_upd;