[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ER_REARCH
Source
1 PACKAGE BODY pay_us_er_rearch AS
2 /* $Header: pyuserre.pkb 120.2 2006/08/30 00:03:38 sodhingr noship $*/
3
4 gv_package_name VARCHAR2(50) := 'pay_us_er_rearch';
5 l_gre_name varchar2(100);
6 l_year varchar2(30);
7 gc_csv_delimiter VARCHAR2(1) := ',';
8 gc_csv_data_delimiter VARCHAR2(1) := '"';
9
10
11 FUNCTION formated_data_string
12 (p_input_string in varchar2
13 ,p_bold in varchar2
14 ,p_output_file_type in varchar2
15 )
16 RETURN VARCHAR2
17 IS
18
19 lv_format varchar2(32000);
20
21 BEGIN
22 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
23
24 if p_output_file_type = 'CSV' then
25
26
27 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
28
29 lv_format := gc_csv_data_delimiter || p_input_string ||
30 gc_csv_data_delimiter || gc_csv_delimiter;
31
32 end if;
33
34 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
35
36 return lv_format;
37
38 END formated_data_string;
39 /*****************************************************************
40 ** This procudure returns the Mandatory Static Labels and the
41 ** Other Additional Static columns.
42 *****************************************************************/
43
44 FUNCTION employer_rearch_header(
45 p_output_file_type in varchar2
46 )
47 RETURN VARCHAR2
48 IS
49
50 lv_format1 varchar2(32000);
51 lv_format2 varchar2(32000);
52
53 BEGIN
54
55 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
56 lv_format1 :=
57 formated_data_string (p_input_string => 'Year'
58 ,p_bold => 'Y'
59 ,p_output_file_type => p_output_file_type) ||
60 formated_data_string (p_input_string => 'Name of the GRE'
61 ,p_bold => 'Y'
62 ,p_output_file_type => p_output_file_type) ||
63 formated_data_string (p_input_string => 'Federal/State Level'
64 ,p_bold => 'Y'
65 ,p_output_file_type => p_output_file_type) ||
66 formated_data_string (p_input_string => 'Name of the field'
67 ,p_bold => 'Y'
68 ,p_output_file_type => p_output_file_type) ||
69 formated_data_string (p_input_string => 'Previous Value'
70 ,p_bold => 'Y'
71 ,p_output_file_type => p_output_file_type) ||
72 formated_data_string (p_input_string => 'Updated Value'
73 ,p_bold => 'Y'
74 ,p_output_file_type => p_output_file_type) ;
75
76 RETURN (lv_format1);
77 END;
78
79
80 FUNCTION employer_rearch_data (
81 p_year in varchar2
82 ,p_tax_unit_name in varchar2
83 ,p_st_fed in varchar2
84 ,p_name in varchar2
85 ,p_old_value in varchar2
86 ,p_new_value in varchar2
87 ,p_output_file_type in varchar2)
88
89 RETURN VARCHAR2
90 IS
91
92 lv_format1 VARCHAR2(32000);
93
94 BEGIN
95
96 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
97 lv_format1 :=
98 formated_data_string (p_input_string => p_year
99 ,p_bold => 'N'
100 ,p_output_file_type => p_output_file_type)||
101 formated_data_string (p_input_string => p_tax_unit_name
102 ,p_bold => 'N'
103 ,p_output_file_type => p_output_file_type)||
104 formated_data_string (p_input_string => p_st_fed
105 ,p_bold => 'N'
106 ,p_output_file_type => p_output_file_type) ||
107 formated_data_string (p_input_string => p_name
108 ,p_bold => 'N'
109 ,p_output_file_type => p_output_file_type)||
110 formated_data_string (p_input_string => p_old_value
111 ,p_bold => 'N'
112 ,p_output_file_type => p_output_file_type) ||
113 formated_data_string (p_input_string => p_new_value
114 ,p_bold => 'N'
115 ,p_output_file_type => p_output_file_type);
116
117
118 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
119
120
121 hr_utility.trace('Static Data1 = ' || lv_format1);
122 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
123
124 return (lv_format1);
125 END;
126
127 PROCEDURE insert_er_rearch_data(errbuf OUT nocopy VARCHAR2,
128 retcode OUT nocopy NUMBER,
129 p_year IN VARCHAR2,
130 p_tax_unit_id IN NUMBER,
131 p_fed_state IN VARCHAR2,
132 p_is_state IN VARCHAR2,
133 p_state_code IN VARCHAR2 default null)
134 IS
135
136 CURSOR c_yrend_pactid is
137 SELECT payroll_action_id
138 FROM pay_payroll_actions
139 WHERE report_type = 'YREND'
140 AND action_status in ('E','C')
141 AND legislative_parameters like to_char(p_tax_unit_id)||'%'
142 AND to_char(effective_date,'YYYY') = substr(p_year,1,4);
143
144
145 CURSOR c_gre_name is
146 SELECT NAME
147 FROM hr_organization_units
148 where organization_id = p_tax_unit_id;
149
150 l_pactid pay_payroll_actions.payroll_action_id%TYPE;
151 l_eff_date Date;
152
153 BEGIN
154
155 --hr_utility.trace_on(null,'erre');
156 hr_utility.trace('PROCEDURE insert_er_rearch_data');
157 hr_utility.trace('p_tax_unit_id '||to_char(p_tax_unit_id));
158 hr_utility.trace('p_fed_state ' ||p_fed_state);
159 hr_utility.trace('p_state_code '||p_state_code);
160 hr_utility.trace('p_year '||substr(p_year,1,4));
161
162
163 OPEN c_yrend_pactid;
164 FETCH c_yrend_pactid INTO l_pactid;
165
166 IF c_yrend_pactid%NOTFOUND THEN
167
168 hr_utility.trace('Pactid not found for GRE id '||to_char(p_tax_unit_id)||' for year' ||p_year);
169
170 ELSE
171
172 hr_utility.trace('Pactid found '||to_char(l_pactid));
173
174 OPEN c_gre_name;
175 FETCH c_gre_name INTO l_gre_name;
176 CLOSE c_gre_name;
177
178 hr_utility.trace(' l_gre_name = '||l_gre_name);
179
180 l_year := substr(p_year,1,4);
181
182 fnd_file.put_line(fnd_file.output,employer_rearch_header('CSV'));
183
184 IF p_fed_state = 'Federal' THEN
185
186 hr_utility.trace('Federal. p_fed_state = '||p_fed_state);
187
188 hr_utility.trace('Going to call eoy_archive_gre_data for Fed');
189 pay_us_archive.eoy_archive_gre_data
190 (l_pactid,
191 p_tax_unit_id,
192 'FED W2 REPORTING RULES REARCH',
193 'ALL');
194
195 pay_us_archive.eoy_archive_gre_data
196 (l_pactid,
197 p_tax_unit_id,
198 'FED TAX UNIT INFORMATION REARCH',
199 'ALL');
200
201 pay_us_archive.eoy_archive_gre_data
202 (l_pactid,
203 p_tax_unit_id,
204 'FEDERAL TAX RULES REARCH',
205 'ALL');
206
207 pay_us_archive.eoy_archive_gre_data
208 (l_pactid,
209 p_tax_unit_id,
210 'FED 1099R MAGNETIC REPORT RULES REARCH',
211 'ALL');
212
213 ELSIF p_fed_state = 'State' THEN
214
215 hr_utility.trace('Going to call eoy_archive_gre_data for State');
216 pay_us_archive.eoy_archive_gre_data
217 (l_pactid,
218 p_tax_unit_id,
219 'STATE TAX RULES REARCH',
220 p_state_code);
221
222 ELSIF p_fed_state = 'View Online W2 Profile' THEN
223
224 hr_utility.trace('Going to call eoy_archive_gre_data for View Online W2 Profile');
225 pay_us_archive.eoy_archive_gre_data
226 (l_pactid,
227 p_tax_unit_id,
228 'View Online W2 Profile',
229 'ALL');
230
231
232 END IF;
233
234 END IF;
235
236 CLOSE c_yrend_pactid;
237 commit;
238 END;
239
240 PROCEDURE print_er_rearch_data(p_user_entity_id IN NUMBER,
241 p_federal_state IN VARCHAR2,
242 p_old_value IN VARCHAR2,
243 p_new_value IN VARCHAR2)
244
245 IS
246
247 CURSOR c_usr_entity_name
248 IS
249 SELECT user_entity_name
250 FROM ff_user_entities
251 WHERE user_entity_id = p_user_entity_id;
252
253 CURSOR c_gre_name(c_tax_unit_id VARCHAR2) is
254 SELECT NAME
255 FROM hr_organization_units
256 where organization_id = to_number(c_tax_unit_id);
257
258 CURSOR c_per_name(c_person_id VARCHAR2) is
259 SELECT FULL_NAME
260 FROM PER_PEOPLE_F
261 WHERE person_id = to_number(c_person_id);
262
263 CURSOR c_lookup_meaning(c_lookup_type fnd_common_lookups.lookup_type%TYPE,
264 c_lookup_code fnd_common_lookups.lookup_code%TYPE)
265 IS
266 SELECT meaning
267 FROM fnd_common_lookups
268 WHERE lookup_type = c_lookup_type
269 AND lookup_code = c_lookup_code;
270
271 CURSOR c_state_code(c_fips_code pay_state_rules.fips_code%TYPE)
272 IS
273 SELECT fips_code||' - ('||state_code||')'
274 FROM pay_state_rules
275 WHERe fips_code = c_fips_code;
276
277
278
279
280 p_item_name ff_user_entities.user_entity_name%TYPE;
281 l_item_name ff_user_entities.user_entity_name%TYPE;
282 l_old_value VARCHAR2(100);
283 l_new_value VARCHAR2(100);
284
285
286 BEGIN
287
288 hr_utility.trace('PROCEDURE print_er_rearch_data');
289 hr_utility.trace('p_federal_state = '||p_federal_state);
290 hr_utility.trace('p_old_value = '||p_old_value);
291 hr_utility.trace('p_new_value = '||p_new_value);
292
293 OPEN c_usr_entity_name;
294 FETCH c_usr_entity_name INTO l_item_name;
295
296 IF c_usr_entity_name%NOTFOUND THEN
297
298 l_item_name:= null;
299 hr_utility.trace('user_entity_name not found for user_entity_id = '
300 ||to_char(p_user_entity_id));
301
302 END IF;
303
304 CLOSE c_usr_entity_name;
305
306 p_item_name := l_item_name;
307
308 IF p_federal_state = 'Federal' THEN
309
310 l_item_name:= replace(substr(l_item_name,6),'_',' ');
311
312 ELSIF p_federal_state = 'State' THEN
313
314 l_item_name:= replace(substr(l_item_name,3),'_',' ');
315
316 END IF;
317
318 IF p_item_name = 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER' THEN
319
320 l_item_name:= replace(substr(p_item_name,3),'_',' ');
321
322 END IF;
323
324
325 hr_utility.trace('l_item_name = '||l_item_name);
326 l_old_value := p_old_value;
327 l_new_value := p_new_value;
328
329 IF l_item_name = 'W2 REPORTING RULES ORG COMPANY NAME' THEN
330
331 IF p_old_value IS NOT Null THEN
332
333 OPEN c_gre_name(p_old_value);
334 FETCH c_gre_name INTO l_old_value;
335
336 IF c_gre_name%NOTFOUND THEN
337
338 l_old_value := null;
339 hr_utility.trace('Gre Name not found for tax_unit_id '||p_old_value);
340
341 END IF;
342
343 CLOSE c_gre_name;
344
345 END IF;
346
347 IF p_new_value IS NOT NULL THEN
348
349 OPEN c_gre_name(p_new_value);
350 FETCH c_gre_name INTO l_new_value;
351
352 IF c_gre_name%NOTFOUND THEN
353
354 l_new_value := null;
355 hr_utility.trace('Gre Name not found for tax_unit_id '||p_new_value);
356
357 END IF;
358
359 CLOSE c_gre_name;
360
361 END IF;
362
363 ELSIF l_item_name ='W2 REPORTING RULES ORG CONTACT NAME' THEN
364
365 IF p_old_value IS NOT Null THEN
366
367 OPEN c_per_name(p_old_value);
368 FETCH c_per_name INTO l_old_value;
369
370 IF c_per_name%NOTFOUND THEN
371
372 l_old_value := null;
373 hr_utility.trace('Person Name not found for person_id '||p_old_value);
374
375 END IF;
376
377 CLOSE c_per_name;
378
379 END IF;
380
381 IF p_new_value IS NOT NULL THEN
382
383 OPEN c_per_name(p_new_value);
384 FETCH c_per_name INTO l_new_value;
385
386 IF c_per_name%NOTFOUND THEN
387
388 l_new_value := null;
389 hr_utility.trace('Person Name not found for person_id '||p_new_value);
390
391 END IF;
392
393 CLOSE c_per_name;
394
395 END IF;
396
397 ELSIF l_item_name ='W2 REPORTING RULES ORG PREPARER' THEN
398
399 IF p_old_value IS NOT NULL THEN
400
401 OPEN c_lookup_meaning('MMREF_PREPARER_CODE',p_old_value);
402 FETCH c_lookup_meaning INTO l_old_value;
403 CLOSE c_lookup_meaning;
404 hr_utility.trace('c_lookup_meaning = '||l_old_value);
405
406 END IF;
407
408 IF p_new_value IS NOT NULL THEN
409
410 OPEN c_lookup_meaning('MMREF_PREPARER_CODE',p_new_value);
411 FETCH c_lookup_meaning INTO l_new_value;
412 CLOSE c_lookup_meaning;
413 hr_utility.trace('c_lookup_meaning = '||l_new_value);
414
415 END IF;
416
417
418 ELSIF l_item_name ='W2 REPORTING RULES ORG NOTIFICATION METHOD' THEN
419
420 IF p_old_value IS NOT NULL THEN
421
422 OPEN c_lookup_meaning('MMREF_PROBLEM_NOTIFICATON_MTHD',p_old_value);
423 FETCH c_lookup_meaning INTO l_old_value;
424 CLOSE c_lookup_meaning;
425 hr_utility.trace('c_lookup_meaning = '||l_old_value);
426
427 END IF;
428
429 IF p_new_value IS NOT NULL THEN
430
431 OPEN c_lookup_meaning('MMREF_PROBLEM_NOTIFICATON_MTHD',p_new_value);
432 FETCH c_lookup_meaning INTO l_new_value;
433 CLOSE c_lookup_meaning;
434 hr_utility.trace('c_lookup_meaning = '||l_new_value);
435
436 END IF;
437
438 ELSIF l_item_name ='W2 REPORTING RULES ORG TAX JURISDICTION' THEN
439
440 IF p_old_value IS NOT NULL THEN
441
442 OPEN c_lookup_meaning('MMREF_TAX_JURISDICTION',p_old_value);
443 FETCH c_lookup_meaning INTO l_old_value;
444 CLOSE c_lookup_meaning;
445 hr_utility.trace('c_lookup_meaning = '||l_old_value);
446
447 END IF;
448
449 IF p_new_value IS NOT NULL THEN
450
451 OPEN c_lookup_meaning('MMREF_TAX_JURISDICTION',p_new_value);
452 FETCH c_lookup_meaning INTO l_new_value;
453 CLOSE c_lookup_meaning;
454 hr_utility.trace('c_lookup_meaning = '||l_new_value);
455
456 END IF;
457
458 ELSIF l_item_name = 'FEDERAL TAX RULES ORG TYPE OF EMPLOYMENT' THEN
459
460 IF p_old_value IS NOT NULL THEN
461
462 OPEN c_lookup_meaning('US_EMPLOYMENT_TYPE',p_old_value);
463 FETCH c_lookup_meaning INTO l_old_value;
464 CLOSE c_lookup_meaning;
465 hr_utility.trace('c_lookup_meaning = '||l_old_value);
466
467 END IF;
468
469 IF p_new_value IS NOT NULL THEN
470
471 OPEN c_lookup_meaning('US_EMPLOYMENT_TYPE',p_new_value);
472 FETCH c_lookup_meaning INTO l_new_value;
473 CLOSE c_lookup_meaning;
474 hr_utility.trace('c_lookup_meaning = '||l_new_value);
475
476 END IF;
477
478 ELSIF l_item_name = 'FIPS CODE JD' THEN
479
480
481 OPEN c_state_code(l_new_value);
482 FETCH c_state_code INTO l_new_value;
483 CLOSE c_state_code;
484
485 hr_utility.trace('l_new_value ='||l_new_value);
486
487
488 END IF;
489
490 fnd_file.put_line(fnd_file.output,employer_rearch_data(
491 l_year,
492 l_gre_name,
493 p_federal_state,
494 l_item_name,
495 l_old_value,
496 l_new_value,
497 'CSV'));
498
499
500
501 END;
502
503
504
505 END pay_us_er_rearch;