[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EMP_TAX_INF
Source
1 PACKAGE BODY PAY_CA_EMP_TAX_INF as
2 /* $Header: pycantax.pkb 120.10.12020000.4 2012/09/06 06:40:18 sbachu ship $ */
3
4
5 procedure get_province_code (p_assignment_id in number,
6 p_session_date in date,
7 p_res_province_code out nocopy varchar2,
8 p_res_province_name out nocopy varchar2,
9 p_work_province_code out nocopy varchar2,
10 p_work_province_name out nocopy varchar2,
11 p_res_inf_flag in varchar2,
12 p_work_inf_flag in varchar2
13
14 ) is
15
16 /* Cursor to get the resident state, county and city codes */
17 cursor csr_get_resident_province is
18 select lkp.lookup_code,
19 lkp.meaning
20 from PER_ASSIGNMENTS_F paf,
21 PER_ADDRESSES pa,
22 HR_LOOKUPS lkp
23 where paf.assignment_id = p_assignment_id
24 and p_session_date between paf.effective_start_date and
25 paf.effective_end_date
26 and pa.person_id = paf.person_id
27 and pa.primary_flag = 'Y'
28 and p_session_date between pa.date_from and
29 nvl(pa.date_to,p_session_date)
30 and lkp.lookup_code = pa.region_1
31 and lkp.lookup_type = 'CA_PROVINCE';
32
33 cursor csr_get_work_province is
34 select lkp.lookup_code,
35 lkp.meaning
36 from PER_ASSIGNMENTS_F paf,
37 HR_LOCATIONS hrl,
38 HR_LOOKUPS lkp
39 where paf.assignment_id = p_assignment_id
40 and p_session_date between paf.effective_start_date and
41 paf.effective_end_date
42 and paf.location_id = hrl.location_id
43 and lkp.lookup_code = hrl.region_1
44 and lkp.lookup_type = 'CA_PROVINCE';
45 begin
46
47 hr_utility.set_location('pay_ca_emp_tax_inf.get_work_provinces',1);
48
49 /* Get the resident address details */
50
51 open csr_get_resident_province;
52
53 fetch csr_get_resident_province into p_res_province_code,
54 p_res_province_name;
55
56 if csr_get_resident_province%NOTFOUND then
57
58 p_res_province_code := null;
59 p_res_province_name := null;
60
61 end if;
62
63 hr_utility.set_location('get_province_code',2);
64
65 close csr_get_resident_province;
66
67 /* Get the work location details */
68
69 open csr_get_work_province;
70
71 fetch csr_get_work_province into p_work_province_code,
72 p_work_province_name;
73
74 if csr_get_work_province%NOTFOUND then
75
76 p_work_province_code := null;
77 p_work_province_name := null;
78
79 end if;
80
81 hr_utility.set_location('pay_ca_emp_tax_inf.get_work_provinces',3);
82
83 close csr_get_work_province;
84 end get_province_code;
85
86
87 procedure create_default_tax_record
88 (p_assignment_id in number,
89 p_effective_start_date out nocopy date,
90 p_effective_end_date out nocopy date,
91 p_effective_date in date,
92 p_business_group_id in number,
93 p_legislation_code in varchar2,
94 p_work_province in varchar2,
95 p_ret_code out nocopy number,
96 p_ret_text out nocopy varchar2)
97 is
98 l_emp_fed_tax_inf_id number;
99 l_emp_province_tax_inf_id number;
100 l_assignment_id number;
101 l_business_group_id number;
102 l_effective_start_date date;
103 l_effective_end_date date;
104 l_effective_date date;
105 l_object_version_number number;
106 l_legislation_code varchar2(30);
107 l_province_code varchar2(30);
108
109 /* Before creating the default tax information for the person following things
110 should be checked:
111 1)The person has a primary address
112 2)The assignmnet has a salary basis, location address, GRE information and
113 Payroll name.
114 3)The default tax record is present is already present in the table or not.
115 If the default tax record is already present in the system it will not be an
116 'INSERT', it will be an 'UPDATE' with proper datetracking
117 */
118
119 /* get_province_codes(parameter-list) */
120 begin
121 l_assignment_id := p_assignment_id;
122 l_legislation_code := p_legislation_code;
123 l_business_group_id := p_business_group_id;
124 pay_ca_emp_fedtax_inf_api.create_ca_emp_fedtax_inf(
125 p_validate => false
126 ,p_emp_fed_tax_inf_id => l_emp_fed_tax_inf_id
127 ,p_effective_start_date => l_effective_start_date
128 ,p_effective_end_date => l_effective_end_date
129 ,p_legislation_code => l_legislation_code
130 ,p_assignment_id => l_assignment_id
131 ,p_business_group_id => l_business_group_id
132 ,p_employment_province => NULL
133 ,p_tax_credit_amount => NULL
134 ,p_claim_code => NULL
135 ,p_basic_exemption_flag => 'Y'
136 ,p_additional_tax => 0
137 ,p_annual_dedn => 0
138 ,p_total_expense_by_commission => 0
139 ,p_total_remnrtn_by_commission => 0
140 ,p_prescribed_zone_dedn_amt => 0
141 ,p_other_fedtax_credits => NULL
142 ,p_cpp_qpp_exempt_flag => 'N'
143 ,p_fed_exempt_flag => 'N'
144 ,p_ei_exempt_flag => 'N'
145 ,p_tax_calc_method => NULL
146 ,p_fed_override_amount => 0
147 ,p_fed_override_rate => 0
148 ,p_ca_tax_information_category => NULL
149 ,p_ca_tax_information1 => NULL
150 ,p_ca_tax_information2 => NULL
151 ,p_ca_tax_information3 => NULL
152 ,p_ca_tax_information4 => NULL
153 ,p_ca_tax_information5 => NULL
154 ,p_ca_tax_information6 => NULL
155 ,p_ca_tax_information7 => NULL
156 ,p_ca_tax_information8 => NULL
157 ,p_ca_tax_information9 => NULL
158 ,p_ca_tax_information10 => NULL
159 ,p_ca_tax_information11 => NULL
160 ,p_ca_tax_information12 => NULL
161 ,p_ca_tax_information13 => NULL
162 ,p_ca_tax_information14 => NULL
163 ,p_ca_tax_information15 => NULL
164 ,p_ca_tax_information16 => NULL
165 ,p_ca_tax_information17 => NULL
166 ,p_ca_tax_information18 => NULL
167 ,p_ca_tax_information19 => NULL
168 ,p_ca_tax_information20 => NULL
169 ,p_ca_tax_information21 => NULL
170 ,p_ca_tax_information22 => NULL
171 ,p_ca_tax_information23 => NULL
172 ,p_ca_tax_information24 => NULL
173 ,p_ca_tax_information25 => NULL
174 ,p_ca_tax_information26 => NULL
175 ,p_ca_tax_information27 => NULL
176 ,p_ca_tax_information28 => NULL
177 ,p_ca_tax_information29 => NULL
178 ,p_ca_tax_information30 => NULL
179 ,p_object_version_number => l_object_version_number
180 ,p_fed_lsf_amount => 0
181 ,p_cpp_election_date => NULL --For Bug Number 13258136
182 ,p_cpp_revocation_date => NULL --For Bug Number 13258136
183 ,p_effective_date => p_effective_date
184 ) ;
185 pay_ca_emp_prvtax_inf_api.create_ca_emp_prvtax_inf
186 (p_validate => false
187 ,p_emp_province_tax_inf_id => l_emp_province_tax_inf_id
188 ,p_effective_start_date => l_effective_start_date
189 ,p_effective_end_date => l_effective_end_date
190 ,p_legislation_code => l_legislation_code
191 ,p_assignment_id => l_assignment_id
192 ,p_business_group_id => l_business_group_id
193 ,p_province_code => p_work_province
194 ,p_jurisdiction_code => NULL
195 ,p_tax_credit_amount => NULL
196 ,p_basic_exemption_flag => 'Y'
197 ,p_deduction_code => NULL
198 ,p_extra_info_not_provided => 'Y'
199 ,p_marriage_status => 'N'
200 ,p_no_of_infirm_dependants => 0
201 ,p_non_resident_status => 'N'
202 ,p_disability_status => 'N'
203 ,p_no_of_dependants => 0
204 ,p_annual_dedn => 0
205 ,p_total_expense_by_commission => 0
206 ,p_total_remnrtn_by_commission => 0
207 ,p_prescribed_zone_dedn_amt => 0
208 ,p_additional_tax => 0
209 ,p_prov_override_rate => 0
210 ,p_prov_override_amount => 0
211 ,p_prov_exempt_flag => 'N'
212 ,p_pmed_exempt_flag => 'N'
213 ,p_wc_exempt_flag => 'N'
214 ,p_qpp_exempt_flag => 'N'
215 ,p_tax_calc_method => NULL
216 ,p_other_tax_credit => 0
217 ,p_ca_tax_information_category => NULL
218 ,p_ca_tax_information1 => NULL
219 ,p_ca_tax_information2 => NULL
220 ,p_ca_tax_information3 => NULL
221 ,p_ca_tax_information4 => NULL
222 ,p_ca_tax_information5 => NULL
223 ,p_ca_tax_information6 => NULL
224 ,p_ca_tax_information7 => NULL
225 ,p_ca_tax_information8 => NULL
226 ,p_ca_tax_information9 => NULL
227 ,p_ca_tax_information10 => NULL
228 ,p_ca_tax_information11 => NULL
229 ,p_ca_tax_information12 => NULL
230 ,p_ca_tax_information13 => NULL
231 ,p_ca_tax_information14 => NULL
232 ,p_ca_tax_information15 => NULL
233 ,p_ca_tax_information16 => NULL
234 ,p_ca_tax_information17 => NULL
235 ,p_ca_tax_information18 => NULL
236 ,p_ca_tax_information19 => NULL
237 ,p_ca_tax_information20 => NULL
238 ,p_ca_tax_information21 => NULL
239 ,p_ca_tax_information22 => NULL
240 ,p_ca_tax_information23 => NULL
241 ,p_ca_tax_information24 => NULL
242 ,p_ca_tax_information25 => NULL
243 ,p_ca_tax_information26 => NULL
244 ,p_ca_tax_information27 => NULL
245 ,p_ca_tax_information28 => NULL
246 ,p_ca_tax_information29 => NULL
247 ,p_ca_tax_information30 => NULL
248 ,p_object_version_number => l_object_version_number
249 ,p_prov_lsp_amount => 0
250 ,p_effective_date => p_effective_date
251 ,p_ppip_exempt_flag => 'N'
252 ) ;
253 hr_utility.set_location('province_code'||l_province_code,999);
254
255 commit;
256
257 hr_utility.set_location('pay_ca_emp_tax_inf.create_default_tax_record',99);
258 /****/
259 end create_default_tax_record;
260
261 function get_basic_exemption(p_effective_date date,
262 p_province varchar2 DEFAULT NULL)
263 return number is
264 CURSOR sel_inf_val IS
265 SELECT fnd_number.canonical_to_number(information_value)
266 FROM pay_ca_legislation_info pcli
267 WHERE pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
268 AND ((p_province IS NULL and pcli.jurisdiction_code is null)
269 OR (pcli.jurisdiction_code = p_province))
270 AND p_effective_date BETWEEN pcli.start_date AND pcli.end_date;
271
272 l_basic_exempt_amnt number;
273 begin
274 open sel_inf_val;
275 fetch sel_inf_val into l_basic_exempt_amnt;
276 close sel_inf_val;
277
278 return l_basic_exempt_amnt;
279 end get_basic_exemption;
280
281 procedure get_min_asg_start_date(p_assignment_id in number,
282 p_min_start_date out nocopy date) is
283 cursor csr_min_date is
284 select min(effective_start_date)
285 from per_assignments_f paf
286 where paf.assignment_id = p_assignment_id
287 and paf.assignment_type <> 'A';
288
289 l_min_start_date date;
290 begin
291
292 open csr_min_date;
293 fetch csr_min_date into l_min_start_date;
294 close csr_min_date;
295
296 p_min_start_date := l_min_start_date;
297
298 end get_min_asg_start_date;
299
300
301
302 function get_tax_detail_num
303 (p_assignment_id NUMBER,
304 p_effective_start_date DATE,
305 p_effective_end_date DATE,
306 p_effective_date DATE,
307 p_info_type VARCHAR2)
308 return number is
309
310 cursor csr_tax_num is
311 select
312 tax_credit_amount
313 ,basic_exemption_flag
314 ,additional_tax
315 ,annual_dedn
316 ,total_expense_by_commission
317 ,total_remnrtn_by_commission
318 ,prescribed_zone_dedn_amt
319 ,fed_override_amount
320 ,fed_override_rate
321 ,fed_lsf_amount
322 from pay_ca_emp_fed_tax_info_f peft where
323 peft.assignment_id = p_assignment_id and
324 p_effective_date between peft.effective_start_date and peft.effective_end_date;
325
326 l_tax_credit_amount number;
327 l_basic_exemption_flag varchar2(1);
328 l_additional_tax number;
329 l_annual_dedn number;
330 l_total_expense_by_commission number;
331 l_total_remnrtn_by_commission number;
332 l_prescribed_zone_dedn_amt number;
333 l_fed_override_amount number;
334 l_fed_override_rate number;
335 l_fed_lsf_amount number;
336
337 begin
338
339 open csr_tax_num;
340
341 fetch csr_tax_num into
342 l_tax_credit_amount
343 ,l_basic_exemption_flag
344 ,l_additional_tax
345 ,l_annual_dedn
346 ,l_total_expense_by_commission
347 ,l_total_remnrtn_by_commission
348 ,l_prescribed_zone_dedn_amt
349 ,l_fed_override_amount
350 ,l_fed_override_rate
351 ,l_fed_lsf_amount ;
352
353 if csr_tax_num%NOTFOUND then
354
355 l_tax_credit_amount := NULL;
356 l_basic_exemption_flag := 'Y';
357 l_additional_tax := 0.0;
358 l_annual_dedn := 0.0;
359 l_total_expense_by_commission := 0.0;
360 l_total_remnrtn_by_commission := 0.0;
361 l_prescribed_zone_dedn_amt := 0.0;
362 l_fed_override_amount := 0.0;
363 l_fed_override_rate := 0.0;
364 l_fed_lsf_amount := 0.0;
365 end if;
366 if p_info_type = 'TCA' then
367
368 if l_tax_credit_amount is null and l_basic_exemption_flag = 'Y' then
369
370 select fnd_number.canonical_to_number(information_value)
371 into l_tax_credit_amount
372 from pay_ca_legislation_info pcli
373 where pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
374 and pcli.jurisdiction_code IS NULL
375 and p_effective_date between pcli.start_date and pcli.end_date;
376
377 end if;
378 return l_tax_credit_amount;
379 --
380 elsif p_info_type = 'ADDTAX' then
381 return l_additional_tax;
382 --
383 elsif p_info_type = 'ANNDED' then
384 return l_annual_dedn;
385 --
386 elsif p_info_type = 'PZDN' then
387 return l_prescribed_zone_dedn_amt;
388 --
389 elsif p_info_type = 'EXPCOMM' then
390 return l_total_expense_by_commission;
391 --
392 elsif p_info_type = 'REMCOMM' then
393 return l_total_remnrtn_by_commission;
394 --
395 elsif p_info_type = 'OVERRIDERATE' then
396 return l_fed_override_rate;
397 --
398 elsif p_info_type = 'OVERRIDEAMNT' then
399 return l_fed_override_amount ;
400 --
401 elsif p_info_type = 'LSF' then
402 return l_fed_lsf_amount ;
403 --
404 end if;
405 end get_tax_detail_num;
406
407 function get_tax_detail_char(p_assignment_id in Number,
408 p_effective_start_date in date,
409 p_effective_end_date in date,
410 p_effective_date in date,
411 p_info_type in VARCHAR2)
412 return VARCHAR2 is
413 cursor csr_tax_char is
414 select
415 cpp_qpp_exempt_flag
416 ,fed_exempt_flag
417 ,ei_exempt_flag
418 ,tax_calc_method
419 from pay_ca_emp_fed_tax_info_f peft where
420 peft.assignment_id = p_assignment_id and
421 p_effective_date between peft.effective_start_date and peft.effective_end_date;
422
423 CURSOR csr_get_default_province IS
424 select pcp.province_abbrev, '70-'||pcp.province_code||'-0000' geocode
425 from per_assignments_f paf,
426 hr_locations hl,
427 pay_ca_provinces_v pcp
428 where paf.assignment_id = p_assignment_id
429 and p_effective_date between paf.effective_start_date
430 and paf.effective_end_date
431 and paf.location_id = hl.location_id
432 and hl.region_1 = pcp.province_abbrev;
433
434 CURSOR csr_get_override_province IS
435 select pcp.province_abbrev, '70-'||pcp.province_code||'-0000' geocode
436 from pay_ca_emp_fed_tax_info_f pf,
437 pay_ca_provinces_v pcp
438 where p_effective_date between pf.effective_start_date
439 and pf.effective_end_date
440 and pf.employment_province = pcp.province_abbrev
441 and pf.assignment_id = p_assignment_id;
442
443
444 l_employment_province varchar2(30);
445 l_cpp_qpp_exempt_flag varchar2(1);
446 l_fed_exempt_flag varchar2(1);
447 l_ei_exempt_flag varchar2(1);
448 l_tax_calc_method varchar2(30);
449 l_geocode varchar2(30);
450 begin
451
452 --Get the province of employments
453
454 IF p_info_type = 'GEOCODE' OR p_info_type = 'EMPPROV' THEN
455 OPEN csr_get_override_province;
456 FETCH csr_get_override_province INTO l_employment_province, l_geocode;
457 IF csr_get_override_province%NOTFOUND THEN
458 OPEN csr_get_default_province;
459 FETCH csr_get_default_province INTO l_employment_province, l_geocode;
460 IF csr_get_default_province%NOTFOUND THEN
461 l_employment_province := NULL;
462 l_geocode := '00-000-0000';
463 END IF;
464 CLOSE csr_get_default_province;
465 END IF;
466 CLOSE csr_get_override_province;
467
468 ELSE
469
470 l_geocode := '00-000-0000';
471
472 END IF;
473
474 open csr_tax_char;
475 fetch csr_tax_char into
476 l_cpp_qpp_exempt_flag
477 ,l_fed_exempt_flag
478 ,l_ei_exempt_flag
479 ,l_tax_calc_method;
480
481 if csr_tax_char%NOTFOUND then
482 l_cpp_qpp_exempt_flag := 'N';
483 l_fed_exempt_flag := 'N';
484 l_ei_exempt_flag := 'N';
485 l_tax_calc_method := NULL;
486 end if;
487
488 if p_info_type = 'EMPPROV' then
489 return l_employment_province;
490 --
491 elsif p_info_type = 'GEOCODE' then
492 return l_geocode;
493 --
494 elsif p_info_type = 'FEDEXEMPT' then
495 return l_fed_exempt_flag;
496 --
497 elsif p_info_type = 'EIEXEMPT' then
498 return l_ei_exempt_flag;
499 --
500 elsif p_info_type = 'PPEXEMPT' then
501 return l_cpp_qpp_exempt_flag;
502 --
503 elsif p_info_type = 'CALCMETHOD' then
504 return l_tax_calc_method;
505 --
506 end if;
507 close csr_tax_char;
508 end get_tax_detail_char;
509
510 --
511 function get_tax_detail_dfs(p_assignment_id in Number,
512 p_effective_start_date in date,
513 p_effective_end_date in date,
514 p_effective_date in date,
515 p_info_type in VARCHAR2)
516
517 return varchar2 is
518
519 cursor csr_tax_dfs is
520 select
521 ca_tax_information1
522 from pay_ca_emp_fed_tax_info_f peft where
523 peft.assignment_id = p_assignment_id and
524 p_effective_date between
525 peft.effective_start_date and peft.effective_end_date and
526 ca_tax_information_category = 'FED' ;
527
528 l_ca_tax_information1 varchar2(1);
529 begin
530
531 open csr_tax_dfs;
532
533 fetch csr_tax_dfs into
534 l_ca_tax_information1;
535
536 if csr_tax_dfs%NOTFOUND then
537 l_ca_tax_information1 := 'N';
538 end if;
539
540 if p_info_type = 'STATINDIAN' then
541 return l_ca_tax_information1;
542 end if;
543
544 end get_tax_detail_dfs;
545
546 --
547 /* For Bug Number 13258136: Added the function 'get_tax_detail_date' to add 2 columns
548 cpp_election_date and cpp_revocation_date in the view PAY_CA_EMP_ALL_FEDTAX_INFO_V */
549
550 function get_tax_detail_date
551 (p_assignment_id NUMBER,
552 p_effective_start_date DATE,
553 p_effective_end_date DATE,
554 p_effective_date DATE,
555 p_info_type VARCHAR2)
556 return date is
557
558 cursor csr_tax_date is
559 select
560 cpp_election_date
561 ,cpp_revocation_date
562 from pay_ca_emp_fed_tax_info_f peft where
563 peft.assignment_id = p_assignment_id and
564 p_effective_date between peft.effective_start_date and peft.effective_end_date;
565
566 l_cpp_election_date date;
567 l_cpp_revocation_date date;
568
569 begin
570
571 open csr_tax_date;
572
573 fetch csr_tax_date into
574 l_cpp_election_date
575 ,l_cpp_revocation_date ;
576
577 if csr_tax_date%NOTFOUND then
578
579 l_cpp_election_date := NULL;
580 l_cpp_revocation_date := NULL;
581
582 end if;
583 if p_info_type = 'CPPELEC' then
584 return l_cpp_election_date;
585 --
586 elsif p_info_type = 'CPPREV' then
587 return l_cpp_revocation_date;
588 --
589 end if;
590 end get_tax_detail_date;
591
592 --
593
594 function get_prov_tax_detail_num(p_assignment_id in Number,
595 p_effective_start_date in date,
596 p_effective_end_date in date,
597 p_effective_date in date,
598 p_province_abbrev in varchar2,
599 p_info_type in VARCHAR2)
600 return number is
601
602 cursor csr_tax_num is
603 select
604 tax_credit_amount
605 ,basic_exemption_flag
606 ,no_of_infirm_dependants
607 ,no_of_dependants
608 ,annual_dedn
609 ,total_expense_by_commission
610 ,total_remnrtn_by_commission
611 ,prescribed_zone_dedn_amt
612 ,additional_tax
613 ,prov_override_rate
614 ,prov_override_amount
615 ,prov_lsp_amount
616 from pay_ca_emp_prov_tax_info_f pept where
617 pept.assignment_id = p_assignment_id and
618 p_effective_date between pept.effective_start_date and pept.effective_end_date and
619 pept.province_code = p_province_abbrev;
620
621 l_tax_credit_amount number;
622 l_basic_exemption_flag varchar2(1);
623 l_no_of_infirm_dependants number;
624 l_no_of_dependants number;
625 l_annual_dedn number;
626 l_additional_tax number;
627 l_total_expense_by_commission number;
628 l_total_remnrtn_by_commission number;
629 l_prov_override_amount number;
630 l_prov_override_rate number;
631 l_prescribed_zone_dedn_amt number;
632 l_prov_lsp_amount number;
633
634 begin
635
636 open csr_tax_num;
637
638 fetch csr_tax_num into
639 l_tax_credit_amount
640 ,l_basic_exemption_flag
641 ,l_no_of_infirm_dependants
642 ,l_no_of_dependants
643 ,l_annual_dedn
644 ,l_total_expense_by_commission
645 ,l_total_remnrtn_by_commission
646 ,l_prescribed_zone_dedn_amt
647 ,l_additional_tax
648 ,l_prov_override_rate
649 ,l_prov_override_amount
650 ,l_prov_lsp_amount ;
651
652 if csr_tax_num%NOTFOUND then
653
654 l_tax_credit_amount := NULL;
655 l_basic_exemption_flag := 'Y';
656 l_no_of_infirm_dependants := 0;
657 l_no_of_dependants := 0;
658 l_annual_dedn := 0.0;
659 l_additional_tax := 0.0;
660 l_total_expense_by_commission := 0.0;
661 l_total_remnrtn_by_commission := 0.0;
662 l_prov_override_amount := 0.0;
663 l_prov_override_rate := 0.0;
664 l_prescribed_zone_dedn_amt := 0.0;
665 l_prov_lsp_amount := 0.0;
666
667 end if;
668
669 if p_info_type = 'TCA' then
670
671 if l_tax_credit_amount is null and l_basic_exemption_flag = 'Y' then
672
673 select fnd_number.canonical_to_number(information_value)
674 into l_tax_credit_amount
675 from pay_ca_legislation_info pcli
676 where pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
677 and pcli.jurisdiction_code = p_province_abbrev
678 and p_effective_date between pcli.start_date and pcli.end_date;
679
680 end if;
681 return l_tax_credit_amount;
682 --
683 elsif p_info_type = 'NUMDEP' then
684 return l_no_of_dependants;
685 --
686 elsif p_info_type = 'INFDEP' then
687 return l_no_of_infirm_dependants;
688 --
689 elsif p_info_type = 'ADDTAX' then
690 return l_additional_tax;
691 --
692 elsif p_info_type = 'ANNDED' then
693 return l_annual_dedn;
694 --
695 elsif p_info_type = 'EXPCOMM' then
696 return l_total_expense_by_commission;
697 --
698 elsif p_info_type = 'REMCOMM' then
699 return l_total_remnrtn_by_commission;
700 --
701 elsif p_info_type = 'PZDN' then
702 return l_prescribed_zone_dedn_amt;
703 --
704 elsif p_info_type = 'OVERRIDERATE' then
705 return l_prov_override_rate;
706 --
707 elsif p_info_type = 'OVERRIDEAMNT' then
708 return l_prov_override_amount ;
709 --
710 elsif p_info_type = 'LSP' then
711 return l_prov_lsp_amount ;
712 --
713 end if;
714 end get_prov_tax_detail_num;
715
716 function get_prov_tax_detail_char(p_assignment_id in Number,
717 p_effective_start_date in date,
718 p_effective_end_date in date,
719 p_effective_date in date,
720 p_province_abbrev in varchar2,
721 p_info_type in VARCHAR2)
722 return VARCHAR2 is
723
724 cursor csr_prov_tax_char is
725 select
726 jurisdiction_code
727 , extra_info_not_provided
728 , marriage_status
729 , non_resident_status
730 , disability_status
731 ,prov_exempt_flag
732 ,pmed_exempt_flag
733 ,wc_exempt_flag
734 ,qpp_exempt_flag
735 ,tax_calc_method
736 ,ppip_exempt_flag
737 from pay_ca_emp_prov_tax_info_f pept where
738 pept.assignment_id = p_assignment_id and
739 p_effective_date between pept.effective_start_date and pept.effective_end_date and
740 pept.province_code = p_province_abbrev;
741
742 l_jurisdiction_code varchar2(11);
743 l_extra_info_not_provided varchar2(11);
744 l_marriage_status varchar2(30);
745 l_non_resident_status varchar2(30);
746 l_disability_status varchar2(30);
747 l_prov_exempt_flag varchar2(30);
748 l_pmed_exempt_flag varchar2(30);
749 l_wc_exempt_flag varchar2(30);
750 l_qpp_exempt_flag varchar2(30);
751 l_tax_calc_method varchar2(30);
752 l_ppip_exempt_flag varchar2(30);
753
754 begin
755 open csr_prov_tax_char;
756 fetch csr_prov_tax_char into
757 l_jurisdiction_code
758 ,l_extra_info_not_provided
759 ,l_marriage_status
760 ,l_non_resident_status
761 ,l_disability_status
762 ,l_prov_exempt_flag
763 ,l_pmed_exempt_flag
764 ,l_wc_exempt_flag
765 ,l_qpp_exempt_flag
766 ,l_tax_calc_method
767 ,l_ppip_exempt_flag;
768
769 if csr_prov_tax_char%NOTFOUND then
770
771 l_extra_info_not_provided := 'Y';
772 l_marriage_status := NULL;
773 l_non_resident_status := NULL;
774 l_disability_status := NULL;
775 l_prov_exempt_flag := NULL;
776 l_pmed_exempt_flag := NULL;
777 l_wc_exempt_flag := NULL;
778 l_tax_calc_method := NULL;
779 l_ppip_exempt_flag := NULL;
780
781 end if;
782
783
784 if p_info_type = 'NOTPROV' then
785 return l_extra_info_not_provided;
786 --
787 elsif p_info_type = 'MARRIED' then
788 return l_marriage_status;
789 --
790 elsif p_info_type = 'NONRES' then
791 return l_non_resident_status;
792 --
793 elsif p_info_type = 'DISABLE' then
794 return l_disability_status;
795 --
796 elsif p_info_type = 'PROVEXEMPT' then
797 return l_prov_exempt_flag;
798 --
799 elsif p_info_type = 'PMEDEXEMPT' then
800 return l_pmed_exempt_flag;
801 --
802 elsif p_info_type = 'WCBEXEMPT' then
803 return l_wc_exempt_flag;
804 --
805 elsif p_info_type = 'QPPEXEMPT' then
806 return l_qpp_exempt_flag;
807 --
808 elsif p_info_type = 'CALCMETHOD' then
809 return l_tax_calc_method;
810 --
811 elsif p_info_type = 'PPIPEXEMPT' then
812 return l_ppip_exempt_flag;
813 --
814 end if;
815 close csr_prov_tax_char;
816 end get_prov_tax_detail_char;
817
818 --
819
820 function get_prov_tax_detail_dfs(p_assignment_id in Number,
821 p_effective_start_date in date,
822 p_effective_end_date in date,
823 p_effective_date in date,
824 p_province_abbrev in varchar2,
825 p_info_type in VARCHAR2)
826 return VARCHAR2 is
827 cursor csr_prov_tax_dfs is
828 select
829 ca_tax_information1,
830 ca_tax_information2
831 from pay_ca_emp_prov_tax_info_f pept where
832 pept.assignment_id = p_assignment_id and
833 p_effective_date between
834 pept.effective_start_date and pept.effective_end_date and
835 -- ca_tax_information_category = p_province_abbrev;
836 ca_tax_information_category = 'PROV' and
837 p_province_abbrev = province_code;
838
839 l_ca_tax_information1 varchar2(1);
840 l_ca_tax_information2 varchar2(1);
841 begin
842
843 /*The value for the Indian status flex field should only be considered in the
844 provincial tax calculation if the province is one of the three provinces below */
845
846 if p_province_abbrev <> 'NT' and
847 p_province_abbrev <> 'NU' and
848 p_province_abbrev <> 'QC' then
849
850 /* If the province is Manitoba then return the over 65 flag */
851
852 if p_province_abbrev = 'MB' then
853
854 open csr_prov_tax_dfs;
855
856 fetch csr_prov_tax_dfs into
857 l_ca_tax_information1,
858 l_ca_tax_information2;
859
860 if csr_prov_tax_dfs%NOTFOUND then
861 l_ca_tax_information2 := 'N';
862 end if;
863
864 close csr_prov_tax_dfs;
865 else
866 l_ca_tax_information2 := 'N';
867 end if;
868
869 l_ca_tax_information1 := 'N';
870
871 else
872 open csr_prov_tax_dfs;
873
874 fetch csr_prov_tax_dfs into
875 l_ca_tax_information1,
876 l_ca_tax_information2;
877
878 if csr_prov_tax_dfs%NOTFOUND then
879 l_ca_tax_information1 := 'N';
880 end if;
881
882 close csr_prov_tax_dfs;
883
884 l_ca_tax_information2 := 'N';
885
886 end if;
887
888 if p_info_type = 'STATINDIAN' then
889 return l_ca_tax_information1;
890 elsif p_info_type = 'OVER65' then
891 return l_ca_tax_information2;
892 end if;
893
894 end get_prov_tax_detail_dfs;
895
896 --
897
898 function get_address(p_person_id in Number,
899 p_effective_date in date,
900 address_line_no in number
901 ) return VARCHAR2 is
902 cursor csr_address( p_person_id in number,
903 p_effective_date in date) is
904
905 select substr(addr.address_line1,1,37) ,
906 substr(addr.address_line2,1,37) ,
907 substr(addr.address_line3,1,37) ,
908 rtrim(substr(addr.town_or_city,1,23)) ||' '||addr.region_1||' '||addr.postal_code
909 from per_addresses addr
910 WHERE addr.person_id = p_person_id
911 AND addr.primary_flag = 'Y'
912 AND p_effective_date between
913 addr.date_from and nvl(addr.date_to, p_effective_date);
914
915 l_emp_addr_line1 varchar2(80);
916 l_emp_addr_line2 varchar2(80);
917 l_emp_addr_line3 varchar2(80);
918 l_emp_addr_line4 varchar2(180);
919 l_emp_addr_line varchar2(180);
920
921 begin
922
923 open csr_address(p_person_id,p_effective_date);
924
925 fetch csr_address into l_emp_addr_line1,
926 l_emp_addr_line2,
927 l_emp_addr_line3,
928 l_emp_addr_line4;
929
930 if csr_address%NOTFOUND then
931 l_emp_addr_line1 := 'ADDRESS NOT IN THE FILE';
932 l_emp_addr_line2 := 'ADDRESS NOT IN THE FILE';
933 l_emp_addr_line3 := 'ADDRESS NOT IN THE FILE';
934 l_emp_addr_line4 := 'ADDRESS NOT IN THE FILE';
935 end if;
936 close csr_address;
937 --
938 if address_line_no = 1 then
939 l_emp_addr_line := l_emp_addr_line1;
940 elsif address_line_no = 2 then
941 l_emp_addr_line := l_emp_addr_line2;
942 elsif address_line_no = 3 then
943 l_emp_addr_line := l_emp_addr_line3;
944 elsif address_line_no = 4 then
945 l_emp_addr_line := l_emp_addr_line4;
946 end if;
947
948 return l_emp_addr_line;
949
950 end get_address;
951
952 function get_salary_basis(p_salary_basis_id in Number)
953 return VARCHAR2 is
954 cursor csr_salary_basis(l_pay_basis_id in number) is
955 select pay_basis from per_pay_bases
956 where pay_basis_id = l_pay_basis_id;
957
958 l_salary_basis varchar2(30);
959 begin
960 open csr_salary_basis(p_salary_basis_id);
961 fetch csr_salary_basis into l_salary_basis;
962 if csr_salary_basis%NOTFOUND then
963 l_salary_basis := 'NOT FOUND';
964 end if;
965 close csr_salary_basis;
966 return l_salary_basis;
967 end get_salary_basis;
968
969
970 function get_base_salary(p_assignment_id in Number,
971 p_effective_date in date,
972 p_salary_basis_id in number)
973 return VARCHAR2 is
974 cursor csr_base_salary(l_assignment_id in number,
975 l_effective_date in date,
976 l_input_value_id in number) is
977 select decode(instr(peev.screen_entry_value,'.'),
978 0,
979 peev.screen_entry_value|| '.00',
980 peev.screen_entry_value
981 )
982 from pay_element_entries_f pee,
983 pay_element_entry_values_f peev,
984 pay_input_values_f piv
985 WHERE l_effective_date
986 between pee.effective_start_date AND pee.effective_end_date
987 AND pee.element_entry_id = peev.element_entry_id
988 AND pee.entry_type = 'E'
989 AND pee.assignment_id = l_assignment_id
990 AND l_effective_date
991 between peev.effective_start_date and peev.effective_end_date
992 AND peev.input_value_id+0 = piv.input_value_id
993 AND l_effective_date
994 between piv.effective_start_date AND piv.effective_end_date
995 AND piv.input_value_id = l_input_value_id;
996
997 cursor csr_input_value_id(l_pay_basis_id in number) is
998 select input_value_id from per_pay_bases
999 where pay_basis_id = l_pay_basis_id;
1000
1001 l_base_salary varchar2(30);
1002 l_input_value_id number;
1003 l_salary_basis varchar2(30); --Bug 8544619
1004 begin
1005
1006 open csr_input_value_id(p_salary_basis_id);
1007 fetch csr_input_value_id into l_input_value_id;
1008 if csr_input_value_id%NOTFOUND then
1009 l_base_salary := 'NOT ENTERED';
1010 else
1011 open csr_base_salary(p_assignment_id,
1012 p_effective_date,
1013 l_input_value_id) ;
1014 fetch csr_base_salary into l_base_salary;
1015 if csr_base_salary%NOTFOUND then
1016 l_base_salary := 'NOT ENTERED';
1017 end if;
1018 close csr_base_salary;
1019 end if;
1020 close csr_input_value_id;
1021 -- Modification for bug 8544619 starts here
1022 l_salary_basis := get_salary_basis(p_salary_basis_id);
1023 if l_salary_basis = 'HOURLY' and l_base_salary <> 'NOT ENTERED' then
1024 l_base_salary := fnd_number.number_to_canonical(round(fnd_number.canonical_to_number(l_base_salary),5));
1025 end if;
1026 -- Modification for bug 8544619 ends here
1027 return l_base_salary;
1028 end get_base_salary;
1029
1030 function get_summary_info(p_assignment_action_id in Number,
1031 p_information_type in varchar2,
1032 p_dimension in varchar2
1033 ) return number is
1034 l_value number;
1035 begin
1036 if p_information_type <> 'DEDUCTIONS_SUMM' then
1037 select decode(p_dimension,'CURRENT',amount_current,'YTD',amount_ytd)
1038 into l_value
1039 from pay_ca_soe_summ_balances_v pcs
1040 where pcs.assignment_action_id = p_assignment_action_id
1041 and pcs.base_bal_name = decode(p_information_type,'GROSS_PAY_SUMM', /*balance_name changed to base_bal_name against bug#5169734*/
1042 'Gross Pay',
1043 'TAXABLE_BENEFIT_SUMM',
1044 'Taxable Benefits',
1045 'GROSS_EARNINGS_SUMM',
1046 'Gross Earnings',
1047 'TAXES_SUMM',
1048 'Tax Deductions',
1049 'NET_PAY_SUMM',
1050 'Payments');
1051 else
1052 select sum(decode(p_dimension,'CURRENT',amount_current,'YTD',amount_ytd))
1053 into l_value
1054 from pay_ca_soe_summ_balances_v pcs
1055 where pcs.assignment_action_id = p_assignment_action_id
1056 and pcs.balance_name in (
1057 'Pre Tax Deductions',
1058 'Involuntary Deductions',
1059 'Voluntary Deductions'
1060 );
1061 end if;
1062 return l_value;
1063 EXCEPTION
1064 when no_data_found
1065 then return 0;
1066
1067 end get_summary_info;
1068
1069 function check_age_under18_or_over70(p_payroll_action_id in Number,
1070 p_date_of_birth in Date,
1071 p_exemption_start_date Date,
1072 p_exemption_end_date Date) return VARCHAR2 is
1073 l_check_age VARCHAR2(1);
1074 l_effective_date Date;
1075 l_exemption_start_date date;
1076 l_exemption_end_date date;
1077 -- Get
1078
1079 CURSOR csr_get_effective_date(l_payroll_action_id in Number) IS
1080 SELECT effective_date
1081 FROM pay_payroll_actions
1082 WHERE payroll_action_id = l_payroll_action_id;
1083
1084 begin
1085 l_check_age := 'N';
1086
1087 open csr_get_effective_date(p_payroll_action_id);
1088 fetch csr_get_effective_date into l_effective_date;
1089 close csr_get_effective_date;
1090
1091 /*
1092 if (( add_months(trunc(p_date_of_birth,'MONTH'),(18*12)+1)) <= l_effective_date ) then
1093 if ( l_effective_date >= ( add_months(trunc(p_date_of_birth,'MONTH'),(70*12)+1))) then
1094 l_check_age := 'Y';
1095 else
1096 l_check_age := 'N';
1097 end if;
1098 else
1099 l_check_age := 'Y';
1100
1101 end if;
1102 */
1103 hr_utility.trace('IN heck_age_under18_or_over70');
1104 hr_utility.trace('age is ' || round(MONTHS_BETWEEN(l_effective_date,p_date_of_birth)/12,1) || ' Yrs');
1105
1106
1107
1108 if (( add_months(trunc(p_date_of_birth,'MONTH'),(18*12)+1)) <= l_effective_date ) then
1109
1110 if ( l_effective_date >= ( add_months(trunc(p_date_of_birth,'MONTH'),(70*12)+1))) then
1111
1112
1113 l_check_age := 'Y';
1114 else -- over 18 unger than 70
1115
1116 if ( l_effective_date >= ( add_months(trunc(p_date_of_birth,'MONTH'),(65*12)+1)) ) then
1117 -- age between 65 abd 70
1118
1119 if p_exemption_start_date <> to_date('01/01/0001','mm/dd/yyyy') OR
1120 p_exemption_end_date <> to_date('01/01/0001','mm/dd/yyyy') then
1121 -- one exemption variable not null x
1122
1123 if p_exemption_start_date <> to_date('01/01/0001','mm/dd/yyyy') AND
1124 p_exemption_end_date = to_date('01/01/0001','mm/dd/yyyy') then
1125
1126 -- one exemption variable not null x
1127 if ((l_effective_date >= p_exemption_start_date) and
1128 (l_effective_date >= p_exemption_end_date)) then
1129 l_check_age := 'Y';
1130 else
1131 l_check_age := 'N';
1132 end if;
1133 else
1134
1135 if ((l_effective_date >= p_exemption_start_date) and
1136 (l_effective_date <= p_exemption_end_date)) then
1137
1138 l_check_age := 'Y';
1139 else
1140 l_check_age := 'N';
1141 end if;
1142 end if;
1143 else
1144 l_check_age := 'N';
1145 end if;
1146
1147
1148 else -- younger than 65
1149 l_check_age := 'N';
1150 end if;
1151 end if;
1152 else -- under 18
1153
1154 l_check_age := 'Y';
1155 end if;
1156
1157 return l_check_age;
1158 end check_age_under18_or_over70;
1159
1160 /*Changes for the bug 8830591 starts here*/
1161 function get_loc_change_retro_exemp(p_payroll_action_id in Number,
1162 p_assignment_id in Number,
1163 p_exemption in VARCHAR2) return Number is
1164 l_value Number;
1165
1166 CURSOR csr_get_exemption_value(l_payroll_action_id in Number,
1167 l_assignment_id in Number,
1168 l_exemption in VARCHAR2) IS
1169 select nvl(sum(pev.screen_entry_value),0)
1170 from pay_element_types_f pet,
1171 pay_input_values_f piv,
1172 pay_element_entries_f pee,
1173 pay_element_entry_Values_f pev,
1174 pay_payroll_actions ppa,
1175 pay_assignment_actions paa,
1176 pay_payroll_actions ppa2
1177 where pee.assignment_id = l_assignment_id
1178 and piv.element_type_id = pet.element_type_id
1179 and pee.element_type_id = pet.element_type_id
1180 and pee.element_entry_id = pev.element_Entry_id
1181 and pev.input_value_id = piv.input_value_id
1182 and ppa.date_earned between pee.effective_start_date and pee.effective_end_Date
1183 and ppa.payroll_action_id = l_payroll_action_id
1184 and pet.element_name = l_exemption
1185 and piv.name = 'Basic Exemption'
1186 and pee.creator_type = 'RR'
1187 and paa.assignment_action_id = pee.source_asg_action_id
1188 and paa.payroll_action_id = ppa2.payroll_action_id
1189 and to_char(ppa.effective_date,'YYYY') = to_char(ppa2.effective_date,'YYYY');
1190
1191 CURSOR csr_get_effective_year( l_payroll_action_id in Number ) IS
1192 select to_number(to_char(effective_date,'YYYY'))
1193 from pay_payroll_actions
1194 where payroll_action_id = l_payroll_action_id;
1195
1196 begin
1197 l_value := 0;
1198 if p_exemption in ('CPP EE Retro Exemption','CPP ER Retro Exemption'
1199 ,'QPP EE Retro Exemption','QPP ER Retro Exemption') then
1200
1201 open csr_get_exemption_value(p_payroll_action_id,p_assignment_id,p_exemption);
1202 fetch csr_get_exemption_value into l_value;
1203 close csr_get_exemption_value;
1204 hr_utility.trace('IN get_location_change_retro_exemption');
1205 hr_utility.trace(p_exemption ||' value is: ' || l_value);
1206
1207 elsif p_exemption in ('Effective Year') then
1208
1209 open csr_get_effective_year(p_payroll_action_id);
1210 fetch csr_get_effective_year into l_value;
1211 close csr_get_effective_year;
1212 hr_utility.trace(p_exemption ||' value is: ' || l_value);
1213
1214 end if;
1215 return l_value;
1216 end get_loc_change_retro_exemp;
1217
1218 /*Changes for the bug 8830591 ends here*/
1219
1220 function check_age_under18(p_payroll_action_id in Number,
1221 p_date_of_birth in Date) return VARCHAR2 is
1222 l_check_age VARCHAR2(1);
1223 l_effective_date Date;
1224 -- Get
1225
1226 CURSOR csr_get_effective_date(l_payroll_action_id in Number) IS
1227 SELECT effective_date
1228 FROM pay_payroll_actions
1229 WHERE payroll_action_id = l_payroll_action_id;
1230
1231 begin
1232 l_check_age := 'N';
1233
1234 open csr_get_effective_date(p_payroll_action_id);
1235 fetch csr_get_effective_date into l_effective_date;
1236 close csr_get_effective_date;
1237
1238 if (( add_months(trunc(p_date_of_birth,'MONTH'),(18*12)+1)) <= l_effective_date ) then
1239 l_check_age := 'N';
1240 else
1241 l_check_age := 'Y';
1242
1243 end if;
1244
1245 return l_check_age;
1246 end check_age_under18;
1247
1248 /*Changes for the bug 8830591 starts here*/
1249 function retro_across_calendar_years (p_element_entry_id in number,
1250 p_payroll_action_id in number)
1251 return varchar2 is
1252
1253 l_creator_type varchar2(30);
1254 l_source_id number;
1255 l_source_asg_action_id number;
1256 l_originating_date date;
1257 l_current_date date;
1258 l_check_years varchar2(1);
1259 l_assignment_id number;
1260 l_current_location varchar2(30);
1261 l_originating_location varchar2(30);
1262 l_check_location varchar2(1);
1263
1264 cursor csr_get_current_date is
1265 select effective_date
1266 from pay_payroll_actions
1267 where payroll_action_id = p_payroll_action_id;
1268
1269 cursor csr_get_ele_entry_info is
1270 select creator_type, source_id, source_asg_action_id,assignment_id
1271 from pay_element_entries_f
1272 where element_entry_id = p_element_entry_id;
1273
1274 cursor csr_get_current_location(l_asst_id in number,l_cur_date in date) is
1275 select hla.region_1
1276 from per_all_assignments_f paf,
1277 hr_locations_all hla
1278 where paf.assignment_id = l_asst_id
1279 and l_cur_date between paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
1280 and hla.location_id = paf.location_id;
1281
1282 cursor csr_get_orig_date_rr(l_run_result_id in number) is
1283 select ppa.effective_date,hla.region_1
1284 from pay_run_results prr,
1285 pay_assignment_actions paa,
1286 pay_payroll_actions ppa,
1287 per_all_assignments_f paf,
1288 hr_locations_all hla
1289 where prr.run_result_id = l_run_result_id
1290 and prr.assignment_action_id = paa.assignment_action_id
1291 and paa.payroll_action_id = ppa.payroll_action_id
1292 and paf.assignment_id = paa.assignment_id
1293 and ppa.effective_date between paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
1294 and hla.location_id = paf.location_id;
1295
1296 cursor csr_get_orig_date_asgact(l_asg_action_id in number) is
1297 select ppa.effective_date,hla.region_1
1298 from pay_assignment_actions paa,
1299 pay_payroll_actions ppa,
1300 per_all_assignments_f paf,
1301 hr_locations_all hla
1302 where paa.assignment_action_id = l_asg_action_id
1303 and ppa.payroll_action_id = paa.payroll_action_id
1304 and paf.assignment_id = paa.assignment_id
1305 and ppa.effective_date between paf.EFFECTIVE_START_DATE and paf.EFFECTIVE_END_DATE
1306 and hla.location_id = paf.location_id;
1307
1308 begin
1309
1310 open csr_get_current_date;
1311 fetch csr_get_current_date
1312 into l_current_date;
1313 close csr_get_current_date;
1314
1315 open csr_get_ele_entry_info;
1316 fetch csr_get_ele_entry_info
1317 into l_creator_type, l_source_id, l_source_asg_action_id,l_assignment_id;
1318 close csr_get_ele_entry_info;
1319
1320 open csr_get_current_location(l_assignment_id,l_current_date);
1321 fetch csr_get_current_location
1322 into l_current_location;
1323 close csr_get_current_location;
1324
1325
1326 if l_creator_type = 'RR' then
1327
1328 open csr_get_orig_date_rr(l_source_id);
1329 fetch csr_get_orig_date_rr
1330 into l_originating_date,l_originating_location;
1331 close csr_get_orig_date_rr;
1332
1333 if to_char(l_originating_date,'YYYY') = to_char(l_current_date,'YYYY') then
1334 l_check_years := 'N';
1335 else
1336 l_check_years := 'Y';
1337 end if;
1338
1339 if (l_originating_location = 'QC' and l_current_location <>'QC') or
1340 (l_originating_location <> 'QC' and l_current_location = 'QC') then
1341 l_check_location := 'N';
1342 else
1343 l_check_location := 'Y';
1344 end if;
1345
1346 elsif l_creator_type = 'EE' then
1347
1348 open csr_get_orig_date_asgact(l_source_asg_action_id);
1349 fetch csr_get_orig_date_asgact
1350 into l_originating_date,l_originating_location;
1351 close csr_get_orig_date_asgact;
1352
1353 if to_char(l_originating_date,'YYYY') = to_char(l_current_date,'YYYY') then
1354 l_check_years := 'N';
1355 else
1356 l_check_years := 'Y';
1357 end if;
1358
1359 if (l_originating_location = 'QC' and l_current_location <>'QC') or
1360 (l_originating_location <> 'QC' and l_current_location = 'QC') then
1361 l_check_location := 'N';
1362 else
1363 l_check_location := 'Y';
1364 end if;
1365
1366 else
1367 l_check_years := 'N';
1368 l_check_location := 'Y';
1369 end if;
1370
1371 if l_check_years = 'Y' or l_check_location = 'N' then
1372 return 'Y';
1373 else
1374 return 'N';
1375 end if;
1376
1377 end retro_across_calendar_years;
1378 /*Changes for the bug 8830591 ends here*/
1379
1380 /*****************************************************************************
1381 Delete_fed_tax_rule procedure calls
1382 pay_ca_emp_fedtax_inf_api.delete_ca_emp_fedtax_inf procedure for updating
1383 Effective_End_Date of tax records in PAY_CA_EMP_FED_TAX_INFO_F table.
1384
1385 pay_ca_emp_prvtax_inf_api.delete_ca_emp_prvtax_inf procedure for updating
1386 Effective_End_Date of tax records in PAY_CA_EMP_PROV_TAX_INFO_F table.
1387
1388 *****************************************************************************/
1389
1390 procedure delete_fed_tax_rule
1391 (p_effective_date in date
1392 ,p_datetrack_delete_mode in varchar2
1393 ,p_assignment_id in number
1394 ,p_delete_routine in varchar2
1395 ,p_effective_start_date out nocopy date
1396 ,p_effective_end_date out nocopy date
1397 ,p_object_version_number out nocopy number
1398 ) is
1399 --
1400 -- Declare cursors and local variables
1401 --
1402 l_proc varchar2(72) := 'delete_fed_tax_rule';
1403 l_effective_date date;
1404 l_emp_prov_tax_inf_id pay_ca_emp_prov_tax_info_f.emp_province_tax_inf_id%TYPE;
1405 l_emp_fed_tax_inf_id pay_ca_emp_fed_tax_info_f.emp_fed_tax_inf_id%TYPE;
1406 l_effective_start_date pay_ca_emp_fed_tax_info_f.effective_start_date%TYPE;
1407 l_effective_end_date pay_ca_emp_fed_tax_info_f.effective_end_date%TYPE;
1408 l_object_version_number pay_ca_emp_fed_tax_info_f.object_version_number%TYPE;
1409 l_tmp_effective_start_date pay_ca_emp_fed_tax_info_f.effective_start_date%TYPE;
1410 l_tmp_effective_end_date pay_ca_emp_fed_tax_info_f.effective_end_date%TYPE;
1411 l_tmp_object_version_number pay_ca_emp_fed_tax_info_f.object_version_number%TYPE;
1412 --
1413 l_exit_quietly exception;
1414 --
1415 cursor csr_fed_rule is
1416 select fed.emp_fed_tax_inf_id, fed.object_version_number
1417 from pay_ca_emp_fed_tax_info_f fed
1418 where fed.assignment_id = p_assignment_id
1419 and l_effective_date between fed.effective_start_date
1420 and fed.effective_end_date;
1421 --
1422 cursor csr_prov_rule is
1423 select sta.emp_province_tax_inf_id, sta.object_version_number
1424 from pay_ca_emp_prov_tax_info_f sta
1425 where sta.assignment_id = p_assignment_id
1426 and l_effective_date between sta.effective_start_date
1427 and sta.effective_end_date;
1428 --
1429 --
1430 begin
1431 --
1432 --
1433 hr_utility.set_location(' Entering: '||'pay_ca_emp_tax_inf'||l_proc, 10);
1434 l_effective_date := trunc(p_effective_date);
1435 --
1436 -- Validate that a federal tax rule exists for this assignment
1437 --
1438 open csr_fed_rule;
1439 fetch csr_fed_rule into l_emp_fed_tax_inf_id, l_object_version_number;
1440 if csr_fed_rule%notfound then
1441 close csr_fed_rule;
1442 raise l_exit_quietly;
1443 end if;
1444 close csr_fed_rule;
1445
1446 hr_utility.set_location(l_proc, 20);
1447 --
1448 if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
1449 hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
1450 hr_utility.raise_error;
1451 end if;
1452 --
1453 -- Validate that this routine is called from Assignment code
1454 --
1455
1456 hr_utility.set_location(l_proc, 30);
1457
1458 if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
1459 hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
1460 hr_utility.raise_error;
1461 end if;
1462 --
1463 open csr_prov_rule;
1464 loop
1465 fetch csr_prov_rule into l_emp_prov_tax_inf_id, l_tmp_object_version_number;
1466 exit when csr_prov_rule%notfound;
1467 --
1468 -- Call delete_tax_rules API here passing in l_assignment_id, l_state_code
1469 pay_ca_emp_prvtax_inf_api.delete_ca_emp_prvtax_inf(
1470 p_validate => NULL -- check whether NULL is correct.
1471 ,p_emp_province_tax_inf_id => l_emp_prov_tax_inf_id
1472 ,p_effective_start_date => l_tmp_effective_start_date
1473 ,p_effective_end_date => l_tmp_effective_end_date
1474 ,p_object_version_number => l_tmp_object_version_number
1475 ,p_effective_date => l_effective_date
1476 ,p_datetrack_mode => p_datetrack_delete_mode
1477 );
1478
1479 --
1480 end loop;
1481 close csr_prov_rule;
1482
1483 hr_utility.set_location(l_proc, 40);
1484 --
1485 -- Need to check whether this procedure should be called
1486 /* maintain_wc(
1487 p_emp_fed_tax_rule_id => l_emp_fed_tax_inf_id
1488 ,p_effective_start_date => l_effective_start_date
1489 ,p_effective_end_date => l_effective_end_date
1490 ,p_effective_date => l_effective_date
1491 ,p_datetrack_mode => p_datetrack_delete_mode
1492 );
1493 */
1494 --
1495 --pay_fed_del.del(p_emp_fed_tax_rule_id => l_emp_fed_tax_inf_id
1496 pay_ca_emp_fedtax_inf_api.delete_ca_emp_fedtax_inf(
1497 p_validate => NULL -- check whether NULL is correct.
1498 ,p_emp_fed_tax_inf_id => l_emp_fed_tax_inf_id
1499 ,p_effective_start_date => l_effective_start_date
1500 ,p_effective_end_date => l_effective_end_date
1501 ,p_object_version_number => l_object_version_number
1502 ,p_effective_date => l_effective_date
1503 ,p_datetrack_mode => p_datetrack_delete_mode
1504 );
1505 --
1506 --
1507 hr_utility.set_location(l_proc, 50);
1508 --
1509 -- Set all output arguments
1510 --
1511 p_effective_start_date := l_effective_start_date;
1512 p_effective_end_date := l_effective_end_date;
1513 p_object_version_number := l_object_version_number;
1514 --
1515 hr_utility.set_location(' Leaving: '||'pay_ca_emp_tax_inf'||l_proc, 60);
1516 --
1517 exception
1518 --
1519 when l_exit_quietly then
1520 --
1521 p_effective_start_date := null;
1522 p_effective_end_date := null;
1523 p_object_version_number := null;
1524 --
1525 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 70);
1526 --
1527 --
1528 end delete_fed_tax_rule;
1529
1530 /*****************************************************************************
1531 Maintain_ca_employee_taxes procedure fetches Assignment_id
1532 values for the given period_of_service_id
1533 and calls Delete_fed_tax_rule procedure.
1534 *****************************************************************************/
1535
1536 procedure maintain_ca_employee_taxes
1537 ( p_period_of_service_id in number,
1538 p_effective_date in date
1539 ,p_datetrack_mode in varchar2 default null
1540 ,p_delete_routine in varchar2 default null
1541 ) is
1542
1543 TYPE assign_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1544 --
1545 -- Declare cursors and local variables
1546 --
1547 l_proc varchar2(72) := 'maintain_ca_employee_taxes';
1548 l_counter number := 0;
1549 l_effective_date date;
1550 l_assignment_id per_assignments_f.assignment_id%TYPE;
1551 l_fed_object_version_number
1552 pay_ca_emp_fed_tax_info_f.object_version_number%TYPE;
1553 l_fed_eff_start_date pay_ca_emp_fed_tax_info_f.effective_start_date%TYPE;
1554 l_fed_eff_end_date pay_ca_emp_fed_tax_info_f.effective_end_date%TYPE;
1555 l_temp_num number;
1556 l_cnt number;
1557 l_assignment_tbl assign_tbl_type;
1558
1559 l_exit_quietly exception;
1560 --
1561 -- rmonge Bug fix 3599825.
1562
1563 cursor csr_asg_id(p_csr_assignment_id number) is
1564 select null
1565 from per_assignments_f asg,
1566 hr_organization_information bus
1567 where asg.assignment_id = p_csr_assignment_id
1568 and bus.organization_id = asg.business_group_id
1569 and bus.org_information9 = 'CA'
1570 and bus.org_information_context = 'Business Group Information'
1571 and p_effective_date between asg.effective_start_date
1572 and asg.effective_end_date ;
1573
1574 cursor csr_adr_asg_id is
1575 select asg.assignment_id
1576 from per_assignments_f asg,
1577 per_periods_of_service pps
1578 where asg.person_id = pps.person_id
1579 and pps.period_of_service_id = p_period_of_service_id
1580 and p_effective_date between asg.effective_start_date
1581 and asg.effective_end_date;
1582 --
1583 --
1584 begin
1585 --
1586 hr_utility.set_location('Entering: '||'pay_ca_emp_tax_inf'||l_proc, 10);
1587 --
1588 l_effective_date := trunc(p_effective_date);
1589 --
1590 hr_utility.set_location(l_proc, 20);
1591 --
1592 -- First check if geocode has been installed or not. If no geocodes
1593 -- installed then return because there is nothing to be done by this
1594 -- procedure
1595 if hr_general.chk_maintain_tax_records = 'N' then
1596 raise l_exit_quietly;
1597 end if;
1598
1599 hr_utility.set_location(l_proc, 30);
1600
1601 if p_datetrack_mode NOT IN ('ZAP',
1602 'DELETE',
1603 'UPDATE',
1604 'CORRECTION',
1605 'UPDATE_OVERRIDE',
1606 'UPDATE_CHANGE_INSERT') then
1607 hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
1608 hr_utility.raise_error;
1609 elsif p_datetrack_mode in ('ZAP', 'DELETE') then
1610 hr_utility.set_location(l_proc, 40);
1611 --
1612 l_cnt := 0;
1613 for l_assgn_rec in csr_adr_asg_id loop
1614 l_cnt := l_cnt + 1;
1615 l_assignment_tbl(l_cnt) := l_assgn_rec.assignment_id;
1616 end loop;
1617
1618 --
1619
1620 hr_utility.set_location('number of assignments '||l_assignment_tbl.count, 45);
1621
1622 for l_cnt in 1..l_assignment_tbl.last loop
1623 open csr_asg_id(l_assignment_tbl(l_cnt));
1624 fetch csr_asg_id into l_temp_num;
1625 if csr_asg_id%notfound then
1626 close csr_asg_id;
1627 hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
1628 hr_utility.raise_error;
1629 end if;
1630 close csr_asg_id;
1631 --
1632 hr_utility.set_location(l_proc, 50);
1633 hr_utility.set_location('assignment id '||l_assignment_tbl(l_cnt), 55);
1634 delete_fed_tax_rule(
1635 p_effective_date => l_effective_date
1636 ,p_datetrack_delete_mode => p_datetrack_mode
1637 ,p_assignment_id => l_assignment_tbl(l_cnt)
1638 ,p_delete_routine => p_delete_routine
1639 ,p_effective_start_date => l_fed_eff_start_date
1640 ,p_effective_end_date => l_fed_eff_end_date
1641 ,p_object_version_number => l_fed_object_version_number
1642 );
1643 end loop;
1644
1645 end if; -- datetrack mode is ZAP?
1646 --
1647 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 60);
1648 --
1649 exception
1650 --
1651 when l_exit_quietly then
1652 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 70);
1653 --
1654 end maintain_ca_employee_taxes;
1655
1656 procedure delete_tax_record
1657 ( p_period_of_service_id in number,
1658 p_final_process_date in date) is
1659
1660 -- Declare cursors and local variables
1661 --
1662 l_proc varchar2(72) := 'delete_tax_record';
1663
1664 begin
1665
1666 --
1667 hr_utility.set_location('Entering: '||'pay_ca_emp_tax_inf'||l_proc, 10);
1668 --
1669
1670 maintain_ca_employee_taxes
1671 ( p_period_of_service_id => p_period_of_service_id,
1672 p_effective_date => p_final_process_date,
1673 p_datetrack_mode => 'DELETE',
1674 p_delete_routine => 'ASSIGNMENT'
1675 );
1676
1677 hr_utility.set_location('Leaving: '||'pay_ca_emp_tax_inf'||l_proc, 20);
1678
1679 exception
1680 --
1681 when others then
1682 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 30);
1683 --
1684 end delete_tax_record;
1685
1686 /* The following function is used for determining whether an employee assignment is
1687 EI exempted or not. This function is invoked by the ROE Magnetic Media to determine whether
1688 Box 17b earnings need to be added to insurable earnings or not. */
1689
1690 function check_ei_exempt(p_roe_assignment_id in Number,
1691 p_roe_date in Date) return VARCHAR2 is
1692 l_ei_flag VARCHAR2(1);
1693 l_assignment_id NUMBER;
1694 l_roe_end_date DATE;
1695
1696
1697 CURSOR csr_get_ei_flag_17b(l_assignment_id in Number,l_roe_end_date Date) IS
1698 select NVL(ei_exempt_flag,'N')
1699 from pay_ca_emp_fed_tax_info_f peft where
1700 peft.assignment_id = l_assignment_id
1701
1702 AND l_roe_end_date between peft.effective_start_date and peft.effective_end_date;
1703
1704
1705 begin
1706 l_ei_flag := 'N';
1707
1708 open csr_get_ei_flag_17b(p_roe_assignment_id,p_roe_date);
1709 fetch csr_get_ei_flag_17b into l_ei_flag;
1710 IF csr_get_ei_flag_17b%NOTFOUND THEN
1711 l_ei_flag := 'N';
1712 END IF;
1713 close csr_get_ei_flag_17b;
1714
1715
1716 return l_ei_flag;
1717 end check_ei_exempt;
1718
1719
1720 end pay_ca_emp_tax_inf;