[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.9 2011/08/08 08:50:00 nkjaladi 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 08-Aug-2011 nkjaladi 115.28 11936382 Modified procedure load_rpt_totals to handle the
70 following scenarios:
71 1. If SUI state change happens in middle of the quarer
72 then the employee would be reported in corresponding
73 location of the quarter instead of 'Invalid Location'.
74 2. For 'MA' resident employees SQWL archives these
75 employees even if the earnigs in resident state
76 is zero. These employees gets picked up by
77 the multi work site report and get reported
78 under 'Invalid Location'. Such employees shouldn't
79 be counted as they are getting reported under the
80 corresponding employee earnings state
81 3. If Location of the employee has override
82 payroll tax state then these MWSR is not
83 considering the override tax state. Modified
84 code to consider the override tax state of
85 the assignment location.
86 /******************************************************************
87 ** Package Local Variables
88 ******************************************************************/
89 gv_package varchar2(50) := 'pay_us_mwr_reporting_pkg';
90
91
92 PROCEDURE get_payroll_action_info (
93 p_payroll_action_id in number,
94 p_start_date out NOCOPY date,
95 p_end_date out NOCOPY date,
96 p_report_qualifier out NOCOPY varchar2,
97 p_report_type out NOCOPY varchar2,
98 p_report_category out NOCOPY varchar2,
99 p_business_group_id out NOCOPY number)
100 IS
101
102 cursor c_payroll_action(cp_payroll_action_id in number) is
103 select ppa.start_date
104 ,ppa.effective_date
105 ,ppa.business_group_id
106 ,ppa.report_qualifier
107 ,ppa.report_type
108 ,ppa.report_category
109 ,ppa.legislative_parameters
110 from pay_payroll_actions ppa
111 where payroll_action_id = cp_payroll_action_id;
112
113 ld_start_date DATE;
114 ld_end_date DATE;
115 ln_business_group_id NUMBER;
116 lv_report_qualifier VARCHAR2(30);
117 lv_report_type VARCHAR2(30);
118 lv_report_category VARCHAR2(30);
119 lv_leg_parameter VARCHAR2(300);
120
121 BEGIN
122 hr_utility.set_location(gv_package || '.get_payroll_action_info', 10);
123
124 open c_payroll_action(p_payroll_action_id);
125 fetch c_payroll_action into
126 ld_start_date, ld_end_date, ln_business_group_id,
127 lv_report_qualifier, lv_report_type,
128 lv_report_category, lv_leg_parameter;
129 if c_payroll_action%notfound then
130 hr_utility.set_location( gv_package || '.get_payroll_action_info',20);
131 hr_utility.raise_error;
132 end if;
133 close c_payroll_action;
134 hr_utility.set_location(gv_package || '.get_payroll_action_info', 30);
135
136
137 hr_utility.set_location(gv_package || '.get_payroll_action_info', 60);
138 p_start_date := ld_start_date;
139 p_end_date := ld_end_date;
140 p_report_qualifier := lv_report_qualifier;
141 p_report_type := lv_report_type;
142 p_report_category := lv_report_category;
143 p_business_group_id := ln_business_group_id;
144
145 hr_utility.set_location(gv_package || '.get_payroll_action_info', 100);
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 p_start_date := NULL;
150 p_end_date := NULL;
151 p_report_qualifier := NULL;
152 p_report_type := NULL;
153 p_report_category := NULL;
154 p_business_group_id := NULL;
155
156 END get_payroll_action_info;
157
158
159 /********************************************************
160 ** Range Code: Multi Threading
161 ********************************************************/
162 PROCEDURE range_cursor ( p_payroll_action_id in number
163 ,p_sql_string out NOCOPY varchar2)
164 IS
165
166 lv_sql_string varchar2(10000);
167
168 ld_start_date DATE;
169 ld_end_date DATE;
170 ln_business_group_id NUMBER;
171 lv_report_qualifier VARCHAR2(30);
172 lv_report_type VARCHAR2(30);
173 lv_report_category VARCHAR2(30);
174
175 ln_tax_unit_id NUMBER;
176 ln_payroll_id NUMBER;
177 ln_consolidation_set_id NUMBER;
178
179 BEGIN
180 hr_utility.set_location(gv_package || '.range_code', 10);
181 get_payroll_action_info (
182 p_payroll_action_id
183 ,ld_start_date
184 ,ld_end_date
185 ,lv_report_qualifier
186 ,lv_report_type
187 ,lv_report_category
188 ,ln_business_group_id);
189 hr_utility.set_location(gv_package || '.range_code', 20);
190
191 lv_sql_string :=
192 'select distinct paa.assignment_id
193 from pay_assignment_actions paa -- SQWL assignment action
194 , pay_payroll_actions ppa
195 where ppa.business_group_id = ' || ln_business_group_id || '
196 and ppa.effective_date between to_date(''' || to_char(ld_start_date, 'dd-mon-yyyy') || ''', ''dd-mon-yyyy'') --Bug 3362257
197 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''', ''dd-mon-yyyy'') --Bug 3362257
198 and ppa.action_type = ''X''
199 and ppa.report_type = ''SQWL''
200 and ppa.action_status =''C''
201 and ppa.payroll_action_id = paa.payroll_action_id
202 and :payroll_action_id is not null
203 order by paa.assignment_id
204 ';
205
206 p_sql_string := lv_sql_string;
207 hr_utility.set_location(gv_package || '.range_code', 50);
208
209 END range_cursor;
210
211 /********************************************************
212 ** Action Creation Code: Multi Threading
213 ********************************************************/
214 PROCEDURE action_creation( p_payroll_action_id in number
215 ,p_start_assignment in number
216 ,p_end_assignment in number
217 ,p_chunk in number)
218
219 IS
220
221 cursor c_get_mwr_asg( cp_business_group_id in number
222 ,cp_start_date in date
223 ,cp_end_date in date
224 ,cp_start_assignment_id in number
225 ,cp_end_assignment_id in number
226 ) is
227 select paa.assignment_id,
228 ppa.effective_date,
229 paa.tax_unit_id,
230 paa.assignment_action_id
231 from pay_assignment_actions paa -- SQWL assignment action
232 , pay_payroll_actions ppa
233 where ppa.business_group_id = cp_business_group_id
234 and ppa.effective_date between cp_start_date
235 and cp_end_date
236 and ppa.action_type = 'X'
237 and ppa.report_type = 'SQWL'
238 and ppa.action_status ='C'
239 and ppa.payroll_action_id = paa.payroll_action_id
240 and paa.assignment_id between cp_start_assignment_id
241 and cp_end_assignment_id;
242
243 ld_start_date DATE;
244 ld_end_date DATE;
245 ln_business_group_id NUMBER;
246 lv_report_qualifier VARCHAR2(30);
247 lv_report_type VARCHAR2(30);
248 lv_report_category VARCHAR2(30);
249 ln_tax_unit_id NUMBER;
250 ln_payroll_id NUMBER;
251 ln_consolidation_set_id NUMBER;
252
253 /* Assignment Record Local Variables */
254 ln_assignment_id NUMBER;
255 ld_effective_date DATE;
256 ln_emp_tax_unit_id NUMBER;
257 ln_assignment_action_id NUMBER;
258
259 ln_locking_action_id NUMBER;
260
261 BEGIN
262 hr_utility.set_location(gv_package || '.action_creation', 10);
263 get_payroll_action_info (
264 p_payroll_action_id
265 ,ld_start_date
266 ,ld_end_date
267 ,lv_report_qualifier
268 ,lv_report_type
269 ,lv_report_category
270 ,ln_business_group_id);
271
272 hr_utility.set_location(gv_package || '.action_creation', 20);
273 open c_get_mwr_asg( ln_business_group_id
274 ,ld_start_date
275 ,ld_end_date
276 ,p_start_assignment
277 ,p_end_assignment);
278 loop
279 hr_utility.set_location(gv_package || '.action_creation', 30);
280 fetch c_get_mwr_asg into ln_assignment_id, ld_effective_date,
281 ln_emp_tax_unit_id, ln_assignment_action_id;
282 if c_get_mwr_asg%notfound then
283 hr_utility.set_location(gv_package || '.action_creation', 40);
284 exit;
285 end if;
286
287 hr_utility.set_location(gv_package || '.action_creation', 50);
288 select pay_assignment_actions_s.nextval
289 into ln_locking_action_id
290 from dual;
291
292 -- **** CHECK FOR SUI WAGES HERE **** ----
293
294 -- insert into pay_assignment_actions.
295 hr_nonrun_asact.insact(ln_locking_action_id, ln_assignment_id,
296 p_payroll_action_id, p_chunk, ln_emp_tax_unit_id);
297 hr_utility.set_location(gv_package || '.action_creation', 60);
298
299 -- insert an interlock to this action
300 hr_nonrun_asact.insint(ln_locking_action_id, ln_assignment_action_id);
301
302 update pay_assignment_actions paa
303 set paa.serial_number = ln_assignment_action_id
304 where paa.assignment_action_id = ln_locking_action_id;
305
306 hr_utility.set_location(gv_package || '.action_creation', 60);
307 end loop;
308 close c_get_mwr_asg;
309
310 hr_utility.set_location(gv_package || '.action_creation', 60);
311 END action_creation;
312
313
314
315 FUNCTION LOAD_RPT_TOTALS( p_payroll_action_id in number)
316 RETURN number
317 IS
318
319 CURSOR get_pact_asg IS
320 SELECT paa.assignment_id
321 ,paa.tax_unit_id
322 ,paa.serial_number
323 ,ppa.business_group_id
324 ,ppa.effective_date
325 FROM pay_payroll_actions ppa,
326 pay_assignment_actions paa
327 WHERE ppa.payroll_action_id = p_payroll_action_id
328 AND ppa.payroll_action_id = paa.payroll_action_id;
329
330 CURSOR c_asg_loc_mon ( p_ass_act_id number
331 ,p_mon_of_qtr number) IS
332 SELECT fai.value,
333 pus.state_code || '-000-0000',
334 pus.state_abbrev
335 FROM ff_archive_items fai
336 ,ff_user_entities ue
337 ,pay_us_states pus
338 ,hr_locations hl
339 where hl.location_id = fai.value
340 and fai.user_entity_id = ue.user_entity_id
341 and ue.user_entity_name =
342 decode(p_mon_of_qtr,4,'A_SQWL_LOC_QTR_END','A_SQWL_LOC_MON_' || to_char(p_mon_of_qtr))
343 and nvl(hl.loc_information17,hl.region_2) = pus.state_abbrev --#11936382 added nvl(hl.loc_information17)
344 and fai.context1 = to_char(p_ass_act_id); -- context of assignment action id
345
346 CURSOR c_get_sui_code ( p_tax_unit_id number,
347 p_jurisdiction varchar2 ) IS
348 SELECT /*+ use_nl (hoi1, hoi2)*/
349 hoi1.org_information2,
350 hoi2.org_information1
351 FROM pay_state_rules SR,
352 hr_organization_information hoi1,
353 hr_organization_information hoi2
354 WHERE hoi1.organization_id = p_tax_unit_id
355 AND hoi1.org_information_context = 'State Tax Rules'
356 AND hoi1.org_information1 = SR.state_code
357 AND SR.jurisdiction_code = substr(p_jurisdiction,1,2)||'-000-0000'
358 AND hoi2.organization_id = hoi1.organization_id
359 AND hoi2.org_information_context = 'Employer Identification' ;
360
361 CURSOR c_get_sui_subject ( p_ass_act_id number,
362 p_user_entity_id number,
363 p_tax_unit_id number,
364 P_jurisdiction_code varchar2
365 ) IS
366 SELECT fai.value
367 FROM ff_archive_item_contexts con3,
368 ff_archive_item_contexts con2,
369 ff_contexts fc3,
370 ff_contexts fc2,
371 ff_archive_items fai
372 WHERE fai.user_entity_id = p_user_entity_id
373 and fai.context1 = to_char(p_ass_act_id)
374 /* context assignment action id */
375 and fc2.context_name = 'TAX_UNIT_ID'
376 and con2.archive_item_id = fai.archive_item_id
377 and con2.context_id = fc2.context_id
378 and ltrim(rtrim(con2.context)) = to_char(p_tax_unit_id)
379 /* 2nd context of tax_unit_id */
380 and fc3.context_name = 'JURISDICTION_CODE'
381 and con3.archive_item_id = fai.archive_item_id
382 and con3.context_id = fc3.context_id
383 and substr(con3.context,1,2) = substr(p_jurisdiction_code,1,2)
384 /* 3rd context of state jurisdiction_code*/;
385
386
387 /*
388 CURSOR c_derive_wksite_estab (p_payroll_action_id number,
389 p_est_hierarchy_id number,
390 p_hierarchy_ver_id number,
391 p_location_id number) IS
392 SELECT pghn2.entity_id
393 FROM per_gen_hierarchy pgh
394 ,per_gen_hierarchy_versions pghv
395 ,per_gen_hierarchy_nodes pghn2 -- establishment organizations
396 ,pay_payroll_actions ppa
397 where ppa.payroll_action_id = p_payroll_action_id
398 and pgh.hierarchy_id = p_est_hierarchy_id
399 and pgh.business_group_id = ppa.business_group_id
400 and pgh.hierarchy_id = pghv.hierarchy_id
401 and pghv.HIERARCHY_VERSION_id = p_hierarchy_ver_id
402 and pghv.hierarchy_version_id = pghn2.hierarchy_version_id
403 and ( ( pghn2.node_type = 'EST'
404 and pghn2.entity_id = p_location_id
405 )
406 OR
407 ( pghn2.node_type = 'EST'
408 AND p_location_id in
409 ( SELECT pghn3.entity_id
410 FROM per_gen_hierarchy_nodes pghn3
411 WHERE pghn3.node_type = 'LOC'
412 AND pghn3.hierarchy_version_id = pghv.HIERARCHY_VERSION_id
413 AND pghn3.parent_hierarchy_node_id = pghn2.hierarchy_node_id
414 )
415 )
416 );
417
418 */
419
420 /* rmonge 15-DEC-2004 */
421 /* Performance bug 4047812 */
422 /* Changed subquery to use index and also changed p_hierarchy_ver_id */
423 /* to pghn2.hierarchy_version_id */
424
425 CURSOR c_derive_wksite_estab (p_hierarchy_ver_id number,
426 p_location_id number) IS
427 SELECT pghn2.entity_id
428 FROM per_gen_hierarchy_nodes pghn2 -- establishment organizations
429
430 where p_hierarchy_ver_id = pghn2.hierarchy_version_id
431 and ( ( pghn2.node_type = 'EST'
432 and pghn2.entity_id = p_location_id
433 )
434 OR
435 ( pghn2.node_type = 'EST'
436 AND p_location_id in
437 ( SELECT /*+ pghn3 PER_GEN_HIER_NOD_VER_N4 */ pghn3.entity_id
438 FROM per_gen_hierarchy_nodes pghn3
439 WHERE pghn3.node_type = 'LOC'
440 AND pghn3.hierarchy_version_id =
441 pghn2.hierarchy_version_id --p_hierarchy_ver_id
442 AND pghn3.parent_hierarchy_node_id =
443 pghn2.hierarchy_node_id
444 )
445 )
446 );
447
448
449
450 CURSOR c_get_sqwl_month_count ( cp_sqwl_assact in number,
451 cp_month_of_quarter in number)
452 IS
453
454 select fai.value,
455 ppa.report_qualifier
456 from ff_archive_items fai,
457 ff_user_entities ue,
458 pay_assignment_actions paa,
459 pay_payroll_actions ppa
460 where fai.context1 = cp_sqwl_assact
461 and paa.assignment_action_id = fai.context1
462 and fai.user_entity_id = ue.user_entity_id
463 and ue.user_entity_name = 'A_SQWL_MONTH' || to_char(cp_month_of_quarter) || '_COUNT'
464 and ppa.payroll_action_id = paa.payroll_action_id ;
465
466 l_month_count_state_code varchar2(2);
467 l_month_count_code varchar2(2); --# 11936382
468 l_est_hierarchy_id number;
469 l_hierarchy_ver_id number;
470 l_ass_id number;
471 l_sqwl_assact number;
472 l_business_group_id number;
473 v_session_id number;
474 l_location_id number;
475 l_jurisdiction varchar2(11);
476 l_sqwl_jurisdiction_code varchar2(11);
477 l_wage_jurisdiction_code varchar2(11);
478 l_state_abbrev varchar2(2);
479 l_estab_loc_id number;
480 l_archive_value number;
481 l_sit_exists varchar2(1);
482 l_user_entity_id number;
483 l_tax_unit_id number;
484 l_sui_id varchar2(50);
485 l_fed_ein varchar2(50);
486 l_worksite number;
487 l_ppa_legislative_parameters varchar2(2000);
488 l_procedure varchar2(15) := 'load_rpt_totals';
489 /* #11936382 start */
490 l_effective_date date;
491 l_state_count number;
492 l_location_invalid number := 0;
493 l_new_location_id number;
494 l_ma_resides_true varchar2(1);
495 l_sui_state_abbrev varchar2(10);
496 l_work_at_home varchar2(10);
497 /* #11936382 end */
498 FUNCTION calc_sui_reductions ( p_sqwl_assact in number
499 ,p_tax_unit_id in number
500 ,p_jurisdiction in varchar2)
501 RETURN number
502 IS
503
504 CURSOR c_get_sui_reds ( cp_ass_act_id number,
505 cp_user_entity_id number,
506 cp_tax_unit_id number,
507 cp_jurisdiction_code varchar2
508 ) IS
509 SELECT fai.value
510 FROM ff_archive_item_contexts con3,
511 ff_archive_item_contexts con2,
512 ff_contexts fc3,
513 ff_contexts fc2,
514 ff_archive_items fai
515 WHERE fai.user_entity_id = cp_user_entity_id
516 and fai.context1 = to_char(cp_ass_act_id)
517 /* context assignment action id */
518 and fc2.context_name = 'TAX_UNIT_ID'
519 and con2.archive_item_id = fai.archive_item_id
520 and con2.context_id = fc2.context_id
521 and ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
522 /* 2nd context of tax_unit_id */
523 and fc3.context_name = 'JURISDICTION_CODE'
524 and con3.archive_item_id = fai.archive_item_id
525 and con3.context_id = fc3.context_id
526 and substr(con3.context,1,2) = substr(cp_jurisdiction_code,1,2)
527 /* 3rd context of state jurisdiction_code*/;
528
529 l_sui_total_reductions number;
530 l_user_entity_id number;
531 BEGIN
532
533 SELECT fue.user_entity_id
534 INTO l_user_entity_id
535 FROM ff_user_entities fue
536 WHERE fue.user_entity_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
537 AND fue.legislation_code = 'US';
538
539 OPEN c_get_sui_reds( p_sqwl_assact
540 ,l_user_entity_id
541 ,p_tax_unit_id
542 ,p_jurisdiction);
543
544 FETCH c_get_sui_reds INTO l_sui_total_reductions;
545
546 IF c_get_sui_reds%NOTFOUND THEN
547 l_sui_total_reductions :=0;
548 END IF;
549
550 CLOSE c_get_sui_reds;
551
552 return(l_sui_total_reductions);
553
554 EXCEPTION
555 WHEN NO_DATA_FOUND THEN
556 RETURN(0);
557 END calc_sui_reductions;
558 /* 11936382 Added this following procedure to check for SUI state
559 change*/
560 FUNCTION check_sui_state_change ( p_assignment_id in number
561 ,p_business_group_id in number
562 ,p_tax_unit_id in number
563 ,p_effective_date in date
564 ,p_loc_state_code in varchar2
565 ,p_sqwl_state_code in varchar2)
566 RETURN number IS
567
568 CURSOR emp_qtr_sui_state_count IS
569 SELECT COUNT(DISTINCT peft.sui_state_code)
570 FROM pay_us_emp_fed_tax_rules_f peft,
571 per_all_assignments_f paa,
572 hr_soft_coding_keyflex hscf
573 WHERE peft.effective_end_date >= trunc(p_effective_date,'Q')
574 AND peft.effective_start_date <= p_effective_date
575 AND peft.assignment_id = p_assignment_id
576 AND peft.business_group_id = p_business_group_id
577 AND peft.sui_state_code in (p_loc_state_code,p_sqwl_state_code)
578 AND paa.assignment_id = peft.assignment_id
579 AND paa.business_group_id = peft.business_group_id
580 AND paa.effective_end_date >= trunc(p_effective_date,'Q')
581 AND paa.effective_start_date <= p_effective_date
582 AND paa.business_group_id = peft.business_group_id
583 AND hscf.segment1 = p_tax_unit_id
584 AND hscf.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
585
586 l_qtr_sui_state_count NUMBER := 0;
587
588 BEGIN
589
590 hr_utility.trace('Inside check_work_loc_change');
591
592 OPEN emp_qtr_sui_state_count;
593
594 FETCH emp_qtr_sui_state_count INTO l_qtr_sui_state_count;
595
596 CLOSE emp_qtr_sui_state_count;
597
598 hr_utility.trace('Returning '||l_qtr_sui_state_count||' from check_sui_state_change');
599
600 return nvl(l_qtr_sui_state_count,0);
601
602 EXCEPTION
603 WHEN OTHERS THEN
604 hr_utility.trace('Exception raised in check_sui_state_change');
605 hr_utility.raise_error;
606 END check_sui_state_change;
607 /* 11936382 Added this following procedure to derive tbe reporting
608 location under MWSR*/
609 FUNCTION get_location_id( p_assignment_id in number
610 ,p_business_group_id in number
611 ,p_tax_unit_id in number
612 ,p_effective_date in date
613 ,p_state_code in varchar2 )
614 RETURN NUMBER
615
616 IS
617
618 l_location_id NUMBER;
619
620 CURSOR asg_with_action_loc_id IS
621 SELECT paaf.location_id
622 FROM per_all_assignments_f paaf,
623 hr_locations_all hl,
624 pay_us_states pus
625 WHERE paaf.effective_start_date <= p_effective_date
626 AND paaf.effective_end_date >= trunc(p_effective_date,'Q')
627 AND paaf.assignment_id = p_assignment_id
628 AND paaf.business_group_id = p_business_group_id
629 AND paaf.location_id = hl.location_id
630 AND nvl(hl.loc_information17,hl.region_2) = pus.state_abbrev
631 AND pus.state_code = p_state_code
632 AND EXISTS
633 (
634 SELECT null
635 FROM pay_assignment_actions paa,
636 pay_payroll_actions ppa
637 WHERE ppa.effective_date BETWEEN trunc(p_effective_date,'Q')
638 AND p_effective_date
639 AND ppa.payroll_action_id = paa.payroll_action_id
640 AND paa.tax_unit_id = p_tax_unit_id
641 AND paa.assignment_id = paaf.assignment_id
642 )
643 ORDER BY paaf.effective_end_date desc;
644
645 CURSOR assignment_loc_id IS
646 SELECT paaf.location_id
647 FROM per_all_assignments_f paaf,
648 hr_locations_all hl,
649 pay_us_states pus
650 WHERE paaf.effective_start_date <= p_effective_date
651 AND paaf.effective_end_date >= trunc(p_effective_date,'Q')
652 AND paaf.assignment_id = p_assignment_id
653 AND paaf.business_group_id = p_business_group_id
654 AND paaf.location_id = hl.location_id
655 AND nvl(hl.loc_information17,hl.region_2) = pus.state_abbrev
656 AND pus.state_code = p_state_code
657 ORDER BY paaf.effective_end_date desc;
658
659 BEGIN
660
661 OPEN asg_with_action_loc_id;
662 FETCH asg_with_action_loc_id INTO l_location_id;
663
664 IF asg_with_action_loc_id%NOTFOUND THEN
665
666 OPEN assignment_loc_id;
667 FETCH assignment_loc_id INTO l_location_id;
668
669 IF assignment_loc_id%NOTFOUND THEN
670 l_location_id := -99999;
671 END IF;
672
673 CLOSE assignment_loc_id;
674
675 END IF;
676
677 CLOSE asg_with_action_loc_id;
678 hr_utility.trace('Returing location_id as '||l_location_id||' from pay_us_mwr_reporting_pkg.get_location_id');
679 RETURN l_location_id;
680
681 EXCEPTION
682 WHEN OTHERS THEN
683 hr_utility.trace('Exception raised in get_location_id');
684 hr_utility.raise_error;
685 END get_location_id;
686 /* 11936382 end*/
687
688
689 BEGIN
690
691 hr_utility.set_location(gv_package || '.' || l_procedure , 10);
692
693 --get the session id
694 SELECT userenv('sessionid')
695 INTO v_session_id
696 FROM dual;
697
698 --get the hierarchy version and establishment from legislative paramters
699 SELECT ppa.legislative_parameters
700 INTO l_ppa_legislative_parameters
701 FROM pay_payroll_actions ppa
702 where ppa.payroll_action_id = p_payroll_action_id;
703
704 hr_utility.set_location(gv_package || '.' || l_procedure , 20);
705
706 l_est_hierarchy_id :=
707 to_number(pay_mag_utils.get_parameter('TRANSFER_HIERARCHY_ID'
708 ,'TRANSFER_HIERARCHY_VERSION'
709 ,l_ppa_legislative_parameters));
710 l_hierarchy_ver_id :=
711 pay_mag_utils.get_parameter('TRANSFER_HIERARCHY_VERSION'
712 ,''
713 ,l_ppa_legislative_parameters);
714
715 hr_utility.set_location(gv_package || '.' || l_procedure , 30);
716
717 -- cusror loop.
718 OPEN get_pact_asg;
719
720 FETCH get_pact_asg into l_ass_id, l_tax_unit_id, l_sqwl_assact, l_business_group_id, l_effective_date;
721 WHILE get_pact_asg%FOUND LOOP
722
723 hr_utility.set_location(gv_package || '.' || l_procedure , 40);
724
725 FOR i IN 1 .. 4 LOOP
726 OPEN c_asg_loc_mon(l_sqwl_assact,
727 i);
728 Fetch c_asg_loc_mon into l_location_id, l_jurisdiction, l_state_abbrev;
729 IF c_asg_loc_mon%NOTFOUND THEN
730 CLOSE c_asg_loc_mon;
731 l_location_id := NULL;
732 l_jurisdiction := NULL;
733 l_state_abbrev := NULL;
734 ELSE
735 CLOSE C_ASG_LOC_MON;
736 l_sit_exists := 'Y';
737 l_wage_jurisdiction_code := l_jurisdiction;
738
739 hr_utility.set_location(gv_package || '.' || l_procedure , 50);
740
741 -- get the sui ID for the SQWL assignment action
742 OPEN c_get_sui_code( l_tax_unit_id, l_jurisdiction );
743
744 FETCH c_get_sui_code into l_sui_id, l_fed_ein;
745 IF c_get_sui_code%NOTFOUND THEN
746 l_sui_ID := lpad(' ',50,0);
747 l_fed_ein := lpad(' ',50,0);
748 END IF;
749 CLOSE c_get_sui_code;
750
751 -- get the user_entity_id for the 'A_SIT_GROSS_PER_JD_GRE_MON_*' DBI
752 IF i < 4 THEN
753 SELECT ue.user_entity_id
754 INTO l_user_entity_id
755 FROM ff_user_entities ue
756 WHERE ue.user_entity_name = 'A_SIT_GROSS_PER_JD_GRE_MON_' || to_char(i)
757 AND ue.legislation_code = 'US';
758 ELSE
759 SELECT ue.user_entity_id
760 INTO l_user_entity_id
761 FROM ff_user_entities ue
762 WHERE ue.user_entity_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
763 AND ue.legislation_code = 'US';
764 END IF;
765 -- As of Q 1 2002 we no longer archive the A_SIT_GROSS_PER_JD_GRE_MON*
766 -- data (used for month counts in the sqwl). We will not archive a new
767 -- Datat base item named A_SQWL_MONTH*_COUNT (where * is 1, 2, 3).
768 -- need to check for the existance of the NEW DBI first and if not
769 -- found revert back to the old DBI's (this is for re-runs of Multiple
770 -- work site report prior to Q 1 2002). For a 1 thru 4 loop we will still
771 -- fetch the A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD which stores the quarterly
772 -- wages.
773
774 OPEN c_get_sqwl_month_count ( l_sqwl_assact,
775 i );
776
777 FETCH c_get_sqwl_month_count
778 INTO l_archive_value,
779 l_month_count_state_code;
780 IF c_get_sqwl_month_count%NOTFOUND THEN
781 close c_get_sqwl_month_count;
782
783 -- get the value for the A_SIT_GROSS_PER_JD_GRE_MON_* archive item.
784
785 hr_utility.set_location(gv_package || '.' || l_procedure , 60);
786 OPEN c_get_sui_subject ( l_sqwl_assact
787 ,l_user_entity_id
788 ,l_tax_unit_id
789 ,l_jurisdiction);
790
791 FETCH c_get_sui_subject into l_archive_value;
792 IF c_get_sui_subject%NOTFOUND THEN
793 -- If get sit_gross is not found then need to check the jurisdiction
794 -- of the SQWL assignment action.
795 CLOSE c_get_sui_subject;
796 BEGIN
797 SELECT psr.jurisdiction_code
798 INTO l_sqwl_jurisdiction_code
799 FROM pay_assignment_actions paa,
800 pay_payroll_actions ppa,
801 pay_state_rules psr
802 WHERE paa.assignment_action_id = l_sqwl_assact
803 AND ppa.payroll_action_id = paa.payroll_action_id
804 AND psr.state_code = ppa.report_qualifier;
805
806 l_wage_jurisdiction_code := l_sqwl_jurisdiction_code;
807 OPEN c_get_sui_subject ( l_sqwl_assact
808 ,l_user_entity_id
809 ,l_tax_unit_id
810 ,l_sqwl_jurisdiction_code);
811
812 FETCH c_get_sui_subject into l_archive_value;
813 IF c_get_sui_subject%NOTFOUND THEN
814 l_archive_value := 0;
815 CLOSE c_get_sui_subject;
816 ELSE
817 -- Need to verify if there is a SUI State Change for this Assignment
818 l_location_invalid := 0;
819
820 l_state_count := check_sui_state_change ( l_ass_id
821 ,l_business_group_id
822 ,l_tax_unit_id
823 ,l_effective_date
824 ,substr(l_jurisdiction,1,2)
825 ,substr(l_sqwl_jurisdiction_code,1,2));
826 IF l_state_count = 2 THEN
827
828 l_new_location_id := get_location_id ( l_ass_id
829 ,l_business_group_id
830 ,l_tax_unit_id
831 ,l_effective_date
832 ,substr(l_sqwl_jurisdiction_code,1,2));
833
834 IF l_new_location_id <> -99999 THEN
835
836 l_location_id := l_new_location_id;
837
838 ELSE
839
840 l_location_invalid := 1;
841
842 END IF; /*l_new_location_id <> -99999 if*/
843
844 ELSE
845
846 l_location_invalid := 1;
847
848 END IF; /*l_state_count = 2*/
849 -- Need to set l_state_code to the state of the l_sqwl_jurisdiction
850
851 SELECT report_qualifier
852 INTO l_state_abbrev
853 FROM pay_assignment_actions paa,
854 pay_payroll_actions ppa
855 WHERE ppa.payroll_action_id = paa.payroll_action_id
856 AND paa.assignment_action_id = l_sqwl_assact;
857 CLOSE c_get_sui_subject;
858
859 -- also need to point the SUI id to the of the state where wages were paid
860 -- verses the sui id of the assignment location state.
861
862 SELECT hoi1.org_information2
863 INTO l_sui_id
864 FROM pay_state_rules SR,
865 hr_organization_information hoi1
866 WHERE hoi1.organization_id = l_tax_unit_id
867 AND hoi1.org_information_context = 'State Tax Rules'
868 AND hoi1.org_information1 = SR.state_code
869 AND SR.jurisdiction_code =
870 substr(l_sqwl_jurisdiction_code,1,2)||'-000-0000';
871 /* #11936382 start */
872 IF l_location_invalid = 1 THEN
873
874 -- if we've gotten this far, then wages are in a different state
875 -- than assignment work location. Need to set the l_location_id
876 -- to -99999
877
878 BEGIN
879 SELECT NVL(paf.work_at_home,'N')
880 INTO l_work_at_home
881 FROM per_all_assignments_f paf
882 WHERE paf.assignment_id = l_ass_id
883 AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
884 EXCEPTION
885 WHEN OTHERS THEN
886 hr_utility.trace('l_work_at_home exception ');
887 NULL;
888 END;
889
890 hr_utility.set_location('99999-1 assignment_id:assignment_action_id: '||l_ass_id||':'||l_sqwl_assact,99999);
891 l_location_id := -99999;
892
893 l_location_invalid := 0;
894
895 END IF; /*l_location_invalid if */
896 /* #11936382 end */
897 END IF;
898
899 EXCEPTION when NO_DATA_FOUND THEN
900
901 l_archive_value := 0;
902 END;
903 ELSE
904 CLOSE c_get_sui_subject;
905
906 END IF;
907
908 -- Need to see it SIT exists in the state where we just retrived
909 -- the archive value, as there are 9 states that have no SIT and
910 -- the arcive value will be 0. They must still be counted on
911 -- the report.
912 l_sit_exists := 'Y';
913
914 Select psif.sit_exists
915 into l_sit_exists
916 from pay_us_state_tax_info_f psif,
917 pay_payroll_actions ppa
918 where ppa.payroll_action_id = p_payroll_action_id
919 and psif.state_code = substr(l_jurisdiction,1,2)
920 and ppa.effective_date
921 BETWEEN psif.effective_start_date AND psif.effective_end_date
922 and sta_information_category = 'State tax limit rate info';
923
924 ELSE
925 -- compare the state code retured from the c_get_sqwl_month_count
926 -- to the state code of the locations ID, if = then fine, else
927 -- change the jurisdiction_code to that of the state that is
928 -- returned in the c_get_sqwl_month_cursor.
929 close c_get_sqwl_month_count;
930
931 l_sit_exists := 'Y';
932
933 IF l_state_abbrev = l_month_count_state_code THEN
934 NULL;
935 ELSE
936
937 -- Need to verify if there is a SUI State Change for this Assignment
938 --# 11936382 Start
939 SELECT state_code INTO l_month_count_code
940 FROM pay_us_states
941 WHERE state_abbrev = l_month_count_state_code;
942 l_state_count := check_sui_state_change ( l_ass_id
943 ,l_business_group_id
944 ,l_tax_unit_id
945 ,l_effective_date
946 ,substr(l_jurisdiction,1,2)
947 ,l_month_count_code);
948 l_location_invalid := 0;
949
950 IF l_state_count = 2 THEN
951 l_new_location_id := get_location_id ( l_ass_id
952 ,l_business_group_id
953 ,l_tax_unit_id
954 ,l_effective_date
955 ,l_month_count_code);
956
957 IF l_new_location_id <> -99999 THEN
958
959 l_location_id := l_new_location_id;
960 l_state_abbrev := l_month_count_state_code;
961
962 ELSE
963
964 l_location_invalid := 1;
965
966 END IF; /*l_new_location_id <> -99999 if*/
967
968 ELSE
969
970 l_location_invalid := 1;
971
972 END IF; /*l_state_count = 2*/
973 --# 11936382 end
974
975 -- Bug fix 5399921 START
976
977 SELECT psr.jurisdiction_code
978 INTO l_sqwl_jurisdiction_code
979 FROM pay_assignment_actions paa,
980 pay_payroll_actions ppa,
981 pay_state_rules psr
982 WHERE paa.assignment_action_id = l_sqwl_assact
983 AND ppa.payroll_action_id = paa.payroll_action_id
984 AND psr.state_code = ppa.report_qualifier;
985
986 SELECT hoi1.org_information2
987 INTO l_sui_id
988 FROM pay_state_rules SR,
989 hr_organization_information hoi1
990 WHERE hoi1.organization_id = l_tax_unit_id
991 AND hoi1.org_information_context = 'State Tax Rules'
992 AND hoi1.org_information1 = SR.state_code
993 AND SR.jurisdiction_code = substr(l_sqwl_jurisdiction_code,1,2)||'-000-0000';
994
995 l_state_abbrev := l_month_count_state_code;
996
997 -- Bug fix 5399921 End
998 --# 11936382 Start
999 IF (l_state_abbrev = 'MA') THEN
1000 BEGIN
1001 SELECT '1'
1002 INTO l_ma_resides_true
1003 FROM dual
1004 WHERE EXISTS (
1005 SELECT '1'
1006 FROM per_assignments_f paf,
1007 per_addresses pad
1008 WHERE paf.assignment_id = l_ass_id
1009 AND paf.person_id = pad.person_id
1010 AND pad.date_from <= l_effective_date
1011 AND NVL(pad.date_to ,l_effective_date) >= trunc(l_effective_date,'Q')
1012 AND pad.region_2 = l_state_abbrev
1013 AND pad.primary_flag = 'Y');
1014 l_location_invalid := 0;
1015 EXCEPTION
1016 when no_data_found then
1017 l_ma_resides_true := '0';
1018 END;
1019 IF l_ma_resides_true = '1' THEN
1020 -- get the user_entity_id for the 'A_SIT_GROSS_PER_JD_GRE_MON_*' DBI
1021 hr_utility.trace('l_ma_resides find user entity');
1022 SELECT ue.user_entity_id
1023 INTO l_user_entity_id
1024 FROM ff_user_entities ue
1025 WHERE ue.user_entity_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
1026 AND ue.legislation_code = 'US';
1027
1028 OPEN c_get_sui_subject ( l_sqwl_assact
1029 ,l_user_entity_id
1030 ,l_tax_unit_id
1031 ,l_sqwl_jurisdiction_code);
1032
1033 FETCH c_get_sui_subject into l_archive_value;
1034 IF c_get_sui_subject%NOTFOUND THEN
1035 l_archive_value := 0;
1036 END IF;
1037 CLOSE c_get_sui_subject;
1038 IF l_archive_value = 0 THEN
1039 hr_utility.set_location('99999-11936382 MA employee Exiting Loop',99999);
1040 EXIT;
1041 END IF;
1042 END IF;
1043 END IF;
1044 --# 11936382 end
1045
1046 IF l_location_invalid = 1 THEN
1047 hr_utility.set_location('99999-2 assignment_id:assignment_action_id: '||l_ass_id||':'||l_sqwl_assact,99999);
1048 l_location_id := -99999;
1049 l_location_invalid := 0;
1050 END IF;
1051 END IF;
1052
1053 END IF; -- if c_get_sqwl_month_count%NOT FOUND
1054
1055 -- If I've gotten this far then I know location_id is not null.
1056 IF l_archive_value <> 0 OR
1057 (l_sit_exists = 'N' and
1058 l_jurisdiction is not NULL ) THEN
1059
1060 /* if i = 4 we are getting the sui wages All states report reduced SUI
1061 subject wages using the formula
1062
1063 SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD -
1064 SUI_ER_125_REDNS_PER_JD_GRE_QTD -
1065 SUI_ER_401_REDNS_PER_JD_GRE_QTD -
1066 SUI_ER_DEP_CARE_REDNS_PER_JD_GRE_QTD
1067
1068 except Ohio State_abbrev 'OH' and Wyoming State_abbrev 'OH'
1069 which use SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD
1070 */
1071 IF i = 4
1072 /* bug 2914661 and
1073 l_state_abbrev <> 'OH' and
1074 l_state_abbrev <> 'WY'*/
1075 THEN
1076 l_archive_value := l_archive_value -
1077 calc_sui_reductions ( l_sqwl_assact
1078 ,l_tax_unit_id
1079 ,l_wage_jurisdiction_code);
1080 END IF;
1081
1082
1083 -- derive the establishment
1084 hr_utility.set_location(gv_package || '.' || l_procedure , 70);
1085
1086 IF l_location_id <> -99999 THEN
1087 OPEN c_derive_wksite_estab( l_hierarchy_ver_id,
1088 l_location_id);
1089
1090 FETCH c_derive_wksite_estab into l_worksite;
1091
1092 IF c_derive_wksite_estab%NOTFOUND THEN
1093 hr_utility.set_location('99999-3 assignment_id:assignment_action_id: '||l_ass_id||':'||l_sqwl_assact,99999);
1094 l_worksite := -99999;
1095 END IF;
1096
1097 CLOSE c_derive_wksite_estab;
1098 ELSE
1099 l_worksite := -99999;
1100 END IF;
1101
1102 -- Write the us_rpt_totals record
1103 hr_utility.set_location(gv_package || '.' || l_procedure , 80);
1104
1105 IF i = 1 THEN -- UPDATING / INSERTING into column value1
1106 hr_utility.set_location(gv_package || '.' || l_procedure , 90);
1107 UPDATE pay_us_rpt_totals prt
1108 SET prt.value1 = NVL(prt.value1,0) + 1
1109 WHERE prt.session_id = v_session_id
1110 AND prt.organization_id = p_payroll_action_id
1111 AND prt.location_id = l_worksite
1112 AND prt.state_abbrev = l_state_abbrev
1113 AND prt.attribute1 = 'MWS_EST'
1114 and prt.attribute2 = l_sui_id
1115 and prt.attribute3 = l_fed_ein;
1116
1117 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
1118 hr_utility.set_location(gv_package || '.' || l_procedure , 100);
1119
1120 INSERT into pay_us_rpt_totals
1121 ( session_id
1122 ,organization_id
1123 ,location_id
1124 ,state_abbrev
1125 ,attribute1
1126 ,attribute2
1127 ,attribute3
1128 ,value1)
1129 VALUES
1130 ( v_session_id
1131 ,p_payroll_action_id
1132 ,l_worksite
1133 ,l_state_abbrev
1134 ,'MWS_EST'
1135 ,l_sui_id
1136 ,l_fed_ein
1137 ,1);
1138 END IF;
1139 ELSIF i = 2 THEN -- UPDATING / INSERTING into Column value2
1140 hr_utility.set_location(gv_package || '.' || l_procedure , 110);
1141 UPDATE pay_us_rpt_totals prt
1142 SET prt.value2 = NVL(prt.value2,0) + 1
1143 WHERE prt.session_id = v_session_id
1144 AND prt.organization_id = p_payroll_action_id
1145 AND prt.location_id = l_worksite
1146 AND prt.state_abbrev = l_state_abbrev
1147 AND prt.attribute1 = 'MWS_EST'
1148 and prt.attribute2 = l_sui_id
1149 and prt.attribute3 = l_fed_ein;
1150
1151 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
1152 hr_utility.set_location(gv_package || '.' || l_procedure , 120);
1153
1154 INSERT into pay_us_rpt_totals
1155 ( session_id
1156 ,organization_id
1157 ,location_id
1158 ,state_abbrev
1159 ,attribute1
1160 ,attribute2
1161 ,attribute3
1162 ,value2)
1163 VALUES
1164 ( v_session_id
1165 ,p_payroll_action_id
1166 ,l_worksite
1167 ,l_state_abbrev
1168 ,'MWS_EST'
1169 ,l_sui_id
1170 ,l_fed_ein
1171 ,1);
1172 END IF;
1173 ELSIF i = 3 THEN -- UPDATING / INSERTING into column value3
1174 hr_utility.set_location(gv_package || '.' || l_procedure , 130);
1175 UPDATE pay_us_rpt_totals prt
1176 SET prt.value3 = NVL(prt.value3,0) + 1
1177 WHERE prt.session_id = v_session_id
1178 AND prt.organization_id = p_payroll_action_id
1179 AND prt.location_id = l_worksite
1180 AND prt.state_abbrev = l_state_abbrev
1181 AND prt.attribute1 = 'MWS_EST'
1182 and prt.attribute2 = l_sui_id
1183 and prt.attribute3 = l_fed_ein;
1184
1185 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
1186 hr_utility.set_location(gv_package || '.' || l_procedure , 140);
1187
1188 INSERT into pay_us_rpt_totals
1189 ( session_id
1190 ,organization_id
1191 ,location_id
1192 ,state_abbrev
1193 ,attribute1
1194 ,attribute2
1195 ,attribute3
1196 ,value3)
1197 VALUES
1198 ( v_session_id
1199 ,p_payroll_action_id
1200 ,l_worksite
1201 ,l_state_abbrev
1202 ,'MWS_EST'
1203 ,l_sui_id
1204 ,l_fed_ein
1205 ,1);
1206 END IF;
1207 ELSE -- UPDATING / INSERTING into column value4
1208 hr_utility.set_location(gv_package || '.' || l_procedure , 150);
1209
1210 UPDATE pay_us_rpt_totals prt
1211 SET prt.value4 = NVL(prt.value4,0) + l_archive_value
1212 WHERE prt.session_id = v_session_id
1213 AND prt.organization_id = p_payroll_action_id
1214 AND prt.location_id = l_worksite
1215 AND prt.state_abbrev = l_state_abbrev
1216 AND prt.attribute1 = 'MWS_EST'
1217 and prt.attribute2 = l_sui_id
1218 and prt.attribute3 = l_fed_ein;
1219
1220 IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
1221 hr_utility.set_location(gv_package || '.' || l_procedure , 160);
1222
1223 INSERT into pay_us_rpt_totals
1224 ( session_id
1225 ,organization_id
1226 ,location_id
1227 ,state_abbrev
1228 ,attribute1
1229 ,attribute2
1230 ,attribute3
1231 ,value4)
1232 VALUES
1233 ( v_session_id
1234 ,p_payroll_action_id
1235 ,l_worksite
1236 ,l_state_abbrev
1237 ,'MWS_EST'
1238 ,l_sui_id
1239 ,l_fed_ein
1240 ,l_archive_value);
1241 END IF;
1242
1243 END IF;
1244 END IF;
1245 END IF;
1246
1247 END LOOP;
1248 hr_utility.set_location(gv_package || '.' || l_procedure , 180);
1249 FETCH get_pact_asg into l_ass_id, l_tax_unit_id, l_sqwl_assact, l_business_group_id, l_effective_date;
1250
1251 END LOOP;
1252
1253 CLOSE get_pact_asg;
1254
1255 return(1);
1256
1257 END LOAD_RPT_TOTALS;
1258
1259 FUNCTION get_mwr_values(p_payroll_action_id number
1260 ,p_fips_code in varchar2
1261 ,p_sui_id in varchar2
1262 ,p_est_id in varchar2
1263 ,p_fed_ein in varchar2
1264 )
1265 RETURN varchar2
1266 IS
1267
1268 l_state_code varchar2(2);
1269 l_month_1_count number;
1270 l_month_2_count number;
1271 l_month_3_count number;
1272 l_est_wages number(10,0);
1273
1274 l_return_value varchar2(28);
1275 BEGIN
1276 -- get the state code.
1277 SELECT state_code
1278 INTO l_state_code
1279 FROM pay_state_rules
1280 where fips_code = to_number(p_fips_code);
1281
1282 -- sum the counts from pay_us_rpt_totals
1283 SELECT nvl(sum(prt.value1),0),
1284 nvl(sum(prt.value2),0),
1285 nvl(sum(prt.value3),0),
1286 nvl(sum(prt.value4),0)
1287 INTO l_month_1_count,
1288 l_month_2_count,
1289 l_month_3_count,
1290 l_est_wages
1291 FROM pay_us_rpt_totals prt
1292 WHERE prt.organization_id = p_payroll_action_id
1293 AND prt.location_id = to_number(p_est_id)
1294 AND prt.state_abbrev = l_state_code
1295 AND prt.attribute2 = p_sui_id
1296 AND prt.attribute3 = p_fed_ein
1297 and prt.attribute1 = 'MWS_EST';
1298
1299 -- Format the output
1300 l_return_value := lpad(to_char(l_month_1_count),6,0) ||
1301 lpad(to_char(l_month_2_count),6,0) ||
1302 lpad(to_char(l_month_3_count),6,0) ||
1303 lpad(to_char(l_est_wages),10,0);
1304
1305 IF l_return_value = '0000000000000000000000000000' THEN
1306 return ('-999999999999999999999999999');
1307 ELSE
1308 return (l_return_value);
1309 END IF;
1310
1311 EXCEPTION
1312 WHEN NO_DATA_FOUND THEN
1313 return ('-999999999999999999999999999');
1314 END get_mwr_values;
1315
1316 FUNCTION REMOVE_RPT_TOTALS(p_payroll_action_id number)
1317 RETURN NUMBER
1318 IS
1319 BEGIN
1320 DELETE
1321 FROM pay_us_rpt_totals prt
1322 WHERE prt.organization_id = p_payroll_action_id
1323 AND prt.attribute1 = 'MWS_EST';
1324
1325 return (1);
1326 END remove_rpt_totals;
1327
1328
1329 FUNCTION derive_sui_id ( p_state_code in varchar2
1330 ,p_sui_id in varchar2
1331 )
1332 RETURN varchar2
1333 IS
1334
1335 l_return_sui_id varchar2(10);
1336
1337 BEGIN
1338
1339 if p_state_code = 'AZ' OR
1340 p_state_code = 'DE' OR
1341 p_state_code = 'IL' OR
1342 p_state_code = 'LA' OR
1343 p_state_code = 'NY' OR
1344 p_state_code = 'NC' OR
1345 p_state_code = 'PA' OR
1346 p_state_code = 'RI' OR
1347 p_state_code = 'SC' OR
1348 p_state_code = 'TN' OR
1349 p_state_code = 'WA' OR
1350 p_state_code = 'WV' THEN
1351
1352 if instr(p_sui_id,'-') > 0 then
1353 l_return_sui_id :=
1354 lpad(
1355 substr(p_sui_id
1356 ,1
1357 ,instr(p_sui_id,'-') -1
1358 )
1359 ,10
1360 ,'0');
1361 else
1362 l_return_sui_id :=
1363 lpad(substr(p_sui_id,1,10)
1364 ,10
1365 ,'0');
1366 end if;
1367
1368 elsif p_state_code = 'IA' OR
1369 p_state_code = 'KS' THEN
1370
1371 if instr(p_sui_id,'-') > 0 then
1372 l_return_sui_id :=
1373 lpad(
1374 substr(p_sui_id
1375 ,1
1376 ,greatest(6
1377 ,instr(p_sui_id,'-') -1
1378 )
1379 )
1380 ,10
1381 ,'0');
1382 else
1383 l_return_sui_id :=
1384 lpad(substr(p_sui_id,1,10)
1385 ,10
1386 ,'0');
1387 end if;
1388
1389 elsif p_state_code = 'KY' OR
1390 p_state_code = 'MA' OR
1391 p_state_code = 'MI' OR
1392 p_state_code = 'NV' OR
1393 p_state_code = 'OR' OR
1394 p_state_code = 'SD' THEN
1395
1396 l_return_sui_id :=
1397 lpad(
1398 substr(p_sui_id
1399 ,1
1400 ,LENGTH(p_sui_id) -1)
1401 ,10
1402 ,'0');
1403
1404 elsif p_state_code = 'CA' OR
1405 p_state_code = 'MN' OR
1406 p_state_code = 'OH' THEN
1407
1408 l_return_sui_id :=
1409 lpad(
1410 substr(p_sui_id,1,7)
1411 ,10
1412 ,'0');
1413
1414 elsif p_state_code = 'MO' OR
1415 p_state_code = 'PR' OR
1416 p_state_code = 'WI' THEN
1417
1418 l_return_sui_id :=
1419 lpad(
1420 substr(p_sui_id,1,6)
1421 ,10
1422 ,'0');
1423
1424
1425 elsif p_state_code = 'CO' THEN
1426
1427 if length(p_sui_id) = 11
1428 and instr(p_sui_id,'-') = 7
1429 and instr(p_sui_id,'-',1,2) = 10 THEN
1430
1431 l_return_sui_id :=
1432 lpad(
1433 substr(p_sui_id,8,2) ||
1434 substr(p_sui_id,1,6) ||
1435 substr(p_sui_id,11,1)
1436 ,10
1437 ,'0');
1438 else
1439 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1440 end if;
1441
1442
1443 elsif p_state_code = 'FL' THEN
1444
1445 if length(p_sui_id) = 8 then
1446 l_return_sui_id :=
1447 lpad(
1448 substr(p_sui_id,1,7) ||
1449 '0'
1450 ,10
1451 ,'0');
1452 else
1453 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1454 end if;
1455
1456 elsif p_state_code = 'GA' THEN
1457
1458 if instr(p_sui_id,'-') > 0 then
1459 l_return_sui_id :=
1460 lpad(
1461 substr(p_sui_id,1,6) ||
1462 substr(p_sui_id,8,1)
1463 ,10
1464 ,'0');
1465 else
1466 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1467 end if;
1468
1469
1470 elsif p_state_code = 'MD' THEN
1471
1472 if instr(p_sui_id,'-') > 0 then
1473 l_return_sui_id :=
1474 lpad(
1475 substr(p_sui_id,1,9) ||
1476 substr(p_sui_id,11,1)
1477 ,10
1478 ,'0');
1479 else
1480 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1481 end if;
1482
1483 elsif p_state_code = 'MS' THEN
1484
1485 if length(p_sui_id) = 10 THEN
1486 l_return_sui_id := p_sui_id;
1487 else
1488 l_return_sui_id :=
1489 rpad(
1490 substr(p_sui_id,1,8)
1491 ,10
1492 ,'0');
1493 end if;
1494
1495 elsif p_state_code = 'NE' THEN
1496
1497 l_return_sui_id :=
1498 lpad(
1499 substr(p_sui_id,1,10)
1500 ,10
1501 ,'0');
1502
1503 elsif p_state_code = 'UT' THEN
1504
1505 if instr(p_sui_id,'-') > 0
1506 and instr(p_sui_id,'-',1,2) > 0 then
1507 l_return_sui_id :=
1508 lpad(
1509 substr(p_sui_id
1510 ,instr(p_sui_id,'-') + 1
1511 ,6) ||
1512 substr(p_sui_id
1513 ,instr(p_sui_id,'-',-1) + 1
1514 ,1)
1515 ,10
1516 ,'0');
1517 else
1518 l_return_sui_id := lpad(substr(p_sui_id,1,10),10,'0');
1519 end if;
1520
1521 else
1522 l_return_sui_id :=
1523 lpad(
1524 substr(p_sui_id,1,10)
1525 ,10
1526 ,'0');
1527 end if;
1528
1529 return l_return_sui_id;
1530
1531 END derive_sui_id;
1532
1533 FUNCTION update_global_values(p_estab_ID number,
1534 p_state_abbrev varchar2)
1535
1536 RETURN NUMBER
1537 IS
1538 BEGIN
1539
1540 IF p_estab_id <> pay_us_mwr_reporting_pkg.est_id
1541 OR p_state_abbrev <> pay_us_mwr_reporting_pkg.state_abbrev
1542 OR pay_us_mwr_reporting_pkg.estab_count = 20 THEN
1543 pay_us_mwr_reporting_pkg.estab_count := 0;
1544 pay_us_mwr_reporting_pkg.est_id := p_estab_id;
1545 pay_us_mwr_reporting_pkg.state_abbrev := p_state_abbrev;
1546 ELSE
1547 pay_us_mwr_reporting_pkg.estab_count := pay_us_mwr_reporting_pkg.estab_count + 1;
1548 END IF;
1549
1550
1551 return (pay_us_mwr_reporting_pkg.estab_count);
1552 END update_global_values;
1553
1554
1555 END pay_us_mwr_reporting_pkg;