[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.3.12010000.2 2008/08/06 06:57:38 ubhat 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_effective_date => p_effective_date
182 ) ;
183 pay_ca_emp_prvtax_inf_api.create_ca_emp_prvtax_inf
184 (p_validate => false
185 ,p_emp_province_tax_inf_id => l_emp_province_tax_inf_id
186 ,p_effective_start_date => l_effective_start_date
187 ,p_effective_end_date => l_effective_end_date
188 ,p_legislation_code => l_legislation_code
189 ,p_assignment_id => l_assignment_id
190 ,p_business_group_id => l_business_group_id
191 ,p_province_code => p_work_province
192 ,p_jurisdiction_code => NULL
193 ,p_tax_credit_amount => NULL
194 ,p_basic_exemption_flag => 'Y'
195 ,p_deduction_code => NULL
196 ,p_extra_info_not_provided => 'Y'
197 ,p_marriage_status => 'N'
198 ,p_no_of_infirm_dependants => 0
199 ,p_non_resident_status => 'N'
200 ,p_disability_status => 'N'
201 ,p_no_of_dependants => 0
202 ,p_annual_dedn => 0
203 ,p_total_expense_by_commission => 0
204 ,p_total_remnrtn_by_commission => 0
205 ,p_prescribed_zone_dedn_amt => 0
206 ,p_additional_tax => 0
207 ,p_prov_override_rate => 0
208 ,p_prov_override_amount => 0
209 ,p_prov_exempt_flag => 'N'
210 ,p_pmed_exempt_flag => 'N'
211 ,p_wc_exempt_flag => 'N'
212 ,p_qpp_exempt_flag => 'N'
213 ,p_tax_calc_method => NULL
214 ,p_other_tax_credit => 0
215 ,p_ca_tax_information_category => NULL
216 ,p_ca_tax_information1 => NULL
217 ,p_ca_tax_information2 => NULL
218 ,p_ca_tax_information3 => NULL
219 ,p_ca_tax_information4 => NULL
220 ,p_ca_tax_information5 => NULL
221 ,p_ca_tax_information6 => NULL
222 ,p_ca_tax_information7 => NULL
223 ,p_ca_tax_information8 => NULL
224 ,p_ca_tax_information9 => NULL
225 ,p_ca_tax_information10 => NULL
226 ,p_ca_tax_information11 => NULL
227 ,p_ca_tax_information12 => NULL
228 ,p_ca_tax_information13 => NULL
229 ,p_ca_tax_information14 => NULL
230 ,p_ca_tax_information15 => NULL
231 ,p_ca_tax_information16 => NULL
232 ,p_ca_tax_information17 => NULL
233 ,p_ca_tax_information18 => NULL
234 ,p_ca_tax_information19 => NULL
235 ,p_ca_tax_information20 => NULL
236 ,p_ca_tax_information21 => NULL
237 ,p_ca_tax_information22 => NULL
238 ,p_ca_tax_information23 => NULL
239 ,p_ca_tax_information24 => NULL
240 ,p_ca_tax_information25 => NULL
241 ,p_ca_tax_information26 => NULL
242 ,p_ca_tax_information27 => NULL
243 ,p_ca_tax_information28 => NULL
244 ,p_ca_tax_information29 => NULL
245 ,p_ca_tax_information30 => NULL
246 ,p_object_version_number => l_object_version_number
247 ,p_prov_lsp_amount => 0
248 ,p_effective_date => p_effective_date
249 ,p_ppip_exempt_flag => 'N'
250 ) ;
251 hr_utility.set_location('province_code'||l_province_code,999);
252
253 commit;
254
255 hr_utility.set_location('pay_ca_emp_tax_inf.create_default_tax_record',99);
256 /****/
257 end create_default_tax_record;
258
259 function get_basic_exemption(p_effective_date date,
260 p_province varchar2 DEFAULT NULL)
261 return number is
262 CURSOR sel_inf_val IS
263 SELECT fnd_number.canonical_to_number(information_value)
264 FROM pay_ca_legislation_info pcli
265 WHERE pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
266 AND ((p_province IS NULL and pcli.jurisdiction_code is null)
267 OR (pcli.jurisdiction_code = p_province))
268 AND p_effective_date BETWEEN pcli.start_date AND pcli.end_date;
269
270 l_basic_exempt_amnt number;
271 begin
272 open sel_inf_val;
273 fetch sel_inf_val into l_basic_exempt_amnt;
274 close sel_inf_val;
275
276 return l_basic_exempt_amnt;
277 end get_basic_exemption;
278
279 procedure get_min_asg_start_date(p_assignment_id in number,
280 p_min_start_date out nocopy date) is
281 cursor csr_min_date is
282 select min(effective_start_date)
283 from per_assignments_f paf
284 where paf.assignment_id = p_assignment_id
285 and paf.assignment_type <> 'A';
286
287 l_min_start_date date;
288 begin
289
290 open csr_min_date;
291 fetch csr_min_date into l_min_start_date;
292 close csr_min_date;
293
294 p_min_start_date := l_min_start_date;
295
296 end get_min_asg_start_date;
297
298
299
300 function get_tax_detail_num
301 (p_assignment_id NUMBER,
302 p_effective_start_date DATE,
303 p_effective_end_date DATE,
304 p_effective_date DATE,
305 p_info_type VARCHAR2)
306 return number is
307
308 cursor csr_tax_num is
309 select
310 tax_credit_amount
311 ,basic_exemption_flag
312 ,additional_tax
313 ,annual_dedn
314 ,total_expense_by_commission
315 ,total_remnrtn_by_commission
316 ,prescribed_zone_dedn_amt
317 ,fed_override_amount
318 ,fed_override_rate
319 ,fed_lsf_amount
320 from pay_ca_emp_fed_tax_info_f peft where
321 peft.assignment_id = p_assignment_id and
322 p_effective_date between peft.effective_start_date and peft.effective_end_date;
323
324 l_tax_credit_amount number;
325 l_basic_exemption_flag varchar2(1);
326 l_additional_tax number;
327 l_annual_dedn number;
328 l_total_expense_by_commission number;
329 l_total_remnrtn_by_commission number;
330 l_prescribed_zone_dedn_amt number;
331 l_fed_override_amount number;
332 l_fed_override_rate number;
333 l_fed_lsf_amount number;
334
335 begin
336
337 open csr_tax_num;
338
339 fetch csr_tax_num into
340 l_tax_credit_amount
341 ,l_basic_exemption_flag
342 ,l_additional_tax
343 ,l_annual_dedn
344 ,l_total_expense_by_commission
345 ,l_total_remnrtn_by_commission
346 ,l_prescribed_zone_dedn_amt
347 ,l_fed_override_amount
348 ,l_fed_override_rate
349 ,l_fed_lsf_amount ;
350
351 if csr_tax_num%NOTFOUND then
352
353 l_tax_credit_amount := NULL;
354 l_basic_exemption_flag := 'Y';
355 l_additional_tax := 0.0;
356 l_annual_dedn := 0.0;
357 l_total_expense_by_commission := 0.0;
358 l_total_remnrtn_by_commission := 0.0;
359 l_prescribed_zone_dedn_amt := 0.0;
360 l_fed_override_amount := 0.0;
361 l_fed_override_rate := 0.0;
362 l_fed_lsf_amount := 0.0;
363 end if;
364 if p_info_type = 'TCA' then
365
366 if l_tax_credit_amount is null and l_basic_exemption_flag = 'Y' then
367
368 select fnd_number.canonical_to_number(information_value)
372 and pcli.jurisdiction_code IS NULL
369 into l_tax_credit_amount
370 from pay_ca_legislation_info pcli
371 where pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
373 and p_effective_date between pcli.start_date and pcli.end_date;
374
375 end if;
376 return l_tax_credit_amount;
377 --
378 elsif p_info_type = 'ADDTAX' then
379 return l_additional_tax;
380 --
381 elsif p_info_type = 'ANNDED' then
382 return l_annual_dedn;
383 --
384 elsif p_info_type = 'PZDN' then
385 return l_prescribed_zone_dedn_amt;
386 --
387 elsif p_info_type = 'EXPCOMM' then
388 return l_total_expense_by_commission;
389 --
390 elsif p_info_type = 'REMCOMM' then
391 return l_total_remnrtn_by_commission;
392 --
393 elsif p_info_type = 'OVERRIDERATE' then
394 return l_fed_override_rate;
395 --
396 elsif p_info_type = 'OVERRIDEAMNT' then
397 return l_fed_override_amount ;
398 --
399 elsif p_info_type = 'LSF' then
400 return l_fed_lsf_amount ;
401 --
402 end if;
403 end get_tax_detail_num;
404
405 function get_tax_detail_char(p_assignment_id in Number,
406 p_effective_start_date in date,
407 p_effective_end_date in date,
408 p_effective_date in date,
409 p_info_type in VARCHAR2)
410 return VARCHAR2 is
411 cursor csr_tax_char is
412 select
413 cpp_qpp_exempt_flag
414 ,fed_exempt_flag
415 ,ei_exempt_flag
416 ,tax_calc_method
417 from pay_ca_emp_fed_tax_info_f peft where
418 peft.assignment_id = p_assignment_id and
419 p_effective_date between peft.effective_start_date and peft.effective_end_date;
420
421 CURSOR csr_get_default_province IS
422 select pcp.province_abbrev, '70-'||pcp.province_code||'-0000' geocode
423 from per_assignments_f paf,
424 hr_locations hl,
425 pay_ca_provinces_v pcp
426 where paf.assignment_id = p_assignment_id
427 and p_effective_date between paf.effective_start_date
428 and paf.effective_end_date
429 and paf.location_id = hl.location_id
430 and hl.region_1 = pcp.province_abbrev;
431
432 CURSOR csr_get_override_province IS
433 select pcp.province_abbrev, '70-'||pcp.province_code||'-0000' geocode
434 from pay_ca_emp_fed_tax_info_f pf,
435 pay_ca_provinces_v pcp
436 where p_effective_date between pf.effective_start_date
437 and pf.effective_end_date
438 and pf.employment_province = pcp.province_abbrev
439 and pf.assignment_id = p_assignment_id;
440
441
442 l_employment_province varchar2(30);
443 l_cpp_qpp_exempt_flag varchar2(1);
444 l_fed_exempt_flag varchar2(1);
445 l_ei_exempt_flag varchar2(1);
446 l_tax_calc_method varchar2(30);
447 l_geocode varchar2(30);
448 begin
449
450 --Get the province of employments
451
452 IF p_info_type = 'GEOCODE' OR p_info_type = 'EMPPROV' THEN
453 OPEN csr_get_override_province;
454 FETCH csr_get_override_province INTO l_employment_province, l_geocode;
455 IF csr_get_override_province%NOTFOUND THEN
456 OPEN csr_get_default_province;
457 FETCH csr_get_default_province INTO l_employment_province, l_geocode;
458 IF csr_get_default_province%NOTFOUND THEN
459 l_employment_province := NULL;
460 l_geocode := '00-000-0000';
461 END IF;
462 CLOSE csr_get_default_province;
463 END IF;
464 CLOSE csr_get_override_province;
465
466 ELSE
467
468 l_geocode := '00-000-0000';
469
470 END IF;
471
472 open csr_tax_char;
473 fetch csr_tax_char into
474 l_cpp_qpp_exempt_flag
475 ,l_fed_exempt_flag
476 ,l_ei_exempt_flag
477 ,l_tax_calc_method;
478
479 if csr_tax_char%NOTFOUND then
480 l_cpp_qpp_exempt_flag := 'N';
481 l_fed_exempt_flag := 'N';
482 l_ei_exempt_flag := 'N';
483 l_tax_calc_method := NULL;
484 end if;
485
486 if p_info_type = 'EMPPROV' then
487 return l_employment_province;
488 --
489 elsif p_info_type = 'GEOCODE' then
490 return l_geocode;
491 --
492 elsif p_info_type = 'FEDEXEMPT' then
493 return l_fed_exempt_flag;
494 --
495 elsif p_info_type = 'EIEXEMPT' then
496 return l_ei_exempt_flag;
497 --
498 elsif p_info_type = 'PPEXEMPT' then
499 return l_cpp_qpp_exempt_flag;
500 --
501 elsif p_info_type = 'CALCMETHOD' then
502 return l_tax_calc_method;
503 --
504 end if;
505 close csr_tax_char;
506 end get_tax_detail_char;
507
508 --
509 function get_tax_detail_dfs(p_assignment_id in Number,
510 p_effective_start_date in date,
511 p_effective_end_date in date,
512 p_effective_date in date,
513 p_info_type in VARCHAR2)
514
515 return varchar2 is
516
517 cursor csr_tax_dfs is
518 select
519 ca_tax_information1
520 from pay_ca_emp_fed_tax_info_f peft where
521 peft.assignment_id = p_assignment_id and
522 p_effective_date between
523 peft.effective_start_date and peft.effective_end_date and
527 begin
524 ca_tax_information_category = 'FED' ;
525
526 l_ca_tax_information1 varchar2(1);
528
529 open csr_tax_dfs;
530
531 fetch csr_tax_dfs into
532 l_ca_tax_information1;
533
534 if csr_tax_dfs%NOTFOUND then
535 l_ca_tax_information1 := 'N';
536 end if;
537
538 if p_info_type = 'STATINDIAN' then
539 return l_ca_tax_information1;
540 end if;
541
542 end get_tax_detail_dfs;
543
544 --
545
546
547 function get_prov_tax_detail_num(p_assignment_id in Number,
548 p_effective_start_date in date,
549 p_effective_end_date in date,
550 p_effective_date in date,
551 p_province_abbrev in varchar2,
552 p_info_type in VARCHAR2)
553 return number is
554
555 cursor csr_tax_num is
556 select
557 tax_credit_amount
558 ,basic_exemption_flag
559 ,no_of_infirm_dependants
560 ,no_of_dependants
561 ,annual_dedn
562 ,total_expense_by_commission
563 ,total_remnrtn_by_commission
564 ,prescribed_zone_dedn_amt
565 ,additional_tax
566 ,prov_override_rate
567 ,prov_override_amount
568 ,prov_lsp_amount
569 from pay_ca_emp_prov_tax_info_f pept where
570 pept.assignment_id = p_assignment_id and
571 p_effective_date between pept.effective_start_date and pept.effective_end_date and
572 pept.province_code = p_province_abbrev;
573
574 l_tax_credit_amount number;
575 l_basic_exemption_flag varchar2(1);
576 l_no_of_infirm_dependants number;
577 l_no_of_dependants number;
578 l_annual_dedn number;
579 l_additional_tax number;
580 l_total_expense_by_commission number;
581 l_total_remnrtn_by_commission number;
582 l_prov_override_amount number;
583 l_prov_override_rate number;
584 l_prescribed_zone_dedn_amt number;
585 l_prov_lsp_amount number;
586
587 begin
588
589 open csr_tax_num;
590
591 fetch csr_tax_num into
592 l_tax_credit_amount
593 ,l_basic_exemption_flag
594 ,l_no_of_infirm_dependants
595 ,l_no_of_dependants
596 ,l_annual_dedn
597 ,l_total_expense_by_commission
598 ,l_total_remnrtn_by_commission
599 ,l_prescribed_zone_dedn_amt
600 ,l_additional_tax
601 ,l_prov_override_rate
602 ,l_prov_override_amount
603 ,l_prov_lsp_amount ;
604
605 if csr_tax_num%NOTFOUND then
606
607 l_tax_credit_amount := NULL;
608 l_basic_exemption_flag := 'Y';
609 l_no_of_infirm_dependants := 0;
610 l_no_of_dependants := 0;
611 l_annual_dedn := 0.0;
612 l_additional_tax := 0.0;
613 l_total_expense_by_commission := 0.0;
614 l_total_remnrtn_by_commission := 0.0;
615 l_prov_override_amount := 0.0;
616 l_prov_override_rate := 0.0;
617 l_prescribed_zone_dedn_amt := 0.0;
618 l_prov_lsp_amount := 0.0;
619
620 end if;
621
622 if p_info_type = 'TCA' then
623
624 if l_tax_credit_amount is null and l_basic_exemption_flag = 'Y' then
625
626 select fnd_number.canonical_to_number(information_value)
627 into l_tax_credit_amount
628 from pay_ca_legislation_info pcli
629 where pcli.information_type = 'BASIC_EXEMPTION_AMOUNT'
630 and pcli.jurisdiction_code = p_province_abbrev
631 and p_effective_date between pcli.start_date and pcli.end_date;
632
633 end if;
634 return l_tax_credit_amount;
635 --
636 elsif p_info_type = 'NUMDEP' then
637 return l_no_of_dependants;
638 --
639 elsif p_info_type = 'INFDEP' then
640 return l_no_of_infirm_dependants;
641 --
642 elsif p_info_type = 'ADDTAX' then
643 return l_additional_tax;
644 --
645 elsif p_info_type = 'ANNDED' then
646 return l_annual_dedn;
647 --
648 elsif p_info_type = 'EXPCOMM' then
649 return l_total_expense_by_commission;
650 --
651 elsif p_info_type = 'REMCOMM' then
652 return l_total_remnrtn_by_commission;
653 --
654 elsif p_info_type = 'PZDN' then
655 return l_prescribed_zone_dedn_amt;
656 --
657 elsif p_info_type = 'OVERRIDERATE' then
658 return l_prov_override_rate;
659 --
660 elsif p_info_type = 'OVERRIDEAMNT' then
661 return l_prov_override_amount ;
662 --
663 elsif p_info_type = 'LSP' then
664 return l_prov_lsp_amount ;
665 --
666 end if;
667 end get_prov_tax_detail_num;
668
669 function get_prov_tax_detail_char(p_assignment_id in Number,
670 p_effective_start_date in date,
671 p_effective_end_date in date,
672 p_effective_date in date,
673 p_province_abbrev in varchar2,
674 p_info_type in VARCHAR2)
675 return VARCHAR2 is
676
677 cursor csr_prov_tax_char is
678 select
679 jurisdiction_code
680 , extra_info_not_provided
681 , marriage_status
682 , non_resident_status
683 , disability_status
684 ,prov_exempt_flag
685 ,pmed_exempt_flag
686 ,wc_exempt_flag
687 ,qpp_exempt_flag
688 ,tax_calc_method
692 p_effective_date between pept.effective_start_date and pept.effective_end_date and
689 ,ppip_exempt_flag
690 from pay_ca_emp_prov_tax_info_f pept where
691 pept.assignment_id = p_assignment_id and
693 pept.province_code = p_province_abbrev;
694
695 l_jurisdiction_code varchar2(11);
696 l_extra_info_not_provided varchar2(11);
697 l_marriage_status varchar2(30);
698 l_non_resident_status varchar2(30);
699 l_disability_status varchar2(30);
700 l_prov_exempt_flag varchar2(30);
701 l_pmed_exempt_flag varchar2(30);
702 l_wc_exempt_flag varchar2(30);
703 l_qpp_exempt_flag varchar2(30);
704 l_tax_calc_method varchar2(30);
705 l_ppip_exempt_flag varchar2(30);
706
707 begin
708 open csr_prov_tax_char;
709 fetch csr_prov_tax_char into
710 l_jurisdiction_code
711 ,l_extra_info_not_provided
712 ,l_marriage_status
713 ,l_non_resident_status
714 ,l_disability_status
715 ,l_prov_exempt_flag
716 ,l_pmed_exempt_flag
717 ,l_wc_exempt_flag
718 ,l_qpp_exempt_flag
719 ,l_tax_calc_method
720 ,l_ppip_exempt_flag;
721
722 if csr_prov_tax_char%NOTFOUND then
723
724 l_extra_info_not_provided := 'Y';
725 l_marriage_status := NULL;
726 l_non_resident_status := NULL;
727 l_disability_status := NULL;
728 l_prov_exempt_flag := NULL;
729 l_pmed_exempt_flag := NULL;
730 l_wc_exempt_flag := NULL;
731 l_tax_calc_method := NULL;
732 l_ppip_exempt_flag := NULL;
733
734 end if;
735
736
737 if p_info_type = 'NOTPROV' then
738 return l_extra_info_not_provided;
739 --
740 elsif p_info_type = 'MARRIED' then
741 return l_marriage_status;
742 --
743 elsif p_info_type = 'NONRES' then
744 return l_non_resident_status;
745 --
746 elsif p_info_type = 'DISABLE' then
747 return l_disability_status;
748 --
749 elsif p_info_type = 'PROVEXEMPT' then
750 return l_prov_exempt_flag;
751 --
752 elsif p_info_type = 'PMEDEXEMPT' then
753 return l_pmed_exempt_flag;
754 --
755 elsif p_info_type = 'WCBEXEMPT' then
756 return l_wc_exempt_flag;
757 --
758 elsif p_info_type = 'QPPEXEMPT' then
759 return l_qpp_exempt_flag;
760 --
761 elsif p_info_type = 'CALCMETHOD' then
762 return l_tax_calc_method;
763 --
764 elsif p_info_type = 'PPIPEXEMPT' then
765 return l_ppip_exempt_flag;
766 --
767 end if;
768 close csr_prov_tax_char;
769 end get_prov_tax_detail_char;
770
771 --
772
773 function get_prov_tax_detail_dfs(p_assignment_id in Number,
774 p_effective_start_date in date,
775 p_effective_end_date in date,
776 p_effective_date in date,
777 p_province_abbrev in varchar2,
778 p_info_type in VARCHAR2)
779 return VARCHAR2 is
780 cursor csr_prov_tax_dfs is
781 select
782 ca_tax_information1,
783 ca_tax_information2
784 from pay_ca_emp_prov_tax_info_f pept where
785 pept.assignment_id = p_assignment_id and
786 p_effective_date between
787 pept.effective_start_date and pept.effective_end_date and
788 -- ca_tax_information_category = p_province_abbrev;
789 ca_tax_information_category = 'PROV' and
790 p_province_abbrev = province_code;
791
792 l_ca_tax_information1 varchar2(1);
793 l_ca_tax_information2 varchar2(1);
794 begin
795
796 /*The value for the Indian status flex field should only be considered in the
797 provincial tax calculation if the province is one of the three provinces below */
798
799 if p_province_abbrev <> 'NT' and
800 p_province_abbrev <> 'NU' and
801 p_province_abbrev <> 'QC' then
802
803 /* If the province is Manitoba then return the over 65 flag */
804
805 if p_province_abbrev = 'MB' then
806
807 open csr_prov_tax_dfs;
808
809 fetch csr_prov_tax_dfs into
810 l_ca_tax_information1,
811 l_ca_tax_information2;
812
813 if csr_prov_tax_dfs%NOTFOUND then
814 l_ca_tax_information2 := 'N';
815 end if;
816
817 close csr_prov_tax_dfs;
818 else
819 l_ca_tax_information2 := 'N';
820 end if;
821
822 l_ca_tax_information1 := 'N';
823
824 else
825 open csr_prov_tax_dfs;
826
827 fetch csr_prov_tax_dfs into
828 l_ca_tax_information1,
829 l_ca_tax_information2;
830
831 if csr_prov_tax_dfs%NOTFOUND then
832 l_ca_tax_information1 := 'N';
833 end if;
834
835 close csr_prov_tax_dfs;
836
837 l_ca_tax_information2 := 'N';
838
839 end if;
840
841 if p_info_type = 'STATINDIAN' then
842 return l_ca_tax_information1;
843 elsif p_info_type = 'OVER65' then
844 return l_ca_tax_information2;
845 end if;
846
847 end get_prov_tax_detail_dfs;
848
849 --
850
851 function get_address(p_person_id in Number,
852 p_effective_date in date,
856 p_effective_date in date) is
853 address_line_no in number
854 ) return VARCHAR2 is
855 cursor csr_address( p_person_id in number,
857
858 select substr(addr.address_line1,1,37) ,
859 substr(addr.address_line2,1,37) ,
860 substr(addr.address_line3,1,37) ,
861 rtrim(substr(addr.town_or_city,1,23)) ||' '||addr.region_1||' '||addr.postal_code
862 from per_addresses addr
863 WHERE addr.person_id = p_person_id
864 AND addr.primary_flag = 'Y'
865 AND p_effective_date between
866 addr.date_from and nvl(addr.date_to, p_effective_date);
867
868 l_emp_addr_line1 varchar2(80);
869 l_emp_addr_line2 varchar2(80);
870 l_emp_addr_line3 varchar2(80);
871 l_emp_addr_line4 varchar2(180);
872 l_emp_addr_line varchar2(180);
873
874 begin
875
876 open csr_address(p_person_id,p_effective_date);
877
878 fetch csr_address into l_emp_addr_line1,
879 l_emp_addr_line2,
880 l_emp_addr_line3,
881 l_emp_addr_line4;
882
883 if csr_address%NOTFOUND then
884 l_emp_addr_line1 := 'ADDRESS NOT IN THE FILE';
885 l_emp_addr_line2 := 'ADDRESS NOT IN THE FILE';
886 l_emp_addr_line3 := 'ADDRESS NOT IN THE FILE';
887 l_emp_addr_line4 := 'ADDRESS NOT IN THE FILE';
888 end if;
889 close csr_address;
890 --
891 if address_line_no = 1 then
892 l_emp_addr_line := l_emp_addr_line1;
893 elsif address_line_no = 2 then
894 l_emp_addr_line := l_emp_addr_line2;
895 elsif address_line_no = 3 then
896 l_emp_addr_line := l_emp_addr_line3;
897 elsif address_line_no = 4 then
898 l_emp_addr_line := l_emp_addr_line4;
899 end if;
900
901 return l_emp_addr_line;
902
903 end get_address;
904
905 function get_salary_basis(p_salary_basis_id in Number)
906 return VARCHAR2 is
907 cursor csr_salary_basis(l_pay_basis_id in number) is
908 select pay_basis from per_pay_bases
909 where pay_basis_id = l_pay_basis_id;
910
911 l_salary_basis varchar2(30);
912 begin
913 open csr_salary_basis(p_salary_basis_id);
914 fetch csr_salary_basis into l_salary_basis;
915 if csr_salary_basis%NOTFOUND then
916 l_salary_basis := 'NOT FOUND';
917 end if;
918 close csr_salary_basis;
919 return l_salary_basis;
920 end get_salary_basis;
921
922
923 function get_base_salary(p_assignment_id in Number,
924 p_effective_date in date,
925 p_salary_basis_id in number)
926 return VARCHAR2 is
927 cursor csr_base_salary(l_assignment_id in number,
928 l_effective_date in date,
929 l_input_value_id in number) is
930 select decode(instr(peev.screen_entry_value,'.'),
931 0,
932 peev.screen_entry_value|| '.00',
933 peev.screen_entry_value
934 )
935 from pay_element_entries_f pee,
936 pay_element_entry_values_f peev,
937 pay_input_values_f piv
938 WHERE l_effective_date
939 between pee.effective_start_date AND pee.effective_end_date
940 AND pee.element_entry_id = peev.element_entry_id
941 AND pee.entry_type = 'E'
942 AND pee.assignment_id = l_assignment_id
943 AND l_effective_date
944 between peev.effective_start_date and peev.effective_end_date
945 AND peev.input_value_id+0 = piv.input_value_id
946 AND l_effective_date
947 between piv.effective_start_date AND piv.effective_end_date
948 AND piv.input_value_id = l_input_value_id;
949
950 cursor csr_input_value_id(l_pay_basis_id in number) is
951 select input_value_id from per_pay_bases
952 where pay_basis_id = l_pay_basis_id;
953
954 l_base_salary varchar2(30);
955 l_input_value_id number;
956 begin
957
958 open csr_input_value_id(p_salary_basis_id);
959 fetch csr_input_value_id into l_input_value_id;
960 if csr_input_value_id%NOTFOUND then
961 l_base_salary := 'NOT ENTERED';
962 else
963 open csr_base_salary(p_assignment_id,
964 p_effective_date,
965 l_input_value_id) ;
966 fetch csr_base_salary into l_base_salary;
967 if csr_base_salary%NOTFOUND then
968 l_base_salary := 'NOT ENTERED';
969 end if;
970 close csr_base_salary;
971 end if;
972 close csr_input_value_id;
973 --
974 return l_base_salary;
975 end get_base_salary;
976
977 function get_summary_info(p_assignment_action_id in Number,
978 p_information_type in varchar2,
979 p_dimension in varchar2
980 ) return number is
981 l_value number;
982 begin
983 if p_information_type <> 'DEDUCTIONS_SUMM' then
984 select decode(p_dimension,'CURRENT',amount_current,'YTD',amount_ytd)
985 into l_value
986 from pay_ca_soe_summ_balances_v pcs
987 where pcs.assignment_action_id = p_assignment_action_id
988 and pcs.base_bal_name = decode(p_information_type,'GROSS_PAY_SUMM', /*balance_name changed to base_bal_name against bug#5169734*/
989 'Gross Pay',
993 'Gross Earnings',
990 'TAXABLE_BENEFIT_SUMM',
991 'Taxable Benefits',
992 'GROSS_EARNINGS_SUMM',
994 'TAXES_SUMM',
995 'Tax Deductions',
996 'NET_PAY_SUMM',
997 'Payments');
998 else
999 select sum(decode(p_dimension,'CURRENT',amount_current,'YTD',amount_ytd))
1000 into l_value
1001 from pay_ca_soe_summ_balances_v pcs
1002 where pcs.assignment_action_id = p_assignment_action_id
1003 and pcs.balance_name in (
1004 'Pre Tax Deductions',
1005 'Involuntary Deductions',
1006 'Voluntary Deductions'
1007 );
1008 end if;
1009 return l_value;
1010 EXCEPTION
1011 when no_data_found
1012 then return 0;
1013
1014 end get_summary_info;
1015
1016 function check_age_under18_or_over70(p_payroll_action_id in Number,
1017 p_date_of_birth in Date) return VARCHAR2 is
1018 l_check_age VARCHAR2(1);
1019 l_effective_date Date;
1020 -- Get
1021
1022 CURSOR csr_get_effective_date(l_payroll_action_id in Number) IS
1023 SELECT effective_date
1024 FROM pay_payroll_actions
1025 WHERE payroll_action_id = l_payroll_action_id;
1026
1027 begin
1028 l_check_age := 'N';
1029
1030 open csr_get_effective_date(p_payroll_action_id);
1031 fetch csr_get_effective_date into l_effective_date;
1032 close csr_get_effective_date;
1033
1034 if (( add_months(trunc(p_date_of_birth,'MONTH'),(18*12)+1)) <= l_effective_date ) then
1035 if ( l_effective_date >= ( add_months(trunc(p_date_of_birth,'MONTH'),(70*12)+1))) then
1036 l_check_age := 'Y';
1037 else
1038 l_check_age := 'N';
1039 end if;
1040 else
1041 l_check_age := 'Y';
1042
1043 end if;
1044
1045 return l_check_age;
1046 end check_age_under18_or_over70;
1047
1048 function check_age_under18(p_payroll_action_id in Number,
1049 p_date_of_birth in Date) return VARCHAR2 is
1050 l_check_age VARCHAR2(1);
1051 l_effective_date Date;
1052 -- Get
1053
1054 CURSOR csr_get_effective_date(l_payroll_action_id in Number) IS
1055 SELECT effective_date
1056 FROM pay_payroll_actions
1057 WHERE payroll_action_id = l_payroll_action_id;
1058
1059 begin
1060 l_check_age := 'N';
1061
1062 open csr_get_effective_date(p_payroll_action_id);
1063 fetch csr_get_effective_date into l_effective_date;
1064 close csr_get_effective_date;
1065
1066 if (( add_months(trunc(p_date_of_birth,'MONTH'),(18*12)+1)) <= l_effective_date ) then
1067 l_check_age := 'N';
1068 else
1069 l_check_age := 'Y';
1070
1071 end if;
1072
1073 return l_check_age;
1074 end check_age_under18;
1075
1076
1077 function retro_across_calendar_years (p_element_entry_id in number,
1078 p_payroll_action_id in number)
1079 return varchar2 is
1080
1081 l_creator_type varchar2(30);
1082 l_source_id number;
1083 l_source_asg_action_id number;
1084 l_originating_date date;
1085 l_current_date date;
1086 l_check_years varchar2(1);
1087
1088 cursor csr_get_current_date is
1089 select effective_date
1090 from pay_payroll_actions
1091 where payroll_action_id = p_payroll_action_id;
1092
1093 cursor csr_get_ele_entry_info is
1094 select creator_type, source_id, source_asg_action_id
1095 from pay_element_entries_f
1096 where element_entry_id = p_element_entry_id;
1097
1098 cursor csr_get_orig_date_rr(l_run_result_id in number) is
1099 select ppa.effective_date
1100 from pay_run_results prr,
1101 pay_assignment_actions paa,
1102 pay_payroll_actions ppa
1103 where prr.run_result_id = l_run_result_id
1104 and prr.assignment_action_id = paa.assignment_action_id
1105 and paa.payroll_action_id = ppa.payroll_action_id;
1106
1107 cursor csr_get_orig_date_asgact(l_asg_action_id in number) is
1108 select ppa.effective_date
1109 from pay_assignment_actions paa,
1110 pay_payroll_actions ppa
1111 where paa.assignment_action_id = l_asg_action_id
1112 and ppa.payroll_action_id = paa.payroll_action_id;
1113
1114 begin
1115
1116 open csr_get_current_date;
1117 fetch csr_get_current_date
1118 into l_current_date;
1119 close csr_get_current_date;
1120
1121 open csr_get_ele_entry_info;
1122 fetch csr_get_ele_entry_info
1123 into l_creator_type, l_source_id, l_source_asg_action_id;
1124 close csr_get_ele_entry_info;
1125
1126 if l_creator_type = 'RR' then
1127
1128 open csr_get_orig_date_rr(l_source_id);
1129 fetch csr_get_orig_date_rr
1130 into l_originating_date;
1131 close csr_get_orig_date_rr;
1132
1133 if to_char(l_originating_date,'YYYY') = to_char(l_current_date,'YYYY') then
1134 l_check_years := 'N';
1135 else
1136 l_check_years := 'Y';
1137 end if;
1138
1139 elsif l_creator_type = 'EE' then
1140
1141 open csr_get_orig_date_asgact(l_source_asg_action_id);
1142 fetch csr_get_orig_date_asgact
1143 into l_originating_date;
1144 close csr_get_orig_date_asgact;
1145
1146 if to_char(l_originating_date,'YYYY') = to_char(l_current_date,'YYYY') then
1147 l_check_years := 'N';
1148 else
1149 l_check_years := 'Y';
1150 end if;
1151
1152 else
1153 l_check_years := 'N';
1154 end if;
1155
1156 return l_check_years;
1157
1158 end retro_across_calendar_years;
1159
1160 /*****************************************************************************
1161 Delete_fed_tax_rule procedure calls
1162 pay_ca_emp_fedtax_inf_api.delete_ca_emp_fedtax_inf procedure for updating
1163 Effective_End_Date of tax records in PAY_CA_EMP_FED_TAX_INFO_F table.
1164
1165 pay_ca_emp_prvtax_inf_api.delete_ca_emp_prvtax_inf procedure for updating
1166 Effective_End_Date of tax records in PAY_CA_EMP_PROV_TAX_INFO_F table.
1167
1168 *****************************************************************************/
1169
1170 procedure delete_fed_tax_rule
1171 (p_effective_date in date
1172 ,p_datetrack_delete_mode in varchar2
1173 ,p_assignment_id in number
1174 ,p_delete_routine in varchar2
1175 ,p_effective_start_date out nocopy date
1176 ,p_effective_end_date out nocopy date
1177 ,p_object_version_number out nocopy number
1178 ) is
1179 --
1180 -- Declare cursors and local variables
1181 --
1182 l_proc varchar2(72) := 'delete_fed_tax_rule';
1183 l_effective_date date;
1184 l_emp_prov_tax_inf_id pay_ca_emp_prov_tax_info_f.emp_province_tax_inf_id%TYPE;
1185 l_emp_fed_tax_inf_id pay_ca_emp_fed_tax_info_f.emp_fed_tax_inf_id%TYPE;
1186 l_effective_start_date pay_ca_emp_fed_tax_info_f.effective_start_date%TYPE;
1187 l_effective_end_date pay_ca_emp_fed_tax_info_f.effective_end_date%TYPE;
1188 l_object_version_number pay_ca_emp_fed_tax_info_f.object_version_number%TYPE;
1189 l_tmp_effective_start_date pay_ca_emp_fed_tax_info_f.effective_start_date%TYPE;
1190 l_tmp_effective_end_date pay_ca_emp_fed_tax_info_f.effective_end_date%TYPE;
1191 l_tmp_object_version_number pay_ca_emp_fed_tax_info_f.object_version_number%TYPE;
1192 --
1193 l_exit_quietly exception;
1194 --
1195 cursor csr_fed_rule is
1196 select fed.emp_fed_tax_inf_id, fed.object_version_number
1197 from pay_ca_emp_fed_tax_info_f fed
1198 where fed.assignment_id = p_assignment_id
1199 and l_effective_date between fed.effective_start_date
1200 and fed.effective_end_date;
1201 --
1202 cursor csr_prov_rule is
1206 and l_effective_date between sta.effective_start_date
1203 select sta.emp_province_tax_inf_id, sta.object_version_number
1204 from pay_ca_emp_prov_tax_info_f sta
1205 where sta.assignment_id = p_assignment_id
1207 and sta.effective_end_date;
1208 --
1209 --
1210 begin
1211 --
1212 --
1213 hr_utility.set_location(' Entering: '||'pay_ca_emp_tax_inf'||l_proc, 10);
1214 l_effective_date := trunc(p_effective_date);
1215 --
1216 -- Validate that a federal tax rule exists for this assignment
1217 --
1218 open csr_fed_rule;
1219 fetch csr_fed_rule into l_emp_fed_tax_inf_id, l_object_version_number;
1220 if csr_fed_rule%notfound then
1221 close csr_fed_rule;
1222 raise l_exit_quietly;
1223 end if;
1224 close csr_fed_rule;
1225
1226 hr_utility.set_location(l_proc, 20);
1227 --
1228 if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
1229 hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
1230 hr_utility.raise_error;
1231 end if;
1232 --
1233 -- Validate that this routine is called from Assignment code
1234 --
1235
1236 hr_utility.set_location(l_proc, 30);
1237
1238 if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
1239 hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
1240 hr_utility.raise_error;
1241 end if;
1242 --
1243 open csr_prov_rule;
1244 loop
1245 fetch csr_prov_rule into l_emp_prov_tax_inf_id, l_tmp_object_version_number;
1246 exit when csr_prov_rule%notfound;
1247 --
1248 -- Call delete_tax_rules API here passing in l_assignment_id, l_state_code
1249 pay_ca_emp_prvtax_inf_api.delete_ca_emp_prvtax_inf(
1250 p_validate => NULL -- check whether NULL is correct.
1251 ,p_emp_province_tax_inf_id => l_emp_prov_tax_inf_id
1252 ,p_effective_start_date => l_tmp_effective_start_date
1253 ,p_effective_end_date => l_tmp_effective_end_date
1254 ,p_object_version_number => l_tmp_object_version_number
1255 ,p_effective_date => l_effective_date
1256 ,p_datetrack_mode => p_datetrack_delete_mode
1257 );
1258
1259 --
1260 end loop;
1261 close csr_prov_rule;
1262
1263 hr_utility.set_location(l_proc, 40);
1264 --
1265 -- Need to check whether this procedure should be called
1266 /* maintain_wc(
1267 p_emp_fed_tax_rule_id => l_emp_fed_tax_inf_id
1268 ,p_effective_start_date => l_effective_start_date
1269 ,p_effective_end_date => l_effective_end_date
1270 ,p_effective_date => l_effective_date
1271 ,p_datetrack_mode => p_datetrack_delete_mode
1272 );
1273 */
1274 --
1275 --pay_fed_del.del(p_emp_fed_tax_rule_id => l_emp_fed_tax_inf_id
1276 pay_ca_emp_fedtax_inf_api.delete_ca_emp_fedtax_inf(
1277 p_validate => NULL -- check whether NULL is correct.
1278 ,p_emp_fed_tax_inf_id => l_emp_fed_tax_inf_id
1279 ,p_effective_start_date => l_effective_start_date
1280 ,p_effective_end_date => l_effective_end_date
1281 ,p_object_version_number => l_object_version_number
1282 ,p_effective_date => l_effective_date
1283 ,p_datetrack_mode => p_datetrack_delete_mode
1284 );
1285 --
1286 --
1287 hr_utility.set_location(l_proc, 50);
1288 --
1289 -- Set all output arguments
1290 --
1291 p_effective_start_date := l_effective_start_date;
1292 p_effective_end_date := l_effective_end_date;
1293 p_object_version_number := l_object_version_number;
1294 --
1295 hr_utility.set_location(' Leaving: '||'pay_ca_emp_tax_inf'||l_proc, 60);
1296 --
1297 exception
1298 --
1299 when l_exit_quietly then
1300 --
1301 p_effective_start_date := null;
1302 p_effective_end_date := null;
1303 p_object_version_number := null;
1304 --
1305 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 70);
1306 --
1307 --
1308 end delete_fed_tax_rule;
1309
1310 /*****************************************************************************
1311 Maintain_ca_employee_taxes procedure fetches Assignment_id
1312 values for the given period_of_service_id
1313 and calls Delete_fed_tax_rule procedure.
1314 *****************************************************************************/
1315
1316 procedure maintain_ca_employee_taxes
1317 ( p_period_of_service_id in number,
1318 p_effective_date in date
1319 ,p_datetrack_mode in varchar2 default null
1320 ,p_delete_routine in varchar2 default null
1321 ) is
1322
1323 TYPE assign_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1324 --
1325 -- Declare cursors and local variables
1326 --
1327 l_proc varchar2(72) := 'maintain_ca_employee_taxes';
1328 l_counter number := 0;
1329 l_effective_date date;
1330 l_assignment_id per_assignments_f.assignment_id%TYPE;
1331 l_fed_object_version_number
1332 pay_ca_emp_fed_tax_info_f.object_version_number%TYPE;
1333 l_fed_eff_start_date pay_ca_emp_fed_tax_info_f.effective_start_date%TYPE;
1334 l_fed_eff_end_date pay_ca_emp_fed_tax_info_f.effective_end_date%TYPE;
1335 l_temp_num number;
1336 l_cnt number;
1337 l_assignment_tbl assign_tbl_type;
1338
1339 l_exit_quietly exception;
1340 --
1341 -- rmonge Bug fix 3599825.
1342
1343 cursor csr_asg_id(p_csr_assignment_id number) is
1344 select null
1345 from per_assignments_f asg,
1346 hr_organization_information bus
1347 where asg.assignment_id = p_csr_assignment_id
1348 and bus.organization_id = asg.business_group_id
1349 and bus.org_information9 = 'CA'
1350 and bus.org_information_context = 'Business Group Information'
1351 and p_effective_date between asg.effective_start_date
1352 and asg.effective_end_date ;
1353
1354 cursor csr_adr_asg_id is
1355 select asg.assignment_id
1356 from per_assignments_f asg,
1357 per_periods_of_service pps
1358 where asg.person_id = pps.person_id
1359 and pps.period_of_service_id = p_period_of_service_id
1360 and p_effective_date between asg.effective_start_date
1361 and asg.effective_end_date;
1362 --
1363 --
1364 begin
1365 --
1366 hr_utility.set_location('Entering: '||'pay_ca_emp_tax_inf'||l_proc, 10);
1367 --
1368 l_effective_date := trunc(p_effective_date);
1369 --
1370 hr_utility.set_location(l_proc, 20);
1371 --
1372 -- First check if geocode has been installed or not. If no geocodes
1373 -- installed then return because there is nothing to be done by this
1374 -- procedure
1375 if hr_general.chk_maintain_tax_records = 'N' then
1376 raise l_exit_quietly;
1377 end if;
1378
1379 hr_utility.set_location(l_proc, 30);
1380
1381 if p_datetrack_mode NOT IN ('ZAP',
1382 'DELETE',
1383 'UPDATE',
1384 'CORRECTION',
1385 'UPDATE_OVERRIDE',
1386 'UPDATE_CHANGE_INSERT') then
1387 hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
1388 hr_utility.raise_error;
1389 elsif p_datetrack_mode in ('ZAP', 'DELETE') then
1390 hr_utility.set_location(l_proc, 40);
1391 --
1392 l_cnt := 0;
1393 for l_assgn_rec in csr_adr_asg_id loop
1394 l_cnt := l_cnt + 1;
1395 l_assignment_tbl(l_cnt) := l_assgn_rec.assignment_id;
1396 end loop;
1397
1398 --
1399
1400 hr_utility.set_location('number of assignments '||l_assignment_tbl.count, 45);
1401
1402 for l_cnt in 1..l_assignment_tbl.last loop
1403 open csr_asg_id(l_assignment_tbl(l_cnt));
1404 fetch csr_asg_id into l_temp_num;
1405 if csr_asg_id%notfound then
1406 close csr_asg_id;
1407 hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
1408 hr_utility.raise_error;
1409 end if;
1410 close csr_asg_id;
1411 --
1412 hr_utility.set_location(l_proc, 50);
1413 hr_utility.set_location('assignment id '||l_assignment_tbl(l_cnt), 55);
1414 delete_fed_tax_rule(
1415 p_effective_date => l_effective_date
1416 ,p_datetrack_delete_mode => p_datetrack_mode
1417 ,p_assignment_id => l_assignment_tbl(l_cnt)
1418 ,p_delete_routine => p_delete_routine
1419 ,p_effective_start_date => l_fed_eff_start_date
1420 ,p_effective_end_date => l_fed_eff_end_date
1421 ,p_object_version_number => l_fed_object_version_number
1422 );
1423 end loop;
1424
1425 end if; -- datetrack mode is ZAP?
1426 --
1427 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 60);
1428 --
1429 exception
1430 --
1431 when l_exit_quietly then
1432 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 70);
1433 --
1434 end maintain_ca_employee_taxes;
1435
1436 procedure delete_tax_record
1437 ( p_period_of_service_id in number,
1438 p_final_process_date in date) is
1439
1440 -- Declare cursors and local variables
1441 --
1442 l_proc varchar2(72) := 'delete_tax_record';
1443
1444 begin
1445
1446 --
1447 hr_utility.set_location('Entering: '||'pay_ca_emp_tax_inf'||l_proc, 10);
1448 --
1449
1450 maintain_ca_employee_taxes
1451 ( p_period_of_service_id => p_period_of_service_id,
1452 p_effective_date => p_final_process_date,
1453 p_datetrack_mode => 'DELETE',
1454 p_delete_routine => 'ASSIGNMENT'
1455 );
1456
1457 hr_utility.set_location('Leaving: '||'pay_ca_emp_tax_inf'||l_proc, 20);
1458
1459 exception
1460 --
1461 when others then
1462 hr_utility.set_location(' Leaving:'||'pay_ca_emp_tax_inf'||l_proc, 30);
1463 --
1464 end delete_tax_record;
1465
1466 end pay_ca_emp_tax_inf;