1 package body PAY_INPUT_VALUES_PKG as
2 /* $Header: pyipv.pkb 120.5.12010000.5 2009/11/06 11:02:28 asnell ship $ */
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 -- bug 8797632
1201 for c_lang_rec in c_language(P_INPUT_VALUE_ID) loop
1202 if (upper(l_name) = 'PAY VALUE') then
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 else
1215 -- some lookups have _ so use like
1216 -- meanings are unique so where already used use description
1217 begin
1218 select nvl(description,meaning)
1219 into l_tl_name
1220 from fnd_lookup_values
1221 where lookup_type = 'NAME_TRANSLATIONS'
1222 and upper(l_name) like lookup_code
1223 and language = c_lang_rec.language_code
1224 and rownum = 1;
1225 exception
1226 when no_data_found then
1227 l_tl_name := l_name;
1228 end;
1229 end if;
1230
1231 insert into PAY_INPUT_VALUES_F_TL (
1232 INPUT_VALUE_ID,
1233 NAME,
1234 LAST_UPDATE_DATE,
1235 LAST_UPDATED_BY,
1236 LAST_UPDATE_LOGIN,
1237 CREATED_BY,
1238 CREATION_DATE,
1239 LANGUAGE,
1240 SOURCE_LANG
1241 ) select
1242 P_INPUT_VALUE_ID,
1243 l_tl_name,
1244 sysdate,
1245 c_user_id,
1246 c_user_id,
1247 c_login_id,
1248 sysdate,
1249 c_lang_rec.language_code,
1250 userenv('LANG')
1251 from dual;
1252
1253 end loop;
1254
1255 --
1256 -- ***********************************************************************
1257 --
1258 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',5);
1259 --
1260 open csr_input_value_rowid;
1261 fetch csr_input_value_rowid into p_rowid;
1262 --
1263 if csr_input_value_rowid%notfound then
1264 close csr_input_value_rowid;
1265 raise no_data_found;
1266 else
1267 --
1268 -- Create link input values for existing links
1269 --
1270 pay_link_input_values_pkg.create_link_input_value (
1271
1272 p_input_value_id,
1273 p_element_type_id,
1274 p_effective_start_date,
1275 p_effective_end_date,
1276 -- change 115.12
1277 l_name,
1278 p_hot_default_flag,
1279 p_default_value,
1280 p_min_value,
1281 p_max_value,
1282 p_warning_or_error);
1283
1284 -- Create balance feeds for pay values
1285 --
1286 -- change 115.12
1287 if (l_name = 'Pay Value' -- the default
1288 or upper (l_name) = upper (v_pay_value_name)) then
1289 l_check_latest_balances := HRASSACT.CHECK_LATEST_BALANCES;
1290 HRASSACT.CHECK_LATEST_BALANCES := FALSE;
1291 hr_balance_feeds.ins_bf_pay_value ( p_input_value_id );
1292 HRASSACT.CHECK_LATEST_BALANCES := l_check_latest_balances;
1293 end if;
1294 --
1295 -- Create DB item for the input value
1296 if p_generate_db_items_flag = 'Y' then
1297 hrdyndbi.create_input_value_dict ( p_input_value_id,
1298 p_effective_start_date );
1299 end if;
1300 --
1301 -- Create application ownership for startup pay value
1302 if upper (l_name) = upper (v_pay_value_name)
1303 and p_legislation_code is not null then
1304 --
1305 -- The 'not exists' clause is used to ensure that duplicate rows are not
1306 -- entered. This could arise because the forms startup code also handles
1307 -- application ownerships where a user enters a pay value on the form, but
1308 -- this code is intended to handle third party insertion from the element
1309 --
1310 insert into hr_application_ownerships
1311 (key_name,
1312 key_value,
1313 product_name)
1314 select 'INPUT_VALUE_ID',
1315 p_input_value_id,
1316 ao.product_name
1317 from hr_application_ownerships ao
1318 where ao.key_name = 'ELEMENT_TYPE_ID'
1319 and ao.key_value = p_element_type_id
1320 and not exists (select 'INPUT_VALUE_ID',
1321 p_input_value_id,
1322 ao.product_name
1323 from hr_application_ownerships ao
1324 where ao.key_name = 'ELEMENT_TYPE_ID'
1325 and ao.key_value = p_element_type_id);
1326 --
1327 end if;
1328 --
1329 end if;
1330 close csr_input_value_rowid;
1331 --
1332 --
1333 end insert_row;
1334 ---------------------------------------------------------------------------
1335 procedure UPDATE_ROW(
1336 --
1337 --******************************************************************************
1338 --* Handles the updating of the base table for the form which is based on a *
1339 --* non-updatable view *
1340 --******************************************************************************
1341 --
1342 -- Parameters are:
1343 --
1344 -- All base table columns
1345 p_ROWID VARCHAR2,
1346 p_INPUT_VALUE_ID NUMBER,
1347 p_EFFECTIVE_START_DATE DATE,
1348 p_EFFECTIVE_END_DATE DATE,
1349 p_ELEMENT_TYPE_ID NUMBER,
1350 p_LOOKUP_TYPE VARCHAR2,
1351 p_BUSINESS_GROUP_ID NUMBER,
1352 p_LEGISLATION_CODE VARCHAR2,
1353 p_FORMULA_ID NUMBER,
1354 p_DISPLAY_SEQUENCE NUMBER,
1355 p_GENERATE_DB_ITEMS_FLAG VARCHAR2,
1356 p_HOT_DEFAULT_FLAG VARCHAR2,
1357 p_MANDATORY_FLAG VARCHAR2,
1358 p_NAME VARCHAR2,
1359 p_UOM VARCHAR2,
1360 p_DEFAULT_VALUE VARCHAR2,
1361 p_LEGISLATION_SUBGROUP VARCHAR2,
1362 p_MAX_VALUE VARCHAR2,
1363 p_MIN_VALUE VARCHAR2,
1364 p_WARNING_OR_ERROR VARCHAR2,
1365 -- Enhancement 2793978
1366 p_value_set_id number default null,
1367 --
1368 p_recreate_db_items varchar2,
1369 p_base_name varchar2
1370 --
1371 ) is
1372 l_tl_name varchar2(255);
1373 --
1374 begin
1375 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.update_row',1);
1376 --
1377 update pay_input_values_f
1378 set INPUT_VALUE_ID = p_INPUT_VALUE_ID,
1379 EFFECTIVE_START_DATE = p_EFFECTIVE_START_DATE,
1380 EFFECTIVE_END_DATE = p_EFFECTIVE_END_DATE,
1381 ELEMENT_TYPE_ID = p_ELEMENT_TYPE_ID,
1382 LOOKUP_TYPE = p_LOOKUP_TYPE,
1383 BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID,
1384 LEGISLATION_CODE = p_LEGISLATION_CODE,
1385 FORMULA_ID = p_FORMULA_ID,
1386 DISPLAY_SEQUENCE = p_DISPLAY_SEQUENCE,
1387 GENERATE_DB_ITEMS_FLAG = p_GENERATE_DB_ITEMS_FLAG,
1388 HOT_DEFAULT_FLAG = p_HOT_DEFAULT_FLAG,
1389 MANDATORY_FLAG = p_MANDATORY_FLAG,
1390 -- --
1391 NAME = p_base_NAME,
1392 -- --
1393 UOM = p_UOM,
1394 DEFAULT_VALUE = p_DEFAULT_VALUE,
1395 LEGISLATION_SUBGROUP = p_LEGISLATION_SUBGROUP,
1396 MAX_VALUE = p_MAX_VALUE,
1397 MIN_VALUE = p_MIN_VALUE,
1398 WARNING_OR_ERROR = p_WARNING_OR_ERROR,
1399 -- Enhancement 2793978
1400 VALUE_SET_ID = p_VALUE_SET_ID,
1401 --
1402 last_update_date = sysdate,
1403 last_updated_by = c_user_id,
1404 last_update_login = c_login_id
1405 where rowid = p_rowid;
1406 --
1407 if sql%notfound then
1408 raise no_data_found;
1409 end if;
1410 --
1411 --
1412 -- ************************************************************************
1413 -- update MLS table (TL)
1414 --
1415 l_tl_name := P_NAME;
1416 if (upper(P_NAME) = 'PAY VALUE') then
1417 begin
1418 select meaning
1419 into l_tl_name
1420 from fnd_lookup_values
1421 where lookup_type = 'NAME_TRANSLATIONS'
1422 and lookup_code = 'PAY VALUE'
1423 and language = userenv('LANG');
1424 exception
1425 when no_data_found then
1426 l_tl_name := P_NAME;
1427 end;
1428 end if;
1429
1430 update PAY_INPUT_VALUES_F_TL
1431 set
1432 NAME = l_tl_name,
1433 last_update_date = sysdate,
1434 last_updated_by = c_user_id,
1435 last_update_login = c_login_id,
1436 SOURCE_LANG = userenv('LANG')
1437 where INPUT_VALUE_ID = P_INPUT_VALUE_ID
1438 and userenv('LANG') = LANGUAGE ; -- bug 6125295
1439 --
1440 if sql%notfound then -- trap system errors during update
1441 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1442 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.UPDATE_TL_ROW');
1443 hr_utility.raise_error;
1444 end if;
1445 --
1446 -- ************************************************************************
1447 --
1448 if p_recreate_db_items = 'Y' then
1449 --
1450 -- Recreate DB items
1451 hrdyndbi.delete_input_value_dict (p_input_value_id);
1452 hrdyndbi.create_input_value_dict (p_input_value_id,p_effective_start_date);
1453 --
1454 end if;
1455 --
1456 end update_row;
1457 ---------------------------------------------------------------------------
1458 procedure DELETE_ROW (
1459 --
1460 --******************************************************************************
1461 --* Handles deletion from the base table for the form which is based on a *
1462 --* non-updatable view, and maintains data integrity *
1463 --******************************************************************************
1464 --
1465 -- Parameters are:
1466 --
1467 -- Identifier of the row to be deleted
1468 p_rowid varchar2,
1469 p_input_value_id number,
1470 --
1471 -- Date Track delete mode
1472 p_delete_mode varchar2,
1473 --
1474 -- Validation period
1475 p_session_date date,
1476 p_validation_start_date date
1477 default to_date ('01/01/0001','DD/MM/YYYY'),
1478 p_validation_end_date date
1479 default to_date ('31/12/4712','DD/MM/YYYY')
1480 ) is
1481 --
1482 begin
1483 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DELETE_ROW',1);
1484 --
1485 if deletion_allowed ( p_input_value_id,
1486 p_delete_mode,
1487 p_validation_start_date,
1488 p_validation_end_date ) then
1489 --
1490 hr_balance_feeds.del_bf_input_value ( p_input_value_id,
1491 p_delete_mode,
1492 p_validation_start_date,
1493 p_validation_end_date );
1494 --
1495 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DELETE_ROW',2);
1496 --
1497 -- delete DB items
1498 hrdyndbi.delete_input_value_dict (p_input_value_id);
1499 --
1500 delete from pay_input_values_f
1501 where rowid = p_rowid;
1502 --
1503 if sql%notfound then
1504 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1505 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.DELETE_ROW');
1506 hr_utility.raise_error;
1507 end if;
1508 --
1509 --
1510 -- ****************************************************************************
1511 -- delete from MLS table (TL)
1512 --
1513
1514 --
1515 -- bugfix 1229606
1516 -- only delete data from the translated tables if the date track mode is ZAP,
1517 -- for all other date track modes the data should remain untouched
1518 --
1519 if p_delete_mode = 'ZAP' then
1520
1521 delete from PAY_INPUT_VALUES_F_TL
1522 where INPUT_VALUE_ID = P_INPUT_VALUE_ID;
1523 --
1524 if sql%notfound then -- trap system errors during deletion
1525 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1526 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.DELETE_TL_ROW');
1527 hr_utility.raise_error;
1528 end if;
1529
1530 end if;
1531 --
1532 -- ******************************************************************************
1533 --
1534 end if;
1535 --
1536 end delete_row;
1537 ---------------------------------------------------------------------------
1538 procedure LOCK_ROW (
1539 p_rowid VARCHAR2,
1540 p_INPUT_VALUE_ID NUMBER,
1541 p_EFFECTIVE_START_DATE DATE,
1542 p_EFFECTIVE_END_DATE DATE,
1543 p_ELEMENT_TYPE_ID NUMBER,
1544 p_LOOKUP_TYPE VARCHAR2,
1545 p_BUSINESS_GROUP_ID NUMBER,
1546 p_LEGISLATION_CODE VARCHAR2,
1547 p_FORMULA_ID NUMBER,
1548 p_DISPLAY_SEQUENCE NUMBER,
1549 p_GENERATE_DB_ITEMS_FLAG VARCHAR2,
1550 p_HOT_DEFAULT_FLAG VARCHAR2,
1551 p_MANDATORY_FLAG VARCHAR2,
1552 -- p_NAME VARCHAR2,
1553 -- --
1554 p_BASE_NAME VARCHAR2,
1555 -- --
1556 p_UOM VARCHAR2,
1557 p_DEFAULT_VALUE VARCHAR2,
1558 p_LEGISLATION_SUBGROUP VARCHAR2,
1559 p_MAX_VALUE VARCHAR2,
1560 p_MIN_VALUE VARCHAR2,
1561 p_WARNING_OR_ERROR VARCHAR2,
1562 -- Enhancement 2793978
1563 p_value_set_id NUMBER default null
1564 --
1565 ) is
1566 --
1567 cursor csr_lock_input_value is
1568 select *
1569 from pay_input_values_f
1570 where rowid = p_rowid
1571 for update of input_value_id nowait;
1572 --
1573 v_locked_row csr_lock_input_value%rowtype;
1574 -- MLS Row counter
1575 v_mls_count NUMBER:=0;
1576 --
1577 -- *****************************************************************
1578 -- cursor for MLS
1579 --
1580 cursor csr_lock_input_value_tl is
1581 select
1582 NAME,
1583 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
1584 from PAY_INPUT_VALUES_F_TL
1585 where INPUT_VALUE_ID = P_INPUT_VALUE_ID
1586 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1587 for update of INPUT_VALUE_ID nowait;
1588 --
1589 -- ****************************************************************
1590 --
1591 begin
1592 hr_utility.set_location ('PAY_INPUT_VALUES_PKG.lock_row',1);
1593 --
1594 open csr_lock_input_value;
1595 fetch csr_lock_input_value into v_locked_row;
1596 if csr_lock_input_value%notfound then
1597 close csr_lock_input_value;
1598 raise no_data_found;
1599 end if;
1600 close csr_lock_input_value;
1601 --
1602 /** sbilling **/
1603 -- removed explicit lock of _TL table,
1604 -- the MLS strategy requires that the base table is locked before update of the
1605 -- _TL table can take place,
1606 -- which implies it is not necessary to lock both tables.
1607 --
1608 -- ************************************************************************
1609 -- code for MLS
1610 --
1611 -- for tlinfo in csr_lock_input_value_tl LOOP
1612 -- v_mls_count := v_mls_count+1;
1613 -- if (tlinfo.BASELANG = 'Y') then
1614 -- if ( (tlinfo.NAME = P_NAME)
1615 -- ) then
1616 -- null;
1617 -- else
1618 -- hr_utility.set_message ('FND','FORM_RECORD_CHANGED');
1619 -- hr_utility.raise_error;
1620 -- end if;
1621 -- end if;
1622 -- end loop;
1623 --
1624 --
1625 -- if (v_mls_count=0) then -- Trap system errors
1626 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1627 -- hr_utility.set_message_token ('PROCEDURE','PAY_INPUT_VALUES_PKG.LOCK_TL_ROW');
1628 -- end if;
1629 --
1630 -- ************************************************************************
1631 --
1632 --
1633 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))
1634 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))
1635 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))
1636 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))
1637 and ((V_LOCKED_ROW.LOOKUP_TYPE = p_LOOKUP_TYPE) OR (V_LOCKED_ROW.LOOKUP_TYPE is null and p_LOOKUP_TYPE is null))
1638 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))
1639 and ((V_LOCKED_ROW.LEGISLATION_CODE = p_LEGISLATION_CODE) OR (V_LOCKED_ROW.LEGISLATION_CODE is null and p_LEGISLATION_CODE is null))
1640 and ((V_LOCKED_ROW.FORMULA_ID = p_FORMULA_ID) OR (V_LOCKED_ROW.FORMULA_ID is null and p_FORMULA_ID is null))
1641 and ((V_LOCKED_ROW.DISPLAY_SEQUENCE = p_DISPLAY_SEQUENCE) OR (V_LOCKED_ROW.DISPLAY_SEQUENCE is null and p_DISPLAY_SEQUENCE is null))
1642 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))
1643 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))
1644 and ((V_LOCKED_ROW.MANDATORY_FLAG = p_MANDATORY_FLAG) OR (V_LOCKED_ROW.MANDATORY_FLAG is null and p_MANDATORY_FLAG is null))
1645 --and ((V_LOCKED_ROW.NAME = p_NAME) OR (V_LOCKED_ROW.NAME is null and p_NAME is null))
1646 -- --
1647 and ((V_LOCKED_ROW.NAME = p_BASE_NAME) OR (V_LOCKED_ROW.NAME is null and p_BASE_NAME is null))
1648 -- --
1649 and ((V_LOCKED_ROW.UOM = p_UOM) OR (V_LOCKED_ROW.UOM is null and p_UOM is null))
1650 and ((V_LOCKED_ROW.DEFAULT_VALUE = p_DEFAULT_VALUE) OR (V_LOCKED_ROW.DEFAULT_VALUE is null and p_DEFAULT_VALUE is null))
1651 and ((V_LOCKED_ROW.LEGISLATION_SUBGROUP = p_LEGISLATION_SUBGROUP) OR (V_LOCKED_ROW.LEGISLATION_SUBGROUP is null and p_LEGISLATION_SUBGROUP is null))
1652 and ((V_LOCKED_ROW.MAX_VALUE = p_MAX_VALUE) OR (V_LOCKED_ROW.MAX_VALUE is null and p_MAX_VALUE is null))
1653 and ((V_LOCKED_ROW.MIN_VALUE = p_MIN_VALUE) OR (V_LOCKED_ROW.MIN_VALUE is null and p_MIN_VALUE is null))
1654 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
1655 return;
1656 else
1657 hr_utility.set_message ('FND','FORM_RECORD_CHANGED');
1658 hr_utility.raise_error;
1659 end if;
1660 --
1661 end lock_row;
1662 ---------------------------------------------------------------------------
1663 procedure ADD_LANGUAGE
1664 is
1665 begin
1666 delete from PAY_INPUT_VALUES_F_TL T
1667 where not exists
1668 (select NULL
1669 from PAY_INPUT_VALUES_F B
1670 where B.INPUT_VALUE_ID = T.INPUT_VALUE_ID
1671 );
1672
1673 update PAY_INPUT_VALUES_F_TL T set (
1674 NAME
1675 ) = (select
1676 B.NAME
1677 from PAY_INPUT_VALUES_F_TL B
1678 where B.INPUT_VALUE_ID = T.INPUT_VALUE_ID
1679 and B.LANGUAGE = T.SOURCE_LANG)
1680 where (
1681 T.INPUT_VALUE_ID,
1682 T.LANGUAGE
1683 ) in (select
1684 SUBT.INPUT_VALUE_ID,
1685 SUBT.LANGUAGE
1686 from PAY_INPUT_VALUES_F_TL SUBB, PAY_INPUT_VALUES_F_TL SUBT
1687 where SUBB.INPUT_VALUE_ID = SUBT.INPUT_VALUE_ID
1688 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1689 and (SUBB.NAME <> SUBT.NAME
1690 ));
1691
1692 insert into PAY_INPUT_VALUES_F_TL (
1693 INPUT_VALUE_ID,
1694 NAME,
1695 LAST_UPDATE_DATE,
1696 LAST_UPDATED_BY,
1697 LAST_UPDATE_LOGIN,
1698 CREATED_BY,
1699 CREATION_DATE,
1700 LANGUAGE,
1701 SOURCE_LANG
1702 ) select
1703 B.INPUT_VALUE_ID,
1704 B.NAME,
1705 B.LAST_UPDATE_DATE,
1706 B.LAST_UPDATED_BY,
1707 B.LAST_UPDATE_LOGIN,
1708 B.CREATED_BY,
1709 B.CREATION_DATE,
1710 L.LANGUAGE_CODE,
1711 B.SOURCE_LANG
1712 from PAY_INPUT_VALUES_F_TL B, FND_LANGUAGES L
1713 where L.INSTALLED_FLAG in ('I', 'B')
1714 and B.LANGUAGE = userenv('LANG')
1715 and not exists
1716 (select NULL
1717 from PAY_INPUT_VALUES_F_TL T
1718 where T.INPUT_VALUE_ID = B.INPUT_VALUE_ID
1719 and T.LANGUAGE = L.LANGUAGE_CODE);
1720 end ADD_LANGUAGE;
1721 -----------------------------------------------------------
1722 procedure unique_chk(
1723 X_I_NAME in varchar2,
1724 X_I_LEGISLATION_CODE in varchar2,
1725 X_I_EFFECTIVE_START_DATE in date,
1726 X_I_EFFECTIVE_END_DATE in date,
1727 X_I_E_ELEMENT_NAME in varchar2,
1728 X_I_E_LEGISLATION_CODE in varchar2,
1729 X_I_E_EFFECTIVE_START_DATE in date,
1730 X_I_E_EFFECTIVE_END_DATE in date )
1731 is
1732 result varchar2(255);
1733 Begin
1734 SELECT count(*) INTO result
1735 FROM pay_element_types_f E, pay_input_values_F I
1736 WHERE I.ELEMENT_TYPE_ID = E.ELEMENT_TYPE_ID
1737 and nvl(E.ELEMENT_NAME,'~null~') = nvl(X_I_E_ELEMENT_NAME,'~null~')
1738 and nvl(E.LEGISLATION_CODE,'~null~') = nvl(X_I_E_LEGISLATION_CODE,'~null~')
1739 and E.EFFECTIVE_START_DATE = X_I_E_EFFECTIVE_START_DATE
1740 and E.EFFECTIVE_end_DATE = X_I_E_EFFECTIVE_END_DATE
1741 and X_I_E_EFFECTIVE_START_DATE is not NULL
1742 and X_I_E_EFFECTIVE_END_DATE is not NULL
1743 and E.BUSINESS_GROUP_ID is NULL
1744 and nvl(I.NAME,'~null~') = nvl(X_I_NAME,'~null~')
1745 and nvl(I.LEGISLATION_CODE,'~null~') = nvl(X_I_LEGISLATION_CODE,'~null~')
1746 and I.EFFECTIVE_START_DATE = X_I_EFFECTIVE_START_DATE
1747 and I.EFFECTIVE_end_DATE = X_I_EFFECTIVE_END_DATE
1748 and X_I_EFFECTIVE_START_DATE is not NULL
1749 and X_I_EFFECTIVE_END_DATE is not NULL
1750 and I.BUSINESS_GROUP_ID is NULL;
1751 --
1752 IF (result>1) THEN
1753 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1754 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.UNIQUE_CHK');
1755 hr_utility.set_message_token('STEP','1');
1756 hr_utility.raise_error;
1757 END IF;
1758 EXCEPTION
1759 when NO_DATA_FOUND then
1760 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1761 hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.UNIQUE_CHK');
1762 hr_utility.set_message_token('STEP','1');
1763 hr_utility.raise_error;
1764 end unique_chk;
1765 --------------------------------------------------------------------------------
1766 procedure TRANSLATE_ROW (
1767 X_I_NAME in varchar2,
1768 X_I_LEGISLATION_CODE in varchar2,
1769 X_I_EFFECTIVE_START_DATE in date,
1770 X_I_EFFECTIVE_END_DATE in date,
1771 X_I_E_ELEMENT_NAME in varchar2,
1772 X_I_E_LEGISLATION_CODE in varchar2,
1773 X_I_E_EFFECTIVE_START_DATE in date,
1774 X_I_E_EFFECTIVE_END_DATE in date,
1775 X_NAME in varchar2,
1776 X_OWNER in varchar2 ) is
1777 --
1778 -- Fetch the input_value_id. This used to be a sub-query in the update
1779 -- statement.
1780 --
1781 cursor csr_ipv_id is
1782 select i.input_value_id
1783 from pay_element_types_f e
1784 , pay_input_values_f i
1785 WHERE i.element_type_id = e.element_type_id
1786 and nvl(e.element_name,'~null~') = nvl(x_i_e_element_name,'~null~')
1787 and nvl(e.legislation_code,'~null~') = nvl(x_i_e_legislation_code,'~null~')
1788 and e.effective_start_date = x_i_e_effective_start_date
1789 and e.effective_END_date = x_i_e_effective_end_date
1790 and x_i_e_effective_start_date is not null
1791 and x_i_e_effective_end_date is not null
1792 and e.business_group_id is null
1793 and nvl(i.name,'~null~') = nvl(x_i_name,'~null~')
1794 and nvl(i.legislation_code,'~null~') = nvl(x_i_legislation_code,'~null~')
1795 and i.effective_start_date = x_i_effective_start_date
1796 and i.effective_end_date = x_i_effective_end_date
1797 and x_i_effective_start_date is not null
1798 and x_i_effective_end_date is not null
1799 and i.business_group_id is null
1800 ;
1801 --
1802 -- Fetch information for the _TL rows that will be affected by the update.
1803 --
1804 cursor csr_tl_info
1805 (p_input_value_id in number
1806 ,p_language in varchar2
1807 ) is
1808 select name
1809 , language
1810 from pay_input_values_f_tl
1811 where input_value_id = p_input_value_id
1812 and p_language in (language, source_lang)
1813 ;
1814 --
1815 l_input_value_id number;
1816 l_found boolean;
1817 i binary_integer := 1;
1818 l_langs dbms_sql.varchar2s;
1819 l_lang varchar2(100);
1820 begin
1821 --
1822 -- Fetch the input_value_id.
1823 --
1824 open csr_ipv_id;
1825 fetch csr_ipv_id
1826 into l_input_value_id
1827 ;
1828 l_found := csr_ipv_id%found;
1829 close csr_ipv_id;
1830
1831
1832 l_lang := userenv('LANG');
1833
1834 if l_found then
1835 --
1836 -- Check if database item translations are supported.
1837 --
1838 if ff_dbi_utils_pkg.translations_supported
1839 (p_legislation_code => x_i_legislation_code
1840 ) then
1841 for crec in csr_tl_info
1842 (p_input_value_id => l_input_value_id
1843 ,p_language => l_lang
1844 ) loop
1845 if upper(crec.name) <> upper(x_name) then
1846 l_langs(i) := crec.language;
1847 i := i + 1;
1848 end if;
1849 end loop;
1850 end if;
1851
1852 UPDATE pay_input_values_f_tl
1853 SET name = nvl(x_name,name),
1854 last_update_date = sysdate,
1855 last_updated_by = decode(x_owner,'SEED',1,0),
1856 last_update_login = 0,
1857 source_lang = userenv('LANG')
1858 WHERE userenv('LANG') IN (language,source_lang)
1859 AND input_value_id = l_input_value_id
1860 ;
1861
1862 --
1863 -- Write any changes to PAY_DYNDBI_CHANGES.
1864 --
1865 if l_langs.count <> 0 then
1866 pay_dyndbi_changes_pkg.input_value_change
1867 (p_input_value_id => l_input_value_id
1868 ,p_languages => l_langs
1869 );
1870 end if;
1871 end if;
1872 end TRANSLATE_ROW;
1873 --------------------------------------------------------------------------------
1874 procedure init_where_clause (
1875 p_vset_defn in out nocopy fnd_vset.valueset_r
1876 )
1877 is
1878 --
1879 c_prof constant varchar2(20) := ':$PROFILES$.';
1880 --
1881 l_where_clause varchar2(32000);
1882 l_replace_string varchar2(200);
1883 l_src varchar2(100);
1884 l_value varchar2(240);
1885 l_default_value varchar2(240);
1886 l_idx number;
1887 l_ch varchar2(10);
1888 l_prof_len number;
1889 l_prof_found boolean;
1890 --
1891 begin
1892 --
1893 if p_vset_defn.validation_type <> 'F' or
1894 p_vset_defn.table_info.where_clause is null then
1895 --
1896 -- no where clause to initialize
1897 --
1898 return;
1899 --
1900 end if;
1901 --
1902 l_prof_len := length(c_prof);
1903 l_where_clause := p_vset_defn.table_info.where_clause;
1904 --
1905 loop
1906 --
1907 l_src := null;
1908 --
1909 -- find the position of ':$PROFILES$.' in the where clause
1910 --
1911 l_idx := instr(upper(l_where_clause),c_prof);
1912 --
1913 exit when l_idx = 0;
1914 --
1915 -- the where clause contains :$PROFILES$ references, the profile option
1916 -- needs to be resolved
1917 --
1918 l_prof_found := true;
1919 l_replace_string := substr(l_where_clause,l_idx,l_prof_len);
1920 l_idx := l_idx + l_prof_len;
1921 --
1922 -- loop to determine profile option name
1923 --
1924 loop
1925 --
1926 -- build up the profile option name 1 character at a time
1927 --
1928 l_ch := substr(l_where_clause,l_idx,1);
1929 --
1930 -- the profile option name can only contain alphanumeric characters and
1931 -- underscores so exit when l_ch is not one of these
1932 --
1933 exit when l_ch is null or
1934 not (upper(l_ch) between 'A' and 'Z' or l_ch between '0' and '9' or l_ch = '_');
1935 --
1936 l_src := l_src || l_ch;
1937 l_idx := l_idx + 1;
1938 --
1939 end loop;
1940 --
1941 l_default_value := null;
1942 --
1943 if l_ch = ':' then
1944 --
1945 -- a default value has been specified
1946 --
1947 l_idx := l_idx + 1;
1948 --
1949 -- loop to determine default value
1950 --
1951 loop
1952 --
1953 -- build up the default value 1 character at a time
1954 --
1955 l_ch := substr(l_where_clause,l_idx,1);
1956 --
1957 -- the default value can only contain alphanumeric characters and
1958 -- underscores so exit when l_ch is not one of these
1959 --
1960 exit when l_ch is null or
1961 not (upper(l_ch) between 'A' and 'Z' or l_ch between '0' and '9' or l_ch = '_');
1962 l_default_value := l_default_value || l_ch;
1963 l_idx := l_idx + 1;
1964 --
1965 end loop;
1966 --
1967 end if;
1968 --
1969 -- l_src now contains the profile option name, so append this to
1970 -- l_replace_string
1971 --
1972 l_replace_string := l_replace_string || l_src;
1973 --
1974 if l_default_value is not null then
1975 --
1976 -- l_default_value is not null so this also needs to be appended to
1977 -- l_replace_string
1978 --
1979 l_replace_string := l_replace_string ||':'||l_default_value;
1980 --
1981 end if;
1982 --
1983 -- now resolve the profile option value
1984 --
1985 l_value := null;
1986 fnd_profile.get(upper(l_src),l_value);
1987 --
1988 if l_value is null then
1989 --
1990 -- use the default value, or 'NULL' if the default is null
1991 --
1992 l_value := nvl(l_default_value,'NULL');
1993 --
1994 end if;
1995 --
1996 -- replace all occurrences of l_replace_string in l_where_clause with
1997 -- l_value (in single quotes)
1998 --
1999 l_where_clause := replace(l_where_clause,l_replace_string,''''||l_value||'''');
2000 --
2001 end loop;
2002 --
2003 -- all :$PROFILES$ references have been resolved, replace the where clause in
2004 -- p_vset_defn
2005 --
2006 if l_prof_found then
2007 --
2008 p_vset_defn.table_info.where_clause := l_where_clause;
2009 --
2010 end if;
2011 --
2012 end init_where_clause;
2013 --------------------------------------------------------------------------------
2014 -- Enhancement 2793978
2015 function decode_vset_value (
2016 p_value_set_id in number
2017 , p_value_set_value in varchar2
2018 )
2019 return varchar2 is
2020 --
2021 -- Returns the corresponding meaning from a value set for a given value.
2022 -- Returns meaning if hasmeaning() is true, returns value otherwise.
2023 --
2024 l_value_set_meaning varchar2(240);
2025 l_vset_defn fnd_vset.valueset_r;
2026 l_vset_fmt fnd_vset.valueset_dr;
2027 l_vset_value fnd_vset.value_dr;
2028 l_vset_row_count number;
2029 l_vset_value_found boolean;
2030 l_match_found boolean;
2031 --
2032 begin
2033 --
2034 fnd_vset.get_valueset(p_value_set_id,l_vset_defn,l_vset_fmt);
2035 init_where_clause(l_vset_defn);
2036 fnd_vset.get_value_init(l_vset_defn, true);
2037 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2038 --
2039 while l_vset_value_found loop
2040 --
2041 if l_vset_fmt.has_id and l_vset_value.id = p_value_set_value then
2042 l_match_found := true;
2043 elsif l_vset_value.value = p_value_set_value then
2044 l_match_found := true;
2045 else
2046 l_match_found := false;
2047 end if;
2048 --
2049 if l_match_found and l_vset_fmt.has_meaning then
2050 l_value_set_meaning := l_vset_value.meaning;
2051 exit;
2052 elsif l_match_found then
2053 l_value_set_meaning := l_vset_value.value;
2054 exit;
2055 end if;
2056 --
2057 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2058 --
2059 end loop;
2060 --
2061 fnd_vset.get_value_end(l_vset_defn);
2062 if l_match_found then
2063 return l_value_set_meaning;
2064 else
2065 return null;
2066 end if;
2067 --
2068 exception
2069 --
2070 when others then
2071 return null;
2072 --
2073 end decode_vset_value;
2074 --------------------------------------------------------------------------------
2075 -- Enhancement 2793978
2076 function decode_vset_meaning (
2077 p_value_set_id in number
2078 , p_value_set_meaning in varchar2
2079 )
2080 return varchar2 is
2081 --
2082 -- Returns the corresponding value from a value set for a given meaning.
2083 -- Returns id if hasid() is true, returns value otherwise.
2084 --
2085 l_value_set_value varchar2(150);
2086 l_vset_defn fnd_vset.valueset_r;
2087 l_vset_fmt fnd_vset.valueset_dr;
2088 l_vset_value fnd_vset.value_dr;
2089 l_vset_row_count number;
2090 l_vset_value_found boolean;
2091 l_match_found boolean;
2092 --
2093 begin
2094 --
2095 fnd_vset.get_valueset(p_value_set_id,l_vset_defn,l_vset_fmt);
2096 init_where_clause(l_vset_defn);
2097 fnd_vset.get_value_init(l_vset_defn, true);
2098 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2099 --
2100 while l_vset_value_found loop
2101 --
2102 if l_vset_fmt.has_meaning and l_vset_value.meaning = p_value_set_meaning then
2103 l_match_found := true;
2104 elsif l_vset_value.value = p_value_set_meaning then
2105 l_match_found := true;
2106 else
2107 l_match_found := false;
2108 end if;
2109 --
2110 if l_match_found and l_vset_fmt.has_id then
2111 l_value_set_value := l_vset_value.id;
2112 exit;
2113 elsif l_match_found then
2114 l_value_set_value := l_vset_value.value;
2115 exit;
2116 end if;
2117 --
2118 fnd_vset.get_value(l_vset_defn,l_vset_row_count,l_vset_value_found,l_vset_value);
2119 --
2120 end loop;
2121 --
2122 fnd_vset.get_value_end(l_vset_defn);
2123 if l_match_found and length(l_value_set_value) <= 60 then
2124 return l_value_set_value;
2125 else
2126 return null;
2127 end if;
2128 --
2129 exception
2130 --
2131 when others then
2132 return null;
2133 --
2134 end decode_vset_meaning;
2135 --------------------------------------------------------------------------------
2136 begin
2137 --
2138 c_user_id := fnd_global.user_id;
2139 c_login_id := fnd_global.login_id;
2140 --
2141 end PAY_INPUT_VALUES_PKG;
[Home] [Help]