DBA Data[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;