[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W2_INFO_PKG
Source
1 PACKAGE BODY PAY_US_W2_INFO_PKG as
2 /* $Header: pyusw2dt.pkb 120.34.12010000.6 2008/08/11 11:21:31 asgugupt ship $ */
3 g_package CONSTANT VARCHAR2(33) := 'pay_us_w2_info_pkg.';
4 l_w2_fields l_w2_fields_rec;
5 l_state_tab l_state_table;
6 l_local_tab l_local_table;
7 l_box12_tab l_box12_table;
8 l_box14_tab l_box14_table;
9 l_state_local_tab l_state_local_table;
10 g_occ_tax_rate NUMBER;
11 g_mh_tax_rate NUMBER;
12 g_mh_tax_limit NUMBER;
13 g_occ_mh_tax_limit NUMBER;
14 g_occ_mh_wage_limit NUMBER;
15 g_mh_tax_wage_limit NUMBER;
16 g_print_instr VARCHAR2(1) := 'Y';
17
18
19 FUNCTION append_to_lob(p_text in varchar)
20 RETURN BLOB IS
21
22 text_size NUMBER;
23 raw_data RAW(32767);
24 temp_blob BLOB;
25 BEGIN
26
27 raw_data:=utl_raw.cast_to_raw(p_text);
28 text_size:=utl_raw.length(raw_data);
29
30 dbms_lob.createtemporary(temp_blob,false,DBMS_LOB.CALL);
31 dbms_lob.open(temp_blob,dbms_lob.lob_readwrite);
32
33 dbms_lob.writeappend(temp_blob,
34 text_size,
35 raw_data
36 );
37
38 IF dbms_lob.ISOPEN(temp_blob)=1 THEN
39 hr_utility.trace('Closing temp_lob' );
40 dbms_lob.close(temp_blob);
41 END IF;
42
43 return temp_blob;
44 END;
45
46 FUNCTION check_negative_number (p_data number)
47 RETURN VARCHAR2 IS
48 l_data VARCHAR2(250);
49 BEGIN
50 IF nvl(p_data,0) <=0 THEN
51 hr_utility.trace('Negative/zero value '||p_data);
52 l_data := '';
53 ELSE
54 l_data := p_data;
55 END IF;
56
57 return l_data;
58
59 END;
60
61 FUNCTION xml_special_chars (p_xml_data VARCHAR2)
62 RETURN VARCHAR2 IS
63 l_xml_data VARCHAR2(250);
64 BEGIN
65 l_xml_data := REPLACE (p_xml_data, '&', '&');
66 l_xml_data := REPLACE (l_xml_data, '>', '>');
67 l_xml_data := REPLACE (l_xml_data, '<', '<');
68 l_xml_data := REPLACE (l_xml_data, '''', ''');
69 l_xml_data := REPLACE (l_xml_data, '"', '"');
70
71 return l_xml_data;
72
73 END;
74
75 FUNCTION populate_state_local_table ( l_state_tab l_state_table,
76 l_local_tab l_local_table)
77 RETURN l_state_local_table IS
78 l_curr_state PLS_INTEGER;
79 l_curr_local PLS_INTEGER;
80 l_stloc_tcnt NUMBER;
81 p_write_state BOOLEAN;
82 l_prior_local PLS_INTEGER;
83
84
85 PROCEDURE check_prior_local IS
86 BEGIN
87 --{ Check for prior local
88 hr_utility.trace('In check_prior_local,l_prior_local '||l_prior_local);
89 p_write_state := FALSE;
90 IF l_prior_local IS NOT NULL THEN
91
92 --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
93 hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code);
94 -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
95 hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code);
96
97 /* If the state code of prior local is same as current state
98 then move the index of the current state */
99 IF (l_state_tab(l_curr_state).state_code <>
100 l_local_tab(l_prior_local).state_code) THEN
101 hr_utility.trace('State Code of prior local matches current state code so setting p_write_state TRUE');
102 p_write_state := TRUE;
103 ELSE
104 p_write_state := FALSE;
105 END IF;
106 ELSE
107 p_write_state := TRUE;
108 END IF;
109 --}
110 END;
111
112 PROCEDURE write_state_only IS
113 BEGIN
114
115 hr_utility.trace('Writing state without local ');
116
117 l_stloc_tcnt := l_state_local_tab.count;
118 l_state_local_tab(l_stloc_tcnt).state_code
119 := l_state_tab(l_curr_state).state_code ;
120 l_state_local_tab(l_stloc_tcnt).state_ein
121 := l_state_tab(l_curr_state).state_ein ;
122 l_state_local_tab(l_stloc_tcnt).state_wages
123 := l_state_tab(l_curr_state).state_wages ;
124 l_state_local_tab(l_stloc_tcnt).state_tax
125 := l_state_tab(l_curr_state).state_tax ;
126 l_state_local_tab(l_stloc_tcnt).locality := '';
127 l_state_local_tab(l_stloc_tcnt).locality_wages := '';
128 l_state_local_tab(l_stloc_tcnt).locality_tax := '';
129 l_curr_state := l_curr_state + 1;
130 END;
131
132 BEGIN -- populate_state_local_table
133
134 l_curr_state := l_state_tab.FIRST;
135 l_curr_local := l_local_tab.FIRST;
136 l_stloc_tcnt := 0;
137
138 LOOP
139
140 hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
141 hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
142 hr_utility.trace('l_curr_state '||l_curr_state);
143 hr_utility.trace('l_curr_local '||l_curr_local);
144
145 EXIT WHEN (l_curr_state > l_state_tab.COUNT and
146 l_curr_local > l_local_tab.COUNT)
147 OR (l_curr_state > l_state_tab.COUNT and
148 l_curr_local IS NULL);
149
150 l_prior_local := l_local_tab.PRIOR(l_curr_local);
151 hr_utility.trace('l_prior_local '||l_local_tab.PRIOR(l_curr_local));
152
153 IF (l_curr_state IS NOT NULL AND
154 l_curr_local IS NOT NULL ) AND
155 (l_curr_state <= l_state_tab.COUNT ) AND
156 (l_curr_local <= l_local_tab.COUNT) THEN
157
158 hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code);
159 hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code);
160 hr_utility.trace('l_curr_state '||l_curr_state);
161
162 IF (l_state_tab(l_curr_state).state_code =
163 l_local_tab(l_curr_local).state_code ) THEN
164
165 hr_utility.trace('Statecode of state is EQUAL to Local state Code');
166
167 l_stloc_tcnt := l_state_local_tab.count;
168
169 hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein);
170 hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages);
171 hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax);
172 /* Check to see if the state code of prior local is same as current state */
173
174 check_prior_local;
175
176 IF p_write_state THEN
177 l_state_local_tab(l_stloc_tcnt).state_code
178 := l_state_tab(l_curr_state).state_code ;
179
180 l_state_local_tab(l_stloc_tcnt).state_ein
181 := l_state_tab(l_curr_state).state_ein ;
182
183 l_state_local_tab(l_stloc_tcnt).state_wages
184 := l_state_tab(l_curr_state).state_wages ;
185
186 l_state_local_tab(l_stloc_tcnt).state_tax
187 := l_state_tab(l_curr_state).state_tax ;
188
189 l_state_local_tab(l_stloc_tcnt).locality
190 := l_local_tab(l_curr_local).locality ;
191
192 l_state_local_tab(l_stloc_tcnt).locality_wages
193 := l_local_tab(l_curr_local).locality_wages;
194
195 l_state_local_tab(l_stloc_tcnt).locality_tax
196 := l_local_tab(l_curr_local).locality_tax;
197
198 ELSE
199 l_state_local_tab(l_stloc_tcnt).state_code
200 := l_state_tab(l_curr_state).state_code;
201
202 l_state_local_tab(l_stloc_tcnt).state_ein
203 := '' ;
204
205 l_state_local_tab(l_stloc_tcnt).state_wages
206 := '' ;
207
208 l_state_local_tab(l_stloc_tcnt).state_tax
209 := '' ;
210
211 l_state_local_tab(l_stloc_tcnt).locality
212 := l_local_tab(l_curr_local).locality ;
213
214 l_state_local_tab(l_stloc_tcnt).locality_wages
215 := l_local_tab(l_curr_local).locality_wages;
216
217 l_state_local_tab(l_stloc_tcnt).locality_tax
218 := l_local_tab(l_curr_local).locality_tax;
219 END IF;
220 -- l_curr_state := l_state_tab.NEXT(l_curr_state);
221 /* Just move the index for the current local as one state
222 may have multiple locals */
223 -- l_curr_local := l_local_tab.NEXT(l_curr_local);
224 l_curr_local := l_curr_local + 1;
225
226 --{
227 ELSIF (l_state_tab(l_curr_state).state_code <
228 l_local_tab(l_curr_local).state_code ) THEN
229 check_prior_local;
230 IF p_write_state THEN
231 hr_utility.trace('current state doesnot match with prior state, so write current state only');
232 write_state_only;
233 ELSE
234 hr_utility.trace('current state matches with prior state, move to next state');
235 l_curr_state := l_curr_state + 1;
236 END IF;
237 --}
238 ELSE
239 hr_utility.trace('Statecode of state is greater than Local state Code');
240 l_stloc_tcnt := l_state_local_tab.count;
241
242 l_state_local_tab(l_stloc_tcnt).state_code
243 := l_local_tab(l_curr_local).state_code ;
244
245 l_state_local_tab(l_stloc_tcnt).state_ein
246 := '';
247
248 l_state_local_tab(l_stloc_tcnt).state_wages
249 := '' ;
250
251 l_state_local_tab(l_stloc_tcnt).state_tax
252 := '' ;
253
254 l_state_local_tab(l_stloc_tcnt).locality
255 := l_local_tab(l_curr_local).locality ;
256
257 l_state_local_tab(l_stloc_tcnt).locality_wages
258 := l_local_tab(l_curr_local).locality_wages;
259
260 l_state_local_tab(l_stloc_tcnt).locality_tax
261 := l_local_tab(l_curr_local).locality_tax;
262
263 l_curr_local := l_curr_local + 1;
264 END IF;
265
266 -- if l_curr_local is not null and l_curr_state is null
267 ELSIF (l_curr_state IS NULL and l_curr_local IS NOT NULL)
268 OR (l_curr_state > l_state_tab.COUNT AND
269 l_curr_local <= l_local_tab.COUNT) THEN
270
271 hr_utility.trace('Current state is null and curr local is NOT null');
272 hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
273 hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality);
274 hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax);
275 hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages);
276
277
278 l_stloc_tcnt := l_state_local_tab.count;
279 l_state_local_tab(l_stloc_tcnt).state_code
280 := '' ;
281
282 l_state_local_tab(l_stloc_tcnt).state_ein
283 := '';
284
285 l_state_local_tab(l_stloc_tcnt).state_wages
286 := '';
287
288 l_state_local_tab(l_stloc_tcnt).state_tax
289 := '' ;
290
291 l_state_local_tab(l_stloc_tcnt).locality
292 := l_local_tab(l_curr_local).locality ;
293
294 l_state_local_tab(l_stloc_tcnt).locality_wages
295 := l_local_tab(l_curr_local).locality_wages;
296
297 l_state_local_tab(l_stloc_tcnt).locality_tax
298 := l_local_tab(l_curr_local).locality_tax;
299
300 l_curr_local := l_curr_local + 1;
301
302 ELSIF (l_curr_state IS NOT NULL and l_curr_local IS NULL)
303 OR (l_curr_state <= l_state_tab.COUNT AND
304 l_curr_local > l_local_tab.COUNT) THEN
305
306 hr_utility.trace('Current state is not null and curr local is null');
307 hr_utility.trace('l_curr_state '||l_curr_state);
308 hr_utility.trace('l_curr_local '||l_curr_local);
309
310 check_prior_local;
311 IF p_write_state THEN
312 hr_utility.trace('current state doesnot match with prior state, so write current state only');
313 write_state_only;
314 ELSE
315 hr_utility.trace('current state matches with prior state, move to next state');
316 l_curr_state := l_curr_state + 1;
317
318 END IF;
319 ELSE
320 hr_utility.trace('Completed populating all states and locals');
321 exit;
322 END IF;
323 END LOOP;
324
325 return l_state_local_tab;
326 END;
327
328 PROCEDURE get_w2_data(p_asg_action_id NUMBER,
329 p_tax_unit_id NUMBER,
330 p_year NUMBER,
331 p_error_msg out nocopy VARCHAR2)
332 -- RETURN l_w2_fields_rec
333 IS
334 l_sl_total_count number;
335 l_sl_count number ;
336 l_b12_total_count number;
337 l_b12_count number;
338 l_b14_total_count number;
339 l_b14_count number;
340 l_local_total_count number;
341 l_nr_jd varchar2(11);
342 l_nr_flag varchar2(1);
343 l_locality varchar2(100);
344 l_locality_wages number;
345 l_locality_tax number;
346 l_jurisdiction varchar2(15);
347 l_state_code varchar2(10);
348 l_tax_type varchar2(100);
349 l_box14_boonmh_value number;
350 l_nj_state_printed VARCHAR2(1);
351 l_hi_state_printed VARCHAR2(1); /* 6519495 */
352 l_nj_planid VARCHAR2(20);
353 l_corrected_date DATE;
354 l_profile_date DATE;
355 l_agent_tax_unit_id number;
356 l_error_msg VARCHAR2(500);
357 l_business_group_id number;
358 l_org_federal_ein VARCHAR2(100);
359 l_org_employer_name VARCHAR2(200);
360 l_org_address VARCHAR2(500);
361 l_live_profile_option VARCHAR2(100);
362 l_payroll_action_id NUMBER;
363 l_w2_corrected VARCHAR2(10);
364 p_effective_date DATE;
365 lr_employee_addr pay_us_get_item_data_pkg.person_name_address;
366 p_assignment_id NUMBER;
367 l_person_id NUMBER;
368 l_profile_date_string VARCHAR2(40);
369 /* 6500188 */
370 l_first_name per_all_people_f.first_name%type;
371 l_middle_name per_all_people_f.middle_names%type;
372 l_dummy varchar2(100);
373 l_full_name per_all_people_f.full_name%type;
374
375
376
377 -- PROCEDURE get_employee_info (p_asg_action_id NUMBER) IS
378
379 CURSOR c_get_emp_info (p_asg_action_id NUMBER,
380 p_tax_unit_id NUMBER,
381 p_year NUMBER ) IS
382 select puw.assignment_action_id control_number,
383 nvl(ssn,'Applied For') SSN,
384 first_name||
385 decode(middle_name,null,' ',
386 ' '||substr(middle_name,1,1)||' ') ||
387 pre_name_adjunt emp_name,
388 last_name , -- Bug 4523389
389 hr_us_w2_rep.get_per_item(p_asg_action_id,
390 'A_PER_SUFFIX') emp_suffix,
391 /* Bug 5575567
392 decode(pa.address_line1,null,null,pa.address_line1 ||'\r')||
393 decode(pa.address_line2,null,null,pa.address_line2||'\r') ||
394 decode(pa.address_line3,null,null,pa.address_line3||'\r') ||
395 decode(pa.town_or_city,null,null,pa.town_or_city ||' ')||
396 decode(pa.region_2,null,null,pa.region_2||' ')|| pa.postal_code employee_address,
397 */
398 decode(W2_WAGES_TIPS_COMPENSATION,0,'',W2_WAGES_TIPS_COMPENSATION) wages_tips_compensation,
399 decode(W2_FED_IT_WITHHELD,0,'',W2_FED_IT_WITHHELD) fit_withheld,
400 decode(W2_SOCIAL_SECURITY_WAGES,0,'',W2_SOCIAL_SECURITY_WAGES) ss_wages,
401 decode(W2_SST_WITHHELD,0,'',W2_SST_WITHHELD) ss_withheld,
402 decode(W2_MED_WAGES_TIPS,0,'',W2_MED_WAGES_TIPS) med_wages,
403 decode(W2_MED_TAX_WITHHELD,0,'',W2_MED_TAX_WITHHELD) med_withheld,
404 decode(W2_SOCIAL_SECURITY_TIPS,0,'',W2_SOCIAL_SECURITY_TIPS) ss_tips,
405 decode(W2_ALLOCATED_TIPS,0,'',W2_ALLOCATED_TIPS) allocated_tips,
406 decode(W2_ADV_EIC_PAYMENT,0,'',W2_ADV_EIC_PAYMENT) eic_payment,
407 decode(W2_DEPENDENT_CARE_BEN,0,'',W2_DEPENDENT_CARE_BEN) dependent_care,
408 decode(W2_NONQUAL_PLANS,0,'',W2_NONQUAL_PLANS) non_qual_plan,
409 decode(W2_STATUTORY_EMPLOYEE,'X','Y',null,'N',' ','N')
410 stat_employee,
411 decode(W2_RETIREMENT_PLAN,'X','Y',null,'N',' ','N')
412 retirement_plan,
413 decode(W2_THIRD_PARTY_SICK_PAY,'X','Y',null,
414 'N',' ','N') sick_pay,
415 person_id , puw.assignment_id -- bug 5575567
416 from pay_us_wages_w2_v puw
417 --per_addresses pa
418 where puw.assignment_action_id = p_asg_action_id
419 /* bug 5575567
420 and pa.primary_flag = 'Y'
421 and pa.person_id = puw.person_id */
422 and puw.tax_unit_id = p_tax_unit_id
423 and puw.year = p_year;
424 --and sysdate between pa.date_from and nvl(pa.date_to,sysdate);
425
426
427 CURSOR c_get_box12_info (p_asg_action_id NUMBER) IS
428 select w2_balance_code,
429 w2_balance_code_value
430 from pay_us_code_w2_v
431 where w2_balance_code_value > 0
432 and assignment_action_id = p_asg_action_id
433 order by w2_balance_code;
434
435 CURSOR c_get_box14_info (p_asg_action_id NUMBER) IS
436 SELECT substr(w2_other_meaning,1,10) w2_other_code,
437 w2_other_value
438 from pay_us_other_w2_v
439 where w2_other_value > 0
440 and assignment_action_id = p_asg_action_id;
441
442 /* CURSOR c_get_box14_boonocc (p_asg_action_id NUMBER) IS
443 SELECT 'BOONOCC' w2_other_code,
444 w2_local_income_tax -
445 decode(sign(w2_local_wages - 16666), -1,
446 w2_local_wages * 0.0015, 25) w2_other_value
447 from pay_us_locality_w2_v
448 where state_abbrev = 'KY'
449 and tax_type = 'COUNTY'
450 and substr(jurisdiction,1,6) = '18-015'
451 and assignment_action_id = p_asg_action_id
452 and w2_local_income_tax > 0;
453
454
455 CURSOR c_get_box14_boonmh (p_asg_action_id NUMBER) IS
456 SELECT 'BOONMH' w2_other_code,
457 decode(sign(w2_local_wages - 16666), -1,
458 w2_local_wages * 0.0015, 25) w2_other_value
459 from pay_us_locality_w2_v
460 where state_abbrev = 'KY'
461 and tax_type = 'COUNTY'
462 and substr(jurisdiction,1,6) = '18-015'
463 and assignment_action_id = p_asg_action_id
464 and w2_local_income_tax > 0;
465 */
466 CURSOR c_get_local_info (p_asg_action_id NUMBER) IS
467 SELECT locality_name locality,
468 decode(w2_local_wages,0,'',w2_local_wages) locality_wages,
469 w2_local_income_tax locality_tax,
470 jurisdiction jurisdiction,
471 state_abbrev state_code,
472 tax_type
473 FROM pay_us_locality_w2_v
474 WHERE assignment_action_id = p_asg_action_id
475 /*commented for 4102684
476 and W2_LOCAL_INCOME_TAX > 0*/
477 -- and (jurisdiction <> '18-015-0000')
478 order by state_code, tax_type;
479
480 CURSOR c_get_state_info (p_asg_action_id NUMBER) IS
481 SELECT substr(state_abbrev,1,2) state_code,
482 substr(state_ein,1,20) state_ein,
483 decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES) state_wages,
484 decode(W2_STATE_INCOME_TAX,0,'',W2_STATE_INCOME_TAX) state_tax
485 FROM pay_us_state_w2_v state
486 WHERE assignment_action_id = p_asg_action_id
487 and ( (w2_state_wages > 0) or
488 (W2_STATE_INCOME_TAX > 0) ) /* 6809739 */
489 order by state_code;
490
491 /* CURSOR c_get_employer_info ( p_tax_unit_id NUMBER, p_year NUMBER) IS
492 select federal_ein federal_ein,
493 tax_unit_name employer_name,
494 substr(decode(put.address_line_1,null,null,put.address_line_1||pay_us_w2_info_pkg.EOL),1,45)||
495 substr(decode(put.address_line_2,null,null,put.address_line_2||pay_us_w2_info_pkg.EOL),1,45)||
496 substr(decode(put.address_line_3,null,null,put.address_line_3||pay_us_w2_info_pkg.EOL),1,45)||
497 decode(put.town_or_city,null,null,put.town_or_city||' ')||
498 decode(state,null,null,state||' ')||put.postal_code
499 employer_address
500 from pay_us_w2_tax_unit_v put
501 where tax_unit_id = p_tax_unit_id
502 and year = p_year;
503 */
504
505 /*Bug 5735076 added by vaprakas*/
506 CURSOR c_get_employer_info ( p_tax_unit_id NUMBER, p_year NUMBER) IS
507 select federal_ein federal_ein,
508 tax_unit_name employer_name,
509 decode(put.address_line_1,null,null,substr(put.address_line_1,1,45)||pay_us_w2_info_pkg.EOL)||
510 decode(put.address_line_2,null,null,substr(put.address_line_2,1,45)||pay_us_w2_info_pkg.EOL)||
511 decode(put.address_line_3,null,null,substr(put.address_line_3,1,45)||pay_us_w2_info_pkg.EOL)||
512 decode(put.town_or_city,null,null,put.town_or_city||' ')||
513 decode(state,null,null,state||' ')||put.postal_code
514 employer_address
515 from pay_us_w2_tax_unit_v put
516 where tax_unit_id = p_tax_unit_id
517 and year = p_year;
518 /*end 5735076*/
519
520 CURSOR c_get_business_group_id ( p_tax_unit_id NUMBER) IS
521 select business_group_id
522 from hr_all_organization_units /*hr_organization_units*/
523 where organization_id = p_tax_unit_id;
524
525 CURSOR c_get_payroll_action (p_asg_action_id NUMBER)IS
526 select payroll_action_id
527 from pay_assignment_actions
528 where assignment_action_id = p_asg_action_id;
529
530
531 PROCEDURE print_corrected IS
532 begin
533
534 /* Code to print Amended/amended date on W-2 */
535
536 l_corrected_date := fnd_date.canonical_to_date(
537 pay_us_archive_util.get_archive_value(p_asg_action_id,
538 'A_ARCHIVE_DATE',
539 p_tax_unit_id));
540
541 hr_utility.trace('Archive Date : ' || l_corrected_date);
542 /* l_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
543 IF (l_profile_option is null) or (l_profile_option = '') THEN
544 l_profile_date := fnd_date.canonical_to_date('4712/12/31');
545 ELSE
546 */
547 OPEN c_get_payroll_action(p_asg_action_id);
548 FETCH c_get_payroll_action INTO l_payroll_action_id;
549 CLOSE c_get_payroll_action;
550
551
552 --l_profile_date := fnd_date.canonical_to_date(p_year+1||'/'||l_profile_option);
553 -- END IF;
554 /* If live profile option is null then allow the view W-2 till end of time
555 otherwise check if the archive profile option exist then use the archive
556 profile option date else continue using the old logic of appending year,
557
558 */
559 l_live_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
560 hr_utility.trace('View Online W2 Profile date'||l_live_profile_option);
561
562 IF (l_live_profile_option is null) or (l_live_profile_option = '') THEN
563 l_profile_date := fnd_date.canonical_to_date('4712/12/31');
564 ELSE
565 --- changed th date format for bug 5656018
566 l_profile_date_string :=-- fnd_date.canonical_to_date(
567 -- fnd_date.chardate_to_date(
568 pay_us_archive_util.get_archive_value(l_payroll_action_id,
569 'A_VIEW_ONLINE_W2',
570 p_tax_unit_id);
571
572 hr_utility.trace('l_profile_date '||l_profile_date_string);
573
574 IF (l_profile_date_string is null) or (l_profile_date_string = '') THEN
575 l_profile_date := fnd_date.canonical_to_date(p_year+1||'/'||l_live_profile_option);
576 hr_utility.trace('l_profile_date was null , setting to '||l_profile_date);
577 ELSE
578 l_profile_date := -- bug 5656018 fnd_date.chardate_to_date
579 fnd_date.canonical_to_date(l_profile_date_string);
580
581 hr_utility.trace('l_profile_date was not null , setting to '||l_profile_date);
582
583 END IF;
584 END IF;
585
586 l_w2_corrected :=pay_us_archive_util.get_archive_value(p_asg_action_id,
587 'A_W2_CORRECTED',
588 p_tax_unit_id);
589
590 hr_utility.trace('View Online W2 Profile date'||l_live_profile_option);
591
592 /* If the profile option is blank for fixing bug 4947964 and archive
593 item , A_W2_CORRECTED is not archived for an employee then it
594 will never print 'CORRECTED' which may be incorrect for W-2s which
595 were corrected sometime. To fix this either
596 archive A_W2_CORRECTED for each employee or set the profile option
597 to a date and run Year end preproces rearchive to archive the profile
598 option */
599
600 IF l_w2_corrected IS NULL THEN
601 IF l_corrected_date > l_profile_date THEN
602 l_w2_fields.amended := 'CORRECTED';
603 l_w2_fields.amended_date := l_corrected_date;
604 END IF;
605 ELSIF l_w2_corrected = 'Y' THEN
606 l_w2_fields.amended := 'CORRECTED';
607 l_w2_fields.amended_date := l_corrected_date;
608 END IF;
609
610 end; -- end print_corrected}
611
612 --{ begin get_w2_data
613 BEGIN
614 l_sl_count := 1;
615 l_b12_count :=1;
616 l_b14_count :=1;
617
618 OPEN c_get_business_group_id(p_tax_unit_id);
619 FETCH c_get_business_group_id
620 INTO l_business_group_id;
621 CLOSE c_get_business_group_id;
622
623 hr_utility.trace('Business Group id ' ||l_business_group_id);
624
625 IF l_business_group_id is not null THEN
626 hr_us_w2_rep.get_agent_tax_unit_id(l_business_group_id
627 ,p_year
628 ,l_agent_tax_unit_id
629 ,l_error_msg);
630 END IF;
631
632 hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
633 hr_utility.trace('l_error_msg ' ||l_error_msg);
634 /* If l_erro_msg is not null then throw error else get remaining data for W2 */
635 IF l_error_msg IS NOT NULL THEN
636 p_error_msg := l_error_msg;
637 ELSE
638
639 OPEN c_get_employer_info(nvl(l_agent_tax_unit_id, p_tax_unit_id),p_year);
640 FETCH c_get_employer_info
641 INTO l_w2_fields.federal_ein,
642 l_w2_fields.employer_name,
643 l_w2_fields.employer_address;
644 CLOSE c_get_employer_info;
645
646 hr_utility.trace('l_w2_fields.federal_ein ' ||l_w2_fields.federal_ein);
647 hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
648 hr_utility.trace('l_w2_fields.employer_name ' ||l_w2_fields.employer_name);
649
650 IF l_agent_tax_unit_id IS NOT NULL THEN
651
652 hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
653
654 OPEN c_get_employer_info(p_tax_unit_id,p_year);
655 FETCH c_get_employer_info
656 INTO l_org_federal_ein,
657 l_org_employer_name,
658 l_org_address;
659 CLOSE c_get_employer_info;
660 hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
661 hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
662 hr_utility.trace('l_org_address ' ||l_org_address);
663
664 l_w2_fields.employer_address
665 := 'Agent For ' ||substr(l_org_employer_name,1,44)||
666 pay_us_w2_info_pkg.EOL ||
667 l_w2_fields.employer_address;
668 END IF;
669 /* Bug 5575567 */
670 hr_utility.trace(' sysdate ' || sysdate);
671 hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
672
673 IF (trunc(sysdate) <
674 fnd_date.canonical_to_date(p_year||'/12/31')) THEN
675 p_effective_date := fnd_date.canonical_to_date(p_year||'/12/31');
676 ELSE
677 p_effective_date := sysdate;
678 END IF;
679
680 --p_effective_date := sysdate; Bug 6443139
681 hr_utility.trace(' p_effective_date ' || p_effective_date);
682
683 OPEN c_get_emp_info(p_asg_action_id,p_tax_unit_id, p_year) ;
684 FETCH c_get_emp_info
685 INTO l_w2_fields.control_number,
686 l_w2_fields.SSN,
687 l_w2_fields.emp_name,
688 l_w2_fields.last_name,
689 l_w2_fields.emp_suffix, -- Bug 4523389
690 -- bug 5575567 l_w2_fields.employee_address,
691 l_w2_fields.wages_tips_compensation,
692 l_w2_fields.fit_withheld,
693 l_w2_fields.ss_wages,
694 l_w2_fields.ss_withheld,
695 l_w2_fields.med_wages,
696 l_w2_fields.med_withheld,
697 l_w2_fields.ss_tips,
698 l_w2_fields.allocated_tips,
699 l_w2_fields.eic_payment,
700 l_w2_fields.dependent_care,
701 l_w2_fields.non_qual_plan,
702 l_w2_fields.stat_employee,
703 l_w2_fields.retirement_plan,
704 l_w2_fields.sick_pay,
705 -- bug 5575567
706 l_person_id ,
707 p_assignment_id ;
708
709 hr_utility.trace('EMP NAME ' ||l_w2_fields.emp_name);
710 hr_utility.trace('Control Number ' ||l_w2_fields.control_number);
711 IF c_get_emp_info%NOTFOUND THEN
712 hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
713 CLOSE c_get_emp_info;
714 raise NO_DATA_FOUND;
715 END IF;
716 CLOSE c_get_emp_info;
717 /* 6500188 */
718 begin
719 l_full_name := pay_us_get_item_data_pkg.GET_CONTACT_PERSON_INFO(
720 0 , p_effective_date ,0, ' ' ,
721 ' ' , 'W2' , ' ' , ' ' , ' ' , l_person_id ,
722 ' ' , l_dummy , l_dummy , l_dummy , l_dummy , l_dummy , l_dummy ,
723 l_first_name , l_middle_name ,l_w2_fields.last_name );
724
725 /* 6782720 */
726 l_w2_fields.last_name := initcap(l_w2_fields.last_name);
727
728 select l_first_name||decode(l_middle_name,null,' ',
729 ' '||substr(l_middle_name,1,1)||' ') ||
730 hr_us_w2_rep.get_per_item(p_asg_action_id,
731 'A_PER_PREFIX' ) into l_w2_fields.emp_name from dual;
732 exception when others then null;
733 end ;
734
735 lr_employee_addr :=
736 pay_us_get_item_data_pkg.GET_PERSON_NAME_ADDRESS(
737 'W2',
738 l_person_id,
739 p_assignment_id,
740 p_effective_date,
741 p_effective_date,
742 'Y', --p_validate,
743 'W2_XML');
744
745 IF lr_employee_addr.addr_line_1 IS NOT NULL THEN
746 l_w2_fields.employee_address := substr(lr_employee_addr.addr_line_1,1,45) ||
747 PAY_US_W2_INFO_PKG.EOL;
748 END IF;
749
750 IF lr_employee_addr.addr_line_2 IS NOT NULL THEN
751 l_w2_fields.employee_address := l_w2_fields.employee_address||
752 substr(lr_employee_addr.addr_line_2,1,45) ||
753 PAY_US_W2_INFO_PKG.EOL;
754 END IF;
755
756 IF lr_employee_addr.addr_line_3 IS NOT NULL THEN
757 l_w2_fields.employee_address := l_w2_fields.employee_address||
758 substr(lr_employee_addr.addr_line_3,1,45) ||
759 PAY_US_W2_INFO_PKG.EOL;
760 END IF;
761
762 l_w2_fields.employee_address :=l_w2_fields.employee_address ||
763 lr_employee_addr.city||' '||
764 lr_employee_addr.region_2 ||' '||
765 lr_employee_addr.postal_code;
766
767 if lr_employee_addr.country <> 'US' then
768 l_w2_fields.employee_address := l_w2_fields.employee_address ||' '||
769 lr_employee_addr.country_name;
770 end if;
771
772
773 IF greatest(nvl(l_w2_fields.wages_tips_compensation,0)
774 ,nvl(l_w2_fields.fit_withheld,0)
775 ,nvl(l_w2_fields.ss_wages,0)
776 ,nvl(l_w2_fields.ss_withheld,0)
777 ,nvl(l_w2_fields.med_wages,0)
778 ,nvl(l_w2_fields.med_withheld,0)
779 ,nvl(l_w2_fields.ss_tips,0)
780 ,nvl(l_w2_fields.allocated_tips,0)
781 ,nvl(l_w2_fields.eic_payment,0)
782 ,nvl(l_w2_fields.dependent_care,0)
783 --bug 6874650
784 -- ,nvl(l_w2_fields.non_qual_plan,0)) <= 0 THEN
785 ,nvl(l_w2_fields.non_qual_plan,0)) < 0 THEN
786 --bug 6874650
787 hr_utility.trace('Negative values for box1-11 ');
788 p_error_msg := 'Negative values for box1-11';
789 END IF;
790 --changes for bug 6821345 starts here
791 -- IF nvl(l_w2_fields.wages_tips_compensation,0) <=0 THEN
792 IF nvl(l_w2_fields.wages_tips_compensation,0) <0 THEN
793 --changes for bug 6821345 ends here
794 hr_utility.trace('Negative/zero value for box1 ');
795 p_error_msg := 'Negative values for box1';
796 END IF;
797
798 l_sl_total_count := 0;
799 OPEN c_get_state_info(p_asg_action_id) ;
800 LOOP
801 hr_utility.trace('In state loop ' );
802 l_sl_total_count := l_sl_total_count + 1;
803 FETCH c_get_state_info
804 INTO l_state_tab(l_sl_total_count).state_code,
805 l_state_tab(l_sl_total_count).state_ein,
806 l_state_tab(l_sl_total_count).state_wages,
807 l_state_tab(l_sl_total_count).state_tax;
808 EXIT WHEN c_get_state_info%NOTFOUND;
809 hr_utility.trace('State_code '|| l_state_tab(l_sl_total_count).state_code);
810 hr_utility.trace('State_EIN '|| l_state_tab(l_sl_total_count).state_ein);
811
812 IF l_state_tab(l_sl_total_count).state_code = 'NJ' THEN
813 l_nj_state_printed := 'Y';
814 END IF;
815
816 IF l_state_tab(l_sl_total_count).state_code = 'HI' THEN /* 6519495 */
817 l_hi_state_printed := 'Y';
818 END IF;
819
820 END LOOP;
821 CLOSE c_get_state_info;
822
823 l_local_total_count := 0;
824 OPEN c_get_local_info(p_asg_action_id) ;
825 LOOP
826 hr_utility.trace('In local loop ' );
827 FETCH c_get_local_info
828 INTO l_locality,
829 l_locality_wages,
830 l_locality_tax,
831 l_jurisdiction,
832 l_state_code,
833 l_tax_type;
834
835 hr_utility.trace('l_locality is '||l_locality);
836
837 EXIT WHEN c_get_local_info%NOTFOUND;
838 IF l_locality_tax > 0 THEN
839 -- l_local_total_count := l_local_total_count + 1;
840 /* populate the locality table only if the jurisdiction code <> 18-015-000
841 as this needs to be reported in box 14 as occupational and mental health tax*/
842 IF ( l_tax_type = 'COUNTY' and l_jurisdiction = '18-015-0000') THEN
843 hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
844 IF (l_locality_tax > 0 and l_locality_wages > 0) THEN
845 hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
846
847 l_b14_total_count := l_box14_tab.count+1;
848 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
849
850 IF (g_mh_tax_rate IS NULL OR g_mh_tax_limit IS NULL
851 OR g_occ_tax_rate IS NULL OR g_occ_mh_tax_limit IS NULL
852 OR g_occ_mh_wage_limit IS NULL OR g_mh_tax_wage_limit IS NULL )
853 THEN
854 hr_utility.trace('Getting Mental health and Occupational tax limits');
855 hr_us_w2_rep.get_county_tax_info('18-015-0000',
856 p_year,
857 g_occ_tax_rate,
858 g_mh_tax_rate,
859 g_mh_tax_limit,
860 g_occ_mh_tax_limit,
861 g_occ_mh_wage_limit,
862 g_mh_tax_wage_limit);
863 END IF;
864
865 IF l_locality_wages >= g_mh_tax_wage_limit then
866 l_box14_boonmh_value := g_mh_tax_limit ;
867 ELSE
868 l_box14_boonmh_value := l_locality_wages * (g_mh_tax_rate/100 ) ;
869 END IF;
870
871 l_box14_tab(l_b14_total_count).box14_code := 'BOONMH';
872 l_box14_tab(l_b14_total_count).box14_meaning := l_box14_boonmh_value;
873 hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning '||l_box14_tab(l_b14_total_count).box14_meaning);
874
875 l_b14_total_count := l_box14_tab.count+1;
876 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
877
878 l_box14_tab(l_b14_total_count).box14_code := 'BOONOCC';
879 l_box14_tab(l_b14_total_count).box14_meaning :=
880 l_locality_tax - l_box14_boonmh_value;
881 END IF;
882 ELSE
883 l_local_total_count := l_local_total_count + 1;
884 l_local_tab(l_local_total_count).locality := l_locality;
885 l_local_tab(l_local_total_count).locality_wages := l_locality_wages;
886 l_local_tab(l_local_total_count).locality_tax := l_locality_tax;
887 l_local_tab(l_local_total_count).jurisdiction := l_jurisdiction;
888 l_local_tab(l_local_total_count).state_code := l_state_code;
889 l_local_tab(l_local_total_count).tax_type := l_tax_type;
890
891 hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality);
892 hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code);
893
894 hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction);
895 hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax);
896 hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type);
897
898
899 IF (nvl(l_local_tab(l_local_total_count).locality_tax,0) > 0) THEN
900 IF (l_local_tab(l_local_total_count).tax_type = 'CITY SCHOOL' or
901 l_local_tab(l_local_total_count).tax_type = 'COUNTY SCHOOL' ) THEN
902
903 hr_utility.trace('Locality Tax Type is County/city school');
904
905 if l_local_tab(l_local_total_count).state_code = 'OH' then
906
907 hr_utility.trace('Locality state code is OH');
908
909 l_local_tab(l_local_total_count).locality
910 := substr(l_local_tab(l_local_total_count).jurisdiction,5,4)
911 ||' '||substr(l_local_tab(l_local_total_count).locality,1,8);
912 elsif l_local_tab(l_local_total_count).state_code = 'KY' then
913 hr_utility.trace('Locality state code is KY');
914 l_local_tab(l_local_total_count).locality
915 := substr(l_local_tab(l_local_total_count).jurisdiction,7,2)
916 ||' '||substr(l_local_tab(l_local_total_count).locality,1,10);
917 else
918 hr_utility.trace('Locality state code neither OH nor KY');
919 l_local_tab(l_local_total_count).locality
920 := substr(l_local_tab(l_local_total_count).jurisdiction,4,5)
921 ||' '||substr(l_local_tab(l_local_total_count).locality,1,7);
922 end if;
923 END IF;
924 END IF;
925
926 hr_utility.trace('l_local_tab(l_local_total_count).locality is '||l_local_tab(l_local_total_count).locality);
927 IF (l_local_tab(l_local_total_count).state_code = 'IN'
928 and l_local_tab(l_local_total_count).tax_type = 'COUNTY') THEN
929 BEGIN
930 select nvl(value,'N') into l_nr_flag
931 from ff_database_items fdi,
932 ff_archive_items fai
933 where user_name = 'A_IN_NR_FLAG'
934 and fdi.user_entity_id = fai.user_entity_id
935 and fai.context1 = p_asg_action_id;
936
937 IF l_nr_flag = 'N' THEN
938 BEGIN
939 select nvl(value,'00-000-0000') into l_nr_jd
940 from ff_database_items fdi,
941 ff_archive_items fai
942 where fdi.user_name = 'A_IN_RES_JD'
943 and fdi.user_entity_id = fai.user_entity_id
944 and context1 = p_asg_action_id;
945
946 IF substr(l_local_tab(l_local_total_count).jurisdiction,1,2) = '15' THEN
947 IF l_nr_jd <> l_local_tab(l_local_total_count).jurisdiction THEN
948 l_local_tab(l_local_total_count).locality
949 := 'NR '||substr(l_local_tab(l_local_total_count).locality,1,10);
950 END IF;
951 END IF;
952 EXCEPTION WHEN others THEN
953 null;
954 END;
955 END IF;
956 EXCEPTION WHEN others THEN
957 null;
958 END;
959 END IF;
960 END IF ; /* end of the KY boone county check */
961 END IF; /* l_locality_tax > 0 */
962 END LOOP;
963 CLOSE c_get_local_info;
964
965 /* l_state_local_tab := populate_state_local_table
966 (l_state_tab,l_local_tab); */
967 l_b12_total_count := 0;
968 OPEN c_get_box12_info (p_asg_action_id) ;
969 LOOP
970 l_b12_total_count := l_b12_total_count + 1;
971 FETCH c_get_box12_info
972 INTO l_box12_tab(l_b12_total_count).box12_code,
973 l_box12_tab(l_b12_total_count).box12_meaning;
974
975 EXIT WHEN c_get_box12_info%NOTFOUND;
976 hr_utility.trace('In box12 loop '||l_box12_tab(l_b12_total_count).box12_code );
977 END LOOP;
978 CLOSE c_get_box12_info ;
979
980 -- l_b14_total_count := l_box14_tab.count;
981 OPEN c_get_box14_info (p_asg_action_id) ;
982 LOOP
983 l_b14_total_count := l_box14_tab.count+1;
984 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
985
986 FETCH c_get_box14_info
987 INTO l_box14_tab(l_b14_total_count).box14_code,
988 l_box14_tab(l_b14_total_count).box14_meaning;
989
990 EXIT WHEN c_get_box14_info%NOTFOUND;
991 hr_utility.trace('In box14 loop ' ||l_box14_tab(l_b14_total_count).box14_code);
992
993 END LOOP;
994 CLOSE c_get_box14_info ;
995 /*
996 OPEN c_get_box14_boonocc (p_asg_action_id) ;
997 LOOP
998 l_b14_total_count := l_box14_tab.count+1;
999 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1000
1001 FETCH c_get_box14_boonocc
1002 INTO l_box14_tab(l_b14_total_count).box14_code,
1003 l_box14_tab(l_b14_total_count).box14_meaning;
1004
1005 EXIT WHEN c_get_box14_boonocc%NOTFOUND;
1006 hr_utility.trace('In c_get_box14_boonocc loop ' ||l_box14_tab(l_b14_total_count).box14_code);
1007
1008 END LOOP;
1009 CLOSE c_get_box14_boonocc ;
1010
1011
1012 OPEN c_get_box14_boonmh (p_asg_action_id) ;
1013 LOOP
1014 l_b14_total_count := l_box14_tab.count+1;
1015 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1016
1017 FETCH c_get_box14_boonmh
1018 INTO l_box14_tab(l_b14_total_count).box14_code,
1019 l_box14_tab(l_b14_total_count).box14_meaning;
1020
1021 EXIT WHEN c_get_box14_boonmh%NOTFOUND;
1022 hr_utility.trace('In c_get_box14_boonmh loop ' ||l_box14_tab(l_b14_total_count).box14_code);
1023
1024 END LOOP;
1025 CLOSE c_get_box14_boonmh ;
1026 */
1027
1028 If l_hi_state_printed = 'Y' then /* 6519495 */
1029
1030 l_b14_total_count := l_box14_tab.count + 1;
1031
1032 l_box14_tab(l_b14_total_count).box14_meaning := l_local_tab(l_local_total_count).locality_tax ;
1033 l_box14_tab(l_b14_total_count).box14_code := l_local_tab(l_local_total_count).locality ;
1034 l_local_tab(l_local_total_count).locality_wages := '' ;
1035 l_local_tab(l_local_total_count).locality_tax := '' ;
1036 l_local_tab(l_local_total_count).locality := '' ;
1037
1038 end if ;
1039
1040 l_state_local_tab := populate_state_local_table
1041 (l_state_tab,l_local_tab);
1042
1043
1044 /* Code to print NJ DI.P.P. # */
1045 -- Bug 4544792
1046 If l_nj_state_printed = 'Y' then
1047 l_nj_planid := pay_us_archive_util.get_archive_value(p_asg_action_id,
1048 'A_SCL_ASG_US_NJ_PLAN_ID', --A_EXTRA_ASSIGNMENT_INFORMATION_PAY_US_DISABILITY_PLAN_INFO_DF_PLAN_ID'
1049 p_tax_unit_id) ;
1050 If l_nj_planid IS NOT NULL then
1051 hr_utility.trace('NJ DIPP plan id: ' || l_nj_planid);
1052 l_b14_total_count := l_box14_tab.count + 1;
1053 l_box14_tab(l_b14_total_count).box14_code := 'DI P.P. # '||l_nj_planid ;
1054 l_box14_tab(l_b14_total_count).box14_meaning:='';
1055 end if;
1056 end if;
1057
1058 /* Code to print Amended/amended date on W-2 */
1059 print_corrected();
1060 END IF; /* l_error_msg is not null */
1061 END;
1062 -- } end get_w2_data
1063
1064 FUNCTION create_xml_string (l_w2_fields l_w2_fields_rec,
1065 l_box14_codea VARCHAR2,l_box14_meaninga VARCHAR2,
1066 l_box14_codeb VARCHAR2,l_box14_meaningb VARCHAR2,
1067 l_box14_codec VARCHAR2,l_box14_meaningc VARCHAR2,
1068 l_box12_codea VARCHAR2,l_box12_meaninga VARCHAR2,
1069 l_box12_codeb VARCHAR2,l_box12_meaningb VARCHAR2,
1070 l_box12_codec VARCHAR2,l_box12_meaningc VARCHAR2,
1071 l_box12_coded VARCHAR2,l_box12_meaningd VARCHAR2,
1072 l_state1_code VARCHAR2,l_state1_ein VARCHAR2,
1073 l_state1_wages VARCHAR2,l_state1_tax VARCHAR2,
1074 l_local1_wages VARCHAR2,l_local1_tax VARCHAR2,
1075 l_locality1 VARCHAR2,
1076 l_state2_code VARCHAR2,l_state2_ein VARCHAR2,
1077 l_state2_wages VARCHAR2, l_state2_tax VARCHAR2,
1078 l_local2_wages VARCHAR2,l_local2_tax VARCHAR2,
1079 l_locality2 VARCHAR2,p_year VARCHAR2)
1080 RETURN BLOB IS
1081 l_xml_string VARCHAR2(32767);
1082 l_xml_BLOB BLOB;
1083 is_temp varchar2(10);
1084 text_size NUMBER;
1085 raw_data RAW(32767);
1086 begin
1087 hr_utility.trace('In create XML string ' );
1088 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1089 IF (g_print_instr IS NULL) OR (g_print_instr = '') THEN
1090 g_print_instr := 'Y';
1091 END IF;
1092 -- Bug 4523389 : added the tag <emp_suffix>
1093 l_xml_string :='<xapi:data>'||EOL||
1094 '<w2>'||EOL||
1095 '<control_number>' || xml_special_chars(l_w2_fields.control_number)||'</control_number>'||EOL||
1096 '<federal_ein>' || xml_special_chars(l_w2_fields.federal_ein) ||'</federal_ein>'||EOL||
1097 '<employer_name>'|| xml_special_chars(l_w2_fields.employer_name)||'</employer_name>'||EOL||
1098 '<employer_address>'|| xml_special_chars(l_w2_fields.employer_address)||'</employer_address>'||EOL||
1099 '<ssn>' || xml_special_chars(l_w2_fields.ssn) ||'</ssn>'||EOL||
1100 '<emp_name>' || xml_special_chars(l_w2_fields.emp_name) ||'</emp_name>'||EOL||
1101 '<last_name>' || xml_special_chars(l_w2_fields.last_name) ||'</last_name>'||EOL||
1102 '<emp_suffix>' || xml_special_chars(l_w2_fields.emp_suffix) ||'</emp_suffix>'||EOL||
1103 '<employee_address>' || xml_special_chars(l_w2_fields.employee_address)||'</employee_address>'||EOL||
1104 '<wages_tips_compensation>' || check_negative_number(l_w2_fields.wages_tips_compensation) ||'</wages_tips_compensation>'||EOL||
1105 '<fit_withheld>' || check_negative_number(l_w2_fields.fit_withheld) ||'</fit_withheld>'||EOL||
1106 '<ss_wages>' || check_negative_number(l_w2_fields.ss_wages)||'</ss_wages>'||EOL||
1107 '<ss_withheld>' || check_negative_number(l_w2_fields.ss_withheld)||'</ss_withheld>'||EOL||
1108 '<med_wages>' || check_negative_number(l_w2_fields.med_wages)||'</med_wages>'||EOL||
1109 '<med_withheld>' || check_negative_number(l_w2_fields.med_withheld)||'</med_withheld>'||EOL||
1110 '<ss_tips>' ||check_negative_number(l_w2_fields.ss_tips)||'</ss_tips>'||EOL||
1111 '<allocated_tips>' ||check_negative_number(l_w2_fields.allocated_tips)||'</allocated_tips>'||EOL||
1112 '<eic_payment>' || check_negative_number(l_w2_fields.eic_payment)||'</eic_payment>'||EOL||
1113 '<dependent_care>' ||check_negative_number(l_w2_fields.dependent_care)||'</dependent_care>'||EOL||
1114 '<non_qual_plan>' || check_negative_number(l_w2_fields.non_qual_plan)||'</non_qual_plan>'||EOL||
1115 '<stat_employee>' || xml_special_chars(nvl(l_w2_fields.stat_employee,'N'))||'</stat_employee>'||EOL||
1116 '<retirement_plan>' || xml_special_chars(nvl(l_w2_fields.retirement_plan,'N'))||'</retirement_plan>'||EOL||
1117 '<sick_pay>' || xml_special_chars(nvl(l_w2_fields.sick_pay,'N'))||'</sick_pay>'||EOL||
1118 '<box14_codea>'||xml_special_chars(l_box14_codea) ||'</box14_codea>' ||EOL||
1119 '<box14_meaninga>'||xml_special_chars(l_box14_meaninga) ||'</box14_meaninga>' ||EOL||
1120 '<box14_codeb>'||xml_special_chars(l_box14_codeb) ||'</box14_codeb>' ||EOL||
1121 '<box14_meaningb>'||xml_special_chars(l_box14_meaningb) ||'</box14_meaningb>' ||EOL||
1122 '<box14_codec>'||xml_special_chars(l_box14_codec) ||'</box14_codec>' ||EOL||
1123 '<box14_meaningc>'||xml_special_chars(l_box14_meaningc) ||'</box14_meaningc>' ||EOL||
1124 '<box12_codea>'||xml_special_chars(l_box12_codea) ||'</box12_codea>' ||EOL||
1125 '<box12_meaninga>'||xml_special_chars(l_box12_meaninga) ||'</box12_meaninga>' ||EOL||
1126 '<box12_codeb>'||xml_special_chars(l_box12_codeb) ||'</box12_codeb>' ||EOL||
1127 '<box12_meaningb>'||xml_special_chars(l_box12_meaningb) ||'</box12_meaningb>' ||EOL||
1128 '<box12_codec>'||xml_special_chars(l_box12_codec) ||'</box12_codec>' ||EOL||
1129 '<box12_meaningc>'||xml_special_chars(l_box12_meaningc) ||'</box12_meaningc>' ||EOL||
1130 '<box12_coded>'||xml_special_chars(l_box12_coded) ||'</box12_coded>' ||EOL||
1131 '<box12_meaningd>'||xml_special_chars(l_box12_meaningd) ||'</box12_meaningd>' ||EOL||
1132 '<state1_code>'||xml_special_chars(l_state1_code)||'</state1_code>' ||EOL||
1133 '<state1_ein>'||xml_special_chars(l_state1_ein)||'</state1_ein>' ||EOL||
1134 '<state1_wages>'||check_negative_number(l_state1_wages)||'</state1_wages>' ||EOL||
1135 '<state1_tax>'||check_negative_number(l_state1_tax)||'</state1_tax>' ||EOL||
1136 '<local1_wages>'||check_negative_number(l_local1_wages)||'</local1_wages>' ||EOL||
1137 '<local1_tax>'||check_negative_number(l_local1_tax)||'</local1_tax>' ||EOL||
1138 '<locality1>'||xml_special_chars(l_locality1)||'</locality1>' ||EOL||
1139 '<state2_code>'||xml_special_chars(l_state2_code)||'</state2_code>' ||EOL||
1140 '<state2_ein>'||xml_special_chars(l_state2_ein)||'</state2_ein>' ||EOL||
1141 '<state2_wages>'||check_negative_number(l_state2_wages)||'</state2_wages>' ||EOL||
1142 '<state2_tax>'||check_negative_number(l_state2_tax)||'</state2_tax>' ||EOL||
1143 '<local2_wages>'||check_negative_number(l_local2_wages)||'</local2_wages>' ||EOL||
1144 '<local2_tax>'||check_negative_number(l_local2_tax)||'</local2_tax>' ||EOL||
1145 '<locality2>'||xml_special_chars(l_locality2)||'</locality2>' ||EOL||
1146 '<year>'||xml_special_chars(p_year)||'</year>' ||EOL||
1147 '<amended>' || xml_special_chars(l_w2_fields.amended)||'</amended>'||EOL||
1148 '<amended_date>' || xml_special_chars(l_w2_fields.amended_date)||'</amended_date>'||EOL||
1149 '<print_instruction>'||xml_special_chars(g_print_instr)||'</print_instruction>' ||EOL||
1150 '</w2>'||EOL||
1151 '</xapi:data>'||EOL;
1152
1153
1154
1155 hr_utility.trace('one set XML string ' ||l_xml_string);
1156 is_temp := dbms_lob.istemporary(l_xml_blob);
1157 hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
1158
1159 IF is_temp = 1 THEN
1160 DBMS_LOB.FREETEMPORARY(l_xml_blob);
1161 END IF;
1162
1163 dbms_lob.createtemporary(l_xml_blob,false,DBMS_LOB.CALL);
1164 dbms_lob.open(l_xml_blob,dbms_lob.lob_readwrite);
1165 hr_utility.trace('OPENED l_xml_blob ' );
1166
1167 raw_data:=utl_raw.cast_to_raw(l_xml_string);
1168 text_size:=utl_raw.length(raw_data);
1169
1170 dbms_lob.writeappend(l_xml_blob,text_size,raw_data);
1171
1172 hr_utility.trace('Get Length l_xml_clob ' ||dbms_lob.getlength(l_xml_blob) );
1173 dbms_lob.close(l_xml_blob);
1174 return l_xml_blob;
1175 exception
1176 when OTHERS then
1177 dbms_lob.close(l_xml_blob);
1178 HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1179 HR_UTILITY.RAISE_ERROR;
1180
1181 end create_xml_string;
1182 --} end create_xml_string
1183
1184 Function fetch_w2_xml(p_assignment_action_id Number,
1185 p_tax_unit_id NUMBER,
1186 p_year NUMBER,
1187 p_error_msg out nocopy VARCHAR2,
1188 p_is_SS boolean)
1189 return BLOB
1190 is
1191 l_xml_blob BLOB ;
1192 l_out_create_xml BLOB;
1193 l_box14_codea VARCHAR2(100);
1194 l_box14_meaninga VARCHAR2(100);
1195 l_box14_codeb VARCHAR2(100);
1196 l_box14_meaningb VARCHAR2(100);
1197 l_box14_codec VARCHAR2(100);
1198 l_box14_meaningc VARCHAR2(100);
1199 l_box12_codea VARCHAR2(100);
1200 l_box12_meaninga VARCHAR2(100);
1201 l_box12_codeb VARCHAR2(100);
1202 l_box12_meaningb VARCHAR2(100);
1203 l_box12_codec VARCHAR2(100);
1204 l_box12_meaningc VARCHAR2(100);
1205 l_box12_coded VARCHAR2(100);
1206 l_box12_meaningd VARCHAR2(100);
1207 l_state1_code VARCHAR2(100);
1208 l_state1_ein VARCHAR2(100);
1209 l_state1_wages VARCHAR2(100);
1210 l_state1_tax VARCHAR2(100);
1211 l_local1_wages VARCHAR2(100);
1212 l_local1_tax VARCHAR2(100);
1213 l_locality1 VARCHAR2(100);
1214 l_state2_code VARCHAR2(100);
1215 l_state2_ein VARCHAR2(100);
1216 l_state2_wages VARCHAR2(100);
1217 l_state2_tax VARCHAR2(100);
1218 l_local2_wages VARCHAR2(100);
1219 l_local2_tax VARCHAR2(100);
1220 l_locality2 VARCHAR2(100);
1221
1222 l_b14_total_count number;
1223 l_b14_count_completed number;
1224 l_b12_total_count number;
1225 l_b12_count_completed number;
1226 l_sl_total_count number;
1227 l_sl_count_completed number;
1228 l_local_total_count number;
1229 l_local_count_completed number;
1230 l_state_local_count number;
1231 l_state_local_total_count number;
1232 l_state_local_count_completed number;
1233 l_w2_set_cnt number;
1234 l_is_temp_xml_string VARCHAR2(2);
1235
1236 begin
1237 hr_utility.trace('In Fetch w2 xml loop ' );
1238
1239 l_b14_total_count := 0;
1240 l_b14_count_completed := 0;
1241 l_b12_total_count := 0;
1242 l_b12_count_completed := 0;
1243 l_sl_total_count := 0;
1244 l_sl_count_completed := 0;
1245 l_local_total_count := 0;
1246 l_local_count_completed := 0;
1247 l_state_local_count := 0;
1248 l_state_local_total_count := 0;
1249 l_state_local_count_completed := 0;
1250 l_w2_set_cnt := 0;
1251
1252 get_w2_data(p_assignment_action_id,p_tax_unit_id,p_year,p_error_msg);
1253
1254 IF p_error_msg IS NULL THEN
1255
1256 hr_utility.trace('After get W2 data' );
1257
1258 l_b14_total_count := l_box14_tab.count;
1259 l_b14_count_completed := 0;
1260 l_b12_total_count := l_box12_tab.count;
1261 l_b12_count_completed := 0;
1262 l_sl_total_count := l_state_tab.count;
1263 l_sl_count_completed := 0;
1264 l_local_total_count := l_local_tab.count;
1265 l_local_count_completed := 0;
1266 l_state_local_total_count := l_state_local_tab.count;
1267 l_state_local_count_completed := 0;
1268
1269 l_is_temp_xml_string := dbms_lob.istemporary(l_xml_blob);
1270 hr_utility.trace('Istemporary(l_xml_blob) ' ||l_is_temp_xml_string );
1271
1272 IF l_is_temp_xml_string = 1 THEN
1273 DBMS_LOB.FREETEMPORARY(l_xml_blob);
1274 END IF;
1275
1276 dbms_lob.createtemporary(l_xml_blob,false,DBMS_LOB.CALL);
1277 dbms_lob.open(l_xml_blob,dbms_lob.lob_readwrite);
1278
1279 IF (l_b14_total_count = 0) AND
1280 (l_b12_total_count = 0) AND
1281 --(l_sl_total_count = 0)
1282 (l_state_local_total_count = 0) THEN
1283
1284 hr_utility.trace('In l_b14_total_count and other counts =0 ' );
1285 -- dbms_lob.append(l_final_xml,p_xml_string);
1286 l_xml_blob := create_xml_string(l_w2_fields,
1287 l_box14_codea,l_box14_meaninga,
1288 l_box14_codeb,l_box14_meaningb,
1289 l_box14_codec,l_box14_meaningc,
1290 l_box12_codea,l_box12_meaninga,
1291 l_box12_codeb,l_box12_meaningb,
1292 l_box12_codec,l_box12_meaningc,
1293 l_box12_coded,l_box12_meaningd,
1294 l_state1_code,l_state1_ein,
1295 l_state1_wages,l_state1_tax,
1296 l_local1_wages,l_local1_tax,
1297 l_locality1,
1298 l_state2_code,l_state2_ein,
1299 l_state2_wages, l_state2_tax,
1300 l_local2_wages,l_local2_tax,
1301 l_locality2,p_year);
1302 hr_utility.trace('after getting XML Blob ' );
1303
1304 ELSE
1305
1306 --{
1307 LOOP
1308
1309 hr_utility.trace('In loop to get XML ' );
1310 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
1311 hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
1312 hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
1313 hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
1314
1315 hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
1316 hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
1317 hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1318 hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1319 hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1320 hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1321 hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
1322
1323 EXIT WHEN
1324 ((l_b14_total_count = 0) AND
1325 (l_b12_total_count = 0 ) AND
1326 (l_state_local_count_completed = l_state_local_total_count )) ;
1327
1328 l_box14_codea := '';
1329 l_box14_meaninga := '';
1330 l_box14_codeb := '';
1331 l_box14_meaningb := '';
1332 l_box14_codec := '';
1333 l_box14_meaningc := '';
1334
1335 IF l_b14_total_count > 0 THEN
1336 hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
1337 l_b14_count_completed := l_b14_count_completed + 1 ;
1338 hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
1339 l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code;
1340 l_box14_meaninga := l_box14_tab(l_b14_count_completed).box14_meaning;
1341 l_b14_total_count := l_b14_total_count -1;
1342 hr_utility.trace('l_box14_codea ' ||l_box14_codea);
1343 hr_utility.trace('l_box14_meaninga ' ||l_box14_meaninga);
1344
1345 END IF;
1346
1347 IF l_b14_total_count > 0 THEN
1348 hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
1349 l_b14_count_completed := l_b14_count_completed + 1 ;
1350 hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
1351 l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code;
1352 l_box14_meaningb := l_box14_tab(l_b14_count_completed).box14_meaning;
1353 l_b14_total_count := l_b14_total_count - 1;
1354 hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
1355 hr_utility.trace('l_box14_meaningb ' ||l_box14_meaningb);
1356
1357 END IF;
1358
1359 IF l_b14_total_count > 0 THEN
1360 hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
1361
1362 l_b14_count_completed := l_b14_count_completed + 1 ;
1363 hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
1364 l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code;
1365 l_box14_meaningc := l_box14_tab(l_b14_count_completed).box14_meaning;
1366 l_b14_total_count := l_b14_total_count -1;
1367 hr_utility.trace('l_box14_codec ' ||l_box14_codec);
1368 hr_utility.trace('l_box14_meaningc ' ||l_box14_meaningc);
1369
1370 END IF;
1371
1372 l_box12_codea := '';
1373 l_box12_meaninga := '';
1374 l_box12_codeb := '';
1375 l_box12_meaningb := '';
1376 l_box12_codec := '';
1377 l_box12_meaningc := '';
1378 l_box12_coded := '';
1379 l_box12_meaningd := '';
1380
1381 IF l_b12_total_count > 0 THEN
1382 hr_utility.trace('1. l_b12_total_count >0 ' ||l_b12_total_count);
1383 l_b12_count_completed := l_b12_count_completed + 1 ;
1384 l_box12_codea := l_box12_tab(l_b12_count_completed).box12_code;
1385 l_box12_meaninga := l_box12_tab(l_b12_count_completed).box12_meaning;
1386 l_b12_total_count := l_b12_total_count -1;
1387
1388 END IF;
1389
1390 IF l_b12_total_count > 0 THEN
1391 hr_utility.trace('2. l_b12_total_count >0 ' ||l_b12_total_count);
1392
1393 l_b12_count_completed := l_b12_count_completed + 1 ;
1394 l_box12_codeb := l_box12_tab(l_b12_count_completed).box12_code;
1395 l_box12_meaningb := l_box12_tab(l_b12_count_completed).box12_meaning;
1396 l_b12_total_count := l_b12_total_count -1;
1397 END IF;
1398
1399 IF l_b12_total_count > 0 THEN
1400 hr_utility.trace('3. l_b12_total_count >0 ' ||l_b12_total_count);
1401
1402 l_b12_count_completed := l_b12_count_completed + 1 ;
1403 l_box12_codec := l_box12_tab(l_b12_count_completed).box12_code;
1404 l_box12_meaningc := l_box12_tab(l_b12_count_completed).box12_meaning;
1405 l_b12_total_count := l_b12_total_count -1;
1406 END IF;
1407
1408 IF l_b12_total_count > 0 THEN
1409 hr_utility.trace('4. l_b12_total_count >0 ' ||l_b12_total_count);
1410
1411 l_b12_count_completed := l_b12_count_completed + 1 ;
1412 l_box12_coded := l_box12_tab(l_b12_count_completed).box12_code;
1413 l_box12_meaningd := l_box12_tab(l_b12_count_completed).box12_meaning;
1414 l_b12_total_count := l_b12_total_count -1;
1415 END IF;
1416
1417 l_state1_code := '';
1418 l_state1_ein := '';
1419 l_state1_wages := '';
1420 l_state1_tax := '';
1421 l_local1_wages := '';
1422 l_local1_tax := '';
1423 l_locality1 := '';
1424
1425 l_state2_code := '';
1426 l_state2_ein := '';
1427 l_state2_wages := '';
1428 l_state2_tax := '';
1429 l_local2_wages := '';
1430 l_local2_tax := '';
1431 l_locality2 := '';
1432
1433
1434 IF l_state_local_count_completed < l_state_local_total_count THEN
1435 hr_utility.trace('1. l_state_local_total_count >0 ' ||l_state_local_total_count);
1436
1437 l_state1_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1438 l_state1_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1439 l_state1_wages:= l_state_local_tab(l_state_local_count_completed).state_wages;
1440 l_state1_tax := l_state_local_tab(l_state_local_count_completed).state_tax;
1441 l_locality1 := l_state_local_tab(l_state_local_count_completed).locality;
1442 l_local1_wages:= l_state_local_tab(l_state_local_count_completed).locality_wages;
1443 l_local1_tax := l_state_local_tab(l_state_local_count_completed).locality_tax;
1444 l_state_local_count_completed := l_state_local_count_completed + 1 ;
1445 -- l_state_local_total_count := l_state_local_total_count -1;
1446
1447 END IF;
1448
1449
1450 hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1451 hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1452
1453 IF l_state_local_count_completed < l_state_local_total_count THEN
1454 hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
1455 hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
1456
1457 l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code;
1458 l_state2_ein := l_state_local_tab(l_state_local_count_completed).state_ein;
1459 l_state2_wages:= l_state_local_tab(l_state_local_count_completed).state_wages;
1460 l_state2_tax := l_state_local_tab(l_state_local_count_completed).state_tax;
1461 l_locality2 := l_state_local_tab(l_state_local_count_completed).locality;
1462 l_local2_wages:= l_state_local_tab(l_state_local_count_completed).locality_wages;
1463 l_local2_tax := l_state_local_tab(l_state_local_count_completed).locality_tax;
1464 -- l_state_local_total_count := l_state_local_total_count -1;
1465 l_state_local_count_completed := l_state_local_count_completed + 1 ;
1466
1467 END IF;
1468
1469 hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
1470 hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
1471 hr_utility.trace('l_local_total_count ' ||l_local_total_count);
1472 hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
1473
1474 l_w2_set_cnt := l_w2_set_cnt +1;
1475 IF l_w2_set_cnt > 1 THEN
1476 l_w2_fields.wages_tips_compensation := '';
1477 l_w2_fields.fit_withheld := '';
1478 l_w2_fields.ss_wages := '';
1479 l_w2_fields.ss_withheld := '';
1480 l_w2_fields.med_wages:= '';
1481 l_w2_fields.med_withheld := '';
1482 l_w2_fields.ss_tips := '';
1483 l_w2_fields.allocated_tips := '';
1484 l_w2_fields.eic_payment := '';
1485 l_w2_fields.dependent_care := '';
1486 l_w2_fields.non_qual_plan := '';
1487 l_w2_fields.stat_employee := '';
1488 l_w2_fields.retirement_plan := '';
1489 l_w2_fields.sick_pay := '';
1490
1491 END IF;
1492
1493 l_out_create_xml := create_xml_string(l_w2_fields,
1494 l_box14_codea,l_box14_meaninga,
1495 l_box14_codeb,l_box14_meaningb,
1496 l_box14_codec,l_box14_meaningc,
1497 l_box12_codea,l_box12_meaninga,
1498 l_box12_codeb,l_box12_meaningb,
1499 l_box12_codec,l_box12_meaningc,
1500 l_box12_coded,l_box12_meaningd,
1501 l_state1_code,l_state1_ein,
1502 l_state1_wages,l_state1_tax,
1503 l_local1_wages,l_local1_tax,
1504 l_locality1,
1505 l_state2_code,l_state2_ein,
1506 l_state2_wages,
1507 l_state2_tax,
1508 l_local2_wages,l_local2_tax,
1509 l_locality2,p_year);
1510
1511 hr_utility.trace('After l_out_create_xml, length of LOB ' ||
1512 dbms_lob.getlength(l_out_create_xml));
1513 -- IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
1514
1515 dbms_lob.append(l_xml_blob,l_out_create_xml);
1516 hr_utility.trace('Length of l_xml_blob ' ||dbms_lob.getlength(l_xml_blob));
1517
1518 /* ELSE
1519 dbms_lob.writeappend(l_xml_string,dbms_lobamount,l_out_create_xml);;*/
1520 -- END IF;
1521
1522 END LOOP;
1523 END IF;
1524 --}
1525 END IF ; /* p_error_msg is null */
1526 hr_utility.trace('XML String is ');
1527
1528 --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
1529
1530 return l_xml_blob;
1531 EXCEPTION
1532 WHEN OTHERS then
1533 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1534 END fetch_w2_xml;
1535
1536 FUNCTION get_final_xml (p_assignment_action_id Number,
1537 p_tax_unit_id NUMBER,
1538 p_year NUMBER,
1539 p_w2_template_location VARCHAR2,
1540 p_inst_template_location VARCHAR2,
1541 p_output_location VARCHAR2,
1542 p_error_msg OUT nocopy VARCHAR2)
1543 RETURN BLOB IS
1544 p_xml_blob BLOB;
1545 l_final_xml BLOB;
1546 l_final_xml_string VARCHAR2(32000);
1547 l_last_xml_string VARCHAR2(32000);
1548 l_last_xml BLOB;
1549 l_is_temp_final_xml VARCHAR2(2);
1550 l_temp_blob BLOB;
1551
1552 BEGIN
1553 -- hr_utility.trace_on(null,'w2');
1554 hr_utility.trace('Deleting PL/SQL tables');
1555 l_state_local_tab.delete;
1556 l_state_tab.delete;
1557 l_local_tab.delete;
1558 l_box12_tab.delete;
1559 l_box14_tab.delete;
1560
1561 l_w2_fields.amended := '';
1562 l_w2_fields.amended_date := '';
1563 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1564
1565 p_xml_blob := fetch_w2_xml(p_assignment_action_id ,
1566 p_tax_unit_id ,
1567 p_year,
1568 p_error_msg , true);
1569
1570 hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1571
1572 IF p_error_msg IS NULL THEN
1573 hr_utility.trace('In final XML p_xml_string ');
1574 /* hr_utility.trace('XML String '||
1575 dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
1576 */
1577 l_final_xml_string :=
1578 '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL||
1579 '<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">'||EOL||
1580 '<xapi:request>'||EOL||
1581 '<xapi:delivery>'||EOL||
1582 '<xapi:filesystem output="'||p_output_location||'" />'||EOL||
1583 '</xapi:delivery>'||EOL||
1584 '<xapi:document output-type="pdf">'||EOL||
1585 -- '<xapi:template type="pdf" location="'||p_w2_template_location||'">'||EOL;
1586 '<xapi:template type="pdf" location="${templateName1}">'||EOL;
1587 -- '<xapi:template type="pdf" location="${templateName1}">'||EOL;
1588
1589
1590 hr_utility.trace('1. final 1. XML l_final_xml '||
1591 dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1592
1593 l_last_xml_string := '</xapi:template>'||EOL||
1594 -- '<xapi:template type="pdf" location="'||p_inst_template_location||'">'||EOL||
1595 '<xapi:template type="pdf" location="${templateName2}">'||EOL||
1596 -- '<xapi:template type="pdf" location="${templateName2}">'||EOL||
1597 '<xapi:data />'|| EOL||
1598 '</xapi:template>'||EOL||
1599 '</xapi:document>'||EOL||
1600 '</xapi:request>'||EOL||
1601 '</xapi:requestset>';
1602
1603 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1604 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1605
1606 IF l_is_temp_final_xml = 1 THEN
1607 DBMS_LOB.FREETEMPORARY(l_final_xml);
1608 END IF;
1609
1610 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1611 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1612 l_final_xml := append_to_lob(l_final_xml_string);
1613 --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1614
1615 hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
1616
1617 dbms_lob.append(l_final_xml,p_xml_blob);
1618
1619 --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
1620 dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
1621 dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
1622 l_temp_blob := append_to_lob(l_last_xml_string);
1623 dbms_lob.append(l_final_xml,l_temp_blob);
1624
1625 /* Added ISOPEN condition for bug 3899583 */
1626 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1627 hr_utility.trace('Closing l_final_xml' );
1628 dbms_lob.close(l_final_xml);
1629 END IF;
1630 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1631 hr_utility.trace('Closing p_xml_blob' );
1632 dbms_lob.close(p_xml_blob);
1633 END IF;
1634 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1635 hr_utility.trace('Closing l_temp_blob' );
1636 dbms_lob.close(l_temp_blob);
1637 END IF;
1638 ELSE
1639 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1640 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1641 l_final_xml := append_to_lob(p_error_msg);
1642
1643 hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
1644
1645 END IF ; /* p_error_msg is null */
1646 hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1647
1648 return l_final_xml;
1649 EXCEPTION
1650 WHEN OTHERS then
1651 /* Added ISOPEN condition for bug 3899583 */
1652 IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1653 hr_utility.trace('Raising exception and Closing l_final_xml' );
1654 dbms_lob.close(l_final_xml);
1655 END IF;
1656 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1657 hr_utility.trace('Raising exception and Closing p_xml_string' );
1658 dbms_lob.close(p_xml_blob);
1659 END IF;
1660 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1661 hr_utility.trace('Closing l_temp_blob' );
1662 dbms_lob.close(l_temp_blob);
1663 END IF;
1664
1665
1666 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1667 raise;
1668 END get_final_xml;
1669
1670
1671 PROCEDURE fetch_w2_xm IS
1672
1673 lc_emp_blob BLOB;
1674 l_error_msg VARCHAR2(200);
1675 l_assignment_action_id NUMBER;
1676 l_main_assignment_action_id NUMBER;
1677 l_tax_unit_id NUMBER;
1678 l_year NUMBER;
1679 l_final_xml BLOB;
1680 l_final_xml_string VARCHAR2(32767);
1681 l_last_xml_string VARCHAR2(32767);
1682 l_last_xml CLOB;
1683 l_is_temp_final_xml VARCHAR2(2);
1684 l_output_location VARCHAR2(100);
1685 l_instr_template VARCHAR2(100);
1686 EOL VARCHAR2(10);
1687 l_log VARCHAR2(100);
1688 buffer VARCHAR2(32767);
1689 amount NUMBER := 255;
1690 position VARCHAR2(1) :=1;
1691 l_temp_blob BLOB;
1692 text_size NUMBER;
1693 raw_data RAW(32767);
1694
1695
1696 CURSOR c_get_params IS
1697 SELECT paa1.assignment_action_id, -- archiver asg action
1698 paa.assignment_action_id,
1699 hr_us_w2_mt.get_parameter('GRE_ID',ppa.legislative_parameters),
1700 hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
1701 hr_us_w2_mt.get_parameter('p_instr_template',ppa.legislative_parameters),
1702 hr_us_w2_mt.get_parameter('print_instrunction',ppa.legislative_parameters)
1703 FROM pay_assignment_actions paa,
1704 pay_payroll_actions ppa,
1705 pay_assignment_actions paa1,
1706 pay_payroll_actions ppa1
1707 where ppa.payroll_action_id = paa.payroll_action_id
1708 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
1709 and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
1710 and paa.serial_number = paa1.assignment_action_id
1711 and paa1.payroll_action_id = ppa1.payroll_action_id
1712 and ppa1.report_type = 'YREND'
1713 and ppa1.action_type = 'X'
1714 and ppa1.action_status = 'C'
1715 and ppa1.effective_date = ppa.effective_date;
1716 BEGIN
1717 --hr_utility.trace_on(null,'w2');
1718 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1719 hr_utility.trace('In fetch_w2_xm');
1720
1721 hr_utility.trace('Deleting PL/SQL tables');
1722 l_state_local_tab.delete;
1723 l_state_tab.delete;
1724 l_local_tab.delete;
1725 l_box12_tab.delete;
1726 l_box14_tab.delete;
1727 l_w2_fields.amended := '';
1728 l_w2_fields.amended_date := '';
1729
1730 OPEN c_get_params;
1731 FETCH c_get_params INTO
1732 l_assignment_action_id, l_main_assignment_action_id,
1733 l_tax_unit_id, l_year,l_instr_template,g_print_instr;
1734 CLOSE c_get_params;
1735
1736 l_output_location := get_outfile;
1737
1738 hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
1739 hr_utility.trace('l_main_assignment_action_id ' ||l_main_assignment_action_id);
1740 hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
1741 hr_utility.trace('l_year ' ||l_year);
1742 hr_utility.trace('l_output_location ' ||l_output_location);
1743 hr_utility.trace('l_instr_template ' ||l_instr_template);
1744
1745
1746 lc_emp_blob := fetch_w2_xml(l_assignment_action_id,
1747 l_tax_unit_id,
1748 l_year,
1749 l_error_msg, false);
1750 hr_utility.trace('XML String is ');
1751 IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
1752
1753 --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
1754
1755 l_final_xml_string :=
1756 '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL ||
1757 '<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">'||EOL||
1758 '<xapi:request>'||EOL||
1759 '<xapi:delivery>'||EOL||
1760 '<xapi:filesystem output="'||l_output_location||'" />'||EOL||
1761 '</xapi:delivery>'||EOL||
1762 '<xapi:document output-type="pdf">'||EOL||
1763 '<xapi:template type="pdf" location="${templateName1}">'||EOL;
1764
1765 l_last_xml_string := '</xapi:template>'||EOL;
1766
1767 IF ( l_instr_template IS NOT null) THEN
1768 l_last_xml_string := l_last_xml_string||
1769 '<xapi:template type="pdf" location="${templateName2}">'||EOL||
1770 '<xapi:data />'|| EOL||
1771 '</xapi:template>'||EOL;
1772 END IF;
1773
1774 l_last_xml_string := l_last_xml_string ||
1775 '</xapi:document>'||EOL||
1776 '</xapi:request>'||EOL||
1777 '</xapi:requestset>'||EOL;
1778
1779
1780 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1781 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1782
1783 IF l_is_temp_final_xml = 1 THEN
1784 DBMS_LOB.FREETEMPORARY(l_final_xml);
1785 END IF;
1786
1787 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1788 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1789
1790 dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
1791 dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
1792
1793 raw_data:=utl_raw.cast_to_raw(l_final_xml_string);
1794 text_size:=utl_raw.length(raw_data);
1795
1796 -- dbms_lob.writeappend(l_final_xml,text_size,raw_data);
1797
1798 /*dbms_lob.writeappend(l_final_xml,
1799 utl_raw.length(utl_raw.cast_to_raw(l_final_xml_string)),
1800 utl_raw.cast_to_raw(l_final_xml_string)
1801 );*/
1802 l_temp_blob := append_to_lob(l_final_xml_string);
1803 dbms_lob.append(l_final_xml,l_temp_blob);
1804
1805 --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1806
1807 hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
1808
1809 dbms_lob.append(l_final_xml,lc_emp_blob);
1810
1811 raw_data:=utl_raw.cast_to_raw(l_last_xml_string);
1812 text_size:=utl_raw.length(raw_data);
1813
1814 --dbms_lob.writeappend(l_final_xml,text_size,raw_data);
1815
1816 /*dbms_lob.writeappend(l_final_xml,
1817 utl_raw.length(utl_raw.cast_to_raw(l_last_xml_string)),
1818 utl_raw.cast_to_raw(l_last_xml_string)
1819 );*/
1820
1821
1822 l_temp_blob := append_to_lob(l_last_xml_string);
1823 dbms_lob.append(l_final_xml,l_temp_blob);
1824 --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
1825
1826 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1827 hr_utility.trace('Closing l_final_xml' );
1828 dbms_lob.close(l_final_xml);
1829 END IF;
1830 IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1831 hr_utility.trace('Closing lc_emp_blob' );
1832 dbms_lob.close(lc_emp_blob);
1833 END IF;
1834 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1835 hr_utility.trace('Closing l_temp_blob' );
1836 dbms_lob.close(l_temp_blob);
1837 END IF;
1838
1839 hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
1840
1841 pay_core_files.write_to_magtape_lob(l_final_xml);
1842 -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1843
1844 END IF; /*dbms_lob.getlength(lc_emp_blob) >0*/
1845 -- l_is_temp_xml_string := dbms_lob.istemporary(pay_mag_tape.g_clob_value);
1846
1847 EXCEPTION
1848 WHEN OTHERS then
1849 /* Added ISOPEN condition for bug 3899583 */
1850 IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1851 hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
1852 dbms_lob.close(l_final_xml);
1853 END IF;
1854 IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
1855 hr_utility.trace('Raising exception and Closing p_xml_string' );
1856 dbms_lob.close(lc_emp_blob);
1857 END IF;
1858 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1859 hr_utility.trace('Closing l_temp_blob' );
1860 dbms_lob.close(l_temp_blob);
1861 END IF;
1862
1863 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1864 raise;
1865 END;
1866
1867 PROCEDURE get_footers IS
1868
1869 l_footer_xml CLOB;
1870 l_last_xml_string VARCHAR2(32000);
1871 l_is_temp_final_xml VARCHAR2(2);
1872 BEGIN
1873 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1874 l_last_xml_string :=
1875 '</EMPLOYEES>'||EOL;
1876 l_is_temp_final_xml := dbms_lob.istemporary(l_footer_xml);
1877 hr_utility.trace('Istemporary(l_footer_xml) ' ||l_is_temp_final_xml );
1878
1879 IF l_is_temp_final_xml = 1 THEN
1880 DBMS_LOB.FREETEMPORARY(l_footer_xml);
1881 END IF;
1882
1883 dbms_lob.createtemporary(l_footer_xml,false,DBMS_LOB.CALL);
1884 dbms_lob.open(l_footer_xml,dbms_lob.lob_readwrite);
1885 dbms_lob.writeappend(l_footer_xml,length(l_last_xml_string),l_last_xml_string);
1886
1887 hr_utility.trace('In Get footers,Length of length(l_footer_xml) ' ||dbms_lob.getlength(l_footer_xml));
1888
1889 --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
1890 pay_core_files.write_to_magtape_lob(l_last_xml_string);
1891 --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
1892
1893 --hr_utility.trace('In Get footers,Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1894 END;
1895
1896 PROCEDURE get_headers IS
1897
1898 l_final_xml CLOB;
1899 l_final_xml_string VARCHAR2(32000);
1900 l_is_temp_final_xml VARCHAR2(2);
1901 BEGIN
1902 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
1903 l_final_xml_string :=
1904 '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL||
1905 '<EMPLOYEES>'||EOL;
1906
1907 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1908 hr_utility.trace('Istemporary(l_final_xml) ' ||l_is_temp_final_xml );
1909
1910 IF l_is_temp_final_xml = 1 THEN
1911 DBMS_LOB.FREETEMPORARY(l_final_xml);
1912 END IF;
1913
1914 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1915 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1916 dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
1917 --dbms_lob.append(pay_mag_tape.g_clob_value,l_final_xml);
1918 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1919 --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_final_xml_string,dbms_lob.getlength(l_final_xml_string),1));
1920 --hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
1921 END;
1922
1923 function get_outfile return VARCHAR2 is
1924 TEMP_UTL varchar2(512);
1925 l_log varchar2(100);
1926 l_out varchar2(100);
1927 begin
1928 hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
1929
1930 if g_temp_dir is null then
1931 -- use first entry of utl_file_dir as the g_temp_dir
1932 select translate(ltrim(value),',',' ')
1933 into TEMP_UTL
1934 from v$parameter
1935 where name = 'utl_file_dir';
1936
1937 if (instr(TEMP_UTL,' ') > 0 and TEMP_UTL is not null) then
1938 select substrb(TEMP_UTL, 1, instr(TEMP_UTL,' ') - 1)
1939 into g_temp_dir
1940 from dual ;
1941 elsif (TEMP_UTL is not null) then
1942 g_temp_dir := TEMP_UTL;
1943 end if;
1944
1945 if (TEMP_UTL is null or g_temp_dir is null ) then
1946 raise no_data_found;
1947 end if;
1948 end if;
1949 hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
1950
1951 FND_FILE.get_names(l_log,l_out);
1952
1953 l_out := g_temp_dir ||'/'||l_out;
1954 hr_utility.trace('In get_out_file,l_out ' ||l_out );
1955
1956 return l_out;
1957
1958 exception
1959 when no_data_found then
1960 return null;
1961 when others then
1962 return null;
1963 end get_outfile;
1964 END PAY_US_W2_INFO_PKG;