[Home] [Help]
PACKAGE BODY: APPS.PER_US_EEO5_PKG
Source
1 PACKAGE BODY per_us_eeo5_pkg AS
2 /* $Header: peuseeo5.pkb 120.6.12000000.3 2007/07/18 11:46:39 rpasumar noship $ */
3
4 g_debug_flag VARCHAR2(1) := 'Y';
5 g_concurrent_flag VARCHAR2(1) := 'Y';
6 g_salary_range VARCHAR2(30);
7 g_start_salary NUMBER := 0;
8 g_end_salary NUMBER := 0;
9 g_lookup_code NUMBER := 1;
10 g_meaning VARCHAR2(80);
11 g_debug BOOLEAN;
12
13 /****************************************************************************
14 Name : HR_UTILITY_TRACE
15 Description : This function prints debug messages during diagnostics mode.
16 *****************************************************************************/
17 PROCEDURE hr_utility_trace(trc_data VARCHAR2) IS
18 BEGIN
19 IF g_debug THEN
20 hr_utility.trace(trc_data);
21 END IF;
22 END hr_utility_trace;
23
24 /*****************************************************************************
25 Name : convert_into_xml
26 Purpose : function to convert the data into an XML String
27 *****************************************************************************/
28 FUNCTION convert_into_xml( p_name IN VARCHAR2,
29 p_value IN VARCHAR2,
30 p_type IN char)
31 RETURN VARCHAR2 IS
32 l_convert_data VARCHAR2(300);
33 BEGIN
34 IF p_type = 'D' THEN
35 l_convert_data := '<'||p_name||'>'||p_value||'</'||p_name||'>';
36 ELSE
37 l_convert_data := '<'||p_name||'>';
38 END IF;
39 RETURN(l_convert_data);
40 END convert_into_xml;
41
42 /*****************************************************************************
43 Name : get_job_category_meaning
44 Purpose : function to fetch the job category meaning based on lookup_code
45 *****************************************************************************/
46 FUNCTION get_job_category_meaning( p_lookup_code IN NUMBER)
47 RETURN VARCHAR2 IS
48 BEGIN
49 IF p_lookup_code = 1 THEN
50 RETURN '1. Officials, Administrators, Managers';
51 ELSIF p_lookup_code = 2 THEN
52 RETURN '2. Principals';
53 ELSIF p_lookup_code = 3 THEN
54 RETURN '3. Assistant Principal, Teaching';
55 ELSIF p_lookup_code = 4 THEN
56 RETURN '4. Assistant Principals, Non teaching';
57 ELSIF p_lookup_code = 5 THEN
58 RETURN '5. Elementary Classroom Teachers';
59 ELSIF p_lookup_code = 6 THEN
60 RETURN '6. Secondary classroom Teachers';
61 ELSIF p_lookup_code = 7 THEN
62 RETURN '7. Other Classroom Teachers';
63 ELSIF p_lookup_code = 8 THEN
64 RETURN '8. Guidance';
65 ELSIF p_lookup_code = 9 THEN
66 RETURN '9. Psychological';
67 ELSIF p_lookup_code = 10 THEN
68 RETURN '10. Librarians /Audio Visual Staff';
69 ELSIF p_lookup_code = 11 THEN
70 RETURN '11. Consultants & Supervisors of Instruction';
71 ELSIF p_lookup_code = 12 THEN
72 RETURN '12. Other Professional Staff';
73 ELSIF p_lookup_code = 13 THEN
74 RETURN '13. Teachers Aids';
75 ELSIF p_lookup_code = 14 THEN
76 RETURN '14. Technicians';
77 ELSIF p_lookup_code = 15 THEN
78 RETURN '15. Clerical/Secretarial Staff';
79 ELSIF p_lookup_code = 16 THEN
80 RETURN '16. Service Workers';
81 ELSIF p_lookup_code = 17 THEN
82 RETURN '17. Skilled Crafts';
83 ELSIF p_lookup_code = 18 THEN
84 RETURN '18. Laborers, Unskilled';
85 ELSIF p_lookup_code = 19 THEN
86 RETURN '19. TOTAL (1-18)';
87 ELSIF p_lookup_code = 20 THEN
88 RETURN '20. Professional Instructional';
89 ELSIF p_lookup_code = 21 THEN
90 RETURN '21. All Other';
91 ELSIF p_lookup_code = 22 THEN
92 RETURN '22. TOTAL (20-21)';
93 ELSIF p_lookup_code = 23 THEN
94 RETURN '23. Officials, Administrators, Managers';
95 ELSIF p_lookup_code = 24 THEN
96 RETURN '24. Principals/Asst. Principals';
97 ELSIF p_lookup_code = 25 THEN
98 RETURN '25. Classroom Teachers';
99 ELSIF p_lookup_code = 26 THEN
100 RETURN '26. Other Professional Staff';
101 ELSIF p_lookup_code = 27 THEN
102 RETURN '27. Nonprofessional Staff';
103 ELSIF p_lookup_code = 28 THEN
104 RETURN '28. TOTAL (23-27)';
105 ELSE
106 RETURN NULL;
107 END IF;
108 END get_job_category_meaning;
109
110 /*****************************************************************************
111 Name : get_sum
112 Purpose : function to sum of the employees.
113 *****************************************************************************/
114 FUNCTION get_sum(p_no_cons_wmale_emps IN NUMBER
115 ,p_no_cons_bmale_emps IN NUMBER
116 ,p_no_cons_hmale_emps IN NUMBER
117 ,p_no_cons_amale_emps IN NUMBER
118 ,p_no_cons_imale_emps IN NUMBER
119 ,p_no_cons_wfemale_emps IN NUMBER
120 ,p_no_cons_bfemale_emps IN NUMBER
121 ,p_no_cons_hfemale_emps IN NUMBER
122 ,p_no_cons_afemale_emps IN NUMBER
123 ,p_no_cons_ifemale_emps IN NUMBER) RETURN NUMBER IS
124 BEGIN
125 RETURN NVL(p_no_cons_wmale_emps,0)
126 + NVL(p_no_cons_bmale_emps,0)
127 + NVL(p_no_cons_hmale_emps,0)
128 + NVL(p_no_cons_amale_emps,0)
129 + NVL(p_no_cons_imale_emps,0)
130 + NVL(p_no_cons_wfemale_emps,0)
131 + NVL(p_no_cons_bfemale_emps,0)
132 + NVL(p_no_cons_hfemale_emps,0)
133 + NVL(p_no_cons_afemale_emps,0)
134 + NVL(p_no_cons_ifemale_emps,0);
135 END get_sum;
136
137 /*****************************************************************************
138 Name : write_to_concurrent_out
139 Purpose : writes to concurrent ouput.
140 *****************************************************************************/
141 PROCEDURE write_to_concurrent_out (p_text VARCHAR2) IS
142 BEGIN
143 -- Write to the concurrent request log
144 fnd_file.put_line(fnd_file.LOG, p_text);
145 -- Write to the concurrent request out
146 fnd_file.put_line(fnd_file.OUTPUT, p_text);
147 -- hr_utility_trace(p_text);
148 END write_to_concurrent_out;
149
150 /*****************************************************************************
151 Name : generate_xml_data
152 Purpose : Procedure is called from concurrent program EEO5 Reporting.
153 Structure :
154 <PQHEEO5>
155 -------------------------------------------------------------------------------
156 <LIST_G_JURISDICTION_DETAIL>
157 <G_JURISDICTION_DETAIL>
158 <CITY_STATE_ZIP>San Francisco,San Francisco,CA,94100-1234</CITY_STATE_ZIP>
159 <BUSINESS_NAME>MM_RT BG2</BUSINESS_NAME>
160 <ADDRESS>314 Maple Street Suite 1000</ADDRESS>
161 </G_JURISDICTION_DETAIL>
162 </LIST_G_JURISDICTION_DETAIL>
163 ------------------------------------------------------------------------------
164 <LIST_G_CERT_OFFICER_NAME>
165 <G_CERT_OFFICER_NAME>
166 <SYSTEM_DISTRICT>DISTRICT</SYSTEM_DISTRICT>
167 <TYPE_REPORT>DISTRICT SUMMARY</TYPE_REPORT>
168 <CONTROL_NUMBER>1234567890</CONTROL_NUMBER>
169 <CERT_OFFICER_NAME></CERT_OFFICER_NAME>
170 <CERT_OFFICIAL_TITLE></CERT_OFFICIAL_TITLE>
171 <CONTACT_TELEPHONE></CONTACT_TELEPHONE>
172 </G_CERT_OFFICER_NAME>
173 </LIST_G_CERT_OFFICER_NAME>
174 ------------------------------------------------------------------------------
175 <LIST_G_EMPLOYMENT_CATEGORY>
176 <G_EMPLOYMENT_CATEGORY>
177 <EMPLOYEE_SALARY_EMPLOYMENT_CAT>A. FULL TIME STAFF</EMPLOYEE_SALARY_EMPLOYMENT_CAT>
178 <LIST_G_JOB_CATEGORIES>
179 <G_JOB_CATEGORIES>
180 <JOB_CATEGORY_MEANING>2.PRIN</JOB_CATEGORY_MEANING>
181 <LIST_G_JOB_INFORMATION>
182 <G_JOB_INFORMATION>
183 <CONS_TOTAL_CATEGORY_EMPS>2</CONS_TOTAL_CATEGORY_EMPS>
184 <NO_CONS_WMALE_EMPS>1</NO_CONS_WMALE_EMPS>
185 <NO_CONS_BMALE_EMPS>0</NO_CONS_BMALE_EMPS>
186 <NO_CONS_HMALE_EMPS>0</NO_CONS_HMALE_EMPS>
187 <NO_CONS_AMALE_EMPS>0</NO_CONS_AMALE_EMPS>
188 <NO_CONS_IMALE_EMPS>0</NO_CONS_IMALE_EMPS>
189 <NO_CONS_WFEMALE_EMPS>1</NO_CONS_WFEMALE_EMPS>
190 <NO_CONS_BFEMALE_EMPS>0</NO_CONS_BFEMALE_EMPS>
191 <NO_CONS_HFEMALE_EMPS>0</NO_CONS_HFEMALE_EMPS>
192 <NO_CONS_AFEMALE_EMPS>0</NO_CONS_AFEMALE_EMPS>
193 <NO_CONS_IFEMALE_EMPS>0</NO_CONS_IFEMALE_EMPS>
194 </G_JOB_INFORMATION>
195 </LIST_G_JOB_INFORMATION>
196 </G_JOB_CATEGORIES>
197 </LIST_G_JOB_CATEGORIES>
198 </G_EMPLOYMENT_CATEGORY>
199 <G_EMPLOYMENT_CATEGORY>
200 <EMPLOYEE_SALARY_EMPLOYMENT_CAT>B. PART-TIME STAFF</EMPLOYEE_SALARY_EMPLOYMENT_CAT>
201 </LIST_G_EMPLOYMENT_CATEGORY>
202 ------------------------------------------------------------------------------
203 <CF_NO_OF_ANNEXES>3</CF_NO_OF_ANNEXES>
204 <CP_REPORT_DATE>30-SEP-05</CP_REPORT_DATE>
205 <CP_NO_OF_SCHOOLS>6</CP_NO_OF_SCHOOLS>
206 <CP_FR>'FR'</CP_FR>
207 <CP_FT>'FT'</CP_FT>
208 <CP_PR>'PR'</CP_PR>
209 <CP_PT>'PT'</CP_PT>
210 </PQHEEO5>
211 *****************************************************************************/
212 PROCEDURE generate_xml_data(errbuf OUT NOCOPY VARCHAR2
213 ,retcode OUT NOCOPY NUMBER
214 ,p_reporting_year IN NUMBER
215 ,p_type_agency IN VARCHAR2
216 ,p_total_enrollments IN NUMBER
217 ,p_business_group_id IN NUMBER
218 ) IS
219
220 l_xml_string VARCHAR2(32767);
221
222 --Step 1
223 CURSOR csr_bg_details(p_business_group_id IN NUMBER) IS
224 SELECT name bg_name,
225 location_id
226 FROM hr_all_organization_units
227 WHERE business_group_id = p_business_group_id
228 AND organization_id = p_business_group_id;
229
230 l_bg_name VARCHAR2(200);
231 l_location_id NUMBER;
232
233 CURSOR csr_bg_location(l_location_id IN NUMBER) IS
234 SELECT address_line_1||' '||address_line_2||' '||address_line_3 bg_address,
235 town_or_city city,
236 region_1 county,
237 region_2 state,
238 postal_code zip_code
239 FROM hr_locations
240 WHERE location_id = l_location_id;
241
242 l_bg_address VARCHAR2(2000);
243 l_bg_city VARCHAR2(2000);
244 l_bg_county VARCHAR2(2000);
245 l_bg_state VARCHAR2(2000);
246 l_bg_zip_code VARCHAR2(2000);
247
248
249 --Step 2
250 CURSOR csr_cert_officer_details(p_business_group_id IN NUMBER) IS
251 SELECT org_information1 cert_officer_name,
252 org_information2 cert_official_title,
253 org_information10 contact_telephone,
254 org_information12 control_number,
255 org_information13 system_district,
256 org_information13||' SUMMARY' type_report
257 FROM hr_organization_information
258 WHERE org_information_context = 'EEO_REPORT'
259 AND organization_id = p_business_group_id;
260
261 --Modified for bug 5437066
262 l_fr VARCHAR2(2000);
263 l_ft VARCHAR2(2000);
264 l_pr VARCHAR2(2000);
265 l_pt VARCHAR2(2000);
266
267 --Step 3
268 CURSOR csr_full_time_details(p_business_group_id IN NUMBER
269 ,p_fr IN VARCHAR2
270 ,p_ft IN VARCHAR2
271 ,p_pr IN VARCHAR2
272 ,p_pt IN VARCHAR2
273 ,p_report_date IN DATE
274 ,p_report_year IN NUMBER) IS
275 SELECT DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
276 'FR', 'A. FULL-TIME STAFF') employment_category,
277 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
278 'FR',LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning) job_category_name,
279 COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) cons_total_category_emps,
280 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
281 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
282 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
283 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'M',1,NULL),'5',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
284 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'M',1,NULL),'7',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
285 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
286 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
287 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
288 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'F',1,NULL),'5',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
289 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'F',1,NULL),'7',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
290 FROM per_all_people_f peo,
291 per_all_assignments_f ass,
292 per_assignment_status_types ast,
293 per_jobs job,
294 hr_lookups hl
295 WHERE peo.person_id = ass.person_id
296 AND peo.current_employee_flag = 'Y'
297 AND hl.lookup_code = job.job_information1
298 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
299 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
300 AND job.job_information_category = 'US'
301 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
302 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
303 AND ass.primary_flag = 'Y'
304 AND ass.assignment_status_type_id = ast.assignment_status_type_id
305 AND ast.per_system_status <> 'TERM_ASSIGN'
306 AND ass.job_id = job.job_id
307 AND ass.assignment_type = 'E'
308 AND ass.organization_id IN (
309 SELECT organization_id
310 FROM hr_all_organization_units
311 WHERE business_group_id = p_business_group_id)
312 GROUP BY
313 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
314 'FR', 'A. FULL-TIME STAFF') ,
315 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
316 'FR',LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning)
317 ORDER BY 1,2;
318
319
320 --Step 4
321 CURSOR csr_part_time_details(p_business_group_id IN NUMBER
322 ,p_fr IN VARCHAR2
323 ,p_ft IN VARCHAR2
324 ,p_pr IN VARCHAR2
325 ,p_pt IN VARCHAR2
326 ,p_report_date IN DATE
327 ,p_report_year IN NUMBER) IS
328 SELECT DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
329 'FT','B. PART-TIME STAFF',
330 'PR','B. PART-TIME STAFF',
331 'PT','B. PART-TIME STAFF') employment_category,
332 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
333 'PR',DECODE(job.job_information1,
334 '2','20.PROF. INSTR.',
335 '3','20.PROF. INSTR.',
336 '4','20.PROF. INSTR.',
337 '5','20.PROF. INSTR.',
338 '6','20.PROF. INSTR.',
339 '7','20.PROF. INSTR.',
340 '8','20.PROF. INSTR.',
341 '9','20.PROF. INSTR.',
342 '10','20.PROF. INSTR.',
343 '11','20.PROF. INSTR.',
344 '12','20.PROF. INSTR.',
345 '21.ALL OTHER')) job_category_name,
346 COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) cons_total_category_emps,
347 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
348 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
349 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
350 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'M',1,NULL),'5',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
351 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'M',1,NULL),'7',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
355 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'F',1,NULL),'5',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
352 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
353 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
354 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
356 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'F',1,NULL),'7',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
357 FROM per_all_people_f peo,
358 per_all_assignments_f ass,
359 per_assignment_status_types ast,
360 per_jobs job,
361 hr_lookups hl
362 WHERE peo.person_id = ass.person_id
363 AND peo.current_employee_flag = 'Y'
364 AND hl.lookup_code = job.job_information1
365 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('PR')
366 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
367 AND job.job_information_category = 'US'
368 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
369 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
370 AND ass.primary_flag = 'Y'
371 AND ass.assignment_status_type_id = ast.assignment_status_type_id
372 AND ast.per_system_status <> 'TERM_ASSIGN'
373 AND ass.job_id = job.job_id
374 AND ass.assignment_type = 'E'
375 AND ass.organization_id IN (
376 SELECT organization_id
377 FROM hr_all_organization_units
378 WHERE business_group_id = p_business_group_id)
379 GROUP BY
380 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
381 'FT','B. PART-TIME STAFF',
382 'PR','B. PART-TIME STAFF',
383 'PT','B. PART-TIME STAFF') ,
384 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
385 'PR', DECODE(job.job_information1,
386 '2','20.PROF. INSTR.', '3','20.PROF. INSTR.',
387 '4','20.PROF. INSTR.', '5','20.PROF. INSTR.',
388 '6','20.PROF. INSTR.', '7','20.PROF. INSTR.',
389 '8','20.PROF. INSTR.', '9','20.PROF. INSTR.',
390 '10','20.PROF. INSTR.', '11','20.PROF. INSTR.',
391 '12','20.PROF. INSTR.', '21.ALL OTHER'))
392 ORDER BY 1,2;
393
394
395 --Step 5
396 CURSOR csr_new_hires_details(p_business_group_id IN NUMBER
397 ,p_fr IN VARCHAR2
398 ,p_ft IN VARCHAR2
399 ,p_pr IN VARCHAR2
400 ,p_pt IN VARCHAR2
401 ,p_report_date IN DATE
402 ,p_report_year IN NUMBER) IS
403 SELECT 'C. NEW HIRES (JULY THRU SEPT. '||p_report_year||')' employment_category,
404 DECODE(job.job_information1,
405 '1','23.0/A/M', '2','24.PRIN/ASST.PR',
406 '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR',
407 '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS',
408 '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.',
409 '9','26.OTHER PROF.', '10','26.OTHER PROF.',
410 '11','26.OTHER PROF.', '12','26.OTHER PROF.',
411 '13','27.NONPROF.', '14','27.NONPROF.',
412 '15','27.NONPROF.', '16','27.NONPROF.',
413 '17','27.NONPROF.', '18','27.NONPROF.') job_category_name,
414 COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) cons_total_category_emps,
415 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
416 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
417 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
418 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'M',1,NULL),'5',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
419 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'M',1,NULL),'7',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
420 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
421 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
422 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
423 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'F',1,NULL),'5',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
424 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'F',1,NULL),'7',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
425 FROM per_all_people_f peo,
426 per_all_assignments_f ass,
427 per_jobs job,
428 hr_lookups hl
429 WHERE peo.person_id = ass.person_id
430 AND peo.current_employee_flag = 'Y'
431 AND hl.lookup_code = job.job_information1
432 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
433 AND job.job_information_category = 'US'
434 AND ass.job_id = job.job_id
435 AND ass.assignment_type = 'E'
436 AND (SELECT date_start
437 FROM per_periods_of_service
438 WHERE period_of_service_id = ass.period_of_service_id)
439 BETWEEN ADD_MONTHS(p_report_date,-3) +1 AND p_report_date
440 AND ass.primary_flag = 'Y'
441 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) = 'FR' -- Only full-time regular no temporaries
442 AND ass.organization_id IN (
446 GROUP BY DECODE(job.job_information1,
443 SELECT organization_id
444 FROM hr_all_organization_units
445 WHERE business_group_id = p_business_group_id )
447 '1','23.0/A/M', '2','24.PRIN/ASST.PR',
448 '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR',
449 '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS',
450 '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.',
451 '9','26.OTHER PROF.', '10','26.OTHER PROF.',
452 '11','26.OTHER PROF.', '12','26.OTHER PROF.',
453 '13','27.NONPROF.', '14','27.NONPROF.',
454 '15','27.NONPROF.', '16','27.NONPROF.',
455 '17','27.NONPROF.', '18','27.NONPROF.')
456 ORDER BY 1,2;
457
458 CURSOR csr_tmr_ft_details(p_business_group_id IN NUMBER
459 ,p_fr IN VARCHAR2
460 ,p_ft IN VARCHAR2
461 ,p_pr IN VARCHAR2
462 ,p_pt IN VARCHAR2
463 ,p_employment_category IN VARCHAR2
464 ,p_job_category_name IN VARCHAR2
465 ,p_report_date IN DATE
466 ,p_report_year IN NUMBER) IS
467 SELECT count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
468 count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
469 count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
470 count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
471 count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
472 count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
473 count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
474 count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
475 count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
476 count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
477 FROM per_all_people_f peo,
478 per_all_assignments_f ass,
479 per_assignment_status_types ast,
480 per_jobs job,
481 hr_lookups hl,
482 per_people_extra_info pei
483 WHERE peo.person_id = ass.person_id
484 AND peo.per_information1 = '13'
485 AND peo.person_id = pei.person_id(+)
486 AND pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
487 AND pei.pei_information5 is not null
488 AND peo.current_employee_flag = 'Y'
489 AND hl.lookup_code = job.job_information1
490 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
491 AND job.job_information_category = 'US'
492 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
493 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
494 AND ass.primary_flag = 'Y'
495 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
496 AND ass.assignment_status_type_id = ast.assignment_status_type_id
497 AND ast.per_system_status <> 'TERM_ASSIGN'
498 AND ass.job_id = job.job_id
499 AND ass.assignment_type = 'E'
500 AND ass.organization_id IN (
501 SELECT organization_id
502 FROM hr_all_organization_units
503 WHERE business_group_id = p_business_group_id)
504 AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),'FR', 'A. FULL-TIME STAFF') = p_employment_category
505 AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),'FR',LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning) = p_job_category_name;
506
507
508
509 --Step 4
510 CURSOR csr_tmr_pt_details(p_business_group_id IN NUMBER
511 ,p_fr IN VARCHAR2
512 ,p_ft IN VARCHAR2
513 ,p_pr IN VARCHAR2
514 ,p_pt IN VARCHAR2
515 ,p_employment_category IN VARCHAR2
516 ,p_job_category_name IN VARCHAR2
517 ,p_report_date IN DATE
518 ,p_report_year IN NUMBER) IS
519 SELECT count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
520 count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
521 count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
522 count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
523 count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
524 count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
528 count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
525 count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
526 count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
527 count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
529 FROM per_all_people_f peo,
530 per_all_assignments_f ass,
531 per_assignment_status_types ast,
532 per_jobs job,
533 hr_lookups hl,
534 per_people_extra_info pei
535 WHERE peo.person_id = ass.person_id
536 AND peo.per_information1 = '13'
537 AND peo.person_id = pei.person_id(+)
538 AND pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
539 AND pei.pei_information5 is not null
540 AND peo.current_employee_flag = 'Y'
541 AND hl.lookup_code = job.job_information1
542 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
543 AND job.job_information_category = 'US'
544 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
545 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
546 AND ass.primary_flag = 'Y'
547 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('PR')
548 AND ass.assignment_status_type_id = ast.assignment_status_type_id
549 AND ast.per_system_status <> 'TERM_ASSIGN'
550 AND ass.job_id = job.job_id
551 AND ass.assignment_type = 'E'
552 AND ass.organization_id IN (
553 SELECT organization_id
554 FROM hr_all_organization_units
555 WHERE business_group_id = p_business_group_id)
556 AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
557 'FT','B. PART-TIME STAFF',
558 'PR','B. PART-TIME STAFF',
559 'PT','B. PART-TIME STAFF') = p_employment_category
560 AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
561 'PR',DECODE(job.job_information1,
562 '2','20.PROF. INSTR.',
563 '3','20.PROF. INSTR.',
564 '4','20.PROF. INSTR.',
565 '5','20.PROF. INSTR.',
566 '6','20.PROF. INSTR.',
567 '7','20.PROF. INSTR.',
568 '8','20.PROF. INSTR.',
569 '9','20.PROF. INSTR.',
570 '10','20.PROF. INSTR.',
571 '11','20.PROF. INSTR.',
572 '12','20.PROF. INSTR.',
573 '21.ALL OTHER')) = p_job_category_name;
574
575
576 --Step 5
577 CURSOR csr_tmr_nh_details(p_business_group_id IN NUMBER
578 ,p_fr IN VARCHAR2
579 ,p_ft IN VARCHAR2
580 ,p_pr IN VARCHAR2
581 ,p_pt IN VARCHAR2
582 ,p_employment_category IN VARCHAR2
583 ,p_job_category_name IN VARCHAR2
584 ,p_report_date IN DATE
585 ,p_report_year IN NUMBER) IS
586 SELECT count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
587 count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
588 count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
589 count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
590 count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
591 count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
592 count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
593 count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
594 count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
595 count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
596 FROM per_all_people_f peo,
597 per_all_assignments_f ass,
598 per_jobs job,
599 hr_lookups hl,
600 per_people_extra_info pei
601 WHERE peo.person_id = ass.person_id
602 AND peo.per_information1 = '13'
603 AND peo.person_id = pei.person_id(+)
604 AND pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
605 AND pei.pei_information5 is not null
606 AND peo.current_employee_flag = 'Y'
607 AND hl.lookup_code = job.job_information1
608 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
609 AND job.job_information_category = 'US'
610 AND ass.job_id = job.job_id
611 AND ass.assignment_type = 'E'
612 AND (SELECT date_start
613 FROM per_periods_of_service
617 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
614 WHERE period_of_service_id = ass.period_of_service_id)
615 BETWEEN ADD_MONTHS(p_report_date,-3) +1 AND p_report_date
616 AND ass.primary_flag = 'Y'
618 AND ass.organization_id IN (
619 SELECT organization_id
620 FROM hr_all_organization_units
621 WHERE business_group_id = p_business_group_id )
622 and 'C. NEW HIRES (JULY THRU SEPT. '||p_report_year||')' = p_employment_category
623 and DECODE(job.job_information1,
624 '1','23.0/A/M', '2','24.PRIN/ASST.PR',
625 '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR',
626 '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS',
627 '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.',
628 '9','26.OTHER PROF.', '10','26.OTHER PROF.',
629 '11','26.OTHER PROF.', '12','26.OTHER PROF.',
630 '13','27.NONPROF.', '14','27.NONPROF.',
631 '15','27.NONPROF.', '16','27.NONPROF.',
632 '17','27.NONPROF.', '18','27.NONPROF.') = p_job_category_name;
633
634
635 l_start_code NUMBER := 1;
636 l_end_code NUMBER := 1;
637 l_current_code NUMBER := 1;
638 l_sum_cons_total_category_emps NUMBER := 0;
639 l_sum_no_cons_wmale_emps NUMBER := 0;
640 l_sum_no_cons_bmale_emps NUMBER := 0;
641 l_sum_no_cons_hmale_emps NUMBER := 0;
642 l_sum_no_cons_amale_emps NUMBER := 0;
643 l_sum_no_cons_imale_emps NUMBER := 0;
644 l_sum_no_cons_wfemale_emps NUMBER := 0;
645 l_sum_no_cons_bfemale_emps NUMBER := 0;
646 l_sum_no_cons_hfemale_emps NUMBER := 0;
647 l_sum_no_cons_afemale_emps NUMBER := 0;
648 l_sum_no_cons_ifemale_emps NUMBER := 0;
649 l_total_category_employees NUMBER := 0;
650 l_report_date DATE;
651 l_job_category_name_main VARCHAR2(200);
652 l_new_hires_heading VARCHAR2(200);
653
654
655 --Final Step
656 CURSOR csr_annexes(p_report_date IN DATE,p_business_group_id IN NUMBER) IS
657 SELECT COUNT(1)
658 FROM (
659 SELECT ass.location_id
660 FROM per_all_assignments_f ass,
661 hr_organization_units_v hou,
662 per_all_people_f peo
663 WHERE ass.organization_id = hou.organization_id
664 AND ass.person_id = peo.person_id
665 AND p_report_date between ass.effective_start_date and ass.effective_end_date
666 AND p_report_date between peo.effective_start_date and peo.effective_end_date
667 AND hou.business_group_id = p_business_group_id
668 UNION
669 SELECT location_id
670 FROM hr_all_organization_units
671 WHERE business_group_id = p_business_group_id
672 AND NVL(date_to,p_report_date + 1) >= p_report_date );
673 l_count_annexes NUMBER := 0;
674
675
676 CURSOR csr_schools(p_business_group_id IN NUMBER) IS
677 SELECT COUNT(1)
678 FROM hr_organization_units_v hou
679 WHERE hou.business_group_id = p_business_group_id;
680 l_count_schools NUMBER := 0;
681
682 CURSOR csr_district_id(p_business_group_id IN NUMBER) IS
683 SELECT org_information12 district_id
684 FROM hr_organization_information
685 WHERE org_information_context = 'EEO_REPORT'
686 AND org_information11 = 'EEO5'
687 AND organization_id = p_business_group_id;
688
689 l_district_name VARCHAR2(200);
690 l_district_id VARCHAR2(200);
691 l_char_report_date VARCHAR2(20);
692
693 no_tmraces_wmale_emps NUMBER := 0;
694 no_tmraces_bmale_emps NUMBER := 0;
695 no_tmraces_hmale_emps NUMBER := 0;
696 no_tmraces_amale_emps NUMBER := 0;
697 no_tmraces_imale_emps NUMBER := 0;
698 no_tmraces_wfemale_emps NUMBER := 0;
699 no_tmraces_bfemale_emps NUMBER := 0;
700 no_tmraces_hfemale_emps NUMBER := 0;
701 no_tmraces_afemale_emps NUMBER := 0;
702 no_tmraces_ifemale_emps NUMBER := 0;
703
704 --Local Procedure
705 PROCEDURE create_record(p_start_code IN NUMBER
706 ,p_end_code IN NUMBER
707 ,p_current_code IN NUMBER
708 ,p_job_category_name IN VARCHAR2
709 ,p_cons_total_category_emps IN NUMBER
710 ,p_no_cons_wmale_emps IN NUMBER
711 ,p_no_cons_bmale_emps IN NUMBER
712 ,p_no_cons_hmale_emps IN NUMBER
713 ,p_no_cons_amale_emps IN NUMBER
714 ,p_no_cons_imale_emps IN NUMBER
715 ,p_no_cons_wfemale_emps IN NUMBER
716 ,p_no_cons_bfemale_emps IN NUMBER
717 ,p_no_cons_hfemale_emps IN NUMBER
718 ,p_no_cons_afemale_emps IN NUMBER
719 ,p_no_cons_ifemale_emps IN NUMBER
720 ) IS
721
722 CURSOR csr_lookup_code(p_counter IN NUMBER) IS
723 SELECT LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning
724 FROM hr_lookups hl
725 WHERE lookup_type = 'US_EEO5_JOB_CATEGORIES'
726 AND TO_NUMBER(lookup_code) = p_counter
727 AND application_id = 800;
728 l_job_category_name VARCHAR2(200);
729
730
731 BEGIN /*create_record*/
732
733 FOR i IN p_start_code..p_end_code LOOP
734
735 --Need to fetch the job_categroy_name from hr_lookups based on the variable 1.
736
737 l_job_category_name := get_job_category_meaning(i);
738
739
743 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
740 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
741 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
742 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name,'D');
744 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
745 l_xml_string := l_xml_string ||convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',0,'D');
746 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WMALE_EMPS',0,'D');
747 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BMALE_EMPS',0,'D');
748 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HMALE_EMPS',0,'D');
749 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AMALE_EMPS',0,'D');
750 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IMALE_EMPS',0,'D');
751 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WFEMALE_EMPS',0,'D');
752 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BFEMALE_EMPS',0,'D');
753 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HFEMALE_EMPS',0,'D');
754 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AFEMALE_EMPS',0,'D');
755 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IFEMALE_EMPS',0,'D');
756 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
757 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
758 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
759 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
760
761 END LOOP;
762
763 IF p_current_code <> 0 THEN
764 l_job_category_name := get_job_category_meaning(p_current_code);
765 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
766 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
767 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name,'D');
768 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
769 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
770 l_xml_string := l_xml_string ||convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',p_cons_total_category_emps,'D');
771 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WMALE_EMPS',p_no_cons_wmale_emps,'D');
772 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BMALE_EMPS',p_no_cons_bmale_emps,'D');
773 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HMALE_EMPS',p_no_cons_hmale_emps,'D');
774 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AMALE_EMPS',p_no_cons_amale_emps,'D');
775 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IMALE_EMPS',p_no_cons_imale_emps,'D');
776 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WFEMALE_EMPS',p_no_cons_wfemale_emps,'D');
777 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BFEMALE_EMPS',p_no_cons_bfemale_emps,'D');
778 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HFEMALE_EMPS',p_no_cons_hfemale_emps,'D');
779 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AFEMALE_EMPS',p_no_cons_afemale_emps,'D');
780 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IFEMALE_EMPS',p_no_cons_ifemale_emps,'D');
781 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
782 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
783 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
784 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
785 END IF;
786
787 END create_record;
788
789 BEGIN /* generate_xml_data */
790 l_xml_string := '<?xml version="1.0"?> <PQHEEO5>';
791
792
793 --Step 1 : Write Business Group Details
794 /*
795 <LIST_G_JURISDICTION_DETAIL>
796 <G_JURISDICTION_DETAIL>
797 <CITY_STATE_ZIP>San Francisco,San Francisco,CA,94100-1234</CITY_STATE_ZIP>
798 <BUSINESS_NAME>MM_RT BG2</BUSINESS_NAME>
799 <ADDRESS>314 Maple Street Suite 1000</ADDRESS>
800 </G_JURISDICTION_DETAIL>
801 </LIST_G_JURISDICTION_DETAIL>
802 */
803
804 l_xml_string := l_xml_string ||'<LIST_G_JURISDICTION_DETAIL>';
805 l_xml_string := l_xml_string ||'<G_JURISDICTION_DETAIL>';
806
807 OPEN csr_district_id(p_business_group_id);
808 FETCH csr_district_id INTO l_district_id;
809 CLOSE csr_district_id;
810
811 OPEN csr_bg_details(p_business_group_id);
812 FETCH csr_bg_details INTO l_bg_name,l_location_id;
813 CLOSE csr_bg_details;
814
815 OPEN csr_bg_location(l_location_id);
816 FETCH csr_bg_location INTO l_bg_address,l_bg_city, l_bg_county,l_bg_state,l_bg_zip_code;
817 CLOSE csr_bg_location;
818
819
820 l_district_name := l_bg_name;
821 l_xml_string := l_xml_string ||convert_into_xml('BUSINESS_NAME',l_bg_name,'D');
822 l_xml_string := l_xml_string ||convert_into_xml('BUSINESS_DISTRICT_ID',l_district_id,'D');
823 l_xml_string := l_xml_string ||convert_into_xml('ADDRESS',l_bg_address,'D');
824 l_xml_string := l_xml_string ||convert_into_xml('CITY',l_bg_city,'D');
825 l_xml_string := l_xml_string ||convert_into_xml('COUNTY',l_bg_county,'D');
826 l_xml_string := l_xml_string ||convert_into_xml('STATE',l_bg_state,'D');
827 l_xml_string := l_xml_string ||convert_into_xml('ZIP_CODE',l_bg_zip_code,'D');
828
829 l_xml_string := l_xml_string ||'</G_JURISDICTION_DETAIL>';
830 l_xml_string := l_xml_string ||'</LIST_G_JURISDICTION_DETAIL>';
831 write_to_concurrent_out(l_xml_string);
832
833
834 --Step 2 :
835 /*
836 <LIST_G_CERT_OFFICER_NAME>
837 <G_CERT_OFFICER_NAME>
838 <SYSTEM_DISTRICT>DISTRICT</SYSTEM_DISTRICT>
839 <TYPE_REPORT>DISTRICT SUMMARY</TYPE_REPORT>
843 <CONTACT_TELEPHONE></CONTACT_TELEPHONE>
840 <CONTROL_NUMBER>1234567890</CONTROL_NUMBER>
841 <CERT_OFFICER_NAME></CERT_OFFICER_NAME>
842 <CERT_OFFICIAL_TITLE></CERT_OFFICIAL_TITLE>
844 </G_CERT_OFFICER_NAME>
845 </LIST_G_CERT_OFFICER_NAME>
846 */
847
848 /* Resetting l_xml_string */
849 l_xml_string := '<LIST_G_CERT_OFFICER_NAME>';
850 l_xml_string := l_xml_string||'<G_CERT_OFFICER_NAME>';
851
852 FOR i IN csr_cert_officer_details(p_business_group_id) LOOP
853 l_xml_string := l_xml_string ||convert_into_xml('SYSTEM_DISTRICT',i.system_district,'D');
854 l_xml_string := l_xml_string ||convert_into_xml('TYPE_REPORT',i.type_report,'D');
855 l_xml_string := l_xml_string ||convert_into_xml('CONTROL_NUMBER',i.control_number,'D');
856 IF ((i.cert_officer_name IS NOT NULL) AND (i.cert_official_title IS NOT NULL) ) THEN
857 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICER_NAME',i.cert_officer_name||'/','D');
858 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICIAL_TITLE',i.cert_official_title,'D');
859 ELSE
860 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICER_NAME',i.cert_officer_name,'D');
861 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICIAL_TITLE',i.cert_official_title,'D');
862 END IF;
863 l_xml_string := l_xml_string ||convert_into_xml('CONTACT_TELEPHONE',i.contact_telephone,'D');
864 -- l_xml_string := l_xml_string||convert_into_xml('CP_REPORT_DATE',l_report_date,'D');
865 END LOOP;
866
867 l_xml_string := l_xml_string||'</G_CERT_OFFICER_NAME>';
868 l_xml_string := l_xml_string||'</LIST_G_CERT_OFFICER_NAME>';
869 write_to_concurrent_out(l_xml_string);
870
871
872 --Step 3 :
873 /*
874 <LIST_G_EMPLOYMENT_CATEGORY>
875 <G_EMPLOYMENT_CATEGORY>
876 <EMPLOYEE_SALARY_EMPLOYMENT_CAT>A. FULL TIME STAFF</EMPLOYEE_SALARY_EMPLOYMENT_CAT>
877 <LIST_G_JOB_CATEGORIES>
878 <G_JOB_CATEGORIES>
879 <JOB_CATEGORY_MEANING>2.PRIN</JOB_CATEGORY_MEANING>
880 <LIST_G_JOB_INFORMATION>
881 <G_JOB_INFORMATION>
882 <CONS_TOTAL_CATEGORY_EMPS>2</CONS_TOTAL_CATEGORY_EMPS>
883 <NO_CONS_WMALE_EMPS>1</NO_CONS_WMALE_EMPS>
884 <NO_CONS_BMALE_EMPS>0</NO_CONS_BMALE_EMPS>
885 <NO_CONS_HMALE_EMPS>0</NO_CONS_HMALE_EMPS>
886 <NO_CONS_AMALE_EMPS>0</NO_CONS_AMALE_EMPS>
887 <NO_CONS_IMALE_EMPS>0</NO_CONS_IMALE_EMPS>
888 <NO_CONS_WFEMALE_EMPS>1</NO_CONS_WFEMALE_EMPS>
889 <NO_CONS_BFEMALE_EMPS>0</NO_CONS_BFEMALE_EMPS>
890 <NO_CONS_HFEMALE_EMPS>0</NO_CONS_HFEMALE_EMPS>
891 <NO_CONS_AFEMALE_EMPS>0</NO_CONS_AFEMALE_EMPS>
892 <NO_CONS_IFEMALE_EMPS>0</NO_CONS_IFEMALE_EMPS>
893 </G_JOB_INFORMATION>
894 </LIST_G_JOB_INFORMATION>
895 </G_JOB_CATEGORIES>
896 </LIST_G_JOB_CATEGORIES>
897 <LIST_G_JOB_CATEGORIES>
898 <G_JOB_CATEGORIES>
899 <JOB_CATEGORY_MEANING>3.</JOB_CATEGORY_MEANING>
900 <LIST_G_JOB_INFORMATION>
901 <G_JOB_INFORMATION>
902 .....
903 .....
904 </G_JOB_INFORMATION>
905 </LIST_G_JOB_INFORMATION>
906 </G_JOB_CATEGORIES>
907 </LIST_G_JOB_CATEGORIES>
908 <CF_TOTAL_TITLE>19.TOTAL</CF_TOTAL_TITLE>
909 <CS_NO_WMALE_EMPS>1</CS_NO_WMALE_EMPS>
910 <CS_NO_BMALE_EMPS>1</CS_NO_BMALE_EMPS>
911 <CS_NO_HMALE_EMPS>0</CS_NO_HMALE_EMPS>
912 <CS_NO_AMALE_EMPS>1</CS_NO_AMALE_EMPS>
913 <CS_NO_IMALE_EMPS>0</CS_NO_IMALE_EMPS>
914 <CS_NO_WFEMALE_EMPS>1</CS_NO_WFEMALE_EMPS>
915 <CS_NO_BFEMALE_EMPS>0</CS_NO_BFEMALE_EMPS>
916 <CS_NO_HFEMALE_EMPS>0</CS_NO_HFEMALE_EMPS>
917 <CS_NO_AFEMALE_EMPS>2</CS_NO_AFEMALE_EMPS>
918 <CS_NO_IFEMALE_EMPS>1</CS_NO_IFEMALE_EMPS>
919 <CS_TOTAL_CATEGORY_EMPS>7</CS_TOTAL_CATEGORY_EMPS>
920 </G_EMPLOYMENT_CATEGORY>
921 <G_EMPLOYMENT_CATEGORY>
922 <EMPLOYEE_SALARY_EMPLOYMENT_CAT>B. PART-TIME STAFF</EMPLOYEE_SALARY_EMPLOYMENT_CAT>
923 </LIST_G_EMPLOYMENT_CATEGORY>
924 */
925
926
927 /* Resetting l_xml_string */
928 l_xml_string := '<LIST_G_EMPLOYMENT_CATEGORY>';
929 l_xml_string := l_xml_string||'<G_EMPLOYMENT_CATEGORY>';
930
931 pqh_employment_category.fetch_empl_categories(p_business_group_id
932 ,l_fr
933 ,l_ft
934 ,l_pr
935 ,l_pt);
936
937 hr_utility.set_location('=======in EEO5 per_us_eeo5_pkg==========='||l_fr, 5);
938 hr_utility.set_location('l_fr -> '||l_fr, 5);
939 hr_utility.set_location('l_fr -> '||l_ft, 5);
940 hr_utility.set_location('l_fr -> '||l_pr, 5);
941 hr_utility.set_location('l_fr -> '||l_pt, 5);
942 hr_utility.set_location('=======in EEO5 per_us_eeo5_pkg==========='||l_fr, 5);
943
944 --l_report_date is always for September.
945 l_report_date := TO_DATE('30-09'||'-'||p_reporting_year,'DD-MM-RRRR');
946 l_xml_string := l_xml_string ||convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','A. FULL-TIME STAFF','D');
947 l_start_code := 1;
948 l_end_code := 1;
949 l_current_code := 1;
950
951 FOR i IN csr_full_time_details(p_business_group_id,l_fr,l_ft,l_pr,l_pt,l_report_date,p_reporting_year) LOOP
952 --employment_category = 'A. FULL TIME STAFF' THEN
953 l_current_code := TO_NUMBER(SUBSTR(i.job_category_name,1,2));
954 l_end_code := l_current_code - 1;
955
956 -- Get the person counts with ethnic code 'Two or more races'
957 OPEN csr_tmr_ft_details(p_business_group_id,l_fr,l_ft,l_pr,l_pt,i.employment_category,i.job_category_name,l_report_date,p_reporting_year);
958 FETCH csr_tmr_ft_details INTO no_tmraces_wmale_emps,
959 no_tmraces_bmale_emps,
960 no_tmraces_hmale_emps,
961 no_tmraces_amale_emps,
962 no_tmraces_imale_emps,
963 no_tmraces_wfemale_emps,
964 no_tmraces_bfemale_emps,
965 no_tmraces_hfemale_emps,
966 no_tmraces_afemale_emps,
967 no_tmraces_ifemale_emps;
968 CLOSE csr_tmr_ft_details;
969
970 l_total_category_employees := i.cons_total_category_emps +
971 no_tmraces_wmale_emps +
972 no_tmraces_bmale_emps +
973 no_tmraces_hmale_emps +
974 no_tmraces_amale_emps +
975 no_tmraces_imale_emps +
976 no_tmraces_wfemale_emps +
977 no_tmraces_bfemale_emps +
978 no_tmraces_hfemale_emps +
979 no_tmraces_afemale_emps +
980 no_tmraces_ifemale_emps;
981
982
983 --Current record needs to be written as XML to FND OUT.
984 create_record(l_start_code
985 ,l_end_code
986 ,l_current_code
987 ,i.job_category_name
988 ,l_total_category_employees
989 ,i.no_cons_wmale_emps + no_tmraces_wmale_emps
990 ,i.no_cons_bmale_emps + no_tmraces_bmale_emps
991 ,i.no_cons_hmale_emps + no_tmraces_hmale_emps
992 ,i.no_cons_amale_emps + no_tmraces_amale_emps
993 ,i.no_cons_imale_emps + no_tmraces_imale_emps
994 ,i.no_cons_wfemale_emps + no_tmraces_wfemale_emps
995 ,i.no_cons_bfemale_emps + no_tmraces_bfemale_emps
996 ,i.no_cons_hfemale_emps + no_tmraces_hfemale_emps
997 ,i.no_cons_afemale_emps + no_tmraces_afemale_emps
998 ,i.no_cons_ifemale_emps + no_tmraces_ifemale_emps);
999 -- Bug# 6242997
1000 l_sum_cons_total_category_emps := l_sum_cons_total_category_emps + l_total_category_employees;
1001 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps + no_tmraces_wmale_emps;
1002 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps + no_tmraces_bmale_emps;
1003 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps + no_tmraces_hmale_emps;
1004 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps + no_tmraces_amale_emps;
1005 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps + no_tmraces_imale_emps;
1006 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps + no_tmraces_wfemale_emps;
1007 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps + no_tmraces_bfemale_emps;
1008 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps + no_tmraces_hfemale_emps;
1009 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps + no_tmraces_afemale_emps;
1010 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps + no_tmraces_ifemale_emps;
1011 hr_utility_trace('==================================================');
1012 hr_utility_trace(' i.job_category_name -> ' || i.job_category_name);
1013 hr_utility_trace(' l_start_code -> ' || l_start_code);
1014 hr_utility_trace(' l_end_code -> ' || l_end_code);
1015 hr_utility_trace(' l_current_code -> ' || l_current_code);
1016 hr_utility_trace('==================================================');
1017 l_start_code := l_current_code+1;
1018 END LOOP;/* csr_full_time_details */
1019
1020 hr_utility_trace('==================================================');
1021 hr_utility_trace(' Out of the Loop -> ');
1022 hr_utility_trace(' length(l_xml_string) -> ' || length(l_xml_string));
1023 hr_utility_trace(' l_start_code -> ' || l_start_code);
1024 hr_utility_trace(' l_end_code -> ' || l_end_code);
1025 hr_utility_trace(' l_current_code -> ' || l_current_code);
1026 hr_utility_trace('==================================================');
1027
1028 IF l_start_code = 1 THEN
1029 -- Cursor csr_full_time_details did not fetch any records.
1030 l_end_code := 18;
1031 create_record(l_start_code
1032 ,l_end_code
1033 ,0
1034 ,0
1035 ,0
1036 ,0
1037 ,0
1038 ,0
1039 ,0
1040 ,0
1041 ,0
1042 ,0
1043 ,0
1044 ,0
1045 ,0);
1046 l_sum_cons_total_category_emps := 0;
1047 l_sum_no_cons_wmale_emps := 0;
1048 l_sum_no_cons_bmale_emps := 0;
1049 l_sum_no_cons_hmale_emps := 0;
1050 l_sum_no_cons_amale_emps := 0;
1051 l_sum_no_cons_imale_emps := 0;
1052 l_sum_no_cons_wfemale_emps := 0;
1053 l_sum_no_cons_bfemale_emps := 0;
1054 l_sum_no_cons_hfemale_emps := 0;
1055 l_sum_no_cons_afemale_emps := 0;
1056 l_sum_no_cons_ifemale_emps := 0;
1057
1058 ELSIF l_start_code > 1 THEN
1059
1060 --Cursor has fetched some data.
1061 l_end_code := 18;
1062 create_record(l_start_code
1063 ,l_end_code
1064 ,0
1065 ,0
1066 ,0
1067 ,0
1068 ,0
1069 ,0
1070 ,0
1071 ,0
1072 ,0
1073 ,0
1074 ,0
1075 ,0
1076 ,0);
1077 END IF; /* l_start_code = 1 */
1078
1079 l_job_category_name_main := get_job_category_meaning(19);
1080 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
1081 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
1082 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name_main,'D');
1083 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
1084 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
1085 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',l_sum_no_cons_wmale_emps,'D');
1086 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',l_sum_no_cons_bmale_emps,'D');
1087 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',l_sum_no_cons_hmale_emps,'D');
1088 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',l_sum_no_cons_amale_emps,'D');
1089 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',l_sum_no_cons_imale_emps,'D');
1090 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',l_sum_no_cons_wfemale_emps,'D');
1091 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',l_sum_no_cons_bfemale_emps,'D');
1092 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',l_sum_no_cons_hfemale_emps,'D');
1093 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',l_sum_no_cons_afemale_emps,'D');
1097 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
1094 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',l_sum_no_cons_ifemale_emps,'D');
1095 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',l_sum_cons_total_category_emps,'D');
1096 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
1098 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
1099 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
1100 l_xml_string := l_xml_string||'</G_EMPLOYMENT_CATEGORY>';
1101
1102 write_to_concurrent_out(l_xml_string);
1103
1104 --End of full time employees. End of Step 3.
1105
1106
1107 --Step 4.
1108 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1109 l_sum_cons_total_category_emps := 0;
1110 l_sum_no_cons_wmale_emps := 0;
1111 l_sum_no_cons_bmale_emps := 0;
1112 l_sum_no_cons_hmale_emps := 0;
1113 l_sum_no_cons_amale_emps := 0;
1114 l_sum_no_cons_imale_emps := 0;
1115 l_sum_no_cons_wfemale_emps := 0;
1116 l_sum_no_cons_bfemale_emps := 0;
1117 l_sum_no_cons_hfemale_emps := 0;
1118 l_sum_no_cons_afemale_emps := 0;
1119 l_sum_no_cons_ifemale_emps := 0;
1120 l_start_code := 20;
1121 l_xml_string := l_xml_string ||convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','B. PART-TIME STAFF','D');
1122
1123 FOR i IN csr_part_time_details(p_business_group_id,l_fr,l_ft,l_pr,l_pt,l_report_date,p_reporting_year) LOOP
1124 l_current_code := TO_NUMBER(SUBSTR(i.job_category_name,1,2));
1125 l_end_code := l_current_code - 1;
1126 -- Get the person counts with ethnic code 'Two or more races'
1127 OPEN csr_tmr_pt_details(p_business_group_id,l_fr,l_ft,l_pr,l_pt,i.employment_category,i.job_category_name,l_report_date,p_reporting_year);
1128 FETCH csr_tmr_pt_details INTO no_tmraces_wmale_emps,
1129 no_tmraces_bmale_emps,
1130 no_tmraces_hmale_emps,
1131 no_tmraces_amale_emps,
1132 no_tmraces_imale_emps,
1133 no_tmraces_wfemale_emps,
1134 no_tmraces_bfemale_emps,
1135 no_tmraces_hfemale_emps,
1136 no_tmraces_afemale_emps,
1137 no_tmraces_ifemale_emps;
1138 CLOSE csr_tmr_pt_details;
1139
1140 l_total_category_employees := i.cons_total_category_emps +
1141 no_tmraces_wmale_emps +
1142 no_tmraces_bmale_emps +
1143 no_tmraces_hmale_emps +
1144 no_tmraces_amale_emps +
1145 no_tmraces_imale_emps +
1146 no_tmraces_wfemale_emps +
1147 no_tmraces_bfemale_emps +
1148 no_tmraces_hfemale_emps +
1149 no_tmraces_afemale_emps +
1150 no_tmraces_ifemale_emps;
1151
1152 --Current record needs to be written as XML to FND OUT.
1153 create_record(l_start_code
1154 ,l_end_code
1155 ,l_current_code
1156 ,i.job_category_name
1157 ,l_total_category_employees
1158 ,i.no_cons_wmale_emps + no_tmraces_wmale_emps
1159 ,i.no_cons_bmale_emps + no_tmraces_bmale_emps
1160 ,i.no_cons_hmale_emps + no_tmraces_hmale_emps
1161 ,i.no_cons_amale_emps + no_tmraces_amale_emps
1162 ,i.no_cons_imale_emps + no_tmraces_imale_emps
1163 ,i.no_cons_wfemale_emps + no_tmraces_wfemale_emps
1164 ,i.no_cons_bfemale_emps + no_tmraces_bfemale_emps
1165 ,i.no_cons_hfemale_emps + no_tmraces_hfemale_emps
1166 ,i.no_cons_afemale_emps + no_tmraces_afemale_emps
1167 ,i.no_cons_ifemale_emps + no_tmraces_ifemale_emps);
1168 -- Bug# 6242997
1169 l_sum_cons_total_category_emps := l_sum_cons_total_category_emps + l_total_category_employees;
1170 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps + no_tmraces_wmale_emps;
1171 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps + no_tmraces_bmale_emps;
1172 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps + no_tmraces_hmale_emps;
1173 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps + no_tmraces_amale_emps;
1174 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps + no_tmraces_imale_emps;
1175 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps + no_tmraces_wfemale_emps;
1176 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps + no_tmraces_bfemale_emps;
1177 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps + no_tmraces_hfemale_emps;
1178 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps + no_tmraces_afemale_emps;
1179 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps + no_tmraces_ifemale_emps;
1180 l_start_code := l_current_code+1;
1181 END LOOP; /* csr_part_time_details */
1182
1183 l_end_code := 21;
1184 IF l_start_code = 20 THEN
1185 --Cursor has not fetched any data.
1186 create_record(l_start_code --20
1187 ,l_end_code --21
1188 ,0
1189 ,0
1190 ,0
1191 ,0
1192 ,0
1193 ,0
1194 ,0
1195 ,0
1196 ,0
1197 ,0
1198 ,0
1199 ,0
1200 ,0);
1201 l_sum_cons_total_category_emps := 0;
1202 l_sum_no_cons_wmale_emps := 0;
1203 l_sum_no_cons_bmale_emps := 0;
1204 l_sum_no_cons_hmale_emps := 0;
1205 l_sum_no_cons_amale_emps := 0;
1206 l_sum_no_cons_imale_emps := 0;
1207 l_sum_no_cons_wfemale_emps := 0;
1211 l_sum_no_cons_ifemale_emps := 0;
1208 l_sum_no_cons_bfemale_emps := 0;
1209 l_sum_no_cons_hfemale_emps := 0;
1210 l_sum_no_cons_afemale_emps := 0;
1212
1213 ELSIF l_start_code = 21 THEN
1214 l_job_category_name_main := '21.ALL OTHER';
1215 create_record(l_start_code --21
1216 ,l_end_code --21
1217 ,0
1218 ,0
1219 ,0
1220 ,0
1221 ,0
1222 ,0
1223 ,0
1224 ,0
1225 ,0
1226 ,0
1227 ,0
1228 ,0
1229 ,0);
1230 END IF;
1231
1232
1233 l_job_category_name_main := get_job_category_meaning(22);
1234 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
1235 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
1236 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name_main,'D');
1237 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
1238 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
1239 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',l_sum_no_cons_wmale_emps,'D');
1240 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',l_sum_no_cons_bmale_emps,'D');
1241 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',l_sum_no_cons_hmale_emps,'D');
1242 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',l_sum_no_cons_amale_emps,'D');
1243 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',l_sum_no_cons_imale_emps,'D');
1244 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',l_sum_no_cons_wfemale_emps,'D');
1245 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',l_sum_no_cons_bfemale_emps,'D');
1246 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',l_sum_no_cons_hfemale_emps,'D');
1247 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',l_sum_no_cons_afemale_emps,'D');
1248 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',l_sum_no_cons_ifemale_emps,'D');
1249 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',l_sum_cons_total_category_emps,'D');
1250 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
1251 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
1252 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
1253 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
1254 l_xml_string := l_xml_string||'</G_EMPLOYMENT_CATEGORY>';
1255 -- l_xml_string := l_xml_string||'</LIST_G_EMPLOYMENT_CATEGORY> </PQHEEO5>';
1256
1257 write_to_concurrent_out(l_xml_string);
1258
1259
1260 --Step 5: Codes 23 to 27
1261 l_sum_cons_total_category_emps := 0;
1262 l_sum_no_cons_wmale_emps := 0;
1263 l_sum_no_cons_bmale_emps := 0;
1264 l_sum_no_cons_hmale_emps := 0;
1265 l_sum_no_cons_amale_emps := 0;
1266 l_sum_no_cons_imale_emps := 0;
1267 l_sum_no_cons_wfemale_emps := 0;
1268 l_sum_no_cons_bfemale_emps := 0;
1269 l_sum_no_cons_hfemale_emps := 0;
1270 l_sum_no_cons_afemale_emps := 0;
1271 l_sum_no_cons_ifemale_emps := 0;
1272 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1273 l_start_code := 23;
1274 l_new_hires_heading := 'C. NEW HIRES (JULY THRU SEPT. '|| p_reporting_year ||')';
1275 l_xml_string := l_xml_string ||convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT',l_new_hires_heading,'D');
1276
1277 FOR i IN csr_new_hires_details(p_business_group_id,l_fr,l_ft,l_pr,l_pt,l_report_date,p_reporting_year) LOOP
1278 l_current_code := TO_NUMBER(SUBSTR(i.job_category_name,1,2));
1279 l_end_code := l_current_code - 1;
1280
1281 -- Get the person counts with ethnic code 'Two or more races'
1282 OPEN csr_tmr_nh_details(p_business_group_id,l_fr,l_ft,l_pr,l_pt,i.employment_category,i.job_category_name,l_report_date,p_reporting_year);
1283 FETCH csr_tmr_nh_details INTO no_tmraces_wmale_emps,
1284 no_tmraces_bmale_emps,
1285 no_tmraces_hmale_emps,
1286 no_tmraces_amale_emps,
1287 no_tmraces_imale_emps,
1288 no_tmraces_wfemale_emps,
1289 no_tmraces_bfemale_emps,
1290 no_tmraces_hfemale_emps,
1291 no_tmraces_afemale_emps,
1292 no_tmraces_ifemale_emps;
1293 CLOSE csr_tmr_nh_details;
1294
1295 l_total_category_employees := i.cons_total_category_emps +
1296 no_tmraces_wmale_emps +
1297 no_tmraces_bmale_emps +
1298 no_tmraces_hmale_emps +
1299 no_tmraces_amale_emps +
1300 no_tmraces_imale_emps +
1301 no_tmraces_wfemale_emps +
1302 no_tmraces_bfemale_emps +
1303 no_tmraces_hfemale_emps +
1304 no_tmraces_afemale_emps +
1305 no_tmraces_ifemale_emps;
1306
1307 --Current record needs to be written as XML to FND OUT.
1308 create_record(l_start_code
1309 ,l_end_code
1310 ,l_current_code
1311 ,i.job_category_name
1315 ,i.no_cons_hmale_emps + no_tmraces_hmale_emps
1312 ,l_total_category_employees
1313 ,i.no_cons_wmale_emps + no_tmraces_wmale_emps
1314 ,i.no_cons_bmale_emps + no_tmraces_bmale_emps
1316 ,i.no_cons_amale_emps + no_tmraces_amale_emps
1317 ,i.no_cons_imale_emps + no_tmraces_imale_emps
1318 ,i.no_cons_wfemale_emps + no_tmraces_wfemale_emps
1319 ,i.no_cons_bfemale_emps + no_tmraces_bfemale_emps
1320 ,i.no_cons_hfemale_emps + no_tmraces_hfemale_emps
1321 ,i.no_cons_afemale_emps + no_tmraces_afemale_emps
1322 ,i.no_cons_ifemale_emps + no_tmraces_ifemale_emps);
1323 -- Bug# 6242997
1324 l_sum_cons_total_category_emps := l_sum_cons_total_category_emps + l_total_category_employees;
1325 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps + no_tmraces_wmale_emps;
1326 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps + no_tmraces_bmale_emps;
1327 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps + no_tmraces_hmale_emps;
1328 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps + no_tmraces_amale_emps;
1329 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps + no_tmraces_imale_emps;
1330 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps + no_tmraces_wfemale_emps;
1331 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps + no_tmraces_bfemale_emps;
1332 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps + no_tmraces_hfemale_emps;
1333 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps + no_tmraces_afemale_emps;
1334 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps + no_tmraces_ifemale_emps;
1335 l_start_code := l_current_code+1;
1336 END LOOP; /* csr_new_hires_details */
1337
1338 l_end_code := 27;
1339 IF l_start_code = 23 THEN
1340 --Cursor has not fetched any data.
1341 create_record(l_start_code
1342 ,l_end_code
1343 ,0
1344 ,0
1345 ,0
1346 ,0
1347 ,0
1348 ,0
1349 ,0
1350 ,0
1351 ,0
1352 ,0
1353 ,0
1354 ,0
1355 ,0);
1356 l_sum_cons_total_category_emps := 0;
1357 l_sum_no_cons_wmale_emps := 0;
1358 l_sum_no_cons_bmale_emps := 0;
1359 l_sum_no_cons_hmale_emps := 0;
1360 l_sum_no_cons_amale_emps := 0;
1361 l_sum_no_cons_imale_emps := 0;
1362 l_sum_no_cons_wfemale_emps := 0;
1363 l_sum_no_cons_bfemale_emps := 0;
1364 l_sum_no_cons_hfemale_emps := 0;
1365 l_sum_no_cons_afemale_emps := 0;
1366 l_sum_no_cons_ifemale_emps := 0;
1367
1368 ELSIF l_start_code > 23 THEN
1369 --Cursor has fetched some data.
1370 l_end_code := 27;
1371 create_record(l_start_code
1372 ,l_end_code
1373 ,0
1374 ,0
1375 ,0
1376 ,0
1377 ,0
1378 ,0
1379 ,0
1380 ,0
1381 ,0
1382 ,0
1383 ,0
1384 ,0
1385 ,0);
1386 END IF;
1387
1388 l_job_category_name_main := get_job_category_meaning(28);
1389 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
1390 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
1391 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name_main,'D');
1392 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
1393 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
1394 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',l_sum_no_cons_wmale_emps,'D');
1395 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',l_sum_no_cons_bmale_emps,'D');
1396 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',l_sum_no_cons_hmale_emps,'D');
1397 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',l_sum_no_cons_amale_emps,'D');
1398 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',l_sum_no_cons_imale_emps,'D');
1399 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',l_sum_no_cons_wfemale_emps,'D');
1400 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',l_sum_no_cons_bfemale_emps,'D');
1401 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',l_sum_no_cons_hfemale_emps,'D');
1402 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',l_sum_no_cons_afemale_emps,'D');
1403 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',l_sum_no_cons_ifemale_emps,'D');
1404 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',l_sum_cons_total_category_emps,'D');
1405 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
1406 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
1407 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
1408 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
1409 l_xml_string := l_xml_string||'</G_EMPLOYMENT_CATEGORY>';
1410 l_xml_string := l_xml_string||'</LIST_G_EMPLOYMENT_CATEGORY>';
1411
1412 write_to_concurrent_out(l_xml_string);
1413
1414 --End of Step 5.
1415 /*Final Step :
1416 <CF_NO_OF_ANNEXES>3</CF_NO_OF_ANNEXES>
1417 <CP_REPORT_DATE>30-SEP-05</CP_REPORT_DATE>
1418 <CP_NO_OF_SCHOOLS>6</CP_NO_OF_SCHOOLS>
1419 <CP_FR>'FR'</CP_FR>
1420 <CP_FT>'FT'</CP_FT>
1421 <CP_PR>'PR'</CP_PR>
1422 <CP_PT>'PT'</CP_PT>
1423 </PQHEEO5> */
1424
1425 OPEN csr_annexes( l_report_date , p_business_group_id);
1426 FETCH csr_annexes INTO l_count_annexes;
1427 CLOSE csr_annexes;
1428
1429 OPEN csr_schools( p_business_group_id);
1430 FETCH csr_schools INTO l_count_schools;
1431 CLOSE csr_schools;
1432
1433 l_char_report_date := '30-SEP'||'-'||p_reporting_year;
1434
1435 l_xml_string := convert_into_xml('CF_NO_OF_ANNEXES',l_count_annexes,'D');
1436 l_xml_string := l_xml_string||convert_into_xml('CP_REPORT_DATE',l_char_report_date,'D');
1437 l_xml_string := l_xml_string||convert_into_xml('CP_NO_OF_SCHOOLS',l_count_schools,'D');
1438 --Added for bug 5404884
1439 l_xml_string := l_xml_string||convert_into_xml('P_TOTAL_ENROLLMENTS',p_total_enrollments,'D');
1440 --End of changes done for bug 5404884.
1441 --Added for bug 5415393
1442 l_xml_string := l_xml_string||convert_into_xml('DISTRICT_NAME',l_district_name,'D');
1443 l_xml_string := l_xml_string||convert_into_xml('DISTRICT_ID',l_district_id,'D');
1444 --End bug 5415393
1445 l_xml_string := l_xml_string||convert_into_xml('CP_FR','FR','D');
1446 l_xml_string := l_xml_string||convert_into_xml('CP_FT','FT','D');
1447 l_xml_string := l_xml_string||convert_into_xml('CP_PR','PR','D');
1448 l_xml_string := l_xml_string||convert_into_xml('CP_PT','PT','D');
1449 l_xml_string := l_xml_string||'</PQHEEO5>';
1450
1451 write_to_concurrent_out(l_xml_string);
1452
1453
1454 END generate_xml_data;
1455
1456
1457 --To put the trace just uncomment the below three lines.
1458 --BEGIN
1459 -- hr_utility.trace_on(null,'EEO5');
1460 -- g_debug := hr_utility.debug_enabled;
1461 END per_us_eeo5_pkg;