[Home] [Help]
PACKAGE BODY: APPS.HR_ACCRUAL_PLAN_API
Source
1 Package Body hr_accrual_plan_api as
2 /* $Header: hrpapapi.pkb 120.1.12010000.1 2008/07/28 03:37:25 appldev 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 -- Insert the two default elements of the plan into the
1805 -- net_calculation_rules table, beginning with
1806 -- the pto input value (always reduces entitlement)
1807 --
1808 pay_ncr_api.create_pay_net_calc_rule (
1809 p_net_calculation_rule_id => l_net_calc_rule_id,
1810 p_accrual_plan_id => l_accrual_plan_id,
1811 p_business_group_id => p_business_group_id,
1812 p_input_value_id => p_pto_input_value_id,
1813 p_add_or_subtract => '-1',
1814 p_date_input_value_id => null,
1815 p_object_version_number => l_dummy_number
1816 );
1817
1818 hr_utility.set_location(l_proc, 290);
1819 --
1820 -- insert the carried over input value (always increases entitlement)
1821 --
1822 pay_ncr_api.create_pay_net_calc_rule (
1823 p_net_calculation_rule_id => l_net_calc_rule_id,
1824 p_accrual_plan_id => l_accrual_plan_id,
1825 p_business_group_id => p_business_group_id,
1826 p_input_value_id => l_co_input_value_id,
1827 p_add_or_subtract => '1',
1828 p_date_input_value_id => l_co_date_input_value_id,
1829 p_object_version_number => l_dummy_number
1830 );
1831
1832 hr_utility.set_location(l_proc, 300);
1833 --
1834 -- Make PAY VALUE input value non-updateable
1835 --
1836 UPDATE pay_input_values_f
1837 SET mandatory_flag = 'X'
1838 WHERE input_value_id =
1839 ( SELECT piv.input_value_id
1840 FROM pay_input_values_f piv,
1841 pay_accrual_plans pap
1842 WHERE pap.accrual_plan_id = p_accrual_plan_id
1843 AND pap.accrual_plan_element_type_id = piv.element_type_id
1844 AND piv.name = 'Pay Value'
1845 );
1846
1847
1848 hr_utility.set_location(l_proc, 310);
1849
1850 --
1851 -- Call After Process User Hook
1852 --
1853 begin
1854 hr_accrual_plan_bk1.create_accrual_plan_a
1855 (p_effective_date => p_effective_date
1856 ,p_business_group_id => p_business_group_id
1857 ,p_accrual_formula_id => p_accrual_formula_id
1858 ,p_co_formula_id => p_co_formula_id
1859 ,p_pto_input_value_id => p_pto_input_value_id
1860 ,p_accrual_plan_name => p_accrual_plan_name
1861 ,p_accrual_units_of_measure => p_accrual_units_of_measure
1862 ,p_accrual_category => p_accrual_category
1863 ,p_accrual_start => p_accrual_start
1864 ,p_ineligible_period_length => p_ineligible_period_length
1865 ,p_ineligible_period_type => p_ineligible_period_type
1866 ,p_description => p_description
1867 ,p_ineligibility_formula_id => p_ineligibility_formula_id
1868 ,p_balance_dimension_id => p_balance_dimension_id
1869 ,p_accrual_plan_id => l_accrual_plan_id
1870 ,p_accrual_plan_element_type_id => l_accrual_plan_element_type_id
1871 ,p_co_element_type_id => l_co_element_type_id
1872 ,p_co_input_value_id => l_co_input_value_id
1873 ,p_co_date_input_value_id => l_co_date_input_value_id
1874 ,p_co_exp_date_input_value_id => l_co_exp_date_input_value_id
1875 ,p_residual_element_type_id => l_residual_element_type_id
1876 ,p_residual_input_value_id => l_residual_input_value_id
1877 ,p_residual_date_input_value_id => l_residual_date_input_value_id
1878 ,p_payroll_formula_id => l_payroll_formula_id
1879 ,p_defined_balance_id => l_defined_balance_id
1880 ,p_balance_element_type_id => l_balance_element_type_id
1881 ,p_tagging_element_type_id => l_tagging_element_type_id
1882 ,p_object_version_number => l_object_version_number
1883 ,p_no_link_message => l_no_link_message
1884 ,p_information1 => p_information1
1885 ,p_information2 => p_information2
1886 ,p_information3 => p_information3
1887 ,p_information4 => p_information4
1888 ,p_information5 => p_information5
1889 ,p_information6 => p_information6
1890 ,p_information7 => p_information7
1891 ,p_information8 => p_information8
1892 ,p_information9 => p_information9
1893 ,p_information10 => p_information10
1894 ,p_information11 => p_information11
1895 ,p_information12 => p_information12
1896 ,p_information13 => p_information13
1897 ,p_information14 => p_information14
1898 ,p_information15 => p_information15
1899 ,p_information16 => p_information16
1900 ,p_information17 => p_information17
1901 ,p_information18 => p_information18
1902 ,p_information19 => p_information19
1903 ,p_information20 => p_information20
1904 ,p_information21 => p_information21
1905 ,p_information22 => p_information22
1906 ,p_information23 => p_information23
1907 ,p_information24 => p_information24
1908 ,p_information25 => p_information25
1909 ,p_information26 => p_information26
1910 ,p_information27 => p_information27
1911 ,p_information28 => p_information28
1912 ,p_information29 => p_information29
1913 ,p_information30 => p_information30
1914 );
1915 exception
1916 when hr_api.cannot_find_prog_unit then
1917 hr_api.cannot_find_prog_unit_error
1918 (p_module_name => 'create_accrual_plan'
1919 ,p_hook_type => 'AP'
1920 );
1921 end;
1922
1923 hr_utility.set_location(l_proc, 320);
1924
1925 --
1926 -- When in validation only mode raise the Validate_Enabled exception
1927 --
1928 if p_validate then
1929 raise hr_api.validate_enabled;
1930 end if;
1931 --
1932 -- Set all output arguments
1933 --
1934 p_accrual_plan_id := l_accrual_plan_id;
1935 p_accrual_plan_element_type_id := l_accrual_plan_element_type_id;
1936 p_co_element_type_id := l_co_element_type_id;
1937 p_co_date_input_value_id := l_co_date_input_value_id;
1938 p_co_exp_date_input_value_id := l_co_exp_date_input_value_id;
1939 p_co_input_value_id := l_co_input_value_id;
1940 p_residual_element_type_id := l_residual_element_type_id;
1941 p_residual_date_input_value_id := l_residual_date_input_value_id;
1942 p_residual_input_value_id := l_residual_input_value_id;
1943 p_payroll_formula_id := l_payroll_formula_id;
1944 p_defined_balance_id := l_defined_balance_id;
1945 p_balance_element_type_id := l_balance_element_type_id;
1946 p_tagging_element_type_id := l_tagging_element_type_id;
1947 p_object_version_number := l_object_version_number;
1948 p_no_link_message := l_no_link_message;
1949
1950 --
1951 -- Pipe the main IN OUT / OUT parameters for ease of debugging.
1952 --
1953 hr_utility.trace(' ');
1954 hr_utility.trace(' --------------------------------'||
1955 '---------------------------------');
1956 hr_utility.trace(' IN OUT NOCOPY / OUT NOCOPY PARAMETER '||
1957 ' VALUE');
1958 hr_utility.trace(' --------------------------------'||
1959 '+--------------------------------');
1960 hr_utility.trace(' p_accrual_plan_id '||
1961 to_char(p_accrual_plan_id));
1962 hr_utility.trace(' p_accrual_plan_element_type_id '||
1963 to_char(p_accrual_plan_element_type_id));
1964 hr_utility.trace(' p_co_element_type_id '||
1965 to_char(p_co_element_type_id));
1966 hr_utility.trace(' p_co_input_value_id '||
1967 to_char(p_co_input_value_id));
1968 hr_utility.trace(' p_co_date_input_value_id '||
1969 to_char(p_co_date_input_value_id));
1970 hr_utility.trace(' p_co_exp_date_input_value_id '||
1971 to_char(p_co_exp_date_input_value_id));
1972 hr_utility.trace(' p_residual_element_type_id '||
1973 to_char(p_residual_element_type_id));
1974 hr_utility.trace(' p_residual_input_value_id '||
1975 to_char(p_residual_input_value_id));
1976 hr_utility.trace(' p_residual_date_input_value_id '||
1977 to_char(p_residual_date_input_value_id));
1978 hr_utility.trace(' p_payroll_formula_id '||
1979 to_char(p_payroll_formula_id));
1980 hr_utility.trace(' p_defined_balance_id '||
1981 to_char(p_defined_balance_id));
1982 hr_utility.trace(' p_balance_element_type_id '||
1983 to_char(p_balance_element_type_id));
1984 hr_utility.trace(' p_tagging_element_type_id '||
1985 to_char(p_tagging_element_type_id));
1986 hr_utility.trace(' p_object_version_number '||
1987 to_char(p_object_version_number));
1988 IF p_no_link_message THEN
1989 hr_utility.trace(' p_no_link_message '||
1990 'TRUE');
1991 ELSE
1992 hr_utility.trace(' p_no_link_message '||
1993 'FALSE');
1994 END IF;
1995 IF p_check_accrual_ff THEN
1996 hr_utility.trace(' p_check_accrual_ff '||
1997 'TRUE');
1998 ELSE
1999 hr_utility.trace(' p_check_accrual_ff '||
2000 'FALSE');
2001 END IF;
2002 hr_utility.trace(' --------------------------------'||
2003 '---------------------------------');
2004 hr_utility.trace(' ');
2005
2006 hr_utility.set_location(' Leaving:'||l_proc, 70);
2007
2008 EXCEPTION
2009
2010 WHEN hr_api.validate_enabled THEN
2011 --
2012 -- As the Validate_Enabled exception has been raised
2013 -- we must rollback to the savepoint
2014 --
2015 ROLLBACK to create_accrual_plan;
2016 --
2017 -- Only set output warning arguments
2018 -- (Any key or derived arguments must be set to null
2019 -- when validation only mode is being used.)
2020 --
2021 p_accrual_plan_id := null;
2022 p_accrual_plan_element_type_id := null;
2023 p_co_element_type_id := null;
2024 p_co_date_input_value_id := null;
2025 p_co_exp_date_input_value_id := null;
2026 p_co_input_value_id := null;
2027 p_residual_element_type_id := null;
2028 p_residual_date_input_value_id := null;
2029 p_residual_input_value_id := null;
2030 p_payroll_formula_id := null;
2031 p_defined_balance_id := null;
2032 p_balance_element_type_id := null;
2033 p_tagging_element_type_id := null;
2034 p_object_version_number := null;
2035
2036 hr_utility.set_location(' Leaving:'||l_proc, 330);
2037
2038 WHEN OTHERS THEN
2039 --
2040 -- A validation or unexpected error has occured
2041 --
2042 ROLLBACK to create_accrual_plan;
2043 -- Set OUT parameters to NULL.
2044 p_accrual_plan_id := null;
2045 p_accrual_plan_element_type_id := null;
2046 p_co_element_type_id := null;
2047 p_co_input_value_id := null;
2048 p_co_date_input_value_id := null;
2049 p_co_exp_date_input_value_id := null;
2050 p_residual_element_type_id := null;
2051 p_residual_input_value_id := null;
2052 p_residual_date_input_value_id := null;
2053 p_payroll_formula_id := null;
2054 p_defined_balance_id := null;
2055 p_balance_element_type_id := null;
2056 p_tagging_element_type_id := null;
2057 p_object_version_number := null;
2058 p_no_link_message := null;
2059 p_check_accrual_ff := null;
2060
2061 hr_utility.set_location(' Leaving:'||l_proc, 340);
2062 RAISE;
2063
2064 END create_accrual_plan;
2065 --
2066 -- ----------------------------------------------------------------------------
2067 -- |-------------------------< update_accrual_plan >--------------------------|
2068 -- ----------------------------------------------------------------------------
2069 --
2070 PROCEDURE update_accrual_plan
2071 (p_validate in boolean default false
2072 ,p_effective_date in date
2073 ,p_accrual_plan_id in number
2074 ,p_pto_input_value_id in number default hr_api.g_number
2075 ,p_accrual_category in varchar2 default hr_api.g_varchar2
2076 ,p_accrual_start in varchar2 default hr_api.g_varchar2
2077 ,p_ineligible_period_length in number default hr_api.g_number
2078 ,p_ineligible_period_type in varchar2 default hr_api.g_varchar2
2079 ,p_accrual_formula_id in number default hr_api.g_number
2080 ,p_co_formula_id in number default hr_api.g_number
2081 ,p_description in varchar2 default hr_api.g_varchar2
2082 ,p_ineligibility_formula_id in number default hr_api.g_number
2083 ,p_balance_dimension_id in number default hr_api.g_number
2084 ,p_information_category in varchar2 default hr_api.g_varchar2
2085 ,p_information1 in varchar2 default hr_api.g_varchar2
2086 ,p_information2 in varchar2 default hr_api.g_varchar2
2087 ,p_information3 in varchar2 default hr_api.g_varchar2
2088 ,p_information4 in varchar2 default hr_api.g_varchar2
2089 ,p_information5 in varchar2 default hr_api.g_varchar2
2090 ,p_information6 in varchar2 default hr_api.g_varchar2
2091 ,p_information7 in varchar2 default hr_api.g_varchar2
2092 ,p_information8 in varchar2 default hr_api.g_varchar2
2093 ,p_information9 in varchar2 default hr_api.g_varchar2
2094 ,p_information10 in varchar2 default hr_api.g_varchar2
2095 ,p_information11 in varchar2 default hr_api.g_varchar2
2096 ,p_information12 in varchar2 default hr_api.g_varchar2
2097 ,p_information13 in varchar2 default hr_api.g_varchar2
2098 ,p_information14 in varchar2 default hr_api.g_varchar2
2099 ,p_information15 in varchar2 default hr_api.g_varchar2
2100 ,p_information16 in varchar2 default hr_api.g_varchar2
2101 ,p_information17 in varchar2 default hr_api.g_varchar2
2102 ,p_information18 in varchar2 default hr_api.g_varchar2
2103 ,p_information19 in varchar2 default hr_api.g_varchar2
2104 ,p_information20 in varchar2 default hr_api.g_varchar2
2105 ,p_information21 in varchar2 default hr_api.g_varchar2
2106 ,p_information22 in varchar2 default hr_api.g_varchar2
2107 ,p_information23 in varchar2 default hr_api.g_varchar2
2108 ,p_information24 in varchar2 default hr_api.g_varchar2
2109 ,p_information25 in varchar2 default hr_api.g_varchar2
2110 ,p_information26 in varchar2 default hr_api.g_varchar2
2111 ,p_information27 in varchar2 default hr_api.g_varchar2
2112 ,p_information28 in varchar2 default hr_api.g_varchar2
2113 ,p_information29 in varchar2 default hr_api.g_varchar2
2114 ,p_information30 in varchar2 default hr_api.g_varchar2
2115 ,p_object_version_number in out nocopy number
2116 ,p_payroll_formula_id out nocopy number
2117 ,p_defined_balance_id out nocopy number
2118 ,p_balance_element_type_id out nocopy number
2119 ,p_tagging_element_type_id out nocopy number
2120 ,p_check_accrual_ff out nocopy boolean)
2121 IS
2122
2123 --
2124 -- Fetches the current accrual plan.
2125 --
2126 cursor csr_get_accrual_plan IS
2127 select *
2128 from pay_accrual_plans pap
2129 where pap.accrual_plan_id = p_accrual_plan_id;
2130
2131 --
2132 -- Declare cursors and local variables
2133 --
2134 cursor c_get_ncr(p_pto_input_value_id number) is
2135 select net_calculation_rule_id,
2136 object_version_number
2137 from pay_net_calculation_rules
2138 where accrual_plan_id = p_accrual_plan_id
2139 and input_value_id = p_pto_input_value_id;
2140
2141 cursor c_class_id(p_classification_name varchar2,
2142 p_business_group_id number,
2143 p_legislation_code varchar2) is
2144 select classification_id
2145 from pay_element_classifications
2146 where classification_name = p_classification_name
2147 and (business_group_id = p_business_group_id or
2148 legislation_code = p_legislation_code);
2149
2150 --
2151 -- Cursor to retrieve details of absence element link, to be
2152 -- copied into links for other elements.
2153 --
2154 cursor c_absence_element_link_id is
2155 select *
2156 from pay_element_links_f
2157 where element_link_id in ( select distinct pel.element_link_id
2158 from pay_element_links_f pel,
2159 pay_input_values_f piv
2160 where pel.element_type_id = piv.element_type_id
2161 and piv.input_value_id = p_pto_input_value_id);
2162
2163 --
2164 -- Cursor to get translated values for element and input value names
2165 --
2166 cursor c_get_lookups(p_lookup_code varchar2) is
2167 select meaning
2168 from hr_lookups
2169 where lookup_type = 'NAME_TRANSLATIONS'
2170 and lookup_code = p_lookup_code;
2171
2172 --
2173 -- Cursors to get balance category id - first check for
2174 -- legislative specific entry, then look for global entry
2175 --
2176 cursor c_get_leg_bal_cat_id(p_leg_code varchar2) is
2177 select balance_category_id
2178 from pay_balance_categories_f
2179 where category_name = 'PTO Accruals'
2180 and legislation_code = p_leg_code;
2181
2182 cursor c_get_gbl_bal_cat_id is
2183 select balance_category_id
2184 from pay_balance_categories_f
2185 where category_name = 'PTO Accruals'
2186 and legislation_code is null;
2187
2188 l_proc varchar2(72);
2189 l_plan_rec csr_get_accrual_plan%ROWTYPE;
2190 l_rec c_get_ncr%rowtype;
2191 l_net_calculation_rule_id number;
2192 l_object_version_number number;
2193 l_dummy number;
2194 l_effective_start_date date;
2195 l_effective_end_date date;
2196 l_user varchar2(80);
2197 l_rowid varchar2(80);
2198 l_status_processing_rule_id number;
2199 l_formula_result_rule_id number;
2200 l_priority number;
2201 l_balance_category_id number;
2202 l_balance_type_id number;
2203 l_balance_feed_id number;
2204 l_tagging_formula_id number;
2205 l_balance_input_value_id number;
2206 l_tagging_input_value_id number;
2207 l_balance_name varchar2(80);
2208 l_leg_code varchar2(30);
2209 l_uom_code varchar2(80);
2210 l_uom_code1 varchar2(80);
2211 l_element_name varchar2(80);
2212 l_input_value_name varchar2(80);
2213 l_primary_classification_id number;
2214 l_classification_type varchar2(2);
2215 l_bg_name per_business_groups.name%TYPE;
2216 l_curr_code varchar2(150);
2217 l_classification_name varchar2(240);
2218 l_post_termination_rule varchar2(240);
2219 l_temp_ovn number;
2220
2221 BEGIN
2222
2223 l_proc := g_package||'update_accrual_plan';
2224 l_temp_ovn := p_object_version_number;
2225
2226 hr_utility.set_location('Entering:'|| l_proc, 10);
2227
2228 --
2229 -- Pipe the main IN / IN OUT parameters for ease of debugging.
2230 --
2231 hr_utility.trace(' ');
2232 hr_utility.trace(' --------------------------------'||
2233 '---------------------------------');
2234 hr_utility.trace(' IN / IN OUT NOCOPY PARAMETER '||
2235 ' VALUE');
2236 hr_utility.trace(' --------------------------------'||
2237 '+--------------------------------');
2238 IF p_validate THEN
2239 hr_utility.trace(' p_validate '||
2240 'TRUE');
2241 ELSE
2242 hr_utility.trace(' p_validate '||
2243 'FALSE');
2244 END IF;
2245 hr_utility.trace(' p_effective_date '||
2246 to_char(p_effective_date));
2247 hr_utility.trace(' p_accrual_plan_id '||
2248 to_char(p_accrual_plan_id));
2249 hr_utility.trace(' p_pto_input_value_id '||
2250 to_char(p_pto_input_value_id));
2251 hr_utility.trace(' p_accrual_category '||
2252 p_accrual_category);
2253 hr_utility.trace(' p_accrual_start '||
2254 p_accrual_start);
2255 hr_utility.trace(' p_ineligible_period_length '||
2256 to_char(p_ineligible_period_length));
2257 hr_utility.trace(' p_ineligible_period_type '||
2258 p_ineligible_period_type);
2259 hr_utility.trace(' p_accrual_formula_id '||
2260 to_char(p_accrual_formula_id));
2261 hr_utility.trace(' p_co_formula_id '||
2262 to_char(p_co_formula_id));
2263 hr_utility.trace(' p_description '||
2264 p_description);
2265 hr_utility.trace(' p_ineligibility_formula_id '||
2266 to_char(p_ineligibility_formula_id));
2267 hr_utility.trace(' p_balance_dimension_id '||
2268 to_char(p_balance_dimension_id));
2269 hr_utility.trace(' p_information_category '||
2270 p_information_category);
2271 hr_utility.trace(' --------------------------------'||
2272 '---------------------------------');
2273 hr_utility.trace(' ');
2274
2275 --
2276 -- Issue a savepoint
2277 --
2278 savepoint update_accrual_plan;
2279
2280 --
2281 -- Call Before Process User Hook
2282 --
2283 begin
2284 hr_accrual_plan_bk2.update_accrual_plan_b
2285 (p_accrual_plan_id => p_accrual_plan_id
2286 ,p_pto_input_value_id => p_pto_input_value_id
2287 ,p_accrual_category => p_accrual_category
2288 ,p_accrual_start => p_accrual_start
2289 ,p_ineligible_period_length => p_ineligible_period_length
2290 ,p_ineligible_period_type => p_ineligible_period_type
2291 ,p_accrual_formula_id => p_accrual_formula_id
2292 ,p_co_formula_id => p_co_formula_id
2293 ,p_description => p_description
2294 ,p_ineligibility_formula_id => p_ineligibility_formula_id
2295 ,p_balance_dimension_id => p_balance_dimension_id
2296 ,p_object_version_number => p_object_version_number
2297 ,p_information_category => p_information_category
2298 ,p_information1 => p_information1
2299 ,p_information2 => p_information2
2300 ,p_information3 => p_information3
2301 ,p_information4 => p_information4
2302 ,p_information5 => p_information5
2303 ,p_information6 => p_information6
2304 ,p_information7 => p_information7
2305 ,p_information8 => p_information8
2306 ,p_information9 => p_information9
2307 ,p_information10 => p_information10
2308 ,p_information11 => p_information11
2309 ,p_information12 => p_information12
2310 ,p_information13 => p_information13
2311 ,p_information14 => p_information14
2312 ,p_information15 => p_information15
2313 ,p_information16 => p_information16
2314 ,p_information17 => p_information17
2315 ,p_information18 => p_information18
2316 ,p_information19 => p_information19
2317 ,p_information20 => p_information20
2318 ,p_information21 => p_information21
2319 ,p_information22 => p_information22
2320 ,p_information23 => p_information23
2321 ,p_information24 => p_information24
2322 ,p_information25 => p_information25
2323 ,p_information26 => p_information26
2324 ,p_information27 => p_information27
2325 ,p_information28 => p_information28
2326 ,p_information29 => p_information29
2327 ,p_information30 => p_information30
2328
2329 );
2330 exception
2331 when hr_api.cannot_find_prog_unit then
2332 hr_api.cannot_find_prog_unit_error
2333 (p_module_name => 'update_accrual_plan'
2334 ,p_hook_type => 'BP'
2335 );
2336 end;
2337
2338 hr_utility.set_location(l_proc, 20);
2339
2340 --
2341 -- Validation in addition to Row Handlers
2342 --
2343 -- Fetch the current accrual plan details.
2344 --
2345 OPEN csr_get_accrual_plan;
2346 FETCH csr_get_accrual_plan into l_plan_rec;
2347
2348 IF csr_get_accrual_plan%NOTFOUND THEN
2349 --
2350 -- p_accrual_plan_id does not exist so error.
2351 --
2352 hr_utility.set_location(l_proc, 30);
2353 CLOSE csr_get_accrual_plan;
2354 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2355 fnd_message.raise_error;
2356
2357 END IF;
2358
2359 CLOSE csr_get_accrual_plan;
2360
2361 hr_utility.set_location(l_proc, 40);
2362
2363 IF l_plan_rec.defined_balance_id IS NULL AND
2364 p_balance_dimension_id IS NOT NULL AND
2365 p_balance_dimension_id <> hr_api.g_number THEN
2366
2367 --
2368 -- Setup the accrual plan for payroll balances.
2369 --
2370
2371 -- The items returned by this select are used in setting up
2372 -- the elements with the correct leg code and currency code.
2373 --
2374 select name, legislation_code, currency_code
2375 into l_bg_name, l_leg_code, l_curr_code
2376 from per_business_groups
2377 where business_group_id = l_plan_rec.business_group_id;
2378
2379 hr_utility.trace('l_bg_name: '||l_bg_name);
2380 hr_utility.trace('l_leg_code: '||l_leg_code);
2381 hr_utility.trace('l_curr_code: '||l_curr_code);
2382
2383 l_classification_name := 'Information';
2384
2385 open c_class_id('Information', l_plan_rec.business_group_id, l_leg_code);
2386 fetch c_class_id into l_primary_classification_id;
2387 close c_class_id;
2388
2389 hr_utility.set_location(l_proc, 50);
2390
2391 select processing_priority + 1
2392 into l_priority
2393 from pay_element_types_f
2394 where element_type_id = l_plan_rec.accrual_plan_element_type_id;
2395
2396 hr_utility.trace('l_priority: '||to_char(l_priority));
2397
2398 l_leg_code := null;
2399
2400 --
2401 -- Get the termination rule
2402 --
2403 begin
2404 --
2405 select hl.meaning
2406 into l_post_termination_rule
2407 from hr_lookups hl
2408 where hl.lookup_type='TERMINATION_RULE'
2409 and hl.lookup_code='F'; -- Final Close
2410
2411 hr_utility.trace('l_post_termination_rule: '||l_post_termination_rule);
2412
2413 exception
2414 --
2415 when no_data_found then
2416 hr_utility.set_location(l_proc, 60);
2417 hr_utility.set_message(801,'HR_NO_F_TERM_RULE');
2418 hr_utility.raise_error;
2419 --
2420 end;
2421
2422 l_classification_type := 'Y';
2423
2424 --
2425 if l_plan_rec.accrual_units_of_measure = 'D' then
2426 --
2427 hr_utility.set_location(l_proc, 70);
2428 open c_get_lookups('PTO_DAYS');
2429 l_uom_code := 'ND';
2430 --
2431 else
2432 --
2433 hr_utility.set_location(l_proc, 80);
2434 open c_get_lookups('PTO_HOURS');
2435 l_uom_code := 'H_DECIMAL3';
2436 --
2437 end if;
2438
2439 fetch c_get_lookups into l_input_value_name;
2440 close c_get_lookups;
2441
2442 hr_utility.trace('l_input_value_name: '||l_input_value_name);
2443
2444 IF l_plan_rec.balance_element_type_id IS NULL THEN
2445 --
2446 -- This plan was created prior to the balance enhancements and
2447 -- so does not alerady have the balance element. It is now
2448 -- being updated for the first time so create the additional
2449 -- element.
2450 --
2451 l_element_name := substr(l_plan_rec.accrual_plan_name
2452 ||' Payroll Balance', 1, 80);
2453
2454 hr_utility.trace('l_element_name: '||l_element_name);
2455
2456 l_plan_rec.balance_element_type_id := create_element(
2457 l_element_name,
2458 null,
2459 'N',
2460 l_bg_name,
2461 l_classification_name,
2462 l_leg_code,
2463 l_curr_code,
2464 l_post_termination_rule,
2465 'Y',
2466 'Y',
2467 null,
2468 l_priority
2469 );
2470
2471 hr_utility.trace('l_plan_rec.balance_element_type_id: '
2472 ||to_char(l_plan_rec.balance_element_type_id));
2473
2474 l_balance_input_value_id := create_input_value(
2475 l_element_name,
2476 l_input_value_name,
2477 l_uom_code,
2478 l_bg_name,
2479 l_plan_rec.balance_element_type_id,
2480 l_primary_classification_id,
2481 l_plan_rec.business_group_id,
2482 'N',
2483 l_leg_code,
2484 l_classification_type,
2485 'N'
2486 );
2487
2488 hr_utility.trace('l_balance_input_value_id: '||to_char(l_balance_input_value_id));
2489
2490 for l_element_link_rec in c_absence_element_link_id loop
2491 --
2492 --
2493 -- Create a link for the balance element
2494 --
2495 create_element_link
2496 (p_element_type_id => l_plan_rec.balance_element_type_id,
2497 p_absence_link_rec => l_element_link_rec,
2498 p_legislation_code => l_leg_code);
2499
2500 end loop;
2501
2502 END IF;
2503
2504 hr_utility.set_location(l_proc, 90);
2505
2506 IF l_plan_rec.tagging_element_type_id IS NULL THEN
2507 --
2508 -- This plan was created prior to the balance enhancements and
2509 -- so does not alerady have the tagging element. It is now
2510 -- being updated for the first time so create the additional
2511 -- element.
2512 --
2513 select processing_priority
2514 into l_priority
2515 from pay_element_types_f
2516 where element_type_id = l_plan_rec.accrual_plan_element_type_id;
2517
2518 hr_utility.trace('l_priority: '||to_char(l_priority));
2519
2520 l_element_name := substr(l_plan_rec.accrual_plan_name||' Tagging', 1, 80);
2521
2522 hr_utility.trace('l_element_name: '||l_element_name);
2523
2524 l_plan_rec.tagging_element_type_id := create_element(
2525 l_element_name,
2526 null,
2527 'N',
2528 l_bg_name,
2529 l_classification_name,
2530 l_leg_code,
2531 l_curr_code,
2532 l_post_termination_rule,
2533 'Y',
2534 'Y',
2535 null,
2536 l_priority
2537 );
2538
2539 hr_utility.trace('l_plan_rec.tagging_element_type_id: '
2540 ||to_char(l_plan_rec.tagging_element_type_id));
2541 l_uom_code1 := 'N';
2542 l_input_value_name := 'Element Entry ID';
2543
2544 l_tagging_input_value_id := create_input_value(
2545 l_element_name,
2546 l_input_value_name,
2547 l_uom_code1,
2548 l_bg_name,
2549 l_plan_rec.tagging_element_type_id,
2550 l_primary_classification_id,
2551 l_plan_rec.business_group_id,
2552 'N',
2553 l_leg_code,
2554 l_classification_type,
2555 'N'
2556 );
2557
2558 hr_utility.trace('l_tagging_input_value_id: '||to_char(l_tagging_input_value_id));
2559
2560 for l_element_link_rec in c_absence_element_link_id loop
2561 --
2562 -- Create a link for the tagging element
2563 --
2564 create_element_link
2565 (p_element_type_id => l_plan_rec.tagging_element_type_id,
2566 p_absence_link_rec => l_element_link_rec,
2567 p_legislation_code => l_leg_code);
2568
2569 end loop;
2570
2571 END IF;
2572
2573 hr_utility.set_location(l_proc, 100);
2574
2575 l_user := fnd_profile.value('USER');
2576 l_effective_start_date := hr_general.start_of_time;
2577 l_effective_end_date := hr_general.end_of_time;
2578
2579 hr_utility.trace('l_user: '||l_user);
2580 --
2581 -- Now set up the balance information. This requires rows to be inserted in
2582 -- pay_balance_feeds, pay_balace_types, and pay_defined_balances. We will
2583 -- make use of the seeded balance dimension, route etc.
2584 --
2585 l_leg_code := null;
2586 l_balance_name := substr(l_plan_rec.accrual_plan_name||' Balance', 1, 80);
2587
2588 open c_get_leg_bal_cat_id(l_leg_code);
2589 fetch c_get_leg_bal_cat_id into l_balance_category_id;
2590 if c_get_leg_bal_cat_id%notfound then
2591 open c_get_gbl_bal_cat_id;
2592 fetch c_get_gbl_bal_cat_id into l_balance_category_id;
2593 if c_get_gbl_bal_cat_id%notfound then
2594 l_balance_category_id := null;
2595 end if;
2596 close c_get_gbl_bal_cat_id;
2597 end if;
2598 close c_get_leg_bal_cat_id;
2599
2600 pay_balance_types_pkg.Insert_Row(
2601 X_Rowid => l_rowid,
2602 X_Balance_Type_Id => l_balance_type_id,
2603 X_Business_Group_Id => l_plan_rec.business_group_id,
2604 X_Legislation_Code => l_leg_code,
2605 X_Currency_Code => null,
2606 X_Assignment_Remuneration_Flag => 'N',
2607 X_Balance_Name => l_balance_name,
2608 X_Base_Balance_Name => l_balance_name,
2609 X_Balance_Uom => l_uom_code,
2610 X_Comments => null,
2611 X_Legislation_Subgroup => null,
2612 X_Reporting_Name => substr(l_balance_name, 1, 30),
2613 X_Attribute_Category => null,
2614 X_Attribute1 => null,
2615 X_Attribute2 => null,
2616 X_Attribute3 => null,
2617 X_Attribute4 => null,
2618 X_Attribute5 => null,
2619 X_Attribute6 => null,
2620 X_Attribute7 => null,
2621 X_Attribute8 => null,
2622 X_Attribute9 => null,
2623 X_Attribute10 => null,
2624 X_Attribute11 => null,
2625 X_Attribute12 => null,
2626 X_Attribute13 => null,
2627 X_Attribute14 => null,
2628 X_Attribute15 => null,
2629 X_Attribute16 => null,
2630 X_Attribute17 => null,
2631 X_Attribute18 => null,
2632 X_Attribute19 => null,
2633 X_Attribute20 => null,
2634 X_Balance_Category_Id => l_balance_category_id
2635 );
2636
2637 hr_utility.set_location(l_proc, 100);
2638
2639 select input_value_id
2640 into l_balance_input_value_id
2641 from pay_input_values_f
2642 where element_type_id = l_plan_rec.balance_element_type_id
2643 and uom = l_uom_code;
2644
2645 hr_utility.trace('l_balance_input_value_id: '||to_char(l_balance_input_value_id));
2646
2647 pay_balance_feeds_f_pkg.Insert_Row(
2648 X_Rowid => l_rowid,
2649 X_Balance_Feed_Id => l_balance_feed_id,
2650 X_Effective_Start_Date => hr_general.start_of_time,
2651 X_Effective_End_Date => hr_general.end_of_time,
2652 X_Business_Group_Id => l_plan_rec.business_group_id,
2653 X_Legislation_Code => l_leg_code,
2654 X_Balance_Type_Id => l_balance_type_id,
2655 X_Input_Value_Id => l_balance_input_value_id,
2656 X_Scale => 1,
2657 X_Legislation_Subgroup => null
2658 );
2659
2660 hr_utility.set_location(l_proc, 110);
2661
2662 pay_defined_balances_pkg.Insert_Row(
2663 X_Rowid => l_rowid,
2664 X_Defined_Balance_Id => l_plan_rec.defined_balance_id,
2665 X_Business_Group_Id => l_plan_rec.business_group_id,
2666 X_Legislation_Code => l_leg_code,
2667 X_Balance_Type_Id => l_balance_type_id,
2668 X_Balance_Dimension_Id => p_balance_dimension_id,
2669 X_Force_Latest_Balance_Flag=> 'N',
2670 X_Legislation_Subgroup => null
2671 );
2672
2673 hr_utility.set_location(l_proc, 120);
2674
2675 --
2676 -- Call procedure to create new Oracle Payroll fomrula.
2677 --
2678 create_payroll_formula(
2679 p_formula_id => l_plan_rec.payroll_formula_id,
2680 p_effective_start_date => l_effective_start_date,
2681 p_effective_end_date => l_effective_end_date,
2682 p_accrual_plan_name => l_plan_rec.accrual_plan_name,
2683 p_defined_balance_id => l_plan_rec.defined_balance_id,
2684 p_business_group_id => l_plan_rec.business_group_id,
2685 p_legislation_code => l_leg_code
2686 );
2687
2688 hr_utility.set_location(l_proc, 130);
2689
2690 --
2691 -- Set up the status processing rules and formula result rules for the
2692 -- recurring accrual plan element.
2693 --
2694 pay_status_rules_pkg.insert_row
2695 (X_Rowid => l_rowid,
2696 X_Status_Processing_Rule_Id => l_status_processing_rule_id,
2697 X_Effective_Start_Date => l_effective_start_date,
2698 X_Effective_End_Date => l_effective_end_date,
2699 X_Business_Group_Id => l_plan_rec.business_group_id,
2700 X_Legislation_Code => l_leg_code,
2701 X_Element_Type_Id => l_plan_rec.accrual_plan_element_type_id,
2702 X_Assignment_Status_Type_Id => null,
2703 X_Formula_Id => l_plan_rec.payroll_formula_id,
2704 X_Processing_Rule => 'P',
2705 X_Comment_Id => null,
2706 X_Legislation_Subgroup => null,
2707 X_Last_Update_Date => sysdate,
2708 X_Last_Updated_By => l_user,
2709 X_Last_Update_Login => l_user,
2710 X_Created_By => l_user,
2711 X_Creation_Date => sysdate
2712 );
2713
2714 hr_utility.set_location(l_proc, 140);
2715
2716 pay_formula_result_rules_pkg.insert_row
2717 (p_Rowid => l_rowid,
2718 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
2719 p_Effective_Start_Date => l_effective_start_date,
2720 p_Effective_End_Date => l_effective_end_date,
2721 p_Business_Group_Id => l_plan_rec.business_group_id,
2722 p_Legislation_Code => l_leg_code,
2723 p_Element_Type_Id => l_plan_rec.balance_element_type_id,
2724 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
2725 p_Result_Name => 'TOTAL_ACCRUED_PTO',
2726 p_Result_Rule_Type => 'I',
2727 p_Legislation_Subgroup => null,
2728 p_Severity_Level => 'I',
2729 p_Input_Value_Id => l_balance_input_value_id,
2730 p_Created_By => l_user,
2731 p_session_date => sysdate
2732 );
2733
2734 l_formula_result_rule_id := null;
2735
2736 select input_value_id
2737 into l_tagging_input_value_id
2738 from pay_input_values_f
2739 where element_type_id = l_plan_rec.tagging_element_type_id
2740 and uom = 'N';
2741
2742 hr_utility.trace('l_tagging_input_value_id: '||to_char(l_tagging_input_value_id));
2743
2744 pay_formula_result_rules_pkg.insert_row
2745 (p_Rowid => l_rowid,
2746 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
2747 p_Effective_Start_Date => l_effective_start_date,
2748 p_Effective_End_Date => l_effective_end_date,
2749 p_Business_Group_Id => l_plan_rec.business_group_id,
2750 p_Legislation_Code => l_leg_code,
2751 p_Element_Type_Id => l_plan_rec.tagging_element_type_id,
2752 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
2753 p_Result_Name => 'DUMMY',
2754 p_Result_Rule_Type => 'I',
2755 p_Legislation_Subgroup => null,
2756 p_Severity_Level => 'I',
2757 p_Input_Value_Id => l_tagging_input_value_id,
2758 p_Created_By => l_user,
2759 p_session_date => sysdate
2760 );
2761
2762 --
2763 -- Set up the status processing rules and formula result rules for the
2764 -- tagging element.
2765 --
2766
2767 select formula_id
2768 into l_tagging_formula_id
2769 from ff_formulas_f ff,
2770 ff_formula_types ft
2771 where ff.formula_type_id = ft.formula_type_id
2772 and ft.formula_type_name = 'Oracle Payroll'
2773 and ff.formula_name = 'PTO_TAGGING_FORMULA';
2774
2775 hr_utility.trace('l_tagging_formula_id: '||to_char(l_tagging_formula_id));
2776
2777 l_status_processing_rule_id := null;
2778 l_formula_result_rule_id := null;
2779
2780 pay_status_rules_pkg.insert_row
2781 (X_Rowid => l_rowid,
2782 X_Status_Processing_Rule_Id => l_status_processing_rule_id,
2783 X_Effective_Start_Date => l_effective_start_date,
2784 X_Effective_End_Date => l_effective_end_date,
2785 X_Business_Group_Id => l_plan_rec.business_group_id,
2786 X_Legislation_Code => l_leg_code,
2787 X_Element_Type_Id => l_plan_rec.tagging_element_type_id,
2788 X_Assignment_Status_Type_Id => null,
2789 X_Formula_Id => l_tagging_formula_id,
2790 X_Processing_Rule => 'P',
2791 X_Comment_Id => null,
2792 X_Legislation_Subgroup => null,
2793 X_Last_Update_Date => sysdate,
2794 X_Last_Updated_By => l_user,
2795 X_Last_Update_Login => l_user,
2796 X_Created_By => l_user,
2797 X_Creation_Date => sysdate
2798 );
2799
2800 hr_utility.set_location(l_proc, 150);
2801
2802 pay_formula_result_rules_pkg.insert_row
2803 (p_Rowid => l_rowid,
2804 p_Formula_Result_Rule_Id => l_formula_result_rule_id,
2805 p_Effective_Start_Date => l_effective_start_date,
2806 p_Effective_End_Date => l_effective_end_date,
2807 p_Business_Group_Id => l_plan_rec.business_group_id,
2808 p_Legislation_Code => l_leg_code,
2809 p_Element_Type_Id => l_plan_rec.tagging_element_type_id,
2810 p_Status_Processing_Rule_Id => l_status_processing_rule_id,
2811 p_Result_Name => 'RETRO_ELEMENT_ENTRY_ID',
2812 p_Result_Rule_Type => 'I',
2813 p_Legislation_Subgroup => null,
2814 p_Severity_Level => 'I',
2815 p_Input_Value_Id => l_tagging_input_value_id,
2816 p_Created_By => l_user,
2817 p_session_date => sysdate
2818 );
2819
2820 hr_utility.set_location(l_proc, 160);
2821
2822 end if;
2823
2824 l_object_version_number := p_object_version_number;
2825
2826 hr_utility.set_location(l_proc, 170);
2827
2828 pay_pap_upd.upd
2829 (p_effective_date => p_effective_date,
2830 p_accrual_plan_id => p_accrual_plan_id,
2831 p_pto_input_value_id => p_pto_input_value_id,
2832 p_accrual_category => p_accrual_category,
2833 p_accrual_start => p_accrual_start,
2834 p_ineligible_period_length => p_ineligible_period_length,
2835 p_ineligible_period_type => p_ineligible_period_type,
2836 p_accrual_formula_id => p_accrual_formula_id,
2837 p_co_formula_id => p_co_formula_id,
2838 p_description => p_description,
2839 p_ineligibility_formula_id => p_ineligibility_formula_id,
2840 p_payroll_formula_id => l_plan_rec.payroll_formula_id,
2841 p_defined_balance_id => l_plan_rec.defined_balance_id,
2842 p_tagging_element_type_id => l_plan_rec.tagging_element_type_id,
2843 p_balance_element_type_id => l_plan_rec.balance_element_type_id,
2844 p_information_category => p_information_category,
2845 p_information1 => p_information1,
2846 p_information2 => p_information2,
2847 p_information3 => p_information3,
2848 p_information4 => p_information4,
2849 p_information5 => p_information5,
2850 p_information6 => p_information6,
2851 p_information7 => p_information7,
2852 p_information8 => p_information8,
2853 p_information9 => p_information9,
2854 p_information10 => p_information10,
2855 p_information11 => p_information11,
2856 p_information12 => p_information12,
2857 p_information13 => p_information13,
2858 p_information14 => p_information14,
2859 p_information15 => p_information15,
2860 p_information16 => p_information16,
2861 p_information17 => p_information17,
2862 p_information18 => p_information18,
2863 p_information19 => p_information19,
2864 p_information20 => p_information20,
2865 p_information21 => p_information21,
2866 p_information22 => p_information22,
2867 p_information23 => p_information23,
2868 p_information24 => p_information24,
2869 p_information25 => p_information25,
2870 p_information26 => p_information26,
2871 p_information27 => p_information27,
2872 p_information28 => p_information28,
2873 p_information29 => p_information29,
2874 p_information30 => p_information30,
2875 p_object_version_number => l_object_version_number,
2876 p_check_accrual_ff => p_check_accrual_ff
2877 );
2878
2879 hr_utility.set_location(l_proc, 180);
2880
2881 if p_pto_input_value_id <> hr_api.g_number
2882 and p_pto_input_value_id <> pay_pap_shd.g_old_rec.pto_input_value_id then
2883 --
2884 for l_ncr_rec in c_get_ncr(pay_pap_shd.g_old_rec.pto_input_value_id) loop
2885 --
2886 pay_ncr_api.delete_pay_net_calc_rule
2887 (p_net_calculation_rule_id => l_ncr_rec.net_calculation_rule_id,
2888 p_object_version_number => l_ncr_rec.object_version_number
2889 );
2890 --
2891 end loop;
2892
2893 --
2894 -- create a new net calculation rule for the new pto input value if one
2895 -- does not already exist
2896 --
2897 open c_get_ncr(p_pto_input_value_id);
2898 fetch c_get_ncr into l_rec;
2899
2900 hr_utility.set_location(l_proc, 190);
2901
2902 if c_get_ncr%notfound then
2903 --
2904 hr_utility.set_location(l_proc, 200);
2905
2906 pay_ncr_api.create_pay_net_calc_rule(
2907 p_net_calculation_rule_id => l_net_calculation_rule_id,
2908 p_accrual_plan_id => p_accrual_plan_id,
2909 p_business_group_id => l_plan_rec.business_group_id,
2910 p_input_value_id => p_pto_input_value_id,
2911 p_add_or_subtract => '-1',
2912 p_date_input_value_id => null,
2913 p_object_version_number => l_dummy
2914 );
2915
2916 hr_utility.set_location(l_proc, 210);
2917
2918 end if;
2919
2920 close c_get_ncr;
2921 --
2922 end if;
2923
2924 hr_utility.set_location(l_proc, 210);
2925 --
2926 -- Call After Process User Hook
2927 --
2928 begin
2929 hr_accrual_plan_bk2.update_accrual_plan_a
2930 (p_accrual_plan_id => p_accrual_plan_id
2931 ,p_pto_input_value_id => p_pto_input_value_id
2932 ,p_accrual_category => p_accrual_category
2933 ,p_accrual_start => p_accrual_start
2934 ,p_ineligible_period_length => p_ineligible_period_length
2935 ,p_ineligible_period_type => p_ineligible_period_type
2936 ,p_accrual_formula_id => p_accrual_formula_id
2937 ,p_co_formula_id => p_co_formula_id
2938 ,p_description => p_description
2939 ,p_ineligibility_formula_id => p_ineligibility_formula_id
2940 ,p_payroll_formula_id => p_payroll_formula_id
2941 ,p_defined_balance_id => l_plan_rec.defined_balance_id
2942 ,p_balance_dimension_id => p_balance_dimension_id
2943 ,p_tagging_element_type_id => l_plan_rec.tagging_element_type_id
2944 ,p_balance_element_type_id => l_plan_rec.balance_element_type_id
2945 ,p_object_version_number => l_object_version_number
2946 ,p_information_category => p_information_category
2947 ,p_information1 => p_information1
2948 ,p_information2 => p_information2
2949 ,p_information3 => p_information3
2950 ,p_information4 => p_information4
2951 ,p_information5 => p_information5
2952 ,p_information6 => p_information6
2953 ,p_information7 => p_information7
2954 ,p_information8 => p_information8
2955 ,p_information9 => p_information9
2956 ,p_information10 => p_information10
2957 ,p_information11 => p_information11
2958 ,p_information12 => p_information12
2959 ,p_information13 => p_information13
2960 ,p_information14 => p_information14
2961 ,p_information15 => p_information15
2962 ,p_information16 => p_information16
2963 ,p_information17 => p_information17
2964 ,p_information18 => p_information18
2965 ,p_information19 => p_information19
2966 ,p_information20 => p_information20
2967 ,p_information21 => p_information21
2968 ,p_information22 => p_information22
2969 ,p_information23 => p_information23
2970 ,p_information24 => p_information24
2971 ,p_information25 => p_information25
2972 ,p_information26 => p_information26
2973 ,p_information27 => p_information27
2974 ,p_information28 => p_information28
2975 ,p_information29 => p_information29
2976 ,p_information30 => p_information30
2977 );
2978 exception
2979 when hr_api.cannot_find_prog_unit then
2980 hr_api.cannot_find_prog_unit_error
2981 (p_module_name => 'update_accrual_plan'
2982 ,p_hook_type => 'AP'
2983 );
2984 end;
2985
2986 hr_utility.set_location(l_proc, 220);
2987
2988 --
2989 -- When in validation only mode raise the Validate_Enabled exception
2990 --
2991 if p_validate then
2992 raise hr_api.validate_enabled;
2993 end if;
2994 --
2995 -- Set all output arguments
2996 --
2997
2998 p_object_version_number := l_object_version_number;
2999 p_payroll_formula_id := l_plan_rec.payroll_formula_id;
3000 p_defined_balance_id := l_plan_rec.defined_balance_id;
3001 p_tagging_element_type_id := l_plan_rec.tagging_element_type_id;
3002 p_balance_element_type_id := l_plan_rec.balance_element_type_id;
3003
3004 hr_utility.trace(' ');
3005 hr_utility.trace(' --------------------------------'||
3006 '---------------------------------');
3007 hr_utility.trace(' IN OUT NOCOPY / OUT NOCOPY PARAMETER '||
3008 ' VALUE');
3009 hr_utility.trace(' --------------------------------'||
3010 '+--------------------------------');
3011 hr_utility.trace(' p_object_version_number '||
3012 to_char(p_object_version_number));
3013 hr_utility.trace(' p_payroll_formula_id '||
3014 to_char(p_payroll_formula_id));
3015 hr_utility.trace(' p_defined_balance_id '||
3016 to_char(p_defined_balance_id));
3017 hr_utility.trace(' p_balance_element_type_id '||
3018 to_char(p_balance_element_type_id));
3019 hr_utility.trace(' p_tagging_element_type_id '||
3020 to_char(p_tagging_element_type_id));
3021 IF p_check_accrual_ff THEN
3022 hr_utility.trace(' p_check_accrual_ff '||
3023 'TRUE');
3024 ELSE
3025 hr_utility.trace(' p_check_accrual_ff '||
3026 'FALSE');
3027 END IF;
3028 hr_utility.trace(' --------------------------------'||
3029 '---------------------------------');
3030 hr_utility.trace(' ');
3031
3032 hr_utility.set_location(' Leaving:'||l_proc, 230);
3033
3034 EXCEPTION
3035
3036 WHEN hr_api.validate_enabled THEN
3037
3038 --
3039 -- As the Validate_Enabled exception has been raised
3040 -- we must rollback to the savepoint
3041 --
3042 ROLLBACK TO update_accrual_plan;
3043 --
3044 -- Only set output warning arguments
3045 -- (Any key or derived arguments must be set to null
3046 -- when validation only mode is being used.)
3047 --
3048 p_object_version_number := null;
3049 p_payroll_formula_id := null;
3050 p_defined_balance_id := null;
3051 p_tagging_element_type_id := null;
3052 p_balance_element_type_id := null;
3053
3054 hr_utility.set_location(' Leaving:'||l_proc, 240);
3055
3056 WHEN OTHERS THEN
3057 --
3058 -- A validation or unexpected error has occured
3059 --
3060 ROLLBACK to update_accrual_plan;
3061 -- Reset IN OUT parameters and set OUT parameters.
3062 p_object_version_number := l_temp_ovn;
3063 p_payroll_formula_id := null;
3064 p_defined_balance_id := null;
3065 p_balance_element_type_id := null;
3066 p_tagging_element_type_id := null;
3067 p_check_accrual_ff := null;
3068 hr_utility.set_location(' Leaving:'||l_proc, 250);
3069
3070 RAISE;
3071
3072 END update_accrual_plan;
3073 --
3074 -- ----------------------------------------------------------------------------
3075 -- |--------------------------< delete_accrual_plan >-------------------------|
3076 -- ----------------------------------------------------------------------------
3077 --
3078 procedure delete_accrual_plan
3079 (p_validate in boolean default false
3080 ,p_effective_date in date
3081 ,p_accrual_plan_id in number
3082 ,p_accrual_plan_element_type_id in number
3083 ,p_co_element_type_id in number
3084 ,p_residual_element_type_id in number
3085 ,p_balance_element_type_id in number
3086 ,p_tagging_element_type_id in number
3087 ,p_object_version_number in number
3088 ) is
3089 --
3090 -- Declare cursors and local variables
3091 --
3092
3093 cursor c_get_ncr(p_accrual_plan_id number) is
3094 select net_calculation_rule_id,
3095 object_version_number
3096 from pay_net_calculation_rules
3097 where accrual_plan_id = p_accrual_plan_id;
3098
3099 l_proc varchar2(72);
3100 l_effective_start_date date;
3101 l_effective_end_date date;
3102 l_balance_element_type_id number;
3103
3104 begin
3105 l_proc := g_package||'delete_accrual_plan';
3106 hr_utility.set_location('Entering:'|| l_proc, 10);
3107 --
3108 -- Issue a savepoint
3109 --
3110 savepoint delete_accrual_plan;
3111 hr_utility.set_location(l_proc, 20);
3112
3113 --
3114 -- Call Before Process User Hook
3115 --
3116 begin
3117 hr_accrual_plan_bk3.delete_accrual_plan_b
3118 (p_effective_date => p_effective_date
3119 ,p_accrual_plan_id => p_accrual_plan_id
3120 ,p_accrual_plan_element_type_id => p_accrual_plan_element_type_id
3121 ,p_co_element_type_id => p_co_element_type_id
3122 ,p_residual_element_type_id => p_residual_element_type_id
3123 ,p_balance_element_type_id => p_balance_element_type_id
3124 ,p_tagging_element_type_id => p_tagging_element_type_id
3125 ,p_object_version_number => p_object_version_number
3126 );
3127 exception
3128 when hr_api.cannot_find_prog_unit then
3129 hr_api.cannot_find_prog_unit_error
3130 (p_module_name => 'delete_accrual_plan'
3131 ,p_hook_type => 'BP'
3132 );
3133 end;
3134
3135 hr_utility.set_location(l_proc, 30);
3136 --
3137 -- Validation in addition to Row Handlers
3138 --
3139
3140 hr_utility.set_location(l_proc, 40);
3141 --
3142 -- Process Logic
3143 --
3144
3145 l_effective_start_date := hr_general.start_of_time;
3146 l_effective_end_date := hr_general.end_of_time;
3147
3148 --
3149 -- delete the accrual bands
3150 --
3151 delete from pay_accrual_bands
3152 where accrual_plan_id = p_accrual_plan_id;
3153
3154
3155 --
3156 -- delete the net calculation rules
3157 --
3158 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 41);
3159 for l_ncr in c_get_ncr(p_accrual_plan_id) loop
3160 --
3161 pay_ncr_api.delete_pay_net_calc_rule (
3162 p_net_calculation_rule_id => l_ncr.net_calculation_rule_id,
3163 p_object_version_number => l_ncr.object_version_number
3164 );
3165 --
3166 end loop;
3167
3168 --
3169 -- delete the element types created for the plan
3170 --
3171 -- first the accrual plan element type...
3172 --
3173 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 42);
3174 hr_elements.chk_del_element_type (
3175 'ZAP',
3176 p_accrual_plan_element_type_id,
3177 '',
3178 p_effective_date,
3179 l_effective_start_date,
3180 l_effective_end_date);
3181
3182 --
3183 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 43);
3184 hr_elements.del_3p_element_type (
3185 p_accrual_plan_element_type_id,
3186 'ZAP',
3187 p_effective_date,
3188 l_effective_start_date,
3189 l_effective_end_date,
3190 '');
3191
3192 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',44);
3193 delete from pay_element_types_f
3194 where element_type_id = p_accrual_plan_element_type_id;
3195
3196 --
3197 -- ...then the carried over element type...
3198 --
3199
3200 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 45);
3201 hr_elements.chk_del_element_type (
3202 'ZAP',
3203 p_co_element_type_id,
3204 '',
3205 p_effective_date,
3206 l_effective_start_date,
3207 l_effective_end_date);
3208
3209 --
3210 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 46);
3211 hr_elements.del_3p_element_type (
3212 p_co_element_type_id,
3213 'ZAP',
3214 p_effective_date,
3215 l_effective_start_date,
3216 l_effective_end_date,
3217 '');
3218
3219 --
3220 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',47);
3221 hr_utility.trace('DELETE ELEMENT: '||to_char(p_co_element_type_id));
3222 delete from pay_element_types_f
3223 where element_type_id = p_co_element_type_id;
3224
3225 --
3226 -- ...then the residual element type.
3227 --
3228 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 48);
3229
3230 hr_elements.chk_del_element_type (
3231 'ZAP',
3232 p_residual_element_type_id,
3233 '',
3234 p_effective_date,
3235 l_effective_start_date,
3236 l_effective_end_date);
3237
3238 --
3239 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 49);
3240 hr_elements.del_3p_element_type (
3241 p_residual_element_type_id,
3242 'ZAP',
3243 p_effective_date,
3244 l_effective_start_date,
3245 l_effective_end_date,
3246 '');
3247
3248 --
3249 delete from pay_element_types_f
3250 where element_type_id = p_residual_element_type_id;
3251
3252
3253 --
3254 -- ...then the payroll balance element type.
3255 --
3256 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 50);
3257
3258 hr_elements.chk_del_element_type (
3259 'ZAP',
3260 p_balance_element_type_id,
3261 '',
3262 p_effective_date,
3263 l_effective_start_date,
3264 l_effective_end_date);
3265
3266 --
3267 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 51);
3268 hr_elements.del_3p_element_type (
3269 p_balance_element_type_id,
3270 'ZAP',
3271 p_effective_date,
3272 l_effective_start_date,
3273 l_effective_end_date,
3274 '');
3275
3276 --
3277 delete from pay_element_types_f
3278 where element_type_id = p_balance_element_type_id;
3279
3280 --
3281 -- ...then the tagging element type.
3282 --
3283 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 50);
3284
3285 hr_elements.chk_del_element_type (
3286 'ZAP',
3287 p_tagging_element_type_id,
3288 '',
3289 p_effective_date,
3290 l_effective_start_date,
3291 l_effective_end_date);
3292
3293 --
3294 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 51);
3295 hr_elements.del_3p_element_type (
3296 p_tagging_element_type_id,
3297 'ZAP',
3298 p_effective_date,
3299 l_effective_start_date,
3300 l_effective_end_date,
3301 '');
3302
3303 --
3304 delete from pay_element_types_f
3305 where element_type_id = p_tagging_element_type_id;
3306
3307 --
3308 -- Finally, delete the accrual plan record itself
3309 --
3310 pay_pap_del.del (
3311 p_accrual_plan_id => p_accrual_plan_id,
3312 p_object_version_number => p_object_version_number
3313 );
3314
3315 hr_utility.set_location(l_proc, 60);
3316 --
3317 -- Call After Process User Hook
3318 --
3319 begin
3320 hr_accrual_plan_bk3.delete_accrual_plan_a
3321 (p_effective_date => p_effective_date
3322 ,p_accrual_plan_id => p_accrual_plan_id
3323 ,p_accrual_plan_element_type_id => p_accrual_plan_element_type_id
3324 ,p_co_element_type_id => p_co_element_type_id
3325 ,p_residual_element_type_id => p_residual_element_type_id
3326 ,p_balance_element_type_id => p_balance_element_type_id
3327 ,p_tagging_element_type_id => p_tagging_element_type_id
3328 ,p_object_version_number => p_object_version_number
3329 );
3330 exception
3331 when hr_api.cannot_find_prog_unit then
3332 hr_api.cannot_find_prog_unit_error
3333 (p_module_name => 'delete_accrual_plan'
3334 ,p_hook_type => 'AP'
3335 );
3336 end;
3337 hr_utility.set_location(l_proc, 65);
3338
3339 --
3340 -- When in validation only mode raise the Validate_Enabled exception
3341 --
3342 if p_validate then
3343 raise hr_api.validate_enabled;
3344 end if;
3345
3346 --
3347 hr_utility.set_location(' Leaving:'||l_proc, 70);
3348 exception
3349 when hr_api.validate_enabled then
3350 --
3351 -- As the Validate_Enabled exception has been raised
3352 -- we must rollback to the savepoint
3353 --
3354 rollback to delete_accrual_plan;
3355 --
3356 -- Only set output warning arguments
3357 -- (Any key or derived arguments must be set to null
3358 -- when validation only mode is being used.)
3359 --
3360
3361 hr_utility.set_location(' Leaving:'||l_proc, 80);
3362 when others then
3363 --
3364 -- A validation or unexpected error has occured
3365 --
3366 rollback to delete_accrual_plan;
3367 hr_utility.set_location(' Leaving:'||l_proc, 90);
3368 raise;
3369 end delete_accrual_plan;
3370 --
3371 end hr_accrual_plan_api;