[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W2C_RPT
Source
1 PACKAGE BODY pay_us_w2c_rpt AS
2 /* $Header: pyusw2cr.pkb 120.4.12020000.21 2013/05/24 07:12:35 skchalla ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_w2c_rpt
21
22 Description : This procedure is used by Employee W-2C Report
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ------ ------- --------------------------
28 10-AUG-2003 asasthan 115.0 Created.
29 10-AUG-2003 irgonzal 115.1 Modified get_payroll_action_info:
30 Removed to_number function for
31 PRINT parameter.
32
33 10-AUG-2003 asasthan 115.9 Added logic for Print 'ALL'.
34 19-FEB-2003 asasthan 115.10 changed get_prev curosors
35 added date effective join
36 25-OCT-2004 schauhan 115.11 3601799 Added selection criteria for "All"
37 if the report is Run with print
38 option "Reprint All W2c".
39 Made changes to w2crpt_range_cursor
40 and w2crpt_action_creation Cursor.
41 05-NOV-2004 schauhan 115.12 Added 'Distinct' to the Range Cursor
42 w2crpt_range_cursor.
43 22-NOV-2004 ahanda 115.13 3601799 Fixed issue in the bug. Changed the
44 action creation, range and sort
45 procedures.
46 16-DEC-2004 ahanda 115.14 4039440 Changed sort code to reduce length
47 to get around c-code issue.
48 14-MAR-2005 sackumar 115.15 4222032 Change in the Range Cursor removing redundant
49 use of bind Variable (:payroll_action_id)
50 04-AUG-2005 pragupta 115.16 3679317 Change in procedure sort_action. Removed
51 +0 from paf and hou for removing FTS and
52 performance improvement
53 14-MAR-2006 ppanda 115.17 4583575 To reduce the shared memory per_all_assignments_f
54 used instead of per_assignments_f.
55 31-MAR-2006 sodhingr 115.18 5111088 removed the comment from sort_cursor to fix signal
56 11 error.
57 05-MAR-2009 asgugupt 115.19 6349762 Adding Order by clause in Range Cursor
58 05-FEB-2013 skchalla 115.20 13599887 Added procedures/ffunction required to
59 generate the XML for W-2c
60 06-FEB-2013 skchalla 115.21 13599887 Added an escape sequence to resolve the compilation error.
61 Modified the CP parameter 'GRE_ID' to 'TRANSFER_GRE' to use the
62 same parameter for both W2-c paper and W2-c PDF
63 06-FEB-2013 skchalla 115.23 13599887 Added locking mechanism for W2-c PDF.
64 08-Feb-2013 skchalla 115.24 13599887 Removed invalid package references.
65 22-Feb-2013 skchalla 115.25 16000014 Removed the logical error in get_w2c_data
66 fuction while processing the Box 12
67 28-Feb-2013 skchalla 115.26 16391213 Modified the get_w2c_data function not to generate
68 extra W-2c copies.
69 01-Mar-2013 skchalla 115.27 16391171 Modified the get_w2c_data function.
70 01-Mar-2013 skchalla 115.28 16391171 Modified the get_w2c_data function for reporting the
71 NJ FLI balance.
72 04-Mar-2013 skchalla 115.29 16426068 Modified the get_w2c_data function.
73 06-Mar-2013 skchalla 115.30 16434271 Modified the get_w2c_data function and
74 create_xml_string function
75 07-Mar-2013 skchalla 115.31 16440238 Modified create_xml_string fuction.
76 18-Mar-2013 pkoduri 115.32 16434271 Corrected the date to be passed to fetch the State EIN.
77 this has to be YEPP arvhive date.
78 19-Mar-2013 skchalla 115.33 16398337 Modified the sort_action cursor.
79 20-Mar-201 skchalla 115.34 16398337 Modified the sort_action cursor.
80 04-Apr-2013 skchalla 115.35 16571508 Restricting the XML generation when there are no corrections.
81 10-Apr-2013 skchalla 115.36 16571508 Modified the code to display '-0-' instead of null when
82 any monetary field being changed either from or to a zero.
83 23-May-2013 skchalla 115.37 16815610 Modified the get_w2c_data function to remove the extra spaces from the values.
84 24-May-2013 skchalla 115.38 16815610 Replaced to_number funtion with the trim function to get the decimal places always
85 *****************************************************************************/
86
87 gv_procedure_name VARCHAR2(100);
88 gv_package VARCHAR2(100);
89 g_package CONSTANT VARCHAR2(33) := 'pay_us_w2c_rpt.';
90 l_w2c_fields_o l_w2c_fields_rec;
91 l_state_tab l_state_table;
92 l_local_tab l_local_table;
93 l_box12_tab l_box12_table;
94 l_box14_tab l_box14_table;
95 l_state_local_tab l_state_local_table;
96
97 l_w2c_fields_c l_w2c_fields_rec;
98
99
100 g_occ_tax_rate NUMBER;
101 g_mh_tax_rate NUMBER;
102 g_mh_tax_limit NUMBER;
103 g_occ_mh_tax_limit NUMBER;
104 g_occ_mh_wage_limit NUMBER;
105 g_mh_tax_wage_limit NUMBER;
106 g_print_instr VARCHAR2(1) := 'Y';
107
108
109 /*****************************************************************************
110 Name : get_payroll_action_info
111 Purpose : This returns the Payroll Action level
112 information for W-2C PAPER.
113 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
114 p_start_date - Start date of W-2C PAPER
115 p_end_date - End date of W-2C PAPER
116 p_business_group_id - Business Group ID
117 *****************************************************************************/
118 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
119 ,p_end_date out nocopy date
120 ,p_start_date out nocopy date
121 ,p_business_group_id out nocopy number
122 ,p_tax_unit_id out nocopy number
123 ,p_person_id out nocopy number
124 ,p_asg_set out nocopy number
125 ,p_print out nocopy varchar2
126 ,p_sort_option1 out nocopy varchar2
127 ,p_sort_option2 out nocopy varchar2
128 ,p_sort_option3 out nocopy varchar2
129 ,p_session_date out nocopy date
130 )
131 IS
132 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
133 select
134 pay_us_payroll_utils.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
135 pay_us_payroll_utils.get_parameter('PER_ID',ppa.legislative_parameters),
136 pay_us_payroll_utils.get_parameter('ASG_SET',ppa.legislative_parameters),
137 pay_us_payroll_utils.get_parameter('PRINT',ppa.legislative_parameters),
138 effective_date,
139 start_date,
140 business_group_id,
141 pay_us_payroll_utils.get_parameter('S1',ppa.legislative_parameters),
142 pay_us_payroll_utils.get_parameter('S2',ppa.legislative_parameters),
143 pay_us_payroll_utils.get_parameter('S3',ppa.legislative_parameters),
144 to_date(pay_us_payroll_utils.get_parameter('EFFECTIVE_DATE',
145 ppa.legislative_parameters)
146 ,'dd-mon-yyyy')
147 from pay_payroll_actions ppa
148 where ppa.payroll_action_id = cp_payroll_action_id;
149
150 ld_end_date DATE;
151 ld_start_date DATE;
152 ln_business_group_id NUMBER;
153 ln_tax_unit_id NUMBER;
154 ln_person_id NUMBER;
155 ln_asg_set NUMBER;
156 lv_print VARCHAR2(60);
157 lv_sort1 VARCHAR2(60);
158 lv_sort2 VARCHAR2(60);
159 lv_sort3 VARCHAR2(60);
160 ld_session_date DATE;
161
162 BEGIN
163 hr_utility.trace('Entered get_payroll_action_info');
164 ln_tax_unit_id := 0;
165 ln_person_id := 0;
166 ln_asg_set := 0;
167 open c_payroll_action_info(p_payroll_action_id);
168 fetch c_payroll_action_info into ln_tax_unit_id,
169 ln_person_id,
170 ln_asg_set,
171 lv_print,
172 ld_end_date,
173 ld_start_date,
174 ln_business_group_id,
175 lv_sort1,
176 lv_sort2,
177 lv_sort3,
178 ld_session_date;
179 close c_payroll_action_info;
180
181 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
182 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
183 hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
184 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
185 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
186
187 p_end_date := ld_end_date;
188 p_start_date := ld_start_date;
189 p_business_group_id := ln_business_group_id;
190 p_tax_unit_id := ln_tax_unit_id;
191 p_person_id := ln_person_id;
192 p_asg_set := ln_asg_set;
193 p_print := lv_print;
194 p_sort_option1 := lv_sort1;
195 p_sort_option2 := lv_sort2;
196 p_sort_option3 := lv_sort3;
197 p_session_date := ld_session_date;
198
199 hr_utility.trace('Leaving get_payroll_action_info');
200
201 EXCEPTION
202 when others then
203 hr_utility.trace('Error in ' || gv_procedure_name ||
204 to_char(sqlcode) || '-' || sqlerrm);
205 raise hr_utility.hr_error;
206
207 END get_payroll_action_info;
208
209 /******************************************************************
210 Name : w2crpt_range_cursor
211 Purpose : This returns the select statement that is
212 used to created the range rows for the
213 W-2C PAPER.
214 Arguments :
215 Notes : Calls procedure - get_payroll_action_info
216 ******************************************************************/
217 PROCEDURE w2crpt_range_cursor(
218 p_payroll_action_id in number
219 ,p_sqlstr out nocopy varchar2)
220 IS
221 ld_end_date DATE;
222 ld_start_date DATE;
223 ln_business_group_id NUMBER;
224 ln_tax_unit_id NUMBER;
225 ln_person_id NUMBER;
226 ln_asg_set NUMBER;
227 lv_sort1 VARCHAR2(60);
228 lv_sort2 VARCHAR2(60);
229 lv_sort3 VARCHAR2(60);
230 ld_session_date DATE;
231
232 lv_sql_string VARCHAR2(32000);
233 ln_eoy_pactid number;
234 lv_print varchar2(10);
235 lv_error_mesg varchar2(100);
236 ln_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
237 ln_year number;
238
239 BEGIN
240 hr_utility.trace('Entered w2crpt_range_cursor');
241 ln_person_id := 0;
242 ln_asg_set := 0;
243 lv_print := null;
244 hr_utility.trace('p_payroll_action_id = ' ||
245 to_char(p_payroll_action_id));
246
247 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
248 ,p_start_date => ld_start_date
249 ,p_end_date => ld_end_date
250 ,p_business_group_id => ln_business_group_id
251 ,p_tax_unit_id => ln_tax_unit_id
252 ,p_person_id => ln_person_id
253 ,p_asg_set => ln_asg_set
254 ,p_print => lv_print
255 ,p_sort_option1 => lv_sort1
256 ,p_sort_option2 => lv_sort2
257 ,p_sort_option3 => lv_sort3
258 ,p_session_date => ld_session_date);
259
260 -- Bug 3601799 - Added condition.
261 IF ln_person_id IS NOT NULL OR ln_asg_set IS NOT NULL THEN
262 ln_year := to_number(to_char(ld_end_date,'YYYY'));
263
264 hr_utility.trace('Checking for Preprocess Agent GRE setup');
265 hr_us_w2_rep.get_agent_tax_unit_id(ln_business_group_id
266 ,ln_year
267 ,ln_agent_tax_unit_id
268 ,lv_error_mesg ) ;
269
270 if lv_error_mesg is not null then
271 if substr(lv_error_mesg,1,45) is not null then
272 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
273 pay_core_utils.push_token('record_name',' ');
274 pay_core_utils.push_token('description',substr(lv_error_mesg,1,45));
275 end if;
276
277 if substr(lv_error_mesg,46,45) is not null then
278 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
279 pay_core_utils.push_token('record_name',' ');
280 pay_core_utils.push_token('description',substr(lv_error_mesg,46,45));
281 end if;
282
283 if substr(lv_error_mesg,91,45) is not null then
284 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
285 pay_core_utils.push_token('record_name',' ');
286 pay_core_utils.push_token('description',substr(lv_error_mesg,91,45));
287 end if;
288
289 if substr(lv_error_mesg,136,45) is not null then
290 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
291 pay_core_utils.push_token('record_name',' ');
292 pay_core_utils.push_token('description',substr(lv_error_mesg,136,45));
293 end if;
294
295 hr_utility.raise_error;
296
297 end if;
298
299
300 if ln_person_id is not null then
301
302 lv_sql_string :=
303 'select distinct asg.person_id person_id
304 from per_all_assignments_f asg
305 where person_id = ' || ln_person_id ||
306 ' and :p_payroll_action_id is not null ';
307
308 hr_utility.trace('Range for person_id not null');
309
310 elsif ln_asg_set is not null then
311
312 lv_sql_string :=
313 'select distinct paf.person_id
314 from hr_assignment_set_amendments asgset,
315 per_all_assignments_f paf
316 where assignment_set_id = ' || ln_asg_set || '
317 and asgset.assignment_id = paf.assignment_id
318 and asgset.include_or_exclude = ''I''
319 and :payroll_action_id is not null order by paf.person_id';
320
321 hr_utility.trace('Range for asg_set not null');
322 end if;
323
324 -- Bug 3601799
325 -- This query string will be executed when for All parameter is passed.
326 ELSE
327 lv_sql_string :=
328 'select distinct paa.serial_number
329 from pay_assignment_actions paa,
330 pay_payroll_actions ppa
331 where ppa.report_type = ''W2C_PRE_PROCESS''
332 and ppa.effective_date = add_months(''' || ld_start_date || ''',12) -1
333 and ppa.business_group_id+0 = ' || ln_business_group_id ||'
334 and ltrim(rtrim(
335 substr(ppa.legislative_parameters,
336 instr(ppa.legislative_parameters,''TRANSFER_GRE='')
337 + length(''TRANSFER_GRE='')
338 ,instr(ppa.legislative_parameters,'' '',2))))
339 = to_char(' || ln_tax_unit_id || ')
340 and paa.payroll_action_id = ppa.payroll_action_id
341 and paa.action_status = ''C''
342 and paa.tax_unit_id = ' || ln_tax_unit_id || '
343 and :payroll_action_id is not null
344 order by paa.serial_number';
345
346 hr_utility.trace('Range for all the persons.');
347 END IF;
348
349 p_sqlstr := lv_sql_string;
350 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
351 hr_utility.trace('Leaving w2crpt_range_cursor');
352 END w2crpt_range_cursor;
353
354
355 /************************************************************
356 Name : w2crpt_action_creation
357 Purpose : This creates the assignment actions for
358 a specific chunk of people to be archived
359 by the W2C Report process.
360 Arguments :
361 Notes : Calls procedure - get_payroll_action_info
362 ************************************************************/
363 PROCEDURE w2crpt_action_creation(
364 p_payroll_action_id in number
365 ,p_start_person_id in number
366 ,p_end_person_id in number
367 ,p_chunk in number)
368
369 IS
370
371 ln_assignment_id NUMBER;
372 ln_tax_unit_id NUMBER;
373 ld_effective_date DATE ;
374 ln_asg_action_id NUMBER;
375 ln_primary_assignment_id NUMBER;
376 ln_yepp_aaid NUMBER;
377 ln_payroll_action_id NUMBER;
378 ln_w2c_asg_action NUMBER;
379 lv_year VARCHAR2(4);
380
381 ld_end_date DATE;
382 ld_start_date DATE;
383 ln_business_group_id NUMBER;
384 ln_person_id NUMBER;
385 ln_set_person_id NUMBER;
386 ln_asg_set NUMBER;
387 lv_print varchar2(10);
388 lv_sort1 VARCHAR2(60);
389 lv_sort2 VARCHAR2(60);
390 lv_sort3 VARCHAR2(60);
391 ld_session_date DATE;
392
393 lv_report_type pay_payroll_actions.report_type%TYPE ;
394 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
395 ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
396 ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
397
398
399 lv_serial_number VARCHAR2(30);
400 lv_employee_number per_all_people_f.employee_number%type;
401 lv_message varchar2(50);
402 lv_full_name per_all_people_f.full_name%type;
403 lv_record_name varchar2(50);
404 lv_prev_report_type pay_payroll_actions.report_type%TYPE;
405 ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
406 ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
407 ln_serial_number pay_assignment_actions.serial_number%TYPE;
408
409 CURSOR c_selected_asg_set(cp_start_person in number
410 ,cp_end_person in number
411 ,cp_asg_set in number) is
412 select distinct paf.person_id
413 from hr_assignment_set_amendments asgset,
414 per_all_assignments_f paf
415 where assignment_set_id = cp_asg_set
416 and asgset.include_or_exclude = 'I'
417 and paf.assignment_id = asgset.assignment_id
418 and paf.person_id between cp_start_person
419 and cp_end_person;
420
421 -- Bug 3601799
422 -- This Cursor is opened when report is run for All persons.
423 -- This will only happen for Re-prints
424 CURSOR c_select_all_person(cp_start_person in number,
425 cp_end_person in number,
426 cp_start_date in date,
427 cp_business_group_id in number,
428 cp_tax_unit_id in number) IS
429 select distinct paf.person_id
430 from pay_assignment_actions paa,
431 pay_payroll_actions ppa,
432 per_all_assignments_f paf
433 where ppa.report_type = 'W-2C PAPER'
434 and ppa.report_category = 'REPORT'
435 and ppa.report_qualifier = 'DEFAULT'
436 and ppa.effective_date = add_months(cp_start_date,12) -1
437 and ppa.business_group_id = cp_business_group_id
438 and ppa.legislative_parameters like '%' || cp_tax_unit_id || '%'
439 and paa.payroll_action_id = ppa.payroll_action_id
440 and paa.action_status = 'C'
441 and paa.tax_unit_id = cp_tax_unit_id
442 and paf.assignment_id = paa.assignment_id
443 and paf.effective_end_date =
444 (SELECT max(paf1.effective_end_date)
445 FROM per_all_assignments_f paf1
446 WHERE paf1.assignment_id = paf.assignment_id
447 AND paf1.effective_start_date <= ppa.effective_date)
448 and paf.person_id between cp_start_person and cp_end_person;
449
450
451 PROCEDURE action_creation (p_person_id in NUMBER)
452 IS
453
454 CURSOR get_prev_w2c_dtls (cp_person_id in number
455 ,cp_tax_unit_id in number
456 ,cp_effective_date in date
457 ,cp_start_date in date) is
458 select ppa.report_type, paa.assignment_id,
459 paa.assignment_action_id
460 from pay_payroll_actions ppa,
461 pay_assignment_actions paa,
462 per_all_assignments_f paf
463 where paa.assignment_id = paf.assignment_id
464 and paf.person_id = cp_person_id
465 and paf.effective_start_date <= cp_effective_date
466 and paf.effective_end_date >= cp_start_date
467 and paa.tax_unit_id = cp_tax_unit_id
468 and paa.action_status = 'C'
469 and ppa.payroll_action_id = paa.payroll_action_id
470 and ppa.effective_date = cp_effective_date
471 and ppa.report_type in ('W2C_PRE_PROCESS','W2C_XML','W-2C PAPER')
472 and paf.effective_end_date =
473 (SELECT max(paf1.effective_end_date)
474 FROM per_all_assignments_f paf1
475 WHERE paf1.assignment_id = paf.assignment_id
476 AND paf1.effective_start_date <= ppa.effective_date)
477 order by paa.assignment_action_id desc;
478
479 CURSOR get_prev_w2c_reprint_dtls (cp_person_id in number
480 ,cp_tax_unit_id in number
481 ,cp_effective_date in date
482 ,cp_start_date in date
483 ,cp_business_group_id in number) is
484 select ppa.report_type, paa.assignment_id,
485 paa.assignment_action_id
486 from pay_payroll_actions ppa,
487 pay_assignment_actions paa,
488 per_all_assignments_f paf
489 where paa.assignment_id = paf.assignment_id
490 and paf.person_id = cp_person_id
491 and paf.effective_start_date <= cp_effective_date
492 and paf.effective_end_date >= cp_start_date
493 and paa.tax_unit_id = cp_tax_unit_id
494 and paa.action_status = 'C'
495 and ppa.payroll_action_id = paa.payroll_action_id
496 and ppa.effective_date = cp_effective_date
497 and ppa.report_type = 'W2C_PRE_PROCESS'
498 and ppa.report_category = 'RT'
499 and ppa.report_qualifier = 'FED'
500 and ppa.business_group_id = cp_business_group_id
501 and paf.effective_end_date =
502 (SELECT max(paf1.effective_end_date)
503 FROM per_all_assignments_f paf1
504 WHERE paf1.assignment_id = paf.assignment_id
505 AND paf1.effective_start_date <= ppa.effective_date)
506 and exists (select 1
507 from pay_action_interlocks pai,
508 pay_assignment_actions paa_paper,
509 pay_payroll_Actions ppa_paper
510 where pai.locked_action_id = paa.assignment_action_id
511 and paa_paper.assignment_Action_id = pai.locking_action_id
512 and ppa_paper.payroll_Action_id = paa_paper.payroll_Action_id
513 and ppa_paper.report_type in ('W-2C PAPER','W2C_XML')
514 and ppa_paper.report_category = 'REPORT'
515 and ppa_paper.report_qualifier = 'DEFAULT'
516 and ppa_paper.effective_date = cp_effective_date
517 and ppa_paper.business_group_id = cp_business_group_id)
518 order by paa.assignment_action_id desc;
519
520 CURSOR get_interlocked_action(cp_w2cpp_action in number)is
521 select ppa.report_type,
522 paa.assignment_action_id,
523 substr(paa.serial_number, 1,15) prev_action_id
524 from pay_payroll_actions ppa,
525 pay_assignment_actions paa,
526 pay_action_interlocks pai
527 where pai.locking_action_id = cp_w2cpp_action
528 and paa.assignment_action_id = pai.locked_action_id
529 and ppa.payroll_action_id = paa.payroll_action_id;
530
531
532 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
533 select substr(full_name,1,48), employee_number
534 from per_all_people_f
535 where person_id = cp_person_id
536 order by effective_end_date desc;
537
538 BEGIN
539 if lv_print = 'NEW' then
540 open get_prev_w2c_dtls(p_person_id
541 ,ln_tax_unit_id
542 ,ld_end_date
543 ,ld_start_date);
544
545 hr_utility.trace('value of p_person_id is:'||p_person_id);
546 hr_utility.trace('value of ln_tax_unit_id is:'||ln_tax_unit_id);
547 hr_utility.trace('value of ld_end_date is:'||ld_end_date);
548 hr_utility.trace('value of ld_start_date is:'||ld_start_date);
549
550 fetch get_prev_w2c_dtls into lv_report_type
551 ,ln_primary_assignment_id
552 ,ln_asg_act_to_lock;
553 if get_prev_w2c_dtls%notfound then
554 open get_warning_dtls_for_ee(p_person_id);
555 fetch get_warning_dtls_for_ee into lv_full_name
556 ,lv_employee_number;
557 close get_warning_dtls_for_ee;
558
559 lv_record_name := 'W2C Report';
560 lv_message := 'No W2c archive actions exist for this employee';
561
562 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
563 pay_core_utils.push_token('record_name',lv_record_name);
564 pay_core_utils.push_token('name_or_number',lv_full_name);
565 pay_core_utils.push_token('description',lv_message);
566 end if;
567
568 if get_prev_w2c_dtls%found then
569
570 if lv_report_type in ('W2C_PRE_PROCESS') then
571 /* Create an assignment action for this person */
572 select pay_assignment_actions_s.nextval
573 into ln_w2c_asg_action
574 from dual;
575 hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
576
577 /* Insert into pay_assignment_actions. */
578 hr_nonrun_asact.insact(ln_w2c_asg_action
579 ,ln_primary_assignment_id
580 ,p_payroll_action_id
581 ,p_chunk
582 ,ln_tax_unit_id);
583
584 /**********************************************************
585 ** Get the second last archive action for this employee
586 ** The First W2C_PRE_PROCESS locks YREND
587 ** but the subsequent W2C_PRE_PROCESS will lock
588 ** the W-2C PAPER process
589 ***********************************************************/
590 open get_interlocked_action(ln_asg_act_to_lock);
591 fetch get_interlocked_action into lv_prev_report_type
592 ,ln_prev_yepp_lock_action
593 ,ln_prev_w2c_action_id;
594 if get_interlocked_action%notfound then
595
596 close get_interlocked_action;
597 hr_utility.raise_error;
598 end if;
599 close get_interlocked_action;
600
601 if lv_prev_report_type = 'YREND' then
602 ln_second_last_arch_action := ln_prev_yepp_lock_action;
603 elsif lv_prev_report_type = 'W-2C PAPER' OR lv_prev_report_type ='W2C_XML'then
604 ln_second_last_arch_action := ln_prev_w2c_action_id;
605 end if;
606
607 /***************************************************************
608 ** Update the serial number column with the assignment action
609 ** of the last two archive processes
610 ***************************************************************/
611 ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
612 lpad(ln_second_last_arch_action,15,0);
613
614 update pay_assignment_actions aa
615 set aa.serial_number = ln_serial_number
616 where aa.assignment_action_id = ln_w2c_asg_action;
617
618 /***************************************************************
619 ** Interlock last w2c archive action with current w2c rep action
620 ***************************************************************/
621 hr_utility.trace('Locking Action'||ln_w2c_asg_action);
622 hr_utility.trace('Locked Action' || ln_asg_act_to_lock);
623 hr_nonrun_asact.insint(ln_w2c_asg_action
624 ,ln_asg_act_to_lock);
625
626 elsif lv_report_type = 'W-2C PAPER' OR lv_report_type = 'W2C_XML' then
627
628 open get_warning_dtls_for_ee(p_person_id);
629 fetch get_warning_dtls_for_ee into lv_full_name
630 ,lv_employee_number;
631
632 close get_warning_dtls_for_ee;
633
634 lv_record_name := 'W2C Report';
635 lv_message := 'No new w2c archive actions exist '||
636 'for this employee';
637
638 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
639 pay_core_utils.push_token('record_name',lv_record_name);
640 pay_core_utils.push_token('name_or_number',lv_full_name);
641 pay_core_utils.push_token('description',lv_message);
642
643 end if; /* report type */
644
645 end if; /* employee found*/
646 close get_prev_w2c_dtls;
647
648 elsif lv_print = 'ALL' then
649
650 open get_prev_w2c_reprint_dtls(p_person_id
651 ,ln_tax_unit_id
652 ,ld_end_date
653 ,ld_start_date
654 ,ln_business_group_id);
655 lv_report_type := null;
656 ln_primary_assignment_id := 0;
657 ln_asg_act_to_lock := 0;
658
659 loop
660 fetch get_prev_w2c_reprint_dtls into lv_report_type
661 ,ln_primary_assignment_id
662 ,ln_asg_act_to_lock;
663 if get_prev_w2c_reprint_dtls%notfound then
664
665 exit;
666 end if;
667
668 /* Create an assignment action for this person */
669 select pay_assignment_actions_s.nextval
670 into ln_w2c_asg_action
671 from dual;
672 hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
673
674 /* Insert into pay_assignment_actions. */
675 hr_nonrun_asact.insact(ln_w2c_asg_action
676 ,ln_primary_assignment_id
677 ,p_payroll_action_id
678 ,p_chunk
679 ,ln_tax_unit_id);
680
681 /**********************************************************
682 ** Get the second last archive action for this employee
683 ** The First W2C_PRE_PROCESS locks YREND
684 ** but the subsequent W2C_PRE_PROCESS will lock
685 ** the W-2C PAPER process
686 ***********************************************************/
687 open get_interlocked_action(ln_asg_act_to_lock);
688 fetch get_interlocked_action into lv_prev_report_type
689 ,ln_prev_yepp_lock_action
690 ,ln_prev_w2c_action_id;
691 if get_interlocked_action%notfound then
692 close get_interlocked_action;
693 hr_utility.raise_error;
694 end if;
695 close get_interlocked_action;
696
697 if lv_prev_report_type = 'YREND' then
698 ln_second_last_arch_action := ln_prev_yepp_lock_action;
699 elsif lv_prev_report_type = 'W-2C PAPER' OR lv_prev_report_type = 'W2C_XML'then
700 ln_second_last_arch_action := ln_prev_w2c_action_id;
701 end if;
702
703 /***************************************************************
704 ** Update the serial number column with the assignment action
705 ** of the last two archive processes
706 ***************************************************************/
707 ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
708 lpad(ln_second_last_arch_action,15,0);
709
710 update pay_assignment_actions aa
711 set aa.serial_number = ln_serial_number
712 where aa.assignment_action_id = ln_w2c_asg_action;
713
714 end loop;
715 close get_prev_w2c_reprint_dtls;
716
717 end if; /* NEW */
718
719 END action_creation;
720
721 BEGIN
722 hr_utility.trace('Entered action_creation ');
723 ln_assignment_id := 0;
724 ln_tax_unit_id := 0;
725 ln_asg_action_id := 0;
726 ln_primary_assignment_id := 0;
727 ln_yepp_aaid := 0;
728 ln_payroll_action_id := 0;
729 ln_w2c_asg_action := 0;
730 lv_year := 0;
731
732 ln_person_id := 0 ;
733 ln_set_person_id := 0 ;
734 ln_asg_set := 0 ;
735 lv_message := null;
736
737 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
738 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
739 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
740 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
741
742 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
743 ,p_start_date => ld_start_date
744 ,p_end_date => ld_end_date
745 ,p_business_group_id => ln_business_group_id
746 ,p_tax_unit_id => ln_tax_unit_id
747 ,p_person_id => ln_person_id
748 ,p_asg_set => ln_asg_set
749 ,p_print => lv_print
750 ,p_sort_option1 => lv_sort1
751 ,p_sort_option2 => lv_sort2
752 ,p_sort_option3 => lv_sort3
753 ,p_session_date => ld_session_date);
754
755 /* PERSON ID IS NOT NULL */
756 if ln_person_id is not null then
757 action_creation(p_start_person_id);
758
759 /* ASSIGNMENT SET ID IS NOT NULL */
760 elsif ln_asg_set is not null then
761 hr_utility.trace('Entered Asg Set logic');
762 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
763 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
764 hr_utility.trace('End Person ='||to_char(p_end_person_id));
765
766 open c_selected_asg_set(p_start_person_id
767 ,p_end_person_id
768 ,ln_asg_set);
769 hr_utility.trace('Opened cusor c_selected_asg_set');
770 loop
771 fetch c_selected_asg_set into ln_set_person_id;
772 if c_selected_asg_set%notfound then
773 hr_utility.trace('No Person found for reporting in this chunk');
774 exit;
775 end if;
776
777 action_creation(ln_set_person_id);
778
779 end loop;
780 close c_selected_asg_set;
781
782 -- Bug 3601799 -- Added this elsif if the report is run for All.
783 /* PERSON ID and ASSIGNMENT SET ID are NULL */
784 elsif ln_person_id is null and ln_asg_set is null then
785 hr_utility.trace('Report run for All persons Logic.');
786 open c_select_all_person(p_start_person_id
787 ,p_end_person_id
788 ,ld_start_date
789 ,ln_business_group_id
790 ,ln_tax_unit_id);
791 hr_utility.trace('Opened cusor c_select_all_person');
792 loop
793 fetch c_select_all_person into ln_person_id;
794 if c_select_all_person%notfound then
795 hr_utility.trace('No Person found for reporting in this chunk.');
796 exit;
797 end if;
798
799 action_creation(ln_person_id);
800 end loop;
801 close c_select_all_person;
802 end if; /* ln_person_id */
803
804 END w2crpt_action_creation;
805
806
807 /************************************************************
808 Name : sort_action
809 Purpose : This sorts the assignment actions based on the
810 sort options given when submitting the W2C Report
811 Arguments :
812 Notes : Calls procedure - get_payroll_action_info
813 ************************************************************/
814 PROCEDURE sort_action(p_payroll_action_id in varchar2
815 ,p_sql_string in out nocopy varchar2
816 ,p_sql_length out nocopy number)
817 IS
818
819 ld_end_date DATE;
820 ld_start_date DATE;
821 ln_business_group_id NUMBER;
822 ln_person_id NUMBER;
823 ln_set_person_id NUMBER;
824 ln_asg_set NUMBER;
825 ln_tax_unit_id NUMBER;
826 lv_print VARCHAR2(10);
827 lv_sort1 VARCHAR2(60);
828 lv_sort2 VARCHAR2(60);
829 lv_sort3 VARCHAR2(60);
830 ld_session_date DATE;
831 l_year number;
832
833 BEGIN
834
835 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
836 ,p_start_date => ld_start_date
837 ,p_end_date => ld_end_date
838 ,p_business_group_id => ln_business_group_id
839 ,p_tax_unit_id => ln_tax_unit_id
840 ,p_person_id => ln_person_id
841 ,p_asg_set => ln_asg_set
842 ,p_print => lv_print
843 ,p_sort_option1 => lv_sort1
844 ,p_sort_option2 => lv_sort2
845 ,p_sort_option3 => lv_sort3
846 ,p_session_date => ld_session_date
847 );
848
849 if ld_end_date > ld_session_date then
850 ld_session_date := ld_end_date;
851 end if;
852
853 select to_char(ld_end_date,'YYYY') into l_year from dual;
854
855 hr_utility.trace('Beginning of the sort_action cursor');
856 p_sql_string :=
857 'select mt.rowid
858 from hr_organization_units hou, hr_locations_all hl,
859 per_periods_of_service pps, per_all_assignments_f paf,
860 pay_assignment_actions mt
861 where mt.payroll_action_id = :p_payroll_action_id
862 and paf.assignment_id = mt.assignment_id -- Bug 3679317 ( +0 removed)
863 and paf.effective_start_date = (select max(paf2.effective_start_date)
864 from per_all_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
865 and paf2.effective_start_date <= to_date(''31-DEC-''||'''||l_year||''',''DD-MM-YYYY''))
866 and paf.effective_end_date >= to_date(''01-JAN-''||'''||l_year||''',''DD-MM-YYYY'')
867 and paf.assignment_type = ''E'' and hou.organization_id = paf.organization_id
868 and pps.period_of_service_id = paf.period_of_service_id
869 and pps.person_id = paf.person_id and hl.location_id = paf.location_id
870 and hou.business_group_id = '''|| ln_business_group_id ||'''
871 order by decode('''||lv_sort1||''', ''Employee_Name'',
872 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
873 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
874 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
875 ''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''),
876 ''Applied For''),
877 ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''|| ld_session_date ||'''),
878 ''Organization'',hou.name, ''Location'',hl.location_code,
879 ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
880 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
881 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
882 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
883 decode('''||lv_sort2||''', ''Employee_Name'',
884 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
885 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
886 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
887 ''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
888 ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''|| ld_session_date ||'''),
889 ''Organization'',hou.name, ''Location'',hl.location_code,
890 ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
891 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
892 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
893 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
894 decode('''||lv_sort3||''', ''Employee_Name'', hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)),
895 ''A_PER_LAST_NAME'')||'' ''
896 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
897 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
898 ''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''),
899 ''Applied For''), ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''|| ld_session_date ||'''),
900 ''Organization'',hou.name, ''Location'',hl.location_code, ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',
901 hr_us_w2_rep.get_leav_reason(leaving_reason)),
902 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
903 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
904 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1))';
905
906 p_sql_length := length(p_sql_string); -- return the length of the string.
907 hr_utility.trace('End of the sort_Action cursor');
908 END sort_action;
909
910 FUNCTION append_to_lob(p_text in varchar)
911 RETURN BLOB IS
912
913 text_size NUMBER;
914 raw_data RAW(32767);
915 temp_blob BLOB;
916 BEGIN
917
918 raw_data:=utl_raw.cast_to_raw(p_text);
919 text_size:=utl_raw.length(raw_data);
920
921 dbms_lob.createtemporary(temp_blob,false,DBMS_LOB.CALL);
922 dbms_lob.open(temp_blob,dbms_lob.lob_readwrite);
923
924 dbms_lob.writeappend(temp_blob,
925 text_size,
926 raw_data
927 );
928
929 IF dbms_lob.ISOPEN(temp_blob)=1 THEN
930 hr_utility.trace('Closing temp_lob' );
931 dbms_lob.close(temp_blob);
932 END IF;
933
934 return temp_blob;
935 END;
936
937 FUNCTION check_negative_number (p_data varchar2)
938 RETURN VARCHAR2 IS
939 l_data VARCHAR2(250);
940 BEGIN
941
942 IF p_data = '-0-' then
943 l_data := p_data;
944 elsif nvl(p_data,0) <=0 THEN
945 hr_utility.trace('Negative/zero value '||p_data);
946 l_data := '';
947 ELSE
948 l_data := p_data;
949 END IF;
950
951 return l_data;
952
953 END;
954
955 FUNCTION xml_special_chars (p_xml_data VARCHAR2)
956 RETURN VARCHAR2 IS
957 l_xml_data VARCHAR2(250);
958 BEGIN
959 l_xml_data := REPLACE (p_xml_data, '&', '&');
960 l_xml_data := REPLACE (l_xml_data, '>', '>');
961 l_xml_data := REPLACE (l_xml_data, '<', '<');
962 l_xml_data := REPLACE (l_xml_data, '''', ''');
963 l_xml_data := REPLACE (l_xml_data, '"', '"');
964
965 return l_xml_data;
966
967 END;
968
969 FUNCTION populate_state_local_table ( l_state_tab l_state_table,
970 l_local_tab l_local_table)
971 RETURN l_state_local_table IS
972 l_curr_state PLS_INTEGER;
973 l_curr_local PLS_INTEGER;
974 l_stloc_tcnt NUMBER;
975 p_write_state BOOLEAN;
976 l_prior_local PLS_INTEGER;
977
978
979 PROCEDURE check_prior_local IS
980 BEGIN
981 --{ Check for prior local
982 hr_utility.trace('In check_prior_local,l_prior_local '||l_prior_local);
983 p_write_state := FALSE;
984 IF l_prior_local IS NOT NULL THEN
985
986 --hr_utility.trace('Statecode of state is LESS than Local state Code and prior local is not null');
987 hr_utility.trace('State Code of current state '||l_state_tab(l_curr_state).state_code_c);
988 -- hr_utility.trace('State Code of current local '||l_local_tab(l_curr_local).state_code);
989 hr_utility.trace('State Code of prior local '||l_local_tab(l_prior_local).state_code_c);
990
991 /* If the state code of prior local is same as current state
992 then move the index of the current state */
993 IF (l_state_tab(l_curr_state).state_code_c <>
994 l_local_tab(l_prior_local).state_code_c) THEN
995 hr_utility.trace('State Code of prior local matches current state code so setting p_write_state TRUE');
996 p_write_state := TRUE;
997 ELSE
998 p_write_state := FALSE;
999 END IF;
1000 ELSE
1001 p_write_state := TRUE;
1002 END IF;
1003 --}
1004 END;
1005
1006 PROCEDURE write_state_only IS
1007 BEGIN
1008
1009 hr_utility.trace('Writing state without local ');
1010
1011 l_stloc_tcnt := l_state_local_tab.count;
1012 l_state_local_tab(l_stloc_tcnt).state_code_c
1013 := l_state_tab(l_curr_state).state_code_c ;
1014 l_state_local_tab(l_stloc_tcnt).state_ein_c
1015 := l_state_tab(l_curr_state).state_ein_c ;
1016 l_state_local_tab(l_stloc_tcnt).state_wages_c
1017 := l_state_tab(l_curr_state).state_wages_c ;
1018 l_state_local_tab(l_stloc_tcnt).state_tax_c
1019 := l_state_tab(l_curr_state).state_tax_c ;
1020 l_state_local_tab(l_stloc_tcnt).state_ein_o
1021 := l_state_tab(l_curr_state).state_ein_o ;
1022 l_state_local_tab(l_stloc_tcnt).state_wages_o
1023 := l_state_tab(l_curr_state).state_wages_o ;
1024 l_state_local_tab(l_stloc_tcnt).state_tax_o
1025 := l_state_tab(l_curr_state).state_tax_o ;
1026 l_state_local_tab(l_stloc_tcnt).locality_c := '';
1027 l_state_local_tab(l_stloc_tcnt).locality_wages_c := '';
1028 l_state_local_tab(l_stloc_tcnt).locality_tax_c := '';
1029 l_state_local_tab(l_stloc_tcnt).locality_wages_o := '';
1030 l_state_local_tab(l_stloc_tcnt).locality_tax_o := '';
1031 l_curr_state := l_curr_state + 1;
1032 END;
1033
1034 BEGIN -- populate_state_local_table
1035
1036 l_curr_state := l_state_tab.FIRST;
1037 l_curr_local := l_local_tab.FIRST;
1038 l_stloc_tcnt := 0;
1039
1040 LOOP
1041
1042 hr_utility.trace('l_state_tab.COUNT '||l_state_tab.COUNT);
1043 hr_utility.trace('l_local_tab.COUNT '||l_local_tab.COUNT);
1044 hr_utility.trace('l_curr_state '||l_curr_state);
1045 hr_utility.trace('l_curr_local '||l_curr_local);
1046
1047 EXIT WHEN (l_curr_state > l_state_tab.COUNT and
1048 l_curr_local > l_local_tab.COUNT)
1049 OR (l_curr_state > l_state_tab.COUNT and
1050 l_curr_local IS NULL)
1051 /* Bug 8313261 : Added the following to exit the loop
1052 in case of no data found in l_state_tab */
1053 OR (l_curr_state IS NULL AND
1054 l_curr_local > l_local_tab.COUNT);
1055
1056 l_prior_local := l_local_tab.PRIOR(l_curr_local);
1057 hr_utility.trace('l_prior_local '||l_local_tab.PRIOR(l_curr_local));
1058
1059 IF (l_curr_state IS NOT NULL AND
1060 l_curr_local IS NOT NULL ) AND
1061 (l_curr_state <= l_state_tab.COUNT ) AND
1062 (l_curr_local <= l_local_tab.COUNT) THEN
1063
1064 hr_utility.trace('l_state_tab(l_curr_state).state_code '||l_state_tab(l_curr_state).state_code_c);
1065 hr_utility.trace('l_local_tab(l_curr_local).state_code '||l_local_tab(l_curr_local).state_code_c);
1066 hr_utility.trace('l_curr_state '||l_curr_state);
1067
1068 IF (l_state_tab(l_curr_state).state_code_c =
1069 l_local_tab(l_curr_local).state_code_c ) THEN
1070
1071 hr_utility.trace('Statecode of state is EQUAL to Local state Code');
1072
1073 l_stloc_tcnt := l_state_local_tab.count;
1074
1075 hr_utility.trace('l_state_tab(l_curr_state).state_ein '||l_state_tab(l_curr_state).state_ein_c);
1076 hr_utility.trace('l_state_tab(l_curr_state).state_wages '||l_state_tab(l_curr_state).state_wages_c);
1077 hr_utility.trace('l_state_tab(l_curr_state).state_tax '||l_state_tab(l_curr_state).state_tax_c);
1078 /* Check to see if the state code of prior local is same as current state */
1079
1080 check_prior_local;
1081
1082 IF p_write_state THEN
1083 l_state_local_tab(l_stloc_tcnt).state_code_c
1084 := l_state_tab(l_curr_state).state_code_c ;
1085
1086 l_state_local_tab(l_stloc_tcnt).state_ein_c
1087 := l_state_tab(l_curr_state).state_ein_c ;
1088
1089 l_state_local_tab(l_stloc_tcnt).state_wages_c
1090 := l_state_tab(l_curr_state).state_wages_c ;
1091
1092 l_state_local_tab(l_stloc_tcnt).state_tax_c
1093 := l_state_tab(l_curr_state).state_tax_c ;
1094
1095
1096 l_state_local_tab(l_stloc_tcnt).state_ein_o
1097 := l_state_tab(l_curr_state).state_ein_o ;
1098
1099 l_state_local_tab(l_stloc_tcnt).state_wages_o
1100 := l_state_tab(l_curr_state).state_wages_o ;
1101
1102 l_state_local_tab(l_stloc_tcnt).state_tax_o
1103 := l_state_tab(l_curr_state).state_tax_o ;
1104
1105 l_state_local_tab(l_stloc_tcnt).locality_c
1106 := l_local_tab(l_curr_local).locality_c ;
1107
1108 l_state_local_tab(l_stloc_tcnt).locality_wages_c
1109 := l_local_tab(l_curr_local).locality_wages_c;
1110
1111 l_state_local_tab(l_stloc_tcnt).locality_tax_c
1112 := l_local_tab(l_curr_local).locality_tax_c;
1113
1114 l_state_local_tab(l_stloc_tcnt).locality_wages_o
1115 := l_local_tab(l_curr_local).locality_wages_o;
1116
1117 l_state_local_tab(l_stloc_tcnt).locality_tax_o
1118 := l_local_tab(l_curr_local).locality_tax_o;
1119
1120 ELSE
1121 l_state_local_tab(l_stloc_tcnt).state_code_c
1122 := l_state_tab(l_curr_state).state_code_c;
1123
1124 l_state_local_tab(l_stloc_tcnt).state_ein_c
1125 := '' ;
1126
1127 l_state_local_tab(l_stloc_tcnt).state_wages_c
1128 := '' ;
1129
1130 l_state_local_tab(l_stloc_tcnt).state_tax_c
1131 := '' ;
1132
1133
1134 l_state_local_tab(l_stloc_tcnt).state_ein_o
1135 := '' ;
1136
1137 l_state_local_tab(l_stloc_tcnt).state_wages_o
1138 := '' ;
1139
1140 l_state_local_tab(l_stloc_tcnt).state_tax_o
1141 := '' ;
1142
1143 l_state_local_tab(l_stloc_tcnt).locality_c
1144 := l_local_tab(l_curr_local).locality_c ;
1145
1146 l_state_local_tab(l_stloc_tcnt).locality_wages_c
1147 := l_local_tab(l_curr_local).locality_wages_c;
1148
1149 l_state_local_tab(l_stloc_tcnt).locality_tax_c
1150 := l_local_tab(l_curr_local).locality_tax_c;
1151
1152
1153 l_state_local_tab(l_stloc_tcnt).locality_wages_o
1154 := l_local_tab(l_curr_local).locality_wages_o;
1155
1156 l_state_local_tab(l_stloc_tcnt).locality_tax_o
1157 := l_local_tab(l_curr_local).locality_tax_o;
1158 END IF;
1159 -- l_curr_state := l_state_tab.NEXT(l_curr_state);
1160 /* Just move the index for the current local as one state
1161 may have multiple locals */
1162 -- l_curr_local := l_local_tab.NEXT(l_curr_local);
1163 l_curr_local := l_curr_local + 1;
1164
1165 --{
1166 ELSIF (l_state_tab(l_curr_state).state_code_c <
1167 l_local_tab(l_curr_local).state_code_c ) THEN
1168 check_prior_local;
1169 IF p_write_state THEN
1170 hr_utility.trace('current state doesnot match with prior state, so write current state only');
1171 write_state_only;
1172 ELSE
1173 hr_utility.trace('current state matches with prior state, move to next state');
1174 l_curr_state := l_curr_state + 1;
1175 END IF;
1176 --}
1177 ELSE
1178 hr_utility.trace('Statecode of state is greater than Local state Code');
1179 l_stloc_tcnt := l_state_local_tab.count;
1180
1181 l_state_local_tab(l_stloc_tcnt).state_code_c
1182 := l_local_tab(l_curr_local).state_code_c ;
1183
1184 l_state_local_tab(l_stloc_tcnt).state_ein_c
1185 := '';
1186
1187 l_state_local_tab(l_stloc_tcnt).state_wages_c
1188 := '' ;
1189
1190 l_state_local_tab(l_stloc_tcnt).state_tax_c
1191 := '' ;
1192
1193 l_state_local_tab(l_stloc_tcnt).state_ein_o
1194 := '';
1195
1196 l_state_local_tab(l_stloc_tcnt).state_wages_o
1197 := '' ;
1198
1199 l_state_local_tab(l_stloc_tcnt).state_tax_o
1200 := '' ;
1201
1202 l_state_local_tab(l_stloc_tcnt).locality_c
1203 := l_local_tab(l_curr_local).locality_c ;
1204
1205 l_state_local_tab(l_stloc_tcnt).locality_wages_c
1206 := l_local_tab(l_curr_local).locality_wages_c;
1207
1208 l_state_local_tab(l_stloc_tcnt).locality_tax_c
1209 := l_local_tab(l_curr_local).locality_tax_c;
1210
1211 l_state_local_tab(l_stloc_tcnt).locality_wages_o
1212 := l_local_tab(l_curr_local).locality_wages_o;
1213
1214 l_state_local_tab(l_stloc_tcnt).locality_tax_o
1215 := l_local_tab(l_curr_local).locality_tax_o;
1216
1217 l_curr_local := l_curr_local + 1;
1218 END IF;
1219
1220 -- if l_curr_local is not null and l_curr_state is null
1221 ELSIF (l_curr_state IS NULL and l_curr_local IS NOT NULL)
1222 OR (l_curr_state > l_state_tab.COUNT AND
1223 l_curr_local <= l_local_tab.COUNT) THEN
1224
1225 hr_utility.trace('Current state is null and curr local is NOT null');
1226 hr_utility.trace('Current state is null and curr local is NOT null, l_curr_local '||l_curr_local);
1227 hr_utility.trace('Locality '||l_local_tab(l_curr_local).locality_c);
1228 hr_utility.trace('l_local_tab(l_curr_local).locality_tax '||l_local_tab(l_curr_local).locality_tax_c);
1229 hr_utility.trace('l_local_tab(l_curr_local).locality_wages '||l_local_tab(l_curr_local).locality_wages_c);
1230
1231
1232 l_stloc_tcnt := l_state_local_tab.count;
1233 l_state_local_tab(l_stloc_tcnt).state_code_c
1234 := '' ;
1235
1236 l_state_local_tab(l_stloc_tcnt).state_ein_c
1237 := '';
1238
1239 l_state_local_tab(l_stloc_tcnt).state_wages_c
1240 := '';
1241
1242 l_state_local_tab(l_stloc_tcnt).state_tax_c
1243 := '' ;
1244
1245 l_state_local_tab(l_stloc_tcnt).state_ein_o
1246 := '';
1247
1248 l_state_local_tab(l_stloc_tcnt).state_wages_o
1249 := '';
1250
1251 l_state_local_tab(l_stloc_tcnt).state_tax_o
1252 := '' ;
1253
1254 l_state_local_tab(l_stloc_tcnt).locality_c
1255 := l_local_tab(l_curr_local).locality_c ;
1256
1257 l_state_local_tab(l_stloc_tcnt).locality_wages_c
1258 := l_local_tab(l_curr_local).locality_wages_c;
1259
1260 l_state_local_tab(l_stloc_tcnt).locality_tax_c
1261 := l_local_tab(l_curr_local).locality_tax_c;
1262
1263
1264 l_state_local_tab(l_stloc_tcnt).locality_wages_o
1265 := l_local_tab(l_curr_local).locality_wages_o;
1266
1267 l_state_local_tab(l_stloc_tcnt).locality_tax_o
1268 := l_local_tab(l_curr_local).locality_tax_o;
1269
1270
1271 l_curr_local := l_curr_local + 1;
1272
1273 ELSIF (l_curr_state IS NOT NULL and l_curr_local IS NULL)
1274 OR (l_curr_state <= l_state_tab.COUNT AND
1275 l_curr_local > l_local_tab.COUNT) THEN
1276
1277 hr_utility.trace('Current state is not null and curr local is null');
1278 hr_utility.trace('l_curr_state '||l_curr_state);
1279 hr_utility.trace('l_curr_local '||l_curr_local);
1280
1281 check_prior_local;
1282 IF p_write_state THEN
1283 hr_utility.trace('current state doesnot match with prior state, so write current state only');
1284 write_state_only;
1285 ELSE
1286 hr_utility.trace('current state matches with prior state, move to next state');
1287 l_curr_state := l_curr_state + 1;
1288
1289 END IF;
1290 ELSE
1291 hr_utility.trace('Completed populating all states and locals');
1292 exit;
1293 END IF;
1294 END LOOP;
1295
1296 return l_state_local_tab;
1297 END;
1298
1299 PROCEDURE get_w2c_data(p_asg_action_id NUMBER,
1300 p_prev_asg_action_id NUMBER,
1301 p_tax_unit_id NUMBER,
1302 p_year NUMBER,
1303 p_error_msg out nocopy VARCHAR2)
1304 -- RETURN l_w2c_fields_rec
1305 IS
1306 l_sl_total_count number;
1307 l_sl_count number ;
1308 l_b12_total_count number;
1309 l_b12_count number;
1310 l_b14_total_count number := 0;
1311 l_b14_count number;
1312 l_local_total_count number;
1313
1314 box12_meaning_c VARCHAR2(100);
1315 box12_code_c VARCHAR2(100);
1316 box12_meaning_o VARCHAR2(100);
1317
1318 box14_meaning_c VARCHAR2(100);
1319 box14_code_c VARCHAR2(100);
1320 box14_meaning_o VARCHAR2(100);
1321 /*
1322 l_sl_total_count_o number;
1323 l_sl_count_o number ;
1324 l_b12_total_count_o number;
1325 l_b12_count_o number;
1326 l_b14_total_count_o number;
1327 l_b14_count_o number;
1328 l_local_total_count_o number; */
1329
1330 l_nr_jd varchar2(11);
1331 l_nr_flag varchar2(1);
1332 l_locality varchar2(100);
1333 l_locality_wages_c VARCHAR2(25);
1334 l_locality_tax_c VARCHAR2(25);
1335 l_locality_wages_o VARCHAR2(25);
1336 l_locality_tax_o VARCHAR2(25);
1337 l_jurisdiction varchar2(16); --Bug#13724610
1338 l_jurisdiction_c varchar2(16);
1339 l_state_code varchar2(10);
1340 state_code_c VARCHAR2(20);
1341 state_ein_c VARCHAR2(200);
1342 state_ein_o VARCHAR2(200);
1343 l_state_wages_c VARCHAR2(25);
1344 l_state_wages_o VARCHAR2(25);
1345 l_state_tax_c VARCHAR2(25);
1346 l_state_tax_o VARCHAR2(25);
1347 l_tax_type varchar2(100);
1348 l_box14_boonmh_value_c number;
1349 l_box14_boonmh_value_o number;
1350 l_nj_state_printed VARCHAR2(1);
1351 l_hi_state_printed VARCHAR2(1); /* 6519495 */
1352 l_nj_planid VARCHAR2(20);
1353 l_corrected_date DATE;
1354 l_profile_date DATE;
1355 l_agent_tax_unit_id number;
1356 l_error_msg VARCHAR2(500);
1357 l_business_group_id number;
1358 l_org_federal_ein VARCHAR2(100);
1359 l_org_employer_name VARCHAR2(240);
1360 l_org_address VARCHAR2(500);
1361 l_live_profile_option VARCHAR2(100);
1362 l_payroll_action_id NUMBER;
1363 l_w2_corrected VARCHAR2(10);
1364 p_effective_date DATE;
1365 lr_employee_addr pay_us_get_item_data_pkg.person_name_address;
1366 p_assignment_id NUMBER;
1367 l_person_id NUMBER;
1368 l_profile_date_string VARCHAR2(40);
1369 /* 6500188 */
1370 l_first_name per_all_people_f.first_name%type;
1371 l_middle_name per_all_people_f.middle_names%type;
1372 l_dummy varchar2(100);
1373 l_dummy_2 varchar2(100);
1374 l_full_name per_all_people_f.full_name%type;
1375 l_nj_sdi1_value varchar2(20) ;
1376 l_flipp_id varchar2(20) ;
1377 l_state_zero_flag varchar2(10) ;
1378 get_old_state_not_f boolean;
1379
1380
1381 CURSOR c_get_emp_info (p_asg_action_id NUMBER,
1382 p_tax_unit_id NUMBER,
1383 p_year NUMBER ) IS
1384 select puw.assignment_action_id control_number,
1385 nvl(ssn,'Applied For') SSN,
1386 first_name||
1387 decode(middle_name,null,' ',
1388 ' '||substr(middle_name,1,1)||' ') ||
1389 pre_name_adjunt emp_name,
1390 last_name ,
1391 hr_us_w2_rep.get_per_item(p_asg_action_id,
1392 'A_PER_SUFFIX') emp_suffix,
1393 trim(to_char(decode(W2_WAGES_TIPS_COMPENSATION,0,'',W2_WAGES_TIPS_COMPENSATION),'9999999990.99')) wages_tips_compensation,
1394 trim(to_char(decode(W2_FED_IT_WITHHELD,0,'',W2_FED_IT_WITHHELD),'9999999990.99')) fit_withheld,
1395 trim(to_char(decode(W2_SOCIAL_SECURITY_WAGES,0,'',W2_SOCIAL_SECURITY_WAGES),'9999999990.99')) ss_wages,
1396 trim(to_char(decode(W2_SST_WITHHELD,0,'',W2_SST_WITHHELD),'9999999990.99')) ss_withheld,
1397 trim(to_char(decode(W2_MED_WAGES_TIPS,0,'',W2_MED_WAGES_TIPS),'9999999990.99')) med_wages,
1398 trim(to_char(decode(W2_MED_TAX_WITHHELD,0,'',W2_MED_TAX_WITHHELD),'9999999990.99')) med_withheld,
1399 trim(to_char(decode(W2_SOCIAL_SECURITY_TIPS,0,'',W2_SOCIAL_SECURITY_TIPS),'9999999990.99')) ss_tips,
1400 trim(to_char(decode(W2_ALLOCATED_TIPS,0,'',W2_ALLOCATED_TIPS),'9999999990.99')) allocated_tips,
1401 trim(to_char(decode(W2_ADV_EIC_PAYMENT,0,'',W2_ADV_EIC_PAYMENT),'9999999990.99')) eic_payment,
1402 trim(to_char(decode(W2_DEPENDENT_CARE_BEN,0,'',W2_DEPENDENT_CARE_BEN),'9999999990.99')) dependent_care,
1403 trim(to_char(decode(W2_NONQUAL_PLANS,0,'',W2_NONQUAL_PLANS),'9999999990.99')) non_qual_plan,
1404 decode(W2_STATUTORY_EMPLOYEE,'X','Y',null,'N',' ','N')
1405 stat_employee,
1406 decode(W2_RETIREMENT_PLAN,'X','Y',null,'N',' ','N')
1407 retirement_plan,
1408 decode(W2_THIRD_PARTY_SICK_PAY,'X','Y',null,
1409 'N',' ','N') sick_pay,
1410 person_id , puw.assignment_id -- bug 5575567
1411 from pay_us_wages_w2c_v puw
1412 --per_addresses pa
1413 where puw.assignment_action_id = p_asg_action_id
1414 /* bug 5575567
1415 and pa.primary_flag = 'Y'
1416 and pa.person_id = puw.person_id */
1417 and puw.tax_unit_id = p_tax_unit_id
1418 and puw.year = p_year;
1419 --and sysdate between pa.date_from and nvl(pa.date_to,sysdate);
1420
1421
1422 CURSOR c_get_box12_info (p_asg_action_id NUMBER) IS
1423 select w2_balance_code,
1424 trim(to_char(decode(w2_balance_code_value,0,null,w2_balance_code_value),'9999999990.99'))
1425 from pay_us_code_w2c_v
1426 where assignment_action_id = p_asg_action_id
1427 and tax_unit_id = p_tax_unit_id
1428 order by w2_balance_code;
1429
1430 CURSOR c_get_box12_info_o (p_prev_asg_action_id NUMBER,p_code varchar2) IS
1431 select w2_balance_code,
1432 trim(to_char(decode(w2_balance_code_value,0,null,w2_balance_code_value),'9999999990.99'))
1433 from pay_us_code_w2c_v
1434 where assignment_action_id = p_prev_asg_action_id
1435 and w2_balance_code = p_code
1436 and tax_unit_id = p_tax_unit_id
1437 order by w2_balance_code;
1438
1439 CURSOR c_get_box14_info (p_asg_action_id NUMBER) IS
1440 SELECT substr(w2_other_meaning,1,10) w2_other_code,
1441 trim(to_char(decode(w2_other_value,0,null,w2_other_value),'9999999990.99'))
1442 from pay_us_other_w2c_v
1443 where tax_unit_id = p_tax_unit_id
1444 and assignment_action_id = p_asg_action_id;
1445
1446 CURSOR c_get_box14_info_o (p_prev_asg_action_id NUMBER, p_code varchar2) IS
1447 SELECT substr(w2_other_meaning,1,10) w2_other_code,
1448 trim(to_char(decode(w2_other_value,0,null,w2_other_value),'9999999990.99'))
1449 from pay_us_other_w2c_v
1450 where tax_unit_id = p_tax_unit_id
1451 and substr(w2_other_meaning,1,10) = p_code
1452 and assignment_action_id = p_prev_asg_action_id;
1453
1454 CURSOR c_get_local_info (p_asg_action_id NUMBER) IS
1455 SELECT locality_name locality,
1456 trim(to_char(decode(w2_local_wages,0,'',w2_local_wages),'9999999990.99')) locality_wages,
1457 trim(to_char(decode(w2_local_income_tax,0,'',w2_local_income_tax),'9999999990.99')) locality_tax,
1458 jurisdiction jurisdiction,
1459 state_abbrev state_code,
1460 tax_type
1461 FROM pay_us_locality_w2c_v
1462 WHERE assignment_action_id = p_asg_action_id
1463 and tax_unit_id = p_tax_unit_id
1464 order by state_code, tax_type;
1465 /* Bug # 9267579 */
1466
1467 CURSOR c_get_local_info_o (p_prev_asg_action_id NUMBER, p_tax_type varchar2, p_jurisdiction varchar2) IS
1468 SELECT trim(to_char(decode(w2_local_wages,0,'',w2_local_wages),'9999999990.99')) locality_wages,
1469 trim(to_char(decode(w2_local_income_tax,0,'',w2_local_income_tax),'9999999990.99')) locality_tax
1470 FROM pay_us_locality_w2c_v
1471 WHERE assignment_action_id = p_prev_asg_action_id
1472 and tax_unit_id = p_tax_unit_id
1473 and jurisdiction = p_jurisdiction
1474 and tax_type = p_tax_type;
1475
1476 CURSOR c_get_state_info (p_asg_action_id NUMBER) IS
1477 SELECT 1 , substr(state_abbrev,1,2) state_code,
1478 substr(state_ein,1,20) state_ein,
1479 trim(to_char(decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES),'9999999990.99')) state_wages,
1480 trim(to_char(decode(W2_STATE_INCOME_TAX,0,'',W2_STATE_INCOME_TAX),'9999999990.99')) state_tax,
1481 jurisdiction
1482 FROM pay_us_state_w2c_v state
1483 WHERE assignment_action_id = p_asg_action_id
1484 /*and ( (w2_state_wages > 0) or
1485 (W2_STATE_INCOME_TAX > 0) ) 6809739 */
1486 and state_ein <> 'FLI P.P. #'
1487 and tax_unit_id = p_tax_unit_id
1488 union all
1489 SELECT 2 , substr(state_abbrev,1,2) state_code,
1490 substr(state_ein,1,20) state_ein,
1491 trim(to_char(decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES),'9999999990.99')) state_wages,
1492 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,
1493 jurisdiction
1494 FROM pay_us_state_w2c_v state
1495 WHERE assignment_action_id = p_asg_action_id
1496 and ( (w2_state_wages <> ' ') or
1497 (to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') <> 0) ) /* 6809739 */
1498 and state_ein = 'FLI P.P. #'
1499 and tax_unit_id = p_tax_unit_id
1500 order by state_code , 1 ;
1501
1502 CURSOR c_get_state_info_o (p_asg_action_id NUMBER,p_state varchar2) IS
1503 SELECT 1 ,
1504 substr(state_ein,1,20) state_ein,
1505 trim(to_char(decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES),'9999999990.99')) state_wages,
1506 trim(to_char(decode(W2_STATE_INCOME_TAX,0,'',W2_STATE_INCOME_TAX),'9999999990.99')) state_tax
1507 FROM pay_us_state_w2c_v state
1508 WHERE assignment_action_id = p_asg_action_id
1509 /*and ( (w2_state_wages > 0) or
1510 (W2_STATE_INCOME_TAX > 0) ) 6809739 */
1511 and state_ein <> 'FLI P.P. #'
1512 and tax_unit_id = p_tax_unit_id
1513 and state_abbrev = p_state
1514 order by 2,1 ;
1515
1516 CURSOR c_get_state_info_o2 (p_asg_action_id NUMBER,p_state varchar2) IS
1517 SELECT 2 ,
1518 substr(state_ein,1,20) state_ein,
1519 nvl(W2_STATE_WAGES,'') state_wages,
1520 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
1521 FROM pay_us_state_w2c_v state
1522 WHERE assignment_action_id = p_asg_action_id
1523 and ( (w2_state_wages <> ' ') or
1524 (to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') <> 0) ) /* 6809739 */
1525 and state_ein = 'FLI P.P. #'
1526 and tax_unit_id = p_tax_unit_id
1527 and state_abbrev = p_state
1528 order by 2,1 ;
1529
1530 /*Bug 5735076 added by vaprakas*/
1531 CURSOR c_get_employer_info ( p_tax_unit_id NUMBER, p_year NUMBER) IS
1532 select federal_ein federal_ein,
1533 tax_unit_name employer_name,
1534 decode(put.address_line_1,null,null,substr(put.address_line_1,1,45)||pay_us_w2c_rpt.EOL)||
1535 decode(put.address_line_2,null,null,substr(put.address_line_2,1,45)||pay_us_w2c_rpt.EOL)||
1536 decode(put.address_line_3,null,null,substr(put.address_line_3,1,45)||pay_us_w2c_rpt.EOL)||
1537 decode(put.town_or_city,null,null,put.town_or_city||' ')||
1538 decode(state,null,null,state||' ')||put.postal_code
1539 employer_address
1540 from pay_us_w2_tax_unit_v put
1541 where tax_unit_id = p_tax_unit_id
1542 and year = p_year;
1543 /*end 5735076*/
1544
1545 CURSOR c_get_business_group_id ( p_tax_unit_id NUMBER) IS
1546 select business_group_id
1547 from hr_all_organization_units /*hr_organization_units*/
1548 where organization_id = p_tax_unit_id;
1549
1550 CURSOR c_get_payroll_action (p_asg_action_id NUMBER)IS
1551 select payroll_action_id
1552 from pay_assignment_actions
1553 where assignment_action_id = p_asg_action_id;
1554
1555 CURSOR c_get_session_date IS
1556 SELECT NVL(TO_DATE(TO_CHAR(TO_DATE(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',
1557 ppa.legislative_parameters),'DD-MON-YYYY'),'YYYY/MM/DD'),'YYYY/MM/DD'),SYSDATE) session_date
1558 FROM pay_payroll_actions ppa
1559 WHERE payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1560
1561 c_get_session_date_rec c_get_session_date%ROWTYPE;
1562
1563 FUNCTION get_state_ein (p_jurisdiction in varchar2
1564 ,p_w2c_eff_date in date
1565 ,p_w2c_tax_unit_id in number)
1566 RETURN varchar2 IS
1567 --
1568 CURSOR csr_get_eoy_info(cp_w2c_eff_date in date
1569 ,cp_w2c_tax_unit_id in number) is
1570 select ppa.payroll_action_id
1571 from pay_payroll_actions ppa
1572 where ppa.report_type = 'YREND'
1573 and ppa.effective_date = cp_w2c_eff_date
1574 and pay_us_w2c_arch.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
1575 = cp_w2c_tax_unit_id;
1576
1577 ln_eoy_pactid number :=0;
1578 lv_ein varchar2(100) := null;
1579
1580 BEGIN
1581
1582 open csr_get_eoy_info(p_w2c_eff_date
1583 ,p_w2c_tax_unit_id);
1584
1585 fetch csr_get_eoy_info into ln_eoy_pactid;
1586
1587 select fai.value
1588 into lv_ein
1589 from ff_database_items fdi,
1590 ff_archive_items fai,
1591 ff_archive_item_contexts fac,
1592 ff_archive_item_contexts fac1
1593 where fai.user_entity_id = fdi.user_entity_id
1594 and fai.context1 = to_char(ln_eoy_pactid)
1595 and fac.archive_item_id = fai.archive_item_id
1596 and ltrim(rtrim(fac.context)) = to_char(p_w2c_tax_unit_id)
1597 and fac1.archive_item_id = fai.archive_item_id
1598 and fac1.context = p_jurisdiction
1599 and fdi.user_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID';
1600
1601 close csr_get_eoy_info;
1602
1603 return(lv_ein);
1604 EXCEPTION
1605 when others then
1606 hr_utility.trace('EIN not found ('||p_jurisdiction||')');
1607 return(null);
1608 END;
1609
1610 --{ begin get_w2c_data
1611 BEGIN
1612
1613 l_sl_count := 1;
1614 l_b12_count :=1;
1615 l_b14_count :=1;
1616
1617 OPEN c_get_business_group_id(p_tax_unit_id);
1618 FETCH c_get_business_group_id
1619 INTO l_business_group_id;
1620 CLOSE c_get_business_group_id;
1621
1622 hr_utility.trace('Business Group id ' ||l_business_group_id);
1623 hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID ' ||pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1624
1625 IF l_business_group_id is not null THEN
1626 hr_us_w2_rep.get_agent_tax_unit_id(l_business_group_id
1627 ,p_year
1628 ,l_agent_tax_unit_id
1629 ,l_error_msg);
1630 END IF;
1631
1632 hr_utility.trace('Agent Tax unit id ' ||l_agent_tax_unit_id);
1633 hr_utility.trace('l_error_msg ' ||l_error_msg);
1634 /* If l_erro_msg is not null then throw error else get remaining data for W2 */
1635 IF l_error_msg IS NOT NULL THEN
1636 p_error_msg := l_error_msg;
1637 ELSE
1638
1639 OPEN c_get_employer_info(nvl(l_agent_tax_unit_id, p_tax_unit_id),p_year);
1640
1641
1642 FETCH c_get_employer_info
1643 INTO l_w2c_fields_c.federal_ein,
1644 l_w2c_fields_c.employer_name,
1645 l_w2c_fields_c.employer_address;
1646
1647 CLOSE c_get_employer_info;
1648
1649 hr_utility.trace('l_w2c_fields.federal_ein ' ||l_w2c_fields_c.federal_ein);
1650 hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1651 hr_utility.trace('l_w2c_fields.employer_name ' ||l_w2c_fields_c.employer_name);
1652
1653 IF l_agent_tax_unit_id IS NOT NULL THEN
1654
1655 hr_utility.trace('p_tax_unit_id ' ||p_tax_unit_id);
1656
1657 OPEN c_get_employer_info(p_tax_unit_id,p_year);
1658 FETCH c_get_employer_info
1659 INTO l_org_federal_ein,
1660 l_org_employer_name,
1661 l_org_address;
1662 CLOSE c_get_employer_info;
1663 hr_utility.trace('l_org_federal_ein ' ||l_org_federal_ein);
1664 hr_utility.trace('l_org_employer_name ' ||l_org_employer_name);
1665 hr_utility.trace('l_org_address ' ||l_org_address);
1666
1667 l_w2c_fields_c.employer_address
1668 := 'Agent For ' ||substr(l_org_employer_name,1,44)||
1669 pay_us_w2c_rpt.EOL ||
1670 l_w2c_fields_c.employer_address;
1671
1672 END IF;
1673 /* Bug 5575567 */
1674 hr_utility.trace(' sysdate ' || sysdate);
1675 hr_utility.trace(' end of year ' || fnd_date.canonical_to_date(p_year||'/12/31'));
1676
1677 /* Start : Bug # 8353425
1678 Considering the Session Date instead of System Date while fetching employee's
1679 name. The report will now take the employee's current name as of the application
1680 session date when the report is run, if the session date is greater than the last
1681 day of the year. Otherwise, the Employee W2 Report will take the employee's
1682 name that was effective as of the last day of the year.
1683 Commenting the following If-Else condition.
1684
1685 IF (trunc(sysdate) <
1686 fnd_date.canonical_to_date(p_year||'/12/31')) THEN
1687 p_effective_date := fnd_date.canonical_to_date(p_year||'/12/31');
1688 ELSE
1689 p_effective_date := trunc(sysdate); --Bug 8222402
1690 END IF;
1691
1692 Adding the following lines */
1693
1694 OPEN c_get_session_date;
1695 FETCH c_get_session_date INTO c_get_session_date_rec;
1696 CLOSE c_get_session_date;
1697
1698 /*Start Bug 9073693: Since Application session date is sysdate in case of selfservice,
1699 Replacing c_get_session_date_rec.session_date with sysdate if it is null */
1700
1701 IF (trunc(nvl(c_get_session_date_rec.session_date,sysdate)) <= fnd_date.canonical_to_date(p_year||'/12/31')) THEN -- Bug 9073693
1702 p_effective_date := fnd_date.canonical_to_date(p_year||'/12/31');
1703 ELSE
1704 p_effective_date := trunc(nvl(c_get_session_date_rec.session_date,sysdate)); -- Bug 9073693
1705 END IF;
1706 hr_utility.trace(' p_effective_date ' || p_effective_date);
1707
1708 OPEN c_get_emp_info(p_asg_action_id,p_tax_unit_id, p_year) ;
1709
1710 FETCH c_get_emp_info
1711 INTO l_w2c_fields_c.control_number,
1712 l_w2c_fields_c.SSN,
1713 l_w2c_fields_c.emp_name,
1714 l_w2c_fields_c.last_name,
1715 l_w2c_fields_c.emp_suffix,
1716 l_w2c_fields_c.wages_tips_compensation,
1717 l_w2c_fields_c.fit_withheld,
1718 l_w2c_fields_c.ss_wages,
1719 l_w2c_fields_c.ss_withheld,
1720 l_w2c_fields_c.med_wages,
1721 l_w2c_fields_c.med_withheld,
1722 l_w2c_fields_c.ss_tips,
1723 l_w2c_fields_c.allocated_tips,
1724 l_w2c_fields_c.eic_payment,
1725 l_w2c_fields_c.dependent_care,
1726 l_w2c_fields_c.non_qual_plan,
1727 l_w2c_fields_c.stat_employee,
1728 l_w2c_fields_c.retirement_plan,
1729 l_w2c_fields_c.sick_pay,
1730 -- bug 5575567
1731 l_person_id ,
1732 p_assignment_id ;
1733
1734
1735 hr_utility.trace('EMP NAME ' ||l_w2c_fields_c.emp_name);
1736 hr_utility.trace('Control Number ' ||l_w2c_fields_c.control_number);
1737 IF c_get_emp_info%NOTFOUND THEN
1738 hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_asg_action_id));
1739 CLOSE c_get_emp_info;
1740 raise NO_DATA_FOUND;
1741 END IF;
1742 CLOSE c_get_emp_info;
1743 /* begin
1744
1745 l_full_name := pay_us_get_item_data_pkg.GET_CONTACT_PERSON_INFO(
1746 0 , p_effective_date ,0, ' ' ,
1747 ' ' , 'W2' , ' ' , ' ' , ' ' , l_person_id ,
1748 ' ' , l_dummy , l_dummy , l_dummy , l_dummy , l_dummy , l_dummy ,
1749 l_first_name , l_middle_name ,l_w2c_fields_c.last_name );
1750
1751 select l_first_name||decode(l_middle_name,null,' ',
1752 ' '||substr(l_middle_name,1,1)||' ') ||
1753 hr_us_w2_rep.get_per_item(p_asg_action_id,
1754 'A_PER_PREFIX' ) into l_w2c_fields_c.emp_name from dual;
1755 exception when others then null;
1756 end ;*/
1757
1758 --Now load the originally reported data.
1759
1760 OPEN c_get_emp_info(p_prev_asg_action_id,p_tax_unit_id, p_year) ;
1761
1762 FETCH c_get_emp_info
1763 INTO l_w2c_fields_o.control_number,
1764 l_w2c_fields_o.SSN,
1765 l_w2c_fields_o.emp_name,
1766 l_w2c_fields_o.last_name,
1767 l_w2c_fields_o.emp_suffix,
1768 l_w2c_fields_o.wages_tips_compensation,
1769 l_w2c_fields_o.fit_withheld,
1770 l_w2c_fields_o.ss_wages,
1771 l_w2c_fields_o.ss_withheld,
1772 l_w2c_fields_o.med_wages,
1773 l_w2c_fields_o.med_withheld,
1774 l_w2c_fields_o.ss_tips,
1775 l_w2c_fields_o.allocated_tips,
1776 l_w2c_fields_o.eic_payment,
1777 l_w2c_fields_o.dependent_care,
1778 l_w2c_fields_o.non_qual_plan,
1779 l_w2c_fields_o.stat_employee,
1780 l_w2c_fields_o.retirement_plan,
1781 l_w2c_fields_o.sick_pay,
1782 l_person_id ,
1783 p_assignment_id ;
1784
1785
1786 hr_utility.trace('EMP NAME ' ||l_w2c_fields_o.emp_name);
1787 hr_utility.trace('Control Number ' ||l_w2c_fields_o.control_number);
1788 IF c_get_emp_info%NOTFOUND THEN
1789 hr_utility.trace('No Data found for this assignment action id ' ||to_char(p_prev_asg_action_id));
1790 CLOSE c_get_emp_info;
1791 raise NO_DATA_FOUND;
1792 END IF;
1793 CLOSE c_get_emp_info;
1794 /*
1795 begin
1796
1797 l_full_name := pay_us_get_item_data_pkg.GET_CONTACT_PERSON_INFO(
1798 0 , p_effective_date ,0, ' ' ,
1799 ' ' , 'W2' , ' ' , ' ' , ' ' , l_person_id ,
1800 ' ' , l_dummy , l_dummy , l_dummy , l_dummy , l_dummy , l_dummy ,
1801 l_first_name , l_middle_name ,l_w2c_fields_o.last_name );
1802
1803 select l_first_name||decode(l_middle_name,null,' ',
1804 ' '||substr(l_middle_name,1,1)||' ') ||
1805 hr_us_w2_rep.get_per_item(p_prev_asg_action_id,
1806 'A_PER_PREFIX' ) into l_w2c_fields_o.emp_name from dual;
1807 hr_utility.trace('EMP NAME2 ' ||l_w2c_fields_o.emp_name);
1808 exception when others then null;
1809 end ;*/
1810
1811 lr_employee_addr :=
1812 pay_us_get_item_data_pkg.GET_PERSON_NAME_ADDRESS(
1813 'W2',
1814 l_person_id,
1815 p_assignment_id,
1816 p_effective_date,
1817 p_effective_date,
1818 'Y', --p_validate,
1819 'W2_XML');
1820
1821 IF lr_employee_addr.addr_line_1 IS NOT NULL THEN
1822 l_w2c_fields_c.employee_address := substr(lr_employee_addr.addr_line_1,1,45) ||
1823 pay_us_w2c_rpt.EOL;
1824 END IF;
1825
1826 IF lr_employee_addr.addr_line_2 IS NOT NULL THEN
1827 l_w2c_fields_c.employee_address := l_w2c_fields_c.employee_address||
1828 substr(lr_employee_addr.addr_line_2,1,45) ||
1829 pay_us_w2c_rpt.EOL;
1830 END IF;
1831
1832 IF lr_employee_addr.addr_line_3 IS NOT NULL THEN
1833 l_w2c_fields_c.employee_address := l_w2c_fields_c.employee_address||
1834 substr(lr_employee_addr.addr_line_3,1,45) ||
1835 pay_us_w2c_rpt.EOL;
1836 END IF;
1837
1838 l_w2c_fields_c.employee_address :=l_w2c_fields_c.employee_address ||
1839 lr_employee_addr.city||' '||
1840 lr_employee_addr.region_2 ||' '||
1841 lr_employee_addr.postal_code;
1842
1843 if lr_employee_addr.country <> 'US' then
1844 l_w2c_fields_c.employee_address := l_w2c_fields_c.employee_address ||' '||
1845 lr_employee_addr.country_name;
1846 end if;
1847
1848
1849 l_state_zero_flag := 'Y' ;
1850 l_sl_total_count := 0; --Starting from 0, for the Bug 16391213
1851 OPEN c_get_state_info(p_asg_action_id) ;
1852 LOOP
1853 hr_utility.trace('In state loop ' );
1854
1855
1856 FETCH c_get_state_info
1857 INTO l_dummy,
1858 /* l_state_tab(l_sl_total_count+1).state_code_c,
1859 l_state_tab(l_sl_total_count+1).state_ein_c,
1860 l_state_tab(l_sl_total_count+1).state_wages_c,
1861 l_state_tab(l_sl_total_count+1).state_tax_c,
1862 l_state_tab(l_sl_total_count+1).jurisdiction_c*/
1863 state_code_c,state_ein_c,l_state_wages_c,l_state_tax_c,l_jurisdiction_c;
1864
1865 EXIT WHEN c_get_state_info%NOTFOUND;
1866
1867 IF state_ein_c ='FLI P.P. #' then
1868
1869 OPEN c_get_state_info_o2(p_prev_asg_action_id,state_code_c);
1870
1871 FETCH c_get_state_info_o2
1872 INTO l_dummy_2,
1873 state_ein_o,
1874 l_state_wages_o,
1875 l_state_tax_o;
1876
1877 IF c_get_state_info_o2%NOTFOUND then
1878 get_old_state_not_f := TRUE;
1879 END If;
1880
1881 ELSE
1882
1883 OPEN c_get_state_info_o(p_prev_asg_action_id,state_code_c);
1884
1885 FETCH c_get_state_info_o
1886 INTO l_dummy_2,
1887 state_ein_o,
1888 l_state_wages_o,
1889 l_state_tax_o;
1890
1891 IF c_get_state_info_o%NOTFOUND then
1892 get_old_state_not_f := TRUE;
1893 END If;
1894
1895 END IF;
1896
1897 IF get_old_state_not_f then --c_get_state_info_o%NOTFOUND then
1898
1899 l_state_tab(l_sl_total_count+1).state_code_c := state_code_c;
1900 l_state_tab(l_sl_total_count+1).jurisdiction_c := l_jurisdiction_c;
1901
1902 l_state_tab(l_sl_total_count+1).state_ein_o :=NULL;
1903 l_state_tab(l_sl_total_count+1).state_wages_o :='-0-';
1904 l_state_tab(l_sl_total_count+1).state_tax_o :='-0-';
1905
1906 l_state_tab(l_sl_total_count+1).state_ein_c := state_ein_c;
1907 l_state_tab(l_sl_total_count+1).state_wages_c :=l_state_wages_c;
1908 l_state_tab(l_sl_total_count+1).state_tax_c :=l_state_tax_c;
1909
1910 l_sl_total_count := l_sl_total_count + 1;
1911
1912 ELSE
1913
1914 get_old_state_not_f := FALSE;
1915
1916 IF nvl(l_state_wages_c,0) <> nvl(l_state_wages_o,0)
1917 OR
1918 nvl(l_state_tax_c,0) <> nvl(l_state_tax_o,0) then
1919
1920 l_state_tab(l_sl_total_count+1).state_code_c := state_code_c;
1921 l_state_tab(l_sl_total_count+1).jurisdiction_c := l_jurisdiction_c;
1922
1923 l_state_tab(l_sl_total_count+1).state_ein_c :=state_ein_c;
1924 l_state_tab(l_sl_total_count+1).state_wages_c :=nvl(l_state_wages_c,'-0-');
1925 l_state_tab(l_sl_total_count+1).state_tax_c :=nvl(l_state_tax_c,'-0-');
1926
1927 l_state_tab(l_sl_total_count+1).state_ein_o :=state_ein_o;
1928 l_state_tab(l_sl_total_count+1).state_wages_o :=nvl(l_state_wages_o,'-0-');
1929 l_state_tab(l_sl_total_count+1).state_tax_o :=nvl(l_state_tax_o,'-0-');
1930
1931 l_sl_total_count := l_sl_total_count + 1;
1932
1933 if ((l_state_tab(l_sl_total_count).state_ein_c <> 'FLI P.P. #' and l_state_tab(l_sl_total_count).state_ein_o <> 'FLI P.P. #')
1934 and (l_state_wages_c > 0 or l_state_tax_c > 0
1935 or l_state_tax_o > 0 or l_state_tax_o > 0))
1936 or (l_state_tab(l_sl_total_count).state_ein_c = 'FLI P.P. #' and l_state_tab(l_sl_total_count).state_ein_o = 'FLI P.P. #') then
1937 --
1938
1939 if nvl(l_state_tab(l_sl_total_count).state_ein_o ,'NO STATE EIN') = 'NO STATE EIN'
1940 OR nvl(l_state_tab(l_sl_total_count).state_ein_c ,'NO STATE EIN') = 'NO STATE EIN' then
1941 l_state_tab(l_sl_total_count).state_ein_o := get_state_ein(l_state_tab(l_sl_total_count).jurisdiction_c,fnd_date.canonical_to_date(p_year||'/12/31'), p_tax_unit_id);
1942 l_state_tab(l_sl_total_count).state_ein_c := l_state_tab(l_sl_total_count).state_ein_o;
1943 end if;
1944 end if;
1945
1946 if l_state_zero_flag = 'Y' and l_state_tab(l_sl_total_count).state_ein_c <> 'FLI P.P. #'
1947 and ( (l_state_wages_c > 0 or l_state_tax_c > 0)
1948 or
1949 (l_state_wages_o > 0 or l_state_tax_o > 0)) THEN
1950 l_state_zero_flag := 'N' ;
1951 end if ;
1952
1953 IF l_state_tab(l_sl_total_count).state_code_c = 'NJ' THEN
1954 l_nj_state_printed := 'Y';
1955 END IF;
1956
1957 IF l_state_tab(l_sl_total_count).state_code_c = 'HI' THEN /* 6519495 */
1958 l_hi_state_printed := 'Y';
1959 END IF;
1960
1961 END IF; -- values are different
1962 END IF; --c_get_state_info_o found
1963
1964 IF state_ein_c ='FLI P.P. #' then
1965 CLOSE c_get_state_info_o2;
1966 ELSE
1967 CLOSE c_get_state_info_o;
1968 END IF;
1969
1970 END LOOP;
1971 CLOSE c_get_state_info;
1972
1973
1974 l_local_total_count := 0; --Starting from 0, for the Bug 16391213
1975 OPEN c_get_local_info(p_asg_action_id) ;
1976 LOOP
1977 hr_utility.trace('In local loop ' );
1978 FETCH c_get_local_info
1979 INTO l_locality,
1980 l_locality_wages_c,
1981 l_locality_tax_c,
1982 l_jurisdiction,
1983 l_state_code,
1984 l_tax_type;
1985
1986 hr_utility.trace('l_locality is '||l_locality);
1987 hr_utility.trace('l_locality_wages_c is '||l_locality_wages_c);
1988 hr_utility.trace('l_locality_tax_c is '||l_locality_tax_c);
1989 EXIT WHEN c_get_local_info%NOTFOUND;
1990
1991 OPEN c_get_local_info_o(p_prev_asg_action_id,l_tax_type,l_jurisdiction);
1992
1993 FETCH c_get_local_info_o
1994 INTO l_locality_wages_o,
1995 l_locality_tax_o;
1996
1997 hr_utility.trace('l_locality_wages_o is '||l_locality_wages_o);
1998 hr_utility.trace('l_locality_tax_o is '||l_locality_tax_o);
1999 IF c_get_local_info_o%NOTFOUND then
2000
2001 l_local_total_count := l_local_total_count + 1;
2002
2003 l_local_tab(l_local_total_count).locality_wages_o := '-0-';
2004 l_local_tab(l_local_total_count).locality_tax_o := '-0-';
2005
2006 l_local_tab(l_local_total_count).locality_wages_c := l_locality_wages_c;
2007 l_local_tab(l_local_total_count).locality_tax_c := l_locality_tax_c;
2008
2009 ELSE
2010
2011 IF l_locality_tax_c > 0 OR l_locality_wages_c > 0 OR l_locality_tax_o > 0 OR l_locality_wages_o > 0 THEN
2012 -- l_local_total_count := l_local_total_count + 1;
2013 /* populate the locality table only if the jurisdiction code <> 18-015-000
2014 as this needs to be reported in box 14 as occupational and mental health tax*/
2015 IF ( l_tax_type = 'COUNTY' and l_jurisdiction = '18-015-0000') THEN
2016 hr_utility.trace('Jurisdiction is 18-015-0000 and tax_type is County');
2017 IF (l_locality_tax_c > 0 and l_locality_wages_c > 0)
2018 OR (l_locality_wages_o > 0 and l_locality_tax_o > 0) THEN
2019
2020 hr_utility.trace('Locality tax withheld > 0 for KY, Boone county');
2021
2022 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2023
2024 l_box14_boonmh_value_c := hr_us_w2_rep.get_w2_arch_bal(p_asg_action_id, 'A_MISC1_COUNTY_TAX_WITHHELD_PER_JD_GRE_YTD' ,
2025 p_tax_unit_id, '18-015-0000', 6);
2026
2027 l_box14_boonmh_value_o := hr_us_w2_rep.get_w2_arch_bal(p_prev_asg_action_id, 'A_MISC1_COUNTY_TAX_WITHHELD_PER_JD_GRE_YTD' ,
2028 p_tax_unit_id, '18-015-0000', 6);
2029
2030 IF NVL(l_box14_boonmh_value_c,0) <> NVL(l_box14_boonmh_value_o,0) then
2031
2032 l_box14_tab(l_b14_total_count+1).box14_code_c := 'BOONMH';
2033
2034 select decode(l_box14_boonmh_value_c,0,'-0-',l_box14_boonmh_value_c) into l_box14_tab(l_b14_total_count+1).box14_meaning_c from dual;
2035
2036 hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning_c '||l_box14_tab(l_b14_total_count+1).box14_meaning_c);
2037
2038 select decode(l_box14_boonmh_value_o,0,'-0-',l_box14_boonmh_value_o) into l_box14_tab(l_b14_total_count+1).box14_meaning_o from dual;
2039
2040 hr_utility.trace('l_box14_tab(l_b14_total_count).box14_meaning_o '||l_box14_tab(l_b14_total_count).box14_meaning_o);
2041
2042 l_b14_total_count := l_box14_tab.count+1;
2043 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2044
2045 l_box14_tab(l_b14_total_count+1).box14_code_c := 'BOONOCC';
2046 l_box14_tab(l_b14_total_count+1).box14_meaning_c :=
2047 l_locality_tax_c - l_box14_boonmh_value_c;
2048
2049
2050 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2051
2052 l_box14_tab(l_b14_total_count+1).box14_meaning_o :=
2053 l_local_tab(l_local_total_count+1).locality_wages_o - l_box14_boonmh_value_o;
2054
2055 END IF;
2056
2057 END IF;
2058 ELSE
2059
2060 IF (NVL(l_locality_wages_c,0) <> NVL(l_locality_wages_o,0))
2061 OR
2062 (NVL(l_locality_tax_c,0) <> NVL(l_locality_tax_o,0)) Then
2063
2064 l_local_total_count := l_local_total_count + 1; /* Imp Point: Increment the counter only if the values are different.
2065 If the values are same then next record will override the current values.
2066 */
2067 l_local_tab(l_local_total_count).locality_c := l_locality;
2068
2069 l_local_tab(l_local_total_count).locality_wages_c := nvl(l_locality_wages_c,'-0-');
2070 l_local_tab(l_local_total_count).locality_tax_c := nvl(l_locality_tax_c,'-0-');
2071
2072 l_local_tab(l_local_total_count).locality_wages_o := nvl(l_locality_wages_o,'-0-');
2073 l_local_tab(l_local_total_count).locality_tax_o := nvl(l_locality_tax_o,'-0-');
2074
2075 l_local_tab(l_local_total_count).jurisdiction_c := l_jurisdiction;
2076 l_local_tab(l_local_total_count).state_code_c := l_state_code;
2077 l_local_tab(l_local_total_count).tax_type_c := l_tax_type;
2078
2079 hr_utility.trace('Locality_code '|| l_local_tab(l_local_total_count).locality_c);
2080 hr_utility.trace('Locality state_code '|| l_local_tab(l_local_total_count).state_code_c);
2081
2082 hr_utility.trace('Locality_jurisdiction '|| l_local_tab(l_local_total_count).jurisdiction_c);
2083 hr_utility.trace('Locality Tax '|| l_local_tab(l_local_total_count).locality_tax_c);
2084 hr_utility.trace('Locality Tax Type '|| l_local_tab(l_local_total_count).tax_type_c);
2085
2086
2087 IF (nvl(l_locality_tax_c,0) > 0) OR (nvl(l_locality_tax_o,0) > 0) THEN
2088 IF (l_local_tab(l_local_total_count).tax_type_c = 'CITY SCHOOL' or
2089 l_local_tab(l_local_total_count).tax_type_c = 'COUNTY SCHOOL' ) THEN
2090
2091 hr_utility.trace('Locality Tax Type is County/city school');
2092
2093 if l_local_tab(l_local_total_count).state_code_c = 'OH' then
2094
2095 hr_utility.trace('Locality state code is OH');
2096
2097 l_local_tab(l_local_total_count).locality_c
2098 := substr(l_local_tab(l_local_total_count).jurisdiction_c,5,4)
2099 ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,8);
2100
2101 elsif l_local_tab(l_local_total_count).state_code_c = 'KY' then
2102 hr_utility.trace('Locality state code is KY');
2103 l_local_tab(l_local_total_count).locality_c
2104 := substr(l_local_tab(l_local_total_count).jurisdiction_c,7,2)
2105 ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,10);
2106 else
2107 hr_utility.trace('Locality state code neither OH nor KY');
2108 l_local_tab(l_local_total_count).locality_c
2109 := substr(l_local_tab(l_local_total_count).jurisdiction_c,4,5)
2110 ||' '||substr(l_local_tab(l_local_total_count).locality_c,1,7);
2111 end if;
2112 END IF;
2113 END IF;
2114
2115 hr_utility.trace('l_local_tab(l_local_total_count).locality_c is '||l_local_tab(l_local_total_count).locality_c);
2116 IF (l_local_tab(l_local_total_count).state_code_c = 'IN'
2117 and l_local_tab(l_local_total_count).tax_type_c = 'COUNTY') THEN
2118 BEGIN
2119 select nvl(value,'N') into l_nr_flag
2120 from ff_database_items fdi,
2121 ff_archive_items fai
2122 where user_name = 'A_IN_NR_FLAG'
2123 and fdi.user_entity_id = fai.user_entity_id
2124 and fai.context1 = p_asg_action_id;
2125
2126 IF l_nr_flag = 'N' THEN
2127 BEGIN
2128 select nvl(value,'00-000-0000') into l_nr_jd
2129 from ff_database_items fdi,
2130 ff_archive_items fai
2131 where fdi.user_name = 'A_IN_RES_JD'
2132 and fdi.user_entity_id = fai.user_entity_id
2133 and context1 = p_asg_action_id;
2134
2135 IF substr(l_local_tab(l_local_total_count).jurisdiction_c,1,2) = '15' THEN
2136 IF l_nr_jd <> l_local_tab(l_local_total_count).jurisdiction_c THEN
2137 l_local_tab(l_local_total_count).locality_c
2138 := 'NR '||substr(l_local_tab(l_local_total_count).locality_c,1,10);
2139 END IF;
2140 END IF;
2141 EXCEPTION WHEN others THEN
2142 null;
2143 END;
2144 END IF;
2145 EXCEPTION WHEN others THEN
2146 null;
2147 END;
2148 END IF;
2149
2150 END IF;/* Wage or withheld check */
2151 END IF ; /* end of the KY boone county check */
2152 END IF; /* l_locality_tax > 0 */
2153
2154 END IF; --c_get_local_info_o not found
2155 CLOSE c_get_local_info_o;
2156
2157 END LOOP;
2158 CLOSE c_get_local_info;
2159
2160 l_b12_total_count := 0; --Starting with 0, for the Bug 16391213
2161
2162 OPEN c_get_box12_info (p_asg_action_id) ;
2163 LOOP
2164 FETCH c_get_box12_info
2165 INTO box12_code_c,
2166 box12_meaning_c;
2167
2168 EXIT WHEN c_get_box12_info%NOTFOUND;
2169
2170 OPEN c_get_box12_info_o(p_prev_asg_action_id,box12_code_c);
2171 FETCH c_get_box12_info_o
2172 INTO box12_code_c,
2173 box12_meaning_o;
2174
2175 IF c_get_box12_info_o%NOTFOUND then
2176
2177 l_box12_tab(l_b12_total_count+1).box12_code_c := box12_code_c;
2178 l_box12_tab(l_b12_total_count+1).box12_meaning_c := box12_meaning_c;
2179 l_box12_tab(l_b12_total_count+1).box12_meaning_o := '-0-';
2180 l_b12_total_count := l_b12_total_count + 1;
2181
2182 ELSE
2183
2184 IF NVL(box12_meaning_o,'XX') <> NVL(box12_meaning_c,'XX') then
2185
2186 hr_utility.trace('In box12 loop '||box12_code_c );
2187
2188 l_box12_tab(l_b12_total_count+1).box12_code_c := box12_code_c;
2189 l_box12_tab(l_b12_total_count+1).box12_meaning_c := nvl(box12_meaning_c,'-0-');
2190 l_box12_tab(l_b12_total_count+1).box12_meaning_o := nvl(box12_meaning_o,'-0-');
2191
2192 l_b12_total_count := l_b12_total_count + 1;
2193
2194 END IF;
2195 END IF;
2196
2197 CLOSE c_get_box12_info_o;
2198
2199 END LOOP;
2200 CLOSE c_get_box12_info ;
2201
2202 -- l_b14_total_count := l_box14_tab.count;
2203 OPEN c_get_box14_info (p_asg_action_id) ;
2204 LOOP
2205
2206 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
2207
2208 FETCH c_get_box14_info
2209 INTO box14_code_c,
2210 box14_meaning_c;
2211
2212 EXIT WHEN c_get_box14_info%NOTFOUND;
2213
2214 OPEN c_get_box14_info_o(p_prev_asg_action_id,box14_code_c);
2215 FETCH c_get_box14_info_o
2216 INTO box14_code_c,
2217 box14_meaning_o;
2218
2219 hr_utility.trace('In box14 loop ' ||box14_code_c);
2220
2221 IF c_get_box14_info_o%NOTFOUND then
2222
2223 l_box14_tab(l_b14_total_count+1).box14_code_c := box14_code_c;
2224 l_box14_tab(l_b14_total_count+1).box14_meaning_c := box14_meaning_c;
2225 l_box14_tab(l_b14_total_count+1).box14_meaning_o := '-0-';
2226
2227 l_b14_total_count := l_b14_total_count+1;
2228 ELSE
2229
2230 IF NVL(box14_meaning_o,'XX') <> NVL(box14_meaning_c,'XX') then
2231
2232 l_box14_tab(l_b14_total_count+1).box14_code_c := box14_code_c;
2233 l_box14_tab(l_b14_total_count+1).box14_meaning_c := nvl(box14_meaning_c,'-0-');
2234 l_box14_tab(l_b14_total_count+1).box14_meaning_o := nvl(box14_meaning_o,'-0-');
2235
2236 l_b14_total_count := l_b14_total_count+1;
2237
2238 END IF;
2239 END IF;
2240 CLOSE c_get_box14_info_o;
2241 END LOOP;
2242 CLOSE c_get_box14_info ;
2243
2244 /* Note: Need to understand the requirement for the state 'HI' and 'NJ', and will modify the code accordingly
2245
2246 hr_utility.trace('l_locality_tax :' || l_locality_tax );
2247 hr_utility.trace('l_hi_state_printed' || l_hi_state_printed );
2248 If l_hi_state_printed = 'Y' and l_locality_tax > 0 then /* 6519495
2249
2250 l_b14_total_count := l_box14_tab.count + 1;
2251
2252 l_box14_tab(l_b14_total_count).box14_meaning := l_local_tab(l_local_total_count).locality_tax ;
2253 l_box14_tab(l_b14_total_count).box14_code := l_local_tab(l_local_total_count).locality ;
2254 l_local_tab(l_local_total_count).locality_wages := 0 ;
2255 l_local_tab(l_local_total_count).locality_tax := 0 ;
2256 l_local_tab(l_local_total_count).locality := ' ' ;
2257
2258 end if ; */
2259
2260
2261
2262 /* Code to print NJ DI.P.P. #
2263 -- Bug 4544792
2264 If l_nj_state_printed = 'Y' then
2265 l_nj_planid := pay_us_archive_util.get_archive_value(p_asg_action_id,
2266 'A_SCL_ASG_US_NJ_PLAN_ID', --A_EXTRA_ASSIGNMENT_INFORMATION_PAY_US_DISABILITY_PLAN_INFO_DF_PLAN_ID'
2267 p_tax_unit_id) ;
2268 If l_nj_planid IS NOT NULL then
2269 hr_utility.trace('NJ DIPP plan id: ' || l_nj_planid);
2270 --Bug 7361496 Formatting DI P.P. # for last 10 characters to appear in Employee W-2 PDF
2271 l_nj_planid := substr(l_nj_planid,length(l_nj_planid)-10+1,length(l_nj_planid));
2272 l_b14_total_count := l_box14_tab.count + 1;
2273 l_box14_tab(l_b14_total_count).box14_code := 'DI P.P. # '||l_nj_planid ;
2274 l_box14_tab(l_b14_total_count).box14_meaning:='';
2275 end if;
2276 end if; */
2277
2278 l_state_local_tab := populate_state_local_table
2279 (l_state_tab,l_local_tab);
2280
2281
2282 /* these checks are not needed for w2c
2283 IF least(nvl(l_w2c_fields.wages_tips_compensation,0)
2284 ,nvl(l_w2c_fields.fit_withheld,0)
2285 ,nvl(l_w2c_fields.ss_wages,0)
2286 ,nvl(l_w2c_fields.ss_withheld,0)
2287 ,nvl(l_w2c_fields.med_wages,0)
2288 ,nvl(l_w2c_fields.med_withheld,0)
2289 ,nvl(l_w2c_fields.ss_tips,0)
2290 ,nvl(l_w2c_fields.allocated_tips,0)
2291 ,nvl(l_w2c_fields.eic_payment,0)
2292 ,nvl(l_w2c_fields.dependent_care,0)
2293 ,nvl(l_w2c_fields.non_qual_plan,0)) < 0 THEN
2294 hr_utility.trace('Negative values for atleast one of box1-11 ');
2295 p_error_msg := 'Negative values for atleast one of box1-11';
2296 END IF;
2297
2298
2299 hr_utility.trace('Before 0 check ');
2300 IF nvl(l_w2c_fields.wages_tips_compensation,0)=0 and
2301 nvl(l_w2c_fields.fit_withheld,0)=0 and
2302 nvl(l_w2c_fields.ss_wages,0)=0 and
2303 nvl(l_w2c_fields.ss_withheld,0)=0 and
2304 nvl(l_w2c_fields.med_wages,0)=0 and
2305 nvl(l_w2c_fields.med_withheld,0)=0 and
2306 nvl(l_w2c_fields.ss_tips,0)=0 and
2307 nvl(l_w2c_fields.allocated_tips,0)=0 and
2308 nvl(l_w2c_fields.eic_payment,0)=0 and
2309 nvl(l_w2c_fields.dependent_care,0)=0 and
2310 nvl(l_w2c_fields.non_qual_plan,0)=0 and l_state_zero_flag = 'Y'
2311 and l_b12_total_count = 0 THEN
2312 hr_utility.trace('Zero values for box1-12 and state wages/withheld');
2313 p_error_msg := 'Zero values for box1-12 and state wages/withheld ';
2314 END IF; */
2315
2316 END IF; /* l_error_msg is not null */
2317
2318 END;
2319 -- } end get_w2_data
2320
2321 FUNCTION create_xml_string (--l_w2c_fields_o l_w2c_fields_rec,
2322 l_w2c_fields_c l_w2c_fields_rec,
2323 l_box14_codea VARCHAR2,l_box14_meaninga_c VARCHAR2,l_box14_meaninga_o VARCHAR2,
2324 l_box14_codeb VARCHAR2,l_box14_meaningb_c VARCHAR2,l_box14_meaningb_o VARCHAR2,
2325 l_box14_codec VARCHAR2,l_box14_meaningc_c VARCHAR2,l_box14_meaningc_o VARCHAR2,
2326 l_box12_codea VARCHAR2,l_box12_meaninga_c VARCHAR2,l_box12_meaninga_o VARCHAR2,
2327 l_box12_codeb VARCHAR2,l_box12_meaningb_c VARCHAR2,l_box12_meaningb_o VARCHAR2,
2328 l_box12_codec VARCHAR2,l_box12_meaningc_c VARCHAR2,l_box12_meaningc_o VARCHAR2,
2329 l_box12_coded VARCHAR2,l_box12_meaningd_c VARCHAR2,l_box12_meaningd_o VARCHAR2,
2330 l_state1_code VARCHAR2,
2331 l_state1_ein_c VARCHAR2,l_state1_wages_c VARCHAR2,l_state1_tax_c VARCHAR2,
2332 l_state1_ein_o VARCHAR2,l_state1_wages_o VARCHAR2,l_state1_tax_o VARCHAR2,
2333 l_local1_wages_c VARCHAR2,l_local1_tax_c VARCHAR2,
2334 l_local1_wages_o VARCHAR2,l_local1_tax_o VARCHAR2,
2335 l_locality1 VARCHAR2,
2336 l_state2_code VARCHAR2,
2337 l_state2_ein_c VARCHAR2,l_state2_wages_c VARCHAR2, l_state2_tax_c VARCHAR2,
2338 l_state2_ein_o VARCHAR2,l_state2_wages_o VARCHAR2, l_state2_tax_o VARCHAR2,
2339 l_local2_wages_c VARCHAR2,l_local2_tax_c VARCHAR2,
2340 l_local2_wages_o VARCHAR2,l_local2_tax_o VARCHAR2,
2341 l_locality2 VARCHAR2,p_year VARCHAR2)
2342 RETURN BLOB IS
2343 l_xml_string VARCHAR2(32767);
2344 l_xml_BLOB BLOB;
2345 is_temp varchar2(10);
2346 text_size NUMBER;
2347 raw_data RAW(32767);
2348
2349 begin
2350 hr_utility.trace('In create XML string ' );
2351 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
2352 IF (g_print_instr IS NULL) OR (g_print_instr = '') THEN
2353 g_print_instr := 'Y';
2354 END IF;
2355
2356 l_xml_string :='<xapi:data>'||EOL||
2357 '<w2c>'||EOL||
2358 '<control_number>' || xml_special_chars(l_w2c_fields_c.control_number)||'</control_number>'||EOL||
2359 '<federal_ein>' || xml_special_chars(l_w2c_fields_c.federal_ein) ||'</federal_ein>'||EOL||
2360 '<employer_name>'|| xml_special_chars(l_w2c_fields_c.employer_name)||'</employer_name>'||EOL||
2361 '<employer_address>'|| xml_special_chars(l_w2c_fields_c.employer_address)||'</employer_address>'||EOL||
2362 '<ssn_o>' || ssn_o ||'</ssn_o>'||EOL||
2363 '<ssn_c>' || ssn_c ||'</ssn_c>'||EOL||
2364 '<emp_name_o>' || emp_name_o ||'</emp_name_o>'||EOL||
2365 '<last_name_o>' || last_name_o ||'</last_name_o>'||EOL||
2366 '<emp_suffix_o>' || emp_suffix_o ||'</emp_suffix_o>'||EOL||
2367 '<emp_name_c>' || emp_name_c ||'</emp_name_c>'||EOL||
2368 '<last_name_c>' || last_name_c ||'</last_name_c>'||EOL||
2369 '<emp_suffix_c>' || emp_suffix_c ||'</emp_suffix_c>'||EOL||
2370 '<name_ssn_corrected>' || name_ssn_corrected ||'</name_ssn_corrected>'||EOL||
2371 '<employee_address>' || xml_special_chars(l_w2c_fields_c.employee_address)||'</employee_address>'||EOL||
2372 '<wages_tips_compensation_o>' || wages_tips_compensation_o ||'</wages_tips_compensation_o>'||EOL||
2373 '<wages_tips_compensation_c>' || wages_tips_compensation_c ||'</wages_tips_compensation_c>'||EOL||
2374 '<fit_withheld_o>' || fit_withheld_o ||'</fit_withheld_o>'||EOL||
2375 '<fit_withheld_c>' || fit_withheld_c ||'</fit_withheld_c>'||EOL||
2376 '<ss_wages_o>' || ss_wages_o||'</ss_wages_o>'||EOL||
2377 '<ss_wages_c>' || ss_wages_c||'</ss_wages_c>'||EOL||
2378 '<ss_withheld_o>' || ss_withheld_o||'</ss_withheld_o>'||EOL||
2379 '<ss_withheld_c>' || ss_withheld_c||'</ss_withheld_c>'||EOL||
2380 '<med_wages_o>' || med_wages_o||'</med_wages_o>'||EOL||
2381 '<med_wages_c>' || med_wages_c||'</med_wages_c>'||EOL||
2382 '<med_withheld_o>' || med_withheld_o||'</med_withheld_o>'||EOL||
2383 '<med_withheld_c>' || med_withheld_c||'</med_withheld_c>'||EOL||
2384 '<ss_tips_o>' ||ss_tips_o||'</ss_tips_o>'||EOL||
2385 '<ss_tips_c>' ||ss_tips_c||'</ss_tips_c>'||EOL||
2386 '<allocated_tips_o>' ||allocated_tips_o||'</allocated_tips_o>'||EOL||
2387 '<allocated_tips_c>' ||allocated_tips_c||'</allocated_tips_c>'||EOL||
2388 '<eic_payment_o>' || eic_payment_o||'</eic_payment_o>'||EOL||
2389 '<eic_payment_c>' || eic_payment_c||'</eic_payment_c>'||EOL||
2390 '<dependent_care_o>' ||dependent_care_o||'</dependent_care_o>'||EOL||
2391 '<dependent_care_c>' ||dependent_care_c||'</dependent_care_c>'||EOL||
2392 '<non_qual_plan_o>' || non_qual_plan_o||'</non_qual_plan_o>'||EOL||
2393 '<non_qual_plan_c>' || non_qual_plan_c||'</non_qual_plan_c>'||EOL||
2394 '<stat_employee_c>' || stat_employee_c||'</stat_employee_c>'||EOL||
2395 '<retirement_plan_c>' || retirement_plan_c||'</retirement_plan_c>'||EOL||
2396 '<sick_pay_c>' || sick_pay_c|| '</sick_pay_c>'||EOL||
2397 '<stat_employee_o>' || stat_employee_o||'</stat_employee_o>'||EOL||
2398 '<retirement_plan_o>' || retirement_plan_o||'</retirement_plan_o>'||EOL||
2399 '<sick_pay_o>' || sick_pay_o|| '</sick_pay_o>'||EOL||
2400 '<box14_codea>'||xml_special_chars(l_box14_codea) ||'</box14_codea>' ||EOL||
2401 '<box14_meaninga_c>'||xml_special_chars(l_box14_meaninga_c) ||'</box14_meaninga_c>' ||EOL||
2402 '<box14_meaninga_o>'||xml_special_chars(l_box14_meaninga_o) ||'</box14_meaninga_o>' ||EOL||
2403 '<box14_codeb>'||xml_special_chars(l_box14_codeb) ||'</box14_codeb>' ||EOL||
2404 '<box14_meaningb_c>'||xml_special_chars(l_box14_meaningb_c) ||'</box14_meaningb_c>' ||EOL||
2405 '<box14_meaningb_o>'||xml_special_chars(l_box14_meaningb_o) ||'</box14_meaningb_o>' ||EOL||
2406 '<box14_codec>'||xml_special_chars(l_box14_codec) ||'</box14_codec>' ||EOL||
2407 '<box14_meaningc_c>'||xml_special_chars(l_box14_meaningc_c) ||'</box14_meaningc_c>' ||EOL||
2408 '<box14_meaningc_o>'||xml_special_chars(l_box14_meaningc_o) ||'</box14_meaningc_o>' ||EOL||
2409 '<box12_codea>'||xml_special_chars(l_box12_codea) ||'</box12_codea>' ||EOL||
2410 '<box12_meaninga_c>'||xml_special_chars(l_box12_meaninga_c) ||'</box12_meaninga_c>' ||EOL||
2411 '<box12_meaninga_o>'||xml_special_chars(l_box12_meaninga_o) ||'</box12_meaninga_o>' ||EOL||
2412 '<box12_codeb>'||xml_special_chars(l_box12_codeb) ||'</box12_codeb>' ||EOL||
2413 '<box12_meaningb_c>'||xml_special_chars(l_box12_meaningb_c) ||'</box12_meaningb_c>' ||EOL||
2414 '<box12_meaningb_o>'||xml_special_chars(l_box12_meaningb_o) ||'</box12_meaningb_o>' ||EOL||
2415 '<box12_codec>'||xml_special_chars(l_box12_codec) ||'</box12_codec>' ||EOL||
2416 '<box12_meaningc_c>'||xml_special_chars(l_box12_meaningc_c) ||'</box12_meaningc_c>' ||EOL||
2417 '<box12_meaningc_o>'||xml_special_chars(l_box12_meaningc_o) ||'</box12_meaningc_o>' ||EOL||
2418 '<box12_coded>'||xml_special_chars(l_box12_coded) ||'</box12_coded>' ||EOL||
2419 '<box12_meaningd_c>'||xml_special_chars(l_box12_meaningd_c) ||'</box12_meaningd_c>' ||EOL||
2420 '<box12_meaningd_o>'||xml_special_chars(l_box12_meaningd_o) ||'</box12_meaningd_o>' ||EOL||
2421 '<state1_code>'||xml_special_chars(l_state1_code)||'</state1_code>' ||EOL||
2422 '<state1_ein_c>'||xml_special_chars(l_state1_ein_c)||'</state1_ein_c>' ||EOL||
2423 '<state1_wages_c>'||xml_special_chars(l_state1_wages_c)||'</state1_wages_c>' ||EOL||
2424 '<state1_tax_c>'||xml_special_chars(l_state1_tax_c)||'</state1_tax_c>' ||EOL||
2425 '<local1_wages_c>'||check_negative_number(l_local1_wages_c)||'</local1_wages_c>' ||EOL||
2426 '<local1_tax_c>'||check_negative_number(l_local1_tax_c)||'</local1_tax_c>' ||EOL||
2427 '<state1_ein_o>'||xml_special_chars(l_state1_ein_o)||'</state1_ein_o>' ||EOL||
2428 '<state1_wages_o>'||xml_special_chars(l_state1_wages_o)||'</state1_wages_o>' ||EOL||
2429 '<state1_tax_o>'||xml_special_chars(l_state1_tax_o)||'</state1_tax_o>' ||EOL||
2430 '<local1_wages_o>'||check_negative_number(l_local1_wages_o)||'</local1_wages_o>' ||EOL||
2431 '<local1_tax_o>'||check_negative_number(l_local1_tax_o)||'</local1_tax_o>' ||EOL||
2432 '<locality1>'||xml_special_chars(l_locality1)||'</locality1>' ||EOL||
2433 '<state2_code>'||xml_special_chars(l_state2_code)||'</state2_code>' ||EOL||
2434 '<state2_ein_c>'||xml_special_chars(l_state2_ein_c)||'</state2_ein_c>' ||EOL||
2435 '<state2_wages_c>'||xml_special_chars(l_state2_wages_c)||'</state2_wages_c>' ||EOL||
2436 '<state2_tax_c>'||xml_special_chars(l_state2_tax_c)||'</state2_tax_c>' ||EOL||
2437 '<local2_wages_c>'||check_negative_number(l_local2_wages_c)||'</local2_wages_c>' ||EOL||
2438 '<local2_tax_c>'||check_negative_number(l_local2_tax_c)||'</local2_tax_c>' ||EOL||
2439 '<state2_ein_o>'||xml_special_chars(l_state2_ein_o)||'</state2_ein_o>' ||EOL||
2440 '<state2_wages_o>'||xml_special_chars(l_state2_wages_o)||'</state2_wages_o>' ||EOL||
2441 '<state2_tax_o>'||xml_special_chars(l_state2_tax_o)||'</state2_tax_o>' ||EOL||
2442 '<local2_wages_o>'||check_negative_number(l_local2_wages_o)||'</local2_wages_o>' ||EOL||
2443 '<local2_tax_o>'||check_negative_number(l_local2_tax_o)||'</local2_tax_o>' ||EOL||
2444 '<locality2>'||xml_special_chars(l_locality2)||'</locality2>' ||EOL||
2445 '<year>'||xml_special_chars(p_year)||'</year>' ||EOL||
2446 '<amended>' || xml_special_chars(l_w2c_fields_c.amended)||'</amended>'||EOL||
2447 '<amended_date>' || xml_special_chars(l_w2c_fields_c.amended_date)||'</amended_date>'||EOL||
2448 '<print_instruction>'||'N'||'</print_instruction>' ||EOL||
2449 '<copy_no>'||'Copy B-To Be Filed with Employee''s FEDERAL Tax Return'||'</copy_no>'||
2450 '</w2c>'||EOL||
2451 /* Copy C*/
2452 '<w2c>'||EOL||
2453 '<control_number>' || xml_special_chars(l_w2c_fields_c.control_number)||'</control_number>'||EOL||
2454 '<federal_ein>' || xml_special_chars(l_w2c_fields_c.federal_ein) ||'</federal_ein>'||EOL||
2455 '<employer_name>'|| xml_special_chars(l_w2c_fields_c.employer_name)||'</employer_name>'||EOL||
2456 '<employer_address>'|| xml_special_chars(l_w2c_fields_c.employer_address)||'</employer_address>'||EOL||
2457 '<ssn_o>' || ssn_o ||'</ssn_o>'||EOL||
2458 '<ssn_c>' || ssn_c ||'</ssn_c>'||EOL||
2459 '<emp_name_o>' || emp_name_o ||'</emp_name_o>'||EOL||
2460 '<last_name_o>' || last_name_o ||'</last_name_o>'||EOL||
2461 '<emp_suffix_o>' || emp_suffix_o ||'</emp_suffix_o>'||EOL||
2462 '<emp_name_c>' || emp_name_c ||'</emp_name_c>'||EOL||
2463 '<last_name_c>' || last_name_c ||'</last_name_c>'||EOL||
2464 '<emp_suffix_c>' || emp_suffix_c ||'</emp_suffix_c>'||EOL||
2465 '<name_ssn_corrected>' || name_ssn_corrected ||'</name_ssn_corrected>'||EOL||
2466 '<employee_address>' || xml_special_chars(l_w2c_fields_c.employee_address)||'</employee_address>'||EOL||
2467 '<wages_tips_compensation_o>' || wages_tips_compensation_o ||'</wages_tips_compensation_o>'||EOL||
2468 '<wages_tips_compensation_c>' || wages_tips_compensation_c ||'</wages_tips_compensation_c>'||EOL||
2469 '<fit_withheld_o>' || fit_withheld_o ||'</fit_withheld_o>'||EOL||
2470 '<fit_withheld_c>' || fit_withheld_c ||'</fit_withheld_c>'||EOL||
2471 '<ss_wages_o>' || ss_wages_o||'</ss_wages_o>'||EOL||
2472 '<ss_wages_c>' || ss_wages_c||'</ss_wages_c>'||EOL||
2473 '<ss_withheld_o>' || ss_withheld_o||'</ss_withheld_o>'||EOL||
2474 '<ss_withheld_c>' || ss_withheld_c||'</ss_withheld_c>'||EOL||
2475 '<med_wages_o>' || med_wages_o||'</med_wages_o>'||EOL||
2476 '<med_wages_c>' || med_wages_c||'</med_wages_c>'||EOL||
2477 '<med_withheld_o>' || med_withheld_o||'</med_withheld_o>'||EOL||
2478 '<med_withheld_c>' || med_withheld_c||'</med_withheld_c>'||EOL||
2479 '<ss_tips_o>' ||ss_tips_o||'</ss_tips_o>'||EOL||
2480 '<ss_tips_c>' ||ss_tips_c||'</ss_tips_c>'||EOL||
2481 '<allocated_tips_o>' ||allocated_tips_o||'</allocated_tips_o>'||EOL||
2482 '<allocated_tips_c>' ||allocated_tips_c||'</allocated_tips_c>'||EOL||
2483 '<eic_payment_o>' || eic_payment_o||'</eic_payment_o>'||EOL||
2484 '<eic_payment_c>' || eic_payment_c||'</eic_payment_c>'||EOL||
2485 '<dependent_care_o>' ||dependent_care_o||'</dependent_care_o>'||EOL||
2486 '<dependent_care_c>' ||dependent_care_c||'</dependent_care_c>'||EOL||
2487 '<non_qual_plan_o>' || non_qual_plan_o||'</non_qual_plan_o>'||EOL||
2488 '<non_qual_plan_c>' || non_qual_plan_c||'</non_qual_plan_c>'||EOL||
2489 '<stat_employee_c>' || stat_employee_c||'</stat_employee_c>'||EOL||
2490 '<retirement_plan_c>' || retirement_plan_c||'</retirement_plan_c>'||EOL||
2491 '<sick_pay_c>' || sick_pay_c|| '</sick_pay_c>'||EOL||
2492 '<stat_employee_o>' || stat_employee_o||'</stat_employee_o>'||EOL||
2493 '<retirement_plan_o>' || retirement_plan_o||'</retirement_plan_o>'||EOL||
2494 '<sick_pay_o>' || sick_pay_o|| '</sick_pay_o>'||EOL||
2495 '<box14_codea>'||xml_special_chars(l_box14_codea) ||'</box14_codea>' ||EOL||
2496 '<box14_meaninga_c>'||xml_special_chars(l_box14_meaninga_c) ||'</box14_meaninga_c>' ||EOL||
2497 '<box14_meaninga_o>'||xml_special_chars(l_box14_meaninga_o) ||'</box14_meaninga_o>' ||EOL||
2498 '<box14_codeb>'||xml_special_chars(l_box14_codeb) ||'</box14_codeb>' ||EOL||
2499 '<box14_meaningb_c>'||xml_special_chars(l_box14_meaningb_c) ||'</box14_meaningb_c>' ||EOL||
2500 '<box14_meaningb_o>'||xml_special_chars(l_box14_meaningb_o) ||'</box14_meaningb_o>' ||EOL||
2501 '<box14_codec>'||xml_special_chars(l_box14_codec) ||'</box14_codec>' ||EOL||
2502 '<box14_meaningc_c>'||xml_special_chars(l_box14_meaningc_c) ||'</box14_meaningc_c>' ||EOL||
2503 '<box14_meaningc_o>'||xml_special_chars(l_box14_meaningc_o) ||'</box14_meaningc_o>' ||EOL||
2504 '<box12_codea>'||xml_special_chars(l_box12_codea) ||'</box12_codea>' ||EOL||
2505 '<box12_meaninga_c>'||xml_special_chars(l_box12_meaninga_c) ||'</box12_meaninga_c>' ||EOL||
2506 '<box12_meaninga_o>'||xml_special_chars(l_box12_meaninga_o) ||'</box12_meaninga_o>' ||EOL||
2507 '<box12_codeb>'||xml_special_chars(l_box12_codeb) ||'</box12_codeb>' ||EOL||
2508 '<box12_meaningb_c>'||xml_special_chars(l_box12_meaningb_c) ||'</box12_meaningb_c>' ||EOL||
2509 '<box12_meaningb_o>'||xml_special_chars(l_box12_meaningb_o) ||'</box12_meaningb_o>' ||EOL||
2510 '<box12_codec>'||xml_special_chars(l_box12_codec) ||'</box12_codec>' ||EOL||
2511 '<box12_meaningc_c>'||xml_special_chars(l_box12_meaningc_c) ||'</box12_meaningc_c>' ||EOL||
2512 '<box12_meaningc_o>'||xml_special_chars(l_box12_meaningc_o) ||'</box12_meaningc_o>' ||EOL||
2513 '<box12_coded>'||xml_special_chars(l_box12_coded) ||'</box12_coded>' ||EOL||
2514 '<box12_meaningd_c>'||xml_special_chars(l_box12_meaningd_c) ||'</box12_meaningd_c>' ||EOL||
2515 '<box12_meaningd_o>'||xml_special_chars(l_box12_meaningd_o) ||'</box12_meaningd_o>' ||EOL||
2516 '<state1_code>'||xml_special_chars(l_state1_code)||'</state1_code>' ||EOL||
2517 '<state1_ein_c>'||xml_special_chars(l_state1_ein_c)||'</state1_ein_c>' ||EOL||
2518 '<state1_wages_c>'||xml_special_chars(l_state1_wages_c)||'</state1_wages_c>' ||EOL||
2519 '<state1_tax_c>'||xml_special_chars(l_state1_tax_c)||'</state1_tax_c>' ||EOL||
2520 '<local1_wages_c>'||check_negative_number(l_local1_wages_c)||'</local1_wages_c>' ||EOL||
2521 '<local1_tax_c>'||check_negative_number(l_local1_tax_c)||'</local1_tax_c>' ||EOL||
2522 '<state1_ein_o>'||xml_special_chars(l_state1_ein_o)||'</state1_ein_o>' ||EOL||
2523 '<state1_wages_o>'||xml_special_chars(l_state1_wages_o)||'</state1_wages_o>' ||EOL||
2524 '<state1_tax_o>'||xml_special_chars(l_state1_tax_o)||'</state1_tax_o>' ||EOL||
2525 '<local1_wages_o>'||check_negative_number(l_local1_wages_o)||'</local1_wages_o>' ||EOL||
2526 '<local1_tax_o>'||check_negative_number(l_local1_tax_o)||'</local1_tax_o>' ||EOL||
2527 '<locality1>'||xml_special_chars(l_locality1)||'</locality1>' ||EOL||
2528 '<state2_code>'||xml_special_chars(l_state2_code)||'</state2_code>' ||EOL||
2529 '<state2_ein_c>'||xml_special_chars(l_state2_ein_c)||'</state2_ein_c>' ||EOL||
2530 '<state2_wages_c>'||xml_special_chars(l_state2_wages_c)||'</state2_wages_c>' ||EOL||
2531 '<state2_tax_c>'||xml_special_chars(l_state2_tax_c)||'</state2_tax_c>' ||EOL||
2532 '<local2_wages_c>'||check_negative_number(l_local2_wages_c)||'</local2_wages_c>' ||EOL||
2533 '<local2_tax_c>'||check_negative_number(l_local2_tax_c)||'</local2_tax_c>' ||EOL||
2534 '<state2_ein_o>'||xml_special_chars(l_state2_ein_o)||'</state2_ein_o>' ||EOL||
2535 '<state2_wages_o>'||xml_special_chars(l_state2_wages_o)||'</state2_wages_o>' ||EOL||
2536 '<state2_tax_o>'||xml_special_chars(l_state2_tax_o)||'</state2_tax_o>' ||EOL||
2537 '<local2_wages_o>'||check_negative_number(l_local2_wages_o)||'</local2_wages_o>' ||EOL||
2538 '<local2_tax_o>'||check_negative_number(l_local2_tax_o)||'</local2_tax_o>' ||EOL||
2539 '<locality2>'||xml_special_chars(l_locality2)||'</locality2>' ||EOL||
2540 '<year>'||xml_special_chars(p_year)||'</year>' ||EOL||
2541 '<amended>' || xml_special_chars(l_w2c_fields_c.amended)||'</amended>'||EOL||
2542 '<amended_date>' || xml_special_chars(l_w2c_fields_c.amended_date)||'</amended_date>'||EOL||
2543 '<print_instruction>'||xml_special_chars(g_print_instr)||'</print_instruction>' ||EOL||
2544 '<copy_no>'||'Copy C-For EMPLOYEE''s RECORDS'||'</copy_no>'||
2545 '</w2c>'||EOL||
2546 /* Copy 2*/
2547 '<w2c>'||EOL||
2548 '<control_number>' || xml_special_chars(l_w2c_fields_c.control_number)||'</control_number>'||EOL||
2549 '<federal_ein>' || xml_special_chars(l_w2c_fields_c.federal_ein) ||'</federal_ein>'||EOL||
2550 '<employer_name>'|| xml_special_chars(l_w2c_fields_c.employer_name)||'</employer_name>'||EOL||
2551 '<employer_address>'|| xml_special_chars(l_w2c_fields_c.employer_address)||'</employer_address>'||EOL||
2552 '<ssn_o>' || ssn_o ||'</ssn_o>'||EOL||
2553 '<ssn_c>' || ssn_c ||'</ssn_c>'||EOL||
2554 '<emp_name_o>' || emp_name_o ||'</emp_name_o>'||EOL||
2555 '<last_name_o>' || last_name_o ||'</last_name_o>'||EOL||
2556 '<emp_suffix_o>' || emp_suffix_o ||'</emp_suffix_o>'||EOL||
2557 '<emp_name_c>' || emp_name_c ||'</emp_name_c>'||EOL||
2558 '<last_name_c>' || last_name_c ||'</last_name_c>'||EOL||
2559 '<emp_suffix_c>' || emp_suffix_c ||'</emp_suffix_c>'||EOL||
2560 '<name_ssn_corrected>' || name_ssn_corrected ||'</name_ssn_corrected>'||EOL||
2561 '<employee_address>' || xml_special_chars(l_w2c_fields_c.employee_address)||'</employee_address>'||EOL||
2562 '<wages_tips_compensation_o>' || wages_tips_compensation_o ||'</wages_tips_compensation_o>'||EOL||
2563 '<wages_tips_compensation_c>' || wages_tips_compensation_c ||'</wages_tips_compensation_c>'||EOL||
2564 '<fit_withheld_o>' || fit_withheld_o ||'</fit_withheld_o>'||EOL||
2565 '<fit_withheld_c>' || fit_withheld_c ||'</fit_withheld_c>'||EOL||
2566 '<ss_wages_o>' || ss_wages_o||'</ss_wages_o>'||EOL||
2567 '<ss_wages_c>' || ss_wages_c||'</ss_wages_c>'||EOL||
2568 '<ss_withheld_o>' || ss_withheld_o||'</ss_withheld_o>'||EOL||
2569 '<ss_withheld_c>' || ss_withheld_c||'</ss_withheld_c>'||EOL||
2570 '<med_wages_o>' || med_wages_o||'</med_wages_o>'||EOL||
2571 '<med_wages_c>' || med_wages_c||'</med_wages_c>'||EOL||
2572 '<med_withheld_o>' || med_withheld_o||'</med_withheld_o>'||EOL||
2573 '<med_withheld_c>' || med_withheld_c||'</med_withheld_c>'||EOL||
2574 '<ss_tips_o>' ||ss_tips_o||'</ss_tips_o>'||EOL||
2575 '<ss_tips_c>' ||ss_tips_c||'</ss_tips_c>'||EOL||
2576 '<allocated_tips_o>' ||allocated_tips_o||'</allocated_tips_o>'||EOL||
2577 '<allocated_tips_c>' ||allocated_tips_c||'</allocated_tips_c>'||EOL||
2578 '<eic_payment_o>' || eic_payment_o||'</eic_payment_o>'||EOL||
2579 '<eic_payment_c>' || eic_payment_c||'</eic_payment_c>'||EOL||
2580 '<dependent_care_o>' ||dependent_care_o||'</dependent_care_o>'||EOL||
2581 '<dependent_care_c>' ||dependent_care_c||'</dependent_care_c>'||EOL||
2582 '<non_qual_plan_o>' || non_qual_plan_o||'</non_qual_plan_o>'||EOL||
2583 '<non_qual_plan_c>' || non_qual_plan_c||'</non_qual_plan_c>'||EOL||
2584 '<stat_employee_c>' || stat_employee_c||'</stat_employee_c>'||EOL||
2585 '<retirement_plan_c>' || retirement_plan_c||'</retirement_plan_c>'||EOL||
2586 '<sick_pay_c>' || sick_pay_c|| '</sick_pay_c>'||EOL||
2587 '<stat_employee_o>' || stat_employee_o||'</stat_employee_o>'||EOL||
2588 '<retirement_plan_o>' || retirement_plan_o||'</retirement_plan_o>'||EOL||
2589 '<sick_pay_o>' || sick_pay_o|| '</sick_pay_o>'||EOL||
2590 '<box14_codea>'||xml_special_chars(l_box14_codea) ||'</box14_codea>' ||EOL||
2591 '<box14_meaninga_c>'||xml_special_chars(l_box14_meaninga_c) ||'</box14_meaninga_c>' ||EOL||
2592 '<box14_meaninga_o>'||xml_special_chars(l_box14_meaninga_o) ||'</box14_meaninga_o>' ||EOL||
2593 '<box14_codeb>'||xml_special_chars(l_box14_codeb) ||'</box14_codeb>' ||EOL||
2594 '<box14_meaningb_c>'||xml_special_chars(l_box14_meaningb_c) ||'</box14_meaningb_c>' ||EOL||
2595 '<box14_meaningb_o>'||xml_special_chars(l_box14_meaningb_o) ||'</box14_meaningb_o>' ||EOL||
2596 '<box14_codec>'||xml_special_chars(l_box14_codec) ||'</box14_codec>' ||EOL||
2597 '<box14_meaningc_c>'||xml_special_chars(l_box14_meaningc_c) ||'</box14_meaningc_c>' ||EOL||
2598 '<box14_meaningc_o>'||xml_special_chars(l_box14_meaningc_o) ||'</box14_meaningc_o>' ||EOL||
2599 '<box12_codea>'||xml_special_chars(l_box12_codea) ||'</box12_codea>' ||EOL||
2600 '<box12_meaninga_c>'||xml_special_chars(l_box12_meaninga_c) ||'</box12_meaninga_c>' ||EOL||
2601 '<box12_meaninga_o>'||xml_special_chars(l_box12_meaninga_o) ||'</box12_meaninga_o>' ||EOL||
2602 '<box12_codeb>'||xml_special_chars(l_box12_codeb) ||'</box12_codeb>' ||EOL||
2603 '<box12_meaningb_c>'||xml_special_chars(l_box12_meaningb_c) ||'</box12_meaningb_c>' ||EOL||
2604 '<box12_meaningb_o>'||xml_special_chars(l_box12_meaningb_o) ||'</box12_meaningb_o>' ||EOL||
2605 '<box12_codec>'||xml_special_chars(l_box12_codec) ||'</box12_codec>' ||EOL||
2606 '<box12_meaningc_c>'||xml_special_chars(l_box12_meaningc_c) ||'</box12_meaningc_c>' ||EOL||
2607 '<box12_meaningc_o>'||xml_special_chars(l_box12_meaningc_o) ||'</box12_meaningc_o>' ||EOL||
2608 '<box12_coded>'||xml_special_chars(l_box12_coded) ||'</box12_coded>' ||EOL||
2609 '<box12_meaningd_c>'||xml_special_chars(l_box12_meaningd_c) ||'</box12_meaningd_c>' ||EOL||
2610 '<box12_meaningd_o>'||xml_special_chars(l_box12_meaningd_o) ||'</box12_meaningd_o>' ||EOL||
2611 '<state1_code>'||xml_special_chars(l_state1_code)||'</state1_code>' ||EOL||
2612 '<state1_ein_c>'||xml_special_chars(l_state1_ein_c)||'</state1_ein_c>' ||EOL||
2613 '<state1_wages_c>'||xml_special_chars(l_state1_wages_c)||'</state1_wages_c>' ||EOL||
2614 '<state1_tax_c>'||xml_special_chars(l_state1_tax_c)||'</state1_tax_c>' ||EOL||
2615 '<local1_wages_c>'||check_negative_number(l_local1_wages_c)||'</local1_wages_c>' ||EOL||
2616 '<local1_tax_c>'||check_negative_number(l_local1_tax_c)||'</local1_tax_c>' ||EOL||
2617 '<state1_ein_o>'||xml_special_chars(l_state1_ein_o)||'</state1_ein_o>' ||EOL||
2618 '<state1_wages_o>'||xml_special_chars(l_state1_wages_o)||'</state1_wages_o>' ||EOL||
2619 '<state1_tax_o>'||xml_special_chars(l_state1_tax_o)||'</state1_tax_o>' ||EOL||
2620 '<local1_wages_o>'||check_negative_number(l_local1_wages_o)||'</local1_wages_o>' ||EOL||
2621 '<local1_tax_o>'||check_negative_number(l_local1_tax_o)||'</local1_tax_o>' ||EOL||
2622 '<locality1>'||xml_special_chars(l_locality1)||'</locality1>' ||EOL||
2623 '<state2_code>'||xml_special_chars(l_state2_code)||'</state2_code>' ||EOL||
2624 '<state2_ein_c>'||xml_special_chars(l_state2_ein_c)||'</state2_ein_c>' ||EOL||
2625 '<state2_wages_c>'||xml_special_chars(l_state2_wages_c)||'</state2_wages_c>' ||EOL||
2626 '<state2_tax_c>'||xml_special_chars(l_state2_tax_c)||'</state2_tax_c>' ||EOL||
2627 '<local2_wages_c>'||check_negative_number(l_local2_wages_c)||'</local2_wages_c>' ||EOL||
2628 '<local2_tax_c>'||check_negative_number(l_local2_tax_c)||'</local2_tax_c>' ||EOL||
2629 '<state2_ein_o>'||xml_special_chars(l_state2_ein_o)||'</state2_ein_o>' ||EOL||
2630 '<state2_wages_o>'||xml_special_chars(l_state2_wages_o)||'</state2_wages_o>' ||EOL||
2631 '<state2_tax_o>'||xml_special_chars(l_state2_tax_o)||'</state2_tax_o>' ||EOL||
2632 '<local2_wages_o>'||check_negative_number(l_local2_wages_o)||'</local2_wages_o>' ||EOL||
2633 '<local2_tax_o>'||check_negative_number(l_local2_tax_o)||'</local2_tax_o>' ||EOL||
2634 '<locality2>'||xml_special_chars(l_locality2)||'</locality2>' ||EOL||
2635 '<year>'||xml_special_chars(p_year)||'</year>' ||EOL||
2636 '<amended>' || xml_special_chars(l_w2c_fields_c.amended)||'</amended>'||EOL||
2637 '<amended_date>' || xml_special_chars(l_w2c_fields_c.amended_date)||'</amended_date>'||EOL||
2638 '<print_instruction>'||'N'||'</print_instruction>' ||EOL||
2639 '<copy_no>'||'Copy 2-To Be Filed with Employee''s State, City, or Local Income Tax Return'||'</copy_no>'||
2640 '</w2c>'||EOL||
2641 /* Another Copy 2*/
2642 '<w2c>'||EOL||
2643 '<control_number>' || xml_special_chars(l_w2c_fields_c.control_number)||'</control_number>'||EOL||
2644 '<federal_ein>' || xml_special_chars(l_w2c_fields_c.federal_ein) ||'</federal_ein>'||EOL||
2645 '<employer_name>'|| xml_special_chars(l_w2c_fields_c.employer_name)||'</employer_name>'||EOL||
2646 '<employer_address>'|| xml_special_chars(l_w2c_fields_c.employer_address)||'</employer_address>'||EOL||
2647 '<ssn_o>' || ssn_o ||'</ssn_o>'||EOL||
2648 '<ssn_c>' || ssn_c ||'</ssn_c>'||EOL||
2649 '<emp_name_o>' || emp_name_o ||'</emp_name_o>'||EOL||
2650 '<last_name_o>' || last_name_o ||'</last_name_o>'||EOL||
2651 '<emp_suffix_o>' || emp_suffix_o ||'</emp_suffix_o>'||EOL||
2652 '<emp_name_c>' || emp_name_c ||'</emp_name_c>'||EOL||
2653 '<last_name_c>' || last_name_c ||'</last_name_c>'||EOL||
2654 '<emp_suffix_c>' || emp_suffix_c ||'</emp_suffix_c>'||EOL||
2655 '<name_ssn_corrected>' || name_ssn_corrected ||'</name_ssn_corrected>'||EOL||
2656 '<employee_address>' || xml_special_chars(l_w2c_fields_c.employee_address)||'</employee_address>'||EOL||
2657 '<wages_tips_compensation_o>' || wages_tips_compensation_o ||'</wages_tips_compensation_o>'||EOL||
2658 '<wages_tips_compensation_c>' || wages_tips_compensation_c ||'</wages_tips_compensation_c>'||EOL||
2659 '<fit_withheld_o>' || fit_withheld_o ||'</fit_withheld_o>'||EOL||
2660 '<fit_withheld_c>' || fit_withheld_c ||'</fit_withheld_c>'||EOL||
2661 '<ss_wages_o>' || ss_wages_o||'</ss_wages_o>'||EOL||
2662 '<ss_wages_c>' || ss_wages_c||'</ss_wages_c>'||EOL||
2663 '<ss_withheld_o>' || ss_withheld_o||'</ss_withheld_o>'||EOL||
2664 '<ss_withheld_c>' || ss_withheld_c||'</ss_withheld_c>'||EOL||
2665 '<med_wages_o>' || med_wages_o||'</med_wages_o>'||EOL||
2666 '<med_wages_c>' || med_wages_c||'</med_wages_c>'||EOL||
2667 '<med_withheld_o>' || med_withheld_o||'</med_withheld_o>'||EOL||
2668 '<med_withheld_c>' || med_withheld_c||'</med_withheld_c>'||EOL||
2669 '<ss_tips_o>' ||ss_tips_o||'</ss_tips_o>'||EOL||
2670 '<ss_tips_c>' ||ss_tips_c||'</ss_tips_c>'||EOL||
2671 '<allocated_tips_o>' ||allocated_tips_o||'</allocated_tips_o>'||EOL||
2672 '<allocated_tips_c>' ||allocated_tips_c||'</allocated_tips_c>'||EOL||
2673 '<eic_payment_o>' || eic_payment_o||'</eic_payment_o>'||EOL||
2674 '<eic_payment_c>' || eic_payment_c||'</eic_payment_c>'||EOL||
2675 '<dependent_care_o>' ||dependent_care_o||'</dependent_care_o>'||EOL||
2676 '<dependent_care_c>' ||dependent_care_c||'</dependent_care_c>'||EOL||
2677 '<non_qual_plan_o>' || non_qual_plan_o||'</non_qual_plan_o>'||EOL||
2678 '<non_qual_plan_c>' || non_qual_plan_c||'</non_qual_plan_c>'||EOL||
2679 '<stat_employee_c>' || stat_employee_c||'</stat_employee_c>'||EOL||
2680 '<retirement_plan_c>' || retirement_plan_c||'</retirement_plan_c>'||EOL||
2681 '<sick_pay_c>' || sick_pay_c|| '</sick_pay_c>'||EOL||
2682 '<stat_employee_o>' || stat_employee_o||'</stat_employee_o>'||EOL||
2683 '<retirement_plan_o>' || retirement_plan_o||'</retirement_plan_o>'||EOL||
2684 '<sick_pay_o>' || sick_pay_o|| '</sick_pay_o>'||EOL||
2685 '<box14_codea>'||xml_special_chars(l_box14_codea) ||'</box14_codea>' ||EOL||
2686 '<box14_meaninga_c>'||xml_special_chars(l_box14_meaninga_c) ||'</box14_meaninga_c>' ||EOL||
2687 '<box14_meaninga_o>'||xml_special_chars(l_box14_meaninga_o) ||'</box14_meaninga_o>' ||EOL||
2688 '<box14_codeb>'||xml_special_chars(l_box14_codeb) ||'</box14_codeb>' ||EOL||
2689 '<box14_meaningb_c>'||xml_special_chars(l_box14_meaningb_c) ||'</box14_meaningb_c>' ||EOL||
2690 '<box14_meaningb_o>'||xml_special_chars(l_box14_meaningb_o) ||'</box14_meaningb_o>' ||EOL||
2691 '<box14_codec>'||xml_special_chars(l_box14_codec) ||'</box14_codec>' ||EOL||
2692 '<box14_meaningc_c>'||xml_special_chars(l_box14_meaningc_c) ||'</box14_meaningc_c>' ||EOL||
2693 '<box14_meaningc_o>'||xml_special_chars(l_box14_meaningc_o) ||'</box14_meaningc_o>' ||EOL||
2694 '<box12_codea>'||xml_special_chars(l_box12_codea) ||'</box12_codea>' ||EOL||
2695 '<box12_meaninga_c>'||xml_special_chars(l_box12_meaninga_c) ||'</box12_meaninga_c>' ||EOL||
2696 '<box12_meaninga_o>'||xml_special_chars(l_box12_meaninga_o) ||'</box12_meaninga_o>' ||EOL||
2697 '<box12_codeb>'||xml_special_chars(l_box12_codeb) ||'</box12_codeb>' ||EOL||
2698 '<box12_meaningb_c>'||xml_special_chars(l_box12_meaningb_c) ||'</box12_meaningb_c>' ||EOL||
2699 '<box12_meaningb_o>'||xml_special_chars(l_box12_meaningb_o) ||'</box12_meaningb_o>' ||EOL||
2700 '<box12_codec>'||xml_special_chars(l_box12_codec) ||'</box12_codec>' ||EOL||
2701 '<box12_meaningc_c>'||xml_special_chars(l_box12_meaningc_c) ||'</box12_meaningc_c>' ||EOL||
2702 '<box12_meaningc_o>'||xml_special_chars(l_box12_meaningc_o) ||'</box12_meaningc_o>' ||EOL||
2703 '<box12_coded>'||xml_special_chars(l_box12_coded) ||'</box12_coded>' ||EOL||
2704 '<box12_meaningd_c>'||xml_special_chars(l_box12_meaningd_c) ||'</box12_meaningd_c>' ||EOL||
2705 '<box12_meaningd_o>'||xml_special_chars(l_box12_meaningd_o) ||'</box12_meaningd_o>' ||EOL||
2706 '<state1_code>'||xml_special_chars(l_state1_code)||'</state1_code>' ||EOL||
2707 '<state1_ein_c>'||xml_special_chars(l_state1_ein_c)||'</state1_ein_c>' ||EOL||
2708 '<state1_wages_c>'||xml_special_chars(l_state1_wages_c)||'</state1_wages_c>' ||EOL||
2709 '<state1_tax_c>'||xml_special_chars(l_state1_tax_c)||'</state1_tax_c>' ||EOL||
2710 '<local1_wages_c>'||check_negative_number(l_local1_wages_c)||'</local1_wages_c>' ||EOL||
2711 '<local1_tax_c>'||check_negative_number(l_local1_tax_c)||'</local1_tax_c>' ||EOL||
2712 '<state1_ein_o>'||xml_special_chars(l_state1_ein_o)||'</state1_ein_o>' ||EOL||
2713 '<state1_wages_o>'||xml_special_chars(l_state1_wages_o)||'</state1_wages_o>' ||EOL||
2714 '<state1_tax_o>'||xml_special_chars(l_state1_tax_o)||'</state1_tax_o>' ||EOL||
2715 '<local1_wages_o>'||check_negative_number(l_local1_wages_o)||'</local1_wages_o>' ||EOL||
2716 '<local1_tax_o>'||check_negative_number(l_local1_tax_o)||'</local1_tax_o>' ||EOL||
2717 '<locality1>'||xml_special_chars(l_locality1)||'</locality1>' ||EOL||
2718 '<state2_code>'||xml_special_chars(l_state2_code)||'</state2_code>' ||EOL||
2719 '<state2_ein_c>'||xml_special_chars(l_state2_ein_c)||'</state2_ein_c>' ||EOL||
2720 '<state2_wages_c>'||xml_special_chars(l_state2_wages_c)||'</state2_wages_c>' ||EOL||
2721 '<state2_tax_c>'||xml_special_chars(l_state2_tax_c)||'</state2_tax_c>' ||EOL||
2722 '<local2_wages_c>'||check_negative_number(l_local2_wages_c)||'</local2_wages_c>' ||EOL||
2723 '<local2_tax_c>'||check_negative_number(l_local2_tax_c)||'</local2_tax_c>' ||EOL||
2724 '<state2_ein_o>'||xml_special_chars(l_state2_ein_o)||'</state2_ein_o>' ||EOL||
2725 '<state2_wages_o>'||xml_special_chars(l_state2_wages_o)||'</state2_wages_o>' ||EOL||
2726 '<state2_tax_o>'||xml_special_chars(l_state2_tax_o)||'</state2_tax_o>' ||EOL||
2727 '<local2_wages_o>'||check_negative_number(l_local2_wages_o)||'</local2_wages_o>' ||EOL||
2728 '<local2_tax_o>'||check_negative_number(l_local2_tax_o)||'</local2_tax_o>' ||EOL||
2729 '<locality2>'||xml_special_chars(l_locality2)||'</locality2>' ||EOL||
2730 '<year>'||xml_special_chars(p_year)||'</year>' ||EOL||
2731 '<amended>' || xml_special_chars(l_w2c_fields_c.amended)||'</amended>'||EOL||
2732 '<amended_date>' || xml_special_chars(l_w2c_fields_c.amended_date)||'</amended_date>'||EOL||
2733 '<print_instruction>'||'N'||'</print_instruction>' ||EOL||
2734 '<copy_no>'||'Copy 2-To Be Filed with Employee''s State, City, or Local Income Tax Return'||'</copy_no>'||
2735 '</w2c>'||EOL||
2736 '</xapi:data>'||EOL;
2737
2738
2739
2740 hr_utility.trace('one set XML string ' ||l_xml_string);
2741 is_temp := dbms_lob.istemporary(l_xml_blob);
2742 hr_utility.trace('Istemporary(l_xml_blob) ' ||is_temp );
2743
2744 IF is_temp = 1 THEN
2745 DBMS_LOB.FREETEMPORARY(l_xml_blob);
2746 END IF;
2747
2748 dbms_lob.createtemporary(l_xml_blob,false,DBMS_LOB.CALL);
2749 dbms_lob.open(l_xml_blob,dbms_lob.lob_readwrite);
2750 hr_utility.trace('OPENED l_xml_blob ' );
2751
2752 raw_data:=utl_raw.cast_to_raw(l_xml_string);
2753 text_size:=utl_raw.length(raw_data);
2754
2755 dbms_lob.writeappend(l_xml_blob,text_size,raw_data);
2756
2757 hr_utility.trace('Get Length l_xml_clob ' ||dbms_lob.getlength(l_xml_blob) );
2758 dbms_lob.close(l_xml_blob);
2759 return l_xml_blob;
2760 exception
2761 when OTHERS then
2762 dbms_lob.close(l_xml_blob);
2763 hr_utility.trace('sqleerm ' || sqlerrm);
2764 HR_UTILITY.RAISE_ERROR;
2765
2766 end create_xml_string;
2767 --} end create_xml_string
2768
2769 Function fetch_w2c_xml(p_assignment_action_id Number,
2770 p_prev_assignment_action_id Number,
2771 p_tax_unit_id NUMBER,
2772 p_year NUMBER,
2773 p_error_msg out nocopy VARCHAR2,
2774 p_is_SS boolean)
2775 return BLOB
2776 is
2777 l_xml_blob BLOB ;
2778 l_out_create_xml BLOB;
2779
2780 l_box14_codea VARCHAR2(100);
2781 l_box14_meaninga_c VARCHAR2(100);
2782 l_box14_meaninga_o VARCHAR2(100);
2783
2784 l_box14_codeb VARCHAR2(100);
2785 l_box14_meaningb_c VARCHAR2(100);
2786 l_box14_meaningb_o VARCHAR2(100);
2787
2788 l_box14_codec VARCHAR2(100);
2789 l_box14_meaningc_c VARCHAR2(100);
2790 l_box14_meaningc_o VARCHAR2(100);
2791
2792
2793 l_box12_codea VARCHAR2(100);
2794 l_box12_meaninga_c VARCHAR2(100);
2795 l_box12_meaninga_o VARCHAR2(100);
2796
2797 l_box12_codeb VARCHAR2(100);
2798 l_box12_meaningb_c VARCHAR2(100);
2799 l_box12_meaningb_o VARCHAR2(100);
2800
2801 l_box12_codec VARCHAR2(100);
2802 l_box12_meaningc_c VARCHAR2(100);
2803 l_box12_meaningc_o VARCHAR2(100);
2804
2805 l_box12_coded VARCHAR2(100);
2806 l_box12_meaningd_c VARCHAR2(100);
2807 l_box12_meaningd_o VARCHAR2(100);
2808
2809
2810 l_state1_code VARCHAR2(100);
2811 l_state1_ein_c VARCHAR2(100);
2812 l_state1_wages_c VARCHAR2(100);
2813 l_state1_tax_c VARCHAR2(100);
2814 l_state1_ein_o VARCHAR2(100);
2815 l_state1_wages_o VARCHAR2(100);
2816 l_state1_tax_o VARCHAR2(100);
2817
2818 l_local1_wages_c VARCHAR2(100);
2819 l_local1_tax_c VARCHAR2(100);
2820 l_local1_wages_o VARCHAR2(100);
2821 l_local1_tax_o VARCHAR2(100);
2822 l_locality1 VARCHAR2(100);
2823
2824
2825 l_state2_code VARCHAR2(100);
2826 l_state2_ein_c VARCHAR2(100);
2827 l_state2_wages_c VARCHAR2(100);
2828 l_state2_tax_c VARCHAR2(100);
2829 l_state2_ein_o VARCHAR2(100);
2830 l_state2_wages_o VARCHAR2(100);
2831 l_state2_tax_o VARCHAR2(100);
2832
2833 l_local2_wages_c VARCHAR2(100);
2834 l_local2_tax_c VARCHAR2(100);
2835 l_local2_wages_o VARCHAR2(100);
2836 l_local2_tax_o VARCHAR2(100);
2837 l_locality2 VARCHAR2(100);
2838
2839 l_b14_total_count number;
2840 l_b14_count_completed number;
2841 l_b12_total_count number;
2842 l_b12_count_completed number;
2843 l_sl_total_count number;
2844 l_sl_count_completed number;
2845 l_local_total_count number;
2846 l_local_count_completed number;
2847 l_state_local_count number;
2848 l_state_local_total_count number;
2849 l_state_local_count_completed number;
2850 l_w2c_set_cnt number;
2851 l_is_temp_xml_string VARCHAR2(2);
2852 lv_full_name per_all_people_f.full_name%type;
2853 lv_report_type pay_payroll_actions.report_type%TYPE ;
2854 lv_message varchar2(350);
2855 lv_record_name varchar2(50);
2856
2857 procedure compare_data(l_w2c_fields_o l_w2c_fields_rec,
2858 l_w2c_fields_c l_w2c_fields_rec)
2859 is
2860
2861 begin
2862
2863 name_ssn_corrected:= 'N';
2864 g_no_corrections :=0;
2865
2866 if NVL(l_w2c_fields_c.emp_name,'XX') <> nvl(l_w2c_fields_o.emp_name,'XX') then
2867 emp_name_c := xml_special_chars(l_w2c_fields_c.emp_name);
2868 emp_name_o := xml_special_chars(l_w2c_fields_o.emp_name);
2869 name_ssn_corrected:= 'Y';
2870 g_no_corrections := g_no_corrections +1;
2871 else
2872 emp_name_c := xml_special_chars(l_w2c_fields_c.emp_name);
2873 emp_name_o := null;
2874 end if;
2875
2876 if nvl(l_w2c_fields_c.last_name,'XX') <> nvl(l_w2c_fields_o.last_name,'XX') then
2877 last_name_c := xml_special_chars(l_w2c_fields_c.last_name);
2878 last_name_o := xml_special_chars(l_w2c_fields_o.last_name);
2879 name_ssn_corrected:= 'Y';
2880 g_no_corrections := g_no_corrections +1;
2881 else
2882 last_name_c := xml_special_chars(l_w2c_fields_c.last_name);
2883 last_name_o := null;
2884 end if;
2885
2886 if nvl(l_w2c_fields_c.emp_suffix,'X') <> nvl(l_w2c_fields_o.emp_suffix,'X') then
2887 emp_suffix_c := xml_special_chars(l_w2c_fields_c.emp_suffix);
2888 emp_suffix_o := xml_special_chars(l_w2c_fields_o.emp_suffix);
2889 name_ssn_corrected:= 'Y';
2890 g_no_corrections := g_no_corrections +1;
2891 else
2892 emp_suffix_c := xml_special_chars(l_w2c_fields_c.emp_suffix);
2893 emp_suffix_o := null;
2894 end if;
2895
2896 if name_ssn_corrected = 'Y' then
2897 emp_name_o := xml_special_chars(l_w2c_fields_o.emp_name);
2898 last_name_o := xml_special_chars(l_w2c_fields_o.last_name);
2899 emp_suffix_o := xml_special_chars(l_w2c_fields_o.emp_suffix);
2900 end if;
2901
2902 if nvl(l_w2c_fields_c.ssn,'XX') <> nvl(l_w2c_fields_o.ssn,'XX') then
2903 ssn_c := xml_special_chars(l_w2c_fields_c.ssn);
2904 ssn_o := xml_special_chars(l_w2c_fields_o.ssn);
2905 name_ssn_corrected:= 'Y';
2906 g_no_corrections := g_no_corrections +1;
2907 else
2908 ssn_c := xml_special_chars(l_w2c_fields_c.ssn);
2909 ssn_o := null;
2910 end if;
2911
2912 if nvl(l_w2c_fields_c.wages_tips_compensation,0) <> nvl(l_w2c_fields_o.wages_tips_compensation,0) then
2913 wages_tips_compensation_c := nvl(check_negative_number(l_w2c_fields_c.wages_tips_compensation),'-0-');
2914 wages_tips_compensation_o := nvl(check_negative_number(l_w2c_fields_o.wages_tips_compensation),'-0-');
2915 g_no_corrections := g_no_corrections +1;
2916 else
2917 wages_tips_compensation_c := null;
2918 wages_tips_compensation_o := null;
2919 end if;
2920
2921 if nvl(l_w2c_fields_c.fit_withheld,0) <> nvl(l_w2c_fields_o.fit_withheld,0) then
2922 fit_withheld_c := nvl(check_negative_number(l_w2c_fields_c.fit_withheld),'-0-');
2923 fit_withheld_o := nvl(check_negative_number(l_w2c_fields_o.fit_withheld),'-0-');
2924 g_no_corrections := g_no_corrections +1;
2925 else
2926 fit_withheld_c := null;
2927 fit_withheld_o := null;
2928 end if;
2929
2930 if nvl(l_w2c_fields_c.ss_wages,0) <> nvl(l_w2c_fields_o.ss_wages,0) then
2931 ss_wages_c := nvl(check_negative_number(l_w2c_fields_c.ss_wages),'-0-');
2932 ss_wages_o := nvl(check_negative_number(l_w2c_fields_o.ss_wages),'-0-');
2933 g_no_corrections := g_no_corrections +1;
2934 else
2935 ss_wages_c := null;
2936 ss_wages_o := null;
2937 end if;
2938
2939 if nvl(l_w2c_fields_c.ss_withheld,0) <> nvl(l_w2c_fields_o.ss_withheld,0) then
2940 ss_withheld_c := nvl(check_negative_number(l_w2c_fields_c.ss_withheld),'-0-');
2941 ss_withheld_o := nvl(check_negative_number(l_w2c_fields_o.ss_withheld),'-0-');
2942 g_no_corrections := g_no_corrections +1;
2943 else
2944 ss_withheld_c := null;
2945 ss_withheld_o := null;
2946 end if;
2947
2948 if nvl(l_w2c_fields_c.med_wages,0) <> nvl(l_w2c_fields_o.med_wages,0) then
2949 med_wages_c := nvl(check_negative_number(l_w2c_fields_c.med_wages),'-0-');
2950 med_wages_o := nvl(check_negative_number(l_w2c_fields_o.med_wages),'-0-');
2951 g_no_corrections := g_no_corrections +1;
2952 else
2953 med_wages_c := null;
2954 med_wages_o := null;
2955 end if;
2956
2957 if nvl(l_w2c_fields_c.med_withheld,0) <> nvl(l_w2c_fields_o.med_withheld,0) then
2958 med_withheld_c := nvl(check_negative_number(l_w2c_fields_c.med_withheld),'-0-');
2959 med_withheld_o := nvl(check_negative_number(l_w2c_fields_o.med_withheld),'-0-');
2960 g_no_corrections := g_no_corrections +1;
2961 else
2962 med_withheld_o := null;
2963 med_withheld_c := null;
2964 end if;
2965
2966 if nvl(l_w2c_fields_c.ss_tips,0) <> nvl(l_w2c_fields_o.ss_tips,0) then
2967 ss_tips_c := nvl(check_negative_number(l_w2c_fields_c.ss_tips),'-0-');
2968 ss_tips_o := nvl(check_negative_number(l_w2c_fields_o.ss_tips),'-0-');
2969 g_no_corrections := g_no_corrections +1;
2970 else
2971 ss_tips_c := null;
2972 ss_tips_o := null;
2973 end if;
2974
2975 if nvl(l_w2c_fields_c.allocated_tips,0) <> nvl(l_w2c_fields_o.allocated_tips,0) then
2976 allocated_tips_c := nvl(check_negative_number(l_w2c_fields_c.allocated_tips),'-0-');
2977 allocated_tips_o := nvl(check_negative_number(l_w2c_fields_o.allocated_tips),'-0-');
2978 g_no_corrections := g_no_corrections +1;
2979 else
2980 allocated_tips_c := null;
2981 allocated_tips_o := null;
2982 end if;
2983
2984 if nvl(l_w2c_fields_c.eic_payment,0) <> nvl(l_w2c_fields_o.eic_payment,0) then
2985 eic_payment_c := nvl(check_negative_number(l_w2c_fields_c.eic_payment),'-0-');
2986 eic_payment_o := nvl(check_negative_number(l_w2c_fields_o.eic_payment),'-0-');
2987 g_no_corrections := g_no_corrections +1;
2988 else
2989 eic_payment_c := null;
2990 eic_payment_o := null;
2991 end if;
2992
2993 if nvl(l_w2c_fields_c.dependent_care,0) <> nvl(l_w2c_fields_o.dependent_care,0) then
2994 dependent_care_c := nvl(check_negative_number(l_w2c_fields_c.dependent_care),'-0-');
2995 dependent_care_o := nvl(check_negative_number(l_w2c_fields_o.dependent_care),'-0-');
2996 g_no_corrections := g_no_corrections +1;
2997 else
2998 dependent_care_c := null;
2999 dependent_care_o := null;
3000 end if;
3001
3002 if nvl(l_w2c_fields_c.non_qual_plan,0) <> nvl(l_w2c_fields_o.non_qual_plan,0) then
3003 non_qual_plan_c := nvl(check_negative_number(l_w2c_fields_c.non_qual_plan),'-0-');
3004 non_qual_plan_o := nvl(check_negative_number(l_w2c_fields_o.non_qual_plan),'-0-');
3005 g_no_corrections := g_no_corrections +1;
3006 else
3007 non_qual_plan_c := null;
3008 non_qual_plan_o := null;
3009 end if;
3010
3011 if nvl(l_w2c_fields_c.stat_employee,'X') <> nvl(l_w2c_fields_o.stat_employee,'X') then
3012 stat_employee_c := xml_special_chars(l_w2c_fields_c.stat_employee);
3013 stat_employee_o := xml_special_chars(l_w2c_fields_o.stat_employee);
3014 g_no_corrections := g_no_corrections +1;
3015 else
3016 stat_employee_c := 'N';
3017 stat_employee_o := 'N';
3018 end if;
3019
3020 if nvl(l_w2c_fields_c.retirement_plan,'X') <> nvl(l_w2c_fields_o.retirement_plan,'X') then
3021 retirement_plan_c := xml_special_chars(l_w2c_fields_c.retirement_plan);
3022 retirement_plan_o := xml_special_chars(l_w2c_fields_o.retirement_plan);
3023 g_no_corrections := g_no_corrections +1;
3024 else
3025 retirement_plan_c := 'N';
3026 retirement_plan_o := 'N';
3027 end if;
3028
3029 if nvl(l_w2c_fields_c.sick_pay,'X') <> nvl(l_w2c_fields_o.sick_pay,'X') then
3030 sick_pay_c := xml_special_chars(l_w2c_fields_c.sick_pay);
3031 sick_pay_o := xml_special_chars(l_w2c_fields_o.sick_pay);
3032 g_no_corrections := g_no_corrections +1;
3033 else
3034 sick_pay_c := 'N';
3035 sick_pay_o := 'N';
3036 end if;
3037
3038 end compare_data;
3039
3040 begin
3041 hr_utility.trace('In Fetch w2c xml loop ' );
3042
3043 l_b14_total_count := 0;
3044 l_b14_count_completed := 0;
3045 l_b12_total_count := 0;
3046 l_b12_count_completed := 0;
3047 l_sl_total_count := 0;
3048 l_sl_count_completed := 0;
3049 l_local_total_count := 0;
3050 l_local_count_completed := 0;
3051 l_state_local_count := 0;
3052 l_state_local_total_count := 0;
3053 l_state_local_count_completed := 0;
3054 l_w2c_set_cnt := 0;
3055
3056 /* Imp Note:
3057 Till Box 11, data comparition will take place in compare_data procedure.
3058 For the rest, comparision will ocuur while loading the data.
3059 */
3060
3061 get_w2c_data(p_assignment_action_id,p_prev_assignment_action_id,p_tax_unit_id,p_year,p_error_msg);
3062
3063 --p_error_msg := Null; --Devlopment in progress so overriding it
3064
3065 select l_w2c_fields_c.emp_name||l_w2c_fields_c.last_name||' '||l_w2c_fields_c.emp_suffix
3066 into lv_full_name from sys.dual;
3067
3068 IF p_error_msg IS NULL THEN
3069
3070 hr_utility.trace('After get W2c data' );
3071 --Calling the compare_data to compare the data till Box 11
3072
3073 compare_data(l_w2c_fields_o,l_w2c_fields_c);
3074
3075 l_b14_total_count := l_box14_tab.count;
3076 l_b14_count_completed := 0;
3077 l_b12_total_count := l_box12_tab.count;
3078 l_b12_count_completed := 0;
3079 l_sl_total_count := l_state_tab.count;
3080 l_sl_count_completed := 0;
3081 l_local_total_count := l_local_tab.count;
3082 l_local_count_completed := 0;
3083 l_state_local_total_count := l_state_local_tab.count;
3084 l_state_local_count_completed := 0;
3085
3086 l_is_temp_xml_string := dbms_lob.istemporary(l_xml_blob);
3087 hr_utility.trace('Istemporary(l_xml_blob) ' ||l_is_temp_xml_string );
3088
3089 IF l_is_temp_xml_string = 1 THEN
3090 DBMS_LOB.FREETEMPORARY(l_xml_blob);
3091 END IF;
3092
3093 dbms_lob.createtemporary(l_xml_blob,false,DBMS_LOB.CALL);
3094 dbms_lob.open(l_xml_blob,dbms_lob.lob_readwrite);
3095
3096 IF (l_b14_total_count = 0) AND
3097 (l_b12_total_count = 0) AND
3098 --(l_sl_total_count = 0)
3099 (l_state_local_total_count = 0) THEN
3100
3101 IF (g_no_corrections > 0) THEN
3102
3103 hr_utility.trace('In l_b14_total_count and other counts =0 ' );
3104 -- dbms_lob.append(l_final_xml,p_xml_string);
3105 l_xml_blob := create_xml_string(--l_w2c_fields_o,
3106 l_w2c_fields_c,
3107 l_box14_codea,l_box14_meaninga_c,l_box14_meaninga_o,
3108 l_box14_codeb,l_box14_meaningb_c,l_box14_meaningb_o,
3109 l_box14_codec,l_box14_meaningc_c,l_box14_meaningc_o,
3110 l_box12_codea,l_box12_meaninga_c,l_box12_meaninga_o,
3111 l_box12_codeb,l_box12_meaningb_c,l_box12_meaningb_o,
3112 l_box12_codec,l_box12_meaningc_c,l_box12_meaningc_o,
3113 l_box12_coded,l_box12_meaningd_c,l_box12_meaningd_o,
3114 l_state1_code,
3115 l_state1_ein_c,l_state1_wages_c,l_state1_tax_c,
3116 l_state1_ein_o,l_state1_wages_o,l_state1_tax_o,
3117 l_local1_wages_c,l_local1_tax_c,
3118 l_local1_wages_o,l_local1_tax_o,
3119 l_locality1,
3120 l_state2_code,
3121 l_state2_ein_c,l_state2_wages_c, l_state2_tax_c,
3122 l_state2_ein_o,l_state2_wages_o, l_state2_tax_o,
3123 l_local2_wages_c,l_local2_tax_c,
3124 l_local2_wages_o,l_local2_tax_o,
3125 l_locality2,
3126 p_year);
3127 hr_utility.trace('after getting XML Blob ' );
3128
3129 ELSE --Do not create the XML, as there are no corrections, Bug 16571508
3130
3131 lv_record_name := 'W2C Report';
3132 lv_message := 'W-2c not printed for Employee: '||rtrim(substr(rtrim(ltrim(lv_full_name)),1,60))||
3133 ' (SSN: '||l_w2c_fields_c.ssn
3134 ||') as no corrections found for the employee.';
3135
3136 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
3137 pay_core_utils.push_token('record_name',lv_record_name);
3138 pay_core_utils.push_token('name_or_number',lv_full_name);
3139 pay_core_utils.push_token('description',lv_message);
3140
3141 END IF;
3142
3143 ELSE
3144
3145 --{
3146 LOOP
3147
3148 hr_utility.trace('In loop to get XML ' );
3149 hr_utility.trace('l_b14_total_count ' ||l_b14_total_count);
3150 hr_utility.trace('l_b14_count_completed ' ||l_b14_count_completed);
3151 hr_utility.trace('l_b12_total_count ' ||l_b12_total_count);
3152 hr_utility.trace('l_b12_count_completed ' ||l_b12_count_completed);
3153
3154 hr_utility.trace('l_sl_total_count ' ||l_sl_total_count);
3155 hr_utility.trace('l_sl_count_completed ' ||l_sl_count_completed);
3156 hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3157 hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3158 hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3159 hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3160 hr_utility.trace('l_state_local_tab.COUNT ' ||l_state_local_tab.COUNT);
3161
3162 EXIT WHEN
3163 ((l_b14_total_count = 0) AND
3164 (l_b12_total_count = 0 ) AND
3165 (l_state_local_count_completed = l_state_local_total_count )) ;
3166
3167 l_box14_codea := '';
3168 l_box14_meaninga_c := '';
3169 l_box14_meaninga_o := '';
3170 l_box14_codeb := '';
3171 l_box14_meaningb_c := '';
3172 l_box14_meaningb_o := '';
3173 l_box14_codec := '';
3174 l_box14_meaningc_c := '';
3175 l_box14_meaningc_o := '';
3176
3177 IF l_b14_total_count > 0 THEN
3178 hr_utility.trace('1. l_b14_total_count >0 ' ||l_b14_total_count);
3179 l_b14_count_completed := l_b14_count_completed + 1 ;
3180 hr_utility.trace('1. l_b14_count_completed ' ||l_b14_count_completed);
3181
3182 l_box14_codea := l_box14_tab(l_b14_count_completed).box14_code_c;
3183 l_box14_meaninga_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3184 l_box14_meaninga_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3185
3186 l_b14_total_count := l_b14_total_count -1;
3187 hr_utility.trace('l_box14_codea ' ||l_box14_codea);
3188 hr_utility.trace('l_box14_meaninga_c ' ||l_box14_meaninga_c);
3189 hr_utility.trace('l_box14_meaninga_o ' ||l_box14_meaninga_o);
3190 END IF;
3191
3192 IF l_b14_total_count > 0 THEN
3193 hr_utility.trace('2. l_b14_total_count >0 ' ||l_b14_total_count);
3194 l_b14_count_completed := l_b14_count_completed + 1 ;
3195 hr_utility.trace('2. l_b14_count_completed ' ||l_b14_count_completed);
3196
3197 l_box14_codeb := l_box14_tab(l_b14_count_completed).box14_code_c;
3198 l_box14_meaningb_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3199 l_box14_meaningb_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3200
3201 l_b14_total_count := l_b14_total_count - 1;
3202 hr_utility.trace('l_box14_codeb ' ||l_box14_codeb);
3203 hr_utility.trace('l_box14_meaningb_c ' ||l_box14_meaningb_c);
3204 hr_utility.trace('l_box14_meaningb_o ' ||l_box14_meaningb_o);
3205
3206 END IF;
3207
3208 IF l_b14_total_count > 0 THEN
3209 hr_utility.trace('3. l_b14_total_count >0 ' ||l_b14_total_count);
3210
3211 l_b14_count_completed := l_b14_count_completed + 1 ;
3212 hr_utility.trace('3. l_b14_count_completed ' ||l_b14_count_completed);
3213
3214 l_box14_codec := l_box14_tab(l_b14_count_completed).box14_code_c;
3215 l_box14_meaningc_c := l_box14_tab(l_b14_count_completed).box14_meaning_c;
3216 l_box14_meaningc_o := l_box14_tab(l_b14_count_completed).box14_meaning_o;
3217
3218 l_b14_total_count := l_b14_total_count -1;
3219 hr_utility.trace('l_box14_codec ' ||l_box14_codec);
3220 hr_utility.trace('l_box14_meaningc_c ' ||l_box14_meaningc_c);
3221 hr_utility.trace('l_box14_meaningc_o ' ||l_box14_meaningc_o);
3222
3223 END IF;
3224
3225 l_box12_codea := '';
3226 l_box12_meaninga_c := '';
3227 l_box12_meaninga_o := '';
3228 l_box12_codeb := '';
3229 l_box12_meaningb_c := '';
3230 l_box12_meaningb_o := '';
3231 l_box12_codec := '';
3232 l_box12_meaningc_c := '';
3233 l_box12_meaningc_o := '';
3234 l_box12_coded := '';
3235 l_box12_meaningd_c := '';
3236 l_box12_meaningd_o := '';
3237
3238 IF l_b12_total_count > 0 THEN
3239 hr_utility.trace('1. l_b12_total_count >0 ' ||l_b12_total_count);
3240 l_b12_count_completed := l_b12_count_completed + 1 ;
3241
3242 l_box12_codea := l_box12_tab(l_b12_count_completed).box12_code_c;
3243 l_box12_meaninga_c := l_box12_tab(l_b12_count_completed).box12_meaning_c;
3244 l_box12_meaninga_o := l_box12_tab(l_b12_count_completed).box12_meaning_o;
3245
3246 l_b12_total_count := l_b12_total_count -1;
3247 END IF;
3248
3249 IF l_b12_total_count > 0 THEN
3250 hr_utility.trace('2. l_b12_total_count >0 ' ||l_b12_total_count);
3251
3252 l_b12_count_completed := l_b12_count_completed + 1 ;
3253
3254 l_box12_codeb := l_box12_tab(l_b12_count_completed).box12_code_c;
3255 l_box12_meaningb_c := l_box12_tab(l_b12_count_completed).box12_meaning_c;
3256 l_box12_meaningb_o := l_box12_tab(l_b12_count_completed).box12_meaning_o;
3257
3258 l_b12_total_count := l_b12_total_count -1;
3259 END IF;
3260
3261 IF l_b12_total_count > 0 THEN
3262 hr_utility.trace('3. l_b12_total_count >0 ' ||l_b12_total_count);
3263
3264 l_b12_count_completed := l_b12_count_completed + 1 ;
3265
3266 l_box12_codec := l_box12_tab(l_b12_count_completed).box12_code_c;
3267 l_box12_meaningc_c := l_box12_tab(l_b12_count_completed).box12_meaning_c;
3268 l_box12_meaningc_o := l_box12_tab(l_b12_count_completed).box12_meaning_o;
3269
3270 l_b12_total_count := l_b12_total_count -1;
3271 END IF;
3272
3273 IF l_b12_total_count > 0 THEN
3274 hr_utility.trace('4. l_b12_total_count >0 ' ||l_b12_total_count);
3275
3276 l_b12_count_completed := l_b12_count_completed + 1 ;
3277
3278 l_box12_coded := l_box12_tab(l_b12_count_completed).box12_code_c;
3279 l_box12_meaningd_c := l_box12_tab(l_b12_count_completed).box12_meaning_c;
3280 l_box12_meaningd_o := l_box12_tab(l_b12_count_completed).box12_meaning_o;
3281
3282 l_b12_total_count := l_b12_total_count -1;
3283 END IF;
3284
3285 l_state1_code := '';
3286 l_state1_ein_c := '';
3287 l_state1_wages_c := '';
3288 l_state1_tax_c := '';
3289 l_local1_wages_c := '';
3290 l_local1_tax_c := '';
3291 l_state1_ein_o := '';
3292 l_state1_wages_o := '';
3293 l_state1_tax_o := '';
3294 l_local1_wages_o := '';
3295 l_local1_tax_o := '';
3296 l_locality1 := '';
3297
3298 l_state2_code := '';
3299 l_state2_ein_c := '';
3300 l_state2_wages_c := '';
3301 l_state2_tax_c := '';
3302 l_local2_wages_c := '';
3303 l_local2_tax_c := '';
3304 l_state2_ein_o := '';
3305 l_state2_wages_o := '';
3306 l_state2_tax_o := '';
3307 l_local2_wages_o := '';
3308 l_local2_tax_o := '';
3309 l_locality2 := '';
3310
3311
3312 IF l_state_local_count_completed < l_state_local_total_count THEN
3313 hr_utility.trace('1. l_state_local_total_count >0 ' ||l_state_local_total_count);
3314
3315 l_state1_code:= l_state_local_tab(l_state_local_count_completed).state_code_c;
3316
3317 l_state1_ein_c := l_state_local_tab(l_state_local_count_completed).state_ein_c;
3318 l_state1_wages_c := l_state_local_tab(l_state_local_count_completed).state_wages_c;
3319 l_state1_tax_c := l_state_local_tab(l_state_local_count_completed).state_tax_c;
3320
3321 l_state1_ein_o := l_state_local_tab(l_state_local_count_completed).state_ein_o;
3322 l_state1_wages_o := l_state_local_tab(l_state_local_count_completed).state_wages_o;
3323 l_state1_tax_o := l_state_local_tab(l_state_local_count_completed).state_tax_o;
3324
3325 l_locality1 := l_state_local_tab(l_state_local_count_completed).locality_c;
3326 l_local1_wages_c:= l_state_local_tab(l_state_local_count_completed).locality_wages_c;
3327 l_local1_tax_c := l_state_local_tab(l_state_local_count_completed).locality_tax_c;
3328
3329 l_local1_wages_o:= l_state_local_tab(l_state_local_count_completed).locality_wages_o;
3330 l_local1_tax_o := l_state_local_tab(l_state_local_count_completed).locality_tax_o;
3331
3332 l_state_local_count_completed := l_state_local_count_completed + 1 ;
3333 -- l_state_local_total_count := l_state_local_total_count -1;
3334
3335 END IF;
3336
3337
3338 hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3339 hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3340
3341 IF l_state_local_count_completed < l_state_local_total_count THEN
3342 hr_utility.trace('2. l_state_local_total_count >0 ' ||l_state_local_total_count);
3343 hr_utility.trace('2. l_state_local_total_count >0,l_state_local_count_completed ' ||l_state_local_total_count);
3344
3345 l_state2_code:= l_state_local_tab(l_state_local_count_completed).state_code_c;
3346
3347 l_state2_ein_c := l_state_local_tab(l_state_local_count_completed).state_ein_c;
3348 l_state2_wages_c := l_state_local_tab(l_state_local_count_completed).state_wages_c;
3349 l_state2_tax_c := l_state_local_tab(l_state_local_count_completed).state_tax_c;
3350
3351 l_state2_ein_o := l_state_local_tab(l_state_local_count_completed).state_ein_o;
3352 l_state2_wages_o := l_state_local_tab(l_state_local_count_completed).state_wages_o;
3353 l_state2_tax_o := l_state_local_tab(l_state_local_count_completed).state_tax_o;
3354
3355 l_locality2 := l_state_local_tab(l_state_local_count_completed).locality_c;
3356 l_local2_wages_c:= l_state_local_tab(l_state_local_count_completed).locality_wages_c;
3357 l_local2_tax_c := l_state_local_tab(l_state_local_count_completed).locality_tax_c;
3358
3359 l_local2_wages_o:= l_state_local_tab(l_state_local_count_completed).locality_wages_o;
3360 l_local2_tax_o := l_state_local_tab(l_state_local_count_completed).locality_tax_o;
3361
3362 -- l_state_local_total_count := l_state_local_total_count -1;
3363 l_state_local_count_completed := l_state_local_count_completed + 1 ;
3364
3365 END IF;
3366
3367 hr_utility.trace('l_state_local_total_count ' ||l_state_local_total_count);
3368 hr_utility.trace('l_state_local_count_completed ' ||l_state_local_count_completed);
3369 hr_utility.trace('l_local_total_count ' ||l_local_total_count);
3370 hr_utility.trace('l_local_count_completed ' ||l_local_count_completed);
3371
3372
3373 l_w2c_set_cnt := l_w2c_set_cnt +1;
3374 IF l_w2c_set_cnt > 1 THEN
3375 l_w2c_fields_c.wages_tips_compensation := '';
3376 l_w2c_fields_c.fit_withheld := '';
3377 l_w2c_fields_c.ss_wages := '';
3378 l_w2c_fields_c.ss_withheld := '';
3379 l_w2c_fields_c.med_wages:= '';
3380 l_w2c_fields_c.med_withheld := '';
3381 l_w2c_fields_c.ss_tips := '';
3382 l_w2c_fields_c.allocated_tips := '';
3383 l_w2c_fields_c.eic_payment := '';
3384 l_w2c_fields_c.dependent_care := '';
3385 l_w2c_fields_c.non_qual_plan := '';
3386 l_w2c_fields_c.stat_employee := '';
3387 l_w2c_fields_c.retirement_plan := '';
3388 l_w2c_fields_c.sick_pay := '';
3389
3390 l_w2c_fields_o.wages_tips_compensation := '';
3391 l_w2c_fields_o.fit_withheld := '';
3392 l_w2c_fields_o.ss_wages := '';
3393 l_w2c_fields_o.ss_withheld := '';
3394 l_w2c_fields_o.med_wages:= '';
3395 l_w2c_fields_o.med_withheld := '';
3396 l_w2c_fields_o.ss_tips := '';
3397 l_w2c_fields_o.allocated_tips := '';
3398 l_w2c_fields_o.eic_payment := '';
3399 l_w2c_fields_o.dependent_care := '';
3400 l_w2c_fields_o.non_qual_plan := '';
3401 l_w2c_fields_o.stat_employee := '';
3402 l_w2c_fields_o.retirement_plan := '';
3403 l_w2c_fields_o.sick_pay := '';
3404
3405 END IF;
3406
3407 l_out_create_xml := create_xml_string(--l_w2c_fields_o,
3408 l_w2c_fields_c,
3409 l_box14_codea,l_box14_meaninga_c,l_box14_meaninga_o,
3410 l_box14_codeb,l_box14_meaningb_c,l_box14_meaningb_o,
3411 l_box14_codec,l_box14_meaningc_c,l_box14_meaningc_o,
3412 l_box12_codea,l_box12_meaninga_c,l_box12_meaninga_o,
3413 l_box12_codeb,l_box12_meaningb_c,l_box12_meaningb_o,
3414 l_box12_codec,l_box12_meaningc_c,l_box12_meaningc_o,
3415 l_box12_coded,l_box12_meaningd_c,l_box12_meaningd_o,
3416 l_state1_code,
3417 l_state1_ein_c,l_state1_wages_c,l_state1_tax_c,
3418 l_state1_ein_o,l_state1_wages_o,l_state1_tax_o,
3419 l_local1_wages_c,l_local1_tax_c,
3420 l_local1_wages_o,l_local1_tax_o,
3421 l_locality1,
3422 l_state2_code,
3423 l_state2_ein_c,l_state2_wages_c, l_state2_tax_c,
3424 l_state2_ein_o,l_state2_wages_o, l_state2_tax_o,
3425 l_local2_wages_c,l_local2_tax_c,
3426 l_local2_wages_o,l_local2_tax_o,
3427 l_locality2,
3428 p_year);
3429
3430 hr_utility.trace('After l_out_create_xml, length of LOB ' ||
3431 dbms_lob.getlength(l_out_create_xml));
3432 --IF l_xml_string is not NULL and l_out_create_xml IS NOT NULL THEN
3433
3434 dbms_lob.append(l_xml_blob,l_out_create_xml);
3435 hr_utility.trace('Length of l_xml_blob ' ||dbms_lob.getlength(l_xml_blob));
3436
3437 --ELSE
3438 -- dbms_lob.writeappend(l_xml_string,dbms_lobamount,l_out_create_xml);
3439 --END IF;
3440
3441 END LOOP;
3442 END IF;
3443 --}
3444 END IF ; /* p_error_msg is null */
3445 hr_utility.trace('XML String is ');
3446
3447 --hr_utility.trace(dbms_lob.substr(l_xml_string,,1));
3448
3449 return l_xml_blob;
3450 EXCEPTION
3451 WHEN OTHERS then
3452 hr_utility.trace('sqleerm ' || SQLERRM);
3453 END fetch_w2c_xml;
3454
3455 FUNCTION get_final_xml (p_assignment_action_id Number,
3456 p_prev_assignment_action_id Number,
3457 p_tax_unit_id NUMBER,
3458 p_year NUMBER,
3459 p_w2c_template_location VARCHAR2,
3460 p_inst_template_location VARCHAR2,
3461 p_output_location VARCHAR2,
3462 p_error_msg OUT nocopy VARCHAR2)
3463 RETURN BLOB IS
3464 p_xml_blob BLOB;
3465 l_final_xml BLOB;
3466 l_final_xml_string VARCHAR2(32000);
3467 l_last_xml_string VARCHAR2(32000);
3468 l_last_xml BLOB;
3469 l_is_temp_final_xml VARCHAR2(2);
3470 l_temp_blob BLOB;
3471
3472 BEGIN
3473 --hr_utility.trace_on(null,'w2');
3474 hr_utility.trace('Deleting PL/SQL tables');
3475
3476 l_state_local_tab.delete;
3477 l_state_tab.delete;
3478 l_local_tab.delete;
3479 l_box12_tab.delete;
3480 l_box14_tab.delete;
3481
3482 l_w2c_fields_c.amended := '';
3483 l_w2c_fields_c.amended_date := '';
3484 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3485
3486 p_xml_blob := fetch_w2c_xml(p_assignment_action_id ,
3487 p_prev_assignment_action_id,
3488 p_tax_unit_id ,
3489 p_year,
3490 p_error_msg , true);
3491
3492 hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
3493
3494 IF p_error_msg IS NULL THEN
3495 hr_utility.trace('In final XML p_xml_string ');
3496 /* hr_utility.trace('XML String '||
3497 dbms_lob.substr(p_xml_string,dbms_lob.getlength(p_xml_string),1));
3498 */
3499 l_final_xml_string :=
3500 -- '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL|| Bug 6712851
3501 '<w2c_data>'||EOL||
3502 '<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">'||EOL||
3503 '<xapi:request>'||EOL||
3504 '<xapi:delivery>'||EOL||
3505 '<xapi:filesystem output="'||p_output_location||'" />'||EOL||
3506 '</xapi:delivery>'||EOL||
3507 '<xapi:document output-type="pdf">'||EOL||
3508 -- '<xapi:template type="pdf" location="'||p_w2_template_location||'">'||EOL;
3509 '<xapi:template type="pdf" location="${templateName1}">'||EOL;
3510 -- '<xapi:template type="pdf" location="${templateName1}">'||EOL;
3511
3512
3513 hr_utility.trace('1. final 1. XML l_final_xml '||
3514 dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
3515
3516 l_last_xml_string := '</xapi:template>'||EOL||
3517 -- '<xapi:template type="pdf" location="'||p_inst_template_location||'">'||EOL||
3518 '<xapi:template type="pdf" location="${templateName2}">'||EOL||
3519 -- '<xapi:template type="pdf" location="${templateName2}">'||EOL||
3520 '<xapi:data />'|| EOL||
3521 '</xapi:template>'||EOL||
3522 '</xapi:document>'||EOL||
3523 '</xapi:request>'||EOL||
3524 '</xapi:requestset>'||EOL||
3525 '</w2c_data>';
3526
3527 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
3528 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
3529
3530 IF l_is_temp_final_xml = 1 THEN
3531 DBMS_LOB.FREETEMPORARY(l_final_xml);
3532 END IF;
3533
3534 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
3535 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
3536 l_final_xml := append_to_lob(l_final_xml_string);
3537 --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
3538
3539 hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
3540
3541 dbms_lob.append(l_final_xml,p_xml_blob);
3542
3543 --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
3544 dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
3545 dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
3546 l_temp_blob := append_to_lob(l_last_xml_string);
3547 dbms_lob.append(l_final_xml,l_temp_blob);
3548
3549 /* Added ISOPEN condition for bug 3899583 */
3550 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
3551 hr_utility.trace('Closing l_final_xml' );
3552 dbms_lob.close(l_final_xml);
3553 END IF;
3554 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3555 hr_utility.trace('Closing p_xml_blob' );
3556 dbms_lob.close(p_xml_blob);
3557 END IF;
3558 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3559 hr_utility.trace('Closing l_temp_blob' );
3560 dbms_lob.close(l_temp_blob);
3561 END IF;
3562 ELSE
3563 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
3564 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
3565 l_final_xml := append_to_lob(p_error_msg);
3566
3567 hr_utility.trace(' get final cml, p_error_msg '||p_error_msg);
3568
3569 END IF ; /* p_error_msg is null */
3570 hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
3571
3572 return l_final_xml;
3573 EXCEPTION
3574 WHEN OTHERS then
3575 /* Added ISOPEN condition for bug 3899583 */
3576 IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
3577 hr_utility.trace('Raising exception and Closing l_final_xml' );
3578 dbms_lob.close(l_final_xml);
3579 END IF;
3580 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3581 hr_utility.trace('Raising exception and Closing p_xml_string' );
3582 dbms_lob.close(p_xml_blob);
3583 END IF;
3584 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3585 hr_utility.trace('Closing l_temp_blob' );
3586 dbms_lob.close(l_temp_blob);
3587 END IF;
3588
3589
3590 hr_utility.trace('sqleerm ' || SQLERRM);
3591 raise;
3592 END get_final_xml;
3593
3594
3595 PROCEDURE fetch_w2c_xm IS
3596
3597 lc_emp_blob BLOB;
3598 l_error_msg VARCHAR2(500);
3599 l_assignment_action_id NUMBER;
3600 l_prev_assignment_action_id NUMBER;
3601 l_tax_unit_id NUMBER;
3602 l_year NUMBER;
3603 l_final_xml BLOB;
3604 l_final_xml_string VARCHAR2(32767);
3605 l_last_xml_string VARCHAR2(32767);
3606 l_last_xml CLOB;
3607 l_is_temp_final_xml VARCHAR2(2);
3608 l_output_location VARCHAR2(100);
3609 l_instr_template VARCHAR2(100);
3610 EOL VARCHAR2(10);
3611 l_log VARCHAR2(100);
3612 buffer VARCHAR2(32767);
3613 amount NUMBER := 255;
3614 position VARCHAR2(1) :=1;
3615 l_temp_blob BLOB;
3616 text_size NUMBER;
3617 raw_data RAW(32767);
3618
3619
3620 CURSOR c_get_params IS
3621 SELECT substr(paa.serial_number, 1,15),--W2c Pre process action id
3622 substr(paa.serial_number, 16,15),--Prev pre process action id (W2c pre process/YEPP)
3623 hr_us_w2_mt.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
3624 hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
3625 hr_us_w2_mt.get_parameter('p_instr_template',ppa.legislative_parameters),
3626 hr_us_w2_mt.get_parameter('print_instrunction',ppa.legislative_parameters)
3627 FROM pay_assignment_actions paa,
3628 pay_payroll_actions ppa
3629 where ppa.payroll_action_id = paa.payroll_action_id
3630 and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
3631
3632 BEGIN
3633 --hr_utility.trace_on(null,'w2');
3634 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3635 hr_utility.trace('In fetch_w2c_xm');
3636
3637 hr_utility.trace('Deleting PL/SQL tables');
3638 l_state_local_tab.delete;
3639 l_state_tab.delete;
3640 l_local_tab.delete;
3641 l_box12_tab.delete;
3642 l_box14_tab.delete;
3643 l_w2c_fields_c.amended := '';
3644 l_w2c_fields_c.amended_date := '';
3645
3646
3647 OPEN c_get_params;
3648 FETCH c_get_params INTO
3649 l_assignment_action_id, l_prev_assignment_action_id,
3650 l_tax_unit_id, l_year,l_instr_template,g_print_instr;
3651 CLOSE c_get_params;
3652
3653 l_output_location := get_outfile;
3654
3655 hr_utility.trace('l_assignment_action_id ' ||l_assignment_action_id);
3656 hr_utility.trace('l_prev_assignment_action_id ' ||l_prev_assignment_action_id);
3657 hr_utility.trace('l_tax_unit_id ' ||l_tax_unit_id);
3658 hr_utility.trace('l_year ' ||l_year);
3659 hr_utility.trace('l_output_location ' ||l_output_location);
3660 hr_utility.trace('l_instr_template ' ||l_instr_template);
3661
3662
3663 lc_emp_blob := fetch_w2c_xml(l_assignment_action_id,
3664 l_prev_assignment_action_id,
3665 l_tax_unit_id,
3666 l_year,
3667 l_error_msg, false);
3668 hr_utility.trace('XML String is ');
3669 IF ((dbms_lob.getlength(lc_emp_blob) >0) and (l_error_msg IS NULL) )THEN
3670
3671 --hr_utility.trace(dbms_lob.substr(lc_emp_blob,dbms_lob.getlength(lc_emp_blob),1));
3672
3673 l_final_xml_string := /* 6712851 '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL|| */
3674 '<w2c_data>'||EOL||
3675 '<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">'||EOL||
3676 '<xapi:request>'||EOL||
3677 '<xapi:delivery>'||EOL||
3678 '<xapi:filesystem output="'||l_output_location||'" />'||EOL||
3679 '</xapi:delivery>'||EOL||
3680 '<xapi:document output-type="pdf">'||EOL||
3681 '<xapi:template type="pdf" location="${templateName1}">'||EOL;
3682
3683 l_last_xml_string := '</xapi:template>'||EOL;
3684
3685 IF ( l_instr_template IS NOT null) THEN
3686 l_last_xml_string := l_last_xml_string||
3687 '<xapi:template type="pdf" location="${templateName2}">'||EOL||
3688 '<xapi:data />'|| EOL||
3689 '</xapi:template>'||EOL;
3690 END IF;
3691
3692 l_last_xml_string := l_last_xml_string ||
3693 '</xapi:document>'||EOL||
3694 '</xapi:request>'||EOL||
3695 '</xapi:requestset>'||EOL||
3696 '</w2c_data>'||EOL;
3697
3698
3699 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
3700 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
3701
3702 IF l_is_temp_final_xml = 1 THEN
3703 DBMS_LOB.FREETEMPORARY(l_final_xml);
3704 END IF;
3705
3706 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
3707 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
3708
3709 dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
3710 dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
3711
3712 raw_data:=utl_raw.cast_to_raw(l_final_xml_string);
3713 text_size:=utl_raw.length(raw_data);
3714
3715 -- dbms_lob.writeappend(l_final_xml,text_size,raw_data);
3716
3717 /*dbms_lob.writeappend(l_final_xml,
3718 utl_raw.length(utl_raw.cast_to_raw(l_final_xml_string)),
3719 utl_raw.cast_to_raw(l_final_xml_string)
3720 );*/
3721 l_temp_blob := append_to_lob(l_final_xml_string);
3722 dbms_lob.append(l_final_xml,l_temp_blob);
3723
3724 --dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
3725
3726 hr_utility.trace('Get Length l_final_xml ' ||dbms_lob.getlength(l_final_xml) );
3727
3728 dbms_lob.append(l_final_xml,lc_emp_blob);
3729
3730 raw_data:=utl_raw.cast_to_raw(l_last_xml_string);
3731 text_size:=utl_raw.length(raw_data);
3732
3733 --dbms_lob.writeappend(l_final_xml,text_size,raw_data);
3734
3735 /*dbms_lob.writeappend(l_final_xml,
3736 utl_raw.length(utl_raw.cast_to_raw(l_last_xml_string)),
3737 utl_raw.cast_to_raw(l_last_xml_string)
3738 );*/
3739
3740
3741 l_temp_blob := append_to_lob(l_last_xml_string);
3742 dbms_lob.append(l_final_xml,l_temp_blob);
3743 --dbms_lob.writeappend(l_final_xml,length(l_last_xml_string),l_last_xml_string);
3744
3745 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
3746 hr_utility.trace('Closing l_final_xml' );
3747 dbms_lob.close(l_final_xml);
3748 END IF;
3749 IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
3750 hr_utility.trace('Closing lc_emp_blob' );
3751 dbms_lob.close(lc_emp_blob);
3752 END IF;
3753 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3754 hr_utility.trace('Closing l_temp_blob' );
3755 dbms_lob.close(l_temp_blob);
3756 END IF;
3757
3758 hr_utility.trace('dbms_lob.getlength(l_final_xml) ' ||dbms_lob.getlength(l_final_xml));
3759
3760 pay_core_files.write_to_magtape_lob(l_final_xml);
3761 -- hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
3762
3763 END IF; /*dbms_lob.getlength(lc_emp_blob) >0*/
3764 -- l_is_temp_xml_string := dbms_lob.istemporary(pay_mag_tape.g_clob_value);
3765
3766
3767
3768 EXCEPTION
3769 WHEN OTHERS then
3770 /* Added ISOPEN condition for bug 3899583 */
3771 IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
3772 hr_utility.trace('Raising exception and Closing l_final_xml' ||sqlerrm);
3773 dbms_lob.close(l_final_xml);
3774 END IF;
3775 IF dbms_lob.ISOPEN(lc_emp_blob)=1 THEN
3776 hr_utility.trace('Raising exception and Closing p_xml_string' );
3777 dbms_lob.close(lc_emp_blob);
3778 END IF;
3779 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3780 hr_utility.trace('Closing l_temp_blob' );
3781 dbms_lob.close(l_temp_blob);
3782 END IF;
3783
3784 hr_utility.trace('sqleerm ' || SQLERRM);
3785
3786 raise;
3787
3788 END;
3789
3790 PROCEDURE get_footers IS
3791
3792 l_footer_xml CLOB;
3793 l_last_xml_string VARCHAR2(32000);
3794 l_is_temp_final_xml VARCHAR2(2);
3795 BEGIN
3796 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3797 l_last_xml_string :=
3798 '</EMPLOYEES>'||EOL;
3799 l_is_temp_final_xml := dbms_lob.istemporary(l_footer_xml);
3800 hr_utility.trace('Istemporary(l_footer_xml) ' ||l_is_temp_final_xml );
3801
3802 IF l_is_temp_final_xml = 1 THEN
3803 DBMS_LOB.FREETEMPORARY(l_footer_xml);
3804 END IF;
3805
3806 dbms_lob.createtemporary(l_footer_xml,false,DBMS_LOB.CALL);
3807 dbms_lob.open(l_footer_xml,dbms_lob.lob_readwrite);
3808 dbms_lob.writeappend(l_footer_xml,length(l_last_xml_string),l_last_xml_string);
3809
3810 hr_utility.trace('In Get footers,Length of length(l_footer_xml) ' ||dbms_lob.getlength(l_footer_xml));
3811
3812 --dbms_lob.append(pay_mag_tape.g_clob_value,l_footer_xml);
3813 pay_core_files.write_to_magtape_lob(l_last_xml_string);
3814 --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_footer_xml,dbms_lob.getlength(l_footer_xml),1));
3815
3816 --hr_utility.trace('In Get footers,Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
3817 END;
3818
3819 PROCEDURE get_headers IS
3820
3821 l_final_xml CLOB;
3822 l_final_xml_string VARCHAR2(32000);
3823 l_is_temp_final_xml VARCHAR2(2);
3824 BEGIN
3825 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
3826 l_final_xml_string :=
3827 -- '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL|| Bug 6712851
3828 '<EMPLOYEES>'||EOL;
3829
3830 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
3831 hr_utility.trace('Istemporary(l_final_xml) ' ||l_is_temp_final_xml );
3832
3833 IF l_is_temp_final_xml = 1 THEN
3834 DBMS_LOB.FREETEMPORARY(l_final_xml);
3835 END IF;
3836
3837 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
3838 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
3839 dbms_lob.writeappend(l_final_xml,length(l_final_xml_string),l_final_xml_string);
3840 --dbms_lob.append(pay_mag_tape.g_clob_value,l_final_xml);
3841 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3842 --pay_core_files.write_to_magtape_lob(dbms_lob.substr(l_final_xml_string,dbms_lob.getlength(l_final_xml_string),1));
3843 --hr_utility.trace('Length of pay_mag_tape.g_clob_value ' ||dbms_lob.getlength(pay_mag_tape.g_clob_value));
3844 END;
3845
3846 function get_outfile return VARCHAR2 is
3847 TEMP_UTL varchar2(512);
3848 l_log varchar2(100);
3849 l_out varchar2(100);
3850 begin
3851 hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
3852
3853 if g_temp_dir is null then
3854 -- use first entry of utl_file_dir as the g_temp_dir
3855 select translate(ltrim(value),',',' ')
3856 into TEMP_UTL
3857 from v$parameter
3858 where name = 'utl_file_dir';
3859
3860 if (instr(TEMP_UTL,' ') > 0 and TEMP_UTL is not null) then
3861 select substrb(TEMP_UTL, 1, instr(TEMP_UTL,' ') - 1)
3862 into g_temp_dir
3863 from dual ;
3864 elsif (TEMP_UTL is not null) then
3865 g_temp_dir := TEMP_UTL;
3866 end if;
3867
3868 if (TEMP_UTL is null or g_temp_dir is null ) then
3869 raise no_data_found;
3870 end if;
3871 end if;
3872 hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
3873
3874 FND_FILE.get_names(l_log,l_out);
3875
3876 l_out := g_temp_dir ||'/'||l_out;
3877 hr_utility.trace('In get_out_file,l_out ' ||l_out );
3878
3879 return l_out;
3880
3881 exception
3882 when no_data_found then
3883 return null;
3884 when others then
3885 return null;
3886 end get_outfile;
3887
3888 BEGIN
3889 -- hr_utility.trace_on(null,'W2CRPT');
3890 gv_package := 'pay_us_w2c_rpt';
3891
3892 END pay_us_w2c_rpt;