[Home] [Help]
PACKAGE BODY: APPS.HR_ACCRUAL_PLAN_API
Source
1 Package Body hr_accrual_plan_api as
2 /* $Header: hrpapapi.pkb 120.1.12020000.2 2013/01/23 09:17:21 sclakkar ship $ */
3 --
4 -- Package Variables
5 --
6 g_package CONSTANT varchar2(33) := 'hr_accrual_plan_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< create_element >------------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 -- NAME create_element
13 --
14 -- DESCRIPTION calls the function PAY_DB_PAY_SETUP.CREATE_ELEMENT,The sole
15 -- reason for this is to cut down on space and reduce the margin
16 -- for errors in the call, passing only the things that change.
17 --
18 -- NOTES anticipate the only use for this is to be called from the
19 -- pre_insert routine.
20 --
21 FUNCTION create_element(p_element_name IN varchar2,
22 p_element_description IN varchar2,
23 p_processing_type IN varchar2,
24 p_bg_name IN varchar2,
25 p_classification_name IN varchar2,
26 p_legislation_code IN varchar2,
27 p_currency_code IN varchar2,
28 p_post_termination_rule IN varchar2,
29 p_mult_entries_allowed IN varchar2,
30 p_indirect_only_flag IN varchar2,
31 p_formula_id IN number,
32 p_processing_priority IN number) return number is
33 --
34 l_effective_start_date date;
35 l_effective_end_date date;
36 l_element_type_id number;
37 l_proc varchar2(72);
38 --
39 Begin
40 --
41 l_proc := g_package||'create_element';
42 hr_utility.set_location('Entering:'||l_proc, 5);
43
44 l_effective_start_date := hr_general.start_of_time;
45 l_effective_end_date := hr_general.end_of_time;
46 --
47 l_element_type_id := PAY_DB_PAY_SETUP.create_element
48 (p_element_name => p_element_name,
49 p_description => p_element_description,
50 p_reporting_name => '',
51 p_classification_name => p_classification_name,
52 p_input_currency_code => p_currency_code,
53 p_output_currency_code => p_currency_code,
54 p_processing_type => p_processing_type,
55 p_mult_entries_allowed => p_mult_entries_allowed,
56 p_formula_id => p_formula_id,
57 p_processing_priority => p_processing_priority,
58 p_closed_for_entry_flag => 'N',
59 p_standard_link_flag => 'N',
60 p_qual_length_of_service => '',
61 p_qual_units => '',
62 p_qual_age => '',
63 p_process_in_run_flag => 'Y',
64 p_post_termination_rule => p_post_termination_rule,
65 p_indirect_only_flag => p_indirect_only_flag,
66 p_adjustment_only_flag => 'N',
67 p_add_entry_allowed_flag => 'N',
68 p_multiply_value_flag => 'N',
69 p_effective_start_date => l_effective_start_date,
70 p_effective_end_date => l_effective_end_date,
71 p_business_group_name => p_bg_name,
72 p_legislation_code => p_legislation_code,
73 p_legislation_subgroup => '');
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76
77 return l_element_type_id;
78 --
79 end create_element;
80 --
81 -- ----------------------------------------------------------------------------
82 -- |---------------------------< create_input_value >-------------------------|
83 -- ----------------------------------------------------------------------------
84 --
85 -- NAME
86 -- Create_input_value
87 --
88 -- DESCRIPTION
89 -- Performs all that is required to create an input value -
90 --
91 -- * create the input value
92 --
93 -- * validate the values
94 --
95 -- * create the DBI'd, balance feeds, etc
96 --
97 -- NOTES
98 -- Anticipate the only use for this is to be called from the
99 -- pre_insert_actions routine in this package.
100 --
101 FUNCTION create_input_value(p_element_name IN varchar2,
102 p_input_value_name IN varchar2,
103 p_uom_code IN varchar2,
104 p_bg_name IN varchar2,
105 p_element_type_id IN number,
106 p_primary_classification_id IN number,
107 p_business_group_id IN number,
108 p_recurring_flag IN varchar2,
109 p_legislation_code IN varchar2,
110 p_classification_type IN varchar2,
111 p_mandatory_flag IN varchar2)
112 RETURN number IS
113 --
114 l_effective_start_date date;
115 l_effective_end_date date;
116 l_input_value_id number;
117 l_generate_db_item_flag varchar2(5);
118 l_proc varchar2(72);
119 --
120 Begin
121 --
122 l_generate_db_item_flag := 'Y';
123 l_proc := g_package||'create_input_value';
124 --
125 hr_utility.set_location('Entering:'||l_proc, 5);
126 --
127 select least(effective_start_date),
128 greatest(effective_end_date)
129 into l_effective_start_date,
130 l_effective_end_date
131 from pay_element_types_f
132 where element_type_id = p_element_type_id;
133
134 if p_recurring_flag = 'N' and
135 (p_uom_code like 'D%' or p_uom_code = 'C') then
136 --
137 l_generate_db_item_flag := 'N';
138 --
139 end if;
140 --
141 l_input_value_id := pay_db_pay_setup.create_input_value(
142 p_element_name => p_element_name,
143 p_name => p_input_value_name,
144 p_uom => '',
145 p_uom_code => p_uom_code,
146 p_mandatory_flag => p_mandatory_flag,
147 p_generate_db_item_flag => l_generate_db_item_flag,
148 p_default_value => '',
149 p_min_value => '',
150 p_max_value => '',
151 p_warning_or_error => '',
152 p_lookup_type => '',
153 p_formula_id => '',
154 p_hot_default_flag => 'N',
155 p_display_sequence => 1,
156 p_business_group_name => p_bg_name,
157 p_effective_start_date => l_effective_start_date,
158 p_effective_end_date => l_effective_end_date);
159 --
160 hr_input_values.chk_input_value(
161 p_element_type_id => p_element_type_id,
162 p_legislation_code => p_legislation_code,
163 p_val_start_date => l_effective_start_date,
164 p_val_end_date => l_effective_end_date,
165 p_insert_update_flag => 'INSERT',
166 p_input_value_id => l_input_value_id,
167 p_rowid => '',
168 p_recurring_flag => p_recurring_flag,
169 p_mandatory_flag => 'N',
170 p_hot_default_flag => 'N',
171 p_standard_link_flag => 'N',
172 p_classification_type => p_classification_type,
173 p_name => p_input_value_name,
174 p_uom => p_uom_code,
175 p_min_value => '',
176 p_max_value => '',
177 p_default_value => '',
178 p_lookup_type => '',
179 p_formula_id => '',
180 p_generate_db_items_flag => l_generate_db_item_flag,
181 p_warning_or_error => '');
182 --
183 hr_input_values.ins_3p_input_values(
184 p_val_start_date => l_effective_start_date,
185 p_val_end_date => l_effective_end_date,
186 p_element_type_id => p_element_type_id,
187 p_primary_classification_id => p_primary_classification_id,
188 p_input_value_id => l_input_value_id,
189 p_default_value => '',
190 p_max_value => '',
191 p_min_value => '',
192 p_warning_or_error_flag => '',
193 p_input_value_name => p_input_value_name,
194 p_db_items_flag => 'Y',
195 p_costable_type => '',
196 p_hot_default_flag => 'N',
197 p_business_group_id => p_business_group_id,
198 p_legislation_code => p_legislation_code,
199 p_startup_mode => '');
200 --
201 hr_utility.set_location('Leaving:'||l_proc, 10);
202 --
203 return l_input_value_id;
204 --
205 end create_input_value;
206 --
207 -- ----------------------------------------------------------------------------
208 -- |--------------------------< create_element_link >------------------------|
209 -- ----------------------------------------------------------------------------
210 --
211 -- NAME
212 -- create_element_link
213 --
214 -- DESCRIPTION
215 -- Creates a default link for a given element, based on the link for a
216 -- plan's absence element.
217 --
218 -- NOTES
219 -- none
220 --
221 PROCEDURE create_element_link(p_element_type_id IN number,
222 p_absence_link_rec IN pay_element_links_f%rowtype,
223 p_legislation_code IN varchar2) is
224 --
225 l_proc varchar2(72);
226 l_max_end_date date;
227 l_dummy1 varchar2(100);
228 l_dummy2 number;
229 --
230 Begin
231 --
232 l_proc := g_package||'create_element_link';
233 hr_utility.set_location('Entering:'||l_proc, 5);
234
235 l_max_end_date := pay_element_links_pkg.max_end_date (
236 p_element_type_id => p_element_type_id,
237 p_element_link_id => null,
238 p_validation_start_date => p_absence_link_rec.effective_start_date,
239 p_validation_end_date => p_absence_link_rec.effective_end_date,
240 p_organization_id => p_absence_link_rec.organization_id,
241 p_people_group_id => p_absence_link_rec.people_group_id,
242 p_job_id => p_absence_link_rec.job_id,
243 p_position_id => p_absence_link_rec.position_id,
244 p_grade_id => p_absence_link_rec.grade_id,
245 p_location_id => p_absence_link_rec.location_id,
246 p_link_to_all_payrolls_flag => p_absence_link_rec.link_to_all_payrolls_flag,
247 p_payroll_id => p_absence_link_rec.payroll_id,
248 p_employment_category => p_absence_link_rec.employment_category,
249 p_pay_basis_id => p_absence_link_rec.pay_basis_id,
250 p_business_group_id => p_absence_link_rec.business_group_id
251 );
252 -- #2848964
253 l_max_end_date := least(l_max_end_date,p_absence_link_rec.effective_end_date);
254
255 pay_element_links_pkg.insert_row (
256 p_rowid => l_dummy1,
257 p_element_link_id => l_dummy2,
258 p_effective_start_date => p_absence_link_rec.effective_start_date,
259 p_effective_end_date => l_max_end_date,
260 p_payroll_id => p_absence_link_rec.payroll_id,
261 p_job_id => p_absence_link_rec.job_id,
262 p_position_id => p_absence_link_rec.position_id,
263 p_people_group_id => p_absence_link_rec.people_group_id,
264 p_cost_allocation_keyflex_id => p_absence_link_rec.cost_allocation_keyflex_id,
265 p_organization_id => p_absence_link_rec.organization_id,
266 p_element_type_id => p_element_type_id,
267 p_location_id => p_absence_link_rec.location_id,
268 p_grade_id => p_absence_link_rec.grade_id,
269 p_balancing_keyflex_id => p_absence_link_rec.balancing_keyflex_id,
270 p_business_group_id => p_absence_link_rec.business_group_id,
271 p_legislation_code => p_legislation_code,
272 p_element_set_id => p_absence_link_rec.element_set_id,
273 p_pay_basis_id => p_absence_link_rec.pay_basis_id,
274 p_costable_type => p_absence_link_rec.costable_type,
275 p_link_to_all_payrolls_flag => p_absence_link_rec.link_to_all_payrolls_flag,
276 p_multiply_value_flag => p_absence_link_rec.multiply_value_flag,
277 p_standard_link_flag => p_absence_link_rec.standard_link_flag,
278 p_transfer_to_gl_flag => p_absence_link_rec.transfer_to_gl_flag,
279 p_comment_id => p_absence_link_rec.comment_id,
280 p_employment_category => p_absence_link_rec.employment_category,
281 p_qualifying_age => p_absence_link_rec.qualifying_age,
282 p_qualifying_length_of_service => p_absence_link_rec.qualifying_length_of_service,
283 p_qualifying_units => p_absence_link_rec.qualifying_units,
284 p_attribute_category => p_absence_link_rec.attribute_category,
285 p_attribute1 => p_absence_link_rec.attribute1,
286 p_attribute2 => p_absence_link_rec.attribute2,
287 p_attribute3 => p_absence_link_rec.attribute3,
288 p_attribute4 => p_absence_link_rec.attribute4,
289 p_attribute5 => p_absence_link_rec.attribute5,
290 p_attribute6 => p_absence_link_rec.attribute6,
291 p_attribute7 => p_absence_link_rec.attribute7,
292 p_attribute8 => p_absence_link_rec.attribute8,
293 p_attribute9 => p_absence_link_rec.attribute9,
294 p_attribute10 => p_absence_link_rec.attribute10,
295 p_attribute11 => p_absence_link_rec.attribute11,
296 p_attribute12 => p_absence_link_rec.attribute12,
297 p_attribute13 => p_absence_link_rec.attribute13,
298 p_attribute14 => p_absence_link_rec.attribute14,
299 p_attribute15 => p_absence_link_rec.attribute15,
300 p_attribute16 => p_absence_link_rec.attribute16,
301 p_attribute17 => p_absence_link_rec.attribute17,
302 p_attribute18 => p_absence_link_rec.attribute18,
303 p_attribute19 => p_absence_link_rec.attribute19,
304 p_attribute20 => p_absence_link_rec.attribute20
305 );
306
307 --
308 hr_utility.set_location('Leaving:'||l_proc, 10);
309 --
310 End create_element_link;
311 --
312 -- ----------------------------------------------------------------------------
313 -- |---------------------< create_payroll_formula >----------------------------|
314 -- ----------------------------------------------------------------------------
315 --
316 procedure create_payroll_formula(
317 p_formula_id out nocopy number,
318 p_effective_start_date in date,
319 p_effective_end_date in date,
320 p_accrual_plan_name in varchar2,
321 p_defined_balance_id in number,
322 p_business_group_id in number,
323 p_legislation_code in varchar2
324 ) is
325
326 l_rowid varchar2(80);
327 l_formula_id number;
328 l_formula_type_id number;
329 l_formula_name varchar2(80);
330 l_formula_text long;
331 l_dbi_name varchar2(80);
332 l_balance_type varchar2(30);
333 l_update_date date;
334 l_leg_code varchar2(30);
335
336 cursor c_get_dbi is
337 select distinct dbi.user_name
338 from ff_database_items dbi,
339 ff_user_entities ue,
340 pay_balance_dimensions pbd,
341 pay_defined_balances dfb,
342 pay_balance_types pbt
343 where dfb.balance_dimension_id = pbd.balance_dimension_id
344 and pbd.route_id = ue.route_id
345 and ue.user_entity_id = dbi.user_entity_id
346 and pbt.balance_type_id = dfb.balance_type_id
347 and dfb.defined_balance_id = p_defined_balance_id
348 and dbi.user_name = upper(replace(pbt.balance_name, ' ', '_')||
349 pbd.database_item_suffix);
350
351 cursor get_bg_balance_type is
352 select org_information1
353 from hr_organization_information
354 where organization_id = p_business_group_id
355 and org_information_context = 'PTO Balance Type';
356
357 cursor get_leg_balance_type is
358 select rule_mode
359 from pay_legislation_rules
360 where rule_type = 'PTO_BALANCE_TYPE'
361 and legislation_code = l_leg_code;
362
363 begin
364 l_update_date := sysdate;
365
366 select formula_type_id
367 into l_formula_type_id
368 from ff_formula_types
369 where formula_type_name = 'Oracle Payroll';
370
371 l_formula_name := upper(substr(replace(p_accrual_plan_name, ' ', '_')||
372 '_Oracle_Payroll',
373 1, 80));
374
375 ff_formulas_f_pkg.insert_row(
376 X_Rowid => l_rowid,
377 X_Formula_Id => l_formula_id,
378 X_Effective_Start_Date => p_effective_start_date,
379 X_Effective_End_Date => p_effective_end_date,
380 X_Business_Group_Id => p_business_group_id,
381 X_Legislation_Code => p_legislation_code,
382 X_Formula_Type_Id => l_formula_type_id,
383 X_Formula_Name => l_formula_name,
384 X_Description => null,
385 X_Formula_Text => null,
386 X_Sticky_Flag => 'N',
387 X_Last_Update_Date => l_update_date
388 );
389
390 l_formula_text := '
391 /* -------------------------------------------------------
392 This formula is a top level formula called during the payroll
393 run to calculate accrued time for the payroll period.
394 It derives the necessary values, and then runs the appropriate
395 pto formulae.
396 It also contains an example (commented out) of one method
397 of calculating the change in employer liability for PTO.
398 Please see the on-line user documentation for more
399 information.
400 ---------------------------------------------------------*/
401
402 DEFAULT FOR PTO_ACCRUAL_PLAN_ID is 0
403 DEFAULT FOR YYYYYYYYYY is ''4712/12/31 00:00:00'' (date)
404 DEFAULT FOR ZZZZZZZZZZ is ''4712/12/31 00:00:00'' (date)
405 DEFAULT FOR XXXXXXXXXX is 0
406
407 /* The below are EXAMPLES of liability defaults. */
408 /*
409 DEFAULT FOR Hourly_Rate IS 0
410 DEFAULT FOR Current_Liability_Increment IS 0
411 DEFAULT FOR PTO_LIABILITY_BALANCE_ASG_YTD IS 0
412 */
413 /* The method of calculating the Hourly_Rate will vary depending
414 on the legislation. This is a US-specific example. */
415 /*
416 ALIAS SCL_ASG_US_WORK_SCHEDULE AS Work_Schedule
417 DEFAULT FOR Work_Schedule IS ''NOT ENTERED''
418 DEFAULT FOR ASG_HOURS IS 0
419 DEFAULT FOR ASG_SALARY IS 0
420 DEFAULT FOR ASG_SALARY_BASIS IS ''NOT ENTERED''
421 DEFAULT FOR PAY_EARNED_START_DATE IS ''0001/01/01 00:00:00'' (date)
422 DEFAULT FOR PAY_EARNED_END_DATE is ''0001/01/02 00:00:00'' (date)
423 DEFAULT FOR ASG_FREQ IS ''NOT ENTERED''
424 */
425
426 /**************************************************************
427 START OF PTO CALCULATIONS.
428 ***************************************************************/
429
430 Total_Accrued_PTO = get_net_accrual(
431 ZZZZZZZZZZ,
432 PTO_ACCRUAL_PLAN_ID,
433 YYYYYYYYYY,
434 XXXXXXXXXX )
435
436 Dummy = get_element_entry()
437
438 /**************************************************************
439 START OF LIABILITY CALCULATIONS.
440 ***************************************************************/
441
442 /* Calculate the Hourly_Rate. This is a US specific example. */
443 /*
444 Hourly_Rate = Convert_Period_Type (Work_Schedule,
445 ASG_HOURS,
446 ASG_SALARY,
447 ASG_SALARY_BASIS,
448 ''HOURLY'',
449 PAY_EARNED_START_DATE,
450 PAY_EARNED_END_DATE,
451 ASG_FREQ,
452 ''FIXED'')
453 */
454
455 /* Calculate the current liability */
456 /*
457 Current_Liability_Increment =
458 ((PTO_PLAN_BALANCE_ASG_PTO_DE_YTD + Total_Accrued_PTO)
459 * hourly_rate) - PTO_LIABILITY_BALANCE_ASG_YTD
460 */
461
462 /* If calculating liabilities, the return statement should
463 include an additional parameter:
464
465 RETURN Total_Accrued_PTO, Dummy, Current_Liability_Increment
466 */
467 /**************************************************************
468 END OF MAIN BODY.
469 ***************************************************************/
470
471 RETURN Total_Accrued_PTO, Dummy
472 ';
473
474 open c_get_dbi;
475 fetch c_get_dbi into l_dbi_name;
476
477 if c_get_dbi%found then
478 --
479 l_formula_text := replace(l_formula_text,
480 'XXXXXXXXXX',
481 l_dbi_name);
482 --
483 end if;
484
485 close c_get_dbi;
486
487 open get_bg_balance_type;
488 fetch get_bg_balance_type into l_balance_type;
489 close get_bg_balance_type;
490
491 if l_balance_type is null then
492 --
493 begin
494 --
495 select legislation_code
496 into l_leg_code
497 from per_business_groups
498 where business_group_id = p_business_group_id;
499 --
500 exception
501 when no_data_found then
502 --
503 l_leg_code := null;
504 --
505 end;
506
507 open get_leg_balance_type;
508 fetch get_leg_balance_type into l_balance_type;
509 close get_leg_balance_type;
510 --
511 end if;
512
513 if l_balance_type = 'DE' then
514 --
515 l_formula_text := replace(l_formula_text,
516 'YYYYYYYYYY',
517 'PTO_DATE_EARNED_START_DATE');
518
519 l_formula_text := replace(l_formula_text,
520 'ZZZZZZZZZZ',
521 'PTO_DATE_EARNED_CALCULATION_DATE');
522 --
523 else
524 --
525 l_formula_text := replace(l_formula_text,
526 'YYYYYYYYYY',
527 'PTO_DATE_PAID_START_DATE');
528
529 l_formula_text := replace(l_formula_text,
530 'ZZZZZZZZZZ',
531 'PTO_DATE_PAID_CALCULATION_DATE');
532 --
533 end if;
534
535 update ff_formulas_f
536 set formula_text = l_formula_text
537 where formula_id = l_formula_id;
538
539 p_formula_id := l_formula_id;
540
541 end create_payroll_formula;
542 --
543 -- ----------------------------------------------------------------------------
544 -- |---------------------< chk_balance_dimension_id >-------------------------|
545 -- ----------------------------------------------------------------------------
546 --
547 PROCEDURE chk_balance_dimension_id
548 (p_balance_dimension_id IN NUMBER
549 ,p_business_group_id IN NUMBER)
550 IS
551
552 l_proc VARCHAR2(80);
553 l_legislation_code per_business_groups.legislation_code%TYPE;
554 l_dummy NUMBER;
555
556 --
557 -- Gets the legislation code.
558 --
559 CURSOR csr_get_leg_code IS
560 SELECT pbg.legislation_code
561 FROM per_business_groups pbg
562 WHERE pbg.business_group_id = p_business_group_id;
563
564 --
565 -- Validates the balance dimension.
566 --
567 CURSOR csr_chk_bal_dim IS
568 SELECT NULL
569 FROM pay_balance_dimensions pbd
570 WHERE pbd.balance_dimension_id = p_balance_dimension_id
571 AND NVL(pbd.business_group_id, p_business_group_id) = p_business_group_id
572 AND NVL(pbd.legislation_code, NVL(l_legislation_code, hr_api.g_varchar2))
573 = NVL(l_legislation_code, hr_api.g_varchar2)
574 AND EXISTS (SELECT NULL
575 FROM ff_routes fr
576 ,ff_contexts fc
577 ,ff_route_context_usages frcu
578 WHERE fr.route_id = pbd.route_id
579 AND fc.context_name IN ('ASSIGNMENT_ACTION_ID'
580 ,'DATE_EARNED'
581 ,'TAX_UNIT_ID')
582 AND frcu.route_id = fr.route_id
583 AND frcu.context_id = fc.context_id)
584 AND NOT EXISTS (SELECT NULL
585 FROM ff_routes fr
586 ,ff_contexts fc
587 ,ff_route_context_usages frcu
588 WHERE fr.route_id = pbd.route_id
589 AND fc.context_name NOT IN ('ASSIGNMENT_ACTION_ID'
590 ,'DATE_EARNED'
591 ,'TAX_UNIT_ID')
592 AND frcu.route_id = fr.route_id
593 AND frcu.context_id = fc.context_id)
594 UNION ALL
595 SELECT NULL
596 FROM pay_balance_dimensions pbd
597 WHERE pbd.balance_dimension_id = p_balance_dimension_id
598 AND NVL(pbd.business_group_id, p_business_group_id) = p_business_group_id
599 AND NVL(pbd.legislation_code, NVL(l_legislation_code, hr_api.g_varchar2))
600 = NVL(l_legislation_code, hr_api.g_varchar2)
601 AND EXISTS (SELECT NULL
602 FROM ff_routes fr
603 ,ff_contexts fc
604 ,ff_route_context_usages frcu
605 WHERE fr.route_id = pbd.route_id
606 AND fc.context_name = 'JURISDICTION_CODE'
607 AND frcu.route_id = fr.route_id
608 AND frcu.context_id = fc.context_id);
609
610 BEGIN
611 l_proc := g_package||'chk_balance_dimension_id';
612 hr_utility.set_location('Entering: '||l_proc, 10);
613
614 OPEN csr_get_leg_code;
615 FETCH csr_get_leg_code INTO l_legislation_code;
616 CLOSE csr_get_leg_code;
617
618 hr_utility.set_location('l_legislation_code: '||l_legislation_code, 20);
619
620 OPEN csr_chk_bal_dim;
621 FETCH csr_chk_bal_dim INTO l_dummy;
622
623 IF csr_chk_bal_dim%NOTFOUND THEN
624 --
625 -- The balance dimension fails validation so error.
626 --
627 hr_utility.set_location(l_proc, 30);
628 CLOSE csr_chk_bal_dim;
629 fnd_message.set_name('PER','HR_289824_PAP_BAL_DIM_INVALID');
630 fnd_message.raise_error;
631
632 END IF;
633
634 CLOSE csr_chk_bal_dim;
635
636 hr_utility.set_location('Leaving: '||l_proc, 40);
637
638 END chk_balance_dimension_id;
639 --
640 -- ----------------------------------------------------------------------------
641 -- |---------------------< create_accrual_plan >------------------------------|
642 -- ----------------------------------------------------------------------------
643 --
644 PROCEDURE create_accrual_plan
645 (p_validate in boolean default false
646 ,p_effective_date in date
647 ,p_business_group_id in number
648 ,p_accrual_formula_id in number
649 ,p_co_formula_id in number
650 ,p_pto_input_value_id in number
651 ,p_accrual_plan_name in varchar2
652 ,p_accrual_units_of_measure in varchar2
653 ,p_accrual_category in varchar2
654 ,p_accrual_start in varchar2 default null
655 ,p_ineligible_period_length in number default null
656 ,p_ineligible_period_type in varchar2 default null
657 ,p_description in varchar2 default null
658 ,p_ineligibility_formula_id in number default null
659 ,p_balance_dimension_id in number default null
660 ,p_information_category in varchar2 default null
661 ,p_information1 in varchar2 default null
662 ,p_information2 in varchar2 default null
663 ,p_information3 in varchar2 default null
664 ,p_information4 in varchar2 default null
665 ,p_information5 in varchar2 default null
666 ,p_information6 in varchar2 default null
667 ,p_information7 in varchar2 default null
668 ,p_information8 in varchar2 default null
669 ,p_information9 in varchar2 default null
670 ,p_information10 in varchar2 default null
671 ,p_information11 in varchar2 default null
672 ,p_information12 in varchar2 default null
673 ,p_information13 in varchar2 default null
674 ,p_information14 in varchar2 default null
675 ,p_information15 in varchar2 default null
676 ,p_information16 in varchar2 default null
677 ,p_information17 in varchar2 default null
678 ,p_information18 in varchar2 default null
679 ,p_information19 in varchar2 default null
680 ,p_information20 in varchar2 default null
681 ,p_information21 in varchar2 default null
682 ,p_information22 in varchar2 default null
683 ,p_information23 in varchar2 default null
684 ,p_information24 in varchar2 default null
685 ,p_information25 in varchar2 default null
686 ,p_information26 in varchar2 default null
687 ,p_information27 in varchar2 default null
688 ,p_information28 in varchar2 default null
689 ,p_information29 in varchar2 default null
690 ,p_information30 in varchar2 default null
691 ,p_accrual_plan_id out nocopy number
692 ,p_accrual_plan_element_type_id out nocopy number
693 ,p_co_element_type_id out nocopy number
694 ,p_co_input_value_id out nocopy number
695 ,p_co_date_input_value_id out nocopy number
696 ,p_co_exp_date_input_value_id out nocopy number
697 ,p_residual_element_type_id out nocopy number
698 ,p_residual_input_value_id out nocopy number
699 ,p_residual_date_input_value_id out nocopy number
700 ,p_payroll_formula_id out nocopy number
701 ,p_defined_balance_id out nocopy number
702 ,p_balance_element_type_id out nocopy number
703 ,p_tagging_element_type_id out nocopy number
704 ,p_object_version_number out nocopy number
705 ,p_no_link_message out nocopy boolean
706 ,p_check_accrual_ff out nocopy boolean
707 ) is
708
709 --
710 -- Declare cursors and local variables
711 --
712
713 --
714 -- cursor to get a primary classification (i.e. one where the
715 -- parent_classification_id is null) for the legislation or business
716 -- group. Get the 'Information' classification if there is one,
717 -- failing that get a non-payments one, otherwise just get the
718 -- first one retrieved.
719 --
720 cursor class_name(p_legislation_code varchar2) is
721 select classification_name,
722 classification_id
723 from pay_element_classifications
724 where (business_group_id = p_business_group_id or
725 legislation_code = p_legislation_code)
726 and parent_classification_id is null
727 order by decode (classification_name, 'Information', 1, 2),
728 nvl (non_payments_flag, 'X') desc,
729 classification_name;
730
731 cursor c_class_id(p_classification_name varchar2,
732 p_legislation_code varchar2) is
733 select classification_id
734 from pay_element_classifications
735 where classification_name = p_classification_name
736 and (business_group_id = p_business_group_id or
737 legislation_code = p_legislation_code);
738
739 --
740 -- Cursor to get translated values for element and input value names
741 --
742 cursor c_get_lookups(p_lookup_code varchar2) is
743 select meaning
744 from hr_lookups
745 where lookup_type = 'NAME_TRANSLATIONS'
746 and lookup_code = p_lookup_code;
747
748 --
749 -- Cursor to retrieve details of absence element link, to be
750 -- copied into links for other elements.
751 --
752 cursor c_absence_element_link_id is
753 select *
754 from pay_element_links_f
755 where element_link_id in ( select pel.element_link_id
756 from pay_element_links_f pel,
757 pay_input_values_f piv
758 where pel.element_type_id = piv.element_type_id
759 and piv.input_value_id = p_pto_input_value_id
760 and p_effective_date between pel.effective_start_date
761 and pel.effective_end_date
762 and p_effective_date between piv.effective_start_date
763 and piv.effective_end_date );
764
765 --
766 -- Cursor to get the PTO skip rule formula ID.
767 --
768 cursor c_get_skip_rule (p_formula_name in varchar2) is
769 select ff.formula_id
770 from ff_formulas_f ff,
771 ff_formula_types ft
772 where ff.formula_type_id = ft.formula_type_id
773 and ft.formula_type_name = 'Element Skip'
774 and ff.formula_name = p_formula_name
775 and p_effective_date between ff.effective_start_date
776 and ff.effective_end_date;
777
778 --
779 -- Cursors to get balance category id - first check for
780 -- legislative specific entry, then look for global entry
781 --
782 cursor c_get_leg_bal_cat_id(p_leg_code varchar2) is
783 select balance_category_id
784 from pay_balance_categories_f
785 where category_name = 'PTO Accruals'
786 and legislation_code = p_leg_code;
787
788 cursor c_get_gbl_bal_cat_id is
789 select balance_category_id
790 from pay_balance_categories_f
791 where category_name = 'PTO Accruals'
792 and legislation_code is null;
793
794 l_proc varchar2(72);
795 l_accrual_plan_id number;
796 l_accrual_plan_element_type_id number;
797 l_input_value_id number;
798 l_co_input_value_id number;
799 l_co_date_input_value_id number;
800 l_co_exp_date_input_value_id number;
801 l_residual_input_value_id number;
802 l_residual_date_input_value_id number;
803 l_balance_input_value_id number;
804 l_balance_element_type_id number;
805 l_tagging_element_type_id number;
806 l_tagging_input_value_id number;
807 l_input_value_name varchar2(80);
808 l_date_input_value_name varchar2(80);
809 l_exp_date_input_value_name varchar2(80);
810 l_element_name varchar2(80);
811 l_element_description varchar2(240);
812 l_classification_name varchar2(240);
813 l_post_termination_rule varchar2(240);
814 l_uom_code varchar2(80);
815 l_uom_code1 varchar2(80);
816 l_primary_classification_id number;
817 l_classification_type varchar2(2);
818 l_bg_name varchar2(80);
819 l_leg_code varchar2(150);
820 l_curr_code varchar2(150);
821 l_date_uom_code varchar2(80);
822 l_co_element_type_id number;
823 l_residual_element_type_id number;
824 l_net_calc_rule_id number;
825 l_object_version_number number;
826 l_no_link_message boolean;
827 l_effective_start_date date;
828 l_effective_end_date date;
829 l_user varchar2(80);
830 l_rowid varchar2(80);
831 l_status_processing_rule_id number;
832 l_formula_result_rule_id number;
833 l_priority number;
834 l_balance_category_id number;
835 l_balance_type_id number;
836 l_defined_balance_id number;
837 l_balance_feed_id number;
838 l_payroll_formula_id number;
839 l_tagging_formula_id number;
840 l_balance_name varchar2(80);
841 l_skip_rule_formula_id number;
842 l_skip_rule_formula_name varchar2(30);
843 l_dummy_string varchar2(80);
844 l_dummy_number number;
845 l_count number := 0;
846
847 BEGIN
848 l_proc := g_package||'create_accrual_plan';
849 l_skip_rule_formula_name := 'PTO_ORACLE_SKIP_RULE';
850 --
851 hr_utility.set_location('Entering:'|| l_proc, 10);
852
853 --
854 -- Pipe the main IN / IN OUT parameters for ease of debugging.
855 --
856 hr_utility.trace(' ');
857 hr_utility.trace(' --------------------------------'||
858 '---------------------------------');
859 hr_utility.trace(' IN / IN OUT NOCOPY PARAMETER '||
860 ' VALUE');
861 hr_utility.trace(' --------------------------------'||
862 '+--------------------------------');
863 IF p_validate THEN
864 hr_utility.trace(' p_validate '||
865 'TRUE');
866 ELSE
867 hr_utility.trace(' p_validate '||
868 'FALSE');
869 END IF;
870 hr_utility.trace(' p_effective_date '||
871 to_char(p_effective_date));
872 hr_utility.trace(' p_business_group_id '||
873 to_char(p_business_group_id));
874 hr_utility.trace(' p_accrual_formula_id '||
875 to_char(p_accrual_formula_id));
876 hr_utility.trace(' p_co_formula_id '||
877 to_char(p_co_formula_id));
878 hr_utility.trace(' p_pto_input_value_id '||
879 to_char(p_pto_input_value_id));
880 hr_utility.trace(' p_accrual_plan_name '||
881 p_accrual_plan_name);
882 hr_utility.trace(' p_accrual_units_of_measure '||
883 p_accrual_units_of_measure);
884 hr_utility.trace(' p_accrual_category '||
885 p_accrual_category);
886 hr_utility.trace(' p_accrual_start '||
887 p_accrual_start);
888 hr_utility.trace(' p_ineligible_period_length '||
889 to_char(p_ineligible_period_length));
890 hr_utility.trace(' p_ineligible_period_type '||
891 p_ineligible_period_type);
892 hr_utility.trace(' p_description '||
893 p_description);
894 hr_utility.trace(' p_ineligibility_formula_id '||
895 to_char(p_ineligibility_formula_id));
896 hr_utility.trace(' p_balance_dimension_id '||
897 to_char(p_balance_dimension_id));
898 hr_utility.trace(' p_information_category '||
899 p_information_category);
900 hr_utility.trace(' --------------------------------'||
901 '---------------------------------');
902 hr_utility.trace(' ');
903
904 --
905 -- Issue a savepoint
906 --
907 savepoint create_accrual_plan;
908 hr_utility.set_location(l_proc, 20);
909
910 --
911 -- Call Before Process User Hook
912 --
913 begin
914 hr_accrual_plan_bk1.create_accrual_plan_b
915 (p_effective_date => p_effective_date
916 ,p_business_group_id => p_business_group_id
917 ,p_accrual_formula_id => p_accrual_formula_id
918 ,p_co_formula_id => p_co_formula_id
919 ,p_pto_input_value_id => p_pto_input_value_id
920 ,p_accrual_plan_name => p_accrual_plan_name
921 ,p_accrual_units_of_measure => p_accrual_units_of_measure
922 ,p_accrual_category => p_accrual_category
923 ,p_accrual_start => p_accrual_start
924 ,p_ineligible_period_length => p_ineligible_period_length
925 ,p_ineligible_period_type => p_ineligible_period_type
926 ,p_description => p_description
927 ,p_ineligibility_formula_id => p_ineligibility_formula_id
928 ,p_balance_dimension_id => p_balance_dimension_id
929 ,p_information1 => p_information1
930 ,p_information2 => p_information2
931 ,p_information3 => p_information3
932 ,p_information4 => p_information4
933 ,p_information5 => p_information5
934 ,p_information6 => p_information6
935 ,p_information7 => p_information7
936 ,p_information8 => p_information8
937 ,p_information9 => p_information9
938 ,p_information10 => p_information10
939 ,p_information11 => p_information11
940 ,p_information12 => p_information12
941 ,p_information13 => p_information13
942 ,p_information14 => p_information14
943 ,p_information15 => p_information15
944 ,p_information16 => p_information16
945 ,p_information17 => p_information17
946 ,p_information18 => p_information18
947 ,p_information19 => p_information19
948 ,p_information20 => p_information20
949 ,p_information21 => p_information21
950 ,p_information22 => p_information22
951 ,p_information23 => p_information23
952 ,p_information24 => p_information24
953 ,p_information25 => p_information25
954 ,p_information26 => p_information26
955 ,p_information27 => p_information27
956 ,p_information28 => p_information28
957 ,p_information29 => p_information29
958 ,p_information30 => p_information30
959
960 );
961 exception
962 when hr_api.cannot_find_prog_unit then
963 hr_api.cannot_find_prog_unit_error
964 (p_module_name => 'create_accrual_plan'
965 ,p_hook_type => 'BP'
966 );
967 end;
968 hr_utility.set_location(l_proc, 30);
969 --
970 -- Validation in addition to Row Handlers
971 --
972 --
973 -- Process Logic
974 --
975
976 --
977 -- The items returned by this select are used in setting up
978 -- the elements with the correct leg code and currency code.
979 --
980
981 select name, legislation_code, currency_code
982 into l_bg_name, l_leg_code, l_curr_code
983 from per_business_groups
984 where business_group_id = p_business_group_id;
985
986 hr_utility.set_location(l_proc, 40);
987 hr_utility.trace('l_bg_name: '||l_bg_name);
988 hr_utility.trace('l_leg_code: '||l_leg_code);
989 hr_utility.trace('l_curr_code: '||l_curr_code);
990 --
991 -- If this is a US legislation, use the classification name
992 -- 'PTO Accruals'. Otherwise, pick any classification, preferably a
993 -- non-payments one.
994 --
995 if l_leg_code = 'US' then
996 --
997 l_classification_name := 'PTO Accruals';
998
999 open c_class_id('PTO Accruals', l_leg_code);
1000 fetch c_class_id into l_primary_classification_id;
1001 close c_class_id;
1002 hr_utility.trace('l_primary_classification_id: '
1003 ||to_char(l_primary_classification_id));
1004 --
1005 else
1006 --
1007 open class_name(l_leg_code);
1008 fetch class_name into l_classification_name,
1009 l_primary_classification_id;
1010 close class_name;
1011 hr_utility.trace('l_classification_name: '||l_classification_name);
1012 hr_utility.trace('l_primary_classification_id: '
1013 ||to_char(l_primary_classification_id));
1014
1015 end if;
1016
1017 begin
1018
1019 hr_utility.set_location(l_proc, 50);
1020 select ec.default_priority + 1
1021 into l_priority
1022 from pay_element_classifications ec
1023 where upper(ec.classification_name) = upper(l_classification_name)
1024 and ec.parent_classification_id is NULL
1025 and ((ec.legislation_code = l_leg_code) or
1026 (ec.legislation_code is null and
1027 ec.business_group_id is not null and
1028 ec.business_group_id = p_business_group_id
1029 and not exists (select ''
1030 from pay_element_classifications ec2
1031 where upper(ec2.classification_name) = upper(l_classification_name)
1032 and ec2.parent_classification_id is NULL
1033 and ec2.legislation_code = l_leg_code)
1034 ));
1035 --
1036 exception
1037 when no_data_found then
1038 l_priority := 1500;
1039 --
1040 end;
1041
1042 hr_utility.trace('l_priority: '||to_char(l_priority));
1043 l_leg_code := null;
1044
1045 --
1046 -- Get the termination rule
1047 --
1048 begin
1049 --
1050 select hl.meaning
1051 into l_post_termination_rule
1052 from hr_lookups hl
1053 where hl.lookup_type='TERMINATION_RULE'
1054 and hl.lookup_code='F'; -- Final Close
1055 --
1056 exception
1057 --
1058 when no_data_found then
1059 hr_utility.set_message(801,'HR_NO_F_TERM_RULE');
1060 hr_utility.raise_error;
1061 --
1062 end;
1063
1064 hr_utility.trace('l_post_termination_rule: '||l_post_termination_rule);
1065 --
1066 -- Get the Skip Rule formula ID.
1067 -- No longer needed. See bug 2620850.
1068 --
1069 l_skip_rule_formula_id := null;
1070 --
1071 --open c_get_skip_rule (l_skip_rule_formula_name);
1072 --fetch c_get_skip_rule into l_skip_rule_formula_id;
1073 --
1074 -- if c_get_skip_rule%notfound then
1075 --
1076 -- The Skip Rule formula cannot be found. Raise an error.
1077 --
1078 -- fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
1079 -- fnd_message.set_token('1',l_skip_rule_formula_name);
1080 -- fnd_message.raise_error;
1081 -- end if;
1082 --
1083 --close c_get_skip_rule;
1084
1085 --
1086 -- create the accrual plan element type and input value...
1087 --
1088 l_element_name := p_accrual_plan_name;
1089 l_element_description := p_accrual_plan_name;
1090 l_classification_type := 'N';
1091
1092 hr_utility.set_location(l_proc, 50);
1093
1094 l_accrual_plan_element_type_id := create_element(
1095 l_element_name,
1096 l_element_description,
1097 'R',
1098 l_bg_name,
1099 l_classification_name,
1100 l_leg_code,
1101 l_curr_code,
1102 l_post_termination_rule,
1103 'N',
1104 'N',
1105 l_skip_rule_formula_id,
1106 null
1107 );
1108 --
1109 hr_utility.set_location(l_proc, 60);
1110 hr_utility.trace('l_accrual_plan_element_type_id: '
1111 ||to_char(l_accrual_plan_element_type_id));
1112
1113 l_input_value_name := 'Continuous Service Date';
1114 l_date_uom_code := 'D';
1115 --
1116 l_input_value_id := create_input_value(
1117 l_element_name,
1118 l_input_value_name,
1119 l_date_uom_code,
1120 l_bg_name,
1121 l_accrual_plan_element_type_id,
1122 l_primary_classification_id,
1123 p_business_group_id,
1124 'R',
1125 l_leg_code,
1126 l_classification_type,
1127 'N'
1128 );
1129
1130 hr_utility.set_location(l_proc, 70);
1131 hr_utility.trace('l_input_value_id: '||to_char(l_input_value_id));
1132 --
1133 -- set up input value names and units of measure
1134 -- for carry over and residual element input values
1135 --
1136 if p_accrual_units_of_measure = 'D' then
1137 --
1138 open c_get_lookups('PTO_DAYS');
1139 l_uom_code := 'ND';
1140 --
1141 else
1142 --
1143 open c_get_lookups('PTO_HOURS');
1144 l_uom_code := 'H_DECIMAL3';
1145 --
1146 end if;
1147
1148 fetch c_get_lookups into l_input_value_name;
1149 close c_get_lookups;
1150
1151 open c_get_lookups('PTO_EFFECTIVE_DATE');
1152 fetch c_get_lookups into l_date_input_value_name;
1153 close c_get_lookups;
1154
1155 open c_get_lookups('PTO_EXPIRY_DATE');
1156 fetch c_get_lookups into l_exp_date_input_value_name;
1157 close c_get_lookups;
1158
1159 hr_utility.set_location(l_proc, 80);
1160 hr_utility.trace('l_input_value_name: '||l_input_value_name);
1161 hr_utility.trace('l_date_input_value_name: '||l_date_input_value_name);
1162 hr_utility.trace('l_exp_date_input_value_name: '||l_exp_date_input_value_name);
1163 --
1164 -- now create the carried-over element type and input values...
1165 --
1166 open c_get_lookups('PTO_CO');
1167 fetch c_get_lookups into l_dummy_string;
1168 close c_get_lookups;
1169
1170 l_element_name := substr(p_accrual_plan_name||' '||l_dummy_string, 1, 80);
1171
1172 open c_get_lookups('PTO_CO_ELEMENT_DESC');
1173 fetch c_get_lookups into l_element_description;
1174 close c_get_lookups;
1175
1176 hr_utility.trace('l_element_name: '||l_element_name);
1177 hr_utility.trace('l_element_description: '||l_element_description);
1178 --
1179 -- If this is a US legislation, use the classification name
1180 -- 'Information'. Otherwise, stick with the classification retrieved
1181 -- above.
1182 --
1183
1184 if l_leg_code = 'US' then
1185 --
1186 l_classification_name := 'Information';
1187
1188 open c_class_id('Information', l_leg_code);
1189 fetch c_class_id into l_primary_classification_id;
1190 close c_class_id;
1191 --
1192 end if;
1193
1194 --
1195 l_classification_type := 'Y';
1196
1197 hr_utility.trace('l_classification_type: '||l_classification_type);
1198
1199 l_co_element_type_id := create_element(
1200 l_element_name,
1201 l_element_description,
1202 'N',
1203 l_bg_name,
1204 l_classification_name,
1205 l_leg_code,
1206 l_curr_code,
1207 l_post_termination_rule,
1208 'Y',
1209 'N',
1210 l_skip_rule_formula_id,
1211 null
1212 );
1213
1214 hr_utility.trace('l_co_element_type_id: '||to_char(l_co_element_type_id));
1215
1216 l_co_input_value_id := create_input_value(
1217 l_element_name,
1218 l_input_value_name,
1219 l_uom_code,
1220 l_bg_name,
1221 l_co_element_type_id,
1222 l_primary_classification_id,
1223 p_business_group_id,
1224 'N',
1225 l_leg_code,
1226 l_classification_type,
1227 'N'
1228 );
1229
1230 hr_utility.trace('l_co_input_value_id: '||to_char(l_co_input_value_id));
1231
1232 l_co_date_input_value_id := create_input_value(
1233 l_element_name,
1234 l_date_input_value_name,
1235 l_date_uom_code,
1236 l_bg_name,
1237 l_co_element_type_id,
1238 l_primary_classification_id,
1239 p_business_group_id,
1240 'N',
1241 l_leg_code,
1242 l_classification_type,
1243 'Y'
1244 );
1245
1246 hr_utility.trace('l_co_date_input_value_id: '||to_char(l_co_date_input_value_id));
1247
1248 l_co_exp_date_input_value_id := create_input_value(
1249 l_element_name,
1250 l_exp_date_input_value_name,
1251 l_date_uom_code,
1252 l_bg_name,
1253 l_co_element_type_id,
1254 l_primary_classification_id,
1255 p_business_group_id,
1256 'N',
1257 l_leg_code,
1258 l_classification_type,
1259 'Y'
1260 );
1261
1262 hr_utility.trace('l_co_exp_date_input_value_id: '
1263 ||to_char(l_co_exp_date_input_value_id));
1264
1265 --
1266 -- and finally the residual element type and input value.
1267 --
1268
1269 open c_get_lookups('PTO_RESIDUAL');
1270 fetch c_get_lookups into l_dummy_string;
1271 close c_get_lookups;
1272
1273 l_element_name := substr(p_accrual_plan_name||' '||l_dummy_string, 1, 80);
1274
1275 open c_get_lookups('PTO_RES_ELEMENT_DESC');
1276 fetch c_get_lookups into l_element_description;
1277 close c_get_lookups;
1278
1279 hr_utility.trace('l_element_name: '||l_element_name);
1280 hr_utility.trace('l_element_description: '||l_element_description);
1281
1282 l_residual_element_type_id := create_element(
1283 l_element_name,
1284 l_element_description,
1285 'N',
1286 l_bg_name,
1287 l_classification_name,
1288 l_leg_code,
1289 l_curr_code,
1290 l_post_termination_rule,
1291 'Y',
1292 'N',
1293 l_skip_rule_formula_id,
1294 null
1295 );
1296
1297 hr_utility.trace('l_residual_element_type_id: '||to_char(l_residual_element_type_id));
1298
1299 l_residual_input_value_id := create_input_value(
1300 l_element_name,
1301 l_input_value_name,
1302 l_uom_code,
1303 l_bg_name,
1304 l_residual_element_type_id,
1305 l_primary_classification_id,
1306 p_business_group_id,
1307 'N',
1308 l_leg_code,
1309 l_classification_type,
1310 'N'
1311 );
1312
1313 hr_utility.trace('l_residual_input_value_id: '||to_char(l_residual_input_value_id));
1314
1315 l_residual_date_input_value_id := create_input_value(
1316 l_element_name,
1317 l_date_input_value_name,
1318 l_date_uom_code,
1319 l_bg_name,
1320 l_residual_element_type_id,
1321 l_primary_classification_id,
1322 p_business_group_id,
1323 'N',
1324 l_leg_code,
1325 l_classification_type,
1326 'Y'
1327 );
1328
1329 hr_utility.trace('l_residual_date_input_value_id: '
1330 ||to_char(l_residual_date_input_value_id));
1331
1332 l_element_name := substr(p_accrual_plan_name||' Payroll Balance', 1, 80);
1333
1334 hr_utility.trace('l_element_name: '||l_element_name);
1335
1336 l_balance_element_type_id := create_element(
1337 l_element_name,
1338 l_element_description,
1339 'N',
1340 l_bg_name,
1341 l_classification_name,
1342 l_leg_code,
1343 l_curr_code,
1344 l_post_termination_rule,
1345 'Y',
1346 'Y',
1347 null,
1348 l_priority
1349 );
1350
1351 hr_utility.trace('l_balance_element_type_id: '||to_char(l_balance_element_type_id));
1352
1353 l_balance_input_value_id := create_input_value(
1354 l_element_name,
1355 l_input_value_name,
1356 l_uom_code,
1357 l_bg_name,
1358 l_balance_element_type_id,
1359 l_primary_classification_id,
1360 p_business_group_id,
1361 'N',
1362 l_leg_code,
1363 l_classification_type,
1364 'N'
1365 );
1366
1367 hr_utility.trace('l_balance_input_value_id: '||to_char(l_balance_input_value_id));
1368 --
1369 -- Create element type for tagging retrospecive absences
1370 --
1371
1372 select processing_priority
1373 into l_priority
1374 from pay_element_types_f
1375 where element_type_id = l_accrual_plan_element_type_id;
1376
1377 hr_utility.trace('l_priority: '||to_char(l_priority));
1378
1379 l_element_name := substr(p_accrual_plan_name||' Tagging', 1, 80);
1380
1381 l_tagging_element_type_id := create_element(
1382 l_element_name,
1383 l_element_description,
1384 'N',
1385 l_bg_name,
1386 l_classification_name,
1387 l_leg_code,
1388 l_curr_code,
1389 l_post_termination_rule,
1390 'Y',
1391 'Y',
1392 null,
1393 l_priority
1394 );
1395
1396 hr_utility.trace('l_tagging_element_type_id: '||to_char(l_tagging_element_type_id));
1397 --
1398 l_uom_code1 := 'N';
1399 l_input_value_name := 'Element Entry ID';
1400
1401 l_tagging_input_value_id := create_input_value(
1402 l_element_name,
1403 l_input_value_name,
1404 l_uom_code1,
1405 l_bg_name,
1406 l_tagging_element_type_id,
1407 l_primary_classification_id,
1408 p_business_group_id,
1409 'N',
1410 l_leg_code,
1411 l_classification_type,
1412 'N'
1413 );
1414
1415 hr_utility.trace('l_tagging_input_value_id: '||to_char(l_tagging_input_value_id));
1416
1417 --
1418 -- See if a link has been defined for the plan's absence element
1419 --
1420
1421 for l_element_link_rec in c_absence_element_link_id loop
1422 --
1423 l_count := l_count + 1;
1424 --
1425 -- Create element links for new accrual plan elements,
1426 -- beginning with the plan element itself.
1427 --
1428 hr_utility.set_location(l_proc, 90);
1429
1430 create_element_link(p_element_type_id => l_accrual_plan_element_type_id,
1431 p_absence_link_rec => l_element_link_rec,
1432 p_legislation_code => l_leg_code);
1433
1434 --
1435 -- Next, a link for the carry over element
1436 --
1437 hr_utility.set_location(l_proc, 100);
1438
1439 create_element_link(p_element_type_id => l_co_element_type_id,
1440 p_absence_link_rec => l_element_link_rec,
1441 p_legislation_code => l_leg_code);
1442
1443 --
1444 -- Next, a link for the residual element
1445 --
1446 hr_utility.set_location(l_proc, 110);
1447
1448 create_element_link(p_element_type_id => l_residual_element_type_id,
1449 p_absence_link_rec => l_element_link_rec,
1450 p_legislation_code => l_leg_code);
1451
1452 --
1453 -- A link for the balance element
1454 --
1455 hr_utility.set_location(l_proc, 120);
1456
1457 create_element_link(p_element_type_id => l_balance_element_type_id,
1458 p_absence_link_rec => l_element_link_rec,
1459 p_legislation_code => l_leg_code);
1460
1461 --
1462 -- Finally, a link for the tagging element
1463 --
1464 hr_utility.set_location(l_proc, 130);
1465
1466 create_element_link(p_element_type_id => l_tagging_element_type_id,
1467 p_absence_link_rec => l_element_link_rec,
1468 p_legislation_code => l_leg_code);
1469
1470 --
1471 end loop;
1472
1473 if l_count > 0 then
1474 --
1475 hr_utility.set_location(l_proc, 140);
1476 l_no_link_message := false;
1477 --
1478 else
1479 --
1480 --
1481 -- If no link was found, we will need to inform the user at
1482 -- a later stage
1483 --
1484 hr_utility.set_location(l_proc, 150);
1485 l_no_link_message := true;
1486 --
1487 end if;
1488
1489 if p_balance_dimension_id is not null then
1490
1491 hr_utility.set_location(l_proc, 160);
1492 --
1493 -- Ideally the balance_dimension_id should be validated in the
1494 -- row-handler but the defined_balance_id is required before
1495 -- calling the row-handler and so the validation must be
1496 -- performed here.
1497 --
1498 chk_balance_dimension_id
1499 (p_balance_dimension_id => p_balance_dimension_id
1500 ,p_business_group_id => p_business_group_id);
1501
1502 hr_utility.set_location(l_proc, 170);
1503
1504 l_user := fnd_profile.value('USER');
1505 l_effective_start_date := hr_general.start_of_time;
1506 l_effective_end_date := hr_general.end_of_time;
1507
1508 --
1509 -- Now set up the balance information. This requires rows to be inserted in
1510 -- pay_balance_feeds, pay_balace_types, and pay_defined_balances. We will
1511 -- make use of the seeded balance dimension, route etc.
1512 --
1513
1514 open c_get_leg_bal_cat_id(l_leg_code);
1515 fetch c_get_leg_bal_cat_id into l_balance_category_id;
1516 if c_get_leg_bal_cat_id%notfound then
1517 open c_get_gbl_bal_cat_id;
1518 fetch c_get_gbl_bal_cat_id into l_balance_category_id;
1519 if c_get_gbl_bal_cat_id%notfound then
1520 l_balance_category_id := null;
1521 end if;
1522 close c_get_gbl_bal_cat_id;
1523 end if;
1524 close c_get_leg_bal_cat_id;
1525
1526 l_balance_name := substr(p_accrual_plan_name||' Balance', 1, 80);
1527
1528 pay_balance_types_pkg.Insert_Row(
1529 X_Rowid => l_rowid,
1530 X_Balance_Type_Id => l_balance_type_id,
1531 X_Business_Group_Id => p_business_group_id,
1532 X_Legislation_Code => l_leg_code,
1533 X_Currency_Code => null,
1534 X_Assignment_Remuneration_Flag => 'N',
1535 X_Balance_Name => l_balance_name,
1536 X_Base_Balance_Name => l_balance_name,
1537 X_Balance_Uom => l_uom_code,
1538 X_Comments => null,
1539 X_Legislation_Subgroup => null,
1540 X_Reporting_Name => substr(l_balance_name, 1, 30),
1541 X_Attribute_Category => null,
1542 X_Attribute1 => null,
1543 X_Attribute2 => null,
1544 X_Attribute3 => null,
1545 X_Attribute4 => null,
1546 X_Attribute5 => null,
1547 X_Attribute6 => null,
1548 X_Attribute7 => null,
1549 X_Attribute8 => null,
1550 X_Attribute9 => null,
1551 X_Attribute10 => null,
1552 X_Attribute11 => null,
1553 X_Attribute12 => null,
1554 X_Attribute13 => null,
1555 X_Attribute14 => null,
1556 X_Attribute15 => null,
1557 X_Attribute16 => null,
1558 X_Attribute17 => null,
1559 X_Attribute18 => null,
1560 X_Attribute19 => null,
1561 X_Attribute20 => null,
1562 X_Balance_Category_Id => l_balance_category_id
1563 );
1564
1565 hr_utility.set_location(l_proc, 180);
1566
1567 pay_balance_feeds_f_pkg.Insert_Row(
1568 X_Rowid => l_rowid,
1569 X_Balance_Feed_Id => l_balance_feed_id,
1570 X_Effective_Start_Date => hr_general.start_of_time,
1571 X_Effective_End_Date => hr_general.end_of_time,
1572 X_Business_Group_Id => p_business_group_id,
1573 X_Legislation_Code => l_leg_code,
1574 X_Balance_Type_Id => l_balance_type_id,
1575 X_Input_Value_Id => l_balance_input_value_id,
1576 X_Scale => 1,
1577 X_Legislation_Subgroup => null
1578 );
1579
1580 hr_utility.set_location(l_proc, 190);
1581
1582 pay_defined_balances_pkg.Insert_Row(
1583 X_Rowid => l_rowid,
1584 X_Defined_Balance_Id => l_defined_balance_id,
1585 X_Business_Group_Id => p_business_group_id,
1586 X_Legislation_Code => l_leg_code,
1587 X_Balance_Type_Id => l_balance_type_id,
1588 X_Balance_Dimension_Id => p_balance_dimension_id,
1589 X_Force_Latest_Balance_Flag=> 'N',
1590 X_Legislation_Subgroup => null
1591 );
1592
1593 --
1594 -- Call procedure to create new Oracle Payroll fomrula.
1595 --
1596
1597 hr_utility.set_location(l_proc, 200);
1598
1599 create_payroll_formula(
1600 p_formula_id => l_payroll_formula_id,
1601 p_effective_start_date => l_effective_start_date,
1602 p_effective_end_date => l_effective_end_date,
1603 p_accrual_plan_name => p_accrual_plan_name,
1604 p_defined_balance_id => l_defined_balance_id,
1605 p_business_group_id => p_business_group_id,
1606 p_legislation_code => l_leg_code
1607 );
1608
1609 hr_utility.set_location(l_proc, 210);
1610 --
1611 -- Set up the status processing rules and formula result rules for the
1612 -- recurring accrual plan element.
1613 --
1614 pay_status_rules_pkg.insert_row (
1615 X_Rowid => l_rowid,
1616 X_Status_Processing_Rule_Id => l_status_processing_rule_id,
1617 X_Effective_Start_Date => l_effective_start_date,
1618 X_Effective_End_Date => l_effective_end_date,
1619 X_Business_Group_Id => p_business_group_id,
1620 X_Legislation_Code => l_leg_code,
1621 X_Element_Type_Id => l_accrual_plan_element_type_id,
1622 X_Assignment_Status_Type_Id => null,
1623 X_Formula_Id => l_payroll_formula_id,
1624 X_Processing_Rule => 'P',
1625 X_Comment_Id => null,
1626 X_Legislation_Subgroup => null,
1627 X_Last_Update_Date => sysdate,
1628 X_Last_Updated_By => l_user,
1629 X_Last_Update_Login => l_user,
1630 X_Created_By => l_user,
1631 X_Creation_Date => sysdate
1632 );
1633
1634 hr_utility.set_location(l_proc, 220);
1635
1636 pay_formula_result_rules_pkg.insert_row(
1637 p_Rowid => l_rowid,
1638 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
1639 p_Effective_Start_Date => l_effective_start_date,
1640 p_Effective_End_Date => l_effective_end_date,
1641 p_Business_Group_Id => p_business_group_id,
1642 p_Legislation_Code => l_leg_code,
1643 p_Element_Type_Id => l_balance_element_type_id,
1644 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
1645 p_Result_Name => 'TOTAL_ACCRUED_PTO',
1646 p_Result_Rule_Type => 'I',
1647 p_Legislation_Subgroup => null,
1648 p_Severity_Level => 'I',
1649 p_Input_Value_Id => l_balance_input_value_id,
1650 p_Created_By => l_user,
1651 p_session_date => sysdate
1652 );
1653
1654 l_formula_result_rule_id := null;
1655 hr_utility.set_location(l_proc, 230);
1656
1657 pay_formula_result_rules_pkg.insert_row(
1658 p_Rowid => l_rowid,
1659 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
1660 p_Effective_Start_Date => l_effective_start_date,
1661 p_Effective_End_Date => l_effective_end_date,
1662 p_Business_Group_Id => p_business_group_id,
1663 p_Legislation_Code => l_leg_code,
1664 p_Element_Type_Id => l_tagging_element_type_id,
1665 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
1666 p_Result_Name => 'DUMMY',
1667 p_Result_Rule_Type => 'I',
1668 p_Legislation_Subgroup => null,
1669 p_Severity_Level => 'I',
1670 p_Input_Value_Id => l_tagging_input_value_id,
1671 p_Created_By => l_user,
1672 p_session_date => sysdate
1673 );
1674
1675 hr_utility.set_location(l_proc, 240);
1676 --
1677 -- Set up the status processing rules and formula result rules for the
1678 -- tagging element.
1679 --
1680
1681 select formula_id
1682 into l_tagging_formula_id
1683 from ff_formulas_f ff,
1684 ff_formula_types ft
1685 where ff.formula_type_id = ft.formula_type_id
1686 and ft.formula_type_name = 'Oracle Payroll'
1687 and ff.formula_name = 'PTO_TAGGING_FORMULA';
1688
1689 l_status_processing_rule_id := null;
1690 l_formula_result_rule_id := null;
1691
1692 hr_utility.trace('l_tagging_formula_id: '||to_char(l_tagging_formula_id));
1693
1694 pay_status_rules_pkg.insert_row (
1695 X_Rowid => l_rowid,
1696 X_Status_Processing_Rule_Id => l_status_processing_rule_id,
1697 X_Effective_Start_Date => l_effective_start_date,
1698 X_Effective_End_Date => l_effective_end_date,
1699 X_Business_Group_Id => p_business_group_id,
1700 X_Legislation_Code => l_leg_code,
1701 X_Element_Type_Id => l_tagging_element_type_id,
1702 X_Assignment_Status_Type_Id => null,
1703 X_Formula_Id => l_tagging_formula_id,
1704 X_Processing_Rule => 'P',
1705 X_Comment_Id => null,
1706 X_Legislation_Subgroup => null,
1707 X_Last_Update_Date => sysdate,
1708 X_Last_Updated_By => l_user,
1709 X_Last_Update_Login => l_user,
1710 X_Created_By => l_user,
1711 X_Creation_Date => sysdate
1712 );
1713
1714 hr_utility.set_location(l_proc, 250);
1715
1716 pay_formula_result_rules_pkg.insert_row(
1717 p_Rowid => l_rowid,
1718 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
1719 p_Effective_Start_Date => l_effective_start_date,
1720 p_Effective_End_Date => l_effective_end_date,
1721 p_Business_Group_Id => p_business_group_id,
1722 p_Legislation_Code => l_leg_code,
1723 p_Element_Type_Id => l_tagging_element_type_id,
1724 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
1725 p_Result_Name => 'RETRO_ELEMENT_ENTRY_ID',
1726 p_Result_Rule_Type => 'I',
1727 p_Legislation_Subgroup => null,
1728 p_Severity_Level => 'I',
1729 p_Input_Value_Id => l_tagging_input_value_id,
1730 p_Created_By => l_user,
1731 p_session_date => sysdate
1732 );
1733
1734 hr_utility.set_location(l_proc, 260);
1735
1736 end if;
1737
1738 hr_utility.set_location(l_proc, 270);
1739 --
1740 -- Now call the insert row handler for pay accrual plans
1741 --
1742 pay_pap_ins.ins
1743 (p_effective_date => p_effective_date
1744 ,p_business_group_id => p_business_group_id
1745 ,p_accrual_plan_element_type_id => l_accrual_plan_element_type_id
1746 ,p_pto_input_value_id => p_pto_input_value_id
1747 ,p_co_input_value_id => l_co_input_value_id
1748 ,p_residual_input_value_id => l_residual_input_value_id
1749 ,p_accrual_category => p_accrual_category
1750 ,p_accrual_plan_name => p_Accrual_plan_name
1751 ,p_accrual_start => p_accrual_start
1752 ,p_accrual_units_of_measure => p_accrual_units_of_measure
1753 ,p_ineligible_period_length => p_ineligible_period_length
1754 ,p_ineligible_period_type => p_ineligible_period_type
1755 ,p_accrual_formula_id => p_accrual_formula_id
1756 ,p_co_formula_id => p_co_formula_id
1757 ,p_co_date_input_value_id => l_co_date_input_value_id
1758 ,p_co_exp_date_input_value_id => l_co_exp_date_input_value_id
1759 ,p_residual_date_input_value_id => l_residual_date_input_value_id
1760 ,p_description => p_description
1761 ,p_ineligibility_formula_id => p_ineligibility_formula_id
1762 ,p_payroll_formula_id => l_payroll_formula_id
1763 ,p_defined_balance_id => l_defined_balance_id
1764 ,p_tagging_element_type_id => l_tagging_element_type_id
1765 ,p_balance_element_type_id => l_balance_element_type_id
1766 ,p_information_category => p_information_category
1767 ,p_information1 => p_information1
1768 ,p_information2 => p_information2
1769 ,p_information3 => p_information3
1770 ,p_information4 => p_information4
1771 ,p_information5 => p_information5
1772 ,p_information6 => p_information6
1773 ,p_information7 => p_information7
1774 ,p_information8 => p_information8
1775 ,p_information9 => p_information9
1776 ,p_information10 => p_information10
1777 ,p_information11 => p_information11
1778 ,p_information12 => p_information12
1779 ,p_information13 => p_information13
1780 ,p_information14 => p_information14
1781 ,p_information15 => p_information15
1782 ,p_information16 => p_information16
1783 ,p_information17 => p_information17
1784 ,p_information18 => p_information18
1785 ,p_information19 => p_information19
1786 ,p_information20 => p_information20
1787 ,p_information21 => p_information21
1788 ,p_information22 => p_information22
1789 ,p_information23 => p_information23
1790 ,p_information24 => p_information24
1791 ,p_information25 => p_information25
1792 ,p_information26 => p_information26
1793 ,p_information27 => p_information27
1794 ,p_information28 => p_information28
1795 ,p_information29 => p_information29
1796 ,p_information30 => p_information30
1797 ,p_accrual_plan_id => l_accrual_plan_id
1798 ,p_object_version_number => l_object_version_number
1799 ,p_check_accrual_ff => p_check_accrual_ff
1800 );
1801
1802 hr_utility.set_location(l_proc, 280);
1803 --
1804 -- MLS changes
1805 hr_accrual_plan_tl.ins
1806 (p_accrual_plan_id => l_accrual_plan_id
1807 , p_accrual_plan_name => p_accrual_plan_name
1808 , p_description => p_description);
1809 -- MLS changes
1810 hr_utility.set_location(l_proc, 285);
1811 --
1812 -- Insert the two default elements of the plan into the
1813 -- net_calculation_rules table, beginning with
1814 -- the pto input value (always reduces entitlement)
1815 --
1816 pay_ncr_api.create_pay_net_calc_rule (
1817 p_net_calculation_rule_id => l_net_calc_rule_id,
1818 p_accrual_plan_id => l_accrual_plan_id,
1819 p_business_group_id => p_business_group_id,
1820 p_input_value_id => p_pto_input_value_id,
1821 p_add_or_subtract => '-1',
1822 p_date_input_value_id => null,
1823 p_object_version_number => l_dummy_number
1824 );
1825
1826 hr_utility.set_location(l_proc, 290);
1827 --
1828 -- insert the carried over input value (always increases entitlement)
1829 --
1830 pay_ncr_api.create_pay_net_calc_rule (
1831 p_net_calculation_rule_id => l_net_calc_rule_id,
1832 p_accrual_plan_id => l_accrual_plan_id,
1833 p_business_group_id => p_business_group_id,
1834 p_input_value_id => l_co_input_value_id,
1835 p_add_or_subtract => '1',
1836 p_date_input_value_id => l_co_date_input_value_id,
1837 p_object_version_number => l_dummy_number
1838 );
1839
1840 hr_utility.set_location(l_proc, 300);
1841 --
1842 -- Make PAY VALUE input value non-updateable
1843 --
1844 UPDATE pay_input_values_f
1845 SET mandatory_flag = 'X'
1846 WHERE input_value_id =
1847 ( SELECT piv.input_value_id
1848 FROM pay_input_values_f piv,
1849 pay_accrual_plans pap
1850 WHERE pap.accrual_plan_id = p_accrual_plan_id
1851 AND pap.accrual_plan_element_type_id = piv.element_type_id
1852 AND piv.name = 'Pay Value'
1853 );
1854
1855
1856 hr_utility.set_location(l_proc, 310);
1857
1858 --
1859 -- Call After Process User Hook
1860 --
1861 begin
1862 hr_accrual_plan_bk1.create_accrual_plan_a
1863 (p_effective_date => p_effective_date
1864 ,p_business_group_id => p_business_group_id
1865 ,p_accrual_formula_id => p_accrual_formula_id
1866 ,p_co_formula_id => p_co_formula_id
1867 ,p_pto_input_value_id => p_pto_input_value_id
1868 ,p_accrual_plan_name => p_accrual_plan_name
1869 ,p_accrual_units_of_measure => p_accrual_units_of_measure
1870 ,p_accrual_category => p_accrual_category
1871 ,p_accrual_start => p_accrual_start
1872 ,p_ineligible_period_length => p_ineligible_period_length
1873 ,p_ineligible_period_type => p_ineligible_period_type
1874 ,p_description => p_description
1875 ,p_ineligibility_formula_id => p_ineligibility_formula_id
1876 ,p_balance_dimension_id => p_balance_dimension_id
1877 ,p_accrual_plan_id => l_accrual_plan_id
1878 ,p_accrual_plan_element_type_id => l_accrual_plan_element_type_id
1879 ,p_co_element_type_id => l_co_element_type_id
1880 ,p_co_input_value_id => l_co_input_value_id
1881 ,p_co_date_input_value_id => l_co_date_input_value_id
1882 ,p_co_exp_date_input_value_id => l_co_exp_date_input_value_id
1883 ,p_residual_element_type_id => l_residual_element_type_id
1884 ,p_residual_input_value_id => l_residual_input_value_id
1885 ,p_residual_date_input_value_id => l_residual_date_input_value_id
1886 ,p_payroll_formula_id => l_payroll_formula_id
1887 ,p_defined_balance_id => l_defined_balance_id
1888 ,p_balance_element_type_id => l_balance_element_type_id
1889 ,p_tagging_element_type_id => l_tagging_element_type_id
1890 ,p_object_version_number => l_object_version_number
1891 ,p_no_link_message => l_no_link_message
1892 ,p_information1 => p_information1
1893 ,p_information2 => p_information2
1894 ,p_information3 => p_information3
1895 ,p_information4 => p_information4
1896 ,p_information5 => p_information5
1897 ,p_information6 => p_information6
1898 ,p_information7 => p_information7
1899 ,p_information8 => p_information8
1900 ,p_information9 => p_information9
1901 ,p_information10 => p_information10
1902 ,p_information11 => p_information11
1903 ,p_information12 => p_information12
1904 ,p_information13 => p_information13
1905 ,p_information14 => p_information14
1906 ,p_information15 => p_information15
1907 ,p_information16 => p_information16
1908 ,p_information17 => p_information17
1909 ,p_information18 => p_information18
1910 ,p_information19 => p_information19
1911 ,p_information20 => p_information20
1912 ,p_information21 => p_information21
1913 ,p_information22 => p_information22
1914 ,p_information23 => p_information23
1915 ,p_information24 => p_information24
1916 ,p_information25 => p_information25
1917 ,p_information26 => p_information26
1918 ,p_information27 => p_information27
1919 ,p_information28 => p_information28
1920 ,p_information29 => p_information29
1921 ,p_information30 => p_information30
1922 );
1923 exception
1924 when hr_api.cannot_find_prog_unit then
1925 hr_api.cannot_find_prog_unit_error
1926 (p_module_name => 'create_accrual_plan'
1927 ,p_hook_type => 'AP'
1928 );
1929 end;
1930
1931 hr_utility.set_location(l_proc, 320);
1932
1933 --
1934 -- When in validation only mode raise the Validate_Enabled exception
1935 --
1936 if p_validate then
1937 raise hr_api.validate_enabled;
1938 end if;
1939 --
1940 -- Set all output arguments
1941 --
1942 p_accrual_plan_id := l_accrual_plan_id;
1943 p_accrual_plan_element_type_id := l_accrual_plan_element_type_id;
1944 p_co_element_type_id := l_co_element_type_id;
1945 p_co_date_input_value_id := l_co_date_input_value_id;
1946 p_co_exp_date_input_value_id := l_co_exp_date_input_value_id;
1947 p_co_input_value_id := l_co_input_value_id;
1948 p_residual_element_type_id := l_residual_element_type_id;
1949 p_residual_date_input_value_id := l_residual_date_input_value_id;
1950 p_residual_input_value_id := l_residual_input_value_id;
1951 p_payroll_formula_id := l_payroll_formula_id;
1952 p_defined_balance_id := l_defined_balance_id;
1953 p_balance_element_type_id := l_balance_element_type_id;
1954 p_tagging_element_type_id := l_tagging_element_type_id;
1955 p_object_version_number := l_object_version_number;
1956 p_no_link_message := l_no_link_message;
1957
1958 --
1959 -- Pipe the main IN OUT / OUT parameters for ease of debugging.
1960 --
1961 hr_utility.trace(' ');
1962 hr_utility.trace(' --------------------------------'||
1963 '---------------------------------');
1964 hr_utility.trace(' IN OUT NOCOPY / OUT NOCOPY PARAMETER '||
1965 ' VALUE');
1966 hr_utility.trace(' --------------------------------'||
1967 '+--------------------------------');
1968 hr_utility.trace(' p_accrual_plan_id '||
1969 to_char(p_accrual_plan_id));
1970 hr_utility.trace(' p_accrual_plan_element_type_id '||
1971 to_char(p_accrual_plan_element_type_id));
1972 hr_utility.trace(' p_co_element_type_id '||
1973 to_char(p_co_element_type_id));
1974 hr_utility.trace(' p_co_input_value_id '||
1975 to_char(p_co_input_value_id));
1976 hr_utility.trace(' p_co_date_input_value_id '||
1977 to_char(p_co_date_input_value_id));
1978 hr_utility.trace(' p_co_exp_date_input_value_id '||
1979 to_char(p_co_exp_date_input_value_id));
1980 hr_utility.trace(' p_residual_element_type_id '||
1981 to_char(p_residual_element_type_id));
1982 hr_utility.trace(' p_residual_input_value_id '||
1983 to_char(p_residual_input_value_id));
1984 hr_utility.trace(' p_residual_date_input_value_id '||
1985 to_char(p_residual_date_input_value_id));
1986 hr_utility.trace(' p_payroll_formula_id '||
1987 to_char(p_payroll_formula_id));
1988 hr_utility.trace(' p_defined_balance_id '||
1989 to_char(p_defined_balance_id));
1990 hr_utility.trace(' p_balance_element_type_id '||
1991 to_char(p_balance_element_type_id));
1992 hr_utility.trace(' p_tagging_element_type_id '||
1993 to_char(p_tagging_element_type_id));
1994 hr_utility.trace(' p_object_version_number '||
1995 to_char(p_object_version_number));
1996 IF p_no_link_message THEN
1997 hr_utility.trace(' p_no_link_message '||
1998 'TRUE');
1999 ELSE
2000 hr_utility.trace(' p_no_link_message '||
2001 'FALSE');
2002 END IF;
2003 IF p_check_accrual_ff THEN
2004 hr_utility.trace(' p_check_accrual_ff '||
2005 'TRUE');
2006 ELSE
2007 hr_utility.trace(' p_check_accrual_ff '||
2008 'FALSE');
2009 END IF;
2010 hr_utility.trace(' --------------------------------'||
2011 '---------------------------------');
2012 hr_utility.trace(' ');
2013
2014 hr_utility.set_location(' Leaving:'||l_proc, 70);
2015
2016 EXCEPTION
2017
2018 WHEN hr_api.validate_enabled THEN
2019 --
2020 -- As the Validate_Enabled exception has been raised
2021 -- we must rollback to the savepoint
2022 --
2023 ROLLBACK to create_accrual_plan;
2024 --
2025 -- Only set output warning arguments
2026 -- (Any key or derived arguments must be set to null
2027 -- when validation only mode is being used.)
2028 --
2029 p_accrual_plan_id := null;
2030 p_accrual_plan_element_type_id := null;
2031 p_co_element_type_id := null;
2032 p_co_date_input_value_id := null;
2033 p_co_exp_date_input_value_id := null;
2034 p_co_input_value_id := null;
2035 p_residual_element_type_id := null;
2036 p_residual_date_input_value_id := null;
2037 p_residual_input_value_id := null;
2038 p_payroll_formula_id := null;
2039 p_defined_balance_id := null;
2040 p_balance_element_type_id := null;
2041 p_tagging_element_type_id := null;
2042 p_object_version_number := null;
2043
2044 hr_utility.set_location(' Leaving:'||l_proc, 330);
2045
2046 WHEN OTHERS THEN
2047 --
2048 -- A validation or unexpected error has occured
2049 --
2050 ROLLBACK to create_accrual_plan;
2051 -- Set OUT parameters to NULL.
2052 p_accrual_plan_id := null;
2053 p_accrual_plan_element_type_id := null;
2054 p_co_element_type_id := null;
2055 p_co_input_value_id := null;
2056 p_co_date_input_value_id := null;
2057 p_co_exp_date_input_value_id := null;
2058 p_residual_element_type_id := null;
2059 p_residual_input_value_id := null;
2060 p_residual_date_input_value_id := null;
2061 p_payroll_formula_id := null;
2062 p_defined_balance_id := null;
2063 p_balance_element_type_id := null;
2064 p_tagging_element_type_id := null;
2065 p_object_version_number := null;
2066 p_no_link_message := null;
2067 p_check_accrual_ff := null;
2068
2069 hr_utility.set_location(' Leaving:'||l_proc, 340);
2070 RAISE;
2071
2072 END create_accrual_plan;
2073 --
2074 -- ----------------------------------------------------------------------------
2075 -- |-------------------------< update_accrual_plan >--------------------------|
2076 -- ----------------------------------------------------------------------------
2077 --
2078 PROCEDURE update_accrual_plan
2079 (p_validate in boolean default false
2080 ,p_effective_date in date
2081 ,p_accrual_plan_id in number
2082 ,p_pto_input_value_id in number default hr_api.g_number
2083 ,p_accrual_category in varchar2 default hr_api.g_varchar2
2084 ,p_accrual_start in varchar2 default hr_api.g_varchar2
2085 ,p_ineligible_period_length in number default hr_api.g_number
2086 ,p_ineligible_period_type in varchar2 default hr_api.g_varchar2
2087 ,p_accrual_formula_id in number default hr_api.g_number
2088 ,p_co_formula_id in number default hr_api.g_number
2089 ,p_description in varchar2 default hr_api.g_varchar2
2090 ,p_ineligibility_formula_id in number default hr_api.g_number
2091 ,p_balance_dimension_id in number default hr_api.g_number
2092 ,p_information_category in varchar2 default hr_api.g_varchar2
2093 ,p_information1 in varchar2 default hr_api.g_varchar2
2094 ,p_information2 in varchar2 default hr_api.g_varchar2
2095 ,p_information3 in varchar2 default hr_api.g_varchar2
2096 ,p_information4 in varchar2 default hr_api.g_varchar2
2097 ,p_information5 in varchar2 default hr_api.g_varchar2
2098 ,p_information6 in varchar2 default hr_api.g_varchar2
2099 ,p_information7 in varchar2 default hr_api.g_varchar2
2100 ,p_information8 in varchar2 default hr_api.g_varchar2
2101 ,p_information9 in varchar2 default hr_api.g_varchar2
2102 ,p_information10 in varchar2 default hr_api.g_varchar2
2103 ,p_information11 in varchar2 default hr_api.g_varchar2
2104 ,p_information12 in varchar2 default hr_api.g_varchar2
2105 ,p_information13 in varchar2 default hr_api.g_varchar2
2106 ,p_information14 in varchar2 default hr_api.g_varchar2
2107 ,p_information15 in varchar2 default hr_api.g_varchar2
2108 ,p_information16 in varchar2 default hr_api.g_varchar2
2109 ,p_information17 in varchar2 default hr_api.g_varchar2
2110 ,p_information18 in varchar2 default hr_api.g_varchar2
2111 ,p_information19 in varchar2 default hr_api.g_varchar2
2112 ,p_information20 in varchar2 default hr_api.g_varchar2
2113 ,p_information21 in varchar2 default hr_api.g_varchar2
2114 ,p_information22 in varchar2 default hr_api.g_varchar2
2115 ,p_information23 in varchar2 default hr_api.g_varchar2
2116 ,p_information24 in varchar2 default hr_api.g_varchar2
2117 ,p_information25 in varchar2 default hr_api.g_varchar2
2118 ,p_information26 in varchar2 default hr_api.g_varchar2
2119 ,p_information27 in varchar2 default hr_api.g_varchar2
2120 ,p_information28 in varchar2 default hr_api.g_varchar2
2121 ,p_information29 in varchar2 default hr_api.g_varchar2
2122 ,p_information30 in varchar2 default hr_api.g_varchar2
2123 ,p_object_version_number in out nocopy number
2124 ,p_payroll_formula_id out nocopy number
2125 ,p_defined_balance_id out nocopy number
2126 ,p_balance_element_type_id out nocopy number
2127 ,p_tagging_element_type_id out nocopy number
2128 ,p_check_accrual_ff out nocopy boolean)
2129 IS
2130
2131 --
2132 -- Fetches the current accrual plan.
2133 --
2134 cursor csr_get_accrual_plan IS
2135 select *
2136 from pay_accrual_plans pap
2137 where pap.accrual_plan_id = p_accrual_plan_id;
2138
2139 --
2140 -- Declare cursors and local variables
2141 --
2142 cursor c_get_ncr(p_pto_input_value_id number) is
2143 select net_calculation_rule_id,
2144 object_version_number
2145 from pay_net_calculation_rules
2146 where accrual_plan_id = p_accrual_plan_id
2147 and input_value_id = p_pto_input_value_id;
2148
2149 cursor c_class_id(p_classification_name varchar2,
2150 p_business_group_id number,
2151 p_legislation_code varchar2) is
2152 select classification_id
2153 from pay_element_classifications
2154 where classification_name = p_classification_name
2155 and (business_group_id = p_business_group_id or
2156 legislation_code = p_legislation_code);
2157
2158 --
2159 -- Cursor to retrieve details of absence element link, to be
2160 -- copied into links for other elements.
2161 --
2162 cursor c_absence_element_link_id is
2163 select *
2164 from pay_element_links_f
2165 where element_link_id in ( select distinct pel.element_link_id
2166 from pay_element_links_f pel,
2167 pay_input_values_f piv
2168 where pel.element_type_id = piv.element_type_id
2169 and piv.input_value_id = p_pto_input_value_id);
2170
2171 --
2172 -- Cursor to get translated values for element and input value names
2173 --
2174 cursor c_get_lookups(p_lookup_code varchar2) is
2175 select meaning
2176 from hr_lookups
2177 where lookup_type = 'NAME_TRANSLATIONS'
2178 and lookup_code = p_lookup_code;
2179
2180 --
2181 -- Cursors to get balance category id - first check for
2182 -- legislative specific entry, then look for global entry
2183 --
2184 cursor c_get_leg_bal_cat_id(p_leg_code varchar2) is
2185 select balance_category_id
2186 from pay_balance_categories_f
2187 where category_name = 'PTO Accruals'
2188 and legislation_code = p_leg_code;
2189
2190 cursor c_get_gbl_bal_cat_id is
2191 select balance_category_id
2192 from pay_balance_categories_f
2193 where category_name = 'PTO Accruals'
2194 and legislation_code is null;
2195
2196 l_proc varchar2(72);
2197 l_plan_rec csr_get_accrual_plan%ROWTYPE;
2198 l_rec c_get_ncr%rowtype;
2199 l_net_calculation_rule_id number;
2200 l_object_version_number number;
2201 l_dummy number;
2202 l_effective_start_date date;
2203 l_effective_end_date date;
2204 l_user varchar2(80);
2205 l_rowid varchar2(80);
2206 l_status_processing_rule_id number;
2207 l_formula_result_rule_id number;
2208 l_priority number;
2209 l_balance_category_id number;
2210 l_balance_type_id number;
2211 l_balance_feed_id number;
2212 l_tagging_formula_id number;
2213 l_balance_input_value_id number;
2214 l_tagging_input_value_id number;
2215 l_balance_name varchar2(80);
2216 l_leg_code varchar2(30);
2217 l_uom_code varchar2(80);
2218 l_uom_code1 varchar2(80);
2219 l_element_name varchar2(80);
2220 l_input_value_name varchar2(80);
2221 l_primary_classification_id number;
2222 l_classification_type varchar2(2);
2223 l_bg_name per_business_groups.name%TYPE;
2224 l_curr_code varchar2(150);
2225 l_classification_name varchar2(240);
2226 l_post_termination_rule varchar2(240);
2227 l_temp_ovn number;
2228
2229 BEGIN
2230
2231 l_proc := g_package||'update_accrual_plan';
2232 l_temp_ovn := p_object_version_number;
2233
2234 hr_utility.set_location('Entering:'|| l_proc, 10);
2235
2236 --
2237 -- Pipe the main IN / IN OUT parameters for ease of debugging.
2238 --
2239 hr_utility.trace(' ');
2240 hr_utility.trace(' --------------------------------'||
2241 '---------------------------------');
2242 hr_utility.trace(' IN / IN OUT NOCOPY PARAMETER '||
2243 ' VALUE');
2244 hr_utility.trace(' --------------------------------'||
2245 '+--------------------------------');
2246 IF p_validate THEN
2247 hr_utility.trace(' p_validate '||
2248 'TRUE');
2249 ELSE
2250 hr_utility.trace(' p_validate '||
2251 'FALSE');
2252 END IF;
2253 hr_utility.trace(' p_effective_date '||
2254 to_char(p_effective_date));
2255 hr_utility.trace(' p_accrual_plan_id '||
2256 to_char(p_accrual_plan_id));
2257 hr_utility.trace(' p_pto_input_value_id '||
2258 to_char(p_pto_input_value_id));
2259 hr_utility.trace(' p_accrual_category '||
2260 p_accrual_category);
2261 hr_utility.trace(' p_accrual_start '||
2262 p_accrual_start);
2263 hr_utility.trace(' p_ineligible_period_length '||
2264 to_char(p_ineligible_period_length));
2265 hr_utility.trace(' p_ineligible_period_type '||
2266 p_ineligible_period_type);
2267 hr_utility.trace(' p_accrual_formula_id '||
2268 to_char(p_accrual_formula_id));
2269 hr_utility.trace(' p_co_formula_id '||
2270 to_char(p_co_formula_id));
2271 hr_utility.trace(' p_description '||
2272 p_description);
2273 hr_utility.trace(' p_ineligibility_formula_id '||
2274 to_char(p_ineligibility_formula_id));
2275 hr_utility.trace(' p_balance_dimension_id '||
2276 to_char(p_balance_dimension_id));
2277 hr_utility.trace(' p_information_category '||
2278 p_information_category);
2279 hr_utility.trace(' --------------------------------'||
2280 '---------------------------------');
2281 hr_utility.trace(' ');
2282
2283 --
2284 -- Issue a savepoint
2285 --
2286 savepoint update_accrual_plan;
2287
2288 --
2289 -- Call Before Process User Hook
2290 --
2291 begin
2292 hr_accrual_plan_bk2.update_accrual_plan_b
2293 (p_accrual_plan_id => p_accrual_plan_id
2294 ,p_pto_input_value_id => p_pto_input_value_id
2295 ,p_accrual_category => p_accrual_category
2296 ,p_accrual_start => p_accrual_start
2297 ,p_ineligible_period_length => p_ineligible_period_length
2298 ,p_ineligible_period_type => p_ineligible_period_type
2299 ,p_accrual_formula_id => p_accrual_formula_id
2300 ,p_co_formula_id => p_co_formula_id
2301 ,p_description => p_description
2302 ,p_ineligibility_formula_id => p_ineligibility_formula_id
2303 ,p_balance_dimension_id => p_balance_dimension_id
2304 ,p_object_version_number => p_object_version_number
2305 ,p_information_category => p_information_category
2306 ,p_information1 => p_information1
2307 ,p_information2 => p_information2
2308 ,p_information3 => p_information3
2309 ,p_information4 => p_information4
2310 ,p_information5 => p_information5
2311 ,p_information6 => p_information6
2312 ,p_information7 => p_information7
2313 ,p_information8 => p_information8
2314 ,p_information9 => p_information9
2315 ,p_information10 => p_information10
2316 ,p_information11 => p_information11
2317 ,p_information12 => p_information12
2318 ,p_information13 => p_information13
2319 ,p_information14 => p_information14
2320 ,p_information15 => p_information15
2321 ,p_information16 => p_information16
2322 ,p_information17 => p_information17
2323 ,p_information18 => p_information18
2324 ,p_information19 => p_information19
2325 ,p_information20 => p_information20
2326 ,p_information21 => p_information21
2327 ,p_information22 => p_information22
2328 ,p_information23 => p_information23
2329 ,p_information24 => p_information24
2330 ,p_information25 => p_information25
2331 ,p_information26 => p_information26
2332 ,p_information27 => p_information27
2333 ,p_information28 => p_information28
2334 ,p_information29 => p_information29
2335 ,p_information30 => p_information30
2336
2337 );
2338 exception
2339 when hr_api.cannot_find_prog_unit then
2340 hr_api.cannot_find_prog_unit_error
2341 (p_module_name => 'update_accrual_plan'
2342 ,p_hook_type => 'BP'
2343 );
2344 end;
2345
2346 hr_utility.set_location(l_proc, 20);
2347
2348 --
2349 -- Validation in addition to Row Handlers
2350 --
2351 -- Fetch the current accrual plan details.
2352 --
2353 OPEN csr_get_accrual_plan;
2354 FETCH csr_get_accrual_plan into l_plan_rec;
2355
2356 IF csr_get_accrual_plan%NOTFOUND THEN
2357 --
2358 -- p_accrual_plan_id does not exist so error.
2359 --
2360 hr_utility.set_location(l_proc, 30);
2361 CLOSE csr_get_accrual_plan;
2362 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2363 fnd_message.raise_error;
2364
2365 END IF;
2366
2367 CLOSE csr_get_accrual_plan;
2368
2369 hr_utility.set_location(l_proc, 40);
2370
2371 IF l_plan_rec.defined_balance_id IS NULL AND
2372 p_balance_dimension_id IS NOT NULL AND
2373 p_balance_dimension_id <> hr_api.g_number THEN
2374
2375 --
2376 -- Setup the accrual plan for payroll balances.
2377 --
2378
2379 -- The items returned by this select are used in setting up
2380 -- the elements with the correct leg code and currency code.
2381 --
2382 select name, legislation_code, currency_code
2383 into l_bg_name, l_leg_code, l_curr_code
2384 from per_business_groups
2385 where business_group_id = l_plan_rec.business_group_id;
2386
2387 hr_utility.trace('l_bg_name: '||l_bg_name);
2388 hr_utility.trace('l_leg_code: '||l_leg_code);
2389 hr_utility.trace('l_curr_code: '||l_curr_code);
2390
2391 l_classification_name := 'Information';
2392
2393 open c_class_id('Information', l_plan_rec.business_group_id, l_leg_code);
2394 fetch c_class_id into l_primary_classification_id;
2395 close c_class_id;
2396
2397 hr_utility.set_location(l_proc, 50);
2398
2399 select processing_priority + 1
2400 into l_priority
2401 from pay_element_types_f
2402 where element_type_id = l_plan_rec.accrual_plan_element_type_id;
2403
2404 hr_utility.trace('l_priority: '||to_char(l_priority));
2405
2406 l_leg_code := null;
2407
2408 --
2409 -- Get the termination rule
2410 --
2411 begin
2412 --
2413 select hl.meaning
2414 into l_post_termination_rule
2415 from hr_lookups hl
2416 where hl.lookup_type='TERMINATION_RULE'
2417 and hl.lookup_code='F'; -- Final Close
2418
2419 hr_utility.trace('l_post_termination_rule: '||l_post_termination_rule);
2420
2421 exception
2422 --
2423 when no_data_found then
2424 hr_utility.set_location(l_proc, 60);
2425 hr_utility.set_message(801,'HR_NO_F_TERM_RULE');
2426 hr_utility.raise_error;
2427 --
2428 end;
2429
2430 l_classification_type := 'Y';
2431
2432 --
2433 if l_plan_rec.accrual_units_of_measure = 'D' then
2434 --
2435 hr_utility.set_location(l_proc, 70);
2436 open c_get_lookups('PTO_DAYS');
2437 l_uom_code := 'ND';
2438 --
2439 else
2440 --
2441 hr_utility.set_location(l_proc, 80);
2442 open c_get_lookups('PTO_HOURS');
2443 l_uom_code := 'H_DECIMAL3';
2444 --
2445 end if;
2446
2447 fetch c_get_lookups into l_input_value_name;
2448 close c_get_lookups;
2449
2450 hr_utility.trace('l_input_value_name: '||l_input_value_name);
2451
2452 IF l_plan_rec.balance_element_type_id IS NULL THEN
2453 --
2454 -- This plan was created prior to the balance enhancements and
2455 -- so does not alerady have the balance element. It is now
2456 -- being updated for the first time so create the additional
2457 -- element.
2458 --
2459 l_element_name := substr(l_plan_rec.accrual_plan_name
2460 ||' Payroll Balance', 1, 80);
2461
2462 hr_utility.trace('l_element_name: '||l_element_name);
2463
2464 l_plan_rec.balance_element_type_id := create_element(
2465 l_element_name,
2466 null,
2467 'N',
2468 l_bg_name,
2469 l_classification_name,
2470 l_leg_code,
2471 l_curr_code,
2472 l_post_termination_rule,
2473 'Y',
2474 'Y',
2475 null,
2476 l_priority
2477 );
2478
2479 hr_utility.trace('l_plan_rec.balance_element_type_id: '
2480 ||to_char(l_plan_rec.balance_element_type_id));
2481
2482 l_balance_input_value_id := create_input_value(
2483 l_element_name,
2484 l_input_value_name,
2485 l_uom_code,
2486 l_bg_name,
2487 l_plan_rec.balance_element_type_id,
2488 l_primary_classification_id,
2489 l_plan_rec.business_group_id,
2490 'N',
2491 l_leg_code,
2492 l_classification_type,
2493 'N'
2494 );
2495
2496 hr_utility.trace('l_balance_input_value_id: '||to_char(l_balance_input_value_id));
2497
2498 for l_element_link_rec in c_absence_element_link_id loop
2499 --
2500 --
2501 -- Create a link for the balance element
2502 --
2503 create_element_link
2504 (p_element_type_id => l_plan_rec.balance_element_type_id,
2505 p_absence_link_rec => l_element_link_rec,
2506 p_legislation_code => l_leg_code);
2507
2508 end loop;
2509
2510 END IF;
2511
2512 hr_utility.set_location(l_proc, 90);
2513
2514 IF l_plan_rec.tagging_element_type_id IS NULL THEN
2515 --
2516 -- This plan was created prior to the balance enhancements and
2517 -- so does not alerady have the tagging element. It is now
2518 -- being updated for the first time so create the additional
2519 -- element.
2520 --
2521 select processing_priority
2522 into l_priority
2523 from pay_element_types_f
2524 where element_type_id = l_plan_rec.accrual_plan_element_type_id;
2525
2526 hr_utility.trace('l_priority: '||to_char(l_priority));
2527
2528 l_element_name := substr(l_plan_rec.accrual_plan_name||' Tagging', 1, 80);
2529
2530 hr_utility.trace('l_element_name: '||l_element_name);
2531
2532 l_plan_rec.tagging_element_type_id := create_element(
2533 l_element_name,
2534 null,
2535 'N',
2536 l_bg_name,
2537 l_classification_name,
2538 l_leg_code,
2539 l_curr_code,
2540 l_post_termination_rule,
2541 'Y',
2542 'Y',
2543 null,
2544 l_priority
2545 );
2546
2547 hr_utility.trace('l_plan_rec.tagging_element_type_id: '
2548 ||to_char(l_plan_rec.tagging_element_type_id));
2549 l_uom_code1 := 'N';
2550 l_input_value_name := 'Element Entry ID';
2551
2552 l_tagging_input_value_id := create_input_value(
2553 l_element_name,
2554 l_input_value_name,
2555 l_uom_code1,
2556 l_bg_name,
2557 l_plan_rec.tagging_element_type_id,
2558 l_primary_classification_id,
2559 l_plan_rec.business_group_id,
2560 'N',
2561 l_leg_code,
2562 l_classification_type,
2563 'N'
2564 );
2565
2566 hr_utility.trace('l_tagging_input_value_id: '||to_char(l_tagging_input_value_id));
2567
2568 for l_element_link_rec in c_absence_element_link_id loop
2569 --
2570 -- Create a link for the tagging element
2571 --
2572 create_element_link
2573 (p_element_type_id => l_plan_rec.tagging_element_type_id,
2574 p_absence_link_rec => l_element_link_rec,
2575 p_legislation_code => l_leg_code);
2576
2577 end loop;
2578
2579 END IF;
2580
2581 hr_utility.set_location(l_proc, 100);
2582
2583 l_user := fnd_profile.value('USER');
2584 l_effective_start_date := hr_general.start_of_time;
2585 l_effective_end_date := hr_general.end_of_time;
2586
2587 hr_utility.trace('l_user: '||l_user);
2588 --
2589 -- Now set up the balance information. This requires rows to be inserted in
2590 -- pay_balance_feeds, pay_balace_types, and pay_defined_balances. We will
2591 -- make use of the seeded balance dimension, route etc.
2592 --
2593 l_leg_code := null;
2594 l_balance_name := substr(l_plan_rec.accrual_plan_name||' Balance', 1, 80);
2595
2596 open c_get_leg_bal_cat_id(l_leg_code);
2597 fetch c_get_leg_bal_cat_id into l_balance_category_id;
2598 if c_get_leg_bal_cat_id%notfound then
2599 open c_get_gbl_bal_cat_id;
2600 fetch c_get_gbl_bal_cat_id into l_balance_category_id;
2601 if c_get_gbl_bal_cat_id%notfound then
2602 l_balance_category_id := null;
2603 end if;
2604 close c_get_gbl_bal_cat_id;
2605 end if;
2606 close c_get_leg_bal_cat_id;
2607
2608 pay_balance_types_pkg.Insert_Row(
2609 X_Rowid => l_rowid,
2610 X_Balance_Type_Id => l_balance_type_id,
2611 X_Business_Group_Id => l_plan_rec.business_group_id,
2612 X_Legislation_Code => l_leg_code,
2613 X_Currency_Code => null,
2614 X_Assignment_Remuneration_Flag => 'N',
2615 X_Balance_Name => l_balance_name,
2616 X_Base_Balance_Name => l_balance_name,
2617 X_Balance_Uom => l_uom_code,
2618 X_Comments => null,
2619 X_Legislation_Subgroup => null,
2620 X_Reporting_Name => substr(l_balance_name, 1, 30),
2621 X_Attribute_Category => null,
2622 X_Attribute1 => null,
2623 X_Attribute2 => null,
2624 X_Attribute3 => null,
2625 X_Attribute4 => null,
2626 X_Attribute5 => null,
2627 X_Attribute6 => null,
2628 X_Attribute7 => null,
2629 X_Attribute8 => null,
2630 X_Attribute9 => null,
2631 X_Attribute10 => null,
2632 X_Attribute11 => null,
2633 X_Attribute12 => null,
2634 X_Attribute13 => null,
2635 X_Attribute14 => null,
2636 X_Attribute15 => null,
2637 X_Attribute16 => null,
2638 X_Attribute17 => null,
2639 X_Attribute18 => null,
2640 X_Attribute19 => null,
2641 X_Attribute20 => null,
2642 X_Balance_Category_Id => l_balance_category_id
2643 );
2644
2645 hr_utility.set_location(l_proc, 100);
2646
2647 select input_value_id
2648 into l_balance_input_value_id
2649 from pay_input_values_f
2650 where element_type_id = l_plan_rec.balance_element_type_id
2651 and uom = l_uom_code;
2652
2653 hr_utility.trace('l_balance_input_value_id: '||to_char(l_balance_input_value_id));
2654
2655 pay_balance_feeds_f_pkg.Insert_Row(
2656 X_Rowid => l_rowid,
2657 X_Balance_Feed_Id => l_balance_feed_id,
2658 X_Effective_Start_Date => hr_general.start_of_time,
2659 X_Effective_End_Date => hr_general.end_of_time,
2660 X_Business_Group_Id => l_plan_rec.business_group_id,
2661 X_Legislation_Code => l_leg_code,
2662 X_Balance_Type_Id => l_balance_type_id,
2663 X_Input_Value_Id => l_balance_input_value_id,
2664 X_Scale => 1,
2665 X_Legislation_Subgroup => null
2666 );
2667
2668 hr_utility.set_location(l_proc, 110);
2669
2670 pay_defined_balances_pkg.Insert_Row(
2671 X_Rowid => l_rowid,
2672 X_Defined_Balance_Id => l_plan_rec.defined_balance_id,
2673 X_Business_Group_Id => l_plan_rec.business_group_id,
2674 X_Legislation_Code => l_leg_code,
2675 X_Balance_Type_Id => l_balance_type_id,
2676 X_Balance_Dimension_Id => p_balance_dimension_id,
2677 X_Force_Latest_Balance_Flag=> 'N',
2678 X_Legislation_Subgroup => null
2679 );
2680
2681 hr_utility.set_location(l_proc, 120);
2682
2683 --
2684 -- Call procedure to create new Oracle Payroll fomrula.
2685 --
2686 create_payroll_formula(
2687 p_formula_id => l_plan_rec.payroll_formula_id,
2688 p_effective_start_date => l_effective_start_date,
2689 p_effective_end_date => l_effective_end_date,
2690 p_accrual_plan_name => l_plan_rec.accrual_plan_name,
2691 p_defined_balance_id => l_plan_rec.defined_balance_id,
2692 p_business_group_id => l_plan_rec.business_group_id,
2693 p_legislation_code => l_leg_code
2694 );
2695
2696 hr_utility.set_location(l_proc, 130);
2697
2698 --
2699 -- Set up the status processing rules and formula result rules for the
2700 -- recurring accrual plan element.
2701 --
2702 pay_status_rules_pkg.insert_row
2703 (X_Rowid => l_rowid,
2704 X_Status_Processing_Rule_Id => l_status_processing_rule_id,
2705 X_Effective_Start_Date => l_effective_start_date,
2706 X_Effective_End_Date => l_effective_end_date,
2707 X_Business_Group_Id => l_plan_rec.business_group_id,
2708 X_Legislation_Code => l_leg_code,
2709 X_Element_Type_Id => l_plan_rec.accrual_plan_element_type_id,
2710 X_Assignment_Status_Type_Id => null,
2711 X_Formula_Id => l_plan_rec.payroll_formula_id,
2712 X_Processing_Rule => 'P',
2713 X_Comment_Id => null,
2714 X_Legislation_Subgroup => null,
2715 X_Last_Update_Date => sysdate,
2716 X_Last_Updated_By => l_user,
2717 X_Last_Update_Login => l_user,
2718 X_Created_By => l_user,
2719 X_Creation_Date => sysdate
2720 );
2721
2722 hr_utility.set_location(l_proc, 140);
2723
2724 pay_formula_result_rules_pkg.insert_row
2725 (p_Rowid => l_rowid,
2726 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
2727 p_Effective_Start_Date => l_effective_start_date,
2728 p_Effective_End_Date => l_effective_end_date,
2729 p_Business_Group_Id => l_plan_rec.business_group_id,
2730 p_Legislation_Code => l_leg_code,
2731 p_Element_Type_Id => l_plan_rec.balance_element_type_id,
2732 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
2733 p_Result_Name => 'TOTAL_ACCRUED_PTO',
2734 p_Result_Rule_Type => 'I',
2735 p_Legislation_Subgroup => null,
2736 p_Severity_Level => 'I',
2737 p_Input_Value_Id => l_balance_input_value_id,
2738 p_Created_By => l_user,
2739 p_session_date => sysdate
2740 );
2741
2742 l_formula_result_rule_id := null;
2743
2744 select input_value_id
2745 into l_tagging_input_value_id
2746 from pay_input_values_f
2747 where element_type_id = l_plan_rec.tagging_element_type_id
2748 and uom = 'N';
2749
2750 hr_utility.trace('l_tagging_input_value_id: '||to_char(l_tagging_input_value_id));
2751
2752 pay_formula_result_rules_pkg.insert_row
2753 (p_Rowid => l_rowid,
2754 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
2755 p_Effective_Start_Date => l_effective_start_date,
2756 p_Effective_End_Date => l_effective_end_date,
2757 p_Business_Group_Id => l_plan_rec.business_group_id,
2758 p_Legislation_Code => l_leg_code,
2759 p_Element_Type_Id => l_plan_rec.tagging_element_type_id,
2760 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
2761 p_Result_Name => 'DUMMY',
2762 p_Result_Rule_Type => 'I',
2763 p_Legislation_Subgroup => null,
2764 p_Severity_Level => 'I',
2765 p_Input_Value_Id => l_tagging_input_value_id,
2766 p_Created_By => l_user,
2767 p_session_date => sysdate
2768 );
2769
2770 --
2771 -- Set up the status processing rules and formula result rules for the
2772 -- tagging element.
2773 --
2774
2775 select formula_id
2776 into l_tagging_formula_id
2777 from ff_formulas_f ff,
2778 ff_formula_types ft
2779 where ff.formula_type_id = ft.formula_type_id
2780 and ft.formula_type_name = 'Oracle Payroll'
2781 and ff.formula_name = 'PTO_TAGGING_FORMULA';
2782
2783 hr_utility.trace('l_tagging_formula_id: '||to_char(l_tagging_formula_id));
2784
2785 l_status_processing_rule_id := null;
2786 l_formula_result_rule_id := null;
2787
2788 pay_status_rules_pkg.insert_row
2789 (X_Rowid => l_rowid,
2790 X_Status_Processing_Rule_Id => l_status_processing_rule_id,
2791 X_Effective_Start_Date => l_effective_start_date,
2792 X_Effective_End_Date => l_effective_end_date,
2793 X_Business_Group_Id => l_plan_rec.business_group_id,
2794 X_Legislation_Code => l_leg_code,
2795 X_Element_Type_Id => l_plan_rec.tagging_element_type_id,
2796 X_Assignment_Status_Type_Id => null,
2797 X_Formula_Id => l_tagging_formula_id,
2798 X_Processing_Rule => 'P',
2799 X_Comment_Id => null,
2800 X_Legislation_Subgroup => null,
2801 X_Last_Update_Date => sysdate,
2802 X_Last_Updated_By => l_user,
2803 X_Last_Update_Login => l_user,
2804 X_Created_By => l_user,
2805 X_Creation_Date => sysdate
2806 );
2807
2808 hr_utility.set_location(l_proc, 150);
2809
2810 pay_formula_result_rules_pkg.insert_row
2811 (p_Rowid => l_rowid,
2812 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
2813 p_Effective_Start_Date => l_effective_start_date,
2814 p_Effective_End_Date => l_effective_end_date,
2815 p_Business_Group_Id => l_plan_rec.business_group_id,
2816 p_Legislation_Code => l_leg_code,
2817 p_Element_Type_Id => l_plan_rec.tagging_element_type_id,
2818 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
2819 p_Result_Name => 'RETRO_ELEMENT_ENTRY_ID',
2820 p_Result_Rule_Type => 'I',
2821 p_Legislation_Subgroup => null,
2822 p_Severity_Level => 'I',
2823 p_Input_Value_Id => l_tagging_input_value_id,
2824 p_Created_By => l_user,
2825 p_session_date => sysdate
2826 );
2827
2828 hr_utility.set_location(l_proc, 160);
2829
2830 end if;
2831
2832 l_object_version_number := p_object_version_number;
2833
2834 hr_utility.set_location(l_proc, 170);
2835
2836 pay_pap_upd.upd
2837 (p_effective_date => p_effective_date,
2838 p_accrual_plan_id => p_accrual_plan_id,
2839 p_pto_input_value_id => p_pto_input_value_id,
2840 p_accrual_category => p_accrual_category,
2841 p_accrual_start => p_accrual_start,
2842 p_ineligible_period_length => p_ineligible_period_length,
2843 p_ineligible_period_type => p_ineligible_period_type,
2844 p_accrual_formula_id => p_accrual_formula_id,
2845 p_co_formula_id => p_co_formula_id,
2846 p_description => p_description,
2847 p_ineligibility_formula_id => p_ineligibility_formula_id,
2848 p_payroll_formula_id => l_plan_rec.payroll_formula_id,
2849 p_defined_balance_id => l_plan_rec.defined_balance_id,
2850 p_tagging_element_type_id => l_plan_rec.tagging_element_type_id,
2851 p_balance_element_type_id => l_plan_rec.balance_element_type_id,
2852 p_information_category => p_information_category,
2853 p_information1 => p_information1,
2854 p_information2 => p_information2,
2855 p_information3 => p_information3,
2856 p_information4 => p_information4,
2857 p_information5 => p_information5,
2858 p_information6 => p_information6,
2859 p_information7 => p_information7,
2860 p_information8 => p_information8,
2861 p_information9 => p_information9,
2862 p_information10 => p_information10,
2863 p_information11 => p_information11,
2864 p_information12 => p_information12,
2865 p_information13 => p_information13,
2866 p_information14 => p_information14,
2867 p_information15 => p_information15,
2868 p_information16 => p_information16,
2869 p_information17 => p_information17,
2870 p_information18 => p_information18,
2871 p_information19 => p_information19,
2872 p_information20 => p_information20,
2873 p_information21 => p_information21,
2874 p_information22 => p_information22,
2875 p_information23 => p_information23,
2876 p_information24 => p_information24,
2877 p_information25 => p_information25,
2878 p_information26 => p_information26,
2879 p_information27 => p_information27,
2880 p_information28 => p_information28,
2881 p_information29 => p_information29,
2882 p_information30 => p_information30,
2883 p_object_version_number => l_object_version_number,
2884 p_check_accrual_ff => p_check_accrual_ff
2885 );
2886
2887 hr_utility.set_location(l_proc, 180);
2888 --
2889 -- MLS changes
2890 hr_accrual_plan_tl.upd
2891 (p_accrual_plan_id => p_accrual_plan_id
2892 , p_description => p_description);
2893 -- MLS changes
2894 hr_utility.set_location(l_proc, 185);
2895 --
2896
2897 if p_pto_input_value_id <> hr_api.g_number
2898 and p_pto_input_value_id <> pay_pap_shd.g_old_rec.pto_input_value_id then
2899 --
2900 for l_ncr_rec in c_get_ncr(pay_pap_shd.g_old_rec.pto_input_value_id) loop
2901 --
2902 pay_ncr_api.delete_pay_net_calc_rule
2903 (p_net_calculation_rule_id => l_ncr_rec.net_calculation_rule_id,
2904 p_object_version_number => l_ncr_rec.object_version_number
2905 );
2906 --
2907 end loop;
2908
2909 --
2910 -- create a new net calculation rule for the new pto input value if one
2911 -- does not already exist
2912 --
2913 open c_get_ncr(p_pto_input_value_id);
2914 fetch c_get_ncr into l_rec;
2915
2916 hr_utility.set_location(l_proc, 190);
2917
2918 if c_get_ncr%notfound then
2919 --
2920 hr_utility.set_location(l_proc, 200);
2921
2922 pay_ncr_api.create_pay_net_calc_rule(
2923 p_net_calculation_rule_id => l_net_calculation_rule_id,
2924 p_accrual_plan_id => p_accrual_plan_id,
2925 p_business_group_id => l_plan_rec.business_group_id,
2926 p_input_value_id => p_pto_input_value_id,
2927 p_add_or_subtract => '-1',
2928 p_date_input_value_id => null,
2929 p_object_version_number => l_dummy
2930 );
2931
2932 hr_utility.set_location(l_proc, 210);
2933
2934 end if;
2935
2936 close c_get_ncr;
2937 --
2938 end if;
2939
2940 hr_utility.set_location(l_proc, 210);
2941 --
2942 -- Call After Process User Hook
2943 --
2944 begin
2945 hr_accrual_plan_bk2.update_accrual_plan_a
2946 (p_accrual_plan_id => p_accrual_plan_id
2947 ,p_pto_input_value_id => p_pto_input_value_id
2948 ,p_accrual_category => p_accrual_category
2949 ,p_accrual_start => p_accrual_start
2950 ,p_ineligible_period_length => p_ineligible_period_length
2951 ,p_ineligible_period_type => p_ineligible_period_type
2952 ,p_accrual_formula_id => p_accrual_formula_id
2953 ,p_co_formula_id => p_co_formula_id
2954 ,p_description => p_description
2955 ,p_ineligibility_formula_id => p_ineligibility_formula_id
2956 ,p_payroll_formula_id => p_payroll_formula_id
2957 ,p_defined_balance_id => l_plan_rec.defined_balance_id
2958 ,p_balance_dimension_id => p_balance_dimension_id
2959 ,p_tagging_element_type_id => l_plan_rec.tagging_element_type_id
2960 ,p_balance_element_type_id => l_plan_rec.balance_element_type_id
2961 ,p_object_version_number => l_object_version_number
2962 ,p_information_category => p_information_category
2963 ,p_information1 => p_information1
2964 ,p_information2 => p_information2
2965 ,p_information3 => p_information3
2966 ,p_information4 => p_information4
2967 ,p_information5 => p_information5
2968 ,p_information6 => p_information6
2969 ,p_information7 => p_information7
2970 ,p_information8 => p_information8
2971 ,p_information9 => p_information9
2972 ,p_information10 => p_information10
2973 ,p_information11 => p_information11
2974 ,p_information12 => p_information12
2975 ,p_information13 => p_information13
2976 ,p_information14 => p_information14
2977 ,p_information15 => p_information15
2978 ,p_information16 => p_information16
2979 ,p_information17 => p_information17
2980 ,p_information18 => p_information18
2981 ,p_information19 => p_information19
2982 ,p_information20 => p_information20
2983 ,p_information21 => p_information21
2984 ,p_information22 => p_information22
2985 ,p_information23 => p_information23
2986 ,p_information24 => p_information24
2987 ,p_information25 => p_information25
2988 ,p_information26 => p_information26
2989 ,p_information27 => p_information27
2990 ,p_information28 => p_information28
2991 ,p_information29 => p_information29
2992 ,p_information30 => p_information30
2993 );
2994 exception
2995 when hr_api.cannot_find_prog_unit then
2996 hr_api.cannot_find_prog_unit_error
2997 (p_module_name => 'update_accrual_plan'
2998 ,p_hook_type => 'AP'
2999 );
3000 end;
3001
3002 hr_utility.set_location(l_proc, 220);
3003
3004 --
3005 -- When in validation only mode raise the Validate_Enabled exception
3006 --
3007 if p_validate then
3008 raise hr_api.validate_enabled;
3009 end if;
3010 --
3011 -- Set all output arguments
3012 --
3013
3014 p_object_version_number := l_object_version_number;
3015 p_payroll_formula_id := l_plan_rec.payroll_formula_id;
3016 p_defined_balance_id := l_plan_rec.defined_balance_id;
3017 p_tagging_element_type_id := l_plan_rec.tagging_element_type_id;
3018 p_balance_element_type_id := l_plan_rec.balance_element_type_id;
3019
3020 hr_utility.trace(' ');
3021 hr_utility.trace(' --------------------------------'||
3022 '---------------------------------');
3023 hr_utility.trace(' IN OUT NOCOPY / OUT NOCOPY PARAMETER '||
3024 ' VALUE');
3025 hr_utility.trace(' --------------------------------'||
3026 '+--------------------------------');
3027 hr_utility.trace(' p_object_version_number '||
3028 to_char(p_object_version_number));
3029 hr_utility.trace(' p_payroll_formula_id '||
3030 to_char(p_payroll_formula_id));
3031 hr_utility.trace(' p_defined_balance_id '||
3032 to_char(p_defined_balance_id));
3033 hr_utility.trace(' p_balance_element_type_id '||
3034 to_char(p_balance_element_type_id));
3035 hr_utility.trace(' p_tagging_element_type_id '||
3036 to_char(p_tagging_element_type_id));
3037 IF p_check_accrual_ff THEN
3038 hr_utility.trace(' p_check_accrual_ff '||
3039 'TRUE');
3040 ELSE
3041 hr_utility.trace(' p_check_accrual_ff '||
3042 'FALSE');
3043 END IF;
3044 hr_utility.trace(' --------------------------------'||
3045 '---------------------------------');
3046 hr_utility.trace(' ');
3047
3048 hr_utility.set_location(' Leaving:'||l_proc, 230);
3049
3050 EXCEPTION
3051
3052 WHEN hr_api.validate_enabled THEN
3053
3054 --
3055 -- As the Validate_Enabled exception has been raised
3056 -- we must rollback to the savepoint
3057 --
3058 ROLLBACK TO update_accrual_plan;
3059 --
3060 -- Only set output warning arguments
3061 -- (Any key or derived arguments must be set to null
3062 -- when validation only mode is being used.)
3063 --
3064 p_object_version_number := null;
3065 p_payroll_formula_id := null;
3066 p_defined_balance_id := null;
3067 p_tagging_element_type_id := null;
3068 p_balance_element_type_id := null;
3069
3070 hr_utility.set_location(' Leaving:'||l_proc, 240);
3071
3072 WHEN OTHERS THEN
3073 --
3074 -- A validation or unexpected error has occured
3075 --
3076 ROLLBACK to update_accrual_plan;
3077 -- Reset IN OUT parameters and set OUT parameters.
3078 p_object_version_number := l_temp_ovn;
3079 p_payroll_formula_id := null;
3080 p_defined_balance_id := null;
3081 p_balance_element_type_id := null;
3082 p_tagging_element_type_id := null;
3083 p_check_accrual_ff := null;
3084 hr_utility.set_location(' Leaving:'||l_proc, 250);
3085
3086 RAISE;
3087
3088 END update_accrual_plan;
3089 --
3090 -- ----------------------------------------------------------------------------
3091 -- |--------------------------< delete_accrual_plan >-------------------------|
3092 -- ----------------------------------------------------------------------------
3093 --
3094 procedure delete_accrual_plan
3095 (p_validate in boolean default false
3096 ,p_effective_date in date
3097 ,p_accrual_plan_id in number
3098 ,p_accrual_plan_element_type_id in number
3099 ,p_co_element_type_id in number
3100 ,p_residual_element_type_id in number
3101 ,p_balance_element_type_id in number
3102 ,p_tagging_element_type_id in number
3103 ,p_object_version_number in number
3104 ) is
3105 --
3106 -- Declare cursors and local variables
3107 --
3108
3109 cursor c_get_ncr(p_accrual_plan_id number) is
3110 select net_calculation_rule_id,
3111 object_version_number
3112 from pay_net_calculation_rules
3113 where accrual_plan_id = p_accrual_plan_id;
3114
3115 l_proc varchar2(72);
3116 l_effective_start_date date;
3117 l_effective_end_date date;
3118 l_balance_element_type_id number;
3119
3120 begin
3121 l_proc := g_package||'delete_accrual_plan';
3122 hr_utility.set_location('Entering:'|| l_proc, 10);
3123 --
3124 -- Issue a savepoint
3125 --
3126 savepoint delete_accrual_plan;
3127 hr_utility.set_location(l_proc, 20);
3128
3129 --
3130 -- Call Before Process User Hook
3131 --
3132 begin
3133 hr_accrual_plan_bk3.delete_accrual_plan_b
3134 (p_effective_date => p_effective_date
3135 ,p_accrual_plan_id => p_accrual_plan_id
3136 ,p_accrual_plan_element_type_id => p_accrual_plan_element_type_id
3137 ,p_co_element_type_id => p_co_element_type_id
3138 ,p_residual_element_type_id => p_residual_element_type_id
3139 ,p_balance_element_type_id => p_balance_element_type_id
3140 ,p_tagging_element_type_id => p_tagging_element_type_id
3141 ,p_object_version_number => p_object_version_number
3142 );
3143 exception
3144 when hr_api.cannot_find_prog_unit then
3145 hr_api.cannot_find_prog_unit_error
3146 (p_module_name => 'delete_accrual_plan'
3147 ,p_hook_type => 'BP'
3148 );
3149 end;
3150
3151 hr_utility.set_location(l_proc, 30);
3152 --
3153 -- Validation in addition to Row Handlers
3154 --
3155
3156 hr_utility.set_location(l_proc, 40);
3157 --
3158 -- Process Logic
3159 --
3160
3161 l_effective_start_date := hr_general.start_of_time;
3162 l_effective_end_date := hr_general.end_of_time;
3163
3164 --
3165 -- delete the accrual bands
3166 --
3167 delete from pay_accrual_bands
3168 where accrual_plan_id = p_accrual_plan_id;
3169
3170
3171 --
3172 -- delete the net calculation rules
3173 --
3174 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 41);
3175 for l_ncr in c_get_ncr(p_accrual_plan_id) loop
3176 --
3177 pay_ncr_api.delete_pay_net_calc_rule (
3178 p_net_calculation_rule_id => l_ncr.net_calculation_rule_id,
3179 p_object_version_number => l_ncr.object_version_number
3180 );
3181 --
3182 end loop;
3183
3184 --
3185 -- delete the element types created for the plan
3186 --
3187 -- first the accrual plan element type...
3188 --
3189 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 42);
3190 hr_elements.chk_del_element_type (
3191 'ZAP',
3192 p_accrual_plan_element_type_id,
3193 '',
3194 p_effective_date,
3195 l_effective_start_date,
3196 l_effective_end_date);
3197
3198 --
3199 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 43);
3200 hr_elements.del_3p_element_type (
3201 p_accrual_plan_element_type_id,
3202 'ZAP',
3203 p_effective_date,
3204 l_effective_start_date,
3205 l_effective_end_date,
3206 '');
3207
3208 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',44);
3209 delete from pay_element_types_f
3210 where element_type_id = p_accrual_plan_element_type_id;
3211
3212 --
3213 -- ...then the carried over element type...
3214 --
3215
3216 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 45);
3217 hr_elements.chk_del_element_type (
3218 'ZAP',
3219 p_co_element_type_id,
3220 '',
3221 p_effective_date,
3222 l_effective_start_date,
3223 l_effective_end_date);
3224
3225 --
3226 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 46);
3227 hr_elements.del_3p_element_type (
3228 p_co_element_type_id,
3229 'ZAP',
3230 p_effective_date,
3231 l_effective_start_date,
3232 l_effective_end_date,
3233 '');
3234
3235 --
3236 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',47);
3237 hr_utility.trace('DELETE ELEMENT: '||to_char(p_co_element_type_id));
3238 delete from pay_element_types_f
3239 where element_type_id = p_co_element_type_id;
3240
3241 --
3242 -- ...then the residual element type.
3243 --
3244 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 48);
3245
3246 hr_elements.chk_del_element_type (
3247 'ZAP',
3248 p_residual_element_type_id,
3249 '',
3250 p_effective_date,
3251 l_effective_start_date,
3252 l_effective_end_date);
3253
3254 --
3255 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 49);
3256 hr_elements.del_3p_element_type (
3257 p_residual_element_type_id,
3258 'ZAP',
3259 p_effective_date,
3260 l_effective_start_date,
3261 l_effective_end_date,
3262 '');
3263
3264 --
3265 delete from pay_element_types_f
3266 where element_type_id = p_residual_element_type_id;
3267
3268
3269 --
3270 -- ...then the payroll balance element type.
3271 --
3272 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 50);
3273
3274 hr_elements.chk_del_element_type (
3275 'ZAP',
3276 p_balance_element_type_id,
3277 '',
3278 p_effective_date,
3279 l_effective_start_date,
3280 l_effective_end_date);
3281
3282 --
3283 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 51);
3284 hr_elements.del_3p_element_type (
3285 p_balance_element_type_id,
3286 'ZAP',
3287 p_effective_date,
3288 l_effective_start_date,
3289 l_effective_end_date,
3290 '');
3291
3292 --
3293 delete from pay_element_types_f
3294 where element_type_id = p_balance_element_type_id;
3295
3296 --
3297 -- ...then the tagging element type.
3298 --
3299 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 50);
3300
3301 hr_elements.chk_del_element_type (
3302 'ZAP',
3303 p_tagging_element_type_id,
3304 '',
3305 p_effective_date,
3306 l_effective_start_date,
3307 l_effective_end_date);
3308
3309 --
3310 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 51);
3311 hr_elements.del_3p_element_type (
3312 p_tagging_element_type_id,
3313 'ZAP',
3314 p_effective_date,
3315 l_effective_start_date,
3316 l_effective_end_date,
3317 '');
3318
3319 --
3320 delete from pay_element_types_f
3321 where element_type_id = p_tagging_element_type_id;
3322
3323 --
3324 -- Finally, delete the accrual plan record itself
3325 --
3326 pay_pap_del.del (
3327 p_accrual_plan_id => p_accrual_plan_id,
3328 p_object_version_number => p_object_version_number
3329 );
3330
3331 hr_utility.set_location(l_proc, 60);
3332
3333 -- MLS changes
3334 hr_accrual_plan_tl.del
3335 (p_accrual_plan_id => p_accrual_plan_id);
3336 -- MLS changes
3337 hr_utility.set_location(l_proc, 61);
3338 --
3339 -- Call After Process User Hook
3340 --
3341 begin
3342 hr_accrual_plan_bk3.delete_accrual_plan_a
3343 (p_effective_date => p_effective_date
3344 ,p_accrual_plan_id => p_accrual_plan_id
3345 ,p_accrual_plan_element_type_id => p_accrual_plan_element_type_id
3346 ,p_co_element_type_id => p_co_element_type_id
3347 ,p_residual_element_type_id => p_residual_element_type_id
3348 ,p_balance_element_type_id => p_balance_element_type_id
3349 ,p_tagging_element_type_id => p_tagging_element_type_id
3350 ,p_object_version_number => p_object_version_number
3351 );
3352 exception
3353 when hr_api.cannot_find_prog_unit then
3354 hr_api.cannot_find_prog_unit_error
3355 (p_module_name => 'delete_accrual_plan'
3356 ,p_hook_type => 'AP'
3357 );
3358 end;
3359 hr_utility.set_location(l_proc, 65);
3360
3361 --
3362 -- When in validation only mode raise the Validate_Enabled exception
3363 --
3364 if p_validate then
3365 raise hr_api.validate_enabled;
3366 end if;
3367
3368 --
3369 hr_utility.set_location(' Leaving:'||l_proc, 70);
3370 exception
3371 when hr_api.validate_enabled then
3372 --
3373 -- As the Validate_Enabled exception has been raised
3374 -- we must rollback to the savepoint
3375 --
3376 rollback to delete_accrual_plan;
3377 --
3378 -- Only set output warning arguments
3379 -- (Any key or derived arguments must be set to null
3380 -- when validation only mode is being used.)
3381 --
3382
3383 hr_utility.set_location(' Leaving:'||l_proc, 80);
3384 when others then
3385 --
3386 -- A validation or unexpected error has occured
3387 --
3388 rollback to delete_accrual_plan;
3389 hr_utility.set_location(' Leaving:'||l_proc, 90);
3390 raise;
3391 end delete_accrual_plan;
3392 --
3393 end hr_accrual_plan_api;