[Home] [Help]
PACKAGE BODY: APPS.PAY_US_YEPP_MISS_ASSIGN_PKG
Source
1 PACKAGE BODY pay_us_yepp_miss_assign_pkg AS
2 /* $Header: pyusyema.pkb 120.4 2005/09/21 02:04:14 sdhole noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disCLOSEd to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_yepp_miss_assign_pkg
21
22 File : pyusyema.pkb
23
24 Description : Package for the YEPP missing assignments report.
25 The package generates the output file in the specified
26 user format. The current formats supported are
27 - HTML
28 - CSV
29
30 Change List
31 -----------
32 Date Name Vers Bug No Description
33 ---- ---- ------ ------- -----------
34 25-AUG-2003 rsethupa 115.0 2527077 Created.
35 27-AUG-2003 rsethupa 115.1 2527077 Modified script to
36 create assignment set
37 only if it does not
38 already exist.
39 29-AUG-2003 rsethupa 115.2 2527077 Included cursor to
40 check for existing
41 record in
42 hr_assignment_set_amendments
43 Moved the functions
44 formated_header_string and
45 formated_data_string to
46 pyusutil.pkb. Modified cursor
47 c_missing_assignments to select
48 only primary assignments.
49 29-AUG-2003 rsethupa 115.3 2527077 Added Comments.
50 10-SEP-2003 rsethupa 115.4 3135440 Modified call to procedure
51 hr_assignment_sets_pkg.insert_row
52 to create assignment set, by
53 passing the business_group_id
54 of the assignment instead of
55 tax unit ID/GRE ID
56 10-NOV-2003 sodhingr 115.5 3228332 Changed the procedure select_employee
57 - modified the cursor c_missing_assignment
58 - reset the flag lv_result_value.
59 07-APR-2004 ssmukher 115.6 3558449 Modified the cursor c_missing_assignments
60 in the procedure select_employee.
61 The RULE based hint in the cursor
62 c_non_zero_run_result is also removed
63 03-AUG-2004 meshah 115.7 3440806 Modified the cursor c_missing_assignments
64 removed the join that checks for
65 primary assignment.
66 17-SEP-2004 meshah 115.8 3505495 Changed CURSOR c_non_zero_run_result in
67 PROCEDURE select_employee.
68 added a check for balance feeds .
69 25-AUG-2005 rsethupa 115.9 4160436 1. Rewrite of the Package for
70 Multithreading of Report.
71 2. Added section to Display Elements
72 05-SEP-2005 rsethupa 115.10 Corrected name of lookup_type to
73 YE_ARCH_REPORTS_BAL_ATTRIBUTES in
74 Element Information section
75 16-SEP-2005 sdhole 115.11 4613898 Modified the Cursor
76 c_get_missing_assignments.
77 21-SEP-2005 sdhole 115.12 Commented the code which displays
78 the Element information.
79 /************************************************************
80 ** Local Package Variables
81 ************************************************************/
82 gv_title VARCHAR2(100) := ' Year End Archive Missing Assignments Report';
83
84 gv_package_name VARCHAR2(50) := 'pay_us_yepp_miss_assign_pkg';
85
86 /**********************************************************************
87 Function to display the Titles of the columns of the employee details
88 **********************************************************************/
89 FUNCTION formated_header_string(
90 p_output_file_type in VARCHAR2
91 )RETURN VARCHAR2
92 IS
93
94 lv_format1 VARCHAR2(32000);
95
96 BEGIN
97
98 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
99 lv_format1 :=
100 pay_us_payroll_utils.formated_data_string (p_input_string => 'Year '
101 ,p_bold => 'Y'
102 ,p_output_file_type => p_output_file_type) ||
103 pay_us_payroll_utils.formated_data_string (p_input_string => 'GRE '
104 ,p_bold => 'Y'
105 ,p_output_file_type => p_output_file_type) ||
106
107 pay_us_payroll_utils.formated_data_string (p_input_string => 'Employee Name '
108 ,p_bold => 'Y'
109 ,p_output_file_type => p_output_file_type) ||
110 pay_us_payroll_utils.formated_data_string (p_input_string => 'Employee SS # '
111 ,p_bold => 'Y'
112 ,p_output_file_type => p_output_file_type) ||
113 pay_us_payroll_utils.formated_data_string (p_input_string => 'Employee #'
114 ,p_bold => 'Y'
115 ,p_output_file_type => p_output_file_type) ;
116
117 hr_utility.trace('Static Label1 = ' || lv_format1);
118
119
120 return lv_format1 ;
121
122 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
123
124 END formated_header_string;
125
126
127
128 /***************************************************************
129 Function to display the details of the selected employee
130 ***************************************************************/
131
132 FUNCTION formated_detail_string(
133 p_output_file_type in VARCHAR2
134 ,p_year VARCHAR2
135 ,p_gre VARCHAR2
136 ,p_Employee_name VARCHAR2
137 ,p_employee_ssn VARCHAR2
138 ,p_emplyee_number VARCHAR2
139
140 ) RETURN VARCHAR2
141 IS
142
143 lv_format1 VARCHAR2(22000);
144
145 BEGIN
146
147 hr_utility.set_location(gv_package_name || '.formated_detail_string', 10);
148 lv_format1 :=
149 pay_us_payroll_utils.formated_data_string (p_input_string => p_year
150 ,p_bold => 'N'
151 ,p_output_file_type => p_output_file_type) ||
152 pay_us_payroll_utils.formated_data_string (p_input_string => p_gre
153 ,p_bold => 'N'
154 ,p_output_file_type => p_output_file_type) ||
155 pay_us_payroll_utils.formated_data_string (p_input_string => p_employee_name
156 ,p_bold => 'N'
157 ,p_output_file_type => p_output_file_type) ||
158 pay_us_payroll_utils.formated_data_string (p_input_string => P_employee_ssn
159 ,p_bold => 'N'
160 ,p_output_file_type => p_output_file_type) ||
161 pay_us_payroll_utils.formated_data_string (p_input_string => p_emplyee_number
162 ,p_bold => 'N'
163 ,p_output_file_type => p_output_file_type);
164
165
166 hr_utility.trace('Static Label1 = ' || lv_format1);
167 return lv_format1;
168
169 hr_utility.set_location(gv_package_name || '.formated_detail_string', 30);
170
171
172
173 END formated_detail_string;
174
175
176 /**************************************************************************
177 Procedure to display message if no employees are selected
178 *************************************************************************/
179
180 PROCEDURE formated_zero_count(output_file_type VARCHAR2)
181 IS
182 lvc_message VARCHAR2(200);
183 lvc_return_message VARCHAR2(400);
184 BEGIN
185 null;
186 lvc_message := 'No person was picked up based on selection parameters.' ||
187 ' The YEPP Archive for the GRE has no missing assignments.';
188 hr_utility.set_location(gv_package_name || '.formated_zero_count', 10);
189 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
190 pay_us_payroll_utils.formated_data_string (p_input_string => lvc_message
191 ,p_bold => 'N'
192 ,p_output_file_type => output_file_type));
193 hr_utility.set_location(gv_package_name || '.formated_zero_count', 20);
194 END;
195
196 /**************************************************************************
197 Procedure to display the name of the assignment set to which the selected
198 assignments are added
199 ************************************************************************/
200
201 PROCEDURE formated_assign_count(assignment_set_name in VARCHAR2,
202 assignment_set_id in number,
203 record_count in number,
204 assign_set_created in number,
205 output_file_type in VARCHAR2)
206 is
207 lvc_message1 VARCHAR2(400);
208 lvc_message2 VARCHAR2(400);
209 lvc_message3 VARCHAR2(400);
210 BEGIN
211 IF assign_set_created=1 THEN
212 lvc_message1 := 'Assignment Set Created : ' || assignment_set_name ;
213 ELSE
214 lvc_message1 := 'Assignment Set Name : ' || assignment_set_name ;
215 END IF;
216 lvc_message2 := 'Assignment Set ID : ' || to_char(assignment_set_id);
217 lvc_message3 := 'Number of employees added to the assignment set : ' ||
218 to_char(record_count);
219 hr_utility.set_location(gv_package_name || '.formated_assign_count', 10);
220
221 IF output_file_type ='HTML' THEN
222 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br>');
223 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br>');
224 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table align=center>');
225
226
227 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<tr>'||
228 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message1,
229 p_bold => 'N',
230 p_output_file_type => output_file_type)||'</tr>');
231
232 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<tr>'||
233 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message2,
234 p_bold => 'N',
235 p_output_file_type => output_file_type)||'</tr>');
236
237 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<tr>'||
238 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message3,
239 p_bold => 'N',
240 p_output_file_type => output_file_type)||'</tr>');
241 ELSE
242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
243 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message1,
244 p_bold => 'N',
245 p_output_file_type => output_file_type));
246
247 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
248 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message2,
249 p_bold => 'N',
250 p_output_file_type => output_file_type));
251
252 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
253 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message3,
254 p_bold => 'N',
255 p_output_file_type => output_file_type));
256 END IF;
257
258 hr_utility.set_location(gv_package_name || '.formated_assign_count', 20);
259 END;
260
261 /**************************************************************************
262 Procedure to display the Elements having input values of type Money
263 and not feeding the YE Balances
264 ************************************************************************/
265
266 PROCEDURE formated_element_header(
267 p_output_file_type in VARCHAR2
268 ,p_static_label out nocopy VARCHAR2
269 )
270 IS
271
272 lv_format VARCHAR2(32000);
273
274 BEGIN
275 hr_utility.set_location(gv_package_name || '.formated_element_header.',10);
276 lv_format:= pay_us_payroll_utils.formated_data_string(p_input_string=>'Element Name'
277 ,p_output_file_type=>p_output_file_type
278 ,p_bold=>'Y')||
279 pay_us_payroll_utils.formated_data_string(p_input_string=>'Classification'
280 ,p_output_file_type=>p_output_file_type
281 ,p_bold=>'Y')||
282 pay_us_payroll_utils.formated_data_string(p_input_string=>'Input Value Name'
283 ,p_output_file_type=>p_output_file_type
284 ,p_bold=>'Y');
285
286 p_static_label := lv_format;
287 hr_utility.trace('Static Label = ' || p_static_label);
288 hr_utility.set_location(gv_package_name || '.formated_element_header', 20);
289
290 END formated_element_header;
291
292 /************************************************************
293 ** Procedure: formated_element_row
294 ** Returns : Formatted Element Row
295 ************************************************************/
296
297 PROCEDURE formated_element_row (
298 p_element_name in varchar2
299 ,p_classification in varchar2
300 ,p_input_value_name in VARCHAR2
301 ,p_output_file_type in VARCHAR2
302 ,p_static_data out nocopy VARCHAR2
303 )
304 IS
305
306 lv_format VARCHAR2(32000);
307
308 BEGIN
309
310 hr_utility.set_location(gv_package_name || '.formated_element_row', 10);
311
312 lv_format :=
313 pay_us_payroll_utils.formated_data_string (p_input_string=>p_element_name
314 ,p_output_file_type=>p_output_file_type
315 ,p_bold=>'N'
316 )||
317 pay_us_payroll_utils.formated_data_string (p_input_string=>p_classification
318 ,p_output_file_type=>p_output_file_type
319 ,p_bold=>'N'
320 )||
321 pay_us_payroll_utils.formated_data_string (p_input_string=>p_input_value_name
322 ,p_output_file_type=>p_output_file_type
323 ,p_bold=>'N'
324 );
325
326 hr_utility.set_location(gv_package_name || '.formated_element_row', 20);
327
328 p_static_data := lv_format;
329 hr_utility.trace('Static Data = ' || lv_format);
330 hr_utility.set_location(gv_package_name || '.formated_element_row', 30);
331
332 END formated_element_row;
333
334 /* ******************************************************
335 Name: select_employee
336 Description: This procedure fetches the assignments
337 archived in PAY_US_RPT_TOTALS by the
338 package PAY_ARCHIVE_MISSING_ASG_PKG
339 and generates the report in the specified
340 format.
341 *****************************************************/
342
343
344 PROCEDURE select_employee(p_payroll_action_id IN NUMBER,
345 p_effective_date IN VARCHAR2,
346 p_tax_unit_id IN NUMBER,
347 p_session_id in NUMBER)
348
349 is
350
351
352 CURSOR c_gre_name(p_tax_unit_id number)
353 IS
354 SELECT name
355 FROM hr_organization_units
356 WHERE organization_id = p_tax_unit_id;
357
358 CURSOR c_person_id (c_assign_id number)
359 IS
360 SELECT person_id,business_group_id
361 FROM per_all_assignments_f
362 WHERE assignment_id=c_assign_id;
363
364 /* Cursor to get Employee details */
365
366 CURSOR c_employee_details ( c_person_id number )
367 IS
368 SELECT employee_number,full_name,national_identifier
369 FROM per_people_f
370 WHERE person_id = c_person_id;
371
372 CURSOR c_assignment_set_id
373 IS
374 SELECT hr_assignment_sets_s.nextval
375 FROM dual;
376
377 CURSOR c_assignment_set_exists(assign_set_name VARCHAR2)
378 IS
379 SELECT assignment_set_id
380 FROM hr_assignment_sets
381 WHERE assignment_set_name=assign_set_name;
382
383 CURSOR c_assignment_amd_exists(c_assignment_id number,c_assignment_set_id number)
384 IS
385 SELECT 1
386 FROM hr_assignment_set_amendments
387 WHERE assignment_set_id=c_assignment_set_id
388 AND assignment_id=c_assignment_id;
389
390 CURSOR c_get_business_group_id(c_tax_unit_id number
391 )
392 IS
393 SELECT business_group_id
394 FROM hr_organization_units
395 WHERE organization_id = c_tax_unit_id;
396
397 CURSOR c_get_missing_assignments(cp_payroll_action_id NUMBER,
398 cp_tax_unit_id NUMBER)
399 IS
400 SELECT distinct value1
401 FROM PAY_US_RPT_TOTALS
402 WHERE location_id = cp_payroll_action_id
403 AND tax_unit_id = cp_tax_unit_id
404 AND attribute1 = 'YEAR END MISSING ASSIGNMENTS';
405
406 CURSOR c_get_legislation_code(cp_business_group_id NUMBER
407 ) IS
408 SELECT legislation_code
409 FROM per_business_groups
410 WHERE business_group_id = cp_business_group_id;
411
412 CURSOR c_get_elements(cp_business_group_id NUMBER,
413 cp_legislation_code VARCHAR2
414 )
415 IS
416 select distinct element_name, classification_name,piv.name
417 from pay_element_types_f pet,
418 pay_element_classifications pec,
419 pay_input_values_f piv
420 where pet.element_type_id = piv.element_type_id
421 and piv.uom = 'M'
422 and pet.classification_id = pec.classification_id
423 and pet.business_group_id = cp_business_group_id
424 and (pec.legislation_code = cp_legislation_code or
425 pec.business_group_id = cp_business_group_id)
426 and piv.input_value_id not in
427 (
428 select distinct piv.input_value_id
429 from pay_element_types_f pet,
430 pay_input_values_f piv,
431 pay_balance_feeds_f pbf,
432 pay_balance_types pbt,
433 pay_defined_balances pdb,
434 pay_balance_attributes pba,
435 pay_bal_attribute_definitions pbad
436 where pet.element_type_id = piv.element_type_id
437 and pet.business_group_id = cp_business_group_id
438 and piv.uom = 'M'
439 and piv.input_value_id = pbf.input_value_id
440 and pbf.balance_type_id = pbt.balance_type_id
441 and pbt.balance_type_id = pdb.balance_type_id
442 and pdb.defined_balance_id = pba.defined_balance_id
443 and pba.attribute_id = pbad.attribute_id
444 and pbad.legislation_code = cp_legislation_code
445 and pbad.attribute_name in
446 (select distinct fcl.lookup_code
447 from fnd_common_lookups fcl,
448 fnd_lookup_values flv
449 where fcl.lookup_type = 'YE_ARCH_REPORTS_BAL_ATTRIBUTES'
450 and fcl.lookup_type = flv.lookup_type
451 and flv.tag = '+' || cp_legislation_code
452 and fcl.lookup_code = flv.lookup_code
453 )
454 );
455
456 lv_result_value number:=0;
457 lv_person_id per_people_f.person_id%type;
458 lv_gre_name hr_organization_units.name%type;
459 lv_emp_name per_people_f.full_name%type;
460 lv_emp_no per_people_f.employee_number%type;
461 lv_emp_ssn per_people_f.national_identifier%type;
462 lv_data_row VARCHAR2(4000);
463 row_id VARCHAR2(100);
464 lv_miss_assignments NUMBER :=0;
465 lv_effective_date date;
466 lv_assignment_set_id number :=0;
467 lv_payroll_id number :=NULL;
468 lv_formula_id number :=NULL;
469 lv_assign_set_created number :=0;
470 lv_assignment_amd_exists number:=0;
471 lv_business_group_id per_all_assignments_f.business_group_id%TYPE;
472 lv_run_balance_status varchar2(1) := 'N';
473 lv_balance_attribute_id NUMBER;
474 lv_assignment_set VARCHAR2(100);
475 lv_legislative_param varchar2(240);
476 lv_assignment_id NUMBER(15);
477 lv_output_file_type VARCHAR2(100);
478
479 lv_title VARCHAR2(1000);
480 lv_header_label VARCHAR2(1000);
481 lv_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
482 lv_classification_name PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_NAME%TYPE;
483 lv_input_value_name PAY_INPUT_VALUES_F.NAME%TYPE;
484 lv_element_row VARCHAR2(1000);
485 lv_element_count NUMBER;
486 lv_legislation_code varchar2(100);
487 lv_element_info varchar2(200);
488 lv_element_count_info varchar2(200);
489
490 BEGIN
491 --hr_utility.trace_on(null,'PYUSYEMA');
492 hr_utility.set_location(gv_package_name || '.select_employee', 10);
493 hr_utility.trace('p_payroll_action_id = ' || p_payroll_action_id);
494 hr_utility.trace('p_effective_date = ' || p_effective_date);
495 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);
496 hr_utility.trace('p_session_id = ' || p_session_id);
497
498 lv_effective_date := fnd_date.canonical_to_date(FND_DATE.date_to_canonical(p_effective_date));
499
500 select legislative_parameters
501 into lv_legislative_param
502 from pay_payroll_actions
503 where payroll_action_id = p_payroll_action_id;
504
505 select pay_us_payroll_utils.get_parameter(
506 'ASSIGNMENT_SET',
507 lv_legislative_param),
508 pay_us_payroll_utils.get_parameter(
509 'OUTPUT_TYPE',
510 lv_legislative_param)
511 into lv_assignment_set, lv_output_file_type
512 from dual;
513
514 hr_utility.set_location(gv_package_name || '.select_employee', 20);
515 hr_utility.trace('lv_assignment_set = ' || lv_assignment_set);
516 hr_utility.trace('lv_output_file_type = ' || lv_output_file_type);
517
518 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
519 pay_us_payroll_utils.formated_header_string(gv_title || ':- Tax Year: ' ||
520 to_char(lv_effective_date,'YYYY'),lv_output_file_type ));
521
522 open c_get_business_group_id(p_tax_unit_id);
523 fetch c_get_business_group_id into lv_business_group_id;
524 close c_get_business_group_id;
525
526 open c_get_legislation_code(lv_business_group_id);
527 fetch c_get_legislation_code into lv_legislation_code;
528 close c_get_legislation_code;
529 /* Report assignments picked up from PAY_US_RPT_TOTALS*/
530
531 hr_utility.set_location(gv_package_name || '.select_employee', 30);
532
533 OPEN c_get_missing_assignments(p_payroll_action_id, p_tax_unit_id);
534 LOOP
535 FETCH c_get_missing_assignments into lv_assignment_id;
536 hr_utility.trace('lv_assignment_id = ' || lv_assignment_id);
537 EXIT when c_get_missing_assignments%NOTFOUND;
538
539 lv_assignment_amd_exists:=0;
540 hr_utility.set_location(gv_package_name || '.select_employee', 40);
541 IF lv_assignment_id IS NOT NULL THEN
542 OPEN c_gre_name(p_tax_unit_id);
543 FETCH c_gre_name into lv_gre_name;
544 CLOSE c_gre_name;
545
546 OPEN c_person_id(lv_assignment_id);
547 FETCH c_person_id into lv_person_id,lv_business_group_id;
548 CLOSE c_person_id;
549
550 OPEN c_employee_details(lv_person_id);
551 FETCH c_employee_details into lv_emp_no,lv_emp_name,lv_emp_ssn;
552 CLOSE c_employee_details;
553
554 /*create assignment set only when the first row is fetched*/
555 hr_utility.set_location(gv_package_name || '.select_employee', 50);
556 IF lv_miss_assignments=0 THEN
557 IF lv_output_file_type ='HTML' THEN
558 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
559 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=center>');
560 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
561 END IF;
562
563
564 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,formated_header_string( lv_output_file_type));
565
566 IF lv_output_file_type ='HTML' THEN
567 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
568 END IF;
569
570 hr_utility.set_location(gv_package_name || '.select_employee', 60);
571
572 OPEN c_assignment_set_exists(lv_assignment_set);
573 FETCH c_assignment_set_exists into lv_assignment_set_id;
574 CLOSE c_assignment_set_exists;
575
576 /*if assignment set does not exist,create a new one*/
577 IF lv_assignment_set_id=0 THEN
578 OPEN c_assignment_set_id;
579 FETCH c_assignment_set_id into lv_assignment_set_id;
580 CLOSE c_assignment_set_id;
581 hr_assignment_sets_pkg.insert_row(row_id,
582 lv_assignment_set_id,
583 lv_business_group_id,
584 lv_payroll_id,
585 lv_assignment_set,
586 lv_formula_id);
587 lv_assign_set_created:=1;
588 END IF;
589
590 hr_utility.set_location(gv_package_name || '.select_employee', 70);
591
592 END IF; /*lv_miss_assignments = 0 */
593
594 IF lv_assign_set_created=0 THEN
595 hr_utility.set_location(gv_package_name || '.select_employee', 80);
596 OPEN c_assignment_amd_exists(lv_assignment_id,lv_assignment_set_id);
597 FETCH c_assignment_amd_exists into lv_assignment_amd_exists;
598 CLOSE c_assignment_amd_exists;
599
600 IF lv_assignment_amd_exists=0 THEN
601 hr_assignment_set_amds_pkg.insert_row(row_id,lv_assignment_id,lv_assignment_set_id,'I');
602 END IF;
603 ELSE
604 hr_assignment_set_amds_pkg.insert_row(row_id,lv_assignment_id,lv_assignment_set_id,'I');
605 END IF; /*lv_assign_set_created=0 */
606
607 lv_miss_assignments := lv_miss_assignments + 1;
608 hr_utility.set_location(gv_package_name || '.select_employee', 90);
609
610
611 lv_data_row := formated_detail_string(
612 lv_output_file_type
613 ,to_char(lv_effective_date,'YYYY')
614 ,lv_gre_name
615 ,lv_emp_name
616 ,lv_emp_ssn
617 ,lv_emp_no);
618 IF lv_output_file_type ='HTML' THEN
619 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
620 END IF;
621 hr_utility.set_location(gv_package_name || '.select_employee', 40);
622 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
623
624 END IF; /* lv_assignment_id IS NOT NULL */
625 END LOOP; /*loop c_get_missing_assignments */
626 CLOSE c_get_missing_assignments;
627 hr_utility.set_location(gv_package_name || '.select_employee', 100);
628
629 IF lv_output_file_type='HTML' THEN
630 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
631
632 END IF;
633
634 IF lv_miss_assignments=0 THEN
635 formated_zero_count(lv_output_file_type);
636 hr_utility.set_location(gv_package_name || '.select_employee', 110);
637 ELSE
638 formated_assign_count(lv_assignment_set,
639 lv_assignment_set_id,
640 lv_miss_assignments,
641 lv_assign_set_created,
642 lv_output_file_type);
643 hr_utility.set_location(gv_package_name || '.select_employee', 120);
644
645 END IF;
646
647 IF lv_output_file_type ='HTML' THEN
648 UPDATE fnd_concurrent_requests
649 SET output_file_type = 'HTML'
650 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
651 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
652 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</body>');
653 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</HTML>');
654
655 COMMIT;
656 END IF;
657 hr_utility.set_location(gv_package_name || '.select_employee', 130);
658
659 /* Code to Display Element List */
660
661 /* Commented the code not to display the Element Information
662
663 lv_element_count := 0;
664
665 lv_title := 'Element Information';
666 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_header_string(
667 lv_title
668 ,lv_output_file_type
669 ));
670
671 lv_element_info := 'Elements With Input Value of Type Money and Not Feeding Year End Balances';
672
673 IF lv_output_file_type ='HTML' THEN
674 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<p align=center>');
675 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
676 pay_us_payroll_utils.formated_data_string (p_input_string =>lv_element_info,
677 p_bold => 'N',
678 p_output_file_type => lv_output_file_type)||'</p>');
679 ELSE
680 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
681 pay_us_payroll_utils.formated_data_string (p_input_string =>lv_element_info,
682 p_bold => 'N',
683 p_output_file_type => lv_output_file_type));
684 END IF;
685
686
687 hr_utility.set_location(gv_package_name || '.select_employee', 140);
688
689 open c_get_elements(lv_business_group_id,
690 lv_legislation_code);
691 LOOP
692 FETCH c_get_elements INTO lv_element_name,
693 lv_classification_name,
694 lv_input_value_name;
695 IF c_get_elements%NOTFOUND THEN
696 EXIT;
697 END IF;
698
699 -- Display Table for HTML and only for 1st record
700 IF lv_element_count = 0 THEN
701 IF lv_output_file_type ='HTML' THEN
702
703 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
704 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
705 END IF;
706
707 formated_element_header(lv_output_file_type
708 ,lv_header_label);
709
710 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label);
711 END IF;
712
713 hr_utility.set_location(gv_package_name || '.select_employee', 150);
714
715 lv_element_count := lv_element_count + 1;
716 formated_element_row(lv_element_name
717 ,lv_classification_name
718 ,lv_input_value_name
719 ,lv_output_file_type
720 ,lv_element_row);
721
722 hr_utility.trace('lv_element_row = ' || lv_element_row);
723
724 IF lv_output_file_type ='HTML' THEN
725 lv_element_row := '<tr>' || lv_element_row || '</tr>' ;
726 END IF;
727
728 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_element_row);
729 END LOOP;
730 CLOSE c_get_elements;
731
732 hr_utility.set_location(gv_package_name || '.select_employee', 160);
733
734 IF lv_output_file_type='HTML' and lv_element_count > 0 THEN
735 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
736 --ELSIF lv_element_count = 0 THEN
737
738 -- hr_utility.set_location(gv_package_name || '.select_employee', 170);
739 END IF;
740
741 lv_element_count_info := pay_us_payroll_utils.formated_data_string
742 (p_input_string =>'Number of Elements Found = '|| lv_element_count
743 ,p_bold => 'N'
744 ,p_output_file_type => lv_output_file_type);
745
746 IF lv_output_file_type='HTML' THEN
747 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<p align="center">');
748 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_element_count_info ||'</p>');
749 ELSE
750 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_element_count_info);
751 END IF;
752 */
753
754 hr_utility.set_location(gv_package_name || '.select_employee', 180);
755
756 END select_employee;
757
758 END pay_us_yepp_miss_assign_pkg;