1 PACKAGE BODY pay_us_sqwl_udf as
2 /* $Header: pyussqut.pkb 120.6.12020000.9 2013/03/26 06:46:06 schowl ship $ */
3 /* +======================================================================+
4 REM | Copyright (c) 1997 Oracle Corporation |
5 REM | Redwood Shores, California, USA |
6 REM | All rights reserved. |
7 REM +======================================================================+
8 REM SQL Script File Name : pyussqut.pkb
9 REM Description : Package and procedure to build sql for payroll
10 REM processes.
11 REM Package Name : pay_us_sqwl_udf
12 REM Purpose : Using the transfer_date and A_EMP_PER_HIRE_DATE,
13 REM this function will determine if the hire date is
14 REM within the quarter defined by the transfer_date.
15 REM Arguments : 1. A_EMP_PER_HIRE_DATE,
16 REM 2. transfer_date.
17 REM Notes : The following value is returned, qtr_hire_flag.
18 REM This flag will contain the value 'Y' if the hire
19 REM date is within the quarter and a value of 'N' if
20 REM the hire date is outside the quarter.
21 REM
22 REM Change List:
23 REM ------------
24 REM
25 REM Name Date Version Bug Text
26 REM ------------ ---------- ------- ------- ------------------------------
27 REM M Doody 16-FEB-2001 115.0 Initial Version
28 REM
29 REM tmehra 17-SEP-2001 115.1 Added 'get_gre_wage_plan_code'
30 REM function.
31 REM tmehra 15-OCT-2001 115.2 Added 'get_asg_wage_plan_code'
32 REM function.
33 REM tmehra 06-DEC-2001 115.3 Made GSCC compliant
34 REM tmehra 07-MAY-2003 115.4 Added new validation for
35 REM california sqwl as a new
36 REM new segment has been introduced
37 REM for the info type.
38 REM tmehra 22-MAY-2003 115.5 Added validation for duplicate
39 REM Wage Plan entered for the Same
40 REM GRE and for the same state.
41 REM Also added the check to trigger
42 REM this validation only for the
43 REM PAY_US_STATE_WAGE_PLAN_INFO
44 REM context.
45 REM tmehra 28-MAY-2003 115.6 2971577 Fixed the Message Token -
46 REM changed 'atleast' to 'at least'
47 REM tmehra 26-AUG-2003 115.7 2219097 Added two new functions for the
48 REM US W2 enhancements for Govt
49 REM employer.
50 REM - get_employment_code
51 REM - chk_govt_employer
52 REM tmehra 12-NOV-2003 115.8 3189039 Modified the chk_for_default_wp
53 REM to execute only for California.
54 REM tmehra 15-NOV-2003 115.9 2219097 Added a new functions for the
55 REM US W2 enhancements for Govt
56 REM employer.
57 REM - get_archived_emp_code
58 REM emunisek 05-Mar-2010 115.10 9356178 Added get_out_of_state_code
59 REM function
60 REM emunisek 30-Mar-2010 115.11 9356178 Modified the parameter type of
61 REM p_out_of_state_taxable parameter
62 REM in function get_out_of_state_code
63 REM Modified to fetch the balances
64 REM based on virtual date
65 REM emunisek 30-Mar-2010 115.12 9356178 Made file GSCC Compliant
66 REM emunisek 12-Apr-2010 115.13 9561700 Made changes to use the maximum
67 REM effective date of Assignment's
68 REM payroll actions in Balance Call
69 REM if the assignment ends in between
70 REM the Quarter.
71 REM nvelaga 26-Jul-2012 115.16 14368821 Added get_ar_out_of_state_code
72 REM function to fetch state code
73 REM from archived data for AR SQWL.
74 REM ybudamal 14-Sep-2012 115.17 14008507 Added Formula Functions Get_Employer_Count,
75 REM Get_Employer_Total_Wages and Get_Excess_Wages
76 REM for Q32012 MI SQWL changes.
77 REM ybudamal 17-Sep-2012 115.18 14008507 Modified formula function Get_Employer_Count
78 REM for Q32012 MI SQWL changes.
79 REM nvelaga 25-Sep-2012 115.19 14368821 Added NVL condition in the query used to
80 REM fetch AR out of state code.
81 REM schowl 22-Jan-2013 115.20 14456648 Added new formula function 'Get_SMWL_SUI_Wages'
82 REM to calculate Monthly SUI wages for the report 'SMWL'
83 REM schowl 11-Mar-2013 115.21 14345202 Added new formula function 'get_ca_sqwl_wages'
84 REM to get required Employer wages for CA_XML_SQWL Format
85 REM schowl 14-Mar-2013 115.22 14345202 Added new formula function 'spl_char_check'
86 REM to remove special characters from the string
87 REM schowl 26-Mar-2013 115.23 14345202 Modified 'get_ca_sqwl_wages' to support different Wage plan
88 REM codes. Modified 'spl_char_check'.
89 REM ===============================================================================================================
90
91 CREATE OR REPLACE PACKAGE BODY pay_us_sqwl_udf as
92 */
93 FUNCTION get_qtr_hire_flag
94 (
95 p_emp_per_hire_date in DATE,
96 p_transfer_date in DATE
97 )
98 RETURN VARCHAR2 is
99 qtr_hire_flag VARCHAR2(1) := 'N';
100
101 BEGIN
102
103 IF (
104 p_emp_per_hire_date > (trunc(p_transfer_date, 'Q') - 1)
105 AND
106 p_emp_per_hire_date < (round(p_transfer_date, 'Q') )
107 )
108 THEN
109 qtr_hire_flag := 'Y';
110 END IF;
111
112 RETURN (qtr_hire_flag);
113
114 END get_qtr_hire_flag;
115
116
117 ----
118 ---- A new function to return Gre Level Wage Plan Code
119 ---- For Single Wage Plan Code SQWL Format for 'CA'
120 ----
121 FUNCTION get_gre_wage_plan_code
122 (
123 p_tax_unit_id in number,
124 p_transfer_state in varchar
125 )
126 RETURN VARCHAR2 is
127
128 l_wage_plan_code VARCHAR2(1) := ' ';
129
130 CURSOR c_gre_wage_plan IS
131 SELECT hoi.org_information3 wage_plan
132 FROM hr_organization_information hoi
133 WHERE hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
134 AND hoi.organization_id = p_tax_unit_id
135 AND hoi.org_information1 = p_transfer_state;
136
137 BEGIN
138
139 FOR i IN c_gre_wage_plan
140 LOOP
141 l_wage_plan_code := i.wage_plan;
142 END LOOP;
143
144 RETURN l_wage_plan_code;
145
146 END get_gre_wage_plan_code;
147
148 ----
149 ---- A new function to return Asg Level Wage Plan Code
150 ---- For Single Wage Plan Code SQWL Format for 'CA'
151 ----
152 FUNCTION get_asg_wage_plan_code
153 (
154 p_assignment_id in number,
155 p_transfer_state in varchar
156 )
157 RETURN VARCHAR2 is
158
159 l_wage_plan_code VARCHAR2(1) := ' ';
160
161 CURSOR c_asg_wage_plan IS
162 SELECT DISTINCT aei_information3 wage_plan
163 FROM per_assignment_extra_info paei
164 WHERE paei.assignment_id = p_assignment_id
165 AND paei.aei_information1 = p_transfer_state
166 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD';
167
168 BEGIN
169
170 FOR i IN c_asg_wage_plan
171 LOOP
172 l_wage_plan_code := i.wage_plan;
173 END LOOP;
174
175 RETURN l_wage_plan_code;
176
177 END get_asg_wage_plan_code;
178
179 -- ----------------------------------------------------------------------------
180 -- |-----------------------< chk_for_default_wp > ----------------------------|
181 -- ----------------------------------------------------------------------------
182 -- Description:
183 -- Verify that only one wage plan is designated as default and
184 -- that at least one wage plan is designated as default
185 -- Added for US Payroll specific situations.
186 --
187 -- Pre Conditions:
188 --
189 --
190 -- In Parameters:
191 -- organization_id, information_context, org_information1, org_information2
192 -- org_information3, org_information4
193 --
194 -- Post Success:
195 -- Processing continues.
196 --
197 -- Post Failure:
198 -- An application error will be raised and processing is terminated.
199 --
200 -- Access Status:
201 -- Internal Table Handler Use Only.
202 -- ----------------------------------------------------------------------------
203 PROCEDURE chk_for_default_wp ( p_organization_id number,
204 p_org_information_context varchar2,
205 p_org_information1 varchar2
206 ) IS
207
208 --
209 l_proc varchar2(100) := 'pay_us_sqwl_udf.chk_for_default_wp';
210
211 l_count number := 0;
212 --
213 CURSOR c1 (p_organization_id number,
214 p_information_context varchar2,
215 p_org_information1 varchar2
216 )IS
217 SELECT count(*) ct
218 FROM hr_organization_information
219 WHERE organization_id = p_organization_id
220 AND org_information_context = p_org_information_context
221 AND org_information1 = p_org_information1
222 AND org_information4 = 'Y';
223 --
224 CURSOR c2(p_organization_id number) IS
225 SELECT count(*) ct
226 FROM (select distinct
227 a.organization_id,
228 a.org_information1,
229 a.org_information3
230 FROM hr_organization_information a
231 WHERE org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO') b
232 WHERE b.organization_id = p_organization_id
233 AND 1 < ( SELECT count(*)
234 FROM hr_organization_information orgi
235 WHERE organization_id = p_organization_id
236 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
237 AND org_information1 = b.org_information1
238 AND org_information3 = b.org_information3);
239 --
240
241 BEGIN
242 --
243 hr_utility.set_location('Entering:'||l_proc, 10);
244 --
245
246 IF p_org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
247 AND p_org_information1 = 'CA' THEN
248
249 l_count := 0;
250
251 FOR c1_rec IN c1 (p_organization_id,
252 p_org_information_context,
253 p_org_information1) LOOP
254
255 l_count := c1_rec.ct;
256
257
258 END LOOP;
259
260 hr_utility.set_location(l_proc, 20);
261
262
263 --
264 -- raise error if the count > 1 or count = 0
265 --
266
267 IF l_count <> 1 THEN
268
269 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
270 hr_utility.set_message_token('FORMAT',' with at least 1 and only 1 marked as default');
271
272 hr_utility.raise_error;
273
274 END IF;
275
276
277 l_count := 0;
278
279 FOR c2_rec IN c2 (p_organization_id) LOOP
280
281 l_count := c2_rec.ct;
282
283
284 END LOOP;
285
286 hr_utility.set_location(l_proc, 20);
287
288
289 --
290 -- raise error if the count > 1 or count = 0
291 --
292
293 IF l_count > 0 THEN
294
295 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
296 hr_utility.set_message_token('FORMAT',' with unique tax type and state code');
297
298 hr_utility.raise_error;
299
300 END IF;
301
302
303 END IF;
304
305 --
306 hr_utility.set_location(' Leaving:'||l_proc, 50);
307 --
308 END chk_for_default_wp;
309 --
310
311 -- ----------------------------------------------------------------------------
312 -- |-----------------------< chk_govt_employer > ----------------------------|
313 -- ----------------------------------------------------------------------------
314 -- Description:
315 -- Verify if the employee/employer is a US government employee/employer
316 -- Added for US Payroll W2 specific situations.
317 --
318 -- Pre Conditions:
319 --
320 --
321 -- In Parameters:
322 -- tax_unit_id, assignment_action_id, assignment_id
323 --
324 --
325 -- Post Success:
326 -- Returns Yes/No
327 --
328 --
329 -- Access Status:
330 -- Internal Table Handler Use Only.
331 -- ----------------------------------------------------------------------------
332 FUNCTION chk_govt_employer ( p_tax_unit_id number DEFAULT NULL,
333 p_assignment_action_id number DEFAULT NULL
334 ) RETURN BOOLEAN IS
335
336 --
337 l_proc varchar2(100) := 'pay_us_sqwl_udf.chk_govt_employer';
338 l_tax_unit_id number;
339 l_yes_no boolean := FALSE;
340 --
341 CURSOR c_get_tax_unit_id IS
342 SELECT tax_unit_id
343 FROM pay_assignment_actions
344 WHERE assignment_action_id = p_assignment_action_id;
345
346
347 CURSOR c_chk_govt_employer IS
348 SELECT target.ORG_INFORMATION8 yes_no
349 FROM hr_organization_information target
350 WHERE target.organization_id = l_tax_unit_id
351 AND target.org_information_context = 'Federal Tax Rules';
352
353 --
354
355 BEGIN
356 --
357 hr_utility.set_location('Entering:'||l_proc, 10);
358 --
359
360 IF p_tax_unit_id IS NOT NULL THEN
361 l_tax_unit_id := p_tax_unit_id;
362 ELSE
363
364 FOR c_rec IN c_get_tax_unit_id
365 LOOP
366 l_tax_unit_id := c_rec.tax_unit_id;
367 END LOOP;
368 END IF;
369
370
371 FOR c_rec IN c_chk_govt_employer
372 LOOP
373 IF c_rec.yes_no = 'Y' THEN
374 l_yes_no := TRUE;
375 ELSE
376 l_yes_no := FALSE;
377 END IF;
378 END LOOP;
379 --
380 hr_utility.set_location(' Leaving:'||l_proc, 50);
381 --
382
383 RETURN l_yes_no;
384
385 END chk_govt_employer;
386
387 -- ----------------------------------------------------------------------------
388 -- |-----------------------< chk_employment_code > ----------------------------|
389 -- ----------------------------------------------------------------------------
390 -- Description:
391 -- Returns the employment code 'Q' or 'R' based on medicare and SS withheld.
392 -- Added for US Payroll W2 specific situations.
393 --
394 -- Pre Conditions:
395 --
396 --
397 -- In Parameters:
398 -- Medicare Wages, SS wages
399 --
400 --
401 -- Post Success:
402 -- Returns 'Q' or 'R'
403 --
404 --
405 -- ----------------------------------------------------------------------------
406 FUNCTION get_employment_code ( p_medicare_wh number DEFAULT NULL,
407 p_ss_wh number DEFAULT NULL
408 ) RETURN varchar2 IS
409
410 --
411 l_proc varchar2(100) := 'pay_us_sqwl_udf.get_employement_code';
412 l_tax_unit_id number;
413 l_code varchar2(1);
414 --
415 --
416
417 BEGIN
418 --
419 hr_utility.set_location('Entering:'||l_proc, 10);
420 --
421
422 IF p_ss_wh = 0 and p_medicare_wh > 0 THEN
423 l_code := 'Q';
424 ELSE
425 l_code := 'R';
426 END IF;
427
428 --
429 hr_utility.set_location(' Leaving:'||l_proc, 50);
430 --
431
432 RETURN l_code;
433
434 END get_employment_code;
435
436 -- ----------------------------------------------------------------------------
437 -- |-----------------------< get_archived_emp_code >---------------------------
438 -- ----------------------------------------------------------------------------
439 -- Description:
440 -- Returns the archived employment code 'Q' or 'R' for the passed assignment
441 -- action_id.
442 -- Added for US Payroll W2 specific situations.
443 --
444 -- Pre Conditions:
445 -- If no archived value is found, default value of 'R' is returned. This is
446 -- done to support the employees whose data was archived before these
447 -- changes.
448 --
449 --
450 -- In Parameters:
451 -- p_assignment_action_id
452 --
453 --
454 -- Post Success:
455 -- Returns 'Q' or 'R'
456 --
457 --
458 -- ----------------------------------------------------------------------------
459 FUNCTION get_archived_emp_code ( p_assignment_action_id number DEFAULT NULL
460 ) RETURN varchar2 IS
461
462 --
463 l_proc varchar2(100) := 'pay_us_sqwl_udf.get_archived_emp_code';
464 l_code varchar2(1);
465 l_ue_id NUMBER;
466 --
467 --
468 CURSOR c_get_user_entity_id IS
469 SELECT user_entity_id
470 FROM ff_user_entities
471 WHERE user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE';
472
473
474 CURSOR c_get_archived_emp_code (p_user_entity_id NUMBER) IS
475 SELECT arch.value
476 FROM ff_archive_items arch
477 WHERE arch.user_entity_id = p_user_entity_id
478 AND arch.context1 = p_assignment_action_id;
479
480 BEGIN
481 --
482 hr_utility.set_location('Entering:'||l_proc, 10);
483 --
484
485
486 -- Get the user entity id for A_ASG_GRE_EMPLOYMENT_TYPE_CODE
487
488 FOR c_rec IN c_get_user_entity_id
489 LOOP
490
491 l_ue_id := c_rec.user_entity_id;
492
493 END LOOP;
494
495
496 -- Get the archived emp code for the passed assignment_action
497
498 l_code := 'R';
499
500 FOR c_rec IN c_get_archived_emp_code(l_ue_id)
501 LOOP
502
503 l_code := c_rec.value;
504
505 END LOOP;
506
507 --
508 hr_utility.set_location(' Leaving:'||l_proc, 50);
509 --
510
511 RETURN l_code;
512
513 END get_archived_emp_code;
514
515 -- ----------------------------------------------------------------------------
516 -- |-----------------------< get_out_of_state_code >---------------------------
517 -- ----------------------------------------------------------------------------
518
519 /*Added for Bug#9356178*/
520 FUNCTION get_out_of_state_code ( p_assignment_action_id number,
521 p_assignment_id number,
522 p_tax_unit_id number,
523 p_reporting_date date,
524 p_out_of_state_taxable IN OUT nocopy number
525 ) RETURN varchar2 IS
526
527 CURSOR get_person_id IS
528 select distinct person_id
529 from per_all_assignments_f
530 where assignment_id = p_assignment_id;
531
532 /* Added for Bug#9561700*/
533 /*Since we are using the Date Based approach to fetch the Balances
534 of the assignment, we need to ensure that on the Date we pass for the
535 assignment, the Assignment record is present.Incase, the employee
536 is terminated, we need to pass the last effective date applicable to the
537 assignment to fetch the balances.This we do by referring to the pay_payroll_actions
538 table to find the maximum effective_date of this person in this Quarter.*/
539
540 CURSOR get_effective_date (p_quarter_start_date DATE,
541 p_quarter_end_date DATE) IS
542 select max(ppa.effective_date)
543 from per_all_assignments_f asg,
544 pay_assignment_actions paa,
545 pay_payroll_actions ppa
546 where ppa.effective_date between p_quarter_start_date
547 and p_quarter_end_date
548 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
549 and paa.payroll_action_id = ppa.payroll_action_id
550 and paa.assignment_id = asg.assignment_id
551 and paa.action_status <> 'S'
552 and asg.effective_end_date >= p_quarter_start_date
553 and asg.effective_start_date <= p_quarter_end_date
554 and asg.business_group_id = ppa.business_group_id
555 and asg.assignment_type = 'E'
556 and paa.tax_unit_id = p_tax_unit_id
557 and asg.assignment_id = p_assignment_id;
558
559 /*End Bug#9561700*/
560
561 CURSOR get_emp_state_codes (p_person_id per_all_people_f.person_id%TYPE,
562 p_year_start_date DATE,
563 p_quarter_end_date DATE) IS
564 select distinct pest.state_code,pus.state_abbrev
565 from per_all_assignments_f paaf,
566 hr_soft_coding_keyflex hsck,
567 pay_us_emp_state_tax_rules_f pest,
568 pay_us_states pus
569 where paaf.person_id = p_person_id
570 and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
571 and paaf.effective_end_date >= p_year_start_date
572 and paaf.effective_start_date <= p_quarter_end_date
573 and hsck.segment1=to_char(p_tax_unit_id)
574 and pest.assignment_id = paaf.assignment_id
575 and pest.business_group_id = paaf.business_group_id
576 and pest.effective_end_date >= p_year_start_date
577 and pest.effective_start_date <= p_quarter_end_date
578 and pus.state_code=pest.state_code;
579
580 CURSOR get_missed_emp_state_codes (p_person_id per_all_people_f.person_id%TYPE,
581 p_year_start_date DATE,
582 p_quarter_end_date DATE) IS
583 select distinct substr(peev.screen_entry_value,1,2),pus.state_abbrev
584 from per_all_assignments_f paaf,
585 hr_soft_coding_keyflex hsck,
586 pay_element_entries_f pee,
587 pay_element_entry_values_f peev,
588 pay_input_values_f piv1,
589 pay_input_values_f piv2,
590 pay_balance_types pbt,
591 pay_balance_feeds_f pbf,
592 pay_element_links_f pel,
593 pay_us_states pus
594 where paaf.person_id=p_person_id
595 and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
596 and paaf.effective_end_date >= p_year_start_date
597 and paaf.effective_start_date <= p_quarter_end_date
598 and hsck.segment1=to_char(p_tax_unit_id)
599 and pee.assignment_id = paaf.assignment_id
600 and pee.effective_end_date >= p_year_start_date
601 and pee.effective_start_date <= p_quarter_end_date
602 and pee.element_link_id = pel.element_link_id
603 and pee.element_entry_id = peev.element_entry_id
604 and paaf.business_group_id = pel.business_group_id
605 and pel.effective_end_date >= p_year_start_date
606 and pel.effective_start_date <= p_quarter_end_date
607 and pel.element_type_id = piv1.element_type_id
608 and piv1.name='Jurisdiction'
609 and piv1.effective_end_date >= p_year_start_date
610 and piv1.effective_start_date <= p_quarter_end_date
611 and piv1.input_value_id = peev.input_value_id
612 and pbt.balance_name ='SUI ER Taxable'
613 and pbt.balance_type_id = pbf.balance_type_id
614 and pbf.input_value_id = piv2.input_value_id
615 and piv2.effective_end_date >= p_year_start_date
616 and piv2.effective_start_date <= p_quarter_end_date
617 and piv2.element_type_id = pee.element_type_id
618 and pus.state_code=substr(peev.screen_entry_value,1,2)
619
620 minus
621
622 select distinct pest.state_code,pus.state_abbrev
623 from per_all_assignments_f paaf,
624 hr_soft_coding_keyflex hsck,
625 pay_us_emp_state_tax_rules_f pest,
626 pay_us_states pus
627 where paaf.person_id = p_person_id
628 and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
629 and paaf.effective_end_date >= p_year_start_date
630 and paaf.effective_start_date <= p_quarter_end_date
631 and hsck.segment1=to_char(p_tax_unit_id)
632 and pest.assignment_id = paaf.assignment_id
633 and pest.business_group_id = paaf.business_group_id
634 and pest.effective_end_date >= p_year_start_date
635 and pest.effective_start_date <= p_quarter_end_date
636 and pus.state_code=pest.state_code;
637
638 CURSOR get_defined_balance_id(p_dimension_name pay_balance_dimensions.dimension_name%TYPE) IS
639 select pdb.defined_balance_id
640 from pay_balance_types pbt,
641 pay_balance_dimensions pbd,
642 pay_defined_balances pdb
643 where pbt.legislation_code = 'US'
644 and pbt.balance_name = 'SUI ER Taxable'
645 and pbd.legislation_code = 'US'
646 and pbd.dimension_name = p_dimension_name
647 and pdb.balance_type_id = pbt.balance_type_id
648 and pdb.balance_dimension_id = pbd.balance_dimension_id;
649
650 l_person_id per_all_people_f.person_id%TYPE;
651 l_state_code pay_us_states.state_code%TYPE;
652 l_state_abbrev pay_us_states.state_abbrev%TYPE;
653 l_out_of_state_code pay_us_states.state_abbrev%TYPE;
654 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
655 l_year_start_date DATE;
656 l_quarter_start_date DATE;
657 l_quarter_end_date DATE;
658 l_effective_end_date DATE;
659 l_effective_date DATE;
660 l_reporting_date DATE;
661 l_total_state_taxable NUMBER;
662 l_total_out_of_state_taxable NUMBER;
663 l_out_of_state_taxable NUMBER;
664 l_state_earnings NUMBER;
665 l_count NUMBER;
666 fl_jurisdiction_code varchar2(11);
667
668 BEGIN
669
670 hr_utility.trace('Entering get_out_of_state_code');
671 hr_utility.trace('Parameters Passed are');
672 hr_utility.trace('p_assignment_id'||p_assignment_id);
673 hr_utility.trace('p_tax_unit_id'||p_tax_unit_id);
674 hr_utility.trace('p_assignment_action_id'||p_assignment_action_id);
675 hr_utility.trace('p_reporting_date'||to_char(p_reporting_date));
676
677 l_year_start_date := trunc(p_reporting_date,'YEAR');
678 l_quarter_start_date := add_months(last_day(p_reporting_date),-3)+1;
679 l_quarter_end_date := last_day(p_reporting_date);
680
681 fl_jurisdiction_code := '10-000-0000';
682
683 OPEN get_person_id;
684
685 FETCH get_person_id INTO l_person_id;
686
687 CLOSE get_person_id;
688
689 /* Added for Bug#9561700*/
690 /* First find out if the Assignment record is ending in between the Quarter.If
691 it is not, then call the balance procedure with Quarter End Date.If the Assignment
692 record ends in between the Quarter, we need to use find the maximum effective
693 date for the assignment from payroll actions and use it in balance calls.*/
694
695 SELECT least(max(effective_end_date),p_reporting_date)
696 INTO l_effective_end_date
697 FROM per_all_assignments_f
698 WHERE assignment_id = p_assignment_id
699 AND assignment_type = 'E'
700 AND effective_end_date >= l_quarter_start_date ;
701
702 IF l_effective_end_date < p_reporting_date THEN
703
704 OPEN get_effective_date(l_quarter_start_date,l_quarter_end_date);
705
706 FETCH get_effective_date INTO l_reporting_date;
707
708 CLOSE get_effective_date;
709
710 hr_utility.trace('Modified l_reporting_date'||to_char(l_reporting_date));
711
712 ELSE
713
714 l_reporting_date := p_reporting_date;
715
716 hr_utility.trace('Use original l_reporting_date'||to_char(l_reporting_date));
717
718 END IF;
719
720 /*End Bug#9561700*/
721
722 /*Fetch the Total Taxable Wages of the Employee*/
723
724 OPEN get_defined_balance_id('Person within Government Reporting Entity Year to Date');
725 FETCH get_defined_balance_id INTO l_defined_balance_id;
726
727 IF get_defined_balance_id%NOTFOUND THEN
728
729 hr_utility.trace('Not able to find Defined Balance for combination of SUI ER Taxable and Person within Government Reporting Entity Year to Date');
730
731 END IF;
732
733 CLOSE get_defined_balance_id;
734
735 l_total_state_taxable := 0;
736
737 l_total_state_taxable := pay_balance_pkg.get_value( l_defined_balance_id,
738 p_assignment_id,
739 l_reporting_date);
740
741 hr_utility.trace('Total SUI Taxable till this Quarter'||l_total_state_taxable);
742
743
744 OPEN get_defined_balance_id('Person in JD within GRE Year to Date');
745 FETCH get_defined_balance_id INTO l_defined_balance_id;
746
747 IF get_defined_balance_id%NOTFOUND THEN
748
749 hr_utility.trace('Not able to find Defined Balance for combination of SUI ER Taxable and Person in JD within GRE Year to Date');
750
751 END IF;
752
753 CLOSE get_defined_balance_id;
754
755 pay_balance_pkg.set_context('JURISDICTION_CODE',fl_jurisdiction_code);
756
757 l_total_out_of_state_taxable := 0;
758
759 l_total_out_of_state_taxable := l_total_state_taxable -
760 pay_balance_pkg.get_value( l_defined_balance_id,
761 p_assignment_id,
762 l_reporting_date);
763
764 hr_utility.trace('Total Out of State SUI Taxable till this Quarter'||l_total_out_of_state_taxable);
765
766 hr_utility.trace('p_out_of_state_taxable passed into function'||p_out_of_state_taxable);
767
768 p_out_of_state_taxable := l_total_out_of_state_taxable;
769
770 hr_utility.trace('p_out_of_state_taxable passed out of function'||p_out_of_state_taxable);
771
772 l_count := 0;
773
774 l_out_of_state_taxable := 0;
775
776 OPEN get_emp_state_codes(l_person_id,l_year_start_date,l_quarter_end_date);
777
778 FETCH get_emp_state_codes INTO l_state_code,l_state_abbrev;
779
780 WHILE get_emp_state_codes%FOUND
781
782 LOOP
783
784 hr_utility.trace('StateCode Fetched'||l_state_code);
785 hr_utility.trace('State Fetched'||l_state_abbrev);
786
787 IF l_state_abbrev <> 'FL'
788 THEN
789
790 l_state_earnings := 0;
791
792 pay_balance_pkg.set_context('JURISDICTION_CODE',l_state_code||'-000-0000');
793
794 l_state_earnings := pay_balance_pkg.get_value( l_defined_balance_id,
795 p_assignment_id,
796 l_reporting_date);
797
798 l_out_of_state_taxable := l_out_of_state_taxable + l_state_earnings;
799
800 hr_utility.trace('State Earnings YTD'||l_state_earnings);
801 hr_utility.trace('l_out_of_state_taxable'||l_out_of_state_taxable);
802
803 IF l_state_earnings > 0 THEN
804
805 l_out_of_state_code := l_state_abbrev;
806 l_count := l_count + 1;
807
808 hr_utility.trace('Found State with YTD Taxable more than 0 and not Florida, Increase out of states count by 1');
809
810 END IF;
811
812
813 END IF;
814
815 FETCH get_emp_state_codes INTO l_state_code,l_state_abbrev;
816
817 END LOOP;
818
819 hr_utility.trace('Number of States other than Florida'||l_count);
820 hr_utility.trace('Out of State Taxable as of now'||l_out_of_state_taxable);
821 hr_utility.trace('Actual Out of State Taxable'||p_out_of_state_taxable);
822
823 IF l_out_of_state_taxable <> p_out_of_state_taxable THEN
824
825 hr_utility.trace('Missed some of the Out of States.Get them Now');
826
827 OPEN get_missed_emp_state_codes(l_person_id,l_year_start_date,l_quarter_end_date);
828
829 FETCH get_missed_emp_state_codes INTO l_state_code,l_state_abbrev;
830
831 WHILE get_missed_emp_state_codes%FOUND
832
833 LOOP
834
835 hr_utility.trace('StateCode Fetched'||l_state_code);
836 hr_utility.trace('State Fetched'||l_state_abbrev);
837
838 IF l_state_abbrev <> 'FL'
839 THEN
840
841 l_state_earnings := 0;
842
843 pay_balance_pkg.set_context('JURISDICTION_CODE',l_state_code||'-000-0000');
844
845 l_state_earnings := pay_balance_pkg.get_value( l_defined_balance_id,
846 p_assignment_id,
847 l_reporting_date);
848
849 l_out_of_state_taxable := l_out_of_state_taxable + l_state_earnings;
850
851 hr_utility.trace('State Earnings YTD'||l_state_earnings);
852 hr_utility.trace('l_out_of_state_taxable'||l_out_of_state_taxable);
853
854 IF l_state_earnings > 0 THEN
855
856 l_out_of_state_code := l_state_abbrev;
857 l_count := l_count + 1;
858
859 hr_utility.trace('Found State with YTD Taxable more than 0 and not Florida, Increase out of states count by 1');
860
861 END IF;
862
863
864 END IF;
865
866 FETCH get_missed_emp_state_codes INTO l_state_code,l_state_abbrev;
867
868 END LOOP;
869
870 END IF;
871
872
873 IF l_count > 1 THEN
874
875 l_out_of_state_code := 'MU';
876
877 ELSIF l_count = 0 THEN
878
879 l_out_of_state_code := 'XX';
880
881 END IF;
882
883 CLOSE get_emp_state_codes;
884
885 hr_utility.trace('Out of State Code returned'||l_out_of_state_code);
886
887 RETURN l_out_of_state_code;
888
889 EXCEPTION
890
891 when others then
892 hr_utility.trace('Error ORA-'||TO_CHAR(SQLCODE));
893 hr_utility.raise_error;
894
895 END get_out_of_state_code;
896
897 /* Bug 9356247 : Function to get reporting unit number which is defined at
898 location EIT and based on Generic Hierarchy setup */
899
900 FUNCTION get_reporting_unit_no ( p_assignment_action_id number)
901 RETURN varchar2 IS
902
903 CURSOR c_get_location_id IS
904 select fnd_number.canonical_to_number(target.value)
905 from
906 ff_archive_items target,ff_user_entities fue
907 where target.user_entity_id = fue.user_entity_id
908 and fue.user_entity_name='A_SQWL_LOC_QTR_END'
909 and target.context1 = to_char(p_assignment_action_id);
910
911
912 CURSOR c_get_reporting_unit_no(cp_location_id number) IS
913 Select lpad(nvl(lei.lei_information1,'0001'),4,'0')
914 from per_gen_hierarchy pgh
915 ,per_gen_hierarchy_versions pghv
916 ,per_gen_hierarchy_nodes pghn -- parent organization
917 ,per_gen_hierarchy_nodes pghn2 -- establishment organizations
918 ,hr_organization_information hoi
919 ,hr_organization_units hou
920 ,hr_locations loc
921 ,hr_location_extra_info lei
922 where pgh.hierarchy_id = Pay_Magtape_Generic.Get_Parameter_Value(
923 'TRANSFER_HIERARCHY_ID') --parameter p_hierarchy_id
924 and pghv.HIERARCHY_VERSION_id =Pay_Magtape_Generic.Get_Parameter_Value(
925 'TRANSFER_HIERARCHY_VERSION') --parameter p_hierarchy_verision_number
926 and pgh.hierarchy_id = pghv.hierarchy_id
927 and pghv.hierarchy_version_id = pghn.hierarchy_version_id
928 and pghn.node_type = 'PAR'
929 and pghn.entity_id = hou.organization_id
930 and hou.business_group_id = pgh.business_group_id
931 and hou.organization_id = hoi.organization_id
932 and hoi.org_information_context = 'MWR_Info'
933 and pghv.hierarchy_version_id = pghn2.hierarchy_version_id
934 and pghn.business_group_id = pghn2.business_group_id
935 and pghn2.node_type = 'EST'
936 and pghn2.entity_id = loc.location_id
937 and loc.region_2 = Pay_Magtape_Generic.Get_Parameter_Value(
938 'TRANSFER_STATE')
939 and loc.location_id = lei.location_id
940 and lei.information_type = 'Multi Work Site Information'
941 and loc.location_id = cp_location_id;
942
943 l_reporting_unit_no hr_location_extra_info.lei_information1%TYPE :='0001';
944 l_location_id NUMBER;
945
946 BEGIN
947
948 hr_utility.trace('Entering get_reporting_unit_no');
949 hr_utility.trace('Parameters Passed are');
950 hr_utility.trace('p_assignment_action_id'||p_assignment_action_id);
951 -- hr_utility.trace('p_location_id'||p_location_id);
952
953 OPEN c_get_location_id;
954 FETCH c_get_location_id into l_location_id;
955
956 OPEN c_get_reporting_unit_no(l_location_id);
957
958 FETCH c_get_reporting_unit_no INTO l_reporting_unit_no;
959 IF c_get_reporting_unit_no%NOTFOUND THEN
960 l_reporting_unit_no := '0001';
961 END IF;
962
963 CLOSE c_get_reporting_unit_no;
964 hr_utility.trace('l_reporting_unit_no'||l_reporting_unit_no);
965
966
967 CLOSE c_get_location_id;
968 hr_utility.trace('1_location_id'||l_location_id);
969 RETURN l_reporting_unit_no;
970 EXCEPTION
971
972 when others then
973 hr_utility.trace('Error ORA-'||TO_CHAR(SQLCODE));
974 hr_utility.raise_error;
975
976 END get_reporting_unit_no;
977
978 /* Added for Bug#14368821 */
979
980 FUNCTION get_ar_out_of_state_code (p_assignment_action_id number)
981 RETURN varchar2 IS
982
983 l_ar_out_of_state_code ff_archive_items.value%TYPE;
984 l_user_entity_id ff_database_items.user_entity_id%TYPE;
985
986 CURSOR get_user_entity_id (p_dbi_name IN VARCHAR2)
987 IS
988 SELECT user_entity_id
989 FROM ff_database_items
990 WHERE user_name = p_dbi_name;
991
992 CURSOR get_archive_data (p_assignment_action_id IN NUMBER,
993 p_user_entity_id IN NUMBER)
994 IS
995 SELECT NVL(target.value, ' ')
996 FROM ff_archive_items target
997 WHERE target.user_entity_id = p_user_entity_id
998 AND target.context1 = p_assignment_action_id;
999
1000 BEGIN
1001
1002 l_ar_out_of_state_code := NULL;
1003 l_user_entity_id := NULL;
1004
1005 OPEN get_user_entity_id('SUI_ER_AR_OOSW_STATE_CODE');
1006 FETCH get_user_entity_id INTO l_user_entity_id;
1007 CLOSE get_user_entity_id;
1008
1009 OPEN get_archive_data(p_assignment_action_id, l_user_entity_id);
1010 FETCH get_archive_data INTO l_ar_out_of_state_code;
1011 CLOSE get_archive_data;
1012
1013 RETURN l_ar_out_of_state_code;
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 hr_utility.trace('Error while getting the ar out of state code' || to_char(sqlcode));
1018 raise hr_utility.hr_error;
1019
1020 END get_ar_out_of_state_code;
1021
1022 /* Bug 14008507 start */
1023 /* Function Name : Get_Employer_Count
1024 Purpose : Purpose of this function is to get the Number of Employers for the
1025 State. Created for getting data in SQWL Output.
1026
1027 Special Note :
1028
1029 */
1030
1031 FUNCTION get_employer_count( p_payroll_action_id number, --context
1032 p_state varchar2 default null
1033 ) RETURN number IS
1034 lv_employer_count number ;
1035 BEGIN
1036 hr_utility.trace('Inside pay_us_reporting_utils_pkg.get_employer_count') ;
1037 hr_utility.trace('p_payroll_action_id := '||p_payroll_action_id) ;
1038
1039 select count(distinct tax_unit_id)
1040 into lv_employer_count
1041 from pay_payroll_actions ppa,
1042 pay_assignment_actions paa
1043 where ppa.payroll_action_id = p_payroll_action_id
1044 and ppa.payroll_action_id = paa.payroll_action_id ;
1045
1046 hr_utility.trace('lv_employer_count := '||lv_employer_count) ;
1047 RETURN lv_employer_count ;
1048
1049 END get_employer_count ;
1050
1051
1052 /* Bug 14008507 end */
1053
1054 /* Bug 14008507 start */
1055 /* Function Name : Get_Employer_Total_Wages
1056 Purpose : Purpose of this function is to get the Total Wages (State Specific)
1057 to be reported in SQWL Magtape.
1058
1059 Special Note :
1060
1061 */
1062
1063 FUNCTION get_employer_total_wages( p_payroll_action_id number, --context
1064 p_state varchar2
1065 ) RETURN number IS
1066 lv_employer_total_wages number ;
1067 BEGIN
1068
1069 hr_utility.trace('Inside pay_us_reporting_utils_pkg.get_employer_total_wages') ;
1070 hr_utility.trace('p_payroll_action_id := '||p_payroll_action_id) ;
1071 hr_utility.trace('p_state := ' || p_state) ;
1072
1073 lv_employer_total_wages := 0 ;
1074
1075 IF p_state = 'MI' THEN
1076
1077 select sum(to_number(nvl(fai.value, '0')) - to_number(nvl(fai1.value, '0')))
1078 into lv_employer_total_wages
1079 from pay_payroll_actions ppa,
1080 pay_assignment_actions paa,
1081 ff_archive_items fai,
1082 ff_archive_items fai1,
1083 ff_database_items fdi,
1084 ff_database_items fdi1
1085 where ppa.payroll_action_id = p_payroll_action_id
1086 and ppa.payroll_action_id = paa.payroll_action_id
1087 and fai.context1 = paa.assignment_action_id
1088 and fai.user_entity_id = fdi.user_entity_id
1089 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
1090 and fai1.context1 = fai.context1
1091 and fai1.user_entity_id = fdi1.user_entity_id
1092 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD' ;
1093
1094 RETURN lv_employer_total_wages;
1095 END IF ;
1096
1097 END get_employer_total_wages;
1098
1099 /* Bug 14008507 end */
1100
1101 /* Bug 14008507 start */
1102 /* Function Name : Get_Excess_Wages
1103 Purpose : Purpose of this function is to get the Excess Wages (State Specific)
1104 to be reported in SQWL Magtape.
1105
1106 Special Note :
1107
1108 */
1109
1110 FUNCTION get_excess_wages( p_payroll_action_id number, --context
1111 p_tax_unit_id number, --context
1112 p_state varchar2
1113 ) RETURN number IS
1114 lv_excess_wages number ;
1115
1116 BEGIN
1117
1118 hr_utility.trace('Inside pay_us_reporting_utils_pkg.get_excess_wages') ;
1119 hr_utility.trace('p_payroll_action_id := '||p_payroll_action_id) ;
1120 hr_utility.trace('p_tax_unit_id := ' || p_tax_unit_id) ;
1121 hr_utility.trace('p_state := ' || p_state) ;
1122
1123 lv_excess_wages := 0 ;
1124
1125
1126 IF p_state='MI' THEN
1127
1128 select sum(to_number(nvl(fai.value, '0')) - to_number(nvl(fai1.value, '0')) - to_number(nvl(fai2.value, '0')))
1129 into lv_excess_wages
1130 from pay_payroll_actions ppa,
1131 pay_assignment_actions paa,
1132 ff_archive_items fai,
1133 ff_archive_items fai1,
1134 ff_archive_items fai2,
1135 ff_database_items fdi,
1136 ff_database_items fdi1,
1137 ff_database_items fdi2
1138 where ppa.payroll_action_id = p_payroll_action_id
1139 and ppa.payroll_action_id = paa.payroll_action_id
1140 and paa.tax_unit_id = p_tax_unit_id
1141 and fai.context1 = paa.assignment_action_id
1142 and fai.user_entity_id = fdi.user_entity_id
1143 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
1144 and fai1.context1 = fai.context1
1145 and fai1.user_entity_id = fdi1.user_entity_id
1146 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
1147 and fai2.context1 = fai1.context1
1148 and fai2.user_entity_id = fdi2.user_entity_id
1149 and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD';
1150
1151 RETURN lv_excess_wages ;
1152 END IF ;
1153
1154 END get_excess_wages;
1155 /* Bug 14008507 end */
1156
1157 /* Function Added for Bug 14456648 */
1158 FUNCTION get_smwl_sui_wages(p_payroll_action_id number, --context
1159 p_tax_unit_id number, --context
1160 p_state varchar2,
1161 p_sui_gross out nocopy number,
1162 p_sui_subj out nocopy number,
1163 p_sui_pre_tax out nocopy number,
1164 p_sui_taxable out nocopy number
1165 )return number is
1166
1167
1168 BEGIN
1169
1170
1171 select nvl(sum(to_number(nvl(fai.value, '0'))),0),
1172 nvl(sum(to_number(nvl(fai1.value, '0'))),0),
1173 nvl(sum(to_number(nvl(fai2.value, '0'))),0),
1174 nvl(sum(to_number(nvl(fai3.value, '0'))),0)
1175 into p_sui_subj,
1176 p_sui_pre_tax,
1177 p_sui_taxable,
1178 p_sui_gross
1179 from pay_payroll_actions ppa,
1180 pay_assignment_actions paa,
1181 ff_archive_items fai,
1182 ff_archive_items fai1,
1183 ff_archive_items fai2,
1184 ff_archive_items fai3,
1185 ff_database_items fdi,
1186 ff_database_items fdi1,
1187 ff_database_items fdi2,
1188 ff_database_items fdi3
1189 where ppa.payroll_action_id = p_payroll_action_id
1190 and ppa.payroll_action_id = paa.payroll_action_id
1191 and paa.tax_unit_id = p_tax_unit_id
1192 and fai.context1 = paa.assignment_action_id
1193 and fai.user_entity_id = fdi.user_entity_id
1194 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_MONTH'
1195 and fai1.context1 = fai.context1
1196 and fai1.user_entity_id = fdi1.user_entity_id
1197 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_MONTH'
1198 and fai2.context1 = fai1.context1
1199 and fai2.user_entity_id = fdi2.user_entity_id
1200 and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_MONTH'
1201 and fai3.context1 = fai1.context1
1202 and fai3.user_entity_id = fdi3.user_entity_id
1203 and fdi3.user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_MONTH'
1204 and length(translate(trim(fai.value),' .0123456789',' ')) is null
1205 and length(translate(trim(fai2.value),' .0123456789',' ')) is null ;
1206
1207 hr_utility.trace('Actual SUI Taxable Amount'||p_sui_taxable);
1208
1209
1210 return(0);
1211
1212 END GET_SMWL_SUI_WAGES;
1213
1214
1215 /* Function Added for Bug 14345202 */
1216
1217
1218 function get_ca_sqwl_wages(p_payroll_action_id number, --context
1219 p_tax_unit_id number, --context
1220 p_wage_plan_code in varchar2,
1221 p_sui_subj out nocopy number,
1222 p_sui_pre_tax out nocopy number,
1223 p_sui_taxable out nocopy number,
1224 p_sui_gross out nocopy number,
1225 p_sit_subj_nwhable out nocopy number,
1226 p_sit_subj_whable out nocopy number,
1227 p_sit_pre_tax out nocopy number,
1228 p_sit_withheld out nocopy number,
1229 p_sdi_pre_tax out nocopy number,
1230 p_sdi_subj_whable out nocopy number,
1231 p_sdi_taxable out nocopy number
1232
1233 )return number is
1234
1235 cursor c_get_emp_wages(c_payroll_action_id number,
1236 c_tax_unit_id number) is /* It will fetch required Person level DBIs*/
1237 select paa.assignment_action_id paa_id, fdi.user_name dbi_name, nvl(fai.value, '0') value
1238 from pay_payroll_actions ppa,
1239 pay_assignment_actions paa,
1240 ff_archive_items fai,
1241 ff_database_items fdi
1242 where ppa.payroll_action_id = c_payroll_action_id --1436067
1243 and ppa.payroll_action_id = paa.payroll_action_id
1244 and paa.tax_unit_id = c_tax_unit_id --7896
1245 and fai.context1 = paa.assignment_action_id
1246 and fai.user_entity_id = fdi.user_entity_id
1247 and fdi.user_name in
1248 ('A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD',
1249 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD',
1250 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD',
1251 'A_SUI_ER_GROSS_PER_JD_GRE_QTD',
1252 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD',
1253 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD',
1254 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD',
1255 'A_SIT_WITHHELD_PER_JD_GRE_QTD',
1256 'A_SDI_EE_PRE_TAX_REDNS_PER_JD_GRE_QTD',
1257 'A_SDI_EE_SUBJ_WHABLE_PER_JD_GRE_QTD',
1258 'A_SDI_EE_TAXABLE_PER_JD_GRE_QTD',
1259 'A_SCL_ASG_US_CA_WAGE_PLAN_CODE'
1260 )
1261 order by paa.assignment_action_id;
1262
1263
1264 ln_employee_count number;
1265
1266
1267 TYPE dbi_values is record(
1268 sui_subj varchar2(100),
1269 sui_pre_tax varchar2(100),
1270 sui_taxable varchar2(100),
1271 sui_gross varchar2(100),
1272 sit_subj_nwhable varchar2(100),
1273 sit_subj_whable varchar2(100),
1274 sit_pre_tax varchar2(100),
1275 sit_withheld varchar2(100),
1276 sdi_pre_tax varchar2(100),
1277 sdi_subj_whable varchar2(100),
1278 sdi_taxable varchar2(100),
1279 wage_plan_code varchar2(100)
1280 );
1281 dbi_val_record dbi_values;
1282 type dbi_val_rec IS table of dbi_val_record%type
1283 INDEX BY BINARY_INTEGER;
1284 dbi_rec_table dbi_val_rec;
1285
1286 BEGIN
1287 p_sui_subj := 0;
1288 p_sui_pre_tax := 0;
1289 p_sui_taxable := 0;
1290 p_sui_gross :=0;
1291 p_sit_subj_nwhable :=0;
1292 p_sit_subj_whable :=0;
1293 p_sit_pre_tax :=0;
1294 p_sit_withheld :=0;
1295 p_sdi_pre_tax :=0;
1296 p_sdi_subj_whable :=0;
1297 p_sdi_taxable :=0;
1298 ln_employee_count := 0;
1299
1300
1301 for c_get_emp_wages_rec in c_get_emp_wages(p_payroll_action_id,p_tax_unit_id) loop
1302
1303 if c_get_emp_wages_rec.dbi_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD' then
1304 dbi_rec_table(c_get_emp_wages_rec.paa_id).sui_subj := c_get_emp_wages_rec.value;
1305 elsif c_get_emp_wages_rec.dbi_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD' then
1306 dbi_rec_table(c_get_emp_wages_rec.paa_id).sui_pre_tax := c_get_emp_wages_rec.value;
1307 elsif c_get_emp_wages_rec.dbi_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD' then
1308 dbi_rec_table(c_get_emp_wages_rec.paa_id).sui_taxable := c_get_emp_wages_rec.value;
1309 elsif c_get_emp_wages_rec.dbi_name = 'A_SUI_ER_GROSS_PER_JD_GRE_QTD' then
1310 dbi_rec_table(c_get_emp_wages_rec.paa_id).sui_gross := c_get_emp_wages_rec.value;
1311 elsif c_get_emp_wages_rec.dbi_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD' then
1312 dbi_rec_table(c_get_emp_wages_rec.paa_id).sit_subj_nwhable := c_get_emp_wages_rec.value;
1313 elsif c_get_emp_wages_rec.dbi_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD' then
1314 dbi_rec_table(c_get_emp_wages_rec.paa_id).sit_subj_whable := c_get_emp_wages_rec.value;
1315 elsif c_get_emp_wages_rec.dbi_name = 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD' then
1316 dbi_rec_table(c_get_emp_wages_rec.paa_id).sit_pre_tax := c_get_emp_wages_rec.value;
1317 elsif c_get_emp_wages_rec.dbi_name = 'A_SIT_WITHHELD_PER_JD_GRE_QTD' then
1318 dbi_rec_table(c_get_emp_wages_rec.paa_id).sit_withheld := c_get_emp_wages_rec.value;
1319 elsif c_get_emp_wages_rec.dbi_name = 'A_SDI_EE_PRE_TAX_REDNS_PER_JD_GRE_QTD' then
1320 dbi_rec_table(c_get_emp_wages_rec.paa_id).sdi_pre_tax := c_get_emp_wages_rec.value;
1321 elsif c_get_emp_wages_rec.dbi_name = 'A_SDI_EE_SUBJ_WHABLE_PER_JD_GRE_QTD' then
1322 dbi_rec_table(c_get_emp_wages_rec.paa_id).sdi_subj_whable := c_get_emp_wages_rec.value;
1323 elsif c_get_emp_wages_rec.dbi_name = 'A_SDI_EE_TAXABLE_PER_JD_GRE_QTD' then
1324 dbi_rec_table(c_get_emp_wages_rec.paa_id).sdi_taxable := c_get_emp_wages_rec.value;
1325 elsif c_get_emp_wages_rec.dbi_name = 'A_SCL_ASG_US_CA_WAGE_PLAN_CODE' then
1326 dbi_rec_table(c_get_emp_wages_rec.paa_id).wage_plan_code := c_get_emp_wages_rec.value;
1327 end if;
1328 end loop;
1329
1330 for i in dbi_rec_table.first .. dbi_rec_table.last loop
1331 if dbi_rec_table.exists(i) then
1332 if (length(translate(trim(dbi_rec_table(i).sui_subj - dbi_rec_table(i).sui_pre_tax),' .0123456789',' ')) is null
1333 and length(translate(trim(dbi_rec_table(i).sit_withheld),' .0123456789',' ')) is null
1334 and length(translate(trim(dbi_rec_table(i).sit_subj_nwhable + dbi_rec_table(i).sit_subj_whable - dbi_rec_table(i).sit_pre_tax),' .0123456789',' ')) is null
1335 and dbi_rec_table(i).wage_plan_code = p_wage_plan_code
1336 ) then
1337
1338 p_sui_subj := p_sui_subj + dbi_rec_table(i).sui_subj;
1339 p_sui_pre_tax := p_sui_pre_tax +dbi_rec_table(i).sui_pre_tax;
1340 p_sui_taxable := p_sui_taxable +dbi_rec_table(i).sui_taxable;
1341 p_sui_gross := p_sui_gross +dbi_rec_table(i).sui_gross;
1342 p_sit_subj_nwhable := p_sit_subj_nwhable+dbi_rec_table(i).sit_subj_nwhable;
1343 p_sit_subj_whable := p_sit_subj_whable + dbi_rec_table(i).sit_subj_whable;
1344 p_sit_pre_tax := p_sit_pre_tax + dbi_rec_table(i).sit_pre_tax;
1345 p_sit_withheld := p_sit_withheld + dbi_rec_table(i).sit_withheld;
1346 p_sdi_pre_tax := p_sdi_pre_tax +dbi_rec_table(i).sdi_pre_tax;
1347 p_sdi_subj_whable := p_sdi_subj_whable +dbi_rec_table(i).sdi_subj_whable;
1348 p_sdi_taxable := p_sdi_taxable + dbi_rec_table(i).sdi_taxable;
1349 ln_employee_count := ln_employee_count +1;
1350 end if;
1351 end if;
1352 end loop;
1353 return(ln_employee_count);
1354 END get_ca_sqwl_wages;
1355
1356
1357
1358 /* Function Added for Bug 14456648 */
1359
1360 FUNCTION spl_char_check(p_value IN varchar2,
1361 p_state IN varchar2,
1362 p_report_format IN varchar2)
1363 RETURN VARCHAR2
1364
1365 IS
1366
1367 TYPE special_characters is record(
1368 p_character varchar2(100));
1369 character_val_record special_characters;
1370 type character_val_rec IS table of character_val_record%type
1371 INDEX BY BINARY_INTEGER;
1372 character_rec character_val_rec;
1373 l_stripped_value varchar2(100);
1374 l_param_length number(20);
1375
1376 Begin
1377
1378 character_rec(1).p_character :='.';
1379 character_rec(2).p_character :=':';
1380 character_rec(3).p_character :='(';
1381 character_rec(4).p_character :=')';
1382 character_rec(5).p_character :='_';
1383 character_rec(6).p_character :='*';
1384 character_rec(7).p_character :='`';
1385 character_rec(8).p_character :='^';
1386 character_rec(9).p_character :='%';
1387 character_rec(10).p_character :='$';
1388 character_rec(11).p_character :='#';
1389 character_rec(12).p_character :='@';
1390 character_rec(13).p_character :='!';
1391 character_rec(14).p_character :='~';
1392 character_rec(15).p_character :='+';
1393 character_rec(16).p_character :='=';
1394 character_rec(17).p_character :='?';
1395 character_rec(18).p_character :='/';
1396 character_rec(19).p_character :=',';
1397 character_rec(20).p_character :='-';
1398 character_rec(21).p_character :='[';
1399 character_rec(22).p_character :=']';
1400 character_rec(23).p_character :='{';
1401 character_rec(24).p_character :='}';
1402 character_rec(25).p_character :='|';
1403 character_rec(26).p_character :=';';
1404 character_rec(27).p_character :='"';
1405 character_rec(28).p_character :='''';
1406 character_rec(29).p_character :='>';
1407 character_rec(30).p_character :='<';
1408 character_rec(31).p_character :='&';
1409 character_rec(32).p_character :='\';
1410
1411
1412 l_stripped_value := p_value;
1413
1414 FOR i in 1 .. 32 LOOP
1415 if (p_state = 'CA' and p_report_format = 'ST') then
1416 if (character_rec(i).p_character not in ('.',',','&','/','-','''','"')) then
1417 l_stripped_value := replace(l_stripped_value,character_rec(i).p_character,' ');
1418 end if;
1419 end if;
1420
1421 END LOOP;
1422
1423 return l_stripped_value;
1424
1425 END spl_char_check;
1426
1427 END pay_us_sqwl_udf;
[Home] [Help]