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