[Home] [Help]
PACKAGE BODY: APPS.PAY_US_941_REPORT
Source
1 PACKAGE BODY PAY_US_941_REPORT AS
2 /* $Header: payus941report.pkb 120.28.12020000.3 2013/03/19 08:44:36 ybudamal ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_941_report
21
22 Description : This package is called for the 941 Report to
23 generate the XML file.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 15-APR-2005 pragupta 115.0 3685216 Created
30 21-APR-2005 pragupta 115.1 3685216 Created
31 18-MAY-2005 pragupta 115.2 3685216 Changes made in order to transit
32 from RTF to PDF Template. Also
33 field 14 to be filled additionally
34 on the report.
35 18-OCT-2005 pragupta 115.3 4682231 lv_STATE_ABBR_1 and lv_STATE_ABBR_2
36 to be nullified.
37 27-OCT-2005 pragupta 115.4 4687794 added CDATA section for GRE Name and
38 address.
39 02-DEC-2005 rdhingra 115.5 4769835 Performance changes. Updated Cursor
40 c_count_asg_processed in procedure
41 get_941_balances
42 06-APR-2006 pragupta 115.8 5117504 introduced tags YEAR1 and YEAR2.
43 30-OCT-2006 alikhar 115.9 5479800 Added procedure pay_us_941_report_wrapper
44 13-Mar-2009 skpatil 115.11 8267959 Removing addition of XML tags for Cobra values
45 25-Mar-2009 skpatil 115.12 8267963 Adding code to submit FND_REQUEST based on
46 release.
47 11-Mar-2010 vvijayku 115.13 9357061 Added code to split number into its integer and
48 decimal part. Also modified the XML tag generation
49 to incorporate the integer and decimal values.
50 24-Mar-2010 vvijayku 115.14 8772549 Added an exist clause to find the correct number of
51 employees who have wages in the quarter.
52 02-Apr-2010 vvijayku 115.15 8772549 Added code to Ignore the Information type classification
53 which has Pay Value not equal to 0.
54 08-Apr-2010 vvijayku 115.16 9556898 Moved the Splitting of the balance values into integer
55 and decimal parts out of the IF clause.
56 19-May-2010 vvijayku 115.17 9595127 Added condition in the function SPLIT_NUMBER_INTO_INT_DECIMAL
57 for returning the correctly formatted Integer part of the number
58 with the commas in the number.
59 9572713 Removed the ROUND function usage in the function
60 SPLIT_NUMBER_INTO_INT_DECIMAL, so as to return correct decimal
61 part of the actual number.
62 15-Jun-2010 vvijayku 115.19 9496602 Added new cursors c_count_qualified_first_paid and
63 c_count_qualified_paid. Also have modified the XML generation
64 code and have added new tags to be displayed as per the 941-PDF
65 2010 Q2 requirements.
66 17-Jun-2010 vvijayku 115.20 9496602 Modified the cursor c_run_balance_value to fetch the balance value
67 for SS ER W11 Taxable based on the qualified employees paid.
68 22-Jun-2010 vvijayku 115.21 9588208 Modified the c_count_asg_processed cursor to resolve the performance
69 issue mentioned in the bug 9588208
70 23-Jun-2010 vvijayku 115.22 9496602 Reverted back the changes that were introduced in the version 115.20.
71 29-Jun-2010 vvijayku 115.23 9921613 Added condition to handle Balance Adjustment as well for the lines 6a
72 and 6b in 941 report.
73 12-Aug-2010 vvijayku 115.24 9921467 Modified the query in the cursor c_count_asg_processed for improving
74 the performance of the 941 report.
75 07-Sep-2010 vvijayku 115.25 10091673 Updated the cursor c_count_qualified_first_paid with adjustments for
76 including the Quarter 3 reporting requirements for the field 6a.
77 11-Oct-2010 vvijayku 115.26 10097699 Included code to append 0 cents to the decimal value if not already
78 present.
79 08-Nov-2010 vvijayku 115.27 10269211 Updated the cursor c_count_qualified_first_paid with adjustments for
80 including the Quarter 4 reporting requirements for the field 6a.
81 11-Mar-2010 nkjaladi 115.28 11831970 Commenting out the code for deriving the hire act wages as
82 currently this is not required to report in 941 report.
83 15-Mar-2010 nkjaladi 115.29 11831970 Modified tax rate for SS wages and tips from .124 to .104
84 13-Jul-2012 ybudamal 115.30 14181799 Modified the query in the cursor c_count_asg_processed to fetch the
85 correct Employee Count.
86 19-Mar-2013 ybudamal 115.31 16211747 Modified the package for new Medicare reporting changes effective
87 2013.
88 ************************************************************************/
89 g_CP_STATUS VARCHAR2(20);
90 g_c_qtr_end_date DATE;
91 g_package VARCHAR2(100) := 'pay_us_941_report';
92
93 /****************************************************************************
94 Name : SPLIT_NUMBER_INTO_INT_DECIMAL
95 Description : This function splits the given number into its integer and
96 decimal part.
97 *****************************************************************************/
98 FUNCTION SPLIT_NUMBER_INTO_INT_DECIMAL -- Bug 9357061: Function start.
99 (
100 P_NUMBER IN NUMBER,
101 P_DEC IN NUMBER,
102 P_INTEGER_PART OUT NOCOPY VARCHAR2,
103 P_DECIMAL_PART OUT NOCOPY VARCHAR2
104 ) RETURN NUMBER AS
105
106 ln_pos_dec NUMBER;
107 ln_integer_part NUMBER;
108
109 BEGIN
110 hr_utility.trace ('Entering number splitting function');
111
112 ln_pos_dec := INSTR(P_NUMBER,substr(ltrim(to_char(.3,'0D0')),2,1));
113
114 IF ln_pos_dec > 0 THEN
115
116 ln_integer_part := SUBSTR(P_NUMBER, 0 , ln_pos_dec-1); --Bug 9595127
117
118 P_DECIMAL_PART := to_char(SUBSTR(P_NUMBER, ln_pos_dec+1 , p_dec));
119
120 ELSIF ln_pos_dec = 0 THEN
121
122 ln_integer_part := P_NUMBER; --Bug 9595127
123
124 P_DECIMAL_PART := NULL;
125
126 END IF;
127
128 P_INTEGER_PART := to_char(ln_integer_part,'999,999,999,999');
129
130 --Bug 10097699
131 IF length(P_DECIMAL_PART) = 1 THEN
132 P_DECIMAL_PART := rpad(P_DECIMAL_PART,2,'0');
133 END IF;
134
135 hr_utility.trace ('Leaving number splitting function');
136 RETURN (0);
137
138 END SPLIT_NUMBER_INTO_INT_DECIMAL; -- Bug 9357061: Function end.
139
140 /* Initialization: Setting session variables etc. */
141 PROCEDURE set_session_variables_contexts(
142 p_business_group_id IN NUMBER,
143 p_tax_unit_id IN NUMBER,
144 p_year IN VARCHAR2,
145 p_qtr IN VARCHAR2)
146 IS
147 ld_quarter_start_date date;
148 ld_quarter_end_date date;
149 BEGIN
150
151 ld_quarter_start_date := TRUNC(TO_DATE(p_qtr||'-'||p_year,'DD-MM-YYYY'),'Q');
152 ld_quarter_end_date := TO_DATE(p_qtr||'-'||p_year,'DD-MM-YYYY');
153 -- Check for the validity of all the balances used by the report
154 g_CP_STATUS := pay_us_payroll_utils.check_balance_status(
155 p_start_date => ld_quarter_start_date,
156 p_business_group_id => p_business_group_id,
157 p_attribute_name => '941_FED',
158 p_legislation_code => 'US');
159
160 /* If all the balances used by the report are valid then set session
161 variables and contexts */
162 IF g_CP_STATUS = 'Y' THEN
163 pay_us_balance_view_pkg.set_session_var('GROUP_RB_REPORT','TRUE');
164 pay_us_balance_view_pkg.set_session_var('REPORT_TYPE','W2');
165 pay_us_balance_view_pkg.set_session_var('GROUP_RB_SDATE',
166 ld_quarter_start_date);
167 pay_us_balance_view_pkg.set_session_var('GROUP_RB_EDATE',
168 ld_quarter_end_date);
169 pay_balance_pkg.set_context(
170 'DATE_EARNED',
171 fnd_date.date_to_canonical(ld_quarter_end_date));
172 pay_balance_pkg.set_context(
173 'BALANCE_DATE',
174 fnd_date.date_to_canonical(ld_quarter_end_date));
175 END IF;
176
177 EXCEPTION WHEN no_data_found THEN
178 RAISE;
179 END set_session_variables_contexts;
180
181
182
183 PROCEDURE get_941_balances(p_business_group_id IN NUMBER,
184 p_tax_unit_id IN NUMBER,
185 p_year IN VARCHAR2,
186 p_qtr IN VARCHAR2)
187 IS
188
189 CURSOR c_gre_info(cp_tax_unit_id NUMBER) IS
190 SELECT hou.name, -- GRE Name
191 hoi_ein.org_information1, -- EIN
192 hrl.address_line_1,
193 hrl.address_line_2,
194 hrl.address_line_3,
195 hrl.town_or_city,
196 hrl.region_2,
197 hrl.postal_code
198 FROM hr_organization_units hou,
199 hr_organization_information hoi_bg,
200 hr_organization_information hoi_ein ,
201 hr_locations hrl
202 WHERE hou.organization_id = cp_tax_unit_id
203 AND hoi_bg.organization_id = hou.organization_id
204 and hoi_bg.org_information_context = 'CLASS'
205 AND hoi_bg.org_information1 = 'HR_LEGAL'
206 AND hoi_ein.organization_id(+) = hou.organization_id
207 AND nvl(hoi_ein.org_information_context(+),'Employer Identification') = 'Employer Identification'
208 AND hrl.location_id = hou.location_id;
209
210 l_gre_name VARCHAR2(240);
211 l_gre_EIN VARCHAR2(150);
212 l_ADDRESS VARCHAR2(720);
213 l_ADDRESS_LINE_1 VARCHAR2(240);
214 l_ADDRESS_LINE_2 VARCHAR2(240);
215 l_ADDRESS_LINE_3 VARCHAR2(240);
216 l_CITY VARCHAR2(30);
217 l_STATE VARCHAR2(120);
218 l_ZIP VARCHAR2(30);
219 l_gre_EIN_1 VARCHAR2(2); -- Bug 9357061
220 l_gre_EIN_2 VARCHAR2(2); -- Bug 9357061
221 l_gre_EIN_3 VARCHAR2(2); -- Bug 9357061
222 l_gre_EIN_4 VARCHAR2(2); -- Bug 9357061
223 l_gre_EIN_5 VARCHAR2(2); -- Bug 9357061
224 l_gre_EIN_6 VARCHAR2(2); -- Bug 9357061
225 l_gre_EIN_7 VARCHAR2(2); -- Bug 9357061
226 l_gre_EIN_8 VARCHAR2(2); -- Bug 9357061
227 l_gre_EIN_9 VARCHAR2(2); -- Bug 9357061
228
229 --******** Employee Count (declarations start)
230 CURSOR c_count_asg_processed(cp_tax_unit_id IN number,
231 cp_qtr IN VARCHAR2,
232 cp_year IN VARCHAR2,
233 cp_def_bal_id IN NUMBER) IS
234 SELECT COUNT(DISTINCT person_id) -- #Bug 14181799
235 FROM
236 (
237 SELECT /*+ LEADING(ptp) -- For Bug 4769835
238 INDEX (ptp PER_TIME_PERIODS_N50)
239 USE_NL(ptp ppa)
240 */
241 paf.person_id person_id,
242 paa.assignment_id assignment_id, -- #Bug 14181799
243 pay_balance_pkg.get_value( cp_def_bal_id,
244 max(paa.assignment_action_id),
245 cp_tax_unit_id,
246 NULL,
247 NULL,
248 NULL,
249 NULL,
250 NULL,
251 NULL,
252 'TRUE') val
253 FROM per_time_periods ptp,
254 per_assignments_f paf,
255 per_assignments_f paf1,
256 pay_assignment_actions paa, pay_payroll_actions ppa
257 WHERE ptp.end_date >= TO_DATE('01'||TO_CHAR
258 (TRUNC(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'), 'Q'),'MM') ||
259 TO_CHAR(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'YYYY'),'DD-MM-YYYY')
260 AND ptp.start_date <= TO_DATE('12'||TO_CHAR
261 (to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'MM') ||
262 TO_CHAR(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'YYYY'),'DD-MM-YYYY')
263 AND ppa.effective_date >= ptp.start_date -- For Bug 4769835
264 AND ppa.effective_date <= ptp.end_date -- For Bug 4769835
265 AND ppa.payroll_id = ptp.payroll_id -- For Bug 4769835
266 AND ppa.time_period_id = ptp.time_period_id
267 AND ppa.action_type IN ('R', 'Q')
268 AND ppa.payroll_action_id = paa.payroll_action_id
269 AND paf1.assignment_id = paa.assignment_id
270 AND paf.person_id = paf1.person_id
271 AND paa.run_type_id IS NULL
272 AND paa.tax_unit_id = cp_tax_unit_id
273 AND paa.action_status = 'C'
274 group by paf.person_id,paa.assignment_id -- #Bug 14181799
275 )
276 WHERE val>0;
277
278
279 ln_emp_count NUMBER;
280 ln_def_bal_id NUMBER;
281 --******** Employee Count (declarations end)
282
283 /* #11831970 start
284 Commenting this code as this is not required to calculate this
285 value to for 941 report
286
287 --******** Qualified Employee First Paid Count (declarations start)
288 CURSOR c_count_qualified_first_paid(cp_tax_unit_id IN number,
289 cp_qtr IN VARCHAR2,
290 cp_year IN VARCHAR2) IS
291 SELECT COUNT(DISTINCT pps.person_id)
292 FROM per_periods_of_service pps,
293 per_all_assignments_f paaf,
294 pay_assignment_actions paa,
295 pay_payroll_actions ppa,
296 pay_us_emp_fed_tax_rules_f ftr
297 WHERE pps.date_start BETWEEN TO_DATE('04-02-2010','DD-MM-YYYY') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
298 AND paaf.person_id = pps.person_id
299 AND paa.tax_unit_id = cp_tax_unit_id
300 AND paa.assignment_id = paaf.assignment_id
301 AND ftr.assignment_id = paa.assignment_id
302 AND ftr.fed_information2 = 'Y'
303 AND ppa.payroll_action_id = paa.payroll_action_id
304 AND ppa.action_type in ('H','M','B')
305 AND ppa.action_status = 'C'
306 AND ppa.effective_date BETWEEN TRUNC(TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY'),'Q') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
307 AND NOT EXISTS (SELECT 'Y'
308 FROM pay_payroll_actions ppa1,
309 pay_assignment_actions paa1
310 WHERE paa1.assignment_id = ftr.assignment_id
311 AND ppa1.payroll_action_id = paa1.payroll_action_id
312 AND ppa1.action_type in ('H','M','B')
313 AND ppa1.action_status = 'C'
314 AND ppa1.effective_date BETWEEN TO_DATE('19-03-2010','DD-MM-YYYY') AND TO_DATE('30-09-2010','DD-MM-YYYY'));
315
316 ln_qua_emp_first_paid_count NUMBER;
317
318 --******** Qualified Employee First Paid Count (declarations end)
319
320 --******** Qualified Employee Paid Count (declarations start)
321 CURSOR c_count_qualified_paid(cp_tax_unit_id IN number,
322 cp_qtr IN VARCHAR2,
323 cp_year IN VARCHAR2) IS
324 SELECT COUNT(DISTINCT pps.person_id)
325 FROM per_periods_of_service pps,
326 per_all_assignments_f paaf,
327 pay_assignment_actions paa,
328 pay_payroll_actions ppa,
329 pay_us_emp_fed_tax_rules_f ftr
330 WHERE pps.date_start BETWEEN TO_DATE('04-02-2010','DD-MM-YYYY') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
331 AND paaf.person_id = pps.person_id
332 AND paa.tax_unit_id = cp_tax_unit_id
333 AND paa.assignment_id = paaf.assignment_id
334 AND ftr.assignment_id = paa.assignment_id
335 AND ftr.fed_information2 = 'Y'
336 AND ppa.payroll_action_id = paa.payroll_action_id
337 AND ppa.action_type in ('H','M','B')
338 AND ppa.action_status = 'C'
339 AND ppa.effective_date BETWEEN TRUNC(TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY'),'Q') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY');
340
341 ln_qua_emp_paid_count NUMBER;
342
343 --******** Qualified Employee Paid Count (declarations end)
344 #11831970 End */
345 --******** Run Balance Valid (declarations start)
346 CURSOR c_run_balance_valid(cp_business_group_id IN number,
347 cp_defined_balance_id IN VARCHAR2) IS
348 SELECT pbv.run_balance_status
349 FROM pay_balance_validation pbv
350 WHERE pbv.defined_balance_id = cp_defined_balance_id
351 AND pbv.business_group_id = cp_business_group_id
352 AND pbv.balance_load_date <= to_date ('01-01-2010','DD-MM-YYYY');
353
354 lv_balance_status VARCHAR2(1);
355
356 --******** Run Balance Valid (declarations end)
357
358 --******** Run Balance Value (declarations starts)
359 CURSOR c_run_balance_value(cp_tax_unit_id IN number,
360 cp_balance_type_id IN number,
361 cp_balance_dimension_id IN number,
362 cp_qtr IN varchar2,
363 cp_year IN varchar2) IS
364 SELECT NVL(SUM(prb.balance_value),0)
365 FROM pay_run_balances prb,
366 pay_defined_balances pdb
367 where pdb.balance_type_id = cp_balance_type_id
368 and pdb.balance_dimension_id = cp_balance_dimension_id
369 and pdb.defined_balance_id = prb.defined_balance_id
370 and prb.tax_unit_id = cp_tax_unit_id
371 and prb.effective_date <= TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
372 and prb.effective_date >= TRUNC(TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY'),'Q');
373
374 --******** Run Balance Value (declarations ends)
375
376 --******** State Abbreviation (declarations start)
377 /* CURSOR c_state_abbr(cp_tax_unit_id IN number) IS
378 SELECT ORG_INFORMATION1
379 FROM HR_ORGANIZATION_INFORMATION_V
380 WHERE org_information_context = 'State Tax Rules'
381 AND ORGANIZATION_ID = cp_tax_unit_id;
382 ln_count_state NUMBER;
383 lv_STATE_ABBR_1 VARCHAR2(1);
384 lv_STATE_ABBR_2 VARCHAR2(1);
385 lv_STATE_ABBR VARCHAR2(150);*/
386 --******** State Abbreviation (declarations end)
387
388 --******** Generate Federal Tax Balances (declarations start)
389 -- Get details for Total Wages
390 -- Cursor to fetch the balance values making use of the BRA
391 -- The cursor uses the view pay_us_federal_tax_bal_gre_v which
392 -- gets the balance information from the table pay_balance_sets.
393 CURSOR c_bal_values (cp_tax_unit_id IN NUMBER) IS
394 SELECT d_tax_otd_value,
395 d_wage_otd_value,
396 d_tax_type
397 FROM pay_us_federal_tax_bal_gre_v
398 WHERE d_balance_set_name = '941_QTD'
399 AND d_tax_unit_id = cp_tax_unit_id;
400
401
402 /* Added for Bug#16211747 */
403 CURSOR get_defined_balance_id (cp_balance_name IN VARCHAR2,
404 cp_balance_dimension IN VARCHAR2)
405 IS
406 SELECT pdb.defined_balance_id
407 FROM pay_defined_balances pdb,
408 pay_balance_types pbt,
409 pay_balance_dimensions pbd
410 WHERE pbt.balance_name = cp_balance_name
411 AND pbt.legislation_code = 'US'
412 AND pbd.database_item_suffix = cp_balance_dimension
413 AND pbd.legislation_code = 'US'
414 AND pdb.balance_type_id = pbt.balance_type_id
415 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
416
417 -- Local variables to hold the balance values
418 ln_regular_earnings NUMBER;
419 ln_fit_withheld NUMBER;
420 ln_fit_withheld_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
421 ln_fit_withheld_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
422 ln_medicare_er_taxable NUMBER;
423 ln_medicare_er_taxable_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
424 ln_medicare_er_taxable_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
425 ln_ss_er_taxable NUMBER;
426 ln_ss_er_taxable_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
427 ln_ss_er_taxable_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
428 ln_eic_advance NUMBER;
429 ln_eic_advance_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
430 ln_eic_advance_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
431 ln_pretax_dedn NUMBER;
432 ln_fit_non_w2_pretax_dedn NUMBER;
433 ln_ss_tips NUMBER;
434 ln_ss_tips_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
435 ln_ss_tips_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
436 ln_w2_uncoll_med_gtl NUMBER;
437 ln_w2_uncoll_med_tips NUMBER;
438 ln_w2_uncoll_ss_gtl NUMBER;
439 ln_w2_uncoll_ss_tax_tips NUMBER;
440 ln_w2_uncoll_med_ss NUMBER;
441 ln_w2_uncoll_med_ss_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
442 ln_w2_uncoll_med_ss_dec VARCHAR(3) := NULL; -- Bug 9357061, Bug 9595127
443 ln_supp_earn_nwfit NUMBER;
444 ln_supp_earn_fit NUMBER;
445 ln_pretax_dedn_fit NUMBER;
446 ln_dummy NUMBER;
447
448 lv_tax_type VARCHAR2(50);
449 ln_tax NUMBER;
450 ln_wage NUMBER;
451
452 ln_total_wages NUMBER;
453 ln_total_wages_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
454 ln_total_wages_dec VARCHAR(3) := NULL; -- Bug 9357061, Bug 9595127
455 ln_taxable_ss_wage NUMBER;
456 ln_taxable_ss_wage_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
457 ln_taxable_ss_wage_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
458 ln_941_box5b NUMBER;
459 ln_941_box5b_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
460 ln_941_box5b_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
461 ln_taxable_medicare NUMBER;
462 ln_taxable_medicare_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
463 ln_taxable_medicare_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
464 ln_tot_ss_medicare_tax NUMBER;
465 ln_tot_ss_medicare_tax_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127
466 ln_tot_ss_medicare_tax_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127
467 -- ln_tot_tax_bfr_adj NUMBER; Commented for #11831970
468 -- ln_tot_tax_bfr_adj_main VARCHAR2(50) := NULL; -- Bug 9357061, Bug 9595127 Commented for #11831970
469 -- ln_tot_tax_bfr_adj_dec VARCHAR2(3) := NULL; -- Bug 9357061, Bug 9595127 Commented for #11831970
470 -- ln_ss_er_w11_taxable NUMBER; Commented for #11831970
471 -- ln_ss_er_w11_taxable_main VARCHAR2(50) := NULL; Commented for #11831970
472 -- ln_ss_er_w11_taxable_dec VARCHAR2(3) := NULL; Commented for #11831970
473 -- ln_qua_taxable_ss_wages NUMBER; Commented for #11831970
474 -- ln_qua_taxable_ss_wages_main VARCHAR2(50) := NULL; Commented for #11831970
475 -- ln_qua_taxable_ss_wages_dec VARCHAR2(3) := NULL; Commented for #11831970
476 ln_defined_balance_id NUMBER;
477 ln_balance_type_id NUMBER;
478 ln_balance_dimension_id NUMBER;
479
480 /* Added for Bug#16211747 */
481 ln_med_er_tax_ovr_lmt NUMBER;
482 ln_med_er_tax_ovr_lmt_main VARCHAR2(50) := NULL;
483 ln_med_er_tax_ovr_lmt_dec VARCHAR2(3) := NULL;
484 ln_tax_med_er_ovr_lmt NUMBER;
485 ln_tax_med_er_ovr_lmt_main VARCHAR2(50) := NULL;
486 ln_tax_med_er_ovr_lmt_dec VARCHAR2(3) := NULL;
487
488 vCtr NUMBER;
489 ln_func_value NUMBER; -- Bug 9357061
490 ln_dec_digits NUMBER; -- Bug 9357061
491
492 BEGIN
493 vCtr := 0;
494 ln_dec_digits := 2;
495 -- GRE Info
496 OPEN c_gre_info(p_tax_unit_id);
497 FETCH c_gre_info
498 INTO l_gre_name, l_gre_ein, l_ADDRESS_LINE_1, l_ADDRESS_LINE_2,
499 l_ADDRESS_LINE_3, l_CITY, l_STATE, l_ZIP;
500 CLOSE c_gre_info;
501
502
503 l_ADDRESS := l_ADDRESS_LINE_1 || ' '
504 || l_ADDRESS_LINE_2 || ' '
505 || l_ADDRESS_LINE_3;
506 IF LENGTH(l_ADDRESS) > 80 THEN
507 l_ADDRESS := l_ADDRESS_LINE_1 || ' ' || l_ADDRESS_LINE_2;
508 IF LENGTH (l_ADDRESS) > 80 THEN
509 l_ADDRESS := l_ADDRESS_LINE_1;
510 IF LENGTH (l_ADDRESS) > 80 THEN
511 l_ADDRESS := SUBSTR(l_ADDRESS_LINE_1, 1, 80);
512 END IF;
513 END IF;
514 END IF;
515
516 /* Bug 9357061: The Splitting of the EIN begin */
517
518 l_gre_EIN_1 := SUBSTR(l_gre_ein,1,1);
519 l_gre_EIN_2 := SUBSTR(l_gre_ein,2,1);
520 l_gre_EIN_3 := SUBSTR(l_gre_ein,-7,1);
521 l_gre_EIN_4 := SUBSTR(l_gre_ein,-6,1);
522 l_gre_EIN_5 := SUBSTR(l_gre_ein,-5,1);
523 l_gre_EIN_6 := SUBSTR(l_gre_ein,-4,1);
524 l_gre_EIN_7 := SUBSTR(l_gre_ein,-3,1);
525 l_gre_EIN_8 := SUBSTR(l_gre_ein,-2,1);
526 l_gre_EIN_9 := SUBSTR(l_gre_ein,-1,1);
527
528 /* Bug 9357061: The Splitting of the EIN End */
529
530 SELECT pdb.defined_balance_id
531 INTO ln_def_bal_id
532 FROM pay_defined_balances pdb,
533 pay_balance_types pbt,
534 pay_balance_dimensions pbd
535 WHERE pbt.balance_name = 'Gross Earnings'
536 AND pbt.legislation_code = 'US'
537 AND pbd.database_item_suffix = '_ASG_QTD'
538 AND pbd.legislation_code = 'US'
539 AND pdb.balance_type_id = pbt.balance_type_id
540 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
541
542 /*SELECT USERENV('SESSIONID')
543 INTO g_session_id
544 FROM DUAL;*/
545
546 -- Employee Count
547 OPEN c_count_asg_processed (p_tax_unit_id, p_qtr, p_year,ln_def_bal_id);
548 FETCH c_count_asg_processed INTO ln_emp_count;
549 CLOSE c_count_asg_processed;
550
551 /* #11831970 start
552 Commenting this code as this is not required to calculate this
553 value to for 941 report
554 -- Qualified First Paid Employee Count
555 OPEN c_count_qualified_first_paid (p_tax_unit_id, p_qtr, p_year);
556 FETCH c_count_qualified_first_paid INTO ln_qua_emp_first_paid_count;
557 CLOSE c_count_qualified_first_paid;
558
559
560 -- Qualified Paid Employee Count
561 OPEN c_count_qualified_paid (p_tax_unit_id, p_qtr, p_year);
562 FETCH c_count_qualified_paid INTO ln_qua_emp_paid_count;
563 CLOSE c_count_qualified_paid;
564
565 #11831970 end */
566
567 -- State Abbreviation
568 /* SELECT count(DISTINCT(ORG_INFORMATION1))
569 INTO ln_count_state
570 FROM HR_ORGANIZATION_INFORMATION_V
571 WHERE org_information_context = 'State Tax Rules'
572 AND ORGANIZATION_ID = p_tax_unit_id;
573
574 IF ln_count_state > 1 THEN
575 lv_STATE_ABBR_1 := 'M';
576 lv_STATE_ABBR_2 := 'U';
577 END IF;
578
579 IF ln_count_state = 1 THEN
580 OPEN c_state_abbr(p_tax_unit_id);
581 FETCH c_state_abbr INTO lv_STATE_ABBR;
582 CLOSE c_state_abbr;
583 lv_STATE_ABBR_1 := SUBSTR(lv_STATE_ABBR, 1, 1);
584 lv_STATE_ABBR_2 := SUBSTR(lv_STATE_ABBR, 2, 1);
585 END IF; */
586
587 /* Added for Bug#16211747 */
588 OPEN get_defined_balance_id('Medicare EE Taxable Over Limit', '_GRE_QTD');
589 FETCH get_defined_balance_id INTO ln_def_bal_id;
590 CLOSE get_defined_balance_id;
591
592 /***********************************************************
593 ** Fetch Balance value
594 ***********************************************************/
595 g_c_qtr_end_date := TO_DATE(p_qtr||'-'||p_year,'DD-MM-YYYY');
596 -- If all the balances being reported are valid then make use of the BRA
597
598 IF g_CP_STATUS = 'Y' THEN
599
600 OPEN c_bal_values(p_tax_unit_id) ;
601 LOOP
602 FETCH c_bal_values INTO ln_tax, ln_wage, lv_tax_type ;
603 EXIT WHEN c_bal_values%NOTFOUND ;
604
605 IF lv_tax_type = 'FIT' THEN
606 ln_total_wages := NVL(ln_wage,0);
607 ln_fit_withheld := NVL(ln_tax,0);
608
609 ELSIF lv_tax_type = 'MEDICARE' THEN
610 ln_medicare_er_taxable := NVL(ln_wage,0);
611
612 ELSIF lv_tax_type = 'SS' THEN
613
614 ln_ss_er_taxable := NVL(ln_wage,0);
615 ELSIF lv_tax_type = 'EIC' THEN
616 ln_eic_advance := NVL(ln_tax,0);
617
618 ELSIF lv_tax_type = 'W2_BOX_7' THEN
619 ln_ss_tips := NVL(ln_tax,0);
620
621 ELSIF lv_tax_type = 'W2_UNCOLL_MED_TIPS' THEN
622 ln_w2_uncoll_med_tips := NVL(ln_tax,0);
623
624 ELSIF lv_tax_type = 'W2_UNCOLL_SS_GTL' THEN
625 ln_w2_uncoll_ss_gtl := NVL(ln_tax,0);
626
627 ELSIF lv_tax_type = 'W2_UNCOLL_MED_GTL' THEN
628 ln_w2_uncoll_med_gtl := NVL(ln_tax,0);
629
630 ELSIF lv_tax_type = 'W2_UNCOLL_SS_TAX_TIPS' THEN
631 ln_w2_uncoll_ss_tax_tips := NVL(ln_tax,0);
632 END IF;
633
634 END LOOP;
635 CLOSE c_bal_values;
636 ln_ss_er_taxable := ln_ss_er_taxable - ln_ss_tips;
637
638 /* Added for Bug#16211747 */
639 ln_wage := pay_balance_pkg.get_value(p_defined_balance_id => ln_def_bal_id,
640 p_assignment_action_id => NULL);
641
642 ln_med_er_tax_ovr_lmt := NVL(ln_wage, 0);
643
644 -- Else use the previous group calls to fetch the balance values
645 ELSE
646 pay_us_taxbal_view_pkg.us_gp_multiple_gre_qtd
647 (p_tax_unit_id => p_tax_unit_id ,
648 p_effective_date => g_c_qtr_end_date,
649 p_balance_name1 => 'Regular Earnings',
650 p_balance_name2 => 'FIT Withheld',
651 p_balance_name3 => 'Medicare ER Taxable',
652 p_balance_name4 => 'SS ER Taxable',
653 p_balance_name5 => 'EIC Advance',
654 p_balance_name6 => 'Pre Tax Deductions',
655 p_balance_name7 => 'FIT Non W2 Pre Tax Dedns',
656 p_balance_name8 => 'W2 BOX 7',
657 p_balance_name9 => 'W2 Uncoll Med GTL',
658 p_balance_name10 => 'W2 Uncoll Med Tips',
659 p_balance_name11 => 'W2 Uncoll SS GTL',
660 p_balance_name12 => 'W2 Uncoll SS Tax Tips',
661 p_value1 => ln_regular_earnings,
662 p_value2 => ln_fit_withheld,
663 p_value3 => ln_medicare_er_taxable,
664 p_value4 => ln_ss_er_taxable,
665 p_value5 => ln_eic_advance,
666 p_value6 => ln_pretax_dedn,
667 p_value7 => ln_fit_non_w2_pretax_dedn,
668 p_value8 => ln_ss_tips,
669 p_value9 => ln_w2_uncoll_med_gtl,
670 p_value10 => ln_w2_uncoll_med_tips,
671 p_value11 => ln_w2_uncoll_ss_gtl,
672 p_value12 => ln_w2_uncoll_ss_tax_tips);
673
674 /* Added for Bug#16211747 */
675 pay_us_taxbal_view_pkg.us_gp_multiple_gre_qtd
676 (p_tax_unit_id => p_tax_unit_id ,
677 p_effective_date => g_c_qtr_end_date,
678 p_balance_name1 => 'Medicare EE Taxable Over Limit',
679 p_balance_name2 => NULL,
680 p_balance_name3 => NULL,
681 p_balance_name4 => NULL,
682 p_balance_name5 => NULL,
683 p_balance_name6 => NULL,
684 p_balance_name7 => NULL,
685 p_balance_name8 => NULL,
686 p_balance_name9 => NULL,
687 p_balance_name10 => NULL,
688 p_balance_name11 => NULL,
689 p_balance_name12 => NULL,
690 p_value1 => ln_med_er_tax_ovr_lmt,
691 p_value2 => ln_dummy,
692 p_value3 => ln_dummy,
693 p_value4 => ln_dummy,
694 p_value5 => ln_dummy,
695 p_value6 => ln_dummy,
696 p_value7 => ln_dummy,
697 p_value8 => ln_dummy,
698 p_value9 => ln_dummy,
699 p_value10 => ln_dummy,
700 p_value11 => ln_dummy,
701 p_value12 => ln_dummy);
702
703 pay_us_taxbal_view_pkg.us_gp_subject_to_tax_gre_qtd
704 (p_balance_name1 => 'Supplemental Earnings for NWFIT',
705 p_balance_name2 => 'Supplemental Earnings for FIT',
706 p_balance_name3 => 'Pre Tax Deductions for FIT',
707 p_balance_name4 => NULL,
708 p_balance_name5 => NULL,
709 p_effective_date => g_c_qtr_end_date,
710 p_tax_unit_id => p_tax_unit_id,
711 p_value1 => ln_supp_earn_nwfit,
712 p_value2 => ln_supp_earn_fit,
713 p_value3 => ln_pretax_dedn_fit,
714 p_value4 => ln_dummy,
715 p_value5 => ln_dummy);
716
717
718 -- Assign the Fed Wages Tips Balances
719 ln_total_wages := ln_regular_earnings
720 + ln_supp_earn_fit
721 + ln_supp_earn_nwfit
722 - ln_pretax_dedn
723 - ln_pretax_dedn_fit
724 - ln_fit_non_w2_pretax_dedn;
725
726
727 ln_ss_er_taxable := ln_ss_er_taxable - ln_ss_tips;
728
729 END IF;
730
731 /* #11831970 start
732 Commenting this code as this is not required to calculate this
733 value to for 941 report
734
735 select defined_balance_id, balance_type_id, balance_dimension_id
736 into ln_defined_balance_id, ln_balance_type_id, ln_balance_dimension_id
737 from pay_defined_balances
738 where balance_type_id = (select balance_type_id
739 from pay_balance_types
740 where balance_name = 'SS ER W11 Taxable'
741 and legislation_code = 'US')
742 and balance_dimension_id = (select balance_dimension_id
743 from pay_balance_dimensions
744 where database_item_suffix = '_ASG_GRE_RUN'
745 and legislation_code = 'US');
746
747 open c_run_balance_valid (p_business_group_id, ln_defined_balance_id);
748 fetch c_run_balance_valid into lv_balance_status;
749 close c_run_balance_valid;
750
751 IF lv_balance_status = 'V' THEN
752
753 OPEN c_run_balance_value(p_tax_unit_id, ln_balance_type_id, ln_balance_dimension_id, p_qtr, p_year);
754 FETCH c_run_balance_value INTO ln_ss_er_w11_taxable;
755 CLOSE c_run_balance_value;
756
757 ELSE
758
759 pay_us_taxbal_view_pkg.us_gp_multiple_gre_qtd_ss_w11
760 (p_tax_unit_id => p_tax_unit_id ,
761 p_effective_date => g_c_qtr_end_date,
762 p_balance_name1 => 'SS ER W11 Taxable',
763 p_value1 => ln_ss_er_w11_taxable);
764
765 END IF;
766
767
768 ln_ss_er_taxable := ln_ss_er_taxable + ln_ss_er_w11_taxable;
769
770 ln_qua_taxable_ss_wages := NVL(ln_ss_er_w11_taxable,0) * 0.062;
771 #11831970 end
772 */
773
774 /* Bug 9357061: Splitting of Integer part and Decimal part begin */
775
776 IF ln_fit_withheld <> 0 THEN -- Bug 9595127
777 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
778 (
779 P_NUMBER => ln_fit_withheld,
780 P_DEC => ln_dec_digits,
781 P_INTEGER_PART => ln_fit_withheld_main,
782 P_DECIMAL_PART => ln_fit_withheld_dec
783 );
784 END IF;
785
786 IF ln_ss_tips <> 0 THEN --Bug 9595127
787 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
788 (
789 P_NUMBER => ln_ss_tips,
790 P_DEC => ln_dec_digits,
791 P_INTEGER_PART => ln_ss_tips_main,
792 P_DECIMAL_PART => ln_ss_tips_dec
793 );
794 END IF;
795
796 IF ln_total_wages <> 0 THEN --Bug 9595127
797 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
798 (
799 P_NUMBER => ln_total_wages,
800 P_DEC => ln_dec_digits,
801 P_INTEGER_PART => ln_total_wages_main,
802 P_DECIMAL_PART => ln_total_wages_dec
803 );
804 END IF;
805 /* #11831970 Start
806 IF ln_ss_er_w11_taxable <> 0 THEN --Bug 9595127
807 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
808 (
809 P_NUMBER => ln_ss_er_w11_taxable,
810 P_DEC => ln_dec_digits,
811 P_INTEGER_PART => ln_ss_er_w11_taxable_main,
812 P_DECIMAL_PART => ln_ss_er_w11_taxable_dec
813 );
814 END IF;
815
816 IF ln_qua_taxable_ss_wages <> 0 THEN --Bug 9595127
817 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
818 (
819 P_NUMBER => ln_qua_taxable_ss_wages,
820 P_DEC => ln_dec_digits,
821 P_INTEGER_PART => ln_qua_taxable_ss_wages_main,
822 P_DECIMAL_PART => ln_qua_taxable_ss_wages_dec
823 );
824 END IF;
825 #11831970 end */
826 IF ln_ss_er_taxable <> 0 THEN --Bug 9595127
827 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
828 (
829 P_NUMBER => ln_ss_er_taxable,
830 P_DEC => ln_dec_digits,
831 P_INTEGER_PART => ln_ss_er_taxable_main,
832 P_DECIMAL_PART => ln_ss_er_taxable_dec
833 );
834 END IF;
835
836 IF ln_medicare_er_taxable <> 0 THEN --Bug 9595127
837 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
838 (
839 P_NUMBER => ln_medicare_er_taxable,
840 P_DEC => ln_dec_digits,
841 P_INTEGER_PART => ln_medicare_er_taxable_main,
842 P_DECIMAL_PART => ln_medicare_er_taxable_dec
843 );
844 END IF;
845
846 /* Added for Bug#16211747 */
847 IF ln_med_er_tax_ovr_lmt <> 0 THEN
848 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
849 (
850 P_NUMBER => ln_med_er_tax_ovr_lmt,
851 P_DEC => ln_dec_digits,
852 P_INTEGER_PART => ln_med_er_tax_ovr_lmt_main,
853 P_DECIMAL_PART => ln_med_er_tax_ovr_lmt_dec
854 );
855 END IF;
856
857 /* #11831970 Start
858 IF ln_eic_advance <> 0 THEN --Bug 9595127
859 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
860 (
861 P_NUMBER => ln_eic_advance,
862 P_DEC => ln_dec_digits,
863 P_INTEGER_PART => ln_eic_advance_main,
864 P_DECIMAL_PART => ln_eic_advance_dec
865 );
866 END IF;
867 #11831970 End */
868 /* Bug 9357061: Splitting of Integer part and Decimal part end */
869
870
871
872 ln_w2_uncoll_med_ss := ln_w2_uncoll_med_gtl
873 + ln_w2_uncoll_med_tips
874 + ln_w2_uncoll_ss_gtl
875 + ln_w2_uncoll_ss_tax_tips;
876
877 /* Bug 9357061: Splitting of Integer part and Decimal part begin */
878
879 IF ln_w2_uncoll_med_ss <> 0 THEN --Bug 9595127
880 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
881 (
882 P_NUMBER => ln_w2_uncoll_med_ss,
883 P_DEC => ln_dec_digits,
884 P_INTEGER_PART => ln_w2_uncoll_med_ss_main,
885 P_DECIMAL_PART => ln_w2_uncoll_med_ss_dec
886 );
887 END IF;
888
889 /* Bug 9357061: Splitting of Integer part and Decimal part end */
890
891 ln_taxable_ss_wage := NVL(ln_ss_er_taxable,0) * 0.124; --0.124; #11831970 Modified rate to .104. Bug#16211747 Modified rate to 0.124
892 IF ln_taxable_ss_wage < 0 THEN
893 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
894 ' has negative B5A Total Taxable SS Wages. Please check.');
895 END IF;
896
897 /* Bug 9357061: Splitting of Integer part and Decimal part begin */
898
899 IF ln_taxable_ss_wage <> 0 THEN --Bug 9595127
900 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
901 (
902 P_NUMBER => ln_taxable_ss_wage,
903 P_DEC => ln_dec_digits,
904 P_INTEGER_PART => ln_taxable_ss_wage_main,
905 P_DECIMAL_PART => ln_taxable_ss_wage_dec
906 );
907 END IF;
908
909 /* Bug 9357061: Splitting of Integer part and Decimal part end */
910
911 ln_941_box5b := NVL(ln_ss_tips,0) * 0.124; --0.124; #11831970 Modified rate to .104 Bug#16211747. Bug#16211747 Modified rate to 0.124
912 IF ln_941_box5b < 0 THEN
913 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
914 ' has negative B5B Total Taxable SS Wages. Please check.');
915 END IF;
916
917 /* Bug 9357061: Splitting of Integer part and Decimal part begin */
918
919 IF ln_941_box5b <> 0 THEN --Bug 9595127
920 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
921 (
922 P_NUMBER => ln_941_box5b,
923 P_DEC => ln_dec_digits,
924 P_INTEGER_PART => ln_941_box5b_main,
925 P_DECIMAL_PART => ln_941_box5b_dec
926 );
927 END IF;
928
929 /* Bug 9357061: Splitting of Integer part and Decimal part end */
930
931 ln_taxable_medicare := NVL(ln_medicare_er_taxable,0) * 0.029;
932 IF ln_taxable_medicare < 0 THEN
933 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
934 ' has negative B5C Taxable Medicare Wages. Please check.');
935 END IF;
936
937 /* Bug 9357061: Splitting of Integer part and Decimal part begin */
938
939 IF ln_taxable_medicare <> 0 THEN --Bug 9595127
940 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
941 (
942 P_NUMBER => ln_taxable_medicare,
943 P_DEC => ln_dec_digits,
944 P_INTEGER_PART => ln_taxable_medicare_main,
945 P_DECIMAL_PART => ln_taxable_medicare_dec
946 );
947 END IF;
948
949 /* Added for Bug#16211747 - Starts */
950 ln_tax_med_er_ovr_lmt := NVL(ln_med_er_tax_ovr_lmt, 0) * 0.009;
951 IF ln_tax_med_er_ovr_lmt < 0 THEN
952 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
953 ' has negative B5D Taxable Medicare Wages. Please check.');
954 END IF;
955
956 IF ln_tax_med_er_ovr_lmt <> 0 THEN
957 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
958 (
959 P_NUMBER => ln_tax_med_er_ovr_lmt,
960 P_DEC => ln_dec_digits,
961 P_INTEGER_PART => ln_tax_med_er_ovr_lmt_main,
962 P_DECIMAL_PART => ln_tax_med_er_ovr_lmt_dec
963 );
964 END IF;
965 /* Added for Bug#16211747 - Ends */
966
967 /* Bug 9357061: Splitting of Integer part and Decimal part end */
968
969 ln_tot_ss_medicare_tax := NVL(ln_taxable_ss_wage,0) +
970 NVL(ln_taxable_medicare,0) +
971 NVL(ln_941_box5b,0) +
972 NVL(ln_tax_med_er_ovr_lmt, 0); /* Added for Bug#16211747 */
973 IF ln_tot_ss_medicare_tax < 0 THEN
974 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
975 ' has negative B5E Total SS Medicare Taxes. Please check.');
976 END IF;
977
978 /* Bug 9357061: Splitting of Integer part and Decimal part begin */
979
980 IF ln_tot_ss_medicare_tax <> 0 THEN --Bug 9595127
981 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
982 (
983 P_NUMBER => ln_tot_ss_medicare_tax,
984 P_DEC => ln_dec_digits,
985 P_INTEGER_PART => ln_tot_ss_medicare_tax_main,
986 P_DECIMAL_PART => ln_tot_ss_medicare_tax_dec
987 );
988 END IF;
989
990 /* Bug 9357061: Splitting of Integer part and Decimal part end */
991
992 /* #11831970 start
993 ln_tot_tax_bfr_adj := ln_fit_withheld + ln_tot_ss_medicare_tax - ln_qua_taxable_ss_wages;
994 #11831970 end*/
995
996 /* Bug 9357061: Splitting of Integer part and Decimal part end */
997 /* #11831970 start
998 IF ln_tot_tax_bfr_adj <> 0 THEN --Bug 9595127
999 ln_func_value := SPLIT_NUMBER_INTO_INT_DECIMAL
1000 (
1001 P_NUMBER => ln_tot_tax_bfr_adj,
1002 P_DEC => ln_dec_digits,
1003 P_INTEGER_PART => ln_tot_tax_bfr_adj_main,
1004 P_DECIMAL_PART => ln_tot_tax_bfr_adj_dec
1005 );
1006 END IF;
1007 #11831970 end*/
1008 /* Bug 9357061: Splitting of Integer part and Decimal part end */
1009
1010 -- Following is the code for writing XML data
1011 vXMLTable.DELETE;
1012 vCtr := 0;
1013 vXMLTable(vCtr).xmlstring := '<?xml version="1.0" ?>';
1014 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1015 || '<PAYUS941>';
1016 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1017 || ' '
1018 || '<LIST_G_TAX_UNIT_HEADER>';
1019 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1020 || ' '
1021 || '<G_TAX_UNIT_HEADER>';
1022 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1023 || ' '
1024 ||'<YEAR>'
1025 || p_year || '</YEAR>';
1026 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1027 || ' '
1028 ||'<YEAR1>'
1029 || p_year || '</YEAR1>';
1030 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1031 || ' '
1032 ||'<YEAR2>'
1033 || p_year || '</YEAR2>';
1034 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1035 || ' '
1036 ||'<EIN>'
1037 || l_gre_ein || '</EIN>';
1038 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring -- Bug 9357061: XML tags for the Split EIN Digits start.
1039 || ' '
1040 ||'<EIN_1>'
1041 || l_gre_EIN_1 || '</EIN_1>';
1042 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1043 || ' '
1044 ||'<EIN_2>'
1045 || l_gre_EIN_2 || '</EIN_2>';
1046 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1047 || ' '
1048 ||'<EIN_3>'
1049 || l_gre_EIN_3 || '</EIN_3>';
1050 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1051 || ' '
1052 ||'<EIN_4>'
1053 || l_gre_EIN_4 || '</EIN_4>';
1054 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1055 || ' '
1056 ||'<EIN_5>'
1057 || l_gre_EIN_5 || '</EIN_5>';
1058 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1059 || ' '
1060 ||'<EIN_6>'
1061 || l_gre_EIN_6 || '</EIN_6>';
1062 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1063 || ' '
1064 ||'<EIN_7>'
1065 || l_gre_EIN_7 || '</EIN_7>';
1066 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1067 || ' '
1068 ||'<EIN_8>'
1069 || l_gre_EIN_8 || '</EIN_8>';
1070 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1071 || ' '
1072 ||'<EIN_9>'
1073 || l_gre_EIN_9 || '</EIN_9>'; -- Bug 9357061: XML tags for the Split EIN Digits end.
1074 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1075 || ' '
1076 ||'<gre_name>'
1077 ||'<![CDATA[ '|| l_gre_name || ' ]]>'||'</gre_name>';
1078 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1079 || ' '
1080 ||'<ADDRESS1>'
1081 ||'<![CDATA[ '|| l_ADDRESS || ' ]]>'||'</ADDRESS1>';
1082 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1083 || ' '
1084 ||'<CITY>'
1085 || l_CITY || '</CITY>';
1086 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1087 || ' '
1088 ||'<STATE>'
1089 || l_STATE || '</STATE>';
1090 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1091 || ' '
1092 ||'<ZIP>'
1093 || l_ZIP || '</ZIP>';
1094 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1095 || ' '
1096 ||'<QTR>'
1097 || p_qtr || '</QTR>';
1098 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1099 || ' '
1100 ||'<EMP_COUNT>'
1101 || ln_EMP_COUNT || '</EMP_COUNT>';
1102 /* #11831970 start
1103 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1104 || ' '
1105 ||'<QUA_EMP_COUNT_FIRST_PAID>'
1106 || ln_qua_emp_first_paid_count || '</QUA_EMP_COUNT_FIRST_PAID>';
1107 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1108 || ' '
1109 ||'<QUA_EMP_PAID_COUNT>'
1110 || ln_qua_emp_paid_count || '</QUA_EMP_PAID_COUNT>';
1111 #11831970 end */
1112 /* IF ln_941_box2 = 0 THEN ln_941_box2 := NULL; END IF;
1113 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1114 || ' '
1115 ||'<B2_TOTAL_WAGES>'
1116 || ln_941_box2
1117 || '</B2_TOTAL_WAGES>';*/
1118 IF ln_total_wages_main IS NULL AND ln_total_wages_dec IS NOT NULL THEN ln_total_wages_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1119 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1120 || ' '
1121 ||'<B2_TOTAL_WAGES>'
1122 || ln_total_wages_main
1123 || '</B2_TOTAL_WAGES>';
1124 IF ln_total_wages_main IS NOT NULL AND ln_total_wages_dec IS NULL THEN ln_total_wages_dec := '00';END IF;
1125 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1126 || ' '
1127 ||'<B2_TOTAL_WAGES_DEC>'
1128 || ln_total_wages_dec
1129 || '</B2_TOTAL_WAGES_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1130 IF ln_supp_earn_fit = 0 THEN ln_supp_earn_fit := NULL; END IF;
1131 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1132 || ' '
1133 ||'<FWT_SUPP_EARN_FIT>'
1134 || ln_supp_earn_fit
1135 || '</FWT_SUPP_EARN_FIT>';
1136 IF ln_supp_earn_nwfit = 0 THEN ln_supp_earn_nwfit := NULL; END IF;
1137 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1138 || ' '
1139 ||'<FWT_SUPP_EARN_NWFIT>'
1140 || ln_supp_earn_nwfit
1141 || '</FWT_SUPP_EARN_NWFIT>';
1142 IF ln_pretax_dedn_fit = 0 THEN ln_pretax_dedn_fit := NULL; END IF;
1143 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1144 || ' '
1145 ||'<PRE_TAX_DED_FOR_FIT>'
1146 || ln_pretax_dedn_fit
1147 || '</PRE_TAX_DED_FOR_FIT>';
1148 IF ln_regular_earnings = 0 THEN ln_regular_earnings := NULL; END IF;
1149 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1150 || ' '
1151 ||'<FWT_REGULAR_EARNINGS>'
1152 || ln_regular_earnings
1153 || '</FWT_REGULAR_EARNINGS>';
1154 /* IF ln_fit_withheld = 0 THEN ln_fit_withheld := NULL; END IF;
1155 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1156 || ' '
1157 ||'<FWT_WITHHELD>'
1158 || ln_fit_withheld
1159 || '</FWT_WITHHELD>';*/
1160 IF ln_fit_withheld_main IS NULL AND ln_fit_withheld_dec IS NOT NULL THEN ln_fit_withheld_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1161 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1162 || ' '
1163 ||'<FWT_WITHHELD>'
1164 || ln_fit_withheld_main
1165 || '</FWT_WITHHELD>';
1166 IF ln_fit_withheld_main IS NOT NULL AND ln_fit_withheld_dec IS NULL THEN ln_fit_withheld_dec := '00'; END IF;
1167 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1168 || ' '
1169 ||'<FWT_WITHHELD_DEC>'
1170 || ln_fit_withheld_dec
1171 || '</FWT_WITHHELD_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1172 /* IF ln_ss_er_taxable = 0 THEN ln_ss_er_taxable := NULL; END IF;
1173 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1174 || ' '
1175 ||'<SS_ER_TAXABLE>'
1176 || ln_ss_er_taxable
1177 || '</SS_ER_TAXABLE>';*/
1178 IF ln_ss_er_taxable_main IS NULL AND ln_ss_er_taxable_dec IS NOT NULL THEN ln_ss_er_taxable_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1179 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1180 || ' '
1181 ||'<SS_ER_TAXABLE>'
1182 || ln_ss_er_taxable_main
1183 || '</SS_ER_TAXABLE>';
1184 IF ln_ss_er_taxable_main IS NOT NULL AND ln_ss_er_taxable_dec IS NULL THEN ln_ss_er_taxable_dec := '00'; END IF;
1185 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1186 || ' '
1187 ||'<SS_ER_TAXABLE_DEC>'
1188 || ln_ss_er_taxable_dec
1189 || '</SS_ER_TAXABLE_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1190 /* IF ln_941_box5a = 0 THEN ln_941_box5a := NULL; END IF;
1191 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1192 || ' '
1193 ||'<B5A_TAXABLE_SS_WAGES>'
1194 || ln_941_box5a
1195 || '</B5A_TAXABLE_SS_WAGES>';*/
1196 IF ln_taxable_ss_wage_main IS NULL AND ln_taxable_ss_wage_dec IS NOT NULL THEN ln_taxable_ss_wage_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1197 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1198 || ' '
1199 ||'<B5A_TAXABLE_SS_WAGES>'
1200 || ln_taxable_ss_wage_main
1201 || '</B5A_TAXABLE_SS_WAGES>';
1202 IF ln_taxable_ss_wage_main IS NOT NULL AND ln_taxable_ss_wage_dec IS NULL THEN ln_taxable_ss_wage_dec := '00'; END IF;
1203 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1204 || ' '
1205 ||'<B5A_TAXABLE_SS_WAGES_DEC>'
1206 || ln_taxable_ss_wage_dec
1207 || '</B5A_TAXABLE_SS_WAGES_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1208 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1209 || ' '
1210 ||'<TAXABLE_SS_TIPS />';
1211 /* IF ln_ss_tips = 0 THEN ln_ss_tips := NULL; END IF;
1212 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1213 || ' '
1214 ||'<SS_ER_TAXABLE_TIPS>'
1215 || ln_ss_tips
1216 || '</SS_ER_TAXABLE_TIPS>';*/
1217 IF ln_ss_tips_main IS NULL AND ln_ss_tips_dec IS NOT NULL THEN ln_ss_tips_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1218 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1219 || ' '
1220 ||'<SS_ER_TAXABLE_TIPS>'
1221 || ln_ss_tips_main
1222 || '</SS_ER_TAXABLE_TIPS>';
1223 IF ln_ss_tips_main IS NOT NULL AND ln_ss_tips_dec IS NULL THEN ln_ss_tips_dec := '00'; END IF;
1224 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1225 || ' '
1226 ||'<SS_ER_TAXABLE_TIPS_DEC>'
1227 || ln_ss_tips_dec
1228 || '</SS_ER_TAXABLE_TIPS_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1229 /* IF ln_941_box5b = 0 THEN ln_941_box5b := NULL; END IF;
1230 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1231 || ' '
1232 ||'<SS_ER_TAXABLE_TIPS_F>'
1233 || ln_941_box5b
1234 || '</SS_ER_TAXABLE_TIPS_F>';*/
1235 IF ln_941_box5b_main IS NULL AND ln_941_box5b_dec IS NOT NULL THEN ln_941_box5b_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1236 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1237 || ' '
1238 ||'<SS_ER_TAXABLE_TIPS_F>'
1239 || ln_941_box5b_main
1240 || '</SS_ER_TAXABLE_TIPS_F>';
1241 IF ln_941_box5b_main IS NOT NULL AND ln_941_box5b_dec IS NULL THEN ln_941_box5b_dec := '00'; END IF;
1242 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1243 || ' '
1244 ||'<SS_ER_TAXABLE_TIPS_F_DEC>'
1245 || ln_941_box5b_dec
1246 || '</SS_ER_TAXABLE_TIPS_F_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1247 /* IF ln_medicare_er_taxable = 0 THEN ln_medicare_er_taxable := NULL; END IF;
1248 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1249 || ' '
1250 ||'<MEDICARE_ER_TAXABLE>'
1251 || ln_medicare_er_taxable
1252 || '</MEDICARE_ER_TAXABLE>';*/
1253 IF ln_medicare_er_taxable_main IS NULL AND ln_medicare_er_taxable_dec IS NOT NULL THEN ln_medicare_er_taxable_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1254 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1255 || ' '
1256 ||'<MEDICARE_ER_TAXABLE>'
1257 || ln_medicare_er_taxable_main
1258 || '</MEDICARE_ER_TAXABLE>';
1259 IF ln_medicare_er_taxable_main IS NOT NULL AND ln_medicare_er_taxable_dec IS NULL THEN ln_medicare_er_taxable_dec := '00'; END IF;
1260 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1261 || ' '
1262 ||'<MEDICARE_ER_TAXABLE_DEC>'
1263 || ln_medicare_er_taxable_dec
1264 || '</MEDICARE_ER_TAXABLE_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1265 /* IF ln_941_box5c = 0 THEN ln_941_box5c := NULL; END IF;
1266 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1267 || ' '
1268 ||'<B5C_TAXABLE_MEDICARE_WAGES>'
1269 || ln_941_box5c
1270 || '</B5C_TAXABLE_MEDICARE_WAGES>';*/
1271 IF ln_taxable_medicare_main IS NULL AND ln_taxable_medicare_dec IS NOT NULL THEN ln_taxable_medicare_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1272 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1273 || ' '
1274 ||'<B5C_TAXABLE_MEDICARE_WAGES>'
1275 || ln_taxable_medicare_main
1276 || '</B5C_TAXABLE_MEDICARE_WAGES>';
1277 IF ln_taxable_medicare_main IS NOT NULL AND ln_taxable_medicare_dec IS NULL THEN ln_taxable_medicare_dec := '00'; END IF;
1278 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1279 || ' '
1280 ||'<B5C_TAXABLE_MEDICARE_WAGES_DEC>'
1281 || ln_taxable_medicare_dec
1282 || '</B5C_TAXABLE_MEDICARE_WAGES_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1283
1284 /* Added for Bug#16211747 - Starts */
1285 IF ln_med_er_tax_ovr_lmt_main IS NULL AND ln_med_er_tax_ovr_lmt_dec IS NOT NULL THEN
1286 ln_med_er_tax_ovr_lmt_main := '0';
1287 END IF;
1288
1289 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1290 || ' '
1291 ||'<MEDICARE_ER_TAXABLE_OVER_LIMIT>'
1292 || ln_med_er_tax_ovr_lmt_main
1293 || '</MEDICARE_ER_TAXABLE_OVER_LIMIT>';
1294
1295 IF ln_med_er_tax_ovr_lmt_main IS NOT NULL AND ln_med_er_tax_ovr_lmt_dec IS NULL THEN
1296 ln_med_er_tax_ovr_lmt_dec := '00';
1297 END IF;
1298
1299 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1300 || ' '
1301 ||'<MEDICARE_ER_TAXABLE_OVER_LIMIT_DEC>'
1302 || ln_med_er_tax_ovr_lmt_dec
1303 || '</MEDICARE_ER_TAXABLE_OVER_LIMIT_DEC>';
1304
1305 IF ln_tax_med_er_ovr_lmt_main IS NULL AND ln_tax_med_er_ovr_lmt_dec IS NOT NULL THEN
1306 ln_tax_med_er_ovr_lmt_main := '0';
1307 END IF;
1308
1309 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1310 || ' '
1311 ||'<B5D_TAXABLE_MEDICARE_WAGES_OVER_LIMIT>'
1312 || ln_tax_med_er_ovr_lmt_main
1313 || '</B5D_TAXABLE_MEDICARE_WAGES_OVER_LIMIT>';
1314
1315 IF ln_tax_med_er_ovr_lmt_main IS NOT NULL AND ln_tax_med_er_ovr_lmt_dec IS NULL THEN
1316 ln_tax_med_er_ovr_lmt_dec := '00';
1317 END IF;
1318
1319 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1320 || ' '
1321 ||'<B5D_TAXABLE_MEDICARE_WAGES_OVER_LIMIT_DEC>'
1322 || ln_tax_med_er_ovr_lmt_dec
1323 || '</B5D_TAXABLE_MEDICARE_WAGES_OVER_LIMIT_DEC>';
1324
1325 /* Added for Bug#16211747 - Ends */
1326
1327 /* IF ln_941_box5d = 0 THEN ln_941_box5d := NULL; END IF;
1328 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1329 || ' '
1330 ||'<B5D_TOTAL_SS_MEDICARE_TAXES>'
1331 || ln_941_box5d
1332 || '</B5D_TOTAL_SS_MEDICARE_TAXES>';*/
1333
1334 /* Modified for Bug#16211747 */
1335 IF ln_tot_ss_medicare_tax_main IS NULL AND ln_tot_ss_medicare_tax_dec IS NOT NULL THEN ln_tot_ss_medicare_tax_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1336 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1337 || ' '
1338 ||'<B5E_TOTAL_SS_MEDICARE_TAXES>'
1339 || ln_tot_ss_medicare_tax_main
1340 || '</B5E_TOTAL_SS_MEDICARE_TAXES>';
1341 IF ln_tot_ss_medicare_tax_main IS NOT NULL AND ln_tot_ss_medicare_tax_dec IS NULL THEN ln_tot_ss_medicare_tax_dec := '00'; END IF;
1342 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1343 || ' '
1344 ||'<B5E_TOTAL_SS_MEDICARE_TAXES_DEC>'
1345 || ln_tot_ss_medicare_tax_dec
1346 || '</B5E_TOTAL_SS_MEDICARE_TAXES_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1347
1348 /* #11831970 start
1349 Commenting this code as this is not required to calculate this
1350 value to for 941 report
1351 IF ln_ss_er_w11_taxable_main IS NULL AND ln_ss_er_w11_taxable_dec IS NOT NULL THEN ln_ss_er_w11_taxable_main := '0'; END IF;
1352 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1353 || ' '
1354 ||'<B6C_EXEMPT_WAGES_QUAL_EMP>'
1355 || ln_ss_er_w11_taxable_main
1356 || '</B6C_EXEMPT_WAGES_QUAL_EMP>';
1357 IF ln_ss_er_w11_taxable_main IS NOT NULL AND ln_ss_er_w11_taxable_dec IS NULL THEN ln_ss_er_w11_taxable_dec := '00'; END IF;
1358 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1359 || ' '
1360 ||'<B6C_EXEMPT_WAGES_QUAL_EMP_DEC>'
1361 || ln_ss_er_w11_taxable_dec
1362 || '</B6C_EXEMPT_WAGES_QUAL_EMP_DEC>';
1363 IF ln_qua_taxable_ss_wages_main IS NULL AND ln_qua_taxable_ss_wages_dec IS NOT NULL THEN ln_qua_taxable_ss_wages_main := '0'; END IF;
1364 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1365 || ' '
1366 ||'<B6D_EXEMPT_WAGES_QUAL_EMP>'
1367 || ln_qua_taxable_ss_wages_main
1368 || '</B6D_EXEMPT_WAGES_QUAL_EMP>';
1369 IF ln_qua_taxable_ss_wages_main IS NOT NULL AND ln_qua_taxable_ss_wages_dec IS NULL THEN ln_qua_taxable_ss_wages_dec := '00'; END IF;
1370 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1371 || ' '
1372 ||'<B6D_EXEMPT_WAGES_QUAL_EMP_DEC>'
1373 || ln_qua_taxable_ss_wages_dec
1374 || '</B6D_EXEMPT_WAGES_QUAL_EMP_DEC>'; #11831970 end */
1375 /* IF ln_941_box6 = 0 THEN ln_941_box6 := NULL; END IF;
1376 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1377 || ' '
1378 ||'<B6_TOTAL_B3_B5D>'
1379 || ln_941_box6
1380 || '</B6_TOTAL_B3_B5D>';*/
1381 /* #11831970 start
1382 IF ln_tot_tax_bfr_adj_main IS NULL AND ln_tot_tax_bfr_adj_dec IS NOT NULL THEN ln_tot_tax_bfr_adj_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1383 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1384 || ' '
1385 ||'<B6_TOTAL_B3_B5D>'
1386 || ln_tot_tax_bfr_adj_main
1387 || '</B6_TOTAL_B3_B5D>';
1388 IF ln_tot_tax_bfr_adj_main IS NOT NULL AND ln_tot_tax_bfr_adj_dec IS NULL THEN ln_tot_tax_bfr_adj_dec := '00'; END IF;
1389 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1390 || ' '
1391 ||'<B6_TOTAL_B3_B5D_DEC>'
1392 || ln_tot_tax_bfr_adj_dec
1393 || '</B6_TOTAL_B3_B5D_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1394 #11831970 end */
1395 /* IF ln_w2_uncoll_med_ss = 0 THEN ln_w2_uncoll_med_ss := NULL; END IF;
1396 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1397 || ' '
1398 ||'<W2_UNCOLL_MED_SS>'
1399 || ln_w2_uncoll_med_ss
1400 || '</W2_UNCOLL_MED_SS>';*/
1401
1402 IF ln_w2_uncoll_med_ss_main IS NULL AND ln_w2_uncoll_med_ss_dec IS NOT NULL THEN ln_w2_uncoll_med_ss_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1403 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1404 || ' '
1405 ||'<W2_UNCOLL_MED_SS>'
1406 || ln_w2_uncoll_med_ss_main
1407 || '</W2_UNCOLL_MED_SS>';
1408 IF ln_w2_uncoll_med_ss_main IS NOT NULL AND ln_w2_uncoll_med_ss_dec IS NULL THEN ln_w2_uncoll_med_ss_dec := '00'; END IF;
1409 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1410 || ' '
1411 ||'<W2_UNCOLL_MED_SS_DEC>'
1412 || ln_w2_uncoll_med_ss_dec
1413 || '</W2_UNCOLL_MED_SS_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1414 /* IF ln_eic_advance =0 THEN ln_eic_advance := NULL; END IF;
1415 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1416 || ' '
1417 ||'<EIC_ADVANCE>'
1418 || ln_eic_advance
1419 || '</EIC_ADVANCE>';*/
1420 /* #11831970 Start
1421 This is commented as this is no longer used in 941 report
1422
1423 IF ln_eic_advance_main IS NULL AND ln_eic_advance_dec IS NOT NULL THEN ln_eic_advance_main := '0'; END IF; -- Bug 9357061: XML tags for the integer and decimal, start.
1424 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1425 || ' '
1426 ||'<EIC_ADVANCE>'
1427 || ln_eic_advance_main
1428 || '</EIC_ADVANCE>';
1429 IF ln_eic_advance_main IS NOT NULL AND ln_eic_advance_dec IS NULL THEN ln_eic_advance_dec := '00'; END IF;
1430 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1431 || ' '
1432 ||'<EIC_ADVANCE_DEC>'
1433 || ln_eic_advance_dec
1434 || '</EIC_ADVANCE_DEC>'; -- Bug 9357061: XML tags for the integer and decimal, end.
1435 #11831970 end*/
1436 /* vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1437 || ' '
1438 ||'<STATE_ABBR_1>'
1439 || lv_STATE_ABBR_1
1440 || '</STATE_ABBR_1>';
1441 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1442 || ' '
1443 ||'<STATE_ABBR_2>'
1444 || lv_STATE_ABBR_2
1445 || '</STATE_ABBR_2>'; */
1446 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1447 || ' '
1448 ||'</G_TAX_UNIT_HEADER>';
1449 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1450 || ' '
1451 ||'</LIST_G_TAX_UNIT_HEADER>';
1452 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1453 || '<C_TRACE />';
1454 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1455 || '</PAYUS941>';
1456 END get_941_balances;
1457 -- covers 9. Advance earned income credit (CP_EIC_ADVANCE)
1458 -- covers 5d. Total SS Taxes (C_B5D_TOTAL_SS_MEDICARE_TAXES)
1459 -- covers 5c.(ii) Taxable Medicare wages (C_B5C_TAXABLE_MEDICARE_WAGES)
1460 -- covers 5c.(i) Taxable Medicare wages (CP_MEDICARE_ER_TAXABLE)
1461 -- covers 5b.(ii) Taxable SS Tips (CF_SS_ER_TAXABLE_TIPS)
1462 -- covers 5b.(i) Taxable SS Tips (CP_SS_ER_TAXABLE_TIPS)
1463 -- covers 5a.(ii) Taxable SS Wages (C_B5A_TAXABLE_SS_WAGES)
1464 -- covers 5a.(i) Taxable SS Wages (CP_SS_ER_TAXABLE)
1465 -- covers 3. Total IT withheld from Wages, Tips and other compensation
1466 -- (CP_FWT_WITHHELD)
1467 -- covers 2. Wages, tips and other compensation (C_B2_TOTAL_WAGES)
1468 -- covers 1. No. of employees (l_C_emp_count)
1469 --******** Generate Federal Tax Balances (end)
1470 --******** Write to CLOB (start)
1471
1472
1473 PROCEDURE WritetoCLOB (p_XML OUT NOCOPY CLOB)
1474 IS
1475 l_xfdf_string CLOB;
1476 BEGIN
1477 hr_utility.set_location('Entered Procedure Write to clob ',100);
1478 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1479 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1480 -- if vXMLTable.count > 0 then
1481 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
1482 LOOP
1483 dbms_lob.writeAppend(l_xfdf_string,
1484 LENGTH(vXMLTable(ctr_table).xmlstring),
1485 vXMLTable(ctr_table).xmlstring );
1486 END LOOP;
1487 p_XML := l_xfdf_string;
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1491 HR_UTILITY.RAISE_ERROR;
1492 END WritetoCLOB;
1493
1494
1495 PROCEDURE gen_941_report(p_business_group_id IN NUMBER,
1496 p_tax_unit_id IN NUMBER,
1497 p_year IN VARCHAR2,
1498 p_qtr IN VARCHAR2,
1499 p_template_name IN VARCHAR2,
1500 p_xml OUT NOCOPY Clob)
1501 IS
1502 BEGIN
1503 set_session_variables_contexts(
1504 p_business_group_id,
1505 p_tax_unit_id,
1506 p_year,
1507 p_qtr);
1508 get_941_balances(
1509 p_business_group_id,
1510 p_tax_unit_id,
1511 p_year,
1512 p_qtr);
1513 WritetoCLOB (p_XML);
1514
1515 END gen_941_report;
1516
1517 /*****************************************************************************
1518 Name : pay_us_941_report_wrapper
1519 Purpose : calls procedure gen_941_report, generates xml output and submits
1520 xml publisher report to merge the xml output with template.
1521 *****************************************************************************/
1522 PROCEDURE pay_us_941_report_wrapper
1523 ( errbuf OUT NOCOPY VARCHAR2,
1524 retcode OUT NOCOPY VARCHAR2,
1525 p_business_group_id IN NUMBER,
1526 p_tax_unit_id IN VARCHAR2,
1527 p_year IN NUMBER,
1528 p_qtr IN VARCHAR2,
1529 p_valid_template_list IN VARCHAR2,
1530 p_appl_short_name IN VARCHAR2,
1531 p_template_name IN VARCHAR2,
1532 p_effective_date IN VARCHAR2
1533 )
1534 IS
1535 --bug 8267963
1536 cursor csr_release is
1537 select to_number(substr(PRODUCT_VERSION,1,2))
1538 from FND_PRODUCT_INSTALLATIONS
1539 where APPLICATION_ID = 800;
1540
1541 l_release NUMBER;
1542 --bug 8267963
1543 l_xml CLOB;
1544 l_req_id NUMBER;
1545 l_req_id2 NUMBER;
1546 l_program VARCHAR2(100);
1547 l_errbuf VARCHAR2(240);
1548 l_procedure_name VARCHAR2(100);
1549
1550 BEGIN
1551
1552 l_procedure_name := '.pay_us_941_report_wrapper';
1553 hr_utility.set_location('Entering '|| g_package || l_procedure_name, 10);
1554
1555 --Bug 8267963
1556 OPEN csr_release;
1557 FETCH csr_release INTO l_release;
1558 CLOSE csr_release;
1559 -- Bug 8267963
1560
1561 /* Generate the xml output */
1562 gen_941_report(p_business_group_id => p_business_group_id,
1563 p_tax_unit_id => p_tax_unit_id,
1564 p_year => p_year,
1565 p_qtr => p_qtr,
1566 p_template_name => 'DUMMY',
1567 p_xml => l_xml);
1568
1569 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
1570 LOOP
1571 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,vXMLTable(ctr_table).xmlstring);
1572 END LOOP;
1573
1574 /* Submit XML Report Publisher request for the generated xml output*/
1575 l_req_id := fnd_global.conc_request_id;
1576
1577 IF l_req_id > 0 THEN
1578
1579 l_program := 'XDOREPPB';
1580
1581 if(l_release = 12) then
1582
1583 l_req_id2 := fnd_request.submit_request(
1584 application => 'XDO',
1585 program => l_program,
1586 argument1 => 'N',
1587 argument2 => l_req_id,
1588 argument3 => 801,
1589 argument4 => p_template_name,
1590 argument5 => 'en-US',
1591 argument6 => 'N',
1592 argument7 => 'PDF',
1593 argument8 => 'PDF' );
1594
1595 hr_utility.trace ('Leaving 12'|| l_procedure_name);
1596
1597 else
1598
1599 l_req_id2 := fnd_request.submit_request(
1600 application => 'XDO',
1601 program => l_program,
1602 argument1 => l_req_id,
1603 argument2 => 801,
1604 argument3 => p_template_name,
1605 argument4 => 'en-US',
1606 argument5 => 'N',
1607 argument6 => 'PDF',
1608 argument7 => 'PDF' );
1609
1610
1611
1612 hr_utility.trace ('Leaving 11i'|| l_procedure_name);
1613
1614 end if;
1615
1616 ELSE
1617
1618 fnd_message.retrieve(l_errbuf);
1619
1620 END IF;
1621
1622 IF l_req_id2 > 0 THEN
1623
1624 Commit;
1625
1626 ELSE
1627
1628 fnd_message.retrieve(l_errbuf);
1629
1630 END IF;
1631
1632 EXCEPTION
1633 WHEN others THEN
1634 hr_utility.raise_error;
1635
1636 hr_utility.set_location('Leaving '|| g_package || l_procedure_name, 20);
1637
1638 End pay_us_941_report_wrapper ;
1639
1640 END pay_us_941_report;
1641
1642
1643