[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAXBAL_VIEW_PKG
Source
1 package body pay_us_taxbal_view_pkg as
2 /* $Header: pyustxbv.pkb 120.5.12010000.3 2008/09/09 16:24:16 rnestor 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 : pyustxbv.pkb
21
22 Description : API to get US tax balance figures.
23
24 Change History
25
26 Name Date Ver. Description
27 --------- ----------- ------ ------------------------------------------------
28 B Homan 12-JAN-1998 Created based on ARASHID code
29 tbattoo 11-MAY-1998 changed so GRE PYDATE routes work over a range
30 mmukherj 26-MAY-1998 changed us_tax_balance_function to use
31 REPORT_TYPE session variable
32 lwthomps 19-JUN-1998 40.5 Created 'Current' time type for check writer
33 when a payment is made of multiple runs.
34 lwthomps 30-JUN-1998 40.6 cleaned up current in named balance
35 lwthomps 30-JUN-1998 40.7 Business group id not set in named_balance
36 meshah 23-DEC-1998 checking session variable QTD or YTD
37 tbattoo 06-JAN-1999 40.9 Fxed bug 788693, changed so YTD cursor in
38 run_asg_vdate_cursor, references YTD instead
39 of QTD
40 meshah 10-FEB-1999 40.10 added in new functions for the payroll
41 register report to handle reversals.
42 the function names are get_prepayment_aaid,
43 reversal_exists_check, reversal_exists_check_tax.
44 rthakur 11-FEB-1999 40.11 uncommented out nocopy the exit command
45 sdoshi 06-APR-1999 115.3 Flexible Dates Conversion
46 sdoshi 06-APR-1999 115.4 Corrections - closing brackets added
47 to fnd_date statements
48 djoshi 08-apr-1999 Verfied and converted for Canonical
49 Complience of Date
50 AMills 28-Jul-1999 115.7 Added us_gp_multiple_gre_ytd,
51 us_gp_subject_to_tax_gre_ytd
52 and us_gp_gre_jd_ytd for Report
53 Tuning, for PAYUSTOT
54 and PAYUS940.
55 skutteti 14-Sep-1999 115.8 Pre-tax enhancements
56 ssarma 31-dec-1999 115.9 Tuning of us_gp_multiple_gre_ytd,
57 us_gp_subject_to_tax_gre_ytd,
58 us_gp_gre_jd_ytd for 1086239 -
59 Performance of PAYUSTOT and PAYUS940.
60 ahanda 07-FEB-2000 115.10 Checking session variable for RUN, PYDATE
61 MONTH, QTD before getting balance.
62 ekim 15-may-2000 115.11 Added legislation check
63 for get_balance_type
64 JARTHURT 24-JUL-2000 115.12 Added legislation check
65 for get_defined_balance
66 JARTHURT 25-JUL-2000 115.13 Corrected legislation check for
67 get_defined_balance
68 irgonzal 13-DEC-2000 115.16 Modified SELECT statements due to poor
69 performance on GRE Totals Report (1542061).
70 Added rule-based hint.
71 irgonzal 15-DEC-2000 115.19 Added rule-based hint to SELECT stmt. in
72 us_gp_subject_to_tax_gre_ytd procedure.
73 ahanda 27-DEC-2000 115.20 Did the change done in 115.19 version of
74 package to 115.16 ver. 115.17 and 115.19
75 should not be send to clients as it has
76 the Winstar Changes.
77 tclewis 6-SEP-2001 115.22 Modified the PAYMENTS_BALANCE_REQUIRED
78 function to work correctly with the umbrella
79 process
80 tclewis 7-SEP-2001 115.24 Added check for session variable PTD in the
81 procedure US_NAMED_BALANCE_VM. Now if the
82 session variable PTD is FALSE the procedure
83 will return null and not attempt to calculate
84 the value.
85 tclewis 11-27-2001 115.25 Added dbdrv command.
86 tclewis 12-05-2001 115.27 Added the following procedures
87 us_gp_multiple_gre_mtd
88 us_gp_multiple_gre_ctd
89 us_gp_subject_to_tax_gre_mtd
90 us_gp_subject_to_tax_gre_ctd
91 us_gp_gre_jd_mtd
92 us_gp_gre_jd_ctd
93 meshah 12-05-2001 115.28 Added set verify off
94 tmehra 12-16-2001 115.29 Currently there is no balance for FIT gross,
95 instead 'Gross Earnings' is used. Changed code
96 to subtract Alien earnings from FIT Gross.
97 ahanda 05-JAN-2002 115.31 Changed the following function to work with
98 umbrella process:
99 get_prepayment_aaid
100 reversal_exists_check
101 reversal_exists_check_tax
102 us_named_balance_vm
103 ahanda 08-JAN-2002 115.32 Changed function reversal_exists_check
104 to pass the business_group_id so that
105 it is set properly. Also changed the
106 default for BG ID to -1 from 0 (2175134).
107 meshah 22-JAN-2002 115.34 added checkfile command. leap frogged 115.33
108 ahanda 23-APR-2002 115.35 Checking session variable for CURRENT
109 in us_named_balance_vm and CURRENT, RUN, PTD,
110 PYDATE, MONTH in us_tax_balance_vm.
111 tclewis 1-may-2002 115.36 Modified the cursors c_run_actions in the
112 procedures
113 reversal_exists_check
114 reversal_exists_check_tax
115 to return data for only the assignment_id
116 processed in the run payroll actions.
117 Eliminate a second join to pay_action_interlocks
118 in the procedure get_prepayment_aaid, as it
119 was not needed.
120 ekim 25-Nov-2002 115.38 Changed like to = in function get_defined_balance
121 in query that gets l_defined_balance_id.
122
123 ekim 25-Nov-2002 115.39 GSCC warning fix for default value.
124 ekim 02-Dec-2002 115.40 GSCC warning fix for nocopy.
125 tclewis 13-MAR-2003 115.41 Modified US_NAMED_BALANCE_VM and US_TAX_BALANCE_VM
126 with respect to the CURRENT Dimension, removed the
127 REV_CHK work around and implemented the ASG_PAYMENTS
128 balance dimension. I also modified US_TAX_BALANCE
129 to accept ASG_PAYMENTS as a vaild time_type.
130 kaverma 19-NOV-2003 115.43 Added status <> 'D' for pay_taxability_rules
131 kaverma 21-NOV-2003 115.44 Corrected join for pay_taxability_rules as
132 nvl(status,'X') <> 'D'
133 sdahiya 12-JAN-2004 115.45 Modified query for performance (Bug 3343982).
134 tclewis 14-JAN-2004 115.46 Added STEIC to check of Taxable and excess bal.
135 djoshi 29-JAN-2004 115.47 Changed the function payments_balance_required
136 to make sure asg_payments route is executed
137 when current ...
138 sdahiya 17-FEB-2004 115.48 Removed RULE hint from queries. Bug 3331031.
139 pragupta 14-APR-2005 115.50 The us_gp_multiple_gre_qtd changed to support 12
140 instead of 10 balance calls.
141 pragupta 20-APR-2005 115.51 us_gp_multiple_gre_qtd procedure overloaded to
142 support 12 instead of 10 balances
143 sackumar 15-SEP-2005 115.53 Revert back the changes done in 115.52.
144 rdhingra 23-SEP-2005 115.54 Bug 4583566: Performance changes done
145 rdhingra 23-SEP-2005 115.55 Bug 4583566: Performance changes done
146 rdhingra 27-SEP-2005 115.56 Bug 4583566: Performance changes done
147 rnestor 09-SEP-2008 115.58 Bug 6989549: TAX SUMMARY & TAX BALANCE SCREEN SHOWS
148 DIFFERENT VALUES FOR EIC SUBJECT
149
150 */
151
152 -- Global declarations
153 type num_array is table of number(15) index by binary_integer;
154 type char80_array is table of varchar2(80) index by binary_integer;
155 type char_array is table of varchar2(1) index by binary_integer;
156 --
157 -- Assignment Id Cache
158 g_asgid_tbl_id num_array;
159 g_asgid_tbl_bgid num_array;
160 g_nxt_free_asgid binary_integer := 0;
161 --
162 -- Group Dimension Cache.
163 g_dim_tbl_grp char80_array;
164 g_dim_tbl_asg char80_array;
165 g_dim_tbl_crs num_array;
166 g_dim_tbl_vtd num_array;
167 g_dim_tbl_jdr char_array;
168 g_dim_tbl_btt char_array;
169 g_nxt_free_dim binary_integer;
170 --
171 -- 'Current' balance dimension info for
172 -- prepayments related to many payroll runs
173 g_run_action_id NUMBER;
174 g_prepay_action_id NUMBER;
175
176 -- Constants for assignment cursors.
177 --
178 ASG_CURSOR0 constant number := 0;
179 ASG_CURSOR1 constant number := 1;
180 ASG_CURSOR2 constant number := 2;
181 --
182 -- Constants for assignment vrtual date cursors.
183 --
184 ASG_VDATE_QTD0 constant number := 0;
185 ASG_VDATE_YTD0 constant number := 1;
186
187 -- BHOMAN
188 bh_workaround_local_error EXCEPTION;
189
190 -------------------------------------------------------------------------------
191 --
192 -- Quick procedure to raise an error
193 --
194 -------------------------------------------------------------------------------
195 PROCEDURE local_error(p_procedure varchar2,
196 p_step number) IS
197 BEGIN
198 --
199 FND_MESSAGE.SET_NAME(801,'HR_6153_ALL_PROCEDURE_FAIL');
200 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'bh_taxbal.'||p_procedure, FALSE);
201 FND_MESSAGE.SET_TOKEN('STEP',p_step, FALSE);
202 --FND_MESSAGE.RAISE_ERROR;
203 -- BHOMAN - todo have to raise some error
204 -- RAISE_APPLICATION_ERROR(-20001, 'local_error called');
205 pay_us_balance_view_pkg.debug_err('local error exception raised from proc: ' ||
206 p_procedure);
207 raise bh_workaround_local_error;
208 --
209 END local_error;
210 --
211 --
212 -------------------------------------------------------------------------------
213 -- run_asg_vdate_cursor
214 -------------------------------------------------------------------------------
215 FUNCTION run_asg_vdate_cursor
216 (
217 p_curno in number,
218 p_assignment_id in number,
219 p_date_earned in date,
220 p_date2_earned in date,
221 p_asg_vdate out nocopy date
222 )
223 RETURN NUMBER
224 IS
225 n_rows number;
226 --
227 cursor asg_vdate_qtd_cursor0( c_assignment_id in number,
228 c_date_earned in date,
229 c_date2_earned in date )
230 is
231 select max(PAF.effective_end_date)
232 from per_assignments_f PAF
233 where PAF.assignment_id = c_assignment_id
234 and PAF.payroll_id is not null
235 and PAF.effective_end_date
236 between trunc(c_date_earned,'Q') and c_date2_earned;
237 --
238 cursor asg_vdate_ytd_cursor0( c_assignment_id in number,
239 c_date_earned in date,
240 c_date2_earned in date )
241 is
242 select max(PAF.effective_end_date)
243 from per_assignments_f PAF
244 where PAF.assignment_id = c_assignment_id
245 and PAF.payroll_id is not null
246 and PAF.effective_end_date
247 between trunc(c_date_earned,'Y') and c_date2_earned;
248 BEGIN
249 --
250 if p_curno = ASG_VDATE_QTD0 then
251 open asg_vdate_qtd_cursor0(p_assignment_id, p_date_earned, p_date2_earned);
252 fetch asg_vdate_qtd_cursor0 into p_asg_vdate;
253 n_rows := asg_vdate_qtd_cursor0%rowcount;
254 close asg_vdate_qtd_cursor0;
255 return n_rows;
256 end if;
257 --
258 if p_curno = ASG_VDATE_YTD0 then
259 open asg_vdate_ytd_cursor0(p_assignment_id, p_date_earned, p_date2_earned);
260 fetch asg_vdate_ytd_cursor0 into p_asg_vdate;
261 n_rows := asg_vdate_ytd_cursor0%rowcount;
262 close asg_vdate_ytd_cursor0;
263 return n_rows;
264 end if;
265 --
266 p_asg_vdate := null;
267 local_error( 'run_asg_vdate_cursor', '1' );
268 return 0;
269 EXCEPTION
270 when others then
271 --
272 if asg_vdate_qtd_cursor0%isopen then
273 close asg_vdate_qtd_cursor0;
274 elsif asg_vdate_ytd_cursor0%isopen then
275 close asg_vdate_ytd_cursor0;
276 end if;
277 raise;
278 END run_asg_vdate_cursor;
279
280 ---------------------------------------------------------------------------
281 -- FUNCTION: Payments_Balance_Required
282 -- This function caches information related to an assignment action
283 -- for a payroll run related to a pre-payment composed of multiple
284 -- runs. This is to support the 'CURRENT' balance value displayed
285 -- on checkwriter and related reports(PAYRPCHK, PAYRPPST, PAYRPREG)
286 --
287 -- Returns:
288 -- TRUE if multiple runs exists and sets global prepayment id
289 -- FALSE if a single run exists and clears global prepayment id
290 --------------------------------------------------------------------------
291
292 FUNCTION payments_balance_required(p_assignment_action_id NUMBER) RETURN boolean IS
293
294 cursor c_prepay_action (cp_run_action_id number) is
295 select paa.assignment_action_id
296 from pay_action_interlocks pai,
297 pay_assignment_actions paa,
298 pay_payroll_actions ppa
299 where pai.locked_action_id = cp_run_action_id
300 and paa.assignment_action_id = pai.locking_action_id
301 and ppa.payroll_action_id = paa.payroll_action_id
302 and ppa.action_type in ('P', 'U');
303
304 cursor c_payments (cp_pre_pymt_action_id number,
305 cp_assignment_action_id number) is
306 select ppp.source_action_id
307 from pay_pre_payments ppp
308 where ppp.assignment_action_id = cp_pre_pymt_action_id
309 and ppp.source_action_id = cp_assignment_action_id;
310 /*
311 CURSOR c_count_runs(cp_pre_pymt_action_id NUMBER ) IS
312 select count(pai.locked_action_id)
313 from pay_action_interlocks pai,
314 pay_pre_payments ppp,
315 pay_assignment_actions paa
316 where ppp.assignment_action_id = cp_pre_pymt_action_id
317 and nvl(ppp.source_action_id,0) <> pai.locked_action_id
318 and pai.locking_action_id = ppp.assignment_action_id
319 and pai.locked_action_id = paa.assignment_action_id
320 and paa.source_action_id is not null;
321 */
322
323
324 CURSOR c_count_runs(cp_pre_pymt_action_id NUMBER , cp_run_type_id NUMBER) IS
325 select count(pai.locked_action_id)
326 from pay_action_interlocks pai,
327 pay_assignment_actions paa
328 where pai.locking_action_id = cp_pre_pymt_action_id
329 and pai.locked_action_id = paa.assignment_action_id
330 and nvl(paa.run_type_id,cp_run_type_id) <> cp_run_type_id
331 and paa.source_action_id is not null;
332
333
334 cursor c_run_type_id is
335 select prt.run_type_id
336 from pay_run_types_f prt
337 where prt.shortname = 'SEPCHECK'
338 and prt.legislation_code = 'US';
339
340 l_count_runs NUMBER;
341 l_prepay_action_id NUMBER;
342 l_source_action_id NUMBER;
343 l_run_type_id NUMBER := -9999;
344
345 BEGIN
346
347 /* fetch the runtype id for sepcheck*/
348
349 open c_run_type_id;
350 fetch c_run_type_id into l_run_type_id;
351 close c_run_type_id;
352
353
354 IF g_run_action_id = p_assignment_action_id
355 AND g_prepay_action_id IS NOT NULL THEN /* Have processed this assignment and*/
356 RETURN TRUE; /* it does have multiple RUNS */
357 ELSIF g_run_action_id = p_assignment_action_id
358 AND g_prepay_action_id IS NULL THEN /* Have processed this assignment and*/
359 RETURN FALSE; /* it does not have multiple RUNS */
360 ELSE
361 g_run_action_id := p_assignment_action_id; /* set Run action id */
362 open c_prepay_action (p_assignment_action_id);
363 fetch c_prepay_action into g_prepay_action_id;
364 if c_prepay_action%FOUND then
365 close c_prepay_action;
366 open c_payments (g_prepay_action_id, p_assignment_action_id);
367 fetch c_payments into l_source_action_id;
368 if c_payments%NOTFOUND then
369 close c_payments;
370 open c_count_runs (g_prepay_action_id,l_run_type_id);
371 fetch c_count_runs into l_count_runs;
372 if c_count_runs%NOTFOUND or l_count_runs < 2 then
373 close c_count_runs;
374 g_prepay_action_id := NULL; /* Clear asg_act_ids if they do not */
375 RETURN FALSE;
376 else
377 close c_count_runs;
378 g_run_action_id := p_assignment_action_id; /* Set asg_act_ids if multple runs */
379 RETURN TRUE;
380 end if;
381 else
382 close c_payments;
383 g_prepay_action_id := NULL; /* Clear asg_act_ids if they do not */
384 RETURN FALSE;
385 end if;
386 else
387 close c_prepay_action;
388 g_prepay_action_id := NULL; /* Clear asg_act_ids if they do not */
389 RETURN FALSE;
390 end if;
391 END IF;
392
393 END; /* payments_balance_required */
394 --
395 ----------------------------------------------------------------------------
396 -- Get the assignment level equivalent of the group balance, plus a cursor
397 -- that returns all the assignments contributing to the group level balance.
398 ----------------------------------------------------------------------------
399 procedure get_asg_for_grp_lvl(p_grp_dvl_dimension in varchar2,
400 p_asg_lvl_dimension out nocopy varchar2,
401 p_asg_cursor out nocopy varchar2,
402 p_asg_jd_required out nocopy boolean,
403 p_asg_vdate_cursor out nocopy number,
404 p_asg_balance_time out nocopy varchar2,
405 p_found out nocopy boolean)
406 is
407 l_count number;
408 l_found boolean;
409 begin
410 -- Look to see if the group level balance is in our cache.
411 --
412 --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 10);
413 pay_us_balance_view_pkg.debug_msg( '===========================================');
414 pay_us_balance_view_pkg.debug_msg('get_asg_for_grp_lvl entry:');
415 pay_us_balance_view_pkg.debug_msg(' p_grp_dvl_dimension: ' || p_grp_dvl_dimension);
416 --
417 l_count := 0;
418 l_found := FALSE;
419 while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
420 pay_us_balance_view_pkg.debug_msg(' checking internal dim tables, count: '
421 || l_count);
422 if (p_grp_dvl_dimension = g_dim_tbl_grp(l_count)) then
423 --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 20);
424 --
425 p_asg_lvl_dimension := g_dim_tbl_asg(l_count);
426 p_asg_cursor := g_dim_tbl_crs(l_count);
427 p_asg_vdate_cursor := g_dim_tbl_vtd(l_count);
428 p_asg_balance_time := g_dim_tbl_btt(l_count);
429 --
430 -- Does the cursor require the jurisdiction_code.
431 --
432 if g_dim_tbl_jdr(l_count) = 'Y' then
433 p_asg_jd_required := TRUE;
434 else
435 p_asg_jd_required := FALSE;
436 end if;
437 l_found := TRUE;
438 pay_us_balance_view_pkg.debug_msg(' FOUND asg level');
439 --
440 end if;
441 l_count := l_count + 1;
442 end loop;
443 --
444 --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 30);
445 p_found := l_found;
446 --
447 end;
448 --
449 -----------------------------------------------------------------------------
450 FUNCTION get_balance_type (p_balance_name in varchar2) RETURN NUMBER
451 --
452 IS
453 l_balance_type_id number;
454 --
455 cursor get_balance_type_id (c_balance_name in varchar2) is
456 select balance_type_id
457 from pay_balance_types
458 where balance_name = c_balance_name
459 and legislation_code = 'US';
460 --
461 BEGIN
462 --
463 if p_balance_name is not null then
464 --
465 open get_balance_type_id(p_balance_name);
466 fetch get_balance_type_id into l_balance_type_id;
467 close get_balance_type_id;
468 --
469 if l_balance_type_id is NULL then
470 RAISE NO_DATA_FOUND;
471 end if;
472 --
473 end if;
474 --
475 RETURN l_balance_type_id;
476 --
477 END get_balance_type;
478 --
479 ----------------------------------------------------------------------------
480 -- Get the defined balance id given the balance name and database item
481 -- suffix.
482 ----------------------------------------------------------------------------
483 function get_defined_balance (p_balance_name varchar2,
484 p_dimension_suffix varchar2) return number is
485 l_defined_balance_id number;
486 l_business_group_id number;
487 --
488 begin
489 --
490 l_business_group_id := pay_us_balance_view_pkg.get_context('BUSINESS_GROUP_ID');
491 --
492 begin
493 SELECT creator_id
494 INTO l_defined_balance_id
495 FROM ff_user_entities
496 WHERE user_entity_name = translate(p_balance_name||'_'||p_dimension_suffix,' ','_')
497 AND (legislation_code = 'US'
498 OR business_group_id = l_business_group_id);
499 --
500 return l_defined_balance_id;
501 exception
502 when others then
503 -- BHOMAN - added this exception to fix issues with testing
504 -- we must revisit this!!
505 pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
506 pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
507 pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
508 pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
509 translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
510 local_error( 'get_defined_balance', '1' );
511 end;
512 return l_defined_balance_id;
513 end;
514 --
515 --
516 ------------------------------------------------------------------------------
517 -- This ensures that the assignment is on a payroll on the effective date,
518 -- if not a valid date is found. If no valid date can be found an error is
519 -- raised.
520 ------------------------------------------------------------------------------
521 function get_virtual_date (p_assignment_id number,
522 p_virtual_date date,
523 p_balance_time varchar2,
524 p_asg_vdate_cursor number) return date is
525 l_dummy varchar2(1);
526 l_virtual_date date;
527 l_virtual_date2 date;
528 l_res_date date;
529 begin
530 begin
531 --
532 -- Is the assignment on a payroll.
533 --
534 --hr_utility.set_location('bh_taxbal.get_virtual_date', 10);
535 select ''
536 into l_dummy
537 from per_assignments_f paf
538 where paf.assignment_id = p_assignment_id
539 and p_virtual_date between paf.effective_start_date
540 and paf.effective_end_date
541 and paf.payroll_id is not null;
542
543 --
544 --hr_utility.set_location('bh_taxbal.get_virtual_date', 20);
545 return p_virtual_date;
546 exception
547 when no_data_found then
548 --
549 -- Find a valid date for the assignment.
550 --
551 declare
552 l_rows number;
553 begin
554 --hr_utility.set_location('bh_taxbal.get_virtual_date', 30);
555 l_rows := run_asg_vdate_cursor( p_asg_vdate_cursor,
556 p_assignment_id,
557 p_virtual_date,
558 p_virtual_date,
559 l_virtual_date );
560 if l_rows > 0 then
561 --hr_utility.set_location( 'bh_taxbal.get_virtual_date', 40);
562 --
563 select max(ppf.effective_end_date)
564 into l_virtual_date2
565 from per_assignments_f paf,
566 pay_payrolls_f ppf
567 where paf.assignment_id = p_assignment_id
568 and paf.payroll_id = ppf.payroll_id
569 and ppf.effective_end_date between
570 trunc(p_virtual_date, p_balance_time)
571 and p_virtual_date;
572 --
573 -- Now work out which date is needed
574 --
575 if l_virtual_date is null then
576 if l_virtual_date2 is null then
577 --hr_utility.set_location( 'bh_taxbal.get_virtual_date', 60);
578 local_error('get_virtual_date', 2);
579 else
580 --hr_utility.set_location( 'bh_taxbal.get_virtual_date', 70);
581 l_res_date := l_virtual_date2;
582 end if;
583 else
584 if l_virtual_date2 is null then
585 --hr_utility.set_location( 'bh_taxbal.get_virtual_date', 80);
586 l_res_date := l_virtual_date;
587 else
588 --hr_utility.set_location( 'bh_taxbal.get_virtual_date', 90);
589 l_res_date := least(l_virtual_date, l_virtual_date2);
590 end if;
591 end if;
592 --
593 else
594 --hr_utility.set_location( 'bh_taxbal.get_virtual_date', 50);
595 local_error('get_virtual_date', 1);
596 end if;
597 --
598 end;
599 --
600 return l_res_date;
601 end;
602 end;
603 --
604 --
605 ------------------------------------------------------------------------------
606 -- Get the balance value of a group level balance given the assignment id.
607 ------------------------------------------------------------------------------
608 function get_grp_asg_value (p_assignment_id number,
609 p_virtual_date date,
610 p_balance_name varchar2,
611 p_database_suffix varchar2,
612 p_gre_id number,
613 p_jurisdiction_code varchar2)
614 return number is
615 --
616 -- Cursors for getting assignments.
617 --
618 cursor asg_cur0( c_tax_unit_id in number,
619 c_date_earned in date,
620 c_date2_earned in date )
621 is
622 select distinct PAA.assignment_id
623 from pay_assignment_actions PAA,
624 pay_payroll_actions PPA
625 where PAA.tax_unit_id = c_tax_unit_id
626 and PPA.payroll_action_id = PAA.payroll_action_id
627 and PPA.effective_date >= trunc(c_date_earned,'Q')
628 and PPA.effective_date <= c_date2_earned
629 and PPA.action_type in ('R','Q','I','B','V');
630 --
631 cursor asg_cur1( c_tax_unit_id in number,
632 c_date_earned in date,
633 c_date2_earned in date )
634 is
635 select distinct PAA.assignment_id
636 from pay_assignment_actions PAA,
637 pay_payroll_actions PPA
638 where PAA.tax_unit_id = c_tax_unit_id
639 and PPA.payroll_action_id = PAA.payroll_action_id
640 and PPA.effective_date >= trunc(c_date_earned,'Y')
641 and PPA.effective_date <= c_date2_earned
642 and PPA.action_type in ('R','Q','I','B','V');
643 --
644 cursor asg_cur2( c_tax_unit_id in number,
645 c_balance_type_id in number,
646 c_jurisdiction_code in varchar2,
647 c_date_earned in date,
648 c_date2_earned in date )
649 is
650 select distinct PAR.assignment_id
651 from pay_balance_types PBT,
652 pay_us_asg_reporting PAR
653 where PAR.tax_unit_id = c_tax_unit_id
654 and PBT.balance_type_id = c_balance_type_id
655 and PBT.jurisdiction_level <> 0
656 and substr(PAR.jurisdiction_code, 1, PBT.jurisdiction_level) =
657 substr(c_jurisdiction_code, 1, PBT.jurisdiction_level)
658 and exists
659 (select 1
660 from pay_payroll_actions PPA,
661 pay_assignment_actions PAA
662 where PAA.assignment_id = PAR.assignment_id
663 and PAA.tax_unit_id = PAR.tax_unit_id
664 and PPA.payroll_action_id = PAA.payroll_action_id
665 and PPA.effective_date >= trunc(c_date_earned,'Y')
666 and PPA.effective_date <= c_date2_earned
667 and PPA.action_type in ('R','Q','I','B','V'));
668 --
669 l_dummy varchar2(5);
670 l_lat_balances boolean;
671 l_asg_data_suffix varchar2(80);
672 l_asg_data_cursor number;
673 l_asg_vdate_cursor number;
674 l_asg_balance_time varchar2(10);
675 l_asg_jd_required boolean;
676 l_grp_lat_exist boolean;
677 l_defined_balance_id number;
678 l_asg_id number;
679 l_balance_value number;
680 l_virtual_date date;
681 l_balance_type_id number;
682 begin
683 --
684 pay_us_balance_view_pkg.debug_msg( '===========================================');
685 pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
686 pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687 pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688 pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689 pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
690 pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
691 pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
692 --
693 l_balance_value := 0;
694 --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 10);
695 --
696 -- Get the assignment level version.
697 --
698 get_asg_for_grp_lvl(p_database_suffix,
699 l_asg_data_suffix,
700 l_asg_data_cursor,
701 l_asg_jd_required,
702 l_asg_vdate_cursor,
703 l_asg_balance_time,
704 l_grp_lat_exist);
705 --
706 --
707 if l_grp_lat_exist then
708 pay_us_balance_view_pkg.debug_msg(' l_grp_lat_exist TRUE');
709 pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
710 || l_asg_data_suffix);
711 pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
712 || l_asg_data_cursor);
713 pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
714 || to_char(l_asg_vdate_cursor));
715 pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
716 || l_asg_balance_time);
717 if l_asg_jd_required = TRUE then
718 pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: TRUE');
719 else
720 pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: FALSE');
721 end if;
722 --
723 -- Are there latest balances available.
724 --
725 --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 20);
726 l_defined_balance_id := get_defined_balance(p_balance_name,
727 l_asg_data_suffix);
728 pay_us_balance_view_pkg.debug_msg(' got defbalid: '
729 || l_defined_balance_id
730 || ' from balname: '
731 || p_balance_name
732 || ' and asg data suffix: '
733 || l_asg_data_suffix);
734 pay_us_balance_view_pkg.debug_msg(' looking for latest bals ');
735 --
736 begin
737 select ''
738 into l_dummy
739 from dual
740 where exists (
741 select ''
742 from pay_payroll_actions ppa,
743 pay_assignment_actions paa,
744 pay_assignment_latest_balances palb
745 where palb.assignment_id = p_assignment_id
746 and palb.defined_balance_id = l_defined_balance_id
747 and palb.assignment_action_id =
748 paa.assignment_action_id
749 and paa.payroll_action_id = ppa.payroll_action_id
750 and p_virtual_date >= ppa.effective_date
751 and ppa.action_type in ('R','Q','I','B','V'));
752 --
753 l_lat_balances := TRUE;
754 --
755 exception
756 when no_data_found then
757 l_lat_balances := FALSE;
758 pay_us_balance_view_pkg.debug_msg(' latest balances found');
759 end;
760 --
761 if (l_lat_balances = TRUE) then
762 --
763 -- OK, we can sum the values of the assignment balances to get the
764 -- group balance.
765 --
766 pay_us_balance_view_pkg.debug_msg(' summing latest balance values');
767 --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 30);
768 begin
769 --
770 -- Does the cursor require the jurisdiction code. Hence balance
771 -- type.
772 --
773 if l_asg_jd_required then
774 select balance_type_id
775 into l_balance_type_id
776 from pay_defined_balances
777 where defined_balance_id = l_defined_balance_id;
778 pay_us_balance_view_pkg.debug_msg(' since jd req, retrieved bal type id of: '
779 || l_balance_type_id);
780 --
781 end if;
782
783 if l_asg_data_cursor = ASG_CURSOR0 then
784 open asg_cur0( p_gre_id, p_virtual_date, p_virtual_date );
785 elsif l_asg_data_cursor = ASG_CURSOR1 then
786 open asg_cur1( p_gre_id, p_virtual_date, p_virtual_date );
787 elsif l_asg_data_cursor = ASG_CURSOR2 then
788 open asg_cur2( p_gre_id, l_balance_type_id, p_jurisdiction_code,
789 p_virtual_date, p_virtual_date );
790 else
791 local_error( 'get_grp_asg_value', 1 );
792 return null;
793 end if;
794
795 --
796 -- Loop through all the contributing assignments, go get there
797 -- balance value and add onto the running total.
798 --
799 loop
800 pay_us_balance_view_pkg.debug_msg(' loop thru latest balances cursor');
801 if l_asg_data_cursor = ASG_CURSOR0 then
802 fetch asg_cur0 into l_asg_id;
803 exit when asg_cur0%notfound;
804 elsif l_asg_data_cursor = ASG_CURSOR1 then
805 fetch asg_cur1 into l_asg_id;
806 exit when asg_cur1%notfound;
807 elsif l_asg_data_cursor = ASG_CURSOR2 then
808 fetch asg_cur2 into l_asg_id;
809 exit when asg_cur2%notfound;
810 end if;
811
812 --hr_utility.set_location( 'bh_taxbal.get_grp_asg_value', 40);
813 l_virtual_date := get_virtual_date(l_asg_id, p_virtual_date,
814 l_asg_balance_time,
815 l_asg_vdate_cursor);
816 pay_us_balance_view_pkg.debug_msg(' got l_virtual_date: '
817 || fnd_date.date_to_canonical(p_virtual_date));
818 --
819 -- Dont cache on this get_value call because assignment_id
820 -- is changing.
821 --
822 l_balance_value := l_balance_value +
823 -- BHOMAN - fixed param order ....
824 pay_us_balance_view_pkg.get_value(l_asg_id,
825 l_defined_balance_id,
826 l_virtual_date, 1);
827 pay_us_balance_view_pkg.debug_msg(' running sum l_balance_value: '
828 || l_balance_value);
829 end loop;
830
831 --
832 if l_asg_data_cursor = ASG_CURSOR0 then
833 close asg_cur0;
834 elsif l_asg_data_cursor = ASG_CURSOR1 then
835 close asg_cur1;
836 elsif l_asg_data_cursor = ASG_CURSOR2 then
837 close asg_cur2;
838 end if;
839 exception
840 when others then
841 if l_asg_data_cursor = ASG_CURSOR0 and asg_cur0%isopen then
842 close asg_cur0;
843 elsif l_asg_data_cursor = ASG_CURSOR1 and asg_cur1%isopen then
844 close asg_cur1;
845 elsif l_asg_data_cursor = ASG_CURSOR2 and asg_cur2%isopen then
846 close asg_cur2;
847 end if;
848 raise;
849 end;
850 else
851 --
852 -- No latest balances available. Run the route.
853 --
854 pay_us_balance_view_pkg.debug_msg(' no latest bals, running route');
855 --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 50);
856 l_defined_balance_id := get_defined_balance(p_balance_name,
857 p_database_suffix);
858 pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
859 l_balance_value := pay_us_balance_view_pkg.get_value( p_assignment_id,
860 l_defined_balance_id,
861 p_virtual_date
862 );
863 end if;
864 else
865 --
866 -- Can not sum the assignment level balances, thus run group
867 -- level route.
868 pay_us_balance_view_pkg.debug_msg(' Can not sum the assignment level balances, running route');
869 --
870 --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 60);
871 l_defined_balance_id := get_defined_balance(p_balance_name,
872 p_database_suffix);
873 pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
874 l_balance_value := pay_us_balance_view_pkg.get_value
875 (
876 p_assignment_id,
877 l_defined_balance_id,
878 p_virtual_date
879 );
880 end if;
881 --
882 --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 70);
883 pay_us_balance_view_pkg.debug_msg(' *** get_grp_asg_value returning l_balance_value: '
884 || l_balance_value);
885 return l_balance_value;
886 --
887 end;
888 --
889 ------------------------------------------------------------------------------
890 -- Get the balance value of a group level balance given the assignment action
891 -- id.
892 ------------------------------------------------------------------------------
893 function get_grp_act_value (p_assignment_action_id number,
894 p_virtual_date date,
895 p_balance_name varchar2,
896 p_database_suffix varchar2,
897 p_gre_id number)
898 return number is
899 l_defined_balance_id number;
900 l_balance_value number;
901 begin
902 --hr_utility.set_location('bh_taxbal.get_grp_act_value', 10);
903 --
904 pay_us_balance_view_pkg.debug_msg( '===========================================');
905 pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
906 pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907 pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908 pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
909 pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
910 pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
911 --
912 l_balance_value := 0;
913 --
914 l_defined_balance_id := get_defined_balance(p_balance_name,
915 p_database_suffix);
916 l_balance_value := pay_us_balance_view_pkg.get_value (
917 p_assignment_action_id,
918 l_defined_balance_id
919 );
920 --
921 --hr_utility.set_location('bh_taxbal.get_grp_act_value', 20);
922 return l_balance_value;
923 --
924 end;
925 --
926 -------------------------------------------------------------------------------
927 -- Get the value of the group level balance.
928 -------------------------------------------------------------------------------
929 function get_grp_value (p_assignment_id number,
930 p_virtual_date date,
931 p_balance_name varchar2,
932 p_database_suffix varchar2,
933 p_gre_id number,
934 p_jurisdiction_code varchar2,
935 p_assignment_action_id number default null)
936 return number is
937 begin
938 --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
939 pay_us_balance_view_pkg.debug_msg( '===========================================');
940 pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
941 pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942 pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943 pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944 pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945 pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
946 pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
947 pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
948
949 if p_assignment_action_id is null then
950 --hr_utility.set_location('bh_taxbal.get_grp_value', 20);
951 return get_grp_asg_value(p_assignment_id,
952 p_virtual_date,
953 p_balance_name,
954 p_database_suffix,
955 p_gre_id,
956 p_jurisdiction_code);
957 else
958 --hr_utility.set_location('bh_taxbal.get_grp_value', 30);
959 return get_grp_act_value(p_assignment_action_id,
960 p_virtual_date,
961 p_balance_name,
962 p_database_suffix,
963 p_gre_id);
964 end if;
965 end;
966 -------------------------------------------------------------------------------
967 --
968 -- Wrapper around the core bal user exit
969 --
970 -------------------------------------------------------------------------------
971 FUNCTION call_balance_user_exit
972 (p_balance_name varchar2,
973 p_dimension_suffix varchar2,
974 p_assignment_action_id number DEFAULT NULL,
975 p_assignment_id number DEFAULT NULL,
976 p_virtual_date date DEFAULT NULL,
977 p_asg_type varchar2 DEFAULT NULL,
978 p_gre_id number DEFAULT NULL,
979 p_jurisdiction_code varchar2 DEFAULT NULL)
980 RETURN number IS
981 --
982 l_defined_balance_id number;
983 l_balance_type_id number;
984 l_dimension_id number;
985 l_session VARCHAR2(15);
986 --
987 BEGIN
988 --
989 pay_us_balance_view_pkg.debug_msg( '===========================================');
990 pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
991 pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992 pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993 pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994 pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995 pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996 pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
997 pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
998 pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
999
1000 --hr_utility.set_location('bh_taxbal.balance_name'||p_balance_name, 9);
1001
1002 --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 10);
1003 IF p_assignment_action_id IS NOT NULL THEN
1004 -- If group level balance, call the group level balance code.
1005 pay_us_balance_view_pkg.debug_msg(' assignment_action_id not NULL: '
1006 || p_assignment_action_id);
1007 if p_asg_type = 'GRE' then
1008 pay_us_balance_view_pkg.debug_msg(', p_asg_type GRE, calling get_grp_value');
1009 --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 20);
1010 return get_grp_value(p_assignment_id,
1011 p_virtual_date,
1012 p_balance_name,
1013 p_dimension_suffix,
1014 p_gre_id,
1015 p_jurisdiction_code,
1016 p_assignment_action_id
1017 );
1018 else
1019 pay_us_balance_view_pkg.debug_msg(' p_asg_type *not* GRE, calling get_defined_balance and get_value');
1020 l_defined_balance_id := get_defined_balance(p_balance_name,
1021 p_dimension_suffix);
1022 IF p_dimension_suffix not like '%PAY%' THEN
1023 --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 30);
1024 -- BHOMAN -- this is where pmadore's test case calls get_value
1025 -- if asg_type = PER, def_bal_id is 3167; if ASG then 3157,
1026 -- if GRE then doesn't call here
1027 -- assact_id is fathful to whatever we pass
1028 -- if TRUE then
1029 -- return p_assignment_action_id;
1030 -- end if;
1031 --
1032 --
1033 -- return pay_us_balance_view_pkg.get_value (l_defined_balance_id,
1034 -- p_assignment_action_id
1035 -- );
1036 pay_us_balance_view_pkg.debug_msg(' dimension not like PAY, calling get_value in default mode');
1037 return pay_us_balance_view_pkg.get_value (p_assignment_action_id,
1038 l_defined_balance_id
1039 );
1040 ELSE /* If payments dimension then must execute DB item 395029 */
1041 --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 40);
1042 pay_us_balance_view_pkg.debug_msg(' dimension *is* like PAY, calling get_value with *NO* caching');
1043 return pay_us_balance_view_pkg.get_value (
1044 p_assignment_action_id,
1045 l_defined_balance_id,
1046 0,
1047 1 );
1048 END IF;
1049 end if;
1050 --
1051 ELSE
1052 -- If group level balance, call the group level balance code.
1053 pay_us_balance_view_pkg.debug_msg(' assignment_action_id *is* NULL: ');
1054 if p_asg_type = 'GRE' then
1055 pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE, calling get_grp_value');
1056 --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 50);
1057 return get_grp_value(p_assignment_id,
1058 p_virtual_date,
1059 p_balance_name,
1060 p_dimension_suffix,
1061 p_gre_id,
1062 p_jurisdiction_code,
1063 null
1064 );
1065 else
1066 pay_us_balance_view_pkg.debug_msg(' p_asg_type is *not* GRE, calling get_defined_balance and get_value');
1067 l_defined_balance_id := get_defined_balance(p_balance_name,
1068 p_dimension_suffix);
1069 --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 60);
1070 return pay_us_balance_view_pkg.get_value (
1071 p_assignment_id,
1072 l_defined_balance_id,
1073 p_virtual_date
1074 );
1075 end if;
1076 END IF;
1077 --
1078 END call_balance_user_exit;
1079 --
1080 -------------------------------------------------------------------------------
1081 --
1082 -- Wrapper around the call_balance_user_exit - this wrapper sets
1083 -- tax_unit_id and/or jd context from parameters.
1084 --
1085 -------------------------------------------------------------------------------
1086 FUNCTION us_named_balance
1087 (p_balance_name varchar2,
1088 p_dimension_suffix varchar2,
1089 p_assignment_action_id number ,
1090 p_assignment_id number ,
1091 p_virtual_date date ,
1092 p_asg_type varchar2 ,
1093 p_gre_id number ,
1094 p_business_group_id number ,
1095 p_jurisdiction_code varchar2 )
1096 RETURN number IS
1097 l_balance number;
1098 BEGIN
1099 pay_us_balance_view_pkg.debug_msg( '===========================================');
1100 pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1101 pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102 pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103 pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104 pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105 pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106 pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107 pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108 pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1109 pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1110 --
1111 pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1112 IF p_jurisdiction_code IS NOT NULL THEN
1113 -- BHOMAN - review this twisted logic with LWTHOMPS:
1114 -- should I use 'SCHOOL%', or '%SCHOOL%', or shoud I just
1115 -- pass a tax type
1116 IF (p_balance_name like ('SCHOOL%') and
1117 length(p_jurisdiction_code) > 11) THEN
1118 pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',
1119 substr(p_jurisdiction_code,1,2)||
1120 '-'||substr(p_jurisdiction_code,13,5));
1121 ELSE
1122 pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
1123 END IF;
1124 END IF;
1125 --
1126 pay_us_balance_view_pkg.set_context('BUSINESS_GROUP_ID',
1127 NVL(p_business_group_id,-1) );
1128
1129 BEGIN
1130 l_balance := call_balance_user_exit (
1131 p_balance_name,
1132 p_dimension_suffix,
1133 p_assignment_action_id,
1134 p_assignment_id,
1135 p_virtual_date,
1136 p_asg_type,
1137 p_gre_id,
1138 p_jurisdiction_code);
1139 EXCEPTION
1140 when others then
1141 --
1142 pay_us_balance_view_pkg.debug_err(
1143 'us_named_balance: call_balance_user_exit raised exception');
1144 pay_us_balance_view_pkg.debug_err(
1145 ' RETURNING NULL.');
1146 return NULL;
1147 END run_asg_vdate_cursor;
1148 --
1149 pay_us_balance_view_pkg.debug_err(
1150 'us_named_balance: return balance: ' || l_balance);
1151 return l_balance;
1152 --
1153 END;
1154
1155 ----------------------------------------------------------------------------------------
1156 --
1157 --This function gets the pre-payments assignment_action_id for the run or quick pay
1158 --assignment_action_id. This is used in the payroll register report (PAYRPREG.rdf) when
1159 --reversal exists. Here check is done that for a assignment_action_id any reversal exists.
1160 --
1161 -----------------------------------------------------------------------------------------
1162 FUNCTION get_prepayment_aaid (p_assignment_action_id number DEFAULT NULL)
1163 RETURN number is
1164 cursor prepayment_aaid(param_aaid number) is
1165 select pai.locking_action_id
1166 from pay_assignment_actions paa2,
1167 pay_payroll_actions ppa2,
1168 pay_action_interlocks pai,
1169 pay_assignment_actions paa,
1170 pay_payroll_actions ppa
1171 where pai.locked_Action_id = param_aaid
1172 and pai.locking_action_id = pai.locking_action_id
1173 and pai.locking_action_id = paa.assignment_action_id
1174 and paa.payroll_Action_id = ppa.payroll_action_id
1175 and ppa.action_type in ('P','U')
1176 and pai.locked_action_id = paa2.assignment_action_id
1177 and paa2.payroll_Action_id = ppa2.payroll_Action_id
1178 and ppa2.action_type in ('R','Q')
1179 /*
1180 and exists ( select locked_action_id,locking_action_id
1181 from pay_action_interlocks paie,
1182 pay_assignment_Actions paae,
1183 pay_payroll_Actions ppae
1184 where paie.locked_action_id = pai2.locked_action_id
1185 and paie.locking_action_id = paae.assignment_action_id
1186 and paae.payroll_Action_id = ppae.payroll_Action_id
1187 and ppae.action_type = 'V' )
1188 */;
1189 temp_aaid number;
1190 begin
1191 open prepayment_aaid(p_assignment_action_id);
1192 fetch prepayment_aaid into temp_aaid;
1193 close prepayment_aaid;
1194 return(temp_aaid);
1195 end;
1196
1197 --------------------------------------------------------------------------------------
1198 --
1199 --This function adds up the amount of all the assignment_action_ids if a reversal exists.
1200 --We do not use the payments balance because it fails when a reversal exists. This function
1201 --is used for Earnings and Deductions.We use ASG_GRE_RUN because we want to add the amounts
1202 --for the run not taking into consideration if a reversal exists.
1203 --
1204 --------------------------------------------------------------------------------------
1205 FUNCTION reversal_exists_check(p_balance_name varchar2,
1206 p_assignment_action_id number DEFAULT NULL,
1207 p_assignment_id number DEFAULT NULL,
1208 p_gre_id number DEFAULT NULL,
1209 p_run_assignment_action_id number DEFAULT NULL,
1210 p_business_group_id number DEFAULT NULL)
1211 RETURN number is
1212 cursor c_run_type_id is
1213 select prt.run_type_id
1214 from pay_run_types_f prt
1215 where prt.shortname = 'SEPCHECK'
1216 and prt.legislation_code = 'US';
1217
1218 cursor c_run_action_info
1219 (cp_assignment_action_id in number) is
1220 select run_type_id,
1221 assignment_id
1222 from pay_assignment_actions paa
1223 where paa.assignment_action_id = cp_assignment_action_id;
1224
1225 cursor c_run_actions (cp_pre_pay_action_id in number,
1226 cp_sep_check_run_type_id in number,
1227 cp_run_assignment_id in number) is
1228 select pai.locked_action_id
1229 from pay_payroll_actions ppa,
1230 pay_assignment_actions paa,
1231 pay_action_interlocks pai
1232 where pai.locking_action_id = cp_pre_pay_action_id
1233 and pai.locked_action_id = paa.assignment_action_id
1234 and paa.assignment_id = cp_run_assignment_id
1235 and paa.payroll_action_id = ppa.payroll_action_id
1236 and ppa.action_type in ('R','Q')
1237 /* The condition below is to take care of Payroll Processes
1238 which have been run with Umbrella process and before that.
1239 Run Type Id will be not null in case of umbrella process
1240 and Source action ID will be not null for Child Actions
1241 */
1242 and ((paa.source_action_id is not null and ppa.run_type_id is not null
1243 and paa.run_type_id <> cp_sep_check_run_type_id) or
1244 (paa.source_action_id is null and ppa.run_type_id is null));
1245
1246 balance_aaid number := 0;
1247 temp_aaid number;
1248
1249 ln_run_type_id NUMBER;
1250 ln_run_action_run_type_id NUMBER;
1251 ln_run_assignment_id number;
1252 begin
1253 open c_run_type_id;
1254 fetch c_run_type_id into ln_run_type_id;
1255 close c_run_type_id;
1256
1257 open c_run_action_info(p_run_assignment_action_id);
1258 fetch c_run_action_info into
1259 ln_run_action_run_type_id,
1260 ln_run_assignment_id;
1261 close c_run_action_info;
1262
1263 if ln_run_type_id = ln_run_action_run_type_id then
1264 balance_aaid := us_named_balance(
1265 p_balance_name,
1266 'ASG_GRE_RUN',
1267 p_run_assignment_action_id,
1268 null,
1269 null,
1270 null,
1271 p_gre_id,
1272 p_business_group_id,
1273 null);
1274 else
1275
1276 open c_run_actions(p_assignment_action_id,
1277 ln_run_type_id,
1278 ln_run_assignment_id);
1279 loop
1280 fetch c_run_actions into temp_aaid;
1281 exit when c_run_actions%notfound;
1282 balance_aaid := balance_aaid +
1283 us_named_balance(
1284 p_balance_name,
1285 'ASG_GRE_RUN',
1286 temp_aaid,
1287 null,
1288 null,
1289 null,
1290 p_gre_id,
1291 p_business_group_id,
1292 null);
1293 end loop;
1294 close c_run_actions;
1295 end if;
1296 return(balance_aaid);
1297 end;
1298 -------------------------------------------------------------------------------
1299 --
1300 --This function adds up the amount of all the assignment_action_ids if a reversal exists.
1301 --We do not use the payments balance because it fails when a reversal exists. This function
1302 --is used for Taxes.We use ASG_GRE_RUN because we want to add the amounts for the run not
1303 --taking into consideration if a reversal exists.
1304 --
1305 ------------------------------------------------------------------------------------
1306 FUNCTION reversal_exists_check_tax(p_tax_balance_category in varchar2,
1307 p_tax_type in varchar2,
1308 p_ee_or_er in varchar2,
1309 p_time_type in varchar2,
1310 p_gre_id_context in number,
1311 p_jd_context in varchar2 default null,
1312 p_assignment_action_id in number default null,
1313 p_assignment_id in number default null,
1314 p_virtual_date in date default null,
1315 p_payroll_action_id in number,
1316 p_run_assignment_action_id number DEFAULT NULL)
1317 RETURN number is
1318
1319 cursor c_run_type_id is
1320 select prt.run_type_id
1321 from pay_run_types_f prt
1322 where prt.shortname = 'SEPCHECK'
1323 and prt.legislation_code = 'US';
1324
1325 cursor c_run_action_info
1326 (cp_assignment_action_id in number) is
1327 select run_type_id,
1328 assignment_id
1329 from pay_assignment_actions paa
1330 where paa.assignment_action_id = cp_assignment_action_id;
1331
1332 cursor c_run_actions (cp_pre_pay_action_id in number,
1333 cp_sep_check_run_type_id in number,
1334 cp_run_assignment_id in number) is
1335 select pai.locked_action_id
1336 from pay_payroll_actions ppa,
1337 pay_assignment_actions paa,
1338 pay_action_interlocks pai
1339 where pai.locking_action_id = cp_pre_pay_action_id
1340 and pai.locked_action_id = paa.assignment_action_id
1341 and paa.assignment_id = cp_run_assignment_id
1342 and paa.payroll_action_id = ppa.payroll_action_id
1343 and ppa.action_type in ('R','Q')
1344 /* The condition below is to take care of Payroll Processes
1345 which have been run with Umbrella process and before that.
1346 Run Type Id will be not null in case of umbrella process
1347 and Source action ID will be not null for Child Actions
1348 */
1349 and ((paa.source_action_id is not null and ppa.run_type_id is not null
1350 and paa.run_type_id <> cp_sep_check_run_type_id) or
1351 (paa.source_action_id is null and ppa.run_type_id is null));
1352
1353 balance_aaid number := 0;
1354 temp_aaid number;
1355
1356 ln_run_type_id NUMBER;
1357 ln_run_action_run_type_id NUMBER;
1358 ln_run_assignment_id number;
1359
1360 begin
1361 open c_run_type_id;
1362 fetch c_run_type_id into ln_run_type_id;
1363 close c_run_type_id;
1364
1365 open c_run_action_info(p_run_assignment_action_id);
1366 fetch c_run_action_info into
1367 ln_run_action_run_type_id,
1368 ln_run_assignment_id;
1369 close c_run_action_info;
1370
1371 if ln_run_type_id = ln_run_action_run_type_id then
1372 balance_aaid := us_tax_balance(p_tax_balance_category,
1373 p_tax_type,
1374 p_ee_or_er,
1375 'RUN',
1376 'ASG',
1377 p_gre_id_context,
1378 p_jd_context,
1379 p_run_assignment_action_id,
1380 p_assignment_id,
1381 p_virtual_date,
1382 NULL );
1383 else
1384
1385 open c_run_actions(p_assignment_action_id,
1386 ln_run_type_id,
1387 ln_run_assignment_id);
1388 loop
1389 fetch c_run_actions into temp_aaid;
1390 exit when c_run_actions%notfound;
1391 balance_aaid := balance_aaid +
1392 us_tax_balance(p_tax_balance_category,
1393 p_tax_type,
1394 p_ee_or_er,
1395 'RUN',
1396 'ASG',
1397 p_gre_id_context,
1398 p_jd_context,
1399 temp_aaid,
1400 p_assignment_id,
1401 p_virtual_date,
1402 NULL );
1403 end loop;
1404 close c_run_actions;
1405 end if;
1406
1407 return(balance_aaid);
1408 end;
1409 -------------------------------------------------------------------------------
1410 --
1411 -- us_named_balance_vm
1412 -- A "view mode" version of us_named_balance with no param for asg_type.
1413 -- Looks in pkg context set by set_view_mode. Also, if p_dimension_suffix
1414 -- like '%PYDATE%' or '%MONTH%', we check whether bal pkg BOOLEAN flag
1415 -- CalcAllTimeTypes is set to TRUE. If CalcAllTimeTypes is TRUE, call
1416 -- us_named_balance; but if CalcAllTimeTypes is FALSE, return NULL for
1417 -- PYDATE and MONTH bals.
1418 -- We always call us_named_balance for other dimensions.
1419 --
1420 FUNCTION us_named_balance_vm
1421 (p_balance_name varchar2,
1422 p_dimension_suffix varchar2,
1423 p_assignment_action_id number ,
1424 p_assignment_id number ,
1425 p_virtual_date date ,
1426 p_gre_id number ,
1427 p_business_group_id number ,
1428 p_jurisdiction_code varchar2 )
1429 RETURN number IS
1430
1431 CURSOR C_GET_MASTER_AAID (cp_prepay_action_id in number,
1432 cp_assignment_id in number) is
1433 select max(paa.assignment_action_id)
1434 from pay_assignment_actions paa, -- assignment_action for master payroll run
1435 pay_action_interlocks pai
1436 where pai.locking_action_id = cp_prepay_action_id
1437 and pai.locked_action_id = paa.assignment_action_id
1438 and paa.assignment_id = cp_assignment_id
1439 and paa.source_action_id is null -- master assignment_action
1440 group by assignment_id;
1441
1442 l_asg_type VARCHAR(32);
1443 l_calc_all NUMBER;
1444 l_dimension_suffix VARCHAR2(40);
1445 l_assignment_action_id NUMBER;
1446 pp_aaid NUMBER;
1447 l_sep_check VARCHAR2(1) := 'N';
1448 l_pre_pay_aaid NUMBER;
1449 l_assignment_id number;
1450
1451 BEGIN
1452 l_dimension_suffix := p_dimension_suffix;
1453 l_assignment_action_id := p_assignment_action_id;
1454
1455 --
1456 -- check time part of dimension, and calc_all_timetype_flag
1457 --
1458 ----------------
1459 if pay_us_balance_view_pkg.get_session_var('CURRENT') = 'FALSE' and
1460 p_dimension_suffix like '%CURRENT%' THEN
1461 return NULL;
1462 end if;
1463
1464 if pay_us_balance_view_pkg.get_session_var('RUN') = 'FALSE' and
1465 p_dimension_suffix like '%RUN%' THEN
1466 return NULL;
1467 end if;
1468
1469 if pay_us_balance_view_pkg.get_session_var('PYDATE') = 'FALSE' and
1470 p_dimension_suffix like '%PYDATE%' THEN
1471 return NULL;
1472 end if;
1473
1474 if pay_us_balance_view_pkg.get_session_var('PTD') = 'FALSE' and
1475 p_dimension_suffix like '%PTD%' THEN
1476 return NULL;
1477 end if;
1478
1479 if pay_us_balance_view_pkg.get_session_var('MONTH') = 'FALSE' and
1480 p_dimension_suffix like '%MONTH%' THEN
1481 return NULL;
1482 end if;
1483
1484 if pay_us_balance_view_pkg.get_session_var('QTD') = 'FALSE' and
1485 p_dimension_suffix like '%QTD%' THEN
1486 return NULL;
1487 end if;
1488
1489 if pay_us_balance_view_pkg.get_session_var('YTD') = 'FALSE' and
1490 p_dimension_suffix like '%YTD%' THEN
1491 return NULL;
1492 end if;
1493
1494 ---------------
1495 if p_dimension_suffix like ('%MONTH%') OR
1496 p_dimension_suffix like ('%PYDATE%') then
1497 l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1498 if l_calc_all = 0 then
1499 pay_us_balance_view_pkg.debug_msg(
1500 'us_named_balance_vm: dimension '
1501 || p_dimension_suffix
1502 || ' disabled, returning NULL');
1503 return NULL;
1504 end if;
1505 -- The 'CURRENT' Dimension is for the current payment method amount.
1506 -- This is needed for checks, deposit advice, and the payroll register
1507 elsif l_dimension_suffix = 'CURRENT' THEN
1508 if pay_us_taxbal_view_pkg.payments_balance_required(l_assignment_action_id) THEN
1509
1510 l_assignment_action_id := p_assignment_action_id;
1511
1512 BEGIN
1513
1514 SELECT DECODE(prt.shortname,'SEPCHECK','Y','N'),
1515 paa.assignment_id
1516 INTO l_sep_check,
1517 l_assignment_id
1518 FROM pay_assignment_actions paa
1519 ,pay_run_types_f prt
1520 WHERE paa.assignment_action_id = l_assignment_action_id
1521 AND prt.run_type_id = paa.run_type_id
1522 AND prt.legislation_code = 'US';
1523
1524 EXCEPTION
1525
1526 WHEN NO_DATA_FOUND THEN
1527 l_sep_check := 'N';
1528 END;
1529
1530 IF l_sep_check <> 'Y' THEN
1531
1532 select paa.assignment_action_id
1533 into l_pre_pay_aaid
1534 from pay_action_interlocks pai,
1535 pay_assignment_actions paa,
1536 pay_payroll_actions ppa
1537 where pai.locked_action_id = l_assignment_action_id
1538 and paa.assignment_action_id = pai.locking_action_id
1539 and paa.source_action_id is NULL -- master pre-payment action.
1540 and ppa.payroll_action_id = paa.payroll_action_id
1541 and ppa.action_type in ('P', 'U');
1542
1543 OPEN C_GET_MASTER_AAID (l_pre_pay_aaid,
1544 l_assignment_id);
1545
1546 FETCH C_GET_MASTER_AAID
1547 INTO l_assignment_action_id;
1548
1549 CLOSE C_GET_MASTER_AAID;
1550
1551 l_dimension_suffix := 'ASG_PAYMENTS';
1552 else -- l_sep_check <> 'Y'
1553 l_dimension_suffix := 'ASG_GRE_RUN';
1554 END IF;
1555 else -- payment_balance_required
1556 l_dimension_suffix := 'ASG_GRE_RUN';
1557 end if; -- payment_balance_required
1558
1559 end if; -- dimension suffix
1560 --
1561 -- get asg_type/view_mode
1562 --
1563 l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1564 pay_us_balance_view_pkg.debug_msg(
1565 'us_named_balance_vm called, view_mode: ' || l_asg_type);
1566 --
1567 -- if GRE mode, set DATE_EARNED context from p_virutal_date
1568 -- TODO - verify non-null p_gre_id and p_virtual_date??
1569 --
1570 if l_asg_type = 'GRE' then
1571 pay_us_balance_view_pkg.set_context('DATE_EARNED',
1572 fnd_date.date_to_canonical(p_virtual_date));
1573 if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1574 then
1575 pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1576 fnd_date.date_to_canonical(p_virtual_date));
1577 end if;
1578 pay_us_balance_view_pkg.debug_msg(
1579 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1580 || fnd_date.date_to_canonical(p_virtual_date));
1581 end if;
1582 --
1583 -- now we can finally call the balance!
1584 --
1585 RETURN us_named_balance(p_balance_name,
1586 l_dimension_suffix,
1587 l_assignment_action_id,
1588 p_assignment_id,
1589 p_virtual_date,
1590 l_asg_type,
1591 p_gre_id,
1592 p_business_group_id,
1593 p_jurisdiction_code);
1594 END;
1595 -------------------------------------------------------------------------------
1596 --
1597 -- us_tax_balance_vm
1598 -- A "view mode" version of us_tax_balance with no param for asg_type.
1599 -- Looks in pkg context set by set_view_mode. Also, if p_time_type in
1600 -- ('PYDATE', 'MONTH'), we check whether bal pkg BOOLEAN flag "CalcAllTimeTypes"
1601 -- is set to TRUE. If CalcAllTimeTypes is TRUE, call us_tax_balance; but
1602 -- if CalcAllTimeTypes is FALSE, just return NULL for PYDATE and MONTH.
1603 -- We always call us_tax_balance for other time types.
1604 --
1605 -------------------------------------------------------------------------------
1606 FUNCTION us_tax_balance_vm (p_tax_balance_category in varchar2,
1607 p_tax_type in varchar2,
1608 p_ee_or_er in varchar2,
1609 p_time_type in varchar2,
1610 p_gre_id_context in number,
1611 p_jd_context in varchar2 ,
1612 p_assignment_action_id in number ,
1613 p_assignment_id in number ,
1614 p_virtual_date in date ,
1615 p_payroll_action_id in number)
1616 RETURN number IS
1617
1618 CURSOR C_GET_MASTER_AAID (cp_prepay_action_id in number,
1619 cp_assignment_id in number) is
1620 select max(paa.assignment_action_id)
1621 from pay_assignment_actions paa, -- assignment_action for master payroll run
1622 pay_action_interlocks pai
1623 where pai.locking_action_id = cp_prepay_action_id
1624 and pai.locked_action_id = paa.assignment_action_id
1625 and paa.assignment_id = cp_assignment_id
1626 and paa.source_action_id is null -- master assignment_action
1627 group by assignment_id;
1628
1629 l_asg_type VARCHAR(32);
1630 l_calc_all NUMBER;
1631 l_time_type VARCHAR2(32);
1632 l_count_runs NUMBER;
1633 l_assignment_action_id NUMBER;
1634 pp_aaid NUMBER;
1635 l_sep_check VARCHAR2(1) := 'N';
1636 l_pre_pay_aaid NUMBER;
1637 l_assignment_id number;
1638
1639 BEGIN
1640 l_time_type := p_time_type;
1641 l_assignment_action_id := p_assignment_action_id;
1642
1643 --
1644 -- check time_type, and CalcAllTimeTypes flag
1645 --
1646 --------------------
1647 if pay_us_balance_view_pkg.get_session_var('CURRENT') = 'FALSE' and
1648 p_time_type like '%CURRENT%' THEN
1649 return NULL;
1650 end if;
1651
1652 if pay_us_balance_view_pkg.get_session_var('RUN') = 'FALSE' and
1653 p_time_type like '%RUN%' THEN
1654 return NULL;
1655 end if;
1656
1657 if pay_us_balance_view_pkg.get_session_var('PYDATE') = 'FALSE' and
1658 p_time_type like '%PYDATE%' THEN
1659 return NULL;
1660 end if;
1661
1662 if pay_us_balance_view_pkg.get_session_var('PTD') = 'FALSE' and
1663 p_time_type like '%PTD%' THEN
1664 return NULL;
1665 end if;
1666
1667 if pay_us_balance_view_pkg.get_session_var('MONTH') = 'FALSE' and
1668 p_time_type like '%MONTH%' THEN
1669 return NULL;
1670 end if;
1671
1672 if pay_us_balance_view_pkg.get_session_var('QTD') = 'FALSE' and
1673 p_time_type like '%QTD%' THEN
1674 return NULL;
1675 end if;
1676
1677 if pay_us_balance_view_pkg.get_session_var('YTD') = 'FALSE' and
1678 p_time_type like '%YTD%' THEN
1679 return NULL;
1680 end if;
1681
1682 ---------------------
1683 if p_time_type in ('MONTH', 'PYDATE') then
1684 l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1685 if l_calc_all = 0 then
1686 pay_us_balance_view_pkg.debug_msg(
1687 'us_tax_balance_vm, timetype '
1688 || p_time_type
1689 || ' disabled, returning NULL');
1690 return NULL;
1691 end if;
1692 -- The 'CURRENT' Dimension is for the current payment method amount.
1693 -- This is needed for checks, deposit advice, and the payroll register
1694 elsif p_time_type = 'CURRENT' THEN
1695 if pay_us_taxbal_view_pkg.payments_balance_required(l_assignment_action_id) THEN
1696
1697 l_assignment_action_id := p_assignment_action_id;
1698
1699 BEGIN
1700
1701 SELECT DECODE(prt.shortname,'SEPCHECK','Y','N'),
1702 paa.assignment_id
1703 INTO l_sep_check,
1704 l_assignment_id
1705 FROM pay_assignment_actions paa
1706 ,pay_run_types_f prt
1707 WHERE paa.assignment_action_id = l_assignment_action_id
1708 AND prt.run_type_id = paa.run_type_id
1709 AND prt.legislation_code = 'US';
1710
1711 EXCEPTION
1712
1713 WHEN NO_DATA_FOUND THEN
1714 l_sep_check := 'N';
1715 END;
1716
1717 IF l_sep_check <> 'Y' THEN
1718
1719 select paa.assignment_action_id
1720 into l_pre_pay_aaid
1721 from pay_action_interlocks pai,
1722 pay_assignment_actions paa,
1723 pay_payroll_actions ppa
1724 where pai.locked_action_id = l_assignment_action_id
1725 and paa.assignment_action_id = pai.locking_action_id
1726 and paa.source_action_id is NULL -- master pre-payment action.
1727 and ppa.payroll_action_id = paa.payroll_action_id
1728 and ppa.action_type in ('P', 'U');
1729
1730 OPEN C_GET_MASTER_AAID (l_pre_pay_aaid,
1731 l_assignment_id);
1732
1733 FETCH C_GET_MASTER_AAID
1734 INTO l_assignment_action_id;
1735
1736 CLOSE C_GET_MASTER_AAID;
1737
1738 l_time_type := 'ASG_PAYMENTS';
1739 else -- l_sep_check <> 'Y'
1740 l_time_type := 'RUN';
1741 END IF;
1742 else -- payments_balance_required
1743 l_time_type := 'RUN';
1744 end if; -- payments_balance_required
1745
1746 end if;
1747 --
1748 -- get asg_type
1749 --
1750 l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1751
1752 pay_us_balance_view_pkg.debug_msg(
1753 'us_tax_balance_vm called, view_mode: ' || l_asg_type);
1754 --
1755 -- if GRE mode, set DATE_EARNED context from p_virutal_date
1756 -- TODO - verify non-null p_gre_id and p_virtual_date??
1757 --
1758 if l_asg_type = 'GRE' then
1759 pay_us_balance_view_pkg.set_context('DATE_EARNED',
1760 fnd_date.date_to_canonical(p_virtual_date));
1761 if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1762 then
1763 pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1764 fnd_date.date_to_canonical(p_virtual_date));
1765 end if;
1766 pay_us_balance_view_pkg.debug_msg(
1767 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1768 || fnd_date.date_to_canonical(p_virtual_date));
1769 end if;
1770 --
1771 -- now we can get the balance!
1772 --
1773 RETURN us_tax_balance (p_tax_balance_category,
1774 p_tax_type,
1775 p_ee_or_er,
1776 l_time_type,
1777 l_asg_type,
1778 p_gre_id_context,
1779 p_jd_context,
1780 l_assignment_action_id,
1781 p_assignment_id,
1782 p_virtual_date,
1783 NULL );
1784 END;
1785 -------------------------------------------------------------------------------
1786 --
1787 -- An overloaded version without the payroll_action_id param to prevent calls
1788 -- from forms from breaking
1789 --
1790 -------------------------------------------------------------------------------
1791 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
1792 p_tax_type in varchar2,
1793 p_ee_or_er in varchar2,
1794 p_time_type in varchar2,
1795 p_asg_type in varchar2,
1796 p_gre_id_context in number,
1797 p_jd_context in varchar2 ,
1798 p_assignment_action_id in number ,
1799 p_assignment_id in number ,
1800 p_virtual_date in date )
1801 RETURN number IS
1802 BEGIN
1803 RETURN us_tax_balance (p_tax_balance_category,
1804 p_tax_type,
1805 p_ee_or_er,
1806 p_time_type,
1807 p_asg_type,
1808 p_gre_id_context,
1809 p_jd_context,
1810 p_assignment_action_id,
1811 p_assignment_id,
1812 p_virtual_date,
1813 null );
1814 END;
1815
1816 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
1817 p_tax_type in varchar2,
1818 p_ee_or_er in varchar2,
1819 p_time_type in varchar2,
1820 p_asg_type in varchar2,
1821 p_gre_id_context in number,
1822 p_jd_context in varchar2 ,
1823 p_assignment_action_id in number ,
1824 p_assignment_id in number ,
1825 p_virtual_date in date ,
1826 p_payroll_action_id in number)
1827 RETURN number IS
1828 --
1829 -- 337641 - cursor rather than ful blown select
1830 -- doing group function (min)
1831 --
1832 CURSOR get_pay_action_id IS
1833 select assignment_action_id
1834 from pay_assignment_actions
1835 where payroll_action_id = p_payroll_action_id;
1836 --
1837 l_return_value number;
1838 l_test number;
1839 l_tax_balance_category varchar2(30);
1840 l_tax_type varchar2(15);
1841 l_ee_or_er varchar2(5);
1842 l_dimension_string varchar2(80);
1843 l_jd_dimension_string varchar2(80);
1844 l_assignment_id number;
1845 l_assignment_action_id number;
1846 l_asg_exists number;
1847 l_max_date date;
1848 l_bal_start_date date;
1849 l_virtual_date date;
1850 l_valid number;
1851 --
1852 BEGIN
1853 --
1854 -- Check that inputs based on lookups are valid
1855 --
1856 pay_us_balance_view_pkg.debug_msg( '===========================================');
1857 pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
1858 pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859 pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860 pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861 pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862 pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863 pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864 pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865 pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866 pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1867 pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1868 pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
1869 --
1870 --
1871 SELECT count(0)
1872 INTO l_valid
1873 FROM hr_lookups
1874 WHERE lookup_type = 'US_TAX_BALANCE_CATEGORY'
1875 AND lookup_code = p_tax_balance_category;
1876 --
1877 IF l_valid = 0 THEN
1878 pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax bal category:' ||
1879 p_tax_balance_category);
1880 local_error('us_tax_balance',1);
1881 END IF;
1882 --
1883 SELECT count(0)
1884 INTO l_valid
1885 FROM hr_lookups
1886 WHERE lookup_type = 'US_TAX_TYPE'
1887 AND lookup_code = p_tax_type;
1888 --
1889 IF l_valid = 0 THEN
1890 pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax type: '
1891 || p_tax_type);
1892 local_error('us_tax_balance',2);
1893 END IF;
1894 --
1895 SELECT count(0)
1896 INTO l_valid
1897 FROM dual
1898 WHERE p_asg_type in ('ASG','PER','GRE');
1899 --
1900 IF l_valid = 0 THEN
1901 pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid asg_type: '
1902 || p_asg_type);
1903 local_error('us_tax_balance',3);
1904 END IF;
1905 --
1906 SELECT count(0)
1907 INTO l_valid
1908 FROM dual
1909 WHERE p_time_type in ('RUN','PTD','MONTH','QTD','YTD', 'PAYMENTS', 'PYDATE', 'ASG_PAYMENTS');
1910 --
1911 IF l_valid = 0 THEN
1912 pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid time_type: '
1913 || p_time_type);
1914 local_error('us_tax_balance',4);
1915 END IF;
1916 --
1917 -- Set the contexts used in the bal user exit. Same throughout, so set
1918 -- them up front
1919 --
1920 --hr_utility.set_location('pay_tax_bals_pkg',30);
1921 --
1922 pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id_context);
1923 IF p_jd_context IS NOT NULL THEN
1924 IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
1925 pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
1926 '-'||substr(p_jd_context,13,5));
1927 ELSE
1928 pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jd_context);
1929 END IF;
1930 END IF;
1931 --
1932 --hr_utility.set_location('pay_tax_bals_pkg',40);
1933 --
1934 l_assignment_id := p_assignment_id;
1935 l_assignment_action_id := p_assignment_action_id;
1936 l_tax_type := p_tax_type;
1937 l_tax_balance_category := p_tax_balance_category;
1938 l_virtual_date := p_virtual_date;
1939 --
1940 -- Check if assignment exists at l_virtual_date, if using date mode
1941 --
1942 --hr_utility.set_location('pay_tax_bals_pkg',50);
1943 --
1944 IF (l_assignment_id is not null and l_virtual_date is not null) THEN
1945 --
1946 select count(0)
1947 into l_asg_exists
1948 from per_assignments_f
1949 where assignment_id = l_assignment_id
1950 and l_virtual_date between effective_start_date and effective_end_date;
1951 --
1952 -- if assignment doesn't exist ...
1953 --
1954 --hr_utility.set_location('pay_tax_bals_pkg',60);
1955 --
1956 IF l_asg_exists = 0 THEN
1957 --
1958 -- get the termination date ...
1959 --
1960 select max(effective_end_date)
1961 into l_max_date
1962 from per_assignments_f
1963 where assignment_id = l_assignment_id;
1964 --
1965 -- get the date of the start of the time period in question
1966 --
1967 --hr_utility.set_location('pay_tax_bals_pkg',70);
1968 --
1969 IF p_time_type = 'QTD' THEN
1970 l_bal_start_date := trunc(l_virtual_date,'Q');
1971 ELSIF p_time_type = 'MONTH' THEN
1972 l_bal_start_date := trunc(l_virtual_date,'MM');
1973 ELSIF p_time_type = 'YTD' THEN
1974 l_bal_start_date := trunc(l_virtual_date,'Y');
1975 ELSIF p_time_type = 'PTD' THEN
1976 select tp.start_date
1977 into l_bal_start_date
1978 from per_time_periods tp,
1979 per_assignments_f asg
1980 where asg.assignment_id = l_assignment_id
1981 and l_max_date between asg.effective_start_date and effective_end_date
1982 and asg.payroll_id = tp.payroll_id
1983 and l_virtual_date between tp.start_date and tp.end_date;
1984 END IF;
1985 --
1986 -- set the virtual date to termination date, or return 0 if terminated
1987 -- before the time period.
1988 --
1989 pay_us_balance_view_pkg.debug_msg('Assignment was terminated on : ' || l_max_date);
1990 pay_us_balance_view_pkg.debug_msg('Time period in question begins on : ' ||
1991 l_bal_start_date);
1992 --
1993 IF l_max_date < l_bal_start_date THEN
1994 return 0;
1995 ELSE
1996 l_virtual_date := l_max_date;
1997 END IF;
1998 --
1999 pay_us_balance_view_pkg.debug_msg('Using new virtual date : ' || l_virtual_date);
2000 --
2001 END IF;
2002 END IF;
2003 --
2004 -- Convert "WITHHELD" to proper balance categories;
2005 --
2006 --hr_utility.set_location('pay_tax_bals_pkg',80);
2007 --
2008 IF l_tax_balance_category = 'WITHHELD' THEN
2009 IF p_ee_or_er = 'ER' or l_tax_type = 'FUTA' THEN
2010 l_tax_balance_category := 'LIABILITY';
2011 ELSIF l_tax_type = 'EIC' OR
2012 l_tax_type = 'STEIC' THEN
2013 l_tax_balance_category := 'ADVANCE';
2014 END IF;
2015 END IF;
2016 IF l_tax_balance_category = 'ADVANCED' THEN
2017 l_tax_balance_category := 'ADVANCE';
2018 END IF;
2019 --
2020 -- Check if illegal tax combo (FIT and TAXABLE, FUTA and SUBJ_NWHABLE, etc.)
2021 --
2022 --hr_utility.set_location('pay_tax_bals_pkg',90);
2023 --
2024 IF (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'COUNTY' or
2025 l_tax_type = 'CITY' or l_tax_type = 'EIC' or l_tax_type = 'HT' or
2026 l_tax_type = 'SCHOOL' or l_tax_type = 'STEIC' ) THEN -- income tax
2027 IF (l_tax_balance_category = 'TAXABLE' or
2028 l_tax_balance_category = 'EXCESS') THEN
2029 pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax cat for tax type');
2030 pay_us_balance_view_pkg.debug_err(' cat: ' || l_tax_balance_category);
2031 pay_us_balance_view_pkg.debug_err(' type: ' || l_tax_type);
2032 local_error('us_tax_balance',5);
2033 END IF;
2034 --
2035 -- return 0 for currently unsupported EIC balances.
2036 --
2037 -- skutteti added 403,457 and PRE_TAX for the pre-tax enhancements.
2038 -- RLNBug 6989549 Need to hit pre-tax enhancements RLN 05/13/08
2039 --
2040 IF l_tax_type = 'EIC' and (l_tax_balance_category = 'SUBJ_NWHABLE' -- or
2041 -- l_tax_balance_category = '401_REDNS' or
2042 -- RLNBug 6989549 -- l_tax_balance_category = '125_REDNS' or
2043 -- Need to hit -- l_tax_balance_category = 'DEP_CARE_REDNS' or
2044 -- pre-tax enhancements -- l_tax_balance_category = '403_REDNS' or
2045 -- l_tax_balance_category = '457_REDNS' or
2046 -- l_tax_balance_category = 'PRE_TAX_REDNS'
2047 ) THEN
2048 return 0;
2049 END IF;
2050 ELSE -- limit tax
2051 IF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
2052 return 0;
2053 END IF;
2054 END IF;
2055 --
2056 --hr_utility.set_location('pay_tax_bals_pkg',100);
2057 --
2058 l_ee_or_er := ltrim(rtrim(p_ee_or_er));
2059 --
2060 --------------- Some Error Checking -------------
2061 --
2062 --
2063 if (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'CITY' or
2064 l_tax_type = 'COUNTY' or l_tax_type = 'EIC' or l_tax_type = 'SCHOOL' or l_tax_type = 'HT' or l_tax_type = 'WC' or l_tax_type = 'WC2'
2065 or l_tax_type = 'STEIC' ) THEN
2066 if l_ee_or_er = 'ER' THEN
2067 pay_us_balance_view_pkg.debug_err('us_tax_balance: ER not valid for tax type: '
2068 || l_tax_type);
2069 local_error('us_tax_balance',6);
2070 else
2071 l_ee_or_er := NULL;
2072 end if;
2073 elsif (l_tax_type = 'FUTA') THEN
2074 if l_ee_or_er = 'EE' THEN
2075 pay_us_balance_view_pkg.debug_err('us_tax_balance: EE not valid for tax type: '
2076 || l_tax_type);
2077 local_error('us_tax_balance',7);
2078 else
2079 l_ee_or_er := NULL;
2080 end if;
2081 elsif (l_tax_type = 'SS' or l_tax_type = 'MEDICARE' or l_tax_type = 'SDI' or
2082 l_tax_type = 'SUI') THEN
2083 if (l_ee_or_er <> 'EE' and l_ee_or_er <> 'ER') THEN
2084 pay_us_balance_view_pkg.debug_err('Error: EE or ER required for tax type: '
2085 || l_tax_type);
2086 local_error('us_tax_balance',8);
2087 end if;
2088 end if;
2089 --
2090 --hr_utility.set_location('pay_tax_bals_pkg',110);
2091 --
2092 -- Force space at end of this parameter if necessary
2093 --
2094 --hr_utility.set_location('pay_tax_bals_pkg',120);
2095 --
2096 IF l_ee_or_er IS NOT NULL THEN
2097 l_ee_or_er := rtrim(l_ee_or_er)||' ';
2098 END IF;
2099 --
2100 -- Set up dimension strings
2101 --
2102 IF p_asg_type <> 'GRE' THEN
2103 pay_us_balance_view_pkg.debug_msg(' p_asg_type is not GRE');
2104 l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
2105 l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
2106 ELSE
2107 pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE');
2108 --
2109 l_dimension_string := 'GRE_'||p_time_type;
2110 l_jd_dimension_string := 'GRE_JD_'||p_time_type;
2111 --
2112 --
2113 --
2114 -- If given payroll action id, get an asg action id from it to use. Else
2115 -- use the assignment_id and virtual date, since the get balance routine
2116 -- will be called in date mode.
2117 --
2118 --
2119 -- bug # gaz
2120 --
2121 IF (p_payroll_action_id is not null) THEN
2122 pay_us_balance_view_pkg.debug_msg(' payroll_action_id is not NULL, getting assignment_action_id from cursor');
2123 begin
2124 OPEN get_pay_action_id;
2125 FETCH get_pay_action_id INTO l_assignment_action_id;
2126 CLOSE get_pay_action_id;
2127 end;
2128 else
2129 pay_us_balance_view_pkg.debug_msg(' payroll_action_id is NULL, cannot use it to get assignment_action_id');
2130 if (p_assignment_action_id is null) then
2131 pay_us_balance_view_pkg.debug_msg(' assignment_action_id is NULL, getting dummy for date mode');
2132 --
2133 -- Get a dummy assignment id to call the balance user exit in date mode.
2134 --
2135 declare
2136 l_bg_id number;
2137 l_count number;
2138 l_found boolean;
2139 check_asg number;
2140 begin
2141 pay_us_balance_view_pkg.set_context('DATE_EARNED',
2142 fnd_date.date_to_canonical(l_virtual_date));
2143 if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
2144 then
2145 pay_us_balance_view_pkg.set_context('BALANCE_DATE',
2146 fnd_date.date_to_canonical(p_virtual_date));
2147 end if;
2148
2149 select business_group_id
2150 into l_bg_id
2151 from hr_organization_units
2152 where organization_id = p_gre_id_context;
2153 --
2154 -- Look to see if theres an assignment in the cache for
2155 -- this business group
2156 --
2157 l_count := 0;
2158 l_found := FALSE;
2159 while ((l_count < g_nxt_free_asgid) AND (l_found = FALSE)) loop
2160 if (l_bg_id = g_asgid_tbl_bgid(l_count)) then
2161 pay_us_balance_view_pkg.debug_msg(' found candidate assignment_action_id in cache');
2162 --
2163 -- OK, now check that the assignment is valid as of the
2164 -- virtual date.
2165 --
2166 begin
2167 select 1
2168 into check_asg
2169 from per_assignments_f paf
2170 where paf.assignment_id = g_asgid_tbl_id(l_count)
2171 and p_virtual_date between paf.effective_start_date
2172 and paf.effective_end_date;
2173 --
2174 l_assignment_id := g_asgid_tbl_id(l_count);
2175 pay_us_balance_view_pkg.debug_msg(' candidate assignment_action_id '
2176 || l_assignment_id
2177 || ' is valid as of vdate');
2178 l_found := TRUE;
2179 --
2180 exception
2181 when no_data_found then null;
2182 end;
2183 end if; ---- (l_bg_id = g_asgid_tbl_bgid(l_count))
2184 l_count := l_count + 1;
2185 end loop;
2186 --
2187 if (l_found = FALSE) then
2188 --
2189 -- OK, need to get an assignment from the database.
2190 --
2191 pay_us_balance_view_pkg.debug_msg(' assignment_action_id not found in cache, going to DB');
2192 begin /* Modified the query for performance (Bug 3343982)*/
2193
2194 select min(paa.assignment_id)
2195 into l_assignment_id
2196 from pay_assignment_actions paa,
2197 pay_payroll_actions pact,
2198 pay_payrolls_f ppf
2199 where pact.effective_date <= p_virtual_date
2200 and pact.payroll_action_id=paa.payroll_action_id
2201 and pact.action_type in ('R', 'Q', 'I', 'V', 'B')
2202 and paa.tax_unit_id = p_gre_id_context
2203 and ppf.payroll_id = pact.payroll_id
2204 and ppf.business_group_id = l_bg_id;
2205
2206 --
2207 -- Place the defined balance in cache.
2208 --
2209 g_asgid_tbl_bgid(g_nxt_free_asgid) := ltrim(rtrim(l_bg_id));
2210 g_asgid_tbl_id (g_nxt_free_asgid) :=
2211 ltrim(rtrim(l_assignment_id));
2212 g_nxt_free_asgid := g_nxt_free_asgid + 1;
2213 --
2214 exception when no_data_found then
2215 begin
2216 pay_us_balance_view_pkg.debug_err('us_tax_balance: Failed find asg id');
2217 local_error('us_tax_balance',1);
2218 --
2219 end;
2220 end;
2221 end if; ---- (l_found = FALSE)
2222 end;
2223 end if; ---- (p_assignment_action_id is null)
2224 END IF; ---- (p_payroll_action_id is not null)
2225 END IF;
2226 --
2227 -- 3-12-2003 added ASG_PAYMENTS
2228 --
2229 IF p_time_type in ('PAYMENTS', 'ASG_PAYMENTS') THEN
2230 --
2231 -- 360669 put PAYMENTS_JD back
2232 --
2233 l_jd_dimension_string := p_time_type||'_JD';
2234 l_dimension_string := p_time_type;
2235 --
2236 END IF;
2237 --
2238 --
2239 -- Check if the tax is federal or not.
2240 --
2241 SELECT count(0)
2242 INTO l_test
2243 FROM sys.dual
2244 WHERE l_tax_type in ('FIT','FUTA','MEDICARE','SS','EIC');
2245 --
2246 IF l_test <> 0 THEN -- yes, the tax is federal
2247 --
2248 IF l_tax_balance_category = 'GROSS' THEN
2249 l_return_value := call_balance_user_exit ('GROSS_EARNINGS',
2250 l_dimension_string,
2251 l_assignment_action_id,
2252 l_assignment_id,
2253 l_virtual_date,
2254 p_asg_type,
2255 p_gre_id_context,
2256 p_jd_context);
2257 --
2258 ELSIF l_tax_balance_category = 'SUBJ_WHABLE' THEN
2259 l_return_value := call_balance_user_exit ('REGULAR_EARNINGS',
2260 l_dimension_string,
2261 l_assignment_action_id,
2262 l_assignment_id,
2263 l_virtual_date,
2264 p_asg_type,
2265 p_gre_id_context,
2266 p_jd_context)
2267 + call_balance_user_exit (
2268 'SUPPLEMENTAL_EARNINGS_FOR_'||l_tax_type,
2269 'SUBJECT_TO_TAX_'||l_dimension_string,
2270 l_assignment_action_id,
2271 l_assignment_id,
2272 l_virtual_date,
2273 p_asg_type,
2274 p_gre_id_context,
2275 p_jd_context);
2276 --
2277 ELSIF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
2278 l_return_value := call_balance_user_exit (
2279 'SUPPLEMENTAL_EARNINGS_FOR_NW'||l_tax_type,
2280 'SUBJECT_TO_TAX_'||l_dimension_string,
2281 l_assignment_action_id,
2282 l_assignment_id,
2283 l_virtual_date,
2284 p_asg_type,
2285 p_gre_id_context,
2286 p_jd_context);
2287 --
2288 ELSIF l_tax_balance_category = '401_REDNS' THEN
2289 l_return_value := call_balance_user_exit ('DEF_COMP_401K',
2290 l_dimension_string,
2291 l_assignment_action_id,
2292 l_assignment_id,
2293 l_virtual_date,
2294 p_asg_type,
2295 p_gre_id_context,
2296 p_jd_context);
2297 --
2298 -- 337641
2299 -- check if balance 0 therefore no need to
2300 -- subtract subsequent balance
2301 --
2302 IF ( l_return_value <> 0 )
2303 THEN
2304 l_return_value := l_return_value
2305 - call_balance_user_exit ('DEF_COMP_401K_FOR_'||l_tax_type,
2306 'SUBJECT_TO_TAX_'||l_dimension_string,
2307 l_assignment_action_id,
2308 l_assignment_id,
2309 l_virtual_date,
2310 p_asg_type,
2311 p_gre_id_context,
2312 p_jd_context);
2313
2314 --
2315 -- added by tmehra in Dec 2001, to remove the Non W2 portion
2316 --
2317 IF l_tax_type = 'FIT' THEN
2318 l_return_value := l_return_value - call_balance_user_exit
2319 ('FIT_NON_W2_DEF_COMP_401',
2320 l_dimension_string,
2321 l_assignment_action_id,
2322 l_assignment_id,
2323 l_virtual_date,
2324 p_asg_type,
2325 p_gre_id_context,
2326 p_jd_context);
2327
2328 END IF;
2329
2330 END IF;
2331 --
2332 ELSIF l_tax_balance_category = '125_REDNS' THEN
2333 l_return_value := call_balance_user_exit ('SECTION_125',
2334 l_dimension_string,
2335 l_assignment_action_id,
2336 l_assignment_id,
2337 l_virtual_date,
2338 p_asg_type,
2339 p_gre_id_context,
2340 p_jd_context);
2341 --
2342 -- 337641
2343 -- check if balance 0 therefore no need to
2344 -- subtract subsequent balance
2345 --
2346 IF ( l_return_value <> 0 )
2347 THEN
2348 l_return_value := l_return_value
2349 - call_balance_user_exit ('SECTION_125_FOR_'||l_tax_type,
2350 'SUBJECT_TO_TAX_'||l_dimension_string,
2351 l_assignment_action_id,
2352 l_assignment_id,
2353 l_virtual_date,
2354 p_asg_type,
2355 p_gre_id_context,
2356 p_jd_context);
2357
2358 --
2359 -- added by tmehra in Dec 2001, to remove the Non W2 portion
2360 --
2361 IF l_tax_type = 'FIT' THEN
2362 l_return_value := l_return_value - call_balance_user_exit
2363 ('FIT_NON_W2_SECTION_125',
2364 l_dimension_string,
2365 l_assignment_action_id,
2366 l_assignment_id,
2367 l_virtual_date,
2368 p_asg_type,
2369 p_gre_id_context,
2370 p_jd_context);
2371
2372 END IF;
2373
2374 END IF;
2375 --
2376 ELSIF l_tax_balance_category = 'DEP_CARE_REDNS' THEN
2377 l_return_value := call_balance_user_exit ('DEPENDENT_CARE',
2378 l_dimension_string,
2379 l_assignment_action_id,
2380 l_assignment_id,
2381 l_virtual_date,
2382 p_asg_type,
2383 p_gre_id_context,
2384 p_jd_context);
2385 --
2386 -- 337641
2387 -- check if balance 0 therefore no need to
2388 -- subtract subsequent balance
2389 --
2390 IF ( l_return_value <> 0 )
2391 THEN
2392 l_return_value := l_return_value
2393 - call_balance_user_exit ('DEPENDENT_CARE_FOR_'||l_tax_type,
2394 'SUBJECT_TO_TAX_'||l_dimension_string,
2395 l_assignment_action_id,
2396 l_assignment_id,
2397 l_virtual_date,
2398 p_asg_type,
2399 p_gre_id_context,
2400 p_jd_context);
2401
2402 --
2403 -- added by tmehra in Dec 2001, to remove the Non W2 portion
2404 --
2405 IF l_tax_type = 'FIT' THEN
2406 l_return_value := l_return_value - call_balance_user_exit
2407 ('FIT_NON_W2_DEPENDENT_CARE',
2408 l_dimension_string,
2409 l_assignment_action_id,
2410 l_assignment_id,
2411 l_virtual_date,
2412 p_asg_type,
2413 p_gre_id_context,
2414 p_jd_context);
2415 END IF;
2416
2417 END IF;
2418 /***************************************************************************
2419 ** 403b, 457 and Pre_Tax added by skutteti for the pre-tax enhancements, as
2420 ** new categories has been added and all the deduction categories feed the
2421 ** generic pretax.
2422 ****************************************************************************/
2423 ELSIF l_tax_balance_category = '403_REDNS' THEN
2424 l_return_value := call_balance_user_exit ('DEF_COMP_403B',
2425 l_dimension_string,
2426 l_assignment_action_id,
2427 l_assignment_id,
2428 l_virtual_date,
2429 p_asg_type,
2430 p_gre_id_context,
2431 p_jd_context);
2432 IF ( l_return_value <> 0 )
2433 THEN
2434 l_return_value := l_return_value - call_balance_user_exit (
2435 'DEF_COMP_403B_FOR_'||l_tax_type,
2436 'SUBJECT_TO_TAX_'||l_dimension_string,
2437 l_assignment_action_id,
2438 l_assignment_id,
2439 l_virtual_date,
2440 p_asg_type,
2441 p_gre_id_context,
2442 p_jd_context);
2443
2444 --
2445 -- added by tmehra in Dec 2001, to remove the Non W2 portion
2446 --
2447 IF l_tax_type = 'FIT' THEN
2448 l_return_value := l_return_value - call_balance_user_exit
2449 ('FIT_NON_W2_DEF_COMP_403',
2450 l_dimension_string,
2451 l_assignment_action_id,
2452 l_assignment_id,
2453 l_virtual_date,
2454 p_asg_type,
2455 p_gre_id_context,
2456 p_jd_context);
2457 END IF;
2458
2459
2460 END IF;
2461 --
2462 ELSIF l_tax_balance_category = '457_REDNS' THEN
2463 l_return_value := call_balance_user_exit ('DEF_COMP_457',
2464 l_dimension_string,
2465 l_assignment_action_id,
2466 l_assignment_id,
2467 l_virtual_date,
2468 p_asg_type,
2469 p_gre_id_context,
2470 p_jd_context);
2471 IF ( l_return_value <> 0 )
2472 THEN
2473 l_return_value := l_return_value - call_balance_user_exit (
2474 'DEF_COMP_457_FOR_'||l_tax_type,
2475 'SUBJECT_TO_TAX_'||l_dimension_string,
2476 l_assignment_action_id,
2477 l_assignment_id,
2478 l_virtual_date,
2479 p_asg_type,
2480 p_gre_id_context,
2481 p_jd_context);
2482
2483 --
2484 -- added by tmehra in Dec 2001, to remove the Non W2 portion
2485 --
2486 IF l_tax_type = 'FIT' THEN
2487 l_return_value := l_return_value - call_balance_user_exit
2488 ('FIT_NON_W2_DEF_COMP_457',
2489 l_dimension_string,
2490 l_assignment_action_id,
2491 l_assignment_id,
2492 l_virtual_date,
2493 p_asg_type,
2494 p_gre_id_context,
2495 p_jd_context);
2496
2497 END IF;
2498
2499 END IF;
2500 --
2501 ELSIF l_tax_balance_category = 'PRE_TAX_REDNS' THEN
2502 l_return_value := call_balance_user_exit ('PRE_TAX_DEDUCTIONS',
2503 l_dimension_string,
2504 l_assignment_action_id,
2505 l_assignment_id,
2506 l_virtual_date,
2507 p_asg_type,
2508 p_gre_id_context,
2509 p_jd_context);
2510 IF ( l_return_value <> 0 )
2511 THEN
2512 l_return_value := l_return_value - call_balance_user_exit (
2513 'PRE_TAX_DEDUCTIONS_FOR_'||l_tax_type,
2514 'SUBJECT_TO_TAX_'||l_dimension_string,
2515 l_assignment_action_id,
2516 l_assignment_id,
2517 l_virtual_date,
2518 p_asg_type,
2519 p_gre_id_context,
2520 p_jd_context);
2521
2522 --
2523 -- added by tmehra in Dec 2001, to remove the Non W2 portion
2524 --
2525 IF l_tax_type = 'FIT' THEN
2526 l_return_value := l_return_value - call_balance_user_exit
2527 ('FIT_NON_W2_PRE_TAX_DEDNS',
2528 l_dimension_string,
2529 l_assignment_action_id,
2530 l_assignment_id,
2531 l_virtual_date,
2532 p_asg_type,
2533 p_gre_id_context,
2534 p_jd_context);
2535
2536 END IF;
2537
2538
2539 --
2540 END IF;
2541 --
2542 ELSIF l_tax_balance_category = 'TAXABLE' THEN
2543 l_return_value := call_balance_user_exit (l_tax_type||'_'||
2544 l_ee_or_er||'TAXABLE',
2545 l_dimension_string,
2546 l_assignment_action_id,
2547 l_assignment_id,
2548 l_virtual_date,
2549 p_asg_type,
2550 p_gre_id_context,
2551 p_jd_context);
2552 --
2553 ELSIF (l_tax_balance_category = 'WITHHELD' or
2554 l_tax_balance_category = 'LIABILITY' or
2555 l_tax_balance_category = 'ADVANCE') THEN
2556 l_return_value := call_balance_user_exit (
2557 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
2558 l_dimension_string,
2559 l_assignment_action_id,
2560 l_assignment_id,
2561 l_virtual_date,
2562 p_asg_type,
2563 p_gre_id_context,
2564 p_jd_context);
2565 END IF;
2566 ELSE -- the tax is non-federal
2567 --
2568 -- if the tax balance is not derived, get it here.
2569 IF (l_tax_balance_category <> 'SUBJECT' and
2570 l_tax_balance_category <> 'EXEMPT' and
2571 l_tax_balance_category <> 'EXCESS' and
2572 l_tax_balance_category <> 'REDUCED_SUBJ_WHABLE') THEN
2573 --
2574 -- Use the CITY balances for HT if we don't want to see LIABILITY
2575 --
2576 IF (l_tax_type = 'HT') THEN
2577 IF (l_tax_balance_category <> 'WITHHELD') THEN
2578 l_tax_type := 'CITY';
2579 ELSE
2580 l_tax_type := 'HEAD TAX';
2581 END IF;
2582 END IF;
2583 --
2584 -- Added for workers comp
2585 If (l_tax_type = 'WC' ) THEN
2586 l_tax_type := 'WORKERS COMP';
2587 END IF;
2588 If (l_tax_type = 'WC2') THEN
2589 l_tax_type := 'WORKERS COMP2';
2590 END IF;
2591 --
2592 l_return_value := call_balance_user_exit (
2593 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
2594 l_jd_dimension_string,
2595 l_assignment_action_id,
2596 l_assignment_id,
2597 l_virtual_date,
2598 p_asg_type,
2599 p_gre_id_context,
2600 p_jd_context);
2601 END IF;
2602 END IF;
2603 -- Some Reports Require Reporting of W2 Wages instead of
2604 -- subject. Properly this should be done with an additional
2605 -- balance type
2606 IF l_tax_balance_category = 'SUBJECT' and
2607 NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') <> 'W2' THEN
2608 pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE');
2609 l_return_value := us_tax_balance('SUBJ_WHABLE',
2610 l_tax_type,
2611 p_ee_or_er,
2612 p_time_type,
2613 p_asg_type,
2614 p_gre_id_context,
2615 p_jd_context,
2616 l_assignment_action_id,
2617 l_assignment_id,
2618 l_virtual_date)
2619 + us_tax_balance('SUBJ_NWHABLE',
2620 l_tax_type,
2621 p_ee_or_er,
2622 p_time_type,
2623 p_asg_type,
2624 p_gre_id_context,
2625 p_jd_context,
2626 l_assignment_action_id,
2627 l_assignment_id,
2628 l_virtual_date);
2629
2630 --
2631 -- Note: Below is equivalent to reduced subject withholdable.
2632 --
2633 ELSIF l_tax_balance_category = 'SUBJECT' and NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') = 'W2' THEN
2634
2635 pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE FOR W2');
2636 l_return_value := us_tax_balance('SUBJ_WHABLE',
2637 l_tax_type,
2638 p_ee_or_er,
2639 p_time_type,
2640 p_asg_type,
2641 p_gre_id_context,
2642 p_jd_context,
2643 l_assignment_action_id,
2644 l_assignment_id,
2645 l_virtual_date)
2646 + us_tax_balance('SUBJ_NWHABLE',
2647 l_tax_type,
2648 p_ee_or_er,
2649 p_time_type,
2650 p_asg_type,
2651 p_gre_id_context,
2652 p_jd_context,
2653 l_assignment_action_id,
2654 l_assignment_id,
2655 l_virtual_date)
2656 /**************************************************************
2657 * Replaced the following(401K, 125 and Dependent care) with
2658 * the Pre_tax Redns as all the above three feeds into this
2659 * along with the new ones like 403B and 457
2660 **************************************************************/
2661 -- - us_tax_balance('401_REDNS',
2662 -- l_tax_type,
2663 -- p_ee_or_er,
2664 -- p_time_type,
2665 -- p_asg_type,
2666 -- p_gre_id_context,
2667 -- p_jd_context,
2668 -- l_assignment_action_id,
2669 -- l_assignment_id,
2670 -- l_virtual_date)
2671 -- - us_tax_balance('125_REDNS',
2672 -- l_tax_type,
2673 -- p_ee_or_er,
2674 -- p_time_type,
2675 -- p_asg_type,
2676 -- p_gre_id_context,
2677 -- p_jd_context,
2678 -- l_assignment_action_id,
2679 -- l_assignment_id,
2680 -- l_virtual_date)
2681 -- - us_tax_balance('DEP_CARE_REDNS',
2682 -- l_tax_type,
2683 -- p_ee_or_er,
2684 -- p_time_type,
2685 -- p_asg_type,
2686 -- p_gre_id_context,
2687 -- p_jd_context,
2688 -- l_assignment_action_id,
2689 -- l_assignment_id,
2690 -- l_virtual_date);
2691 /************************************************
2692 ** Added the Pre_tax Redns instead
2693 ************************************************/
2694 - us_tax_balance('PRE_TAX_REDNS',
2695 l_tax_type,
2696 p_ee_or_er,
2697 p_time_type,
2698 p_asg_type,
2699 p_gre_id_context,
2700 p_jd_context,
2701 l_assignment_action_id,
2702 l_assignment_id,
2703 l_virtual_date);
2704 --
2705 ELSIF l_tax_balance_category = 'EXEMPT' THEN
2706 l_return_value := us_tax_balance('GROSS',
2707 l_tax_type,
2708 p_ee_or_er,
2709 p_time_type,
2710 p_asg_type,
2711 p_gre_id_context,
2712 p_jd_context,
2713 l_assignment_action_id,
2714 l_assignment_id,
2715 l_virtual_date);
2716 --
2717 -- 337641
2718 -- check if balance 0 therefore no need to
2719 -- subtract subsequent balance
2720 --
2721 IF ( l_return_value <> 0 )
2722 THEN
2723 l_return_value := l_return_value
2724 - us_tax_balance('SUBJECT',
2725 l_tax_type,
2726 p_ee_or_er,
2727 p_time_type,
2728 p_asg_type,
2729 p_gre_id_context,
2730 p_jd_context,
2731 l_assignment_action_id,
2732 l_assignment_id,
2733 l_virtual_date);
2734 END IF;
2735 --
2736 ELSIF l_tax_balance_category = 'REDUCED_SUBJ_WHABLE' THEN
2737 l_return_value := us_tax_balance('SUBJ_WHABLE',
2738 l_tax_type,
2739 p_ee_or_er,
2740 p_time_type,
2741 p_asg_type,
2742 p_gre_id_context,
2743 p_jd_context,
2744 l_assignment_action_id,
2745 l_assignment_id,
2746 l_virtual_date);
2747 --
2748 -- 337641
2749 -- check if balance 0 therefore no need to
2750 -- subtract subsequent balance
2751 --
2752 IF ( l_return_value <> 0 )
2753 THEN
2754 l_return_value := l_return_value
2755 /**********************************************************
2756 * Skutteti commented the following and replaced it by
2757 * Pre_tax redns as all the three along with the
2758 * new categories (403B and 457) feeds the Pre Tax Redns
2759 **********************************************************
2760 -- - us_tax_balance('401_REDNS',
2761 -- l_tax_type,
2762 -- p_ee_or_er,
2763 -- p_time_type,
2764 -- p_asg_type,
2765 -- p_gre_id_context,
2766 -- p_jd_context,
2767 -- l_assignment_action_id,
2768 -- l_assignment_id,
2769 -- l_virtual_date)
2770 -- - us_tax_balance('125_REDNS',
2771 -- l_tax_type,
2772 -- p_ee_or_er,
2773 -- p_time_type,
2774 -- p_asg_type,
2775 -- p_gre_id_context,
2776 -- p_jd_context,
2777 -- l_assignment_action_id,
2778 -- l_assignment_id,
2779 -- l_virtual_date)
2780 -- - us_tax_balance('DEP_CARE_REDNS',
2781 -- l_tax_type,
2782 -- p_ee_or_er,
2783 -- p_time_type,
2784 -- p_asg_type,
2785 -- p_gre_id_context,
2786 -- p_jd_context,
2787 -- l_assignment_action_id,
2788 -- l_assignment_id,
2789 -- l_virtual_date);
2790 /*************************************************
2791 * replaced by PRE_TAX_REDNS below
2792 **************************************************/
2793 - us_tax_balance('PRE_TAX_REDNS',
2794 l_tax_type,
2795 p_ee_or_er,
2796 p_time_type,
2797 p_asg_type,
2798 p_gre_id_context,
2799 p_jd_context,
2800 l_assignment_action_id,
2801 l_assignment_id,
2802 l_virtual_date);
2803 END IF;
2804 --
2805 ELSIF l_tax_balance_category = 'EXCESS' THEN
2806 l_return_value := us_tax_balance('REDUCED_SUBJ_WHABLE',
2807 l_tax_type,
2808 p_ee_or_er,
2809 p_time_type,
2810 p_asg_type,
2811 p_gre_id_context,
2812 p_jd_context,
2813 l_assignment_action_id,
2814 l_assignment_id,
2815 l_virtual_date);
2816 --
2817 -- 337641
2818 -- check if balance 0 therefore no need to
2819 -- subtract subsequent balance
2820 --
2821 IF ( l_return_value <> 0 )
2822 THEN
2823 l_return_value := l_return_value
2824 - us_tax_balance('TAXABLE',
2825 l_tax_type,
2826 p_ee_or_er,
2827 p_time_type,
2828 p_asg_type,
2829 p_gre_id_context,
2830 p_jd_context,
2831 l_assignment_action_id,
2832 l_assignment_id,
2833 l_virtual_date);
2834 END IF;
2835 END IF;
2836 --
2837 pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE Returning : ' || l_return_value);
2838 --
2839 return l_return_value;
2840 --
2841 END us_tax_balance;
2842 --
2843 ---------------------------------------------------------------------------------------
2844 -- PROCEDURE us_gp_multiple_gre_ytd
2845 --
2846 -- Description: This procedure is passed up to ten balances (using balance name).
2847 -- It calculates the GRE_YTD dimension figure of the balance(s) at
2848 -- GROUP LEVEL only, as at the effective date (also passed in).
2849 -- The route code for GRE_YTD is copied from the correspoding
2850 -- row in FF_ROUTES, plus the PAY_BALANCE_TYPES table is added
2851 -- so that balance names can be used to match on balance feeds
2852 -- for this balance. This method of multiple-decode select means
2853 -- that selecting 10 balances takes the same amount of time as selecting
2854 -- a single balance in the previous implementation.
2855 --
2856 -- Written primarily for GRE Totals report, but can be used by other
2857 -- processes due to the slightly more generic interface. The normal
2858 -- balance retrieval mechanism bypassed as assignment-level route
2859 -- (and latest balances) always used which is not performant for
2860 -- this purpose.
2861 --
2862 -- Maintenance: The cursor should be dual maintained with the row for GRE_YTD in
2863 -- FF_ROUTES.
2864 ---------------------------------------------------------------------------------------
2865 procedure us_gp_multiple_gre_ytd (p_tax_unit_id IN NUMBER,
2866 p_effective_date IN DATE,
2867 p_balance_name1 IN VARCHAR2 ,
2868 p_balance_name2 IN VARCHAR2 ,
2869 p_balance_name3 IN VARCHAR2 ,
2870 p_balance_name4 IN VARCHAR2 ,
2871 p_balance_name5 IN VARCHAR2 ,
2872 p_balance_name6 IN VARCHAR2 ,
2873 p_balance_name7 IN VARCHAR2 ,
2874 p_balance_name8 IN VARCHAR2 ,
2875 p_balance_name9 IN VARCHAR2 ,
2876 p_balance_name10 IN VARCHAR2 ,
2877 p_value1 OUT NOCOPY NUMBER,
2878 p_value2 OUT NOCOPY NUMBER,
2879 p_value3 OUT NOCOPY NUMBER,
2880 p_value4 OUT NOCOPY NUMBER,
2881 p_value5 OUT NOCOPY NUMBER,
2882 p_value6 OUT NOCOPY NUMBER,
2883 p_value7 OUT NOCOPY NUMBER,
2884 p_value8 OUT NOCOPY NUMBER,
2885 p_value9 OUT NOCOPY NUMBER,
2886 p_value10 OUT NOCOPY NUMBER)
2887 IS
2888 --
2889 l_balance_type_id1 number;
2890 l_balance_type_id2 number;
2891 l_balance_type_id3 number;
2892 l_balance_type_id4 number;
2893 l_balance_type_id5 number;
2894 l_balance_type_id6 number;
2895 l_balance_type_id7 number;
2896 l_balance_type_id8 number;
2897 l_balance_type_id9 number;
2898 l_balance_type_id10 number;
2899
2900 cursor get_values (c_balance_type_id1 in number,
2901 c_balance_type_id2 in number,
2902 c_balance_type_id3 in number,
2903 c_balance_type_id4 in number,
2904 c_balance_type_id5 in number,
2905 c_balance_type_id6 in number,
2906 c_balance_type_id7 in number,
2907 c_balance_type_id8 in number,
2908 c_balance_type_id9 in number,
2909 c_balance_type_id10 in number) is
2910 SELECT /* Removed RULE hint. Bug 3331031 */
2911 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
2912 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
2913 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
2914 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
2915 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
2916 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
2917 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
2918 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
2919 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
2920 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0)
2921 FROM pay_balance_feeds_f FEED
2922 , pay_run_result_values TARGET
2923 , pay_run_results RR
2924 , pay_assignment_actions ASSACT
2925 , pay_payroll_actions PACT
2926 where PACT.effective_date between trunc(p_effective_date,'Y')
2927 and p_effective_date
2928 and PACT.action_type in ('R','Q','I','B','V')
2929 /*
2930 and PACT.action_status = 'C'
2931 */
2932 and ASSACT.payroll_action_id = PACT.payroll_action_id
2933 and ASSACT.action_status = 'C'
2934 and ASSACT.tax_unit_id = p_tax_unit_id
2935 and RR.assignment_action_id = ASSACT.assignment_action_id
2936 and RR.status in ('P','PA')
2937 and TARGET.run_result_id = RR.run_result_id
2938 and nvl(TARGET.result_value,'0') <> '0'
2939 and FEED.input_value_id = TARGET.input_value_id
2940 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
2941 c_balance_type_id3,c_balance_type_id4,
2942 c_balance_type_id5,c_balance_type_id6,
2943 c_balance_type_id7,c_balance_type_id8,
2944 c_balance_type_id9,c_balance_type_id10)
2945 and PACT.effective_date between FEED.effective_start_date
2946 and FEED.effective_end_date;
2947 --
2948 BEGIN -- us_multiple_gre_ytd
2949 --
2950 -- Get Multiple balance type values for this dimension
2951 --
2952 l_balance_type_id1 := get_balance_type(p_balance_name1);
2953 l_balance_type_id2 := get_balance_type(p_balance_name2);
2954 l_balance_type_id3 := get_balance_type(p_balance_name3);
2955 l_balance_type_id4 := get_balance_type(p_balance_name4);
2956 l_balance_type_id5 := get_balance_type(p_balance_name5);
2957 l_balance_type_id6 := get_balance_type(p_balance_name6);
2958 l_balance_type_id7 := get_balance_type(p_balance_name7);
2959 l_balance_type_id8 := get_balance_type(p_balance_name8);
2960 l_balance_type_id9 := get_balance_type(p_balance_name9);
2961 l_balance_type_id10:= get_balance_type(p_balance_name10);
2962
2963 open get_values (l_balance_type_id1,
2964 l_balance_type_id2,
2965 l_balance_type_id3,
2966 l_balance_type_id4,
2967 l_balance_type_id5,
2968 l_balance_type_id6,
2969 l_balance_type_id7,
2970 l_balance_type_id8,
2971 l_balance_type_id9,
2972 l_balance_type_id10);
2973
2974 fetch get_values into p_value1 ,
2975 p_value2 ,
2976 p_value3 ,
2977 p_value4 ,
2978 p_value5 ,
2979 p_value6 ,
2980 p_value7 ,
2981 p_value8 ,
2982 p_value9 ,
2983 p_value10;
2984 close get_values;
2985 --
2986 END us_gp_multiple_gre_ytd;
2987 --
2988 ---------------------------------------------------------------------------------------
2989 -- PROCEDURE us_gp_multiple_gre_qtd
2990 --
2991 -- Description: This procedure is passed up to ten balances (using balance name).
2992 -- It calculates the GRE_QTD dimension figure of the balance(s) at
2993 -- GROUP LEVEL only, as at the effective date (also passed in).
2994 -- The route code for GRE_QTD is copied from the correspoding
2995 -- row in FF_ROUTES, plus the PAY_BALANCE_TYPES table is added
2996 -- so that balance names can be used to match on balance feeds
2997 -- for this balance. This method of multiple-decode select means
2998 -- that selecting 10 balances takes the same amount of time as selecting
2999 -- a single balance in the previous implementation.
3000 --
3001 -- Written primarily for GRE Totals report, but can be used by other
3002 -- processes due to the slightly more generic interface. The normal
3003 -- balance retrieval mechanism bypassed as assignment-level route
3004 -- (and latest balances) always used which is not performant for
3005 -- this purpose.
3006 --
3007 -- Maintenance: The cursor should be dual maintained with the row for GRE_YTD in
3008 -- FF_ROUTES.
3009 ---------------------------------------------------------------------------------------
3010 procedure us_gp_multiple_gre_qtd (p_tax_unit_id IN NUMBER,
3011 p_effective_date IN DATE,
3012 p_balance_name1 IN VARCHAR2 ,
3013 p_balance_name2 IN VARCHAR2 ,
3014 p_balance_name3 IN VARCHAR2 ,
3015 p_balance_name4 IN VARCHAR2 ,
3016 p_balance_name5 IN VARCHAR2 ,
3017 p_balance_name6 IN VARCHAR2 ,
3018 p_balance_name7 IN VARCHAR2 ,
3019 p_balance_name8 IN VARCHAR2 ,
3020 p_balance_name9 IN VARCHAR2 ,
3021 p_balance_name10 IN VARCHAR2 ,
3022 p_value1 OUT NOCOPY NUMBER,
3023 p_value2 OUT NOCOPY NUMBER,
3024 p_value3 OUT NOCOPY NUMBER,
3025 p_value4 OUT NOCOPY NUMBER,
3026 p_value5 OUT NOCOPY NUMBER,
3027 p_value6 OUT NOCOPY NUMBER,
3028 p_value7 OUT NOCOPY NUMBER,
3029 p_value8 OUT NOCOPY NUMBER,
3030 p_value9 OUT NOCOPY NUMBER,
3031 p_value10 OUT NOCOPY NUMBER)
3032 IS
3033 l_dummy NUMBER;
3034 BEGIN
3035 us_gp_multiple_gre_qtd (p_tax_unit_id => p_tax_unit_id
3036 ,p_effective_date => p_effective_date
3037 ,p_balance_name1 => p_balance_name1
3038 ,p_balance_name2 => p_balance_name2
3039 ,p_balance_name3 => p_balance_name3
3040 ,p_balance_name4 => p_balance_name4
3041 ,p_balance_name5 => p_balance_name5
3042 ,p_balance_name6 => p_balance_name6
3043 ,p_balance_name7 => p_balance_name7
3044 ,p_balance_name8 => p_balance_name8
3045 ,p_balance_name9 => p_balance_name9
3046 ,p_balance_name10 => p_balance_name10
3047 ,p_balance_name11 => null
3048 ,p_balance_name12 => null
3049 ,p_value1 => p_value1
3050 ,p_value2 => p_value2
3051 ,p_value3 => p_value3
3052 ,p_value4 => p_value4
3053 ,p_value5 => p_value5
3054 ,p_value6 => p_value6
3055 ,p_value7 => p_value7
3056 ,p_value8 => p_value8
3057 ,p_value9 => p_value9
3058 ,p_value10 => p_value10
3059 ,p_value11 => l_dummy
3060 ,p_value12 => l_dummy);
3061
3062 END us_gp_multiple_gre_qtd;
3063
3064 procedure us_gp_multiple_gre_qtd (p_tax_unit_id IN NUMBER,
3065 p_effective_date IN DATE,
3066 p_balance_name1 IN VARCHAR2 ,
3067 p_balance_name2 IN VARCHAR2 ,
3068 p_balance_name3 IN VARCHAR2 ,
3069 p_balance_name4 IN VARCHAR2 ,
3070 p_balance_name5 IN VARCHAR2 ,
3071 p_balance_name6 IN VARCHAR2 ,
3072 p_balance_name7 IN VARCHAR2 ,
3073 p_balance_name8 IN VARCHAR2 ,
3074 p_balance_name9 IN VARCHAR2 ,
3075 p_balance_name10 IN VARCHAR2 ,
3076 p_balance_name11 IN VARCHAR2 ,
3077 p_balance_name12 IN VARCHAR2 ,
3078 p_value1 OUT NOCOPY NUMBER,
3079 p_value2 OUT NOCOPY NUMBER,
3080 p_value3 OUT NOCOPY NUMBER,
3081 p_value4 OUT NOCOPY NUMBER,
3082 p_value5 OUT NOCOPY NUMBER,
3083 p_value6 OUT NOCOPY NUMBER,
3084 p_value7 OUT NOCOPY NUMBER,
3085 p_value8 OUT NOCOPY NUMBER,
3086 p_value9 OUT NOCOPY NUMBER,
3087 p_value10 OUT NOCOPY NUMBER,
3088 p_value11 OUT NOCOPY NUMBER,
3089 p_value12 OUT NOCOPY NUMBER)
3090 IS
3091 --
3092 l_balance_type_id1 number;
3093 l_balance_type_id2 number;
3094 l_balance_type_id3 number;
3095 l_balance_type_id4 number;
3096 l_balance_type_id5 number;
3097 l_balance_type_id6 number;
3098 l_balance_type_id7 number;
3099 l_balance_type_id8 number;
3100 l_balance_type_id9 number;
3101 l_balance_type_id10 number;
3102 l_balance_type_id11 number;
3103 l_balance_type_id12 number;
3104
3105 cursor get_values (c_balance_type_id1 in number,
3106 c_balance_type_id2 in number,
3107 c_balance_type_id3 in number,
3108 c_balance_type_id4 in number,
3109 c_balance_type_id5 in number,
3110 c_balance_type_id6 in number,
3111 c_balance_type_id7 in number,
3112 c_balance_type_id8 in number,
3113 c_balance_type_id9 in number,
3114 c_balance_type_id10 in number,
3115 c_balance_type_id11 in number,
3116 c_balance_type_id12 in number) is
3117 SELECT /* Removed RULE hint. Bug 3331031 */
3118 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
3119 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
3120 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
3121 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
3122 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
3123 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
3124 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
3125 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
3126 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
3127 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0),
3128 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id11,TARGET.result_value * FEED.scale,0)),0),
3129 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id12,TARGET.result_value * FEED.scale,0)),0)
3130 FROM pay_balance_feeds_f FEED
3131 , pay_run_result_values TARGET
3132 , pay_run_results RR
3133 , pay_assignment_actions ASSACT
3134 , pay_payroll_actions PACT
3135 where PACT.effective_date between trunc(p_effective_date,'Q')
3136 and p_effective_date
3137 and PACT.action_type in ('R','Q','I','B','V')
3138 /*
3139 and PACT.action_status = 'C'
3140 */
3141 and ASSACT.payroll_action_id = PACT.payroll_action_id
3142 and ASSACT.action_status = 'C'
3143 and ASSACT.tax_unit_id = p_tax_unit_id
3144 and RR.assignment_action_id = ASSACT.assignment_action_id
3145 and RR.status in ('P','PA')
3146 and TARGET.run_result_id = RR.run_result_id
3147 and nvl(TARGET.result_value,'0') <> '0'
3148 and FEED.input_value_id = TARGET.input_value_id
3149 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
3150 c_balance_type_id3,c_balance_type_id4,
3151 c_balance_type_id5,c_balance_type_id6,
3152 c_balance_type_id7,c_balance_type_id8,
3153 c_balance_type_id9,c_balance_type_id10,
3154 c_balance_type_id11, c_balance_type_id12)
3155 and PACT.effective_date between FEED.effective_start_date
3156 and FEED.effective_end_date;
3157 --
3158 BEGIN -- us_multiple_gre_qtd
3159 --
3160 -- Get Multiple balance type values for this dimension
3161 --
3162 l_balance_type_id1 := get_balance_type(p_balance_name1);
3163 l_balance_type_id2 := get_balance_type(p_balance_name2);
3164 l_balance_type_id3 := get_balance_type(p_balance_name3);
3165 l_balance_type_id4 := get_balance_type(p_balance_name4);
3166 l_balance_type_id5 := get_balance_type(p_balance_name5);
3167 l_balance_type_id6 := get_balance_type(p_balance_name6);
3168 l_balance_type_id7 := get_balance_type(p_balance_name7);
3169 l_balance_type_id8 := get_balance_type(p_balance_name8);
3170 l_balance_type_id9 := get_balance_type(p_balance_name9);
3171 l_balance_type_id10:= get_balance_type(p_balance_name10);
3172 l_balance_type_id11:= get_balance_type(p_balance_name11);
3173 l_balance_type_id12:= get_balance_type(p_balance_name12);
3174 open get_values (l_balance_type_id1,
3175 l_balance_type_id2,
3176 l_balance_type_id3,
3177 l_balance_type_id4,
3178 l_balance_type_id5,
3179 l_balance_type_id6,
3180 l_balance_type_id7,
3181 l_balance_type_id8,
3182 l_balance_type_id9,
3183 l_balance_type_id10,
3184 l_balance_type_id11,
3185 l_balance_type_id12);
3186
3187 fetch get_values into p_value1 ,
3188 p_value2 ,
3189 p_value3 ,
3190 p_value4 ,
3191 p_value5 ,
3192 p_value6 ,
3193 p_value7 ,
3194 p_value8 ,
3195 p_value9 ,
3196 p_value10,
3197 p_value11,
3198 p_value12;
3199 close get_values;
3200 --
3201 END us_gp_multiple_gre_qtd;
3202 --
3203 ----------------------------------------------------------------------------------
3204 -- PROCEDURE us_gp_subject_to_tax_gre_ytd
3205 --
3206 -- Description: This procedure returns values for given balance name using
3207 -- the route code for SUBJECT_TO_TAX_GRE_YTD. This is written
3208 -- primarily for The GRE Totals report, in order that group level
3209 -- (By Tax Unit ID) balances are always returned as were found to
3210 -- be much more performant than assignment-level (inc latest balances)
3211 --
3212 -- Maintenance: This procedure must be maintained along with the row in FF_ROUTES for
3213 -- The dimension SUBJECT_TO_TAX_GRE_YTD.
3214 ----------------------------------------------------------------------------------
3215 --
3216 --
3217 PROCEDURE us_gp_subject_to_tax_gre_ytd (p_balance_name1 IN VARCHAR2 ,
3218 p_balance_name2 IN VARCHAR2 ,
3219 p_balance_name3 IN VARCHAR2 ,
3220 p_balance_name4 IN VARCHAR2 ,
3221 p_balance_name5 IN VARCHAR2 ,
3222 p_effective_date IN DATE,
3223 p_tax_unit_id IN NUMBER,
3224 p_value1 OUT NOCOPY NUMBER,
3225 p_value2 OUT NOCOPY NUMBER,
3226 p_value3 OUT NOCOPY NUMBER,
3227 p_value4 OUT NOCOPY NUMBER,
3228 p_value5 OUT NOCOPY NUMBER)
3229 --
3230 IS
3231 --
3232 l_balance_type_id1 number;
3233 l_balance_type_id2 number;
3234 l_balance_type_id3 number;
3235 l_balance_type_id4 number;
3236 l_balance_type_id5 number;
3237 --
3238 cursor get_value (c_balance_type_id1 in number,
3239 c_balance_type_id2 in number,
3240 c_balance_type_id3 in number,
3241 c_balance_type_id4 in number,
3242 c_balance_type_id5 in number,
3243 c_effective_date in date,
3244 c_tax_unit_id in number)
3245 IS
3246 SELECT /* Removed RULE hint. Bug 3331031 */
3247 nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
3248 nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
3249 nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
3250 nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
3251 nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
3252 FROM
3253 pay_balance_feeds_f FEED
3254 , pay_run_result_values TARGET
3255 , pay_run_results RR
3256 , pay_assignment_actions ASSACT
3257 , pay_payroll_actions PACT
3258 where PACT.effective_date between trunc(c_effective_date,'Y')
3259 and c_effective_date
3260 and PACT.action_type in ('R','Q','I','B','V')
3261 /*
3262 and PACT.action_status = 'C'
3263 */
3264 and ASSACT.payroll_action_id = PACT.payroll_action_id
3265 and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
3266 and ASSACT.action_status = 'C'
3267 and RR.assignment_action_id = ASSACT.assignment_action_id
3268 and RR.status in ('P','PA')
3269 and TARGET.run_result_id = RR.run_result_id
3270 and FEED.input_value_id = TARGET.input_value_id
3271 and nvl(TARGET.result_value,'0') <> '0'
3272 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
3273 c_balance_type_id3,c_balance_type_id4,
3274 c_balance_type_id5)
3275 and PACT.effective_date between FEED.effective_start_date
3276 and FEED.effective_end_date
3277 and EXISTS ( select 'x'
3278 from pay_taxability_rules TR,
3279 pay_element_types_f ET
3280 where ET.element_type_id = RR.element_type_id
3281 and PACT.date_earned between ET.effective_start_date
3282 and ET.effective_end_date
3283 and TR.classification_id = ET.classification_id + 0
3284 and TR.tax_category = ET.element_information1
3285 and TR.tax_type = (select bt.tax_type from pay_balance_types bt
3286 where bt.balance_type_id = FEED.balance_type_id)
3287 and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
3288 and nvl(TR.status,'X') <>'D'); -- Bug 3251672
3289
3290
3291 --
3292 BEGIN
3293 --
3294 l_balance_type_id1 := get_balance_type(p_balance_name1);
3295 l_balance_type_id2 := get_balance_type(p_balance_name2);
3296 l_balance_type_id3 := get_balance_type(p_balance_name3);
3297 l_balance_type_id4 := get_balance_type(p_balance_name4);
3298 l_balance_type_id5 := get_balance_type(p_balance_name5);
3299 --
3300 open get_value(l_balance_type_id1,l_balance_type_id2,
3301 l_balance_type_id3,l_balance_type_id4,
3302 l_balance_type_id5,p_effective_date,
3303 p_tax_unit_id);
3304 fetch get_value into p_value1,p_value2,p_value3,p_value4,p_value5;
3305 close get_value;
3306 --
3307 END us_gp_subject_to_tax_gre_ytd;
3308 --
3309 --
3310 ----------------------------------------------------------------------------------
3311 -- PROCEDURE us_gp_subject_to_tax_gre_qtd
3312 --
3313 -- Description: This procedure returns values for given balance name using
3314 -- the route code for SUBJECT_TO_TAX_GRE_QTD. This is written
3315 -- primarily for The GRE Totals report, in order that group level
3316 -- (By Tax Unit ID) balances are always returned as were found to
3317 -- be much more performant than assignment-level (inc latest balances)
3318 --
3319 -- Maintenance: This procedure must be maintained along with the row in FF_ROUTES for
3320 -- The dimension SUBJECT_TO_TAX_GRE_QTD.
3321 ----------------------------------------------------------------------------------
3322 --
3323
3324 PROCEDURE us_gp_subject_to_tax_gre_qtd (p_balance_name1 IN VARCHAR2 ,
3325 p_balance_name2 IN VARCHAR2 ,
3326 p_balance_name3 IN VARCHAR2 ,
3327 p_balance_name4 IN VARCHAR2 ,
3328 p_balance_name5 IN VARCHAR2 ,
3329 p_effective_date IN DATE,
3330 p_tax_unit_id IN NUMBER,
3331 p_value1 OUT NOCOPY NUMBER,
3332 p_value2 OUT NOCOPY NUMBER,
3333 p_value3 OUT NOCOPY NUMBER,
3334 p_value4 OUT NOCOPY NUMBER,
3335 p_value5 OUT NOCOPY NUMBER)
3336 --
3337 IS
3338 --
3339 l_balance_type_id1 number;
3340 l_balance_type_id2 number;
3341 l_balance_type_id3 number;
3342 l_balance_type_id4 number;
3343 l_balance_type_id5 number;
3344 --
3345 cursor get_value (c_balance_type_id1 in number,
3346 c_balance_type_id2 in number,
3347 c_balance_type_id3 in number,
3348 c_balance_type_id4 in number,
3349 c_balance_type_id5 in number,
3350 c_effective_date in date,
3351 c_tax_unit_id in number)
3352 IS
3353 SELECT /* Removed RULE hint. Bug 3331031 */
3354 nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
3355 nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
3356 nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
3357 nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
3358 nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
3359 FROM
3360 pay_balance_feeds_f FEED
3361 , pay_run_result_values TARGET
3362 , pay_run_results RR
3363 , pay_assignment_actions ASSACT
3364 , pay_payroll_actions PACT
3365 where PACT.effective_date between trunc(c_effective_date,'Q')
3366 and c_effective_date
3367 and PACT.action_type in ('R','Q','I','B','V')
3368 /*
3369 and PACT.action_status = 'C'
3370 */
3371 and ASSACT.payroll_action_id = PACT.payroll_action_id
3372 and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
3373 and ASSACT.action_status = 'C'
3374 and RR.assignment_action_id = ASSACT.assignment_action_id
3375 and RR.status in ('P','PA')
3376 and TARGET.run_result_id = RR.run_result_id
3377 and FEED.input_value_id = TARGET.input_value_id
3378 and nvl(TARGET.result_value,'0') <> '0'
3379 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
3380 c_balance_type_id3,c_balance_type_id4,
3381 c_balance_type_id5)
3382 and PACT.effective_date between FEED.effective_start_date
3383 and FEED.effective_end_date
3384 and EXISTS ( select 'x'
3385 from pay_taxability_rules TR,
3386 pay_element_types_f ET
3387 where ET.element_type_id = RR.element_type_id
3388 and PACT.date_earned between ET.effective_start_date
3389 and ET.effective_end_date
3390 and TR.classification_id = ET.classification_id + 0
3391 and TR.tax_category = ET.element_information1
3392 and TR.tax_type = (select bt.tax_type from pay_balance_types bt
3393 where bt.balance_type_id = FEED.balance_type_id)
3394 and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
3395 and nvl(TR.status,'X') <>'D'); -- Bug 3251672
3396
3397 --
3398 BEGIN
3399 --
3400 l_balance_type_id1 := get_balance_type(p_balance_name1);
3401 l_balance_type_id2 := get_balance_type(p_balance_name2);
3402 l_balance_type_id3 := get_balance_type(p_balance_name3);
3403 l_balance_type_id4 := get_balance_type(p_balance_name4);
3404 l_balance_type_id5 := get_balance_type(p_balance_name5);
3405 --
3406 open get_value(l_balance_type_id1,l_balance_type_id2,
3407 l_balance_type_id3,l_balance_type_id4,
3408 l_balance_type_id5,p_effective_date,
3409 p_tax_unit_id);
3410 fetch get_value into p_value1,p_value2,p_value3,p_value4,p_value5;
3411 close get_value;
3412 --
3413 END us_gp_subject_to_tax_gre_qtd;
3414
3415 -----------------------------------------------------------------------------------------
3416 -- PROCEDURE us_gp_gre_jd_ytd
3417 --
3418 -- DESCRIPTION: This procedure performs a multiple balance-type decode fetch
3419 -- from the GRE_JD_YTD route, which is used for State-Level balance
3420 -- reporting. It can return up to 7 balance values. This was coded
3421 -- originally for performance fixing to the GRE Totals Report.
3422 --
3423 -- Maintenance: This should be dual-maintained with the row in FF_ROUTES for
3424 -- GRE_JD_YTD dimension, although note slight changes to main where
3425 -- clause to allow for multiple-decoding.
3426 --
3427 -----------------------------------------------------------------------------------------
3428 PROCEDURE us_gp_gre_jd_ytd (p_balance_name1 IN VARCHAR2 ,
3429 p_balance_name2 IN VARCHAR2 ,
3430 p_balance_name3 IN VARCHAR2 ,
3431 p_balance_name4 IN VARCHAR2 ,
3432 p_balance_name5 IN VARCHAR2 ,
3433 p_balance_name6 IN VARCHAR2 ,
3434 p_balance_name7 IN VARCHAR2 ,
3435 p_effective_date IN DATE,
3436 p_tax_unit_id IN NUMBER,
3437 p_state_code IN VARCHAR2,
3438 p_value1 OUT NOCOPY NUMBER,
3439 p_value2 OUT NOCOPY NUMBER,
3440 p_value3 OUT NOCOPY NUMBER,
3441 p_value4 OUT NOCOPY NUMBER,
3442 p_value5 OUT NOCOPY NUMBER,
3443 p_value6 OUT NOCOPY NUMBER,
3444 p_value7 OUT NOCOPY NUMBER)
3445 --
3446 IS
3447 --
3448 l_balance_type_id1 number;
3449 l_balance_type_id2 number;
3450 l_balance_type_id3 number;
3451 l_balance_type_id4 number;
3452 l_balance_type_id5 number;
3453 l_balance_type_id6 number;
3454 l_balance_type_id7 number;
3455 --
3456 cursor get_state_level_value (c_balance_type_id1 in number,
3457 c_balance_type_id2 in number,
3458 c_balance_type_id3 in number,
3459 c_balance_type_id4 in number,
3460 c_balance_type_id5 in number,
3461 c_balance_type_id6 in number,
3462 c_balance_type_id7 in number,
3463 c_effective_date in date,
3464 c_tax_unit_id in number,
3465 c_state_code in varchar2)
3466 is
3467 SELECT /* Removed RULE hint. Bug 3331031 */
3468 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
3469 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
3470 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
3471 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
3472 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
3473 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
3474 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
3475 FROM
3476 pay_balance_feeds_f FEED
3477 , pay_run_result_values TARGET
3478 , pay_run_results RR
3479 , pay_assignment_actions ASSACT
3480 , pay_payroll_actions PACT
3481 , (select distinct puar.assignment_id assignment_id
3482 from pay_us_asg_reporting puar
3483 where puar.tax_unit_id = c_tax_unit_id
3484 and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
3485 --
3486 where PACT.effective_date between trunc(c_effective_date,'Y')
3487 and c_effective_date
3488 and PACT.action_type in ('R','Q','I','B','V')
3489 /*
3490 and PACT.action_status = 'C'
3491 */
3492 and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
3493 c_balance_type_id3 , c_balance_type_id4 ,
3494 c_balance_type_id5 , c_balance_type_id6 ,
3495 c_balance_type_id7 )
3496 and PACT.effective_date between FEED.effective_start_date
3497 and FEED.effective_end_date
3498 and ASSACT.payroll_action_id = PACT.payroll_action_id
3499 and ASSACT.assignment_id = ASGRPT.assignment_id
3500 and ASSACT.tax_unit_id = c_tax_unit_id
3501 and ASSACT.action_status = 'C'
3502 and RR.assignment_action_id = ASSACT.assignment_action_id
3503 and RR.status in ('P','PA')
3504 and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
3505 and TARGET.run_result_id = RR.run_result_id
3506 and FEED.input_value_id = TARGET.input_value_id
3507 and nvl(TARGET.result_value,'0') <> '0';
3508 --
3509 BEGIN --us_gp_gre_jd_ytd
3510 --
3511 l_balance_type_id1 := get_balance_type(p_balance_name1);
3512 l_balance_type_id2 := get_balance_type(p_balance_name2);
3513 l_balance_type_id3 := get_balance_type(p_balance_name3);
3514 l_balance_type_id4 := get_balance_type(p_balance_name4);
3515 l_balance_type_id5 := get_balance_type(p_balance_name5);
3516 l_balance_type_id6 := get_balance_type(p_balance_name6);
3517 l_balance_type_id7 := get_balance_type(p_balance_name7);
3518
3519 --
3520 open get_state_level_value(l_balance_type_id1,l_balance_type_id2,
3521 l_balance_type_id3,l_balance_type_id4,
3522 l_balance_type_id5,l_balance_type_id6,
3523 l_balance_type_id7,p_effective_date, p_tax_unit_id, p_state_code);
3524 fetch get_state_level_value into p_value1,p_value2,p_value3,p_value4,p_value5,p_value6,p_value7;
3525 close get_state_level_value;
3526 --
3527 --
3528 END us_gp_gre_jd_ytd;
3529 -----------------------------------------------------------------------------------------
3530 -- PROCEDURE us_gp_gre_jd_qtd
3531 --
3532 -- DESCRIPTION: This procedure performs a multiple balance-type decode fetch
3533 -- from the GRE_JD_QTD route, which is used for State-Level balance
3534 -- reporting. It can return up to 7 balance values. This was coded
3535 -- originally for performance fixing to the GRE Totals Report.
3536 --
3537 -- Maintenance: This should be dual-maintained with the row in FF_ROUTES for
3538 -- GRE_JD_QTD dimension, although note slight changes to main where
3539 -- clause to allow for multiple-decoding.
3540 --
3541 -----------------------------------------------------------------------------------------
3542 PROCEDURE us_gp_gre_jd_qtd (p_balance_name1 IN VARCHAR2 ,
3543 p_balance_name2 IN VARCHAR2 ,
3544 p_balance_name3 IN VARCHAR2 ,
3545 p_balance_name4 IN VARCHAR2 ,
3546 p_balance_name5 IN VARCHAR2 ,
3547 p_balance_name6 IN VARCHAR2 ,
3548 p_balance_name7 IN VARCHAR2 ,
3549 p_effective_date IN DATE,
3550 p_tax_unit_id IN NUMBER,
3551 p_state_code IN VARCHAR2,
3552 p_value1 OUT NOCOPY NUMBER,
3553 p_value2 OUT NOCOPY NUMBER,
3554 p_value3 OUT NOCOPY NUMBER,
3555 p_value4 OUT NOCOPY NUMBER,
3556 p_value5 OUT NOCOPY NUMBER,
3557 p_value6 OUT NOCOPY NUMBER,
3558 p_value7 OUT NOCOPY NUMBER)
3559 --
3560 IS
3561 --
3562 l_balance_type_id1 number;
3563 l_balance_type_id2 number;
3564 l_balance_type_id3 number;
3565 l_balance_type_id4 number;
3566 l_balance_type_id5 number;
3567 l_balance_type_id6 number;
3568 l_balance_type_id7 number;
3569 --
3570 cursor get_state_level_value (c_balance_type_id1 in number,
3571 c_balance_type_id2 in number,
3572 c_balance_type_id3 in number,
3573 c_balance_type_id4 in number,
3574 c_balance_type_id5 in number,
3575 c_balance_type_id6 in number,
3576 c_balance_type_id7 in number,
3577 c_effective_date in date,
3578 c_tax_unit_id in number,
3579 c_state_code in varchar2)
3580 is
3581 SELECT /* Removed RULE hint. Bug 3331031 */
3582 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
3583 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
3584 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
3585 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
3586 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
3587 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
3588 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
3589 FROM
3590 pay_balance_feeds_f FEED
3591 , pay_run_result_values TARGET
3592 , pay_run_results RR
3593 , pay_assignment_actions ASSACT
3594 , pay_payroll_actions PACT
3595 , (select distinct puar.assignment_id assignment_id
3596 from pay_us_asg_reporting puar
3597 where puar.tax_unit_id = c_tax_unit_id
3598 and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
3599 --
3600 where PACT.effective_date between trunc(c_effective_date,'Q')
3601 and c_effective_date
3602 and PACT.action_type in ('R','Q','I','B','V')
3603 /*
3604 and PACT.action_status = 'C'
3605 */
3606 and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
3607 c_balance_type_id3 , c_balance_type_id4 ,
3608 c_balance_type_id5 , c_balance_type_id6 ,
3609 c_balance_type_id7 )
3610 and PACT.effective_date between FEED.effective_start_date
3611 and FEED.effective_end_date
3612 and ASSACT.payroll_action_id = PACT.payroll_action_id
3613 and ASSACT.assignment_id = ASGRPT.assignment_id
3614 and ASSACT.tax_unit_id = c_tax_unit_id
3615 and ASSACT.action_status = 'C'
3616 and RR.assignment_action_id = ASSACT.assignment_action_id
3617 and RR.status in ('P','PA')
3618 and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
3619 and TARGET.run_result_id = RR.run_result_id
3620 and FEED.input_value_id = TARGET.input_value_id
3621 and nvl(TARGET.result_value,'0') <> '0';
3622 --
3623 BEGIN --us_gp_gre_jd_qtd
3624 --
3625 l_balance_type_id1 := get_balance_type(p_balance_name1);
3626 l_balance_type_id2 := get_balance_type(p_balance_name2);
3627 l_balance_type_id3 := get_balance_type(p_balance_name3);
3628 l_balance_type_id4 := get_balance_type(p_balance_name4);
3629 l_balance_type_id5 := get_balance_type(p_balance_name5);
3630 l_balance_type_id6 := get_balance_type(p_balance_name6);
3631 l_balance_type_id7 := get_balance_type(p_balance_name7);
3632
3633 --
3634 open get_state_level_value(l_balance_type_id1,l_balance_type_id2,
3635 l_balance_type_id3,l_balance_type_id4,
3636 l_balance_type_id5,l_balance_type_id6,
3637 l_balance_type_id7,p_effective_date, p_tax_unit_id, p_state_code);
3638 fetch get_state_level_value into p_value1,p_value2,p_value3,p_value4,p_value5,p_value6,p_value7;
3639 close get_state_level_value;
3640 --
3641 --
3642 END us_gp_gre_jd_qtd;
3643 --
3644 ---------------------------------------------------------------------------------------
3645 -- PROCEDURE us_gp_multiple_gre_mtd
3646 --
3647 -- Description: This procedure is passed up to ten balances (using balance name).
3648 -- It calculates the GRE_MTD dimension figure of the balance(s) at
3649 -- GROUP LEVEL only, as at the effective date (also passed in).
3650 -- The route code for GRE_MTD is copied from the correspoding
3651 -- row in FF_ROUTES, plus the PAY_BALANCE_TYPES table is added
3652 -- so that balance names can be used to match on balance feeds
3653 -- for this balance. This method of multiple-decode select means
3654 -- that selecting 10 balances takes the same amount of time as selecting
3655 -- a single balance in the previous implementation.
3656 --
3657 -- Written primarily for GRE Totals report, but can be used by other
3658 -- processes due to the slightly more generic interface. The normal
3659 -- balance retrieval mechanism bypassed as assignment-level route
3660 -- (and latest balances) always used which is not performant for
3661 -- this purpose.
3662 --
3663 -- Maintenance: The cursor should be dual maintained with the row for GRE_MTD in
3664 -- FF_ROUTES.
3665 ---------------------------------------------------------------------------------------
3666 procedure us_gp_multiple_gre_mtd (p_tax_unit_id IN NUMBER,
3667 p_effective_date IN DATE,
3668 p_balance_name1 IN VARCHAR2 ,
3669 p_balance_name2 IN VARCHAR2 ,
3670 p_balance_name3 IN VARCHAR2 ,
3671 p_balance_name4 IN VARCHAR2 ,
3672 p_balance_name5 IN VARCHAR2 ,
3673 p_balance_name6 IN VARCHAR2 ,
3674 p_balance_name7 IN VARCHAR2 ,
3675 p_balance_name8 IN VARCHAR2 ,
3676 p_balance_name9 IN VARCHAR2 ,
3677 p_balance_name10 IN VARCHAR2 ,
3678 p_value1 OUT NOCOPY NUMBER,
3679 p_value2 OUT NOCOPY NUMBER,
3680 p_value3 OUT NOCOPY NUMBER,
3681 p_value4 OUT NOCOPY NUMBER,
3682 p_value5 OUT NOCOPY NUMBER,
3683 p_value6 OUT NOCOPY NUMBER,
3684 p_value7 OUT NOCOPY NUMBER,
3685 p_value8 OUT NOCOPY NUMBER,
3686 p_value9 OUT NOCOPY NUMBER,
3687 p_value10 OUT NOCOPY NUMBER)
3688 IS
3689 --
3690 l_balance_type_id1 number;
3691 l_balance_type_id2 number;
3692 l_balance_type_id3 number;
3693 l_balance_type_id4 number;
3694 l_balance_type_id5 number;
3695 l_balance_type_id6 number;
3696 l_balance_type_id7 number;
3697 l_balance_type_id8 number;
3698 l_balance_type_id9 number;
3699 l_balance_type_id10 number;
3700
3701 cursor get_values (c_balance_type_id1 in number,
3702 c_balance_type_id2 in number,
3703 c_balance_type_id3 in number,
3704 c_balance_type_id4 in number,
3705 c_balance_type_id5 in number,
3706 c_balance_type_id6 in number,
3707 c_balance_type_id7 in number,
3708 c_balance_type_id8 in number,
3709 c_balance_type_id9 in number,
3710 c_balance_type_id10 in number) is
3711 SELECT /* Removed RULE hint. Bug 3331031 */
3712 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
3713 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
3714 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
3715 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
3716 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
3717 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
3718 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
3719 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
3720 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
3721 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0)
3722 FROM pay_balance_feeds_f FEED
3723 , pay_run_result_values TARGET
3724 , pay_run_results RR
3725 , pay_assignment_actions ASSACT
3726 , pay_payroll_actions PACT
3727 where PACT.effective_date between trunc(p_effective_date,'MON')
3728 and p_effective_date
3729 and PACT.action_type in ('R','Q','I','B','V')
3730 /*
3731 and PACT.action_status = 'C'
3732 */
3733 and ASSACT.payroll_action_id = PACT.payroll_action_id
3734 and ASSACT.action_status = 'C'
3735 and ASSACT.tax_unit_id = p_tax_unit_id
3736 and RR.assignment_action_id = ASSACT.assignment_action_id
3737 and RR.status in ('P','PA')
3738 and TARGET.run_result_id = RR.run_result_id
3739 and nvl(TARGET.result_value,'0') <> '0'
3740 and FEED.input_value_id = TARGET.input_value_id
3741 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
3742 c_balance_type_id3,c_balance_type_id4,
3743 c_balance_type_id5,c_balance_type_id6,
3744 c_balance_type_id7,c_balance_type_id8,
3745 c_balance_type_id9,c_balance_type_id10)
3746 and PACT.effective_date between FEED.effective_start_date
3747 and FEED.effective_end_date;
3748 --
3749 BEGIN -- us_multiple_gre_mtd
3750 --
3751 -- Get Multiple balance type values for this dimension
3752 --
3753 l_balance_type_id1 := get_balance_type(p_balance_name1);
3754 l_balance_type_id2 := get_balance_type(p_balance_name2);
3755 l_balance_type_id3 := get_balance_type(p_balance_name3);
3756 l_balance_type_id4 := get_balance_type(p_balance_name4);
3757 l_balance_type_id5 := get_balance_type(p_balance_name5);
3758 l_balance_type_id6 := get_balance_type(p_balance_name6);
3759 l_balance_type_id7 := get_balance_type(p_balance_name7);
3760 l_balance_type_id8 := get_balance_type(p_balance_name8);
3761 l_balance_type_id9 := get_balance_type(p_balance_name9);
3762 l_balance_type_id10:= get_balance_type(p_balance_name10);
3763
3764 open get_values (l_balance_type_id1,
3765 l_balance_type_id2,
3766 l_balance_type_id3,
3767 l_balance_type_id4,
3768 l_balance_type_id5,
3769 l_balance_type_id6,
3770 l_balance_type_id7,
3771 l_balance_type_id8,
3772 l_balance_type_id9,
3773 l_balance_type_id10);
3774
3775 fetch get_values into p_value1 ,
3776 p_value2 ,
3777 p_value3 ,
3778 p_value4 ,
3779 p_value5 ,
3780 p_value6 ,
3781 p_value7 ,
3782 p_value8 ,
3783 p_value9 ,
3784 p_value10;
3785 close get_values;
3786 --
3787 END us_gp_multiple_gre_mtd;
3788 --
3789 ---------------------------------------------------------------------------------------
3790 -- PROCEDURE us_gp_multiple_gre_ctd
3791 --
3792 -- Description: This procedure is passed up to ten balances (using balance name).
3793 -- It calculates the GRE_CTD dimension figure of the balance(s) at
3794 -- GROUP LEVEL only, as at the effective date (also passed in).
3795 -- The route code for GRE_CTD is copied from the correspoding
3796 -- row in FF_ROUTES, plus the PAY_BALANCE_TYPES table is added
3797 -- so that balance names can be used to match on balance feeds
3798 -- for this balance. This method of multiple-decode select means
3799 -- that selecting 10 balances takes the same amount of time as selecting
3800 -- a single balance in the previous implementation.
3801 --
3802 -- Written primarily for GRE Totals report, but can be used by other
3803 -- processes due to the slightly more generic interface. The normal
3804 -- balance retrieval mechanism bypassed as assignment-level route
3805 -- (and latest balances) always used which is not performant for
3806 -- this purpose.
3807 --
3808 -- Maintenance: The cursor should be dual maintained with the row for GRE_CTD in
3809 -- FF_ROUTES.
3810 ---------------------------------------------------------------------------------------
3811 procedure us_gp_multiple_gre_ctd (p_tax_unit_id IN NUMBER,
3812 p_start_date IN DATE,
3813 p_effective_date IN DATE,
3814 p_balance_name1 IN VARCHAR2 ,
3815 p_balance_name2 IN VARCHAR2 ,
3816 p_balance_name3 IN VARCHAR2 ,
3817 p_balance_name4 IN VARCHAR2 ,
3818 p_balance_name5 IN VARCHAR2 ,
3819 p_balance_name6 IN VARCHAR2 ,
3820 p_balance_name7 IN VARCHAR2 ,
3821 p_balance_name8 IN VARCHAR2 ,
3822 p_balance_name9 IN VARCHAR2 ,
3823 p_balance_name10 IN VARCHAR2 ,
3824 p_value1 OUT NOCOPY NUMBER,
3825 p_value2 OUT NOCOPY NUMBER,
3826 p_value3 OUT NOCOPY NUMBER,
3827 p_value4 OUT NOCOPY NUMBER,
3828 p_value5 OUT NOCOPY NUMBER,
3829 p_value6 OUT NOCOPY NUMBER,
3830 p_value7 OUT NOCOPY NUMBER,
3831 p_value8 OUT NOCOPY NUMBER,
3832 p_value9 OUT NOCOPY NUMBER,
3833 p_value10 OUT NOCOPY NUMBER)
3834 IS
3835 --
3836 l_balance_type_id1 number;
3837 l_balance_type_id2 number;
3838 l_balance_type_id3 number;
3839 l_balance_type_id4 number;
3840 l_balance_type_id5 number;
3841 l_balance_type_id6 number;
3842 l_balance_type_id7 number;
3843 l_balance_type_id8 number;
3844 l_balance_type_id9 number;
3845 l_balance_type_id10 number;
3846
3847 cursor get_values (c_balance_type_id1 in number,
3848 c_balance_type_id2 in number,
3849 c_balance_type_id3 in number,
3850 c_balance_type_id4 in number,
3851 c_balance_type_id5 in number,
3852 c_balance_type_id6 in number,
3853 c_balance_type_id7 in number,
3854 c_balance_type_id8 in number,
3855 c_balance_type_id9 in number,
3856 c_balance_type_id10 in number) is
3857 SELECT /* Removed RULE hint. Bug 3331031 */
3858 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
3859 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
3860 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
3861 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
3862 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
3863 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
3864 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
3865 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
3866 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
3867 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0)
3868 FROM pay_balance_feeds_f FEED
3869 , pay_run_result_values TARGET
3870 , pay_run_results RR
3871 , pay_assignment_actions ASSACT
3872 , pay_payroll_actions PACT
3873 where PACT.effective_date between p_start_date
3874 and p_effective_date
3875 and PACT.action_type in ('R','Q','I','B','V')
3876 /*
3877 and PACT.action_status = 'C'
3878 */
3879 and ASSACT.payroll_action_id = PACT.payroll_action_id
3880 and ASSACT.action_status = 'C'
3881 and ASSACT.tax_unit_id = p_tax_unit_id
3882 and RR.assignment_action_id = ASSACT.assignment_action_id
3883 and RR.status in ('P','PA')
3884 and TARGET.run_result_id = RR.run_result_id
3885 and nvl(TARGET.result_value,'0') <> '0'
3886 and FEED.input_value_id = TARGET.input_value_id
3887 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
3888 c_balance_type_id3,c_balance_type_id4,
3889 c_balance_type_id5,c_balance_type_id6,
3890 c_balance_type_id7,c_balance_type_id8,
3891 c_balance_type_id9,c_balance_type_id10)
3892 and PACT.effective_date between FEED.effective_start_date
3893 and FEED.effective_end_date;
3894 --
3895 BEGIN -- us_multiple_gre_ctd
3896 --
3897 -- Get Multiple balance type values for this dimension
3898 --
3899 l_balance_type_id1 := get_balance_type(p_balance_name1);
3900 l_balance_type_id2 := get_balance_type(p_balance_name2);
3901 l_balance_type_id3 := get_balance_type(p_balance_name3);
3902 l_balance_type_id4 := get_balance_type(p_balance_name4);
3903 l_balance_type_id5 := get_balance_type(p_balance_name5);
3904 l_balance_type_id6 := get_balance_type(p_balance_name6);
3905 l_balance_type_id7 := get_balance_type(p_balance_name7);
3906 l_balance_type_id8 := get_balance_type(p_balance_name8);
3907 l_balance_type_id9 := get_balance_type(p_balance_name9);
3908 l_balance_type_id10:= get_balance_type(p_balance_name10);
3909
3910 open get_values (l_balance_type_id1,
3911 l_balance_type_id2,
3912 l_balance_type_id3,
3913 l_balance_type_id4,
3914 l_balance_type_id5,
3915 l_balance_type_id6,
3916 l_balance_type_id7,
3917 l_balance_type_id8,
3918 l_balance_type_id9,
3919 l_balance_type_id10);
3920
3921 fetch get_values into p_value1 ,
3922 p_value2 ,
3923 p_value3 ,
3924 p_value4 ,
3925 p_value5 ,
3926 p_value6 ,
3927 p_value7 ,
3928 p_value8 ,
3929 p_value9 ,
3930 p_value10;
3931 close get_values;
3932 --
3933 END us_gp_multiple_gre_ctd;
3934 --
3935 ----------------------------------------------------------------------------------
3936 -- PROCEDURE us_gp_subject_to_tax_gre_mtd
3937 --
3938 -- Description: This procedure returns values for given balance name using
3939 -- the route code for SUBJECT_TO_TAX_GRE_MTD. This is written
3940 -- primarily for The GRE Totals report, in order that group level
3941 -- (By Tax Unit ID) balances are always returned as were found to
3942 -- be much more performant than assignment-level (inc latest balances)
3943 --
3944 -- Maintenance: This procedure must be maintained along with the row in FF_ROUTES for
3945 -- The dimension SUBJECT_TO_TAX_GRE_MTD.
3946 ----------------------------------------------------------------------------------
3947 --
3948
3949 PROCEDURE us_gp_subject_to_tax_gre_mtd (p_balance_name1 IN VARCHAR2 ,
3950 p_balance_name2 IN VARCHAR2 ,
3951 p_balance_name3 IN VARCHAR2 ,
3952 p_balance_name4 IN VARCHAR2 ,
3953 p_balance_name5 IN VARCHAR2 ,
3954 p_effective_date IN DATE,
3955 p_tax_unit_id IN NUMBER,
3956 p_value1 OUT NOCOPY NUMBER,
3957 p_value2 OUT NOCOPY NUMBER,
3958 p_value3 OUT NOCOPY NUMBER,
3959 p_value4 OUT NOCOPY NUMBER,
3960 p_value5 OUT NOCOPY NUMBER)
3961 --
3962 IS
3963 --
3964 l_balance_type_id1 number;
3965 l_balance_type_id2 number;
3966 l_balance_type_id3 number;
3967 l_balance_type_id4 number;
3968 l_balance_type_id5 number;
3969 --
3970 cursor get_value (c_balance_type_id1 in number,
3971 c_balance_type_id2 in number,
3972 c_balance_type_id3 in number,
3973 c_balance_type_id4 in number,
3974 c_balance_type_id5 in number,
3975 c_effective_date in date,
3976 c_tax_unit_id in number)
3977 IS
3978 SELECT /* Removed RULE hint. Bug 3331031 */
3979 nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
3980 nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
3981 nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
3982 nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
3983 nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
3984 FROM
3985 pay_balance_feeds_f FEED
3986 , pay_run_result_values TARGET
3987 , pay_run_results RR
3988 , pay_assignment_actions ASSACT
3989 , pay_payroll_actions PACT
3990 where PACT.effective_date between trunc(c_effective_date,'MON')
3991 and c_effective_date
3992 and PACT.action_type in ('R','Q','I','B','V')
3993 /*
3994 and PACT.action_status = 'C'
3995 */
3996 and ASSACT.payroll_action_id = PACT.payroll_action_id
3997 and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
3998 and ASSACT.action_status = 'C'
3999 and RR.assignment_action_id = ASSACT.assignment_action_id
4000 and RR.status in ('P','PA')
4001 and TARGET.run_result_id = RR.run_result_id
4002 and FEED.input_value_id = TARGET.input_value_id
4003 and nvl(TARGET.result_value,'0') <> '0'
4004 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
4005 c_balance_type_id3,c_balance_type_id4,
4006 c_balance_type_id5)
4007 and PACT.effective_date between FEED.effective_start_date
4008 and FEED.effective_end_date
4009 and EXISTS ( select 'x'
4010 from pay_taxability_rules TR,
4011 pay_element_types_f ET
4012 where ET.element_type_id = RR.element_type_id
4013 and PACT.date_earned between ET.effective_start_date
4014 and ET.effective_end_date
4015 and TR.classification_id = ET.classification_id + 0
4016 and TR.tax_category = ET.element_information1
4017 and TR.tax_type = (select bt.tax_type from pay_balance_types bt
4018 where bt.balance_type_id = FEED.balance_type_id)
4019 and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
4020 and nvl(TR.status,'X') <>'D') ; -- Bug 3251672
4021
4022
4023 --
4024 BEGIN
4025 --
4026 l_balance_type_id1 := get_balance_type(p_balance_name1);
4027 l_balance_type_id2 := get_balance_type(p_balance_name2);
4028 l_balance_type_id3 := get_balance_type(p_balance_name3);
4029 l_balance_type_id4 := get_balance_type(p_balance_name4);
4030 l_balance_type_id5 := get_balance_type(p_balance_name5);
4031 --
4032 open get_value(l_balance_type_id1,l_balance_type_id2,
4033 l_balance_type_id3,l_balance_type_id4,
4034 l_balance_type_id5,p_effective_date,
4035 p_tax_unit_id);
4036 fetch get_value into p_value1,p_value2,p_value3,p_value4,p_value5;
4037 close get_value;
4038 --
4039 END us_gp_subject_to_tax_gre_mtd;
4040 --
4041 ----------------------------------------------------------------------------------
4042 -- PROCEDURE us_gp_subject_to_tax_gre_ctd
4043 --
4044 -- Description: This procedure returns values for given balance name using
4045 -- the route code for SUBJECT_TO_TAX_GRE_CTD. This is written
4046 -- primarily for The GRE Totals report, in order that group level
4047 -- (By Tax Unit ID) balances are always returned as were found to
4048 -- be much more performant than assignment-level (inc latest balances)
4049 --
4050 -- Maintenance: This procedure must be maintained along with the row in FF_ROUTES for
4051 -- The dimension SUBJECT_TO_TAX_GRE_CTD.
4052 ----------------------------------------------------------------------------------
4053 --
4054
4055 PROCEDURE us_gp_subject_to_tax_gre_ctd (p_balance_name1 IN VARCHAR2 ,
4056 p_balance_name2 IN VARCHAR2 ,
4057 p_balance_name3 IN VARCHAR2 ,
4058 p_balance_name4 IN VARCHAR2 ,
4059 p_balance_name5 IN VARCHAR2 ,
4060 p_start_date IN DATE,
4061 p_effective_date IN DATE,
4062 p_tax_unit_id IN NUMBER,
4063 p_value1 OUT NOCOPY NUMBER,
4064 p_value2 OUT NOCOPY NUMBER,
4065 p_value3 OUT NOCOPY NUMBER,
4066 p_value4 OUT NOCOPY NUMBER,
4067 p_value5 OUT NOCOPY NUMBER)
4068 --
4069 IS
4070 --
4071 l_balance_type_id1 number;
4072 l_balance_type_id2 number;
4073 l_balance_type_id3 number;
4074 l_balance_type_id4 number;
4075 l_balance_type_id5 number;
4076 --
4077 cursor get_value (c_balance_type_id1 in number,
4078 c_balance_type_id2 in number,
4079 c_balance_type_id3 in number,
4080 c_balance_type_id4 in number,
4081 c_balance_type_id5 in number,
4082 c_start_date in date,
4083 c_effective_date in date,
4084 c_tax_unit_id in number)
4085 IS
4086 SELECT /* Removed RULE hint. Bug 3331031 */
4087 nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
4088 nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
4089 nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
4090 nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
4091 nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
4092 FROM
4093 pay_balance_feeds_f FEED
4094 , pay_run_result_values TARGET
4095 , pay_run_results RR
4096 , pay_assignment_actions ASSACT
4097 , pay_payroll_actions PACT
4098 where PACT.effective_date between c_start_date
4099 and c_effective_date
4100 and PACT.action_type in ('R','Q','I','B','V')
4101 /*
4102 and PACT.action_status = 'C'
4103 */
4104 and ASSACT.payroll_action_id = PACT.payroll_action_id
4105 and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
4106 and ASSACT.action_status = 'C'
4107 and RR.assignment_action_id = ASSACT.assignment_action_id
4108 and RR.status in ('P','PA')
4109 and TARGET.run_result_id = RR.run_result_id
4110 and FEED.input_value_id = TARGET.input_value_id
4111 and nvl(TARGET.result_value,'0') <> '0'
4112 and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
4113 c_balance_type_id3,c_balance_type_id4,
4114 c_balance_type_id5)
4115 and PACT.effective_date between FEED.effective_start_date
4116 and FEED.effective_end_date
4117 and EXISTS ( select 'x'
4118 from pay_taxability_rules TR,
4119 pay_element_types_f ET
4120 where ET.element_type_id = RR.element_type_id
4121 and PACT.date_earned between ET.effective_start_date
4122 and ET.effective_end_date
4123 and TR.classification_id = ET.classification_id + 0
4124 and TR.tax_category = ET.element_information1
4125 and TR.tax_type = (select bt.tax_type from pay_balance_types bt
4126 where bt.balance_type_id = FEED.balance_type_id)
4127 and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
4128 and nvl(TR.status,'X') <>'D') ; -- Bug 3251672
4129
4130
4131 --
4132 BEGIN
4133 --
4134 l_balance_type_id1 := get_balance_type(p_balance_name1);
4135 l_balance_type_id2 := get_balance_type(p_balance_name2);
4136 l_balance_type_id3 := get_balance_type(p_balance_name3);
4137 l_balance_type_id4 := get_balance_type(p_balance_name4);
4138 l_balance_type_id5 := get_balance_type(p_balance_name5);
4139 --
4140 open get_value(l_balance_type_id1,l_balance_type_id2,
4141 l_balance_type_id3,l_balance_type_id4,
4142 l_balance_type_id5,p_start_date,
4143 p_effective_date,p_tax_unit_id);
4144 fetch get_value into p_value1,p_value2,p_value3,p_value4,p_value5;
4145 close get_value;
4146 --
4147 END us_gp_subject_to_tax_gre_ctd;
4148 --
4149 -----------------------------------------------------------------------------------------
4150 -- PROCEDURE us_gp_gre_jd_mtd
4151 --
4152 -- DESCRIPTION: This procedure performs a multiple balance-type decode fetch
4153 -- from the GRE_JD_QTD route, which is used for State-Level balance
4154 -- reporting. It can return up to 7 balance values. This was coded
4155 -- originally for performance fixing to the GRE Totals Report.
4156 --
4157 -- Maintenance: This should be dual-maintained with the row in FF_ROUTES for
4158 -- GRE_JD_MTD dimension, although note slight changes to main where
4159 -- clause to allow for multiple-decoding.
4160 --
4161 -----------------------------------------------------------------------------------------
4162 PROCEDURE us_gp_gre_jd_mtd (p_balance_name1 IN VARCHAR2 ,
4163 p_balance_name2 IN VARCHAR2 ,
4164 p_balance_name3 IN VARCHAR2 ,
4165 p_balance_name4 IN VARCHAR2 ,
4166 p_balance_name5 IN VARCHAR2 ,
4167 p_balance_name6 IN VARCHAR2 ,
4168 p_balance_name7 IN VARCHAR2 ,
4169 p_effective_date IN DATE,
4170 p_tax_unit_id IN NUMBER,
4171 p_state_code IN VARCHAR2,
4172 p_value1 OUT NOCOPY NUMBER,
4173 p_value2 OUT NOCOPY NUMBER,
4174 p_value3 OUT NOCOPY NUMBER,
4175 p_value4 OUT NOCOPY NUMBER,
4176 p_value5 OUT NOCOPY NUMBER,
4177 p_value6 OUT NOCOPY NUMBER,
4178 p_value7 OUT NOCOPY NUMBER)
4179 --
4180 IS
4181 --
4182 l_balance_type_id1 number;
4183 l_balance_type_id2 number;
4184 l_balance_type_id3 number;
4185 l_balance_type_id4 number;
4186 l_balance_type_id5 number;
4187 l_balance_type_id6 number;
4188 l_balance_type_id7 number;
4189 --
4190 cursor get_state_level_value (c_balance_type_id1 in number,
4191 c_balance_type_id2 in number,
4192 c_balance_type_id3 in number,
4193 c_balance_type_id4 in number,
4194 c_balance_type_id5 in number,
4195 c_balance_type_id6 in number,
4196 c_balance_type_id7 in number,
4197 c_effective_date in date,
4198 c_tax_unit_id in number,
4199 c_state_code in varchar2)
4200 is
4201 SELECT /* Removed RULE hint. Bug 3331031 */
4202 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
4203 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
4204 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
4205 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
4206 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
4207 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
4208 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
4209 FROM
4210 pay_balance_feeds_f FEED
4211 , pay_run_result_values TARGET
4212 , pay_run_results RR
4213 , pay_assignment_actions ASSACT
4214 , pay_payroll_actions PACT
4215 , (select distinct puar.assignment_id assignment_id
4216 from pay_us_asg_reporting puar
4217 where puar.tax_unit_id = c_tax_unit_id
4218 and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
4219 --
4220 where PACT.effective_date between trunc(c_effective_date,'MON')
4221 and c_effective_date
4222 and PACT.action_type in ('R','Q','I','B','V')
4223 /*
4224 and PACT.action_status = 'C'
4225 */
4226 and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
4227 c_balance_type_id3 , c_balance_type_id4 ,
4228 c_balance_type_id5 , c_balance_type_id6 ,
4229 c_balance_type_id7 )
4230 and PACT.effective_date between FEED.effective_start_date
4231 and FEED.effective_end_date
4232 and ASSACT.payroll_action_id = PACT.payroll_action_id
4233 and ASSACT.assignment_id = ASGRPT.assignment_id
4234 and ASSACT.tax_unit_id = c_tax_unit_id
4235 and ASSACT.action_status = 'C'
4236 and RR.assignment_action_id = ASSACT.assignment_action_id
4237 and RR.status in ('P','PA')
4238 and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
4239 and TARGET.run_result_id = RR.run_result_id
4240 and FEED.input_value_id = TARGET.input_value_id
4241 and nvl(TARGET.result_value,'0') <> '0';
4242 --
4243 BEGIN --us_gp_gre_jd_mtd
4244 --
4245 l_balance_type_id1 := get_balance_type(p_balance_name1);
4246 l_balance_type_id2 := get_balance_type(p_balance_name2);
4247 l_balance_type_id3 := get_balance_type(p_balance_name3);
4248 l_balance_type_id4 := get_balance_type(p_balance_name4);
4249 l_balance_type_id5 := get_balance_type(p_balance_name5);
4250 l_balance_type_id6 := get_balance_type(p_balance_name6);
4251 l_balance_type_id7 := get_balance_type(p_balance_name7);
4252
4253 --
4254 open get_state_level_value(l_balance_type_id1,l_balance_type_id2,
4255 l_balance_type_id3,l_balance_type_id4,
4256 l_balance_type_id5,l_balance_type_id6,
4257 l_balance_type_id7,p_effective_date, p_tax_unit_id, p_state_code);
4258 fetch get_state_level_value into p_value1,p_value2,p_value3,p_value4,p_value5,p_value6,p_value7;
4259 close get_state_level_value;
4260 --
4261 --
4262 END us_gp_gre_jd_mtd;
4263 --
4264 -----------------------------------------------------------------------------------------
4265 -- PROCEDURE us_gp_gre_jd_ctd
4266 --
4267 -- DESCRIPTION: This procedure performs a multiple balance-type decode fetch
4268 -- from the GRE_JD_CTD route, which is used for State-Level balance
4269 -- reporting. It can return up to 7 balance values. This was coded
4270 -- originally for performance fixing to the GRE Totals Report.
4271 --
4272 -- Maintenance: This should be dual-maintained with the row in FF_ROUTES for
4273 -- GRE_JD_CTD dimension, although note slight changes to main where
4274 -- clause to allow for multiple-decoding.
4275 --
4276 -----------------------------------------------------------------------------------------
4277 PROCEDURE us_gp_gre_jd_ctd (p_balance_name1 IN VARCHAR2 ,
4278 p_balance_name2 IN VARCHAR2 ,
4279 p_balance_name3 IN VARCHAR2 ,
4280 p_balance_name4 IN VARCHAR2 ,
4281 p_balance_name5 IN VARCHAR2 ,
4282 p_balance_name6 IN VARCHAR2 ,
4283 p_balance_name7 IN VARCHAR2 ,
4284 p_start_date IN DATE,
4285 p_effective_date IN DATE,
4286 p_tax_unit_id IN NUMBER,
4287 p_state_code IN VARCHAR2,
4288 p_value1 OUT NOCOPY NUMBER,
4289 p_value2 OUT NOCOPY NUMBER,
4290 p_value3 OUT NOCOPY NUMBER,
4291 p_value4 OUT NOCOPY NUMBER,
4292 p_value5 OUT NOCOPY NUMBER,
4293 p_value6 OUT NOCOPY NUMBER,
4294 p_value7 OUT NOCOPY NUMBER)
4295 --
4296 IS
4297 --
4298 l_balance_type_id1 number;
4299 l_balance_type_id2 number;
4300 l_balance_type_id3 number;
4301 l_balance_type_id4 number;
4302 l_balance_type_id5 number;
4303 l_balance_type_id6 number;
4304 l_balance_type_id7 number;
4305 --
4306 cursor get_state_level_value (c_balance_type_id1 in number,
4307 c_balance_type_id2 in number,
4308 c_balance_type_id3 in number,
4309 c_balance_type_id4 in number,
4310 c_balance_type_id5 in number,
4311 c_balance_type_id6 in number,
4312 c_balance_type_id7 in number,
4313 c_start_date in date,
4314 c_effective_date in date,
4315 c_tax_unit_id in number,
4316 c_state_code in varchar2)
4317 is
4318 SELECT /* Removed RULE hint. Bug 3331031 */
4319 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
4320 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
4321 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
4322 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
4323 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
4324 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
4325 nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
4326 FROM
4327 pay_balance_feeds_f FEED
4328 , pay_run_result_values TARGET
4329 , pay_run_results RR
4330 , pay_assignment_actions ASSACT
4331 , pay_payroll_actions PACT
4332 , (select distinct puar.assignment_id assignment_id
4333 from pay_us_asg_reporting puar
4334 where puar.tax_unit_id = c_tax_unit_id
4335 and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
4336 --
4337 where PACT.effective_date between c_start_date
4338 and c_effective_date
4339 and PACT.action_type in ('R','Q','I','B','V')
4340 /*
4341 and PACT.action_status = 'C'
4342 */
4343 and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
4344 c_balance_type_id3 , c_balance_type_id4 ,
4345 c_balance_type_id5 , c_balance_type_id6 ,
4346 c_balance_type_id7 )
4347 and PACT.effective_date between FEED.effective_start_date
4348 and FEED.effective_end_date
4349 and ASSACT.payroll_action_id = PACT.payroll_action_id
4350 and ASSACT.assignment_id = ASGRPT.assignment_id
4351 and ASSACT.tax_unit_id = c_tax_unit_id
4352 and ASSACT.action_status = 'C'
4353 and RR.assignment_action_id = ASSACT.assignment_action_id
4354 and RR.status in ('P','PA')
4355 and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
4356 and TARGET.run_result_id = RR.run_result_id
4357 and FEED.input_value_id = TARGET.input_value_id
4358 and nvl(TARGET.result_value,'0') <> '0';
4359 --
4360 BEGIN --us_gp_gre_jd_ctd
4361 --
4362 l_balance_type_id1 := get_balance_type(p_balance_name1);
4363 l_balance_type_id2 := get_balance_type(p_balance_name2);
4364 l_balance_type_id3 := get_balance_type(p_balance_name3);
4365 l_balance_type_id4 := get_balance_type(p_balance_name4);
4366 l_balance_type_id5 := get_balance_type(p_balance_name5);
4367 l_balance_type_id6 := get_balance_type(p_balance_name6);
4368 l_balance_type_id7 := get_balance_type(p_balance_name7);
4369
4370 --
4371 open get_state_level_value(l_balance_type_id1,l_balance_type_id2,
4372 l_balance_type_id3,l_balance_type_id4,
4373 l_balance_type_id5,l_balance_type_id6,
4374 l_balance_type_id7,p_start_date,
4375 p_effective_date, p_tax_unit_id,
4376 p_state_code);
4377 fetch get_state_level_value into p_value1,p_value2,p_value3,p_value4,p_value5,p_value6,p_value7;
4378 close get_state_level_value;
4379 --
4380 --
4381 END us_gp_gre_jd_ctd;
4382 --
4383 ---------------------------------------------------------------------------------------
4384 BEGIN
4385 --
4386 -- Setup the Quarter To Date dimensions in the Cache.
4387 --
4388 g_dim_tbl_grp(0) := 'GRE_QTD';
4389 g_dim_tbl_asg(0) := 'ASG_GRE_QTD';
4390 g_dim_tbl_jdr(0) := 'N';
4391 g_dim_tbl_crs(0) := ASG_CURSOR0;
4392 g_dim_tbl_vtd(0) := ASG_VDATE_QTD0;
4393 g_dim_tbl_btt(0) := 'Q';
4394 --
4395 -- Setup the Year To Date dimensions in the Cache.
4396 --
4397 g_dim_tbl_grp(1) := 'GRE_YTD';
4398 g_dim_tbl_asg(1) := 'ASG_GRE_YTD';
4399 g_dim_tbl_jdr(1) := 'N';
4400 g_dim_tbl_crs(1) := ASG_CURSOR1;
4401 g_dim_tbl_vtd(1) := ASG_VDATE_YTD0;
4402 g_dim_tbl_btt(1) := 'Y';
4403 --
4404 -- Setup the Subject to Tax Year To Date dimensions in the Cache.
4405 --
4406 g_dim_tbl_grp(2) := 'SUBJECT_TO_TAX_GRE_YTD';
4407 g_dim_tbl_asg(2) := 'SUBJECT_TO_TAX_ASG_GRE_YTD';
4408 g_dim_tbl_jdr(2) := 'N';
4409 g_dim_tbl_crs(2) := ASG_CURSOR1;
4410 g_dim_tbl_vtd(2) := ASG_VDATE_YTD0;
4411 g_dim_tbl_btt(2) := 'Y';
4412 --
4413 -- Setup the Year To Date in Jurisdiction dimensions in the Cache.
4414 --
4415 g_dim_tbl_grp(3) := 'GRE_JD_YTD';
4416 g_dim_tbl_asg(3) := 'ASG_JD_GRE_YTD';
4417 g_dim_tbl_jdr(3) := 'Y';
4418 g_dim_tbl_crs(3) := ASG_CURSOR2;
4419 g_dim_tbl_vtd(3) := ASG_VDATE_YTD0;
4420 g_dim_tbl_btt(3) := 'Y';
4421 -- Set the next free cache space.
4422 g_nxt_free_dim := 4;
4423
4424 end pay_us_taxbal_view_pkg;