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