1 PACKAGE BODY PAY_ACCRUAL_PLANS_PKG as
2 /* $Header: pyappap.pkb 115.3 99/08/24 06:49:02 porting shi $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
6 X_Accrual_Plan_Id IN OUT NUMBER,
7 X_Business_Group_Id NUMBER,
8 X_Accrual_Plan_Element_Type_Id NUMBER,
9 X_Pto_Input_Value_Id NUMBER,
10 X_Co_Input_Value_Id NUMBER,
11 X_Residual_Input_Value_Id NUMBER,
12 X_Accrual_Category VARCHAR2,
13 X_Accrual_Plan_Name VARCHAR2,
14 X_Accrual_Start VARCHAR2,
15 X_Accrual_Units_Of_Measure VARCHAR2,
16 X_Ineligible_Period_Length NUMBER,
17 X_Ineligible_Period_Type VARCHAR2
18 ) IS
19 CURSOR C IS SELECT rowid FROM PAY_ACCRUAL_PLANS
20 WHERE accrual_plan_id = X_Accrual_Plan_Id;
21
22 CURSOR C2 IS SELECT pay_accrual_plans_s.nextval FROM sys.dual;
23 BEGIN
24
25 if (X_Accrual_Plan_Id is NULL) then
26 OPEN C2;
27 FETCH C2 INTO X_Accrual_Plan_Id;
28 CLOSE C2;
29 end if;
30 INSERT INTO PAY_ACCRUAL_PLANS(
31 accrual_plan_id,
32 business_group_id,
33 accrual_plan_element_type_id,
34 pto_input_value_id,
35 co_input_value_id,
36 residual_input_value_id,
37 accrual_category,
38 accrual_plan_name,
39 accrual_start,
40 accrual_units_of_measure,
41 ineligible_period_length,
42 ineligible_period_type
43 ) VALUES (
44 X_Accrual_Plan_Id,
45 X_Business_Group_Id,
46 X_Accrual_Plan_Element_Type_Id,
47 X_Pto_Input_Value_Id,
48 X_Co_Input_Value_Id,
49 X_Residual_Input_Value_Id,
50 X_Accrual_Category,
51 X_Accrual_Plan_Name,
52 X_Accrual_Start,
53 X_Accrual_Units_Of_Measure,
54 X_Ineligible_Period_Length,
55 X_Ineligible_Period_Type
56 );
57
58 OPEN C;
59 FETCH C INTO X_Rowid;
60 if (C%NOTFOUND) then
61 CLOSE C;
62 RAISE NO_DATA_FOUND;
63 end if;
64 CLOSE C;
65 END Insert_Row;
66 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
67 X_Accrual_Plan_Id NUMBER,
68 X_Business_Group_Id NUMBER,
69 X_Accrual_Plan_Element_Type_Id NUMBER,
70 X_Pto_Input_Value_Id NUMBER,
71 X_Co_Input_Value_Id NUMBER,
72 X_Residual_Input_Value_Id NUMBER,
73 X_Accrual_Category VARCHAR2,
74 X_Accrual_Plan_Name VARCHAR2,
75 X_Accrual_Start VARCHAR2,
76 X_Accrual_Units_Of_Measure VARCHAR2,
77 X_Ineligible_Period_Length NUMBER,
78 X_Ineligible_Period_Type VARCHAR2
79 ) IS
80 CURSOR C IS
81 SELECT *
82 FROM PAY_ACCRUAL_PLANS
83 WHERE rowid = X_Rowid
84 FOR UPDATE of Accrual_Plan_Id NOWAIT;
85 Recinfo C%ROWTYPE;
86 BEGIN
87 OPEN C;
88 FETCH C INTO Recinfo;
89 if (C%NOTFOUND) then
90 CLOSE C;
91 RAISE NO_DATA_FOUND;
92 end if;
93 CLOSE C;
94 -- Added by RMAMGAIN
95 --
96 Recinfo.accrual_category := RTRIM(Recinfo.accrual_category);
97 Recinfo.accrual_plan_name := RTRIM(Recinfo.accrual_plan_name);
98 Recinfo.accrual_start := RTRIM(Recinfo.accrual_start);
99 Recinfo.accrual_units_of_measure := RTRIM(Recinfo.accrual_units_of_measure);
100 Recinfo.ineligible_period_type := RTRIM(Recinfo.ineligible_period_type);
101 --
102 -- END
103 if (
104 ( (Recinfo.accrual_plan_id = X_Accrual_Plan_Id)
105 OR ( (Recinfo.accrual_plan_id IS NULL)
106 AND (X_Accrual_Plan_Id IS NULL)))
107 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
108 OR ( (Recinfo.business_group_id IS NULL)
109 AND (X_Business_Group_Id IS NULL)))
110 AND ( (Recinfo.accrual_plan_element_type_id = X_Accrual_Plan_Element_Type_Id)
111 OR ( (Recinfo.accrual_plan_element_type_id IS NULL)
112 AND (X_Accrual_Plan_Element_Type_Id IS NULL)))
113 AND ( (Recinfo.pto_input_value_id = X_Pto_Input_Value_Id)
114 OR ( (Recinfo.pto_input_value_id IS NULL)
115 AND (X_Pto_Input_Value_Id IS NULL)))
116 AND ( (Recinfo.co_input_value_id = X_Co_Input_Value_Id)
117 OR ( (Recinfo.co_input_value_id IS NULL)
118 AND (X_Co_Input_Value_Id IS NULL)))
119 AND ( (Recinfo.residual_input_value_id = X_Residual_Input_Value_Id)
120 OR ( (Recinfo.residual_input_value_id IS NULL)
121 AND (X_Residual_Input_Value_Id IS NULL)))
122 AND ( (Recinfo.accrual_category = X_Accrual_Category)
123 OR ( (Recinfo.accrual_category IS NULL)
124 AND (X_Accrual_Category IS NULL)))
125 AND ( (Recinfo.accrual_plan_name = X_Accrual_Plan_Name)
126 OR ( (Recinfo.accrual_plan_name IS NULL)
127 AND (X_Accrual_Plan_Name IS NULL)))
128 AND ( (Recinfo.accrual_start = X_Accrual_Start)
129 OR ( (Recinfo.accrual_start IS NULL)
130 AND (X_Accrual_Start IS NULL)))
131 AND ( (Recinfo.accrual_units_of_measure = X_Accrual_Units_Of_Measure)
132 OR ( (Recinfo.accrual_units_of_measure IS NULL)
133 AND (X_Accrual_Units_Of_Measure IS NULL)))
134 AND ( (Recinfo.ineligible_period_length = X_Ineligible_Period_Length)
135 OR ( (Recinfo.ineligible_period_length IS NULL)
136 AND (X_Ineligible_Period_Length IS NULL)))
137 AND ( (Recinfo.ineligible_period_type = X_Ineligible_Period_Type)
138 OR ( (Recinfo.ineligible_period_type IS NULL)
139 AND (X_Ineligible_Period_Type IS NULL)))
140 ) then
141 return;
142 else
143 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 end if;
146 END Lock_Row;
147
148 PROCEDURE Update_Row(X_Rowid VARCHAR2,
149 X_Accrual_Plan_Id NUMBER,
150 X_Business_Group_Id NUMBER,
151 X_Accrual_Plan_Element_Type_Id NUMBER,
152 X_Pto_Input_Value_Id NUMBER,
153 X_Co_Input_Value_Id NUMBER,
154 X_Residual_Input_Value_Id NUMBER,
155 X_Accrual_Category VARCHAR2,
156 X_Accrual_Plan_Name VARCHAR2,
157 X_Accrual_Start VARCHAR2,
158 X_Accrual_Units_Of_Measure VARCHAR2,
159 X_Ineligible_Period_Length NUMBER,
160 X_Ineligible_Period_Type VARCHAR2
161 ) IS
162 BEGIN
163 UPDATE PAY_ACCRUAL_PLANS
164 SET
165 accrual_plan_id = X_Accrual_Plan_Id,
166 business_group_id = X_Business_Group_Id,
167 accrual_plan_element_type_id = X_Accrual_Plan_Element_Type_Id,
168 pto_input_value_id = X_Pto_Input_Value_Id,
169 co_input_value_id = X_Co_Input_Value_Id,
170 residual_input_value_id = X_Residual_Input_Value_Id,
171 accrual_category = X_Accrual_Category,
172 accrual_plan_name = X_Accrual_Plan_Name,
173 accrual_start = X_Accrual_Start,
174 accrual_units_of_measure = X_Accrual_Units_Of_Measure,
175 ineligible_period_length = X_Ineligible_Period_Length,
176 ineligible_period_type = X_Ineligible_Period_Type
177 WHERE rowid = X_rowid;
178
179 if (SQL%NOTFOUND) then
180 RAISE NO_DATA_FOUND;
181 end if;
182
183 END Update_Row;
184
185 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
186 BEGIN
187 DELETE FROM PAY_ACCRUAL_PLANS
188 WHERE rowid = X_Rowid;
189
190 if (SQL%NOTFOUND) then
191 RAISE NO_DATA_FOUND;
192 end if;
193 END Delete_Row;
194 --
195 --
196 --
197 /*
198 ****************************************************************************
199 NAME chk_plan_name
200
201 DESCRIPTION validates the plan name - the name must not be duplicated within
202 the accrual plans table; the name must not cause any clashes
203 with the element type names that the plan will create
204
205 NOTES none
206 ****************************************************************************
207 */
208 PROCEDURE chk_plan_name(p_plan_name IN varchar2,
209 p_accrual_plan_id IN number) IS
210 --
211 l_comb_exists VARCHAR2(2);
212 --
213 CURSOR dup_rec1 IS
214 select 'Y'
215 from PAY_ACCRUAL_PLANS
216 where upper(ACCRUAL_PLAN_NAME) = upper(p_plan_name)
217 and ((p_accrual_plan_id is null)
218 or
219 (p_accrual_plan_id is not null
220 and
221 ACCRUAL_PLAN_ID <> p_accrual_plan_id));
222 --
223 CURSOR dup_rec2 IS
224 select 'Y'
225 from PAY_ELEMENT_TYPES_F
226 where upper(ELEMENT_NAME) in
227 (upper(p_plan_name),
228 'RESIDUAL ' || upper(p_plan_name),
229 'CARRIED OVER ' || upper(p_plan_name));
230 --
231 BEGIN
232 --
233 l_comb_exists := 'N';
234 --
235 -- determine whether the plan name has been duplicated - if a record is found
236 -- then the local variable will be set to 'Y', otherwise it will remain 'N'
237 --
238 OPEN dup_rec1;
239 FETCH dup_rec1 INTO l_comb_exists;
240 CLOSE dup_rec1;
241 --
242 -- go ahead and check the value of the local variable
243 --
244 IF (l_comb_exists = 'Y') THEN
245 hr_utility.set_message(801, 'HR_13163_PTO_DUP_PLAN_NAME');
246 hr_utility.raise_error;
247 END IF;
248 --
249 --
250 l_comb_exists := 'N';
251 --
252 -- determine whether any element types exist which would cause a clash with the
253 -- plan's elements that it will create
254 --
258 --
255 OPEN dup_rec2;
256 FETCH dup_rec2 INTO l_comb_exists;
257 CLOSE dup_rec2;
259 IF (l_comb_exists = 'Y') THEN
260 hr_utility.set_message(801, 'HR_13164_PTO_INVALID_PLAN_NAME');
261 hr_utility.raise_error;
262 END IF;
263 --
264 END chk_plan_name;
265 --
266 --
267 --
268 /*
269 ****************************************************************************
270 NAME insert_validation
271
272 DESCRIPTION performs all of the validation required at insert time
273
274 NOTES none
275 ****************************************************************************
276 */
277 PROCEDURE insert_validation(p_plan_name IN varchar2,
278 p_accrual_plan_id IN number) IS
279 --
280 BEGIN
281 --
282 PAY_ACCRUAL_PLANS_PKG.CHK_PLAN_NAME(p_plan_name,
283 p_accrual_plan_id);
284 END insert_validation;
285 --
286 --
287 --
288 /*
289 ****************************************************************************
290 NAME create_element
291
292 DESCRIPTION calls the function PAY_DB_PAY_SETUP.CREATE_ELEMENT,The sole
293 reason for this is to cut down on space and reduce the margin
294 for errors in the call, apssing only the things that change.
295
296 NOTES anticipate the only use for this is to be called from the
297 pre_insert_actions routine in this package.
298
299 Added p_legislation_code and p_currency_code to the param list
300 passed in and used them instead of the hard coded US and USD
301 in the calls to PAY_DB_PAY_SETUP.create_element. RMF 27-Nov-95.
302 ****************************************************************************
303 */
304 FUNCTION create_element(p_element_name IN varchar2,
305 p_element_description IN varchar2,
306 p_processing_type IN varchar2,
307 p_bg_name IN varchar2,
308 p_classification_name IN varchar2,
309 p_legislation_code IN varchar2,
310 p_currency_code IN varchar2,
311 p_post_termination_rule IN varchar2)
312 RETURN number IS
313 --
314 l_effective_start_date date;
315 l_effective_end_date date;
316 l_element_type_id number;
317 BEGIN
318 --
319 hr_utility.set_location('pay_accrual_plans_pkg.create_element',1);
320 l_effective_start_date := hr_general.start_of_time;
321 l_effective_end_date := hr_general.end_of_time;
322 --
323 l_element_type_id := PAY_DB_PAY_SETUP.create_element
324 (p_element_name => p_element_name,
325 p_description => p_element_description,
326 p_reporting_name => '',
327 p_classification_name => p_classification_name,
328 p_input_currency_code => p_currency_code,
329 p_output_currency_code => p_currency_code,
330 p_processing_type => p_processing_type,
331 p_mult_entries_allowed => 'N',
332 p_formula_id => '',
333 p_processing_priority => '',
334 p_closed_for_entry_flag => 'N',
335 p_standard_link_flag => 'N',
336 p_qual_length_of_service => '',
337 p_qual_units => '',
338 p_qual_age => '',
339 p_process_in_run_flag => 'Y',
340 p_post_termination_rule => p_post_termination_rule,
341 p_indirect_only_flag => 'N',
342 p_adjustment_only_flag => 'N',
343 p_add_entry_allowed_flag => 'N',
344 p_multiply_value_flag => 'N',
345 p_effective_start_date => l_effective_start_date,
346 p_effective_end_date => l_effective_end_date,
347 p_business_group_name => p_bg_name,
348 p_legislation_code => p_legislation_code,
349 p_legislation_subgroup => '');
350 --
351 return l_element_type_id;
352 --
353 end create_element;
354 --
355 --
356 --
357 /*
358 ****************************************************************************
359 NAME create_input_value
360
361 DESCRIPTION performs all that is required to create an input value -
362
363 create the input value
364
365 validate the values
366
367 create the DBI'd, balance feeds, etc
368
369 NOTES anticipate the only use for this is to be called from the
370 pre_insert_actions routine in this package.
371
372 Added p_legislation_code to the param list passed in and used
373 it instead of the hard coded US in the calls below.
374 RMF 27-Nov-95.
375 ****************************************************************************
376 */
377 FUNCTION create_input_value(p_element_name IN varchar2,
378 p_input_value_name IN varchar2,
379 p_uom_code IN varchar2,
380 p_bg_name IN varchar2,
381 p_element_type_id IN number,
382 p_primary_classification_id IN number,
383 p_business_group_id IN number,
384 p_recurring_flag IN varchar2,
385 p_legislation_code IN varchar2,
386 p_classification_type IN varchar2)
387 RETURN number IS
388 --
389 l_effective_start_date date;
390 l_effective_end_date date;
391 l_input_value_id number;
392 --
393 BEGIN
394 --
398 --
395 hr_utility.set_location('pay_accrual_plans_pkg.create_input_value',1);
396 l_effective_start_date := hr_general.start_of_time;
397 l_effective_end_date := hr_general.end_of_time;
399 l_input_value_id := pay_db_pay_setup.create_input_value(
400 p_element_name => p_element_name,
401 p_name => p_input_value_name,
402 p_uom => '',
403 p_uom_code => p_uom_code,
404 p_mandatory_flag => 'N',
405 p_generate_db_item_flag => 'Y',
406 p_default_value => '',
407 p_min_value => '',
408 p_max_value => '',
409 p_warning_or_error => '',
410 p_lookup_type => '',
411 p_formula_id => '',
412 p_hot_default_flag => 'N',
413 p_display_sequence => 1,
414 p_business_group_name => p_bg_name,
415 p_effective_start_date => l_effective_start_date,
416 p_effective_end_date => l_effective_end_date);
417 --
418 hr_input_values.chk_input_value(
419 p_element_type_id => p_element_type_id,
420 p_legislation_code => p_legislation_code,
421 p_val_start_date => l_effective_start_date,
422 p_val_end_date => l_effective_end_date,
423 p_insert_update_flag => 'INSERT',
424 p_input_value_id => l_input_value_id,
425 p_rowid => '',
426 p_recurring_flag => p_recurring_flag,
427 p_mandatory_flag => 'N',
428 p_hot_default_flag => 'N',
429 p_standard_link_flag => 'N',
430 p_classification_type => p_classification_type,
431 p_name => p_input_value_name,
432 p_uom => p_uom_code,
433 p_min_value => '',
434 p_max_value => '',
435 p_default_value => '',
436 p_lookup_type => '',
437 p_formula_id => '',
438 p_generate_db_items_flag => 'Y',
439 p_warning_or_error => '');
440 --
441 hr_input_values.ins_3p_input_values(
442 p_val_start_date => l_effective_start_date,
443 p_val_end_date => l_effective_end_date,
444 p_element_type_id => p_element_type_id,
445 p_primary_classification_id => p_primary_classification_id,
446 p_input_value_id => l_input_value_id,
447 p_default_value => '',
448 p_max_value => '',
449 p_min_value => '',
450 p_warning_or_error_flag => '',
451 p_input_value_name => p_input_value_name,
452 p_db_items_flag => 'Y',
453 p_costable_type => '',
454 p_hot_default_flag => 'N',
455 p_business_group_id => p_business_group_id,
456 p_legislation_code => p_legislation_code,
457 p_startup_mode => '');
458 --
459 return l_input_value_id;
460 --
461 end create_input_value;
462 --
463 --
464 --
465 /*
466 ****************************************************************************
467 NAME pre_insert_actions
468
469 DESCRIPTION handles all of the pre-insert actions for pay_accrual_plans; at
470 the moment all it does is creates the element types and input
471 values, but it may need to do more in the future (there's nothing
472 wrong with anticipating expansion in functionality)
473
474 NOTES none
475 ****************************************************************************
476 */
477 PROCEDURE pre_insert_actions(p_plan_name IN varchar2,
478 p_bg_name IN varchar2,
479 p_plan_uom IN varchar2,
480 p_business_group_id IN number,
481 p_accrual_plan_element_type_id OUT number,
482 p_co_input_value_id OUT number,
483 p_co_element_type_id OUT number,
484 p_residual_input_value_id OUT number,
485 p_residual_element_type_id OUT number) IS
486 --
487 l_element_type_id number;
488 l_element_name varchar2(80);
489 l_element_description varchar2(240);
490 l_classification_name varchar2(240);
491 l_post_termination_rule varchar2(240);
492 l_input_value_id number;
493 l_input_value_name varchar2(30);
494 l_uom_code varchar2(80);
495 l_primary_classification_id number;
496 l_classification_type varchar2(2);
497 l_bg_name varchar2(80);
498 l_leg_code varchar2(150);
499 l_curr_code varchar2(150);
500 --
501 -- cursor to get a primary classification (i.e. one where the
502 -- parent_classification_id is null) for the legislation or business
503 -- group. Get the 'Information' classification if there is one,
504 -- failing that get a non-payments one, otherwise just get the
505 -- first one retrieved. Note that both US and GB legislations have
506 -- a startup Information classification. RMF 27-Nov-95.
507 --
508 cursor class_name is
509 select classification_name
510 from pay_element_classifications
511 where (business_group_id = p_business_group_id
512 or legislation_code = l_leg_code)
513 and parent_classification_id is null
514 order by decode (classification_name, 'Information', 1, 2),
518 hr_utility.set_location
515 nvl (non_payments_flag, 'X') desc, classification_name;
516 --
517 BEGIN
519 ('pay_accrual_plans_pkg.pre_insert_actions',1);
520 --
521 -- create the accrual plan element type and input value...
522 --
523 -- Added by RMAMGAIN
524 -- p_bg_name is a in params to this proc. but it is null and hence
525 -- element creation is failing because it thinks it has to create a
526 -- startup element. To avoid network traffic on the forms side we
527 -- can get BG name in this package and avoid changes to other forms
528 -- and package. Also in this proc all references to P_bg_name are
529 -- changed to l_bg_name.
530 --
531 -- Added the legislation code and currency code to the list of
532 -- columns returned by the following select. These are then used
533 -- in setting up the elements with the correct leg code and currency
534 -- code. RMF 27-Nov-95.
535 --
536 select name, legislation_code, currency_code
537 into l_bg_name, l_leg_code, l_curr_code
538 from per_business_groups
539 where business_group_id + 0 = p_business_group_id;
540 --
541 -- If this is a US legislation, use the classification name
542 -- 'PTO Accruals'. Otherwise, pick any classification, preferably a
543 -- non-payments one. RMF 27-Nov-95.
544 --
545 if l_leg_code = 'US' then
546 l_classification_name := 'PTO Accruals';
547 else
548 open class_name;
549 fetch class_name into l_classification_name;
550 close class_name;
551 end if;
552 --
553 -- VT #500299 06/05/97
554 --l_post_termination_rule := 'Final Close';
555 begin
556 select hl.meaning
557 into l_post_termination_rule
558 from hr_lookups hl
559 where hl.lookup_type='TERMINATION_RULE'
560 and hl.lookup_code='F';
561 exception
562 when no_data_found then
563 hr_utility.set_message(801,'HR_NO_F_TERM_RULE');
564 hr_utility.raise_error;
565 end;
566 --
567 l_element_name := p_plan_name;
568 l_element_description := 'Accrual plan for ' || l_element_name;
569 l_primary_classification_id := 37;
570 l_classification_type := 'N';
571 --
572 l_element_type_id := PAY_ACCRUAL_PLANS_PKG.CREATE_ELEMENT(
573 l_element_name,
574 l_element_description,
575 'R',
576 l_bg_name,
577 l_classification_name,
578 l_leg_code,
579 l_curr_code,
580 l_post_termination_rule);
581 --
585 l_uom_code := 'D';
582 p_accrual_plan_element_type_id := l_element_type_id;
583 --
584 l_input_value_name := 'Continuous Service Date';
586 --
587 l_input_value_id := PAY_ACCRUAL_PLANS_PKG.CREATE_INPUT_VALUE(
588 l_element_name,
589 l_input_value_name,
590 l_uom_code,
591 l_bg_name,
592 l_element_type_id,
593 l_primary_classification_id,
594 p_business_group_id,
595 'R',
596 l_leg_code,
597 l_classification_type);
598 --
599 --
600 -- now create the carried-over element type and input value...
601 --
602 --
603 -- set up input value names and units of measure
604 --
605 if p_plan_uom = 'D' then
606 l_input_value_name := 'Days';
607 l_uom_code := 'ND';
608 else
609 l_input_value_name := 'Hours';
610 l_uom_code := 'H_DECIMAL3';
611 end if;
612 --
613 l_element_name := substr('Carried Over ' || p_plan_name, 1, 80);
614 l_element_description :=
615 'Carried over entitlement for accrual plan ' || p_plan_name;
616 --
617 -- If this is a US legislation, use the classification name
618 -- 'Information'. Otherwise, stick with the classification retrieved
619 -- above. RMF 27-Nov-95.
620 --
621 if l_leg_code = 'US' then
622 l_classification_name := 'Information';
623 end if;
624 --
625 l_primary_classification_id := 40;
626 l_classification_type := 'Y';
627 --
628 l_element_type_id := PAY_ACCRUAL_PLANS_PKG.CREATE_ELEMENT(
629 l_element_name,
630 l_element_description,
631 'N',
632 l_bg_name,
633 l_classification_name,
634 l_leg_code,
635 l_curr_code,
636 l_post_termination_rule);
637 --
638 l_input_value_id := PAY_ACCRUAL_PLANS_PKG.CREATE_INPUT_VALUE(
639 l_element_name,
640 l_input_value_name,
641 l_uom_code,
642 l_bg_name,
643 l_element_type_id,
644 l_primary_classification_id,
645 p_business_group_id,
646 'N',
647 l_leg_code,
648 l_classification_type);
649 --
650 p_co_element_type_id := l_element_type_id;
651 p_co_input_value_id := l_input_value_id;
652 --
653 --
657 l_element_name := substr('Residual ' || p_plan_name, 1, 80);
654 -- and finally, Esther, the residual element type and input value.
655 --
656 --
658 l_element_description :=
659 'Residual entitlement for accrual plan ' || p_plan_name;
660 --
661 l_element_type_id := PAY_ACCRUAL_PLANS_PKG.CREATE_ELEMENT(
662 l_element_name,
663 l_element_description,
664 'N',
665 l_bg_name,
666 l_classification_name,
667 l_leg_code,
668 l_curr_code,
669 l_post_termination_rule);
670 --
671 l_input_value_id := PAY_ACCRUAL_PLANS_PKG.CREATE_INPUT_VALUE(
672 l_element_name,
673 l_input_value_name,
674 l_uom_code,
675 l_bg_name,
676 l_element_type_id,
677 l_primary_classification_id,
678 p_business_group_id,
679 'N',
680 l_leg_code,
681 l_classification_type);
682 --
683 p_residual_element_type_id := l_element_type_id;
684 p_residual_input_value_id := l_input_value_id;
685 --
686 --
687 END pre_insert_actions;
688 --
689 --
690 --
691 /*
692 ****************************************************************************
693 NAME post_insert_actions
694
695 DESCRIPTION handles all of the post-insert actions for pay_accrual_plans; at
696 the moment all it does is creates the default net calculation
697 rules for the plan.
698
699 04-AUG-95 hparicha 279860 Update Pay Value mand_flag
700 to 'X' - ie. not enterable
701 by user.
702 NOTES none
703 ****************************************************************************
704 */
705 PROCEDURE post_insert_actions(p_accrual_plan_id IN number,
706 p_business_group_id IN number,
707 p_pto_input_value_id IN number,
708 p_co_input_value_id IN number) IS
709 --
710 v_accrual_payval_id NUMBER(9);
711
712 BEGIN
713 --
714 -- insert the pto input value (always reduces entitlement)
715 --
716 hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',1);
717 insert into pay_net_calculation_rules(
718 net_calculation_rule_id,
719 accrual_plan_id,
720 business_group_id,
721 input_value_id,
722 add_or_subtract)
723 select
724 pay_net_calculation_rules_s.nextval,
725 p_accrual_plan_id,
726 p_business_group_id,
727 p_pto_input_value_id,
728 -1
729 from dual;
730 --
731 -- insert the carried over input value (always increases entitlement)
732 --
733 hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',2);
734 insert into pay_net_calculation_rules(
735 net_calculation_rule_id,
736 accrual_plan_id,
737 business_group_id,
738 input_value_id,
739 add_or_subtract)
740 select
741 pay_net_calculation_rules_s.nextval,
742 p_accrual_plan_id,
743 p_business_group_id,
744 p_co_input_value_id,
745 1
746 from dual;
747 --
748 hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',3);
749 --
750 -- The update below was originally written as a separate implicit SELECT
751 -- and UPDATE. Changed to a single UPDATE, because non-US accrual plans
752 -- create all element as non-payment, hence have no PAY INPUT VALUE. The
753 -- SELECT was failing with NO_DATA_FOUND. RMF 27-Nov-1995.
754 --
755 UPDATE pay_input_values_f
756 SET mandatory_flag = 'X'
757 WHERE input_value_id =
758 ( SELECT piv.input_value_id
759 FROM pay_input_values_f piv,
760 pay_accrual_plans pap,
761 hr_lookups hrl
762 WHERE pap.accrual_plan_id = p_accrual_plan_id
763 AND pap.accrual_plan_element_type_id = piv.element_type_id
764 AND piv.name = hrl.meaning
765 AND hrl.lookup_code = 'PAY VALUE'
766 AND hrl.lookup_type = 'NAME_TRANSLATIONS'
767 );
768
769 END post_insert_actions;
770 --
771 --
772 --
773 /*
774 ****************************************************************************
775 NAME update_validation
776
777 DESCRIPTION performs all of the validation required at update time
778
779 NOTES none
780 ****************************************************************************
781 */
782 PROCEDURE update_validation(p_plan_name IN varchar2,
783 p_old_plan_name IN varchar2,
784 p_accrual_plan_id IN number) IS
785 --
786 BEGIN
787 --
788 if p_plan_name <> p_old_plan_name then
789 PAY_ACCRUAL_PLANS_PKG.CHK_PLAN_NAME(p_plan_name,
790 p_accrual_plan_id);
791 end if;
792 --
793 END update_validation;
794 --
795 --
796 --
797 /*
798 ****************************************************************************
799 NAME post_update_actions
803
800
801 DESCRIPTION performs all of the actions required after changing a plan's
802 details -
804 changes the net calculation rule if the pto element has
805 changed
806
807 NOTES none
808 ****************************************************************************
809 */
810 PROCEDURE post_update_actions(p_accrual_plan_id IN number,
811 p_business_group_id IN number,
812 p_pto_input_value_id IN number,
813 p_old_pto_input_value_id IN number) IS
814 --
815 BEGIN
816 --
817 if p_pto_input_value_id <> p_old_pto_input_value_id then
818 --
819 -- delete the old pto input value from the net calculation rules
820 --
821 hr_utility.set_location('pay_accrual_plans_pkg.post_update_actions',1);
822 delete from pay_net_calculation_rules
823 where input_value_id = p_old_pto_input_value_id
824 and accrual_plan_id = p_accrual_plan_id;
825 --
826 -- create a new net calculation rule for the new pto input value if one
827 -- does not already exist
828 --
829 hr_utility.set_location('pay_accrual_plans_pkg.post_update_actions',2);
830 insert into pay_net_calculation_rules(
831 net_calculation_rule_id,
832 accrual_plan_id,
833 business_group_id,
834 input_value_id,
835 add_or_subtract)
836 select
837 pay_net_calculation_rules_s.nextval,
838 p_accrual_plan_id,
839 p_business_group_id,
840 p_pto_input_value_id,
841 -1
842 from dual
843 where not exists(
844 select 1
845 from pay_net_calculation_rules
846 where input_value_id = p_pto_input_value_id
847 and accrual_plan_id = p_accrual_plan_id);
848 end if;
849 --
850 END post_update_actions;
851 --
852 --
853 --
854 /*
855 ****************************************************************************
856 NAME pre_delete_actions
857
858 DESCRIPTION performs all of the actions required before deleting the plan -
859 delete all child accrual bands;
860 delete all child net calculation rules;
861 delete the element type created for the accrual plan;
862 delete the residual element type;
863 delete the carried over element type.
864
865 NOTES none
866 ****************************************************************************
867 */
868 PROCEDURE pre_delete_actions(p_accrual_plan_id IN number,
869 p_accrual_plan_element_type_id IN number,
870 p_co_element_type_id IN number,
871 p_residual_element_type_id IN number,
872 p_session_date IN date) IS
873 --
874 l_effective_start_date date;
875 l_effective_end_date date;
876 --
877 BEGIN
878 l_effective_start_date := hr_general.start_of_time;
879 l_effective_end_date := hr_general.end_of_time;
880 --
881 --
882 -- delete the accrual bands
883 --
884 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 1);
885 delete from pay_accrual_bands
886 where accrual_plan_id = p_accrual_plan_id;
887 --
888 -- delete the net calculation rules
889 --
890 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 2);
891 delete from pay_net_calculation_rules
892 where accrual_plan_id = p_accrual_plan_id;
893 --
894 -- delete the element types created for the plan
895 --
896 -- first the accrual plan element type...
897 --
898 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 3);
899 hr_elements.chk_del_element_type (
900 'ZAP',
901 p_accrual_plan_element_type_id,
902 '',
903 p_session_date,
904 l_effective_start_date,
905 l_effective_end_date);
906 --
907 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 4);
908 hr_elements.del_3p_element_type (
909 p_accrual_plan_element_type_id,
910 'ZAP',
911 p_session_date,
912 l_effective_start_date,
913 l_effective_end_date,
914 '');
915 --
916 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',5);
917 delete from pay_element_types_f
918 where element_type_id = p_accrual_plan_element_type_id;
919 --
920 --
921 -- ...then the carried over element type...
922 --
923 --
924 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 6);
925 hr_elements.chk_del_element_type (
926 'ZAP',
927 p_co_element_type_id,
928 '',
929 p_session_date,
930 l_effective_start_date,
931 l_effective_end_date);
932 --
933 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 7);
934 hr_elements.del_3p_element_type (
935 p_co_element_type_id,
936 'ZAP',
937 p_session_date,
938 l_effective_start_date,
939 l_effective_end_date,
940 '');
941 --
942 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',8);
943 delete from pay_element_types_f
944 where element_type_id = p_co_element_type_id;
945 --
946 --
947 -- ...then the residual element type.
948 --
949 --
950 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 9);
951 hr_elements.chk_del_element_type (
952 'ZAP',
953 p_residual_element_type_id,
957 l_effective_end_date);
954 '',
955 p_session_date,
956 l_effective_start_date,
958 --
959 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 10);
960 hr_elements.del_3p_element_type (
961 p_residual_element_type_id,
962 'ZAP',
963 p_session_date,
964 l_effective_start_date,
965 l_effective_end_date,
966 '');
967 --
968 hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 11);
969 delete from pay_element_types_f
970 where element_type_id = p_residual_element_type_id;
971 --
972 END pre_delete_actions;
973 --
974 --
975 --
976 END PAY_ACCRUAL_PLANS_PKG;
[Home] [Help]