[Home] [Help]
PACKAGE BODY: APPS.PAY_ES_TWR_CALC_PKG
Source
1 PACKAGE BODY pay_es_twr_calc_pkg AS
2 /* $Header: pyestwrc.pkb 120.9 2006/01/09 23:28:59 kseth noship $ */
3 --
4 START_OF_TIME CONSTANT DATE := TO_DATE('01/01/0001','DD/MM/YYYY');
5 END_OF_TIME CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
6 --
7 --------------------------------------------------------------------------------
8 -- get_payment_key
9 --------------------------------------------------------------------------------
10 FUNCTION get_payment_key(passignment_id NUMBER
11 ,peffective_date DATE ) RETURN VARCHAR2 IS
12 --
13 CURSOR c_payment_key(c_element_name VARCHAR2
14 ,c_input_value_name VARCHAR2) IS
15 SELECT eev.screen_entry_value payment_key
16 FROM pay_element_types_f pet
17 ,pay_input_values_f piv
18 ,pay_element_entry_values_f eev
19 ,pay_element_entries_f pee
20 WHERE pee.assignment_id = passignment_id
21 AND pee.element_type_id = pet.element_type_id
22 AND pet.element_name = c_element_name
23 AND pet.legislation_code = 'ES'
24 AND peffective_date BETWEEN pet.effective_start_date
25 AND pet.effective_end_date
26 AND piv.name = c_input_value_name
27 AND piv.legislation_code = 'ES'
28 AND peffective_date BETWEEN piv.effective_start_date
29 AND piv.effective_end_date
30 AND piv.element_type_id = pee.element_type_id
31 AND eev.input_value_id + 0 = piv.input_value_id
32 AND peffective_date BETWEEN eev.effective_start_date
33 AND eev.effective_end_date
34 AND pee.element_entry_id = eev.element_entry_id
35 AND peffective_date BETWEEN pee.effective_start_date
36 AND pee.effective_end_date ;
37 --
38 lpayment_key pay_element_entry_values_f.screen_entry_value%TYPE;
39 --
40 BEGIN
41 --
42 OPEN c_payment_key('Tax Details','Payment Key');
43 FETCH c_payment_key INTO lpayment_key;
44 IF c_payment_key%NOTFOUND THEN
45 CLOSE c_payment_key;
46 RETURN NULL;
47 END IF;
48 CLOSE c_payment_key;
49 --
50 RETURN lpayment_key;
51 --
52 END get_payment_key;
53 --
54 --------------------------------------------------------------------------------
55 -- get_no_contacts
56 --------------------------------------------------------------------------------
57 FUNCTION get_no_contacts(passignment_id IN NUMBER
58 ,pbusiness_gr_id IN NUMBER
59 ,peffective_date IN DATE
60 ,pno_descendant OUT NOCOPY NUMBER
61 ,pno_descendant_less_3 OUT NOCOPY NUMBER
62 ,pno_descendant_bet_3_25 OUT NOCOPY NUMBER
63 ,pno_desc_disability_33_64 OUT NOCOPY NUMBER
64 ,pno_desc_disability_gr_65 OUT NOCOPY NUMBER
65 ,pno_desc_reduced_mobility OUT NOCOPY NUMBER
66 ,pno_desc_single_parent OUT NOCOPY NUMBER
67 ,pno_ascendant OUT NOCOPY NUMBER
68 ,pno_ascendant_gr_75 OUT NOCOPY NUMBER
69 ,pno_asc_disability_33_64 OUT NOCOPY NUMBER
70 ,pno_asc_disability_gr_65 OUT NOCOPY NUMBER
71 ,pno_asc_reduced_mobility OUT NOCOPY NUMBER
72 ,pno_asc_single_descendant OUT NOCOPY NUMBER
73 ,pdescendant_dis_amt OUT NOCOPY NUMBER
74 ,pdescendant_sp_assistance_amt OUT NOCOPY NUMBER
75 ,pascendant_dis_amt OUT NOCOPY NUMBER
76 ,pascendant_sp_assistance_amt OUT NOCOPY NUMBER
77 ,pascendant_age_deduction_amt OUT NOCOPY NUMBER
78 ,pno_independent_siblings OUT NOCOPY NUMBER
79 ,psingle_parent OUT NOCOPY VARCHAR2
80 ,pno_descendant_adopt_less_3 OUT NOCOPY NUMBER)
81 RETURN NUMBER IS
82 --
83 CURSOR c_contact_info IS
84 SELECT pap.date_of_birth date_of_birth
85 ,pcr.contact_person_id contact_person_id
86 ,pcr.contact_type
87 ,NVL(pcr.cont_information1,'N') fiscal_dependent
88 ,NVL(pcr.cont_information2,'N') single_parent
89 ,pcr.date_start date_start
90 FROM per_contact_relationships pcr
91 ,per_all_people_f pap
92 ,per_All_assignments_f paaf
93 WHERE paaf.assignment_id = passignment_id
94 AND pcr.person_id = paaf.person_id
95 AND pap.person_id = pcr.contact_person_id
96 AND pcr.rltd_per_rsds_w_dsgntr_flag = 'Y'
97 AND pcr.cont_information_category = 'ES'
98 AND ((pcr.contact_type in ('C','JP_GC','NEPHEW','NIECE','A') AND NVL(pap.marital_status,'S') <> 'M')
99 OR (pcr.contact_type in ('P','GP','UNCLE','AUNT','BROTHER','SISTER')))
100 AND ((pcr.cont_information1 = 'Y'
101 AND pcr.contact_type in ('C','JP_GC','NEPHEW','NIECE','A','P','GP','UNCLE','AUNT'))
102 OR(pcr.cont_information1 = 'N'
103 AND pcr.contact_type in ('BROTHER','SISTER')))
104 AND peffective_date BETWEEN pap.effective_start_date
105 AND pap.effective_end_date
106 AND peffective_date BETWEEN paaf.effective_start_date
107 AND paaf.effective_end_date
108 AND peffective_date BETWEEN nvl(pcr.date_start,START_OF_TIME)
109 AND nvl(pcr.date_end,END_OF_TIME);
110 --
111 lage NUMBER;
112 ldegree per_disabilities_f.degree%TYPE;
113 lspecial_care_flag per_disabilities_f.dis_information1%TYPE;
114 ldisablity_amt NUMBER;
115 lspl_care_amt NUMBER;
116 l_asc_age_deduction NUMBER;
117 --
118 BEGIN
119 --
120 pno_descendant := 0;
121 pno_ascendant := 0;
122 pno_descendant_less_3 := 0;
123 pno_descendant_bet_3_25 := 0;
124 pno_desc_disability_33_64 := 0;
125 pno_desc_disability_gr_65 := 0;
126 pno_desc_reduced_mobility := 0;
127 pno_desc_single_parent := 0;
128 pno_ascendant_gr_75 := 0;
129 pno_asc_disability_33_64 := 0;
130 pno_asc_disability_gr_65 := 0;
131 pno_asc_reduced_mobility := 0;
132 pdescendant_dis_amt := 0;
133 pdescendant_sp_assistance_amt := 0;
134 pascendant_dis_amt := 0;
135 pascendant_sp_assistance_amt := 0;
136 pno_independent_siblings := 0;
137 pno_asc_single_descendant := 0;
138 pascendant_age_deduction_amt := 0;
139 pno_descendant_adopt_less_3 := 0;
140
141 --
142 FOR i in c_contact_info LOOP
143 IF i.single_parent = 'Y' THEN
144 psingle_parent := 'Y';
145 END IF;
146 ldegree := 0;
147 lspecial_care_flag := 'N';
148 lspl_care_amt := 0;
149 ldisablity_amt := 0;
150 l_asc_age_deduction := 0;
151 IF i.fiscal_dependent = 'Y' THEN
152 lage := MONTHS_BETWEEN(peffective_date,i.date_of_birth)/12;
153 IF lage <= 25 AND
154 i.contact_type in ('C','JP_GC','NEPHEW','NIECE','A') THEN
155 --
156 pno_descendant := pno_descendant + 1;
157 --
158 IF i.contact_type = 'A' AND
159 months_between(peffective_date,nvl(i.date_start,START_OF_TIME)) < 36 THEN
160 --
161 pno_descendant_adopt_less_3 := pno_descendant_adopt_less_3 + 1;
162 --
163 END IF;
164 IF lage <=3 THEN
165 pno_descendant_less_3 := pno_descendant_less_3 + 1;
166 ELSE
167 pno_descendant_bet_3_25 := pno_descendant_bet_3_25 + 1;
168 END IF;
169 IF NVL(i.single_parent,'N') = 'Y' THEN
170 pno_desc_single_parent := pno_desc_single_parent + 1;
171 END IF;
172 IF get_disability_detail(i.contact_person_id
173 ,peffective_date
174 ,ldegree
175 ,lspecial_care_flag) = 'Y' THEN
176 --
177 IF ldegree >= 33 and ldegree <65 THEN
178 pno_desc_disability_33_64 := pno_desc_disability_33_64 + 1;
179 ELSIF ldegree >= 65 THEN
180 pno_desc_disability_gr_65 := pno_desc_disability_gr_65 + 1;
181 END IF;
182 ldisablity_amt :=get_table_value(
183 bus_group_id => pbusiness_gr_id
184 ,ptab_name => 'ES_DISABILITY_ALLOWANCE'
185 ,pcol_name => 'DESCENDANT'
186 ,prow_value => ldegree
187 ,peffective_date => peffective_date);
188 IF i.single_parent = 'N' THEN
189 ldisablity_amt := ldisablity_amt / 2;
190 END IF;
191 --
192 pdescendant_dis_amt := pdescendant_dis_amt + ldisablity_amt;
193 --
194 IF ldegree >= 65 AND lspecial_care_flag = 'Y' THEN
195 IF lspecial_care_flag = 'Y' THEN
196 pno_desc_reduced_mobility := pno_desc_reduced_mobility + 1;
197 END IF;
198 lspl_care_amt := get_table_value(
199 bus_group_id => pbusiness_gr_id
200 ,ptab_name => 'ES_DISABILITY_ASSISTANCE_ALLOWANCE'
201 ,pcol_name => 'DISABILITY_ALLOWANCE'
202 ,prow_value => 'DESCENDANT'
203 ,peffective_date => peffective_date);
204 IF i.single_parent = 'N' THEN
205 lspl_care_amt := lspl_care_amt / 2;
206 END IF;
207 pdescendant_sp_assistance_amt := pdescendant_sp_assistance_amt + lspl_care_amt;
208 END IF;
209 END IF;
210 ELSIF lage >= 75 AND i.contact_type IN ('P','GP','UNCLE','AUNT') THEN
211 pno_ascendant := pno_ascendant + 1;
212 pno_ascendant_gr_75 := pno_ascendant_gr_75 + 1;
213 l_asc_age_deduction:= get_table_value(
214 bus_group_id => pbusiness_gr_id
215 ,ptab_name => 'ES_AGE_ALLOWANCE'
216 ,pcol_name => 'AGE_ALLOWANCE'
217 ,prow_value => 'ASCENDANT'
218 ,peffective_date => peffective_Date);
219 --
220 pascendant_age_deduction_amt := pascendant_age_deduction_amt + l_asc_age_deduction;
221 --
222 IF get_disability_detail(i.contact_person_id
223 ,peffective_date
224 ,ldegree
225 ,lspecial_care_flag) = 'Y' THEN
226 --
227 IF ldegree >= 33 AND ldegree <65 THEN
228 pno_asc_disability_33_64 := pno_asc_disability_33_64 + 1;
229 ELSIF ldegree >= 65 THEN
230 pno_asc_disability_gr_65 := pno_asc_disability_gr_65 + 1;
231 END IF;
232 ldisablity_amt :=get_table_value(
233 bus_group_id => pbusiness_gr_id
234 ,ptab_name => 'ES_DISABILITY_ALLOWANCE'
235 ,pcol_name => 'ASCENDANT'
236 ,prow_value => ldegree
237 ,peffective_date => peffective_date);
238 --
239 pascendant_dis_amt := pascendant_dis_amt + ldisablity_amt;
240 --
241 IF ldegree >= 65 AND lspecial_care_flag = 'Y' THEN
242 IF lspecial_care_flag = 'Y' THEN
243 pno_asc_reduced_mobility := pno_asc_reduced_mobility + 1;
244 END IF;
245 lspl_care_amt := get_table_value(
246 bus_group_id => pbusiness_gr_id
247 ,ptab_name => 'ES_DISABILITY_ASSISTANCE_ALLOWANCE'
248 ,pcol_name => 'DISABILITY_ALLOWANCE'
249 ,prow_value => 'ASCENDANT'
250 ,peffective_date => peffective_date);
251 --
252 pascendant_sp_assistance_amt := pascendant_sp_assistance_amt + lspl_care_amt;
253 --
254 END IF;
255 END IF;
256 ELSIF get_disability_detail(i.contact_person_id
257 ,peffective_date
258 ,ldegree
259 ,lspecial_care_flag) = 'Y' THEN
260 --
261 IF i.contact_type IN ('C','JP_GC','NEPHEW','NIECE','A') THEN
262 IF i.contact_type = 'A' AND
263 MONTHS_BETWEEN(peffective_date,nvl(i.date_start,START_OF_TIME)) < 36 THEN
264 --
265 pno_descendant_adopt_less_3 := pno_descendant_adopt_less_3 + 1;
266 --
267 END IF;
268 IF i.single_parent = 'Y' THEN
269 pno_desc_single_parent := pno_desc_single_parent + 1;
270 END IF;
271 IF ldegree >= 33 AND ldegree <65 THEN
272 pno_desc_disability_33_64 := pno_desc_disability_33_64 + 1;
273 ELSIF ldegree >= 65 THEN
274 pno_desc_disability_gr_65 := pno_desc_disability_gr_65 + 1;
275 END IF;
276 pno_descendant := pno_descendant + 1;
277 ldisablity_amt :=get_table_value(
278 bus_group_id => pbusiness_gr_id
279 ,ptab_name => 'ES_DISABILITY_ALLOWANCE'
280 ,pcol_name => 'DESCENDANT'
281 ,prow_value => ldegree
282 ,peffective_date => peffective_date);
283 --
284 IF i.single_parent = 'N' THEN
285 ldisablity_amt := ldisablity_amt / 2;
286 END IF;
287 pdescendant_dis_amt := pdescendant_dis_amt + ldisablity_amt;
288 IF ldegree >= 65 AND lspecial_care_flag = 'Y' THEN
289 IF lspecial_care_flag = 'Y' THEN
290 pno_desc_reduced_mobility := pno_desc_reduced_mobility + 1;
291 END IF;
292 lspl_care_amt := get_table_value(
293 bus_group_id => pbusiness_gr_id
294 ,ptab_name => 'ES_DISABILITY_ASSISTANCE_ALLOWANCE'
295 ,pcol_name => 'DISABILITY_ALLOWANCE'
296 ,prow_value => 'DESCENDANT'
297 ,peffective_date => peffective_date);
298 IF i.single_parent = 'N' THEN
299 lspl_care_amt := lspl_care_amt / 2;
300 END IF;
301 pdescendant_sp_assistance_amt := pdescendant_sp_assistance_amt + lspl_care_amt;
302 END IF;
303 ELSIF i.contact_type IN ('P','GP','UNCLE','AUNT') THEN
304 IF ldegree >= 33 AND ldegree <65 THEN
305 pno_asc_disability_33_64 := pno_asc_disability_33_64 + 1;
306 ELSIF ldegree >= 65 THEN
307 pno_asc_disability_gr_65 := pno_asc_disability_gr_65 + 1;
308 END IF;
309 pno_ascendant := pno_ascendant + 1;
310 ldisablity_amt :=get_table_value(bus_group_id => pbusiness_gr_id
311 ,ptab_name => 'ES_DISABILITY_ALLOWANCE'
312 ,pcol_name => 'ASCENDANT'
313 ,prow_value => ldegree
314 ,peffective_date => peffective_date);
315 pascendant_dis_amt := pascendant_dis_amt + ldisablity_amt;
316 IF ldegree >= 33 OR lage > 65 THEN
317 l_asc_age_deduction:= get_table_value(
318 bus_group_id => pbusiness_gr_id
319 ,ptab_name => 'ES_AGE_ALLOWANCE'
320 ,pcol_name => 'AGE_ALLOWANCE'
321 ,prow_value => 'ASCENDANT'
322 ,peffective_date => peffective_Date);
323 pascendant_age_deduction_amt := pascendant_age_deduction_amt + l_asc_age_deduction;
324 END IF;
325 IF ldegree >= 65 AND lspecial_care_flag = 'Y' THEN
326 IF lspecial_care_flag = 'Y' THEN
327 pno_asc_reduced_mobility := pno_asc_reduced_mobility + 1;
328 END IF;
329 lspl_care_amt := get_table_value(
330 bus_group_id => pbusiness_gr_id
331 ,ptab_name => 'ES_DISABILITY_ASSISTANCE_ALLOWANCE'
332 ,pcol_name => 'DISABILITY_ALLOWANCE'
333 ,prow_value => 'ASCENDANT'
334 ,peffective_date => peffective_date);
335 pascendant_sp_assistance_amt := pascendant_sp_assistance_amt + lspl_care_amt;
336 END IF;
337 END IF;
338 END IF;
339 ELSE
340 pno_independent_siblings := pno_independent_siblings + 1;
341 END IF;
342 END LOOP;
343 psingle_parent := nvl(psingle_parent,'N');
344 RETURN 0;
345 --
346 END get_no_contacts;
347 --------------------------------------------------------------------------------
348 -- get_marital_status
349 --------------------------------------------------------------------------------
350 FUNCTION get_marital_status (passignment_id IN NUMBER
351 ,peffective_date IN DATE
352 ,passignment_number OUT NOCOPY VARCHAR2
353 ,pmarital_status_code OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
354 --
355 CURSOR c_marital_status IS
356 SELECT pap.marital_status
357 ,pap.person_id
358 ,paaf.assignment_number
359 FROM per_all_people_f pap
360 ,per_all_assignments_f paaf
361 WHERE paaf.assignment_id = passignment_id
362 AND pap.person_id = paaf.person_id
363 AND peffective_date BETWEEN pap.effective_start_date
364 AND pap.effective_end_date
365 AND peffective_date BETWEEN paaf.effective_start_date
366 AND paaf.effective_end_date;
367 --
368 l_marital_status per_all_people_f.marital_status%TYPE;
369 l_person_id per_all_people_f.person_id%TYPE;
370 l_fiscal_dependent per_contact_relationships.cont_information1%TYPE;
371 l_marital_status_desc VARCHAR2(80);
372 --
373 BEGIN
374 --
375 OPEN c_marital_status;
376 FETCH c_marital_status INTO l_marital_status,l_person_id, passignment_number;
377 CLOSE c_marital_status;
378 --
379 pmarital_status_code := NVL(l_marital_status,' ');
380 --
381 l_fiscal_dependent := get_spouse_info(l_person_id, peffective_date);
382 --
383 IF l_marital_status = 'S' THEN
384 l_marital_status_desc := 'SINGLE';
385 ELSIF l_marital_status = 'M' AND l_fiscal_dependent = 'Y' THEN
386 l_marital_status_desc := 'MARRIED_TO_FISCAL_DEPENDENT';
387 ELSIF l_marital_status = 'L' THEN
388 l_marital_status_desc := 'LEGALLY_SEPARATED';
389 ELSIF l_marital_status = 'D' THEN
390 l_marital_status_desc := 'DIVORCED';
391 ELSIF l_marital_status = 'W' THEN
392 l_marital_status_desc := 'WIDOWED';
393 ELSIF l_marital_status IS NULL THEN
394 l_marital_status_desc := 'SINGLE';
395 ELSE
396 l_marital_status_desc := 'OTHERS';
397 END IF;
398 --
399 RETURN l_marital_status_desc;
400 --
401 END get_marital_status;
402 --
403 --------------------------------------------------------------------------------
404 -- get_marital_status
405 --------------------------------------------------------------------------------
406 FUNCTION get_spouse_info(pperson_id NUMBER
407 ,peffective_date DATE ) RETURN VARCHAR2 IS
408 --
409 CURSOR c_spouse_info IS
410 SELECT cont_information1
411 FROM per_contact_relationships pcr
412 WHERE pcr.person_id = pperson_id
413 AND pcr.contact_type = 'S' --Spouse
414 AND pcr.cont_information_category = 'ES'
415 AND peffective_date BETWEEN nvl(pcr.date_start,START_OF_TIME)
416 AND nvl(pcr.date_end,END_OF_TIME);
417 --
418 l_fiscal_dependent per_contact_relationships.cont_information1%TYPE;
419 --
420 BEGIN
421 OPEN c_spouse_info;
422 FETCH c_spouse_info INTO l_fiscal_dependent;
423 CLOSE c_spouse_info;
424 --
425 RETURN l_fiscal_dependent;
426 --
427 END get_spouse_info;
428 --
429 --------------------------------------------------------------------------------
430 -- get_disability_info
431 --------------------------------------------------------------------------------
432 --
433 FUNCTION get_disability_info(passignment_id IN NUMBER
434 ,peffective_date IN DATE
435 ,pdegree OUT NOCOPY NUMBER
436 ,pspecial_care_flag OUT NOCOPY VARCHAR2)
437 RETURN VARCHAR2 IS
438 --
439 CURSOR c_get_person_id IS
440 SELECT person_id
441 FROM per_all_assignments_f paaf
442 WHERE paaf.assignment_id = passignment_id
443 AND peffective_date BETWEEN paaf.effective_start_date
444 AND paaf.effective_end_date;
445 --
446 lperson_id per_all_people_f.person_id%TYPE;
447 ldisabled VARCHAR2(1);
448 BEGIN
449 --
450 OPEN c_get_person_id;
451 FETCH c_get_person_id INTO lperson_id;
452 CLOSE c_get_person_id;
453
454 ldisabled := get_disability_detail(lperson_id
455 ,peffective_date
456 ,pdegree
457 ,pspecial_care_flag);
458 --
459 RETURN ldisabled;
460 --
461 END get_disability_info;
462 --------------------------------------------------------------------------------
463 -- get_disability_detail
464 --------------------------------------------------------------------------------
465 --
466 FUNCTION get_disability_detail(pperson_id IN NUMBER
467 ,peffective_date IN DATE
468 ,pdegree OUT NOCOPY NUMBER
469 ,pspecial_care_flag OUT NOCOPY VARCHAR2)
470 RETURN VARCHAR2 IS
471 --
472 CURSOR c_disability_info IS
473 SELECT pdf.dis_information1
474 ,pdf.degree
475 FROM per_disabilities_f pdf
476 WHERE pdf.person_id = pperson_id
477 AND pdf.dis_information_category = 'ES'
478 AND peffective_date BETWEEN pdf.effective_start_date
479 AND pdf.effective_end_date;
480 --
481 BEGIN
482 OPEN c_disability_info;
483 FETCH c_disability_info INTO pspecial_care_flag,pdegree;
484 IF c_disability_info%NOTFOUND THEN
485 CLOSE c_disability_info;
486 RETURN 'N';
487 END IF;
488 CLOSE c_disability_info;
489 --
490 RETURN 'Y';
491 --
492 END get_disability_detail;
493 --
494 --------------------------------------------------------------------------------
495 -- get_table_value
496 --------------------------------------------------------------------------------
497 FUNCTION get_table_value(bus_group_id IN NUMBER
498 ,ptab_name IN VARCHAR2
499 ,pcol_name IN VARCHAR2
500 ,prow_value IN VARCHAR2
501 ,peffective_date IN DATE )RETURN NUMBER IS
502 --
503 l_ret pay_user_column_instances_f.value%type;
504 --
505 BEGIN
506 --
507 BEGIN
508 --
509 l_ret:= hruserdt.get_table_value(bus_group_id
510 ,ptab_name
511 ,pcol_name
512 ,prow_value
513 ,peffective_date);
514 --
515 EXCEPTION
516 WHEN NO_DATA_FOUND THEN
517 l_ret:='0';
518 END;
519 --
520 RETURN to_number(l_ret);
521 --
522 END get_table_value;
523 --
524 --------------------------------------------------------------------------------
525 -- get_user_table_upper_value
526 --------------------------------------------------------------------------------
527 FUNCTION get_user_table_upper_value(pvalue IN NUMBER
528 ,peffective_date IN DATE) RETURN NUMBER IS
529 --
530 CURSOR csr_get_table_range(c_value NUMBER
531 ,c_effective_date DATE) IS
532 SELECT to_number(pur.ROW_LOW_RANGE_OR_NAME) Low_value
533 ,to_number(pur.ROW_HIGH_RANGE) High_value
534 FROM PAY_USER_ROWS_F pur
535 ,PAY_USER_TABLES put
536 WHERE put.USER_TABLE_NAME = 'ES_WORK_RELATED_EARNINGS_DEDUCTION'
537 AND put.USER_TABLE_ID = pur.USER_TABLE_ID
538 AND c_value between to_number(pur.ROW_LOW_RANGE_OR_NAME) AND to_number(pur.ROW_HIGH_RANGE)
539 AND c_effective_date between pur.effective_start_date AND pur.effective_end_date;
540 --
541 l_table_values csr_get_table_range%ROWTYPE;
542 l_table_upper_values csr_get_table_range%ROWTYPE;
543 l_val number;
544 BEGIN
545 --
546 OPEN csr_get_table_range(pvalue, peffective_date);
547 FETCH csr_get_table_range INTO l_table_values;
548 CLOSE csr_get_table_range;
549
550 l_val := l_table_values.low_value - 1;
551
552 OPEN csr_get_table_range(l_val, peffective_date);
553 FETCH csr_get_table_range INTO l_table_upper_values;
554 CLOSE csr_get_table_range;
555
556 RETURN l_table_upper_values.high_value;
557 --
558 END get_user_table_upper_value;
559 --
560 --------------------------------------------------------------------------------
561 -- get_parameter_value
562 --------------------------------------------------------------------------------
563 FUNCTION get_parameter_value(p_payroll_action_id IN NUMBER
564 ,p_token_name IN VARCHAR2) RETURN VARCHAR2 IS
565 --
566 CURSOR csr_parameter_info IS
567 SELECT SUBSTR(legislative_parameters,
568 INSTR(legislative_parameters,p_token_name)+(LENGTH(p_token_name)+1),
569 INSTR(legislative_parameters,' ',
570 INSTR(legislative_parameters,p_token_name)))
571 FROM pay_payroll_actions
572 WHERE payroll_action_id = p_payroll_action_id;
573 --
574 l_token_value VARCHAR2(50);
575 --
576 BEGIN
577 --
578 OPEN csr_parameter_info;
579 FETCH csr_parameter_info INTO l_token_value;
580 CLOSE csr_parameter_info;
581 --
582 RETURN(l_token_value);
583 --
584 END get_parameter_value;
585 --
586 --------------------------------------------------------------------------------
587 -- Emp_Address_chk
588 --------------------------------------------------------------------------------
589 FUNCTION Emp_address_chk(passignment_id IN NUMBER
590 ,peffective_date IN DATE) RETURN VARCHAR2 IS
591 --
592 CURSOR cur_emp_address_chk IS
593 SELECT pa.Region_2
594 FROM per_addresses pa
595 ,per_All_assignments_f paaf
596 WHERE paaf.assignment_id = passignment_id
597 AND paaf.person_id = pa.person_id
598 AND pa.style IN('ES','ES_GLB')
599 AND pa.primary_flag = 'Y'
600 AND pa.Region_2 IN (51,52)
601 AND peffective_date BETWEEN pa.date_from
602 AND NVL(pa.date_to,END_OF_TIME)
603 AND peffective_date BETWEEN paaf.effective_start_date
604 AND paaf.effective_end_date;
605 --
606 CURSOR cur_emp_loc_chk IS
607 SELECT pa.Region_2
608 FROM hr_locations pa
609 ,per_All_assignments_f paaf
610 WHERE paaf.assignment_id = passignment_id
611 AND paaf.location_id = pa.location_id
612 AND pa.style IN ('ES','ES_GLB')
613 AND peffective_date BETWEEN paaf.effective_start_date
614 AND paaf.effective_end_date
615 AND pa.Region_2 IN (51,52);
616 --
617 l_region per_addresses.Region_2%type;
618 l_region1 hr_locations.Region_2%type;
619 --
620 BEGIN
621 --
622 OPEN cur_emp_address_chk;
623 FETCH cur_emp_address_chk INTO l_region;
624 IF cur_emp_address_chk%NOTFOUND THEN
625 CLOSE cur_emp_address_chk;
626 RETURN 'N';
627 END IF;
628 CLOSE cur_emp_address_chk;
629
630 OPEN cur_emp_loc_chk;
631 FETCH cur_emp_loc_chk INTO l_region1;
632 IF cur_emp_loc_chk%NOTFOUND THEN
633 CLOSE cur_emp_loc_chk;
634 RETURN 'N';
635 END IF;
636 CLOSE cur_emp_loc_chk;
637 --
638 RETURN 'Y';
639 --
640 END Emp_Address_chk;
641 --
642 --------------------------------------------------------------------------------
643 -- get_effective_date
644 --------------------------------------------------------------------------------
645 FUNCTION get_effective_date(p_payroll_action_id IN NUMBER
646 ,p_assignment_id IN NUMBER
647 ,p_date_earned IN DATE
648 ,p_run_type OUT NOCOPY VARCHAR2
649 ,p_process_twr_flag OUT NOCOPY VARCHAR2)
650 RETURN DATE IS
651 --
652 CURSOR csr_get_effective_date IS
653 SELECT ppa.effective_date
654 ,prt.shortname
655 FROM pay_payroll_actions ppa
656 ,pay_run_types_f prt
657 WHERE ppa.payroll_action_id = p_payroll_action_id
658 AND ppa.run_type_id = prt.run_type_id
659 AND ppa.effective_date BETWEEN prt.effective_start_date
660 AND prt.effective_end_date;
661 --
662 CURSOR csr_chk_twr_process(c_assignment_id NUMBER
663 ,c_effective_date DATE) IS
664 SELECT 'Y'
665 FROM DUAL
666 WHERE EXISTS(SELECT 1
667 FROM pay_element_entries_f pee
668 ,pay_element_types_f pet
669 WHERE pee.assignment_id = c_assignment_id
670 AND pee.element_type_id = pet.element_type_id
671 AND pet.element_name = 'Tax Withholding Rate'
672 AND pet.legislation_code= 'ES'
673 AND c_effective_date BETWEEN pee.effective_start_date
674 AND pee.effective_end_date
675 AND c_effective_date BETWEEN pet.effective_start_date
676 AND pet.effective_end_date);
677 --
678 l_eff_date DATE;
679 --
680 BEGIN
681 --
682 p_process_twr_flag := 'N';
683 --
684 OPEN csr_get_effective_date;
685 FETCH csr_get_effective_date INTO l_eff_date,p_run_type;
686 CLOSE csr_get_effective_date;
687 --
688 IF p_run_type <> 'TAX_WITHHOLDING_RATE'
689 OR p_run_type IS NULL THEN
690 l_eff_date := p_date_earned;
691 END IF;
692 --
693 OPEN csr_chk_twr_process(p_assignment_id,l_eff_date);
694 FETCH csr_chk_twr_process INTO p_process_twr_flag;
695 CLOSE csr_chk_twr_process;
696 --
697 p_run_type := NVL(p_run_type,'STANDARD');
698 RETURN(l_eff_date);
699 --
700 END get_effective_date;
701 --
702 --------------------------------------------------------------------------------
703 -- get_pay_period_number
704 --------------------------------------------------------------------------------
705 FUNCTION get_pay_period_number(payroll_id IN NUMBER
706 ,peffective_date IN DATE) RETURN NUMBER IS
707 --
708 CURSOR csr_get_period_num(c_payroll_id NUMBER,c_effective_date DATE) IS
709 SELECT ptp.period_num
710 FROM per_time_periods ptp
711 WHERE ptp.payroll_id = c_payroll_id
712 AND c_effective_date BETWEEN ptp.start_date
713 AND ptp.end_date;
714 --
715 l_no_period per_time_periods.period_num%TYPE;
716 --
717 BEGIN
718 --
719 OPEN csr_get_period_num(payroll_id,peffective_date);
720 FETCH csr_get_period_num INTO l_no_period;
721 IF csr_get_period_num%NOTFOUND THEN
722 CLOSE csr_get_period_num;
723 RETURN 0;
724 END IF;
725 CLOSE csr_get_period_num;
726 --
727 RETURN (l_no_period);
728 --
729 END get_pay_period_number;
730 --
731 --------------------------------------------------------------------------------
732 -- get_proration_factor
733 --------------------------------------------------------------------------------
734 FUNCTION get_proration_factor(passignment_id IN NUMBER
735 ,payroll_id IN NUMBER
736 ,peffective_date IN DATE
737 ,phire_date IN DATE
738 ,ptermination_date IN DATE
739 ,ppay_periods_per_year IN NUMBER
740 ,ppay_proc_period_number IN NUMBER
741 ,pchk_new_emp IN VARCHAR2
742 ,p_run_type IN VARCHAR2)
743 RETURN NUMBER IS
744 --
745 CURSOR csr_assignment_start_date(c_assignment_id NUMBER
746 ,c_payroll_id NUMBER) IS
747 SELECT MIN(effective_start_date)
748 FROM per_all_assignments_f
749 WHERE assignment_id = c_assignment_id
750 AND payroll_id = c_payroll_id;
751
752 CURSOR csr_payroll_end_date(c_payroll_id NUMBER
753 ,c_effective_date DATE) IS
754 SELECT max(period_num)
755 FROM pay_payrolls_f ppf
756 ,per_time_periods ptp
757 WHERE ppf.payroll_id = c_payroll_id
758 AND ppf.period_type = ptp.period_type
759 AND ppf.payroll_id = ptp.payroll_id
760 AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
761 --
762 l_start_period NUMBER;
763 l_end_period NUMBER;
764 l_start_financial_year NUMBER;
765 l_assignment_start_date DATE;
766 l_payroll_end NUMBER;
767 l_proration_factor NUMBER;
768 --
769 BEGIN
770 hr_utility.trace('~~ppay_periods_per_year'||ppay_periods_per_year);
771 hr_utility.trace('~~ppay_proc_period_number'||ppay_proc_period_number);
772 hr_utility.trace('~~pchk_new_emp'||pchk_new_emp);
773 IF pchk_new_emp <> 'Y' and to_char(ptermination_date,'YYYY') <> to_char(peffective_date,'YYYY') THEN
774 IF p_run_type = 'TAX_WITHHOLDING_RATE' THEN
775 l_proration_factor := (ppay_periods_per_year - ppay_proc_period_number + 1)/ppay_periods_per_year;
776 ELSE
777 l_proration_factor := (ppay_periods_per_year - ppay_proc_period_number)/ppay_periods_per_year;
778 END IF;
779 RETURN (l_proration_factor);
780 ELSE
781 OPEN csr_assignment_start_date(passignment_id,payroll_id);
782 FETCH csr_assignment_start_date INTO l_assignment_start_date;
783 CLOSE csr_assignment_start_date;
784 --
785 OPEN csr_payroll_end_date(payroll_id,peffective_date);
786 FETCH csr_payroll_end_date INTO l_payroll_end;
787 CLOSE csr_payroll_end_date;
788 l_start_financial_year := get_pay_period_number(
789 payroll_id
790 ,to_date('0101'||to_char(peffective_date,'yyyy'),'ddmmyyyy'));
791 IF l_assignment_start_date < to_date('0101'||to_char(peffective_date,'yyyy'),'ddmmyyyy') THEN
792 l_assignment_start_date := to_date('0101'||to_char(peffective_date,'yyyy'),'ddmmyyyy');
793 END IF;
794 --
795 IF pchk_new_emp = 'Y' THEN
796 l_start_period := get_pay_period_number(payroll_id,peffective_date);
797 IF TO_CHAR(ptermination_date,'YYYY') = TO_CHAR(peffective_date,'YYYY') THEN
798 l_end_period := get_pay_period_number(payroll_id,ptermination_date);
799 IF l_end_period = 0 THEN
800 l_end_period := l_payroll_end;
801 END IF;
802 IF p_run_type = 'TAX_WITHHOLDING_RATE' THEN
803 l_proration_factor := (l_end_period - l_start_period + 1)/ppay_periods_per_year;
804 ELSE
805 l_proration_factor := (l_end_period - l_start_period)/ppay_periods_per_year;
806 END IF;
807 ELSE
808 l_end_period := ppay_periods_per_year;
809 IF p_run_type = 'TAX_WITHHOLDING_RATE' THEN
810 l_proration_factor := (l_end_period - (l_start_period- l_start_financial_year + 1) + 1)/ppay_periods_per_year;
811 ELSE
812 l_proration_factor := (l_end_period - (l_start_period- l_start_financial_year + 1))/ppay_periods_per_year;
813 END IF;
814 END IF;
815 RETURN (l_proration_factor);
816 ELSIF to_char(ptermination_date,'YYYY') = to_char(peffective_date,'YYYY') THEN
817 l_end_period := get_pay_period_number(payroll_id,ptermination_date);
818 --
819 IF l_end_period = 0 THEN
820 l_end_period := l_payroll_end;
821 END IF;
822 IF p_run_type = 'TAX_WITHHOLDING_RATE' THEN
823 l_proration_factor := ((l_end_period - l_start_financial_year + 1)- ppay_proc_period_number + 1)/ppay_periods_per_year;
824 ELSE
825 l_proration_factor := ((l_end_period - l_start_financial_year + 1)- ppay_proc_period_number)/ppay_periods_per_year;
826 END IF;
827 RETURN (l_proration_factor);
828 END IF;
829 END IF;
830 RETURN 0;
831 END get_proration_factor;
832 --
833 --------------------------------------------------------------------------------
834 -- chk_new_employee
835 --------------------------------------------------------------------------------
836 FUNCTION chk_new_employee(passignment_id IN NUMBER
837 ,peffective_date IN DATE) RETURN VARCHAR2 IS
838 --
839 CURSOR csr_chk_new_emp (c_assignment_id number)IS
840 SELECT 'N'
841 FROM dual
842 WHERE EXISTS(SELECT NULL
843 FROM pay_assignment_actions paa
844 ,pay_payroll_actions ppa
845 ,pay_run_results prr
846 ,pay_run_result_values prv
847 ,pay_element_types_f petf
848 ,pay_input_values_f pivf
849 WHERE paa.assignment_id = c_assignment_id
850 AND paa.ACTION_STATUS IN ('C' ,'U')
851 AND ppa.payroll_action_id = paa.payroll_action_id
852 and ppa.action_status IN ('C' ,'U')
853 AND to_char(ppa.effective_date,'YYYY') = to_char(peffective_date,'YYYY')
854 AND petf.legislation_code = 'ES'
855 AND ((petf.element_name = 'Tax Withholding Rate'
856 AND pivf.name = ('Rate'))
857 OR (petf.element_name = 'Tax'
858 AND pivf.name = ('Tax Withholding Rate')))
859 AND petf.element_type_id = pivf.element_type_id
860 AND pivf.legislation_code = 'ES'
861 AND prr.assignment_action_id = paa.assignment_action_id
862 AND paa.source_action_id IS NOT NULL
863 AND prr.element_type_id = petf.element_type_id
864 AND prv.run_result_id = prr.run_result_id
865 AND prv.input_value_id = pivf.input_value_id
866 AND prv.result_value IS NOT NULL
867 AND peffective_date BETWEEN petf.effective_start_date
868 AND petf.effective_end_date
869 AND peffective_date BETWEEN pivf.effective_start_date
870 AND pivf.effective_end_date);
871
872 l_chk varchar2(1);
873 --
874 BEGIN
875 OPEN csr_chk_new_emp(passignment_id);
876 FETCH csr_chk_new_emp INTO l_chk;
877 IF csr_chk_new_emp%NOTFOUND THEN
878 CLOSE csr_chk_new_emp;
879 RETURN 'Y';
880 END IF;
881 CLOSE csr_chk_new_emp;
882 --
883 RETURN l_chk;
884 --
885 END chk_new_employee;
886 --
887 --------------------------------------------------------------------------------
888 -- get_previous_twr_run_values
889 --------------------------------------------------------------------------------
890 FUNCTION get_previous_twr_run_values(passignment_id IN NUMBER
891 ,peffective_date IN DATE
892 ,ptax_base OUT NOCOPY NUMBER
893 ,pcont_earnings OUT NOCOPY NUMBER)
894 RETURN NUMBER IS
895 --
896 CURSOR csr_get_run_result_values(c_assignment_action_id NUMBER
897 ,c_effective_date DATE
898 ,c_element_name VARCHAR2
899 ,c_input_value_name VARCHAR2) IS
900 SELECT NVL(prrv.result_value,0) Result_Value
901 FROM pay_run_results prr
902 ,pay_run_result_values prrv
903 ,pay_element_types_f pet
904 ,pay_input_values_f piv
905 where pet.element_name = c_element_name
906 AND pet.legislation_code = 'ES'
907 AND piv.element_type_id = pet.element_type_id
908 AND piv.name = c_input_value_name
909 AND pet.element_type_id = prr.element_type_id
910 AND prr.assignment_action_id= c_assignment_action_id
911 AND prrv.run_result_id = prr.run_result_id
912 AND piv.input_value_id = prrv.input_value_id
913 AND c_effective_date BETWEEN pet.effective_start_date
914 AND pet.effective_end_date
915 AND c_effective_date BETWEEN piv.effective_start_date
916 AND piv.effective_end_date;
917 --
918 CURSOR csr_get_assignment_action(c_assignment_id NUMBER
919 ,c_effective_date DATE) IS
920 SELECT paa.assignment_action_id
921 FROM pay_assignment_actions paa
922 ,pay_payroll_actions ppa
923 ,pay_run_results prr
924 ,pay_run_result_values prv
925 ,pay_element_types_f petf
926 ,pay_input_values_f pivf
927 WHERE paa.assignment_id = c_assignment_id
928 AND paa.action_status IN ('C' ,'U')
929 AND ppa.payroll_action_id = paa.payroll_action_id
930 AND ppa.action_type IN ('Q' ,'R')
931 AND ppa.effective_date < c_effective_date
932 AND petf.legislation_code = 'ES'
933 AND ((petf.element_name = 'TWR Employee Information' AND pivf.name = 'Payment Key')
934 OR (petf.element_name = 'Tax' AND pivf.name = 'Tax Withholding Rate'))
935 AND petf.element_type_id = pivf.element_type_id
936 AND pivf.legislation_code = 'ES'
937 AND prr.assignment_action_id = paa.assignment_action_id
938 AND paa.source_action_id IS NOT NULL
939 AND prr.element_type_id = petf.element_type_id
940 AND prv.run_result_id = prr.run_result_id
941 AND prv.input_value_id = pivf.input_value_id
942 AND prv.result_value IS NOT NULL
943 AND c_effective_date BETWEEN petf.effective_start_date
944 AND petf.effective_end_date
945 AND c_effective_date BETWEEN pivf.effective_start_date
946 AND pivf.effective_end_date
947 ORDER BY ppa.effective_date DESC;
948 --
949 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
950 l_result_value pay_run_result_values.result_value%TYPE;
951 --
952 BEGIN
953 --
954 ptax_base := 0;
955 pcont_earnings := 0;
956 --
957 OPEN csr_get_assignment_action(passignment_id,peffective_date);
958 FETCH csr_get_assignment_action into l_assignment_action_id;
959 IF csr_get_assignment_action%NOTFOUND THEN
960 CLOSE csr_get_assignment_action;
961 RETURN 0;
962 END IF;
963 CLOSE csr_get_assignment_action;
964
965 OPEN csr_get_run_result_values(l_assignment_action_id
966 ,peffective_date
967 ,'TWR Deduction Information'
968 ,'Tax Base');
969 FETCH csr_get_run_result_values INTO l_result_value;
970 CLOSE csr_get_run_result_values;
971 --
972 ptax_base := l_result_value;
973 --
974 OPEN csr_get_run_result_values(l_assignment_action_id
975 ,peffective_date
976 ,'TWR Deduction Information'
977 ,'Override Contractual Earnings');
978 FETCH csr_get_run_result_values INTO l_result_value;
979 IF csr_get_run_result_values%NOTFOUND THEN
980 OPEN csr_get_run_result_values(l_assignment_action_id
981 ,peffective_date
982 ,'TWR Employee Information'
983 ,'Contractual Earnings');
984 FETCH csr_get_run_result_values INTO l_result_value;
985 CLOSE csr_get_run_result_values;
986 END IF;
987 CLOSE csr_get_run_result_values;
988 --
989 pcont_earnings := l_result_value;
990 --
991 RETURN 1;
992 --
993 END get_previous_twr_run_values;
994 --
995 --------------------------------------------------------------------------------
996 -- FETCH_PDF_BLOB
997 --------------------------------------------------------------------------------
998 FUNCTION get_name(p_payroll_action_id IN NUMBER
999 ,p_action_type IN VARCHAR2
1000 ,p_effective_date IN DATE) RETURN VARCHAR2
1001 IS
1002 --
1003 CURSOR csr_get_emp_name(C_payroll_action_id NUMBER
1004 ,c_effective_date DATE) IS
1005 SELECT pap.full_name
1006 FROM per_all_people_f pap
1007 ,per_All_assignments_f paaf
1008 ,pay_assignment_actions paa
1009 WHERE paa.payroll_action_id = c_payroll_action_id
1010 AND paa.assignment_id = paaf.assignment_id
1011 AND paaf.person_id = pap.person_id
1012 AND paa.source_action_id IS NULL
1013 AND c_effective_date BETWEEN pap.effective_start_date
1014 AND pap.effective_end_date
1015 AND c_effective_date BETWEEN paaf.effective_start_date
1016 AND paaf.effective_end_date;
1017 --
1018 l_name per_all_people_f.full_name%TYPE;
1019 BEGIN
1020 --
1021 l_name := ' ';
1022 IF p_action_type = 'Q' THEN
1023 OPEN csr_get_emp_name(p_payroll_action_id
1024 ,p_effective_date);
1025 FETCH csr_get_emp_name INTO l_name;
1026 IF csr_get_emp_name%NOTFOUND THEN
1027 l_name := ' ';
1028 END IF;
1029 CLOSE csr_get_emp_name;
1030 END IF;
1031 --
1032 RETURN l_name;
1033 END get_name;
1034 --
1035 --------------------------------------------------------------------------------
1036 -- FETCH_PDF_BLOB
1037 --------------------------------------------------------------------------------
1038 PROCEDURE fetch_pdf_blob(p_pdf_blob OUT NOCOPY BLOB) IS
1039 --
1040 BEGIN
1041 --
1042 SELECT file_data INTO p_pdf_blob
1043 FROM fnd_lobs
1044 WHERE file_id = (SELECT MAX(file_id) FROM per_gb_xdo_templates
1045 WHERE file_name like '%PAY_TWR_e_ES.pdf%');
1046 EXCEPTION
1047 WHEN no_data_found THEN
1048 NULL;
1049
1050 END fetch_pdf_blob;
1051 --
1052 --------------------------------------------------------------------------------
1053 -- POPULATE_TWR_REPORT
1054 --------------------------------------------------------------------------------
1055 PROCEDURE populate_TWR_Report (p_request_id IN NUMBER
1056 ,p_payroll_action_id NUMBER
1057 ,p_legal_employer NUMBER
1058 ,p_person_id NUMBER
1059 ,p_xfdf_blob OUT NOCOPY BLOB)IS
1060 --
1061 p_xfdf_string clob;
1062 --
1063 BEGIN
1064 --
1065 populate_plsql_table( p_request_id
1066 ,p_payroll_action_id
1067 ,p_legal_employer
1068 ,p_person_id);
1069 --
1070 WritetoCLOB (p_xfdf_blob,p_xfdf_string);
1071 --
1072 END populate_TWR_Report;
1073 --
1074 --------------------------------------------------------------------------------
1075 -- POPULATE_PLSQL_TABLE
1076 --------------------------------------------------------------------------------
1077 PROCEDURE populate_plsql_table
1078 (p_request_id IN NUMBER
1079 ,p_payroll_action_id NUMBER
1080 ,p_legal_employer NUMBER
1081 ,p_person_id NUMBER
1082 )
1083 IS
1084
1085 CURSOR csr_get_assignment_ids IS
1086 SELECT paa.assignment_id
1087 ,paa.assignment_action_id
1088 ,ppa.effective_date
1089 ,paa.action_status
1090 ,prtf.shortname
1091 FROM pay_payroll_actions ppa
1092 ,pay_assignment_actions paa
1093 ,per_all_assignments_f paaf
1094 ,pay_run_types_f prtf
1095 WHERE ppa.payroll_action_id = p_payroll_action_id
1096 AND ppa.payroll_action_id = paa.payroll_action_id
1097 AND ppa.run_type_id = prtf.run_type_id
1098 AND paaf.assignment_id = paa.assignment_id
1099 AND ((paa.source_action_id IS NULL and paa.action_status = 'E')
1100 or (paa.source_action_id IS NOT NULL
1101 AND exists (select 1
1102 FROM pay_run_results r
1103 ,pay_element_types_f pet
1104 WHERE paa.assignment_action_id = r.assignment_action_id
1105 AND pet.element_type_id = r.element_type_id
1106 AND pet.legislation_code = 'ES'
1107 AND pet.element_name in ('Tax Withholding Rate','Tax'))))
1108 AND paaf.person_id = nvl(p_person_id,paaf.person_id)
1109 AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1110 AND ppa.effective_date BETWEEN prtf.effective_start_date AND prtf.effective_end_date;
1111 --
1112 CURSOR csr_chk_emp_err(c_assignment_action_id number, c_effective_date DATE) IS
1113 SELECT 'N'
1114 FROM PAY_RUN_RESULTs prr
1115 ,pay_element_types_f pet
1116 WHERE prr.assignment_action_id = c_assignment_action_id
1117 AND prr.element_type_id = pet.element_type_id
1118 AND pet.element_name = 'TWR Employee Information'
1119 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
1120 --
1121 CURSOR csr_get_emp_detail(c_assignment_id number, c_effective_date DATE) IS
1122 SELECT pap.full_name name
1123 ,pap.person_id
1124 ,paaf.assignment_number
1125 ,hr_general.decode_lookup('MAR_STATUS',pap.marital_status) marital_status
1126 ,floor(months_between(c_effective_date,pap.date_of_birth)/12) Age
1127 FROM per_all_people_f pap
1128 ,per_all_assignments_f paaf
1129 WHERE pap.person_id = paaf.person_id
1130 AND paaf.assignment_id = c_assignment_id
1131 AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
1132 AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
1133
1134 CURSOR csr_get_twr_emp_values(c_assignment_action_id number, c_effective_date DATE) IS
1135 SELECT prr.assignment_action_id
1136 ,prr.run_result_id
1137 ,min(decode(piv.name, 'Assignment Number', prrv.RESULT_VALUE , null)) Assignment_Number
1138 ,min(decode(piv.name, 'Name', prrv.RESULT_VALUE , null)) Name
1139 ,min(decode(piv.name, 'Age', prrv.RESULT_VALUE , null)) Age
1140 ,min(decode(piv.name, 'Payment Key',prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_PAYMENT_KEY',prrv.RESULT_VALUE), null)) Payment_Key
1141 ,min(decode(piv.name, 'Length of Contract', prrv.RESULT_VALUE , null)) Length_Of_Contract
1142 ,min(decode(piv.name, 'Change in Residency', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Change_in_Residency
1143 ,min(decode(piv.name, 'Contract Type', prrv.RESULT_VALUE, null)) Contract_Type
1144 ,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
1145 ,min(decode(piv.name, 'Disabled', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Disabled
1146 ,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
1147 ,min(decode(piv.name, 'Work Status', substr(prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_WORKER_STATUS',prrv.RESULT_VALUE),1,10) , null)) Work_Status
1148 ,min(decode(piv.name, 'Location Benefit', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Resident_Ceuta_Melila
1149 ,min(decode(piv.name, 'Contractual Earnings', prrv.RESULT_VALUE , null)) Calc_Cont_Earnings
1150 ,min(decode(piv.name, 'America Cup', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) America_Cup_Flag
1151 FROM pay_run_results prr
1152 ,pay_run_result_values prrv
1153 ,pay_element_types_f pet
1154 ,pay_input_values_f piv
1155 WHERE pet.element_name = 'TWR Employee Information'
1156 AND pet.legislation_code = 'ES'
1157 AND piv.element_type_id =pet.element_type_id
1158 AND pet.element_type_id = prr.element_type_id
1159 AND prr.assignment_action_id= c_assignment_action_id
1160 AND prrv.run_result_id = prr.run_result_id
1161 AND piv.input_value_id = prrv.input_value_id
1162 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1163 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1164 group BY prr.assignment_action_id , prr.run_result_id;
1165 --
1166 CURSOR csr_get_twr_asc_values(c_assignment_action_id number, c_effective_date DATE)IS
1167 SELECT prr.assignment_action_id
1168 ,prr.run_result_id
1169 ,min(decode(piv.name, 'Number of Ascendants', prrv.RESULT_VALUE, null)) No_of_Asc
1170 ,min(decode(piv.name, 'Ascendants Greater than 75', prrv.RESULT_VALUE, null)) No_of_Asc_Gr_75
1171 ,min(decode(piv.name, 'Disability between 33 and 64', prrv.RESULT_VALUE, null)) No_Asc_disablity_bet_33_65
1172 ,min(decode(piv.name, 'Disability greater than 64', prrv.RESULT_VALUE, null)) No_Asc_disablity_gr_65
1173 ,min(decode(piv.name, 'Single Descendant', prrv.RESULT_VALUE, null)) No_Asc_Single_Descendent
1174 ,min(decode(piv.name, 'Reduced Mobility', prrv.RESULT_VALUE, null)) No_Asc_Reduced_Mobility
1175 ,min(decode(piv.name, 'Disability Amount', prrv.RESULT_VALUE, null)) Asc_Disability_Amt
1176 ,min(decode(piv.name, 'Special Assistance', prrv.RESULT_VALUE, null)) Asc_Special_Assistance
1177 ,min(decode(piv.name, 'Special Allowance', prrv.RESULT_VALUE, null)) Asc_Special_Allowance
1178 ,min(decode(piv.name, 'Age Deduction', prrv.RESULT_VALUE, null)) Asc_Age_Deduction
1179 FROM pay_run_results prr
1180 ,pay_run_result_values prrv
1181 ,pay_element_types_f pet
1182 ,pay_input_values_f piv
1183 WHERE pet.element_name = 'TWR Employee Ascendants Information'
1184 AND pet.legislation_code = 'ES'
1185 AND piv.element_type_id =pet.element_type_id
1186 AND pet.element_type_id = prr.element_type_id
1187 AND prr.assignment_action_id= c_assignment_action_id
1188 AND prrv.run_result_id = prr.run_result_id
1189 AND piv.input_value_id = prrv.input_value_id
1190 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1191 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1192 group BY prr.assignment_action_id , prr.run_result_id;
1193 --
1194 CURSOR csr_get_twr_desc_values(c_assignment_action_id number, c_effective_date DATE)IS
1195 SELECT prr.assignment_action_id
1196 ,prr.run_result_id
1197 ,min(decode(piv.name, 'Number of Descendants', prrv.RESULT_VALUE, null)) No_of_Desc
1198 ,min(decode(piv.name, 'Age less than 3', prrv.RESULT_VALUE, null)) No_of_Desc_less_3
1199 ,min(decode(piv.name, 'Age between 3 and 25', prrv.RESULT_VALUE, null)) No_Desc_bet_3_25
1200 ,min(decode(piv.name, 'Disability between 33 and 64', prrv.RESULT_VALUE, null)) No_Desc_disablity_bet_33_65
1201 ,min(decode(piv.name, 'Disability greater than 64', prrv.RESULT_VALUE, null)) No_Desc_disablity_gr_65
1202 ,min(decode(piv.name, 'Reduced Mobility', prrv.RESULT_VALUE, null)) No_Desc_Reduced_Mobility
1203 ,min(decode(piv.name, 'Single Parent', prrv.RESULT_VALUE, null)) No_Desc_Single_Parent
1204 ,min(decode(piv.name, 'Adopted less than 3 years ago', prrv.RESULT_VALUE, null)) No_Desc_Adopted_less_3
1205 ,min(decode(piv.name, 'Special Assistance', prrv.RESULT_VALUE, null)) Desc_Disability_Amt
1206 ,min(decode(piv.name, 'Disability Amount', prrv.RESULT_VALUE, null)) Desc_Special_Assistance
1207 FROM pay_run_results prr
1208 ,pay_run_result_values prrv
1209 ,pay_element_types_f pet
1210 ,pay_input_values_f piv
1211 WHERE pet.element_name = 'TWR Employee Descendants Information'
1212 AND pet.legislation_code = 'ES'
1213 AND piv.element_type_id =pet.element_type_id
1214 AND pet.element_type_id = prr.element_type_id
1215 AND prr.assignment_action_id= c_assignment_action_id
1216 AND prrv.run_result_id = prr.run_result_id
1217 AND piv.input_value_id = prrv.input_value_id
1218 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1219 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1220 group BY prr.assignment_action_id , prr.run_result_id;
1221 --
1222 CURSOR csr_get_twr_amount_values(c_assignment_action_id number, c_effective_date DATE)IS
1223 SELECT prr.assignment_action_id
1224 ,prr.run_result_id
1225 ,min(decode(piv.name, 'Employee Special Assistance', prrv.RESULT_VALUE, null)) Emp_Special_Assistance
1226 ,min(decode(piv.name, 'Employee Disability Assistance', prrv.RESULT_VALUE, null)) Emp_Disability_Assistance
1227 ,min(decode(piv.name, 'Employee Special Allowance', prrv.RESULT_VALUE, null)) Employee_Special_Allowance
1228 ,min(decode(piv.name, 'Employee Age Deduction', prrv.RESULT_VALUE, null)) Emp_Age_Deduction
1229 ,min(decode(piv.name, 'Child Support', prrv.RESULT_VALUE, null)) Child_Support
1230 ,min(decode(piv.name, 'Deductible Expenses', prrv.RESULT_VALUE, null)) Deductible_Expences
1231 ,min(decode(piv.name, 'Irregular Earnings', prrv.RESULT_VALUE, null)) Irregular_Earnings
1232 ,min(decode(piv.name, 'Spouse Alimony', prrv.RESULT_VALUE, null)) Spouse_Alimony
1233 ,min(decode(piv.name, 'Tax Base', prrv.RESULT_VALUE, null)) Tax_Base
1234 ,min(decode(piv.name, 'Override Tax Rate', prrv.RESULT_VALUE, null)) Override_TWR
1235 ,min(decode(piv.name, 'Override Contractual Earnings', prrv.RESULT_VALUE, null)) Override_Cont_Earnings
1236 FROM pay_run_results prr
1237 ,pay_run_result_values prrv
1238 ,pay_element_types_f pet
1239 ,pay_input_values_f piv
1240 WHERE pet.element_name = 'TWR Deduction Information'
1241 AND pet.legislation_code = 'ES'
1242 AND piv.element_type_id =pet.element_type_id
1243 AND pet.element_type_id = prr.element_type_id
1244 AND prr.assignment_action_id= c_assignment_action_id
1245 AND prrv.run_result_id = prr.run_result_id
1246 AND piv.input_value_id = prrv.input_value_id
1247 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1248 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1249 group BY prr.assignment_action_id , prr.run_result_id;
1250 --
1251 CURSOR csr_get_twr_rate(c_assignment_action_id number, c_effective_date DATE)IS
1252 SELECT prr.assignment_action_id
1253 ,prr.run_result_id
1254 ,min(decode(piv.name, 'Rate', prrv.RESULT_VALUE, null)) Rate
1255 FROM pay_run_results prr
1256 ,pay_run_result_values prrv
1257 ,pay_element_types_f pet
1258 ,pay_input_values_f piv
1259 WHERE pet.element_name = 'Tax Withholding Rate'
1260 AND pet.legislation_code = 'ES'
1261 AND piv.element_type_id =pet.element_type_id
1262 AND pet.element_type_id = prr.element_type_id
1263 AND prr.assignment_action_id= c_assignment_action_id
1264 AND prrv.run_result_id = prr.run_result_id
1265 AND piv.input_value_id = prrv.input_value_id
1266 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1267 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1268 GROUP BY prr.assignment_action_id , prr.run_result_id;
1269 --
1270 CURSOR csr_get_tax_rate(c_assignment_action_id number, c_effective_date DATE)IS
1271 SELECT prr.assignment_action_id
1272 ,prr.run_result_id
1273 ,min(decode(piv.name, 'Tax Withholding Rate', prrv.RESULT_VALUE, null)) Rate
1274 FROM pay_run_results prr
1275 ,pay_run_result_values prrv
1276 ,pay_element_types_f pet
1277 ,pay_input_values_f piv
1278 WHERE pet.element_name = 'Tax'
1279 AND pet.legislation_code = 'ES'
1280 AND piv.element_type_id =pet.element_type_id
1281 AND pet.element_type_id = prr.element_type_id
1282 AND prr.assignment_action_id= c_assignment_action_id
1283 AND prrv.run_result_id = prr.run_result_id
1284 AND piv.input_value_id = prrv.input_value_id
1285 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1286 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1287 GROUP BY prr.assignment_action_id , prr.run_result_id;
1288 --
1289 CURSOR csr_get_message(c_assignment_action_id NUMBER) IS
1290 SELECT line_text Msg
1291 FROM pay_message_lines
1292 WHERE source_id = c_assignment_action_id
1293 ORDER BY line_sequence DESC;
1294 --
1295 l_emp_rec csr_get_twr_emp_values%ROWTYPE;
1296 l_asc_rec csr_get_twr_asc_values%ROWTYPE;
1297 l_desc_rec csr_get_twr_desc_values%ROWTYPE;
1298 l_amt_rec csr_get_twr_amount_values%ROWTYPE;
1299 l_twr_rec csr_get_twr_rate%ROWTYPE;
1300 --l_otwr_rec csr_get_twr_override_values%ROWTYPE;
1301 l_emp_detail_rec csr_get_emp_detail%ROWTYPE;
1302 l_msg_rec csr_get_message%ROWTYPE;
1303 l_chk_emp_err VARCHAR2(1);
1304 l_no NUMBER;
1305 l_header VARCHAR2(255);
1306 l_underline VARCHAR2(255);
1307 l_print_emp_info VARCHAR2(255);
1308 l_working_past_retirement VARCHAR2(10);
1309 l_twr_rate NUMBER;
1310 l_total_earnings NUMBER;
1311 --
1312 BEGIN
1313 vXMLTable.DELETE;
1314 vCtr := 1;
1315 hr_utility.trace(' Entering procedure pay_es_twr_calc_pkg.populate_plsql_table ');
1316 l_no := 0;
1317 l_header := /*rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),4)||' '||*/
1318 rpad(hr_general.decode_lookup('ES_FORM_LABELS','ANO'),10)||' '||
1319 rpad(hr_general.decode_lookup('ES_FORM_LABELS','ENAME'),30)||' '||
1320 rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),200);
1321 --
1322 l_underline :=--rpad('-',04,'-')||' '||
1323 rpad('-',10,'-')||' '||
1324 rpad('-',30,'-')||' '||
1325 rpad('-',200,'-')||' ';
1326
1327
1328 FOR i IN csr_get_assignment_ids LOOP
1329 hr_utility.trace(' Assignment id :' || i.assignment_id);
1330 hr_utility.trace(' i.effective_date:' || i.effective_date);
1331
1332 l_msg_rec.Msg := ' ';
1333
1334 OPEN csr_chk_emp_err(i.assignment_action_id, i.effective_date);
1335 FETCH csr_chk_emp_err into l_chk_emp_err;
1336 IF csr_chk_emp_err%notfound then
1337 l_chk_emp_err := 'Y';
1338 END IF;
1339 CLOSE csr_chk_emp_err;
1340
1341 hr_utility.trace(' l_chk_emp_err :' || l_chk_emp_err);
1342 IF l_chk_emp_err = 'Y' THEN
1343 IF l_no = 0 THEN
1344 Fnd_File.New_Line(FND_FILE.LOG,1);
1345 Fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('ES_FORM_LABELS','EXCEPTION_LIST'));
1346 Fnd_file.put_line(FND_FILE.LOG,rpad('-',length(hr_general.decode_lookup('ES_FORM_LABELS','EXCEPTION_LIST')),'-'));
1347 Fnd_File.New_Line(FND_FILE.LOG,1);
1348 Fnd_file.put_line(FND_FILE.LOG,l_underline);
1349 Fnd_file.put_line(FND_FILE.LOG,l_header);
1350 Fnd_file.put_line(FND_FILE.LOG,l_underline);
1351 END IF;
1352 l_no := l_no + 1;
1353
1354 vXMLTable(vCtr).TagName := 'TWR_EFFECTIVE_DATE';
1355 vXMLTable(vCtr).TagValue := fnd_date.date_to_displaydate(i.effective_date);
1356 vCtr := vCtr + 1;
1357
1358 OPEN csr_get_emp_detail(i.assignment_id, i.effective_date);
1359 FETCH csr_get_emp_detail INTO l_emp_detail_rec;
1360 CLOSE csr_get_emp_detail;
1361
1362 vXMLTable(vCtr).TagName := 'TWR_ASSIGNMENT_NO';
1363 vXMLTable(vCtr).TagValue := l_emp_detail_rec.Assignment_Number;
1364 vCtr := vCtr + 1;
1365
1366 vXMLTable(vCtr).TagName := 'TWR_EMP_NAME';
1367 vXMLTable(vCtr).TagValue := l_emp_detail_rec.Name;
1368 vCtr := vCtr + 1;
1369
1370 vXMLTable(vCtr).TagName := 'TWR_AGE';
1371 vXMLTable(vCtr).TagValue := l_emp_detail_rec.Age;
1372 vCtr := vCtr + 1;
1373
1374 vXMLTable(vCtr).TagName := 'TWR_PAYMENT_KEY';
1375 vXMLTable(vCtr).TagValue := get_payment_key(i.assignment_id,i.effective_date);
1376 vCtr := vCtr + 1;
1377
1378 vXMLTable(vCtr).TagName := 'TWR_MARITAL_STATUS';
1379 vXMLTable(vCtr).TagValue := l_emp_detail_rec.Marital_Status;
1380 vCtr := vCtr + 1;
1381
1382
1383 OPEN csr_get_message(i.assignment_action_id);
1384 FETCH csr_get_message into l_msg_rec;
1385 CLOSE csr_get_message;
1386
1387 vXMLTable(vCtr).TagName := 'TWR_ERR_MSG1';
1388 vXMLTable(vCtr).TagValue := l_msg_rec.Msg;
1389 vCtr := vCtr + 1;
1390 l_no := l_no + 1;
1391 l_print_emp_info:= --rpad((to_char(l_no),' '),4)||' '||
1392 rpad(nvl(substr(l_emp_detail_rec.Assignment_Number,1,10),' '),10)||' '||
1393 rpad(nvl(substr(l_emp_detail_rec.Name,1,30),' '),30)||' '||
1394 rpad(nvl(substr(l_msg_rec.Msg,1,200),' '),200);
1395
1396 Fnd_file.put_line(FND_FILE.LOG,l_print_emp_info);
1397
1398 ELSE
1399 OPEN csr_get_twr_emp_values(i.assignment_action_id, i.effective_date);
1400 FETCH csr_get_twr_emp_values into l_emp_rec;
1401 CLOSE csr_get_twr_emp_values;
1402
1403 OPEN csr_get_twr_asc_values(i.assignment_action_id, i.effective_date);
1404 FETCH csr_get_twr_asc_values into l_asc_rec;
1405 CLOSE csr_get_twr_asc_values;
1406
1407 OPEN csr_get_twr_desc_values(i.assignment_action_id, i.effective_date);
1408 FETCH csr_get_twr_desc_values into l_desc_rec;
1409 CLOSE csr_get_twr_desc_values;
1410
1411 OPEN csr_get_twr_amount_values(i.assignment_action_id, i.effective_date);
1412 FETCH csr_get_twr_amount_values into l_amt_rec;
1413 CLOSE csr_get_twr_amount_values;
1414
1415 IF i.shortname = 'TAX_WITHHOLDING_RATE' THEN
1416 OPEN csr_get_twr_rate(i.assignment_action_id, i.effective_date);
1417 FETCH csr_get_twr_rate into l_twr_rec;
1418 CLOSE csr_get_twr_rate;
1419 ELSE
1420 OPEN csr_get_tax_rate(i.assignment_action_id, i.effective_date);
1421 FETCH csr_get_tax_rate into l_twr_rec;
1422 CLOSE csr_get_tax_rate;
1423 END IF;
1424
1425 /*OPEN csr_get_twr_override_values(i.assignment_id, i.effective_date);
1426 FETCH csr_get_twr_override_values into l_otwr_rec;
1427 CLOSE csr_get_twr_override_values;*/
1428
1429 vXMLTable(vCtr).TagName := 'TWR_EFFECTIVE_DATE';
1430 vXMLTable(vCtr).TagValue := fnd_date.date_to_displaydate(i.effective_date);
1431 vCtr := vCtr + 1;
1432
1433 vXMLTable(vCtr).TagName := 'TWR_ASSIGNMENT_NO';
1434 vXMLTable(vCtr).TagValue := l_emp_rec.Assignment_Number;
1435 vCtr := vCtr + 1;
1436
1437 vXMLTable(vCtr).TagName := 'TWR_EMP_NAME';
1438 vXMLTable(vCtr).TagValue := l_emp_rec.Name;
1439 vCtr := vCtr + 1;
1440
1441 vXMLTable(vCtr).TagName := 'TWR_AGE';
1442 vXMLTable(vCtr).TagValue := l_emp_rec.Age;
1443 vCtr := vCtr + 1;
1444
1445 vXMLTable(vCtr).TagName := 'TWR_PAYMENT_KEY';
1446 vXMLTable(vCtr).TagValue := l_emp_rec.Payment_Key;
1447 vCtr := vCtr + 1;
1448
1449 vXMLTable(vCtr).TagName := 'TWR_LENGTH_OF_CONTRACT';
1450 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_emp_rec.Length_Of_Contract);
1451 vCtr := vCtr + 1;
1452
1453 vXMLTable(vCtr).TagName := 'TWR_EMP_MOVED';
1454 vXMLTable(vCtr).TagValue := l_emp_rec.Change_in_Residency;
1455 vCtr := vCtr + 1;
1456
1457 vXMLTable(vCtr).TagName := 'TWR_CONTRACT_TYPE';
1458 vXMLTable(vCtr).TagValue := l_emp_rec.Contract_Type;
1459 vCtr := vCtr + 1;
1460
1461 vXMLTable(vCtr).TagName := 'TWR_EMPLOYEE_DOD';
1462 vXMLTable(vCtr).TagValue := l_emp_rec.Emp_DOD;
1463 vCtr := vCtr + 1;
1464
1465 vXMLTable(vCtr).TagName := 'TWR_EMP_DISABLED';
1466 vXMLTable(vCtr).TagValue := l_emp_rec.Disabled;
1467 vCtr := vCtr + 1;
1468
1469 l_working_past_retirement := ' ';
1470 IF SUBSTR(l_emp_rec.Payment_Key,1,1) NOT IN ('E', 'F') THEN
1471 IF l_emp_rec.Age >= 75 THEN
1472 l_working_past_retirement := hr_general.decode_lookup('YES_NO','Y');
1473 ELSE
1474 l_working_past_retirement := hr_general.decode_lookup('YES_NO','N');
1475 END IF;
1476 END IF;
1477
1478 vXMLTable(vCtr).TagName := 'TWR_WORK_PAST_RETIREMENT';
1479 vXMLTable(vCtr).TagValue := l_working_past_retirement;
1480 vCtr := vCtr + 1;
1481
1482 vXMLTable(vCtr).TagName := 'TWR_MARITAL_STATUS';
1483 vXMLTable(vCtr).TagValue := l_emp_rec.Marital_Status;
1484 vCtr := vCtr + 1;
1485
1486 vXMLTable(vCtr).TagName := 'TWR_AMERICA_CUP_FLAG';
1487 vXMLTable(vCtr).TagValue := l_emp_rec.America_Cup_Flag;
1488 vCtr := vCtr + 1;
1489
1490 vXMLTable(vCtr).TagName := 'TWR_WORK_STATUS';
1491 vXMLTable(vCtr).TagValue := l_emp_rec.Work_Status;
1492 vCtr := vCtr + 1;
1493
1494 vXMLTable(vCtr).TagName := 'TWR_RESIDENT_C_M';
1495 vXMLTable(vCtr).TagValue := l_emp_rec.Resident_Ceuta_Melila;
1496 vCtr := vCtr + 1;
1497
1498 IF l_amt_rec.Override_Cont_Earnings IS NOT NULL THEN
1499 l_total_earnings := NULL;
1500 ELSE
1501 l_total_earnings := l_emp_rec.Calc_Cont_Earnings;
1502 END IF;
1503 vXMLTable(vCtr).TagName := 'TWR_TOTAL_EARNINGS';
1504 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_total_earnings);
1505 vCtr := vCtr + 1;
1506
1507 vXMLTable(vCtr).TagName := 'TWR_CALC_CONT_EARNINGS';
1508 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_emp_rec.Calc_Cont_Earnings);
1509 vCtr := vCtr + 1;
1510
1511 vXMLTable(vCtr).TagName := 'TWR_NO_OF_DESC';
1512 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_of_Desc);
1513 vCtr := vCtr + 1;
1514
1515 vXMLTable(vCtr).TagName := 'TWR_NO_OF_DESC_LESS_3';
1516 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_of_Desc_less_3);
1517 vCtr := vCtr + 1;
1518
1519 vXMLTable(vCtr).TagName := 'TWR_NO_OF_DESC_BET_3_25';
1520 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_Desc_bet_3_25);
1521 vCtr := vCtr + 1;
1522
1523 vXMLTable(vCtr).TagName := 'TWR_NO_OF_DESC_ADOPT_LESS_3';
1524 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_Desc_Adopted_less_3);
1525 vCtr := vCtr + 1;
1526
1527 vXMLTable(vCtr).TagName := 'TWR_DESC_DISABILITY_BET_33_65';
1528 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_Desc_disablity_bet_33_65);
1529 vCtr := vCtr + 1;
1530
1531 vXMLTable(vCtr).TagName := 'TWR_DESC_DISABILITY_GR_65';
1532 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_Desc_disablity_gr_65);
1533 vCtr := vCtr + 1;
1534
1535 vXMLTable(vCtr).TagName := 'TWR_DESC_REDUCED_MOBILITY';
1536 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_Desc_Reduced_Mobility);
1537 vCtr := vCtr + 1;
1538
1539 vXMLTable(vCtr).TagName := 'TWR_DESC_SINGLE_PARENT';
1540 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.No_Desc_Single_Parent);
1541 vCtr := vCtr + 1;
1542
1543 vXMLTable(vCtr).TagName := 'TWR_DESC_DISABILITY_AMT';
1544 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.Desc_Disability_Amt);
1545 vCtr := vCtr + 1;
1546
1547 vXMLTable(vCtr).TagName := 'TWR_DESC_SPL_ASSISTANCE_AMT';
1548 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_desc_rec.DESC_SPECIAL_ASSISTANCE);
1549 vCtr := vCtr + 1;
1550
1551 vXMLTable(vCtr).TagName := 'TWR_NO_OF_ASC';
1552 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.NO_OF_ASC);
1553 vCtr := vCtr + 1;
1554
1555 vXMLTable(vCtr).TagName := 'TWR_NO_OF_ASC_GR_75';
1556 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.No_of_Asc_Gr_75);
1557 vCtr := vCtr + 1;
1558
1559 vXMLTable(vCtr).TagName := 'TWR_ASC_DISABILITY_BET_33_65';
1560 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.No_Asc_disablity_bet_33_65);
1561 vCtr := vCtr + 1;
1562
1563 vXMLTable(vCtr).TagName := 'TWR_ASC_DISABILITY_GR_65';
1564 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.No_Asc_disablity_gr_65);
1565 vCtr := vCtr + 1;
1566
1567 vXMLTable(vCtr).TagName := 'TWR_ASC_REDUCED_MOBILITY';
1568 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.No_Asc_Reduced_Mobility);
1569 vCtr := vCtr + 1;
1570
1571 vXMLTable(vCtr).TagName := 'TWR_ASC_SNGLE_DESC';
1572 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.No_Asc_Single_Descendent);
1573 vCtr := vCtr + 1;
1574
1575 vXMLTable(vCtr).TagName := 'TWR_ASC_DISABILITY_AMT';
1576 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.Asc_Disability_Amt);
1577 vCtr := vCtr + 1;
1578
1579 vXMLTable(vCtr).TagName := 'TWR_ASC_SPECIAL_ALLOWANCE';
1580 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.Asc_Special_Allowance);
1581 vCtr := vCtr + 1;
1582
1583 vXMLTable(vCtr).TagName := 'TWR_ASC_AGE_DEDUCTION';
1584 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.Asc_Age_Deduction);
1585 vCtr := vCtr + 1;
1586
1587 vXMLTable(vCtr).TagName := 'TWR_ASC_SPL_ASSISTANCE_AMT';
1588 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_asc_rec.Asc_Special_Assistance);
1589 vCtr := vCtr + 1;
1590
1591 vXMLTable(vCtr).TagName := 'TWR_EMP_SPECIAL_ASSISTANCE';
1592 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Emp_Special_Assistance);
1593 vCtr := vCtr + 1;
1594
1595 vXMLTable(vCtr).TagName := 'TWR_EMP_DISABILITY_ASSISTANCE';
1596 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Emp_Disability_Assistance);
1597 vCtr := vCtr + 1;
1598
1599 vXMLTable(vCtr).TagName := 'TWR_EMPLOYEE_SPECIAL_ALLOWANCE';
1600 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Employee_Special_Allowance);
1601 vCtr := vCtr + 1;
1602
1603 vXMLTable(vCtr).TagName := 'TWR_EMP_AGE_DEDUCTION';
1604 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Emp_Age_Deduction);
1605 vCtr := vCtr + 1;
1606
1607 vXMLTable(vCtr).TagName := 'TWR_SPOUSE_ALIMONY';
1608 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Spouse_Alimony);
1609 vCtr := vCtr + 1;
1610
1611 vXMLTable(vCtr).TagName := 'TWR_CHILD_SUPPORT';
1612 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Child_Support);
1613 vCtr := vCtr + 1;
1614
1615 vXMLTable(vCtr).TagName := 'TWR_DEDUCTIBLE_EXPENSES';
1616 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Deductible_Expences);
1617 vCtr := vCtr + 1;
1618
1619 vXMLTable(vCtr).TagName := 'TWR_IRREGULAR_EARNINGS';
1620 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Irregular_Earnings);
1621 vCtr := vCtr + 1;
1622
1623 vXMLTable(vCtr).TagName := 'TWR_TAX_BASE';
1624 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Tax_Base);
1625 vCtr := vCtr + 1;
1626
1627 vXMLTable(vCtr).TagName := 'TWR_OVERRIDE_RATE';
1628 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Override_TWR);
1629 vCtr := vCtr + 1;
1630
1631 vXMLTable(vCtr).TagName := 'TWR_OVERRIDE_CONT_EARNINGS';
1632 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_amt_rec.Override_Cont_Earnings);
1633 vCtr := vCtr + 1;
1634
1635 IF l_amt_rec.Override_TWR IS NOT NULL THEN
1636 l_twr_rate := NULL;
1637 ELSE
1638 l_twr_rate := l_twr_rec.Rate;
1639 END IF;
1640
1641 vXMLTable(vCtr).TagName := 'TWR_RATE';
1642 vXMLTable(vCtr).TagValue := fnd_number.canonical_to_number(l_twr_rate);
1643 vCtr := vCtr + 1;
1644 END IF;
1645 --
1646 END LOOP;
1647 hr_utility.trace(' Leaving procedure pay_es_twr_calc_pkg.populate_plsql_table ');
1648 END populate_plsql_table ;
1649 --
1650 -------------------------------------------------------------------------------
1651 -- WRITETOCLOB
1652 --------------------------------------------------------------------------------
1653 PROCEDURE WritetoCLOB (p_xfdf_blob OUT NOCOPY blob
1654 ,p_xfdf_string OUT NOCOPY clob)
1655 IS
1656 l_str1 varchar2(1000);
1657 l_str2 varchar2(20);
1658 l_str3 varchar2(20);
1659 l_str4 varchar2(20);
1660 l_str5 varchar2(20);
1661 l_str6 varchar2(30);
1662 l_str7 varchar2(1000);
1663 l_str8 varchar2(240);
1664 l_str9 varchar2(240);
1665 BEGIN
1666 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
1667 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
1668 <fields> ' ;
1669 l_str2 := '<field name="';
1670 l_str3 := '">';
1671 l_str4 := '<value><![CDATA[';
1672 l_str5 := ']]></value> </field>' ;
1673 l_str6 := '</fields> </xfdf>';
1674 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
1675 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
1676 <fields>
1677 </fields> </xfdf>';
1678 dbms_lob.createtemporary(p_xfdf_string,FALSE,DBMS_LOB.CALL);
1679 dbms_lob.open(p_xfdf_string,dbms_lob.lob_readwrite);
1680 IF vXMLTable.count > 0 THEN
1681 dbms_lob.writeAppend( p_xfdf_string, length(l_str1), l_str1 );
1682 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
1683 l_str8 := vXMLTable(ctr_table).TagName;
1684 l_str9 := vXMLTable(ctr_table).TagValue;
1685 --
1686 IF (l_str9 is not null) THEN
1687 dbms_lob.writeAppend( p_xfdf_string, length(l_str2), l_str2 );
1688 dbms_lob.writeAppend( p_xfdf_string, length(l_str8),l_str8);
1689 dbms_lob.writeAppend( p_xfdf_string, length(l_str3), l_str3 );
1690 dbms_lob.writeAppend( p_xfdf_string, length(l_str4), l_str4 );
1691 dbms_lob.writeAppend( p_xfdf_string, length(l_str9), l_str9);
1692 dbms_lob.writeAppend( p_xfdf_string, length(l_str5), l_str5 );
1693 ELSE
1694 null;
1695 END IF;
1696 END LOOP;
1697 dbms_lob.writeAppend( p_xfdf_string, length(l_str6), l_str6 );
1698 ELSE
1699 dbms_lob.writeAppend( p_xfdf_string, length(l_str7), l_str7 );
1700 END IF;
1701 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
1702 clob_to_blob(p_xfdf_string,p_xfdf_blob);
1703 --return p_xfdf_blob;
1704 EXCEPTION
1705 WHEN OTHERS then
1706 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1707 HR_UTILITY.RAISE_ERROR;
1708 END WritetoCLOB;
1709 --------------------------------------------------------------------------------
1710 -- CLOB_TO_BLOB
1711 --------------------------------------------------------------------------------
1712 PROCEDURE clob_to_blob(p_clob CLOB
1713 ,p_blob IN OUT NOCOPY BLOB) IS
1714 --
1715 l_length_clob NUMBER;
1716 l_offset pls_integer;
1717 l_varchar_buffer VARCHAR2(32767);
1718 l_raw_buffer RAW(32767);
1719 l_buffer_len NUMBER;
1720 l_chunk_len NUMBER;
1721 l_blob blob;
1722 g_nls_db_char VARCHAR2(60);
1723 --
1724 l_raw_buffer_len pls_integer;
1725 l_blob_offset pls_integer := 1;
1726 --
1727 BEGIN
1728 --
1729 hr_utility.set_location('Entered Procedure clob to blob',120);
1730 --
1731 SELECT userenv('LANGUAGE') INTO g_nls_db_char FROM dual;
1732 --
1733 l_buffer_len := 20000;
1734 l_length_clob := dbms_lob.getlength(p_clob);
1735 l_offset := 1;
1736 --
1737 while l_length_clob > 0 loop
1738 --
1739 IF l_length_clob < l_buffer_len THEN
1740 l_chunk_len := l_length_clob;
1741 ELSE
1742 l_chunk_len := l_buffer_len;
1743 END IF;
1744 --
1745 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
1746 --
1747 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.AL32UTF8',g_nls_db_char);
1748 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.AL32UTF8',g_nls_db_char));
1749 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
1750 --
1751 l_blob_offset := l_blob_offset + l_raw_buffer_len;
1752 l_offset := l_offset + l_chunk_len;
1753 l_length_clob := l_length_clob - l_chunk_len;
1754 --
1755 END LOOP;
1756 hr_utility.set_location('Finished Procedure clob to blob ',130);
1757 END;
1758 --
1759 --------------------------------------------------------------------------------
1760 -- GET_CONTRACTUAL_EARNINGS
1761 --------------------------------------------------------------------------------
1762 FUNCTION get_contractual_earnings(p_assignment_id IN NUMBER
1763 ,p_calculation_date IN DATE
1764 ,p_name IN VARCHAR2
1765 ,p_rt_element IN VARCHAR2
1766 ,p_to_time_dim IN VARCHAR2
1767 ,p_rate IN OUT NOCOPY NUMBER
1768 ,p_error_message IN OUT NOCOPY VARCHAR2) RETURN NUMBER
1769 IS
1770 --
1771 CURSOR csr_get_work_center(c_assignment_id NUMBER
1772 ,c_effective_date DATE) IS
1773 SELECT scl.segment2
1774 ,paaf.person_id
1775 FROM per_all_assignments_f paaf
1776 ,hr_soft_coding_keyflex scl
1777 WHERE paaf.assignment_id = c_assignment_id
1778 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1779 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1780 --
1781 CURSOR csr_get_work_centers(c_work_center NUMBER) IS
1782 SELECT hoi2.org_information1 work_center
1783 FROM hr_organization_information hoi1
1784 ,hr_organization_information hoi2
1785 WHERE hoi1.organization_id = hoi2.organization_id
1786 AND hoi1.org_information1 = c_work_center
1787 AND hoi1.org_information_context = 'ES_WORK_CENTER_REF'
1788 AND hoi2.org_information_context = 'ES_WORK_CENTER_REF';
1789 --
1790 CURSOR csr_get_assignment_id(c_person_id NUMBER
1791 ,c_effective_date DATE
1792 ,c_work_center NUMBER) IS
1793 SELECT paaf.assignment_id
1794 FROM per_all_assignments_f paaf
1795 ,hr_soft_coding_keyflex scl
1796 WHERE paaf.person_id = c_person_id
1797 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1798 AND scl.segment2 = c_work_center
1799 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1800 --
1801 l_salary NUMBER;
1802 l_tot_salary NUMBER;
1803 l_work_center NUMBER;
1804 l_person_id NUMBER;
1805 --
1806 BEGIN
1807 --
1808 OPEN csr_get_work_center(p_assignment_id,p_calculation_date);
1809 FETCH csr_get_work_center INTO l_work_center,l_person_id;
1810 CLOSE csr_get_work_center;
1811 --
1812 l_tot_salary := 0;
1813 l_salary := 0;
1814 --
1815 FOR i in csr_get_work_centers(l_work_center) LOOP
1816 hr_utility.trace(' Work Center ID : '||i.work_center);
1817
1818 FOR l_rec IN csr_get_assignment_id(l_person_id,p_calculation_date,i.work_center) LOOP
1819 --
1820 hr_utility.trace(' Assignment ID : '||l_rec.assignment_id);
1821
1822 l_salary := pqp_rates_history_calc.get_historic_rate(
1823 p_assignment_id => l_rec.assignment_id
1824 ,p_rate_name => p_name
1825 ,p_effective_date => p_calculation_date
1826 ,p_time_dimension => p_to_time_dim
1827 ,p_rate_type_or_element => p_rt_element
1828 );
1829 l_tot_salary := l_tot_salary + l_salary;
1830 END LOOP;
1831 END LOOP;
1832 p_rate := l_tot_salary;
1833 p_error_message := 'No Error';
1834 RETURN l_tot_salary;
1835 --
1836 END get_contractual_earnings;
1837 --
1838 --------------------------------------------------------------------------------
1839 -- CALC_WITHHOLDING_QUOTA
1840 --------------------------------------------------------------------------------
1841 FUNCTION calc_withholding_quota(p_business_gr_id IN NUMBER
1842 ,p_effective_date IN DATE
1843 ,p_tax_base IN NUMBER) RETURN NUMBER IS
1844 --
1845 CURSOR c_get_rows(c_efective_date DATE) IS
1846 SELECT to_number(pur.row_low_range_or_name) Low_val
1847 ,to_number(pur.ROW_HIGH_RANGE) high_val
1848 FROM pay_user_rows_f pur
1849 ,pay_user_tables put
1850 WHERE put.legislation_code = 'ES'
1851 AND pur.user_table_id = put.user_table_id
1852 AND put.user_table_name like 'ES_WITHHOLDING_QUOTAS'
1853 AND c_efective_date BETWEEN pur.effective_start_date AND pur.effective_end_date
1854 ORDER BY 1;
1855 --
1856 l_tax_base NUMBER;
1857 l_withholding_quota NUMBER;
1858 l_perc NUMBER;
1859 l_diff NUMBER;
1860 --
1861
1862 BEGIN
1863 --
1864 l_tax_base := p_tax_base;
1865 l_withholding_quota := 0;
1866 --
1867 FOR i IN c_get_rows(p_effective_date) LOOP
1868 --
1869 l_diff := i.high_val - round(i.low_val);
1870 l_perc := get_table_value(bus_group_id => p_business_gr_id
1871 ,ptab_name => 'ES_WITHHOLDING_QUOTAS'
1872 ,pcol_name => 'WITHHOLDING_QUOTAS'
1873 ,prow_value => i.high_val
1874 ,peffective_date => p_effective_date);
1875 IF l_tax_base <= l_diff THEN
1876 l_withholding_quota := l_withholding_quota + l_tax_base * l_perc/100;
1877 RETURN l_withholding_quota;
1878 ELSE
1879 l_withholding_quota := l_withholding_quota + (l_diff * l_perc/100);
1880 END IF;
1881 l_tax_base := l_tax_base - l_diff;
1882 END LOOP;
1883 RETURN l_withholding_quota;
1884 END calc_withholding_quota;
1885 --
1886 --
1887 --------------------------------------------------------------------------------
1888 -- CALC_WITHHOLDING_QUOTA
1889 --------------------------------------------------------------------------------
1890 FUNCTION get_contract_end_date(p_assignment_id IN NUMBER
1891 ,p_effective_date IN DATE) RETURN DATE
1892 IS
1893 CURSOR csr_get_contract_end_date(c_assignment_id NUMBER
1894 ,c_effective_date DATE) IS
1895 SELECT NVL(fnd_date.canonical_to_date(CTR_INFORMATION4),to_date('31-12-4712','dd-mm-yyyy')) Contract_End_Date
1896 FROM PER_CONTRACTS_f pcf
1897 ,per_all_assignments_f paaf
1898 WHERE paaf.assignment_id = c_assignment_id
1899 AND paaf.contract_id = pcf.contract_id
1900 AND pcf.ctr_information_category = 'ES'
1901 AND sysdate BETWEEN paaf.effective_start_date
1902 AND paaf.effective_end_date
1903 AND sysdate BETWEEN pcf.effective_start_date
1904 AND pcf.effective_end_date;
1905 l_end_date DATE;
1906 --
1907 BEGIN
1908 --
1909 OPEN csr_get_contract_end_date(p_assignment_id,p_effective_date);
1910 FETCH csr_get_contract_end_date INTO l_end_date;
1911 IF csr_get_contract_end_date%NOTFOUND THEN
1912 CLOSE csr_get_contract_end_date;
1913 RETURN to_date('31-12-4712','dd-mm-yyyy');
1914 END IF;
1915 CLOSE csr_get_contract_end_date;
1916 --
1917 RETURN l_end_date;
1918 --
1919 END get_contract_end_date;
1920 --
1921 --------------------------------------------------------------------------------
1922 -- CALC_WITHHOLDING_QUOTA
1923 --------------------------------------------------------------------------------
1924 FUNCTION get_contractual_deductions(p_assignment_id IN NUMBER
1925 ,p_calculation_date IN DATE
1926 ,p_period_start_date IN DATE
1927 ,p_period_end_date IN DATE
1928 ,p_pay_periods_per_year IN NUMBER
1929 ,p_pay_proc_period_number IN NUMBER
1930 ,p_child_support_amt OUT NOCOPY NUMBER
1931 ,p_spouse_alimony_amt OUT NOCOPY NUMBER)
1932 RETURN NUMBER IS
1933 --
1934 CURSOR csr_get_work_center(c_assignment_id NUMBER
1935 ,c_effective_date DATE) IS
1936 SELECT scl.segment2
1937 ,paaf.person_id
1938 FROM per_all_assignments_f paaf
1939 ,hr_soft_coding_keyflex scl
1940 WHERE paaf.assignment_id = c_assignment_id
1941 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1942 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1943 --
1944 CURSOR csr_get_work_centers(c_work_center NUMBER) IS
1945 SELECT hoi2.org_information1 work_center
1946 FROM hr_organization_information hoi1
1947 ,hr_organization_information hoi2
1948 WHERE hoi1.organization_id = hoi2.organization_id
1949 AND hoi1.org_information1 = c_work_center
1950 AND hoi1.org_information_context = 'ES_WORK_CENTER_REF'
1951 AND hoi2.org_information_context = 'ES_WORK_CENTER_REF';
1952 --
1953 CURSOR csr_get_assignment_id(c_person_id NUMBER
1954 ,c_effective_date DATE
1955 ,c_work_center NUMBER) IS
1956 SELECT paaf.assignment_id
1957 ,paaf.payroll_id
1958 ,pet.element_name element_name
1959 ,pee.element_entry_id
1960 ,pee.effective_start_date rec_start_date
1961 ,pee.effective_end_date rec_end_date
1962 ,min(decode(piv.name, 'Amount', peev.screen_entry_value , null)) Amount
1963 ,min(decode(piv.name, 'Period Type', peev.screen_entry_value , null)) Period_type
1964 ,min(decode(piv.name, 'Start Date', peev.screen_entry_value , null)) Start_date
1965 ,min(decode(piv.name, 'End Date', peev.screen_entry_value , null)) End_date
1966 FROM per_all_assignments_f paaf
1967 ,hr_soft_coding_keyflex scl
1968 ,pay_element_entries_f pee
1969 ,pay_element_entry_values_f peev
1970 ,pay_element_types_f pet
1971 ,pay_input_values_f piv
1972 WHERE paaf.person_id = c_person_id
1973 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1974 AND scl.segment2 = c_work_center
1975 AND pee.assignment_id = paaf.assignment_id
1976 AND pee.element_entry_id = peev.element_entry_id
1977 AND pet.legislation_code = 'ES'
1978 AND pet.element_name in ('Child Support','Spouse Alimony')
1979 AND pet.element_type_id = pee.element_type_id
1980 AND piv.element_type_id = pet.element_type_id
1981 AND piv.input_value_id = peev.input_value_id
1982 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
1983 AND c_effective_date between pee.effective_start_date and pee.effective_end_date
1984 AND c_effective_date between peev.effective_start_date and peev.effective_end_date
1985 AND c_effective_date between piv.effective_start_date and piv.effective_end_date
1986 AND c_effective_date between pet.effective_start_date and pet.effective_end_date
1987 group by paaf.assignment_id
1988 ,paaf.payroll_id
1989 ,pet.element_name
1990 ,pee.element_entry_id
1991 ,pee.effective_start_date
1992 ,pee.effective_end_date;
1993
1994 --
1995 l_salary NUMBER;
1996 l_tot_salary NUMBER;
1997 l_work_center NUMBER;
1998 l_person_id NUMBER;
1999 l_start_date DATE;
2000 l_end_date DATE;
2001 l_end_year DATE;
2002 l_amt NUMBER;
2003 l_end_period_no NUMBER;
2004 l_curr_period_no NUMBER;
2005 l_co_start_date DATE;
2006 l_co_end_date DATE;
2007 --
2008 BEGIN
2009 --
2010 OPEN csr_get_work_center(p_assignment_id,p_calculation_date);
2011 FETCH csr_get_work_center INTO l_work_center,l_person_id;
2012 CLOSE csr_get_work_center;
2013 --
2014 p_child_support_amt := 0;
2015 p_spouse_alimony_amt := 0;
2016 l_end_year := TO_DATE('3112'||TO_CHAR(p_calculation_date,'YYYY'),'ddmmyyyy');
2017 --
2018 FOR l_rec in csr_get_work_centers(l_work_center) LOOP
2019 hr_utility.trace(' Work Center ID : '||l_rec.work_center);
2020 FOR i IN csr_get_assignment_id(l_person_id,p_calculation_date,l_rec.work_center) LOOP
2021 --
2022 l_co_start_date := fnd_date.canonical_to_date(i.start_date);
2023 l_co_end_date := fnd_date.canonical_to_date(i.end_date);
2024 l_amt := 0;
2025 l_start_date := GREATEST(p_period_start_date,nvl(l_co_start_date,p_period_start_date));
2026 l_end_date := LEAST(l_end_year,nvl(l_co_end_date,l_end_year));
2027 IF i.Period_type = 'A' THEN
2028 IF l_co_end_date IS NULL THEN
2029 l_amt := (i.Amount/p_pay_periods_per_year)*(p_pay_periods_per_year - p_pay_proc_period_number + 1);
2030 ELSE
2031 l_end_period_no := get_pay_period_number(i.payroll_id,l_end_date);
2032 l_curr_period_no := get_pay_period_number(i.payroll_id,l_start_date);
2033 l_amt := (i.Amount/p_pay_periods_per_year)*(l_end_period_no - l_curr_period_no + 1);
2034 END IF;
2035 ELSE
2036 l_end_period_no := get_pay_period_number(i.payroll_id,l_end_date);
2037 l_curr_period_no := get_pay_period_number(i.payroll_id,l_start_date);
2038 l_amt := i.Amount*(l_end_period_no - l_curr_period_no + 1);
2039 END IF;
2040 --
2041 IF i.element_name = 'Child Support' THEN
2042 p_child_support_amt := p_child_support_amt + l_amt;
2043 ELSE
2044 p_spouse_alimony_amt := p_spouse_alimony_amt + l_amt;
2045 END IF;
2046 END LOOP;
2047 END LOOP;
2048 RETURN 0;
2049 --
2050 END get_contractual_deductions;
2051 --
2052 END pay_es_twr_calc_pkg;