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