[Home] [Help]
PACKAGE BODY: APPS.PER_US_EEO4_PKG
Source
4 ******************************************************************
1 PACKAGE BODY "PER_US_EEO4_PKG" AS
2 /* $Header: peruseeo4.pkb 120.12.12020000.4 2012/07/05 04:55:55 amnaraya ship $ */
3 /*
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, IN *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : PER_US_EEO4_PKG
21
22 Description : This package is used by 'EEO4 Report (XML)' concurrent
23 program.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 27-JUN-2006 rpasumar 115.0 Created.
30 19-JUL-2006 rpasumar 115.1 Fixed GSCC Errors.
31 21-JUL-2006 rpasumar 115.2 5402332 Fixed Issues 13 and 22.
32 26-JUL-2006 rpasumar 115.3 5410233 Reset the g_line_number for each report.
33 5410130 Commented out the code which populates
34 the functions into first order table
35 for the condition full time emp count
36 > 999.
37 5397638 Commented out the code which populates
38 the functions into first order table
39 for the condition number of emp for
40 function > 99.
41 28-JUL-2006 rpasumar 115.4 5415136 Added the function get_function_number
42 and generated XML for control number
43 and function numbers (1-15).
44 5409988 Added the function check_function to
45 check appropriate function check box.
46 31-JUL-2006 rpasumar 115.5 5415136 Removed the reporting type EEO4 condition
47 in get_cert_details cursor of
48 generate_juris_cert_xml_data procedure.
49 31-JUL-2006 rpasumar 115.6 5414756 Modified the procedure generate_sql and
50 populate_ft_emp_data to handle the salaries
51 more than 70000 per annum.
52 01-AUG-2006 rpasumar 115.7 5437076 To generate XML when there are no functions.
53 07-AUG-2006 rpasumar 115.8 To display correct function numbers.
54 08-JUN-2007 rpasumar 115.9 5593259 To fetch ethnic code from per_people_extra_info table
55 for the persons whose ethnic code is Two or more races.
56 15-JUL-2007 rpasumar 115.9 6200441 Modified not to report employees whose ethnic origin is
57 blank or whose ethnic origin is 'Two or more races' and
58 additional ethnic category is blank.
59 28-AUG-2009 lbodired 115.14 8812609 Modified the API 'populate_ft_emp_data' to correct the salary ranges
60 18-SEP-2009 lbodired 115.15 7218995 Replaced the hard coded employee categories with the categories
61 115.16 115.17 getting from the API 'pqh.employment_category.fetch_empl_categories'
62 20-APR-2011 nvelaga 115.18 11736960 Modified the package to support the changes to Ethnic Origin Data capture.
63 115.19 Modified g_select_clause to use new package function
64 per_us_hr_utility_pkg.derive_single_race. This deals with people of
65 Two or More race also.
66 Commented the variables and queries declared for usage with Two or More races.
67 04-OCT-2011 nvelaga 115.20 13020321 Changed the data type of variable l_report_date from DATE to VARCHAR2(20).
68 Replaced the condition to_char(l_report_date) with
69 TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') in g_from_where_clause,
70 g_ft_effective_dates and g_nh_effective_dates.
71 15-MAR-2012 nkjaladi 115.21 13610842/ Modified generate_xml_data to modify
75 the full time employee count correctly
72 13610922 text of g_nh_effective_dates so that
73 new hires are counted correctly and also
74 formation of l_query_text to fetch
76 as it was using the fixed employee
77 category instead of the customer
78 defined employee category.
79 21-MAR-2012 agarai 115.22 13645315 Increased the size of l_bg_name from 100
80 to 300 in the procedure generate_footer_xml_data.
81 07-MAY-2012 agarai 115.23 14034810 Added a check in g_from_clause to exclude
82 employees having EEO4A ethnic origin data.
83 Added l_eeo4_ethnic in generate_xml_data
84 procedure to include employees having
85 EEO4 ethnic origin data in the count of
86 employees in each job function.
87 ****************************************************************************/
88
89 -- Added for bug 7218995
90 g_fp_regulars VARCHAR2(2000);
91 g_fp_temps VARCHAR2(2000);
92
93 l_xml_string VARCHAR2(32767);
94
95 l_query_text VARCHAR2(32767);
96
97 g_select_clause VARCHAR2(10000);
98 g_from_where_clause VARCHAR2(10000);
99 g_ft_effective_dates VARCHAR2(1000);
100 g_nh_effective_dates VARCHAR2(1000);
101 g_group_order_by VARCHAR2(1000) := ' GROUP BY hl.lookup_code,hl.meaning
102 ORDER BY hl.lookup_code,hl.meaning';
103 g_nh_sql VARCHAR2(32767);
104 g_ft_emp_sql VARCHAR2(32767);
105 g_oft_sql VARCHAR2(32767);
106
107 /* Commented for bug#11736960 starts
108 -- People with Two or More race are considered in the modified g_select_clause,
109 -- hence commented the variables declared for Two or More race usage.
110
111 --Bug# 5593259
112 g_tmraces_select_clause VARCHAR2(10000);
113 g_tmraces_where_clause VARCHAR2(10000);
114
115 g_tmr_nh_sql VARCHAR2(32767);
116 g_tmr_ft_emp_sql VARCHAR2(32767);
117 g_tmr_oft_sql VARCHAR2(32767);
118 Commented for bug#11736960 ends */
119
120 g_business_group_id VARCHAR2(32767);
121
122 -- Bug# 5415136
123 g_control_number VARCHAR2(1000);
124 g_function_numbers VARCHAR2(1000);
125
126 g_salary_range VARCHAR2(30);
127 g_start_salary NUMBER := 0;
128 g_end_salary NUMBER := 0;
129 g_lookup_code VARCHAR2(30) := ' ';
130 g_meaning VARCHAR2(80) := ' ';
131
132 g_for_all_emp VARCHAR2(1) := 'F';
133
134 g_job_code VARCHAR2(4000) := ' ''XX'' ';
135 g_func_desc VARCHAR2(4000);
136
137 g_dynamic_where VARCHAR2(4000) := ' ''XX'' ';
138 g_line_number NUMBER := 0 ;
139
140 l_counter1 NUMBER := 0;
141 l_counter2 NUMBER := 0;
142 l_counter3 NUMBER := 0;
143
144 l_function_code VARCHAR2(32767);
145
146 -- DBMS Cursor Variables
147 source_cursor INTEGER;
148 rows_processed INTEGER;
149
150 -- PL/SQL table variables
151 ft_emp_table full_time_emp_data;
152 other_ft_emp_table other_full_time_emp_data;
153 new_hire_table new_hire_emp_data;
154
155 -- Functions whose data to be displayed first
156 first_order_func_table function_data;
157 -- Functions whose data to be displayed later
158 second_order_func_table function_data;
159 -- All functions to populate data
160 func_table function_data;
161
162
163 -- variables to hold total number of employees in each category (65th line)
164 /*****************************************************************************
165 Name : convert_into_xml
166 Purpose : function to convert the data into an XML String
167 *****************************************************************************/
168
169 FUNCTION convert_into_xml( p_name IN VARCHAR2,
170 p_value IN VARCHAR2,
171 p_type IN char)
172 RETURN VARCHAR2 IS
173
174 l_convert_data VARCHAR2(32767);
175
176 BEGIN
177
178 IF p_type = 'D' THEN
179
180 l_convert_data := '<'||p_name||'>'||p_value||'</'||p_name||'>';
181
182 ELSE
183
184 l_convert_data := '<'||p_name||'>';
185
186 END IF;
187
188 RETURN(l_convert_data);
189
190 END convert_into_xml;
191
192 /*****************************************************************************
193 Name : get_lookup_meaning
194 Purpose : To display the meaning of job categories on the report.
195 *****************************************************************************/
196
197 FUNCTION get_lookup_meaning(p_emp_category IN NUMBER, p_lookup_code IN NUMBER)
198 RETURN VARCHAR2 IS
199 l_meaning VARCHAR2(80);
200
201 BEGIN
202 IF p_lookup_code = 2 THEN
203 l_meaning := 'PROFESSIONALS';
204 ELSIF p_lookup_code = 3 THEN
205 l_meaning := 'TECHNICIANS';
206 ELSIF p_lookup_code = 4 THEN
207 l_meaning := 'PROTECTIVE SERVICE';
208 ELSIF p_lookup_code = 5 THEN
209 -- Bug# 5402332 (Issue 22)
210 IF p_emp_category = 1 THEN
211 l_meaning := 'PARA-PROFESSIONALS';
212 ELSE
216 IF p_emp_category = 1 THEN
213 l_meaning := 'PARA-PROFESSIONAL';
214 END IF;
215 ELSIF p_lookup_code = 1 THEN
217 l_meaning := 'OFFICIALS ADMINISTRATORS';
218 ELSE
219 l_meaning := 'OFFICIALS/ADMIN';
220 END IF;
221 ELSIF p_lookup_code = 6 THEN
222 IF p_emp_category = 1 THEN
223 l_meaning := 'ADMINISTRATIVE SUPPORT';
224 ELSE
225 l_meaning := 'ADMIN.SUPPORT';
226 END IF;
227 ELSIF p_lookup_code = 7 THEN
228 IF p_emp_category = 1 THEN
229 l_meaning := 'SKILLEDCRAFT';
230 ELSE
231 l_meaning := 'SKILLED CRAFT';
232 END IF;
233 ELSIF p_lookup_code = 8 THEN
234 IF p_emp_category = 1 THEN
235 l_meaning := 'SERVICE MAINTENANCE';
236 ELSE
237 l_meaning := 'SERVICE/MAINTENANCE';
238 END IF;
239 END IF;
240
241 RETURN l_meaning;
242 END get_lookup_meaning;
243
244 -- Added this method for the Bug# 5409988
245
246 /*****************************************************************************
247 Name : check_function
248 Purpose : To generate XML for those functions which have to be checked
249 in the first page of the report.
250 *****************************************************************************/
251 PROCEDURE check_function(p_function_code IN NUMBER) IS
252 BEGIN
253 IF p_function_code = 10 THEN
254 l_xml_string := convert_into_xml('G_FUN_1_CHECK_VAL','true','D');
255 ELSIF p_function_code = 20 THEN
256 l_xml_string := convert_into_xml('G_FUN_2_CHECK_VAL','true','D');
257 ELSIF p_function_code = 30 THEN
258 l_xml_string := convert_into_xml('G_FUN_3_CHECK_VAL','true','D');
259 ELSIF p_function_code = 40 THEN
260 l_xml_string := convert_into_xml('G_FUN_4_CHECK_VAL','true','D');
261 ELSIF p_function_code = 50 THEN
262 l_xml_string := convert_into_xml('G_FUN_5_CHECK_VAL','true','D');
263 ELSIF p_function_code = 60 THEN
264 l_xml_string := convert_into_xml('G_FUN_6_CHECK_VAL','true','D');
265 ELSIF p_function_code = 70 THEN
266 l_xml_string := convert_into_xml('G_FUN_7_CHECK_VAL','true','D');
267 ELSIF p_function_code = 80 THEN
268 l_xml_string := convert_into_xml('G_FUN_8_CHECK_VAL','true','D');
269 ELSIF p_function_code = 90 THEN
270 l_xml_string := convert_into_xml('G_FUN_9_CHECK_VAL','true','D');
271 ELSIF p_function_code = 100 THEN
272 l_xml_string := convert_into_xml('G_FUN_10_CHECK_VAL','true','D');
273 ELSIF p_function_code = 110 THEN
274 l_xml_string := convert_into_xml('G_FUN_11_CHECK_VAL','true','D');
275 ELSIF p_function_code = 120 THEN
276 l_xml_string := convert_into_xml('G_FUN_12_CHECK_VAL','true','D');
277 ELSIF p_function_code = 130 THEN
278 l_xml_string := convert_into_xml('G_FUN_13_CHECK_VAL','true','D');
279 ELSIF p_function_code = 140 THEN
280 l_xml_string := convert_into_xml('G_FUN_14_CHECK_VAL','true','D');
281 ELSIF p_function_code = 150 THEN
282 l_xml_string := convert_into_xml('G_FUN_15_CHECK_VAL','true','D');
283 END IF;
284
285 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
286 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
287 END check_function;
288
289 -- Added Bug# 5415136
290 /*****************************************************************************
291 Name : get_function_number
292 Purpose : To return the function number with the function code as input.
293 *****************************************************************************/
294 FUNCTION get_function_number(p_function_code IN NUMBER)
295 RETURN NUMBER IS
296 l_function_number NUMBER := 0;
297 BEGIN
298 IF p_function_code = 10 THEN
299 l_function_number := 1;
300 ELSIF p_function_code = 20 THEN
301 l_function_number := 2;
302 ELSIF p_function_code = 30 THEN
303 l_function_number := 3;
304 ELSIF p_function_code = 40 THEN
305 l_function_number := 4;
306 ELSIF p_function_code = 50 THEN
307 l_function_number := 5;
308 ELSIF p_function_code = 60 THEN
309 l_function_number := 6;
310 ELSIF p_function_code = 70 THEN
311 l_function_number := 7;
312 ELSIF p_function_code = 80 THEN
313 l_function_number := 8;
314 ELSIF p_function_code = 90 THEN
315 l_function_number := 9;
316 ELSIF p_function_code = 100 THEN
317 l_function_number := 10;
318 ELSIF p_function_code = 110 THEN
319 l_function_number := 11;
320 ELSIF p_function_code = 120 THEN
321 l_function_number := 12;
322 ELSIF p_function_code = 130 THEN
323 l_function_number := 13;
324 ELSIF p_function_code = 140 THEN
325 l_function_number := 14;
326 ELSIF p_function_code = 150 THEN
327 l_function_number := 15;
328 END IF;
329 RETURN l_function_number;
330 END get_function_number;
331
332
333
334 /*****************************************************************************
335 Name : write_to_concurrent_out
336 Purpose : writes to concurrent ouput.
337 *****************************************************************************/
338 PROCEDURE write_to_concurrent_out (p_text VARCHAR2) IS
339 --
340 BEGIN
341 -- Write to the concurrent request log
342 --fnd_file.put_line(fnd_file.log, p_text);
343 -- Write to the concurrent request out
344 fnd_file.put_line(fnd_file.OUTPUT, p_text);
345
346 END write_to_concurrent_out;
347
348 PROCEDURE generate_sql(p_job_codes IN VARCHAR2 , p_dynamic_where IN VARCHAR2) IS
349 BEGIN
350 g_nh_sql := 'SELECT hl.lookup_code job_category_code,
351 hl.meaning job_category_name,'
355 -- ||' AND ass.employment_category in (''FR'')'
352 || g_select_clause || g_from_where_clause || g_nh_effective_dates
353 ||' AND job.job_information7 in (' || p_job_codes || ')'
354 ||' AND hl.lookup_code = :1 '
356 ||' AND ass.employment_category in ('|| g_fp_regulars ||')'
357 || g_group_order_by;
358
359 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_nh_sql: '|| g_nh_sql);
360
361 g_ft_emp_sql := 'SELECT hl.lookup_code job_category_code,
362 hl.meaning job_category_name,'
363 ||g_select_clause || g_from_where_clause|| g_ft_effective_dates
364 ||' AND job.job_information7 in ( ' || p_job_codes || ' ) '
365 ||' AND hl.lookup_code = :1 '
366 || p_dynamic_where
367 -- ||' AND ass.employment_category in (''FR'')'
368 ||' AND ass.employment_category in ('|| g_fp_regulars ||')'
369 || g_group_order_by;
370
371 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_ft_emp_sql: '|| g_ft_emp_sql);
372
373 g_oft_sql := 'SELECT hl.lookup_code job_category_code,
374 hl.meaning job_category_name,'
375 ||g_select_clause||g_from_where_clause||g_ft_effective_dates
376 ||' AND job.job_information7 in ( ' || p_job_codes || ' )'
377 ||' AND hl.lookup_code = :1 '
378 -- ||' AND ass.employment_category NOT IN (''FR'')'
379 ||' AND ass.employment_category NOT IN ('|| g_fp_regulars ||')'
380 ||g_group_order_by;
381
382 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_oft_sql: '|| g_oft_sql);
383
384
385 /* Commented for bug#11736960 starts
386 --Bug# 5593259
387 g_tmr_nh_sql := 'SELECT hl.lookup_code job_category_code,
388 hl.meaning job_category_name,'
389 || g_tmraces_select_clause || g_tmraces_where_clause || g_nh_effective_dates
390 ||' AND job.job_information7 in (' || p_job_codes || ')'
391 ||' AND hl.lookup_code = :1 '
392 -- ||' AND ass.employment_category in (''FR'')'
393 ||' AND ass.employment_category in ('||g_fp_regulars ||')'
394 || g_group_order_by;
395
396 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_tmr_nh_sql: '|| g_tmr_nh_sql);
397
398 g_tmr_ft_emp_sql := 'SELECT hl.lookup_code job_category_code,
399 hl.meaning job_category_name,'
400 || g_tmraces_select_clause || g_tmraces_where_clause|| g_ft_effective_dates
401 ||' AND job.job_information7 in ( ' || p_job_codes || ' ) '
402 ||' AND hl.lookup_code = :1 '
403 || p_dynamic_where
404 -- ||' AND ass.employment_category in (''FR'')'
405 ||' AND ass.employment_category in ('|| g_fp_regulars ||')'
406 || g_group_order_by;
407
408 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_tmr_ft_emp_sql: '|| g_tmr_ft_emp_sql);
409
410 g_tmr_oft_sql := 'SELECT hl.lookup_code job_category_code,
411 hl.meaning job_category_name,'
412 || g_tmraces_select_clause || g_tmraces_where_clause ||g_ft_effective_dates
413 ||' AND job.job_information7 in ( ' || p_job_codes || ' )'
414 ||' AND hl.lookup_code = :1 '
415 -- ||' AND ass.employment_category NOT IN (''FR'')'
416 ||' AND ass.employment_category NOT IN ('|| g_fp_regulars ||')'
417 ||g_group_order_by;
418
419 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_tmr_oft_sql: '|| g_tmr_oft_sql);
420 Commented for bug#11736960 ends */
421
422 END generate_sql;
423
424 PROCEDURE generate_xml_data(errbuf OUT NOCOPY VARCHAR2
425 ,retcode OUT NOCOPY NUMBER
426 ,p_reporting_year IN NUMBER
427 ,p_add_message1 IN VARCHAR2
428 ,p_add_message2 IN VARCHAR2
429 ,p_add_message3 IN VARCHAR2
430 ,p_add_message4 IN VARCHAR2
431 ,p_add_message5 IN VARCHAR2
432 ,p_add_message6 IN VARCHAR2
433 ,p_add_message7 IN VARCHAR2
434 ,p_business_group_id IN VARCHAR2
435 ,p_full_time_emp_count IN NUMBER
436 ,p_emp_count_for_function IN NUMBER
437 ) IS
438
439 l_ft_emp_count NUMBER;
440
441 l_fr VARCHAR2(2000);
442 l_ft VARCHAR2(2000);
443 l_pr VARCHAR2(2000);
444 l_pt VARCHAR2(2000);
445
446 l_profile_option VARCHAR2(10);
447
448 l_function_name VARCHAR2(240);
449 l_function_count NUMBER := 0;
450 l_function_desc VARCHAR2(80);
451 l_cur_function_desc VARCHAR2(260);
452
453 l_report_command VARCHAR2(500);
454
455
456 l_row_count NUMBER := 0;
457 l_frc VARCHAR2(2000);
458
459 -- counters for PL/SQL tables
460 l_counter NUMBER := 0;
461 l_fo_funct_counter NUMBER := 0;
462 l_so_funct_counter NUMBER := 0;
463 l_funct_counter NUMBER := 0;
464
465 l_current_function VARCHAR2(30);
466 l_cur_lookup_code VARCHAR2(30);
467 l_prev_lookup_code VARCHAR2(30);
468 l_function VARCHAR2(30);
469
470 l_start_salary NUMBER;
471 l_end_salary NUMBER;
472
473 l_lookup_code VARCHAR2(30);
474 l_meaning VARCHAR2(80);
475 l_eeo4_ethnic VARCHAR2(1000); --added for bug 14034810
476
477 l_report_day_month VARCHAR2(20) := '30-06';
478 l_report_year VARCHAR2(4) := p_reporting_year;
482
479 --l_report_date DATE := to_date(l_report_day_month || '-' || l_report_year, 'DD-MM-YYYY'); --Bug#13020321
480
481 l_report_date VARCHAR2(20) := l_report_day_month || '-' || l_report_year; --Bug#13020321
483 -- Dynamic SQL Variables
484 CURSOR get_eeo4_lookup_details IS
485 SELECT lookup_code, meaning, description
486 FROM hr_lookups
487 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
488 ORDER BY lookup_code;
489
490 CURSOR func_desc_cur(l_lookup_code VARCHAR) IS
491 SELECT meaning
492 FROM hr_lookups
493 WHERE lookup_type = 'US_EEO4_JOB_FUNCTIONS'
494 AND lookup_code = l_lookup_code;
495
496 BEGIN
497
498 g_business_group_id := p_business_group_id;
499
500 -- Added for bug#11736960
501 g_select_clause := ' count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
502 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''M'',1,null),null)) no_cons_bmale_emps,
503 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
504 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''M'',1,null),5,decode(peo.sex,''M'',1,null),null)) no_cons_amale_emps,
505 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''M'',1,null),null)) no_cons_imale_emps,
506 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
507 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''F'',1,null),null)) no_cons_bfemale_emps,
508 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
509 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''F'',1,null),5,decode(peo.sex,''F'',1,null),null)) no_cons_afemale_emps,
510 count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''F'',1,null),null)) no_cons_ifemale_emps ';
511
512 /* Commented for bug#11736960 starts
513 g_select_clause := ' count(decode(peo.per_information1,''1'',decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
514 count(decode(peo.per_information1,''2'',decode(peo.sex,''M'',1,null),null)) no_cons_bmale_emps,
515 count(decode(peo.per_information1,''3'',decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
516 count(decode(peo.per_information1,''4'',decode(peo.sex,''M'',1,null),''5'',decode(peo.sex,''M'',1,null),null)) no_cons_amale_emps,
517 count(decode(peo.per_information1,''6'',decode(peo.sex,''M'',1,null),null)) no_cons_imale_emps,
518 count(decode(peo.per_information1,''1'',decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
519 count(decode(peo.per_information1,''2'',decode(peo.sex,''F'',1,null),null)) no_cons_bfemale_emps,
520 count(decode(peo.per_information1,''3'',decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
521 count(decode(peo.per_information1,''4'',decode(peo.sex,''F'',1,null),''5'',decode(peo.sex,''F'',1,null),null)) no_cons_afemale_emps,
522 count(decode(peo.per_information1,''6'',decode(peo.sex,''F'',1,null),null)) no_cons_ifemale_emps ';
523 Commented for bug#11736960 ends */
524
525 --Bug#13020321 - Replaced to_char(l_report_date) with TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')
526 --Bug#13610922 - Changed sysdate to TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')
527 g_from_where_clause := ' FROM per_all_people_f peo,
528 per_all_assignments_f ass,
529 per_assignment_status_types past,
530 per_pay_proposals ppp,
531 per_jobs job,
532 per_pay_bases ppb,
533 hr_lookups hl
534 WHERE peo.person_id = ass.person_id
535 AND ass.pay_basis_id = ppb.pay_basis_id
536 AND ass.assignment_id = ppp.assignment_id
537 AND hl.lookup_code = job.job_information1
538 AND job.job_information1 IS NOT NULL
539 AND job.job_information_category = ''US''
540 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
541 AND ass.job_id = job.job_id
542 AND ass.primary_flag = ''Y''
543 AND ppp.change_date = ( SELECT MAX(change_date)
544 FROM per_pay_proposals pro
545 WHERE ppp.assignment_id = pro.assignment_id
546 AND pro.change_date <= TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')
547 AND pro.approved = ''Y'' )
548 AND ass.organization_id IN (SELECT organization_id
549 FROM hr_all_organization_units
550 WHERE business_group_id = ' || p_business_group_id || '
551 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN date_from AND NVL(date_to,TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')) )
552 AND ass.assignment_status_type_id = past.assignment_status_type_id
553 AND peo.current_employee_flag = ''Y''
554 AND ass.assignment_type = ''E''
558 WHERE ppei.person_id = peo.person_id
555 --added for bug 14034810
556 AND (NOT EXISTS (SELECT ''X''
557 FROM per_people_extra_info ppei
559 AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN''
560 AND(DECODE(ppei.pei_information1, ''Y'', 1,0)
561 + DECODE(ppei.pei_information2, ''Y'', 1,0)
562 + DECODE(ppei.pei_information3, ''Y'', 1,0)
563 + DECODE(ppei.pei_information4, ''Y'', 1,0)
564 + DECODE(ppei.pei_information5, ''Y'', 1,0)
565 + DECODE(ppei.pei_information6, ''Y'', 1,0)
566 ) >= 1
567 )
568 )';
569
570 /* Commented for bug#11736960 starts
571 -- People with Two or More race are considered in the modified g_select_clause, hence commented this portion of code.
572 --Bug# 5593259
573 g_tmraces_select_clause := ' count(decode(pei.pei_information5,''1'',decode(peo.sex,''M'',1,null),null)) no_tmraces_wmale_emps,
574 count(decode(pei.pei_information5,''2'',decode(peo.sex,''M'',1,null),null)) no_tmraces_bmale_emps,
575 count(decode(pei.pei_information5,''3'',decode(peo.sex,''M'',1,null),''9'',decode(peo.sex,''M'',1,null),null)) no_tmraces_hmale_emps,
576 count(decode(pei.pei_information5,''4'',decode(peo.sex,''M'',1,null),''5'',decode(peo.sex,''M'',1,null),null)) no_tmraces_amale_emps,
577 count(decode(pei.pei_information5,''6'',decode(peo.sex,''M'',1,null),null)) no_tmraces_imale_emps,
578 count(decode(pei.pei_information5,''1'',decode(peo.sex,''F'',1,null),null)) no_tmraces_wfemale_emps,
579 count(decode(pei.pei_information5,''2'',decode(peo.sex,''F'',1,null),null)) no_tmraces_bfemale_emps,
580 count(decode(pei.pei_information5,''3'',decode(peo.sex,''F'',1,null),''9'',decode(peo.sex,''F'',1,null),null)) no_tmraces_hfemale_emps,
581 count(decode(pei.pei_information5,''4'',decode(peo.sex,''F'',1,null),''5'',decode(peo.sex,''F'',1,null),null)) no_tmraces_afemale_emps,
582 count(decode(pei.pei_information5,''6'',decode(peo.sex,''F'',1,null),null)) no_tmraces_ifemale_emps';
583
584
585 g_tmraces_where_clause := ' FROM per_all_people_f peo,
586 per_all_assignments_f ass,
587 per_assignment_status_types past,
588 per_pay_proposals ppp,
589 per_jobs job,
590 per_pay_bases ppb,
591 hr_lookups hl,
592 per_people_extra_info pei
593 WHERE peo.person_id = ass.person_id
594 AND peo.per_information1 = ''13''
595 AND peo.person_id = pei.person_id(+)
596 AND pei.information_type = ''PER_US_ADDL_ETHNIC_CAT''
597 AND pei.pei_information5 is not null
598 AND ass.pay_basis_id = ppb.pay_basis_id
599 AND ass.assignment_id = ppp.assignment_id
600 AND hl.lookup_code = job.job_information1
601 AND job.job_information1 IS NOT NULL
602 AND job.job_information_category = ''US''
603 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
604 AND ass.job_id = job.job_id
605 AND ass.primary_flag = ''Y''
606 AND ppp.change_date = ( SELECT MAX(change_date)
607 FROM per_pay_proposals pro
608 WHERE ppp.assignment_id = pro.assignment_id
609 AND pro.change_date <= ''' || to_char(l_report_date) || '''
610 AND pro.approved = ''Y'' )
611 AND ass.organization_id IN (SELECT organization_id
612 FROM hr_all_organization_units
613 WHERE business_group_id = ' || p_business_group_id || '
614 AND SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE) )
615 AND ass.assignment_status_type_id = past.assignment_status_type_id
616 AND peo.current_employee_flag = ''Y''
617 AND ass.assignment_type = ''E''';
618 Commented for bug#11736960 ends */
619
620 /*
621 -- #13610922 commented the query text building as it has to built after fetching the custom employement
622 -- categories
623 l_query_text := 'select count(1) l_ft_emp_count ' || g_from_where_clause
624 || g_ft_effective_dates || ' AND ass.employment_category in (''FR'')';
625 */
626
627 -- Status and Hire/Fire condition for Full-Time employees
628 -- different from New-Hires.
629
630 --Bug#13020321 - Replaced to_char(l_report_date) with TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')
634 -- Not a terminated assignment.
631 g_ft_effective_dates := ' AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN peo.effective_start_date AND peo.effective_end_date
632 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN ass.effective_start_date AND ass.effective_end_date
633 AND past.per_system_status <> ''TERM_ASSIGN''';
635 --condition for checking that the assignment status not corresponds to TERM_ASSIGN
636
637 --criteria for deriving new-hire records modified by kgowripe
638 /*Bug#13020321 - Replaced to_char(l_report_date) with TO_DATE(''' || l_report_date|| ''', ''DD-MM-YYYY'')
639 Replaced to_char(add_months(l_report_date, -12) + 1) with (add_months(TO_DATE(l_report_date, 'DD-MM-YYYY'), -12) + 1) */
640 /* #13610842 changes start Modified the g_nh_effective_dates to use exists condition
641 g_nh_effective_dates := ' AND TO_DATE(''' || l_report_date|| ''', ''DD-MM-YYYY'') BETWEEN peo.effective_start_date AND peo.effective_end_date
642 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN ass.effective_start_date AND ass.effective_end_date
643 AND (SELECT date_start
644 FROM per_periods_of_service
645 WHERE period_of_service_id = ass.period_of_service_id)
646 BETWEEN (add_months(TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY''), -12) + 1)
647 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') ';*/
648 g_nh_effective_dates := ' AND TO_DATE(''' || l_report_date|| ''', ''DD-MM-YYYY'') BETWEEN peo.effective_start_date AND peo.effective_end_date
649 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN ass.effective_start_date AND ass.effective_end_date
650 AND EXISTS (SELECT 1
651 FROM per_periods_of_service pps
652 WHERE pps.period_of_service_id = ass.period_of_service_id
653 AND pps.person_id = peo.person_id
654 AND pps.date_start BETWEEN (add_months(TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY''), -12) + 1)
655 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')) ';
656 --#13610842 changes end
657 --added for bug 14034810
658 l_eeo4_ethnic := ' AND ( EXISTS (SELECT ''X''
659 FROM per_people_extra_info ppei
660 WHERE ppei.person_id = peo.person_id
661 AND ppei.information_type = ''US_ETHNIC_ORIGIN''
662 AND(DECODE(ppei.pei_information1, ''Y'', 1,0)
663 + DECODE(ppei.pei_information2, ''Y'', 1,0)
664 + DECODE(ppei.pei_information3, ''Y'', 1,0)
665 + DECODE(ppei.pei_information4, ''Y'', 1,0)
666 + DECODE(ppei.pei_information5, ''Y'', 1,0)
667 + DECODE(ppei.pei_information6, ''Y'', 1,0)
668 ) = 1
669 )
670 )';
671
672 -- write Header line
673
674 -- Fetch the list of employment categories
675 pqh_employment_category.fetch_empl_categories(p_business_group_id, l_fr, l_ft, l_pr, l_pt);
676
677 -- Added for bug 7218995
678 g_fp_regulars := replace(replace(l_fr,'''',''''),',',''',''');
679 g_fp_temps := replace(replace(l_ft,'''',''''),',',''',''')||',' ||replace(replace(l_pt,'''',''''),',',''',''') ||',' ||replace(replace(l_pr,'''',''''),',',''',''');
680
681 --
682 -- #13610922 changes start
683 -- Employement categories would be custom defined so building the query text
684 -- after the custom Full Time regulars status are fetched
685 --
686 FND_FILE.PUT_LINE(FND_FILE.LOG,'G_FP_REGULARS: '||g_fp_regulars);
687 -- added l_eeo4_ethnic for bug 14034810
688 l_query_text := 'select count(1) l_ft_emp_count ' || g_from_where_clause|| l_eeo4_ethnic
689 || g_ft_effective_dates || ' AND ass.employment_category in ('||g_fp_regulars||')';
690 --#13610922 changes end
691
692 source_cursor := dbms_sql.open_cursor;
693 dbms_sql.parse(source_cursor,l_query_text,1);
694 dbms_sql.define_column(source_cursor,1,l_ft_emp_count);
695
696 rows_processed := dbms_sql.EXECUTE(source_cursor);
697
698 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
699 dbms_sql.column_value(source_cursor, 1, l_ft_emp_count);
700 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_ft_emp_count: ' || l_ft_emp_count);
701
702 END IF;
703
704 dbms_sql.close_cursor(source_cursor);
705 --added l_eeo4_ethnic for bug 14034810
706 l_query_text := 'Select job.job_information7 l_function_code,
707 count(1) l_function_count ' || g_from_where_clause || l_eeo4_ethnic || g_ft_effective_dates || ' group by job.job_information7 ';
708 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_query_text: '||l_query_text);
709 source_cursor := dbms_sql.open_cursor;
710 dbms_sql.parse(source_cursor,l_query_text,2);
711 dbms_sql.define_column_char(source_cursor,1,l_function_code,30);
712 dbms_sql.define_column(source_cursor,2,l_function_count);
713 rows_processed := dbms_sql.EXECUTE(source_cursor);
714
715 LOOP
716 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
717 dbms_sql.column_value_char(source_cursor,1,l_function_code);
718 dbms_sql.column_value(source_cursor,2,l_function_count);
719 l_function_code := RTRIM(l_function_code);
720
721 -- Fetch the function description
722 IF l_function_code IS NOT NULL THEN
723
727
724 OPEN func_desc_cur(l_function_code);
725 FETCH func_desc_cur INTO l_function_desc;
726 CLOSE func_desc_cur;
728 /* If full time employee count is more than 999 Then
729 a) Populate first_order_func_table, for first 14 functions
730 b) Populate second_order_func_table for the rest
731 */
732
733 IF (l_ft_emp_count >= p_full_time_emp_count AND l_row_count <= 14) THEN
734 --FND_FILE.PUT_LINE(FND_FILE.LOG,' i AM IN IF (l_ft_emp_count >= p_full_time_emp_count AND l_row_count <= 14)');
735 g_dynamic_where := g_dynamic_where || ',' || l_function_code;
736
737 -- Commented the folowing to fix the bug# 5410130
738 /*
739 l_fo_funct_counter := l_fo_funct_counter + 1;
740 first_order_func_table(l_fo_funct_counter).job_function := l_function_code;
741 first_order_func_table(l_fo_funct_counter).description := l_function_desc;
742 */
743
744 l_so_funct_counter := l_so_funct_counter + 1;
745 second_order_func_table(l_so_funct_counter).job_function := l_function_code;
746 second_order_func_table(l_so_funct_counter).description := l_function_desc;
747
748 -- Commented the folowing to fix the bug# 5410130
749 /*
750 ELSIF (l_ft_emp_count >= p_full_time_emp_count AND l_row_count > 14) THEN
751 --FND_FILE.PUT_LINE(FND_FILE.LOG,' i AM IN ELSIF (l_ft_emp_count >= p_full_time_emp_count AND l_row_count > 14) THEN');
752 l_fo_funct_counter := l_fo_funct_counter + 1;
753 first_order_func_table(l_fo_funct_counter).job_function := l_function_code;
754 first_order_func_table(l_fo_funct_counter).description := l_function_desc;
755 */
756
757 END IF;
758
759 /* If full time employee count is less than 1000 Then
760 a) Populate first_order_func_table, if the function has more than 100 employees
761 b) Populate second_order_func_table for all functions
762 */
763 IF (l_ft_emp_count < p_full_time_emp_count AND l_function_count >= p_emp_count_for_function) THEN
764 --FND_FILE.PUT_LINE(FND_FILE.LOG,' i AM IN IF (l_ft_emp_count < p_full_time_emp_count AND l_function_count >= p_emp_count_for_function) THEN ');
765 g_dynamic_where := g_dynamic_where || ',' || l_function_code;
766 -- Commented the folowing to fix the bug# 5397638
767 /*
768 l_fo_funct_counter := l_fo_funct_counter + 1;
769 first_order_func_table(l_fo_funct_counter).job_function := l_function_code;
770 first_order_func_table(l_fo_funct_counter).description := l_function_desc;
771 */
772 l_so_funct_counter := l_so_funct_counter + 1;
773 second_order_func_table(l_so_funct_counter).job_function := l_function_code;
774 second_order_func_table(l_so_funct_counter).description := l_function_desc;
775 ELSIF (l_ft_emp_count < p_full_time_emp_count AND l_function_count < p_emp_count_for_function) THEN
776 --FND_FILE.PUT_LINE(FND_FILE.LOG,' i AM IN ELSIF (l_ft_emp_count < p_full_time_emp_count AND l_function_count < p_emp_count_for_function) THEN');
777 l_fo_funct_counter := l_fo_funct_counter + 1;
778 first_order_func_table(l_fo_funct_counter).job_function := l_function_code;
779 first_order_func_table(l_fo_funct_counter).description := l_function_desc;
780 END IF;
781 END IF; -- IF l_function_code IS NOT NULL THEN
782
783 l_row_count := l_row_count + 1;
784 ELSE
785 EXIT;
786 END IF;
787 END LOOP;
788
789 dbms_sql.close_cursor(source_cursor);
790
791 -- Bug# 5437076
792 IF l_fo_funct_counter = 0 AND l_so_funct_counter = 0 THEN
793 l_fo_funct_counter := l_fo_funct_counter + 1;
794 first_order_func_table(l_fo_funct_counter).job_function := 'X';
795 first_order_func_table(l_fo_funct_counter).description := 'X';
796 END IF;
797
798 l_current_function := ' ';
799
800 g_job_code := '''X''';
801 g_func_desc := '''X''';
802
803 -- Populate PL/SQL tables
804
805 FOR i IN 1 .. first_order_func_table.count LOOP -- For Each Function
806 l_function := first_order_func_table(i).job_function;
807 l_function_desc := first_order_func_table(i).description;
808 IF l_function <> l_current_function THEN
809 --FND_FILE.PUT_LINE(FND_FILE.LOG,' I am in FOR i IN 1 .. first_order_func_table.count LOOP');
810 l_current_function := l_function;
811
812 IF g_job_code = '''X''' THEN
813 g_job_code := ''''|| l_current_function ||'''';
814 g_func_desc := l_function_desc;
815 ELSE
816 g_job_code := g_job_code|| ', ''' ||l_current_function||'''';
817 g_func_desc := g_func_desc|| ' , ' ||l_function_desc;
818 END IF;
819 END IF;
820 END LOOP; -- For Each Function
821
822 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_job_code: '||g_job_code);
823 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_func_desc: '||g_func_desc);
824
825 g_for_all_emp := 'T';
826
827 -- Bug# 5410130
828 --IF g_job_code <> '''X''' THEN
829 --generate_sql(g_job_code);
830 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_nh_sql : '|| g_nh_sql);
831 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_ft_emp_sql : ' || g_ft_emp_sql);
832 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_oft_sql : ' || g_oft_sql);
833 populate_ft_emp_data(g_job_code);
834 populate_oft_emp_data(g_job_code);
835 populate_nh_emp_data(g_job_code);
836 --END IF;
837
838 g_for_all_emp := 'F';
839
840 FOR i IN 1 .. second_order_func_table.count LOOP
841 --FND_FILE.PUT_LINE(FND_FILE.LOG,'FOR i IN 1 .. second_order_func_table.count LOOP');
842 l_function := second_order_func_table(i).job_function;
843 l_function_desc := second_order_func_table(i).description;
844 IF l_function <> l_current_function THEN
845 l_current_function := l_function;
846 l_funct_counter := l_funct_counter + 1;
847 --generate_sql(l_current_function);
848 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_nh_sql : '|| g_nh_sql);
849 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_ft_emp_sql : ' || g_ft_emp_sql);
850 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_oft_sql : ' || g_oft_sql);
851 populate_ft_emp_data(l_current_function);
852 populate_oft_emp_data(l_current_function);
853 populate_nh_emp_data(l_current_function);
854 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_function : '||l_function ||' inserted into func_table');
855 END IF;
856 END LOOP; -- end of second_order_func_table */
857
858 -- Show the functions data whose data should be displayed first
859 generate_header_xml_data();
860 --generate_juris_cert_xml_data();
861 -- create a report for all the functions in the first order table.
862 -- Bug# 5410130
863 IF first_order_func_table.count <> 0 THEN
864 create_report(1);
865 END IF;
866
867 -- create a separate report for each function in the second order table.
868 create_report(2);
869 generate_footer_xml_data();
870
871 END generate_xml_data;
872
873 PROCEDURE create_report(report_type NUMBER) IS
874 l_current_function VARCHAR2(30);
875 l_function VARCHAR2(30);
876 l_function_desc VARCHAR2(80);
877
878 BEGIN
879
880 IF report_type = 1 THEN
881
882 -- Show the functions data whose data should be displayed first
883 l_xml_string := '<G_REPORT>';
884
885 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
886 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
887 --FND_FILE.PUT_LINE(FND_FILE.LOG,' Calling create_xml for the function: '||g_job_code||' with description: '||g_func_desc);
888
889 -- Bug# 5415136
890 g_function_numbers := '''X''';
891
892 --FND_FILE.PUT_LINE(FND_FILE.LOG,'g_function_numbers: ' || g_function_numbers);
893
894 FOR i IN 1 .. first_order_func_table.count LOOP
895
896 l_current_function := first_order_func_table(i).job_function;
897
898 IF g_function_numbers = '''X''' THEN
899 IF l_current_function = 'X' THEN
900 g_function_numbers := 0;
901 ELSE
902 g_function_numbers := get_function_number(l_current_function);
903 END IF;
904 ELSE
905 g_function_numbers := g_function_numbers || ', ' || get_function_number(l_current_function);
906 END IF;
907 END LOOP;
908
909 generate_juris_cert_xml_data();
910
911 l_xml_string := convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
912 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
913
914 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
915 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
916
917
918 -- Bug# 5409988
919 FOR i IN 1 .. first_order_func_table.count LOOP
920 l_current_function := first_order_func_table(i).job_function;
921 IF l_current_function <> 'X' THEN
922 check_function(to_number(l_current_function));
923 END IF;
924 END LOOP;
925
926 l_xml_string := '<LIST_G_JOB_FUNCTION>';
927 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
928 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
929
930 -- Bug# 5410233.
931 g_line_number := 0;
932
933 g_for_all_emp := 'T';
934 create_xml(g_job_code);
935 g_for_all_emp := 'F';
936
937 l_xml_string := '</LIST_G_JOB_FUNCTION></G_REPORT>';
938
939 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
940 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
941
942 ELSIF report_type = 2 THEN
943
944 -- Show the functions data whose data should be displayed later
945
946 FOR i IN 1 .. second_order_func_table.count LOOP -- For Each Function
947
948 l_current_function := second_order_func_table(i).job_function;
949 g_func_desc := second_order_func_table(i).description;
950
951 -- Bug# 5415136
952 g_function_numbers := get_function_number(l_current_function);
953
954 l_xml_string := '<G_REPORT>';
958
955 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
956 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
957 generate_juris_cert_xml_data();
959 -- Bug# 5415136
960 l_xml_string := convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
961 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
962
963 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
964 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
965
966 -- Bug# 5409988
967 check_function(to_number(l_current_function));
968
969 l_xml_string := '<LIST_G_JOB_FUNCTION>';
970 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
971 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
972
973 -- Bug# 5410233.
974 g_line_number := 0;
975
976 g_for_all_emp := 'F';
977 create_xml(l_current_function);
978
979 l_xml_string := '</LIST_G_JOB_FUNCTION></G_REPORT>';
980
981 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
982 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
983 END LOOP; -- For Each Function
984 END IF;
985
986 END create_report;
987
988 PROCEDURE generate_header_xml_data IS
989
990 BEGIN
991
992 l_xml_string := '<?xml version="1.0"?> <PQHEEO4>';
993
994 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
995 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
996
997 END generate_header_xml_data;
998
999 PROCEDURE generate_footer_xml_data IS
1000
1001 l_bg_name VARCHAR2(300); --bug 13645315 -increased the size of l_bg_name from 100 to 300.
1002
1003 CURSOR get_bg_name IS
1004 SELECT name from hr_organization_units
1005 WHERE organization_id = g_business_group_id
1006 AND business_group_id = g_business_group_id;
1007
1008 BEGIN
1009
1010 l_bg_name := ' ';
1011
1012 OPEN get_bg_name;
1013 FETCH get_bg_name INTO l_bg_name;
1014 CLOSE get_bg_name;
1015
1016 l_xml_string := convert_into_xml('C_BUSINESS_GROUP_NAME',l_bg_name,'D')
1017 || convert_into_xml('C_REPORT_TYPE','2006 EEO-4 REPORT','D')
1018 || '<C_ORGANIZATION_HIERARCHY></C_ORGANIZATION_HIERARCHY>
1019 <C_EEO1_ORGANIZATION></C_EEO1_ORGANIZATION>
1020 <C_END_OF_TIME></C_END_OF_TIME>
1021 </PQHEEO4>';
1022 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
1023 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1024
1025 END generate_footer_xml_data;
1026
1027 PROCEDURE generate_juris_cert_xml_data IS
1028
1029 CURSOR get_jurisdiction_details IS
1030
1031 -- Bug# 5437076
1032
1033 SELECT NVL(hou.name,' ') jurisdiction_name,
1034 NVL(hl.address_line_1,' ')||' '||NVL(hl.address_line_2,' ')
1035 ||' '||NVL(hl.address_line_3,' ') address,
1036 NVL(hl.town_or_city,' ') town_or_city,
1037 NVL(hl.region_1,' ') county,
1041 WHERE hou.location_id = hl.location_id
1038 NVL(hl.region_2,' ')||' '||NVL(hl.postal_code,' ') state_zip
1039 FROM hr_all_organization_units hou,
1040 hr_locations hl
1042 AND hou.business_group_id = g_business_group_id
1043 AND hou.organization_id = g_business_group_id;
1044
1045 /* This query is replaced with the above query for the bug# 5437076
1046
1047 SELECT NVL(name,' ') jurisdiction_name,
1048 NVL(address_line_1,' ')||' '||NVL(address_line_2,' ')
1049 ||' '||NVL(address_line_3,' ') address,
1050 NVL(town_or_city,' ') town_or_city,
1051 NVL(region_1,' ') county,
1052 NVL(region_2,' ')||' '||NVL(postal_code,' ') state_zip
1053 FROM hr_all_organization_units
1054 WHERE business_group_id = g_business_group_id
1055 and organization_id = g_business_group_id;
1056 */
1057
1058 CURSOR get_cert_details IS
1059
1060 SELECT NVL(org_information1,' ') cert_officer_name,
1061 NVL(org_information2,' ') cert_officer_title,
1062 NVL(org_information3,' ') contact_name,
1063 NVL(org_information4,' ') contact_title,
1064 NVL(org_information5,' ')
1065 ||' '|| NVL(org_information6,' ') contact_address,
1066 NVL(org_information7,' ')
1070 NVL(org_information12,' ') control_number,
1067 ||' '|| NVL(org_information8,' ')
1068 ||' '|| NVL(org_information9,' ') contact_city_state_zip,
1069 NVL(org_information10,' ') contact_telephone,
1071 NVL(org_information15, ' ') email,
1072 NVL(org_information14, ' ') fax
1073 FROM hr_organization_information
1074 WHERE org_information_context = 'EEO_REPORT'
1075 AND organization_id = g_business_group_id;
1076 --AND org_information11 = 'EEO4';
1077
1078 BEGIN
1079
1080 l_xml_string := '<LIST_G_CERT_OFFICER_NAME>';
1081
1082 FOR rec IN get_cert_details LOOP
1083
1084 l_xml_string := l_xml_string || '<G_CERT_OFFICER_NAME>';
1085 l_xml_string := l_xml_string || convert_into_xml('CERT_OFFICIAL_NAME',rec.cert_officer_name,'D');
1086 l_xml_string := l_xml_string || convert_into_xml('CERT_OFFICIAL_TITLE',rec.cert_officer_title,'D');
1087 l_xml_string := l_xml_string || convert_into_xml('CONTACT_NAME',rec.contact_name,'D');
1088 l_xml_string := l_xml_string || convert_into_xml('CONTACT_TITLE',rec.contact_title,'D');
1089 l_xml_string := l_xml_string || convert_into_xml('CONTACT_ADDRESS',rec.contact_address,'D');
1090 l_xml_string := l_xml_string || convert_into_xml('CONTACT_CITY_STATE_ZIP',rec.contact_city_state_zip,'D');
1091 l_xml_string := l_xml_string || convert_into_xml('CONTACT_TELEPHONE',rec.contact_telephone,'D');
1092 -- Bug# 5415136
1093 g_control_number := rec.control_number;
1094 l_xml_string := l_xml_string || convert_into_xml('CONTACT_FAX',rec.fax,'D');
1095 l_xml_string := l_xml_string || convert_into_xml('CONTACT_EMAIL',rec.email,'D');
1096 l_xml_string := l_xml_string || convert_into_xml('CONTROL_NUMBER',rec.control_number,'D');
1097 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
1098 l_xml_string := l_xml_string || '</G_CERT_OFFICER_NAME>';
1099
1100 END LOOP;
1101
1102 l_xml_string := l_xml_string || '</LIST_G_CERT_OFFICER_NAME>';
1103
1104 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
1105 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1106
1107
1108 l_xml_string := '<LIST_G_JURISDICTION_DETAIL>';
1109
1110 FOR rec IN get_jurisdiction_details LOOP
1111 l_xml_string := l_xml_string || '<G_JURISDICTION_DETAIL>';
1112 l_xml_string := l_xml_string || convert_into_xml('BUSINESS_NAME',rec.jurisdiction_name,'D');
1113 l_xml_string := l_xml_string || convert_into_xml('ADDRESS',rec.address,'D');
1114 l_xml_string := l_xml_string || convert_into_xml('CITY_TOWN',rec.town_or_city,'D');
1115 l_xml_string := l_xml_string || convert_into_xml('COUNTY',rec.county,'D');
1116 l_xml_string := l_xml_string || convert_into_xml('STATE_ZIP',rec.state_zip,'D');
1117
1118 -- Bug# 5415136
1119 l_xml_string := l_xml_string || convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
1120 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
1121
1122 l_xml_string := l_xml_string || '</G_JURISDICTION_DETAIL>';
1123 END LOOP;
1124
1125 l_xml_string := l_xml_string || '</LIST_G_JURISDICTION_DETAIL>';
1126
1127 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
1128 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1129
1130 END generate_juris_cert_xml_data;
1131
1132
1133 PROCEDURE create_xml(p_current_function IN VARCHAR2) IS
1134
1135 BEGIN
1136 l_xml_string := '<G_JOB_FUNCTION><JOB_FUNCTION_CODE>';
1137 -- Bug# 5415136
1138 -- Replaced p_current_function with g_function_numbers.
1139 l_xml_string := l_xml_string || g_function_numbers;
1140 l_xml_string := l_xml_string || '</JOB_FUNCTION_CODE><LIST_G_EMPLOYMENT_CATEGORY>';
1141
1142 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
1143 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1144
1145 g_line_number := g_line_number + 1;
1146
1147 -- Genarate full time emp xml data
1148 generate_ft_xml_data(p_current_function);
1149
1150 g_line_number := g_line_number + 1;
1151
1152 -- Genarate other than full time emp xml data
1153 generate_oft_xml_data(p_current_function);
1154
1155 g_line_number := g_line_number + 1;
1156
1157 -- Genarate new hires emp xml data
1158 generate_nh_xml_data(p_current_function);
1159
1160 l_xml_string := ' </LIST_G_EMPLOYMENT_CATEGORY>'
1161 || convert_into_xml('CF_SET_FUNCTION_DESC',g_func_desc,'D')
1162 || '</G_JOB_FUNCTION>';
1163 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
1164 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1165 END create_xml;
1166
1167
1168 -- This method populates ft_emp_table
1169
1170 PROCEDURE populate_ft_emp_data(p_function_code IN VARCHAR2) IS
1171
1172 l_function_code VARCHAR2(32767);
1173 l_job_category_code VARCHAR2(30);
1174 l_job_category_name VARCHAR2(80);
1175 l_cons_total_category_emps NUMBER := 0;
1176 l_no_cons_wmale_emps NUMBER := 0;
1177 l_no_cons_bmale_emps NUMBER := 0;
1178 l_no_cons_hmale_emps NUMBER := 0;
1179 l_no_cons_amale_emps NUMBER := 0;
1180 l_no_cons_imale_emps NUMBER := 0;
1184 l_no_cons_afemale_emps NUMBER := 0;
1181 l_no_cons_wfemale_emps NUMBER := 0;
1182 l_no_cons_bfemale_emps NUMBER := 0;
1183 l_no_cons_hfemale_emps NUMBER := 0;
1185 l_no_cons_ifemale_emps NUMBER := 0;
1186
1187 /* Commented for bug#11736960 starts
1188 -- Bug# 5593259
1189 l_no_tmr_wmale_emps NUMBER := 0;
1190 l_no_tmr_bmale_emps NUMBER := 0;
1191 l_no_tmr_hmale_emps NUMBER := 0;
1192 l_no_tmr_amale_emps NUMBER := 0;
1193 l_no_tmr_imale_emps NUMBER := 0;
1194 l_no_tmr_wfemale_emps NUMBER := 0;
1195 l_no_tmr_bfemale_emps NUMBER := 0;
1196 l_no_tmr_hfemale_emps NUMBER := 0;
1197 l_no_tmr_afemale_emps NUMBER := 0;
1198 l_no_tmr_ifemale_emps NUMBER := 0;
1199 Commented for bug#11736960 ends */
1200
1201
1202 CURSOR get_eeo4_lookup_details IS
1203 SELECT lookup_code, meaning, description
1204 FROM hr_lookups
1205 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
1206 ORDER BY lookup_code;
1207
1208
1209 BEGIN
1210
1211 l_function_code := p_function_code;
1212
1213 FOR i IN get_eeo4_lookup_details LOOP -- for each job category
1214
1215 g_lookup_code := i.lookup_code;
1216 g_meaning := i.meaning;
1217
1218
1219 FOR j IN 1 .. 8 LOOP -- for each salary range
1220
1221 /* CASE j
1222 WHEN 1 THEN g_start_salary := 100;
1223 g_end_salary := 15900;
1224 g_salary_range := '$0.1-15.9';
1225 WHEN 2 THEN g_start_salary := 16000;
1226 g_end_salary := 19900;
1227 g_salary_range := '16.0-19.9';
1228 WHEN 3 THEN g_start_salary := 20000;
1229 g_end_salary := 24900;
1230 g_salary_range := '20.0-24.9';
1231 WHEN 4 THEN g_start_salary := 25000;
1232 g_end_salary := 32900;
1233 g_salary_range := '25.0-32.9';
1234 WHEN 5 THEN g_start_salary := 33000;
1235 g_end_salary := 42900;
1236 g_salary_range := '33.0-42.9';
1237 WHEN 6 THEN g_start_salary := 43000;
1238 g_end_salary := 54900;
1239 g_salary_range := '43.0-54.9';
1240 WHEN 7 THEN g_start_salary := 55000;
1241 g_end_salary := 64900;
1242 g_salary_range := '55.0-69.9';
1243 WHEN 8 THEN g_start_salary := 70000;
1244 g_end_salary := 99900;
1245 g_salary_range := '70.0 PLUS';
1246 END CASE; */
1247 -- Fix for Bug#8812609
1248 CASE j
1249 WHEN 1 THEN g_start_salary := 100;
1250 g_end_salary := 15999;
1251 g_salary_range := '$0.1-15.999';
1252 WHEN 2 THEN g_start_salary := 16000;
1253 g_end_salary := 19999;
1254 g_salary_range := '16.0-19.999';
1255 WHEN 3 THEN g_start_salary := 20000;
1256 g_end_salary := 24999;
1257 g_salary_range := '20.0-24.999';
1258 WHEN 4 THEN g_start_salary := 25000;
1259 g_end_salary := 32999;
1260 g_salary_range := '25.0-32.999';
1261 WHEN 5 THEN g_start_salary := 33000;
1262 g_end_salary := 42999;
1263 g_salary_range := '33.0-42.999';
1264 WHEN 6 THEN g_start_salary := 43000;
1265 g_end_salary := 54999;
1266 g_salary_range := '43.0-54.999';
1267 WHEN 7 THEN g_start_salary := 55000;
1268 g_end_salary := 69999;
1269 g_salary_range := '55.0-69.999';
1270 WHEN 8 THEN g_start_salary := 70000;
1271 g_end_salary := 99900;
1272 g_salary_range := '70.0 PLUS';
1273 END CASE;
1274
1275 source_cursor := dbms_sql.open_cursor;
1276
1277 -- Bug# 5414756
1278 IF J = 8 THEN
1279 g_dynamic_where := ' AND round(NVL(ppp.proposed_salary_n * ppb.pay_annualization_factor,0)) >= 70000 ';
1280 ELSE
1281 g_dynamic_where := ' AND round(NVL(ppp.proposed_salary_n * ppb.pay_annualization_factor,0)) BETWEEN ' || g_start_salary || ' AND ' || g_end_salary;
1282 END IF;
1283
1284 generate_sql(p_function_code, g_dynamic_where);
1285
1286
1287 dbms_sql.parse(source_cursor,g_ft_emp_sql,2);
1288 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
1289 --dbms_sql.BIND_VARIABLE(source_cursor,':2',g_start_salary);
1290 --dbms_sql.BIND_VARIABLE(source_cursor,':3',g_end_salary);
1291
1292 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
1293 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
1294 --Bug# 6200441
1295 --dbms_sql.define_column(source_cursor,3,l_cons_total_category_emps);
1296 dbms_sql.define_column(source_cursor,3,l_no_cons_wmale_emps);
1297 dbms_sql.define_column(source_cursor,4,l_no_cons_bmale_emps);
1298 dbms_sql.define_column(source_cursor,5,l_no_cons_hmale_emps);
1299 dbms_sql.define_column(source_cursor,6,l_no_cons_amale_emps);
1300 dbms_sql.define_column(source_cursor,7,l_no_cons_imale_emps);
1301 dbms_sql.define_column(source_cursor,8,l_no_cons_wfemale_emps);
1302 dbms_sql.define_column(source_cursor,9,l_no_cons_bfemale_emps);
1303 dbms_sql.define_column(source_cursor,10,l_no_cons_hfemale_emps);
1304 dbms_sql.define_column(source_cursor,11,l_no_cons_afemale_emps);
1305 dbms_sql.define_column(source_cursor,12,l_no_cons_ifemale_emps);
1306
1307 rows_processed := dbms_sql.EXECUTE(source_cursor);
1308
1309 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
1310
1311 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
1312 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
1313 --Bug# 6200441
1314 --dbms_sql.column_value(source_cursor,3,l_cons_total_category_emps);
1315 dbms_sql.column_value(source_cursor,3,l_no_cons_wmale_emps);
1316 dbms_sql.column_value(source_cursor,4,l_no_cons_bmale_emps);
1317 dbms_sql.column_value(source_cursor,5,l_no_cons_hmale_emps);
1318 dbms_sql.column_value(source_cursor,6,l_no_cons_amale_emps);
1319 dbms_sql.column_value(source_cursor,7,l_no_cons_imale_emps);
1323 dbms_sql.column_value(source_cursor,11,l_no_cons_afemale_emps);
1320 dbms_sql.column_value(source_cursor,8,l_no_cons_wfemale_emps);
1321 dbms_sql.column_value(source_cursor,9,l_no_cons_bfemale_emps);
1322 dbms_sql.column_value(source_cursor,10,l_no_cons_hfemale_emps);
1324 dbms_sql.column_value(source_cursor,12,l_no_cons_ifemale_emps);
1325
1326 l_counter1 := l_counter1 + 1;
1327
1328 IF g_for_all_emp = 'T' THEN
1329 ft_emp_table(l_counter1).job_function := 'AF';
1330 ELSIF g_for_all_emp = 'F' THEN
1331 ft_emp_table(l_counter1).job_function := ltrim(rtrim(l_function_code));
1332 END IF;
1333
1334 ft_emp_table(l_counter1).lookup_code := ltrim(rtrim(l_job_category_code));
1335 ft_emp_table(l_counter1).salary_range := ltrim(rtrim(g_salary_range));
1336 -- Bug# 6200441
1337 ft_emp_table(l_counter1).cons_total_category_emps := ltrim(rtrim(l_no_cons_wmale_emps)) +
1338 ltrim(rtrim(l_no_cons_bmale_emps)) +
1339 ltrim(rtrim(l_no_cons_hmale_emps)) +
1340 ltrim(rtrim(l_no_cons_amale_emps)) +
1341 ltrim(rtrim(l_no_cons_imale_emps)) +
1342 ltrim(rtrim(l_no_cons_wfemale_emps)) +
1343 ltrim(rtrim(l_no_cons_bfemale_emps)) +
1344 ltrim(rtrim(l_no_cons_hfemale_emps)) +
1345 ltrim(rtrim(l_no_cons_afemale_emps)) +
1346 ltrim(rtrim(l_no_cons_ifemale_emps));
1347 ft_emp_table(l_counter1).no_cons_wmale_emps := ltrim(rtrim(l_no_cons_wmale_emps));
1348 ft_emp_table(l_counter1).no_cons_bmale_emps := ltrim(rtrim(l_no_cons_bmale_emps));
1349 ft_emp_table(l_counter1).no_cons_hmale_emps := ltrim(rtrim(l_no_cons_hmale_emps));
1350 ft_emp_table(l_counter1).no_cons_amale_emps := ltrim(rtrim(l_no_cons_amale_emps));
1351 ft_emp_table(l_counter1).no_cons_imale_emps := ltrim(rtrim(l_no_cons_imale_emps));
1352 ft_emp_table(l_counter1).no_cons_wfemale_emps := ltrim(rtrim(l_no_cons_wfemale_emps));
1353 ft_emp_table(l_counter1).no_cons_bfemale_emps := ltrim(rtrim(l_no_cons_bfemale_emps));
1354 ft_emp_table(l_counter1).no_cons_hfemale_emps := ltrim(rtrim(l_no_cons_hfemale_emps));
1355 ft_emp_table(l_counter1).no_cons_afemale_emps := ltrim(rtrim(l_no_cons_afemale_emps));
1359
1356 ft_emp_table(l_counter1).no_cons_ifemale_emps := ltrim(rtrim(l_no_cons_ifemale_emps));
1357
1358 ELSE
1360 l_counter1 := l_counter1 + 1;
1361
1362 IF g_for_all_emp = 'T' THEN
1363 ft_emp_table(l_counter1).job_function := 'AF';
1364 ELSIF g_for_all_emp = 'F' THEN
1365 ft_emp_table(l_counter1).job_function := ltrim(rtrim(l_function_code));
1366 END IF;
1367 ft_emp_table(l_counter1).lookup_code := ltrim(rtrim(g_lookup_code));
1368 ft_emp_table(l_counter1).salary_range := ltrim(rtrim(g_salary_range));
1369 ft_emp_table(l_counter1).cons_total_category_emps := 0;
1370 ft_emp_table(l_counter1).no_cons_wmale_emps := 0;
1371 ft_emp_table(l_counter1).no_cons_bmale_emps := 0;
1372 ft_emp_table(l_counter1).no_cons_hmale_emps := 0;
1373 ft_emp_table(l_counter1).no_cons_amale_emps := 0;
1374 ft_emp_table(l_counter1).no_cons_imale_emps := 0;
1375 ft_emp_table(l_counter1).no_cons_wfemale_emps := 0;
1376 ft_emp_table(l_counter1).no_cons_bfemale_emps := 0;
1377 ft_emp_table(l_counter1).no_cons_hfemale_emps := 0;
1378 ft_emp_table(l_counter1).no_cons_afemale_emps := 0;
1379 ft_emp_table(l_counter1).no_cons_ifemale_emps := 0;
1380 END IF;
1381 dbms_sql.close_cursor(source_cursor); -- Closing the cursor
1382
1383 -- Bug# 5593259
1384 /* For Two or more races:
1385 If the employee's ethnic code is 'Two or more races',
1386 select pei_information5 from per_people_extra_info table
1387 */
1388
1389 /* Commented for bug#11736960 starts
1390 source_cursor := dbms_sql.open_cursor;
1391 dbms_sql.parse(source_cursor,g_tmr_ft_emp_sql,2);
1392 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
1393
1394 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
1395 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
1396 dbms_sql.define_column(source_cursor,3,l_no_tmr_wmale_emps);
1397 dbms_sql.define_column(source_cursor,4,l_no_tmr_bmale_emps);
1398 dbms_sql.define_column(source_cursor,5,l_no_tmr_hmale_emps);
1399 dbms_sql.define_column(source_cursor,6,l_no_tmr_amale_emps);
1400 dbms_sql.define_column(source_cursor,7,l_no_tmr_imale_emps);
1401 dbms_sql.define_column(source_cursor,8,l_no_tmr_wfemale_emps);
1402 dbms_sql.define_column(source_cursor,9,l_no_tmr_bfemale_emps);
1403 dbms_sql.define_column(source_cursor,10,l_no_tmr_hfemale_emps);
1404 dbms_sql.define_column(source_cursor,11,l_no_tmr_afemale_emps);
1405 dbms_sql.define_column(source_cursor,12,l_no_tmr_ifemale_emps);
1406
1407 rows_processed := dbms_sql.EXECUTE(source_cursor);
1408
1409 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
1410
1411 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
1412 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
1413 dbms_sql.column_value(source_cursor,3,l_no_tmr_wmale_emps);
1414 dbms_sql.column_value(source_cursor,4,l_no_tmr_bmale_emps);
1415 dbms_sql.column_value(source_cursor,5,l_no_tmr_hmale_emps);
1416 dbms_sql.column_value(source_cursor,6,l_no_tmr_amale_emps);
1417 dbms_sql.column_value(source_cursor,7,l_no_tmr_imale_emps);
1418 dbms_sql.column_value(source_cursor,8,l_no_tmr_wfemale_emps);
1419 dbms_sql.column_value(source_cursor,9,l_no_tmr_bfemale_emps);
1420 dbms_sql.column_value(source_cursor,10,l_no_tmr_hfemale_emps);
1421 dbms_sql.column_value(source_cursor,11,l_no_tmr_afemale_emps);
1422 dbms_sql.column_value(source_cursor,12,l_no_tmr_ifemale_emps);
1423
1427 ltrim(rtrim(l_no_tmr_bmale_emps));
1424 ft_emp_table(l_counter1).no_cons_wmale_emps := ft_emp_table(l_counter1).no_cons_wmale_emps +
1425 ltrim(rtrim(l_no_tmr_wmale_emps));
1426 ft_emp_table(l_counter1).no_cons_bmale_emps := ft_emp_table(l_counter1).no_cons_bmale_emps +
1428 ft_emp_table(l_counter1).no_cons_hmale_emps := ft_emp_table(l_counter1).no_cons_hmale_emps +
1429 ltrim(rtrim(l_no_tmr_hmale_emps));
1430 ft_emp_table(l_counter1).no_cons_amale_emps := ft_emp_table(l_counter1).no_cons_amale_emps +
1434 ft_emp_table(l_counter1).no_cons_wfemale_emps := ft_emp_table(l_counter1).no_cons_wfemale_emps +
1431 ltrim(rtrim(l_no_tmr_amale_emps));
1432 ft_emp_table(l_counter1).no_cons_imale_emps := ft_emp_table(l_counter1).no_cons_imale_emps +
1433 ltrim(rtrim(l_no_tmr_imale_emps));
1435 ltrim(rtrim(l_no_tmr_wfemale_emps));
1436 ft_emp_table(l_counter1).no_cons_bfemale_emps := ft_emp_table(l_counter1).no_cons_bfemale_emps +
1437 ltrim(rtrim(l_no_tmr_bfemale_emps));
1438 ft_emp_table(l_counter1).no_cons_hfemale_emps := ft_emp_table(l_counter1).no_cons_hfemale_emps +
1439 ltrim(rtrim(l_no_tmr_hfemale_emps));
1440 ft_emp_table(l_counter1).no_cons_afemale_emps := ft_emp_table(l_counter1).no_cons_afemale_emps +
1441 ltrim(rtrim(l_no_tmr_afemale_emps));
1442 ft_emp_table(l_counter1).no_cons_ifemale_emps := ft_emp_table(l_counter1).no_cons_ifemale_emps +
1443 ltrim(rtrim(l_no_tmr_ifemale_emps));
1444 -- Bug# 6200441
1445 ft_emp_table(l_counter1).cons_total_category_emps := ft_emp_table(l_counter1).cons_total_category_emps +
1446 ltrim(rtrim(l_no_tmr_wmale_emps)) +
1447 ltrim(rtrim(l_no_tmr_bmale_emps)) +
1448 ltrim(rtrim(l_no_tmr_hmale_emps)) +
1449 ltrim(rtrim(l_no_tmr_amale_emps)) +
1450 ltrim(rtrim(l_no_tmr_imale_emps)) +
1451 ltrim(rtrim(l_no_tmr_wfemale_emps)) +
1452 ltrim(rtrim(l_no_tmr_bfemale_emps)) +
1453 ltrim(rtrim(l_no_tmr_hfemale_emps)) +
1454 ltrim(rtrim(l_no_tmr_afemale_emps)) +
1455 ltrim(rtrim(l_no_tmr_ifemale_emps));
1456
1457 END IF;
1458
1459 dbms_sql.close_cursor(source_cursor); -- Closing the cursor
1460 Commented for bug#11736960 ends */
1461
1462 END LOOP; -- for each salary range
1463 END LOOP; -- for each job category
1464 END populate_ft_emp_data; -- End of the procedure populate_ft_emp_data
1465
1466 PROCEDURE populate_oft_emp_data(p_function_code IN VARCHAR2) IS
1467
1468 l_function_code VARCHAR2(32767);
1469 l_job_category_code VARCHAR2(30);
1470 l_job_category_name VARCHAR2(80);
1471 l_cons_total_category_emps NUMBER := 0;
1472 l_no_cons_wmale_emps NUMBER := 0;
1473 l_no_cons_bmale_emps NUMBER := 0;
1474 l_no_cons_hmale_emps NUMBER := 0;
1475 l_no_cons_amale_emps NUMBER := 0;
1476 l_no_cons_imale_emps NUMBER := 0;
1477 l_no_cons_wfemale_emps NUMBER := 0;
1478 l_no_cons_bfemale_emps NUMBER := 0;
1479 l_no_cons_hfemale_emps NUMBER := 0;
1480 l_no_cons_afemale_emps NUMBER := 0;
1481 l_no_cons_ifemale_emps NUMBER := 0;
1482
1483 /* Commented for bug#11736960 starts
1484 -- Bug# 5593259
1485 l_no_tmr_wmale_emps NUMBER := 0;
1486 l_no_tmr_bmale_emps NUMBER := 0;
1487 l_no_tmr_hmale_emps NUMBER := 0;
1488 l_no_tmr_amale_emps NUMBER := 0;
1489 l_no_tmr_imale_emps NUMBER := 0;
1490 l_no_tmr_wfemale_emps NUMBER := 0;
1491 l_no_tmr_bfemale_emps NUMBER := 0;
1492 l_no_tmr_hfemale_emps NUMBER := 0;
1493 l_no_tmr_afemale_emps NUMBER := 0;
1494 l_no_tmr_ifemale_emps NUMBER := 0;
1495 Commented for bug#11736960 ends */
1496
1497
1498 CURSOR get_eeo4_lookup_details IS
1499 SELECT lookup_code, meaning, description
1500 FROM hr_lookups
1501 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
1502 ORDER BY lookup_code;
1503
1504 BEGIN
1505
1506 l_function_code := p_function_code;
1507
1508 FOR i IN get_eeo4_lookup_details LOOP -- for each job category
1509
1510 g_lookup_code := i.lookup_code;
1511 g_meaning := i.meaning;
1512
1513 source_cursor := dbms_sql.open_cursor;
1514 dbms_sql.parse(source_cursor,g_oft_sql,2);
1515 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
1516
1517 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
1518 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
1519 -- Bug# 6200441
1520 --dbms_sql.define_column(source_cursor,3,l_cons_total_category_emps);
1521 dbms_sql.define_column(source_cursor,3,l_no_cons_wmale_emps);
1522 dbms_sql.define_column(source_cursor,4,l_no_cons_bmale_emps);
1523 dbms_sql.define_column(source_cursor,5,l_no_cons_hmale_emps);
1524 dbms_sql.define_column(source_cursor,6,l_no_cons_amale_emps);
1525 dbms_sql.define_column(source_cursor,7,l_no_cons_imale_emps);
1526 dbms_sql.define_column(source_cursor,8,l_no_cons_wfemale_emps);
1527 dbms_sql.define_column(source_cursor,9,l_no_cons_bfemale_emps);
1528 dbms_sql.define_column(source_cursor,10,l_no_cons_hfemale_emps);
1529 dbms_sql.define_column(source_cursor,11,l_no_cons_afemale_emps);
1530 dbms_sql.define_column(source_cursor,12,l_no_cons_ifemale_emps);
1531
1532 rows_processed := dbms_sql.EXECUTE(source_cursor);
1533
1534 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
1535
1536 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
1537 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
1538 -- Bug# 6200441
1544 dbms_sql.column_value(source_cursor,7,l_no_cons_imale_emps);
1539 --dbms_sql.column_value(source_cursor,3,l_cons_total_category_emps);
1540 dbms_sql.column_value(source_cursor,3,l_no_cons_wmale_emps);
1541 dbms_sql.column_value(source_cursor,4,l_no_cons_bmale_emps);
1542 dbms_sql.column_value(source_cursor,5,l_no_cons_hmale_emps);
1543 dbms_sql.column_value(source_cursor,6,l_no_cons_amale_emps);
1545 dbms_sql.column_value(source_cursor,8,l_no_cons_wfemale_emps);
1546 dbms_sql.column_value(source_cursor,9,l_no_cons_bfemale_emps);
1547 dbms_sql.column_value(source_cursor,10,l_no_cons_hfemale_emps);
1548 dbms_sql.column_value(source_cursor,11,l_no_cons_afemale_emps);
1549 dbms_sql.column_value(source_cursor,12,l_no_cons_ifemale_emps);
1550
1551 l_counter2 := l_counter2 + 1;
1552
1553 IF g_for_all_emp = 'T' THEN
1554 other_ft_emp_table(l_counter2).job_function := 'AF';
1555 ELSIF g_for_all_emp = 'F' THEN
1556 other_ft_emp_table(l_counter2).job_function := ltrim(rtrim(l_function_code));
1557 END IF;
1558
1559 other_ft_emp_table(l_counter2).lookup_code := ltrim(rtrim(g_lookup_code));
1560 -- Bug# 6200441
1561 other_ft_emp_table(l_counter2).cons_total_category_emps := ltrim(rtrim(l_no_cons_wmale_emps)) +
1562 ltrim(rtrim(l_no_cons_bmale_emps)) +
1563 ltrim(rtrim(l_no_cons_hmale_emps)) +
1564 ltrim(rtrim(l_no_cons_amale_emps)) +
1565 ltrim(rtrim(l_no_cons_imale_emps)) +
1566 ltrim(rtrim(l_no_cons_wfemale_emps)) +
1567 ltrim(rtrim(l_no_cons_bfemale_emps)) +
1568 ltrim(rtrim(l_no_cons_hfemale_emps)) +
1569 ltrim(rtrim(l_no_cons_afemale_emps)) +
1570 ltrim(rtrim(l_no_cons_ifemale_emps));
1571 other_ft_emp_table(l_counter2).no_cons_wmale_emps := ltrim(rtrim(l_no_cons_wmale_emps));
1572 other_ft_emp_table(l_counter2).no_cons_bmale_emps := ltrim(rtrim(l_no_cons_bmale_emps));
1573 other_ft_emp_table(l_counter2).no_cons_hmale_emps := ltrim(rtrim(l_no_cons_hmale_emps));
1574 other_ft_emp_table(l_counter2).no_cons_amale_emps := ltrim(rtrim(l_no_cons_amale_emps));
1575 other_ft_emp_table(l_counter2).no_cons_imale_emps := ltrim(rtrim(l_no_cons_imale_emps));
1576 other_ft_emp_table(l_counter2).no_cons_wfemale_emps := ltrim(rtrim(l_no_cons_wfemale_emps));
1577 other_ft_emp_table(l_counter2).no_cons_bfemale_emps := ltrim(rtrim(l_no_cons_bfemale_emps));
1578 other_ft_emp_table(l_counter2).no_cons_hfemale_emps := ltrim(rtrim(l_no_cons_hfemale_emps));
1579 other_ft_emp_table(l_counter2).no_cons_afemale_emps := ltrim(rtrim(l_no_cons_afemale_emps));
1580 other_ft_emp_table(l_counter2).no_cons_ifemale_emps := ltrim(rtrim(l_no_cons_ifemale_emps));
1581 ELSE
1582
1583 l_counter2 := l_counter2 + 1;
1584
1585 IF g_for_all_emp = 'T' THEN
1586 other_ft_emp_table(l_counter2).job_function := 'AF';
1587 ELSIF g_for_all_emp = 'F' THEN
1588 other_ft_emp_table(l_counter2).job_function := ltrim(rtrim(l_function_code));
1589 END IF;
1590
1591 other_ft_emp_table(l_counter2).lookup_code := ltrim(rtrim(g_lookup_code));
1592 other_ft_emp_table(l_counter2).cons_total_category_emps := 0;
1593 other_ft_emp_table(l_counter2).no_cons_wmale_emps := 0;
1594 other_ft_emp_table(l_counter2).no_cons_bmale_emps := 0;
1595 other_ft_emp_table(l_counter2).no_cons_hmale_emps := 0;
1596 other_ft_emp_table(l_counter2).no_cons_amale_emps := 0;
1597 other_ft_emp_table(l_counter2).no_cons_imale_emps := 0;
1598 other_ft_emp_table(l_counter2).no_cons_wfemale_emps := 0;
1599 other_ft_emp_table(l_counter2).no_cons_bfemale_emps := 0;
1600 other_ft_emp_table(l_counter2).no_cons_hfemale_emps := 0;
1601 other_ft_emp_table(l_counter2).no_cons_afemale_emps := 0;
1602 other_ft_emp_table(l_counter2).no_cons_ifemale_emps := 0;
1603 END IF;
1604 dbms_sql.close_cursor(source_cursor); -- Close the cursor
1605
1606 -- Bug# 5593259
1607 /* For Two or more races:
1608 If the employee's ethnic code is 'Two or more races',
1609 select pei_information5 from per_people_extra_info table
1610 */
1611
1612 /* Commented for bug#11736960 starts
1613 source_cursor := dbms_sql.open_cursor;
1614 dbms_sql.parse(source_cursor,g_tmr_oft_sql,2);
1615 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
1616
1617 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
1618 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
1619 dbms_sql.define_column(source_cursor,3,l_no_tmr_wmale_emps);
1623 dbms_sql.define_column(source_cursor,7,l_no_tmr_imale_emps);
1620 dbms_sql.define_column(source_cursor,4,l_no_tmr_bmale_emps);
1621 dbms_sql.define_column(source_cursor,5,l_no_tmr_hmale_emps);
1622 dbms_sql.define_column(source_cursor,6,l_no_tmr_amale_emps);
1624 dbms_sql.define_column(source_cursor,8,l_no_tmr_wfemale_emps);
1625 dbms_sql.define_column(source_cursor,9,l_no_tmr_bfemale_emps);
1626 dbms_sql.define_column(source_cursor,10,l_no_tmr_hfemale_emps);
1627 dbms_sql.define_column(source_cursor,11,l_no_tmr_afemale_emps);
1628 dbms_sql.define_column(source_cursor,12,l_no_tmr_ifemale_emps);
1629
1630 rows_processed := dbms_sql.EXECUTE(source_cursor);
1631
1632 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
1633
1634 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
1635 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
1636 dbms_sql.column_value(source_cursor,3,l_no_tmr_wmale_emps);
1637 dbms_sql.column_value(source_cursor,4,l_no_tmr_bmale_emps);
1638 dbms_sql.column_value(source_cursor,5,l_no_tmr_hmale_emps);
1639 dbms_sql.column_value(source_cursor,6,l_no_tmr_amale_emps);
1640 dbms_sql.column_value(source_cursor,7,l_no_tmr_imale_emps);
1641 dbms_sql.column_value(source_cursor,8,l_no_tmr_wfemale_emps);
1642 dbms_sql.column_value(source_cursor,9,l_no_tmr_bfemale_emps);
1643 dbms_sql.column_value(source_cursor,10,l_no_tmr_hfemale_emps);
1644 dbms_sql.column_value(source_cursor,11,l_no_tmr_afemale_emps);
1645 dbms_sql.column_value(source_cursor,12,l_no_tmr_ifemale_emps);
1646
1647 other_ft_emp_table(l_counter2).no_cons_wmale_emps := other_ft_emp_table(l_counter2).no_cons_wmale_emps +
1648 ltrim(rtrim(l_no_tmr_wmale_emps));
1652 ltrim(rtrim(l_no_tmr_hmale_emps));
1649 other_ft_emp_table(l_counter2).no_cons_bmale_emps := other_ft_emp_table(l_counter2).no_cons_bmale_emps +
1650 ltrim(rtrim(l_no_tmr_bmale_emps));
1651 other_ft_emp_table(l_counter2).no_cons_hmale_emps := other_ft_emp_table(l_counter2).no_cons_hmale_emps +
1653 other_ft_emp_table(l_counter2).no_cons_amale_emps := other_ft_emp_table(l_counter2).no_cons_amale_emps +
1654 ltrim(rtrim(l_no_tmr_amale_emps));
1655 other_ft_emp_table(l_counter2).no_cons_imale_emps := other_ft_emp_table(l_counter2).no_cons_imale_emps +
1656 ltrim(rtrim(l_no_tmr_imale_emps));
1657 other_ft_emp_table(l_counter2).no_cons_wfemale_emps := other_ft_emp_table(l_counter2).no_cons_wfemale_emps +
1658 ltrim(rtrim(l_no_tmr_wfemale_emps));
1659 other_ft_emp_table(l_counter2).no_cons_bfemale_emps := other_ft_emp_table(l_counter2).no_cons_bfemale_emps +
1660 ltrim(rtrim(l_no_tmr_bfemale_emps));
1661 other_ft_emp_table(l_counter2).no_cons_hfemale_emps := other_ft_emp_table(l_counter2).no_cons_hfemale_emps +
1662 ltrim(rtrim(l_no_tmr_hfemale_emps));
1663 other_ft_emp_table(l_counter2).no_cons_afemale_emps := other_ft_emp_table(l_counter2).no_cons_afemale_emps +
1664 ltrim(rtrim(l_no_tmr_afemale_emps));
1665 other_ft_emp_table(l_counter2).no_cons_ifemale_emps := other_ft_emp_table(l_counter2).no_cons_ifemale_emps +
1666 ltrim(rtrim(l_no_tmr_ifemale_emps));
1667 -- Bug# 6200441
1668 other_ft_emp_table(l_counter2).cons_total_category_emps := other_ft_emp_table(l_counter2).cons_total_category_emps +
1669 ltrim(rtrim(l_no_tmr_wmale_emps)) +
1670 ltrim(rtrim(l_no_tmr_bmale_emps)) +
1671 ltrim(rtrim(l_no_tmr_hmale_emps)) +
1672 ltrim(rtrim(l_no_tmr_amale_emps)) +
1673 ltrim(rtrim(l_no_tmr_imale_emps)) +
1674 ltrim(rtrim(l_no_tmr_wfemale_emps)) +
1675 ltrim(rtrim(l_no_tmr_bfemale_emps)) +
1676 ltrim(rtrim(l_no_tmr_hfemale_emps)) +
1677 ltrim(rtrim(l_no_tmr_afemale_emps)) +
1678 ltrim(rtrim(l_no_tmr_ifemale_emps));
1679
1680 END IF;
1681
1682 dbms_sql.close_cursor(source_cursor); -- Closing the cursor
1683 Commented for bug#11736960 ends */
1684
1685 END LOOP; -- for each job category
1686 END;
1687
1688 PROCEDURE populate_nh_emp_data(p_function_code IN VARCHAR2) IS
1689
1690 l_function_code VARCHAR2(32767);
1691 l_job_category_code VARCHAR2(30);
1692 l_job_category_name VARCHAR2(80);
1693 l_cons_total_category_emps NUMBER := 0;
1694 l_no_cons_wmale_emps NUMBER := 0;
1695 l_no_cons_bmale_emps NUMBER := 0;
1696 l_no_cons_hmale_emps NUMBER := 0;
1697 l_no_cons_amale_emps NUMBER := 0;
1698 l_no_cons_imale_emps NUMBER := 0;
1699 l_no_cons_wfemale_emps NUMBER := 0;
1700 l_no_cons_bfemale_emps NUMBER := 0;
1704
1701 l_no_cons_hfemale_emps NUMBER := 0;
1702 l_no_cons_afemale_emps NUMBER := 0;
1703 l_no_cons_ifemale_emps NUMBER := 0;
1705 /* Commented for bug#11736960 starts
1706 -- Bug# 5593259
1707 l_no_tmr_wmale_emps NUMBER := 0;
1708 l_no_tmr_bmale_emps NUMBER := 0;
1709 l_no_tmr_hmale_emps NUMBER := 0;
1710 l_no_tmr_amale_emps NUMBER := 0;
1711 l_no_tmr_imale_emps NUMBER := 0;
1712 l_no_tmr_wfemale_emps NUMBER := 0;
1713 l_no_tmr_bfemale_emps NUMBER := 0;
1714 l_no_tmr_hfemale_emps NUMBER := 0;
1715 l_no_tmr_afemale_emps NUMBER := 0;
1716 l_no_tmr_ifemale_emps NUMBER := 0;
1717 Commented for bug#11736960 ends */
1718
1719 CURSOR get_eeo4_lookup_details IS
1720 SELECT lookup_code, meaning, description
1721 FROM hr_lookups
1722 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
1723 ORDER BY lookup_code;
1724
1725 BEGIN
1726
1727 l_function_code := p_function_code;
1728 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_function in populate_nh_emp_data: ' || l_function_code);
1729
1730 FOR i IN get_eeo4_lookup_details LOOP -- for each job category
1731
1732 g_lookup_code := i.lookup_code;
1733 g_meaning := i.meaning;
1734
1735 source_cursor := dbms_sql.open_cursor;
1736
1737 dbms_sql.parse(source_cursor,g_nh_sql,2);
1738 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
1739
1740 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
1741 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
1742 -- Bug# 6200441
1743 --dbms_sql.define_column(source_cursor,3,l_cons_total_category_emps);
1744 dbms_sql.define_column(source_cursor,3,l_no_cons_wmale_emps);
1745 dbms_sql.define_column(source_cursor,4,l_no_cons_bmale_emps);
1746 dbms_sql.define_column(source_cursor,5,l_no_cons_hmale_emps);
1747 dbms_sql.define_column(source_cursor,6,l_no_cons_amale_emps);
1748 dbms_sql.define_column(source_cursor,7,l_no_cons_imale_emps);
1749 dbms_sql.define_column(source_cursor,8,l_no_cons_wfemale_emps);
1750 dbms_sql.define_column(source_cursor,9,l_no_cons_bfemale_emps);
1751 dbms_sql.define_column(source_cursor,10,l_no_cons_hfemale_emps);
1752 dbms_sql.define_column(source_cursor,11,l_no_cons_afemale_emps);
1753 dbms_sql.define_column(source_cursor,12,l_no_cons_ifemale_emps);
1754
1755 rows_processed := dbms_sql.EXECUTE(source_cursor);
1756
1757 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
1758
1759 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
1760 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
1761 -- Bug# 6200441
1762 --dbms_sql.column_value(source_cursor,3,l_cons_total_category_emps);
1763 dbms_sql.column_value(source_cursor,3,l_no_cons_wmale_emps);
1764 dbms_sql.column_value(source_cursor,4,l_no_cons_bmale_emps);
1765 dbms_sql.column_value(source_cursor,5,l_no_cons_hmale_emps);
1766 dbms_sql.column_value(source_cursor,6,l_no_cons_amale_emps);
1767 dbms_sql.column_value(source_cursor,7,l_no_cons_imale_emps);
1768 dbms_sql.column_value(source_cursor,8,l_no_cons_wfemale_emps);
1769 dbms_sql.column_value(source_cursor,9,l_no_cons_bfemale_emps);
1770 dbms_sql.column_value(source_cursor,10,l_no_cons_hfemale_emps);
1771 dbms_sql.column_value(source_cursor,11,l_no_cons_afemale_emps);
1772 dbms_sql.column_value(source_cursor,12,l_no_cons_ifemale_emps);
1773
1774 l_counter3 := l_counter3 + 1;
1775
1776 IF g_for_all_emp = 'T' THEN
1777 new_hire_table(l_counter3).job_function := 'AF';
1778 ELSIF g_for_all_emp = 'F' THEN
1779 new_hire_table(l_counter3).job_function := ltrim(rtrim(l_function_code));
1780 END IF;
1781 new_hire_table(l_counter3).lookup_code := ltrim(rtrim(g_lookup_code));
1782 --new_hire_table(l_counter3).meaning := ltrim(rtrim(l_job_category_name));
1783 -- Bug# 6200441
1784 new_hire_table(l_counter3).cons_total_category_emps := ltrim(rtrim(l_no_cons_wmale_emps)) +
1785 ltrim(rtrim(l_no_cons_bmale_emps)) +
1786 ltrim(rtrim(l_no_cons_hmale_emps)) +
1787 ltrim(rtrim(l_no_cons_amale_emps)) +
1788 ltrim(rtrim(l_no_cons_imale_emps)) +
1789 ltrim(rtrim(l_no_cons_wfemale_emps)) +
1790 ltrim(rtrim(l_no_cons_bfemale_emps)) +
1791 ltrim(rtrim(l_no_cons_hfemale_emps)) +
1792 ltrim(rtrim(l_no_cons_afemale_emps)) +
1793 ltrim(rtrim(l_no_cons_ifemale_emps));
1794 new_hire_table(l_counter3).no_cons_wmale_emps := ltrim(rtrim(l_no_cons_wmale_emps));
1795 new_hire_table(l_counter3).no_cons_bmale_emps := ltrim(rtrim(l_no_cons_bmale_emps));
1796 new_hire_table(l_counter3).no_cons_hmale_emps := ltrim(rtrim(l_no_cons_hmale_emps));
1797 new_hire_table(l_counter3).no_cons_amale_emps := ltrim(rtrim(l_no_cons_amale_emps));
1798 new_hire_table(l_counter3).no_cons_imale_emps := ltrim(rtrim(l_no_cons_imale_emps));
1799 new_hire_table(l_counter3).no_cons_wfemale_emps := ltrim(rtrim(l_no_cons_wfemale_emps));
1800 new_hire_table(l_counter3).no_cons_bfemale_emps := ltrim(rtrim(l_no_cons_bfemale_emps));
1801 new_hire_table(l_counter3).no_cons_hfemale_emps := ltrim(rtrim(l_no_cons_hfemale_emps));
1802 new_hire_table(l_counter3).no_cons_afemale_emps := ltrim(rtrim(l_no_cons_afemale_emps));
1803 new_hire_table(l_counter3).no_cons_ifemale_emps := ltrim(rtrim(l_no_cons_ifemale_emps));
1804
1805 ELSE
1806 l_counter3 := l_counter3 + 1;
1807
1808 IF g_for_all_emp = 'T' THEN
1812 END IF;
1809 new_hire_table(l_counter3).job_function := 'AF';
1810 ELSIF g_for_all_emp = 'F' THEN
1811 new_hire_table(l_counter3).job_function := ltrim(rtrim(l_function_code));
1813 new_hire_table(l_counter3).lookup_code := ltrim(rtrim(g_lookup_code));
1814 --new_hire_table(l_counter3).meaning := g_meaning;
1815 new_hire_table(l_counter3).cons_total_category_emps := 0;
1816 new_hire_table(l_counter3).no_cons_wmale_emps := 0;
1817 new_hire_table(l_counter3).no_cons_bmale_emps := 0;
1818 new_hire_table(l_counter3).no_cons_hmale_emps := 0;
1819 new_hire_table(l_counter3).no_cons_amale_emps := 0;
1820 new_hire_table(l_counter3).no_cons_imale_emps := 0;
1821 new_hire_table(l_counter3).no_cons_wfemale_emps := 0;
1822 new_hire_table(l_counter3).no_cons_bfemale_emps := 0;
1823 new_hire_table(l_counter3).no_cons_hfemale_emps := 0;
1824 new_hire_table(l_counter3).no_cons_afemale_emps := 0;
1825 new_hire_table(l_counter3).no_cons_ifemale_emps := 0;
1826 END IF;
1827 dbms_sql.close_cursor(source_cursor); -- Close the cursor
1828
1829 -- Bug# 5593259
1830 /* For Two or more races:
1831 If the employee's ethnic code is 'Two or more races',
1832 select pei_information5 from per_people_extra_info table
1833 */
1834
1835 /* Commented for bug#11736960 starts
1836 source_cursor := dbms_sql.open_cursor;
1837 dbms_sql.parse(source_cursor,g_tmr_nh_sql,2);
1838 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
1839
1840 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
1841 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
1842 dbms_sql.define_column(source_cursor,3,l_no_tmr_wmale_emps);
1843 dbms_sql.define_column(source_cursor,4,l_no_tmr_bmale_emps);
1844 dbms_sql.define_column(source_cursor,5,l_no_tmr_hmale_emps);
1845 dbms_sql.define_column(source_cursor,6,l_no_tmr_amale_emps);
1846 dbms_sql.define_column(source_cursor,7,l_no_tmr_imale_emps);
1847 dbms_sql.define_column(source_cursor,8,l_no_tmr_wfemale_emps);
1848 dbms_sql.define_column(source_cursor,9,l_no_tmr_bfemale_emps);
1849 dbms_sql.define_column(source_cursor,10,l_no_tmr_hfemale_emps);
1850 dbms_sql.define_column(source_cursor,11,l_no_tmr_afemale_emps);
1851 dbms_sql.define_column(source_cursor,12,l_no_tmr_ifemale_emps);
1852
1853 rows_processed := dbms_sql.EXECUTE(source_cursor);
1854
1855 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
1856
1857 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
1858 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
1859 dbms_sql.column_value(source_cursor,3,l_no_tmr_wmale_emps);
1860 dbms_sql.column_value(source_cursor,4,l_no_tmr_bmale_emps);
1861 dbms_sql.column_value(source_cursor,5,l_no_tmr_hmale_emps);
1862 dbms_sql.column_value(source_cursor,6,l_no_tmr_amale_emps);
1863 dbms_sql.column_value(source_cursor,7,l_no_tmr_imale_emps);
1864 dbms_sql.column_value(source_cursor,8,l_no_tmr_wfemale_emps);
1865 dbms_sql.column_value(source_cursor,9,l_no_tmr_bfemale_emps);
1866 dbms_sql.column_value(source_cursor,10,l_no_tmr_hfemale_emps);
1867 dbms_sql.column_value(source_cursor,11,l_no_tmr_afemale_emps);
1868 dbms_sql.column_value(source_cursor,12,l_no_tmr_ifemale_emps);
1869
1870 new_hire_table(l_counter3).no_cons_wmale_emps := new_hire_table(l_counter3).no_cons_wmale_emps +
1871 ltrim(rtrim(l_no_tmr_wmale_emps));
1872 new_hire_table(l_counter3).no_cons_bmale_emps := new_hire_table(l_counter3).no_cons_bmale_emps +
1873 ltrim(rtrim(l_no_tmr_bmale_emps));
1874 new_hire_table(l_counter3).no_cons_hmale_emps := new_hire_table(l_counter3).no_cons_hmale_emps +
1875 ltrim(rtrim(l_no_tmr_hmale_emps));
1876 new_hire_table(l_counter3).no_cons_amale_emps := new_hire_table(l_counter3).no_cons_amale_emps +
1877 ltrim(rtrim(l_no_tmr_amale_emps));
1878 new_hire_table(l_counter3).no_cons_imale_emps := new_hire_table(l_counter3).no_cons_imale_emps +
1879 ltrim(rtrim(l_no_tmr_imale_emps));
1880 new_hire_table(l_counter3).no_cons_wfemale_emps := new_hire_table(l_counter3).no_cons_wfemale_emps +
1881 ltrim(rtrim(l_no_tmr_wfemale_emps));
1882 new_hire_table(l_counter3).no_cons_bfemale_emps := new_hire_table(l_counter3).no_cons_bfemale_emps +
1883 ltrim(rtrim(l_no_tmr_bfemale_emps));
1884 new_hire_table(l_counter3).no_cons_hfemale_emps := new_hire_table(l_counter3).no_cons_hfemale_emps +
1885 ltrim(rtrim(l_no_tmr_hfemale_emps));
1886 new_hire_table(l_counter3).no_cons_afemale_emps := new_hire_table(l_counter3).no_cons_afemale_emps +
1887 ltrim(rtrim(l_no_tmr_afemale_emps));
1888 new_hire_table(l_counter3).no_cons_ifemale_emps := new_hire_table(l_counter3).no_cons_ifemale_emps +
1892 ltrim(rtrim(l_no_tmr_wmale_emps)) +
1889 ltrim(rtrim(l_no_tmr_ifemale_emps));
1890 -- Bug# 6200441
1891 new_hire_table(l_counter3).cons_total_category_emps := new_hire_table(l_counter3).cons_total_category_emps +
1893 ltrim(rtrim(l_no_tmr_bmale_emps)) +
1894 ltrim(rtrim(l_no_tmr_hmale_emps)) +
1895 ltrim(rtrim(l_no_tmr_amale_emps)) +
1896 ltrim(rtrim(l_no_tmr_imale_emps)) +
1897 ltrim(rtrim(l_no_tmr_wfemale_emps)) +
1898 ltrim(rtrim(l_no_tmr_bfemale_emps)) +
1899 ltrim(rtrim(l_no_tmr_hfemale_emps)) +
1900 ltrim(rtrim(l_no_tmr_afemale_emps)) +
1901 ltrim(rtrim(l_no_tmr_ifemale_emps));
1902
1903 END IF;
1904
1905 dbms_sql.close_cursor(source_cursor); -- Closing the cursor
1906 Commented for bug#11736960 ends */
1907
1908 END LOOP; -- End of get_eeo4_lookup_details
1909
1910 END populate_nh_emp_data; -- End of populate_oft_emp_data
1911
1912 PROCEDURE generate_ft_xml_data(p_function_code VARCHAR2) IS
1913
1914 l_function_code VARCHAR2(32767);
1915 l_job_category_code VARCHAR2(30);
1916 l_job_category_name VARCHAR2(80);
1917 l_cons_total_category_emps NUMBER := 0;
1918 l_no_cons_wmale_emps NUMBER := 0;
1919 l_no_cons_bmale_emps NUMBER := 0;
1920 l_no_cons_hmale_emps NUMBER := 0;
1921 l_no_cons_amale_emps NUMBER := 0;
1922 l_no_cons_imale_emps NUMBER := 0;
1923 l_no_cons_wfemale_emps NUMBER := 0;
1924 l_no_cons_bfemale_emps NUMBER := 0;
1925 l_no_cons_hfemale_emps NUMBER := 0;
1926 l_no_cons_afemale_emps NUMBER := 0;
1927 l_no_cons_ifemale_emps NUMBER := 0;
1928
1929 l_cs_no_wmale_emps NUMBER := 0;
1930 l_cs_no_bmale_emps NUMBER := 0;
1931 l_cs_no_hmale_emps NUMBER := 0;
1932 l_cs_no_amale_emps NUMBER := 0;
1933 l_cs_no_imale_emps NUMBER := 0;
1934 l_cs_no_wfemale_emps NUMBER := 0;
1935 l_cs_no_bfemale_emps NUMBER := 0;
1936 l_cs_no_hfemale_emps NUMBER := 0;
1937 l_cs_no_afemale_emps NUMBER := 0;
1938 l_cs_no_ifemale_emps NUMBER := 0;
1939 l_cs_total_category_emps NUMBER := 0;
1940
1941 l_lookup_code VARCHAR2(30);
1942 l_meaning VARCHAR2(80);
1943
1944
1945 CURSOR get_eeo4_lookup_details IS
1946 SELECT lookup_code, meaning, description
1947 FROM hr_lookups
1948 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
1949 ORDER BY lookup_code;
1950
1951 BEGIN
1952
1953 IF g_for_all_emp = 'T' THEN
1954 l_function_code := 'AF';
1955 ELSIF g_for_all_emp = 'F' THEN
1956 l_function_code := p_function_code;
1957 END IF;
1958 -- 1. FULL-TIME EMPLOYEES (Temporary employees are not included)
1959 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1960 l_xml_string := l_xml_string || convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','1. FULL-TIME EMPLOYEES (Temporary employees are not included)','D');
1961 l_xml_string := l_xml_string || '<LIST_G_JOB_CATEGORIES>';
1962
1963 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
1964 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1965
1966 FOR i IN get_eeo4_lookup_details LOOP -- for each job category
1967
1968 l_lookup_code := i.lookup_code;
1969 l_meaning := i.meaning;
1970
1971
1972
1973 FOR sal_range in 1.. 8 LOOP
1974 -- This fix is for the bug#8812609
1975 CASE sal_range
1976 WHEN 1 THEN g_salary_range := '$0.1-15.999';
1977 WHEN 2 THEN g_salary_range := '16.0-19.999';
1978 WHEN 3 THEN g_salary_range := '20.0-24.999';
1979 WHEN 4 THEN g_salary_range := '25.0-32.999';
1980 WHEN 5 THEN g_salary_range := '33.0-42.999';
1981 WHEN 6 THEN g_salary_range := '43.0-54.999';
1982 WHEN 7 THEN g_salary_range := '55.0-69.999';
1983 WHEN 8 THEN g_salary_range := '70.0 PLUS';
1984 END CASE;
1985
1986
1987 FOR counter in 1 .. ft_emp_table.count LOOP -- Fetch from ft_emp_table
1988
1989 IF (ltrim(trim(ft_emp_table(counter).job_function)) = ltrim(rtrim(l_function_code)))
1990 AND (ltrim(rtrim(ft_emp_table(counter).lookup_code)) = ltrim(rtrim(l_lookup_code)))
1991 AND (ltrim(rtrim(ft_emp_table(counter).salary_range)) = ltrim(rtrim(g_salary_range)))
1992
1993 THEN
1994
1995 IF sal_range = 1 THEN
1996
1997 l_xml_string := '<G_JOB_CATEGORIES>';
1998 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_CODE',ft_emp_table(counter).lookup_code,'D');
1999 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_NAME',get_lookup_meaning(1,ltrim(rtrim(to_number(ft_emp_table(counter).lookup_code)))),'D');
2000
2001 -- Bug# 5402332 (Issue:13 )
2002 IF ltrim(rtrim(l_lookup_code)) = '1' OR ltrim(rtrim(l_lookup_code)) = '7' THEN
2003 -- Bug# 5415136
2004 -- l_xml_string := l_xml_string || convert_into_xml('CF_SET_FUNCTION_DESC',g_func_desc,'D');
2005 l_xml_string := l_xml_string || convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
2006 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
2007 END IF;
2008
2009 l_xml_string := l_xml_string || '<LIST_G_JOB_INFORMATION>';
2010
2011 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2012 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2013
2014 END IF;
2015
2016
2017 l_xml_string := '<G_JOB_INFORMATION>';
2018
2019 l_cs_no_wmale_emps := l_cs_no_wmale_emps + ft_emp_table(counter).no_cons_wmale_emps ;
2023 l_cs_no_imale_emps := l_cs_no_imale_emps + ft_emp_table(counter).no_cons_imale_emps;
2020 l_cs_no_bmale_emps := l_cs_no_bmale_emps + ft_emp_table(counter).no_cons_bmale_emps;
2021 l_cs_no_hmale_emps := l_cs_no_hmale_emps + ft_emp_table(counter).no_cons_hmale_emps;
2022 l_cs_no_amale_emps := l_cs_no_amale_emps + ft_emp_table(counter).no_cons_amale_emps;
2024 l_cs_no_wfemale_emps := l_cs_no_wfemale_emps + ft_emp_table(counter).no_cons_wfemale_emps;
2025 l_cs_no_bfemale_emps := l_cs_no_bfemale_emps + ft_emp_table(counter).no_cons_bfemale_emps;
2026 l_cs_no_hfemale_emps := l_cs_no_hfemale_emps + ft_emp_table(counter).no_cons_hfemale_emps;
2027 l_cs_no_afemale_emps := l_cs_no_afemale_emps + ft_emp_table(counter).no_cons_afemale_emps;
2028 l_cs_no_ifemale_emps := l_cs_no_ifemale_emps + ft_emp_table(counter).no_cons_ifemale_emps;
2029 l_cs_total_category_emps := l_cs_total_category_emps + ft_emp_table(counter).cons_total_category_emps;
2030
2031 l_xml_string := l_xml_string || convert_into_xml('EMPLOYEE_SALARY_SALARY_RANGE_A',g_line_number||'. '||ft_emp_table(counter).salary_range,'D');
2032 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',ft_emp_table(counter).cons_total_category_emps,'D');
2033 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',ft_emp_table(counter).no_cons_wmale_emps,'D');
2034 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',ft_emp_table(counter).no_cons_bmale_emps,'D');
2035 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',ft_emp_table(counter).no_cons_hmale_emps,'D');
2036 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',ft_emp_table(counter).no_cons_amale_emps,'D');
2037 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',ft_emp_table(counter).no_cons_imale_emps,'D');
2038 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',ft_emp_table(counter).no_cons_wfemale_emps,'D');
2039 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',ft_emp_table(counter).no_cons_bfemale_emps,'D');
2040 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',ft_emp_table(counter).no_cons_hfemale_emps,'D');
2041 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',ft_emp_table(counter).no_cons_afemale_emps,'D');
2042 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',ft_emp_table(counter).no_cons_ifemale_emps,'D');
2043
2044 l_xml_string := l_xml_string || '</G_JOB_INFORMATION>';
2045
2046 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2047 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2048 -- Increment the line number
2049 g_line_number := g_line_number + 1 ;
2050 END IF; -- End of qualifying condition
2051 END LOOP; -- End of Fetch from ft_emp_table
2052 END LOOP; -- End of for each salary range
2053 l_xml_string := '</LIST_G_JOB_INFORMATION></G_JOB_CATEGORIES>';
2054 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2055 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2056 END LOOP; -- for each job category
2057
2058 l_xml_string := '</LIST_G_JOB_CATEGORIES>';
2059 l_xml_string := l_xml_string || convert_into_xml('CF_TOTAL_TITLE',g_line_number||'. TOTAL FULL-TIME (LINES 1-64)','D');
2060 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WMALE_EMPS', l_cs_no_wmale_emps,'D');
2061 l_xml_string := l_xml_string || convert_into_xml('CS_NO_BMALE_EMPS', l_cs_no_bmale_emps,'D');
2062 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HMALE_EMPS', l_cs_no_hmale_emps,'D');
2063 l_xml_string := l_xml_string || convert_into_xml('CS_NO_AMALE_EMPS', l_cs_no_amale_emps,'D');
2064 l_xml_string := l_xml_string || convert_into_xml('CS_NO_IMALE_EMPS', l_cs_no_imale_emps,'D');
2065 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WFEMALE_EMPS', l_cs_no_wfemale_emps,'D');
2066 l_xml_string := l_xml_string || convert_into_xml('CS_NO_BFEMALE_EMPS', l_cs_no_bfemale_emps,'D');
2067 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HFEMALE_EMPS', l_cs_no_hfemale_emps,'D');
2068 l_xml_string := l_xml_string || convert_into_xml('CS_NO_AFEMALE_EMPS', l_cs_no_afemale_emps,'D');
2069 l_xml_string := l_xml_string || convert_into_xml('CS_NO_IFEMALE_EMPS', l_cs_no_ifemale_emps,'D');
2070 l_xml_string := l_xml_string || convert_into_xml('CS_TOTAL_CATEGORY_EMPS', l_cs_total_category_emps,'D');
2071 l_xml_string := l_xml_string || '</G_EMPLOYMENT_CATEGORY>';
2072
2073 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2074 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2075 END;
2076
2077 PROCEDURE generate_oft_xml_data(p_function_code VARCHAR2) IS
2078
2079 l_function_code VARCHAR2(32767);
2080 l_job_category_code VARCHAR2(30);
2081 l_job_category_name VARCHAR2(80);
2082 l_cons_total_category_emps NUMBER := 0;
2083 l_no_cons_wmale_emps NUMBER := 0;
2084 l_no_cons_bmale_emps NUMBER := 0;
2085 l_no_cons_hmale_emps NUMBER := 0;
2086 l_no_cons_amale_emps NUMBER := 0;
2087 l_no_cons_imale_emps NUMBER := 0;
2088 l_no_cons_wfemale_emps NUMBER := 0;
2089 l_no_cons_bfemale_emps NUMBER := 0;
2090 l_no_cons_hfemale_emps NUMBER := 0;
2091 l_no_cons_afemale_emps NUMBER := 0;
2092 l_no_cons_ifemale_emps NUMBER := 0;
2093
2094 l_cs_no_wmale_emps NUMBER := 0;
2095 l_cs_no_bmale_emps NUMBER := 0;
2096 l_cs_no_hmale_emps NUMBER := 0;
2097 l_cs_no_amale_emps NUMBER := 0;
2098 l_cs_no_imale_emps NUMBER := 0;
2099 l_cs_no_wfemale_emps NUMBER := 0;
2100 l_cs_no_bfemale_emps NUMBER := 0;
2101 l_cs_no_hfemale_emps NUMBER := 0;
2102 l_cs_no_afemale_emps NUMBER := 0;
2103 l_cs_no_ifemale_emps NUMBER := 0;
2104 l_cs_total_category_emps NUMBER := 0;
2105
2106 BEGIN
2107
2108 IF g_for_all_emp = 'T' THEN
2109 l_function_code := 'AF';
2113 -- 2. OTHER THAN FULL-TIME EMPLOYEES (Including temporary employees)
2110 ELSIF g_for_all_emp = 'F' THEN
2111 l_function_code := p_function_code;
2112 END IF;
2114 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
2115 l_xml_string := l_xml_string || convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','2. OTHER THAN FULL-TIME EMPLOYEES (Including temporary employees)','D');
2116 l_xml_string := l_xml_string || '<LIST_G_JOB_CATEGORIES>';
2117 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2118 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2119
2120
2121 FOR counter in 1 .. other_ft_emp_table.count LOOP -- Fetch from other_ft_emp_table
2122
2123
2124 IF (ltrim(rtrim(other_ft_emp_table(counter).job_function)) = ltrim(rtrim(l_function_code)))
2125 THEN
2126
2127 l_cs_no_wmale_emps := l_cs_no_wmale_emps + other_ft_emp_table(counter).no_cons_wmale_emps ;
2128 l_cs_no_bmale_emps := l_cs_no_bmale_emps + other_ft_emp_table(counter).no_cons_bmale_emps;
2129 l_cs_no_hmale_emps := l_cs_no_hmale_emps + other_ft_emp_table(counter).no_cons_hmale_emps;
2130 l_cs_no_amale_emps := l_cs_no_amale_emps + other_ft_emp_table(counter).no_cons_amale_emps;
2131 l_cs_no_imale_emps := l_cs_no_imale_emps + other_ft_emp_table(counter).no_cons_imale_emps;
2132 l_cs_no_wfemale_emps := l_cs_no_wfemale_emps + other_ft_emp_table(counter).no_cons_wfemale_emps;
2133 l_cs_no_bfemale_emps := l_cs_no_bfemale_emps + other_ft_emp_table(counter).no_cons_bfemale_emps;
2134 l_cs_no_hfemale_emps := l_cs_no_hfemale_emps + other_ft_emp_table(counter).no_cons_hfemale_emps;
2135 l_cs_no_afemale_emps := l_cs_no_afemale_emps + other_ft_emp_table(counter).no_cons_afemale_emps;
2136 l_cs_no_ifemale_emps := l_cs_no_ifemale_emps + other_ft_emp_table(counter).no_cons_ifemale_emps;
2137 l_cs_total_category_emps := l_cs_total_category_emps + other_ft_emp_table(counter).cons_total_category_emps;
2138
2139 l_xml_string := '<G_JOB_CATEGORIES>';
2140 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_CODE',other_ft_emp_table(counter).lookup_code,'D');
2141 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_NAME',g_line_number||'. '||get_lookup_meaning(2,to_number(other_ft_emp_table(counter).lookup_code)),'D');
2142 l_xml_string := l_xml_string || '<LIST_G_JOB_INFORMATION> <G_JOB_INFORMATION>';
2143
2144 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',other_ft_emp_table(counter).cons_total_category_emps,'D');
2145 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',other_ft_emp_table(counter).no_cons_wmale_emps,'D');
2146 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',other_ft_emp_table(counter).no_cons_bmale_emps,'D');
2147 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',other_ft_emp_table(counter).no_cons_hmale_emps,'D');
2148 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',other_ft_emp_table(counter).no_cons_amale_emps,'D');
2149 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',other_ft_emp_table(counter).no_cons_imale_emps,'D');
2150 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',other_ft_emp_table(counter).no_cons_wfemale_emps,'D');
2151 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',other_ft_emp_table(counter).no_cons_bfemale_emps,'D');
2152 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',other_ft_emp_table(counter).no_cons_hfemale_emps,'D');
2153 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',other_ft_emp_table(counter).no_cons_afemale_emps,'D');
2154 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',other_ft_emp_table(counter).no_cons_ifemale_emps,'D');
2155 l_xml_string := l_xml_string || '</G_JOB_INFORMATION></LIST_G_JOB_INFORMATION></G_JOB_CATEGORIES>';
2156 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2157 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2158 g_line_number := g_line_number + 1;
2159 END IF;
2160 END LOOP; -- Fetch other than full emp table
2161
2162
2163 l_xml_string := '</LIST_G_JOB_CATEGORIES>';
2164 l_xml_string := l_xml_string || convert_into_xml('CF_TOTAL_TITLE',g_line_number||'. '||'TOTAL OTHER THAN FULL TIME(LINES 66 - 73)','D');
2165 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WMALE_EMPS', l_cs_no_wmale_emps,'D');
2166 l_xml_string := l_xml_string || convert_into_xml('CS_NO_BMALE_EMPS', l_cs_no_bmale_emps,'D');
2167 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HMALE_EMPS', l_cs_no_hmale_emps,'D');
2168 l_xml_string := l_xml_string || convert_into_xml('CS_NO_AMALE_EMPS', l_cs_no_amale_emps,'D');
2169 l_xml_string := l_xml_string || convert_into_xml('CS_NO_IMALE_EMPS', l_cs_no_imale_emps,'D');
2170 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WFEMALE_EMPS', l_cs_no_wfemale_emps,'D');
2171 l_xml_string := l_xml_string || convert_into_xml('CS_NO_BFEMALE_EMPS', l_cs_no_bfemale_emps,'D');
2172 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HFEMALE_EMPS', l_cs_no_hfemale_emps,'D');
2173 l_xml_string := l_xml_string || convert_into_xml('CS_NO_AFEMALE_EMPS', l_cs_no_afemale_emps,'D');
2174 l_xml_string := l_xml_string || convert_into_xml('CS_NO_IFEMALE_EMPS', l_cs_no_ifemale_emps,'D');
2175 l_xml_string := l_xml_string || convert_into_xml('CS_TOTAL_CATEGORY_EMPS', l_cs_total_category_emps,'D');
2176 l_xml_string := l_xml_string || '</G_EMPLOYMENT_CATEGORY>';
2177 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2178 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2179 END;
2180
2181 PROCEDURE generate_nh_xml_data(p_function_code IN VARCHAR2) IS
2182
2183 l_job_category_code VARCHAR2(30);
2184 l_job_category_name VARCHAR2(80);
2185 l_cons_total_category_emps NUMBER := 0;
2186 l_no_cons_wmale_emps NUMBER := 0;
2187 l_no_cons_bmale_emps NUMBER := 0;
2188 l_no_cons_hmale_emps NUMBER := 0;
2189 l_no_cons_amale_emps NUMBER := 0;
2190 l_no_cons_imale_emps NUMBER := 0;
2191 l_no_cons_wfemale_emps NUMBER := 0;
2195 l_no_cons_ifemale_emps NUMBER := 0;
2192 l_no_cons_bfemale_emps NUMBER := 0;
2193 l_no_cons_hfemale_emps NUMBER := 0;
2194 l_no_cons_afemale_emps NUMBER := 0;
2196
2197 l_cs_no_wmale_emps NUMBER := 0;
2198 l_cs_no_bmale_emps NUMBER := 0;
2199 l_cs_no_hmale_emps NUMBER := 0;
2200 l_cs_no_amale_emps NUMBER := 0;
2201 l_cs_no_imale_emps NUMBER := 0;
2202 l_cs_no_wfemale_emps NUMBER := 0;
2203 l_cs_no_bfemale_emps NUMBER := 0;
2204 l_cs_no_hfemale_emps NUMBER := 0;
2205 l_cs_no_afemale_emps NUMBER := 0;
2206 l_cs_no_ifemale_emps NUMBER := 0;
2207 l_cs_total_category_emps NUMBER := 0;
2208
2209 BEGIN
2210
2211 IF g_for_all_emp = 'T' THEN
2212 l_function_code := 'AF';
2213 ELSIF g_for_all_emp = 'F' THEN
2214 l_function_code := p_function_code;
2215 END IF;
2216
2217 -- 3. NEW HIRES DURING FISCAL YEAR - Permanent full time only JULY 1 - JUNE 30
2218
2219 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
2220 l_xml_string := l_xml_string || convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','3. NEW HIRES DURING FISCAL YEAR - Permanent full time only JULY 1 - JUNE 30','D');
2221 l_xml_string := l_xml_string || '<LIST_G_JOB_CATEGORIES>';
2222 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2223 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2224
2225 FOR counter in 1 .. new_hire_table.count LOOP -- Fetch from other_ft_emp_table
2226
2227
2228 IF (ltrim(rtrim(new_hire_table(counter).job_function)) = ltrim(rtrim(l_function_code)))THEN
2229
2230 l_cs_no_wmale_emps := l_cs_no_wmale_emps + new_hire_table(counter).no_cons_wmale_emps ;
2231 l_cs_no_bmale_emps := l_cs_no_bmale_emps + new_hire_table(counter).no_cons_bmale_emps;
2232 l_cs_no_hmale_emps := l_cs_no_hmale_emps + new_hire_table(counter).no_cons_hmale_emps;
2233 l_cs_no_amale_emps := l_cs_no_amale_emps + new_hire_table(counter).no_cons_amale_emps;
2234 l_cs_no_imale_emps := l_cs_no_imale_emps + new_hire_table(counter).no_cons_imale_emps;
2235 l_cs_no_wfemale_emps := l_cs_no_wfemale_emps + new_hire_table(counter).no_cons_wfemale_emps;
2236 l_cs_no_bfemale_emps := l_cs_no_bfemale_emps + new_hire_table(counter).no_cons_bfemale_emps;
2237 l_cs_no_hfemale_emps := l_cs_no_hfemale_emps + new_hire_table(counter).no_cons_hfemale_emps;
2238 l_cs_no_afemale_emps := l_cs_no_afemale_emps + new_hire_table(counter).no_cons_afemale_emps;
2239 l_cs_no_ifemale_emps := l_cs_no_ifemale_emps + new_hire_table(counter).no_cons_ifemale_emps;
2240 l_cs_total_category_emps := l_cs_total_category_emps + new_hire_table(counter).cons_total_category_emps;
2241
2242 l_xml_string := '<G_JOB_CATEGORIES>';
2243 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_CODE',new_hire_table(counter).lookup_code,'D');
2244 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_NAME',g_line_number||'. '||get_lookup_meaning(3,ltrim(rtrim(new_hire_table(counter).lookup_code))),'D');
2245 l_xml_string := l_xml_string || '<LIST_G_JOB_INFORMATION> <G_JOB_INFORMATION>';
2246
2247 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',new_hire_table(counter).cons_total_category_emps,'D');
2248 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',new_hire_table(counter).no_cons_wmale_emps,'D');
2249 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',new_hire_table(counter).no_cons_bmale_emps,'D');
2250 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',new_hire_table(counter).no_cons_hmale_emps,'D');
2251 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',new_hire_table(counter).no_cons_amale_emps,'D');
2252 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',new_hire_table(counter).no_cons_imale_emps,'D');
2253 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',new_hire_table(counter).no_cons_wfemale_emps,'D');
2254 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',new_hire_table(counter).no_cons_bfemale_emps,'D');
2255 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',new_hire_table(counter).no_cons_hfemale_emps,'D');
2256 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',new_hire_table(counter).no_cons_afemale_emps,'D');
2257 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',new_hire_table(counter).no_cons_ifemale_emps,'D');
2258 l_xml_string := l_xml_string || '</G_JOB_INFORMATION></LIST_G_JOB_INFORMATION></G_JOB_CATEGORIES>';
2259
2260 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2261 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2262
2263 g_line_number := g_line_number + 1;
2264 END IF;
2265 END LOOP; -- End of Fetch from other_ft_emp_table
2266
2267 l_xml_string := '</LIST_G_JOB_CATEGORIES>';
2268 l_xml_string := l_xml_string || convert_into_xml('CF_TOTAL_TITLE',g_line_number||'. '||'TOTAL NEW HIRES (LINES 75 - 82)','D');
2269 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WMALE_EMPS', l_cs_no_wmale_emps,'D');
2270 l_xml_string := l_xml_string || convert_into_xml('CS_NO_BMALE_EMPS', l_cs_no_bmale_emps,'D');
2271 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HMALE_EMPS', l_cs_no_hmale_emps,'D');
2272 l_xml_string := l_xml_string || convert_into_xml('CS_NO_AMALE_EMPS', l_cs_no_amale_emps,'D');
2273 l_xml_string := l_xml_string || convert_into_xml('CS_NO_IMALE_EMPS', l_cs_no_imale_emps,'D');
2274 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WFEMALE_EMPS', l_cs_no_wfemale_emps,'D');
2275 l_xml_string := l_xml_string || convert_into_xml('CS_NO_BFEMALE_EMPS', l_cs_no_bfemale_emps,'D');
2276 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HFEMALE_EMPS', l_cs_no_hfemale_emps,'D');
2277 l_xml_string := l_xml_string || convert_into_xml('CS_NO_AFEMALE_EMPS', l_cs_no_afemale_emps,'D');
2278 l_xml_string := l_xml_string || convert_into_xml('CS_NO_IFEMALE_EMPS', l_cs_no_ifemale_emps,'D');
2279 l_xml_string := l_xml_string || convert_into_xml('CS_TOTAL_CATEGORY_EMPS', l_cs_total_category_emps,'D');
2280 l_xml_string := l_xml_string || '</G_EMPLOYMENT_CATEGORY>';
2281
2282 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
2283 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2284
2285 END;
2286 END PER_US_EEO4_PKG;
2287