[Home] [Help]
PACKAGE BODY: APPS.PAY_US_RETRO_UPGRADE
Source
1 PACKAGE BODY pay_us_retro_upgrade AS
2 /* $Header: payusretroupg.pkb 120.5 2012/01/19 08:22:04 rpahune 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_us_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 05-DEC-2004 ahanda 115.0 Intial Version
33 10-DEC-2004 ahanda 115.1 Fixed dbdrv issue.
34 19-DEC-2004 fusman 115.2 Added code to insert legislation rules.
35 29-APR-2005 mmukherj 115.3 Removed hard coded legislation_code
36 'US' from the package.The
37 legislation_code are taken from the
38 legislation_code attached to the
39 elements,if it is a seeded element or
40 from the legislation_code
41 of the Business Group for the elements,
42 if it is an user defined element.
43 21-SEP-2005 ahanda 115.3 Changed the insertion of legislation
44 rules. Data is inserted if it does not
45 exist.
46 06-06-2008 svannian 115.4 to avoid unique constraint voilation
47 error when upgradation is ran for the second time.
48 */
49
50 gv_package_name VARCHAR2(100);
51 gn_time_span_id NUMBER;
52 gn_retro_component_id NUMBER;
53
54 PROCEDURE insert_retro_comp_usages
55 (p_business_group_id in number,
56 p_legislation_code in varchar2,
57 p_retro_component_id in number,
58 p_creator_id in number,
59 p_retro_comp_usage_id out nocopy number)
60 IS
61
62 ln_retro_component_usage_id NUMBER;
63 lv_procedure_name VARCHAR2(100);
64
65 BEGIN
66 lv_procedure_name := '.insert_retro_comp_usages';
67 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
68
69 select pay_retro_component_usages_s.nextval
70 into ln_retro_component_usage_id
71 from dual;
72
73 insert into pay_retro_component_usages
74 (retro_component_usage_id, retro_component_id, creator_id, creator_type,
75 default_component, reprocess_type, business_group_id, legislation_code,
76 creation_date, created_by, last_update_date, last_updated_by,
77 last_update_login, object_version_number)
78 select ln_retro_component_usage_id, p_retro_component_id, p_creator_id,
79 'ET', 'Y', 'R', p_business_group_id, p_legislation_code,
80 sysdate, 2, sysdate, 2, 2, 1
81 from dual
82 WHERE NOT EXISTS ( SELECT 1 FROM pay_retro_component_usages
83 WHERE retro_component_id = p_retro_component_id
84 AND creator_id = p_creator_id
85 AND creator_type = 'ET'); /* 7138282 */
86
87 p_retro_comp_usage_id := ln_retro_component_usage_id;
88 hr_utility.trace('p_retro_comp_usage_id= ' || p_retro_comp_usage_id);
89 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
90
91 exception
92 when others then
93 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
94 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
95 raise;
96 END insert_retro_comp_usages;
97
98
99 PROCEDURE insert_element_span_usages
100 (p_business_group_id in number,
101 p_retro_element_type_id in number,
102 p_legislation_code in varchar2,
103 p_time_span_id in number,
104 p_retro_comp_usage_id in number)
105 IS
106
107 lv_procedure_name VARCHAR2(100);
108
109 BEGIN
110 lv_procedure_name := '.insert_element_span_usages';
111 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
112
113 hr_utility.trace('p_business_group_id ='|| p_business_group_id);
114 -- hr_utility.trace('p_legcd ='|| p_legislation_code);
115 hr_utility.trace('p_time_span_id ='|| p_time_span_id);
116 hr_utility.trace('p_retro_comp_usage_id ='|| p_retro_comp_usage_id);
117 hr_utility.trace('p_retro_element_type_id ='|| p_retro_element_type_id);
118
119 insert into pay_element_span_usages
120 (element_span_usage_id, business_group_id, time_span_id,
121 retro_component_usage_id, retro_element_type_id,
122 creation_date, created_by, last_update_date, last_updated_by,
123 last_update_login, object_version_number)
124 select pay_element_span_usages_s.nextval, p_business_group_id, p_time_span_id,
125 p_retro_comp_usage_id, p_retro_element_type_id,
126 sysdate, 2, sysdate, 2, 2, 1
127 from dual
128 WHERE not exists ( SELECT 1 FROM pay_element_span_usages pesu
129 WHERE pesu.business_group_id = p_business_group_id
130 AND pesu.legislation_code IS NULL
131 AND pesu.time_span_id = p_time_span_id
132 AND retro_component_usage_id = p_retro_comp_usage_id); /* 7138282 */
133
134 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
135
136 exception
137 when others then
138 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
139 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
140 raise;
141 END insert_element_span_usages;
142
143
144 /****************************************************************************
148 ** the element passed in as a parameter needs to be migrated.
145 ** Name : qualify_element
146 **
147 ** Description: This is the qualifying procedure which determines whether
149 ** The conditions that are checked here are
150 ** 1. Element is part of a Retro Set used for Retro
151 **
152 ****************************************************************************/
153 PROCEDURE qualify_element(p_object_id in varchar2
154 ,p_qualified out nocopy varchar2)
155 IS
156 cursor c_element_class(cp_element_type_id in number) is
157 select classification_id, element_name, legislation_code, business_group_id
158 from pay_element_types_f
159 where element_type_id = cp_element_type_id;
160
161 cursor c_legislation_code(cp_business_group_id in number) is
162 select legislation_code
163 from per_business_groups
164 where business_group_id = cp_business_group_id;
165
166 cursor c_element_set(cp_element_type_id in number
167 ,cp_classification_id in number
168 ,cp_legislation_code in varchar2) is
169 select petr.element_set_id
170 from pay_element_type_rules petr
171 where petr.element_type_id = cp_element_type_id
172 and petr.include_or_exclude = 'I'
173 union all
174 select pes.element_set_id
175 from pay_ele_classification_rules pecr,
176 pay_element_types_f pet,
177 pay_element_sets pes
178 where pet.classification_id = pecr.classification_id
179 and pes.element_set_id = pecr.element_set_id
180 and (pes.business_group_id = pet.business_group_id
181 or pet.legislation_code = cp_legislation_code)
182 and pet.element_type_id = cp_element_type_id
183 and pecr.classification_id = cp_classification_id
184 minus
185 select petr.element_set_id
186 from pay_element_type_rules petr
187 where petr.element_type_id = cp_element_type_id
188 and petr.include_or_exclude = 'E';
189
190 cursor c_element_check(cp_element_set_id in number) is
191 select 1
192 from pay_payroll_actions ppa
193 where ppa.action_type = 'L'
194 and ppa.element_set_id = cp_element_set_id;
195
196 cursor c_retro_rule_check(cp_rule_type in varchar2
197 ,cp_legislation_code in Varchar2) is
198 select 'Y'
199 from pay_legislation_rules
200 where legislation_code = cp_legislation_code
201 and rule_type = cp_rule_type;
202
203 ln_classification_id NUMBER;
204 ln_business_group_id NUMBER;
205 ln_element_set_id NUMBER;
206 ln_element_used NUMBER;
207 lv_qualified VARCHAR2(1);
208 lv_element_name VARCHAR2(100);
209 lv_procedure_name VARCHAR2(100);
210 lv_legislation_code VARCHAR2(150);
211 ln_exists VARCHAR2(1);
212
213 TYPE character_data_table IS TABLE OF VARCHAR2(280)
214 INDEX BY BINARY_INTEGER;
215
216 ltt_rule_type character_data_table;
217 ltt_rule_mode character_data_table;
218
219
220 BEGIN
221
222 lv_procedure_name := '.qualify_element';
223 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
224 lv_qualified := 'N';
225 lv_legislation_code := null;
226 ln_business_group_id := null;
227 ln_classification_id := null;
228 lv_element_name := null;
229
230 open c_element_class(p_object_id);
231 fetch c_element_class into ln_classification_id,
232 lv_element_name,
233 lv_legislation_code,
234 ln_business_group_id;
235 close c_element_class;
236
237 if lv_legislation_code is null and
238 ln_business_group_id is not null then
239 open c_legislation_code(ln_business_group_id);
240 FETCH c_legislation_code into lv_legislation_code;
241 close c_legislation_code;
242 end if;
243
244 ltt_rule_type(1) := 'RETRO_DELETE';
245 ltt_rule_mode(1) := 'N';
246 ltt_rule_type(2) := 'ADVANCED_RETRO';
247 ltt_rule_mode(2) := 'Y';
248 ltt_rule_type(3) := 'ADJUSTMENT_EE_SOURCE';
249 ltt_rule_mode(3) := 'T';
250
251 FOR i in 1 ..3 LOOP
252 OPEN c_retro_rule_check(ltt_rule_type(i),lv_legislation_code) ;
253 FETCH c_retro_rule_check into ln_exists;
254 IF c_retro_rule_check%NOTFOUND THEN
255 INSERT INTO pay_legislation_rules
256 (legislation_code, rule_type, rule_mode) VALUES
257 (lv_legislation_code, ltt_rule_type(i), ltt_rule_mode(i));
258 END IF;
259 CLOSE c_retro_rule_check;
260 END LOOP;
261
262 open c_element_set(p_object_id
263 ,ln_classification_id
264 ,lv_legislation_code);
265 loop
266 fetch c_element_set into ln_element_set_id;
267 if c_element_set%notfound then
268 exit;
269 end if;
270
271 hr_utility.trace('Element Set ID ' || ln_element_set_id);
272 open c_element_check(ln_element_set_id);
273 fetch c_element_check into ln_element_used;
274 if c_element_check%found then
275 lv_qualified := 'Y';
276 hr_utility.trace('UPGRADE Element ' || lv_element_name ||
277 '(' || p_object_id || ')');
278 exit;
279 else
280 lv_qualified := 'N';
281 hr_utility.trace('Element ' || lv_element_name ||
282 '(' || p_object_id || ') does not need to be upgraded');
283 end if;
284 close c_element_check;
285 end loop;
286 close c_element_set;
287
288 p_qualified := lv_qualified;
289 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
290
291 exception
292 when others then
293 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
294 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
295 raise;
296 END qualify_element;
297
298
299 PROCEDURE upgrade_element(p_element_type_id in number)
300 IS
301 cursor c_element_dtl(cp_element_type_id in number) is
302 select business_group_id, legislation_code, classification_id,
303 nvl(retro_summ_ele_id, pet.element_type_id),
304 element_name
305 from pay_element_types_f pet
306 where pet.element_type_id = cp_element_type_id
307 order by pet.effective_start_date desc;
308
309 cursor c_legislation_code(cp_business_group_id in number) is
310 select legislation_code
311 from per_business_groups
312 where business_group_id = cp_business_group_id;
313
314 cursor c_element_set(cp_element_type_id in number
315 ,cp_classification_id in number
316 ,cp_legislation_code in varchar2) is
317 select petr.element_set_id
318 from pay_element_type_rules petr
319 where petr.element_type_id = cp_element_type_id
320 and petr.include_or_exclude = 'I'
321 union all
322 select pes.element_set_id
323 from pay_ele_classification_rules pecr,
324 pay_element_types_f pet,
325 pay_element_sets pes
326 where pet.classification_id = pecr.classification_id
327 and pes.element_set_id = pecr.element_set_id
328 and (pes.business_group_id = pet.business_group_id
329 or pet.legislation_code = cp_legislation_code)
330 and pet.element_type_id = cp_element_type_id
331 and pecr.classification_id = cp_classification_id
332 minus
333 select petr.element_set_id
334 from pay_element_type_rules petr
335 where petr.element_type_id = cp_element_type_id
336 and petr.include_or_exclude = 'E';
337
338 cursor c_get_business_group(cp_element_set_id in number
339 ,cp_legislation_code in varchar2) is
340 select hoi.organization_id
341 from hr_organization_information hoi,
342 hr_organization_information hoi2
343 where hoi.org_information_context = 'CLASS'
344 and hoi.org_information1 = 'HR_BG'
345 and hoi.organization_id = hoi2.organization_id
346 and hoi2.org_information_context = 'Business Group Information'
347 and hoi2.org_information9 = cp_legislation_code
348 and exists (select 1 from pay_payroll_actions ppa
349 where ppa.business_group_id = hoi.organization_id
350 and ppa.action_type = 'L'
351 and ppa.element_set_id = cp_element_set_id
352 );
353
354 cursor c_retro_info(cp_legislation_code in varchar2) is
355 select retro_component_id, pts.time_span_id
356 from pay_retro_components prc,
357 pay_time_spans pts
358 where pts.creator_id = prc.retro_component_id
359 and prc.legislation_code = cp_legislation_code
360 and prc.short_name = 'Retropay';
361
362 ln_ele_business_group_id NUMBER;
363 ln_business_group_id NUMBER;
364 ln_classification_id NUMBER;
365 ln_legislation_code VARCHAR2(10);
366 lv_legislation_code VARCHAR2(10);
367 ln_element_set_id NUMBER;
368 ln_retro_element_type_id NUMBER;
369 ln_retro_comp_usage_id NUMBER;
370 ln_count NUMBER;
371 lv_element_name VARCHAR2(100);
372 lv_procedure_name VARCHAR2(100);
373
374 TYPE numeric_data_table IS TABLE OF NUMBER
375 INDEX BY BINARY_INTEGER;
376
377 ltt_business_group numeric_data_table;
378 BEGIN
379 lv_procedure_name := '.upgrade_element';
380 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
381
382 hr_utility.set_location(gv_package_name || lv_procedure_name, 30);
383 open c_element_dtl(p_element_type_id);
384 fetch c_element_dtl into ln_ele_business_group_id, ln_legislation_code,
385 ln_classification_id, ln_retro_element_type_id,
386 lv_element_name;
387 close c_element_dtl;
388 hr_utility.trace('p_element_type_id ='|| p_element_type_id);
389 hr_utility.trace('lv_element_name ='|| lv_element_name);
390 hr_utility.trace('ln_legislation_code ='|| ln_legislation_code);
391 hr_utility.trace('ln_ele_business_group_id ='|| ln_ele_business_group_id);
392 hr_utility.trace('ln_retro_element_type_id ='|| ln_retro_element_type_id);
393
394 if ln_legislation_code is null and
395 ln_ele_business_group_id is not null then
396 open c_legislation_code(ln_ele_business_group_id);
397 FETCH c_legislation_code into lv_legislation_code;
398 close c_legislation_code;
399 else
400 lv_legislation_code := ln_legislation_code;
401 end if;
402 hr_utility.trace('lv_legislation_code ='|| lv_legislation_code);
403
404 if gn_retro_component_id is null then
405 hr_utility.trace('getting gn_retro_component_id ='|| gn_retro_component_id);
406 hr_utility.set_location(gv_package_name || lv_procedure_name, 20);
407 open c_retro_info(lv_legislation_code);
408 fetch c_retro_info into gn_retro_component_id
409 ,gn_time_span_id;
410 close c_retro_info;
411 end if;
412 hr_utility.trace('gn_retro_component_id ='|| gn_retro_component_id);
413 hr_utility.trace('gn_time_span_id ='|| gn_time_span_id);
414
415 hr_utility.set_location(gv_package_name || lv_procedure_name, 40);
416
417 if ln_legislation_code is not null and
418 ln_ele_business_group_id is null then
419
420 hr_utility.trace('Seeded Element');
421 hr_utility.set_location(gv_package_name || lv_procedure_name, 60);
422 insert_retro_comp_usages
423 (p_business_group_id => null
424 ,p_legislation_code => ln_legislation_code
425 ,p_retro_component_id => gn_retro_component_id
426 ,p_creator_id => p_element_type_id
427 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
428
429 hr_utility.set_location(gv_package_name || lv_procedure_name, 70);
430 open c_element_set(p_element_type_id, ln_classification_id,ln_legislation_code);
431 loop
432 fetch c_element_set into ln_element_set_id;
433 if c_element_set%notfound then
434 hr_utility.set_location(gv_package_name || lv_procedure_name, 99999);
435 exit;
436 end if;
437
438 open c_get_business_group(ln_element_set_id,ln_legislation_code);
439 loop
440 fetch c_get_business_group into ln_business_group_id;
441 if c_get_business_group%notfound then
442 hr_utility.set_location(gv_package_name || lv_procedure_name, 8888);
443 exit;
444 end if;
445
446 -- ln_count := ltt_business_group.count;
447 -- ltt_business_group(ln_count) := ln_business_group_id;
448
449 hr_utility.trace('ln_business_group_id ='|| ln_business_group_id);
450 hr_utility.set_location(gv_package_name || lv_procedure_name, 80);
451
452 insert_element_span_usages
453 (p_business_group_id => ln_business_group_id
454 ,p_retro_element_type_id => ln_retro_element_type_id
455 ,p_legislation_code => ln_legislation_code
456 ,p_time_span_id => gn_time_span_id
457 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
458
459 hr_utility.set_location(gv_package_name || lv_procedure_name, 90);
460 end loop;
461 close c_get_business_group;
462 end loop;
463 close c_element_set;
464 end if;
465 hr_utility.set_location(gv_package_name || lv_procedure_name, 100);
466
467 if ln_legislation_code is null and
468 ln_ele_business_group_id is not null then
469
470 hr_utility.trace('Custom Element');
471 hr_utility.set_location(gv_package_name || lv_procedure_name, 110);
472 insert_retro_comp_usages
473 (p_business_group_id => ln_ele_business_group_id
474 ,p_legislation_code => null
475 ,p_retro_component_id => gn_retro_component_id
476 ,p_creator_id => p_element_type_id
477 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
478 hr_utility.set_location(gv_package_name || lv_procedure_name, 120);
479 insert_element_span_usages
480 (p_business_group_id => ln_ele_business_group_id
481 ,p_retro_element_type_id => ln_retro_element_type_id
482 ,p_legislation_code => null
483 ,p_time_span_id => gn_time_span_id
484 ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
485 end if;
486
487 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
488 exception
489 when others then
490 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
491 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
492 raise;
493 END upgrade_element;
494
495 BEGIN
496 -- hr_utility.trace_on(null, 'US_RETRO_UPG');
497 gv_package_name := 'pay_us_retro_upgrade';
498
499 END pay_us_retro_upgrade;