[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ER_REARCH
Source
1 PACKAGE BODY pay_us_er_rearch AS
2 /* $Header: pyuserre.pkb 120.3.12020000.2 2012/10/30 14:55:57 pkoduri ship $*/
3
4 gv_package_name VARCHAR2(50) := 'pay_us_er_rearch';
5 l_gre_name varchar2(240); --Bug# 14286448 Gre length issue
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 /* Changes for #8239671 Start*/
232 ELSIF p_fed_state = 'Original 1099-R on or before' THEN
233
234 hr_utility.trace('Going to call eoy_archive_gre_data for Original 1099- on or before Profile');
235 pay_us_archive.eoy_archive_gre_data
236 (l_pactid,
237 p_tax_unit_id,
238 'Original 1099-R on or before',
239 'ALL');
240 /* Changes for #8239671 End*/
241
242 END IF;
243
244 END IF;
245
246 CLOSE c_yrend_pactid;
247 commit;
248 END;
249
250 PROCEDURE print_er_rearch_data(p_user_entity_id IN NUMBER,
251 p_federal_state IN VARCHAR2,
252 p_old_value IN VARCHAR2,
253 p_new_value IN VARCHAR2)
254
255 IS
256
257 CURSOR c_usr_entity_name
258 IS
259 SELECT user_entity_name
260 FROM ff_user_entities
261 WHERE user_entity_id = p_user_entity_id;
262
263 CURSOR c_gre_name(c_tax_unit_id VARCHAR2) is
264 SELECT NAME
265 FROM hr_organization_units
266 where organization_id = to_number(c_tax_unit_id);
267
268 CURSOR c_per_name(c_person_id VARCHAR2) is
269 SELECT FULL_NAME
270 FROM PER_PEOPLE_F
271 WHERE person_id = to_number(c_person_id);
272
273 CURSOR c_lookup_meaning(c_lookup_type fnd_common_lookups.lookup_type%TYPE,
274 c_lookup_code fnd_common_lookups.lookup_code%TYPE)
275 IS
276 SELECT meaning
277 FROM fnd_common_lookups
278 WHERE lookup_type = c_lookup_type
279 AND lookup_code = c_lookup_code;
280
281 CURSOR c_state_code(c_fips_code pay_state_rules.fips_code%TYPE)
282 IS
283 SELECT fips_code||' - ('||state_code||')'
284 FROM pay_state_rules
285 WHERe fips_code = c_fips_code;
286
287
288
289
290 p_item_name ff_user_entities.user_entity_name%TYPE;
291 l_item_name ff_user_entities.user_entity_name%TYPE;
292 l_old_value VARCHAR2(100);
293 l_new_value VARCHAR2(100);
294
295
296 BEGIN
297
298 hr_utility.trace('PROCEDURE print_er_rearch_data');
299 hr_utility.trace('p_federal_state = '||p_federal_state);
300 hr_utility.trace('p_old_value = '||p_old_value);
301 hr_utility.trace('p_new_value = '||p_new_value);
302
303 OPEN c_usr_entity_name;
304 FETCH c_usr_entity_name INTO l_item_name;
305
306 IF c_usr_entity_name%NOTFOUND THEN
307
308 l_item_name:= null;
309 hr_utility.trace('user_entity_name not found for user_entity_id = '
310 ||to_char(p_user_entity_id));
311
312 END IF;
313
314 CLOSE c_usr_entity_name;
315
316 p_item_name := l_item_name;
317
318 IF p_federal_state = 'Federal' THEN
319
320 l_item_name:= replace(substr(l_item_name,6),'_',' ');
321
322 ELSIF p_federal_state = 'State' THEN
323
324 l_item_name:= replace(substr(l_item_name,3),'_',' ');
325
326 END IF;
327
328 IF p_item_name = 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER' THEN
329
330 l_item_name:= replace(substr(p_item_name,3),'_',' ');
331
332 END IF;
333
334
335 hr_utility.trace('l_item_name = '||l_item_name);
336 l_old_value := p_old_value;
337 l_new_value := p_new_value;
338
339 IF l_item_name = 'W2 REPORTING RULES ORG COMPANY NAME' THEN
340
341 IF p_old_value IS NOT Null THEN
342
343 OPEN c_gre_name(p_old_value);
344 FETCH c_gre_name INTO l_old_value;
345
346 IF c_gre_name%NOTFOUND THEN
347
348 l_old_value := null;
349 hr_utility.trace('Gre Name not found for tax_unit_id '||p_old_value);
350
351 END IF;
352
353 CLOSE c_gre_name;
354
355 END IF;
356
357 IF p_new_value IS NOT NULL THEN
358
359 OPEN c_gre_name(p_new_value);
360 FETCH c_gre_name INTO l_new_value;
361
362 IF c_gre_name%NOTFOUND THEN
363
364 l_new_value := null;
365 hr_utility.trace('Gre Name not found for tax_unit_id '||p_new_value);
366
367 END IF;
368
369 CLOSE c_gre_name;
370
371 END IF;
372
373 ELSIF l_item_name ='W2 REPORTING RULES ORG CONTACT NAME' THEN
374
375 IF p_old_value IS NOT Null THEN
376
377 OPEN c_per_name(p_old_value);
378 FETCH c_per_name INTO l_old_value;
379
380 IF c_per_name%NOTFOUND THEN
381
382 l_old_value := null;
383 hr_utility.trace('Person Name not found for person_id '||p_old_value);
384
385 END IF;
386
387 CLOSE c_per_name;
388
389 END IF;
390
391 IF p_new_value IS NOT NULL THEN
392
393 OPEN c_per_name(p_new_value);
394 FETCH c_per_name INTO l_new_value;
395
396 IF c_per_name%NOTFOUND THEN
397
398 l_new_value := null;
399 hr_utility.trace('Person Name not found for person_id '||p_new_value);
400
401 END IF;
402
403 CLOSE c_per_name;
404
405 END IF;
406
407 ELSIF l_item_name ='W2 REPORTING RULES ORG PREPARER' THEN
408
409 IF p_old_value IS NOT NULL THEN
410
411 OPEN c_lookup_meaning('MMREF_PREPARER_CODE',p_old_value);
412 FETCH c_lookup_meaning INTO l_old_value;
413 CLOSE c_lookup_meaning;
414 hr_utility.trace('c_lookup_meaning = '||l_old_value);
415
416 END IF;
417
418 IF p_new_value IS NOT NULL THEN
419
420 OPEN c_lookup_meaning('MMREF_PREPARER_CODE',p_new_value);
421 FETCH c_lookup_meaning INTO l_new_value;
422 CLOSE c_lookup_meaning;
423 hr_utility.trace('c_lookup_meaning = '||l_new_value);
424
425 END IF;
426
427
428 ELSIF l_item_name ='W2 REPORTING RULES ORG NOTIFICATION METHOD' THEN
429
430 IF p_old_value IS NOT NULL THEN
431
432 OPEN c_lookup_meaning('MMREF_PROBLEM_NOTIFICATON_MTHD',p_old_value);
433 FETCH c_lookup_meaning INTO l_old_value;
434 CLOSE c_lookup_meaning;
435 hr_utility.trace('c_lookup_meaning = '||l_old_value);
436
437 END IF;
438
439 IF p_new_value IS NOT NULL THEN
440
441 OPEN c_lookup_meaning('MMREF_PROBLEM_NOTIFICATON_MTHD',p_new_value);
442 FETCH c_lookup_meaning INTO l_new_value;
443 CLOSE c_lookup_meaning;
444 hr_utility.trace('c_lookup_meaning = '||l_new_value);
445
446 END IF;
447
448 ELSIF l_item_name ='W2 REPORTING RULES ORG TAX JURISDICTION' THEN
449
450 IF p_old_value IS NOT NULL THEN
451
452 OPEN c_lookup_meaning('MMREF_TAX_JURISDICTION',p_old_value);
453 FETCH c_lookup_meaning INTO l_old_value;
454 CLOSE c_lookup_meaning;
455 hr_utility.trace('c_lookup_meaning = '||l_old_value);
456
457 END IF;
458
459 IF p_new_value IS NOT NULL THEN
460
461 OPEN c_lookup_meaning('MMREF_TAX_JURISDICTION',p_new_value);
462 FETCH c_lookup_meaning INTO l_new_value;
463 CLOSE c_lookup_meaning;
464 hr_utility.trace('c_lookup_meaning = '||l_new_value);
465
466 END IF;
467
468 ELSIF l_item_name = 'FEDERAL TAX RULES ORG TYPE OF EMPLOYMENT' THEN
469
470 IF p_old_value IS NOT NULL THEN
471
472 OPEN c_lookup_meaning('US_EMPLOYMENT_TYPE',p_old_value);
473 FETCH c_lookup_meaning INTO l_old_value;
474 CLOSE c_lookup_meaning;
475 hr_utility.trace('c_lookup_meaning = '||l_old_value);
476
477 END IF;
478
479 IF p_new_value IS NOT NULL THEN
480
481 OPEN c_lookup_meaning('US_EMPLOYMENT_TYPE',p_new_value);
482 FETCH c_lookup_meaning INTO l_new_value;
483 CLOSE c_lookup_meaning;
484 hr_utility.trace('c_lookup_meaning = '||l_new_value);
485
486 END IF;
487
488 ELSIF l_item_name = 'FIPS CODE JD' THEN
489
490
491 OPEN c_state_code(l_new_value);
492 FETCH c_state_code INTO l_new_value;
493 CLOSE c_state_code;
494
495 hr_utility.trace('l_new_value ='||l_new_value);
496
497
498 END IF;
499
500 fnd_file.put_line(fnd_file.output,employer_rearch_data(
501 l_year,
502 l_gre_name,
503 p_federal_state,
504 l_item_name,
505 l_old_value,
506 l_new_value,
507 'CSV'));
508
509
510
511 END;
512
513
514
515 END pay_us_er_rearch;