[Home] [Help]
PACKAGE BODY: APPS.PAY_COUNTY_TAX_RULE_API
Source
1 PACKAGE BODY pay_county_tax_rule_api AS
2 /* $Header: pycntapi.pkb 120.1.12020000.2 2012/07/06 09:33:50 vmaripal ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_county_tax_rule_api.';
7 --
8 -- Global package cursor
9 --
10 cursor csr_derive_business_grp(
11 l_assignment_id number,
12 l_effective_date date
13 )
14 is
15 Select Business_Group_Id
16 From Per_Assignments_F
17 Where Assignment_Id = l_assignment_id
18 and l_effective_date between effective_start_date
19 and effective_end_date;
20
21 cursor csr_defaulting_date(p_assignment_id number)
22 is
23 Select min(effective_start_date)
24 From Pay_Us_Emp_Fed_Tax_Rules_f
25 Where assignment_id = p_assignment_id;
26
27 --
28 -- ----------------------------------------------------------------------------
29 -- |------------------------< create_county_tax_rule >----------------------|
30 -- ----------------------------------------------------------------------------
31 --
32 procedure create_county_tax_rule
33 (p_validate in boolean default false
34 ,p_emp_county_tax_rule_id out nocopy number
35 ,p_effective_start_date out nocopy date
36 ,p_effective_end_date out nocopy date
37 ,p_assignment_id in number
38 ,p_state_code in varchar2
39 ,p_county_code in varchar2
40 ,p_additional_wa_rate in number
41 ,p_filing_status_code in varchar2
42 ,p_lit_additional_tax in number
43 ,p_lit_override_amount in number
44 ,p_lit_override_rate in number
45 ,p_withholding_allowances in number
46 ,p_lit_exempt in varchar2 default null
47 ,p_sd_exempt in varchar2 default null
48 ,p_ht_exempt in varchar2 default null
49 ,p_wage_exempt in varchar2 default null
50 ,p_school_district_code in varchar2 default null
51 ,p_object_version_number out nocopy number
52 ,p_effective_date in date
53 ,p_attribute_category in varchar2 default null
54 ,p_attribute1 in varchar2 default null
55 ,p_attribute2 in varchar2 default null
56 ,p_attribute3 in varchar2 default null
57 ,p_attribute4 in varchar2 default null
58 ,p_attribute5 in varchar2 default null
59 ,p_attribute6 in varchar2 default null
60 ,p_attribute7 in varchar2 default null
64 ,p_attribute11 in varchar2 default null
61 ,p_attribute8 in varchar2 default null
62 ,p_attribute9 in varchar2 default null
63 ,p_attribute10 in varchar2 default null
65 ,p_attribute12 in varchar2 default null
66 ,p_attribute13 in varchar2 default null
67 ,p_attribute14 in varchar2 default null
68 ,p_attribute15 in varchar2 default null
69 ,p_attribute16 in varchar2 default null
70 ,p_attribute17 in varchar2 default null
71 ,p_attribute18 in varchar2 default null
72 ,p_attribute19 in varchar2 default null
73 ,p_attribute20 in varchar2 default null
74 ,p_attribute21 in varchar2 default null
75 ,p_attribute22 in varchar2 default null
76 ,p_attribute23 in varchar2 default null
77 ,p_attribute24 in varchar2 default null
78 ,p_attribute25 in varchar2 default null
79 ,p_attribute26 in varchar2 default null
80 ,p_attribute27 in varchar2 default null
81 ,p_attribute28 in varchar2 default null
82 ,p_attribute29 in varchar2 default null
83 ,p_attribute30 in varchar2 default null
84 ,p_cnt_information_category in varchar2 default null
85 ,p_cnt_information1 in varchar2 default null
86 ,p_cnt_information2 in varchar2 default null
87 ,p_cnt_information3 in varchar2 default null
88 ,p_cnt_information4 in varchar2 default null
89 ,p_cnt_information5 in varchar2 default null
90 ,p_cnt_information6 in varchar2 default null
91 ,p_cnt_information7 in varchar2 default null
92 ,p_cnt_information8 in varchar2 default null
93 ,p_cnt_information9 in varchar2 default null
94 ,p_cnt_information10 in varchar2 default null
95 ,p_cnt_information11 in varchar2 default null
96 ,p_cnt_information12 in varchar2 default null
97 ,p_cnt_information13 in varchar2 default null
98 ,p_cnt_information14 in varchar2 default null
99 ,p_cnt_information15 in varchar2 default null
100 ,p_cnt_information16 in varchar2 default null
101 ,p_cnt_information17 in varchar2 default null
102 ,p_cnt_information18 in varchar2 default null
103 ,p_cnt_information19 in varchar2 default null
104 ,p_cnt_information20 in varchar2 default null
105 ,p_cnt_information21 in varchar2 default null
106 ,p_cnt_information22 in varchar2 default null
107 ,p_cnt_information23 in varchar2 default null
108 ,p_cnt_information24 in varchar2 default null
109 ,p_cnt_information25 in varchar2 default null
110 ,p_cnt_information26 in varchar2 default null
111 ,p_cnt_information27 in varchar2 default null
112 ,p_cnt_information28 in varchar2 default null
113 ,p_cnt_information29 in varchar2 default null
114 ,p_cnt_information30 in varchar2 default null
115 ) is
116 --
117 -- Declare cursors and local variables
118 --
119 l_business_group_id per_assignments_f.business_group_id%TYPE;
120 l_defaulting_date pay_us_emp_county_tax_rules_f.effective_end_date%TYPE;
121 l_emp_county_tax_rule_id pay_us_emp_county_tax_rules_f.emp_county_tax_rule_id%TYPE;
122 l_effective_start_date pay_us_emp_county_tax_rules_f.effective_start_date%TYPE;
123 l_effective_end_date pay_us_emp_county_tax_rules_f.effective_end_date%TYPE;
124 l_proc varchar2(72) := g_package||'create_county_tax_rule';
125 l_object_version_number pay_us_emp_county_tax_rules_f.object_version_number%TYPE;
126 l_jurisdiction_code pay_us_emp_county_tax_rules_f.jurisdiction_code%TYPE;
127 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE := null;
128 --
129 /** added for bug 13891625**/
130 cursor csr_county_tax_rule is
131 select null
132 from pay_us_emp_county_tax_rules_f sta
133 where sta.assignment_id = p_assignment_id
134 and sta.state_code = p_state_code
135 and sta.county_code= p_county_code;
136
137 l_dummy varchar2(5);
138 --End of changes for bug 13891625
139
140 begin
141 --
142 hr_utility.set_location('Entering:'|| l_proc, 10);
143 --
144 -- Issue a savepoint if operating in validation only mode
145 --
146 savepoint create_county_tax_rule;
147
148 --
149 hr_utility.set_location(l_proc, 15);
150 --
151 -- Process Logic
152 --
153 /*
154 begin
155 --
156 -- Start of API User Hook for the before hook of create_county_tax_rule
157 --
158 pay_cnt_bk1.create_county_tax_rule_b
159 (
160 p_assignment_id => p_assignment_id
161 ,p_state_code => p_state_code
162 ,p_county_code => p_county_code
163 ,p_business_group_id => l_business_group_id
164 ,p_additional_wa_rate => p_additional_wa_rate
165 ,p_filing_status_code => p_filing_status_code
166 ,p_jurisdiction_code => l_jurisdiction_code
167 ,p_lit_additional_tax => p_lit_additional_tax
171 ,p_lit_exempt => p_lit_exempt
168 ,p_lit_override_amount => p_lit_override_amount
169 ,p_lit_override_rate => p_lit_override_rate
170 ,p_withholding_allowances => p_withholding_allowances
172 ,p_sd_exempt => p_sd_exempt
173 ,p_ht_exempt => p_ht_exempt
174 ,p_wage_exempt => p_wage_exempt
175 ,p_school_district_code => p_school_district_code
176 );
177 exception
178 when hr_api.cannot_find_prog_unit then
179 hr_api.cannot_find_prog_unit_error
180 (
181 p_module_name => 'CREATE_COUNTY_TAX_RULE'
182 ,p_hook_type => 'BP'
183 );
184 --
185 -- End of API User Hook for the before hook of create_county_tax_rule
186 --
187 end;
188 */
189 --
190 --
191 -- Business Process Main Code
192 --
193 Open csr_derive_Business_Grp(p_assignment_id, p_effective_date);
194 Fetch csr_derive_business_grp into l_business_group_id;
195 If csr_derive_business_grp%Found then
196 close csr_derive_business_grp;
197 open csr_defaulting_date(p_assignment_id);
198 Fetch csr_defaulting_date into l_defaulting_date;
199 If l_defaulting_date is null then
200 close csr_defaulting_date;
201 hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
202 hr_utility.set_message_token('table_name', 'PAY_US_EMP_FED_TAX_RULES_F');
203 hr_utility.raise_error;
204 Else
205 l_jurisdiction_code := p_state_code||'-'||p_county_code||'-0000';
206 close csr_defaulting_date;
207 hr_utility.set_location(l_proc, 20);
208
209 /** added for bug 13891625**/
210 open csr_county_tax_rule;
211 fetch csr_county_tax_rule into l_dummy;
212 if csr_county_tax_rule%FOUND then
213 close csr_county_tax_rule;
214 --
215 -- This county tax rule already exists
216 --
217 hr_utility.set_message(801, 'PAY_75275_COUNTY_TAX_ONE_RULE');
218 hr_utility.raise_error;
219 end if;
220 close csr_county_tax_rule;
221 -- End of changes for bug 13891625
222
223 pay_cnt_ins.ins
224 (
225 p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
226 ,p_effective_start_date => l_effective_start_date
227 ,p_effective_end_date => l_effective_end_date
228 ,p_assignment_id => p_assignment_id
229 ,p_state_code => p_state_code
230 ,p_county_code => p_county_code
231 ,p_business_group_id => l_business_group_id
232 ,p_additional_wa_rate => p_additional_wa_rate
233 ,p_filing_status_code => p_filing_status_code
234 ,p_jurisdiction_code => l_jurisdiction_code
235 ,p_lit_additional_tax => p_lit_additional_tax
236 ,p_lit_override_amount => p_lit_override_amount
237 ,p_lit_override_rate => p_lit_override_rate
238 ,p_withholding_allowances => p_withholding_allowances
239 ,p_lit_exempt => p_lit_exempt
240 ,p_sd_exempt => p_sd_exempt
241 ,p_ht_exempt => p_ht_exempt
242 ,p_wage_exempt => p_wage_exempt
243 ,p_school_district_code => p_school_district_code
244 ,p_object_version_number => l_object_version_number
245 ,p_effective_date => l_defaulting_date
246 ,p_attribute_category => p_attribute_category
247 ,p_attribute1 => p_attribute1
248 ,p_attribute2 => p_attribute2
249 ,p_attribute3 => p_attribute3
250 ,p_attribute4 => p_attribute4
251 ,p_attribute5 => p_attribute5
252 ,p_attribute6 => p_attribute6
253 ,p_attribute7 => p_attribute7
254 ,p_attribute8 => p_attribute8
255 ,p_attribute9 => p_attribute9
256 ,p_attribute10 => p_attribute10
257 ,p_attribute11 => p_attribute11
258 ,p_attribute12 => p_attribute12
259 ,p_attribute13 => p_attribute13
260 ,p_attribute14 => p_attribute14
261 ,p_attribute15 => p_attribute15
262 ,p_attribute16 => p_attribute16
263 ,p_attribute17 => p_attribute17
264 ,p_attribute18 => p_attribute18
265 ,p_attribute19 => p_attribute19
266 ,p_attribute20 => p_attribute20
267 ,p_attribute21 => p_attribute21
268 ,p_attribute22 => p_attribute22
269 ,p_attribute23 => p_attribute23
270 ,p_attribute24 => p_attribute24
271 ,p_attribute25 => p_attribute25
272 ,p_attribute26 => p_attribute26
273 ,p_attribute27 => p_attribute27
274 ,p_attribute28 => p_attribute28
275 ,p_attribute29 => p_attribute29
276 ,p_attribute30 => p_attribute30
277 ,p_cnt_information_category => p_cnt_information_category
278 ,p_cnt_information1 => p_cnt_information1
282 ,p_cnt_information5 => p_cnt_information5
279 ,p_cnt_information2 => p_cnt_information2
280 ,p_cnt_information3 => p_cnt_information3
281 ,p_cnt_information4 => p_cnt_information4
283 ,p_cnt_information6 => p_cnt_information6
284 ,p_cnt_information7 => p_cnt_information7
285 ,p_cnt_information8 => p_cnt_information8
286 ,p_cnt_information9 => p_cnt_information9
287 ,p_cnt_information10 => p_cnt_information10
288 ,p_cnt_information11 => p_cnt_information11
289 ,p_cnt_information12 => p_cnt_information12
290 ,p_cnt_information13 => p_cnt_information13
291 ,p_cnt_information14 => p_cnt_information14
292 ,p_cnt_information15 => p_cnt_information15
293 ,p_cnt_information16 => p_cnt_information16
294 ,p_cnt_information17 => p_cnt_information17
295 ,p_cnt_information18 => p_cnt_information18
296 ,p_cnt_information19 => p_cnt_information19
297 ,p_cnt_information20 => p_cnt_information20
298 ,p_cnt_information21 => p_cnt_information21
299 ,p_cnt_information22 => p_cnt_information22
300 ,p_cnt_information23 => p_cnt_information23
301 ,p_cnt_information24 => p_cnt_information24
302 ,p_cnt_information25 => p_cnt_information25
303 ,p_cnt_information26 => p_cnt_information26
304 ,p_cnt_information27 => p_cnt_information27
305 ,p_cnt_information28 => p_cnt_information28
306 ,p_cnt_information29 => p_cnt_information29
307 ,p_cnt_information30 => p_cnt_information30
308 );
309 --
310 hr_utility.set_location(l_proc, 25);
311 --
312 pay_us_tax_internal.maintain_tax_percentage
313 (
314 p_assignment_id => p_assignment_id,
315 p_effective_date => p_effective_date,
316 p_state_code => p_state_code,
317 p_county_code => p_county_code,
318 p_city_code => '0000',
319 p_calculate_pct => FALSE,
320 p_datetrack_mode => 'INSERT',
321 p_effective_start_date => l_effective_start_date,
322 p_effective_end_date => l_effective_end_date
323 );
324 --
325 --
326 -- Call create_asg_geo_row to create rows in the pay_us_asg_reporting
327 -- table if this is a new county
328 --
329 pay_asg_geo_pkg.create_asg_geo_row(
330 p_assignment_id => p_assignment_id
331 ,p_jurisdiction => l_jurisdiction_code
332 );
333 End if;
334 Else
335 close csr_derive_business_grp;
336 hr_utility.set_message(801, 'HR_7348_ASSIGNMENT_INVALID');
337 hr_utility.set_message_token('PROCEDURE', l_proc);
338 hr_utility.set_message_token('STEP','30');
339 hr_utility.raise_error;
340 End if;
341 --
342 --
343 /*
344 begin
345 --
346 -- Start of API User Hook for the after hook of create_county_tax_rule
347 --
348 pay_cnt_bk1.create_county_tax_rule_a
349 (
350 p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
351 ,p_effective_start_date => l_effective_start_date
352 ,p_effective_end_date => l_effective_end_date
353 ,p_assignment_id => p_assignment_id
354 ,p_state_code => p_state_code
355 ,p_county_code => p_county_code
356 ,p_business_group_id => l_business_group_id
357 ,p_additional_wa_rate => p_additional_wa_rate
358 ,p_filing_status_code => p_filing_status_code
359 ,p_jurisdiction_code => l_jurisdiction_code
360 ,p_lit_additional_tax => p_lit_additional_tax
361 ,p_lit_override_amount => p_lit_override_amount
362 ,p_lit_override_rate => p_lit_override_rate
363 ,p_withholding_allowances => p_withholding_allowances
364 ,p_lit_exempt => p_lit_exempt
365 ,p_sd_exempt => p_sd_exempt
366 ,p_ht_exempt => p_ht_exempt
367 ,p_wage_exempt => p_wage_exempt
368 ,p_school_district_code => p_school_district_code
369 ,p_object_version_number => l_object_version_number
370 );
371 exception
372 when hr_api.cannot_find_prog_unit then
373 hr_api.cannot_find_prog_unit_error
374 (p_module_name => 'CREATE_COUNTY_TAX_RULE'
375 ,p_hook_type => 'AP'
376 );
377 --
378 -- End of API User Hook for the after hook of create_county_tax_rule
379 --
380 end;
381 */
382 --
383 hr_utility.set_location(l_proc, 40);
384 --
385 -- When in validation only mode raise the Validate_Enabled exception
386 --
387 if p_validate then
388 raise hr_api.validate_enabled;
389 end if;
390 --
391 -- Set all output arguments
392 --
396 p_object_version_number := l_object_version_number;
393 p_emp_county_tax_rule_id := l_emp_county_tax_rule_id;
394 p_effective_start_date := l_effective_start_date;
395 p_effective_end_date := l_effective_end_date;
397 --
398 hr_utility.set_location(' Leaving:'||l_proc, 50);
399 --
400 exception
401 --
402 when hr_api.validate_enabled then
403 --
404 -- As the Validate_Enabled exception has been raised
405 -- we must rollback to the savepoint
406 --
407 ROLLBACK TO create_county_tax_rule;
408 --
409 -- Only set output warning arguments
410 -- (Any key or derived arguments must be set to null
411 -- when validation only mode is being used.)
412 --
413 p_emp_county_tax_rule_id := null;
414 p_effective_start_date := null;
415 p_effective_end_date := null;
416 p_object_version_number := null;
417 hr_utility.set_location(' Leaving:'||l_proc, 70);
418 --
419 when others then
420 --
421 -- A validation or unexpected error has occurred
422 --
423 ROLLBACK TO create_county_tax_rule;
424 --
425 -- Reset IN OUT params and set OUT params.
426 --
427 p_emp_county_tax_rule_id := null;
428 p_effective_start_date := null;
429 p_effective_end_date := null;
430 p_object_version_number := null;
431 --
432 raise;
433 --
434 end create_county_tax_rule;
435 --
436 -- ----------------------------------------------------------------------------
437 -- |------------------------< update_county_tax_rule >------------------------|
438 -- ----------------------------------------------------------------------------
439 --
440 procedure update_county_tax_rule
441 (p_validate in boolean default false
442 ,p_emp_county_tax_rule_id in number
443 ,p_effective_start_date out nocopy date
444 ,p_effective_end_date out nocopy date
445 ,p_additional_wa_rate in number default hr_api.g_number
446 ,p_filing_status_code in varchar2 default hr_api.g_varchar2
447 ,p_lit_additional_tax in number default hr_api.g_number
448 ,p_lit_override_amount in number default hr_api.g_number
449 ,p_lit_override_rate in number default hr_api.g_number
450 ,p_withholding_allowances in number default hr_api.g_number
451 ,p_lit_exempt in varchar2 default hr_api.g_varchar2
452 ,p_sd_exempt in varchar2 default hr_api.g_varchar2
453 ,p_ht_exempt in varchar2 default hr_api.g_varchar2
454 ,p_wage_exempt in varchar2 default hr_api.g_varchar2
455 ,p_school_district_code in varchar2 default hr_api.g_varchar2
456 ,p_object_version_number in out nocopy number
457 ,p_effective_date in date
458 ,p_datetrack_mode in varchar2
459 ,p_attribute_category in varchar2 default hr_api.g_varchar2
460 ,p_attribute1 in varchar2 default hr_api.g_varchar2
461 ,p_attribute2 in varchar2 default hr_api.g_varchar2
462 ,p_attribute3 in varchar2 default hr_api.g_varchar2
463 ,p_attribute4 in varchar2 default hr_api.g_varchar2
464 ,p_attribute5 in varchar2 default hr_api.g_varchar2
465 ,p_attribute6 in varchar2 default hr_api.g_varchar2
466 ,p_attribute7 in varchar2 default hr_api.g_varchar2
467 ,p_attribute8 in varchar2 default hr_api.g_varchar2
468 ,p_attribute9 in varchar2 default hr_api.g_varchar2
469 ,p_attribute10 in varchar2 default hr_api.g_varchar2
470 ,p_attribute11 in varchar2 default hr_api.g_varchar2
471 ,p_attribute12 in varchar2 default hr_api.g_varchar2
472 ,p_attribute13 in varchar2 default hr_api.g_varchar2
473 ,p_attribute14 in varchar2 default hr_api.g_varchar2
474 ,p_attribute15 in varchar2 default hr_api.g_varchar2
475 ,p_attribute16 in varchar2 default hr_api.g_varchar2
476 ,p_attribute17 in varchar2 default hr_api.g_varchar2
477 ,p_attribute18 in varchar2 default hr_api.g_varchar2
478 ,p_attribute19 in varchar2 default hr_api.g_varchar2
479 ,p_attribute20 in varchar2 default hr_api.g_varchar2
480 ,p_attribute21 in varchar2 default hr_api.g_varchar2
481 ,p_attribute22 in varchar2 default hr_api.g_varchar2
482 ,p_attribute23 in varchar2 default hr_api.g_varchar2
483 ,p_attribute24 in varchar2 default hr_api.g_varchar2
484 ,p_attribute25 in varchar2 default hr_api.g_varchar2
485 ,p_attribute26 in varchar2 default hr_api.g_varchar2
486 ,p_attribute27 in varchar2 default hr_api.g_varchar2
487 ,p_attribute28 in varchar2 default hr_api.g_varchar2
488 ,p_attribute29 in varchar2 default hr_api.g_varchar2
489 ,p_attribute30 in varchar2 default hr_api.g_varchar2
490 ,p_cnt_information_category in varchar2 default hr_api.g_varchar2
491 ,p_cnt_information1 in varchar2 default hr_api.g_varchar2
492 ,p_cnt_information2 in varchar2 default hr_api.g_varchar2
493 ,p_cnt_information3 in varchar2 default hr_api.g_varchar2
494 ,p_cnt_information4 in varchar2 default hr_api.g_varchar2
498 ,p_cnt_information8 in varchar2 default hr_api.g_varchar2
495 ,p_cnt_information5 in varchar2 default hr_api.g_varchar2
496 ,p_cnt_information6 in varchar2 default hr_api.g_varchar2
497 ,p_cnt_information7 in varchar2 default hr_api.g_varchar2
499 ,p_cnt_information9 in varchar2 default hr_api.g_varchar2
500 ,p_cnt_information10 in varchar2 default hr_api.g_varchar2
501 ,p_cnt_information11 in varchar2 default hr_api.g_varchar2
502 ,p_cnt_information12 in varchar2 default hr_api.g_varchar2
503 ,p_cnt_information13 in varchar2 default hr_api.g_varchar2
504 ,p_cnt_information14 in varchar2 default hr_api.g_varchar2
505 ,p_cnt_information15 in varchar2 default hr_api.g_varchar2
506 ,p_cnt_information16 in varchar2 default hr_api.g_varchar2
507 ,p_cnt_information17 in varchar2 default hr_api.g_varchar2
508 ,p_cnt_information18 in varchar2 default hr_api.g_varchar2
509 ,p_cnt_information19 in varchar2 default hr_api.g_varchar2
510 ,p_cnt_information20 in varchar2 default hr_api.g_varchar2
511 ,p_cnt_information21 in varchar2 default hr_api.g_varchar2
512 ,p_cnt_information22 in varchar2 default hr_api.g_varchar2
513 ,p_cnt_information23 in varchar2 default hr_api.g_varchar2
514 ,p_cnt_information24 in varchar2 default hr_api.g_varchar2
515 ,p_cnt_information25 in varchar2 default hr_api.g_varchar2
516 ,p_cnt_information26 in varchar2 default hr_api.g_varchar2
517 ,p_cnt_information27 in varchar2 default hr_api.g_varchar2
518 ,p_cnt_information28 in varchar2 default hr_api.g_varchar2
519 ,p_cnt_information29 in varchar2 default hr_api.g_varchar2
520 ,p_cnt_information30 in varchar2 default hr_api.g_varchar2
521 ) is
522 --
523 -- Declare cursors and local variables
524 --
525 l_proc varchar2(72) := g_package||'update_county_tax_rule';
526 l_object_version_number pay_us_emp_county_tax_rules_f.object_version_number%TYPE;
527 l_effective_start_date pay_us_emp_county_tax_rules_f.effective_start_date%TYPE;
528 l_effective_end_date pay_us_emp_county_tax_rules_f.effective_end_date%TYPE;
529 l_assignment_id pay_us_emp_county_tax_rules_f.assignment_id%TYPE;
530 l_jurisdiction_code pay_us_emp_county_tax_rules_f.jurisdiction_code%TYPE;
531 --
532 CURSOR csr_assignment_id (p_rule_id NUMBER,
533 p_eff_date DATE) IS
534 SELECT ctr.assignment_id,
535 ctr.state_code||'-'||ctr.school_district_code
536 FROM pay_us_emp_county_tax_rules_f ctr
537 WHERE ctr.emp_county_tax_rule_id = p_rule_id
538 AND p_eff_date BETWEEN ctr.effective_start_date
539 AND ctr.effective_end_date;
540 --
541 begin
542 --
543 hr_utility.set_location('Entering:'|| l_proc, 10);
544 --
545 -- Issue a savepoint if operating in validation only mode
546 --
547 savepoint update_county_tax_rule;
548
549 --
550 hr_utility.set_location(l_proc, 15);
551 --
552 -- Check to see if we need to maintain the tax records.
553 --
554 IF hr_general.chk_maintain_tax_records = 'N' Then
555 return;
556 end if;
557
558 --
559 -- Process Logic
560 --
561 l_object_version_number := p_object_version_number;
562 --
563 /*
564 begin
565 --
566 -- Start of API User Hook for the before hook of update_county_tax_rule
567 --
568 pay_cnt_bk2.update_county_tax_rule_b
569 (
570 p_emp_county_tax_rule_id => p_emp_county_tax_rule_id
571 ,p_assignment_id => p_assignment_id
572 ,p_state_code => p_state_code
573 ,p_county_code => p_county_code
574 ,p_business_group_id => l_business_group_id
575 ,p_additional_wa_rate => p_additional_wa_rate
576 ,p_filing_status_code => p_filing_status_code
577 ,p_jurisdiction_code => l_jurisdiction_code
578 ,p_lit_additional_tax => p_lit_additional_tax
579 ,p_lit_override_amount => p_lit_override_amount
580 ,p_lit_override_rate => p_lit_override_rate
581 ,p_withholding_allowances => p_withholding_allowances
582 ,p_lit_exempt => p_lit_exempt
583 ,p_sd_exempt => p_sd_exempt
584 ,p_ht_exempt => p_ht_exempt
585 ,p_wage_exempt => p_wage_exempt
586 ,p_school_district_code => p_school_district_code
587 ,p_object_version_number => p_object_version_number
588 );
589 exception
590 when hr_api.cannot_find_prog_unit then
591 hr_api.cannot_find_prog_unit_error
592 (p_module_name => 'UPDATE_COUNTY_TAX_RULE'
593 ,p_hook_type => 'BP'
594 );
595 --
596 -- End of API User Hook for the before hook of update_county_tax_rule
597 --
598 end;
599 */
600 --
601 hr_utility.set_location(l_proc, 20);
602 --
603 pay_cnt_upd.upd
604 (
605 p_emp_county_tax_rule_id => p_emp_county_tax_rule_id
606 ,p_effective_start_date => l_effective_start_date
607 ,p_effective_end_date => l_effective_end_date
608 ,p_additional_wa_rate => p_additional_wa_rate
609 ,p_filing_status_code => p_filing_status_code
613 ,p_withholding_allowances => p_withholding_allowances
610 ,p_lit_additional_tax => p_lit_additional_tax
611 ,p_lit_override_amount => p_lit_override_amount
612 ,p_lit_override_rate => p_lit_override_rate
614 ,p_lit_exempt => p_lit_exempt
615 ,p_sd_exempt => p_sd_exempt
616 ,p_ht_exempt => p_ht_exempt
617 ,p_wage_exempt => p_wage_exempt
618 ,p_school_district_code => p_school_district_code
619 ,p_object_version_number => l_object_version_number
620 ,p_effective_date => p_effective_date
621 ,p_datetrack_mode => p_datetrack_mode
622 ,p_attribute_category => p_attribute_category
623 ,p_attribute1 => p_attribute1
624 ,p_attribute2 => p_attribute2
625 ,p_attribute3 => p_attribute3
626 ,p_attribute4 => p_attribute4
627 ,p_attribute5 => p_attribute5
628 ,p_attribute6 => p_attribute6
629 ,p_attribute7 => p_attribute7
630 ,p_attribute8 => p_attribute8
631 ,p_attribute9 => p_attribute9
632 ,p_attribute10 => p_attribute10
633 ,p_attribute11 => p_attribute11
634 ,p_attribute12 => p_attribute12
635 ,p_attribute13 => p_attribute13
636 ,p_attribute14 => p_attribute14
637 ,p_attribute15 => p_attribute15
638 ,p_attribute16 => p_attribute16
639 ,p_attribute17 => p_attribute17
640 ,p_attribute18 => p_attribute18
641 ,p_attribute19 => p_attribute19
642 ,p_attribute20 => p_attribute20
646 ,p_attribute24 => p_attribute24
643 ,p_attribute21 => p_attribute21
644 ,p_attribute22 => p_attribute22
645 ,p_attribute23 => p_attribute23
647 ,p_attribute25 => p_attribute25
648 ,p_attribute26 => p_attribute26
649 ,p_attribute27 => p_attribute27
650 ,p_attribute28 => p_attribute28
651 ,p_attribute29 => p_attribute29
652 ,p_attribute30 => p_attribute30
653 ,p_cnt_information_category => p_cnt_information_category
654 ,p_cnt_information1 => p_cnt_information1
655 ,p_cnt_information2 => p_cnt_information2
656 ,p_cnt_information3 => p_cnt_information3
657 ,p_cnt_information4 => p_cnt_information4
658 ,p_cnt_information5 => p_cnt_information5
659 ,p_cnt_information6 => p_cnt_information6
660 ,p_cnt_information7 => p_cnt_information7
661 ,p_cnt_information8 => p_cnt_information8
662 ,p_cnt_information9 => p_cnt_information9
663 ,p_cnt_information10 => p_cnt_information10
664 ,p_cnt_information11 => p_cnt_information11
665 ,p_cnt_information12 => p_cnt_information12
666 ,p_cnt_information13 => p_cnt_information13
667 ,p_cnt_information14 => p_cnt_information14
668 ,p_cnt_information15 => p_cnt_information15
669 ,p_cnt_information16 => p_cnt_information16
670 ,p_cnt_information17 => p_cnt_information17
671 ,p_cnt_information18 => p_cnt_information18
672 ,p_cnt_information19 => p_cnt_information19
673 ,p_cnt_information20 => p_cnt_information20
674 ,p_cnt_information21 => p_cnt_information21
675 ,p_cnt_information22 => p_cnt_information22
676 ,p_cnt_information23 => p_cnt_information23
677 ,p_cnt_information24 => p_cnt_information24
678 ,p_cnt_information25 => p_cnt_information25
679 ,p_cnt_information26 => p_cnt_information26
680 ,p_cnt_information27 => p_cnt_information27
681 ,p_cnt_information28 => p_cnt_information28
682 ,p_cnt_information29 => p_cnt_information29
683 ,p_cnt_information30 => p_cnt_information30
684 );
685 --
686 --
687 -- Call create_asg_geo_row to create rows in the pay_us_asg_reporting
688 -- table if the county school district has changed
689 --
690 if p_school_district_code IS NOT NULL AND
694 p_effective_date);
691 p_school_district_code <> hr_api.g_varchar2 THEN
692 hr_utility.set_location(l_proc, 22);
693 OPEN csr_assignment_id(p_emp_county_tax_rule_id,
695 FETCH csr_assignment_id
696 INTO l_assignment_id,
697 l_jurisdiction_code;
698 pay_asg_geo_pkg.create_asg_geo_row(
699 p_assignment_id => l_assignment_id
700 ,p_jurisdiction => l_jurisdiction_code
701 );
702 CLOSE csr_assignment_id;
703 end if;
704 /*
705 begin
706 --
707 -- Start of API User Hook for the after hook of update_county_tax_rule
708 --
709 pay_cnt_bk2.update_county_tax_rule_a
710 (
711 p_emp_county_tax_rule_id => p_emp_county_tax_rule_id
712 ,p_effective_start_date => l_effective_start_date
713 ,p_effective_end_date => l_effective_end_date
714 ,p_assignment_id => p_assignment_id
715 ,p_state_code => p_state_code
716 ,p_county_code => p_county_code
717 ,p_business_group_id => l_business_group_id
718 ,p_additional_wa_rate => p_additional_wa_rate
719 ,p_filing_status_code => p_filing_status_code
720 ,p_jurisdiction_code => l_jurisdiction_code
721 ,p_lit_additional_tax => p_lit_additional_tax
722 ,p_lit_override_amount => p_lit_override_amount
723 ,p_lit_override_rate => p_lit_override_rate
724 ,p_withholding_allowances => p_withholding_allowances
725 ,p_lit_exempt => p_lit_exempt
726 ,p_sd_exempt => p_sd_exempt
727 ,p_ht_exempt => p_ht_exempt
728 ,p_wage_exempt => p_wage_exempt
729 ,p_school_district_code => p_school_district_code
730 ,p_object_version_number => l_object_version_number
731 );
732 exception
733 when hr_api.cannot_find_prog_unit then
734 hr_api.cannot_find_prog_unit_error
735 (p_module_name => 'UPDATE_COUNTY_TAX_RULE'
736 ,p_hook_type => 'AP'
737 );
738 --
739 -- End of API User Hook for the after hook of update_county_tax_rule
740 --
741 end;
742 */
743 --
744 hr_utility.set_location(l_proc, 25);
745 --
746 -- When in validation only mode raise the Validate_Enabled exception
747 --
748 if p_validate then
749 raise hr_api.validate_enabled;
750 end if;
751 --
752 -- Set all output arguments
753 --
754 p_object_version_number := l_object_version_number;
755 p_effective_start_date := l_effective_start_date;
756 p_effective_end_date := l_effective_end_date;
757 --
758 hr_utility.set_location(' Leaving:'||l_proc, 30);
759 --
760 exception
761 --
762 when hr_api.validate_enabled then
763 --
764 -- As the Validate_Enabled exception has been raised
765 -- we must rollback to the savepoint
766 --
767 ROLLBACK TO update_county_tax_rule;
768 --
769 -- Only set output warning arguments
770 -- (Any key or derived arguments must be set to null
771 -- when validation only mode is being used.)
772 --
773 p_object_version_number := l_object_version_number;
774 p_effective_start_date := null;
775 p_effective_end_date := null;
776 hr_utility.set_location(' Leaving:'||l_proc, 50);
777 --
778 when others then
779 --
780 -- A validation or unexpected error has occurred
781 --
782 ROLLBACK TO update_county_tax_rule;
783 --
784 -- Reset IN OUT params and set OUT params.
785 --
789 --
786 p_object_version_number := l_object_version_number;
787 p_effective_start_date := null;
788 p_effective_end_date := null;
790 raise;
791 --
792 end update_county_tax_rule;
793 --
794 -- ----------------------------------------------------------------------------
795 -- |-------------------------------< lck >------------------------------------|
796 -- ----------------------------------------------------------------------------
797 --
798 procedure lck
799 (
800 p_emp_county_tax_rule_id in number
801 ,p_object_version_number in number
802 ,p_effective_date in date
803 ,p_datetrack_mode in varchar2
804 ,p_validation_start_date out nocopy date
805 ,p_validation_end_date out nocopy date
806 ) is
807 --
808 --
809 -- Declare cursors and local variables
810 --
811 l_proc varchar2(72) := g_package||'update_county_tax_rule';
812 l_validation_start_date date;
813 l_validation_end_date date;
814 --
815 begin
816 --
817 hr_utility.set_location('Entering:'|| l_proc, 10);
818 --
819 pay_cnt_shd.lck
820 (
821 p_emp_county_tax_rule_id => p_emp_county_tax_rule_id
822 ,p_validation_start_date => l_validation_start_date
823 ,p_validation_end_date => l_validation_end_date
824 ,p_object_version_number => p_object_version_number
825 ,p_effective_date => p_effective_date
826 ,p_datetrack_mode => p_datetrack_mode
827 );
828 --
829 hr_utility.set_location(' Leaving:'||l_proc, 70);
830 --
831 end lck;
832 --
833 end pay_county_tax_rule_api;