[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.12.12020000.4 2012/07/06 09:10:28 vmaripal ship $ */
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
27 (p_payroll_id NUMBER,
24 -- saved in the PL/SQL table
25 -------------------------------------------------------------------------------
26 FUNCTION populate_date_lookup_table
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 /*+ leading(asa,pya,ele) use_merge(ele) */
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 /*+ leading(asa,pya,ele) use_merge(ele) */
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
485 AND blt.legislation_code = 'CA';
482 AND blf.effective_end_date
483 AND blf.balance_type_id = blt.balance_type_id
484 AND blt.balance_name = 'EI Hours'
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 /*+ leading(asa,pya,ele) use_merge(ele) */
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 /*+ leading(asa,pya,ele) use_merge(ele) */
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
571 /* Modifed the cursor for Bug 4510534 */
572 CURSOR csr_get_dp_total(p_asg_id NUMBER,
573 p_gre NUMBER,
574 p_start_date DATE,
575 p_end_date DATE) IS
576 SELECT /*+ RULE */
577 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
578 1, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
579 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
580 2, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
581 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
582 3, NVL(rrv.result_value, 0)*blf.scale,0)),0),
583 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
584 4, NVL(rrv.result_value, 0)*blf.scale,0)),0),
585 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
586 5, NVL(rrv.result_value, 0)*blf.scale,0)),0),
587 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
591 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
588 6, NVL(rrv.result_value, 0)*blf.scale,0)),0),
589 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
590 7, NVL(rrv.result_value, 0)*blf.scale,0)),0),
592 8, NVL(rrv.result_value, 0)*blf.scale,0)),0),
593 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
594 9, NVL(rrv.result_value, 0)*blf.scale,0)),0),
595 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
596 10,NVL(rrv.result_value, 0)*blf.scale,0)),0),
597 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
598 11,NVL(rrv.result_value, 0)*blf.scale,0)),0),
599 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
600 12,NVL(rrv.result_value, 0)*blf.scale,0)),0),
601 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
602 13,NVL(rrv.result_value, 0)*blf.scale,0)),0),
603 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
604 14,NVL(rrv.result_value, 0)*blf.scale,0)),0),
605 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
606 15,NVL(rrv.result_value, 0)*blf.scale,0)),0),
607 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
608 16,NVL(rrv.result_value, 0)*blf.scale,0)),0),
609 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
610 17,NVL(rrv.result_value, 0)*blf.scale,0)),0),
611 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
612 18,NVL(rrv.result_value, 0)*blf.scale,0)),0),
613 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
614 19,NVL(rrv.result_value, 0)*blf.scale,0)),0),
615 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
616 20,NVL(rrv.result_value, 0)*blf.scale,0)),0),
617 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
618 21,NVL(rrv.result_value, 0)*blf.scale,0)),0),
619 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
620 22,NVL(rrv.result_value, 0)*blf.scale,0)),0),
621 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
622 23,NVL(rrv.result_value, 0)*blf.scale,0)),0),
623 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
624 24,NVL(rrv.result_value, 0)*blf.scale,0)),0),
625 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
626 25,NVL(rrv.result_value, 0)*blf.scale,0)),0),
627 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
628 26,NVL(rrv.result_value, 0)*blf.scale,0)),0),
629 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
630 27,NVL(rrv.result_value, 0)*blf.scale,0)),0),
631 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
632 28,NVL(rrv.result_value, 0)*blf.scale,0)),0),
633 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
634 29,NVL(rrv.result_value, 0)*blf.scale,0)),0),
635 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
636 30, NVL(rrv.result_value, 0)*blf.scale,0)),0),
637 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
638 31, NVL(rrv.result_value, 0)*blf.scale,0)),0),
639 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
640 32, NVL(rrv.result_value, 0)*blf.scale,0)),0),
641 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
642 33, NVL(rrv.result_value, 0)*blf.scale,0)),0),
643 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
644 34, NVL(rrv.result_value, 0)*blf.scale,0)),0),
645 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
646 35, NVL(rrv.result_value, 0)*blf.scale,0)),0),
647 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
648 36, NVL(rrv.result_value, 0)*blf.scale,0)),0),
649 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
650 37,NVL(rrv.result_value, 0)*blf.scale,0)),0),
651 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
652 38,NVL(rrv.result_value, 0)*blf.scale,0)),0),
653 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
654 39,NVL(rrv.result_value, 0)*blf.scale,0)),0),
655 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
656 40,NVL(rrv.result_value, 0)*blf.scale,0)),0),
657 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
658 41,NVL(rrv.result_value, 0)*blf.scale,0)),0),
659 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
660 42,NVL(rrv.result_value, 0)*blf.scale,0)),0),
661 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
662 43,NVL(rrv.result_value, 0)*blf.scale,0)),0),
663 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
664 44,NVL(rrv.result_value, 0)*blf.scale,0)),0),
665 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
666 45,NVL(rrv.result_value, 0)*blf.scale,0)),0),
667 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
668 46,NVL(rrv.result_value, 0)*blf.scale,0)),0),
669 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
670 47,NVL(rrv.result_value, 0)*blf.scale,0)),0),
671 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
672 48,NVL(rrv.result_value, 0)*blf.scale,0)),0),
673 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
674 49,NVL(rrv.result_value, 0)*blf.scale,0)),0),
675 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
676 50,NVL(rrv.result_value, 0)*blf.scale,0)),0),
677 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
678 51,NVL(rrv.result_value, 0)*blf.scale,0)),0),
679 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
680 52,NVL(rrv.result_value, 0)*blf.scale,0)),0),
681 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
682 53,NVL(rrv.result_value, 0)*blf.scale,0)),0),
683 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
684 54,NVL(rrv.result_value, 0)*blf.scale,0)),0),
685 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
686 55,NVL(rrv.result_value, 0)*blf.scale,0)),0)
687 FROM pay_assignment_actions asa,
688 pay_payroll_actions pya,
689 pay_run_results rrs,
690 pay_run_result_values rrv,
691 pay_element_types_f ele,
692 pay_element_classifications elc,
693 pay_input_values_f ipv,
694 pay_balance_feeds_f blf,
695 pay_balance_types blt
696 WHERE asa.assignment_id = p_asg_id
697 AND asa.tax_unit_id = p_gre
698 AND pya.payroll_id = p_payroll_id
699 AND pya.payroll_action_id = asa.payroll_action_id
700 AND pya.effective_date BETWEEN p_start_date
701 AND p_end_date
702 AND rrs.assignment_action_id = asa.assignment_action_id
703 AND ele.element_type_id = rrs.element_type_id
704 AND NVL(ele.element_information3,'DP') = 'DP'
705 AND pya.effective_date BETWEEN ele.effective_start_date
706 AND ele.effective_end_date
707 AND elc.classification_id = ele.classification_id
708 AND elc.classification_name IN ('Earnings',
709 'Supplemental Earnings',
710 'Taxable Benefits',
711 'Balance Initialization')
712 AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
713 elc.classification_id,
714 ele.element_information1,
715 pya.effective_date,
716 blt.tax_type) = 'TRUE'
717 AND rrv.run_result_id = rrs.run_result_id
718 AND ipv.input_value_id = rrv.input_value_id
719 AND pya.effective_date BETWEEN ipv.effective_start_date
720 AND ipv.effective_end_date
721 AND blf.input_value_id = ipv.input_value_id
722 AND pya.effective_date BETWEEN blf.effective_start_date
723 AND blf.effective_end_date
724 AND blf.balance_type_id = blt.balance_type_id
725 AND blt.balance_name IN ('Regular Earnings',
726 'Supplemental Earnings for EI',
727 'Taxable Benefits for EI')
728 AND blt.legislation_code = 'CA'
729 AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
730 SYSDATE,SYSDATE, pya.effective_date, 'EIEXEMPT'),'N') = 'N';
731
732 CURSOR csr_get_dp_total1(p_asg_id NUMBER,
733 p_gre NUMBER,
734 p_start_date DATE,
735 p_end_date DATE) IS
736 SELECT /*+ RULE */
737 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
738 1, NVL(rrv.result_value, 0)*blf.scale,0)),0),
739 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
740 2, NVL(rrv.result_value, 0)*blf.scale,0)),0),
741 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
742 3, NVL(rrv.result_value, 0)*blf.scale,0)),0),
743 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
744 4, NVL(rrv.result_value, 0)*blf.scale,0)),0),
745 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
746 5, NVL(rrv.result_value, 0)*blf.scale,0)),0),
747 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
748 6, NVL(rrv.result_value, 0)*blf.scale,0)),0),
752 8, NVL(rrv.result_value, 0)*blf.scale,0)),0),
749 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
750 7, NVL(rrv.result_value, 0)*blf.scale,0)),0),
751 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
753 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
754 9, NVL(rrv.result_value, 0)*blf.scale,0)),0),
755 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
756 10,NVL(rrv.result_value, 0)*blf.scale,0)),0),
757 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
758 11,NVL(rrv.result_value, 0)*blf.scale,0)),0),
759 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
760 12,NVL(rrv.result_value, 0)*blf.scale,0)),0),
761 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
762 13,NVL(rrv.result_value, 0)*blf.scale,0)),0),
763 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
764 14,NVL(rrv.result_value, 0)*blf.scale,0)),0),
765 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
766 15,NVL(rrv.result_value, 0)*blf.scale,0)),0),
767 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
768 16,NVL(rrv.result_value, 0)*blf.scale,0)),0),
769 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
770 17,NVL(rrv.result_value, 0)*blf.scale,0)),0),
771 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
772 18,NVL(rrv.result_value, 0)*blf.scale,0)),0),
773 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
774 19,NVL(rrv.result_value, 0)*blf.scale,0)),0),
775 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
776 20,NVL(rrv.result_value, 0)*blf.scale,0)),0),
777 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
778 21,NVL(rrv.result_value, 0)*blf.scale,0)),0),
779 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
780 22,NVL(rrv.result_value, 0)*blf.scale,0)),0),
781 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
782 23,NVL(rrv.result_value, 0)*blf.scale,0)),0),
783 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
784 24,NVL(rrv.result_value, 0)*blf.scale,0)),0),
785 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
786 25,NVL(rrv.result_value, 0)*blf.scale,0)),0),
787 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
788 26,NVL(rrv.result_value, 0)*blf.scale,0)),0),
789 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
790 27,NVL(rrv.result_value, 0)*blf.scale,0)),0),
791 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
792 28, NVL(rrv.result_value, 0)*blf.scale,0)),0),
793 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
794 29, NVL(rrv.result_value, 0)*blf.scale,0)),0),
795 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
796 30, NVL(rrv.result_value, 0)*blf.scale,0)),0),
797 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
798 31, NVL(rrv.result_value, 0)*blf.scale,0)),0),
799 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
800 32, NVL(rrv.result_value, 0)*blf.scale,0)),0),
801 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
802 33, NVL(rrv.result_value, 0)*blf.scale,0)),0),
803 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
804 34, NVL(rrv.result_value, 0)*blf.scale,0)),0),
805 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
806 35, NVL(rrv.result_value, 0)*blf.scale,0)),0),
807 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
808 36,NVL(rrv.result_value, 0)*blf.scale,0)),0),
809 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
810 37,NVL(rrv.result_value, 0)*blf.scale,0)),0),
811 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
812 38,NVL(rrv.result_value, 0)*blf.scale,0)),0),
813 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
814 39,NVL(rrv.result_value, 0)*blf.scale,0)),0),
815 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
816 40,NVL(rrv.result_value, 0)*blf.scale,0)),0),
817 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
818 41,NVL(rrv.result_value, 0)*blf.scale,0)),0),
819 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
820 42,NVL(rrv.result_value, 0)*blf.scale,0)),0),
821 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
822 43,NVL(rrv.result_value, 0)*blf.scale,0)),0),
823 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
824 44,NVL(rrv.result_value, 0)*blf.scale,0)),0),
825 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
826 45,NVL(rrv.result_value, 0)*blf.scale,0)),0),
827 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
828 46,NVL(rrv.result_value, 0)*blf.scale,0)),0),
829 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
830 47,NVL(rrv.result_value, 0)*blf.scale,0)),0),
831 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
832 48,NVL(rrv.result_value, 0)*blf.scale,0)),0),
833 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
834 49,NVL(rrv.result_value, 0)*blf.scale,0)),0),
835 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
836 50,NVL(rrv.result_value, 0)*blf.scale,0)),0),
837 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
838 51,NVL(rrv.result_value, 0)*blf.scale,0)),0),
839 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
840 52,NVL(rrv.result_value, 0)*blf.scale,0)),0),
841 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.effective_date,p_end_date),
842 53,NVL(rrv.result_value, 0)*blf.scale,0)),0)
843 FROM pay_assignment_actions asa,
844 pay_payroll_actions pya,
845 pay_run_results rrs,
846 pay_run_result_values rrv,
847 pay_element_types_f ele,
848 pay_element_classifications elc,
849 pay_input_values_f ipv,
850 pay_balance_feeds_f blf,
851 pay_balance_types blt
852 WHERE asa.assignment_id = p_asg_id
853 AND asa.tax_unit_id = p_gre
854 AND pya.payroll_id = p_payroll_id
855 AND pya.payroll_action_id = asa.payroll_action_id
856 AND pya.effective_date BETWEEN p_start_date
857 AND p_end_date
858 AND rrs.assignment_action_id = asa.assignment_action_id
859 AND ele.element_type_id = rrs.element_type_id
860 AND NVL(ele.element_information3,'DP') = 'DP'
861 AND pya.effective_date BETWEEN ele.effective_start_date
862 AND ele.effective_end_date
863 AND elc.classification_id = ele.classification_id
864 AND elc.classification_name IN ('Earnings',
865 'Supplemental Earnings',
866 'Taxable Benefits',
867 'Balance Initialization')
868 AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
869 elc.classification_id,
870 ele.element_information1,
871 pya.effective_date,
872 blt.tax_type) = 'TRUE'
873 AND rrv.run_result_id = rrs.run_result_id
874 AND ipv.input_value_id = rrv.input_value_id
875 AND pya.effective_date BETWEEN ipv.effective_start_date
876 AND ipv.effective_end_date
877 AND blf.input_value_id = ipv.input_value_id
878 AND pya.effective_date BETWEEN blf.effective_start_date
879 AND blf.effective_end_date
880 AND blf.balance_type_id = blt.balance_type_id
881 AND blt.balance_name IN ('Regular Earnings',
882 'Supplemental Earnings for EI',
883 'Taxable Benefits for EI')
884 AND blt.legislation_code = 'CA'
885 AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
886 SYSDATE,SYSDATE, pya.effective_date, 'EIEXEMPT'),'N') = 'N';
887
888 CURSOR csr_get_de_total(p_asg_id NUMBER,
889 p_gre NUMBER,
890 p_start_date DATE,
891 p_end_date DATE) IS
892 SELECT /*+ RULE */
893 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
894 1, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
895 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
896 2, NVL(rrv.result_value, 0)*blf.scale,0)),0) +
897 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
898 3, NVL(rrv.result_value, 0)*blf.scale,0)),0),
899 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
900 4, NVL(rrv.result_value, 0)*blf.scale,0)),0),
901 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
902 5, NVL(rrv.result_value, 0)*blf.scale,0)),0),
903 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
904 6, NVL(rrv.result_value, 0)*blf.scale,0)),0),
905 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
906 7, NVL(rrv.result_value, 0)*blf.scale,0)),0),
907 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
908 8, NVL(rrv.result_value, 0)*blf.scale,0)),0),
909 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
910 9, NVL(rrv.result_value, 0)*blf.scale,0)),0),
911 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
912 10,NVL(rrv.result_value, 0)*blf.scale,0)),0),
916 12,NVL(rrv.result_value, 0)*blf.scale,0)),0),
913 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
914 11,NVL(rrv.result_value, 0)*blf.scale,0)),0),
915 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
917 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
918 13,NVL(rrv.result_value, 0)*blf.scale,0)),0),
919 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
920 14,NVL(rrv.result_value, 0)*blf.scale,0)),0),
921 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
922 15,NVL(rrv.result_value, 0)*blf.scale,0)),0),
923 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
924 16,NVL(rrv.result_value, 0)*blf.scale,0)),0),
925 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
926 17,NVL(rrv.result_value, 0)*blf.scale,0)),0),
927 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
928 18,NVL(rrv.result_value, 0)*blf.scale,0)),0),
929 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
930 19,NVL(rrv.result_value, 0)*blf.scale,0)),0),
931 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
932 20,NVL(rrv.result_value, 0)*blf.scale,0)),0),
933 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
934 21,NVL(rrv.result_value, 0)*blf.scale,0)),0),
935 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
936 22,NVL(rrv.result_value, 0)*blf.scale,0)),0),
937 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
938 23,NVL(rrv.result_value, 0)*blf.scale,0)),0),
939 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
940 24,NVL(rrv.result_value, 0)*blf.scale,0)),0),
941 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
942 25,NVL(rrv.result_value, 0)*blf.scale,0)),0),
943 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
944 26,NVL(rrv.result_value, 0)*blf.scale,0)),0),
945 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
946 27,NVL(rrv.result_value, 0)*blf.scale,0)),0),
947 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
948 28,NVL(rrv.result_value, 0)*blf.scale,0)),0),
949 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
950 29,NVL(rrv.result_value, 0)*blf.scale,0)),0),
951 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
952 30, NVL(rrv.result_value, 0)*blf.scale,0)),0),
953 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
954 31, NVL(rrv.result_value, 0)*blf.scale,0)),0),
955 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
956 32, NVL(rrv.result_value, 0)*blf.scale,0)),0),
957 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
958 33, NVL(rrv.result_value, 0)*blf.scale,0)),0),
959 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
960 34, NVL(rrv.result_value, 0)*blf.scale,0)),0),
961 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
962 35, NVL(rrv.result_value, 0)*blf.scale,0)),0),
963 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
964 36, NVL(rrv.result_value, 0)*blf.scale,0)),0),
965 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
966 37,NVL(rrv.result_value, 0)*blf.scale,0)),0),
967 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
968 38,NVL(rrv.result_value, 0)*blf.scale,0)),0),
969 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
970 39,NVL(rrv.result_value, 0)*blf.scale,0)),0),
971 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
972 40,NVL(rrv.result_value, 0)*blf.scale,0)),0),
973 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
974 41,NVL(rrv.result_value, 0)*blf.scale,0)),0),
975 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
976 42,NVL(rrv.result_value, 0)*blf.scale,0)),0),
977 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
978 43,NVL(rrv.result_value, 0)*blf.scale,0)),0),
979 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
980 44,NVL(rrv.result_value, 0)*blf.scale,0)),0),
981 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
982 45,NVL(rrv.result_value, 0)*blf.scale,0)),0),
983 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
984 46,NVL(rrv.result_value, 0)*blf.scale,0)),0),
985 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
986 47,NVL(rrv.result_value, 0)*blf.scale,0)),0),
987 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
988 48,NVL(rrv.result_value, 0)*blf.scale,0)),0),
989 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
990 49,NVL(rrv.result_value, 0)*blf.scale,0)),0),
991 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
992 50,NVL(rrv.result_value, 0)*blf.scale,0)),0),
993 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
994 51,NVL(rrv.result_value, 0)*blf.scale,0)),0),
995 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
996 52,NVL(rrv.result_value, 0)*blf.scale,0)),0),
997 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
998 53,NVL(rrv.result_value, 0)*blf.scale,0)),0),
999 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1000 54,NVL(rrv.result_value, 0)*blf.scale,0)),0),
1001 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1002 55,NVL(rrv.result_value, 0)*blf.scale,0)),0)
1003 FROM pay_assignment_actions asa,
1004 pay_payroll_actions pya,
1005 pay_run_results rrs,
1006 pay_run_result_values rrv,
1007 pay_element_types_f ele,
1008 pay_element_classifications elc,
1009 pay_input_values_f ipv,
1010 pay_balance_feeds_f blf,
1011 pay_balance_types blt
1012 WHERE asa.assignment_id = p_asg_id
1013 AND asa.tax_unit_id = p_gre
1014 AND pya.payroll_id = p_payroll_id
1015 AND pya.payroll_action_id = asa.payroll_action_id
1016 AND pya.date_earned BETWEEN p_start_date
1017 AND p_end_date
1018 AND rrs.assignment_action_id = asa.assignment_action_id
1019 AND ele.element_type_id = rrs.element_type_id
1020 AND NVL(ele.element_information3,'DP') = 'DE'
1021 AND pya.date_earned BETWEEN ele.effective_start_date
1022 AND ele.effective_end_date
1023 AND elc.classification_id = ele.classification_id
1024 AND elc.classification_name IN ('Earnings',
1025 'Supplemental Earnings',
1026 'Taxable Benefits')
1027 AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
1028 elc.classification_id,
1029 ele.element_information1,
1030 pya.date_earned,
1031 blt.tax_type) = 'TRUE'
1032 AND rrv.run_result_id = rrs.run_result_id
1033 AND ipv.input_value_id = rrv.input_value_id
1034 AND pya.date_earned BETWEEN ipv.effective_start_date
1035 AND ipv.effective_end_date
1036 AND blf.input_value_id = ipv.input_value_id
1037 AND pya.date_earned BETWEEN blf.effective_start_date
1038 AND blf.effective_end_date
1039 AND blf.balance_type_id = blt.balance_type_id
1040 AND blt.balance_name IN ('Regular Earnings',
1041 'Supplemental Earnings for EI',
1042 'Taxable Benefits for EI')
1043 AND blt.legislation_code = 'CA'
1044 AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
1045 SYSDATE, SYSDATE, pya.date_earned, 'EIEXEMPT'),'N') = 'N';
1046
1047 CURSOR csr_get_de_total1(p_asg_id NUMBER,
1048 p_gre NUMBER,
1049 p_start_date DATE,
1050 p_end_date DATE) IS
1051 SELECT /*+ RULE */
1052 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1053 1, NVL(rrv.result_value, 0)*blf.scale,0)),0),
1054 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1055 2, NVL(rrv.result_value, 0)*blf.scale,0)),0),
1056 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1057 3, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1058 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1059 4, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1060 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1061 5, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1062 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1063 6, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1064 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1065 7, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1066 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1067 8, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1068 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1069 9, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1070 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1071 10, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1072 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1073 11, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1074 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1075 12, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1076 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1077 13, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1078 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1079 14, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1080 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1081 15, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1082 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1083 16, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1084 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1085 17, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1086 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1087 18, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1088 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1089 19, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1090 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1091 20, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1092 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1093 21, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1094 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1095 22, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1096 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1097 23, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1098 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1099 24, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1100 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1101 25, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1102 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1103 26, NVL(rrv.result_value, 0)*blf.scale,0)),0),
1104 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1105 27, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1106 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1107 28, NVL(rrv.result_value, 0)*blf.scale,0)),0),
1108 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1109 29, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1110 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1111 30, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1112 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1113 31, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1114 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1115 32, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1116 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1117 33, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1118 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1119 34, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1120 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1121 35, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1122 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1123 36, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1124 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1125 37, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1126 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1127 38, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1128 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1129 39, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1130 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1131 40, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1132 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1133 41, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1134 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1135 42, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1136 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1137 43, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1138 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1139 44, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1140 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1141 45, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1142 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1143 46, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1144 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1145 47, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1146 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1147 48, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1148 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1149 49, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1150 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1151 50, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1152 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1156 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1153 51, NVL(rrv.result_value, 0)*blf.scale,0)),0) ,
1154 NVL(SUM(DECODE(pay_ca_roe_ei_pkg.get_pd_num(pya.date_earned,p_end_date),
1155 52, NVL(rrv.result_value, 0)*blf.scale,0)),0),
1157 53, NVL(rrv.result_value, 0)*blf.scale,0)),0)
1158 FROM pay_assignment_actions asa,
1159 pay_payroll_actions pya,
1160 pay_run_results rrs,
1161 pay_run_result_values rrv,
1162 pay_element_types_f ele,
1163 pay_element_classifications elc,
1164 pay_input_values_f ipv,
1165 pay_balance_feeds_f blf,
1166 pay_balance_types blt
1167 WHERE asa.assignment_id = p_asg_id
1168 AND asa.tax_unit_id = p_gre
1169 AND pya.payroll_id = p_payroll_id
1170 AND pya.payroll_action_id = asa.payroll_action_id
1171 AND pya.date_earned BETWEEN p_start_date
1172 AND p_end_date
1173 AND rrs.assignment_action_id = asa.assignment_action_id
1174 AND ele.element_type_id = rrs.element_type_id
1175 AND NVL(ele.element_information3,'DP') = 'DE'
1176 AND pya.date_earned BETWEEN ele.effective_start_date
1177 AND ele.effective_end_date
1178 AND elc.classification_id = ele.classification_id
1179 AND elc.classification_name IN ('Earnings',
1180 'Supplemental Earnings',
1181 'Taxable Benefits')
1182 AND pay_ca_roe_ei_pkg.taxability_rule_exists(elc.classification_name,
1183 elc.classification_id,
1184 ele.element_information1,
1185 pya.date_earned,
1186 blt.tax_type) = 'TRUE'
1187 AND rrv.run_result_id = rrs.run_result_id
1188 AND ipv.input_value_id = rrv.input_value_id
1189 AND pya.date_earned BETWEEN ipv.effective_start_date
1190 AND ipv.effective_end_date
1191 AND blf.input_value_id = ipv.input_value_id
1192 AND pya.date_earned BETWEEN blf.effective_start_date
1193 AND blf.effective_end_date
1194 AND blf.balance_type_id = blt.balance_type_id
1195 AND blt.balance_name IN ('Regular Earnings',
1196 'Supplemental Earnings for EI',
1197 'Taxable Benefits for EI')
1198 AND blt.legislation_code = 'CA'
1199 AND NVL(pay_ca_emp_tax_inf.get_tax_detail_char(p_asg_id,
1200 SYSDATE, SYSDATE, pya.date_earned, 'EIEXEMPT'),'N') = 'N';
1201
1202 l_proc_name VARCHAR2(60) := 'pay_ca_roe_ei_pkg.get_ei_amount_totals';
1203
1204 l_start_date DATE;
1205 l_start_period DATE;
1206 l_last_period_start_date DATE;
1207 l_last_period_end_date DATE;
1208 l_value NUMBER;
1209 l_prev_element_entry_id NUMBER;
1210 l_period_count NUMBER;
1211 l_period_count1 NUMBER;
1212 l_box15c_flag BOOLEAN := FALSE;
1213
1214 l_dp_hours_total NUMBER;
1215 l_de_hours_total NUMBER;
1216 l_hours_total NUMBER;
1217
1218 l_de_total t_large_number_table;
1219 l_dp_total t_large_number_table;
1220
1221
1222 CURSOR cur_count_pay_periods(p_start_date1 DATE) IS
1223 SELECT COUNT(*)
1224 FROM per_time_periods
1225 WHERE payroll_id = p_payroll_id
1226 AND end_date >= p_start_date1
1227 AND start_date <= p_end_date;
1228
1229 l_no_of_pay_periods NUMBER;
1230 l_start_date1 DATE;
1231
1232 cursor cur_date_of_hire is
1233 select max(service.date_start) hire_date
1234 from per_periods_of_service service,
1235 per_assignments_f asg
1236 where asg.assignment_id = p_assignment_id
1237 and p_end_date BETWEEN
1238 asg.effective_start_date
1239 AND asg.effective_end_date
1240 and asg.person_id = service.person_id(+)
1241 and service.date_start <= p_end_date;
1242
1243 l_hire_date DATE;
1244
1245 cursor cur_next_prd_start_date is
1246 select
1247 ptp.end_date + 1
1248 from
1249 per_time_periods ptp
1250 where
1251 ptp.payroll_id = p_payroll_id and
1252 p_start_date between
1253 ptp.start_date and ptp.end_date;
1254
1255 cursor csr_start_date (p_payroll_id number,
1256 p_start_date date) is
1257 select start_date
1258 from per_time_periods
1259 where payroll_id = p_payroll_id
1260 and start_date = p_start_date;
1261
1262 l_period_start_date date;
1263 l_period_start_date_exists varchar2(1);
1264
1265 cursor cur_pay_period_dates(p_date date) is
1266 select
1267 ptp2.start_date,
1268 ptp2.end_date
1269 from
1270 per_time_periods ptp,
1271 per_time_periods ptp1,
1272 per_time_periods ptp2
1273 where
1274 ptp.payroll_id = p_payroll_id and
1275 p_date between
1276 ptp.start_date and
1277 ptp.end_date and
1278 ptp.payroll_id = ptp1.payroll_id and
1279 ptp.end_date + 1 between
1280 ptp1.start_date and
1281 ptp1.end_date and
1282 ptp1.payroll_id = ptp2.payroll_id and
1283 ptp1.end_date + 1 between
1284 ptp2.start_date and
1285 ptp2.end_date;
1286
1287 l_prd_st_date_after_final date;
1288 l_prd_end_date_after_final date;
1289
1290 CURSOR cur_retro_run(p_start_date date,
1291 p_end_date date) is
1292 select
1296 from
1293 ppa.payroll_action_id,
1294 ppa.effective_date,
1295 ppa.start_date
1297 pay_payroll_actions ppa,
1298 pay_assignment_actions paa
1299 where
1300 paa.assignment_id = p_assignment_id and
1301 paa.tax_unit_id = p_gre and
1302 ppa.payroll_id = p_payroll_id and
1303 paa.payroll_action_id = ppa.payroll_action_id and
1304 ppa.action_type = 'L' and
1305 ppa.action_status = 'C' and
1306 ppa.start_date between p_start_date and
1307 p_end_date;
1308
1309 cursor cur_curr_pay_period_dates(p_date date) is
1310 select
1311 ptp.start_date,
1312 ptp.end_date
1313 from
1314 per_time_periods ptp
1315 where
1316 ptp.payroll_id = p_payroll_id and
1317 p_date between
1318 ptp.start_date and
1319 ptp.end_date;
1320
1321 CURSOR cur_payroll_exists(p_pay_period_start_date date,
1322 p_pay_period_end_date date) is
1323 select
1324 'X'
1325 from
1326 pay_payroll_actions ppa,
1327 pay_assignment_actions paa
1328 where
1329 ppa.action_status = 'C' and
1330 ppa.action_type in ('Q','R') and
1331 ppa.date_earned between p_pay_period_start_date and
1332 p_pay_period_end_date and
1333 ppa.payroll_action_id = paa.payroll_action_id and
1334 paa.assignment_id = p_assignment_id;
1335
1336
1337 CURSOR cur_ele_entries(p_start_date date,
1338 p_end_date date) IS
1339 select
1340 pet.element_type_id,
1341 pee.element_entry_id,
1342 pee.creator_type,
1343 pee.source_id,
1344 pee.source_asg_action_id,
1345 nvl(pet.element_information3,'DE') element_information3,
1346 peev.screen_entry_value,
1347 pec.classification_name,
1348 pec.classification_id,
1349 pet.element_information1
1350 from
1351 pay_element_entries_f pee,
1352 pay_element_links_f pel,
1353 pay_element_types_f pet,
1354 pay_element_entry_values_f peev,
1355 pay_input_values_f piv,
1356 pay_retro_component_usages prcu,
1357 pay_element_span_usages pesu,
1358 pay_retro_components prc,
1359 pay_time_spans pts,
1360 pay_element_classifications pec
1361 where
1362 pee.assignment_id = p_assignment_id and
1363 pee.creator_type in ('EE','RR') and
1364 pee.effective_start_date <= p_end_date and
1365 pee.effective_end_date >= p_start_date and
1366 pee.element_link_id = pel.element_link_id and
1367 pel.effective_start_date <= p_end_date and
1368 pel.effective_end_date >= p_start_date and
1369 pel.element_type_id = pet.element_type_id and
1370 pet.effective_start_date <= p_end_date and
1371 pet.effective_end_date >= p_start_date and
1372 pet.element_type_id = pesu.retro_element_type_id and
1373 pesu.time_span_id = pts.time_span_id and
1374 pesu.retro_component_usage_id = prcu.retro_component_usage_id and
1375 prcu.retro_component_id = prc.retro_component_id and
1376 prcu.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID') and -- Added for bug 13975570
1377 pts.creator_id = prc.retro_component_id and
1378 prc.legislation_code = 'CA' and
1379 prc.short_name = 'Retropay' and
1380 pee.element_entry_id = peev.element_entry_id and
1381 peev.effective_start_date <= p_end_date and
1382 peev.effective_end_date >= p_start_date and
1383 peev.input_value_id = piv.input_value_id and
1384 piv.element_type_id = pet.element_type_id and
1385 piv.effective_start_date <= p_end_date and
1386 piv.effective_end_date >= p_start_date and
1387 piv.name = 'Pay Value' and
1388 pet.classification_id = pec.classification_id and
1389 pec.classification_name in ('Earnings',
1390 'Supplemental Earnings',
1391 'Taxable Benefits')
1392 -- EXISTS condition added by sneelapa for bug 9766346 and 10236743
1393 and exists
1394 (
1395 select /*+ push_subq no_unnest */ 1 from
1396 pay_element_entries_f pee_inner
1397 where pee_inner.assignment_id=pee.assignment_id
1398 and pee_inner.entry_type = 'E'
1399 and (
1400 ( pee.creator_type = 'EE'
1401 and pee_inner.element_entry_id = pee.source_id
1402 )
1403 )
1404 and pee_inner.element_type_id = prcu.creator_id
1405 union select /*+ push_subq no_unnest */ 1 from
1406 pay_element_entries_f pee_inner,
1407 pay_run_results prr_inner
1408 where pee_inner.assignment_id=pee.assignment_id
1409 and pee_inner.entry_type = 'E'
1410 and (
1411 (pee.creator_type = 'RR'
1412 and pee.source_id = prr_inner.run_result_id
1413 and prr_inner.source_id = pee_inner.element_entry_id
1414 )
1415 )
1416 and pee_inner.element_type_id = prcu.creator_id
1417 );
1418
1419 cursor cur_originating_period_rr_de(p_run_result_id number,
1420 p_start_date date,
1421 p_end_date date) is
1422 select ppa.date_earned
1423 from
1424 pay_run_results prr,
1425 pay_assignment_actions paa,
1426 pay_payroll_actions ppa
1427 where ppa.payroll_action_id = paa.payroll_action_id
1428 and ppa.date_earned between p_start_date
1429 and p_end_date
1433 cursor cur_originating_period_asg_de(p_asg_action_id number,
1430 and prr.assignment_action_id = paa.assignment_action_id
1431 and prr.run_result_id = p_run_result_id;
1432
1434 p_start_date date,
1435 p_end_date date) is
1436 select ppa.date_earned
1437 from
1438 pay_assignment_actions paa,
1439 pay_payroll_actions ppa
1440 where ppa.payroll_action_id = paa.payroll_action_id
1441 and ppa.date_earned between p_start_date
1442 and p_end_date
1443 and paa.assignment_action_id = p_asg_action_id;
1444
1445 l_pay_period_start_date date;
1446 l_pay_period_end_date date;
1447 l_pay_period_st_date date;
1448 l_pay_period_e_date date;
1449 period_from number;
1450 period_to number;
1451 dummy varchar2(1);
1452 l_rehire varchar2(1);
1453 l_next_prd_start_date date;
1454
1455 CURSOR cur_ftr(p_ftr_start_date DATE,
1456 p_ftr_end_date DATE) IS
1457 SELECT
1458 'X'
1459 FROM
1460 pay_ca_emp_fed_tax_info_f
1461 WHERE
1462 assignment_id = p_assignment_id AND
1463 effective_start_date <= p_ftr_end_date AND
1464 effective_end_date >= p_ftr_start_date;
1465
1466 l_ftr_exists BOOLEAN := FALSE;
1467 l_hour_start_date DATE;
1468
1469 BEGIN
1470
1471 hr_utility.set_location('Starting: ' || l_proc_name, 10);
1472 hr_utility.set_location('p_start_date: ' || to_char(p_start_date), 11);
1473 hr_utility.set_location('p_end_date: ' || p_end_date, 12);
1474
1475 /*
1476 * Initialise the output parameters
1477 */
1478 p_total_insurable := 0;
1479 FOR r_index IN 1..53 LOOP
1480 p_period_total(r_index) := 0;
1481 END LOOP;
1482
1483 OPEN csr_get_period_type(p_payroll_id,
1484 p_end_date);
1485 FETCH csr_get_period_type INTO p_period_type,
1486 l_last_period_start_date,
1487 l_last_period_end_date;
1488 CLOSE csr_get_period_type;
1489
1490
1491 hr_utility.set_location('l_last_period_start_date: ' ||
1492 l_last_period_start_date, 13);
1493 hr_utility.set_location(l_proc_name, 20);
1494 IF p_total_type = 'EI Hours' THEN
1495
1496 IF p_period_type = 'Week' OR
1497 p_period_type = 'Bi-Week' OR
1498 p_period_type = 'Lunar Month' THEN
1499 hr_utility.set_location(l_proc_name, 30);
1500 l_start_date := l_last_period_start_date - 364;
1501 ELSIF p_period_type = 'Semi-Month' OR
1502 p_period_type = 'Calendar Month' THEN
1503 hr_utility.set_location(l_proc_name, 40);
1504 l_start_date := ADD_MONTHS(l_last_period_start_date, -12);
1505 END IF;
1506
1507 hr_utility.set_location('EI Hours l_start_date: ' ||
1508 l_start_date, 20);
1509
1510 hr_utility.set_location('EI Hours p_start_date: ' ||
1511 p_start_date, 20);
1512
1513 l_period_start_date_exists := 'N';
1514
1515 IF p_start_date IS NOT NULL AND
1516 p_start_date > l_start_date THEN
1517
1518 open cur_next_prd_start_date;
1519 fetch cur_next_prd_start_date
1520 into l_next_prd_start_date;
1521 close cur_next_prd_start_date;
1522
1523 l_start_date := p_start_date;
1524
1525 hr_utility.trace('l_next_prd_start_date : '||to_char(l_next_prd_start_date));
1526 hr_utility.trace('l_start_date : '||to_char(l_start_date));
1527
1528 -- Check to see if l_start_date is the start date of a period
1529 -- If it is, then we set l_period_start_date_exists to Y
1530 -- this means that the previous ROE date was on the last day
1531 -- of a period
1532
1533 OPEN csr_start_date (p_payroll_id, l_start_date);
1534 FETCH csr_start_date INTO l_period_start_date;
1535 IF csr_start_date%NOTFOUND THEN
1536 CLOSE csr_start_date;
1537 l_period_start_date_exists := 'Y';
1538 --since we are not passing previous ROE Date but the first day worked
1539 --for current ROE, pay period should be included for calculations.
1540 ELSE
1541 l_period_start_date_exists := 'Y';
1542 CLOSE csr_start_date;
1543 END IF;
1544
1545 END IF;
1546
1547 l_rehire := populate_date_lookup_table(p_payroll_id,
1548 p_assignment_id,
1549 l_start_date,
1550 l_last_period_end_date,
1551 l_last_period_start_date);
1552
1553 -- If l_period_start_date_exists is Y then we want to
1554 -- retrieve the hours from the period l_start_date falls in
1555 -- not the next period
1556
1557 IF l_rehire = 'N' AND
1558 l_next_prd_start_date IS NOT NULL AND
1559 l_period_start_date_exists = 'N' THEN
1560
1561 -- If there are no rehires then get the hours
1562 -- starting from the next period
1563
1564 l_hour_start_date := l_next_prd_start_date;
1565
1566 ELSE
1567
1568 -- If rehires exist in the previous roe pay period
1569 -- then get hours starting from l_start_date
1570
1571 l_hour_start_date := l_start_date;
1572
1573 END IF;
1574
1575 hr_utility.trace('EI Hours: l_hour_start_date = '
1576 || to_char(l_hour_start_date));
1577 OPEN cur_ftr(l_hour_start_date,
1578 l_pay_period_end_date);
1579 FETCH cur_ftr
1580 INTO dummy;
1581
1582 IF cur_ftr%NOTFOUND THEN
1583 hr_utility.trace('EI Hours cur_ftr not Found !!!');
1584 l_ftr_exists := FALSE;
1585 ELSE
1586 hr_utility.trace('EI Hours cur_ftr Found !!!');
1587 l_ftr_exists := TRUE;
1588 END IF;
1589
1590 CLOSE cur_ftr;
1591
1592 IF l_ftr_exists THEN
1593
1594 hr_utility.trace('EI Hours l_ftr_exists !!!');
1595 OPEN csr_dp_hours_total_ftr_exists(p_assignment_id,
1596 p_gre,
1597 l_hour_start_date,
1598 l_last_period_end_date);
1599 FETCH csr_dp_hours_total_ftr_exists INTO l_dp_hours_total;
1600 CLOSE csr_dp_hours_total_ftr_exists;
1601
1602 OPEN csr_de_hours_total_ftr_exists(p_assignment_id,
1603 p_gre,
1604 l_hour_start_date,
1605 l_last_period_end_date);
1606 FETCH csr_de_hours_total_ftr_exists INTO l_de_hours_total;
1607 CLOSE csr_de_hours_total_ftr_exists;
1608
1609 ELSE
1610
1611 hr_utility.trace('EI Hours NOT l_ftr_exists !!!');
1612 OPEN csr_dp_hours_total_ftr_nexists(p_assignment_id,
1613 p_gre,
1614 l_hour_start_date,
1615 l_last_period_end_date);
1616 FETCH csr_dp_hours_total_ftr_nexists INTO l_dp_hours_total;
1617 CLOSE csr_dp_hours_total_ftr_nexists;
1618
1619 OPEN csr_de_hours_total_ftr_nexists(p_assignment_id,
1620 p_gre,
1621 l_hour_start_date,
1622 l_last_period_end_date);
1623 FETCH csr_de_hours_total_ftr_nexists INTO l_de_hours_total;
1624 CLOSE csr_de_hours_total_ftr_nexists;
1625
1626 END IF;
1627
1628 hr_utility.trace (' l_dp_hours_total = ' || to_char(l_dp_hours_total));
1629 hr_utility.trace (' l_de_hours_total = ' || to_char(l_de_hours_total));
1630
1631 p_total_insurable := NVL(l_dp_hours_total,0) +
1632 NVL(l_de_hours_total,0);
1633
1634 hr_utility.trace('Total Hours : '|| to_char(p_total_insurable));
1635
1636 RETURN 'BOX15A';
1637
1638 ELSIF p_total_type = 'EI Earnings' THEN
1639 /* Modified the period count for bug 4510534 */
1640 IF p_period_type = 'Week' THEN
1641 hr_utility.set_location(l_proc_name, 50);
1642 l_start_date := l_last_period_start_date - 365;
1643 l_period_count := 53;
1644 l_period_count1 := 27;
1645 ELSIF p_period_type = 'Bi-Week' THEN
1646 hr_utility.set_location(l_proc_name, 60);
1647 l_start_date := l_last_period_start_date - 365;
1648 l_period_count := 27;
1649 l_period_count1 := 14;
1650 ELSIF p_period_type = 'Semi-Month' THEN
1651 hr_utility.set_location(l_proc_name, 70);
1652 l_start_date := ADD_MONTHS(l_last_period_start_date, -12);
1653 l_period_count := 25;
1654 l_period_count1 := 13;
1655 ELSIF p_period_type = 'Calendar Month' THEN
1656 hr_utility.set_location(l_proc_name, 80);
1657 l_start_date := ADD_MONTHS(l_last_period_start_date, -12);
1658 l_period_count := 13;
1659 l_period_count1 := 7;
1660 ELSIF p_period_type = 'Lunar Month' THEN
1661 hr_utility.set_location(l_proc_name, 90);
1662 l_start_date := l_last_period_start_date - 336;
1663 l_period_count := 14;
1664 l_period_count1 := 7;
1665 END IF;
1666
1667 IF p_start_date IS NOT NULL AND
1668 p_start_date > l_start_date THEN
1669
1670 -- commented out because it can cause earnings not to be archived
1671
1672 /* open cur_next_prd_start_date;
1673 fetch cur_next_prd_start_date
1674 into l_start_date;
1675 close cur_next_prd_start_date; */
1676
1677 l_start_date := p_start_date;
1678
1679 END IF;
1680
1681 IF p_term_or_abs_flag = 'Y' THEN
1682
1683 open cur_pay_period_dates(p_end_date);
1684 fetch cur_pay_period_dates
1685 into
1686 l_prd_st_date_after_final,
1687 l_prd_end_date_after_final;
1688 close cur_pay_period_dates;
1689
1690 l_rehire := populate_date_lookup_table(p_payroll_id,
1691 p_assignment_id,
1692 l_start_date,
1693 l_prd_end_date_after_final,
1694 l_prd_st_date_after_final);
1695 hr_utility.trace('p_assignment_id de' || to_char(p_assignment_id));
1696 hr_utility.trace('p_gre de' || to_char(p_gre));
1697 hr_utility.trace('l_start_date de' || to_char(l_start_date));
1698 hr_utility.trace('l_prd_end_date_after_final de' || to_char(l_prd_end_date_after_final));
1699
1700
1701
1702 /* Modified the code to add aadditional amounts 28 to 53 for bug4510534 */
1703 OPEN csr_get_de_total(p_assignment_id,
1704 p_gre,
1705 l_start_date,l_prd_end_date_after_final);
1706
1707 FETCH csr_get_de_total INTO l_de_total(1),
1708 l_de_total(2),
1709 l_de_total(3),
1710 l_de_total(4),
1711 l_de_total(5),
1712 l_de_total(6),
1713 l_de_total(7),
1714 l_de_total(8),
1715 l_de_total(9),
1716 l_de_total(10),
1717 l_de_total(11),
1718 l_de_total(12),
1719 l_de_total(13),
1720 l_de_total(14),
1721 l_de_total(15),
1722 l_de_total(16),
1723 l_de_total(17),
1724 l_de_total(18),
1725 l_de_total(19),
1726 l_de_total(20),
1727 l_de_total(21),
1728 l_de_total(22),
1729 l_de_total(23),
1730 l_de_total(24),
1731 l_de_total(25),
1732 l_de_total(26),
1733 l_de_total(27),
1734 l_de_total(28),
1735 l_de_total(29),
1736 l_de_total(30),
1737 l_de_total(31),
1738 l_de_total(32),
1739 l_de_total(33),
1740 l_de_total(34),
1741 l_de_total(35),
1742 l_de_total(36),
1743 l_de_total(37),
1744 l_de_total(38),
1745 l_de_total(39),
1746 l_de_total(40),
1747 l_de_total(41),
1748 l_de_total(42),
1749 l_de_total(43),
1750 l_de_total(44),
1751 l_de_total(45),
1752 l_de_total(46),
1753 l_de_total(47),
1754 l_de_total(48),
1755 l_de_total(49),
1756 l_de_total(50),
1757 l_de_total(51),
1758 l_de_total(52),
1759 l_de_total(53) ;
1760 CLOSE csr_get_de_total;
1761
1762 hr_utility.trace('p_assignment_id dp' || to_char(p_assignment_id));
1763 hr_utility.trace('p_gre dp' || to_char(p_gre));
1764 hr_utility.trace('l_start_date dp' || to_char(l_start_date));
1765 hr_utility.trace('l_prd_end_date_after_final dp' || to_char(l_prd_end_date_after_final));
1766
1767
1768
1769 OPEN csr_get_dp_total(p_assignment_id,
1770 p_gre,
1771 l_start_date,
1772 l_prd_end_date_after_final);
1773
1774 FETCH csr_get_dp_total INTO l_dp_total(1),
1775 l_dp_total(2),
1776 l_dp_total(3),
1777 l_dp_total(4),
1778 l_dp_total(5),
1779 l_dp_total(6),
1780 l_dp_total(7),
1781 l_dp_total(8),
1782 l_dp_total(9),
1783 l_dp_total(10),
1784 l_dp_total(11),
1785 l_dp_total(12),
1786 l_dp_total(13),
1787 l_dp_total(14),
1788 l_dp_total(15),
1789 l_dp_total(16),
1790 l_dp_total(17),
1791 l_dp_total(18),
1792 l_dp_total(19),
1793 l_dp_total(20),
1794 l_dp_total(21),
1795 l_dp_total(22),
1796 l_dp_total(23),
1797 l_dp_total(24),
1798 l_dp_total(25),
1799 l_dp_total(26),
1800 l_dp_total(27),
1801 l_dp_total(28),
1802 l_dp_total(29),
1803 l_dp_total(30),
1804 l_dp_total(31),
1805 l_dp_total(32),
1806 l_dp_total(33),
1807 l_dp_total(34),
1808 l_dp_total(35),
1809 l_dp_total(36),
1810 l_dp_total(37),
1811 l_dp_total(38),
1812 l_dp_total(39),
1813 l_dp_total(40),
1814 l_dp_total(41),
1815 l_dp_total(42),
1816 l_dp_total(43),
1817 l_dp_total(44),
1818 l_dp_total(45),
1819 l_dp_total(46),
1820 l_dp_total(47),
1821 l_dp_total(48),
1822 l_dp_total(49),
1823 l_dp_total(50),
1824 l_dp_total(51),
1825 l_dp_total(52),
1826 l_dp_total(53);
1827 CLOSE csr_get_dp_total;
1828
1829
1830 -- Must reset pay periods so that periods after termination
1831 -- are ignored for retro processing purposes
1832
1833 l_rehire := populate_date_lookup_table(p_payroll_id,
1834 p_assignment_id,
1835 l_start_date,
1836 l_last_period_end_date,
1837 l_last_period_start_date);
1838 ELSE
1839
1840 hr_utility.trace('p_assignment_id de1= ' || to_char(p_assignment_id));
1841 hr_utility.trace('p_gre de1= ' || to_char(p_gre));
1842 hr_utility.trace('l_start_date de1= ' || to_char(l_start_date));
1843 hr_utility.trace('l_last_period_end_date de1= '
1844 || to_char(l_last_period_end_date));
1845
1846 OPEN csr_get_de_total1(p_assignment_id,
1847 p_gre,
1848 l_start_date,l_last_period_end_date);
1849
1850 FETCH csr_get_de_total1 INTO l_de_total(1),
1851 l_de_total(2),
1852 l_de_total(3),
1853 l_de_total(4),
1854 l_de_total(5),
1855 l_de_total(6),
1856 l_de_total(7),
1857 l_de_total(8),
1858 l_de_total(9),
1859 l_de_total(10),
1860 l_de_total(11),
1861 l_de_total(12),
1862 l_de_total(13),
1863 l_de_total(14),
1864 l_de_total(15),
1865 l_de_total(16),
1866 l_de_total(17),
1867 l_de_total(18),
1868 l_de_total(19),
1869 l_de_total(20),
1870 l_de_total(21),
1871 l_de_total(22),
1872 l_de_total(23),
1873 l_de_total(24),
1874 l_de_total(25),
1875 l_de_total(26),
1876 l_de_total(27),
1877 l_de_total(28),
1878 l_de_total(29),
1879 l_de_total(30),
1880 l_de_total(31),
1881 l_de_total(32),
1882 l_de_total(33),
1883 l_de_total(34),
1884 l_de_total(35),
1885 l_de_total(36),
1886 l_de_total(37),
1887 l_de_total(38),
1888 l_de_total(39),
1889 l_de_total(40),
1890 l_de_total(41),
1891 l_de_total(42),
1892 l_de_total(43),
1893 l_de_total(44),
1894 l_de_total(45),
1895 l_de_total(46),
1896 l_de_total(47),
1897 l_de_total(48),
1898 l_de_total(49),
1899 l_de_total(50),
1900 l_de_total(51),
1901 l_de_total(52),
1902 l_de_total(53) ;
1903 CLOSE csr_get_de_total1;
1904 hr_utility.trace('p_assignment_id dp1= ' || to_char(p_assignment_id));
1905 hr_utility.trace('p_gre dp1= ' || to_char(p_gre));
1906 hr_utility.trace('l_start_date dp1= ' || to_char(l_start_date));
1907 hr_utility.trace('l_last_period_end_date dp1= '
1908 || to_char(l_last_period_end_date));
1909
1910 OPEN csr_get_dp_total1(p_assignment_id,
1911 p_gre,
1912 l_start_date,
1913 l_last_period_end_date);
1914 FETCH csr_get_dp_total1 INTO l_dp_total(1),
1915 l_dp_total(2),
1916 l_dp_total(3),
1917 l_dp_total(4),
1918 l_dp_total(5),
1919 l_dp_total(6),
1920 l_dp_total(7),
1921 l_dp_total(8),
1922 l_dp_total(9),
1923 l_dp_total(10),
1924 l_dp_total(11),
1925 l_dp_total(12),
1926 l_dp_total(13),
1927 l_dp_total(14),
1928 l_dp_total(15),
1929 l_dp_total(16),
1930 l_dp_total(17),
1931 l_dp_total(18),
1932 l_dp_total(19),
1933 l_dp_total(20),
1934 l_dp_total(21),
1935 l_dp_total(22),
1936 l_dp_total(23),
1937 l_dp_total(24),
1938 l_dp_total(25),
1939 l_dp_total(26),
1940 l_dp_total(27),
1941 l_dp_total(28),
1942 l_dp_total(29),
1943 l_dp_total(30),
1944 l_dp_total(31),
1945 l_dp_total(32),
1946 l_dp_total(33),
1947 l_dp_total(34),
1948 l_dp_total(35),
1949 l_dp_total(36),
1950 l_dp_total(37),
1951 l_dp_total(38),
1952 l_dp_total(39),
1953 l_dp_total(40),
1954 l_dp_total(41),
1955 l_dp_total(42),
1956 l_dp_total(43),
1957 l_dp_total(44),
1958 l_dp_total(45),
1959 l_dp_total(46),
1960 l_dp_total(47),
1961 l_dp_total(48),
1962 l_dp_total(49),
1963 l_dp_total(50),
1964 l_dp_total(51),
1965 l_dp_total(52),
1966 l_dp_total(53);
1967 CLOSE csr_get_dp_total1;
1968
1969 END IF;
1970
1971 hr_utility.trace('l_de_total(1) = ' || to_char(l_de_total(1)));
1972 hr_utility.trace('l_de_total(2) = ' || to_char(l_de_total(2)));
1973 hr_utility.trace('l_de_total(3) = ' || to_char(l_de_total(3)));
1974 hr_utility.trace('l_de_total(4) = ' || to_char(l_de_total(4)));
1975 hr_utility.trace('l_de_total(5) = ' || to_char(l_de_total(5)));
1976 hr_utility.trace('l_de_total(6) = ' || to_char(l_de_total(6)));
1977 hr_utility.trace('l_de_total(7) = ' || to_char(l_de_total(7)));
1978 hr_utility.trace('l_de_total(8) = ' || to_char(l_de_total(8)));
1979 hr_utility.trace('l_de_total(9) = ' || to_char(l_de_total(9)));
1980 hr_utility.trace('l_de_total(10) = ' || to_char(l_de_total(10)));
1981 hr_utility.trace('l_de_total(11) = ' || to_char(l_de_total(11)));
1982 hr_utility.trace('l_de_total(12) = ' || to_char(l_de_total(12)));
1983 hr_utility.trace('l_de_total(13) = ' || to_char(l_de_total(13)));
1984 hr_utility.trace('l_de_total(14) = ' || to_char(l_de_total(14)));
1985 hr_utility.trace('l_de_total(15) = ' || to_char(l_de_total(15)));
1986 hr_utility.trace('l_de_total(16) = ' || to_char(l_de_total(16)));
1987 hr_utility.trace('l_de_total(17) = ' || to_char(l_de_total(17)));
1988 hr_utility.trace('l_de_total(18) = ' || to_char(l_de_total(18)));
1989 hr_utility.trace('l_de_total(19) = ' || to_char(l_de_total(19)));
1990 hr_utility.trace('l_de_total(20) = ' || to_char(l_de_total(20)));
1991 hr_utility.trace('l_de_total(21) = ' || to_char(l_de_total(21)));
1992 hr_utility.trace('l_de_total(22) = ' || to_char(l_de_total(22)));
1993 hr_utility.trace('l_de_total(23) = ' || to_char(l_de_total(23)));
1994 hr_utility.trace('l_de_total(24) = ' || to_char(l_de_total(24)));
1995 hr_utility.trace('l_de_total(25) = ' || to_char(l_de_total(25)));
1996 hr_utility.trace('l_de_total(26) = ' || to_char(l_de_total(26)));
1997 hr_utility.trace('l_de_total(27) = ' || to_char(l_de_total(27)));
1998
1999
2000
2001 hr_utility.trace('l_dp_total(1) = ' || to_char(l_dp_total(1)));
2002 hr_utility.trace('l_dp_total(2) = ' || to_char(l_dp_total(2)));
2003 hr_utility.trace('l_dp_total(3) = ' || to_char(l_dp_total(3)));
2004 hr_utility.trace('l_dp_total(4) = ' || to_char(l_dp_total(4)));
2005 hr_utility.trace('l_dp_total(5) = ' || to_char(l_dp_total(5)));
2006 hr_utility.trace('l_dp_total(6) = ' || to_char(l_dp_total(6)));
2007 hr_utility.trace('l_dp_total(7) = ' || to_char(l_dp_total(7)));
2008 hr_utility.trace('l_dp_total(8) = ' || to_char(l_dp_total(8)));
2009 hr_utility.trace('l_dp_total(9) = ' || to_char(l_dp_total(9)));
2010 hr_utility.trace('l_dp_total(10) = ' || to_char(l_dp_total(10)));
2011 hr_utility.trace('l_dp_total(11) = ' || to_char(l_dp_total(11)));
2012 hr_utility.trace('l_dp_total(12) = ' || to_char(l_dp_total(12)));
2013 hr_utility.trace('l_dp_total(13) = ' || to_char(l_dp_total(13)));
2014 hr_utility.trace('l_dp_total(14) = ' || to_char(l_dp_total(14)));
2015 hr_utility.trace('l_dp_total(15) = ' || to_char(l_dp_total(15)));
2016 hr_utility.trace('l_dp_total(16) = ' || to_char(l_dp_total(16)));
2017 hr_utility.trace('l_dp_total(17) = ' || to_char(l_dp_total(17)));
2018 hr_utility.trace('l_dp_total(18) = ' || to_char(l_dp_total(18)));
2019 hr_utility.trace('l_dp_total(19) = ' || to_char(l_dp_total(19)));
2020 hr_utility.trace('l_dp_total(20) = ' || to_char(l_dp_total(20)));
2021 hr_utility.trace('l_dp_total(21) = ' || to_char(l_dp_total(21)));
2022 hr_utility.trace('l_dp_total(22) = ' || to_char(l_dp_total(22)));
2023 hr_utility.trace('l_dp_total(23) = ' || to_char(l_dp_total(23)));
2024 hr_utility.trace('l_dp_total(24) = ' || to_char(l_dp_total(24)));
2025 hr_utility.trace('l_dp_total(25) = ' || to_char(l_dp_total(25)));
2026 hr_utility.trace('l_dp_total(26) = ' || to_char(l_dp_total(26)));
2027 hr_utility.trace('l_dp_total(27) = ' || to_char(l_dp_total(27)));
2028
2029
2030 hr_utility.set_location(l_proc_name, 100);
2031
2032 -- If the hire date is later than either the
2033 -- previous roe date (p_start_date) or the
2034 -- starting date for the current roe then hire
2035 -- date should be used for calculating the
2036 -- number of pay periods
2037
2038 open cur_date_of_hire;
2039 fetch cur_date_of_hire
2040 into l_hire_date;
2041 close cur_date_of_hire;
2042
2043 hr_utility.trace('l_hire_date = ' || to_char(l_hire_date));
2044
2045 if p_start_date is not null and
2046 p_start_date > l_start_date then
2047
2048 hr_utility.trace('p_start_date = ' || to_char(p_start_date));
2049
2050 if l_hire_date > p_start_date then
2051 l_start_date1 := l_hire_date;
2052 else
2053 l_start_date1 := p_start_date;
2054 end if;
2055
2056 else
2057
2058 hr_utility.trace('l_start_date = ' || to_char(l_start_date));
2059
2060 if l_hire_date > l_start_date then
2061 l_start_date1 := l_hire_date;
2062 else
2063 l_start_date1 := l_start_date;
2064 end if;
2065
2066 end if;
2067
2068 hr_utility.trace('l_start_date1 = ' || to_char(l_start_date1));
2069
2070 OPEN cur_count_pay_periods(l_start_date1);
2071 FETCH cur_count_pay_periods
2072 INTO l_no_of_pay_periods;
2073 CLOSE cur_count_pay_periods;
2074
2075 hr_utility.trace('l_no_of_pay_periods = ' || to_char(l_no_of_pay_periods));
2076 hr_utility.trace('l_period_count = ' || to_char(l_period_count));
2077
2078 FOR l_index IN 1..l_period_count LOOP
2079
2080 p_period_total(l_index) := l_dp_total(l_index) + l_de_total(l_index);
2081
2082 hr_utility.trace('l_dp_total = ' || to_char(l_dp_total(l_index)));
2083 hr_utility.trace('l_de_total = ' || to_char(l_de_total(l_index)));
2084 hr_utility.trace('l_index = ' || to_char(l_index));
2085
2086 IF p_period_total(l_index) = 0 and
2087 l_index <= l_no_of_pay_periods THEN
2088 l_box15c_flag := TRUE;
2089 END IF;
2090 -- Commented below code for bug 8835569
2091 /*if l_index <= l_period_count1 then
2092 p_total_insurable := p_total_insurable + p_period_total(l_index);
2093 end if;*/
2094
2095 END LOOP;
2096
2097 p_no_of_periods := l_period_count;
2098
2099 -- Retro Functionality starts here
2100
2101 hr_utility.trace('Retro Functionality starts here');
2102
2103 for i in cur_retro_run(l_start_date,
2104 l_last_period_end_date) loop
2105
2106 hr_utility.trace('cur_retro_run found');
2107 hr_utility.trace('i.effective_date = ' || to_char(i.effective_date));
2108 hr_utility.trace('i.start_date = ' || to_char(i.start_date));
2109
2110 open cur_curr_pay_period_dates(i.effective_date);
2111 fetch cur_curr_pay_period_dates
2112 into l_pay_period_start_date,
2113 l_pay_period_end_date;
2114 close cur_curr_pay_period_dates;
2115
2116 hr_utility.trace('l_pay_period_start_date = ' || to_char(l_pay_period_start_date));
2117 hr_utility.trace('l_pay_period_end_date = ' || to_char(l_pay_period_end_date));
2118
2119 open cur_payroll_exists(l_pay_period_start_date,
2120 l_pay_period_end_date);
2121 fetch cur_payroll_exists
2122 into dummy;
2123 if cur_payroll_exists%NOTFOUND then
2124 close cur_payroll_exists;
2125 else
2126 close cur_payroll_exists;
2127
2128 for k in cur_ele_entries(l_pay_period_start_date,
2129 l_pay_period_end_date) loop
2130
2131 hr_utility.trace('k.element_type_id = ' || to_char(k.element_type_id));
2132 hr_utility.trace('k.creator_type = ' || k.creator_type);
2133 hr_utility.trace('k.source_id = ' || to_char(nvl(k.source_id,0)));
2134 hr_utility.trace('k.source_asg_action_id = ' || to_char(nvl(k.source_asg_action_id,0)));
2135 hr_utility.trace('k.screen_entry_value = ' || nvl(k.screen_entry_value,'0'));
2136 hr_utility.trace('k.element_entry_id = ' || to_char(k.element_entry_id));
2137 hr_utility.trace('k.classification_name = ' || nvl(k.classification_name,' '));
2138 hr_utility.trace('k.classification_id = ' || to_char(nvl(k.classification_id,0)));
2139 hr_utility.trace('k.element_information1 = ' || k.element_information1);
2140
2141 if (((k.element_entry_id <> l_prev_element_entry_id) or
2142 (l_prev_element_entry_id is null)) and
2143 (taxability_rule_exists(k.classification_name,
2144 k.classification_id,
2145 k.element_information1,
2146 l_pay_period_end_date,
2147 'EIM') = 'TRUE')) then
2148
2149 l_value := to_number(k.screen_entry_value);
2150 hr_utility.trace('l_value '||l_value);
2151
2152 if (k.element_information3 = 'DE') then
2153
2154 if k.creator_type = 'RR' then
2155
2156 open cur_originating_period_rr_de(k.source_id,
2157 l_start_date,
2158 l_last_period_end_date);
2159 fetch cur_originating_period_rr_de into l_start_period;
2160 close cur_originating_period_rr_de;
2161
2162 else
2163
2164 open cur_originating_period_asg_de(k.source_asg_action_id,
2165 l_start_date,
2166 l_last_period_end_date);
2167 fetch cur_originating_period_asg_de into l_start_period;
2168 close cur_originating_period_asg_de;
2169
2170 end if;
2171
2172 else /* Retro element is Date Paid */
2173
2174 l_start_period := null;
2175
2176 end if;
2177
2178 period_from := pay_ca_roe_ei_pkg.get_pd_num(i.effective_date,
2179 l_last_period_end_date);
2180
2181 if (l_start_period is not null and
2182 l_value is not null) then
2183
2184 period_to := pay_ca_roe_ei_pkg.get_pd_num(l_start_period,
2185 l_last_period_end_date);
2186
2187 hr_utility.trace('period_from = ' || to_char(period_from));
2188 hr_utility.trace('period_to = ' || to_char(period_to));
2189
2190 p_period_total(period_from) := p_period_total(period_from) - l_value;
2191 p_period_total(period_to) := p_period_total(period_to) + l_value;
2192 hr_utility.trace('p_period_total(period_from) '||p_period_total(period_from));
2193 hr_utility.trace('p_period_total(period_to) '||p_period_total(period_to));
2194
2195 end if;
2196
2197 end if; -- prev element entry id
2198
2199 l_prev_element_entry_id := k.element_entry_id;
2200
2201 end loop;
2202
2203 end if;
2204
2205 end loop;
2206
2207 -- Retro Functionality ends here;
2208 hr_utility.set_location(l_proc_name, 120);
2209
2210 -- Summing 15B, Total insurable earnings, bug 8835569
2211 for l_index in 1..l_period_count1 loop
2212 p_total_insurable := p_total_insurable + p_period_total(l_index);
2213 end loop;
2214
2215 IF l_box15c_flag THEN
2216 hr_utility.set_location('Ending: ' || l_proc_name, 130);
2217 RETURN 'BOX15C';
2218 ELSE
2219 hr_utility.set_location('Ending: ' || l_proc_name, 140);
2220 RETURN 'BOX15B';
2221 END IF;
2222
2223 END IF;
2224
2225 END get_ei_amount_totals;
2226
2227
2228 -------------------------------------------------------------------------------
2229 -- Name: populate_element_table
2230 --
2231 -- Parameters:
2232 --
2233 -- Description: This procedure creates element tables that would subsequently be
2234 -- be used to determine if date paid or date earned should be used
2235 -- to calculate the balance totals for the ROE report.
2236 -- We need to check Special Features element because 'EI Horus' are
2237 -- stored on the Special Features element.
2238 -------------------------------------------------------------------------------
2239 PROCEDURE populate_element_table(p_bg_id number) IS
2240
2241 CURSOR cur_bal_type_id IS
2242 SELECT
2243 pbt.balance_type_id
2244 FROM
2245 pay_balance_types pbt
2246 WHERE
2247 pbt.balance_name = 'EI Hours' and
2248 pbt.legislation_code = 'CA';
2249
2250 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
2251
2252 /* CURSOR csr_get_element_id (p_dp_or_de VARCHAR2)
2253 IS
2254 SELECT DISTINCT ele.element_type_id
2255 FROM pay_element_types_f ele,
2256 pay_template_core_objects tco1,
2257 pay_shadow_element_types sel,
2258 pay_element_templates etp,
2259 pay_template_core_objects tco2
2260 WHERE tco2.core_object_type = 'ET'
2261 AND etp.template_id = tco2.template_id
2262 AND sel.template_id = etp.template_id
2263 AND sel.element_name NOT LIKE ('%Special Inputs')
2264 AND sel.element_type_id = tco1.shadow_object_id
2265 AND tco1.core_object_type = 'ET'
2266 AND ele.element_type_id = tco1.core_object_id
2267 AND NVL(ele.element_information3,'DP') = p_dp_or_de
2268 UNION ALL
2269 SELECT DISTINCT ele.element_type_id
2270 FROM pay_element_types_f ele,
2271 pay_element_classifications pec
2272 WHERE ele.business_group_id is NULL
2273 AND ele.legislation_code = 'CA'
2274 AND pec.legislation_code = 'CA'
2275 AND pec.classification_name = 'Earnings'
2276 AND ele.classification_id = pec.classification_id
2277 AND p_dp_or_de = 'DE'; */
2278
2279 CURSOR csr_get_element_id (p_dp_or_de VARCHAR2) IS
2280 SELECT
2281 pet.element_type_id
2282 FROM
2283 pay_element_types_f pet
2284 WHERE
2285 pet.business_group_id = p_bg_id and
2286 NVL(pet.element_information3,'DP') = p_dp_or_de and
2287 EXISTS
2288 (SELECT 'X' FROM
2289 pay_input_values_f piv,
2290 pay_balance_feeds_f pbf
2291 WHERE
2292 piv.element_type_id = pet.element_type_id AND
2293 piv.input_value_id = pbf.input_value_id AND
2294 pbf.balance_type_id = l_bal_type_id)
2295 UNION ALL
2296 SELECT DISTINCT ele.element_type_id
2297 FROM pay_element_types_f ele,
2298 pay_element_classifications pec
2299 WHERE ele.business_group_id is NULL
2300 AND ele.legislation_code = 'CA'
2301 AND pec.legislation_code = 'CA'
2302 AND pec.classification_name = 'Earnings'
2303 AND ele.classification_id = pec.classification_id
2304 AND p_dp_or_de = 'DE';
2305
2306 de_element NUMBER;
2307 dp_element NUMBER;
2308
2309 BEGIN
2310
2311 OPEN cur_bal_type_id;
2312 FETCH cur_bal_type_id
2313 INTO l_bal_type_id;
2314 CLOSE cur_bal_type_id;
2315
2316 OPEN csr_get_element_id ('DE');
2317 LOOP
2318 FETCH csr_get_element_id
2319 INTO de_element;
2320 EXIT WHEN csr_get_element_id%NOTFOUND;
2321
2322 de_element_table(de_element).element_id := de_element;
2323
2324 END LOOP;
2325
2326 CLOSE csr_get_element_id;
2327
2328 OPEN csr_get_element_id ('DP');
2329 LOOP
2330 FETCH csr_get_element_id
2331 INTO dp_element;
2332 EXIT WHEN csr_get_element_id%NOTFOUND;
2333
2334 dp_element_table(dp_element).element_id := dp_element;
2335
2336 END LOOP;
2337
2338 CLOSE csr_get_element_id;
2339
2340 END populate_element_table;
2341
2342 -------------------------------------------------------------------------------
2343 -- Name: date_paid_or_date_earned
2344 --
2345 -- Parameters: p_element_type_id
2346 -- p_dp_or_de
2347 -- p_ele_info3
2348 --
2349 -- Return: VARCHAR2 - 'TRUE' or 'FALSE'
2350 --
2351 -- Description: This function determines whether we should use date paid or date
2352 -- earned to calculate the balance totals for the ROE report.
2353 -------------------------------------------------------------------------------
2354 FUNCTION date_paid_or_date_earned
2355 (p_element_type_id NUMBER,
2356 p_dp_or_de VARCHAR2,
2357 p_ele_info3 VARCHAR2)
2358 RETURN VARCHAR2 IS
2359
2360 BEGIN
2361
2362 IF p_ele_info3 IN ('DP', 'DE') THEN
2363 IF p_ele_info3 = p_dp_or_de THEN
2364 RETURN 'TRUE';
2365 ELSE
2366 RETURN 'FALSE';
2367 END IF;
2368 ELSE
2369 IF p_dp_or_de = 'DE' THEN
2370 IF de_element_table.EXISTS(p_element_type_id) THEN
2371 RETURN 'TRUE';
2372 ELSE
2373 RETURN 'FALSE';
2374 END IF;
2375 ELSE
2376 IF dp_element_table.EXISTS(p_element_type_id) THEN
2377 RETURN 'TRUE';
2378 ELSE
2379 RETURN 'FALSE';
2380 END IF;
2381 END IF;
2382 END IF;
2383
2384 END date_paid_or_date_earned;
2385
2386 END pay_ca_roe_ei_pkg;