[Home] [Help]
PACKAGE BODY: APPS.PY_FR_ADDITIONAL_ELEMENT_RULES
Source
1 PACKAGE BODY py_fr_additional_element_rules AS
2 /* $Header: pyfreliv.pkb 120.1.12000000.2 2007/02/28 10:02:03 spendhar noship $ */
3 g_package varchar2(80) := 'py_fr_additional_element_rules.create_extra_elements';
4
5 FUNCTION create_input_value
6 (p_element_name IN varchar2
7 ,p_input_value_name IN varchar2
8 ,p_uom_code IN varchar2
9 ,p_bg_name IN varchar2
10 ,p_element_type_id IN number
11 ,p_primary_classification_id IN number
12 ,p_business_group_id IN number
13 ,p_legislation_code IN varchar2
14 ,p_classification_type IN varchar2
15 ,p_sequence IN number
16 ,p_base_name IN varchar2 default null)
17 RETURN number IS
18 --
19 l_effective_start_date date;
20 l_effective_end_date date;
21 l_input_value_id number;
22 l_generate_db_item_flag varchar2(5) := 'Y';
23 --
24 Begin
25 --
26 select least(effective_start_date),greatest(effective_end_date)
27 into l_effective_start_date,l_effective_end_date
28 from pay_element_types_f
29 where element_type_id =p_element_type_id;
30 --
31 l_input_value_id := pay_db_pay_setup.create_input_value
32 (p_element_name => p_element_name
33 ,p_name => p_input_value_name
34 ,p_uom => ''
35 ,p_uom_code => p_uom_code
36 ,p_mandatory_flag => 'N'
37 ,p_generate_db_item_flag => 'Y'
38 ,p_default_value => ''
39 ,p_min_value => ''
40 ,p_max_value => ''
41 ,p_warning_or_error => ''
42 ,p_lookup_type => ''
43 ,p_formula_id => NULL
44 ,p_hot_default_flag => 'N'
45 ,p_display_sequence => p_sequence
46 ,p_business_group_name => p_bg_name
47 ,p_effective_start_date => l_effective_start_date
48 ,p_effective_end_date => l_effective_end_date);
49 --
50 --
51 hr_input_values.chk_input_value
52 (p_element_type_id => p_element_type_id
53 ,p_legislation_code => p_legislation_code
54 ,p_val_start_date => l_effective_start_date
55 ,p_val_end_date => l_effective_end_date
56 ,p_insert_update_flag => 'NULL'
57 ,p_input_value_id => l_input_value_id
58 ,p_rowid => ''
59 ,p_recurring_flag => 'R'
60 ,p_mandatory_flag => 'N'
61 ,p_hot_default_flag => 'N'
62 ,p_standard_link_flag => 'N'
63 ,p_classification_type => p_classification_type
64 ,p_name => p_input_value_name
65 ,p_uom => p_uom_code
66 ,p_min_value => ''
67 ,p_max_value => ''
68 ,p_default_value => ''
69 ,p_lookup_type => ''
70 ,p_formula_id => NULL
71 ,p_generate_db_items_flag => 'Y'
72 ,p_warning_or_error => '');
73 --
74 -- Bug 3794513, need to use US lang base names for context inputs.
75 -- Nb. This may not match a value in the tl table, but that is also
76 -- true of the Pay Value input.
77 if p_base_name <> p_input_value_name then
78 update pay_input_values_f
79 set name = p_base_name
80 where input_value_id = l_input_value_id;
81 end if;
82 return l_input_value_id;
83 --
84 end create_input_value;
85
86 procedure create_extra_elements
87 (p_effective_date IN date
88 ,p_accrual_plan_id IN number
89 ,p_accrual_plan_name IN varchar2
90 ,p_accrual_plan_element_type_id IN number
91 ,p_business_group_id IN number
92 ,p_pto_input_value_id IN number
93 ,p_accrual_category IN varchar2) is
94
95 l_element_id number;
96 j number;
97 l_unused_number number;
98 l_element_name varchar2(80);
99 l_item_suffix varchar2(80);
100 l_bg_name varchar2(80);
101 l_leg_code varchar2(80);
102 l_post_term_rule varchar2(80);
103 l_input_value_name varchar2(80);
104 l_uom_code varchar2(80);
105 l_primary_class_id number;
106 l_effective_start_date date;
107 l_effective_end_date date;
108 l_count number;
109 l_accrual_class_id number;
110 l_accrual_classification_name varchar2(30) := 'Accrual Information';
111 l_new_skip_rule_id ff_formulas_f.formula_id%TYPE;
112
113
114 type inp_val_table IS TABLE of pay_input_values.input_value_id%type
115 index by binary_integer;
116
117 input_value_table inp_val_table;
118
119 --
120 -- Cursor to retrieve details of absence element link, to be
121 -- copied into links for other elements.
122 --
123
124 cursor c_absence_element_link_id is
125 select *
126 from pay_element_links_f
127 where element_link_id in ( select pel.element_link_id
128 from pay_element_links_f pel,
129 pay_input_values_f piv
130 where pel.element_type_id = piv.element_type_id
131 and piv.input_value_id = p_pto_input_value_id
132 and p_effective_date between pel.effective_start_date
133 and pel.effective_end_date
134 and p_effective_date between piv.effective_start_date
135 and piv.effective_end_date );
136
137 cursor class_id is
138 select classification_id
139 from pay_element_types_f
140 where element_type_id =(select element_type_id from pay_input_values_f
141 where input_value_id = p_pto_input_value_id)
142 and rownum = 1;
143
144 cursor csr_name_translation(p_code varchar2) is
145 select meaning
146 from hr_lookups
147 where lookup_type = 'NAME_TRANSLATIONS'
148 and lookup_code = p_code;
149
150 cursor accrual_class_id is
151 select CLASSIFICATION_ID
152 from pay_element_classifications
153 where LEGISLATION_CODE = 'FR'
154 and CLASSIFICATION_NAME = 'Accrual Information';
155
156 cursor csr_skip_rule is
157 select f1.formula_id
158 from ff_formulas_f f1, ff_formula_types ft
159 where f1.formula_name = 'FR_PROCESS_IN_LAST_PRORATION_PERIOD_PROCESSED'
160 and f1.formula_type_id = ft.formula_type_id
161 and ft.formula_type_name = 'Element Skip'
162 and f1.effective_start_date = (select max(f0.effective_start_date)
163 from ff_formulas_f f0
164 where f0.legislation_code = 'FR'
165 and f0.formula_id = f1.formula_id
166 and f0.formula_type_id = f1.formula_type_id
167 and f0.business_Group_id is null);
168
169 Procedure Create_Termination_Element is
170
171 l_element_group varchar2(80);
172 l_fres_rule_id number;
173 l_formula_id number;
174 l_index number := 1;
175 l_process_rule_id number;
176 l_rate_input_value_id number;
177 l_date_input_value_id number;
178 l_input_value_id number;
179 l_end_of_time date := to_date('31/12/4712','DD/MM/YYYY');
180 l_earnings_class_id number;
181 l_act_term_date varchar2(80);
182 l_proration_group_id number;
183 l_seq_no number;
184
185 l_element_link_id number;
186 l_comment_id number;
187 l_ovn number;
188 l_effective_start_date date;
189 l_effective_end_date date;
190 TYPE input_value_rec IS RECORD
191 ( input_value_id pay_input_values_f.input_value_id%type,
192 result_name pay_formula_result_rules_f.result_name%type,
193 result_rule_type pay_formula_result_rules_f.result_rule_type%type,
194 uom pay_input_values_f.uom%type,
195 base_name pay_input_values_f.name%type,
196 meaning_iv_name pay_input_values_f.name%type);
197
198 TYPE t_input_value IS TABLE OF input_value_rec INDEX BY BINARY_INTEGER;
199 l_input_value t_input_value;
200
201 cursor csr_formula_exists IS
202 SELECT formula_id
203 FROM ff_formulas_f
204 WHERE formula_name = 'FR_HOLIDAY_TERMINATION_PAYMENT'
205 AND legislation_code = 'FR';
206
207 cursor Earnings_class_id is
208 select CLASSIFICATION_ID
209 from pay_element_classifications
210 where LEGISLATION_CODE = 'FR'
211 and CLASSIFICATION_NAME = 'Earnings';
212
213 cursor element_group is
214 select TAG ||' : '||meaning
215 from
216 fnd_lookup_values
217 where LOOKUP_TYPE = 'FR_ELEMENT_GROUP'
218 and LOOKUP_CODE = 'TERMINATION_HOLIDAY_PAY';
219
220 Begin
221 /* Create the element */
222 hr_utility.set_location('Step '||g_package,810);
223 open csr_name_translation('FR_TERM_PAYMENT');
224 fetch csr_name_translation into l_item_suffix;
225 close csr_name_translation;
226
227 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
228
229 /* Create element */
230 -- for testing ... waiting for API availability
231
232 hr_utility.set_location('Step '||g_package,815);
233 select hl.meaning
234 into l_act_term_date
235 from hr_lookups hl
236 where hl.lookup_type='TERMINATION_RULE'
237 and hl.lookup_code='A';
238
239 hr_utility.set_location('Step '||g_package,816);
240 select event_group_id
241 into l_proration_group_id
242 from pay_event_groups
243 where
244 event_group_name = 'FR_BASIC';
245
246 l_element_id := hr_accrual_plan_api.create_element
247 (p_element_name => l_element_name
248 ,p_element_description => ''
249 ,p_processing_type => 'N'
250 ,p_bg_name => l_bg_name
251 ,p_classification_name => 'Earnings'
252 ,p_legislation_code => ''
253 ,p_currency_code => 'EUR'
254 ,p_post_termination_rule => l_act_term_date
255 ,p_mult_entries_allowed => 'Y'
256 ,p_indirect_only_flag => 'N'
257 ,p_formula_id => NULL
258 ,p_processing_priority => 9591);
259
260
261 open element_group;
262 fetch element_group into
263 l_element_group;
264
265 If element_group%notfound Then
266 close element_group;
267 fnd_message.set_token('LOOKUP_CODE','TERMINATION_HOLIDAY_PAY');
268 fnd_message.set_token('LOOKUP_TYPE','FR_ELEMENT_GROUP');
269 hr_utility.set_message(801,'PAY_75051_ELEMENT_GROUP');
270 hr_utility.raise_error;
271 Else
272 update pay_element_types_f
273 set element_information1 = 'TERMINATION_HOLIDAY_PAY' -- l_element_group
274 ,element_information_category = 'FR_EARNINGS'
275 ,proration_group_id = l_proration_group_id
276 where element_type_id = l_element_id;
277 End if;
278 close element_group;
279
280 hr_utility.set_location('Step '||g_package,820);
281 open csr_name_translation('FR_ACCRUAL_PLAN');
282 fetch csr_name_translation into l_input_value_name;
283 close csr_name_translation;
284
285 l_input_value(1).result_name := 'ACCRUAL_PLAN_ID';
286 l_input_value(1).uom := 'N';
287 l_input_value(1).result_rule_type := 'I';
288 l_input_value(1).base_name := 'Accrual Plan ID';
289 l_input_value(1).meaning_iv_name := l_input_value_name;
290
291 hr_utility.set_location('Step '||g_package,830);
292
293 /* store the details of the 6 input values in a table */
294 open csr_name_translation('START_DATE');
295 fetch csr_name_translation into l_input_value_name;
296 close csr_name_translation;
297
298 hr_utility.set_location('Step '||g_package,860);
299 l_input_value(2).result_name := 'START_DATE';
300 l_input_value(2).uom := 'D';
301 l_input_value(2).result_rule_type := 'D';
302 l_input_value(2).base_name := 'Start Date';
303 l_input_value(2).meaning_iv_name := l_input_value_name;
304
305 open csr_name_translation('BASE');
306 fetch csr_name_translation into l_input_value_name;
307 close csr_name_translation;
308
309 l_input_value(3).result_name := 'DAILY_RATE';
310 l_input_value(3).uom := 'N';
311 l_input_value(3).result_rule_type := 'D';
312 l_input_value(3).base_name := 'Base';
313 l_input_value(3).meaning_iv_name := l_input_value_name;
314
315 hr_utility.set_location('Step '||g_package,870);
316
317 open csr_name_translation('RATE');
318 fetch csr_name_translation into l_input_value_name;
319 close csr_name_translation;
320
321 l_input_value(4).result_name := 'DAYS';
322 l_input_value(4).uom := 'N';
323 /* 4538139 Changed from D to I */
324 l_input_value(4).result_rule_type := 'I';
325 l_input_value(4).base_name := 'Rate';
326 l_input_value(4).meaning_iv_name := l_input_value_name;
327
328 hr_utility.set_location('Step '||g_package,890);
329
330 open csr_name_translation('FR_PAYMENT_INDEX');
331 fetch csr_name_translation into l_input_value_name;
332 close csr_name_translation;
333
334 l_input_value(5).result_name := 'PAYMENT_INDEX';
335 l_input_value(5).uom := 'N';
336 l_input_value(5).result_rule_type := 'I';
337 l_input_value(5).base_name := 'Payment Index';
338 l_input_value(5).meaning_iv_name := l_input_value_name;
339
340 hr_utility.set_location('Step '||g_package,910);
341
342 l_input_value(6).result_name := 'PAY_VALUE';
343 l_input_value(6).uom := 'N';
344 l_input_value(6).result_rule_type := 'D';
345 l_input_value(6).base_name := 'Pay Value';
346
347 hr_utility.set_location('Step '||g_package,920);
348
349 /* 4538139 - dummy rate is not used here after */
350 /* l_input_value(7).result_name := 'L_DUMMY_RATE';
351 l_input_value(7).uom := 'N';
352 l_input_value(7).result_rule_type := 'I';
353 l_input_value(7).base_name := 'Rate';
354 l_input_value(7).meaning_iv_name := l_input_value(4).meaning_iv_name; */
355
356 /* create the input value and formula results for the new Termination element
357 loop round the table created.
358 First Check if the formula is present and then create the processing rule */
359
360 OPEN csr_formula_exists;
361 FETCH csr_formula_exists INTO l_formula_id;
362 close csr_formula_exists;
363
364 hr_utility.set_location('Step '||g_package,920);
365
366 If l_formula_id IS NOT null then
367 l_process_rule_id := pay_formula_results.ins_stat_proc_rule (
368 p_business_group_id => p_business_group_id,
369 p_legislation_code => l_leg_code,
370 -- p_legislation_subgroup => g_template_leg_subgroup,
371 p_effective_start_date => p_effective_date,
372 p_effective_end_date => l_end_of_time,
373 p_element_type_id => l_element_id,
374 p_assignment_status_type_id => NULL,
375 p_formula_id => l_formula_id,
376 p_processing_rule => 'P');
377 Else
378 hr_utility.set_message(801,'PAY_75050_TERM_MISSG_FORMULA');
379 hr_utility.raise_error;
380 END IF;
381
382 open earnings_class_id;
383 fetch earnings_class_id into l_earnings_class_id;
384 close earnings_class_id;
385
386 hr_utility.set_location('Step '||g_package,930);
387 For l_index in 1..5 loop -- only loop 5 times as we don't want to create pay value
388 hr_utility.trace ('input value = ' || l_input_value(l_index).base_name);
389 l_seq_no :=l_index * 10;
390 l_input_value(l_index).input_value_id :=
391 create_input_value(l_element_name
392 ,l_input_value(l_index).meaning_iv_name
393 ,l_input_value(l_index).uom
394 ,l_bg_name
395 ,l_element_id
396 ,l_earnings_class_id
397 ,p_business_group_id
398 ,l_leg_code
399 ,'N'
400 ,l_seq_no
401 ,l_input_value(l_index).base_name);
402 End Loop;
403
404 /* Need to select the ip value id for Pay Value as this is created automatically */
405 Select input_value_id
406 into l_input_value(6).input_value_id
407 from pay_input_values_f
408 where element_type_id = l_element_id
409 and name = 'Pay Value';
410
411 /* Rate input value is target for 2 results so set the dummy rate input_value_id */
412 /* Bug 4538139 - Since l_input_value(7) is not used anymore, code is commented out */
413 -- l_input_value(7).input_value_id := l_input_value(4).input_value_id;
414
415 hr_utility.set_location('Step '||g_package,940);
416
417 For l_index in 1..6 loop /* Bug 4538139 - Since only 6 records are used, loop is cut short to 6 */
418 -- 115.16 Set the element_type_id on direct result rules for
419 -- consistency with the form behaviour.
420 l_fres_rule_id := pay_formula_results.ins_form_res_rule (
421 p_business_group_id => p_business_group_id,
422 p_legislation_code => NULL,
423 p_effective_start_date => p_effective_date,
424 p_effective_end_date => l_end_of_time,
425 p_status_processing_rule_id => l_process_rule_id,
426 p_input_value_id => l_input_value(l_index).input_value_id,
427 p_result_name => l_input_value(l_index).result_name,
428 p_result_rule_type => l_input_value(l_index).result_rule_type,
429 p_element_type_id => l_element_id);
430 IF l_index in (2,3,4) then
431 /* As direct input value for direct results always gets set to Pay value, we need to
432 update the result rule to have the correct input value id */
433 update pay_formula_result_rules_f
434 set input_value_id = l_input_value(l_index).input_value_id
435 where formula_result_rule_id = l_fres_rule_id;
436 END IF;
437 End loop;
438 /* Now store the input value id's for the rate and date in the Accrual plan DDF */
439
440 hr_utility.set_location('Step '||g_package,950);
441
442 IF p_accrual_category = 'FR_MAIN_HOLIDAY' Then
443 hr_utility.set_location('Step '||g_package,960);
444 update pay_accrual_plans set
445 information28 = l_input_value(3).input_value_id -- index 3 holds rate
446 ,information29 = l_input_value(2).input_value_id -- index 2 holds date
447 where accrual_plan_id = p_accrual_plan_id;
448
449 ELSE
450 IF (p_accrual_category = 'FR_RTT_HOLIDAY') OR (p_accrual_category = 'FR_ADDITIONAL_HOLIDAY') Then
451 hr_utility.set_location('Step '||g_package,970);
452 update pay_accrual_plans set
453 information28 = l_input_value(3).input_value_id
454 ,information29 = l_input_value(2).input_value_id
455 where accrual_plan_id = p_accrual_plan_id;
456 END IF;
457 END IF;
458
459
460 /* create element link for the termination element */
461
462 hr_utility.set_location('Step '||g_package,980);
463 PAY_ELEMENT_LINK_API.create_element_link
464 (
465 p_effective_date => p_effective_date
466 ,p_element_type_id => l_element_id
467 ,p_business_group_id => p_business_group_id
468 ,p_costable_type => 'N'
469 ,p_link_to_all_payrolls_flag => 'Y'
470 ,p_cost_concat_segments => null
471 ,p_balance_concat_segments => null
472 ,p_element_link_id => l_element_link_id
473 ,p_comment_id => l_comment_id
474 ,p_object_version_number => l_ovn
475 ,p_effective_start_date => l_effective_start_date
476 ,p_effective_end_date => l_effective_end_date
477 );
478
479 Exception
480 when no_data_found then
481 hr_utility.trace(g_package||'.Create_Termination_element when no_data_found');
482 hr_utility.set_message(801,'HR_NO_F_TERM_RULE');
483 hr_utility.raise_error;
484
485 when others then
486 hr_utility.trace(g_package||'.Create_Termination_element when others exception');
487 hr_utility.trace(SQLCODE);
488 hr_utility.trace(SQLERRM);
489 Raise;
490
491 End Create_Termination_element;
492
493
494 begin
495
496 /* Added for GSI Bug 5472781 */
497 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
498 hr_utility.set_location('Leaving '||g_package , 10);
499 return;
500 END IF;
501
502 --
503 --hr_utility.trace_on(null,'EXTRA');
504 hr_utility.set_location('Entering '||g_package , 10);
505 IF (p_accrual_category = 'FR_RTT_HOLIDAY') OR (p_accrual_category = 'FR_ADDITIONAL_HOLIDAY')
506 OR (p_accrual_category = 'FR_MAIN_HOLIDAY') Then
507
508 --
509 -- Get value for variables
510 --
511 hr_utility.set_location('Step '||g_package,20);
512
513 open class_id;
514 fetch class_id into l_primary_class_id;
515 close class_id;
516
517 open accrual_class_id;
518 fetch accrual_class_id into l_accrual_class_id;
519 close accrual_class_id;
520
521 hr_utility.set_location('Step '||g_package,30);
522
523 begin
524 --
525 select hl.meaning
526 into l_post_term_rule
527 from hr_lookups hl
528 where hl.lookup_type='TERMINATION_RULE'
529 and hl.lookup_code='F'; -- Final Close
530
531 select name,legislation_code
532 into l_bg_name,l_leg_code
533 from per_business_groups
534 where business_group_id = p_business_group_id;
535
536
537 --
538 exception
539 --
540 when no_data_found then
541 hr_utility.set_message(801,'HR_NO_F_TERM_RULE');
542 hr_utility.raise_error;
543 --
544 --
545 end;
546 hr_utility.set_location('Step '||g_package,40);
547 for i in 1..3 loop
548 hr_utility.set_location('Step '||g_package,50);
549 --
550 if i = 1 then
551
552 hr_utility.set_location('Step '||g_package,60);
553 open csr_name_translation('FR_ENTITLEMENT');
554 fetch csr_name_translation into l_item_suffix;
555 close csr_name_translation;
556
557 hr_utility.set_location('Step '||g_package,70);
558 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
559 j := 0;
560
561 elsif i = 2 then
562
563 hr_utility.set_location('Step '||g_package,80);
564 open csr_name_translation('FR_OBSOLETE');
565 fetch csr_name_translation into l_item_suffix;
566 close csr_name_translation;
567
568 hr_utility.set_location('Step '||g_package,90);
569 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
570 j := 9;
571
572 elsif i = 3 then
573
574 hr_utility.set_location('Step '||g_package,100);
575 open csr_name_translation('FR_ADJUSTMENT');
576 fetch csr_name_translation into l_item_suffix;
577 close csr_name_translation;
578
579 hr_utility.set_location('Step '||g_package,110);
580 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
581 j := 18;
582 end if;
583 --
584
585 hr_utility.set_location('Step '||g_package,120);
586 l_element_id := hr_accrual_plan_api.create_element
587 (p_element_name => l_element_name
588 ,p_element_description => ''
589 ,p_processing_type => 'N'
590 ,p_bg_name => l_bg_name
591 ,p_classification_name => 'Information'
592 ,p_legislation_code => ''
593 ,p_currency_code => 'EUR'
594 ,p_post_termination_rule => l_post_term_rule
595 ,p_mult_entries_allowed => 'Y'
596 ,p_indirect_only_flag => 'N'
597 ,p_formula_id => NULL
598 ,p_processing_priority => NULL);
599
600 hr_utility.set_location('Step '||g_package,130);
601 open csr_name_translation('FR_ACCRUAL_PLAN');
602 fetch csr_name_translation into l_input_value_name;
603 close csr_name_translation;
604
605 hr_utility.set_location('Step '||g_package,140);
606 l_uom_code := 'N';
607
608 input_value_table(j+1) := create_input_value
609 (l_element_name
610 ,l_input_value_name
611 ,l_uom_code
612 ,l_bg_name
613 ,l_element_id
614 ,l_primary_class_id
615 ,p_business_group_id
616 ,l_leg_code
617 ,'N'
618 ,10);
619
620 hr_utility.set_location('Step '||g_package,150);
621 open csr_name_translation('FR_ACCRUAL_DATE');
622 fetch csr_name_translation into l_input_value_name;
623 close csr_name_translation;
624
625 hr_utility.set_location('Step '||g_package,160);
626 l_uom_code := 'D';
627
628 input_value_table(j+2) := create_input_value
629 (l_element_name
630 ,l_input_value_name
631 ,l_uom_code
632 ,l_bg_name
633 ,l_element_id
634 ,l_primary_class_id
635 ,p_business_group_id
636 ,l_leg_code
637 ,'N'
638 ,20);
639
640 hr_utility.set_location('Step '||g_package,170);
641 open csr_name_translation('FR_MAIN_DAY');
642 fetch csr_name_translation into l_input_value_name;
643 close csr_name_translation;
644
645 hr_utility.set_location('Step '||g_package,180);
646 l_uom_code := 'ND';
647
648 input_value_table(j+3) := create_input_value
649 (l_element_name
650 ,l_input_value_name
651 ,l_uom_code
652 ,l_bg_name
653 ,l_element_id
654 ,l_primary_class_id
655 ,p_business_group_id
656 ,l_leg_code
657 ,'N'
658 ,30);
659
660 hr_utility.set_location('Step '||g_package,190);
661 --
662 IF p_accrual_category = 'FR_MAIN_HOLIDAY' Then
663
664 hr_utility.set_location('Step '||g_package,200);
665 open csr_name_translation('FR_PROTECT_DAY');
666 fetch csr_name_translation into l_input_value_name;
667 close csr_name_translation;
668
669 hr_utility.set_location('Step '||g_package,210);
670 l_uom_code := 'ND';
671
672 input_value_table(j+4) := create_input_value
673 (l_element_name
674 ,l_input_value_name
675 ,l_uom_code
676 ,l_bg_name
677 ,l_element_id
678 ,l_primary_class_id
679 ,p_business_group_id
680 ,l_leg_code
681 ,'N'
682 ,40);
683
684 hr_utility.set_location('Step '||g_package,220);
685 open csr_name_translation('FR_CONVEN_DAY');
686 fetch csr_name_translation into l_input_value_name;
687 close csr_name_translation;
688
689 hr_utility.set_location('Step '||g_package,230);
690 l_uom_code := 'ND';
691
692 input_value_table(j+5) := create_input_value
693 (l_element_name
694 ,l_input_value_name
695 ,l_uom_code
696 ,l_bg_name
697 ,l_element_id
698 ,l_primary_class_id
699 ,p_business_group_id
700 ,l_leg_code
701 ,'N'
702 ,50);
703
704
705 hr_utility.set_location('Step '||g_package,240);
706 open csr_name_translation('FR_SENIORITY_DAY');
707 fetch csr_name_translation into l_input_value_name;
708 close csr_name_translation;
709
710 hr_utility.set_location('Step '||g_package,250);
711 l_uom_code := 'ND';
712
713 input_value_table(j+6) := create_input_value
714 (l_element_name
715 ,l_input_value_name
716 ,l_uom_code
717 ,l_bg_name
718 ,l_element_id
719 ,l_primary_class_id
720 ,p_business_group_id
721 ,l_leg_code
722 ,'N'
723 ,60);
724
725
726 hr_utility.set_location('Step '||g_package,260);
727 open csr_name_translation('FR_YMOTHER_DAY');
728 fetch csr_name_translation into l_input_value_name;
729 close csr_name_translation;
730
731 hr_utility.set_location('Step '||g_package,270);
732 l_uom_code := 'ND';
733
734 input_value_table(j+7) := create_input_value
735 (l_element_name
736 ,l_input_value_name
737 ,l_uom_code
738 ,l_bg_name
739 ,l_element_id
740 ,l_primary_class_id
741 ,p_business_group_id
742 ,l_leg_code
743 ,'N'
744 ,70);
745
746
747 hr_utility.set_location('Step '||g_package,280);
748 --
749 IF i = 1 THEN
750 hr_utility.set_location('Step '||g_package,290);
751 open csr_name_translation('FR_REF_SALARY');
752 fetch csr_name_translation into l_input_value_name;
753 close csr_name_translation;
754
755 hr_utility.set_location('Step '||g_package,300);
756 l_uom_code := 'M';
757
758 input_value_table(j+8) := create_input_value
759 (l_element_name
760 ,l_input_value_name
761 ,l_uom_code
762 ,l_bg_name
763 ,l_element_id
764 ,l_primary_class_id
765 ,p_business_group_id
766 ,l_leg_code
767 ,'N'
768 ,80);
769
770
771 hr_utility.set_location('Step '||g_package,310);
772 open csr_name_translation('FR_REF_DAY');
773 fetch csr_name_translation into l_input_value_name;
774 close csr_name_translation;
775
776 hr_utility.set_location('Step '||g_package,320);
777 l_uom_code := 'ND';
778
779 input_value_table(j+9) := create_input_value
780 (l_element_name
781 ,l_input_value_name
782 ,l_uom_code
783 ,l_bg_name
784 ,l_element_id
785 ,l_primary_class_id
786 ,p_business_group_id
787 ,l_leg_code
788 ,'N'
789 ,90);
790 hr_utility.set_location('Step '||g_package,330);
791
792 END IF;
793 --
794 hr_utility.set_location('Step '||g_package,340);
795 END IF;
796 --
797
798 for l_element_link_rec in c_absence_element_link_id
799 loop
800 --
801 l_count := l_count + 1;
802 --
803 -- Create element links for new accrual plan elements,
804 -- beginning with the plan element itself.
805 --
806 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
807 ,p_absence_link_rec => l_element_link_rec
808 ,p_legislation_code => l_leg_code);
809 --
810 end loop;
811
812
813 hr_utility.set_location('Step '||g_package,350);
814 end loop;
815 --
816 hr_utility.set_location('Step '||g_package,360);
817 open csr_name_translation('FR_WORK_DAY');
818 fetch csr_name_translation into l_input_value_name;
819 close csr_name_translation;
820
821 hr_utility.set_location('Step '||g_package,370);
822 l_uom_code := 'ND';
823
824 input_value_table(28) := create_input_value
825 (p_accrual_plan_name
826 ,l_input_value_name
827 ,l_uom_code
828 ,l_bg_name
829 ,p_accrual_plan_element_type_id
830 ,l_primary_class_id
831 ,p_business_group_id
832 ,l_leg_code
833 ,'N'
834 ,20);
835
836 hr_utility.set_location('Step '||g_package,380);
837 --
838 IF p_accrual_category = 'FR_MAIN_HOLIDAY' then
839 hr_utility.set_location('Step '||g_package,390);
840 open csr_name_translation('FR_PROTECT_DAY');
841 fetch csr_name_translation into l_input_value_name;
842 close csr_name_translation;
843
844 hr_utility.set_location('Step '||g_package,400);
845 l_uom_code := 'ND';
846
847 input_value_table(29) := create_input_value
848 (p_accrual_plan_name
849 ,l_input_value_name
850 ,l_uom_code
851 ,l_bg_name
852 ,p_accrual_plan_element_type_id
853 ,l_primary_class_id
854 ,p_business_group_id
855 ,l_leg_code
856 ,'N'
857 ,30);
858 hr_utility.set_location('Step '||g_package,410);
859 END IF;
860 --
861 --
862 IF p_accrual_category = 'FR_MAIN_HOLIDAY' Then
863 hr_utility.set_location('Step '||g_package,420);
864 update pay_accrual_plans set
865 information8 = input_value_table(3)
866 ,information9 = input_value_table(4)
867 ,information10 = input_value_table(5)
868 ,information11 = input_value_table(6)
869 ,information12 = input_value_table(7)
870 ,information13 = input_value_table(12)
871 ,information14 = input_value_table(13)
872 ,information15 = input_value_table(14)
873 ,information16 = input_value_table(15)
874 ,information17 = input_value_table(16)
875 ,information18 = input_value_table(21)
876 ,information19 = input_value_table(22)
877 ,information20 = input_value_table(23)
878 ,information21 = input_value_table(24)
879 ,information22 = input_value_table(25)
880 ,information23 = input_value_table(2)
881 ,information24 = input_value_table(11)
882 ,information25 = input_value_table(20)
883 ,information26 = input_value_table(28)
884 ,information27 = input_value_table(29)
885 where accrual_plan_id = p_accrual_plan_id;
886
887 ELSE
888 IF (p_accrual_category = 'FR_RTT_HOLIDAY') OR (p_accrual_category = 'FR_ADDITIONAL_HOLIDAY') Then
889 hr_utility.set_location('Step '||g_package,430);
890 update pay_accrual_plans set
891 information8 = input_value_table(3)
892 ,information13 = input_value_table(12)
893 ,information18 = input_value_table(21)
894 ,information23 = input_value_table(2)
895 ,information24 = input_value_table(11)
896 ,information25 = input_value_table(20)
897 ,information26 = input_value_table(28)
898 where accrual_plan_id = p_accrual_plan_id;
899 END IF;
900 hr_utility.set_location('Step '||g_package,440);
901 END IF;
902 --
903 --
904 -- Now create the extra Accounting Accrual elements
905 --
906 hr_utility.set_location('Step '||g_package,450);
907 l_uom_code := 'M';
908
909 open csr_name_translation('PAY VALUE');
910 fetch csr_name_translation into l_input_value_name;
911 close csr_name_translation;
912
913 hr_utility.set_location('Step '||g_package,460);
914 --
915 -- AMOUNT, Current Year
916 --
917 open csr_name_translation('FR_ACC_Y_AMOUNT');
918 fetch csr_name_translation into l_item_suffix;
919 close csr_name_translation;
920
921 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
922
923 hr_utility.trace('****l_element_name****'||l_element_name);
924 hr_utility.set_location('Step '||g_package,470);
925
926 l_element_id := hr_accrual_plan_api.create_element
927 (p_element_name => p_accrual_plan_name||' '||l_item_suffix
928 ,p_element_description => ''
929 ,p_processing_type => 'N'
930 ,p_bg_name => l_bg_name
931 ,p_classification_name => l_accrual_classification_name
932 ,p_legislation_code => ''
933 ,p_currency_code => 'EUR'
934 ,p_post_termination_rule => l_post_term_rule
935 ,p_mult_entries_allowed => 'Y'
936 ,p_indirect_only_flag => 'N'
937 ,p_formula_id => NULL
938 ,p_processing_priority => NULL);
939
940 hr_utility.set_location('Step '||g_package,480);
941
942 l_unused_number := create_input_value
943 (l_element_name
944 ,l_input_value_name
945 ,l_uom_code
946 ,l_bg_name
947 ,l_element_id
948 ,l_accrual_class_id
949 ,p_business_group_id
950 ,l_leg_code
951 ,'N'
952 ,10);
953
954 hr_utility.set_location('Step '||g_package,490);
955 --
956 -- Create the links for this record
957 --
958 for l_element_link_rec in c_absence_element_link_id loop
959
960 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
961 ,p_absence_link_rec => l_element_link_rec
962 ,p_legislation_code => l_leg_code);
963
964 end loop;
965 --
966 hr_utility.set_location('Step '||g_package,500);
967 --
968 -- AMOUNT, Current year minus one
969 --
970 open csr_name_translation('FR_ACC_Y1_AMOUNT');
971 fetch csr_name_translation into l_item_suffix;
972 close csr_name_translation;
973
974 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
975 hr_utility.set_location('Step '||g_package,510);
976
977 l_element_id := hr_accrual_plan_api.create_element
978 (p_element_name => l_element_name
979 ,p_element_description => ''
980 ,p_processing_type => 'N'
981 ,p_bg_name => l_bg_name
982 ,p_classification_name => l_accrual_classification_name
983 ,p_legislation_code => ''
984 ,p_currency_code => 'EUR'
985 ,p_post_termination_rule => l_post_term_rule
986 ,p_mult_entries_allowed => 'Y'
987 ,p_indirect_only_flag => 'N'
988 ,p_formula_id => NULL
989 ,p_processing_priority => NULL);
990
991 hr_utility.set_location('Step '||g_package,520);
992
993 l_unused_number := create_input_value
994 (l_element_name
995 ,l_input_value_name
996 ,l_uom_code
997 ,l_bg_name
998 ,l_element_id
999 ,l_accrual_class_id
1000 ,p_business_group_id
1001 ,l_leg_code
1002 ,'N'
1003 ,10);
1004
1005 hr_utility.set_location('Step '||g_package,530);
1006 --
1007 -- Create the links for this record
1008 --
1009 for l_element_link_rec in c_absence_element_link_id loop
1010 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
1011 ,p_absence_link_rec => l_element_link_rec
1012 ,p_legislation_code => l_leg_code);
1013 --
1014 end loop;
1015
1016 hr_utility.set_location('Step '||g_package,540);
1017 --
1018 -- AMOUNT, Current Year minus two
1019 --
1020 open csr_name_translation('FR_ACC_Y2_AMOUNT');
1021 fetch csr_name_translation into l_item_suffix;
1022 close csr_name_translation;
1023
1024 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
1025 hr_utility.set_location('Step '||g_package,550);
1026
1027 l_element_id := hr_accrual_plan_api.create_element
1028 (p_element_name => l_element_name
1029 ,p_element_description => ''
1030 ,p_processing_type => 'N'
1031 ,p_bg_name => l_bg_name
1032 ,p_classification_name => l_accrual_classification_name
1033 ,p_legislation_code => ''
1034 ,p_currency_code => 'EUR'
1035 ,p_post_termination_rule => l_post_term_rule
1036 ,p_mult_entries_allowed => 'Y'
1037 ,p_indirect_only_flag => 'N'
1038 ,p_formula_id => NULL
1039 ,p_processing_priority => NULL);
1040
1041 hr_utility.set_location('Step '||g_package,560);
1042
1043 l_unused_number := create_input_value
1044 (l_element_name
1045 ,l_input_value_name
1046 ,l_uom_code
1047 ,l_bg_name
1048 ,l_element_id
1049 ,l_accrual_class_id
1050 ,p_business_group_id
1051 ,l_leg_code
1052 ,'N'
1053 ,10);
1054
1055 hr_utility.set_location('Step '||g_package,570);
1056 --
1057 -- Create the links for this record
1058 --
1059 for l_element_link_rec in c_absence_element_link_id loop
1060 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
1061 ,p_absence_link_rec => l_element_link_rec
1062 ,p_legislation_code => l_leg_code);
1063
1064 end loop;
1065 --
1066 hr_utility.set_location('Step '||g_package,580);
1067 --
1068 -- AMOUNT, year - 3
1069 --
1070 open csr_name_translation('FR_ACC_Y3_AMOUNT');
1071 fetch csr_name_translation into l_item_suffix;
1072 close csr_name_translation;
1073
1074 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
1075 hr_utility.set_location('Step '||g_package,590);
1076
1077 l_element_id := hr_accrual_plan_api.create_element
1078 (p_element_name => l_element_name
1079 ,p_element_description => ''
1080 ,p_processing_type => 'N'
1081 ,p_bg_name => l_bg_name
1082 ,p_classification_name => l_accrual_classification_name
1083 ,p_legislation_code => ''
1084 ,p_currency_code => 'EUR'
1085 ,p_post_termination_rule => l_post_term_rule
1086 ,p_mult_entries_allowed => 'Y'
1087 ,p_indirect_only_flag => 'N'
1088 ,p_formula_id => NULL
1089 ,p_processing_priority => NULL);
1090
1091 hr_utility.set_location('Step '||g_package,600);
1092
1093 l_unused_number := create_input_value
1094 (l_element_name
1095 ,l_input_value_name
1096 ,l_uom_code
1097 ,l_bg_name
1098 ,l_element_id
1099 ,l_accrual_class_id
1100 ,p_business_group_id
1101 ,l_leg_code
1102 ,'N'
1103 ,10);
1104
1105 hr_utility.set_location('Step '||g_package,610);
1106 --
1107 -- Create the links for this record
1108 --
1109 for l_element_link_rec in c_absence_element_link_id loop
1110 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
1111 ,p_absence_link_rec => l_element_link_rec
1112 ,p_legislation_code => l_leg_code);
1113
1114 end loop;
1115 --
1116 hr_utility.set_location('Step '||g_package,620);
1117 --
1118 -- CHARGES, Current Year
1119 --
1120 open csr_name_translation('FR_ACC_Y_CHARGES');
1121 fetch csr_name_translation into l_item_suffix;
1122 close csr_name_translation;
1123
1124 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
1125 hr_utility.set_location('Step '||g_package,630);
1126
1127 l_element_id := hr_accrual_plan_api.create_element
1128 (p_element_name => l_element_name
1129 ,p_element_description => ''
1130 ,p_processing_type => 'N'
1131 ,p_bg_name => l_bg_name
1132 ,p_classification_name => l_accrual_classification_name
1133 ,p_legislation_code => ''
1134 ,p_currency_code => 'EUR'
1135 ,p_post_termination_rule => l_post_term_rule
1136 ,p_mult_entries_allowed => 'Y'
1137 ,p_indirect_only_flag => 'N'
1138 ,p_formula_id => NULL
1139 ,p_processing_priority => NULL);
1140
1141 hr_utility.set_location('Step '||g_package,640);
1142
1143 l_unused_number := create_input_value
1144 ( l_element_name
1145 ,l_input_value_name
1146 ,l_uom_code
1147 ,l_bg_name
1148 ,l_element_id
1149 ,l_accrual_class_id
1150 ,p_business_group_id
1151 ,l_leg_code
1152 ,'N'
1153 ,10);
1154
1155 hr_utility.set_location('Step '||g_package,650);
1156 --
1157 -- Create the links for this record
1158 --
1159 for l_element_link_rec in c_absence_element_link_id loop
1160 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
1161 ,p_absence_link_rec => l_element_link_rec
1162 ,p_legislation_code => l_leg_code);
1163 end loop;
1164 --
1165
1166 hr_utility.set_location('Step '||g_package,660);
1167 --
1168 -- CHARGES, Current Year minus one
1169 --
1170 open csr_name_translation('FR_ACC_Y1_CHARGES');
1171 fetch csr_name_translation into l_item_suffix;
1172 close csr_name_translation;
1173
1174 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
1175 hr_utility.set_location('Step '||g_package,670);
1176
1177 l_element_id := hr_accrual_plan_api.create_element
1178 (p_element_name => l_element_name
1179 ,p_element_description => ''
1180 ,p_processing_type => 'N'
1181 ,p_bg_name => l_bg_name
1182 ,p_classification_name => l_accrual_classification_name
1183 ,p_legislation_code => ''
1184 ,p_currency_code => 'EUR'
1185 ,p_post_termination_rule => l_post_term_rule
1186 ,p_mult_entries_allowed => 'Y'
1187 ,p_indirect_only_flag => 'N'
1188 ,p_formula_id => NULL
1189 ,p_processing_priority => NULL);
1190
1191 hr_utility.set_location('Step '||g_package,680);
1192
1193 l_unused_number := create_input_value
1194 (l_element_name
1195 ,l_input_value_name
1196 ,l_uom_code
1197 ,l_bg_name
1198 ,l_element_id
1199 ,l_accrual_class_id
1200 ,p_business_group_id
1201 ,l_leg_code
1202 ,'N'
1203 ,10);
1204
1205 hr_utility.set_location('Step '||g_package,690);
1206 --
1207 -- Create the links for this record
1208 --
1209 for l_element_link_rec in c_absence_element_link_id loop
1210 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
1211 ,p_absence_link_rec => l_element_link_rec
1212 ,p_legislation_code => l_leg_code);
1213 end loop;
1214 --
1215
1216 hr_utility.set_location('Step '||g_package,700);
1217 --
1218 -- CHARGES, Current Year minus two
1219 --
1220 open csr_name_translation('FR_ACC_Y2_CHARGES');
1221 fetch csr_name_translation into l_item_suffix;
1222 close csr_name_translation;
1223
1224 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
1225 hr_utility.set_location('Step '||g_package,710);
1226
1227 l_element_id := hr_accrual_plan_api.create_element
1228 (p_element_name => l_element_name
1229 ,p_element_description => ''
1230 ,p_processing_type => 'N'
1231 ,p_bg_name => l_bg_name
1232 ,p_classification_name => l_accrual_classification_name
1233 ,p_legislation_code => ''
1234 ,p_currency_code => 'EUR'
1235 ,p_post_termination_rule => l_post_term_rule
1236 ,p_mult_entries_allowed => 'Y'
1237 ,p_indirect_only_flag => 'N'
1238 ,p_formula_id => NULL
1239 ,p_processing_priority => NULL);
1240
1241 hr_utility.set_location('Step '||g_package,720);
1242
1243 l_unused_number := create_input_value
1244 (l_element_name
1245 ,l_input_value_name
1246 ,l_uom_code
1247 ,l_bg_name
1248 ,l_element_id
1249 ,l_accrual_class_id
1250 ,p_business_group_id
1251 ,l_leg_code
1252 ,'N'
1253 ,10);
1254
1255 hr_utility.set_location('Step '||g_package,730);
1256 --
1257 -- Create the links for this record
1258 --
1259 for l_element_link_rec in c_absence_element_link_id loop
1260 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
1261 ,p_absence_link_rec => l_element_link_rec
1262 ,p_legislation_code => l_leg_code);
1263 end loop;
1264 --
1265
1266 hr_utility.set_location('Step '||g_package,740);
1267 --
1268 -- CHARGES, Current Year minus 3
1269 --
1270 open csr_name_translation('FR_ACC_Y3_CHARGES');
1271 fetch csr_name_translation into l_item_suffix;
1272 close csr_name_translation;
1273
1274 l_element_name := p_accrual_plan_name||' '||l_item_suffix;
1275 hr_utility.set_location('Step '||g_package,750);
1276
1277 l_element_id := hr_accrual_plan_api.create_element
1278 (p_element_name => l_element_name
1279 ,p_element_description => ''
1280 ,p_processing_type => 'N'
1281 ,p_bg_name => l_bg_name
1282 ,p_classification_name => l_accrual_classification_name
1283 ,p_legislation_code => ''
1284 ,p_currency_code => 'EUR'
1285 ,p_post_termination_rule => l_post_term_rule
1286 ,p_mult_entries_allowed => 'Y'
1287 ,p_indirect_only_flag => 'N'
1288 ,p_formula_id => NULL
1289 ,p_processing_priority => NULL);
1290
1291 hr_utility.set_location('Step '||g_package,760);
1292
1293 l_unused_number := create_input_value
1294 (l_element_name
1295 ,l_input_value_name
1296 ,l_uom_code
1297 ,l_bg_name
1298 ,l_element_id
1299 ,l_accrual_class_id
1300 ,p_business_group_id
1301 ,l_leg_code
1302 ,'N'
1303 ,10);
1304
1305 hr_utility.set_location('Step '||g_package,780);
1306 --
1307 -- Create the links for this record
1308 --
1309 for l_element_link_rec in c_absence_element_link_id loop
1310 hr_accrual_plan_api.create_element_link(p_element_type_id => l_element_id
1311 ,p_absence_link_rec => l_element_link_rec
1312 ,p_legislation_code => l_leg_code);
1313 end loop;
1314 hr_utility.set_location('Step '||g_package,785);
1315 --
1316 -- get the new skip rule id, if it exists.
1317 --
1318 BEGIN
1319 open csr_skip_rule;
1320 fetch csr_skip_rule into l_new_skip_rule_id;
1321 close csr_skip_rule;
1322 EXCEPTION
1323 -- if the skip rule does not exist, continue
1324 when others then null;
1325 END;
1326 --
1327 -- Now update the main acp element to have a processing priority of 9590
1328 --
1329 hr_utility.set_location('Step '||g_package,790);
1330 begin
1331 update pay_element_types_f
1332 set processing_priority = 9590
1333 ,formula_id = nvl(l_new_skip_rule_id, formula_id)
1334 where element_name = p_accrual_plan_name
1335 and business_group_id = p_business_group_id;
1336 end;
1337 --
1338
1339 hr_utility.trace('Creating Termination element');
1340 Create_Termination_Element;
1341
1342 hr_utility.set_location('Leaving '||g_package,800);
1343 END IF;
1344
1345 END create_extra_elements;
1346 --
1347 END py_fr_additional_element_rules;