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