[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W2_GENERIC_EXTRACT
Source
1 PACKAGE BODY PAY_US_W2_GENERIC_EXTRACT as
2 /* $Header: payusw2genxtract.pkb 120.0.12010000.2 2008/09/15 06:19:06 svannian ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 PAY_US_W2_GENERIC_EXTRACT
10
11 File
12 payusw2genxtract.pkb
13
14 Purpose
15 The purpose of this package is to support the YearEnd Generic Interface Extract Process
16 This package to include generic dtabase package components may be used for various
17 YearEnd process for Extractiing archived Data, Validating and constructing XML element
18
19 Notes
20
21 History
22
23 Date User Id Version Description
24 ============================================================================
25 08-Nov-06 ppanda 115.0 Initial Version Created
26 16-Jan-07 ppanda 115.3 Tag name CITY_TAX_WITHELD changed to CITY_TAX_WITHHELD
27 for employee City level data
28 ============================================================================*/
29 --
30 -- Global Variables
31 --
32 g_proc_name varchar2(240);
33 g_debug boolean;
34 g_document_type varchar2(50);
35
36 /****************************************************************************
37 Name : HR_UTILITY_TRACE
38 Description : This procedure prints debug messages.
39 *****************************************************************************/
40
41 PROCEDURE HR_UTILITY_TRACE
42 (
43 P_TRC_DATA varchar2
44 ) AS
45 BEGIN
46 IF g_debug THEN
47 hr_utility.trace(p_trc_data);
48 END IF;
49 END HR_UTILITY_TRACE;
50
51 FUNCTION convert_special_char( p_data varchar2)
52 RETURN varchar2 IS
53 l_data VARCHAR2(2000);
54 BEGIN
55 l_data := trim(p_data);
56 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
57 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
58 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
59 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
60 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
61 RETURN l_data;
62 END convert_special_char;
63
64
65 --
66 -- This procedure would be used to populate the Tag used for RA Data Record
67 --
68 PROCEDURE populate_ra_data_tag
69 IS
70 i number;
71 BEGIN
72
73 ltr_ra_data_tag(1) := 'TR_REC_IDENTIFIER';
74 ltr_ra_data_tag(2) :='TR_EIN';
75 ltr_ra_data_tag(3) :='TR_PIN';
76 ltr_ra_data_tag(4) :='RESUB_INDICATOR';
77 ltr_ra_data_tag(5) :='RESUB_WFID';
78 ltr_ra_data_tag(6) :='SOFTWARE_CODE';
79 ltr_ra_data_tag(7) :='COMPANY_NAME';
80 ltr_ra_data_tag(8) :='LOCATION_ADDRESS';
81 ltr_ra_data_tag(9) :='TR_DELIVERY_ADDRESS';
82 ltr_ra_data_tag(10) :='TR_CITY';
83 ltr_ra_data_tag(11) :='TR_STATE_ABBREVIATION';
84 ltr_ra_data_tag(12) :='TR_ZIP_CODE';
85 ltr_ra_data_tag(13) :='TR_ZIP_CODE_EXTENSION';
86 ltr_ra_data_tag(14) :='TR_FOREIGN_STATE_PROVINCE';
87 ltr_ra_data_tag(15) :='TR_FOREIGN_POSTAL_CODE';
88 ltr_ra_data_tag(16) :='TR_COUNTRY_CODE';
89 ltr_ra_data_tag(17) :='SUBMITTER_NAME';
90 ltr_ra_data_tag(18) :='CP_LOCATION_ADDRESS';
91 ltr_ra_data_tag(19) :='CP_DELIVERY_ADDRESS';
92 ltr_ra_data_tag(20) :='CP_CITY';
93 ltr_ra_data_tag(21) :='CP_STATE_ABBREVIATION';
94 ltr_ra_data_tag(22) :='CP_ZIP_CODE';
95 ltr_ra_data_tag(23) :='CP_ZIP_CODE_EXTENSION';
96 ltr_ra_data_tag(24) :='CP_FOREIGN_STATE_PROVINCE';
97 ltr_ra_data_tag(25) :='CP_FOREIGN_POSTAL_CODE';
98 ltr_ra_data_tag(26) :='CP_COUNTRY_CODE';
99 ltr_ra_data_tag(27) :='CONTACT_NAME';
100 ltr_ra_data_tag(28) :='CONTACT_PHONE_NUMBER';
101 ltr_ra_data_tag(29) :='CONTACT_PHONE_EXTENSION';
102 ltr_ra_data_tag(30) :='CONTACT_EMAIL';
103 ltr_ra_data_tag(31) :='CONTACT_FAX ';
104 ltr_ra_data_tag(32) :='METHOD_OF_NOTIFICATION';
105 ltr_ra_data_tag(33) :='PREPARER_CODE';
106 --
107 -- Following Loop structure used to debug the Tag Values
108 --
109 FOR I IN 1 .. g_ra_no_of_tag LOOP
110 HR_UTILITY_TRACE('Tag'||to_char(i)|| ' : '||ltr_ra_data_tag(i));
111 END LOOP;
112 END populate_ra_data_tag;
113
114
115 --
116 -- This Procedue would be used to fetch all the submitter/transmitter releated data
117 -- All the data would then be populated into a global pl/sql based table for construting XML
118 --
119 PROCEDURE populate_arch_transmitter ( p_payroll_action_id NUMBER
120 ,p_tax_unit_id NUMBER
121 ,p_date_earned DATE
122 ,p_reporting_year VARCHAR2
123 ,p_jurisdiction_code VARCHAR2
124 ,p_state_code NUMBER
125 ,p_state_abbreviation VARCHAR2
126 ,p_locality_code VARCHAR2
127 ,status VARCHAR2
128 ,p_final_string OUT NOCOPY VARCHAR2
129 )
130 AS
131 --{
132 --
133 -- Declaration of Index Value that will be used for storing and fetching Submitter level Data
134 -- from the global pl/sql table maintained for submitter level data
135 --
136 TR_REC_IDENTIFIER NUMBER := 1;
137 TR_EIN NUMBER := 2;
138 TR_PIN NUMBER := 3;
139 RESUB_INDICATOR NUMBER := 4;
140 RESUB_WFID NUMBER := 5;
141 SOFTWARE_CODE NUMBER := 6;
142 COMPANY_NAME NUMBER := 7;
143 LOCATION_ADDRESS NUMBER := 8;
144 TR_DELIVERY_ADDRESS NUMBER := 9;
145 TR_CITY NUMBER := 10;
146 TR_STATE_ABBREVIATION NUMBER := 11;
147 TR_ZIP_CODE NUMBER := 12;
148 TR_ZIP_CODE_EXTENSION NUMBER := 13;
149 TR_FOREIGN_STATE_PROVINCE NUMBER := 14;
150 TR_FOREIGN_POSTAL_CODE NUMBER := 15;
151 TR_COUNTRY_CODE NUMBER := 16;
152 SUBMITTER_NAME NUMBER := 17;
153 CP_LOCATION_ADDRESS NUMBER := 18;
154 CP_DELIVERY_ADDRESS NUMBER := 19;
155 CP_CITY NUMBER := 20;
156 CP_STATE_ABBREVIATION NUMBER := 21;
157 CP_ZIP_CODE NUMBER := 22;
158 CP_ZIP_CODE_EXTENSION NUMBER := 23;
159 CP_FOREIGN_STATE_PROVINCE NUMBER := 24;
160 CP_FOREIGN_POSTAL_CODE NUMBER := 25;
161 CP_COUNTRY_CODE NUMBER := 26;
162 CONTACT_NAME NUMBER := 27;
163 CONTACT_PHONE_NUMBER NUMBER := 28;
164 CONTACT_PHONE_EXTENSION NUMBER := 29;
165 CONTACT_EMAIL NUMBER := 30;
166 CONTACT_FAX NUMBER := 31;
167 METHOD_OF_NOTIFICATION NUMBER := 32;
168 PREPARER_CODE NUMBER := 33;
169
170
171 --
172 -- Local Variables required for Employer/Contact person Data
173 --
174 l_payroll_action_id number;
175 l_assignment_id number;
176 l_date_earned date;
177 l_tax_unit_id number;
178 l_input_report_type varchar2(200) := 'W2';
179 l_input_report_type_format varchar2(200) := 'MMREF';
180 l_input_record_name varchar2(200) := 'RA';
181 l_effective_date varchar2(200);
182 l_item_name varchar2(200);
183 l_input_report_qualifier varchar2(200);
184
185 /* Submitter Input Variables for fetching Submitter Data */
186
187 input_sbmtr_name varchar2(200) ;
188 input_sbmtr_1 varchar2(200) ;
189 input_sbmtr_2 varchar2(200) ;
190 input_sbmtr_3 varchar2(200) ;
191 input_sbmtr_4 varchar2(200) ;
192 input_sbmtr_5 varchar2(200) ;
193 input_sbmtr_validiate_flag varchar2(200) ;
194 sbmtr_exclude_output_flag varchar2(200) ;
195 -- Output
196 sbmtr_out_1 varchar2(200) := ' ';
197 sbmtr_out_2 varchar2(200) := ' ';
198 sbmtr_out_3 varchar2(200) := ' ';
199 sbmtr_out_4 varchar2(200) := ' ';
200 sbmtr_out_5 varchar2(200) := ' ';
201 sbmtr_out_6 varchar2(200) := ' ';
202 sbmtr_out_7 varchar2(200) := ' ';
203 sbmtr_out_8 varchar2(200) := ' ';
204 sbmtr_out_9 varchar2(200) := ' ';
205 sbmtr_out_10 varchar2(200) := ' ';
206
207 /* Local Variables for Company Information */
208 input_empr_name varchar2(200);
209 input_empr_1 varchar2(200);
210 input_empr_2 varchar2(200) := ' ';
211 input_empr_3 varchar2(200) := ' ';
212 input_empr_4 varchar2(200) := ' ';
213 input_empr_5 varchar2(200) := ' ';
214 empr_validate_flag varchar2(200) := 'Y';
215 empr_exclude_output_flag varchar2(200) := 'N';
216 empr_out_1 varchar2(200) := ' ';
217 empr_out_2 varchar2(200) := ' ';
218 empr_out_3 varchar2(200) := ' ';
219 empr_out_4 varchar2(200) := ' ';
220 empr_out_5 varchar2(200) := ' ';
221 empr_out_6 varchar2(200) := ' ';
222 empr_out_7 varchar2(200) := ' ';
223 empr_out_8 varchar2(200) := ' ';
224 empr_out_9 varchar2(200) := ' ';
225 empr_out_10 varchar2(200) := ' ';
226
227 /* Local Variables for CONTACT INFORMATION Input */
228 input_cnti_name varchar2(200);
229 input_cnti_1 varchar2(200);
230 input_cnti_2 varchar2(200);
231 input_cnti_3 varchar2(200);
232 input_cnti_4 varchar2(200);
233 input_cnti_5 varchar2(200);
234 input_cnti_validate_flag varchar2(200);
235 cnti_exclude_output_flag varchar2(200);
236 cnti_out_1 varchar2(200) := ' ';
237 cnti_out_2 varchar2(200) := ' ';
238 cnti_out_3 varchar2(200) := ' ';
239 cnti_out_4 varchar2(200) := ' ';
240 cnti_out_5 varchar2(200) := ' ';
241 cnti_out_6 varchar2(200) := ' ';
242 cnti_out_7 varchar2(200) := ' ';
243 cnti_out_8 varchar2(200) := ' ';
244 cnti_out_9 varchar2(200) := ' ';
245 cnti_out_10 varchar2(200) := ' ';
246
247 i number :=0;
248 j number :=0;
249 --PL Table used for storing and manipulating DataBaseItem used for
250 -- Submitter level information
251 TYPE RA_UE_REC IS RECORD (
252 ue_name varchar2(200),
253 ue_value varchar2(200)
254 );
255
256 TYPE ra_ue_record IS TABLE OF ra_ue_rec
257 INDEX BY BINARY_INTEGER;
258 ltr_ue_name_table ra_ue_record;
259 l_number_ra_dbi NUMBER := 6;
260
261 --
262 -- RA Record Data Item Tags
263 --
264
265 submitter_data varchar2(200);
266 employer_data varchar2(200);
267 contact_data varchar2(200);
268
269 -- SRS Parameter
270
271 l_srs_trns_pin varchar2(200);
272 l_srs_trns_tlcn varchar2(200);
273 l_srs_resub_indicator varchar2(200);
274
275 -- Derived Local Variables
276 l_resub_indicator varchar2(200);
277 l_reporting_date varchar2(200);
278
279 l_err boolean := FALSE;
280 l_validate varchar2(100);
281 l_validated_EIN varchar2(200);
282
283 l_final_xml_string varchar2(32767);
284 l_last_xml CLOB;
285 l_is_temp_final_xml varchar2(2);
286 l_output_location varchar2(100);
287 l_instr_template varchar2(100);
288 EOL varchar2(10);
289
290 /* End of Variable Declarations */
291 BEGIN
292 --{
293 --
294 -- Fetch all Context or Parameters set at the Transmitter Cursor
295 --
296 --l_tax_unit_id := pay_magtape_generic.get_parameter_value('TAX_UNIT_ID');
297 --l_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
298 --l_date_earned := pay_magtape_generic.get_parameter_value('DATE_EARNED');
299 --l_reporting_date := '31-DEC-'||pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
300
301 l_tax_unit_id := p_tax_unit_id;
302 l_payroll_action_id := p_payroll_action_id;
303 l_date_earned := p_date_earned;
304 l_reporting_date := '31-DEC-'||p_reporting_year;
305 l_effective_date := l_reporting_date;
306
307 --
308 -- Fetch SRS Parameter
309 --
310 l_srs_trns_pin := pay_magtape_generic.get_parameter_value('TRNS_PIN');
311 l_srs_trns_tlcn := pay_magtape_generic.get_parameter_value('TRNS_TLCN');
312
313 l_input_report_qualifier := pay_magtape_generic.get_parameter_value('TRANSFER_STATE');
314
315 --
316 -- Fetch Archived Values of various DBIs used in submitter Record
317 --
318
319 i := i +1;
320 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER';
321 i := i +1;
322 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_NAME';
323 i := i +1;
324 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_COMPANY_NAME';
325 i := i +1;
326 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME';
327 i := i +1;
328 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_NOTIFICATION_METHOD';
329 i := i +1;
330 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_PREPARER';
331
332 if ltr_ue_name_table.count > 0 then
333 for j in ltr_ue_name_table.first .. ltr_ue_name_table.last
334 loop
335 ltr_ue_name_table(j).ue_value := hr_us_w2_rep.get_w2_tax_unit_item (l_tax_unit_id,
336 l_payroll_action_id,
337 ltr_ue_name_table(j).ue_name);
338 end loop;
339 end if;
340
341 /* =========================================================
342 Get Submitted Information to be used for extracting MMREF-1 RA record type
343 ========================================================== */
344 input_sbmtr_name := 'ER_ADDRESS';
345 input_sbmtr_1 := pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID');
346 input_sbmtr_validiate_flag := 'Y';
347 sbmtr_exclude_output_flag := 'N';
348
349 submitter_data := pay_us_reporting_utils_pkg.get_item_data(
350 NULL, -- Assignment Id (Not Used at RA record)
351 l_date_earned,
352 l_tax_unit_id,
353 l_reporting_date,
354 input_sbmtr_name,
355 l_input_report_type,
356 l_input_report_type_format,
357 l_input_report_qualifier,
358 l_input_record_name,
359 input_sbmtr_1,
360 input_sbmtr_2,
361 input_sbmtr_3,
362 input_sbmtr_4,
363 input_sbmtr_5,
364 input_sbmtr_validiate_flag,
365 sbmtr_exclude_output_flag,
366 sbmtr_out_1,
367 sbmtr_out_2,
368 sbmtr_out_3,
369 sbmtr_out_4,
370 sbmtr_out_5,
371 sbmtr_out_6,
372 sbmtr_out_7,
373 sbmtr_out_8,
374 sbmtr_out_9,
375 sbmtr_out_10);
376
377
378 /* ==================================================
379 Get Employer or transmitter Company Information releated Submitter
380 ================================================== */
381
382 input_empr_name := 'CR_ADDRESS';
383 input_empr_1 := ltr_ue_name_table(3).ue_value;
384
385 employer_data := pay_us_reporting_utils_pkg.get_item_data(
386 NULL, -- Assignment Id (Not Used at RA record)
387 l_date_earned,
388 l_tax_unit_id,
389 l_reporting_date,
390 input_empr_name,
391 l_input_report_type,
392 l_input_report_type_format,
393 l_input_report_qualifier,
394 l_input_record_name,
395 input_empr_1,
396 input_empr_2,
397 input_empr_3,
398 input_empr_4,
399 input_empr_5,
400 empr_validate_flag,
401 empr_exclude_output_flag,
402 empr_out_1,
403 empr_out_2,
404 empr_out_3,
405 empr_out_4,
406 empr_out_5,
407 empr_out_6,
408 empr_out_7,
409 empr_out_8,
410 empr_out_9,
411 empr_out_10);
412
413 /* ========================================================
414 Fetch Information of Contact Person submitting the Lcoal W-2 Magnetic Tape
415 ========================================================= */
416 input_cnti_name := 'CR_PERSON';
417 -- A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME
418 input_cnti_1 := ltr_ue_name_table(4).ue_value;
419 input_cnti_validate_flag := 'Y';
420 cnti_exclude_output_flag := 'N';
421
422 contact_data := pay_us_reporting_utils_pkg.get_item_data(
423 NULL, -- Assignment Id (Not Used at RA record)
424 l_date_earned,
425 l_tax_unit_id,
426 l_reporting_date,
427 input_cnti_name,
428 l_input_report_type,
429 l_input_report_type_format,
430 l_input_report_qualifier,
431 l_input_record_name,
432 input_cnti_1,
433 input_cnti_2,
434 input_cnti_3,
435 input_cnti_4,
436 input_cnti_5,
437 input_cnti_validate_flag,
438 cnti_exclude_output_flag,
439 cnti_out_1,
440 cnti_out_2,
441 cnti_out_3,
442 cnti_out_4,
443 cnti_out_5,
444 cnti_out_6,
445 cnti_out_7,
446 cnti_out_8,
447 cnti_out_9,
448 cnti_out_10);
449 --
450 -- Pouplate All the Tags to be used for RA record Data Items
451 --
452 pay_us_w2_generic_extract.populate_ra_data_tag;
453
454
455 /* Record Identifier */
456 g_ra_record(TR_REC_IDENTIFIER).submitter_data := 'RA';
457 g_ra_record(TR_REC_IDENTIFIER).submitter_tag := ltr_ra_data_tag(TR_REC_IDENTIFIER);
458
459 /* Submitter Employer Identification Number(EIN) */
460 --
461 -- EIN Validation
462 --
463 l_validated_EIN :=
464 pay_us_report_data_validation.validate_W2_EIN( 'FED',
465 l_input_record_name,
466 ltr_ue_name_table(1).ue_value,
467 empr_out_10,
468 empr_validate_flag,
469 l_err
470 );
471 g_submitter_ein := l_validated_EIN;
472 g_ra_record(TR_EIN).submitter_data := l_validated_EIN;
473 --ltr_ue_name_table(1).ue_value;
474 g_ra_record(TR_EIN).submitter_tag := ltr_ra_data_tag(TR_EIN);
475
476 /* Personal Identification Number (PIN) */
477 g_ra_record(TR_PIN).submitter_data := l_srs_trns_pin;
478 g_ra_record(TR_PIN).submitter_tag := ltr_ra_data_tag(TR_PIN);
479
480 /* Resub Indicator */
481 IF length(l_srs_trns_tlcn) > 0
482 THEN
483 l_resub_indicator := '1';
484 ELSE
485 l_resub_indicator := '0';
486 END IF;
487 g_ra_record(RESUB_INDICATOR).submitter_data := l_resub_indicator;
488 g_ra_record(RESUB_INDICATOR).submitter_tag := ltr_ra_data_tag(RESUB_INDICATOR);
489
490 /* Resub TLCN */
491 g_ra_record(RESUB_WFID).submitter_data := l_srs_trns_tlcn;
492 g_ra_record(RESUB_WFID).submitter_tag := ltr_ra_data_tag(RESUB_WFID);
493 /* Software Code */
494 g_ra_record(SOFTWARE_CODE).submitter_data := '99';
495 g_ra_record(SOFTWARE_CODE).submitter_tag := ltr_ra_data_tag(SOFTWARE_CODE);
496 /* Company Name */
497 g_ra_record(COMPANY_NAME).submitter_data := empr_out_10;
498 g_ra_record(COMPANY_NAME).submitter_tag := ltr_ra_data_tag(COMPANY_NAME);
499 /* Company, Location Address */
500 g_ra_record(LOCATION_ADDRESS).submitter_data := empr_out_1;
501 g_ra_record(LOCATION_ADDRESS).submitter_tag := ltr_ra_data_tag(LOCATION_ADDRESS);
502 /* Company, Delivery Address */
503 g_ra_record(TR_DELIVERY_ADDRESS).submitter_data := empr_out_2;
504 g_ra_record(TR_DELIVERY_ADDRESS).submitter_tag := ltr_ra_data_tag(TR_DELIVERY_ADDRESS);
505 /* Company, City */
506 g_ra_record(TR_CITY).submitter_data := empr_out_3;
507 g_ra_record(TR_CITY).submitter_tag := 'TR_CITY';
508 /* Company, State Abbreviation */
509 g_ra_record(TR_STATE_ABBREVIATION).submitter_data := empr_out_4;
510 g_ra_record(TR_STATE_ABBREVIATION).submitter_tag := ltr_ra_data_tag(TR_STATE_ABBREVIATION);
511 /* Desc: Company, Zip Code */
512 g_ra_record(TR_ZIP_CODE).submitter_data := empr_out_5;
513 g_ra_record(TR_ZIP_CODE).submitter_tag := 'TR_ZIP_CODE';
514 /* Desc: Company, Zip Code Extension */
515 g_ra_record(TR_ZIP_CODE_EXTENSION).submitter_data := empr_out_6;
516 g_ra_record(TR_ZIP_CODE_EXTENSION).submitter_tag := ltr_ra_data_tag(TR_ZIP_CODE_EXTENSION);
517 /* Company, Foreign State or Province */
518 g_ra_record(TR_FOREIGN_STATE_PROVINCE).submitter_data := empr_out_7;
519 g_ra_record(TR_FOREIGN_STATE_PROVINCE).submitter_tag := ltr_ra_data_tag(TR_FOREIGN_STATE_PROVINCE);
520 /* Company, Foreign Postal Code */
521 g_ra_record(TR_FOREIGN_POSTAL_CODE).submitter_data := empr_out_8;
522 g_ra_record(TR_FOREIGN_POSTAL_CODE).submitter_tag := ltr_ra_data_tag(TR_FOREIGN_POSTAL_CODE);
523 /* Company, Country Code */
524 g_ra_record(TR_COUNTRY_CODE).submitter_data := empr_out_9;
525 g_ra_record(TR_COUNTRY_CODE).submitter_tag := ltr_ra_data_tag(TR_COUNTRY_CODE);
526 /* Submitter Name */
527 g_ra_record(SUBMITTER_NAME).submitter_data := ltr_ue_name_table(2).ue_value;
528 g_ra_record(SUBMITTER_NAME).submitter_tag := ltr_ra_data_tag(SUBMITTER_NAME);
529 /* Submitter, Location Address */
530 g_ra_record(CP_LOCATION_ADDRESS).submitter_data := sbmtr_out_1;
531 g_ra_record(CP_LOCATION_ADDRESS).submitter_tag := ltr_ra_data_tag(CP_LOCATION_ADDRESS);
532 /* Submitter, Delivery Address */
533 g_ra_record(CP_DELIVERY_ADDRESS).submitter_data := sbmtr_out_2;
534 g_ra_record(CP_DELIVERY_ADDRESS).submitter_tag := ltr_ra_data_tag(CP_DELIVERY_ADDRESS);
535 /* Submitter, City */
536 g_ra_record(CP_CITY).submitter_data := sbmtr_out_3;
537 g_ra_record(CP_CITY).submitter_tag := ltr_ra_data_tag(CP_CITY);
538 /* Submitter, State Abbreviation */
539 g_ra_record(CP_STATE_ABBREVIATION).submitter_data := sbmtr_out_4;
540 g_ra_record(CP_STATE_ABBREVIATION).submitter_tag := ltr_ra_data_tag(CP_STATE_ABBREVIATION);
541 /* Submitter, Zip Code */
542 g_ra_record(CP_ZIP_CODE).submitter_data := sbmtr_out_5;
543 g_ra_record(CP_ZIP_CODE).submitter_tag := ltr_ra_data_tag(CP_ZIP_CODE);
544 /* Submitter, Zip Code Extension */
545 g_ra_record(CP_ZIP_CODE_EXTENSION).submitter_data := sbmtr_out_6;
546 g_ra_record(CP_ZIP_CODE_EXTENSION).submitter_tag := ltr_ra_data_tag(CP_ZIP_CODE_EXTENSION);
547 /* Submitter, Foreign State or Province */
548 g_ra_record(CP_FOREIGN_STATE_PROVINCE).submitter_data := sbmtr_out_7;
549 g_ra_record(CP_FOREIGN_STATE_PROVINCE).submitter_tag := ltr_ra_data_tag(CP_FOREIGN_STATE_PROVINCE);
550 /* Submitter, Foreign Postal Code */
551 g_ra_record(CP_FOREIGN_POSTAL_CODE).submitter_data := sbmtr_out_8;
552 g_ra_record(CP_FOREIGN_POSTAL_CODE).submitter_tag := ltr_ra_data_tag(CP_FOREIGN_POSTAL_CODE);
553 /* Submitter, Country Code */
554 g_ra_record(CP_COUNTRY_CODE).submitter_data := sbmtr_out_8;
555 g_ra_record(CP_COUNTRY_CODE).submitter_tag := ltr_ra_data_tag(CP_COUNTRY_CODE);
556 /* Contact Name */
557 g_ra_record(CONTACT_NAME).submitter_data := cnti_out_1;
558 g_ra_record(CONTACT_NAME).submitter_tag := ltr_ra_data_tag(CONTACT_NAME);
559 /* Contact Phone Number */
560 g_ra_record(CONTACT_PHONE_NUMBER).submitter_data := cnti_out_2;
561 g_ra_record(CONTACT_PHONE_NUMBER).submitter_tag := ltr_ra_data_tag(CONTACT_PHONE_NUMBER);
562 /* Contact Phone Extension */
563 g_ra_record(CONTACT_PHONE_EXTENSION).submitter_data := cnti_out_3;
564 g_ra_record(CONTACT_PHONE_EXTENSION).submitter_tag := ltr_ra_data_tag(CONTACT_PHONE_EXTENSION);
565 /* Contact E-Mail */
566 g_ra_record(CONTACT_EMAIL).submitter_data := cnti_out_4;
567 g_ra_record(CONTACT_EMAIL).submitter_tag := ltr_ra_data_tag(CONTACT_EMAIL);
568 /* Contact FAX */
569 g_ra_record(CONTACT_FAX).submitter_data := cnti_out_5;
570 g_ra_record(CONTACT_FAX).submitter_tag := ltr_ra_data_tag(CONTACT_FAX);
571 /* Perferred Method of Problem Notification Code */
572 g_ra_record(METHOD_OF_NOTIFICATION).submitter_data := ltr_ue_name_table(5).ue_value;
573 g_ra_record(METHOD_OF_NOTIFICATION).submitter_tag := ltr_ra_data_tag(METHOD_OF_NOTIFICATION);
574 /* Preparer Code */
575 g_ra_record(PREPARER_CODE).submitter_data := ltr_ue_name_table(6).ue_value;
576 g_ra_record(PREPARER_CODE).submitter_tag := ltr_ra_data_tag(PREPARER_CODE);
577
578 /*
579 Consturct XML Elements using all the RA record data items stored in the
580 '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL ||
581 */
582 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
583 FROM dual;
584
585 l_final_xml_string := '<TRANSMITTER>'||EOL;
586
587 FOR I IN 1 .. g_ra_no_of_tag LOOP
588 l_final_xml_string := l_final_xml_string || '<'||g_ra_record(I).submitter_tag||'>'||
589 convert_special_char(g_ra_record(I).submitter_data)
590 ||'</'||g_ra_record(I).submitter_tag||'>'|| EOL;
591 -- HR_UTILITY_TRACE(l_final_xml_string);
592 END LOOP;
593 HR_UTILITY_TRACE(l_final_xml_string);
594 p_final_string := l_final_xml_string;
595 --pay_us_mmref_local_xml.write_to_magtape_lob(l_final_xml_string);
596 --pay_core_files.write_to_magtape_lob(l_final_xml_string);
597 --}
598 END populate_arch_transmitter; -- End of Procedure populate_arch_transmitter
599
600 --
601 -- This Procedue would be used to fetch all the Employer releated data for yearEnd reporting
602 -- All the data would then be populated into a global pl/sql based table for constructing XML
603 --
604
605 --
606 -- This procedure would be used to populate the Tag used for RE Data Record
607 --
608 PROCEDURE populate_re_data_tag
609 IS
610
611 i number;
612
613 BEGIN
614
615 ltr_re_data_tag(1) := 'ER_RECORD_IDENTIFIER';
616 ltr_re_data_tag(2) := 'ER_TAX_YEAR';
617 ltr_re_data_tag(3) := 'ER_AGENT_INDICATOR_CODE';
618 ltr_re_data_tag(4) := 'ER_EIN';
619 ltr_re_data_tag(5) := 'ER_AGENT_EIN';
620 ltr_re_data_tag(6) := 'ER_TERMINATE_BUSINESS_IND';
621 ltr_re_data_tag(7) := 'ESTABLISHMENT_NUMBER';
622 ltr_re_data_tag(8) := 'ER_OTHER_EIN';
623 ltr_re_data_tag(9) := 'ER_NAME';
624 ltr_re_data_tag(10) := 'ER_ADDRESS';
625 ltr_re_data_tag(11) := 'ER_DELIVERY_ADDRESS';
626 ltr_re_data_tag(12) := 'ER_CITY';
627 ltr_re_data_tag(13) := 'ER_STATE_ABBREVIATION';
628 ltr_re_data_tag(14) := 'ER_ZIP_CODE';
629 ltr_re_data_tag(15) := 'ER_ZIP_CODE_EXTENSION';
630 ltr_re_data_tag(16) := 'ER_FOREIGN_STATE_PROVINCE';
631 ltr_re_data_tag(17) := 'ER_FOREIGN_POSTAL_CODE';
632 ltr_re_data_tag(18) := 'ER_COUNTRY_CODE';
633 ltr_re_data_tag(19) := 'ER_EMPLOYMENT_CODE';
634 ltr_re_data_tag(20) := 'ER_TAX_JD_CODE';
635 ltr_re_data_tag(21) := 'ER_THIRD_PARTY_SICK_PAY_IND';
636 ltr_re_data_tag(22) := 'ER_SS_EE_WAGE_BASE';
637 ltr_re_data_tag(23) := 'ER_SS_EE_WAGE_RATE';
638 ltr_re_data_tag(24) := 'ER_1099R_TRANSMITTER_CODE';
639 ltr_re_data_tag(25) := 'ER_1099R_TRANSMITTER_INDICATOR';
640 ltr_re_data_tag(26) := 'ER_1099R_BUREAU_INDICATOR';
641 ltr_re_data_tag(27) := 'ER_1099R_COMBINED_FILER';
642 ltr_re_data_tag(28) := 'ER_SIT_COMPANY_STATE_ID';
643 ltr_re_data_tag(29) := 'ER_SUI_COMPANY_STATE_ID';
644 ltr_re_data_tag(30) := 'ER_FIPS_CODE_JD ';
645 ltr_re_data_tag(31) := 'ER_GOVT_EMPLOYER';
646 ltr_re_data_tag(32) := 'ER_TYPE_OF_EMPLOYMENT ';
647 ltr_re_data_tag(33) := 'ER_BLOCKING_FACTOR';
648 ltr_re_data_tag(34) := 'ER_W2_2678_FILER';
649 ltr_re_data_tag(35) := 'ER_COMPANY_NAME';
650 ltr_re_data_tag(36) := 'ER_CONTACT_NAME';
651 ltr_re_data_tag(37) := 'ER_NOTIFICATION_METHOD';
652 ltr_re_data_tag(38) := 'ER_PREPARER';
653 --
654 -- Following Loop structure used to debug the Tag Values
655 --
656 FOR I IN ltr_re_data_tag.first .. ltr_re_data_tag.last LOOP
657 HR_UTILITY_TRACE('Tag'||to_char(i)|| ' : '||ltr_re_data_tag(i));
658 END LOOP;
659
660 END populate_re_data_tag;
661
662 --
663 -- This Procedue would be used to fetch all the employer/Tax Unit releated data
664 -- All the data would then be populated into a global pl/sql based table for construting XML
665 --
666 PROCEDURE populate_arch_employer(
667 p_payroll_action_id NUMBER
668 ,p_tax_unit_id NUMBER
669 ,p_date_earned DATE
670 ,p_reporting_year VARCHAR2
671 ,p_jurisdiction_code VARCHAR2
672 ,p_state_code NUMBER
673 ,p_state_abbreviation VARCHAR2
674 ,p_locality_code VARCHAR2
675 ,status VARCHAR2
676 ,p_final_string OUT NOCOPY VARCHAR2
677 )
678 AS
679 --{
680 --
681 -- Declaration of Index Value that will be used for storing and fetching Employer level Data
682 -- from the global pl/sql table maintained for employer or Tax Unit level data
683 --
684
685 ER_RECORD_IDENTIFIER NUMBER := 1;
686 ER_TAX_YEAR NUMBER := 2;
687 ER_AGENT_INDICATOR_CODE NUMBER := 3;
688 ER_EIN NUMBER := 4;
689 ER_AGENT_EIN NUMBER := 5;
690 ER_TERMINATE_BUSINESS_IND NUMBER := 6;
691 ESTABLISHMENT_NUMBER NUMBER := 7;
692 ER_OTHER_EIN NUMBER := 8;
693 ER_NAME NUMBER := 9;
694 ER_ADDRESS NUMBER := 10;
695 ER_DELIVERY_ADDRESS NUMBER := 11;
696 ER_CITY NUMBER := 12;
697 ER_STATE_ABBREVIATION NUMBER := 13;
698 ER_ZIP_CODE NUMBER := 14;
699 ER_ZIP_CODE_EXTENSION NUMBER := 15;
700 ER_FOREIGN_STATE_PROVINCE NUMBER := 16;
701 ER_FOREIGN_POSTAL_CODE NUMBER := 17;
702 ER_COUNTRY_CODE NUMBER := 18;
703 ER_EMPLOYMENT_CODE NUMBER := 19;
704 ER_TAX_JD_CODE NUMBER := 20;
705 ER_THIRD_PARTY_SICK_PAY_IND NUMBER := 21;
706 ER_SS_EE_WAGE_BASE NUMBER := 22;
707 ER_SS_EE_WAGE_RATE NUMBER := 23;
708 ER_1099R_TRANSMITTER_CODE NUMBER := 24;
709 ER_1099R_TRANSMITTER_INDICATOR NUMBER := 25;
710 ER_1099R_BUREAU_INDICATOR NUMBER := 26;
711 ER_1099R_COMBINED_FILER NUMBER := 27;
712 ER_SIT_COMPANY_STATE_ID NUMBER := 28;
713 ER_SUI_COMPANY_STATE_ID NUMBER := 29;
714 ER_FIPS_CODE_JD NUMBER := 30;
715 ER_GOVT_EMPLOYER NUMBER := 31;
716 ER_TYPE_OF_EMPLOYMENT NUMBER := 32;
717 ER_BLOCKING_FACTOR NUMBER := 33;
718 ER_W2_2678_FILER NUMBER := 34;
719 ER_COMPANY_NAME NUMBER := 35;
720 ER_CONTACT_NAME NUMBER := 36;
721 ER_NOTIFICATION_METHOD NUMBER := 37;
722 ER_PREPARER NUMBER := 38;
723
724 --
725 -- Local Variables required for Employer/Contact person Data
726 --
727 l_payroll_action_id number;
728 l_assignment_id number;
729 l_date_earned date;
730 l_tax_unit_id number;
731 l_input_report_type varchar2(200) := 'W2';
732 l_input_report_type_format varchar2(200) := 'MMREF';
733 l_input_record_name varchar2(200) := 'RE';
734 l_effective_date varchar2(200);
735 l_item_name varchar2(200);
736 l_input_report_qualifier varchar2(200);
737
738 /* Local Variables for Company Information */
739 input_empr_name varchar2(200);
740 input_empr_1 varchar2(200);
741 input_empr_2 varchar2(200) := ' ';
742 input_empr_3 varchar2(200) := ' ';
743 input_empr_4 varchar2(200) := ' ';
744 input_empr_5 varchar2(200) := ' ';
745 empr_validate_flag varchar2(200) := 'Y';
746 empr_exclude_output_flag varchar2(200) := 'N';
747 empr_out_1 varchar2(200) := ' ';
748 empr_out_2 varchar2(200) := ' ';
749 empr_out_3 varchar2(200) := ' ';
750 empr_out_4 varchar2(200) := ' ';
751 empr_out_5 varchar2(200) := ' ';
752 empr_out_6 varchar2(200) := ' ';
753 empr_out_7 varchar2(200) := ' ';
754 empr_out_8 varchar2(200) := ' ';
755 empr_out_9 varchar2(200) := ' ';
756 empr_out_10 varchar2(200) := ' ';
757
758 i number :=0;
759 j number :=0;
760 --PL Table used for storing and manipulating DataBaseItem used for
761 -- Submitter level information
762 TYPE re_ue_rec IS RECORD (
763 ue_name varchar2(200),
764 ue_value varchar2(200)
765 );
766 TYPE re_ue_record IS TABLE OF re_ue_rec
767 INDEX BY BINARY_INTEGER;
768 ltr_ue_name_table re_ue_record;
769 l_number_re_dbi number := 6;
770
771 employer_data varchar2(200);
772
773 -- SRS Parameter
774
775 l_srs_trns_pin varchar2(200);
776 l_srs_trns_tlcn varchar2(200);
777 l_srs_resub_indicator varchar2(200);
778
779 -- Derived Local Variables
780 l_resub_indicator varchar2(200);
781 l_reporting_date varchar2(200);
782
783 l_err boolean := FALSE;
784 l_validate varchar2(100);
785 l_validated_EIN varchar2(200);
786
787 l_final_xml_string VARCHAR2(32767);
788 l_last_xml CLOB;
789 l_is_temp_final_xml VARCHAR2(2);
790 l_output_location VARCHAR2(100);
791 l_instr_template VARCHAR2(100);
792 EOL VARCHAR2(10);
793
794 /* End of Variable Declarations */
795 BEGIN
796 --{
797 --
798 -- Fetch all Context or Parameters set at the Transmitter Cursor
799 --
800 --l_tax_unit_id := pay_magtape_generic.get_parameter_value('TAX_UNIT_ID');
801 --l_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
802 --l_date_earned := pay_magtape_generic.get_parameter_value('DATE_EARNED');
803 --l_reporting_date := '31-DEC-'||pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
804
805 l_tax_unit_id := p_tax_unit_id;
806 l_payroll_action_id := p_payroll_action_id;
807 l_date_earned := p_date_earned;
808 l_reporting_date := '31-DEC-'||p_reporting_year;
809 l_effective_date := l_reporting_date;
810
811 --
812 -- Fetch SRS Parameter
813 --
814 l_srs_trns_pin := pay_magtape_generic.get_parameter_value('TRNS_PIN');
815 l_srs_trns_tlcn := pay_magtape_generic.get_parameter_value('TRNS_TLCN');
816
817 l_input_report_qualifier := pay_magtape_generic.get_parameter_value('TRANSFER_STATE');
818
819 i := 0;
820 --
821 -- Fetch Archived Values of various DBIs used in submitter Record
822 --
823 i := i +1;
824 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER';
825 i := i +1;
826 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_TERMINATED_GRE_INDICATOR';
827 i := i +1;
828 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_OTHER_EIN';
829 i := i +1;
830 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_NAME';
831 i := i +1;
832 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_COUNTRY_CODE';
833 i := i +1;
834 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_TAX_JURISDICTION';
835 i := i +1;
836 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_THIRD_PARTY_SICK_PAY';
837 i := i +1;
838 ltr_ue_name_table(i).ue_name := 'A_SS_EE_WAGE_BASE';
839 i := i +1;
840 ltr_ue_name_table(i).ue_name := 'A_SS_EE_WAGE_RATE';
841 i := i +1;
842 ltr_ue_name_table(i).ue_name := 'A_US_1099R_TRANSMITTER_CODE';
843 i := i +1;
844 ltr_ue_name_table(i).ue_name := 'A_US_1099R_TRANSMITTER_INDICATOR';
845 i := i +1;
846 ltr_ue_name_table(i).ue_name := 'A_US_1099R_BUREAU_INDICATOR';
847 i := i +1;
848 ltr_ue_name_table(i).ue_name := 'A_US_1099R_COMBINED_FED_STATE_FILER';
849 i := i +1;
850 ltr_ue_name_table(i).ue_name := 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID';
851 i := i +1;
852 ltr_ue_name_table(i).ue_name := 'A_STATE_TAX_RULES_ORG_SUI_COMPANY_STATE_ID';
853 i := i +1;
854 ltr_ue_name_table(i).ue_name := 'A_FIPS_CODE_JD';
855 i := i +1;
856 ltr_ue_name_table(i).ue_name := 'A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER';
857 i := i +1;
858 ltr_ue_name_table(i).ue_name := 'A_LC_FEDERAL_TAX_RULES_ORG_TYPE_OF_EMPLOYMENT';
859 i := i +1;
860 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_BLOCKING_FACTOR';
861 i := i +1;
862 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_W2_2678_FILER';
863 i := i +1;
864 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_COMPANY_NAME';
865 i := i +1;
866 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME';
867 i := i +1;
868 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_NOTIFICATION_METHOD';
869 i := i +1;
870 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_PREPARER';
871
872 if ltr_ue_name_table.count > 0 then
873 for j in ltr_ue_name_table.first .. ltr_ue_name_table.last
874 loop
875 ltr_ue_name_table(j).ue_value := hr_us_w2_rep.get_w2_tax_unit_item (l_tax_unit_id,
876 l_payroll_action_id,
877 ltr_ue_name_table(j).ue_name);
878 end loop;
879 end if;
880
881 /* ==================================================
882 Get Employer or transmitter Company Information releated Submitter
883 ================================================== */
884
885 input_empr_name := 'ER_ADDRESS';
886 input_empr_1 := ltr_ue_name_table(4).ue_value;
887
888 employer_data := pay_us_reporting_utils_pkg.get_item_data(
889 NULL, -- Assignment Id (Not Used at RE record)
890 l_date_earned,
891 l_tax_unit_id,
892 l_reporting_date,
893 input_empr_name,
894 l_input_report_type,
895 l_input_report_type_format,
896 l_input_report_qualifier,
897 l_input_record_name,
898 input_empr_1,
899 input_empr_2,
900 input_empr_3,
901 input_empr_4,
902 input_empr_5,
903 empr_validate_flag,
904 empr_exclude_output_flag,
905 empr_out_1,
906 empr_out_2,
907 empr_out_3,
908 empr_out_4,
909 empr_out_5,
910 empr_out_6,
911 empr_out_7,
912 empr_out_8,
913 empr_out_9,
914 empr_out_10);
915
916 --
917 -- Pouplate All the Tags to be used for RE record Data Items
918 --
919
920 pay_us_w2_generic_extract.populate_re_data_tag;
921
922 /* Initialize with Default Value */
923
924 FOR I IN ltr_re_data_tag.first .. ltr_re_data_tag.last
925 LOOP
926 g_re_record(I).employer_data := ' ';
927 g_re_record(I).employer_tag := ltr_re_data_tag(I);
928 END LOOP;
929
930 /* Record Identifier */
931 g_re_record(ER_RECORD_IDENTIFIER).employer_data := 'RE';
932 g_re_record(ER_RECORD_IDENTIFIER).employer_tag := ltr_re_data_tag(ER_RECORD_IDENTIFIER);
933
934 /* Tax Year */
935 g_re_record(ER_TAX_YEAR).employer_data := p_reporting_year;
936 g_re_record(ER_TAX_YEAR).employer_tag := ltr_re_data_tag(ER_TAX_YEAR);
937
938 /* Agent Indicator Code
939 Employer - Agent(EIN)
940 Agent for EIN */
941 --
942 -- EIN Validation
943 --
944 l_validated_EIN :=
945 pay_us_report_data_validation.validate_W2_EIN( 'FED',
946 l_input_record_name,
947 ltr_ue_name_table(1).ue_value,
948 empr_out_10,
949 empr_validate_flag,
950 l_err
951 );
952 /* If Employer is 2678 Filer Agent EIN is reported otherwise Not */
953 IF ltr_ue_name_table(20).ue_value = 'Y'
954 THEN
955 --{
956 g_re_record(ER_AGENT_INDICATOR_CODE).employer_data := ltr_ue_name_table(20).ue_value;
957 g_re_record(ER_AGENT_INDICATOR_CODE).employer_tag := ltr_re_data_tag(ER_AGENT_INDICATOR_CODE);
958
959 g_re_record(ER_EIN).employer_data := l_validated_EIN;
960 g_re_record(ER_EIN).employer_tag := ltr_re_data_tag(ER_EIN);
961
962 g_re_record(ER_AGENT_EIN).employer_data := g_submitter_ein;
963 g_re_record(ER_AGENT_EIN).employer_tag := ltr_re_data_tag(ER_AGENT_EIN);
964
965 --}
966 ELSE
967 --{
968 g_re_record(ER_AGENT_INDICATOR_CODE).employer_data := ' ';
969 g_re_record(ER_AGENT_INDICATOR_CODE).employer_tag := ltr_re_data_tag(ER_AGENT_INDICATOR_CODE);
970
971 g_re_record(ER_EIN).employer_data := l_validated_EIN;
972 g_re_record(ER_EIN).employer_tag := ltr_re_data_tag(ER_EIN);
973
974 g_re_record(ER_AGENT_EIN).employer_data := ' ';
975 g_re_record(ER_AGENT_EIN).employer_tag := ltr_re_data_tag(ER_AGENT_EIN);
976 --}
977 END IF;
978 /* Terminating Business Indicator */
979 g_re_record(ER_TERMINATE_BUSINESS_IND).employer_data :=ltr_ue_name_table(2).ue_value;
980 g_re_record(ER_TERMINATE_BUSINESS_IND).employer_tag := ltr_re_data_tag(ER_TERMINATE_BUSINESS_IND);
981
982 /* Establishment Number */
983 g_re_record(ESTABLISHMENT_NUMBER).employer_data :=' ';
984 g_re_record(ESTABLISHMENT_NUMBER).employer_tag := ltr_re_data_tag(ESTABLISHMENT_NUMBER);
985
986 /* Other EIN */
987 g_re_record(ER_OTHER_EIN).employer_data :=ltr_ue_name_table(3).ue_value;
988 g_re_record(ER_OTHER_EIN).employer_tag := ltr_re_data_tag(ER_OTHER_EIN);
989
990 /* Employer Name */
991 g_re_record(ER_NAME).employer_data :=ltr_ue_name_table(4).ue_value;
992 g_re_record(ER_NAME).employer_tag := ltr_re_data_tag(ER_NAME);
993
994 /* Employer, Location Address */
995 g_re_record(ER_ADDRESS).employer_data :=empr_out_1;
996 g_re_record(ER_ADDRESS).employer_tag := ltr_re_data_tag(ER_ADDRESS);
997
998 /* Employer, Delivery Address */
999 g_re_record(ER_DELIVERY_ADDRESS).employer_data := empr_out_2;
1000 g_re_record(ER_DELIVERY_ADDRESS).employer_tag := ltr_re_data_tag(ER_DELIVERY_ADDRESS);
1001
1002 /* Employer, City */
1003 g_re_record(ER_CITY).employer_data := empr_out_3;
1004 g_re_record(ER_CITY).employer_tag := ltr_re_data_tag(ER_CITY);
1005
1006 /* Employer, State Abbreviation */
1007 g_re_record(ER_STATE_ABBREVIATION).employer_data := empr_out_4;
1008 g_re_record(ER_STATE_ABBREVIATION).employer_tag := ltr_re_data_tag(ER_STATE_ABBREVIATION);
1009
1010 /* Employer, Zip Code */
1011 g_re_record(ER_ZIP_CODE).employer_data := empr_out_5;
1012 g_re_record(ER_ZIP_CODE).employer_tag := ltr_re_data_tag(ER_ZIP_CODE);
1013
1014 /* Employer, Zip Code Extension */
1015 g_re_record(ER_ZIP_CODE_EXTENSION).employer_data := empr_out_6;
1016 g_re_record(ER_ZIP_CODE_EXTENSION).employer_tag := ltr_re_data_tag(ER_ZIP_CODE_EXTENSION);
1017
1018 /* Employer, Foriegn State or Province */
1019 g_re_record(ER_FOREIGN_STATE_PROVINCE).employer_data := empr_out_7;
1020 g_re_record(ER_FOREIGN_STATE_PROVINCE).employer_tag := ltr_re_data_tag(ER_FOREIGN_STATE_PROVINCE);
1021
1022 /* Employer, Foriegn Postal Code */
1023 g_re_record(ER_FOREIGN_POSTAL_CODE).employer_data := empr_out_8;
1024 g_re_record(ER_FOREIGN_POSTAL_CODE).employer_tag := ltr_re_data_tag(ER_FOREIGN_POSTAL_CODE);
1025
1026 /* Employer, Country Code */
1027 g_re_record(ER_COUNTRY_CODE).employer_data := empr_out_9;
1028 g_re_record(ER_COUNTRY_CODE).employer_tag := ltr_re_data_tag(ER_COUNTRY_CODE);
1029
1030 /* Employment Code */
1031 -- ltr_ue_name_table(17).ue_value has A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER
1032
1033 g_re_record(ER_GOVT_EMPLOYER).employer_data :=ltr_ue_name_table(17).ue_value;
1034 g_re_record(ER_GOVT_EMPLOYER).employer_tag := ltr_re_data_tag(ER_GOVT_EMPLOYER);
1035
1036 g_re_record(ER_TYPE_OF_EMPLOYMENT).employer_data :=ltr_ue_name_table(18).ue_value;
1037 g_re_record(ER_TYPE_OF_EMPLOYMENT).employer_tag := ltr_re_data_tag(ER_TYPE_OF_EMPLOYMENT);
1038
1039 IF ltr_ue_name_table(17).ue_value = 'N' THEN
1040 g_re_record(ER_EMPLOYMENT_CODE).employer_data := ltr_ue_name_table(18).ue_value;
1041 g_re_record(ER_EMPLOYMENT_CODE).employer_tag := ltr_re_data_tag(ER_EMPLOYMENT_CODE);
1042 ELSE
1043 g_re_record(ER_EMPLOYMENT_CODE).employer_data := 'R' ;
1044 g_re_record(ER_EMPLOYMENT_CODE).employer_tag := ltr_re_data_tag(ER_EMPLOYMENT_CODE);
1045 END IF;
1046
1047 /* Tax Jurisdiction Code */
1048 IF ltr_ue_name_table(6).ue_value = 'P' THEN
1049 g_re_record(ER_TAX_JD_CODE).employer_data := ltr_ue_name_table(6).ue_value;
1050 g_re_record(ER_TAX_JD_CODE).employer_tag := ltr_re_data_tag(ER_TAX_JD_CODE);
1051 ELSE
1052 g_re_record(ER_TAX_JD_CODE).employer_data := ' ' ;
1053 g_re_record(ER_TAX_JD_CODE).employer_tag := ltr_re_data_tag(ER_TAX_JD_CODE);
1054 END IF;
1055
1056 /* Third Party Sick Pay Indicator */
1057 IF ltr_ue_name_table(7).ue_value = 'Y' THEN
1058 g_re_record(ER_THIRD_PARTY_SICK_PAY_IND).employer_data := '1';
1059 g_re_record(ER_THIRD_PARTY_SICK_PAY_IND).employer_tag := ltr_re_data_tag(ER_THIRD_PARTY_SICK_PAY_IND);
1060 ELSE
1061 g_re_record(ER_THIRD_PARTY_SICK_PAY_IND).employer_data := '0' ;
1062 g_re_record(ER_THIRD_PARTY_SICK_PAY_IND).employer_tag := ltr_re_data_tag(ER_THIRD_PARTY_SICK_PAY_IND);
1063 END IF;
1064
1065 /* Social Security Wage Base */
1066 g_re_record(ER_SS_EE_WAGE_BASE).employer_data := ltr_ue_name_table(8).ue_value ;
1067 g_re_record(ER_SS_EE_WAGE_BASE).employer_tag := ltr_re_data_tag(ER_SS_EE_WAGE_BASE);
1068
1069 /* Social Security Wage Base Rate*/
1070 g_re_record(ER_SS_EE_WAGE_RATE).employer_data := ltr_ue_name_table(9).ue_value ;
1071 g_re_record(ER_SS_EE_WAGE_RATE).employer_tag := ltr_re_data_tag(ER_SS_EE_WAGE_RATE);
1072
1073 /* 1099R Transmitter Code*/
1074 g_re_record(ER_1099R_TRANSMITTER_CODE).employer_data := ltr_ue_name_table(10).ue_value ;
1075 g_re_record(ER_1099R_TRANSMITTER_CODE).employer_tag := ltr_re_data_tag(ER_1099R_TRANSMITTER_CODE);
1076
1077 /* 1099R Transmitter Indicator */
1078 g_re_record(ER_1099R_TRANSMITTER_INDICATOR).employer_data := ltr_ue_name_table(11).ue_value ;
1079 g_re_record(ER_1099R_TRANSMITTER_INDICATOR).employer_tag := ltr_re_data_tag(ER_1099R_TRANSMITTER_INDICATOR);
1080
1081 /* 1099R Transmitter Bureau Indicator */
1082 g_re_record(ER_1099R_BUREAU_INDICATOR).employer_data := ltr_ue_name_table(12).ue_value ;
1083 g_re_record(ER_1099R_BUREAU_INDICATOR).employer_tag := ltr_re_data_tag(ER_1099R_BUREAU_INDICATOR);
1084
1085 /* 1099R Combined Federal and State Filer */
1086 g_re_record(ER_1099R_COMBINED_FILER).employer_data := ltr_ue_name_table(13).ue_value ;
1087 g_re_record(ER_1099R_COMBINED_FILER).employer_tag := ltr_re_data_tag(ER_1099R_COMBINED_FILER);
1088
1089 /* State SIT Company Id number */
1090 g_re_record(ER_SIT_COMPANY_STATE_ID).employer_data := ltr_ue_name_table(14).ue_value ;
1091 g_re_record(ER_SIT_COMPANY_STATE_ID).employer_tag := ltr_re_data_tag(ER_SIT_COMPANY_STATE_ID);
1092
1093 /* State SUI Company Id number */
1094 g_re_record(ER_SUI_COMPANY_STATE_ID).employer_data := ltr_ue_name_table(15).ue_value ;
1095 g_re_record(ER_SUI_COMPANY_STATE_ID).employer_tag := ltr_re_data_tag(ER_SUI_COMPANY_STATE_ID);
1096
1097 /* FIPS Jurisdiction Code */
1098 g_re_record(ER_FIPS_CODE_JD).employer_data := ltr_ue_name_table(16).ue_value ;
1099 g_re_record(ER_FIPS_CODE_JD).employer_tag := ltr_re_data_tag(ER_FIPS_CODE_JD);
1100
1101 /* Blocking Factor */
1102 g_re_record(ER_BLOCKING_FACTOR).employer_data := ltr_ue_name_table(19).ue_value ;
1103 g_re_record(ER_BLOCKING_FACTOR).employer_tag := ltr_re_data_tag(ER_BLOCKING_FACTOR);
1104
1105 /* W2 2678 Filer */
1106 g_re_record(ER_W2_2678_FILER).employer_data := ltr_ue_name_table(20).ue_value ;
1107 g_re_record(ER_W2_2678_FILER).employer_tag := ltr_re_data_tag(ER_W2_2678_FILER);
1108
1109 /* Company Name */
1110 g_re_record(ER_COMPANY_NAME).employer_data := ltr_ue_name_table(21).ue_value ;
1111 g_re_record(ER_COMPANY_NAME).employer_tag := ltr_re_data_tag(ER_COMPANY_NAME);
1112
1113 /* Cotact Name */
1114 g_re_record(ER_CONTACT_NAME).employer_data := ltr_ue_name_table(22).ue_value ;
1115 g_re_record(ER_CONTACT_NAME).employer_tag := ltr_re_data_tag(ER_CONTACT_NAME);
1116
1117 /* Perferred Method of Problem Notification Code */
1118 g_re_record(ER_NOTIFICATION_METHOD).employer_data := ltr_ue_name_table(23).ue_value ;
1119 g_re_record(ER_NOTIFICATION_METHOD).employer_tag := ltr_re_data_tag(ER_NOTIFICATION_METHOD);
1120
1121 /* Preparer Code */
1122 g_re_record(ER_PREPARER).employer_data := ltr_ue_name_table(24).ue_value ;
1123 g_re_record(ER_PREPARER).employer_tag := ltr_re_data_tag(ER_PREPARER);
1124
1125 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
1126 FROM dual;
1127
1128 l_final_xml_string := '<EMPLOYER>'||EOL;
1129
1130 FOR I IN 1 .. g_ra_no_of_tag LOOP
1131 l_final_xml_string := l_final_xml_string || '<'||g_re_record(I).employer_tag||'>'||
1132 convert_special_char(g_re_record(I).employer_data)
1133 ||'</'||g_re_record(I).employer_tag||'>'|| EOL;
1134 -- HR_UTILITY_TRACE(l_final_xml_string);
1135 END LOOP;
1136 HR_UTILITY_TRACE(l_final_xml_string);
1137 p_final_string := l_final_xml_string;
1138 --pay_us_mmref_local_xml.write_to_magtape_lob(l_final_xml_string);
1139 --pay_core_files.write_to_magtape_lob(l_final_xml_string);
1140 --}
1141 END populate_arch_employer; -- End of Procedure populate_arch_employer
1142
1143
1144 --
1145 -- This procedure would be used to populate the Tag used for RW, RO, RS Data Record
1146 --
1147 PROCEDURE populate_ee_data_tag
1148 IS
1149 i number;
1150 BEGIN
1151
1152 ltr_ee_data_tag(1) := 'RW_RECORD_IDENTIFIER';
1153 ltr_ee_data_tag(2) := 'EE_SSN';
1154 ltr_ee_data_tag(3) := 'EE_FIRST_NAME';
1155 ltr_ee_data_tag(4) := 'EE_MIDDLE_INITIAL';
1156 ltr_ee_data_tag(5) := 'EE_LAST_NAME';
1157 ltr_ee_data_tag(6) := 'EE_SUFFIX';
1158 ltr_ee_data_tag(7) := 'EE_LOCATION_ADDRESS';
1159 ltr_ee_data_tag(8) := 'EE_DELIVERY_ADDRESS';
1160 ltr_ee_data_tag(9) := 'EE_CITY';
1161 ltr_ee_data_tag(10) := 'EE_STATE_ABBREVIATION';
1162 ltr_ee_data_tag(11) := 'EE_ZIP_CODE';
1163 ltr_ee_data_tag(12) := 'EE_ZIP_CODE_EXTENSION';
1164 ltr_ee_data_tag(13) := 'EE_FOREIGN_STATE_PROVINCE';
1165 ltr_ee_data_tag(14) := 'EE_FOREIGN_POSTAL_CODE';
1166 ltr_ee_data_tag(15) := 'EE_COUNTRY_CODE';
1167 ltr_ee_data_tag(16) := 'FIT_GROSS_WAGES';
1168 ltr_ee_data_tag(17) := 'FIT_WITHHELD';
1169 ltr_ee_data_tag(18) := 'SS_WAGES';
1170 ltr_ee_data_tag(19) := 'SS_TAX_WITHHELD';
1171 ltr_ee_data_tag(20) := 'MEDICARE_WAGES_TIPS';
1172 ltr_ee_data_tag(21) := 'MEDICARE_TAX_WITHHELD';
1173 ltr_ee_data_tag(22) := 'SS_TIPS';
1174 ltr_ee_data_tag(23) := 'EIC_ADVANCE';
1175 ltr_ee_data_tag(24) := 'W2_DEPENDENT_CARE';
1176 ltr_ee_data_tag(25) := 'W2_401K ';
1177 ltr_ee_data_tag(26) := 'W2_403B';
1178 ltr_ee_data_tag(27) := 'W2_408K ';
1179 ltr_ee_data_tag(28) := 'W2_457';
1180 ltr_ee_data_tag(29) := 'W2_501C';
1181 ltr_ee_data_tag(30) := 'W2_MILITARY_HOUSING';
1182 ltr_ee_data_tag(31) := 'W2_NONQUAL_457';
1183 ltr_ee_data_tag(32) := 'W2_HSA';
1184 ltr_ee_data_tag(33) := 'NON_QUAL_NOT_457';
1185 ltr_ee_data_tag(34) := 'W2_NONTAX_COMBAT';
1186 ltr_ee_data_tag(35) := 'W2_GROUP_TERM_LIFE';
1187 ltr_ee_data_tag(36) := 'W2_NONQUAL_STOCK';
1188 ltr_ee_data_tag(37) := 'W2_NONQUAL_DEF_COMP';
1189 ltr_ee_data_tag(38) := 'W2_ROTH_401K';
1190 ltr_ee_data_tag(39) := 'W2_ROTH_403B';
1191 ltr_ee_data_tag(40) := 'W2_ASG_STATUTORY_EMPLOYEE';
1192 ltr_ee_data_tag(41) := 'RETIREMENT_PLAN_INDICATOR';
1193 ltr_ee_data_tag(42) := 'W2_TP_SICK_PAY_IND';
1194 ltr_ee_data_tag(43) := 'RO_RECORD_IDENTIFIER';
1195 ltr_ee_data_tag(44) := 'RO_W2_BOX_8';
1196 ltr_ee_data_tag(45) := 'RO_UNCOLLECT_TAX_ON_TIPS';
1197 ltr_ee_data_tag(46) := 'RO_W2_MSA';
1198 ltr_ee_data_tag(47) := 'RO_W2_408P';
1199 ltr_ee_data_tag(48) := 'RO_W2_ADOPTION';
1200 ltr_ee_data_tag(49) := 'RO_W2_UNCOLL_SS_GTL';
1201 ltr_ee_data_tag(50) := 'RO_W2_UNCOLL_MED_GTL';
1202 ltr_ee_data_tag(51) := 'RO_W2_409A_NONQUAL_INCOM';
1203 ltr_ee_data_tag(52) := 'RO_CIVIL_STATUS';
1204 ltr_ee_data_tag(53) := 'RO_SPOUSE_SSN';
1205 ltr_ee_data_tag(54) := 'RO_WAGES_SUBJ_PR_TAX';
1206 ltr_ee_data_tag(55) := 'RO_COMM_SUBJ_PR_TAX';
1207 ltr_ee_data_tag(56) := 'RO_ALLOWANCE_SUBJ_PR_TAX';
1208 ltr_ee_data_tag(57) := 'RO_TIPS_SUBJ_PR_TAX';
1209 ltr_ee_data_tag(58) := 'RO_W2_STATE_WAGES';
1210 ltr_ee_data_tag(59) := 'RO_PR_TAX_WITHHELD';
1211 ltr_ee_data_tag(60) := 'RO_RETIREMENT_CONTRIB';
1212 ltr_ee_data_tag(61) := 'RS_TAXING_ENTITY_CODE';
1213 ltr_ee_data_tag(62) := 'RS_OPTIONAL_CODE';
1214 ltr_ee_data_tag(63) := 'RS_REPORTING_PERIOD';
1215 ltr_ee_data_tag(64) := 'RS_SQWL_UNEMP_INS_WAGES';
1216 ltr_ee_data_tag(65) := 'RS_SQWL_UNEMP_TXBL_WAGES';
1217 ltr_ee_data_tag(66) := 'RS_WEEKS_WORKED';
1218 ltr_ee_data_tag(67) := 'RS_DATE_FIRST_EMPLOYED';
1219 ltr_ee_data_tag(68) := 'RS_DATE_OF_SEPARATION';
1220 ltr_ee_data_tag(69) := 'RS_STATE_ER_ACCT_NUM';
1221 ltr_ee_data_tag(70) := 'RS_STATE_CODE';
1222 ltr_ee_data_tag(71) := 'RS_STATE_WAGES';
1223 ltr_ee_data_tag(72) := 'RS_SIT_WITHHELD';
1224 ltr_ee_data_tag(73) := 'RS_OTHER_STATE_DATA';
1225 ltr_ee_data_tag(74) := 'RS_STEIC_ADVANCE';
1226 ltr_ee_data_tag(75) := 'RS_SUI_EE_WITHHELD';
1227 ltr_ee_data_tag(76) := 'RS_SDI_EE_WITHHELD';
1228 ltr_ee_data_tag(77) := 'RS_SUPPLEMENTAL_DATA_1';
1229 ltr_ee_data_tag(78) := 'RS_SUPPLEMENTAL_DATA_2';
1230 ltr_ee_data_tag(79) := 'FIT_WITHHELD_THIRD_PARTY';
1231 --
1232 -- Following Loop structure used to debug the Tag Values
1233 --
1234 FOR I IN ltr_ee_data_tag.first .. ltr_ee_data_tag.last
1235 LOOP
1236 HR_UTILITY_TRACE('Tag'||to_char(i)|| ' : '|| ltr_ee_data_tag(i));
1237 END LOOP;
1238 END populate_ee_data_tag;
1239
1240 --
1241 -- This procedure would be used to populate the Tag used for Employee level Locality Data
1242 --
1243 PROCEDURE populate_ee_locality_tag
1244 IS
1245 i number;
1246 BEGIN
1247 -- ltr_ee_locality_tag
1248 ltr_ee_locality_tag(1) := 'CITY_JURISDICTION';
1249 ltr_ee_locality_tag(2) := 'CITY_NAME';
1250 ltr_ee_locality_tag(3) := 'COUNTY_NAME';
1251 ltr_ee_locality_tag(4) := 'TAX_TYPE';
1252 ltr_ee_locality_tag(5) := 'CITY_CODE';
1253 ltr_ee_locality_tag(6) := 'CITY_WAGES';
1254 ltr_ee_locality_tag(7) := 'CITY_TAX_WITHHELD';
1255
1256 -- Bug # 6117216 SD Reporting Changes START
1257 ltr_ee_locality_tag(8) := 'SD_JURISDICTION';
1258 ltr_ee_locality_tag(9) := 'SD_NAME';
1259 ltr_ee_locality_tag(10) := 'TAX_TYPE';
1260 ltr_ee_locality_tag(11) := 'SD_CODE';
1261 ltr_ee_locality_tag(12) := 'SD_WAGES';
1262 ltr_ee_locality_tag(13) := 'SD_TAX_WITHHELD';
1263 -- Bug # 6117216 SD Reporting Changes END
1264
1265 -- Following Loop structure used to debug the Tag Values
1266 --
1267 FOR I IN ltr_ee_locality_tag.first .. ltr_ee_locality_tag.last
1268 LOOP
1269 HR_UTILITY_TRACE('Tag'||to_char(i)|| ' : '|| ltr_ee_locality_tag(i));
1270 END LOOP;
1271 END populate_ee_locality_tag;
1272
1273 --
1274 -- This Procedue would be used to fetch all the employee releated data
1275 -- All the data would then be populated into a global pl/sql based table for
1276 -- construting XML
1277 --
1278 PROCEDURE populate_arch_employee(
1279 p_payroll_action_id NUMBER
1280 ,p_ye_assignment_action_id NUMBER
1281 ,p_tax_unit_id NUMBER
1282 ,p_assignment_id NUMBER
1283 ,p_date_earned DATE
1284 ,p_reporting_year VARCHAR2
1285 ,p_jurisdiction_code VARCHAR2
1286 ,p_state_code NUMBER
1287 ,p_state_abbreviation VARCHAR2
1288 ,p_locality_code VARCHAR2
1289 ,status VARCHAR2
1290 ,p_final_string OUT NOCOPY VARCHAR2
1291 )
1292 AS
1293 --{
1294 --
1295 -- Declaration of Index Value that will be used for storing and fetching Employer level Data
1296 -- from the global pl/sql table maintained for employer or Tax Unit level data
1297 --
1298 RW_RECORD_IDENTIFIER NUMBER :=1;
1299 EE_SSN NUMBER :=2;
1300 EE_FIRST_NAME NUMBER :=3;
1301 EE_MIDDLE_INITIAL NUMBER :=4;
1302 EE_LAST_NAME NUMBER :=5;
1303 EE_SUFFIX NUMBER :=6;
1304 EE_LOCATION_ADDRESS NUMBER :=7;
1305 EE_DELIVERY_ADDRESS NUMBER :=8;
1306 EE_CITY NUMBER :=9;
1307 EE_STATE_ABBREVIATION NUMBER :=10;
1308 EE_ZIP_CODE NUMBER :=11;
1309 EE_ZIP_CODE_EXTENSION NUMBER :=12;
1310 EE_FOREIGN_STATE_PROVINCE NUMBER :=13;
1311 EE_FOREIGN_POSTAL_CODE NUMBER :=14;
1312 EE_COUNTRY_CODE NUMBER :=15;
1313 FIT_GROSS_WAGES NUMBER :=16;
1314 FIT_WITHHELD NUMBER :=17;
1315 SS_WAGES NUMBER :=18;
1316 SS_TAX_WITHHELD NUMBER :=19;
1317 MEDICARE_WAGES_TIPS NUMBER :=20;
1318 MEDICARE_TAX_WITHHELD NUMBER :=21;
1319 SS_TIPS NUMBER :=22;
1320 EIC_ADVANCE NUMBER :=23;
1321 W2_DEPENDENT_CARE NUMBER :=24;
1322 W2_401K NUMBER :=25;
1323 W2_403B NUMBER :=26;
1324 W2_408K NUMBER :=27;
1325 W2_457 NUMBER :=28;
1326 W2_501C NUMBER :=29;
1327 W2_MILITARY_HOUSING NUMBER :=30;
1328 W2_NONQUAL_457 NUMBER :=31;
1329 W2_HSA NUMBER :=32;
1330 NON_QUAL_NOT_457 NUMBER :=33;
1331 W2_NONTAX_COMBAT NUMBER :=34;
1332 W2_GROUP_TERM_LIFE NUMBER :=35;
1333 W2_NONQUAL_STOCK NUMBER :=36;
1334 W2_NONQUAL_DEF_COMP NUMBER :=37;
1335 W2_ROTH_401K NUMBER :=38;
1336 W2_ROTH_403B NUMBER :=39;
1337 W2_ASG_STATUTORY_EMPLOYEE NUMBER :=40;
1338 RETIREMENT_PLAN_INDICATOR NUMBER :=41;
1339 W2_TP_SICK_PAY_IND NUMBER :=42;
1340 RO_RECORD_IDENTIFIER NUMBER :=43;
1341 RO_W2_BOX_8 NUMBER :=44;
1342 RO_UNCOLLECT_TAX_ON_TIPS NUMBER :=45;
1343 RO_W2_MSA NUMBER :=46;
1344 RO_W2_408P NUMBER :=47;
1345 RO_W2_ADOPTION NUMBER :=48;
1346 RO_W2_UNCOLL_SS_GTL NUMBER :=49;
1347 RO_W2_UNCOLL_MED_GTL NUMBER :=50;
1348 RO_W2_409A_NONQUAL_INCOM NUMBER :=51;
1349 RO_CIVIL_STATUS NUMBER :=52;
1350 RO_SPOUSE_SSN NUMBER :=53;
1351 RO_WAGES_SUBJ_PR_TAX NUMBER :=54;
1352 RO_COMM_SUBJ_PR_TAX NUMBER :=55;
1353 RO_ALLOWANCE_SUBJ_PR_TAX NUMBER :=56;
1354 RO_TIPS_SUBJ_PR_TAX NUMBER :=57;
1355 RO_W2_STATE_WAGES NUMBER :=58;
1356 RO_PR_TAX_WITHHELD NUMBER :=59;
1357 RO_RETIREMENT_CONTRIB NUMBER :=60;
1358 RS_TAXING_ENTITY_CODE NUMBER :=61;
1359 RS_OPTIONAL_CODE NUMBER :=62;
1360 RS_REPORTING_PERIOD NUMBER :=63;
1361 RS_SQWL_UNEMP_INS_WAGES NUMBER :=64;
1362 RS_SQWL_UNEMP_TXBL_WAGES NUMBER :=65;
1363 RS_WEEKS_WORKED NUMBER :=66;
1364 RS_DATE_FIRST_EMPLOYED NUMBER :=67;
1365 RS_DATE_OF_SEPARATION NUMBER :=68;
1366 RS_STATE_ER_ACCT_NUM NUMBER :=69;
1367 RS_STATE_CODE NUMBER :=70;
1368 RS_STATE_WAGES NUMBER :=71;
1369 RS_SIT_WITHHELD NUMBER :=72;
1370 RS_OTHER_STATE_DATA NUMBER :=73;
1371 RS_STEIC_ADVANCE NUMBER :=74;
1372 RS_SUI_EE_WITHHELD NUMBER :=75;
1373 RS_SDI_EE_WITHHELD NUMBER :=76;
1374 RS_SUPPLEMENTAL_DATA_1 NUMBER :=77;
1375 RS_SUPPLEMENTAL_DATA_2 NUMBER :=78;
1376 FIT_WITHHELD_THIRD_PARTY NUMBER :=79;
1377
1378 --
1379 -- Local Variables required for Employee
1380 --
1381 l_payroll_action_id NUMBER;
1382 l_assignment_id NUMBER;
1383 l_date_earned DATE;
1384 l_tax_unit_id NUMBER;
1385
1386
1387 /* Local Variables for EMPLOYEE INFORMATION Input */
1388 input_empe_name VARCHAR2(200) := 'EE_ADDRESS';
1389 input_empe_1 VARCHAR2(200) := ' ';
1390 input_empe_2 VARCHAR2(200) := ' ';
1391 input_empe_3 VARCHAR2(200) := ' ';
1392 input_empe_4 VARCHAR2(200) := ' ';
1393 input_empe_5 VARCHAR2(200) := ' ';
1394 input_empe_validate_flag VARCHAR2(200) := 'Y';
1395 /* Local Variables for EMPLOYEE INFORMATION Output */
1396 empe_exclude_output_flag VARCHAR2(200) := 'N';
1397 empe_out_1 VARCHAR2(200) := ' ';
1398 empe_out_2 VARCHAR2(200) := ' ';
1399 empe_out_3 VARCHAR2(200) := ' ';
1400 empe_out_4 VARCHAR2(200) := ' ';
1401 empe_out_5 VARCHAR2(200) := ' ';
1402 empe_out_6 VARCHAR2(200) := ' ';
1403 empe_out_7 VARCHAR2(200) := ' ';
1404 empe_out_8 VARCHAR2(200) := ' ';
1405 empe_out_9 VARCHAR2(200) := ' ';
1406 empe_out_10 VARCHAR2(200) := ' ';
1407
1408 l_input_report_type VARCHAR2(200) := 'W2';
1409 l_input_report_type_format VARCHAR2(200) := 'MMREF';
1410 l_input_record_name VARCHAR2(200) := 'RW';
1411 l_effective_date VARCHAR2(200);
1412 l_item_name VARCHAR2(200);
1413 l_input_report_qualifier VARCHAR2(200);
1414 i NUMBER :=0;
1415 j NUMBER :=0;
1416 --PL Table used for storing and manipulating DataBaseItem used for
1417 -- Submitter level information
1418 TYPE ee_ue_rec IS RECORD (
1419 ue_name varchar2(200),
1420 ue_data_level varchar2(200),
1421 ue_value varchar2(200),
1422 data_type varchar2(200),
1423 mandatory varchar2(200),
1424 negative_check varchar2(200)
1425 );
1426 TYPE ee_ue_record IS TABLE OF ee_ue_rec
1427 INDEX BY BINARY_INTEGER;
1428 ltr_ue_name_table ee_ue_record;
1429
1430 l_number_ee_dbi NUMBER := 6;
1431
1432 employee_data VARCHAR2(200);
1433
1434 -- SRS Parameter
1435
1436 l_srs_trns_pin VARCHAR2(200);
1437 l_srs_trns_tlcn VARCHAR2(200);
1438 l_srs_resub_indicator VARCHAR2(200);
1439
1440 -- Derived Local Variables
1441 l_resub_indicator VARCHAR2(200);
1442 l_reporting_date VARCHAR2(200);
1443 l_reporting_period VARCHAR2(200);
1444
1445 l_err BOOLEAN := FALSE;
1446 l_validate VARCHAR2(100);
1447 l_validated_EIN VARCHAR2(200);
1448
1449 l_final_xml_string VARCHAR2(32767);
1450 l_data_item_xml VARCHAR2(32767);
1451 l_last_xml CLOB;
1452 l_is_temp_final_xml VARCHAR2(2);
1453 l_output_location VARCHAR2(100);
1454 l_instr_template VARCHAR2(100);
1455 EOL VARCHAR2(10);
1456 l_status VARCHAR2(200) := 'SUCCESS';
1457
1458 --
1459 -- RCO Record
1460 --
1461 /* initalize parameters for Bal Call for Puerto Rico */
1462 ro_input_2 VARCHAR2(200);
1463 ro_input_3 VARCHAR2(200);
1464 ro_input_4 VARCHAR2(200);
1465 ro_input_5 VARCHAR2(200);
1466 ro_validate VARCHAR2(200) := 'Y';
1467 ro_exlude_from_out VARCHAR2(200) := 'N';
1468 out_terr_taxabable_allow_per VARCHAR2(200);
1469 out_terr_taxabale_com_per VARCHAR2(200);
1470 out_terr_taxabale_tips_per VARCHAR2(200);
1471 out_sit_with_per VARCHAR2(200);
1472 out_w2_state_wages VARCHAR2(200);
1473 ro_out_6 VARCHAR2(200);
1474 ro_out_7 VARCHAR2(200);
1475 ro_out_8 VARCHAR2(200);
1476 ro_out_9 VARCHAR2(200);
1477 ro_out_10 VARCHAR2(200);
1478 out_ret_contrib_perjdgreytd VARCHAR2(200);
1479 nout_ret_contrib_perjdgreytd NUMBER := 0;
1480 nout_terr_taxabable_allow_per NUMBER := 0;
1481 nout_terr_taxabale_com_per NUMBER := 0;
1482 nout_terr_taxabale_tips_per NUMBER := 0;
1483 nout_sit_with_per NUMBER := 0;
1484 nout_total_w2_state_wages NUMBER := 0;
1485 nout_wages_subject NUMBER := 0;
1486 l_zero_ro_record VARCHAR2(200) := 'N';
1487 l_tax_tax_jurisdiction VARCHAR2(200) := ' ';
1488
1489 /* RS Record Local Varialbes */
1490 l_state_wages NUMBER := 0;
1491
1492 Cursor c_locality_jurisdiction (c_asgn_act_id NUMBER,
1493 c_state_code VARCHAR2,
1494 c_locality_code VARCHAR2)
1495 IS
1496 SELECT faic1.context Jurisdiction_code
1497 FROM pay_assignment_actions paa, -- YREND PAA
1498 pay_payroll_actions ppa, -- YREND PPA
1499 ff_contexts fc1, -- FOR CITY CONTEXT
1500 ff_archive_items fai1, -- CITY
1501 ff_archive_item_contexts faic1, -- CITY_CONTEXT
1502 ff_database_items fdi1 --DATABASE_ITEMS FOR CITY_WITHHELD
1503 WHERE paa.assignment_action_id = c_asgn_act_id
1504 AND ppa.payroll_action_id = paa.payroll_action_id
1505 AND fc1.context_name = 'JURISDICTION_CODE'
1506 AND faic1.context_id = fc1.context_id
1507 AND fdi1.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
1508 AND fdi1.user_entity_id = fai1.user_entity_id
1509 AND fai1.context1 = paa.assignment_action_id
1510 AND fai1.archive_item_id = faic1.archive_item_id
1511 AND ltrim(rtrim(faic1.context)) like c_state_code||'%'
1512 AND c_locality_code IS NULL
1513 AND rtrim(ltrim(fai1.value)) <> '0'
1514 AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
1515 WHERE puctif.jurisdiction_code = ltrim(rtrim(faic1.context))
1516 AND puctif.effective_start_date < ppa.effective_date
1517 AND puctif.effective_end_date >= ppa.effective_date
1518 )
1519 UNION
1520 SELECT faic1.context Jurisdiction_code
1521 FROM pay_assignment_actions paa, -- YREND PAA
1522 pay_payroll_actions ppa, -- YREND PPA
1523 ff_contexts fc1, -- FOR CITY CONTEXT
1524 ff_archive_items fai1, -- CITY
1525 ff_archive_item_contexts faic1, -- CITY_CONTEXT
1526 ff_database_items fdi1 --DATABASE_ITEMS FOR CITY_WITHHELD
1527 WHERE paa.assignment_action_id = c_asgn_act_id
1528 AND ppa.payroll_action_id = paa.payroll_action_id
1529 AND fc1.context_name = 'JURISDICTION_CODE'
1530 AND faic1.context_id = fc1.context_id
1531 AND fdi1.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
1532 AND fdi1.user_entity_id = fai1.user_entity_id
1533 AND fai1.context1 = paa.assignment_action_id
1534 AND fai1.archive_item_id = faic1.archive_item_id
1535 AND substr(ltrim(rtrim(faic1.context)),1,2) = c_state_code
1536 AND substr(ltrim(rtrim(faic1.context)),8,4) = substr(c_locality_code,8,4)
1537 AND c_locality_code IS NOT NULL
1538 AND rtrim(ltrim(fai1.value)) <> '0'
1539 AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
1540 WHERE puctif.jurisdiction_code = ltrim(rtrim(faic1.context))
1541 AND puctif.effective_start_date < ppa.effective_date
1542 AND puctif.effective_end_date >= ppa.effective_date
1543 )
1544 -- Bug # 6117216 SD Reporting Changes START
1545 UNION
1546 SELECT faic1.context Jurisdiction_code
1547 FROM pay_assignment_actions paa, -- YREND PAA
1548 pay_payroll_actions ppa, -- YREND PPA
1549 ff_contexts fc1, -- FOR CITY CONTEXT
1550 ff_archive_items fai1, -- CITY
1551 ff_archive_item_contexts faic1, -- CITY_CONTEXT
1552 ff_database_items fdi1 --DATABASE_ITEMS FOR CITY_WITHHELD
1553 WHERE paa.assignment_action_id = c_asgn_act_id
1554 AND ppa.payroll_action_id = paa.payroll_action_id
1555 AND fc1.context_name = 'JURISDICTION_CODE'
1556 AND faic1.context_id = fc1.context_id
1557 AND fdi1.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
1558 AND fdi1.user_entity_id = fai1.user_entity_id
1559 AND fai1.context1 = paa.assignment_action_id
1560 AND fai1.archive_item_id = faic1.archive_item_id
1561 AND ltrim(rtrim(faic1.context)) like c_state_code||'%'
1562 -- AND c_locality_code IS NULL
1563 AND rtrim(ltrim(fai1.value)) <> '0'
1564 /* To Be Modified Later */
1565 /* AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
1566 WHERE puctif.jurisdiction_code = ltrim(rtrim(faic1.context))
1567 AND puctif.effective_start_date < ppa.effective_date
1568 AND puctif.effective_end_date >= ppa.effective_date
1569 )*/;
1570 /* To Be Modified Later */
1571
1572 -- Bug # 6117216 SD Reporting Changes END
1573
1574
1575
1576 l_locality_code VARCHAR2(200);
1577 l_jurisdiction_code VARCHAR2(200);
1578 l_city_name VARCHAR2(200);
1579 l_county_name VARCHAR2(200);
1580 l_tax_type VARCHAR2(200);
1581 l_city_code VARCHAR2(200);
1582 l_city_wages VARCHAR2(200);
1583 l_city_tax_withheld VARCHAR2(200);
1584
1585 CITY_JURISDICTION NUMBER := 1;
1586 CITY_NAME NUMBER := 2;
1587 COUNTY_NAME NUMBER := 3;
1588 TAX_TYPE NUMBER := 4;
1589 CITY_CODE NUMBER := 5;
1590 CITY_WAGES NUMBER := 6;
1591 CITY_TAX_WITHHELD NUMBER := 7;
1592
1593
1594 --PL Table used for storing and manipulating DataBaseItem used for
1595 -- Employee level locality Data
1596 TYPE ee_locality_ue_rec IS RECORD (
1597 ue_name varchar2(200),
1598 ue_data_level varchar2(200),
1599 ue_value varchar2(200),
1600 data_type varchar2(200),
1601 mandatory varchar2(200),
1602 negative_check varchar2(200)
1603 );
1604 TYPE ee_locality_ue_record IS TABLE OF ee_locality_ue_rec
1605 INDEX BY BINARY_INTEGER;
1606 ltr_ue_locality ee_locality_ue_record;
1607 k NUMBER := 0;
1608
1609 CURSOR c_city_data(c_jurisdiction_code VARCHAR2,
1610 c_effective_date VARCHAR2)
1611 IS
1612 SELECT c.city_name city_name,
1613 n.county_name county_name,
1614 'C' tax_type,
1615 a.city_information1 city_code
1616 from pay_us_city_tax_info_f a,
1617 pay_us_city_names c,
1618 pay_us_counties n
1619 where sysdate between a.effective_start_date and a.effective_end_date
1620 and a.jurisdiction_code = c_jurisdiction_code
1621 and c.primary_flag = 'Y'
1622 and a.city_tax = 'Y'
1623 and c.city_code = substr(a.jurisdiction_code,8,4)
1624 and c.county_code = substr(a.jurisdiction_code,4,3)
1625 and c.state_code = substr(a.jurisdiction_code,1,2)
1626 and c.county_code = n.county_code
1627 and c.state_code = n.state_code;
1628
1629 -- Bug # 6117216 SD Reporting Changes START
1630 CURSOR c_sd_data (c_jurisdiction_code VARCHAR2)
1631 IS
1632 SELECT distinct c.school_dst_name,
1633 'SD' tax_type,
1634 substr(c_jurisdiction_code,4,5) city_code
1635 from pay_us_city_school_dsts c
1636 where c.school_dst_code = substr(c_jurisdiction_code,4,5)
1637 and c.state_code = substr(c_jurisdiction_code,1,2);
1638 -- Bug # 6117216 SD Reporting Changes END
1639
1640 /* End of Variable Declarations */
1641 BEGIN
1642 --{
1643 --
1644 -- Fetch all Context or Parameters set at the Transmitter Cursor
1645 --
1646
1647 l_tax_unit_id := p_tax_unit_id;
1648 l_payroll_action_id := p_payroll_action_id;
1649 l_date_earned := p_date_earned;
1650 l_reporting_date := '31-DEC-'||p_reporting_year;
1651 l_effective_date := l_reporting_date;
1652 l_reporting_period := '12' || p_reporting_year;
1653
1654 --
1655 -- Fetch SRS Parameter
1656 --
1657 l_srs_trns_pin := pay_magtape_generic.get_parameter_value('TRNS_PIN');
1658 l_srs_trns_tlcn := pay_magtape_generic.get_parameter_value('TRNS_TLCN');
1659
1660 l_input_report_qualifier := pay_magtape_generic.get_parameter_value('TRANSFER_STATE');
1661
1662 ltr_ue_name_table.delete;
1663 i := 0;
1664 --
1665 -- Fetch Archived Values of various DBIs used in submitter Record
1666 --
1667 i := i +1;
1668 ltr_ue_name_table(i).ue_name := 'A_PER_NATIONAL_IDENTIFIER'; -- AI 1
1669 ltr_ue_name_table(i).ue_data_level := 'PER';
1670 i := i +1;
1671 ltr_ue_name_table(i).ue_name := 'A_PER_FIRST_NAME'; -- AI 2
1672 ltr_ue_name_table(i).ue_data_level := 'PER';
1673 i := i +1;
1674 ltr_ue_name_table(i).ue_name := 'A_PER_MIDDLE_NAMES' ; --AI 3
1675 ltr_ue_name_table(i).ue_data_level := 'PER';
1676 i := i +1;
1677 ltr_ue_name_table(i).ue_name := 'A_PER_LAST_NAME'; --AI 4
1678 ltr_ue_name_table(i).ue_data_level := 'PER';
1679 i := i +1;
1680 ltr_ue_name_table(i).ue_name := 'A_PER_SUFFIX'; --AI 5
1681 ltr_ue_name_table(i).ue_data_level := 'PER';
1682 i := i +1;
1683 ltr_ue_name_table(i).ue_name := 'A_GROSS_EARNINGS_PER_GRE_YTD'; --AI 6
1684 ltr_ue_name_table(i).ue_data_level :='FED';
1685 ltr_ue_name_table(i).data_type :='AMT';
1686 ltr_ue_name_table(i).negative_check := 'Y';
1687 i := i +1;
1688 ltr_ue_name_table(i).ue_name := 'A_FIT_WITHHELD_PER_GRE_YTD'; --AI 7
1689 ltr_ue_name_table(i).ue_data_level :='FED';
1690 ltr_ue_name_table(i).data_type :='AMT';
1691 ltr_ue_name_table(i).negative_check := 'Y';
1692 i := i +1;
1693 ltr_ue_name_table(i).ue_name := 'A_SS_EE_TAXABLE_PER_GRE_YTD'; --AI 8
1694 ltr_ue_name_table(i).ue_data_level :='FED';
1695 ltr_ue_name_table(i).data_type :='AMT';
1696 ltr_ue_name_table(i).negative_check := 'Y';
1697 i := i +1;
1698 ltr_ue_name_table(i).ue_name := 'A_SS_EE_WITHHELD_PER_GRE_YTD'; --AI 9
1699 ltr_ue_name_table(i).ue_data_level :='FED';
1700 ltr_ue_name_table(i).data_type :='AMT';
1701 ltr_ue_name_table(i).negative_check := 'Y';
1702 i := i +1;
1703 ltr_ue_name_table(i).ue_name := 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD'; --AI 10
1704 ltr_ue_name_table(i).ue_data_level :='FED';
1705 ltr_ue_name_table(i).data_type :='AMT';
1706 ltr_ue_name_table(i).negative_check := 'Y';
1707 i := i +1;
1708 ltr_ue_name_table(i).ue_name := 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD'; --AI 11
1709 ltr_ue_name_table(i).ue_data_level :='FED';
1710 ltr_ue_name_table(i).data_type :='AMT';
1711 ltr_ue_name_table(i).negative_check := 'Y';
1712 i := i +1;
1713 ltr_ue_name_table(i).ue_name := 'A_W2_BOX_7_PER_GRE_YTD'; --AI 12
1714 ltr_ue_name_table(i).ue_data_level :='FED';
1715 ltr_ue_name_table(i).data_type :='AMT';
1716 ltr_ue_name_table(i).negative_check := 'Y';
1717 i := i +1;
1718 ltr_ue_name_table(i).ue_name := 'A_EIC_ADVANCE_PER_GRE_YTD'; --AI 13
1719 ltr_ue_name_table(i).ue_data_level :='FED';
1720 ltr_ue_name_table(i).data_type :='AMT';
1721 ltr_ue_name_table(i).negative_check := 'Y';
1722 i := i +1;
1723 ltr_ue_name_table(i).ue_name := 'A_W2_DEPENDENT_CARE_PER_GRE_YTD'; --AI 14
1724 ltr_ue_name_table(i).ue_data_level :='FED';
1725 ltr_ue_name_table(i).data_type :='AMT';
1726 ltr_ue_name_table(i).negative_check := 'Y';
1727 i := i +1;
1728 ltr_ue_name_table(i).ue_name := 'A_W2_401K_PER_GRE_YTD'; --AI 15
1729 ltr_ue_name_table(i).ue_data_level :='FED';
1730 ltr_ue_name_table(i).data_type :='AMT';
1731 ltr_ue_name_table(i).negative_check := 'Y';
1732 i := i +1;
1733 ltr_ue_name_table(i).ue_name := 'A_W2_403B_PER_GRE_YTD'; --AI 16
1734 ltr_ue_name_table(i).ue_data_level :='FED';
1735 ltr_ue_name_table(i).data_type :='AMT';
1736 ltr_ue_name_table(i).negative_check := 'Y';
1737 i := i +1;
1738 ltr_ue_name_table(i).ue_name := 'A_W2_408K_PER_GRE_YTD'; --AI 17
1739 ltr_ue_name_table(i).ue_data_level :='FED';
1740 ltr_ue_name_table(i).data_type :='AMT';
1741 ltr_ue_name_table(i).negative_check := 'Y';
1742 i := i +1;
1743 ltr_ue_name_table(i).ue_name := 'A_W2_457_PER_GRE_YTD'; --AI 18
1744 ltr_ue_name_table(i).ue_data_level :='FED';
1745 ltr_ue_name_table(i).data_type :='AMT';
1746 ltr_ue_name_table(i).negative_check := 'Y';
1747 i := i +1;
1748 ltr_ue_name_table(i).ue_name := 'A_W2_501C_PER_GRE_YTD'; --AI 19
1749 ltr_ue_name_table(i).ue_data_level :='FED';
1750 ltr_ue_name_table(i).data_type :='AMT';
1751 ltr_ue_name_table(i).negative_check := 'Y';
1752 i := i +1;
1753 ltr_ue_name_table(i).ue_name := 'A_W2_NONQUAL_PLAN_PER_GRE_YTD'; --AI 20
1754 ltr_ue_name_table(i).ue_data_level :='FED';
1755 ltr_ue_name_table(i).data_type :='AMT';
1756 ltr_ue_name_table(i).negative_check := 'Y';
1757 i := i +1;
1758 ltr_ue_name_table(i).ue_name := 'A_W2_NONQUAL_457_PER_GRE_YTD'; --AI 21
1759 ltr_ue_name_table(i).ue_data_level :='FED';
1760 ltr_ue_name_table(i).data_type :='AMT';
1761 ltr_ue_name_table(i).negative_check := 'Y';
1762 i := i +1;
1763 ltr_ue_name_table(i).ue_name := 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD'; --AI 22
1764 ltr_ue_name_table(i).ue_data_level :='FED';
1765 ltr_ue_name_table(i).data_type :='AMT';
1766 ltr_ue_name_table(i).negative_check := 'Y';
1767 i := i +1;
1768 ltr_ue_name_table(i).ue_name := 'A_W2_NONQUAL_STOCK_PER_GRE_YTD'; --AI 23
1769 ltr_ue_name_table(i).ue_data_level :='FED';
1770 ltr_ue_name_table(i).data_type :='AMT';
1771 ltr_ue_name_table(i).negative_check := 'Y';
1772 i := i +1;
1773 ltr_ue_name_table(i).ue_name := 'A_W2_ASG_STATUTORY_EMPLOYEE'; --AI 24
1774 ltr_ue_name_table(i).ue_data_level :='FED';
1775 ltr_ue_name_table(i).data_type :='AMT';
1776 ltr_ue_name_table(i).negative_check := 'Y';
1777 i := i +1;
1778 ltr_ue_name_table(i).ue_name := 'A_W2_PENSION_PLAN_PER_GRE_YTD'; --AI 25
1779 ltr_ue_name_table(i).ue_data_level :='FED';
1780 ltr_ue_name_table(i).data_type :='AMT';
1781 ltr_ue_name_table(i).negative_check := 'Y';
1782 i := i +1;
1783 ltr_ue_name_table(i).ue_name := 'A_DEF_COMP_401K_PER_GRE_YTD'; --AI 26
1784 ltr_ue_name_table(i).ue_data_level :='FED';
1785 ltr_ue_name_table(i).data_type :='AMT';
1786 ltr_ue_name_table(i).negative_check := 'Y';
1787 i := i +1;
1788 ltr_ue_name_table(i).ue_name := 'A_FIT_3RD_PARTY_PER_GRE_YTD'; --AI 27
1789 ltr_ue_name_table(i).ue_data_level :='FED';
1790 ltr_ue_name_table(i).data_type :='AMT';
1791 ltr_ue_name_table(i).negative_check := 'Y';
1792 i := i +1;
1793 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_THIRD_PARTY_SICK_PAY'; --AI 28
1794 ltr_ue_name_table(i).ue_data_level :='FED';
1795 ltr_ue_name_table(i).data_type :='AMT';
1796 ltr_ue_name_table(i).negative_check := 'Y';
1797 i := i +1;
1798 ltr_ue_name_table(i).ue_name := 'A_REGULAR_EARNINGS_PER_GRE_YTD'; --AI 29
1799 ltr_ue_name_table(i).ue_data_level :='FED';
1800 ltr_ue_name_table(i).data_type :='AMT';
1801 ltr_ue_name_table(i).negative_check := 'Y';
1802 i := i +1;
1803 ltr_ue_name_table(i).ue_name := 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'; --AI 30
1804 ltr_ue_name_table(i).ue_data_level :='FED';
1805 ltr_ue_name_table(i).data_type :='AMT';
1806 ltr_ue_name_table(i).negative_check := 'Y';
1807 i := i +1;
1808 ltr_ue_name_table(i).ue_name := 'A_DEF_COMP_401K_PER_GRE_YTD'; --AI 31
1809 ltr_ue_name_table(i).ue_data_level :='FED';
1810 ltr_ue_name_table(i).data_type :='AMT';
1811 ltr_ue_name_table(i).negative_check := 'Y';
1812 i := i +1;
1813 ltr_ue_name_table(i).ue_name := 'A_DEF_COMP_401K_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'; --AI 32
1814 ltr_ue_name_table(i).ue_data_level :='FED';
1815 ltr_ue_name_table(i).data_type :='AMT';
1816 ltr_ue_name_table(i).negative_check := 'Y';
1817 i := i +1;
1818 ltr_ue_name_table(i).ue_name := 'A_SECTION_125_PER_GRE_YTD'; --AI 33
1819 ltr_ue_name_table(i).ue_data_level :='FED';
1820 ltr_ue_name_table(i).data_type :='AMT';
1821 ltr_ue_name_table(i).negative_check := 'Y';
1822 i := i +1;
1823 ltr_ue_name_table(i).ue_name := 'A_SECTION_125_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'; --AI 34
1824 ltr_ue_name_table(i).ue_data_level :='FED';
1825 ltr_ue_name_table(i).data_type :='AMT';
1826 ltr_ue_name_table(i).negative_check := 'Y';
1827 i := i +1;
1828 ltr_ue_name_table(i).ue_name := 'A_DEPENDENT_CARE_PER_GRE_YTD'; --AI 35
1829 ltr_ue_name_table(i).ue_data_level :='FED';
1830 ltr_ue_name_table(i).data_type :='AMT';
1831 ltr_ue_name_table(i).negative_check := 'Y';
1832 i := i +1;
1833 ltr_ue_name_table(i).ue_name := 'A_DEPENDENT_CARE_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'; --AI 36
1834 ltr_ue_name_table(i).ue_data_level :='FED';
1835 ltr_ue_name_table(i).data_type :='AMT';
1836 ltr_ue_name_table(i).negative_check := 'Y';
1837 i := i +1;
1838 ltr_ue_name_table(i).ue_name := 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD'; -- AI 37
1839 ltr_ue_name_table(i).ue_data_level :='FED';
1840 ltr_ue_name_table(i).data_type :='AMT';
1841 ltr_ue_name_table(i).negative_check := 'Y';
1842 i := i +1;
1843 ltr_ue_name_table(i).ue_name := 'A_W2_TP_SICK_PAY_PER_GRE_YTD'; --AI 38
1844 ltr_ue_name_table(i).ue_data_level :='FED';
1845 ltr_ue_name_table(i).data_type :='AMT';
1846 ltr_ue_name_table(i).negative_check := 'Y';
1847 i := i +1;
1848 ltr_ue_name_table(i).ue_name := 'A_W2_NONTAX_COMBAT_PER_GRE_YTD'; --AI 39
1849 ltr_ue_name_table(i).ue_data_level :='FED';
1850 ltr_ue_name_table(i).data_type :='AMT';
1851 ltr_ue_name_table(i).negative_check := 'Y';
1852 i := i +1;
1853 ltr_ue_name_table(i).ue_name := 'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD'; --AI 40
1854 ltr_ue_name_table(i).ue_data_level :='FED';
1855 ltr_ue_name_table(i).data_type :='AMT';
1856 ltr_ue_name_table(i).negative_check := 'Y';
1857 i := i +1;
1858 ltr_ue_name_table(i).ue_name := 'A_W2_ROTH_401K_PER_GRE_YTD'; --AI 41
1859 ltr_ue_name_table(i).ue_data_level :='FED';
1860 ltr_ue_name_table(i).data_type :='AMT';
1861 ltr_ue_name_table(i).negative_check := 'Y';
1862 i := i +1;
1863 ltr_ue_name_table(i).ue_name := 'A_W2_ROTH_403B_PER_GRE_YTD'; --AI 42
1864 ltr_ue_name_table(i).ue_data_level :='FED';
1865 ltr_ue_name_table(i).data_type :='AMT';
1866 ltr_ue_name_table(i).negative_check := 'Y';
1867 i := i +1;
1868 ltr_ue_name_table(i).ue_name := 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD'; --AI 43
1869 ltr_ue_name_table(i).ue_data_level :='FED';
1870 ltr_ue_name_table(i).data_type :='AMT';
1871 ltr_ue_name_table(i).negative_check := 'Y';
1872 i := i +1;
1873 ltr_ue_name_table(i).ue_name := 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD'; --AI 44
1874 ltr_ue_name_table(i).ue_data_level :='FED';
1875 ltr_ue_name_table(i).data_type :='AMT';
1876 ltr_ue_name_table(i).negative_check := 'Y';
1877 i := i +1;
1878 ltr_ue_name_table(i).ue_name := 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'; --AI 45
1879 ltr_ue_name_table(i).ue_data_level :='FED';
1880 ltr_ue_name_table(i).data_type :='AMT';
1881 ltr_ue_name_table(i).negative_check := 'Y';
1882
1883 -- RO Record Archived Data Item
1884 i := i +1;
1885 ltr_ue_name_table(i).ue_name := 'A_W2_BOX_8_PER_GRE_YTD'; --AI 46
1886 ltr_ue_name_table(i).ue_data_level :='FED';
1887 ltr_ue_name_table(i).data_type :='AMT';
1888 ltr_ue_name_table(i).negative_check := 'Y';
1889
1890 i := i +1;
1891 ltr_ue_name_table(i).ue_name := 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD'; --AI 47
1892 ltr_ue_name_table(i).ue_data_level :='FED';
1893 ltr_ue_name_table(i).data_type :='AMT';
1894 ltr_ue_name_table(i).negative_check := 'Y';
1895
1896 i := i +1;
1897 ltr_ue_name_table(i).ue_name := 'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD'; --AI 48
1898 ltr_ue_name_table(i).ue_data_level :='FED';
1899 ltr_ue_name_table(i).data_type :='AMT';
1900 ltr_ue_name_table(i).negative_check := 'Y';
1901
1902 i := i +1;
1903 ltr_ue_name_table(i).ue_name := 'A_W2_MSA_PER_GRE_YTD'; --AI 49
1904 ltr_ue_name_table(i).ue_data_level :='FED';
1905 ltr_ue_name_table(i).data_type :='AMT';
1906 ltr_ue_name_table(i).negative_check := 'Y';
1907
1908 i := i +1;
1909 ltr_ue_name_table(i).ue_name := 'A_W2_408P_PER_GRE_YTD'; --AI 50
1910 ltr_ue_name_table(i).ue_data_level :='FED';
1911 ltr_ue_name_table(i).data_type :='AMT';
1912 ltr_ue_name_table(i).negative_check := 'Y';
1913
1914 i := i +1;
1915 ltr_ue_name_table(i).ue_name := 'A_W2_ADOPTION_PER_GRE_YTD'; --AI 51
1916 ltr_ue_name_table(i).ue_data_level :='FED';
1917 ltr_ue_name_table(i).data_type :='AMT';
1918 ltr_ue_name_table(i).negative_check := 'Y';
1919
1920 i := i +1;
1921 ltr_ue_name_table(i).ue_name := 'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD'; --AI 52
1922 ltr_ue_name_table(i).ue_data_level :='FED';
1923 ltr_ue_name_table(i).data_type :='AMT';
1924 ltr_ue_name_table(i).negative_check := 'Y';
1925
1926 i := i +1;
1927 ltr_ue_name_table(i).ue_name := 'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD'; --AI 53
1928 ltr_ue_name_table(i).ue_data_level :='FED';
1929 ltr_ue_name_table(i).data_type :='AMT';
1930 ltr_ue_name_table(i).negative_check := 'Y';
1931
1932 i := i +1;
1933 ltr_ue_name_table(i).ue_name := 'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD'; --AI 54
1934 ltr_ue_name_table(i).ue_data_level :='FED';
1935 ltr_ue_name_table(i).data_type :='AMT';
1936 ltr_ue_name_table(i).negative_check := 'Y';
1937
1938 --
1939 -- Following User Entities are used for RS record
1940 --
1941
1942 i := i +1;
1943 ltr_ue_name_table(i).ue_name := 'A_EMP_PER_HIRE_DATE'; --AI 55
1944 ltr_ue_name_table(i).ue_data_level := 'PER';
1945
1946 i := i +1;
1947 ltr_ue_name_table(i).ue_name := 'A_EMP_PER_SEPARATION_DATE'; --AI 56
1948 ltr_ue_name_table(i).ue_data_level := 'PER';
1949
1950 i := i +1;
1951 ltr_ue_name_table(i).ue_name := 'A_STATE_ASG_FILING_STATUS_CODE'; --AI 57
1952 ltr_ue_name_table(i).ue_data_level := 'PER';
1953
1954 i := i +1;
1955 ltr_ue_name_table(i).ue_name := 'A_SCL_ASG_US_WORK_SCHEDULE'; --AI 58
1956 ltr_ue_name_table(i).ue_data_level := 'PER';
1957
1958 i := i +1;
1959 ltr_ue_name_table(i).ue_name := 'A_ASG_HOURS'; --AI 59
1960 ltr_ue_name_table(i).ue_data_level := 'PER';
1961
1962 i := i +1;
1963 ltr_ue_name_table(i).ue_name := 'A_ASG_FREQ'; --AI 60
1964 ltr_ue_name_table(i).ue_data_level := 'PER';
1965
1966 i := i +1;
1967 ltr_ue_name_table(i).ue_name := 'A_SCL_ASG_US_NJ_PLAN_ID'; --AI 61
1968 ltr_ue_name_table(i).ue_data_level := 'PER';
1969
1970 i := i +1;
1971 ltr_ue_name_table(i).ue_name := 'A_STATE_ASG_WITHHOLDING_ALLOWANCES'; --AI 62
1972 ltr_ue_name_table(i).ue_data_level := 'PER';
1973
1974 i := i +1;
1975 ltr_ue_name_table(i).ue_name := 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD'; --AI 63
1976 ltr_ue_name_table(i).ue_data_level := 'STATE';
1977 ltr_ue_name_table(i).data_type :='AMT';
1978 ltr_ue_name_table(i).negative_check := 'Y';
1979
1980 i := i +1;
1981 ltr_ue_name_table(i).ue_name := 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD'; --AI 64
1982 ltr_ue_name_table(i).ue_data_level := 'STATE';
1983 ltr_ue_name_table(i).data_type :='AMT';
1984 ltr_ue_name_table(i).negative_check := 'Y';
1985
1986 i := i +1;
1987 ltr_ue_name_table(i).ue_name := 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD'; --AI 65
1988 ltr_ue_name_table(i).ue_data_level := 'STATE';
1989 ltr_ue_name_table(i).data_type :='AMT';
1990 ltr_ue_name_table(i).negative_check := 'Y';
1991
1992 i := i +1;
1993 ltr_ue_name_table(i).ue_name := 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD'; --AI 66
1994 ltr_ue_name_table(i).ue_data_level := 'STATE';
1995 ltr_ue_name_table(i).data_type :='AMT';
1996 ltr_ue_name_table(i).negative_check := 'Y';
1997
1998 i := i +1;
1999 ltr_ue_name_table(i).ue_name := 'A_W2_STATE_WAGES'; --AI 67
2000 ltr_ue_name_table(i).ue_data_level := 'STATE';
2001 ltr_ue_name_table(i).data_type :='AMT';
2002 ltr_ue_name_table(i).negative_check := 'Y';
2003
2004 i := i +1;
2005 ltr_ue_name_table(i).ue_name := 'A_SIT_WITHHELD_PER_JD_GRE_YTD'; --AI 68
2006 ltr_ue_name_table(i).ue_data_level := 'STATE';
2007 ltr_ue_name_table(i).data_type :='AMT';
2008 ltr_ue_name_table(i).negative_check := 'Y';
2009
2010 i := i +1;
2011 ltr_ue_name_table(i).ue_name := 'A_STEIC_ADVANCE_PER_JD_GRE_YTD'; --AI 69
2012 ltr_ue_name_table(i).ue_data_level := 'STATE';
2013 ltr_ue_name_table(i).data_type :='AMT';
2014 ltr_ue_name_table(i).negative_check := 'Y';
2015
2016 i := i +1;
2017 ltr_ue_name_table(i).ue_name := 'A_SUI_EE_WITHHELD_PER_JD_GRE_YTD'; --AI 70
2018 ltr_ue_name_table(i).ue_data_level := 'STATE';
2019 ltr_ue_name_table(i).data_type :='AMT';
2020 ltr_ue_name_table(i).negative_check := 'Y';
2021
2022 i := i +1;
2023 ltr_ue_name_table(i).ue_name := 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD'; --AI 71
2024 ltr_ue_name_table(i).ue_data_level := 'STATE';
2025 ltr_ue_name_table(i).data_type :='AMT';
2026 ltr_ue_name_table(i).negative_check := 'Y';
2027
2028 --
2029 -- Fetch GRE level archived data to be used in State level Record
2030 --
2031 i := i +1;
2032 ltr_ue_name_table(i).ue_name := 'A_FIPS_CODE_JD'; --AI 72
2033 ltr_ue_name_table(i).ue_data_level := 'GRE';
2034
2035 i := i +1;
2036 ltr_ue_name_table(i).ue_name := 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID'; --AI 73
2037 ltr_ue_name_table(i).ue_data_level := 'GRE';
2038
2039 i := i +1;
2040 ltr_ue_name_table(i).ue_name := 'A_STATE_TAX_RULES_ORG_SUI_COMPANY_STATE_ID'; --AI 74
2041 ltr_ue_name_table(i).ue_data_level := 'GRE';
2042
2043 --
2044 -- End of setting User Entities to PL/Table
2045 --
2046
2047 HR_UTILITY_TRACE('DBI Count '||to_char(ltr_ue_name_table.count));
2048 HR_UTILITY_TRACE('YE Archive Assignment Action Id '||
2049 to_char(p_ye_assignment_action_id));
2050
2051 IF ltr_ue_name_table.count > 0 then
2052 FOR j IN ltr_ue_name_table.first .. ltr_ue_name_table.last
2053 LOOP
2054 IF ltr_ue_name_table(j).ue_data_level = 'PER'
2055 THEN
2056 ltr_ue_name_table(j).ue_value :=
2057 hr_us_w2_rep.get_per_item( p_ye_assignment_action_id,
2058 ltr_ue_name_table(j).ue_name);
2059 ELSIF ltr_ue_name_table(J).ue_data_level ='FED' THEN
2060 ltr_ue_name_table(j).ue_value := hr_us_w2_rep.get_w2_arch_bal(
2061 p_ye_assignment_action_id
2062 ,ltr_ue_name_table(j).ue_name
2063 ,p_tax_unit_id
2064 ,'00-000-0000'
2065 , 0);
2066 IF ( ltr_ue_name_table(j).data_type = 'AMT' AND
2067 to_number(ltr_ue_name_table(j).ue_value) < 0)
2068 THEN
2069 l_status := 'FAILED';
2070 END IF;
2071 ELSIF ltr_ue_name_table(J).ue_data_level ='STATE' THEN
2072 ltr_ue_name_table(j).ue_value :=
2073 hr_us_w2_rep.get_w2_arch_bal(
2074 p_ye_assignment_action_id
2075 ,ltr_ue_name_table(j).ue_name
2076 ,p_tax_unit_id
2077 ,p_state_code||'-000-0000'
2078 , 2);
2079 IF ( ltr_ue_name_table(j).data_type = 'AMT' AND
2080 to_number(ltr_ue_name_table(j).ue_value) < 0)
2081 THEN
2082 l_status := 'FAILED';
2083 END IF;
2084 ELSIF ltr_ue_name_table(J).ue_data_level ='GRE' THEN
2085 ltr_ue_name_table(j).ue_value :=
2086 hr_us_w2_rep.get_state_item(
2087 p_tax_unit_id,
2088 p_state_code||'-000-0000',
2089 p_payroll_action_id,
2090 ltr_ue_name_table(j).ue_name);
2091 ELSE
2092 ltr_ue_name_table(j).ue_value := ' ';
2093 END IF;
2094 HR_UTILITY_TRACE('DBI ' || ltr_ue_name_table(J).ue_data_level ||
2095 ' -('||to_char(j)|| ') : < '||ltr_ue_name_table(j).ue_name||
2096 ' > VALUE : < '|| ltr_ue_name_table(j).ue_value||' >');
2097 END LOOP;
2098 HR_UTILITY_TRACE('Status of Employee Record ' || l_status);
2099 END IF;
2100
2101 /* ==================================================
2102 Get Employer or transmitter Company Information releated Submitter
2103 ================================================== */
2104 employee_data := pay_us_reporting_utils_pkg.get_item_data(
2105 p_assignment_id,
2106 l_date_earned,
2107 l_tax_unit_id,
2108 l_reporting_date,
2109 input_empe_name,
2110 l_input_report_type,
2111 l_input_report_type_format,
2112 l_input_report_qualifier,
2113 l_input_record_name,
2114 input_empe_1,
2115 input_empe_2,
2116 input_empe_3,
2117 input_empe_4,
2118 input_empe_5,
2119 input_empe_validate_flag,
2120 empe_exclude_output_flag,
2121 empe_out_1,
2122 empe_out_2,
2123 empe_out_3,
2124 empe_out_4,
2125 empe_out_5,
2126 empe_out_6,
2127 empe_out_7,
2128 empe_out_8,
2129 empe_out_9,
2130 empe_out_10);
2131
2132 --
2133 -- Pouplate All the Tags to be used for RE record Data Items
2134 --
2135 pay_us_w2_generic_extract.populate_ee_data_tag;
2136
2137 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
2138 FROM dual;
2139
2140 /* Initialize with Default Value */
2141
2142 FOR I IN ltr_ee_data_tag.first .. ltr_ee_data_tag.last
2143 LOOP
2144 g_ee_record(I).employee_data := ' ';
2145 g_ee_record(I).employee_tag := ltr_ee_data_tag(I);
2146 END LOOP;
2147
2148 /* Deriving all data Items that are used for RW record */
2149
2150 /* Record Identifier */
2151 g_ee_record(RW_RECORD_IDENTIFIER).employee_data := 'RW';
2152 HR_UTILITY_TRACE(g_ee_record(1).employee_tag || ' : '
2153 || g_ee_record(RW_RECORD_IDENTIFIER).employee_data);
2154 /* Employee, Social Security Number (SSN) */
2155 g_ee_record(EE_SSN).employee_data :=
2156 ltr_ue_name_table(1).ue_value;
2157 HR_UTILITY_TRACE(g_ee_record(EE_SSN).employee_tag || ' : '
2158 || g_ee_record(EE_SSN).employee_data);
2159
2160 /* Employee, First Name */
2161 g_ee_record(EE_FIRST_NAME).employee_data :=
2162 ltr_ue_name_table(2).ue_value;
2163 HR_UTILITY_TRACE(g_ee_record(EE_FIRST_NAME).employee_tag || ' : '
2164 || g_ee_record(EE_FIRST_NAME).employee_data);
2165
2166 /* Employee, Middle Name or Initial */
2167 g_ee_record(EE_MIDDLE_INITIAL).employee_data :=
2168 ltr_ue_name_table(3).ue_value;
2169 HR_UTILITY_TRACE(g_ee_record(EE_MIDDLE_INITIAL).employee_tag || ' : '
2170 || g_ee_record(EE_MIDDLE_INITIAL).employee_data);
2171
2172 /* Employee, Last Name */
2173 g_ee_record(EE_LAST_NAME).employee_data :=
2174 ltr_ue_name_table(4).ue_value;
2175 HR_UTILITY_TRACE(g_ee_record(EE_LAST_NAME).employee_tag || ' : '
2176 || g_ee_record(EE_LAST_NAME).employee_data);
2177
2178 /* Employee, Name Suffix */
2179 g_ee_record(EE_SUFFIX).employee_data :=
2180 ltr_ue_name_table(5).ue_value;
2181 HR_UTILITY_TRACE(g_ee_record(EE_SUFFIX).employee_tag || ' : '
2182 || g_ee_record(EE_SUFFIX).employee_data);
2183
2184 /* Employee, Location Address */
2185 g_ee_record(EE_LOCATION_ADDRESS).employee_data := empe_out_1;
2186 HR_UTILITY_TRACE(g_ee_record(EE_LOCATION_ADDRESS).employee_tag || ' : '
2187 || g_ee_record(EE_LOCATION_ADDRESS).employee_data);
2188
2189 /* Pos: 88 Len: 22 Desc: Employee, Delivery Address */
2190 g_ee_record(EE_DELIVERY_ADDRESS).employee_data := empe_out_2;
2191 HR_UTILITY_TRACE(g_ee_record(EE_DELIVERY_ADDRESS).employee_tag || ' : '
2192 || g_ee_record(EE_DELIVERY_ADDRESS).employee_data);
2193
2194 /* Employee, City */
2195 g_ee_record(EE_CITY).employee_data := empe_out_3;
2196 HR_UTILITY_TRACE(g_ee_record(EE_CITY).employee_tag || ' : '
2197 || g_ee_record(EE_CITY).employee_data);
2198
2199 /* Employee, State Abbreviation */
2200 g_ee_record(EE_STATE_ABBREVIATION).employee_data := empe_out_4;
2201 HR_UTILITY_TRACE(g_ee_record(EE_STATE_ABBREVIATION).employee_tag || ' : '
2202 || g_ee_record(EE_STATE_ABBREVIATION).employee_data);
2203
2204 /* Employee, Zip Code */
2205 g_ee_record(EE_ZIP_CODE).employee_data := empe_out_5;
2206 HR_UTILITY_TRACE(g_ee_record(EE_ZIP_CODE).employee_tag || ' : '
2207 || g_ee_record(EE_ZIP_CODE).employee_data);
2208
2209 /* Employee, Zip Code Extension */
2210 g_ee_record(EE_ZIP_CODE_EXTENSION).employee_data :=
2211 empe_out_6;
2212 HR_UTILITY_TRACE(g_ee_record(EE_ZIP_CODE_EXTENSION).employee_tag || ' : '
2213 || g_ee_record(EE_ZIP_CODE_EXTENSION).employee_data);
2214
2215 /* Employee, Foreign State - Province */
2216 g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_data
2217 := empe_out_7;
2218 HR_UTILITY_TRACE(g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_tag || ' : '
2219 || g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_data);
2220
2221 /* Employee, Foreign Postal Code */
2222 g_ee_record(EE_FOREIGN_POSTAL_CODE).employee_data
2223 := empe_out_8;
2224 HR_UTILITY_TRACE(g_ee_record(EE_FOREIGN_POSTAL_CODE).employee_tag || ' : '
2225 || g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_data);
2226
2227 /* Pos: 186 Len: 2 Desc: Employee, Country Code */
2228 g_ee_record(EE_COUNTRY_CODE).employee_data
2229 := empe_out_9;
2230 HR_UTILITY_TRACE(g_ee_record(EE_COUNTRY_CODE).employee_tag || ' : '
2231 || g_ee_record(EE_COUNTRY_CODE).employee_data);
2232
2233 /* Pos: 188 Len: 11 Desc: Wages, Tips and Other Compensation */
2234 g_ee_record(FIT_GROSS_WAGES).employee_data :=
2235 to_char(to_number(ltr_ue_name_table(29).ue_value) +
2236 to_number(ltr_ue_name_table(37).ue_value) +
2237 to_number(ltr_ue_name_table(30).ue_value) -
2238 to_number(ltr_ue_name_table(43).ue_value) +
2239 to_number(ltr_ue_name_table(44).ue_value) +
2240 to_number(ltr_ue_name_table(45).ue_value));
2241
2242 HR_UTILITY_TRACE(g_ee_record(FIT_GROSS_WAGES).employee_tag || ' : '
2243 || g_ee_record(FIT_GROSS_WAGES).employee_data);
2244
2245 /*
2246 FIT_GROSS_WAGES = A_REGULAR_EARNINGS_PER_GRE_YTD +
2247 A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD +
2248 A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD -
2249 A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD +
2250 A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD +
2251 A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD
2252 */
2253
2254 /* Federal Income Tax Withheld */
2255 g_ee_record(FIT_WITHHELD).employee_data := ltr_ue_name_table(7).ue_value;
2256 HR_UTILITY_TRACE(g_ee_record(FIT_WITHHELD).employee_tag || ' : '
2257 || g_ee_record(FIT_WITHHELD).employee_data);
2258
2259 /* Pos: 210 Len: 11 Desc: Social Security Wages
2260 SS_WAGES := A_SS_EE_TAXABLE_PER_GRE_YTD
2261 - A_W2_BOX_7_PER_GRE_YTD
2262 */
2263 g_ee_record(SS_WAGES).employee_data := to_char(to_number(ltr_ue_name_table(8).ue_value)
2264 - to_number(ltr_ue_name_table(12).ue_value));
2265 HR_UTILITY_TRACE(g_ee_record(SS_WAGES).employee_tag || ' : '
2266 || g_ee_record(SS_WAGES).employee_data);
2267
2268 /* Social Security Tax Withheld */
2269 g_ee_record(SS_TAX_WITHHELD).employee_data := ltr_ue_name_table(9).ue_value;
2270
2271 /* Medicare Wages and Tips */
2272 g_ee_record(MEDICARE_WAGES_TIPS).employee_data := ltr_ue_name_table(10).ue_value;
2273
2274 /* Medicare Tax Withheld */
2275 g_ee_record(MEDICARE_TAX_WITHHELD).employee_data := ltr_ue_name_table(11).ue_value;
2276
2277 /* Social Security Tips */
2278 g_ee_record(SS_TIPS).employee_data := ltr_ue_name_table(12).ue_value;
2279
2280 /* Advance Earned Income Credit */
2281 g_ee_record(EIC_ADVANCE).employee_data := ltr_ue_name_table(13).ue_value;
2282
2283 /* Dependent Care Benefits */
2284 g_ee_record(W2_DEPENDENT_CARE).employee_data := ltr_ue_name_table(14).ue_value;
2285 HR_UTILITY_TRACE(g_ee_record(W2_DEPENDENT_CARE).employee_tag || ' : '
2286 || g_ee_record(W2_DEPENDENT_CARE).employee_data);
2287
2288 /* Deferred Compensation Contributions to Section 401(k) */
2289 g_ee_record(W2_401K).employee_data := ltr_ue_name_table(15).ue_value;
2290
2291 /* Deferred Compensation Contributions to Section 403(b) */
2292 g_ee_record(W2_403B).employee_data := ltr_ue_name_table(16).ue_value;
2293
2294 /* Deferred Compensation Contributions to Section 408(k)(6) */
2295 g_ee_record(W2_408K).employee_data := ltr_ue_name_table(17).ue_value;
2296
2297 /* Deferred Compensation Contributions to Section 457(b) */
2298 g_ee_record(W2_457).employee_data := ltr_ue_name_table(18).ue_value;
2299 HR_UTILITY_TRACE(g_ee_record(W2_457).employee_tag || ' : '
2300 || g_ee_record(W2_457).employee_data);
2301
2302 /* Deferred Compensation Contributions to Section 501(c)(18)(D) */
2303 g_ee_record(W2_501C).employee_data := ltr_ue_name_table(19).ue_value;
2304
2305 /* Military Employees Basic Ouarters, Subsistence and Combat Pay
2306 Not Used
2307 */
2308 g_ee_record(W2_MILITARY_HOUSING).employee_data := '0';
2309
2310 /* Non-qualified Plan Section 457 Distributions or Contributions */
2311 g_ee_record(W2_NONQUAL_457).employee_data := ltr_ue_name_table(21).ue_value;
2312
2313 /* ER Contibutions to Health Savings Acct
2314 /* Bug 3680056 - New field
2315 Added function call to fetch the DBI value for the ER contrib to HSA
2316 input_rwrc_38 = to_char(trunc(get_ff_archive_value('A_W2_HSA_PER_GRE_YTD') * 100))*/
2317 g_ee_record(W2_HSA).employee_data := '0';
2318
2319 /* Non-qualified Plan Not Section 457 Distributions or Contributions */
2320 /* Non Qual Plan not 457 is Nonqual Plan - Nonqaul 457
2321 (A_W2_NONQUAL_PLAN_PER_GRE_YTD -
2322 A_W2_NONQUAL_457_PER_GRE_YTD )
2323 */
2324 g_ee_record(NON_QUAL_NOT_457).employee_data :=
2325 to_char(to_number(ltr_ue_name_table(20).ue_value) -
2326 to_number(ltr_ue_name_table(21).ue_value));
2327 HR_UTILITY_TRACE(g_ee_record(NON_QUAL_NOT_457).employee_tag || ' : '
2328 || g_ee_record(NON_QUAL_NOT_457).employee_data);
2329
2330 /* Non-Taxable Combat Pay (Not for Puerto Rico) */
2331 g_ee_record(W2_NONTAX_COMBAT).employee_data := ltr_ue_name_table(39).ue_value;
2332
2333 /* Employer Cost of Premiums for Group Term Life Insruance over $50,000 */
2334 g_ee_record(W2_GROUP_TERM_LIFE).employee_data := ltr_ue_name_table(22).ue_value;
2335
2336 /* Income from the Exercise of Nonstatutory Stock Option - Optional for 2001 */
2337 g_ee_record(W2_NONQUAL_STOCK).employee_data := ltr_ue_name_table(23).ue_value;
2338
2339 /* Deferrals Under a Section 409A Non-Qualified Deferred Comp Plan (Not for PR) */
2340 g_ee_record(W2_NONQUAL_DEF_COMP).employee_data := ltr_ue_name_table(40).ue_value;
2341
2342 /* Designated Roth Contributions to a section 401(k) Plan */
2343 g_ee_record(W2_ROTH_401K).employee_data := ltr_ue_name_table(41).ue_value;
2344 HR_UTILITY_TRACE(g_ee_record(W2_ROTH_401K).employee_tag || ' : '
2345 || g_ee_record(W2_ROTH_401K).employee_data);
2346
2347 /* Designated Roth Contributions Und sec 403(b) Plan */
2348 g_ee_record(W2_ROTH_403B).employee_data := ltr_ue_name_table(42).ue_value;
2349 HR_UTILITY_TRACE(g_ee_record(W2_ROTH_403B).employee_tag || ' : '
2350 || g_ee_record(W2_ROTH_403B).employee_data);
2351
2352 /* Statutory Employee Indicator */
2353 IF ltr_ue_name_table(24).ue_value = 'Y' THEN
2354 g_ee_record(W2_ASG_STATUTORY_EMPLOYEE).employee_data := '1';
2355 ELSE
2356 g_ee_record(W2_ASG_STATUTORY_EMPLOYEE).employee_data := '0';
2357 END IF;
2358 HR_UTILITY_TRACE(g_ee_record(W2_ASG_STATUTORY_EMPLOYEE).employee_tag || ' : '
2359 || g_ee_record(W2_ASG_STATUTORY_EMPLOYEE).employee_data);
2360
2361 /* Retirement Plan Indicator */
2362 IF (( to_number(ltr_ue_name_table(25).ue_value) > 0) OR
2363 ( to_number(ltr_ue_name_table(15).ue_value) > 0) OR
2364 ( to_number(ltr_ue_name_table(16).ue_value) > 0) OR
2365 ( to_number(ltr_ue_name_table(17).ue_value) > 0) OR
2366 ( to_number(ltr_ue_name_table(19).ue_value) > 0))
2367 THEN
2368 g_ee_record(RETIREMENT_PLAN_INDICATOR).employee_data := '1';
2369 ELSE
2370 g_ee_record(RETIREMENT_PLAN_INDICATOR).employee_data := '0';
2371 END IF;
2372 HR_UTILITY_TRACE(g_ee_record(RETIREMENT_PLAN_INDICATOR).employee_tag || ' : '
2373 || g_ee_record(RETIREMENT_PLAN_INDICATOR).employee_data);
2374
2375 /* Third-Party Sick Pay Indicator */
2376 IF to_number(ltr_ue_name_table(38).ue_value) > 0
2377 THEN
2378 g_ee_record(W2_TP_SICK_PAY_IND).employee_data := '1';
2379 ELSE
2380 g_ee_record(W2_TP_SICK_PAY_IND).employee_data := '0';
2381 END IF;
2382 HR_UTILITY_TRACE(g_ee_record(W2_TP_SICK_PAY_IND).employee_tag || ' : '
2383 || g_ee_record(W2_TP_SICK_PAY_IND).employee_data);
2384
2385 /* Income Tax Withheld by Third Party Payer
2386 This balance is not reported in RW record but summ total is reported at the GRE level in RT record
2387 */
2388 g_ee_record(FIT_WITHHELD_THIRD_PARTY).employee_data := ltr_ue_name_table(27).ue_value;
2389 HR_UTILITY_TRACE(g_ee_record(FIT_WITHHELD_THIRD_PARTY).employee_tag || ' : '
2390 || g_ee_record(FIT_WITHHELD_THIRD_PARTY).employee_data);
2391
2392 /* RO Record Data Items fetched to build XML components */
2393 out_ret_contrib_perjdgreytd :=
2394 pay_us_reporting_utils_pkg.Get_Territory_Values(
2395 p_ye_assignment_action_id,
2396 p_tax_unit_id,
2397 l_reporting_date,
2398 l_input_report_type,
2399 l_input_report_type_format,
2400 'FED',
2401 'RO',
2402 empe_out_10, -- Employee Number
2403 ro_input_2,
2404 ro_input_3,
2405 ro_input_4,
2406 ro_input_5,
2407 ro_validate,
2408 ro_exlude_from_out,
2409 out_terr_taxabable_allow_per,
2410 out_terr_taxabale_com_per,
2411 out_terr_taxabale_tips_per,
2412 out_sit_with_per,
2413 out_w2_state_wages,
2414 ro_out_6,
2415 ro_out_7,
2416 ro_out_8,
2417 ro_out_9,
2418 ro_out_10);
2419
2420 nout_ret_contrib_perjdgreytd := to_number(out_ret_contrib_perjdgreytd) ;
2421 nout_terr_taxabable_allow_per := to_number(out_terr_taxabable_allow_per);
2422 nout_terr_taxabale_com_per := to_number(out_terr_taxabale_com_per);
2423 nout_terr_taxabale_tips_per := to_number(out_terr_taxabale_tips_per);
2424 nout_sit_with_per := to_number(out_sit_with_per);
2425 nout_total_w2_state_wages := to_number(out_w2_state_wages);
2426 nout_wages_subject := ( nout_total_w2_state_wages -
2427 nout_terr_taxabale_tips_per -
2428 nout_terr_taxabable_allow_per -
2429 nout_terr_taxabale_com_per );
2430
2431 /* RO record balance is = 0 */
2432 IF to_number(ltr_ue_name_table(46).ue_value) = 0 --A_W2_BOX_8_PER_GRE_YTD
2433 AND to_number(ltr_ue_name_table(47).ue_value) = 0 --A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD
2434 AND to_number(ltr_ue_name_table(48).ue_value) = 0 --A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD
2435 AND to_number(ltr_ue_name_table(49).ue_value) = 0 --A_W2_MSA_PER_GRE_YTD
2436 AND to_number(ltr_ue_name_table(50).ue_value) = 0 --A_W2_408P_PER_GRE_YTD
2437 AND to_number(ltr_ue_name_table(51).ue_value) = 0 --A_W2_ADOPTION_PER_GRE_YTD
2438 AND to_number(ltr_ue_name_table(52).ue_value) = 0 --A_W2_UNCOLL_SS_GTL_PER_GRE_YTD
2439 AND to_number(ltr_ue_name_table(53).ue_value) = 0 --A_W2_UNCOLL_MED_GTL_PER_GRE_YTD
2440 AND to_number(ltr_ue_name_table(54).ue_value) = 0 --A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD = 0 /* Bug 4737567 */
2441 AND nout_wages_subject = 0
2442 AND nout_total_w2_state_wages = 0
2443 AND nout_terr_taxabale_tips_per = 0
2444 AND nout_ret_contrib_perjdgreytd = 0
2445 AND nout_terr_taxabable_allow_per = 0
2446 AND nout_sit_with_per = 0
2447 THEN
2448 l_zero_ro_record := 'Y';
2449 Else
2450 l_zero_ro_record := 'N';
2451 END IF;
2452
2453 /* RO Record Identifier */
2454 g_ee_record(RO_RECORD_IDENTIFIER).employee_data := 'RO';
2455 HR_UTILITY_TRACE(g_ee_record(RO_RECORD_IDENTIFIER).employee_tag || ' : '
2456 || g_ee_record(RO_RECORD_IDENTIFIER).employee_data);
2457
2458 /* Allocated Tips */
2459 g_ee_record(RO_W2_BOX_8).employee_data := ltr_ue_name_table(46).ue_value;
2460 HR_UTILITY_TRACE(g_ee_record(RO_W2_BOX_8).employee_tag || ' : '
2461 || g_ee_record(RO_W2_BOX_8).employee_data);
2462
2463 /* Uncollected Employee Tax on Tips */
2464 g_ee_record(RO_UNCOLLECT_TAX_ON_TIPS).employee_data :=
2465 to_char(to_number(ltr_ue_name_table(47).ue_value) +
2466 to_number(ltr_ue_name_table(48).ue_value));
2467 HR_UTILITY_TRACE(g_ee_record(RO_UNCOLLECT_TAX_ON_TIPS).employee_tag || ' : '
2468 || g_ee_record(RO_UNCOLLECT_TAX_ON_TIPS).employee_data);
2469
2470 /****************************************************************************/
2471 /* Locations 34 to 66 Do not apply to Puerto Rico, Virgin Islands, American */
2472 /* Samoa, Guam, or Northern Mariana Islands, Employees. */
2473 /****************************************************************************/
2474 /* Medical Savings Account */
2475 g_ee_record(RO_W2_MSA).employee_data :=
2476 ltr_ue_name_table(49).ue_value;
2477 HR_UTILITY_TRACE(g_ee_record(RO_W2_MSA).employee_tag || ' : '
2478 || g_ee_record(RO_W2_MSA).employee_data);
2479
2480 /* Simple Retirement Account */
2481 g_ee_record(RO_W2_408P).employee_data :=
2482 ltr_ue_name_table(50).ue_value;
2483 HR_UTILITY_TRACE(g_ee_record(RO_W2_408P).employee_tag || ' : '
2484 || g_ee_record(RO_W2_408P).employee_data);
2485
2486 /* Qualified Adoption Expenses */
2487 g_ee_record(RO_W2_ADOPTION).employee_data :=
2488 ltr_ue_name_table(51).ue_value;
2489 HR_UTILITY_TRACE(g_ee_record(RO_W2_ADOPTION).employee_tag || ' : '
2490 || g_ee_record(RO_W2_ADOPTION).employee_data);
2491 /****************************************************************************/
2492 /* Uncollected Social Security or RPTA Tax on Cost
2493 of Group Term Life Insurance Over $50,000 */
2494 g_ee_record(RO_W2_UNCOLL_SS_GTL).employee_data :=
2495 ltr_ue_name_table(52).ue_value;
2496 HR_UTILITY_TRACE(g_ee_record(RO_W2_UNCOLL_SS_GTL).employee_tag || ' : '
2497 || g_ee_record(RO_W2_UNCOLL_SS_GTL).employee_data);
2498 /* Uncollected Medicare Tax on Cost of Group Term Life Insurance Over $50,000 */
2499 g_ee_record(RO_W2_UNCOLL_MED_GTL).employee_data :=
2500 ltr_ue_name_table(53).ue_value;
2501 HR_UTILITY_TRACE(g_ee_record(RO_W2_UNCOLL_MED_GTL).employee_tag || ' : '
2502 || g_ee_record(RO_W2_UNCOLL_MED_GTL).employee_data);
2503 /* Income Under Sec 409A on Non-Qual Def Comp Plan */
2504 g_ee_record(RO_W2_409A_NONQUAL_INCOM).employee_data :=
2505 ltr_ue_name_table(54).ue_value;
2506 HR_UTILITY_TRACE(g_ee_record(RO_W2_409A_NONQUAL_INCOM).employee_tag || ' : '
2507 || g_ee_record(RO_W2_409A_NONQUAL_INCOM).employee_data);
2508
2509 /****************************************************************************/
2510 /* Locations 265 to 362 are for Puerto Rico Employees ONLY. */
2511 /****************************************************************************/
2512 /* Civil Status */
2513
2514 l_tax_tax_jurisdiction :=
2515 hr_us_w2_rep.get_w2_tax_unit_item (p_tax_unit_id,
2516 p_payroll_action_id,
2517 'A_LC_W2_REPORTING_RULES_ORG_TAX_JURISDICTION');
2518 HR_UTILITY_TRACE('Tax Jurisdiction Code '||l_tax_tax_jurisdiction);
2519
2520 IF l_tax_tax_jurisdiction = 'P' THEN
2521 g_ee_record(RO_CIVIL_STATUS).employee_data := ro_out_6;
2522 ELSE
2523 g_ee_record(RO_CIVIL_STATUS).employee_data := ' ';
2524 END IF;
2525 HR_UTILITY_TRACE(g_ee_record(RO_CIVIL_STATUS).employee_tag || ' : '
2526 || g_ee_record(RO_CIVIL_STATUS).employee_data);
2527
2528 /* Spouse Social Security Number (SSN) */
2529 g_ee_record(RO_SPOUSE_SSN).employee_data := ro_out_7;
2530 HR_UTILITY_TRACE(g_ee_record(RO_SPOUSE_SSN).employee_tag || ' : '
2531 || g_ee_record(RO_SPOUSE_SSN).employee_data);
2532
2533 /* Wages Subject to Puerto Rico Tax */
2534 g_ee_record(RO_WAGES_SUBJ_PR_TAX).employee_data := to_char(nout_wages_subject);
2535 HR_UTILITY_TRACE(g_ee_record(RO_WAGES_SUBJ_PR_TAX).employee_tag || ' : '
2536 || g_ee_record(RO_WAGES_SUBJ_PR_TAX).employee_data);
2537
2538 /* Commissions Subject to Puerto Rico Tax */
2539 g_ee_record(RO_COMM_SUBJ_PR_TAX).employee_data := to_char(nout_terr_taxabale_com_per);
2540 HR_UTILITY_TRACE(g_ee_record(RO_COMM_SUBJ_PR_TAX).employee_tag || ' : '
2541 || g_ee_record(RO_COMM_SUBJ_PR_TAX).employee_data);
2542
2543 /* Allowances Subject to Puerto Rico Tax */
2544 g_ee_record(RO_ALLOWANCE_SUBJ_PR_TAX).employee_data := to_char(nout_terr_taxabable_allow_per);
2545 HR_UTILITY_TRACE(g_ee_record(RO_ALLOWANCE_SUBJ_PR_TAX).employee_tag || ' : '
2546 || g_ee_record(RO_ALLOWANCE_SUBJ_PR_TAX).employee_data);
2547
2548 /* Tips Subject to Puerto Rico Tax */
2549 g_ee_record(RO_TIPS_SUBJ_PR_TAX).employee_data := to_char(nout_terr_taxabale_tips_per);
2550 HR_UTILITY_TRACE(g_ee_record(RO_TIPS_SUBJ_PR_TAX).employee_tag || ' : '
2551 || g_ee_record(RO_TIPS_SUBJ_PR_TAX).employee_data);
2552
2553 /* Total Wages, Commissions, Tips, and Allowances Subject to Puerto Rico Tax */
2554 g_ee_record(RO_W2_STATE_WAGES).employee_data := to_char(nout_total_w2_state_wages);
2555 HR_UTILITY_TRACE(g_ee_record(RO_W2_STATE_WAGES).employee_tag || ' : '
2556 || g_ee_record(RO_W2_STATE_WAGES).employee_data);
2557
2558 /* Puerto Rico Tax Withheld */
2559 g_ee_record(RO_PR_TAX_WITHHELD).employee_data := to_char(nout_sit_with_per);
2560 HR_UTILITY_TRACE(g_ee_record(RO_PR_TAX_WITHHELD).employee_tag || ' : '
2561 || g_ee_record(RO_PR_TAX_WITHHELD).employee_data);
2562
2563 /* Retirement Fund Annual Contributions */
2564 g_ee_record(RO_RETIREMENT_CONTRIB).employee_data := to_char(nout_ret_contrib_perjdgreytd);
2565 HR_UTILITY_TRACE(g_ee_record(RO_RETIREMENT_CONTRIB).employee_tag || ' : '
2566 || g_ee_record(RO_RETIREMENT_CONTRIB).employee_data);
2567
2568 /* Formating RS Record Data Items */
2569
2570 /* Retirement Fund Annual Contributions */
2571 g_ee_record(RS_TAXING_ENTITY_CODE).employee_data := ' ';
2572 HR_UTILITY_TRACE(g_ee_record(RS_TAXING_ENTITY_CODE).employee_tag || ' : '
2573 || g_ee_record(RS_TAXING_ENTITY_CODE).employee_data);
2574
2575 /* Optional Code */
2576 g_ee_record(RS_OPTIONAL_CODE).employee_data := ' ';
2577 HR_UTILITY_TRACE(g_ee_record(RS_OPTIONAL_CODE).employee_tag || ' : '
2578 || g_ee_record(RS_OPTIONAL_CODE).employee_data);
2579
2580 /* Reporting Period */
2581 g_ee_record(RS_REPORTING_PERIOD).employee_data := l_reporting_period;
2582 HR_UTILITY_TRACE(g_ee_record(RS_REPORTING_PERIOD).employee_tag || ' : '
2583 || g_ee_record(RS_REPORTING_PERIOD).employee_data);
2584
2585 /* State Quarterly Unemployment Insurance Total Wages */
2586 g_ee_record(RS_SQWL_UNEMP_INS_WAGES).employee_data := '0';
2587 HR_UTILITY_TRACE(g_ee_record(RS_SQWL_UNEMP_INS_WAGES).employee_tag || ' : '
2588 || g_ee_record(RS_SQWL_UNEMP_INS_WAGES).employee_data);
2589
2590 /* State Quarterly Unemployment Total Taxable Wages */
2591 g_ee_record(RS_SQWL_UNEMP_TXBL_WAGES).employee_data := '0';
2592 HR_UTILITY_TRACE(g_ee_record(RS_SQWL_UNEMP_TXBL_WAGES).employee_tag || ' : '
2593 || g_ee_record(RS_SQWL_UNEMP_TXBL_WAGES).employee_data);
2594
2595 /* Number of Weeks Worked */
2596 g_ee_record(RS_WEEKS_WORKED).employee_data := ' ';
2597 HR_UTILITY_TRACE(g_ee_record(RS_WEEKS_WORKED).employee_tag || ' : '
2598 || g_ee_record(RS_WEEKS_WORKED).employee_data);
2599
2600 /* Date First Employed */
2601 g_ee_record(RS_DATE_FIRST_EMPLOYED).employee_data := ltr_ue_name_table(55).ue_value;
2602 HR_UTILITY_TRACE(g_ee_record(RS_DATE_FIRST_EMPLOYED).employee_tag || ' : '
2603 || g_ee_record(RS_DATE_FIRST_EMPLOYED).employee_data);
2604
2605 /* Date of Separation */
2606 g_ee_record(RS_DATE_OF_SEPARATION).employee_data := NVL(trim(ltr_ue_name_table(56).ue_value),' ');
2607 HR_UTILITY_TRACE(g_ee_record(RS_DATE_OF_SEPARATION).employee_tag || ' : '
2608 || g_ee_record(RS_DATE_OF_SEPARATION).employee_data);
2609
2610 /* State Employer Account Number */
2611 g_ee_record(RS_STATE_ER_ACCT_NUM).employee_data :=
2612 replace(replace(nvl(replace(ltr_ue_name_table(73).ue_value,' '), ' ') ,'-'),'/');
2613 HR_UTILITY_TRACE(g_ee_record(RS_STATE_ER_ACCT_NUM).employee_tag || ' : '
2614 || g_ee_record(RS_STATE_ER_ACCT_NUM).employee_data);
2615
2616 /* State Code */
2617
2618 g_ee_record(RS_STATE_CODE).employee_data := ltr_ue_name_table(72).ue_value;
2619 HR_UTILITY_TRACE(g_ee_record(RS_STATE_CODE).employee_tag || ' : '
2620 || g_ee_record(RS_STATE_CODE).employee_data);
2621
2622 /* State Taxable Wages
2623 A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD +
2624 A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD -
2625 A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD
2626 */
2627 l_state_wages := to_number(ltr_ue_name_table(63).ue_value) +
2628 to_number(ltr_ue_name_table(64).ue_value) -
2629 to_number(ltr_ue_name_table(65).ue_value);
2630
2631 g_ee_record(RS_STATE_WAGES).employee_data := to_char(l_state_wages);
2632 HR_UTILITY_TRACE(g_ee_record(RS_STATE_WAGES).employee_tag || ' : '
2633 || g_ee_record(RS_STATE_WAGES).employee_data);
2634
2635 /* SIT Withheld */
2636 g_ee_record(RS_SIT_WITHHELD).employee_data := ltr_ue_name_table(68).ue_value;
2637 HR_UTILITY_TRACE(g_ee_record(RS_SIT_WITHHELD).employee_tag || ' : '
2638 || g_ee_record(RS_SIT_WITHHELD).employee_data);
2639
2640 /* Other State Data*/
2641 g_ee_record(RS_OTHER_STATE_DATA).employee_data := ' ';
2642 HR_UTILITY_TRACE(g_ee_record(RS_OTHER_STATE_DATA).employee_tag || ' : '
2643 || g_ee_record(RS_OTHER_STATE_DATA).employee_data);
2644
2645 /* State EIC Advance*/
2646 g_ee_record(RS_STEIC_ADVANCE).employee_data :=ltr_ue_name_table(69).ue_value;
2647 HR_UTILITY_TRACE(g_ee_record(RS_STEIC_ADVANCE).employee_tag || ' : '
2648 || g_ee_record(RS_STEIC_ADVANCE).employee_data);
2649
2650 /* SUI Employee Withheld */
2651 g_ee_record(RS_SUI_EE_WITHHELD).employee_data := ltr_ue_name_table(70).ue_value;
2652 HR_UTILITY_TRACE(g_ee_record(RS_SUI_EE_WITHHELD).employee_tag || ' : '
2653 || g_ee_record(RS_SUI_EE_WITHHELD).employee_data);
2654
2655 /* SDI Employee Withheld */
2656 g_ee_record(RS_SDI_EE_WITHHELD).employee_data := ltr_ue_name_table(71).ue_value;
2657 HR_UTILITY_TRACE(g_ee_record(RS_SDI_EE_WITHHELD).employee_tag || ' : '
2658 || g_ee_record(RS_SDI_EE_WITHHELD).employee_data);
2659
2660 /* Supplemental Data 1 */
2661 g_ee_record(RS_SUPPLEMENTAL_DATA_1).employee_data := ' ';
2662 HR_UTILITY_TRACE(g_ee_record(RS_SUPPLEMENTAL_DATA_1).employee_tag || ' : '
2663 || g_ee_record(RS_SUPPLEMENTAL_DATA_1).employee_data);
2664
2665 /* Supplemental Data 2 */
2666 g_ee_record(RS_SUPPLEMENTAL_DATA_2).employee_data := ' ';
2667 HR_UTILITY_TRACE(g_ee_record(RS_SUPPLEMENTAL_DATA_2).employee_tag || ' : '
2668 || g_ee_record(RS_SUPPLEMENTAL_DATA_2).employee_data);
2669
2670 --
2671 -- Locality Data Items
2672 --
2673 HR_UTILITY_TRACE('Processing Locality Level Data for the Employee');
2674
2675 IF p_locality_code = 'NULL' THEN
2676 l_locality_code := NULL;
2677 ELSE
2678 l_locality_code := p_locality_code;
2679 END IF;
2680 --
2681 -- Delete the PL Table used for storing Locality Level Data for the Employee
2682 --
2683 ltr_local_record.delete;
2684 ltr_ue_locality.delete;
2685 i := 0;
2686 k := 0;
2687
2688 k := k +1;
2689 ltr_ue_locality(k).ue_name := 'A_CITY_SUBJ_NWHABLE_PER_JD_GRE_YTD'; --Local AI 1
2690 ltr_ue_locality(k).ue_data_level :='CITY';
2691 ltr_ue_locality(k).data_type :='AMT';
2692 ltr_ue_locality(k).negative_check := 'Y';
2693
2694 k := k +1;
2695 ltr_ue_locality(k).ue_name := 'A_CITY_SUBJ_WHABLE_PER_JD_GRE_YTD'; --Local AI 2
2696 ltr_ue_locality(k).ue_data_level :='CITY';
2697 ltr_ue_locality(k).data_type :='AMT';
2698 ltr_ue_locality(k).negative_check := 'Y';
2699
2700 k := k +1;
2701 ltr_ue_locality(k).ue_name := 'A_CITY_PRE_TAX_REDNS_PER_JD_GRE_YTD'; --Local AI 3
2702 ltr_ue_locality(k).ue_data_level :='CITY';
2703 ltr_ue_locality(k).data_type :='AMT';
2704 ltr_ue_locality(k).negative_check := 'Y';
2705
2706 k := k +1;
2707 ltr_ue_locality(k).ue_name := 'A_CITY_WITHHELD_PER_JD_GRE_YTD'; --Local AI 4
2708 ltr_ue_locality(k).ue_data_level :='CITY';
2709 ltr_ue_locality(k).data_type :='AMT';
2710 ltr_ue_locality(k).negative_check := 'Y';
2711
2712 -- Bug # 6117216 SD Reporting Changes START
2713
2714 k := k +1;
2715 ltr_ue_locality(k).ue_name := 'A_SCHOOL_LOCAL_WAGES'; --Local AI 5 for SD
2716 ltr_ue_locality(k).ue_data_level :='CITY SCHOOL';
2717 ltr_ue_locality(k).data_type :='AMT';
2718 ltr_ue_locality(k).negative_check := 'Y';
2719
2720 k := k +1;
2721 ltr_ue_locality(k).ue_name := 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'; --Local AI 6 for SD
2722 ltr_ue_locality(k).ue_data_level :='CITY SCHOOL';
2723 ltr_ue_locality(k).data_type :='AMT';
2724 ltr_ue_locality(k).negative_check := 'Y';
2725
2726
2727
2728 HR_UTILITY_TRACE('YE Assignment_Action_Id '|| to_char(p_ye_assignment_action_id));
2729 HR_UTILITY_TRACE('State Code '|| p_state_code);
2730 HR_UTILITY_TRACE('Locality Code '|| l_locality_code);
2731
2732 OPEN c_locality_jurisdiction( p_ye_assignment_action_id,
2733 p_state_code,
2734 l_locality_code);
2735 LOOP
2736 l_jurisdiction_code := NULL;
2737 FETCH c_locality_jurisdiction INTO l_jurisdiction_code;
2738 HR_UTILITY_TRACE('Processing Jurisdiction '|| l_jurisdiction_code);
2739 EXIT WHEN c_locality_jurisdiction%NOTFOUND;
2740 IF l_jurisdiction_code IS NOT NULL
2741 THEN
2742 --{
2743 --
2744 -- Fetching locality level archived data for employee
2745 --
2746 IF ltr_ue_locality.count > 0 then
2747 FOR j IN ltr_ue_locality.first .. ltr_ue_locality.last
2748 LOOP
2749 IF ltr_ue_locality(j).ue_data_level ='CITY' THEN
2750 ltr_ue_locality(j).ue_value := hr_us_w2_rep.get_w2_arch_bal(
2751 p_ye_assignment_action_id
2752 ,ltr_ue_locality(j).ue_name
2753 ,p_tax_unit_id
2754 ,l_jurisdiction_code
2755 , 11);
2756 IF ( ltr_ue_locality(j).data_type = 'AMT' AND
2757 to_number(ltr_ue_locality(j).ue_value) < 0)
2758 THEN
2759 l_status := 'FAILED';
2760 END IF;
2761
2762 ELSIF ltr_ue_locality(j).ue_data_level ='CITY SCHOOL' THEN
2763 ltr_ue_locality(j).ue_value := hr_us_w2_rep.get_w2_arch_bal(
2764 p_ye_assignment_action_id
2765 ,ltr_ue_locality(j).ue_name
2766 ,p_tax_unit_id
2767 ,l_jurisdiction_code
2768 ,8);
2769 IF ( ltr_ue_locality(j).data_type = 'AMT' AND
2770 to_number(ltr_ue_locality(j).ue_value) < 0)
2771 THEN
2772 l_status := 'FAILED';
2773 END IF;
2774
2775 ELSE
2776 ltr_ue_locality(j).ue_value := '0';
2777 END IF;
2778 HR_UTILITY_TRACE('DBI ' || ltr_ue_locality(J).ue_data_level ||
2779 ' -('||to_char(j)|| ') : < ' || ltr_ue_locality(j).ue_name||
2780 ' > VALUE : < '|| ltr_ue_locality(j).ue_value||' >');
2781
2782 END LOOP;
2783 END IF;
2784
2785 IF length(l_jurisdiction_code) > 9 THEN
2786
2787 OPEN c_city_data(l_jurisdiction_code
2788 ,p_date_earned);
2789 FETCH c_city_data INTO l_city_name,
2790 l_county_name,
2791 l_tax_type,
2792 l_city_code;
2793 CLOSE c_city_data;
2794 -- Derive City Wages
2795 l_city_wages := ltr_ue_locality(1).ue_value +
2796 ltr_ue_locality(2).ue_value -
2797 ltr_ue_locality(3).ue_value;
2798 -- City Tax Withheld
2799 l_city_tax_withheld := ltr_ue_locality(4).ue_value;
2800
2801 i := to_number(substr(l_jurisdiction_code,1,2) ||
2802 substr(l_jurisdiction_code,4,3) ||
2803 substr(l_jurisdiction_code,8,4) );
2804
2805 IF ltr_local_record.EXISTS(i) THEN
2806 --{
2807 NULL;
2808 --}
2809 ELSE
2810 ltr_local_record(i).jurisdiction := l_jurisdiction_code;
2811 ltr_local_record(i).city_name := l_city_name;
2812 ltr_local_record(i).county_name := l_county_name;
2813 ltr_local_record(i).tax_type := l_tax_type;
2814 ltr_local_record(i).locality_code := l_city_code;
2815 ltr_local_record(i).locality_wages := l_city_wages;
2816 ltr_local_record(i).locality_tax := l_city_tax_withheld;
2817 END IF;
2818
2819
2820 ELSE
2821
2822 OPEN c_sd_data(l_jurisdiction_code);
2823 FETCH c_sd_data INTO l_city_name, -- using city name for sd name
2824 l_tax_type,
2825 l_city_code;
2826 CLOSE c_sd_data;
2827
2828 l_city_wages := ltr_ue_locality(5).ue_value;
2829
2830 l_city_tax_withheld := ltr_ue_locality(6).ue_value;
2831
2832 i := to_number(substr(l_jurisdiction_code,1,2) ||
2833 substr(l_jurisdiction_code,4,5));
2834
2835 IF ltr_local_record.EXISTS(i) THEN
2836 --{
2837 NULL;
2838 --}
2839 ELSE
2840 ltr_local_record(i).jurisdiction := l_jurisdiction_code;
2841 ltr_local_record(i).city_name := l_city_name;
2842 ltr_local_record(i).tax_type := l_tax_type;
2843 ltr_local_record(i).locality_code := l_city_code;
2844 ltr_local_record(i).locality_wages := l_city_wages;
2845 ltr_local_record(i).locality_tax := l_city_tax_withheld;
2846 END IF;
2847
2848
2849 HR_UTILITY_TRACE('Locality Jurisdiction :' || ltr_local_record(i).jurisdiction);
2850 HR_UTILITY_TRACE(' City Name :' || ltr_local_record(i).city_name);
2851 HR_UTILITY_TRACE(' County Name :' || ltr_local_record(i).county_name);
2852 HR_UTILITY_TRACE(' Tax Type :' || ltr_local_record(i).tax_type);
2853 HR_UTILITY_TRACE(' City Code :' || ltr_local_record(i).locality_code);
2854 HR_UTILITY_TRACE(' City Wages :' || ltr_local_record(i).locality_wages);
2855 HR_UTILITY_TRACE(' City Withheld :' || ltr_local_record(i).locality_tax);
2856 --}
2857 END IF;
2858 END IF;
2859
2860 END LOOP;
2861 close c_locality_jurisdiction;
2862
2863 -- Bug # 6117216 SD Reporting Changes END
2864
2865 l_final_xml_string := '';
2866 l_final_xml_string := '<EMPLOYEE>' ||
2867 '<EXCEPTION>' || l_status || '</EXCEPTION>'|| EOL
2868 || '<RW>';
2869 BEGIN
2870 FOR I IN 1 .. 42 LOOP
2871 l_data_item_xml := '<'||g_ee_record(I).employee_tag||'>'||
2872 convert_special_char(nvl(g_ee_record(I).employee_data,' '))
2873 ||'</'||g_ee_record(I).employee_tag||'>'|| EOL;
2874 l_final_xml_string := l_final_xml_string || l_data_item_xml;
2875 HR_UTILITY_TRACE(l_data_item_xml);
2876 END LOOP;
2877 /*
2878 This got added for FIT Withheld at 3rd Party Payer
2879 This Balance is not reported in RW record but total is reported in RT record
2880 */
2881 l_data_item_xml := '<'||g_ee_record(FIT_WITHHELD_THIRD_PARTY).employee_tag||'>'||
2882 convert_special_char(nvl(g_ee_record(FIT_WITHHELD_THIRD_PARTY).employee_data,' '))
2883 ||'</'||g_ee_record(FIT_WITHHELD_THIRD_PARTY).employee_tag||'>'|| EOL;
2884 l_final_xml_string := l_final_xml_string || l_data_item_xml;
2885 HR_UTILITY_TRACE(l_data_item_xml);
2886
2887 l_final_xml_string := l_final_xml_string || '</RW>' || EOL;
2888 EXCEPTION
2889 WHEN OTHERS THEN
2890 HR_UTILITY_TRACE('Error Encountered While formating RW Record Data Item');
2891 END;
2892 HR_UTILITY_TRACE(' RW Record Formatted Successfully Length '|| length(l_final_xml_string));
2893
2894 /* Merge RO Record Data Compoents to XML Construct */
2895 l_final_xml_string := l_final_xml_string
2896 || '<'||convert_special_char(g_ee_record(43).employee_data)||'>';
2897 IF l_zero_ro_record = 'Y' THEN
2898 l_final_xml_string := l_final_xml_string
2899 || '<ZERO_VALUE>Y</ZERO_VALUE>' || EOL;
2900 ELSE
2901 l_final_xml_string := l_final_xml_string
2902 || '<ZERO_VALUE>N</ZERO_VALUE>' || EOL;
2903 END IF;
2904
2905 BEGIN
2906 FOR I IN 44 .. 60
2907 LOOP
2908 l_data_item_xml := '<'||g_ee_record(I).employee_tag||'>'||
2909 convert_special_char(nvl(g_ee_record(I).employee_data,' '))
2910 ||'</'||g_ee_record(I).employee_tag||'>'|| EOL;
2911 HR_UTILITY_TRACE(l_data_item_xml);
2912 l_final_xml_string := l_final_xml_string || l_data_item_xml;
2913 END LOOP;
2914 l_final_xml_string := l_final_xml_string || '</' || convert_special_char(g_ee_record(43).employee_data)
2915 || '>' || EOL;
2916 EXCEPTION
2917 WHEN OTHERS THEN
2918 HR_UTILITY_TRACE('Error Encountered While formating RO Record Data Item');
2919 END;
2920 HR_UTILITY_TRACE(' RO Record Formatted Successfully Length '|| length(l_final_xml_string));
2921
2922 /* Merge RS Record Data Compoents to XML Construct */
2923 l_final_xml_string := l_final_xml_string || '<RS>';
2924 BEGIN
2925 FOR I IN 61 .. 78
2926 LOOP
2927 l_data_item_xml := '<'||g_ee_record(I).employee_tag||'>'||
2928 convert_special_char(nvl(g_ee_record(I).employee_data,' '))
2929 ||'</'||g_ee_record(I).employee_tag||'>'|| EOL;
2930 HR_UTILITY_TRACE(l_data_item_xml);
2931 l_final_xml_string := l_final_xml_string || l_data_item_xml;
2932 END LOOP;
2933 l_final_xml_string := l_final_xml_string || '</RS>' || EOL;
2934
2935 EXCEPTION
2936 WHEN OTHERS THEN
2937 HR_UTILITY_TRACE('Error Encountered While formating RS Record Data Item');
2938 END;
2939 HR_UTILITY_TRACE(' RS Record Formatted Successfully Length '|| length(l_final_xml_string));
2940 --
2941 -- Following Procedure will initialize Locality Data Item Tags
2942 --
2943 populate_ee_locality_tag;
2944
2945 HR_UTILITY_TRACE(' Formating Locality Records for Employee ');
2946 -- l_final_xml_string := l_final_xml_string || '<CITY>';
2947
2948 IF ltr_local_record.COUNT >= 1
2949 THEN
2950 --{
2951 i := NULL;
2952 j := ltr_local_record.COUNT;
2953 k := 0;
2954 i := ltr_local_record.FIRST;
2955 l_data_item_xml := '';
2956 WHILE i IS NOT NULL
2957 LOOP
2958 IF (ltr_local_record(i).jurisdiction IS NOT NULL)
2959 THEN
2960 --{
2961 k := k + 1;
2962
2963 -- Bug # 6117216 SD Reporting Changes START
2964
2965 IF length(ltr_local_record(i).jurisdiction) < 9 THEN
2966 l_data_item_xml := '<SD>'||
2967 '<'|| ltr_ee_locality_tag(8)||'>'||
2968 convert_special_char(nvl(ltr_local_record(i).jurisdiction ,' '))
2969 ||'</'|| ltr_ee_locality_tag(8)||'>'|| EOL
2970 || '<'|| ltr_ee_locality_tag(9)||'>'||
2971 convert_special_char(nvl(ltr_local_record(i).city_name ,' '))
2972 ||'</'|| ltr_ee_locality_tag(9)||'>'|| EOL
2973 || '<'|| ltr_ee_locality_tag(10)||'>'||
2974 convert_special_char(nvl(ltr_local_record(i).tax_type ,' '))
2975 ||'</'|| ltr_ee_locality_tag(10)||'>'|| EOL
2976 || '<'|| ltr_ee_locality_tag(11)||'>'||
2977 convert_special_char(nvl(ltr_local_record(i).locality_code ,' '))
2978 ||'</'|| ltr_ee_locality_tag(11)||'>'|| EOL
2979 || '<'|| ltr_ee_locality_tag(12)||'>'||
2980 convert_special_char(nvl(ltr_local_record(i).locality_wages ,' '))
2981 ||'</'|| ltr_ee_locality_tag(12)||'>'|| EOL
2982 || '<'|| ltr_ee_locality_tag(13)||'>'||
2983 convert_special_char(nvl(ltr_local_record(i).locality_tax ,' '))
2984 ||'</'|| ltr_ee_locality_tag(13)||'>'|| EOL
2985 ||'</SD>' || EOL;
2986 HR_UTILITY_TRACE('SD XML '|| l_data_item_xml);
2987
2988 ELSE l_data_item_xml := '<CITY>'||
2989 '<'|| ltr_ee_locality_tag(CITY_JURISDICTION)||'>'||
2990 convert_special_char(nvl(ltr_local_record(i).jurisdiction ,' '))
2991 ||'</'|| ltr_ee_locality_tag(CITY_JURISDICTION)||'>'|| EOL
2992 || '<'|| ltr_ee_locality_tag(CITY_NAME)||'>'||
2993 convert_special_char(nvl(ltr_local_record(i).city_name ,' '))
2994 ||'</'|| ltr_ee_locality_tag(CITY_NAME)||'>'|| EOL
2995 || '<'|| ltr_ee_locality_tag(COUNTY_NAME)||'>'||
2996 convert_special_char(nvl(ltr_local_record(i).county_name ,' '))
2997 ||'</'|| ltr_ee_locality_tag(COUNTY_NAME)||'>'|| EOL
2998 || '<'|| ltr_ee_locality_tag(TAX_TYPE)||'>'||
2999 convert_special_char(nvl(ltr_local_record(i).tax_type ,' '))
3000 ||'</'|| ltr_ee_locality_tag(TAX_TYPE)||'>'|| EOL
3001 || '<'|| ltr_ee_locality_tag(CITY_CODE)||'>'||
3002 convert_special_char(nvl(ltr_local_record(i).locality_code ,' '))
3003 ||'</'|| ltr_ee_locality_tag(CITY_CODE)||'>'|| EOL
3004 || '<'|| ltr_ee_locality_tag(CITY_WAGES)||'>'||
3005 convert_special_char(nvl(ltr_local_record(i).locality_wages ,' '))
3006 ||'</'|| ltr_ee_locality_tag(CITY_WAGES)||'>'|| EOL
3007 || '<'|| ltr_ee_locality_tag(CITY_TAX_WITHHELD)||'>'||
3008 convert_special_char(nvl(ltr_local_record(i).locality_tax ,' '))
3009 ||'</'|| ltr_ee_locality_tag(CITY_TAX_WITHHELD)||'>'|| EOL
3010 ||'</CITY>' || EOL;
3011 HR_UTILITY_TRACE('Locality XML '|| l_data_item_xml);
3012 --}
3013 END IF;
3014
3015 -- Bug # 6117216 SD Reporting Changes END
3016
3017 END IF;
3018 i := ltr_local_record.NEXT(i);
3019 l_final_xml_string := l_final_xml_string || l_data_item_xml;
3020 END LOOP;
3021 --}
3022 END IF; -- ltr_local_record.COUNT >= 1
3023
3024 l_final_xml_string := l_final_xml_string || '</EMPLOYEE>' || EOL;
3025 p_final_string := l_final_xml_string;
3026 --}
3027 EXCEPTION
3028 WHEN OTHERS THEN
3029 HR_UTILITY_TRACE('Error Encountered in procedure populate_arch_employee');
3030 END populate_arch_employee; -- End of Procedure populate_arch_employee
3031
3032
3033 BEGIN
3034 -- hr_utility.trace_on(null, 'USLOCALW2');
3035 g_proc_name := 'pay_us_w2_generic_extract.';
3036 g_debug := hr_utility.debug_enabled;
3037 g_document_type := 'LOCAL_W2_XML';
3038 END pay_us_w2_generic_extract;