[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MWR_REPORTING_PKG
Source
1 PACKAGE BODY pay_us_mwr_reporting_pkg AS
2 /* $Header: pyusmwrp.pkb 120.3 2006/08/18 11:29:35 schowta noship $ */
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 : pay_us_mwr_reporting_pkg
21
22 Description : Generate Multi Worksite magnetic reports.
23
24 Uses :
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ---- ---- ------ ------- -----------
30 8-FEB-2001 tclewis 115.0 Created.
31
32 15-nov 2001 tclewis 115.7 Modified the check for
33 IF l_archive_value = 0 THEN
34 to also check to see it SIT_Exists
35 for the jurisdiction (state) as
36 (as of this time) there are 9 states that
37 Have no income tax. Hence, the archive value
38 will be 0 and they are not included on the
39 report.
40
41 20-MAY-2002 tclewis 115.10 Modified the code to check the NEW archive items
42 used for employee counts since Q 1 2002 SQWL's.
43
44 24-DEC-2002 tclewis 115.11 Added NOCOPY 11.5.9 performance fixes.
45 21-APR-2002 tclewis 115.12 Removed spectial code for OH and WY
46 to NOT reducde Sui wages by pre tax.
47
48 29-JAN-2004 ardsouza 115.14 3362257 Added date format mask for GSCC compliance.
49
50 24-JUN-2004 rmonge 115.15 3711795 Added the following condition
51 'and sta_information_category =
52 'State tax limit rate info';
53 to the query that retrieves the SIT tax
54 exists in the Load_rpt_totals function.
55 query= select psif.sit_exists
56 into l_sit_exists...
57 This query was returning multiple rows and
58 the Multi Work Site report was failing.
59 12-OCT-2004 rmonge 115.16 3909329 Changed/Modified the cursor
60 'c_derive_wksite_estab due to performance problems.
61 12-OCT-2004 rmonge 115.17 No changes.
62 15-DEC-2004 rmonge 115.18 4047812 Modified c_derive_wksite_estab due to
63 performance problems.
64 14-MAR-2005 sackumar 115.19 4222032 Change in the Range Cursor removing redundant
65 use of bind Variable (:payroll_action_id)
66 18-aug-2005 sackumar 115.20 3613544 changed the c_get_sui_code cursor introduce use_nl hint.
67
68 18-aug-2006 schowta 115.21 5399921 added code fix to include the work at home employee count in load_rpt_totals
69 /******************************************************************
70 ** Package Local Variables
71 ******************************************************************/
72 gv_package varchar2(50) := 'pay_us_mwr_reporting_pkg';
73
74
75 PROCEDURE get_payroll_action_info (
76 p_payroll_action_id in number,
77 p_start_date out NOCOPY date,
78 p_end_date out NOCOPY date,
79 p_report_qualifier out NOCOPY varchar2,
80 p_report_type out NOCOPY varchar2,
81 p_report_category out NOCOPY varchar2,
82 p_business_group_id out NOCOPY number)
83 IS
84
85 cursor c_payroll_action(cp_payroll_action_id in number) is
86 select ppa.start_date
87 ,ppa.effective_date
88 ,ppa.business_group_id
89 ,ppa.report_qualifier
90 ,ppa.report_type
91 ,ppa.report_category
92 ,ppa.legislative_parameters
93 from pay_payroll_actions ppa
94 where payroll_action_id = cp_payroll_action_id;
95
96 ld_start_date DATE;
97 ld_end_date DATE;
98 ln_business_group_id NUMBER;
99 lv_report_qualifier VARCHAR2(30);
100 lv_report_type VARCHAR2(30);
101 lv_report_category VARCHAR2(30);
102 lv_leg_parameter VARCHAR2(300);
103
104 BEGIN
105 hr_utility.set_location(gv_package || '.get_payroll_action_info', 10);
106
107 open c_payroll_action(p_payroll_action_id);
108 fetch c_payroll_action into
109 ld_start_date, ld_end_date, ln_business_group_id,
110 lv_report_qualifier, lv_report_type,
111 lv_report_category, lv_leg_parameter;
112 if c_payroll_action%notfound then
113 hr_utility.set_location( gv_package || '.get_payroll_action_info',20);
114 hr_utility.raise_error;
115 end if;
116 close c_payroll_action;
117 hr_utility.set_location(gv_package || '.get_payroll_action_info', 30);
118
119
120 hr_utility.set_location(gv_package || '.get_payroll_action_info', 60);
121 p_start_date := ld_start_date;
122 p_end_date := ld_end_date;
123 p_report_qualifier := lv_report_qualifier;
124 p_report_type := lv_report_type;
125 p_report_category := lv_report_category;
126 p_business_group_id := ln_business_group_id;
127
128 hr_utility.set_location(gv_package || '.get_payroll_action_info', 100);
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 p_start_date := NULL;
133 p_end_date := NULL;
134 p_report_qualifier := NULL;
135 p_report_type := NULL;
136 p_report_category := NULL;
137 p_business_group_id := NULL;
138
139 END get_payroll_action_info;
140
141
142 /********************************************************
143 ** Range Code: Multi Threading
144 ********************************************************/
145 PROCEDURE range_cursor ( p_payroll_action_id in number
146 ,p_sql_string out NOCOPY varchar2)
147 IS
148
149 lv_sql_string varchar2(10000);
150
151 ld_start_date DATE;
152 ld_end_date DATE;
153 ln_business_group_id NUMBER;
154 lv_report_qualifier VARCHAR2(30);
155 lv_report_type VARCHAR2(30);
156 lv_report_category VARCHAR2(30);
157
158 ln_tax_unit_id NUMBER;
159 ln_payroll_id NUMBER;
160 ln_consolidation_set_id NUMBER;
161
162 BEGIN
163 hr_utility.set_location(gv_package || '.range_code', 10);
164 get_payroll_action_info (
165 p_payroll_action_id
166 ,ld_start_date
167 ,ld_end_date
168 ,lv_report_qualifier
169 ,lv_report_type
170 ,lv_report_category
171 ,ln_business_group_id);
172 hr_utility.set_location(gv_package || '.range_code', 20);
173
174 lv_sql_string :=
175 'select distinct paa.assignment_id
176 from pay_assignment_actions paa -- SQWL assignment action
177 , pay_payroll_actions ppa
178 where ppa.business_group_id = ' || ln_business_group_id || '
179 and ppa.effective_date between to_date(''' || to_char(ld_start_date, 'dd-mon-yyyy') || ''', ''dd-mon-yyyy'') --Bug 3362257
180 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''', ''dd-mon-yyyy'') --Bug 3362257
181 and ppa.action_type = ''X''
182 and ppa.report_type = ''SQWL''
183 and ppa.action_status =''C''
184 and ppa.payroll_action_id = paa.payroll_action_id
185 and :payroll_action_id is not null
186 order by paa.assignment_id
187 ';
188
189 p_sql_string := lv_sql_string;
190 hr_utility.set_location(gv_package || '.range_code', 50);
191
192 END range_cursor;
193
194 /********************************************************
195 ** Action Creation Code: Multi Threading
196 ********************************************************/
197 PROCEDURE action_creation( p_payroll_action_id in number
198 ,p_start_assignment in number
199 ,p_end_assignment in number
200 ,p_chunk in number)
201
202 IS
203
204 cursor c_get_mwr_asg( cp_business_group_id in number
205 ,cp_start_date in date
206 ,cp_end_date in date
207 ,cp_start_assignment_id in number
208 ,cp_end_assignment_id in number
209 ) is
210 select paa.assignment_id,
211 ppa.effective_date,
212 paa.tax_unit_id,
213 paa.assignment_action_id
214 from pay_assignment_actions paa -- SQWL assignment action
215 , pay_payroll_actions ppa
216 where ppa.business_group_id = cp_business_group_id
217 and ppa.effective_date between cp_start_date
218 and cp_end_date
219 and ppa.action_type = 'X'
220 and ppa.report_type = 'SQWL'
221 and ppa.action_status ='C'
222 and ppa.payroll_action_id = paa.payroll_action_id
223 and paa.assignment_id between cp_start_assignment_id
224 and cp_end_assignment_id;
225
226 ld_start_date DATE;
227 ld_end_date DATE;
228 ln_business_group_id NUMBER;
229 lv_report_qualifier VARCHAR2(30);
230 lv_report_type VARCHAR2(30);
231 lv_report_category VARCHAR2(30);
232 ln_tax_unit_id NUMBER;
233 ln_payroll_id NUMBER;
234 ln_consolidation_set_id NUMBER;
235
236 /* Assignment Record Local Variables */
237 ln_assignment_id NUMBER;
238 ld_effective_date DATE;
239 ln_emp_tax_unit_id NUMBER;
240 ln_assignment_action_id NUMBER;
241
242 ln_locking_action_id NUMBER;
243
244 BEGIN
245 hr_utility.set_location(gv_package || '.action_creation', 10);
246 get_payroll_action_info (
247 p_payroll_action_id
248 ,ld_start_date
249 ,ld_end_date
250 ,lv_report_qualifier
251 ,lv_report_type
252 ,lv_report_category
253 ,ln_business_group_id);
254
255 hr_utility.set_location(gv_package || '.action_creation', 20);
256 open c_get_mwr_asg( ln_business_group_id
257 ,ld_start_date
258 ,ld_end_date
259 ,p_start_assignment
260 ,p_end_assignment);
261 loop
262 hr_utility.set_location(gv_package || '.action_creation', 30);
263 fetch c_get_mwr_asg into ln_assignment_id, ld_effective_date,
264 ln_emp_tax_unit_id, ln_assignment_action_id;
265 if c_get_mwr_asg%notfound then
266 hr_utility.set_location(gv_package || '.action_creation', 40);
267 exit;
268 end if;
269
270 hr_utility.set_location(gv_package || '.action_creation', 50);
271 select pay_assignment_actions_s.nextval
272 into ln_locking_action_id
273 from dual;
274
275 -- **** CHECK FOR SUI WAGES HERE **** ----
276
277 -- insert into pay_assignment_actions.
278 hr_nonrun_asact.insact(ln_locking_action_id, ln_assignment_id,
279 p_payroll_action_id, p_chunk, ln_emp_tax_unit_id);
280 hr_utility.set_location(gv_package || '.action_creation', 60);
281
282 -- insert an interlock to this action
283 hr_nonrun_asact.insint(ln_locking_action_id, ln_assignment_action_id);
284
285 update pay_assignment_actions paa
286 set paa.serial_number = ln_assignment_action_id
287 where paa.assignment_action_id = ln_locking_action_id;
288
289 hr_utility.set_location(gv_package || '.action_creation', 60);
290 end loop;
291 close c_get_mwr_asg;
292
293 hr_utility.set_location(gv_package || '.action_creation', 60);
294 END action_creation;
295
296
297
298 FUNCTION LOAD_RPT_TOTALS( p_payroll_action_id in number)
299 RETURN number
300 IS
301
302 CURSOR get_pact_asg IS
303 SELECT paa.assignment_id
304 ,paa.tax_unit_id
305 ,paa.serial_number
306 ,ppa.business_group_id
307 FROM pay_payroll_actions ppa,
308 pay_assignment_actions paa
309 WHERE ppa.payroll_action_id = p_payroll_action_id
310 AND ppa.payroll_action_id = paa.payroll_action_id;
311
312 CURSOR c_asg_loc_mon ( p_ass_act_id number
313 ,p_mon_of_qtr number) IS
314 SELECT fai.value,
315 pus.state_code || '-000-0000',
316 pus.state_abbrev
317 FROM ff_archive_items fai
318 ,ff_user_entities ue
319 ,pay_us_states pus
320 ,hr_locations hl
321 where fai.user_entity_id = ue.user_entity_id
322 and fai.context1 = to_char(p_ass_act_id) -- context of assignment action id
323 and ue.user_entity_name =
324 decode(p_mon_of_qtr,4,'A_SQWL_LOC_QTR_END','A_SQWL_LOC_MON_' || to_char(p_mon_of_qtr))
325 and fai.value = hl.location_id
326 and hl.region_2 = pus.state_abbrev;
327
328 CURSOR c_get_sui_code ( p_tax_unit_id number,
329 p_jurisdiction varchar2 ) IS
330 SELECT /*+ use_nl (hoi1, hoi2)*/
331 hoi1.org_information2,
332 hoi2.org_information1
333 FROM pay_state_rules SR,
334 hr_organization_information hoi1,
335 hr_organization_information hoi2
336 WHERE hoi1.organization_id = p_tax_unit_id
337 AND hoi1.org_information_context = 'State Tax Rules'
338 AND hoi1.org_information1 = SR.state_code
339 AND SR.jurisdiction_code = substr(p_jurisdiction,1,2)||'-000-0000'
340 AND hoi2.organization_id = hoi1.organization_id
341 AND hoi2.org_information_context = 'Employer Identification' ;
342
343 CURSOR c_get_sui_subject ( p_ass_act_id number,
344 p_user_entity_id number,
345 p_tax_unit_id number,
346 P_jurisdiction_code varchar2
347 ) IS
348 SELECT fai.value
349 FROM ff_archive_item_contexts con3,
350 ff_archive_item_contexts con2,
351 ff_contexts fc3,
352 ff_contexts fc2,
353 ff_archive_items fai
354 WHERE fai.user_entity_id = p_user_entity_id
355 and fai.context1 = to_char(p_ass_act_id)
356 /* context assignment action id */
357 and fc2.context_name = 'TAX_UNIT_ID'
358 and con2.archive_item_id = fai.archive_item_id
359 and con2.context_id = fc2.context_id
360 and ltrim(rtrim(con2.context)) = to_char(p_tax_unit_id)
361 /* 2nd context of tax_unit_id */
362 and fc3.context_name = 'JURISDICTION_CODE'
363 and con3.archive_item_id = fai.archive_item_id
364 and con3.context_id = fc3.context_id
365 and substr(con3.context,1,2) = substr(p_jurisdiction_code,1,2)
366 /* 3rd context of state jurisdiction_code*/;
367
368
369 /*
370 CURSOR c_derive_wksite_estab (p_payroll_action_id number,
371 p_est_hierarchy_id number,
372 p_hierarchy_ver_id number,
373 p_location_id number) IS
374 SELECT pghn2.entity_id
375 FROM per_gen_hierarchy pgh
376 ,per_gen_hierarchy_versions pghv
377 ,per_gen_hierarchy_nodes pghn2 -- establishment organizations
378 ,pay_payroll_actions ppa
379 where ppa.payroll_action_id = p_payroll_action_id
380 and pgh.hierarchy_id = p_est_hierarchy_id
381 and pgh.business_group_id = ppa.business_group_id
382 and pgh.hierarchy_id = pghv.hierarchy_id
383 and pghv.HIERARCHY_VERSION_id = p_hierarchy_ver_id
384 and pghv.hierarchy_version_id = pghn2.hierarchy_version_id
385 and ( ( pghn2.node_type = 'EST'
386 and pghn2.entity_id = p_location_id
387 )
388 OR
389 ( pghn2.node_type = 'EST'
390 AND p_location_id in
391 ( SELECT pghn3.entity_id
392 FROM per_gen_hierarchy_nodes pghn3
393 WHERE pghn3.node_type = 'LOC'
394 AND pghn3.hierarchy_version_id = pghv.HIERARCHY_VERSION_id
395 AND pghn3.parent_hierarchy_node_id = pghn2.hierarchy_node_id
396 )
397 )
398 );
399
400 */
401
402 /* rmonge 15-DEC-2004 */
403 /* Performance bug 4047812 */
404 /* Changed subquery to use index and also changed p_hierarchy_ver_id */
405 /* to pghn2.hierarchy_version_id */
406
407 CURSOR c_derive_wksite_estab (p_hierarchy_ver_id number,
408 p_location_id number) IS
409 SELECT pghn2.entity_id
410 FROM per_gen_hierarchy_nodes pghn2 -- establishment organizations
411
412 where p_hierarchy_ver_id = pghn2.hierarchy_version_id
413 and ( ( pghn2.node_type = 'EST'
414 and pghn2.entity_id = p_location_id
415 )
416 OR
417 ( pghn2.node_type = 'EST'
418 AND p_location_id in
419 ( SELECT /*+ pghn3 PER_GEN_HIER_NOD_VER_N4 */ pghn3.entity_id
420 FROM per_gen_hierarchy_nodes pghn3
421 WHERE pghn3.node_type = 'LOC'
422 AND pghn3.hierarchy_version_id =
423 pghn2.hierarchy_version_id --p_hierarchy_ver_id
424 AND pghn3.parent_hierarchy_node_id =
425 pghn2.hierarchy_node_id
426 )
427 )
428 );
429
430
431
432 CURSOR c_get_sqwl_month_count ( cp_sqwl_assact in number,
433 cp_month_of_quarter in number)
434 IS
435
436 select fai.value,
437 ppa.report_qualifier
438 from ff_archive_items fai,
439 ff_user_entities ue,
440 pay_assignment_actions paa,
441 pay_payroll_actions ppa
442 where fai.context1 = cp_sqwl_assact
443 and paa.assignment_action_id = fai.context1
444 and fai.user_entity_id = ue.user_entity_id
445 and ue.user_entity_name = 'A_SQWL_MONTH' || to_char(cp_month_of_quarter) || '_COUNT'
446 and ppa.payroll_action_id = paa.payroll_action_id ;
447
448 l_month_count_state_code varchar2(2);
449 l_est_hierarchy_id number;
450 l_hierarchy_ver_id number;
451 l_ass_id number;
452 l_sqwl_assact number;
453 l_business_group_id number;
454 v_session_id number;
455 l_location_id number;
456 l_jurisdiction varchar2(11);
457 l_sqwl_jurisdiction_code varchar2(11);
458 l_wage_jurisdiction_code varchar2(11);
459 l_state_abbrev varchar2(2);
460 l_estab_loc_id number;
461 l_archive_value number;
462 l_sit_exists varchar2(1);
463 l_user_entity_id number;
464 l_tax_unit_id number;
465 l_sui_id varchar2(50);
466 l_fed_ein varchar2(50);
467 l_worksite number;
468 l_ppa_legislative_parameters varchar2(2000);
469 l_procedure varchar2(15) := 'load_rpt_totals';
470
471 FUNCTION calc_sui_reductions ( p_sqwl_assact in number
472 ,p_tax_unit_id in number
473 ,p_jurisdiction in varchar2)
474 RETURN number
475 IS
476
477 CURSOR c_get_sui_reds ( cp_ass_act_id number,
478 cp_user_entity_id number,
479 cp_tax_unit_id number,
480 cp_jurisdiction_code varchar2
481 ) IS
482 SELECT fai.value
483 FROM ff_archive_item_contexts con3,
484 ff_archive_item_contexts con2,
485 ff_contexts fc3,
486 ff_contexts fc2,
487 ff_archive_items fai
488 WHERE fai.user_entity_id = cp_user_entity_id
489 and fai.context1 = to_char(cp_ass_act_id)
490 /* context assignment action id */
491 and fc2.context_name = 'TAX_UNIT_ID'
492 and con2.archive_item_id = fai.archive_item_id
493 and con2.context_id = fc2.context_id
494 and ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
495 /* 2nd context of tax_unit_id */
496 and fc3.context_name = 'JURISDICTION_CODE'
497 and con3.archive_item_id = fai.archive_item_id
498 and con3.context_id = fc3.context_id
499 and substr(con3.context,1,2) = substr(cp_jurisdiction_code,1,2)
500 /* 3rd context of state jurisdiction_code*/;
501
502 l_sui_total_reductions number;
503 l_user_entity_id number;
504 BEGIN
505
506 SELECT fue.user_entity_id
507 INTO l_user_entity_id
508 FROM ff_user_entities fue
509 WHERE fue.user_entity_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
510 AND fue.legislation_code = 'US';
511
512 OPEN c_get_sui_reds( p_sqwl_assact
513 ,l_user_entity_id
514 ,p_tax_unit_id
515 ,p_jurisdiction);
516
517 FETCH c_get_sui_reds INTO l_sui_total_reductions;
518
519 IF c_get_sui_reds%NOTFOUND THEN
520 l_sui_total_reductions :=0;
521 END IF;
522
523 CLOSE c_get_sui_reds;
524
525 return(l_sui_total_reductions);
526
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 RETURN(0);
530 END calc_sui_reductions;
531
532
533
534 BEGIN
535
536 hr_utility.set_location(gv_package || '.' || l_procedure , 10);
537
538 --get the session id
539 SELECT userenv('sessionid')
540 INTO v_session_id
541 FROM dual;
542
543 --get the hierarchy version and establishment from legislative paramters
544 SELECT ppa.legislative_parameters
545 INTO l_ppa_legislative_parameters
546 FROM pay_payroll_actions ppa
547 where ppa.payroll_action_id = p_payroll_action_id;
548
549 hr_utility.set_location(gv_package || '.' || l_procedure , 20);
550
551 l_est_hierarchy_id :=
552 to_number(pay_mag_utils.get_parameter('TRANSFER_HIERARCHY_ID'
553 ,'TRANSFER_HIERARCHY_VERSION'
554 ,l_ppa_legislative_parameters));
555 l_hierarchy_ver_id :=
556 pay_mag_utils.get_parameter('TRANSFER_HIERARCHY_VERSION'
557 ,''
558 ,l_ppa_legislative_parameters);
559
560 hr_utility.set_location(gv_package || '.' || l_procedure , 30);
561
562 -- cusror loop.
563 OPEN get_pact_asg;
564
565 FETCH get_pact_asg into l_ass_id, l_tax_unit_id, l_sqwl_assact, l_business_group_id;
566
567 WHILE get_pact_asg%FOUND LOOP
568
569 hr_utility.set_location(gv_package || '.' || l_procedure , 40);
570
571 FOR i IN 1 .. 4 LOOP
572
573 OPEN c_asg_loc_mon(l_sqwl_assact,
574 i);
575 Fetch c_asg_loc_mon into l_location_id, l_jurisdiction, l_state_abbrev;
576 IF c_asg_loc_mon%NOTFOUND THEN
577 CLOSE c_asg_loc_mon;
578 l_location_id := NULL;
579 l_jurisdiction := NULL;
580 l_state_abbrev := NULL;
581 ELSE
582 CLOSE C_ASG_LOC_MON;
583 l_sit_exists := 'Y';
584 l_wage_jurisdiction_code := l_jurisdiction;
585
586 hr_utility.set_location(gv_package || '.' || l_procedure , 50);
587
588 -- get the sui ID for the SQWL assignment action
589 OPEN c_get_sui_code( l_tax_unit_id, l_jurisdiction );
590
591 FETCH c_get_sui_code into l_sui_id, l_fed_ein;
592
593 IF c_get_sui_code%NOTFOUND THEN
594 l_sui_ID := lpad(' ',50,0);
595 l_fed_ein := lpad(' ',50,0);
596 END IF;
597 CLOSE c_get_sui_code;
598
599 -- get the user_entity_id for the 'A_SIT_GROSS_PER_JD_GRE_MON_*' DBI
600 IF i < 4 THEN
601 SELECT ue.user_entity_id
602 INTO l_user_entity_id
603 FROM ff_user_entities ue
604 WHERE ue.user_entity_name = 'A_SIT_GROSS_PER_JD_GRE_MON_' || to_char(i)
605 AND ue.legislation_code = 'US';
606 ELSE
607 SELECT ue.user_entity_id
608 INTO l_user_entity_id
609 FROM ff_user_entities ue
610 WHERE ue.user_entity_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
611 AND ue.legislation_code = 'US';
612 END IF;
613
614 -- As of Q 1 2002 we no longer archive the A_SIT_GROSS_PER_JD_GRE_MON*
615 -- data (used for month counts in the sqwl). We will not archive a new
616 -- Datat base item named A_SQWL_MONTH*_COUNT (where * is 1, 2, 3).
617 -- need to check for the existance of the NEW DBI first and if not
618 -- found revert back to the old DBI's (this is for re-runs of Multiple
619 -- work site report prior to Q 1 2002). For a 1 thru 4 loop we will still
620 -- fetch the A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD which stores the quarterly
621 -- wages.
622
623 OPEN c_get_sqwl_month_count ( l_sqwl_assact,
624 i );
625
626 FETCH c_get_sqwl_month_count
627 INTO l_archive_value,
628 l_month_count_state_code;
629
630 IF c_get_sqwl_month_count%NOTFOUND THEN
631
632 close c_get_sqwl_month_count;
633
634 -- get the value for the A_SIT_GROSS_PER_JD_GRE_MON_* archive item.
635
636 hr_utility.set_location(gv_package || '.' || l_procedure , 60);
637 OPEN c_get_sui_subject ( l_sqwl_assact
638 ,l_user_entity_id
639 ,l_tax_unit_id
640 ,l_jurisdiction);
641
642 FETCH c_get_sui_subject into l_archive_value;
643
644 IF c_get_sui_subject%NOTFOUND THEN
645 -- If get sit_gross is not found then need to check the jurisdiction
646 -- of the SQWL assignment action.
647 CLOSE c_get_sui_subject;
648
649 BEGIN
650 SELECT psr.jurisdiction_code
651 INTO l_sqwl_jurisdiction_code
652 FROM pay_assignment_actions paa,
653 pay_payroll_actions ppa,
654 pay_state_rules psr
655 WHERE paa.assignment_action_id = l_sqwl_assact
656 AND ppa.payroll_action_id = paa.payroll_action_id
657 AND psr.state_code = ppa.report_qualifier;
658
659 l_wage_jurisdiction_code := l_sqwl_jurisdiction_code;
660
661 OPEN c_get_sui_subject ( l_sqwl_assact
662 ,l_user_entity_id
663 ,l_tax_unit_id
664 ,l_sqwl_jurisdiction_code);
665
666 FETCH c_get_sui_subject into l_archive_value;
667
668 IF c_get_sui_subject%NOTFOUND THEN
669 l_archive_value := 0;
670 CLOSE c_get_sui_subject;
671 ELSE
672 -- if we've gotten this far, then wages are in a different state
673 -- than assignment work location. Need to set the l_location_id
674 -- to -99999 and the l_state_code to the state of the l_sqwl_jurisdiction
675
676 l_location_id := -99999;
677
678 SELECT report_qualifier
679 INTO l_state_abbrev
680 FROM pay_assignment_actions paa,
681 pay_payroll_actions ppa
682 WHERE ppa.payroll_action_id = paa.payroll_action_id
683 AND paa.assignment_action_id = l_sqwl_assact;
684
685 CLOSE c_get_sui_subject;
686
687 -- also need to point the SUI id to the of the state where wages were paid
688 -- verses the sui id of the assignment location state.
689
690 SELECT hoi1.org_information2
691 INTO l_sui_id
692 FROM pay_state_rules SR,
693 hr_organization_information hoi1
694 WHERE hoi1.organization_id = l_tax_unit_id
695 AND hoi1.org_information_context = 'State Tax Rules'
696 AND hoi1.org_information1 = SR.state_code
697 AND SR.jurisdiction_code =
698 substr(l_sqwl_jurisdiction_code,1,2)||'-000-0000';
699
700 END IF;
701
702 EXCEPTION when NO_DATA_FOUND THEN
703
704 l_archive_value := 0;
705 END;
706 ELSE
707 CLOSE c_get_sui_subject;
708
709 END IF;
710
711 -- Need to see it SIT exists in the state where we just retrived
712 -- the archive value, as there are 9 states that have no SIT and
713 -- the arcive value will be 0. They must still be counted on
714 -- the report.
715 l_sit_exists := 'Y';
716
717 Select psif.sit_exists
718 into l_sit_exists
719 from pay_us_state_tax_info_f psif,
720 pay_payroll_actions ppa
721 where ppa.payroll_action_id = p_payroll_action_id
722 and psif.state_code = substr(l_jurisdiction,1,2)
723 and ppa.effective_date
724 BETWEEN psif.effective_start_date AND psif.effective_end_date
725 and sta_information_category = 'State tax limit rate info';
726
727 ELSE
728 -- compare the state code retured from the c_get_sqwl_month_count
729 -- to the state code of the locations ID, if = then fine, else
730 -- change the jurisdiction_code to that of the state that is
731 -- returned in the c_get_sqwl_month_cursor.
732
733 close c_get_sqwl_month_count;
734
735 l_sit_exists := 'Y';
736
737 IF l_state_abbrev = l_month_count_state_code THEN
738 NULL;
739 ELSE
740
741 -- Bug fix 5399921 START
742
743 SELECT psr.jurisdiction_code
744 INTO l_sqwl_jurisdiction_code
745 FROM pay_assignment_actions paa,
746 pay_payroll_actions ppa,
747 pay_state_rules psr
748 WHERE paa.assignment_action_id = l_sqwl_assact
749 AND ppa.payroll_action_id = paa.payroll_action_id
750 AND psr.state_code = ppa.report_qualifier;
751
752 SELECT hoi1.org_information2
753 INTO l_sui_id
754 FROM pay_state_rules SR,
755 hr_organization_information hoi1
756 WHERE hoi1.organization_id = l_tax_unit_id
757 AND hoi1.org_information_context = 'State Tax Rules'
758 AND hoi1.org_information1 = SR.state_code
759 AND SR.jurisdiction_code = substr(l_sqwl_jurisdiction_code,1,2)||'-000-0000';
760
761 -- Bug fix 5399921 End
762
763 l_location_id := -99999;
764 l_state_abbrev := l_month_count_state_code;
765 END IF;
766
767 END IF; -- if c_get_sqwl_month_count%NOT FOUND
768
769 -- If I've gotten this far then I know location_id is not null.
770 IF l_archive_value <> 0 OR
771 (l_sit_exists = 'N' and
772 l_jurisdiction is not NULL ) THEN
773
774 /* if i = 4 we are getting the sui wages All states report reduced SUI
775 subject wages using the formula
776
777 SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD -
778 SUI_ER_125_REDNS_PER_JD_GRE_QTD -
779 SUI_ER_401_REDNS_PER_JD_GRE_QTD -
780 SUI_ER_DEP_CARE_REDNS_PER_JD_GRE_QTD
781
782 except Ohio State_abbrev 'OH' and Wyoming State_abbrev 'OH'
783 which use SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD
784 */
785 IF i = 4
786 /* bug 2914661 and
787 l_state_abbrev <> 'OH' and
788 l_state_abbrev <> 'WY'*/
789 THEN
790 l_archive_value := l_archive_value -
791 calc_sui_reductions ( l_sqwl_assact
792 ,l_tax_unit_id
793 ,l_wage_jurisdiction_code);
794 END IF;
795
796
797 -- derive the establishment
798 hr_utility.set_location(gv_package || '.' || l_procedure , 70);
799
800 IF l_location_id <> -99999 THEN
801 OPEN c_derive_wksite_estab( l_hierarchy_ver_id,
802 l_location_id);
803
804 FETCH c_derive_wksite_estab into l_worksite;
805
806 IF c_derive_wksite_estab%NOTFOUND THEN
807 l_worksite := -99999;
808 END IF;
809
810 CLOSE c_derive_wksite_estab;
811 ELSE
812 l_worksite := -99999;
813 END IF;
814
815 -- Write the us_rpt_totals record
816 hr_utility.set_location(gv_package || '.' || l_procedure , 80);
817
818 IF i = 1 THEN -- UPDATING / INSERTING into column value1
819 hr_utility.set_location(gv_package || '.' || l_procedure , 90);
820 UPDATE pay_us_rpt_totals prt
821 SET prt.value1 = NVL(prt.value1,0) + 1
822 WHERE prt.session_id = v_session_id
823 AND prt.organization_id = p_payroll_action_id
824 AND prt.location_id = l_worksite
825 AND prt.state_abbrev = l_state_abbrev
826 AND prt.attribute1 = 'MWS_EST'
827 and prt.attribute2 = l_sui_id
828 and prt.attribute3 = l_fed_ein;
829
830 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
831 hr_utility.set_location(gv_package || '.' || l_procedure , 100);
832
833 INSERT into pay_us_rpt_totals
834 ( session_id
835 ,organization_id
836 ,location_id
837 ,state_abbrev
838 ,attribute1
839 ,attribute2
840 ,attribute3
841 ,value1)
842 VALUES
843 ( v_session_id
844 ,p_payroll_action_id
845 ,l_worksite
846 ,l_state_abbrev
847 ,'MWS_EST'
848 ,l_sui_id
849 ,l_fed_ein
850 ,1);
851 END IF;
852 ELSIF i = 2 THEN -- UPDATING / INSERTING into Column value2
853 hr_utility.set_location(gv_package || '.' || l_procedure , 110);
854 UPDATE pay_us_rpt_totals prt
855 SET prt.value2 = NVL(prt.value2,0) + 1
856 WHERE prt.session_id = v_session_id
857 AND prt.organization_id = p_payroll_action_id
858 AND prt.location_id = l_worksite
859 AND prt.state_abbrev = l_state_abbrev
860 AND prt.attribute1 = 'MWS_EST'
861 and prt.attribute2 = l_sui_id
862 and prt.attribute3 = l_fed_ein;
863
864 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
865 hr_utility.set_location(gv_package || '.' || l_procedure , 120);
866
867 INSERT into pay_us_rpt_totals
868 ( session_id
869 ,organization_id
870 ,location_id
871 ,state_abbrev
872 ,attribute1
873 ,attribute2
874 ,attribute3
875 ,value2)
876 VALUES
877 ( v_session_id
878 ,p_payroll_action_id
879 ,l_worksite
880 ,l_state_abbrev
881 ,'MWS_EST'
882 ,l_sui_id
883 ,l_fed_ein
884 ,1);
885 END IF;
886 ELSIF i = 3 THEN -- UPDATING / INSERTING into column value3
887 hr_utility.set_location(gv_package || '.' || l_procedure , 130);
888 UPDATE pay_us_rpt_totals prt
889 SET prt.value3 = NVL(prt.value3,0) + 1
890 WHERE prt.session_id = v_session_id
891 AND prt.organization_id = p_payroll_action_id
892 AND prt.location_id = l_worksite
893 AND prt.state_abbrev = l_state_abbrev
894 AND prt.attribute1 = 'MWS_EST'
895 and prt.attribute2 = l_sui_id
896 and prt.attribute3 = l_fed_ein;
897
898 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
899 hr_utility.set_location(gv_package || '.' || l_procedure , 140);
900
901 INSERT into pay_us_rpt_totals
902 ( session_id
903 ,organization_id
904 ,location_id
905 ,state_abbrev
906 ,attribute1
907 ,attribute2
908 ,attribute3
909 ,value3)
910 VALUES
911 ( v_session_id
912 ,p_payroll_action_id
913 ,l_worksite
914 ,l_state_abbrev
915 ,'MWS_EST'
916 ,l_sui_id
917 ,l_fed_ein
918 ,1);
919 END IF;
920 ELSE -- UPDATING / INSERTING into column value4
921 hr_utility.set_location(gv_package || '.' || l_procedure , 150);
922
923 UPDATE pay_us_rpt_totals prt
924 SET prt.value4 = NVL(prt.value4,0) + l_archive_value
925 WHERE prt.session_id = v_session_id
926 AND prt.organization_id = p_payroll_action_id
927 AND prt.location_id = l_worksite
928 AND prt.state_abbrev = l_state_abbrev
929 AND prt.attribute1 = 'MWS_EST'
930 and prt.attribute2 = l_sui_id
931 and prt.attribute3 = l_fed_ein;
932
933 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
934 hr_utility.set_location(gv_package || '.' || l_procedure , 160);
935
936 INSERT into pay_us_rpt_totals
937 ( session_id
938 ,organization_id
939 ,location_id
940 ,state_abbrev
941 ,attribute1
942 ,attribute2
943 ,attribute3
944 ,value4)
945 VALUES
946 ( v_session_id
947 ,p_payroll_action_id
948 ,l_worksite
949 ,l_state_abbrev
950 ,'MWS_EST'
951 ,l_sui_id
952 ,l_fed_ein
953 ,l_archive_value);
954 END IF;
955
956 END IF;
957 END IF;
958 END IF;
959
960 END LOOP;
961 FETCH get_pact_asg into l_ass_id, l_tax_unit_id, l_sqwl_assact, l_business_group_id;
962
963 END LOOP;
964
965 CLOSE get_pact_asg;
966
967 return(1);
968
969 END LOAD_RPT_TOTALS;
970
971 FUNCTION get_mwr_values(p_payroll_action_id number
972 ,p_fips_code in varchar2
973 ,p_sui_id in varchar2
974 ,p_est_id in varchar2
975 ,p_fed_ein in varchar2
976 )
977 RETURN varchar2
978 IS
979
980 l_state_code varchar2(2);
981 l_month_1_count number;
982 l_month_2_count number;
983 l_month_3_count number;
984 l_est_wages number(10,0);
985
986 l_return_value varchar2(28);
987 BEGIN
988 -- get the state code.
989 SELECT state_code
990 INTO l_state_code
991 FROM pay_state_rules
992 where fips_code = to_number(p_fips_code);
993
994 -- sum the counts from pay_us_rpt_totals
995 SELECT nvl(sum(prt.value1),0),
996 nvl(sum(prt.value2),0),
997 nvl(sum(prt.value3),0),
998 nvl(sum(prt.value4),0)
999 INTO l_month_1_count,
1000 l_month_2_count,
1001 l_month_3_count,
1002 l_est_wages
1003 FROM pay_us_rpt_totals prt
1004 WHERE prt.organization_id = p_payroll_action_id
1005 AND prt.location_id = to_number(p_est_id)
1006 AND prt.state_abbrev = l_state_code
1007 AND prt.attribute2 = p_sui_id
1008 AND prt.attribute3 = p_fed_ein
1009 and prt.attribute1 = 'MWS_EST';
1010
1011 -- Format the output
1012 l_return_value := lpad(to_char(l_month_1_count),6,0) ||
1013 lpad(to_char(l_month_2_count),6,0) ||
1014 lpad(to_char(l_month_3_count),6,0) ||
1015 lpad(to_char(l_est_wages),10,0);
1016
1017 IF l_return_value = '0000000000000000000000000000' THEN
1018 return ('-999999999999999999999999999');
1019 ELSE
1020 return (l_return_value);
1021 END IF;
1022
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN
1025 return ('-999999999999999999999999999');
1026 END get_mwr_values;
1027
1028 FUNCTION REMOVE_RPT_TOTALS(p_payroll_action_id number)
1029 RETURN NUMBER
1030 IS
1031 BEGIN
1032 DELETE
1033 FROM pay_us_rpt_totals prt
1034 WHERE prt.organization_id = p_payroll_action_id
1035 AND prt.attribute1 = 'MWS_EST';
1036
1037 return (1);
1038 END remove_rpt_totals;
1039
1040
1041 FUNCTION derive_sui_id ( p_state_code in varchar2
1042 ,p_sui_id in varchar2
1043 )
1044 RETURN varchar2
1045 IS
1046
1047 l_return_sui_id varchar2(10);
1048
1049 BEGIN
1050
1051 if p_state_code = 'AZ' OR
1052 p_state_code = 'DE' OR
1053 p_state_code = 'IL' OR
1054 p_state_code = 'LA' OR
1055 p_state_code = 'NY' OR
1056 p_state_code = 'NC' OR
1057 p_state_code = 'PA' OR
1058 p_state_code = 'RI' OR
1059 p_state_code = 'SC' OR
1060 p_state_code = 'TN' OR
1061 p_state_code = 'WA' OR
1062 p_state_code = 'WV' THEN
1063
1064 if instr(p_sui_id,'-') > 0 then
1065 l_return_sui_id :=
1066 lpad(
1067 substr(p_sui_id
1068 ,1
1069 ,instr(p_sui_id,'-') -1
1070 )
1071 ,10
1072 ,'0');
1073 else
1074 l_return_sui_id :=
1075 lpad(substr(p_sui_id,1,10)
1076 ,10
1077 ,'0');
1078 end if;
1079
1080 elsif p_state_code = 'IA' OR
1081 p_state_code = 'KS' THEN
1082
1083 if instr(p_sui_id,'-') > 0 then
1084 l_return_sui_id :=
1085 lpad(
1086 substr(p_sui_id
1087 ,1
1088 ,greatest(6
1089 ,instr(p_sui_id,'-') -1
1090 )
1091 )
1092 ,10
1093 ,'0');
1094 else
1095 l_return_sui_id :=
1096 lpad(substr(p_sui_id,1,10)
1097 ,10
1098 ,'0');
1099 end if;
1100
1101 elsif p_state_code = 'KY' OR
1102 p_state_code = 'MA' OR
1103 p_state_code = 'MI' OR
1104 p_state_code = 'NV' OR
1105 p_state_code = 'OR' OR
1106 p_state_code = 'SD' THEN
1107
1108 l_return_sui_id :=
1109 lpad(
1110 substr(p_sui_id
1111 ,1
1112 ,LENGTH(p_sui_id) -1)
1113 ,10
1114 ,'0');
1115
1116 elsif p_state_code = 'CA' OR
1117 p_state_code = 'MN' OR
1118 p_state_code = 'OH' THEN
1119
1120 l_return_sui_id :=
1121 lpad(
1122 substr(p_sui_id,1,7)
1123 ,10
1124 ,'0');
1125
1126 elsif p_state_code = 'MO' OR
1127 p_state_code = 'PR' OR
1128 p_state_code = 'WI' THEN
1129
1130 l_return_sui_id :=
1131 lpad(
1132 substr(p_sui_id,1,6)
1133 ,10
1134 ,'0');
1135
1136
1137 elsif p_state_code = 'CO' THEN
1138
1139 if length(p_sui_id) = 11
1140 and instr(p_sui_id,'-') = 7
1141 and instr(p_sui_id,'-',1,2) = 10 THEN
1142
1143 l_return_sui_id :=
1144 lpad(
1145 substr(p_sui_id,8,2) ||
1146 substr(p_sui_id,1,6) ||
1147 substr(p_sui_id,11,1)
1148 ,10
1149 ,'0');
1150 else
1151 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1152 end if;
1153
1154
1155 elsif p_state_code = 'FL' THEN
1156
1157 if length(p_sui_id) = 8 then
1158 l_return_sui_id :=
1159 lpad(
1160 substr(p_sui_id,1,7) ||
1161 '0'
1162 ,10
1163 ,'0');
1164 else
1165 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1166 end if;
1167
1168 elsif p_state_code = 'GA' THEN
1169
1170 if instr(p_sui_id,'-') > 0 then
1171 l_return_sui_id :=
1172 lpad(
1173 substr(p_sui_id,1,6) ||
1174 substr(p_sui_id,8,1)
1175 ,10
1176 ,'0');
1177 else
1178 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1179 end if;
1180
1181
1182 elsif p_state_code = 'MD' THEN
1183
1184 if instr(p_sui_id,'-') > 0 then
1185 l_return_sui_id :=
1186 lpad(
1187 substr(p_sui_id,1,9) ||
1188 substr(p_sui_id,11,1)
1189 ,10
1190 ,'0');
1191 else
1192 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1193 end if;
1194
1195 elsif p_state_code = 'MS' THEN
1196
1197 if length(p_sui_id) = 10 THEN
1198 l_return_sui_id := p_sui_id;
1199 else
1200 l_return_sui_id :=
1201 rpad(
1202 substr(p_sui_id,1,8)
1203 ,10
1204 ,'0');
1205 end if;
1206
1207 elsif p_state_code = 'NE' THEN
1208
1209 l_return_sui_id :=
1210 lpad(
1211 substr(p_sui_id,1,10)
1212 ,10
1213 ,'0');
1214
1215 elsif p_state_code = 'UT' THEN
1216
1217 if instr(p_sui_id,'-') > 0
1218 and instr(p_sui_id,'-',1,2) > 0 then
1219 l_return_sui_id :=
1220 lpad(
1221 substr(p_sui_id
1222 ,instr(p_sui_id,'-') + 1
1223 ,6) ||
1224 substr(p_sui_id
1225 ,instr(p_sui_id,'-',-1) + 1
1226 ,1)
1227 ,10
1228 ,'0');
1229 else
1230 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1231 end if;
1232
1233 else
1234 l_return_sui_id :=
1235 lpad(
1236 substr(p_sui_id,1,10)
1237 ,10
1238 ,'0');
1239 end if;
1240
1241 return l_return_sui_id;
1242
1243 END derive_sui_id;
1244
1245 FUNCTION update_global_values(p_estab_ID number,
1246 p_state_abbrev varchar2)
1247
1248 RETURN NUMBER
1249 IS
1250 BEGIN
1251
1252 IF p_estab_id <> pay_us_mwr_reporting_pkg.est_id
1253 OR p_state_abbrev <> pay_us_mwr_reporting_pkg.state_abbrev
1254 OR pay_us_mwr_reporting_pkg.estab_count = 20 THEN
1255 pay_us_mwr_reporting_pkg.estab_count := 0;
1256 pay_us_mwr_reporting_pkg.est_id := p_estab_id;
1257 pay_us_mwr_reporting_pkg.state_abbrev := p_state_abbrev;
1258 ELSE
1259 pay_us_mwr_reporting_pkg.estab_count := pay_us_mwr_reporting_pkg.estab_count + 1;
1260 END IF;
1261
1262
1263 return (pay_us_mwr_reporting_pkg.estab_count);
1264 END update_global_values;
1265
1266 END pay_us_mwr_reporting_pkg;