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