1 package body hr_input_values as
2 /* $Header: pyinpval.pkb 115.7 2003/03/04 11:56:32 alogue ship $ */
3 --
4
5 /*
6 NAME
7 chk_input_value
8 DESCRIPTION
9 Checks attributes of inserted and update input values for concurrence
10 with business rules.
11 */
12 --
13 PROCEDURE chk_input_value(p_element_type_id in number,
14 p_legislation_code in varchar2,
15 p_val_start_date in date,
16 p_val_end_date in date,
17 p_insert_update_flag in varchar2,
18 p_input_value_id in number,
19 p_rowid in varchar2,
20 p_recurring_flag in varchar2,
21 p_mandatory_flag in varchar2,
22 p_hot_default_flag in varchar2,
23 p_standard_link_flag in varchar2,
24 p_classification_type in varchar2,
25 p_name in varchar2,
26 p_uom in varchar2,
27 p_min_value in varchar2,
28 p_max_value in varchar2,
29 p_default_value in varchar2,
30 p_lookup_type in varchar2,
31 p_formula_id in number,
32 p_generate_db_items_flag in varchar2,
33 p_warning_or_error in varchar2) is
34 --
35 v_validation_check varchar2(1);
36 v_num_input_values number;
37 l_pay_value_name varchar2(80);
38 --
39 begin
40 -- get pay value name
41 l_pay_value_name := hr_input_values.get_pay_value_name
42 (p_legislation_code);
43 --
44 -- payments type 'Pay Values' must have uom of money
45 --
46 if p_name = l_pay_value_name and
47 p_classification_type = 'N' and
48 p_uom <> 'M' then
49 --
50 hr_utility.set_message(801,'');
51 hr_utility.raise_error;
52 --
53 end if;
54
55 if p_insert_update_flag = 'INSERT' then
56 -- Make sure that a maximum of 6 input values can be created
57 begin
58 --
59 select count(distinct iv.input_value_id)
60 into v_num_input_values
61 from pay_input_values_f iv
62 where iv.element_type_id = p_element_type_id
63 and p_val_start_date between
64 iv.effective_start_date and iv.effective_end_date;
65 --
66 exception
67 when NO_DATA_FOUND then NULL;
68 end;
69 if v_num_input_values >= 6 then
70 --
71 hr_utility.set_message(801,'PAY_6167_INPVAL_ONLY_6');
72 hr_utility.raise_error;
73 --
74 end if;
75 --
76 v_validation_check := 'Y';
77 --
78 -- no entries can be in existence
79 -- during the validation period
80 -- for the other input values.
81 -- This check only needs to be done on insert not on updatE
82 --
83 begin
84 --
85 select 'N'
86 into v_validation_check
87 from sys.dual
88 where exists
89 (select 1
90 from pay_element_links_f el,
91 pay_element_entries_f ee
92 where p_element_type_id = el.element_type_id
93 and el.element_link_id = ee.element_link_id
94 and ee.effective_end_date >= p_val_start_date
95 and ee.effective_start_date <= p_val_end_date);
96 --
97 exception
98 when NO_DATA_FOUND then NULL;
99 end;
100 --
101 if v_validation_check = 'N' then
102 --
103 hr_utility.set_message(801,'PAY_6197_INPVAL_NO_ENTRY');
104 hr_utility.raise_error;
105 --
106 end if;
107 --
108 end if;-- In INSERT mode
109 --
110 -- Make sure that the input value name is unique within the element
111 -- This will ensure also that only one PAY_VALUE can be used.
112 begin
113 select 'N'
114 into v_validation_check
115 from sys.dual
116 where exists
117 (select 1
118 from pay_input_values_f_tl ipv_tl,
119 pay_input_values_f ipv
120 where ipv_tl.input_value_id = ipv.input_value_id
121 and userenv('LANG') = ipv_tl.language
122 and ipv.element_type_id = p_element_type_id
123 and ipv.input_value_id <> p_input_value_id
124 and upper(p_name) = upper(ipv_tl.name));
125 --
126 exception
127 when NO_DATA_FOUND then NULL;
128 end;
129
130 if v_validation_check = 'N' then
131 --
132 hr_utility.set_message(801,'PAY_6168_INPVAL_DUP_NAME');
133 hr_utility.raise_error;
134 --
135 end if;
136 --
137 -- Hot defaulted values must be mandatory.
138 --
139 if (p_hot_default_flag = 'Y' and p_mandatory_flag = 'N') then
140 --
141 hr_utility.set_message(801,'PAY_6609_ELEMENT_HOT_DEF_MAN');
142 hr_utility.raise_error;
143 --
144 end if;
145 --
146 -- Hot defaulted values must have default, max and min less than 59
147 -- characters. This is to allow for the inclusion of quotes around the
148 -- values when they are displayed at the lower level
149 if (p_hot_default_flag = 'Y') and
150 ((length(p_default_value) > 58) or
151 (length(p_min_value) > 58) or
152 (length(p_max_value) > 58)) then
153 --
154 hr_utility.set_message(801,'PAY_6616_INPVAL_HOT_LESS_58');
155 hr_utility.raise_error;
156 --
157 end if;
158 --
159 -- If the element is nonrecurring then do not allow any non-numeric input
160 -- values to have create db items set to 'Y' ie. cannot specify Date or
161 -- Character. This is so that they can be summed on the entity horizon
162 if ((p_recurring_flag = 'N' and
163 p_generate_db_items_flag = 'Y') and
164 ((p_uom = 'C') or
165 (p_uom like 'D%'))) then
166 --
167 hr_utility.set_message(801,'PAY_6169_INPVAL_ONLY_NUM');
168 hr_utility.raise_error;
169 --
170 end if;
171 --
172 -- Makes sure that the validation specified for the input value is correct
173 -- ie. it can either be formula and default OR
174 -- lookup type and default OR default and
175 -- min / max
176 if p_formula_id is not NULL then
177 --
178 if (p_lookup_type is not NULL or
179 p_min_value is not NULL or
180 p_max_value is not NULL or
181 p_warning_or_error is NULL) then
182 --
183 hr_utility.set_message(801,'PAY_6905_INPVAL_FORMULA_VAL');
184 hr_utility.raise_error;
185 --
186 end if;
187 --
188 elsif p_lookup_type is not NULL then
189 --
190 if (p_min_value is not NULL or
191 p_max_value is not NULL or
192 p_formula_id is not NULL or
193 p_warning_or_error is not NULL) then
194 --
195 hr_utility.set_message(801,'PAY_6906_INPVAL_LOOKUP_VAL');
196 hr_utility.raise_error;
197 --
198 end if;
199 --
200 elsif (p_min_value is not NULL or p_max_value is not NULL) then
201 --
202 if (p_lookup_type is not NULL or
203 p_formula_id is not NULL) then
204 --
205 hr_utility.set_message(801,'PAY_6907_INPVAL_MIN_MAX_VAL');
206 hr_utility.raise_error;
207 --
208 elsif (p_warning_or_error is null) then
209 --
210 hr_utility.set_message(801,'PAY_6907_INPVAL_MIN_MAX_VAL');
211 hr_utility.raise_error;
212 --
213 end if;
214
215 end if;
216 --
217 if (p_warning_or_error is not null and
218 p_min_value is null and
219 p_max_value is null and
220 p_formula_id is null) then
221 --
222 hr_utility.set_message(801,'PAY_6908_INPVAL_ERROR_VAL');
223 hr_utility.raise_error;
224 --
225 end if;
226 --
227 -- Mkae sure that when lookup validation is being used that the default when
228 -- specified is valid for the lookup type
229 --
230 if (p_lookup_type is not NULL and p_default_value is not NULL) then
231 --
232 begin
233 --
234 v_validation_check := 'Y';
235 --
236 select 'N'
237 into v_validation_check
238 from sys.dual
239 where not exists(select 1
240 from hr_lookups
241 where lookup_type = p_lookup_type
242 and lookup_code = p_default_value);
243 --
244 exception
245 when NO_DATA_FOUND then NULL;
246 end;
247 --
248 if v_validation_check = 'N' then
249 --
250 hr_utility.set_message(801,'PAY_6171_INPVAL_NO_LOOKUP');
251 hr_utility.raise_error;
252 --
253 end if;
254 --
255 end if;
256 --
257 -- No new input values can be created if there are any run results existing
258 -- for this element
259 begin
260 --
261 select 'N'
262 into v_validation_check
263 from sys.dual
264 where exists
265 (select 1
266 from pay_run_results rr
267 where rr.element_type_id = p_element_type_id);
268 --
269 exception
270 when NO_DATA_FOUND then null;
271 end;
272 --
273 if v_validation_check = 'N' then
274 hr_utility.set_message(801,'PAY_6913_INPVAL_NO_INS_RUN_RES');
275 hr_utility.raise_error;
276 end if;
277
278 end chk_input_value;
279 --
280 /*
281 NAME
282 chk_entry_default
283 DESCRIPTION
284 This function will check if all entries for an element link and an input
285 value have a default value. This is called in situations where we need to
286 check for defaults because of hot defaulting. This function will return TRUE
287 if any nulls are found in the selected entries. It will also return TRUE if
288 there are no entries at all for this link and input value. This allows for
289 the fact that entries may be created subsequently with null values.
290 */
291 --
292 FUNCTION chk_entry_default(f_input_value_id in number,
293 f_element_link_id in number,
294 f_val_start_date in date,
295 f_val_end_date in date) return BOOLEAN is
296 --
297 null_entries_found varchar2(1) := 'N';
298 --
299 begin
300 --
301
302 -- First check to see if there are any entries
303 --
304 begin
305 --
306 select 'Y'
307 into null_entries_found
308 from sys.dual
309 where not exists(
310 select 1
311 from pay_element_entries_f ee,
312 pay_element_entry_values_f eev
313 where f_element_link_id = ee.element_link_id
314 and ee.element_entry_id = eev.element_entry_id
315 and eev.input_value_id = f_input_value_id
316 and eev.effective_start_date <= f_val_end_date
317 and eev.effective_end_date >= f_val_start_date);
318 --
319 exception
320 when NO_DATA_FOUND then null;
321 end;
322 --
323 if (null_entries_found = 'N') then
324 --
325 begin
326 --
327 select 'Y'
328 into null_entries_found
329 from sys.dual
330 where exists(
331 select 1
332 from pay_element_entries_f ee,
333 pay_element_entry_values_f eev
334 where f_element_link_id = ee.element_link_id
335 and ee.element_entry_id = eev.element_entry_id
336 and eev.input_value_id = f_input_value_id
337 and eev.effective_start_date <= f_val_end_date
338 and eev.effective_end_date >= f_val_start_date
339 and eev.screen_entry_value is null);
340 --
341 exception
342 when NO_DATA_FOUND then null;
343 end;
344 --
345 end if;
346
347 return null_entries_found = 'Y';
348 --
349 end chk_entry_default;
350 --
351 --
352 /*
353 NAME
354 chk_link_hot_defaults
355 DESCRIPTION
356 This procedure checks whether all link_input_values and entry values have
357 defaults if a hot defaulted default value is made null. It calls the function chk_entry_default
358 */
359 --
360 PROCEDURE chk_link_hot_defaults(p_update_mode in varchar2,
361 p_val_start_date in date,
362 p_val_end_date in date,
363 p_input_value_id in number,
364 p_element_link_id in number,
365 p_default_delete in varchar2,
366 p_min_delete in varchar2,
367 p_max_delete in varchar2) is
368 --
369 --
370 null_links_found varchar2(1);
371 l_min_value_missing varchar2(1) := 'N';
372 l_max_value_missing varchar2(1) := 'N';
373 l_default_value_missing varchar2(1) := 'N';
374 --
375 begin
376 --
377 -- First check that value exists at type level. If this is not the case
378 -- then we want to return an error for min and max values and continue
379 -- with default to check for all defaults at entry level.
380 --
381 if (p_min_delete = 'Y') then
382 --
383 begin
384 --
385 select 'Y'
386 into l_min_value_missing
387 from sys.dual
388 where exists
389 (select 1
390 from pay_input_values_f iv
391 where p_input_value_id = iv.input_value_id
392 and iv.min_value is null
393 and iv.effective_start_date <= p_val_end_date
394 and iv.effective_end_date >= p_val_start_date);
395 exception
396 when NO_DATA_FOUND then null;
397 end;
398 --
399 if l_min_value_missing = 'Y' then
400 hr_utility.set_message(801,'PAY_6192_INPVAL_NO_MIN_DEFS');
401 hr_utility.raise_error;
402 end if;
403 end if;
404 --
405 if (p_max_delete = 'Y') then
406 --
407 begin
408 --
409 select 'Y'
410 into l_max_value_missing
411 from sys.dual
412 where exists
413 (select 1
414 from pay_input_values_f iv
415 where p_input_value_id = iv.input_value_id
416 and iv.max_value is null
417 and iv.effective_start_date <= p_val_end_date
418 and iv.effective_end_date >= p_val_start_date);
419 exception
420 when NO_DATA_FOUND then null;
421 end;
422 --
423 if l_max_value_missing = 'Y' then
424 hr_utility.set_message(801,'PAY_6193_INPVAL_NO_MAX_DEFS');
425 hr_utility.raise_error;
426 end if;
427 --
428 end if;
429 --
430 -- If the default value is being deleted we need to first check if there
431 -- is a default at element type level. If there is not then we need to
432 -- check if all the element entries have defaults available.
433 --
434 if (p_default_delete = 'Y') then
435 --
436 begin
437 --
438 select 'Y'
439 into l_default_value_missing
440 from sys.dual
441 where exists
442 (select 1
443 from pay_input_values_f iv
444 where p_input_value_id = iv.input_value_id
445 and iv.default_value is null
446 and iv.effective_start_date <= p_val_end_date
447 and iv.effective_end_date >= p_val_start_date);
448 exception
449 when NO_DATA_FOUND then null;
450 end;
451 --
452 if l_default_value_missing = 'Y' then
453 --
454 if hr_input_values.chk_entry_default
455 (p_input_value_id,
456 p_element_link_id,
457 p_val_start_date,
458 p_val_end_date) then
459 hr_utility.set_message(801,'PAY_6191_INPVAL_NO_ENTRY_DEFS');
460 hr_utility.raise_error;
461 end if;
462 end if;
463 --
464 end if;
465 --
466 end chk_link_hot_defaults;
467 --
468 /*
469 NAME
470 chk_hot_defaults
471 DESCRIPTION
472 This procedure checks whether all link_input_values and entry values have
473 defaults if a hot defaulted default value is made null. It calls the function chk_entry_default
474 */
475 --
476 PROCEDURE chk_hot_defaults(p_update_mode in varchar2,
477 p_val_start_date in date,
478 p_val_end_date in date,
479 p_input_value_id in number,
480 p_element_type_id in number,
481 p_default_deleted in varchar2,
482 p_min_deleted in varchar2,
483 p_max_deleted in varchar2) is
484 --
485 null_links_found varchar2(1);
486 --
487 CURSOR c_chk_link_default(p_val_start_date date,
488 p_val_end_date date,
489 p_input_value_id number) is
490 select element_link_id,
491 effective_start_date,
492 effective_end_date
493 from pay_link_input_values_f
494 where input_value_id = p_input_value_id
495 and default_value is null
496 and effective_end_date >= p_val_start_date
497 and effective_start_date <= p_val_end_date;
498 --
499 begin
500 --
501 --
502 -- Check if this input value has a null default value
503 if p_default_deleted = 'Y' then
504 --
505 -- Go though all the links checking they have defaults.
506 -- If any don't then check the element entry value exists.
507 -- the function 'chk_entry_default' will return 'TRUE' if any entries
508 -- are found without values entered for them.
509 --
510 for chk_default in c_chk_link_default( p_val_start_date,
511 p_val_end_date,
512 p_input_value_id) loop
513 if hr_input_values.chk_entry_default(p_input_value_id,
514 chk_default.element_link_id,
515 chk_default.effective_start_date,
516 chk_default.effective_end_date) then
517 hr_utility.set_message(801,'PAY_6191_INPVAL_NO_ENTRY_DEFS');
518 hr_utility.raise_error;
519 end if;
520 end loop;
521 end if;
522 --
523 if p_min_deleted = 'Y' then
524 --
525 -- Check that there are no link input values over the validation period
526 -- that have a null minimum default.
527 begin
528 --
529 select 'Y'
530 into null_links_found
531 from pay_link_input_values_f
532 where input_value_id = p_input_value_id
533 and min_value is null
534 and effective_end_date >= p_val_start_date
535 and effective_start_date <= p_val_end_date;
536 --
537 exception
538 when NO_DATA_FOUND then null;
539 end;
540 --
541 if null_links_found = 'Y' then
542 hr_utility.set_message(801,'PAY_6192_INPVAL_NO_MIN_DEFS');
543 hr_utility.raise_error;
544 end if;
545 --
546 end if;
547 --
548 if p_max_deleted = 'Y' then
549 --
550 -- Check that there are no link input values over the validation period
551 -- that have a null maximum default.
552 begin
553 --
554 select 'Y'
555 into null_links_found
556 from pay_link_input_values_f
557 where input_value_id = p_input_value_id
558 and max_value is null
559 and effective_end_date >= p_val_start_date
560 and effective_start_date <= p_val_end_date;
561 --
562 exception
563 when NO_DATA_FOUND then null;
564 end;
565 --
566 if null_links_found = 'Y' then
567 hr_utility.set_message(801,'PAY_6193_INPVAL_NO_MAX_DEFS');
568 hr_utility.raise_error;
569 end if;
570 --
571 end if; -- of 'if default is null' statement
572 --
573 end chk_hot_defaults;
574 --
575 --
576 /*
577 NAME
578 chk_del_input_value
579 DESCRIPTION
580 Checks whether an input value can be deleted. This consists of checking
581 if various child records exist for this input value.
582 */
583 --
584 PROCEDURE chk_del_input_values(p_delete_mode in varchar2,
585 p_val_start_date in date,
586 p_val_end_date in date,
587 p_input_value_id in number) is
588 --
589 v_links_exist_flag varchar2(1) := 'N';
590 v_db_items_exist_flag varchar2(1) := 'N';
591 v_results_exist_flag varchar2(1) := 'N';
592 v_run_results_exist_flag varchar2(1) := 'N';
593 v_entries_exist_flag varchar2(1) := 'N';
594 l_pay_value_name varchar2(80);
595 --
596 begin
597 --
598 -- Delete future change not allowed for input values
599 if p_delete_mode = 'FUTURE_CHANGE' then
600 hr_utility.set_message(801,'PAY_6209_ELEMENT_NO_FC_DEL');
601 hr_utility.raise_error;
602 --
603 -- the following checks only need to be made for date effective delete or
604 -- ZAP delete. Delete next change requires different processing
605 elsif p_delete_mode = 'ZAP' then
606 --
607 begin
608 -- if 'ZAP' then
609 -- test to see if there are any element links during validation period.
610 -- and input value is PAY VALUE and the link is distributed
611 --
612 l_pay_value_name := hr_input_values.get_pay_value_name(null);
613 --
614 select 'Y'
615 into v_links_exist_flag
616 from sys.dual
617 where exists
618 (select 1
619 from pay_element_links_f el,
620 pay_input_values_f_tl ip_tl,
621 pay_input_values_f ip
622 where ip_tl.input_value_id = ip.input_value_id
623 and ip.input_value_id = p_input_value_id
624 and userenv('LANG') = ip_tl.language
625 and ip_tl.name = l_pay_value_name
626 and el.element_type_id = ip.element_type_id
627 and el.costable_type = 'D'
628 and el.effective_start_date <= p_val_end_date
629 and el.effective_end_date >= p_val_start_date);
630 --
631 exception
632 when NO_DATA_FOUND then NULL;
633 end;
634 --
635 if v_links_exist_flag = 'Y' then
636 hr_utility.set_message(801,'PAY_6210_INPVAL_NO_LINKS_DEL');
637 hr_utility.raise_error;
638 end if;
639 --
640 --
641 end if;
642 --
643 --
644 if p_delete_mode = 'DELETE' or p_delete_mode = 'ZAP' then
645 --
646 begin
647 -- Test to see if there are any element entry values during validation period
648 --
649 select 'Y'
650 into v_entries_exist_flag
651 from sys.dual
652 where exists
653 (select 1
654 from pay_element_entry_values_f
655 where input_value_id = p_input_value_id
656 and effective_start_date <= p_val_end_date
657 and effective_end_date >= p_val_start_date);
658 --
659 exception
660 when NO_DATA_FOUND then NULL;
661 end;
662 --
663 if v_entries_exist_flag = 'Y' then
664 hr_utility.set_message(801,'PAY_6211_INPVAL_NO_DEL_ENTRY');
665 hr_utility.raise_error;
666 end if;
667 --
668 begin
669 -- Test to see if any formula result rules target this input value during
670 -- the validation period.
671 --
672 select 'Y'
673 into v_results_exist_flag
674 from sys.dual
675 where exists
676 (select 1
677 from pay_formula_result_rules_f
678 where input_value_id = p_input_value_id
679 and effective_start_date <= p_val_end_date
680 and effective_end_date >= p_val_start_date);
681 --
682 exception
683 when NO_DATA_FOUND then NULL;
684 end;
685 --
686 if v_results_exist_flag = 'Y' then
687 hr_utility.set_message(801,'PAY_6213_INPVAL_NO_FRR_DEL');
688 hr_utility.raise_error;
689 end if;
690 --
691 begin
692 -- Test to see if any run_result_values are in existence during the validation
693 -- period. The effective date of run result values can be found from the
694 -- payroll_actions table
695 --
696 select 'Y'
697 into v_run_results_exist_flag
698 from sys.dual
699 where exists
700 (select /*+ INDEX(rr PAY_RUN_RESULTS_PK) */ 1
701 from pay_run_result_values rrv,
702 pay_run_results rr,
703 pay_assignment_actions aa,
704 pay_payroll_actions pa
705 where p_input_value_id = rrv.input_value_id
706 and rrv.run_result_id = rr.run_result_id
707 and aa.assignment_action_id = rr.assignment_action_id
708 and aa.payroll_action_id = pa.payroll_action_id
709 and pa.effective_date between
710 p_val_start_date and p_val_end_date);
711 --
712 exception
713 when NO_DATA_FOUND then NULL;
714 end;
715 --
716 if v_run_results_exist_flag = 'Y' then
717 hr_utility.set_message(801,'PAY_6212_INPVAL_NO_RR_DEL');
718 hr_utility.raise_error;
719 end if;
720 --
721 begin
722 -- You cannot delete an input value if any absence_attendance types access
723 -- This input value.
724 --
725 select 'Y'
726 into v_results_exist_flag
727 from sys.dual
728 where exists
729 (select 1
730 from per_absence_attendance_types
731 where input_value_id = p_input_value_id
732 and date_effective between
733 p_val_start_date and p_val_end_date);
734 --
735 exception
736 when NO_DATA_FOUND then NULL;
737 end;
738 --
739 if v_results_exist_flag = 'Y' then
740 hr_utility.set_message(801,'PAY_6214_INPVAL_NO_ABS_DEL');
741 hr_utility.raise_error;
742 end if;
743 --
744 begin
745 -- You cannot delete an input value if any absence_attendance types access
746 -- This input value.
747 --
748 select 'Y'
749 into v_results_exist_flag
750 from sys.dual
751 where exists
752 (select 1
753 from pay_backpay_rules
754 where input_value_id = p_input_value_id);
755 --
756 exception
757 when NO_DATA_FOUND then NULL;
758 end;
759 --
760 if v_results_exist_flag = 'Y' then
761 hr_utility.set_message(801,'PAY_6215_INPVAL_NO_DEL_BP');
762 hr_utility.raise_error;
763 end if;
764 --
765 end if; -- of check delete mode condition.
766 --
767 end chk_del_input_values;
768 --
769 --
770 /*
771 NAME
772 chk_field_update
773 DESCRIPTION
774 A general function for input values that forces correction for a particular
775 field over the lifetime of a complete input value. It should be called after
776 the postfield datetrack trigger.
777 */
778 FUNCTION chk_field_update(
779 p_input_value_id in number,
780 p_val_start_date in date,
781 p_val_end_date in date,
782 p_update_mode in varchar2) return BOOLEAN is
783 --
784 l_validation_check varchar2(1) := 'N';
785 --
786 begin
787 --
788 if (p_update_mode <> 'CORRECTION') then
789 --
790 return FALSE;
791 end if;
792 --
793 begin
794 --
795 select 'Y'
796 into l_validation_check
797 from sys.dual
798 where p_val_end_date =
799 (select max(iv1.effective_end_date)
800 from pay_input_values iv1
801 where iv1.input_value_id = p_input_value_id)
802 and p_val_start_date =
803 (select min(iv2.effective_start_date)
804 from pay_input_values iv2
805 where iv2.input_value_id = p_input_value_id);
806 --
807 exception
808 when NO_DATA_FOUND then null;
809 end;
810 --
811 return l_validation_check = 'Y';
812 --
813 end chk_field_update;
814 --
815 /*
816 NAME
817 get_pay_value_name
818 DESCRIPTION
819 gets pay value from translation table.
820 */
821 --
822 FUNCTION get_pay_value_name(p_legislation_code varchar2)
823 return varchar2 is
824 l_pay_value_name varchar2(80);
825 begin
826 --
827 begin
828
829 select meaning
830 into l_pay_value_name
831 from hr_lookups
832 where lookup_type = 'NAME_TRANSLATIONS'
833 and lookup_code = 'PAY VALUE';
834 --
835 exception
836 when NO_DATA_FOUND then
837 hr_utility.set_message(801,'PAY_6162_ELEMENT_NO_NAME_TRANS');
838 hr_utility.raise_error;
839 end;
840 --
841 return(l_pay_value_name);
842 --
843 end get_pay_value_name;
844 /*
845 NAME
846 chk_upd_input_value
847 DESCRIPTION
848 Checks whether an input value can be updated. Some values can be updated
849 under any circumstances and others can only be updated if certain conditions
850 exist. For instance if there are no links in existence. This procedure calls
851 chk_hot_defaults.
852 */
853 --
854 PROCEDURE chk_upd_input_values(p_update_mode in varchar2,
855 p_val_start_date in date,
856 p_val_end_date in date,
857 p_classification_type in varchar2,
858 p_old_name in varchar2,
859 p_name in varchar2,
860 p_input_value_id in number,
861 p_element_type_id in number,
862 p_old_uom in varchar2,
863 p_uom in varchar2,
864 p_old_db_items_flag in varchar2,
865 p_db_items_flag in varchar2,
866 p_old_default_value in varchar2,
867 p_default_value in varchar2,
868 p_old_min_value in varchar2,
869 p_min_value in varchar2,
870 p_old_max_value in varchar2,
871 p_max_value in varchar2,
872 p_old_error_flag in varchar2,
873 p_error_flag in varchar2,
874 p_old_mandatory_flag in varchar2,
875 p_mandatory_flag in varchar2,
876 p_old_formula_id in number,
877 p_formula_id in number,
878 p_old_lookup_type in varchar2,
879 p_lookup_type in varchar2,
880 p_business_group_id in number,
881 p_legislation_code in varchar2) is
882 --
883 local_warning exception;
884 l_validation_check varchar2(1) := 'N';
885 l_link_inputs_exist varchar2(1) := 'N';
886 v_entries_exist_flag varchar2(1) := 'N';
887 l_pay_value_name varchar2(80);
888 l_record_ok varchar2(1) := 'N';
889 --
890 --
891 begin
892 --
893 -- We need to clear the warning flag as this may still be in force from
894 -- a previous update
895 hr_utility.clear_warning;
896 --
897 -- Do checks if the following fields have been updated.
898 if (p_old_uom <> p_uom)
899 or (p_old_db_items_flag <> p_db_items_flag)
900 or (p_old_mandatory_flag <> p_mandatory_flag)
901 or (p_old_name <> p_name)
902 or (p_error_flag = 'E' and p_error_flag <> p_old_error_flag) then
903 --
904 -- Obtain Pay value name from translation table.
905 l_pay_value_name := hr_input_values.get_pay_value_name
906 (p_legislation_code);
907 --
908 -- No date effective change of name, Can only be changed if no formulas
909 -- access it.
910
911 if (p_old_name <> p_name) and
912 ((p_update_mode <> 'CORRECTION') or
913 (p_old_name = l_pay_value_name) or
914 (p_name = l_pay_value_name) or
915 (p_business_group_id is null)) then
916 hr_utility.set_message(801,'PAY_6177_INPVAL_NO_NAME_CHANGE');
917 hr_utility.raise_error;
918 end if;
919 --
920 -- Unit of measure can only be changed if non_payment_flag on
921 -- Classification is 'N' or the change keeps within the Unit of measure
922 -- type. This can be found by comparing the first two letters.
923 if (p_old_uom <> p_uom) and
924 (p_classification_type = 'N') and
925 (substr(p_old_uom, 1, 2) <> substr(p_uom, 1, 2)) then
926 hr_utility.set_message(801,'PAY_6178_INPVAL_NO_UOM_CHANGE');
927 hr_utility.raise_error;
928 end if;
929 --
930 -- The mandatory flag can only ever be changed from mandatory to non mandatory -- otherwise the entries might be invalidated.
931 if ((p_mandatory_flag = 'Y') and (p_old_mandatory_flag = 'N')) then
932 --
933 hr_utility.set_message(801,'PAY_6179_INPVAL_MAND_NO_CHANGE');
934 hr_utility.raise_error;
935 --
936 elsif (p_mandatory_flag <> p_old_mandatory_flag) then
937 -- we must also check to see if the mandatory flag will ever
938 -- become mandatory in the future.
939 begin
940
941 select 'Y'
942 into l_validation_check
943 from sys.dual
944 where exists
945 (select 1
946 from pay_input_values_f iv
947 where iv.input_value_id = p_input_value_id
948 and iv.effective_start_date > p_val_start_date
949 and iv.mandatory_flag = 'Y');
950 --
951 exception
952 when NO_DATA_FOUND then NULL;
953 end;
954 --
955 if l_validation_check = 'Y' then
956 hr_utility.set_message(801,'PAY_6179_INPVAL_MAND_NO_CHANGE');
957 hr_utility.raise_error;
958 end if;
959 --
960 end if;
961 --
962 -- Warning or error flag can only be updated if there are no entries
963 -- in existence. This will only need to be checked if there are any
964 -- links
965 if ((p_error_flag = 'E') and (p_error_flag <> p_old_error_flag)) then
966 --
967 begin
968 --
969 select 'Y'
970 into v_entries_exist_flag
971 from sys.dual
972 where exists
973 (select 1
974 from pay_element_links_f el,
975 pay_element_entries_f ee
976 where p_element_type_id = el.element_type_id
977 and el.element_link_id = ee.element_link_id
978 and el.effective_start_date <= p_val_end_date
979 and el.effective_end_date >= p_val_start_date
980 and ee.effective_start_date <= p_val_end_date
981 and ee.effective_end_date >= p_val_start_date);
982 --
983 exception
984 when NO_DATA_FOUND then NULL;
985 end;
986 --
987 if v_entries_exist_flag = 'Y' then
988 hr_utility.set_message(801,'PAY_6181_INPVAL_ERR_FLAG_UPD');
989 hr_utility.raise_error;
990 end if;
991 end if; -- error flag checks
992 --
993 if chk_field_update(p_input_value_id,
994 p_val_start_date,
995 p_val_end_date,
996 p_update_mode) = FALSE then
997 --
998 if (p_old_name <> p_name) then
999 --
1000 hr_utility.set_message(801,'PAY_6632_INPVAL_NO_NAME_UPD');
1001 hr_utility.raise_error;
1002 --
1003 elsif (p_old_db_items_flag <> p_db_items_flag) then
1004 --
1005 hr_utility.set_message(801,'PAY_6633_INPVAL_NO_DB_UPD');
1006 hr_utility.raise_error;
1007 --
1008 elsif (p_uom <> p_old_uom) then
1009 --
1010 hr_utility.set_message(801,'PAY_6634_INPVAL_NO_UOM_UPD');
1011 hr_utility.raise_error;
1012 --
1013 end if;
1014 end if;
1015 --
1016 end if; -- General Check conditions
1017 --
1018 if (nvl(p_old_default_value, ' ') <> nvl(p_default_value, ' ')) or
1019 (nvl(p_old_min_value, ' ') <> nvl(p_min_value, ' ')) or
1020 (nvl(p_old_max_value, ' ') <> nvl(p_max_value, ' ')) or
1021 (nvl(p_old_error_flag, ' ') <> nvl(p_error_flag, ' ')) or
1022 (nvl(p_old_formula_id, ' ') <> nvl(p_formula_id, ' ')) or
1023 (nvl(p_old_lookup_type, ' ') <> nvl(p_lookup_type, ' ')) then
1024 --
1025 -- we must check for the existence of link input values and issue
1026 -- a warning if there are any.
1027 begin
1028 --
1029 select 'Y'
1030 into l_link_inputs_exist
1031 from sys.dual
1032 where exists
1033 (select 1
1034 from pay_link_input_values_f liv
1035 where liv.input_value_id = p_input_value_id);
1036 --
1037 exception
1038 when NO_DATA_FOUND then NULL;
1039 end;
1040 --
1041 if l_link_inputs_exist = 'Y' then
1042 --
1043 hr_utility.set_message(801, 'PAY_INPVAL_LINK_UPD_WARN');
1044 hr_utility.set_warning;
1045 --
1046 end if;
1047 end if;
1048 --
1049 end chk_upd_input_values;
1050 --
1051 /*
1052 NAME
1053 create_link_input_value
1054 DESCRIPTION
1055 This procedure creates links under two circumstances.
1056 1. When a new link has been created.
1057 2. When a new input value is created and there are already existing links
1058 This behaviour is controlled by the p_insert_type parameter which can take
1059 the values 'INSERT_LINK' or 'INSERT_INPUT_VALUE'.
1060 */
1061 --
1062 PROCEDURE
1063 create_link_input_value(p_insert_type varchar2,
1064 p_element_link_id number,
1065 p_input_value_id number,
1066 p_input_value_name varchar2,
1067 p_costable_type varchar2,
1068 p_validation_start_date date,
1069 p_validation_end_date date,
1070 p_default_value varchar2,
1071 p_max_value varchar2,
1072 p_min_value varchar2,
1073 p_warning_or_error_flag varchar2,
1074 p_hot_default_flag varchar2,
1075 p_legislation_code varchar2,
1076 p_pay_value_name varchar2,
1077 p_element_type_id number) is
1078 --
1079 v_link_input_value_id number;
1080 v_old_input_value_id number := 0;
1081 --
1082 -- This selects all input values for an element type
1083 cursor c_input_value(p_element_type_id number) is
1084 select iv.input_value_id input_value_id
1085 from pay_input_values_f iv
1086 where iv.element_type_id = p_element_type_id
1087 order by iv.input_value_id
1088 for update;
1089 --
1090 begin
1091 --
1092 -- The following code will insert link input values when a link is inserted.
1093 if p_insert_type = 'INSERT_LINK' then
1094 --
1095 -- For each input value for the element type NB. this locks all the records
1096 for iv_rec in c_input_value(p_element_type_id) loop
1097 --
1098 -- Check to see if this input value has already been processed. If it has
1099 -- then do not process again
1100 if iv_rec.input_value_id <> v_old_input_value_id then
1101 --
1102 -- Get sequence number for link_input_value
1103 select pay_link_input_values_s.nextval
1104 into v_link_input_value_id
1105 from sys.dual;
1106 --
1107 -- Copy the date effective rows from the input value to the link input
1108 -- value where they overlap
1109 insert into pay_link_input_values_f
1110 (LINK_INPUT_VALUE_ID,
1111 EFFECTIVE_START_DATE,
1112 EFFECTIVE_END_DATE,
1113 ELEMENT_LINK_ID,
1114 INPUT_VALUE_ID,
1115 COSTED_FLAG,
1116 DEFAULT_VALUE,
1117 MAX_VALUE,
1118 MIN_VALUE,
1119 WARNING_OR_ERROR,
1120 LAST_UPDATE_DATE,
1121 LAST_UPDATED_BY,
1122 LAST_UPDATE_LOGIN,
1123 CREATED_BY,
1124 CREATION_DATE)
1125 select
1126 v_link_input_value_id,
1127 greatest(p_validation_start_date,iv.effective_start_date),
1128 least(p_validation_end_date,iv.effective_end_date),
1129 p_element_link_id,
1130 iv.input_value_id,
1131 decode(p_costable_type,
1132 'F', decode(iv_tl.name, p_pay_value_name, 'Y','N'),
1133 'C', decode(iv_tl.name, p_pay_value_name, 'Y','N'),
1134 'D', decode(iv_tl.name, p_pay_value_name, 'Y','N'),
1135 'N'),
1136 decode(iv.hot_default_flag,'Y',NULL,iv.default_value),
1137 decode(iv.hot_default_flag,'Y',NULL,iv.max_value),
1138 decode(iv.hot_default_flag,'Y',NULL,iv.min_value),
1139 decode(iv.hot_default_flag,'Y',NULL,iv.warning_or_error),
1140 sysdate,
1141 -1,
1142 -1,
1143 -1,
1144 sysdate
1145 from pay_input_values_f_tl iv_tl,
1146 pay_input_values_f iv
1147 where iv_tl.input_value_id = iv.input_value_id
1148 and iv.input_value_id = iv_rec.input_value_id
1149 and userenv('LANG') = iv_tl.language
1150 and iv.effective_start_date <= p_validation_end_date
1151 and iv.effective_end_date >= p_validation_start_date;
1152 --
1153 --
1154 -- Hold onto the current input_value_id that has been processed for use in
1155 -- a check to make sure that it is not processed twice
1156 v_old_input_value_id := iv_rec.input_value_id;
1157 --
1158 end if;
1159 --
1160 end loop;
1161 --
1162 elsif p_insert_type = 'INSERT_INPUT_VALUE' then
1163 --
1164 -- insert link input values when an new input value has been inserted and
1165 -- links already exist.
1166 --
1167 insert into pay_link_input_values_f
1168 (LINK_INPUT_VALUE_ID,
1169 EFFECTIVE_START_DATE,
1170 EFFECTIVE_END_DATE,
1171 ELEMENT_LINK_ID,
1172 INPUT_VALUE_ID,
1173 COSTED_FLAG,
1174 DEFAULT_VALUE,
1175 MAX_VALUE,
1176 MIN_VALUE,
1177 WARNING_OR_ERROR,
1178 LAST_UPDATE_DATE,
1179 LAST_UPDATED_BY,
1180 LAST_UPDATE_LOGIN,
1181 CREATED_BY,
1182 CREATION_DATE)
1183 select
1184 pay_link_input_values_s.nextval,
1185 greatest(p_validation_start_date,eL.effective_start_date),
1186 least(p_validation_end_date,eL.effective_end_date),
1187 el.element_link_id,
1188 p_input_value_id,
1189 decode(el.costable_type,
1190 'F', decode(P_input_value_name, p_pay_value_name, 'Y','N'),
1191 'C', decode(p_input_value_name, p_pay_value_name, 'Y','N'),
1192 'D', decode(p_input_value_name, p_pay_value_name, 'Y','N'),
1193 'N'),
1194 decode(p_hot_default_flag,'Y',NULL,p_default_value),
1195 decode(p_hot_default_flag,'Y',NULL,p_max_value),
1196 decode(P_hot_default_flag,'Y',NULL,p_min_value),
1197 decode(p_hot_default_flag,'Y',NULL,p_warning_or_error_flag),
1198 sysdate,
1199 -1,
1200 -1,
1201 -1,
1202 sysdate
1203 from pay_element_links_F el
1204 where p_element_type_id = el.element_type_id
1205 and el.effective_start_date <= p_validation_end_date
1206 and el.effective_end_date >= p_validation_start_date;
1207
1208 End if; -- decision code for insert type.
1209 --
1210 End create_link_input_value;
1211 --
1212 --
1213 /*
1214 NAME
1215 ins_3p_input_values
1216 DESCRIPTION
1217 This procedure controls the third party inserts when an input value is
1218 created manually. (Rather than being created at the same time as an element
1219 type.) It calls the procedures create_link_input_value and
1220 hr_balances.ins_balance_feed.
1221 */
1222 --
1223 PROCEDURE ins_3p_input_values(p_val_start_date in date,
1224 p_val_end_date in date,
1225 p_element_type_id in number,
1226 p_primary_classification_id in number,
1227 p_input_value_id in number,
1228 p_default_value in varchar2,
1229 p_max_value in varchar2,
1230 p_min_value in varchar2,
1231 p_warning_or_error_flag in varchar2,
1232 p_input_value_name in varchar2,
1233 p_db_items_flag in varchar2,
1234 p_costable_type in varchar2,
1235 p_hot_default_flag in varchar2,
1236 p_business_group_id in number,
1237 p_legislation_code in varchar2,
1238 p_startup_mode in varchar2) is
1239 --
1240 l_pay_value_name varchar2(80);
1241 --
1242 --
1243 begin
1244 --
1245 -- Obtain Pay value name from translation table.
1246 l_pay_value_name :=
1247 hr_input_values.get_pay_value_name(p_legislation_code);
1248 --
1249 -- Call function to insert new link input value
1250 hr_input_values.create_link_input_value('INSERT_INPUT_VALUE',
1251 NULL,
1252 p_input_value_id ,
1253 p_input_value_name ,
1254 NULL,
1255 p_val_start_date ,
1256 p_val_end_date ,
1257 p_default_value ,
1258 p_max_value ,
1259 p_min_value ,
1260 p_warning_or_error_flag ,
1261 p_hot_default_flag ,
1262 p_legislation_code ,
1263 l_pay_value_name ,
1264 p_element_type_id );
1265 --
1266 -- A balance feed will be inserted if a new pay value is created.
1267 if p_input_value_name = l_pay_value_name then
1268 hr_balances.ins_balance_feed('INS_PER_PAY_VALUE',
1269 p_input_value_id,
1270 NULL,
1271 p_primary_classification_id,
1272 NULL,NULL,NULL,NULL,
1273 p_val_start_date,
1274 p_business_group_id,
1275 p_legislation_code,
1276 p_startup_mode);
1277 --
1278 end if;
1279 --
1280 if p_db_items_flag = 'Y' then
1281 --
1282 -- Create database items
1283 --
1284 hrdyndbi.create_input_value_dict(
1285 p_input_value_id,
1286 p_val_start_date);
1287 --
1288 end if;
1289
1290 end ins_3p_input_values;
1291 --
1292 /*
1293 NAME
1294 upd_3p_input_values
1295 DESCRIPTION
1296 This procedure should be called on post delete. When the name has been
1297 updated and create database items is set to Yes then the database items
1298 will be dropped and recreated. This will fail if it is unable to drop the
1299 database items.
1300 */
1301 PROCEDURE upd_3p_input_values(p_input_value_id in number,
1302 p_val_start_date in date,
1303 p_old_name in varchar2,
1304 p_name in varchar2,
1305 p_db_items_flag in varchar2,
1306 p_old_db_items_flag in varchar2) is
1307 --
1308 begin
1309 --
1310 if (p_db_items_flag = 'Y') and (p_old_name <> p_name) then
1311 --
1312 hrdyndbi.delete_input_value_dict(
1313 p_input_value_id);
1314 --
1315 hrdyndbi.create_input_value_dict(
1316 p_input_value_id,
1317 p_val_start_date);
1318 --
1319 elsif (p_db_items_flag = 'Y' and p_old_db_items_flag = 'N') then
1320 --
1321 hrdyndbi.create_input_value_dict(
1322 p_input_value_id,
1323 p_val_start_date);
1324 --
1325 elsif (p_db_items_flag = 'N' and p_old_db_items_flag = 'Y') then
1326 --
1327 hrdyndbi.delete_input_value_dict(
1328 p_input_value_id);
1329 --
1330 end if;
1331 end upd_3p_input_values;
1332
1333 --
1334 /*
1335 NAME
1336 del_3p_input_values
1337 DESCRIPTION
1338 This procedure does the necessary cascade deletes when deleting an input
1339 value. This only deletes balance feeds. It calls the procedure -
1340 hr.balances.del_balance_feed.
1341 */
1342 --
1343 PROCEDURE del_3p_input_values(p_delete_mode in varchar2,
1344 p_input_value_id in number,
1345 p_db_items_flag in varchar2,
1346 p_val_end_date in date,
1347 p_session_date in date,
1348 p_startup_mode in varchar2) is
1349 --
1350 l_delete_mode varchar2(30);
1351 l_on_final_record varchar2(1) := 'N';
1352 v_end_of_time date;
1353 --
1354 begin
1355 --
1356 hr_balances.del_balance_feed
1357 ('DEL_INPUT_VALUE',
1358 p_delete_mode,
1359 NULL,
1360 p_input_value_id,
1361 NULL,
1362 NULL,
1363 NULL,
1364 NULL,
1365 NULL,
1366 p_session_date,
1367 p_val_end_date,
1368 NULL,
1369 p_startup_mode);
1370 --
1371 --
1372 -- DELETE and DELETE_FUTURE_CHANGE are not allowed on input values.
1373 --
1374 -- Delete link input values
1375 --
1376 if p_delete_mode = 'ZAP' then
1377 delete
1378 from pay_link_input_values_f
1379 where input_value_id = p_input_value_id;
1380 --
1381 elsif p_delete_mode = 'DELETE_NEXT_CHANGE' then
1382 --
1383 -- DELETE_NEXT_CHANGE will only affect the link input value records if we are
1384 -- on The final record of the input value. In this case the final link input
1385 -- value records will need to be extended to the end of time.
1386 --
1387 begin
1388 --
1389 select 'Y'
1390 into l_on_final_record
1391 from pay_input_values_f iv1
1392 where p_input_value_id = iv1.input_value_id
1393 and p_session_date between
1394 iv1.effective_start_date and iv1.effective_end_date
1395 and iv1.effective_end_date =
1396 (select max(iv2.effective_end_date)
1397 from pay_input_values_f iv2
1398 where p_input_value_id = iv2.input_value_id);
1399 --
1400 exception
1401 when NO_DATA_FOUND then NULL;
1402 end;
1403 --
1404 if l_on_final_record = 'Y' then
1405 --
1406 v_end_of_time := to_date('31/12/4712', 'DD/MM/YYYY');
1407 --
1408 update pay_link_input_values_f lv1
1409 set lv1.effective_end_date = v_end_of_time
1410 where p_input_value_id = lv1.input_value_id
1411 and lv1.effective_end_date =
1412 (select max(lv2.effective_end_date)
1413 from pay_link_input_values_f lv2
1414 where lv2.link_input_value_id = lv1.link_input_value_id
1415 and lv2.input_value_id = p_input_value_id);
1416 --
1417 end if;
1418 --
1419 --
1420 end if;
1421 -- Create database items
1422 if p_db_items_flag = 'Y' then
1423 --
1424 hrdyndbi.delete_input_value_dict(p_input_value_id);
1425 end if;
1426 --
1427 end del_3p_input_values;
1428 --
1429 end hr_input_values;
[Home] [Help]