[Home] [Help]
PACKAGE BODY: APPS.PAY_ELEMENT_TYPES_PKG
Source
1 package body PAY_ELEMENT_TYPES_PKG as
2 /* $Header: pyelt.pkb 120.7.12020000.3 2012/07/12 10:57:16 jkvallab ship $ */
3 --
4 --------------------------------------------------------------------------------
5 -- Declaration of package-wide (global) variables and cursors
6 --
7 -- Constant for name of table being manipulated (useful for generic code)
8 c_base_table constant varchar2(30) := 'PAY_ELEMENT_TYPES_F';
9 --
10 c_user_id number;
11 c_login_id number;
12 --
13 -- Dummy variables for selecting into when not interested in value of result
14 g_dummy_number number(30);
15 g_dummy_char varchar2(255);
16 --
17 g_business_group_id number(15); -- For validating translation.
18 g_legislation_code varchar2(150);-- For validating translation.
19 --
20 -- Cursor to select formula result rules of a specified type for a
21 -- given element within the validation period.
22 --
23 cursor g_csr_result_rules (
24 --
25 p_element_type_id number,
26 p_validation_start_date date,
27 p_validation_end_date date,
28 p_rule_type varchar2
29 ) is
30 --
31 select result.status_processing_rule_id
32 from pay_formula_result_rules_f RESULT,
33 pay_input_values_f INPUT
34 where input.element_type_id = p_element_type_id
35 and input.input_value_id = result.input_value_id
36 and result.result_rule_type = p_rule_type
37 and result.effective_start_date between p_validation_start_date
38 and p_validation_end_date;
39 --
40 --
41 --
42 --
43 --
44 -------------------------------------------------------------------------------
45 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
46 p_legislation_code IN VARCHAR2) IS
47 BEGIN
48 g_business_group_id := p_business_group_id;
49 g_legislation_code := p_legislation_code;
50 END;
51 -------------------------------------------------------------------------------
52 procedure validate_translation(element_type_id IN NUMBER,
53 language IN VARCHAR2,
54 element_name IN VARCHAR2,
55 reporting_name IN VARCHAR2,
56 description IN VARCHAR2) IS
57 /*
58
59 This procedure fails if a element translation is already present in
60 the table for a given language. Otherwise, no action is performed. It is
61 used to ensure uniqueness of translated element names.
62
63 */
64
65
66 --
67 -- This cursor implements the validation we require,
68 -- and expects that the various package globals are set before
69 -- the call to this procedure is made. This is done from the
70 -- user-named trigger 'TRANSLATIONS' in the form
71 --
72 cursor c_translation(p_language IN VARCHAR2,
73 p_element_name IN VARCHAR2,
74 p_element_type_id IN NUMBER) IS
75 SELECT 1
76 FROM pay_element_types_f_tl ettl,
77 pay_element_types_f et
78 WHERE upper(translate(ettl.element_name,'x_','x '))
79 = upper(translate(p_element_name,'x_','x '))
80 AND ettl.element_type_id = et.element_type_id
81 AND ettl.language = p_language
82 AND ( et.element_type_id <> p_element_type_id OR p_element_type_id is null)
83 AND ( g_business_group_id = et.business_group_id + 0 OR g_business_group_id is null )
84 AND ( g_legislation_code = et.legislation_code OR g_legislation_code is null );
85
86 l_package_name VARCHAR2(80) := 'PAY_ELEMENT_TYPES_PKG.VALIDATE_TRANSLATION';
87 l_dummy varchar2(100);
88 l_name pay_element_types.element_name%type := element_name;
89
90 BEGIN
91
92 hr_utility.set_location (l_package_name,1);
93
94 BEGIN
95 hr_chkfmt.checkformat (l_name,
96 'PAY_NAME',
97 l_dummy, null, null, 'N', l_dummy, null);
98 hr_utility.set_location (l_package_name,2);
99 EXCEPTION
100 when app_exception.application_exception then
101 hr_utility.set_location (l_package_name,3);
102 fnd_message.set_name ('PAY','PAY_6365_ELEMENT_NO_DB_NAME'); -- checkformat failure
103 fnd_message.raise_error;
104 END;
105
106 hr_utility.set_location (l_package_name,10);
107
108 OPEN c_translation(language, element_name,element_type_id);
109 hr_utility.set_location (l_package_name,20);
110 FETCH c_translation INTO g_dummy_number;
111
112 IF c_translation%NOTFOUND THEN
113 hr_utility.set_location (l_package_name,30);
114 CLOSE c_translation;
115 ELSE
116 hr_utility.set_location (l_package_name,40);
117 CLOSE c_translation;
118 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
119 fnd_message.raise_error;
120 END IF;
121 hr_utility.set_location ('Leaving: '||l_package_name,80);
122
123 END validate_translation;
124
125 -------------------------------------------------------------------------------
126 procedure CHECK_FOR_PAYLINK_BATCHES (
127 --
128 p_element_type_id number,
129 p_element_name varchar2) is
130 --
131 -- Prevents the updating of input value names and display sequences for an
132 -- element's input values if there are paylink batch lines for the element.
133 -- Not to do so would mean that the values in the batch line would then be
134 -- associated with the wrong input values because they rely on the sequence
135 -- remaining unchanged.
136 --
137 -- Bug 2786908 : converted into a union as new version oy paylink
138 -- always populates element_type_id in pay_batch_lines. Hence 2nd
139 -- half of union can be removed at some future stage.
140 --
141 cursor csr_paylink is
142 select 1
143 from pay_batch_lines
144 where element_type_id = p_element_type_id
145 and element_type_id is not null
146 union all
147 select 1
148 from pay_batch_lines
149 where element_type_id is null
150 and upper (element_name) = upper (p_element_name);
151 --
152 begin
153 --
154 open csr_paylink;
155 fetch csr_paylink into g_dummy_number;
156 --
157 if csr_paylink%found then
158 close csr_paylink;
159 hr_utility.set_message (801, 'HR_7431_INPVAL_PAYLINK_BATCHES');
160 hr_utility.raise_error;
161 end if;
162 --
163 close csr_paylink;
164 --
165 end check_for_paylink_batches;
166 -------------------------------------------------------------------------------
167 procedure RECREATE_DB_ITEMS (
168 --
169 --******************************************************************************
170 --* Drops DB items for the element and then re-creates them. This is *
171 --* necessary if the element name is updated because the DB items use the same *
172 --* name. *
173 --******************************************************************************
174 --
175 -- The parameters to be passed in are:
176 --
177 p_element_type_id number,
178 p_effective_start_date date default to_date ('01/01/0001','DD/MM/YYYY')
179 ) is
180 --
181 begin
182 --
183 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RECREATE_DB_ITEMS',1);
184 --
185 -- Drop the DB items
186 hrdyndbi.delete_element_type_dict(p_element_type_id);
187 --
188 -- Re-create the DB items
189 hrdyndbi.create_element_type_dict(p_element_type_id,
190 p_effective_start_date);
191 --
192 -- Re-create all the input value DB items for the element
193 pay_input_values_pkg.recreate_db_items (p_element_type_id);
194 --
195 end recreate_db_items;
196 --
197 --
198 --
199 --
200 --
201 -----------------------------------------------------------------------------
202 procedure INSERT_ROW(
203 --
204 --******************************************************************************
205 --* Handles the insertion of rows into the base table for the form which is *
206 --* based on a non-updatable view. It also ensures the correct integrity of *
207 --* cascading actions is enforced. *
208 --******************************************************************************
209 --
210 -- Parameters to be passed in/out are:
211 --
212 -- The rowid and element type ID are generated by this procedure and
213 -- passed back to the form
214 p_rowid in out nocopy varchar2,
215 p_element_type_id in out nocopy number,
216 --
217 -- All the base table fields from the forms block
218 p_effective_start_date date,
219 p_effective_end_date date,
220 p_business_group_id number,
221 p_legislation_code varchar2,
222 p_formula_id number ,
223 p_input_currency_code varchar2,
224 p_output_currency_code varchar2,
225 p_classification_id number,
226 p_benefit_classification_id number,
227 p_additional_entry_allowed varchar2,
228 p_adjustment_only_flag varchar2,
229 p_closed_for_entry_flag varchar2,
230 p_element_name varchar2,
231 -- --
232 p_base_element_name varchar2,
233 -- --
234 p_indirect_only_flag varchar2,
235 p_multiple_entries_allowed varchar2,
236 p_multiply_value_flag varchar2,
237 p_post_termination_rule varchar2,
238 p_process_in_run_flag varchar2,
239 p_processing_priority number,
240 p_processing_type varchar2,
241 p_standard_link_flag varchar2,
242 p_comment_id number,
243 p_description varchar2,
244 p_legislation_subgroup varchar2,
245 p_qualifying_age number,
246 p_qualifying_length_of_service number,
247 p_qualifying_units varchar2,
248 p_reporting_name varchar2,
249 p_attribute_category varchar2,
250 p_attribute1 varchar2,
251 p_attribute2 varchar2,
252 p_attribute3 varchar2,
253 p_attribute4 varchar2,
254 p_attribute5 varchar2,
255 p_attribute6 varchar2,
256 p_attribute7 varchar2,
257 p_attribute8 varchar2,
258 p_attribute9 varchar2,
259 p_attribute10 varchar2,
260 p_attribute11 varchar2,
261 p_attribute12 varchar2,
262 p_attribute13 varchar2,
263 p_attribute14 varchar2,
264 p_attribute15 varchar2,
265 p_attribute16 varchar2,
266 p_attribute17 varchar2,
267 p_attribute18 varchar2,
268 p_attribute19 varchar2,
269 p_attribute20 varchar2,
270 p_element_information_category varchar2,
271 p_element_information1 varchar2,
272 p_element_information2 varchar2,
273 p_element_information3 varchar2,
274 p_element_information4 varchar2,
275 p_element_information5 varchar2,
276 p_element_information6 varchar2,
277 p_element_information7 varchar2,
278 p_element_information8 varchar2,
279 p_element_information9 varchar2,
280 p_element_information10 varchar2,
281 p_element_information11 varchar2,
282 p_element_information12 varchar2,
283 p_element_information13 varchar2,
284 p_element_information14 varchar2,
285 p_element_information15 varchar2,
286 p_element_information16 varchar2,
287 p_element_information17 varchar2,
288 p_element_information18 varchar2,
289 p_element_information19 varchar2,
290 p_element_information20 varchar2,
291 --
292 -- The type of element will affect further actions
293 p_non_payments_flag varchar2,
294 --
295 -- The benefits attributes may be needed for defaulting input values
296 --
297 p_default_benefit_uom varchar2,
298 p_contributions_used varchar2,
299 --
300 p_third_party_pay_only_flag varchar2,
301 p_retro_summ_ele_id number,
302 p_iterative_flag varchar2,
303 p_iterative_formula_id number,
304 p_iterative_priority number,
305 p_process_mode varchar2,
306 p_grossup_flag varchar2,
307 p_advance_indicator varchar2,
308 p_advance_payable varchar2,
309 p_advance_deduction varchar2,
310 p_process_advance_entry varchar2,
311 p_proration_group_id number,
312 --Code added by prsundar for Continous calculation enhancement
313 p_proration_formula_id number,
314 p_recalc_event_group_id number,
315 p_once_each_period_flag varchar2 default null,
316 -- Added for FLSA Dynamic Period Allocation
317 p_time_definition_type varchar2 default null,
318 p_time_definition_id varchar2 default null,
319 -- Added for Advance Pay
320 p_advance_element_type_id number default null,
321 p_deduction_element_type_id number default null) is
322 --
323 cursor csr_new_id is
324 select pay_element_types_s.nextval
325 from sys.dual;
326 --
327 cursor csr_element_rowid is
328 --
329 -- Returns the system-generated columns for return to the
330 -- form, and for use in cascading action
331 --
332 select rowid
333 from pay_element_types_f
334 where element_type_id = p_element_type_id
335 and effective_start_date = p_effective_start_date;
336 --
337 --
338 begin
339 --
340 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',1);
341 --
342 open csr_new_id;
343 fetch csr_new_id into p_element_type_id;
344 close csr_new_id;
345 --
346 insert into pay_element_types_f ( element_type_id,
347 effective_start_date,
348 effective_end_date,
349 business_group_id,
350 legislation_code,
351 formula_id,
352 input_currency_code,
353 output_currency_code,
354 classification_id,
355 benefit_classification_id,
356 additional_entry_allowed_flag,
357 adjustment_only_flag,
358 closed_for_entry_flag,
359 element_name,
360 indirect_only_flag,
361 multiple_entries_allowed_flag,
362 multiply_value_flag,
363 post_termination_rule,
364 process_in_run_flag,
365 processing_priority,
366 processing_type,
367 standard_link_flag,
368 comment_id,
369 description,
370 legislation_subgroup,
371 qualifying_age,
372 qualifying_length_of_service,
373 qualifying_units,
374 reporting_name,
375 attribute_category,
376 attribute1,
377 attribute2,
378 attribute3,
379 attribute4,
380 attribute5,
381 attribute6,
382 attribute7,
383 attribute8,
384 attribute9,
385 attribute10,
386 attribute11,
387 attribute12,
388 attribute13,
389 attribute14,
390 attribute15,
391 attribute16,
392 attribute17,
393 attribute18,
394 attribute19,
395 attribute20,
396 element_information_category,
397 element_information1,
398 element_information2,
399 element_information3,
400 element_information4,
401 element_information5,
402 element_information6,
403 element_information7,
404 element_information8,
405 element_information9,
406 element_information10,
407 element_information11,
408 element_information12,
409 element_information13,
410 element_information14,
411 element_information15,
412 element_information16,
413 element_information17,
414 element_information18,
415 element_information19,
416 element_information20,
417 created_by,
418 creation_date,
419 last_updated_by,
420 last_update_date,
421 last_update_login,
422 third_party_pay_only_flag,
423 retro_summ_ele_id,
424 iterative_flag,
425 iterative_formula_id,
426 iterative_priority,
427 process_mode,
428 grossup_flag,
429 advance_indicator,
430 advance_payable,
431 advance_deduction,
432 process_advance_entry,
433 proration_group_id,
434 proration_formula_id,
435 recalc_event_group_id,
436 once_each_period_flag,
437 time_definition_type,
438 time_definition_id,
439 advance_element_type_id,
440 deduction_element_type_id)
441 --
442 values ( p_element_type_id,
443 p_effective_start_date,
444 p_effective_end_date,
445 p_business_group_id,
446 p_legislation_code,
447 p_formula_id,
448 p_input_currency_code,
449 p_output_currency_code,
450 p_classification_id,
451 p_benefit_classification_id,
452 p_additional_entry_allowed,
453 p_adjustment_only_flag,
454 p_closed_for_entry_flag,
455 -- p_element_name,
456 -- --
457 p_base_element_name,
458 -- --
459 p_indirect_only_flag,
460 p_multiple_entries_allowed,
461 p_multiply_value_flag,
462 p_post_termination_rule,
463 p_process_in_run_flag,
464 p_processing_priority,
465 p_processing_type,
466 p_standard_link_flag,
467 p_comment_id,
468 p_description,
469 p_legislation_subgroup,
470 p_qualifying_age,
471 p_qualifying_length_of_service,
472 p_qualifying_units,
473 p_reporting_name,
474 p_attribute_category,
475 p_attribute1,
476 p_attribute2,
477 p_attribute3,
478 p_attribute4,
479 p_attribute5,
480 p_attribute6,
481 p_attribute7,
482 p_attribute8,
483 p_attribute9,
484 p_attribute10,
485 p_attribute11,
486 p_attribute12,
487 p_attribute13,
488 p_attribute14,
489 p_attribute15,
490 p_attribute16,
491 p_attribute17,
492 p_attribute18,
493 p_attribute19,
494 p_attribute20,
495 p_element_information_category,
496 p_element_information1,
497 p_element_information2,
498 p_element_information3,
499 p_element_information4,
500 p_element_information5,
501 p_element_information6,
502 p_element_information7,
503 p_element_information8,
504 p_element_information9,
505 p_element_information10,
506 p_element_information11,
507 p_element_information12,
508 p_element_information13,
509 p_element_information14,
510 p_element_information15,
511 p_element_information16,
512 p_element_information17,
513 p_element_information18,
514 p_element_information19,
515 p_element_information20,
516 c_user_id,
517 sysdate,
518 c_user_id,
519 sysdate,
520 c_login_id,
521 p_third_party_pay_only_flag,
522 p_retro_summ_ele_id,
523 p_iterative_flag,
524 p_iterative_formula_id,
525 p_iterative_priority,
526 p_process_mode,
527 p_grossup_flag,
528 p_advance_indicator,
529 p_advance_payable,
530 p_advance_deduction,
531 p_process_advance_entry,
532 p_proration_group_id,
533 p_proration_formula_id,
534 p_recalc_event_group_id,
535 p_once_each_period_flag,
536 p_time_definition_type,
537 p_time_definition_id,
538 p_advance_element_type_id,
539 p_deduction_element_type_id);
540 --
541 -- **************************************************************************
542 -- insert into MLS table (TL)
543 --
544 insert into PAY_ELEMENT_TYPES_F_TL (
545 ELEMENT_TYPE_ID,
546 ELEMENT_NAME,
547 REPORTING_NAME,
548 DESCRIPTION,
549 LAST_UPDATE_DATE,
550 LAST_UPDATED_BY,
551 LAST_UPDATE_LOGIN,
552 CREATED_BY,
553 CREATION_DATE,
554 LANGUAGE,
555 SOURCE_LANG
556 ) select
557 P_ELEMENT_TYPE_ID,
558 P_ELEMENT_NAME,
559 P_REPORTING_NAME,
560 P_DESCRIPTION,
561 sysdate,
562 c_user_id,
563 c_user_id,
564 c_login_id,
565 sysdate,
566 L.LANGUAGE_CODE,
567 userenv('LANG')
568 from FND_LANGUAGES L
569 where L.INSTALLED_FLAG in ('I', 'B')
570 and not exists
571 (select NULL
572 from PAY_ELEMENT_TYPES_F_TL T
573 where T.ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
574 and T.LANGUAGE = L.LANGUAGE_CODE);
575 --
576 --
577 -- *******************************************************************************
578 --
579 -- Return the new rowid, and retrieve the newly generated element type
580 -- identifier into the forms row to avoid needing to requery
581 --
582 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',2);
583 --
584 open csr_element_rowid;
585 fetch csr_element_rowid into p_rowid;
586 if (csr_element_rowid%notfound) then
587 close csr_element_rowid;
588 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
589 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.INSERT_ROW');
590 end if;
591 close csr_element_rowid;
592 --
593 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',3);
594 --
595 -- Create element DB items on the entity horizon
596 hrdyndbi.create_element_type_dict(p_element_type_id,
597 p_effective_start_date);
598 --
599 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',4);
600 --
601 -- Create pay value for payment type elements which will be processed in the
602 -- payroll run
603 if (p_process_in_run_flag = 'Y' and p_non_payments_flag = 'N') then
604 -- Create pay value
605 pay_input_values_pkg.insert_row (
606 -- change 115.10
607 p_base_name => 'Pay Value',
608 p_element_type_id => p_element_type_id,
609 p_effective_start_date => p_effective_start_date,
610 p_effective_end_date => p_effective_end_date,
611 p_legislation_code => p_legislation_code,
612 p_business_group_id => p_business_group_id,
613 p_legislation_subgroup => p_legislation_subgroup,
614 p_input_value_id => g_dummy_number,
615 p_rowid => g_dummy_char );
616 --
617 end if;
618 --
619 if p_contributions_used = 'Y' then
620 --
621 -- Create default benefit input values for type A benefit plans
622 --
623 -- #282294. The value of p_generate_db_items_flag in the three following
624 -- calls has been temporarily changed from Y to N. This is because the US
625 -- startup data does not currently create a US_CONTRIBUTION_VALUES route.
626 -- This value must be reset to Y when the US startup data is fixed. See the
627 -- three lines below marked ** temp change **.
628 -- Temp fix removed: values reset back to 'Y'. RMF 03.07.95.
629 --
630 pay_input_values_pkg.insert_row (
631 --
632 p_element_type_id => p_element_type_id,
633 p_effective_start_date => p_effective_start_date,
634 p_effective_end_date => p_effective_end_date,
635 p_legislation_code => p_legislation_code,
636 p_business_group_id => p_business_group_id,
637 p_legislation_subgroup => p_legislation_subgroup,
638 p_input_value_id => g_dummy_number,
639 p_rowid => g_dummy_char,
640 -- change 115.10
641 --p_name => 'Coverage',
642 p_base_name => 'Coverage',
643 p_display_sequence => 1,
644 p_hot_default_flag => 'N',
645 p_mandatory_flag => 'Y',
646 p_lookup_type => 'US_BENEFIT_COVERAGE',
647 p_generate_db_items_flag=> 'Y',
648 p_uom => 'C' );
649 --
650 pay_input_values_pkg.insert_row (
651 --
652 p_element_type_id => p_element_type_id,
653 p_effective_start_date => p_effective_start_date,
654 p_effective_end_date => p_effective_end_date,
655 p_legislation_code => p_legislation_code,
656 p_business_group_id => p_business_group_id,
657 p_legislation_subgroup => p_legislation_subgroup,
658 p_input_value_id => g_dummy_number,
659 p_rowid => g_dummy_char,
660 -- change 115.10
661 --p_name => 'ER Contr',
662 p_base_name => 'ER Contr',
663 p_display_sequence => 2,
664 p_hot_default_flag => 'N',
665 p_mandatory_flag => 'N',
666 p_generate_db_items_flag=> 'Y',
667 p_uom => p_default_benefit_uom);
668 --
669 pay_input_values_pkg.insert_row (
670 --
671 p_element_type_id => p_element_type_id,
672 p_effective_start_date => p_effective_start_date,
673 p_effective_end_date => p_effective_end_date,
674 p_legislation_code => p_legislation_code,
675 p_business_group_id => p_business_group_id,
676 p_legislation_subgroup => p_legislation_subgroup,
677 p_input_value_id => g_dummy_number,
678 p_rowid => g_dummy_char,
679 -- change 115.10
680 --p_name => 'EE Contr',
681 p_base_name => 'EE Contr',
682 p_display_sequence => 3,
683 p_hot_default_flag => 'N',
684 p_mandatory_flag => 'N',
685 p_generate_db_items_flag=> 'Y',
686 p_uom => p_default_benefit_uom);
687 --
688 end if;
689 --
690 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',5);
691 --
692 pay_sub_class_rules_pkg.insert_defaults (
693 --
694 p_element_type_id,
695 p_classification_id,
696 p_effective_start_date,
697 p_effective_end_date,
698 p_business_group_id,
699 p_legislation_code);
700 --
701 -- Populate the retro component usages for the element type.
702 --
703 pay_retro_comp_usage_internal.populate_retro_comp_usages
704 (p_effective_date => p_effective_start_date
705 ,p_element_type_id => p_element_type_id
706 );
707 --
708 end insert_row;
709 --
710 --
711 --
712 --
713 --
714 --------------------------------------------------------------------------------
715 procedure UPDATE_ROW(
716 --
717 --******************************************************************************
718 --* Handles the updating of the base table for the form which is based on a *
719 --* non-updatable view. It also ensures the integrity of data is maintained *
720 --* according to the business rules. *
721 --******************************************************************************
722 --
723 -- Parameters to be passed in are:
724 --
725 -- All base table column values
726 p_rowid varchar2,
727 p_element_type_id number,
728 p_effective_start_date date,
729 p_effective_end_date date,
730 p_business_group_id number,
731 p_legislation_code varchar2,
732 p_formula_id number,
733 p_input_currency_code varchar2,
734 p_output_currency_code varchar2,
735 p_classification_id number,
736 p_benefit_classification_id number,
737 p_additional_entry_allowed varchar2,
738 p_adjustment_only_flag varchar2,
739 p_closed_for_entry_flag varchar2,
740 p_element_name varchar2,
741 p_indirect_only_flag varchar2,
742 p_multiple_entries_allowed varchar2,
743 p_multiply_value_flag varchar2,
744 p_post_termination_rule varchar2,
745 p_process_in_run_flag varchar2,
746 p_processing_priority number,
747 p_processing_type varchar2,
748 p_standard_link_flag varchar2,
749 p_comment_id number,
750 p_description varchar2,
751 p_legislation_subgroup varchar2,
752 p_qualifying_age number,
753 p_qualifying_length_of_service number,
754 p_qualifying_units varchar2,
755 p_reporting_name varchar2,
756 p_attribute_category varchar2,
757 p_attribute1 varchar2,
758 p_attribute2 varchar2,
759 p_attribute3 varchar2,
760 p_attribute4 varchar2,
761 p_attribute5 varchar2,
762 p_attribute6 varchar2,
763 p_attribute7 varchar2,
764 p_attribute8 varchar2,
765 p_attribute9 varchar2,
766 p_attribute10 varchar2,
767 p_attribute11 varchar2,
768 p_attribute12 varchar2,
769 p_attribute13 varchar2,
770 p_attribute14 varchar2,
771 p_attribute15 varchar2,
772 p_attribute16 varchar2,
773 p_attribute17 varchar2,
774 p_attribute18 varchar2,
775 p_attribute19 varchar2,
776 p_attribute20 varchar2,
777 p_element_information_category varchar2,
778 p_element_information1 varchar2,
779 p_element_information2 varchar2,
780 p_element_information3 varchar2,
781 p_element_information4 varchar2,
782 p_element_information5 varchar2,
783 p_element_information6 varchar2,
784 p_element_information7 varchar2,
785 p_element_information8 varchar2,
786 p_element_information9 varchar2,
787 p_element_information10 varchar2,
788 p_element_information11 varchar2,
789 p_element_information12 varchar2,
790 p_element_information13 varchar2,
791 p_element_information14 varchar2,
792 p_element_information15 varchar2,
793 p_element_information16 varchar2,
794 p_element_information17 varchar2,
795 p_element_information18 varchar2,
796 p_element_information19 varchar2,
797 p_element_information20 varchar2,
798 p_third_party_pay_only_flag varchar2,
799 p_retro_summ_ele_id number,
800 p_iterative_flag varchar2,
801 p_iterative_formula_id number,
802 p_iterative_priority number,
803 p_process_mode varchar2,
804 p_grossup_flag varchar2,
805 p_advance_indicator varchar2,
806 p_advance_payable varchar2,
807 p_advance_deduction varchar2,
808 p_process_advance_entry varchar2,
809 p_proration_group_id number,
810 p_base_element_name varchar2,
811 p_proration_formula_id number,
812 p_recalc_event_group_id number,
813 p_once_each_period_flag varchar2 default null,
814 -- Added for FLSA Dynamic Period Allocation
815 p_time_definition_type varchar2 default null,
816 p_time_definition_id varchar2 default null,
817 -- Added for Advance Pay Enhancement
818 p_advance_element_type_id number default null,
819 p_deduction_element_type_id number default null
820 )
821 is
822 --
823 begin
824 --
825 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.UPDATE_ROW',1);
826 --
827 update pay_element_types_f
828 set
829 element_type_id = p_element_type_id,
830 effective_start_date = p_effective_start_date,
831 effective_end_date = p_effective_end_date,
832 business_group_id = p_business_group_id,
833 legislation_code = p_legislation_code,
834 formula_id = p_formula_id,
835 input_currency_code = p_input_currency_code,
836 output_currency_code = p_output_currency_code,
840 adjustment_only_flag = p_adjustment_only_flag,
837 classification_id = p_classification_id,
838 benefit_classification_id = p_benefit_classification_id,
839 additional_entry_allowed_flag = p_additional_entry_allowed,
841 closed_for_entry_flag = p_closed_for_entry_flag,
842 -- --
843 element_name = p_base_element_name,
844 -- --
845 indirect_only_flag = p_indirect_only_flag,
846 multiple_entries_allowed_flag = p_multiple_entries_allowed,
847 multiply_value_flag = p_multiply_value_flag,
848 post_termination_rule = p_post_termination_rule,
849 process_in_run_flag = p_process_in_run_flag,
850 processing_priority = p_processing_priority,
851 processing_type = p_processing_type,
852 standard_link_flag = p_standard_link_flag,
853 comment_id = p_comment_id,
854 description = p_description,
855 legislation_subgroup = p_legislation_subgroup,
856 qualifying_age = p_qualifying_age,
857 qualifying_length_of_service = p_qualifying_length_of_service,
858 qualifying_units = p_qualifying_units,
859 reporting_name = p_reporting_name,
860 attribute_category = p_attribute_category,
861 attribute1 = p_attribute1,
862 attribute2 = p_attribute2,
863 attribute3 = p_attribute3,
864 attribute4 = p_attribute4,
865 attribute5 = p_attribute5,
866 attribute6 = p_attribute6,
867 attribute7 = p_attribute7,
868 attribute8 = p_attribute8,
869 attribute9 = p_attribute9,
870 attribute10 = p_attribute10,
871 attribute11 = p_attribute11,
872 attribute12 = p_attribute12,
873 attribute13 = p_attribute13,
874 attribute14 = p_attribute14,
875 attribute15 = p_attribute15,
876 attribute16 = p_attribute16,
877 attribute17 = p_attribute17,
878 attribute18 = p_attribute18,
879 attribute19 = p_attribute19,
880 attribute20 = p_attribute20,
881 last_update_date = sysdate,
882 last_updated_by = c_user_id,
883 last_update_login = c_login_id,
884 element_information_category = p_element_information_category,
885 element_information1 = p_element_information1,
886 element_information2 = p_element_information2,
887 element_information3 = p_element_information3,
888 element_information4 = p_element_information4,
889 element_information5 = p_element_information5,
890 element_information6 = p_element_information6,
891 element_information7 = p_element_information7,
892 element_information8 = p_element_information8,
893 element_information9 = p_element_information9,
894 element_information10 = p_element_information10,
895 element_information11 = p_element_information11,
896 element_information12 = p_element_information12,
897 element_information13 = p_element_information13,
898 element_information14 = p_element_information14,
899 element_information15 = p_element_information15,
900 element_information16 = p_element_information16,
901 element_information17 = p_element_information17,
902 element_information18 = p_element_information18,
903 element_information19 = p_element_information19,
904 element_information20 = p_element_information20,
905 third_party_pay_only_flag = p_third_party_pay_only_flag,
906 retro_summ_ele_id = p_retro_summ_ele_id,
907 iterative_flag = p_iterative_flag,
908 iterative_formula_id = p_iterative_formula_id,
909 iterative_priority = p_iterative_priority,
910 process_mode = p_process_mode ,
911 grossup_flag = p_grossup_flag,
912 advance_indicator = p_advance_indicator,
913 advance_payable = p_advance_payable,
914 advance_deduction = p_advance_deduction,
915 process_advance_entry = p_process_advance_entry,
916 proration_group_id = p_proration_group_id,
917 proration_formula_id = p_proration_formula_id,
918 recalc_event_group_id = p_recalc_event_group_id,
919 once_each_period_flag = p_once_each_period_flag,
920 time_definition_type = p_time_definition_type,
921 time_definition_id = p_time_definition_id,
922 advance_element_type_id = p_advance_element_type_id,
923 deduction_element_type_id = p_deduction_element_type_id
924 where rowid = p_rowid;
925 --
926 if (sql%notfound) then -- trap system errors during update
927 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
928 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UPDATE_ROW');
929 end if;
930 --
931 --
932 -- ****************************************************************************************
933 --
934 -- update MLS table (TL)
935 --
936 update PAY_ELEMENT_TYPES_F_TL
937 set
938 ELEMENT_NAME = P_ELEMENT_NAME,
939 REPORTING_NAME = P_REPORTING_NAME,
940 DESCRIPTION = P_DESCRIPTION,
941 last_update_date = sysdate,
942 last_updated_by = c_user_id,
943 last_update_login = c_login_id,
944 SOURCE_LANG = userenv('LANG')
945 where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
946 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
947 --
948 --
949 if (sql%notfound) then -- trap system errors during update
950 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
951 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UPDATE_TL_ROW');
952 end if;
953 --
954 -- ***************************************************************************************
955 --
956 end update_row;
957 --
958 --
959 --
960 --
961 --
962 --------------------------------------------------------------------------------
963 procedure DELETE_ROW (
964 --
965 --*********************************************************
966 --* Performs deletion of an element type and its children *
967 --*********************************************************
968 --
969 -- Parameters are:
970 --
971 p_element_type_id number,
972 p_rowid varchar2,
973 --
974 -- The priority of the element for integrity checks
975 p_processing_priority number,
976 --
977 -- The type of deletion action being performed (Date Track)
978 p_delete_mode varchar2 default 'DELETE',
979 --
980 -- The effective date
981 p_session_date date default trunc (sysdate),
982 --
983 -- The validation period for integrity checks
984 p_validation_start_date date
985 default to_date ('01/01/0001','DD/MM/YYYY'),
986 p_validation_end_date date
987 default to_date ('31/12/4712','DD/MM/YYYY')
988 --
989 --
990 ) is
991 --
992 begin
993 --
994 if deletion_allowed ( p_element_type_id,
995 p_processing_priority,
996 p_validation_start_date,
997 p_validation_end_date,
998 p_delete_mode ) then
999 --
1000 -- Cascade deletion through child entities
1001 --
1002 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',1);
1003 --
1004 pay_input_values_pkg.parent_deleted ( p_element_type_id,
1005 p_session_date,
1006 p_validation_start_date,
1007 p_validation_end_date,
1008 p_delete_mode );
1009 --
1010 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',2);
1011 --
1012 ben_benefit_contributions_pkg.parent_deleted ( p_element_type_id,
1013 p_delete_mode,
1014 p_session_date,
1015 c_base_table );
1016 --
1017 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',3);
1018 --
1019 pay_sub_class_rules_pkg.parent_deleted ( p_element_type_id,
1020 p_session_date,
1021 p_validation_start_date,
1022 p_validation_end_date,
1023 p_delete_mode,
1024 c_base_table );
1025 --
1026 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',4);
1027 --
1028 pay_status_rules_pkg.parent_deleted ( p_element_type_id,
1029 p_session_date,
1030 p_delete_mode );
1031 if p_delete_mode = 'ZAP' then
1032 -- We need to delete the database items for the deleted element
1033 hrdyndbi.delete_element_type_dict (p_element_type_id);
1034 --
1035 -- We need to delete any payroll frequency rules for the element
1036 --
1037 delete from pay_ele_payroll_freq_rules
1038 where element_type_id = p_element_type_id;
1039 --
1040 -- Delete the child retro component usages.
1041 --
1042 pay_retro_comp_usage_internal.delete_child_retro_comp_usages
1043 (p_effective_date => p_session_date
1044 ,p_element_type_id => p_element_type_id
1045 );
1046 -- Bug 14189892 Need to delete entries in pay_element_span_usages
1047 -- when date mode is ZAP
1048 delete from pay_element_span_usages
1049 where RETRO_ELEMENT_TYPE_ID = p_element_type_id;
1050 hr_utility.trace('Deleted entry in PAY_ELEMENT_SPAN_USAGES with RETRO_ELEMENT_TYPE_ID ' || p_element_type_id );
1051
1052 elsif p_delete_mode = 'DELETE' then
1053 --
1054 -- We need to remove any payroll frequency rules starting after the new end
1055 -- date
1056 --
1060 --
1057 delete from pay_ele_payroll_freq_rules
1058 where element_type_id = p_element_type_id
1059 and start_date > p_session_date;
1061 end if;
1062 --
1063 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',5);
1064 --
1065 -- Delete the element itself
1066 delete
1067 from pay_element_types_f
1068 where rowid = p_rowid;
1069 --
1070 if sql%notfound then -- trap system errors during deletion
1071 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1072 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.DELETE_ROW');
1073 end if;
1074 --
1075 -- ****************************************************************************
1076 --
1077 -- bugfix 1229606
1078 -- only delete data from the translated tables if the date track mode is ZAP,
1079 -- for all other date track modes the data should remain untouched
1080 --
1081 if p_delete_mode = 'ZAP' then
1082 --
1083 -- delete from MLS table (TL)
1084 --
1085 delete from PAY_ELEMENT_TYPES_F_TL
1086 where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID;
1087 --
1088 if sql%notfound then -- trap system errors during deletion
1089 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1090 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.DELETE_TL_ROW');
1091 end if;
1092
1093 end if;
1094 --
1095 -- ****************************************************************************
1096 --
1097 end if;
1098 --
1099 end delete_row;
1100 --
1101 --
1102 --
1103 --
1104 --
1105 --------------------------------------------------------------------------------
1106 procedure LOCK_ROW(
1107 --
1108 --******************************************************************************
1109 --* Handles row-locking on the base table for the form which is based on a view*
1110 --******************************************************************************
1111 --
1112 -- Parameters are:
1113 -- All base table columns
1114 p_rowid varchar2,
1115 p_element_type_id number,
1116 p_effective_start_date date,
1117 p_effective_end_date date,
1118 p_business_group_id number,
1119 p_legislation_code varchar2,
1120 p_formula_id number,
1121 p_input_currency_code varchar2,
1122 p_output_currency_code varchar2,
1123 p_classification_id number,
1124 p_benefit_classification_id number,
1125 p_additional_entry_allowed varchar2,
1126 p_adjustment_only_flag varchar2,
1127 p_closed_for_entry_flag varchar2,
1128 -- p_element_name varchar2,
1129 p_base_element_name varchar2,
1130 p_indirect_only_flag varchar2,
1131 p_multiple_entries_allowed varchar2,
1132 p_multiply_value_flag varchar2,
1133 p_post_termination_rule varchar2,
1134 p_process_in_run_flag varchar2,
1135 p_processing_priority number,
1136 p_processing_type varchar2,
1137 p_standard_link_flag varchar2,
1138 p_comment_id number,
1139 p_description varchar2,
1140 p_legislation_subgroup varchar2,
1141 p_qualifying_age number,
1142 p_qualifying_length_of_service number,
1143 p_qualifying_units varchar2,
1144 p_reporting_name varchar2,
1145 p_attribute_category varchar2,
1146 p_attribute1 varchar2,
1147 p_attribute2 varchar2,
1148 p_attribute3 varchar2,
1149 p_attribute4 varchar2,
1150 p_attribute5 varchar2,
1151 p_attribute6 varchar2,
1152 p_attribute7 varchar2,
1153 p_attribute8 varchar2,
1154 p_attribute9 varchar2,
1155 p_attribute10 varchar2,
1156 p_attribute11 varchar2,
1157 p_attribute12 varchar2,
1158 p_attribute13 varchar2,
1159 p_attribute14 varchar2,
1160 p_attribute15 varchar2,
1161 p_attribute16 varchar2,
1162 p_attribute17 varchar2,
1163 p_attribute18 varchar2,
1164 p_attribute19 varchar2,
1165 p_attribute20 varchar2,
1166 p_element_information_category varchar2,
1167 p_element_information1 varchar2,
1168 p_element_information2 varchar2,
1169 p_element_information3 varchar2,
1170 p_element_information4 varchar2,
1171 p_element_information5 varchar2,
1172 p_element_information6 varchar2,
1173 p_element_information7 varchar2,
1174 p_element_information8 varchar2,
1175 p_element_information9 varchar2,
1176 p_element_information10 varchar2,
1177 p_element_information11 varchar2,
1181 p_element_information15 varchar2,
1178 p_element_information12 varchar2,
1179 p_element_information13 varchar2,
1180 p_element_information14 varchar2,
1182 p_element_information16 varchar2,
1183 p_element_information17 varchar2,
1184 p_element_information18 varchar2,
1185 p_element_information19 varchar2,
1186 p_element_information20 varchar2,
1187 p_third_party_pay_only_flag varchar2,
1188 p_retro_summ_ele_id number,
1189 p_iterative_flag varchar2,
1190 p_iterative_formula_id number,
1191 p_iterative_priority number,
1192 p_process_mode varchar2,
1193 p_grossup_flag varchar2,
1194 p_advance_indicator varchar2,
1195 p_advance_payable varchar2,
1196 p_advance_deduction varchar2,
1197 p_process_advance_entry varchar2,
1198 p_proration_group_id number,
1199 p_proration_formula_id number,
1200 p_recalc_event_group_id number,
1201 p_once_each_period_flag varchar2 default null,
1202 -- Added for FLSA Dynamic Period Allocation
1203 p_time_definition_type varchar2 default null,
1204 p_time_definition_id varchar2 default null,
1205 -- Added for Advance Pay Enhancement
1206 p_advance_element_type_id number default null,
1207 p_deduction_element_type_id number default null
1208 ) is
1209 --
1210 cursor csr_element_type is
1211 select *
1212 from pay_element_types_f
1213 where rowid = p_rowid
1214 for update of element_type_id nowait;
1215 --
1216 element_record csr_element_type%rowtype;
1217 --
1218 -- ***************************************************************************
1219 -- cursor for MLS
1220 --
1221 cursor csr_element_type_tl is
1222 select ELEMENT_NAME,
1223 REPORTING_NAME,
1224 DESCRIPTION,
1225 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
1226 from PAY_ELEMENT_TYPES_F_TL
1227 where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
1228 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1229 for update of ELEMENT_TYPE_ID nowait;
1230 --
1231 -- ***************************************************************************
1232 --
1233 -- Counter to check for locked MLS rows
1234 --
1235 l_count NUMBER := 0;
1236 --
1237 -- Bug 6411503. l_time_definition_type added. If it is N then it will be changed to NULL
1238 l_time_definition_type pay_element_types_f.time_definition_type%type;
1239 --
1240 begin
1241 --
1242 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LOCK_ROW',10);
1243 --
1244 -- Fetching the row effectively locks it because of the 'for update' clause
1245 open csr_element_type;
1246 fetch csr_element_type into element_record;
1247 --
1248 if (csr_element_type%notfound) then -- Trap system errors
1249 close csr_element_type;
1250 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1251 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.LOCK_ROW');
1252 end if;
1253 --
1254 close csr_element_type;
1255 --
1256 /** sbilling **/
1257 -- removed explicit lock of _TL table,
1258 -- the MLS strategy requires that the base table is locked before update of the
1259 -- _TL table can take place,
1260 -- which implies it is not necessary to lock both tables.
1261 --
1262 -- ***************************************************************************
1263 -- code for MLS
1264 --
1265 --for tlinfo in csr_element_type_tl LOOP
1266 -- l_count := l_count+1;
1267 -- hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LOCK_ROW:'||to_char(l_count),15);
1268 -- if (tlinfo.BASELANG = 'Y') then
1269 -- if ( (tlinfo.ELEMENT_NAME = P_ELEMENT_NAME)
1270 -- AND ((tlinfo.REPORTING_NAME = P_REPORTING_NAME)
1271 -- OR ((tlinfo.REPORTING_NAME is null) AND (P_REPORTING_NAME is null)))
1272 -- AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
1273 -- OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION is null)))
1274 -- ) then
1275 -- null;
1276 -- else
1277 -- hr_utility.set_message('fnd', 'form_record_changed');
1278 -- hr_utility.raise_error;
1279 -- end if;
1280 -- end if;
1281 --end loop;
1282 --IF(l_count=0) THEN -- We have data missing from the _TL table.
1283 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1284 -- hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.LOCK_ROW');
1285 --end if;
1286 --
1287 -- ***************************************************************************
1288 --
1289 /** sbilling **/
1290 -- combined if statements,
1291 -- causes locking error,
1292 -- ie. if first set of items match then a 'return' is issued
1293 --
1294 -- The following two IF statements are logically a single
1295 -- statement split into two parts because its length exceeds
1296 -- parser stack limitations. It checks to see if any column
1297 -- showing in the form has been changed by another user prior
1298 -- to this user changing it and since the row was queried
1299 --
1300 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LOCK_ROW',20);
1301 --
1302
1303 -- Bug 6411503. Checking for time_definition_type
1304 select DECODE(element_record.time_definition_type, 'N', NULL, element_record.TIME_DEFINITION_TYPE)
1305 into l_time_definition_type
1306 from dual;
1307
1308 if
1309 --
1310 ((element_record.additional_entry_allowed_flag = p_additional_entry_allowed)
1311 or (element_record.additional_entry_allowed_flag is null and p_additional_entry_allowed is null))
1312 --
1313 and ((element_record.third_party_pay_only_flag = p_third_party_pay_only_flag) or (element_record.third_party_pay_only_flag is null and p_third_party_pay_only_flag is null))
1314 --
1315 and ((element_record.retro_summ_ele_id = p_retro_summ_ele_id) or (element_record.retro_summ_ele_id is null and p_retro_summ_ele_id is null))
1316 --
1317 and ((element_record.adjustment_only_flag = p_adjustment_only_flag) or (element_record.adjustment_only_flag is null and p_adjustment_only_flag is null))
1318 --
1319 and ((element_record.attribute1 = p_attribute1) or (element_record.attribute1 is null and p_attribute1 is null))
1320 --
1321 and ((element_record.attribute10 = p_attribute10) or (element_record.attribute10 is null and p_attribute10 is null))
1322 --
1323 and ((element_record.attribute11 = p_attribute11) or (element_record.attribute11 is null and p_attribute11 is null))
1324 --
1325 and ((element_record.attribute12 = p_attribute12) or (element_record.attribute12 is null and p_attribute12 is null))
1326 --
1327 and ((element_record.attribute13 = p_attribute13) or (element_record.attribute13 is null and p_attribute13 is null))
1328 --
1329 and ((element_record.attribute14 = p_attribute14) or (element_record.attribute14 is null and p_attribute14 is null))
1330 --
1331 and ((element_record.attribute15 = p_attribute15) or (element_record.attribute15 is null and p_attribute15 is null))
1332 --
1333 and ((element_record.attribute16 = p_attribute16) or (element_record.attribute16 is null and p_attribute16 is null))
1334 --
1335 and ((element_record.attribute17 = p_attribute17) or (element_record.attribute17 is null and p_attribute17 is null))
1336 --
1337 and ((element_record.attribute18 = p_attribute18) or (element_record.attribute18 is null and p_attribute18 is null))
1338 --
1339 and ((element_record.attribute19 = p_attribute19) or (element_record.attribute19 is null and p_attribute19 is null))
1340 --
1341 and ((element_record.attribute2 = p_attribute2) or (element_record.attribute2 is null and p_attribute2 is null))
1342 --
1343 and ((element_record.attribute20 = p_attribute20) or (element_record.attribute20 is null and p_attribute20 is null))
1344 --
1345 and ((element_record.attribute3 = p_attribute3) or (element_record.attribute3 is null and p_attribute3 is null))
1346 --
1347 and ((element_record.attribute4 = p_attribute4) or (element_record.attribute4 is null and p_attribute4 is null))
1348 --
1349 and ((element_record.attribute5 = p_attribute5) or (element_record.attribute5 is null and p_attribute5 is null))
1350 --
1351 and ((element_record.attribute6 = p_attribute6) or (element_record.attribute6 is null and p_attribute6 is null))
1352 --
1353 and ((element_record.attribute7 = p_attribute7) or (element_record.attribute7 is null and p_attribute7 is null))
1354 --
1355 and ((element_record.attribute8 = p_attribute8) or (element_record.attribute8 is null and p_attribute8 is null))
1356 --
1357 and ((element_record.attribute9 = p_attribute9) or (element_record.attribute9 is null and p_attribute9 is null))
1358 --
1359 and ((element_record.attribute_category = p_attribute_category) or (element_record.attribute_category is null and p_attribute_category is null))
1360 --
1361 and ((element_record.benefit_classification_id = p_benefit_classification_id) or (element_record.benefit_classification_id is null and p_benefit_classification_id is null))
1362 --
1363 and ((element_record.business_group_id = p_business_group_id) or (element_record.business_group_id is null and p_business_group_id is null))
1364 --
1365 and ((element_record.classification_id = p_classification_id) or (element_record.classification_id is null and p_classification_id is null))
1366 --
1367 and ((element_record.closed_for_entry_flag = p_closed_for_entry_flag) or (element_record.closed_for_entry_flag is null and p_closed_for_entry_flag is null))
1368 --
1369 and ((element_record.comment_id = p_comment_id) or (element_record.comment_id is null and p_comment_id is null))
1370 --
1371 and ((element_record.effective_end_date = p_effective_end_date) or (element_record.effective_end_date is null and p_effective_end_date is null))
1372 --
1373 and ((element_record.effective_start_date = p_effective_start_date) or (element_record.effective_start_date is null and p_effective_start_date is null))
1374 --
1375 and ((element_record.element_information1 = p_element_information1) or (element_record.element_information1 is null and p_element_information1 is null))
1376 --
1377 and ((element_record.element_information10 = p_element_information10) or (element_record.element_information10 is null and p_element_information10 is null))
1378 --
1379 and ((element_record.element_information11 = p_element_information11) or (element_record.element_information11 is null and p_element_information11 is null))
1380 --
1381 and ((element_record.element_information12 = p_element_information12) or (element_record.element_information12 is null and p_element_information12 is null))
1382 --
1383 and ((element_record.element_information13 = p_element_information13) or (element_record.element_information13 is null and p_element_information13 is null))
1384 --
1385 and ((element_record.element_information14 = p_element_information14) or (element_record.element_information14 is null and p_element_information14 is null))
1386 --
1387 and ((element_record.element_information15 = p_element_information15) or (element_record.element_information15 is null and p_element_information15 is null))
1388 --
1389 and ((element_record.element_information16 = p_element_information16) or (element_record.element_information16 is null and p_element_information16 is null))
1390 --
1391 and ((element_record.element_information17 = p_element_information17) or (element_record.element_information17 is null and p_element_information17 is null))
1392 --
1393 and ((element_record.element_information18 = p_element_information18) or (element_record.element_information18 is null and p_element_information18 is null))
1394 --
1395 and ((element_record.element_information19 = p_element_information19) or (element_record.element_information19 is null and p_element_information19 is null))
1396 --
1397 and ((element_record.element_information2 = p_element_information2) or (element_record.element_information2 is null and p_element_information2 is null))
1398 --
1399 --then
1400 -- return;
1401 -- else
1402 -- hr_utility.set_message('fnd', 'form_record_changed');
1403 -- hr_utility.raise_error;
1404 --end if;
1405 --
1406 --hr_utility.set_location ('pay_element_types_pkg.lock_row',30);
1407 --
1408 --if
1409 --
1410 --((element_record.element_information20 = p_element_information20)
1411 and ((element_record.element_information20 = p_element_information20)
1412 or (element_record.element_information20 is null and p_element_information20 is null))
1413 --
1414 and ((element_record.element_information3 = p_element_information3) or (element_record.element_information3 is null and p_element_information3 is null))
1415 --
1416 and ((element_record.element_information4 = p_element_information4) or (element_record.element_information4 is null and p_element_information4 is null))
1417 --
1418 and ((element_record.element_information5 = p_element_information5) or (element_record.element_information5 is null and p_element_information5 is null))
1419 --
1420 and ((element_record.element_information6 = p_element_information6) or (element_record.element_information6 is null and p_element_information6 is null))
1421 --
1422 and ((element_record.element_information7 = p_element_information7) or (element_record.element_information7 is null and p_element_information7 is null))
1423 --
1424 and ((element_record.element_information8 = p_element_information8)
1425 or (element_record.element_information8 is null and p_element_information8 is null))
1426 --
1427 and ((element_record.element_information9 = p_element_information9) or (element_record.element_information9 is null and p_element_information9 is null))
1428 --
1429 and ((element_record.element_information_category = p_element_information_category) or (element_record.element_information_category is null and p_element_information_category is null))
1430 --and ((element_record.element_name = p_element_name) or (element_record.element_name is null and p_element_name is null))
1431 -- --
1432 and ((element_record.element_name = p_base_element_name) or (element_record.element_name is null and p_base_element_name is null))
1433 -- --
1434 and ((element_record.element_type_id = p_element_type_id) or (element_record.element_type_id is null and p_element_type_id is null))
1435 --
1436 and ((element_record.formula_id = p_formula_id) or (element_record.formula_id is null and p_formula_id is null))
1437 --
1438 and ((element_record.indirect_only_flag = p_indirect_only_flag) or (element_record.indirect_only_flag is null and p_indirect_only_flag is null))
1439 --
1440 and ((element_record.input_currency_code = p_input_currency_code) or (element_record.input_currency_code is null and p_input_currency_code is null))
1441 --
1442 and ((element_record.legislation_code = p_legislation_code) or (element_record.legislation_code is null and p_legislation_code is null))
1443 --
1444 and ((element_record.legislation_subgroup = p_legislation_subgroup) or (element_record.legislation_subgroup is null and p_legislation_subgroup is null))
1445 --
1446 and ((element_record.multiple_entries_allowed_flag = p_multiple_entries_allowed) or (element_record.multiple_entries_allowed_flag is null and p_multiple_entries_allowed is null))
1447 --
1451 --
1448 and ((element_record.multiply_value_flag = p_multiply_value_flag) or (element_record.multiply_value_flag is null and p_multiply_value_flag is null))
1449 --
1450 and ((element_record.output_currency_code = p_output_currency_code) or (element_record.output_currency_code is null and p_output_currency_code is null))
1452 and ((element_record.post_termination_rule = p_post_termination_rule) or (element_record.post_termination_rule is null and p_post_termination_rule is null))
1453 --
1454 and ((element_record.processing_priority = p_processing_priority) or (element_record.processing_priority is null and p_processing_priority is null))
1455 --
1456 and ((element_record.processing_type = p_processing_type) or (element_record.processing_type is null and p_processing_type is null))
1457 --
1458 and ((element_record.process_in_run_flag = p_process_in_run_flag) or (element_record.process_in_run_flag is null and p_process_in_run_flag is null))
1459 --
1460 and ((element_record.qualifying_age = p_qualifying_age) or (element_record.qualifying_age is null and p_qualifying_age is null))
1461 --
1462 and ((element_record.qualifying_length_of_service = p_qualifying_length_of_service) or (element_record.qualifying_length_of_service is null and p_qualifying_length_of_service is null))
1463 --
1464 and ((element_record.qualifying_units = p_qualifying_units) or (element_record.qualifying_units is null and p_qualifying_units is null))
1465 --
1466 and ((element_record.iterative_flag = p_iterative_flag) or (element_record.iterative_flag is null and p_iterative_flag is null))
1467 --
1468 and ((element_record.iterative_formula_id = p_iterative_formula_id) or (element_record.iterative_formula_id is null and p_iterative_formula_id is null))
1469 --
1470 and ((element_record.iterative_priority = p_iterative_priority) or (element_record.iterative_priority is null and p_iterative_priority is null))
1471 --
1472 and ((element_record.process_mode = p_process_mode) or (element_record.process_mode is null and p_process_mode is null))
1473 --
1474 and ((element_record.grossup_flag = p_grossup_flag) or (element_record.grossup_flag is null and p_grossup_flag is null))
1475 --
1476 and ((element_record.standard_link_flag = p_standard_link_flag) or (element_record.standard_link_flag is null and p_standard_link_flag is null))
1477 --
1478 and ((element_record.advance_indicator = p_advance_indicator) or (element_record.advance_indicator is null and p_advance_indicator is null))
1479 --
1480 and ((element_record.advance_payable = p_advance_payable) or (element_record.advance_payable is null and p_advance_payable is null))
1481 --
1482 and ((element_record.advance_deduction = p_advance_deduction) or (element_record.advance_deduction is null and p_advance_deduction is null))
1483 --
1484 and ((element_record.process_advance_entry = p_process_advance_entry) or (element_record.process_advance_entry is null and p_process_advance_entry is null))
1485 --
1486 and ((element_record.proration_group_id = p_proration_group_id) or (element_record.proration_group_id is null and p_proration_group_id is null))
1487 --
1488 and ((element_record.once_each_period_flag = p_once_each_period_flag) or (element_record.once_each_period_flag is null and p_once_each_period_flag is null))
1489 --
1490 and ((l_time_definition_type= p_time_definition_type) or (l_time_definition_type is null and p_time_definition_type is null)) -- Bug 6411503
1491 --
1492 and ((element_record.time_definition_id = p_time_definition_id) or (element_record.time_definition_id is null and p_time_definition_id is null))
1493 --
1494 and ((element_record.advance_element_type_id = p_advance_element_type_id) or (element_record.advance_element_type_id is null and p_advance_element_type_id is null))
1495 --
1496 and ((element_record.deduction_element_type_id = p_deduction_element_type_id) or (element_record.deduction_element_type_id is null and p_deduction_element_type_id is null))
1497 --
1498 then
1499 return;
1500 else
1501 hr_utility.set_message(0, 'FORM_RECORD_CHANGED');
1502 hr_utility.raise_error;
1503 end if;
1504 --
1505 end lock_row;
1506 -----------------------------------------------------------------------
1507 function DATE_EFFECTIVELY_UPDATED (
1508 --
1509 --******************************************************************************
1510 --* Returns TRUE if more than one row exists with the parameter element type ID*
1511 --******************************************************************************
1512 --
1513 -- parameters are:
1514 --
1515 p_element_type_id number,
1516 p_rowid varchar2,
1517 p_error_if_true boolean default FALSE
1518 --
1519 ) return boolean is
1520 --
1521 v_dated_updates boolean := FALSE;
1522 --
1523 cursor csr_dated_updates is
1524 select 1
1525 from pay_element_types_f
1526 where element_type_id = p_element_type_id
1527 and rowid <> p_rowid;
1528 --
1529 begin
1530 --
1531 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DATE_EFFECTIVELY_UPDATED',1);
1532 --
1533 open csr_dated_updates;
1534 fetch csr_dated_updates into g_dummy_number;
1535 v_dated_updates := csr_dated_updates%found;
1536 close csr_dated_updates;
1537 --
1538 if v_dated_updates and p_error_if_true then
1539 hr_utility.set_message (801,'PAY_6460_ELEMENT_NO_PROC_CORR');
1540 hr_utility.raise_error;
1541 end if;
1542 --
1543 return v_dated_updates;
1544 --
1545 end date_effectively_updated;
1546 -----------------------------------------------------------------------
1547 function STOP_ENTRY_RULES_EXIST (
1548 --
1552 --* source element type
1549 --*****************************************************************************
1550 --* Returns TRUE if there are existing formula result rules for the parameter *
1551 --* element type, whose type is stop-entry, and which are not targetting the *
1553 --*****************************************************************************
1554 --
1555 -- Parameters are:
1556 --
1557 p_element_type_id number,
1558 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
1559 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
1560 p_error_if_true boolean default FALSE
1561 --
1562 ) return boolean is
1563 --
1564 v_stop_entry_rules boolean;
1565 --
1566 cursor csr_stop_entry_rules is
1567 select null
1568 from pay_formula_result_rules_f FRR,
1569 pay_status_processing_rules_f SPR
1570 where p_element_type_id = frr.element_type_id
1571 and frr.result_rule_type = 'S'
1572 and spr.STATUS_PROCESSING_RULE_ID = frr.STATUS_PROCESSING_RULE_ID
1573 and spr.element_type_id <> p_element_type_id
1574 and spr.effective_start_date between p_validation_start_date
1575 and p_validation_end_date
1576 and frr.effective_start_date between p_validation_start_date
1577 and p_validation_end_date;
1578 begin
1579 --
1580 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.STOP_ENTRY_RULES_EXIST',1);
1581 --
1582 open csr_stop_entry_rules;
1583 fetch csr_stop_entry_rules into g_dummy_number;
1584 v_stop_entry_rules := csr_stop_entry_rules%found;
1585 close csr_stop_entry_rules;
1586 --
1587 if v_stop_entry_rules and p_error_if_true then
1588 hr_utility.set_message (801,'PAY_6157_ELEMENT_NO_DEL_FRR');
1589 hr_utility.raise_error;
1590 end if;
1591 --
1592 return v_stop_entry_rules;
1593 --
1594 end stop_entry_rules_exist;
1595 -----------------------------------------------------------------------
1596 function RUN_RESULTS_EXIST (
1597 --
1598 --************************************************************************
1599 --* Returns TRUE if run results exist for the parameter element type, in *
1600 --* payroll actions during the validation period *
1601 --* The driving cursors have been rewritten to allow for the removal of *
1602 --* the element_type_id index on pay_run_results. The function now *
1603 --* relies on their being run result values present for any input value *
1604 --* if the element would have run results. The only current exception *
1605 --* to this would be for elements with no input values. This is handled *
1606 --* in a different, less performant cursor. *
1607 --************************************************************************
1608 --
1609 -- Parameters are:
1610 --
1611 p_element_type_id number,
1612 p_validation_start_date date default to_date ('01/01/0001',
1613 'DD/MM/YYYY'),
1614 p_validation_end_date date default to_date ('31/12/4712',
1615 'DD/MM/YYYY'),
1616 p_DML_action_being_checked varchar2 default 'UPDATE',
1617 p_error_if_true boolean default FALSE
1618 --
1619 ) return boolean is
1620 --
1621 v_run_results_exist boolean;
1622 v_input_values_exist boolean;
1623 v_input_value_id pay_input_values_f.input_value_id%type;
1624 --
1625 cursor csr_input_values is
1626 select iv.input_value_id
1627 from pay_input_values_f iv
1628 where iv.element_type_id = p_element_type_id;
1629
1630 cursor csr_run_result_values (p_input_value_id NUMBER) is
1631 select 1
1632 from dual
1633 where exists
1634 (select /*+ ORDERED INDEX(RESULT PAY_RUN_RESULTS_PK)
1635 USE_NL(RESULT ASSIGN PAYROLL) */ 1
1636 from pay_run_result_values VALUE,
1637 pay_run_results RESULT,
1638 pay_assignment_actions ASSIGN,
1639 pay_payroll_actions PAYROLL
1640 where value.run_result_id = result.run_result_id
1641 and assign.assignment_action_id = result.assignment_action_id
1642 and assign.payroll_action_id = payroll.payroll_action_id
1643 and value.input_value_id = p_input_value_id
1644 and payroll.effective_date between p_validation_start_date
1645 and p_validation_end_date);
1646
1647 cursor csr_run_results is
1648 select 1
1649 from dual
1650 where exists
1651 (select /*+ INDEX(PAYROLL PAY_PAYROLL_ACTIONS_PK)
1652 INDEX(ASSIGN PAY_ASSIGNMENT_ACTIONS_PK) */ 1
1653 from pay_run_results RUN,
1654 pay_payroll_actions PAYROLL,
1655 pay_assignment_actions ASSIGN
1656 where run.element_type_id = p_element_type_id
1657 and assign.assignment_action_id = run.assignment_action_id
1658 and assign.payroll_action_id = payroll.payroll_action_id
1659 and payroll.effective_date between p_validation_start_date
1660 and p_validation_end_date);
1661
1662 cursor csr_default_run_result_values (p_input_value_id NUMBER) is
1663 select 1
1664 from dual
1665 where exists
1666 (select 1
1670 --
1667 from pay_run_result_values VALUE
1668 where value.input_value_id = p_input_value_id);
1669
1671 begin
1672 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',1);
1673 /* Check if the element type has any input values */
1674
1675 open csr_input_values;
1676 fetch csr_input_values into v_input_value_id;
1677 v_input_values_exist := csr_input_values%found;
1678 close csr_input_values;
1679
1680 /* If input values exist use the input value to check if run result
1681 values exists. */
1682
1683 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',5);
1684
1685 if v_input_values_exist then
1686 if ((p_validation_start_date = to_date ('01/01/0001','DD/MM/YYYY'))
1687 AND
1688 (p_validation_end_date = to_date ('31/12/4712','DD/MM/YYYY'))) then
1689 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',121);
1690 open csr_default_run_result_values(v_input_value_id);
1691 fetch csr_default_run_result_values into g_dummy_number;
1692 v_run_results_exist := csr_default_run_result_values%found;
1693 close csr_default_run_result_values;
1694
1695 else
1696
1697 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',122);
1698
1699 open csr_run_result_values(v_input_value_id);
1700 fetch csr_run_result_values into g_dummy_number;
1701 v_run_results_exist := csr_run_result_values%found;
1702 close csr_run_result_values;
1703 end if;
1704
1705 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',10);
1706 open csr_run_result_values(v_input_value_id);
1707 fetch csr_run_result_values into g_dummy_number;
1708 v_run_results_exist := csr_run_result_values%found;
1709 close csr_run_result_values;
1710 else
1711 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',15);
1712 open csr_run_results;
1713 fetch csr_run_results into g_dummy_number;
1714 v_run_results_exist := csr_run_results%found;
1715 close csr_run_results;
1716 end if;
1717
1718 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',20);
1719 if v_run_results_exist and p_error_if_true then
1720 --
1721 if p_DML_action_being_checked = 'UPDATE' then
1722 hr_utility.set_message (801,'PAY_6909_ELEMENT_NO_UPD_RR');
1723 --
1724 elsif p_DML_action_being_checked = 'DELETE' then
1725 hr_utility.set_message (801,'PAY_6242_ELEMENTS_NO_DEL_RR');
1726 --
1727 end if;
1728 --
1729 hr_utility.raise_error;
1730 --
1731 end if;
1732 --
1733 return v_run_results_exist;
1734 --
1735 end run_results_exist;
1736 -----------------------------------------------------------------------
1737 function FED_BY_INDIRECT_RESULTS (
1738 --
1739 --*****************************************************************************
1740 --* Returns TRUE if the parameter element type has input values which are fed *
1741 --* by results from other element types' input values *
1742 --*****************************************************************************
1743 --
1744 -- Parameters are:
1745 --
1746 p_element_type_id number,
1747 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
1748 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
1749 p_error_if_true boolean default FALSE
1750 ) return boolean is
1751 --
1752 v_fed_by_indirect_results boolean;
1753 --
1754 begin
1755 --
1756 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.FED_BY_INDIRECT_RESULTS',1);
1757 --
1758 -- Find formula result rules of type Indirect ('I')
1759 open g_csr_result_rules ( p_element_type_id,
1760 p_validation_start_date,
1761 p_validation_end_date,
1762 'I' );
1763 --
1764 fetch g_csr_result_rules into g_dummy_number;
1765 v_fed_by_indirect_results := g_csr_result_rules%found;
1766 close g_csr_result_rules;
1767 --
1768 -- bug 374841.Invalid message number 69012 changed to 6912.19-JUN-1996 mlisieck
1769 if v_fed_by_indirect_results and p_error_if_true then
1770 hr_utility.set_message (801,'PAY_6912_ELEMENT_NO_FRR_UPD');
1771 hr_utility.raise_error;
1772 end if;
1773 --
1774 return v_fed_by_indirect_results;
1775 --
1776 end fed_by_indirect_results;
1777 -----------------------------------------------------------------------
1778 function UPDATE_RECURRING_RULES_EXIST (
1779 --
1780 --**************************************************************************
1781 --* Returns TRUE if the parameter element type has input values which are *
1782 --* subject to result rules of type update-recurring during the validation *
1783 --* period, and the source element is different from the target element. *
1784 --**************************************************************************
1785 --
1786 -- Parameters are:
1787 --
1788 p_element_type_id number,
1789 p_validation_start_date date,
1790 p_validation_end_date date,
1791 p_error_if_true boolean default FALSE
1792 --
1793 ) return boolean is
1794 --
1795 v_update_recurring boolean := FALSE;
1796 v_status_processing_rule_id number;
1797 v_element_type_id number;
1798 --
1799 cursor csr_source_element is
1800 select element_type_id
1801 from pay_status_processing_rules_f
1802 where element_type_id = v_element_type_id
1806 --
1803 and status_processing_rule_id = v_status_processing_rule_id;
1804 --
1805 begin
1807 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.UPDATE_RECURRING_RULES_EXIST',1);
1808 --
1809 -- Find formula result rules of type Update Recurring ('U')
1810 open g_csr_result_rules ( p_element_type_id,
1811 p_validation_start_date,
1812 p_validation_end_date,
1813 p_rule_type => 'U' );
1814 --
1815 fetch g_csr_result_rules into v_status_processing_rule_id;
1816 --
1817 if g_csr_result_rules%found then
1818 --
1819 open csr_source_element;
1820 fetch csr_source_element into v_element_type_id;
1821 v_update_recurring := (csr_source_element%found
1822 and v_element_type_id <> p_element_type_id);
1823 close csr_source_element;
1824 --
1825 end if;
1826 --
1827 close g_csr_result_rules;
1828 --
1829 if v_update_recurring and p_error_if_true then
1830 hr_utility.set_message (801,'HR_6954_PAY_ELE_NO_UPD_REC');
1831 hr_utility.raise_error;
1832 end if;
1833 --
1834 return v_update_recurring;
1835 --
1836 end update_recurring_rules_exist;
1837 --
1838 --
1839 --
1840 --
1841 --
1842 -----------------------------------------------------------------------------
1843 function ELEMENT_USED_AS_PAY_BASIS (
1844 --
1845 p_element_type_id number,
1846 p_error_if_true boolean default FALSE) return boolean is
1847 --
1848 --*************************************************************************
1849 --* Returns TRUE if the element has an input value which is used as a pay
1850 --* basis.
1851 --*************************************************************************
1852 --
1853 v_pay_basis_element boolean := FALSE;
1854 --
1855 cursor csr_pay_basis is
1856 select 1
1857 from per_pay_bases BASIS,
1858 pay_input_values_f IV
1859 where iv.input_value_id = basis.input_value_id
1860 and iv.element_type_id = p_element_type_id;
1861 --
1862 begin
1863 --
1864 open csr_pay_basis;
1865 fetch csr_pay_basis into g_dummy_number;
1866 v_pay_basis_element := csr_pay_basis%found;
1867 close csr_pay_basis;
1868 --
1869 if v_pay_basis_element and p_error_if_true then
1870 --
1871 hr_utility.set_message (801, 'PAY_6965_INPVAL_NO_DEL_SB');
1872 hr_utility.raise_error;
1873 --
1874 end if;
1875 --
1876 return v_pay_basis_element;
1877 --
1878 end element_used_as_pay_basis;
1879 -----------------------------------------------------------------------------
1880 procedure CHECK_RELATIONSHIPS (
1881 --
1882 --*************************************************************************
1883 --* Checks all relationships required to establish various forms item *
1884 --* properties. This simply saves FORMS from having to call each of this *
1885 --* procedure's called functions separately and thus cuts down on network *
1886 --* traffic *
1887 --*************************************************************************
1888 --
1889 -- Parameters are:
1890 --
1891 p_element_type_id number,
1892 p_rowid varchar2,
1893 --
1894 -- Validation period
1895 p_validation_start_date date
1896 default to_date ('01/01/0001','DD/MM/YYYY'),
1897 p_validation_end_date date
1898 default to_date ('31/12/4712','DD/MM/YYYY'),
1899 --
1900 -- The results of the relationship checks must be passed back
1901 p_run_results out nocopy boolean,
1902 p_element_links out nocopy boolean,
1903 p_indirect_results out nocopy boolean,
1904 p_dated_updates out nocopy boolean,
1905 p_update_recurring out nocopy boolean,
1906 p_pay_basis out nocopy boolean,
1907 p_stop_entry_rules out nocopy boolean) is
1908 --
1909 begin
1910 --
1911 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',1);
1912 --
1913 -- Perform individual checks and place results directly in the OUT parameters
1914 --
1915 p_run_results := run_results_exist ( p_element_type_id,
1916 p_validation_start_date,
1917 p_validation_end_date);
1918 --
1919 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',2);
1920 --
1921 p_element_links := links_exist ( p_element_type_id,
1922 p_validation_start_date,
1923 p_validation_end_date );
1924
1925 --
1926 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',3);
1927 --
1928 p_indirect_results := fed_by_indirect_results ( p_element_type_id,
1929 p_validation_start_date,
1930 p_validation_end_date);
1931 --
1932 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',4);
1933 --
1934 p_dated_updates := date_effectively_updated ( p_element_type_id,
1935 p_rowid);
1936 --
1937 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',5);
1938 --
1939 p_stop_entry_rules := stop_entry_rules_exist ( p_element_type_id,
1940 p_validation_start_date,
1941 p_validation_end_date);
1942 --
1943 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',6);
1944 --
1945 p_update_recurring := update_recurring_rules_exist (p_element_type_id,
1946 p_validation_start_date,
1947 p_validation_end_date);
1948 --
1949 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',6);
1950 --
1951 p_pay_basis := element_used_as_pay_basis (p_element_type_id);
1952 --
1953 end check_relationships;
1954 --
1955 --
1956 --
1957 --
1958 --
1959 -----------------------------------------------------------------------------
1960 function ELEMENT_IS_IN_AN_ELEMENT_SET (
1961 --
1962 --**************************************************************
1963 --* Returns TRUE if the parameter element is in an element set *
1964 --**************************************************************
1965 --
1966 -- Parameters are:
1967 --
1968 p_element_type_id number,
1969 p_error_if_true boolean default FALSE
1970 ) return boolean is
1971 --
1972 v_in_set boolean := FALSE;
1973 --
1974 cursor csr_element_set is
1975 select null
1976 from pay_element_type_rules
1977 where element_type_id = p_element_type_id;
1978 --
1979 begin
1980 --
1981 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_IS_IN_AN_ELEMENT_SET',1);
1982 --
1983 open csr_element_set;
1984 fetch csr_element_set into g_dummy_number;
1985 v_in_set := csr_element_set%found;
1986 close csr_element_set;
1987 --
1988 if v_in_set and p_error_if_true then
1989 hr_utility.set_message (801,'PAY_6713_ELEMENT_NO_DEL_RULE');
1990 hr_utility.raise_error;
1991 end if;
1992 --
1993 return v_in_set;
1994 --
1995 end element_is_in_an_element_set;
1996 --
1997 --
1998 --
1999 --
2000 --
2001 -----------------------------------------------------------------------------
2002 function LINKS_EXIST (
2003 --
2004 --***************************************************************************
2005 --* Returns TRUE if the parameter element type has element links during the *
2006 --* validation period *
2007 --***************************************************************************
2008 --
2009 -- Parameters are:
2010 --
2011 p_element_type_id number,
2012 p_validation_start_date date default to_date ('01/01/0001',
2013 'DD/MM/YYYY'),
2014 p_validation_end_date date default to_date ('31/12/4712',
2015 'DD/MM/YYYY'),
2016 p_DML_action_being_checked varchar2 default 'UPDATE',
2017 p_error_if_true boolean default FALSE
2018 --
2019 ) return boolean is
2020 --
2021 v_links_exist boolean;
2022 --
2023 cursor csr_links is
2024 select 1
2025 from pay_element_links_f
2026 where element_type_id = p_element_type_id
2027 and effective_end_date >= p_validation_start_date
2028 and effective_start_date <= p_validation_end_date;
2029 --
2030 begin
2031 --
2032 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LINKS_EXIST',1);
2033 --
2034 open csr_links;
2035 fetch csr_links into g_dummy_number;
2036 v_links_exist := csr_links%found;
2037 close csr_links;
2038 --
2039 if v_links_exist and p_error_if_true then
2040 if p_DML_action_being_checked = 'UPDATE' then
2041 hr_utility.set_message (801,'PAY_6147_ELEMENT_LINK_UPDATE');
2042 --
2043 elsif p_DML_action_being_checked = 'DELETE' then
2044 hr_utility.set_message (801,'PAY_6155_ELEMENT_NO_DEL_LINK');
2045 --
2046 end if;
2047 hr_utility.raise_error;
2048 end if;
2049 --
2050 return v_links_exist;
2051 --
2052 end links_exist;
2053 --
2054 --
2055 --
2056 --
2057 --
2058 -----------------------------------------------------------------------------
2059 function ACCRUAL_PLAN_EXISTS (
2060 --
2061 --***************************************************************************
2062 --* Returns TRUE if there are accrual plans for the parameter element type
2063 --***************************************************************************
2064 --
2065 -- Parameters are:
2066 --
2067 p_element_type_id number,
2068 p_error_if_true boolean default FALSE) return boolean is
2069 --
2070 v_accrual_exists boolean := FALSE;
2071 --
2072 cursor csr_accrual is
2073 select null
2074 from pay_accrual_plans
2075 where accrual_plan_element_type_id = p_element_type_id;
2076 --
2077 begin
2078 --
2079 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ACCRUAL_PLAN_EXISTS',1);
2080 --
2081 open csr_accrual;
2082 fetch csr_accrual into g_dummy_number;
2083 v_accrual_exists := csr_accrual%found;
2084 close csr_accrual;
2085 --
2086 if v_accrual_exists and p_error_if_true then
2087 hr_utility.set_message (801,'PAY_35560_ELT_NO_DEL_ACCRUAL');
2088 hr_utility.raise_error;
2089 end if;
2090 --
2091 return v_accrual_exists;
2092 --
2093 end accrual_plan_exists;
2094 --
2095 --
2096 --
2097 --
2098 --
2099 -----------------------------------------------------------------------------
2100 function COBRA_BENEFITS_EXIST (
2101 --
2102 --***************************************************************************
2103 --* Returns TRUE if there are COBRA benefits for the parameter element type *
2104 --* within the validation period *
2105 --***************************************************************************
2106 --
2107 -- Parameters are:
2108 --
2109 p_element_type_id number,
2110 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2111 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
2112 p_error_if_true boolean default FALSE
2113 ) return boolean is
2114 --
2115 v_cobra_exists boolean := FALSE;
2116 --
2117 cursor csr_cobra is
2118 select null
2119 from per_cobra_coverage_benefits_f
2120 where element_type_id = p_element_type_id
2121 and effective_start_date <= p_validation_end_date
2122 and effective_end_date >= p_validation_start_date;
2123 --
2124 begin
2125 --
2126 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.COBRA_BENEFITS_EXIST',1);
2127 --
2128 open csr_cobra;
2129 fetch csr_cobra into g_dummy_number;
2130 v_cobra_exists := csr_cobra%found;
2131 close csr_cobra;
2132 --
2133 if v_cobra_exists and p_error_if_true then
2134 hr_utility.set_message (801,'PAY_COBRA_BENS_NO_DEL');
2135 hr_utility.raise_error;
2136 end if;
2137 --
2138 return v_cobra_exists;
2139 --
2140 end cobra_benefits_exist;
2141 --
2142 --
2143 --
2144 --
2145 --
2146 -----------------------------------------------------------------------------
2147 function benefit_contributions_exist (
2148 --
2149 --*****************************************************************************
2150 --* Returns TRUE if there are benefit contributions which refer to the
2151 --* specified element.
2152 --*****************************************************************************
2153 --
2154 p_element_type_id number,
2155 p_validation_start_date date,
2156 p_validation_end_date date,
2157 p_error_if_true boolean default FALSE) return boolean is
2158 --
2159 v_contribution_exists boolean := FALSE;
2160 --
2161 cursor csr_contribution is
2162 select 1
2163 from ben_benefit_contributions_f
2164 where element_type_id = p_element_type_id
2165 and effective_start_date <= p_validation_end_date
2166 and effective_end_date >= p_validation_start_date;
2167 --
2168 begin
2169 --
2170 hr_utility.set_location
2171 ('PAY_ELEMENT_TYPES_PKG.benefit_contributions_exist',1);
2172 --
2173 open csr_contribution;
2174 fetch csr_contribution into g_dummy_number;
2175 v_contribution_exists := csr_contribution%found;
2176 close csr_contribution;
2177 --
2178 if v_contribution_exists and p_error_if_true then
2179 hr_utility.set_message (801,'');
2180 hr_utility.raise_error;
2181 end if;
2182 --
2183 return v_contribution_exists;
2184 --
2185 end benefit_contributions_exist;
2186 -----------------------------------------------------------------------------
2187 function DELETION_ALLOWED (
2191 --* type are complied with for the parameter element or else returns an error *
2188 --
2189 --*****************************************************************************
2190 --* Returns TRUE if all the business rules relating to deletion of an element *
2192 --*****************************************************************************
2193 --
2194 -- Parameters are:
2195 --
2196 p_element_type_id number,
2197 p_processing_priority number,
2198 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2199 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
2200 p_delete_mode varchar2 default 'ZAP'
2201 ) return boolean is
2202 --
2203 v_deletion_allowed boolean := TRUE;
2204 --
2205 begin
2206 --
2207 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETION_ALLOWED',1);
2208 --
2209 -- Check business rules relating to deletion of element type
2210 --
2211 if (p_delete_mode = 'DELETE_NEXT_CHANGE'
2212 and priority_result_rule_violated ( p_element_type_id,
2213 p_processing_priority,
2214 p_validation_start_date,
2215 p_validation_end_date,
2216 p_error_if_true => TRUE))
2217 --
2218 or (p_delete_mode <> 'DELETE_NEXT_CHANGE'
2219 --
2220 and (links_exist ( p_element_type_id,
2221 p_validation_start_date,
2222 p_validation_end_date,
2223 p_DML_action_being_checked => 'DELETE',
2224 p_error_if_true => TRUE)
2225 -- Bug # 4991482 : Added to raise an error message if any run results exists.
2226 or run_results_exist ( p_element_type_id,
2227 p_validation_start_date,
2228 p_validation_end_date,
2229 p_DML_action_being_checked => 'DELETE',
2230 p_error_if_true => TRUE)
2231 --
2232 or cobra_benefits_exist (p_element_type_id,
2233 p_validation_start_date,
2234 p_validation_end_date,
2235 p_error_if_true => TRUE)
2236 --
2237 or (p_delete_mode = 'ZAP'
2238 --
2239 and (element_is_in_an_element_set ( p_element_type_id,
2240 p_error_if_true => TRUE)
2241 --
2242 or element_used_as_pay_basis (p_element_type_id,
2243 p_error_if_true => TRUE)
2244 --
2245 or benefit_contributions_exist (p_element_type_id,
2246 p_validation_start_date,
2247 p_validation_end_date,
2248 p_error_if_true => TRUE)
2249 --
2250 or accrual_plan_exists ( p_element_type_id,
2251 p_error_if_true => TRUE)
2252 --
2253 or stop_entry_rules_exist ( p_element_type_id,
2254 p_error_if_true => TRUE)
2255 )
2256 )
2257 or pay_input_values_pkg.cant_delete_all_input_values (
2258 --
2259 p_element_type_id,
2260 p_delete_mode,
2261 p_validation_start_date,
2262 p_validation_end_date,
2263 p_error_if_true => TRUE)
2264 )
2265 )
2266 or dt_api.rows_exist(
2267 p_base_table_name => 'ben_acty_base_rt_f',
2268 p_base_key_column => 'element_type_id',
2269 p_base_key_value => p_element_type_id,
2270 p_from_date => p_validation_start_date,
2271 p_to_date => p_validation_end_date
2272 )
2273 THEN
2274 v_deletion_allowed := FALSE;
2275 --
2276 END IF;
2277 --
2278
2279 return v_deletion_allowed;
2280 --
2281 end deletion_allowed;
2282 --
2283 --
2284 --
2285 --
2286 --
2287 -----------------------------------------------------------------------------
2288 function ELEMENT_PRIORITY_TOO_HIGH (
2289 --
2290 --****************************************************************************
2291 --* Returns TRUE if an element has a priority higher than an element it feeds*
2292 --****************************************************************************
2293 --
2294 -- Parameters are:
2295 --
2296 p_element_type_id number,
2297 --
2298 -- The priority of the element being checked
2299 p_processing_priority number,
2300 --
2301 -- The validation period
2302 p_validation_start_date date
2303 default to_date ('01/01/0001','DD/MM/YYYY'),
2304 p_validation_end_date date
2305 default to_date ('31/12/4712','DD/MM/YYYY')
2306 --
2307 ) return boolean is
2308 --
2309 v_priority_too_high boolean := FALSE;
2310 --
2311 cursor csr_elements_fed_by_this_one is
2312 --
2313 /* Returns a row if there are any elements whose input
2314 values rely on the output from the parameter element,
2315 and which have a lower priority than the parameter
2319 from pay_status_processing_rules_f STATUS,
2316 priority (not allowed) */
2317 --
2318 select 1
2320 pay_formula_result_rules_f RESULT,
2321 pay_input_values_f INPUT,
2322 pay_element_types_f ELEMENT
2323 where status.status_processing_rule_id = result.status_processing_rule_id
2324 and result.input_value_id = input.input_value_id
2325 and input.element_type_id = element.element_type_id
2326 and result.result_rule_type = 'I'
2327 and status.element_type_id = p_element_type_id
2328 and element.element_type_id <> p_element_type_id
2329 and element.processing_priority < p_processing_priority
2330 and (status.effective_end_date >= p_validation_start_date
2331 and status.effective_start_date <= p_validation_end_date)
2332 and (result.effective_end_date >= p_validation_start_date
2333 and result.effective_start_date <= p_validation_end_date);
2334 --
2335 begin
2336 --
2337 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_PRIORITY_TOO_HIGH',1);
2338 --
2339 open csr_elements_fed_by_this_one;
2340 fetch csr_elements_fed_by_this_one into g_dummy_number;
2341 v_priority_too_high := csr_elements_fed_by_this_one%found;
2342 close csr_elements_fed_by_this_one;
2343 --
2344 return v_priority_too_high;
2345 --
2346 end element_priority_too_high;
2347 --
2348 --
2349 --
2350 --
2351 --
2352 -------------------------------------------------------------------------------
2353 function ELEMENT_PRIORITY_TOO_LOW (
2354 --
2355 --***************************************************************************
2356 --* Returns TRUE if the parameter element has a priority lower than that of *
2357 --* an element whose input value results feed it *
2358 --***************************************************************************
2359 --
2360 -- Parameters are:
2361 --
2362 p_element_type_id number,
2363 p_processing_priority number,
2364 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2365 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY')
2366 --
2367 ) return boolean is
2368 --
2369 v_priority_too_low boolean := FALSE;
2370 --
2371 cursor csr_elements_feeding_this_one is
2372 --
2373 /* Returns a row if there are elements whose output feed
2374 input values of the parameter element and which have a
2375 priority higher than the parameter priority
2376 (not allowed) */
2377 --
2378 select 1
2379 from pay_status_processing_rules_f STATUS,
2380 pay_formula_result_rules_f RESULT,
2381 pay_input_values_f INPUT,
2382 pay_element_types_f ELEMENT
2383 where result.input_value_id = input.input_value_id
2384 and status.element_type_id = element.element_type_id
2385 and result.status_processing_rule_id= status.status_processing_rule_id
2386 and result.result_rule_type = 'I'
2387 and input.element_type_id = p_element_type_id
2388 and element.element_type_id <> p_element_type_id
2389 and element.processing_priority > p_processing_priority
2390 and (status.effective_end_date >= p_validation_start_date
2391 and status.effective_start_date <= p_validation_end_date)
2392 and (result.effective_end_date >= p_validation_start_date
2393 and result.effective_start_date<= p_validation_end_date);
2394 --
2395 begin
2396 --
2397 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_PRIORITY_TOO_LOW',1);
2398 --
2399 open csr_elements_feeding_this_one;
2400 fetch csr_elements_feeding_this_one into g_dummy_number;
2401 v_priority_too_low := csr_elements_feeding_this_one%found;
2402 close csr_elements_feeding_this_one;
2403 --
2404 return v_priority_too_low;
2405 --
2406 end element_priority_too_low;
2407 --
2408 --
2409 --
2410 --
2411 --
2412 ---------------------------------------------------------------------------
2413 function PRIORITY_RESULT_RULE_VIOLATED (
2414 --
2415 --*****************************************************************************
2416 --* Returns TRUE if either *
2417 --* 1. The element will be processed in a payroll run before an element whose*
2418 --* results are needed to process it *
2419 --* or *
2420 --* 2. The element will be processed in a run after an element which needs *
2421 --* the results produced *
2422 --*****************************************************************************
2423 --
2424 -- Parameters are:
2425 --
2426 p_element_type_id number,
2427 p_processing_priority number,
2428 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2429 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
2430 p_error_if_true boolean default FALSE
2431 --
2432 ) return boolean is
2433 --
2434 v_priority_rule_violated boolean := FALSE;
2435 --
2436 begin
2437 --
2438 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.PRIORITY_RESULT_RULE_VIOLATED',1);
2439 --
2440 if element_priority_too_high ( p_element_type_id,
2441 p_processing_priority,
2442 p_validation_start_date,
2446 p_processing_priority,
2443 p_validation_end_date )
2444 --
2445 or element_priority_too_low ( p_element_type_id,
2447 p_validation_start_date,
2448 p_validation_end_date ) then
2449 --
2450 v_priority_rule_violated := TRUE;
2451 --
2452 if v_priority_rule_violated and p_error_if_true then
2453 hr_utility.set_message (801,'PAY_6149_ELEMENT_PRIORITY_UPD');
2454 hr_utility.raise_error;
2455 end if;
2456 --
2457 end if;
2458 --
2459 return v_priority_rule_violated;
2460 --
2461 end priority_result_rule_violated;
2462 --
2463 --
2464 --
2465 --
2466 --
2467 -------------------------------------------------------------------------------
2468 function NAME_IS_NOT_UNIQUE (
2469 --
2470 --******************************************************************************
2471 --* Returns TRUE if the element name has been duplicated within business group *
2472 --* and legislation code. If the name is the only parameter, then the check *
2473 --* will return TRUE if the name is not unique within the generic data set. *
2474 --******************************************************************************
2475 --
2476 -- Parameters are:
2477 --
2478 p_element_name varchar2,
2479 p_element_type_id number default null,
2480 p_business_group_id number default null,
2481 p_legislation_code varchar2 default null,
2482 p_error_if_true boolean default FALSE
2483 --
2484 ) return boolean is
2485 --
2486 v_name_duplicated boolean := FALSE;
2487 --
2488 cursor csr_duplicate is
2489 select null
2490 from pay_element_types_f et,
2491 pay_element_types_f_tl et_tl
2492 where upper(translate(p_element_name,'x_','x '))
2493 = upper(translate(et_tl.element_name,'x_','x '))
2494 and (et.element_type_id <> p_element_type_id
2495 or p_element_type_id is null)
2496 and ( p_business_group_id = et.business_group_id + 0
2497 or ( et.business_group_id is null
2498 and p_legislation_code = et.legislation_code ))
2499 and et_tl.element_type_id = et.element_type_id
2500 and et_tl.language = userenv('LANG');
2501 --
2502 begin
2503 --
2504 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.NAME_IS_NOT_UNIQUE',1);
2505 --
2506 open csr_duplicate;
2507 fetch csr_duplicate into g_dummy_number;
2508 v_name_duplicated := csr_duplicate%found;
2509 close csr_duplicate;
2510 --
2511 if v_name_duplicated and p_error_if_true then
2512 hr_utility.set_message (801,'PAY_6137_ELEMENT_DUP_NAME');
2513 hr_utility.raise_error;
2514 end if;
2515 --
2516 return v_name_duplicated;
2517 --
2518 end name_is_not_unique;
2519 --
2520 --
2521 --
2522 --
2523 --
2524 -----------------------------------------------------------------------------
2525 function ELEMENT_START_DATE (p_element_type_id number) return date is
2526 --
2527 --******************************************************************************
2528 --* Returns the minimum start date for a given element *
2529 --******************************************************************************
2530 --
2531 cursor csr_date is
2532 select min (effective_start_date)
2533 from pay_element_types_f
2534 where element_type_id = p_element_type_id;
2535 --
2536 v_start_date date;
2537 --
2538 begin
2539 --
2540 open csr_date;
2541 fetch csr_date into v_start_date;
2542 close csr_date;
2543 --
2544 return v_start_date;
2545 --
2546 end element_start_date;
2547 --------------------------------------------------------------------------------
2548 function ELEMENT_END_DATE (p_element_type_id number) return date is
2549 --
2550 --******************************************************************************
2551 --* Returns the maximum end date for a given element *
2552 --******************************************************************************
2553 --
2554 cursor csr_end_date is
2555 select max (effective_end_date)
2556 from pay_element_types_f
2557 where element_type_id = p_element_type_id;
2558 --
2559 v_end_date date;
2560 --
2561 begin
2562 --
2563 open csr_end_date;
2564 fetch csr_end_date into v_end_date;
2565 close csr_end_date;
2566 --
2567 return v_end_date;
2568 --
2569 end element_end_date;
2570 -----------------------------------------------------------------------------
2571 function ELEMENT_ENTRIES_EXIST (p_element_type_id number,
2572 p_error_if_true boolean default FALSE)
2573 return boolean is
2574 --
2575 --******************************************************************************
2576 --* Returns TRUE if there are element entries which use the link belonging to *
2577 --* the parameter element type. *
2578 --******************************************************************************
2579 --
2580 v_entries_exist boolean :=FALSE;
2581 --
2582 cursor csr_entries is
2583 select null
2584 from pay_element_entries_f ENTRY,
2585 pay_element_links_f LINK
2586 where link.element_link_id = entry.element_link_id
2587 and link.element_type_id = p_element_type_id;
2588 --
2592 --
2589 begin
2590 --
2591 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_ENTRIES_EXIST',1);
2593 open csr_entries;
2594 fetch csr_entries into g_dummy_number;
2595 v_entries_exist := csr_entries%found;
2596 close csr_entries;
2597 --
2598 if v_entries_exist and p_error_if_true then
2599 hr_utility.set_message (801,'PAY_6197_INPVAL_NO_ENTRY');
2600 hr_utility.raise_error;
2601 end if;
2602 --
2603 return v_entries_exist;
2604 --
2605 end element_entries_exist;
2606 --
2607 --
2608 --
2609 --
2610 --
2611 -----------------------------------------------------------------------------
2612 procedure ADD_LANGUAGE
2613 is
2614 begin
2615 delete from PAY_ELEMENT_TYPES_F_TL T
2616 where not exists
2617 (select NULL
2618 from PAY_ELEMENT_TYPES_F B
2619 where B.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
2620 );
2621
2622 update PAY_ELEMENT_TYPES_F_TL T set (
2623 ELEMENT_NAME,
2624 REPORTING_NAME,
2625 DESCRIPTION
2626 ) = (select
2627 B.ELEMENT_NAME,
2628 B.REPORTING_NAME,
2629 B.DESCRIPTION
2630 from PAY_ELEMENT_TYPES_F_TL B
2631 where B.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
2632 and B.LANGUAGE = T.SOURCE_LANG)
2633 where (
2634 T.ELEMENT_TYPE_ID,
2635 T.LANGUAGE
2636 ) in (select
2637 SUBT.ELEMENT_TYPE_ID,
2638 SUBT.LANGUAGE
2639 from PAY_ELEMENT_TYPES_F_TL SUBB, PAY_ELEMENT_TYPES_F_TL SUBT
2640 where SUBB.ELEMENT_TYPE_ID = SUBT.ELEMENT_TYPE_ID
2641 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2642 and (SUBB.ELEMENT_NAME <> SUBT.ELEMENT_NAME
2643 or SUBB.REPORTING_NAME <> SUBT.REPORTING_NAME
2644 or (SUBB.REPORTING_NAME is null and SUBT.REPORTING_NAME is not null)
2645 or (SUBB.REPORTING_NAME is not null and SUBT.REPORTING_NAME is null)
2646 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
2647 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
2648 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
2649 ));
2650
2651 insert into PAY_ELEMENT_TYPES_F_TL (
2652 ELEMENT_TYPE_ID,
2653 ELEMENT_NAME,
2654 REPORTING_NAME,
2655 DESCRIPTION,
2656 LAST_UPDATE_DATE,
2657 LAST_UPDATED_BY,
2658 LAST_UPDATE_LOGIN,
2659 CREATED_BY,
2660 CREATION_DATE,
2661 LANGUAGE,
2662 SOURCE_LANG
2663 ) select
2664 B.ELEMENT_TYPE_ID,
2665 B.ELEMENT_NAME,
2666 B.REPORTING_NAME,
2667 B.DESCRIPTION,
2668 B.LAST_UPDATE_DATE,
2669 B.LAST_UPDATED_BY,
2670 B.LAST_UPDATE_LOGIN,
2671 B.CREATED_BY,
2672 B.CREATION_DATE,
2673 L.LANGUAGE_CODE,
2674 B.SOURCE_LANG
2675 from PAY_ELEMENT_TYPES_F_TL B, FND_LANGUAGES L
2676 where L.INSTALLED_FLAG in ('I', 'B')
2677 and B.LANGUAGE = userenv('LANG')
2678 and not exists
2679 (select NULL
2680 from PAY_ELEMENT_TYPES_F_TL T
2681 where T.ELEMENT_TYPE_ID = B.ELEMENT_TYPE_ID
2682 and T.LANGUAGE = L.LANGUAGE_CODE);
2683 end ADD_LANGUAGE;
2684 -----------------------------------------------------------------------------
2685 procedure unique_chk(X_E_ELEMENT_NAME in VARCHAR2,X_E_LEGISLATION_CODE in VARCHAR2,
2686 X_E_EFFECTIVE_START_DATE in date, X_E_EFFECTIVE_END_DATE in date)
2687 is
2688 result varchar2(255);
2689 Begin
2690 SELECT count(*) INTO result
2691 FROM pay_element_types_f
2692 WHERE nvl(ELEMENT_NAME,'~null~') = nvl(X_E_ELEMENT_NAME,'~null~')
2693 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_E_LEGISLATION_CODE,'~null~')
2694 and EFFECTIVE_START_DATE = X_E_EFFECTIVE_START_DATE
2695 and EFFECTIVE_end_DATE = X_E_EFFECTIVE_END_DATE
2696 and X_E_EFFECTIVE_START_DATE is not NULL
2697 and X_E_EFFECTIVE_END_DATE is not NULL
2698 and BUSINESS_GROUP_ID is NULL;
2699 --
2700 IF (result>1) THEN
2701 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2702 hr_utility.set_message_token('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UNIQUE_CHK');
2703 hr_utility.set_message_token('STEP','1');
2704 hr_utility.raise_error;
2705 END IF;
2706 EXCEPTION
2707 when NO_DATA_FOUND then
2708 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2709 hr_utility.set_message_token('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UNIQUE_CHK');
2710 hr_utility.set_message_token('STEP','1');
2711 hr_utility.raise_error;
2712 end unique_chk;
2713 --------------------------------------------------------------------------------
2714 procedure TRANSLATE_ROW (
2715 X_E_ELEMENT_NAME in varchar2,
2716 X_E_LEGISLATION_CODE in varchar2,
2717 X_E_EFFECTIVE_START_DATE in date,
2718 X_E_EFFECTIVE_END_DATE in date,
2719 X_ELEMENT_NAME in varchar2,
2720 X_REPORTING_NAME in varchar2,
2721 X_DESCRIPTION in varchar2,
2722 X_OWNER in varchar2 ) is
2723 --
2724 -- Fetch the element_type_id. This used to be a sub-query in the update
2725 -- statement.
2726 --
2727 cursor csr_ele_id is
2728 select element_type_id
2729 from pay_element_types_f
2730 where nvl(ELEMENT_NAME,'~null~') = nvl(X_E_ELEMENT_NAME,'~null~')
2731 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_E_LEGISLATION_CODE,'~null~')
2732 and EFFECTIVE_START_DATE = X_E_EFFECTIVE_START_DATE
2733 and EFFECTIVE_end_DATE = X_E_EFFECTIVE_END_DATE
2734 and X_E_EFFECTIVE_START_DATE is not NULL
2735 and X_E_EFFECTIVE_END_DATE is not NULL
2736 and BUSINESS_GROUP_ID is NULL
2737 ;
2738 --
2739 -- Fetch information for the _TL rows that will be affected by the update.
2740 --
2741 cursor csr_tl_info
2742 (p_element_type_id in number
2743 ,p_language in varchar2
2744 ) is
2748 where element_type_id = p_element_type_id
2745 select element_name
2746 , language
2747 from pay_element_types_f_tl
2749 and p_language in (language, source_lang)
2750 ;
2751 --
2752 l_element_type_id number;
2753 l_found boolean;
2754 i binary_integer := 1;
2755 l_langs dbms_sql.varchar2s;
2756 l_lang varchar2(100);
2757 begin
2758 --
2759 -- Fetch the element_type_id.
2760 --
2761 open csr_ele_id;
2762 fetch csr_ele_id
2763 into l_element_type_id
2764 ;
2765 l_found := csr_ele_id%found;
2766 close csr_ele_id;
2767
2768 l_lang := userenv('LANG');
2769
2770 if l_found then
2771 --
2772 -- Check if database item translations are supported.
2773 --
2774 if ff_dbi_utils_pkg.translations_supported
2775 (p_legislation_code => x_e_legislation_code
2776 ) then
2777 for crec in csr_tl_info
2778 (p_element_type_id => l_element_type_id
2779 ,p_language => l_lang
2780 ) loop
2781 if upper(crec.element_name) <> upper(x_element_name) then
2782 l_langs(i) := crec.language;
2783 i := i + 1;
2784 end if;
2785 end loop;
2786 end if;
2787
2788 UPDATE pay_element_types_f_tl
2789 SET element_name = nvl(x_element_name,element_name),
2790 reporting_name = nvl(x_reporting_name,reporting_name),
2791 description = nvl(x_description,description),
2792 last_update_date = SYSDATE,
2793 last_updated_by = decode(x_owner,'SEED',1,0),
2794 last_update_login = 0,
2795 source_lang = l_lang
2796 WHERE l_lang IN (language,source_lang)
2797 AND element_type_id = l_element_type_id
2798 ;
2799
2800 --
2801 -- Write any changes to PAY_DYNDBI_CHANGES.
2802 --
2803 if l_langs.count <> 0 then
2804 pay_dyndbi_changes_pkg.element_type_change
2805 (p_element_type_id => l_element_type_id
2806 ,p_languages => l_langs
2807 );
2808 end if;
2809 end if;
2810 end TRANSLATE_ROW;
2811 --------------------------------------------------------------------------------
2812 begin
2813
2814 c_user_id := fnd_global.user_id;
2815 c_login_id := fnd_global.login_id;
2816
2817 end PAY_ELEMENT_TYPES_PKG;