[Home] [Help]
PACKAGE BODY: APPS.PER_US_EEO4A_PKG
Source
1 PACKAGE BODY "PER_US_EEO4A_PKG" AS
2 /* $Header: peuseeo4a.pkb 120.3.12020000.1 2012/07/16 10:21:50 agarai 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_EEO4A_PKG
21
22 Description : This package is used by 'EEO4A Report (XML)' concurrent
23 program.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 09-DEC-2011 agarai 115.0 13112110 Created for the EEO4A
30 Report.
31 17-JAN-2012 agarai 115.1 13112110 Increased the size of
32 l_bg_name from 100 to
33 240 in the procedure
34 generate_footer_xml_data
35 08-FEB-2012 agarai 115.2 13112110 Corrected the counts of
36 the full-time employees
37 and new hires.
38 16-MAR-2012 agarai 115.3 13849850 Modified g_nh_effective_dates
39 to use exists condition to
40 correct new hires count.
41 Modified l_query_text to
42 use the customer defined
43 employment categories also
44 in full-time employees count.
45 07-MAY-2012 agarai 115.4 14034790 Modified generate_xml_data
46 procedure to include
47 employees having
48 US_EEO4A_ETHNIC_ORIGIN
49 information category.
50 ****************************************************************************/
51
52 g_fp_regulars VARCHAR2(2000);
53 g_fp_temps VARCHAR2(2000);
54
55 g_select_clause VARCHAR2(10000);
56 g_from_where_clause VARCHAR2(10000);
57 g_ft_effective_dates VARCHAR2(1000);
58 g_nh_effective_dates VARCHAR2(1000);
59 g_group_order_by VARCHAR2(1000) := ' GROUP BY hl.lookup_code,hl.meaning
60 ORDER BY hl.lookup_code,hl.meaning';
61 g_nh_sql VARCHAR2(32767);
62 g_ft_emp_sql VARCHAR2(32767);
63 g_oft_sql VARCHAR2(32767);
64
65 g_business_group_id VARCHAR2(32767);
66
67 g_control_number VARCHAR2(1000);
68 g_function_numbers VARCHAR2(1000);
69
70 g_salary_range VARCHAR2(30);
71 g_start_salary NUMBER := 0;
72 g_end_salary NUMBER := 0;
73 g_lookup_code VARCHAR2(30) := ' ';
74 g_meaning VARCHAR2(80) := ' ';
75
76 g_for_all_emp VARCHAR2(1) := 'F';
77
78 g_job_code VARCHAR2(4000) := ' ''XX'' ';
79 g_func_desc VARCHAR2(4000);
80
81 g_dynamic_where VARCHAR2(4000) := ' ''XX'' ';
82 g_line_number NUMBER := 0 ;
83
84 l_xml_string VARCHAR2(32767);
85 l_query_text VARCHAR2(32767);
86
87 l_counter1 NUMBER := 0;
88 l_counter2 NUMBER := 0;
89 l_counter3 NUMBER := 0;
90
91 l_function_code VARCHAR2(32767);
92
93 -- DBMS Cursor Variables
94 source_cursor INTEGER;
95 rows_processed INTEGER;
96
97 -- PL/SQL table variables
98 ft_emp_table full_time_emp_data;
99 other_ft_emp_table other_full_time_emp_data;
100 new_hire_table new_hire_emp_data;
101
102 -- Functions whose data to be displayed first
103 first_order_func_table function_data;
104 -- Functions whose data to be displayed later
105 second_order_func_table function_data;
106 -- All functions to populate data
107 func_table function_data;
108
109 /** Function to get the race of a person **/
110 FUNCTION derive_single_race(p_person_id NUMBER)
111 RETURN NUMBER AS
112 CURSOR csr_ethnic_origin(cp_person_id NUMBER)
113 IS
114 SELECT pei.pei_information1,
115 pei.pei_information2,
116 pei.pei_information3,
117 pei.pei_information4,
118 pei.pei_information5,
119 pei.pei_information6
120 FROM per_people_extra_info pei
121 WHERE pei.person_id = cp_person_id
122 AND pei.information_type = 'US_EEO4A_ETHNIC_ORIGIN';
123
124 l_ethnic_origin NUMBER;
125 l_race_count NUMBER;
126 l_extra_info csr_ethnic_origin%ROWTYPE;
127 BEGIN
128 l_ethnic_origin := -1;
129 l_race_count := 0;
130
131 OPEN csr_ethnic_origin(p_person_id);
132 FETCH csr_ethnic_origin into l_extra_info;
133
134 IF csr_ethnic_origin%FOUND THEN
135
136 IF l_extra_info.pei_information1 = 'Y' THEN -- White
137 l_ethnic_origin :=1;
138 l_race_count := l_race_count + 1;
139 END IF;
140
141 IF l_extra_info.pei_information2 = 'Y' THEN --Hawaiian and Part Hawaiian
142 l_ethnic_origin := 2;
143 l_race_count := l_race_count + 1;
144 END IF;
145
146 IF l_extra_info.pei_information3 = 'Y' THEN -- Chinese and Korean
147 l_ethnic_origin := 3;
148 l_race_count := l_race_count + 1;
149 END IF;
150
151 IF l_extra_info.pei_information4 = 'Y' THEN -- Filipino
152 l_ethnic_origin := 4;
153 l_race_count := l_race_count + 1;
154 END IF;
155
156 IF l_extra_info.pei_information5 = 'Y' THEN --Japanese
157 l_ethnic_origin := 5;
158 l_race_count := l_race_count + 1;
159 END IF;
160
161 IF l_extra_info.pei_information6 = 'Y' THEN -- Others
162 l_ethnic_origin := 6;
163 l_race_count := l_race_count + 1;
164 END IF;
165
166 IF l_race_count = 0 THEN
167 l_ethnic_origin := 99; -- Unknonwn Race
168 ELSIF l_race_count > 1 THEN
169 l_ethnic_origin := 13; -- Two or more Races
170 END IF;
171 CLOSE csr_ethnic_origin;
172
173 ELSE
174 CLOSE csr_ethnic_origin;
175 l_ethnic_origin := 99; -- Unknonwn Race
176 END IF;
177
178 RETURN l_ethnic_origin;
179 END derive_single_race;
180
181 /** Function to convert the data into an xml string **/
182 FUNCTION convert_into_xml( p_name IN VARCHAR2,
183 p_value IN VARCHAR2,
184 p_type IN CHAR)
185 RETURN VARCHAR2 IS
186
187 l_convert_data VARCHAR2(32767);
188
189 BEGIN
190
191 IF p_type = 'D' THEN
192 l_convert_data := '<'||p_name||'>'||p_value||'</'||p_name||'>';
193 ELSE
194 l_convert_data := '<'||p_name||'>';
195 END IF;
196
197 RETURN(l_convert_data);
198 END convert_into_xml;
199
200 /**To get the meaning of Job Categories **/
201 FUNCTION get_lookup_meaning(p_emp_category IN NUMBER, p_lookup_code IN NUMBER)
202 RETURN VARCHAR2 IS
203 l_meaning VARCHAR2(80);
204 BEGIN
205
206 IF p_lookup_code = 1 THEN
207 IF p_emp_category = 1 THEN
208 l_meaning := 'OFFICIALS/ ADMINISTRATORS';
209 ELSE
210 l_meaning := 'Officials/Admin.';
211 END IF;
212 ELSIF p_lookup_code = 2 THEN
213 IF p_emp_category = 1 THEN
214 l_meaning := 'PROFESSIONALS';
215 ELSE
216 l_meaning := 'Professionals';
217 END IF;
218 ELSIF p_lookup_code = 3 THEN
219 IF p_emp_category = 1 THEN
220 l_meaning := 'TECHNICIANS';
221 ELSE
222 l_meaning := 'Technicians';
223 END IF;
224 ELSIF p_lookup_code = 4 THEN
225 IF p_emp_category = 1 THEN
226 l_meaning := 'PROTECTIVE SERVICES';
227 ELSE
228 l_meaning := 'Protective Serv.';
229 END IF;
230 ELSIF p_lookup_code = 5 THEN
231 IF p_emp_category = 1 THEN
232 l_meaning := 'PARA-PROFESSIONALS';
233 ELSE
234 l_meaning := 'Para-Professional';
235 END IF;
236 ELSIF p_lookup_code = 6 THEN
237 IF p_emp_category = 1 THEN
238 l_meaning := 'ADMINISTRATIVE SUPPORT';
239 ELSE
240 l_meaning := 'Admin.Support';
241 END IF;
242 ELSIF p_lookup_code = 7 THEN
243 IF p_emp_category = 1 THEN
244 l_meaning := 'SKILLED CRAFT';
245 ELSE
246 l_meaning := 'Skilled Craft';
247 END IF;
248 ELSIF p_lookup_code = 8 THEN
249 IF p_emp_category = 1 THEN
250 l_meaning := 'SERVICE/ MAINTENANCE';
251 ELSE
252 l_meaning := 'Serv./Maint.';
253 END IF;
254 END IF;
255 RETURN l_meaning;
256 END get_lookup_meaning;
257
258 /** Function to return the function number with the function code as input **/
259 FUNCTION get_function_number(p_function_code IN NUMBER)
260 RETURN NUMBER IS
261 l_function_number NUMBER := 0;
262 BEGIN
263
264 IF p_function_code = 10 THEN
265 l_function_number := 1;
266 ELSIF p_function_code = 20 THEN
267 l_function_number := 2;
268 ELSIF p_function_code = 30 THEN
269 l_function_number := 3;
270 ELSIF p_function_code = 40 THEN
271 l_function_number := 4;
272 ELSIF p_function_code = 50 THEN
273 l_function_number := 5;
274 ELSIF p_function_code = 60 THEN
275 l_function_number := 6;
276 ELSIF p_function_code = 70 THEN
277 l_function_number := 7;
278 ELSIF p_function_code = 80 THEN
279 l_function_number := 8;
280 ELSIF p_function_code = 90 THEN
281 l_function_number := 9;
282 ELSIF p_function_code = 100 THEN
283 l_function_number := 10;
284 ELSIF p_function_code = 110 THEN
285 l_function_number := 11;
286 ELSIF p_function_code = 120 THEN
287 l_function_number := 12;
288 ELSIF p_function_code = 130 THEN
289 l_function_number := 13;
290 ELSIF p_function_code = 140 THEN
291 l_function_number := 14;
292 ELSIF p_function_code = 150 THEN
293 l_function_number := 15;
294 END IF;
295
296 RETURN l_function_number;
297 END get_function_number;
298
299 /** procedure to generate XML for those functions which have to be checked
300 in the first page of the report. **/
301 PROCEDURE check_function(p_function_code IN NUMBER) IS
302 BEGIN
303
304 IF p_function_code = 10 THEN
305 l_xml_string := convert_into_xml('G_FUN_1_CHECK_VAL','true','D');
306 ELSIF p_function_code = 20 THEN
307 l_xml_string := convert_into_xml('G_FUN_2_CHECK_VAL','true','D');
308 ELSIF p_function_code = 30 THEN
309 l_xml_string := convert_into_xml('G_FUN_3_CHECK_VAL','true','D');
310 ELSIF p_function_code = 40 THEN
311 l_xml_string := convert_into_xml('G_FUN_4_CHECK_VAL','true','D');
312 ELSIF p_function_code = 50 THEN
313 l_xml_string := convert_into_xml('G_FUN_5_CHECK_VAL','true','D');
314 ELSIF p_function_code = 60 THEN
315 l_xml_string := convert_into_xml('G_FUN_6_CHECK_VAL','true','D');
316 ELSIF p_function_code = 70 THEN
317 l_xml_string := convert_into_xml('G_FUN_7_CHECK_VAL','true','D');
318 ELSIF p_function_code = 80 THEN
319 l_xml_string := convert_into_xml('G_FUN_8_CHECK_VAL','true','D');
320 ELSIF p_function_code = 90 THEN
321 l_xml_string := convert_into_xml('G_FUN_9_CHECK_VAL','true','D');
322 ELSIF p_function_code = 100 THEN
323 l_xml_string := convert_into_xml('G_FUN_10_CHECK_VAL','true','D');
324 ELSIF p_function_code = 110 THEN
325 l_xml_string := convert_into_xml('G_FUN_11_CHECK_VAL','true','D');
326 ELSIF p_function_code = 120 THEN
327 l_xml_string := convert_into_xml('G_FUN_12_CHECK_VAL','true','D');
328 ELSIF p_function_code = 130 THEN
329 l_xml_string := convert_into_xml('G_FUN_13_CHECK_VAL','true','D');
330 ELSIF p_function_code = 140 THEN
331 l_xml_string := convert_into_xml('G_FUN_14_CHECK_VAL','true','D');
332 ELSIF p_function_code = 150 THEN
333 l_xml_string := convert_into_xml('G_FUN_15_CHECK_VAL','true','D');
334 END IF;
335
336 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
337 END check_function;
338
339 /**procedure to generate_sql**/
340 PROCEDURE generate_sql(p_job_codes IN VARCHAR2 , p_dynamic_where IN VARCHAR2) IS
341 BEGIN
342
343
344 g_nh_sql := 'SELECT hl.lookup_code job_category_code,
345 hl.meaning job_category_name,'
346 || g_select_clause || g_from_where_clause || g_nh_effective_dates
347 ||' AND job.job_information7 in (' || p_job_codes || ')'
348 ||' AND hl.lookup_code = :1 '
349 ||' AND ass.employment_category in ('|| g_fp_regulars ||')'
350 || g_group_order_by;
351
352 g_ft_emp_sql := 'SELECT hl.lookup_code job_category_code,
353 hl.meaning job_category_name,'
354 ||g_select_clause || g_from_where_clause|| g_ft_effective_dates
355 ||' AND job.job_information7 in ( ' || p_job_codes || ' ) '
356 ||' AND hl.lookup_code = :1 '
357 || p_dynamic_where
358 ||' AND ass.employment_category in ('|| g_fp_regulars ||')'
359 || g_group_order_by;
360
361 g_oft_sql := 'SELECT hl.lookup_code job_category_code,
362 hl.meaning job_category_name,'
363 ||g_select_clause||g_from_where_clause||g_ft_effective_dates
364 ||' AND job.job_information7 in ( ' || p_job_codes || ' )'
365 ||' AND hl.lookup_code = :1 '
366 ||' AND ass.employment_category NOT IN ('|| g_fp_regulars ||')'
367 ||g_group_order_by;
368 END generate_sql;
369
370 /** Procedure to populate ft_emp_table **/
371 PROCEDURE populate_ft_emp_data(p_function_code IN VARCHAR2) IS
372
373 l_function_code VARCHAR2(32767);
374 l_job_category_code VARCHAR2(30);
375 l_job_category_name VARCHAR2(80);
376 l_cons_total_category_emps NUMBER := 0;
377 l_no_cons_wmale_emps NUMBER := 0;
378 l_no_cons_hmale_emps NUMBER := 0;
379 l_no_cons_cmale_emps NUMBER := 0;
380 l_no_cons_fmale_emps NUMBER := 0;
381 l_no_cons_jmale_emps NUMBER := 0;
382 l_no_cons_omale_emps NUMBER := 0;
383 l_no_cons_wfemale_emps NUMBER := 0;
384 l_no_cons_hfemale_emps NUMBER := 0;
385 l_no_cons_cfemale_emps NUMBER := 0;
386 l_no_cons_ffemale_emps NUMBER := 0;
387 l_no_cons_jfemale_emps NUMBER := 0;
388 l_no_cons_ofemale_emps NUMBER := 0;
389
390 CURSOR get_eeo4_lookup_details
391 IS
392 SELECT lookup_code, meaning, description
393 FROM hr_lookups
394 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
395 ORDER BY lookup_code;
396
397 BEGIN
398
399 l_function_code := p_function_code;
400
401 FOR i IN get_eeo4_lookup_details LOOP -- for each job category
402
403 g_lookup_code := i.lookup_code;
404 g_meaning := i.meaning;
405
406 FOR j IN 1 .. 8 LOOP -- for each salary range
407 CASE j
408 WHEN 1 THEN g_start_salary := 100;
409 g_end_salary := 15999;
410 g_salary_range := '$0.1-15.9';
411 WHEN 2 THEN g_start_salary := 16000;
412 g_end_salary := 19999;
413 g_salary_range := '16.0-19.9';
414 WHEN 3 THEN g_start_salary := 20000;
415 g_end_salary := 24999;
416 g_salary_range := '20.0-24.9';
417 WHEN 4 THEN g_start_salary := 25000;
418 g_end_salary := 32999;
419 g_salary_range := '25.0-32.9';
420 WHEN 5 THEN g_start_salary := 33000;
421 g_end_salary := 42999;
422 g_salary_range := '33.0-42.9';
423 WHEN 6 THEN g_start_salary := 43000;
424 g_end_salary := 54999;
425 g_salary_range := '43.0-54.9';
426 WHEN 7 THEN g_start_salary := 55000;
427 g_end_salary := 69999;
428 g_salary_range := '55.0-69.9';
429 WHEN 8 THEN g_start_salary := 70000;
430 g_end_salary := 99900;
431 g_salary_range := '70.0 PLUS';
432 END CASE;
433
434 source_cursor := dbms_sql.open_cursor;
435
436 IF J = 8 THEN
437 g_dynamic_where := ' AND round(NVL(ppp.proposed_salary_n * ppb.pay_annualization_factor,0)) >= 70000 ';
438 ELSE
439 g_dynamic_where := ' AND round(NVL(ppp.proposed_salary_n * ppb.pay_annualization_factor,0)) BETWEEN ' || g_start_salary || ' AND ' || g_end_salary;
440 END IF;
441
442 generate_sql(p_function_code, g_dynamic_where);
443
444 dbms_sql.parse(source_cursor,g_ft_emp_sql,2);
445 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
446
447 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
448 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
449
450 dbms_sql.define_column(source_cursor,3,l_no_cons_wmale_emps);
451 dbms_sql.define_column(source_cursor,4,l_no_cons_hmale_emps);
452 dbms_sql.define_column(source_cursor,5,l_no_cons_cmale_emps);
453 dbms_sql.define_column(source_cursor,6,l_no_cons_fmale_emps);
454 dbms_sql.define_column(source_cursor,7,l_no_cons_jmale_emps);
455 dbms_sql.define_column(source_cursor,8,l_no_cons_omale_emps);
456 dbms_sql.define_column(source_cursor,9,l_no_cons_wfemale_emps);
457 dbms_sql.define_column(source_cursor,10,l_no_cons_hfemale_emps);
458 dbms_sql.define_column(source_cursor,11,l_no_cons_cfemale_emps);
459 dbms_sql.define_column(source_cursor,12,l_no_cons_ffemale_emps);
460 dbms_sql.define_column(source_cursor,13,l_no_cons_jfemale_emps);
461 dbms_sql.define_column(source_cursor,14,l_no_cons_ofemale_emps);
462
463 rows_processed := dbms_sql.EXECUTE(source_cursor);
464
465 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
466 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
467 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
468
469 dbms_sql.column_value(source_cursor,3,l_no_cons_wmale_emps);
470 dbms_sql.column_value(source_cursor,4,l_no_cons_hmale_emps);
471 dbms_sql.column_value(source_cursor,5,l_no_cons_cmale_emps);
472 dbms_sql.column_value(source_cursor,6,l_no_cons_fmale_emps);
473 dbms_sql.column_value(source_cursor,7,l_no_cons_jmale_emps);
474 dbms_sql.column_value(source_cursor,8,l_no_cons_omale_emps);
475 dbms_sql.column_value(source_cursor,9,l_no_cons_wfemale_emps);
476 dbms_sql.column_value(source_cursor,10,l_no_cons_hfemale_emps);
477 dbms_sql.column_value(source_cursor,11,l_no_cons_cfemale_emps);
478 dbms_sql.column_value(source_cursor,12,l_no_cons_ffemale_emps);
479 dbms_sql.column_value(source_cursor,13,l_no_cons_jfemale_emps);
480 dbms_sql.column_value(source_cursor,14,l_no_cons_ofemale_emps);
481
482
483 l_counter1 := l_counter1 + 1;
484
485 IF g_for_all_emp = 'T' THEN
486 ft_emp_table(l_counter1).job_function := 'AF';
487 ELSIF g_for_all_emp = 'F' THEN
488 ft_emp_table(l_counter1).job_function := ltrim(rtrim(l_function_code));
489 END IF;
490
491 ft_emp_table(l_counter1).lookup_code := ltrim(rtrim(l_job_category_code));
492 ft_emp_table(l_counter1).salary_range := ltrim(rtrim(g_salary_range));
493
494 ft_emp_table(l_counter1).cons_total_category_emps := ltrim(rtrim(l_no_cons_wmale_emps)) +
495 ltrim(rtrim(l_no_cons_hmale_emps)) +
496 ltrim(rtrim(l_no_cons_cmale_emps)) +
497 ltrim(rtrim(l_no_cons_fmale_emps)) +
498 ltrim(rtrim(l_no_cons_jmale_emps)) +
499 ltrim(rtrim(l_no_cons_omale_emps)) +
500 ltrim(rtrim(l_no_cons_wfemale_emps)) +
501 ltrim(rtrim(l_no_cons_hfemale_emps)) +
502 ltrim(rtrim(l_no_cons_cfemale_emps)) +
503 ltrim(rtrim(l_no_cons_ffemale_emps)) +
504 ltrim(rtrim(l_no_cons_jfemale_emps)) +
505 ltrim(rtrim(l_no_cons_ofemale_emps));
506
507 ft_emp_table(l_counter1).no_cons_wmale_emps := ltrim(rtrim(l_no_cons_wmale_emps));
508 ft_emp_table(l_counter1).no_cons_hmale_emps := ltrim(rtrim(l_no_cons_hmale_emps));
509 ft_emp_table(l_counter1).no_cons_cmale_emps := ltrim(rtrim(l_no_cons_cmale_emps));
510 ft_emp_table(l_counter1).no_cons_fmale_emps := ltrim(rtrim(l_no_cons_fmale_emps));
511 ft_emp_table(l_counter1).no_cons_jmale_emps := ltrim(rtrim(l_no_cons_jmale_emps));
512 ft_emp_table(l_counter1).no_cons_omale_emps := ltrim(rtrim(l_no_cons_omale_emps));
513 ft_emp_table(l_counter1).no_cons_wfemale_emps := ltrim(rtrim(l_no_cons_wfemale_emps));
514 ft_emp_table(l_counter1).no_cons_hfemale_emps := ltrim(rtrim(l_no_cons_hfemale_emps));
515 ft_emp_table(l_counter1).no_cons_cfemale_emps := ltrim(rtrim(l_no_cons_cfemale_emps));
516 ft_emp_table(l_counter1).no_cons_ffemale_emps := ltrim(rtrim(l_no_cons_ffemale_emps));
517 ft_emp_table(l_counter1).no_cons_jfemale_emps := ltrim(rtrim(l_no_cons_jfemale_emps));
518 ft_emp_table(l_counter1).no_cons_ofemale_emps := ltrim(rtrim(l_no_cons_ofemale_emps));
519
520 ELSE
521
522 l_counter1 := l_counter1 + 1;
523
524 IF g_for_all_emp = 'T' THEN
525 ft_emp_table(l_counter1).job_function := 'AF';
526 ELSIF g_for_all_emp = 'F' THEN
527 ft_emp_table(l_counter1).job_function := ltrim(rtrim(l_function_code));
528 END IF;
529 ft_emp_table(l_counter1).lookup_code := ltrim(rtrim(g_lookup_code));
530 ft_emp_table(l_counter1).salary_range := ltrim(rtrim(g_salary_range));
531 ft_emp_table(l_counter1).cons_total_category_emps := 0;
532 ft_emp_table(l_counter1).no_cons_wmale_emps := 0;
533 ft_emp_table(l_counter1).no_cons_hmale_emps := 0;
534 ft_emp_table(l_counter1).no_cons_cmale_emps := 0;
535 ft_emp_table(l_counter1).no_cons_fmale_emps := 0;
536 ft_emp_table(l_counter1).no_cons_jmale_emps := 0;
537 ft_emp_table(l_counter1).no_cons_omale_emps := 0;
538 ft_emp_table(l_counter1).no_cons_wfemale_emps := 0;
539 ft_emp_table(l_counter1).no_cons_hfemale_emps := 0;
540 ft_emp_table(l_counter1).no_cons_cfemale_emps := 0;
541 ft_emp_table(l_counter1).no_cons_ffemale_emps := 0;
542 ft_emp_table(l_counter1).no_cons_jfemale_emps := 0;
543 ft_emp_table(l_counter1).no_cons_ofemale_emps := 0;
544 END IF;
545 dbms_sql.close_cursor(source_cursor); -- Closing the cursor
546 END LOOP; -- for each salary range
547 END LOOP; -- for each job category
548
549 END populate_ft_emp_data;
550
551 /** procedure to populate other_ft_emp_table **/
552 PROCEDURE populate_oft_emp_data(p_function_code IN VARCHAR2) IS
553
554 l_function_code VARCHAR2(32767);
555 l_job_category_code VARCHAR2(30);
556 l_job_category_name VARCHAR2(80);
557 l_cons_total_category_emps NUMBER := 0;
558 l_no_cons_wmale_emps NUMBER := 0;
559 l_no_cons_hmale_emps NUMBER := 0;
560 l_no_cons_cmale_emps NUMBER := 0;
561 l_no_cons_fmale_emps NUMBER := 0;
562 l_no_cons_jmale_emps NUMBER := 0;
563 l_no_cons_omale_emps NUMBER := 0;
564 l_no_cons_wfemale_emps NUMBER := 0;
565 l_no_cons_hfemale_emps NUMBER := 0;
566 l_no_cons_cfemale_emps NUMBER := 0;
567 l_no_cons_ffemale_emps NUMBER := 0;
568 l_no_cons_jfemale_emps NUMBER := 0;
569 l_no_cons_ofemale_emps NUMBER := 0;
570
571 CURSOR get_eeo4_lookup_details IS
572 SELECT lookup_code, meaning, description
573 FROM hr_lookups
574 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
575 ORDER BY lookup_code;
576 BEGIN
577
578 l_function_code := p_function_code;
579
580 FOR i IN get_eeo4_lookup_details LOOP -- for each job category
581
582 g_meaning := i.meaning;
583 g_lookup_code := i.lookup_code;
584
585 source_cursor := dbms_sql.open_cursor;
586
587 dbms_sql.parse(source_cursor,g_oft_sql,2);
588 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
589
590 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
591 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
592
593 dbms_sql.define_column(source_cursor,3,l_no_cons_wmale_emps);
594 dbms_sql.define_column(source_cursor,4,l_no_cons_hmale_emps);
595 dbms_sql.define_column(source_cursor,5,l_no_cons_cmale_emps);
596 dbms_sql.define_column(source_cursor,6,l_no_cons_fmale_emps);
597 dbms_sql.define_column(source_cursor,7,l_no_cons_jmale_emps);
598 dbms_sql.define_column(source_cursor,8,l_no_cons_omale_emps);
599 dbms_sql.define_column(source_cursor,9,l_no_cons_wfemale_emps);
600 dbms_sql.define_column(source_cursor,10,l_no_cons_hfemale_emps);
601 dbms_sql.define_column(source_cursor,11,l_no_cons_cfemale_emps);
602 dbms_sql.define_column(source_cursor,12,l_no_cons_ffemale_emps);
603 dbms_sql.define_column(source_cursor,13,l_no_cons_jfemale_emps);
604 dbms_sql.define_column(source_cursor,14,l_no_cons_ofemale_emps);
605
606 rows_processed := dbms_sql.EXECUTE(source_cursor);
607
608 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
609
610 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
611 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
612
613 dbms_sql.column_value(source_cursor,3,l_no_cons_wmale_emps);
614 dbms_sql.column_value(source_cursor,4,l_no_cons_hmale_emps);
615 dbms_sql.column_value(source_cursor,5,l_no_cons_cmale_emps);
616 dbms_sql.column_value(source_cursor,6,l_no_cons_fmale_emps);
617 dbms_sql.column_value(source_cursor,7,l_no_cons_jmale_emps);
618 dbms_sql.column_value(source_cursor,8,l_no_cons_omale_emps);
619 dbms_sql.column_value(source_cursor,9,l_no_cons_wfemale_emps);
620 dbms_sql.column_value(source_cursor,10,l_no_cons_hfemale_emps);
621 dbms_sql.column_value(source_cursor,11,l_no_cons_cfemale_emps);
622 dbms_sql.column_value(source_cursor,12,l_no_cons_ffemale_emps);
623 dbms_sql.column_value(source_cursor,13,l_no_cons_jfemale_emps);
624 dbms_sql.column_value(source_cursor,14,l_no_cons_ofemale_emps);
625
626 l_counter2 := l_counter2 + 1;
627
628 IF g_for_all_emp = 'T' THEN
629 other_ft_emp_table(l_counter2).job_function := 'AF';
630 ELSIF g_for_all_emp = 'F' THEN
631 other_ft_emp_table(l_counter2).job_function := ltrim(rtrim(l_function_code));
632 END IF;
633
634 other_ft_emp_table(l_counter2).lookup_code := ltrim(rtrim(l_job_category_code));
635
636 other_ft_emp_table(l_counter2).cons_total_category_emps := ltrim(rtrim(l_no_cons_wmale_emps)) +
637 ltrim(rtrim(l_no_cons_hmale_emps)) +
638 ltrim(rtrim(l_no_cons_cmale_emps)) +
639 ltrim(rtrim(l_no_cons_fmale_emps)) +
640 ltrim(rtrim(l_no_cons_jmale_emps)) +
641 ltrim(rtrim(l_no_cons_omale_emps)) +
642 ltrim(rtrim(l_no_cons_wfemale_emps)) +
643 ltrim(rtrim(l_no_cons_hfemale_emps)) +
644 ltrim(rtrim(l_no_cons_cfemale_emps)) +
645 ltrim(rtrim(l_no_cons_ffemale_emps)) +
646 ltrim(rtrim(l_no_cons_jfemale_emps)) +
647 ltrim(rtrim(l_no_cons_ofemale_emps));
648
649 other_ft_emp_table(l_counter2).no_cons_wmale_emps := ltrim(rtrim(l_no_cons_wmale_emps));
650 other_ft_emp_table(l_counter2).no_cons_hmale_emps := ltrim(rtrim(l_no_cons_hmale_emps));
651 other_ft_emp_table(l_counter2).no_cons_cmale_emps := ltrim(rtrim(l_no_cons_cmale_emps));
652 other_ft_emp_table(l_counter2).no_cons_fmale_emps := ltrim(rtrim(l_no_cons_fmale_emps));
653 other_ft_emp_table(l_counter2).no_cons_jmale_emps := ltrim(rtrim(l_no_cons_jmale_emps));
654 other_ft_emp_table(l_counter2).no_cons_omale_emps := ltrim(rtrim(l_no_cons_omale_emps));
655 other_ft_emp_table(l_counter2).no_cons_wfemale_emps := ltrim(rtrim(l_no_cons_wfemale_emps));
656 other_ft_emp_table(l_counter2).no_cons_hfemale_emps := ltrim(rtrim(l_no_cons_hfemale_emps));
657 other_ft_emp_table(l_counter2).no_cons_cfemale_emps := ltrim(rtrim(l_no_cons_cfemale_emps));
658 other_ft_emp_table(l_counter2).no_cons_ffemale_emps := ltrim(rtrim(l_no_cons_ffemale_emps));
659 other_ft_emp_table(l_counter2).no_cons_jfemale_emps := ltrim(rtrim(l_no_cons_jfemale_emps));
660 other_ft_emp_table(l_counter2).no_cons_ofemale_emps := ltrim(rtrim(l_no_cons_ofemale_emps));
661
662 ELSE
663
664 l_counter2 := l_counter2 + 1;
665
666 IF g_for_all_emp = 'T' THEN
667 other_ft_emp_table(l_counter2).job_function := 'AF';
668 ELSIF g_for_all_emp = 'F' THEN
669 other_ft_emp_table(l_counter2).job_function := ltrim(rtrim(l_function_code));
670 END IF;
671
672 other_ft_emp_table(l_counter2).lookup_code := ltrim(rtrim(g_lookup_code));
673
674 other_ft_emp_table(l_counter2).cons_total_category_emps := 0;
675 other_ft_emp_table(l_counter2).no_cons_wmale_emps := 0;
676 other_ft_emp_table(l_counter2).no_cons_hmale_emps := 0;
677 other_ft_emp_table(l_counter2).no_cons_cmale_emps := 0;
678 other_ft_emp_table(l_counter2).no_cons_fmale_emps := 0;
679 other_ft_emp_table(l_counter2).no_cons_jmale_emps := 0;
680 other_ft_emp_table(l_counter2).no_cons_omale_emps := 0;
681 other_ft_emp_table(l_counter2).no_cons_wfemale_emps := 0;
682 other_ft_emp_table(l_counter2).no_cons_hfemale_emps := 0;
683 other_ft_emp_table(l_counter2).no_cons_cfemale_emps := 0;
684 other_ft_emp_table(l_counter2).no_cons_ffemale_emps := 0;
685 other_ft_emp_table(l_counter2).no_cons_jfemale_emps := 0;
686 other_ft_emp_table(l_counter2).no_cons_ofemale_emps := 0;
687
688 END IF;
689
690 dbms_sql.close_cursor(source_cursor);
691
692 END LOOP;--FOR EACH JOB CATEGORY
693
694 END populate_oft_emp_data;
695
696 /** procedure to populate new_hire_table **/
697 PROCEDURE populate_nh_emp_data(p_function_code IN VARCHAR2) IS
698
699 l_function_code VARCHAR2(32767);
700 l_job_category_code VARCHAR2(30);
701 l_job_category_name VARCHAR2(80);
702 l_cons_total_category_emps NUMBER := 0;
703 l_no_cons_wmale_emps NUMBER := 0;
704 l_no_cons_hmale_emps NUMBER := 0;
705 l_no_cons_cmale_emps NUMBER := 0;
706 l_no_cons_fmale_emps NUMBER := 0;
707 l_no_cons_jmale_emps NUMBER := 0;
708 l_no_cons_omale_emps NUMBER := 0;
709 l_no_cons_wfemale_emps NUMBER := 0;
710 l_no_cons_hfemale_emps NUMBER := 0;
711 l_no_cons_cfemale_emps NUMBER := 0;
712 l_no_cons_ffemale_emps NUMBER := 0;
713 l_no_cons_jfemale_emps NUMBER := 0;
714 l_no_cons_ofemale_emps NUMBER := 0;
715
716 /* Job Category is same for both EEO4 and EEO4A ethnic origins */
717 CURSOR get_eeo4_lookup_details
718 IS
719 SELECT lookup_code, meaning, description
720 FROM hr_lookups
721 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
722 ORDER BY lookup_code;
723
724 BEGIN
725
726 l_function_code := p_function_code;
727 FOR i IN get_eeo4_lookup_details LOOP -- for each job category
728
729 g_lookup_code := i.lookup_code;
730 g_meaning := i.meaning;
731
732 source_cursor := dbms_sql.open_cursor;
733
734 dbms_sql.parse(source_cursor,g_nh_sql,2);
735 dbms_sql.BIND_VARIABLE(source_cursor,':1',g_lookup_code);
736
737 dbms_sql.define_column_char(source_cursor,1,l_job_category_code,4000);
738 dbms_sql.define_column_char(source_cursor,2,l_job_category_name,4000);
739
740 dbms_sql.define_column(source_cursor,3,l_no_cons_wmale_emps);
741 dbms_sql.define_column(source_cursor,4,l_no_cons_hmale_emps);
742 dbms_sql.define_column(source_cursor,5,l_no_cons_cmale_emps);
743 dbms_sql.define_column(source_cursor,6,l_no_cons_fmale_emps);
744 dbms_sql.define_column(source_cursor,7,l_no_cons_jmale_emps);
745 dbms_sql.define_column(source_cursor,8,l_no_cons_omale_emps);
746 dbms_sql.define_column(source_cursor,9,l_no_cons_wfemale_emps);
747 dbms_sql.define_column(source_cursor,10,l_no_cons_hfemale_emps);
748 dbms_sql.define_column(source_cursor,11,l_no_cons_cfemale_emps);
749 dbms_sql.define_column(source_cursor,12,l_no_cons_ffemale_emps);
750 dbms_sql.define_column(source_cursor,13,l_no_cons_jfemale_emps);
751 dbms_sql.define_column(source_cursor,14,l_no_cons_ofemale_emps);
752
753 rows_processed := dbms_sql.EXECUTE(source_cursor);
754
755 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
756
757 dbms_sql.column_value_char(source_cursor,1,l_job_category_code);
758 dbms_sql.column_value_char(source_cursor,2,l_job_category_name);
759
760 dbms_sql.column_value(source_cursor,3,l_no_cons_wmale_emps);
761 dbms_sql.column_value(source_cursor,4,l_no_cons_hmale_emps);
762 dbms_sql.column_value(source_cursor,5,l_no_cons_cmale_emps);
763 dbms_sql.column_value(source_cursor,6,l_no_cons_fmale_emps);
764 dbms_sql.column_value(source_cursor,7,l_no_cons_jmale_emps);
765 dbms_sql.column_value(source_cursor,8,l_no_cons_omale_emps);
766 dbms_sql.column_value(source_cursor,9,l_no_cons_wfemale_emps);
767 dbms_sql.column_value(source_cursor,10,l_no_cons_hfemale_emps);
768 dbms_sql.column_value(source_cursor,11,l_no_cons_cfemale_emps);
769 dbms_sql.column_value(source_cursor,12,l_no_cons_ffemale_emps);
770 dbms_sql.column_value(source_cursor,13,l_no_cons_jfemale_emps);
771 dbms_sql.column_value(source_cursor,14,l_no_cons_ofemale_emps);
772
773 l_counter3 := l_counter3 + 1;
774
775 IF g_for_all_emp = 'T' THEN
776 new_hire_table(l_counter3).job_function := 'AF';
777 ELSIF g_for_all_emp = 'F' THEN
778 new_hire_table(l_counter3).job_function := ltrim(rtrim(l_function_code));
779 END IF;
780
781 new_hire_table(l_counter3).lookup_code := ltrim(rtrim(g_lookup_code));
782 new_hire_table(l_counter3).cons_total_category_emps := ltrim(rtrim(l_no_cons_wmale_emps)) +
783 ltrim(rtrim(l_no_cons_hmale_emps)) +
784 ltrim(rtrim(l_no_cons_cmale_emps)) +
785 ltrim(rtrim(l_no_cons_fmale_emps)) +
786 ltrim(rtrim(l_no_cons_jmale_emps)) +
787 ltrim(rtrim(l_no_cons_omale_emps)) +
788 ltrim(rtrim(l_no_cons_wfemale_emps)) +
789 ltrim(rtrim(l_no_cons_hfemale_emps)) +
790 ltrim(rtrim(l_no_cons_cfemale_emps)) +
791 ltrim(rtrim(l_no_cons_ffemale_emps)) +
792 ltrim(rtrim(l_no_cons_jfemale_emps)) +
793 ltrim(rtrim(l_no_cons_ofemale_emps));
794
795 new_hire_table(l_counter3).no_cons_wmale_emps := ltrim(rtrim(l_no_cons_wmale_emps));
796 new_hire_table(l_counter3).no_cons_hmale_emps := ltrim(rtrim(l_no_cons_hmale_emps));
797 new_hire_table(l_counter3).no_cons_cmale_emps := ltrim(rtrim(l_no_cons_cmale_emps));
798 new_hire_table(l_counter3).no_cons_fmale_emps := ltrim(rtrim(l_no_cons_fmale_emps));
799 new_hire_table(l_counter3).no_cons_jmale_emps := ltrim(rtrim(l_no_cons_jmale_emps));
800 new_hire_table(l_counter3).no_cons_omale_emps := ltrim(rtrim(l_no_cons_omale_emps));
801 new_hire_table(l_counter3).no_cons_wfemale_emps := ltrim(rtrim(l_no_cons_wfemale_emps));
802 new_hire_table(l_counter3).no_cons_hfemale_emps := ltrim(rtrim(l_no_cons_hfemale_emps));
803 new_hire_table(l_counter3).no_cons_cfemale_emps := ltrim(rtrim(l_no_cons_cfemale_emps));
804 new_hire_table(l_counter3).no_cons_ffemale_emps := ltrim(rtrim(l_no_cons_ffemale_emps));
805 new_hire_table(l_counter3).no_cons_jfemale_emps := ltrim(rtrim(l_no_cons_jfemale_emps));
806 new_hire_table(l_counter3).no_cons_ofemale_emps := ltrim(rtrim(l_no_cons_ofemale_emps));
807
808 ELSE
809 l_counter3 := l_counter3 + 1;
810
811 IF g_for_all_emp = 'T' THEN
812 new_hire_table(l_counter3).job_function := 'AF';
813 ELSIF g_for_all_emp = 'F' THEN
814 new_hire_table(l_counter3).job_function := ltrim(rtrim(l_function_code));
815 END IF;
816
817 new_hire_table(l_counter3).lookup_code := ltrim(rtrim(g_lookup_code));
818 new_hire_table(l_counter3).cons_total_category_emps := 0;
819 new_hire_table(l_counter3).no_cons_wmale_emps := 0;
820 new_hire_table(l_counter3).no_cons_hmale_emps := 0;
821 new_hire_table(l_counter3).no_cons_cmale_emps := 0;
822 new_hire_table(l_counter3).no_cons_fmale_emps := 0;
823 new_hire_table(l_counter3).no_cons_jmale_emps := 0;
824 new_hire_table(l_counter3).no_cons_omale_emps := 0;
825 new_hire_table(l_counter3).no_cons_wfemale_emps := 0;
826 new_hire_table(l_counter3).no_cons_hfemale_emps := 0;
827 new_hire_table(l_counter3).no_cons_cfemale_emps := 0;
828 new_hire_table(l_counter3).no_cons_ffemale_emps := 0;
829 new_hire_table(l_counter3).no_cons_jfemale_emps := 0;
830 new_hire_table(l_counter3).no_cons_ofemale_emps := 0;
831 END IF;
832 dbms_sql.close_cursor(source_cursor);
833 END LOOP;--for each job category
834
835 END populate_nh_emp_data;
836
837 /** procedure to generate header xml data **/
838 PROCEDURE generate_header_xml_data IS
839 BEGIN
840
841 l_xml_string := '<?xml version="1.0"?> <PQHEEO4A>';
842 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
843
844 END generate_header_xml_data;
845
846 /** procedure to generate the footer xml data **/
847 PROCEDURE generate_footer_xml_data IS
848
849 l_bg_name VARCHAR2(240); /* increased the size from 100 to 240 */
850
851 CURSOR get_bg_name
852 IS
853 SELECT name
854 FROM hr_organization_units
855 WHERE organization_id = g_business_group_id
856 AND business_group_id = g_business_group_id;
857
858 BEGIN
859 l_bg_name := ' ';
860
861 OPEN get_bg_name;
862 FETCH get_bg_name INTO l_bg_name;
863 CLOSE get_bg_name;
864
865 l_xml_string := convert_into_xml('C_BUSINESS_GROUP_NAME',l_bg_name,'D')
866 || convert_into_xml('C_REPORT_TYPE','2011 EEO-4A REPORT','D')
867 || '<C_ORGANIZATION_HIERARCHY></C_ORGANIZATION_HIERARCHY>
868 <C_EEO1_ORGANIZATION></C_EEO1_ORGANIZATION>
869 <C_END_OF_TIME></C_END_OF_TIME>
870 </PQHEEO4A>';
871
872 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
873
874 END generate_footer_xml_data;
875
876 /** procedure to generate jurisdiction and certifying officer xml data**/
877 PROCEDURE generate_juris_cert_xml_data IS
878
879 CURSOR get_jurisdiction_details
880 IS
881 SELECT NVL(hou.name,' ') jurisdiction_name,
882 NVL(hl.address_line_1,' ')||' '||NVL(hl.address_line_2,' ')
883 ||' '||NVL(hl.address_line_3,' ') address,
884 NVL(hl.town_or_city,' ') town_or_city,
885 NVL(hl.region_1,' ') county,
886 NVL(hl.region_2,' ')||' '||NVL(hl.postal_code,' ') state_zip
887 FROM hr_all_organization_units hou,
888 hr_locations hl
889 WHERE hou.location_id = hl.location_id
890 AND hou.business_group_id = g_business_group_id
891 AND hou.organization_id = g_business_group_id;
892
893 CURSOR get_cert_details
894 IS
895 SELECT NVL(org_information1,' ') cert_officer_name,
896 NVL(org_information2,' ') cert_officer_title,
897 NVL(org_information3,' ') contact_name,
898 NVL(org_information4,' ') contact_title,
899 NVL(org_information5,' ')
900 ||' '|| NVL(org_information6,' ') contact_address,
901 NVL(org_information7,' ')
902 ||' '|| NVL(org_information8,' ')
903 ||' '|| NVL(org_information9,' ') contact_city_state_zip,
904 NVL(org_information10,' ') contact_telephone,
905 NVL(org_information12,' ') control_number,
906 NVL(org_information15, ' ') email,
907 NVL(org_information14, ' ') fax
908 FROM hr_organization_information
909 WHERE org_information_context = 'EEO_REPORT'
910 AND organization_id = g_business_group_id;
911
912 BEGIN
913 l_xml_string := '<LIST_G_CERT_OFFICER_NAME>';
914
915 FOR rec IN get_cert_details LOOP
916
917 l_xml_string := l_xml_string || '<G_CERT_OFFICER_NAME>';
918 l_xml_string := l_xml_string || convert_into_xml('CERT_OFFICIAL_NAME',rec.cert_officer_name,'D');
919 l_xml_string := l_xml_string || convert_into_xml('CERT_OFFICIAL_TITLE',rec.cert_officer_title,'D');
920 l_xml_string := l_xml_string || convert_into_xml('CONTACT_NAME',rec.contact_name,'D');
921 l_xml_string := l_xml_string || convert_into_xml('CONTACT_TITLE',rec.contact_title,'D');
922 l_xml_string := l_xml_string || convert_into_xml('CONTACT_ADDRESS',rec.contact_address,'D');
923 l_xml_string := l_xml_string || convert_into_xml('CONTACT_CITY_STATE_ZIP',rec.contact_city_state_zip,'D');
924 l_xml_string := l_xml_string || convert_into_xml('CONTACT_TELEPHONE',rec.contact_telephone,'D');
925 g_control_number := rec.control_number;
926 l_xml_string := l_xml_string || convert_into_xml('CONTACT_FAX',rec.fax,'D');
927 l_xml_string := l_xml_string || convert_into_xml('CONTACT_EMAIL',rec.email,'D');
928 l_xml_string := l_xml_string || convert_into_xml('CONTROL_NUMBER',rec.control_number,'D');
929 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
930 l_xml_string := l_xml_string || '</G_CERT_OFFICER_NAME>';
931
932 END LOOP;
933
934 l_xml_string := l_xml_string || '</LIST_G_CERT_OFFICER_NAME>';
935
936 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
937
938 /**get jurisdiction details **/
939 l_xml_string := '<LIST_G_JURISDICTION_DETAIL>';
940
941 FOR rec IN get_jurisdiction_details LOOP
942
943 l_xml_string := l_xml_string || '<G_JURISDICTION_DETAIL>';
944 l_xml_string := l_xml_string || convert_into_xml('BUSINESS_NAME',rec.jurisdiction_name,'D');
945 l_xml_string := l_xml_string || convert_into_xml('ADDRESS',rec.address,'D');
946 l_xml_string := l_xml_string || convert_into_xml('CITY_TOWN',rec.town_or_city,'D');
947 l_xml_string := l_xml_string || convert_into_xml('COUNTY',rec.county,'D');
948 l_xml_string := l_xml_string || convert_into_xml('STATE_ZIP',rec.state_zip,'D');
949 l_xml_string := l_xml_string || convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
950 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
951 l_xml_string := l_xml_string || '</G_JURISDICTION_DETAIL>';
952
953 END LOOP;
954
955 l_xml_string := l_xml_string || '</LIST_G_JURISDICTION_DETAIL>';
956
957 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
958
959 END generate_juris_cert_xml_data;
960
961 /**Procedure to generate full time employees table xml data**/
962 PROCEDURE generate_ft_xml_data(p_function_code VARCHAR2) IS
963
964 l_function_code VARCHAR2(32767);
965 l_job_category_code VARCHAR2(30);
966 l_job_category_name VARCHAR2(80);
967
968 l_cs_no_wmale_emps NUMBER := 0;
969 l_cs_no_hmale_emps NUMBER := 0;
970 l_cs_no_cmale_emps NUMBER := 0;
971 l_cs_no_fmale_emps NUMBER := 0;
972 l_cs_no_jmale_emps NUMBER := 0;
973 l_cs_no_omale_emps NUMBER := 0;
974 l_cs_no_wfemale_emps NUMBER := 0;
975 l_cs_no_hfemale_emps NUMBER := 0;
976 l_cs_no_cfemale_emps NUMBER := 0;
977 l_cs_no_ffemale_emps NUMBER := 0;
978 l_cs_no_jfemale_emps NUMBER := 0;
979 l_cs_no_ofemale_emps NUMBER := 0;
980 l_cs_total_category_emps NUMBER := 0;
981
982 l_lookup_code VARCHAR2(30);
983 l_meaning VARCHAR2(80);
984
985 /** Job Category is same for EEO4 and EEO4A Ethnic origins **/
986 CURSOR get_eeo4_lookup_details
987 IS
988 SELECT lookup_code, meaning, description
989 FROM hr_lookups
990 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
991 ORDER BY lookup_code;
992
993 BEGIN
994
995 IF g_for_all_emp = 'T' THEN
996 l_function_code := 'AF';
997 ELSIF g_for_all_emp = 'F' THEN
998 l_function_code := p_function_code;
999 END IF;
1000
1001 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1002 l_xml_string := l_xml_string || convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','1. FULL-TIME EMPLOYEES (Temporary employees are not included)','D');
1003 l_xml_string := l_xml_string || '<LIST_G_JOB_CATEGORIES>';
1004
1005 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1006
1007 FOR i IN get_eeo4_lookup_details LOOP --for each job category
1008
1009 l_lookup_code := i.lookup_code;
1010 l_meaning := i.meaning;
1011
1012 FOR sal_range in 1.. 8 LOOP -- FOR EACH SALARY RANGE
1013
1014 CASE sal_range
1015 WHEN 1 THEN g_salary_range := '$0.1-15.9';
1016 WHEN 2 THEN g_salary_range := '16.0-19.9';
1017 WHEN 3 THEN g_salary_range := '20.0-24.9';
1018 WHEN 4 THEN g_salary_range := '25.0-32.9';
1019 WHEN 5 THEN g_salary_range := '33.0-42.9';
1020 WHEN 6 THEN g_salary_range := '43.0-54.9';
1021 WHEN 7 THEN g_salary_range := '55.0-69.9';
1022 WHEN 8 THEN g_salary_range := '70.0 PLUS';
1023 END CASE;
1024
1025 FOR counter in 1 .. ft_emp_table.count LOOP -- Fetch from ft_emp_table
1026
1027 IF (ltrim(trim(ft_emp_table(counter).job_function)) = ltrim(rtrim(l_function_code)))
1028 AND (ltrim(rtrim(ft_emp_table(counter).lookup_code)) = ltrim(rtrim(l_lookup_code)))
1029 AND (ltrim(rtrim(ft_emp_table(counter).salary_range)) = ltrim(rtrim(g_salary_range)))
1030 THEN
1031
1032 IF sal_range = 1 THEN
1033
1034 l_xml_string := '<G_JOB_CATEGORIES>';
1035 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_CODE',ft_emp_table(counter).lookup_code,'D');
1036 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');
1037
1038 IF ltrim(rtrim(l_lookup_code)) = '1' OR ltrim(rtrim(l_lookup_code)) = '7' THEN
1039 l_xml_string := l_xml_string || convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
1040 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
1041 END IF;
1042
1043 l_xml_string := l_xml_string || '<LIST_G_JOB_INFORMATION>';
1044 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1045 END IF;
1046
1047 l_xml_string := '<G_JOB_INFORMATION>';
1048
1049 l_cs_no_wmale_emps := l_cs_no_wmale_emps + ft_emp_table(counter).no_cons_wmale_emps ;
1050 l_cs_no_hmale_emps := l_cs_no_hmale_emps + ft_emp_table(counter).no_cons_hmale_emps;
1051 l_cs_no_cmale_emps := l_cs_no_cmale_emps + ft_emp_table(counter).no_cons_cmale_emps;
1052 l_cs_no_fmale_emps := l_cs_no_fmale_emps + ft_emp_table(counter).no_cons_fmale_emps;
1053 l_cs_no_jmale_emps := l_cs_no_jmale_emps + ft_emp_table(counter).no_cons_jmale_emps;
1054 l_cs_no_omale_emps := l_cs_no_omale_emps + ft_emp_table(counter).no_cons_omale_emps;
1055
1056 l_cs_no_wfemale_emps := l_cs_no_wfemale_emps + ft_emp_table(counter).no_cons_wfemale_emps;
1057 l_cs_no_hfemale_emps := l_cs_no_hfemale_emps + ft_emp_table(counter).no_cons_hfemale_emps;
1058 l_cs_no_cfemale_emps := l_cs_no_cfemale_emps + ft_emp_table(counter).no_cons_cfemale_emps;
1059 l_cs_no_ffemale_emps := l_cs_no_ffemale_emps + ft_emp_table(counter).no_cons_ffemale_emps;
1060 l_cs_no_jfemale_emps := l_cs_no_jfemale_emps + ft_emp_table(counter).no_cons_jfemale_emps;
1061 l_cs_no_ofemale_emps := l_cs_no_ofemale_emps + ft_emp_table(counter).no_cons_ofemale_emps;
1062
1063 l_cs_total_category_emps := l_cs_total_category_emps + ft_emp_table(counter).cons_total_category_emps;
1064
1065 l_xml_string := l_xml_string || convert_into_xml('EMPLOYEE_SALARY_SALARY_RANGE_A',g_line_number||'. '||ft_emp_table(counter).salary_range,'D');
1066 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',ft_emp_table(counter).cons_total_category_emps,'D');
1067
1068 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',ft_emp_table(counter).no_cons_wmale_emps,'D');
1069 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',ft_emp_table(counter).no_cons_hmale_emps,'D');
1070 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_CMALE_EMPS',ft_emp_table(counter).no_cons_cmale_emps,'D');
1071 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_FMALE_EMPS',ft_emp_table(counter).no_cons_fmale_emps,'D');
1072 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_JMALE_EMPS',ft_emp_table(counter).no_cons_jmale_emps,'D');
1073 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_OMALE_EMPS',ft_emp_table(counter).no_cons_omale_emps,'D');
1074
1075 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',ft_emp_table(counter).no_cons_wfemale_emps,'D');
1076 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',ft_emp_table(counter).no_cons_hfemale_emps,'D');
1077 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_CFEMALE_EMPS',ft_emp_table(counter).no_cons_cfemale_emps,'D');
1078 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_FFEMALE_EMPS',ft_emp_table(counter).no_cons_ffemale_emps,'D');
1079 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_JFEMALE_EMPS',ft_emp_table(counter).no_cons_jfemale_emps,'D');
1080 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_OFEMALE_EMPS',ft_emp_table(counter).no_cons_ofemale_emps,'D');
1081
1082 l_xml_string := l_xml_string || '</G_JOB_INFORMATION>';
1083
1084 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1085
1086 g_line_number := g_line_number + 1 ;-- Increment the line number
1087
1088 END IF;
1089 END LOOP;-- End of Fetch from ft_emp_table
1090 END LOOP; --END OF FOR EACH SALARY RANGE
1091
1092 l_xml_string := '</LIST_G_JOB_INFORMATION></G_JOB_CATEGORIES>';
1093
1094 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1095 END LOOP;--End for each job category
1096
1097 l_xml_string := '</LIST_G_JOB_CATEGORIES>';
1098 l_xml_string := l_xml_string || convert_into_xml('CF_TOTAL_TITLE',g_line_number||'. TOTAL FULL-TIME (LINES 1-64)','D');
1099 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WMALE_EMPS', l_cs_no_wmale_emps,'D');
1100 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HMALE_EMPS', l_cs_no_hmale_emps,'D');
1101 l_xml_string := l_xml_string || convert_into_xml('CS_NO_CMALE_EMPS', l_cs_no_cmale_emps,'D');
1102 l_xml_string := l_xml_string || convert_into_xml('CS_NO_FMALE_EMPS', l_cs_no_fmale_emps,'D');
1103 l_xml_string := l_xml_string || convert_into_xml('CS_NO_JMALE_EMPS', l_cs_no_jmale_emps,'D');
1104 l_xml_string := l_xml_string || convert_into_xml('CS_NO_OMALE_EMPS', l_cs_no_omale_emps,'D');
1105
1106 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WFEMALE_EMPS', l_cs_no_wfemale_emps,'D');
1107 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HFEMALE_EMPS', l_cs_no_hfemale_emps,'D');
1108 l_xml_string := l_xml_string || convert_into_xml('CS_NO_CFEMALE_EMPS', l_cs_no_cfemale_emps,'D');
1109 l_xml_string := l_xml_string || convert_into_xml('CS_NO_FFEMALE_EMPS', l_cs_no_ffemale_emps,'D');
1110 l_xml_string := l_xml_string || convert_into_xml('CS_NO_JFEMALE_EMPS', l_cs_no_jfemale_emps,'D');
1111 l_xml_string := l_xml_string || convert_into_xml('CS_NO_OFEMALE_EMPS', l_cs_no_ofemale_emps,'D');
1112
1113 l_xml_string := l_xml_string || convert_into_xml('CS_TOTAL_CATEGORY_EMPS', l_cs_total_category_emps,'D');
1114 l_xml_string := l_xml_string || '</G_EMPLOYMENT_CATEGORY>';
1115
1116 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1117
1118 END generate_ft_xml_data;
1119
1120 /**Procedure to generate the Other than full time employees table data **/
1121 PROCEDURE generate_oft_xml_data(p_function_code VARCHAR2) IS
1122
1123 l_function_code VARCHAR2(32767);
1124 l_job_category_code VARCHAR2(30);
1125 l_job_category_name VARCHAR2(80);
1126
1127 l_cs_no_wmale_emps NUMBER := 0;
1128 l_cs_no_hmale_emps NUMBER := 0;
1129 l_cs_no_cmale_emps NUMBER := 0;
1130 l_cs_no_fmale_emps NUMBER := 0;
1131 l_cs_no_jmale_emps NUMBER := 0;
1132 l_cs_no_omale_emps NUMBER := 0;
1133 l_cs_no_wfemale_emps NUMBER := 0;
1134 l_cs_no_hfemale_emps NUMBER := 0;
1135 l_cs_no_cfemale_emps NUMBER := 0;
1136 l_cs_no_ffemale_emps NUMBER := 0;
1137 l_cs_no_jfemale_emps NUMBER := 0;
1138 l_cs_no_ofemale_emps NUMBER := 0;
1139 l_cs_total_category_emps NUMBER := 0;
1140
1141 BEGIN
1142 IF g_for_all_emp = 'T' THEN
1143 l_function_code := 'AF';
1144 ELSIF g_for_all_emp = 'F' THEN
1145 l_function_code := p_function_code;
1146 END IF;
1147
1148 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1149 l_xml_string := l_xml_string || convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','2. OTHER THAN FULL-TIME EMPLOYEES (Including temporary employees)','D');
1150 l_xml_string := l_xml_string || '<LIST_G_JOB_CATEGORIES>';
1151
1152 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1153
1154 FOR counter in 1 .. other_ft_emp_table.count LOOP -- Fetch from other_ft_emp_table
1155 IF (ltrim(rtrim(other_ft_emp_table(counter).job_function)) = ltrim(rtrim(l_function_code)))
1156 THEN
1157 l_cs_no_wmale_emps := l_cs_no_wmale_emps + other_ft_emp_table(counter).no_cons_wmale_emps ;
1158 l_cs_no_hmale_emps := l_cs_no_hmale_emps + other_ft_emp_table(counter).no_cons_hmale_emps;
1159 l_cs_no_cmale_emps := l_cs_no_cmale_emps + other_ft_emp_table(counter).no_cons_cmale_emps;
1160 l_cs_no_fmale_emps := l_cs_no_fmale_emps + other_ft_emp_table(counter).no_cons_fmale_emps;
1161 l_cs_no_jmale_emps := l_cs_no_jmale_emps + other_ft_emp_table(counter).no_cons_jmale_emps;
1162 l_cs_no_omale_emps := l_cs_no_omale_emps + other_ft_emp_table(counter).no_cons_omale_emps;
1163
1164 l_cs_no_wfemale_emps := l_cs_no_wfemale_emps + other_ft_emp_table(counter).no_cons_wfemale_emps;
1165 l_cs_no_hfemale_emps := l_cs_no_hfemale_emps + other_ft_emp_table(counter).no_cons_hfemale_emps;
1166 l_cs_no_cfemale_emps := l_cs_no_cfemale_emps + other_ft_emp_table(counter).no_cons_cfemale_emps;
1167 l_cs_no_ffemale_emps := l_cs_no_ffemale_emps + other_ft_emp_table(counter).no_cons_ffemale_emps;
1168 l_cs_no_jfemale_emps := l_cs_no_jfemale_emps + other_ft_emp_table(counter).no_cons_jfemale_emps;
1169 l_cs_no_ofemale_emps := l_cs_no_ofemale_emps + other_ft_emp_table(counter).no_cons_ofemale_emps;
1170
1171 l_cs_total_category_emps := l_cs_total_category_emps + other_ft_emp_table(counter).cons_total_category_emps;
1172
1173 l_xml_string := '<G_JOB_CATEGORIES>';
1174 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_CODE',other_ft_emp_table(counter).lookup_code,'D');
1175 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');
1176 l_xml_string := l_xml_string || '<LIST_G_JOB_INFORMATION> <G_JOB_INFORMATION>';
1177
1178 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',other_ft_emp_table(counter).cons_total_category_emps,'D');
1179
1180 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',other_ft_emp_table(counter).no_cons_wmale_emps,'D');
1181 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',other_ft_emp_table(counter).no_cons_hmale_emps,'D');
1182 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_CMALE_EMPS',other_ft_emp_table(counter).no_cons_cmale_emps,'D');
1183 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_FMALE_EMPS',other_ft_emp_table(counter).no_cons_fmale_emps,'D');
1184 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_JMALE_EMPS',other_ft_emp_table(counter).no_cons_jmale_emps,'D');
1185 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_OMALE_EMPS',other_ft_emp_table(counter).no_cons_omale_emps,'D');
1186
1187 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',other_ft_emp_table(counter).no_cons_wfemale_emps,'D');
1188 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',other_ft_emp_table(counter).no_cons_hfemale_emps,'D');
1189 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_CFEMALE_EMPS',other_ft_emp_table(counter).no_cons_cfemale_emps,'D');
1190 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_FFEMALE_EMPS',other_ft_emp_table(counter).no_cons_ffemale_emps,'D');
1191 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_JFEMALE_EMPS',other_ft_emp_table(counter).no_cons_jfemale_emps,'D');
1192 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_OFEMALE_EMPS',other_ft_emp_table(counter).no_cons_ofemale_emps,'D');
1193
1194 l_xml_string := l_xml_string || '</G_JOB_INFORMATION></LIST_G_JOB_INFORMATION></G_JOB_CATEGORIES>';
1195
1196 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1197
1198 g_line_number := g_line_number + 1;--increment the line number
1199 END IF;
1200 END LOOP;--end of fetch from other_ft_emp_table
1201
1202 l_xml_string := '</LIST_G_JOB_CATEGORIES>';
1203 l_xml_string := l_xml_string || convert_into_xml('CF_TOTAL_TITLE',g_line_number||'. '||'Total Other Than Full Time (LINES 66 - 73)','D');
1204
1205 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WMALE_EMPS', l_cs_no_wmale_emps,'D');
1206 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HMALE_EMPS', l_cs_no_hmale_emps,'D');
1207 l_xml_string := l_xml_string || convert_into_xml('CS_NO_CMALE_EMPS', l_cs_no_cmale_emps,'D');
1208 l_xml_string := l_xml_string || convert_into_xml('CS_NO_FMALE_EMPS', l_cs_no_fmale_emps,'D');
1209 l_xml_string := l_xml_string || convert_into_xml('CS_NO_JMALE_EMPS', l_cs_no_jmale_emps,'D');
1210 l_xml_string := l_xml_string || convert_into_xml('CS_NO_OMALE_EMPS', l_cs_no_omale_emps,'D');
1211
1212 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WFEMALE_EMPS', l_cs_no_wfemale_emps,'D');
1213 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HFEMALE_EMPS', l_cs_no_hfemale_emps,'D');
1214 l_xml_string := l_xml_string || convert_into_xml('CS_NO_CFEMALE_EMPS', l_cs_no_cfemale_emps,'D');
1215 l_xml_string := l_xml_string || convert_into_xml('CS_NO_FFEMALE_EMPS', l_cs_no_ffemale_emps,'D');
1216 l_xml_string := l_xml_string || convert_into_xml('CS_NO_JFEMALE_EMPS', l_cs_no_jfemale_emps,'D');
1217 l_xml_string := l_xml_string || convert_into_xml('CS_NO_OFEMALE_EMPS', l_cs_no_ofemale_emps,'D');
1218
1219 l_xml_string := l_xml_string || convert_into_xml('CS_TOTAL_CATEGORY_EMPS', l_cs_total_category_emps,'D');
1220
1221 l_xml_string := l_xml_string || '</G_EMPLOYMENT_CATEGORY>';
1222
1223 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1224
1225 END generate_oft_xml_data;
1226
1227 /**procedure to generate new hire employee xml data**/
1228 PROCEDURE generate_nh_xml_data(p_function_code IN VARCHAR2) IS
1229
1230 l_job_category_code VARCHAR2(30);
1231 l_job_category_name VARCHAR2(80);
1232
1233 l_cs_no_wmale_emps NUMBER := 0;
1234 l_cs_no_hmale_emps NUMBER := 0;
1235 l_cs_no_cmale_emps NUMBER := 0;
1236 l_cs_no_fmale_emps NUMBER := 0;
1237 l_cs_no_jmale_emps NUMBER := 0;
1238 l_cs_no_omale_emps NUMBER := 0;
1239 l_cs_no_wfemale_emps NUMBER := 0;
1240 l_cs_no_hfemale_emps NUMBER := 0;
1241 l_cs_no_cfemale_emps NUMBER := 0;
1242 l_cs_no_ffemale_emps NUMBER := 0;
1243 l_cs_no_jfemale_emps NUMBER := 0;
1244 l_cs_no_ofemale_emps NUMBER := 0;
1245 l_cs_total_category_emps NUMBER := 0;
1246
1247 BEGIN
1248
1249 IF g_for_all_emp = 'T' THEN
1250 l_function_code := 'AF';
1251 ELSIF g_for_all_emp = 'F' THEN
1252 l_function_code := p_function_code;
1253 END IF;
1254
1255
1256 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1257 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');
1258 l_xml_string := l_xml_string || '<LIST_G_JOB_CATEGORIES>';
1259
1260 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1261
1262 FOR counter in 1 .. new_hire_table.count LOOP -- Fetch from new_hire_table
1263 IF (ltrim(rtrim(new_hire_table(counter).job_function)) = ltrim(rtrim(l_function_code)))
1264 THEN
1265
1266 l_cs_no_wmale_emps := l_cs_no_wmale_emps + new_hire_table(counter).no_cons_wmale_emps ;
1267 l_cs_no_hmale_emps := l_cs_no_hmale_emps + new_hire_table(counter).no_cons_hmale_emps;
1268 l_cs_no_cmale_emps := l_cs_no_cmale_emps + new_hire_table(counter).no_cons_cmale_emps;
1269 l_cs_no_fmale_emps := l_cs_no_fmale_emps + new_hire_table(counter).no_cons_fmale_emps;
1270 l_cs_no_jmale_emps := l_cs_no_jmale_emps + new_hire_table(counter).no_cons_jmale_emps;
1271 l_cs_no_omale_emps := l_cs_no_omale_emps + new_hire_table(counter).no_cons_omale_emps;
1272
1273 l_cs_no_wfemale_emps := l_cs_no_wfemale_emps + new_hire_table(counter).no_cons_wfemale_emps;
1274 l_cs_no_hfemale_emps := l_cs_no_hfemale_emps + new_hire_table(counter).no_cons_hfemale_emps;
1275 l_cs_no_cfemale_emps := l_cs_no_cfemale_emps + new_hire_table(counter).no_cons_cfemale_emps;
1276 l_cs_no_ffemale_emps := l_cs_no_ffemale_emps + new_hire_table(counter).no_cons_ffemale_emps;
1277 l_cs_no_jfemale_emps := l_cs_no_jfemale_emps + new_hire_table(counter).no_cons_jfemale_emps;
1278 l_cs_no_ofemale_emps := l_cs_no_ofemale_emps + new_hire_table(counter).no_cons_ofemale_emps;
1279
1280 l_cs_total_category_emps := l_cs_total_category_emps + new_hire_table(counter).cons_total_category_emps;
1281
1282 l_xml_string := '<G_JOB_CATEGORIES>';
1283 l_xml_string := l_xml_string || convert_into_xml('JOB_CATEGORY_CODE',new_hire_table(counter).lookup_code,'D');
1284 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');
1285 l_xml_string := l_xml_string || '<LIST_G_JOB_INFORMATION> <G_JOB_INFORMATION>';
1286
1287 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',new_hire_table(counter).cons_total_category_emps,'D');
1288
1289 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',new_hire_table(counter).no_cons_wmale_emps,'D');
1290 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',new_hire_table(counter).no_cons_hmale_emps,'D');
1291 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_CMALE_EMPS',new_hire_table(counter).no_cons_cmale_emps,'D');
1292 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_FMALE_EMPS',new_hire_table(counter).no_cons_fmale_emps,'D');
1293 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_JMALE_EMPS',new_hire_table(counter).no_cons_jmale_emps,'D');
1294 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_OMALE_EMPS',new_hire_table(counter).no_cons_omale_emps,'D');
1295
1296 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',new_hire_table(counter).no_cons_wfemale_emps,'D');
1297 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',new_hire_table(counter).no_cons_hfemale_emps,'D');
1298 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_CFEMALE_EMPS',new_hire_table(counter).no_cons_cfemale_emps,'D');
1299 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_FFEMALE_EMPS',new_hire_table(counter).no_cons_ffemale_emps,'D');
1300 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_JFEMALE_EMPS',new_hire_table(counter).no_cons_jfemale_emps,'D');
1301 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_OFEMALE_EMPS',new_hire_table(counter).no_cons_ofemale_emps,'D');
1302
1303 l_xml_string := l_xml_string || '</G_JOB_INFORMATION></LIST_G_JOB_INFORMATION></G_JOB_CATEGORIES>';
1304
1305 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1306
1307 g_line_number := g_line_number + 1; --increment the line number
1308
1309 END IF;
1310 END LOOP; --End of Fetch from new_hire_table
1311
1312 l_xml_string := '</LIST_G_JOB_CATEGORIES>';
1313 l_xml_string := l_xml_string || convert_into_xml('CF_TOTAL_TITLE',g_line_number||'. '||'Total New Hires (LINES 75 - 82)','D');
1314
1315 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WMALE_EMPS', l_cs_no_wmale_emps,'D');
1316 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HMALE_EMPS', l_cs_no_hmale_emps,'D');
1317 l_xml_string := l_xml_string || convert_into_xml('CS_NO_CMALE_EMPS', l_cs_no_cmale_emps,'D');
1318 l_xml_string := l_xml_string || convert_into_xml('CS_NO_FMALE_EMPS', l_cs_no_fmale_emps,'D');
1319 l_xml_string := l_xml_string || convert_into_xml('CS_NO_JMALE_EMPS', l_cs_no_jmale_emps,'D');
1320 l_xml_string := l_xml_string || convert_into_xml('CS_NO_OMALE_EMPS', l_cs_no_omale_emps,'D');
1321
1322 l_xml_string := l_xml_string || convert_into_xml('CS_NO_WFEMALE_EMPS', l_cs_no_wfemale_emps,'D');
1323 l_xml_string := l_xml_string || convert_into_xml('CS_NO_HFEMALE_EMPS', l_cs_no_hfemale_emps,'D');
1324 l_xml_string := l_xml_string || convert_into_xml('CS_NO_CFEMALE_EMPS', l_cs_no_cfemale_emps,'D');
1325 l_xml_string := l_xml_string || convert_into_xml('CS_NO_FFEMALE_EMPS', l_cs_no_ffemale_emps,'D');
1326 l_xml_string := l_xml_string || convert_into_xml('CS_NO_JFEMALE_EMPS', l_cs_no_jfemale_emps,'D');
1327 l_xml_string := l_xml_string || convert_into_xml('CS_NO_OFEMALE_EMPS', l_cs_no_ofemale_emps,'D');
1328
1329 l_xml_string := l_xml_string || convert_into_xml('CS_TOTAL_CATEGORY_EMPS', l_cs_total_category_emps,'D');
1330
1331 l_xml_string := l_xml_string || '</G_EMPLOYMENT_CATEGORY>';
1332
1333 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1334
1335 END generate_nh_xml_data;
1336
1337 /** procedure to create the xml for all 3 categories of employees **/
1338 PROCEDURE create_xml(p_current_function IN VARCHAR2) IS
1339 BEGIN
1340 l_xml_string := '<G_JOB_FUNCTION><JOB_FUNCTION_CODE>';
1341 l_xml_string := l_xml_string || g_function_numbers;
1342 l_xml_string := l_xml_string || '</JOB_FUNCTION_CODE><LIST_G_EMPLOYMENT_CATEGORY>';
1343
1344 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1345
1346 g_line_number := g_line_number + 1; -- increment the line number
1347
1348 -- Genarate full time employees xml data
1349 generate_ft_xml_data(p_current_function);
1350
1351 g_line_number := g_line_number + 1; -- increment the line number
1352
1353 -- Generate other than full time employees data
1354 generate_oft_xml_data(p_current_function);
1355
1356 g_line_number := g_line_number + 1; -- increment the line number
1357
1358 -- Generate new hires xml data
1359 generate_nh_xml_data(p_current_function);
1360
1361 l_xml_string := ' </LIST_G_EMPLOYMENT_CATEGORY>'
1362 || convert_into_xml('CF_SET_FUNCTION_DESC',g_func_desc,'D')
1363 || '</G_JOB_FUNCTION>';
1364
1365 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1366
1367 END create_xml;
1368
1369 /** Procedure to create the report **/
1370 PROCEDURE create_report(report_type NUMBER) IS
1371
1372 l_current_function VARCHAR2(30);
1373 l_function VARCHAR2(30);
1374 l_function_desc VARCHAR2(80);
1375
1376 BEGIN
1377 IF report_type = 1
1378 THEN
1379 l_xml_string := '<G_REPORT>';
1380 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1381
1382 g_function_numbers := '''X''';
1383
1384 FOR i IN 1 .. first_order_func_table.count
1385 LOOP
1386 l_current_function := first_order_func_table(i).job_function;
1387
1388 IF g_function_numbers = '''X'''
1389 THEN
1390 IF l_current_function = 'X'
1391 THEN
1392 g_function_numbers := 0;
1393 ELSE
1394 g_function_numbers := get_function_number(l_current_function);
1395 END IF;
1396 ELSE
1397 g_function_numbers := g_function_numbers || ', ' || get_function_number(l_current_function);
1398 END IF;
1399 END LOOP;
1400
1401 generate_juris_cert_xml_data();
1402
1403 l_xml_string := convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
1404 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
1405
1406 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1407
1408 FOR i IN 1 .. first_order_func_table.count
1409 LOOP
1410 l_current_function := first_order_func_table(i).job_function;
1411
1412 IF l_current_function <> 'X' THEN
1413 check_function(to_number(l_current_function));
1414 END IF;
1415
1416 END LOOP;
1417
1418 l_xml_string := '<LIST_G_JOB_FUNCTION>';
1419
1420 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1421
1422 g_line_number := 0;
1423
1424 g_for_all_emp := 'T';
1425 create_xml(g_job_code);
1426 g_for_all_emp := 'F';
1427
1428 l_xml_string := '</LIST_G_JOB_FUNCTION></G_REPORT>';
1429
1430 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1431
1432 ELSIF report_type = 2
1433 THEN
1434 FOR i IN 1 .. second_order_func_table.count LOOP -- For Each Function
1435
1436 l_current_function := second_order_func_table(i).job_function;
1437 g_func_desc := second_order_func_table(i).description;
1438 g_function_numbers := get_function_number(l_current_function);
1439
1440 l_xml_string := '<G_REPORT>';
1441
1442 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1443
1444 generate_juris_cert_xml_data();
1445
1446 l_xml_string := convert_into_xml('CONTROL_NUMBER',g_control_number,'D');
1447 l_xml_string := l_xml_string || convert_into_xml('FUNCTION_NUMBERS',g_function_numbers,'D');
1448
1449 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1450
1451 check_function(to_number(l_current_function));
1452
1453 l_xml_string := '<LIST_G_JOB_FUNCTION>';
1454
1455 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1456
1457 g_line_number := 0;
1458
1459 g_for_all_emp := 'F';
1460 create_xml(l_current_function);
1461
1462 l_xml_string := '</LIST_G_JOB_FUNCTION></G_REPORT>';
1463
1464 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
1465 END LOOP;--For each Function
1466 END IF;
1467 END create_report;
1468
1469 /** Procedure to generate the entire xml data **/
1470 PROCEDURE generate_xml_data(errbuf OUT NOCOPY VARCHAR2
1471 ,retcode OUT NOCOPY NUMBER
1472 ,p_reporting_year IN NUMBER
1473 ,p_add_message1 IN VARCHAR2
1474 ,p_add_message2 IN VARCHAR2
1475 ,p_add_message3 IN VARCHAR2
1476 ,p_add_message4 IN VARCHAR2
1477 ,p_add_message5 IN VARCHAR2
1478 ,p_add_message6 IN VARCHAR2
1479 ,p_add_message7 IN VARCHAR2
1480 ,p_business_group_id IN VARCHAR2
1481 ,p_full_time_emp_count IN NUMBER
1482 ,p_emp_count_for_function IN NUMBER
1483 ) IS
1484
1485
1486 l_ft_emp_count NUMBER;
1487
1488 l_fr VARCHAR2(2000);
1489 l_ft VARCHAR2(2000);
1490 l_pr VARCHAR2(2000);
1491 l_pt VARCHAR2(2000);
1492
1493 l_profile_option VARCHAR2(10);
1494
1495 l_function_name VARCHAR2(240);
1496 l_function_count NUMBER := 0;
1497 l_function_desc VARCHAR2(80);
1498 l_cur_function_desc VARCHAR2(260);
1499
1500 l_row_count NUMBER := 0;
1501 l_frc VARCHAR2(2000);
1502
1503 -- counters for PL/SQL tables
1504 l_counter NUMBER := 0;
1505 l_fo_funct_counter NUMBER := 0;
1506 l_so_funct_counter NUMBER := 0;
1507 l_funct_counter NUMBER := 0;
1508
1509 l_current_function VARCHAR2(30);
1510 l_cur_lookup_code VARCHAR2(30);
1511 l_prev_lookup_code VARCHAR2(30);
1512 l_function VARCHAR2(30);
1513
1514 l_start_salary NUMBER;
1515 l_end_salary NUMBER;
1516
1517 l_lookup_code VARCHAR2(30);
1518 l_meaning VARCHAR2(80);
1519 l_eeo4a_ethnic VARCHAR2(1000); --added for bug 14034790
1520
1521 l_report_day_month VARCHAR2(20) := '30-06';
1522 l_report_year VARCHAR2(4) := p_reporting_year;
1523 l_report_date VARCHAR2(20) := l_report_day_month || '-' || l_report_year;
1524
1525 CURSOR get_eeo4_lookup_details
1526 IS
1527 SELECT lookup_code, meaning, description
1528 FROM hr_lookups
1529 WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
1530 ORDER BY lookup_code;
1531
1532 CURSOR func_desc_cur(l_lookup_code VARCHAR)
1533 IS
1534 SELECT meaning
1535 FROM hr_lookups
1536 WHERE lookup_type = 'US_EEO4_JOB_FUNCTIONS'
1537 AND lookup_code = l_lookup_code;
1538
1539 BEGIN
1540 g_business_group_id := p_business_group_id;
1541
1542 g_select_clause :='count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
1543 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
1544 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''M'',1,null),null)) no_cons_cmale_emps,
1545 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''M'',1,null),null)) no_cons_fmale_emps,
1546 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),5,decode(peo.sex,''M'',1,null),null)) no_cons_jmale_emps,
1547 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''M'',1,null),null)) no_cons_omale_emps,
1548 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
1549 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
1550 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''F'',1,null),null)) no_cons_cfemale_emps,
1551 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''F'',1,null),null)) no_cons_ffemale_emps,
1552 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),5,decode(peo.sex,''F'',1,null),null)) no_cons_jfemale_emps,
1553 count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''F'',1,null),null)) no_cons_ofemale_emps ';
1554
1555 g_from_where_clause := 'FROM per_all_people_f peo,
1556 per_all_assignments_f ass,
1557 per_assignment_status_types past,
1558 per_pay_proposals ppp,
1559 per_jobs job,
1560 per_pay_bases ppb,
1561 hr_lookups hl
1562 WHERE peo.person_id = ass.person_id
1563 AND ass.pay_basis_id = ppb.pay_basis_id
1564 AND ass.assignment_id = ppp.assignment_id
1565 AND hl.lookup_code = job.job_information1
1566 AND job.job_information1 IS NOT NULL
1567 AND job.job_information_category = ''US''
1568 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
1569 AND ass.job_id = job.job_id
1570 AND ass.primary_flag = ''Y''
1571 AND ppp.change_date = ( SELECT MAX(change_date)
1572 FROM per_pay_proposals pro
1573 WHERE ppp.assignment_id = pro.assignment_id
1574 AND pro.change_date <= TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')
1575 AND pro.approved = ''Y'' )
1576 AND ass.organization_id IN (SELECT organization_id
1577 FROM hr_all_organization_units
1578 WHERE business_group_id = ' || p_business_group_id || '
1579 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN date_from AND NVL(date_to,TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')) )
1580 AND ass.assignment_status_type_id = past.assignment_status_type_id
1581 AND peo.current_employee_flag = ''Y''
1582 AND ass.assignment_type = ''E''
1583 AND (NOT EXISTS (SELECT ''X''
1584 FROM per_people_extra_info ppei
1585 WHERE ppei.person_id = peo.person_id
1586 AND ppei.information_type = ''US_ETHNIC_ORIGIN''
1587 AND(DECODE(ppei.pei_information1, ''Y'', 1,0)
1588 + DECODE(ppei.pei_information2, ''Y'', 1,0)
1589 + DECODE(ppei.pei_information3, ''Y'', 1,0)
1590 + DECODE(ppei.pei_information4, ''Y'', 1,0)
1591 + DECODE(ppei.pei_information5, ''Y'', 1,0)
1592 + DECODE(ppei.pei_information6, ''Y'', 1,0)
1593 ) >= 1
1594 )
1595 )';
1596
1597
1598 g_ft_effective_dates := ' AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN peo.effective_start_date AND peo.effective_end_date
1599 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN ass.effective_start_date AND ass.effective_end_date
1600 AND past.per_system_status <> ''TERM_ASSIGN''';
1601 /* Comment for bug 13849850 starts
1602 g_nh_effective_dates := ' AND TO_DATE(''' || l_report_date|| ''', ''DD-MM-YYYY'') BETWEEN peo.effective_start_date AND peo.effective_end_date
1603 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN ass.effective_start_date AND ass.effective_end_date
1604 AND pps.date_start BETWEEN (add_months(TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY''), -12) + 1)
1605 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') ';
1606 comment for bug 13849850 ends*/
1607
1608 /* bug#13849850:Modified the g_nh_effective clause to correct the count of new hires using exists*/
1609 g_nh_effective_dates := ' AND TO_DATE(''' || l_report_date|| ''', ''DD-MM-YYYY'') BETWEEN peo.effective_start_date AND peo.effective_end_date
1610 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN ass.effective_start_date AND ass.effective_end_date
1611 AND EXISTS (SELECT 1
1612 FROM per_periods_of_service pps
1613 WHERE pps.period_of_service_id = ass.period_of_service_id
1614 AND pps.person_id = peo.person_id
1615 AND pps.date_start BETWEEN (add_months(TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY''), -12) + 1)
1616 AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')) ';
1617
1618 -- added for bug 14034790
1619 l_eeo4a_ethnic := ' AND ( EXISTS (SELECT ''X''
1620 FROM per_people_extra_info ppei
1621 WHERE ppei.person_id = peo.person_id
1622 AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN''
1623 AND(DECODE(ppei.pei_information1, ''Y'', 1,0)
1624 + DECODE(ppei.pei_information2, ''Y'', 1,0)
1625 + DECODE(ppei.pei_information3, ''Y'', 1,0)
1626 + DECODE(ppei.pei_information4, ''Y'', 1,0)
1627 + DECODE(ppei.pei_information5, ''Y'', 1,0)
1628 + DECODE(ppei.pei_information6, ''Y'', 1,0)
1629 ) = 1
1630 )
1631 )';
1632
1633 -- Fetch the list of employment categories
1634 pqh_employment_category.fetch_empl_categories(p_business_group_id, l_fr, l_ft, l_pr, l_pt);
1635
1636 g_fp_regulars := replace(replace(l_fr,'''',''''),',',''',''');
1637 g_fp_temps := replace(replace(l_ft,'''',''''),',',''',''')||',' ||replace(replace(l_pt,'''',''''),',',''',''') ||',' ||replace(replace(l_pr,'''',''''),',',''',''');
1638
1639 /* Bug 13849850 :Modified l_query_text to include the employees belonging to custom defined
1640 Full Time Regular employment categories in full-time employees count */
1641 -- added l_eeo4a_ethnic for bug 14034790
1642 l_query_text := 'select count(1) l_ft_emp_count ' || g_from_where_clause || l_eeo4a_ethnic
1643 || g_ft_effective_dates || ' AND ass.employment_category in ('||g_fp_regulars||')';
1644
1645 source_cursor := dbms_sql.open_cursor;
1646 dbms_sql.parse(source_cursor,l_query_text,1);
1647 dbms_sql.define_column(source_cursor,1,l_ft_emp_count);
1648
1649 rows_processed := dbms_sql.EXECUTE(source_cursor);
1650
1651 IF dbms_sql.fetch_rows(source_cursor) > 0 THEN
1652 dbms_sql.column_value(source_cursor,1,l_ft_emp_count); --FR employee count
1653 END IF;
1654
1655 dbms_sql.close_cursor(source_cursor);
1656
1657 --count of no of employees in each FUNCTION category
1658 -- added l_eeo4a_ethnic for bug 14034790
1659 l_query_text := 'Select job.job_information7 l_function_code,
1660 count(1) l_function_count ' || g_from_where_clause || l_eeo4a_ethnic || g_ft_effective_dates || ' group by job.job_information7 ';
1661
1662 source_cursor := dbms_sql.open_cursor;
1663 dbms_sql.parse(source_cursor,l_query_text,2);
1664 dbms_sql.define_column_char(source_cursor,1,l_function_code,30);
1665 dbms_sql.define_column(source_cursor,2,l_function_count);
1666 rows_processed := dbms_sql.EXECUTE(source_cursor);
1667
1668 LOOP
1669 IF dbms_sql.fetch_rows(source_cursor) > 0
1670 THEN
1671
1672 dbms_sql.column_value_char(source_cursor,1,l_function_code);
1673 dbms_sql.column_value(source_cursor,2,l_function_count);
1674 l_function_code := RTRIM(l_function_code);
1675
1676 -- Fetch the function description
1677 IF l_function_code IS NOT NULL
1678 THEN
1679
1680 OPEN func_desc_cur(l_function_code);
1681 FETCH func_desc_cur INTO l_function_desc;
1682 CLOSE func_desc_cur;
1683
1684 IF (l_ft_emp_count >= p_full_time_emp_count AND l_row_count <= 14)
1685 THEN
1686
1687 g_dynamic_where := g_dynamic_where || ',' || l_function_code;
1688 l_so_funct_counter := l_so_funct_counter + 1;
1689 second_order_func_table(l_so_funct_counter).job_function := l_function_code;
1690 second_order_func_table(l_so_funct_counter).description := l_function_desc;
1691
1692 END IF;
1693
1694 IF (l_ft_emp_count < p_full_time_emp_count AND l_function_count >= p_emp_count_for_function)
1695 THEN
1696
1697 g_dynamic_where := g_dynamic_where || ',' || l_function_code;
1698 l_so_funct_counter := l_so_funct_counter + 1;
1699 second_order_func_table(l_so_funct_counter).job_function := l_function_code;
1700 second_order_func_table(l_so_funct_counter).description := l_function_desc;
1701
1702 ELSIF (l_ft_emp_count < p_full_time_emp_count AND l_function_count < p_emp_count_for_function)
1703 THEN
1704
1705 l_fo_funct_counter := l_fo_funct_counter + 1;
1706 first_order_func_table(l_fo_funct_counter).job_function := l_function_code;
1707 first_order_func_table(l_fo_funct_counter).description := l_function_desc;
1708
1709 END IF;
1710 END IF;
1711
1712 l_row_count := l_row_count + 1;
1713
1714 ELSE
1715 EXIT;
1716 END IF; --END of if the fetch_rows>0
1717 END LOOP;--end of loop for fetching the function code and description
1718
1719 dbms_sql.close_cursor(source_cursor);
1720
1721 IF l_fo_funct_counter = 0 AND l_so_funct_counter = 0
1722 THEN
1723 l_fo_funct_counter := l_fo_funct_counter + 1;
1724 first_order_func_table(l_fo_funct_counter).job_function := 'X';
1725 first_order_func_table(l_fo_funct_counter).description := 'X';
1726 END IF;
1727
1728 l_current_function := ' ';
1729
1730 g_job_code := '''X''';
1731 g_func_desc := '''X''';
1732
1733 FOR i IN 1 .. first_order_func_table.count LOOP -- For Each Function
1734
1735 l_function := first_order_func_table(i).job_function;
1736 l_function_desc := first_order_func_table(i).description;
1737
1738 IF l_function <> l_current_function
1739 THEN
1740
1741 l_current_function := l_function;
1742
1743 IF g_job_code = '''X'''
1744 THEN
1745 g_job_code := ''''|| l_current_function ||'''';
1746 g_func_desc := l_function_desc;
1747 ELSE
1748 g_job_code := g_job_code|| ', ''' ||l_current_function||'''';
1749 g_func_desc := g_func_desc|| ' , ' ||l_function_desc;
1750 END IF;
1751
1752 END IF;-- END of if l_function <> l_current_function
1753 END LOOP;-- For Each Function
1754
1755
1756 g_for_all_emp := 'T';
1757
1758 populate_ft_emp_data(g_job_code);
1759 populate_oft_emp_data(g_job_code);
1760 populate_nh_emp_data(g_job_code);
1761
1762 g_for_all_emp := 'F';
1763
1764 FOR i IN 1 .. second_order_func_table.count LOOP
1765 l_function := second_order_func_table(i).job_function;
1766 l_function_desc := second_order_func_table(i).description;
1767
1768 IF l_function <> l_current_function
1769 THEN
1770 l_current_function := l_function;
1771 l_funct_counter := l_funct_counter + 1;
1772
1773 populate_ft_emp_data(l_current_function);
1774 populate_oft_emp_data(l_current_function);
1775 populate_nh_emp_data(l_current_function);
1776
1777 END IF;
1778 END LOOP;
1779
1780 generate_header_xml_data();
1781
1782 IF first_order_func_table.count <> 0
1783 THEN
1784 create_report(1);
1785 END IF;
1786
1787 create_report(2);
1788 generate_footer_xml_data();
1789
1790 END generate_xml_data;
1791
1792 /** Procedure to write to concurrent output**/
1793 PROCEDURE write_to_concurrent_out (p_text VARCHAR2) IS
1794 BEGIN
1795
1796 fnd_file.put_line(fnd_file.OUTPUT, p_text);
1797
1798 END write_to_concurrent_out;
1799
1800 END PER_US_EEO4A_PKG;
1801