1 PACKAGE BODY pay_template_ivs AS
2 /* $Header: pyaddivs.pkb 115.2 2003/04/03 17:55:28 ekim ship $ */
3 /*
4 Copyright 1996
5 Oracle Corporation
6 Redwood Shores, California 94065
7 USA
8
9 Filename : pyaddivs.pkb
10
11 Description : Design for API enabling addition of an input value
12 over the lifetime of an EXISTING element type.
13
14 Change History
15 -----------------
16 Date Name Vers Bug No Description
17 ----------- ---------- ------ ------- --------------------------
18 05-Aug-1996 hparicha 40.0 Created.
19 06-Jun-1997 sxshah 40.3 Moved Whenever SQLERROR to top.
20 This script did not compile.!!
21 03-Apr-2003 ekim 115.2 2886771 Changed hr_utility message to reflect
22 package name pay_template_ivs rather
23 than hr_input_values.
24 Added gscc fix.
25 ======================================================================
26
27 I. REQUIREMENTS DEFINITION
28
29 An ongoing upgrade issue with US Oracle Payroll has been needing to
30 change, fix, or enhance template Earnings and Deductions which
31 ALREADY EXIST on the customer account - ie. a live account.
32
33 The solution will be to provide the new or changed functionality
34 transparently to the customer - without interruption to normal operation
35 and without (or with minimal) manual intervention from the users.
36
37
38 II. CURRENT DIFFICULTIES, SCOPE DEFINITION
39
40 The major diffuculty with enhancing existing elements is the need to
41 add input values on elements. The API pay_db_pay_setup.create_input_value
42 performs a check preventing addition of an input value if run results exist
43 for the element. This has resulted in requiring the customer to
44 a. Rollback all existing payroll runs which included the existing element, OR
45 b. Create new elements such that the new functionality is enabled, and
46 replacing the use of existing elements with new ones.
47
48 Both options (a) and (b) are less than desirable, passable with
49 beta-customers, but not acceptable for live customers.
50
51 The upgrade solution provided here will enable delivery of new
52 functionality to existing earnings and deductions WITHOUT requiring
53 (a) or (b) from the customer.
54
55 When new functionality is added to an existing earning or deduction,
56 the functionality is enabled and will be operational for any NEW payroll
57 runs and quickpays processed on the customer account. Any existing runs
58 on the customer account are left intact but obviously will not have used
59 the new functionality. This solution/upgrade will enable the new
60 functionality to be operational for existing runs which are rolled back
61 and re-processed. This is handled by the upgrade which will
62 add new functionality over the lifetime of the element.
63
64
65 III. TECHNICAL DETAILS
66
67 The normal template generation code in packages pygenptx.pkb, pyusuidt.pkb,
68 pywatgen.pkb will handle the creation of the following rows for the new
69 input value on deductions templates:
70 PAY_INPUT_VALUES_F
71 PAY_BALANCE_FEEDS_F
72 PAY_FORMULA_RESULT_RULES_F
73 NOTE: These rows can be created at any time, regardless of existing
74 payroll runs on the live account.
75
76 The package pyusuiet.pkb does the same for earnings templates.
77
78 The new New_Input_Value API needs to retrofit [date-effective] rows in
79 the following tables:
80 PAY_LINK_INPUT_VALUES_F
81 PAY_ELEMENT_ENTRY_VALUES_F
82 PAY_RUN_RESULT_VALUES
83 NOTE: These are the rows created for and by payroll runs.
84
85
86 IV. ALGORITHM
87
88 1a. For the element type having a new input value added, we need to find all
89 element links...
90 Date-effective Element Links that exist for the element type on which
91 the input value is being added:
92 SELECT DISTINCT pel.element_link_id
93 INTO l_element_link_id
94 FROM pay_element_links_f pel
95 WHERE pel.element_type_id = p_element_type_id
96 ORDER BY pel.effective_start_date;
97
98
99 1b. For each element link id found in 1a, find the min start and max end date.
100 SELECT min(pel.effective_start_date)
101 INTO l_link_eff_start
102 FROM pay_element_links_f pel
103 WHERE pel.element_link_id = l_element_link_id;
104
105 SELECT max(pel.effective_end_date)
106 INTO l_link_eff_end
107 FROM pay_element_links_f pel
108 WHERE pel.element_link_id = l_element_link_id;
109
110
111 1c. Then create appropriately date-effective link input value row...
112 Create SINGLE date-effective PAY_LINK_INPUT_VALUES_F row...lasting from
113 earliest (min) effective_start_date for element link...to latest (max)
114 eff end date of link...is this ideally the end of time? yes.
115 INSERT INTO pay_link_input_values_f (
116 link_input_value_id,
117 element_link_id,
118 input_value_id,
119 effective_start_date,
120 effective_end_date,
121 costed_flag,
122 default_value,
123 max_value,
124 min_value,
125 warning_or_error
126 )
127 VALUES (
128 pay_link_input_values_s.nextval,
129 l_element_link_id,
130 p_input_value_id, -- ie. id of iv being added
131 l_link_eff_start,
132 l_link_eff_end,
133 p_costed_flag,
134 p_default_value,
135 p_max_value,
136 p_min_value,
137 p_warning_or_error
138 );
139
140
141 2a. For each link row found in 1a, need to find date-effective
142 Element Entries that exist for the element link:
143 SELECT DISTINCT pee.element_entry_id
144 INTO l_element_entry_id
145 FROM pay_element_entries_f pee
146 WHERE pee.element_link_id = l_element_link_id -- ie. link found in 1a.
147 ORDER BY pee.element_entry_id;
148
149
150 2b. For each entry found in 2a, find all date-effective entry value rows
151 over the life of the entry.
152
153 select DISTINCT pev.effective_start_date,
154 pev.effective_end_date
155 from pay_element_entry_values_f pev
156 where pev.element_entry_id = l_element_entry_id
157 order by pev.effective_start_date;
158
159
160 2c. For each date-effective row found in 2b, create date-effective
161 PAY_ELEMENT_ENTRY_VALUES_F row...MULTIPLE ROWS...
162 INSERT INTO pay_element_entry_values_f (
163 element_entry_value_id,
164 element_entry_id,
165 input_value_id,
166 effective_start_date,
167 effective_end_date,
168 screen_entry_value
169 )
170 VALUES (
171 pay_element_entry_values_s.nextval,
172 l_element_entry_id,
173 p_input_value_id, -- ie. id of iv being added
174 l_entry_eff_start,
175 l_entry_eff_end,
176 nvl(p_default_value, l_screen_entry_value)
177 );
178
179
180 3a. Get run results that exist for the element type on which the input value
181 is being added:
182 SELECT DISTINCT prr.run_result_id
183 INTO l_run_result_id
184 FROM pay_run_results prr
185 AND prr.element_type_id = p_element_type_id -- ie. ele w/new iv.
186 ORDER BY prr.run_result_id;
187
188
189 3b. For each run result found in 3a, create PAY_RUN_RESULT_VALUES row...
190 INSERT INTO pay_run_result_values (
191 run_result_id,
192 input_value_id,
193 result_value
194 )
195 VALUES (
196 l_run_result_id,
197 p_input_value_id, -- ie. id of iv being added
198 nvl(p_default_value, l_run_result_value
199 );
200
201 ======================================================================
202 */
203
204 PROCEDURE new_input_value (
205 p_element_type_id in number,
206 p_input_value_id in number,
207 p_costed_flag in varchar2 default 'N',
208 p_default_value in varchar2 default NULL,
209 p_max_value in varchar2 default NULL,
210 p_min_value in varchar2 default NULL,
211 p_warning_or_error in varchar2 default NULL) is
212
213 l_element_link_id number(9);
214 l_link_eff_start date;
215 l_link_eff_end date;
216
217 l_element_entry_id number(9);
218 l_entry_eff_start date;
219 l_entry_eff_end date;
220 l_screen_entry_value varchar2(60) := NULL;
221
222 l_run_result_id number(9);
223 l_run_result_value varchar2(60) := NULL;
224
225 already_exists number;
226 entryval_exists number;
227
228 CURSOR get_links (p_ele_id NUMBER) IS
229 SELECT pel.element_link_id
230 FROM pay_element_links_f pel
231 WHERE pel.element_type_id = p_ele_id
232 ORDER BY pel.effective_start_date;
233
234 CURSOR get_entries (p_link_id NUMBER) IS
235 SELECT pee.element_entry_id
236 FROM pay_element_entries_f pee
237 WHERE pee.element_link_id = p_link_id
238 ORDER BY pee.element_entry_id;
239
240 CURSOR get_entry_values (p_entry_id NUMBER) IS
241 select pev.effective_start_date,
242 pev.effective_end_date
243 from pay_element_entry_values_f pev
244 where pev.element_entry_id = p_entry_id
245 order by pev.effective_start_date;
246
247 CURSOR get_results (p_eletype_id NUMBER) IS
248 SELECT prr.run_result_id
249 FROM pay_run_results prr
250 WHERE prr.element_type_id = p_eletype_id
251 ORDER BY prr.run_result_id;
252
253
254 BEGIN
255
256 /*
257 1a. For the element type having a new input value added, we need to find all
258 element links...
259 Date-effective Element Links that exist for the element type on which
260 the input value is being added:
261 */
262
263 OPEN get_links(p_element_type_id);
264 LOOP
265
266 /*
267 1b. For each element link id found in 1a, find the min start and max end date.
268 */
269
270 FETCH get_links
271 INTO l_element_link_id;
272 EXIT WHEN get_links%NOTFOUND;
273
274 SELECT min(pel.effective_start_date)
275 INTO l_link_eff_start
276 FROM pay_element_links_f pel
277 WHERE pel.element_link_id = l_element_link_id;
278
279 SELECT max(pel.effective_end_date)
280 INTO l_link_eff_end
281 FROM pay_element_links_f pel
282 WHERE pel.element_link_id = l_element_link_id;
283
284 /*
285 1c. Then create appropriately date-effective link input value row...
286 Create SINGLE date-effective PAY_LINK_INPUT_VALUES_F row...lasting from
287 earliest (min) effective_start_date for element link...to latest (max)
288 eff end date of link...is this ideally the end of time? yes.
289 */
290
291 /*
292 Check if link_input_value already exists before inserting...
293 if it does, do nothing...all this tells us is the upgrade has
294 already been attempted for this element...and the input value has
295 already been added successfully to this point.
296 */
297
298 already_exists := hr_template_existence.upg_link_iv_exists(
299 p_element_link_id => l_element_link_id,
300 p_input_val_id => p_input_value_id);
301
302 if already_exists = 0 then
303
304 INSERT INTO pay_link_input_values_f (
305 link_input_value_id,
306 element_link_id,
307 input_value_id,
308 effective_start_date,
309 effective_end_date,
310 costed_flag,
311 default_value,
312 max_value,
313 min_value,
314 warning_or_error
315 )
316 VALUES (
317 pay_link_input_values_s.nextval,
318 l_element_link_id,
319 p_input_value_id,
320 l_link_eff_start,
321 l_link_eff_end,
322 p_costed_flag,
323 p_default_value,
324 p_max_value,
325 p_min_value,
326 p_warning_or_error
327 );
328
329 -- dbms_output.put_line('Added link input val: link = '||l_element_link_id||' iv = '||p_input_value_id);
330
331 end if;
332
333 /*
334 2a. For each link row found in 1a, need to find date-effective
335 Element Entries that exist for the element link:
336 */
337
338 OPEN get_entries(l_element_link_id);
339 LOOP
340
341 /*
342 2b. For each entry found in 2a, find all date-effective entry value rows
343 over the life of the entry.
344 */
345
346 FETCH get_entries
347 INTO l_element_entry_id;
348 EXIT WHEN get_entries%NOTFOUND;
349
350 OPEN get_entry_values(l_element_entry_id);
351 LOOP
352
353 /*
354 2c. For each date-effective row found in 2b, create date-effective
355 PAY_ELEMENT_ENTRY_VALUES_F row...MULTIPLE ROWS...
356 */
357
358 FETCH get_entry_values
359 INTO l_entry_eff_start,
360 l_entry_eff_end;
361 EXIT WHEN get_entry_values%NOTFOUND;
362
363 /*
364 Check if entry value already exists for this iv. If so, do nothing.
365 */
366
367 already_exists := hr_template_existence.upg_entry_val_exists(
368 p_element_entry_id => l_element_entry_id,
369 p_input_val_id => p_input_value_id);
370
371 if already_exists = 0 then
372
373 INSERT INTO pay_element_entry_values_f (
374 element_entry_value_id,
375 element_entry_id,
376 input_value_id,
377 effective_start_date,
378 effective_end_date,
379 screen_entry_value
380 )
381 VALUES (
382 pay_element_entry_values_s.nextval,
383 l_element_entry_id,
384 p_input_value_id,
385 l_entry_eff_start,
386 l_entry_eff_end,
387 nvl(p_default_value, l_screen_entry_value)
388 );
389
390 -- dbms_output.put_line('Added entry val: entry = '||l_element_entry_id||' iv = '||p_input_value_id);
391
392 else
393
394 select count(0)
395 into entryval_exists
396 from pay_element_entry_values_f
397 where element_entry_value_id = already_exists
398 and effective_start_date = l_entry_eff_start;
399
400 if entryval_exists = 0 then
401
402 INSERT INTO pay_element_entry_values_f (
403 element_entry_value_id,
404 element_entry_id,
405 input_value_id,
406 effective_start_date,
407 effective_end_date,
408 screen_entry_value
409 )
410 VALUES (
411 already_exists,
412 l_element_entry_id,
413 p_input_value_id,
414 l_entry_eff_start,
415 l_entry_eff_end,
416 nvl(p_default_value, l_screen_entry_value)
417 );
418
419 -- dbms_output.put_line('Added entry val: entry = '||l_element_entry_id||' iv = '||p_input_value_id);
420
421 end if;
422
423 end if;
424
425 END LOOP; -- get_entry_values
426 CLOSE get_entry_values;
427
428 END LOOP; -- get_entries
429 CLOSE get_entries;
430
431 END LOOP; -- get_links
432 CLOSE get_links;
433
437 */
434 /*
435 3a. Get run results that exist for the element type on which the input value
436 is being added:
438
439 OPEN get_results(p_element_type_id);
440 LOOP
441
442 /*
443 3b. For each run result found in 3a, create PAY_RUN_RESULT_VALUES row...
444 */
445
446 FETCH get_results
447 INTO l_run_result_id;
448 EXIT WHEN get_results%NOTFOUND;
449
450 /*
451 Check if run result values already exist for this iv. If so, do nothing.
452 */
453
454 already_exists := hr_template_existence.upg_result_val_exists(
455 p_run_result_id => l_run_result_id,
456 p_input_val_id => p_input_value_id);
457
458 if already_exists = 0 then
459
460 INSERT INTO pay_run_result_values (
461 run_result_id,
462 input_value_id,
463 result_value
464 )
465 VALUES (
466 l_run_result_id,
467 p_input_value_id,
468 nvl(p_default_value, l_run_result_value)
469 );
470
471 -- dbms_output.put_line('Added run result val: result = '||l_run_result_id||' iv = '||p_input_value_id);
472
473 end if;
474
475 END LOOP; -- get_results
476 CLOSE get_results;
477
478 END new_input_value;
479
480
481
482
483
484
485
486
487
488
489 --
490 /*
491 NAME
492 ins_3p_input_values
493 DESCRIPTION
494 This procedure controls the third party inserts when an input value is
495 created manually. (Rather than being created at the same time as an element
496 type.) It calls the procedures create_link_input_value and
497 hr_balances.ins_balance_feed.
498
499 NOTE: This procedure has been copied from hr_input_values package.
500 For purposes of upgrading template earnings and deductions, we do not
501 need to call the link input value and balance feed api - so these have been
502 commented out. The upgrade procedure will handle adding these rows
503 appropriately over the lifetime of the element type being upgraded.
504 */
505 --
506 PROCEDURE ins_3p_input_values(p_val_start_date in date,
507 p_val_end_date in date,
508 p_element_type_id in number,
509 p_primary_classification_id in number,
510 p_input_value_id in number,
511 p_default_value in varchar2,
512 p_max_value in varchar2,
513 p_min_value in varchar2,
514 p_warning_or_error_flag in varchar2,
515 p_input_value_name in varchar2,
516 p_db_items_flag in varchar2,
517 p_costable_type in varchar2,
518 p_hot_default_flag in varchar2,
519 p_business_group_id in number,
520 p_legislation_code in varchar2,
521 p_startup_mode in varchar2) is
522 --
523 l_pay_value_name varchar2(80);
524 --
525 --
526 begin
527 --
528 hr_utility.set_location('pay_template_ivs.ins_3p_input_values', 1);
529 /*
530 --
531 -- Obtain Pay value name from translation table.
532 l_pay_value_name :=
533 hr_input_values.get_pay_value_name(p_legislation_code);
534 --
535 -- Call function to insert new link input value
536 hr_input_values.create_link_input_value('INSERT_INPUT_VALUE',
537 NULL,
538 p_input_value_id ,
539 p_input_value_name ,
540 NULL,
541 p_val_start_date ,
542 p_val_end_date ,
543 p_default_value ,
544 p_max_value ,
545 p_min_value ,
546 p_warning_or_error_flag ,
547 p_hot_default_flag ,
548 p_legislation_code ,
549 l_pay_value_name ,
550 p_element_type_id );
551 --
552 */
553
554 /*
555 -- A balance feed will be inserted if a new pay value is created.
556 if p_input_value_name = l_pay_value_name then
557 hr_balances.ins_balance_feed('INS_PER_PAY_VALUE',
558 p_input_value_id,
559 NULL,
560 p_primary_classification_id,
561 NULL,NULL,NULL,NULL,
562 p_val_start_date,
563 p_business_group_id,
564 p_legislation_code,
565 p_startup_mode);
566 --
567 end if;
568 */
569 --
570 if p_db_items_flag = 'Y' then
571 --
572 -- Create database items
573 --
574 hrdyndbi.create_input_value_dict(
575 p_input_value_id,
576 p_val_start_date);
577 --
578 end if;
579
580 end ins_3p_input_values;
581 --
582
583
584 /*
585 NAME
586 chk_input_value
587 DESCRIPTION
588 Checks attributes of inserted and update input values for concurrence
589 with business rules.
590
591 NOTE: This procedure has been copied from hr_input_values package.
592 For purposes of upgrading template earnings and deductions, we do not
593 need to check for existing element entries or run results - so these checks
594 have been commented out. The upgrade procedure will handle adding these
595 rows appropriately over the lifetime of the element type being upgraded.
596 */
597 --
598 PROCEDURE chk_input_value(p_element_type_id in number,
602 p_insert_update_flag in varchar2,
599 p_legislation_code in varchar2,
600 p_val_start_date in date,
601 p_val_end_date in date,
603 p_input_value_id in number,
604 p_rowid in varchar2,
605 p_recurring_flag in varchar2,
606 p_mandatory_flag in varchar2,
607 p_hot_default_flag in varchar2,
608 p_standard_link_flag in varchar2,
609 p_classification_type in varchar2,
610 p_name in varchar2,
611 p_uom in varchar2,
612 p_min_value in varchar2,
613 p_max_value in varchar2,
614 p_default_value in varchar2,
615 p_lookup_type in varchar2,
616 p_formula_id in number,
617 p_generate_db_items_flag in varchar2,
618 p_warning_or_error in varchar2) is
619 --
620 v_validation_check varchar2(1);
621 v_num_input_values number;
622 l_pay_value_name varchar2(80);
623 --
624 begin
625 hr_utility.set_location('pay_template_ivs.chk_input_value', 1);
626 --
627 -- get pay value name
628 l_pay_value_name := hr_input_values.get_pay_value_name
629 (p_legislation_code);
630 --
631 -- payments type 'Pay Values' must have uom of money
632 --
633 if p_name = l_pay_value_name and
634 p_classification_type = 'N' and
635 p_uom <> 'M' then
636 --
637 hr_utility.set_message(801,'');
638 hr_utility.raise_error;
639 --
640 end if;
641
642 hr_utility.set_location('pay_template_ivs.chk_input_value', 10);
643 --
644 if p_insert_update_flag = 'INSERT' then
645 -- Make sure that a maximum of 6 input values can be created
646 begin
647 --
648 select count(distinct iv.input_value_id)
649 into v_num_input_values
650 from pay_input_values_f iv
651 where iv.element_type_id = p_element_type_id
652 and p_val_start_date between
653 iv.effective_start_date and iv.effective_end_date;
654 --
655 exception
656 when NO_DATA_FOUND then NULL;
657 end;
658 if v_num_input_values >= 6 then
659 --
660 hr_utility.set_message(801,'PAY_6167_INPVAL_ONLY_6');
661 hr_utility.raise_error;
662 --
663 end if;
664 --
665 /*
666 v_validation_check := 'Y';
667 --
668 -- no entries can be in existence
669 -- during the validation period
670 -- for the other input values.
671 -- This check only needs to be done on insert not on updatE
672 --
673 begin
674 --
675 select 'N'
676 into v_validation_check
677 from sys.dual
678 where exists
679 (select 1
680 from pay_element_links_f el,
681 pay_element_entries_f ee
682 where p_element_type_id = el.element_type_id
683 and el.element_link_id = ee.element_link_id
684 and ee.effective_end_date >= p_val_start_date
685 and ee.effective_start_date <= p_val_end_date);
686 --
687 exception
688 when NO_DATA_FOUND then NULL;
689 end;
690 --
691 if v_validation_check = 'N' then
692 --
693 hr_utility.set_message(801,'PAY_6197_INPVAL_NO_ENTRY');
694 hr_utility.raise_error;
695 --
696 end if;
697 --
698 hr_utility.set_location('pay_template_ivs.chk_input_value', 20);
699 --
700 */
701
702 end if;-- In INSERT mode
703 --
704 -- Make sure that the input value name is unique within the element
705 -- This will ensure also that only one PAY_VALUE can be used.
706 begin
707 select 'N'
708 into v_validation_check
709 from sys.dual
710 where exists
711 (select 1
712 from pay_input_values_f
713 where element_type_id = p_element_type_id
714 and input_value_id <> p_input_value_id
715 and upper(p_name) = upper(name));
716 --
717 exception
718 when NO_DATA_FOUND then NULL;
719 end;
720
721 hr_utility.set_location('pay_template_ivs.chk_input_value', 40);
722 --
723 if v_validation_check = 'N' then
724 --
725 hr_utility.set_message(801,'PAY_6168_INPVAL_DUP_NAME');
726 hr_utility.raise_error;
727 --
728 end if;
729 --
730 -- Hot defaulted values must be mandatory.
731 --
732 if (p_hot_default_flag = 'Y' and p_mandatory_flag = 'N') then
733 --
734 hr_utility.set_message(801,'PAY_6609_ELEMENT_HOT_DEF_MAN');
735 hr_utility.raise_error;
736 --
737 end if;
738 --
739 -- Hot defaulted values must have default, max and min less than 59
740 -- characters. This is to allow for the inclusion of quotes around the
741 -- values when they are displayed at the lower level
742 if (p_hot_default_flag = 'Y') and
743 ((length(p_default_value) > 58) or
744 (length(p_min_value) > 58) or
745 (length(p_max_value) > 58)) then
746 --
747 hr_utility.set_message(801,'PAY_6616_INPVAL_HOT_LESS_58');
748 hr_utility.raise_error;
749 --
750 end if;
751 --
752 -- If the element is nonrecurring then do not allow any non-numeric input
756 p_generate_db_items_flag = 'Y') and
753 -- values to have create db items set to 'Y' ie. cannot specify Date or
754 -- Character. This is so that they can be summed on the entity horizon
755 if ((p_recurring_flag = 'N' and
757 ((p_uom = 'C') or
758 (p_uom like 'D%'))) then
759 --
760 hr_utility.set_message(801,'PAY_6169_INPVAL_ONLY_NUM');
761 hr_utility.raise_error;
762 --
763 end if;
764 --
765 -- Makes sure that the validation specified for the input value is correct
766 -- ie. it can either be formula and default OR
767 -- lookup type and default OR default and
768 -- min / max
769 if p_formula_id is not NULL then
770 --
771 if (p_lookup_type is not NULL or
772 p_min_value is not NULL or
773 p_max_value is not NULL or
774 p_warning_or_error is NULL) then
775 --
776 hr_utility.set_message(801,'PAY_6905_INPVAL_FORMULA_VAL');
777 hr_utility.raise_error;
778 --
779 end if;
780 --
781 hr_utility.set_location('pay_template_ivs.chk_input_value', 50);
782 --
783 elsif p_lookup_type is not NULL then
784 --
785 if (p_min_value is not NULL or
786 p_max_value is not NULL or
787 p_formula_id is not NULL or
788 p_warning_or_error is not NULL) then
789 --
790 hr_utility.set_message(801,'PAY_6906_INPVAL_LOOKUP_VAL');
791 hr_utility.raise_error;
792 --
793 end if;
794 hr_utility.set_location('pay_template_ivs.chk_input_value', 60);
795 --
796 --
797 elsif (p_min_value is not NULL or p_max_value is not NULL) then
798 --
799 if (p_lookup_type is not NULL or
800 p_formula_id is not NULL) then
801 --
802 hr_utility.set_message(801,'PAY_6907_INPVAL_MIN_MAX_VAL');
803 hr_utility.raise_error;
804 --
805 elsif (p_warning_or_error is null) then
806 --
807 hr_utility.set_message(801,'PAY_6907_INPVAL_MIN_MAX_VAL');
808 hr_utility.raise_error;
809 --
810 end if;
811
812 end if;
813 --
814 if (p_warning_or_error is not null and
815 p_min_value is null and
816 p_max_value is null and
817 p_formula_id is null) then
818 --
819 hr_utility.set_message(801,'PAY_6908_INPVAL_ERROR_VAL');
820 hr_utility.raise_error;
821 --
822 end if;
823 hr_utility.set_location('pay_template_ivs.chk_input_value', 70);
824 --
825 -- Mkae sure that when lookup validation is being used that the default when
826 -- specified is valid for the lookup type
827 --
828 --
829 if (p_lookup_type is not NULL and p_default_value is not NULL) then
830 hr_utility.set_location('pay_template_ivs.chk_input_value', 80);
831 --
832 begin
833 --
834 v_validation_check := 'Y';
835 --
836 select 'N'
837 into v_validation_check
838 from sys.dual
839 where not exists(select 1
840 from hr_lookups
841 where lookup_type = p_lookup_type
842 and lookup_code = p_default_value);
843 --
844 exception
845 when NO_DATA_FOUND then NULL;
846 end;
847 --
848 hr_utility.set_location('pay_template_ivs.chk_input_value', 90);
849 --
850 if v_validation_check = 'N' then
851 --
852 hr_utility.set_message(801,'PAY_6171_INPVAL_NO_LOOKUP');
853 hr_utility.raise_error;
854 --
855 end if;
856 --
857 end if;
858 --
859
860 /*
861 -- No new input values can be created if there are any run results existing
862 -- for this element
863 begin
864 --
865 select 'N'
866 into v_validation_check
867 from sys.dual
868 where exists
869 (select 1
870 from pay_run_results rr
871 where rr.element_type_id = p_element_type_id);
872 --
873 exception
874 when NO_DATA_FOUND then null;
875 end;
876 --
877 if v_validation_check = 'N' then
878 hr_utility.set_message(801,'PAY_6913_INPVAL_NO_INS_RUN_RES');
879 hr_utility.raise_error;
880 end if;
881 */
882
883 end chk_input_value;
884 --
885
886
887 END pay_template_ivs;
[Home] [Help]