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