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