[Home] [Help]
PACKAGE BODY: APPS.PAY_LIVEARCHIVE_PKG
Source
1 PACKAGE BODY pay_livearchive_pkg AS
2 /* $Header: pyuslvar.pkb 115.12 2004/01/22 05:40:41 ardsouza noship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_livearchive_pkg
22
23 File : pyuslvar.pkb
24
25 Description : Package for the Elements Reports. The package
26 generated the output file in the specified user
27 format. The current formats supported are
28 - HTML
29 - CSV
30
31 Change List
32 -----------
33 Date Name Vers Bug No Description
34 ---- ---- ------ ------- -----------
35 09-SEP-2002 djoshi 115.0 Created.
36 11-oct-2002 djoshi 115.1 Added code to remove ','
37 11-oct-2002 djoshi 115.2 Changed header from
38 to Archive - Live
39 05-nov-2002 djoshi 115.4 Changed for count
40 05-nov-2002 djoshi 115.5 Corrected spelling Error
41 21-nov-2002 djoshi 115.6 Changes for bug 2679192
42 and 2679586
43 21-nov-2002 djoshi 115.8 changed for message
44 24-nov-2002 djoshi 115.9 now box3 = box3 - box 7.
45 02-dec-2002 djoshi 115.10 Corrected out with nocopy
46 25-sep-2003 ardsouza 115.11 2554865 Changes for Enhancement
47 to reconcile 1099R specific
48 balances
49 22-jan-2004 ardsouza 115.12 3361925 Suppressed index on effective_date
50 in pay_payroll_actions to improve
51 performance.
52
53 */
54
55 /************************************************************
56 ** Local Package Variables
57 ************************************************************/
58 gv_title VARCHAR2(100) := ' Year End Archive Reconciliation Report';
59 gc_csv_delimiter VARCHAR2(1) := ',';
60 gc_csv_data_delimiter VARCHAR2(1) := '"';
61
62 gv_html_start_data VARCHAR2(5) := '<td>' ;
63 gv_html_end_data VARCHAR2(5) := '</td>' ;
64
65 gv_package_name VARCHAR2(50) := 'pay_livearchive_pkg';
66
67 gvr_balance live_bal_tab;
68
69
70 /***************************************************************
71 Name : bal_db_item
72 Purpose : For a balance DB item name
73 it returns the defined_balance_id of the balance.
74 Arguments :
75 Notes : A defined balance_id is required by the PLSQL
76 balance function.
77 ****************************************************************/
78
79 FUNCTION bal_db_item ( p_db_item_name varchar2)
80 RETURN number
81 IS
82
83 /* Get the defined_balance_id for the specified balance DB item. */
84
85 CURSOR csr_defined_balance is
86 SELECT to_number(UE.creator_id)
87 FROM ff_user_entities UE,
88 ff_database_items DI
89 WHERE DI.user_name = p_db_item_name
90 AND UE.user_entity_id = DI.user_entity_id
91 AND Ue.creator_type = 'B'
92 AND UE.legislation_code = 'US';
93
94 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
95
96
97 BEGIN
98
99 --hr_utility.trace('p_db_item_name is '||p_db_item_name);
100
101 OPEN csr_defined_balance;
102 FETCH csr_defined_balance INTO l_defined_balance_id;
103 IF csr_defined_balance%notfound THEN
104 CLOSE csr_defined_balance;
105 RAISE hr_utility.hr_error;
106 ELSE
107 CLOSE csr_defined_balance;
108 END IF;
109
110 --hr_utility.trace('l_defined_balance_id is '||to_char(l_defined_balance_id));
111 RETURN (l_defined_balance_id);
112
113 END bal_db_item;
114
115 /************************************************************
116 ** Function returns the string with the HTML Header tags
117 ************************************************************/
118
119 FUNCTION formated_header_string
120 (p_input_string in varchar2
121 ,p_output_file_type in varchar2
122 )
123 RETURN VARCHAR2
124 IS
125
126 lv_format varchar2(1000);
127
128 BEGIN
129 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
130 if p_output_file_type = 'CSV' then
131 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
132 lv_format := p_input_string;
133 elsif p_output_file_type = 'HTML' then
134 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
135 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
136 '</B></H1></CENTER></HEAD>';
137 end if;
138
139 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
140 return lv_format;
141
142 END formated_header_string;
143
144
145 /******************************************************************
146 ** Function Returns the formated input string based on the
147 ** Output format. If the format is CSV then the values are returned
148 ** seperated by comma (,). If the format is HTML then the returned
149 ** string as the HTML tags. The parameter p_bold only works for
150 ** the HTML format.
151 ******************************************************************/
152 FUNCTION formated_data_string
153 (p_input_string in varchar2
154 ,p_output_file_type in varchar2
155 ,p_bold in varchar2
156 )
157 RETURN VARCHAR2
158 IS
159
160 lv_format varchar2(1000);
161 lv_bold varchar2(10);
162 BEGIN
163 lv_bold := nvl(p_bold,'N');
164 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
165 if p_output_file_type = 'CSV' then
166 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
167 lv_format := gc_csv_data_delimiter || p_input_string ||
168 gc_csv_data_delimiter || gc_csv_delimiter;
169 elsif p_output_file_type = 'HTML' then
170 if p_input_string is null then
171 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
172 lv_format := gv_html_start_data || ' ' || gv_html_end_data;
173 else
174 if lv_bold = 'Y' then
175 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
176 lv_format := gv_html_start_data || '<b> ' || p_input_string
177 || '</b>' || gv_html_end_data;
178 else
179 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
180 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
181 end if;
182 end if;
183 end if;
184
185 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
186 return lv_format;
187
188 END formated_data_string;
189
190
191 FUNCTION formated_header_state(
192 p_output_file_type in varchar2
193 )RETURN VARCHAR2
194 IS
195
196 lv_format1 varchar2(32000);
197 lv_format2 varchar2(32000);
198
199 BEGIN
200
201 hr_utility.set_location(gv_package_name || '.formated_header_state', 10);
202 lv_format1 :=
203 formated_data_string (p_input_string => 'Year '
204 ,p_bold => 'Y'
205 ,p_output_file_type => p_output_file_type) ||
206 formated_data_string (p_input_string => 'GRE '
207 ,p_bold => 'Y'
208 ,p_output_file_type => p_output_file_type) ||
209
210 formated_data_string (p_input_string => 'State '
211 ,p_bold => 'Y'
212 ,p_output_file_type => p_output_file_type) ||
213
214 formated_data_string (p_input_string => 'JD Code '
215 ,p_bold => 'Y'
216 ,p_output_file_type => p_output_file_type) ||
217 formated_data_string (p_input_string => 'Employee Name '
218 ,p_bold => 'Y'
219 ,p_output_file_type => p_output_file_type) ||
220 formated_data_string (p_input_string => 'Employee SS # '
221 ,p_bold => 'Y'
222 ,p_output_file_type => p_output_file_type) ||
223 formated_data_string (p_input_string => 'Employee #'
224 ,p_bold => 'Y'
225 ,p_output_file_type => p_output_file_type) ||
226 formated_data_string (p_input_string => 'Box name '
227 ,p_bold => 'Y'
228 ,p_output_file_type => p_output_file_type) ||
229 formated_data_string (p_input_string => 'Archive '
230 ,p_bold => 'Y'
231 ,p_output_file_type => p_output_file_type)
232 ;
233
234 hr_utility.set_location(gv_package_name || '.formated_header_state', 20);
235 lv_format2 :=
236 formated_data_string (p_input_string => 'Live '
237 ,p_bold => 'Y'
238 ,p_output_file_type => p_output_file_type) ||
239 formated_data_string (p_input_string => 'Archive - Live'
240 ,p_bold => 'Y'
241 ,p_output_file_type => p_output_file_type)
242 ;
243
244 hr_utility.trace('Static Label1 = ' || lv_format1);
245 hr_utility.trace('Static Label2 = ' || lv_format2);
246
247 return lv_format1 || lv_format2;
248
249 hr_utility.set_location(gv_package_name || '.formated_header_state', 40);
250
251 END formated_header_state;
252
253
254 FUNCTION formated_header_federal(
255 p_output_file_type in varchar2
256 ) RETURN varchar2
257 IS
258
259 lv_format1 varchar2(32000);
260 lv_format2 varchar2(32000);
261
262 BEGIN
263
264 hr_utility.set_location(gv_package_name || '.formated_header_federal', 10);
265 lv_format1 :=
266 formated_data_string (p_input_string => 'Year '
267 ,p_bold => 'Y'
268 ,p_output_file_type => p_output_file_type) ||
269 formated_data_string (p_input_string => 'GRE '
270 ,p_bold => 'Y'
271 ,p_output_file_type => p_output_file_type) ||
272 formated_data_string (p_input_string => 'Employee Name '
273 ,p_bold => 'Y'
274 ,p_output_file_type => p_output_file_type) ||
275 formated_data_string (p_input_string => 'Employee SS # '
276 ,p_bold => 'Y'
277 ,p_output_file_type => p_output_file_type) ||
278 formated_data_string (p_input_string => 'Employee #'
279 ,p_bold => 'Y'
280 ,p_output_file_type => p_output_file_type) ||
281 formated_data_string (p_input_string => 'Box name '
282 ,p_bold => 'Y'
283 ,p_output_file_type => p_output_file_type) ||
284 formated_data_string (p_input_string => 'Archive '
285 ,p_bold => 'Y'
286 ,p_output_file_type => p_output_file_type)
287 ;
288
289 hr_utility.set_location(gv_package_name || '.formated_header_federal', 20);
290 lv_format2 :=
291 formated_data_string (p_input_string => 'Live '
292 ,p_bold => 'Y'
293 ,p_output_file_type => p_output_file_type) ||
294 formated_data_string (p_input_string => 'Archive - Live '
295 ,p_bold => 'Y'
296 ,p_output_file_type => p_output_file_type)
297 ;
298
299 hr_utility.trace('Static Label1 = ' || lv_format1);
300 hr_utility.trace('Static Label2 = ' || lv_format2);
301 return lv_format1 || lv_format2;
302
303 hr_utility.set_location(gv_package_name || '.formated_header_federal', 40);
304
305 END formated_header_federal;
306
307
308
309 FUNCTION formated_detail_state(
310 p_output_file_type in varchar2
311 ,p_year varchar2
312 ,p_gre varchar2
313 ,p_state varchar2
314 ,p_jd_code varchar2
315 ,p_Employee_name varchar2
316 ,p_employee_ssn varchar2
317 ,p_emplyee_number varchar2
318 ,p_box_name varchar2
319 ,p_live_balance varchar2
320 ,p_archive_balance varchar2
321 ,p_diff varchar2
322 ) RETURN varchar2
323 IS
324
325 lv_format1 varchar2(22000);
326 lv_format2 varchar2(10000);
327
328 BEGIN
329
330 hr_utility.set_location(gv_package_name || '.formated_detail_state', 10);
331 lv_format1 :=
332 formated_data_string (p_input_string => p_year
333 ,p_bold => 'N'
334 ,p_output_file_type => p_output_file_type) ||
335 formated_data_string (p_input_string => p_gre
336 ,p_bold => 'N'
337 ,p_output_file_type => p_output_file_type) ||
338 formated_data_string (p_input_string => p_state
339 ,p_bold => 'N'
340 ,p_output_file_type => p_output_file_type) ||
341 formated_data_string (p_input_string => p_jd_code
342 ,p_bold => 'N'
343 ,p_output_file_type => p_output_file_type) ||
344 formated_data_string (p_input_string => p_employee_name
345 ,p_bold => 'N'
346 ,p_output_file_type => p_output_file_type) ||
347 formated_data_string (p_input_string => P_employee_ssn
348 ,p_bold => 'N'
349 ,p_output_file_type => p_output_file_type) ||
350 formated_data_string (p_input_string => p_emplyee_number
351 ,p_bold => 'N'
352 ,p_output_file_type => p_output_file_type) ||
353 formated_data_string (p_input_string => p_box_name
354 ,p_bold => 'N'
355 ,p_output_file_type => p_output_file_type) ||
356 formated_data_string (p_input_string => p_archive_balance
357 ,p_bold => 'N'
358 ,p_output_file_type => p_output_file_type)
359 ;
360
361 hr_utility.set_location(gv_package_name || '.formated_detail_state', 20);
362 lv_format2 :=
363 formated_data_string (p_input_string => p_live_balance
364 ,p_bold => 'N'
365 ,p_output_file_type => p_output_file_type) ||
366 formated_data_string (p_input_string => p_Diff
367 ,p_bold => 'N'
368 ,p_output_file_type => p_output_file_type)
369 ;
370
371 hr_utility.trace('Static Label1 = ' || lv_format1);
372 hr_utility.trace('Static Label2 = ' || lv_format2);
373
374 hr_utility.set_location(gv_package_name || '.formated_detail_state', 30);
375
376 return lv_format1 || lv_format2;
377
378 END formated_detail_state;
379
380
381 function formated_detail_federaL(
382 p_output_file_type in varchar2
383 ,p_year varchar2
384 ,p_gre varchar2
385 ,p_Employee_name varchar2
386 ,p_employee_ssn varchar2
387 ,p_emplyee_number varchar2
388 ,p_box_name varchar2
389 ,p_live_balance varchar2
390 ,p_archive_balance varchar2
391 ,p_diff varchar2
392 ) RETURN VARCHAR2
393 IS
394
395 lv_format1 varchar2(22000);
396 lv_format2 varchar2(10000);
397
398 BEGIN
399
400 hr_utility.set_location(gv_package_name || '.formated_detail_federal', 10);
401 lv_format1 :=
402 formated_data_string (p_input_string => p_year
403 ,p_bold => 'N'
404 ,p_output_file_type => p_output_file_type) ||
405 formated_data_string (p_input_string => p_gre
406 ,p_bold => 'N'
407 ,p_output_file_type => p_output_file_type) ||
408 formated_data_string (p_input_string => p_employee_name
409 ,p_bold => 'N'
410 ,p_output_file_type => p_output_file_type) ||
411 formated_data_string (p_input_string => P_employee_ssn
412 ,p_bold => 'N'
413 ,p_output_file_type => p_output_file_type) ||
414 formated_data_string (p_input_string => p_emplyee_number
415 ,p_bold => 'N'
416 ,p_output_file_type => p_output_file_type) ||
417 formated_data_string (p_input_string => p_box_name
418 ,p_bold => 'N'
419 ,p_output_file_type => p_output_file_type) ||
420 formated_data_string (p_input_string => p_archive_balance
421 ,p_bold => 'N'
422 ,p_output_file_type => p_output_file_type)
423 ;
424
425 hr_utility.set_location(gv_package_name || '.formated_detail_federal', 20);
426 lv_format2 :=
427 formated_data_string (p_input_string => p_live_balance
428 ,p_bold => 'N'
429 ,p_output_file_type => p_output_file_type) ||
430 formated_data_string (p_input_string => p_diff
431 ,p_bold => 'N'
432 ,p_output_file_type => p_output_file_type)
433 ;
434
435 hr_utility.trace('Static Label1 = ' || lv_format1);
436 hr_utility.trace('Static Label2 = ' || lv_format2);
437
438 hr_utility.set_location(gv_package_name || '.formated_detail_federal', 30);
439
440 return lv_format1 || lv_format2;
441
442 END formated_detail_federal;
443
444
445 PROCEDURE formated_no_diff(output_file_type varchar2, p_lookup_description varchar2,
446 p_employee_count number, p_diff_count number)
447 IS
448 lvc_message varchar2(200);
449 lvc_return_message varchar2(400);
450 BEGIN
451 null;
452 /* Bug 2554865 - Modified Employee's to Employees */
453 IF output_file_type = 'CSV' THEN
454 lvc_message := 'For ' || substr(p_lookup_description,1,30) || ', Number of Employees compared = '
455 || to_char(p_employee_count)
456 || ' Number of Employees with difference = '
457 || to_char(p_diff_count);
458 hr_utility.set_location(gv_package_name || '.formated_no_diff', 10);
459 fnd_file.put_line(fnd_file.output,formated_data_string (p_input_string => lvc_message
460 ,p_bold => 'N'
461 ,p_output_file_type => output_file_type));
462 END IF;
463 END;
464
465
466
467 PROCEDURE formated_zero_count(output_file_type varchar2)
468 IS
469 lvc_message varchar2(200);
470 lvc_return_message varchar2(400);
471 BEGIN
472 null;
473 -- IF output_file_type = 'CSV' THEN
474 lvc_message := 'No person was picked up for comparision based on selection parameters.' ||
475 ' Ensure GRE has employees and all employees were successfully archived in the YEPP for the GRE.';
476 hr_utility.set_location(gv_package_name || '.formated_zero_count', 10);
477 fnd_file.put_line(fnd_file.output, formated_data_string (p_input_string => lvc_message
478 ,p_bold => 'N'
479 ,p_output_file_type => output_file_type));
480 -- END IF;
481 hr_utility.set_location(gv_package_name || '.formated_zero_count', 20);
482 END;
483
484 /* get the archive value */
485
486 function get_archive_value ( p_assignment_action_id number,
487 p_balance_name varchar2,
488 p_tax_unit_id number,
489 p_jurisdiction varchar2,
490 p_jurisdiction_level number
491 ) return number
492 IS
493
494 BEGIN
495
496 hr_utility.set_location(gv_package_name || '.get_archive_value', 10);
497
498
499
500 /* Set jurisdiction value */
501
502 return hr_us_w2_rep.get_w2_arch_bal(p_assignment_action_id,p_balance_name,p_tax_unit_id, p_jurisdiction,p_jurisdiction_level);
503
504 hr_utility.set_location(gv_package_name || '.get_archive_value', 20);
505
506 END; /* get_archive_value */
507
508
509 procedure populate_balance_id (
510 p_balance_name varchar2
511 )
512 IS
513 BEGIN
514 /* Populate value for balance_name balance id */
515
516 IF p_balance_name = 'A_WAGES' THEN
517
518 gvr_balance(1).bal_name := 'A_REGULAR_EARNINGS_PER_GRE_YTD';
519 gvr_balance(2).bal_name := 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD';
520 gvr_balance(3).bal_name := 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD';
521 gvr_balance(4).bal_name := 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD';
522 gvr_balance(5).bal_name := 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD';
523 gvr_balance(6).bal_name := 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD';
524
525
526 /* get the defined balance id for the given balance used for balance call */
527
528 gvr_balance(1).bal_id := bal_db_item(substr('A_REGULAR_EARNINGS_PER_GRE_YTD',3));
529 gvr_balance(2).bal_id := bal_db_item(substr('A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD',3));
530 gvr_balance(3).bal_id := bal_db_item(substr('A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD',3));
531 gvr_balance(4).bal_id := bal_db_item(substr('A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD',3));
532 gvr_balance(5).bal_id := bal_db_item(substr('A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD',3));
533 gvr_balance(6).bal_id := bal_db_item(substr('A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD',3));
534
535 ELSIF /* if it is state */
536 p_balance_name = 'A_W2_STATE_WAGES' THEN
537
538 gvr_balance(1).bal_name := 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD';
539 gvr_balance(2).bal_name := 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD';
540 gvr_balance(3).bal_name := 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD';
541
542 gvr_balance(1).bal_id := bal_db_item(substr(gvr_balance(1).bal_name,3));
543 gvr_balance(2).bal_id := bal_db_item(substr(gvr_balance(2).bal_name,3));
544 gvr_balance(3).bal_id := bal_db_item(substr(gvr_balance(3).bal_name,3));
545
546 ELSIF p_balance_name = 'A_SS_EE_TAXABLE_PER_GRE_YTD' THEN
547
548 gvr_balance(1).bal_name := 'A_SS_EE_TAXABLE_PER_GRE_YTD';
549 gvr_balance(2).bal_name := 'A_W2_BOX_7_PER_GRE_YTD';
550
551 gvr_balance(1).bal_id := bal_db_item(substr(gvr_balance(1).bal_name,3));
552 gvr_balance(2).bal_id := bal_db_item(substr(gvr_balance(2).bal_name,3));
553
554 /* Bug 2554865 - Modified populate_balance_id to map 'A_W2_GROSS_1099R'
555 to 'A_GROSS_EARNINGS_PER_GRE_YTD'. */
556
557 ELSIF p_balance_name = 'A_W2_GROSS_1099R' THEN
558
559 gvr_balance(1).bal_name := 'A_GROSS_EARNINGS_PER_GRE_YTD';
560
561 gvr_balance(1).bal_id := bal_db_item(substr(gvr_balance(1).bal_name,3));
562
563 ELSE
564 gvr_balance(1).bal_name := p_balance_name;
565 /* populate balance id */
566 gvr_balance(1).bal_id := bal_db_item(substr(gvr_balance(1).bal_name,3));
567
568 END IF;
569
570 END populate_balance_id;
571
572
573
574 procedure populate_balance_value (
575 p_assignment_action_id number
576 , p_balance_name varchar2
577 )
578 IS
579 BEGIN
580
581 IF p_balance_name = 'A_WAGES' THEN
582
583 gvr_balance(1).bal_value := nvl(pay_balance_pkg.get_value
584 (p_defined_balance_id => gvr_balance(1).bal_id,
585 p_assignment_action_id => p_assignment_action_id),0);
586
587 gvr_balance(2).bal_value := nvl(pay_balance_pkg.get_value
588 (p_defined_balance_id => gvr_balance(2).bal_id,
589 p_assignment_action_id => p_assignment_action_id),0);
590
591 gvr_balance(3).bal_value := nvl(pay_balance_pkg.get_value
592 (p_defined_balance_id => gvr_balance(3).bal_id,
593 p_assignment_action_id => p_assignment_action_id),0);
594
595 gvr_balance(4).bal_value := nvl(pay_balance_pkg.get_value
596 (p_defined_balance_id => gvr_balance(4).bal_id,
597 p_assignment_action_id => p_assignment_action_id),0);
598
599 gvr_balance(5).bal_value := nvl(pay_balance_pkg.get_value
600 (p_defined_balance_id => gvr_balance(5).bal_id,
601 p_assignment_action_id => p_assignment_action_id),0);
602 gvr_balance(6).bal_value := nvl(pay_balance_pkg.get_value
603 (p_defined_balance_id => gvr_balance(6).bal_id,
604 p_assignment_action_id => p_assignment_action_id),0);
605
606 ELSIF
607 p_balance_name = 'A_W2_STATE_WAGES' THEN
608
609 gvr_balance(1).bal_value := nvl(pay_balance_pkg.get_value
610 (p_defined_balance_id => gvr_balance(1).bal_id,
611 p_assignment_action_id => p_assignment_action_id),0);
612 gvr_balance(2).bal_value := nvl(pay_balance_pkg.get_value
613 (p_defined_balance_id => gvr_balance(2).bal_id,
614 p_assignment_action_id => p_assignment_action_id),0);
615 gvr_balance(3).bal_value := nvl(pay_balance_pkg.get_value
616 (p_defined_balance_id => gvr_balance(3).bal_id,
617 p_assignment_action_id => p_assignment_action_id),0);
618
619 ELSIF p_balance_name = 'A_SS_EE_TAXABLE_PER_GRE_YTD' THEN
620
621 gvr_balance(1).bal_value := nvl(pay_balance_pkg.get_value
622 (p_defined_balance_id => gvr_balance(1).bal_id,
623 p_assignment_action_id => p_assignment_action_id),0);
624 gvr_balance(2).bal_value := nvl(pay_balance_pkg.get_value
625 (p_defined_balance_id => gvr_balance(2).bal_id,
626 p_assignment_action_id => p_assignment_action_id),0);
627 ELSE
628 /* get the value of the dbi */
629 gvr_balance(1).bal_value := nvl(pay_balance_pkg.get_value
630 (p_defined_balance_id => gvr_balance(1).bal_id,
631 p_assignment_action_id => p_assignment_action_id),0);
632
633 END IF; /* A_WAGES */
634
635
636 END populate_balance_value;
637
638 /* This procedure assigns Zeros to all the values */
639
640 procedure zero_balance_value
641 IS
642 BEGIN
643
644
645 gvr_balance(1).bal_value := 0;
646 gvr_balance(2).bal_value := 0;
647 gvr_balance(3).bal_value := 0;
648 gvr_balance(4).bal_value := 0;
649 gvr_balance(5).bal_value := 0;
650 gvr_balance(6).bal_value := 0;
651
652 END Zero_balance_value;
653
654
655 /************************************************************
656 ** Following Function will will calculate the value of
657 ** balance based on balance name and return the calculated
658 ** value for federal and state Wages
659 ** For all other balances the values would be
660 ** single defined value.
661 **
662 ***************************************************************/
663
664 FUNCTION get_live_value (
665 p_balance_name varchar2
666 ) return number
667 IS
668 lvn_cal_value number ;
669 BEGIN
670 hr_utility.set_location(gv_package_name || '.get_live_value', 10);
671 hr_utility.trace('balance_name = ' || p_balance_name );
672 IF p_balance_name = 'A_WAGES' THEN
673 lvn_cal_value := gvr_balance(1).bal_value
674 + gvr_balance(2).bal_value
675 + gvr_balance(3).bal_value
676 + gvr_balance(4).bal_value
677 + gvr_balance(5).bal_value
678 - gvr_balance(6).bal_value;
679
680 ELSIF p_balance_name = 'A_W2_STATE_WAGES' THEN
681 lvn_cal_value := gvr_balance(1).bal_value
682 + gvr_balance(2).bal_value
683 - gvr_balance(3).bal_value ;
684 ELSIF p_balance_name = 'A_SS_EE_TAXABLE_PER_GRE_YTD' THEN
685
686 lvn_cal_value := gvr_balance(1).bal_value
687 - gvr_balance(2).bal_value ;
688 ELSE
689
690 lvn_cal_value := gvr_balance(1).bal_value ;
691 END IF;
692 return lvn_cal_value;
693 hr_utility.set_location(gv_package_name || '.get_live_value', 20);
694 END; /* Get Live value fucntion call */
695
696
697 /*****************************************************************
698 ** This procedure is called from the Concurrent Request. Based on
699 ** paramaters selected in SRS the report will compare the the
700 ** the Values and print the the values for the assignment that
701 ** have diffrent live and archive balances. The output format of
702 ** the report will be either a CSV format or an HTML format.
703 *****************************************************************/
704
705 PROCEDURE select_employee
706 (errbuf OUT nocopy varchar2,
707 retcode OUT nocopy number,
708 p_year IN VARCHAR2,
709 p_tax_unit_id IN NUMBER,
710 p_fed_state IN VARCHAR2,
711 p_is_state IN VARCHAR2,
712 p_state_code IN VARCHAR2,
713 p_box_type IN VARCHAR2, -- Bug 2554865
714 p_box_name IN VARCHAR2,
715 p_output_file_type IN VARCHAR2
716 )
717 IS
718
719
720 /************************************************************
721 ** Cursor to get all the employee and assignment data.
722 ** This cursor will return one row for each Assignment Action
723 ** based on the Selection parameter entered by the user
724 ** in the SRS.
725 ************************************************************/
726 /**************************************************************
727 Parameter for the Cursor :c_select_assignment
728 c_end_of_year -- 31st Dec. of the Year in date format
729 c_state_of_year -- 1st of Jan of Year in date format
730 c_gre_id -- GRE id in character Format
731 **************************************************************/
732
733 CURSOR c_select_assignment(c_end_of_year date,
734 c_start_of_year date,
735 c_gre_id varchar2 )
736 IS
737 SELECT assignment_action_id ,
738 serial_number ,
739 tax_unit_id
740 FROM pay_payroll_actions ppa, -- Year End
741 pay_assignment_actions paa, -- Year End
742 per_assignments_f paf
743 WHERE ppa.report_type = 'YREND'
744 AND ppa.action_status = 'C'
745 AND ppa.effective_date = c_end_of_year
746 AND ppa.legislative_parameters like c_gre_id || ' TRANSFER%'
747 AND ppa.payroll_action_id = paa.payroll_action_id
748 AND paa.action_status = 'C'
749 AND paf.assignment_id = paa.assignment_id
750 AND paf.effective_start_date = ( SELECT max(paf2.effective_start_date)
751 FROM per_assignments_f paf2
752 WHERE paf2.assignment_id = paf.assignment_id
753 AND paf2.effective_start_date <= c_end_of_year )
754 AND paf.effective_end_date >= c_start_of_year;
755
756
757
758 -- Bug 3361925 - Suppressed index on ppa.effective_date to improve performance
759 --
760
761 CURSOR c_live_ass_action_id(c_person_id number, c_tax_unit_id number,
762 c_start_of_year date, c_end_of_year date )
763 IS
764 SELECT paa.assignment_action_id
765 FROM
766 pay_assignment_actions paa,
767 per_assignments_f paf,
768 pay_payroll_actions ppa,
769 pay_action_classifications pac
770 WHERE paf.person_id = c_person_id
771 AND paa.assignment_id = paf.assignment_id
772 AND paa.tax_unit_id = c_tax_unit_id
773 AND paa.payroll_action_id = ppa.payroll_action_id
774 AND ppa.action_type = pac.action_type
775 AND pac.classification_name = 'SEQUENCED'
776 AND ppa.effective_date +0 BETWEEN paf.effective_start_date
777 AND paf.effective_end_date
778 AND ppa.effective_date +0 BETWEEN c_start_of_year and
779 c_end_of_year
780 order by paa.action_sequence desc;
781
782
783 /*************************************************************
784 Cursor to get state Abbrevaiation for the Selected State
785 *************************************************************/
786
787 Cursor c_selected_state
788 IS SELECT state_abbrev
789 FROM pay_us_states
790 where state_code = p_state_code;
791
792
793 /*************************************************************
794 Cursor to get Box Meaning
795 *************************************************************/
796
797 CURSOR c_box_description( c_lookup_type varchar2,
798 c_meaning varchar2
799 )
800 IS SELECT description,to_char(sysdate,'mm/dd/yyyy HH:MI')
801 FROM fnd_common_lookups
802 WHERE application_id = 801
803 AND lookup_type = c_lookup_type
804 AND meaning = c_meaning;
805
806 /*************************************************************
807 Cursor to get GRE name
808 *************************************************************/
809
810 CURSOR c_gre_name
811 IS SELECT name
812 FROM hr_organization_units
813 WHERE organization_id = p_tax_unit_id;
814
815 /*************************************************************
816 Cursor to get Employee Number
817 *************************************************************/
818
819 CURSOR c_employee_number ( c_person_id number )
820 IS SELECT employee_number
821 FROM per_people_f
822 WHERE person_id = c_person_id;
823
824 /*************************************************************
825 ** Local Variables
826 *************************************************************/
827 lvc_last_name VARCHAR2(150);
828 lvc_first_name VARCHAR2(150);
829
830 lb_print_row BOOLEAN := FALSE;
831
832 lv_header_label VARCHAR2(32000);
833 /* Changed from 32000 to 22000 and 100000 */
834 lv_header_label1 VARCHAR2(22000);
835 lv_header_label2 VARCHAR2(10000);
836
837 lv_report_asgn VARCHAR2(1) := 'N';
838 lv_ VARCHAR2(32000);
839 lv_data_row VARCHAR2(32000);
840
841 lvc_name VARCHAR2(300);
842 lvc_label1 VARCHAR2(32000);
843 lvc_national_identifier VARCHAR2(100);
844 lvc_employee_number VARCHAR2(100);
845 lvn_person_id NUMBER;
846 lvc_gre_name hr_organization_units.name%type;
847 lvc_state_abbrev pay_us_states.state_abbrev%type := null;
848 lvc_jurisdiction varchar2(11) := '00-000-0000';
849 lvn_tax_unit_id number := -1;
850 lvc_tax_unit_id varchar2(15);
851 lvc_date_time varchar2(50);
852 lvd_effective_date date ;
853 lvd_end_of_year date;
854 lvd_start_of_year date;
855 lvc_year varchar2(4);
856 lvn_level number := 0;
857 lvn_archive_value number := 0;
858 lvn_live_value number := 0;
859 lvc_lookup_type fnd_common_lookups.lookup_type%type;
860 lvc_lookup_meaning fnd_common_lookups.meaning%type;
861 lvc_lookup_description fnd_common_lookups.description%type;
862 lvn_diff_value number := 0;
863 lvc_balance_name varchar2(240);
864 lvn_employee_count number := 0;
865 lvn_diff_count number := 0;
866 lvn_live_aaid number := 0;
867 lvc_message varchar2(32000);
868 BEGIN
869
870 hr_utility.set_location(gv_package_name || '.select_employee', 10);
871 /* build the jurisdiction code based on State Code */
872 IF p_fed_state = 'Federal' THEN
873 /* Bug 2554865 - Modified to handle 1099R specific Federal lookup */
874 IF p_box_type = 'W-2' THEN
875 lvc_lookup_type := 'US_FEDERAL_LIVE_ARCHIVE';
876 ELSIF p_box_type = '1099-R' THEN
877 lvc_lookup_type := 'US_FEDERAL_1099R_LIVE_ARCHIVE';
878 ELSE
879 null;
880 END IF;
881 lvc_jurisdiction := '00-000-0000';
882 lvc_lookup_meaning := p_box_name;
883 lvn_level := 0;
884 ELSIF p_fed_state = 'State' THEN
885 /* Bug 2554865 - Modified to handle 1099R specific State lookup */
886 IF p_box_type = 'W-2' THEN
887 lvc_lookup_type := 'US_STATE_LIVE_ARCHIVE';
888 ELSIF p_box_type = '1099-R' THEN
889 lvc_lookup_type := 'US_STATE_1099R_LIVE_ARCHIVE';
890 ELSE
891 null;
892 END IF;
893 lvc_jurisdiction := p_state_code || '-000-0000' ;
894 lvc_lookup_meaning := p_box_name;
895 lvn_level := 2;
896 ELSE
897 null;
898 END IF;
899
900 lvc_balance_name := p_box_name ;
901
902 gvr_balance.delete;
903
904 populate_balance_id(p_box_name);
905
906
907 /* Build the tax Unit Id */
908 lvn_tax_unit_id := p_tax_unit_id;
909 lvc_tax_unit_id := to_char(lvn_tax_unit_id);
910
911 /* Builiding Date and Year */
912 lvd_start_of_year := fnd_date.canonical_to_date(p_year);
913 lvd_end_of_year := add_months(fnd_date.canonical_to_date(p_year),12) -1;
914 lvc_year := to_char(fnd_date.canonical_to_date(p_year),'YYYY');
915
916
917 BEGIN
918
919
920 OPEN c_box_description( lvc_lookup_type,
921 lvc_Lookup_meaning
922 );
923 FETCH c_box_description INTO lvc_lookup_description,lvc_date_time;
924 CLOSE c_box_description;
925
926 OPEN c_selected_state;
927 FETCH c_selected_state INTO lvc_state_abbrev;
928 CLOSE c_selected_state;
929
930 OPEN c_gre_name;
931 FETCH c_gre_name INTO lvc_gre_name;
932 CLOSE c_gre_name;
933 lvc_gre_name := replace(lvc_gre_name, ',',' ');
934
935
936 EXCEPTION
937 WHEN OTHERS THEN
938 hr_utility.trace(' State Abbereviation or GRE Name cursor failed');
939 END;
940
941 hr_utility.set_location(gv_package_name || '.select_employee', 20);
942 -- code is hard coded to get only one value dJoshi will change and will be based on value
943
944
945 /* set the context for the give Tax unit_id and jurisdiction only once in report */
946
947 pay_balance_pkg.set_context ('JURISDICTION_CODE',lvc_jurisdiction);
948 pay_balance_pkg.set_context ('TAX_UNIT_ID',lvn_tax_unit_id);
949
950
951 -- djoshi write the utility trace over here
952 /* get the assignment */
953
954 for i in c_select_assignment(lvd_end_of_year,
955 lvd_start_of_year,
956 lvc_tax_unit_id ) loop
957 lvn_person_id := to_number(i.serial_number);
958
959
960
961
962 hr_utility.set_location(gv_package_name || '.select_employee', 30);
963 /* Get the assignment action id for the live call */
964
965 open c_live_ass_action_id(lvn_person_id, lvn_tax_unit_id,
966 lvd_start_of_year , lvd_end_of_year );
967
968 FETCH c_live_ass_action_id INTO lvn_live_aaid;
969 close c_live_ass_action_id;
970
971
972 /* Get archive balance */
973
974
975
976 lvn_archive_value := get_archive_value ( i.assignment_action_id,
977 lvc_balance_name,
978 lvn_tax_unit_id,
979 lvc_jurisdiction,
980 lvn_level
981 ) ;
982
983 /* Get Live Balance */
984 populate_balance_value(lvn_live_aaid,p_box_name);
985
986 hr_utility.set_location(gv_package_name || '.select_employee', 40);
987 lvn_live_value := get_live_value (
988 lvc_balance_name
989 ) ;
990
991 hr_utility.set_location(gv_package_name || '.select_employee', 50);
992 /* Compare the Balances */
993 IF lvn_live_value <> lvn_archive_value THEN
994 /* Get the value of person */
995 null;
996 /* get person Value */
997
998 hr_utility.set_location(gv_package_name || '.select_employee', 60);
999 lvc_last_name := hr_us_w2_rep.get_per_item(i.assignment_action_id,'A_PER_LAST_NAME' );
1000 lvc_first_name :=hr_us_w2_rep.get_per_item(i.assignment_action_id,'A_PER_FIRST_NAME' );
1001 lvc_national_identifier := nvl(hr_us_w2_rep.get_per_item(i.assignment_action_id, 'A_PER_NATIONAL_IDENTIFIER'),'Applied For');
1002 open c_employee_number(i.serial_number);
1003 FETCH c_employee_number INTO lvc_employee_number ;
1004 close c_employee_number ;
1005
1006 lvc_name := lvc_first_name || ' ' || lvc_last_name ;
1007 lvc_name := replace(lvc_name,',',' ');
1008
1009 hr_utility.set_location(gv_package_name || '.select_employee', 70);
1010
1011 /* Print header for the first time for Federal or state comparision*/
1012 if lvn_diff_count = 0 THEN
1013
1014 fnd_file.put_line(fnd_file.output, formated_header_string(
1015 gv_title || ':- Tax Year: ' || lvc_year || ' ( ' || lvc_date_time || ' ) '
1016 ,p_output_file_type
1017 ));
1018
1019 IF p_output_file_type ='HTML' THEN
1020 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1021 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1022 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1023 END IF;
1024
1025
1026 IF p_fed_state = 'Federal' AND lvn_diff_count = 0 THEN
1027 fnd_file.put_line(fnd_file.output,formated_header_federal( p_output_file_type));
1028 lvc_label1 := formated_header_federal( p_output_file_type);
1029
1030 ELSIF p_fed_state = 'State' and lvn_diff_count = 0 THEN
1031 fnd_file.put_line(fnd_file.output,formated_header_state( p_output_file_type));
1032 lvc_label1 := formated_header_state ( p_output_file_type);
1033 END IF; /* p_fed_state = Federal */
1034
1035 IF p_output_file_type ='HTML' THEN
1036 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1037 END IF;
1038 END IF;
1039 lvn_diff_value := lvn_archive_value - lvn_live_value ;
1040
1041 /* print details based on record */
1042
1043 hr_utility.set_location(gv_package_name || '.select_employee', 80);
1044 IF p_fed_state = 'Federal' THEN
1045
1046
1047 lv_data_row := formated_detail_federal(
1048 p_output_file_type
1049 ,lvc_year
1050 ,lvc_gre_name
1051 ,lvc_name
1052 ,lvc_national_identifier
1053 ,lvc_employee_number
1054 ,lvc_lookup_description
1055 ,lvn_live_value
1056 ,lvn_archive_value
1057 ,lvn_diff_value );
1058
1059 if p_output_file_type ='HTML' then
1060 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1061 end if;
1062
1063 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1064
1065
1066 hr_utility.set_location(gv_package_name || '.select_employee', 90);
1067 ELSIF p_fed_state = 'State' THEN
1068
1069
1070 lv_data_row := formated_detail_state(
1071 p_output_file_type
1072 ,lvc_year
1073 ,lvc_gre_name
1074 ,lvc_state_abbrev
1075 ,lvc_jurisdiction
1076 ,lvc_name
1077 ,lvc_national_identifier
1078 ,lvc_employee_number
1079 ,lvc_lookup_description
1080 ,lvn_live_value
1081 ,lvn_archive_value
1082 ,lvn_diff_value
1083 );
1084
1085 IF p_output_file_type ='HTML' THEN
1086 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1087 end if;
1088
1089 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1090
1091 hr_utility.set_location(gv_package_name || '.select_employee', 100);
1092
1093 END IF; /* to print the details */
1094
1095 /* increament the count of Imployee */
1096
1097 lvn_diff_count := lvn_diff_count + 1;
1098
1099 END IF; /* if balance dont match */
1100
1101 lvn_employee_count := lvn_employee_count + 1;
1102
1103 /* re-initalizing value to zero */
1104 lvn_live_value := 0;
1105 lvn_archive_value := 0;
1106 zero_balance_value();
1107 end loop; /* for assignment picked up */
1108
1109 /* Zero Employee were compared up by Report */
1110
1111
1112 IF lvn_employee_count = 0 THEN
1113 formated_zero_count(p_output_file_type);
1114 hr_utility.set_location(gv_package_name || '.select_employee', 110);
1115 END IF ;
1116
1117 /* If there was anything written then clsoe for HTML format */
1118
1119 /* Bug 2554865 - Modified Employee's to Employees */
1120 lvc_message := 'For ' || lvc_lookup_description || ', Number of Employees compared = '
1121 || to_char(lvn_employee_count) || ', Number of Employees with difference = '
1122 || to_char(lvn_diff_count);
1123
1124 IF lvn_employee_count > 0 THEN
1125 IF lvn_diff_count > 0 THEN
1126 IF p_output_file_type ='HTML' THEN
1127 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1128 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1129 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lvc_message);
1130 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</body></html>');
1131 ELSE
1132 formated_no_diff(p_output_file_type,lvc_lookup_description,
1133 lvn_employee_count, lvn_diff_count);
1134 END IF;
1135 ELSE
1136 IF p_output_file_type = 'HTML' THEN
1137 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lvc_message);
1138 ELSE
1139 formated_no_diff(p_output_file_type,lvc_lookup_description,
1140 lvn_employee_count, lvn_diff_count);
1141 END IF; /* if html */
1142
1143 END IF; /* count > 0 */
1144 hr_utility.set_location(gv_package_name || '.select_employee', 130);
1145 END IF; /* IF employee Count > 0 */
1146
1147
1148 IF p_output_file_type ='HTML' THEN
1149 UPDATE fnd_concurrent_requests
1150 SET output_file_type = 'HTML'
1151 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1152
1153 COMMIT;
1154 END IF;
1155
1156 hr_utility.set_location(gv_package_name || '.select_employee', 160);
1157
1158 END select_employee;
1159
1160
1161
1162 --begin
1163 --hr_utility.trace_on(null, 'ORACLE');
1164 end pay_livearchive_pkg;