1 PACKAGE BODY pay_us_garn_upgrade AS
2 /* $Header: pyusgrup.pkb 120.4 2012/01/19 11:30:42 rpahune ship $ */
3 /*****************************************************************************
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_garn_upgrade
21
22 Description : This package is called by a concurrent program.
23 In this package we upgrade all old architectural
24 Garnishment Elements to New architecture.
25
26 NOTE : Customer needs to recompile all uncompiled
27 formulas after running the Upgrade Process.
28
29 Change List
30 -----------
31 Date Name Ver Bug No Description
32 ----------- -------- ------- --------- -------------------------------
33 30-Sep-2004 kvsankar 115.0 3549298 Created.
34 21-feb-2005 djoshi 115.1 4198125 We are not deleting the
35 input values for newly created
36 base element in upgrade
37 we need to delete input values
38 27-Sep-2006 sudedas 115.2 5497299 Modify proc modify_calc_res_rule.
39 Formula Result WH_FEE_AMT will
40 feed Old Fees 'Withheld Fee Amount'
41 Instead of New Fees.
42 Changed cursor c_get_mig_elem_bg
43 in proc qual_elem_upg.
44 19-Feb-2010 nkjaladi 115.3 8972240 Modifed proc upgrade_garnishment
45 to update the old element special
46 inputs element information with
47 the newly created special
48 inputs element information value
49 17-Nov-2011 emunisek 115.4 13375560 Replaced Long Datatype by
50 FF_FORMULAS_F.FORMULA_TEXT%TYPE
51 *****************************************************************************/
52
53 /************************************************************
54 ** Local Package Variables
55 ************************************************************/
56 type varchar2_tab is table of varchar2(100) index by binary_integer;
57 type number_tab is table of number index by binary_integer;
58 gv_name_not_gen varchar2_tab;
59 gv_count_name number;
60 gv_package_name varchar2(50);
61 gv_location number;
62
63 /*****************************************************************************
64 Name : get_new_elem_name
65
66 Description : This function returns a new name that can be used for the
67 new element. The element name is based on the old element
68 name with suffix _New added. If the element already exists
69 then we try with combination like _New1, _New2 etc.
70 A check for New element name > 40 characters is also made.
71 Element names generated by this function will be less
72 than 40 characters. This is required so that no errors are
73 generated when we try to create a New element.
74 This function also takes into consideration the names
75 already tried. If a name errored out, it generates a
76 different name for trying.
77 Return Value : Name to be used for creating a new element.
78 *****************************************************************************/
79 FUNCTION get_new_elem_name(p_old_ele_name varchar2,
80 p_business_grp_id number) RETURN VARCHAR2
81 IS
82
83 -- Cursor to check if the name alreadt exists
84 cursor c_get_name_exists(c_ele_name varchar2) IS
85 select 'Exists'
86 from pay_element_types_f
87 where (upper(element_name) like upper(c_ele_name) OR
88 upper(element_name) like upper(c_ele_name || ' Calculator'))
89 and business_group_id = p_business_grp_id;
90
91 l_new_ele_name varchar2(80);
92 l_ele_suffix varchar2(10);
93 l_num_suffix number;
94 l_exists varchar2(20);
95 l_trunc_old_name varchar2(80);
96 l_name_count number;
97 l_regenerate boolean;
98
99 BEGIN
100
101 l_num_suffix := 0;
102 l_ele_suffix := '_New';
103 l_trunc_old_name := p_old_ele_name;
104 l_name_count := 0;
105
106 --
107 loop
108 l_regenerate := FALSE;
109 if l_num_suffix = 0 then
110 l_new_ele_name := l_trunc_old_name || l_ele_suffix;
111 else
112 l_new_ele_name := l_trunc_old_name || l_ele_suffix || to_char(l_num_suffix);
113 end if;
114 l_num_suffix := l_num_suffix + 1;
115
116 /*
117 * If length of the new element is greater than 40, then truncate
118 * the name to 34 chars and then add _New suffix for the new element.
119 * We cannot create elements with name > 40 characters.
120 */
121 if length(l_new_ele_name) > 40 then
122 l_trunc_old_name := substr(p_old_ele_name ,0,34);
123 l_num_suffix := 0;
124 l_new_ele_name := l_trunc_old_name || l_ele_suffix;
125 end if;
126
127 open c_get_name_exists(l_new_ele_name);
128 fetch c_get_name_exists into l_exists;
129 if c_get_name_exists%FOUND then
130 l_regenerate := TRUE;
131 end if;
132 close c_get_name_exists;
133
134 /*
135 * If element does not exist in database Check if it had been tried
136 * earlier. If YES then generate a new name.
137 */
138 if NOT(l_regenerate) then
139 if gv_count_name <> 0 then
140 /*
141 * Check if the name generated has been tried earlier.
142 * If Yes regenerate New name.
143 */
144 for l_name_count in gv_name_not_gen.first..gv_name_not_gen.last loop
145 if l_new_ele_name = gv_name_not_gen(l_name_count) then
146 l_regenerate := TRUE;
147 exit;
148 end if;
149 end loop;
150 end if;
151 end if;
152
153 /*
154 * If no need to regerate then check for existence in the database
155 */
156 if NOT(l_regenerate) then
157 exit;
158 end if;
159 end loop;
160 return l_new_ele_name;
161 END;
162
163 /*****************************************************************************
164 Name : modify_formula_text
165
166 Description : This function modifies the Base formula of the New element.
167 All references in the formuals to the New element are
168 replaced by references to the corrresponding old element.
169 *****************************************************************************/
170 PROCEDURE modify_formula_text(p_old_ele_name varchar2,
171 p_new_ele_name varchar2,
172 p_ele_categ varchar2,
173 p_business_grp_id number
174 )
175 IS
176
177 -- Get Formula Text for modification
178 cursor c_formula_text(cp_formula_name varchar2) IS
179 select formula_text
180 from ff_formulas_f
181 where formula_name = cp_formula_name
182 and business_group_id = p_business_grp_id;
183
184 l_formula_name varchar2(100);
185 l_cal_formula_name varchar2(100);
186 l_formula_text ff_formulas_f.formula_text%TYPE;
187 l_new_formula_text ff_formulas_f.formula_text%TYPE;
188 BEGIN
189 hr_utility.trace('Entering ' || gv_package_name || '.modify_formula_text');
190
191 /*
192 * IF ELSE condition is used to form the formula name that will be
193 * modified. Replace is used for replacing the SPACES woth '_' as
194 * formulas names do not have spaces.
195 */
196 if p_ele_categ in ('AY', 'CS', 'SS') then
197 l_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_AY_BALANCE_SETUP_FORMULA';
198 l_cal_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_AY_CHILD_SUP_CALCULATION_FORMULA';
199 elsif p_ele_categ in ('CD', 'G') then
200 l_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_CD_BALANCE_SETUP_FORMULA';
201 l_cal_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_CD_GARN_CALCULATION_FORMULA';
202 elsif p_ele_categ = 'BO' then
203 l_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_BO_BALANCE_SETUP_FORMULA';
204 l_cal_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_BO_GARN_CALCULATION_FORMULA';
205 elsif p_ele_categ = 'EL' then
206 l_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_EL_BALANCE_SETUP_FORMULA';
207 l_cal_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_EL_BALANCE_SETUP_FORMULA';
208 elsif p_ele_categ = 'ER' then
209 l_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_ER_BALANCE_SETUP_FORMULA';
210 l_cal_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_ER_BALANCE_SETUP_FORMULA';
211 elsif p_ele_categ = 'TL' then
212 l_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_TL_BALANCE_SETUP_FORMULA';
213 l_cal_formula_name := upper(replace(p_new_ele_name, ' ' ,'_')) || '_TL_FED_LEVY_CALCULATION_FORMULA';
214 end if;
215
216 hr_utility.trace('Modifying Formula ' || l_formula_name);
217
218 l_formula_text := NULL;
219 open c_formula_text(l_formula_name);
220 fetch c_formula_text into l_formula_text;
221 close c_formula_text;
222
223 l_new_formula_text := replace(l_formula_text,
224 upper(replace(p_new_ele_name, ' ' ,'_')),
225 upper(replace(p_old_ele_name, ' ' ,'_')));
226
227 update ff_formulas_f
228 set formula_text = l_new_formula_text
229 where formula_name = l_formula_name
230 and business_group_id = p_business_grp_id;
231
232 /*
233 * Removing Entry from ff_compiled_info and ff_fdi_usages for both the formulas
234 * that are created for the new element. Customer needs to recompile all the
235 * formulae after running this process.
236 */
237 delete
238 from ff_compiled_info
239 where formula_id in (select formula_id
240 from ff_formulas_f
241 where formula_name in (l_formula_name,
242 l_cal_formula_name));
243 delete
244 from ff_fdi_usages
245 where formula_id in (select formula_id
246 from ff_formulas_f
247 where formula_name in (l_formula_name,
248 l_cal_formula_name));
249
250 hr_utility.trace('Leaving ' || gv_package_name || '.modify_formula_text');
251
252 END modify_formula_text;
253
254 /*****************************************************************************
255 Name : create_new_element
256
257 Description : This function creates a new element for the old element
258 passed. This uses the effective start date and effective
259 end date of the old element to create the new element. These
260 dates are passed in as parameters.
261 Return Value : 0 ---> Indicates Error
262 Other than 0 ---> Element Type ID of the new element.
263 *****************************************************************************/
264 FUNCTION create_new_element(p_old_ele_type_id number,
265 p_business_grp_id number,
266 p_eff_start_date date,
267 p_eff_end_date date) RETURN number
268 IS
269
270 -- Get the details of the old element
271 cursor c_get_old_ele_det(cp_eff_date date) IS
272 select element_name,
273 reporting_name,
274 description,
275 benefit_classification_id,
276 element_information1 category,
277 processing_type,
278 processing_priority,
279 standard_link_flag,
280 element_information3 processing_runtype,
281 post_termination_rule
282 from pay_element_types_f
283 where element_type_id = p_old_ele_type_id
284 and business_group_id = p_business_grp_id
285 and cp_eff_date between effective_start_date
286 and effective_end_date
287 order by effective_start_date;
288
289 cursor c_get_new_ele_det(cp_ele_type_id number) IS
290 select formula_id, -- Formula ID for Skip Rule
291 element_information2,
292 element_information5, -- Calculator Element
293 element_information8, -- Voluntary Deductions
294 element_information19, -- Special Features
295 element_information20 -- Verifier
296 from pay_element_types_f
297 where element_type_id = cp_ele_type_id
298 and business_group_id = p_business_grp_id;
299
300
301 l_old_ele_name varchar2(100);
302 l_new_elem_name varchar2(100);
303 l_reporting_name varchar2(200);
304 l_description varchar2(400);
305 l_ben_class_id number;
306 l_ele_cat varchar2(10);
307 l_process_type varchar2(10);
308 l_process_prty number;
309 l_std_link_flag varchar2(10);
310 l_process_runtype varchar2(10);
311 l_start_rule varchar2(10);
312 l_stop_rule varchar2(10);
313 l_amount_rule varchar2(10);
314 l_ser_ee_bond varchar2(10);
315 l_arrearage varchar2(10);
316 l_ded_partial varchar2(10);
317 l_emp_match varchar2(10);
318 l_aft_tax_comp varchar2(10);
319 l_srs_plan_type varchar2(10);
320 l_srs_buy_back varchar2(10);
321 l_catch_up_proc varchar2(10);
322 l_post_term_rule varchar2(10);
323 l_new_ele_type_id number;
324 l_count number;
325 l_skip_rule number;
326 l_elem_inf2 varchar2(100);
327 l_cal_elem varchar2(100);
328 l_vol_dedn varchar2(100);
329 l_spec_feat varchar2(100);
330 l_verifier varchar2(100);
331
332 BEGIN
333 -- hr_utility.trace_on(NULL, 'Dedn');
334 hr_utility.trace('Entering ' || gv_package_name || '.create_new_element');
335
336 /*
337 * Initialization Code
338 */
339 l_start_rule := 'OE';
340 l_stop_rule := 'OE';
341 l_amount_rule := 'FA';
342 l_ser_ee_bond := 'N';
343 l_arrearage := 'N';
344 l_ded_partial := 'N';
345 l_emp_match := 'N';
346 l_aft_tax_comp := 'N';
347 l_srs_plan_type := 'N';
348 l_srs_buy_back := 'N';
349 l_catch_up_proc := 'NONE';
350 l_count := 0;
351 gv_count_name := 0;
352 gv_name_not_gen.delete;
353
354 /*
355 * Setting Location for identifying error
356 */
357 gv_location := 1;
358
359 /*
360 * In case where there has been some date effective change done to
361 * Involuntary Deduction element, the latest record is taken into account
362 * for the creation of new element.
363 */
364 open c_get_old_ele_det(p_eff_end_date);
365 fetch c_get_old_ele_det into l_old_ele_name
366 ,l_reporting_name
367 ,l_description
368 ,l_ben_class_id
369 ,l_ele_cat
370 ,l_process_type
371 ,l_process_prty
372 ,l_std_link_flag
373 ,l_process_runtype
374 ,l_post_term_rule;
375 close c_get_old_ele_det;
376
377
378 if l_description is NULL then
379 l_description := 'New Architectural Element For ' || l_old_ele_name;
380 else
381 l_description := l_description || ' New Architectural Element For ' || l_old_ele_name;
382 end if;
383
384
385 /*
386 * Trace Statements
387 */
388 hr_utility.trace('Old Element ' || l_old_ele_name);
389 hr_utility.trace('Old Element Type ID ' || p_old_ele_type_id);
390 hr_utility.trace('Effective Start Date ' || p_eff_start_date);
391 hr_utility.trace('Effective End Date ' || p_eff_end_date);
392 -- hr_utility.trace_off();
393
394 /*
395 * This loop tries to create a New element for the Old element
396 * A maximum of 10 elements are tried for a single OLD element.
397 * IF the creation of ALL 10 fail we error the upgrade process for the
398 * OLD element. After succesful creation of the very first New element we
399 * exit out of the loop and continue the process.
400 */
401 loop
402 begin
403 /*
404 * Get the new element name. If the creation the new errors out,
405 * add the new element to the global table used by get_new_elem_name
406 * function. The function then will return a new name other than
407 * the one tried now.
408 */
409 l_new_elem_name := get_new_elem_name(l_old_ele_name,
410 p_business_grp_id);
411 -- hr_utility.trace_on(NULL, 'Dedn');
412 hr_utility.trace('Element Name Trying ' || l_new_elem_name);
413 -- hr_utility.trace_off();
414
415 /*
416 * Create a Savepoint. If the element creation fails half way through
417 * then we need to rollback whatever is done before trying to
418 * create another NEW architectural element for the same old element
419 */
420 savepoint ELEM_CREAT;
421 l_new_ele_type_id := pay_us_dedn_template_wrapper.create_deduction_element(
422 p_element_name => l_new_elem_name
423 ,p_reporting_name => l_reporting_name
424 ,p_description => l_description
425 ,p_classification_name => 'Involuntary Deductions'
426 ,p_ben_class_id => l_ben_class_id
427 ,p_category => l_ele_cat
428 ,p_processing_type => l_process_type
429 ,p_processing_priority => l_process_prty
430 ,p_standard_link_flag => l_std_link_flag
431 ,p_processing_runtype => l_process_runtype
432 ,p_start_rule => l_start_rule
433 ,p_stop_rule => l_stop_rule
434 ,p_amount_rule => l_amount_rule
435 ,p_series_ee_bond => l_ser_ee_bond
436 ,p_payroll_table => NULL
437 ,p_paytab_column => NULL
438 ,p_rowtype_meaning => NULL
439 ,p_arrearage => l_ded_partial
440 ,p_deduct_partial => l_ded_partial
441 ,p_employer_match => l_emp_match
442 ,p_aftertax_component => l_aft_tax_comp
443 ,p_ele_eff_start_date => p_eff_start_date
444 ,p_ele_eff_end_date => p_eff_end_date
445 ,p_business_group_id => p_business_grp_id
446 ,p_catchup_processing => l_catch_up_proc
447 ,p_termination_rule => l_post_term_rule
448 ,p_srs_plan_type => l_srs_plan_type
449 ,p_srs_buy_back => l_srs_buy_back
450 );
451 exception
452 when others then
453 -- hr_utility.trace_on(NULL, 'Dedn');
454 hr_utility.trace('Element Name Tried ' || l_new_elem_name);
455 -- hr_utility.trace_off();
456 /*
457 * Rollback to the point where the element creation was started
458 */
459 rollback to ELEM_CREAT;
460 gv_count_name := gv_count_name + 1;
461 gv_name_not_gen(gv_count_name) := l_new_elem_name;
462 l_new_ele_type_id := 0;
463 end; /* End Creation of element */
464
465 /*
466 * If the number of tries for creating a new element goes more than
467 * 10, then we stop the upgrade process for that element and error out
468 * the upgrade process for that element alone.
469 */
470 if l_new_ele_type_id <> 0 or gv_count_name > 10 then
471 exit;
472 end if;
473 end loop;
474
475 gv_count_name := 0;
476 gv_name_not_gen.delete;
477
478 -- hr_utility.trace_on(NULL, 'Dedn');
479 hr_utility.trace('New Element ' || l_new_elem_name);
480 hr_utility.trace('New Element Type ID ' || l_new_ele_type_id);
481
482 if l_new_ele_type_id <> 0 then
483
484 hr_utility.trace('Before CALL to modify_formula_text');
485 /*
486 * Modify The Formula text of New Base Formula
487 */
488 modify_formula_text(l_old_ele_name
489 ,l_new_elem_name
490 ,l_ele_cat
491 ,p_business_grp_id);
492
493 /*
494 * Modifying the OLD Base Element
495 */
496 open c_get_new_ele_det(l_new_ele_type_id);
497 fetch c_get_new_ele_det into l_skip_rule
498 ,l_elem_inf2
499 ,l_cal_elem
500 ,l_vol_dedn
501 ,l_spec_feat
502 ,l_verifier;
503 close c_get_new_ele_det;
504 update pay_element_types_f
505 set formula_id = l_skip_rule
506 ,element_information2 = l_elem_inf2
507 ,element_information5 = l_cal_elem
508 ,element_information8 = l_vol_dedn
509 ,element_information19 = l_spec_feat
510 ,element_information20 = l_verifier
511 where element_type_id = p_old_ele_type_id
512 and business_group_id = p_business_grp_id;
513
514 end if;
515
516 hr_utility.trace('Leaving ' || gv_package_name || '.create_new_element');
517 return l_new_ele_type_id;
518
519 exception
520 --
521 when others then
522 l_new_ele_type_id := 0;
523 -- hr_utility.trace_on(NULL, 'Dedn');
524 hr_utility.trace('Could not create new element');
525 return l_new_ele_type_id;
526 END;
527
528 /*****************************************************************************
529 Name : copy_result_rules
530
531 Description : STEP 1
532 This function first deletes all the formula result rules for
533 the old element.
534 STEP 2
535 It copies the formula result rules from the Base element
536 of the New element created to the Base element of the Old
537 element.
538 *****************************************************************************/
539 PROCEDURE copy_result_rules(p_old_ele_type_id number,
540 p_new_ele_type_id number,
541 p_business_grp_id number
542 )
543 IS
544
545 -- Get processing rules that need to be copied
546 cursor c_get_stat_proc_rules(cp_ele_type_id number,
547 cp_bg_grp_id number) IS
548 select business_group_id,
549 legislation_code,
550 legislation_subgroup,
551 effective_start_date,
552 effective_end_date,
553 assignment_status_type_id,
554 formula_id,
555 processing_rule
556 from pay_status_processing_rules_f
557 where element_type_id in (select element_type_id
558 from pay_element_types_f
559 where element_type_id = cp_ele_type_id
560 and business_group_id = cp_bg_grp_id);
561
562 -- Get the formula result rules that need to be copied
563 cursor c_get_form_result_rules(cp_ele_type_id varchar2,
564 cp_bg_grp_id number) IS
565 select pfrrf.business_group_id,
566 NULL legislation_code,
567 NULL legislation_sub_grp,
568 pfrrf.effective_start_date,
569 pfrrf.effective_end_date,
570 pfrrf.input_value_id,
571 pfrrf.result_name,
572 pfrrf.result_rule_type,
573 pfrrf.severity_level,
574 pfrrf.element_type_id
575 from pay_status_processing_rules_f psprf,
576 pay_element_types_f petf,
577 pay_formula_result_rules_f pfrrf
578 where petf.element_type_id = cp_ele_type_id
579 and petf.business_group_id = cp_bg_grp_id
580 and psprf.element_type_id = petf.element_type_id
581 and pfrrf.status_processing_rule_id = psprf.status_processing_rule_id;
582
583 l_bg_id number;
584 l_leg_code varchar2(10);
585 l_leg_sub_grp varchar2(10);
586 l_eff_start_date date;
587 l_eff_end_date date;
588 l_stat_proc_rule_id number;
589 l_input_val_id number;
590 l_result_name varchar2(100);
591 l_result_rule_type varchar2(10);
592 l_severity_level varchar2(10);
593 l_ele_type_id number;
594 l_form_res_rule_id number;
595 l_assg_stat_type_id number;
596 l_formula_id number;
597 l_process_rule varchar2(10);
598
599 BEGIN
600 hr_utility.trace('Entering ' || gv_package_name || '.copy_result_rules');
601
602 /*
603 * Delete the formula result rules for the Old element.
604 */
605 delete from pay_formula_result_rules_f
606 where status_processing_rule_id in (select distinct status_processing_rule_id
607 from pay_status_processing_rules_f psprf,
608 pay_element_types_f petf
609 where petf.element_type_id = p_old_ele_type_id
610 and psprf.element_type_id = petf.element_type_id
611 and petf.business_group_id = p_business_grp_id);
612 delete from pay_status_processing_rules_f
613 where element_type_id in (select distinct element_type_id
614 from pay_element_types_f
615 where element_type_id = p_old_ele_type_id
616 and business_group_id = p_business_grp_id);
617
618 open c_get_stat_proc_rules(p_new_ele_type_id,
619 p_business_grp_id);
620 --
621 fetch c_get_stat_proc_rules into l_bg_id
622 ,l_leg_code
623 ,l_leg_sub_grp
624 ,l_eff_start_date
625 ,l_eff_end_date
626 ,l_assg_stat_type_id
627 ,l_formula_id
628 ,l_process_rule;
629 close c_get_stat_proc_rules;
630
631 -- hr_utility.trace_off;
632
633 /*
634 * Create Processing Rule for Old Base Element. Use the Data
635 * obtained in the cursor 'c_get_stat_proc_rules'
636 */
637 l_stat_proc_rule_id := pay_formula_results.ins_stat_proc_rule (
638 p_business_group_id => l_bg_id
639 ,p_legislation_code => l_leg_code
640 ,p_legislation_subgroup => l_leg_sub_grp
641 ,p_effective_start_date => l_eff_start_date
642 ,p_effective_end_date => l_eff_end_date
643 ,p_element_type_id => p_old_ele_type_id
644 ,p_assignment_status_type_id => l_assg_stat_type_id
645 ,p_formula_id => l_formula_id
646 ,p_processing_rule => l_process_rule);
647
648
649 /*
650 * Create Formula Resuls rules for the Old element
651 * based on the new element's formula result rules
652 */
653 open c_get_form_result_rules(p_new_ele_type_id,
654 p_business_grp_id);
655 loop
656 fetch c_get_form_result_rules into l_bg_id
657 ,l_leg_code
658 ,l_leg_sub_grp
659 ,l_eff_start_date
660 ,l_eff_end_date
661 ,l_input_val_id
662 ,l_result_name
663 ,l_result_rule_type
664 ,l_severity_level
665 ,l_ele_type_id;
666 exit when c_get_form_result_rules%NOTFOUND;
667 l_form_res_rule_id := pay_formula_results.ins_form_res_rule(
668 p_business_group_id => l_bg_id
669 ,p_legislation_code => l_leg_code
670 ,p_legislation_subgroup => l_leg_sub_grp
671 ,p_effective_start_date => l_eff_start_date
672 ,p_effective_end_date => l_eff_end_date
673 ,p_status_processing_rule_id => l_stat_proc_rule_id
674 ,p_input_value_id => l_input_val_id
675 ,p_result_name => l_result_name
676 ,p_result_rule_type => l_result_rule_type
677 ,p_severity_level => l_severity_level
678 ,p_element_type_id => l_ele_type_id);
679 end loop;
680 close c_get_form_result_rules;
681
682 -- hr_utility.trace_on(NULL, 'Dedn');
683 hr_utility.trace('Leaving ' || gv_package_name || '.copy_result_rules');
684 END copy_result_rules;
685
686
687 /*****************************************************************************
688 Name : copy_balance_feeds
689
690 Description : This procedure copies all the balance feeds from the new
691 balances to the corresponding old balances.
692 *****************************************************************************/
693 FUNCTION copy_balance_feeds(p_old_ele_name varchar2,
694 p_new_ele_name varchar2,
695 p_business_grp_id number) RETURN BOOLEAN
696 IS
697
698
699
700 -- Get Balance Type Ids
701 cursor c_balance_type_id(cp_ele_name varchar2,
702 cp_business_grp_id number) IS
703 select balance_type_id
704 from pay_balance_types
705 where business_group_id = cp_business_grp_id
706 and balance_name in (cp_ele_name,
707 cp_ele_name || ' Accrued',
708 cp_ele_name || ' Accrued Fees',
709 cp_ele_name || ' Additional',
710 cp_ele_name || ' Arrears',
711 cp_ele_name || ' Fees',
712 cp_ele_name || ' Not Taken',
713 cp_ele_name || ' Replacement')
714 order by balance_name;
715
716 -- Get Input Value Ids to be transferred
717 cursor c_input_val_id(cp_balance_type_id number,
718 cp_business_grp_id number) IS
719 select effective_start_date,
720 effective_end_date,
721 legislation_code,
722 input_value_id,
723 scale,
724 legislation_subgroup
725 from pay_balance_feeds_f
726 where business_group_id = cp_business_grp_id
727 and balance_type_id = cp_balance_type_id;
728
729
730 -- Query to chk existence of inp val for balance type id
731 cursor c_chk_inp_val(cp_balance_type_id number,
732 cp_input_val_id number,
733 cp_business_grp_id number) IS
734 select 'Exists'
735 from pay_balance_feeds_f
736 where balance_type_id = cp_balance_type_id
737 and input_value_id = cp_input_val_id
738 and business_group_id = cp_business_grp_id;
739
740 l_rowid varchar2(100);
741 l_inp_val_id number;
742 l_count number;
743 l_balance_feed_id number;
744 l_eff_start_date date;
745 l_eff_end_date date;
746 l_leg_code varchar2(50);
747 l_scale varchar2(10);
748 l_leg_sub_group varchar2(50);
749 l_exists varchar2(10);
750 l_old_bal_type_id_tab number_tab;
751 l_new_bal_type_id_tab number_tab;
752
753
754 BEGIN
755 hr_utility.trace('Entering ' || gv_package_name || '.copy_balance_feeds');
756 hr_utility.trace('Copying Balance Feeds from ' || p_new_ele_name
757 || ' into ' || p_old_ele_name);
758 -- hr_utility.trace_off;
759
760 /*
761 * Setting Location for identifying error
762 */
763 gv_location := 2;
764
765
766 /*
767 * Fetching Balance Type IDs for Old Balances
768 */
769 l_count := 0;
770 open c_balance_type_id(p_old_ele_name,
771 p_business_grp_id);
772 loop
773 fetch c_balance_type_id into l_old_bal_type_id_tab(l_count);
774 exit when c_balance_type_id%NOTFOUND;
775 l_count := l_count + 1;
776 end loop;
777 close c_balance_type_id;
778 hr_utility.trace('Balance Count Old Balance = ' || l_count);
779
780
781 /*
782 * Fetching Balance Type IDs for New Balances
783 */
784 l_count := 0;
785 open c_balance_type_id(p_new_ele_name,
786 p_business_grp_id);
787 loop
788 fetch c_balance_type_id into l_new_bal_type_id_tab(l_count);
789 exit when c_balance_type_id%NOTFOUND;
790 l_count := l_count + 1;
791 end loop;
792 close c_balance_type_id;
793
794 hr_utility.trace('Balance Count New Balance = ' || l_count);
795 /*
796 * This FOR loop copies the balance feeds from New Balances
797 * to the corresponding Old Balances.
798 * The outer FOR loop runs for the New balances. For every New balance
799 * we copy the balances feeds to the corresponding Old Balance.
800 */
801 l_count := 0;
802 for l_count in l_new_bal_type_id_tab.first..l_new_bal_type_id_tab.last
803 loop
804 if l_new_bal_type_id_tab.exists(l_count) then
805 open c_input_val_id(l_new_bal_type_id_tab(l_count),
806 p_business_grp_id);
807 loop
808 fetch c_input_val_id into l_eff_start_date,
809 l_eff_end_date,
810 l_leg_code,
811 l_inp_val_id,
812 l_scale,
813 l_leg_sub_group;
814 exit when c_input_val_id%NOTFOUND;
815 hr_utility.trace('Input Value ID = ' || l_inp_val_id);
816 hr_utility.trace('Effective_start date ' || l_eff_start_date);
817 hr_utility.trace('Effective End Date ' || l_eff_end_date);
818
819 /*
820 * Check If the balance feed already exists.
821 * At this point Duplicate feed cannot exist but have added the code
822 * for cheking anyway.
823 */
824 open c_chk_inp_val(l_old_bal_type_id_tab(l_count),
825 l_inp_val_id,
826 p_business_grp_id);
827 fetch c_chk_inp_val into l_exists;
828 if c_chk_inp_val%NOTFOUND and l_old_bal_type_id_tab.exists(l_count) then
829 hr_utility.trace('Creating Balance Feed for ' || l_old_bal_type_id_tab(l_count));
830 l_rowid := NULL;
831 l_balance_feed_id := NULL;
832
833 pay_balance_feeds_f_pkg.Insert_Row(
834 X_Rowid => l_rowid,
835 X_Balance_Feed_Id => l_balance_feed_id,
836 X_Effective_Start_Date => l_eff_start_date,
837 X_Effective_End_Date => l_eff_end_date,
838 X_Business_Group_Id => p_business_grp_id,
839 X_Legislation_Code => l_leg_code,
840 X_Balance_Type_Id => l_old_bal_type_id_tab(l_count),
841 X_Input_Value_Id => l_inp_val_id,
842 X_Scale => l_scale,
843 X_Legislation_Subgroup => l_leg_sub_group);
844 if l_balance_feed_id is NULL then
845 hr_utility.raise_error;
846 end if;
847
848 end if;
849 close c_chk_inp_val;
850 end loop;
851 close c_input_val_id;
852 end if;
853 end loop;
854 -- hr_utility.trace_on(NULL, 'Dedn');
855 hr_utility.trace('Leaving ' || gv_package_name || '.copy_balance_feeds');
856 return TRUE;
857
858 exception
859 --
860 when others then
861 -- hr_utility.trace_on(NULL, 'Dedn');
862 hr_utility.trace('Could not Copy Balance Feed');
863 return FALSE;
864 END copy_balance_feeds;
865
866
867 /*****************************************************************************
868 Name : del_balance_feeds
869
870 Description : This procedure deletes all the balance feeds for the balances
871 associated to the element passed.
872 *****************************************************************************/
873 PROCEDURE del_balance_feeds(p_new_ele_name varchar2,
874 p_business_grp_id number)
875 IS
876
877 BEGIN
878 hr_utility.trace('Entering ' || gv_package_name || '.del_balances');
879
880 hr_utility.trace('Deleting Balance Feeds For Balances associated with '
881 || p_new_ele_name);
882 -- hr_utility.trace_off;
883
884
885 /*
886 * This delete statement deletes the balance feeds for the balances
887 * associated with the element passed.
888 */
889 delete
890 from pay_balance_feeds_f
891 where balance_type_id in (select balance_type_id
892 from pay_balance_types
893 where balance_name in
894 (p_new_ele_name,
895 p_new_ele_name || ' Accrued',
896 p_new_ele_name || ' Accrued Fees',
897 p_new_ele_name || ' Additional',
898 p_new_ele_name || ' Arrears',
899 p_new_ele_name || ' Fees',
900 p_new_ele_name || ' Not Taken',
901 p_new_ele_name || ' Replacement')
902 and business_group_id = p_business_grp_id);
903
904 -- hr_utility.trace_on(NULL, 'Dedn');
905 hr_utility.trace('Leaving ' || gv_package_name || '.del_balances');
906
907 END del_balance_feeds;
908
909 /*****************************************************************************
910 Name : modify_calc_res_rule
911
912 Description : This procedure modifies the Calculator formula of the new
913 element so that the STOP ENTRY result is passed to the 'Pay
914 Value' of the old element instead of the new element.
915 Also Formula Result WH_FEE_AMT will feed Old Fees
916 'Withheld Fee Amount' Instead of New Fees 'Pay Value' or
917 'Withheld Fee Amount'.
918 *****************************************************************************/
919 PROCEDURE modify_calc_res_rule(p_old_ele_name varchar2,
920 p_new_ele_name varchar2,
921 p_business_grp_id number)
922 IS
923
924 -- Cursor to get the status_processing_rule_id
925 cursor c_get_stat_proc_rules(cp_ele_name varchar2,
926 cp_bg_grp_id number) IS
927 select status_processing_rule_id
928 from pay_status_processing_rules_f
929 where element_type_id in (select element_type_id
930 from pay_element_types_f
931 where element_name = cp_ele_name
932 and business_group_id = cp_bg_grp_id);
933
934 -- Cursor to get the input value id of the Any input Value
935 cursor c_get_inp_val_id_general(cp_ele_name varchar2
936 ,cp_inp_val_name varchar2
937 ,cp_business_group_id number
938 ) IS
939 select petf.element_type_id,
940 pivf.input_value_id
941 from pay_element_types_f petf,
942 pay_input_values_f pivf
943 where petf.element_name like cp_ele_name
944 and petf.business_group_id = cp_business_group_id
945 and pivf.element_type_id = petf.element_type_id
946 and pivf.name = cp_inp_val_name;
947
948 l_ele_type_id number ;
949 l_inp_val_id number ;
950 l_stat_proc_rule_id number ;
951 l_old_fees_ele_type_id number ;
952 l_old_fees_inp_val_id number ;
953
954 BEGIN
955 hr_utility.trace('Entering ' || gv_package_name || '.modify_res_rule_calc');
956 hr_utility.trace('Modifying The Formula ' || p_new_ele_name || ' Calculator');
957
958
959 /*
960 * Get the status processing rule id. This is then used in the Update
961 * statement below'
962 */
963 open c_get_stat_proc_rules(p_new_ele_name || ' Calculator',
964 p_business_grp_id);
965 fetch c_get_stat_proc_rules into l_stat_proc_rule_id;
966 close c_get_stat_proc_rules;
967
968 /*
969 * Get the element_type_id and input_value_id of 'Pay Value' of
970 * the old Base element. These values are then updated for the
971 * STOP_ENTRY result in the Calculator formula
972 */
973 open c_get_inp_val_id_general(p_old_ele_name,
974 'Pay Value',
975 p_business_grp_id);
976 fetch c_get_inp_val_id_general into l_ele_type_id
977 ,l_inp_val_id;
978 close c_get_inp_val_id_general ;
979
980 hr_utility.trace('Modifying the STOP_ENTRY Result');
981
982 /*
983 * Updates the New Calculator formula result rule so that the
984 * STOP_ENTY result goes to the 'Pay Value' of Old Base element
985 */
986 update pay_formula_result_rules_f
987 set element_type_id = l_ele_type_id,
988 input_value_id = l_inp_val_id
989 where status_processing_rule_id = l_stat_proc_rule_id
990 and result_name = 'STOP_ENTRY'
991 and business_group_id = p_business_grp_id;
992
993 -- Modify the Formula Result Rule for Fees.
994 -- Formula Result WH_FEE_AMT will feed Old Fees 'Withheld Fee Amount'
995 -- Instead of New Fees .
996
997 /* Get Element Type ID and Input Value of the Old Fees Elements */
998 open c_get_inp_val_id_general(p_old_ele_name || ' Fees',
999 'Withheld Fee Amount',
1000 p_business_grp_id);
1001 fetch c_get_inp_val_id_general into l_old_fees_ele_type_id
1002 ,l_old_fees_inp_val_id;
1003 close c_get_inp_val_id_general;
1004
1005 hr_utility.trace('Ele Typ ID for Old Fees Ele := '||l_old_fees_ele_type_id) ;
1006 hr_utility.trace('Inp Val ID for Withheld Fee Amt for Old Fees Ele := '||l_old_fees_inp_val_id) ;
1007 hr_utility.trace('Status Proc Rule ID = '||l_stat_proc_rule_id) ;
1008
1009 /* Updating FRR so that WH_FEE_AMT feeds Withheld Fee Amount of Old Fees Element
1010 instead of New Fees
1011 Using Status Processing Rule ID of New Calculator Element
1012 */
1013
1014 update pay_formula_result_rules_f
1015 set element_type_id = l_old_fees_ele_type_id,
1016 input_value_id = l_old_fees_inp_val_id
1017 where status_processing_rule_id = l_stat_proc_rule_id
1018 and result_name = 'WH_FEE_AMT'
1019 and business_group_id = p_business_grp_id;
1020
1021 hr_utility.trace('Leaving ' || gv_package_name || '.modify_res_rule_calc');
1022
1023 END modify_calc_res_rule;
1024
1025
1026 /*****************************************************************************
1027 Name : del_base_element
1028
1029 Description : This procedure deletes the base element of the New element
1030 created. We do not want the customer to use the Base element
1031 in the future for processing. We will also have to delete
1032 input Values associated with the base element.
1033 *****************************************************************************/
1034
1035 PROCEDURE del_base_element(p_new_ele_name varchar2,
1036 p_business_grp_id number)
1037 IS
1038
1039 CURSOR c_element_name(cp_element_name varchar2) IS
1040 SELECT element_Type_id
1041 FROM pay_element_Types_f
1042 WHERE element_name = cp_element_name
1043 AND business_group_id = p_business_grp_id;
1044
1045 l_element_type_id number;
1046
1047 BEGIN
1048 hr_utility.trace('Entering ' || gv_package_name || '.del_base_element');
1049
1050 hr_utility.trace('Deleting Base Element ' || p_new_ele_name);
1051 -- hr_utility.trace_off;
1052
1053 /* Get Element Type Id */
1054
1055 OPEN c_element_name(p_new_ele_name);
1056 FETCH c_element_name into l_element_type_id;
1057 CLOSE c_element_name;
1058
1059
1060 /*
1061 * Delete the formula result rules associated with the New Base element
1062 */
1063 DELETE
1064 FROM pay_formula_result_rules_f
1065 WHERE status_processing_rule_id
1066 IN (SELECT status_processing_rule_id
1067 FROM pay_status_processing_rules_f psprf
1068 WHERE psprf.element_type_id = l_element_type_id );
1069
1070 DELETE
1071 FROM pay_status_processing_rules_f
1072 WHERE element_type_id = l_element_Type_id;
1073
1074 /* Delete the Input Values associated with the Base Element */
1075 DELETE
1076 FROM pay_input_values_f
1077 WHERE element_type_id = l_element_type_id;
1078
1079
1080 /*
1081 * Deleted the base element
1082 */
1083 DELETE
1084 FROM pay_element_types_f
1085 WHERE element_Type_id = l_element_type_id;
1086
1087
1088 -- hr_utility.trace_on(NULL, 'Dedn');
1089 hr_utility.trace('Leaving ' || gv_package_name || '.del_base_element');
1090
1091 END del_base_element;
1092
1093 /*****************************************************************************
1094 Name : mod_inp_vals
1095
1096 Description : This procedure modifies the input values for the old element
1097 to be consistent with the new architecture.
1098 *****************************************************************************/
1099 PROCEDURE mod_inp_vals(p_ele_name varchar2,
1100 p_ele_category varchar2,
1101 p_business_grp_id number)
1102 IS
1103
1104 BEGIN
1105 hr_utility.trace('Entering ' || gv_package_name || '.mod_inp_vals');
1106
1107 hr_utility.trace('Modifying the Display Sequence for the element ' || p_ele_name);
1108 -- hr_utility.trace_off;
1109
1110 if p_ele_category = 'AY' OR p_ele_category = 'CS' or p_ele_category = 'SS' then
1111 hr_utility.trace('Setting Mandatory Flag for the Input Value Allowances');
1112
1113 /*
1114 * Update statement for elements of category 'Alimony', 'Child Support' and
1115 * 'Spousal Support'.
1116 */
1117 update pay_input_values_f pivf
1118 set mandatory_flag = 'X'
1119 where pivf.element_type_id in (select distinct element_type_id
1120 from pay_element_types_f
1121 where element_name = p_ele_name
1122 and business_group_id = p_business_grp_id)
1123 and pivf.name in ('Allowances')
1124 and pivf.business_group_id = p_business_grp_id;
1125 elsif p_ele_category = 'CD' or p_ele_category = 'G' then
1126 hr_utility.trace('Setting Mandatory Flag for the Input Value Dedns at Time of Writ, Allowances and Clear Arrears.');
1127
1128 /*
1129 * Update statement for elements of category 'Credit Debt' and 'Garnishment'.
1130 */
1131 update pay_input_values_f pivf
1132 set mandatory_flag = 'X'
1133 where pivf.element_type_id in (select distinct element_type_id
1134 from pay_element_types_f
1135 where element_name = p_ele_name
1136 and business_group_id = p_business_grp_id)
1137 and pivf.name in ('Dedns at Time of Writ',
1138 'Allowances',
1139 'Clear Arrears')
1140 and pivf.business_group_id = p_business_grp_id;
1141 elsif p_ele_category = 'BO' then
1142 hr_utility.trace('Setting Mandatory Flag for the Input Value Dedns at Time of Writ, Allowances and Clear Arrears.');
1143
1144 /*
1145 * Update statement for elements of category 'Bankruptcy Order'.
1146 */
1147 update pay_input_values_f pivf
1148 set mandatory_flag = 'X'
1149 where pivf.element_type_id in (select distinct element_type_id
1150 from pay_element_types_f
1151 where element_name = p_ele_name
1152 and business_group_id = p_business_grp_id)
1153 and pivf.name in ('Dedns at Time of Writ',
1154 'Allowances',
1155 'Clear Arrears')
1156 and pivf.business_group_id = p_business_grp_id;
1157 elsif p_ele_category = 'EL' then
1158 hr_utility.trace('Setting Mandatory Flag for the Input Value Dedns at Time of Writ, Allowances and Clear Arrears.');
1159
1160 /*
1161 * Update statement for elements of category 'Education Loan'.
1162 */
1163 update pay_input_values_f pivf
1164 set mandatory_flag = 'X'
1165 where pivf.element_type_id in (select distinct element_type_id
1166 from pay_element_types_f
1167 where element_name = p_ele_name
1168 and business_group_id = p_business_grp_id)
1169 and pivf.name in ('Dedns at Time of Writ',
1170 'Filing Status',
1171 'Allowances',
1172 'Num Dependents',
1173 'Clear Arrears')
1174 and pivf.business_group_id = p_business_grp_id;
1175 elsif p_ele_category = 'TL' then
1176 hr_utility.trace('Setting Mandatory Flag for the Input Value Dedns at Time of Writ, Allowances and Clear Arrears.');
1177
1178 /*
1179 * Update statement for elements of category 'Tax Levy'.
1180 */
1181 update pay_input_values_f pivf
1182 set mandatory_flag = 'X'
1183 where pivf.element_type_id in (select distinct element_type_id
1184 from pay_element_types_f
1185 where element_name = p_ele_name
1186 and business_group_id = p_business_grp_id)
1187 and pivf.name in ('Percentage',
1188 'Num Dependents',
1189 'Clear Arrears')
1190 and pivf.business_group_id = p_business_grp_id;
1191 elsif p_ele_category = 'ER' then
1192 hr_utility.trace('Setting Mandatory Flag for the Input Value Dedns at Time of Writ, Allowances and Clear Arrears.');
1193
1194 /*
1195 * Update statement for elements of category 'Employee Requested'.
1196 */
1197 update pay_input_values_f pivf
1198 set mandatory_flag = 'X'
1199 where pivf.element_type_id in (select distinct element_type_id
1200 from pay_element_types_f
1201 where element_name = p_ele_name
1202 and business_group_id = p_business_grp_id)
1203 and pivf.name in ('Dedns at Time of Writ',
1204 'Allowances',
1205 'Clear Arrears')
1206 and pivf.business_group_id = p_business_grp_id;
1207 end if;
1208
1209 -- hr_utility.trace_on(NULL, 'Dedn');
1210 hr_utility.trace('Leaving ' || gv_package_name || '.mod_inp_vals');
1211
1212 END mod_inp_vals;
1213
1214
1215 /*****************************************************************************
1216 Name : garn_upgrade
1217
1218 Description : This procedure is called from the Concurrent Request. Based on
1219 category passed in as a parameter, we will select the elements
1220 that will be migrated in ine request.
1221 *****************************************************************************/
1222 PROCEDURE upgrade_garnishment
1223 (p_elem_type_id in number)
1224 IS
1225
1226 -- Get the elements that need to be migrated for the BG
1227 -- This package will be called with the element category passed
1228 -- to it as a parameter. This method is used to multi-thread the whole process
1229 cursor c_get_mig_elem_bg(cp_ele_type_id number) IS
1230 select /*+ Choose */
1231 petf.element_type_id, min(petf.effective_start_date), max(petf.effective_end_date)
1232 from pay_element_types_f petf
1233 where petf.element_type_id = p_elem_type_id
1234 and petf.element_information4 is NULL
1235 group by petf.element_type_id;
1236
1237 -- Get element name, category and Calc Ele type id
1238 cursor c_get_ele_name_cat (cp_ele_type_id number,
1239 cp_business_grp_id number) IS
1240 select element_name,
1241 element_information1 element_category,
1242 element_information5 calc_ele_type_id,
1243 element_information18 spl_inp_ele_type_id -- Added for #8972240
1244 from pay_element_types_f
1245 where element_type_id = cp_ele_type_id
1246 and business_group_id = cp_business_grp_id;
1247
1248 -- Get the element name
1249 cursor c_get_ele_name (cp_ele_type_id number,
1250 cp_business_grp_id number) IS
1251 select element_name
1252 from pay_element_types_f
1253 where element_type_id = cp_ele_type_id
1254 and business_group_id = cp_business_grp_id;
1255
1256 -- Local Variable Declaration
1257 l_business_group_id number;
1258 l_date_of_mig date;
1259 l_count number;
1260 l_old_ele_type_id number;
1261 l_new_ele_type_id number;
1262 l_ele_category varchar2(10);
1263 l_old_ele_name varchar2(100);
1264 l_new_ele_name varchar2(100);
1265 l_eff_start_date date;
1266 l_eff_end_date date;
1267 l_calc_ele_type_id number;
1268 l_spl_inp_ele_type_id number; -- Added for #8972240
1269 l_flag boolean;
1270 custom_exception exception;
1271
1272 begin
1273
1274 /*
1275 * Initialization Code
1276 */
1277 gv_package_name := 'pay_us_garn_upgrade';
1278 l_business_group_id := fnd_global.per_business_group_id;
1279 l_flag := TRUE;
1280 -- Initialise Variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
1281
1282 /*
1283 * Initial Trace
1284 */
1285 -- hr_utility.trace_on(NULL, 'Dedn');
1286 hr_utility.trace('Entering ' || gv_package_name || '.upgrade_garnishment');
1287 hr_utility.trace('Values Passed From The Concurrent Program');
1288
1289 /*
1290 * Business Group Level Migration
1291 */
1292
1293 l_date_of_mig := fnd_date.canonical_to_date('2004/01/01');
1294 l_count := 0;
1295
1296 hr_utility.trace('Date of Migration ' || l_date_of_mig);
1297
1298
1299 open c_get_mig_elem_bg(p_elem_type_id);
1300 --
1301 loop
1302 fetch c_get_mig_elem_bg into l_old_ele_type_id
1303 ,l_eff_start_date
1304 ,l_eff_end_date;
1305
1306 exit when c_get_mig_elem_bg%NOTFOUND;
1307
1308 /*
1309 * STEP 1
1310 * Create a New Element for the Old element
1311 * Modify The Base Formula of the New element to refer the Old Balances.
1312 * Call to modify the Base Formula is inside the call to 'create_new_element'
1313 */
1314
1315 l_new_ele_type_id := create_new_element(
1316 p_old_ele_type_id => l_old_ele_type_id,
1317 p_business_grp_id => l_business_group_id,
1318 p_eff_start_date => l_eff_start_date,
1319 p_eff_end_date => l_eff_end_date);
1320 if l_new_ele_type_id = 0 then
1321 hr_utility.trace('Element Not Created');
1322 raise custom_exception;
1323 end if;
1324
1325
1326 /*
1327 * Get the New and Old element name and category.
1328 * The category will be same for both the elements
1329 */
1330 open c_get_ele_name_cat(l_new_ele_type_id,
1331 l_business_group_id);
1332 fetch c_get_ele_name_cat into l_new_ele_name,
1333 l_ele_category,
1334 l_calc_ele_type_id,
1335 l_spl_inp_ele_type_id; -- Added for #8972240
1336 close c_get_ele_name_cat;
1337
1338 open c_get_ele_name(l_old_ele_type_id,
1339 l_business_group_id);
1340 fetch c_get_ele_name into l_old_ele_name;
1341 close c_get_ele_name;
1342
1343 hr_utility.trace('Element Created ' || l_new_ele_name);
1344
1345 /*
1346 * Store the New Calcualtor elements element_type_id value in the
1347 * element_information4 column of the Old Base element.
1348 */
1349
1350 /* #8972240 Also updated the special input element type id value
1351 * in the element_information18 column of the Old Base element.
1352 */
1353 update pay_element_types_f
1354 set element_information4 = l_calc_ele_type_id,
1355 element_information18 = l_spl_inp_ele_type_id -- Added for #8972240
1356 where element_type_id = l_old_ele_type_id;
1357
1358 /*
1359 * STEP 2
1360 * Copy Formula Result rules from the New Base
1361 */
1362 copy_result_rules(l_old_ele_type_id
1363 ,l_new_ele_type_id
1364 ,l_business_group_id);
1365
1366 /*
1367 * STEP 3
1368 * Copy Balance Feeds from New Balance To Old Balance
1369 */
1370 l_flag := copy_balance_feeds(l_old_ele_name
1371 ,l_new_ele_name
1372 ,l_business_group_id);
1373 if NOT(l_flag) then
1374 hr_utility.trace('Could Not Copy Balance Feed');
1375 raise custom_exception;
1376 end if;
1377
1378
1379 /*
1380 * STEP 4
1381 * Delete Balance Feeds for New Balances
1382 */
1383 del_balance_feeds(l_new_ele_name
1384 ,l_business_group_id);
1385
1386
1387 /*
1388 * STEP 5
1389 * Modify The Formula Result Rules for the New Calculator formula
1390 * to feed the STOP_ENTRY result of the Old Base element instead of
1391 * the New Base element.
1392 */
1393 modify_calc_res_rule(l_old_ele_name
1394 ,l_new_ele_name
1395 ,l_business_group_id);
1396
1397 /*
1398 * Step 6
1399 * Delete the New Base Element
1400 */
1401 del_base_element(l_new_ele_name,
1402 l_business_group_id);
1403
1404 /*
1405 * Modify Display Sequence
1406 */
1407 mod_inp_vals(l_old_ele_name,
1408 l_ele_category,
1409 l_business_group_id);
1410
1411 end loop;
1412
1413 close c_get_mig_elem_bg;
1414
1415 hr_utility.trace('Leaving ' || gv_package_name || '.upgrade_garnishment');
1416 return;
1417
1418 EXCEPTION
1419 when CUSTOM_EXCEPTION then
1420 pay_core_utils.push_message(801, 'PAY_US_GARN_UPG', 'A');
1421 pay_core_utils.push_token ('Element Type ID', p_elem_type_id);
1422 pay_core_utils.push_token ('Element Name ', l_old_ele_name);
1423 if gv_location = 1 then
1424 pay_core_utils.push_token ('Error Creating New Element ', gv_location);
1425 elsif gv_location = 2 then
1426 pay_core_utils.push_token ('Error Creating Balance Feed ', gv_location);
1427 end if;
1428 hr_utility.raise_error;
1429 when others then
1430 pay_core_utils.push_message(801, 'PAY_US_GARN_UPG', 'A');
1431 pay_core_utils.push_token ('Element Type ID', p_elem_type_id);
1432 pay_core_utils.push_token ('Element Name ', l_old_ele_name);
1433 if gv_location = 1 then
1434 pay_core_utils.push_token ('Error Creating New Element ', gv_location);
1435 elsif gv_location = 2 then
1436 pay_core_utils.push_token ('Error Creating Balance Feed ', gv_location);
1437 end if;
1438 hr_utility.raise_error;
1439 end upgrade_garnishment;
1440
1441 /*****************************************************************************
1442 Name : qual_elem_upg
1443
1444 Description : This is the qualifying procedure which determines whether
1445 the element passed in as a parameter needs to be migrated.
1446 The conditions that are checked here are
1447 1. Should be Involuntary Deduction element.
1448 2. Should have been created using Old Architecture.
1449 3. Should not have been already migrated.
1450 4. Should have the end_date > '01-JAN-2004'
1451 *****************************************************************************/
1452 PROCEDURE qual_elem_upg(p_object_id varchar2,
1453 p_qualified out nocopy varchar2)
1454 IS
1455
1456 -- Verify if the element is of Involuntary Deductions Category
1457 -- and needs to be migrated
1458 cursor c_get_mig_elem_bg(cp_ele_type_id number,
1459 cp_mig_date date) IS
1460 select /*+ Choose */
1461 'Upgrade'
1462 from pay_element_types_f petf,
1463 pay_element_classifications pec
1464 where petf.element_type_id = cp_ele_type_id
1465 and petf.legislation_code is NULL
1466 and petf.effective_end_date > cp_mig_date
1467 and pec.classification_id = petf.classification_id
1468 and pec.classification_name = 'Involuntary Deductions'
1469 and petf.element_name not like '%Calculator'
1470 and petf.element_name not like '%Verifier'
1471 and petf.element_name not like '%Special Inputs'
1472 and petf.element_name not like '%Special Features'
1473 and petf.element_name not like '%Fees'
1474 and petf.element_name not like '%Priority'
1475 and petf.element_information4 is NULL
1476 and petf.element_information1 in ('CS', 'SS', 'AY', 'EL', 'ER', 'BO', 'CD', 'G', 'TL')
1477 and exists (select 'Exists'
1478 from pay_element_types_f petfi
1479 where petfi.element_name like petf.element_name || ' Verifier'
1480 and petfi.business_group_id = petf.business_group_id
1481 and petfi.legislation_code is NULL);
1482
1483 cursor c_get_curr_arch is
1484 select parameter_value
1485 from pay_action_parameters
1486 where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';
1487
1488 l_date_of_mig date;
1489 l_upg_stat varchar2(10);
1490 l_qualifier varchar2(10);
1491 lv_package_name varchar2(50);
1492 l_curr_arch varchar2(10);
1493
1494 BEGIN
1495 /*
1496 * Initializing Date used for migration.
1497 * Element having end date before '01-JAN-2004' are not migrated.
1498 */
1499 l_date_of_mig := fnd_date.canonical_to_date('2004/01/01');
1500 lv_package_name := 'pay_us_garn_upgrade';
1501
1502 hr_utility.trace('Entering ' || lv_package_name || '.qual_elem_upg');
1503
1504
1505 /*
1506 * Check current architecture.
1507 * If OLD architecture is selected currently then set the value for
1508 * 'US_ADVANCED_WAGE_ATTACHMENT' to 'Y' and make an explicit
1509 * commit. This ensures all elements are created using the New
1510 * architecture.
1511 */
1512 open c_get_curr_arch;
1513 fetch c_get_curr_arch into l_curr_arch;
1514 if c_get_curr_arch%FOUND then
1515 if upper(substr(l_curr_arch,1,1)) = 'N' then
1516 update pay_action_parameters
1517 set parameter_value = 'Y'
1518 where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';
1519 commit;
1520 end if;
1521 end if;
1522 close c_get_curr_arch;
1523
1524 open c_get_mig_elem_bg(p_object_id
1525 ,l_date_of_mig);
1526 fetch c_get_mig_elem_bg into l_upg_stat;
1527 if c_get_mig_elem_bg%NOTFOUND then
1528 l_qualifier := 'N';
1529 else
1530 l_qualifier := 'Y';
1531 end if;
1532 close c_get_mig_elem_bg;
1533
1534 p_qualified := l_qualifier;
1535
1536 if l_qualifier = 'Y' then
1537 hr_utility.trace('Element Type ID ' || p_object_id || 'Upgraded');
1538 end if;
1539 hr_utility.trace('Leaving ' || lv_package_name || '.qual_elem_upg');
1540 END qual_elem_upg;
1541
1542 end pay_us_garn_upgrade;
[Home] [Help]