[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EMP_PRVTAX_INF_API
Source
1 Package Body pay_ca_emp_prvtax_inf_api as
2 /* $Header: pycprapi.pkb 120.2.12000000.1 2007/01/17 18:11:39 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_ca_emp_prvtax_inf_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_ca_emp_prvtax_inf >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ca_emp_prvtax_inf
13 (p_validate in boolean default false
14 ,p_emp_province_tax_inf_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_legislation_code in varchar2
18 ,p_assignment_id in number
19 ,p_business_group_id in number
20 ,p_province_code in varchar2
21 ,p_jurisdiction_code in varchar2 default null
22 ,p_tax_credit_amount in number default null
23 ,p_basic_exemption_flag in varchar2 default null
24 ,p_deduction_code in varchar2 default null
25 ,p_extra_info_not_provided in varchar2 default null
26 ,p_marriage_status in varchar2 default null
27 ,p_no_of_infirm_dependants in number default null
28 ,p_non_resident_status in varchar2 default null
29 ,p_disability_status in varchar2 default null
30 ,p_no_of_dependants in number default null
31 ,p_annual_dedn in number default null
32 ,p_total_expense_by_commission in number default null
33 ,p_total_remnrtn_by_commission in number default null
34 ,p_prescribed_zone_dedn_amt in number default null
35 ,p_additional_tax in number default null
36 ,p_prov_override_rate in number default null
37 ,p_prov_override_amount in number default null
38 ,p_prov_exempt_flag in varchar2 default null
39 ,p_pmed_exempt_flag in varchar2 default null
40 ,p_wc_exempt_flag in varchar2 default null
41 ,p_qpp_exempt_flag in varchar2 default null
42 ,p_tax_calc_method in varchar2 default null
43 ,p_other_tax_credit in number default null
44 ,p_ca_tax_information_category in varchar2 default null
45 ,p_ca_tax_information1 in varchar2 default null
46 ,p_ca_tax_information2 in varchar2 default null
47 ,p_ca_tax_information3 in varchar2 default null
48 ,p_ca_tax_information4 in varchar2 default null
49 ,p_ca_tax_information5 in varchar2 default null
50 ,p_ca_tax_information6 in varchar2 default null
51 ,p_ca_tax_information7 in varchar2 default null
52 ,p_ca_tax_information8 in varchar2 default null
53 ,p_ca_tax_information9 in varchar2 default null
54 ,p_ca_tax_information10 in varchar2 default null
55 ,p_ca_tax_information11 in varchar2 default null
56 ,p_ca_tax_information12 in varchar2 default null
57 ,p_ca_tax_information13 in varchar2 default null
58 ,p_ca_tax_information14 in varchar2 default null
59 ,p_ca_tax_information15 in varchar2 default null
60 ,p_ca_tax_information16 in varchar2 default null
61 ,p_ca_tax_information17 in varchar2 default null
62 ,p_ca_tax_information18 in varchar2 default null
63 ,p_ca_tax_information19 in varchar2 default null
64 ,p_ca_tax_information20 in varchar2 default null
65 ,p_ca_tax_information21 in varchar2 default null
66 ,p_ca_tax_information22 in varchar2 default null
67 ,p_ca_tax_information23 in varchar2 default null
68 ,p_ca_tax_information24 in varchar2 default null
69 ,p_ca_tax_information25 in varchar2 default null
70 ,p_ca_tax_information26 in varchar2 default null
71 ,p_ca_tax_information27 in varchar2 default null
72 ,p_ca_tax_information28 in varchar2 default null
73 ,p_ca_tax_information29 in varchar2 default null
74 ,p_ca_tax_information30 in varchar2 default null
75 ,p_object_version_number out nocopy number
76 ,p_prov_lsp_amount in number default null
77 ,p_effective_date in date
78 ,p_ppip_exempt_flag in varchar2 default null
79 ) is
80 --
81 -- Declare cursors and local variables
82 --
83 l_emp_province_tax_inf_id pay_ca_emp_prov_tax_info_f.emp_province_tax_inf_id%TYPE;
84 l_effective_start_date pay_ca_emp_prov_tax_info_f.effective_start_date%TYPE;
85 l_effective_end_date pay_ca_emp_prov_tax_info_f.effective_end_date%TYPE;
86 l_proc varchar2(72) := g_package||'create_ca_emp_prvtax_inf';
87 l_object_version_number pay_ca_emp_prov_tax_info_f.object_version_number%TYPE;
88 l_rec_present varchar2(1);
89 l_tax_credit_amount number;
90 l_additional_tax number;
91 l_annual_dedn number;
92 l_total_expense_by_commission number;
93 l_total_remnrtn_by_commission number;
94 l_prescribed_zone_dedn_amt number;
95 l_other_tax_credit number;
96 l_prov_override_amount number;
97 l_prov_override_rate number;
98 l_no_of_dependants number;
99 l_no_of_infirm_dependants number;
100 l_prov_lsp_amount number;
101 --
102 begin
103 --
104 hr_utility.set_location('Entering:'|| l_proc, 10);
105 --
106 -- Issue a savepoint if operating in validation only mode
107 --
108 savepoint create_ca_emp_prvtax_inf;
109 --
110 hr_utility.set_location(l_proc, 20);
111 --
112 -- Process Logic
113 --
114
115 perform_assignment_validation(p_assignment_id => p_assignment_id,
116 p_effective_date => p_effective_date);
117 --
118 tax_record_already_present(p_assignment_id => p_assignment_id,
119 p_province_code => p_province_code,
120 p_effective_date => p_effective_date,
121 p_rec_present => l_rec_present) ; /* added */
122
123 if l_rec_present <> 'Y' then
124
125 /* Defaulting all the number fields which are null */
126
127 l_tax_credit_amount := convert_null_to_zero(p_tax_credit_amount);
128 l_additional_tax := convert_null_to_zero(p_additional_tax);
129 l_annual_dedn := convert_null_to_zero(p_annual_dedn);
130 l_total_expense_by_commission := convert_null_to_zero(p_total_expense_by_commission);
131 l_total_remnrtn_by_commission := convert_null_to_zero(p_total_remnrtn_by_commission);
132 l_prescribed_zone_dedn_amt := convert_null_to_zero(p_prescribed_zone_dedn_amt);
133 l_other_tax_credit := convert_null_to_zero(p_other_tax_credit);
134 l_prov_override_amount := convert_null_to_zero(p_prov_override_amount);
135 l_prov_override_rate := convert_null_to_zero(p_prov_override_rate);
136 l_no_of_dependants := convert_null_to_zero(p_no_of_dependants);
137 l_no_of_infirm_dependants := convert_null_to_zero(p_no_of_infirm_dependants);
138 l_prov_lsp_amount := convert_null_to_zero(p_prov_lsp_amount);
139 /*
140 begin
141 --
142 -- Start of API User Hook for the before hook of create_ca_emp_prvtax_inf
143 --
144 pay_ca_emp_prvtax_inf_bk1.create_ca_emp_prvtax_inf_b
145 (
146 p_legislation_code => p_legislation_code
147 ,p_assignment_id => p_assignment_id
148 ,p_business_group_id => p_business_group_id
149 ,p_province_code => p_province_code
150 ,p_jurisdiction_code => p_jurisdiction_code
151 ,p_tax_credit_amount => p_tax_credit_amount
152 ,p_basic_exemption_flag => p_basic_exemption_flag
153 ,p_deduction_code => p_deduction_code
154 ,p_extra_info_not_provided => p_extra_info_not_provided
155 ,p_marriage_status => p_marriage_status
156 ,p_no_of_infirm_dependants => p_no_of_infirm_dependants
157 ,p_non_resident_status => p_non_resident_status
158 ,p_disability_status => p_disability_status
159 ,p_no_of_dependants => p_no_of_dependants
160 ,p_annual_dedn => p_annual_dedn
161 ,p_total_expense_by_commission => p_total_expense_by_commission
162 ,p_total_remnrtn_by_commission => p_total_remnrtn_by_commission
163 ,p_prescribed_zone_dedn_amt => p_prescribed_zone_dedn_amt
164 ,p_additional_tax => p_additional_tax
165 ,p_prov_override_rate => p_prov_override_rate
166 ,p_prov_override_amount => p_prov_override_amount
167 ,p_prov_exempt_flag => p_prov_exempt_flag
168 ,p_pmed_exempt_flag => p_pmed_exempt_flag
169 ,p_wc_exempt_flag => p_wc_exempt_flag
170 ,p_qpp_exempt_flag => p_qpp_exempt_flag
171 ,p_tax_calc_method => p_tax_calc_method
172 ,p_other_tax_credit => p_other_tax_credit
173 ,p_ca_tax_information_category => p_ca_tax_information_category
174 ,p_ca_tax_information1 => p_ca_tax_information1
175 ,p_ca_tax_information2 => p_ca_tax_information2
176 ,p_ca_tax_information3 => p_ca_tax_information3
177 ,p_ca_tax_information4 => p_ca_tax_information4
178 ,p_ca_tax_information5 => p_ca_tax_information5
179 ,p_ca_tax_information6 => p_ca_tax_information6
180 ,p_ca_tax_information7 => p_ca_tax_information7
181 ,p_ca_tax_information8 => p_ca_tax_information8
182 ,p_ca_tax_information9 => p_ca_tax_information9
183 ,p_ca_tax_information10 => p_ca_tax_information10
184 ,p_ca_tax_information11 => p_ca_tax_information11
185 ,p_ca_tax_information12 => p_ca_tax_information12
186 ,p_ca_tax_information13 => p_ca_tax_information13
187 ,p_ca_tax_information14 => p_ca_tax_information14
188 ,p_ca_tax_information15 => p_ca_tax_information15
189 ,p_ca_tax_information16 => p_ca_tax_information16
190 ,p_ca_tax_information17 => p_ca_tax_information17
191 ,p_ca_tax_information18 => p_ca_tax_information18
192 ,p_ca_tax_information19 => p_ca_tax_information19
193 ,p_ca_tax_information20 => p_ca_tax_information20
194 ,p_ca_tax_information21 => p_ca_tax_information21
195 ,p_ca_tax_information22 => p_ca_tax_information22
196 ,p_ca_tax_information23 => p_ca_tax_information23
197 ,p_ca_tax_information24 => p_ca_tax_information24
198 ,p_ca_tax_information25 => p_ca_tax_information25
199 ,p_ca_tax_information26 => p_ca_tax_information26
200 ,p_ca_tax_information27 => p_ca_tax_information27
201 ,p_ca_tax_information28 => p_ca_tax_information28
202 ,p_ca_tax_information29 => p_ca_tax_information29
203 ,p_ca_tax_information30 => p_ca_tax_information30
204 ,p_prov_lsp_amount => p_prov_lsp_amount
205 ,p_effective_date => trunc(p_effective_date)
206 ,p_ppip_exempt_flag => p_ppip_exempt_flag
207 );
208 exception
209 when hr_api.cannot_find_prog_unit then
210 hr_api.cannot_find_prog_unit_error
211 (
212 p_module_name => 'CREATE_ca_emp_prvtax_inf'
213 ,p_hook_type => 'BP'
214 );
215 --
216 -- End of API User Hook for the before hook of create_ca_emp_prvtax_inf
217 --
218 end;
219 */
220 --
221 pay_cpt_ins.ins
222 (
223 p_emp_province_tax_inf_id => l_emp_province_tax_inf_id
224 ,p_effective_start_date => l_effective_start_date
225 ,p_effective_end_date => l_effective_end_date
226 ,p_legislation_code => p_legislation_code
227 ,p_assignment_id => p_assignment_id
228 ,p_business_group_id => p_business_group_id
229 ,p_province_code => p_province_code
230 ,p_jurisdiction_code => p_jurisdiction_code
231 ,p_tax_credit_amount => p_tax_credit_amount
232 ,p_basic_exemption_flag => p_basic_exemption_flag
233 ,p_deduction_code => p_deduction_code
234 ,p_extra_info_not_provided => p_extra_info_not_provided
235 ,p_marriage_status => p_marriage_status
236 ,p_no_of_infirm_dependants => l_no_of_infirm_dependants
237 ,p_non_resident_status => p_non_resident_status
238 ,p_disability_status => p_disability_status
239 ,p_no_of_dependants => l_no_of_dependants
240 ,p_annual_dedn => l_annual_dedn
241 ,p_total_expense_by_commission => l_total_expense_by_commission
242 ,p_total_remnrtn_by_commission => l_total_remnrtn_by_commission
243 ,p_prescribed_zone_dedn_amt => l_prescribed_zone_dedn_amt
244 ,p_additional_tax => l_additional_tax
245 ,p_prov_override_rate => l_prov_override_rate
246 ,p_prov_override_amount => l_prov_override_amount
247 ,p_prov_exempt_flag => p_prov_exempt_flag
248 ,p_pmed_exempt_flag => p_pmed_exempt_flag
249 ,p_wc_exempt_flag => p_wc_exempt_flag
250 ,p_qpp_exempt_flag => p_qpp_exempt_flag
251 ,p_tax_calc_method => p_tax_calc_method
252 ,p_other_tax_credit => l_other_tax_credit
253 ,p_ca_tax_information_category => p_ca_tax_information_category
254 ,p_ca_tax_information1 => p_ca_tax_information1
255 ,p_ca_tax_information2 => p_ca_tax_information2
256 ,p_ca_tax_information3 => p_ca_tax_information3
257 ,p_ca_tax_information4 => p_ca_tax_information4
258 ,p_ca_tax_information5 => p_ca_tax_information5
259 ,p_ca_tax_information6 => p_ca_tax_information6
260 ,p_ca_tax_information7 => p_ca_tax_information7
261 ,p_ca_tax_information8 => p_ca_tax_information8
262 ,p_ca_tax_information9 => p_ca_tax_information9
263 ,p_ca_tax_information10 => p_ca_tax_information10
264 ,p_ca_tax_information11 => p_ca_tax_information11
265 ,p_ca_tax_information12 => p_ca_tax_information12
266 ,p_ca_tax_information13 => p_ca_tax_information13
267 ,p_ca_tax_information14 => p_ca_tax_information14
268 ,p_ca_tax_information15 => p_ca_tax_information15
269 ,p_ca_tax_information16 => p_ca_tax_information16
270 ,p_ca_tax_information17 => p_ca_tax_information17
271 ,p_ca_tax_information18 => p_ca_tax_information18
272 ,p_ca_tax_information19 => p_ca_tax_information19
273 ,p_ca_tax_information20 => p_ca_tax_information20
274 ,p_ca_tax_information21 => p_ca_tax_information21
275 ,p_ca_tax_information22 => p_ca_tax_information22
276 ,p_ca_tax_information23 => p_ca_tax_information23
277 ,p_ca_tax_information24 => p_ca_tax_information24
278 ,p_ca_tax_information25 => p_ca_tax_information25
279 ,p_ca_tax_information26 => p_ca_tax_information26
280 ,p_ca_tax_information27 => p_ca_tax_information27
281 ,p_ca_tax_information28 => p_ca_tax_information28
282 ,p_ca_tax_information29 => p_ca_tax_information29
283 ,p_ca_tax_information30 => p_ca_tax_information30
284 ,p_object_version_number => l_object_version_number
285 ,p_prov_lsp_amount => l_prov_lsp_amount
286 ,p_effective_date => trunc(p_effective_date)
287 ,p_ppip_exempt_flag => p_ppip_exempt_flag
288 );
289 --
290 /*
291 begin
292 --
293 -- Start of API User Hook for the after hook of create_ca_emp_prvtax_inf
294 --
295
296 pay_ca_emp_prvtax_inf_bk1.create_ca_emp_prvtax_inf_a
297 (
298 p_emp_province_tax_inf_id => l_emp_province_tax_inf_id
299 ,p_effective_start_date => l_effective_start_date
300 ,p_effective_end_date => l_effective_end_date
301 ,p_legislation_code => p_legislation_code
302 ,p_assignment_id => p_assignment_id
303 ,p_business_group_id => p_business_group_id
304 ,p_province_code => p_province_code
305 ,p_jurisdiction_code => p_jurisdiction_code
306 ,p_tax_credit_amount => p_tax_credit_amount
307 ,p_basic_exemption_flag => p_basic_exemption_flag
308 ,p_deduction_code => p_deduction_code
309 ,p_extra_info_not_provided => p_extra_info_not_provided
310 ,p_marriage_status => p_marriage_status
311 ,p_no_of_infirm_dependants => p_no_of_infirm_dependants
312 ,p_non_resident_status => p_non_resident_status
313 ,p_disability_status => p_disability_status
314 ,p_no_of_dependants => p_no_of_dependants
315 ,p_annual_dedn => p_annual_dedn
316 ,p_total_expense_by_commission => p_total_expense_by_commission
317 ,p_total_remnrtn_by_commission => p_total_remnrtn_by_commission
318 ,p_prescribed_zone_dedn_amt => p_prescribed_zone_dedn_amt
319 ,p_additional_tax => p_additional_tax
320 ,p_prov_override_rate => p_prov_override_rate
321 ,p_prov_override_amount => p_prov_override_amount
322 ,p_prov_exempt_flag => p_prov_exempt_flag
323 ,p_pmed_exempt_flag => p_pmed_exempt_flag
324 ,p_wc_exempt_flag => p_wc_exempt_flag
325 ,p_qpp_exempt_flag => p_qpp_exempt_flag
326 ,p_tax_calc_method => p_tax_calc_method
327 ,p_other_tax_credit => p_other_tax_credit
328 ,p_ca_tax_information_category => p_ca_tax_information_category
329 ,p_ca_tax_information1 => p_ca_tax_information1
330 ,p_ca_tax_information2 => p_ca_tax_information2
334 ,p_ca_tax_information6 => p_ca_tax_information6
331 ,p_ca_tax_information3 => p_ca_tax_information3
332 ,p_ca_tax_information4 => p_ca_tax_information4
333 ,p_ca_tax_information5 => p_ca_tax_information5
335 ,p_ca_tax_information7 => p_ca_tax_information7
336 ,p_ca_tax_information8 => p_ca_tax_information8
337 ,p_ca_tax_information9 => p_ca_tax_information9
338 ,p_ca_tax_information10 => p_ca_tax_information10
339 ,p_ca_tax_information11 => p_ca_tax_information11
340 ,p_ca_tax_information12 => p_ca_tax_information12
341 ,p_ca_tax_information13 => p_ca_tax_information13
342 ,p_ca_tax_information14 => p_ca_tax_information14
343 ,p_ca_tax_information15 => p_ca_tax_information15
344 ,p_ca_tax_information16 => p_ca_tax_information16
345 ,p_ca_tax_information17 => p_ca_tax_information17
346 ,p_ca_tax_information18 => p_ca_tax_information18
347 ,p_ca_tax_information19 => p_ca_tax_information19
348 ,p_ca_tax_information20 => p_ca_tax_information20
349 ,p_ca_tax_information21 => p_ca_tax_information21
350 ,p_ca_tax_information22 => p_ca_tax_information22
351 ,p_ca_tax_information23 => p_ca_tax_information23
352 ,p_ca_tax_information24 => p_ca_tax_information24
353 ,p_ca_tax_information25 => p_ca_tax_information25
354 ,p_ca_tax_information26 => p_ca_tax_information26
355 ,p_ca_tax_information27 => p_ca_tax_information27
356 ,p_ca_tax_information28 => p_ca_tax_information28
357 ,p_ca_tax_information29 => p_ca_tax_information29
358 ,p_ca_tax_information30 => p_ca_tax_information30
359 ,p_object_version_number => l_object_version_number
360 ,p_prov_lsp_amount => p_prov_lsp_amount
361 ,p_effective_date => trunc(p_effective_date)
362 );
363 exception
364 when hr_api.cannot_find_prog_unit then
365 hr_api.cannot_find_prog_unit_error
366 (p_module_name => 'CREATE_ca_emp_prvtax_inf'
367 ,p_hook_type => 'AP'
368 );
369 --
370 -- End of API User Hook for the after hook of create_ca_emp_prvtax_inf
371 --
372 end;
373 */
374 --
375 hr_utility.set_location(l_proc, 60);
376 --
377 -- When in validation only mode raise the Validate_Enabled exception
378 --
379 if p_validate then
380 raise hr_api.validate_enabled;
381 end if;
382 --
383 -- Set all output arguments
384 --
385 p_emp_province_tax_inf_id := l_emp_province_tax_inf_id;
386 p_effective_start_date := l_effective_start_date;
387 p_effective_end_date := l_effective_end_date;
388 p_object_version_number := l_object_version_number;
389 --
390 hr_utility.set_location(' Leaving:'||l_proc, 70);
391 --
392 end if;
393 exception
394 --
395 when hr_api.validate_enabled then
396 --
397 -- As the Validate_Enabled exception has been raised
398 -- we must rollback to the savepoint
399 --
400 ROLLBACK TO create_ca_emp_prvtax_inf;
401 --
402 -- Only set output warning arguments
403 -- (Any key or derived arguments must be set to null
404 -- when validation only mode is being used.)
405 --
406 p_emp_province_tax_inf_id := null;
407 p_effective_start_date := null;
408 p_effective_end_date := null;
409 p_object_version_number := null;
410 hr_utility.set_location(' Leaving:'||l_proc, 80);
411 --
412 when others then
413 --
414 -- A validation or unexpected error has occured
415 --
416 ROLLBACK TO create_ca_emp_prvtax_inf;
417 --
418 -- Reset IN OUT params and set OUT params.
419 --
420 p_emp_province_tax_inf_id := null;
421 p_effective_start_date := null;
422 p_effective_end_date := null;
423 p_object_version_number := null;
424 --
425 raise;
426 --
427 end create_ca_emp_prvtax_inf;
428 -- ----------------------------------------------------------------------------
429 -- |------------------------< update_ca_emp_prvtax_inf >--- ------------------|
430 -- ----------------------------------------------------------------------------
431 --
432 procedure update_ca_emp_prvtax_inf
433 (p_validate in boolean default false
434 ,p_emp_province_tax_inf_id in number
435 ,p_effective_start_date out nocopy date
436 ,p_effective_end_date out nocopy date
437 ,p_legislation_code in varchar2 default hr_api.g_varchar2
438 ,p_assignment_id in number default hr_api.g_number
439 ,p_business_group_id in number default hr_api.g_number
440 ,p_province_code in varchar2 default hr_api.g_varchar2
441 ,p_jurisdiction_code in varchar2 default hr_api.g_varchar2
442 ,p_tax_credit_amount in number default hr_api.g_number
443 ,p_basic_exemption_flag in varchar2 default hr_api.g_varchar2
444 ,p_deduction_code in varchar2 default hr_api.g_varchar2
445 ,p_extra_info_not_provided in varchar2 default hr_api.g_varchar2
446 ,p_marriage_status in varchar2 default hr_api.g_varchar2
447 ,p_no_of_infirm_dependants in number default hr_api.g_number
448 ,p_non_resident_status in varchar2 default hr_api.g_varchar2
449 ,p_disability_status in varchar2 default hr_api.g_varchar2
450 ,p_no_of_dependants in number default hr_api.g_number
451 ,p_annual_dedn in number default hr_api.g_number
452 ,p_total_expense_by_commission in number default hr_api.g_number
453 ,p_total_remnrtn_by_commission in number default hr_api.g_number
454 ,p_prescribed_zone_dedn_amt in number default hr_api.g_number
455 ,p_additional_tax in number default hr_api.g_number
456 ,p_prov_override_rate in number default hr_api.g_number
457 ,p_prov_override_amount in number default hr_api.g_number
458 ,p_prov_exempt_flag in varchar2 default hr_api.g_varchar2
459 ,p_pmed_exempt_flag in varchar2 default hr_api.g_varchar2
460 ,p_wc_exempt_flag in varchar2 default hr_api.g_varchar2
461 ,p_qpp_exempt_flag in varchar2 default hr_api.g_varchar2
462 ,p_tax_calc_method in varchar2 default hr_api.g_varchar2
463 ,p_other_tax_credit in number default hr_api.g_number
464 ,p_ca_tax_information_category in varchar2 default hr_api.g_varchar2
465 ,p_ca_tax_information1 in varchar2 default hr_api.g_varchar2
466 ,p_ca_tax_information2 in varchar2 default hr_api.g_varchar2
467 ,p_ca_tax_information3 in varchar2 default hr_api.g_varchar2
468 ,p_ca_tax_information4 in varchar2 default hr_api.g_varchar2
469 ,p_ca_tax_information5 in varchar2 default hr_api.g_varchar2
470 ,p_ca_tax_information6 in varchar2 default hr_api.g_varchar2
471 ,p_ca_tax_information7 in varchar2 default hr_api.g_varchar2
472 ,p_ca_tax_information8 in varchar2 default hr_api.g_varchar2
473 ,p_ca_tax_information9 in varchar2 default hr_api.g_varchar2
474 ,p_ca_tax_information10 in varchar2 default hr_api.g_varchar2
475 ,p_ca_tax_information11 in varchar2 default hr_api.g_varchar2
476 ,p_ca_tax_information12 in varchar2 default hr_api.g_varchar2
477 ,p_ca_tax_information13 in varchar2 default hr_api.g_varchar2
478 ,p_ca_tax_information14 in varchar2 default hr_api.g_varchar2
479 ,p_ca_tax_information15 in varchar2 default hr_api.g_varchar2
480 ,p_ca_tax_information16 in varchar2 default hr_api.g_varchar2
481 ,p_ca_tax_information17 in varchar2 default hr_api.g_varchar2
482 ,p_ca_tax_information18 in varchar2 default hr_api.g_varchar2
483 ,p_ca_tax_information19 in varchar2 default hr_api.g_varchar2
484 ,p_ca_tax_information20 in varchar2 default hr_api.g_varchar2
485 ,p_ca_tax_information21 in varchar2 default hr_api.g_varchar2
486 ,p_ca_tax_information22 in varchar2 default hr_api.g_varchar2
487 ,p_ca_tax_information23 in varchar2 default hr_api.g_varchar2
488 ,p_ca_tax_information24 in varchar2 default hr_api.g_varchar2
489 ,p_ca_tax_information25 in varchar2 default hr_api.g_varchar2
490 ,p_ca_tax_information26 in varchar2 default hr_api.g_varchar2
491 ,p_ca_tax_information27 in varchar2 default hr_api.g_varchar2
492 ,p_ca_tax_information28 in varchar2 default hr_api.g_varchar2
493 ,p_ca_tax_information29 in varchar2 default hr_api.g_varchar2
494 ,p_ca_tax_information30 in varchar2 default hr_api.g_varchar2
495 ,p_object_version_number in out nocopy number
496 ,p_prov_lsp_amount in number default hr_api.g_number
497 ,p_effective_date in date
498 ,p_datetrack_mode in varchar2
499 ,p_ppip_exempt_flag in varchar2 default hr_api.g_varchar2
500 ) is
501 --
502 -- Declare cursors and local variables
503 --
504 l_proc varchar2(72) := g_package||'update_ca_emp_prvtax_inf';
505 l_object_version_number pay_ca_emp_prov_tax_info_f.object_version_number%TYPE;
506 l_effective_start_date pay_ca_emp_prov_tax_info_f.effective_start_date%TYPE;
507 l_effective_end_date pay_ca_emp_prov_tax_info_f.effective_end_date%TYPE;
508 --
509 begin
510 --
511 hr_utility.set_location('Entering:'|| l_proc, 10);
512 --
513 -- Issue a savepoint if operating in validation only mode
514 --
515 savepoint update_ca_emp_prvtax_inf;
516 --
517 hr_utility.set_location(l_proc, 20);
518 --
519 -- Process Logic
520 --
521 l_object_version_number := p_object_version_number;
522 --
523 /*
524 begin
525 --
526 -- Start of API User Hook for the before hook of update_ca_emp_prvtax_inf
527 --
528 pay_ca_emp_prvtax_inf_bk2.update_ca_emp_prvtax_inf_b
529 (
530 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id
531 ,p_legislation_code => p_legislation_code
532 ,p_assignment_id => p_assignment_id
533 ,p_business_group_id => p_business_group_id
534 ,p_province_code => p_province_code
535 ,p_jurisdiction_code => p_jurisdiction_code
536 ,p_tax_credit_amount => p_tax_credit_amount
537 ,p_basic_exemption_flag => p_basic_exemption_flag
538 ,p_deduction_code => p_deduction_code
539 ,p_extra_info_not_provided => p_extra_info_not_provided
540 ,p_marriage_status => p_marriage_status
541 ,p_no_of_infirm_dependants => p_no_of_infirm_dependants
542 ,p_non_resident_status => p_non_resident_status
543 ,p_disability_status => p_disability_status
544 ,p_no_of_dependants => p_no_of_dependants
545 ,p_annual_dedn => p_annual_dedn
546 ,p_total_expense_by_commission => p_total_expense_by_commission
547 ,p_total_remnrtn_by_commission => p_total_remnrtn_by_commission
548 ,p_prescribed_zone_dedn_amt => p_prescribed_zone_dedn_amt
549 ,p_additional_tax => p_additional_tax
550 ,p_prov_override_rate => p_prov_override_rate
551 ,p_prov_override_amount => p_prov_override_amount
552 ,p_prov_exempt_flag => p_prov_exempt_flag
553 ,p_pmed_exempt_flag => p_pmed_exempt_flag
554 ,p_wc_exempt_flag => p_wc_exempt_flag
555 ,p_qpp_exempt_flag => p_qpp_exempt_flag
556 ,p_tax_calc_method => p_tax_calc_method
557 ,p_other_tax_credit => p_other_tax_credit
558 ,p_ca_tax_information_category => p_ca_tax_information_category
559 ,p_ca_tax_information1 => p_ca_tax_information1
560 ,p_ca_tax_information2 => p_ca_tax_information2
561 ,p_ca_tax_information3 => p_ca_tax_information3
562 ,p_ca_tax_information4 => p_ca_tax_information4
563 ,p_ca_tax_information5 => p_ca_tax_information5
564 ,p_ca_tax_information6 => p_ca_tax_information6
565 ,p_ca_tax_information7 => p_ca_tax_information7
566 ,p_ca_tax_information8 => p_ca_tax_information8
567 ,p_ca_tax_information9 => p_ca_tax_information9
568 ,p_ca_tax_information10 => p_ca_tax_information10
569 ,p_ca_tax_information11 => p_ca_tax_information11
570 ,p_ca_tax_information12 => p_ca_tax_information12
571 ,p_ca_tax_information13 => p_ca_tax_information13
572 ,p_ca_tax_information14 => p_ca_tax_information14
573 ,p_ca_tax_information15 => p_ca_tax_information15
574 ,p_ca_tax_information16 => p_ca_tax_information16
575 ,p_ca_tax_information17 => p_ca_tax_information17
576 ,p_ca_tax_information18 => p_ca_tax_information18
577 ,p_ca_tax_information19 => p_ca_tax_information19
578 ,p_ca_tax_information20 => p_ca_tax_information20
579 ,p_ca_tax_information21 => p_ca_tax_information21
580 ,p_ca_tax_information22 => p_ca_tax_information22
581 ,p_ca_tax_information23 => p_ca_tax_information23
582 ,p_ca_tax_information24 => p_ca_tax_information24
583 ,p_ca_tax_information25 => p_ca_tax_information25
584 ,p_ca_tax_information26 => p_ca_tax_information26
585 ,p_ca_tax_information27 => p_ca_tax_information27
586 ,p_ca_tax_information28 => p_ca_tax_information28
587 ,p_ca_tax_information29 => p_ca_tax_information29
588 ,p_ca_tax_information30 => p_ca_tax_information30
589 ,p_object_version_number => p_object_version_number
590 ,p_prov_lsp_amount => p_prov_lsp_amount
591 ,p_effective_date => trunc(p_effective_date)
592 ,p_datetrack_mode => p_datetrack_mode
593 );
594 exception
595 when hr_api.cannot_find_prog_unit then
596 hr_api.cannot_find_prog_unit_error
597 (p_module_name => 'UPDATE_ca_emp_prvtax_inf'
598 ,p_hook_type => 'BP'
599 );
600 --
601 -- End of API User Hook for the before hook of update_ca_emp_prvtax_inf
602 --
603 end;
604 */
605 --
606
607 pay_cpt_upd.upd
608 (
612 ,p_legislation_code => p_legislation_code
609 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id
610 ,p_effective_start_date => l_effective_start_date
611 ,p_effective_end_date => l_effective_end_date
613 ,p_assignment_id => p_assignment_id
614 ,p_business_group_id => p_business_group_id
615 ,p_province_code => p_province_code
616 ,p_jurisdiction_code => p_jurisdiction_code
617 ,p_tax_credit_amount => p_tax_credit_amount
618 ,p_basic_exemption_flag => p_basic_exemption_flag
619 ,p_deduction_code => p_deduction_code
620 ,p_extra_info_not_provided => p_extra_info_not_provided
621 ,p_marriage_status => p_marriage_status
622 ,p_no_of_infirm_dependants => p_no_of_infirm_dependants
623 ,p_non_resident_status => p_non_resident_status
624 ,p_disability_status => p_disability_status
625 ,p_no_of_dependants => p_no_of_dependants
626 ,p_annual_dedn => p_annual_dedn
627 ,p_total_expense_by_commission => p_total_expense_by_commission
628 ,p_total_remnrtn_by_commission => p_total_remnrtn_by_commission
629 ,p_prescribed_zone_dedn_amt => p_prescribed_zone_dedn_amt
630 ,p_additional_tax => p_additional_tax
631 ,p_prov_override_rate => p_prov_override_rate
632 ,p_prov_override_amount => p_prov_override_amount
633 ,p_prov_exempt_flag => p_prov_exempt_flag
634 ,p_pmed_exempt_flag => p_pmed_exempt_flag
635 ,p_wc_exempt_flag => p_wc_exempt_flag
636 ,p_qpp_exempt_flag => p_qpp_exempt_flag
637 ,p_tax_calc_method => p_tax_calc_method
638 ,p_other_tax_credit => p_other_tax_credit
639 ,p_ca_tax_information_category => p_ca_tax_information_category
640 ,p_ca_tax_information1 => p_ca_tax_information1
641 ,p_ca_tax_information2 => p_ca_tax_information2
642 ,p_ca_tax_information3 => p_ca_tax_information3
643 ,p_ca_tax_information4 => p_ca_tax_information4
644 ,p_ca_tax_information5 => p_ca_tax_information5
645 ,p_ca_tax_information6 => p_ca_tax_information6
646 ,p_ca_tax_information7 => p_ca_tax_information7
647 ,p_ca_tax_information8 => p_ca_tax_information8
648 ,p_ca_tax_information9 => p_ca_tax_information9
649 ,p_ca_tax_information10 => p_ca_tax_information10
650 ,p_ca_tax_information11 => p_ca_tax_information11
651 ,p_ca_tax_information12 => p_ca_tax_information12
652 ,p_ca_tax_information13 => p_ca_tax_information13
653 ,p_ca_tax_information14 => p_ca_tax_information14
654 ,p_ca_tax_information15 => p_ca_tax_information15
655 ,p_ca_tax_information16 => p_ca_tax_information16
656 ,p_ca_tax_information17 => p_ca_tax_information17
657 ,p_ca_tax_information18 => p_ca_tax_information18
658 ,p_ca_tax_information19 => p_ca_tax_information19
659 ,p_ca_tax_information20 => p_ca_tax_information20
660 ,p_ca_tax_information21 => p_ca_tax_information21
661 ,p_ca_tax_information22 => p_ca_tax_information22
662 ,p_ca_tax_information23 => p_ca_tax_information23
663 ,p_ca_tax_information24 => p_ca_tax_information24
664 ,p_ca_tax_information25 => p_ca_tax_information25
665 ,p_ca_tax_information26 => p_ca_tax_information26
666 ,p_ca_tax_information27 => p_ca_tax_information27
667 ,p_ca_tax_information28 => p_ca_tax_information28
668 ,p_ca_tax_information29 => p_ca_tax_information29
669 ,p_ca_tax_information30 => p_ca_tax_information30
670 ,p_object_version_number => l_object_version_number
671 ,p_prov_lsp_amount => p_prov_lsp_amount
672 ,p_effective_date => trunc(p_effective_date)
673 ,p_datetrack_mode => p_datetrack_mode
674 ,p_ppip_exempt_flag => p_ppip_exempt_flag
675 );
676 --
677 /*
678 begin
679 --
680 -- Start of API User Hook for the after hook of update_ca_emp_prvtax_inf
681 --
682 pay_ca_emp_prvtax_inf_bk2.update_ca_emp_prvtax_inf_a
683 (
684 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id
685 ,p_effective_start_date => l_effective_start_date
686 ,p_effective_end_date => l_effective_end_date
687 ,p_legislation_code => p_legislation_code
688 ,p_assignment_id => p_assignment_id
689 ,p_business_group_id => p_business_group_id
690 ,p_province_code => p_province_code
691 ,p_jurisdiction_code => p_jurisdiction_code
692 ,p_tax_credit_amount => p_tax_credit_amount
693 ,p_basic_exemption_flag => p_basic_exemption_flag
694 ,p_deduction_code => p_deduction_code
695 ,p_extra_info_not_provided => p_extra_info_not_provided
696 ,p_marriage_status => p_marriage_status
697 ,p_no_of_infirm_dependants => p_no_of_infirm_dependants
698 ,p_non_resident_status => p_non_resident_status
699 ,p_disability_status => p_disability_status
700 ,p_no_of_dependants => p_no_of_dependants
701 ,p_annual_dedn => p_annual_dedn
702 ,p_total_expense_by_commission => p_total_expense_by_commission
703 ,p_total_remnrtn_by_commission => p_total_remnrtn_by_commission
704 ,p_prescribed_zone_dedn_amt => p_prescribed_zone_dedn_amt
705 ,p_additional_tax => p_additional_tax
706 ,p_prov_override_rate => p_prov_override_rate
707 ,p_prov_override_amount => p_prov_override_amount
708 ,p_prov_exempt_flag => p_prov_exempt_flag
709 ,p_pmed_exempt_flag => p_pmed_exempt_flag
710 ,p_wc_exempt_flag => p_wc_exempt_flag
711 ,p_qpp_exempt_flag => p_qpp_exempt_flag
712 ,p_tax_calc_method => p_tax_calc_method
713 ,p_other_tax_credit => p_other_tax_credit
714 ,p_ca_tax_information_category => p_ca_tax_information_category
715 ,p_ca_tax_information1 => p_ca_tax_information1
716 ,p_ca_tax_information2 => p_ca_tax_information2
717 ,p_ca_tax_information3 => p_ca_tax_information3
718 ,p_ca_tax_information4 => p_ca_tax_information4
719 ,p_ca_tax_information5 => p_ca_tax_information5
720 ,p_ca_tax_information6 => p_ca_tax_information6
721 ,p_ca_tax_information7 => p_ca_tax_information7
722 ,p_ca_tax_information8 => p_ca_tax_information8
723 ,p_ca_tax_information9 => p_ca_tax_information9
724 ,p_ca_tax_information10 => p_ca_tax_information10
725 ,p_ca_tax_information11 => p_ca_tax_information11
726 ,p_ca_tax_information12 => p_ca_tax_information12
727 ,p_ca_tax_information13 => p_ca_tax_information13
728 ,p_ca_tax_information14 => p_ca_tax_information14
729 ,p_ca_tax_information15 => p_ca_tax_information15
730 ,p_ca_tax_information16 => p_ca_tax_information16
731 ,p_ca_tax_information17 => p_ca_tax_information17
732 ,p_ca_tax_information18 => p_ca_tax_information18
733 ,p_ca_tax_information19 => p_ca_tax_information19
734 ,p_ca_tax_information20 => p_ca_tax_information20
735 ,p_ca_tax_information21 => p_ca_tax_information21
736 ,p_ca_tax_information22 => p_ca_tax_information22
737 ,p_ca_tax_information23 => p_ca_tax_information23
738 ,p_ca_tax_information24 => p_ca_tax_information24
739 ,p_ca_tax_information25 => p_ca_tax_information25
740 ,p_ca_tax_information26 => p_ca_tax_information26
741 ,p_ca_tax_information27 => p_ca_tax_information27
742 ,p_ca_tax_information28 => p_ca_tax_information28
743 ,p_ca_tax_information29 => p_ca_tax_information29
744 ,p_ca_tax_information30 => p_ca_tax_information30
745 ,p_object_version_number => l_object_version_number
746 ,p_prov_lsp_amount => p_prov_lsp_amount
747 ,p_effective_date => trunc(p_effective_date)
748 ,p_datetrack_mode => p_datetrack_mode
749 );
750 exception
751 when hr_api.cannot_find_prog_unit then
752 hr_api.cannot_find_prog_unit_error
753 (p_module_name => 'UPDATE_ca_emp_prvtax_inf'
754 ,p_hook_type => 'AP'
755 );
756 --
757 -- End of API User Hook for the after hook of update_ca_emp_prvtax_inf
758 --
759 end;
760 */
761 --
762 hr_utility.set_location(l_proc, 60);
763 --
764 -- When in validation only mode raise the Validate_Enabled exception
765 --
766 if p_validate then
767 raise hr_api.validate_enabled;
768 end if;
769 --
770 -- Set all output arguments
771 --
772 p_object_version_number := l_object_version_number;
773 p_effective_start_date := l_effective_start_date;
774 p_effective_end_date := l_effective_end_date;
775 --
776 hr_utility.set_location(' Leaving:'||l_proc, 70);
777 --
778 exception
779 --
780 when hr_api.validate_enabled then
781 --
782 -- As the Validate_Enabled exception has been raised
783 -- we must rollback to the savepoint
784 --
785 ROLLBACK TO update_ca_emp_prvtax_inf;
786 --
787 -- Only set output warning arguments
788 -- (Any key or derived arguments must be set to null
789 -- when validation only mode is being used.)
790 --
791 hr_utility.set_location(' Leaving:'||l_proc, 80);
792 --
793 when others then
794 --
795 -- A validation or unexpected error has occured
796 --
797 ROLLBACK TO update_ca_emp_prvtax_inf;
798 --
799 -- Reset IN OUT params and set OUT params.
800 --
801 p_effective_start_date := null;
802 p_effective_end_date := null;
803 p_object_version_number := l_object_version_number;
804 --
805 raise;
806 --
807 end update_ca_emp_prvtax_inf;
808 -- ----------------------------------------------------------------------------
809 -- |------------------------< delete_ca_emp_prvtax_inf >----------------------|
810 -- ----------------------------------------------------------------------------
811 --
812 procedure delete_ca_emp_prvtax_inf
813 (p_validate in boolean default false
814 ,p_emp_province_tax_inf_id in number
815 ,p_effective_start_date out nocopy date
816 ,p_effective_end_date out nocopy date
817 ,p_object_version_number in out nocopy number
818 ,p_effective_date in date
819 ,p_datetrack_mode in varchar2
820 ) is
821 --
822 -- Declare cursors and local variables
823 --
824 l_proc varchar2(72) := g_package||'update_ca_emp_prvtax_inf';
825 l_object_version_number pay_ca_emp_prov_tax_info_f.object_version_number%TYPE;
826 l_effective_start_date pay_ca_emp_prov_tax_info_f.effective_start_date%TYPE;
827 l_effective_end_date pay_ca_emp_prov_tax_info_f.effective_end_date%TYPE;
828 --
829 begin
830 --
831 hr_utility.set_location('Entering:'|| l_proc, 10);
832 --
833 -- Issue a savepoint if operating in validation only mode
834 --
835 savepoint delete_ca_emp_prvtax_inf;
836 --
837 hr_utility.set_location(l_proc, 20);
838 --
839 -- Process Logic
840 --
841 l_object_version_number := p_object_version_number;
842 --
843 --
844 /*
845 begin
846 --
847 -- Start of API User Hook for the before hook of delete_ca_emp_prvtax_inf
848 --
849 pay_ca_emp_prvtax_inf_bk3.delete_ca_emp_prvtax_inf_b
850 (
851 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id
852 ,p_object_version_number => p_object_version_number
853 ,p_effective_date => trunc(p_effective_date)
854 ,p_datetrack_mode => p_datetrack_mode
855 );
856 exception
857 when hr_api.cannot_find_prog_unit then
858 hr_api.cannot_find_prog_unit_error
859 (p_module_name => 'DELETE_ca_emp_prvtax_inf'
860 ,p_hook_type => 'BP'
861 );
862 --
863 -- End of API User Hook for the before hook of delete_ca_emp_prvtax_inf
864 --
865 end;
866 --
867 */
868 pay_cpt_del.del
869 (
870 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id
871 ,p_effective_start_date => l_effective_start_date
872 ,p_effective_end_date => l_effective_end_date
873 ,p_object_version_number => l_object_version_number
874 ,p_effective_date => p_effective_date
875 ,p_datetrack_mode => p_datetrack_mode
876 );
877 --
878 /*
879 begin
880 --
881 -- Start of API User Hook for the after hook of delete_ca_emp_prvtax_inf
882 --
883 pay_ca_emp_prvtax_inf_bk3.delete_ca_emp_prvtax_inf_a
884 (
888 ,p_object_version_number => l_object_version_number
885 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id
886 ,p_effective_start_date => l_effective_start_date
887 ,p_effective_end_date => l_effective_end_date
889 ,p_effective_date => trunc(p_effective_date)
890 ,p_datetrack_mode => p_datetrack_mode
891 );
892 exception
893 when hr_api.cannot_find_prog_unit then
894 hr_api.cannot_find_prog_unit_error
895 (p_module_name => 'DELETE_ca_emp_prvtax_inf'
896 ,p_hook_type => 'AP'
897 );
898 --
899 -- End of API User Hook for the after hook of delete_ca_emp_prvtax_inf
900 --
901 end;
902 --
903 */
904 hr_utility.set_location(l_proc, 60);
905 --
906 -- When in validation only mode raise the Validate_Enabled exception
907 --
908 if p_validate then
909 raise hr_api.validate_enabled;
910 end if;
911 --
912 hr_utility.set_location(' Leaving:'||l_proc, 70);
913 --
914 exception
915 --
916 when hr_api.validate_enabled then
917 --
918 -- As the Validate_Enabled exception has been raised
919 -- we must rollback to the savepoint
920 --
921 ROLLBACK TO delete_ca_emp_prvtax_inf;
922 --
923 -- Only set output warning arguments
924 -- (Any key or derived arguments must be set to null
925 -- when validation only mode is being used.)
926 --
927 p_effective_start_date := null;
928 p_effective_end_date := null;
929 --
930 when others then
931 --
932 -- A validation or unexpected error has occured
933 --
934 ROLLBACK TO delete_ca_emp_prvtax_inf;
935 --
936 -- Reset IN OUT params and set OUT params.
937 --
938 p_effective_start_date := null;
939 p_effective_end_date := null;
940 p_object_version_number := l_object_version_number;
941 --
942 raise;
943 --
944 end delete_ca_emp_prvtax_inf;
945 --
946 -- ----------------------------------------------------------------------------
947 -- |-------------------------------< lck >------------------------------------|
948 -- ----------------------------------------------------------------------------
949 --
950 procedure lck
951 (
952 p_emp_province_tax_inf_id in number
953 ,p_object_version_number in number
954 ,p_effective_date in date
955 ,p_datetrack_mode in varchar2
956 ,p_validation_start_date out nocopy date
957 ,p_validation_end_date out nocopy date
958 ) is
959 --
960 --
961 -- Declare cursors and local variables
962 --
963 l_proc varchar2(72) := g_package||'lck';
964 l_validation_start_date date;
965 l_validation_end_date date;
966 --
967 begin
968 --
969 hr_utility.set_location('Entering:'|| l_proc, 10);
970 --
971 pay_cpt_shd.lck
972 (
973 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id
974 ,p_validation_start_date => l_validation_start_date
975 ,p_validation_end_date => l_validation_end_date
976 ,p_object_version_number => p_object_version_number
977 ,p_effective_date => p_effective_date
978 ,p_datetrack_mode => p_datetrack_mode
979 );
980 --
981 hr_utility.set_location(' Leaving:'||l_proc, 70);
982 --
983 end lck;
984 --
985
986 /** Business Processes added **/
987 procedure pull_tax_records( p_assignment_id in number,
988 p_new_start_date in date,
989 p_default_date in date,
990 p_province_code in varchar2) is
991 l_ef_date DATE;
992 l_proc VARCHAR2(50) := 'pay_ca_emp_provtax_inf_api.pull_tax_records';
993
994 begin
995 hr_utility.set_location('Entering: ' || l_proc, 5);
996
997 if p_new_start_date < p_default_date then
998 l_ef_date := p_default_date;
999 elsif p_new_start_date > p_default_date then
1000 l_ef_date := p_new_start_date;
1001 else -- do nothing
1002 return;
1003 end if;
1004
1005 /* First update the tax rules records */
1006
1007 update PAY_CA_EMP_PROV_TAX_INFO_F
1008 set effective_start_date = p_new_start_date
1009 where assignment_id = p_assignment_id
1010 and l_ef_date between effective_start_date and effective_end_date
1011 and province_code = p_province_code;
1012
1013 if sql%notfound then
1014 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1015 hr_utility.set_message_token('PROCEDURE',l_proc);
1016 hr_utility.set_message_token('STEP','2');
1017 hr_utility.raise_error;
1018 end if;
1019
1020 /* Next delete any orphaned rows */
1021 if p_new_start_date > p_default_date then
1022 hr_utility.set_location(l_proc, 10);
1023 delete PAY_CA_EMP_PROV_TAX_INFO_F
1024 where assignment_id = p_assignment_id
1025 and p_new_start_date > effective_start_date
1026 and province_code = p_province_code;
1027
1028 end if;
1029
1030 hr_utility.set_location('Leaving: ' || l_proc, 20);
1031
1032 end pull_tax_records;
1033
1034 procedure check_hiring_date( p_assignment_id in number,
1035 p_default_date in date,
1036 p_s_start_date in date,
1037 p_prov_code in varchar2) is
1038
1039 begin
1040
1041 /* If the hiring date has been changed and pulled back, for the
1042 assignment then pull back the start date of the tax rules records
1043 */
1044
1045 if p_s_start_date < p_default_date then
1046 pull_tax_records(p_assignment_id => p_assignment_id,
1047 p_new_start_date => p_s_start_date,
1048 p_default_date => p_default_date,
1049 p_province_code => p_prov_code);
1050
1051
1052 /* If the hiring date has been pulled forward then the person api
1053 pulls forward the element entries but does not pull forward the
1054 tax rules record. So, we will pull them forward */
1055
1056 elsif p_s_start_date > p_default_date then
1057
1058 pull_tax_records(p_assignment_id => p_assignment_id,
1059 p_new_start_date => p_s_start_date,
1060 p_default_date => p_default_date,
1061 p_province_code => p_prov_code);
1062 end if;
1063
1064 end check_hiring_date;
1065
1066 procedure tax_record_already_present(p_assignment_id in number,
1067 p_province_code in varchar2,
1068 p_effective_date in date,
1069 p_rec_present out nocopy varchar2) is
1070 cursor csr_chk_assignment_rec is
1071 select '1'
1072 from pay_ca_emp_prov_tax_info_f ptt
1073 where ptt.assignment_id = p_assignment_id
1074 /*
1075 and p_effective_date between ptt.effective_start_date and
1076 ptt.effective_end_date
1077 */
1078 and ptt.province_code = p_province_code;
1079
1080 cursor csr_get_default_date (p_assignment number,p_province varchar2) is
1081 select min(effective_start_date)
1082 from pay_ca_emp_prov_tax_info_f ptt
1083 where ptt.assignment_id = p_assignment_id
1084 and ptt.province_code = p_province_code;
1085
1086
1087 l_present varchar2(2);
1088 l_default_date date;
1089 rec_present varchar2(1);
1090 begin
1091 open csr_chk_assignment_rec;
1092 fetch csr_chk_assignment_rec into l_present;
1093 if csr_chk_assignment_rec%FOUND then
1094 p_rec_present := 'Y';
1095 /* Get the default date */
1096 open csr_get_default_date(p_assignment_id,p_province_code);
1097
1098 fetch csr_get_default_date into l_default_date;
1099
1100 if l_default_date is null then
1101
1102 close csr_get_default_date;
1103 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1104 hr_utility.set_message_token('PROCEDURE',
1105 'pay_ca_emp_fedtax_inf_api.tax_record_already_present');
1106 hr_utility.set_message_token('STEP','1');
1107 hr_utility.raise_error;
1108
1109 end if;
1110
1111 close csr_get_default_date;
1112
1113 /* Now check for pull back of the hiring date */
1114 check_hiring_date(p_assignment_id => p_assignment_id,
1115 p_default_date => l_default_date,
1116 p_s_start_date => p_effective_date,
1117 p_prov_code => p_province_code);
1118 else
1119 p_rec_present := 'N';
1120 end if;
1121 end tax_record_already_present;
1122
1123 procedure perform_assignment_validation(p_assignment_id in varchar2,
1124 p_effective_date in date)
1125 is
1126 l_location_id number;
1127 l_country varchar2(30);
1128 cursor csr_chk_assignment is
1129 select paf.location_id
1130 from PER_ASSIGNMENTS_F paf
1131 where paf.assignment_id = p_assignment_id
1132 and p_effective_date between paf.effective_start_date and
1133 paf.effective_end_date ;
1134 cursor csr_get_location(p_location_id number) is
1135 select hrl.country from hr_locations hrl
1136 where hrl.location_id = p_location_id;
1137
1138 begin
1139 /* Check whether the assignment is present as of that date or not and the location id of it. Raise error message if assignment is not present or location_id is null */
1140 open csr_chk_assignment;
1141 fetch csr_chk_assignment into l_location_id;
1142
1143 if csr_chk_assignment%NOTFOUND then
1144 hr_utility.set_message(800, 'HR_74004_ASSIGNMENT_ABSENT');
1145 hr_utility.raise_error;
1146 else
1147 if l_location_id is NULL then
1148 hr_utility.set_message(800, 'HR_74005_LOCATION_ABSENT');
1149 hr_utility.raise_error;
1150 else
1151 open csr_get_location(l_location_id);
1152 fetch csr_get_location into l_country;
1153 if l_country is null or l_country <> 'CA' then
1154 hr_utility.set_message(800, 'HR_74006_LOCATION_WRONG');
1155 hr_utility.raise_error;
1156 end if;
1157 end if;
1158 end if;
1159 end perform_assignment_validation;
1160
1161
1162 procedure check_basic_exemption(p_basic_exemption_flag in varchar2,
1163 p_tax_credit_amount in number) is
1164 begin
1165 if p_basic_exemption_flag = 'Y' then
1166 if p_tax_credit_amount is not null then
1167 hr_utility.set_message(800, 'HR_74007_BOTH_NOT_NULL');
1168 hr_utility.raise_error;
1169 end if;
1170 elsif p_basic_exemption_flag = 'N' then
1171 if p_tax_credit_amount is NULL then
1172 hr_utility.set_message(800, 'HR_74008_BOTH_NULL');
1173 hr_utility.raise_error;
1174 end if;
1175 elsif p_basic_exemption_flag is NULL then
1176 if p_tax_credit_amount is NULL then
1177 hr_utility.set_message(800, 'HR_74008_BOTH_NULL');
1178 hr_utility.raise_error;
1179 end if;
1180 end if;
1181 end check_basic_exemption;
1182
1183 procedure check_province_code(p_province_code in varchar2) is
1184
1185 cursor csr_province_code is
1186 select '1' from hr_lookups lkp
1187 where lkp.lookup_code = p_province_code
1188 and lkp.lookup_type = 'CA_PROVINCE';
1189
1190 l_province_code varchar2(30);
1191
1192 begin
1193 open csr_province_code;
1194 fetch csr_province_code into l_province_code;
1195 if csr_province_code%NOTFOUND then
1196 hr_utility.set_message(800, 'HR_74009_EMPL_PROV_WRONG');
1197 hr_utility.raise_error;
1198 end if;
1199 end check_province_code;
1200 --
1201 function convert_null_to_zero(p_value in number) return number is
1202 begin
1203 if p_value is null then
1204 return 0;
1205 else
1206 return p_value;
1207 end if;
1208 end convert_null_to_zero;
1209 --
1210 end pay_ca_emp_prvtax_inf_api;