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