[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAX_BAL_SUMMARY_PKG
Source
1 package body pay_us_tax_bal_summary_pkg as
2 /* $Header: pyustxbs.pkb 115.2 2004/01/29 09:20:53 sdahiya noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_tax_bal_summary_pkg
21
22 Description : This package is used by Tax Balance Summary form AND
23 contains procedures to fetch federal, state AND local
24 balances.
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ------ ------- ------------------------------------
30 05-DEC-2003 sdahiya 115.0 3129694 Created.
31 22-DEC-2003 sdahiya 115.1 3129694 Properly indented the code. Local
32 variable names start with 'l_'.
33 Added few more comments.
34 29-JAN-2004 sdahiya 115.2 3362423 Modified following cursors for
35 performance: -
36 c_state_ee c_local2
37 c_local3 c_local6
38 c_local8 c_local12
39 Modified references of these cursors
40 in GET_STATE and GET_LOCAL procedures.
41
42
43 *****************************************************************************/
44
45
46
47 l_package VARCHAR2(30) := 'pay_us_tax_bal_summary_pkg.';
48
49 /*****************************************************************************
50 Name : GET_FED
51 Purpose : This procedure obtains all federal balance categories,
52 tax types, wage types AND liability types depending on the
53 EE/ER parameter passed. Along with other parameters, all these
54 balance categories AND tax/wage/liability types are passed to
55 US payroll package us_taxbal_view_pkg to get actual balance
56 values.
57 *****************************************************************************/
58
59
60 PROCEDURE GET_FED (p_ee_er IN VARCHAR2
61 , p_assg_id IN NUMBER
62 , p_asact_id IN NUMBER
63 , p_tax_unit_id IN NUMBER
64 , p_fed_taxes_tab OUT NOCOPY tab_taxes) IS
65
66
67 /* Cursor to get Federal Taxes/Liabilities/Wages */
68 CURSOR c_fed_cur IS
69 SELECT
70 decode(tax_type_code, 'MEDICARE','Medicare',tax_type_code)||' '
71 ||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
72 'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
73 , decode (tax_type_code
74 ,'FIT', 1
75 ,'SS' , 2
76 ,'MEDICARE' , 3
77 ,'FUTA', 4
78 ,'EIC', 5
79 , 6) ordercol1
80 , decode (balance_category_code
81 ,'GROSS', 1
82 ,'TAXABLE' , 2
83 ,'SUBJECT' , 3
84 ,'WITHHELD', 4
85 ,'ADVANCED', 5
86 , 6) ordercol2
87 , tax_type_code
88 ,balance_category_code
89 FROM pay_us_fed_tax_types_v
90 WHERE ee_or_er_code = p_ee_er
91 AND element_name IN ('EIC', 'Medicare_EE', 'FIT', 'SS_EE')
92 AND balance_category_code IN ('WITHHELD', 'ADVANCED')
93 AND tax_type_code IN ('EIC', 'MEDICARE', 'FIT', 'SS')
94 UNION ALL
95 SELECT
96 decode(tax_type_code, 'MEDICARE','Medicare' ,tax_type_code)||' '
97 ||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
98 'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
99 , decode (tax_type_code
100 ,'FIT', 1
101 ,'SS' , 2
102 ,'MEDICARE' , 3
103 ,'FUTA', 4
104 ,'EIC', 5
105 , 6) ordercol1
106 , decode (balance_category_code
107 ,'GROSS', 1
108 ,'TAXABLE' , 2
109 ,'SUBJECT' , 3
110 ,'WITHHELD', 4
111 ,'ADVANCED', 5
112 , 6) ordercol2
113 , tax_type_code
114 ,balance_category_code
115 FROM pay_us_fed_ee_wage_types_v
116 WHERE ee_or_er_code = p_ee_er
117 AND ((tax_type_code in ('MEDICARE', 'SS')
118 AND balance_category_code = 'TAXABLE')
119 OR (tax_type_code IN ('FIT','EIC'))
120 )
121 UNION ALL
122 SELECT
123 decode(tax_type_code, 'MEDICARE','Medicare',tax_type_code)||' '
124 ||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
125 'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
126 , decode (tax_type_code
127 ,'FIT', 1
128 ,'SS' , 2
129 ,'MEDICARE' , 3
130 ,'FUTA', 4
131 ,'EIC', 5
132 , 6) ordercol1
133 , decode (balance_category_code
134 ,'GROSS', 1
135 ,'TAXABLE' , 2
136 ,'SUBJECT' , 3
137 ,'WITHHELD', 4
138 ,'ADVANCED', 5
139 , 6) ordercol2
140 , tax_type_code
141 ,balance_category_code
142 FROM pay_us_fed_liability_types_v
143 WHERE ee_or_er_code = p_ee_er
144 AND tax_type_code in ('MEDICARE', 'FUTA', 'SS')
145 AND element_name IN ('Medicare_ER', 'FUTA', 'SS_ER')
146 UNION ALL
147 SELECT
148 decode(tax_type_code, 'MEDICARE','Medicare' ,tax_type_code)||' '
149 ||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
150 'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
151 , decode (tax_type_code
152 ,'FIT', 1
153 ,'SS' , 2
154 ,'MEDICARE' , 3
155 ,'FUTA', 4
156 ,'EIC', 5
157 , 6) ordercol1
158 , decode (balance_category_code
159 ,'GROSS', 1
160 ,'TAXABLE' , 2
161 ,'SUBJECT' , 3
162 ,'WITHHELD', 4
163 ,'ADVANCED', 5
164 , 6) ordercol2
165 , tax_type_code
166 ,balance_category_code
167 FROM pay_us_fed_er_wage_types_v
168 WHERE ee_or_er_code = p_ee_er
169 AND tax_type_code in ('MEDICARE', 'SS','FUTA')
170 AND balance_category_code = 'TAXABLE'
171 AND element_name in ('Medicare_ER','FUTA','SS_ER')
172 order by 2,3 ;
173
174 l_bal_val number;
175 l_cnt number := 1;
176 l_procedure varchar2(30) := 'get_fed';
177
178 BEGIN
179 hr_utility.set_location(l_package||l_procedure, 10);
180 --hr_utility.trace_on(null,'tax_bal_summary');
181 for fed_taxes_rec in c_fed_cur loop
182 p_fed_taxes_tab(l_cnt).prompt := fed_taxes_rec.prompt;
183 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
184 p_tax_balance_category => fed_taxes_rec.balance_category_code,
185 p_tax_type => fed_taxes_rec.tax_type_code,
186 p_ee_or_er => p_ee_er,
187 p_time_type => 'YTD',
188 p_gre_id_context => p_tax_unit_id,
189 p_jd_context => NULL,
190 p_assignment_action_id => p_asact_id ,
191 p_assignment_id => p_assg_id,
192 p_virtual_date => NULL,
193 p_payroll_action_id => NULL);
194 p_fed_taxes_tab(l_cnt).ytd_val := l_bal_val;
195 hr_utility.set_location(l_package||l_procedure, 20);
196 hr_utility.trace('YTD value = '||l_bal_val);
197
198 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
199 p_tax_balance_category => fed_taxes_rec.balance_category_code,
200 p_tax_type => fed_taxes_rec.tax_type_code,
201 p_ee_or_er => p_ee_er,
202 p_time_type => 'PTD',
203 p_gre_id_context => p_tax_unit_id,
204 p_jd_context => NULL,
205 p_assignment_action_id => p_asact_id ,
206 p_assignment_id => p_assg_id,
207 p_virtual_date => NULL,
208 p_payroll_action_id => NULL);
209 p_fed_taxes_tab(l_cnt).ptd_val := l_bal_val;
210 hr_utility.set_location(l_package||l_procedure, 30);
211 hr_utility.trace('PTD value = '||l_bal_val);
212
213 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
214 p_tax_balance_category => fed_taxes_rec.balance_category_code,
215 p_tax_type => fed_taxes_rec.tax_type_code,
216 p_ee_or_er => p_ee_er,
217 p_time_type => 'MONTH',
218 p_gre_id_context => p_tax_unit_id,
219 p_jd_context => NULL,
220 p_assignment_action_id => p_asact_id ,
221 p_assignment_id => p_assg_id,
222 p_virtual_date => NULL,
223 p_payroll_action_id => NULL);
224 p_fed_taxes_tab(l_cnt).mtd_val := l_bal_val;
225 hr_utility.set_location(l_package||l_procedure, 40);
226 hr_utility.trace('MTD value = '||l_bal_val);
227
228 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
229 p_tax_balance_category => fed_taxes_rec.balance_category_code,
230 p_tax_type => fed_taxes_rec.tax_type_code,
231 p_ee_or_er => p_ee_er,
232 p_time_type => 'QTD',
233 p_gre_id_context => p_tax_unit_id,
234 p_jd_context => NULL,
235 p_assignment_action_id => p_asact_id ,
236 p_assignment_id => p_assg_id,
237 p_virtual_date => NULL,
238 p_payroll_action_id => NULL);
239 p_fed_taxes_tab(l_cnt).qtd_val := l_bal_val;
240 hr_utility.set_location(l_package||l_procedure, 50);
241 hr_utility.trace('QTD value = '||l_bal_val);
242
243 l_cnt := l_cnt + 1;
244 end loop;
245 hr_utility.set_location(l_package||l_procedure, 60);
246 hr_utility.trace('Normal completion of '||l_package||l_procedure);
247
248 EXCEPTION
249 WHEN others THEN
250 hr_utility.set_location(l_package||l_procedure,70);
251 hr_utility.trace('Abormal completion of '||l_package||l_procedure);
252 raise_application_error(-20101, 'Error in ' || l_package||l_procedure || ' - ' || sqlerrm);
253 END GET_FED;
254
255
256
257 /*****************************************************************************
258 Name : GET_STATE
259 Purpose : This procedure obtains all state level EE/ER balance categories,
260 tax types, wage types AND liability types for a given state.
261 Along with other parameters, all these balance categories AND
262 tax/wage/liability types are passed to US payroll package
263 us_taxbal_view_pkg to get actual balance values.
264 *****************************************************************************/
265
266 PROCEDURE GET_STATE (p_ee_er IN VARCHAR2
267 , p_assg_id IN NUMBER
268 , p_asact_id IN NUMBER
269 , p_tax_unit_id IN NUMBER
270 , p_state_code IN VARCHAR2
271 , p_state_taxes_tab OUT NOCOPY tab_taxes) IS
272
273 /* Cursor created as per bug 3362423 */
274 CURSOR c_state_dt IS
275 SELECT DISTINCT
276 NVL(ppa.date_earned,ppa.effective_date)effective_date,
277 jurisdiction_code
278 FROM pay_assignment_actions paa
279 ,pay_us_emp_state_tax_rules_f pue
280 ,pay_payroll_actions ppa
281 WHERE paa.assignment_action_id = p_asact_id
282 AND pue.assignment_id = paa.assignment_id
283 AND ppa.payroll_action_id = paa.payroll_action_id
284 AND ppa.effective_date between pue.effective_start_date
285 AND pue.effective_end_date
286 AND pue.state_code = p_state_code
287 AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I');
288
289
290 /* Cursor to get State Taxes and EE Wages. Modified as per bug 3362423. */
291 CURSOR c_state_ee (p_eff_dt IN DATE,p_jurisdiction_code VARCHAR2) IS
292 SELECT DISTINCT
293 tax_type_code||' '
294 ||decode(tax_type_code,'SIT',decode(balance_category_code, 'SUBJECT',
295 'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
296 , decode (tax_type_code
297 ,'SIT', 1
298 ,'SUI', 2
299 ,'SDI' , 3
300 ,'WCE' , 4
301 ,'WC2', 5
302 , 6) ordercol1
303 , decode (balance_category_code
304 ,'GROSS', 1
305 ,'TAXABLE' , 2
306 ,'SUBJECT' , 3
307 ,'WITHHELD', 4
308 ,'ADVANCED', 5
309 , 6) ordercol2
310 , tax_type_code
311 , balance_category_code
312 , jurisdiction_code
313 FROM pay_assignment_actions paa
314 ,pay_payroll_actions ppa
315 ,pay_us_state_tax_types_v pstt
316 ,pay_us_emp_state_tax_rules_f pue
317 WHERE paa.assignment_action_id = p_asact_id
318 AND pue.assignment_id = paa.assignment_id
319 AND ppa.payroll_action_id = paa.payroll_action_id
320 AND ppa.effective_date between pue.effective_start_date
321 AND pue.effective_end_date
322 AND pue.state_code = p_state_code
323 AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I')
324 AND NVL(ppa.date_earned,ppa.effective_date) between
325 pstt.effective_start_date AND pstt.effective_end_date
326
327 UNION ALL
328 SELECT DISTINCT
329 tax_type_code||' '
330 ||decode(tax_type_code,'SIT',decode(balance_category_code, 'SUBJECT',
331 'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
332 , decode (tax_type_code
333 ,'SIT', 1
334 ,'SUI', 2
335 ,'SDI' , 3
336 ,'WCE' , 4
337 ,'WC2', 5
338 , 6) ordercol1
339 , decode (balance_category_code
340 ,'GROSS', 1
341 ,'TAXABLE' , 2
345 , 6) ordercol2
342 ,'SUBJECT' , 3
343 ,'WITHHELD', 4
344 ,'ADVANCED', 5
346 , tax_type_code
347 , balance_category_code
348 , p_jurisdiction_code
349 FROM pay_us_state_ee_wage_types_v pstt
350 WHERE p_eff_dt between
351 pstt.effective_start_date AND pstt.effective_end_date
352 AND ((tax_type_code = 'SIT'
353 AND balance_category_code = 'SUBJECT')
354 OR (tax_type_code <> 'SIT'
355 AND balance_category_code = 'TAXABLE')
356 )
357 AND (element_name like 'SIT%'
358 OR element_name like 'SDI%'
359 OR element_name like 'SUI%' )
360 AND pstt.element_type_id >= 0
361 ORDER BY 2,3;
362
363 /* Cursor to get State Liabilities/ER Wages */
364 CURSOR c_state_er (p_state_code IN VARCHAR2) IS
365 SELECT DISTINCT
366 tax_type_code||' '
367 ||decode(tax_type_code,'SIT',decode(balance_category_code,
368 'SUBJECT','Taxable',
369 initcap(balance_category_code)),
370 initcap(balance_category_code)) prompt
371 , decode (tax_type_code
372 ,'SIT', 1
373 ,'SUI', 2
374 ,'SDI' , 3
375 ,'WCE' , 4
376 ,'WC2', 5
377 , 6) ordercol1
378 , decode (balance_category_code
379 ,'GROSS', 1
380 ,'TAXABLE' , 2
381 ,'SUBJECT' , 3
382 ,'WITHHELD', 4
383 ,'ADVANCED', 5
384 , 6) ordercol2
385 , tax_type_code
386 , balance_category_code
387 , jurisdiction_code
388 FROM pay_assignment_actions paa
389 ,pay_payroll_actions ppa
390 ,pay_us_state_liability_types_v pstt
391 ,pay_us_emp_state_tax_rules_f pue
392 WHERE paa.assignment_action_id = p_asact_id
393 AND pue.assignment_id = paa.assignment_id
394 AND ppa.payroll_action_id = paa.payroll_action_id
395 AND ppa.effective_date between pue.effective_start_date
396 AND pue.effective_end_date
397 AND pue.state_code = p_state_code
398 AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I')
399 AND NVL(ppa.date_earned,ppa.effective_date) between
400 pstt.effective_start_date AND pstt.effective_end_date
401 AND pstt.ELEMENT_NAME IN ('SDI_ER', 'SUI_ER')
402 AND pstt.BALANCE_CATEGORY_CODE = 'LIABILITY'
403 AND pstt.TAX_TYPE_CODE in ('SDI','SUI')
404 UNION ALL
405 SELECT DISTINCT
406 tax_type_code||' '||
407 decode(tax_type_code,'SIT',decode(balance_category_code,
408 'SUBJECT','Taxable',
409 initcap(balance_category_code)),
410 initcap(balance_category_code)) prompt
411 , decode (tax_type_code
412 ,'SIT', 1
413 ,'SUI', 2
414 ,'SDI' , 3
415 ,'WCE' , 4
416 ,'WC2', 5
417 , 6) ordercol1
418 , decode (balance_category_code
419 ,'GROSS', 1
420 ,'TAXABLE' , 2
421 ,'WITHHELD' , 3
422 ,'SUBJECT', 4
423 ,'ADVANCED', 5
424 , 6) ordercol2
425 , tax_type_code
426 , balance_category_code
427 , jurisdiction_code
428 FROM pay_assignment_actions paa
429 ,pay_payroll_actions ppa
430 ,pay_us_state_er_wage_types_v pstt
431 ,pay_us_emp_state_tax_rules_f pue
432 WHERE pue.assignment_id = paa.assignment_id
433 AND ppa.payroll_action_id = paa.payroll_action_id
434 AND ppa.effective_date between pue.effective_start_date
435 AND pue.effective_end_date
436 AND pue.state_code = p_state_code
437 AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I')
438 AND NVL(ppa.date_earned,ppa.effective_date) between
439 pstt.effective_start_date AND pstt.effective_end_date
440 AND paa.assignment_action_id = p_asact_id
441 AND ELEMENT_NAME IN ('SDI_ER', 'SUI_ER')
442 AND tax_type_code in ('SDI','SUI')
443 AND balance_category_code = 'TAXABLE'
444 ORDER BY 2,3;
445
446 l_bal_val number;
447 l_cnt number := 1;
448 l_procedure varchar2(30) := 'get_state';
449
450 BEGIN
451 hr_utility.set_location(l_package||l_procedure, 10);
452 --hr_utility.trace_on(null,'tax_bal_summary');
453 if p_ee_er = 'EE' then
454 for state_dt in c_state_dt loop
455 for state_ee in c_state_ee (state_dt.effective_date, state_dt.jurisdiction_code) loop
456 p_state_taxes_tab(l_cnt).prompt := state_ee.prompt;
457 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
458 p_tax_balance_category => state_ee.balance_category_code,
459 p_tax_type => state_ee.tax_type_code,
460 p_ee_or_er => p_ee_er,
461 p_time_type => 'YTD',
462 p_gre_id_context => p_tax_unit_id,
463 p_jd_context => state_ee.jurisdiction_code,
464 p_assignment_action_id => p_asact_id ,
465 p_assignment_id => p_assg_id,
466 p_virtual_date => NULL,
467 p_payroll_action_id => NULL);
468 p_state_taxes_tab(l_cnt).ytd_val := l_bal_val;
469 hr_utility.set_location(l_package||l_procedure, 20);
470 hr_utility.trace('YTD value = '||l_bal_val);
471
472 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
473 p_tax_balance_category => state_ee.balance_category_code,
474 p_tax_type => state_ee.tax_type_code,
475 p_ee_or_er => p_ee_er,
476 p_time_type => 'PTD',
477 p_gre_id_context => p_tax_unit_id,
478 p_jd_context => state_ee.jurisdiction_code,
479 p_assignment_action_id => p_asact_id ,
480 p_assignment_id => p_assg_id,
481 p_virtual_date => NULL,
482 p_payroll_action_id => NULL);
483 p_state_taxes_tab(l_cnt).ptd_val := l_bal_val;
484 hr_utility.set_location(l_package||l_procedure, 30);
488 p_tax_balance_category => state_ee.balance_category_code,
485 hr_utility.trace('PTD value = '||l_bal_val);
486
487 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
489 p_tax_type => state_ee.tax_type_code,
490 p_ee_or_er => p_ee_er,
491 p_time_type => 'MONTH',
492 p_gre_id_context => p_tax_unit_id,
493 p_jd_context => state_ee.jurisdiction_code,
494 p_assignment_action_id => p_asact_id ,
495 p_assignment_id => p_assg_id,
496 p_virtual_date => NULL,
497 p_payroll_action_id => NULL);
498 p_state_taxes_tab(l_cnt).mtd_val := l_bal_val;
499 hr_utility.set_location(l_package||l_procedure, 40);
500 hr_utility.trace('MTD value = '||l_bal_val);
501
502 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
503 p_tax_balance_category => state_ee.balance_category_code,
504 p_tax_type => state_ee.tax_type_code,
505 p_ee_or_er => p_ee_er,
506 p_time_type => 'QTD',
507 p_gre_id_context => p_tax_unit_id,
508 p_jd_context => state_ee.jurisdiction_code,
509 p_assignment_action_id => p_asact_id ,
510 p_assignment_id => p_assg_id,
511 p_virtual_date => NULL,
512 p_payroll_action_id => NULL);
513 p_state_taxes_tab(l_cnt).qtd_val := l_bal_val;
514 hr_utility.set_location(l_package||l_procedure, 50);
515 hr_utility.trace('QTD value = '||l_bal_val);
516
517 l_cnt := l_cnt + 1;
518 end loop;
519 end loop;
520 else
521 for state_er in c_state_er (p_state_code) loop
522 p_state_taxes_tab(l_cnt).prompt := state_er.prompt;
523 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
524 p_tax_balance_category => state_er.balance_category_code,
525 p_tax_type => state_er.tax_type_code,
526 p_ee_or_er => p_ee_er,
527 p_time_type => 'YTD',
528 p_gre_id_context => p_tax_unit_id,
529 p_jd_context => state_er.jurisdiction_code,
530 p_assignment_action_id => p_asact_id ,
531 p_assignment_id => p_assg_id,
532 p_virtual_date => NULL,
533 p_payroll_action_id => NULL);
534 p_state_taxes_tab(l_cnt).ytd_val := l_bal_val;
535 hr_utility.set_location(l_package||l_procedure, 60);
536 hr_utility.trace('YTD value = '||l_bal_val);
537
538 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
539 p_tax_balance_category => state_er.balance_category_code,
540 p_tax_type => state_er.tax_type_code,
541 p_ee_or_er => p_ee_er,
542 p_time_type => 'PTD',
543 p_gre_id_context => p_tax_unit_id,
544 p_jd_context => state_er.jurisdiction_code,
545 p_assignment_action_id => p_asact_id ,
546 p_assignment_id => p_assg_id,
547 p_virtual_date => NULL,
548 p_payroll_action_id => NULL);
549 p_state_taxes_tab(l_cnt).ptd_val := l_bal_val;
550 hr_utility.set_location(l_package||l_procedure, 70);
551 hr_utility.trace('PTD value = '||l_bal_val);
552
553 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
554 p_tax_balance_category => state_er.balance_category_code,
555 p_tax_type => state_er.tax_type_code,
556 p_ee_or_er => p_ee_er,
557 p_time_type => 'MONTH',
558 p_gre_id_context => p_tax_unit_id,
559 p_jd_context => state_er.jurisdiction_code,
560 p_assignment_action_id => p_asact_id ,
561 p_assignment_id => p_assg_id,
562 p_virtual_date => NULL,
563 p_payroll_action_id => NULL);
564 p_state_taxes_tab(l_cnt).mtd_val := l_bal_val;
565 hr_utility.set_location(l_package||l_procedure, 80);
566 hr_utility.trace('MTD value = '||l_bal_val);
567
568 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
569 p_tax_balance_category => state_er.balance_category_code,
570 p_tax_type => state_er.tax_type_code,
571 p_ee_or_er => p_ee_er,
572 p_time_type => 'QTD',
573 p_gre_id_context => p_tax_unit_id,
574 p_jd_context => state_er.jurisdiction_code,
575 p_assignment_action_id => p_asact_id ,
576 p_assignment_id => p_assg_id,
577 p_virtual_date => NULL,
578 p_payroll_action_id => NULL);
579 p_state_taxes_tab(l_cnt).qtd_val := l_bal_val;
580 hr_utility.set_location(l_package||l_procedure, 90);
581 hr_utility.trace('QTD value = '||l_bal_val);
582
583 l_cnt := l_cnt + 1;
584 end loop;
585 end if;
586 hr_utility.set_location(l_package||l_procedure, 100);
587 hr_utility.trace('Normal completion of '||l_package||l_procedure);
588 EXCEPTION
589 WHEN others THEN
590 hr_utility.set_location(l_package||l_procedure,110);
591 hr_utility.trace('Abnormal completion of '||l_package||l_procedure);
592 raise_application_error(-20101, 'Error in ' || l_package||l_procedure || ' - ' || sqlerrm);
593 END GET_STATE;
594
595
596
597
598
599 /*****************************************************************************
600 Name : GET_LOCAL
601 Purpose : This procedure obtains all local balance categories,
602 tax types AND EE wage types for a given jurisdiction code.
603 Along with other parameters, all these balance categories
604 AND tax/wage types are passed to US payroll package
605 us_taxbal_view_pkg to get actual balance values.
606 *****************************************************************************/
607
608 PROCEDURE GET_LOCAL (p_ee_er IN VARCHAR2
609 , p_assg_id IN NUMBER
610 , p_asact_id IN NUMBER
611 , p_tax_unit_id NUMBER
612 , p_jurisdiction IN VARCHAR2
613 , p_school IN VARCHAR2
614 , p_local_taxes_tab OUT NOCOPY tab_taxes) IS
615
616
617 ------------Local Taxes ! SCHOOL------------
618 /* Cursor for City taxes with tax_type_code <> School */
619 CURSOR c_local1 IS
620 SELECT DISTINCT
621 decode(tax_type_code, 'COUNTY', 'County',
625 ,'COUNTY', 1
622 'CITY', 'City', tax_type_code)||' '
623 ||initcap(balance_category_code) prompt
624 , decode (tax_type_code
626 ,'CITY' , 2
627 ,'HT' , 3
628 , 6) ordercol1
629 , decode (balance_category_code
630 ,'TAXABLE' , 2
631 ,'SUBJECT' , 3
632 ,'WITHHELD' , 4
633 , 6) ordercol2
634 , tax_type_code
635 , balance_category_code
636 , city.jurisdiction_code
637 FROM
638 pay_assignment_actions paa ,
639 pay_payroll_actions ppa ,
640 pay_us_local_tax_types_v petv ,
641 pay_us_emp_city_tax_rules_f city ,
642 pay_us_city_names names ,
643 pay_us_city_tax_info_f citf
644 WHERE paa.payroll_action_id = ppa.payroll_action_id
645 AND ppa.effective_date between city.effective_start_date
646 AND city.effective_end_date
647 AND city.assignment_id = paa.assignment_id
648 AND names.city_code = substr(city.jurisdiction_code,8,4)
649 AND names.county_code = substr(city.jurisdiction_code,4,3)
650 AND names.state_code = substr(city.jurisdiction_code,1,2)
651 AND names.primary_flag = 'Y'
652 AND citf.jurisdiction_code = city.jurisdiction_code
653 AND decode(tax_type_code, 'CITY', citf.city_tax, 'HT' , citf.head_tax, 'N') = 'Y'
654 AND ppa.effective_date between citf.effective_start_date AND citf.effective_end_date
655 AND petv.tax_type_code IN ('CITY', 'HT')
656 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
657 AND nvl(ppa.date_earned, ppa.effective_date) between petv.effective_start_date AND petv.effective_end_date
658 AND paa.assignment_id = p_assg_id
659 AND assignment_action_id = p_asact_id
660 AND tax_unit_id = p_tax_unit_id
661 AND (city.jurisdiction_code||'' = p_jurisdiction OR
662 city.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000')
663 AND tax_type_code <> 'SCHOOL'
664 ORDER BY 2,3;
665
666
667 /* Cursor created as per bug 3362423 */
668 CURSOR c_local_dt2 IS
669 SELECT DISTINCT
670 NVL(ppa.date_earned,ppa.effective_date)effective_date,
671 cnty.jurisdiction_code jurisdiction_code
672 FROM
673 pay_assignment_actions paa ,
674 pay_payroll_actions ppa ,
675 pay_us_emp_county_tax_rules_f cnty ,
676 pay_us_county_tax_info_f ctif ,
677 pay_us_counties names
678 WHERE paa.payroll_action_id = ppa.payroll_action_id
679 AND ppa.effective_date between cnty.effective_start_date AND cnty.effective_end_date
680 AND cnty.assignment_id = paa.assignment_id
681 AND names.county_code = substr(cnty.jurisdiction_code,4,3)
682 AND names.state_code = substr(cnty.jurisdiction_code,1,2)
683 AND ctif.jurisdiction_code = cnty.jurisdiction_code
684 AND ctif.county_tax = 'Y'
685 AND ppa.effective_date between ctif.effective_start_date AND ctif.effective_end_date
686 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
687 AND paa.assignment_id = p_assg_id
688 AND assignment_action_id = p_asact_id
689 AND tax_unit_id = p_tax_unit_id
690 AND (cnty.jurisdiction_code||'' = p_jurisdiction or
691 cnty.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000');
692
693
694 /* Cursor for County taxes with tax_type_code <> School. Modified as per bug 3362423. */
695 CURSOR c_local2(p_eff_date DATE, p_jurisdiction_code VARCHAR2) IS
696 SELECT DISTINCT
697 decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
698 ||initcap(balance_category_code) prompt
699 , decode (tax_type_code
700 ,'COUNTY', 1
701 ,'CITY' , 2
702 ,'HT' , 3
703 , 6) ordercol1
704 , decode (balance_category_code
705 ,'TAXABLE' , 2
706 ,'SUBJECT' , 3
707 ,'WITHHELD' , 4
708 , 6) ordercol2
709 , tax_type_code
710 , balance_category_code
711 , p_jurisdiction_code jurisdiction_code
712 FROM
713 pay_us_local_tax_types_v petv
714 WHERE petv.tax_type_code = 'COUNTY'
715 AND p_eff_date between petv.effective_start_date AND petv.effective_end_date
716 AND petv.tax_type_code <> 'SCHOOL'
717 ORDER BY 2,3;
718
719 /* Cursor created as per bug 3362423 */
720 CURSOR c_local_dt3 IS
721 SELECT DISTINCT
722 NVL(ppa.date_earned,ppa.effective_date)effective_date,
723 jurisdiction_code
724 FROM pay_assignment_actions paa ,
725 pay_payroll_actions ppa ,
726 pay_us_asg_schools_v school
727 WHERE paa.payroll_action_id = ppa.payroll_action_id
728 AND school.assignment_id = paa.assignment_id
729 AND school.tax_unit_id = paa.tax_unit_id
730 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
731 AND paa.assignment_id = p_assg_id
732 AND assignment_action_id = p_asact_id
733 AND paa.tax_unit_id = p_tax_unit_id
734 AND (jurisdiction_code||'' = p_jurisdiction or
735 jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000');
736
737
738 /* Cursor for School taxes with tax_type_code <> School. Modified as per bug 3362423. */
739 CURSOR c_local3(p_eff_date DATE, p_jurisdiction_code VARCHAR2) IS
740 SELECT DISTINCT
741 decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
742 ||initcap(balance_category_code) prompt
743 , decode (tax_type_code
744 ,'COUNTY', 1
745 ,'CITY' , 2
746 ,'HT' , 3
747 , 6) ordercol1
748 , decode (balance_category_code
749 ,'TAXABLE' , 2
750 ,'SUBJECT' , 3
751 ,'WITHHELD' , 4
752 , 6) ordercol2
753 , tax_type_code
754 , balance_category_code
755 , p_jurisdiction_code jurisdiction_code
756 FROM pay_us_local_tax_types_v petv
760
757 WHERE p_eff_date between petv.effective_start_date AND petv.effective_end_date
758 AND petv.tax_type_code <> 'SCHOOL'
759 ORDER BY 2,3;
761 ------------Local Taxes ! School ------------
762
763
764 /* Cursor for City EE Wages with tax_type_code <> School */
765 CURSOR c_local4 IS
766 ------------Local EE Wages ! School ---------
767 SELECT DISTINCT
768 decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
769 ||decode(balance_category_code, 'SUBJECT',
770 'Taxable', initcap(balance_category_code)) prompt
771 , decode (tax_type_code
772 ,'COUNTY', 1
773 ,'CITY', 2
774 ,'HT' , 3
775 , 6) ordercol1
776 , decode (balance_category_code
777 ,'TAXABLE' , 2
778 ,'SUBJECT' , 3
779 ,'WITHHELD' , 4
780 , 6) ordercol2
781 , tax_type_code
782 , balance_category_code
783 , city.jurisdiction_code
784 FROM pay_assignment_actions paa
785 ,pay_payroll_actions ppa
786 ,pay_us_local_ee_wage_types_v petv
787 ,pay_us_city_names names
788 ,pay_us_emp_city_tax_rules_f city
789 ,pay_us_city_tax_info_f citf
790 WHERE paa.payroll_action_id = ppa.payroll_action_id
791 AND ppa.effective_date between city.effective_start_date AND city.effective_end_date
792 AND city.assignment_id = paa.assignment_id
793 AND names.city_code = substr(city.jurisdiction_code,8,4)
794 AND names.county_code = substr(city.jurisdiction_code,4,3)
795 AND names.state_code = substr(city.jurisdiction_code,1,2)
796 AND names.primary_flag = 'Y'
797 AND citf.jurisdiction_code = city.jurisdiction_code
798 AND decode(tax_type_code, 'CITY', citf.city_tax, 'HT', citf.head_tax, 'N') = 'Y'
799 AND ppa.effective_date between citf.effective_start_date AND citf.effective_end_date
800 AND petv.tax_type_code in ('CITY', 'HT')
801 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
802 AND nvl(ppa.date_earned, ppa.effective_date) between petv.effective_start_date AND petv.effective_end_date
803 AND paa.assignment_id = p_assg_id
804 AND assignment_action_id = p_asact_id
805 AND tax_unit_id = p_tax_unit_id
806 AND (city.jurisdiction_code||'' = p_jurisdiction or
807 city.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000')
808 AND tax_type_code <> 'SCHOOL'
809 ORDER BY 2,3;
810
811 /* Cursor for County EE Wages with tax_type_code <> School */
812 CURSOR c_local5 IS
813 SELECT DISTINCT
814 decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
815 ||decode(balance_category_code, 'SUBJECT',
816 'Taxable', initcap(balance_category_code)) prompt
817 , decode (tax_type_code
818 ,'COUNTY', 1
819 ,'CITY', 2
820 ,'HT' , 3
821 , 6) ordercol1
822 , decode (balance_category_code
823 ,'TAXABLE' , 2
824 ,'SUBJECT' , 3
825 ,'WITHHELD' , 4
826 , 6) ordercol2
827 , tax_type_code
828 , balance_category_code
829 , cnty.jurisdiction_code
830 FROM pay_assignment_actions paa
831 ,pay_payroll_actions ppa
832 ,pay_us_local_ee_wage_types_v petv
833 ,pay_us_emp_county_tax_rules_f cnty
834 ,pay_us_county_tax_info_f ctif
835 ,pay_us_counties names
836 WHERE paa.payroll_action_id = ppa.payroll_action_id
837 AND ppa.effective_date between cnty.effective_start_date AND cnty.effective_end_date
838 AND cnty.assignment_id = paa.assignment_id
839 AND names.county_code = substr(cnty.jurisdiction_code,4,3)
840 AND names.state_code = substr(cnty.jurisdiction_code,1,2)
841 AND petv.tax_type_code = 'COUNTY'
842 AND ctif.jurisdiction_code = cnty.jurisdiction_code
843 AND ctif.county_tax = 'Y'
844 AND ppa.effective_date between ctif.effective_start_date AND ctif.effective_end_date
845 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
846 AND nvl(ppa.date_earned, ppa.effective_date) between petv.effective_start_date AND petv.effective_end_date
847 AND paa.assignment_id = p_assg_id
848 AND assignment_action_id = p_asact_id
849 AND tax_unit_id = p_tax_unit_id
850 AND (cnty.jurisdiction_code||'' = p_jurisdiction or
851 cnty.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000')
852 AND tax_type_code <> 'SCHOOL'
853 ORDER BY 2,3;
854
855 /* Cursor created as per bug 3362423 */
856 CURSOR c_local_dt6 IS
857 SELECT DISTINCT
858 NVL(ppa.date_earned,ppa.effective_date)effective_date,
859 jurisdiction_code
860 FROM pay_assignment_actions paa
861 , pay_payroll_actions ppa
862 , pay_us_asg_schools_v school
863 WHERE paa.payroll_action_id = ppa.payroll_action_id
864 AND school.assignment_id = paa.assignment_id
865 AND school.tax_unit_id = paa.tax_unit_id
866 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
867 AND paa.assignment_id = p_assg_id
868 AND assignment_action_id = p_asact_id
869 AND paa.tax_unit_id = p_tax_unit_id
870 AND (jurisdiction_code||'' = p_jurisdiction or
871 jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000');
872
873 /* Cursor for School EE Wages with tax_type_code <> School. Modified as per bug 3362423. */
874 CURSOR c_local6 (p_eff_date DATE, p_jurisdiction_code VARCHAR2) IS
875 SELECT DISTINCT
876 decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
877 ||decode(balance_category_code, 'SUBJECT',
878 'Taxable', initcap(balance_category_code)) prompt
879 , decode (tax_type_code
880 ,'COUNTY', 1
881 ,'CITY', 2
882 ,'HT' , 3
883 , 6) ordercol1
884 , decode (balance_category_code
885 ,'TAXABLE' , 2
886 ,'SUBJECT' , 3
890 , balance_category_code
887 ,'WITHHELD' , 4
888 , 6) ordercol2
889 , tax_type_code
891 , p_jurisdiction_code jurisdiction_code
892 FROM pay_us_local_ee_wage_types_v petv
893 WHERE petv.tax_type_code in ('SCHOOL')
894 AND p_eff_date between petv.effective_start_date AND petv.effective_end_date
895 AND petv.tax_type_code <> 'SCHOOL'
896 ORDER BY 2,3;
897
898 ------------Local EE Wages ! School---------
899
900 /* Cursor for City Taxes with tax_type_code = School */
901 CURSOR c_local7 IS
902 -----local taxes = school-----------
903 SELECT DISTINCT
904 tax_type_code||' '
905 ||initcap(balance_category_code) prompt
906 , 1 ordercol1
907 , decode (balance_category_code
908 ,'TAXABLE' , 2
909 ,'SUBJECT' , 3
910 ,'WITHHELD' , 4
911 , 6) ordercol2
912 , tax_type_code
913 , balance_category_code
914 , city.jurisdiction_code
915 FROM pay_assignment_actions paa ,
916 pay_payroll_actions ppa ,
917 pay_us_local_tax_types_v petv ,
918 pay_us_emp_city_tax_rules_f city ,
919 pay_us_city_names names ,
920 pay_us_city_tax_info_f citf
921 WHERE paa.payroll_action_id = ppa.payroll_action_id
922 AND ppa.effective_date between city.effective_start_date AND city.effective_end_date
923 AND city.assignment_id = paa.assignment_id
924 AND names.city_code = substr(city.jurisdiction_code,8,4)
925 AND names.county_code = substr(city.jurisdiction_code,4,3)
926 AND names.state_code = substr(city.jurisdiction_code,1,2)
927 AND names.primary_flag = 'Y'
928 AND citf.jurisdiction_code = city.jurisdiction_code
929 AND decode(tax_type_code, 'CITY', citf.city_tax, 'HT' , citf.head_tax, 'N') = 'Y'
930 AND ppa.effective_date between citf.effective_start_date AND citf.effective_end_date
931 AND petv.tax_type_code IN ('CITY', 'HT')
932 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
933 AND nvl(ppa.date_earned, ppa.effective_date) between petv.effective_start_date AND petv.effective_end_date
934 AND paa.assignment_id = p_assg_id
935 AND assignment_action_id = p_asact_id
936 AND tax_unit_id = p_tax_unit_id
937 AND city.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
938 AND tax_type_code = 'SCHOOL'
939 ORDER BY 2,3;
940
941 /* Cursor created as per bug 3362423 */
942 CURSOR c_local_dt8 IS
943 SELECT DISTINCT
944 NVL(ppa.date_earned,ppa.effective_date)effective_date,
945 cnty.jurisdiction_code jurisdiction_code
946 FROM pay_assignment_actions paa ,
947 pay_payroll_actions ppa ,
948 pay_us_emp_county_tax_rules_f cnty ,
949 pay_us_county_tax_info_f ctif ,
950 pay_us_counties names
951 WHERE paa.payroll_action_id = ppa.payroll_action_id
952 AND ppa.effective_date between cnty.effective_start_date AND cnty.effective_end_date
953 AND cnty.assignment_id = paa.assignment_id
954 AND names.county_code = substr(cnty.jurisdiction_code,4,3)
955 AND names.state_code = substr(cnty.jurisdiction_code,1,2)
956 AND ctif.jurisdiction_code = cnty.jurisdiction_code
957 AND ctif.county_tax = 'Y'
958 AND ppa.effective_date between ctif.effective_start_date AND ctif.effective_end_date
959 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
960 AND paa.assignment_id = p_assg_id
961 AND assignment_action_id = p_asact_id
962 AND tax_unit_id = p_tax_unit_id
963 AND cnty.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school);
964
965 /* Cursor for County Taxes with tax_type_code = School. Modified as per bug 3362423. */
966 CURSOR c_local8 (p_eff_date DATE, p_jurisdiction_code VARCHAR2) IS
967 SELECT DISTINCT
968 tax_type_code||' '
969 ||initcap(balance_category_code) prompt
970 , 1 ordercol1
971 , decode (balance_category_code
972 ,'TAXABLE' , 2
973 ,'SUBJECT' , 3
974 ,'WITHHELD' , 4
975 , 6) ordercol2
976 , tax_type_code
977 , balance_category_code
978 , p_jurisdiction_code jurisdiction_code
979 FROM
980 pay_us_local_tax_types_v petv
981 WHERE petv.tax_type_code = 'COUNTY'
982 AND p_eff_date between petv.effective_start_date AND petv.effective_end_date
983 AND petv.tax_type_code = 'SCHOOL'
984 ORDER BY 2,3;
985
986 /* Cursor for School Taxes with tax_type_code = School */
987 CURSOR c_local9 IS
988 SELECT DISTINCT
989 tax_type_code||' '
990 ||initcap(balance_category_code) prompt
991 , 1 ordercol1
992 , decode (balance_category_code
993 ,'TAXABLE' , 2
994 ,'SUBJECT' , 3
995 ,'WITHHELD' , 4
996 , 6) ordercol2
997 , tax_type_code
998 , balance_category_code
999 , jurisdiction_code
1000 FROM pay_assignment_actions paa ,
1001 pay_payroll_actions ppa ,
1002 pay_us_local_tax_types_v petv ,
1003 pay_us_asg_schools_v school
1004 WHERE paa.payroll_action_id = ppa.payroll_action_id
1005 AND school.assignment_id = paa.assignment_id
1006 AND school.tax_unit_id = paa.tax_unit_id
1007 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
1008 AND nvl(ppa.date_earned, ppa.effective_date) between petv.effective_start_date AND petv.effective_end_date
1009 AND paa.assignment_id = p_assg_id
1010 AND assignment_action_id = p_asact_id
1011 AND paa.tax_unit_id = p_tax_unit_id
1012 AND jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
1013 AND tax_type_code = 'SCHOOL'
1014 ORDER BY 2,3;
1015 ----local taxes = school ----------------
1016
1017 /* Cursor for City EE Wages with tax_type_code = School */
1021 tax_type_code||' '
1018 CURSOR c_local10 IS
1019 -------Local ee wages = School ----------
1020 SELECT DISTINCT
1022 ||decode(balance_category_code, 'SUBJECT',
1023 'Taxable', initcap(balance_category_code)) prompt
1024 , 1 ordercol1
1025 , decode (balance_category_code
1026 ,'TAXABLE' , 2
1027 ,'SUBJECT' , 3
1028 ,'WITHHELD' , 4
1029 , 6) ordercol2
1030 , tax_type_code
1031 , balance_category_code
1032 , city.jurisdiction_code
1033 FROM pay_assignment_actions paa
1034 ,pay_payroll_actions ppa
1035 ,pay_us_local_ee_wage_types_v petv
1036 ,pay_us_city_names names
1037 ,pay_us_emp_city_tax_rules_f city
1038 ,pay_us_city_tax_info_f citf
1039 WHERE paa.payroll_action_id = ppa.payroll_action_id
1040 AND ppa.effective_date between city.effective_start_date AND city.effective_end_date
1041 AND city.assignment_id = paa.assignment_id
1042 AND names.city_code = substr(city.jurisdiction_code,8,4)
1043 AND names.county_code = substr(city.jurisdiction_code,4,3)
1044 AND names.state_code = substr(city.jurisdiction_code,1,2)
1045 AND names.primary_flag = 'Y'
1046 AND citf.jurisdiction_code = city.jurisdiction_code
1047 AND decode(tax_type_code, 'CITY', citf.city_tax, 'HT', citf.head_tax, 'N') = 'Y'
1048 AND ppa.effective_date between citf.effective_start_date AND citf.effective_end_date
1049 AND petv.tax_type_code in ('CITY', 'HT')
1050 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
1051 AND nvl(ppa.date_earned, ppa.effective_date) between petv.effective_start_date AND petv.effective_end_date
1052 AND paa.assignment_id = p_assg_id
1053 AND assignment_action_id = p_asact_id
1054 AND tax_unit_id = p_tax_unit_id
1055 AND city.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
1056 AND tax_type_code = 'SCHOOL'
1057 ORDER BY 2,3;
1058
1059 /* Cursor for County EE Wages with tax_type_code = School */
1060 CURSOR c_local11 IS
1061 SELECT DISTINCT
1062 tax_type_code||' '
1063 ||decode(balance_category_code, 'SUBJECT',
1064 'Taxable', initcap(balance_category_code)) prompt
1065 , 1 ordercol1
1066 , decode (balance_category_code
1067 ,'TAXABLE' , 2
1068 ,'SUBJECT' , 3
1069 ,'WITHHELD' , 4
1070 , 6) ordercol2
1071 , tax_type_code
1072 , balance_category_code
1073 , cnty.jurisdiction_code
1074 FROM pay_assignment_actions paa
1075 ,pay_payroll_actions ppa
1076 ,pay_us_local_ee_wage_types_v petv
1077 ,pay_us_emp_county_tax_rules_f cnty
1078 ,pay_us_county_tax_info_f ctif
1079 ,pay_us_counties names
1080 WHERE paa.payroll_action_id = ppa.payroll_action_id
1081 AND ppa.effective_date between cnty.effective_start_date AND cnty.effective_end_date
1082 AND cnty.assignment_id = paa.assignment_id
1083 AND names.county_code = substr(cnty.jurisdiction_code,4,3)
1084 AND names.state_code = substr(cnty.jurisdiction_code,1,2)
1085 AND petv.tax_type_code = 'COUNTY'
1086 AND ctif.jurisdiction_code = cnty.jurisdiction_code
1087 AND ctif.county_tax = 'Y'
1088 AND ppa.effective_date between ctif.effective_start_date AND ctif.effective_end_date
1089 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
1090 AND nvl(ppa.date_earned, ppa.effective_date) between petv.effective_start_date AND petv.effective_end_date
1091 AND paa.assignment_id = p_assg_id
1092 AND assignment_action_id = p_asact_id
1093 AND tax_unit_id = p_tax_unit_id
1094 AND cnty.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
1095 AND tax_type_code = 'SCHOOL'
1096 ORDER BY 2,3;
1097
1098 /* Cursor created as per bug 3362423 */
1099 CURSOR c_local_dt12 IS
1100 SELECT DISTINCT
1101 NVL(ppa.date_earned,ppa.effective_date)effective_date,
1102 jurisdiction_code
1103 FROM pay_assignment_actions paa
1104 , pay_payroll_actions ppa
1105 , pay_us_asg_schools_v school
1106 WHERE paa.payroll_action_id = ppa.payroll_action_id
1107 AND school.assignment_id = paa.assignment_id
1108 AND school.tax_unit_id = paa.tax_unit_id
1109 AND ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
1110 AND paa.assignment_id = p_assg_id
1111 AND assignment_action_id = p_asact_id
1112 AND paa.tax_unit_id = p_tax_unit_id
1113 AND jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school);
1114
1115 /* Cursor for School EE Wages with tax_type_code = School. Modified as per bug 3362423. */
1116 CURSOR c_local12 (p_eff_date DATE, p_jurisdiction_code VARCHAR2) IS
1117 SELECT DISTINCT
1118 tax_type_code||' '
1119 ||decode(balance_category_code, 'SUBJECT',
1120 'Taxable', initcap(balance_category_code)) prompt
1121 , 1 ordercol1
1122 , decode (balance_category_code
1123 ,'TAXABLE' , 2
1124 ,'SUBJECT' , 3
1125 ,'WITHHELD' , 4
1126 , 6) ordercol2
1127 , tax_type_code
1128 , balance_category_code
1129 , p_jurisdiction_code jurisdiction_code
1130 FROM pay_us_local_ee_wage_types_v petv
1131 WHERE p_eff_date between petv.effective_start_date AND petv.effective_end_date
1132 AND petv.tax_type_code = 'SCHOOL'
1133 ORDER BY 2,3;
1134 ------- Local ee wages = school-----------
1135
1136 l_bal_val number;
1137 l_cnt number := 1;
1138 l_procedure varchar2(30) := 'get_local';
1139
1140
1141 PROCEDURE get_local_balances (p_prompt in varchar2,
1142 p_tax_type_code in varchar2,
1143 p_balance_category_code in varchar2,
1144 p_jurisdiction_code in varchar2
1145 )
1146 IS
1150 p_local_taxes_tab(l_cnt).prompt := p_prompt;
1147 l_procedure varchar2(30) := 'get_local_balances';
1148 BEGIN
1149 hr_utility.set_location(l_package||l_procedure, 130);
1151 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
1152 p_tax_balance_category => p_balance_category_code,
1153 p_tax_type => p_tax_type_code,
1154 p_ee_or_er => p_ee_er,
1155 p_time_type => 'YTD',
1156 p_gre_id_context => p_tax_unit_id,
1157 p_jd_context => p_jurisdiction_code,
1158 p_assignment_action_id => p_asact_id ,
1159 p_assignment_id => p_assg_id,
1160 p_virtual_date => NULL,
1161 p_payroll_action_id => NULL);
1162 p_local_taxes_tab(l_cnt).ytd_val := l_bal_val;
1163 hr_utility.set_location(l_package||l_procedure, 140);
1164 hr_utility.trace('YTD value = '||l_bal_val);
1165
1166 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
1167 p_tax_balance_category => p_balance_category_code,
1168 p_tax_type => p_tax_type_code,
1169 p_ee_or_er => p_ee_er,
1170 p_time_type => 'PTD',
1171 p_gre_id_context => p_tax_unit_id,
1172 p_jd_context => p_jurisdiction_code,
1173 p_assignment_action_id => p_asact_id ,
1174 p_assignment_id => p_assg_id,
1175 p_virtual_date => NULL,
1176 p_payroll_action_id => NULL);
1177 p_local_taxes_tab(l_cnt).ptd_val := l_bal_val;
1178 hr_utility.set_location(l_package||l_procedure, 150);
1179 hr_utility.trace('PTD value = '||l_bal_val);
1180
1181 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
1182 p_tax_balance_category => p_balance_category_code,
1183 p_tax_type => p_tax_type_code,
1184 p_ee_or_er => p_ee_er,
1185 p_time_type => 'MONTH',
1186 p_gre_id_context => p_tax_unit_id,
1187 p_jd_context => p_jurisdiction_code,
1188 p_assignment_action_id => p_asact_id ,
1189 p_assignment_id => p_assg_id,
1190 p_virtual_date => NULL,
1191 p_payroll_action_id => NULL);
1192 p_local_taxes_tab(l_cnt).mtd_val := l_bal_val;
1193 hr_utility.set_location(l_package||l_procedure, 160);
1194 hr_utility.trace('MTD value = '||l_bal_val);
1195
1196 l_bal_val := pay_us_taxbal_view_pkg.us_tax_balance_vm(
1197 p_tax_balance_category => p_balance_category_code,
1198 p_tax_type => p_tax_type_code,
1199 p_ee_or_er => p_ee_er,
1200 p_time_type => 'QTD',
1201 p_gre_id_context => p_tax_unit_id,
1202 p_jd_context => p_jurisdiction_code,
1203 p_assignment_action_id => p_asact_id ,
1204 p_assignment_id => p_assg_id,
1205 p_virtual_date => NULL,
1206 p_payroll_action_id => NULL);
1207 p_local_taxes_tab(l_cnt).qtd_val := l_bal_val;
1208 hr_utility.set_location(l_package||l_procedure, 170);
1209 hr_utility.trace('QTD value = '||l_bal_val);
1210
1211 EXCEPTION
1212 WHEN others THEN
1213 hr_utility.set_location(l_package||l_procedure,180);
1214 raise_application_error(-20101, 'Error in ' || l_package||l_procedure || ' - ' || sqlerrm);
1215 END get_local_balances;
1216
1217 BEGIN
1218 hr_utility.set_location(l_package||l_procedure, 10);
1219 --hr_utility.trace_on(null,'tax_bal_summary');
1220 hr_utility.trace('opening cursor for city tax, tax_type_code <> SCHOOL');
1221 FOR localrec in c_local1 LOOP
1222 get_local_balances (
1223 p_prompt => localrec.prompt,
1224 p_tax_type_code => localrec.tax_type_code,
1225 p_balance_category_code => localrec.balance_category_code,
1226 p_jurisdiction_code => localrec.jurisdiction_code);
1227 l_cnt := l_cnt + 1;
1228 END LOOP;
1229
1230 hr_utility.set_location(l_package||l_procedure, 20);
1231 hr_utility.trace('opening cursor for county tax, tax_type_code <> SCHOOL');
1232
1233 FOR local_dt_rec in c_local_dt2 LOOP
1234 FOR localrec in c_local2 (local_dt_rec.effective_date, local_dt_rec.jurisdiction_code) LOOP
1235 get_local_balances (
1236 p_prompt => localrec.prompt,
1237 p_tax_type_code => localrec.tax_type_code,
1238 p_balance_category_code => localrec.balance_category_code,
1239 p_jurisdiction_code => localrec.jurisdiction_code);
1240 l_cnt := l_cnt + 1;
1241 END LOOP;
1242 END LOOP;
1243
1244 hr_utility.set_location(l_package||l_procedure, 30);
1245 hr_utility.trace('opening cursor for school tax, tax_type_code <> SCHOOL');
1246
1247 FOR local_dt_rec in c_local_dt3 LOOP
1248 FOR localrec in c_local3 (local_dt_rec.effective_date, local_dt_rec.jurisdiction_code) LOOP
1249 get_local_balances (
1250 p_prompt => localrec.prompt,
1251 p_tax_type_code => localrec.tax_type_code,
1252 p_balance_category_code => localrec.balance_category_code,
1253 p_jurisdiction_code => localrec.jurisdiction_code);
1254 l_cnt := l_cnt + 1;
1255 END LOOP;
1256 END LOOP;
1257
1258 hr_utility.set_location(l_package||l_procedure, 40);
1259 hr_utility.trace('opening cursor for city EE wages, tax_type_code <> SCHOOL');
1260 FOR localrec in c_local4 LOOP
1261 get_local_balances (
1262 p_prompt => localrec.prompt,
1263 p_tax_type_code => localrec.tax_type_code,
1264 p_balance_category_code => localrec.balance_category_code,
1265 p_jurisdiction_code => localrec.jurisdiction_code);
1266 l_cnt := l_cnt + 1;
1267 END LOOP;
1268
1269 hr_utility.set_location(l_package||l_procedure, 50);
1270 hr_utility.trace('opening cursor for county EE wages, tax_type_code <> SCHOOL');
1271 FOR localrec in c_local5 LOOP
1272 get_local_balances (
1276 p_jurisdiction_code => localrec.jurisdiction_code);
1273 p_prompt => localrec.prompt,
1274 p_tax_type_code => localrec.tax_type_code,
1275 p_balance_category_code => localrec.balance_category_code,
1277 l_cnt := l_cnt + 1;
1278 END LOOP;
1279
1280 hr_utility.set_location(l_package||l_procedure, 60);
1281 hr_utility.trace('opening cursor for school EE wages, tax_type_code <> SCHOOL');
1282
1283 FOR local_dt_rec in c_local_dt6 LOOP
1284 FOR localrec in c_local6 (local_dt_rec.effective_date, local_dt_rec.jurisdiction_code) LOOP
1285 get_local_balances (
1286 p_prompt => localrec.prompt,
1287 p_tax_type_code => localrec.tax_type_code,
1288 p_balance_category_code => localrec.balance_category_code,
1289 p_jurisdiction_code => localrec.jurisdiction_code);
1290 l_cnt := l_cnt + 1;
1291 END LOOP;
1292 END LOOP;
1293
1294 hr_utility.set_location(l_package||l_procedure, 70);
1295 hr_utility.trace('opening cursor for city tax, tax_type_code = SCHOOL');
1296 FOR localrec in c_local7 LOOP
1297 get_local_balances (
1298 p_prompt => localrec.prompt,
1299 p_tax_type_code => localrec.tax_type_code,
1300 p_balance_category_code => localrec.balance_category_code,
1301 p_jurisdiction_code => localrec.jurisdiction_code);
1302 l_cnt := l_cnt + 1;
1303 END LOOP;
1304
1305 hr_utility.set_location(l_package||l_procedure, 80);
1306 hr_utility.trace('opening cursor for county tax, tax_type_code = SCHOOL');
1307 FOR local_dt_rec in c_local_dt8 LOOP
1308 FOR localrec in c_local8 (local_dt_rec.effective_date, local_dt_rec.jurisdiction_code) LOOP
1309 get_local_balances (
1310 p_prompt => localrec.prompt,
1311 p_tax_type_code => localrec.tax_type_code,
1312 p_balance_category_code => localrec.balance_category_code,
1313 p_jurisdiction_code => localrec.jurisdiction_code);
1314 l_cnt := l_cnt + 1;
1315 END LOOP;
1316 END LOOP;
1317
1318 hr_utility.set_location(l_package||l_procedure, 90);
1319 hr_utility.trace('opening cursor for school tax, tax_type_code = SCHOOL');
1320 FOR localrec in c_local9 LOOP
1321 get_local_balances (
1322 p_prompt => localrec.prompt,
1323 p_tax_type_code => localrec.tax_type_code,
1324 p_balance_category_code => localrec.balance_category_code,
1325 p_jurisdiction_code => localrec.jurisdiction_code);
1326 l_cnt := l_cnt + 1;
1327 END LOOP;
1328
1329 hr_utility.set_location(l_package||l_procedure, 100);
1330 hr_utility.trace('opening cursor for city EE wages, tax_type_code = SCHOOL');
1331 FOR localrec in c_local10 LOOP
1332 get_local_balances (
1333 p_prompt => localrec.prompt,
1334 p_tax_type_code => localrec.tax_type_code,
1335 p_balance_category_code => localrec.balance_category_code,
1336 p_jurisdiction_code => localrec.jurisdiction_code);
1337 l_cnt := l_cnt + 1;
1338 END LOOP;
1339
1340 hr_utility.set_location(l_package||l_procedure, 110);
1341 hr_utility.trace('opening cursor for county EE wages, tax_type_code = SCHOOL');
1342 FOR localrec in c_local11 LOOP
1343 get_local_balances (
1344 p_prompt => localrec.prompt,
1345 p_tax_type_code => localrec.tax_type_code,
1346 p_balance_category_code => localrec.balance_category_code,
1347 p_jurisdiction_code => localrec.jurisdiction_code);
1348 l_cnt := l_cnt + 1;
1349 END LOOP;
1350
1351 hr_utility.set_location(l_package||l_procedure, 120);
1352 hr_utility.trace('opening cursor for school EE wages, tax_type_code = SCHOOL');
1353
1354 FOR local_dt_rec in c_local_dt12 LOOP
1355 FOR localrec in c_local12 (local_dt_rec.effective_date, local_dt_rec.jurisdiction_code) LOOP
1356 get_local_balances (
1357 p_prompt => localrec.prompt,
1358 p_tax_type_code => localrec.tax_type_code,
1359 p_balance_category_code => localrec.balance_category_code,
1360 p_jurisdiction_code => localrec.jurisdiction_code);
1361 l_cnt := l_cnt + 1;
1362 END LOOP;
1363 END LOOP;
1364
1365 hr_utility.trace('Normal completion of '||l_package||l_procedure);
1366 EXCEPTION
1367 WHEN others THEN
1368 hr_utility.set_location(l_package||l_procedure,190);
1369 hr_utility.trace('Abnormal completion of '||l_package||l_procedure);
1370 raise_application_error(-20101, 'Error in ' || l_package||l_procedure || ' - ' || sqlerrm);
1371 END get_local;
1372
1373 END pay_us_tax_bal_summary_pkg;
1374