[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_ROE_EI_PKG
Source
1 PACKAGE BODY pay_ca_roe_ei_pkg AS
2 /* $Header: pycaroei.pkb 120.10 2007/03/23 05:46:16 pganguly noship $ */
3
4 --
5 -- Functions/Procedures
6 --
7
8 -------------------------------------------------------------------------------
9 -- Name: populate_date_lookup_table
10 --
11 -- Parameters: p_payroll_id
12 -- p_assignment_id
13 -- p_start_date
14 -- p_effective_date
15 -- p_last_period_start_date
16 --
17 -- Return: Rehire flag set either to Y or N
18 --
19 -- Description: This procedure populates a PL/SQL table with data on the
20 -- periods of a given payroll. This table is created for
21 -- performance reasons. If the employee has been rehired in
22 -- the same pay period as the previous ROE then the days from
23 -- the rehire date until the first pay period end date are also
24 -- saved in the PL/SQL table
25 -------------------------------------------------------------------------------
26 FUNCTION populate_date_lookup_table
27 (p_payroll_id NUMBER,
28 p_assignment_id NUMBER,
29 p_start_date DATE,
30 p_effective_date DATE,
31 p_last_period_start_date DATE)
32 RETURN VARCHAR2 IS
33
34 CURSOR csr_periods (p_payroll_id NUMBER,
35 p_start_date DATE,
36 p_end_date DATE) IS
37 SELECT tpd.start_date,
38 tpd.end_date
39 FROM per_time_periods tpd
40 WHERE payroll_id = p_payroll_id
41 AND ((start_date >= p_start_date
42 AND end_date < p_end_date )
43 OR
44 (start_date <= p_start_date
45 AND end_date >= p_start_date ))
46 ORDER BY start_date DESC;
47
48 CURSOR csr_prev_roe_end_date (p_payroll_id NUMBER,
49 p_start_date DATE) IS
50 SELECT tpd.end_date
51 FROM per_time_periods tpd
52 WHERE tpd.payroll_id = p_payroll_id
53 AND p_start_date BETWEEN
54 tpd.start_date
55 AND tpd.end_date;
56
57 CURSOR csr_rehire_date (p_asg_id NUMBER,
58 p_end_date DATE,
59 p_start_date DATE) IS
60 SELECT MAX(service.date_start) hire_date
61 FROM per_periods_of_service service,
62 per_assignments_f asg
63 WHERE asg.assignment_id = p_asg_id
64 AND p_end_date BETWEEN
65 asg.effective_start_date
66 AND asg.effective_end_date
67 AND asg.person_id = service.person_id
68 AND service.date_start BETWEEN
69 asg.effective_start_date
70 AND asg.effective_end_date
71 AND service.date_start <= p_end_date
72 AND service.date_start >= p_start_date;
73
74 l_proc_name VARCHAR2(60) := 'pay_ca_roe_ei_pkg.populate_date_lookup_table';
75
76 l_period_num NUMBER;
77 l_days NUMBER;
78 l_rehire_date DATE;
79 l_rehire_flag VARCHAR2(1) := 'N';
80 l_prev_roe_end_date DATE;
81 l_first_start_date DATE;
82
83 BEGIN
84 hr_utility.set_location('Starting: ' || l_proc_name, 10);
85 hr_utility.set_location('p_start_date: ' || p_start_date, 10.5);
86 hr_utility.set_location('p_effective_date: ' || p_effective_date, 11);
87 hr_utility.set_location('p_last_period_start_date: ' || p_last_period_start_date, 12);
88
89 l_period_num := 1;
90 l_days := 0;
91
92 FOR l_index IN 0..(p_effective_date-p_last_period_start_date) LOOP
93
94 l_days_from_start(l_days) := l_days;
95 l_period_number(l_days) := l_period_num;
96
97 hr_utility.trace('Day : '||to_char(l_days_from_start(l_days))|| ' Period : '|| to_char(l_period_number(l_days)));
98
99 l_days := l_days + 1;
100
101 END LOOP;
102 l_period_num := l_period_num + 1;
103
104 FOR r_periods IN csr_periods (p_payroll_id,
105 p_start_date,
106 p_effective_date) LOOP
107
108 FOR l_index IN 0..(r_periods.end_date-r_periods.start_date) LOOP
109
110 l_days_from_start(l_days) := l_days;
111 l_period_number(l_days) := l_period_num;
112
113 hr_utility.trace('Day : '||to_char(l_days_from_start(l_days))|| ' Period : '|| to_char(l_period_number(l_days)));
114
115 l_days := l_days + 1;
116
117 END LOOP;
118
119 l_period_num := l_period_num + 1;
120
121 -- After the final iteration l_first_start_date
122 -- will have the very first start date
123
124 l_first_start_date := r_periods.start_date;
125
126 END LOOP;
127
128 hr_utility.trace('p_start_date : ' || to_char(p_start_date));
129
130 -- This section checks to see if the employee was rehired in the
131 -- same period as the previous ROE (same period as p_start_date)
132 -- if so it we will return Y otherwise N
133 -- The days between the rehired date and the end of the period are
134 -- also saved in the PL/SQL table
135 -- Generally the earnings that fall in the same period as the previous
136 -- ROE should not be archived, since they would have been archived in
137 -- the previous ROE run, however in the case of a rehire they wouldn't
138 -- have been archived, hence the need for this section
139
140
141 -- Find the end date of the pay period the previous ROE falls into
142
143 OPEN csr_prev_roe_end_date (p_payroll_id, p_start_date);
144 FETCH csr_prev_roe_end_date INTO l_prev_roe_end_date;
145
146 IF csr_prev_roe_end_date%FOUND THEN
147
148 hr_utility.trace('l_prev_roe_end_date : '|| to_char(l_prev_roe_end_date));
149
150 -- Find any rehire dates that fall between the previous (ROE date + 1)
151 -- and the end date of it's pay period
152
153 OPEN csr_rehire_date (p_assignment_id, l_prev_roe_end_date, p_start_date);
154 FETCH csr_rehire_date INTO l_rehire_date;
155
156 IF csr_rehire_date%FOUND AND
157 l_rehire_date IS NOT NULL THEN
158
159 hr_utility.trace('l_rehire_date : ' || to_char(l_rehire_date));
160 hr_utility.trace('l_first_start_date : ' || to_char(l_first_start_date));
161 hr_utility.trace('p_last_period_start_date : ' || to_char(p_last_period_start_date));
162
163 -- If the rehire date falls under the correct range then store
164 -- the period between the rehire date and the end of the first
165 -- period. The last condition ensures that we do not store any
166 -- periods more than once
167
168 IF l_rehire_date >= p_start_date AND
169 l_rehire_date <= l_prev_roe_end_date AND
170 l_rehire_date < nvl(l_first_start_date, p_last_period_start_date) THEN
171
172 FOR l_index IN 0..(l_prev_roe_end_date-l_rehire_date) LOOP
173
174 l_days_from_start(l_days) := l_days;
175 l_period_number(l_days) := l_period_num;
176
177 hr_utility.trace('Day : '||to_char(l_days_from_start(l_days))|| ' Period : '|| to_char(l_period_number(l_days)));
178
179 l_days := l_days + 1;
180
181 END LOOP;
182
183 l_rehire_flag := 'Y';
184
185 END IF;
186
187 END IF;
188
189 CLOSE csr_rehire_date;
190
191 END IF;
192
193 CLOSE csr_prev_roe_end_date;
194
195 hr_utility.trace('Rehire flag returned : ' || l_rehire_flag);
196 hr_utility.set_location('Ending: ' || l_proc_name, 1000);
197
198 RETURN l_rehire_flag;
199
200 END populate_date_lookup_table;
201
202
203 -------------------------------------------------------------------------------
204 -- Name: taxability_rule_exists
205 --
206 -- Parameters: p_classification_name
207 -- p_classification_id
208 -- p_tax_category
209 -- p_effective_date
210 -- p_tax_type
211 --
212 -- Return: VARCHAR2 - 'TRUE' or 'FALSE'
213 --
214 -- Description: This procedure determines whether a taxability rule is
215 -- required. If one is required it also ensures that the rule is
216 -- applied.
217 -------------------------------------------------------------------------------
218 FUNCTION taxability_rule_exists
219 (p_classification_name VARCHAR2,
220 p_classification_id NUMBER,
221 p_tax_category VARCHAR2,
222 p_effective_date DATE,
223 p_tax_type VARCHAR2)
224 RETURN VARCHAR2 IS
225
226 CURSOR csr_get_taxability_rules (p_class_name VARCHAR2,
227 p_class_id NUMBER,
228 p_tax_cat VARCHAR2,
229 p_eff_date DATE) IS
230 SELECT 1
231 FROM pay_taxability_rules_dates trd,
232 pay_taxability_rules txr
233 WHERE txr.classification_id = p_class_id
234 AND txr.tax_type = 'EIM'
235 AND txr.tax_category = p_tax_cat
236 AND trd.taxability_rules_date_id = txr.taxability_rules_date_id
237 AND p_eff_date BETWEEN trd.valid_date_from
238 AND trd.valid_date_to;
239
240 l_proc_name VARCHAR2(60) := 'pay_ca_roe_ei_pkg.taxability_rule_exists';
241
242 l_dummy NUMBER;
243
244 BEGIN
245
246 IF p_classification_name in
247 ('Earnings','Balance Initialization') THEN
248
249 RETURN 'TRUE';
250
251 ELSE
252
253 IF p_tax_type = 'EIM' THEN
254 OPEN csr_get_taxability_rules(p_classification_name,
255 p_classification_id,
256 p_tax_category,
257 p_effective_date);
258 FETCH csr_get_taxability_rules INTO l_dummy;
259 IF csr_get_taxability_rules%NOTFOUND THEN
260 RETURN 'FALSE';
261 ELSE
262 RETURN 'TRUE';
263 END IF;
264 ELSE
265 RETURN 'FALSE';
266 END IF;
267
268 END IF;
269
270 END taxability_rule_exists;
271
272 -------------------------------------------------------------------------------
273 -- Name: get_pd_num
274 --
275 -- Parameters: p_current_date
276 -- p_end_date
277 --
278 -- Return: NUMBER - period number
279 --
280 -- Description: This function looks up the number of pay periods between two
281 -- given dates.
282 -------------------------------------------------------------------------------
283 FUNCTION get_pd_num
284 (p_current_date IN DATE,
285 p_end_date IN DATE)
286 RETURN NUMBER IS
287
288 BEGIN
289
290 RETURN l_period_number(p_end_date - p_current_date);
291
292 END get_pd_num;
293
294 -------------------------------------------------------------------------------
295 -- Name: get_ei_amount_totals
296 --
297 -- Parameters: p_total_type 'EI Hours' or 'EI Earnings'
298 -- p_assignment_id
299 -- p_gre
300 -- p_payroll_id
301 -- p_end_date - date of ROE
302 -- p_period_type (output)
303 -- p_total_insurable (output) - either Hours or Earnings total
304 -- p_no_of_periods (output) - only used for Box15C
305 -- p_periods_totals (output) - only used for Earnings
306 -- p_term_or_abs_flag - only used for Date Paid Amount
307 --
308 -- Return: VARCHAR2 - 'BOX15B' or 'BOX15C'
309 --
310 -- Description: This is an overloaded version of get_ei_amount_totals without
311 -- the p_start_date parameter date. If the start date is not
312 -- entered we set it to NULL.
313 -------------------------------------------------------------------------------
314 FUNCTION get_ei_amount_totals
315 (p_total_type IN VARCHAR2,
316 p_assignment_id IN NUMBER,
317 p_gre IN NUMBER,
318 p_payroll_id IN NUMBER,
319 p_end_date IN DATE,
320 p_period_type OUT NOCOPY VARCHAR2,
321 p_total_insurable OUT NOCOPY NUMBER,
322 p_no_of_periods OUT NOCOPY NUMBER,
323 p_period_total OUT NOCOPY t_large_number_table,
324 p_term_or_abs_flag IN VARCHAR2)
325 RETURN VARCHAR2 IS
326 l_return VARCHAR2(10);
327 BEGIN
328 l_return := get_ei_amount_totals
329 (p_total_type => p_total_type,
330 p_assignment_id => p_assignment_id,
331 p_gre => p_gre,
332 p_payroll_id => p_payroll_id,
333 p_start_date => NULL,
334 p_end_date => p_end_date,
335 p_period_type => p_period_type,
336 p_total_insurable => p_total_insurable,
337 p_no_of_periods => p_no_of_periods,
338 p_period_total => p_period_total,
339 p_term_or_abs_flag => p_term_or_abs_flag);
340 RETURN l_return;
341 END;
342
343 -------------------------------------------------------------------------------
344 -- Name: get_ei_amount_totals
345 --
346 -- Parameters: p_total_type 'EI Hours' or 'EI Earnings'
347 -- p_assignment_id
348 -- p_gre
349 -- p_payroll_id
350 -- p_start_date - non mandatory (date + 1 of last ROE)
351 -- p_end_date - date of ROE
352 -- p_period_type (output) - period type
353 -- p_total_insurable (output) - either Hours or Earnings total
354 -- p_no_of_periods (output) - only used for Box15C
355 -- p_periods_totals (output) - only used for Earnings
356 -- p_term_or_abs_flag - only used for Date Paid Amount
357 --
358 -- Return: VARCHAR2 - 'BOX15B' or 'BOX15C'
359 --
360 -- Description: This function is the main calling routine of this package.
361 -- It is used by the Canadian Record of Employment (ROE) Report
362 -- to calculate the values of boxes 15A, 15B and 15C on that
363 -- report.
364 -- 15A - This calculates the Insurable Hours for a time period
365 -- approximately equal to a year. The details of the
366 -- exact time period are different for each period type.
367 -- 15C - This calculate the Insurable Earnings for each pay
368 -- period for a tme period approximately equal to 6 months.
369 -- The details of the exact time period are different for
370 -- ech period type.
371 -- 15B - If any of the results from 15C are zero then values for
372 -- all periods (15C) must be returned, otherwise just a
373 -- total is required (15B).
374 -- Note for all of the above balance calculations the
375 -- element_information3 field ('ROE Allocation By') is used to
376 -- determine whether we use the 'Date Earned' or 'Date Paid' as
377 -- the balance's effective date for each element of the balance.
378 -------------------------------------------------------------------------------
379 FUNCTION get_ei_amount_totals
380 (p_total_type IN VARCHAR2,
381 p_assignment_id IN NUMBER,
382 p_gre IN NUMBER,
383 p_payroll_id IN NUMBER,
384 p_start_date IN DATE,
385 p_end_date IN DATE,
386 p_period_type OUT NOCOPY VARCHAR2,
387 p_total_insurable OUT NOCOPY NUMBER,
388 p_no_of_periods OUT NOCOPY NUMBER,
389 p_period_total OUT NOCOPY t_large_number_table,
390 p_term_or_abs_flag IN VARCHAR2)
391 RETURN VARCHAR2 IS
392
393 CURSOR csr_get_period_type (p_payroll_id NUMBER,
394 p_date DATE) IS
395 SELECT tpd.period_type,
396 tpd.start_date,
397 tpd.end_date
398 FROM per_time_periods tpd
399 WHERE tpd.payroll_id = p_payroll_id
400 AND p_date BETWEEN tpd.start_date
401 AND tpd.end_date;
402
403 CURSOR csr_dp_hours_total_ftr_exists (p_asg_id NUMBER,
404 p_gre NUMBER,
405 p_start_date DATE,
406 p_end_date DATE) IS
407 SELECT /*+ RULE */
408 SUM(NVL(rrv.result_value, 0) * blf.scale) total_dp_hours
409 FROM pay_ca_emp_fed_tax_info_f fti,
410 pay_assignment_actions asa,
411 pay_payroll_actions pya,
412 pay_run_results rrs,
413 pay_run_result_values rrv,
414 pay_element_types_f ele,
415 pay_input_values_f ipv,
416 pay_balance_feeds_f blf,
417 pay_balance_types blt
418 WHERE asa.assignment_id = p_asg_id
419 AND asa.tax_unit_id = p_gre
420 AND pya.payroll_id = p_payroll_id
421 AND pya.payroll_action_id = asa.payroll_action_id
422 AND pya.effective_date BETWEEN p_start_date
423 AND p_end_date
424 AND fti.assignment_id = p_asg_id
425 AND NVL(fti.ei_exempt_flag,'N') = 'N'
426 AND pya.effective_date BETWEEN fti.effective_start_date
427 AND fti.effective_end_date
428 AND fti.assignment_id = asa.assignment_id
429 AND rrs.assignment_action_id = asa.assignment_action_id
430 AND ele.element_type_id = rrs.element_type_id
431 AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
432 (ele.element_type_id,
433 'DP',
434 ele.element_information3) = 'TRUE'
435 AND pya.effective_date BETWEEN ele.effective_start_date
436 AND ele.effective_end_date
437 AND rrv.run_result_id = rrs.run_result_id
438 AND ipv.input_value_id = rrv.input_value_id
439 AND pya.effective_date BETWEEN ipv.effective_start_date
440 AND ipv.effective_end_date
441 AND blf.input_value_id = ipv.input_value_id
442 AND pya.effective_date BETWEEN blf.effective_start_date
443 AND blf.effective_end_date
444 AND blf.balance_type_id = blt.balance_type_id
445 AND blt.balance_name = 'EI Hours'
446 AND blt.legislation_code = 'CA';
447
448 CURSOR csr_dp_hours_total_ftr_nexists (p_asg_id NUMBER,
449 p_gre NUMBER,
450 p_start_date DATE,
451 p_end_date DATE) IS
452 SELECT /*+ RULE */
453 SUM(NVL(rrv.result_value, 0) * blf.scale) total_dp_hours
454 FROM pay_assignment_actions asa,
455 pay_payroll_actions pya,
456 pay_run_results rrs,
457 pay_run_result_values rrv,
458 pay_element_types_f ele,
459 pay_input_values_f ipv,
460 pay_balance_feeds_f blf,
461 pay_balance_types blt
462 WHERE asa.assignment_id = p_asg_id
463 AND asa.tax_unit_id = p_gre
464 AND pya.payroll_id = p_payroll_id
465 AND pya.payroll_action_id = asa.payroll_action_id
466 AND pya.effective_date BETWEEN p_start_date
467 AND p_end_date
468 AND rrs.assignment_action_id = asa.assignment_action_id
469 AND ele.element_type_id = rrs.element_type_id
470 AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
471 (ele.element_type_id,
472 'DP',
473 ele.element_information3) = 'TRUE'
474 AND pya.effective_date BETWEEN ele.effective_start_date
475 AND ele.effective_end_date
476 AND rrv.run_result_id = rrs.run_result_id
477 AND ipv.input_value_id = rrv.input_value_id
478 AND pya.effective_date BETWEEN ipv.effective_start_date
479 AND ipv.effective_end_date
480 AND blf.input_value_id = ipv.input_value_id
481 AND pya.effective_date BETWEEN blf.effective_start_date
482 AND blf.effective_end_date
483 AND blf.balance_type_id = blt.balance_type_id
484 AND blt.balance_name = 'EI Hours'
485 AND blt.legislation_code = 'CA';
486
487 CURSOR csr_de_hours_total_ftr_exists (p_asg_id NUMBER,
488 p_gre NUMBER,
489 p_start_date DATE,
490 p_end_date DATE) IS
491 SELECT /*+ RULE */
492 SUM(NVL(rrv.result_value, 0) * blf.scale) total_de_hours
493 FROM pay_ca_emp_fed_tax_info_f fti,
494 pay_assignment_actions asa,
495 pay_payroll_actions pya,
496 pay_run_results rrs,
497 pay_run_result_values rrv,
498 pay_element_types_f ele,
499 pay_input_values_f ipv,
500 pay_balance_feeds_f blf,
501 pay_balance_types blt
502 WHERE asa.assignment_id = p_asg_id
503 AND asa.tax_unit_id = p_gre
504 AND pya.payroll_id = p_payroll_id
505 AND pya.payroll_action_id = asa.payroll_action_id
506 AND pya.date_earned BETWEEN p_start_date
507 AND p_end_date
508 AND fti.assignment_id = p_asg_id
509 AND fti.assignment_id = asa.assignment_id
510 AND NVL(fti.ei_exempt_flag,'N') = 'N'
511 AND pya.date_earned BETWEEN fti.effective_start_date
512 AND fti.effective_end_date
513 AND rrs.assignment_action_id = asa.assignment_action_id
514 AND ele.element_type_id = rrs.element_type_id
515 AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
516 (ele.element_type_id,
517 'DE',
518 ele.element_information3) = 'TRUE'
519 AND pya.date_earned BETWEEN ele.effective_start_date
520 AND ele.effective_end_date
521 AND rrv.run_result_id = rrs.run_result_id
522 AND ipv.input_value_id = rrv.input_value_id
523 AND pya.date_earned BETWEEN ipv.effective_start_date
524 AND ipv.effective_end_date
525 AND blf.input_value_id = ipv.input_value_id
526 AND pya.date_earned BETWEEN blf.effective_start_date
527 AND blf.effective_end_date
528 AND blf.balance_type_id = blt.balance_type_id
529 AND blt.balance_name = 'EI Hours'
530 AND blt.legislation_code = 'CA';
531
532 CURSOR csr_de_hours_total_ftr_nexists (p_asg_id NUMBER,
533 p_gre NUMBER,
534 p_start_date DATE,
535 p_end_date DATE) IS
536 SELECT /*+ RULE */
537 SUM(NVL(rrv.result_value, 0) * blf.scale) total_de_hours
538 FROM pay_assignment_actions asa,
539 pay_payroll_actions pya,
540 pay_run_results rrs,
541 pay_run_result_values rrv,
542 pay_element_types_f ele,
543 pay_input_values_f ipv,
544 pay_balance_feeds_f blf,
545 pay_balance_types blt
546 WHERE asa.assignment_id = p_asg_id
547 AND asa.tax_unit_id = p_gre
548 AND pya.payroll_id = p_payroll_id
549 AND pya.payroll_action_id = asa.payroll_action_id
550 AND pya.date_earned BETWEEN p_start_date
551 AND p_end_date
552 AND rrs.assignment_action_id = asa.assignment_action_id
553 AND ele.element_type_id = rrs.element_type_id
554 AND pay_ca_roe_ei_pkg.date_paid_or_date_earned
555 (ele.element_type_id,
556 'DE',
557 ele.element_information3) = 'TRUE'
558 AND pya.date_earned BETWEEN ele.effective_start_date
559 AND ele.effective_end_date
560 AND rrv.run_result_id = rrs.run_result_id
561 AND ipv.input_value_id = rrv.input_value_id
562 AND pya.date_earned BETWEEN ipv.effective_start_date
563 AND ipv.effective_end_date
564 AND blf.input_value_id = ipv.input_value_id
565 AND pya.date_earned BETWEEN blf.effective_start_date
566 AND blf.effective_end_date
567 AND blf.balance_type_id = blt.balance_type_id
568 AND blt.balance_name = 'EI Hours'
569 AND blt.legislation_code = 'CA';
570 /* Modifed the cursor for Bug 4510534 */
571 CURSOR csr_get_dp_total(p_asg_id NUMBER,
572 p_gre NUMBER,
573 p_start_date DATE,
574 p_end_date DATE) IS
575 SELECT /*+ RULE */
576 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
577 1, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
578 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
579 2, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
580 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
581 3, NVL(rrv.result_value, 0)*blf.scale,0)),0),
582 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
583 4, NVL(rrv.result_value, 0)*blf.scale,0)),0),
584 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
585 5, NVL(rrv.result_value, 0)*blf.scale,0)),0),
586 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
587 6, NVL(rrv.result_value, 0)*blf.scale,0)),0),
588 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
589 7, NVL(rrv.result_value, 0)*blf.scale,0)),0),
590 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
591 8, NVL(rrv.result_value, 0)*blf.scale,0)),0),
592 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
593 9, NVL(rrv.result_value, 0)*blf.scale,0)),0),
594 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
595 10,NVL(rrv.result_value, 0)*blf.scale,0)),0),
596 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
597 11,NVL(rrv.result_value, 0)*blf.scale,0)),0),
598 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
599 12,NVL(rrv.result_value, 0)*blf.scale,0)),0),
600 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
601 13,NVL(rrv.result_value, 0)*blf.scale,0)),0),
602 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
603 14,NVL(rrv.result_value, 0)*blf.scale,0)),0),
604 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
605 15,NVL(rrv.result_value, 0)*blf.scale,0)),0),
606 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
607 16,NVL(rrv.result_value, 0)*blf.scale,0)),0),
608 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
609 17,NVL(rrv.result_value, 0)*blf.scale,0)),0),
610 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
611 18,NVL(rrv.result_value, 0)*blf.scale,0)),0),
612 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
613 19,NVL(rrv.result_value, 0)*blf.scale,0)),0),
614 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
615 20,NVL(rrv.result_value, 0)*blf.scale,0)),0),
616 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
617 21,NVL(rrv.result_value, 0)*blf.scale,0)),0),
618 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
619 22,NVL(rrv.result_value, 0)*blf.scale,0)),0),
620 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
621 23,NVL(rrv.result_value, 0)*blf.scale,0)),0),
622 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
623 24,NVL(rrv.result_value, 0)*blf.scale,0)),0),
624 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
625 25,NVL(rrv.result_value, 0)*blf.scale,0)),0),
626 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
627 26,NVL(rrv.result_value, 0)*blf.scale,0)),0),
628 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
629 27,NVL(rrv.result_value, 0)*blf.scale,0)),0),
630 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
631 28,NVL(rrv.result_value, 0)*blf.scale,0)),0),
632 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
633 29,NVL(rrv.result_value, 0)*blf.scale,0)),0),
634 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
635 30, NVL(rrv.result_value, 0)*blf.scale,0)),0),
636 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
637 31, NVL(rrv.result_value, 0)*blf.scale,0)),0),
638 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
639 32, NVL(rrv.result_value, 0)*blf.scale,0)),0),
640 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
641 33, NVL(rrv.result_value, 0)*blf.scale,0)),0),
642 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
643 34, NVL(rrv.result_value, 0)*blf.scale,0)),0),
644 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
645 35, NVL(rrv.result_value, 0)*blf.scale,0)),0),
646 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
647 36, NVL(rrv.result_value, 0)*blf.scale,0)),0),
648 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
649 37,NVL(rrv.result_value, 0)*blf.scale,0)),0),
650 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
651 38,NVL(rrv.result_value, 0)*blf.scale,0)),0),
652 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
653 39,NVL(rrv.result_value, 0)*blf.scale,0)),0),
654 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
655 40,NVL(rrv.result_value, 0)*blf.scale,0)),0),
656 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
657 41,NVL(rrv.result_value, 0)*blf.scale,0)),0),
658 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
659 42,NVL(rrv.result_value, 0)*blf.scale,0)),0),
660 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
661 43,NVL(rrv.result_value, 0)*blf.scale,0)),0),
662 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
663 44,NVL(rrv.result_value, 0)*blf.scale,0)),0),
664 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
665 45,NVL(rrv.result_value, 0)*blf.scale,0)),0),
666 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
667 46,NVL(rrv.result_value, 0)*blf.scale,0)),0),
668 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
669 47,NVL(rrv.result_value, 0)*blf.scale,0)),0),
670 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
671 48,NVL(rrv.result_value, 0)*blf.scale,0)),0),
672 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
673 49,NVL(rrv.result_value, 0)*blf.scale,0)),0),
674 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
675 50,NVL(rrv.result_value, 0)*blf.scale,0)),0),
676 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
677 51,NVL(rrv.result_value, 0)*blf.scale,0)),0),
678 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
679 52,NVL(rrv.result_value, 0)*blf.scale,0)),0),
680 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
681 53,NVL(rrv.result_value, 0)*blf.scale,0)),0),
682 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
683 54,NVL(rrv.result_value, 0)*blf.scale,0)),0),
684 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
685 55,NVL(rrv.result_value, 0)*blf.scale,0)),0)
686 FROM pay_assignment_actions asa,
687 pay_payroll_actions pya,
688 pay_run_results rrs,
689 pay_run_result_values rrv,
690 pay_element_types_f ele,
691 pay_element_classifications elc,
692 pay_input_values_f ipv,
693 pay_balance_feeds_f blf,
694 pay_balance_types blt
695 WHERE asa.assignment_id = p_asg_id
696 AND asa.tax_unit_id = p_gre
697 AND pya.payroll_id = p_payroll_id
698 AND pya.payroll_action_id = asa.payroll_action_id
699 AND pya.effective_date BETWEEN p_start_date
700 AND p_end_date
701 AND rrs.assignment_action_id = asa.assignment_action_id
702 AND ele.element_type_id = rrs.element_type_id
703 AND NVL(ele.element_information3,'DP') = 'DP'
704 AND pya.effective_date BETWEEN ele.effective_start_date
705 AND ele.effective_end_date
706 AND elc.classification_id = ele.classification_id
707 AND elc.classification_name IN ('Earnings',
708 'Supplemental Earnings',
709 'Taxable Benefits',
710 'Balance Initialization')
711 AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
712 elc.classification_id,
713 ele.element_information1,
714 pya.effective_date,
715 blt.tax_type) = 'TRUE'
716 AND rrv.run_result_id = rrs.run_result_id
717 AND ipv.input_value_id = rrv.input_value_id
718 AND pya.effective_date BETWEEN ipv.effective_start_date
719 AND ipv.effective_end_date
720 AND blf.input_value_id = ipv.input_value_id
721 AND pya.effective_date BETWEEN blf.effective_start_date
722 AND blf.effective_end_date
723 AND blf.balance_type_id = blt.balance_type_id
724 AND blt.balance_name IN ('Regular Earnings',
725 'Supplemental Earnings for EI',
726 'Taxable Benefits for EI')
727 AND blt.legislation_code = 'CA'
728 AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
729 SYSDATE,SYSDATE, pya.effective_date, 'EIEXEMPT'),'N') = 'N';
730
731 CURSOR csr_get_dp_total1(p_asg_id NUMBER,
732 p_gre NUMBER,
733 p_start_date DATE,
734 p_end_date DATE) IS
735 SELECT /*+ RULE */
736 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
737 1, NVL(rrv.result_value, 0)*blf.scale,0)),0),
738 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
739 2, NVL(rrv.result_value, 0)*blf.scale,0)),0),
740 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
741 3, NVL(rrv.result_value, 0)*blf.scale,0)),0),
742 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
743 4, NVL(rrv.result_value, 0)*blf.scale,0)),0),
744 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
745 5, NVL(rrv.result_value, 0)*blf.scale,0)),0),
746 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
747 6, NVL(rrv.result_value, 0)*blf.scale,0)),0),
748 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
749 7, NVL(rrv.result_value, 0)*blf.scale,0)),0),
750 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
751 8, NVL(rrv.result_value, 0)*blf.scale,0)),0),
752 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
753 9, NVL(rrv.result_value, 0)*blf.scale,0)),0),
754 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
755 10,NVL(rrv.result_value, 0)*blf.scale,0)),0),
756 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
757 11,NVL(rrv.result_value, 0)*blf.scale,0)),0),
758 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
759 12,NVL(rrv.result_value, 0)*blf.scale,0)),0),
760 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
761 13,NVL(rrv.result_value, 0)*blf.scale,0)),0),
762 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
763 14,NVL(rrv.result_value, 0)*blf.scale,0)),0),
764 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
765 15,NVL(rrv.result_value, 0)*blf.scale,0)),0),
766 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
767 16,NVL(rrv.result_value, 0)*blf.scale,0)),0),
768 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
769 17,NVL(rrv.result_value, 0)*blf.scale,0)),0),
770 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
771 18,NVL(rrv.result_value, 0)*blf.scale,0)),0),
772 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
773 19,NVL(rrv.result_value, 0)*blf.scale,0)),0),
774 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
775 20,NVL(rrv.result_value, 0)*blf.scale,0)),0),
776 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
777 21,NVL(rrv.result_value, 0)*blf.scale,0)),0),
778 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
779 22,NVL(rrv.result_value, 0)*blf.scale,0)),0),
780 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
781 23,NVL(rrv.result_value, 0)*blf.scale,0)),0),
782 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
783 24,NVL(rrv.result_value, 0)*blf.scale,0)),0),
784 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
785 25,NVL(rrv.result_value, 0)*blf.scale,0)),0),
786 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
787 26,NVL(rrv.result_value, 0)*blf.scale,0)),0),
788 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
789 27,NVL(rrv.result_value, 0)*blf.scale,0)),0),
790 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
791 28, NVL(rrv.result_value, 0)*blf.scale,0)),0),
792 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
793 29, NVL(rrv.result_value, 0)*blf.scale,0)),0),
794 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
795 30, NVL(rrv.result_value, 0)*blf.scale,0)),0),
796 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
797 31, NVL(rrv.result_value, 0)*blf.scale,0)),0),
798 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
799 32, NVL(rrv.result_value, 0)*blf.scale,0)),0),
800 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
801 33, NVL(rrv.result_value, 0)*blf.scale,0)),0),
802 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
803 34, NVL(rrv.result_value, 0)*blf.scale,0)),0),
804 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
805 35, NVL(rrv.result_value, 0)*blf.scale,0)),0),
806 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
807 36,NVL(rrv.result_value, 0)*blf.scale,0)),0),
808 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
809 37,NVL(rrv.result_value, 0)*blf.scale,0)),0),
810 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
811 38,NVL(rrv.result_value, 0)*blf.scale,0)),0),
812 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
813 39,NVL(rrv.result_value, 0)*blf.scale,0)),0),
814 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
815 40,NVL(rrv.result_value, 0)*blf.scale,0)),0),
816 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
817 41,NVL(rrv.result_value, 0)*blf.scale,0)),0),
818 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
819 42,NVL(rrv.result_value, 0)*blf.scale,0)),0),
820 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
821 43,NVL(rrv.result_value, 0)*blf.scale,0)),0),
822 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
823 44,NVL(rrv.result_value, 0)*blf.scale,0)),0),
824 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
825 45,NVL(rrv.result_value, 0)*blf.scale,0)),0),
826 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
827 46,NVL(rrv.result_value, 0)*blf.scale,0)),0),
828 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
829 47,NVL(rrv.result_value, 0)*blf.scale,0)),0),
830 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
831 48,NVL(rrv.result_value, 0)*blf.scale,0)),0),
832 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
833 49,NVL(rrv.result_value, 0)*blf.scale,0)),0),
834 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
835 50,NVL(rrv.result_value, 0)*blf.scale,0)),0),
836 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
837 51,NVL(rrv.result_value, 0)*blf.scale,0)),0),
838 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
839 52,NVL(rrv.result_value, 0)*blf.scale,0)),0),
840 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
841 53,NVL(rrv.result_value, 0)*blf.scale,0)),0)
842 FROM pay_assignment_actions asa,
843 pay_payroll_actions pya,
844 pay_run_results rrs,
845 pay_run_result_values rrv,
846 pay_element_types_f ele,
847 pay_element_classifications elc,
848 pay_input_values_f ipv,
849 pay_balance_feeds_f blf,
850 pay_balance_types blt
851 WHERE asa.assignment_id = p_asg_id
852 AND asa.tax_unit_id = p_gre
853 AND pya.payroll_id = p_payroll_id
854 AND pya.payroll_action_id = asa.payroll_action_id
855 AND pya.effective_date BETWEEN p_start_date
856 AND p_end_date
857 AND rrs.assignment_action_id = asa.assignment_action_id
858 AND ele.element_type_id = rrs.element_type_id
859 AND NVL(ele.element_information3,'DP') = 'DP'
860 AND pya.effective_date BETWEEN ele.effective_start_date
861 AND ele.effective_end_date
862 AND elc.classification_id = ele.classification_id
863 AND elc.classification_name IN ('Earnings',
864 'Supplemental Earnings',
865 'Taxable Benefits',
866 'Balance Initialization')
867 AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
868 elc.classification_id,
869 ele.element_information1,
870 pya.effective_date,
871 blt.tax_type) = 'TRUE'
872 AND rrv.run_result_id = rrs.run_result_id
873 AND ipv.input_value_id = rrv.input_value_id
874 AND pya.effective_date BETWEEN ipv.effective_start_date
875 AND ipv.effective_end_date
876 AND blf.input_value_id = ipv.input_value_id
877 AND pya.effective_date BETWEEN blf.effective_start_date
878 AND blf.effective_end_date
879 AND blf.balance_type_id = blt.balance_type_id
880 AND blt.balance_name IN ('Regular Earnings',
881 'Supplemental Earnings for EI',
882 'Taxable Benefits for EI')
883 AND blt.legislation_code = 'CA'
884 AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
885 SYSDATE,SYSDATE, pya.effective_date, 'EIEXEMPT'),'N') = 'N';
886
887 CURSOR csr_get_de_total(p_asg_id NUMBER,
888 p_gre NUMBER,
889 p_start_date DATE,
890 p_end_date DATE) IS
891 SELECT /*+ RULE */
892 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
893 1, NVL(rrv.result_value, 0)*blf.scale,0)),0),
894 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
895 2, NVL(rrv.result_value, 0)*blf.scale,0)),0),
896 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
897 3, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
898 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
899 4, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
900 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
901 5, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
902 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
903 6, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
904 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
905 7, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
906 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
907 8, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
908 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
909 9, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
910 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
911 10, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
912 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
913 11, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
914 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
915 12, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
916 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
917 13, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
918 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
919 14, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
920 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
921 15, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
922 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
923 16, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
924 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
925 17, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
926 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
927 18, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
928 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
929 19, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
930 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
931 20, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
932 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
933 21, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
934 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
935 22, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
936 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
937 23, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
938 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
939 24, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
940 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
941 25, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
942 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
943 26, NVL(rrv.result_value, 0)*blf.scale,0)),0),
944 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
945 27, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
946 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
947 28, NVL(rrv.result_value, 0)*blf.scale,0)),0),
948 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
949 29, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
950 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
951 30, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
952 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
953 31, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
954 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
955 32, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
956 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
957 33, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
958 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
959 34, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
960 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
961 35, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
962 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
963 36, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
964 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
965 37, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
966 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
967 38, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
968 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
969 39, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
970 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
971 40, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
972 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
973 41, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
974 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
975 42, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
976 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
977 43, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
978 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
979 44, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
980 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
981 45, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
982 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
983 46, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
984 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
985 47, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
986 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
987 48, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
988 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
989 49, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
990 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
991 50, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
992 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
993 51, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
994 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
995 52, NVL(rrv.result_value, 0)*blf.scale,0)),0),
996 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
997 53, NVL(rrv.result_value, 0)*blf.scale,0)),0)
998 FROM pay_assignment_actions asa,
999 pay_payroll_actions pya,
1000 pay_run_results rrs,
1001 pay_run_result_values rrv,
1002 pay_element_types_f ele,
1003 pay_element_classifications elc,
1004 pay_input_values_f ipv,
1005 pay_balance_feeds_f blf,
1006 pay_balance_types blt
1007 WHERE asa.assignment_id = p_asg_id
1008 AND asa.tax_unit_id = p_gre
1009 AND pya.payroll_id = p_payroll_id
1010 AND pya.payroll_action_id = asa.payroll_action_id
1011 AND pya.date_earned BETWEEN p_start_date
1012 AND p_end_date
1013 AND rrs.assignment_action_id = asa.assignment_action_id
1014 AND ele.element_type_id = rrs.element_type_id
1015 AND NVL(ele.element_information3,'DP') = 'DE'
1016 AND pya.date_earned BETWEEN ele.effective_start_date
1017 AND ele.effective_end_date
1018 AND elc.classification_id = ele.classification_id
1019 AND elc.classification_name IN ('Earnings',
1020 'Supplemental Earnings',
1021 'Taxable Benefits')
1022 AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
1023 elc.classification_id,
1024 ele.element_information1,
1025 pya.date_earned,
1026 blt.tax_type) = 'TRUE'
1027 AND rrv.run_result_id = rrs.run_result_id
1028 AND ipv.input_value_id = rrv.input_value_id
1029 AND pya.date_earned BETWEEN ipv.effective_start_date
1030 AND ipv.effective_end_date
1031 AND blf.input_value_id = ipv.input_value_id
1032 AND pya.date_earned BETWEEN blf.effective_start_date
1033 AND blf.effective_end_date
1034 AND blf.balance_type_id = blt.balance_type_id
1035 AND blt.balance_name IN ('Regular Earnings',
1036 'Supplemental Earnings for EI',
1037 'Taxable Benefits for EI')
1038 AND blt.legislation_code = 'CA'
1039 AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
1040 SYSDATE, SYSDATE, pya.date_earned, 'EIEXEMPT'),'N') = 'N';
1041
1042 l_proc_name VARCHAR2(60) := 'pay_ca_roe_ei_pkg.get_ei_amount_totals';
1043
1044 l_start_date DATE;
1045 l_start_period DATE;
1046 l_last_period_start_date DATE;
1047 l_last_period_end_date DATE;
1048 l_value NUMBER;
1049 l_prev_element_entry_id NUMBER;
1050 l_period_count NUMBER;
1051 l_period_count1 NUMBER;
1052 l_box15c_flag BOOLEAN := FALSE;
1053
1054 l_dp_hours_total NUMBER;
1055 l_de_hours_total NUMBER;
1056 l_hours_total NUMBER;
1057
1058 l_de_total t_large_number_table;
1059 l_dp_total t_large_number_table;
1060
1061
1062 CURSOR cur_count_pay_periods(p_start_date1 DATE) IS
1063 SELECT COUNT(*)
1064 FROM per_time_periods
1065 WHERE payroll_id = p_payroll_id
1066 AND end_date >= p_start_date1
1067 AND start_date <= p_end_date;
1068
1069 l_no_of_pay_periods NUMBER;
1070 l_start_date1 DATE;
1071
1072 cursor cur_date_of_hire is
1073 select max(service.date_start) hire_date
1074 from per_periods_of_service service,
1075 per_assignments_f asg
1076 where asg.assignment_id = p_assignment_id
1077 and p_end_date BETWEEN
1078 asg.effective_start_date
1079 AND asg.effective_end_date
1080 and asg.person_id = service.person_id(+)
1081 and service.date_start <= p_end_date;
1082
1083 l_hire_date DATE;
1084
1085 cursor cur_next_prd_start_date is
1086 select
1087 ptp.end_date + 1
1088 from
1089 per_time_periods ptp
1090 where
1091 ptp.payroll_id = p_payroll_id and
1092 p_start_date between
1093 ptp.start_date and ptp.end_date;
1094
1095 cursor csr_start_date (p_payroll_id number,
1096 p_start_date date) is
1097 select start_date
1098 from per_time_periods
1099 where payroll_id = p_payroll_id
1100 and start_date = p_start_date;
1101
1102 l_period_start_date date;
1103 l_period_start_date_exists varchar2(1);
1104
1105 cursor cur_pay_period_dates(p_date date) is
1106 select
1107 ptp2.start_date,
1108 ptp2.end_date
1109 from
1110 per_time_periods ptp,
1111 per_time_periods ptp1,
1112 per_time_periods ptp2
1113 where
1114 ptp.payroll_id = p_payroll_id and
1115 p_date between
1116 ptp.start_date and
1117 ptp.end_date and
1118 ptp.payroll_id = ptp1.payroll_id and
1119 ptp.end_date + 1 between
1120 ptp1.start_date and
1121 ptp1.end_date and
1122 ptp1.payroll_id = ptp2.payroll_id and
1123 ptp1.end_date + 1 between
1124 ptp2.start_date and
1125 ptp2.end_date;
1126
1127 l_prd_st_date_after_final date;
1128 l_prd_end_date_after_final date;
1129
1130 CURSOR cur_retro_run(p_start_date date,
1131 p_end_date date) is
1132 select
1133 ppa.payroll_action_id,
1134 ppa.effective_date,
1135 ppa.start_date
1136 from
1137 pay_payroll_actions ppa,
1138 pay_assignment_actions paa
1139 where
1140 paa.assignment_id = p_assignment_id and
1141 paa.tax_unit_id = p_gre and
1142 ppa.payroll_id = p_payroll_id and
1143 paa.payroll_action_id = ppa.payroll_action_id and
1144 ppa.action_type = 'L' and
1145 ppa.action_status = 'C' and
1146 ppa.start_date between p_start_date and
1147 p_end_date;
1148
1149 cursor cur_curr_pay_period_dates(p_date date) is
1150 select
1151 ptp.start_date,
1152 ptp.end_date
1153 from
1154 per_time_periods ptp
1155 where
1156 ptp.payroll_id = p_payroll_id and
1157 p_date between
1158 ptp.start_date and
1159 ptp.end_date;
1160
1161 CURSOR cur_payroll_exists(p_pay_period_start_date date,
1162 p_pay_period_end_date date) is
1163 select
1164 'X'
1165 from
1166 pay_payroll_actions ppa,
1167 pay_assignment_actions paa
1168 where
1169 ppa.action_status = 'C' and
1170 ppa.action_type in ('Q','R') and
1171 ppa.date_earned between p_pay_period_start_date and
1172 p_pay_period_end_date and
1173 ppa.payroll_action_id = paa.payroll_action_id and
1174 paa.assignment_id = p_assignment_id;
1175
1176
1177 CURSOR cur_ele_entries(p_start_date date,
1178 p_end_date date) IS
1179 select
1180 pet.element_type_id,
1181 pee.element_entry_id,
1182 pee.creator_type,
1183 pee.source_id,
1184 pee.source_asg_action_id,
1185 nvl(pet.element_information3,'DE') element_information3,
1186 peev.screen_entry_value,
1187 pec.classification_name,
1188 pec.classification_id,
1189 pet.element_information1
1190 from
1191 pay_element_entries_f pee,
1192 pay_element_links_f pel,
1193 pay_element_types_f pet,
1194 pay_element_entry_values_f peev,
1195 pay_input_values_f piv,
1196 pay_retro_component_usages prcu,
1197 pay_element_span_usages pesu,
1198 pay_retro_components prc,
1199 pay_time_spans pts,
1200 pay_element_classifications pec
1201 where
1202 pee.assignment_id = p_assignment_id and
1203 pee.creator_type in ('EE','RR') and
1204 pee.effective_start_date <= p_end_date and
1205 pee.effective_end_date >= p_start_date and
1206 pee.element_link_id = pel.element_link_id and
1207 pel.effective_start_date <= p_end_date and
1208 pel.effective_end_date >= p_start_date and
1209 pel.element_type_id = pet.element_type_id and
1210 pet.effective_start_date <= p_end_date and
1211 pet.effective_end_date >= p_start_date and
1212 pet.element_type_id = pesu.retro_element_type_id and
1213 pesu.time_span_id = pts.time_span_id and
1214 pesu.retro_component_usage_id = prcu.retro_component_usage_id and
1215 prcu.retro_component_id = prc.retro_component_id and
1216 pts.creator_id = prc.retro_component_id and
1217 prc.legislation_code = 'CA' and
1218 prc.short_name = 'Retropay' and
1219 pee.element_entry_id = peev.element_entry_id and
1220 peev.effective_start_date <= p_end_date and
1221 peev.effective_end_date >= p_start_date and
1222 peev.input_value_id = piv.input_value_id and
1223 piv.element_type_id = pet.element_type_id and
1224 piv.effective_start_date <= p_end_date and
1225 piv.effective_end_date >= p_start_date and
1226 piv.name = 'Pay Value' and
1227 pet.classification_id = pec.classification_id and
1228 pec.classification_name in ('Earnings',
1229 'Supplemental Earnings',
1230 'Taxable Benefits');
1231
1232 cursor cur_originating_period_rr_de(p_run_result_id number,
1233 p_start_date date,
1234 p_end_date date) is
1235 select ppa.date_earned
1236 from
1237 pay_run_results prr,
1238 pay_assignment_actions paa,
1239 pay_payroll_actions ppa
1240 where ppa.payroll_action_id = paa.payroll_action_id
1241 and ppa.date_earned between p_start_date
1242 and p_end_date
1243 and prr.assignment_action_id = paa.assignment_action_id
1244 and prr.run_result_id = p_run_result_id;
1245
1246 cursor cur_originating_period_asg_de(p_asg_action_id number,
1247 p_start_date date,
1248 p_end_date date) is
1249 select ppa.date_earned
1250 from
1251 pay_assignment_actions paa,
1252 pay_payroll_actions ppa
1253 where ppa.payroll_action_id = paa.payroll_action_id
1254 and ppa.date_earned between p_start_date
1255 and p_end_date
1256 and paa.assignment_action_id = p_asg_action_id;
1257
1258 l_pay_period_start_date date;
1259 l_pay_period_end_date date;
1260 l_pay_period_st_date date;
1261 l_pay_period_e_date date;
1262 period_from number;
1263 period_to number;
1264 dummy varchar2(1);
1265 l_rehire varchar2(1);
1266 l_next_prd_start_date date;
1267
1268 CURSOR cur_ftr(p_ftr_start_date DATE,
1269 p_ftr_end_date DATE) IS
1270 SELECT
1271 'X'
1272 FROM
1273 pay_ca_emp_fed_tax_info_f
1274 WHERE
1275 assignment_id = p_assignment_id AND
1276 effective_start_date <= p_ftr_end_date AND
1277 effective_end_date >= p_ftr_start_date;
1278
1279 l_ftr_exists BOOLEAN := FALSE;
1280 l_hour_start_date DATE;
1281
1282 BEGIN
1283
1284 hr_utility.set_location('Starting: ' || l_proc_name, 10);
1285 hr_utility.set_location('p_start_date: ' || to_char(p_start_date), 11);
1286 hr_utility.set_location('p_end_date: ' || p_end_date, 12);
1287
1288 /*
1289 * Initialise the output parameters
1290 */
1291 p_total_insurable := 0;
1292 FOR r_index IN 1..53 LOOP
1293 p_period_total(r_index) := 0;
1294 END LOOP;
1295
1296 OPEN csr_get_period_type(p_payroll_id,
1297 p_end_date);
1298 FETCH csr_get_period_type INTO p_period_type,
1299 l_last_period_start_date,
1300 l_last_period_end_date;
1301 CLOSE csr_get_period_type;
1302
1303
1304 hr_utility.set_location('l_last_period_start_date: ' ||
1305 l_last_period_start_date, 13);
1306 hr_utility.set_location(l_proc_name, 20);
1307 IF p_total_type = 'EI Hours' THEN
1308
1309 IF p_period_type = 'Week' OR
1310 p_period_type = 'Bi-Week' OR
1311 p_period_type = 'Lunar Month' THEN
1312 hr_utility.set_location(l_proc_name, 30);
1313 l_start_date := l_last_period_start_date - 364;
1314 ELSIF p_period_type = 'Semi-Month' OR
1315 p_period_type = 'Calendar Month' THEN
1316 hr_utility.set_location(l_proc_name, 40);
1317 l_start_date := ADD_MONTHS(l_last_period_start_date, -12);
1318 END IF;
1319
1320 hr_utility.set_location('EI Hours l_start_date: ' ||
1321 l_start_date, 20);
1322
1323 hr_utility.set_location('EI Hours p_start_date: ' ||
1324 p_start_date, 20);
1325
1326 l_period_start_date_exists := 'N';
1327
1328 IF p_start_date IS NOT NULL AND
1329 p_start_date > l_start_date THEN
1330
1331 open cur_next_prd_start_date;
1332 fetch cur_next_prd_start_date
1333 into l_next_prd_start_date;
1334 close cur_next_prd_start_date;
1335
1336 l_start_date := p_start_date;
1337
1338 hr_utility.trace('l_next_prd_start_date : '||to_char(l_next_prd_start_date));
1339 hr_utility.trace('l_start_date : '||to_char(l_start_date));
1340
1341 -- Check to see if l_start_date is the start date of a period
1342 -- If it is, then we set l_period_start_date_exists to Y
1343 -- this means that the previous ROE date was on the last day
1344 -- of a period
1345
1346 OPEN csr_start_date (p_payroll_id, l_start_date);
1347 FETCH csr_start_date INTO l_period_start_date;
1348 IF csr_start_date%NOTFOUND THEN
1349 CLOSE csr_start_date;
1350 l_period_start_date_exists := 'Y';
1351 --since we are not passing previous ROE Date but the first day worked
1352 --for current ROE, pay period should be included for calculations.
1353 ELSE
1354 l_period_start_date_exists := 'Y';
1355 CLOSE csr_start_date;
1356 END IF;
1357
1358 END IF;
1359
1360 l_rehire := populate_date_lookup_table(p_payroll_id,
1361 p_assignment_id,
1362 l_start_date,
1363 l_last_period_end_date,
1364 l_last_period_start_date);
1365
1366 -- If l_period_start_date_exists is Y then we want to
1367 -- retrieve the hours from the period l_start_date falls in
1368 -- not the next period
1369
1370 IF l_rehire = 'N' AND
1371 l_next_prd_start_date IS NOT NULL AND
1372 l_period_start_date_exists = 'N' THEN
1373
1374 -- If there are no rehires then get the hours
1375 -- starting from the next period
1376
1377 l_hour_start_date := l_next_prd_start_date;
1378
1379 ELSE
1380
1381 -- If rehires exist in the previous roe pay period
1382 -- then get hours starting from l_start_date
1383
1384 l_hour_start_date := l_start_date;
1385
1386 END IF;
1387
1388 hr_utility.trace('EI Hours: l_hour_start_date = '
1389 || to_char(l_hour_start_date));
1390 OPEN cur_ftr(l_hour_start_date,
1391 l_pay_period_end_date);
1392 FETCH cur_ftr
1393 INTO dummy;
1394
1395 IF cur_ftr%NOTFOUND THEN
1396 hr_utility.trace('EI Hours cur_ftr not Found !!!');
1397 l_ftr_exists := FALSE;
1398 ELSE
1399 hr_utility.trace('EI Hours cur_ftr Found !!!');
1400 l_ftr_exists := TRUE;
1401 END IF;
1402
1403 CLOSE cur_ftr;
1404
1405 IF l_ftr_exists THEN
1406
1407 hr_utility.trace('EI Hours l_ftr_exists !!!');
1408 OPEN csr_dp_hours_total_ftr_exists(p_assignment_id,
1409 p_gre,
1410 l_hour_start_date,
1411 l_last_period_end_date);
1412 FETCH csr_dp_hours_total_ftr_exists INTO l_dp_hours_total;
1413 CLOSE csr_dp_hours_total_ftr_exists;
1414
1415 OPEN csr_de_hours_total_ftr_exists(p_assignment_id,
1416 p_gre,
1417 l_hour_start_date,
1418 l_last_period_end_date);
1419 FETCH csr_de_hours_total_ftr_exists INTO l_de_hours_total;
1420 CLOSE csr_de_hours_total_ftr_exists;
1421
1422 ELSE
1423
1424 hr_utility.trace('EI Hours NOT l_ftr_exists !!!');
1425 OPEN csr_dp_hours_total_ftr_nexists(p_assignment_id,
1426 p_gre,
1427 l_hour_start_date,
1428 l_last_period_end_date);
1429 FETCH csr_dp_hours_total_ftr_nexists INTO l_dp_hours_total;
1430 CLOSE csr_dp_hours_total_ftr_nexists;
1431
1432 OPEN csr_de_hours_total_ftr_nexists(p_assignment_id,
1433 p_gre,
1434 l_hour_start_date,
1435 l_last_period_end_date);
1436 FETCH csr_de_hours_total_ftr_nexists INTO l_de_hours_total;
1437 CLOSE csr_de_hours_total_ftr_nexists;
1438
1439 END IF;
1440
1441 hr_utility.trace (' l_dp_hours_total = ' || to_char(l_dp_hours_total));
1442 hr_utility.trace (' l_de_hours_total = ' || to_char(l_de_hours_total));
1443
1444 p_total_insurable := NVL(l_dp_hours_total,0) +
1445 NVL(l_de_hours_total,0);
1446
1447 hr_utility.trace('Total Hours : '|| to_char(p_total_insurable));
1448
1449 RETURN 'BOX15A';
1450
1451 ELSIF p_total_type = 'EI Earnings' THEN
1452 /* Modified the period count for bug 4510534 */
1453 IF p_period_type = 'Week' THEN
1454 hr_utility.set_location(l_proc_name, 50);
1455 l_start_date := l_last_period_start_date - 365;
1456 l_period_count := 53;
1457 l_period_count1 := 27;
1458 ELSIF p_period_type = 'Bi-Week' THEN
1459 hr_utility.set_location(l_proc_name, 60);
1460 l_start_date := l_last_period_start_date - 365;
1461 l_period_count := 27;
1462 l_period_count1 := 14;
1463 ELSIF p_period_type = 'Semi-Month' THEN
1464 hr_utility.set_location(l_proc_name, 70);
1465 l_start_date := ADD_MONTHS(l_last_period_start_date, -12);
1466 l_period_count := 25;
1467 l_period_count1 := 13;
1468 ELSIF p_period_type = 'Calendar Month' THEN
1469 hr_utility.set_location(l_proc_name, 80);
1470 l_start_date := ADD_MONTHS(l_last_period_start_date, -12);
1471 l_period_count := 13;
1472 l_period_count1 := 7;
1473 ELSIF p_period_type = 'Lunar Month' THEN
1474 hr_utility.set_location(l_proc_name, 90);
1475 l_start_date := l_last_period_start_date - 336;
1476 l_period_count := 14;
1477 l_period_count1 := 7;
1478 END IF;
1479
1480 IF p_start_date IS NOT NULL AND
1481 p_start_date > l_start_date THEN
1482
1483 -- commented out because it can cause earnings not to be archived
1484
1485 /* open cur_next_prd_start_date;
1486 fetch cur_next_prd_start_date
1487 into l_start_date;
1488 close cur_next_prd_start_date; */
1489
1490 l_start_date := p_start_date;
1491
1492 END IF;
1493
1494 hr_utility.set_location('l_start_date: ' || l_start_date, 21);
1495
1496 hr_utility.trace('b4 populate_date_lookup_table');
1497 hr_utility.trace('l_start_date = ' || to_char(l_start_date));
1498 hr_utility.trace('p_end_date = ' || to_char(p_end_date));
1499 hr_utility.trace('l_last_period_start_date = ' || to_char(l_last_period_start_date));
1500
1501 l_rehire := populate_date_lookup_table(p_payroll_id,
1502 p_assignment_id,
1503 l_start_date,
1504 l_last_period_end_date,
1505 l_last_period_start_date);
1506 /* Modified the code to add aadditional amounts 28 to 53 for bug4510534 */
1507 OPEN csr_get_de_total(p_assignment_id,
1508 p_gre,
1509 l_start_date,
1510 l_last_period_end_date);
1511 FETCH csr_get_de_total INTO l_de_total(1),
1512 l_de_total(2),
1513 l_de_total(3),
1514 l_de_total(4),
1515 l_de_total(5),
1516 l_de_total(6),
1517 l_de_total(7),
1518 l_de_total(8),
1519 l_de_total(9),
1520 l_de_total(10),
1521 l_de_total(11),
1522 l_de_total(12),
1523 l_de_total(13),
1524 l_de_total(14),
1525 l_de_total(15),
1526 l_de_total(16),
1527 l_de_total(17),
1528 l_de_total(18),
1529 l_de_total(19),
1530 l_de_total(20),
1531 l_de_total(21),
1532 l_de_total(22),
1533 l_de_total(23),
1534 l_de_total(24),
1535 l_de_total(25),
1536 l_de_total(26),
1537 l_de_total(27),
1538 l_de_total(28),
1539 l_de_total(29),
1540 l_de_total(30),
1541 l_de_total(31),
1542 l_de_total(32),
1543 l_de_total(33),
1544 l_de_total(34),
1545 l_de_total(35),
1546 l_de_total(36),
1547 l_de_total(37),
1548 l_de_total(38),
1549 l_de_total(39),
1550 l_de_total(40),
1551 l_de_total(41),
1552 l_de_total(42),
1553 l_de_total(43),
1554 l_de_total(44),
1555 l_de_total(45),
1556 l_de_total(46),
1557 l_de_total(47),
1558 l_de_total(48),
1559 l_de_total(49),
1560 l_de_total(50),
1561 l_de_total(51),
1562 l_de_total(52),
1563 l_de_total(53) ;
1564 CLOSE csr_get_de_total;
1565
1566 hr_utility.trace('l_de_total(1) = ' || to_char(l_de_total(1)));
1567 hr_utility.trace('l_de_total(2) = ' || to_char(l_de_total(2)));
1568 hr_utility.trace('l_de_total(3) = ' || to_char(l_de_total(3)));
1569 hr_utility.trace('l_de_total(4) = ' || to_char(l_de_total(4)));
1570 hr_utility.trace('l_de_total(5) = ' || to_char(l_de_total(5)));
1571 hr_utility.trace('l_de_total(6) = ' || to_char(l_de_total(6)));
1572 hr_utility.trace('l_de_total(7) = ' || to_char(l_de_total(7)));
1573 hr_utility.trace('l_de_total(8) = ' || to_char(l_de_total(8)));
1574 hr_utility.trace('l_de_total(9) = ' || to_char(l_de_total(9)));
1575 hr_utility.trace('l_de_total(10) = ' || to_char(l_de_total(10)));
1576 hr_utility.trace('l_de_total(11) = ' || to_char(l_de_total(11)));
1577 hr_utility.trace('l_de_total(12) = ' || to_char(l_de_total(12)));
1578 hr_utility.trace('l_de_total(13) = ' || to_char(l_de_total(13)));
1579 hr_utility.trace('l_de_total(14) = ' || to_char(l_de_total(14)));
1580 hr_utility.trace('l_de_total(15) = ' || to_char(l_de_total(15)));
1581 hr_utility.trace('l_de_total(16) = ' || to_char(l_de_total(16)));
1582 hr_utility.trace('l_de_total(17) = ' || to_char(l_de_total(17)));
1583 hr_utility.trace('l_de_total(18) = ' || to_char(l_de_total(18)));
1584 hr_utility.trace('l_de_total(19) = ' || to_char(l_de_total(19)));
1585 hr_utility.trace('l_de_total(20) = ' || to_char(l_de_total(20)));
1586 hr_utility.trace('l_de_total(21) = ' || to_char(l_de_total(21)));
1587 hr_utility.trace('l_de_total(22) = ' || to_char(l_de_total(22)));
1588 hr_utility.trace('l_de_total(23) = ' || to_char(l_de_total(23)));
1589 hr_utility.trace('l_de_total(24) = ' || to_char(l_de_total(24)));
1590 hr_utility.trace('l_de_total(25) = ' || to_char(l_de_total(25)));
1591 hr_utility.trace('l_de_total(26) = ' || to_char(l_de_total(26)));
1592 hr_utility.trace('l_de_total(27) = ' || to_char(l_de_total(27)));
1593
1594 IF p_term_or_abs_flag = 'Y' THEN
1595
1596 open cur_pay_period_dates(p_end_date);
1597 fetch cur_pay_period_dates
1598 into
1599 l_prd_st_date_after_final,
1600 l_prd_end_date_after_final;
1601 close cur_pay_period_dates;
1602
1603 l_rehire := populate_date_lookup_table(p_payroll_id,
1604 p_assignment_id,
1605 l_start_date,
1606 l_prd_end_date_after_final,
1607 l_prd_st_date_after_final);
1608
1609 OPEN csr_get_dp_total(p_assignment_id,
1610 p_gre,
1611 l_start_date,
1612 l_prd_end_date_after_final);
1613
1614 FETCH csr_get_dp_total INTO l_dp_total(1),
1615 l_dp_total(2),
1616 l_dp_total(3),
1617 l_dp_total(4),
1618 l_dp_total(5),
1619 l_dp_total(6),
1620 l_dp_total(7),
1621 l_dp_total(8),
1622 l_dp_total(9),
1623 l_dp_total(10),
1624 l_dp_total(11),
1625 l_dp_total(12),
1626 l_dp_total(13),
1627 l_dp_total(14),
1628 l_dp_total(15),
1629 l_dp_total(16),
1630 l_dp_total(17),
1631 l_dp_total(18),
1632 l_dp_total(19),
1633 l_dp_total(20),
1634 l_dp_total(21),
1635 l_dp_total(22),
1636 l_dp_total(23),
1637 l_dp_total(24),
1638 l_dp_total(25),
1639 l_dp_total(26),
1640 l_dp_total(27),
1641 l_dp_total(28),
1642 l_dp_total(29),
1643 l_dp_total(30),
1644 l_dp_total(31),
1645 l_dp_total(32),
1646 l_dp_total(33),
1647 l_dp_total(34),
1648 l_dp_total(35),
1649 l_dp_total(36),
1650 l_dp_total(37),
1651 l_dp_total(38),
1652 l_dp_total(39),
1653 l_dp_total(40),
1654 l_dp_total(41),
1655 l_dp_total(42),
1656 l_dp_total(43),
1657 l_dp_total(44),
1658 l_dp_total(45),
1659 l_dp_total(46),
1660 l_dp_total(47),
1661 l_dp_total(48),
1662 l_dp_total(49),
1663 l_dp_total(50),
1664 l_dp_total(51),
1665 l_dp_total(52),
1666 l_dp_total(53);
1667 CLOSE csr_get_dp_total;
1668
1669 -- Must reset pay periods so that periods after termination
1670 -- are ignored for retro processing purposes
1671
1672 l_rehire := populate_date_lookup_table(p_payroll_id,
1673 p_assignment_id,
1674 l_start_date,
1675 l_last_period_end_date,
1676 l_last_period_start_date);
1677 ELSE
1678
1679 hr_utility.trace('p_assignment_id = ' || to_char(p_assignment_id));
1680 hr_utility.trace('p_gre = ' || to_char(p_gre));
1681 hr_utility.trace('l_start_date = ' || to_char(l_start_date));
1682 hr_utility.trace('l_last_period_end_date = '
1683 || to_char(l_last_period_end_date));
1684
1685 OPEN csr_get_dp_total1(p_assignment_id,
1686 p_gre,
1687 l_start_date,
1688 l_last_period_end_date);
1689 FETCH csr_get_dp_total1 INTO l_dp_total(1),
1690 l_dp_total(2),
1691 l_dp_total(3),
1692 l_dp_total(4),
1693 l_dp_total(5),
1694 l_dp_total(6),
1695 l_dp_total(7),
1696 l_dp_total(8),
1697 l_dp_total(9),
1698 l_dp_total(10),
1699 l_dp_total(11),
1700 l_dp_total(12),
1701 l_dp_total(13),
1702 l_dp_total(14),
1703 l_dp_total(15),
1704 l_dp_total(16),
1705 l_dp_total(17),
1706 l_dp_total(18),
1707 l_dp_total(19),
1708 l_dp_total(20),
1709 l_dp_total(21),
1710 l_dp_total(22),
1711 l_dp_total(23),
1712 l_dp_total(24),
1713 l_dp_total(25),
1714 l_dp_total(26),
1715 l_dp_total(27),
1716 l_dp_total(28),
1717 l_dp_total(29),
1718 l_dp_total(30),
1719 l_dp_total(31),
1720 l_dp_total(32),
1721 l_dp_total(33),
1722 l_dp_total(34),
1723 l_dp_total(35),
1724 l_dp_total(36),
1725 l_dp_total(37),
1726 l_dp_total(38),
1727 l_dp_total(39),
1728 l_dp_total(40),
1729 l_dp_total(41),
1730 l_dp_total(42),
1731 l_dp_total(43),
1732 l_dp_total(44),
1733 l_dp_total(45),
1734 l_dp_total(46),
1735 l_dp_total(47),
1736 l_dp_total(48),
1737 l_dp_total(49),
1738 l_dp_total(50),
1739 l_dp_total(51),
1740 l_dp_total(52),
1741 l_dp_total(53);
1742 CLOSE csr_get_dp_total1;
1743
1744 END IF;
1745
1746
1747 hr_utility.trace('l_dp_total(1) = ' || to_char(l_dp_total(1)));
1748 hr_utility.trace('l_dp_total(2) = ' || to_char(l_dp_total(2)));
1749 hr_utility.trace('l_dp_total(3) = ' || to_char(l_dp_total(3)));
1750 hr_utility.trace('l_dp_total(4) = ' || to_char(l_dp_total(4)));
1751 hr_utility.trace('l_dp_total(5) = ' || to_char(l_dp_total(5)));
1752 hr_utility.trace('l_dp_total(6) = ' || to_char(l_dp_total(6)));
1753 hr_utility.trace('l_dp_total(7) = ' || to_char(l_dp_total(7)));
1754 hr_utility.trace('l_dp_total(8) = ' || to_char(l_dp_total(8)));
1755 hr_utility.trace('l_dp_total(9) = ' || to_char(l_dp_total(9)));
1756 hr_utility.trace('l_dp_total(10) = ' || to_char(l_dp_total(10)));
1757 hr_utility.trace('l_dp_total(11) = ' || to_char(l_dp_total(11)));
1758 hr_utility.trace('l_dp_total(12) = ' || to_char(l_dp_total(12)));
1759 hr_utility.trace('l_dp_total(13) = ' || to_char(l_dp_total(13)));
1760 hr_utility.trace('l_dp_total(14) = ' || to_char(l_dp_total(14)));
1761 hr_utility.trace('l_dp_total(15) = ' || to_char(l_dp_total(15)));
1762 hr_utility.trace('l_dp_total(16) = ' || to_char(l_dp_total(16)));
1763 hr_utility.trace('l_dp_total(17) = ' || to_char(l_dp_total(17)));
1764 hr_utility.trace('l_dp_total(18) = ' || to_char(l_dp_total(18)));
1765 hr_utility.trace('l_dp_total(19) = ' || to_char(l_dp_total(19)));
1766 hr_utility.trace('l_dp_total(20) = ' || to_char(l_dp_total(20)));
1767 hr_utility.trace('l_dp_total(21) = ' || to_char(l_dp_total(21)));
1768 hr_utility.trace('l_dp_total(22) = ' || to_char(l_dp_total(22)));
1769 hr_utility.trace('l_dp_total(23) = ' || to_char(l_dp_total(23)));
1770 hr_utility.trace('l_dp_total(24) = ' || to_char(l_dp_total(24)));
1771 hr_utility.trace('l_dp_total(25) = ' || to_char(l_dp_total(25)));
1772 hr_utility.trace('l_dp_total(26) = ' || to_char(l_dp_total(26)));
1773 hr_utility.trace('l_dp_total(27) = ' || to_char(l_dp_total(27)));
1774
1775
1776 hr_utility.set_location(l_proc_name, 100);
1777
1778 -- If the hire date is later than either the
1779 -- previous roe date (p_start_date) or the
1780 -- starting date for the current roe then hire
1781 -- date should be used for calculating the
1782 -- number of pay periods
1783
1784 open cur_date_of_hire;
1785 fetch cur_date_of_hire
1786 into l_hire_date;
1787 close cur_date_of_hire;
1788
1789 hr_utility.trace('l_hire_date = ' || to_char(l_hire_date));
1790
1791 if p_start_date is not null and
1792 p_start_date > l_start_date then
1793
1794 hr_utility.trace('p_start_date = ' || to_char(p_start_date));
1795
1796 if l_hire_date > p_start_date then
1797 l_start_date1 := l_hire_date;
1798 else
1799 l_start_date1 := p_start_date;
1800 end if;
1801
1802 else
1803
1804 hr_utility.trace('l_start_date = ' || to_char(l_start_date));
1805
1806 if l_hire_date > l_start_date then
1807 l_start_date1 := l_hire_date;
1808 else
1809 l_start_date1 := l_start_date;
1810 end if;
1811
1812 end if;
1813
1814 hr_utility.trace('l_start_date1 = ' || to_char(l_start_date1));
1815
1816 OPEN cur_count_pay_periods(l_start_date1);
1817 FETCH cur_count_pay_periods
1818 INTO l_no_of_pay_periods;
1819 CLOSE cur_count_pay_periods;
1820
1821 hr_utility.trace('l_no_of_pay_periods = ' || to_char(l_no_of_pay_periods));
1822 hr_utility.trace('l_period_count = ' || to_char(l_period_count));
1823
1824 FOR l_index IN 1..l_period_count LOOP
1825
1826 p_period_total(l_index) := l_dp_total(l_index) + l_de_total(l_index);
1827
1828 hr_utility.trace('l_dp_total = ' || to_char(l_dp_total(l_index)));
1829 hr_utility.trace('l_de_total = ' || to_char(l_de_total(l_index)));
1830 hr_utility.trace('l_index = ' || to_char(l_index));
1831
1832 IF p_period_total(l_index) = 0 and
1833 l_index <= l_no_of_pay_periods THEN
1834 l_box15c_flag := TRUE;
1835 END IF;
1836 if l_index <= l_period_count1 then
1837 p_total_insurable := p_total_insurable + p_period_total(l_index);
1838 end if;
1839
1840 END LOOP;
1841
1842 p_no_of_periods := l_period_count;
1843
1844 -- Retro Functionality starts here
1845
1846 hr_utility.trace('Retro Functionality starts here');
1847
1848 for i in cur_retro_run(l_start_date,
1849 l_last_period_end_date) loop
1850
1851 hr_utility.trace('cur_retro_run found');
1852 hr_utility.trace('i.effective_date = ' || to_char(i.effective_date));
1853 hr_utility.trace('i.start_date = ' || to_char(i.start_date));
1854
1855 open cur_curr_pay_period_dates(i.effective_date);
1856 fetch cur_curr_pay_period_dates
1857 into l_pay_period_start_date,
1858 l_pay_period_end_date;
1859 close cur_curr_pay_period_dates;
1860
1861 hr_utility.trace('l_pay_period_start_date = ' || to_char(l_pay_period_start_date));
1862 hr_utility.trace('l_pay_period_end_date = ' || to_char(l_pay_period_end_date));
1863
1864 open cur_payroll_exists(l_pay_period_start_date,
1865 l_pay_period_end_date);
1866 fetch cur_payroll_exists
1867 into dummy;
1868 if cur_payroll_exists%NOTFOUND then
1869 close cur_payroll_exists;
1870 else
1871 close cur_payroll_exists;
1872
1873 for k in cur_ele_entries(l_pay_period_start_date,
1874 l_pay_period_end_date) loop
1875
1876 hr_utility.trace('k.element_type_id = ' || to_char(k.element_type_id));
1877 hr_utility.trace('k.creator_type = ' || k.creator_type);
1878 hr_utility.trace('k.source_id = ' || to_char(nvl(k.source_id,0)));
1879 hr_utility.trace('k.source_asg_action_id = ' || to_char(nvl(k.source_asg_action_id,0)));
1880 hr_utility.trace('k.screen_entry_value = ' || nvl(k.screen_entry_value,'0'));
1881 hr_utility.trace('k.element_entry_id = ' || to_char(k.element_entry_id));
1882 hr_utility.trace('k.classification_name = ' || nvl(k.classification_name,' '));
1883 hr_utility.trace('k.classification_id = ' || to_char(nvl(k.classification_id,0)));
1884 hr_utility.trace('k.element_information1 = ' || k.element_information1);
1885
1886 if (((k.element_entry_id <> l_prev_element_entry_id) or
1887 (l_prev_element_entry_id is null)) and
1888 (taxability_rule_exists(k.classification_name,
1889 k.classification_id,
1890 k.element_information1,
1891 l_pay_period_end_date,
1892 'EIM') = 'TRUE')) then
1893
1894 l_value := to_number(k.screen_entry_value);
1895
1896 if (k.element_information3 = 'DE') then
1897
1898 if k.creator_type = 'RR' then
1899
1900 open cur_originating_period_rr_de(k.source_id,
1901 l_start_date,
1902 l_last_period_end_date);
1903 fetch cur_originating_period_rr_de into l_start_period;
1904 close cur_originating_period_rr_de;
1905
1906 else
1907
1908 open cur_originating_period_asg_de(k.source_asg_action_id,
1909 l_start_date,
1910 l_last_period_end_date);
1911 fetch cur_originating_period_asg_de into l_start_period;
1912 close cur_originating_period_asg_de;
1913
1914 end if;
1915
1916 else /* Retro element is Date Paid */
1917
1918 l_start_period := null;
1919
1920 end if;
1921
1922 period_from := pay_ca_roe_ei_pkg.get_pd_num(i.effective_date,
1923 l_last_period_end_date);
1924
1925 if (l_start_period is not null and
1926 l_value is not null) then
1927
1928 period_to := pay_ca_roe_ei_pkg.get_pd_num(l_start_period,
1929 l_last_period_end_date);
1930
1931 hr_utility.trace('period_from = ' || to_char(period_from));
1932 hr_utility.trace('period_to = ' || to_char(period_to));
1933
1934 p_period_total(period_from) := p_period_total(period_from) - l_value;
1935 p_period_total(period_to) := p_period_total(period_to) + l_value;
1936
1937 end if;
1938
1939 end if; -- prev element entry id
1940
1941 l_prev_element_entry_id := k.element_entry_id;
1942
1943 end loop;
1944
1945 end if;
1946
1947 end loop;
1948
1949 -- Retro Functionality ends here;
1950
1951 hr_utility.set_location(l_proc_name, 120);
1952 IF l_box15c_flag THEN
1953 hr_utility.set_location('Ending: ' || l_proc_name, 130);
1954 RETURN 'BOX15C';
1955 ELSE
1956 hr_utility.set_location('Ending: ' || l_proc_name, 140);
1957 RETURN 'BOX15B';
1958 END IF;
1959
1960 END IF;
1961
1962 END get_ei_amount_totals;
1963
1964
1965 -------------------------------------------------------------------------------
1966 -- Name: populate_element_table
1967 --
1968 -- Parameters:
1969 --
1970 -- Description: This procedure creates element tables that would subsequently be
1971 -- be used to determine if date paid or date earned should be used
1972 -- to calculate the balance totals for the ROE report.
1973 -- We need to check Special Features element because 'EI Horus' are
1974 -- stored on the Special Features element.
1975 -------------------------------------------------------------------------------
1976 PROCEDURE populate_element_table(p_bg_id number) IS
1977
1978 CURSOR cur_bal_type_id IS
1979 SELECT
1980 pbt.balance_type_id
1981 FROM
1982 pay_balance_types pbt
1983 WHERE
1984 pbt.balance_name = 'EI Hours' and
1985 pbt.legislation_code = 'CA';
1986
1987 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
1988
1989 /* CURSOR csr_get_element_id (p_dp_or_de VARCHAR2)
1990 IS
1991 SELECT DISTINCT ele.element_type_id
1992 FROM pay_element_types_f ele,
1993 pay_template_core_objects tco1,
1994 pay_shadow_element_types sel,
1995 pay_element_templates etp,
1996 pay_template_core_objects tco2
1997 WHERE tco2.core_object_type = 'ET'
1998 AND etp.template_id = tco2.template_id
1999 AND sel.template_id = etp.template_id
2000 AND sel.element_name NOT LIKE ('%Special Inputs')
2001 AND sel.element_type_id = tco1.shadow_object_id
2002 AND tco1.core_object_type = 'ET'
2003 AND ele.element_type_id = tco1.core_object_id
2004 AND NVL(ele.element_information3,'DP') = p_dp_or_de
2005 UNION ALL
2006 SELECT DISTINCT ele.element_type_id
2007 FROM pay_element_types_f ele,
2008 pay_element_classifications pec
2009 WHERE ele.business_group_id is NULL
2010 AND ele.legislation_code = 'CA'
2011 AND pec.legislation_code = 'CA'
2012 AND pec.classification_name = 'Earnings'
2013 AND ele.classification_id = pec.classification_id
2014 AND p_dp_or_de = 'DE'; */
2015
2016 CURSOR csr_get_element_id (p_dp_or_de VARCHAR2) IS
2017 SELECT
2018 pet.element_type_id
2019 FROM
2020 pay_element_types_f pet
2021 WHERE
2022 pet.business_group_id = p_bg_id and
2023 NVL(pet.element_information3,'DP') = p_dp_or_de and
2024 EXISTS
2025 (SELECT 'X' FROM
2026 pay_input_values_f piv,
2027 pay_balance_feeds_f pbf
2028 WHERE
2029 piv.element_type_id = pet.element_type_id AND
2030 piv.input_value_id = pbf.input_value_id AND
2031 pbf.balance_type_id = l_bal_type_id)
2032 UNION ALL
2033 SELECT DISTINCT ele.element_type_id
2034 FROM pay_element_types_f ele,
2035 pay_element_classifications pec
2036 WHERE ele.business_group_id is NULL
2037 AND ele.legislation_code = 'CA'
2038 AND pec.legislation_code = 'CA'
2039 AND pec.classification_name = 'Earnings'
2040 AND ele.classification_id = pec.classification_id
2041 AND p_dp_or_de = 'DE';
2042
2043 de_element NUMBER;
2044 dp_element NUMBER;
2045
2046 BEGIN
2047
2048 OPEN cur_bal_type_id;
2049 FETCH cur_bal_type_id
2050 INTO l_bal_type_id;
2051 CLOSE cur_bal_type_id;
2052
2053 OPEN csr_get_element_id ('DE');
2054 LOOP
2055 FETCH csr_get_element_id
2056 INTO de_element;
2057 EXIT WHEN csr_get_element_id%NOTFOUND;
2058
2059 de_element_table(de_element).element_id := de_element;
2060
2061 END LOOP;
2062
2063 CLOSE csr_get_element_id;
2064
2065 OPEN csr_get_element_id ('DP');
2066 LOOP
2067 FETCH csr_get_element_id
2068 INTO dp_element;
2069 EXIT WHEN csr_get_element_id%NOTFOUND;
2070
2071 dp_element_table(dp_element).element_id := dp_element;
2072
2073 END LOOP;
2074
2075 CLOSE csr_get_element_id;
2076
2077 END populate_element_table;
2078
2079 -------------------------------------------------------------------------------
2080 -- Name: date_paid_or_date_earned
2081 --
2082 -- Parameters: p_element_type_id
2083 -- p_dp_or_de
2084 -- p_ele_info3
2085 --
2086 -- Return: VARCHAR2 - 'TRUE' or 'FALSE'
2087 --
2088 -- Description: This function determines whether we should use date paid or date
2089 -- earned to calculate the balance totals for the ROE report.
2090 -------------------------------------------------------------------------------
2091 FUNCTION date_paid_or_date_earned
2092 (p_element_type_id NUMBER,
2093 p_dp_or_de VARCHAR2,
2094 p_ele_info3 VARCHAR2)
2095 RETURN VARCHAR2 IS
2096
2097 BEGIN
2098
2099 IF p_ele_info3 IN ('DP', 'DE') THEN
2100 IF p_ele_info3 = p_dp_or_de THEN
2101 RETURN 'TRUE';
2102 ELSE
2103 RETURN 'FALSE';
2104 END IF;
2105 ELSE
2106 IF p_dp_or_de = 'DE' THEN
2107 IF de_element_table.EXISTS(p_element_type_id) THEN
2108 RETURN 'TRUE';
2109 ELSE
2110 RETURN 'FALSE';
2111 END IF;
2112 ELSE
2113 IF dp_element_table.EXISTS(p_element_type_id) THEN
2114 RETURN 'TRUE';
2115 ELSE
2116 RETURN 'FALSE';
2117 END IF;
2118 END IF;
2119 END IF;
2120
2121 END date_paid_or_date_earned;
2122
2123 END pay_ca_roe_ei_pkg;