[Home] [Help]
PACKAGE BODY: APPS.PAY_ELEMENT_TYPES_PKG
Source
1 package body PAY_ELEMENT_TYPES_PKG as
2 /* $Header: pyelt.pkb 120.6.12010000.2 2008/08/06 07:09:44 ubhat 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,
360 indirect_only_flag,
357 adjustment_only_flag,
358 closed_for_entry_flag,
359 element_name,
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,
457 p_base_element_name,
454 p_closed_for_entry_flag,
455 -- p_element_name,
456 -- --
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);
601 -- Create pay value for payment type elements which will be processed in the
598 --
599 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.INSERT_ROW',4);
600 --
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. *
724 --
721 --******************************************************************************
722 --
723 -- Parameters to be passed in are:
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,
819 p_deduction_element_type_id number 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,
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,
837 classification_id = p_classification_id,
838 benefit_classification_id = p_benefit_classification_id,
839 additional_entry_allowed_flag = p_additional_entry_allowed,
840 adjustment_only_flag = p_adjustment_only_flag,
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,
924 where rowid = p_rowid;
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
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
1047 elsif p_delete_mode = 'DELETE' then
1048 --
1052 delete from pay_ele_payroll_freq_rules
1049 -- We need to remove any payroll frequency rules starting after the new end
1050 -- date
1051 --
1053 where element_type_id = p_element_type_id
1054 and start_date > p_session_date;
1055 --
1056 end if;
1057 --
1058 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETE_ROW',5);
1059 --
1060 -- Delete the element itself
1061 delete
1062 from pay_element_types_f
1063 where rowid = p_rowid;
1064 --
1065 if sql%notfound then -- trap system errors during deletion
1066 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1067 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.DELETE_ROW');
1068 end if;
1069 --
1070 -- ****************************************************************************
1071 --
1072 -- bugfix 1229606
1073 -- only delete data from the translated tables if the date track mode is ZAP,
1074 -- for all other date track modes the data should remain untouched
1075 --
1076 if p_delete_mode = 'ZAP' then
1077 --
1078 -- delete from MLS table (TL)
1079 --
1080 delete from PAY_ELEMENT_TYPES_F_TL
1081 where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID;
1082 --
1083 if sql%notfound then -- trap system errors during deletion
1084 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1085 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.DELETE_TL_ROW');
1086 end if;
1087
1088 end if;
1089 --
1090 -- ****************************************************************************
1091 --
1092 end if;
1093 --
1094 end delete_row;
1095 --
1096 --
1097 --
1098 --
1099 --
1100 --------------------------------------------------------------------------------
1101 procedure LOCK_ROW(
1102 --
1103 --******************************************************************************
1104 --* Handles row-locking on the base table for the form which is based on a view*
1105 --******************************************************************************
1106 --
1107 -- Parameters are:
1108 -- All base table columns
1109 p_rowid varchar2,
1110 p_element_type_id number,
1111 p_effective_start_date date,
1112 p_effective_end_date date,
1113 p_business_group_id number,
1114 p_legislation_code varchar2,
1115 p_formula_id number,
1116 p_input_currency_code varchar2,
1117 p_output_currency_code varchar2,
1118 p_classification_id number,
1119 p_benefit_classification_id number,
1120 p_additional_entry_allowed varchar2,
1121 p_adjustment_only_flag varchar2,
1122 p_closed_for_entry_flag varchar2,
1123 -- p_element_name varchar2,
1124 p_base_element_name varchar2,
1125 p_indirect_only_flag varchar2,
1126 p_multiple_entries_allowed varchar2,
1127 p_multiply_value_flag varchar2,
1128 p_post_termination_rule varchar2,
1129 p_process_in_run_flag varchar2,
1130 p_processing_priority number,
1131 p_processing_type varchar2,
1132 p_standard_link_flag varchar2,
1133 p_comment_id number,
1134 p_description varchar2,
1135 p_legislation_subgroup varchar2,
1136 p_qualifying_age number,
1137 p_qualifying_length_of_service number,
1138 p_qualifying_units varchar2,
1139 p_reporting_name varchar2,
1140 p_attribute_category varchar2,
1141 p_attribute1 varchar2,
1142 p_attribute2 varchar2,
1143 p_attribute3 varchar2,
1144 p_attribute4 varchar2,
1145 p_attribute5 varchar2,
1146 p_attribute6 varchar2,
1147 p_attribute7 varchar2,
1148 p_attribute8 varchar2,
1149 p_attribute9 varchar2,
1150 p_attribute10 varchar2,
1151 p_attribute11 varchar2,
1152 p_attribute12 varchar2,
1153 p_attribute13 varchar2,
1154 p_attribute14 varchar2,
1155 p_attribute15 varchar2,
1156 p_attribute16 varchar2,
1157 p_attribute17 varchar2,
1158 p_attribute18 varchar2,
1159 p_attribute19 varchar2,
1160 p_attribute20 varchar2,
1161 p_element_information_category varchar2,
1162 p_element_information1 varchar2,
1163 p_element_information2 varchar2,
1164 p_element_information3 varchar2,
1165 p_element_information4 varchar2,
1169 p_element_information8 varchar2,
1166 p_element_information5 varchar2,
1167 p_element_information6 varchar2,
1168 p_element_information7 varchar2,
1170 p_element_information9 varchar2,
1171 p_element_information10 varchar2,
1172 p_element_information11 varchar2,
1173 p_element_information12 varchar2,
1174 p_element_information13 varchar2,
1175 p_element_information14 varchar2,
1176 p_element_information15 varchar2,
1177 p_element_information16 varchar2,
1178 p_element_information17 varchar2,
1179 p_element_information18 varchar2,
1180 p_element_information19 varchar2,
1181 p_element_information20 varchar2,
1182 p_third_party_pay_only_flag varchar2,
1183 p_retro_summ_ele_id number,
1184 p_iterative_flag varchar2,
1185 p_iterative_formula_id number,
1186 p_iterative_priority number,
1187 p_process_mode varchar2,
1188 p_grossup_flag varchar2,
1189 p_advance_indicator varchar2,
1190 p_advance_payable varchar2,
1191 p_advance_deduction varchar2,
1192 p_process_advance_entry varchar2,
1193 p_proration_group_id number,
1194 p_proration_formula_id number,
1195 p_recalc_event_group_id number,
1196 p_once_each_period_flag varchar2 default null,
1197 -- Added for FLSA Dynamic Period Allocation
1198 p_time_definition_type varchar2 default null,
1199 p_time_definition_id varchar2 default null,
1200 -- Added for Advance Pay Enhancement
1201 p_advance_element_type_id number default null,
1202 p_deduction_element_type_id number default null
1203 ) is
1204 --
1205 cursor csr_element_type is
1206 select *
1207 from pay_element_types_f
1208 where rowid = p_rowid
1209 for update of element_type_id nowait;
1210 --
1211 element_record csr_element_type%rowtype;
1212 --
1213 -- ***************************************************************************
1214 -- cursor for MLS
1215 --
1216 cursor csr_element_type_tl is
1217 select ELEMENT_NAME,
1218 REPORTING_NAME,
1219 DESCRIPTION,
1220 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
1221 from PAY_ELEMENT_TYPES_F_TL
1222 where ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID
1223 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1224 for update of ELEMENT_TYPE_ID nowait;
1225 --
1226 -- ***************************************************************************
1227 --
1228 -- Counter to check for locked MLS rows
1229 --
1230 l_count NUMBER := 0;
1231 --
1232 -- Bug 6411503. l_time_definition_type added. If it is N then it will be changed to NULL
1233 l_time_definition_type pay_element_types_f.time_definition_type%type;
1234 --
1235 begin
1236 --
1237 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LOCK_ROW',10);
1238 --
1239 -- Fetching the row effectively locks it because of the 'for update' clause
1240 open csr_element_type;
1241 fetch csr_element_type into element_record;
1242 --
1243 if (csr_element_type%notfound) then -- Trap system errors
1244 close csr_element_type;
1245 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1246 hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.LOCK_ROW');
1247 end if;
1248 --
1249 close csr_element_type;
1250 --
1251 /** sbilling **/
1252 -- removed explicit lock of _TL table,
1253 -- the MLS strategy requires that the base table is locked before update of the
1254 -- _TL table can take place,
1255 -- which implies it is not necessary to lock both tables.
1256 --
1257 -- ***************************************************************************
1258 -- code for MLS
1259 --
1260 --for tlinfo in csr_element_type_tl LOOP
1261 -- l_count := l_count+1;
1262 -- hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LOCK_ROW:'||to_char(l_count),15);
1263 -- if (tlinfo.BASELANG = 'Y') then
1264 -- if ( (tlinfo.ELEMENT_NAME = P_ELEMENT_NAME)
1265 -- AND ((tlinfo.REPORTING_NAME = P_REPORTING_NAME)
1266 -- OR ((tlinfo.REPORTING_NAME is null) AND (P_REPORTING_NAME is null)))
1267 -- AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
1268 -- OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION is null)))
1269 -- ) then
1270 -- null;
1271 -- else
1272 -- hr_utility.set_message('fnd', 'form_record_changed');
1273 -- hr_utility.raise_error;
1274 -- end if;
1275 -- end if;
1276 --end loop;
1277 --IF(l_count=0) THEN -- We have data missing from the _TL table.
1278 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1279 -- hr_utility.set_message_token ('PROCEDURE','PAY_ELEMENT_TYPES_PKG.LOCK_ROW');
1280 --end if;
1281 --
1282 -- ***************************************************************************
1283 --
1284 /** sbilling **/
1285 -- combined if statements,
1286 -- causes locking error,
1287 -- ie. if first set of items match then a 'return' is issued
1288 --
1289 -- The following two IF statements are logically a single
1293 -- to this user changing it and since the row was queried
1290 -- statement split into two parts because its length exceeds
1291 -- parser stack limitations. It checks to see if any column
1292 -- showing in the form has been changed by another user prior
1294 --
1295 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LOCK_ROW',20);
1296 --
1297
1298 -- Bug 6411503. Checking for time_definition_type
1299 select DECODE(element_record.time_definition_type, 'N', NULL, element_record.TIME_DEFINITION_TYPE)
1300 into l_time_definition_type
1301 from dual;
1302
1303 if
1304 --
1305 ((element_record.additional_entry_allowed_flag = p_additional_entry_allowed)
1306 or (element_record.additional_entry_allowed_flag is null and p_additional_entry_allowed is null))
1307 --
1308 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))
1309 --
1310 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))
1311 --
1312 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))
1313 --
1314 and ((element_record.attribute1 = p_attribute1) or (element_record.attribute1 is null and p_attribute1 is null))
1315 --
1316 and ((element_record.attribute10 = p_attribute10) or (element_record.attribute10 is null and p_attribute10 is null))
1317 --
1318 and ((element_record.attribute11 = p_attribute11) or (element_record.attribute11 is null and p_attribute11 is null))
1319 --
1320 and ((element_record.attribute12 = p_attribute12) or (element_record.attribute12 is null and p_attribute12 is null))
1321 --
1322 and ((element_record.attribute13 = p_attribute13) or (element_record.attribute13 is null and p_attribute13 is null))
1323 --
1324 and ((element_record.attribute14 = p_attribute14) or (element_record.attribute14 is null and p_attribute14 is null))
1325 --
1326 and ((element_record.attribute15 = p_attribute15) or (element_record.attribute15 is null and p_attribute15 is null))
1327 --
1328 and ((element_record.attribute16 = p_attribute16) or (element_record.attribute16 is null and p_attribute16 is null))
1329 --
1330 and ((element_record.attribute17 = p_attribute17) or (element_record.attribute17 is null and p_attribute17 is null))
1331 --
1332 and ((element_record.attribute18 = p_attribute18) or (element_record.attribute18 is null and p_attribute18 is null))
1333 --
1334 and ((element_record.attribute19 = p_attribute19) or (element_record.attribute19 is null and p_attribute19 is null))
1335 --
1336 and ((element_record.attribute2 = p_attribute2) or (element_record.attribute2 is null and p_attribute2 is null))
1337 --
1338 and ((element_record.attribute20 = p_attribute20) or (element_record.attribute20 is null and p_attribute20 is null))
1339 --
1340 and ((element_record.attribute3 = p_attribute3) or (element_record.attribute3 is null and p_attribute3 is null))
1341 --
1342 and ((element_record.attribute4 = p_attribute4) or (element_record.attribute4 is null and p_attribute4 is null))
1343 --
1344 and ((element_record.attribute5 = p_attribute5) or (element_record.attribute5 is null and p_attribute5 is null))
1345 --
1346 and ((element_record.attribute6 = p_attribute6) or (element_record.attribute6 is null and p_attribute6 is null))
1347 --
1348 and ((element_record.attribute7 = p_attribute7) or (element_record.attribute7 is null and p_attribute7 is null))
1349 --
1350 and ((element_record.attribute8 = p_attribute8) or (element_record.attribute8 is null and p_attribute8 is null))
1351 --
1352 and ((element_record.attribute9 = p_attribute9) or (element_record.attribute9 is null and p_attribute9 is null))
1353 --
1354 and ((element_record.attribute_category = p_attribute_category) or (element_record.attribute_category is null and p_attribute_category is null))
1355 --
1356 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))
1357 --
1358 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))
1359 --
1360 and ((element_record.classification_id = p_classification_id) or (element_record.classification_id is null and p_classification_id is null))
1361 --
1362 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))
1363 --
1364 and ((element_record.comment_id = p_comment_id) or (element_record.comment_id is null and p_comment_id is null))
1365 --
1366 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))
1367 --
1368 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))
1369 --
1370 and ((element_record.element_information1 = p_element_information1) or (element_record.element_information1 is null and p_element_information1 is null))
1371 --
1372 and ((element_record.element_information10 = p_element_information10) or (element_record.element_information10 is null and p_element_information10 is null))
1373 --
1374 and ((element_record.element_information11 = p_element_information11) or (element_record.element_information11 is null and p_element_information11 is null))
1375 --
1376 and ((element_record.element_information12 = p_element_information12) or (element_record.element_information12 is null and p_element_information12 is null))
1377 --
1378 and ((element_record.element_information13 = p_element_information13) or (element_record.element_information13 is null and p_element_information13 is null))
1379 --
1383 --
1380 and ((element_record.element_information14 = p_element_information14) or (element_record.element_information14 is null and p_element_information14 is null))
1381 --
1382 and ((element_record.element_information15 = p_element_information15) or (element_record.element_information15 is null and p_element_information15 is null))
1384 and ((element_record.element_information16 = p_element_information16) or (element_record.element_information16 is null and p_element_information16 is null))
1385 --
1386 and ((element_record.element_information17 = p_element_information17) or (element_record.element_information17 is null and p_element_information17 is null))
1387 --
1388 and ((element_record.element_information18 = p_element_information18) or (element_record.element_information18 is null and p_element_information18 is null))
1389 --
1390 and ((element_record.element_information19 = p_element_information19) or (element_record.element_information19 is null and p_element_information19 is null))
1391 --
1392 and ((element_record.element_information2 = p_element_information2) or (element_record.element_information2 is null and p_element_information2 is null))
1393 --
1394 --then
1395 -- return;
1396 -- else
1397 -- hr_utility.set_message('fnd', 'form_record_changed');
1398 -- hr_utility.raise_error;
1399 --end if;
1400 --
1401 --hr_utility.set_location ('pay_element_types_pkg.lock_row',30);
1402 --
1403 --if
1404 --
1405 --((element_record.element_information20 = p_element_information20)
1406 and ((element_record.element_information20 = p_element_information20)
1407 or (element_record.element_information20 is null and p_element_information20 is null))
1408 --
1409 and ((element_record.element_information3 = p_element_information3) or (element_record.element_information3 is null and p_element_information3 is null))
1410 --
1411 and ((element_record.element_information4 = p_element_information4) or (element_record.element_information4 is null and p_element_information4 is null))
1412 --
1413 and ((element_record.element_information5 = p_element_information5) or (element_record.element_information5 is null and p_element_information5 is null))
1414 --
1415 and ((element_record.element_information6 = p_element_information6) or (element_record.element_information6 is null and p_element_information6 is null))
1416 --
1417 and ((element_record.element_information7 = p_element_information7) or (element_record.element_information7 is null and p_element_information7 is null))
1418 --
1419 and ((element_record.element_information8 = p_element_information8)
1420 or (element_record.element_information8 is null and p_element_information8 is null))
1421 --
1422 and ((element_record.element_information9 = p_element_information9) or (element_record.element_information9 is null and p_element_information9 is null))
1423 --
1424 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))
1425 --and ((element_record.element_name = p_element_name) or (element_record.element_name is null and p_element_name is null))
1426 -- --
1427 and ((element_record.element_name = p_base_element_name) or (element_record.element_name is null and p_base_element_name is null))
1428 -- --
1429 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))
1430 --
1431 and ((element_record.formula_id = p_formula_id) or (element_record.formula_id is null and p_formula_id is null))
1432 --
1433 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))
1434 --
1435 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))
1436 --
1437 and ((element_record.legislation_code = p_legislation_code) or (element_record.legislation_code is null and p_legislation_code is null))
1438 --
1439 and ((element_record.legislation_subgroup = p_legislation_subgroup) or (element_record.legislation_subgroup is null and p_legislation_subgroup is null))
1440 --
1441 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))
1442 --
1443 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))
1444 --
1445 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))
1446 --
1447 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))
1448 --
1449 and ((element_record.processing_priority = p_processing_priority) or (element_record.processing_priority is null and p_processing_priority is null))
1450 --
1451 and ((element_record.processing_type = p_processing_type) or (element_record.processing_type is null and p_processing_type is null))
1452 --
1453 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))
1454 --
1455 and ((element_record.qualifying_age = p_qualifying_age) or (element_record.qualifying_age is null and p_qualifying_age is null))
1456 --
1457 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))
1458 --
1459 and ((element_record.qualifying_units = p_qualifying_units) or (element_record.qualifying_units is null and p_qualifying_units is null))
1460 --
1464 --
1461 and ((element_record.iterative_flag = p_iterative_flag) or (element_record.iterative_flag is null and p_iterative_flag is null))
1462 --
1463 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))
1465 and ((element_record.iterative_priority = p_iterative_priority) or (element_record.iterative_priority is null and p_iterative_priority is null))
1466 --
1467 and ((element_record.process_mode = p_process_mode) or (element_record.process_mode is null and p_process_mode is null))
1468 --
1469 and ((element_record.grossup_flag = p_grossup_flag) or (element_record.grossup_flag is null and p_grossup_flag is null))
1470 --
1471 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))
1472 --
1473 and ((element_record.advance_indicator = p_advance_indicator) or (element_record.advance_indicator is null and p_advance_indicator is null))
1474 --
1475 and ((element_record.advance_payable = p_advance_payable) or (element_record.advance_payable is null and p_advance_payable is null))
1476 --
1477 and ((element_record.advance_deduction = p_advance_deduction) or (element_record.advance_deduction is null and p_advance_deduction is null))
1478 --
1479 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))
1480 --
1481 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))
1482 --
1483 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))
1484 --
1485 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
1486 --
1487 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))
1488 --
1489 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))
1490 --
1491 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))
1492 --
1493 then
1494 return;
1495 else
1496 hr_utility.set_message(0, 'FORM_RECORD_CHANGED');
1497 hr_utility.raise_error;
1498 end if;
1499 --
1500 end lock_row;
1501 -----------------------------------------------------------------------
1502 function DATE_EFFECTIVELY_UPDATED (
1503 --
1504 --******************************************************************************
1505 --* Returns TRUE if more than one row exists with the parameter element type ID*
1506 --******************************************************************************
1507 --
1508 -- parameters are:
1509 --
1510 p_element_type_id number,
1511 p_rowid varchar2,
1512 p_error_if_true boolean default FALSE
1513 --
1514 ) return boolean is
1515 --
1516 v_dated_updates boolean := FALSE;
1517 --
1518 cursor csr_dated_updates is
1519 select 1
1520 from pay_element_types_f
1521 where element_type_id = p_element_type_id
1522 and rowid <> p_rowid;
1523 --
1524 begin
1525 --
1526 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DATE_EFFECTIVELY_UPDATED',1);
1527 --
1528 open csr_dated_updates;
1529 fetch csr_dated_updates into g_dummy_number;
1530 v_dated_updates := csr_dated_updates%found;
1531 close csr_dated_updates;
1532 --
1533 if v_dated_updates and p_error_if_true then
1534 hr_utility.set_message (801,'PAY_6460_ELEMENT_NO_PROC_CORR');
1535 hr_utility.raise_error;
1536 end if;
1537 --
1538 return v_dated_updates;
1539 --
1540 end date_effectively_updated;
1541 -----------------------------------------------------------------------
1542 function STOP_ENTRY_RULES_EXIST (
1543 --
1544 --*****************************************************************************
1545 --* Returns TRUE if there are existing formula result rules for the parameter *
1546 --* element type, whose type is stop-entry, and which are not targetting the *
1547 --* source element type
1548 --*****************************************************************************
1549 --
1550 -- Parameters are:
1551 --
1552 p_element_type_id number,
1553 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
1554 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
1555 p_error_if_true boolean default FALSE
1556 --
1557 ) return boolean is
1558 --
1559 v_stop_entry_rules boolean;
1560 --
1561 cursor csr_stop_entry_rules is
1562 select null
1563 from pay_formula_result_rules_f FRR,
1564 pay_status_processing_rules_f SPR
1565 where p_element_type_id = frr.element_type_id
1566 and frr.result_rule_type = 'S'
1567 and spr.STATUS_PROCESSING_RULE_ID = frr.STATUS_PROCESSING_RULE_ID
1568 and spr.element_type_id <> p_element_type_id
1569 and spr.effective_start_date between p_validation_start_date
1570 and p_validation_end_date
1571 and frr.effective_start_date between p_validation_start_date
1575 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.STOP_ENTRY_RULES_EXIST',1);
1572 and p_validation_end_date;
1573 begin
1574 --
1576 --
1577 open csr_stop_entry_rules;
1578 fetch csr_stop_entry_rules into g_dummy_number;
1579 v_stop_entry_rules := csr_stop_entry_rules%found;
1580 close csr_stop_entry_rules;
1581 --
1582 if v_stop_entry_rules and p_error_if_true then
1583 hr_utility.set_message (801,'PAY_6157_ELEMENT_NO_DEL_FRR');
1584 hr_utility.raise_error;
1585 end if;
1586 --
1587 return v_stop_entry_rules;
1588 --
1589 end stop_entry_rules_exist;
1590 -----------------------------------------------------------------------
1591 function RUN_RESULTS_EXIST (
1592 --
1593 --************************************************************************
1594 --* Returns TRUE if run results exist for the parameter element type, in *
1595 --* payroll actions during the validation period *
1596 --* The driving cursors have been rewritten to allow for the removal of *
1597 --* the element_type_id index on pay_run_results. The function now *
1598 --* relies on their being run result values present for any input value *
1599 --* if the element would have run results. The only current exception *
1600 --* to this would be for elements with no input values. This is handled *
1601 --* in a different, less performant cursor. *
1602 --************************************************************************
1603 --
1604 -- Parameters are:
1605 --
1606 p_element_type_id number,
1607 p_validation_start_date date default to_date ('01/01/0001',
1608 'DD/MM/YYYY'),
1609 p_validation_end_date date default to_date ('31/12/4712',
1610 'DD/MM/YYYY'),
1611 p_DML_action_being_checked varchar2 default 'UPDATE',
1612 p_error_if_true boolean default FALSE
1613 --
1614 ) return boolean is
1615 --
1616 v_run_results_exist boolean;
1617 v_input_values_exist boolean;
1618 v_input_value_id pay_input_values_f.input_value_id%type;
1619 --
1620 cursor csr_input_values is
1621 select iv.input_value_id
1622 from pay_input_values_f iv
1623 where iv.element_type_id = p_element_type_id;
1624
1625 cursor csr_run_result_values (p_input_value_id NUMBER) is
1626 select 1
1627 from dual
1628 where exists
1629 (select /*+ ORDERED INDEX(RESULT PAY_RUN_RESULTS_PK)
1630 USE_NL(RESULT ASSIGN PAYROLL) */ 1
1631 from pay_run_result_values VALUE,
1632 pay_run_results RESULT,
1633 pay_assignment_actions ASSIGN,
1634 pay_payroll_actions PAYROLL
1635 where value.run_result_id = result.run_result_id
1636 and assign.assignment_action_id = result.assignment_action_id
1637 and assign.payroll_action_id = payroll.payroll_action_id
1638 and value.input_value_id = p_input_value_id
1639 and payroll.effective_date between p_validation_start_date
1640 and p_validation_end_date);
1641
1642 cursor csr_run_results is
1643 select 1
1644 from dual
1645 where exists
1646 (select /*+ INDEX(PAYROLL PAY_PAYROLL_ACTIONS_PK)
1647 INDEX(ASSIGN PAY_ASSIGNMENT_ACTIONS_PK) */ 1
1648 from pay_run_results RUN,
1649 pay_payroll_actions PAYROLL,
1650 pay_assignment_actions ASSIGN
1651 where run.element_type_id = p_element_type_id
1652 and assign.assignment_action_id = run.assignment_action_id
1653 and assign.payroll_action_id = payroll.payroll_action_id
1654 and payroll.effective_date between p_validation_start_date
1655 and p_validation_end_date);
1656 --
1657 begin
1658 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',1);
1659 /* Check if the element type has any input values */
1660
1661 open csr_input_values;
1662 fetch csr_input_values into v_input_value_id;
1663 v_input_values_exist := csr_input_values%found;
1664 close csr_input_values;
1665
1666 /* If input values exist use the input value to check if run result
1667 values exists. */
1668
1669 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',5);
1670
1671 if v_input_values_exist then
1672 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',10);
1673 open csr_run_result_values(v_input_value_id);
1674 fetch csr_run_result_values into g_dummy_number;
1675 v_run_results_exist := csr_run_result_values%found;
1676 close csr_run_result_values;
1677 else
1678 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',15);
1679 open csr_run_results;
1680 fetch csr_run_results into g_dummy_number;
1681 v_run_results_exist := csr_run_results%found;
1682 close csr_run_results;
1683 end if;
1684
1685 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.RUN_RESULTS_EXIST',20);
1686 if v_run_results_exist and p_error_if_true then
1687 --
1688 if p_DML_action_being_checked = 'UPDATE' then
1689 hr_utility.set_message (801,'PAY_6909_ELEMENT_NO_UPD_RR');
1690 --
1694 end if;
1691 elsif p_DML_action_being_checked = 'DELETE' then
1692 hr_utility.set_message (801,'PAY_6242_ELEMENTS_NO_DEL_RR');
1693 --
1695 --
1696 hr_utility.raise_error;
1697 --
1698 end if;
1699 --
1700 return v_run_results_exist;
1701 --
1702 end run_results_exist;
1703 -----------------------------------------------------------------------
1704 function FED_BY_INDIRECT_RESULTS (
1705 --
1706 --*****************************************************************************
1707 --* Returns TRUE if the parameter element type has input values which are fed *
1708 --* by results from other element types' input values *
1709 --*****************************************************************************
1710 --
1711 -- Parameters are:
1712 --
1713 p_element_type_id number,
1714 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
1715 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
1716 p_error_if_true boolean default FALSE
1717 ) return boolean is
1718 --
1719 v_fed_by_indirect_results boolean;
1720 --
1721 begin
1722 --
1723 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.FED_BY_INDIRECT_RESULTS',1);
1724 --
1725 -- Find formula result rules of type Indirect ('I')
1726 open g_csr_result_rules ( p_element_type_id,
1727 p_validation_start_date,
1728 p_validation_end_date,
1729 'I' );
1730 --
1731 fetch g_csr_result_rules into g_dummy_number;
1732 v_fed_by_indirect_results := g_csr_result_rules%found;
1733 close g_csr_result_rules;
1734 --
1735 -- bug 374841.Invalid message number 69012 changed to 6912.19-JUN-1996 mlisieck
1736 if v_fed_by_indirect_results and p_error_if_true then
1737 hr_utility.set_message (801,'PAY_6912_ELEMENT_NO_FRR_UPD');
1738 hr_utility.raise_error;
1739 end if;
1740 --
1741 return v_fed_by_indirect_results;
1742 --
1743 end fed_by_indirect_results;
1744 -----------------------------------------------------------------------
1745 function UPDATE_RECURRING_RULES_EXIST (
1746 --
1747 --**************************************************************************
1748 --* Returns TRUE if the parameter element type has input values which are *
1749 --* subject to result rules of type update-recurring during the validation *
1750 --* period, and the source element is different from the target element. *
1751 --**************************************************************************
1752 --
1753 -- Parameters are:
1754 --
1755 p_element_type_id number,
1756 p_validation_start_date date,
1757 p_validation_end_date date,
1758 p_error_if_true boolean default FALSE
1759 --
1760 ) return boolean is
1761 --
1762 v_update_recurring boolean := FALSE;
1763 v_status_processing_rule_id number;
1764 v_element_type_id number;
1765 --
1766 cursor csr_source_element is
1767 select element_type_id
1768 from pay_status_processing_rules_f
1769 where element_type_id = v_element_type_id
1770 and status_processing_rule_id = v_status_processing_rule_id;
1771 --
1772 begin
1773 --
1774 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.UPDATE_RECURRING_RULES_EXIST',1);
1775 --
1776 -- Find formula result rules of type Update Recurring ('U')
1777 open g_csr_result_rules ( p_element_type_id,
1778 p_validation_start_date,
1779 p_validation_end_date,
1780 p_rule_type => 'U' );
1781 --
1782 fetch g_csr_result_rules into v_status_processing_rule_id;
1783 --
1784 if g_csr_result_rules%found then
1785 --
1786 open csr_source_element;
1787 fetch csr_source_element into v_element_type_id;
1788 v_update_recurring := (csr_source_element%found
1789 and v_element_type_id <> p_element_type_id);
1790 close csr_source_element;
1791 --
1792 end if;
1793 --
1794 close g_csr_result_rules;
1795 --
1796 if v_update_recurring and p_error_if_true then
1797 hr_utility.set_message (801,'HR_6954_PAY_ELE_NO_UPD_REC');
1798 hr_utility.raise_error;
1799 end if;
1800 --
1801 return v_update_recurring;
1802 --
1803 end update_recurring_rules_exist;
1804 --
1805 --
1806 --
1807 --
1808 --
1809 -----------------------------------------------------------------------------
1810 function ELEMENT_USED_AS_PAY_BASIS (
1811 --
1812 p_element_type_id number,
1813 p_error_if_true boolean default FALSE) return boolean is
1814 --
1815 --*************************************************************************
1816 --* Returns TRUE if the element has an input value which is used as a pay
1817 --* basis.
1818 --*************************************************************************
1819 --
1820 v_pay_basis_element boolean := FALSE;
1821 --
1822 cursor csr_pay_basis is
1823 select 1
1824 from per_pay_bases BASIS,
1825 pay_input_values_f IV
1826 where iv.input_value_id = basis.input_value_id
1827 and iv.element_type_id = p_element_type_id;
1828 --
1829 begin
1830 --
1831 open csr_pay_basis;
1835 --
1832 fetch csr_pay_basis into g_dummy_number;
1833 v_pay_basis_element := csr_pay_basis%found;
1834 close csr_pay_basis;
1836 if v_pay_basis_element and p_error_if_true then
1837 --
1838 hr_utility.set_message (801, 'PAY_6965_INPVAL_NO_DEL_SB');
1839 hr_utility.raise_error;
1840 --
1841 end if;
1842 --
1843 return v_pay_basis_element;
1844 --
1845 end element_used_as_pay_basis;
1846 -----------------------------------------------------------------------------
1847 procedure CHECK_RELATIONSHIPS (
1848 --
1849 --*************************************************************************
1850 --* Checks all relationships required to establish various forms item *
1851 --* properties. This simply saves FORMS from having to call each of this *
1852 --* procedure's called functions separately and thus cuts down on network *
1853 --* traffic *
1854 --*************************************************************************
1855 --
1856 -- Parameters are:
1857 --
1858 p_element_type_id number,
1859 p_rowid varchar2,
1860 --
1861 -- Validation period
1862 p_validation_start_date date
1863 default to_date ('01/01/0001','DD/MM/YYYY'),
1864 p_validation_end_date date
1865 default to_date ('31/12/4712','DD/MM/YYYY'),
1866 --
1867 -- The results of the relationship checks must be passed back
1868 p_run_results out nocopy boolean,
1869 p_element_links out nocopy boolean,
1870 p_indirect_results out nocopy boolean,
1871 p_dated_updates out nocopy boolean,
1872 p_update_recurring out nocopy boolean,
1873 p_pay_basis out nocopy boolean,
1874 p_stop_entry_rules out nocopy boolean) is
1875 --
1876 begin
1877 --
1878 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',1);
1879 --
1880 -- Perform individual checks and place results directly in the OUT parameters
1881 --
1882 p_run_results := run_results_exist ( p_element_type_id,
1883 p_validation_start_date,
1884 p_validation_end_date);
1885 --
1886 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',2);
1887 --
1888 p_element_links := links_exist ( p_element_type_id,
1889 p_validation_start_date,
1890 p_validation_end_date );
1891
1892 --
1893 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',3);
1894 --
1895 p_indirect_results := fed_by_indirect_results ( p_element_type_id,
1896 p_validation_start_date,
1897 p_validation_end_date);
1898 --
1899 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',4);
1900 --
1901 p_dated_updates := date_effectively_updated ( p_element_type_id,
1902 p_rowid);
1903 --
1904 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',5);
1905 --
1906 p_stop_entry_rules := stop_entry_rules_exist ( p_element_type_id,
1907 p_validation_start_date,
1908 p_validation_end_date);
1909 --
1910 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',6);
1911 --
1912 p_update_recurring := update_recurring_rules_exist (p_element_type_id,
1913 p_validation_start_date,
1914 p_validation_end_date);
1915 --
1916 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.CHECK_RELATIONSHIPS',6);
1917 --
1918 p_pay_basis := element_used_as_pay_basis (p_element_type_id);
1919 --
1920 end check_relationships;
1921 --
1922 --
1923 --
1924 --
1925 --
1926 -----------------------------------------------------------------------------
1927 function ELEMENT_IS_IN_AN_ELEMENT_SET (
1928 --
1929 --**************************************************************
1930 --* Returns TRUE if the parameter element is in an element set *
1931 --**************************************************************
1932 --
1933 -- Parameters are:
1934 --
1935 p_element_type_id number,
1936 p_error_if_true boolean default FALSE
1937 ) return boolean is
1938 --
1939 v_in_set boolean := FALSE;
1940 --
1941 cursor csr_element_set is
1942 select null
1943 from pay_element_type_rules
1944 where element_type_id = p_element_type_id;
1945 --
1946 begin
1947 --
1948 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_IS_IN_AN_ELEMENT_SET',1);
1949 --
1950 open csr_element_set;
1951 fetch csr_element_set into g_dummy_number;
1952 v_in_set := csr_element_set%found;
1953 close csr_element_set;
1954 --
1955 if v_in_set and p_error_if_true then
1956 hr_utility.set_message (801,'PAY_6713_ELEMENT_NO_DEL_RULE');
1957 hr_utility.raise_error;
1958 end if;
1959 --
1960 return v_in_set;
1961 --
1962 end element_is_in_an_element_set;
1963 --
1967 --
1964 --
1965 --
1966 --
1968 -----------------------------------------------------------------------------
1969 function LINKS_EXIST (
1970 --
1971 --***************************************************************************
1972 --* Returns TRUE if the parameter element type has element links during the *
1973 --* validation period *
1974 --***************************************************************************
1975 --
1976 -- Parameters are:
1977 --
1978 p_element_type_id number,
1979 p_validation_start_date date default to_date ('01/01/0001',
1980 'DD/MM/YYYY'),
1981 p_validation_end_date date default to_date ('31/12/4712',
1982 'DD/MM/YYYY'),
1983 p_DML_action_being_checked varchar2 default 'UPDATE',
1984 p_error_if_true boolean default FALSE
1985 --
1986 ) return boolean is
1987 --
1988 v_links_exist boolean;
1989 --
1990 cursor csr_links is
1991 select 1
1992 from pay_element_links_f
1993 where element_type_id = p_element_type_id
1994 and effective_end_date >= p_validation_start_date
1995 and effective_start_date <= p_validation_end_date;
1996 --
1997 begin
1998 --
1999 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.LINKS_EXIST',1);
2000 --
2001 open csr_links;
2002 fetch csr_links into g_dummy_number;
2003 v_links_exist := csr_links%found;
2004 close csr_links;
2005 --
2006 if v_links_exist and p_error_if_true then
2007 if p_DML_action_being_checked = 'UPDATE' then
2008 hr_utility.set_message (801,'PAY_6147_ELEMENT_LINK_UPDATE');
2009 --
2010 elsif p_DML_action_being_checked = 'DELETE' then
2011 hr_utility.set_message (801,'PAY_6155_ELEMENT_NO_DEL_LINK');
2012 --
2013 end if;
2014 hr_utility.raise_error;
2015 end if;
2016 --
2017 return v_links_exist;
2018 --
2019 end links_exist;
2020 --
2021 --
2022 --
2023 --
2024 --
2025 -----------------------------------------------------------------------------
2026 function ACCRUAL_PLAN_EXISTS (
2027 --
2028 --***************************************************************************
2029 --* Returns TRUE if there are accrual plans for the parameter element type
2030 --***************************************************************************
2031 --
2032 -- Parameters are:
2033 --
2034 p_element_type_id number,
2035 p_error_if_true boolean default FALSE) return boolean is
2036 --
2037 v_accrual_exists boolean := FALSE;
2038 --
2039 cursor csr_accrual is
2040 select null
2041 from pay_accrual_plans
2042 where accrual_plan_element_type_id = p_element_type_id;
2043 --
2044 begin
2045 --
2046 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ACCRUAL_PLAN_EXISTS',1);
2047 --
2048 open csr_accrual;
2049 fetch csr_accrual into g_dummy_number;
2050 v_accrual_exists := csr_accrual%found;
2051 close csr_accrual;
2052 --
2053 if v_accrual_exists and p_error_if_true then
2054 hr_utility.set_message (801,'PAY_35560_ELT_NO_DEL_ACCRUAL');
2055 hr_utility.raise_error;
2056 end if;
2057 --
2058 return v_accrual_exists;
2059 --
2060 end accrual_plan_exists;
2061 --
2062 --
2063 --
2064 --
2065 --
2066 -----------------------------------------------------------------------------
2067 function COBRA_BENEFITS_EXIST (
2068 --
2069 --***************************************************************************
2070 --* Returns TRUE if there are COBRA benefits for the parameter element type *
2071 --* within the validation period *
2072 --***************************************************************************
2073 --
2074 -- Parameters are:
2075 --
2076 p_element_type_id number,
2077 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2078 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
2079 p_error_if_true boolean default FALSE
2080 ) return boolean is
2081 --
2082 v_cobra_exists boolean := FALSE;
2083 --
2084 cursor csr_cobra is
2085 select null
2086 from per_cobra_coverage_benefits_f
2087 where element_type_id = p_element_type_id
2088 and effective_start_date <= p_validation_end_date
2089 and effective_end_date >= p_validation_start_date;
2090 --
2091 begin
2092 --
2093 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.COBRA_BENEFITS_EXIST',1);
2094 --
2095 open csr_cobra;
2096 fetch csr_cobra into g_dummy_number;
2097 v_cobra_exists := csr_cobra%found;
2098 close csr_cobra;
2099 --
2100 if v_cobra_exists and p_error_if_true then
2101 hr_utility.set_message (801,'PAY_COBRA_BENS_NO_DEL');
2102 hr_utility.raise_error;
2103 end if;
2104 --
2105 return v_cobra_exists;
2106 --
2107 end cobra_benefits_exist;
2108 --
2109 --
2110 --
2111 --
2112 --
2113 -----------------------------------------------------------------------------
2114 function benefit_contributions_exist (
2115 --
2119 --*****************************************************************************
2116 --*****************************************************************************
2117 --* Returns TRUE if there are benefit contributions which refer to the
2118 --* specified element.
2120 --
2121 p_element_type_id number,
2122 p_validation_start_date date,
2123 p_validation_end_date date,
2124 p_error_if_true boolean default FALSE) return boolean is
2125 --
2126 v_contribution_exists boolean := FALSE;
2127 --
2128 cursor csr_contribution is
2129 select 1
2130 from ben_benefit_contributions_f
2131 where element_type_id = p_element_type_id
2132 and effective_start_date <= p_validation_end_date
2133 and effective_end_date >= p_validation_start_date;
2134 --
2135 begin
2136 --
2137 hr_utility.set_location
2138 ('PAY_ELEMENT_TYPES_PKG.benefit_contributions_exist',1);
2139 --
2140 open csr_contribution;
2141 fetch csr_contribution into g_dummy_number;
2142 v_contribution_exists := csr_contribution%found;
2143 close csr_contribution;
2144 --
2145 if v_contribution_exists and p_error_if_true then
2146 hr_utility.set_message (801,'');
2147 hr_utility.raise_error;
2148 end if;
2149 --
2150 return v_contribution_exists;
2151 --
2152 end benefit_contributions_exist;
2153 -----------------------------------------------------------------------------
2154 function DELETION_ALLOWED (
2155 --
2156 --*****************************************************************************
2157 --* Returns TRUE if all the business rules relating to deletion of an element *
2158 --* type are complied with for the parameter element or else returns an error *
2159 --*****************************************************************************
2160 --
2161 -- Parameters are:
2162 --
2163 p_element_type_id number,
2164 p_processing_priority number,
2165 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2166 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
2167 p_delete_mode varchar2 default 'ZAP'
2168 ) return boolean is
2169 --
2170 v_deletion_allowed boolean := TRUE;
2171 --
2172 begin
2173 --
2174 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DELETION_ALLOWED',1);
2175 --
2176 -- Check business rules relating to deletion of element type
2177 --
2178 if (p_delete_mode = 'DELETE_NEXT_CHANGE'
2179 and priority_result_rule_violated ( p_element_type_id,
2180 p_processing_priority,
2181 p_validation_start_date,
2182 p_validation_end_date,
2183 p_error_if_true => TRUE))
2184 --
2185 or (p_delete_mode <> 'DELETE_NEXT_CHANGE'
2186 --
2187 and (links_exist ( p_element_type_id,
2188 p_validation_start_date,
2189 p_validation_end_date,
2190 p_DML_action_being_checked => 'DELETE',
2191 p_error_if_true => TRUE)
2192 -- Bug # 4991482 : Added to raise an error message if any run results exists.
2193 or run_results_exist ( p_element_type_id,
2194 p_validation_start_date,
2195 p_validation_end_date,
2196 p_DML_action_being_checked => 'DELETE',
2197 p_error_if_true => TRUE)
2198 --
2199 or cobra_benefits_exist (p_element_type_id,
2200 p_validation_start_date,
2201 p_validation_end_date,
2202 p_error_if_true => TRUE)
2203 --
2204 or (p_delete_mode = 'ZAP'
2205 --
2206 and (element_is_in_an_element_set ( p_element_type_id,
2207 p_error_if_true => TRUE)
2208 --
2209 or element_used_as_pay_basis (p_element_type_id,
2210 p_error_if_true => TRUE)
2211 --
2212 or benefit_contributions_exist (p_element_type_id,
2213 p_validation_start_date,
2214 p_validation_end_date,
2215 p_error_if_true => TRUE)
2216 --
2217 or accrual_plan_exists ( p_element_type_id,
2218 p_error_if_true => TRUE)
2219 --
2220 or stop_entry_rules_exist ( p_element_type_id,
2221 p_error_if_true => TRUE)
2222 )
2223 )
2224 or pay_input_values_pkg.cant_delete_all_input_values (
2225 --
2226 p_element_type_id,
2227 p_delete_mode,
2228 p_validation_start_date,
2229 p_validation_end_date,
2230 p_error_if_true => TRUE)
2231 )
2232 )
2233 or dt_api.rows_exist(
2234 p_base_table_name => 'ben_acty_base_rt_f',
2235 p_base_key_column => 'element_type_id',
2236 p_base_key_value => p_element_type_id,
2240 THEN
2237 p_from_date => p_validation_start_date,
2238 p_to_date => p_validation_end_date
2239 )
2241 v_deletion_allowed := FALSE;
2242 --
2243 END IF;
2244 --
2245
2246 return v_deletion_allowed;
2247 --
2248 end deletion_allowed;
2249 --
2250 --
2251 --
2252 --
2253 --
2254 -----------------------------------------------------------------------------
2255 function ELEMENT_PRIORITY_TOO_HIGH (
2256 --
2257 --****************************************************************************
2258 --* Returns TRUE if an element has a priority higher than an element it feeds*
2259 --****************************************************************************
2260 --
2261 -- Parameters are:
2262 --
2263 p_element_type_id number,
2264 --
2265 -- The priority of the element being checked
2266 p_processing_priority number,
2267 --
2268 -- The validation period
2269 p_validation_start_date date
2270 default to_date ('01/01/0001','DD/MM/YYYY'),
2271 p_validation_end_date date
2272 default to_date ('31/12/4712','DD/MM/YYYY')
2273 --
2274 ) return boolean is
2275 --
2276 v_priority_too_high boolean := FALSE;
2277 --
2278 cursor csr_elements_fed_by_this_one is
2279 --
2280 /* Returns a row if there are any elements whose input
2281 values rely on the output from the parameter element,
2282 and which have a lower priority than the parameter
2283 priority (not allowed) */
2284 --
2285 select 1
2286 from pay_status_processing_rules_f STATUS,
2287 pay_formula_result_rules_f RESULT,
2288 pay_input_values_f INPUT,
2289 pay_element_types_f ELEMENT
2290 where status.status_processing_rule_id = result.status_processing_rule_id
2291 and result.input_value_id = input.input_value_id
2292 and input.element_type_id = element.element_type_id
2293 and result.result_rule_type = 'I'
2294 and status.element_type_id = p_element_type_id
2295 and element.element_type_id <> p_element_type_id
2296 and element.processing_priority < p_processing_priority
2297 and (status.effective_end_date >= p_validation_start_date
2298 and status.effective_start_date <= p_validation_end_date)
2299 and (result.effective_end_date >= p_validation_start_date
2300 and result.effective_start_date <= p_validation_end_date);
2301 --
2302 begin
2303 --
2304 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_PRIORITY_TOO_HIGH',1);
2305 --
2306 open csr_elements_fed_by_this_one;
2307 fetch csr_elements_fed_by_this_one into g_dummy_number;
2308 v_priority_too_high := csr_elements_fed_by_this_one%found;
2309 close csr_elements_fed_by_this_one;
2310 --
2311 return v_priority_too_high;
2312 --
2313 end element_priority_too_high;
2314 --
2315 --
2316 --
2317 --
2318 --
2319 -------------------------------------------------------------------------------
2320 function ELEMENT_PRIORITY_TOO_LOW (
2321 --
2322 --***************************************************************************
2323 --* Returns TRUE if the parameter element has a priority lower than that of *
2324 --* an element whose input value results feed it *
2325 --***************************************************************************
2326 --
2327 -- Parameters are:
2328 --
2329 p_element_type_id number,
2330 p_processing_priority number,
2331 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2332 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY')
2333 --
2334 ) return boolean is
2335 --
2336 v_priority_too_low boolean := FALSE;
2337 --
2338 cursor csr_elements_feeding_this_one is
2339 --
2340 /* Returns a row if there are elements whose output feed
2341 input values of the parameter element and which have a
2342 priority higher than the parameter priority
2343 (not allowed) */
2344 --
2345 select 1
2346 from pay_status_processing_rules_f STATUS,
2347 pay_formula_result_rules_f RESULT,
2348 pay_input_values_f INPUT,
2349 pay_element_types_f ELEMENT
2350 where result.input_value_id = input.input_value_id
2351 and status.element_type_id = element.element_type_id
2352 and result.status_processing_rule_id= status.status_processing_rule_id
2353 and result.result_rule_type = 'I'
2354 and input.element_type_id = p_element_type_id
2355 and element.element_type_id <> p_element_type_id
2356 and element.processing_priority > p_processing_priority
2357 and (status.effective_end_date >= p_validation_start_date
2358 and status.effective_start_date <= p_validation_end_date)
2359 and (result.effective_end_date >= p_validation_start_date
2360 and result.effective_start_date<= p_validation_end_date);
2361 --
2362 begin
2363 --
2364 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_PRIORITY_TOO_LOW',1);
2365 --
2369 close csr_elements_feeding_this_one;
2366 open csr_elements_feeding_this_one;
2367 fetch csr_elements_feeding_this_one into g_dummy_number;
2368 v_priority_too_low := csr_elements_feeding_this_one%found;
2370 --
2371 return v_priority_too_low;
2372 --
2373 end element_priority_too_low;
2374 --
2375 --
2376 --
2377 --
2378 --
2379 ---------------------------------------------------------------------------
2380 function PRIORITY_RESULT_RULE_VIOLATED (
2381 --
2382 --*****************************************************************************
2383 --* Returns TRUE if either *
2384 --* 1. The element will be processed in a payroll run before an element whose*
2385 --* results are needed to process it *
2386 --* or *
2387 --* 2. The element will be processed in a run after an element which needs *
2388 --* the results produced *
2389 --*****************************************************************************
2390 --
2391 -- Parameters are:
2392 --
2393 p_element_type_id number,
2394 p_processing_priority number,
2395 p_validation_start_date date default to_date ('01/01/0001','DD/MM/YYYY'),
2396 p_validation_end_date date default to_date ('31/12/4712','DD/MM/YYYY'),
2397 p_error_if_true boolean default FALSE
2398 --
2399 ) return boolean is
2400 --
2401 v_priority_rule_violated boolean := FALSE;
2402 --
2403 begin
2404 --
2405 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.PRIORITY_RESULT_RULE_VIOLATED',1);
2406 --
2407 if element_priority_too_high ( p_element_type_id,
2408 p_processing_priority,
2409 p_validation_start_date,
2410 p_validation_end_date )
2411 --
2412 or element_priority_too_low ( p_element_type_id,
2413 p_processing_priority,
2414 p_validation_start_date,
2415 p_validation_end_date ) then
2416 --
2417 v_priority_rule_violated := TRUE;
2418 --
2419 if v_priority_rule_violated and p_error_if_true then
2420 hr_utility.set_message (801,'PAY_6149_ELEMENT_PRIORITY_UPD');
2421 hr_utility.raise_error;
2422 end if;
2423 --
2424 end if;
2425 --
2426 return v_priority_rule_violated;
2427 --
2428 end priority_result_rule_violated;
2429 --
2430 --
2431 --
2432 --
2433 --
2434 -------------------------------------------------------------------------------
2435 function NAME_IS_NOT_UNIQUE (
2436 --
2437 --******************************************************************************
2438 --* Returns TRUE if the element name has been duplicated within business group *
2439 --* and legislation code. If the name is the only parameter, then the check *
2440 --* will return TRUE if the name is not unique within the generic data set. *
2441 --******************************************************************************
2442 --
2443 -- Parameters are:
2444 --
2445 p_element_name varchar2,
2446 p_element_type_id number default null,
2447 p_business_group_id number default null,
2448 p_legislation_code varchar2 default null,
2449 p_error_if_true boolean default FALSE
2450 --
2451 ) return boolean is
2452 --
2453 v_name_duplicated boolean := FALSE;
2454 --
2455 cursor csr_duplicate is
2456 select null
2457 from pay_element_types_f et,
2458 pay_element_types_f_tl et_tl
2459 where upper(translate(p_element_name,'x_','x '))
2460 = upper(translate(et_tl.element_name,'x_','x '))
2461 and (et.element_type_id <> p_element_type_id
2462 or p_element_type_id is null)
2463 and ( p_business_group_id = et.business_group_id + 0
2464 or ( et.business_group_id is null
2465 and p_legislation_code = et.legislation_code ))
2466 and et_tl.element_type_id = et.element_type_id
2467 and et_tl.language = userenv('LANG');
2468 --
2469 begin
2470 --
2471 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.NAME_IS_NOT_UNIQUE',1);
2472 --
2473 open csr_duplicate;
2474 fetch csr_duplicate into g_dummy_number;
2475 v_name_duplicated := csr_duplicate%found;
2476 close csr_duplicate;
2477 --
2478 if v_name_duplicated and p_error_if_true then
2479 hr_utility.set_message (801,'PAY_6137_ELEMENT_DUP_NAME');
2480 hr_utility.raise_error;
2481 end if;
2482 --
2483 return v_name_duplicated;
2484 --
2485 end name_is_not_unique;
2486 --
2487 --
2488 --
2489 --
2490 --
2491 -----------------------------------------------------------------------------
2492 function ELEMENT_START_DATE (p_element_type_id number) return date is
2493 --
2494 --******************************************************************************
2495 --* Returns the minimum start date for a given element *
2496 --******************************************************************************
2497 --
2498 cursor csr_date is
2502 --
2499 select min (effective_start_date)
2500 from pay_element_types_f
2501 where element_type_id = p_element_type_id;
2503 v_start_date date;
2504 --
2505 begin
2506 --
2507 open csr_date;
2508 fetch csr_date into v_start_date;
2509 close csr_date;
2510 --
2511 return v_start_date;
2512 --
2513 end element_start_date;
2514 --------------------------------------------------------------------------------
2515 function ELEMENT_END_DATE (p_element_type_id number) return date is
2516 --
2517 --******************************************************************************
2518 --* Returns the maximum end date for a given element *
2519 --******************************************************************************
2520 --
2521 cursor csr_end_date is
2522 select max (effective_end_date)
2523 from pay_element_types_f
2524 where element_type_id = p_element_type_id;
2525 --
2526 v_end_date date;
2527 --
2528 begin
2529 --
2530 open csr_end_date;
2531 fetch csr_end_date into v_end_date;
2532 close csr_end_date;
2533 --
2534 return v_end_date;
2535 --
2536 end element_end_date;
2537 -----------------------------------------------------------------------------
2538 function ELEMENT_ENTRIES_EXIST (p_element_type_id number,
2539 p_error_if_true boolean default FALSE)
2540 return boolean is
2541 --
2542 --******************************************************************************
2543 --* Returns TRUE if there are element entries which use the link belonging to *
2544 --* the parameter element type. *
2545 --******************************************************************************
2546 --
2547 v_entries_exist boolean :=FALSE;
2548 --
2549 cursor csr_entries is
2550 select null
2551 from pay_element_entries_f ENTRY,
2552 pay_element_links_f LINK
2553 where link.element_link_id = entry.element_link_id
2554 and link.element_type_id = p_element_type_id;
2555 --
2556 begin
2557 --
2558 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.ELEMENT_ENTRIES_EXIST',1);
2559 --
2560 open csr_entries;
2561 fetch csr_entries into g_dummy_number;
2562 v_entries_exist := csr_entries%found;
2563 close csr_entries;
2564 --
2565 if v_entries_exist and p_error_if_true then
2566 hr_utility.set_message (801,'PAY_6197_INPVAL_NO_ENTRY');
2567 hr_utility.raise_error;
2568 end if;
2569 --
2570 return v_entries_exist;
2571 --
2572 end element_entries_exist;
2573 --
2574 --
2575 --
2576 --
2577 --
2578 -----------------------------------------------------------------------------
2579 procedure ADD_LANGUAGE
2580 is
2581 begin
2582 delete from PAY_ELEMENT_TYPES_F_TL T
2583 where not exists
2584 (select NULL
2585 from PAY_ELEMENT_TYPES_F B
2586 where B.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
2587 );
2588
2589 update PAY_ELEMENT_TYPES_F_TL T set (
2590 ELEMENT_NAME,
2591 REPORTING_NAME,
2592 DESCRIPTION
2593 ) = (select
2594 B.ELEMENT_NAME,
2595 B.REPORTING_NAME,
2596 B.DESCRIPTION
2597 from PAY_ELEMENT_TYPES_F_TL B
2598 where B.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
2599 and B.LANGUAGE = T.SOURCE_LANG)
2600 where (
2601 T.ELEMENT_TYPE_ID,
2602 T.LANGUAGE
2603 ) in (select
2604 SUBT.ELEMENT_TYPE_ID,
2605 SUBT.LANGUAGE
2606 from PAY_ELEMENT_TYPES_F_TL SUBB, PAY_ELEMENT_TYPES_F_TL SUBT
2607 where SUBB.ELEMENT_TYPE_ID = SUBT.ELEMENT_TYPE_ID
2608 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2609 and (SUBB.ELEMENT_NAME <> SUBT.ELEMENT_NAME
2610 or SUBB.REPORTING_NAME <> SUBT.REPORTING_NAME
2611 or (SUBB.REPORTING_NAME is null and SUBT.REPORTING_NAME is not null)
2612 or (SUBB.REPORTING_NAME is not null and SUBT.REPORTING_NAME is null)
2613 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
2614 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
2615 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
2616 ));
2617
2618 insert into PAY_ELEMENT_TYPES_F_TL (
2619 ELEMENT_TYPE_ID,
2620 ELEMENT_NAME,
2621 REPORTING_NAME,
2622 DESCRIPTION,
2623 LAST_UPDATE_DATE,
2624 LAST_UPDATED_BY,
2625 LAST_UPDATE_LOGIN,
2626 CREATED_BY,
2627 CREATION_DATE,
2628 LANGUAGE,
2629 SOURCE_LANG
2630 ) select
2631 B.ELEMENT_TYPE_ID,
2632 B.ELEMENT_NAME,
2633 B.REPORTING_NAME,
2634 B.DESCRIPTION,
2635 B.LAST_UPDATE_DATE,
2636 B.LAST_UPDATED_BY,
2637 B.LAST_UPDATE_LOGIN,
2638 B.CREATED_BY,
2639 B.CREATION_DATE,
2640 L.LANGUAGE_CODE,
2641 B.SOURCE_LANG
2642 from PAY_ELEMENT_TYPES_F_TL B, FND_LANGUAGES L
2643 where L.INSTALLED_FLAG in ('I', 'B')
2644 and B.LANGUAGE = userenv('LANG')
2645 and not exists
2646 (select NULL
2647 from PAY_ELEMENT_TYPES_F_TL T
2648 where T.ELEMENT_TYPE_ID = B.ELEMENT_TYPE_ID
2649 and T.LANGUAGE = L.LANGUAGE_CODE);
2650 end ADD_LANGUAGE;
2651 -----------------------------------------------------------------------------
2655 result varchar2(255);
2652 procedure unique_chk(X_E_ELEMENT_NAME in VARCHAR2,X_E_LEGISLATION_CODE in VARCHAR2,
2653 X_E_EFFECTIVE_START_DATE in date, X_E_EFFECTIVE_END_DATE in date)
2654 is
2656 Begin
2657 SELECT count(*) INTO result
2658 FROM pay_element_types_f
2659 WHERE nvl(ELEMENT_NAME,'~null~') = nvl(X_E_ELEMENT_NAME,'~null~')
2660 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_E_LEGISLATION_CODE,'~null~')
2661 and EFFECTIVE_START_DATE = X_E_EFFECTIVE_START_DATE
2662 and EFFECTIVE_end_DATE = X_E_EFFECTIVE_END_DATE
2663 and X_E_EFFECTIVE_START_DATE is not NULL
2664 and X_E_EFFECTIVE_END_DATE is not NULL
2665 and BUSINESS_GROUP_ID is NULL;
2666 --
2667 IF (result>1) THEN
2668 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2669 hr_utility.set_message_token('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UNIQUE_CHK');
2670 hr_utility.set_message_token('STEP','1');
2671 hr_utility.raise_error;
2672 END IF;
2673 EXCEPTION
2674 when NO_DATA_FOUND then
2675 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2676 hr_utility.set_message_token('PROCEDURE','PAY_ELEMENT_TYPES_PKG.UNIQUE_CHK');
2677 hr_utility.set_message_token('STEP','1');
2678 hr_utility.raise_error;
2679 end unique_chk;
2680 --------------------------------------------------------------------------------
2681 procedure TRANSLATE_ROW (
2682 X_E_ELEMENT_NAME in varchar2,
2683 X_E_LEGISLATION_CODE in varchar2,
2684 X_E_EFFECTIVE_START_DATE in date,
2685 X_E_EFFECTIVE_END_DATE in date,
2686 X_ELEMENT_NAME in varchar2,
2687 X_REPORTING_NAME in varchar2,
2688 X_DESCRIPTION in varchar2,
2689 X_OWNER in varchar2 ) is
2690 --
2691 -- Fetch the element_type_id. This used to be a sub-query in the update
2692 -- statement.
2693 --
2694 cursor csr_ele_id is
2695 select element_type_id
2696 from pay_element_types_f
2697 where nvl(ELEMENT_NAME,'~null~') = nvl(X_E_ELEMENT_NAME,'~null~')
2698 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_E_LEGISLATION_CODE,'~null~')
2699 and EFFECTIVE_START_DATE = X_E_EFFECTIVE_START_DATE
2700 and EFFECTIVE_end_DATE = X_E_EFFECTIVE_END_DATE
2701 and X_E_EFFECTIVE_START_DATE is not NULL
2702 and X_E_EFFECTIVE_END_DATE is not NULL
2703 and BUSINESS_GROUP_ID is NULL
2704 ;
2705 --
2706 -- Fetch information for the _TL rows that will be affected by the update.
2707 --
2708 cursor csr_tl_info
2709 (p_element_type_id in number
2710 ,p_language in varchar2
2711 ) is
2712 select element_name
2713 , language
2714 from pay_element_types_f_tl
2715 where element_type_id = p_element_type_id
2716 and p_language in (language, source_lang)
2717 ;
2718 --
2719 l_element_type_id number;
2720 l_found boolean;
2721 i binary_integer := 1;
2722 l_langs dbms_sql.varchar2s;
2723 l_lang varchar2(100);
2724 begin
2725 --
2726 -- Fetch the element_type_id.
2727 --
2728 open csr_ele_id;
2729 fetch csr_ele_id
2730 into l_element_type_id
2731 ;
2732 l_found := csr_ele_id%found;
2733 close csr_ele_id;
2734
2735 l_lang := userenv('LANG');
2736
2737 if l_found then
2738 --
2739 -- Check if database item translations are supported.
2740 --
2741 if ff_dbi_utils_pkg.translations_supported
2742 (p_legislation_code => x_e_legislation_code
2743 ) then
2744 for crec in csr_tl_info
2745 (p_element_type_id => l_element_type_id
2746 ,p_language => l_lang
2747 ) loop
2748 if upper(crec.element_name) <> upper(x_element_name) then
2749 l_langs(i) := crec.language;
2750 i := i + 1;
2751 end if;
2752 end loop;
2753 end if;
2754
2755 UPDATE pay_element_types_f_tl
2756 SET element_name = nvl(x_element_name,element_name),
2757 reporting_name = nvl(x_reporting_name,reporting_name),
2758 description = nvl(x_description,description),
2759 last_update_date = SYSDATE,
2760 last_updated_by = decode(x_owner,'SEED',1,0),
2761 last_update_login = 0,
2762 source_lang = l_lang
2763 WHERE l_lang IN (language,source_lang)
2764 AND element_type_id = l_element_type_id
2765 ;
2766
2767 --
2768 -- Write any changes to PAY_DYNDBI_CHANGES.
2769 --
2770 if l_langs.count <> 0 then
2771 pay_dyndbi_changes_pkg.element_type_change
2772 (p_element_type_id => l_element_type_id
2773 ,p_languages => l_langs
2774 );
2775 end if;
2776 end if;
2777 end TRANSLATE_ROW;
2778 --------------------------------------------------------------------------------
2779 begin
2780
2781 c_user_id := fnd_global.user_id;
2782 c_login_id := fnd_global.login_id;
2783
2784 end PAY_ELEMENT_TYPES_PKG;