[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_ENH_RETRO_PAY
Source
4 gn_time_span_id NUMBER;
1 PACKAGE body PAY_GB_ENH_RETRO_PAY AS
2 /* $Header: pygberpy.pkb 120.0 2011/02/02 14:14:54 pprvenka noship $ */
3 gv_package_name VARCHAR2(100);
5 gn_retro_component_id NUMBER;
6 PROCEDURE migrate_element_set
7 AS
8 CURSOR c_get_element_type_id
9 IS
10 SELECT DISTINCT
11 pet.element_type_id
12 FROM
13 pay_element_types_f pet,
14 per_business_groups_perf pbg1,
15 per_business_groups_perf pbg
16 WHERE
17 pbg1.legislation_code = pbg.legislation_code
18 AND
19 (
20 pet.business_group_id = pbg1.business_group_id
21 OR pet.legislation_code = pbg1.legislation_code
22 )
23 AND pbg.legislation_code ='GB'
24 ORDER BY
25 pet.element_type_id;
26 CURSOR c_get_completion_status
27 IS
28 SELECT
29 STATUS
30 FROM
31 pay_upgrade_status
32 WHERE
33 UPGRADE_DEFINITION_ID IN
34 (
35 SELECT
36 UPGRADE_DEFINITION_ID
37 FROM
38 pay_upgrade_definitions
39 WHERE
40 legislation_code='GB'
41 AND short_name ='GB_ENHANCED_RETROPAY'
42 );
43 CURSOR c_get_upgrade_definition
44 IS
45 SELECT
46 UPGRADE_DEFINITION_ID
47 FROM
48 pay_upgrade_definitions
49 WHERE
50 legislation_code='GB'
51 AND short_name ='GB_ENHANCED_RETROPAY';
52 l_element_type_id pay_element_types_f.element_type_id%TYPE;
53 l_element_validity BOOLEAN;
54 l_completion_status pay_upgrade_status.STATUS%TYPE;
55 l_definition_id pay_upgrade_definitions.UPGRADE_DEFINITION_ID%TYPE;
56 BEGIN
57 OPEN c_get_completion_status;
58 FETCH
59 c_get_completion_status
60 INTO
61 l_completion_status;
62 IF c_get_completion_status%notfound OR l_completion_status<>'C' THEN
63 OPEN c_get_element_type_id;
64 LOOP
68 l_element_type_id;
65 FETCH
66 c_get_element_type_id
67 INTO
69 IF c_get_element_type_id%notfound THEN
70 EXIT;
71 END IF;
72 l_element_validity := qualify_element(l_element_type_id);
73 IF l_element_validity = TRUE THEN
74 upgrade_element(l_element_type_id);
75 ELSE
76 NULL;
77 END IF;
78 END LOOP;
79 CLOSE c_get_element_type_id;
80 IF c_get_completion_status%notfound THEN
81 OPEN c_get_upgrade_definition;
82 FETCH
83 c_get_upgrade_definition
84 INTO
85 l_definition_id;
86 CLOSE c_get_upgrade_definition;
87 INSERT
88 INTO
89 pay_upgrade_status
90 (
91 UPGRADE_DEFINITION_ID,
92 STATUS,
93 LEGISLATION_CODE
94 )
95 VALUES
96 (
97 l_definition_id,
98 'C',
99 'GB'
100 );
101 ELSE
102 UPDATE
103 pay_upgrade_status
104 SET
105 status = 'C'
106 WHERE
107 UPGRADE_DEFINITION_ID IN
108 (
109 SELECT
110 UPGRADE_DEFINITION_ID
111 FROM
112 pay_upgrade_definitions
113 WHERE
114 legislation_code='GB'
115 AND short_name ='GB_ENHANCED_RETROPAY'
116 );
117 END IF;
118 END IF;
119 CLOSE c_get_completion_status;
120 END migrate_element_set;
121 FUNCTION qualify_element(
122 p_object_id NUMBER)
123 RETURN BOOLEAN
124 AS
125 CURSOR c_element_class(cp_element_type_id IN NUMBER)
126 IS
127 SELECT
128 pet.classification_id,
129 pet.element_name,
130 pet.legislation_code,
131 pet.business_group_id,
132 pec.classification_name
133 FROM
134 pay_element_types_f pet,
135 pay_element_classifications pec
136 WHERE
137 pet.element_type_id = cp_element_type_id
138 AND pet.classification_id = pec.classification_id;
139 CURSOR c_legislation_code(cp_business_group_id IN NUMBER)
140 IS
141 SELECT
142 legislation_code
143 FROM
144 per_business_groups
145 WHERE
146 business_group_id = cp_business_group_id;
147 CURSOR c_element_set(cp_element_type_id IN NUMBER ,cp_classification_id IN
148 NUMBER ,cp_legislation_code IN VARCHAR2)
149 IS
150 SELECT
151 petr.element_set_id
152 FROM
153 pay_element_type_rules petr
154 WHERE
155 petr.element_type_id = cp_element_type_id
156 AND petr.include_or_exclude = 'I'
157 UNION ALL
158 SELECT
159 pes.element_set_id
160 FROM
161 pay_ele_classification_rules pecr,
162 pay_element_types_f pet,
163 pay_element_sets pes
164 WHERE
165 pet.classification_id = pecr.classification_id
166 AND pes.element_set_id = pecr.element_set_id
167 AND
168 (
169 pes.business_group_id = pet.business_group_id
170 OR pet.legislation_code = cp_legislation_code
171 )
172 AND pet.element_type_id = cp_element_type_id
173 AND pecr.classification_id = cp_classification_id
174 MINUS
175 SELECT
176 petr.element_set_id
177 FROM
178 pay_element_type_rules petr
179 WHERE
180 petr.element_type_id = cp_element_type_id
181 AND petr.include_or_exclude = 'E';
182 CURSOR c_element_check(cp_element_set_id IN NUMBER)
183 IS
184 SELECT
185 1
186 FROM
187 pay_payroll_actions ppa
188 WHERE
189 ppa.action_type = 'L'
190 AND ppa.element_set_id = cp_element_set_id;
191 ln_classification_id NUMBER;
192 lv_element_name VARCHAR2(100);
193 lv_legislation_code VARCHAR2(150);
194 ln_business_group_id NUMBER;
195 lv_classification_name pay_element_classifications.classification_name%TYPE;
196 lv_ele_leg_code VARCHAR2(150);
197 lv_qualified VARCHAR2(1);
198 ln_element_set_id NUMBER;
199 ln_element_used NUMBER;
200 p_qualified BOOLEAN;
201 BEGIN
202 OPEN c_element_class(p_object_id);
203 FETCH
204 c_element_class
205 INTO
206 ln_classification_id,
207 lv_element_name,
208 lv_legislation_code,
209 ln_business_group_id,
210 lv_classification_name;
211 CLOSE c_element_class;
212 lv_ele_leg_code := lv_legislation_code;
213 IF lv_legislation_code IS NULL AND ln_business_group_id IS NOT NULL THEN
214 OPEN c_legislation_code(ln_business_group_id);
215 FETCH
216 c_legislation_code
217 INTO
218 lv_legislation_code;
219 CLOSE c_legislation_code;
220 END IF;
221 lv_qualified := 'N';
222 OPEN c_element_set(p_object_id, ln_classification_id,lv_legislation_code);
223 LOOP
224 FETCH
225 c_element_set
226 INTO
227 ln_element_set_id;
228 IF c_element_set%notfound THEN
229 EXIT;
230 END IF;
231 OPEN c_element_check(ln_element_set_id);
232 FETCH
233 c_element_check
234 INTO
235 ln_element_used;
236 IF c_element_check%found THEN
237 IF lv_ele_leg_code IS NOT NULL THEN
238 lv_qualified := 'N';
239 ELSE
240 IF lv_classification_name IN ('Court Orders', 'PAYE', 'NI',
241 'SSP Non Payment', 'SMP Non Payment', 'SPP Birth Non Payment',
242 'SPP Adoption Non Payment', 'SAP Non Payment') THEN
243 lv_qualified := 'N';
244 ELSE
245 lv_qualified := 'Y';
246 END IF;
247 END IF;
248 EXIT;
249 ELSE
250 lv_qualified := 'N';
251 END IF;
252 CLOSE c_element_check;
253 END LOOP;
254 CLOSE c_element_set;
255 IF lv_qualified = 'Y' THEN
256 p_qualified := TRUE;
257 ELSE
258 p_qualified := FALSE;
259 END IF;
260 RETURN p_qualified;
261 EXCEPTION
262 WHEN OTHERS THEN
263 raise;
264 END qualify_element;
265 PROCEDURE upgrade_element(
266 p_element_type_id IN NUMBER)
267 AS
268 CURSOR c_element_dtl(cp_element_type_id IN NUMBER)
269 IS
270 SELECT
271 business_group_id,
272 legislation_code,
273 classification_id,
274 NVL(retro_summ_ele_id, pet.element_type_id),
275 element_name
276 FROM
277 pay_element_types_f pet
278 WHERE
279 pet.element_type_id = cp_element_type_id
280 ORDER BY
281 pet.effective_start_date DESC;
282 CURSOR c_legislation_code(cp_business_group_id IN NUMBER)
283 IS
284 SELECT
285 legislation_code
286 FROM
287 per_business_groups
288 WHERE
289 business_group_id = cp_business_group_id;
290 CURSOR c_retro_info(cp_legislation_code IN VARCHAR2)
291 IS
292 SELECT
293 retro_component_id,
294 pts.time_span_id
295 FROM
296 pay_retro_components prc,
297 pay_time_spans pts
298 WHERE
299 pts.creator_id = prc.retro_component_id
300 AND prc.legislation_code = cp_legislation_code
301 AND prc.short_name = 'UK_Enh_Retro';
302 CURSOR c_element_set(cp_element_type_id IN NUMBER ,cp_classification_id IN
303 NUMBER ,cp_legislation_code IN VARCHAR2)
304 IS
305 SELECT
306 petr.element_set_id
307 FROM
308 pay_element_type_rules petr
309 WHERE
310 petr.element_type_id = cp_element_type_id
311 AND petr.include_or_exclude = 'I'
312 UNION ALL
313 SELECT
314 pes.element_set_id
315 FROM
316 pay_ele_classification_rules pecr,
317 pay_element_types_f pet,
318 pay_element_sets pes
319 WHERE
320 pet.classification_id = pecr.classification_id
321 AND pes.element_set_id = pecr.element_set_id
322 AND
323 (
324 pes.business_group_id = pet.business_group_id
325 OR pet.legislation_code = cp_legislation_code
326 )
327 AND pet.element_type_id = cp_element_type_id
328 AND pecr.classification_id = cp_classification_id
329 MINUS
330 SELECT
331 petr.element_set_id
332 FROM
333 pay_element_type_rules petr
334 WHERE
335 petr.element_type_id = cp_element_type_id
336 AND petr.include_or_exclude = 'E';
337 CURSOR c_get_business_group(cp_element_set_id IN NUMBER ,cp_legislation_code
338 IN VARCHAR2)
339 IS
340 SELECT
341 hoi.organization_id
342 FROM
343 hr_organization_information hoi,
344 hr_organization_information hoi2
345 WHERE
346 hoi.org_information_context = 'CLASS'
347 AND hoi.org_information1 = 'HR_BG'
348 AND hoi.organization_id = hoi2.organization_id
349 AND hoi2.org_information_context = 'Business Group Information'
350 AND hoi2.org_information9 = cp_legislation_code
351 AND EXISTS
352 (
353 SELECT
354 1
355 FROM
356 pay_payroll_actions ppa
357 WHERE
358 ppa.business_group_id = hoi.organization_id
359 AND ppa.action_type = 'L'
360 AND ppa.element_set_id = cp_element_set_id
361 );
362 ln_ele_business_group_id NUMBER;
363 ln_legislation_code VARCHAR2(10);
364 ln_classification_id NUMBER;
365 ln_retro_element_type_id NUMBER;
366 lv_element_name VARCHAR2(100);
367 lv_legislation_code VARCHAR2(10);
368 ln_element_set_id NUMBER;
369 ln_business_group_id NUMBER;
370 ln_retro_comp_usage_id NUMBER;
371 BEGIN
372 OPEN c_element_dtl(p_element_type_id);
373 FETCH
374 c_element_dtl
375 INTO
376 ln_ele_business_group_id,
377 ln_legislation_code,
378 ln_classification_id,
379 ln_retro_element_type_id,
380 lv_element_name;
381 CLOSE c_element_dtl;
382 IF ln_legislation_code IS NULL AND ln_ele_business_group_id IS NOT NULL THEN
383 OPEN c_legislation_code(ln_ele_business_group_id);
384 FETCH
385 c_legislation_code
386 INTO
387 lv_legislation_code;
388 CLOSE c_legislation_code;
389 ELSE
390 lv_legislation_code := ln_legislation_code;
391 END IF;
392 IF gn_retro_component_id IS NULL THEN
393 OPEN c_retro_info(lv_legislation_code);
394 FETCH
395 c_retro_info
396 INTO
397 gn_retro_component_id ,
398 gn_time_span_id;
399 CLOSE c_retro_info;
400 END IF;
401 IF ln_legislation_code IS NOT NULL AND ln_ele_business_group_id IS NULL THEN
402 insert_retro_comp_usages (p_business_group_id => NULL ,p_legislation_code
403 => ln_legislation_code ,p_retro_component_id => gn_retro_component_id ,
404 p_creator_id => p_element_type_id ,p_retro_comp_usage_id =>
405 ln_retro_comp_usage_id);
406 OPEN c_element_set(p_element_type_id, ln_classification_id,
407 ln_legislation_code);
408 LOOP
409 FETCH
410 c_element_set
411 INTO
412 ln_element_set_id;
413 IF c_element_set%notfound THEN
414 EXIT;
415 END IF;
416 OPEN c_get_business_group(ln_element_set_id,ln_legislation_code);
417 LOOP
418 FETCH
419 c_get_business_group
420 INTO
421 ln_business_group_id;
422 IF c_get_business_group%notfound THEN
423 EXIT;
424 END IF;
425 -- ln_count := ltt_business_group.count;
426 -- ltt_business_group(ln_count) := ln_business_group_id;
427 insert_element_span_usages (p_business_group_id => ln_business_group_id
428 ,p_retro_element_type_id => ln_retro_element_type_id ,
429 p_legislation_code => ln_legislation_code ,p_time_span_id =>
430 gn_time_span_id ,p_retro_comp_usage_id => ln_retro_comp_usage_id);
431 END LOOP;
432 CLOSE c_get_business_group;
433 END LOOP;
434 CLOSE c_element_set;
435 END IF;
436 IF ln_legislation_code IS NULL AND ln_ele_business_group_id IS NOT NULL THEN
437 insert_retro_comp_usages (p_business_group_id => ln_ele_business_group_id ,
438 p_legislation_code => NULL ,p_retro_component_id => gn_retro_component_id ,
439 p_creator_id => p_element_type_id ,p_retro_comp_usage_id =>
440 ln_retro_comp_usage_id);
441 insert_element_span_usages (p_business_group_id => ln_ele_business_group_id
442 ,p_retro_element_type_id => ln_retro_element_type_id ,p_legislation_code =>
443 NULL ,p_time_span_id => gn_time_span_id ,p_retro_comp_usage_id =>
444 ln_retro_comp_usage_id);
445 END IF;
446 EXCEPTION
447 WHEN OTHERS THEN
448 raise;
449 END upgrade_element;
450 PROCEDURE insert_retro_comp_usages(
451 p_business_group_id IN NUMBER,
452 p_legislation_code IN VARCHAR2,
453 p_retro_component_id IN NUMBER,
454 p_creator_id IN NUMBER,
455 p_retro_comp_usage_id OUT nocopy NUMBER)
456 IS
457 ln_retro_component_usage_id NUMBER;
458 BEGIN
459 SELECT
460 pay_retro_component_usages_s.nextval
461 INTO
462 ln_retro_component_usage_id
463 FROM
464 dual;
465 INSERT
466 INTO
467 pay_retro_component_usages
468 (
469 retro_component_usage_id,
470 retro_component_id,
471 creator_id,
472 creator_type,
473 default_component,
474 reprocess_type,
475 business_group_id,
476 legislation_code,
477 creation_date,
478 created_by,
479 last_update_date,
480 last_updated_by,
481 last_update_login,
482 object_version_number
483 )
484 SELECT
485 ln_retro_component_usage_id,
486 p_retro_component_id,
487 p_creator_id,
488 'ET',
489 'Y',
490 'R',
491 p_business_group_id,
492 p_legislation_code,
493 sysdate,
494 2,
495 sysdate,
496 2,
497 2,
498 1
499 FROM
500 dual
501 WHERE
502 NOT EXISTS
503 (
504 SELECT
505 1
506 FROM
507 pay_retro_component_usages
508 WHERE
509 retro_component_id = p_retro_component_id
510 AND creator_id = p_creator_id
511 AND creator_type = 'ET'
512 );
513 p_retro_comp_usage_id := ln_retro_component_usage_id;
514 EXCEPTION
515 WHEN OTHERS THEN
516 raise;
517 END insert_retro_comp_usages;
518 PROCEDURE insert_element_span_usages(
519 p_business_group_id IN NUMBER,
520 p_retro_element_type_id IN NUMBER,
521 p_legislation_code IN VARCHAR2,
522 p_time_span_id IN NUMBER,
523 p_retro_comp_usage_id IN NUMBER)
524 IS
525 BEGIN
526 INSERT
527 INTO
528 pay_element_span_usages
529 (
530 element_span_usage_id,
531 business_group_id,
532 time_span_id,
533 retro_component_usage_id,
534 retro_element_type_id,
535 creation_date,
536 created_by,
537 last_update_date,
538 last_updated_by,
539 last_update_login,
540 object_version_number
541 )
542 --values
543 SELECT
544 pay_element_span_usages_s.nextval,
545 p_business_group_id,
546 p_time_span_id,
547 p_retro_comp_usage_id,
548 p_retro_element_type_id,
549 sysdate,
550 2,
551 sysdate,
552 2,
553 2,
554 1
555 FROM
556 dual
557 WHERE
558 NOT EXISTS
559 (
560 SELECT
561 1
562 FROM
563 pay_element_span_usages pesu
564 WHERE
565 pesu.business_group_id = p_business_group_id
566 AND pesu.legislation_code IS NULL
567 AND pesu.time_span_id = p_time_span_id
568 AND retro_component_usage_id = p_retro_comp_usage_id
569 );
570 EXCEPTION
571 WHEN OTHERS THEN
572 raise;
573 END insert_element_span_usages;
574 END PAY_GB_ENH_RETRO_PAY;