1 package body PAY_INPUT_VALUES_PKG as
2 /* $Header: pyipv.pkb 120.5 2007/10/25 12:44:40 ckesanap noship $ */
3 -- Declare global variables and cursors
4
5 -- Dummy variable for selecting into when not interested in value of result
6 g_dummy number(30);
7 --
8 g_element_type_id number(15); -- For validating translation.
9 --
10 -- The end of time for date effective records
11 c_end_of_time constant date := to_date ('31/12/4712','DD/MM/YYYY');
12 c_user_id number;
13 c_login_id number;
14
15 -------------------------------------------------------------------------------
16 PROCEDURE set_translation_globals(p_element_type_id IN NUMBER) IS
17 BEGIN
18 g_element_type_id := p_element_type_id;
19 END;
20 -------------------------------------------------------------------------------
21 procedure validate_translation(input_value_id IN NUMBER,
22 language IN VARCHAR2,
23 input_name IN VARCHAR2) IS
24 /*
25
26 This procedure fails if a input value translation is already present in
27 the table for a given language. Otherwise, no action is performed. It is
28 used to ensure uniqueness of translated input value names.
29
30 */
31
32 --
33 -- This cursor implements the validation we require,
34 -- but this will only work if the record exists in the db already,
35 -- and we have a primary key id. Note: The legislation code column
36 -- can be null, hence use a decode and NVL - this also applies to the
37 -- business group column.
38 --
39 cursor c_translation(p_language IN VARCHAR2,
40 p_input_name IN VARCHAR2,
41 p_input_value_id IN NUMBER) IS
42 SELECT 1
43 FROM pay_input_values_f_tl inptl,
44 pay_input_values_f inp
45 WHERE upper(inptl.name) = upper(p_input_name)
46 AND inptl.input_value_id = inp.input_value_id
47 AND inptl.language = p_language
48 AND (inp.input_value_id <> p_input_value_id OR p_input_value_id IS NULL)
49 AND inp.element_type_id = g_element_type_id;
50
51 l_package_name VARCHAR2(80) := 'PAY_INPUT_VALUES_PKG.VALIDATE_TRANSLATION';
52 l_name pay_balance_types.balance_name%type := input_name;
53 l_dummy varchar2(100);
54
55 BEGIN
56
57 hr_utility.set_location (l_package_name,1);
58
59 BEGIN
60 hr_chkfmt.checkformat (l_name,
61 'PAY_NAME',
62 l_dummy, null, null, 'N', l_dummy, null);
63 hr_utility.set_location (l_package_name,2);
64
65 EXCEPTION
66 when app_exception.application_exception then
67 hr_utility.set_location (l_package_name,3);
68 fnd_message.set_name ('PAY','PAY_6365_ELEMENT_NO_DB_NAME'); -- checkformat failure
69 fnd_message.raise_error;
70 END;
71
72 hr_utility.set_location (l_package_name,10);
73 OPEN c_translation(language, input_name, input_value_id);
74 hr_utility.set_location (l_package_name,20);
75 FETCH c_translation INTO g_dummy;
76
77 IF c_translation%NOTFOUND THEN
78 hr_utility.set_location (l_package_name,30);
79 CLOSE c_translation;
80 ELSE
81 hr_utility.set_location (l_package_name,40);
82 CLOSE c_translation;
83 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
84 fnd_message.raise_error;
85 END IF;
86 hr_utility.set_location ('Leaving: '||l_package_name,60);
87
88 END validate_translation;
89
90
91 --------------------------------------------------------------------------------
92 function NO_DEFAULT_AT_LINK (
93
94 --******************************************************************************
95 --* Returns TRUE if there is no default value specified at the link. *
96 --* This will affect whether or not the default at the type may be deleted for *
97 --* hot-defaulted standard entries. *
98 --******************************************************************************
99
100 -- Parameters are:
101
102 p_input_value_id number,
103 p_effective_start_date date,
104 p_effective_end_date date,
105 p_error_if_true boolean := FALSE ) return boolean is
106
107 cursor csr_link is
108 select 1
109 from pay_link_input_values_f
110 where input_value_id = p_input_value_id
111 and default_value is null
112 and effective_start_date <=p_effective_end_date
113 and effective_end_date >=p_effective_start_date;
114
115 v_no_default boolean := FALSE;
116
117 begin
118 open csr_link;
119 fetch csr_link into g_dummy;
120 v_no_default := csr_link%found;
121 close csr_link;
122
123 if p_error_if_true and v_no_default then
124 hr_utility.set_message (801, 'PAY_INPVAL_MUST_HAVE_DEFAULT');
125 hr_utility.raise_error;
126 end if;
127
128 return v_no_default;
129
130 end no_default_at_link;
131 --------------------------------------------------------------------------------
132 function ELEMENT_ENTRY_NEEDS_DEFAULT (
133
134 --******************************************************************************
135 --* Returns TRUE if an entry value uses the input_value's hot default *
136 --******************************************************************************
137 --
138 -- Parameters are:
139 --
140 p_input_value_id number,
141 p_effective_start_date date,
142 p_effective_end_date date,
143 p_error_if_true boolean := FALSE ) return boolean is
144 --
145 cursor csr_hot_defaulted_entry is
146 select 1
147 from pay_element_entry_values_f ENTRY,
148 pay_link_input_values_f LINK
149 where link.input_value_id = p_input_value_id
150 and link.input_value_id = entry.input_value_id
151 and link.default_value is null
152 and entry.screen_entry_value is null
153 and entry.effective_start_date <= p_effective_end_date
154 and entry.effective_end_date >= p_effective_start_date
155 and link.effective_start_date <= p_effective_end_date
156 and link.effective_end_date >= p_effective_start_date;
157 --
158 v_hot_default_required boolean := FALSE;
159 --
160 begin
161 --
162 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.ELEMENT_ENTRY_NEEDS_DEFAULT',1);
163 --
164 open csr_hot_defaulted_entry;
165 fetch csr_hot_defaulted_entry into g_dummy;
166 v_hot_default_required := csr_hot_defaulted_entry%found;
167 close csr_hot_defaulted_entry;
168 --
169 if p_error_if_true and v_hot_default_required then
170 hr_utility.set_message (801,'PAY_6191_INPVAL_NO_ENTRY_DEFS');
171 hr_utility.raise_error;
172 end if;
173 --
174 return v_hot_default_required;
175 --
176 end ELEMENT_ENTRY_NEEDS_DEFAULT;
177 --------------------------------------------------------------------------------
178 function CANT_DELETE_ALL_INPUT_VALUES (
179 --
180 --******************************************************************************
181 --* Returns TRUE if any input value for a given element may not be deleted *
182 --******************************************************************************
183 --
184 -- Parameters are:
185 --
186 p_element_type_id number,
187 p_delete_mode varchar2,
188 p_validation_start_date date,
189 p_validation_end_date date,
190 p_error_if_true boolean default FALSE
191 --
192 ) return boolean is
193 --
194 cursor csr_input_values is
195 select *
196 from pay_input_values_f
197 where element_type_id = p_element_type_id
198 and effective_start_date <= p_validation_end_date
199 and effective_end_date >= p_validation_start_date;
200 --
201 v_protected_row_exists boolean := FALSE;
202 --
203 begin
204 --
205 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.cant_delete_all_input_values',1);
206 --
207 <<CHECK_EACH_INPUT_VALUE>>
208 for fetched_input_value in csr_input_values LOOP
209 --
210 if NOT deletion_allowed ( fetched_input_value.input_value_id,
211 p_delete_mode,
212 p_validation_start_date,
213 p_validation_end_date,
214 p_error_if_true ) then
215 --
216 v_protected_row_exists := TRUE;
217 --
218 end if;
219 --
220 exit when v_protected_row_exists;
221 --
222 end loop check_each_input_value;
223 --
224 return v_protected_row_exists;
225 --
226 end cant_delete_all_input_values;
227 --------------------------------------------------------------------------------
228 function RUN_RESULT_VALUE_EXISTS (
229 --
230 --******************************************************************************
231 --* Returns TRUE if there are any run result values for the input value *
232 --******************************************************************************
233 --
234 -- Parameters are:
235 --
236 p_input_value_id number,
237 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
238 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
239 p_error_if_true boolean default FALSE) return boolean is
240 --
241 v_value_exists boolean := FALSE;
242 --
243 cursor csr_value is
244 select 1
245 from dual
246 where exists
247 (select /*+ INDEX(RESULT PAY_RUN_RESULTS_PK) */ 1
248 from pay_run_result_values VALUE,
249 pay_run_results RESULT,
250 pay_assignment_actions ASSIGN,
251 pay_payroll_actions PAYROLL
252 where value.run_result_id = result.run_result_id
253 and assign.assignment_action_id = result.assignment_action_id
254 and assign.payroll_action_id = payroll.payroll_action_id
255 and value.input_value_id = p_input_value_id
256 and payroll.effective_date between p_validation_start_date
257 and p_validation_end_date);
258 --
259 begin
260 --
261 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.RUN_RESULT_VALUE_EXISTS',1);
262 open csr_value;
263 fetch csr_value into g_dummy;
264 v_value_exists := csr_value%found;
265 close csr_value;
266 --
267 if v_value_exists and p_error_if_true then
268 hr_utility.set_message (801,'PAY_6212_INPVAL_NO_RR_DEL');
269 hr_utility.raise_error;
270 end if;
271 --
272 return v_value_exists;
273 --
274 end run_result_value_exists;
275 --------------------------------------------------------------------------------
276 function BACKPAY_RULE_EXISTS (
277 --
278 --******************************************************************************
279 --* Returns TRUE if there are any backpay rules applying to this input value *
280 --******************************************************************************
281 --
282 -- Parameters are:
283 --
284 p_input_value_id number,
285 p_error_if_true boolean default FALSE) return boolean is
286 --
287 v_backpay_rules_exist boolean := FALSE;
288 --
289 cursor csr_backpay_rules is
290 select 1
291 from pay_backpay_rules
292 where input_value_id = p_input_value_id;
293 --
294 begin
295 --
296 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.BACKPAY_RULE_EXISTS',1);
297 open csr_backpay_rules;
298 fetch csr_backpay_rules into g_dummy;
299 v_backpay_rules_exist := csr_backpay_rules%found;
300 close csr_backpay_rules;
301 --
302 if v_backpay_rules_exist and p_error_if_true then
303 hr_utility.set_message (801,'PAY_6215_INPVAL_NO_DEL_BP');
304 hr_utility.raise_error;
305 end if;
306 --
307 return v_backpay_rules_exist;
308 --
309 end backpay_rule_exists;
310 --------------------------------------------------------------------------------
311 function ABSENCE_EXISTS (
312 --
313 --******************************************************************************
314 --* Returns TRUE if there are any absence records applying to an input value *
315 --* after the date of its date-effective deletion *
316 --******************************************************************************
317 --
318 -- Parameters are:
319 --
320 p_input_value_id number,
321 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
322 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
323 p_error_if_true boolean default FALSE
324 --
325 ) return boolean is
326 --
327 v_orphans_exist boolean := FALSE;
328 --
329 cursor csr_orphans is
330 select 1
331 from per_absence_attendance_types
332 where input_value_id = p_input_value_id
333 and date_effective between p_validation_start_date
334 and p_validation_end_date;
335 --
336 begin
337 --
338 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.ABSENCE_EXISTS',1);
339 open csr_orphans;
340 fetch csr_orphans into g_dummy;
341 v_orphans_exist := csr_orphans%found;
342 close csr_orphans;
343 --
344 if v_orphans_exist and p_error_if_true then
345 hr_utility.set_message (801,'PAY_6214_INPVAL_NO_ABS_DEL');
346 hr_utility.raise_error;
347 end if;
348 --
349 return v_orphans_exist;
350 --
351 end absence_exists;
352 --------------------------------------------------------------------------------
353 function ELEMENT_ENTRY_VALUE_EXISTS (
354 --
355 --******************************************************************************
356 --* Returns TRUE if there are any element entry values for the input value *
357 --******************************************************************************
358 --
359 -- Parameters are:
360 --
361 p_input_value_id number,
362 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
363 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
364 p_error_if_true boolean default FALSE
365 --
366 ) return boolean is
367 --
368 v_entries_exist boolean := FALSE;
369 --
370 cursor csr_entries is
371 select 1
372 from pay_element_entry_values_f
373 where input_value_id = p_input_value_id
374 and effective_start_date <= p_validation_end_date
375 and effective_end_date >= p_validation_start_date;
376 --
377 begin
378 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.ELEMENT_ENTRY_VALUE_EXISTS',1);
379 hr_utility.trace ('p_input_value_id = '||to_char(p_input_value_id));
380 hr_utility.trace ('p_val_start = '||p_validation_start_date);
381 hr_utility.trace ('p_val_end = '||p_validation_end_date);
382 --
383 open csr_entries;
384 fetch csr_entries into g_dummy;
385 v_entries_exist := csr_entries%found;
386 close csr_entries;
387 --
388 if v_entries_exist and p_error_if_true then
389 hr_utility.set_message (801,'PAY_6211_INPVAL_NO_DEL_ENTRY');
390 hr_utility.raise_error;
391 end if;
392 --
393 return v_entries_exist;
394 --
395 end element_entry_value_exists;
396 --------------------------------------------------------------------------------
397 function RESULT_RULE_EXISTS (
398 --
399 --******************************************************************************
400 --* Returns TRUE if there are any formula result rules applying to an input *
401 --* value after the date of its date-effective deletion *
402 --******************************************************************************
403 --
404 -- Parameters are:
405 --
406 p_input_value_id number,
407 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
408 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
409 p_error_if_true boolean default FALSE
410 --
411 ) return boolean is
412 --
413 v_orphans_exist boolean := FALSE;
414 --
415 cursor csr_orphans is
416 select 1
417 from pay_formula_result_rules_f
418 where input_value_id = p_input_value_id
419 and effective_start_date <= p_validation_end_date
420 and effective_end_date >= p_validation_start_date;
421 --
422 begin
423 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.RESULT_RULE_EXISTS',1);
424 --
425 open csr_orphans;
426 fetch csr_orphans into g_dummy;
427 v_orphans_exist := csr_orphans%found;
428 close csr_orphans;
429 --
430 if v_orphans_exist and p_error_if_true then
431 hr_utility.set_message (801,'PAY_6213_INPVAL_NO_FRR_DEL');
432 hr_utility.raise_error;
433 end if;
434 --
435 return v_orphans_exist;
436 --
437 end result_rule_exists;
438 --------------------------------------------------------------------------------
439 function INPUT_VALUE_USED_AS_PAY_BASIS (
440 --
441 p_input_value_id number,
442 p_error_if_true boolean default FALSE) return boolean IS
443 --
444 --******************************************************************************
445 --* Returns TRUE if the input value is used as a pay basis *
446 --******************************************************************************
447 --
448 v_pay_basis_input_value boolean := FALSE;
449 v_dummy_number number(1);
450 --
451 cursor csr_pay_basis is
452 select 1
453 from per_pay_bases
454 where input_value_id = p_input_value_id;
455 --
456 begin
457 --
458 open csr_pay_basis;
459 fetch csr_pay_basis into v_dummy_number;
460 v_pay_basis_input_value := csr_pay_basis%found;
461 close csr_pay_basis;
462 --
463 if v_pay_basis_input_value and p_error_if_true then
464 --
465 hr_utility.set_message(801,'PAY_6965_INPVAL_NO_DEL_SB');
466 hr_utility.raise_error;
467 --
468 end if;
469 --
470 return v_pay_basis_input_value;
471 --
472 end input_value_used_as_pay_basis;
473 --------------------------------------------------------------------------------
474 function DISTRIBUTED_COST_LINK_EXISTS (
475 --
476 --******************************************************************************
477 --* Returns TRUE if there are any element links for the input value's element *
478 --* which have a costable type of Distributed, and the input value is a Pay *
479 --* Value *
480 --******************************************************************************
481 --
482 -- Parameters are:
483 --
484 p_input_value_id number,
485 p_error_if_true boolean default FALSE) return boolean is
486 --
487 v_links_exist boolean := FALSE;
488 --
489 -- Find local name for pay values
490 v_pay_value_name hr_lookups.meaning%type := hr_general.pay_value;
491 --
492 cursor csr_links is
493 select 1
494 from pay_element_links_f LINK,
495 pay_input_values_f INPUT
496 where input.input_value_id = p_input_value_id
497 and input.name = 'Pay Value'
498 and link.element_type_id = input.element_type_id
499 and link.costable_type = 'D';
500 --
501 begin
502 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DISTRIBUTED_COST_LINK_EXISTS',1);
503 --
504 open csr_links;
505 fetch csr_links into g_dummy;
506 v_links_exist := csr_links%found;
507 close csr_links;
508 --
509 if v_links_exist and p_error_if_true then
510 hr_utility.set_message (801,'PAY_6210_INPVAL_NO_LINKS_DEL');
511 hr_utility.raise_error;
512 end if;
513 --
514 return v_links_exist;
515 --
516 end distributed_cost_link_exists;
517 --------------------------------------------------------------------------------
518 function ASSIGNED_SALARY_BASE_EXISTS (
519 --
520 --******************************************************************************
521 --* Returns TRUE if there are any salary bases for the input value which are *
522 --* tied to assignments *
523 --******************************************************************************
524 --
525 -- Parameters are:
526 --
527 p_input_value_id number,
528 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
529 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
530 p_error_if_true boolean default FALSE) return boolean is
531 --
532 v_base_exists boolean := FALSE;
533 --
534 cursor csr_salary_base is
535 select 1
536 from per_pay_bases BASE
537 where base.input_value_id = p_input_value_id;
538 --
539 begin
540 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.ASSIGNED_SALARY_BASE_EXISTS',1);
541 --
542 open csr_salary_base;
543 fetch csr_salary_base into g_dummy;
544 v_base_exists := csr_salary_base%found;
545 close csr_salary_base;
546 --
547 if v_base_exists and p_error_if_true then
548 hr_utility.set_message (801,'PAY_6965_INPVAL_NO_DEL_SB');
549 hr_utility.raise_error;
550 end if;
551 --
552 return v_base_exists;
553 --
554 end assigned_salary_base_exists;
555 --------------------------------------------------------------------------------
556 function NET_CALCULATION_RULE_EXISTS (
557 --
558 --******************************************************************************
559 --* Returns TRUE if there are net calculation rules which make use of the
560 --* specified input value id.
561 --******************************************************************************
562 --
563 -- Parameters are:
564 --
565 p_input_value_id number,
566 p_error_if_true boolean default false) return boolean is
567 --
568 v_calculation_exists boolean := FALSE;
569 --
570 cursor csr_calc_rule is
571 select 1
572 from pay_net_calculation_rules
573 where input_value_id = p_input_value_id;
574 --
575 begin
576 --
577 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.NET_CALCULATION_RULES_EXIST',1);
578 --
579 open csr_calc_rule;
580 fetch csr_calc_rule into g_dummy;
581 v_calculation_exists := csr_calc_rule%found;
582 close csr_calc_rule;
583 --
584 if v_calculation_exists and p_error_if_true then
585 hr_utility.set_message (801, 'PAY_35559_INPVAL_NO_DEL_CALC');
586 hr_utility.raise_error;
587 end if;
588 --
589 return v_calculation_exists;
590 --
591 end net_calculation_rule_exists;
592 --------------------------------------------------------------------------------
593 function ACCRUAL_PLAN_EXISTS (
594 --
595 --******************************************************************************
596 --* Returns TRUE if there are any accrual plans which make use of the specified
597 --* input_value_id.
598 --******************************************************************************
599 --
600 -- Parameters are:
601 --
602 p_input_value_id number,
603 p_error_if_true boolean default FALSE) return boolean is
604 --
605 v_accrual_plan_exists boolean := FALSE;
606 --
607 cursor csr_accrual_plan is
608 select 1
609 from pay_accrual_plans
610 where p_input_value_id in ( pto_input_value_id,
611 co_input_value_id,
612 residual_input_value_id );
613 --
614 begin
615 --
616 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.ACCRUAL_PLAN_EXISTS',1);
617 --
618 open csr_accrual_plan;
619 fetch csr_accrual_plan into g_dummy;
620 v_accrual_plan_exists := csr_accrual_plan%found;
621 close csr_accrual_plan;
622 --
623 if v_accrual_plan_exists and p_error_if_true then
624 hr_utility.set_message (801,'PAY_35558_INPVAL_NO_DEL_ACCRUA');
625 hr_utility.raise_error;
626 end if;
627 --
628 return v_accrual_plan_exists;
629 --
630 end accrual_plan_exists;
631 --------------------------------------------------------------------------------
632 procedure PARENT_DELETED (
633 --
634 --******************************************************************************
635 --* Handles the case when the element type is deleted. *
636 --******************************************************************************
637 --
638 -- Parameters are:
639 --
640 -- Identifier of the element
641 p_element_type_id number,
642 --
643 -- The effective date
644 p_session_date date default trunc(sysdate),
645 --
646 -- The validation period
647 p_validation_start_date date,
648 p_validation_end_date date,
649 --
650 -- The type of Date Track deletion
651 p_delete_mode varchar2 default 'DELETE'
652 --
653 ) is
654 --
655 cursor csr_all_inputs_for_element is
656 select rowid,pay_input_values_f.*
657 from pay_input_values_f
658 where element_type_id = p_element_type_id
659 for update;
660 --
661 begin
662 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.PARENT_DELETED',1);
663 --
664 <<REMOVE_ORPHANED_ROWS>>
665 FOR fetched_input_value in csr_all_inputs_for_element LOOP
666 --
667 hr_balance_feeds.del_bf_input_value ( fetched_input_value.input_value_id,
668 p_delete_mode,
669 p_validation_start_date,
670 p_validation_end_date );
671 --
672 -- Delete input value if in ZAP mode
673 -- Delete input value if it is in the future and in DELETE mode
674 --
675 if p_delete_mode = 'ZAP'
676 or (p_delete_mode = 'DELETE'
677 and fetched_input_value.effective_start_date > p_session_date ) then
678 --
679 delete_row ( fetched_input_value.rowid,
680 fetched_input_value.input_value_id,
681 p_delete_mode,
682 p_session_date,
683 p_validation_start_date,
684 p_validation_end_date );
685 --
686 delete from hr_application_ownerships
687 where key_name = 'INPUT_VALUE_ID'
688 and key_value = fetched_input_value.input_value_id;
689 --
690 -- For date effective deletes, shut down the input value by ensuring its end
691 -- date matches that of its closed parent
692 --
693 elsif p_delete_mode = 'DELETE'
694 and p_session_date between fetched_input_value.effective_start_date
695 and fetched_input_value.effective_end_date then
696 --
697 update pay_input_values_f
698 set effective_end_date = p_session_date
699 where current of csr_all_inputs_for_element;
700 --
701 -- For delete next changes when there are no future rows for the element,
702 -- extend the input value's end date to the end of time to match the action
703 -- which will be performed on the parent
704 --
705 elsif p_delete_mode = 'DELETE_NEXT_CHANGE'
706 and p_validation_end_date = c_end_of_time then
707 --
708 -- bugfix 1507600
709 -- only update peices date effective as of session date
710 --
711 hr_utility.trace ('***** in DELETE_NEXT_CHANGE');
712 hr_utility.trace ('***** ESD>' ||
713 fetched_input_value.effective_start_date || '<');
714 hr_utility.trace ('***** EED>' ||
715 fetched_input_value.effective_end_date || '<');
716
717 -- if p_session_date >= fetched_input_value.effective_start_date and
718 -- p_session_date <= fetched_input_value.effective_end_date then
719 hr_utility.trace ('***** peice within ESD and EED');
720 hr_utility.trace ('***** ESD>' ||
721 fetched_input_value.effective_start_date || '<');
722 hr_utility.trace ('***** EED>' ||
723 fetched_input_value.effective_end_date || '<');
724 --
725 update pay_input_values_f
726 set effective_end_date = c_end_of_time
727 where --current of csr_all_inputs_for_element
728 rowid = fetched_input_value.rowid
729 and not exists
730 (select null
731 from pay_input_values_f pipv
732 where pipv.element_type_id = fetched_input_value.element_type_id
733 and pipv.input_value_id = fetched_input_value.input_value_id
734 and pipv.effective_start_date > fetched_input_value.effective_start_date);
735 -- end if;
736 --
737 end if;
738 --
739 end loop remove_orphaned_rows;
740 --
741 end parent_deleted;
742 -------------------------------------------------------------------------------
743 procedure RECREATE_DB_ITEMS (
744 --
745 --******************************************************************************
746 --* Drops and then creates new DB items for all input values belonging to an *
747 --* element. *
748 --******************************************************************************
749 --
750 -- Parameters are:
751 --
752 p_element_type_id number) is
753 --
754 cursor csr_input_values is
755 select *
756 from pay_input_values_f
757 where element_type_id = p_element_type_id
758 and generate_db_items_flag = 'Y'; -- Bug 6432304
759 --
760 begin
761 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.RECREATE_DB_ITEMS',1);
762 --
763 FOR fetched_input_value in csr_input_values LOOP
764 --
765 hrdyndbi.delete_input_value_dict (fetched_input_value.input_value_id);
766 --
767 hrdyndbi.create_input_value_dict (fetched_input_value.input_value_id,
768 fetched_input_value.effective_start_date);
769 --
770 end loop;
771 --
772 end recreate_db_items;
773 -------------------------------------------------------------------------------
774 function DELETION_ALLOWED (
775
776 --******************************************************************************
777 --* Returns TRUE if no business rules will be broken by deletion of the input *
778 --* value *
779 --******************************************************************************
780
781 -- Parameters are:
782
783 p_input_value_id number,
784 p_delete_mode varchar2,
785 p_validation_start_date date,
786 p_validation_end_date date,
787 p_error_if_true boolean default FALSE
788
789 ) return boolean is
790
791 v_deletion_allowed boolean := TRUE;
792
793 begin
794 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DELETION_ALLOWED',1);
795
796 if (p_delete_mode = 'ZAP'
797
798 and (element_entry_value_exists ( p_input_value_id,
799 p_validation_start_date,
800 p_validation_end_date,
801 p_error_if_true )
802
803 or accrual_plan_exists ( p_input_value_id,
804 p_error_if_true)
805
806 or net_calculation_rule_exists (p_input_value_id,
807 p_error_if_true)
808
809 or assigned_salary_base_exists (p_input_value_id,
810 p_validation_start_date,
811 p_validation_end_date,
812 p_error_if_true)
813
814 or run_result_value_exists ( p_input_value_id,
815 p_validation_start_date,
816 p_validation_end_date,
817 p_error_if_true )
818
819 or result_rule_exists ( p_input_value_id,
820 p_validation_start_date,
821 p_validation_end_date,
822 p_error_if_true )
823
824 or absence_exists ( p_input_value_id,
825 p_validation_start_date,
826 p_validation_end_date,
827 p_error_if_true )
828
829 or backpay_rule_exists ( p_input_value_id,
830 p_error_if_true )
831
832 or distributed_cost_link_exists ( p_input_value_id,
833 p_error_if_true)
834 )
835 or input_value_used_as_pay_basis( p_input_value_id,
836 p_error_if_true))
837
838 or (p_delete_mode = 'DELETE'
839
840 and (result_rule_exists ( p_input_value_id,
841 p_validation_start_date,
842 p_validation_end_date,
843 p_error_if_true )
844
845 or absence_exists ( p_input_value_id,
846 p_validation_start_date,
847 p_validation_end_date,
848 p_error_if_true )
849
850 ))
851 or dt_api.rows_exist(
852 p_base_table_name => 'ben_acty_base_rt_f',
853 p_base_key_column => 'input_value_id',
854 p_base_key_value => p_input_value_id,
855 p_from_date => p_validation_start_date,
856 p_to_date => p_validation_end_date
857 )
858 then
859 v_deletion_allowed := FALSE;
860
861 end if;
862
863 return v_deletion_allowed;
864
865 end deletion_allowed;
866 -----------------------------------------------------------------------------
867 function NO_OF_INPUT_VALUES (p_element_type_id number) return number is
868 --
869 --******************************************************************************
870 --* Returns the number of input values on the database for a given element *
871 --******************************************************************************
872 --
873 v_no_of_input_values number(30);
874 --
875 cursor csr_count_input_values is
876 select count(distinct input_value_id)
877 from pay_input_values_f
878 where element_type_id = p_element_type_id;
879 --
880 begin
881 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.NO_OF_INPUT_VALUES',1);
882 --
883 open csr_count_input_values;
884 fetch csr_count_input_values into v_no_of_input_values;
885 close csr_count_input_values;
886 --
887 return v_no_of_input_values;
888 --
889 end no_of_input_values;
890 --------------------------------------------------------------------------------
891 function DATE_EFFECTIVELY_UPDATED (
892 --
893 --******************************************************************************
894 --* Returns TRUE if there is more than one date effective row for the input *
895 --* value *
896 --******************************************************************************
897 --
898 -- Parameters are:
899 --
900 -- Identifier of the input value and its particular instance
901 p_input_value_id number,
902 p_rowid varchar2) return boolean is
903 --
904 cursor csr_dated_updates is
905 select 1
906 from pay_input_values_f
907 where input_value_id = p_input_value_id
908 and rowid <> p_rowid;
909 --
910 v_date_effectively_updated boolean := FALSE;
911 --
912 begin
913 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DATE_EFFECTIVELY_UPDATED',1);
914 --
915 open csr_dated_updates;
916 fetch csr_dated_updates into g_dummy;
917 v_date_effectively_updated := csr_dated_updates%found;
918 close csr_dated_updates;
919 --
920 return v_date_effectively_updated;
921 --
922 end date_effectively_updated;
923 --------------------------------------------------------------------------------
924 function NAME_NOT_UNIQUE (
925 --
926 --******************************************************************************
927 --* Returns TRUE if the input value name is NOT unique within the element type *
928 --******************************************************************************
929 --
930 -- Parameters are:
931 --
932 p_element_type_id number,
933 p_rowid varchar2 default null,
934 p_name varchar2,
935 p_error_if_true boolean default FALSE) return boolean is
936 --
937 l_ivid pay_input_values_f.input_value_id%type;
938 v_duplicate boolean := FALSE;
939 --
940 cursor csr_duplicate_name (p_ivid number) is
941 select 1
942 from pay_input_values_f_tl iv_tl,
943 pay_input_values_f iv
944 where iv.element_type_id = p_element_type_id
945 and (iv.rowid <> p_rowid or p_rowid is null)
946 and iv_tl.input_value_id = iv.input_value_id
947 and iv_tl.language = userenv('LANG')
948 and upper(iv_tl.name) = upper(p_name);
949 --
950 begin
951 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.NAME_NOT_UNIQUE',1);
952 --
953 if p_rowid is not null then
954 select input_value_id
955 into l_ivid
956 from pay_input_values_f
957 where rowid = p_rowid;
958 else
959 l_ivid := null;
960 end if;
961 --
962 open csr_duplicate_name(l_ivid);
963 fetch csr_duplicate_name into g_dummy;
964 v_duplicate := csr_duplicate_name%found;
965 close csr_duplicate_name;
966 --
967 if v_duplicate and p_error_if_true then
968 hr_utility.set_message (801,'PAY_6168_INPVAL_DUP_NAME');
969 hr_utility.raise_error;
970 end if;
971 --
972 return v_duplicate;
973 --
974 end name_not_unique;
975 --------------------------------------------------------------------------------
976 function MANDATORY_IN_FUTURE (
977 --
978 --******************************************************************************
979 --* Returns TRUE if the input value is mandatory in any future date effective
980 --* row. *
981 --******************************************************************************
982 --
983 -- Parameters are:
984 --
985 p_input_value_id number,
986 p_session_date date default trunc(sysdate),
987 p_error_if_true boolean default FALSE)
988 --
989 return boolean is
990 --
991 v_mandatory_in_future boolean;
992 --
993 cursor csr_mandatory_flag is
994 select 1
995 from pay_input_values_f
996 where input_value_id = p_input_value_id
997 and mandatory_flag = 'Y'
998 and effective_start_date > p_session_date;
999 --
1000 begin
1001 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.MANDATORY_IN_FUTURE',1);
1002 open csr_mandatory_flag;
1003 fetch csr_mandatory_flag into g_dummy;
1004 v_mandatory_in_future := csr_mandatory_flag%found;
1005 close csr_mandatory_flag;
1006 --
1007 if p_error_if_true and v_mandatory_in_future then
1008 hr_utility.set_message(801,'PAY_6179_INPVAL_MAND_NO_CHANGE');
1009 hr_utility.raise_error;
1010 end if;
1011 --
1012 return v_mandatory_in_future;
1013 --
1014 end mandatory_in_future;
1015 --------------------------------------------------------------------------------
1016 procedure INSERT_ROW (
1017 --
1018 --******************************************************************************
1019 --* Handles the insertion of an input value into the base table and ensures *
1020 --* that any cascaded actions are carried out
1021 --******************************************************************************
1022 --
1023 -- Parameters are:
1024 --
1025 -- All base table columns
1026 p_effective_start_date date default trunc (sysdate),
1027 p_effective_end_date date default to_date ('31/12/4712',
1028 'DD/MM/YYYY'),
1029 p_element_type_id number,
1030 p_lookup_type varchar2 default null,
1031 p_business_group_id number default null,
1032 p_legislation_code varchar2 default null,
1033 p_formula_id number default null,
1034 p_display_sequence number default 1,
1035 p_generate_db_items_flag varchar2 default 'Y',
1036 p_hot_default_flag varchar2 default 'N',
1037 p_mandatory_flag varchar2 default 'N',
1038
1039 -- change 115.12 - make p_name default to null
1040 --p_name varchar2 default 'Pay Value',
1041 p_name varchar2 default null,
1042 -- change 115.12 - make p_base_name a mandatory parameter
1043 --p_base_name varchar2 default 'Pay Value',
1044 p_base_name varchar2,
1045
1046 p_uom varchar2 default 'M',
1047 p_default_value varchar2 default null,
1048 p_legislation_subgroup varchar2 default null,
1049 p_max_value varchar2 default null,
1050 p_min_value varchar2 default null,
1051 p_warning_or_error varchar2 default null,
1052 --
1053 -- Attributes of the parent element type which will affect
1054 -- subsequent actions
1055 p_classification_id number default null,
1056 --
1057 -- Enhancement 2793978
1058 p_value_set_id number default null,
1059 --
1060 -- The identifiers generated by the system for return to the form
1061 p_input_value_id in out nocopy number,
1062 p_rowid in out nocopy varchar2
1063 --
1064 ) is
1065 --
1066 cursor csr_next_id is
1067 select pay_input_values_s.nextval
1068 from sys.dual;
1069 --
1070 cursor csr_input_value_rowid is
1071 --
1072 /* We need to know the value of the newly created
1073 rowid so that forms does not have to re-query */
1074 --
1075 select rowid
1076 from pay_input_values_f
1077 where input_value_id = p_input_value_id
1078 and effective_start_date = p_effective_start_date
1079 and effective_end_date = p_effective_end_date;
1080 --
1081 cursor c_language (c_input_value_id number) is
1082 select L.LANGUAGE_CODE
1083 from FND_LANGUAGES L
1084 where L.INSTALLED_FLAG in ('I', 'B')
1085 and not exists
1086 (select NULL
1087 from PAY_INPUT_VALUES_F_TL T
1088 where T.INPUT_VALUE_ID = c_input_value_id
1089 and T.LANGUAGE = L.LANGUAGE_CODE);
1090
1091 --
1092 -- Each system may have a different name for a pay value
1093 v_pay_value_name varchar2(255) := hr_general.pay_value;
1094 -- change 115.12
1095 l_name varchar2(80);
1096 --
1097 l_tl_name varchar2(255);
1098 --
1099 l_check_latest_balances boolean;
1100 --
1101 begin
1102 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',1);
1103 --
1104
1105 -- change 115.12 - if p_name has not been specified then default it to
1106 -- p_base_name
1107 l_name := p_name;
1108 if l_name is null then
1109 l_name := p_base_name;
1110 end if;
1111
1112 open csr_next_id;
1113 fetch csr_next_id into p_input_value_id;
1114 close csr_next_id;
1115 --
1116 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',2);
1117 --
1118 if no_of_input_values (p_element_type_id) >= 15 then
1119
1120 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',3);
1121
1122 hr_utility.set_message (801, 'HR_7124_INPVAL_MAX_ENTRIES');
1123 hr_utility.raise_error;
1124
1125 end if;
1126
1127 insert into pay_input_values_f (
1128 --
1129 INPUT_VALUE_ID,
1130 EFFECTIVE_START_DATE,
1131 EFFECTIVE_END_DATE,
1132 ELEMENT_TYPE_ID,
1133 LOOKUP_TYPE,
1134 BUSINESS_GROUP_ID,
1135 LEGISLATION_CODE,
1136 FORMULA_ID,
1137 DISPLAY_SEQUENCE,
1138 GENERATE_DB_ITEMS_FLAG,
1139 HOT_DEFAULT_FLAG,
1140 MANDATORY_FLAG,
1141 NAME,
1142 UOM,
1143 DEFAULT_VALUE,
1144 LEGISLATION_SUBGROUP,
1145 MAX_VALUE,
1146 MIN_VALUE,
1147 WARNING_OR_ERROR,
1148 -- Enhancement 2793978
1149 VALUE_SET_ID,
1150 --
1151 last_update_date,
1152 last_updated_by,
1153 last_update_login,
1154 creation_date,
1155 created_by)
1156 --
1157 values (
1158 --
1159 p_input_value_id,
1160 p_effective_start_date,
1161 p_effective_end_date,
1162 p_element_type_id,
1163 p_lookup_type,
1164 p_business_group_id,
1165 p_legislation_code,
1166 p_formula_id,
1167 p_display_sequence,
1168 p_generate_db_items_flag,
1169 p_hot_default_flag,
1170 p_mandatory_flag,
1171 -- If the input value is a pay value, translate it to local language
1172 -- DECODE(UPPER(p_name),
1173 -- 'PAY VALUE', v_pay_value_name,
1174 -- p_name
1175 -- ),
1176 -- --
1177 -- only insert the base value into the _F table
1178 p_base_name,
1179 -- --
1180 p_uom,
1181 p_default_value,
1182 p_legislation_subgroup,
1183 p_max_value,
1184 p_min_value,
1185 p_warning_or_error,
1186 -- Enhancement 2793978
1187 p_value_set_id,
1188 --
1189 sysdate,
1190 c_user_id,
1191 c_login_id,
1192 sysdate,
1193 c_user_id);
1194 --
1195 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',4);
1196 --
1197 -- ***********************************************************************
1198 -- Insert into MLS table (TL)
1199 --
1200 if (upper(l_name) = 'PAY VALUE') then
1201 for c_lang_rec in c_language(P_INPUT_VALUE_ID) loop
1202
1203 begin
1204 select meaning
1205 into l_tl_name
1206 from fnd_lookup_values
1207 where lookup_type = 'NAME_TRANSLATIONS'
1208 and lookup_code = 'PAY VALUE'
1209 and language = c_lang_rec.language_code;
1210 exception
1211 when no_data_found then
1212 l_tl_name := l_name;
1213 end;
1214
1215 insert into PAY_INPUT_VALUES_F_TL (
1216 INPUT_VALUE_ID,
1217 NAME,
1218 LAST_UPDATE_DATE,
1219 LAST_UPDATED_BY,
1220 LAST_UPDATE_LOGIN,
1221 CREATED_BY,
1222 CREATION_DATE,
1223 LANGUAGE,
1224 SOURCE_LANG
1225 ) select
1226 P_INPUT_VALUE_ID,
1227 l_tl_name,
1228 sysdate,
1229 c_user_id,
1230 c_user_id,
1231 c_login_id,
1232 sysdate,
1233 c_lang_rec.language_code,
1234 userenv('LANG')
1235 from dual;
1236
1237 end loop;
1238 else
1239
1240 insert into PAY_INPUT_VALUES_F_TL (
1241 INPUT_VALUE_ID,
1242 NAME,
1243 LAST_UPDATE_DATE,
1244 LAST_UPDATED_BY,
1245 LAST_UPDATE_LOGIN,
1246 CREATED_BY,
1247 CREATION_DATE,
1248 LANGUAGE,
1249 SOURCE_LANG
1250 ) select
1251 P_INPUT_VALUE_ID,
1252 l_name,
1253 sysdate,
1254 c_user_id,
1255 c_user_id,
1256 c_login_id,
1257 sysdate,
1258 L.LANGUAGE_CODE,
1259 userenv('LANG')
1260 from FND_LANGUAGES L
1261 where L.INSTALLED_FLAG in ('I', 'B')
1262 and not exists
1263 (select NULL
1264 from PAY_INPUT_VALUES_F_TL T
1265 where T.INPUT_VALUE_ID = P_INPUT_VALUE_ID
1266 and T.LANGUAGE = L.LANGUAGE_CODE);
1267
1268 end if;
1269
1270 --
1271 -- ***********************************************************************
1272 --
1273 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',5);
1274 --
1275 open csr_input_value_rowid;
1276 fetch csr_input_value_rowid into p_rowid;
1277 --
1278 if csr_input_value_rowid%notfound then
1279 close csr_input_value_rowid;
1280 raise no_data_found;
1281 else
1282 --
1283 -- Create link input values for existing links
1284 --
1285 pay_link_input_values_pkg.create_link_input_value (
1286
1287 p_input_value_id,
1288 p_element_type_id,
1289 p_effective_start_date,
1290 p_effective_end_date,
1291 -- change 115.12
1292 l_name,
1293 p_hot_default_flag,
1294 p_default_value,
1295 p_min_value,
1296 p_max_value,
1297 p_warning_or_error);
1298
1299 -- Create balance feeds for pay values
1300 --
1301 -- change 115.12
1302 if (l_name = 'Pay Value' -- the default
1303 or upper (l_name) = upper (v_pay_value_name)) then
1304 l_check_latest_balances := HRASSACT.CHECK_LATEST_BALANCES;
1305 HRASSACT.CHECK_LATEST_BALANCES := FALSE;
1306 hr_balance_feeds.ins_bf_pay_value ( p_input_value_id );
1307 HRASSACT.CHECK_LATEST_BALANCES := l_check_latest_balances;
1308 end if;
1309 --
1310 -- Create DB item for the input value
1311 if p_generate_db_items_flag = 'Y' then
1312 hrdyndbi.create_input_value_dict ( p_input_value_id,
1313 p_effective_start_date );
1314 end if;
1315 --
1316 -- Create application ownership for startup pay value
1317 if upper (l_name) = upper (v_pay_value_name)
1318 and p_legislation_code is not null then
1319 --
1320 -- The 'not exists' clause is used to ensure that duplicate rows are not
1321 -- entered. This could arise because the forms startup code also handles
1322 -- application ownerships where a user enters a pay value on the form, but
1323 -- this code is intended to handle third party insertion from the element
1324 --
1325 insert into hr_application_ownerships
1326 (key_name,
1327 key_value,
1328 product_name)
1329 select 'INPUT_VALUE_ID',
1330 p_input_value_id,
1331 ao.product_name
1332 from hr_application_ownerships ao
1333 where ao.key_name = 'ELEMENT_TYPE_ID'
1334 and ao.key_value = p_element_type_id
1335 and not exists (select 'INPUT_VALUE_ID',
1336 p_input_value_id,
1337 ao.product_name
1338 from hr_application_ownerships ao
1339 where ao.key_name = 'ELEMENT_TYPE_ID'
1340 and ao.key_value = p_element_type_id);
1341 --
1342 end if;
1343 --
1344 end if;
1345 close csr_input_value_rowid;
1346 --
1347 --
1348 end insert_row;
1349 ---------------------------------------------------------------------------
1350 procedure UPDATE_ROW(
1351 --
1352 --******************************************************************************
1353 --* Handles the updating of the base table for the form which is based on a *
1354 --* non-updatable view *
1355 --******************************************************************************
1356 --
1357 -- Parameters are:
1358 --
1359 -- All base table columns
1360 p_ROWID VARCHAR2,
1361 p_INPUT_VALUE_ID NUMBER,
1362 p_EFFECTIVE_START_DATE DATE,
1363 p_EFFECTIVE_END_DATE DATE,
1364 p_ELEMENT_TYPE_ID NUMBER,
1365 p_LOOKUP_TYPE VARCHAR2,
1366 p_BUSINESS_GROUP_ID NUMBER,
1367 p_LEGISLATION_CODE VARCHAR2,
1368 p_FORMULA_ID NUMBER,
1369 p_DISPLAY_SEQUENCE NUMBER,
1370 p_GENERATE_DB_ITEMS_FLAG VARCHAR2,
1371 p_HOT_DEFAULT_FLAG VARCHAR2,
1372 p_MANDATORY_FLAG VARCHAR2,
1373 p_NAME VARCHAR2,
1374 p_UOM VARCHAR2,
1375 p_DEFAULT_VALUE VARCHAR2,
1376 p_LEGISLATION_SUBGROUP VARCHAR2,
1377 p_MAX_VALUE VARCHAR2,
1378 p_MIN_VALUE VARCHAR2,
1379 p_WARNING_OR_ERROR VARCHAR2,
1380 -- Enhancement 2793978
1381 p_value_set_id number default null,
1382 --
1383 p_recreate_db_items varchar2,
1384 p_base_name varchar2
1385 --
1386 ) is
1387 --
1388 begin
1389 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.update_row',1);
1390 --
1391 update pay_input_values_f
1392 set INPUT_VALUE_ID = p_INPUT_VALUE_ID,
1393 EFFECTIVE_START_DATE = p_EFFECTIVE_START_DATE,
1394 EFFECTIVE_END_DATE = p_EFFECTIVE_END_DATE,
1395 ELEMENT_TYPE_ID = p_ELEMENT_TYPE_ID,
1396 LOOKUP_TYPE = p_LOOKUP_TYPE,
1397 BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID,
1398 LEGISLATION_CODE = p_LEGISLATION_CODE,
1399 FORMULA_ID = p_FORMULA_ID,
1400 DISPLAY_SEQUENCE = p_DISPLAY_SEQUENCE,
1401 GENERATE_DB_ITEMS_FLAG = p_GENERATE_DB_ITEMS_FLAG,
1402 HOT_DEFAULT_FLAG = p_HOT_DEFAULT_FLAG,
1403 MANDATORY_FLAG = p_MANDATORY_FLAG,
1404 -- --
1405 NAME = p_base_NAME,
1406 -- --
1407 UOM = p_UOM,
1408 DEFAULT_VALUE = p_DEFAULT_VALUE,
1409 LEGISLATION_SUBGROUP = p_LEGISLATION_SUBGROUP,
1410 MAX_VALUE = p_MAX_VALUE,
1411 MIN_VALUE = p_MIN_VALUE,
1412 WARNING_OR_ERROR = p_WARNING_OR_ERROR,
1413 -- Enhancement 2793978
1414 VALUE_SET_ID = p_VALUE_SET_ID,
1415 --
1416 last_update_date = sysdate,
1417 last_updated_by = c_user_id,
1418 last_update_login = c_login_id
1419 where rowid = p_rowid;
1420 --
1421 if sql%notfound then
1422 raise no_data_found;
1423 end if;
1424 --
1425 --
1426 -- ************************************************************************
1427 -- update MLS table (TL)
1428 --
1429 update PAY_INPUT_VALUES_F_TL
1430 set
1431 NAME = P_NAME,
1432 last_update_date = sysdate,
1433 last_updated_by = c_user_id,
1434 last_update_login = c_login_id,
1435 SOURCE_LANG = userenv('LANG')
1436 where INPUT_VALUE_ID = P_INPUT_VALUE_ID
1437 and userenv('LANG') = LANGUAGE ; -- bug 6125295
1438 --
1439 if sql%notfound then -- trap system errors during update
1440 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1441 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.UPDATE_TL_ROW');
1442 hr_utility.raise_error;
1443 end if;
1444 --
1445 -- ************************************************************************
1446 --
1447 if p_recreate_db_items = 'Y' then
1448 --
1449 -- Recreate DB items
1450 hrdyndbi.delete_input_value_dict (p_input_value_id);
1451 hrdyndbi.create_input_value_dict (p_input_value_id,p_effective_start_date);
1452 --
1453 end if;
1454 --
1455 end update_row;
1456 ---------------------------------------------------------------------------
1457 procedure DELETE_ROW (
1458 --
1459 --******************************************************************************
1460 --* Handles deletion from the base table for the form which is based on a *
1461 --* non-updatable view, and maintains data integrity *
1462 --******************************************************************************
1463 --
1464 -- Parameters are:
1465 --
1466 -- Identifier of the row to be deleted
1467 p_rowid varchar2,
1468 p_input_value_id number,
1469 --
1470 -- Date Track delete mode
1471 p_delete_mode varchar2,
1472 --
1473 -- Validation period
1474 p_session_date date,
1475 p_validation_start_date date
1476 default to_date ('01/01/0001','DD/MM/YYYY'),
1477 p_validation_end_date date
1478 default to_date ('31/12/4712','DD/MM/YYYY')
1479 ) is
1480 --
1481 begin
1482 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DELETE_ROW',1);
1483 --
1484 if deletion_allowed ( p_input_value_id,
1485 p_delete_mode,
1486 p_validation_start_date,
1487 p_validation_end_date ) then
1488 --
1489 hr_balance_feeds.del_bf_input_value ( p_input_value_id,
1490 p_delete_mode,
1491 p_validation_start_date,
1492 p_validation_end_date );
1493 --
1494 pay_link_input_values_pkg.parent_deleted ( p_input_value_id,
1495 p_session_date,
1496 p_validation_start_date,
1497 p_validation_end_date,
1498 p_delete_mode,
1499 'PAY_INPUT_VALUES_F' );
1500 --
1501 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DELETE_ROW',2);
1502 --
1503 -- delete DB items
1504 hrdyndbi.delete_input_value_dict (p_input_value_id);
1505 --
1506 delete from pay_input_values_f
1507 where rowid = p_rowid;
1508 --
1509 if sql%notfound then
1510 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1511 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.DELETE_ROW');
1512 hr_utility.raise_error;
1513 end if;
1514 --
1515 --
1516 -- ****************************************************************************
1517 -- delete from MLS table (TL)
1518 --
1519
1520 --
1521 -- bugfix 1229606
1522 -- only delete data from the translated tables if the date track mode is ZAP,
1523 -- for all other date track modes the data should remain untouched
1524 --
1525 if p_delete_mode = 'ZAP' then
1526
1527 delete from PAY_INPUT_VALUES_F_TL
1528 where INPUT_VALUE_ID = P_INPUT_VALUE_ID;
1529 --
1530 if sql%notfound then -- trap system errors during deletion
1531 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1532 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.DELETE_TL_ROW');
1533 hr_utility.raise_error;
1534 end if;
1535
1536 end if;
1537 --
1538 -- ******************************************************************************
1539 --
1540 end if;
1541 --
1542 end delete_row;
1543 ---------------------------------------------------------------------------
1544 procedure LOCK_ROW (
1545 p_rowid VARCHAR2,
1546 p_INPUT_VALUE_ID NUMBER,
1547 p_EFFECTIVE_START_DATE DATE,
1548 p_EFFECTIVE_END_DATE DATE,
1549 p_ELEMENT_TYPE_ID NUMBER,
1550 p_LOOKUP_TYPE VARCHAR2,
1551 p_BUSINESS_GROUP_ID NUMBER,
1552 p_LEGISLATION_CODE VARCHAR2,
1553 p_FORMULA_ID NUMBER,
1554 p_DISPLAY_SEQUENCE NUMBER,
1555 p_GENERATE_DB_ITEMS_FLAG VARCHAR2,
1556 p_HOT_DEFAULT_FLAG VARCHAR2,
1557 p_MANDATORY_FLAG VARCHAR2,
1558 -- p_NAME VARCHAR2,
1559 -- --
1560 p_BASE_NAME VARCHAR2,
1561 -- --
1562 p_UOM VARCHAR2,
1563 p_DEFAULT_VALUE VARCHAR2,
1564 p_LEGISLATION_SUBGROUP VARCHAR2,
1565 p_MAX_VALUE VARCHAR2,
1566 p_MIN_VALUE VARCHAR2,
1567 p_WARNING_OR_ERROR VARCHAR2,
1568 -- Enhancement 2793978
1569 p_value_set_id NUMBER default null
1570 --
1571 ) is
1572 --
1573 cursor csr_lock_input_value is
1574 select *
1575 from pay_input_values_f
1576 where rowid = p_rowid
1577 for update of input_value_id nowait;
1578 --
1579 v_locked_row csr_lock_input_value%rowtype;
1580 -- MLS Row counter
1581 v_mls_count NUMBER:=0;
1582 --
1583 -- *****************************************************************
1584 -- cursor for MLS
1585 --
1586 cursor csr_lock_input_value_tl is
1587 select
1588 NAME,
1589 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
1590 from PAY_INPUT_VALUES_F_TL
1591 where INPUT_VALUE_ID = P_INPUT_VALUE_ID
1592 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1593 for update of INPUT_VALUE_ID nowait;
1594 --
1595 -- ****************************************************************
1596 --
1597 begin
1598 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.lock_row',1);
1599 --
1600 open csr_lock_input_value;
1601 fetch csr_lock_input_value into v_locked_row;
1602 if csr_lock_input_value%notfound then
1603 close csr_lock_input_value;
1604 raise no_data_found;
1605 end if;
1606 close csr_lock_input_value;
1607 --
1608 /** sbilling **/
1609 -- removed explicit lock of _TL table,
1610 -- the MLS strategy requires that the base table is locked before update of the
1611 -- _TL table can take place,
1612 -- which implies it is not necessary to lock both tables.
1613 --
1614 -- ************************************************************************
1615 -- code for MLS
1616 --
1617 -- for tlinfo in csr_lock_input_value_tl LOOP
1618 -- v_mls_count := v_mls_count+1;
1619 -- if (tlinfo.BASELANG = 'Y') then
1620 -- if ( (tlinfo.NAME = P_NAME)
1621 -- ) then
1622 -- null;
1623 -- else
1624 -- hr_utility.set_message ('FND','FORM_RECORD_CHANGED');
1625 -- hr_utility.raise_error;
1626 -- end if;
1627 -- end if;
1628 -- end loop;
1629 --
1630 --
1631 -- if (v_mls_count=0) then -- Trap system errors
1632 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1633 -- hr_utility.set_message_token ('PROCEDURE','PAY_INPUT_VALUES_PKG.LOCK_TL_ROW');
1634 -- end if;
1635 --
1636 -- ************************************************************************
1637 --
1638 --
1639 if ((V_LOCKED_ROW.INPUT_VALUE_ID = p_INPUT_VALUE_ID) OR (V_LOCKED_ROW.INPUT_VALUE_ID is null and p_INPUT_VALUE_ID is null))
1640 and ((V_LOCKED_ROW.EFFECTIVE_START_DATE = p_EFFECTIVE_START_DATE) OR (V_LOCKED_ROW.EFFECTIVE_START_DATE is null and p_EFFECTIVE_START_DATE is null))
1641 and ((V_LOCKED_ROW.EFFECTIVE_END_DATE = p_EFFECTIVE_END_DATE) OR (V_LOCKED_ROW.EFFECTIVE_END_DATE is null and p_EFFECTIVE_END_DATE is null))
1642 and ((V_LOCKED_ROW.ELEMENT_TYPE_ID = p_ELEMENT_TYPE_ID) OR (V_LOCKED_ROW.ELEMENT_TYPE_ID is null and p_ELEMENT_TYPE_ID is null))
1643 and ((V_LOCKED_ROW.LOOKUP_TYPE = p_LOOKUP_TYPE) OR (V_LOCKED_ROW.LOOKUP_TYPE is null and p_LOOKUP_TYPE is null))
1644 and ((V_LOCKED_ROW.BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID) OR (V_LOCKED_ROW.BUSINESS_GROUP_ID is null and p_BUSINESS_GROUP_ID is null))
1645 and ((V_LOCKED_ROW.LEGISLATION_CODE = p_LEGISLATION_CODE) OR (V_LOCKED_ROW.LEGISLATION_CODE is null and p_LEGISLATION_CODE is null))
1646 and ((V_LOCKED_ROW.FORMULA_ID = p_FORMULA_ID) OR (V_LOCKED_ROW.FORMULA_ID is null and p_FORMULA_ID is null))
1647 and ((V_LOCKED_ROW.DISPLAY_SEQUENCE = p_DISPLAY_SEQUENCE) OR (V_LOCKED_ROW.DISPLAY_SEQUENCE is null and p_DISPLAY_SEQUENCE is null))
1648 and ((V_LOCKED_ROW.GENERATE_DB_ITEMS_FLAG= p_GENERATE_DB_ITEMS_FLAG) OR (V_LOCKED_ROW.GENERATE_DB_ITEMS_FLAG is null and p_GENERATE_DB_ITEMS_FLAG is null))
1649 and ((V_LOCKED_ROW.HOT_DEFAULT_FLAG = p_HOT_DEFAULT_FLAG) OR (V_LOCKED_ROW.HOT_DEFAULT_FLAG is null and p_HOT_DEFAULT_FLAG is null))
1650 and ((V_LOCKED_ROW.MANDATORY_FLAG = p_MANDATORY_FLAG) OR (V_LOCKED_ROW.MANDATORY_FLAG is null and p_MANDATORY_FLAG is null))
1651 --and ((V_LOCKED_ROW.NAME = p_NAME) OR (V_LOCKED_ROW.NAME is null and p_NAME is null))
1652 -- --
1653 and ((V_LOCKED_ROW.NAME = p_BASE_NAME) OR (V_LOCKED_ROW.NAME is null and p_BASE_NAME is null))
1654 -- --
1655 and ((V_LOCKED_ROW.UOM = p_UOM) OR (V_LOCKED_ROW.UOM is null and p_UOM is null))
1656 and ((V_LOCKED_ROW.DEFAULT_VALUE = p_DEFAULT_VALUE) OR (V_LOCKED_ROW.DEFAULT_VALUE is null and p_DEFAULT_VALUE is null))
1657 and ((V_LOCKED_ROW.LEGISLATION_SUBGROUP = p_LEGISLATION_SUBGROUP) OR (V_LOCKED_ROW.LEGISLATION_SUBGROUP is null and p_LEGISLATION_SUBGROUP is null))
1658 and ((V_LOCKED_ROW.MAX_VALUE = p_MAX_VALUE) OR (V_LOCKED_ROW.MAX_VALUE is null and p_MAX_VALUE is null))
1659 and ((V_LOCKED_ROW.MIN_VALUE = p_MIN_VALUE) OR (V_LOCKED_ROW.MIN_VALUE is null and p_MIN_VALUE is null))
1660 and ((V_LOCKED_ROW.WARNING_OR_ERROR = p_WARNING_OR_ERROR) OR (V_LOCKED_ROW.WARNING_OR_ERROR is null and p_WARNING_OR_ERROR is null)) then
1661 return;
1662 else
1663 hr_utility.set_message ('FND','FORM_RECORD_CHANGED');
1664 hr_utility.raise_error;
1665 end if;
1666 --
1667 end lock_row;
1668 ---------------------------------------------------------------------------
1669 procedure ADD_LANGUAGE
1670 is
1671 begin
1672 delete from PAY_INPUT_VALUES_F_TL T
1673 where not exists
1674 (select NULL
1675 from PAY_INPUT_VALUES_F B
1676 where B.INPUT_VALUE_ID = T.INPUT_VALUE_ID
1677 );
1678
1679 update PAY_INPUT_VALUES_F_TL T set (
1680 NAME
1681 ) = (select
1682 B.NAME
1683 from PAY_INPUT_VALUES_F_TL B
1684 where B.INPUT_VALUE_ID = T.INPUT_VALUE_ID
1685 and B.LANGUAGE = T.SOURCE_LANG)
1686 where (
1687 T.INPUT_VALUE_ID,
1688 T.LANGUAGE
1689 ) in (select
1690 SUBT.INPUT_VALUE_ID,
1691 SUBT.LANGUAGE
1692 from PAY_INPUT_VALUES_F_TL SUBB, PAY_INPUT_VALUES_F_TL SUBT
1693 where SUBB.INPUT_VALUE_ID = SUBT.INPUT_VALUE_ID
1694 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1695 and (SUBB.NAME <> SUBT.NAME
1696 ));
1697
1698 insert into PAY_INPUT_VALUES_F_TL (
1699 INPUT_VALUE_ID,
1700 NAME,
1701 LAST_UPDATE_DATE,
1702 LAST_UPDATED_BY,
1703 LAST_UPDATE_LOGIN,
1704 CREATED_BY,
1705 CREATION_DATE,
1706 LANGUAGE,
1707 SOURCE_LANG
1708 ) select
1709 B.INPUT_VALUE_ID,
1710 B.NAME,
1711 B.LAST_UPDATE_DATE,
1712 B.LAST_UPDATED_BY,
1713 B.LAST_UPDATE_LOGIN,
1714 B.CREATED_BY,
1715 B.CREATION_DATE,
1716 L.LANGUAGE_CODE,
1717 B.SOURCE_LANG
1718 from PAY_INPUT_VALUES_F_TL B, FND_LANGUAGES L
1719 where L.INSTALLED_FLAG in ('I', 'B')
1720 and B.LANGUAGE = userenv('LANG')
1721 and not exists
1722 (select NULL
1723 from PAY_INPUT_VALUES_F_TL T
1724 where T.INPUT_VALUE_ID = B.INPUT_VALUE_ID
1725 and T.LANGUAGE = L.LANGUAGE_CODE);
1726 end ADD_LANGUAGE;
1727 -----------------------------------------------------------
1728 procedure unique_chk(
1729 X_I_NAME in varchar2,
1730 X_I_LEGISLATION_CODE in varchar2,
1731 X_I_EFFECTIVE_START_DATE in date,
1732 X_I_EFFECTIVE_END_DATE in date,
1733 X_I_E_ELEMENT_NAME in varchar2,
1734 X_I_E_LEGISLATION_CODE in varchar2,
1735 X_I_E_EFFECTIVE_START_DATE in date,
1736 X_I_E_EFFECTIVE_END_DATE in date )
1737 is
1738 result varchar2(255);
1739 Begin
1740 SELECT count(*) INTO result
1741 FROM pay_element_types_f E, pay_input_values_F I
1742 WHERE I.ELEMENT_TYPE_ID = E.ELEMENT_TYPE_ID
1743 and nvl(E.ELEMENT_NAME,'~null~') = nvl(X_I_E_ELEMENT_NAME,'~null~')
1744 and nvl(E.LEGISLATION_CODE,'~null~') = nvl(X_I_E_LEGISLATION_CODE,'~null~')
1745 and E.EFFECTIVE_START_DATE = X_I_E_EFFECTIVE_START_DATE
1746 and E.EFFECTIVE_end_DATE = X_I_E_EFFECTIVE_END_DATE
1747 and X_I_E_EFFECTIVE_START_DATE is not NULL
1748 and X_I_E_EFFECTIVE_END_DATE is not NULL
1749 and E.BUSINESS_GROUP_ID is NULL
1750 and nvl(I.NAME,'~null~') = nvl(X_I_NAME,'~null~')
1751 and nvl(I.LEGISLATION_CODE,'~null~') = nvl(X_I_LEGISLATION_CODE,'~null~')
1752 and I.EFFECTIVE_START_DATE = X_I_EFFECTIVE_START_DATE
1753 and I.EFFECTIVE_end_DATE = X_I_EFFECTIVE_END_DATE
1754 and X_I_EFFECTIVE_START_DATE is not NULL
1755 and X_I_EFFECTIVE_END_DATE is not NULL
1756 and I.BUSINESS_GROUP_ID is NULL;
1757 --
1758 IF (result>1) THEN
1759 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1760 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.UNIQUE_CHK');
1761 hr_utility.set_message_token('STEP','1');
1762 hr_utility.raise_error;
1763 END IF;
1764 EXCEPTION
1765 when NO_DATA_FOUND then
1766 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1767 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.UNIQUE_CHK');
1768 hr_utility.set_message_token('STEP','1');
1769 hr_utility.raise_error;
1770 end unique_chk;
1771 --------------------------------------------------------------------------------
1772 procedure TRANSLATE_ROW (
1773 X_I_NAME in varchar2,
1774 X_I_LEGISLATION_CODE in varchar2,
1775 X_I_EFFECTIVE_START_DATE in date,
1776 X_I_EFFECTIVE_END_DATE in date,
1777 X_I_E_ELEMENT_NAME in varchar2,
1778 X_I_E_LEGISLATION_CODE in varchar2,
1779 X_I_E_EFFECTIVE_START_DATE in date,
1780 X_I_E_EFFECTIVE_END_DATE in date,
1781 X_NAME in varchar2,
1782 X_OWNER in varchar2 ) is
1783 --
1784 -- Fetch the input_value_id. This used to be a sub-query in the update
1785 -- statement.
1786 --
1787 cursor csr_ipv_id is
1788 select i.input_value_id
1789 from pay_element_types_f e
1790 , pay_input_values_f i
1791 WHERE i.element_type_id = e.element_type_id
1792 and nvl(e.element_name,'~null~') = nvl(x_i_e_element_name,'~null~')
1793 and nvl(e.legislation_code,'~null~') = nvl(x_i_e_legislation_code,'~null~')
1794 and e.effective_start_date = x_i_e_effective_start_date
1795 and e.effective_END_date = x_i_e_effective_end_date
1796 and x_i_e_effective_start_date is not null
1797 and x_i_e_effective_end_date is not null
1798 and e.business_group_id is null
1799 and nvl(i.name,'~null~') = nvl(x_i_name,'~null~')
1800 and nvl(i.legislation_code,'~null~') = nvl(x_i_legislation_code,'~null~')
1801 and i.effective_start_date = x_i_effective_start_date
1802 and i.effective_end_date = x_i_effective_end_date
1803 and x_i_effective_start_date is not null
1804 and x_i_effective_end_date is not null
1805 and i.business_group_id is null
1806 ;
1807 --
1808 -- Fetch information for the _TL rows that will be affected by the update.
1809 --
1810 cursor csr_tl_info
1811 (p_input_value_id in number
1812 ,p_language in varchar2
1813 ) is
1814 select name
1815 , language
1816 from pay_input_values_f_tl
1817 where input_value_id = p_input_value_id
1818 and p_language in (language, source_lang)
1819 ;
1820 --
1821 l_input_value_id number;
1822 l_found boolean;
1823 i binary_integer := 1;
1824 l_langs dbms_sql.varchar2s;
1825 l_lang varchar2(100);
1826 begin
1827 --
1828 -- Fetch the input_value_id.
1829 --
1830 open csr_ipv_id;
1831 fetch csr_ipv_id
1832 into l_input_value_id
1833 ;
1834 l_found := csr_ipv_id%found;
1835 close csr_ipv_id;
1836
1837
1838 l_lang := userenv('LANG');
1839
1840 if l_found then
1841 --
1842 -- Check if database item translations are supported.
1843 --
1844 if ff_dbi_utils_pkg.translations_supported
1845 (p_legislation_code => x_i_legislation_code
1846 ) then
1847 for crec in csr_tl_info
1848 (p_input_value_id => l_input_value_id
1849 ,p_language => l_lang
1850 ) loop
1851 if upper(crec.name) <> upper(x_name) then
1852 l_langs(i) := crec.language;
1853 i := i + 1;
1854 end if;
1855 end loop;
1856 end if;
1857
1858 UPDATE pay_input_values_f_tl
1859 SET name = nvl(x_name,name),
1860 last_update_date = sysdate,
1861 last_updated_by = decode(x_owner,'SEED',1,0),
1862 last_update_login = 0,
1863 source_lang = userenv('LANG')
1864 WHERE userenv('LANG') IN (language,source_lang)
1865 AND input_value_id = l_input_value_id
1866 ;
1867
1868 --
1869 -- Write any changes to PAY_DYNDBI_CHANGES.
1870 --
1871 if l_langs.count <> 0 then
1872 pay_dyndbi_changes_pkg.input_value_change
1873 (p_input_value_id => l_input_value_id
1874 ,p_languages => l_langs
1875 );
1876 end if;
1877 end if;
1878 end TRANSLATE_ROW;
1879 --------------------------------------------------------------------------------
1880 procedure init_where_clause (
1881 p_vset_defn in out nocopy fnd_vset.valueset_r
1882 )
1883 is
1884 --
1885 c_prof constant varchar2(20) := ':$PROFILES$.';
1886 --
1887 l_where_clause varchar2(32000);
1888 l_replace_string varchar2(200);
1889 l_src varchar2(100);
1890 l_value varchar2(240);
1891 l_default_value varchar2(240);
1892 l_idx number;
1893 l_ch varchar2(10);
1894 l_prof_len number;
1895 l_prof_found boolean;
1896 --
1897 begin
1898 --
1899 if p_vset_defn.validation_type <> 'F' or
1900 p_vset_defn.table_info.where_clause is null then
1901 --
1902 -- no where clause to initialize
1903 --
1904 return;
1905 --
1906 end if;
1907 --
1908 l_prof_len := length(c_prof);
1909 l_where_clause := p_vset_defn.table_info.where_clause;
1910 --
1911 loop
1912 --
1913 l_src := null;
1914 --
1915 -- find the position of ':$PROFILES$.' in the where clause
1916 --
1917 l_idx := instr(upper(l_where_clause),c_prof);
1918 --
1919 exit when l_idx = 0;
1920 --
1921 -- the where clause contains :$PROFILES$ references, the profile option
1922 -- needs to be resolved
1923 --
1924 l_prof_found := true;
1925 l_replace_string := substr(l_where_clause,l_idx,l_prof_len);
1926 l_idx := l_idx + l_prof_len;
1927 --
1928 -- loop to determine profile option name
1929 --
1930 loop
1931 --
1932 -- build up the profile option name 1 character at a time
1933 --
1934 l_ch := substr(l_where_clause,l_idx,1);
1935 --
1936 -- the profile option name can only contain alphanumeric characters and
1937 -- underscores so exit when l_ch is not one of these
1938 --
1939 exit when l_ch is null or
1940 not (upper(l_ch) between 'A' and 'Z' or l_ch between '0' and '9' or l_ch = '_');
1941 --
1942 l_src := l_src || l_ch;
1943 l_idx := l_idx + 1;
1944 --
1945 end loop;
1946 --
1947 l_default_value := null;
1948 --
1949 if l_ch = ':' then
1950 --
1951 -- a default value has been specified
1952 --
1953 l_idx := l_idx + 1;
1954 --
1955 -- loop to determine default value
1956 --
1957 loop
1958 --
1959 -- build up the default value 1 character at a time
1960 --
1961 l_ch := substr(l_where_clause,l_idx,1);
1962 --
1963 -- the default value can only contain alphanumeric characters and
1964 -- underscores so exit when l_ch is not one of these
1965 --
1966 exit when l_ch is null or
1967 not (upper(l_ch) between 'A' and 'Z' or l_ch between '0' and '9' or l_ch = '_');
1968 l_default_value := l_default_value || l_ch;
1969 l_idx := l_idx + 1;
1970 --
1971 end loop;
1972 --
1973 end if;
1974 --
1975 -- l_src now contains the profile option name, so append this to
1976 -- l_replace_string
1977 --
1978 l_replace_string := l_replace_string || l_src;
1979 --
1980 if l_default_value is not null then
1981 --
1982 -- l_default_value is not null so this also needs to be appended to
1983 -- l_replace_string
1984 --
1985 l_replace_string := l_replace_string ||':'||l_default_value;
1986 --
1987 end if;
1988 --
1989 -- now resolve the profile option value
1990 --
1991 l_value := null;
1992 fnd_profile.get(upper(l_src),l_value);
1993 --
1994 if l_value is null then
1995 --
1996 -- use the default value, or 'NULL' if the default is null
1997 --
1998 l_value := nvl(l_default_value,'NULL');
1999 --
2000 end if;
2001 --
2002 -- replace all occurrences of l_replace_string in l_where_clause with
2003 -- l_value (in single quotes)
2004 --
2005 l_where_clause := replace(l_where_clause,l_replace_string,''''||l_value||'''');
2006 --
2007 end loop;
2008 --
2009 -- all :$PROFILES$ references have been resolved, replace the where clause in
2010 -- p_vset_defn
2011 --
2012 if l_prof_found then
2013 --
2014 p_vset_defn.table_info.where_clause := l_where_clause;
2015 --
2016 end if;
2017 --
2018 end init_where_clause;
2019 --------------------------------------------------------------------------------
2020 -- Enhancement 2793978
2021 function decode_vset_value (
2022 p_value_set_id in number
2023 , p_value_set_value in varchar2
2024 )
2025 return varchar2 is
2026 --
2027 -- Returns the corresponding meaning from a value set for a given value.
2028 -- Returns meaning if hasmeaning() is true, returns value otherwise.
2029 --
2030 l_value_set_meaning varchar2(240);
2031 l_vset_defn fnd_vset.valueset_r;
2032 l_vset_fmt fnd_vset.valueset_dr;
2033 l_vset_value fnd_vset.value_dr;
2034 l_vset_row_count number;
2035 l_vset_value_found boolean;
2036 l_match_found boolean;
2037 --
2038 begin
2039 --
2040 fnd_vset.get_valueset(p_value_set_id,l_vset_defn,l_vset_fmt);
2041 init_where_clause(l_vset_defn);
2042 fnd_vset.get_value_init(l_vset_defn, true);
2043 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2044 --
2045 while l_vset_value_found loop
2046 --
2047 if l_vset_fmt.has_id and l_vset_value.id = p_value_set_value then
2048 l_match_found := true;
2049 elsif l_vset_value.value = p_value_set_value then
2050 l_match_found := true;
2051 else
2052 l_match_found := false;
2053 end if;
2054 --
2055 if l_match_found and l_vset_fmt.has_meaning then
2056 l_value_set_meaning := l_vset_value.meaning;
2057 exit;
2058 elsif l_match_found then
2059 l_value_set_meaning := l_vset_value.value;
2060 exit;
2061 end if;
2062 --
2063 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2064 --
2065 end loop;
2066 --
2067 fnd_vset.get_value_end(l_vset_defn);
2068 if l_match_found then
2069 return l_value_set_meaning;
2070 else
2071 return null;
2072 end if;
2073 --
2074 exception
2075 --
2076 when others then
2077 return null;
2078 --
2079 end decode_vset_value;
2080 --------------------------------------------------------------------------------
2081 -- Enhancement 2793978
2082 function decode_vset_meaning (
2083 p_value_set_id in number
2084 , p_value_set_meaning in varchar2
2085 )
2086 return varchar2 is
2087 --
2088 -- Returns the corresponding value from a value set for a given meaning.
2089 -- Returns id if hasid() is true, returns value otherwise.
2090 --
2091 l_value_set_value varchar2(150);
2092 l_vset_defn fnd_vset.valueset_r;
2093 l_vset_fmt fnd_vset.valueset_dr;
2094 l_vset_value fnd_vset.value_dr;
2095 l_vset_row_count number;
2096 l_vset_value_found boolean;
2097 l_match_found boolean;
2098 --
2099 begin
2100 --
2101 fnd_vset.get_valueset(p_value_set_id,l_vset_defn,l_vset_fmt);
2102 init_where_clause(l_vset_defn);
2103 fnd_vset.get_value_init(l_vset_defn, true);
2104 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2105 --
2106 while l_vset_value_found loop
2107 --
2108 if l_vset_fmt.has_meaning and l_vset_value.meaning = p_value_set_meaning then
2109 l_match_found := true;
2110 elsif l_vset_value.value = p_value_set_meaning then
2111 l_match_found := true;
2112 else
2113 l_match_found := false;
2114 end if;
2115 --
2116 if l_match_found and l_vset_fmt.has_id then
2117 l_value_set_value := l_vset_value.id;
2118 exit;
2119 elsif l_match_found then
2120 l_value_set_value := l_vset_value.value;
2121 exit;
2122 end if;
2123 --
2124 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2125 --
2126 end loop;
2127 --
2128 fnd_vset.get_value_end(l_vset_defn);
2129 if l_match_found and length(l_value_set_value) <= 60 then
2130 return l_value_set_value;
2131 else
2132 return null;
2133 end if;
2134 --
2135 exception
2136 --
2137 when others then
2138 return null;
2139 --
2140 end decode_vset_meaning;
2141 --------------------------------------------------------------------------------
2142 begin
2143 --
2144 c_user_id := fnd_global.user_id;
2145 c_login_id := fnd_global.login_id;
2146 --
2147 end PAY_INPUT_VALUES_PKG;
[Home] [Help]