[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_US_PSD_GENERIC_EXTRACT
Source
1 PACKAGE BODY PAY_US_PSD_GENERIC_EXTRACT as
2 /* $Header: payuspsdgnxtract.pkb 120.0.12020000.14 2013/01/10 11:14:33 pracagra noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 PAY_US_PSD_GENERIC_EXTRACT
10
11 File
12 payuspsdgnxtract.pkb
13
14 Purpose
15 The purpose of this package is to support the 'PSD Periodic Wage Listing Report' Process
16 This package is created to include generic database package components may be used for by
17 'PSD Periodic Wage Listing Report' process for Extracting archived Data, Validating
18 and constructing XML element
19
20 Notes
21
22 History
23
24 Date User Id Version Bug Description
25 ============================================================================
26 09-jul-12 pracagra 115.0 11712075 Initial Version Created.
27 16-jul-12 pracagra 115.1 11712075 Included 'EE_EMPLOYEE_NUMBER' employee xml tag.
28 19-jul-12 pracagra 115.2 11712075 Modified 'EE_EXCEPTION_TCD_CODE' employee xml tag.
29 26-jun-12 pracagra 115.3 14373899 Modified hoi.org_information10 to
30 substr(hoi.org_information10,1,6)
31 because of the changes done to
32 'PAY_US_PSD_CODES' valueset ID.
33 06-aug-12 pracagra 115.4 14407407 Added two new EE xml tags 'EE_TAX_YEAR'
34 and 'EE_TAX_QUARTER'
35 24-aug-12 pracagra 115.5 14379256 Added new EE xml tag 'EE_WAH_PSD_CODE_EXISTS'
36 and new exception status for Philadephia emps.
37 07-sep-12 pracagra 115.6 14580715 and 14596652 Added new EE xml tag 'EE_EXCEPTION_WORK_PSD',
38 new ER xml tag 'ER_SUCCESS_PSD_CODE'.
39 and removed duplicate 'ER_TAX_TYPE' xml tag.
40 21-sep-12 pracagra 115.7 Added a condition to mark employees with missing SSN as invalid.
41 21-sep-12 pracagra 115.7 Removed GSCC errors.
42 22-nov-12 skchalla 115.8 14799833 Modified for the Yearly reporting.
43 26-nov-12 pkoduri 115.9 14286448 Corrections for GRE name length issue.
44 06-dec-12 skchalla 115.10 15960706 Modified the Monthly Filing Indicator.
45 07-dec-12 skchalla 115.11 15960706 Modified the RS record pos 197-202, Reporting period.
46 10-jan-13 pracagra 115.13 16008268 Corrections for GRE name length issue.
47 ============================================================================*/
48 --
49 -- Global Variables
50 --
51 g_proc_name varchar2(240);
52 g_debug boolean;
53 g_document_type varchar2(50);
54
55 /****************************************************************************
56 Name : HR_UTILITY_TRACE
57 Description : This procedure prints debug messages.
58 *****************************************************************************/
59
60 PROCEDURE HR_UTILITY_TRACE
61 (
62 P_TRC_DATA varchar2
63 ) AS
64 BEGIN
65 IF g_debug THEN
66 hr_utility.trace(p_trc_data);
67 END IF;
68 END HR_UTILITY_TRACE;
69
70 FUNCTION convert_special_char( p_data varchar2)
71 RETURN varchar2 IS
72 l_data VARCHAR2(2000);
73 BEGIN
74 l_data := trim(p_data);
75 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
76 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
77 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
78 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
79 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
80 RETURN l_data;
81 END convert_special_char;
82
83
84 --
85 -- This procedure would be used to populate the Tag used for RA Data Record
86 --
87 PROCEDURE populate_ra_data_tag
88 IS
89 i number;
90 BEGIN
91
92 ltr_ra_data_tag(1) :='TR_REC_IDENTIFIER';
93 ltr_ra_data_tag(2) :='TR_EIN';
94 ltr_ra_data_tag(3) :='TR_PIN';
95 ltr_ra_data_tag(4) :='RESUB_INDICATOR';
96 ltr_ra_data_tag(5) :='RESUB_WFID';
97 ltr_ra_data_tag(6) :='SOFTWARE_CODE';
98 ltr_ra_data_tag(7) :='COMPANY_NAME';
99 ltr_ra_data_tag(8) :='LOCATION_ADDRESS';
100 ltr_ra_data_tag(9) :='TR_DELIVERY_ADDRESS';
101 ltr_ra_data_tag(10) :='TR_CITY';
102 ltr_ra_data_tag(11) :='TR_STATE_ABBREVIATION';
103 ltr_ra_data_tag(12) :='TR_ZIP_CODE';
104 ltr_ra_data_tag(13) :='TR_ZIP_CODE_EXTENSION';
105 ltr_ra_data_tag(14) :='TR_FOREIGN_STATE_PROVINCE';
106 ltr_ra_data_tag(15) :='TR_FOREIGN_POSTAL_CODE';
107 ltr_ra_data_tag(16) :='TR_COUNTRY_CODE';
108 ltr_ra_data_tag(17) :='SUBMITTER_NAME';
109 ltr_ra_data_tag(18) :='CP_LOCATION_ADDRESS';
110 ltr_ra_data_tag(19) :='CP_DELIVERY_ADDRESS';
111 ltr_ra_data_tag(20) :='CP_CITY';
112 ltr_ra_data_tag(21) :='CP_STATE_ABBREVIATION';
113 ltr_ra_data_tag(22) :='CP_ZIP_CODE';
114 ltr_ra_data_tag(23) :='CP_ZIP_CODE_EXTENSION';
115 ltr_ra_data_tag(24) :='CP_FOREIGN_STATE_PROVINCE';
116 ltr_ra_data_tag(25) :='CP_FOREIGN_POSTAL_CODE';
117 ltr_ra_data_tag(26) :='CP_COUNTRY_CODE';
118 ltr_ra_data_tag(27) :='CONTACT_NAME';
119 ltr_ra_data_tag(28) :='CONTACT_PHONE_NUMBER';
120 ltr_ra_data_tag(29) :='CONTACT_PHONE_EXTENSION';
121 ltr_ra_data_tag(30) :='CONTACT_EMAIL';
122 ltr_ra_data_tag(31) :='CONTACT_FAX ';
123 ltr_ra_data_tag(32) :='METHOD_OF_NOTIFICATION';
124 ltr_ra_data_tag(33) :='PREPARER_CODE';
125 --
126 -- Following Loop structure used to debug the Tag Values
127 --
128 FOR I IN 1 .. g_ra_no_of_tag LOOP
129 HR_UTILITY_TRACE('Tag'||to_char(i)|| ' : '||ltr_ra_data_tag(i));
130 END LOOP;
131 END populate_ra_data_tag;
132
133
134 --
135 -- This Procedue would be used to fetch all the submitter/transmitter related data
136 -- All the data would then be populated into a global pl/sql based table for constructing XML
137 --
138 PROCEDURE populate_arch_transmitter ( p_payroll_action_id NUMBER
139 ,p_tax_unit_id NUMBER
140 ,p_date_earned DATE
141 ,p_reporting_year VARCHAR2
142 ,p_jurisdiction_code VARCHAR2
143 ,p_state_code NUMBER
144 ,p_state_abbreviation VARCHAR2
145 ,status VARCHAR2
146 ,p_final_string OUT NOCOPY VARCHAR2
147 )
148 AS
149 --{
150 --
151 -- Declaration of Index Value that will be used for storing and fetching Submitter level Data
152 -- from the global pl/sql table maintained for submitter level data
153 --
154 TR_REC_IDENTIFIER NUMBER := 1;
155 TR_EIN NUMBER := 2;
156 TR_PIN NUMBER := 3;
157 RESUB_INDICATOR NUMBER := 4;
158 RESUB_WFID NUMBER := 5;
159 SOFTWARE_CODE NUMBER := 6;
160 COMPANY_NAME NUMBER := 7;
161 LOCATION_ADDRESS NUMBER := 8;
162 TR_DELIVERY_ADDRESS NUMBER := 9;
163 TR_CITY NUMBER := 10;
164 TR_STATE_ABBREVIATION NUMBER := 11;
165 TR_ZIP_CODE NUMBER := 12;
166 TR_ZIP_CODE_EXTENSION NUMBER := 13;
167 TR_FOREIGN_STATE_PROVINCE NUMBER := 14;
168 TR_FOREIGN_POSTAL_CODE NUMBER := 15;
169 TR_COUNTRY_CODE NUMBER := 16;
170 SUBMITTER_NAME NUMBER := 17;
171 CP_LOCATION_ADDRESS NUMBER := 18;
172 CP_DELIVERY_ADDRESS NUMBER := 19;
173 CP_CITY NUMBER := 20;
174 CP_STATE_ABBREVIATION NUMBER := 21;
175 CP_ZIP_CODE NUMBER := 22;
176 CP_ZIP_CODE_EXTENSION NUMBER := 23;
177 CP_FOREIGN_STATE_PROVINCE NUMBER := 24;
178 CP_FOREIGN_POSTAL_CODE NUMBER := 25;
179 CP_COUNTRY_CODE NUMBER := 26;
180 CONTACT_NAME NUMBER := 27;
181 CONTACT_PHONE_NUMBER NUMBER := 28;
182 CONTACT_PHONE_EXTENSION NUMBER := 29;
183 CONTACT_EMAIL NUMBER := 30;
184 CONTACT_FAX NUMBER := 31;
185 METHOD_OF_NOTIFICATION NUMBER := 32;
186 PREPARER_CODE NUMBER := 33;
187
188
189 --
190 -- Local Variables required for Employer/Contact person Data
191 --
192 l_payroll_action_id number;
193 l_assignment_id number;
194 l_date_earned date;
195 l_tax_unit_id number;
196 l_input_report_type varchar2(200) := 'W2';
197 l_input_report_type_format varchar2(200) := 'MMREF';
198 l_input_record_name varchar2(200) := 'RA';
199 l_effective_date varchar2(200);
200 l_item_name varchar2(200);
201 l_input_report_qualifier varchar2(200);
202
203 /* Submitter Input Variables for fetching Submitter Data */
204
205 input_sbmtr_name varchar2(200) ;
206 input_sbmtr_1 varchar2(200) ;
207 input_sbmtr_2 varchar2(200) ;
208 input_sbmtr_3 varchar2(200) ;
209 input_sbmtr_4 varchar2(200) ;
210 input_sbmtr_5 varchar2(200) ;
211 input_sbmtr_validiate_flag varchar2(200) ;
212 sbmtr_exclude_output_flag varchar2(200) ;
213 -- Output
214 sbmtr_out_1 varchar2(200) := ' ';
215 sbmtr_out_2 varchar2(200) := ' ';
216 sbmtr_out_3 varchar2(200) := ' ';
217 sbmtr_out_4 varchar2(200) := ' ';
218 sbmtr_out_5 varchar2(200) := ' ';
219 sbmtr_out_6 varchar2(200) := ' ';
220 sbmtr_out_7 varchar2(200) := ' ';
221 sbmtr_out_8 varchar2(200) := ' ';
222 sbmtr_out_9 varchar2(200) := ' ';
223 sbmtr_out_10 varchar2(240) := ' '; -- Bug# 16008268 Gre length issue
224
225 /* Local Variables for Company Information */
226 input_empr_name varchar2(240); -- Bug# 14286448 Gre length issue
227 input_empr_1 varchar2(200);
228 input_empr_2 varchar2(200) := ' ';
229 input_empr_3 varchar2(200) := ' ';
230 input_empr_4 varchar2(200) := ' ';
231 input_empr_5 varchar2(200) := ' ';
232 empr_validate_flag varchar2(200) := 'Y';
233 empr_exclude_output_flag varchar2(200) := 'N';
234 empr_out_1 varchar2(200) := ' ';
235 empr_out_2 varchar2(200) := ' ';
236 empr_out_3 varchar2(200) := ' ';
237 empr_out_4 varchar2(200) := ' ';
238 empr_out_5 varchar2(200) := ' ';
239 empr_out_6 varchar2(200) := ' ';
240 empr_out_7 varchar2(200) := ' ';
241 empr_out_8 varchar2(200) := ' ';
242 empr_out_9 varchar2(200) := ' ';
243 empr_out_10 varchar2(240) := ' '; -- Bug# 16008268 Gre length issue
244
245 /* Local Variables for CONTACT INFORMATION Input */
246 input_cnti_name varchar2(200);
247 input_cnti_1 varchar2(200);
248 input_cnti_2 varchar2(200);
249 input_cnti_3 varchar2(200);
250 input_cnti_4 varchar2(200);
251 input_cnti_5 varchar2(200);
252 input_cnti_validate_flag varchar2(200);
253 cnti_exclude_output_flag varchar2(200);
254 cnti_out_1 varchar2(200) := ' ';
255 cnti_out_2 varchar2(200) := ' ';
256 cnti_out_3 varchar2(200) := ' ';
257 cnti_out_4 varchar2(200) := ' ';
258 cnti_out_5 varchar2(200) := ' ';
259 cnti_out_6 varchar2(200) := ' ';
260 cnti_out_7 varchar2(200) := ' ';
261 cnti_out_8 varchar2(200) := ' ';
262 cnti_out_9 varchar2(200) := ' ';
263 cnti_out_10 varchar2(200) := ' ';
264
265 i number :=0;
266 j number :=0;
267 --PL Table used for storing and manipulating DataBaseItem used for
268 -- Submitter level information
269 TYPE RA_UE_REC IS RECORD (
270 ue_name varchar2(200),
271 ue_value varchar2(240) -- Bug# 16008268 Gre length issue
272 );
273
274 TYPE ra_ue_record IS TABLE OF ra_ue_rec
275 INDEX BY BINARY_INTEGER;
276 ltr_ue_name_table ra_ue_record;
277 l_number_ra_dbi NUMBER := 6;
278
279 --
280 -- RA Record Data Item Tags
281 --
282
283 submitter_data varchar2(200);
284 employer_data varchar2(200);
285 contact_data varchar2(200);
286
287 -- SRS Parameter
288
289 l_srs_trns_pin varchar2(200);
290 l_srs_trns_tlcn varchar2(200);
291 l_srs_resub_indicator varchar2(200);
292
293 -- Derived Local Variables
294 l_resub_indicator varchar2(200);
295 l_reporting_date varchar2(200);
296
297 l_err boolean := FALSE;
298 l_validate varchar2(100);
299 l_validated_EIN varchar2(200);
300
301 l_final_xml_string varchar2(32767);
302
303 EOL varchar2(10);
304
305 /* End of Variable Declarations */
306 BEGIN
307 --{
308 --
309 -- Fetch all Context or Parameters set at the Transmitter Cursor
310 --
311 --l_tax_unit_id := pay_magtape_generic.get_parameter_value('TAX_UNIT_ID');
312 --l_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
313 --l_date_earned := pay_magtape_generic.get_parameter_value('DATE_EARNED');
314 --l_reporting_date := '31-DEC-'||pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
315
316 l_tax_unit_id := p_tax_unit_id;
317 l_payroll_action_id := p_payroll_action_id;
318 l_date_earned := p_date_earned;
319 --l_reporting_date := '31-DEC-'||p_reporting_year;
320 --l_effective_date := l_reporting_date;
321
322 l_effective_date := l_date_earned;
323 l_reporting_date :=l_effective_date;
324
325 --
326 -- Fetch SRS Parameter
327 --
328 l_srs_trns_pin := pay_magtape_generic.get_parameter_value('TRNS_PIN');
329 l_srs_trns_tlcn := pay_magtape_generic.get_parameter_value('TRNS_TLCN');
330
331 l_input_report_qualifier := pay_magtape_generic.get_parameter_value('TRANSFER_STATE');
332
333 --
334 -- Fetch Archived Values of various DBIs used in submitter Record
335 --
336
337 i := i +1;
338 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER'; --1
339 i := i +1;
340 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_NAME'; --2
341 i := i +1;
342 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_COMPANY_NAME'; --3
343 i := i +1;
344 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME'; --4
345 i := i +1;
346 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_NOTIFICATION_METHOD'; --5
347 i := i +1;
348 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_PREPARER'; --6
349
350 if ltr_ue_name_table.count > 0 then
351 for j in ltr_ue_name_table.first .. ltr_ue_name_table.last
352 loop
353 ltr_ue_name_table(j).ue_value := hr_us_w2_rep.get_w2_tax_unit_item (l_tax_unit_id,
354 l_payroll_action_id,
355 ltr_ue_name_table(j).ue_name);
356 end loop;
357 end if;
358
359 /* =========================================================
360 Get Submitted Information to be used for extracting MMREF-1 RA record type
361 ========================================================== */
362 input_sbmtr_name := 'ER_ADDRESS';
363 input_sbmtr_1 := pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID');
364 input_sbmtr_validiate_flag := 'Y';
365 sbmtr_exclude_output_flag := 'N';
366
367 submitter_data := pay_us_reporting_utils_pkg.get_item_data(
368 NULL, -- Assignment Id (Not Used at RA record)
369 l_date_earned,
370 l_tax_unit_id,
371 l_reporting_date,
372 input_sbmtr_name,
373 l_input_report_type,
374 l_input_report_type_format,
375 l_input_report_qualifier,
376 l_input_record_name,
377 input_sbmtr_1,
378 input_sbmtr_2,
379 input_sbmtr_3,
380 input_sbmtr_4,
381 input_sbmtr_5,
382 input_sbmtr_validiate_flag,
383 sbmtr_exclude_output_flag,
384 sbmtr_out_1,
385 sbmtr_out_2,
386 sbmtr_out_3,
387 sbmtr_out_4,
388 sbmtr_out_5,
389 sbmtr_out_6,
390 sbmtr_out_7,
391 sbmtr_out_8,
392 sbmtr_out_9,
393 sbmtr_out_10);
394
395
396 /* ==================================================
397 Get Employer or transmitter Company Information releated Submitter
398 ================================================== */
399
400 input_empr_name := 'CR_ADDRESS';
401 input_empr_1 := ltr_ue_name_table(3).ue_value;
402
403 employer_data := pay_us_reporting_utils_pkg.get_item_data(
404 NULL, -- Assignment Id (Not Used at RA record)
405 l_date_earned,
406 l_tax_unit_id,
407 l_reporting_date,
408 input_empr_name,
409 l_input_report_type,
410 l_input_report_type_format,
411 l_input_report_qualifier,
412 l_input_record_name,
413 input_empr_1,
414 input_empr_2,
415 input_empr_3,
416 input_empr_4,
417 input_empr_5,
418 empr_validate_flag,
419 empr_exclude_output_flag,
420 empr_out_1,
421 empr_out_2,
422 empr_out_3,
423 empr_out_4,
424 empr_out_5,
425 empr_out_6,
426 empr_out_7,
427 empr_out_8,
428 empr_out_9,
429 empr_out_10);
430
431 /* ========================================================
432 Fetch Information of Contact Person submitting the PSD Period Wage Listing Magnetic Tape
433 ========================================================= */
434 input_cnti_name := 'CR_PERSON';
435 -- A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME
436 input_cnti_1 := ltr_ue_name_table(4).ue_value;
437 input_cnti_validate_flag := 'Y';
438 cnti_exclude_output_flag := 'N';
439
440 contact_data := pay_us_reporting_utils_pkg.get_item_data(
441 NULL, -- Assignment Id (Not Used at RA record)
442 l_date_earned,
443 l_tax_unit_id,
444 l_reporting_date,
445 input_cnti_name,
446 l_input_report_type,
447 l_input_report_type_format,
448 l_input_report_qualifier,
449 l_input_record_name,
450 input_cnti_1,
451 input_cnti_2,
452 input_cnti_3,
453 input_cnti_4,
454 input_cnti_5,
455 input_cnti_validate_flag,
456 cnti_exclude_output_flag,
457 cnti_out_1,
458 cnti_out_2,
459 cnti_out_3,
460 cnti_out_4,
461 cnti_out_5,
462 cnti_out_6,
463 cnti_out_7,
464 cnti_out_8,
465 cnti_out_9,
466 cnti_out_10);
467 --
468 -- Pouplate All the Tags to be used for RA record Data Items
469 --
470 pay_us_psd_generic_extract.populate_ra_data_tag;
471
472
473 /* Record Identifier */
474 g_ra_record(TR_REC_IDENTIFIER).submitter_data := 'RA';
475 g_ra_record(TR_REC_IDENTIFIER).submitter_tag := ltr_ra_data_tag(TR_REC_IDENTIFIER);
476
477 /* Submitter Employer Identification Number(EIN) */
478 --
479 -- EIN Validation
480 --
481 l_validated_EIN :=
482 pay_us_report_data_validation.validate_W2_EIN( 'FED',
483 l_input_record_name,
484 ltr_ue_name_table(1).ue_value,
485 empr_out_10,
486 empr_validate_flag,
487 l_err
488 );
489 g_submitter_ein := l_validated_EIN;
490 g_ra_record(TR_EIN).submitter_data := l_validated_EIN;
491 --ltr_ue_name_table(1).ue_value;
492 g_ra_record(TR_EIN).submitter_tag := ltr_ra_data_tag(TR_EIN);
493
494 /* Personal Identification Number (PIN) */
495 g_ra_record(TR_PIN).submitter_data := l_srs_trns_pin;
496 g_ra_record(TR_PIN).submitter_tag := ltr_ra_data_tag(TR_PIN);
497
498 /* Resub Indicator */
499 IF length(l_srs_trns_tlcn) > 0
500 THEN
501 l_resub_indicator := '1';
502 ELSE
503 l_resub_indicator := '0';
504 END IF;
505 g_ra_record(RESUB_INDICATOR).submitter_data := l_resub_indicator;
506 g_ra_record(RESUB_INDICATOR).submitter_tag := ltr_ra_data_tag(RESUB_INDICATOR);
507
508 /* Resub TLCN */
509 g_ra_record(RESUB_WFID).submitter_data := l_srs_trns_tlcn;
510 g_ra_record(RESUB_WFID).submitter_tag := ltr_ra_data_tag(RESUB_WFID);
511 /* Software Code */
512 g_ra_record(SOFTWARE_CODE).submitter_data := '99';
513 g_ra_record(SOFTWARE_CODE).submitter_tag := ltr_ra_data_tag(SOFTWARE_CODE);
514 /* Company Name */
515 g_ra_record(COMPANY_NAME).submitter_data := empr_out_10;
516 g_ra_record(COMPANY_NAME).submitter_tag := ltr_ra_data_tag(COMPANY_NAME);
517 /* Company, Location Address */
518 g_ra_record(LOCATION_ADDRESS).submitter_data := empr_out_1;
519 g_ra_record(LOCATION_ADDRESS).submitter_tag := ltr_ra_data_tag(LOCATION_ADDRESS);
520 /* Company, Delivery Address */
521 g_ra_record(TR_DELIVERY_ADDRESS).submitter_data := empr_out_2;
522 g_ra_record(TR_DELIVERY_ADDRESS).submitter_tag := ltr_ra_data_tag(TR_DELIVERY_ADDRESS);
523 /* Company, City */
524 g_ra_record(TR_CITY).submitter_data := empr_out_3;
525 g_ra_record(TR_CITY).submitter_tag := 'TR_CITY';
526 /* Company, State Abbreviation */
527 g_ra_record(TR_STATE_ABBREVIATION).submitter_data := empr_out_4;
528 g_ra_record(TR_STATE_ABBREVIATION).submitter_tag := ltr_ra_data_tag(TR_STATE_ABBREVIATION);
529 /* Desc: Company, Zip Code */
530 g_ra_record(TR_ZIP_CODE).submitter_data := empr_out_5;
531 g_ra_record(TR_ZIP_CODE).submitter_tag := 'TR_ZIP_CODE';
532 /* Desc: Company, Zip Code Extension */
533 g_ra_record(TR_ZIP_CODE_EXTENSION).submitter_data := empr_out_6;
534 g_ra_record(TR_ZIP_CODE_EXTENSION).submitter_tag := ltr_ra_data_tag(TR_ZIP_CODE_EXTENSION);
535 /* Company, Foreign State or Province */
536 g_ra_record(TR_FOREIGN_STATE_PROVINCE).submitter_data := empr_out_7;
537 g_ra_record(TR_FOREIGN_STATE_PROVINCE).submitter_tag := ltr_ra_data_tag(TR_FOREIGN_STATE_PROVINCE);
538 /* Company, Foreign Postal Code */
539 g_ra_record(TR_FOREIGN_POSTAL_CODE).submitter_data := empr_out_8;
540 g_ra_record(TR_FOREIGN_POSTAL_CODE).submitter_tag := ltr_ra_data_tag(TR_FOREIGN_POSTAL_CODE);
541 /* Company, Country Code */
542 g_ra_record(TR_COUNTRY_CODE).submitter_data := empr_out_9;
543 g_ra_record(TR_COUNTRY_CODE).submitter_tag := ltr_ra_data_tag(TR_COUNTRY_CODE);
544 /* Submitter Name */
545 g_ra_record(SUBMITTER_NAME).submitter_data := ltr_ue_name_table(2).ue_value;
546 g_ra_record(SUBMITTER_NAME).submitter_tag := ltr_ra_data_tag(SUBMITTER_NAME);
547 /* Submitter, Location Address */
548 g_ra_record(CP_LOCATION_ADDRESS).submitter_data := sbmtr_out_1;
549 g_ra_record(CP_LOCATION_ADDRESS).submitter_tag := ltr_ra_data_tag(CP_LOCATION_ADDRESS);
550 /* Submitter, Delivery Address */
551 g_ra_record(CP_DELIVERY_ADDRESS).submitter_data := sbmtr_out_2;
552 g_ra_record(CP_DELIVERY_ADDRESS).submitter_tag := ltr_ra_data_tag(CP_DELIVERY_ADDRESS);
553 /* Submitter, City */
554 g_ra_record(CP_CITY).submitter_data := sbmtr_out_3;
555 g_ra_record(CP_CITY).submitter_tag := ltr_ra_data_tag(CP_CITY);
556 /* Submitter, State Abbreviation */
557 g_ra_record(CP_STATE_ABBREVIATION).submitter_data := sbmtr_out_4;
558 g_ra_record(CP_STATE_ABBREVIATION).submitter_tag := ltr_ra_data_tag(CP_STATE_ABBREVIATION);
559 /* Submitter, Zip Code */
560 g_ra_record(CP_ZIP_CODE).submitter_data := sbmtr_out_5;
561 g_ra_record(CP_ZIP_CODE).submitter_tag := ltr_ra_data_tag(CP_ZIP_CODE);
562 /* Submitter, Zip Code Extension */
563 g_ra_record(CP_ZIP_CODE_EXTENSION).submitter_data := sbmtr_out_6;
564 g_ra_record(CP_ZIP_CODE_EXTENSION).submitter_tag := ltr_ra_data_tag(CP_ZIP_CODE_EXTENSION);
565 /* Submitter, Foreign State or Province */
566 g_ra_record(CP_FOREIGN_STATE_PROVINCE).submitter_data := sbmtr_out_7;
567 g_ra_record(CP_FOREIGN_STATE_PROVINCE).submitter_tag := ltr_ra_data_tag(CP_FOREIGN_STATE_PROVINCE);
568 /* Submitter, Foreign Postal Code */
569 g_ra_record(CP_FOREIGN_POSTAL_CODE).submitter_data := sbmtr_out_8;
570 g_ra_record(CP_FOREIGN_POSTAL_CODE).submitter_tag := ltr_ra_data_tag(CP_FOREIGN_POSTAL_CODE);
571 /* Submitter, Country Code */
572 g_ra_record(CP_COUNTRY_CODE).submitter_data := sbmtr_out_8;
573 g_ra_record(CP_COUNTRY_CODE).submitter_tag := ltr_ra_data_tag(CP_COUNTRY_CODE);
574 /* Contact Name */
575 g_ra_record(CONTACT_NAME).submitter_data := cnti_out_1;
576 g_ra_record(CONTACT_NAME).submitter_tag := ltr_ra_data_tag(CONTACT_NAME);
577 /* Contact Phone Number */
578 g_ra_record(CONTACT_PHONE_NUMBER).submitter_data := cnti_out_2;
579 g_ra_record(CONTACT_PHONE_NUMBER).submitter_tag := ltr_ra_data_tag(CONTACT_PHONE_NUMBER);
580 /* Contact Phone Extension */
581 g_ra_record(CONTACT_PHONE_EXTENSION).submitter_data := cnti_out_3;
582 g_ra_record(CONTACT_PHONE_EXTENSION).submitter_tag := ltr_ra_data_tag(CONTACT_PHONE_EXTENSION);
583 /* Contact E-Mail */
584 g_ra_record(CONTACT_EMAIL).submitter_data := cnti_out_4;
585 g_ra_record(CONTACT_EMAIL).submitter_tag := ltr_ra_data_tag(CONTACT_EMAIL);
586 /* Contact FAX */
587 g_ra_record(CONTACT_FAX).submitter_data := cnti_out_5;
588 g_ra_record(CONTACT_FAX).submitter_tag := ltr_ra_data_tag(CONTACT_FAX);
589 /* Perferred Method of Problem Notification Code */
590 g_ra_record(METHOD_OF_NOTIFICATION).submitter_data := ltr_ue_name_table(5).ue_value;
591 g_ra_record(METHOD_OF_NOTIFICATION).submitter_tag := ltr_ra_data_tag(METHOD_OF_NOTIFICATION);
592 /* Preparer Code */
593 g_ra_record(PREPARER_CODE).submitter_data := ltr_ue_name_table(6).ue_value;
594 g_ra_record(PREPARER_CODE).submitter_tag := ltr_ra_data_tag(PREPARER_CODE);
595
596 /*
597 Consturct XML Elements using all the RA record data items stored in the
598 '<?xml version="1.0" encoding="UTF-8" ?>'|| EOL ||
599 */
600 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
601 FROM dual;
602
603 l_final_xml_string := '<TRANSMITTER>'||EOL;
604
605 FOR I IN 1 .. g_ra_no_of_tag LOOP
606 l_final_xml_string := l_final_xml_string || '<'||g_ra_record(I).submitter_tag||'>'||
607 convert_special_char(g_ra_record(I).submitter_data)
608 ||'</'||g_ra_record(I).submitter_tag||'>'|| EOL;
609 -- HR_UTILITY_TRACE(l_final_xml_string);
610 END LOOP;
611 HR_UTILITY_TRACE(l_final_xml_string);
612 p_final_string := l_final_xml_string;
613 --pay_us_mmref_local_xml.write_to_magtape_lob(l_final_xml_string);
614 --pay_core_files.write_to_magtape_lob(l_final_xml_string);
615 --}
616 END populate_arch_transmitter; -- End of Procedure populate_arch_transmitter
617
618 --
619 -- This Procedue would be used to fetch all the Employer releated data for PSD Periodic Wage Listing reporting
620 -- All the data would then be populated into a global pl/sql based table for constructing XML
621 --
622
623 --
624 -- This procedure would be used to populate the Tag used for RE Data Record
625 --
626 PROCEDURE populate_re_data_tag
627 IS
628
629 i number;
630
631 BEGIN
632
633 ltr_re_data_tag(1) := 'ER_RECORD_IDENTIFIER';
634 ltr_re_data_tag(2) := 'ER_TAX_YEAR';
635 ltr_re_data_tag(3) := 'ER_TAX_QUARTER';
636 ltr_re_data_tag(4) := 'ER_EIN';
637 ltr_re_data_tag(5) := 'ER_TAX_ACCOUNT_NUMBER';
638 ltr_re_data_tag(6) := 'ER_TAX_TYPE';
639 ltr_re_data_tag(7) := 'ER_OTHER_EIN';
640 ltr_re_data_tag(8) := 'ER_NAME';
641 ltr_re_data_tag(9) := 'ER_ADDRESS';
642 ltr_re_data_tag(10) := 'ER_DELIVERY_ADDRESS';
643 ltr_re_data_tag(11) := 'ER_CITY';
644 ltr_re_data_tag(12) := 'ER_STATE_ABBREVIATION';
645 ltr_re_data_tag(13) := 'ER_ZIP_CODE';
646 ltr_re_data_tag(14) := 'ER_ZIP_CODE_EXTENSION';
647 ltr_re_data_tag(15) := 'ER_RECORD_CHANGE_INDICATOR';
648 ltr_re_data_tag(16) := 'ER_FOREIGN_STATE_PROVINCE';
649 ltr_re_data_tag(17) := 'ER_FOREIGN_POSTAL_CODE';
650 ltr_re_data_tag(18) := 'ER_COUNTRY_CODE';
651 ltr_re_data_tag(19) := 'ER_PERIOD_TYPE';
652 ltr_re_data_tag(20) := 'ER_PSD_CODE';
653 ltr_re_data_tag(21) := 'ER_MONTHLY_FILING_INDICATOR';
654 ltr_re_data_tag(22) := 'ER_REPORTING_PERIOD';
655 ltr_re_data_tag(23) := 'ER_TOTAL_SCHOOL_DST_ONLY_PAYMENTS';
656 ltr_re_data_tag(24) := 'ER_TOTAL_NUMBER_OF_EXEMPTIONS';
657 ltr_re_data_tag(25) := 'ER_LOCAL_ACCOUNT_NUMBER';
658 ltr_re_data_tag(26) := 'ER_PA_ACCOUNT_NUMBER';
659 ltr_re_data_tag(27) := 'ER_TCD_CODE';
660 ltr_re_data_tag(28) := 'ER_SUCCESS_PSD_CODE';
661 --
662 -- Following Loop structure used to debug the Tag Values
663 --
664 FOR I IN ltr_re_data_tag.first .. ltr_re_data_tag.last LOOP
665 HR_UTILITY_TRACE('Tag'||to_char(i)|| ' : '||ltr_re_data_tag(i));
666 END LOOP;
667
668 END populate_re_data_tag;
669
670 --
671 -- This Procedue would be used to fetch all the employer/Tax Unit related data
672 -- All the data would then be populated into a global pl/sql based table for constructing XML
673 --
674 PROCEDURE populate_arch_employer(
675 p_payroll_action_id NUMBER
676 ,p_tax_unit_id NUMBER
677 ,p_date_earned DATE
678 ,p_reporting_year VARCHAR2
679 ,p_jurisdiction_code VARCHAR2
680 ,p_state_code NUMBER
681 ,p_state_abbreviation VARCHAR2
682 ,status VARCHAR2
683 ,p_final_string OUT NOCOPY VARCHAR2
684 )
685 AS
686
687 CURSOR c_get_psd_data(psd_ue_name varchar2) IS
688 select fai.value
689 from ff_archive_item_contexts faic,
690 ff_contexts fc,
691 ff_archive_items fai,
692 ff_database_items fdi,
693 ff_contexts fc2,
694 ff_archive_item_contexts faic2
695 where fdi.user_name = psd_ue_name
696 and fdi.user_entity_id = fai.user_entity_id
697 and fai.context1 = p_payroll_action_id
698 and fc.context_name = 'TAX_UNIT_ID'
699 and fai.archive_item_id = faic.archive_item_id
700 and faic.context_id = fc.context_id
701 and faic.context = to_char(p_tax_unit_id)
702 and fc2.context_name = 'JURISDICTION_CODE'
703 and fai.archive_item_id = faic2.archive_item_id
704 and faic2.context_id = fc2.context_id
705 and faic2.context = to_char('39-000000-' ||pay_magtape_generic.get_parameter_value('ER_PSD_CODE'));
706
707
708 --
709 -- Declaration of Index Value that will be used for storing and fetching Employer level Data
710 -- from the global pl/sql table maintained for employer or Tax Unit level data
711 --
712
713 ER_RECORD_IDENTIFIER NUMBER := 1;
714 ER_TAX_YEAR NUMBER := 2;
715 ER_TAX_QUARTER NUMBER := 3;
716 ER_EIN NUMBER := 4;
717 ER_TAX_ACCOUNT_NUMBER NUMBER := 5;
718 ER_TAX_TYPE NUMBER := 6;
719 ER_OTHER_EIN NUMBER := 7;
720 ER_NAME NUMBER := 8;
721 ER_ADDRESS NUMBER := 9;
722 ER_DELIVERY_ADDRESS NUMBER := 10;
723 ER_CITY NUMBER := 11;
724 ER_STATE_ABBREVIATION NUMBER := 12;
725 ER_ZIP_CODE NUMBER := 13;
726 ER_ZIP_CODE_EXTENSION NUMBER := 14;
727 ER_RECORD_CHANGE_INDICATOR NUMBER := 15;
728 ER_FOREIGN_STATE_PROVINCE NUMBER := 16;
729 ER_FOREIGN_POSTAL_CODE NUMBER := 17;
730 ER_COUNTRY_CODE NUMBER := 18;
731 ER_PERIOD_TYPE NUMBER := 19;
732 ER_PSD_CODE NUMBER := 20;
733 ER_MONTHLY_FILING_INDICATOR NUMBER := 21;
734 ER_REPORTING_PERIOD NUMBER := 22;
735 ER_TOTAL_SCH_DST_ONLY_PAYMTS NUMBER := 23;
736 ER_TOTAL_NUMBER_OF_EXEMPTIONS NUMBER := 24;
737 ER_LOCAL_ACCOUNT_NUMBER NUMBER := 25;
738 ER_PA_ACCOUNT_NUMBER NUMBER := 26;
739 ER_TCD_CODE NUMBER :=27;
740 ER_SUCCESS_PSD_CODE NUMBER :=28;
741 --
742 -- Local Variables required for Employer/Contact person Data
743 --
744 l_payroll_action_id number;
745 l_assignment_id number;
746 l_date_earned date;
747 l_tax_unit_id number;
748 l_input_report_type varchar2(200) := 'W2';
749 l_input_report_type_format varchar2(200) := 'MMREF';
750 l_input_record_name varchar2(200) := 'RE';
751 l_effective_date varchar2(200);
752 l_item_name varchar2(200);
753 l_input_report_qualifier varchar2(200);
754 l_quarter varchar2(100);
755 l_period_type varchar2(100);
756 l_reporting_period varchar2(100);
757
758 /* Local Variables for Company Information */
759 input_empr_name varchar2(200);
760 input_empr_1 varchar2(240); -- Bug# 14286448 Gre length issue
761 input_empr_2 varchar2(200) := ' ';
762 input_empr_3 varchar2(200) := ' ';
763 input_empr_4 varchar2(200) := ' ';
764 input_empr_5 varchar2(200) := ' ';
765 empr_validate_flag varchar2(200) := 'Y';
766 empr_exclude_output_flag varchar2(200) := 'N';
767 empr_out_1 varchar2(200) := ' ';
768 empr_out_2 varchar2(200) := ' ';
769 empr_out_3 varchar2(200) := ' ';
770 empr_out_4 varchar2(200) := ' ';
771 empr_out_5 varchar2(200) := ' ';
772 empr_out_6 varchar2(200) := ' ';
773 empr_out_7 varchar2(200) := ' ';
774 empr_out_8 varchar2(200) := ' ';
775 empr_out_9 varchar2(200) := ' ';
776 empr_out_10 varchar2(240) := ' '; -- Bug# 16008268 Gre length issue
777
778 i number :=0;
779 j number :=0;
780 --PL Table used for storing and manipulating DataBaseItem used for
781 -- Submitter level information
782 TYPE re_ue_rec IS RECORD (
783 ue_name varchar2(200),
784 ue_value varchar2(240) -- Bug# 14286448 Gre length issue
785 );
786 TYPE re_ue_record IS TABLE OF re_ue_rec
787 INDEX BY BINARY_INTEGER;
788 ltr_ue_name_table re_ue_record;
789 l_number_re_dbi number := 6;
790
791 employer_data varchar2(200);
792
793 -- SRS Parameter
794
795 l_srs_trns_pin varchar2(200);
796 l_srs_trns_tlcn varchar2(200);
797 l_srs_resub_indicator varchar2(200);
798
799 -- Derived Local Variables
800 l_resub_indicator varchar2(200);
801 l_reporting_date varchar2(200);
802
803 l_err boolean := FALSE;
804 l_validate varchar2(100);
805 l_validated_EIN varchar2(200);
806
807 l_final_xml_string VARCHAR2(32767);
808
809 EOL VARCHAR2(10);
810
811 /* End of Variable Declarations */
812
813
814
815 BEGIN
816 --{
817 --
818 -- Fetch all Context or Parameters set at the Transmitter Cursor
819 --
820 --l_tax_unit_id := pay_magtape_generic.get_parameter_value('TAX_UNIT_ID');
821 --l_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
822 --l_date_earned := pay_magtape_generic.get_parameter_value('DATE_EARNED');
823 --l_reporting_date := '31-DEC-'||pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
824
825 l_tax_unit_id := p_tax_unit_id;
826 l_tax_unit_id := pay_magtape_generic.get_parameter_value('TAX_UNIT_ID');
827 l_payroll_action_id := p_payroll_action_id;
828 l_date_earned := p_date_earned;
829 l_effective_date := l_date_earned;
830 l_reporting_date :=l_effective_date;
831 l_quarter := to_char(l_date_earned,'Q');
832 l_reporting_period :=p_reporting_year||to_char(l_date_earned,'MM');
833 --Commented for the Bug 14799833
834 /*select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
835 'TRANSFER_PERIOD_TYPE') into l_period_type
836 from pay_payroll_Actions ppa where payroll_action_id= p_payroll_action_id;*/
837
838 select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),
839 'TRANSFER_PERIOD_TYPE') into l_period_type
840 from dual;
841
842 --Added for the Bug 14799833
843 if l_period_type = 'Yearly' then
844 l_reporting_period := p_reporting_year||'12';
845 end if;
846
847 --
848 -- Fetch SRS Parameter
849 --
850 l_srs_trns_pin := pay_magtape_generic.get_parameter_value('TRNS_PIN');
851 l_srs_trns_tlcn := pay_magtape_generic.get_parameter_value('TRNS_TLCN');
852
853 l_input_report_qualifier := pay_magtape_generic.get_parameter_value('TRANSFER_STATE');
854
855 i := 0;
856 --
857 -- Fetch Archived Values of various DBIs used in submitter Record
858 --
859 i := i +1;
860 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER'; --1
861 i := i +1;
862 ltr_ue_name_table(i).ue_name := 'A_LC_W2_REPORTING_RULES_ORG_OTHER_EIN';--2
863 i := i +1;
864 ltr_ue_name_table(i).ue_name := 'A_TAX_UNIT_NAME';--3
865 i := i +1;
866 ltr_ue_name_table(i).ue_name := 'A_PSD_REPORTING_RULES_ORG_EMPLOYER_PSD_CODE';--4
867 i := i +1;
868 ltr_ue_name_table(i).ue_name := 'A_PSD_REPORTING_RULES_ORG_EMPLOYER_TAX_ACCOUNT_NUMBER';--5
869
870
871
872 if ltr_ue_name_table.count > 0 then
873 for j in ltr_ue_name_table.first .. ltr_ue_name_table.last
874 loop
875 HR_UTILITY_TRACE('ER_PSD_CODE: '||pay_magtape_generic.get_parameter_value('ER_PSD_CODE'));
876 if(ltr_ue_name_table(j).ue_name in ('A_PSD_REPORTING_RULES_ORG_EMPLOYER_PSD_CODE',
877 'A_PSD_REPORTING_RULES_ORG_EMPLOYER_TAX_ACCOUNT_NUMBER')) then
878 HR_UTILITY_TRACE('ER_PSD_CODE:'||pay_magtape_generic.get_parameter_value('ER_PSD_CODE'));
879 begin
880 open c_get_psd_data(ltr_ue_name_table(j).ue_name);
881 fetch c_get_psd_data into ltr_ue_name_table(j).ue_value ;
882
883 close c_get_psd_data;
884 exception when no_data_found then
885 hr_utility_trace ('populate_arch_employer: getting PSd Reporting Rules (ER) archvied values '||SQLERRM);
886 end;
887 else
888 ltr_ue_name_table(j).ue_value := hr_us_w2_rep.get_w2_tax_unit_item (l_tax_unit_id,
889 l_payroll_action_id,
890 ltr_ue_name_table(j).ue_name);
891 end if;
892 end loop;
893 end if;
894
895 /* ==================================================
896 Get Employer or transmitter Company Information releated Submitter
897 ================================================== */
898
899 input_empr_name := 'ER_ADDRESS';
900 input_empr_1 := ltr_ue_name_table(3).ue_value;
901 employer_data := pay_us_reporting_utils_pkg.get_item_data(
902 NULL, -- Assignment Id (Not Used at RE record)
903 l_date_earned,
904 l_tax_unit_id,
905 l_reporting_date,
906 input_empr_name,
907 l_input_report_type,
908 l_input_report_type_format,
909 l_input_report_qualifier,
910 l_input_record_name,
911 input_empr_1,
912 input_empr_2,
913 input_empr_3,
914 input_empr_4,
915 input_empr_5,
916 empr_validate_flag,
917 empr_exclude_output_flag,
918 empr_out_1,
919 empr_out_2,
920 empr_out_3,
921 empr_out_4,
922 empr_out_5,
923 empr_out_6,
924 empr_out_7,
925 empr_out_8,
926 empr_out_9,
927 empr_out_10);
928
929 --
930 -- Pouplate All the Tags to be used for RE record Data Items
931 --
932
933 pay_us_psd_generic_extract.populate_re_data_tag;
934
935 /* Initialize with Default Value */
936
937
938 FOR I IN ltr_re_data_tag.first .. ltr_re_data_tag.last
939 LOOP
940 g_re_record(I).employer_data := ' ';
941 g_re_record(I).employer_tag := ltr_re_data_tag(I);
942 END LOOP;
943 /* Record Identifier */
944 g_re_record(ER_RECORD_IDENTIFIER).employer_data := 'RE';
945 g_re_record(ER_RECORD_IDENTIFIER).employer_tag := ltr_re_data_tag(ER_RECORD_IDENTIFIER);
946 /* Tax Year */
947 g_re_record(ER_TAX_YEAR).employer_data := p_reporting_year;
948 g_re_record(ER_TAX_YEAR).employer_tag := ltr_re_data_tag(ER_TAX_YEAR);
949 /* Tax Quarter */
950 g_re_record(ER_TAX_QUARTER).employer_data := l_quarter;
951 g_re_record(ER_TAX_QUARTER).employer_tag := ltr_re_data_tag(ER_TAX_QUARTER);
952 /* Agent Indicator Code
953 Employer - Agent(EIN)
954 Agent for EIN */
955 --
956 -- EIN Validation
957 --
958 l_validated_EIN :=
959 pay_us_report_data_validation.validate_W2_EIN( 'FED',
960 l_input_record_name,
961 ltr_ue_name_table(1).ue_value,
962 empr_out_10,
963 empr_validate_flag,
964 l_err
965 );
966 /* If Employer is 2678 Filer Agent EIN is reported otherwise Not */
967 g_re_record(ER_EIN).employer_data := l_validated_EIN;
968 g_re_record(ER_EIN).employer_tag := ltr_re_data_tag(ER_EIN);
969 /*Employer Tax Account Number*/
970 g_re_record(ER_TAX_ACCOUNT_NUMBER).employer_data := ltr_ue_name_table(5).ue_value ;
971 g_re_record(ER_TAX_ACCOUNT_NUMBER).employer_tag := ltr_re_data_tag(ER_TAX_ACCOUNT_NUMBER);
972 /* Tax Type: E = EIT; L = LST or W = W-2 ; Defaulted to 'E'*/
973
974 IF l_period_type <> 'Yearly' then --Added for the Bug 14799833
975 g_re_record(ER_TAX_TYPE).employer_data := 'E';
976 g_re_record(ER_TAX_TYPE).employer_tag := ltr_re_data_tag(ER_TAX_TYPE);
977 Else
978 g_re_record(ER_TAX_TYPE).employer_data := 'W';
979 g_re_record(ER_TAX_TYPE).employer_tag := ltr_re_data_tag(ER_TAX_TYPE);
980 End if;
981 /* Other EIN */
982 g_re_record(ER_OTHER_EIN).employer_data :=ltr_ue_name_table(2).ue_value;
983 g_re_record(ER_OTHER_EIN).employer_tag := ltr_re_data_tag(ER_OTHER_EIN);
984 /* Employer Name */
985 g_re_record(ER_NAME).employer_data :=ltr_ue_name_table(3).ue_value;
986 g_re_record(ER_NAME).employer_tag := ltr_re_data_tag(ER_NAME);
987 /* Employer, Location Address */
988 g_re_record(ER_ADDRESS).employer_data :=empr_out_1;
989 g_re_record(ER_ADDRESS).employer_tag := ltr_re_data_tag(ER_ADDRESS);
990 /* Employer, Delivery Address */
991 g_re_record(ER_DELIVERY_ADDRESS).employer_data := empr_out_2;
992 g_re_record(ER_DELIVERY_ADDRESS).employer_tag := ltr_re_data_tag(ER_DELIVERY_ADDRESS);
993 /* Employer, City */
994 g_re_record(ER_CITY).employer_data := empr_out_3;
995 g_re_record(ER_CITY).employer_tag := ltr_re_data_tag(ER_CITY);
996 /* Employer, State Abbreviation */
997 g_re_record(ER_STATE_ABBREVIATION).employer_data := empr_out_4;
998 g_re_record(ER_STATE_ABBREVIATION).employer_tag := ltr_re_data_tag(ER_STATE_ABBREVIATION);
999 /* Employer, Zip Code */
1000 g_re_record(ER_ZIP_CODE).employer_data := empr_out_5;
1001 g_re_record(ER_ZIP_CODE).employer_tag := ltr_re_data_tag(ER_ZIP_CODE);
1002 /* Employer, Zip Code Extension */
1003 g_re_record(ER_ZIP_CODE_EXTENSION).employer_data := empr_out_6;
1004 g_re_record(ER_ZIP_CODE_EXTENSION).employer_tag := ltr_re_data_tag(ER_ZIP_CODE_EXTENSION);
1005 /*Record Change Indicator or Notice*/
1006 g_re_record(ER_RECORD_CHANGE_INDICATOR).employer_data := '0';
1007 g_re_record(ER_RECORD_CHANGE_INDICATOR).employer_tag := ltr_re_data_tag(ER_RECORD_CHANGE_INDICATOR);
1008 /* Employer, Foriegn State or Province */
1009 g_re_record(ER_FOREIGN_STATE_PROVINCE).employer_data := empr_out_7;
1010 g_re_record(ER_FOREIGN_STATE_PROVINCE).employer_tag := ltr_re_data_tag(ER_FOREIGN_STATE_PROVINCE);
1011 /* Employer, Foriegn Postal Code */
1012 g_re_record(ER_FOREIGN_POSTAL_CODE).employer_data := empr_out_8;
1013 g_re_record(ER_FOREIGN_POSTAL_CODE).employer_tag := ltr_re_data_tag(ER_FOREIGN_POSTAL_CODE);
1014 /* Employer, Country Code */
1015 g_re_record(ER_COUNTRY_CODE).employer_data := empr_out_9;
1016 g_re_record(ER_COUNTRY_CODE).employer_tag := ltr_re_data_tag(ER_COUNTRY_CODE);
1017 /*Period Type: M for Monthly, Q for Quarterly, Y for Yearend Reconciliation*/
1018
1019 if (l_period_type='Monthly') then
1020 g_re_record(ER_PERIOD_TYPE).employer_data := 'M';
1021 g_re_record(ER_PERIOD_TYPE).employer_tag := ltr_re_data_tag(ER_PERIOD_TYPE);
1022 elsif (l_period_type='Quarterly') then
1023 g_re_record(ER_PERIOD_TYPE).employer_data := 'Q';
1024 g_re_record(ER_PERIOD_TYPE).employer_tag := ltr_re_data_tag(ER_PERIOD_TYPE);
1025 else
1026 g_re_record(ER_PERIOD_TYPE).employer_data := 'Y';
1027 g_re_record(ER_PERIOD_TYPE).employer_tag := ltr_re_data_tag(ER_PERIOD_TYPE);
1028 end if;
1029
1030 /*Employer PSD Code*/
1031 -- If the employee is working at home, then we are getting the value of the Employee Work PSD code
1032 --is being used as the Employer PSD code.
1033 if (ltr_ue_name_table(4).ue_value is null) then
1034
1035 g_re_record(ER_PSD_CODE).employer_data := pay_magtape_generic.get_parameter_value('ER_PSD_CODE');
1036 g_re_record(ER_PSD_CODE).employer_tag := ltr_re_data_tag(ER_PSD_CODE);
1037 else
1038
1039 g_re_record(ER_PSD_CODE).employer_data := ltr_ue_name_table(4).ue_value ;
1040 g_re_record(ER_PSD_CODE).employer_tag := ltr_re_data_tag(ER_PSD_CODE);
1041 end if;
1042 /*Monthly Filing Indicator: "M" if filing Monthly, "Q" if filing Quarterly*/
1043
1044 if (l_period_type='Monthly') then
1045 g_re_record(ER_MONTHLY_FILING_INDICATOR).employer_data := 'M';
1046 g_re_record(ER_MONTHLY_FILING_INDICATOR).employer_tag := ltr_re_data_tag(ER_MONTHLY_FILING_INDICATOR);
1047 elsif (l_period_type='Quarterly') then
1048 g_re_record(ER_MONTHLY_FILING_INDICATOR).employer_data := 'Q';
1049 g_re_record(ER_MONTHLY_FILING_INDICATOR).employer_tag := ltr_re_data_tag(ER_MONTHLY_FILING_INDICATOR);
1050 else
1051 g_re_record(ER_MONTHLY_FILING_INDICATOR).employer_data := ' ';--Modified for the Bug 15960706
1052 g_re_record(ER_MONTHLY_FILING_INDICATOR).employer_tag := ltr_re_data_tag(ER_MONTHLY_FILING_INDICATOR);
1053 end if;
1054
1055 /*Reporting Period*/
1056 g_re_record(ER_REPORTING_PERIOD).employer_data := l_reporting_period ;
1057 g_re_record(ER_REPORTING_PERIOD).employer_tag := ltr_re_data_tag(ER_REPORTING_PERIOD);
1058
1059 /*Employer Total School District Only Payments*/
1060 g_re_record(ER_TOTAL_SCH_DST_ONLY_PAYMTS).employer_data := ' ' ;
1061 g_re_record(ER_TOTAL_SCH_DST_ONLY_PAYMTS).employer_tag := ltr_re_data_tag(ER_TOTAL_SCH_DST_ONLY_PAYMTS);
1062
1063 /*Employer Total Number of Exemptions*/
1064 g_re_record(ER_TOTAL_NUMBER_OF_EXEMPTIONS).employer_data :=' ';
1065 g_re_record(ER_TOTAL_NUMBER_OF_EXEMPTIONS).employer_tag := ltr_re_data_tag(ER_TOTAL_NUMBER_OF_EXEMPTIONS);
1066
1067 /*Local Account Number*/
1068 g_re_record(ER_LOCAL_ACCOUNT_NUMBER).employer_data := ' ';
1069 g_re_record(ER_LOCAL_ACCOUNT_NUMBER).employer_tag := ltr_re_data_tag(ER_LOCAL_ACCOUNT_NUMBER);
1070
1071 /*PA eight-digit Account Number (as displayed on each payment coupon)*/
1072 g_re_record(ER_PA_ACCOUNT_NUMBER).employer_data := ' ';
1073 g_re_record(ER_PA_ACCOUNT_NUMBER).employer_tag := ltr_re_data_tag(ER_PA_ACCOUNT_NUMBER);
1074
1075 /*Employer Tax Collection District Code*/
1076 g_re_record(ER_TCD_CODE).employer_data := substr(g_re_record(ER_PSD_CODE).employer_data,1,2);
1077 g_re_record(ER_TCD_CODE).employer_tag := ltr_re_data_tag(ER_TCD_CODE);
1078
1079 /*Success||Employer PSD Code--Added this tag to compare it with EE_EXCEPTION_WORK_PSD (for successful employees)in templates*/
1080 g_re_record(ER_SUCCESS_PSD_CODE).employer_data := 'SUCCESS'||g_re_record(ER_PSD_CODE).employer_data;
1081 g_re_record(ER_SUCCESS_PSD_CODE).employer_tag := ltr_re_data_tag(ER_SUCCESS_PSD_CODE);
1082
1083 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
1084 FROM dual;
1085 l_final_xml_string := '<EMPLOYER>'||EOL;
1086
1087 FOR I IN 1 .. g_re_no_of_tag LOOP
1088 l_final_xml_string := l_final_xml_string || '<'||g_re_record(I).employer_tag||'>'||
1089 convert_special_char(g_re_record(I).employer_data)
1090 ||'</'||g_re_record(I).employer_tag||'>'|| EOL;
1091 HR_UTILITY_TRACE(l_final_xml_string);
1092 END LOOP;
1093 HR_UTILITY_TRACE(l_final_xml_string);
1094 p_final_string := l_final_xml_string;
1095 --pay_us_mmref_local_xml.write_to_magtape_lob(l_final_xml_string);
1096 --pay_core_files.write_to_magtape_lob(l_final_xml_string);
1097 --}
1098 END populate_arch_employer; -- End of Procedure populate_arch_employer
1099
1100
1101 --
1102 -- This procedure would be used to populate the Tag used for RS Data Record
1103 --
1104 PROCEDURE populate_ee_data_tag
1105 IS
1106 i number;
1107 BEGIN
1108
1109 ltr_ee_data_tag(1) := 'RS_RECORD_IDENTIFIER';
1110 ltr_ee_data_tag(2) := 'EE_STATE_CODE';
1111 ltr_ee_data_tag(3) := 'EE_TAXING_ENTITY_CODE';
1112 ltr_ee_data_tag(4) := 'EE_SSN';
1113 ltr_ee_data_tag(5) := 'EE_FIRST_NAME';
1114 ltr_ee_data_tag(6) := 'EE_MIDDLE_INITIAL';
1115 ltr_ee_data_tag(7) := 'EE_LAST_NAME';
1116 ltr_ee_data_tag(8) := 'EE_SUFFIX';
1117 ltr_ee_data_tag(9) := 'EE_LOCATION_ADDRESS';
1118 ltr_ee_data_tag(10) := 'EE_DELIVERY_ADDRESS';
1119 ltr_ee_data_tag(11) := 'EE_CITY';
1120 ltr_ee_data_tag(12) := 'EE_STATE_ABBREVIATION';
1121 ltr_ee_data_tag(13) := 'EE_ZIP_CODE';
1122 ltr_ee_data_tag(14) := 'EE_ZIP_CODE_EXTENSION';
1123 ltr_ee_data_tag(15) := 'EE_FOREIGN_STATE_PROVINCE';
1124 ltr_ee_data_tag(16) := 'EE_FOREIGN_POSTAL_CODE';
1125 ltr_ee_data_tag(17) := 'EE_COUNTRY_CODE';
1126 ltr_ee_data_tag(18) := 'EE_RECORD_CHANGE_NOTICE';
1127 ltr_ee_data_tag(19) := 'EE_REPORTING_PERIOD';
1128 ltr_ee_data_tag(20) := 'EE_STATE_CODE2';
1129 ltr_ee_data_tag(21) := 'EE_STATE_WAGES';
1130 ltr_ee_data_tag(22) := 'EE_SIT_WITHHELD';
1131 ltr_ee_data_tag(23) := 'EE_LOCAL_TAX_TYPE';
1132 ltr_ee_data_tag(24) := 'EE_LOCAL_TAXABLE_WAGES';
1133 ltr_ee_data_tag(25) := 'EE_LOCAL_INCOME_TAX_WITHHELD';
1134 ltr_ee_data_tag(26) := 'EE_STATE_CONTROL_NUMBER';
1135 ltr_ee_data_tag(27) := 'EE_RESIDENCE_PSD_CODE';
1136 ltr_ee_data_tag(28) := 'EE_SCHOOL_DST_ONLY_PAYMENT';
1137 ltr_ee_data_tag(29) := 'EE_WORK_PSD_CODE';
1138 ltr_ee_data_tag(30) := 'EE_LOCAL_TAX_TYPE_R_NR';
1139 ltr_ee_data_tag(31) := 'EE_PSD_SUBJ_WHABLE_MONTH';
1140 ltr_ee_data_tag(32) := 'EE_PSD_WITHHELD_MONTH';
1141 ltr_ee_data_tag(33) := 'EE_PSD_SUBJ_WHABLE_QTD';
1142 ltr_ee_data_tag(34) := 'EE_PSD_WITHHELD_QTD';
1143 ltr_ee_data_tag(35) := 'EE_CITY_PSD_SUBJ_WHABLE_PER_JD_GRE_MONTH';
1144 ltr_ee_data_tag(36) := 'EE_CITY_PSD_WITHHELD_PER_JD_GRE_MONTH';
1145 ltr_ee_data_tag(37) := 'EE_SCHOOL_PSD_WITHHELD_PER_JD_GRE_MONTH';
1146 ltr_ee_data_tag(38) := 'EE_CITY_PSD_SUBJ_WHABLE_PER_JD_GRE_QTD';
1147 ltr_ee_data_tag(39) := 'EE_CITY_PSD_WITHHELD_PER_JD_GRE_QTD';
1148 ltr_ee_data_tag(40) := 'EE_SCHOOL_PSD_WITHHELD_PER_JD_GRE_QTD';
1149 ltr_ee_data_tag(41) := 'EE_WORK_AT_HOME';
1150 ltr_ee_data_tag(42) := 'EE_TCD_CODE';
1151 ltr_ee_data_tag(43) := 'EE_EXCEPTION_TCD_CODE';
1152 ltr_ee_data_tag(44) := 'EE_EMPLOYEE_NUMBER';
1153 ltr_ee_data_tag(45) := 'EE_TAX_YEAR';
1154 ltr_ee_data_tag(46) := 'EE_TAX_QUARTER';
1155 ltr_ee_data_tag(47) := 'EE_WAH_PSD_CODE_EXISTS';
1156 ltr_ee_data_tag(48) := 'EE_EXCEPTION_WORK_PSD';
1157 --Added for Bug 14799833
1158 ltr_ee_data_tag(49) := 'EE_PSD_SUBJ_WHABLE_YTD';
1159 ltr_ee_data_tag(50) := 'EE_PSD_WITHHELD_YTD';
1160
1161 --
1162 -- Following Loop structure used to debug the Tag Values
1163 --
1164 FOR I IN ltr_ee_data_tag.first .. ltr_ee_data_tag.last
1165 LOOP
1166 HR_UTILITY_TRACE('Tag'||to_char(i)|| ' : '|| ltr_ee_data_tag(i));
1167 END LOOP;
1168 END populate_ee_data_tag;
1169 --
1170 -- This Procedue would be used to fetch all the employee related data
1171 -- All the data would then be populated into a global pl/sql based table for
1172 -- constructing XML
1173 --
1174 PROCEDURE populate_arch_employee(
1175 p_payroll_action_id NUMBER
1176 ,p_assignment_action_id NUMBER
1177 ,p_tax_unit_id NUMBER
1178 ,p_assignment_id NUMBER
1179 ,p_date_earned DATE
1180 ,p_reporting_year VARCHAR2
1181 ,p_jurisdiction_code VARCHAR2
1182 ,p_state_code NUMBER
1183 ,p_state_abbreviation VARCHAR2
1184 ,status VARCHAR2
1185 ,p_final_string OUT NOCOPY VARCHAR2
1186 )
1187 AS
1188
1189
1190 CURSOR c_check_psd_code_for_gre(psd_code varchar2, tax_unit_id number) IS
1191 SELECT 'Y'
1192 FROM hr_organization_information hoi
1193 WHERE hoi.organization_id = tax_unit_id AND
1194 hoi.org_information_context = 'PSD Reporting Rules' AND
1195 substr(hoi.org_information10,1,6)=psd_code;
1196
1197
1198
1199 CURSOR c_get_wah_flag(l_assignment_action_id number, l_effective_date date) IS
1200 SELECT paf.work_at_home
1201 FROM per_all_assignments_f paf,
1202 pay_assignment_actions paa
1203 WHERE paa.assignment_action_id= l_assignment_action_id
1204 AND paa.assignment_id = paf.assignment_id
1205 AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
1206
1207 --Added for Bug 14799833
1208 CURSOR c_get_ytd_values(l_assignment_action_id number,l_jd_code varchar2) IS
1209 select W2_LOCAL_WAGES,
1210 W2_LOCAL_INCOME_TAX
1211 from PAY_US_LOCALITY_W2_V v
1212 where v.assignment_action_id = l_assignment_action_id
1213 and v.JURISDICTION = l_jd_code;
1214 --{
1215 --
1216 -- Declaration of Index Value that will be used for storing and fetching Employer level Data
1217 -- from the global pl/sql table maintained for employer or Tax Unit level data
1218 --
1219
1220 RS_RECORD_IDENTIFIER NUMBER :=1;
1221 EE_STATE_CODE NUMBER :=2;
1222 EE_TAXING_ENTITY_CODE NUMBER :=3;
1223 EE_SSN NUMBER :=4;
1224 EE_FIRST_NAME NUMBER :=5;
1225 EE_MIDDLE_INITIAL NUMBER :=6;
1226 EE_LAST_NAME NUMBER :=7;
1227 EE_SUFFIX NUMBER :=8;
1228 EE_LOCATION_ADDRESS NUMBER :=9;
1229 EE_DELIVERY_ADDRESS NUMBER :=10;
1230 EE_CITY NUMBER :=11;
1231 EE_STATE_ABBREVIATION NUMBER :=12;
1232 EE_ZIP_CODE NUMBER :=13;
1233 EE_ZIP_CODE_EXTENSION NUMBER :=14;
1234 EE_FOREIGN_STATE_PROVINCE NUMBER :=15;
1235 EE_FOREIGN_POSTAL_CODE NUMBER :=16;
1236 EE_COUNTRY_CODE NUMBER :=17;
1237 EE_RECORD_CHANGE_NOTICE NUMBER :=18;
1238 EE_REPORTING_PERIOD NUMBER :=19;
1239 EE_STATE_CODE2 NUMBER :=20;
1240 EE_STATE_WAGES NUMBER :=21;
1241 EE_SIT_WITHHELD NUMBER :=22;
1242 EE_LOCAL_TAX_TYPE NUMBER :=23;
1243 EE_LOCAL_TAXABLE_WAGES NUMBER :=24;
1244 EE_LOCAL_INCOME_TAX_WITHHELD NUMBER :=25;
1245 EE_STATE_CONTROL_NUMBER NUMBER :=26;
1246 EE_RESIDENCE_PSD_CODE NUMBER :=27;
1247 EE_SCHOOL_DST_ONLY_PAYMENT NUMBER :=28;
1248 EE_WORK_PSD_CODE NUMBER :=29;
1249 EE_LOCAL_TAX_TYPE_R_NR NUMBER :=30;
1250 EE_PSD_SUBJ_WHABLE_MONTH NUMBER :=31;
1251 EE_PSD_WITHHELD_MONTH NUMBER :=32;
1252 EE_PSD_SUBJ_WHABLE_QTD NUMBER :=33;
1253 EE_PSD_WITHHELD_QTD NUMBER :=34;
1254 EE_CITY_PSD_SUBJ_WHABLE_MONTH NUMBER :=35;
1255 EE_CITY_PSD_WITHHELD_MONTH NUMBER :=36;
1256 EE_SCHOOL_PSD_WITHHELD_MONTH NUMBER :=37;
1257 EE_CITY_PSD_SUBJ_WHABLE_QTD NUMBER :=38;
1258 EE_CITY_PSD_WITHHELD_QTD NUMBER :=39;
1259 EE_SCHOOL_PSD_WITHHELD_QTD NUMBER :=40;
1260 EE_WORK_AT_HOME NUMBER :=41;
1261 EE_TCD_CODE NUMBER :=42;
1262 EE_EXCEPTION_TCD_CODE NUMBER:=43;
1263 EE_EMPLOYEE_NUMBER NUMBER:=44;
1264 EE_TAX_YEAR NUMBER:=45;
1265 EE_TAX_QUARTER NUMBER:=46;
1266 EE_WAH_PSD_CODE_EXISTS NUMBER:=47;
1267 EE_EXCEPTION_WORK_PSD NUMBER:=48;
1268
1269 --Added for Bug 14799833
1270
1271 EE_PSD_SUBJ_WHABLE_YTD NUMBER :=49;
1272 EE_PSD_WITHHELD_YTD NUMBER :=50;
1273 --
1274 -- Local Variables required for Employee
1275 --
1276 l_payroll_action_id NUMBER;
1277 l_assignment_id NUMBER;
1278 l_date_earned DATE;
1279 l_tax_unit_id NUMBER;
1280 l_period_type varchar2(100);
1281 l_ee_work_psd_code varchar2(100);
1282 l_ee_residence_psd_code varchar2(100);
1283 l_quarter varchar2(100);
1284 l_wah_flag Varchar2(30);
1285 l_psd_code_exists Varchar2(30);
1286 l_wah_psd_code_exists Varchar2(30);
1287
1288 /* Local Variables for EMPLOYEE INFORMATION Input */
1289 input_empe_name VARCHAR2(200) := 'EE_ADDRESS';
1290 input_empe_1 VARCHAR2(200) := ' ';
1291 input_empe_2 VARCHAR2(200) := ' ';
1292 input_empe_3 VARCHAR2(200) := ' ';
1293 input_empe_4 VARCHAR2(200) := ' ';
1294 input_empe_5 VARCHAR2(200) := ' ';
1295 input_empe_validate_flag VARCHAR2(200) := 'Y';
1296 /* Local Variables for EMPLOYEE INFORMATION Output */
1297 empe_exclude_output_flag VARCHAR2(200) := 'N';
1298 empe_out_1 VARCHAR2(200) := ' ';
1299 empe_out_2 VARCHAR2(200) := ' ';
1300 empe_out_3 VARCHAR2(200) := ' ';
1301 empe_out_4 VARCHAR2(200) := ' ';
1302 empe_out_5 VARCHAR2(200) := ' ';
1303 empe_out_6 VARCHAR2(200) := ' ';
1304 empe_out_7 VARCHAR2(200) := ' ';
1305 empe_out_8 VARCHAR2(200) := ' ';
1306 empe_out_9 VARCHAR2(200) := ' ';
1307 empe_out_10 VARCHAR2(200) := ' ';
1308
1309 l_input_report_type VARCHAR2(200) := 'W2';
1310 l_input_report_type_format VARCHAR2(200) := 'MMREF';
1311 l_input_record_name VARCHAR2(200) := 'RW';
1312 l_effective_date VARCHAR2(200);
1313 l_item_name VARCHAR2(200);
1314 l_input_report_qualifier VARCHAR2(200);
1315 i NUMBER :=0;
1316 j NUMBER :=0;
1317 --PL Table used for storing and manipulating DataBaseItem used for
1318 -- Submitter level information
1319 TYPE ee_ue_rec IS RECORD (
1320 ue_name varchar2(200),
1321 ue_data_level varchar2(200),
1322 ue_value varchar2(200),
1323 data_type varchar2(200),
1324 mandatory varchar2(200),
1325 negative_check varchar2(200)
1326 );
1327 TYPE ee_ue_record IS TABLE OF ee_ue_rec
1328 INDEX BY BINARY_INTEGER;
1329 ltr_ue_name_table ee_ue_record;
1330
1331 l_number_ee_dbi NUMBER := 6;
1332
1333 employee_data VARCHAR2(200);
1334
1335 -- SRS Parameter
1336
1337 l_srs_trns_pin VARCHAR2(200);
1338 l_srs_trns_tlcn VARCHAR2(200);
1339 l_srs_resub_indicator VARCHAR2(200);
1340
1341 -- Derived Local Variables
1342 l_resub_indicator VARCHAR2(200);
1343 l_reporting_date VARCHAR2(200);
1344 l_reporting_period VARCHAR2(200);
1345
1346 l_err BOOLEAN := FALSE;
1347 l_validate VARCHAR2(100);
1348 l_validated_EIN VARCHAR2(200);
1349
1350 l_final_xml_string VARCHAR2(32767);
1351 l_data_item_xml VARCHAR2(32767);
1352
1353 EOL VARCHAR2(10);
1354 l_status VARCHAR2(200) := 'SUCCESS';
1355 l_status_temp VARCHAR2(200) := 'SUCCESS';
1356 l_status_description VARCHAR2(1500) ;
1357 l_ovrde_skip_status_f boolean := FALSE; --Added for the Bug 14799833
1358
1359 /* RS Record Local Varialbes */
1360 l_state_wages NUMBER := 0;
1361
1362 k NUMBER := 0;
1363 /* End of Variable Declarations */
1364 BEGIN
1365 --{
1366 --
1367 -- Fetch all Context or Parameters set at the Transmitter Cursor
1368 --
1369
1370 l_tax_unit_id := p_tax_unit_id;
1371 l_payroll_action_id := p_payroll_action_id;
1372 l_date_earned := p_date_earned;
1373 --l_reporting_date := '31-DEC-'||p_reporting_year;
1374 l_effective_date := l_date_earned;
1375
1376 l_reporting_date :=l_effective_date;
1377 l_quarter := to_char(l_date_earned,'Q');
1378 l_reporting_period := to_char(l_date_earned,'MM')|| p_reporting_year;
1379 --Commented for the Bug 14799833
1380 /*select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
1381 'TRANSFER_PERIOD_TYPE') into l_period_type
1382 from pay_payroll_Actions ppa where payroll_action_id= p_payroll_action_id;*/
1383
1384
1385 select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),
1386 'TRANSFER_PERIOD_TYPE') into l_period_type
1387 from dual;
1388
1389 --Added for the Bug 14799833--Modified for the Bug 15960706
1390 if l_period_type = 'Yearly' then
1391 l_reporting_period := '12'||p_reporting_year;
1392 end if;
1393
1394 select substr( p_jurisdiction_code, 4,6) into l_ee_residence_psd_code from dual;
1395 select substr( p_jurisdiction_code, 11,6) into l_ee_work_psd_code from dual;
1396
1397 --
1398 -- Fetch SRS Parameter
1399 --
1400 l_srs_trns_pin := pay_magtape_generic.get_parameter_value('TRNS_PIN');
1401 l_srs_trns_tlcn := pay_magtape_generic.get_parameter_value('TRNS_TLCN');
1402
1403 l_input_report_qualifier := pay_magtape_generic.get_parameter_value('TRANSFER_STATE');
1404
1405 ltr_ue_name_table.delete;
1406 i := 0;
1407 --
1408 -- Fetch Archived Values of various DBIs used in submitter Record
1409 --
1410
1411 i := i +1;
1412 ltr_ue_name_table(i).ue_name := 'A_FIPS_CODE_JD'; -- 1
1413 ltr_ue_name_table(i).ue_data_level := 'GRE';
1414
1415 i := i +1;
1416 ltr_ue_name_table(i).ue_name := 'A_PER_NATIONAL_IDENTIFIER'; -- 2
1417 ltr_ue_name_table(i).ue_data_level := 'PER';
1418 i := i +1;
1419 ltr_ue_name_table(i).ue_name := 'A_PER_FIRST_NAME'; -- 3
1420 ltr_ue_name_table(i).ue_data_level := 'PER';
1421 i := i +1;
1422 ltr_ue_name_table(i).ue_name := 'A_PER_MIDDLE_NAMES' ; -- 4
1423 ltr_ue_name_table(i).ue_data_level := 'PER';
1424 i := i +1;
1425 ltr_ue_name_table(i).ue_name := 'A_PER_LAST_NAME'; -- 5
1426 ltr_ue_name_table(i).ue_data_level := 'PER';
1427 i := i +1;
1428 ltr_ue_name_table(i).ue_name := 'A_PER_SUFFIX'; -- 6
1429 ltr_ue_name_table(i).ue_data_level := 'PER';
1430 i := i +1;
1431 ltr_ue_name_table(i).ue_name := 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD'; -- 7
1432 ltr_ue_name_table(i).ue_data_level := 'STATE';
1433 ltr_ue_name_table(i).data_type :='AMT';
1434 ltr_ue_name_table(i).negative_check := 'Y';
1435 i := i +1;
1436 ltr_ue_name_table(i).ue_name := 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD'; -- 8
1437 ltr_ue_name_table(i).ue_data_level := 'STATE';
1438 ltr_ue_name_table(i).data_type :='AMT';
1439 ltr_ue_name_table(i).negative_check := 'Y';
1440 i := i +1;
1441 ltr_ue_name_table(i).ue_name := 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD'; -- 9
1442 ltr_ue_name_table(i).ue_data_level := 'STATE';
1443 ltr_ue_name_table(i).data_type :='AMT';
1444 ltr_ue_name_table(i).negative_check := 'Y';
1445 i := i +1;
1446 ltr_ue_name_table(i).ue_name := 'A_SIT_WITHHELD_PER_JD_GRE_YTD'; -- 10
1447 ltr_ue_name_table(i).ue_data_level := 'STATE';
1448 ltr_ue_name_table(i).data_type :='AMT';
1449 ltr_ue_name_table(i).negative_check := 'Y';
1450
1451
1452 i := i +1;
1453 ltr_ue_name_table(i).ue_name := 'A_PSD_SUBJ_WHABLE_PER_JD_GRE_MONTH'; -- 11
1454 ltr_ue_name_table(i).ue_data_level :='PSD';
1455 ltr_ue_name_table(i).data_type :='AMT';
1456 ltr_ue_name_table(i).negative_check := 'Y';
1457 i := i +1;
1458 ltr_ue_name_table(i).ue_name := 'A_PSD_WITHHELD_PER_JD_GRE_MONTH'; -- 12
1459 ltr_ue_name_table(i).ue_data_level :='PSD';
1460 ltr_ue_name_table(i).data_type :='AMT';
1461 ltr_ue_name_table(i).negative_check := 'Y';
1462
1463 i := i +1;
1464 ltr_ue_name_table(i).ue_name := 'A_PSD_SUBJ_WHABLE_PER_JD_GRE_QTD'; -- 13
1465 ltr_ue_name_table(i).ue_data_level :='PSD';
1466 ltr_ue_name_table(i).data_type :='AMT';
1467 ltr_ue_name_table(i).negative_check := 'Y';
1468
1469 i := i +1;
1470 ltr_ue_name_table(i).ue_name := 'A_PSD_WITHHELD_PER_JD_GRE_QTD'; -- 14
1471 ltr_ue_name_table(i).ue_data_level :='PSD';
1472 ltr_ue_name_table(i).data_type :='AMT';
1473 ltr_ue_name_table(i).negative_check := 'Y';
1474
1475 /*This balance is being archived so as to find out the R/NR value*/
1476 i := i +1;
1477 ltr_ue_name_table(i).ue_name := 'A_CITY_WK_PSD_WITHHELD_PER_JD_GRE_RUN'; -- 15
1478 ltr_ue_name_table(i).ue_data_level :='PSD';
1479 ltr_ue_name_table(i).data_type :='AMT';
1480 ltr_ue_name_table(i).negative_check := 'Y';
1481
1482 i := i +1;
1483 ltr_ue_name_table(i).ue_name := 'A_CITY_PSD_SUBJ_WHABLE_PER_JD_GRE_MONTH'; -- 16
1484 ltr_ue_name_table(i).ue_data_level :='PSD';
1485 ltr_ue_name_table(i).data_type :='AMT';
1486 ltr_ue_name_table(i).negative_check := 'Y';
1487
1488 i := i +1;
1489 ltr_ue_name_table(i).ue_name := 'A_CITY_PSD_WITHHELD_PER_JD_GRE_MONTH'; -- 17
1490 ltr_ue_name_table(i).ue_data_level :='PSD';
1491 ltr_ue_name_table(i).data_type :='AMT';
1492 ltr_ue_name_table(i).negative_check := 'Y';
1493
1494 i := i +1;
1495 ltr_ue_name_table(i).ue_name := 'A_SCHOOL_PSD_WITHHELD_PER_JD_GRE_MONTH'; -- 18
1496 ltr_ue_name_table(i).ue_data_level :='PSD';
1497 ltr_ue_name_table(i).data_type :='AMT';
1498 ltr_ue_name_table(i).negative_check := 'Y';
1499
1500 i := i +1;
1501 ltr_ue_name_table(i).ue_name := 'A_CITY_PSD_SUBJ_WHABLE_PER_JD_GRE_QTD'; -- 19
1502 ltr_ue_name_table(i).ue_data_level :='PSD';
1503 ltr_ue_name_table(i).data_type :='AMT';
1504 ltr_ue_name_table(i).negative_check := 'Y';
1505
1506 i := i +1;
1507 ltr_ue_name_table(i).ue_name := 'A_CITY_PSD_WITHHELD_PER_JD_GRE_QTD'; -- 20
1508 ltr_ue_name_table(i).ue_data_level :='PSD';
1509 ltr_ue_name_table(i).data_type :='AMT';
1510 ltr_ue_name_table(i).negative_check := 'Y';
1511
1512 i := i +1;
1513 ltr_ue_name_table(i).ue_name := 'A_SCHOOL_PSD_WITHHELD_PER_JD_GRE_QTD'; -- 21
1514 ltr_ue_name_table(i).ue_data_level :='PSD';
1515 ltr_ue_name_table(i).data_type :='AMT';
1516 ltr_ue_name_table(i).negative_check := 'Y';
1517
1518 --Added for the Bug 14799833
1519
1520 i := i +1;
1521 ltr_ue_name_table(i).ue_name := 'A_PSD_SUBJ_WHABLE_PER_JD_GRE_YTD'; -- 22
1522 ltr_ue_name_table(i).ue_data_level :='PSD_YTD';
1523 ltr_ue_name_table(i).data_type :='AMT';
1524 ltr_ue_name_table(i).negative_check := 'Y';
1525
1526 i := i +1;
1527 ltr_ue_name_table(i).ue_name := 'A_PSD_WITHHELD_PER_JD_GRE_YTD'; -- 23
1528 ltr_ue_name_table(i).ue_data_level :='PSD_YTD';
1529 ltr_ue_name_table(i).data_type :='AMT';
1530 ltr_ue_name_table(i).negative_check := 'Y';
1531
1532 --
1533 -- End of setting User Entities to PL/Table
1534 --
1535 --Added for the Bug 14799833, getting the YTD values
1536
1537 HR_UTILITY_TRACE('skchalla Period type'|| l_period_type);
1538
1539 HR_UTILITY_TRACE('p_assignment_action_id is '|| p_assignment_action_id);
1540 HR_UTILITY_TRACE('p_jurisdiction_code is '|| p_jurisdiction_code);
1541
1542 --Changes for the Bug 14799833
1543 IF l_period_type = 'Yearly' then
1544
1545 open c_get_ytd_values(p_assignment_action_id,p_jurisdiction_code);
1546
1547 fetch c_get_ytd_values into ltr_ue_name_table(22).ue_value,ltr_ue_name_table(23).ue_value;
1548
1549 --We will have to move the employee out of the Skip report if work psd code is either 510101 or 880000 and having non zero withheld.
1550
1551 IF c_get_ytd_values%FOUND Then
1552
1553 IF ltr_ue_name_table(23).ue_value <> 0 AND
1554 ( substr(p_jurisdiction_code,11,6) = '510101' OR substr(p_jurisdiction_code,11,6) = '880000') then
1555 l_ovrde_skip_status_f := TRUE;
1556 END IF;
1557
1558 END IF;
1559
1560 close c_get_ytd_values;
1561
1562 HR_UTILITY_TRACE('YTD wages '|| ltr_ue_name_table(22).ue_value);
1563 HR_UTILITY_TRACE('YTD withheld '|| ltr_ue_name_table(23).ue_value);
1564
1565 END IF;
1566
1567 HR_UTILITY_TRACE('DBI Count '||to_char(ltr_ue_name_table.count));
1568 HR_UTILITY_TRACE('YE Archive Assignment Action Id '||
1569 to_char(p_assignment_action_id));
1570
1571 IF ltr_ue_name_table.count > 0 then
1572 FOR j IN ltr_ue_name_table.first .. ltr_ue_name_table.last
1573 LOOP
1574 IF ltr_ue_name_table(j).ue_data_level = 'PER'
1575 THEN
1576 ltr_ue_name_table(j).ue_value :=
1577 hr_us_w2_rep.get_per_item( p_assignment_action_id,
1578 ltr_ue_name_table(j).ue_name);
1579 ELSIF ltr_ue_name_table(J).ue_data_level ='FED' THEN
1580 ltr_ue_name_table(j).ue_value := hr_us_w2_rep.get_w2_arch_bal(
1581 p_assignment_action_id
1582 ,ltr_ue_name_table(j).ue_name
1583 ,p_tax_unit_id
1584 ,'00-000-0000'
1585 , 0);
1586 IF ( ltr_ue_name_table(j).data_type = 'AMT' AND
1587 to_number(ltr_ue_name_table(j).ue_value) < 0)
1588 THEN
1589 l_status := 'FAILED';
1590 /*l_status_description := SUBSTR(l_status_description || 'Archive Item ' || ltr_ue_name_table(j).ue_name || ' has negative balance(' || trunc(ltr_ue_name_table(j).ue_value)||
1591 decode(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),0,'.00',rpad(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),3,'0'))||').'
1592 ||fnd_global.local_chr(13) || fnd_global.local_chr(10)||' ',1,1500);*/
1593 select SUBSTR(l_status_description || 'Archive Item ' || ltr_ue_name_table(j).ue_name || ' has negative balance(' || trunc(ltr_ue_name_table(j).ue_value)||
1594 decode(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),0,'.00',rpad(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),3,'0'))||').'
1595 ||fnd_global.local_chr(13) || fnd_global.local_chr(10)||' ',1,1500) into l_status_description
1596 from dual;
1597 END IF;
1598 ELSIF ltr_ue_name_table(J).ue_data_level ='STATE' THEN
1599 ltr_ue_name_table(j).ue_value :=
1600 hr_us_w2_rep.get_w2_arch_bal(
1601 p_assignment_action_id
1602 ,ltr_ue_name_table(j).ue_name
1603 ,p_tax_unit_id
1604 /* Bug 7592972 : State Code should be passed as
1605 two character long code
1606 ,p_state_code||'-000-0000'*/
1607 ,lpad(p_state_code,2,'0')||'-000-0000'
1608 , 2);
1609 IF ( ltr_ue_name_table(j).data_type = 'AMT' AND
1610 to_number(ltr_ue_name_table(j).ue_value) < 0)
1611 THEN
1612 l_status := 'FAILED';
1613 /*l_status_description := SUBSTR(l_status_description || 'Archive Item ' || ltr_ue_name_table(j).ue_name || ' has negative balance(' || trunc(ltr_ue_name_table(j).ue_value)||
1614 decode(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),0,'.00',rpad(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),3,'0'))||').'
1615 ||fnd_global.local_chr(13) || fnd_global.local_chr(10)||' ',1,1500);*/
1616 select SUBSTR(l_status_description || 'Archive Item ' || ltr_ue_name_table(j).ue_name || ' has negative balance(' || trunc(ltr_ue_name_table(j).ue_value)||
1617 decode(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),0,'.00',rpad(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),3,'0'))||').'
1618 ||fnd_global.local_chr(13) || fnd_global.local_chr(10)||' ',1,1500) into l_status_description
1619 from dual;
1620 END IF;
1621 ELSIF ltr_ue_name_table(J).ue_data_level ='PSD' THEN
1622 ltr_ue_name_table(j).ue_value :=
1623 hr_us_w2_rep.get_w2_arch_bal(
1624 p_assignment_action_id
1625 ,ltr_ue_name_table(j).ue_name
1626 ,p_tax_unit_id
1627 ,p_jurisdiction_code
1628 ,16);
1629
1630 IF ( ltr_ue_name_table(j).data_type = 'AMT' AND
1631 to_number(ltr_ue_name_table(j).ue_value) < 0)
1632 THEN
1633 l_status := 'FAILED';
1634 /*l_status_description := SUBSTR(l_status_description || 'Archive Item ' || ltr_ue_name_table(j).ue_name || ' has negative balance(' || trunc(ltr_ue_name_table(j).ue_value)||
1635 decode(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),0,'.00',rpad(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),3,'0'))||').'
1636 ||fnd_global.local_chr(13) || fnd_global.local_chr(10)||' ',1,1500);*/
1637 select SUBSTR(l_status_description || 'Archive Item ' || ltr_ue_name_table(j).ue_name || ' has negative balance(' || trunc(ltr_ue_name_table(j).ue_value)||
1638 decode(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),0,'.00',rpad(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),3,'0'))||').'
1639 ||fnd_global.local_chr(13) || fnd_global.local_chr(10)||' ',1,1500) into l_status_description
1640 from dual;
1641 END IF;
1642 --Added for the Bug 14799833
1643 ELSIF ltr_ue_name_table(J).ue_data_level ='PSD_YTD' THEN
1644
1645 IF ( ltr_ue_name_table(j).data_type = 'AMT' AND
1646 to_number(ltr_ue_name_table(j).ue_value) < 0)
1647 THEN
1648 l_status := 'FAILED';
1649 select SUBSTR(l_status_description || 'Archive Item ' || ltr_ue_name_table(j).ue_name || ' has negative balance(' || trunc(ltr_ue_name_table(j).ue_value)||
1650 decode(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),0,'.00',rpad(abs(ltr_ue_name_table(j).ue_value)-abs(trunc(ltr_ue_name_table(j).ue_value)),3,'0'))||').'
1651 ||fnd_global.local_chr(13) || fnd_global.local_chr(10)||' ',1,1500) into l_status_description
1652 from dual;
1653 END IF;
1654 ELSIF ltr_ue_name_table(J).ue_data_level ='GRE' THEN
1655 ltr_ue_name_table(j).ue_value :=
1656 hr_us_w2_rep.get_state_item(
1657 p_tax_unit_id,
1658 /* Bug 7592972 : State Code should be passed as
1659 two character long code
1660 p_state_code||'-000-0000',*/
1661 lpad(p_state_code,2,'0')||'-000-0000',
1662 p_payroll_action_id,
1663 ltr_ue_name_table(j).ue_name);
1664 ELSE
1665 ltr_ue_name_table(j).ue_value := ' ';
1666 END IF;
1667 HR_UTILITY_TRACE('DBI ' || ltr_ue_name_table(J).ue_data_level ||
1668 ' -('||to_char(j)|| ') : < '||ltr_ue_name_table(j).ue_name||
1669 ' > VALUE : < '|| ltr_ue_name_table(j).ue_value||' >');
1670 END LOOP;
1671 HR_UTILITY_TRACE('Status of Employee Record ' || l_status);
1672 END IF;
1673
1674 /* ==================================================
1675 Get Employer or transmitter Company Information releated Submitter
1676 ================================================== */
1677 employee_data := pay_us_reporting_utils_pkg.get_item_data(
1678 p_assignment_id,
1679 l_date_earned,
1680 l_tax_unit_id,
1681 l_reporting_date,
1682 input_empe_name,
1683 l_input_report_type,
1684 l_input_report_type_format,
1685 l_input_report_qualifier,
1686 l_input_record_name,
1687 input_empe_1,
1688 input_empe_2,
1689 input_empe_3,
1690 input_empe_4,
1691 input_empe_5,
1692 input_empe_validate_flag,
1693 empe_exclude_output_flag,
1694 empe_out_1,
1695 empe_out_2,
1696 empe_out_3,
1697 empe_out_4,
1698 empe_out_5,
1699 empe_out_6,
1700 empe_out_7,
1701 empe_out_8,
1702 empe_out_9,
1703 empe_out_10);
1704
1705 --
1706 -- Pouplate All the Tags to be used for RE record Data Items
1707 --
1708 pay_us_psd_generic_extract.populate_ee_data_tag;
1709
1710 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
1711 FROM dual;
1712
1713 /* Initialize with Default Value */
1714
1715 FOR I IN ltr_ee_data_tag.first .. ltr_ee_data_tag.last
1716 LOOP
1717 g_ee_record(I).employee_data := ' ';
1718 g_ee_record(I).employee_tag := ltr_ee_data_tag(I);
1719 END LOOP;
1720
1721 /* Deriving all data Items that are used for RS record */
1722
1723
1724 /* Record Identifier */
1725 g_ee_record(RS_RECORD_IDENTIFIER).employee_data := 'RS';
1726 HR_UTILITY_TRACE(g_ee_record(1).employee_tag || ' : '
1727 || g_ee_record(RS_RECORD_IDENTIFIER).employee_data);
1728
1729 /*FIPS CODE*/
1730 g_ee_record(EE_STATE_CODE).employee_data := ltr_ue_name_table(1).ue_value;
1731 HR_UTILITY_TRACE(g_ee_record(EE_STATE_CODE).employee_tag || ' : '
1732 || g_ee_record(EE_STATE_CODE).employee_data);
1733
1734 g_ee_record(EE_TAXING_ENTITY_CODE).employee_data := '';
1735 HR_UTILITY_TRACE(g_ee_record(EE_TAXING_ENTITY_CODE).employee_tag || ' : '
1736 || g_ee_record(EE_TAXING_ENTITY_CODE).employee_data);
1737
1738 /* Employee, Social Security Number (SSN) */
1739 g_ee_record(EE_SSN).employee_data := ltr_ue_name_table(2).ue_value;
1740 HR_UTILITY_TRACE(g_ee_record(EE_SSN).employee_tag || ' : '
1741 || g_ee_record(EE_SSN).employee_data);
1742
1743 /* Employee, First Name */
1744 --g_ee_record(EE_FIRST_NAME).employee_data := ltr_ue_name_table(2).ue_value;
1745 g_ee_record(EE_FIRST_NAME).employee_data := upper(ltr_ue_name_table(3).ue_value);
1746 HR_UTILITY_TRACE(g_ee_record(EE_FIRST_NAME).employee_tag || ' : '
1747 || g_ee_record(EE_FIRST_NAME).employee_data);
1748
1749 /* Employee, Middle Name or Initial */
1750 --g_ee_record(EE_MIDDLE_INITIAL).employee_data := ltr_ue_name_table(3).ue_value;
1751 g_ee_record(EE_MIDDLE_INITIAL).employee_data := upper(ltr_ue_name_table(4).ue_value);
1752 HR_UTILITY_TRACE(g_ee_record(EE_MIDDLE_INITIAL).employee_tag || ' : '
1753 || g_ee_record(EE_MIDDLE_INITIAL).employee_data);
1754
1755 /* Employee, Last Name */
1756 --g_ee_record(EE_LAST_NAME).employee_data := ltr_ue_name_table(4).ue_value;
1757 g_ee_record(EE_LAST_NAME).employee_data := upper(ltr_ue_name_table(5).ue_value);
1758 HR_UTILITY_TRACE(g_ee_record(EE_LAST_NAME).employee_tag || ' : '
1759 || g_ee_record(EE_LAST_NAME).employee_data);
1760
1761 /* Employee, Name Suffix */
1762 --g_ee_record(EE_SUFFIX).employee_data := ltr_ue_name_table(5).ue_value;
1763 g_ee_record(EE_SUFFIX).employee_data := upper(ltr_ue_name_table(6).ue_value);
1764 HR_UTILITY_TRACE(g_ee_record(EE_SUFFIX).employee_tag || ' : '
1765 || g_ee_record(EE_SUFFIX).employee_data);
1766
1767 /* Employee, Location Address */
1768 g_ee_record(EE_LOCATION_ADDRESS).employee_data := empe_out_1;
1769 HR_UTILITY_TRACE(g_ee_record(EE_LOCATION_ADDRESS).employee_tag || ' : '
1770 || g_ee_record(EE_LOCATION_ADDRESS).employee_data);
1771
1772 /* Pos: 88 Len: 22 Desc: Employee, Delivery Address */
1773 g_ee_record(EE_DELIVERY_ADDRESS).employee_data := empe_out_2;
1774 HR_UTILITY_TRACE(g_ee_record(EE_DELIVERY_ADDRESS).employee_tag || ' : '
1775 || g_ee_record(EE_DELIVERY_ADDRESS).employee_data);
1776
1777 /* Employee, City */
1778 g_ee_record(EE_CITY).employee_data := empe_out_3;
1779 HR_UTILITY_TRACE(g_ee_record(EE_CITY).employee_tag || ' : '
1780 || g_ee_record(EE_CITY).employee_data);
1781
1782 /* Employee, State Abbreviation */
1783 g_ee_record(EE_STATE_ABBREVIATION).employee_data := empe_out_4;
1784 HR_UTILITY_TRACE(g_ee_record(EE_STATE_ABBREVIATION).employee_tag || ' : '
1785 || g_ee_record(EE_STATE_ABBREVIATION).employee_data);
1786
1787 /* Employee, Zip Code */
1788 g_ee_record(EE_ZIP_CODE).employee_data := empe_out_5;
1789 HR_UTILITY_TRACE(g_ee_record(EE_ZIP_CODE).employee_tag || ' : '
1790 || g_ee_record(EE_ZIP_CODE).employee_data);
1791
1792 /* Employee, Zip Code Extension */
1793 g_ee_record(EE_ZIP_CODE_EXTENSION).employee_data :=
1794 empe_out_6;
1795 HR_UTILITY_TRACE(g_ee_record(EE_ZIP_CODE_EXTENSION).employee_tag || ' : '
1796 || g_ee_record(EE_ZIP_CODE_EXTENSION).employee_data);
1797
1798 /* Employee, Foreign State - Province */
1799 g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_data
1800 := empe_out_7;
1801 HR_UTILITY_TRACE(g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_tag || ' : '
1802 || g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_data);
1803
1804 /* Employee, Foreign Postal Code */
1805 g_ee_record(EE_FOREIGN_POSTAL_CODE).employee_data
1806 := empe_out_8;
1807 HR_UTILITY_TRACE(g_ee_record(EE_FOREIGN_POSTAL_CODE).employee_tag || ' : '
1808 || g_ee_record(EE_FOREIGN_STATE_PROVINCE).employee_data);
1809
1810 /*Employee, Country Code */
1811 g_ee_record(EE_COUNTRY_CODE).employee_data
1812 := empe_out_9;
1813 HR_UTILITY_TRACE(g_ee_record(EE_COUNTRY_CODE).employee_tag || ' : '
1814 || g_ee_record(EE_COUNTRY_CODE).employee_data);
1815
1816 /*Record Change Notice: 0 for no change, 1 to indicate a change to Name, Address, or PSD/Jurisdiction Code.*/
1817 g_ee_record(EE_RECORD_CHANGE_NOTICE).employee_data := '0';
1818 HR_UTILITY_TRACE(g_ee_record(EE_RECORD_CHANGE_NOTICE).employee_tag || ' : '
1819 || g_ee_record(EE_RECORD_CHANGE_NOTICE).employee_data);
1820
1821 /* Reporting Period */
1822 g_ee_record(EE_REPORTING_PERIOD).employee_data := l_reporting_period;
1823 HR_UTILITY_TRACE(g_ee_record(EE_REPORTING_PERIOD).employee_tag || ' : '
1824 || g_ee_record(EE_REPORTING_PERIOD).employee_data);
1825
1826
1827 /* State Code */
1828
1829 g_ee_record(EE_STATE_CODE2).employee_data := '';
1830 HR_UTILITY_TRACE(g_ee_record(EE_STATE_CODE2).employee_tag || ' : '
1831 || g_ee_record(EE_STATE_CODE2).employee_data);
1832
1833 /* State Taxable Wages
1834 A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD +
1835 A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD -
1836 A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD
1837 */
1838 l_state_wages := to_number(ltr_ue_name_table(7).ue_value) +
1839 to_number(ltr_ue_name_table(8).ue_value) -
1840 to_number(ltr_ue_name_table(9).ue_value);
1841
1842 g_ee_record(EE_STATE_WAGES).employee_data := to_char(l_state_wages);
1843 HR_UTILITY_TRACE(g_ee_record(EE_STATE_WAGES).employee_tag || ' : '
1844 || g_ee_record(EE_STATE_WAGES).employee_data);
1845
1846 /* SIT Withheld */
1847 g_ee_record(EE_SIT_WITHHELD).employee_data := ltr_ue_name_table(10).ue_value;
1848 HR_UTILITY_TRACE(g_ee_record(EE_SIT_WITHHELD).employee_tag || ' : '
1849 || g_ee_record(EE_SIT_WITHHELD).employee_data);
1850
1851 /*Local Tax Type: Required. "E" = EIT, "L" = LST, "W" = W2
1852 Defaulted to 'E'*/
1853 --Modified for the Bug 14799833
1854 if (l_period_type <>'Yearly') then
1855 g_ee_record(EE_LOCAL_TAX_TYPE).employee_data := 'E';
1856 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_TAX_TYPE).employee_tag || ' : '
1857 || g_ee_record(EE_LOCAL_TAX_TYPE).employee_data);
1858 else
1859 g_ee_record(EE_LOCAL_TAX_TYPE).employee_data := 'W';
1860 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_TAX_TYPE).employee_tag || ' : '
1861 || g_ee_record(EE_LOCAL_TAX_TYPE).employee_data);
1862 end if;
1863
1864 /*PSD Subject Withholdable and Withheld taxes*/
1865 if (l_period_type='Monthly') then
1866 --PSD_SUBJ_WHABLE_MONTH
1867 g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_data := ltr_ue_name_table(11).ue_value;
1868 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_tag || ' : '
1869 || g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_data);
1870 --PSD_WITHHELD_MONTH
1871 g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_data := ltr_ue_name_table(12).ue_value;
1872 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_tag || ' : '
1873 || g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_data);
1874 --
1875 elsif (l_period_type='Quarterly') then--Modified for the Bug 14799833
1876
1877 --PSD_SUBJ_WHABLE_QTD
1878 g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_data := ltr_ue_name_table(13).ue_value;
1879 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_tag || ' : '
1880 || g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_data);
1881 --PSD_WITHHELD_QTD
1882 g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_data := ltr_ue_name_table(14).ue_value;
1883 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_tag || ' : '
1884 || g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_data);
1885
1886 --Added for the Bug 14799833
1887 elsif (l_period_type='Yearly') then
1888
1889 --PSD_SUBJ_WHABLE_YTD
1890 g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_data := ltr_ue_name_table(22).ue_value;
1891 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_tag || ' : '
1892 || g_ee_record(EE_LOCAL_TAXABLE_WAGES).employee_data);
1893 --PSD_WITHHELD_YTD
1894 g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_data := ltr_ue_name_table(23).ue_value;
1895 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_tag || ' : '
1896 || g_ee_record(EE_LOCAL_INCOME_TAX_WITHHELD).employee_data);
1897
1898 end if;
1899
1900 /* EE_STATE_CONTROL_NUMBER */
1901 g_ee_record(EE_STATE_CONTROL_NUMBER).employee_data := ' ';
1902 HR_UTILITY_TRACE(g_ee_record(EE_STATE_CONTROL_NUMBER).employee_tag || ' : '
1903 || g_ee_record(EE_STATE_CONTROL_NUMBER).employee_data);
1904
1905 /*PSD code of employee's residence*/
1906 g_ee_record(EE_RESIDENCE_PSD_CODE).employee_data :=l_ee_residence_psd_code;
1907 HR_UTILITY_TRACE(g_ee_record(EE_RESIDENCE_PSD_CODE).employee_tag || ' : '
1908 || g_ee_record(EE_RESIDENCE_PSD_CODE).employee_data);
1909
1910 g_ee_record(EE_SCHOOL_DST_ONLY_PAYMENT).employee_data := ' ';
1911 HR_UTILITY_TRACE(g_ee_record(EE_SCHOOL_DST_ONLY_PAYMENT).employee_tag || ' : '
1912 || g_ee_record(EE_SCHOOL_DST_ONLY_PAYMENT).employee_data);
1913
1914 /*PSD code of employee's work location*/
1915 g_ee_record(EE_WORK_PSD_CODE).employee_data := l_ee_work_psd_code;
1916 HR_UTILITY_TRACE(g_ee_record(EE_WORK_PSD_CODE).employee_tag || ' : '
1917 || g_ee_record(EE_WORK_PSD_CODE).employee_data);
1918
1919 /*The Tax paid by the employee is Resident/ Non-Resident*/
1920 if (ltr_ue_name_table(15).ue_value>0) then
1921 --Non Resident
1922 g_ee_record(EE_LOCAL_TAX_TYPE_R_NR).employee_data := 'ENR';
1923 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_TAX_TYPE_R_NR).employee_tag || ' : '
1924 || g_ee_record(EE_LOCAL_TAX_TYPE_R_NR).employee_data);
1925 else
1926 --Resident
1927 g_ee_record(EE_LOCAL_TAX_TYPE_R_NR).employee_data := 'EIT';
1928 HR_UTILITY_TRACE(g_ee_record(EE_LOCAL_TAX_TYPE_R_NR).employee_tag || ' : '
1929 || g_ee_record(EE_LOCAL_TAX_TYPE_R_NR).employee_data);
1930 end if;
1931
1932 g_ee_record(EE_PSD_SUBJ_WHABLE_MONTH).employee_data := ltr_ue_name_table(11).ue_value;
1933 HR_UTILITY_TRACE(g_ee_record(EE_PSD_SUBJ_WHABLE_MONTH).employee_tag || ' : '
1934 || g_ee_record(EE_PSD_SUBJ_WHABLE_MONTH).employee_data);
1935 g_ee_record(EE_PSD_WITHHELD_MONTH).employee_data := ltr_ue_name_table(12).ue_value;
1936 HR_UTILITY_TRACE(g_ee_record(EE_PSD_WITHHELD_MONTH).employee_tag || ' : '
1937 || g_ee_record(EE_PSD_WITHHELD_MONTH).employee_data);
1938 g_ee_record(EE_PSD_SUBJ_WHABLE_QTD).employee_data := ltr_ue_name_table(13).ue_value;
1939 HR_UTILITY_TRACE(g_ee_record(EE_PSD_SUBJ_WHABLE_QTD).employee_tag || ' : '
1940 || g_ee_record(EE_PSD_SUBJ_WHABLE_QTD).employee_data);
1941 g_ee_record(EE_PSD_WITHHELD_QTD).employee_data := ltr_ue_name_table(14).ue_value;
1942 HR_UTILITY_TRACE(g_ee_record(EE_PSD_WITHHELD_QTD).employee_tag || ' : '
1943 || g_ee_record(EE_PSD_WITHHELD_QTD).employee_data);
1944
1945 g_ee_record(EE_CITY_PSD_SUBJ_WHABLE_MONTH).employee_data := ltr_ue_name_table(16).ue_value;
1946 HR_UTILITY_TRACE(g_ee_record(EE_CITY_PSD_SUBJ_WHABLE_MONTH).employee_tag || ' : '
1947 || g_ee_record(EE_CITY_PSD_SUBJ_WHABLE_MONTH).employee_data);
1948 g_ee_record(EE_CITY_PSD_WITHHELD_MONTH).employee_data := ltr_ue_name_table(17).ue_value;
1949 HR_UTILITY_TRACE(g_ee_record(EE_CITY_PSD_WITHHELD_MONTH).employee_tag || ' : '
1950 || g_ee_record(EE_CITY_PSD_WITHHELD_MONTH).employee_data);
1951 g_ee_record(EE_SCHOOL_PSD_WITHHELD_MONTH).employee_data := ltr_ue_name_table(18).ue_value;
1952 HR_UTILITY_TRACE(g_ee_record(EE_SCHOOL_PSD_WITHHELD_MONTH).employee_tag || ' : '
1953 || g_ee_record(EE_SCHOOL_PSD_WITHHELD_MONTH).employee_data);
1954 g_ee_record(EE_CITY_PSD_SUBJ_WHABLE_QTD).employee_data := ltr_ue_name_table(19).ue_value;
1955 HR_UTILITY_TRACE(g_ee_record(EE_CITY_PSD_SUBJ_WHABLE_QTD).employee_tag || ' : '
1956 || g_ee_record(EE_CITY_PSD_SUBJ_WHABLE_QTD).employee_data);
1957 g_ee_record(EE_CITY_PSD_WITHHELD_QTD).employee_data := ltr_ue_name_table(20).ue_value;
1958 HR_UTILITY_TRACE(g_ee_record(EE_CITY_PSD_WITHHELD_QTD).employee_tag || ' : '
1959 || g_ee_record(EE_CITY_PSD_WITHHELD_QTD).employee_data);
1960 g_ee_record(EE_SCHOOL_PSD_WITHHELD_QTD).employee_data := ltr_ue_name_table(21).ue_value;
1961 HR_UTILITY_TRACE(g_ee_record(EE_SCHOOL_PSD_WITHHELD_QTD).employee_tag || ' : '
1962 || g_ee_record(EE_SCHOOL_PSD_WITHHELD_QTD).employee_data);
1963 --Added for the Bug#14799833
1964
1965 g_ee_record(EE_PSD_SUBJ_WHABLE_YTD).employee_data := ltr_ue_name_table(22).ue_value;
1966 HR_UTILITY_TRACE(g_ee_record(EE_PSD_SUBJ_WHABLE_YTD).employee_tag || ' : '
1967 || g_ee_record(EE_PSD_SUBJ_WHABLE_YTD).employee_data);
1968 g_ee_record(EE_PSD_WITHHELD_YTD).employee_data := ltr_ue_name_table(23).ue_value;
1969 HR_UTILITY_TRACE(g_ee_record(EE_PSD_WITHHELD_YTD).employee_tag || ' : '
1970 || g_ee_record(EE_PSD_WITHHELD_YTD).employee_data);
1971
1972 /*Work At Home Flag*/
1973 open c_get_wah_flag(p_assignment_action_id, p_date_earned);
1974 fetch c_get_wah_flag into l_wah_flag;
1975 close c_get_wah_flag;
1976 if(l_wah_flag = 'Y') then
1977 g_ee_record(EE_WORK_AT_HOME).employee_data := 'Y';
1978 HR_UTILITY_TRACE(g_ee_record(EE_WORK_AT_HOME).employee_tag || ' : '
1979 || g_ee_record(EE_WORK_AT_HOME).employee_data);
1980 else
1981 g_ee_record(EE_WORK_AT_HOME).employee_data := 'N';
1982 HR_UTILITY_TRACE(g_ee_record(EE_WORK_AT_HOME).employee_tag || ' : '
1983 || g_ee_record(EE_WORK_AT_HOME).employee_data);
1984 end if;
1985
1986 /*Employee's Tax Collection District Code*/
1987 g_ee_record(EE_TCD_CODE).employee_data := substr(l_ee_work_psd_code,1,2);
1988 HR_UTILITY_TRACE(g_ee_record(EE_TCD_CODE).employee_tag || ' : '
1989 || g_ee_record(EE_TCD_CODE).employee_data);
1990
1991 /*Employee Number*/
1992 g_ee_record(EE_EMPLOYEE_NUMBER).employee_data := empe_out_10;
1993 HR_UTILITY_TRACE(g_ee_record(EE_EMPLOYEE_NUMBER).employee_tag || ' : '
1994 || g_ee_record(EE_EMPLOYEE_NUMBER).employee_data);
1995
1996 /*Tax Year*/
1997 g_ee_record(EE_TAX_YEAR).employee_data := p_reporting_year;
1998 HR_UTILITY_TRACE(g_ee_record(EE_TAX_YEAR).employee_tag || ' : '
1999 || g_ee_record(EE_TAX_YEAR).employee_data);
2000
2001 /*Tax Quarterr*/
2002 g_ee_record(EE_TAX_QUARTER).employee_data := l_quarter;
2003 HR_UTILITY_TRACE(g_ee_record(EE_TAX_QUARTER).employee_tag || ' : '
2004 || g_ee_record(EE_TAX_QUARTER).employee_data);
2005
2006 open c_check_psd_code_for_gre(l_ee_work_psd_code, l_tax_unit_id);
2007 fetch c_check_psd_code_for_gre into l_psd_code_exists;
2008 close c_check_psd_code_for_gre;
2009 if (l_psd_code_exists= 'Y') then
2010 if(l_wah_flag = 'Y') then
2011 l_wah_psd_code_exists:='YY';
2012 else
2013 l_wah_psd_code_exists:='NY';
2014 end if;
2015 else
2016 if(l_wah_flag = 'Y') then
2017 --l_status := 'WAH';
2018 l_wah_psd_code_exists:='YN';
2019 l_status_description := 'This employee is "Working At Home". The Resident PSD code: '||l_ee_work_psd_code||' for this employee is not entered in the PSD Reporting Rules under the GRE id: '||l_tax_unit_id;
2020 else
2021 l_status := 'SKIP';
2022 l_wah_psd_code_exists:='NN';
2023 l_status_description := 'The Work PSD code: '||l_ee_work_psd_code||' for this employee is not entered in the PSD Reporting Rules under the GRE id: '||l_tax_unit_id;
2024 end if;
2025 end if;
2026
2027 if (l_ee_work_psd_code='990000') then
2028 l_status := 'SKIP';
2029 l_status_description := 'This employee has an invalid Work PSD Code: '||l_ee_work_psd_code;
2030 end if;
2031
2032 if (l_ee_work_psd_code='880000') then
2033 l_status := 'SKIP';
2034 l_status_description := 'This employee has an out of state Work PSD Code: '||l_ee_work_psd_code;
2035 end if;
2036
2037 if (l_ee_work_psd_code='510101' or l_ee_residence_psd_code='510101') then
2038 l_status := 'SKIP_PHILLY';
2039 l_status_description := 'This employee works/stays in Philadelphia (Work PSD Code:'||l_ee_work_psd_code||')';
2040 end if;
2041
2042 /* If employee SSN is not entered then the employee needs to be included in the exception report*/
2043 if (g_ee_record(EE_SSN).employee_data is null) then
2044 l_status := 'FAILED';
2045 l_status_description := 'Employee''s Social Security Number is missing or invalid.';
2046 end if;
2047
2048 --Overriding the status for the Yearly reporting, Bug 14799833
2049
2050 IF (l_ovrde_skip_status_f) and (l_status = 'SKIP' OR l_status = 'SKIP_PHILLY') then
2051 l_status := 'SUCCESS';
2052 l_status_description := 'NULL';
2053
2054 END IF;
2055
2056 /*Exception||TCD Code*/
2057 g_ee_record(EE_EXCEPTION_TCD_CODE).employee_data := l_status||'_'||g_ee_record(EE_TCD_CODE).employee_data;
2058 HR_UTILITY_TRACE(g_ee_record(EE_EXCEPTION_TCD_CODE).employee_tag || ' : '
2059 || g_ee_record(EE_EXCEPTION_TCD_CODE).employee_data);
2060
2061 /*WAH Flag || PSD Code Exists Flag*/
2062 g_ee_record(EE_WAH_PSD_CODE_EXISTS).employee_data := l_wah_psd_code_exists;
2063 HR_UTILITY_TRACE(g_ee_record(EE_WAH_PSD_CODE_EXISTS).employee_tag || ' : '
2064 || g_ee_record(EE_WAH_PSD_CODE_EXISTS).employee_data);
2065
2066 /*Exception|| Work PSD Code: for use in templates*/
2067 g_ee_record(EE_EXCEPTION_WORK_PSD).employee_data := l_status||l_ee_work_psd_code;
2068 HR_UTILITY_TRACE(g_ee_record(EE_EXCEPTION_WORK_PSD).employee_tag || ' : '
2069 || g_ee_record(EE_EXCEPTION_WORK_PSD).employee_data);
2070
2071 --
2072 l_final_xml_string := '';
2073 l_final_xml_string := '<EMPLOYEE>' ||
2074 '<EXCEPTION>' || l_status || '</EXCEPTION>'|| EOL
2075 || '<EXCEPTION_DETAILS>' || l_status_description || '</EXCEPTION_DETAILS>'|| EOL
2076 || '<WORK_AT_HOME>' || l_wah_flag || '</WORK_AT_HOME>'|| EOL
2077 || '<EE_TCD_CODE>' || g_ee_record(EE_TCD_CODE).employee_data || '</EE_TCD_CODE>'|| EOL
2078 || '<EE_EXCEPTION_TCD_CODE>' || g_ee_record(EE_EXCEPTION_TCD_CODE).employee_data || '</EE_EXCEPTION_TCD_CODE>'|| EOL
2079 || '<EE_EXCEPTION_WORK_PSD>' || g_ee_record(EE_EXCEPTION_WORK_PSD).employee_data || '</EE_EXCEPTION_WORK_PSD>'|| EOL
2080 --|| '<RW>'
2081 ;
2082 BEGIN
2083 l_final_xml_string := l_final_xml_string
2084 || '<'||convert_special_char(g_ee_record(1).employee_data)||'>';
2085 FOR I IN 1 .. g_ee_no_of_tag LOOP
2086 l_data_item_xml := '<'||g_ee_record(I).employee_tag||'>'||
2087 convert_special_char(nvl(g_ee_record(I).employee_data,' '))
2088 ||'</'||g_ee_record(I).employee_tag||'>'|| EOL;
2089 l_final_xml_string := l_final_xml_string || l_data_item_xml;
2090 HR_UTILITY_TRACE(l_data_item_xml);
2091 END LOOP;
2092 l_final_xml_string := l_final_xml_string || '</' || convert_special_char(g_ee_record(1).employee_data)
2093 || '>' || EOL;
2094
2095 --l_final_xml_string := l_final_xml_string || '</RW>' || EOL;
2096 EXCEPTION
2097 WHEN OTHERS THEN
2098 HR_UTILITY_TRACE('Error Encountered While formating RS Record Data Item');
2099 END;
2100 HR_UTILITY_TRACE(' RS Record Formatted Successfully Length '|| length(l_final_xml_string));
2101
2102
2103 l_final_xml_string := l_final_xml_string || '</EMPLOYEE>' || EOL;
2104 p_final_string := l_final_xml_string;
2105 --}
2106 EXCEPTION
2107 WHEN OTHERS THEN
2108 HR_UTILITY_TRACE('Error Encountered in procedure populate_arch_employee');
2109 END populate_arch_employee; -- End of Procedure populate_arch_employee
2110
2111
2112 BEGIN
2113
2114 g_proc_name := 'pay_us_psd_generic_extract.';
2115 g_debug := hr_utility.debug_enabled;
2116 g_document_type := 'LOCAL_PSD_XML';
2117 END pay_us_psd_generic_extract;