[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_RETRO_UPGRADE
Source
1 PACKAGE BODY pay_gb_retro_upgrade AS
2 /* $Header: paygbretroupg.pkb 120.1.12010000.3 2008/08/06 06:32:56 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 Name : pay_gb_retro_upgrade
24
25 Description : This procedure is used to upgrade elements for
26 Enhanced Retropay.
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ------ ------- -----------------------------------
32 19-Jul-2005 rmakhija 115.0 Intial Version copied from
33 payusretroupg.pkb
34 19-Aug-2005 rmakhija 115.1 Excluded seeded elements
35 31-Aug-2005 rmakhija 115.2 added not exist clause in insert_retro_
36 comp_usages procedure to ensure the
37 process is re-runable
38 06-Sep-2005 rmakhija 115.3 Fixed delete from pay_leg_field_info
39 18-OCT-2006 rmakhija 115.5 5609218 Added ADV_RETRO_COMPONENT_USAGE leg
40 field info again
41 19-OCT-2006 rmakhija 115.6 5609218 Reversed changes done in previous ver
42 because the leg field info is not
43 needed to enable the button,
44 ADVANCED_RETRO leg rule shd enable it
45 */
46
47 gv_package_name VARCHAR2(100);
48 gn_time_span_id NUMBER;
49 gn_retro_component_id NUMBER;
50
51 PROCEDURE insert_retro_comp_usages
52 (p_business_group_id in number,
53 p_legislation_code in varchar2,
54 p_retro_component_id in number,
55 p_creator_id in number,
56 p_retro_comp_usage_id out nocopy number)
57 IS
58
59 ln_retro_component_usage_id NUMBER;
60 lv_procedure_name VARCHAR2(100);
61
62 BEGIN
63 lv_procedure_name := '.insert_retro_comp_usages';
64 fnd_file.put_line(fnd_file.log,'Entering ' || gv_package_name || lv_procedure_name);
65
66 select pay_retro_component_usages_s.nextval
67 into ln_retro_component_usage_id
68 from dual;
69
70 insert into pay_retro_component_usages
71 (retro_component_usage_id, retro_component_id, creator_id, creator_type,
72 default_component, reprocess_type, business_group_id, legislation_code,
73 creation_date, created_by, last_update_date, last_updated_by,
74 last_update_login, object_version_number)
75 SELECT ln_retro_component_usage_id, p_retro_component_id, p_creator_id,
76 'ET', 'Y', 'R', p_business_group_id, p_legislation_code,
77 sysdate, 2, sysdate, 2, 2, 1
78 FROM dual
79 WHERE NOT EXISTS ( SELECT 1 FROM pay_retro_component_usages
80 WHERE retro_component_id = p_retro_component_id
81 AND creator_id = p_creator_id
82 AND creator_type = 'ET');
83
84 p_retro_comp_usage_id := ln_retro_component_usage_id;
85 fnd_file.put_line(fnd_file.log,'p_retro_comp_usage_id= ' || p_retro_comp_usage_id);
86 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || lv_procedure_name);
87
88 exception
89 when others then
90 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
91 fnd_file.put_line(fnd_file.log,'ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
92 raise;
93 END insert_retro_comp_usages;
94
95
96 PROCEDURE insert_element_span_usages
97 (p_business_group_id in number,
98 p_retro_element_type_id in number,
99 p_legislation_code in varchar2,
100 p_time_span_id in number,
101 p_retro_comp_usage_id in number)
102 IS
103
104 lv_procedure_name VARCHAR2(100);
105
106 BEGIN
107 lv_procedure_name := '.insert_element_span_usages';
108 fnd_file.put_line(fnd_file.log,'Entering ' || gv_package_name || lv_procedure_name);
109
110 fnd_file.put_line(fnd_file.log,'p_business_group_id ='|| p_business_group_id);
111 fnd_file.put_line(fnd_file.log,'p_time_span_id ='|| p_time_span_id);
112 fnd_file.put_line(fnd_file.log,'p_retro_comp_usage_id ='|| p_retro_comp_usage_id);
116 (element_span_usage_id, business_group_id, time_span_id,
113 fnd_file.put_line(fnd_file.log,'p_retro_element_type_id ='|| p_retro_element_type_id);
114
115 insert into pay_element_span_usages
117 retro_component_usage_id, retro_element_type_id,
118 creation_date, created_by, last_update_date, last_updated_by,
119 last_update_login, object_version_number)
120 --values
121 SELECT pay_element_span_usages_s.nextval,
122 p_business_group_id, p_time_span_id,
123 p_retro_comp_usage_id, p_retro_element_type_id,
124 sysdate, 2, sysdate, 2, 2, 1
125 FROM dual
126 WHERE not exists ( SELECT 1 FROM pay_element_span_usages pesu
127 WHERE pesu.business_group_id = p_business_group_id
128 AND pesu.legislation_code IS NULL
129 AND pesu.time_span_id = p_time_span_id
130 AND retro_component_usage_id = p_retro_comp_usage_id);
131
132 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || lv_procedure_name);
133
134 exception
135 when others then
136 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
137 fnd_file.put_line(fnd_file.log,'ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
138 raise;
139 END insert_element_span_usages;
140
141
142 /****************************************************************************
143 ** Name : qualify_element
144 **
145 ** Description: This is the qualifying procedure which determines whether
146 ** the element passed in as a parameter needs to be migrated.
147 ** The conditions that are checked here are
148 ** 1. Element is part of a Retro Set used for Retro
149 **
150 ****************************************************************************/
151 PROCEDURE qualify_element(p_object_id in varchar2
152 ,p_qualified out nocopy varchar2)
153 IS
154 cursor c_element_class(cp_element_type_id in number) is
155 select pet.classification_id, pet.element_name, pet.legislation_code, pet.business_group_id, pec.classification_name
156 from pay_element_types_f pet, pay_element_classifications pec
157 where pet.element_type_id = cp_element_type_id
158 and pet.classification_id = pec.classification_id;
159
160 cursor c_legislation_code(cp_business_group_id in number) is
161 select legislation_code
162 from per_business_groups
163 where business_group_id = cp_business_group_id;
164
165 cursor c_element_set(cp_element_type_id in number
166 ,cp_classification_id in number
167 ,cp_legislation_code in varchar2) is
168 select petr.element_set_id
169 from pay_element_type_rules petr
170 where petr.element_type_id = cp_element_type_id
171 and petr.include_or_exclude = 'I'
172 union all
173 select pes.element_set_id
174 from pay_ele_classification_rules pecr,
175 pay_element_types_f pet,
176 pay_element_sets pes
177 where pet.classification_id = pecr.classification_id
178 and pes.element_set_id = pecr.element_set_id
179 and (pes.business_group_id = pet.business_group_id
180 or pet.legislation_code = cp_legislation_code)
181 and pet.element_type_id = cp_element_type_id
182 and pecr.classification_id = cp_classification_id
183 minus
184 select petr.element_set_id
185 from pay_element_type_rules petr
186 where petr.element_type_id = cp_element_type_id
187 and petr.include_or_exclude = 'E';
188
189 cursor c_element_check(cp_element_set_id in number) is
190 select 1
191 from pay_payroll_actions ppa
192 where ppa.action_type = 'L'
193 and ppa.element_set_id = cp_element_set_id;
194
195 cursor c_retro_rule_check(cp_rule_type in varchar2
196 ,cp_legislation_code in Varchar2) is
197 select 'Y'
198 from pay_legislation_rules
199 where legislation_code = cp_legislation_code
200 and rule_type = cp_rule_type;
201 --
202 ln_classification_id NUMBER;
203 ln_business_group_id NUMBER;
204 ln_element_set_id NUMBER;
205 ln_element_used NUMBER;
206 lv_qualified VARCHAR2(1);
207 lv_element_name VARCHAR2(100);
208 lv_classification_name pay_element_classifications.classification_name%TYPE;
209 lv_procedure_name VARCHAR2(100);
210 lv_legislation_code VARCHAR2(150);
211 ln_exists VARCHAR2(1);
212 l_fi_rule_mode pay_legislative_field_info.rule_mode%TYPE;
213
214
215 TYPE character_data_table IS TABLE OF VARCHAR2(280)
216 INDEX BY BINARY_INTEGER;
217
218 ltt_rule_type character_data_table;
219 ltt_rule_mode character_data_table;
220 lv_ele_leg_code VARCHAR2(150);
221 ln_ele_bg_id NUMBER;
222
223
224 BEGIN
225
226 fnd_file.put_line(fnd_file.log,'Entering ' || gv_package_name || lv_procedure_name);
227 fnd_file.put_line(fnd_file.log,'Checking element type id '||p_object_id);
228 open c_element_class(p_object_id);
229 fetch c_element_class into ln_classification_id,
230 lv_element_name,
231 lv_legislation_code,
232 ln_business_group_id,
233 lv_classification_name;
234 close c_element_class;
235 --
236 -- Store original values from the lement to disqualify
237 -- seeded elements
241 if lv_legislation_code is null and
238 lv_ele_leg_code := lv_legislation_code;
239 ln_ele_bg_id := ln_business_group_id;
240 --
242 ln_business_group_id is not null then
243 open c_legislation_code(ln_business_group_id);
244 FETCH c_legislation_code into lv_legislation_code;
245 close c_legislation_code;
246 end if;
247
248 ltt_rule_type(1) := 'RETRO_DELETE';
249 ltt_rule_mode(1) := 'N';
250 ltt_rule_type(2) := 'ADVANCED_RETRO';
251 ltt_rule_mode(2) := 'Y';
252 ltt_rule_type(3) := 'ADJUSTMENT_EE_SOURCE';
253 ltt_rule_mode(3) := 'T';
254 ltt_rule_type(4) := 'RETRO_TU_CONTEXT';
255 ltt_rule_mode(4) := 'N';
256 ltt_rule_type(5) := 'RETRO_STD_CONTEXTS';
257 ltt_rule_mode(5) := 'N';
258 ltt_rule_type(6) := 'RETROELEMENT_CHECK';
259 ltt_rule_mode(6) := 'Y';
260
261 FOR i in 1 ..6 LOOP
262 OPEN c_retro_rule_check(ltt_rule_type(i),lv_legislation_code) ;
263 FETCH c_retro_rule_check into ln_exists;
264
265 IF c_retro_rule_check%FOUND THEN
266
267 delete pay_legislation_rules
268 where legislation_code = lv_legislation_code
269 and rule_type = ltt_rule_type(i);
270
271 END IF;
272
273 INSERT INTO pay_legislation_rules(legislation_code,rule_type,rule_mode)
274 VALUES( lv_legislation_code,ltt_rule_type(i),ltt_rule_mode(i));
275
276 CLOSE c_retro_rule_check;
277
278 END LOOP;
279
280 --
281
282 lv_qualified := 'N';
283 lv_procedure_name := '.qualify_element';
284
285
286 open c_element_set(p_object_id, ln_classification_id,lv_legislation_code);
287 loop
288 fetch c_element_set into ln_element_set_id;
289 if c_element_set%notfound then
290 exit;
291 end if;
292
293 fnd_file.put_line(fnd_file.log,'Element Set ID ' || ln_element_set_id);
294 open c_element_check(ln_element_set_id);
295 fetch c_element_check into ln_element_used;
296 if c_element_check%found then
297 fnd_file.put_line(fnd_file.log, 'Checking legislation code '||lv_ele_leg_code);
298 if lv_ele_leg_code IS NOT NULL then
299 fnd_file.put_line(fnd_file.log,' Seeded element '||lv_element_name||' ('||p_object_id||') can not be upgraded.');
300 lv_qualified := 'N';
301 else
302 fnd_file.put_line(fnd_file.log,'Element retro-ed before, check classification.');
303 if lv_classification_name in ('Court Orders',
304 'PAYE', 'NI', 'SSP Non Payment',
305 'SMP Non Payment',
306 'SPP Birth Non Payment',
307 'SPP Adoption Non Payment',
308 'SAP Non Payment')
309 then
310 fnd_file.put_line(fnd_file.log,'Classification '||lv_classification_name||' is not supported for this upgrade.');
311 lv_qualified := 'N';
312 else
313 lv_qualified := 'Y';
314 fnd_file.put_line(fnd_file.log,'UPGRADE Element ' || lv_element_name ||
315 '(' || p_object_id || ')');
316 end if;
317 end if;
318 --
319 exit;
320 else
321 lv_qualified := 'N';
322 fnd_file.put_line(fnd_file.log,'Element ' || lv_element_name ||
323 '(' || p_object_id || ') does not need to be upgraded');
324 end if;
325 close c_element_check;
326 end loop;
327 close c_element_set;
328
329 p_qualified := lv_qualified;
330 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || lv_procedure_name);
331
332 exception
333 when others then
334 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
335 fnd_file.put_line(fnd_file.log,'ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
336 raise;
337 END qualify_element;
338
339
340 PROCEDURE upgrade_element(p_element_type_id in number)
341 IS
342 cursor c_element_dtl(cp_element_type_id in number) is
343 select business_group_id, legislation_code, classification_id,
344 nvl(retro_summ_ele_id, pet.element_type_id),
345 element_name
346 from pay_element_types_f pet
347 where pet.element_type_id = cp_element_type_id
348 order by pet.effective_start_date desc;
349
350 cursor c_legislation_code(cp_business_group_id in number) is
351 select legislation_code
352 from per_business_groups
353 where business_group_id = cp_business_group_id;
354
355 cursor c_element_set(cp_element_type_id in number
356 ,cp_classification_id in number
357 ,cp_legislation_code in varchar2) is
358 select petr.element_set_id
359 from pay_element_type_rules petr
360 where petr.element_type_id = cp_element_type_id
361 and petr.include_or_exclude = 'I'
362 union all
363 select pes.element_set_id
364 from pay_ele_classification_rules pecr,
365 pay_element_types_f pet,
366 pay_element_sets pes
367 where pet.classification_id = pecr.classification_id
368 and pes.element_set_id = pecr.element_set_id
369 and (pes.business_group_id = pet.business_group_id
370 or pet.legislation_code = cp_legislation_code)
371 and pet.element_type_id = cp_element_type_id
372 and pecr.classification_id = cp_classification_id
376 where petr.element_type_id = cp_element_type_id
373 minus
374 select petr.element_set_id
375 from pay_element_type_rules petr
377 and petr.include_or_exclude = 'E';
378
379 cursor c_get_business_group(cp_element_set_id in number
380 ,cp_legislation_code in varchar2) is
381 select hoi.organization_id
382 from hr_organization_information hoi,
383 hr_organization_information hoi2
384 where hoi.org_information_context = 'CLASS'
385 and hoi.org_information1 = 'HR_BG'
386 and hoi.organization_id = hoi2.organization_id
387 and hoi2.org_information_context = 'Business Group Information'
388 and hoi2.org_information9 = cp_legislation_code
389 and exists (select 1 from pay_payroll_actions ppa
390 where ppa.business_group_id = hoi.organization_id
391 and ppa.action_type = 'L'
392 and ppa.element_set_id = cp_element_set_id
393 );
394
395 cursor c_retro_info(cp_legislation_code in varchar2) is
396 select retro_component_id, pts.time_span_id
397 from pay_retro_components prc,
398 pay_time_spans pts
399 where pts.creator_id = prc.retro_component_id
400 and prc.legislation_code = cp_legislation_code
401 and prc.short_name = 'UK_Enh_Retro';
402
403 ln_ele_business_group_id NUMBER;
404 ln_business_group_id NUMBER;
405 ln_classification_id NUMBER;
406 ln_legislation_code VARCHAR2(10);
407 lv_legislation_code VARCHAR2(10);
408 ln_element_set_id NUMBER;
409 ln_retro_element_type_id NUMBER;
410 ln_retro_comp_usage_id NUMBER;
411 ln_count NUMBER;
412 lv_element_name VARCHAR2(100);
413 lv_procedure_name VARCHAR2(100);
414
415 TYPE numeric_data_table IS TABLE OF NUMBER
416 INDEX BY BINARY_INTEGER;
417
418 ltt_business_group numeric_data_table;
419 BEGIN
420 lv_procedure_name := '.upgrade_element';
421 fnd_file.put_line(fnd_file.log,'Entering ' || gv_package_name || lv_procedure_name);
422
423 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
424 open c_element_dtl(p_element_type_id);
425 fetch c_element_dtl into ln_ele_business_group_id, ln_legislation_code,
426 ln_classification_id, ln_retro_element_type_id,
427 lv_element_name;
428 close c_element_dtl;
429 fnd_file.put_line(fnd_file.log,'p_element_type_id ='|| p_element_type_id);
430 fnd_file.put_line(fnd_file.log,'lv_element_name ='|| lv_element_name);
431 fnd_file.put_line(fnd_file.log,'ln_legislation_code ='|| ln_legislation_code);
432 fnd_file.put_line(fnd_file.log,'ln_ele_business_group_id ='|| ln_ele_business_group_id);
433 fnd_file.put_line(fnd_file.log,'ln_retro_element_type_id ='|| ln_retro_element_type_id);
434
435 if ln_legislation_code is null and
436 ln_ele_business_group_id is not null then
440 else
437 open c_legislation_code(ln_ele_business_group_id);
438 FETCH c_legislation_code into lv_legislation_code;
439 close c_legislation_code;
441 lv_legislation_code := ln_legislation_code;
442 end if;
443 fnd_file.put_line(fnd_file.log,'lv_legislation_code ='|| lv_legislation_code);
444
445 if gn_retro_component_id is null then
446 fnd_file.put_line(fnd_file.log,'getting gn_retro_component_id ='|| gn_retro_component_id);
447 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
448 open c_retro_info(lv_legislation_code);
449 fetch c_retro_info into gn_retro_component_id
450 ,gn_time_span_id;
451 close c_retro_info;
452 end if;
453 fnd_file.put_line(fnd_file.log,'gn_retro_component_id ='|| gn_retro_component_id);
454 fnd_file.put_line(fnd_file.log,'gn_time_span_id ='|| gn_time_span_id);
455
456 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
457
458 if ln_legislation_code is not null and
459 ln_ele_business_group_id is null then
460
461 fnd_file.put_line(fnd_file.log,'Seeded Element');
462 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
463 insert_retro_comp_usages
464 (p_business_group_id => null
465 ,p_legislation_code => ln_legislation_code
466 ,p_retro_component_id => gn_retro_component_id
467 ,p_creator_id => p_element_type_id
468 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
472 loop
469
470 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
471 open c_element_set(p_element_type_id, ln_classification_id,ln_legislation_code);
473 fetch c_element_set into ln_element_set_id;
474 if c_element_set%notfound then
475 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
476 exit;
477 end if;
478
479 open c_get_business_group(ln_element_set_id,ln_legislation_code);
480 loop
481 fetch c_get_business_group into ln_business_group_id;
482 if c_get_business_group%notfound then
483 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
484 exit;
485 end if;
486
487 -- ln_count := ltt_business_group.count;
488 -- ltt_business_group(ln_count) := ln_business_group_id;
489
490 fnd_file.put_line(fnd_file.log,'ln_business_group_id ='|| ln_business_group_id);
491 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
492
493 insert_element_span_usages
494 (p_business_group_id => ln_business_group_id
495 ,p_retro_element_type_id => ln_retro_element_type_id
496 ,p_legislation_code => ln_legislation_code
497 ,p_time_span_id => gn_time_span_id
498 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
499
500 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
501 end loop;
502 close c_get_business_group;
503 end loop;
504 close c_element_set;
505 end if;
506 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
507
508 if ln_legislation_code is null and
509 ln_ele_business_group_id is not null then
510
511 fnd_file.put_line(fnd_file.log,'Custom Element');
512 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
513 insert_retro_comp_usages
514 (p_business_group_id => ln_ele_business_group_id
515 ,p_legislation_code => null
516 ,p_retro_component_id => gn_retro_component_id
517 ,p_creator_id => p_element_type_id
518 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
519 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
520 insert_element_span_usages
521 (p_business_group_id => ln_ele_business_group_id
522 ,p_retro_element_type_id => ln_retro_element_type_id
523 ,p_legislation_code => null
524 ,p_time_span_id => gn_time_span_id
525 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
526 end if;
527
528 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || lv_procedure_name);
529 exception
530 when others then
531 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
532 fnd_file.put_line(fnd_file.log,'ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
533 raise;
534 END upgrade_element;
535
536 BEGIN
537 gv_package_name := 'pay_gb_retro_upgrade';
538
539 END pay_gb_retro_upgrade;