[Home] [Help]
PACKAGE BODY: APPS.PAYUSUNB_PKG
Source
1 package body PAYUSUNB_PKG as
2 /* $Header: payusunb.pkb 120.19.12020000.3 2012/11/22 09:12:11 nvelaga 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 : PAYUSUNB_PKG
21
22 Description: This package defines the cursors needed to run
23 Unacceptable Tax Balance
24
25
26 Change List
27 -----------
28 Date Name Bug No Vers Description
29 ----------- --------- -------- ----- -----------------------------------
30 29-SEP-1999 mcpham 110.0 created
31 03-DEC-1999 mcpham 115.1 Added fnd_date.canonical_to_date
32 05-JAN-2000 mcpham 115.2 converted latest file from release 11.0
33 22-AUG-2001 tmehra 1158217 115.3 Added new function to check if
34 an employee is Medicare Exempt
35 1709095 Added Generic Pre-Tax enhancements
36 14-NOV-2001 meshah 115.4 now comparing state_code of HI and NY
37 as characters.
38 26-NOV-2001 meshah 115.5 added dbdrv command.
39 26-NOV-2001 meshah 115.6 commenting all the balance calls to
40 401, 125 and dep care.
41 30-NOV-2001 meshah also commenting checking for condition
42 L_fit_ee_gross_earnings <> L_medi_ee_bal
43 +L_fit_ee_125_redns_qtd
44 +L_fit_ee_dep_care_redns_qtd
45 for both qtd and ytd.
46 30-NOV-2001 meshah changed DD-MON to DD-MM for GSCC compliance.
47 21-DEC-2001 meshah 2152217 115.7 Moved one each SDI and SUI conditions
48 in the respective loops for QTD and YTD.
49 25-SEP-2002 tclewis 115.8 Performance improvements to range
50 cursor procedure and associated cursors.
51 18-OCT-2002 tclewis 115.10 Modified the action_creation cursor removing
52 the for update of . . . added a for update
53 on the lock the created assignment_action_id.
54 06-DEC-2002 tclewis 115.11 Added NOCOPY directive and fixed some typo's
55 formatting issue with the 'YTD SUI EE Taxable'
56 and 'YTD SUI ER Taxable' messages
57 25-JUN-2003 vinaraya 2963239 115.13 Added extra check in prc_process_data for medicare
58 and SS balance check.(bug number 2963239)
59 Moved the call to prc_process_data from report to
60 action_creation code. prc_write_data definition has
61 been changed to include two new arguements.
62 30-JUN-2003 vinaraya 3005756 115.14 Modified code for caching and removal of unwanted
63 code as per review comments.
64 01-JUL-2003 vinaraya 3005756 115.15 Changed function fnc_get_tax_limit_rate to include
65 join for start date in the cursor c_sui_sdi_info.
66 03-JUL-2003 vinaraya 3005756 115.16 Included 4 new cursors for state,county,city and
67 school jurisdiction data fetch.Included check for
68 validity of run balances to make use of the new
69 cursors accordingly.
70 Moved state,county,city and school
71 balance checks to inline procedures.
72 08-JUL-2003 vinaraya 3005756 115.17 Restructured entire code to remove repeated code.
73 Removed action interlocking
74 27-AUG-2003 kaverma 3115988 115.18 Added difference calculation for FUTA
75 19-DEC-2003 saurgupt 3291736 115.19 In action_creation, procedure insert_action
76 is removed. Also, if no Unacceptable tax balances
77 are found then a dummy action is created. This will
78 happen only if payroll/prepayments have been run.
79 26-DEC-2003 saurgupt 3316599 115.20 Tax Unit id is added to where condition to decrease
80 the cost of query.
81 06-JAN-2004 sdahiya 3316599 115.21 Modified queries for performance enhancement.
82 24-MAR-2004 fusman 3418991 115.22 Modified cursors c_actions,c_get_latest_asg,
83 c_school_jurisdictions_valid and
84 c_school_jurisdictions.
85 17-NOV-2004 ahanda 3962872 115.23 Changed range code, action creation and
86 enabled RANGE_PERSON_ID.
87 18-NOV-2004 ahanda 115.24 Fixed GSCC issues.
88 18-NOV-2004 ahanda 115.25 Fixed GSCC issues.
89 08-NOV-2007 dduvvuri 6360505 115.26 Performance Improvements for Bug 6360505
90 05-May-2008 Pannapur 6719359 115.27 Reverted the peformance fix
91 01-Jul-2008 Pannapur 7174993 115.28 Perfomance Improvements for bug 7174993
92 21-Jul-2008 Pannapur 7174993 115.29 Perfomance Improvements for bug 7174993(removed the hint added
93 in previous version)
94 10-Jul-2009 emunisek 8665548 115.30 Modified cursor c_sui_sdi_info in function
95 fnc_get_tax_limit_rate to pick a state tax
96 record which is effective on "As of Date"
97 07-Jan-2010 pbalu 8754952 115.31 Added new error condition for Negative Reduced Subject whable
98 06-May-2010 nkjaladi 8606883 115.32 Added new debug statements and Modified Cursor
99 c_state_jurisdictions in pkg procedure
100 prc_process_data
101 29-Jul-2010 emunisek 9872952 115.33 Modified the report that Employees
102 having Federal Exempt from Wage Accumulation
103 will not be verified in Unacceptable Tax Balance
104 Report.Manual verification is required for
105 them and all those employees will be shown
106 at the end of report.
107 11-Aug-2010 emunisek 9872952 115.34 Modified procedure action_creation to consider the
108 Wage Accumulation setting before skipping the
109 Employees.
110 07-OCT-2010 tclewis 9721787 115.35 Modified the calls to check_balance_status to
111 pass L_business_id instead of L_gre_id for the
112 second parameter. 2) modfied the cursors
113 c_state_jurisdictions, c_county_jurisdictions,
114 c_city_jurisdictions, c_school_jurisdictions.
115 Use the max of max(assignment effective_end_date)
116 or adjustment date when fetching the jurisdictions.
117 26-Jan-2011 rosuri 10350917 115.36 Modified the procedure prc_state_balances
118 so that L_first_half_rate gets the value from
119 SUI ER Experience Rate 2 and L_second_half_rate
120 gets the value from SUI ER Experience Rate 1
121 07-Feb-2011 rosuri 10350917 115.37 Modified the procedure prc_state_balances.
122 Added a new variable l_assignment_id which is used as
123 parameter in function call pay_us_tax_bals_pkg.us_tax_balance.
124 07-Mar-2011 rosuri 10631126 115.38 SS_ER_RATE and SS_EE_RATE are not equal from 01-JAN-2011
125 Commented the messege "YTD SS EE Withheld does not =
126 YTD SS ER Liability"
127 14-Sep-2011 rosuri 12742758 115.39 Modified fnc_get_futa_credit_rate function
128 to return -1 if there is no override FUTA Credit
129 rate in "State Tax Rules" Flexfiled.
130 Modified prc_federal_balances procedure
131 to take the Net FUTA Rate directly
132 from pay_us_federal_tax_info_f table
133 in case override credit limit is not set.
134 otherwise it will take Gross Rate from
135 FED_INFORMATION11 to calculate net futa rate.
136 21-Sep-2011 emunisek 12742758 115.40 Created function get_calculated_fed_limit_tax to calculate
137 the FUTA,SS and MEDICARE calculated values by considering
138 in-between year changes. This function is used as of now
139 for FUTA in procedure prc_federal_balances.
140 22-Nov-2011 emunisek 13394260 115.42 Made changes to consider Default SDI EE Rate for California
141 Employees having "Use Default SDI EE Rate" selected as Yes
142 in State Tax Rules.
143 29-Nov-2011 nvelaga 13360446 115.43 Reverted the change made for bug#10631126 (version 115.38)
144 Added IF clause to check for SS ER and SS EE Rates.
145 05-Dec-2011 ybudamal 13434213 115.44 Modified the function get_calculated_fed_limit_tax to
146 calculate the balance for all the assignments
147 for the person instead of current one assignment.
148 Modified the procedure prc_get_balance to change the
149 p_asg_type parameter passed to the procedure us_tax_balance
150 in the package pay_us_tax_bals_pkg from 'PER' to 'ASG' to
151 calculate the Assignment Level balance.
152 02-SEP-2012 nvelaga 9796821 115.45 Modified the calculation of Medicare EE Tax based on new
153 limit and rate effective 2013.
154 22-NOV-2012 nvelaga 15852506 115.57 Modified the calculation of new Medicate EE Tax Limit.
155 Subtracted 0.01 from the new Medicare Limit insteadof 1.
156 ******************************************************************************/
157
158 --c_fixed_futa_rt CONSTANT NUMBER(10,4) := 6.2;
159 l_gfuta_rt NUMBER;
160 l_futa_change_count NUMBER;
161 -- define some global variables for temporary storage
162 G_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE := NULL;
163 G_payroll_id pay_payroll_actions.payroll_id%TYPE := NULL;
164 G_got_fed_rate BOOLEAN := FALSE;
165 G_ss_ee_rate NUMBER := NULL;
166 G_ss_er_rate NUMBER := NULL;
167 G_medi_ee_rate NUMBER := NULL;
168 G_medi_er_rate NUMBER := NULL;
169 G_commit_count NUMBER := NULL;
170
171 -- Global values to store the flag based on the validity of
172 -- the corresponding balances i.e., IF G_state_flag := 'Y' then
173 -- all state balances are valid in pay_run_balances
174 -- else atleast one of the state balance is invalid
175 G_state_flag VARCHAR2(1);
176 G_county_flag VARCHAR2(1);
177 G_city_flag VARCHAR2(1);
178 G_school_flag VARCHAR2(1);
179
180 -- Bug 3291736
181 -- Variable to hold the dummy assignment insertion
182 -- if there are no employees with Unacceptable balance
183 G_dummy_action_inserted_flag VARCHAR2(1) := 'N';
184
185 /**********************Bug 2963239 Changes start ******************************
186 ********************** variables to hold the SS limit values ******************/
187 G_ss_ee_wage_limit NUMBER := NULL;
188 G_ss_er_wage_limit NUMBER := NULL;
189
190 /********************3005756 START *******************************/
191 -- Definitions of the pl/sql tables for caching.
192
193 TYPE futa_credit_info_rec IS RECORD
194 ( organization_id NUMBER
195 ,sui_state_code VARCHAR2(2)
196 ,futa_credit_rate NUMBER );
197
198 TYPE futa_credit_info_table IS TABLE OF
199 futa_credit_info_rec
200 INDEX BY BINARY_INTEGER;
201
202 futa_credit_info futa_credit_info_table;
203
204 TYPE sui_sdi_tax_info_rec IS RECORD
205 ( sui_ee_limit NUMBER
206 ,sui_er_limit NUMBER
207 ,sdi_ee_limit NUMBER
208 ,sdi_er_limit NUMBER
209 );
210
211 TYPE sui_sdi_tax_info_table IS TABLE OF
212 sui_sdi_tax_info_rec
213 INDEX BY BINARY_INTEGER;
214
215 sui_sdi_tax_info1 sui_sdi_tax_info_table;
216 sui_sdi_tax_info2 sui_sdi_tax_info_table;
217 sui_sdi_tax_info3 sui_sdi_tax_info_table;
218
219 TYPE sui_sdi_override_rec is RECORD
220 ( sui_override_rate NUMBER
221 ,sui_dummy_rate NUMBER
222 ,sdi_override_rate NUMBER );
223
224 TYPE sui_sdi_override_tab IS TABLE OF sui_sdi_override_rec
225 INDEX BY BINARY_INTEGER;
226
227 sui_sdi_override_info sui_sdi_override_tab;
228
229 /********************** fnc_lit ***************************/
230 TYPE county_tax_info_rec IS RECORD
231 ( jurisdiction_code varchar2(11)
232 ,cnty_tax_exists varchar2(1)
233 ,cnty_sd_tax_exists varchar2(1)
234 );
235
236 TYPE county_tax_info_table IS TABLE OF
237 county_tax_info_rec
238 INDEX BY BINARY_INTEGER;
239
240 county_tax_info county_tax_info_table;
241
242 TYPE city_tax_info_rec IS RECORD
243 ( jurisdiction_code varchar2(11)
244 , city_tax_exists varchar2(1)
245 , city_sd_tax_exists varchar2(1)
246 );
247
248 TYPE city_tax_info_table IS TABLE OF
249 city_tax_info_rec
250 INDEX BY BINARY_INTEGER;
251
252 city_tax_info city_tax_info_table;
253
254 -- Global variables to hold vales fetched by c_get_payroll_stuff cursor
255 G_as_of_date DATE := NULL;
256 G_business_id per_all_assignments_f.business_group_id%TYPE;
257 G_leg_param pay_payroll_actions.legislative_parameters%TYPE;
258
259 -- Cursor and global variable to store the futa_override rate
260 G_futa_override_rt NUMBER := 0;
261
262 -- Cursor fetches the futa override rate based on the tax unit id passed.
263 CURSOR c_get_futa_override_rt(
264 IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE) IS
265 SELECT NVL(org_information7,0)/100
266 FROM hr_organization_information
267 WHERE organization_id = IN_tax_unit_id
268 AND org_information_context = 'Federal Tax Rules';
269
270 -- cursor c_get_payroll_stuff made public
271 -- It will now be called once in action_creation
272 CURSOR c_get_payroll_stuff(IN_pact_id IN pay_payroll_actions.payroll_action_id%TYPE) IS
273 SELECT effective_date, business_group_id, legislative_parameters
274 FROM pay_payroll_actions
275 WHERE payroll_action_id = IN_pact_id;
276
277 CURSOR c_get_futa_rate(p_as_of_date DATE) IS
278 SELECT futa_rate
279 FROM pay_us_federal_tax_info_f
280 WHERE p_as_of_date
281 BETWEEN effective_start_date
282 AND effective_end_date
283 AND fed_information_category='401K LIMITS'
284 ORDER BY effective_start_date;
285
286 CURSOR c_futa_count_changes(p_as_of_date DATE,p_dimension VARCHAR2) IS
287 SELECT count(distinct futa_rate)
288 FROM pay_us_federal_tax_info_f
289 WHERE effective_start_date <= p_as_of_date
290 AND effective_end_date >= TRUNC(p_as_of_date,decode(p_dimension,'QTD','Q','YTD','Y'))
291 AND fed_information_category='401K LIMITS';
292
293 /***************** 3005756 END ******************************/
294
295 /***********************************************************************
296 * routine name: range_cursor
297 * purpose:
298 * parameters:
299 * return:
300 * specs:
301 *************************************************************************/
302 PROCEDURE range_cursor (IN_pactid IN NUMBER,
303 OUT_sqlstr OUT NOCOPY VARCHAR2)
304 IS
305
306 lv_sqlstr varchar2(32000);
307 lv_leg_param varchar2(2000);
308 lv_cur_date varchar2(30);
309 lv_b_dim varchar2(10);
310 lv_location_id varchar2(30);
311 lv_organization_id varchar2(30);
312 lv_tax_unit_id varchar2(30);
313 ld_effective_date date;
314 ld_cur_date date;
315
316
317 BEGIN
318 BEGIN
319 select effective_date,legislative_parameters
320 into ld_effective_date,lv_leg_param
321 from pay_payroll_actions
322 where payroll_action_id = IN_pactid;
323
324 END;
325
326 lv_tax_unit_id := payusunb_pkg.fnc_get_parameter('GRE',lv_leg_param);
327 lv_organization_id := payusunb_pkg.fnc_get_parameter('Org',lv_leg_param);
328 lv_location_id := payusunb_pkg.fnc_get_parameter('Loc',lv_leg_param);
329 lv_b_dim := payusunb_pkg.fnc_get_parameter('B_Dim',lv_leg_param);
330
331 if lv_b_dim ='QTD' then
332 ld_cur_date := TRUNC(ld_effective_date,'Q');
333 elsif lv_b_dim ='YTD' then
334 ld_cur_date := TRUNC(ld_effective_date, 'Y');
335 end if;
336
337 select fnd_date.date_to_canonical(ld_cur_date)
338 into lv_cur_date
339 from dual;
340
341 -- range cursor query
342 lv_sqlstr :=
343 'SELECT /*+ ORDERED
344 INDEX (ppa PAY_PAYROLL_ACTIONS_PK)
345 INDEX (pa1 PAY_PAYROLL_ACTIONS_N5)
346 INDEX (act PAY_ASSIGNMENT_ACTIONS_N50)
347 INDEX (paf PER_ASSIGNMENTS_F_PK) */
348 DISTINCT paf.person_id
349 FROM pay_payroll_actions ppa,
350 pay_payroll_actions pa1,
351 pay_assignment_actions act,
352 per_assignments_f paf
353 WHERE ppa.payroll_action_id = :payroll_action_id
354 AND pa1.effective_date >= fnd_date.canonical_to_date('''|| lv_cur_date ||''')
355 AND pa1.effective_date <= ppa.effective_date
356 AND pa1.payroll_action_id = act.payroll_action_id
357 AND paf.assignment_id = act.assignment_id
358 AND pa1.effective_date BETWEEN paf.effective_start_date
359 AND paf.effective_end_date
360 AND pa1.action_type in (''B'',''I'',''R'',''Q'',''V'')
361 AND act.action_status = ''C''
362 AND paf.business_group_id +0 = ppa.business_group_id
363 AND act.tax_unit_id = ' || lv_tax_unit_id;
364
365 if lv_organization_id is not null then
366 lv_sqlstr := lv_sqlstr || ' and paf.organization_id = '||lv_organization_id;
367 end if;
368
369 if lv_location_id is not null then
370 lv_sqlstr := lv_sqlstr || ' and paf.location_id = '||lv_location_id;
371 end if;
372
373 lv_sqlstr := lv_sqlstr || ' ORDER BY paf.person_id';
374
375
376 OUT_sqlstr := lv_sqlstr;
377
378 END range_cursor;
379
380 /**************************************************************************
381 Overloaded prc_write_data created for Bug#9872952
382 This Procedure will be called only for those Employees who have the FIT
383 Exempt from Wage Accumulation enabled and the Profile value
384 'PAY:Use Direct Balances for US Federal Taxes' must be set as Yes.
385 This will insert a record into pay_us_rpt_totals with state_code as E.
386 The PAYUSUNB.rdf fetches these employees and shows them in last of the
387 report as an exemption.
388 ***************************************************************************/
389
390 PROCEDURE prc_write_data (IN_commit_count IN NUMBER,
391 IN_record_type IN VARCHAR2,
392 IN_asgn_action_id IN NUMBER,
393 IN_gre_id IN NUMBER,
394 IN_org_id IN NUMBER,
395 IN_location_id IN NUMBER,
396 IN_pact_id IN NUMBER,
397 IN_chunk_number IN NUMBER,
398 IN_person_id IN NUMBER,
399 IN_assignment_no IN VARCHAR2,
400 IN_balance_nm1 IN VARCHAR2,
401 IN_balance_nm2 IN VARCHAR2,
402 IN_taxable IN NUMBER,
403 IN_withheld IN NUMBER,
404 IN_calculated IN NUMBER,
405 IN_difference IN NUMBER,
406 IN_jurisdiction IN VARCHAR2,
407 IN_message IN VARCHAR2,
408 IN_sort_code IN VARCHAR2,
409 IN_locked_asg_action_id IN NUMBER,
410 IN_assign_id IN NUMBER,
411 IN_attribute IN VARCHAR2 ) IS
412
413 L_jurisdiction VARCHAR2(30);
414
415 BEGIN
416
417 IF IN_jurisdiction IS NULL THEN
418 L_jurisdiction := 'Federal';
419 ELSE
420 L_jurisdiction := IN_jurisdiction;
421 END IF;
422
423 IF G_asgn_action_id IS NULL OR G_asgn_action_id <> IN_asgn_action_id THEN
424
425 -- if assignment_action_id changed then write new header record
426 G_asgn_action_id := IN_asgn_action_id;
427 hr_utility.trace('Inserting Data into pay_us_rpt_totals1');
428 INSERT INTO pay_us_rpt_totals
429 (state_code,
430 tax_unit_id,
431 organization_id,
432 location_id,
433 session_id,
434 business_group_id,
435 value1,
436 gre_name,
437 value6,
438 attribute45
439 )
440 VALUES
441 ('E',
442 IN_gre_id,
443 IN_org_id,
444 IN_location_id,
445 IN_pact_id,
446 IN_chunk_number,
447 IN_person_id,
448 IN_assignment_no,
449 IN_asgn_action_id,
450 IN_attribute
451 );
452
453 /******************************** 2963239 Change ***********************************************************/
454
455 -- insert the action record.
456
457 hr_nonrun_asact.insact(IN_asgn_action_id,IN_assign_id,IN_pact_id,IN_chunk_number,IN_gre_id);
458
459 -- Bug 3291736: Change the flag to Y as the assignment action is created
460 G_dummy_action_inserted_flag := 'Y';
461
462 /******************************** END ******************************************************************/
463
464 END IF;
465
466 G_commit_count := G_commit_count - 1;
467 IF G_commit_count = 0 THEN
468 COMMIT;
469 G_commit_count := IN_commit_count;
470 END IF;
471 END prc_write_data;
472
473
474 /***************************************************************************
475 * routine name: action_creation
476 * purpose:
477 * parameters:
478 * return:
479 * specs:
480 ****************************************************************************/
481 PROCEDURE action_creation(IN_pactid IN NUMBER,
482 IN_stperson IN NUMBER,
483 IN_endperson IN NUMBER,
484 IN_chunk IN NUMBER) IS
485
486 CURSOR c_actions(cp_start_person_id in number
487 ,cp_end_person_id in number
488 ,cp_tax_unit_id in number
489 ,cp_organization_id in number
490 ,cp_location_id in number
491 ,cp_business_group_id in number
492 ,cp_period_start in date
493 ,cp_period_end in date) is
494 SELECT DISTINCT
495 paf.person_id person_id
496 FROM per_all_assignments_f paf,
497 pay_all_payrolls_f PPY
498 WHERE exists
499 (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
500 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
501 'x'
502 from pay_payroll_actions ppa,
503 pay_assignment_actions paa
504 where ppa.effective_date between cp_period_start
505 and cp_period_end
506 and ppa.action_type in ('R','Q','V','B','I')
507 and ppa.action_status = 'C'
508 and ppa.business_group_id + 0 = cp_business_group_id
509 and ppa.payroll_action_id = paa.payroll_action_id
510 and paa.tax_unit_id = cp_tax_unit_id
511 and paa.action_status = 'C'
512 and paa.assignment_id = paf.assignment_id
513 and ppa.business_group_id = paf.business_group_id +0
514 and ppa.effective_date between paf.effective_start_date
515 and paf.effective_end_date)
516 AND paf.person_id between cp_start_person_id and cp_end_person_id
517 AND paf.assignment_type = 'E'
518 AND (cp_organization_id is null OR
519 paf.organization_id = cp_organization_id)
520 AND (cp_location_id is null OR
521 paf.LOCATION_ID = cp_location_id)
522 AND PPY.payroll_id = paf.payroll_id;
523
524 CURSOR c_actions_person_on(
525 cp_payroll_Action_id in number
526 ,cp_chunk_number in number) is
527 SELECT ppr.person_id person_id
528 FROM pay_population_ranges ppr
529 where ppr.payroll_action_id = cp_payroll_Action_id
530 and ppr.chunk_number = cp_chunk_number;
531
532
533 -- Cursor to get the latest assignment action id details for the person
534 -- selected
535 CURSOR c_get_latest_asg(
536 cp_person_id IN NUMBER
537 ,cp_tax_unit_id IN NUMBER
538 ,cp_as_of_date IN DATE
539 ,cp_start_date IN DATE
540 ,IN_org_id IN NUMBER
541 ,IN_location_id IN NUMBER
542 ) IS
543 /* Change for Performance Bug 6360505 */
544 SELECT /*+ ORDERED */
545 to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
546 FROM per_all_assignments_f paf,
547 pay_payroll_actions ppa,
548 pay_assignment_actions paa,
549 pay_action_classifications pac
550 WHERE paf.person_id = cp_person_id
551 AND paf.payroll_id = ppa.payroll_id
552 AND (paf.organization_id = IN_org_id
553 OR IN_org_id IS NULL)
554 AND (paf.location_id = IN_location_id
555 OR IN_location_id IS NULL)
556 AND paa.assignment_id = paf.assignment_id
557 AND paa.tax_unit_id = cp_tax_unit_id
558 AND paa.payroll_action_id = ppa.payroll_action_id
559 AND ((NVL(paa.run_type_id, ppa.run_type_id) IS NULL
560 AND paa.source_action_id IS NULL)
561 OR (NVL(paa.run_type_id, ppa.run_type_id) IS NOT NULL
562 AND paa.source_action_id IS NOT NULL )
563 OR (ppa.action_type = 'V' AND ppa.run_type_id IS NULL
564 AND paa.run_type_id IS NOT NULL
565 AND paa.source_action_id IS NULL))
566 AND ppa.effective_date BETWEEN paf.effective_start_date
567 AND paf.effective_end_date
568 AND ppa.effective_date BETWEEN cp_start_date AND cp_as_of_date
569 AND ppa.action_type = pac.action_type
570 AND pac.classification_name = 'SEQUENCED';
571
572 /* Change for Performance Bug 6360505 */
573 CURSOR c_get_asg_details(
574 cp_asg_act_id IN NUMBER
575 ,cp_tax_unit_id IN NUMBER
576 ,cp_person_id IN NUMBER
577 ,cp_as_of_date IN DATE
578 ,cp_start_date IN DATE
579 ) IS
580 SELECT paa.assignment_id ,
581 paf.location_id,
582 paf.organization_id,
583 paf.assignment_number
584 FROM pay_assignment_actions paa,
585 pay_payroll_actions ppa,
586 per_all_assignments_f paf
587 WHERE paa.assignment_action_id = cp_asg_act_id
588 AND paa.tax_unit_id = cp_tax_unit_id
589 AND ppa.payroll_action_id = paa.payroll_action_id
590 AND ppa.effective_date BETWEEN cp_start_date AND cp_as_of_date
591 AND paf.assignment_id = paa.assignment_id
592 AND ppa.effective_date BETWEEN paf.effective_start_date
593 AND paf.effective_end_date
594 AND paf.person_id = cp_person_id;
595 /* Change for Performance Bug 6360505 */
596
597 /*Added for Bug#9872952*/
598 CURSOR c_get_wage_acc_flag IS
599 SELECT parameter_value
600 FROM pay_action_parameters
601 WHERE parameter_name = 'WAGE_ACCUMULATION_ENABLED';
602 /*End Bug#9872952*/
603
604 L_lockingactid NUMBER;
605 L_lockedactid NUMBER;
606 L_assignid NUMBER;
607 L_greid NUMBER;
608 L_as_of_date DATE := NULL;
609 L_start_date DATE;
610 L_leg_param pay_payroll_actions.legislative_parameters%TYPE;
611 L_gre_id pay_assignment_actions.tax_unit_id%TYPE;
612 L_org_id per_all_assignments_f.organization_id%TYPE;
613 L_location_id per_all_assignments_f.location_id%TYPE;
614 L_business_id per_all_assignments_f.business_group_id%TYPE;
615 L_dimension VARCHAR2(20) := NULL;
616
617 L_person_id per_all_assignments_f.person_id%TYPE;
618 L_loc_id per_all_assignments_f.location_id%TYPE;
619 L_organization_id per_all_assignments_f.organization_id%TYPE;
620 L_assignment_number per_all_assignments_f.assignment_number%TYPE;
621
622 l_range_person BOOLEAN;
623 /*Added for Bug#9872952*/
624 L_asg_count NUMBER := 0;
625 L_direct_fed_bal VARCHAR2(2) := 'N';
626 L_wage_accumulation VARCHAR2(1) := 'N';
627 /*End Bug#9872952*/
628 BEGIN
629
630 -- get all required parameters from legislative parameter string
631 OPEN c_get_payroll_stuff(IN_pactid);
632 FETCH c_get_payroll_stuff INTO G_as_of_date, G_business_id, G_leg_param;
633 CLOSE c_get_payroll_stuff;
634
635 -- Local variables for payroll related stuff
636 L_as_of_date := G_as_of_date;
637 L_business_id := G_business_id;
638 L_leg_param := G_leg_param;
639
640 L_dimension := fnc_get_parameter('B_Dim',L_leg_param);
641 L_gre_id := fnc_get_parameter('GRE',L_leg_param);
642 L_org_id := fnc_get_parameter('Org',L_leg_param);
643 L_location_id := fnc_get_parameter('Loc',L_leg_param);
644
645 /***************************3005756 START *******************************/
646 -- Get the futa override rate
647 OPEN c_get_futa_override_rt(L_gre_id);
648 FETCH c_get_futa_override_rt INTO G_futa_override_rt;
649 CLOSE c_get_futa_override_rt;
650 /******************************* 3005756 END *******************************/
651
652 -- calculate the start date based on YTD or QTD dimensions
653 IF L_dimension = 'QTD' THEN
654 L_start_date := TRUNC(L_as_of_date,'Q');
655 ELSIF L_dimension = 'YTD' THEN
656 L_start_date := TRUNC(L_as_of_date,'YYYY');
657 END IF;
658
659 /************************* 3005756 start ********************************/
660 -- Fetch the balance validity flags into the global variables for use in
661 -- prc_process_data
662 G_state_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_business_id,'UNB_STATE');
663 G_county_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_business_id,'UNB_COUNTY');
664 G_city_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_business_id,'UNB_CITY');
665 G_school_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_business_id,'UNB_SCHOOL');
666 /************************* 3005756 end *********************************/
667
668 l_range_person := pay_ac_utility.range_person_on(
669 p_report_type => 'PAYUSUNB'
670 ,p_report_format => 'DEFAULT'
671 ,p_report_qualifier => 'DEFAULT'
672 ,p_report_category => 'REPORT');
673 hr_utility.set_location('procpyr',1);
674 if l_range_person then
675 OPEN c_actions_person_on(IN_pactid, IN_chunk);
676 else
677 OPEN c_actions(IN_stperson, IN_endperson, L_gre_id,
678 L_org_id, L_location_id, L_business_id,
679 L_start_date, L_as_of_date);
680 end if;
681
682 LOOP
683 hr_utility.set_location('procpyr',2);
684 if l_range_person then
685 FETCH c_actions_person_on INTO L_person_id;
686 EXIT WHEN c_actions_person_on%NOTFOUND;
687 else
688 FETCH c_actions INTO L_person_id;
689 EXIT WHEN c_actions%NOTFOUND;
690 end if;
691
692 -- Bug 3291736
693 -- insert_action(IN_pactid,IN_chunk,L_gre_id,L_person_id,
694 -- L_location_id,L_org_id,L_start_date,L_as_of_date);
695 -- Code to replace call to insert_actions.
696
697 -- we need to insert one action for each of the
698 -- rows that we return FROM the cursor (i.e. one
699 -- for each assignment/pre-payment/reversal).
700 hr_utility.trace('L_person_id = '||to_char(L_person_id));
701 hr_utility.trace('L_org_id = '||to_char(L_org_id));
702 hr_utility.trace('L_location_id = '||to_char(L_location_id));
703 hr_utility.trace('L_as_of_date = '||L_as_of_date);
704 hr_utility.trace('L_start_date = '||L_start_date);
705 hr_utility.trace('L_gre_id = '||to_char(L_gre_id));
706
707 OPEN c_get_latest_asg(L_person_id,L_gre_id,L_as_of_date,
708 L_start_date,L_org_id,L_location_id);
709 FETCH c_get_latest_asg INTO L_lockedactid; /* Change for Performance Bug 6360505 */
710 CLOSE c_get_latest_asg;
711
712 hr_utility.trace('L_lockedactid ' || L_lockedactid);
713 /* Change for Performance Bug 6360505 */
714 OPEN c_get_asg_details(L_lockedactid, L_gre_id, L_person_id,
715 L_as_of_date, L_start_date);
716 FETCH c_get_asg_details INTO L_assignid,L_loc_id,
717 L_organization_id,L_assignment_number;
718 CLOSE c_get_asg_details;
719 /* Change for Performance Bug 6360505 */
720
721 hr_utility.trace('L_assignid '||to_char(L_assignid));
722 hr_utility.trace('L_assignid ' || L_assignid);
723 hr_utility.trace('L_assignment_number ' || L_assignment_number);
724 hr_utility.set_location('procpyr',3);
725
726 SELECT pay_assignment_actions_s.NEXTVAL
727 INTO L_lockingactid
728 FROM dual;
729 /*Added for Bug#9872952*/
730 /*When the Profile value of 'PAY:Use Direct Balances for US Federal Taxes' is set as Yes,
731 the employees who have the FIT Exempt from Wage Accumulation selected will be shown
732 in exception section in the end of report.*/
733
734 L_direct_fed_bal := fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES');
735
736 IF L_direct_fed_bal = 'Y' THEN
737 OPEN c_get_wage_acc_flag;
738 FETCH c_get_wage_acc_flag INTO L_wage_accumulation;
739 CLOSE c_get_wage_acc_flag;
740
741 IF L_wage_accumulation = 'Y' THEN
742 /*Query to see if the Person has any assignments with FIT Exempt from Wage Accumulation selected */
743 SELECT count(0) INTO L_asg_count
744 FROM per_all_assignments_f paf,
745 pay_us_emp_fed_tax_rules_f peft
746 WHERE paf.person_id = L_person_id
747 AND paf.effective_end_date >= L_start_date
748 AND paf.effective_start_date <= L_as_of_date
749 AND peft.assignment_id = paf.assignment_id
750 AND peft.business_group_id = paf.business_group_id
751 AND peft.wage_exempt = 'Y'
752 AND peft.effective_end_date >= L_start_date
753 AND peft.effective_start_date <= L_as_of_date;
754
755 END IF; --L_wage_accumulation
756
757 END IF; --L_direct_fed_bal
758
759 IF L_asg_count > 0 THEN
760
761 IF L_lockedactid is not null then
762
763 hr_utility.trace('Skipping the verification of Balances as Person has an Assignment opting'||
764 'for FIT Exempt from Wage Accumulation-Manual Verification needed');
765
766 prc_write_data(500,'U',L_lockingactid,L_gre_id,L_organization_id,
767 L_loc_id,IN_pactid,IN_chunk,L_person_id,L_assignment_number,
768 NULL,NULL,NULL,NULL,NULL,NULL,NULL,
769 '*** Manual Verification of Balances is required for Assignments of this Person ***',
770 '00-000-0000',L_lockedactid,L_assignid,'T');
771 END IF;
772
773
774 ELSE
775
776 IF L_lockedactid is not null then
777 prc_process_data(IN_pactid,IN_chunk,500,L_lockingactid,
778 L_lockedactid,L_assignid,L_gre_id
779 ,L_person_id,L_loc_id,L_organization_id
780 ,L_assignment_number );
781 END IF;
782
783 END IF;
784
785 END LOOP;
786 if l_range_person then
787 CLOSE c_actions_person_on;
788 else
789 CLOSE c_actions;
790 end if;
791
792 -- Bug 3291736
793 -- Code to insert dummuy action if there are no actions inserted.
794 -- But if there is no payroll run or prepayments then no dummy action
795 -- will be inserted.
796 IF L_lockedactid is not null and
797 G_dummy_action_inserted_flag = 'N' THEN
798 hr_nonrun_asact.insact(L_lockingactid,L_assignid,
799 IN_pactid,IN_chunk,L_gre_id);
800 END IF;
801
802 END action_creation;
803
804
805 /*************************************************************************
806 * routine name: sort_action
807 * purpose:
808 * parameters:
809 * return:
810 * specs:
811 **************************************************************************/
812 PROCEDURE sort_action(IN_payactid IN VARCHAR2
813 ,IO_sqlstr IN OUT NOCOPY VARCHAR2
814 ,OUT_len OUT NOCOPY NUMBER)
815 IS
816 BEGIN
817 IO_sqlstr := 'SELECT paa1.rowid
818 /* we need the row id of the assignment actions that are
819 created by PYUGEN */
820 FROM hr_organization_units hou,
821 hr_organization_units hou1,
822 hr_locations loc,
823 per_people_f ppf,
824 per_all_assignments_f paf,
825 pay_assignment_actions paa1, /* PYUGEN assignment action */
826 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
827 WHERE ppa1.payroll_action_id = :pactid
828 AND paa1.payroll_action_id = ppa1.payroll_action_id
829 AND paa1.assignment_id = paf.assignment_id
830 AND paf.effective_start_date =
831 (SELECT MAX(paf1.effective_start_date)
832 FROM per_assignments_f paf1
833 WHERE paf1.assignment_id = paf.assignment_id
834 AND paf1.effective_start_date <= ppa1.effective_date
835 AND paf1.effective_end_date >=
836 DECODE(payusunb_pkg.fnc_get_parameter(''B_Dim'',
837 ppa1.legislative_parameters),
838 ''QTD'',
839 TRUNC(ppa1.effective_date,''Q''),
840 ''YTD'',
841 TRUNC(ppa1.effective_date,''Y''))
842 )
843 AND hou1.organization_id = paa1.tax_unit_id
844 AND hou.organization_id = paf.organization_id
845 AND loc.location_id = paf.location_id
846 AND ppf.person_id = paf.person_id
847 AND ppa1.effective_date BETWEEN ppf.effective_start_date
848 AND ppf.effective_END_date
849 ORDER BY
850 hou1.name, /* GRE */
851 DECODE(payusunb_pkg.fnc_get_parameter(
852 ''SO1'',ppa1.legislative_parameters),
853 ''Employee'',ppf.full_name,
854 ''Social'',ppf.national_identifier,
855 ''Organization'',hou.name,
856 ''Location'',loc.location_code,null),
857 DECODE(payusunb_pkg.fnc_get_parameter(
858 ''SO2'',ppa1.legislative_parameters),
859 ''Employee'',ppf.full_name,
860 ''Social'',ppf.national_identifier,
861 ''Organization'',hou.name,
862 ''Location'',loc.location_code,null),
863 DECODE(payusunb_pkg.fnc_get_parameter(
864 ''SO3'',ppa1.legislative_parameters),
865 ''Employee'',ppf.full_name,
866 ''Social'',ppf.national_identifier,
867 ''Organization'',hou.name,
868 ''Location'',loc.location_code,null),
869 hou.name,
870 ppf.full_name
871 FOR UPDATE of paa1.assignment_action_id';
872
873 OUT_len := LENGTH(IO_sqlstr); -- return the length of the string.
874
875 END sort_action;
876
877
878 /*************************************************************************
879 routine name: fnc_get_parameter
880 purpose: Gets specified parameter value from legislative Parameter
881 String
882 parameters: IN_name - name of the parameter to get value
883 IN_parameter_list - String containing legislative parameter
884 return: Value for specified parameter name
885 specs:
886 **************************************************************************/
887 FUNCTION fnc_get_parameter(IN_name IN VARCHAR2,
888 IN_parameter_list IN VARCHAR2) RETURN VARCHAR2
889 IS
890 L_start_ptr NUMBER;
891 L_end_ptr NUMBER;
892 L_token_val pay_payroll_actions.legislative_parameters%TYPE;
893 L_par_value pay_payroll_actions.legislative_parameters%TYPE;
894 BEGIN
895
896 L_token_val := IN_name||'=';
897 L_start_ptr := INSTR(IN_parameter_list, L_token_val)
898 + length(L_token_val);
899 L_end_ptr := INSTR(IN_parameter_list, ' ',L_start_ptr);
900
901 /* if there is no spaces use then length of the string */
902 IF L_end_ptr = 0 THEN
903 L_end_ptr := LENGTH(IN_parameter_list)+1;
904 END IF;
905
906 /* Did we find the token */
907 IF INSTR(IN_parameter_list, L_token_val) = 0 THEN
908 L_par_value := NULL;
909 ELSE
910 L_par_value := SUBSTR(IN_parameter_list,
911 L_start_ptr, L_end_ptr - L_start_ptr);
912 END IF;
913
914 RETURN L_par_value;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 --hr_utility.trace('Error: PAYUSUNB_PKG.fnc_get_parameter failed - ORA'||TO_CHAR(SQLCODE));
919 RAISE;
920 END fnc_get_parameter;
921
922 /*************************************************************************
923 routine name: prc_get_balance
924 purpose: Pulls all applicable balances for specified dimension,
925 tax type and juridiction
926 parameters:
927 return:
928 specs:
929 **************************************************************************/
930 PROCEDURE prc_get_balance(IN_asg_action_id IN NUMBER,
931 IN_tax_unit_id IN NUMBER,
932 IN_as_of_date IN DATE,
933 IN_dimension IN VARCHAR2,
934 IN_tax_type IN VARCHAR2,
935 IN_balance_type IN VARCHAR2,
936 IN_ee_or_er IN VARCHAR2,
937 IN_jurisdiction IN VARCHAR2,
938 OUT_bal OUT NOCOPY NUMBER) IS
939
940 L_rval NUMBER := 0;
941
942 BEGIN
943
944 L_rval := pay_us_tax_bals_pkg.us_tax_balance
945 (IN_balance_type,
946 IN_tax_type,
947 IN_ee_or_er,
948 IN_dimension,
949 'PER',
950 IN_tax_unit_id,
951 IN_jurisdiction,
952 IN_asg_action_id,
953 NULL,
954 NULL,
955 NULL,
956 TRUE);
957
958 IF L_rval IS NULL THEN
959 L_rval := 0;
960 END IF;
961
962 OUT_bal := L_rval;
963 EXCEPTION
964 WHEN NO_DATA_FOUND THEN
965 OUT_bal := 0;
966 WHEN OTHERS THEN
967 RAISE;
968 END prc_get_balance;
969
970 /*************************************************************************
971 routine name: prc_get_balance
972 purpose: Pulls all applicable balances for specified dimension,
973 tax type and juridiction. This is an overloaded function to
974 find the balances in virutal date mode.
975 parameters:
976 return:
977 specs:
978 **************************************************************************/
979 PROCEDURE prc_get_balance(IN_assignment_id IN NUMBER,
980 IN_tax_unit_id IN NUMBER,
981 IN_as_of_date IN DATE,
982 IN_dimension IN VARCHAR2,
983 IN_tax_type IN VARCHAR2,
984 IN_balance_type IN VARCHAR2,
985 IN_ee_or_er IN VARCHAR2,
986 IN_jurisdiction IN VARCHAR2,
987 IN_mode IN VARCHAR2,
988 OUT_bal OUT NOCOPY NUMBER) IS
989
990 L_rval NUMBER := 0;
991
992 BEGIN
993
994 hr_utility.trace('Inside prc_get_balance DATE mode');
995 hr_utility.trace('IN_as_of_date'||IN_as_of_date);
996
997 IF IN_mode = 'DATE' THEN
998
999 L_rval := pay_us_tax_bals_pkg.us_tax_balance
1000 (IN_balance_type,
1001 IN_tax_type,
1002 IN_ee_or_er,
1003 IN_dimension,
1004 'ASG', --#Bug 13434213 Changed from 'PER' to 'ASG'
1005 IN_tax_unit_id,
1006 IN_jurisdiction,
1007 NULL,
1008 IN_assignment_id,
1009 IN_as_of_date,
1010 NULL,
1011 TRUE);
1012 ELSE
1013 L_rval := 0;
1014 END IF;
1015
1016 IF L_rval IS NULL THEN
1017 L_rval := 0;
1018 END IF;
1019
1020 OUT_bal := L_rval;
1021
1022 hr_utility.trace('OUT_bal'||OUT_bal);
1023
1024 EXCEPTION
1025 WHEN NO_DATA_FOUND THEN
1026 OUT_bal := 0;
1027 WHEN OTHERS THEN
1028 RAISE;
1029 END prc_get_balance;
1030
1031 /*************************************************************************
1032 routine name: get_calculated_fed_limit_tax
1033 purpose: Calculates the calculated value by considering mid year
1034 rate changes.
1035 parameters:
1036 return:
1037 specs:
1038 **************************************************************************/
1039
1040 FUNCTION get_calculated_fed_limit_tax(p_balance_type VARCHAR2,
1041 p_assignment_action_id NUMBER,
1042 p_tax_unit_id NUMBER,
1043 p_ee_or_er VARCHAR2,
1044 p_as_of_date DATE,
1045 p_dimension VARCHAR2
1046 )
1047 RETURN NUMBER
1048 IS
1049
1050 CURSOR c_get_federal_rates IS
1051 SELECT effective_start_date,
1052 effective_end_date,
1053 futa_rate,
1054 ss_ee_rate,
1055 ss_er_rate,
1056 medi_ee_rate,
1057 medi_er_rate
1058 FROM pay_us_federal_tax_info_f
1059 WHERE effective_start_date <= p_as_of_date
1060 AND effective_end_date >= TRUNC(p_as_of_date,decode(p_dimension,'QTD','Q','YTD','Y'))
1061 AND fed_information_category='401K LIMITS'
1062 ORDER BY effective_start_date;
1063
1064 CURSOR c_get_assignment_id(p_assignment_action_id NUMBER) is
1065 SELECT assignment_id
1066 FROM pay_assignment_actions
1067 WHERE assignment_action_id = p_assignment_action_id;
1068
1069 CURSOR c_get_all_assignment_id(p_assignment_id NUMBER,p1_as_of_date DATE,p1_dimension VARCHAR2) is --#Bug 13434213 Added cursor c_get_all_assignment_id
1070 SELECT distinct assignment_id
1071 FROM per_all_assignments_f
1072 WHERE person_id = (SELECT person_id
1073 FROM per_all_assignments_f
1074 WHERE assignment_id = p_assignment_id
1075 AND ROWNUM < 2)
1076 AND TRUNC(p1_as_of_date,decode(p1_dimension,'QTD','Q','YTD','Y'))<=effective_end_date;
1077
1078 l_rate NUMBER;
1079 l_futa_rate NUMBER;
1080 l_ss_ee_rate NUMBER;
1081 l_ss_er_rate NUMBER;
1082 l_medi_ee_rate NUMBER;
1083 l_medi_er_rate NUMBER;
1084 l_effective_start_date DATE;
1085 l_effective_end_date DATE;
1086 l_previous_balance NUMBER;
1087 l_assignment_id NUMBER;
1088 l_calculated NUMBER;
1089 l_balance NUMBER;
1090 l_asg_balance NUMBER; --#Bug 13434213 Added l_asg_balance
1091
1092 BEGIN
1093
1094 hr_utility.trace('Inside function get_calculated_fed_limit_tax');
1095 hr_utility.trace('Parameters passed are ');
1096 hr_utility.trace('p_balance_type : '||p_balance_type);
1097 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
1098 hr_utility.trace('p_tax_unit_id : '||p_tax_unit_id);
1099 hr_utility.trace('p_as_of_date : '||p_as_of_date);
1100 hr_utility.trace('p_dimension : '||p_dimension);
1101
1102 OPEN c_get_assignment_id(p_assignment_action_id);
1103 FETCH c_get_assignment_id INTO l_assignment_id;
1104 CLOSE c_get_assignment_id;
1105
1106 hr_utility.trace('l_assignment_id : '||l_assignment_id);
1107
1108 OPEN c_get_federal_rates;
1109 FETCH c_get_federal_rates INTO l_effective_start_date,l_effective_end_date,
1110 l_futa_rate,l_ss_ee_rate,l_ss_er_rate,
1111 l_medi_ee_rate,l_medi_er_rate;
1112
1113 l_calculated := 0;
1114 l_previous_balance := 0;
1115 l_asg_balance :=0; --#Bug 13434213
1116 l_balance :=0; --#Bug 13434213
1117
1118
1119 WHILE (c_get_federal_rates%FOUND)
1120
1121 LOOP
1122
1123 hr_utility.trace('Effective Start Date : '||l_effective_start_date);
1124
1125 OPEN c_get_all_assignment_id(l_assignment_id,p_as_of_date,p_dimension); --#Bug 13434213
1126 FETCH c_get_all_assignment_id INTO l_assignment_id;
1127 hr_utility.trace('l_assignment_id :'||l_assignment_id);
1128
1129 WHILE (c_get_all_assignment_id%FOUND) --#Bug 13434213
1130 LOOP
1131 IF l_effective_end_date <= p_as_of_date THEN
1132
1133 prc_get_balance(l_assignment_id, p_tax_unit_id,
1134 l_effective_end_date, p_dimension,
1135 p_balance_type, 'TAXABLE', p_ee_or_er,
1136 NULL,'DATE', l_asg_balance); --#Bug 13434213 changed from l_balance to l_asg_balance
1137
1138 hr_utility.trace('l_asg_balance : '||l_asg_balance);
1139
1140 l_balance := l_balance + l_asg_balance; --#Bug 13434213
1141
1142 hr_utility.trace('l_balance : '||l_balance);
1143
1144 ELSE
1145
1146 prc_get_balance(l_assignment_id, p_tax_unit_id,
1147 p_as_of_date, p_dimension,
1148 p_balance_type, 'TAXABLE', p_ee_or_er,
1149 NULL,'DATE', l_asg_balance); --#Bug 13434213 changed from l_balance to l_asg_balance
1150
1151 hr_utility.trace('l_asg_balance : '||l_asg_balance);
1152
1153 l_balance := l_balance + l_asg_balance; --#Bug 13434213
1154
1155 hr_utility.trace('l_balance : '||l_balance);
1156 END IF;
1157
1158 FETCH c_get_all_assignment_id INTO l_assignment_id; --#Bug 13434213
1159 hr_utility.trace('l_assignment_id :'||l_assignment_id);
1160
1161 END LOOP;
1162 CLOSE c_get_all_assignment_id; --#Bug 13434213
1163
1164 IF p_balance_type = 'FUTA' THEN
1165
1166 l_rate := l_futa_rate;
1167
1168 ELSIF p_balance_type = 'SS' AND p_ee_or_er = 'EE' THEN
1169
1170 l_rate := l_ss_ee_rate;
1171
1172 ELSIF p_balance_type = 'SS' AND p_ee_or_er = 'ER' THEN
1173
1174 l_rate := l_ss_er_rate;
1175
1176 ELSIF p_balance_type = 'MEDICARE' AND p_ee_or_er = 'EE' THEN
1177
1178 l_rate := l_medi_ee_rate;
1179
1180 ELSIF p_balance_type = 'MEDICARE' AND p_ee_or_er = 'ER' THEN
1181
1182 l_rate := l_medi_er_rate;
1183
1184 END IF;
1185
1186 hr_utility.trace('l_rate : '||l_rate);
1187 hr_utility.trace('l_balance : '||l_balance);
1188 hr_utility.trace('l_previous_balance : '||l_previous_balance);
1189
1190 IF p_dimension = 'QTD' THEN
1191 l_calculated := l_calculated + (l_balance - l_previous_balance) * l_rate;
1192 ELSE
1193 l_calculated := l_calculated + ROUND((l_balance - l_previous_balance) * l_rate,2);
1194 END IF;
1195
1196 l_previous_balance := l_balance;
1197
1198 hr_utility.trace('l_calculated'||l_calculated);
1199
1200 FETCH c_get_federal_rates INTO l_effective_start_date,l_effective_end_date,
1201 l_futa_rate,l_ss_ee_rate,l_ss_er_rate,
1202 l_medi_ee_rate,l_medi_er_rate;
1203
1204 l_balance :=0; --#Bug 13434213
1205 END LOOP;
1206
1207 CLOSE c_get_federal_rates;
1208
1209 hr_utility.trace('l_calculated : '||l_calculated );
1210 RETURN l_calculated;
1211
1212 EXCEPTION
1213
1214 WHEN OTHERS THEN
1215
1216 hr_utility.trace('Entered into Exception in get_calculated_fed_limit_tax function');
1217
1218 END get_calculated_fed_limit_tax;
1219
1220 /*************************************************************************
1221 routine name: fnc_get_tax_limit_rate
1222 purpose: get tax limit rates in table PAY_US_STATE_TAX_INFO_F for
1223 specified state and category
1224 parameters:
1225 return:
1226 specs:
1227 **************************************************************************/
1228 FUNCTION fnc_get_tax_limit_rate(IN_state_code IN pay_us_states.state_code%TYPE,
1229 IN_start_date IN DATE,
1230 IN_as_of_date IN DATE,
1231 IN_tax_type IN VARCHAR2,
1232 IN_ee_or_er IN VARCHAR2,
1233 IN_tab_flag IN VARCHAR2,
1234 IN_tax_unit_id IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
1235 L_return_val NUMBER;
1236
1237 /**********************3005756 START ****************************************/
1238
1239 -- Modified the function to cache the values and later use it instead of
1240 -- hitting the database for each balance call
1241
1242 -- Modified cursor c_sui_sdi_info to get the state tax record effective on As of Date
1243 -- as against looking for the tax record for the entire period.
1244
1245 CURSOR c_sui_sdi_info IS
1246 SELECT state_code,sta_information5,sta_information6,sta_information3,sta_information4
1247 FROM pay_us_state_tax_info_f pusif
1248 WHERE IN_as_of_date between pusif.effective_start_date AND pusif.effective_end_date
1249 --IN_as_of_date <= pusif.effective_end_date --Modified for Bug#8665548
1250 --AND IN_start_date >= pusif.effective_start_date --Removed for Bug#8665548
1251 AND pusif.sta_information_category = 'State tax limit rate info'
1252 ORDER BY 1;
1253
1254 l_sui_ee VARCHAR2(20);
1255 l_sui_er VARCHAR2(20);
1256 l_sdi_ee VARCHAR2(20);
1257 l_sdi_er VARCHAR2(20);
1258 lv_state_code VARCHAR2(2);
1259
1260 /********************** 3005756 END ************************************/
1261
1262 BEGIN
1263
1264 /************************ 3005756 START **************************************/
1265 -- Check if the date passes is as_of_date and populate the pl/sql accordingly
1266 -- If date = as_of_date populate the sui_sdi_tax_info1 table
1267 -- else populate sui_sdi_tax_info2 table
1268
1269 IF IN_tab_flag = 'FULL' THEN
1270 IF payusunb_pkg.sui_sdi_tax_info1.count < 1 THEN
1271 OPEN c_sui_sdi_info ;
1272 LOOP
1273 FETCH c_sui_sdi_info into lv_state_code,l_sui_ee,l_sui_er,l_sdi_ee,l_sdi_er;
1274 EXIT WHEN c_sui_sdi_info%NOTFOUND;
1275 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sui_ee_limit := l_sui_ee;
1276 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sui_er_limit := l_sui_er;
1277 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sdi_ee_limit := l_sdi_ee;
1278 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sdi_er_limit := l_sdi_er;
1279 END LOOP;
1280 CLOSE c_sui_sdi_info ;
1281 END IF;
1282
1283 IF payusunb_pkg.sui_sdi_tax_info1.exists(IN_state_code) THEN
1284
1285 IF IN_tax_type = 'SUI' AND IN_ee_or_er = 'EE' THEN
1286 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sui_ee_limit;
1287 ELSIF IN_tax_type = 'SUI' AND IN_ee_or_er = 'ER' THEN
1288 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sui_er_limit;
1289 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'EE' THEN
1290 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sdi_ee_limit;
1291 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'ER' THEN
1292 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sdi_er_limit;
1293 END IF;
1294
1295 END IF;
1296
1297 ELSIF IN_tab_flag = 'FIRST' THEN
1298
1299 IF payusunb_pkg.sui_sdi_tax_info2.count < 1 THEN
1300 OPEN c_sui_sdi_info ;
1301 LOOP
1302 FETCH c_sui_sdi_info into lv_state_code,l_sui_ee,l_sui_er,l_sdi_ee,l_sdi_er;
1303 EXIT WHEN c_sui_sdi_info%NOTFOUND;
1304 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sui_ee_limit := l_sui_ee;
1305 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sui_er_limit := l_sui_er;
1306 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sdi_ee_limit := l_sdi_ee;
1307 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sdi_er_limit := l_sdi_er;
1308 END LOOP;
1309 CLOSE c_sui_sdi_info ;
1310 END IF;
1311
1312 IF payusunb_pkg.sui_sdi_tax_info2.exists(IN_state_code) THEN
1313
1314 IF IN_tax_type = 'SUI' AND IN_ee_or_er = 'EE' THEN
1315 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sui_ee_limit;
1316 ELSIF IN_tax_type = 'SUI' AND IN_ee_or_er = 'ER' THEN
1317 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sui_er_limit;
1318 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'EE' THEN
1319 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sdi_ee_limit;
1320 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'ER' THEN
1321 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sdi_er_limit;
1322 END IF;
1323
1324 END IF;
1325
1326 ELSIF IN_tab_flag = 'LAST' THEN
1327
1328 IF payusunb_pkg.sui_sdi_tax_info3.count < 1 THEN
1329 OPEN c_sui_sdi_info ;
1330 LOOP
1331 FETCH c_sui_sdi_info into lv_state_code,l_sui_ee,l_sui_er,l_sdi_ee,l_sdi_er;
1332 EXIT WHEN c_sui_sdi_info%NOTFOUND;
1333 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sui_ee_limit := l_sui_ee;
1334 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sui_er_limit := l_sui_er;
1335 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sdi_ee_limit := l_sdi_ee;
1336 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sdi_er_limit := l_sdi_er;
1337 END LOOP;
1338 CLOSE c_sui_sdi_info ;
1339 END IF;
1340
1341 IF payusunb_pkg.sui_sdi_tax_info3.exists(IN_state_code) THEN
1342
1343 IF IN_tax_type = 'SUI' AND IN_ee_or_er = 'EE' THEN
1344 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sui_ee_limit;
1345 ELSIF IN_tax_type = 'SUI' AND IN_ee_or_er = 'ER' THEN
1346 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sui_er_limit;
1347 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'EE' THEN
1348 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sdi_ee_limit;
1349 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'ER' THEN
1350 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sdi_er_limit;
1351 END IF;
1352
1353 END IF;
1354
1355
1356 END IF;
1357
1358 /**************************3005756 END ***********************************************/
1359
1360 IF L_return_val IS NULL THEN
1361 L_return_val := 0.0;
1362 END IF;
1363
1364 RETURN L_return_val;
1365
1366 END fnc_get_tax_limit_rate;
1367
1368 /****************************** 3005756 START ******************************/
1369
1370 -- New function to cache the futa credit rates in pl/sql tables .
1371 -- Used in place of the cursor c_get_futa_credit_rt
1372
1373 FUNCTION fnc_get_futa_credit_rate(IN_organization_id IN per_all_assignments_f.organization_id%TYPE,
1374 IN_sui_state_code IN VARCHAR2 ) RETURN NUMBER IS
1375 L_return_val NUMBER;
1376
1377 CURSOR c_get_futa_credit_rt (IN_organization_id IN per_all_assignments_f.organization_id%TYPE) IS
1378 SELECT org_information1,org_information15
1379 FROM hr_organization_information
1380 WHERE organization_id = IN_organization_id
1381 AND org_information_context = 'State Tax Rules';
1382
1383 CURSOR c_get_state_code (IN_sui_state_code IN VARCHAR2) Is
1384 SELECT state_abbrev
1385 FROM pay_us_states
1386 WHERE state_code = IN_sui_state_code;
1387
1388 l_flag VARCHAR2(2) := 'F' ;
1389 l_count NUMBER := 0;
1390 l_sui_state_code VARCHAR2(2);
1391 l_futa_state_code VARCHAR2(10);
1392 l_futa_credit_rate NUMBER;
1393
1394 BEGIN
1395
1396 l_count := payusunb_pkg.futa_credit_info.count;
1397
1398 OPEN c_get_state_code(IN_sui_state_code);
1399 FETCH c_get_state_code into l_sui_state_code;
1400 CLOSE c_get_state_code;
1401
1402 hr_utility.trace('L_count ' || to_char(l_count));
1403 hr_utility.trace('Org Id ' || to_char(IN_organization_id));
1404 IF l_count > 0 THEN
1405 For i in 1..l_count
1406 LOOP
1407 hr_utility.trace('IN_sui_state_code : ' || IN_sui_state_code);
1408 IF payusunb_pkg.futa_credit_info.exists(i) THEN
1409 IF payusunb_pkg.futa_credit_info(i).organization_id = IN_organization_id THEN
1410 l_flag := 'T' ;
1411 IF (payusunb_pkg.futa_credit_info(i).sui_state_code = l_sui_state_code) THEN
1412 L_return_val := payusunb_pkg.futa_credit_info(i).futa_credit_rate;
1413 RETURN nvl(L_return_val,-1);
1414 END IF;
1415 END IF;
1416 END IF; -- exists
1417 END LOOP;
1418 END IF;
1419 IF l_flag = 'F' THEN
1420 OPEN c_get_futa_credit_rt ( IN_organization_id );
1421 LOOP
1422 l_count := l_count + 1;
1423 FETCH c_get_futa_credit_rt INTO l_futa_state_code,l_futa_credit_rate ;
1424 EXIT WHEN c_get_futa_credit_rt%NOTFOUND;
1425 hr_utility.trace('State_code pupulated : ' || l_futa_state_code);
1426 payusunb_pkg.futa_credit_info(l_count).organization_id := IN_organization_id;
1427 payusunb_pkg.futa_credit_info(l_count).sui_state_code := l_futa_state_code;
1428 payusunb_pkg.futa_credit_info(l_count).futa_credit_rate := NVL(l_futa_credit_rate,-1);
1429 IF l_futa_state_code = l_sui_state_code THEN
1430 L_return_val := nvl(l_futa_credit_rate,-1); -- -1 to check if no override rate is there
1431 END IF;
1432
1433 END LOOP;
1434 CLOSE c_get_futa_credit_rt;
1435 END IF;
1436 hr_utility.trace('return value : ' || L_return_val);
1437
1438 RETURN nvl(L_return_val,-1);
1439
1440 END fnc_get_futa_credit_rate;
1441
1442
1443
1444 -- New function to cache the sui and sdi override rates
1445 -- Caches for the first time and returns the value later on
1446
1447 FUNCTION fnc_sui_sdi_override ( IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
1448 ,IN_state_code IN VARCHAR2
1449 ,IN_ret_flag IN VARCHAR2) RETURN NUMBER IS
1450
1451 L_return_val NUMBER;
1452
1453 CURSOR c_get_sui_sdi_overide_rt (IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE) IS
1454 SELECT pus.state_code,org_information6/100, org_information7/100 , org_information14/100
1455 FROM hr_organization_information org, pay_us_states pus
1456 WHERE org.org_information1 = pus.state_abbrev
1457 AND pus.state_code between 00 and 99
1458 AND org.organization_id = IN_tax_unit_id
1459 AND org.org_information_context = 'State Tax Rules';
1460
1461 ln_sui_override_rt NUMBER;
1462 ln_sui_dummy_rt NUMBER;
1463 ln_sdi_override_rt NUMBER;
1464 lv_state_code VARCHAR2(2);
1465 ln_count NUMBER;
1466
1467 BEGIN
1468 IF payusunb_pkg.sui_sdi_override_info.count < 1 THEN
1469 hr_utility.trace('Inside the sui_override');
1470 OPEN c_get_sui_sdi_overide_rt ( IN_tax_unit_id );
1471 LOOP
1472 FETCH c_get_sui_sdi_overide_rt INTO lv_state_code,ln_sui_override_rt,ln_sui_dummy_rt,ln_sdi_override_rt ;
1473 EXIT WHEN c_get_sui_sdi_overide_rt%NOTFOUND;
1474 payusunb_pkg.sui_sdi_override_info(lv_state_code).sui_override_rate := ln_sui_override_rt;
1475 payusunb_pkg.sui_sdi_override_info(lv_state_code).sui_dummy_rate := ln_sui_dummy_rt;
1476 payusunb_pkg.sui_sdi_override_info(lv_state_code).sdi_override_rate := ln_sdi_override_rt;
1477 END LOOP;
1478 CLOSE c_get_sui_sdi_overide_rt ;
1479 END IF;
1480
1481 IF payusunb_pkg.sui_sdi_override_info.exists(IN_state_code) THEN
1482 IF IN_ret_flag = 'C' THEN -- calculated value
1483 L_return_val := payusunb_pkg.sui_sdi_override_info(IN_state_code).sui_override_rate ;
1484 ELSIF IN_ret_flag = 'D' THEN -- Dummy value
1485 L_return_val := payusunb_pkg.sui_sdi_override_info(IN_state_code).sui_dummy_rate ;
1486 ELSIF IN_ret_flag = 'SDI' THEN -- SDI value
1487 L_return_val := payusunb_pkg.sui_sdi_override_info(IN_state_code).sdi_override_rate ;
1488 END IF;
1489 END IF;
1490 RETURN L_return_val;
1491
1492 END fnc_sui_sdi_override ;
1493
1494
1495 /**************************************** 3005756 END ***************************************/
1496
1497 /*************************************************************************
1498 routine name: prc_write_data
1499 purpose: Write data to temp table PAY_US_RPT_TOTALS
1500 parameters: IN_record_type - 'V' record is part of tax verification
1501 - 'U' record is part of unacceptable
1502 IN_gre_id -
1503 IN_org_id -
1504 IN_location_id -
1505 IN_pact_id - PYUGEN payroll_action_id
1506 IN_chunk_number -
1507 IN_person_id -
1508 IN_balance_nm - String containing name of balance
1509 IN_taxable -
1510 IN_withheld -
1511 IN_calculated -
1512 IN_difference -
1513 IN_jurisdiction -
1514 IN_message - Corresponding message for each record
1515 IN_sort_code - Derived Jurisdiction code for sorting in report
1516 return: None
1517 specs: Below is the mapping that it used to write processed data to
1518 PAY_US_RPT_TOTALS table. There are two types of records, 1 is
1519 header record and the other is detail record.
1520 column mapping specs for header record:
1521 SESSION_ID := payroll_action_id (PYUGEN Payroll Action)
1522 TAX_UNIT_ID := tax_unit_id (from pay_assignment_actions)
1523 ORGANIZATION_ID := organization_id (from per_assignments_f)
1524 LOCATION_ID := location_id (from per_assignments_f)
1525 BUSINESS_GROUP_ID := chunk number from PYUGEN process
1526 VALUE1 := person_id
1527 GRE_NAME := assignment_number (from per_assignments_f)
1528 STATE_CODE := H indicating this record is header record
1529 VALUE6 := assignment_action_id
1530 Each header record may have multiple detail records and the key
1531 used to link header to detail records is assignment_action_id
1532 stored in value6 column.
1533 column mapping specs for detail record:
1534 SESSION_ID := payroll_action_id (PYUGEN Payroll Action)
1535 VALUE6 := assignment_action_id
1536 BUSINESS_GROUP_ID := chunk number from PYUGEN process
1537 STATE_NAME := jurisdiction_code
1538 STATE_CODE := U if row is data for Unacceptable Balance
1539 V if row is data for Taxable Verification
1540 ORGANIZATION_NAME := IF STATE_CODE = U THEN "Balance 1 Name"
1541 IF STATE_CODE = V THEN "Reported Balance Name"
1542 LOCATION_NAME := IF STATE_CODE = U THEN "Balance 2 Name"
1543 VALUE2 := IF STATE_CODE = U THEN "Balance 1 Name" Value
1544 IF STATE_CODE = V THEN "Tax Balance" Value
1545 VALUE3 := IF STATE_CODE = U THEN "Balance 2 Name" Value
1546 IF STATE_CODE = V THEN "Tax Withheld" Value
1547 VALUE4 := IF STATE_CODE = V THEN "Calculated Withheld" Value
1548 VALUE5 := IF STATE_CODE = V THEN Difference (Value3 - Value4)
1549 ATTRIBUTE1 := IF STATE_CODE = U THEN "Unacceptable Report" Message
1550 IF STATE_CODE = V THEN "Taxable Verification Report" Message
1551 ATTRIBUTE2 := Derived Jurisdiction Code for sorting in report
1552 **************************************************************************/
1553 PROCEDURE prc_write_data (IN_commit_count IN NUMBER,
1554 IN_record_type IN VARCHAR2,
1555 IN_asgn_action_id IN NUMBER,
1556 IN_gre_id IN NUMBER,
1557 IN_org_id IN NUMBER,
1558 IN_location_id IN NUMBER,
1559 IN_pact_id IN NUMBER,
1560 IN_chunk_number IN NUMBER,
1561 IN_person_id IN NUMBER,
1562 IN_assignment_no IN VARCHAR2,
1563 IN_balance_nm1 IN VARCHAR2,
1564 IN_balance_nm2 IN VARCHAR2,
1565 IN_taxable IN NUMBER,
1566 IN_withheld IN NUMBER,
1567 IN_calculated IN NUMBER,
1568 IN_difference IN NUMBER,
1569 IN_jurisdiction IN VARCHAR2,
1570 IN_message IN VARCHAR2,
1571 IN_sort_code IN VARCHAR2,
1572 IN_locked_asg_action_id IN NUMBER,
1573 IN_assign_id IN NUMBER) IS
1574
1575 L_jurisdiction VARCHAR2(30);
1576
1577 BEGIN
1578
1579 IF IN_jurisdiction IS NULL THEN
1580 L_jurisdiction := 'Federal';
1581 ELSE
1582 L_jurisdiction := IN_jurisdiction;
1583 END IF;
1584
1585 IF G_asgn_action_id IS NULL OR G_asgn_action_id <> IN_asgn_action_id THEN
1586
1587 -- if assignment_action_id changed then write new header record
1588 G_asgn_action_id := IN_asgn_action_id;
1589
1590 INSERT INTO pay_us_rpt_totals
1591 (state_code,
1592 tax_unit_id,
1593 organization_id,
1594 location_id,
1595 session_id,
1596 business_group_id,
1597 value1,
1598 gre_name,
1599 value6
1600 )
1601 VALUES
1602 ('H',
1603 IN_gre_id,
1604 IN_org_id,
1605 IN_location_id,
1606 IN_pact_id,
1607 IN_chunk_number,
1608 IN_person_id,
1609 IN_assignment_no,
1610 IN_asgn_action_id
1611 );
1612
1613 /******************************** 2963239 Change ***********************************************************/
1614
1615 -- insert the action record.
1616
1617 hr_nonrun_asact.insact(IN_asgn_action_id,IN_assign_id,IN_pact_id,IN_chunk_number,IN_gre_id);
1618
1619 -- Bug 3291736: Change the flag to Y as the assignment action is created
1620 G_dummy_action_inserted_flag := 'Y';
1621
1622 /******************************** END ******************************************************************/
1623
1624 END IF;
1625
1626 -- write data for taxable verification/unacceptable portion of report
1627 INSERT INTO pay_us_rpt_totals
1628 (state_code,
1629 tax_unit_id,
1630 session_id,
1631 business_group_id,
1632 organization_name,
1633 location_name, -- NULL
1634 value2,
1635 value3,
1636 value4,
1637 value5,
1638 value6,
1639 state_name,
1640 attribute1,
1641 attribute2
1642 )
1643 VALUES
1644 (IN_record_type,
1645 IN_gre_id,
1646 IN_pact_id,
1647 IN_chunk_number,
1648 IN_balance_nm1,
1649 IN_balance_nm2, -- NULL
1650 IN_taxable,
1651 IN_withheld,
1652 IN_calculated, -- NULL
1653 IN_difference, -- NULL
1654 IN_asgn_action_id,
1655 L_jurisdiction,
1656 IN_message,
1657 IN_sort_code
1658 );
1659
1660 G_commit_count := G_commit_count - 1;
1661 IF G_commit_count = 0 THEN
1662 COMMIT;
1663 G_commit_count := IN_commit_count;
1664 END IF;
1665 END prc_write_data;
1666
1667 /*************************************************************************
1668 routine name: fnc_sit_exists
1669 purpose: checks table pay_us_state_tax_info_f and return TRUE/FALSE
1670 based on value of column sit_exists.
1671 parameters: IN_state_code -
1672 IN_as_of_date -
1673 return: TRUE/FALSE
1674 specs:
1675 **************************************************************************/
1676 FUNCTION fnc_sit_exists(IN_state_code IN pay_us_states.state_code%TYPE,
1677 IN_as_of_date IN DATE) RETURN BOOLEAN IS
1678
1679 /********************* 3005756 START ************************************/
1680
1681 L_sit_exists pay_us_state_tax_info_f.sit_exists%TYPE;
1682
1683 BEGIN
1684
1685 IF pay_us_payroll_utils.ltr_state_tax_info.count < 1 THEN
1686 pay_us_payroll_utils.populate_jit_information ( p_effective_date => IN_as_of_date
1687 ,p_get_state => 'Y' );
1688 END IF;
1689
1690 IF pay_us_payroll_utils.ltr_state_tax_info.exists(IN_state_code) THEN
1691 L_sit_exists := pay_us_payroll_utils.ltr_state_tax_info(IN_state_code).sit_exists ;
1692 END IF;
1693
1694 /************************** 3005756 END ********************************************/
1695
1696 IF L_sit_exists = 'Y' THEN
1697 RETURN TRUE;
1698 ELSE
1699 RETURN FALSE;
1700 END IF;
1701 END fnc_sit_exists;
1702
1703 /*************************************************************************
1704 routine name: fnc_lit_tax_exists
1705 purpose: checks table pay_us_city_tax_info_f and return TRUE/FALSE
1706 based on value of column city_tax or school_tax flag
1707 depending on IN_lit string.
1708 IF IN_lit is 'COUNTY' then checks table pay_us_county_tax_info_f
1709 and return TRUE/FALSE based on value of column county_tax.
1710 parameters: IN_jurisdiction -
1711 IN_as_of_date -
1712 IN_lit - string contain 'CITY' or 'SCHOOL' or 'COUNTY'
1713 return: TRUE/FALSE
1714 specs:
1715 **************************************************************************/
1716 FUNCTION fnc_lit_tax_exists(IN_jurisdiction IN pay_us_county_tax_info_f.jurisdiction_code%TYPE,
1717 IN_as_of_date IN DATE,
1718 IN_lit IN VARCHAR2) RETURN BOOLEAN IS
1719
1720 /******************* 3005756 changes start *********************************/
1721
1722 CURSOR c_check_city IS
1723 SELECT city_tax,school_tax
1724 FROM pay_us_city_tax_info_f
1725 WHERE jurisdiction_code = IN_jurisdiction
1726 AND IN_as_of_date BETWEEN effective_start_date AND effective_end_date;
1727
1728
1729 CURSOR c_check_county IS
1730 SELECT county_tax,school_tax
1731 FROM pay_us_county_tax_info_f
1732 WHERE jurisdiction_code = IN_jurisdiction
1733 AND IN_as_of_date BETWEEN effective_start_date AND effective_end_date;
1734
1735
1736 L_jurisdiction_code VARCHAR2(11);
1737 L_city_tax VARCHAR2(1);
1738 L_school_tax VARCHAR2(1);
1739 L_county_tax VARCHAR2(1);
1740
1741 lv_state_code VARCHAR2(20);
1742 lv_county_code VARCHAR2(20);
1743 lv_city_code VARCHAR2(20);
1744 lv_temp_code VARCHAR2(20);
1745 ln_index_code NUMBER;
1746
1747 L_tax_flag VARCHAR2(1);
1748
1749 BEGIN
1750
1751 lv_state_code := substr(IN_jurisdiction,1,2);
1752 lv_county_code := substr(IN_jurisdiction,4,3);
1753 lv_city_code := substr(IN_jurisdiction,8,4);
1754
1755 lv_temp_code := lv_state_code||lv_county_code||lv_city_code;
1756 ln_index_code := to_number(lv_temp_code);
1757
1758
1759 IF IN_lit = 'CITY' THEN
1760 IF payusunb_pkg.city_tax_info.exists(ln_index_code) THEN
1761 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists;
1762 ELSE
1763 OPEN c_check_city ;
1764 FETCH c_check_city INTO L_city_tax,L_school_tax;
1765 payusunb_pkg.city_tax_info(ln_index_code).jurisdiction_code := IN_jurisdiction;
1766 payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists := L_city_tax;
1767 payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists := L_school_tax;
1768 CLOSE c_check_city;
1769 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists;
1770 END IF;
1771
1772 ELSIF IN_lit = 'SCHOOL' THEN
1773 IF payusunb_pkg.city_tax_info.exists(ln_index_code) THEN
1774 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists;
1775 ELSE
1776 OPEN c_check_city ;
1777 FETCH c_check_city INTO L_city_tax,L_school_tax;
1778 payusunb_pkg.city_tax_info(ln_index_code).jurisdiction_code := IN_jurisdiction;
1779 payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists := L_city_tax;
1780 payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists := L_school_tax;
1781 CLOSE c_check_city;
1782 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists;
1783 END IF;
1784
1785 ELSIF IN_lit = 'COUNTY' THEN
1786 IF payusunb_pkg.county_tax_info.exists(ln_index_code) THEN
1787 L_tax_flag := payusunb_pkg.county_tax_info(ln_index_code).cnty_tax_exists;
1788 ELSE
1789 OPEN c_check_county;
1790 FETCH c_check_county INTO L_county_tax,L_school_tax;
1791 payusunb_pkg.county_tax_info(ln_index_code).jurisdiction_code := IN_jurisdiction;
1792 payusunb_pkg.county_tax_info(ln_index_code).cnty_tax_exists := L_county_tax;
1793 payusunb_pkg.county_tax_info(ln_index_code).cnty_sd_tax_exists := L_school_tax;
1794 CLOSE c_check_county;
1795 L_tax_flag := payusunb_pkg.county_tax_info(ln_index_code).cnty_tax_exists;
1796 END IF;
1797
1798 END IF;
1799
1800 IF L_tax_flag = 'Y' THEN
1801 RETURN TRUE;
1802 ELSE
1803 RETURN FALSE;
1804 END IF;
1805
1806 /****************************** 3005756 Changes End ********************************/
1807
1808 END fnc_lit_tax_exists;
1809
1810
1811 /************************* 2963239 Change *******************************************/
1812
1813 /*************************************************************************
1814 routine name: prc_process_data
1815 purpose: Does the entire processing for unacceptable balance report
1816 and Dumps the data to table PAY_US_RPT_TOTALS
1817 parameters: IN_pact_id -
1818 IN_chunk_no -
1819 IN_commit_count -
1820 IN_lockingactid -
1821 IN_lockedactid -
1822 IN_assignment_id -
1823 IN_tax_unit_id -
1824 IN_person_id -
1825 IN_location_id -
1826 IN_organization_id -
1827 IN_assignment_number -
1828
1829 return: None
1830 specs:
1831 **************************************************************************/
1832 PROCEDURE prc_process_data(IN_pact_id IN pay_payroll_actions.payroll_action_id%TYPE,
1833 IN_chunk_no IN NUMBER,
1834 IN_commit_count IN NUMBER DEFAULT 1000,
1835 IN_prc_lockingactid IN pay_assignment_actions.assignment_action_id%TYPE,
1836 IN_prc_lockedactid IN pay_assignment_actions.assignment_action_id%TYPE,
1837 IN_prc_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1838 IN_prc_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1839 IN_prc_person_id IN per_all_assignments_f.person_id%TYPE,
1840 IN_prc_location_id IN per_all_assignments_f.location_id%TYPE,
1841 IN_prc_organization_id IN per_all_assignments_f.organization_id%TYPE,
1842 IN_prc_assignment_number IN per_all_assignments_f.assignment_number%TYPE ) IS
1843
1844 /****************************** END ***************************************************/
1845
1846
1847 /*************************** 3005756 start **********************************************/
1848 -- Run Balance cursors
1849 -- get all state level jurisdiction codes for specified person
1850 CURSOR c_state_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1851 IN_state_code IN VARCHAR2,
1852 IN_start_date IN DATE,
1853 IN_as_of_date IN DATE) IS
1854 SELECT DISTINCT
1855 prb.jurisdiction_code||'-000-0000' jurisdiction_code,
1856 pus.state_code state_code,
1857 pus.state_abbrev
1858 FROM pay_run_balances prb,
1859 per_assignments_f paf,
1860 pay_us_states pus
1861 WHERE paf.person_id = IN_person_id
1862 AND prb.effective_date BETWEEN IN_start_date and IN_as_of_date
1863 AND prb.effective_date BETWEEN paf.effective_start_date
1864 AND paf.effective_end_date
1865 AND prb.assignment_id = paf.assignment_id
1866 AND prb.jurisdiction_code = pus.state_code
1867 AND (pus.state_code = IN_state_code
1868 OR IN_state_code IS NULL);
1869
1870
1871 -- get all county level jurisdiction codes for specified person
1872 CURSOR c_county_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1873 IN_state_code IN VARCHAR2,
1874 IN_start_date IN DATE,
1875 IN_as_of_date IN DATE) IS
1876 SELECT DISTINCT
1877 prb.jurisdiction_code||'-0000' jurisdiction_code,
1878 puc.county_name||','||pus.state_abbrev jurisdiction_name
1879 FROM pay_run_balances prb,
1880 per_assignments_f paf,
1881 pay_us_states pus,
1882 pay_us_counties puc
1883 WHERE paf.person_id = IN_person_id
1884 AND paf.effective_start_date <= IN_as_of_date
1885 AND paf.effective_end_date >= IN_start_date
1886 AND prb.assignment_id = paf.assignment_id
1887 AND prb.effective_date BETWEEN paf.effective_start_date
1888 AND paf.effective_end_date
1889 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1890 AND pus.state_code = prb.jurisdiction_comp1
1891 AND (pus.state_code = IN_state_code
1892 OR IN_state_code IS NULL)
1893 AND prb.jurisdiction_code = puc.state_code||'-'||puc.county_code
1894 AND pus.state_code = puc.state_code;
1895
1896
1897 -- get all city level jurisdiction codes for specified person
1898 CURSOR c_city_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1899 IN_state_code IN VARCHAR2,
1900 IN_start_date IN DATE,
1901 IN_as_of_date IN DATE) IS
1902 SELECT DISTINCT
1903 prb.jurisdiction_code,
1904 pun.city_name||','||pus.state_abbrev jurisdiction_name
1905 FROM pay_run_balances prb,
1906 per_assignments_f paf,
1907 pay_us_states pus,
1908 pay_us_city_names pun
1909 WHERE paf.person_id = IN_person_id
1910 AND paf.effective_start_date <= IN_as_of_date
1911 AND paf.effective_end_date >= IN_start_date
1912 AND paf.assignment_id = prb.assignment_id
1913 AND prb.effective_date BETWEEN paf.effective_start_date
1914 AND paf.effective_end_date
1915 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1916 AND prb.jurisdiction_code =
1917 pun.state_code||'-'||pun.county_code||'-'||pun.city_code
1918 AND pun.primary_flag = 'Y'
1919 AND prb.jurisdiction_comp2 = pun.county_code
1920 AND prb.jurisdiction_comp3 = pun.city_code
1921 AND pun.state_code = pus.state_code
1922 AND (pus.state_code = IN_state_code
1923 OR IN_state_code IS NULL)
1924 AND pus.state_code = prb.jurisdiction_comp1;
1925
1926
1927 -- get all city level jurisdiction codes for specified person
1928 CURSOR c_school_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1929 IN_state_code IN VARCHAR2,
1930 IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1931 IN_start_date IN DATE,
1932 IN_as_of_date IN DATE) IS
1933 SELECT DISTINCT
1934 prb.jurisdiction_code,
1935 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1936 psd.state_code||'-'||psd.county_code||'-'||psd.city_code reg_jurisdiction_cd
1937 FROM pay_run_balances prb,
1938 per_assignments_f paf,
1939 pay_us_states pus,
1940 pay_us_city_school_dsts psd
1941 WHERE paf.person_id = IN_person_id
1942 AND paf.effective_start_date <= IN_as_of_date
1943 AND paf.effective_end_date >= IN_start_date
1944 AND paf.assignment_id = prb.assignment_id
1945 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1946 AND prb.effective_date BETWEEN paf.effective_start_date
1947 AND paf.effective_end_date
1948 AND prb.jurisdiction_code = psd.state_code||'-'||psd.school_dst_code
1949 AND prb.jurisdiction_comp2 = psd.school_dst_code
1950 AND prb.jurisdiction_comp1 = psd.state_code
1951 AND (pus.state_code = IN_state_code
1952 OR IN_state_code IS NULL)
1953 AND prb.jurisdiction_comp1 = pus.state_code
1954 AND pus.state_code = psd.state_code
1955 UNION ALL
1956 SELECT /*+ ORDERED */DISTINCT
1957 prb.jurisdiction_code,
1958 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1959 psd.state_code||'-'||psd.county_code||'-0000' reg_jurisdiction_cd
1960 FROM per_assignments_f paf,
1961 pay_run_balances prb,
1962 pay_us_states pus,
1963 pay_us_county_school_dsts psd
1964 WHERE paf.person_id = IN_person_id
1965 AND paf.effective_start_date <= IN_as_of_date
1966 AND paf.effective_end_date >= IN_start_date
1967 AND prb.assignment_id = paf.assignment_id
1968 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1969 AND prb.effective_date BETWEEN paf.effective_start_date
1970 AND paf.effective_end_date
1971 AND prb.jurisdiction_code = psd.state_code||'-'||psd.school_dst_code
1972 AND prb.jurisdiction_comp2 = psd.school_dst_code
1973 AND prb.jurisdiction_comp1 = psd.state_code
1974 AND (pus.state_code = IN_state_code
1975 OR IN_state_code IS NULL)
1976 AND prb.jurisdiction_comp1 = pus.state_code
1977 AND pus.state_code = psd.state_code;
1978
1979
1980 -- Original run result cursors
1981
1982
1983 -- get all state level jurisdiction codes for specified person
1984 CURSOR c_state_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
1985 IN_state_code IN VARCHAR2,
1986 IN_start_date IN DATE,
1987 IN_as_of_date IN DATE) IS
1988 SELECT DISTINCT
1989 pes.jurisdiction_code,
1990 pes.state_code,
1991 pus.state_abbrev
1992 FROM pay_us_emp_state_tax_rules_f pes,
1993 per_assignments_f paf,
1994 pay_us_states pus
1995 WHERE pes.assignment_id = paf.assignment_id
1996 AND pes.state_code = pus.state_code
1997 AND paf.effective_start_date BETWEEN pes.effective_start_date
1998 AND pes.effective_end_date
1999 /* Change for Performance Bug 6360505 */
2000 AND IN_as_of_date BETWEEN paf.effective_start_date
2001 AND paf.effective_end_date
2002 /* Change for Performance Bug 6360505 */
2003 /* 8606883 - commented as this wouldn't pick up records
2004 if there is an assignment update between start_date and
2005 as_of_date */
2006 /* AND IN_start_date BETWEEN paf.effective_start_date
2007 AND paf.effective_end_date*/
2008 AND paf.person_id = IN_person_id
2009 /* Change for Performance Bug 7174993 */
2010 AND (pus.state_code = IN_state_code
2011 OR IN_state_code IS NULL)
2012 -- AND pus.state_code = NVL(IN_state_code, pus.state_code)
2013 AND EXISTS (
2014 SELECT 'X'
2015 FROM pay_payroll_actions ppa,
2016 pay_assignment_actions paa,
2017 pay_run_results prr
2018 WHERE action_type IN ('B','I','R','Q','V')
2019 AND ppa.action_status = 'C'
2020 AND ppa.effective_date BETWEEN IN_start_date
2021 AND IN_as_of_date
2022 AND paa.payroll_action_id = ppa.payroll_action_id
2023 AND paa.assignment_id = pes.assignment_id
2024 AND prr.assignment_action_id = paa.assignment_action_id
2025 AND prr.jurisdiction_code = pes.jurisdiction_code
2026 AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
2027
2028
2029
2030 -- get all county level jurisdiction codes for specified person
2031 CURSOR c_county_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
2032 IN_state_code IN VARCHAR2,
2033 IN_start_date IN DATE,
2034 IN_as_of_date IN DATE) IS
2035 SELECT DISTINCT pes.jurisdiction_code,
2036 puc.county_name||','||pus.state_abbrev jurisdiction_name
2037 FROM pay_us_emp_county_tax_rules_f pes,
2038 per_assignments_f paf,
2039 pay_us_states pus,
2040 pay_us_counties puc
2041 WHERE pes.assignment_id = paf.assignment_id
2042 AND pes.state_code = pus.state_code
2043 AND pes.county_code = puc.county_code
2044 AND pes.state_code = puc.state_code
2045 AND paf.effective_start_date BETWEEN pes.effective_start_date
2046 AND pes.effective_end_date
2047 /* Change for Performance Bug 6360505 */
2048 AND IN_as_of_date BETWEEN paf.effective_start_date
2049 AND paf.effective_end_date
2050 /* Change for Performance Bug 6360505 */
2051 AND IN_start_date BETWEEN paf.effective_start_date
2052 AND paf.effective_end_date
2053 AND paf.person_id = IN_person_id
2054 /* Change for Performance Bug 7174993 */
2055 AND (pus.state_code = IN_state_code
2056 OR IN_state_code IS NULL)
2057 --AND pus.state_code = NVL(IN_state_code, pus.state_code)
2058 AND EXISTS (
2059 SELECT 'X'
2060 FROM pay_payroll_actions ppa,
2061 pay_assignment_actions paa,
2062 pay_run_results prr
2063 WHERE action_type IN ('B','I','R','Q','V')
2064 AND ppa.action_status = 'C'
2065 AND ppa.effective_date BETWEEN IN_start_date
2066 AND IN_as_of_date
2067 AND paa.payroll_action_id = ppa.payroll_action_id
2068 AND paa.assignment_id = pes.assignment_id
2069 AND prr.assignment_action_id = paa.assignment_action_id
2070 AND prr.jurisdiction_code = pes.jurisdiction_code
2071 AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
2072
2073
2074 -- get all city level jurisdiction codes for specified person
2075 CURSOR c_city_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
2076 IN_state_code IN VARCHAR2,
2077 IN_start_date IN DATE,
2078 IN_as_of_date IN DATE) IS
2079 SELECT DISTINCT
2080 pes.jurisdiction_code,
2081 pun.city_name||','||pus.state_abbrev jurisdiction_name
2082 FROM pay_us_emp_city_tax_rules_f pes,
2083 per_assignments_f paf,
2084 pay_us_states pus,
2085 pay_us_city_names pun
2086 WHERE pes.assignment_id = paf.assignment_id
2087 AND pes.state_code = pus.state_code
2088 AND pes.state_code = pun.state_code
2089 AND pes.county_code = pun.county_code
2090 AND pes.city_code = pun.city_code
2091 AND paf.effective_start_date BETWEEN pes.effective_start_date
2092 AND pes.effective_end_date
2093 AND pun.primary_flag = 'Y'
2094 /* Change for Performance Bug 6360505 */
2095 AND IN_as_of_date BETWEEN paf.effective_start_date
2096 AND paf.effective_end_date
2097 /* Change for Performance Bug 6360505 */
2098 AND IN_start_date BETWEEN paf.effective_start_date
2099 AND paf.effective_end_date
2100 AND paf.person_id = IN_person_id
2101 /* Change for Performance Bug 7174993 */
2102 AND (pus.state_code = IN_state_code
2103 OR IN_state_code IS NULL)
2104 -- AND pus.state_code = NVL(IN_state_code, pus.state_code)
2105 AND EXISTS (
2106 SELECT 'X'
2107 FROM pay_payroll_actions ppa,
2108 pay_assignment_actions paa,
2109 pay_run_results prr
2110 WHERE action_type IN ('B','I','R','Q','V')
2111 AND ppa.action_status = 'C'
2112 AND ppa.effective_date BETWEEN IN_start_date
2113 AND IN_as_of_date
2114 AND paa.payroll_action_id = ppa.payroll_action_id
2115 AND paa.assignment_id = pes.assignment_id
2116 AND prr.assignment_action_id = paa.assignment_action_id
2117 AND prr.jurisdiction_code = pes.jurisdiction_code
2118 AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
2119
2120
2121 -- get all city level jurisdiction codes for specified person
2122 CURSOR c_school_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
2123 IN_state_code IN VARCHAR2,
2124 IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
2125 IN_start_date IN DATE,
2126 IN_as_of_date IN DATE) IS
2127 SELECT DISTINCT
2128 pes.state_code||'-'||pes.school_district_code jurisdiction_code,
2129 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
2130 pes.jurisdiction_code reg_jurisdiction_cd
2131 FROM pay_us_emp_city_tax_rules_f pes,
2132 per_assignments_f paf,
2133 pay_us_states pus,
2134 pay_us_city_school_dsts psd
2135 WHERE pes.assignment_id = paf.assignment_id
2136 AND pes.school_district_code IS NOT NULL
2137 AND pes.state_code = pus.state_code
2138 AND pes.school_district_code = psd.school_dst_code
2139 AND pes.state_code = psd.state_code
2140 AND pes.county_code = psd.county_code
2141 AND pes.city_code = psd.city_code
2142 AND paf.effective_start_date BETWEEN pes.effective_start_date
2143 AND pes.effective_end_date
2144 /* Change for Performance Bug 6360505 */
2145 AND IN_as_of_date BETWEEN paf.effective_start_date
2146 AND paf.effective_end_date
2147 /* Change for Performance Bug 6360505 */
2148 AND IN_start_date BETWEEN paf.effective_start_date
2149 AND paf.effective_end_date
2150 AND paf.person_id = IN_person_id
2151 --AND pus.state_code = NVL(IN_state_code, pus.state_code)
2152 /* Change for Performance Bug 7174993 */
2153 AND (pus.state_code = IN_state_code
2154 OR IN_state_code IS NULL)
2155 AND EXISTS (
2156 SELECT 'X'
2157 FROM pay_payroll_actions ppa,
2158 pay_assignment_actions paa,
2159 pay_run_results prr
2160 WHERE action_type IN ('B','I','R','Q','V')
2161 AND ppa.action_status = 'C'
2162 AND ppa.effective_date BETWEEN IN_start_date
2163 AND IN_as_of_date
2164 AND paa.payroll_action_id = ppa.payroll_action_id
2165 AND paa.assignment_id = pes.assignment_id
2166 AND prr.assignment_action_id = paa.assignment_action_id
2167 AND prr.jurisdiction_code = pes.state_code||'-'||pes.school_district_code
2168 AND rownum = 1) -- Added rownum for perfromance enhancement (Bug 3316599)
2169 UNION
2170 SELECT DISTINCT
2171 pes.state_code||'-'||pes.school_district_code jurisdiction_code,
2172 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
2173 pes.jurisdiction_code reg_jurisdiction_cd
2174 FROM pay_us_emp_county_tax_rules_f pes,
2175 per_assignments_f paf,
2176 pay_us_states pus,
2177 pay_us_county_school_dsts psd
2178 WHERE pes.assignment_id = paf.assignment_id
2179 AND pes.school_district_code IS NOT NULL
2180 AND pes.state_code = pus.state_code
2181 AND pes.school_district_code = psd.school_dst_code
2182 AND pes.state_code = psd.state_code
2183 AND pes.county_code = psd.county_code
2184 AND paf.effective_start_date BETWEEN pes.effective_start_date
2185 AND pes.effective_end_date
2186 /* Change for Performance Bug 6360505 */
2187 AND IN_as_of_date BETWEEN paf.effective_start_date
2188 AND paf.effective_end_date
2189 /* Change for Performance Bug 6360505 */
2190 AND IN_start_date BETWEEN paf.effective_start_date
2191 AND paf.effective_end_date
2192 AND paf.person_id = IN_person_id
2193 --AND pus.state_code = NVL(IN_state_code, pus.state_code)
2194 /* Change for Performance Bug 7174993 */
2195 AND (pus.state_code = IN_state_code
2196 OR IN_state_code IS NULL)
2197 AND EXISTS (
2198 SELECT 'X'
2199 FROM pay_payroll_actions ppa,
2200 pay_assignment_actions paa,
2201 pay_run_results prr
2202 WHERE action_type IN ('B','I','R','Q','V')
2203 AND ppa.action_status = 'C'
2204 AND ppa.effective_date BETWEEN IN_start_date
2205 AND IN_as_of_date
2206 AND paa.payroll_action_id = ppa.payroll_action_id
2207 AND paa.assignment_id = pes.assignment_id
2208 AND prr.assignment_action_id = paa.assignment_action_id
2209 AND prr.jurisdiction_code = pes.state_code||'-'||pes.school_district_code
2210 AND rownum = 1); -- Added rownum for perfromance enhancement (Bug 3316599)
2211
2212 /*************************************** 3005756 end ****************************************************/
2213
2214 CURSOR c_get_sui_state_code (IN_business_id IN NUMBER,
2215 IN_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2216 IN_start_date IN DATE,
2217 IN_as_of_date IN DATE) IS
2218 SELECT NVL(sui_state_code,'00')
2219 FROM pay_us_emp_fed_tax_rules_f
2220 WHERE business_group_id = IN_business_id
2221 AND assignment_id = IN_assignment_id
2222 AND effective_start_date <= IN_start_date
2223 AND effective_end_date >= IN_as_of_date;
2224
2225 L_as_of_date DATE := NULL;
2226 L_start_date DATE ;
2227 L_leg_param pay_payroll_actions.legislative_parameters%TYPE;
2228 L_gre_id pay_assignment_actions.tax_unit_id%TYPE;
2229 L_org_id per_all_assignments_f.organization_id%TYPE;
2230 L_location_id per_all_assignments_f.location_id%TYPE;
2231 L_business_id per_all_assignments_f.business_group_id%TYPE;
2232 L_dimension VARCHAR2(20) := NULL;
2233 L_tax_type VARCHAR2(20) := NULL;
2234 L_tax_type_state VARCHAR2(20) := NULL;
2235 L_usr_SDI_ER_rate NUMBER := NULL;
2236 L_usr_SDI_EE_rate NUMBER := NULL;
2237 L_asg_action_id NUMBER ;
2238 L_first_half_date DATE ;
2239 L_sui_state_code VARCHAR2(2);
2240 L_calc_rate NUMBER := NULL;
2241 L_dummy_rate NUMBER ;
2242 L_futa_override_rt NUMBER := 0;
2243 L_futa_credit_rt NUMBER := 0;
2244 L_first_half_rate NUMBER ;
2245 L_second_half_rate NUMBER ;
2246 L_difference NUMBER ;
2247 L_calculated NUMBER ;
2248 L_medi_exempt VARCHAR2(1); -- added by tmehra
2249 -- for bug#1158217
2250
2251 -- FUTA balance variables
2252 L_futa_bal NUMBER := 0;
2253 L_futa_tax NUMBER := 0;
2254
2255 -- Medicare balance variables
2256 L_medi_ee_bal NUMBER := 0;
2257 L_medi_ee_tax NUMBER := 0;
2258 L_medi_er_bal NUMBER := 0;
2259 L_medi_er_tax NUMBER := 0;
2260 L_medi_er_liability NUMBER := 0;
2261
2262 /* Added for Bug#9796821 starts */
2263 L_medi_ee_lmt1 NUMBER := 0;
2264 L_medi_ee_rate1 NUMBER := 0;
2265 L_defined_balance_id NUMBER := 0;
2266 L_medi_ee_taxable_over_lmt NUMBER := 0;
2267 L_medi_ee_bal_ytd NUMBER := 0;
2268 /* Added for Bug#9796821 ends */
2269
2270 -- SS balance variables
2271 L_ss_ee_bal NUMBER := 0;
2272 L_ss_ee_tax NUMBER := 0;
2273 L_ss_er_bal NUMBER := 0;
2274 L_ss_er_liability NUMBER := 0;
2275
2276 -- SUI balance variables(only for YTD )
2277 L_sui_ee_bal_first NUMBER := 0;
2278 L_sui_er_bal_first NUMBER := 0;
2279
2280 L_sum_sui_er_bal NUMBER := 0;
2281 L_sui_ee_tax NUMBER := 0;
2282 L_sui_ee_bal NUMBER := 0;
2283 L_sui_er_tax NUMBER := 0;
2284 L_sui_er_bal NUMBER := 0;
2285 L_sui_ee_subj_whable NUMBER := 0;
2286 L_sui_er_subj_whable NUMBER := 0;
2287
2288 -- SDI balance variables
2289 L_sdi_ee_bal NUMBER := 0;
2290 L_sdi_ee_tax NUMBER := 0;
2291 L_sdi_er_bal NUMBER := 0;
2292 L_sdi_er_tax NUMBER := 0;
2293 L_sum_sdi_ee_bal NUMBER := 0;
2294 L_sdi_ee_subj_whable NUMBER := 0;
2295 L_sdi_ee_subj_nwhable NUMBER := 0;
2296
2297 -- SIT balance variables
2298 L_sit_ee_subject NUMBER := 0;
2299 L_sit_ee_withheld NUMBER := 0;
2300 L_sit_ee_pretax_redns NUMBER := 0;
2301 L_sit_ee_subj_whable NUMBER := 0;
2302 L_sit_ee_subj_nwhable NUMBER := 0;
2303 L_sit_ee_reduced_s_whable NUMBER := 0;
2304
2305 --FIT balance variables
2306 L_fit_ee_gross_earnings NUMBER := 0;
2307 L_fit_ee_reduced_s_whable NUMBER := 0;
2308 L_fit_ee_tax NUMBER := 0;
2309 L_fit_ee_subject NUMBER := 0;
2310
2311 -- these balances are for deriving other fit balances
2312 L_fit_ee_subj_whable NUMBER := 0;
2313 L_fit_ee_subj_nwhable NUMBER := 0;
2314 L_fit_ee_pretax_redns NUMBER := 0;
2315
2316
2317 -- LIT City balance variables
2318 L_city_ee_tax NUMBER := 0;
2319 L_city_ee_subject NUMBER := 0;
2320 L_city_ee_r_s_whable NUMBER := 0;
2321 L_city_ee_s_whable NUMBER := 0;
2322 L_city_ee_s_nwhable NUMBER := 0;
2323
2324 -- LIT County balance variables
2325 L_county_ee_tax NUMBER := 0;
2326 L_county_ee_subject NUMBER := 0;
2327 L_county_ee_r_s_whable NUMBER := 0;
2328 L_county_ee_s_whable NUMBER := 0;
2329 L_county_ee_s_nwhable NUMBER := 0;
2330
2331 -- LIT School balance variables
2332 L_school_ee_tax NUMBER := 0;
2333 L_school_ee_subject NUMBER := 0;
2334 L_school_ee_r_s_whable NUMBER := 0;
2335 L_school_ee_s_whable NUMBER := 0;
2336 L_school_ee_s_nwhable NUMBER := 0;
2337
2338 /****************** Bug 2963239 Changes start ******************************************
2339 *** Flags for SS and medicare balances.Set the flags when the balances are fetched ******/
2340
2341 L_medi_ee_bal_flg VARCHAR2(1) := 'F';
2342 L_medi_er_bal_flg VARCHAR2(1) := 'F';
2343 L_ss_ee_bal_flg VARCHAR2(1) := 'F';
2344 L_ss_er_bal_flg VARCHAR2(1) := 'F';
2345
2346 /*Added for Bug#9721787*/
2347 l_asg_exists Number;
2348 l_cursor_fetch_date DATE;
2349 /*End Bug#9721787*/
2350
2351
2352
2353 -- Message variables for prc_write_data
2354
2355 L_balance_nm1 VARCHAR2(150);
2356 L_balance_nm2 VARCHAR2(150);
2357 L_main_mesg VARCHAR2(150);
2358
2359 -----------------------------------------------
2360 --
2361 -- changes made be tmehra
2362 --
2363 FUNCTION f_check_medi_exempt(f_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
2364 f_start_date IN DATE,
2365 f_as_of_date IN DATE) RETURN VARCHAR2 IS
2366
2367 CURSOR c_chk_medi_exempt(IN_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
2368 IN_start_date IN DATE,
2369 IN_as_of_date IN DATE) IS
2370 SELECT medicare_tax_exempt
2371 FROM pay_us_emp_fed_tax_rules_v
2372 WHERE assignment_id = IN_assignment_id
2373 AND effective_start_date <= IN_start_date
2374 AND effective_end_date >= IN_as_of_date;
2375
2376 l_exempt_status VARCHAR2(1);
2377
2378 BEGIN
2379
2380 l_exempt_status := 'N';
2381
2382 FOR i in c_chk_medi_exempt (f_assignment_id,
2383 f_start_date,
2384 f_as_of_date)
2385 LOOP
2386 l_exempt_status := i.medicare_tax_exempt;
2387 END LOOP;
2388
2389 RETURN l_exempt_status;
2390
2391 END; -- end of function f_check_medi_exempt
2392
2393
2394 /******************************** 3005756 start ******************************************/
2395
2396 -- prc_federal_balances
2397 -- prc_state_balances
2398 -- prc_county_balances
2399 -- prc_city_balances
2400 -- prc_school_balances
2401
2402
2403 PROCEDURE prc_federal_balances
2404 IS
2405 BEGIN
2406 IF L_tax_type IS NULL OR (L_tax_type <> 'SIT' AND L_tax_type <> 'LIT') THEN
2407 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2408 L_as_of_date, L_dimension, 'FIT', 'GROSS', 'EE',
2409 NULL, L_fit_ee_gross_earnings);
2410
2411 END IF;
2412
2413 -- The following balance will be required if tax type is FIT or Medicare
2414 IF (L_tax_type = 'FIT' OR L_tax_type = 'Medicare' OR L_tax_type = 'SIT' OR L_tax_type IS NULL)
2415 AND L_medi_exempt = 'N' THEN
2416
2417 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2418 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'EE',
2419 NULL, L_medi_ee_bal);
2420 L_medi_ee_bal_flg := 'T' ;
2421
2422 -- added new pre-tax balance - tmehra
2423 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2424 L_as_of_date,L_dimension, 'FIT', 'PRE_TAX_REDNS', 'EE',
2425 NULL, L_fit_ee_pretax_redns);
2426 hr_utility.trace('FIT - prc_federal_balances - L_fit_ee_pretax_redns: ' || L_fit_ee_pretax_redns);
2427 END IF;
2428
2429 -- if tax_type is anything but Medicare, SS, FUTA,
2430 -- then get subj whable balance for later use
2431 IF (L_tax_type <> 'Medicare' AND L_tax_type <> 'SS' AND L_tax_type <> 'FUTA')
2432 OR L_tax_type IS NULL THEN
2433 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2434 L_as_of_date, L_dimension, 'FIT', 'SUBJ_WHABLE', 'EE',
2435 NULL, L_fit_ee_subj_whable);
2436 hr_utility.trace('NOT FUTA- prc_federal_balances - L_fit_ee_subj_whable: ' || L_fit_ee_subj_whable);
2437 END IF;
2438
2439 IF L_tax_type = 'FIT' OR L_tax_type = 'SIT' OR L_tax_type IS NULL THEN
2440 hr_utility.trace('prc_federal_balances - L_fit_ee_subj_whable: ' || L_fit_ee_subj_whable);
2441 hr_utility.trace('prc_federal_balances - L_fit_ee_pretax_redns: ' || L_fit_ee_pretax_redns);
2442 L_fit_ee_reduced_s_whable := L_fit_ee_subj_whable - L_fit_ee_pretax_redns;
2443 hr_utility.trace('prc_federal_balances - L_fit_ee_reduced_s_whable: ' || L_fit_ee_reduced_s_whable);
2444 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2445 L_as_of_date, L_dimension, 'FIT', 'SUBJ_NWHABLE', 'EE',
2446 NULL, L_fit_ee_subj_nwhable);
2447 hr_utility.trace('prc_federal_balances - L_fit_ee_subj_nwhable: ' || L_fit_ee_subj_nwhable);
2448 L_fit_ee_subject := L_fit_ee_subj_whable + L_fit_ee_subj_nwhable;
2449 hr_utility.trace('prc_federal_balances - L_fit_ee_subject: ' || L_fit_ee_subject);
2450 END IF;
2451
2452 IF L_tax_type = 'FIT' OR L_tax_type IS NULL THEN
2453
2454 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2455 L_as_of_date, L_dimension, 'FIT', 'WITHHELD', 'EE',
2456 NULL, L_fit_ee_tax);
2457
2458 -- d)
2459 IF L_fit_ee_subj_whable < L_fit_ee_reduced_s_whable THEN
2460
2461 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
2462 L_balance_nm2 := L_dimension || ' FIT Reduced Subject Withholdable';
2463 L_main_mesg := '*** ' || L_dimension ||' FIT Subject Withholdable < ' || L_dimension ||
2464 ' FIT Reduced Subject '||'Withholdable ***';
2465
2466 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2467 IN_prc_tax_unit_id,
2468 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2469 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2470 L_balance_nm1,
2471 L_balance_nm2,
2472 L_fit_ee_subj_whable, L_fit_ee_reduced_s_whable,
2473 NULL, NULL, NULL,
2474 L_main_mesg,
2475 '00-000-0000',
2476 L_asg_action_id,
2477 IN_prc_assignment_id );
2478 END IF;
2479
2480
2481 -- c)
2482 IF L_fit_ee_subj_whable <= 0 AND L_fit_ee_tax > 0 THEN
2483
2484 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
2485 L_balance_nm2 := L_dimension || ' FIT Withheld';
2486 L_main_mesg := '*** ' || L_dimension ||' FIT Subject Withholdable <= 0 but ' ||
2487 L_dimension || ' FIT '||'Withheld > 0 ***';
2488
2489 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2490 IN_prc_tax_unit_id,
2491 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2492 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2493 L_balance_nm1,
2494 L_balance_nm2,
2495 L_fit_ee_subj_whable, L_fit_ee_tax,
2496 NULL, NULL, NULL,
2497 L_main_mesg,
2498 '00-000-0000',
2499 L_asg_action_id ,
2500 IN_prc_assignment_id );
2501 END IF;
2502
2503 -- b)
2504 IF L_fit_ee_reduced_s_whable <= 0 AND L_fit_ee_tax > 0 THEN
2505
2506 L_balance_nm1 := L_dimension || ' FIT Reduced Subject Withholdable';
2507 L_balance_nm2 := L_dimension || ' FIT Withheld';
2508 L_main_mesg := '*** ' || L_dimension || ' FIT Reduced Subject Withholdable <= 0 but '||
2509 L_dimension || ' FIT '||'Withheld > 0 ***';
2510
2511 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2512 IN_prc_tax_unit_id,
2513 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2514 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2515 L_balance_nm1,
2516 L_balance_nm2,
2517 L_fit_ee_reduced_s_whable, L_fit_ee_tax,
2518 NULL, NULL, NULL,
2519 L_main_mesg,
2520 '00-000-0000',
2521 L_asg_action_id ,
2522 IN_prc_assignment_id );
2523 END IF;
2524
2525 -- a)
2526 IF L_fit_ee_gross_earnings < L_fit_ee_reduced_s_whable THEN
2527
2528 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2529 L_balance_nm2 := L_dimension || ' FIT Reduced Subject Withholdable';
2530 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < ' || L_dimension ||
2531 ' FIT Reduced Subject '||'Withholdable ***';
2532
2533 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2534 IN_prc_tax_unit_id,
2535 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2536 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2537 L_balance_nm1,
2538 L_balance_nm2,
2539 L_fit_ee_gross_earnings, L_fit_ee_reduced_s_whable,
2540 NULL, NULL, NULL,
2541 L_main_mesg,
2542 '00-000-0000',
2543 L_asg_action_id ,
2544 IN_prc_assignment_id );
2545 END IF;
2546
2547 --8754952 BEGIN
2548 IF L_fit_ee_reduced_s_whable < 0 THEN
2549
2550 L_balance_nm1 := L_dimension || ' FIT Reduced Subject Withholdable';
2551 L_balance_nm2 := NULL;
2552 L_main_mesg := '*** ' || L_dimension || ' FIT Reduced Subject Withholdable < 0' ;
2553
2554 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2555 IN_prc_tax_unit_id,
2556 IN_prc_organization_id, IN_prc_location_id,
2557 IN_pact_id,IN_chunk_no, IN_prc_person_id,
2558 IN_prc_assignment_number,
2559 L_balance_nm1,
2560 L_balance_nm2,
2561 L_fit_ee_reduced_s_whable,
2562 NULL,
2563 NULL, NULL, NULL,
2564 L_main_mesg,
2565 '00-000-0000',
2566 L_asg_action_id ,
2567 IN_prc_assignment_id );
2568 END IF;
2569 --8754952 END
2570
2571 END IF; -- end if 'FIT'
2572
2573 -- Pull all federal level applicable FUTA balances
2574 IF L_tax_type = 'FUTA' OR L_tax_type IS NULL THEN
2575
2576 -- all applicable balances will be pulled beginning with Federal
2577
2578 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2579 L_as_of_date, L_dimension, 'FUTA', 'TAXABLE', 'ER', NULL, L_futa_bal);
2580
2581 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2582 L_as_of_date, L_dimension, 'FUTA', 'LIABILITY', 'ER', NULL, L_futa_tax);
2583
2584
2585 /***************************** 3005756 START *********************************/
2586
2587 -- Value is fetched into the global variable in action_creation
2588
2589 L_futa_override_rt := G_futa_override_rt ;
2590
2591 /************************************** 3005756 END ******************************/
2592
2593 /*IF L_futa_override_rt = 0 THEN
2594 BEGIN
2595 OPEN c_get_sui_state_code(L_business_id, IN_prc_assignment_id,
2596 L_start_date, L_as_of_date);
2597 FETCH c_get_sui_state_code INTO L_sui_state_code;
2598 CLOSE c_get_sui_state_code;
2599 EXCEPTION
2600 WHEN OTHERS THEN
2601 L_sui_state_code := '00';
2602 END;
2603
2604
2605
2606
2607 IF L_sui_state_code <> '00' THEN */
2608 -- find for futa tax credit only if state found
2609
2610 /**************************3005756 START ************************************/
2611
2612 /* L_futa_credit_rt := fnc_get_futa_credit_rate( IN_prc_organization_id, L_sui_state_code );*/
2613
2614 /*********************** 3005656 END ******************************************/
2615 /*
2616 ELSE
2617 L_futa_credit_rt := 0;
2618 END IF;*/
2619
2620 /* BUG 12742758
2621 The following code will directly get the FUTA Rate from Federal JIT Table
2622 in case override credit limit is not specified.
2623 Otherwise we subtract the Credit Rate from Gross Rate to
2624 get the Net FUTA Rate
2625 */
2626
2627 IF L_futa_override_rt = 0 THEN
2628
2629 L_calculated := get_calculated_fed_limit_tax('FUTA',L_asg_action_id,IN_prc_tax_unit_id,'ER',L_as_of_date,L_dimension);
2630
2631 ELSE
2632
2633 L_calc_rate := L_futa_override_rt;
2634
2635 IF L_dimension = 'QTD' THEN
2636 L_calculated := L_futa_bal * L_calc_rate;
2637 ELSE
2638 L_calculated := ROUND(L_futa_bal * L_calc_rate,2);
2639 END IF;
2640
2641 END IF;
2642
2643 /*
2644 IF (L_futa_credit_rt <> -1) then
2645 L_calc_rate := (l_gfuta_rt - TO_NUMBER(L_futa_credit_rt))/100;
2646 END IF;
2647
2648 ELSE
2649 L_calc_rate := L_futa_override_rt;
2650 END IF;
2651 */
2652 /*
2653 IF L_dimension = 'QTD' THEN
2654 L_calculated := L_futa_bal * L_calc_rate;
2655 ELSE
2656 L_calculated := ROUND(L_futa_bal * L_calc_rate,2);
2657 END IF;
2658 */
2659 IF ABS(L_futa_tax - L_calculated) > 0.1 THEN
2660
2661 l_futa_change_count := 0;
2662
2663 IF L_futa_override_rt = 0 THEN
2664
2665 OPEN c_get_futa_rate(L_as_of_date);
2666 FETCH c_get_futa_rate INTO L_calc_rate;
2667 CLOSE c_get_futa_rate;
2668
2669 OPEN c_futa_count_changes(L_as_of_date,L_dimension);
2670 FETCH c_futa_count_changes INTO l_futa_change_count;
2671 CLOSE c_futa_count_changes;
2672
2673 END IF;
2674
2675 L_difference := L_futa_tax - L_calculated; --Bug 3115988
2676 L_balance_nm1 := L_dimension || ' FUTA Taxable';
2677 L_balance_nm2 := NULL;
2678
2679 IF l_futa_change_count > 1 THEN
2680
2681 L_main_mesg := '*** FUTA ER Liability does not = Calculated FUTA ER Liability ***';
2682
2683 ELSE
2684
2685 L_main_mesg := '*** FUTA ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of FUTA ER Taxable Balance ***';
2686
2687 END IF;
2688
2689 -- significant different found, write to tmp file for report
2690 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2691 IN_prc_tax_unit_id,
2692 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2693 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2694 L_balance_nm1,
2695 L_balance_nm2,
2696 L_futa_bal, L_futa_tax,
2697 L_calculated, L_difference, NULL,
2698 L_main_mesg,
2699 '00-000-0000',
2700 L_asg_action_id,
2701 IN_prc_assignment_id );
2702 END IF;
2703
2704
2705 -- e)
2706 IF L_fit_ee_gross_earnings < L_futa_bal THEN
2707
2708 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2709 L_balance_nm2 := L_dimension || ' FUTA Taxable';
2710 L_main_mesg := '*** '|| L_dimension || ' FIT Gross Earnings < ' || L_dimension || ' FUTA Taxable ***';
2711
2712 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2713 IN_prc_tax_unit_id,
2714 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2715 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2716 L_balance_nm1,
2717 L_balance_nm2,
2718 L_fit_ee_gross_earnings, L_futa_bal,
2719 NULL, NULL, NULL,
2720 L_main_mesg,
2721 '00-000-0000',
2722 L_asg_action_id,
2723 IN_prc_assignment_id );
2724 END IF;
2725
2726
2727
2728 END IF; -- end if 'FUTA'
2729
2730
2731 -- Pull all federal level applicable Medicare balances
2732 -- tmehra added the L_medi_exempt condition
2733 IF (L_tax_type = 'Medicare' OR L_tax_type IS NULL) AND L_medi_exempt = 'N' THEN
2734 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2735 L_as_of_date, L_dimension, 'MEDICARE', 'WITHHELD', 'EE',
2736 NULL, L_medi_ee_tax);
2737
2738 /* Added for Bug#9796821 - Starts */
2739 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2740 L_as_of_date, 'YTD', 'MEDICARE', 'TAXABLE', 'EE',
2741 NULL, l_medi_ee_bal_ytd);
2742
2743 IF TO_CHAR(L_as_of_date, 'YYYY') >= '2013' THEN
2744
2745 BEGIN /* Get new Medicare Limit and Rate, Subtract 0.01 from Limit as the actual value is the starting value of limit */
2746 SELECT TO_NUMBER(fed_attribute1) - 0.01, /* Bug#15852506 */
2747 fed_attribute2
2748 INTO L_medi_ee_lmt1, L_medi_ee_rate1
2749 FROM pay_us_federal_tax_info_f
2750 WHERE fed_information_category = '401K LIMITS'
2751 AND L_as_of_date BETWEEN effective_start_date AND effective_end_date;
2752 EXCEPTION
2753 WHEN OTHERS THEN
2754 raise_application_error(-20010, SQLCODE || ' - ' ||
2755 'Error while getting Medicare Limit - ' || SQLERRM);
2756 END;
2757
2758 BEGIN /* Get Defined Balance ID for Medicare EE Taxable Over Limit */
2759 SELECT pdb.defined_balance_id
2760 INTO l_defined_balance_id
2761 FROM pay_balance_types pbt,
2762 pay_balance_dimensions pbd,
2763 pay_defined_balances pdb
2764 WHERE pbt.balance_type_id = pdb.balance_type_id
2765 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2766 AND pbt.legislation_code = 'US'
2767 AND pbt.business_group_id IS NULL
2768 AND pbd.legislation_code = 'US'
2769 AND pbd.business_group_id IS NULL
2770 AND pdb.legislation_code = 'US'
2771 AND pdb.business_group_id IS NULL
2772 AND pbt.balance_name = 'Medicare EE Taxable Over Limit'
2773 AND pbd.database_item_suffix = '_PER_GRE_' || L_dimension;
2774 EXCEPTION
2775 WHEN OTHERS THEN
2776 raise_application_error(-20011, SQLCODE || ' - ' ||
2777 'Error while getting Defined Balance ID - ' || SQLERRM);
2778 END;
2779
2780 l_medi_ee_taxable_over_lmt :=
2781 pay_balance_pkg.get_value(l_defined_balance_id, /* Defined Balance ID */
2782 L_asg_action_id, /* Assignment Action ID */
2783 IN_prc_tax_unit_id, /* Tax Unit ID */
2784 NULL, /* Jurisdiction Code */
2785 NULL, /* Source ID */
2786 NULL, /* Tax Group */
2787 NULL /* Date Earned */
2788 );
2789
2790 L_calculated := ROUND(L_medi_ee_bal * G_medi_ee_rate, 2)
2791 + ROUND(l_medi_ee_taxable_over_lmt * (l_medi_ee_rate1 - G_medi_ee_rate), 2);
2792
2793 ELSE
2794 L_calculated := ROUND(L_medi_ee_bal * G_medi_ee_rate,2);
2795 END IF;
2796 /* Added for Bug#9796821 Ends */
2797 -- L_calculated := ROUND(L_medi_ee_bal * G_medi_ee_rate,2); /* Commented for Bug#9796821 */
2798
2799 IF ABS(L_medi_ee_tax - L_calculated) > 0.1 THEN
2800
2801 L_difference := L_medi_ee_tax - L_calculated;
2802 -- significant different found, write to tmp file for report
2803
2804 L_balance_nm1 := L_dimension || ' MEDICARE EE Taxable';
2805 L_balance_nm2 := NULL;
2806 -- L_main_mesg := '*** Medicare Withheld does not = '||TO_CHAR(G_medi_ee_rate*100)||'% of Taxable Balance ***';
2807 L_main_mesg := '*** Actual Medicare Withheld does not = Calculated Medicare Withheld ***'; /* Bug#9796821 */
2808 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2809 IN_prc_tax_unit_id,
2810 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2811 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2812 L_balance_nm1,
2813 L_balance_nm2,
2814 L_medi_ee_bal,
2815 L_medi_ee_tax,
2816 L_calculated, L_difference, NULL,
2817 L_main_mesg,
2818 '00-000-0000',
2819 L_asg_action_id,
2820 IN_prc_assignment_id );
2821 END IF;
2822
2823 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2824 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'ER',
2825 NULL, L_medi_er_bal);
2826
2827 /********************* Bug 2963239 changes start : Set flag *******************************/
2828
2829 L_medi_er_bal_flg := 'T' ;
2830
2831 /******************** Bug 2963239 Changes End ******************************************/
2832
2833 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2834 L_as_of_date, L_dimension, 'MEDICARE', 'WITHHELD', 'ER',
2835 NULL, L_medi_er_tax);
2836
2837 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2838 L_as_of_date, L_dimension, 'MEDICARE', 'LIABILITY', 'ER',
2839 NULL, L_medi_er_liability);
2840
2841 L_calculated := ROUND(L_medi_er_bal * G_medi_er_rate,2);
2842
2843 IF ABS(L_medi_er_tax - L_calculated) > 0.1 THEN
2844
2845 L_difference := L_medi_er_tax - L_calculated;
2846 L_balance_nm1 := L_dimension || ' MEDICARE ER Taxable';
2847 L_balance_nm2 := NULL;
2848 L_main_mesg := '*** Medicare Withheld does not = '||TO_CHAR(G_medi_er_rate*100)||'% of Taxable Balance ***';
2849
2850 -- significant different found, write to tmp file for report
2851 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2852 IN_prc_tax_unit_id,
2853 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2854 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2855 L_balance_nm1,
2856 L_balance_nm2,
2857 L_medi_er_bal,
2858 L_medi_er_tax,
2859 L_calculated, L_difference, NULL,
2860 '*** Medicare Withheld does not = '||TO_CHAR(G_medi_er_rate*100)||'% of Taxable Balance ***',
2861 '00-000-0000',
2862 L_asg_action_id ,
2863 IN_prc_assignment_id );
2864 END IF;
2865
2866
2867 -- g)
2868 IF (TO_CHAR(L_as_of_date, 'YYYY') < '2013' OR L_medi_ee_bal_ytd <= L_medi_ee_lmt1) THEN /* Added for Bug#9796821 */
2869
2870 IF L_medi_ee_tax <> L_medi_er_liability THEN
2871
2872 L_balance_nm1 := L_dimension || ' Medicare EE Withheld';
2873 L_balance_nm2 := L_dimension || ' Medicare ER Liability';
2874 L_main_mesg := '*** ' || L_dimension || ' Medicare EE Withheld does not = ' ||L_dimension ||
2875 ' Medicare ER Liability ***';
2876
2877 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2878 IN_prc_tax_unit_id,
2879 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2880 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2881 L_balance_nm1,
2882 L_balance_nm2,
2883 L_medi_ee_tax, L_medi_er_liability,
2884 NULL, NULL, NULL,
2885 L_main_mesg,
2886 '00-000-0000',
2887 L_asg_action_id,
2888 IN_prc_assignment_id );
2889 END IF;
2890 END IF; /* Added for Bug#9796821 */
2891
2892 -- f)
2893 IF L_fit_ee_gross_earnings < L_medi_ee_bal THEN
2894
2895 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2896 L_balance_nm2 := L_dimension || ' Medicare EE Taxable';
2897 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < ' || L_dimension || ' Medicare EE Taxable ***';
2898
2899 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2900 IN_prc_tax_unit_id,
2901 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2902 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2903 L_balance_nm1,
2904 L_balance_nm2,
2905 L_fit_ee_gross_earnings, L_medi_ee_bal,
2906 NULL, NULL, NULL,
2907 L_main_mesg,
2908 '00-00-0000',
2909 L_asg_action_id,
2910 IN_prc_assignment_id );
2911 END IF;
2912
2913
2914 END IF; -- end if 'Medicare'
2915
2916
2917 -- Pull all federal level applicable Social Security balances
2918 IF L_tax_type = 'SS' OR L_tax_type IS NULL THEN
2919
2920 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2921 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'EE', NULL, L_ss_ee_bal);
2922
2923 /********************* Bug 2963239 changes start : Set flag *******************************/
2924
2925 L_ss_ee_bal_flg := 'T' ;
2926
2927 /******************** Bug 2963239 Changes End ******************************************/
2928
2929 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2930 L_as_of_date, L_dimension, 'SS', 'WITHHELD', 'EE', NULL, L_ss_ee_tax);
2931
2932 L_calculated := ROUND(L_ss_ee_bal * G_ss_ee_rate,2);
2933
2934 IF ABS(L_ss_ee_tax - L_calculated) > 0.1 THEN
2935
2936 L_difference := L_ss_ee_tax - L_calculated;
2937 L_balance_nm1 := L_dimension || ' SS EE Taxable';
2938 L_balance_nm2 := NULL;
2939 L_main_mesg := '*** SS Withheld does not = '||TO_CHAR(G_ss_ee_rate*100)||'% of Taxable Balance ***';
2940 -- significant different found, write to tmp file for report
2941 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2942 IN_prc_tax_unit_id,
2943 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2944 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2945 L_balance_nm1,
2946 L_balance_nm2,
2947 L_ss_ee_bal,L_ss_ee_tax,
2948 L_calculated, L_difference, NULL,
2949 L_main_mesg,
2950 '00-000-0000',
2951 L_asg_action_id,
2952 IN_prc_assignment_id );
2953 END IF;
2954
2955 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2956 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'ER', NULL, L_ss_er_bal);
2957
2958
2959 /********************* Bug 2963239 changes start : Set flag *******************************/
2960
2961 L_ss_er_bal_flg := 'T' ;
2962
2963 /******************** Bug 2963239 Changes End ******************************************/
2964
2965 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2966 L_as_of_date, L_dimension, 'SS', 'LIABILITY', 'ER',
2967 NULL, L_ss_er_liability);
2968
2969 L_calculated := ROUND(L_ss_er_bal * G_ss_er_rate,2);
2970
2971
2972 IF ABS(L_ss_er_liability - L_calculated) > 0.1 THEN
2973
2974 L_difference := L_ss_er_liability - L_calculated;
2975 -- significant different found, write to tmp file for report
2976 L_balance_nm1 := L_dimension || ' SS ER Taxable';
2977 L_balance_nm2 := NULL;
2978 L_main_mesg := '*** SS Withheld does not = '||TO_CHAR(G_ss_er_rate*100)||'% of Taxable Balance ***';
2979 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2980 IN_prc_tax_unit_id,
2981 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2982 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2983 L_balance_nm1,
2984 L_balance_nm2,
2985 L_ss_er_bal, L_ss_er_liability,
2986 L_calculated, L_difference, NULL,
2987 L_main_mesg,
2988 '00-000-0000',
2989 L_asg_action_id,
2990 IN_prc_assignment_id );
2991 END IF;
2992
2993 -- i)
2994 /* BUG# 10631126 : commenting this part as SS EE and SS ER may not be same always */
2995 /* BUG# 13360466 : Added the Outer IF clause to check for the SS EE and SS ER rates
2996 Uncommented the check for ss_ee_tax and ss_er_liability (inner IF clause) */
2997
2998 IF G_ss_ee_rate = G_ss_er_rate THEN
2999 IF L_ss_ee_tax <> L_ss_er_liability THEN
3000
3001 L_balance_nm1 := L_dimension || ' SS EE Withheld';
3002 L_balance_nm2 := L_dimension || ' SS ER Liability';
3003 L_main_mesg := '*** ' || L_dimension || ' SS EE Withheld does not = ' || L_dimension || ' SS ER Liability ***';
3004
3005 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3006 IN_prc_tax_unit_id,
3007 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3008 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
3009 L_balance_nm1,
3010 L_balance_nm2,
3011 L_ss_ee_tax, L_ss_er_liability,
3012 NULL, NULL, NULL,
3013 L_main_mesg,
3014 '00-000-0000',
3015 L_asg_action_id,
3016 IN_prc_assignment_id );
3017 END IF;
3018 END IF;
3019
3020 -- h)
3021 IF L_fit_ee_gross_earnings < L_ss_ee_bal THEN
3022
3023 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
3024 L_balance_nm2 := L_dimension || ' SS EE Taxable';
3025 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < ' || L_dimension || ' SS EE Taxable ***';
3026
3027 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3028 IN_prc_tax_unit_id,
3029 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3030 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
3031 L_balance_nm1,
3032 L_balance_nm2,
3033 L_fit_ee_gross_earnings, L_ss_ee_bal,
3034 NULL, NULL, NULL,
3035 L_main_mesg,
3036 '00-000-0000',
3037 L_asg_action_id,
3038 IN_prc_assignment_id );
3039 END IF;
3040
3041
3042 END IF; -- end if 'SS'
3043
3044
3045 /********************* Bug 2963239 Changes start: Extra check **********************************/
3046
3047 IF L_tax_type = 'SS' or L_tax_type = 'Medicare' or L_tax_type IS NULL THEN
3048
3049 IF L_medi_ee_bal_flg = 'F' THEN
3050 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3051 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'EE',
3052 NULL, L_medi_ee_bal);
3053 END IF;
3054 IF L_medi_er_bal_flg = 'F' THEN
3055 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3056 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'ER',
3057 NULL, L_medi_er_bal);
3058 END IF;
3059 IF L_ss_ee_bal_flg = 'F' THEN
3060 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3061 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'EE', NULL, L_ss_ee_bal);
3062 END IF;
3063 IF L_ss_er_bal_flg = 'F' THEN
3064 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3065 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'ER', NULL, L_ss_er_bal);
3066 END IF;
3067
3068
3069 IF L_ss_ee_bal > L_medi_ee_bal THEN
3070
3071 L_balance_nm1 := L_dimension || ' MEDICARE EE Taxable';
3072 L_balance_nm2 := L_dimension || ' SS EE Taxable';
3073 L_main_mesg := '*** ' || L_dimension || ' SS EE Taxable > ' || L_dimension || ' MEDICARE EE Taxable ***' ;
3074
3075 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3076 IN_prc_tax_unit_id,
3077 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3078 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
3079 L_balance_nm1,
3080 L_balance_nm2,
3081 L_medi_ee_bal, L_ss_ee_bal,
3082 NULL, NULL, NULL,
3083 L_main_mesg,
3084 '00-000-0000',
3085 L_asg_action_id,
3086 IN_prc_assignment_id );
3087 END IF;
3088
3089 IF L_ss_er_bal > L_medi_er_bal THEN
3090
3091 L_balance_nm1 := L_dimension || ' MEDICARE ER Taxable';
3092 L_balance_nm2 := L_dimension || ' SS ER Taxable';
3093 L_main_mesg := '*** ' || L_dimension || ' SS ER Taxable > ' || L_dimension || ' MEDICARE ER Taxable ***';
3094
3095 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3096 IN_prc_tax_unit_id,
3097 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3098 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
3099 L_balance_nm1,
3100 L_balance_nm2,
3101 L_medi_er_bal, L_ss_er_bal,
3102 NULL, NULL, NULL,
3103 L_main_mesg,
3104 '00-000-0000',
3105 L_asg_action_id,
3106 IN_prc_assignment_id );
3107 END IF;
3108
3109 END IF; -- ss or medicare
3110
3111 END prc_federal_balances ;
3112
3113
3114
3115 PROCEDURE prc_state_balances ( curr_jurisdiction_code IN VARCHAR2
3116 , curr_state_code IN VARCHAR2
3117 , curr_state_abbrev IN VARCHAR2)
3118 IS
3119
3120 /*Changes for Bug#13394260*/
3121
3122 CURSOR get_use_default_sdi_ee_rate IS
3123 SELECT NVL(sta_information9,'N')
3124 FROM pay_us_emp_state_tax_rules_f
3125 WHERE assignment_id = IN_prc_assignment_id
3126 AND state_code = curr_state_code
3127 AND L_as_of_date BETWEEN effective_start_date
3128 AND effective_end_date
3129 ORDER BY effective_end_date desc;
3130
3131 L_use_default_sdi_ee VARCHAR2(2) := 'N';
3132
3133 /*End of Changes for Bug#13394260*/
3134
3135 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3136 BEGIN
3137 hr_utility.trace('prc_state_balances - curr_jurisdiction_code: ' || curr_jurisdiction_code);
3138 hr_utility.trace('prc_state_balances - curr_state_code: ' || curr_state_code);
3139 hr_utility.trace('prc_state_balances - curr_state_abbrev: ' || curr_state_abbrev);
3140 hr_utility.trace('prc_state_balances - L_tax_type: ' || L_tax_type);
3141 IF L_tax_type = 'SIT' OR L_tax_type IS NULL THEN
3142 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3143 L_as_of_date, L_dimension, 'SIT', 'WITHHELD', 'EE',
3144 curr_jurisdiction_code,
3145 L_sit_ee_withheld);
3146 hr_utility.trace('prc_state_balances - L_sit_ee_withheld: ' || L_sit_ee_withheld);
3147 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3148 L_as_of_date, L_dimension, 'SIT', 'SUBJ_WHABLE', 'EE',
3149 curr_jurisdiction_code,
3150 L_sit_ee_subj_whable);
3151 hr_utility.trace('prc_state_balances - L_sit_ee_subj_whable: ' || L_sit_ee_subj_whable);
3152 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3153 L_as_of_date, L_dimension, 'SIT', 'SUBJ_NWHABLE', 'EE',
3154 curr_jurisdiction_code,
3155 L_sit_ee_subj_nwhable);
3156 hr_utility.trace('prc_state_balances - L_sit_ee_subj_nwhable: ' || L_sit_ee_subj_nwhable);
3157 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3158 L_as_of_date,L_dimension, 'SIT', 'PRE_TAX_REDNS', 'EE',
3159 curr_jurisdiction_code,
3160 L_sit_ee_pretax_redns);
3161 hr_utility.trace('prc_state_balances - L_sit_ee_pretax_redns: ' || L_sit_ee_pretax_redns);
3162 L_sit_ee_subject := L_sit_ee_subj_whable + L_sit_ee_subj_nwhable;
3163 L_sit_ee_reduced_s_whable := L_sit_ee_subj_whable - L_sit_ee_pretax_redns;
3164 hr_utility.trace('prc_state_balances - L_sit_ee_subject: ' || L_sit_ee_subject);
3165 hr_utility.trace('prc_state_balances - L_sit_ee_reduced_s_whable: ' || L_sit_ee_reduced_s_whable);
3166 -- j)
3167 IF L_sit_ee_subj_whable <= 0 AND L_sit_ee_withheld > 0 THEN
3168
3169 L_balance_nm1 := L_dimension || ' SIT Subject Withholdable';
3170 L_balance_nm2 := L_dimension || ' SIT Withheld';
3171 L_main_mesg := '*** ' || L_dimension || ' SIT Subject Withholdable <= 0 and ' || L_dimension ||
3172 ' SIT Withheld > 0 ***';
3173
3174 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3175 IN_prc_tax_unit_id,
3176 IN_prc_organization_id, IN_prc_location_id,
3177 IN_pact_id,
3178 IN_chunk_no, IN_prc_person_id,
3179 IN_prc_assignment_number,
3180 L_balance_nm1,
3181 L_balance_nm2,
3182 L_sit_ee_subj_whable, L_sit_ee_withheld,
3183 NULL, NULL, curr_state_abbrev,
3184 L_main_mesg,
3185 curr_state_code||'-000-0000',
3186 L_asg_action_id ,
3187 IN_prc_assignment_id );
3188 END IF;
3189
3190
3191 -- l)
3192 IF L_sit_ee_subj_whable < L_sit_ee_reduced_s_whable THEN
3193
3194 L_balance_nm1 := L_dimension || ' SIT Subject Withholdable';
3195 L_balance_nm2 := L_dimension || ' SIT Reduced Subject Withholdable';
3196 L_main_mesg := '*** ' || L_dimension || ' SIT Subject Withholdable < ' || L_dimension ||
3197 ' SIT Reduced Subject Withholdable ***';
3198
3199 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3200 IN_prc_tax_unit_id,
3201 IN_prc_organization_id, IN_prc_location_id,
3202 IN_pact_id,
3203 IN_chunk_no, IN_prc_person_id,
3204 IN_prc_assignment_number,
3205 L_balance_nm1,
3206 L_balance_nm2,
3207 L_sit_ee_subj_whable, L_sit_ee_reduced_s_whable,
3208 NULL, NULL, curr_state_abbrev,
3209 L_main_mesg,
3210 curr_state_code||'-000-0000',
3211 L_asg_action_id,
3212 IN_prc_assignment_id );
3213 END IF;
3214
3215 hr_utility.trace('prc_state_balances - L_fit_ee_subject: ' || L_fit_ee_subject);
3216 hr_utility.trace('prc_state_balances - L_sit_ee_subject: ' || L_sit_ee_subject);
3217 -- o)
3218 IF L_fit_ee_subject < L_sit_ee_subject THEN
3219 hr_utility.trace('prc_state_balances - If condition FIT < SIT');
3220 L_balance_nm1 := L_dimension || ' FIT Subject';
3221 L_balance_nm2 := L_dimension || ' SIT Subject';
3222 L_main_mesg := '*** ' || L_dimension || ' FIT Subject < ' || L_dimension || ' SIT Subject ***';
3223
3224 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3225 IN_prc_tax_unit_id,
3226 IN_prc_organization_id, IN_prc_location_id,
3227 IN_pact_id,
3228 IN_chunk_no, IN_prc_person_id,
3229 IN_prc_assignment_number,
3230 L_balance_nm1,
3231 L_balance_nm2,
3232 L_fit_ee_subject, L_sit_ee_subject,
3233 NULL, NULL, curr_state_abbrev,
3234 L_main_mesg,
3235 curr_state_code||'-000-0000',
3236 L_asg_action_id ,
3237 IN_prc_assignment_id );
3238 END IF;
3239
3240
3241 -- k)
3242 IF L_fit_ee_subj_whable <= 0 AND L_sit_ee_withheld > 0 THEN
3243
3244 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3245 L_balance_nm2 := L_dimension || ' SIT Withheld';
3246 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3247 ' SIT Withheld > 0 ***';
3248 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3249 IN_prc_tax_unit_id,
3250 IN_prc_organization_id, IN_prc_location_id,
3251 IN_pact_id,
3252 IN_chunk_no, IN_prc_person_id,
3253 IN_prc_assignment_number,
3254 L_balance_nm1,
3255 L_balance_nm2,
3256 L_fit_ee_subj_whable, L_sit_ee_withheld,
3257 NULL, NULL, curr_state_abbrev,
3258 L_main_mesg,
3259 curr_state_code||'-000-0000',
3260 L_asg_action_id ,
3261 IN_prc_assignment_id );
3262 END IF;
3263
3264
3265 -- p)
3266 IF L_sit_ee_withheld > 0 AND NOT
3267 fnc_sit_exists(curr_state_code, L_as_of_date)
3268 THEN
3269
3270 L_balance_nm1 := L_dimension || ' SIT Withheld';
3271 L_balance_nm2 := ' ';
3272 L_main_mesg := '*** ' || L_dimension || ' SIT Withheld > 0 when state has no SIT '||'withholding rule ***';
3273
3274 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3275 IN_prc_tax_unit_id,
3276 IN_prc_organization_id, IN_prc_location_id,
3277 IN_pact_id,
3278 IN_chunk_no, IN_prc_person_id,
3279 IN_prc_assignment_number,
3280 L_balance_nm1,
3281 L_balance_nm2,
3282 L_sit_ee_withheld, NULL,
3283 NULL, NULL, curr_state_abbrev,
3284 L_main_mesg,
3285 curr_state_code||'-000-0000',
3286 L_asg_action_id ,
3287 IN_prc_assignment_id );
3288 END IF;
3289
3290
3291 END IF; -- end if 'SIT'
3292
3293
3294 -- don't bother checking if state is Hawaii or New York
3295 IF (L_tax_type = 'SDI' OR L_tax_type IS NULL) AND
3296 curr_state_code NOT IN ('12','33')
3297 THEN
3298
3299 /*Changes for Bug#13394260*/
3300
3301 IF curr_state_code = '05' THEN
3302
3303 OPEN get_use_default_sdi_ee_rate;
3304 FETCH get_use_default_sdi_ee_rate INTO L_use_default_sdi_ee;
3305 CLOSE get_use_default_sdi_ee_rate;
3306
3307 END IF;
3308
3309 /*End of changes for Bug#13394260*/
3310 /*Added check on L_use_default_sdi_ee below for Bug#13394260*/
3311 -- first get the rate, if user specified rate exists then use it, otherwise ...
3312 IF L_usr_SDI_EE_rate IS NOT NULL AND L_use_default_sdi_ee <> 'Y' THEN
3313 L_calc_rate := L_usr_SDI_EE_rate/100;
3314 ELSE
3315 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code,
3316 L_start_date, L_as_of_date,
3317 'SDI', 'EE','FULL');
3318 END IF;
3319
3320 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3321 L_as_of_date, L_dimension, 'SDI', 'SUBJ_WHABLE', 'EE',
3322 curr_jurisdiction_code,
3323 L_sdi_ee_subj_whable);
3324
3325 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3326 L_as_of_date, L_dimension, 'SDI', 'SUBJ_NWHABLE', 'EE',
3327 curr_jurisdiction_code,
3328 L_sdi_ee_subj_nwhable);
3329
3330 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3331 L_as_of_date, L_dimension, 'SDI', 'TAXABLE', 'EE',
3332 curr_jurisdiction_code,
3333 L_sdi_ee_bal);
3334
3335 L_sum_sdi_ee_bal := L_sum_sdi_ee_bal + L_sdi_ee_bal;
3336
3337
3338 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3339 L_as_of_date, L_dimension, 'SDI', 'WITHHELD', 'EE',
3340 curr_jurisdiction_code,
3341 L_sdi_ee_tax);
3342
3343 L_calculated := ROUND(L_sdi_ee_bal * L_calc_rate,2);
3344
3345
3346 IF ABS(L_sdi_ee_tax - L_calculated) > 0.1 THEN
3347
3348 L_difference := L_sdi_ee_tax - L_calculated;
3349 -- significant different found, write to tmp file for report
3350 L_balance_nm1 := L_dimension || ' SDI EE Taxable';
3351 L_balance_nm2 := NULL;
3352
3353 IF L_use_default_sdi_ee = 'Y' AND L_usr_SDI_EE_rate IS NOT NULL THEN
3354 L_main_mesg := '** SDI EE Liability does not = '||TO_CHAR(L_calc_rate*100)||
3355 '% of SDI EE Taxable Balance (Using Default SDI EE Rate)**';
3356 ELSE
3357 L_main_mesg := '*** SDI EE Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SDI EE Taxable Balance ***';
3358 END IF;
3359 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3360 IN_prc_tax_unit_id,
3361 IN_prc_organization_id, IN_prc_location_id,
3362 IN_pact_id,
3363 IN_chunk_no, IN_prc_person_id,
3364 IN_prc_assignment_number,
3365 L_balance_nm1,
3366 L_balance_nm2,
3367 L_sdi_ee_bal, L_sdi_ee_tax,
3368 L_calculated, L_difference, curr_state_abbrev,
3369 L_main_mesg,
3370 curr_state_code||'-000-0000',
3371 L_asg_action_id,
3372 IN_prc_assignment_id );
3373 END IF;
3374
3375
3376 L_calc_rate := NULL;
3377 IF L_usr_SDI_ER_rate IS NOT NULL THEN
3378 L_calc_rate := L_usr_SDI_ER_rate/100;
3379 ELSE
3380
3381 /****************************** 3005756 START ****************************************************/
3382
3383 L_calc_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'SDI' );
3384
3385 /************************************* 3005756 END *******************************************/
3386
3387 IF L_calc_rate IS NULL THEN
3388 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
3389 L_as_of_date, 'SDI', 'ER','FULL');
3390 END IF;
3391 END IF;
3392
3393 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3394 L_as_of_date, L_dimension, 'SDI', 'TAXABLE', 'ER',
3395 curr_jurisdiction_code,
3396 L_sdi_er_bal);
3397
3398 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3399 L_as_of_date, L_dimension, 'SDI', 'LIABILITY', 'ER',
3400 curr_jurisdiction_code,
3401 L_sdi_er_tax);
3402
3403
3404 L_calculated := ROUND(L_sdi_er_bal * L_calc_rate,2);
3405
3406
3407 IF ABS(L_sdi_er_tax - L_calculated) > 0.1 THEN
3408
3409 L_difference := L_sdi_er_tax - L_calculated;
3410 -- significant different found, write to tmp file for report
3411 L_balance_nm1 := L_dimension || ' SDI ER Taxable';
3412 L_balance_nm2 := NULL;
3413 L_main_mesg := '*** SDI ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SDI ER Taxable Balance ***';
3414
3415 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3416 IN_prc_tax_unit_id,
3417 IN_prc_organization_id, IN_prc_location_id,
3418 IN_pact_id,
3419 IN_chunk_no, IN_prc_person_id,
3420 IN_prc_assignment_number,
3421 L_balance_nm1,
3422 L_balance_nm2,
3423 L_sdi_er_bal, L_sdi_er_tax,
3424 L_calculated, L_difference, curr_state_abbrev,
3425 L_main_mesg,
3426 curr_state_code||'-000-0000',
3427 L_asg_action_id,
3428 IN_prc_assignment_id );
3429 END IF;
3430
3431
3432 -- u)
3433 IF L_sdi_ee_subj_whable <= 0 AND L_sdi_ee_tax > 0 THEN
3434
3435 L_balance_nm1 := L_dimension || ' SDI EE Subject Withholdable';
3436 L_balance_nm2 := L_dimension || ' SDI EE Withheld';
3437 L_main_mesg := '*** ' || L_dimension || ' SDI EE Subject Withholdable <= 0 and ' || L_dimension ||
3438 ' SDI EE Withheld > 0 ***';
3439
3440 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3441 IN_prc_tax_unit_id,
3442 IN_prc_organization_id, IN_prc_location_id,
3443 IN_pact_id,
3444 IN_chunk_no, IN_prc_person_id,
3445 IN_prc_assignment_number,
3446 L_balance_nm1,
3447 L_balance_nm2,
3448 L_sdi_ee_subj_whable, L_sdi_ee_tax,
3449 NULL, NULL, curr_state_abbrev,
3450 L_main_mesg,
3451 curr_state_code||'-000-0000',
3452 L_asg_action_id,
3453 IN_prc_assignment_id );
3454 END IF;
3455
3456
3457 -- v)
3458 IF L_fit_ee_subj_whable <= 0 AND L_sdi_ee_tax > 0 THEN
3459
3460 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3461 L_balance_nm2 := 'YTD SDI EE Withheld';
3462 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3463 ' SDI EE Withheld > 0 ***';
3464
3465 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3466 IN_prc_tax_unit_id,
3467 IN_prc_organization_id, IN_prc_location_id,
3468 IN_pact_id,
3469 IN_chunk_no, IN_prc_person_id,
3470 IN_prc_assignment_number,
3471 L_balance_nm1,
3472 L_balance_nm2,
3473 L_fit_ee_subj_whable, L_sdi_ee_tax,
3474 NULL, NULL, curr_state_abbrev,
3475 L_main_mesg,
3476 curr_state_code||'-000-0000',
3477 L_asg_action_id,
3478 IN_prc_assignment_id );
3479 END IF;
3480
3481
3482 -- v)
3483 IF L_fit_ee_subj_whable <= 0 AND L_sdi_er_tax > 0 THEN
3484
3485 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3486 L_balance_nm2 := L_dimension || ' SDI ER Liability';
3487 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3488 ' SDI ER Withheld > 0 ***';
3489
3490 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3491 IN_prc_tax_unit_id,
3492 IN_prc_organization_id, IN_prc_location_id,
3493 IN_pact_id,
3494 IN_chunk_no, IN_prc_person_id,
3495 IN_prc_assignment_number,
3496 L_balance_nm1,
3497 L_balance_nm2,
3498 L_fit_ee_subj_whable, L_sdi_er_tax,
3499 NULL, NULL, curr_state_abbrev,
3500 L_main_mesg,
3501 curr_state_code||'-000-0000',
3502 L_asg_action_id,
3503 IN_prc_assignment_id );
3504 END IF;
3505
3506
3507 -- t)
3508 IF L_fit_ee_gross_earnings < L_sum_sdi_ee_bal THEN
3509
3510 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
3511 L_balance_nm2 := 'TOTAL ' || L_dimension || ' SDI EE Taxable';
3512 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < TOTAL ' || L_dimension ||
3513 ' SDI EE Taxable ***';
3514
3515 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3516 IN_prc_tax_unit_id,
3517 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3518 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
3519 L_balance_nm1,
3520 L_balance_nm2,
3521 L_fit_ee_gross_earnings, L_sum_sdi_ee_bal,
3522 NULL, NULL, NULL,
3523 L_main_mesg,
3524 '00-000-0000',
3525 L_asg_action_id,
3526 IN_prc_assignment_id );
3527 END IF;
3528
3529
3530 END IF; -- end if 'SDI'
3531
3532
3533
3534 IF L_tax_type = 'SUI' OR L_tax_type IS NULL THEN
3535 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
3536 L_as_of_date, 'SUI', 'EE','FULL');
3537
3538 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3539 L_as_of_date, L_dimension, 'SUI', 'TAXABLE', 'EE',
3540 curr_jurisdiction_code,
3541 L_sui_ee_bal);
3542
3543 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3544 L_as_of_date, L_dimension, 'SUI', 'WITHHELD', 'EE',
3545 curr_jurisdiction_code,
3546 L_sui_ee_tax);
3547
3548
3549 IF L_dimension = 'QTD' THEN
3550
3551 /******************* QTD **************************************/
3552
3553 L_calculated := ROUND(L_sui_ee_bal * L_calc_rate,2);
3554
3555 IF ABS(L_sui_ee_tax - L_calculated) > 0.1 THEN
3556
3557 L_difference := L_sui_ee_tax - L_calculated;
3558 -- significant different found, write to tmp file for report
3559 L_balance_nm1 := 'QTD SUI EE Taxable';
3560 L_balance_nm2 := NULL;
3561 L_main_mesg := '*** SUI EE Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI EE Taxable Balance ***';
3562
3563 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3564 IN_prc_tax_unit_id,
3565 IN_prc_organization_id, IN_prc_location_id,
3566 IN_pact_id,
3567 IN_chunk_no, IN_prc_person_id,
3568 IN_prc_assignment_number,
3569 L_balance_nm1,
3570 L_balance_nm2,
3571 L_sui_ee_bal, L_sui_ee_tax,
3572 L_calculated, L_difference, curr_state_abbrev,
3573 L_main_mesg,
3574 curr_state_code||'-000-0000',
3575 L_asg_action_id ,
3576 IN_prc_assignment_id );
3577 END IF;
3578
3579
3580
3581 -- Now do the ER SUI portion
3582 -- First check if SUI override rate is entered by user
3583 L_calc_rate := NULL;
3584
3585 /********************************** 3005756 START *******************************************/
3586
3587 L_calc_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id, curr_state_code , 'C' );
3588 L_dummy_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id, curr_state_code , 'D' );
3589
3590 /********************************** 3005756 END **********************************************/
3591
3592
3593 IF L_calc_rate IS NULL THEN
3594 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
3595 L_as_of_date, 'SUI', 'ER','FULL');
3596 END IF;
3597
3598 /******************************* QTD *********************************************/
3599
3600 END IF; -- QTD
3601
3602 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3603 L_as_of_date, L_dimension, 'SUI', 'TAXABLE', 'ER',
3604 curr_jurisdiction_code,
3605 L_sui_er_bal);
3606
3607 L_sum_sui_er_bal := L_sum_sui_er_bal + L_sui_er_bal;
3608
3609
3610 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3611 L_as_of_date, L_dimension, 'SUI', 'LIABILITY', 'ER',
3612 curr_jurisdiction_code,
3613 L_sui_er_tax);
3614
3615 IF L_dimension = 'QTD' THEN
3616
3617 /************************** QTD ********************************/
3618 L_calculated := ROUND(L_sui_er_bal * L_calc_rate,2);
3619
3620 IF ABS(L_sui_er_tax - L_calculated) > 0.1 THEN
3621
3622 L_difference := L_sui_er_tax - L_calculated;
3623 -- significant different found, write to tmp file for report
3624 L_balance_nm1 := 'QTD SUI ER Taxable';
3625 L_balance_nm2 := NULL ;
3626 L_main_mesg := '*** SUI ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI ER Taxable Balance ***';
3627
3628 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3629 IN_prc_tax_unit_id,
3630 IN_prc_organization_id, IN_prc_location_id,
3631 IN_pact_id,
3632 IN_chunk_no, IN_prc_person_id,
3633 IN_prc_assignment_number,
3634 L_balance_nm1,
3635 L_balance_nm2,
3636 L_sui_er_bal, L_sui_er_tax,
3637 L_calculated, L_difference, curr_state_abbrev,
3638 L_main_mesg,
3639 curr_state_code||'-000-0000',
3640 L_asg_action_id,
3641 IN_prc_assignment_id );
3642 END IF;
3643
3644 /************************ QTD ***********************************/
3645
3646 END IF; -- QTD
3647
3648 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3649 L_as_of_date, L_dimension, 'SUI', 'SUBJ_WHABLE', 'EE',
3650 curr_jurisdiction_code,
3651 L_sui_ee_subj_whable);
3652
3653
3654 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3655 L_as_of_date, L_dimension, 'SUI', 'SUBJ_WHABLE', 'ER',
3656 curr_jurisdiction_code,
3657 L_sui_er_subj_whable);
3658
3659 IF L_dimension = 'YTD' THEN
3660
3661 /****************** YTD *************************************/
3662
3663 -- only do this if state is New Hampshire, New Jersey, Tennessee, Vermont and
3664 -- dimension is YTD
3665 IF curr_state_code IN (30, 31, 43, 46) AND
3666 L_as_of_date > L_first_half_date THEN
3667
3668 -- get the rates from jan 1 to end of june and for july 1 to as_of_date
3669 L_first_half_rate := fnc_get_tax_limit_rate(curr_state_code,
3670 L_start_date, L_first_half_date, 'SUI', 'EE','FIRST');
3671
3672 L_second_half_rate := fnc_get_tax_limit_rate(curr_state_code,
3673 L_first_half_date+1, L_as_of_date, 'SUI', 'EE','LAST');
3674
3675 -- get ee balance for first 6 months
3676 BEGIN
3677 select assignment_id into l_assignment_id
3678 from pay_assignment_actions where assignment_action_id = L_asg_action_id; /* BUG# 10350917 */
3679 L_sui_ee_bal_first := pay_us_tax_bals_pkg.us_tax_balance
3680 ('TAXABLE',
3681 'SUI',
3682 'EE',
3683 'YTD',
3684 'ASG',
3685 IN_prc_tax_unit_id,
3686 curr_jurisdiction_code,
3687 NULL,
3688 l_assignment_id, /* Bug 10350917 */
3689 L_first_half_date,
3690 NULL,
3691 TRUE);
3692 EXCEPTION
3693 WHEN NO_DATA_FOUND THEN
3694 L_sui_ee_bal_first := 0;
3695 WHEN OTHERS THEN
3696 RAISE;
3697 END;
3698 -- now combine first and second half to make complete ee balance
3699 L_calculated := ROUND((L_sui_ee_bal_first * L_first_half_rate)
3700 + ((L_sui_ee_bal-L_sui_ee_bal_first)
3701 *L_second_half_rate),2);
3702
3703 -- now check if ee difference is erroneous
3704 IF ABS(L_sui_ee_tax - L_calculated) > 0.1 THEN
3705
3706 L_difference := L_sui_ee_tax - L_calculated;
3707 -- significant different found, write to tmp file for report
3708 L_balance_nm1 := 'YTD SUI EE Taxable';
3709 L_balance_nm2 := NULL;
3710 L_main_mesg := '*** SUI EE Liability does not = 1st half year '||
3711 TO_CHAR(L_first_half_rate*100)||'%, 2nd half year '
3712 ||TO_CHAR(L_second_half_rate*100)||'% of SUI EE Taxable Balance ***';
3713
3714 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3715 IN_prc_tax_unit_id,
3716 IN_prc_organization_id,
3717 IN_prc_location_id, IN_pact_id,
3718 IN_chunk_no, IN_prc_person_id,
3719 IN_prc_assignment_number,
3720 L_balance_nm1,
3721 L_balance_nm2,
3722 L_sui_ee_bal, L_sui_ee_tax,
3723 L_calculated, L_difference, curr_state_abbrev,
3724 L_main_mesg,
3725 curr_state_code||'-000-0000',
3726 L_asg_action_id,
3727 IN_prc_assignment_id );
3728 END IF;
3729
3730 -- now do the ER portion
3731 -- First check if SUI override rate is entered by user
3732
3733 /************************************* 3005756 START ****************************************/
3734
3735 L_first_half_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'D' );
3736 L_second_half_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'C' );
3737
3738 /************************************* 3005756 END *******************************************/
3739
3740 IF L_first_half_rate IS NULL THEN
3741 L_first_half_rate := fnc_get_tax_limit_rate(curr_state_code,
3742 L_start_date, L_first_half_date, 'SUI', 'ER','FIRST');
3743 END IF;
3744
3745 IF L_second_half_rate IS NULL THEN
3746 L_second_half_rate := fnc_get_tax_limit_rate(curr_state_code,
3747 L_first_half_date+1, L_as_of_date, 'SUI', 'ER','LAST');
3748 END IF;
3749
3750 -- get er balance for first 6 months
3751 BEGIN
3752 select assignment_id into l_assignment_id
3753 from pay_assignment_actions where assignment_action_id = L_asg_action_id; /* Bug 10350917 */
3754 L_sui_er_bal_first := pay_us_tax_bals_pkg.us_tax_balance
3755 ('TAXABLE',
3756 'SUI',
3757 'ER',
3758 'YTD',
3759 'ASG',
3760 IN_prc_tax_unit_id,
3761 curr_jurisdiction_code,
3762 NULL,
3763 l_assignment_id, /* Bug 10350917 */
3764 L_first_half_date,
3765 NULL,
3766 TRUE);
3767 EXCEPTION
3768 WHEN NO_DATA_FOUND THEN
3769 L_sui_er_bal_first := 0;
3770 WHEN OTHERS THEN
3771 RAISE;
3772 END;
3773
3774 -- now combine first and second half to make complete er balance
3775 L_calculated := ROUND((L_sui_er_bal_first * L_first_half_rate)
3776 + ((L_sui_er_bal - L_sui_er_bal_first)
3777 *L_second_half_rate),2);
3778
3779 -- now check if ee difference is erroneous
3780 IF ABS(L_sui_er_tax - L_calculated) > 0.1 THEN
3781
3782 L_difference := L_sui_er_tax - L_calculated;
3783 -- significant different found, write to tmp file for report
3784 L_balance_nm1 := 'YTD SUI ER Taxable';
3785 L_balance_nm2 := NULL;
3786 L_main_mesg := '*** SUI ER Liability does not = 1st 6 month rate '||
3787 TO_CHAR(L_first_half_rate*100)||
3788 '%, last 6 month rate '||
3789 TO_CHAR(L_second_half_rate*100)||
3790 '% of SUI ER Taxable Balance ***';
3791
3792 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3793 IN_prc_tax_unit_id,
3794 IN_prc_organization_id,
3795 IN_prc_location_id, IN_pact_id,
3796 IN_chunk_no, IN_prc_person_id,
3797 IN_prc_assignment_number,
3798 L_balance_nm1,
3799 L_balance_nm2,
3800 L_sui_er_bal, L_sui_er_tax,
3801 L_calculated, L_difference, curr_state_abbrev,
3802 L_main_mesg,
3803 curr_state_code||'-000-0000',
3804 L_asg_action_id,
3805 IN_prc_assignment_id );
3806 END IF;
3807 ELSE
3808 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
3809 L_as_of_date, 'SUI', 'EE','FULL');
3810
3811 L_calculated := ROUND(L_sui_ee_bal * L_calc_rate,2);
3812
3813 IF ABS(L_sui_ee_tax - L_calculated) > 0.1 THEN
3814
3815 L_difference := L_sui_ee_tax - L_calculated;
3816 -- significant different found, write to tmp file for report
3817 L_balance_nm1 := 'YTD SUI EE Taxable';
3818 L_balance_nm2 := NULL;
3819 L_main_mesg := '*** SUI EE Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI EE Taxable Balance ***';
3820
3821 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3822 IN_prc_tax_unit_id,
3823 IN_prc_organization_id,
3824 IN_prc_location_id, IN_pact_id,
3825 IN_chunk_no, IN_prc_person_id,
3826 IN_prc_assignment_number,
3827 L_balance_nm1,
3828 L_balance_nm2,
3829 L_sui_ee_bal, L_sui_ee_tax,
3830 L_calculated, L_difference, curr_state_abbrev,
3831 L_main_mesg,
3832 curr_state_code||'-000-0000',
3833 L_asg_action_id,
3834 IN_prc_assignment_id );
3835 END IF;
3836
3837 -- Now do the ER portion
3838 -- First check if SUI override rate is entered by user
3839 L_calc_rate := NULL;
3840
3841
3842 /*********************************** 3005756 START ****************************************/
3843
3844 L_calc_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'C' );
3845 L_dummy_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'D' );
3846
3847 /************************************* 3005756 END *******************************************/
3848
3849 IF L_calc_rate IS NULL THEN
3850 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
3851 L_as_of_date, 'SUI', 'ER','FULL');
3852 END IF;
3853
3854 L_calculated := ROUND(L_sui_er_bal * L_calc_rate,2);
3855
3856 IF ABS(L_sui_er_tax - L_calculated) > 0.1 THEN
3857
3858 L_difference := L_sui_er_tax - L_calculated;
3859 -- significant different found, write to tmp file for report
3860 L_balance_nm1 := 'YTD SUI ER Taxable';
3861 L_balance_nm2 := NULL;
3862 L_main_mesg := '*** SUI ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI ER Taxable Balance ***';
3863
3864 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3865 IN_prc_tax_unit_id,
3866 IN_prc_organization_id,
3867 IN_prc_location_id, IN_pact_id,
3868 IN_chunk_no, IN_prc_person_id,
3869 IN_prc_assignment_number,
3870 L_balance_nm1,
3871 L_balance_nm2,
3872 L_sui_er_bal, L_sui_er_tax,
3873 L_calculated, L_difference, curr_state_abbrev,
3874 L_main_mesg,
3875 curr_state_code||'-000-0000',
3876 L_asg_action_id,
3877 IN_prc_assignment_id );
3878 END IF;
3879
3880 END IF; -- end if curr_state_code IN (30, 31, 43, 46)
3881
3882 /****************** YTD ****************************************/
3883
3884 END IF; -- YTD
3885
3886 -- q)
3887 IF L_sui_ee_subj_whable <= 0 AND L_sui_ee_tax > 0 THEN
3888
3889 L_balance_nm1 := L_dimension || ' SUI EE Subject Withholdable';
3890 L_balance_nm2 := L_dimension || ' SUI EE Withheld';
3891 L_main_mesg := '*** ' || L_dimension || ' SUI EE Subject Withholdable <= 0 and ' || L_dimension ||
3892 ' SUI EE Withheld > 0 ***';
3893
3894 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3895 IN_prc_tax_unit_id,
3896 IN_prc_organization_id, IN_prc_location_id,
3897 IN_pact_id,
3898 IN_chunk_no, IN_prc_person_id,
3899 IN_prc_assignment_number,
3900 L_balance_nm1,
3901 L_balance_nm2,
3902 L_sui_ee_subj_whable, L_sui_ee_tax,
3903 NULL, NULL, curr_state_abbrev,
3904 L_main_mesg,
3905 curr_state_code||'-000-0000',
3906 L_asg_action_id,
3907 IN_prc_assignment_id );
3908 END IF;
3909
3910 -- q)
3911 IF L_sui_er_subj_whable <= 0 AND L_sui_er_tax > 0 THEN
3912
3913 L_balance_nm1 := L_dimension || ' SUI ER Subject Withholdable';
3914 L_balance_nm2 := L_dimension || ' SUI ER Withheld';
3915 L_main_mesg := '*** ' || L_dimension || ' SUI ER Subject Withholdable <= 0 and ' || L_dimension ||
3916 ' SUI ER Withheld > 0 ***';
3917
3918 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3919 IN_prc_tax_unit_id,
3920 IN_prc_organization_id, IN_prc_location_id,
3921 IN_pact_id,
3922 IN_chunk_no, IN_prc_person_id,
3923 IN_prc_assignment_number,
3924 L_balance_nm1,
3925 L_balance_nm2,
3926 L_sui_er_subj_whable, L_sui_er_tax,
3927 NULL, NULL, curr_state_abbrev,
3928 L_main_mesg,
3929 curr_state_code||'-000-0000',
3930 L_asg_action_id,
3931 IN_prc_assignment_id );
3932 END IF;
3933
3934
3935 -- s)
3936 IF L_fit_ee_subj_whable <= 0 AND L_sui_ee_tax > 0 THEN
3937
3938 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3939 L_balance_nm2 := L_dimension || ' SUI EE Withheld';
3940 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3941 ' SUI EE Withheld > 0 ***';
3942
3943 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3944 IN_prc_tax_unit_id,
3945 IN_prc_organization_id, IN_prc_location_id,
3946 IN_pact_id,
3947 IN_chunk_no, IN_prc_person_id,
3948 IN_prc_assignment_number,
3949 L_balance_nm1,
3950 L_balance_nm2,
3951 L_fit_ee_subj_whable, L_sui_ee_tax,
3952 NULL, NULL, curr_state_abbrev,
3953 L_main_mesg,
3954 curr_state_code||'-000-0000',
3955 L_asg_action_id,
3956 IN_prc_assignment_id );
3957 END IF;
3958
3959
3960 -- s)
3961 IF L_fit_ee_subj_whable <= 0 AND L_sui_er_tax > 0 THEN
3962
3963 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3964 L_balance_nm2 := L_dimension || ' SUI ER Withheld';
3965 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3966 ' SUI ER Withheld > 0 ***';
3967
3968 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3969 IN_prc_tax_unit_id,
3970 IN_prc_organization_id, IN_prc_location_id,
3971 IN_pact_id,
3972 IN_chunk_no, IN_prc_person_id,
3973 IN_prc_assignment_number,
3974 L_balance_nm1,
3975 L_balance_nm2,
3976 L_fit_ee_subj_whable, L_sui_er_tax,
3977 NULL, NULL, curr_state_abbrev,
3978 L_main_mesg,
3979 curr_state_code||'-000-0000',
3980 L_asg_action_id,
3981 IN_prc_assignment_id );
3982 END IF;
3983
3984
3985 -- r)
3986 IF L_fit_ee_gross_earnings < L_sum_sui_er_bal THEN
3987
3988 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
3989 L_balance_nm2 := 'TOTAL ' || L_dimension || ' SUI ER Taxable';
3990 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < TOTAL ' || L_dimension ||
3991 ' SUI ER Taxable ***';
3992
3993 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3994 IN_prc_tax_unit_id,
3995 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3996 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
3997 L_balance_nm1,
3998 L_balance_nm2,
3999 L_fit_ee_gross_earnings, L_sum_sui_er_bal,
4000 NULL, NULL, NULL,
4001 L_main_mesg,
4002 '00-000-0000',
4003 L_asg_action_id,
4004 IN_prc_assignment_id );
4005 END IF;
4006
4007
4008 END IF; -- end if 'SUI'
4009
4010
4011 END prc_state_balances ;
4012
4013
4014 PROCEDURE prc_county_balances ( curr_jurisdiction_code IN VARCHAR2
4015 , curr_jurisdiction_name IN VARCHAR2 )
4016 IS
4017 BEGIN
4018 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4019 L_as_of_date, L_dimension, 'COUNTY', 'WITHHELD', 'EE',
4020 curr_jurisdiction_code,
4021 L_county_ee_tax);
4022
4023 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4024 L_as_of_date, L_dimension, 'COUNTY', 'SUBJ_WHABLE', 'EE',
4025 curr_jurisdiction_code,
4026 L_county_ee_s_whable);
4027
4028 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4029 L_as_of_date, L_dimension, 'COUNTY', 'SUBJ_NWHABLE', 'EE',
4030 curr_jurisdiction_code,
4031 L_county_ee_s_nwhable);
4032
4033 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4034 L_as_of_date, L_dimension, 'COUNTY', 'REDUCED_SUBJ_WHABLE', 'EE',
4035 curr_jurisdiction_code,
4036 L_county_ee_r_s_whable);
4037
4038
4039 L_county_ee_subject := L_county_ee_s_whable + L_county_ee_s_nwhable;
4040
4041
4042 -- y)
4043 IF L_county_ee_s_whable < L_county_ee_r_s_whable THEN
4044
4045 L_balance_nm1 := L_dimension || ' COUNTY EE Subject Withholdable';
4046 L_balance_nm2 := L_dimension || ' COUNTY EE Reduced Subject Withholdable';
4047 L_main_mesg := '*** ' || L_dimension || ' COUNTY EE Subject Withholdable < ' || L_dimension ||
4048 ' COUNTY EE '||'Reduced Subject Withholdable ***';
4049
4050 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4051 IN_prc_tax_unit_id,
4052 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
4053 IN_chunk_no, IN_prc_person_id,
4054 IN_prc_assignment_number,
4055 L_balance_nm1,
4056 L_balance_nm2,
4057 L_county_ee_s_whable, L_county_ee_r_s_whable,
4058 NULL, NULL, curr_jurisdiction_name,
4059 L_main_mesg,
4060 SUBSTR(curr_jurisdiction_code,1,6)||'-0000',
4061 L_asg_action_id ,
4062 IN_prc_assignment_id );
4063 END IF;
4064
4065 -- x)
4066 IF L_fit_ee_subj_whable <= 0 AND L_county_ee_tax > 0 THEN
4067
4068 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
4069 L_balance_nm2 := L_dimension || ' County Withheld';
4070 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
4071 ' County '||'Withheld > 0 ***';
4072
4073 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4074 IN_prc_tax_unit_id,
4075 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
4076 IN_chunk_no, IN_prc_person_id,
4077 IN_prc_assignment_number,
4078 L_balance_nm1,
4079 L_balance_nm2,
4080 L_fit_ee_subj_whable, L_county_ee_tax,
4081 NULL, NULL, curr_jurisdiction_name,
4082 L_main_mesg,
4083 SUBSTR(curr_jurisdiction_code,1,6)||'-0000',
4084 L_asg_action_id,
4085 IN_prc_assignment_id );
4086 END IF;
4087
4088
4089 -- w)
4090 IF fnc_lit_tax_exists(curr_jurisdiction_code, L_as_of_date, 'COUNTY') THEN
4091 IF L_county_ee_subject <= 0 AND L_county_ee_tax > 0 THEN
4092
4093 L_balance_nm1 := L_dimension || ' County Subject';
4094 L_balance_nm2 := L_dimension || ' County Withheld';
4095 L_main_mesg := '*** ' || L_dimension || ' County Subject <= 0 and ' || L_dimension ||
4096 ' County Withheld > 0 ***';
4097
4098 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4099 IN_prc_tax_unit_id,
4100 IN_prc_organization_id, IN_prc_location_id,
4101 IN_pact_id,
4102 IN_chunk_no, IN_prc_person_id,
4103 IN_prc_assignment_number,
4104 L_balance_nm1,
4105 L_balance_nm2,
4106 L_county_ee_subject, L_county_ee_tax,
4107 NULL, NULL, curr_jurisdiction_name,
4108 L_main_mesg,
4109 SUBSTR(curr_jurisdiction_code,1,6)||'-0000',
4110 L_asg_action_id,
4111 IN_prc_assignment_id );
4112 END IF;
4113 END IF; -- w)
4114
4115
4116 END prc_county_balances ;
4117
4118
4119 PROCEDURE prc_city_balances ( curr_jurisdiction_code IN VARCHAR2
4120 , curr_jurisdiction_name IN VARCHAR2 )
4121 IS
4122 BEGIN
4123 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4124 L_as_of_date, L_dimension, 'CITY', 'WITHHELD', 'EE',
4125 curr_jurisdiction_code,
4126 L_city_ee_tax);
4127
4128 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4129 L_as_of_date, L_dimension, 'CITY', 'SUBJ_WHABLE', 'EE',
4130 curr_jurisdiction_code,
4131 L_city_ee_s_whable);
4132
4133 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4134 L_as_of_date,L_dimension, 'CITY', 'SUBJ_NWHABLE', 'EE',
4135 curr_jurisdiction_code,
4136 L_city_ee_s_nwhable);
4137
4138 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4139 L_as_of_date, L_dimension, 'CITY', 'REDUCED_SUBJ_WHABLE', 'EE',
4140 curr_jurisdiction_code,
4141 L_city_ee_r_s_whable);
4142
4143 L_city_ee_subject := L_city_ee_s_whable + L_city_ee_s_nwhable;
4144
4145 -- y)
4146 IF L_city_ee_s_whable < L_city_ee_r_s_whable THEN
4147
4148 L_balance_nm1 := L_dimension || ' CITY EE Subject Withholdable';
4149 L_balance_nm2 := L_dimension || ' CITY EE Reduced Subject Withholdable';
4150 L_main_mesg := '*** ' || L_dimension || ' CITY EE Subject Withholdable < ' || L_dimension ||
4151 ' CITY EE Reduced '||'Subject Withholdable ***';
4152
4153 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4154 IN_prc_tax_unit_id,
4155 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
4156 IN_chunk_no, IN_prc_person_id,
4157 IN_prc_assignment_number,
4158 L_balance_nm1,
4159 L_balance_nm2,
4160 L_city_ee_s_whable, L_city_ee_r_s_whable,
4161 NULL, NULL, curr_jurisdiction_name,
4162 L_main_mesg,
4163 curr_jurisdiction_code,
4164 L_asg_action_id,
4165 IN_prc_assignment_id );
4166 END IF;
4167
4168
4169 -- x)
4170 IF L_fit_ee_subj_whable <= 0 AND L_city_ee_tax > 0 THEN
4171
4172 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
4173 L_balance_nm2 := L_dimension || ' City Withheld';
4174 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
4175 ' City '||'Withheld > 0 ***';
4176
4177
4178 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4179 IN_prc_tax_unit_id,
4180 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
4181 IN_chunk_no, IN_prc_person_id,
4182 IN_prc_assignment_number,
4183 L_balance_nm1,
4184 L_balance_nm2,
4185 L_fit_ee_subj_whable, L_city_ee_tax,
4186 NULL, NULL, curr_jurisdiction_name,
4187 L_main_mesg,
4188 curr_jurisdiction_code,
4189 L_asg_action_id,
4190 IN_prc_assignment_id );
4191 END IF;
4192
4193
4194 -- w)
4195 IF fnc_lit_tax_exists(curr_jurisdiction_code, L_as_of_date, 'CITY') THEN
4196 IF L_city_ee_subject <= 0 AND L_city_ee_tax > 0 THEN
4197
4198 L_balance_nm1 := L_dimension || ' City Subject';
4199 L_balance_nm2 := L_dimension || ' City Withheld';
4200 L_main_mesg := '*** ' || L_dimension || ' City Subject <= 0 and ' || L_dimension ||
4201 ' City Withheld > 0 ***';
4202
4203 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4204 IN_prc_tax_unit_id,
4205 IN_prc_organization_id, IN_prc_location_id,
4206 IN_pact_id,
4207 IN_chunk_no, IN_prc_person_id,
4208 IN_prc_assignment_number,
4209 L_balance_nm1,
4210 L_balance_nm2,
4211 L_city_ee_subject, L_city_ee_tax,
4212 NULL, NULL, curr_jurisdiction_name,
4213 L_main_mesg,
4214 curr_jurisdiction_code,
4215 L_asg_action_id,
4216 IN_prc_assignment_id );
4217 END IF;
4218 END IF;
4219
4220
4221 END prc_city_balances ;
4222
4223
4224
4225 PROCEDURE prc_school_balances ( curr_jurisdiction_code IN VARCHAR2
4226 , curr_jurisdiction_name IN VARCHAR2
4227 , curr_reg_jurisdiction_cd IN VARCHAR2 )
4228 IS
4229 BEGIN
4230 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4231 L_as_of_date, L_dimension, 'SCHOOL', 'WITHHELD', 'EE',
4232 curr_jurisdiction_code,
4233 L_school_ee_tax);
4234
4235 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4236 L_as_of_date, L_dimension, 'SCHOOL', 'SUBJ_WHABLE', 'EE',
4237 curr_jurisdiction_code,
4238 L_school_ee_s_whable);
4239
4240 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4241 L_as_of_date, L_dimension, 'SCHOOL', 'SUBJ_NWHABLE', 'EE',
4242 curr_jurisdiction_code,
4243 L_school_ee_s_nwhable);
4244
4245 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
4246 L_as_of_date, L_dimension, 'SCHOOL', 'REDUCED_SUBJ_WHABLE', 'EE',
4247 curr_jurisdiction_code,
4248 L_school_ee_r_s_whable);
4249
4250 L_school_ee_subject := L_school_ee_s_whable + L_school_ee_s_nwhable;
4251
4252
4253 -- y)
4254 IF L_school_ee_s_whable < L_school_ee_r_s_whable THEN
4255
4256 L_balance_nm1 := L_dimension || ' SCHOOL EE Subject Withholdable';
4257 L_balance_nm2 := L_dimension || ' SCHOOL EE Reduced Subject Withholdable';
4258 L_main_mesg := '*** ' || L_dimension || ' SCHOOL EE Subject Withholdable < ' || L_dimension ||
4259 ' SCHOOL EE '||'Reduced Subject Withholdable ***';
4260
4261 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4262 IN_prc_tax_unit_id,
4263 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
4264 IN_chunk_no, IN_prc_person_id,
4265 IN_prc_assignment_number,
4266 L_balance_nm1,
4267 L_balance_nm2,
4268 L_school_ee_s_whable, L_school_ee_r_s_whable,
4269 NULL, NULL, curr_jurisdiction_name,
4270 L_main_mesg,
4271 curr_reg_jurisdiction_cd,
4272 L_asg_action_id,
4273 IN_prc_assignment_id );
4274 END IF;
4275
4276
4277 -- x)
4278 IF L_fit_ee_subj_whable <= 0 AND L_school_ee_tax > 0 THEN
4279
4280 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
4281 L_balance_nm2 := L_dimension || ' School Withheld';
4282 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
4283 ' School '||'Withheld > 0 ***';
4284
4285 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4286 IN_prc_tax_unit_id,
4287 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
4288 IN_chunk_no, IN_prc_person_id,
4289 IN_prc_assignment_number,
4290 L_balance_nm1,
4291 L_balance_nm2,
4292 L_fit_ee_subj_whable, L_school_ee_tax,
4293 NULL, NULL, curr_jurisdiction_name,
4294 L_main_mesg,
4295 curr_reg_jurisdiction_cd,
4296 L_asg_action_id ,
4297 IN_prc_assignment_id );
4298 END IF;
4299
4300
4301 -- w)
4302 IF fnc_lit_tax_exists(curr_reg_jurisdiction_cd, L_as_of_date, 'SCHOOL') THEN
4303 IF L_school_ee_subject <= 0 AND L_school_ee_tax > 0 THEN
4304
4305 L_balance_nm1 := L_dimension || ' School Subject';
4306 L_balance_nm2 := L_dimension || ' School Withheld';
4307 L_main_mesg := '*** ' || L_dimension || ' School Subject <= 0 and ' || L_dimension ||
4308 ' School Withheld > 0 ***';
4309
4310 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
4311 IN_prc_tax_unit_id,
4312 IN_prc_organization_id, IN_prc_location_id,
4313 IN_pact_id,
4314 IN_chunk_no, IN_prc_person_id,
4315 IN_prc_assignment_number,
4316 L_balance_nm1,
4317 L_balance_nm2,
4318 L_school_ee_subject, L_school_ee_tax,
4319 NULL, NULL, curr_jurisdiction_name,
4320 L_main_mesg,
4321 curr_reg_jurisdiction_cd,
4322 L_asg_action_id,
4323 IN_prc_assignment_id );
4324 END IF;
4325 END IF;
4326
4327 END prc_school_balances ;
4328
4329 /******************************** 3005756 end ************************************************/
4330
4331 -----------------------------------------------
4332 BEGIN
4333 -- setup commit counter before we start
4334
4335
4336 G_commit_count := IN_commit_count;
4337
4338 -- get all required parameters from legislative parameter string
4339
4340 /***************** 3005756 START *******************************/
4341
4342 -- Assign the local payroll stuff variables the global values
4343
4344 L_business_id := G_business_id;
4345 L_as_of_date := G_as_of_date;
4346 L_leg_param := G_leg_param;
4347
4348 /*********************3005756 END ***************************/
4349
4350 L_dimension := fnc_get_parameter('B_Dim',L_leg_param);
4351 L_gre_id := fnc_get_parameter('GRE',L_leg_param);
4352 L_org_id := fnc_get_parameter('Org',L_leg_param);
4353 L_location_id := fnc_get_parameter('Loc',L_leg_param);
4354 L_tax_type := fnc_get_parameter('T_T',L_leg_param);
4355 L_tax_type_state := fnc_get_parameter('T_T_S',L_leg_param);
4356 L_usr_SDI_ER_rate := fnc_get_parameter('ERR',L_leg_param);
4357 L_usr_SDI_EE_rate := fnc_get_parameter('EER',L_leg_param);
4358
4359 hr_utility.trace('L_dimension: ' || L_dimension);
4360 hr_utility.trace('L_tax_type: ' || L_tax_type);
4361 hr_utility.trace('L_tax_type_state: ' || L_tax_type_state);
4362
4363 -- calculate first half date for later use if type is SUI
4364 --L_first_half_date := TO_DATE('30-JUN-'||TO_CHAR(L_as_of_date,'YYYY'),'DD-MON-YYYY');
4365 L_first_half_date := TO_DATE('30-06-'||TO_CHAR(L_as_of_date,'YYYY'),'DD-MM-YYYY');
4366
4367 hr_utility.trace('L_first_half_date: ' || to_char(L_first_half_date,'DD-MON-YYYY'));
4368 -- calculate the start date based on YTD or QTD dimensions
4369
4370 /***********************3005756 START *********************************/
4371
4372 -- If the federal pl/sql table is empty populate it and then fetch the
4373 -- values into global variables
4374
4375 IF pay_us_payroll_utils.ltr_fed_tax_info.count<1 THEN
4376 pay_us_payroll_utils.populate_jit_information(p_effective_date => L_as_of_date
4377 ,p_get_federal => 'Y' );
4378 END IF;
4379
4380 G_ss_ee_wage_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_wage;
4381 G_ss_ee_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_rate;
4382 G_ss_er_wage_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_wage;
4383 G_ss_er_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_rate;
4384 G_medi_ee_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).med_ee_rate;
4385 G_medi_er_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).med_er_rate;
4386
4387 /****************************** 3005756 END ***************************************/
4388
4389
4390 IF L_dimension = 'QTD' THEN
4391 L_start_date := TRUNC(L_as_of_date,'Q') ;
4392 ELSIF L_dimension = 'YTD' THEN
4393 L_start_date := TRUNC(L_as_of_date,'YYYY');
4394 END IF;
4395
4396 L_asg_action_id := IN_prc_lockedactid ;
4397
4398 L_medi_exempt := f_check_medi_exempt(IN_prc_assignment_id,
4399 L_start_date,
4400 L_as_of_date);
4401
4402
4403 -- Call for all Unacceptable Federal balance checks
4404
4405 prc_federal_balances();
4406
4407 /************************************* Bug 2963239 Changes End *********************************************/
4408
4409 /* bug 9721787 check if the assignment exists as of the AS_OF_DATE.
4410 if not get the max assignment effective_end_date and pass that
4411 to the cursor
4412 */
4413
4414 l_asg_exists := 1;
4415 l_cursor_fetch_date := L_as_of_date;
4416
4417 select count(0)
4418 into l_asg_exists
4419 from per_assignments_f
4420 where assignment_id = IN_prc_assignment_id
4421 and L_as_of_date between effective_start_date and effective_end_date;
4422 --
4423 -- if assignment doesn't exist ...
4424 --
4425
4426 --
4427 IF l_asg_exists = 0 THEN
4428 --
4429 -- get the termination date ...
4430 --
4431 select max(effective_end_date)
4432 into l_cursor_fetch_date
4433 from per_assignments_f
4434 where assignment_id = IN_prc_assignment_id;
4435 ELSE
4436 l_cursor_fetch_date := L_as_of_date;
4437 END IF;
4438
4439
4440 IF L_tax_type = 'SDI' OR L_tax_type = 'SUI' OR L_tax_type = 'SIT'
4441 OR L_tax_type IS NULL
4442 THEN
4443
4444 -- this variable must be reset before going into loop
4445
4446 L_sum_sui_er_bal := 0;
4447 L_sum_sdi_ee_bal := 0;
4448
4449 /************************ 3005756 start *******************************************************/
4450 hr_utility.trace('after code changes G_state_flag: ' || G_state_flag);
4451 IF G_state_flag = 'Y' THEN
4452
4453 hr_utility.trace('Balances are valid .Inside vailid state cursor');
4454
4455
4456 FOR curr_state IN c_state_jurisdictions_valid(IN_prc_person_id,
4457 L_tax_type_state, L_start_date,
4458 L_as_of_date)
4459 LOOP
4460
4461 hr_utility.trace('Valid - State Code: ' || curr_state.state_code);
4462 hr_utility.trace('Valid - jurisdiction_code: ' || curr_state.jurisdiction_code);
4463 hr_utility.trace('Valid - state_abbrev: ' || curr_state.state_abbrev);
4464 prc_state_balances ( curr_state.jurisdiction_code,curr_state.state_code,curr_state.state_abbrev);
4465
4466 END LOOP; -- end curr_state
4467
4468 ELSE
4469 hr_utility.trace('Balances are invalid .Inside invalid state cursor');
4470
4471
4472 FOR curr_state IN c_state_jurisdictions(IN_prc_person_id,
4473 L_tax_type_state, L_start_date,
4474 l_cursor_fetch_date)
4475 LOOP
4476 hr_utility.trace('Invalid - State Code: ' || curr_state.state_code);
4477 hr_utility.trace('Invalid - jurisdiction_code: ' || curr_state.jurisdiction_code);
4478 hr_utility.trace('Invalid - state_abbrev: ' || curr_state.state_abbrev);
4479
4480 prc_state_balances ( curr_state.jurisdiction_code,curr_state.state_code,curr_state.state_abbrev);
4481
4482 END LOOP; -- end curr_state
4483
4484 END IF;
4485
4486
4487 /************************** 3005756 end ********************************************************/
4488
4489 END IF; -- end if 'SDI', 'SUI', 'SIT'
4490
4491
4492
4493 IF L_tax_type = 'LIT' OR L_tax_type IS NULL THEN
4494 -- for each valid county jurisdiction ...
4495
4496
4497 /******************** 3005756 start ***********************************************/
4498
4499 IF G_county_flag = 'Y' THEN
4500 hr_utility.trace('Balances are valid .Inside valid county cursor');
4501
4502 FOR curr_county IN c_county_jurisdictions_valid(IN_prc_person_id,
4503 L_tax_type_state, L_start_date,
4504 L_as_of_date)
4505 LOOP
4506
4507 prc_county_balances ( curr_county.jurisdiction_code,curr_county.jurisdiction_name );
4508
4509 END LOOP; -- end curr_county loop
4510
4511 ELSE
4512 hr_utility.trace('Balances are invalid .Inside invalid county cursor');
4513
4514
4515 FOR curr_county IN c_county_jurisdictions(IN_prc_person_id,
4516 L_tax_type_state, L_start_date,
4517 l_cursor_fetch_date)
4518 LOOP
4519
4520 prc_county_balances ( curr_county.jurisdiction_code,curr_county.jurisdiction_name );
4521
4522 END LOOP; -- end curr_county loop
4523
4524 END IF;
4525
4526 /**************************3005756 end ********************************/
4527
4528 -- for each city valid jurisdiction ...
4529
4530
4531 /*********************** 3005756 start *****************************************/
4532
4533 IF G_city_flag = 'Y' THEN
4534 hr_utility.trace('Balances are valid .Inside valid city cursor');
4535
4536 FOR curr_city IN c_city_jurisdictions_valid(IN_prc_person_id,
4537 L_tax_type_state, L_start_date,
4538 L_as_of_date)
4539 LOOP
4540
4541 prc_city_balances ( curr_city.jurisdiction_code,curr_city.jurisdiction_name );
4542
4543 END LOOP; -- end curr_city loop
4544
4545 ELSE
4546 hr_utility.trace('Balances are invalid .Inside invalid city cursor');
4547
4548
4549 FOR curr_city IN c_city_jurisdictions(IN_prc_person_id,
4550 L_tax_type_state, L_start_date,
4551 l_cursor_fetch_date)
4552 LOOP
4553
4554 prc_city_balances ( curr_city.jurisdiction_code,curr_city.jurisdiction_name );
4555
4556 END LOOP; -- end curr_city loop
4557
4558 END IF;
4559
4560 /************************************ 3005756 end ***************************/
4561
4562
4563 -- for each valid school jurisdiction ...
4564
4565 /**************************** 3005756 start **********************************/
4566
4567 IF G_school_flag = 'Y' THEN
4568 hr_utility.trace('Balances are valid .Inside valid school cursor');
4569
4570 FOR curr_school IN c_school_jurisdictions_valid(IN_prc_person_id,
4571 L_tax_type_state,
4572 IN_prc_tax_unit_id, L_start_date,
4573 L_as_of_date)
4574 LOOP
4575
4576 prc_school_balances ( curr_school.jurisdiction_code
4577 , curr_school.jurisdiction_name
4578 , curr_school.reg_jurisdiction_cd );
4579
4580 END LOOP; -- end curr_school loop
4581
4582 ELSE
4583 hr_utility.trace('Balances are invalid .Inside invalid school cursor');
4584
4585
4586 FOR curr_school IN c_school_jurisdictions(IN_prc_person_id,
4587 L_tax_type_state,
4588 IN_prc_tax_unit_id, L_start_date,
4589 l_cursor_fetch_date)
4590 LOOP
4591
4592 prc_school_balances ( curr_school.jurisdiction_code
4593 , curr_school.jurisdiction_name
4594 , curr_school.reg_jurisdiction_cd );
4595
4596 END LOOP; -- end curr_school loop
4597
4598 END IF;
4599
4600 /*********************************** 3005756 end ******************************/
4601
4602
4603 END IF; -- end if 'LIT'
4604
4605
4606
4607 EXCEPTION
4608 WHEN OTHERS THEN
4609 -- rollback all uncommited changes
4610 ROLLBACK;
4611 -- does not matter what the error is delete all commited inserted tmp records
4612 DELETE pay_us_rpt_totals
4613 WHERE session_id = IN_pact_id
4614 AND business_group_id = IN_chunk_no
4615 AND tax_unit_id = IN_prc_tax_unit_id; -- Bug 3316599 to reduce the cost of query
4616 COMMIT;
4617 -- reraise the error
4618 RAISE;
4619 END prc_process_data;
4620
4621
4622 END PAYUSUNB_PKG;