[Home] [Help]
PACKAGE BODY: APPS.PAY_CITY_TAX_RULE_API
Source
1 PACKAGE BODY pay_city_tax_rule_api AS
2 /* $Header: pyctyapi.pkb 120.1.12020000.2 2012/07/06 09:47:16 vmaripal ship $ */
3 --
4 -- Package Variables
5 --
9 --
6 g_package varchar2(33) := ' pay_cty_api.';
7 --
8 -- Global package cursor
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 -- |------------------------< create_city_tax_rule >----------------------|
29 -- ----------------------------------------------------------------------------
30 --
31 procedure create_city_tax_rule
32 (p_validate in boolean default false
33 ,p_emp_city_tax_rule_id out nocopy number
34 ,p_effective_start_date out nocopy date
35 ,p_effective_end_date out nocopy date
36 ,p_assignment_id in number
37 ,p_state_code in varchar2
38 ,p_county_code in varchar2
39 ,p_city_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
61 ,p_attribute8 in varchar2 default null
62 ,p_attribute9 in varchar2 default null
63 ,p_attribute10 in varchar2 default null
64 ,p_attribute11 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_cty_information_category in varchar2 default null
85 ,p_cty_information1 in varchar2 default null
86 ,p_cty_information2 in varchar2 default null
87 ,p_cty_information3 in varchar2 default null
88 ,p_cty_information4 in varchar2 default null
89 ,p_cty_information5 in varchar2 default null
90 ,p_cty_information6 in varchar2 default null
91 ,p_cty_information7 in varchar2 default null
92 ,p_cty_information8 in varchar2 default null
93 ,p_cty_information9 in varchar2 default null
94 ,p_cty_information10 in varchar2 default null
95 ,p_cty_information11 in varchar2 default null
96 ,p_cty_information12 in varchar2 default null
97 ,p_cty_information13 in varchar2 default null
98 ,p_cty_information14 in varchar2 default null
99 ,p_cty_information15 in varchar2 default null
100 ,p_cty_information16 in varchar2 default null
101 ,p_cty_information17 in varchar2 default null
102 ,p_cty_information18 in varchar2 default null
103 ,p_cty_information19 in varchar2 default null
104 ,p_cty_information20 in varchar2 default null
105 ,p_cty_information21 in varchar2 default null
106 ,p_cty_information22 in varchar2 default null
107 ,p_cty_information23 in varchar2 default null
108 ,p_cty_information24 in varchar2 default null
109 ,p_cty_information25 in varchar2 default null
110 ,p_cty_information26 in varchar2 default null
111 ,p_cty_information27 in varchar2 default null
112 ,p_cty_information28 in varchar2 default null
113 ,p_cty_information29 in varchar2 default null
114 ,p_cty_information30 in varchar2 default null
115 ) is
116 --
117 l_jurisdiction_code pay_us_emp_city_tax_rules.jurisdiction_code%TYPE;
118 l_business_group_id per_assignments_f.business_group_id%TYPE;
119 l_defaulting_date pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
120 l_emp_city_tax_rule_id pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE;
121 l_effective_start_date pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
122 l_effective_end_date pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
123 l_proc varchar2(72) := g_package||'create_city_tax_rule';
124 l_object_version_number pay_us_emp_city_tax_rules_f.object_version_number%TYPE;
125 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE := null;
126 --
127
128 /** added for bug 13872151 **/
129 cursor csr_city_tax_rule is
130 select null
131 from pay_us_emp_city_tax_rules_f sta
132 where sta.assignment_id = p_assignment_id
133 and sta.state_code = p_state_code
134 and sta.county_code= p_county_code
135 and sta.city_code= p_city_code;
136
137 l_dummy NUMBER;
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
145 --
146 savepoint create_city_tax_rule;
147
148 --
149 hr_utility.set_location(l_proc, 15);
150 --
151 -- Process Logic
152 --
153 -- Derive the jurisdiction code
154 --
155 l_jurisdiction_code := p_state_code||'-'||p_county_code||'-'||p_city_code;
156 --
157 -- Derive the business group id
158 --
159 Open csr_derive_business_grp(p_assignment_id, p_effective_date);
160 fetch csr_derive_business_grp into l_business_group_id;
161 if csr_derive_business_grp%found then
162 close csr_derive_business_grp;
163 else
164 close csr_derive_business_grp;
165 hr_utility.set_message(801, 'HR_7348_ASSIGNMENT_INVALID');
166 hr_utility.set_message_token('PROCEDURE', l_proc);
167 hr_utility.set_message_token('step','20');
168 hr_utility.raise_error;
169 end if;
170 /*
171 begin
172 --
173 --
174 -- Start of API User Hook for the before hook of create_city_tax_rule
175 --
176 pay_cty_bk1.create_city_tax_rule_b
177 (
178 p_assignment_id => p_assignment_id
179 ,p_state_code => p_state_code
180 ,p_county_code => p_county_code
181 ,p_city_code => p_city_code
182 ,p_business_group_id => l_business_group_id
183 ,p_additional_wa_rate => p_additional_wa_rate
184 ,p_filing_status_code => p_filing_status_code
185 ,p_jurisdiction_code => l_jurisdiction_code
186 ,p_lit_additional_tax => p_lit_additional_tax
187 ,p_lit_override_amount => p_lit_override_amount
188 ,p_lit_override_rate => p_lit_override_rate
189 ,p_withholding_allowances => p_withholding_allowances
190 ,p_lit_exempt => p_lit_exempt
191 ,p_sd_exempt => p_sd_exempt
192 ,p_ht_exempt => p_ht_exempt
193 ,p_wage_exempt => p_wage_exempt
194 ,p_school_district_code => p_school_district_code
195 );
196 exception
197 when hr_api.cannot_find_prog_unit then
198 hr_api.cannot_find_prog_unit_error
199 (
200 p_module_name => 'CREATE_CITY_TAX_RULE'
201 ,p_hook_type => 'BP'
202 );
203 --
204 -- End of API User Hook for the before hook of create_city_tax_rule
205 --
206 end;
207 */
208 --
209 -- Business Process Main Code
210 --
211 open csr_defaulting_date(p_assignment_id);
212 fetch csr_defaulting_date into l_defaulting_date;
213 if l_defaulting_date is null then
214 close csr_defaulting_date;
215 hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
216 hr_utility.set_message_token('table_name', 'pay_us_emp_fed_tax_rules_f');
220 close csr_defaulting_date;
217 hr_utility.set_message_token('step','25');
218 hr_utility.raise_error;
219 else
221
222 --Bug 13891625:Check if the same state, county and city combination tax rule already exists
223 open csr_city_tax_rule;
224 fetch csr_city_tax_rule into l_dummy;
225 if csr_city_tax_rule%FOUND then
226 close csr_city_tax_rule;
227 --
228 -- This city tax rule already exists
229 --
230 hr_utility.set_message(801, 'PAY_75276_CITY_TAX_ONE_RULE');
231 hr_utility.raise_error;
232 end if;
233 close csr_city_tax_rule;
234 -- End of changes for bug 13891625
235
236 pay_cty_ins.ins
237 (
238 p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
239 ,p_effective_start_date => l_effective_start_date
240 ,p_effective_end_date => l_effective_end_date
241 ,p_assignment_id => p_assignment_id
242 ,p_state_code => p_state_code
243 ,p_county_code => p_county_code
244 ,p_city_code => p_city_code
245 ,p_business_group_id => l_business_group_id
246 ,p_additional_wa_rate => p_additional_wa_rate
247 ,p_filing_status_code => p_filing_status_code
248 ,p_jurisdiction_code => l_jurisdiction_code
249 ,p_lit_additional_tax => p_lit_additional_tax
250 ,p_lit_override_amount => p_lit_override_amount
251 ,p_lit_override_rate => p_lit_override_rate
252 ,p_withholding_allowances => p_withholding_allowances
253 ,p_lit_exempt => p_lit_exempt
254 ,p_sd_exempt => p_sd_exempt
255 ,p_ht_exempt => p_ht_exempt
256 ,p_wage_exempt => p_wage_exempt
257 ,p_school_district_code => p_school_district_code
258 ,p_object_version_number => l_object_version_number
259 ,p_effective_date => l_defaulting_date
260 ,p_attribute_category => p_attribute_category
261 ,p_attribute1 => p_attribute1
262 ,p_attribute2 => p_attribute2
263 ,p_attribute3 => p_attribute3
264 ,p_attribute4 => p_attribute4
265 ,p_attribute5 => p_attribute5
266 ,p_attribute6 => p_attribute6
267 ,p_attribute7 => p_attribute7
268 ,p_attribute8 => p_attribute8
269 ,p_attribute9 => p_attribute9
270 ,p_attribute10 => p_attribute10
271 ,p_attribute11 => p_attribute11
272 ,p_attribute12 => p_attribute12
273 ,p_attribute13 => p_attribute13
274 ,p_attribute14 => p_attribute14
275 ,p_attribute15 => p_attribute15
276 ,p_attribute16 => p_attribute16
277 ,p_attribute17 => p_attribute17
278 ,p_attribute18 => p_attribute18
279 ,p_attribute19 => p_attribute19
280 ,p_attribute20 => p_attribute20
281 ,p_attribute21 => p_attribute21
282 ,p_attribute22 => p_attribute22
283 ,p_attribute23 => p_attribute23
284 ,p_attribute24 => p_attribute24
285 ,p_attribute25 => p_attribute25
286 ,p_attribute26 => p_attribute26
287 ,p_attribute27 => p_attribute27
288 ,p_attribute28 => p_attribute28
289 ,p_attribute29 => p_attribute29
290 ,p_attribute30 => p_attribute30
291 ,p_cty_information_category => p_cty_information_category
292 ,p_cty_information1 => p_cty_information1
293 ,p_cty_information2 => p_cty_information2
294 ,p_cty_information3 => p_cty_information3
295 ,p_cty_information4 => p_cty_information4
296 ,p_cty_information5 => p_cty_information5
297 ,p_cty_information6 => p_cty_information6
298 ,p_cty_information7 => p_cty_information7
299 ,p_cty_information8 => p_cty_information8
300 ,p_cty_information9 => p_cty_information9
301 ,p_cty_information10 => p_cty_information10
302 ,p_cty_information11 => p_cty_information11
303 ,p_cty_information12 => p_cty_information12
304 ,p_cty_information13 => p_cty_information13
305 ,p_cty_information14 => p_cty_information14
306 ,p_cty_information15 => p_cty_information15
307 ,p_cty_information16 => p_cty_information16
308 ,p_cty_information17 => p_cty_information17
309 ,p_cty_information18 => p_cty_information18
310 ,p_cty_information19 => p_cty_information19
311 ,p_cty_information20 => p_cty_information20
312 ,p_cty_information21 => p_cty_information21
313 ,p_cty_information22 => p_cty_information22
314 ,p_cty_information23 => p_cty_information23
315 ,p_cty_information24 => p_cty_information24
316 ,p_cty_information25 => p_cty_information25
317 ,p_cty_information26 => p_cty_information26
318 ,p_cty_information27 => p_cty_information27
319 ,p_cty_information28 => p_cty_information28
320 ,p_cty_information29 => p_cty_information29
321 ,p_cty_information30 => p_cty_information30
322 );
323 --
324 hr_utility.set_location(l_proc, 30);
325 --
326 pay_us_tax_internal.maintain_tax_percentage
327 (
328 p_assignment_id => p_assignment_id,
329 p_effective_date => p_effective_date,
330 p_state_code => p_state_code,
331 p_county_code => p_county_code,
332 p_city_code => p_city_code,
333 p_datetrack_mode => 'INSERT',
334 p_calculate_pct => FALSE,
335 p_effective_start_date => l_effective_start_date,
336 p_effective_end_date => l_effective_end_date
337 );
338 --
339 -- Call create_asg_geo_row to create rows in the pay_us_asg_reporting
340 -- table if this is a new city
341 --
342 pay_asg_geo_pkg.create_asg_geo_row(
343 p_assignment_id => p_assignment_id
344 ,p_jurisdiction => l_jurisdiction_code
345 );
346 End if;
347 --
348 /*
349 begin
350 --
351 -- Start of API User Hook for the after hook of create_city_tax_rule
352 --
353 pay_cty_bk1.create_city_tax_rule_a
354 (
355 p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
356 ,p_effective_start_date => l_effective_start_date
357 ,p_effective_end_date => l_effective_end_date
358 ,p_assignment_id => p_assignment_id
359 ,p_state_code => p_state_code
360 ,p_county_code => p_county_code
361 ,p_city_code => p_city_code
362 ,p_business_group_id => l_business_group_id
363 ,p_additional_wa_rate => p_additional_wa_rate
364 ,p_filing_status_code => p_filing_status_code
365 ,p_jurisdiction_code => l_jurisdiction_code
366 ,p_lit_additional_tax => p_lit_additional_tax
367 ,p_lit_override_amount => p_lit_override_amount
368 ,p_lit_override_rate => p_lit_override_rate
369 ,p_withholding_allowances => p_withholding_allowances
370 ,p_lit_exempt => p_lit_exempt
371 ,p_sd_exempt => p_sd_exempt
372 ,p_ht_exempt => p_ht_exempt
373 ,p_wage_exempt => p_wage_exempt
374 ,p_school_district_code => p_school_district_code
375 ,p_object_version_number => l_object_version_number
376 );
377 exception
378 when hr_api.cannot_find_prog_unit then
379 hr_api.cannot_find_prog_unit_error
380 (p_module_name => 'CREATE_CITY_TAX_RULE'
381 ,p_hook_type => 'AP'
382 );
383 --
384 -- End of API User Hook for the after hook of create_city_tax_rule
385 --
386 end;
387 */
388 --
389 hr_utility.set_location(l_proc, 35);
390 --
391 -- When in validation only mode raise the Validate_Enabled exception
392 --
393 if p_validate then
394 raise hr_api.validate_enabled;
395 end if;
396 --
397 -- Set all output arguments
398 --
399 p_emp_city_tax_rule_id := l_emp_city_tax_rule_id;
400 p_effective_start_date := l_effective_start_date;
401 p_effective_end_date := l_effective_end_date;
402 p_object_version_number := l_object_version_number;
403 --
404 hr_utility.set_location(' Leaving:'||l_proc, 40);
405 --
406 exception
407 --
408 when hr_api.validate_enabled then
409 --
410 -- As the Validate_Enabled exception has been raised
411 -- we must rollback to the savepoint
412 --
413 ROLLBACK TO create_city_tax_rule;
414 --
415 -- Only set output warning arguments
416 -- (Any key or derived arguments must be set to null
417 -- when validation only mode is being used.)
418 --
419 p_emp_city_tax_rule_id := null;
420 p_effective_start_date := null;
421 p_effective_end_date := null;
422 p_object_version_number := null;
423 hr_utility.set_location(' Leaving:'||l_proc, 60);
424 --
425 when others then
426 --
427 -- A validation or unexpected error has occurred
428 --
429 ROLLBACK TO create_city_tax_rule;
430 p_emp_city_tax_rule_id := null;
431 p_effective_start_date := null;
432 p_effective_end_date := null;
433 p_object_version_number := null;
434
435 raise;
436 --
437 end create_city_tax_rule;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |-------------------------< update_city_tax_rule >-------------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 procedure update_city_tax_rule
444 (p_validate in boolean default false
445 ,p_emp_city_tax_rule_id in number
446 ,p_effective_start_date out nocopy date
447 ,p_effective_end_date out nocopy date
448 ,p_additional_wa_rate in number default hr_api.g_number
449 ,p_filing_status_code in varchar2 default hr_api.g_varchar2
450 ,p_lit_additional_tax in number default hr_api.g_number
451 ,p_lit_override_amount in number default hr_api.g_number
452 ,p_lit_override_rate in number default hr_api.g_number
453 ,p_withholding_allowances in number default hr_api.g_number
454 ,p_lit_exempt in varchar2 default hr_api.g_varchar2
455 ,p_sd_exempt in varchar2 default hr_api.g_varchar2
456 ,p_ht_exempt in varchar2 default hr_api.g_varchar2
457 ,p_wage_exempt in varchar2 default hr_api.g_varchar2
458 ,p_school_district_code in varchar2 default hr_api.g_varchar2
462 ,p_attribute_category in varchar2 default hr_api.g_varchar2
459 ,p_object_version_number in out nocopy number
460 ,p_effective_date in date
461 ,p_datetrack_mode in varchar2
463 ,p_attribute1 in varchar2 default hr_api.g_varchar2
464 ,p_attribute2 in varchar2 default hr_api.g_varchar2
465 ,p_attribute3 in varchar2 default hr_api.g_varchar2
466 ,p_attribute4 in varchar2 default hr_api.g_varchar2
467 ,p_attribute5 in varchar2 default hr_api.g_varchar2
468 ,p_attribute6 in varchar2 default hr_api.g_varchar2
469 ,p_attribute7 in varchar2 default hr_api.g_varchar2
470 ,p_attribute8 in varchar2 default hr_api.g_varchar2
471 ,p_attribute9 in varchar2 default hr_api.g_varchar2
472 ,p_attribute10 in varchar2 default hr_api.g_varchar2
473 ,p_attribute11 in varchar2 default hr_api.g_varchar2
474 ,p_attribute12 in varchar2 default hr_api.g_varchar2
475 ,p_attribute13 in varchar2 default hr_api.g_varchar2
476 ,p_attribute14 in varchar2 default hr_api.g_varchar2
477 ,p_attribute15 in varchar2 default hr_api.g_varchar2
478 ,p_attribute16 in varchar2 default hr_api.g_varchar2
479 ,p_attribute17 in varchar2 default hr_api.g_varchar2
480 ,p_attribute18 in varchar2 default hr_api.g_varchar2
481 ,p_attribute19 in varchar2 default hr_api.g_varchar2
482 ,p_attribute20 in varchar2 default hr_api.g_varchar2
483 ,p_attribute21 in varchar2 default hr_api.g_varchar2
484 ,p_attribute22 in varchar2 default hr_api.g_varchar2
485 ,p_attribute23 in varchar2 default hr_api.g_varchar2
486 ,p_attribute24 in varchar2 default hr_api.g_varchar2
487 ,p_attribute25 in varchar2 default hr_api.g_varchar2
488 ,p_attribute26 in varchar2 default hr_api.g_varchar2
489 ,p_attribute27 in varchar2 default hr_api.g_varchar2
490 ,p_attribute28 in varchar2 default hr_api.g_varchar2
491 ,p_attribute29 in varchar2 default hr_api.g_varchar2
492 ,p_attribute30 in varchar2 default hr_api.g_varchar2
493 ,p_cty_information_category in varchar2 default hr_api.g_varchar2
494 ,p_cty_information1 in varchar2 default hr_api.g_varchar2
495 ,p_cty_information2 in varchar2 default hr_api.g_varchar2
496 ,p_cty_information3 in varchar2 default hr_api.g_varchar2
497 ,p_cty_information4 in varchar2 default hr_api.g_varchar2
498 ,p_cty_information5 in varchar2 default hr_api.g_varchar2
499 ,p_cty_information6 in varchar2 default hr_api.g_varchar2
500 ,p_cty_information7 in varchar2 default hr_api.g_varchar2
501 ,p_cty_information8 in varchar2 default hr_api.g_varchar2
502 ,p_cty_information9 in varchar2 default hr_api.g_varchar2
503 ,p_cty_information10 in varchar2 default hr_api.g_varchar2
504 ,p_cty_information11 in varchar2 default hr_api.g_varchar2
505 ,p_cty_information12 in varchar2 default hr_api.g_varchar2
506 ,p_cty_information13 in varchar2 default hr_api.g_varchar2
507 ,p_cty_information14 in varchar2 default hr_api.g_varchar2
508 ,p_cty_information15 in varchar2 default hr_api.g_varchar2
509 ,p_cty_information16 in varchar2 default hr_api.g_varchar2
510 ,p_cty_information17 in varchar2 default hr_api.g_varchar2
511 ,p_cty_information18 in varchar2 default hr_api.g_varchar2
512 ,p_cty_information19 in varchar2 default hr_api.g_varchar2
513 ,p_cty_information20 in varchar2 default hr_api.g_varchar2
514 ,p_cty_information21 in varchar2 default hr_api.g_varchar2
515 ,p_cty_information22 in varchar2 default hr_api.g_varchar2
516 ,p_cty_information23 in varchar2 default hr_api.g_varchar2
517 ,p_cty_information24 in varchar2 default hr_api.g_varchar2
518 ,p_cty_information25 in varchar2 default hr_api.g_varchar2
519 ,p_cty_information26 in varchar2 default hr_api.g_varchar2
520 ,p_cty_information27 in varchar2 default hr_api.g_varchar2
521 ,p_cty_information28 in varchar2 default hr_api.g_varchar2
522 ,p_cty_information29 in varchar2 default hr_api.g_varchar2
523 ,p_cty_information30 in varchar2 default hr_api.g_varchar2
524 ) is
525 --
526 -- Declare cursors and local variables
527 --
528 l_proc varchar2(72) := g_package||'update_city_tax_rule';
529 l_object_version_number pay_us_emp_city_tax_rules_f.object_version_number%TYPE;
530 l_effective_start_date pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
531 l_effective_end_date pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
532 l_assignment_id pay_us_emp_city_tax_rules_f.assignment_id%TYPE;
533 l_jurisdiction_code pay_us_emp_city_tax_rules_f.jurisdiction_code%TYPE;
534 --
535 CURSOR csr_assignment_id (p_rule_id NUMBER,
536 p_eff_date DATE) IS
537 SELECT ctr.assignment_id,
538 ctr.state_code||'-'||ctr.school_district_code
539 FROM pay_us_emp_city_tax_rules_f ctr
540 WHERE ctr.emp_city_tax_rule_id = p_rule_id
541 AND p_eff_date BETWEEN ctr.effective_start_date
545 --
542 AND ctr.effective_end_date;
543 --
544 begin
546 hr_utility.set_location('Entering:'|| l_proc, 10);
547 --
548 -- Issue a savepoint
549 --
550 savepoint update_city_tax_rule;
551 --
552 hr_utility.set_location(l_proc, 15);
553 --
554 -- Process Logic
555 --
556 -- Changed call to check hr_general.chk_maintain_tax_records to
557 -- determine if we need to maintain tax records.
558 --
559 IF hr_general.chk_maintain_tax_records = 'N' Then
560 return;
561 end if;
562 --
563 l_object_version_number := p_object_version_number;
564 --
565 /*
566 begin
567 --
568 -- Start of API User Hook for the before hook of update_city_tax_rule
569 --
570 pay_cty_bk2.update_city_tax_rule_b
571 (
572 p_emp_city_tax_rule_id => p_emp_city_tax_rule_id
573 ,p_assignment_id => p_assignment_id
574 ,p_state_code => p_state_code
575 ,p_county_code => p_county_code
576 ,p_city_code => p_city_code
577 ,p_business_group_id => l_business_group_id
578 ,p_additional_wa_rate => p_additional_wa_rate
579 ,p_filing_status_code => p_filing_status_code
580 ,p_jurisdiction_code => l_jurisdiction_code
581 ,p_lit_additional_tax => p_lit_additional_tax
582 ,p_lit_override_amount => p_lit_override_amount
583 ,p_lit_override_rate => p_lit_override_rate
584 ,p_withholding_allowances => p_withholding_allowances
585 ,p_lit_exempt => p_lit_exempt
586 ,p_sd_exempt => p_sd_exempt
587 ,p_ht_exempt => p_ht_exempt
588 ,p_wage_exempt => p_wage_exempt
589 ,p_school_district_code => p_school_district_code
590 ,p_object_version_number => p_object_version_number
591 );
592 exception
593 when hr_api.cannot_find_prog_unit then
594 hr_api.cannot_find_prog_unit_error
595 (p_module_name => 'UPDATE_CITY_TAX_RULE'
596 ,p_hook_type => 'BP'
597 );
598 --
599 -- End of API User Hook for the before hook of update_city_tax_rule
600 --
601 end;
602 */
603 --
604 pay_cty_upd.upd
605 (
606 p_emp_city_tax_rule_id => p_emp_city_tax_rule_id
607 ,p_effective_start_date => l_effective_start_date
608 ,p_effective_end_date => l_effective_end_date
609 ,p_additional_wa_rate => p_additional_wa_rate
610 ,p_filing_status_code => p_filing_status_code
611 ,p_lit_additional_tax => p_lit_additional_tax
612 ,p_lit_override_amount => p_lit_override_amount
613 ,p_lit_override_rate => p_lit_override_rate
614 ,p_withholding_allowances => p_withholding_allowances
615 ,p_lit_exempt => p_lit_exempt
616 ,p_sd_exempt => p_sd_exempt
617 ,p_ht_exempt => p_ht_exempt
618 ,p_wage_exempt => p_wage_exempt
619 ,p_school_district_code => p_school_district_code
620 ,p_object_version_number => l_object_version_number
621 ,p_effective_date => p_effective_date
622 ,p_datetrack_mode => p_datetrack_mode
623 ,p_attribute_category => p_attribute_category
624 ,p_attribute1 => p_attribute1
625 ,p_attribute2 => p_attribute2
626 ,p_attribute3 => p_attribute3
627 ,p_attribute4 => p_attribute4
628 ,p_attribute5 => p_attribute5
629 ,p_attribute6 => p_attribute6
630 ,p_attribute7 => p_attribute7
631 ,p_attribute8 => p_attribute8
632 ,p_attribute9 => p_attribute9
633 ,p_attribute10 => p_attribute10
634 ,p_attribute11 => p_attribute11
635 ,p_attribute12 => p_attribute12
636 ,p_attribute13 => p_attribute13
637 ,p_attribute14 => p_attribute14
638 ,p_attribute15 => p_attribute15
639 ,p_attribute16 => p_attribute16
640 ,p_attribute17 => p_attribute17
641 ,p_attribute18 => p_attribute18
642 ,p_attribute19 => p_attribute19
643 ,p_attribute20 => p_attribute20
644 ,p_attribute21 => p_attribute21
645 ,p_attribute22 => p_attribute22
646 ,p_attribute23 => p_attribute23
647 ,p_attribute24 => p_attribute24
648 ,p_attribute25 => p_attribute25
649 ,p_attribute26 => p_attribute26
650 ,p_attribute27 => p_attribute27
651 ,p_attribute28 => p_attribute28
652 ,p_attribute29 => p_attribute29
653 ,p_attribute30 => p_attribute30
654 ,p_cty_information_category => p_cty_information_category
655 ,p_cty_information1 => p_cty_information1
656 ,p_cty_information2 => p_cty_information2
657 ,p_cty_information3 => p_cty_information3
658 ,p_cty_information4 => p_cty_information4
659 ,p_cty_information5 => p_cty_information5
660 ,p_cty_information6 => p_cty_information6
664 ,p_cty_information10 => p_cty_information10
661 ,p_cty_information7 => p_cty_information7
662 ,p_cty_information8 => p_cty_information8
663 ,p_cty_information9 => p_cty_information9
665 ,p_cty_information11 => p_cty_information11
666 ,p_cty_information12 => p_cty_information12
667 ,p_cty_information13 => p_cty_information13
668 ,p_cty_information14 => p_cty_information14
669 ,p_cty_information15 => p_cty_information15
670 ,p_cty_information16 => p_cty_information16
671 ,p_cty_information17 => p_cty_information17
672 ,p_cty_information18 => p_cty_information18
673 ,p_cty_information19 => p_cty_information19
674 ,p_cty_information20 => p_cty_information20
675 ,p_cty_information21 => p_cty_information21
676 ,p_cty_information22 => p_cty_information22
677 ,p_cty_information23 => p_cty_information23
678 ,p_cty_information24 => p_cty_information24
679 ,p_cty_information25 => p_cty_information25
680 ,p_cty_information26 => p_cty_information26
681 ,p_cty_information27 => p_cty_information27
682 ,p_cty_information28 => p_cty_information28
683 ,p_cty_information29 => p_cty_information29
684 ,p_cty_information30 => p_cty_information30
685 );
686 --
687 -- Call create_asg_geo_row to create rows in the pay_us_asg_reporting
688 -- table if this city school district has changed.
689 --
690 if p_school_district_code IS NOT NULL AND
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_city_tax_rule_id,
694 p_effective_date);
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 /*
706 begin
707 --
708 -- Start of API User Hook for the after hook of update_city_tax_rule
709 --
710 pay_cty_bk2.update_city_tax_rule_a
711 (
712 p_emp_city_tax_rule_id => p_emp_city_tax_rule_id
713 ,p_effective_start_date => l_effective_start_date
714 ,p_effective_end_date => l_effective_end_date
715 ,p_assignment_id => p_assignment_id
716 ,p_state_code => p_state_code
717 ,p_county_code => p_county_code
718 ,p_city_code => p_city_code
719 ,p_business_group_id => l_business_group_id
720 ,p_additional_wa_rate => p_additional_wa_rate
721 ,p_filing_status_code => p_filing_status_code
722 ,p_jurisdiction_code => p_jurisdiction_code
723 ,p_lit_additional_tax => p_lit_additional_tax
724 ,p_lit_override_amount => p_lit_override_amount
725 ,p_lit_override_rate => p_lit_override_rate
726 ,p_withholding_allowances => p_withholding_allowances
727 ,p_lit_exempt => p_lit_exempt
728 ,p_sd_exempt => p_sd_exempt
729 ,p_ht_exempt => p_ht_exempt
730 ,p_wage_exempt => p_wage_exempt
731 ,p_school_district_code => p_school_district_code
732 ,p_object_version_number => p_object_version_number
733 );
734 exception
735 when hr_api.cannot_find_prog_unit then
736 hr_api.cannot_find_prog_unit_error
737 (p_module_name => 'UPDATE_CITY_TAX_RULE'
738 ,p_hook_type => 'AP'
739 );
740 --
741 -- End of API User Hook for the after hook of update_city_tax_rule
742 --
743 end;
744 */
745 --
746 hr_utility.set_location(l_proc, 25);
747 --
748 -- When in validation only mode raise the Validate_Enabled exception
749 --
750 if p_validate then
751 raise hr_api.validate_enabled;
752 end if;
753 --
754 -- Set all output arguments
755 --
756 p_object_version_number := l_object_version_number;
757 p_effective_start_date := l_effective_start_date;
758 p_effective_end_date := l_effective_end_date;
759 --
760 hr_utility.set_location(' Leaving:'||l_proc, 30);
761 --
762 exception
763 --
764 when hr_api.validate_enabled then
765 --
766 -- As the Validate_Enabled exception has been raised
767 -- we must rollback to the savepoint
768 --
769 ROLLBACK TO update_city_tax_rule;
770 --
771 -- Only set output warning arguments
772 -- (Any key or derived arguments must be set to null
773 -- when validation only mode is being used.)
774 --
775 p_object_version_number := l_object_version_number;
776 p_effective_start_date := null;
777 p_effective_end_date := null;
778 hr_utility.set_location(' Leaving:'||l_proc, 50);
779 --
780 when others then
781 --
782 -- A validation or unexpected error has occurred
783 --
784 ROLLBACK TO update_city_tax_rule;
785 p_effective_start_date := null;
786 p_effective_end_date := null;
787 p_object_version_number := l_object_version_number;
788
789 raise;
790 --
791 end update_city_tax_rule;
792 --
793 --
794 -- ----------------------------------------------------------------------------
795 -- |-------------------------------< lck >------------------------------------|
796 -- ----------------------------------------------------------------------------
797 --
798 procedure lck
799 (
800 p_emp_city_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_city_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_cty_shd.lck
820 (
821 p_emp_city_tax_rule_id => p_emp_city_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_city_tax_rule_api;