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