[Home] [Help]
PACKAGE BODY: APPS.PAYUSUNB_PKG
Source
1 package body PAYUSUNB_PKG as
2 /* $Header: payusunb.pkb 120.0.12010000.5 2008/08/06 06:41:49 ubhat 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
95
96 ******************************************************************************/
97
98 c_fixed_futa_rt CONSTANT NUMBER(10,4) := 6.2;
99
100 -- define some global variables for temporary storage
101 G_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE := NULL;
102 G_payroll_id pay_payroll_actions.payroll_id%TYPE := NULL;
103 G_got_fed_rate BOOLEAN := FALSE;
104 G_ss_ee_rate NUMBER := NULL;
105 G_ss_er_rate NUMBER := NULL;
106 G_medi_ee_rate NUMBER := NULL;
107 G_medi_er_rate NUMBER := NULL;
108 G_commit_count NUMBER := NULL;
109
110 -- Global values to store the flag based on the validity of
111 -- the corresponding balances i.e., IF G_state_flag := 'Y' then
112 -- all state balances are valid in pay_run_balances
113 -- else atleast one of the state balance is invalid
114 G_state_flag VARCHAR2(1);
115 G_county_flag VARCHAR2(1);
116 G_city_flag VARCHAR2(1);
117 G_school_flag VARCHAR2(1);
118
119 -- Bug 3291736
120 -- Variable to hold the dummy assignment insertion
121 -- if there are no employees with Unacceptable balance
122 G_dummy_action_inserted_flag VARCHAR2(1) := 'N';
123
124 /**********************Bug 2963239 Changes start ******************************
125 ********************** variables to hold the SS limit values ******************/
126 G_ss_ee_wage_limit NUMBER := NULL;
127 G_ss_er_wage_limit NUMBER := NULL;
128
129 /********************3005756 START *******************************/
130 -- Definitions of the pl/sql tables for caching.
131
132 TYPE futa_credit_info_rec IS RECORD
133 ( organization_id NUMBER
134 ,sui_state_code VARCHAR2(2)
135 ,futa_credit_rate NUMBER );
136
137 TYPE futa_credit_info_table IS TABLE OF
138 futa_credit_info_rec
139 INDEX BY BINARY_INTEGER;
140
141 futa_credit_info futa_credit_info_table;
142
143 TYPE sui_sdi_tax_info_rec IS RECORD
144 ( sui_ee_limit NUMBER
145 ,sui_er_limit NUMBER
146 ,sdi_ee_limit NUMBER
147 ,sdi_er_limit NUMBER
148 );
149
150 TYPE sui_sdi_tax_info_table IS TABLE OF
151 sui_sdi_tax_info_rec
152 INDEX BY BINARY_INTEGER;
153
154 sui_sdi_tax_info1 sui_sdi_tax_info_table;
155 sui_sdi_tax_info2 sui_sdi_tax_info_table;
156 sui_sdi_tax_info3 sui_sdi_tax_info_table;
157
158 TYPE sui_sdi_override_rec is RECORD
159 ( sui_override_rate NUMBER
160 ,sui_dummy_rate NUMBER
161 ,sdi_override_rate NUMBER );
162
163 TYPE sui_sdi_override_tab IS TABLE OF sui_sdi_override_rec
164 INDEX BY BINARY_INTEGER;
165
166 sui_sdi_override_info sui_sdi_override_tab;
167
168 /********************** fnc_lit ***************************/
169 TYPE county_tax_info_rec IS RECORD
170 ( jurisdiction_code varchar2(11)
171 ,cnty_tax_exists varchar2(1)
172 ,cnty_sd_tax_exists varchar2(1)
173 );
174
175 TYPE county_tax_info_table IS TABLE OF
176 county_tax_info_rec
177 INDEX BY BINARY_INTEGER;
178
179 county_tax_info county_tax_info_table;
180
181 TYPE city_tax_info_rec IS RECORD
182 ( jurisdiction_code varchar2(11)
183 , city_tax_exists varchar2(1)
184 , city_sd_tax_exists varchar2(1)
185 );
186
187 TYPE city_tax_info_table IS TABLE OF
188 city_tax_info_rec
189 INDEX BY BINARY_INTEGER;
190
191 city_tax_info city_tax_info_table;
192
193 -- Global variables to hold vales fetched by c_get_payroll_stuff cursor
194 G_as_of_date DATE := NULL;
195 G_business_id per_all_assignments_f.business_group_id%TYPE;
196 G_leg_param pay_payroll_actions.legislative_parameters%TYPE;
197
198 -- Cursor and global variable to store the futa_override rate
199 G_futa_override_rt NUMBER := 0;
200
201 -- Cursor fetches the futa override rate based on the tax unit id passed.
202 CURSOR c_get_futa_override_rt(
203 IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE) IS
204 SELECT NVL(org_information7,0)/100
205 FROM hr_organization_information
206 WHERE organization_id = IN_tax_unit_id
207 AND org_information_context = 'Federal Tax Rules';
208
209 -- cursor c_get_payroll_stuff made public
210 -- It will now be called once in action_creation
211 CURSOR c_get_payroll_stuff(IN_pact_id IN pay_payroll_actions.payroll_action_id%TYPE) IS
212 SELECT effective_date, business_group_id, legislative_parameters
213 FROM pay_payroll_actions
214 WHERE payroll_action_id = IN_pact_id;
215
216 /***************** 3005756 END ******************************/
217
218 /***********************************************************************
219 * routine name: range_cursor
220 * purpose:
221 * parameters:
222 * return:
223 * specs:
224 *************************************************************************/
225 PROCEDURE range_cursor (IN_pactid IN NUMBER,
226 OUT_sqlstr OUT NOCOPY VARCHAR2)
227 IS
228
229 lv_sqlstr varchar2(32000);
230 lv_leg_param varchar2(2000);
231 lv_cur_date varchar2(30);
232 lv_b_dim varchar2(10);
233 lv_location_id varchar2(30);
234 lv_organization_id varchar2(30);
235 lv_tax_unit_id varchar2(30);
236 ld_effective_date date;
237 ld_cur_date date;
238
239
240 BEGIN
241 BEGIN
242 select effective_date,legislative_parameters
243 into ld_effective_date,lv_leg_param
244 from pay_payroll_actions
245 where payroll_action_id = IN_pactid;
246
247 END;
248
249 lv_tax_unit_id := payusunb_pkg.fnc_get_parameter('GRE',lv_leg_param);
250 lv_organization_id := payusunb_pkg.fnc_get_parameter('Org',lv_leg_param);
251 lv_location_id := payusunb_pkg.fnc_get_parameter('Loc',lv_leg_param);
252 lv_b_dim := payusunb_pkg.fnc_get_parameter('B_Dim',lv_leg_param);
253
254 if lv_b_dim ='QTD' then
255 ld_cur_date := TRUNC(ld_effective_date,'Q');
256 elsif lv_b_dim ='YTD' then
257 ld_cur_date := TRUNC(ld_effective_date, 'Y');
258 end if;
259
260 select fnd_date.date_to_canonical(ld_cur_date)
261 into lv_cur_date
262 from dual;
263
264 -- range cursor query
265 lv_sqlstr :=
266 'SELECT /*+ ORDERED
267 INDEX (ppa PAY_PAYROLL_ACTIONS_PK)
268 INDEX (pa1 PAY_PAYROLL_ACTIONS_N5)
269 INDEX (act PAY_ASSIGNMENT_ACTIONS_N50)
270 INDEX (paf PER_ASSIGNMENTS_F_PK) */
271 DISTINCT paf.person_id
272 FROM pay_payroll_actions ppa,
273 pay_payroll_actions pa1,
274 pay_assignment_actions act,
275 per_assignments_f paf
276 WHERE ppa.payroll_action_id = :payroll_action_id
277 AND pa1.effective_date >= fnd_date.canonical_to_date('''|| lv_cur_date ||''')
278 AND pa1.effective_date <= ppa.effective_date
279 AND pa1.payroll_action_id = act.payroll_action_id
280 AND paf.assignment_id = act.assignment_id
281 AND pa1.effective_date BETWEEN paf.effective_start_date
282 AND paf.effective_end_date
283 AND pa1.action_type in (''B'',''I'',''R'',''Q'',''V'')
284 AND act.action_status = ''C''
285 AND paf.business_group_id +0 = ppa.business_group_id
286 AND act.tax_unit_id = ' || lv_tax_unit_id;
287
288 if lv_organization_id is not null then
289 lv_sqlstr := lv_sqlstr || ' and paf.organization_id = '||lv_organization_id;
290 end if;
291
292 if lv_location_id is not null then
293 lv_sqlstr := lv_sqlstr || ' and paf.location_id = '||lv_location_id;
294 end if;
295
296 lv_sqlstr := lv_sqlstr || ' ORDER BY paf.person_id';
297
298
299 OUT_sqlstr := lv_sqlstr;
300
301 END range_cursor;
302
303
304 /***************************************************************************
305 * routine name: action_creation
306 * purpose:
307 * parameters:
308 * return:
309 * specs:
310 ****************************************************************************/
311 PROCEDURE action_creation(IN_pactid IN NUMBER,
312 IN_stperson IN NUMBER,
313 IN_endperson IN NUMBER,
314 IN_chunk IN NUMBER) IS
315
316 CURSOR c_actions(cp_start_person_id in number
317 ,cp_end_person_id in number
318 ,cp_tax_unit_id in number
319 ,cp_organization_id in number
320 ,cp_location_id in number
321 ,cp_business_group_id in number
322 ,cp_period_start in date
323 ,cp_period_end in date) is
324 SELECT DISTINCT
325 paf.person_id person_id
326 FROM per_all_assignments_f paf,
327 pay_all_payrolls_f PPY
328 WHERE exists
329 (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
330 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
331 'x'
332 from pay_payroll_actions ppa,
333 pay_assignment_actions paa
334 where ppa.effective_date between cp_period_start
335 and cp_period_end
336 and ppa.action_type in ('R','Q','V','B','I')
337 and ppa.action_status = 'C'
338 and ppa.business_group_id + 0 = cp_business_group_id
339 and ppa.payroll_action_id = paa.payroll_action_id
340 and paa.tax_unit_id = cp_tax_unit_id
341 and paa.action_status = 'C'
342 and paa.assignment_id = paf.assignment_id
343 and ppa.business_group_id = paf.business_group_id +0
344 and ppa.effective_date between paf.effective_start_date
345 and paf.effective_end_date)
346 AND paf.person_id between cp_start_person_id and cp_end_person_id
347 AND paf.assignment_type = 'E'
348 AND (cp_organization_id is null OR
349 paf.organization_id = cp_organization_id)
350 AND (cp_location_id is null OR
351 paf.LOCATION_ID = cp_location_id)
352 AND PPY.payroll_id = paf.payroll_id;
353
354 CURSOR c_actions_person_on(
355 cp_payroll_Action_id in number
356 ,cp_chunk_number in number) is
357 SELECT ppr.person_id person_id
358 FROM pay_population_ranges ppr
359 where ppr.payroll_action_id = cp_payroll_Action_id
360 and ppr.chunk_number = cp_chunk_number;
361
362
363 -- Cursor to get the latest assignment action id details for the person
364 -- selected
365 CURSOR c_get_latest_asg(
366 cp_person_id IN NUMBER
367 ,cp_tax_unit_id IN NUMBER
368 ,cp_as_of_date IN DATE
369 ,cp_start_date IN DATE
370 ,IN_org_id IN NUMBER
371 ,IN_location_id IN NUMBER
372 ) IS
373 /* Change for Performance Bug 6360505 */
374 SELECT /*+ ORDERED */
375 to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
376 FROM per_all_assignments_f paf,
377 pay_payroll_actions ppa,
378 pay_assignment_actions paa,
379 pay_action_classifications pac
380 WHERE paf.person_id = cp_person_id
381 AND paf.payroll_id = ppa.payroll_id
382 AND (paf.organization_id = IN_org_id
383 OR IN_org_id IS NULL)
384 AND (paf.location_id = IN_location_id
385 OR IN_location_id IS NULL)
386 AND paa.assignment_id = paf.assignment_id
387 AND paa.tax_unit_id = cp_tax_unit_id
388 AND paa.payroll_action_id = ppa.payroll_action_id
389 AND ((NVL(paa.run_type_id, ppa.run_type_id) IS NULL
390 AND paa.source_action_id IS NULL)
391 OR (NVL(paa.run_type_id, ppa.run_type_id) IS NOT NULL
392 AND paa.source_action_id IS NOT NULL )
393 OR (ppa.action_type = 'V' AND ppa.run_type_id IS NULL
394 AND paa.run_type_id IS NOT NULL
395 AND paa.source_action_id IS NULL))
396 AND ppa.effective_date BETWEEN paf.effective_start_date
397 AND paf.effective_end_date
398 AND ppa.effective_date BETWEEN cp_start_date AND cp_as_of_date
399 AND ppa.action_type = pac.action_type
400 AND pac.classification_name = 'SEQUENCED';
401
402 /* Change for Performance Bug 6360505 */
403 CURSOR c_get_asg_details(
404 cp_asg_act_id IN NUMBER
405 ,cp_tax_unit_id IN NUMBER
406 ,cp_person_id IN NUMBER
407 ,cp_as_of_date IN DATE
408 ,cp_start_date IN DATE
409 ) IS
410 SELECT paa.assignment_id ,
411 paf.location_id,
412 paf.organization_id,
413 paf.assignment_number
414 FROM pay_assignment_actions paa,
415 pay_payroll_actions ppa,
416 per_all_assignments_f paf
417 WHERE paa.assignment_action_id = cp_asg_act_id
418 AND paa.tax_unit_id = cp_tax_unit_id
419 AND ppa.payroll_action_id = paa.payroll_action_id
420 AND ppa.effective_date BETWEEN cp_start_date AND cp_as_of_date
421 AND paf.assignment_id = paa.assignment_id
422 AND ppa.effective_date BETWEEN paf.effective_start_date
423 AND paf.effective_end_date
424 AND paf.person_id = cp_person_id;
425 /* Change for Performance Bug 6360505 */
426
427 L_lockingactid NUMBER;
428 L_lockedactid NUMBER;
429 L_assignid NUMBER;
430 L_greid NUMBER;
431 L_as_of_date DATE := NULL;
432 L_start_date DATE;
433 L_leg_param pay_payroll_actions.legislative_parameters%TYPE;
434 L_gre_id pay_assignment_actions.tax_unit_id%TYPE;
435 L_org_id per_all_assignments_f.organization_id%TYPE;
436 L_location_id per_all_assignments_f.location_id%TYPE;
437 L_business_id per_all_assignments_f.business_group_id%TYPE;
438 L_dimension VARCHAR2(20) := NULL;
439
440 L_person_id per_all_assignments_f.person_id%TYPE;
441 L_loc_id per_all_assignments_f.location_id%TYPE;
442 L_organization_id per_all_assignments_f.organization_id%TYPE;
443 L_assignment_number per_all_assignments_f.assignment_number%TYPE;
444
445 l_range_person BOOLEAN;
446 BEGIN
447
448 -- get all required parameters from legislative parameter string
449 OPEN c_get_payroll_stuff(IN_pactid);
450 FETCH c_get_payroll_stuff INTO G_as_of_date, G_business_id, G_leg_param;
451 CLOSE c_get_payroll_stuff;
452
453 -- Local variables for payroll related stuff
454 L_as_of_date := G_as_of_date;
455 L_business_id := G_business_id;
456 L_leg_param := G_leg_param;
457
458 L_dimension := fnc_get_parameter('B_Dim',L_leg_param);
459 L_gre_id := fnc_get_parameter('GRE',L_leg_param);
460 L_org_id := fnc_get_parameter('Org',L_leg_param);
461 L_location_id := fnc_get_parameter('Loc',L_leg_param);
462
463 /***************************3005756 START *******************************/
464 -- Get the futa override rate
465 OPEN c_get_futa_override_rt(L_gre_id);
466 FETCH c_get_futa_override_rt INTO G_futa_override_rt;
467 CLOSE c_get_futa_override_rt;
468 /******************************* 3005756 END *******************************/
469
470 -- calculate the start date based on YTD or QTD dimensions
471 IF L_dimension = 'QTD' THEN
472 L_start_date := TRUNC(L_as_of_date,'Q');
473 ELSIF L_dimension = 'YTD' THEN
474 L_start_date := TRUNC(L_as_of_date,'YYYY');
475 END IF;
476
477 /************************* 3005756 start ********************************/
478 -- Fetch the balance validity flags into the global variables for use in
479 -- prc_process_data
480 G_state_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_gre_id,'UNB_STATE');
481 G_county_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_gre_id,'UNB_COUNTY');
482 G_city_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_gre_id,'UNB_CITY');
483 G_school_flag := pay_us_payroll_utils.check_balance_status(L_start_date,L_gre_id,'UNB_SCHOOL');
484 /************************* 3005756 end *********************************/
485
486 l_range_person := pay_ac_utility.range_person_on(
487 p_report_type => 'PAYUSUNB'
488 ,p_report_format => 'DEFAULT'
489 ,p_report_qualifier => 'DEFAULT'
490 ,p_report_category => 'REPORT');
491 hr_utility.set_location('procpyr',1);
492 if l_range_person then
493 OPEN c_actions_person_on(IN_pactid, IN_chunk);
494 else
495 OPEN c_actions(IN_stperson, IN_endperson, L_gre_id,
496 L_org_id, L_location_id, L_business_id,
497 L_start_date, L_as_of_date);
498 end if;
499
500 LOOP
501 hr_utility.set_location('procpyr',2);
502 if l_range_person then
503 FETCH c_actions_person_on INTO L_person_id;
504 EXIT WHEN c_actions_person_on%NOTFOUND;
505 else
506 FETCH c_actions INTO L_person_id;
507 EXIT WHEN c_actions%NOTFOUND;
508 end if;
509
510 -- Bug 3291736
511 -- insert_action(IN_pactid,IN_chunk,L_gre_id,L_person_id,
512 -- L_location_id,L_org_id,L_start_date,L_as_of_date);
513 -- Code to replace call to insert_actions.
514
515 -- we need to insert one action for each of the
516 -- rows that we return FROM the cursor (i.e. one
517 -- for each assignment/pre-payment/reversal).
518 hr_utility.trace('L_person_id = '||to_char(L_person_id));
519 hr_utility.trace('L_org_id = '||to_char(L_org_id));
520 hr_utility.trace('L_location_id = '||to_char(L_location_id));
521 hr_utility.trace('L_as_of_date = '||L_as_of_date);
522 hr_utility.trace('L_start_date = '||L_start_date);
523 hr_utility.trace('L_gre_id = '||to_char(L_gre_id));
524
525 OPEN c_get_latest_asg(L_person_id,L_gre_id,L_as_of_date,
526 L_start_date,L_org_id,L_location_id);
527 FETCH c_get_latest_asg INTO L_lockedactid; /* Change for Performance Bug 6360505 */
528 CLOSE c_get_latest_asg;
529
530 hr_utility.trace('L_lockedactid ' || L_lockedactid);
531 /* Change for Performance Bug 6360505 */
532 OPEN c_get_asg_details(L_lockedactid, L_gre_id, L_person_id,
533 L_as_of_date, L_start_date);
534 FETCH c_get_asg_details INTO L_assignid,L_loc_id,
535 L_organization_id,L_assignment_number;
536 CLOSE c_get_asg_details;
537 /* Change for Performance Bug 6360505 */
538
539 hr_utility.trace('L_assignid '||to_char(L_assignid));
540 hr_utility.trace('L_assignid ' || L_assignid);
541 hr_utility.set_location('procpyr',3);
542
543 SELECT pay_assignment_actions_s.NEXTVAL
544 INTO L_lockingactid
545 FROM dual;
546
547 IF L_lockedactid is not null then
548 prc_process_data(IN_pactid,IN_chunk,500,L_lockingactid,
549 L_lockedactid,L_assignid,L_gre_id
550 ,L_person_id,L_loc_id,L_organization_id
551 ,L_assignment_number );
552 END IF;
553
554 END LOOP;
555 if l_range_person then
556 CLOSE c_actions_person_on;
557 else
558 CLOSE c_actions;
559 end if;
560
561 -- Bug 3291736
562 -- Code to insert dummuy action if there are no actions inserted.
563 -- But if there is no payroll run or prepayments then no dummy action
564 -- will be inserted.
565 IF L_lockedactid is not null and
566 G_dummy_action_inserted_flag = 'N' THEN
567 hr_nonrun_asact.insact(L_lockingactid,L_assignid,
568 IN_pactid,IN_chunk,L_gre_id);
569 END IF;
570
571 END action_creation;
572
573
574 /*************************************************************************
575 * routine name: sort_action
576 * purpose:
577 * parameters:
578 * return:
579 * specs:
580 **************************************************************************/
581 PROCEDURE sort_action(IN_payactid IN VARCHAR2
582 ,IO_sqlstr IN OUT NOCOPY VARCHAR2
583 ,OUT_len OUT NOCOPY NUMBER)
584 IS
585 BEGIN
586 IO_sqlstr := 'SELECT paa1.rowid
587 /* we need the row id of the assignment actions that are
588 created by PYUGEN */
589 FROM hr_organization_units hou,
590 hr_organization_units hou1,
591 hr_locations loc,
592 per_people_f ppf,
593 per_all_assignments_f paf,
594 pay_assignment_actions paa1, /* PYUGEN assignment action */
595 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
596 WHERE ppa1.payroll_action_id = :pactid
597 AND paa1.payroll_action_id = ppa1.payroll_action_id
598 AND paa1.assignment_id = paf.assignment_id
599 AND paf.effective_start_date =
600 (SELECT MAX(paf1.effective_start_date)
601 FROM per_assignments_f paf1
602 WHERE paf1.assignment_id = paf.assignment_id
603 AND paf1.effective_start_date <= ppa1.effective_date
604 AND paf1.effective_end_date >=
605 DECODE(payusunb_pkg.fnc_get_parameter(''B_Dim'',
606 ppa1.legislative_parameters),
607 ''QTD'',
608 TRUNC(ppa1.effective_date,''Q''),
609 ''YTD'',
610 TRUNC(ppa1.effective_date,''Y''))
611 )
612 AND hou1.organization_id = paa1.tax_unit_id
613 AND hou.organization_id = paf.organization_id
614 AND loc.location_id = paf.location_id
615 AND ppf.person_id = paf.person_id
616 AND ppa1.effective_date BETWEEN ppf.effective_start_date
617 AND ppf.effective_END_date
618 ORDER BY
619 hou1.name, /* GRE */
620 DECODE(payusunb_pkg.fnc_get_parameter(
621 ''SO1'',ppa1.legislative_parameters),
622 ''Employee'',ppf.full_name,
623 ''Social'',ppf.national_identifier,
624 ''Organization'',hou.name,
625 ''Location'',loc.location_code,null),
626 DECODE(payusunb_pkg.fnc_get_parameter(
627 ''SO2'',ppa1.legislative_parameters),
628 ''Employee'',ppf.full_name,
629 ''Social'',ppf.national_identifier,
630 ''Organization'',hou.name,
631 ''Location'',loc.location_code,null),
632 DECODE(payusunb_pkg.fnc_get_parameter(
633 ''SO3'',ppa1.legislative_parameters),
634 ''Employee'',ppf.full_name,
635 ''Social'',ppf.national_identifier,
636 ''Organization'',hou.name,
637 ''Location'',loc.location_code,null),
638 hou.name,
639 ppf.full_name
640 FOR UPDATE of paa1.assignment_action_id';
641
642 OUT_len := LENGTH(IO_sqlstr); -- return the length of the string.
643
644 END sort_action;
645
646
647 /*************************************************************************
648 routine name: fnc_get_parameter
649 purpose: Gets specified parameter value from legislative Parameter
650 String
651 parameters: IN_name - name of the parameter to get value
652 IN_parameter_list - String containing legislative parameter
653 return: Value for specified parameter name
654 specs:
655 **************************************************************************/
656 FUNCTION fnc_get_parameter(IN_name IN VARCHAR2,
657 IN_parameter_list IN VARCHAR2) RETURN VARCHAR2
658 IS
659 L_start_ptr NUMBER;
660 L_end_ptr NUMBER;
661 L_token_val pay_payroll_actions.legislative_parameters%TYPE;
662 L_par_value pay_payroll_actions.legislative_parameters%TYPE;
663 BEGIN
664
665 L_token_val := IN_name||'=';
666 L_start_ptr := INSTR(IN_parameter_list, L_token_val)
667 + length(L_token_val);
668 L_end_ptr := INSTR(IN_parameter_list, ' ',L_start_ptr);
669
670 /* if there is no spaces use then length of the string */
671 IF L_end_ptr = 0 THEN
672 L_end_ptr := LENGTH(IN_parameter_list)+1;
673 END IF;
674
675 /* Did we find the token */
676 IF INSTR(IN_parameter_list, L_token_val) = 0 THEN
677 L_par_value := NULL;
678 ELSE
679 L_par_value := SUBSTR(IN_parameter_list,
680 L_start_ptr, L_end_ptr - L_start_ptr);
681 END IF;
682
683 RETURN L_par_value;
684
685 EXCEPTION
686 WHEN OTHERS THEN
687 --hr_utility.trace('Error: PAYUSUNB_PKG.fnc_get_parameter failed - ORA'||TO_CHAR(SQLCODE));
688 RAISE;
689 END fnc_get_parameter;
690
691 /*************************************************************************
692 routine name: prc_get_balance
693 purpose: Pulls all applicable balances for specified dimension,
694 tax type and juridiction
695 parameters:
696 return:
697 specs:
698 **************************************************************************/
699 PROCEDURE prc_get_balance(IN_asg_action_id IN NUMBER,
700 IN_tax_unit_id IN NUMBER,
701 IN_as_of_date IN DATE,
702 IN_dimension IN VARCHAR2,
703 IN_tax_type IN VARCHAR2,
704 IN_balance_type IN VARCHAR2,
705 IN_ee_or_er IN VARCHAR2,
706 IN_jurisdiction IN VARCHAR2,
707 OUT_bal OUT NOCOPY NUMBER) IS
708
709 L_rval NUMBER := 0;
710
711 BEGIN
712
713 L_rval := pay_us_tax_bals_pkg.us_tax_balance
714 (IN_balance_type,
715 IN_tax_type,
716 IN_ee_or_er,
717 IN_dimension,
718 'PER',
719 IN_tax_unit_id,
720 IN_jurisdiction,
721 IN_asg_action_id,
722 NULL,
723 NULL,
724 NULL,
725 TRUE);
726
727 IF L_rval IS NULL THEN
728 L_rval := 0;
729 END IF;
730
731 OUT_bal := L_rval;
732 EXCEPTION
733 WHEN NO_DATA_FOUND THEN
734 OUT_bal := 0;
735 WHEN OTHERS THEN
736 RAISE;
737 END prc_get_balance;
738
739 /*************************************************************************
740 routine name: fnc_get_tax_limit_rate
741 purpose: get tax limit rates in table PAY_US_STATE_TAX_INFO_F for
742 specified state and category
743 parameters:
744 return:
745 specs:
746 **************************************************************************/
747 FUNCTION fnc_get_tax_limit_rate(IN_state_code IN pay_us_states.state_code%TYPE,
748 IN_start_date IN DATE,
749 IN_as_of_date IN DATE,
750 IN_tax_type IN VARCHAR2,
751 IN_ee_or_er IN VARCHAR2,
752 IN_tab_flag IN VARCHAR2,
753 IN_tax_unit_id IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
754 L_return_val NUMBER;
755
756 /**********************3005756 START ****************************************/
757
758 -- Modified the function to cache the values and later use it instead of
759 -- hitting the database for each balance call
760
761 CURSOR c_sui_sdi_info IS
762 SELECT state_code,sta_information5,sta_information6,sta_information3,sta_information4
763 FROM pay_us_state_tax_info_f pusif
764 WHERE IN_as_of_date <= pusif.effective_end_date
765 AND IN_start_date >= pusif.effective_start_date
766 AND pusif.sta_information_category = 'State tax limit rate info'
767 ORDER BY 1;
768
769 l_sui_ee VARCHAR2(20);
770 l_sui_er VARCHAR2(20);
771 l_sdi_ee VARCHAR2(20);
772 l_sdi_er VARCHAR2(20);
773 lv_state_code VARCHAR2(2);
774
775 /********************** 3005756 END ************************************/
776
777 BEGIN
778
779 /************************ 3005756 START **************************************/
780 -- Check if the date passes is as_of_date and populate the pl/sql accordingly
781 -- If date = as_of_date populate the sui_sdi_tax_info1 table
782 -- else populate sui_sdi_tax_info2 table
783
784 IF IN_tab_flag = 'FULL' THEN
785 IF payusunb_pkg.sui_sdi_tax_info1.count < 1 THEN
786 OPEN c_sui_sdi_info ;
787 LOOP
788 FETCH c_sui_sdi_info into lv_state_code,l_sui_ee,l_sui_er,l_sdi_ee,l_sdi_er;
789 EXIT WHEN c_sui_sdi_info%NOTFOUND;
790 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sui_ee_limit := l_sui_ee;
791 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sui_er_limit := l_sui_er;
792 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sdi_ee_limit := l_sdi_ee;
793 payusunb_pkg.sui_sdi_tax_info1(lv_state_code).sdi_er_limit := l_sdi_er;
794 END LOOP;
795 CLOSE c_sui_sdi_info ;
796 END IF;
797
798 IF payusunb_pkg.sui_sdi_tax_info1.exists(IN_state_code) THEN
799
800 IF IN_tax_type = 'SUI' AND IN_ee_or_er = 'EE' THEN
801 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sui_ee_limit;
802 ELSIF IN_tax_type = 'SUI' AND IN_ee_or_er = 'ER' THEN
803 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sui_er_limit;
804 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'EE' THEN
805 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sdi_ee_limit;
806 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'ER' THEN
807 L_return_val := payusunb_pkg.sui_sdi_tax_info1(IN_state_code).sdi_er_limit;
808 END IF;
809
810 END IF;
811
812 ELSIF IN_tab_flag = 'FIRST' THEN
813
814 IF payusunb_pkg.sui_sdi_tax_info2.count < 1 THEN
815 OPEN c_sui_sdi_info ;
816 LOOP
817 FETCH c_sui_sdi_info into lv_state_code,l_sui_ee,l_sui_er,l_sdi_ee,l_sdi_er;
818 EXIT WHEN c_sui_sdi_info%NOTFOUND;
819 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sui_ee_limit := l_sui_ee;
820 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sui_er_limit := l_sui_er;
821 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sdi_ee_limit := l_sdi_ee;
822 payusunb_pkg.sui_sdi_tax_info2(lv_state_code).sdi_er_limit := l_sdi_er;
823 END LOOP;
824 CLOSE c_sui_sdi_info ;
825 END IF;
826
827 IF payusunb_pkg.sui_sdi_tax_info2.exists(IN_state_code) THEN
828
829 IF IN_tax_type = 'SUI' AND IN_ee_or_er = 'EE' THEN
830 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sui_ee_limit;
831 ELSIF IN_tax_type = 'SUI' AND IN_ee_or_er = 'ER' THEN
832 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sui_er_limit;
833 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'EE' THEN
834 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sdi_ee_limit;
835 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'ER' THEN
836 L_return_val := payusunb_pkg.sui_sdi_tax_info2(IN_state_code).sdi_er_limit;
837 END IF;
838
839 END IF;
840
841 ELSIF IN_tab_flag = 'LAST' THEN
842
843 IF payusunb_pkg.sui_sdi_tax_info3.count < 1 THEN
844 OPEN c_sui_sdi_info ;
845 LOOP
846 FETCH c_sui_sdi_info into lv_state_code,l_sui_ee,l_sui_er,l_sdi_ee,l_sdi_er;
847 EXIT WHEN c_sui_sdi_info%NOTFOUND;
848 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sui_ee_limit := l_sui_ee;
849 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sui_er_limit := l_sui_er;
850 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sdi_ee_limit := l_sdi_ee;
851 payusunb_pkg.sui_sdi_tax_info3(lv_state_code).sdi_er_limit := l_sdi_er;
852 END LOOP;
853 CLOSE c_sui_sdi_info ;
854 END IF;
855
856 IF payusunb_pkg.sui_sdi_tax_info3.exists(IN_state_code) THEN
857
858 IF IN_tax_type = 'SUI' AND IN_ee_or_er = 'EE' THEN
859 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sui_ee_limit;
860 ELSIF IN_tax_type = 'SUI' AND IN_ee_or_er = 'ER' THEN
861 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sui_er_limit;
862 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'EE' THEN
863 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sdi_ee_limit;
864 ELSIF IN_tax_type = 'SDI' AND IN_ee_or_er = 'ER' THEN
865 L_return_val := payusunb_pkg.sui_sdi_tax_info3(IN_state_code).sdi_er_limit;
866 END IF;
867
868 END IF;
869
870
871 END IF;
872
873 /**************************3005756 END ***********************************************/
874
875 IF L_return_val IS NULL THEN
876 L_return_val := 0.0;
877 END IF;
878
879 RETURN L_return_val;
880
881 END fnc_get_tax_limit_rate;
882
883 /****************************** 3005756 START ******************************/
884
885 -- New function to cache the futa credit rates in pl/sql tables .
886 -- Used in place of the cursor c_get_futa_credit_rt
887
888 FUNCTION fnc_get_futa_credit_rate(IN_organization_id IN per_all_assignments_f.organization_id%TYPE,
889 IN_sui_state_code IN VARCHAR2 ) RETURN NUMBER IS
890 L_return_val NUMBER;
891
892 CURSOR c_get_futa_credit_rt (IN_organization_id IN per_all_assignments_f.organization_id%TYPE) IS
893 SELECT org_information1,org_information15
894 FROM hr_organization_information
895 WHERE organization_id = IN_organization_id
896 AND org_information_context = 'State Tax Rules';
897
898 CURSOR c_get_state_code (IN_sui_state_code IN VARCHAR2) Is
899 SELECT state_abbrev
900 FROM pay_us_states
901 WHERE state_code = IN_sui_state_code;
902
903 l_flag VARCHAR2(2) := 'F' ;
904 l_count NUMBER := 0;
905 l_sui_state_code VARCHAR2(2);
906 l_futa_state_code VARCHAR2(10);
907 l_futa_credit_rate NUMBER;
908
909 BEGIN
910
911 l_count := payusunb_pkg.futa_credit_info.count;
912
913 OPEN c_get_state_code(IN_sui_state_code);
914 FETCH c_get_state_code into l_sui_state_code;
915 CLOSE c_get_state_code;
916
917 hr_utility.trace('L_count ' || to_char(l_count));
918 hr_utility.trace('Org Id ' || to_char(IN_organization_id));
919 IF l_count > 0 THEN
920 For i in 1..l_count
921 LOOP
922 hr_utility.trace('IN_sui_state_code : ' || IN_sui_state_code);
923 IF payusunb_pkg.futa_credit_info.exists(i) THEN
924 IF payusunb_pkg.futa_credit_info(i).organization_id = IN_organization_id THEN
925 l_flag := 'T' ;
926 IF (payusunb_pkg.futa_credit_info(i).sui_state_code = l_sui_state_code) THEN
927 L_return_val := payusunb_pkg.futa_credit_info(i).futa_credit_rate;
928 RETURN nvl(L_return_val,0);
929 END IF;
930 END IF;
931 END IF; -- exists
932 END LOOP;
933 END IF;
934 IF l_flag = 'F' THEN
935 OPEN c_get_futa_credit_rt ( IN_organization_id );
936 LOOP
937 l_count := l_count + 1;
938 FETCH c_get_futa_credit_rt INTO l_futa_state_code,l_futa_credit_rate ;
939 EXIT WHEN c_get_futa_credit_rt%NOTFOUND;
940 hr_utility.trace('State_code pupulated : ' || l_futa_state_code);
941 payusunb_pkg.futa_credit_info(l_count).organization_id := IN_organization_id;
942 payusunb_pkg.futa_credit_info(l_count).sui_state_code := l_futa_state_code;
943 payusunb_pkg.futa_credit_info(l_count).futa_credit_rate := NVL(l_futa_credit_rate,0);
944 IF l_futa_state_code = IN_sui_state_code THEN
945 L_return_val := nvl(l_futa_credit_rate,0);
946 END IF;
947
948 END LOOP;
949 CLOSE c_get_futa_credit_rt;
950 END IF;
951 hr_utility.trace('return value : ' || L_return_val);
952
953 RETURN nvl(L_return_val,0);
954
955 END fnc_get_futa_credit_rate;
956
957
958
959 -- New function to cache the sui and sdi override rates
960 -- Caches for the first time and returns the value later on
961
962 FUNCTION fnc_sui_sdi_override ( IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
963 ,IN_state_code IN VARCHAR2
964 ,IN_ret_flag IN VARCHAR2) RETURN NUMBER IS
965
966 L_return_val NUMBER;
967
968 CURSOR c_get_sui_sdi_overide_rt (IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE) IS
969 SELECT pus.state_code,org_information6/100, org_information7/100 , org_information14/100
970 FROM hr_organization_information org, pay_us_states pus
971 WHERE org.org_information1 = pus.state_abbrev
972 AND pus.state_code between 00 and 99
973 AND org.organization_id = IN_tax_unit_id
974 AND org.org_information_context = 'State Tax Rules';
975
976 ln_sui_override_rt NUMBER;
977 ln_sui_dummy_rt NUMBER;
978 ln_sdi_override_rt NUMBER;
979 lv_state_code VARCHAR2(2);
980 ln_count NUMBER;
981
982 BEGIN
983 IF payusunb_pkg.sui_sdi_override_info.count < 1 THEN
984 hr_utility.trace('Inside the sui_override');
985 OPEN c_get_sui_sdi_overide_rt ( IN_tax_unit_id );
986 LOOP
987 FETCH c_get_sui_sdi_overide_rt INTO lv_state_code,ln_sui_override_rt,ln_sui_dummy_rt,ln_sdi_override_rt ;
988 EXIT WHEN c_get_sui_sdi_overide_rt%NOTFOUND;
989 payusunb_pkg.sui_sdi_override_info(lv_state_code).sui_override_rate := ln_sui_override_rt;
990 payusunb_pkg.sui_sdi_override_info(lv_state_code).sui_dummy_rate := ln_sui_dummy_rt;
991 payusunb_pkg.sui_sdi_override_info(lv_state_code).sdi_override_rate := ln_sdi_override_rt;
992 END LOOP;
993 CLOSE c_get_sui_sdi_overide_rt ;
994 END IF;
995
996 IF payusunb_pkg.sui_sdi_override_info.exists(IN_state_code) THEN
997 IF IN_ret_flag = 'C' THEN -- calculated value
998 L_return_val := payusunb_pkg.sui_sdi_override_info(IN_state_code).sui_override_rate ;
999 ELSIF IN_ret_flag = 'D' THEN -- Dummy value
1000 L_return_val := payusunb_pkg.sui_sdi_override_info(IN_state_code).sui_dummy_rate ;
1001 ELSIF IN_ret_flag = 'SDI' THEN -- SDI value
1002 L_return_val := payusunb_pkg.sui_sdi_override_info(IN_state_code).sdi_override_rate ;
1003 END IF;
1004 END IF;
1005 RETURN L_return_val;
1006
1007 END fnc_sui_sdi_override ;
1008
1009
1010 /**************************************** 3005756 END ***************************************/
1011
1012 /*************************************************************************
1013 routine name: prc_write_data
1014 purpose: Write data to temp table PAY_US_RPT_TOTALS
1015 parameters: IN_record_type - 'V' record is part of tax verification
1016 - 'U' record is part of unacceptable
1017 IN_gre_id -
1018 IN_org_id -
1019 IN_location_id -
1020 IN_pact_id - PYUGEN payroll_action_id
1021 IN_chunk_number -
1022 IN_person_id -
1023 IN_balance_nm - String containing name of balance
1024 IN_taxable -
1025 IN_withheld -
1026 IN_calculated -
1027 IN_difference -
1028 IN_jurisdiction -
1029 IN_message - Corresponding message for each record
1030 IN_sort_code - Derived Jurisdiction code for sorting in report
1031 return: None
1032 specs: Below is the mapping that it used to write processed data to
1033 PAY_US_RPT_TOTALS table. There are two types of records, 1 is
1034 header record and the other is detail record.
1035 column mapping specs for header record:
1036 SESSION_ID := payroll_action_id (PYUGEN Payroll Action)
1037 TAX_UNIT_ID := tax_unit_id (from pay_assignment_actions)
1038 ORGANIZATION_ID := organization_id (from per_assignments_f)
1039 LOCATION_ID := location_id (from per_assignments_f)
1040 BUSINESS_GROUP_ID := chunk number from PYUGEN process
1041 VALUE1 := person_id
1042 GRE_NAME := assignment_number (from per_assignments_f)
1043 STATE_CODE := H indicating this record is header record
1044 VALUE6 := assignment_action_id
1045 Each header record may have multiple detail records and the key
1046 used to link header to detail records is assignment_action_id
1047 stored in value6 column.
1048 column mapping specs for detail record:
1049 SESSION_ID := payroll_action_id (PYUGEN Payroll Action)
1050 VALUE6 := assignment_action_id
1051 BUSINESS_GROUP_ID := chunk number from PYUGEN process
1052 STATE_NAME := jurisdiction_code
1053 STATE_CODE := U if row is data for Unacceptable Balance
1054 V if row is data for Taxable Verification
1055 ORGANIZATION_NAME := IF STATE_CODE = U THEN "Balance 1 Name"
1056 IF STATE_CODE = V THEN "Reported Balance Name"
1057 LOCATION_NAME := IF STATE_CODE = U THEN "Balance 2 Name"
1058 VALUE2 := IF STATE_CODE = U THEN "Balance 1 Name" Value
1059 IF STATE_CODE = V THEN "Tax Balance" Value
1060 VALUE3 := IF STATE_CODE = U THEN "Balance 2 Name" Value
1061 IF STATE_CODE = V THEN "Tax Withheld" Value
1062 VALUE4 := IF STATE_CODE = V THEN "Calculated Withheld" Value
1063 VALUE5 := IF STATE_CODE = V THEN Difference (Value3 - Value4)
1064 ATTRIBUTE1 := IF STATE_CODE = U THEN "Unacceptable Report" Message
1065 IF STATE_CODE = V THEN "Taxable Verification Report" Message
1066 ATTRIBUTE2 := Derived Jurisdiction Code for sorting in report
1067 **************************************************************************/
1068 PROCEDURE prc_write_data (IN_commit_count IN NUMBER,
1069 IN_record_type IN VARCHAR2,
1070 IN_asgn_action_id IN NUMBER,
1071 IN_gre_id IN NUMBER,
1072 IN_org_id IN NUMBER,
1073 IN_location_id IN NUMBER,
1074 IN_pact_id IN NUMBER,
1075 IN_chunk_number IN NUMBER,
1076 IN_person_id IN NUMBER,
1077 IN_assignment_no IN VARCHAR2,
1078 IN_balance_nm1 IN VARCHAR2,
1079 IN_balance_nm2 IN VARCHAR2,
1080 IN_taxable IN NUMBER,
1081 IN_withheld IN NUMBER,
1082 IN_calculated IN NUMBER,
1083 IN_difference IN NUMBER,
1084 IN_jurisdiction IN VARCHAR2,
1085 IN_message IN VARCHAR2,
1086 IN_sort_code IN VARCHAR2,
1087 IN_locked_asg_action_id IN NUMBER,
1088 IN_assign_id IN NUMBER) IS
1089
1090 L_jurisdiction VARCHAR2(30);
1091
1092 BEGIN
1093
1094 IF IN_jurisdiction IS NULL THEN
1095 L_jurisdiction := 'Federal';
1096 ELSE
1097 L_jurisdiction := IN_jurisdiction;
1098 END IF;
1099
1100 IF G_asgn_action_id IS NULL OR G_asgn_action_id <> IN_asgn_action_id THEN
1101
1102 -- if assignment_action_id changed then write new header record
1103 G_asgn_action_id := IN_asgn_action_id;
1104
1105 INSERT INTO pay_us_rpt_totals
1106 (state_code,
1107 tax_unit_id,
1108 organization_id,
1109 location_id,
1110 session_id,
1111 business_group_id,
1112 value1,
1113 gre_name,
1114 value6
1115 )
1116 VALUES
1117 ('H',
1118 IN_gre_id,
1119 IN_org_id,
1120 IN_location_id,
1121 IN_pact_id,
1122 IN_chunk_number,
1123 IN_person_id,
1124 IN_assignment_no,
1125 IN_asgn_action_id
1126 );
1127
1128 /******************************** 2963239 Change ***********************************************************/
1129
1130 -- insert the action record.
1131
1132 hr_nonrun_asact.insact(IN_asgn_action_id,IN_assign_id,IN_pact_id,IN_chunk_number,IN_gre_id);
1133
1134 -- Bug 3291736: Change the flag to Y as the assignment action is created
1135 G_dummy_action_inserted_flag := 'Y';
1136
1137 /******************************** END ******************************************************************/
1138
1139 END IF;
1140
1141 -- write data for taxable verification/unacceptable portion of report
1142 INSERT INTO pay_us_rpt_totals
1143 (state_code,
1144 tax_unit_id,
1145 session_id,
1146 business_group_id,
1147 organization_name,
1148 location_name, -- NULL
1149 value2,
1150 value3,
1151 value4,
1152 value5,
1153 value6,
1154 state_name,
1155 attribute1,
1156 attribute2
1157 )
1158 VALUES
1159 (IN_record_type,
1160 IN_gre_id,
1161 IN_pact_id,
1162 IN_chunk_number,
1163 IN_balance_nm1,
1164 IN_balance_nm2, -- NULL
1165 IN_taxable,
1166 IN_withheld,
1167 IN_calculated, -- NULL
1168 IN_difference, -- NULL
1169 IN_asgn_action_id,
1170 L_jurisdiction,
1171 IN_message,
1172 IN_sort_code
1173 );
1174
1175 G_commit_count := G_commit_count - 1;
1176 IF G_commit_count = 0 THEN
1177 COMMIT;
1178 G_commit_count := IN_commit_count;
1179 END IF;
1180 END prc_write_data;
1181
1182 /*************************************************************************
1183 routine name: fnc_sit_exists
1184 purpose: checks table pay_us_state_tax_info_f and return TRUE/FALSE
1185 based on value of column sit_exists.
1186 parameters: IN_state_code -
1187 IN_as_of_date -
1188 return: TRUE/FALSE
1189 specs:
1190 **************************************************************************/
1191 FUNCTION fnc_sit_exists(IN_state_code IN pay_us_states.state_code%TYPE,
1192 IN_as_of_date IN DATE) RETURN BOOLEAN IS
1193
1194 /********************* 3005756 START ************************************/
1195
1196 L_sit_exists pay_us_state_tax_info_f.sit_exists%TYPE;
1197
1198 BEGIN
1199
1200 IF pay_us_payroll_utils.ltr_state_tax_info.count < 1 THEN
1201 pay_us_payroll_utils.populate_jit_information ( p_effective_date => IN_as_of_date
1202 ,p_get_state => 'Y' );
1203 END IF;
1204
1205 IF pay_us_payroll_utils.ltr_state_tax_info.exists(IN_state_code) THEN
1206 L_sit_exists := pay_us_payroll_utils.ltr_state_tax_info(IN_state_code).sit_exists ;
1207 END IF;
1208
1209 /************************** 3005756 END ********************************************/
1210
1211 IF L_sit_exists = 'Y' THEN
1212 RETURN TRUE;
1213 ELSE
1214 RETURN FALSE;
1215 END IF;
1216 END fnc_sit_exists;
1217
1218 /*************************************************************************
1219 routine name: fnc_lit_tax_exists
1220 purpose: checks table pay_us_city_tax_info_f and return TRUE/FALSE
1221 based on value of column city_tax or school_tax flag
1222 depending on IN_lit string.
1223 IF IN_lit is 'COUNTY' then checks table pay_us_county_tax_info_f
1224 and return TRUE/FALSE based on value of column county_tax.
1225 parameters: IN_jurisdiction -
1226 IN_as_of_date -
1227 IN_lit - string contain 'CITY' or 'SCHOOL' or 'COUNTY'
1228 return: TRUE/FALSE
1229 specs:
1230 **************************************************************************/
1231 FUNCTION fnc_lit_tax_exists(IN_jurisdiction IN pay_us_county_tax_info_f.jurisdiction_code%TYPE,
1232 IN_as_of_date IN DATE,
1233 IN_lit IN VARCHAR2) RETURN BOOLEAN IS
1234
1235 /******************* 3005756 changes start *********************************/
1236
1237 CURSOR c_check_city IS
1238 SELECT city_tax,school_tax
1239 FROM pay_us_city_tax_info_f
1240 WHERE jurisdiction_code = IN_jurisdiction
1241 AND IN_as_of_date BETWEEN effective_start_date AND effective_end_date;
1242
1243
1244 CURSOR c_check_county IS
1245 SELECT county_tax,school_tax
1246 FROM pay_us_county_tax_info_f
1247 WHERE jurisdiction_code = IN_jurisdiction
1248 AND IN_as_of_date BETWEEN effective_start_date AND effective_end_date;
1249
1250
1251 L_jurisdiction_code VARCHAR2(11);
1252 L_city_tax VARCHAR2(1);
1253 L_school_tax VARCHAR2(1);
1254 L_county_tax VARCHAR2(1);
1255
1256 lv_state_code VARCHAR2(20);
1257 lv_county_code VARCHAR2(20);
1258 lv_city_code VARCHAR2(20);
1259 lv_temp_code VARCHAR2(20);
1260 ln_index_code NUMBER;
1261
1262 L_tax_flag VARCHAR2(1);
1263
1264 BEGIN
1265
1266 lv_state_code := substr(IN_jurisdiction,1,2);
1267 lv_county_code := substr(IN_jurisdiction,4,3);
1268 lv_city_code := substr(IN_jurisdiction,8,4);
1269
1270 lv_temp_code := lv_state_code||lv_county_code||lv_city_code;
1271 ln_index_code := to_number(lv_temp_code);
1272
1273
1274 IF IN_lit = 'CITY' THEN
1275 IF payusunb_pkg.city_tax_info.exists(ln_index_code) THEN
1276 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists;
1277 ELSE
1278 OPEN c_check_city ;
1279 FETCH c_check_city INTO L_city_tax,L_school_tax;
1280 payusunb_pkg.city_tax_info(ln_index_code).jurisdiction_code := IN_jurisdiction;
1281 payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists := L_city_tax;
1282 payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists := L_school_tax;
1283 CLOSE c_check_city;
1284 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists;
1285 END IF;
1286
1287 ELSIF IN_lit = 'SCHOOL' THEN
1288 IF payusunb_pkg.city_tax_info.exists(ln_index_code) THEN
1289 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists;
1290 ELSE
1291 OPEN c_check_city ;
1292 FETCH c_check_city INTO L_city_tax,L_school_tax;
1293 payusunb_pkg.city_tax_info(ln_index_code).jurisdiction_code := IN_jurisdiction;
1294 payusunb_pkg.city_tax_info(ln_index_code).city_tax_exists := L_city_tax;
1295 payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists := L_school_tax;
1296 CLOSE c_check_city;
1297 L_tax_flag := payusunb_pkg.city_tax_info(ln_index_code).city_sd_tax_exists;
1298 END IF;
1299
1300 ELSIF IN_lit = 'COUNTY' THEN
1301 IF payusunb_pkg.county_tax_info.exists(ln_index_code) THEN
1302 L_tax_flag := payusunb_pkg.county_tax_info(ln_index_code).cnty_tax_exists;
1303 ELSE
1304 OPEN c_check_county;
1305 FETCH c_check_county INTO L_county_tax,L_school_tax;
1306 payusunb_pkg.county_tax_info(ln_index_code).jurisdiction_code := IN_jurisdiction;
1307 payusunb_pkg.county_tax_info(ln_index_code).cnty_tax_exists := L_county_tax;
1308 payusunb_pkg.county_tax_info(ln_index_code).cnty_sd_tax_exists := L_school_tax;
1309 CLOSE c_check_county;
1310 L_tax_flag := payusunb_pkg.county_tax_info(ln_index_code).cnty_tax_exists;
1311 END IF;
1312
1313 END IF;
1314
1315 IF L_tax_flag = 'Y' THEN
1316 RETURN TRUE;
1317 ELSE
1318 RETURN FALSE;
1319 END IF;
1320
1321 /****************************** 3005756 Changes End ********************************/
1322
1323 END fnc_lit_tax_exists;
1324
1325
1326 /************************* 2963239 Change *******************************************/
1327
1328 /*************************************************************************
1329 routine name: prc_process_data
1330 purpose: Does the entire processing for unacceptable balance report
1331 and Dumps the data to table PAY_US_RPT_TOTALS
1332 parameters: IN_pact_id -
1333 IN_chunk_no -
1334 IN_commit_count -
1335 IN_lockingactid -
1336 IN_lockedactid -
1337 IN_assignment_id -
1338 IN_tax_unit_id -
1339 IN_person_id -
1340 IN_location_id -
1341 IN_organization_id -
1342 IN_assignment_number -
1343
1344 return: None
1345 specs:
1346 **************************************************************************/
1347 PROCEDURE prc_process_data(IN_pact_id IN pay_payroll_actions.payroll_action_id%TYPE,
1348 IN_chunk_no IN NUMBER,
1349 IN_commit_count IN NUMBER DEFAULT 1000,
1350 IN_prc_lockingactid IN pay_assignment_actions.assignment_action_id%TYPE,
1351 IN_prc_lockedactid IN pay_assignment_actions.assignment_action_id%TYPE,
1352 IN_prc_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1353 IN_prc_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1354 IN_prc_person_id IN per_all_assignments_f.person_id%TYPE,
1355 IN_prc_location_id IN per_all_assignments_f.location_id%TYPE,
1356 IN_prc_organization_id IN per_all_assignments_f.organization_id%TYPE,
1357 IN_prc_assignment_number IN per_all_assignments_f.assignment_number%TYPE ) IS
1358
1359 /****************************** END ***************************************************/
1360
1361
1362 /*************************** 3005756 start **********************************************/
1363 -- Run Balance cursors
1364 -- get all state level jurisdiction codes for specified person
1365 CURSOR c_state_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1366 IN_state_code IN VARCHAR2,
1367 IN_start_date IN DATE,
1368 IN_as_of_date IN DATE) IS
1369 SELECT DISTINCT
1370 prb.jurisdiction_code||'-000-0000' jurisdiction_code,
1371 pus.state_code state_code,
1372 pus.state_abbrev
1373 FROM pay_run_balances prb,
1374 per_assignments_f paf,
1375 pay_us_states pus
1376 WHERE paf.person_id = IN_person_id
1377 AND prb.effective_date BETWEEN IN_start_date and IN_as_of_date
1378 AND prb.effective_date BETWEEN paf.effective_start_date
1379 AND paf.effective_end_date
1380 AND prb.assignment_id = paf.assignment_id
1381 AND prb.jurisdiction_code = pus.state_code
1382 AND (pus.state_code = IN_state_code
1383 OR IN_state_code IS NULL);
1384
1385
1386 -- get all county level jurisdiction codes for specified person
1387 CURSOR c_county_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1388 IN_state_code IN VARCHAR2,
1389 IN_start_date IN DATE,
1390 IN_as_of_date IN DATE) IS
1391 SELECT DISTINCT
1392 prb.jurisdiction_code||'-0000' jurisdiction_code,
1393 puc.county_name||','||pus.state_abbrev jurisdiction_name
1394 FROM pay_run_balances prb,
1395 per_assignments_f paf,
1396 pay_us_states pus,
1397 pay_us_counties puc
1398 WHERE paf.person_id = IN_person_id
1399 AND paf.effective_start_date <= IN_as_of_date
1400 AND paf.effective_end_date >= IN_start_date
1401 AND prb.assignment_id = paf.assignment_id
1402 AND prb.effective_date BETWEEN paf.effective_start_date
1403 AND paf.effective_end_date
1404 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1405 AND pus.state_code = prb.jurisdiction_comp1
1406 AND (pus.state_code = IN_state_code
1407 OR IN_state_code IS NULL)
1408 AND prb.jurisdiction_code = puc.state_code||'-'||puc.county_code
1409 AND pus.state_code = puc.state_code;
1410
1411
1412 -- get all city level jurisdiction codes for specified person
1413 CURSOR c_city_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1414 IN_state_code IN VARCHAR2,
1415 IN_start_date IN DATE,
1416 IN_as_of_date IN DATE) IS
1417 SELECT DISTINCT
1418 prb.jurisdiction_code,
1419 pun.city_name||','||pus.state_abbrev jurisdiction_name
1420 FROM pay_run_balances prb,
1421 per_assignments_f paf,
1422 pay_us_states pus,
1423 pay_us_city_names pun
1424 WHERE paf.person_id = IN_person_id
1425 AND paf.effective_start_date <= IN_as_of_date
1426 AND paf.effective_end_date >= IN_start_date
1427 AND paf.assignment_id = prb.assignment_id
1428 AND prb.effective_date BETWEEN paf.effective_start_date
1429 AND paf.effective_end_date
1430 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1431 AND prb.jurisdiction_code =
1432 pun.state_code||'-'||pun.county_code||'-'||pun.city_code
1433 AND pun.primary_flag = 'Y'
1434 AND prb.jurisdiction_comp2 = pun.county_code
1435 AND prb.jurisdiction_comp3 = pun.city_code
1436 AND pun.state_code = pus.state_code
1437 AND (pus.state_code = IN_state_code
1438 OR IN_state_code IS NULL)
1439 AND pus.state_code = prb.jurisdiction_comp1;
1440
1441
1442 -- get all city level jurisdiction codes for specified person
1443 CURSOR c_school_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1444 IN_state_code IN VARCHAR2,
1445 IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1446 IN_start_date IN DATE,
1447 IN_as_of_date IN DATE) IS
1448 SELECT DISTINCT
1449 prb.jurisdiction_code,
1450 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1451 psd.state_code||'-'||psd.county_code||'-'||psd.city_code reg_jurisdiction_cd
1452 FROM pay_run_balances prb,
1453 per_assignments_f paf,
1454 pay_us_states pus,
1455 pay_us_city_school_dsts psd
1456 WHERE paf.person_id = IN_person_id
1457 AND paf.effective_start_date <= IN_as_of_date
1458 AND paf.effective_end_date >= IN_start_date
1459 AND paf.assignment_id = prb.assignment_id
1460 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1461 AND prb.effective_date BETWEEN paf.effective_start_date
1462 AND paf.effective_end_date
1463 AND prb.jurisdiction_code = psd.state_code||'-'||psd.school_dst_code
1464 AND prb.jurisdiction_comp2 = psd.school_dst_code
1465 AND prb.jurisdiction_comp1 = psd.state_code
1466 AND (pus.state_code = IN_state_code
1467 OR IN_state_code IS NULL)
1468 AND prb.jurisdiction_comp1 = pus.state_code
1469 AND pus.state_code = psd.state_code
1470 UNION ALL
1471 SELECT /*+ ORDERED */DISTINCT
1472 prb.jurisdiction_code,
1473 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1474 psd.state_code||'-'||psd.county_code||'-0000' reg_jurisdiction_cd
1475 FROM per_assignments_f paf,
1476 pay_run_balances prb,
1477 pay_us_states pus,
1478 pay_us_county_school_dsts psd
1479 WHERE paf.person_id = IN_person_id
1480 AND paf.effective_start_date <= IN_as_of_date
1481 AND paf.effective_end_date >= IN_start_date
1482 AND prb.assignment_id = paf.assignment_id
1483 AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
1484 AND prb.effective_date BETWEEN paf.effective_start_date
1485 AND paf.effective_end_date
1486 AND prb.jurisdiction_code = psd.state_code||'-'||psd.school_dst_code
1487 AND prb.jurisdiction_comp2 = psd.school_dst_code
1488 AND prb.jurisdiction_comp1 = psd.state_code
1489 AND (pus.state_code = IN_state_code
1490 OR IN_state_code IS NULL)
1491 AND prb.jurisdiction_comp1 = pus.state_code
1492 AND pus.state_code = psd.state_code;
1493
1494
1495 -- Original run result cursors
1496
1497
1498 -- get all state level jurisdiction codes for specified person
1499 CURSOR c_state_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
1500 IN_state_code IN VARCHAR2,
1501 IN_start_date IN DATE,
1502 IN_as_of_date IN DATE) IS
1503 SELECT DISTINCT
1504 pes.jurisdiction_code,
1505 pes.state_code,
1506 pus.state_abbrev
1507 FROM pay_us_emp_state_tax_rules_f pes,
1508 per_assignments_f paf,
1509 pay_us_states pus
1510 WHERE pes.assignment_id = paf.assignment_id
1511 AND pes.state_code = pus.state_code
1512 AND paf.effective_start_date BETWEEN pes.effective_start_date
1513 AND pes.effective_end_date
1514 /* Change for Performance Bug 6360505 */
1515 AND IN_as_of_date BETWEEN paf.effective_start_date
1516 AND paf.effective_end_date
1517 /* Change for Performance Bug 6360505 */
1518 AND IN_start_date BETWEEN paf.effective_start_date
1519 AND paf.effective_end_date
1520 AND paf.person_id = IN_person_id
1521 /* Change for Performance Bug 7174993 */
1522 AND (pus.state_code = IN_state_code
1523 OR IN_state_code IS NULL)
1524 -- AND pus.state_code = NVL(IN_state_code, pus.state_code)
1525 AND EXISTS (
1526 SELECT 'X'
1527 FROM pay_payroll_actions ppa,
1528 pay_assignment_actions paa,
1529 pay_run_results prr
1530 WHERE action_type IN ('B','I','R','Q','V')
1531 AND ppa.action_status = 'C'
1532 AND ppa.effective_date BETWEEN IN_start_date
1533 AND IN_as_of_date
1534 AND paa.payroll_action_id = ppa.payroll_action_id
1535 AND paa.assignment_id = pes.assignment_id
1536 AND prr.assignment_action_id = paa.assignment_action_id
1537 AND prr.jurisdiction_code = pes.jurisdiction_code
1538 AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
1539
1540
1541
1542 -- get all county level jurisdiction codes for specified person
1543 CURSOR c_county_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
1544 IN_state_code IN VARCHAR2,
1545 IN_start_date IN DATE,
1546 IN_as_of_date IN DATE) IS
1547 SELECT DISTINCT pes.jurisdiction_code,
1548 puc.county_name||','||pus.state_abbrev jurisdiction_name
1549 FROM pay_us_emp_county_tax_rules_f pes,
1550 per_assignments_f paf,
1551 pay_us_states pus,
1552 pay_us_counties puc
1553 WHERE pes.assignment_id = paf.assignment_id
1554 AND pes.state_code = pus.state_code
1555 AND pes.county_code = puc.county_code
1556 AND pes.state_code = puc.state_code
1557 AND paf.effective_start_date BETWEEN pes.effective_start_date
1558 AND pes.effective_end_date
1559 /* Change for Performance Bug 6360505 */
1560 AND IN_as_of_date BETWEEN paf.effective_start_date
1561 AND paf.effective_end_date
1562 /* Change for Performance Bug 6360505 */
1563 AND IN_start_date BETWEEN paf.effective_start_date
1564 AND paf.effective_end_date
1565 AND paf.person_id = IN_person_id
1566 /* Change for Performance Bug 7174993 */
1567 AND (pus.state_code = IN_state_code
1568 OR IN_state_code IS NULL)
1569 --AND pus.state_code = NVL(IN_state_code, pus.state_code)
1570 AND EXISTS (
1571 SELECT 'X'
1572 FROM pay_payroll_actions ppa,
1573 pay_assignment_actions paa,
1574 pay_run_results prr
1575 WHERE action_type IN ('B','I','R','Q','V')
1576 AND ppa.action_status = 'C'
1577 AND ppa.effective_date BETWEEN IN_start_date
1578 AND IN_as_of_date
1579 AND paa.payroll_action_id = ppa.payroll_action_id
1580 AND paa.assignment_id = pes.assignment_id
1581 AND prr.assignment_action_id = paa.assignment_action_id
1582 AND prr.jurisdiction_code = pes.jurisdiction_code
1583 AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
1584
1585
1586 -- get all city level jurisdiction codes for specified person
1587 CURSOR c_city_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
1588 IN_state_code IN VARCHAR2,
1589 IN_start_date IN DATE,
1590 IN_as_of_date IN DATE) IS
1591 SELECT DISTINCT
1592 pes.jurisdiction_code,
1593 pun.city_name||','||pus.state_abbrev jurisdiction_name
1594 FROM pay_us_emp_city_tax_rules_f pes,
1595 per_assignments_f paf,
1596 pay_us_states pus,
1597 pay_us_city_names pun
1598 WHERE pes.assignment_id = paf.assignment_id
1599 AND pes.state_code = pus.state_code
1600 AND pes.state_code = pun.state_code
1601 AND pes.county_code = pun.county_code
1602 AND pes.city_code = pun.city_code
1603 AND paf.effective_start_date BETWEEN pes.effective_start_date
1604 AND pes.effective_end_date
1605 AND pun.primary_flag = 'Y'
1606 /* Change for Performance Bug 6360505 */
1607 AND IN_as_of_date BETWEEN paf.effective_start_date
1608 AND paf.effective_end_date
1609 /* Change for Performance Bug 6360505 */
1610 AND IN_start_date BETWEEN paf.effective_start_date
1611 AND paf.effective_end_date
1612 AND paf.person_id = IN_person_id
1613 /* Change for Performance Bug 7174993 */
1614 AND (pus.state_code = IN_state_code
1615 OR IN_state_code IS NULL)
1616 -- AND pus.state_code = NVL(IN_state_code, pus.state_code)
1617 AND EXISTS (
1618 SELECT 'X'
1619 FROM pay_payroll_actions ppa,
1620 pay_assignment_actions paa,
1621 pay_run_results prr
1622 WHERE action_type IN ('B','I','R','Q','V')
1623 AND ppa.action_status = 'C'
1624 AND ppa.effective_date BETWEEN IN_start_date
1625 AND IN_as_of_date
1626 AND paa.payroll_action_id = ppa.payroll_action_id
1627 AND paa.assignment_id = pes.assignment_id
1628 AND prr.assignment_action_id = paa.assignment_action_id
1629 AND prr.jurisdiction_code = pes.jurisdiction_code
1630 AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
1631
1632
1633 -- get all city level jurisdiction codes for specified person
1634 CURSOR c_school_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
1635 IN_state_code IN VARCHAR2,
1636 IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1637 IN_start_date IN DATE,
1638 IN_as_of_date IN DATE) IS
1639 SELECT DISTINCT
1640 pes.state_code||'-'||pes.school_district_code jurisdiction_code,
1641 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1642 pes.jurisdiction_code reg_jurisdiction_cd
1643 FROM pay_us_emp_city_tax_rules_f pes,
1644 per_assignments_f paf,
1645 pay_us_states pus,
1646 pay_us_city_school_dsts psd
1647 WHERE pes.assignment_id = paf.assignment_id
1648 AND pes.school_district_code IS NOT NULL
1649 AND pes.state_code = pus.state_code
1650 AND pes.school_district_code = psd.school_dst_code
1651 AND pes.state_code = psd.state_code
1652 AND pes.county_code = psd.county_code
1653 AND pes.city_code = psd.city_code
1654 AND paf.effective_start_date BETWEEN pes.effective_start_date
1655 AND pes.effective_end_date
1656 /* Change for Performance Bug 6360505 */
1657 AND IN_as_of_date BETWEEN paf.effective_start_date
1658 AND paf.effective_end_date
1659 /* Change for Performance Bug 6360505 */
1660 AND IN_start_date BETWEEN paf.effective_start_date
1661 AND paf.effective_end_date
1662 AND paf.person_id = IN_person_id
1663 --AND pus.state_code = NVL(IN_state_code, pus.state_code)
1664 /* Change for Performance Bug 7174993 */
1665 AND (pus.state_code = IN_state_code
1666 OR IN_state_code IS NULL)
1667 AND EXISTS (
1668 SELECT 'X'
1669 FROM pay_payroll_actions ppa,
1670 pay_assignment_actions paa,
1671 pay_run_results prr
1672 WHERE action_type IN ('B','I','R','Q','V')
1673 AND ppa.action_status = 'C'
1674 AND ppa.effective_date BETWEEN IN_start_date
1675 AND IN_as_of_date
1676 AND paa.payroll_action_id = ppa.payroll_action_id
1677 AND paa.assignment_id = pes.assignment_id
1678 AND prr.assignment_action_id = paa.assignment_action_id
1679 AND prr.jurisdiction_code = pes.state_code||'-'||pes.school_district_code
1680 AND rownum = 1) -- Added rownum for perfromance enhancement (Bug 3316599)
1681 UNION
1682 SELECT DISTINCT
1683 pes.state_code||'-'||pes.school_district_code jurisdiction_code,
1684 psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1685 pes.jurisdiction_code reg_jurisdiction_cd
1686 FROM pay_us_emp_county_tax_rules_f pes,
1687 per_assignments_f paf,
1688 pay_us_states pus,
1689 pay_us_county_school_dsts psd
1690 WHERE pes.assignment_id = paf.assignment_id
1691 AND pes.school_district_code IS NOT NULL
1692 AND pes.state_code = pus.state_code
1693 AND pes.school_district_code = psd.school_dst_code
1694 AND pes.state_code = psd.state_code
1695 AND pes.county_code = psd.county_code
1696 AND paf.effective_start_date BETWEEN pes.effective_start_date
1697 AND pes.effective_end_date
1698 /* Change for Performance Bug 6360505 */
1699 AND IN_as_of_date BETWEEN paf.effective_start_date
1700 AND paf.effective_end_date
1701 /* Change for Performance Bug 6360505 */
1702 AND IN_start_date BETWEEN paf.effective_start_date
1703 AND paf.effective_end_date
1704 AND paf.person_id = IN_person_id
1705 --AND pus.state_code = NVL(IN_state_code, pus.state_code)
1706 /* Change for Performance Bug 7174993 */
1707 AND (pus.state_code = IN_state_code
1708 OR IN_state_code IS NULL)
1709 AND EXISTS (
1710 SELECT 'X'
1711 FROM pay_payroll_actions ppa,
1712 pay_assignment_actions paa,
1713 pay_run_results prr
1714 WHERE action_type IN ('B','I','R','Q','V')
1715 AND ppa.action_status = 'C'
1716 AND ppa.effective_date BETWEEN IN_start_date
1717 AND IN_as_of_date
1718 AND paa.payroll_action_id = ppa.payroll_action_id
1719 AND paa.assignment_id = pes.assignment_id
1720 AND prr.assignment_action_id = paa.assignment_action_id
1721 AND prr.jurisdiction_code = pes.state_code||'-'||pes.school_district_code
1722 AND rownum = 1); -- Added rownum for perfromance enhancement (Bug 3316599)
1723
1724 /*************************************** 3005756 end ****************************************************/
1725
1726 CURSOR c_get_sui_state_code (IN_business_id IN NUMBER,
1727 IN_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1728 IN_start_date IN DATE,
1729 IN_as_of_date IN DATE) IS
1730 SELECT NVL(sui_state_code,'00')
1731 FROM pay_us_emp_fed_tax_rules_f
1732 WHERE business_group_id = IN_business_id
1733 AND assignment_id = IN_assignment_id
1734 AND effective_start_date <= IN_start_date
1735 AND effective_end_date >= IN_as_of_date;
1736
1737 L_as_of_date DATE := NULL;
1738 L_start_date DATE ;
1739 L_leg_param pay_payroll_actions.legislative_parameters%TYPE;
1740 L_gre_id pay_assignment_actions.tax_unit_id%TYPE;
1741 L_org_id per_all_assignments_f.organization_id%TYPE;
1742 L_location_id per_all_assignments_f.location_id%TYPE;
1743 L_business_id per_all_assignments_f.business_group_id%TYPE;
1744 L_dimension VARCHAR2(20) := NULL;
1745 L_tax_type VARCHAR2(20) := NULL;
1746 L_tax_type_state VARCHAR2(20) := NULL;
1747 L_usr_SDI_ER_rate NUMBER := NULL;
1748 L_usr_SDI_EE_rate NUMBER := NULL;
1749 L_asg_action_id NUMBER ;
1750 L_first_half_date DATE ;
1751 L_sui_state_code VARCHAR2(2);
1752 L_calc_rate NUMBER := NULL;
1753 L_dummy_rate NUMBER ;
1754 L_futa_override_rt NUMBER := 0;
1755 L_futa_credit_rt NUMBER := 0;
1756 L_first_half_rate NUMBER ;
1757 L_second_half_rate NUMBER ;
1758 L_difference NUMBER ;
1759 L_calculated NUMBER ;
1760 L_medi_exempt VARCHAR2(1); -- added by tmehra
1761 -- for bug#1158217
1762
1763 -- FUTA balance variables
1764 L_futa_bal NUMBER := 0;
1765 L_futa_tax NUMBER := 0;
1766
1767 -- Medicare balance variables
1768 L_medi_ee_bal NUMBER := 0;
1769 L_medi_ee_tax NUMBER := 0;
1770 L_medi_er_bal NUMBER := 0;
1771 L_medi_er_tax NUMBER := 0;
1772 L_medi_er_liability NUMBER := 0;
1773
1774 -- SS balance variables
1775 L_ss_ee_bal NUMBER := 0;
1776 L_ss_ee_tax NUMBER := 0;
1777 L_ss_er_bal NUMBER := 0;
1778 L_ss_er_liability NUMBER := 0;
1779
1780 -- SUI balance variables(only for YTD )
1781 L_sui_ee_bal_first NUMBER := 0;
1782 L_sui_er_bal_first NUMBER := 0;
1783
1784 L_sum_sui_er_bal NUMBER := 0;
1785 L_sui_ee_tax NUMBER := 0;
1786 L_sui_ee_bal NUMBER := 0;
1787 L_sui_er_tax NUMBER := 0;
1788 L_sui_er_bal NUMBER := 0;
1789 L_sui_ee_subj_whable NUMBER := 0;
1790 L_sui_er_subj_whable NUMBER := 0;
1791
1792 -- SDI balance variables
1793 L_sdi_ee_bal NUMBER := 0;
1794 L_sdi_ee_tax NUMBER := 0;
1795 L_sdi_er_bal NUMBER := 0;
1796 L_sdi_er_tax NUMBER := 0;
1797 L_sum_sdi_ee_bal NUMBER := 0;
1798 L_sdi_ee_subj_whable NUMBER := 0;
1799 L_sdi_ee_subj_nwhable NUMBER := 0;
1800
1801 -- SIT balance variables
1802 L_sit_ee_subject NUMBER := 0;
1803 L_sit_ee_withheld NUMBER := 0;
1804 L_sit_ee_pretax_redns NUMBER := 0;
1805 L_sit_ee_subj_whable NUMBER := 0;
1806 L_sit_ee_subj_nwhable NUMBER := 0;
1807 L_sit_ee_reduced_s_whable NUMBER := 0;
1808
1809 --FIT balance variables
1810 L_fit_ee_gross_earnings NUMBER := 0;
1811 L_fit_ee_reduced_s_whable NUMBER := 0;
1812 L_fit_ee_tax NUMBER := 0;
1813 L_fit_ee_subject NUMBER := 0;
1814
1815 -- these balances are for deriving other fit balances
1816 L_fit_ee_subj_whable NUMBER := 0;
1817 L_fit_ee_subj_nwhable NUMBER := 0;
1818 L_fit_ee_pretax_redns NUMBER := 0;
1819
1820
1821 -- LIT City balance variables
1822 L_city_ee_tax NUMBER := 0;
1823 L_city_ee_subject NUMBER := 0;
1824 L_city_ee_r_s_whable NUMBER := 0;
1825 L_city_ee_s_whable NUMBER := 0;
1826 L_city_ee_s_nwhable NUMBER := 0;
1827
1828 -- LIT County balance variables
1829 L_county_ee_tax NUMBER := 0;
1830 L_county_ee_subject NUMBER := 0;
1831 L_county_ee_r_s_whable NUMBER := 0;
1832 L_county_ee_s_whable NUMBER := 0;
1833 L_county_ee_s_nwhable NUMBER := 0;
1834
1835 -- LIT School balance variables
1836 L_school_ee_tax NUMBER := 0;
1837 L_school_ee_subject NUMBER := 0;
1838 L_school_ee_r_s_whable NUMBER := 0;
1839 L_school_ee_s_whable NUMBER := 0;
1840 L_school_ee_s_nwhable NUMBER := 0;
1841
1842 /****************** Bug 2963239 Changes start ******************************************
1843 *** Flags for SS and medicare balances.Set the flags when the balances are fetched ******/
1844
1845 L_medi_ee_bal_flg VARCHAR2(1) := 'F';
1846 L_medi_er_bal_flg VARCHAR2(1) := 'F';
1847 L_ss_ee_bal_flg VARCHAR2(1) := 'F';
1848 L_ss_er_bal_flg VARCHAR2(1) := 'F';
1849
1850
1851
1852
1853 -- Message variables for prc_write_data
1854
1855 L_balance_nm1 VARCHAR2(150);
1856 L_balance_nm2 VARCHAR2(150);
1857 L_main_mesg VARCHAR2(150);
1858
1859 -----------------------------------------------
1860 --
1861 -- changes made be tmehra
1862 --
1863 FUNCTION f_check_medi_exempt(f_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1864 f_start_date IN DATE,
1865 f_as_of_date IN DATE) RETURN VARCHAR2 IS
1866
1867 CURSOR c_chk_medi_exempt(IN_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1868 IN_start_date IN DATE,
1869 IN_as_of_date IN DATE) IS
1870 SELECT medicare_tax_exempt
1871 FROM pay_us_emp_fed_tax_rules_v
1872 WHERE assignment_id = IN_assignment_id
1873 AND effective_start_date <= IN_start_date
1874 AND effective_end_date >= IN_as_of_date;
1875
1876 l_exempt_status VARCHAR2(1);
1877
1878 BEGIN
1879
1880 l_exempt_status := 'N';
1881
1882 FOR i in c_chk_medi_exempt (f_assignment_id,
1883 f_start_date,
1884 f_as_of_date)
1885 LOOP
1886 l_exempt_status := i.medicare_tax_exempt;
1887 END LOOP;
1888
1889 RETURN l_exempt_status;
1890
1891 END; -- end of function f_check_medi_exempt
1892
1893
1894 /******************************** 3005756 start ******************************************/
1895
1896 -- prc_federal_balances
1897 -- prc_state_balances
1898 -- prc_county_balances
1899 -- prc_city_balances
1900 -- prc_school_balances
1901
1902
1903 PROCEDURE prc_federal_balances
1904 IS
1905 BEGIN
1906 IF L_tax_type IS NULL OR (L_tax_type <> 'SIT' AND L_tax_type <> 'LIT') THEN
1907 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
1908 L_as_of_date, L_dimension, 'FIT', 'GROSS', 'EE',
1909 NULL, L_fit_ee_gross_earnings);
1910
1911 END IF;
1912
1913 -- The following balance will be required if tax type is FIT or Medicare
1914 IF (L_tax_type = 'FIT' OR L_tax_type = 'Medicare' OR L_tax_type = 'SIT' OR L_tax_type IS NULL)
1915 AND L_medi_exempt = 'N' THEN
1916
1917 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
1918 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'EE',
1919 NULL, L_medi_ee_bal);
1920 L_medi_ee_bal_flg := 'T' ;
1921
1922 -- added new pre-tax balance - tmehra
1923 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
1924 L_as_of_date,L_dimension, 'FIT', 'PRE_TAX_REDNS', 'EE',
1925 NULL, L_fit_ee_pretax_redns);
1926
1927 END IF;
1928
1929 -- if tax_type is anything but Medicare, SS, FUTA,
1930 -- then get subj whable balance for later use
1931 IF (L_tax_type <> 'Medicare' AND L_tax_type <> 'SS' AND L_tax_type <> 'FUTA')
1932 OR L_tax_type IS NULL THEN
1933 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
1934 L_as_of_date, L_dimension, 'FIT', 'SUBJ_WHABLE', 'EE',
1935 NULL, L_fit_ee_subj_whable);
1936 END IF;
1937
1938 IF L_tax_type = 'FIT' OR L_tax_type = 'SIT' OR L_tax_type IS NULL THEN
1939
1940 L_fit_ee_reduced_s_whable := L_fit_ee_subj_whable - L_fit_ee_pretax_redns;
1941
1942 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
1943 L_as_of_date, L_dimension, 'FIT', 'SUBJ_NWHABLE', 'EE',
1944 NULL, L_fit_ee_subj_nwhable);
1945
1946 L_fit_ee_subject := L_fit_ee_subj_whable + L_fit_ee_subj_nwhable;
1947
1948 END IF;
1949
1950 IF L_tax_type = 'FIT' OR L_tax_type IS NULL THEN
1951
1952 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
1953 L_as_of_date, L_dimension, 'FIT', 'WITHHELD', 'EE',
1954 NULL, L_fit_ee_tax);
1955
1956 -- d)
1957 IF L_fit_ee_subj_whable < L_fit_ee_reduced_s_whable THEN
1958
1959 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
1960 L_balance_nm2 := L_dimension || ' FIT Reduced Subject Withholdable';
1961 L_main_mesg := '*** ' || L_dimension ||' FIT Subject Withholdable < ' || L_dimension ||
1962 ' FIT Reduced Subject '||'Withholdable ***';
1963
1964 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
1965 IN_prc_tax_unit_id,
1966 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
1967 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
1968 L_balance_nm1,
1969 L_balance_nm2,
1970 L_fit_ee_subj_whable, L_fit_ee_reduced_s_whable,
1971 NULL, NULL, NULL,
1972 L_main_mesg,
1973 '00-000-0000',
1974 L_asg_action_id,
1975 IN_prc_assignment_id );
1976 END IF;
1977
1978
1979 -- c)
1980 IF L_fit_ee_subj_whable <= 0 AND L_fit_ee_tax > 0 THEN
1981
1982 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
1983 L_balance_nm2 := L_dimension || ' FIT Withheld';
1984 L_main_mesg := '*** ' || L_dimension ||' FIT Subject Withholdable <= 0 but ' ||
1985 L_dimension || ' FIT '||'Withheld > 0 ***';
1986
1987 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
1988 IN_prc_tax_unit_id,
1989 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
1990 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
1991 L_balance_nm1,
1992 L_balance_nm2,
1993 L_fit_ee_subj_whable, L_fit_ee_tax,
1994 NULL, NULL, NULL,
1995 L_main_mesg,
1996 '00-000-0000',
1997 L_asg_action_id ,
1998 IN_prc_assignment_id );
1999 END IF;
2000
2001 -- b)
2002 IF L_fit_ee_reduced_s_whable <= 0 AND L_fit_ee_tax > 0 THEN
2003
2004 L_balance_nm1 := L_dimension || ' FIT Reduced Subject Withholdable';
2005 L_balance_nm2 := L_dimension || ' FIT Withheld';
2006 L_main_mesg := '*** ' || L_dimension || ' FIT Reduced Subject Withholdable <= 0 but '||
2007 L_dimension || ' FIT '||'Withheld > 0 ***';
2008
2009 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2010 IN_prc_tax_unit_id,
2011 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2012 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2013 L_balance_nm1,
2014 L_balance_nm2,
2015 L_fit_ee_reduced_s_whable, L_fit_ee_tax,
2016 NULL, NULL, NULL,
2017 L_main_mesg,
2018 '00-000-0000',
2019 L_asg_action_id ,
2020 IN_prc_assignment_id );
2021 END IF;
2022
2023 -- a)
2024 IF L_fit_ee_gross_earnings < L_fit_ee_reduced_s_whable THEN
2025
2026 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2027 L_balance_nm2 := L_dimension || ' FIT Reduced Subject Withholdable';
2028 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < ' || L_dimension ||
2029 ' FIT Reduced Subject '||'Withholdable ***';
2030
2031 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2032 IN_prc_tax_unit_id,
2033 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2034 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2035 L_balance_nm1,
2036 L_balance_nm2,
2037 L_fit_ee_gross_earnings, L_fit_ee_reduced_s_whable,
2038 NULL, NULL, NULL,
2039 L_main_mesg,
2040 '00-000-0000',
2041 L_asg_action_id ,
2042 IN_prc_assignment_id );
2043 END IF;
2044
2045
2046 END IF; -- end if 'FIT'
2047
2048 -- Pull all federal level applicable FUTA balances
2049 IF L_tax_type = 'FUTA' OR L_tax_type IS NULL THEN
2050
2051 -- all applicable balances will be pulled beginning with Federal
2052 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2053 L_as_of_date, L_dimension, 'FUTA', 'TAXABLE', 'ER', NULL, L_futa_bal);
2054
2055 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2056 L_as_of_date, L_dimension, 'FUTA', 'LIABILITY', 'ER', NULL, L_futa_tax);
2057
2058
2059 /***************************** 3005756 START *********************************/
2060
2061 -- Value is fetched into the global variable in action_creation
2062
2063 L_futa_override_rt := G_futa_override_rt ;
2064
2065 /************************************** 3005756 END ******************************/
2066
2067 IF L_futa_override_rt = 0 THEN
2068 BEGIN
2069 OPEN c_get_sui_state_code(L_business_id, IN_prc_assignment_id,
2070 L_start_date, L_as_of_date);
2071 FETCH c_get_sui_state_code INTO L_sui_state_code;
2072 CLOSE c_get_sui_state_code;
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 L_sui_state_code := '00';
2076 END;
2077
2078
2079
2080
2081 IF L_sui_state_code <> '00' THEN
2082 -- find for futa tax credit only if state found
2083
2084 /**************************3005756 START ************************************/
2085
2086 L_futa_credit_rt := fnc_get_futa_credit_rate( IN_prc_organization_id, L_sui_state_code );
2087
2088 /*********************** 3005656 END ******************************************/
2089
2090 ELSE
2091 L_futa_credit_rt := 0;
2092 END IF;
2093
2094 L_calc_rate := (c_fixed_futa_rt - TO_NUMBER(L_futa_credit_rt))/100;
2095 ELSE
2096 L_calc_rate := L_futa_override_rt;
2097 END IF;
2098
2099 IF L_dimension = 'QTD' THEN
2100 L_calculated := L_futa_bal * L_calc_rate;
2101 ELSE
2102 L_calculated := ROUND(L_futa_bal * L_calc_rate,2);
2103 END IF;
2104
2105
2106 IF ABS(L_futa_tax - L_calculated) > 0.1 THEN
2107
2108 L_difference := L_futa_tax - L_calculated; --Bug 3115988
2109 L_balance_nm1 := L_dimension || ' FUTA Taxable';
2110 L_balance_nm2 := NULL;
2111 L_main_mesg := '*** FUTA ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of FUTA ER Taxable Balance ***';
2112
2113 -- significant different found, write to tmp file for report
2114 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2115 IN_prc_tax_unit_id,
2116 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2117 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2118 L_balance_nm1,
2119 L_balance_nm2,
2120 L_futa_bal, L_futa_tax,
2121 L_calculated, L_difference, NULL,
2122 L_main_mesg,
2123 '00-000-0000',
2124 L_asg_action_id,
2125 IN_prc_assignment_id );
2126 END IF;
2127
2128
2129 -- e)
2130 IF L_fit_ee_gross_earnings < L_futa_bal THEN
2131
2132 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2133 L_balance_nm2 := L_dimension || ' FUTA Taxable';
2134 L_main_mesg := '*** '|| L_dimension || ' FIT Gross Earnings < ' || L_dimension || ' FUTA Taxable ***';
2135
2136 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2137 IN_prc_tax_unit_id,
2138 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2139 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2140 L_balance_nm1,
2141 L_balance_nm2,
2142 L_fit_ee_gross_earnings, L_futa_bal,
2143 NULL, NULL, NULL,
2144 L_main_mesg,
2145 '00-000-0000',
2146 L_asg_action_id,
2147 IN_prc_assignment_id );
2148 END IF;
2149
2150
2151
2152 END IF; -- end if 'FUTA'
2153
2154
2155 -- Pull all federal level applicable Medicare balances
2156 -- tmehra added the L_medi_exempt condition
2157 IF (L_tax_type = 'Medicare' OR L_tax_type IS NULL) AND L_medi_exempt = 'N' THEN
2158 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2159 L_as_of_date, L_dimension, 'MEDICARE', 'WITHHELD', 'EE',
2160 NULL, L_medi_ee_tax);
2161
2162 L_calculated := ROUND(L_medi_ee_bal * G_medi_ee_rate,2);
2163
2164 IF ABS(L_medi_ee_tax - L_calculated) > 0.1 THEN
2165
2166 L_difference := L_medi_ee_tax - L_calculated;
2167 -- significant different found, write to tmp file for report
2168
2169 L_balance_nm1 := L_dimension || ' MEDICARE EE Taxable';
2170 L_balance_nm2 := NULL;
2171 L_main_mesg := '*** Medicare Withheld does not = '||TO_CHAR(G_medi_ee_rate*100)||'% of Taxable Balance ***';
2172 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2173 IN_prc_tax_unit_id,
2174 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2175 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2176 L_balance_nm1,
2177 L_balance_nm2,
2178 L_medi_ee_bal,
2179 L_medi_ee_tax,
2180 L_calculated, L_difference, NULL,
2181 L_main_mesg,
2182 '00-000-0000',
2183 L_asg_action_id,
2184 IN_prc_assignment_id );
2185 END IF;
2186
2187 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2188 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'ER',
2189 NULL, L_medi_er_bal);
2190
2191 /********************* Bug 2963239 changes start : Set flag *******************************/
2192
2193 L_medi_er_bal_flg := 'T' ;
2194
2195 /******************** Bug 2963239 Changes End ******************************************/
2196
2197 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2198 L_as_of_date, L_dimension, 'MEDICARE', 'WITHHELD', 'ER',
2199 NULL, L_medi_er_tax);
2200
2201 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2202 L_as_of_date, L_dimension, 'MEDICARE', 'LIABILITY', 'ER',
2203 NULL, L_medi_er_liability);
2204
2205 L_calculated := ROUND(L_medi_er_bal * G_medi_er_rate,2);
2206
2207 IF ABS(L_medi_er_tax - L_calculated) > 0.1 THEN
2208
2209 L_difference := L_medi_er_tax - L_calculated;
2210 L_balance_nm1 := L_dimension || ' MEDICARE ER Taxable';
2211 L_balance_nm2 := NULL;
2212 L_main_mesg := '*** Medicare Withheld does not = '||TO_CHAR(G_medi_er_rate*100)||'% of Taxable Balance ***';
2213
2214 -- significant different found, write to tmp file for report
2215 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2216 IN_prc_tax_unit_id,
2217 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2218 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2219 L_balance_nm1,
2220 L_balance_nm2,
2221 L_medi_er_bal,
2222 L_medi_er_tax,
2223 L_calculated, L_difference, NULL,
2224 '*** Medicare Withheld does not = '||TO_CHAR(G_medi_er_rate*100)||'% of Taxable Balance ***',
2225 '00-000-0000',
2226 L_asg_action_id ,
2227 IN_prc_assignment_id );
2228 END IF;
2229
2230
2231 -- g)
2232 IF L_medi_ee_tax <> L_medi_er_liability THEN
2233
2234 L_balance_nm1 := L_dimension || ' Medicare EE Withheld';
2235 L_balance_nm2 := L_dimension || ' Medicare ER Liability';
2236 L_main_mesg := '*** ' || L_dimension || ' Medicare EE Withheld does not = ' ||L_dimension ||
2237 ' Medicare ER Liability ***';
2238
2239 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2240 IN_prc_tax_unit_id,
2241 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2242 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2243 L_balance_nm1,
2244 L_balance_nm2,
2245 L_medi_ee_tax, L_medi_er_liability,
2246 NULL, NULL, NULL,
2247 L_main_mesg,
2248 '00-000-0000',
2249 L_asg_action_id,
2250 IN_prc_assignment_id );
2251 END IF;
2252
2253
2254 -- f)
2255 IF L_fit_ee_gross_earnings < L_medi_ee_bal THEN
2256
2257 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2258 L_balance_nm2 := L_dimension || ' Medicare EE Taxable';
2259 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < ' || L_dimension || ' Medicare EE Taxable ***';
2260
2261 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2262 IN_prc_tax_unit_id,
2263 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2264 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2265 L_balance_nm1,
2266 L_balance_nm2,
2267 L_fit_ee_gross_earnings, L_medi_ee_bal,
2268 NULL, NULL, NULL,
2269 L_main_mesg,
2270 '00-00-0000',
2271 L_asg_action_id,
2272 IN_prc_assignment_id );
2273 END IF;
2274
2275
2276 END IF; -- end if 'Medicare'
2277
2278
2279 -- Pull all federal level applicable Social Security balances
2280 IF L_tax_type = 'SS' OR L_tax_type IS NULL THEN
2281
2282 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2283 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'EE', NULL, L_ss_ee_bal);
2284
2285 /********************* Bug 2963239 changes start : Set flag *******************************/
2286
2287 L_ss_ee_bal_flg := 'T' ;
2288
2289 /******************** Bug 2963239 Changes End ******************************************/
2290
2291 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2292 L_as_of_date, L_dimension, 'SS', 'WITHHELD', 'EE', NULL, L_ss_ee_tax);
2293
2294 L_calculated := ROUND(L_ss_ee_bal * G_ss_ee_rate,2);
2295
2296 IF ABS(L_ss_ee_tax - L_calculated) > 0.1 THEN
2297
2298 L_difference := L_ss_ee_tax - L_calculated;
2299 L_balance_nm1 := L_dimension || ' SS EE Taxable';
2300 L_balance_nm2 := NULL;
2301 L_main_mesg := '*** SS Withheld does not = '||TO_CHAR(G_ss_ee_rate*100)||'% of Taxable Balance ***';
2302 -- significant different found, write to tmp file for report
2303 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2304 IN_prc_tax_unit_id,
2305 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2306 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2307 L_balance_nm1,
2308 L_balance_nm2,
2309 L_ss_ee_bal,L_ss_ee_tax,
2310 L_calculated, L_difference, NULL,
2311 L_main_mesg,
2312 '00-000-0000',
2313 L_asg_action_id,
2314 IN_prc_assignment_id );
2315 END IF;
2316
2317 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2318 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'ER', NULL, L_ss_er_bal);
2319
2320
2321 /********************* Bug 2963239 changes start : Set flag *******************************/
2322
2323 L_ss_er_bal_flg := 'T' ;
2324
2325 /******************** Bug 2963239 Changes End ******************************************/
2326
2327 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2328 L_as_of_date, L_dimension, 'SS', 'LIABILITY', 'ER',
2329 NULL, L_ss_er_liability);
2330
2331 L_calculated := ROUND(L_ss_er_bal * G_ss_er_rate,2);
2332
2333
2334 IF ABS(L_ss_er_liability - L_calculated) > 0.1 THEN
2335
2336 L_difference := L_ss_er_liability - L_calculated;
2337 -- significant different found, write to tmp file for report
2338 L_balance_nm1 := L_dimension || ' SS ER Taxable';
2339 L_balance_nm2 := NULL;
2340 L_main_mesg := '*** SS Withheld does not = '||TO_CHAR(G_ss_er_rate*100)||'% of Taxable Balance ***';
2341 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2342 IN_prc_tax_unit_id,
2343 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2344 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2345 L_balance_nm1,
2346 L_balance_nm2,
2347 L_ss_er_bal, L_ss_er_liability,
2348 L_calculated, L_difference, NULL,
2349 L_main_mesg,
2350 '00-000-0000',
2351 L_asg_action_id,
2352 IN_prc_assignment_id );
2353 END IF;
2354
2355 -- i)
2356 IF L_ss_ee_tax <> L_ss_er_liability THEN
2357
2358 L_balance_nm1 := L_dimension || ' SS EE Withheld';
2359 L_balance_nm2 := L_dimension || ' SS ER Liability';
2360 L_main_mesg := '*** ' || L_dimension || ' SS EE Withheld does not = ' || L_dimension || ' SS ER Liability ***';
2361
2362 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2363 IN_prc_tax_unit_id,
2364 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2365 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2366 L_balance_nm1,
2367 L_balance_nm2,
2368 L_ss_ee_tax, L_ss_er_liability,
2369 NULL, NULL, NULL,
2370 L_main_mesg,
2371 '00-000-0000',
2372 L_asg_action_id,
2373 IN_prc_assignment_id );
2374 END IF;
2375
2376
2377 -- h)
2378 IF L_fit_ee_gross_earnings < L_ss_ee_bal THEN
2379
2380 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2381 L_balance_nm2 := L_dimension || ' SS EE Taxable';
2382 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < ' || L_dimension || ' SS EE Taxable ***';
2383
2384 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2385 IN_prc_tax_unit_id,
2386 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2387 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2388 L_balance_nm1,
2389 L_balance_nm2,
2390 L_fit_ee_gross_earnings, L_ss_ee_bal,
2391 NULL, NULL, NULL,
2392 L_main_mesg,
2393 '00-000-0000',
2394 L_asg_action_id,
2395 IN_prc_assignment_id );
2396 END IF;
2397
2398
2399 END IF; -- end if 'SS'
2400
2401
2402 /********************* Bug 2963239 Changes start: Extra check **********************************/
2403
2404 IF L_tax_type = 'SS' or L_tax_type = 'Medicare' or L_tax_type IS NULL THEN
2405
2406 IF L_medi_ee_bal_flg = 'F' THEN
2407 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2408 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'EE',
2409 NULL, L_medi_ee_bal);
2410 END IF;
2411 IF L_medi_er_bal_flg = 'F' THEN
2412 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2413 L_as_of_date, L_dimension, 'MEDICARE', 'TAXABLE', 'ER',
2414 NULL, L_medi_er_bal);
2415 END IF;
2416 IF L_ss_ee_bal_flg = 'F' THEN
2417 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2418 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'EE', NULL, L_ss_ee_bal);
2419 END IF;
2420 IF L_ss_er_bal_flg = 'F' THEN
2421 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2422 L_as_of_date, L_dimension, 'SS', 'TAXABLE', 'ER', NULL, L_ss_er_bal);
2423 END IF;
2424
2425
2426 IF L_ss_ee_bal > L_medi_ee_bal THEN
2427
2428 L_balance_nm1 := L_dimension || ' MEDICARE EE Taxable';
2429 L_balance_nm2 := L_dimension || ' SS EE Taxable';
2430 L_main_mesg := '*** ' || L_dimension || ' SS EE Taxable > ' || L_dimension || ' MEDICARE EE Taxable ***' ;
2431
2432 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2433 IN_prc_tax_unit_id,
2434 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2435 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2436 L_balance_nm1,
2437 L_balance_nm2,
2438 L_medi_ee_bal, L_ss_ee_bal,
2439 NULL, NULL, NULL,
2440 L_main_mesg,
2441 '00-000-0000',
2442 L_asg_action_id,
2443 IN_prc_assignment_id );
2444 END IF;
2445
2446 IF L_ss_er_bal > L_medi_er_bal THEN
2447
2448 L_balance_nm1 := L_dimension || ' MEDICARE ER Taxable';
2449 L_balance_nm2 := L_dimension || ' SS ER Taxable';
2450 L_main_mesg := '*** ' || L_dimension || ' SS ER Taxable > ' || L_dimension || ' MEDICARE ER Taxable ***';
2451
2452 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2453 IN_prc_tax_unit_id,
2454 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2455 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2456 L_balance_nm1,
2457 L_balance_nm2,
2458 L_medi_er_bal, L_ss_er_bal,
2459 NULL, NULL, NULL,
2460 L_main_mesg,
2461 '00-000-0000',
2462 L_asg_action_id,
2463 IN_prc_assignment_id );
2464 END IF;
2465
2466 END IF; -- ss or medicare
2467
2468 END prc_federal_balances ;
2469
2470
2471
2472 PROCEDURE prc_state_balances ( curr_jurisdiction_code IN VARCHAR2
2473 , curr_state_code IN VARCHAR2
2474 , curr_state_abbrev IN VARCHAR2)
2475 IS
2476 BEGIN
2477 IF L_tax_type = 'SIT' OR L_tax_type IS NULL THEN
2478 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2479 L_as_of_date, L_dimension, 'SIT', 'WITHHELD', 'EE',
2480 curr_jurisdiction_code,
2481 L_sit_ee_withheld);
2482
2483 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2484 L_as_of_date, L_dimension, 'SIT', 'SUBJ_WHABLE', 'EE',
2485 curr_jurisdiction_code,
2486 L_sit_ee_subj_whable);
2487
2488 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2489 L_as_of_date, L_dimension, 'SIT', 'SUBJ_NWHABLE', 'EE',
2490 curr_jurisdiction_code,
2491 L_sit_ee_subj_nwhable);
2492
2493 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2494 L_as_of_date,L_dimension, 'SIT', 'PRE_TAX_REDNS', 'EE',
2495 curr_jurisdiction_code,
2496 L_sit_ee_pretax_redns);
2497
2498 L_sit_ee_subject := L_sit_ee_subj_whable + L_sit_ee_subj_nwhable;
2499 L_sit_ee_reduced_s_whable := L_sit_ee_subj_whable - L_sit_ee_pretax_redns;
2500
2501 -- j)
2502 IF L_sit_ee_subj_whable <= 0 AND L_sit_ee_withheld > 0 THEN
2503
2504 L_balance_nm1 := L_dimension || ' SIT Subject Withholdable';
2505 L_balance_nm2 := L_dimension || ' SIT Withheld';
2506 L_main_mesg := '*** ' || L_dimension || ' SIT Subject Withholdable <= 0 and ' || L_dimension ||
2507 ' SIT Withheld > 0 ***';
2508
2509 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2510 IN_prc_tax_unit_id,
2511 IN_prc_organization_id, IN_prc_location_id,
2512 IN_pact_id,
2513 IN_chunk_no, IN_prc_person_id,
2514 IN_prc_assignment_number,
2515 L_balance_nm1,
2516 L_balance_nm2,
2517 L_sit_ee_subj_whable, L_sit_ee_withheld,
2518 NULL, NULL, curr_state_abbrev,
2519 L_main_mesg,
2520 curr_state_code||'-000-0000',
2521 L_asg_action_id ,
2522 IN_prc_assignment_id );
2523 END IF;
2524
2525
2526 -- l)
2527 IF L_sit_ee_subj_whable < L_sit_ee_reduced_s_whable THEN
2528
2529 L_balance_nm1 := L_dimension || ' SIT Subject Withholdable';
2530 L_balance_nm2 := L_dimension || ' SIT Reduced Subject Withholdable';
2531 L_main_mesg := '*** ' || L_dimension || ' SIT Subject Withholdable < ' || L_dimension ||
2532 ' SIT Reduced Subject Withholdable ***';
2533
2534 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2535 IN_prc_tax_unit_id,
2536 IN_prc_organization_id, IN_prc_location_id,
2537 IN_pact_id,
2538 IN_chunk_no, IN_prc_person_id,
2539 IN_prc_assignment_number,
2540 L_balance_nm1,
2541 L_balance_nm2,
2542 L_sit_ee_subj_whable, L_sit_ee_reduced_s_whable,
2543 NULL, NULL, curr_state_abbrev,
2544 L_main_mesg,
2545 curr_state_code||'-000-0000',
2546 L_asg_action_id,
2547 IN_prc_assignment_id );
2548 END IF;
2549
2550
2551 -- o)
2552 IF L_fit_ee_subject < L_sit_ee_subject THEN
2553
2554 L_balance_nm1 := L_dimension || ' FIT Subject';
2555 L_balance_nm2 := L_dimension || ' SIT Subject';
2556 L_main_mesg := '*** ' || L_dimension || ' FIT Subject < ' || L_dimension || ' SIT Subject ***';
2557
2558 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2559 IN_prc_tax_unit_id,
2560 IN_prc_organization_id, IN_prc_location_id,
2561 IN_pact_id,
2562 IN_chunk_no, IN_prc_person_id,
2563 IN_prc_assignment_number,
2564 L_balance_nm1,
2565 L_balance_nm2,
2566 L_fit_ee_subject, L_sit_ee_subject,
2567 NULL, NULL, curr_state_abbrev,
2568 L_main_mesg,
2569 curr_state_code||'-000-0000',
2570 L_asg_action_id ,
2571 IN_prc_assignment_id );
2572 END IF;
2573
2574
2575 -- k)
2576 IF L_fit_ee_subj_whable <= 0 AND L_sit_ee_withheld > 0 THEN
2577
2578 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
2579 L_balance_nm2 := L_dimension || ' SIT Withheld';
2580 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
2581 ' SIT Withheld > 0 ***';
2582 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2583 IN_prc_tax_unit_id,
2584 IN_prc_organization_id, IN_prc_location_id,
2585 IN_pact_id,
2586 IN_chunk_no, IN_prc_person_id,
2587 IN_prc_assignment_number,
2588 L_balance_nm1,
2589 L_balance_nm2,
2590 L_fit_ee_subj_whable, L_sit_ee_withheld,
2591 NULL, NULL, curr_state_abbrev,
2592 L_main_mesg,
2593 curr_state_code||'-000-0000',
2594 L_asg_action_id ,
2595 IN_prc_assignment_id );
2596 END IF;
2597
2598
2599 -- p)
2600 IF L_sit_ee_withheld > 0 AND NOT
2601 fnc_sit_exists(curr_state_code, L_as_of_date)
2602 THEN
2603
2604 L_balance_nm1 := L_dimension || ' SIT Withheld';
2605 L_balance_nm2 := ' ';
2606 L_main_mesg := '*** ' || L_dimension || ' SIT Withheld > 0 when state has no SIT '||'withholding rule ***';
2607
2608 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2609 IN_prc_tax_unit_id,
2610 IN_prc_organization_id, IN_prc_location_id,
2611 IN_pact_id,
2612 IN_chunk_no, IN_prc_person_id,
2613 IN_prc_assignment_number,
2614 L_balance_nm1,
2615 L_balance_nm2,
2616 L_sit_ee_withheld, NULL,
2617 NULL, NULL, curr_state_abbrev,
2618 L_main_mesg,
2619 curr_state_code||'-000-0000',
2620 L_asg_action_id ,
2621 IN_prc_assignment_id );
2622 END IF;
2623
2624
2625 END IF; -- end if 'SIT'
2626
2627
2628 -- don't bother checking if state is Hawaii or New York
2629 IF (L_tax_type = 'SDI' OR L_tax_type IS NULL) AND
2630 curr_state_code NOT IN ('12','33')
2631 THEN
2632 -- first get the rate, if user specified rate exists then use it, otherwise ...
2633 IF L_usr_SDI_EE_rate IS NOT NULL THEN
2634 L_calc_rate := L_usr_SDI_EE_rate/100;
2635 ELSE
2636 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code,
2637 L_start_date, L_as_of_date,
2638 'SDI', 'EE','FULL');
2639 END IF;
2640
2641 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2642 L_as_of_date, L_dimension, 'SDI', 'SUBJ_WHABLE', 'EE',
2643 curr_jurisdiction_code,
2644 L_sdi_ee_subj_whable);
2645
2646 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2647 L_as_of_date, L_dimension, 'SDI', 'SUBJ_NWHABLE', 'EE',
2648 curr_jurisdiction_code,
2649 L_sdi_ee_subj_nwhable);
2650
2651 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2652 L_as_of_date, L_dimension, 'SDI', 'TAXABLE', 'EE',
2653 curr_jurisdiction_code,
2654 L_sdi_ee_bal);
2655
2656 L_sum_sdi_ee_bal := L_sum_sdi_ee_bal + L_sdi_ee_bal;
2657
2658
2659 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2660 L_as_of_date, L_dimension, 'SDI', 'WITHHELD', 'EE',
2661 curr_jurisdiction_code,
2662 L_sdi_ee_tax);
2663
2664 L_calculated := ROUND(L_sdi_ee_bal * L_calc_rate,2);
2665
2666
2667 IF ABS(L_sdi_ee_tax - L_calculated) > 0.1 THEN
2668
2669 L_difference := L_sdi_ee_tax - L_calculated;
2670 -- significant different found, write to tmp file for report
2671 L_balance_nm1 := L_dimension || ' SDI EE Taxable';
2672 L_balance_nm2 := NULL;
2673 L_main_mesg := '*** SDI EE Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SDI EE Taxable Balance ***';
2674
2675 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2676 IN_prc_tax_unit_id,
2677 IN_prc_organization_id, IN_prc_location_id,
2678 IN_pact_id,
2679 IN_chunk_no, IN_prc_person_id,
2680 IN_prc_assignment_number,
2681 L_balance_nm1,
2682 L_balance_nm2,
2683 L_sdi_ee_bal, L_sdi_ee_tax,
2684 L_calculated, L_difference, curr_state_abbrev,
2685 L_main_mesg,
2686 curr_state_code||'-000-0000',
2687 L_asg_action_id,
2688 IN_prc_assignment_id );
2689 END IF;
2690
2691
2692 L_calc_rate := NULL;
2693 IF L_usr_SDI_ER_rate IS NOT NULL THEN
2694 L_calc_rate := L_usr_SDI_ER_rate/100;
2695 ELSE
2696
2697 /****************************** 3005756 START ****************************************************/
2698
2699 L_calc_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'SDI' );
2700
2701 /************************************* 3005756 END *******************************************/
2702
2703 IF L_calc_rate IS NULL THEN
2704 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
2705 L_as_of_date, 'SDI', 'ER','FULL');
2706 END IF;
2707 END IF;
2708
2709 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2710 L_as_of_date, L_dimension, 'SDI', 'TAXABLE', 'ER',
2711 curr_jurisdiction_code,
2712 L_sdi_er_bal);
2713
2714 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2715 L_as_of_date, L_dimension, 'SDI', 'LIABILITY', 'ER',
2716 curr_jurisdiction_code,
2717 L_sdi_er_tax);
2718
2719
2720 L_calculated := ROUND(L_sdi_er_bal * L_calc_rate,2);
2721
2722
2723 IF ABS(L_sdi_er_tax - L_calculated) > 0.1 THEN
2724
2725 L_difference := L_sdi_er_tax - L_calculated;
2726 -- significant different found, write to tmp file for report
2727 L_balance_nm1 := L_dimension || ' SDI ER Taxable';
2728 L_balance_nm2 := NULL;
2729 L_main_mesg := '*** SDI ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SDI ER Taxable Balance ***';
2730
2731 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2732 IN_prc_tax_unit_id,
2733 IN_prc_organization_id, IN_prc_location_id,
2734 IN_pact_id,
2735 IN_chunk_no, IN_prc_person_id,
2736 IN_prc_assignment_number,
2737 L_balance_nm1,
2738 L_balance_nm2,
2739 L_sdi_er_bal, L_sdi_er_tax,
2740 L_calculated, L_difference, curr_state_abbrev,
2741 L_main_mesg,
2742 curr_state_code||'-000-0000',
2743 L_asg_action_id,
2744 IN_prc_assignment_id );
2745 END IF;
2746
2747
2748 -- u)
2749 IF L_sdi_ee_subj_whable <= 0 AND L_sdi_ee_tax > 0 THEN
2750
2751 L_balance_nm1 := L_dimension || ' SDI EE Subject Withholdable';
2752 L_balance_nm2 := L_dimension || ' SDI EE Withheld';
2753 L_main_mesg := '*** ' || L_dimension || ' SDI EE Subject Withholdable <= 0 and ' || L_dimension ||
2754 ' SDI EE Withheld > 0 ***';
2755
2756 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2757 IN_prc_tax_unit_id,
2758 IN_prc_organization_id, IN_prc_location_id,
2759 IN_pact_id,
2760 IN_chunk_no, IN_prc_person_id,
2761 IN_prc_assignment_number,
2762 L_balance_nm1,
2763 L_balance_nm2,
2764 L_sdi_ee_subj_whable, L_sdi_ee_tax,
2765 NULL, NULL, curr_state_abbrev,
2766 L_main_mesg,
2767 curr_state_code||'-000-0000',
2768 L_asg_action_id,
2769 IN_prc_assignment_id );
2770 END IF;
2771
2772
2773 -- v)
2774 IF L_fit_ee_subj_whable <= 0 AND L_sdi_ee_tax > 0 THEN
2775
2776 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
2777 L_balance_nm2 := 'YTD SDI EE Withheld';
2778 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
2779 ' SDI EE Withheld > 0 ***';
2780
2781 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2782 IN_prc_tax_unit_id,
2783 IN_prc_organization_id, IN_prc_location_id,
2784 IN_pact_id,
2785 IN_chunk_no, IN_prc_person_id,
2786 IN_prc_assignment_number,
2787 L_balance_nm1,
2788 L_balance_nm2,
2789 L_fit_ee_subj_whable, L_sdi_ee_tax,
2790 NULL, NULL, curr_state_abbrev,
2791 L_main_mesg,
2792 curr_state_code||'-000-0000',
2793 L_asg_action_id,
2794 IN_prc_assignment_id );
2795 END IF;
2796
2797
2798 -- v)
2799 IF L_fit_ee_subj_whable <= 0 AND L_sdi_er_tax > 0 THEN
2800
2801 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
2802 L_balance_nm2 := L_dimension || ' SDI ER Liability';
2803 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
2804 ' SDI ER Withheld > 0 ***';
2805
2806 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2807 IN_prc_tax_unit_id,
2808 IN_prc_organization_id, IN_prc_location_id,
2809 IN_pact_id,
2810 IN_chunk_no, IN_prc_person_id,
2811 IN_prc_assignment_number,
2812 L_balance_nm1,
2813 L_balance_nm2,
2814 L_fit_ee_subj_whable, L_sdi_er_tax,
2815 NULL, NULL, curr_state_abbrev,
2816 L_main_mesg,
2817 curr_state_code||'-000-0000',
2818 L_asg_action_id,
2819 IN_prc_assignment_id );
2820 END IF;
2821
2822
2823 -- t)
2824 IF L_fit_ee_gross_earnings < L_sum_sdi_ee_bal THEN
2825
2826 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
2827 L_balance_nm2 := 'TOTAL ' || L_dimension || ' SDI EE Taxable';
2828 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < TOTAL ' || L_dimension ||
2829 ' SDI EE Taxable ***';
2830
2831 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
2832 IN_prc_tax_unit_id,
2833 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
2834 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
2835 L_balance_nm1,
2836 L_balance_nm2,
2837 L_fit_ee_gross_earnings, L_sum_sdi_ee_bal,
2838 NULL, NULL, NULL,
2839 L_main_mesg,
2840 '00-000-0000',
2841 L_asg_action_id,
2842 IN_prc_assignment_id );
2843 END IF;
2844
2845
2846 END IF; -- end if 'SDI'
2847
2848
2849
2850 IF L_tax_type = 'SUI' OR L_tax_type IS NULL THEN
2851 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
2852 L_as_of_date, 'SUI', 'EE','FULL');
2853
2854 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2855 L_as_of_date, L_dimension, 'SUI', 'TAXABLE', 'EE',
2856 curr_jurisdiction_code,
2857 L_sui_ee_bal);
2858
2859 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2860 L_as_of_date, L_dimension, 'SUI', 'WITHHELD', 'EE',
2861 curr_jurisdiction_code,
2862 L_sui_ee_tax);
2863
2864
2865 IF L_dimension = 'QTD' THEN
2866
2867 /******************* QTD **************************************/
2868
2869 L_calculated := ROUND(L_sui_ee_bal * L_calc_rate,2);
2870
2871 IF ABS(L_sui_ee_tax - L_calculated) > 0.1 THEN
2872
2873 L_difference := L_sui_ee_tax - L_calculated;
2874 -- significant different found, write to tmp file for report
2875 L_balance_nm1 := 'QTD SUI EE Taxable';
2876 L_balance_nm2 := NULL;
2877 L_main_mesg := '*** SUI EE Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI EE Taxable Balance ***';
2878
2879 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2880 IN_prc_tax_unit_id,
2881 IN_prc_organization_id, IN_prc_location_id,
2882 IN_pact_id,
2883 IN_chunk_no, IN_prc_person_id,
2884 IN_prc_assignment_number,
2885 L_balance_nm1,
2886 L_balance_nm2,
2887 L_sui_ee_bal, L_sui_ee_tax,
2888 L_calculated, L_difference, curr_state_abbrev,
2889 L_main_mesg,
2890 curr_state_code||'-000-0000',
2891 L_asg_action_id ,
2892 IN_prc_assignment_id );
2893 END IF;
2894
2895
2896
2897 -- Now do the ER SUI portion
2898 -- First check if SUI override rate is entered by user
2899 L_calc_rate := NULL;
2900
2901 /********************************** 3005756 START *******************************************/
2902
2903 L_calc_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id, curr_state_code , 'C' );
2904 L_dummy_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id, curr_state_code , 'D' );
2905
2906 /********************************** 3005756 END **********************************************/
2907
2908
2909 IF L_calc_rate IS NULL THEN
2910 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
2911 L_as_of_date, 'SUI', 'ER','FULL');
2912 END IF;
2913
2914 /******************************* QTD *********************************************/
2915
2916 END IF; -- QTD
2917
2918 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2919 L_as_of_date, L_dimension, 'SUI', 'TAXABLE', 'ER',
2920 curr_jurisdiction_code,
2921 L_sui_er_bal);
2922
2923 L_sum_sui_er_bal := L_sum_sui_er_bal + L_sui_er_bal;
2924
2925
2926 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2927 L_as_of_date, L_dimension, 'SUI', 'LIABILITY', 'ER',
2928 curr_jurisdiction_code,
2929 L_sui_er_tax);
2930
2931 IF L_dimension = 'QTD' THEN
2932
2933 /************************** QTD ********************************/
2934 L_calculated := ROUND(L_sui_er_bal * L_calc_rate,2);
2935
2936 IF ABS(L_sui_er_tax - L_calculated) > 0.1 THEN
2937
2938 L_difference := L_sui_er_tax - L_calculated;
2939 -- significant different found, write to tmp file for report
2940 L_balance_nm1 := 'QTD SUI ER Taxable';
2941 L_balance_nm2 := NULL ;
2942 L_main_mesg := '*** SUI ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI ER Taxable Balance ***';
2943
2944 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
2945 IN_prc_tax_unit_id,
2946 IN_prc_organization_id, IN_prc_location_id,
2947 IN_pact_id,
2948 IN_chunk_no, IN_prc_person_id,
2949 IN_prc_assignment_number,
2950 L_balance_nm1,
2951 L_balance_nm2,
2952 L_sui_er_bal, L_sui_er_tax,
2953 L_calculated, L_difference, curr_state_abbrev,
2954 L_main_mesg,
2955 curr_state_code||'-000-0000',
2956 L_asg_action_id,
2957 IN_prc_assignment_id );
2958 END IF;
2959
2960 /************************ QTD ***********************************/
2961
2962 END IF; -- QTD
2963
2964 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2965 L_as_of_date, L_dimension, 'SUI', 'SUBJ_WHABLE', 'EE',
2966 curr_jurisdiction_code,
2967 L_sui_ee_subj_whable);
2968
2969
2970 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
2971 L_as_of_date, L_dimension, 'SUI', 'SUBJ_WHABLE', 'ER',
2972 curr_jurisdiction_code,
2973 L_sui_er_subj_whable);
2974
2975 IF L_dimension = 'YTD' THEN
2976
2977 /****************** YTD *************************************/
2978
2979 -- only do this if state is New Hampshire, New Jersey, Tennessee, Vermont and
2980 -- dimension is YTD
2981 IF curr_state_code IN (30, 31, 43, 46) AND
2982 L_as_of_date > L_first_half_date THEN
2983
2984 -- get the rates from jan 1 to end of june and for july 1 to as_of_date
2985 L_first_half_rate := fnc_get_tax_limit_rate(curr_state_code,
2986 L_start_date, L_first_half_date, 'SUI', 'EE','FIRST');
2987
2988 L_second_half_rate := fnc_get_tax_limit_rate(curr_state_code,
2989 L_first_half_date+1, L_as_of_date, 'SUI', 'EE','LAST');
2990
2991 -- get ee balance for first 6 months
2992 BEGIN
2993 L_sui_ee_bal_first := pay_us_tax_bals_pkg.us_tax_balance
2994 ('TAXABLE',
2995 'SUI',
2996 'EE',
2997 'YTD',
2998 'ASG',
2999 IN_prc_tax_unit_id,
3000 curr_jurisdiction_code,
3001 NULL,
3002 L_asg_action_id,
3003 L_first_half_date,
3004 NULL,
3005 TRUE);
3006 EXCEPTION
3007 WHEN NO_DATA_FOUND THEN
3008 L_sui_ee_bal_first := 0;
3009 WHEN OTHERS THEN
3010 RAISE;
3011 END;
3012 -- now combine first and second half to make complete ee balance
3013 L_calculated := ROUND((L_sui_ee_bal_first * L_first_half_rate)
3014 + ((L_sui_ee_bal-L_sui_ee_bal_first)
3015 *L_second_half_rate),2);
3016
3017 -- now check if ee difference is erroneous
3018 IF ABS(L_sui_ee_tax - L_calculated) > 0.1 THEN
3019
3020 L_difference := L_sui_ee_tax - L_calculated;
3021 -- significant different found, write to tmp file for report
3022 L_balance_nm1 := 'YTD SUI EE Taxable';
3023 L_balance_nm2 := NULL;
3024 L_main_mesg := '*** SUI EE Liability does not = 1st half year '||
3025 TO_CHAR(L_first_half_rate*100)||'%, 2nd half year '
3026 ||TO_CHAR(L_second_half_rate*100)||'% of SUI EE Taxable Balance ***';
3027
3028 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3029 IN_prc_tax_unit_id,
3030 IN_prc_organization_id,
3031 IN_prc_location_id, IN_pact_id,
3032 IN_chunk_no, IN_prc_person_id,
3033 IN_prc_assignment_number,
3034 L_balance_nm1,
3035 L_balance_nm2,
3036 L_sui_ee_bal, L_sui_ee_tax,
3037 L_calculated, L_difference, curr_state_abbrev,
3038 L_main_mesg,
3039 curr_state_code||'-000-0000',
3040 L_asg_action_id,
3041 IN_prc_assignment_id );
3042 END IF;
3043
3044 -- now do the ER portion
3045 -- First check if SUI override rate is entered by user
3046
3047 /************************************* 3005756 START ****************************************/
3048
3049 L_first_half_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'C' );
3050 L_second_half_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'D' );
3051
3052 /************************************* 3005756 END *******************************************/
3053
3054 IF L_first_half_rate IS NULL THEN
3055 L_first_half_rate := fnc_get_tax_limit_rate(curr_state_code,
3056 L_start_date, L_first_half_date, 'SUI', 'ER','FIRST');
3057 END IF;
3058
3059 IF L_second_half_rate IS NULL THEN
3060 L_second_half_rate := fnc_get_tax_limit_rate(curr_state_code,
3061 L_first_half_date+1, L_as_of_date, 'SUI', 'ER','LAST');
3062 END IF;
3063
3064 -- get er balance for first 6 months
3065 BEGIN
3066 L_sui_er_bal_first := pay_us_tax_bals_pkg.us_tax_balance
3067 ('TAXABLE',
3068 'SUI',
3069 'ER',
3070 'YTD',
3071 'ASG',
3072 IN_prc_tax_unit_id,
3073 curr_jurisdiction_code,
3074 NULL,
3075 L_asg_action_id,
3076 L_first_half_date,
3077 NULL,
3078 TRUE);
3079 EXCEPTION
3080 WHEN NO_DATA_FOUND THEN
3081 L_sui_er_bal_first := 0;
3082 WHEN OTHERS THEN
3083 RAISE;
3084 END;
3085
3086 -- now combine first and second half to make complete er balance
3087 L_calculated := ROUND((L_sui_er_bal_first * L_first_half_rate)
3088 + ((L_sui_er_bal - L_sui_er_bal_first)
3089 *L_second_half_rate),2);
3090
3091 -- now check if ee difference is erroneous
3092 IF ABS(L_sui_er_tax - L_calculated) > 0.1 THEN
3093
3094 L_difference := L_sui_er_tax - L_calculated;
3095 -- significant different found, write to tmp file for report
3096 L_balance_nm1 := 'YTD SUI ER Taxable';
3097 L_balance_nm2 := NULL;
3098 L_main_mesg := '*** SUI ER Liability does not = 1st 6 month rate '||
3099 TO_CHAR(L_first_half_rate*100)||
3100 '%, last 6 month rate '||
3101 TO_CHAR(L_second_half_rate*100)||
3102 '% of SUI ER Taxable Balance ***';
3103
3104 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3105 IN_prc_tax_unit_id,
3106 IN_prc_organization_id,
3107 IN_prc_location_id, IN_pact_id,
3108 IN_chunk_no, IN_prc_person_id,
3109 IN_prc_assignment_number,
3110 L_balance_nm1,
3111 L_balance_nm2,
3112 L_sui_er_bal, L_sui_er_tax,
3113 L_calculated, L_difference, curr_state_abbrev,
3114 L_main_mesg,
3115 curr_state_code||'-000-0000',
3116 L_asg_action_id,
3117 IN_prc_assignment_id );
3118 END IF;
3119 ELSE
3120 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
3121 L_as_of_date, 'SUI', 'EE','FULL');
3122
3123 L_calculated := ROUND(L_sui_ee_bal * L_calc_rate,2);
3124
3125 IF ABS(L_sui_ee_tax - L_calculated) > 0.1 THEN
3126
3127 L_difference := L_sui_ee_tax - L_calculated;
3128 -- significant different found, write to tmp file for report
3129 L_balance_nm1 := 'YTD SUI EE Taxable';
3130 L_balance_nm2 := NULL;
3131 L_main_mesg := '*** SUI EE Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI EE Taxable Balance ***';
3132
3133 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3134 IN_prc_tax_unit_id,
3135 IN_prc_organization_id,
3136 IN_prc_location_id, IN_pact_id,
3137 IN_chunk_no, IN_prc_person_id,
3138 IN_prc_assignment_number,
3139 L_balance_nm1,
3140 L_balance_nm2,
3141 L_sui_ee_bal, L_sui_ee_tax,
3142 L_calculated, L_difference, curr_state_abbrev,
3143 L_main_mesg,
3144 curr_state_code||'-000-0000',
3145 L_asg_action_id,
3146 IN_prc_assignment_id );
3147 END IF;
3148
3149 -- Now do the ER portion
3150 -- First check if SUI override rate is entered by user
3151 L_calc_rate := NULL;
3152
3153
3154 /*********************************** 3005756 START ****************************************/
3155
3156 L_calc_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'C' );
3157 L_dummy_rate := fnc_sui_sdi_override( IN_prc_tax_unit_id , curr_state_code ,'D' );
3158
3159 /************************************* 3005756 END *******************************************/
3160
3161 IF L_calc_rate IS NULL THEN
3162 L_calc_rate := fnc_get_tax_limit_rate(curr_state_code, L_start_date,
3163 L_as_of_date, 'SUI', 'ER','FULL');
3164 END IF;
3165
3166 L_calculated := ROUND(L_sui_er_bal * L_calc_rate,2);
3167
3168 IF ABS(L_sui_er_tax - L_calculated) > 0.1 THEN
3169
3170 L_difference := L_sui_er_tax - L_calculated;
3171 -- significant different found, write to tmp file for report
3172 L_balance_nm1 := 'YTD SUI ER Taxable';
3173 L_balance_nm2 := NULL;
3174 L_main_mesg := '*** SUI ER Liability does not = '||TO_CHAR(L_calc_rate*100)||'% of SUI ER Taxable Balance ***';
3175
3176 prc_write_data(IN_commit_count,'V', IN_prc_lockingactid,
3177 IN_prc_tax_unit_id,
3178 IN_prc_organization_id,
3179 IN_prc_location_id, IN_pact_id,
3180 IN_chunk_no, IN_prc_person_id,
3181 IN_prc_assignment_number,
3182 L_balance_nm1,
3183 L_balance_nm2,
3184 L_sui_er_bal, L_sui_er_tax,
3185 L_calculated, L_difference, curr_state_abbrev,
3186 L_main_mesg,
3187 curr_state_code||'-000-0000',
3188 L_asg_action_id,
3189 IN_prc_assignment_id );
3190 END IF;
3191
3192 END IF; -- end if curr_state_code IN (30, 31, 43, 46)
3193
3194 /****************** YTD ****************************************/
3195
3196 END IF; -- YTD
3197
3198 -- q)
3199 IF L_sui_ee_subj_whable <= 0 AND L_sui_ee_tax > 0 THEN
3200
3201 L_balance_nm1 := L_dimension || ' SUI EE Subject Withholdable';
3202 L_balance_nm2 := L_dimension || ' SUI EE Withheld';
3203 L_main_mesg := '*** ' || L_dimension || ' SUI EE Subject Withholdable <= 0 and ' || L_dimension ||
3204 ' SUI EE Withheld > 0 ***';
3205
3206 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3207 IN_prc_tax_unit_id,
3208 IN_prc_organization_id, IN_prc_location_id,
3209 IN_pact_id,
3210 IN_chunk_no, IN_prc_person_id,
3211 IN_prc_assignment_number,
3212 L_balance_nm1,
3213 L_balance_nm2,
3214 L_sui_ee_subj_whable, L_sui_ee_tax,
3215 NULL, NULL, curr_state_abbrev,
3216 L_main_mesg,
3217 curr_state_code||'-000-0000',
3218 L_asg_action_id,
3219 IN_prc_assignment_id );
3220 END IF;
3221
3222 -- q)
3223 IF L_sui_er_subj_whable <= 0 AND L_sui_er_tax > 0 THEN
3224
3225 L_balance_nm1 := L_dimension || ' SUI ER Subject Withholdable';
3226 L_balance_nm2 := L_dimension || ' SUI ER Withheld';
3227 L_main_mesg := '*** ' || L_dimension || ' SUI ER Subject Withholdable <= 0 and ' || L_dimension ||
3228 ' SUI ER Withheld > 0 ***';
3229
3230 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3231 IN_prc_tax_unit_id,
3232 IN_prc_organization_id, IN_prc_location_id,
3233 IN_pact_id,
3234 IN_chunk_no, IN_prc_person_id,
3235 IN_prc_assignment_number,
3236 L_balance_nm1,
3237 L_balance_nm2,
3238 L_sui_er_subj_whable, L_sui_er_tax,
3239 NULL, NULL, curr_state_abbrev,
3240 L_main_mesg,
3241 curr_state_code||'-000-0000',
3242 L_asg_action_id,
3243 IN_prc_assignment_id );
3244 END IF;
3245
3246
3247 -- s)
3248 IF L_fit_ee_subj_whable <= 0 AND L_sui_ee_tax > 0 THEN
3249
3250 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3251 L_balance_nm2 := L_dimension || ' SUI EE Withheld';
3252 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3253 ' SUI EE Withheld > 0 ***';
3254
3255 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3256 IN_prc_tax_unit_id,
3257 IN_prc_organization_id, IN_prc_location_id,
3258 IN_pact_id,
3259 IN_chunk_no, IN_prc_person_id,
3260 IN_prc_assignment_number,
3261 L_balance_nm1,
3262 L_balance_nm2,
3263 L_fit_ee_subj_whable, L_sui_ee_tax,
3264 NULL, NULL, curr_state_abbrev,
3265 L_main_mesg,
3266 curr_state_code||'-000-0000',
3267 L_asg_action_id,
3268 IN_prc_assignment_id );
3269 END IF;
3270
3271
3272 -- s)
3273 IF L_fit_ee_subj_whable <= 0 AND L_sui_er_tax > 0 THEN
3274
3275 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3276 L_balance_nm2 := L_dimension || ' SUI ER Withheld';
3277 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3278 ' SUI ER Withheld > 0 ***';
3279
3280 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3281 IN_prc_tax_unit_id,
3282 IN_prc_organization_id, IN_prc_location_id,
3283 IN_pact_id,
3284 IN_chunk_no, IN_prc_person_id,
3285 IN_prc_assignment_number,
3286 L_balance_nm1,
3287 L_balance_nm2,
3288 L_fit_ee_subj_whable, L_sui_er_tax,
3289 NULL, NULL, curr_state_abbrev,
3290 L_main_mesg,
3291 curr_state_code||'-000-0000',
3292 L_asg_action_id,
3293 IN_prc_assignment_id );
3294 END IF;
3295
3296
3297 -- r)
3298 IF L_fit_ee_gross_earnings < L_sum_sui_er_bal THEN
3299
3300 L_balance_nm1 := L_dimension || ' FIT Gross Earnings';
3301 L_balance_nm2 := 'TOTAL ' || L_dimension || ' SUI ER Taxable';
3302 L_main_mesg := '*** ' || L_dimension || ' FIT Gross Earnings < TOTAL ' || L_dimension ||
3303 ' SUI ER Taxable ***';
3304
3305 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3306 IN_prc_tax_unit_id,
3307 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3308 IN_chunk_no, IN_prc_person_id, IN_prc_assignment_number,
3309 L_balance_nm1,
3310 L_balance_nm2,
3311 L_fit_ee_gross_earnings, L_sum_sui_er_bal,
3312 NULL, NULL, NULL,
3313 L_main_mesg,
3314 '00-000-0000',
3315 L_asg_action_id,
3316 IN_prc_assignment_id );
3317 END IF;
3318
3319
3320 END IF; -- end if 'SUI'
3321
3322
3323 END prc_state_balances ;
3324
3325
3326 PROCEDURE prc_county_balances ( curr_jurisdiction_code IN VARCHAR2
3327 , curr_jurisdiction_name IN VARCHAR2 )
3328 IS
3329 BEGIN
3330 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3331 L_as_of_date, L_dimension, 'COUNTY', 'WITHHELD', 'EE',
3332 curr_jurisdiction_code,
3333 L_county_ee_tax);
3334
3335 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3336 L_as_of_date, L_dimension, 'COUNTY', 'SUBJ_WHABLE', 'EE',
3337 curr_jurisdiction_code,
3338 L_county_ee_s_whable);
3339
3340 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3341 L_as_of_date, L_dimension, 'COUNTY', 'SUBJ_NWHABLE', 'EE',
3342 curr_jurisdiction_code,
3343 L_county_ee_s_nwhable);
3344
3345 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3346 L_as_of_date, L_dimension, 'COUNTY', 'REDUCED_SUBJ_WHABLE', 'EE',
3347 curr_jurisdiction_code,
3348 L_county_ee_r_s_whable);
3349
3350
3351 L_county_ee_subject := L_county_ee_s_whable + L_county_ee_s_nwhable;
3352
3353
3354 -- y)
3355 IF L_county_ee_s_whable < L_county_ee_r_s_whable THEN
3356
3357 L_balance_nm1 := L_dimension || ' COUNTY EE Subject Withholdable';
3358 L_balance_nm2 := L_dimension || ' COUNTY EE Reduced Subject Withholdable';
3359 L_main_mesg := '*** ' || L_dimension || ' COUNTY EE Subject Withholdable < ' || L_dimension ||
3360 ' COUNTY EE '||'Reduced Subject Withholdable ***';
3361
3362 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3363 IN_prc_tax_unit_id,
3364 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3365 IN_chunk_no, IN_prc_person_id,
3366 IN_prc_assignment_number,
3367 L_balance_nm1,
3368 L_balance_nm2,
3369 L_county_ee_s_whable, L_county_ee_r_s_whable,
3370 NULL, NULL, curr_jurisdiction_name,
3371 L_main_mesg,
3372 SUBSTR(curr_jurisdiction_code,1,6)||'-0000',
3373 L_asg_action_id ,
3374 IN_prc_assignment_id );
3375 END IF;
3376
3377 -- x)
3378 IF L_fit_ee_subj_whable <= 0 AND L_county_ee_tax > 0 THEN
3379
3380 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3381 L_balance_nm2 := L_dimension || ' County Withheld';
3382 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3383 ' County '||'Withheld > 0 ***';
3384
3385 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3386 IN_prc_tax_unit_id,
3387 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3388 IN_chunk_no, IN_prc_person_id,
3389 IN_prc_assignment_number,
3390 L_balance_nm1,
3391 L_balance_nm2,
3392 L_fit_ee_subj_whable, L_county_ee_tax,
3393 NULL, NULL, curr_jurisdiction_name,
3394 L_main_mesg,
3395 SUBSTR(curr_jurisdiction_code,1,6)||'-0000',
3396 L_asg_action_id,
3397 IN_prc_assignment_id );
3398 END IF;
3399
3400
3401 -- w)
3402 IF fnc_lit_tax_exists(curr_jurisdiction_code, L_as_of_date, 'COUNTY') THEN
3403 IF L_county_ee_subject <= 0 AND L_county_ee_tax > 0 THEN
3404
3405 L_balance_nm1 := L_dimension || ' County Subject';
3406 L_balance_nm2 := L_dimension || ' County Withheld';
3407 L_main_mesg := '*** ' || L_dimension || ' County Subject <= 0 and ' || L_dimension ||
3408 ' County Withheld > 0 ***';
3409
3410 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3411 IN_prc_tax_unit_id,
3412 IN_prc_organization_id, IN_prc_location_id,
3413 IN_pact_id,
3414 IN_chunk_no, IN_prc_person_id,
3415 IN_prc_assignment_number,
3416 L_balance_nm1,
3417 L_balance_nm2,
3418 L_county_ee_subject, L_county_ee_tax,
3419 NULL, NULL, curr_jurisdiction_name,
3420 L_main_mesg,
3421 SUBSTR(curr_jurisdiction_code,1,6)||'-0000',
3422 L_asg_action_id,
3423 IN_prc_assignment_id );
3424 END IF;
3425 END IF; -- w)
3426
3427
3428 END prc_county_balances ;
3429
3430
3431 PROCEDURE prc_city_balances ( curr_jurisdiction_code IN VARCHAR2
3432 , curr_jurisdiction_name IN VARCHAR2 )
3433 IS
3434 BEGIN
3435 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3436 L_as_of_date, L_dimension, 'CITY', 'WITHHELD', 'EE',
3437 curr_jurisdiction_code,
3438 L_city_ee_tax);
3439
3440 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3441 L_as_of_date, L_dimension, 'CITY', 'SUBJ_WHABLE', 'EE',
3442 curr_jurisdiction_code,
3443 L_city_ee_s_whable);
3444
3445 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3446 L_as_of_date,L_dimension, 'CITY', 'SUBJ_NWHABLE', 'EE',
3447 curr_jurisdiction_code,
3448 L_city_ee_s_nwhable);
3449
3450 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3451 L_as_of_date, L_dimension, 'CITY', 'REDUCED_SUBJ_WHABLE', 'EE',
3452 curr_jurisdiction_code,
3453 L_city_ee_r_s_whable);
3454
3455 L_city_ee_subject := L_city_ee_s_whable + L_city_ee_s_nwhable;
3456
3457 -- y)
3458 IF L_city_ee_s_whable < L_city_ee_r_s_whable THEN
3459
3460 L_balance_nm1 := L_dimension || ' CITY EE Subject Withholdable';
3461 L_balance_nm2 := L_dimension || ' CITY EE Reduced Subject Withholdable';
3462 L_main_mesg := '*** ' || L_dimension || ' CITY EE Subject Withholdable < ' || L_dimension ||
3463 ' CITY EE Reduced '||'Subject Withholdable ***';
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, IN_pact_id,
3468 IN_chunk_no, IN_prc_person_id,
3469 IN_prc_assignment_number,
3470 L_balance_nm1,
3471 L_balance_nm2,
3472 L_city_ee_s_whable, L_city_ee_r_s_whable,
3473 NULL, NULL, curr_jurisdiction_name,
3474 L_main_mesg,
3475 curr_jurisdiction_code,
3476 L_asg_action_id,
3477 IN_prc_assignment_id );
3478 END IF;
3479
3480
3481 -- x)
3482 IF L_fit_ee_subj_whable <= 0 AND L_city_ee_tax > 0 THEN
3483
3484 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3485 L_balance_nm2 := L_dimension || ' City Withheld';
3486 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3487 ' City '||'Withheld > 0 ***';
3488
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, IN_pact_id,
3493 IN_chunk_no, IN_prc_person_id,
3494 IN_prc_assignment_number,
3495 L_balance_nm1,
3496 L_balance_nm2,
3497 L_fit_ee_subj_whable, L_city_ee_tax,
3498 NULL, NULL, curr_jurisdiction_name,
3499 L_main_mesg,
3500 curr_jurisdiction_code,
3501 L_asg_action_id,
3502 IN_prc_assignment_id );
3503 END IF;
3504
3505
3506 -- w)
3507 IF fnc_lit_tax_exists(curr_jurisdiction_code, L_as_of_date, 'CITY') THEN
3508 IF L_city_ee_subject <= 0 AND L_city_ee_tax > 0 THEN
3509
3510 L_balance_nm1 := L_dimension || ' City Subject';
3511 L_balance_nm2 := L_dimension || ' City Withheld';
3512 L_main_mesg := '*** ' || L_dimension || ' City Subject <= 0 and ' || L_dimension ||
3513 ' City Withheld > 0 ***';
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,
3518 IN_pact_id,
3519 IN_chunk_no, IN_prc_person_id,
3520 IN_prc_assignment_number,
3521 L_balance_nm1,
3522 L_balance_nm2,
3523 L_city_ee_subject, L_city_ee_tax,
3524 NULL, NULL, curr_jurisdiction_name,
3525 L_main_mesg,
3526 curr_jurisdiction_code,
3527 L_asg_action_id,
3528 IN_prc_assignment_id );
3529 END IF;
3530 END IF;
3531
3532
3533 END prc_city_balances ;
3534
3535
3536
3537 PROCEDURE prc_school_balances ( curr_jurisdiction_code IN VARCHAR2
3538 , curr_jurisdiction_name IN VARCHAR2
3539 , curr_reg_jurisdiction_cd IN VARCHAR2 )
3540 IS
3541 BEGIN
3542 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3543 L_as_of_date, L_dimension, 'SCHOOL', 'WITHHELD', 'EE',
3544 curr_jurisdiction_code,
3545 L_school_ee_tax);
3546
3547 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3548 L_as_of_date, L_dimension, 'SCHOOL', 'SUBJ_WHABLE', 'EE',
3549 curr_jurisdiction_code,
3550 L_school_ee_s_whable);
3551
3552 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3553 L_as_of_date, L_dimension, 'SCHOOL', 'SUBJ_NWHABLE', 'EE',
3554 curr_jurisdiction_code,
3555 L_school_ee_s_nwhable);
3556
3557 prc_get_balance(L_asg_action_id, IN_prc_tax_unit_id,
3558 L_as_of_date, L_dimension, 'SCHOOL', 'REDUCED_SUBJ_WHABLE', 'EE',
3559 curr_jurisdiction_code,
3560 L_school_ee_r_s_whable);
3561
3562 L_school_ee_subject := L_school_ee_s_whable + L_school_ee_s_nwhable;
3563
3564
3565 -- y)
3566 IF L_school_ee_s_whable < L_school_ee_r_s_whable THEN
3567
3568 L_balance_nm1 := L_dimension || ' SCHOOL EE Subject Withholdable';
3569 L_balance_nm2 := L_dimension || ' SCHOOL EE Reduced Subject Withholdable';
3570 L_main_mesg := '*** ' || L_dimension || ' SCHOOL EE Subject Withholdable < ' || L_dimension ||
3571 ' SCHOOL EE '||'Reduced Subject Withholdable ***';
3572
3573 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3574 IN_prc_tax_unit_id,
3575 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3576 IN_chunk_no, IN_prc_person_id,
3577 IN_prc_assignment_number,
3578 L_balance_nm1,
3579 L_balance_nm2,
3580 L_school_ee_s_whable, L_school_ee_r_s_whable,
3581 NULL, NULL, curr_jurisdiction_name,
3582 L_main_mesg,
3583 curr_reg_jurisdiction_cd,
3584 L_asg_action_id,
3585 IN_prc_assignment_id );
3586 END IF;
3587
3588
3589 -- x)
3590 IF L_fit_ee_subj_whable <= 0 AND L_school_ee_tax > 0 THEN
3591
3592 L_balance_nm1 := L_dimension || ' FIT Subject Withholdable';
3593 L_balance_nm2 := L_dimension || ' School Withheld';
3594 L_main_mesg := '*** ' || L_dimension || ' FIT Subject Withholdable <= 0 and ' || L_dimension ||
3595 ' School '||'Withheld > 0 ***';
3596
3597 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3598 IN_prc_tax_unit_id,
3599 IN_prc_organization_id, IN_prc_location_id, IN_pact_id,
3600 IN_chunk_no, IN_prc_person_id,
3601 IN_prc_assignment_number,
3602 L_balance_nm1,
3603 L_balance_nm2,
3604 L_fit_ee_subj_whable, L_school_ee_tax,
3605 NULL, NULL, curr_jurisdiction_name,
3606 L_main_mesg,
3607 curr_reg_jurisdiction_cd,
3608 L_asg_action_id ,
3609 IN_prc_assignment_id );
3610 END IF;
3611
3612
3613 -- w)
3614 IF fnc_lit_tax_exists(curr_reg_jurisdiction_cd, L_as_of_date, 'SCHOOL') THEN
3615 IF L_school_ee_subject <= 0 AND L_school_ee_tax > 0 THEN
3616
3617 L_balance_nm1 := L_dimension || ' School Subject';
3618 L_balance_nm2 := L_dimension || ' School Withheld';
3619 L_main_mesg := '*** ' || L_dimension || ' School Subject <= 0 and ' || L_dimension ||
3620 ' School Withheld > 0 ***';
3621
3622 prc_write_data(IN_commit_count,'U', IN_prc_lockingactid,
3623 IN_prc_tax_unit_id,
3624 IN_prc_organization_id, IN_prc_location_id,
3625 IN_pact_id,
3626 IN_chunk_no, IN_prc_person_id,
3627 IN_prc_assignment_number,
3628 L_balance_nm1,
3629 L_balance_nm2,
3630 L_school_ee_subject, L_school_ee_tax,
3631 NULL, NULL, curr_jurisdiction_name,
3632 L_main_mesg,
3633 curr_reg_jurisdiction_cd,
3634 L_asg_action_id,
3635 IN_prc_assignment_id );
3636 END IF;
3637 END IF;
3638
3639 END prc_school_balances ;
3640
3641 /******************************** 3005756 end ************************************************/
3642
3643 -----------------------------------------------
3644 BEGIN
3645 -- setup commit counter before we start
3646
3647
3648 G_commit_count := IN_commit_count;
3649
3650 -- get all required parameters from legislative parameter string
3651
3652 /***************** 3005756 START *******************************/
3653
3654 -- Assign the local payroll stuff variables the global values
3655
3656 L_business_id := G_business_id;
3657 L_as_of_date := G_as_of_date;
3658 L_leg_param := G_leg_param;
3659
3660 /*********************3005756 END ***************************/
3661
3662 L_dimension := fnc_get_parameter('B_Dim',L_leg_param);
3663 L_gre_id := fnc_get_parameter('GRE',L_leg_param);
3664 L_org_id := fnc_get_parameter('Org',L_leg_param);
3665 L_location_id := fnc_get_parameter('Loc',L_leg_param);
3666 L_tax_type := fnc_get_parameter('T_T',L_leg_param);
3667 L_tax_type_state := fnc_get_parameter('T_T_S',L_leg_param);
3668 L_usr_SDI_ER_rate := fnc_get_parameter('ERR',L_leg_param);
3669 L_usr_SDI_EE_rate := fnc_get_parameter('EER',L_leg_param);
3670
3671 -- calculate first half date for later use if type is SUI
3672 --L_first_half_date := TO_DATE('30-JUN-'||TO_CHAR(L_as_of_date,'YYYY'),'DD-MON-YYYY');
3673 L_first_half_date := TO_DATE('30-06-'||TO_CHAR(L_as_of_date,'YYYY'),'DD-MM-YYYY');
3674
3675 -- calculate the start date based on YTD or QTD dimensions
3676
3677 /***********************3005756 START *********************************/
3678
3679 -- If the federal pl/sql table is empty populate it and then fetch the
3680 -- values into global variables
3681
3682 IF pay_us_payroll_utils.ltr_fed_tax_info.count<1 THEN
3683 pay_us_payroll_utils.populate_jit_information(p_effective_date => L_as_of_date
3684 ,p_get_federal => 'Y' );
3685 END IF;
3686
3687 G_ss_ee_wage_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_wage;
3688 G_ss_ee_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_rate;
3689 G_ss_er_wage_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_wage;
3690 G_ss_er_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_rate;
3691 G_medi_ee_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).med_ee_rate;
3692 G_medi_er_rate := pay_us_payroll_utils.ltr_fed_tax_info(1).med_er_rate;
3693
3694 /****************************** 3005756 END ***************************************/
3695
3696
3697 IF L_dimension = 'QTD' THEN
3698 L_start_date := TRUNC(L_as_of_date,'Q') ;
3699 ELSIF L_dimension = 'YTD' THEN
3700 L_start_date := TRUNC(L_as_of_date,'YYYY');
3701 END IF;
3702
3703 L_asg_action_id := IN_prc_lockedactid ;
3704
3705 L_medi_exempt := f_check_medi_exempt(IN_prc_assignment_id,
3706 L_start_date,
3707 L_as_of_date);
3708
3709
3710 -- Call for all Unacceptable Federal balance checks
3711
3712 prc_federal_balances();
3713
3714 /************************************* Bug 2963239 Changes End *********************************************/
3715
3716
3717
3718 IF L_tax_type = 'SDI' OR L_tax_type = 'SUI' OR L_tax_type = 'SIT'
3719 OR L_tax_type IS NULL
3720 THEN
3721
3722 -- this variable must be reset before going into loop
3723
3724 L_sum_sui_er_bal := 0;
3725 L_sum_sdi_ee_bal := 0;
3726
3727 /************************ 3005756 start *******************************************************/
3728
3729 IF G_state_flag = 'Y' THEN
3730
3731 hr_utility.trace('Balances are valid .Inside vailid state cursor');
3732
3733 FOR curr_state IN c_state_jurisdictions_valid(IN_prc_person_id,
3734 L_tax_type_state, L_start_date,
3735 L_as_of_date)
3736 LOOP
3737
3738 prc_state_balances ( curr_state.jurisdiction_code,curr_state.state_code,curr_state.state_abbrev);
3739
3740 END LOOP; -- end curr_state
3741
3742 ELSE
3743 hr_utility.trace('Balances are invalid .Inside invalid state cursor');
3744
3745 FOR curr_state IN c_state_jurisdictions(IN_prc_person_id,
3746 L_tax_type_state, L_start_date,
3747 L_as_of_date)
3748 LOOP
3749
3750 prc_state_balances ( curr_state.jurisdiction_code,curr_state.state_code,curr_state.state_abbrev);
3751
3752 END LOOP; -- end curr_state
3753
3754 END IF;
3755
3756
3757 /************************** 3005756 end ********************************************************/
3758
3759 END IF; -- end if 'SDI', 'SUI', 'SIT'
3760
3761
3762
3763 IF L_tax_type = 'LIT' OR L_tax_type IS NULL THEN
3764 -- for each valid county jurisdiction ...
3765
3766
3767 /******************** 3005756 start ***********************************************/
3768
3769 IF G_county_flag = 'Y' THEN
3770 hr_utility.trace('Balances are valid .Inside valid county cursor');
3771
3772 FOR curr_county IN c_county_jurisdictions_valid(IN_prc_person_id,
3773 L_tax_type_state, L_start_date,
3774 L_as_of_date)
3775 LOOP
3776
3777 prc_county_balances ( curr_county.jurisdiction_code,curr_county.jurisdiction_name );
3778
3779 END LOOP; -- end curr_county loop
3780
3781 ELSE
3782 hr_utility.trace('Balances are invalid .Inside invalid county cursor');
3783
3784 FOR curr_county IN c_county_jurisdictions(IN_prc_person_id,
3785 L_tax_type_state, L_start_date,
3786 L_as_of_date)
3787 LOOP
3788
3789 prc_county_balances ( curr_county.jurisdiction_code,curr_county.jurisdiction_name );
3790
3791 END LOOP; -- end curr_county loop
3792
3793 END IF;
3794
3795 /**************************3005756 end ********************************/
3796
3797 -- for each city valid jurisdiction ...
3798
3799
3800 /*********************** 3005756 start *****************************************/
3801
3802 IF G_city_flag = 'Y' THEN
3803 hr_utility.trace('Balances are valid .Inside valid city cursor');
3804
3805 FOR curr_city IN c_city_jurisdictions_valid(IN_prc_person_id,
3806 L_tax_type_state, L_start_date,
3807 L_as_of_date)
3808 LOOP
3809
3810 prc_city_balances ( curr_city.jurisdiction_code,curr_city.jurisdiction_name );
3811
3812 END LOOP; -- end curr_city loop
3813
3814 ELSE
3815 hr_utility.trace('Balances are invalid .Inside invalid city cursor');
3816
3817 FOR curr_city IN c_city_jurisdictions(IN_prc_person_id,
3818 L_tax_type_state, L_start_date,
3819 L_as_of_date)
3820 LOOP
3821
3822 prc_city_balances ( curr_city.jurisdiction_code,curr_city.jurisdiction_name );
3823
3824 END LOOP; -- end curr_city loop
3825
3826 END IF;
3827
3828 /************************************ 3005756 end ***************************/
3829
3830
3831 -- for each valid school jurisdiction ...
3832
3833 /**************************** 3005756 start **********************************/
3834
3835 IF G_school_flag = 'Y' THEN
3836 hr_utility.trace('Balances are valid .Inside valid school cursor');
3837
3838 FOR curr_school IN c_school_jurisdictions_valid(IN_prc_person_id,
3839 L_tax_type_state,
3840 IN_prc_tax_unit_id, L_start_date,
3841 L_as_of_date)
3842 LOOP
3843
3844 prc_school_balances ( curr_school.jurisdiction_code
3845 , curr_school.jurisdiction_name
3846 , curr_school.reg_jurisdiction_cd );
3847
3848 END LOOP; -- end curr_school loop
3849
3850 ELSE
3851 hr_utility.trace('Balances are invalid .Inside invalid school cursor');
3852
3853 FOR curr_school IN c_school_jurisdictions(IN_prc_person_id,
3854 L_tax_type_state,
3855 IN_prc_tax_unit_id, L_start_date,
3856 L_as_of_date)
3857 LOOP
3858
3859 prc_school_balances ( curr_school.jurisdiction_code
3860 , curr_school.jurisdiction_name
3861 , curr_school.reg_jurisdiction_cd );
3862
3863 END LOOP; -- end curr_school loop
3864
3865 END IF;
3866
3867 /*********************************** 3005756 end ******************************/
3868
3869
3870 END IF; -- end if 'LIT'
3871
3872
3873
3874 EXCEPTION
3875 WHEN OTHERS THEN
3876 -- rollback all uncommited changes
3877 ROLLBACK;
3878 -- does not matter what the error is delete all commited inserted tmp records
3879 DELETE pay_us_rpt_totals
3880 WHERE session_id = IN_pact_id
3881 AND business_group_id = IN_chunk_no
3882 AND tax_unit_id = IN_prc_tax_unit_id; -- Bug 3316599 to reduce the cost of query
3883 COMMIT;
3884 -- reraise the error
3885 RAISE;
3886 END prc_process_data;
3887
3888
3889 END PAYUSUNB_PKG;