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