[Home] [Help]
PACKAGE BODY: APPS.PER_US_EEO5_PKG
Source
1 PACKAGE BODY per_us_eeo5_pkg AS
2 /* $Header: peuseeo5.pkb 120.11 2011/04/20 12:46:37 nvelaga ship $ */
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>
187 <NO_CONS_AMALE_EMPS>0</NO_CONS_AMALE_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>
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 -- Added for bug#11736960
280 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,1,2,1,3,1,4,1,5,1,6,1,NULL)) cons_total_category_emps,
281 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
282 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
283 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
284 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'M',1,NULL),5,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
285 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
286 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
287 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
288 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
289 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'F',1,NULL),5,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
293 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
290 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
291 /* Commented for bug#11736960 starts
292 COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) cons_total_category_emps,
294 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
295 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
296 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'M',1,NULL),'5',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
297 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'M',1,NULL),'7',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
298 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
299 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
300 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
301 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'F',1,NULL),'5',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
302 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'F',1,NULL),'7',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
303 Commented for bug#11736960 ends */
304 FROM per_all_people_f peo,
305 per_all_assignments_f ass,
306 per_assignment_status_types ast,
307 per_jobs job,
308 hr_lookups hl
309 WHERE peo.person_id = ass.person_id
310 AND peo.current_employee_flag = 'Y'
311 AND hl.lookup_code = job.job_information1
312 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
313 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
314 AND job.job_information_category = 'US'
315 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
316 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
317 AND ass.primary_flag = 'Y'
318 AND ass.assignment_status_type_id = ast.assignment_status_type_id
319 AND ast.per_system_status <> 'TERM_ASSIGN'
320 AND ass.job_id = job.job_id
321 AND ass.assignment_type = 'E'
322 AND ass.organization_id IN (
323 SELECT organization_id
324 FROM hr_all_organization_units
325 WHERE business_group_id = p_business_group_id)
326 GROUP BY
327 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
328 'FR', 'A. FULL-TIME STAFF') ,
329 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
330 'FR',LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning)
331 ORDER BY 1,2;
332
333
334 --Step 4
335 CURSOR csr_part_time_details(p_business_group_id IN NUMBER
336 ,p_fr IN VARCHAR2
337 ,p_ft IN VARCHAR2
338 ,p_pr IN VARCHAR2
339 ,p_pt IN VARCHAR2
340 ,p_report_date IN DATE
341 ,p_report_year IN NUMBER) IS
342 SELECT DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
343 'FT','B. PART-TIME STAFF',
344 'PR','B. PART-TIME STAFF',
345 'PT','B. PART-TIME STAFF') employment_category,
346 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
347 'PR',DECODE(job.job_information1,
348 '2','20.PROF. INSTR.',
349 '3','20.PROF. INSTR.',
350 '4','20.PROF. INSTR.',
351 '5','20.PROF. INSTR.',
352 '6','20.PROF. INSTR.',
353 '7','20.PROF. INSTR.',
354 '8','20.PROF. INSTR.',
355 '9','20.PROF. INSTR.',
356 '10','20.PROF. INSTR.',
357 '11','20.PROF. INSTR.',
358 '12','20.PROF. INSTR.',
359 '21.ALL OTHER')) job_category_name,
360 -- Added for bug#11736960
361 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,1,2,1,3,1,4,1,5,1,6,1,NULL)) cons_total_category_emps,
362 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
363 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
364 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
365 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'M',1,NULL),5,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
366 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
367 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
368 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
369 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
370 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'F',1,NULL),5,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
371 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
372 /* Commented for bug#11736960 starts
376 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
373 COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) cons_total_category_emps,
374 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
375 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
377 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'M',1,NULL),'5',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
378 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'M',1,NULL),'7',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
379 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
380 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
381 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
382 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'F',1,NULL),'5',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
383 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'F',1,NULL),'7',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
384 Commented for bug#11736960 ends */
385 FROM per_all_people_f peo,
386 per_all_assignments_f ass,
387 per_assignment_status_types ast,
388 per_jobs job,
389 hr_lookups hl
390 WHERE peo.person_id = ass.person_id
391 AND peo.current_employee_flag = 'Y'
392 AND hl.lookup_code = job.job_information1
393 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('PR')
394 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
395 AND job.job_information_category = 'US'
396 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
397 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
398 AND ass.primary_flag = 'Y'
399 AND ass.assignment_status_type_id = ast.assignment_status_type_id
400 AND ast.per_system_status <> 'TERM_ASSIGN'
401 AND ass.job_id = job.job_id
402 AND ass.assignment_type = 'E'
403 AND ass.organization_id IN (
404 SELECT organization_id
405 FROM hr_all_organization_units
406 WHERE business_group_id = p_business_group_id)
407 GROUP BY
408 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
409 'FT','B. PART-TIME STAFF',
410 'PR','B. PART-TIME STAFF',
411 'PT','B. PART-TIME STAFF') ,
412 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
413 'PR', DECODE(job.job_information1,
414 '2','20.PROF. INSTR.', '3','20.PROF. INSTR.',
415 '4','20.PROF. INSTR.', '5','20.PROF. INSTR.',
416 '6','20.PROF. INSTR.', '7','20.PROF. INSTR.',
417 '8','20.PROF. INSTR.', '9','20.PROF. INSTR.',
418 '10','20.PROF. INSTR.', '11','20.PROF. INSTR.',
419 '12','20.PROF. INSTR.', '21.ALL OTHER'))
420 ORDER BY 1,2;
421
422
423 --Step 5
424 CURSOR csr_new_hires_details(p_business_group_id IN NUMBER
425 ,p_fr IN VARCHAR2
426 ,p_ft IN VARCHAR2
427 ,p_pr IN VARCHAR2
428 ,p_pt IN VARCHAR2
429 ,p_report_date IN DATE
430 ,p_report_year IN NUMBER) IS
431 SELECT 'C. NEW HIRES (JULY THRU SEPT. '||p_report_year||')' employment_category,
432 DECODE(job.job_information1,
433 '1','23.0/A/M', '2','24.PRIN/ASST.PR',
434 '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR',
435 '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS',
436 '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.',
437 '9','26.OTHER PROF.', '10','26.OTHER PROF.',
438 '11','26.OTHER PROF.', '12','26.OTHER PROF.',
439 '13','27.NONPROF.', '14','27.NONPROF.',
440 '15','27.NONPROF.', '16','27.NONPROF.',
441 '17','27.NONPROF.', '18','27.NONPROF.') job_category_name,
442 -- Added for bug#11736960
443 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,1,2,1,3,1,4,1,5,1,6,1,NULL)) cons_total_category_emps,
444 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
445 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
446 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
447 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'M',1,NULL),5,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
448 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
449 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
450 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
451 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
452 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,DECODE(peo.sex,'F',1,NULL),5,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
453 COUNT(DECODE(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
454 /* Commented for bug#11736960 starts
458 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_hmale_emps,
455 COUNT(DECODE(peo.per_information1,'1',1,'2',1,'3',1,'4',1,'5',1,'6',1,'7',1,NULL)) cons_total_category_emps,
456 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_wmale_emps,
457 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_bmale_emps,
459 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'M',1,NULL),'5',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_amale_emps,
460 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'M',1,NULL),'7',DECODE(peo.sex,'M',1,NULL),NULL)) no_cons_imale_emps,
461 COUNT(DECODE(peo.per_information1,'1',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_wfemale_emps,
462 COUNT(DECODE(peo.per_information1,'2',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_bfemale_emps,
463 COUNT(DECODE(peo.per_information1,'3',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_hfemale_emps,
464 COUNT(DECODE(peo.per_information1,'4',DECODE(peo.sex,'F',1,NULL),'5',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_afemale_emps,
465 COUNT(DECODE(peo.per_information1,'6',DECODE(peo.sex,'F',1,NULL),'7',DECODE(peo.sex,'F',1,NULL),NULL)) no_cons_ifemale_emps
466 Commented for bug#11736960 ends */
467 FROM per_all_people_f peo,
468 per_all_assignments_f ass,
469 per_jobs job,
470 hr_lookups hl
471 WHERE peo.person_id = ass.person_id
472 AND peo.current_employee_flag = 'Y'
473 AND hl.lookup_code = job.job_information1
474 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
475 AND job.job_information_category = 'US'
476 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date -- Added for #9446839
477 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date -- Added for #9446839
478 AND ass.job_id = job.job_id
479 AND ass.assignment_type = 'E'
480 AND (SELECT date_start
481 FROM per_periods_of_service
482 WHERE period_of_service_id = ass.period_of_service_id)
483 BETWEEN ADD_MONTHS(p_report_date,-3) +1 AND p_report_date
484 AND ass.primary_flag = 'Y'
485 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
486 AND ass.organization_id IN (
487 SELECT organization_id
488 FROM hr_all_organization_units
489 WHERE business_group_id = p_business_group_id )
490 GROUP BY DECODE(job.job_information1,
491 '1','23.0/A/M', '2','24.PRIN/ASST.PR',
492 '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR',
493 '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS',
494 '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.',
495 '9','26.OTHER PROF.', '10','26.OTHER PROF.',
496 '11','26.OTHER PROF.', '12','26.OTHER PROF.',
497 '13','27.NONPROF.', '14','27.NONPROF.',
498 '15','27.NONPROF.', '16','27.NONPROF.',
499 '17','27.NONPROF.', '18','27.NONPROF.')
500 ORDER BY 1,2;
501
502 /* Commented for bug#11736960 starts
503 -- Full-Time People with Two or More race are considered in the modified csr_full_time_details cursor.
504 CURSOR csr_tmr_ft_details(p_business_group_id IN NUMBER
505 ,p_fr IN VARCHAR2
506 ,p_ft IN VARCHAR2
507 ,p_pr IN VARCHAR2
508 ,p_pt IN VARCHAR2
509 ,p_employment_category IN VARCHAR2
510 ,p_job_category_name IN VARCHAR2
511 ,p_report_date IN DATE
512 ,p_report_year IN NUMBER) IS
513 SELECT count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
514 count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
515 count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
516 count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
517 count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
518 count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
519 count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
520 count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
521 count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
522 count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
523 FROM per_all_people_f peo,
524 per_all_assignments_f ass,
525 per_assignment_status_types ast,
526 per_jobs job,
527 hr_lookups hl,
528 per_people_extra_info pei
529 WHERE peo.person_id = ass.person_id
530 AND peo.per_information1 = '13'
531 AND peo.person_id = pei.person_id(+)
532 AND pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
533 AND pei.pei_information5 is not null
534 AND peo.current_employee_flag = 'Y'
535 AND hl.lookup_code = job.job_information1
536 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
537 AND job.job_information_category = 'US'
538 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
542 AND ass.assignment_status_type_id = ast.assignment_status_type_id
539 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
540 AND ass.primary_flag = 'Y'
541 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
543 AND ast.per_system_status <> 'TERM_ASSIGN'
544 AND ass.job_id = job.job_id
545 AND ass.assignment_type = 'E'
546 AND ass.organization_id IN (
547 SELECT organization_id
548 FROM hr_all_organization_units
549 WHERE business_group_id = p_business_group_id)
550 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
551 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;
552 Commented for bug#11736960 ends */
553
554
555 --Step 4
556 /* Commented for bug#11736960 starts
557 -- Part-Time People with Two or More race are considered in the modified csr_part_time_details cursor.
558 CURSOR csr_tmr_pt_details(p_business_group_id IN NUMBER
559 ,p_fr IN VARCHAR2
560 ,p_ft IN VARCHAR2
561 ,p_pr IN VARCHAR2
562 ,p_pt IN VARCHAR2
563 ,p_employment_category IN VARCHAR2
564 ,p_job_category_name IN VARCHAR2
565 ,p_report_date IN DATE
566 ,p_report_year IN NUMBER) IS
567 SELECT count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
568 count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
569 count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
570 count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
571 count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
572 count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
573 count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
574 count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
575 count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
576 count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
577 FROM per_all_people_f peo,
578 per_all_assignments_f ass,
579 per_assignment_status_types ast,
580 per_jobs job,
581 hr_lookups hl,
582 per_people_extra_info pei
583 WHERE peo.person_id = ass.person_id
584 AND peo.per_information1 = '13'
585 AND peo.person_id = pei.person_id(+)
586 AND pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
587 AND pei.pei_information5 is not null
588 AND peo.current_employee_flag = 'Y'
589 AND hl.lookup_code = job.job_information1
590 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
591 AND job.job_information_category = 'US'
592 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
593 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date
594 AND ass.primary_flag = 'Y'
595 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('PR')
596 AND ass.assignment_status_type_id = ast.assignment_status_type_id
597 AND ast.per_system_status <> 'TERM_ASSIGN'
598 AND ass.job_id = job.job_id
599 AND ass.assignment_type = 'E'
600 AND ass.organization_id IN (
601 SELECT organization_id
602 FROM hr_all_organization_units
603 WHERE business_group_id = p_business_group_id)
604 AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
605 'FT','B. PART-TIME STAFF',
606 'PR','B. PART-TIME STAFF',
607 'PT','B. PART-TIME STAFF') = p_employment_category
608 AND DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt),
609 'PR',DECODE(job.job_information1,
610 '2','20.PROF. INSTR.',
611 '3','20.PROF. INSTR.',
612 '4','20.PROF. INSTR.',
613 '5','20.PROF. INSTR.',
614 '6','20.PROF. INSTR.',
615 '7','20.PROF. INSTR.',
616 '8','20.PROF. INSTR.',
617 '9','20.PROF. INSTR.',
618 '10','20.PROF. INSTR.',
619 '11','20.PROF. INSTR.',
620 '12','20.PROF. INSTR.',
621 '21.ALL OTHER')) = p_job_category_name;
622 Commented for bug#11736960 ends */
623
624 --Step 5
625 /* Commented for bug#11736960 starts
626 -- New-Hire People with Two or More race are considered in the modified csr_new_hires_details cursor.
627 CURSOR csr_tmr_nh_details(p_business_group_id IN NUMBER
628 ,p_fr IN VARCHAR2
629 ,p_ft IN VARCHAR2
633 ,p_job_category_name IN VARCHAR2
630 ,p_pr IN VARCHAR2
631 ,p_pt IN VARCHAR2
632 ,p_employment_category IN VARCHAR2
634 ,p_report_date IN DATE
635 ,p_report_year IN NUMBER) IS
636 SELECT count(decode(pei.pei_information5,'1',decode(peo.sex,'M',1,null),null)) no_tmraces_wmale_emps,
637 count(decode(pei.pei_information5,'2',decode(peo.sex,'M',1,null),null)) no_tmraces_bmale_emps,
638 count(decode(pei.pei_information5,'3',decode(peo.sex,'M',1,null),'9',decode(peo.sex,'M',1,null),null)) no_tmraces_hmale_emps,
639 count(decode(pei.pei_information5,'4',decode(peo.sex,'M',1,null),'5',decode(peo.sex,'M',1,null),null)) no_tmraces_amale_emps,
640 count(decode(pei.pei_information5,'6',decode(peo.sex,'M',1,null),null)) no_tmraces_imale_emps,
641 count(decode(pei.pei_information5,'1',decode(peo.sex,'F',1,null),null)) no_tmraces_wfemale_emps,
642 count(decode(pei.pei_information5,'2',decode(peo.sex,'F',1,null),null)) no_tmraces_bfemale_emps,
643 count(decode(pei.pei_information5,'3',decode(peo.sex,'F',1,null),'9',decode(peo.sex,'F',1,null),null)) no_tmraces_hfemale_emps,
644 count(decode(pei.pei_information5,'4',decode(peo.sex,'F',1,null),'5',decode(peo.sex,'F',1,null),null)) no_tmraces_afemale_emps,
645 count(decode(pei.pei_information5,'6',decode(peo.sex,'F',1,null),null)) no_tmraces_ifemale_emps
646 FROM per_all_people_f peo,
647 per_all_assignments_f ass,
648 per_jobs job,
649 hr_lookups hl,
650 per_people_extra_info pei
651 WHERE peo.person_id = ass.person_id
652 AND peo.per_information1 = '13'
653 AND peo.person_id = pei.person_id(+)
654 AND pei.information_type = 'PER_US_ADDL_ETHNIC_CAT'
655 AND pei.pei_information5 is not null
656 AND peo.current_employee_flag = 'Y'
657 AND hl.lookup_code = job.job_information1
658 AND hl.lookup_type = 'US_EEO5_JOB_CATEGORIES'
659 AND job.job_information_category = 'US'
660 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date -- Added for #9446839
661 AND p_report_date BETWEEN ass.effective_start_date AND ass.effective_end_date -- Added for #9446839
662 AND ass.job_id = job.job_id
663 AND ass.assignment_type = 'E'
664 AND (SELECT date_start
665 FROM per_periods_of_service
666 WHERE period_of_service_id = ass.period_of_service_id)
667 BETWEEN ADD_MONTHS(p_report_date,-3) +1 AND p_report_date
668 AND ass.primary_flag = 'Y'
669 AND pqh_employment_category.identify_empl_category(ass.employment_category,p_fr,p_ft,p_pr,p_pt) IN ('FR')
670 AND ass.organization_id IN (
671 SELECT organization_id
672 FROM hr_all_organization_units
673 WHERE business_group_id = p_business_group_id )
674 and 'C. NEW HIRES (JULY THRU SEPT. '||p_report_year||')' = p_employment_category
675 and DECODE(job.job_information1,
676 '1','23.0/A/M', '2','24.PRIN/ASST.PR',
677 '3','24.PRIN/ASST.PR', '4','24.PRIN/ASST.PR',
678 '5','25.CLSRM. TCHRS', '6','25.CLSRM. TCHRS',
679 '7','25.CLSRM. TCHRS', '8','26.OTHER PROF.',
680 '9','26.OTHER PROF.', '10','26.OTHER PROF.',
681 '11','26.OTHER PROF.', '12','26.OTHER PROF.',
682 '13','27.NONPROF.', '14','27.NONPROF.',
683 '15','27.NONPROF.', '16','27.NONPROF.',
684 '17','27.NONPROF.', '18','27.NONPROF.') = p_job_category_name;
685 Commented for bug#11736960 ends */
686
687 l_start_code NUMBER := 1;
688 l_end_code NUMBER := 1;
689 l_current_code NUMBER := 1;
690 l_sum_cons_total_category_emps NUMBER := 0;
691 l_sum_no_cons_wmale_emps NUMBER := 0;
692 l_sum_no_cons_bmale_emps NUMBER := 0;
693 l_sum_no_cons_hmale_emps NUMBER := 0;
694 l_sum_no_cons_amale_emps NUMBER := 0;
695 l_sum_no_cons_imale_emps NUMBER := 0;
696 l_sum_no_cons_wfemale_emps NUMBER := 0;
697 l_sum_no_cons_bfemale_emps NUMBER := 0;
698 l_sum_no_cons_hfemale_emps NUMBER := 0;
699 l_sum_no_cons_afemale_emps NUMBER := 0;
700 l_sum_no_cons_ifemale_emps NUMBER := 0;
701 l_total_category_employees NUMBER := 0;
702 l_report_date DATE;
703 l_job_category_name_main VARCHAR2(200);
704 l_new_hires_heading VARCHAR2(200);
705
706
707 --Final Step
708 CURSOR csr_annexes(p_report_date IN DATE,p_business_group_id IN NUMBER) IS
709 SELECT COUNT(1)
710 FROM (
711 SELECT ass.location_id
712 FROM per_all_assignments_f ass,
713 hr_organization_units_v hou,
714 per_all_people_f peo
715 WHERE ass.organization_id = hou.organization_id
716 AND ass.person_id = peo.person_id
717 AND p_report_date between ass.effective_start_date and ass.effective_end_date
718 AND p_report_date between peo.effective_start_date and peo.effective_end_date
719 AND hou.business_group_id = p_business_group_id
720 UNION
721 SELECT location_id
722 FROM hr_all_organization_units
723 WHERE business_group_id = p_business_group_id
724 AND NVL(date_to,p_report_date + 1) >= p_report_date );
725 l_count_annexes NUMBER := 0;
726
727
728 CURSOR csr_schools(p_business_group_id IN NUMBER) IS
729 SELECT COUNT(1)
730 FROM hr_organization_units_v hou
731 WHERE hou.business_group_id = p_business_group_id;
732 l_count_schools NUMBER := 0;
733
737 WHERE org_information_context = 'EEO_REPORT'
734 CURSOR csr_district_id(p_business_group_id IN NUMBER) IS
735 SELECT org_information12 district_id
736 FROM hr_organization_information
738 AND org_information11 = 'EEO5'
739 AND organization_id = p_business_group_id;
740
741 l_district_name VARCHAR2(200);
742 l_district_id VARCHAR2(200);
743 l_char_report_date VARCHAR2(20);
744
745 /* Commented for bug#11736960 starts
746 -- People with Two or More race are considered in the modified cursors.
747 -- Hence, commented these variables declared for Two or More race usage.
748 no_tmraces_wmale_emps NUMBER := 0;
749 no_tmraces_bmale_emps NUMBER := 0;
750 no_tmraces_hmale_emps NUMBER := 0;
751 no_tmraces_amale_emps NUMBER := 0;
752 no_tmraces_imale_emps NUMBER := 0;
753 no_tmraces_wfemale_emps NUMBER := 0;
754 no_tmraces_bfemale_emps NUMBER := 0;
755 no_tmraces_hfemale_emps NUMBER := 0;
756 no_tmraces_afemale_emps NUMBER := 0;
757 no_tmraces_ifemale_emps NUMBER := 0;
758 Commented for bug#11736960 ends */
759
760 --Local Procedure
761 PROCEDURE create_record(p_start_code IN NUMBER
762 ,p_end_code IN NUMBER
763 ,p_current_code IN NUMBER
764 ,p_job_category_name IN VARCHAR2
765 ,p_cons_total_category_emps IN NUMBER
766 ,p_no_cons_wmale_emps IN NUMBER
767 ,p_no_cons_bmale_emps IN NUMBER
768 ,p_no_cons_hmale_emps IN NUMBER
769 ,p_no_cons_amale_emps IN NUMBER
770 ,p_no_cons_imale_emps IN NUMBER
771 ,p_no_cons_wfemale_emps IN NUMBER
772 ,p_no_cons_bfemale_emps IN NUMBER
773 ,p_no_cons_hfemale_emps IN NUMBER
774 ,p_no_cons_afemale_emps IN NUMBER
775 ,p_no_cons_ifemale_emps IN NUMBER
776 ) IS
777
778 CURSOR csr_lookup_code(p_counter IN NUMBER) IS
779 SELECT LPAD(hl.lookup_code,2,' ')||'.'||hl.meaning
780 FROM hr_lookups hl
781 WHERE lookup_type = 'US_EEO5_JOB_CATEGORIES'
782 AND TO_NUMBER(lookup_code) = p_counter
783 AND application_id = 800;
784 l_job_category_name VARCHAR2(200);
785
786
787 BEGIN /*create_record*/
788
789 FOR i IN p_start_code..p_end_code LOOP
790
791 --Need to fetch the job_categroy_name from hr_lookups based on the variable 1.
792
793 l_job_category_name := get_job_category_meaning(i);
794
795
796 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
797 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
798 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name,'D');
799 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
800 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
801 l_xml_string := l_xml_string ||convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',0,'D');
802 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WMALE_EMPS',0,'D');
803 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BMALE_EMPS',0,'D');
804 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HMALE_EMPS',0,'D');
805 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AMALE_EMPS',0,'D');
806 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IMALE_EMPS',0,'D');
807 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WFEMALE_EMPS',0,'D');
808 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BFEMALE_EMPS',0,'D');
809 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HFEMALE_EMPS',0,'D');
810 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AFEMALE_EMPS',0,'D');
811 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IFEMALE_EMPS',0,'D');
812 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
813 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
814 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
815 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
816
817 END LOOP;
818
819 IF p_current_code <> 0 THEN
820 l_job_category_name := get_job_category_meaning(p_current_code);
821 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
822 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
823 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name,'D');
824 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
825 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
826 l_xml_string := l_xml_string ||convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',p_cons_total_category_emps,'D');
827 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WMALE_EMPS',p_no_cons_wmale_emps,'D');
828 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BMALE_EMPS',p_no_cons_bmale_emps,'D');
829 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HMALE_EMPS',p_no_cons_hmale_emps,'D');
830 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AMALE_EMPS',p_no_cons_amale_emps,'D');
831 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IMALE_EMPS',p_no_cons_imale_emps,'D');
832 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_WFEMALE_EMPS',p_no_cons_wfemale_emps,'D');
833 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_BFEMALE_EMPS',p_no_cons_bfemale_emps,'D');
834 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_HFEMALE_EMPS',p_no_cons_hfemale_emps,'D');
835 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_AFEMALE_EMPS',p_no_cons_afemale_emps,'D');
836 l_xml_string := l_xml_string ||convert_into_xml('NO_CONS_IFEMALE_EMPS',p_no_cons_ifemale_emps,'D');
840 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
837 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
838 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
839 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
841 END IF;
842
843 END create_record;
844
845 BEGIN /* generate_xml_data */
846 l_xml_string := '<?xml version="1.0"?> <PQHEEO5>';
847
848
849 --Step 1 : Write Business Group Details
850 /*
851 <LIST_G_JURISDICTION_DETAIL>
852 <G_JURISDICTION_DETAIL>
853 <CITY_STATE_ZIP>San Francisco,San Francisco,CA,94100-1234</CITY_STATE_ZIP>
854 <BUSINESS_NAME>MM_RT BG2</BUSINESS_NAME>
855 <ADDRESS>314 Maple Street Suite 1000</ADDRESS>
856 </G_JURISDICTION_DETAIL>
857 </LIST_G_JURISDICTION_DETAIL>
858 */
859
860 l_xml_string := l_xml_string ||'<LIST_G_JURISDICTION_DETAIL>';
861 l_xml_string := l_xml_string ||'<G_JURISDICTION_DETAIL>';
862
863 OPEN csr_district_id(p_business_group_id);
864 FETCH csr_district_id INTO l_district_id;
865 CLOSE csr_district_id;
866
867 OPEN csr_bg_details(p_business_group_id);
868 FETCH csr_bg_details INTO l_bg_name,l_location_id;
869 CLOSE csr_bg_details;
870
871 OPEN csr_bg_location(l_location_id);
872 FETCH csr_bg_location INTO l_bg_address,l_bg_city, l_bg_county,l_bg_state,l_bg_zip_code;
873 CLOSE csr_bg_location;
874
875
876 l_district_name := l_bg_name;
877 l_xml_string := l_xml_string ||convert_into_xml('BUSINESS_NAME',l_bg_name,'D');
878 l_xml_string := l_xml_string ||convert_into_xml('BUSINESS_DISTRICT_ID',l_district_id,'D');
879 l_xml_string := l_xml_string ||convert_into_xml('ADDRESS',l_bg_address,'D');
880 l_xml_string := l_xml_string ||convert_into_xml('CITY',l_bg_city,'D');
881 l_xml_string := l_xml_string ||convert_into_xml('COUNTY',l_bg_county,'D');
882 l_xml_string := l_xml_string ||convert_into_xml('STATE',l_bg_state,'D');
883 l_xml_string := l_xml_string ||convert_into_xml('ZIP_CODE',l_bg_zip_code,'D');
884
885 l_xml_string := l_xml_string ||'</G_JURISDICTION_DETAIL>';
886 l_xml_string := l_xml_string ||'</LIST_G_JURISDICTION_DETAIL>';
887 write_to_concurrent_out(l_xml_string);
888
889
890 --Step 2 :
891 /*
892 <LIST_G_CERT_OFFICER_NAME>
893 <G_CERT_OFFICER_NAME>
894 <SYSTEM_DISTRICT>DISTRICT</SYSTEM_DISTRICT>
895 <TYPE_REPORT>DISTRICT SUMMARY</TYPE_REPORT>
896 <CONTROL_NUMBER>1234567890</CONTROL_NUMBER>
897 <CERT_OFFICER_NAME></CERT_OFFICER_NAME>
898 <CERT_OFFICIAL_TITLE></CERT_OFFICIAL_TITLE>
899 <CONTACT_TELEPHONE></CONTACT_TELEPHONE>
900 </G_CERT_OFFICER_NAME>
901 </LIST_G_CERT_OFFICER_NAME>
902 */
903
904 /* Resetting l_xml_string */
905 l_xml_string := '<LIST_G_CERT_OFFICER_NAME>';
906 l_xml_string := l_xml_string||'<G_CERT_OFFICER_NAME>';
907
908 FOR i IN csr_cert_officer_details(p_business_group_id) LOOP
909 l_xml_string := l_xml_string ||convert_into_xml('SYSTEM_DISTRICT',i.system_district,'D');
910 l_xml_string := l_xml_string ||convert_into_xml('TYPE_REPORT',i.type_report,'D');
911 l_xml_string := l_xml_string ||convert_into_xml('CONTROL_NUMBER',i.control_number,'D');
912 IF ((i.cert_officer_name IS NOT NULL) AND (i.cert_official_title IS NOT NULL) ) THEN
913 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICER_NAME',i.cert_officer_name||'/','D');
914 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICIAL_TITLE',i.cert_official_title,'D');
915 ELSE
916 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICER_NAME',i.cert_officer_name,'D');
917 l_xml_string := l_xml_string ||convert_into_xml('CERT_OFFICIAL_TITLE',i.cert_official_title,'D');
918 END IF;
919 l_xml_string := l_xml_string ||convert_into_xml('CONTACT_TELEPHONE',i.contact_telephone,'D');
920 -- l_xml_string := l_xml_string||convert_into_xml('CP_REPORT_DATE',l_report_date,'D');
921 END LOOP;
922
923 l_xml_string := l_xml_string||'</G_CERT_OFFICER_NAME>';
924 l_xml_string := l_xml_string||'</LIST_G_CERT_OFFICER_NAME>';
925 write_to_concurrent_out(l_xml_string);
926
927
928 --Step 3 :
929 /*
930 <LIST_G_EMPLOYMENT_CATEGORY>
931 <G_EMPLOYMENT_CATEGORY>
932 <EMPLOYEE_SALARY_EMPLOYMENT_CAT>A. FULL TIME STAFF</EMPLOYEE_SALARY_EMPLOYMENT_CAT>
933 <LIST_G_JOB_CATEGORIES>
934 <G_JOB_CATEGORIES>
935 <JOB_CATEGORY_MEANING>2.PRIN</JOB_CATEGORY_MEANING>
936 <LIST_G_JOB_INFORMATION>
937 <G_JOB_INFORMATION>
938 <CONS_TOTAL_CATEGORY_EMPS>2</CONS_TOTAL_CATEGORY_EMPS>
939 <NO_CONS_WMALE_EMPS>1</NO_CONS_WMALE_EMPS>
940 <NO_CONS_BMALE_EMPS>0</NO_CONS_BMALE_EMPS>
941 <NO_CONS_HMALE_EMPS>0</NO_CONS_HMALE_EMPS>
942 <NO_CONS_AMALE_EMPS>0</NO_CONS_AMALE_EMPS>
943 <NO_CONS_IMALE_EMPS>0</NO_CONS_IMALE_EMPS>
944 <NO_CONS_WFEMALE_EMPS>1</NO_CONS_WFEMALE_EMPS>
945 <NO_CONS_BFEMALE_EMPS>0</NO_CONS_BFEMALE_EMPS>
946 <NO_CONS_HFEMALE_EMPS>0</NO_CONS_HFEMALE_EMPS>
947 <NO_CONS_AFEMALE_EMPS>0</NO_CONS_AFEMALE_EMPS>
948 <NO_CONS_IFEMALE_EMPS>0</NO_CONS_IFEMALE_EMPS>
949 </G_JOB_INFORMATION>
950 </LIST_G_JOB_INFORMATION>
951 </G_JOB_CATEGORIES>
952 </LIST_G_JOB_CATEGORIES>
953 <LIST_G_JOB_CATEGORIES>
954 <G_JOB_CATEGORIES>
955 <JOB_CATEGORY_MEANING>3.</JOB_CATEGORY_MEANING>
956 <LIST_G_JOB_INFORMATION>
957 <G_JOB_INFORMATION>
958 .....
959 .....
960 </G_JOB_INFORMATION>
961 </LIST_G_JOB_INFORMATION>
965 <CS_NO_WMALE_EMPS>1</CS_NO_WMALE_EMPS>
962 </G_JOB_CATEGORIES>
963 </LIST_G_JOB_CATEGORIES>
964 <CF_TOTAL_TITLE>19.TOTAL</CF_TOTAL_TITLE>
966 <CS_NO_BMALE_EMPS>1</CS_NO_BMALE_EMPS>
967 <CS_NO_HMALE_EMPS>0</CS_NO_HMALE_EMPS>
968 <CS_NO_AMALE_EMPS>1</CS_NO_AMALE_EMPS>
969 <CS_NO_IMALE_EMPS>0</CS_NO_IMALE_EMPS>
970 <CS_NO_WFEMALE_EMPS>1</CS_NO_WFEMALE_EMPS>
971 <CS_NO_BFEMALE_EMPS>0</CS_NO_BFEMALE_EMPS>
972 <CS_NO_HFEMALE_EMPS>0</CS_NO_HFEMALE_EMPS>
973 <CS_NO_AFEMALE_EMPS>2</CS_NO_AFEMALE_EMPS>
974 <CS_NO_IFEMALE_EMPS>1</CS_NO_IFEMALE_EMPS>
975 <CS_TOTAL_CATEGORY_EMPS>7</CS_TOTAL_CATEGORY_EMPS>
976 </G_EMPLOYMENT_CATEGORY>
977 <G_EMPLOYMENT_CATEGORY>
978 <EMPLOYEE_SALARY_EMPLOYMENT_CAT>B. PART-TIME STAFF</EMPLOYEE_SALARY_EMPLOYMENT_CAT>
979 </LIST_G_EMPLOYMENT_CATEGORY>
980 */
981
982
983 /* Resetting l_xml_string */
984 l_xml_string := '<LIST_G_EMPLOYMENT_CATEGORY>';
985 l_xml_string := l_xml_string||'<G_EMPLOYMENT_CATEGORY>';
986
987 pqh_employment_category.fetch_empl_categories(p_business_group_id
988 ,l_fr
989 ,l_ft
990 ,l_pr
991 ,l_pt);
992
993 hr_utility.set_location('=======in EEO5 per_us_eeo5_pkg==========='||l_fr, 5);
994 hr_utility.set_location('l_fr -> '||l_fr, 5);
995 hr_utility.set_location('l_fr -> '||l_ft, 5);
996 hr_utility.set_location('l_fr -> '||l_pr, 5);
997 hr_utility.set_location('l_fr -> '||l_pt, 5);
998 hr_utility.set_location('=======in EEO5 per_us_eeo5_pkg==========='||l_fr, 5);
999
1000 --l_report_date is always for September.
1001 l_report_date := TO_DATE('30-09'||'-'||p_reporting_year,'DD-MM-RRRR');
1002 l_xml_string := l_xml_string ||convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','A. FULL-TIME STAFF','D');
1003 l_start_code := 1;
1004 l_end_code := 1;
1005 l_current_code := 1;
1006
1007 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
1008 --employment_category = 'A. FULL TIME STAFF' THEN
1009 l_current_code := TO_NUMBER(SUBSTR(i.job_category_name,1,2));
1010 l_end_code := l_current_code - 1;
1011
1012 /* Commented for bug#11736960 starts
1013 -- People with Two or More race are considered in the modified csr_full_time_details cursor.
1014 -- Get the person counts with ethnic code 'Two or more races'
1015 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);
1016 FETCH csr_tmr_ft_details INTO no_tmraces_wmale_emps,
1017 no_tmraces_bmale_emps,
1018 no_tmraces_hmale_emps,
1019 no_tmraces_amale_emps,
1020 no_tmraces_imale_emps,
1021 no_tmraces_wfemale_emps,
1022 no_tmraces_bfemale_emps,
1023 no_tmraces_hfemale_emps,
1024 no_tmraces_afemale_emps,
1025 no_tmraces_ifemale_emps;
1026 CLOSE csr_tmr_ft_details;
1027 Commented for bug#11736960 ends */
1028
1029 -- Added for bug#11736960
1030 l_total_category_employees := i.cons_total_category_emps;
1031 /* Commented for bug#11736960 starts
1032 l_total_category_employees := i.cons_total_category_emps +
1033 no_tmraces_wmale_emps +
1034 no_tmraces_bmale_emps +
1035 no_tmraces_hmale_emps +
1036 no_tmraces_amale_emps +
1037 no_tmraces_imale_emps +
1038 no_tmraces_wfemale_emps +
1039 no_tmraces_bfemale_emps +
1040 no_tmraces_hfemale_emps +
1041 no_tmraces_afemale_emps +
1042 no_tmraces_ifemale_emps;
1043 Commented for bug#11736960 ends */
1044
1045 --Current record needs to be written as XML to FND OUT.
1046 -- Added for bug#11736960
1047 create_record(l_start_code
1048 ,l_end_code
1049 ,l_current_code
1050 ,i.job_category_name
1051 ,l_total_category_employees
1052 ,i.no_cons_wmale_emps
1053 ,i.no_cons_bmale_emps
1054 ,i.no_cons_hmale_emps
1055 ,i.no_cons_amale_emps
1056 ,i.no_cons_imale_emps
1057 ,i.no_cons_wfemale_emps
1058 ,i.no_cons_bfemale_emps
1059 ,i.no_cons_hfemale_emps
1060 ,i.no_cons_afemale_emps
1061 ,i.no_cons_ifemale_emps);
1062 /* Commented for bug#11736960 starts
1063 create_record(l_start_code
1064 ,l_end_code
1065 ,l_current_code
1066 ,i.job_category_name
1067 ,l_total_category_employees
1068 ,i.no_cons_wmale_emps + no_tmraces_wmale_emps
1069 ,i.no_cons_bmale_emps + no_tmraces_bmale_emps
1070 ,i.no_cons_hmale_emps + no_tmraces_hmale_emps
1071 ,i.no_cons_amale_emps + no_tmraces_amale_emps
1072 ,i.no_cons_imale_emps + no_tmraces_imale_emps
1073 ,i.no_cons_wfemale_emps + no_tmraces_wfemale_emps
1074 ,i.no_cons_bfemale_emps + no_tmraces_bfemale_emps
1075 ,i.no_cons_hfemale_emps + no_tmraces_hfemale_emps
1076 ,i.no_cons_afemale_emps + no_tmraces_afemale_emps
1077 ,i.no_cons_ifemale_emps + no_tmraces_ifemale_emps);
1078 Commented for bug#11736960 ends */
1079
1080 -- Bug# 6242997
1081 l_sum_cons_total_category_emps := l_sum_cons_total_category_emps + l_total_category_employees;
1082 -- Added for bug#11736960
1083 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps;
1084 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps;
1088 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps;
1085 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps;
1086 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps;
1087 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps;
1089 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps;
1090 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps;
1091 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps;
1092 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps;
1093 /* Commented for bug#11736960 starts
1094 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps + no_tmraces_wmale_emps;
1095 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps + no_tmraces_bmale_emps;
1096 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps + no_tmraces_hmale_emps;
1097 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps + no_tmraces_amale_emps;
1098 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps + no_tmraces_imale_emps;
1099 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps + no_tmraces_wfemale_emps;
1100 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps + no_tmraces_bfemale_emps;
1101 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps + no_tmraces_hfemale_emps;
1102 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps + no_tmraces_afemale_emps;
1103 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps + no_tmraces_ifemale_emps;
1104 Commented for bug#11736960 ends */
1105 hr_utility_trace('==================================================');
1106 hr_utility_trace(' i.job_category_name -> ' || i.job_category_name);
1107 hr_utility_trace(' l_start_code -> ' || l_start_code);
1108 hr_utility_trace(' l_end_code -> ' || l_end_code);
1109 hr_utility_trace(' l_current_code -> ' || l_current_code);
1110 hr_utility_trace('==================================================');
1111 l_start_code := l_current_code+1;
1112 END LOOP;/* csr_full_time_details */
1113
1114 hr_utility_trace('==================================================');
1115 hr_utility_trace(' Out of the Loop -> ');
1116 hr_utility_trace(' length(l_xml_string) -> ' || length(l_xml_string));
1117 hr_utility_trace(' l_start_code -> ' || l_start_code);
1118 hr_utility_trace(' l_end_code -> ' || l_end_code);
1119 hr_utility_trace(' l_current_code -> ' || l_current_code);
1120 hr_utility_trace('==================================================');
1121
1122 IF l_start_code = 1 THEN
1123 -- Cursor csr_full_time_details did not fetch any records.
1124 l_end_code := 18;
1125 create_record(l_start_code
1126 ,l_end_code
1127 ,0
1128 ,0
1129 ,0
1130 ,0
1131 ,0
1132 ,0
1133 ,0
1134 ,0
1135 ,0
1136 ,0
1137 ,0
1138 ,0
1139 ,0);
1140 l_sum_cons_total_category_emps := 0;
1141 l_sum_no_cons_wmale_emps := 0;
1142 l_sum_no_cons_bmale_emps := 0;
1143 l_sum_no_cons_hmale_emps := 0;
1144 l_sum_no_cons_amale_emps := 0;
1145 l_sum_no_cons_imale_emps := 0;
1146 l_sum_no_cons_wfemale_emps := 0;
1147 l_sum_no_cons_bfemale_emps := 0;
1148 l_sum_no_cons_hfemale_emps := 0;
1149 l_sum_no_cons_afemale_emps := 0;
1150 l_sum_no_cons_ifemale_emps := 0;
1151
1152 ELSIF l_start_code > 1 THEN
1153
1154 --Cursor has fetched some data.
1155 l_end_code := 18;
1156 create_record(l_start_code
1157 ,l_end_code
1158 ,0
1159 ,0
1160 ,0
1161 ,0
1162 ,0
1163 ,0
1164 ,0
1165 ,0
1166 ,0
1167 ,0
1168 ,0
1169 ,0
1170 ,0);
1171 END IF; /* l_start_code = 1 */
1172
1173 l_job_category_name_main := get_job_category_meaning(19);
1174 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
1175 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
1176 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name_main,'D');
1177 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
1178 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
1179 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',l_sum_no_cons_wmale_emps,'D');
1180 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',l_sum_no_cons_bmale_emps,'D');
1181 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',l_sum_no_cons_hmale_emps,'D');
1182 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',l_sum_no_cons_amale_emps,'D');
1183 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',l_sum_no_cons_imale_emps,'D');
1184 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',l_sum_no_cons_wfemale_emps,'D');
1185 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',l_sum_no_cons_bfemale_emps,'D');
1186 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',l_sum_no_cons_hfemale_emps,'D');
1187 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',l_sum_no_cons_afemale_emps,'D');
1188 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',l_sum_no_cons_ifemale_emps,'D');
1189 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',l_sum_cons_total_category_emps,'D');
1190 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
1191 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
1192 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
1193 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
1194 l_xml_string := l_xml_string||'</G_EMPLOYMENT_CATEGORY>';
1195
1196 write_to_concurrent_out(l_xml_string);
1197
1198 --End of full time employees. End of Step 3.
1199
1200
1201 --Step 4.
1202 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1203 l_sum_cons_total_category_emps := 0;
1204 l_sum_no_cons_wmale_emps := 0;
1205 l_sum_no_cons_bmale_emps := 0;
1206 l_sum_no_cons_hmale_emps := 0;
1207 l_sum_no_cons_amale_emps := 0;
1208 l_sum_no_cons_imale_emps := 0;
1209 l_sum_no_cons_wfemale_emps := 0;
1210 l_sum_no_cons_bfemale_emps := 0;
1211 l_sum_no_cons_hfemale_emps := 0;
1212 l_sum_no_cons_afemale_emps := 0;
1213 l_sum_no_cons_ifemale_emps := 0;
1214 l_start_code := 20;
1215 l_xml_string := l_xml_string ||convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT','B. PART-TIME STAFF','D');
1216
1217 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
1218 l_current_code := TO_NUMBER(SUBSTR(i.job_category_name,1,2));
1219 l_end_code := l_current_code - 1;
1220 /* Commented for bug#11736960 starts
1221 -- People with Two or More race are considered in the modified csr_part_time_details cursor.
1222 -- Get the person counts with ethnic code 'Two or more races'
1223 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);
1224 FETCH csr_tmr_pt_details INTO no_tmraces_wmale_emps,
1225 no_tmraces_bmale_emps,
1226 no_tmraces_hmale_emps,
1227 no_tmraces_amale_emps,
1228 no_tmraces_imale_emps,
1229 no_tmraces_wfemale_emps,
1230 no_tmraces_bfemale_emps,
1231 no_tmraces_hfemale_emps,
1232 no_tmraces_afemale_emps,
1233 no_tmraces_ifemale_emps;
1234 CLOSE csr_tmr_pt_details;
1235 Commented for bug#11736960 ends */
1236
1237 -- Added for bug#11736960
1238 l_total_category_employees := i.cons_total_category_emps;
1239 /* Commented for bug#11736960 starts
1240 l_total_category_employees := i.cons_total_category_emps +
1241 no_tmraces_wmale_emps +
1242 no_tmraces_bmale_emps +
1243 no_tmraces_hmale_emps +
1244 no_tmraces_amale_emps +
1245 no_tmraces_imale_emps +
1246 no_tmraces_wfemale_emps +
1247 no_tmraces_bfemale_emps +
1248 no_tmraces_hfemale_emps +
1249 no_tmraces_afemale_emps +
1250 no_tmraces_ifemale_emps;
1251 Commented for bug#11736960 ends */
1252
1253 --Current record needs to be written as XML to FND OUT.
1254 -- Added for bug#11736960
1255 create_record(l_start_code
1256 ,l_end_code
1257 ,l_current_code
1258 ,i.job_category_name
1259 ,l_total_category_employees
1260 ,i.no_cons_wmale_emps
1261 ,i.no_cons_bmale_emps
1262 ,i.no_cons_hmale_emps
1263 ,i.no_cons_amale_emps
1264 ,i.no_cons_imale_emps
1265 ,i.no_cons_wfemale_emps
1266 ,i.no_cons_bfemale_emps
1267 ,i.no_cons_hfemale_emps
1268 ,i.no_cons_afemale_emps
1269 ,i.no_cons_ifemale_emps);
1270 /* Commented for bug#11736960 starts
1271 create_record(l_start_code
1272 ,l_end_code
1273 ,l_current_code
1274 ,i.job_category_name
1275 ,l_total_category_employees
1276 ,i.no_cons_wmale_emps + no_tmraces_wmale_emps
1277 ,i.no_cons_bmale_emps + no_tmraces_bmale_emps
1278 ,i.no_cons_hmale_emps + no_tmraces_hmale_emps
1279 ,i.no_cons_amale_emps + no_tmraces_amale_emps
1280 ,i.no_cons_imale_emps + no_tmraces_imale_emps
1281 ,i.no_cons_wfemale_emps + no_tmraces_wfemale_emps
1282 ,i.no_cons_bfemale_emps + no_tmraces_bfemale_emps
1283 ,i.no_cons_hfemale_emps + no_tmraces_hfemale_emps
1284 ,i.no_cons_afemale_emps + no_tmraces_afemale_emps
1285 ,i.no_cons_ifemale_emps + no_tmraces_ifemale_emps);
1286 Commented for bug#11736960 ends */
1287
1288 -- Bug# 6242997
1289 l_sum_cons_total_category_emps := l_sum_cons_total_category_emps + l_total_category_employees;
1290 -- Added for bug#11736960
1291 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps;
1292 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps;
1293 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps;
1294 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps;
1295 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps;
1296 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps;
1297 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps;
1298 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps;
1299 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps;
1300 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps;
1301 /* Commented for bug#11736960 starts
1302 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps + no_tmraces_wmale_emps;
1303 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps + no_tmraces_bmale_emps;
1304 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps + no_tmraces_hmale_emps;
1305 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps + no_tmraces_amale_emps;
1306 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps + no_tmraces_imale_emps;
1307 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps + no_tmraces_wfemale_emps;
1308 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps + no_tmraces_bfemale_emps;
1309 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps + no_tmraces_hfemale_emps;
1310 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps + no_tmraces_afemale_emps;
1311 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps + no_tmraces_ifemale_emps;
1312 Commented for bug#11736960 ends */
1313 l_start_code := l_current_code+1;
1314 END LOOP; /* csr_part_time_details */
1315
1316 l_end_code := 21;
1317 IF l_start_code = 20 THEN
1318 --Cursor has not fetched any data.
1319 create_record(l_start_code --20
1320 ,l_end_code --21
1321 ,0
1322 ,0
1323 ,0
1324 ,0
1325 ,0
1326 ,0
1327 ,0
1328 ,0
1329 ,0
1330 ,0
1331 ,0
1332 ,0
1333 ,0);
1334 l_sum_cons_total_category_emps := 0;
1335 l_sum_no_cons_wmale_emps := 0;
1336 l_sum_no_cons_bmale_emps := 0;
1337 l_sum_no_cons_hmale_emps := 0;
1338 l_sum_no_cons_amale_emps := 0;
1339 l_sum_no_cons_imale_emps := 0;
1340 l_sum_no_cons_wfemale_emps := 0;
1341 l_sum_no_cons_bfemale_emps := 0;
1342 l_sum_no_cons_hfemale_emps := 0;
1343 l_sum_no_cons_afemale_emps := 0;
1344 l_sum_no_cons_ifemale_emps := 0;
1345
1346 ELSIF l_start_code = 21 THEN
1347 l_job_category_name_main := '21.ALL OTHER';
1348 create_record(l_start_code --21
1349 ,l_end_code --21
1350 ,0
1351 ,0
1352 ,0
1353 ,0
1354 ,0
1355 ,0
1356 ,0
1357 ,0
1358 ,0
1359 ,0
1360 ,0
1361 ,0
1362 ,0);
1363 END IF;
1364
1365
1366 l_job_category_name_main := get_job_category_meaning(22);
1367 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
1368 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
1369 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name_main,'D');
1370 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
1371 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
1372 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',l_sum_no_cons_wmale_emps,'D');
1373 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',l_sum_no_cons_bmale_emps,'D');
1374 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',l_sum_no_cons_hmale_emps,'D');
1375 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',l_sum_no_cons_amale_emps,'D');
1376 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',l_sum_no_cons_imale_emps,'D');
1377 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',l_sum_no_cons_wfemale_emps,'D');
1378 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',l_sum_no_cons_bfemale_emps,'D');
1379 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',l_sum_no_cons_hfemale_emps,'D');
1380 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',l_sum_no_cons_afemale_emps,'D');
1381 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',l_sum_no_cons_ifemale_emps,'D');
1382 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',l_sum_cons_total_category_emps,'D');
1383 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
1384 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
1385 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
1386 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
1387 l_xml_string := l_xml_string||'</G_EMPLOYMENT_CATEGORY>';
1388 -- l_xml_string := l_xml_string||'</LIST_G_EMPLOYMENT_CATEGORY> </PQHEEO5>';
1389
1390 write_to_concurrent_out(l_xml_string);
1391
1392
1393 --Step 5: Codes 23 to 27
1394 l_sum_cons_total_category_emps := 0;
1395 l_sum_no_cons_wmale_emps := 0;
1396 l_sum_no_cons_bmale_emps := 0;
1397 l_sum_no_cons_hmale_emps := 0;
1398 l_sum_no_cons_amale_emps := 0;
1399 l_sum_no_cons_imale_emps := 0;
1400 l_sum_no_cons_wfemale_emps := 0;
1401 l_sum_no_cons_bfemale_emps := 0;
1402 l_sum_no_cons_hfemale_emps := 0;
1403 l_sum_no_cons_afemale_emps := 0;
1404 l_sum_no_cons_ifemale_emps := 0;
1405 l_xml_string := '<G_EMPLOYMENT_CATEGORY>';
1406 l_start_code := 23;
1407 l_new_hires_heading := 'C. NEW HIRES (JULY THRU SEPT. '|| p_reporting_year ||')';
1408 l_xml_string := l_xml_string ||convert_into_xml('EMPLOYEE_SALARY_EMPLOYMENT_CAT',l_new_hires_heading,'D');
1409
1410 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
1411 l_current_code := TO_NUMBER(SUBSTR(i.job_category_name,1,2));
1412 l_end_code := l_current_code - 1;
1413
1414 /* Commented for bug#11736960 starts
1415 -- People with Two or More race are considered in the modified csr_new_hires_details cursor.
1416 -- Get the person counts with ethnic code 'Two or more races'
1417 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);
1418 FETCH csr_tmr_nh_details INTO no_tmraces_wmale_emps,
1419 no_tmraces_bmale_emps,
1420 no_tmraces_hmale_emps,
1421 no_tmraces_amale_emps,
1422 no_tmraces_imale_emps,
1423 no_tmraces_wfemale_emps,
1424 no_tmraces_bfemale_emps,
1425 no_tmraces_hfemale_emps,
1426 no_tmraces_afemale_emps,
1427 no_tmraces_ifemale_emps;
1428 CLOSE csr_tmr_nh_details;
1429 Commented for bug#11736960 ends */
1430
1431 -- Added for bug#11736960
1432 l_total_category_employees := i.cons_total_category_emps;
1433 /* Commented for bug#11736960 starts
1434 l_total_category_employees := i.cons_total_category_emps +
1435 no_tmraces_wmale_emps +
1436 no_tmraces_bmale_emps +
1437 no_tmraces_hmale_emps +
1438 no_tmraces_amale_emps +
1439 no_tmraces_imale_emps +
1440 no_tmraces_wfemale_emps +
1441 no_tmraces_bfemale_emps +
1442 no_tmraces_hfemale_emps +
1443 no_tmraces_afemale_emps +
1444 no_tmraces_ifemale_emps;
1445 Commented for bug#11736960 ends */
1446
1447 --Current record needs to be written as XML to FND OUT.
1448 -- Added for bug#11736960
1449 create_record(l_start_code
1450 ,l_end_code
1451 ,l_current_code
1452 ,i.job_category_name
1453 ,l_total_category_employees
1454 ,i.no_cons_wmale_emps
1455 ,i.no_cons_bmale_emps
1456 ,i.no_cons_hmale_emps
1457 ,i.no_cons_amale_emps
1458 ,i.no_cons_imale_emps
1459 ,i.no_cons_wfemale_emps
1460 ,i.no_cons_bfemale_emps
1461 ,i.no_cons_hfemale_emps
1462 ,i.no_cons_afemale_emps
1463 ,i.no_cons_ifemale_emps);
1464 /* Commented for bug#11736960 starts
1465 create_record(l_start_code
1466 ,l_end_code
1467 ,l_current_code
1468 ,i.job_category_name
1469 ,l_total_category_employees
1470 ,i.no_cons_wmale_emps + no_tmraces_wmale_emps
1471 ,i.no_cons_bmale_emps + no_tmraces_bmale_emps
1472 ,i.no_cons_hmale_emps + no_tmraces_hmale_emps
1473 ,i.no_cons_amale_emps + no_tmraces_amale_emps
1474 ,i.no_cons_imale_emps + no_tmraces_imale_emps
1475 ,i.no_cons_wfemale_emps + no_tmraces_wfemale_emps
1476 ,i.no_cons_bfemale_emps + no_tmraces_bfemale_emps
1480 Commented for bug#11736960 ends */
1477 ,i.no_cons_hfemale_emps + no_tmraces_hfemale_emps
1478 ,i.no_cons_afemale_emps + no_tmraces_afemale_emps
1479 ,i.no_cons_ifemale_emps + no_tmraces_ifemale_emps);
1481
1482 -- Bug# 6242997
1483 l_sum_cons_total_category_emps := l_sum_cons_total_category_emps + l_total_category_employees;
1484 -- Added for bug#11736960
1485 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps;
1486 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps;
1487 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps;
1488 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps;
1489 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps;
1490 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps;
1491 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps;
1492 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps;
1493 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps;
1494 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps;
1495 /* Commented for bug#11736960 starts
1496 l_sum_no_cons_wmale_emps := l_sum_no_cons_wmale_emps + i.no_cons_wmale_emps + no_tmraces_wmale_emps;
1497 l_sum_no_cons_bmale_emps := l_sum_no_cons_bmale_emps + i.no_cons_bmale_emps + no_tmraces_bmale_emps;
1498 l_sum_no_cons_hmale_emps := l_sum_no_cons_hmale_emps + i.no_cons_hmale_emps + no_tmraces_hmale_emps;
1499 l_sum_no_cons_amale_emps := l_sum_no_cons_amale_emps + i.no_cons_amale_emps + no_tmraces_amale_emps;
1500 l_sum_no_cons_imale_emps := l_sum_no_cons_imale_emps + i.no_cons_imale_emps + no_tmraces_imale_emps;
1501 l_sum_no_cons_wfemale_emps := l_sum_no_cons_wfemale_emps + i.no_cons_wfemale_emps + no_tmraces_wfemale_emps;
1502 l_sum_no_cons_bfemale_emps := l_sum_no_cons_bfemale_emps + i.no_cons_bfemale_emps + no_tmraces_bfemale_emps;
1503 l_sum_no_cons_hfemale_emps := l_sum_no_cons_hfemale_emps + i.no_cons_hfemale_emps + no_tmraces_hfemale_emps;
1504 l_sum_no_cons_afemale_emps := l_sum_no_cons_afemale_emps + i.no_cons_afemale_emps + no_tmraces_afemale_emps;
1505 l_sum_no_cons_ifemale_emps := l_sum_no_cons_ifemale_emps + i.no_cons_ifemale_emps + no_tmraces_ifemale_emps;
1506 Commented for bug#11736960 ends */
1507 l_start_code := l_current_code+1;
1508 END LOOP; /* csr_new_hires_details */
1509
1510 l_end_code := 27;
1511 IF l_start_code = 23 THEN
1512 --Cursor has not fetched any data.
1513 create_record(l_start_code
1514 ,l_end_code
1515 ,0
1516 ,0
1517 ,0
1518 ,0
1519 ,0
1520 ,0
1521 ,0
1522 ,0
1523 ,0
1524 ,0
1525 ,0
1526 ,0
1527 ,0);
1528 l_sum_cons_total_category_emps := 0;
1529 l_sum_no_cons_wmale_emps := 0;
1530 l_sum_no_cons_bmale_emps := 0;
1531 l_sum_no_cons_hmale_emps := 0;
1532 l_sum_no_cons_amale_emps := 0;
1533 l_sum_no_cons_imale_emps := 0;
1534 l_sum_no_cons_wfemale_emps := 0;
1535 l_sum_no_cons_bfemale_emps := 0;
1536 l_sum_no_cons_hfemale_emps := 0;
1537 l_sum_no_cons_afemale_emps := 0;
1538 l_sum_no_cons_ifemale_emps := 0;
1539
1540 ELSIF l_start_code > 23 THEN
1541 --Cursor has fetched some data.
1542 l_end_code := 27;
1543 create_record(l_start_code
1544 ,l_end_code
1545 ,0
1546 ,0
1547 ,0
1548 ,0
1549 ,0
1550 ,0
1551 ,0
1552 ,0
1553 ,0
1554 ,0
1555 ,0
1556 ,0
1557 ,0);
1558 END IF;
1559
1560 l_job_category_name_main := get_job_category_meaning(28);
1561 l_xml_string := l_xml_string ||'<LIST_G_JOB_CATEGORIES>';
1562 l_xml_string := l_xml_string ||'<G_JOB_CATEGORIES>';
1563 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_MEANING',l_job_category_name_main,'D');
1564 l_xml_string := l_xml_string ||'<LIST_G_JOB_INFORMATION>';
1565 l_xml_string := l_xml_string ||'<G_JOB_INFORMATION>';
1566 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WMALE_EMPS',l_sum_no_cons_wmale_emps,'D');
1567 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BMALE_EMPS',l_sum_no_cons_bmale_emps,'D');
1568 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HMALE_EMPS',l_sum_no_cons_hmale_emps,'D');
1569 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AMALE_EMPS',l_sum_no_cons_amale_emps,'D');
1570 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IMALE_EMPS',l_sum_no_cons_imale_emps,'D');
1571 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_WFEMALE_EMPS',l_sum_no_cons_wfemale_emps,'D');
1572 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_BFEMALE_EMPS',l_sum_no_cons_bfemale_emps,'D');
1573 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_HFEMALE_EMPS',l_sum_no_cons_hfemale_emps,'D');
1574 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_AFEMALE_EMPS',l_sum_no_cons_afemale_emps,'D');
1575 l_xml_string := l_xml_string || convert_into_xml('NO_CONS_IFEMALE_EMPS',l_sum_no_cons_ifemale_emps,'D');
1576 l_xml_string := l_xml_string || convert_into_xml('CONS_TOTAL_CATEGORY_EMPS',l_sum_cons_total_category_emps,'D');
1577 l_xml_string := l_xml_string ||'</G_JOB_INFORMATION>';
1578 l_xml_string := l_xml_string ||'</LIST_G_JOB_INFORMATION>';
1579 l_xml_string := l_xml_string ||'</G_JOB_CATEGORIES>';
1580 l_xml_string := l_xml_string ||'</LIST_G_JOB_CATEGORIES>';
1581 l_xml_string := l_xml_string||'</G_EMPLOYMENT_CATEGORY>';
1582 l_xml_string := l_xml_string||'</LIST_G_EMPLOYMENT_CATEGORY>';
1583
1584 write_to_concurrent_out(l_xml_string);
1585
1586 --End of Step 5.
1587 /*Final Step :
1588 <CF_NO_OF_ANNEXES>3</CF_NO_OF_ANNEXES>
1589 <CP_REPORT_DATE>30-SEP-05</CP_REPORT_DATE>
1590 <CP_NO_OF_SCHOOLS>6</CP_NO_OF_SCHOOLS>
1591 <CP_FR>'FR'</CP_FR>
1592 <CP_FT>'FT'</CP_FT>
1593 <CP_PR>'PR'</CP_PR>
1594 <CP_PT>'PT'</CP_PT>
1595 </PQHEEO5> */
1596
1597 OPEN csr_annexes( l_report_date , p_business_group_id);
1598 FETCH csr_annexes INTO l_count_annexes;
1599 CLOSE csr_annexes;
1600
1601 OPEN csr_schools( p_business_group_id);
1602 FETCH csr_schools INTO l_count_schools;
1603 CLOSE csr_schools;
1604
1605 l_char_report_date := '30-SEP'||'-'||p_reporting_year;
1606
1607 l_xml_string := convert_into_xml('CF_NO_OF_ANNEXES',l_count_annexes,'D');
1608 l_xml_string := l_xml_string||convert_into_xml('CP_REPORT_DATE',l_char_report_date,'D');
1609 l_xml_string := l_xml_string||convert_into_xml('CP_NO_OF_SCHOOLS',l_count_schools,'D');
1610 --Added for bug 5404884
1611 l_xml_string := l_xml_string||convert_into_xml('P_TOTAL_ENROLLMENTS',p_total_enrollments,'D');
1612 --End of changes done for bug 5404884.
1613 --Added for bug 5415393
1614 l_xml_string := l_xml_string||convert_into_xml('DISTRICT_NAME',l_district_name,'D');
1615 l_xml_string := l_xml_string||convert_into_xml('DISTRICT_ID',l_district_id,'D');
1616 --End bug 5415393
1617 l_xml_string := l_xml_string||convert_into_xml('CP_FR','FR','D');
1618 l_xml_string := l_xml_string||convert_into_xml('CP_FT','FT','D');
1619 l_xml_string := l_xml_string||convert_into_xml('CP_PR','PR','D');
1620 l_xml_string := l_xml_string||convert_into_xml('CP_PT','PT','D');
1621 l_xml_string := l_xml_string||'</PQHEEO5>';
1622
1623 write_to_concurrent_out(l_xml_string);
1624
1625
1626 END generate_xml_data;
1627
1628
1629 --To put the trace just uncomment the below three lines.
1630 --BEGIN
1631 -- hr_utility.trace_on(null,'EEO5');
1632 -- g_debug := hr_utility.debug_enabled;
1633 END per_us_eeo5_pkg;