1 PACKAGE BODY pay_element_extract_pkg AS
2 /* $Header: pyelerep.pkb 120.11.12020000.2 2012/07/06 09:57:49 vmaripal 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_element_extract_pkg
22
23 Description : Package for the Elements Reports. The package
24 generated the output file in the specified user
25 format. The current formats supported are
26 - HTML
27 - CSV
28
29 Change List
30 -----------
31 Date Name Vers Bug No Description
32 ---- ---- ------ ------- -----------
33 04-AUG-2000 ahanda 115.0 Created.
34 14-SEP-2000 ahanda 115.1 1407284 Corrected package name.
35 25-SEP-2000 ahanda 115.2 1416995 Adding delete for PL/SQL
36 table.
37 10-OCT-2000 ahanda 115.3 Added check to pick only the
38 selected employee if employee
39 ID is passed and only not null
40 run results.
41 02-FEB-2001 ahanda 115.4 1625762 Added check for business group
42 26-APR-2001 ahanda 115.5 1755126 Changed logic for Elment Sets.
43 22-MAY-2001 ahanda 115.6 Changed parameter in cursor
44 c_element_results to
45 cp_element_set_id from
46 cp_element_type_id when joining
47 to element_Set_id
48 26-JUN-2001 ahanda 115.7 1855697 Changed logic for populate PL/SQL
49 table with result_value to take
50 care of multiple element entries.
51 17-AUG-2001 ahanda 115.8 1918074 Changed cursor c_element_results
52 for performance.
53 04-DEC-2002 dsaxby 115.13 2692195 Nocopy changes.
54 18-DEC-2002 tclewis 115.12 2390994 Modifications to the element_extract
55 procedure, c_assignments cursor.
56 Broke out the cursor into 2 querries
57 to Reduce High Buffer gets.
58
59 05-AUG-2003 trugless 115.13 Replaced hardcoded text for
60 report headings and
61 gv_title with lookup to
62 FND_COMMON_LOOKUPS table using
63 hr_general.decode_fnd_comm_lookup
64 function
65
66 Modified c_element_results,
67 c_class_elements,c_set_elements,
68 and c_elements cursors to use the
69 PAY_ELELMENT_TYPES_F_TL table
70 instead of
71 PAY_ELELMENT_TYPES_F for reporting
72 name so translated value will be
73 used.
74
75 modified the c_element_results
76 to query the
77 PAY_INPUT_VALUES_F_TL table for
78 25-FEB-2003 ssmukher 115.14 2007614 Added a new cursor c_legislation_code
79 for handling the
80 changing of SSN to SIN in case
81 of CA legislation in the procedure
82 formated_static_header
83 16-JUN-2004 ahanda 115.15 3433727 Changed code to use ref cursor.
84 2007614 Changed cursor c_legislation_code to
85 use base table instead of view.
86 16-JUL-2004 schauhan 115.16 3731178 Changed cursor c_class_elements,c_set_elements
87 c_elements and query string
88 lv_element_result_query.
89 Now element name shall be shown if reporting
90 name is null. Also made changes to
91 lv_element_result_query so that new
92 garnishment elements are also processed.
93 19-JUL-2004 schauhan 115.18 3731178 Reverted back to version 115.16
94 20-JUL-2004 ahanda 115.19 3778025 Changes query lv_element_result_query
95 to use bind parameters. Also, removed
96 special login for Invol Calculator element.
97 10-SEP-2004 schauhan 115.20 3650988 Changed the size of the variable lv_employment_category_code
98 from Varchar2(10) to per_assignments_f.employment_category%type.
99 10-MAR-2005 rajeesha 115.21 4214739 Used Status Column in ltr_elements in Extract_element
100 to avoid the entries which are Replace
101 24-APR-2006 ppanda 115.23 5167072 Element Register Report was not picking up
102 any data for Secondary classification.
103 26-APR-2006 ppanda 115.23 5179163 Element Register Report was not having correct
104 28-JUN-2006 asasthan 115.24 5231257 Performance tuning added hints
105 08-AUG-2006 jdevasah 115.25 5229191 Added two parameters to cursors c_class_elements and
106 c_set_elements and added conditions to filter elements that are not
107 eligible for the given Element Report period.
108 13-DEC-2006 saurgupt 115.27 5684493 Changed the union clause to union all in lv_element_result_query.
109 With this the report will now sum up the values for multiple entries
110 of same report.
111 01-AUG-2007 vaprakas 115.29 6075462 Added a distinct clause and selected pay_run_results.run_result_id
112 in the cursor lv_element_result_query.
113 28-AUG-2008 keyazawa 115.30 7264010 Fixed lv_element_result_query to work properly
114 multiple entry, same reporting name, secondary class parameter
115 29-AUG-2008 keyazawa 115.31 Fixed lv_element_result_query to work properly
116 additional entry, retro pay entry
117 Fixed lv_element_status condition to exclude R, O, U
118 due to work properly override entry
119 11-MAY-2010 phattarg 115.32 9604489 Removed the decode from the select clause of
120 lv_element_result_query to fetch multiple results in a pay period
121 of an element correctly.
122 08-AUG-2010 nvelaga 115.33 12605384 Modified the package to extract Fees Elements also.
123 Removed the condition 'and element_name not like '%Fees' from
124 cursors c_class_elements, c_set_elements and lv_element_result_query
125 of element_extract procedure and from c_element_set cursor of
126 get_element_set_where_clause function.
127 30-APR-2012 prasrang 115.34 13914244 Modified lv_element_result_query.
128 */
129
130 /************************************************************
131 ** Local Package Variables
132 ************************************************************/
133 gv_title VARCHAR2(100);
134 --gv_title VARCHAR2(100) := ' Element Register ';
135 gc_csv_delimiter VARCHAR2(1) := ',';
136 gc_csv_data_delimiter VARCHAR2(1) := '"';
137
138 gv_html_start_data VARCHAR2(5) := '<td>' ;
139 gv_html_end_data VARCHAR2(5) := '</td>' ;
140
141 gv_package_name VARCHAR2(50) := 'pay_element_extract_pkg';
142
143
144 /******************************************************************
145 ** Function Returns the formated input string based on the
146 ** Output format. If the format is CSV then the values are returned
147 ** seperated by comma (,). If the format is HTML then the returned
148 ** string as the HTML tags. The parameter p_bold only works for
149 ** the HTML format.
150 ******************************************************************/
151 FUNCTION formated_data_string
152 (p_input_string in varchar2
153 ,p_output_file_type in varchar2
154 ,p_bold in varchar2 default 'N'
155 )
156 RETURN VARCHAR2
157 IS
158
159 lv_format varchar2(1000);
160
161 BEGIN
162 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
163 if p_output_file_type = 'CSV' then
164 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
165 lv_format := gc_csv_data_delimiter || p_input_string ||
166 gc_csv_data_delimiter || gc_csv_delimiter;
167 elsif p_output_file_type = 'HTML' then
168 if p_input_string is null then
169 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
170 lv_format := gv_html_start_data || ' ' || gv_html_end_data;
171 else
172 if p_bold = 'Y' then
173 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
174 lv_format := gv_html_start_data || '<b> ' || p_input_string
175 || '</b>' || gv_html_end_data;
176 else
177 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
178 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
179 end if;
180 end if;
181 end if;
182
183 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
184 return lv_format;
185
186 END formated_data_string;
187
188
189 /************************************************************
190 ** Function returns the string with the HTML Header tags
191 ************************************************************/
192 FUNCTION formated_header_string
193 (p_input_string in varchar2
194 ,p_output_file_type in varchar2
195 )
196 RETURN VARCHAR2
197 IS
198
199 lv_format varchar2(1000);
200
201 BEGIN
202 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
203 if p_output_file_type = 'CSV' then
204 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
205 lv_format := p_input_string;
206 elsif p_output_file_type = 'HTML' then
207 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
208 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
209 '</B></H1></CENTER></HEAD>';
210 end if;
211
212 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
213 return lv_format;
214
215 END formated_header_string;
216
217
218 /*****************************************************************
219 ** This procudure returns the Mandatory Static Labels and the
220 ** Other Additional Static columns. The other static columns are
221 ** printed after all the Element Information is printed for each
222 ** employee assignment.
223 ** The users can add hooks to this package to print more additional
224 ** data which they require for this report.
225 ** The package prints the user data from a PL/SQL table. The users
226 ** can insert data and the label in this PL/SQL table which will
227 ** be printed at the end of the report.
228 ** The PL/SQL table which needs to be populated is
229 ** LTR_ELEMENT_EXTRACT_DATA. This PL/SQL table is defined in the
230 ** Package pay_element_extract_data_pkg (pyelerpd.pkh/pkb).
231 *****************************************************************/
232 PROCEDURE formated_static_header(
233 p_output_file_type in varchar2
234 ,p_static_label1 out nocopy varchar2
235 ,p_static_label2 out nocopy varchar2
236 ,p_business_group_id in varchar2 -- Bug No : 2007614
237 )
238 IS
239 -- Bug No : 2007614
243 from hr_organization_information hoi_bg
240 -- changed call to per_business_groups to the base table
241 cursor c_legislation_code is
242 select hoi_bg.org_information9
244 where organization_id = p_business_group_id
245 and org_information_context = 'Business Group Information';
246
247 lv_legislation_code varchar2(150); --Bug No :2007614
248 lv_ssl_number varchar2(150); -- Bug No :2007614 and 5179163 (Size changed from 3 to 150)
249 lv_format1 varchar2(32000);
250 lv_format2 varchar2(32000);
251
252 BEGIN
253
254 --Bug No : 2007614 New cursor for fetching the Legislation code
255 -- based on which the value will be SIN for CA legislation code or SSN
256 -- for other legislation code in the header part
257 open c_legislation_code;
258 fetch c_legislation_code into lv_legislation_code;
259 /* commented for Bug # 5179163
260 if lv_legislation_code = 'CA' then
261 lv_ssl_number := 'SIN';
262 else
263 lv_ssl_number := 'SSN';
264 end if;
265 */
266 /* This is added to fix Bug # 5179163 */
267 fnd_message.set_name('PER','HR_NATIONAL_ID_NUMBER_'||lv_legislation_code);
268 lv_ssl_number := fnd_message.get;
269 if lv_ssl_number IS NULL
270 then
271 lv_ssl_number := 'National Identifier';
272 end if;
273 hr_utility.trace('HR_NATIONAL_ID_NUMBER_'||lv_legislation_code ||' = ' || lv_ssl_number);
274
275 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
276 lv_format1 :=
277 formated_data_string (p_input_string =>
278 hr_general.decode_fnd_comm_lookup
279 ('PAYROLL_REPORTS', --lookup_type
280 'L_NAME')--lookup_code
281 ,p_bold => 'Y'
282 ,p_output_file_type => p_output_file_type) ||
283
284 formated_data_string (p_input_string =>
285 hr_general.decode_fnd_comm_lookup
286 ('PAYROLL_REPORTS', --lookup_type
287 'F_NAME')--lookup_code
288 ,p_bold => 'Y'
289 ,p_output_file_type => p_output_file_type) ||
290 formated_data_string (p_input_string =>
291 hr_general.decode_fnd_comm_lookup
292 ('PAYROLL_REPORTS', --lookup_type
293 'MI_NAME')--lookup_code
294 ,p_bold => 'Y'
295 ,p_output_file_type => p_output_file_type) ||
296
297 formated_data_string (p_input_string =>
298 hr_general.decode_fnd_comm_lookup
299 ('PAYROLL_REPORTS', --lookup_type
300 'EMP_NO')--lookup_code
301 ,p_bold => 'Y'
302 ,p_output_file_type => p_output_file_type) ||
303
304 formated_data_string (p_input_string =>
305 hr_general.decode_fnd_comm_lookup
306 ('PAYROLL_REPORTS', --lookup_type
307 'ASSIGN_NO')--lookup_code
308 ,p_bold => 'Y'
309 ,p_output_file_type => p_output_file_type) ||
310
311 formated_data_string (p_input_string =>
312 hr_general.decode_fnd_comm_lookup
313 ('PAYROLL_REPORTS', --lookup_type
314 'ORG_NAME')--lookup_code
315 ,p_bold => 'Y'
316 ,p_output_file_type => p_output_file_type) ||
317
318 formated_data_string (p_input_string =>
319 hr_general.decode_fnd_comm_lookup
320 ('PAYROLL_REPORTS', --lookup_type
321 'ACT_TYP')--lookup_code
322 ,p_bold => 'Y'
323 ,p_output_file_type => p_output_file_type) ||
324
325 formated_data_string (p_input_string =>
326 hr_general.decode_fnd_comm_lookup
327 ('PAYROLL_REPORTS', --lookup_type
328 'EFF_DT')--lookup_code
329 ,p_bold => 'Y'
330 ,p_output_file_type => p_output_file_type)
331 ;
332
333 hr_utility.set_location(gv_package_name || '.formated_static_header', 20);
334 lv_format2 :=
335 formated_data_string (p_input_string =>
336 hr_general.decode_fnd_comm_lookup
337 ('PAYROLL_REPORTS', --lookup_type
338 'PR_NAME')--lookup_code
339 ,p_bold => 'Y'
340 ,p_output_file_type => p_output_file_type) ||
341
342 formated_data_string (p_input_string =>
343 hr_general.decode_fnd_comm_lookup
344 ('PAYROLL_REPORTS', --lookup_type
345 'GRE')--lookup_code
346 ,p_bold => 'Y'
347 ,p_output_file_type => p_output_file_type) ||
348
349 formated_data_string (p_input_string =>
350 hr_general.decode_fnd_comm_lookup
351 ('PAYROLL_REPORTS', --lookup_type
352 'LOC_NAME')--lookup_code
353 ,p_bold => 'Y'
354 ,p_output_file_type => p_output_file_type) ||
355 --
356 -- Lookup Code defaulted if meaning is not found for the lookup_code
357 -- This is to fix bug # 5179163
358 --
359 formated_data_string (p_input_string =>
360 NVL(hr_general.decode_fnd_comm_lookup
361 ('PAYROLL_REPORTS', --lookup_type
362 lv_ssl_number),
363 lv_ssl_number)--lookup_code Bug No : 2007614
364 ,p_bold => 'Y'
365 ,p_output_file_type => p_output_file_type) ||
366
367 formated_data_string (p_input_string =>
368 hr_general.decode_fnd_comm_lookup
369 ('PAYROLL_REPORTS', --lookup_type
370 'DOB')--lookup_code
371 ,p_bold => 'Y'
372 ,p_output_file_type => p_output_file_type) ||
373
374 formated_data_string (p_input_string =>
375 hr_general.decode_fnd_comm_lookup
376 ('PAYROLL_REPORTS', --lookup_type
377 'GENDER')--lookup_code
378 ,p_bold => 'Y'
379 ,p_output_file_type => p_output_file_type) ||
380
381 formated_data_string (p_input_string =>
382 hr_general.decode_fnd_comm_lookup
383 ('PAYROLL_REPORTS', --lookup_type
384 'DT_FIRST_HIRED')
385 ,p_bold => 'Y'
386 ,p_output_file_type => p_output_file_type) ||
387
388 formated_data_string (p_input_string =>
389 hr_general.decode_fnd_comm_lookup
390 ('PAYROLL_REPORTS', --lookup_type
391 'LT_HIRE_DT')--lookup_code
392 ,p_bold => 'Y'
393 ,p_output_file_type => p_output_file_type) ||
394
395 formated_data_string (p_input_string =>
396 hr_general.decode_fnd_comm_lookup
397 ('PAYROLL_REPORTS', --lookup_type
398 'EMP_TYP')--lookup_code
399 ,p_bold => 'Y'
400 ,p_output_file_type => p_output_file_type) ||
401
402 formated_data_string (p_input_string =>
403 hr_general.decode_fnd_comm_lookup
404 ('PAYROLL_REPORTS', --lookup_type
405 'ASSIGN_STAT')--lookup_code
406 ,p_bold => 'Y'
407 ,p_output_file_type => p_output_file_type) ||
408
409 formated_data_string (p_input_string =>
410 hr_general.decode_fnd_comm_lookup
411 ('PAYROLL_REPORTS', --lookup_type
412 'EMP_CAT')--lookup_code
413 ,p_bold => 'Y'
414 ,p_output_file_type => p_output_file_type)
415 ;
416
417 /*******************************************************************
418 ** Print the User Defined data for each Employee Assignment at the
419 ** end of the report
420 *******************************************************************/
421 hr_utility.set_location(gv_package_name || '.formated_static_header', 30);
422
423 /*******************************************************************
424 ** Only do this if there is some configuration data present
425 *******************************************************************/
429 loop
426 if pay_element_extract_data_pkg.ltt_element_extract_label.count > 0 then
427 for i in pay_element_extract_data_pkg.ltt_element_extract_label.first ..
428 pay_element_extract_data_pkg.ltt_element_extract_label.last
430
431 lv_format2 := lv_format2 ||
432 formated_data_string (
433 p_input_string =>
434 pay_element_extract_data_pkg.ltt_element_extract_label(i)
435 ,p_bold => 'Y'
436 ,p_output_file_type => p_output_file_type);
437
438 end loop;
439 end if;
440
441 p_static_label1 := lv_format1;
442 p_static_label2 := lv_format2;
443 hr_utility.trace('Static Label1 = ' || lv_format1);
444 hr_utility.trace('Static Label2 = ' || lv_format2);
445 hr_utility.set_location(gv_package_name || '.formated_static_header', 40);
446
447 END;
448
449
450 /*****************************************************************
451 ** This procudure returns the Mandatory Static Labels and the
452 ** Other Additional Static columns. The other static columns are
453 ** printed after all the Element Information is printed for each
454 ** employee assignment.
455 ** The users can add hooks to this package to print more additional
456 ** data which they require for this report.
457 ** The package prints the user data from a PL/SQL table. The users
458 ** can insert data and the label in this PL/SQL table which will
459 ** be printed at the end of the report.
460 ** The PL/SQL table which needs to be populated is
461 ** LTR_ELEMENT_EXTRACT_DATA. This PL/SQL table is defined in the
462 ** Package pay_element_extract_data_pkg (pyelerpd.pkh/pkb).
463 *****************************************************************/
464 PROCEDURE formated_static_data (
465 p_action_type in varchar2
466 ,p_action_effective_date in date
467 ,p_payroll_name in varchar2
468 ,p_gre_name in varchar2
469 ,p_org_name in varchar2
470 ,p_location_code in varchar2
471 ,p_emp_last_name in varchar2
472 ,p_emp_first_name in varchar2
473 ,p_emp_middle_names in varchar2
474 ,p_emp_employee_number in varchar2
475 ,p_emp_national_identifier in varchar2
476 ,p_emp_date_of_birth in date
477 ,p_gender in varchar2
478 ,p_emp_original_date_of_hire in date
479 ,p_emp_projected_start_date in date
480 ,p_emp_user_person_type in varchar2
481 ,p_assignment_number in varchar2
482 ,p_assignment_status in varchar2
483 ,p_employment_category in varchar2
484 ,p_output_file_type in varchar2
485 ,p_static_data1 out nocopy varchar2
486 ,p_static_data2 out nocopy varchar2
487 )
488 IS
489
490 lv_format1 VARCHAR2(32000);
491 lv_format2 VARCHAR2(32000);
492
493 lv_emp_date_of_birth varchar2(20)
494 := to_char(p_emp_date_of_birth, 'dd-MON-yyyy');
495 lv_emp_original_date_of_hire varchar2(20)
496 := to_char(p_emp_original_date_of_hire, 'dd-MON-yyyy');
497 lv_emp_projected_start_date varchar2(20)
498 := to_char(p_emp_projected_start_date, 'dd-MON-yyyy');
499 lv_action_effective_date varchar2(20)
500 := to_char(p_action_effective_date, 'dd-MON-yyyy');
501
502 BEGIN
503
504 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
505 lv_format1 :=
506 formated_data_string (p_input_string => p_emp_last_name
507 ,p_output_file_type => p_output_file_type) ||
508 formated_data_string (p_input_string => p_emp_first_name
509 ,p_output_file_type => p_output_file_type) ||
510 formated_data_string (p_input_string => p_emp_middle_names
511 ,p_output_file_type => p_output_file_type) ||
512 formated_data_string (p_input_string => p_emp_employee_number
513 ,p_output_file_type => p_output_file_type) ||
514 formated_data_string (p_input_string => p_assignment_number
515 ,p_output_file_type => p_output_file_type) ||
516 formated_data_string (p_input_string => p_org_name
517 ,p_output_file_type => p_output_file_type) ||
518 formated_data_string (p_input_string => p_action_type
519 ,p_output_file_type => p_output_file_type) ||
520 formated_data_string (p_input_string => lv_action_effective_date
521 ,p_output_file_type => p_output_file_type)
522 ;
523
524 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
525 lv_format2 :=
526 formated_data_string (p_input_string => p_payroll_name
527 ,p_output_file_type => p_output_file_type) ||
528 formated_data_string (p_input_string => p_gre_name
529 ,p_output_file_type => p_output_file_type) ||
530 formated_data_string (p_input_string => p_location_code
531 ,p_output_file_type => p_output_file_type) ||
532 formated_data_string (p_input_string => p_emp_national_identifier
533 ,p_output_file_type => p_output_file_type) ||
534 formated_data_string (p_input_string => lv_emp_date_of_birth
535 ,p_output_file_type => p_output_file_type) ||
536 formated_data_string (p_input_string => p_gender
537 ,p_output_file_type => p_output_file_type) ||
538 formated_data_string (p_input_string => lv_emp_original_date_of_hire
539 ,p_output_file_type => p_output_file_type) ||
540 formated_data_string (p_input_string => lv_emp_projected_start_date
541 ,p_output_file_type => p_output_file_type) ||
542 formated_data_string (p_input_string => p_emp_user_person_type
543 ,p_output_file_type => p_output_file_type) ||
544 formated_data_string (p_input_string => p_assignment_status
545 ,p_output_file_type => p_output_file_type) ||
546 formated_data_string (p_input_string => p_employment_category
547 ,p_output_file_type => p_output_file_type)
548 ;
549
550 /*******************************************************************
551 ** Print the User Defined data for each Employee Assignment at the
552 ** end of the report
553 *******************************************************************/
554 hr_utility.set_location(gv_package_name || '.formated_static_data', 30);
555
556 /*******************************************************************
557 ** Only do this if there is some configuration data present
558 *******************************************************************/
559 if pay_element_extract_data_pkg.ltt_element_extract_label.count > 0 then
560 for i in pay_element_extract_data_pkg.ltt_element_extract_data.first ..
561 pay_element_extract_data_pkg.ltt_element_extract_data.last
562 loop
563
564 lv_format2 := lv_format2 ||
565 formated_data_string (
566 p_input_string =>
567 pay_element_extract_data_pkg.ltt_element_extract_data(i)
568 ,p_output_file_type => p_output_file_type);
569 end loop;
570 end if;
571
572 p_static_data1 := lv_format1;
573 p_static_data2 := lv_format2;
574 hr_utility.trace('Static Data1 = ' || lv_format1);
575 hr_utility.trace('Static Data2 = ' || lv_format2);
576 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
577
578 END;
579
580 FUNCTION get_element_set_where_clause (p_element_set_id in number)
581 RETURN varchar2
582 IS
583
584 cursor c_element_set (cp_element_set_id in number) is
585 select petr.element_type_id
586 from pay_element_type_rules petr
587 where petr.element_set_id = cp_element_set_id
588 and petr.include_or_exclude = 'I'
589 union all
590 select pet1.element_type_id
591 from pay_element_types_f pet1
592 where pet1.classification_id in
593 (select classification_id
594 from pay_ele_classification_rules
595 where element_set_id = cp_element_set_id)
596 and pet1.element_name not like '%Special Features'
597 and pet1.element_name not like '%Special Inputs'
598 and pet1.element_name not like '%Withholding'
599 and pet1.element_name not like '%Verifier'
603 select petr.element_type_id
600 -- and pet1.element_name not like '%Fees' /* Bug#12605384 */
601 and pet1.element_name not like '%Priority'
602 minus
604 from pay_element_type_rules petr
605 where petr.element_set_id = cp_element_set_id
606 and petr.include_or_exclude = 'E';
607
608 ln_set_element_type_id NUMBER;
609 lv_where_clause VARCHAR2(32000);
610
611 BEGIN
612
613 open c_element_set(p_element_set_id);
614 loop
615 fetch c_element_set into ln_set_element_type_id;
616 if c_element_set%notfound then
617 lv_where_clause := substr(lv_where_clause,2);
618 exit;
619 end if;
620
621 lv_where_clause := lv_where_clause || ',' || ln_set_element_type_id;
622
623 end loop;
624 close c_element_set;
625
626 lv_where_clause := 'pet.element_type_id in (' || lv_where_clause
627 || ')';
628
629
630 return (lv_where_clause);
631
632 END get_element_set_where_clause;
633
634 /*****************************************************************
635 ** This is the main procedure which is called from the Concurrent
636 ** Request. All the paramaters are passed based on which it will
637 ** either print a CSV format or an HTML format file.
638 *****************************************************************/
639 PROCEDURE element_extract
640 (errbuf out nocopy varchar2
641 ,retcode out nocopy number
642 ,p_business_group_id in number
643 ,p_start_date in varchar2
644 ,p_end_date in varchar2
645 ,p_selection_criteria in varchar2
646 ,p_is_ele_set in varchar2
647 ,p_element_set_id in number
648 ,p_is_ele_class in varchar2
649 ,p_element_classification_id in number
650 ,p_is_ele in varchar2
651 ,p_element_type_id in number
652 ,p_payroll_id in number
653 ,p_consolidation_set_id in number
654 ,p_tax_unit_id in number
655 ,p_organization_id in number
656 ,p_location_id in number
657 ,p_person_id in number
658 ,p_assignment_set_id in number
659 ,p_output_file_type in varchar2
660 )
661 IS
662
663
664 /************************************************************
665 ** Cursor to get all the employee and assignment data.
666 ** This cursor will return one row for each Assignment Action
667 ** for the Selection parameter entered by the user in the SRS.
668 ** the Assignment Action returned by this cursor is used to
669 ** retreive the Elements processed and its Pay Value.
670 ************************************************************/
671 cursor c_assignments (
672 cp_start_date in date
673 ,cp_end_date in date
674 ,cp_payroll_id in number
675 ,cp_consolidation_set_id in number
676 ,cp_organization_id in number
677 ,cp_tax_unit_id in number
678 ,cp_location_id in number
679 ,cp_person_id in number
680 ,cp_business_group_id in number
681 ) is
682 select paa.assignment_action_id
683 ,paa.tax_unit_id
684 ,paf.assignment_id
685 ,ppa.payroll_action_id
686 ,ppf.person_id
687 ,ppa.effective_date
688 ,fcl.meaning
689 ,pf.payroll_name
690 ,ppf.last_name
691 ,ppf.first_name
692 ,ppf.middle_names
693 ,ppf.employee_number
694 ,ppf.national_identifier
695 ,ppf.date_of_birth
696 ,ppf.sex
697 ,ppf.original_date_of_hire
698 ,ppf.projected_start_date
699 ,paf.assignment_number
700 ,paf.employment_category
701 from pay_payroll_actions ppa,
702 pay_assignment_actions paa,
703 per_assignments_f paf,
704 per_people_f ppf,
705 pay_payrolls_f pf,
706 fnd_common_lookups fcl
707 where ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
708 and ppa.business_group_id = cp_business_group_id
709 --
710 and pf.payroll_id = ppa.payroll_id
711 and ppa.effective_date between pf.effective_start_date
712 and pf.effective_end_date
713 and pf.payroll_id like nvl(to_char(cp_payroll_id), '%')
714 and (cp_consolidation_set_id is null
715 or ppa.consolidation_set_id = cp_consolidation_set_id )
716 --
717 and ppa.effective_date between cp_start_date
718 and cp_end_date
719 and fcl.lookup_code = ppa.action_type
720 and fcl.lookup_type = 'ACTION_TYPE'
721 and fcl.application_id = 800
722 and fcl.enabled_flag = 'Y'
723 and paa.payroll_action_id = ppa.payroll_action_id
724 and (cp_tax_unit_id is null
725 or paa.tax_unit_id = cp_tax_unit_id)
726 and paf.assignment_id = paa.assignment_id
727 and ppa.effective_date between paf.effective_start_date
728 and paf.effective_end_date
729 and (cp_organization_id is null
730 or paf.organization_id = cp_organization_id)
731 and (cp_location_id is null
732 or paf.location_id = cp_location_id)
733 and ppf.person_id = paf.person_id
734 and ppa.effective_date between ppf.effective_start_date
735 and ppf.effective_end_date
736 and (cp_person_id is null
737 or ppf.person_id = cp_person_id)
738 order by ppf.last_name, ppf.first_name,
739 ppf.middle_names, ppa.effective_date;
740
741
742 /*************************************************************
743 ** This cursor returns the elements processed for a particular
744 ** assignment action and the Pay Value for that element. The
745 ** cursor also accepts element set, element type and
746 ** classification id as an input. Only one of these will have
747 ** a value. Only the Pay Value of elements in the element set
748 ** or classification or just the element is returned.
749 **************************************************************/
753 cursor c_element_results (cp_assignment_action_id in number
750 --Element Register Report changes delivered with bug 3039097
751 --August 2003
752 /*
754 ,cp_classification_id in number
755 ,cp_element_set_id in number
756 ,cp_element_type_id in number
757 ,cp_effective_date in date) is
758 select pet.reporting_name,
759 prrv.result_value
760 from pay_element_types_f pet,
761 pay_element_types_f_tl pettl,
762 pay_input_values_f piv,
763 pay_run_result_values prrv,
764 pay_run_results prr,
765 pay_input_values_f_tl pivtl
766 where pivtl.name = 'Pay Value'
767 and pivtl.language = 'US'
768 and pivtl.input_value_id = piv.input_value_id
769 and prrv.input_value_id = piv.input_value_id
770 and piv.element_type_id = pet.element_type_id
771 and prrv.result_value is not null
772 and prr.run_result_id = prrv.run_result_id
773 and prr.assignment_action_id = cp_assignment_action_id
774 and pet.element_type_id = prr.element_type_id
775 and pettl.language = userenv('LANG')
776 and pettl.element_type_id = pet.element_type_id
777 and cp_effective_date between pet.effective_start_date
778 and pet.effective_end_date
779 and pet.classification_id like nvl(to_char(cp_classification_id), '%')
780 and pet.element_type_id like nvl(to_char(cp_element_type_id), '%')
781 and (cp_element_set_id is null
782 or (cp_element_set_id is not null
783 and prr.element_type_id in
784 (select petr.element_type_id
785 from pay_element_type_rules petr
786 where petr.element_set_id = cp_element_set_id
787 and petr.include_or_exclude = 'I'
788 union all
789 select pet1.element_type_id
790 from pay_element_types_f pet1
791 where pet1.classification_id in
792 (select classification_id
793 from pay_ele_classification_rules
794 where element_set_id = cp_element_set_id)
795 minus
796 select petr.element_type_id
797 from pay_element_type_rules petr
798 where petr.element_set_id = cp_element_set_id
799 and petr.include_or_exclude = 'E'
800 )
801 )
802 );
803 */
804 /*
805 cursor c_element_results (cp_assignment_action_id in number
806 ,cp_classification_id in number
807 ,cp_element_set_id in number
808 ,cp_element_type_id in number
809 ,cp_effective_date in date) is
810 select pet.reporting_name,
811 prrv.result_value
812 from pay_element_types_f pet,
813 pay_input_values_f piv,
814 pay_run_result_values prrv,
815 pay_run_results prr
816 where piv.name = 'Pay Value'
817 and prrv.input_value_id = piv.input_value_id
818 and piv.element_type_id = pet.element_type_id
819 and prrv.result_value is not null
820 and prr.run_result_id = prrv.run_result_id
821 and prr.assignment_action_id = cp_assignment_action_id
822 and pet.element_type_id = prr.element_type_id
823 and cp_effective_date between pet.effective_start_date
824 and pet.effective_end_date
825 and pet.classification_id like nvl(to_char(cp_classification_id), '%')
826 and pet.element_type_id like nvl(to_char(cp_element_type_id), '%')
827 and (cp_element_set_id is null
828 or (cp_element_set_id is not null
829 and prr.element_type_id in
830 (select petr.element_type_id
831 from pay_element_type_rules petr
832 where petr.element_set_id = cp_element_set_id
833 and petr.include_or_exclude = 'I'
834 union all
835 select pet1.element_type_id
836 from pay_element_types_f pet1
837 where pet1.classification_id in
838 (select classification_id
839 from pay_ele_classification_rules
840 where element_set_id = cp_element_set_id)
841 minus
842 select petr.element_type_id
843 from pay_element_type_rules petr
844 where petr.element_set_id = cp_element_set_id
845 and petr.include_or_exclude = 'E'
846 )
847 )
848 );
849
850 */
851 /*************************************************************
852 ** Cursor returns all the valid element names for the input
853 ** Element Classification.
854 *************************************************************/
855 --Element Register Report changes delivered with bug 3039097
856 --August 2003
857
858 --Bug 3731178
859 -- Added NVL in the select of c_class_elements,c_set_elements,
860 -- c_elements so that reporting name is displayed if element
861 -- name is null. %Priotiry Elements will not be shown
862
863 -- Bug 5229191
864 -- Added parameters cp_start_date and cp_end_date
865 -- Added two where conditions to filter elements not eligible for
866 -- the Element Report period
867
868 cursor c_class_elements( cp_element_classification_id in number
869 ,cp_business_group_id in number
870 ,cp_start_date in date
871 ,cp_end_date in date
872 ) is
873 select distinct nvl(pettl.reporting_name,pettl.element_name)
874 from pay_element_types_f pet,
875 pay_element_types_f_tl pettl
876 where pet.classification_id = cp_element_classification_id
877 and cp_business_group_id = nvl(pet.business_group_id, cp_business_group_id)
878 and pet.element_name not like '%Special Features'
879 and pet.element_name not like '%Special Inputs'
880 and pet.element_name not like '%Withholding'
881 and pet.element_name not like '%Verifier'
882 -- and pet.element_name not like '%Fees' /* Bug#12605384 */
883 and pet.element_name not like '%Priority'
884 and pettl.language = userenv('LANG')
885 and pettl.element_type_id = pet.element_type_id
886 and pet.effective_start_date <= cp_end_date --bug # 5229191
887 and pet.effective_end_date >= cp_start_date
888 /* Added to fix Bug # 5167072
889 START */
890 UNION ALL
891 select distinct nvl(pettl.reporting_name,pettl.element_name)
892 from pay_element_types_f pet,
893 pay_element_types_f_tl pettl,
894 PAY_SUB_CLASSIFICATION_RULES_F scr,
895 pay_element_classifications pec
896 where scr.element_type_id = pet.element_type_id
897 and pec.classification_id = cp_element_classification_id
898 and pec.classification_id = scr.classification_id
899 and cp_business_group_id = nvl(pet.business_group_id,
900 cp_business_group_id)
901 and pet.element_name not like '%Special Features'
902 and pet.element_name not like '%Special Inputs'
903 and pet.element_name not like '%Withholding'
904 and pet.element_name not like '%Verifier'
905 -- and pet.element_name not like '%Fees' /* Bug#12605384 */
906 and pet.element_name not like '%Priority'
907 and pettl.language = userenv('LANG')
908 and pettl.element_type_id = pet.element_type_id
909 and pet.effective_start_date <= cp_end_date --bug # 5229191
910 and pet.effective_end_date >= cp_start_date
911 /* END of fix Bug # 5167072 */
912 order by 1;
913
914 /*************************************************************
915 ** Cursor returns all the valid element names for the input
916 ** Element Set.
917 *************************************************************/
918 --Element Register Report changes delivered with bug 3039097
919 --August 2003
920
921 -- Bug 5229191
922 -- Added parameters cp_start_date and cp_end_date
923 -- Added two where conditions to filter elements not eligible for
924 -- the Element Report period
925
926 cursor c_set_elements (cp_ele_set_id in number
927 ,cp_business_group_id in number
928 ,cp_start_date in date
929 ,cp_end_date in date
930 ) is
931 select distinct nvl(pettl.reporting_name,pettl.element_name)
932 from pay_element_type_rules petr,
933 pay_element_types_f pet,
934 pay_element_types_f_tl pettl
935 where pet.element_type_id = petr.element_type_id
936 and petr.element_set_id = cp_ele_set_id
937 and petr.include_or_exclude = 'I'
938 and pettl.language = userenv('LANG')
939 and pettl.element_type_id = pet.element_type_id
940 and pet.effective_start_date <= cp_end_date --bug # 5229191
941 and pet.effective_end_date >= cp_start_date
942 union all
943 select distinct nvl(pettl1.reporting_name,pettl1.element_name)
944 from pay_element_types_f pet1,
945 pay_element_types_f_tl pettl1
946 where cp_business_group_id = nvl(pet1.business_group_id, cp_business_group_id)
947 and pet1.classification_id in
948 (select classification_id
949 from pay_ele_classification_rules
950 where element_set_id = cp_ele_set_id)
951 and pet1.element_name not like '%Special Features'
952 and pet1.element_name not like '%Special Inputs'
953 and pet1.element_name not like '%Withholding'
954 and pet1.element_name not like '%Verifier'
955 -- and pet1.element_name not like '%Fees' /* Bug#12605384 */
956 and pet1.element_name not like '%Priority'
957 and pettl1.language = userenv('LANG')
958 and pettl1.element_type_id = pet1.element_type_id
959 and pet1.effective_start_date <= cp_end_date --bug # 5229191
960 and pet1.effective_end_date >= cp_start_date
961 minus
962 select distinct nvl(pettl.reporting_name,pettl.element_name)
963 from pay_element_type_rules petr,
964 pay_element_types_f_tl pettl
965 where pettl.element_type_id = petr.element_type_id
966 and petr.element_set_id = cp_ele_set_id
967 and petr.include_or_exclude = 'E'
968 and pettl.language = userenv('LANG')
969 order by 1; -- reporting_name;
970
971
972 /*************************************************************
973 ** Cursor returns valid element names for the input Element ID
974 *************************************************************/
975 --Element Register Report changes delivered with bug 3039097
976 --August 2003
977
978 cursor c_elements (cp_ele_type_id in number) is
979 select distinct nvl(pettl.reporting_name,pettl.element_name)
980 from pay_element_types_f_tl pettl
981 where pettl.element_type_id = cp_ele_type_id
982 and pettl.language = userenv('LANG');
983
984 /*************************************************************
985 ** Cursor to return the Employement Category from Lookups
986 *************************************************************/
987 cursor c_employment_category (cp_lookup_code in varchar2) is
988 select fcl.meaning
989 from fnd_common_lookups fcl
990 where fcl.lookup_type = 'EMP_CAT'
991 and fcl.lookup_code = cp_lookup_code;
992
993 /*************************************************************
994 ** Local Variables
995 *************************************************************/
996 TYPE cur_type is REF CURSOR;
997 c_element_results cur_type;
998
999 lv_element_set_where_clause VARCHAR2(32000);
1000 lv_element_cls_where_clause VARCHAR2(32000);
1001 lv_element_id_where_clause VARCHAR2(32000);
1002 lv_element_result_query VARCHAR2(32000);
1003 lv_element_cls_where_clause2 VARCHAR2(32000); -- Bug # 5167072
1004 ln_assignment_action_id NUMBER;
1005 ln_assignment_id NUMBER;
1006 ln_person_id NUMBER;
1007 ld_effective_date DATE;
1008 lv_action_type VARCHAR2(100);
1009
1010 lv_tax_unit_id number;
1011 ln_payroll_action_id NUMBER;
1012 lv_gre_name VARCHAR2(240);
1013 lv_emp_last_name VARCHAR2(150);
1014 lv_emp_first_name VARCHAR2(150);
1015 lv_emp_middle_names VARCHAR2(100);
1016 lv_emp_employee_number VARCHAR2(100);
1017 lv_emp_national_identifier VARCHAR2(100);
1018 ld_emp_date_of_birth DATE;
1019 lv_gender VARCHAR2(100);
1020 ld_emp_original_date_of_hire DATE;
1021 ld_emp_projected_start_date DATE;
1022 lv_emp_user_person_type VARCHAR2(100);
1023 lv_assignment_number VARCHAR2(100);
1024 lv_org_name VARCHAR2(240);
1025 lv_payroll_name VARCHAR2(100);
1026 lv_location_code VARCHAR2(60);
1027 lv_assignment_status VARCHAR2(100);
1028 lv_employment_category_code per_assignments_f.employment_category%type; --Bug 3650988
1029 lv_employment_category VARCHAR2(100);
1030
1031 lv_element_name VARCHAR2(100);
1032 lv_element_value VARCHAR2(100);
1033 lv_element_status VARCHAR2(2); --Bug 4214739 Added to get value of status
1034 ln_run_result_id NUMBER(15,0);
1035
1036 lb_print_row BOOLEAN := FALSE;
1037
1038 lv_header_label VARCHAR2(32000);
1039 lv_header_label1 VARCHAR2(32000);
1040 lv_header_label2 VARCHAR2(32000);
1041
1042 lv_data_row VARCHAR2(32000);
1043 lv_data_row1 VARCHAR2(32000);
1044 lv_data_row2 VARCHAR2(32000);
1045
1046 ln_count NUMBER := 0;
1047
1048 ltr_elements tab_element;
1049
1050 BEGIN
1051 -- hr_utility.trace_on (null,'pyelerep');
1052
1053 hr_utility.set_location(gv_package_name || '.element_extract', 10);
1054
1055 formated_static_header( p_output_file_type
1056 ,lv_header_label1
1057 ,lv_header_label2
1058 ,p_business_group_id); -- Bug No : 2007614
1059
1060 lv_header_label := lv_header_label1;
1061
1062 if p_element_set_id is not null then
1063 hr_utility.set_location(gv_package_name || '.element_extract', 20);
1064 open c_set_elements( p_element_set_id
1065 ,p_business_group_id
1066 ,fnd_date.canonical_to_date(p_start_date)
1067 ,fnd_date.canonical_to_date(p_end_date)
1068 );
1069 elsif p_element_classification_id is not null then
1070 hr_utility.set_location(gv_package_name || '.element_extract', 30);
1071 open c_class_elements( p_element_classification_id
1072 ,p_business_group_id
1073 ,fnd_date.canonical_to_date(p_start_date)
1074 ,fnd_date.canonical_to_date(p_end_date)
1075 );
1076 elsif p_element_type_id is not null then
1077 hr_utility.set_location(gv_package_name || '.element_extract', 40);
1078 open c_elements(p_element_type_id);
1079 end if;
1080
1081 hr_utility.set_location(gv_package_name || '.element_extract', 50);
1082 loop
1083 if p_element_set_id is not null then
1084 fetch c_set_elements into lv_element_name;
1085 hr_utility.trace(' lv_element_name 1 : ' || lv_element_name );
1086 if c_set_elements%notfound then
1087 exit;
1088 end if;
1089 elsif p_element_classification_id is not null then
1090 fetch c_class_elements into lv_element_name;
1091 hr_utility.trace(' lv_element_name 2 : ' || lv_element_name );
1092 if c_class_elements%notfound then
1093 exit;
1094 end if;
1095 elsif p_element_type_id is not null then
1096 fetch c_elements into lv_element_name;
1097 hr_utility.trace(' lv_element_name 3 : ' || lv_element_name );
1098 if c_elements%notfound then
1099 exit;
1100 end if;
1101 end if;
1102
1103 ln_count := ln_count + 1;
1104 ltr_elements(ln_count).element_name := lv_element_name;
1105
1106 lv_header_label := lv_header_label ||
1107 formated_data_string(
1108 lv_element_name
1109 ,p_output_file_type
1110 ,'Y');
1111
1112 end loop;
1113 hr_utility.set_location(gv_package_name || '.element_extract', 60);
1114
1115 if p_element_set_id is not null then
1116 close c_set_elements;
1117 elsif p_element_classification_id is not null then
1118 close c_class_elements;
1119 elsif p_element_type_id is not null then
1120 close c_elements;
1121 end if;
1122
1123 hr_utility.set_location(gv_package_name || '.element_extract', 70);
1124 /****************************************************************
1125 ** Concatnating the second Header Label which includes the User
1126 ** Defined data set so that it is printed at the end of the
1127 ** report.
1128 ****************************************************************/
1129 lv_header_label := lv_header_label || lv_header_label2;
1130
1131 hr_utility.set_location(gv_package_name || '.element_extract', 80);
1132 hr_utility.trace('Static and Element Label = ' || lv_header_label);
1133
1134 gv_title := hr_general.decode_fnd_comm_lookup
1135 ('PAYROLL_REPORTS',
1136 'ELEMENT_REGISTER_TITLE');
1137
1138 fnd_file.put_line(fnd_file.output, formated_header_string(
1139 gv_title
1140 ,p_output_file_type
1141 ));
1142
1143 hr_utility.set_location(gv_package_name || '.element_extract', 90);
1144 /****************************************************************
1145 ** Print the Header Information. If the format is HTML then open
1146 ** the body and table before printing the header info, otherwise
1147 ** just print the header information.
1148 ****************************************************************/
1149 if p_output_file_type ='HTML' then
1150 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1151 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1152 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1153 end if;
1154
1155 fnd_file.put_line(fnd_file.output, lv_header_label);
1156
1157 if p_output_file_type ='HTML' then
1158 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1159 end if;
1160
1161 hr_utility.set_location(gv_package_name || '.element_extract', 100);
1162 /*****************************************************
1163 ** Start of the Data Section of the Report
1164 *****************************************************/
1165 hr_utility.trace('Assignment Set ID = ' || p_assignment_set_id);
1166
1167 if p_element_classification_id is not null then
1168 lv_element_cls_where_clause
1169 := 'pet.classification_id = ' || p_element_classification_id;
1170 else
1171 lv_element_cls_where_clause := '1 = 1';
1172 end if;
1173
1174 /* Added for Bug # 5167072 START */
1175 if p_element_classification_id is not null then
1176 lv_element_cls_where_clause2
1177 := 'pec.classification_id = ' || p_element_classification_id;
1178 else
1179 lv_element_cls_where_clause2 := '1 = 1';
1180 end if;
1181 /* Bug # 5167072 END */
1182
1183 if p_element_type_id is not null then
1184 lv_element_id_where_clause
1185 := 'pet.element_type_id = ' || p_element_type_id;
1186 else
1187 lv_element_id_where_clause := '1 = 1';
1188 end if;
1189
1190 if p_element_set_id is not null then
1191 lv_element_set_where_clause := get_element_set_where_clause(
1192 p_element_set_id => p_element_set_id);
1193 else
1194 lv_element_set_where_clause := '1 = 1';
1195 end if;
1196
1197 open c_assignments( to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1198 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1199 ,p_payroll_id
1200 ,p_consolidation_set_id
1201 ,p_organization_id
1202 ,p_tax_unit_id
1203 ,p_location_id
1204 ,p_person_id
1205 ,p_business_group_id
1206 );
1207
1208 loop
1209 fetch c_assignments into ln_assignment_action_id
1210 ,lv_tax_unit_id
1211 ,ln_assignment_id
1212 ,ln_payroll_action_id
1213 ,ln_person_id
1214 ,ld_effective_date
1215 ,lv_action_type
1216 ,lv_payroll_name
1217 ,lv_emp_last_name
1218 ,lv_emp_first_name
1219 ,lv_emp_middle_names
1220 ,lv_emp_employee_number
1221 ,lv_emp_national_identifier
1222 ,ld_emp_date_of_birth
1223 ,lv_gender
1224 ,ld_emp_original_date_of_hire
1225 ,ld_emp_projected_start_date
1226 ,lv_assignment_number
1227 ,lv_employment_category_code;
1228
1229 if c_assignments%notfound then
1230 hr_utility.set_location(gv_package_name || '.element_extract', 105);
1231 exit;
1232 end if;
1233 /*
1234 hr_utility.trace(' ln_assignment_action_id : ' || ln_assignment_action_id );
1235 hr_utility.trace(' lv_tax_unit_id : ' || lv_tax_unit_id );
1236 hr_utility.trace(' ln_assignment_id : ' || ln_assignment_id );
1237 hr_utility.trace(' ln_payroll_action_id : ' || ln_payroll_action_id );
1238 hr_utility.trace(' ln_person_id : ' || ln_person_id );
1239 hr_utility.trace(' ld_effective_date : ' || ld_effective_date );
1240 hr_utility.trace(' lv_action_type : ' || lv_action_type );
1241 hr_utility.trace(' lv_payroll_name : ' || lv_payroll_name );
1242 hr_utility.trace(' lv_emp_last_name : ' || lv_emp_last_name );
1243 hr_utility.trace(' lv_emp_first_name : ' || lv_emp_first_name );
1244 hr_utility.trace(' lv_emp_middle_names : ' || lv_emp_middle_names );
1245 hr_utility.trace(' lv_emp_employee_number : ' || lv_emp_employee_number );
1246 hr_utility.trace(' lv_emp_national_identifier : ' || lv_emp_national_identifier );
1247 hr_utility.trace(' ld_emp_date_of_birth : ' || ld_emp_date_of_birth );
1248 hr_utility.trace(' lv_gender : ' || lv_gender );
1249 hr_utility.trace(' ld_emp_original_date_of_hire : ' || ld_emp_original_date_of_hire );
1250 hr_utility.trace(' ld_emp_projected_start_date : ' || ld_emp_projected_start_date );
1251 hr_utility.trace(' lv_assignment_number : ' || lv_assignment_number );
1252 hr_utility.trace(' lv_employment_category_code : ' || lv_employment_category_code );
1253 */
1254 BEGIN
1255
1256 select hou_org.name
1257 ,hl.location_code
1258 ,ppt.user_person_type
1259 ,past.user_status
1260 into lv_org_name
1261 ,lv_location_code
1262 ,lv_emp_user_person_type
1263 ,lv_assignment_status
1264 from per_person_types ppt,
1265 per_people_f ppf,
1266 hr_locations_all hl,
1267 hr_organization_units hou_org,
1268 per_assignment_status_types past,
1269 per_assignments_f paf
1270 where paf.assignment_id = ln_assignment_id
1271 and ld_effective_date between paf.effective_start_date
1272 and paf.effective_end_date
1273 and hou_org.organization_id = paf.organization_id
1274 and past.assignment_status_type_id = paf.assignment_status_type_id
1275 and hl.location_id = paf.location_id
1276 and ppf.person_id = paf.person_id
1277 and ld_effective_date between ppf.effective_start_date
1278 and ppf.effective_end_date
1279 and ppt.person_type_id = ppf.person_type_id;
1280
1281 EXCEPTION
1282
1283 WHEN NO_DATA_FOUND THEN
1284 lv_org_name := null;
1285 lv_location_code := null;
1286 lv_emp_user_person_type := null;
1287 lv_assignment_status := null;
1288 END;
1289
1290 BEGIN
1291
1292 select hou_gre.name
1293 into lv_gre_name
1294 from hr_organization_units hou_gre
1295 where hou_gre.organization_id = lv_tax_unit_id;
1296
1297 EXCEPTION
1298 WHEN NO_DATA_FOUND THEN
1299 lv_gre_name := null;
1300 END;
1301
1302 /************************************************************
1303 ** If Assignment Set is used, pick up only those employee
1304 ** assignments which are part of the Assignment Set - STATIC
1305 ** or DYNAMIC.
1306 ************************************************************/
1307 hr_utility.set_location(gv_package_name || '.element_extract', 110);
1308 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1309 hr_utility.trace('Assignment Action ID = ' || ln_assignment_action_id);
1310
1311 if hr_assignment_set.assignment_in_set(
1312 p_assignment_set_id
1313 ,ln_assignment_id) = 'Y' then
1314
1315
1316 hr_utility.set_location(gv_package_name || '.element_extract', 120);
1317 /********************************************************************
1318 ** Populate the user defined PL/SQL table to print the additional
1319 ** columns in the report.
1320 ********************************************************************/
1321 pay_element_extract_data_pkg.populate_table(
1322 p_assignment_id => ln_assignment_id
1323 ,p_person_id => ln_person_id
1324 ,p_assignment_action_id => ln_assignment_action_id
1325 ,p_effective_date=> ld_effective_date
1326 );
1327 lv_employment_category := ''; -- Initialise to Blank Bug 4255046
1328 open c_employment_category(lv_employment_category_code);
1329 fetch c_employment_category into lv_employment_category;
1330 close c_employment_category;
1331
1332 hr_utility.set_location(gv_package_name || '.element_extract', 125);
1333 formated_static_data(
1334 lv_action_type
1335 ,ld_effective_date
1336 ,lv_payroll_name
1337 ,lv_gre_name
1338 ,lv_org_name
1339 ,lv_location_code
1340 ,lv_emp_last_name
1341 ,lv_emp_first_name
1342 ,lv_emp_middle_names
1343 ,lv_emp_employee_number
1344 ,lv_emp_national_identifier
1345 ,ld_emp_date_of_birth
1346 ,lv_gender
1347 ,ld_emp_original_date_of_hire
1348 ,ld_emp_projected_start_date
1349 ,lv_emp_user_person_type
1350 ,lv_assignment_number
1351 ,lv_assignment_status
1352 ,lv_employment_category
1353 ,p_output_file_type
1354 ,lv_data_row1
1355 ,lv_data_row2);
1356
1357 lv_data_row := lv_data_row1;
1358 hr_utility.set_location(gv_package_name || '.element_extract', 130);
1359 hr_utility.trace('Effective Date = ' || to_char(ld_effective_date,'dd-mon-yyyy'));
1360 hr_utility.trace('Assignment Action ID = ' || ln_assignment_action_id);
1361 hr_utility.trace('Classification ID = ' ||
1362 nvl(to_char(p_element_classification_id), 'NULL'));
1363 hr_utility.trace('Element Set ID = ' || nvl(to_char(p_element_set_id), 'NULL'));
1364 hr_utility.trace('Element Type ID = ' || nvl(to_char(p_element_type_id), 'NULL'));
1365
1366 -- Bug 3731178 -- Added NVL in select so that element
1367 -- name is fetched if reporting name is NULL.
1368 -- Also added a decode for garn elements.
1369
1370 --Bug 4214739 added column prr.status in below query
1371
1372 --Bug 12605384 -- Removed the condition 'and pet.element_name not like ''%Fees'''
1373 -- from both the parts of query.
1374 --Bug 13914244
1375 lv_element_result_query :=
1376 ' select /*+ leading(pet) */
1377 distinct
1378 nvl(pet.reporting_name,pet.element_name),
1379 prrv.result_value,
1380 prr.Status,
1381 prr.run_result_id
1382 from pay_element_types_f pet,
1383 pay_element_types_f_tl pettl,
1384 pay_input_values_f piv,
1385 pay_run_result_values prrv,
1386 pay_run_results prr,
1387 pay_input_values_f_tl pivtl,
1388 pay_element_classifications pec
1389 where piv.name = ''Pay Value''
1390 and pivtl.input_value_id = piv.input_value_id
1391 and prrv.input_value_id = piv.input_value_id
1392 and piv.element_type_id = pet.element_type_id
1393 and prrv.result_value is not null
1394 and prr.run_result_id = prrv.run_result_id
1395 and prr.assignment_action_id = :cp_assignment_action_id
1396 and pet.classification_id = pec.classification_id
1397 and pet.element_type_id = prr.element_type_id
1398 and pettl.language = userenv(''LANG'')
1399 and pettl.element_type_id = pet.element_type_id
1400 and :cp_effective_date between pet.effective_start_date
1401 and pet.effective_end_date
1402 and pet.element_name not like ''%Special Features''
1403 and pet.element_name not like ''%Special Inputs''
1404 and pet.element_name not like ''%Withholding''
1405 and pet.element_name not like ''%Verifier''
1406 and pet.element_name not like ''%Priority''
1407 and '
1408 || lv_element_cls_where_clause
1409 || ' and ' || lv_element_id_where_clause
1410 || ' and ' || lv_element_set_where_clause
1411 || '
1412 UNION
1413 select /*+ leading(pet) */
1414 distinct
1415 nvl(pet.reporting_name,pet.element_name),
1416 prrv.result_value,
1417 prr.Status,
1418 prr.run_result_id
1419 from pay_element_types_f pet,
1420 pay_element_types_f_tl pettl,
1421 pay_input_values_f piv,
1422 pay_run_result_values prrv,
1423 pay_run_results prr,
1424 pay_input_values_f_tl pivtl,
1425 pay_element_classifications pec,
1426 pay_sub_classification_rules_f scr
1427 where piv.name = ''Pay Value''
1428 and pivtl.input_value_id = piv.input_value_id
1429 and prrv.input_value_id = piv.input_value_id
1430 and piv.element_type_id = pet.element_type_id
1431 and prrv.result_value is not null
1432 and prr.run_result_id = prrv.run_result_id
1433 and prr.assignment_action_id = :cp_assignment_action_id
1434 and scr.classification_id = pec.classification_id
1435 and scr.element_type_id = pet.element_type_id
1436 and pet.element_type_id = prr.element_type_id
1437 and pettl.language = userenv(''LANG'')
1438 and pettl.element_type_id = pet.element_type_id
1439 and :cp_effective_date between pet.effective_start_date
1440 and pet.effective_end_date
1441 and pet.element_name not like ''%Special Features''
1442 and pet.element_name not like ''%Special Inputs''
1443 and pet.element_name not like ''%Withholding''
1444 and pet.element_name not like ''%Verifier''
1445 and pet.element_name not like ''%Priority''
1446 and '
1447 || lv_element_cls_where_clause2
1448 || ' and ' || lv_element_id_where_clause
1449 || ' and ' || lv_element_set_where_clause
1450 ;
1451 open c_element_results FOR lv_element_result_query USING
1452 ln_assignment_action_id
1453 ,ld_effective_date
1454 ,ln_assignment_action_id
1455 ,ld_effective_date;
1456
1457 hr_utility.set_location(gv_package_name || '.element_extract', 140);
1458 loop
1459 fetch c_element_results into lv_element_name
1460 ,lv_element_value
1461 ,lv_element_status
1462 ,ln_run_result_id;
1463 if c_element_results%notfound then
1464 hr_utility.set_location(gv_package_name || '.element_extract', 150);
1465 exit;
1466 end if;
1467
1468 hr_utility.trace('Element Name = ' || lv_element_name ||
1469 ' Value = ' || lv_element_value);
1470 for i in ltr_elements.first .. ltr_elements.last loop
1471 hr_utility.trace('Element Name PL/SQL = ' || ltr_elements(i).element_name);
1472 if ltr_elements(i).element_name = lv_element_name then
1473 hr_utility.set_location(gv_package_name || '.element_extract', 155);
1474
1475 /*******************************************************************
1476 Bug 4214739 Checked if element status is 'R' - Replaced then there is no need to
1477 add the value so made lv_element_value 0
1478 ********************************************************************/
1479 --
1480 -- changed condition from lv_element_status = 'R', due to support eclusive O, U also
1481 --
1482 if lv_element_status not in ('P','PA') then
1483 lv_element_value := 0;
1484 end if;
1485
1486 if ltr_elements(i).value is not null then
1487 ltr_elements(i).value := ltr_elements(i).value + nvl(lv_element_value,0);
1488 else
1489 ltr_elements(i).value := lv_element_value;
1490 end if;
1491 exit ;
1492 end if;
1493 end loop ;
1494
1495 end loop;
1496 close c_element_results;
1497
1498 /*******************************************************************
1499 ** Do Not Print records of an employee, if the employee has non of
1500 ** the elements which are picked up by the report.
1501 *******************************************************************/
1502 hr_utility.set_location(gv_package_name || '.element_extract', 160);
1503 if ltr_elements.count > 0 then
1504 for i in ltr_elements.first .. ltr_elements.last loop
1505 if ltr_elements(i).value is not null then
1506 hr_utility.set_location(gv_package_name || '.element_extract', 165);
1507 lb_print_row := TRUE;
1508 exit;
1509 end if;
1510 end loop;
1511 end if;
1512
1513 /*******************************************************************
1514 ** If there is atleast one column which needs to be printed, print
1515 ** the entire row.
1516 *******************************************************************/
1517 if lb_print_row then
1518 hr_utility.set_location(gv_package_name || '.element_extract', 170);
1519 for i in ltr_elements.first .. ltr_elements.last loop
1520 lv_data_row := lv_data_row ||
1521 formated_data_string (
1522 p_input_string => ltr_elements(i).value
1523 ,p_output_file_type => p_output_file_type);
1524
1525 end loop ;
1526
1527 /****************************************************************
1528 ** Concatnating the second Header Label which includes the User
1529 ** Defined data set so that it is printed at the end of the
1530 ** report.
1531 ****************************************************************/
1532 lv_data_row := lv_data_row || lv_data_row2;
1533
1534 if p_output_file_type ='HTML' then
1535 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1536 end if;
1537
1538 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1539 end if; /************** End of Print Row *************************/
1540
1541 end if; /********** End of Assignment Set ************************/
1542
1543 /*****************************************************************
1544 ** initialize Print Row valiable again
1545 *****************************************************************/
1546 lb_print_row := FALSE;
1547
1548 /*****************************************************************
1549 ** initialize Data varaibles
1550 *****************************************************************/
1551 lv_data_row := null;
1552 lv_data_row1 := null;
1553 lv_data_row2 := null;
1554 if ltr_elements.count > 0 then
1555 for i in ltr_elements.first .. ltr_elements.last loop
1556 ltr_elements(i).value := null ;
1557 end loop ;
1558 end if;
1559
1560 end loop;
1561 close c_assignments;
1562
1563 /*****************************************************
1564 ** Close of the Data Section of the Report
1565 *****************************************************/
1566
1567 if p_output_file_type ='HTML' then
1568 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1569 end if;
1570 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
1571
1572
1573 /**********************************************************
1574 ** Not Required as the output file type is HTML by default
1575 ***********************************************************
1576 if p_output_file_type ='HTML' then
1577 update fnd_concurrent_requests
1578 set output_file_type = 'HTML'
1579 where request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1580
1581 commit;
1582 end if;
1583 **********************************************************/
1584
1585
1586 END element_extract;
1587
1588 --begin
1589 --hr_utility.trace_on(null, 'ELE');
1590 end pay_element_extract_pkg;
[Home] [Help]