[Home] [Help]
PACKAGE BODY: APPS.PAY_US_941_REPORT
Source
1 PACKAGE BODY PAY_US_941_REPORT AS
2 /* $Header: payus941report.pkb 120.7.12000000.1 2007/01/17 14:39:58 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_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 ************************************************************************/
45 g_CP_STATUS VARCHAR2(20);
46 g_c_qtr_end_date DATE;
47 g_package VARCHAR2(100) := 'pay_us_941_report';
48
49
50 /* Initialization: Setting session variables etc. */
51 PROCEDURE set_session_variables_contexts(
52 p_business_group_id IN NUMBER,
53 p_tax_unit_id IN NUMBER,
54 p_year IN VARCHAR2,
55 p_qtr IN VARCHAR2)
56 IS
57 ld_quarter_start_date date;
58 ld_quarter_end_date date;
59 BEGIN
60
61 ld_quarter_start_date := TRUNC(TO_DATE(p_qtr||'-'||p_year,'DD-MM-YYYY'),'Q');
62 ld_quarter_end_date := TO_DATE(p_qtr||'-'||p_year,'DD-MM-YYYY');
63 -- Check for the validity of all the balances used by the report
64 g_CP_STATUS := pay_us_payroll_utils.check_balance_status(
65 p_start_date => ld_quarter_start_date,
66 p_business_group_id => p_business_group_id,
67 p_attribute_name => '941_FED',
68 p_legislation_code => 'US');
69
70 /* If all the balances used by the report are valid then set session
71 variables and contexts */
72 IF g_CP_STATUS = 'Y' THEN
73 pay_us_balance_view_pkg.set_session_var('GROUP_RB_REPORT','TRUE');
74 pay_us_balance_view_pkg.set_session_var('REPORT_TYPE','W2');
75 pay_us_balance_view_pkg.set_session_var('GROUP_RB_SDATE',
76 ld_quarter_start_date);
77 pay_us_balance_view_pkg.set_session_var('GROUP_RB_EDATE',
78 ld_quarter_end_date);
79 pay_balance_pkg.set_context(
80 'DATE_EARNED',
81 fnd_date.date_to_canonical(ld_quarter_end_date));
82 pay_balance_pkg.set_context(
83 'BALANCE_DATE',
84 fnd_date.date_to_canonical(ld_quarter_end_date));
85 END IF;
86
87 EXCEPTION WHEN no_data_found THEN
88 RAISE;
89 END set_session_variables_contexts;
90
91
92
93 PROCEDURE get_941_balances(p_business_group_id IN NUMBER,
94 p_tax_unit_id IN NUMBER,
95 p_year IN VARCHAR2,
96 p_qtr IN VARCHAR2)
97 IS
98
99 CURSOR c_gre_info(cp_tax_unit_id NUMBER) IS
100 SELECT hou.name, -- GRE Name
101 hoi_ein.org_information1, -- EIN
102 hrl.address_line_1,
103 hrl.address_line_2,
104 hrl.address_line_3,
105 hrl.town_or_city,
106 hrl.region_2,
107 hrl.postal_code
108 FROM hr_organization_units hou,
109 hr_organization_information hoi_bg,
110 hr_organization_information hoi_ein ,
111 hr_locations hrl
112 WHERE hou.organization_id = cp_tax_unit_id
113 AND hoi_bg.organization_id = hou.organization_id
114 and hoi_bg.org_information_context = 'CLASS'
115 AND hoi_bg.org_information1 = 'HR_LEGAL'
116 AND hoi_ein.organization_id(+) = hou.organization_id
117 AND nvl(hoi_ein.org_information_context(+),'Employer Identification') = 'Employer Identification'
118 AND hrl.location_id = hou.location_id;
119
120 l_gre_name VARCHAR2(240);
121 l_gre_EIN VARCHAR2(150);
122 l_ADDRESS VARCHAR2(720);
123 l_ADDRESS_LINE_1 VARCHAR2(240);
124 l_ADDRESS_LINE_2 VARCHAR2(240);
125 l_ADDRESS_LINE_3 VARCHAR2(240);
126 l_CITY VARCHAR2(30);
127 l_STATE VARCHAR2(120);
128 l_ZIP VARCHAR2(30);
129
130 --******** Employee Count (declarations start)
131 CURSOR c_count_asg_processed(cp_tax_unit_id IN number,
132 cp_qtr IN VARCHAR2,
133 cp_year IN VARCHAR2) IS
134 SELECT /*+ LEADING(ptp) -- For Bug 4769835
135 INDEX (ptp PER_TIME_PERIODS_N50)
136 USE_NL(ptp ppa)
137 */
138 COUNT(DISTINCT paf.person_id)
139 FROM per_time_periods ptp,
140 per_assignments_f paf,
141 per_assignments_f paf1,
142 pay_assignment_actions paa, pay_payroll_actions ppa
143 WHERE ptp.end_date >= TO_DATE('01'||TO_CHAR
144 (TRUNC(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'), 'Q'),'MM') ||
145 TO_CHAR(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'YYYY'),'DD-MM-YYYY')
146 AND ptp.start_date <= TO_DATE('12'||TO_CHAR
147 (to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'MM') ||
148 TO_CHAR(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'YYYY'),'DD-MM-YYYY')
149 AND ppa.effective_date >= ptp.start_date -- For Bug 4769835
150 AND ppa.effective_date <= ptp.end_date -- For Bug 4769835
151 AND ppa.payroll_id = ptp.payroll_id -- For Bug 4769835
152 AND ppa.time_period_id = ptp.time_period_id
153 AND ppa.action_type IN ('R', 'Q')
154 AND ppa.payroll_action_id = paa.payroll_action_id
155 AND paf1.assignment_id = paa.assignment_id
156 AND paf.person_id = paf1.person_id
157 AND paa.run_type_id IS NULL
158 AND paa.tax_unit_id = cp_tax_unit_id
159 AND paa.action_status = 'C';
160
161 ln_emp_count NUMBER;
162 --******** Employee Count (declarations end)
163
164 --******** State Abbreviation (declarations start)
165 /* CURSOR c_state_abbr(cp_tax_unit_id IN number) IS
166 SELECT ORG_INFORMATION1
167 FROM HR_ORGANIZATION_INFORMATION_V
168 WHERE org_information_context = 'State Tax Rules'
169 AND ORGANIZATION_ID = cp_tax_unit_id;
170 ln_count_state NUMBER;
171 lv_STATE_ABBR_1 VARCHAR2(1);
172 lv_STATE_ABBR_2 VARCHAR2(1);
173 lv_STATE_ABBR VARCHAR2(150);*/
174 --******** State Abbreviation (declarations end)
175
176 --******** Generate Federal Tax Balances (declarations start)
177 -- Get details for Total Wages
178 -- Cursor to fetch the balance values making use of the BRA
179 -- The cursor uses the view pay_us_federal_tax_bal_gre_v which
180 -- gets the balance information from the table pay_balance_sets.
181 CURSOR c_bal_values (cp_tax_unit_id IN NUMBER) IS
182 SELECT d_tax_otd_value,
183 d_wage_otd_value,
184 d_tax_type
185 FROM pay_us_federal_tax_bal_gre_v
186 WHERE d_balance_set_name = '941_QTD'
187 AND d_tax_unit_id = cp_tax_unit_id;
188
189 -- Local variables to hold the balance values
190 ln_regular_earnings NUMBER;
191 ln_fit_withheld NUMBER;
192 ln_medicare_er_taxable NUMBER;
193 ln_ss_er_taxable NUMBER;
194 ln_eic_advance NUMBER;
195 ln_pretax_dedn NUMBER;
196 ln_fit_non_w2_pretax_dedn NUMBER;
197 ln_ss_tips NUMBER;
198 ln_w2_uncoll_med_gtl NUMBER;
199 ln_w2_uncoll_med_tips NUMBER;
200 ln_w2_uncoll_ss_gtl NUMBER;
201 ln_w2_uncoll_ss_tax_tips NUMBER;
202 ln_w2_uncoll_med_ss NUMBER;
203 ln_supp_earn_nwfit NUMBER;
204 ln_supp_earn_fit NUMBER;
205 ln_pretax_dedn_fit NUMBER;
206 ln_dummy NUMBER;
207
208 lv_tax_type VARCHAR2(50);
209 ln_tax NUMBER;
210 ln_wage NUMBER;
211
212 ln_941_box2 NUMBER;
213 ln_941_box5a NUMBER;
214 ln_941_box5b NUMBER;
215 ln_941_box5c NUMBER;
216 ln_941_box5d NUMBER;
217 ln_941_box6 NUMBER;
218
219 vCtr NUMBER;
220
221 BEGIN
222 vCtr := 0;
223 -- GRE Info
224 OPEN c_gre_info(p_tax_unit_id);
225 FETCH c_gre_info
226 INTO l_gre_name, l_gre_ein, l_ADDRESS_LINE_1, l_ADDRESS_LINE_2,
227 l_ADDRESS_LINE_3, l_CITY, l_STATE, l_ZIP;
228 CLOSE c_gre_info;
229
230
231 l_ADDRESS := l_ADDRESS_LINE_1 || ' '
232 || l_ADDRESS_LINE_2 || ' '
233 || l_ADDRESS_LINE_3;
234 IF LENGTH(l_ADDRESS) > 80 THEN
235 l_ADDRESS := l_ADDRESS_LINE_1 || ' ' || l_ADDRESS_LINE_2;
236 IF LENGTH (l_ADDRESS) > 80 THEN
237 l_ADDRESS := l_ADDRESS_LINE_1;
238 IF LENGTH (l_ADDRESS) > 80 THEN
239 l_ADDRESS := SUBSTR(l_ADDRESS_LINE_1, 1, 80);
240 END IF;
241 END IF;
242 END IF;
243
244 -- Employee Count
245 OPEN c_count_asg_processed (p_tax_unit_id, p_qtr, p_year);
246 FETCH c_count_asg_processed INTO ln_emp_count;
247 CLOSE c_count_asg_processed;
248
249 -- State Abbreviation
250 /* SELECT count(DISTINCT(ORG_INFORMATION1))
251 INTO ln_count_state
252 FROM HR_ORGANIZATION_INFORMATION_V
253 WHERE org_information_context = 'State Tax Rules'
254 AND ORGANIZATION_ID = p_tax_unit_id;
255
256 IF ln_count_state > 1 THEN
257 lv_STATE_ABBR_1 := 'M';
258 lv_STATE_ABBR_2 := 'U';
259 END IF;
260
261 IF ln_count_state = 1 THEN
262 OPEN c_state_abbr(p_tax_unit_id);
263 FETCH c_state_abbr INTO lv_STATE_ABBR;
264 CLOSE c_state_abbr;
265 lv_STATE_ABBR_1 := SUBSTR(lv_STATE_ABBR, 1, 1);
266 lv_STATE_ABBR_2 := SUBSTR(lv_STATE_ABBR, 2, 1);
267 END IF; */
268
269 /***********************************************************
270 ** Fetch Balance value
271 ***********************************************************/
272 g_c_qtr_end_date := TO_DATE(p_qtr||'-'||p_year,'DD-MM-YYYY');
273 -- If all the balances being reported are valid then make use of the BRA
274
275 IF g_CP_STATUS = 'Y' THEN
276
277 OPEN c_bal_values(p_tax_unit_id) ;
278 LOOP
279 FETCH c_bal_values INTO ln_tax, ln_wage, lv_tax_type ;
280 EXIT WHEN c_bal_values%NOTFOUND ;
281
282 IF lv_tax_type = 'FIT' THEN
283 ln_941_box2 := NVL(ln_wage,0);
284 ln_fit_withheld := NVL(ln_tax,0);
285
286 ELSIF lv_tax_type = 'MEDICARE' THEN
287 ln_medicare_er_taxable := NVL(ln_wage,0);
288
289 ELSIF lv_tax_type = 'SS' THEN
290
291 ln_ss_er_taxable := NVL(ln_wage,0);
292 ELSIF lv_tax_type = 'EIC' THEN
293 ln_eic_advance := NVL(ln_tax,0);
294
295 ELSIF lv_tax_type = 'W2_BOX_7' THEN
296 ln_ss_tips := NVL(ln_tax,0);
297
298 ELSIF lv_tax_type = 'W2_UNCOLL_MED_TIPS' THEN
299 ln_w2_uncoll_med_tips := NVL(ln_tax,0);
300
301 ELSIF lv_tax_type = 'W2_UNCOLL_SS_GTL' THEN
302 ln_w2_uncoll_ss_gtl := NVL(ln_tax,0);
303
304 ELSIF lv_tax_type = 'W2_UNCOLL_MED_GTL' THEN
305 ln_w2_uncoll_med_gtl := NVL(ln_tax,0);
306
307 ELSIF lv_tax_type = 'W2_UNCOLL_SS_TAX_TIPS' THEN
308 ln_w2_uncoll_ss_tax_tips := NVL(ln_tax,0);
309 END IF;
310
311 END LOOP;
312 CLOSE c_bal_values;
313 ln_ss_er_taxable := ln_ss_er_taxable - ln_ss_tips;
314
315 -- Else use the previous group calls to fetch the balance values
316 ELSE
317 pay_us_taxbal_view_pkg.us_gp_multiple_gre_qtd
318 (p_tax_unit_id => p_tax_unit_id ,
319 p_effective_date => g_c_qtr_end_date,
320 p_balance_name1 => 'Regular Earnings',
321 p_balance_name2 => 'FIT Withheld',
322 p_balance_name3 => 'Medicare ER Taxable',
323 p_balance_name4 => 'SS ER Taxable',
324 p_balance_name5 => 'EIC Advance',
325 p_balance_name6 => 'Pre Tax Deductions',
326 p_balance_name7 => 'FIT Non W2 Pre Tax Dedns',
327 p_balance_name8 => 'W2 BOX 7',
328 p_balance_name9 => 'W2 Uncoll Med GTL',
329 p_balance_name10 => 'W2 Uncoll Med Tips',
330 p_balance_name11 => 'W2 Uncoll SS GTL',
331 p_balance_name12 => 'W2 Uncoll SS Tax Tips',
332 p_value1 => ln_regular_earnings,
333 p_value2 => ln_fit_withheld,
334 p_value3 => ln_medicare_er_taxable,
335 p_value4 => ln_ss_er_taxable,
336 p_value5 => ln_eic_advance,
337 p_value6 => ln_pretax_dedn,
338 p_value7 => ln_fit_non_w2_pretax_dedn,
339 p_value8 => ln_ss_tips,
340 p_value9 => ln_w2_uncoll_med_gtl,
341 p_value10 => ln_w2_uncoll_med_tips,
342 p_value11 => ln_w2_uncoll_ss_gtl,
343 p_value12 => ln_w2_uncoll_ss_tax_tips);
344
345 pay_us_taxbal_view_pkg.us_gp_subject_to_tax_gre_qtd
346 (p_balance_name1 => 'Supplemental Earnings for NWFIT',
347 p_balance_name2 => 'Supplemental Earnings for FIT',
348 p_balance_name3 => 'Pre Tax Deductions for FIT',
349 p_balance_name4 => NULL,
350 p_balance_name5 => NULL,
351 p_effective_date => g_c_qtr_end_date,
352 p_tax_unit_id => p_tax_unit_id,
353 p_value1 => ln_supp_earn_nwfit,
354 p_value2 => ln_supp_earn_fit,
355 p_value3 => ln_pretax_dedn_fit,
356 p_value4 => ln_dummy,
357 p_value5 => ln_dummy);
358
359 -- Assign the Fed Wages Tips Balances
360 ln_941_box2 := ln_regular_earnings
361 + ln_supp_earn_fit
362 + ln_supp_earn_nwfit
363 - ln_pretax_dedn
364 - ln_pretax_dedn_fit
365 - ln_fit_non_w2_pretax_dedn;
366
367 ln_ss_er_taxable := ln_ss_er_taxable - ln_ss_tips;
368
369 END IF;
370
371
372 ln_w2_uncoll_med_ss := ln_w2_uncoll_med_gtl
373 + ln_w2_uncoll_med_tips
374 + ln_w2_uncoll_ss_gtl
375 + ln_w2_uncoll_ss_tax_tips;
376
377 ln_941_box5a := NVL(ln_ss_er_taxable,0) * 0.124;
378 IF ln_941_box5a < 0 THEN
379 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
380 ' has negative B5A Total Taxable SS Wages. Please check.');
381 END IF;
382
383 ln_941_box5b := NVL(ln_ss_tips,0) * 0.124;
384 IF ln_941_box5b < 0 THEN
385 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
386 ' has negative B5B Total Taxable SS Wages. Please check.');
387 END IF;
388
389 ln_941_box5c := NVL(ln_medicare_er_taxable,0) * 0.029;
390 IF ln_941_box5c < 0 THEN
391 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
392 ' has negative B5C Taxable Medicare Wages. Please check.');
393 END IF;
394
395 ln_941_box5d := NVL(ln_941_box5a,0) +
396 NVL(ln_941_box5c,0) +
397 NVL(ln_941_box5b,0);
398 IF ln_941_box5d < 0 THEN
399 hr_utility.trace('001.'||'Tax Unit ID: '||TO_CHAR(p_tax_unit_id)||
400 ' has negative B5D Total SS Medicare Taxes. Please check.');
401 END IF;
402
403 ln_941_box6 := ln_fit_withheld + ln_941_box5d;
404
405 -- Following is the code for writing XML data
406 vXMLTable.DELETE;
407 vCtr := 0;
408 vXMLTable(vCtr).xmlstring := '<?xml version="1.0" ?>';
409 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
410 || '<PAYUS941>';
411 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
412 || ' '
413 || '<LIST_G_TAX_UNIT_HEADER>';
414 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
415 || ' '
416 || '<G_TAX_UNIT_HEADER>';
417 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
418 || ' '
419 ||'<YEAR>'
420 || p_year || '</YEAR>';
421 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
422 || ' '
423 ||'<YEAR1>'
424 || p_year || '</YEAR1>';
425 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
426 || ' '
427 ||'<YEAR2>'
428 || p_year || '</YEAR2>';
429 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
430 || ' '
431 ||'<EIN>'
432 || l_gre_ein || '</EIN>';
433 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
434 || ' '
435 ||'<gre_name>'
436 ||'<![CDATA[ '|| l_gre_name || ' ]]>'||'</gre_name>';
437 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
438 || ' '
439 ||'<ADDRESS1>'
440 ||'<![CDATA[ '|| l_ADDRESS || ' ]]>'||'</ADDRESS1>';
441 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
442 || ' '
443 ||'<CITY>'
444 || l_CITY || '</CITY>';
445 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
446 || ' '
447 ||'<STATE>'
448 || l_STATE || '</STATE>';
449 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
450 || ' '
451 ||'<ZIP>'
452 || l_ZIP || '</ZIP>';
453 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
454 || ' '
455 ||'<QTR>'
456 || p_qtr || '</QTR>';
457 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
458 || ' '
459 ||'<EMP_COUNT>'
460 || ln_EMP_COUNT || '</EMP_COUNT>';
461 IF ln_941_box2 = 0 THEN ln_941_box2 := NULL; END IF;
462 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
463 || ' '
464 ||'<B2_TOTAL_WAGES>'
465 || ln_941_box2
466 || '</B2_TOTAL_WAGES>';
467 IF ln_supp_earn_fit = 0 THEN ln_supp_earn_fit := NULL; END IF;
468 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
469 || ' '
470 ||'<FWT_SUPP_EARN_FIT>'
471 || ln_supp_earn_fit
472 || '</FWT_SUPP_EARN_FIT>';
473 IF ln_supp_earn_nwfit = 0 THEN ln_supp_earn_nwfit := NULL; END IF;
474 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
475 || ' '
476 ||'<FWT_SUPP_EARN_NWFIT>'
477 || ln_supp_earn_nwfit
478 || '</FWT_SUPP_EARN_NWFIT>';
479 IF ln_pretax_dedn_fit = 0 THEN ln_pretax_dedn_fit := NULL; END IF;
480 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
481 || ' '
482 ||'<PRE_TAX_DED_FOR_FIT>'
483 || ln_pretax_dedn_fit
484 || '</PRE_TAX_DED_FOR_FIT>';
485 IF ln_regular_earnings = 0 THEN ln_regular_earnings := NULL; END IF;
486 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
487 || ' '
488 ||'<FWT_REGULAR_EARNINGS>'
489 || ln_regular_earnings
490 || '</FWT_REGULAR_EARNINGS>';
491 IF ln_fit_withheld = 0 THEN ln_fit_withheld := NULL; END IF;
492 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
493 || ' '
494 ||'<FWT_WITHHELD>'
495 || ln_fit_withheld
496 || '</FWT_WITHHELD>';
497 IF ln_ss_er_taxable = 0 THEN ln_ss_er_taxable := NULL; END IF;
498 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
499 || ' '
500 ||'<SS_ER_TAXABLE>'
501 || ln_ss_er_taxable
502 || '</SS_ER_TAXABLE>';
503 IF ln_941_box5a = 0 THEN ln_941_box5a := NULL; END IF;
504 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
505 || ' '
506 ||'<B5A_TAXABLE_SS_WAGES>'
507 || ln_941_box5a
508 || '</B5A_TAXABLE_SS_WAGES>';
509 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
510 || ' '
511 ||'<TAXABLE_SS_TIPS />';
512 IF ln_ss_tips = 0 THEN ln_ss_tips := NULL; END IF;
513 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
514 || ' '
515 ||'<SS_ER_TAXABLE_TIPS>'
516 || ln_ss_tips
517 || '</SS_ER_TAXABLE_TIPS>';
518 IF ln_941_box5b = 0 THEN ln_941_box5b := NULL; END IF;
519 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
520 || ' '
521 ||'<SS_ER_TAXABLE_TIPS_F>'
522 || ln_941_box5b
523 || '</SS_ER_TAXABLE_TIPS_F>';
524 IF ln_medicare_er_taxable = 0 THEN ln_medicare_er_taxable := NULL; END IF;
525 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
526 || ' '
527 ||'<MEDICARE_ER_TAXABLE>'
528 || ln_medicare_er_taxable
529 || '</MEDICARE_ER_TAXABLE>';
530 IF ln_941_box5c = 0 THEN ln_941_box5c := NULL; END IF;
531 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
532 || ' '
533 ||'<B5C_TAXABLE_MEDICARE_WAGES>'
534 || ln_941_box5c
535 || '</B5C_TAXABLE_MEDICARE_WAGES>';
536 IF ln_941_box5d = 0 THEN ln_941_box5d := NULL; END IF;
537 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
538 || ' '
539 ||'<B5D_TOTAL_SS_MEDICARE_TAXES>'
540 || ln_941_box5d
541 || '</B5D_TOTAL_SS_MEDICARE_TAXES>';
542 IF ln_941_box6 = 0 THEN ln_941_box6 := NULL; END IF;
543 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
544 || ' '
545 ||'<B6_TOTAL_B3_B5D>'
546 || ln_941_box6
547 || '</B6_TOTAL_B3_B5D>';
548 IF ln_w2_uncoll_med_ss = 0 THEN ln_w2_uncoll_med_ss := NULL; END IF;
549 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
550 || ' '
551 ||'<W2_UNCOLL_MED_SS>'
552 || ln_w2_uncoll_med_ss
553 || '</W2_UNCOLL_MED_SS>';
554 IF ln_eic_advance =0 THEN ln_eic_advance := NULL; END IF;
555 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
556 || ' '
557 ||'<EIC_ADVANCE>'
558 || ln_eic_advance
559 || '</EIC_ADVANCE>';
560 /* vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
561 || ' '
562 ||'<STATE_ABBR_1>'
563 || lv_STATE_ABBR_1
564 || '</STATE_ABBR_1>';
565 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
566 || ' '
567 ||'<STATE_ABBR_2>'
568 || lv_STATE_ABBR_2
569 || '</STATE_ABBR_2>'; */
570 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
571 || ' '
572 ||'</G_TAX_UNIT_HEADER>';
573 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
574 || ' '
575 ||'</LIST_G_TAX_UNIT_HEADER>';
576 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
577 || '<C_TRACE />';
578 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
579 || '</PAYUS941>';
580 END get_941_balances;
581 -- covers 9. Advance earned income credit (CP_EIC_ADVANCE)
582 -- covers 5d. Total SS Taxes (C_B5D_TOTAL_SS_MEDICARE_TAXES)
583 -- covers 5c.(ii) Taxable Medicare wages (C_B5C_TAXABLE_MEDICARE_WAGES)
584 -- covers 5c.(i) Taxable Medicare wages (CP_MEDICARE_ER_TAXABLE)
585 -- covers 5b.(ii) Taxable SS Tips (CF_SS_ER_TAXABLE_TIPS)
586 -- covers 5b.(i) Taxable SS Tips (CP_SS_ER_TAXABLE_TIPS)
587 -- covers 5a.(ii) Taxable SS Wages (C_B5A_TAXABLE_SS_WAGES)
588 -- covers 5a.(i) Taxable SS Wages (CP_SS_ER_TAXABLE)
589 -- covers 3. Total IT withheld from Wages, Tips and other compensation
590 -- (CP_FWT_WITHHELD)
591 -- covers 2. Wages, tips and other compensation (C_B2_TOTAL_WAGES)
592 -- covers 1. No. of employees (l_C_emp_count)
593 --******** Generate Federal Tax Balances (end)
594 --******** Write to CLOB (start)
595
596
597 PROCEDURE WritetoCLOB (p_XML OUT NOCOPY CLOB)
598 IS
599 l_xfdf_string CLOB;
600 BEGIN
601 hr_utility.set_location('Entered Procedure Write to clob ',100);
602 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
603 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
604 -- if vXMLTable.count > 0 then
605 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
606 LOOP
607 dbms_lob.writeAppend(l_xfdf_string,
608 LENGTH(vXMLTable(ctr_table).xmlstring),
609 vXMLTable(ctr_table).xmlstring );
610 END LOOP;
611 p_XML := l_xfdf_string;
612 EXCEPTION
613 WHEN OTHERS THEN
614 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
615 HR_UTILITY.RAISE_ERROR;
616 END WritetoCLOB;
617
618
619 PROCEDURE gen_941_report(p_business_group_id IN NUMBER,
620 p_tax_unit_id IN NUMBER,
621 p_year IN VARCHAR2,
622 p_qtr IN VARCHAR2,
623 p_template_name IN VARCHAR2,
624 p_xml OUT NOCOPY Clob)
625 IS
626 BEGIN
627 set_session_variables_contexts(
628 p_business_group_id,
629 p_tax_unit_id,
630 p_year,
631 p_qtr);
632 get_941_balances(
633 p_business_group_id,
634 p_tax_unit_id,
635 p_year,
636 p_qtr);
637 WritetoCLOB (p_XML);
638
639 END gen_941_report;
640
641 /*****************************************************************************
642 Name : pay_us_941_report_wrapper
643 Purpose : calls procedure gen_941_report, generates xml output and submits
644 xml publisher report to merge the xml output with template.
645 *****************************************************************************/
646 PROCEDURE pay_us_941_report_wrapper
647 ( errbuf OUT NOCOPY VARCHAR2,
648 retcode OUT NOCOPY VARCHAR2,
649 p_business_group_id IN NUMBER,
650 p_tax_unit_id IN VARCHAR2,
651 p_year IN NUMBER,
652 p_qtr IN VARCHAR2,
653 p_valid_template_list IN VARCHAR2,
654 p_appl_short_name IN VARCHAR2,
655 p_template_name IN VARCHAR2,
656 p_effective_date IN VARCHAR2
657 )
658 IS
659
660 l_xml CLOB;
661 l_req_id NUMBER;
662 l_req_id2 NUMBER;
663 l_program VARCHAR2(100);
664 l_errbuf VARCHAR2(240);
665 l_procedure_name VARCHAR2(100);
666
667 BEGIN
668
669 l_procedure_name := '.pay_us_941_report_wrapper';
670 hr_utility.set_location('Entering '|| g_package || l_procedure_name, 10);
671
672 /* Generate the xml output */
673 gen_941_report(p_business_group_id => p_business_group_id,
674 p_tax_unit_id => p_tax_unit_id,
675 p_year => p_year,
676 p_qtr => p_qtr,
677 p_template_name => 'DUMMY',
678 p_xml => l_xml);
679
680 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
681 LOOP
682 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,vXMLTable(ctr_table).xmlstring);
683 END LOOP;
684
685 /* Submit XML Report Publisher request for the generated xml output*/
686 l_req_id := fnd_global.conc_request_id;
687
688 IF l_req_id > 0 THEN
689
690 l_program := 'XDOREPPB';
691 l_req_id2 := fnd_request.submit_request(
692 application => 'XDO',
693 program => l_program,
694 argument1 => l_req_id,
695 argument2 => 801,
696 argument3 => p_template_name,
697 argument4 => 'en-US',
698 argument5 => 'N',
699 argument6 => 'PDF',
700 argument7 => 'PDF' );
701 ELSE
702
703 fnd_message.retrieve(l_errbuf);
704
705 END IF;
706
707 IF l_req_id2 > 0 THEN
708
709 Commit;
710
711 ELSE
712
713 fnd_message.retrieve(l_errbuf);
714
715 END IF;
716
717 EXCEPTION
718 WHEN others THEN
719 hr_utility.raise_error;
720
721 hr_utility.set_location('Leaving '|| g_package || l_procedure_name, 20);
722
723 End pay_us_941_report_wrapper ;
724
725 END pay_us_941_report;
726