[Home] [Help]
PACKAGE BODY: APPS.PER_EEO_MAG_REPORT
Source
4 g_package VARCHAR2(33) := ' per_eeo_mag_report.'; -- Global package name
1 PACKAGE BODY per_eeo_mag_report AS
2 /* $Header: peeeomag.pkb 120.22.12020000.3 2012/07/05 02:20:59 amnaraya ship $ */
3
5
6 TYPE org_rec IS RECORD
7 (org_name VARCHAR2(20) DEFAULT ' ',
8 company_number_1 VARCHAR2(27) DEFAULT ' ',
9 l_status_code_2 VARCHAR2(5) DEFAULT ' ',
10 form_type VARCHAR2(5),
11 c1_over_100_13 VARCHAR2(5) DEFAULT ' ',
12 c2_affiliated_14 VARCHAR2(5) DEFAULT ' ',
13 gov_contract_15 VARCHAR2(1) DEFAULT ' ',
14 duns_16 VARCHAR2(20) DEFAULT ' ',
15 l_d1_payroll_period_18 VARCHAR2(21) DEFAULT ' ',
16 apprentices_emp_19 VARCHAR2(1) DEFAULT ' ',
17 sic_20 VARCHAR2(14) DEFAULT ' ',
18 naics_21 VARCHAR2(16) DEFAULT ' ',
19 title_cert_off_22 VARCHAR2(50) DEFAULT ' ',
20 name_cert_off_23 VARCHAR2(50) DEFAULT ' ',
21 tel_num_24 VARCHAR2(20) DEFAULT ' ',
22 fax_num_25 VARCHAR2(10) DEFAULT ' ',
26
23 email_26 VARCHAR2(40) DEFAULT ' ',
24 par_ent_org_id number(15) DEFAULT NULL
25 );
27 l_org_rec org_rec;
28
29 TYPE con_rec IS RECORD
30 (a_1_total_mfc NUMBER DEFAULT 0
31 );
32 l_con_rec con_rec;
33
34
35 TYPE estab_rec IS RECORD
36 (unit_number_3 VARCHAR2(50) DEFAULT ' ',
37 unit_name_4 VARCHAR2(70) DEFAULT ' ',
38 unit_address_req_5 VARCHAR2(200) DEFAULT ' ',
39 unit_address_6 VARCHAR2(200) DEFAULT ' ',
40 city_7 VARCHAR2(50) DEFAULT ' ',
41 state_8 VARCHAR2(40) DEFAULT ' ',
42 zip_code_9 VARCHAR2(40) DEFAULT ' ',
43 zip_code_last_4_10 VARCHAR2(4) DEFAULT ' ',
44 reported_last_year_11 VARCHAR2(1) DEFAULT '2',
45 ein_12 VARCHAR2(19) DEFAULT ' ',
46 gov_contract_15 VARCHAR2(1) DEFAULT ' ',
47 duns_16 VARCHAR2(20) DEFAULT ' ',
48 county_17 VARCHAR2(38) DEFAULT ' ',
49 apprentices_emp_19 VARCHAR2(1) DEFAULT ' ',
50 sic_20 VARCHAR2(14) DEFAULT ' ',
51 naics_21 VARCHAR2(16) DEFAULT ' ',
52 hq VARCHAR2(1) DEFAULT ' ',
53 max_count VARCHAR2(1) DEFAULT ' ',
54 --
55 a_1_hl_male NUMBER(6) DEFAULT 0,
56 b_1_hl_female NUMBER(6) DEFAULT 0,
57 c_1_white_male NUMBER(6) DEFAULT 0,
58 d_1_black_male NUMBER(6) DEFAULT 0,
59 e_1_latin_male NUMBER(6) DEFAULT 0,
60 f_1_aspac_male NUMBER(6) DEFAULT 0,
61 g_1_ameri_male NUMBER(6) DEFAULT 0,
62 h_1_tmraces_male NUMBER(6) DEFAULT 0,
63 i_1_white_fem NUMBER(6) DEFAULT 0,
64 j_1_black_fem NUMBER(6) DEFAULT 0,
65 k_1_latin_fem NUMBER(6) DEFAULT 0,
66 l_1_aspac_fem NUMBER(6) DEFAULT 0,
67 m_1_ameri_fem NUMBER(6) DEFAULT 0,
68 n_1_tmraces_female NUMBER(6) DEFAULT 0,
69 o_1_total_cat NUMBER(7) DEFAULT 0,
70 --
71 a_2_hl_male NUMBER(6) DEFAULT 0,
72 b_2_hl_female NUMBER(6) DEFAULT 0,
73 c_2_white_male NUMBER(6) DEFAULT 0,
74 d_2_black_male NUMBER(6) DEFAULT 0,
75 e_2_latin_male NUMBER(6) DEFAULT 0,
76 f_2_aspac_male NUMBER(6) DEFAULT 0,
77 g_2_ameri_male NUMBER(6) DEFAULT 0,
78 h_2_tmraces_male NUMBER(6) DEFAULT 0,
79 i_2_white_fem NUMBER(6) DEFAULT 0,
80 j_2_black_fem NUMBER(6) DEFAULT 0,
81 k_2_latin_fem NUMBER(6) DEFAULT 0,
82 l_2_aspac_fem NUMBER(6) DEFAULT 0,
83 m_2_ameri_fem NUMBER(6) DEFAULT 0,
84 n_2_tmraces_female NUMBER(6) DEFAULT 0,
85 o_2_total_cat NUMBER(7) DEFAULT 0,
86 --
87 a_3_hl_male NUMBER(6) DEFAULT 0,
88 b_3_hl_female NUMBER(6) DEFAULT 0,
89 c_3_white_male NUMBER(6) DEFAULT 0,
90 d_3_black_male NUMBER(6) DEFAULT 0,
91 e_3_latin_male NUMBER(6) DEFAULT 0,
92 f_3_aspac_male NUMBER(6) DEFAULT 0,
93 g_3_ameri_male NUMBER(6) DEFAULT 0,
94 h_3_tmraces_male NUMBER(6) DEFAULT 0,
95 i_3_white_fem NUMBER(6) DEFAULT 0,
96 j_3_black_fem NUMBER(6) DEFAULT 0,
97 k_3_latin_fem NUMBER(6) DEFAULT 0,
98 l_3_aspac_fem NUMBER(6) DEFAULT 0,
99 m_3_ameri_fem NUMBER(6) DEFAULT 0,
100 n_3_tmraces_female NUMBER(6) DEFAULT 0,
101 o_3_total_cat NUMBER(7) DEFAULT 0,
102 --
103 a_4_hl_male NUMBER(6) DEFAULT 0,
104 b_4_hl_female NUMBER(6) DEFAULT 0,
105 c_4_white_male NUMBER(6) DEFAULT 0,
106 d_4_black_male NUMBER(6) DEFAULT 0,
107 e_4_latin_male NUMBER(6) DEFAULT 0,
108 f_4_aspac_male NUMBER(6) DEFAULT 0,
109 g_4_ameri_male NUMBER(6) DEFAULT 0,
110 h_4_tmraces_male NUMBER(6) DEFAULT 0,
111 i_4_white_fem NUMBER(6) DEFAULT 0,
112 j_4_black_fem NUMBER(6) DEFAULT 0,
113 k_4_latin_fem NUMBER(6) DEFAULT 0,
114 l_4_aspac_fem NUMBER(6) DEFAULT 0,
115 m_4_ameri_fem NUMBER(6) DEFAULT 0,
116 n_4_tmraces_female NUMBER(6) DEFAULT 0,
117 o_4_total_cat NUMBER(7) DEFAULT 0,
118 --
119 a_5_hl_male NUMBER(6) DEFAULT 0,
120 b_5_hl_female NUMBER(6) DEFAULT 0,
121 c_5_white_male NUMBER(6) DEFAULT 0,
122 d_5_black_male NUMBER(6) DEFAULT 0,
123 e_5_latin_male NUMBER(6) DEFAULT 0,
124 f_5_aspac_male NUMBER(6) DEFAULT 0,
125 g_5_ameri_male NUMBER(6) DEFAULT 0,
126 h_5_tmraces_male NUMBER(6) DEFAULT 0,
127 i_5_white_fem NUMBER(6) DEFAULT 0,
128 j_5_black_fem NUMBER(6) DEFAULT 0,
129 k_5_latin_fem NUMBER(6) DEFAULT 0,
130 l_5_aspac_fem NUMBER(6) DEFAULT 0,
131 m_5_ameri_fem NUMBER(6) DEFAULT 0,
132 n_5_tmraces_female NUMBER(6) DEFAULT 0,
133 o_5_total_cat NUMBER(7) DEFAULT 0,
134 --
135 a_6_hl_male NUMBER(6) DEFAULT 0,
136 b_6_hl_female NUMBER(6) DEFAULT 0,
137 c_6_white_male NUMBER(6) DEFAULT 0,
138 d_6_black_male NUMBER(6) DEFAULT 0,
139 e_6_latin_male NUMBER(6) DEFAULT 0,
140 f_6_aspac_male NUMBER(6) DEFAULT 0,
141 g_6_ameri_male NUMBER(6) DEFAULT 0,
142 h_6_tmraces_male NUMBER(6) DEFAULT 0,
143 i_6_white_fem NUMBER(6) DEFAULT 0,
144 j_6_black_fem NUMBER(6) DEFAULT 0,
145 k_6_latin_fem NUMBER(6) DEFAULT 0,
146 l_6_aspac_fem NUMBER(6) DEFAULT 0,
147 m_6_ameri_fem NUMBER(6) DEFAULT 0,
148 n_6_tmraces_female NUMBER(6) DEFAULT 0,
149 o_6_total_cat NUMBER(7) DEFAULT 0,
150 --
151 a_7_hl_male NUMBER(6) DEFAULT 0,
152 b_7_hl_female NUMBER(6) DEFAULT 0,
153 c_7_white_male NUMBER(6) DEFAULT 0,
154 d_7_black_male NUMBER(6) DEFAULT 0,
155 e_7_latin_male NUMBER(6) DEFAULT 0,
156 f_7_aspac_male NUMBER(6) DEFAULT 0,
160 j_7_black_fem NUMBER(6) DEFAULT 0,
157 g_7_ameri_male NUMBER(6) DEFAULT 0,
158 h_7_tmraces_male NUMBER(6) DEFAULT 0,
159 i_7_white_fem NUMBER(6) DEFAULT 0,
161 k_7_latin_fem NUMBER(6) DEFAULT 0,
162 l_7_aspac_fem NUMBER(6) DEFAULT 0,
163 m_7_ameri_fem NUMBER(6) DEFAULT 0,
164 n_7_tmraces_female NUMBER(6) DEFAULT 0,
165 o_7_total_cat NUMBER(7) DEFAULT 0,
166 --
167 a_8_hl_male NUMBER(6) DEFAULT 0,
168 b_8_hl_female NUMBER(6) DEFAULT 0,
169 c_8_white_male NUMBER(6) DEFAULT 0,
170 d_8_black_male NUMBER(6) DEFAULT 0,
171 e_8_latin_male NUMBER(6) DEFAULT 0,
172 f_8_aspac_male NUMBER(6) DEFAULT 0,
173 g_8_ameri_male NUMBER(6) DEFAULT 0,
174 h_8_tmraces_male NUMBER(6) DEFAULT 0,
175 i_8_white_fem NUMBER(6) DEFAULT 0,
176 j_8_black_fem NUMBER(6) DEFAULT 0,
177 k_8_latin_fem NUMBER(6) DEFAULT 0,
178 l_8_aspac_fem NUMBER(6) DEFAULT 0,
179 m_8_ameri_fem NUMBER(6) DEFAULT 0,
180 n_8_tmraces_female NUMBER(6) DEFAULT 0,
181 o_8_total_cat NUMBER(7) DEFAULT 0,
182 --
183 a_9_hl_male NUMBER(6) DEFAULT 0,
184 b_9_hl_female NUMBER(6) DEFAULT 0,
185 c_9_white_male NUMBER(6) DEFAULT 0,
186 d_9_black_male NUMBER(6) DEFAULT 0,
187 e_9_latin_male NUMBER(6) DEFAULT 0,
188 f_9_aspac_male NUMBER(6) DEFAULT 0,
189 g_9_ameri_male NUMBER(6) DEFAULT 0,
190 h_9_tmraces_male NUMBER(6) DEFAULT 0,
191 i_9_white_fem NUMBER(6) DEFAULT 0,
192 j_9_black_fem NUMBER(6) DEFAULT 0,
193 k_9_latin_fem NUMBER(6) DEFAULT 0,
194 l_9_aspac_fem NUMBER(6) DEFAULT 0,
195 m_9_ameri_fem NUMBER(6) DEFAULT 0,
196 n_9_tmraces_female NUMBER(6) DEFAULT 0,
197 o_9_total_cat NUMBER(7) DEFAULT 0,
198 --
199 a_10_hl_male NUMBER(6) DEFAULT 0,
200 b_10_hl_female NUMBER(6) DEFAULT 0,
201 c_10_white_male NUMBER(6) DEFAULT 0,
202 d_10_black_male NUMBER(6) DEFAULT 0,
203 e_10_latin_male NUMBER(6) DEFAULT 0,
204 f_10_aspac_male NUMBER(6) DEFAULT 0,
205 g_10_ameri_male NUMBER(6) DEFAULT 0,
206 h_10_tmraces_male NUMBER(6) DEFAULT 0,
207 i_10_white_fem NUMBER(6) DEFAULT 0,
208 j_10_black_fem NUMBER(6) DEFAULT 0,
209 k_10_latin_fem NUMBER(6) DEFAULT 0,
210 l_10_aspac_fem NUMBER(6) DEFAULT 0,
211 m_10_ameri_fem NUMBER(6) DEFAULT 0,
212 n_10_tmraces_female NUMBER(6) DEFAULT 0,
213 o_10_total_cat NUMBER(7) DEFAULT 0,
214 --
215 a_10_grand_total NUMBER(6) DEFAULT 0,
216 b_10_grand_total NUMBER(6) DEFAULT 0,
217 c_10_grand_total NUMBER(6) DEFAULT 0,
218 d_10_grand_total NUMBER(6) DEFAULT 0,
219 e_10_grand_total NUMBER(6) DEFAULT 0,
220 f_10_grand_total NUMBER(6) DEFAULT 0,
221 g_10_grand_total NUMBER(6) DEFAULT 0,
222 h_10_grand_total NUMBER(6) DEFAULT 0,
223 i_10_grand_total NUMBER(6) DEFAULT 0,
224 j_10_grand_total NUMBER(6) DEFAULT 0,
225 k_10_grand_total NUMBER(6) DEFAULT 0,
226 l_10_grand_total NUMBER(6) DEFAULT 0,
227 m_10_grand_total NUMBER(6) DEFAULT 0,
228 n_10_grand_total NUMBER(6) DEFAULT 0,
229 o_10_grand_total NUMBER(7) DEFAULT 0);
230 --
231 l_estab_rec estab_rec;
232 l_consol_rec estab_rec;
233 l_holder_rec estab_rec;
234 l_estab_rec_blank estab_rec;
235
236
237 l_hierarchy_name NUMBER;
238 l_hierarchy_version_num NUMBER;
239 l_parent_org_id NUMBER;
240 l_parent_node_id NUMBER;
241 g_message_text VARCHAR2(240);
242 l_report_year VARCHAR2(4);
243 l_prev_year_filed VARCHAR2(4);
244 l_total NUMBER := 0;
245
246
247 PROCEDURE set_org_details(p_hierarchy_version_id IN NUMBER,
248 p_business_group_id IN NUMBER,
249 p_start_date IN DATE,
250 p_end_date IN DATE) IS
251
252
253 CURSOR c_org_details IS
254 SELECT SUBSTR(hou.name,1,20) org_name,
255 SUBSTR(hoi1.org_information2,1,27) company_number_1,
256 decode(hoi1.org_information3,'Y',1,2) c2_affiliated_14,
257 decode(hoi3.org_information5,'Y',1,2) gov_contract_15,
258 SUBSTR(hoi3.org_information4,1,20) duns_16,
259 decode(hoi3.org_information6,'Y',1,2) apprentices_emp_19,
260 SUBSTR(hoi3.org_information1,1,14) sic_20,
261 SUBSTR(hoi3.org_information2,1,16) naics_21,
262 SUBSTR(hoi2.org_information2,1,50) title_cert_off_22,
263 SUBSTR(hoi2.org_information1,1,50) name_cert_off_23,
264 SUBSTR(hoi2.org_information10,1,20) tel_num_24,
265 SUBSTR(hoi2.org_information14,1,10) fax_num_25,
266 SUBSTR(hoi2.org_information15,1,40) email_26,
267 hoi1.organization_id par_ent_org_id
268 FROM per_gen_hierarchy_nodes pgn,
269 hr_all_organization_units hou,
270 hr_organization_information hoi1,
271 hr_organization_information hoi2,
272 hr_organization_information hoi3
273 WHERE pgn.hierarchy_version_id = p_hierarchy_version_id
274 AND pgn.node_type = 'PAR'
275 AND hou.organization_id = p_business_group_id
276 AND pgn.business_group_id = p_business_group_id
277 AND hou.organization_id = pgn.business_group_id --BUG3646445
278 AND hoi3.org_information_context = 'VETS_EEO_Dup'
279 AND hoi3.organization_id = pgn.entity_id
280 AND hoi2.org_information_context = 'EEO_REPORT'
281 AND hoi2.organization_id = hou.organization_id
282 AND hoi1.org_information_context = 'EEO_Spec'
283 AND hoi1.organization_id = pgn.entity_id;
284
285 -- find out if over 100 people IN company for 13
286 CURSOR c_max IS
287 SELECT count('num_emps')
288 FROM per_all_assignments_f paf
289 WHERE paf.business_group_id = p_business_group_id
293 AND p_end_date < paf.effective_end_date
290 AND paf.primary_flag = 'Y'
291 AND paf.assignment_type = 'E'
292 AND p_start_date > paf.effective_start_date
294 AND paf.location_id IN
295 (SELECT entity_id
296 FROM per_gen_hierarchy_nodes
297 WHERE hierarchy_version_id = p_hierarchy_version_id
298 );
299
300 l_max NUMBER;
301
302 BEGIN --set_org_details
303
304 OPEN c_org_details;
305 FETCH c_org_details INTO l_org_rec.org_name,
306 l_org_rec.company_number_1,
307 l_org_rec.c2_affiliated_14,
308 l_org_rec.gov_contract_15,
309 l_org_rec.duns_16,
310 l_org_rec.apprentices_emp_19,
311 l_org_rec.sic_20,
312 l_org_rec.naics_21,
313 l_org_rec.title_cert_off_22,
317 l_org_rec.email_26,
314 l_org_rec.name_cert_off_23,
315 l_org_rec.tel_num_24,
316 l_org_rec.fax_num_25,
318 l_org_rec.par_ent_org_id;
319
320 CLOSE c_org_details;
321
322 OPEN c_max;
323 FETCH c_max INTO l_max;
324 CLOSE c_max;
325
326 IF l_max >= 100 THEN
327 l_org_rec.c1_over_100_13 := '1';
328 ELSE
329 l_org_rec.c1_over_100_13 := '2';
330 END IF;
331
332 /* fnd_file.put_line
333 (which => fnd_file.log,
334 buff => 'p_start_date '||p_start_date);
335 --
336 fnd_file.put_line
337 (which => fnd_file.log,
338 buff => 'p_end_date '||p_end_date);
339 --
340 fnd_file.put_line
341 (which => fnd_file.log,
342 buff => 'l_org_rec.l_d1_payroll_period_18 before '
343 ||l_org_rec.l_d1_payroll_period_18); */
344
345 l_org_rec.l_d1_payroll_period_18 :=
346 (TO_CHAR(p_start_date,'MMDDYYYY')
347 ||
348 TO_CHAR(p_end_date,'MMDDYYYY'));
349 --
350 -- for selection FROM location eit
351 --
352 -- Bug 7447266
353 -- l_report_year := SUBSTR(l_org_rec.l_d1_payroll_period_18,1,4);
354 l_report_year := SUBSTR(l_org_rec.l_d1_payroll_period_18,5,4);
355
356 l_prev_year_filed := TO_CHAR(TO_NUMBER(l_report_year - 1));
357 /*fnd_file.put_line
358 (which => fnd_file.log,
359 buff => 'l_org_rec.name_cert_off_23 '
360 ||l_org_rec.name_cert_off_23);
361 fnd_file.put_line
362 (which => fnd_file.log,
363 buff => 'sustr of l_org_rec.name_cert_off_23 '
364 ||substr(l_org_rec.name_cert_off_23,1,3)); */
365
366 BEGIN --Local1
367 -- error IF required fields not present.
368 IF l_org_rec.company_number_1 IS NULL THEN
369 fnd_file.put_line
370 (which => fnd_file.log,
371 buff => '==================================');
372 fnd_file.put_line
373 (which => fnd_file.log,
374 buff => '*** Field 1 - company NUMBER is blank, '
375 ||'but this is a required field - Please enter. '
376 ||'This is entered IN the GRE parent Entity Data '
377 ||'nav=Organization/Description/Parent Entity/Others ***');
378 fnd_file.put_line
379 (which => fnd_file.log,
380 buff => '==================================');
381 fnd_file.put_line
382 (which => fnd_file.log,
383 buff => 'Field 14 - Question C2 may also be blank '
384 ||'(is company affiliated with Companies of at least 100 emps?) '
385 ||'This is also a required field - which the app will force you to '
386 ||'enter at organization level for the GRE parent Entity Data. '
387 ||'nav=Organization/Description/Parent Entity/Others ');
388 fnd_file.put_line
389 (which => fnd_file.log,
390 buff => '==================================');
391 RAISE hr_utility.hr_error;
392 END IF;
393 END; --Local1
394
395 IF UPPER(SUBSTR(l_org_rec.title_cert_off_22,1,3)) = UPPER('THE') THEN
396 l_org_rec.title_cert_off_22 :=
397 ltrim(l_org_rec.title_cert_off_22,'THEthe');
398 l_org_rec.title_cert_off_22 :=
399 l_org_rec.title_cert_off_22||' The';
400 END IF;
401
405 -- fnd_file.put_line
402 IF UPPER(SUBSTR(l_org_rec.name_cert_off_23,1,3)) = UPPER('THE') THEN
403 l_org_rec.name_cert_off_23 :=
404 ltrim(l_org_rec.name_cert_off_23,'THEthe');
406 --(which => fnd_file.log,
407 -- buff => 'l_org_rec.name_cert_off_23 '
408 -- ||l_org_rec.name_cert_off_23);
409 l_org_rec.name_cert_off_23 :=
410 l_org_rec.name_cert_off_23||' The';
411 -- fnd_file.put_line
412 --(which => fnd_file.log,
413 -- buff => 'l_org_rec.name_cert_off_23 '
414 -- ||l_org_rec.name_cert_off_23);
415 END IF;
416
417 --
418 /* fnd_file.put_line
419 (which => fnd_file.log,
420 buff => 'l_org_rec.l_d1_payroll_period_18 after '
421 ||l_org_rec.l_d1_payroll_period_18);
422 --
423 fnd_file.put_line
424 (which => fnd_file.log,
425 buff => 'l_report_year '||l_report_year);
426 --
427 fnd_file.put_line
428 (which => fnd_file.log,
429 buff => 'l_prev_year_filed '||l_prev_year_filed); */
430
431 END set_org_details;
432
433
434 PROCEDURE write_consolidated_record is
435
436 l_string VARCHAR2(3000);
437 l_proc VARCHAR2(60) := g_package || 'write_consolidated_record';
438 l_consol_unit_name_4 VARCHAR2 (3000) ;
439 l_consol_unit_address_5 VARCHAR2(3000);
440 l_consol_unit_address_6 VARCHAR2(3000);
441
442 BEGIN
443
444 hr_utility.set_location('Entering..' || l_proc,10);
445 hr_utility.trace('l_consol_rec.unit_number_3 : ' || l_consol_rec.unit_number_3);
446 hr_utility.trace('l_consol_rec.unit_name_4 : ' || l_consol_rec.unit_name_4);
447 hr_utility.trace('l_consol_rec.unit_address_5: ' || l_consol_rec.unit_address_req_5);
448
449 IF l_org_rec.form_type = 'M' THEN
450
451 IF l_consol_rec.unit_name_4 IS NULL THEN
452
453 fnd_file.put_line
454 (which => fnd_file.log,
455 buff => ' ');
456
457 fnd_file.put_line
458 (which => fnd_file.log,
459 buff => ' UNIT NAME OR NUMBER SHOULD NOT BE NULL FOR CONSOLIDATED REPORT.'
460 ||'THIS INFORMATION COMES FROM HEADQUARTERS ESTABLISHMENT.'
461 ||'The unit NUMBER IS ' || l_consol_rec.unit_number_3
462 ||'The unit name IS ' || l_consol_rec.unit_name_4
463 ||'The unit address IS ' || l_consol_rec.unit_address_req_5);
464
465 fnd_file.put_line
466 (which => fnd_file.log,
467 buff => 'Reported field IN the EEO1 Specific Data Extra '
468 ||'Information Type ');
469
470 fnd_file.put_line
471 (which => fnd_file.log,
472 buff => ' ');
473
474 END IF; --l_consol_rec.unit_name_4 IS NULL
475
476 l_org_rec.l_status_code_2 := '2';
477
478 l_consol_rec.o_1_total_cat := l_consol_rec.a_1_hl_male +
479 l_consol_rec.b_1_hl_female +
480 l_consol_rec.c_1_white_male +
481 l_consol_rec.d_1_black_male +
482 l_consol_rec.e_1_latin_male +
483 l_consol_rec.f_1_aspac_male +
484 l_consol_rec.g_1_ameri_male +
485 l_consol_rec.h_1_tmraces_male +
486 l_consol_rec.i_1_white_fem +
487 l_consol_rec.j_1_black_fem +
488 l_consol_rec.k_1_latin_fem +
489 l_consol_rec.l_1_aspac_fem +
490 l_consol_rec.m_1_ameri_fem +
491 l_consol_rec.n_1_tmraces_female;
492
493 l_consol_rec.o_2_total_cat := l_consol_rec.a_2_hl_male +
494 l_consol_rec.b_2_hl_female +
495 l_consol_rec.c_2_white_male +
496 l_consol_rec.d_2_black_male +
497 l_consol_rec.e_2_latin_male +
498 l_consol_rec.f_2_aspac_male +
499 l_consol_rec.g_2_ameri_male +
500 l_consol_rec.h_2_tmraces_male +
501 l_consol_rec.i_2_white_fem +
502 l_consol_rec.j_2_black_fem +
503 l_consol_rec.k_2_latin_fem +
504 l_consol_rec.l_2_aspac_fem +
505 l_consol_rec.m_2_ameri_fem +
506 l_consol_rec.n_2_tmraces_female;
507
508 l_consol_rec.o_3_total_cat := l_consol_rec.a_3_hl_male +
509 l_consol_rec.b_3_hl_female +
510 l_consol_rec.c_3_white_male +
511 l_consol_rec.d_3_black_male +
512 l_consol_rec.e_3_latin_male +
513 l_consol_rec.f_3_aspac_male +
514 l_consol_rec.g_3_ameri_male +
515 l_consol_rec.h_3_tmraces_male +
516 l_consol_rec.i_3_white_fem +
517 l_consol_rec.j_3_black_fem +
518 l_consol_rec.k_3_latin_fem +
519 l_consol_rec.l_3_aspac_fem +
520 l_consol_rec.m_3_ameri_fem +
521 l_consol_rec.n_3_tmraces_female;
522
523 l_consol_rec.o_4_total_cat := l_consol_rec.a_4_hl_male +
524 l_consol_rec.b_4_hl_female +
525 l_consol_rec.c_4_white_male +
526 l_consol_rec.d_4_black_male +
527 l_consol_rec.e_4_latin_male +
528 l_consol_rec.f_4_aspac_male +
529 l_consol_rec.g_4_ameri_male +
530 l_consol_rec.h_4_tmraces_male +
531 l_consol_rec.i_4_white_fem +
532 l_consol_rec.j_4_black_fem +
533 l_consol_rec.k_4_latin_fem +
537
534 l_consol_rec.l_4_aspac_fem +
535 l_consol_rec.m_4_ameri_fem +
536 l_consol_rec.n_4_tmraces_female;
538 l_consol_rec.o_5_total_cat := l_consol_rec.a_5_hl_male +
539 l_consol_rec.b_5_hl_female +
540 l_consol_rec.c_5_white_male +
541 l_consol_rec.d_5_black_male +
542 l_consol_rec.e_5_latin_male +
543 l_consol_rec.f_5_aspac_male +
544 l_consol_rec.g_5_ameri_male +
545 l_consol_rec.h_5_tmraces_male +
546 l_consol_rec.i_5_white_fem +
547 l_consol_rec.j_5_black_fem +
548 l_consol_rec.k_5_latin_fem +
549 l_consol_rec.l_5_aspac_fem +
550 l_consol_rec.m_5_ameri_fem +
551 l_consol_rec.n_5_tmraces_female;
552
553 l_consol_rec.o_6_total_cat := l_consol_rec.a_6_hl_male +
554 l_consol_rec.b_6_hl_female +
555 l_consol_rec.c_6_white_male +
556 l_consol_rec.d_6_black_male +
557 l_consol_rec.e_6_latin_male +
558 l_consol_rec.f_6_aspac_male +
559 l_consol_rec.g_6_ameri_male +
560 l_consol_rec.h_6_tmraces_male +
561 l_consol_rec.i_6_white_fem +
562 l_consol_rec.j_6_black_fem +
563 l_consol_rec.k_6_latin_fem +
564 l_consol_rec.l_6_aspac_fem +
565 l_consol_rec.m_6_ameri_fem +
566 l_consol_rec.n_6_tmraces_female;
567
568 l_consol_rec.o_7_total_cat := l_consol_rec.a_7_hl_male +
569 l_consol_rec.b_7_hl_female +
570 l_consol_rec.c_7_white_male +
571 l_consol_rec.d_7_black_male +
572 l_consol_rec.e_7_latin_male +
573 l_consol_rec.f_7_aspac_male +
574 l_consol_rec.g_7_ameri_male +
575 l_consol_rec.h_7_tmraces_male +
576 l_consol_rec.i_7_white_fem +
577 l_consol_rec.j_7_black_fem +
578 l_consol_rec.k_7_latin_fem +
579 l_consol_rec.l_7_aspac_fem +
580 l_consol_rec.m_7_ameri_fem +
581 l_consol_rec.n_7_tmraces_female;
582
583 l_consol_rec.o_8_total_cat := l_consol_rec.a_8_hl_male +
584 l_consol_rec.b_8_hl_female +
585 l_consol_rec.c_8_white_male +
586 l_consol_rec.d_8_black_male +
587 l_consol_rec.e_8_latin_male +
588 l_consol_rec.f_8_aspac_male +
589 l_consol_rec.g_8_ameri_male +
590 l_consol_rec.h_8_tmraces_male +
591 l_consol_rec.i_8_white_fem +
592 l_consol_rec.j_8_black_fem +
593 l_consol_rec.k_8_latin_fem +
594 l_consol_rec.l_8_aspac_fem +
595 l_consol_rec.m_8_ameri_fem +
596 l_consol_rec.n_8_tmraces_female;
597
598 l_consol_rec.o_9_total_cat := l_consol_rec.a_9_hl_male +
599 l_consol_rec.b_9_hl_female +
600 l_consol_rec.c_9_white_male +
601 l_consol_rec.d_9_black_male +
602 l_consol_rec.e_9_latin_male +
603 l_consol_rec.f_9_aspac_male +
604 l_consol_rec.g_9_ameri_male +
605 l_consol_rec.h_9_tmraces_male +
606 l_consol_rec.i_9_white_fem +
607 l_consol_rec.j_9_black_fem +
608 l_consol_rec.k_9_latin_fem +
609 l_consol_rec.l_9_aspac_fem +
610 l_consol_rec.m_9_ameri_fem +
611 l_consol_rec.n_9_tmraces_female;
612
613 l_consol_rec.o_10_total_cat := l_consol_rec.a_10_hl_male +
614 l_consol_rec.b_10_hl_female +
615 l_consol_rec.c_10_white_male +
616 l_consol_rec.d_10_black_male +
617 l_consol_rec.e_10_latin_male +
618 l_consol_rec.f_10_aspac_male +
619 l_consol_rec.g_10_ameri_male +
620 l_consol_rec.h_10_tmraces_male +
621 l_consol_rec.i_10_white_fem +
622 l_consol_rec.j_10_black_fem +
623 l_consol_rec.k_10_latin_fem +
624 l_consol_rec.l_10_aspac_fem +
625 l_consol_rec.m_10_ameri_fem +
626 l_consol_rec.n_10_tmraces_female;
627
628 l_consol_rec.o_10_grand_total := l_consol_rec.a_10_grand_total +
629 l_consol_rec.b_10_grand_total +
630 l_consol_rec.c_10_grand_total +
631 l_consol_rec.d_10_grand_total +
632 l_consol_rec.e_10_grand_total +
633 l_consol_rec.f_10_grand_total +
634 l_consol_rec.g_10_grand_total +
635 l_consol_rec.h_10_grand_total +
636 l_consol_rec.i_10_grand_total +
637 l_consol_rec.j_10_grand_total +
638 l_consol_rec.k_10_grand_total +
639 l_consol_rec.l_10_grand_total +
640 l_consol_rec.m_10_grand_total +
641 l_consol_rec.n_10_grand_total;
642
643
644 l_consol_unit_name_4 := ltrim(substr(
645 translate(l_consol_rec.unit_name_4,'`;"|\-+=_#$%^&*@!~:<>?/()[]{},',' '),1,35),'1234567890');
646
647 l_consol_unit_address_5 := substr(
648 translate(l_consol_rec.unit_address_req_5,'`;"|\-+=_#$%^&*@!~:<>?/()[]{},',' '),1,34);
649 l_consol_unit_address_6 := substr(
650 translate(l_consol_rec.unit_address_6,'`;"|\-+=_#$%^&*@!~:<>?/()[]{},',' '),1,25);
651
652
653 hr_utility.trace('l_consol_unit_name_4 :'||l_consol_unit_name_4);
654 hr_utility.trace('l_consol_unit_address_5 : '||l_consol_unit_address_5);
658 -- 1
655 hr_utility.trace('l_consol_unit_address_6 : '||l_consol_unit_address_6);
656
657 l_string :=
659 nvl(lpad(l_org_rec.company_number_1,7,0),(lpad(' ',7,' ')))
660 -- 2
661 -- status code always 2 for consol rpt
662 ||'2'
663 -- 3
664 ||nvl(lpad(SUBSTR(l_consol_rec.unit_number_3,1,7),7,0),
665 ('0000000'))
666 -- 4
667 ||nvl(rpad(l_consol_unit_name_4,35,' '),(lpad(' ',35,' ')))
668
669
670 -- 5
671 ||nvl(rpad(l_consol_unit_address_5,34,' '),(lpad(' ',34,' ')))
672
673 -- 6
674 ||nvl(rpad(l_consol_unit_address_6,25,' '),(lpad(' ',25,' ')))
675 -- 7
676 ||nvl(rpad(replace(replace(SUBSTR(l_consol_rec.city_7,1,20),',','')
677 ,'.',''),20,' '),(lpad(' ',20,' ')))
678 -- 8
679 ||nvl(rpad(l_consol_rec.state_8,2),(lpad(' ',8,' ')))
680 -- 9
681 ||nvl(rpad(l_consol_rec.zip_code_9,5),(lpad(' ',5,' ')))
682 -- 10
683 ||rpad(l_consol_rec.reported_last_year_11,1)
684 -- 11
685 ||rpad(l_org_rec.c1_over_100_13,1)
686 -- 12
687 ||rpad(l_org_rec.c2_affiliated_14,1)
688 -- 13
689 ||rpad(nvl(l_consol_rec.gov_contract_15,l_org_rec.gov_contract_15)
690 ,1)
691 -- 14
692 ||nvl(lpad(nvl(l_consol_rec.duns_16,l_org_rec.duns_16),9,0)
693 ,(lpad(' ',9,' ')))
694 -- 15
695 ||nvl(rpad(replace(replace(l_consol_rec.county_17,',',''),'.','')
696 ,18),(lpad(' ',18,' ')))
697 -- 16
698 ||rpad(l_org_rec.l_d1_payroll_period_18,16)
699 -- 17
700 ||nvl(lpad(nvl(l_consol_rec.naics_21,l_org_rec.naics_21),6,0), --BUG4494412
701 (lpad(' ',6,' ')))
702 -- 18
703 ||nvl(rpad(ltrim(replace(replace(l_org_rec.title_cert_off_22,',','')
704 ,'.',''),'1234567890'),35),(lpad(' ',35,' ')))
705 -- 19
706 ||nvl(rpad(ltrim(replace(replace(l_org_rec.name_cert_off_23,',','')
707 ,'.',''),'1234567890'),35),(lpad(' ',35,' ')))
708 -- 20
709 ||nvl(rpad(replace(replace(l_org_rec.tel_num_24,',',''),'.','')
710 ,10),(lpad(' ',10,' ')))
711 -- 21
712 ||nvl(rpad(replace(l_org_rec.email_26,',',''),40),
713 (lpad(' ',40,' ')))||
714 --
715 lpad(l_consol_rec.a_1_hl_male,6,0)||
716 lpad(l_consol_rec.b_1_hl_female,6,0)||
717 lpad(l_consol_rec.c_1_white_male,6,0)||
718 lpad(l_consol_rec.d_1_black_male,6,0)||
719 lpad(l_consol_rec.e_1_latin_male,6,0)||
720 lpad(l_consol_rec.f_1_aspac_male,6,0)||
721 lpad(l_consol_rec.g_1_ameri_male,6,0)||
722 lpad(l_consol_rec.h_1_tmraces_male,6,0)||
723 lpad(l_consol_rec.i_1_white_fem,6,0)||
724 lpad(l_consol_rec.j_1_black_fem,6,0)||
725 lpad(l_consol_rec.k_1_latin_fem,6,0)||
726 lpad(l_consol_rec.l_1_aspac_fem,6,0)||
727 lpad(l_consol_rec.m_1_ameri_fem,6,0)||
728 lpad(l_consol_rec.n_1_tmraces_female,6,0)||
729 lpad(l_consol_rec.o_1_total_cat,7,0)||
730 --
731 lpad(l_consol_rec.a_2_hl_male,6,0)||
732 lpad(l_consol_rec.b_2_hl_female,6,0)||
733 lpad(l_consol_rec.c_2_white_male,6,0)||
734 lpad(l_consol_rec.d_2_black_male,6,0)||
735 lpad(l_consol_rec.e_2_latin_male,6,0)||
736 lpad(l_consol_rec.f_2_aspac_male,6,0)||
737 lpad(l_consol_rec.g_2_ameri_male,6,0)||
738 lpad(l_consol_rec.h_2_tmraces_male,6,0)||
739 lpad(l_consol_rec.i_2_white_fem,6,0)||
740 lpad(l_consol_rec.j_2_black_fem,6,0)||
741 lpad(l_consol_rec.k_2_latin_fem,6,0)||
742 lpad(l_consol_rec.l_2_aspac_fem,6,0)||
743 lpad(l_consol_rec.m_2_ameri_fem,6,0)||
744 lpad(l_consol_rec.n_2_tmraces_female,6,0)||
745 lpad(l_consol_rec.o_2_total_cat,7,0)||
746 --
747 lpad(l_consol_rec.a_3_hl_male,6,0)||
748 lpad(l_consol_rec.b_3_hl_female,6,0)||
749 lpad(l_consol_rec.c_3_white_male,6,0)||
750 lpad(l_consol_rec.d_3_black_male,6,0)||
751 lpad(l_consol_rec.e_3_latin_male,6,0)||
752 lpad(l_consol_rec.f_3_aspac_male,6,0)||
753 lpad(l_consol_rec.g_3_ameri_male,6,0)||
754 lpad(l_consol_rec.h_3_tmraces_male,6,0)||
755 lpad(l_consol_rec.i_3_white_fem,6,0)||
756 lpad(l_consol_rec.j_3_black_fem,6,0)||
757 lpad(l_consol_rec.k_3_latin_fem,6,0)||
758 lpad(l_consol_rec.l_3_aspac_fem,6,0)||
759 lpad(l_consol_rec.m_3_ameri_fem,6,0)||
760 lpad(l_consol_rec.n_3_tmraces_female,6,0)||
761 lpad(l_consol_rec.o_3_total_cat,7,0)||
762 --
763 lpad(l_consol_rec.a_4_hl_male,6,0)||
764 lpad(l_consol_rec.b_4_hl_female,6,0)||
765 lpad(l_consol_rec.c_4_white_male,6,0)||
766 lpad(l_consol_rec.d_4_black_male,6,0)||
767 lpad(l_consol_rec.e_4_latin_male,6,0)||
768 lpad(l_consol_rec.f_4_aspac_male,6,0)||
769 lpad(l_consol_rec.g_4_ameri_male,6,0)||
770 lpad(l_consol_rec.h_4_tmraces_male,6,0)||
774 lpad(l_consol_rec.l_4_aspac_fem,6,0)||
771 lpad(l_consol_rec.i_4_white_fem,6,0)||
772 lpad(l_consol_rec.j_4_black_fem,6,0)||
773 lpad(l_consol_rec.k_4_latin_fem,6,0)||
775 lpad(l_consol_rec.m_4_ameri_fem,6,0)||
776 lpad(l_consol_rec.n_4_tmraces_female,6,0)||
777 lpad(l_consol_rec.o_4_total_cat,7,0)||
778 --
779 lpad(l_consol_rec.a_5_hl_male,6,0)||
780 lpad(l_consol_rec.b_5_hl_female,6,0)||
781 lpad(l_consol_rec.c_5_white_male,6,0)||
782 lpad(l_consol_rec.d_5_black_male,6,0)||
783 lpad(l_consol_rec.e_5_latin_male,6,0)||
784 lpad(l_consol_rec.f_5_aspac_male,6,0)||
785 lpad(l_consol_rec.g_5_ameri_male,6,0)||
786 lpad(l_consol_rec.h_5_tmraces_male,6,0)||
787 lpad(l_consol_rec.i_5_white_fem,6,0)||
788 lpad(l_consol_rec.j_5_black_fem,6,0)||
789 lpad(l_consol_rec.k_5_latin_fem,6,0)||
790 lpad(l_consol_rec.l_5_aspac_fem,6,0)||
791 lpad(l_consol_rec.m_5_ameri_fem,6,0)||
792 lpad(l_consol_rec.n_5_tmraces_female,6,0)||
793 lpad(l_consol_rec.o_5_total_cat,7,0)||
794 --
795 lpad(l_consol_rec.a_6_hl_male,6,0)||
796 lpad(l_consol_rec.b_6_hl_female,6,0)||
797 lpad(l_consol_rec.c_6_white_male,6,0)||
798 lpad(l_consol_rec.d_6_black_male,6,0)||
799 lpad(l_consol_rec.e_6_latin_male,6,0)||
800 lpad(l_consol_rec.f_6_aspac_male,6,0)||
801 lpad(l_consol_rec.g_6_ameri_male,6,0)||
802 lpad(l_consol_rec.h_6_tmraces_male,6,0)||
803 lpad(l_consol_rec.i_6_white_fem,6,0)||
804 lpad(l_consol_rec.j_6_black_fem,6,0)||
805 lpad(l_consol_rec.k_6_latin_fem,6,0)||
806 lpad(l_consol_rec.l_6_aspac_fem,6,0)||
807 lpad(l_consol_rec.m_6_ameri_fem,6,0)||
808 lpad(l_consol_rec.n_6_tmraces_female,6,0)||
809 lpad(l_consol_rec.o_6_total_cat,7,0)||
810 --
811 lpad(l_consol_rec.a_7_hl_male,6,0)||
812 lpad(l_consol_rec.b_7_hl_female,6,0)||
813 lpad(l_consol_rec.c_7_white_male,6,0)||
814 lpad(l_consol_rec.d_7_black_male,6,0)||
815 lpad(l_consol_rec.e_7_latin_male,6,0)||
816 lpad(l_consol_rec.f_7_aspac_male,6,0)||
817 lpad(l_consol_rec.g_7_ameri_male,6,0)||
818 lpad(l_consol_rec.h_7_tmraces_male,6,0)||
819 lpad(l_consol_rec.i_7_white_fem,6,0)||
820 lpad(l_consol_rec.j_7_black_fem,6,0)||
821 lpad(l_consol_rec.k_7_latin_fem,6,0)||
822 lpad(l_consol_rec.l_7_aspac_fem,6,0)||
823 lpad(l_consol_rec.m_7_ameri_fem,6,0)||
824 lpad(l_consol_rec.n_7_tmraces_female,6,0)||
825 lpad(l_consol_rec.o_7_total_cat,7,0)||
826 --
827 lpad(l_consol_rec.a_8_hl_male,6,0)||
828 lpad(l_consol_rec.b_8_hl_female,6,0)||
829 lpad(l_consol_rec.c_8_white_male,6,0)||
830 lpad(l_consol_rec.d_8_black_male,6,0)||
831 lpad(l_consol_rec.e_8_latin_male,6,0)||
832 lpad(l_consol_rec.f_8_aspac_male,6,0)||
833 lpad(l_consol_rec.g_8_ameri_male,6,0)||
834 lpad(l_consol_rec.h_8_tmraces_male,6,0)||
835 lpad(l_consol_rec.i_8_white_fem,6,0)||
836 lpad(l_consol_rec.j_8_black_fem,6,0)||
837 lpad(l_consol_rec.k_8_latin_fem,6,0)||
838 lpad(l_consol_rec.l_8_aspac_fem,6,0)||
839 lpad(l_consol_rec.m_8_ameri_fem,6,0)||
840 lpad(l_consol_rec.n_8_tmraces_female,6,0)||
841 lpad(l_consol_rec.o_8_total_cat,7,0)||
842 --
843 lpad(l_consol_rec.a_9_hl_male,6,0)||
844 lpad(l_consol_rec.b_9_hl_female,6,0)||
845 lpad(l_consol_rec.c_9_white_male,6,0)||
846 lpad(l_consol_rec.d_9_black_male,6,0)||
847 lpad(l_consol_rec.e_9_latin_male,6,0)||
848 lpad(l_consol_rec.f_9_aspac_male,6,0)||
849 lpad(l_consol_rec.g_9_ameri_male,6,0)||
850 lpad(l_consol_rec.h_9_tmraces_male,6,0)||
851 lpad(l_consol_rec.i_9_white_fem,6,0)||
852 lpad(l_consol_rec.j_9_black_fem,6,0)||
853 lpad(l_consol_rec.k_9_latin_fem,6,0)||
854 lpad(l_consol_rec.l_9_aspac_fem,6,0)||
855 lpad(l_consol_rec.m_9_ameri_fem,6,0)||
856 lpad(l_consol_rec.n_9_tmraces_female,6,0)||
857 lpad(l_consol_rec.o_9_total_cat,7,0)||
858 --
859 lpad(l_consol_rec.a_10_hl_male,6,0)||
860 lpad(l_consol_rec.b_10_hl_female,6,0)||
861 lpad(l_consol_rec.c_10_white_male,6,0)||
862 lpad(l_consol_rec.d_10_black_male,6,0)||
863 lpad(l_consol_rec.e_10_latin_male,6,0)||
864 lpad(l_consol_rec.f_10_aspac_male,6,0)||
865 lpad(l_consol_rec.g_10_ameri_male,6,0)||
866 lpad(l_consol_rec.h_10_tmraces_male,6,0)||
867 lpad(l_consol_rec.i_10_white_fem,6,0)||
868 lpad(l_consol_rec.j_10_black_fem,6,0)||
869 lpad(l_consol_rec.k_10_latin_fem,6,0)||
870 lpad(l_consol_rec.l_10_aspac_fem,6,0)||
871 lpad(l_consol_rec.m_10_ameri_fem,6,0)||
872 lpad(l_consol_rec.n_10_tmraces_female,6,0)||
873 lpad(l_consol_rec.o_10_total_cat,7,0)||
874 --
875 lpad(l_consol_rec.a_10_grand_total,6,0) ||
876 lpad(l_consol_rec.b_10_grand_total,6,0) ||
880 lpad(l_consol_rec.f_10_grand_total,6,0) ||
877 lpad(l_consol_rec.c_10_grand_total,6,0) ||
878 lpad(l_consol_rec.d_10_grand_total,6,0) ||
879 lpad(l_consol_rec.e_10_grand_total,6,0) ||
881 lpad(l_consol_rec.g_10_grand_total,6,0) ||
882 lpad(l_consol_rec.h_10_grand_total,6,0) ||
883 lpad(l_consol_rec.i_10_grand_total,6,0) ||
884 lpad(l_consol_rec.j_10_grand_total,6,0) ||
885 lpad(l_consol_rec.k_10_grand_total,6,0)||
886 lpad(l_consol_rec.l_10_grand_total,6,0)||
887 lpad(l_consol_rec.m_10_grand_total,6,0)||
888 lpad(l_consol_rec.n_10_grand_total,6,0)||
889 lpad(l_consol_rec.o_10_grand_total,7,0);
890 --
894 (which => fnd_file.log,
891 /* g_message_text := 'd1) l_consol_rec.a_1_total_mf IN string '
892 ||l_consol_rec.a_1_total_mf;
893 fnd_file.put_line
895 buff => g_message_text);
896 g_message_text := ' ';
897 fnd_file.put_line
898 (which => fnd_file.log,
899 buff => g_message_text);
900 g_message_text := 'e1) put line for l_string for consolidated RECORD ';
901 fnd_file.put_line
902 (which => fnd_file.log,
903 buff => g_message_text);
904 g_message_text := ' ';
905 fnd_file.put_line
906 (which => fnd_file.log,
907 buff => g_message_text); */
908 fnd_file.put_line
909 (which => fnd_file.output,
910 buff => l_string);
911
912 END IF; -- l_org_rec.form_type = 'M'
913 hr_utility.set_location('Leaving..' || l_proc,100);
914 END write_consolidated_record;
915
916
917
918 PROCEDURE write_establishment_record IS
919
920 l_string VARCHAR2 (3000);
921 l_proc VARCHAR2(60) := g_package || 'write_establishment_record';
922 l_estab_unit_name_4 VARCHAR2 (3000) ;
923 l_estab_unit_address_5 VARCHAR2(3000);
924 l_estab_unit_address_6 VARCHAR2(3000);
925
926 BEGIN
927
928 hr_utility.set_location('Entering..' || l_proc,10);
929 hr_utility.trace('l_estab_rec.unit_number_3 : '||l_estab_rec.unit_number_3);
930 hr_utility.trace('l_estab_rec.unit_name_4 : '||l_estab_rec.unit_name_4);
931 hr_utility.trace('l_estab_rec.unit_address_5: '||l_estab_rec.unit_address_req_5);
932
933 /* fnd_file.put_line
934 (which => fnd_file.log,
935 buff => ' ');
936 fnd_file.put_line
937 (which => fnd_file.log,
938 buff => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
939 fnd_file.put_line
940 (which => fnd_file.log,
941 buff => ' PROCEDURE write_establishment_record ');
942 fnd_file.put_line
943 (which => fnd_file.log,
944 buff => '-------------------------------------------------------------');
945 */
946 --
947 -- Set Status
948 --
949 hr_utility.trace('l_org_rec.form_type : ' || l_org_rec.form_type);
950 IF l_org_rec.form_type = 'S' THEN
951
952 IF l_estab_rec.reported_last_year_11 = '2' THEN
953 hr_utility.set_location(l_proc,15);
954 l_org_rec.l_status_code_2 := '9';
955 ELSE
956 hr_utility.set_location(l_proc,16);
957 l_org_rec.l_status_code_2 := '1';
958 END IF;
959
960 ELSE --l_org_rec.form_type = 'S'
961 -- IF under 50 employees THEN always status 8 .. unless status 3
962
963 IF l_estab_rec.reported_last_year_11 = '2' AND l_estab_rec.max_count = 'Y' THEN
964
965 IF l_estab_rec.hq = 'Y' THEN
966 l_org_rec.l_status_code_2 := '3';
967 hr_utility.set_location(l_proc,30);
968 -- BUG4494412
969 l_consol_rec.unit_number_3 := l_estab_rec.unit_number_3;
970 l_consol_rec.unit_name_4 := l_estab_rec.unit_name_4;
971 l_consol_rec.unit_address_req_5 := l_estab_rec.unit_address_req_5;
972 l_consol_rec.unit_address_6 := l_estab_rec.unit_address_6;
973 l_consol_rec.city_7 := l_estab_rec.city_7;
974 l_consol_rec.state_8 := l_estab_rec.state_8;
975 l_consol_rec.zip_code_9 := l_estab_rec.zip_code_9;
976 l_consol_rec.zip_code_last_4_10 := l_estab_rec.zip_code_last_4_10;
977 l_consol_rec.reported_last_year_11 := l_estab_rec.reported_last_year_11;
978 l_consol_rec.ein_12 := l_estab_rec.ein_12;
979 l_consol_rec.gov_contract_15 := l_estab_rec.gov_contract_15;
980 l_consol_rec.duns_16 := l_estab_rec.duns_16;
981 l_consol_rec.county_17 := l_estab_rec.county_17;
982 l_consol_rec.apprentices_emp_19 := l_estab_rec.apprentices_emp_19;
983 l_consol_rec.sic_20 := l_estab_rec.sic_20;
984 l_consol_rec.naics_21 := l_estab_rec.naics_21;
985 ELSE
986 hr_utility.set_location(l_proc,20);
987 l_org_rec.l_status_code_2 := '9';
988 END IF;
989
990 ELSIF l_estab_rec.hq = 'Y'
991 AND l_estab_rec.reported_last_year_11 = '1' THEN
992
993 hr_utility.set_location(l_proc,40);
994 l_org_rec.l_status_code_2 := '3';
995 -- BUG4494412
996 l_consol_rec.unit_number_3 := l_estab_rec.unit_number_3;
997 l_consol_rec.unit_name_4 := l_estab_rec.unit_name_4;
998 l_consol_rec.unit_address_req_5 := l_estab_rec.unit_address_req_5;
999 l_consol_rec.unit_address_6 := l_estab_rec.unit_address_6;
1000 l_consol_rec.city_7 := l_estab_rec.city_7;
1001 l_consol_rec.state_8 := l_estab_rec.state_8;
1002 l_consol_rec.zip_code_9 := l_estab_rec.zip_code_9;
1003 l_consol_rec.zip_code_last_4_10 := l_estab_rec.zip_code_last_4_10;
1004 l_consol_rec.reported_last_year_11 := l_estab_rec.reported_last_year_11;
1005 l_consol_rec.ein_12 := l_estab_rec.ein_12;
1006 l_consol_rec.gov_contract_15 := l_estab_rec.gov_contract_15;
1007 l_consol_rec.duns_16 := l_estab_rec.duns_16;
1008 l_consol_rec.county_17 := l_estab_rec.county_17;
1009 l_consol_rec.apprentices_emp_19 := l_estab_rec.apprentices_emp_19;
1010 l_consol_rec.sic_20 := l_estab_rec.sic_20;
1011 l_consol_rec.naics_21 := l_estab_rec.naics_21;
1012 --
1013 -- headquarters report.
1014 ELSIF l_estab_rec.hq = 'N'
1015 AND l_estab_rec.max_count = 'Y'
1016 AND l_estab_rec.reported_last_year_11 = '1'
1017 THEN
1018
1019 hr_utility.set_location(l_proc,50);
1020 l_org_rec.l_status_code_2 := '4';
1021 --
1022 -- not HQ AND less than 50 emps over at location
1023 ELSIF l_estab_rec.hq = 'N' AND l_estab_rec.max_count = 'N'
1024 THEN
1025
1026 hr_utility.set_location(l_proc,60);
1027 l_org_rec.l_status_code_2 := '8';
1028 --
1029 -- not HQ AND under 50 emps at location
1030 /*
1031 fnd_file.put_line
1032 (which => fnd_file.log,
1033 buff => 'l_estab_rec.unit_number_3: '||l_estab_rec.unit_number_3);
1034 */
1035 -- END IF;
1036
1037 END IF; --l_estab_rec.reported_last_year_11 = '2'
1038 hr_utility.trace('l_org_rec.l_status_code_2 : ' || l_org_rec.l_status_code_2);
1039 hr_utility.trace('l_estab_rec.unit_number_3 : ' || l_estab_rec.unit_number_3);
1040
1041 IF l_estab_rec.hq = 'Y' THEN
1042 l_org_rec.l_status_code_2 := '3';
1043 END IF;
1044
1045 IF l_estab_rec.unit_number_3 IS NOT NULL
1046 AND l_org_rec.l_status_code_2 IN ('8','9') THEN
1047
1048 fnd_file.put_line
1049 (which => fnd_file.log,
1050 buff => ' ');
1051 fnd_file.put_line
1052 (which => fnd_file.log,
1053 buff => ' UNIT NUMBER SHOULD BE NULL AS THIS LOCATION('
1054 || l_estab_rec.unit_name_4 || ') HAS EITHER '
1055 ||'NOT YET BEEN REPORTED OR IS UNDER 50 EMPS');
1056 fnd_file.put_line
1057 (which => fnd_file.log,
1058 buff => ' ');
1059
1060 ELSIF l_estab_rec.unit_number_3 IS NULL
1061 AND l_org_rec.l_status_code_2 IN ('1','3','4','5') THEN
1062
1063 fnd_file.put_line
1064 (which => fnd_file.log,
1065 buff => ' ');
1066 fnd_file.put_line
1067 (which => fnd_file.log,
1068 buff => '********** UNIT NAME AND NUMBER SHOULD NOT BE NULL AS '
1069 ||'THIS LOCATION HAS BEEN REPORTED ACCORDING TO THE Previously ');
1070 fnd_file.put_line
1071 (which => fnd_file.log,
1072 buff => 'Reported field IN the EEO1 Specific Data Extra '
1073 ||'Information Type. '
1074 ||' The unit NUMBER IS ' || l_estab_rec.unit_number_3
1075 ||' The unit name IS ' || l_estab_rec.unit_name_4
1076 ||' The unit address IS ' || l_estab_rec.unit_address_req_5
1077 || ' **********');
1078 fnd_file.put_line
1079 (which => fnd_file.log,
1080 buff => ' ');
1081
1082 END IF; --l_estab_rec.unit_number_3 IS NOT NULL
1083
1084
1085 g_message_text := '*2-for estab rec write form TYPE IS* '||l_org_rec.form_type;
1086 fnd_file.put_line
1087 (which => fnd_file.log,
1088 buff => g_message_text);
1089 g_message_text := '*2-l_estab_rec.reported_last_year_11 IS* '||l_estab_rec.reported_last_year_11;
1090 fnd_file.put_line
1091 (which => fnd_file.log,
1092 buff => g_message_text);
1093 g_message_text := '*2-l_estab_rec.hq IS* '||l_estab_rec.hq ;
1094 fnd_file.put_line
1095 (which => fnd_file.log,
1096 buff => g_message_text);
1097 g_message_text := '*2-status code IS* '||l_org_rec.l_status_code_2;
1098 fnd_file.put_line
1099 (which => fnd_file.log,
1100 buff => g_message_text);
1101 g_message_text := ' '||l_org_rec.l_status_code_2;
1102 fnd_file.put_line
1103 (which => fnd_file.log,
1104 buff => g_message_text);
1105 END IF; ----l_org_rec.form_type = 'S'
1106 --
1110 -- Set totals
1107 -- include hawaii IN main processing
1108 -- IF state = 'HI' everyone counted as white.
1109 --
1111 --
1112 -- grand total for column a (Hispanic or latino male)
1113 --
1114 hr_utility.set_location(l_proc,70);
1115 l_estab_rec.a_10_grand_total := nvl(l_estab_rec.a_1_hl_male,0)+
1116 nvl(l_estab_rec.a_2_hl_male,0)+
1117 nvl(l_estab_rec.a_3_hl_male,0)+
1118 nvl(l_estab_rec.a_4_hl_male,0)+
1119 nvl(l_estab_rec.a_5_hl_male,0)+
1120 nvl(l_estab_rec.a_6_hl_male,0)+
1121 nvl(l_estab_rec.a_7_hl_male,0)+
1122 nvl(l_estab_rec.a_8_hl_male,0)+
1123 nvl(l_estab_rec.a_9_hl_male,0)+
1124 nvl(l_estab_rec.a_10_hl_male,0);
1125 --
1126 hr_utility.trace('l_estab_rec.a_10_grand_total : '||l_estab_rec.a_10_grand_total);
1127 l_consol_rec.a_10_grand_total := (l_consol_rec.a_10_grand_total +
1128 l_estab_rec.a_10_grand_total);
1129 hr_utility.trace('l_consol_rec.a_10_grand_total : '||l_consol_rec.a_10_grand_total);
1130 /*
1131 g_message_text := 'grand total for estab rep column a '
1132 ||'(total males AND females) ';
1133 fnd_file.put_line
1134 (which => fnd_file.log,
1135 buff => g_message_text);
1136 g_message_text := 'l_estab_rec.a_10_grand_total *'
1137 ||l_estab_rec.a_10_grand_total;
1138 fnd_file.put_line
1139 (which => fnd_file.log,
1140 buff => g_message_text);
1141 fnd_file.put_line
1142 (which => fnd_file.log,
1143 buff => ' '); */
1144 --
1145 -- grand total for column b (Hispanic or Latino Female)
1146 --
1147 l_estab_rec.b_10_grand_total := nvl(l_estab_rec.b_1_hl_female,0)+
1148 nvl(l_estab_rec.b_2_hl_female,0)+
1149 nvl(l_estab_rec.b_3_hl_female,0)+
1150 nvl(l_estab_rec.b_4_hl_female,0)+
1151 nvl(l_estab_rec.b_5_hl_female,0)+
1152 nvl(l_estab_rec.b_6_hl_female,0)+
1153 nvl(l_estab_rec.b_7_hl_female,0)+
1154 nvl(l_estab_rec.b_8_hl_female,0)+
1155 nvl(l_estab_rec.b_9_hl_female,0)+
1156 nvl(l_estab_rec.b_10_hl_female,0);
1157 --
1158 l_consol_rec.b_10_grand_total := (l_consol_rec.b_10_grand_total +
1159 l_estab_rec.b_10_grand_total);
1160 --
1161 -- grand total for column c (total white males - non hisp)
1162 --
1163 l_estab_rec.c_10_grand_total := nvl(l_estab_rec.c_1_white_male,0)+
1164 nvl(l_estab_rec.c_2_white_male,0)+
1165 nvl(l_estab_rec.c_3_white_male,0)+
1166 nvl(l_estab_rec.c_4_white_male,0)+
1167 nvl(l_estab_rec.c_5_white_male,0)+
1168 nvl(l_estab_rec.c_6_white_male,0)+
1169 nvl(l_estab_rec.c_7_white_male,0)+
1170 nvl(l_estab_rec.c_8_white_male,0)+
1171 nvl(l_estab_rec.c_9_white_male,0)+
1172 nvl(l_estab_rec.c_10_white_male,0);
1173 --
1174 l_consol_rec.c_10_grand_total := (l_consol_rec.c_10_grand_total +
1175 l_estab_rec.c_10_grand_total);
1176 --
1177 -- grand total for column d (Black or African American - non hisp)
1178 --
1179 l_estab_rec.d_10_grand_total := nvl(l_estab_rec.d_1_black_male,0)+
1180 nvl(l_estab_rec.d_2_black_male,0)+
1181 nvl(l_estab_rec.d_3_black_male,0)+
1182 nvl(l_estab_rec.d_4_black_male,0)+
1183 nvl(l_estab_rec.d_5_black_male,0)+
1184 nvl(l_estab_rec.d_6_black_male,0)+
1185 nvl(l_estab_rec.d_7_black_male,0)+
1186 nvl(l_estab_rec.d_8_black_male,0)+
1187 nvl(l_estab_rec.d_9_black_male,0)+
1188 nvl(l_estab_rec.d_10_black_male,0);
1189 --
1190 l_consol_rec.d_10_grand_total := (l_consol_rec.d_10_grand_total +
1191 l_estab_rec.d_10_grand_total);
1192 --
1193 -- grand total for column e (total Native Hawaiian or Other Pacific Islanderr males - non hisp)
1194 --
1195 l_estab_rec.e_10_grand_total := nvl(l_estab_rec.e_1_latin_male,0)+
1196 nvl(l_estab_rec.e_2_latin_male,0)+
1197 nvl(l_estab_rec.e_3_latin_male,0)+
1198 nvl(l_estab_rec.e_4_latin_male,0)+
1199 nvl(l_estab_rec.e_5_latin_male,0)+
1200 nvl(l_estab_rec.e_6_latin_male,0)+
1201 nvl(l_estab_rec.e_7_latin_male,0)+
1202 nvl(l_estab_rec.e_8_latin_male,0)+
1203 nvl(l_estab_rec.e_9_latin_male,0)+
1204 nvl(l_estab_rec.e_10_latin_male,0);
1205 --
1206 l_consol_rec.e_10_grand_total := (l_consol_rec.e_10_grand_total +
1207 l_estab_rec.e_10_grand_total);
1208 --
1209 -- grand total for column f (total Asian males - non hisp)
1210 --
1211 l_estab_rec.f_10_grand_total := nvl(l_estab_rec.f_1_aspac_male,0)+
1212 nvl(l_estab_rec.f_2_aspac_male,0)+
1213 nvl(l_estab_rec.f_3_aspac_male,0)+
1214 nvl(l_estab_rec.f_4_aspac_male,0)+
1215 nvl(l_estab_rec.f_5_aspac_male,0)+
1219 nvl(l_estab_rec.f_9_aspac_male,0)+
1216 nvl(l_estab_rec.f_6_aspac_male,0)+
1217 nvl(l_estab_rec.f_7_aspac_male,0)+
1218 nvl(l_estab_rec.f_8_aspac_male,0)+
1220 nvl(l_estab_rec.f_10_aspac_male,0);
1221 --
1222 l_consol_rec.f_10_grand_total := (l_consol_rec.f_10_grand_total +
1223 l_estab_rec.f_10_grand_total);
1224 --
1225 -- grand total for column g (total American Indian or Alaska Native males - non hisp)
1226 --
1227 l_estab_rec.g_10_grand_total := nvl(l_estab_rec.g_1_ameri_male,0)+
1228 nvl(l_estab_rec.g_2_ameri_male,0)+
1229 nvl(l_estab_rec.g_3_ameri_male,0)+
1230 nvl(l_estab_rec.g_4_ameri_male,0)+
1231 nvl(l_estab_rec.g_5_ameri_male,0)+
1232 nvl(l_estab_rec.g_6_ameri_male,0)+
1233 nvl(l_estab_rec.g_7_ameri_male,0)+
1234 nvl(l_estab_rec.g_8_ameri_male,0)+
1235 nvl(l_estab_rec.g_9_ameri_male,0)+
1236 nvl(l_estab_rec.g_10_ameri_male,0);
1237 --
1238 l_consol_rec.g_10_grand_total := (l_consol_rec.g_10_grand_total +
1239 l_estab_rec.g_10_grand_total);
1240 --
1241 -- grand total for column h (total Two or more races males - non hisp)
1242 --
1243 l_estab_rec.h_10_grand_total := nvl(l_estab_rec.h_1_tmraces_male,0)+
1244 nvl(l_estab_rec.h_2_tmraces_male,0)+
1245 nvl(l_estab_rec.h_3_tmraces_male,0)+
1246 nvl(l_estab_rec.h_4_tmraces_male,0)+
1247 nvl(l_estab_rec.h_5_tmraces_male,0)+
1248 nvl(l_estab_rec.h_6_tmraces_male,0)+
1249 nvl(l_estab_rec.h_7_tmraces_male,0)+
1250 nvl(l_estab_rec.h_8_tmraces_male,0)+
1251 nvl(l_estab_rec.h_9_tmraces_male,0)+
1252 nvl(l_estab_rec.h_10_tmraces_male,0);
1253 --
1254 l_consol_rec.h_10_grand_total := (l_consol_rec.h_10_grand_total +
1255 l_estab_rec.h_10_grand_total);
1256 --
1257 -- grand total for column i (total White females - non hisp)
1258 --
1259 l_estab_rec.i_10_grand_total := nvl(l_estab_rec.i_1_white_fem,0)+
1260 nvl(l_estab_rec.i_2_white_fem,0)+
1261 nvl(l_estab_rec.i_3_white_fem,0)+
1262 nvl(l_estab_rec.i_4_white_fem,0)+
1263 nvl(l_estab_rec.i_5_white_fem,0)+
1264 nvl(l_estab_rec.i_6_white_fem,0)+
1265 nvl(l_estab_rec.i_7_white_fem,0)+
1266 nvl(l_estab_rec.i_8_white_fem,0)+
1267 nvl(l_estab_rec.i_9_white_fem,0)+
1268 nvl(l_estab_rec.i_10_white_fem,0);
1269 --
1270 l_consol_rec.i_10_grand_total := (l_consol_rec.i_10_grand_total +
1271 l_estab_rec.i_10_grand_total);
1272 --
1273 -- grand total for column j (total Black or African American females - non hisp)
1274 --
1275 l_estab_rec.j_10_grand_total := nvl(l_estab_rec.j_1_black_fem,0)+
1276 nvl(l_estab_rec.j_2_black_fem,0)+
1277 nvl(l_estab_rec.j_3_black_fem,0)+
1278 nvl(l_estab_rec.j_4_black_fem,0)+
1279 nvl(l_estab_rec.j_5_black_fem,0)+
1280 nvl(l_estab_rec.j_6_black_fem,0)+
1281 nvl(l_estab_rec.j_7_black_fem,0)+
1282 nvl(l_estab_rec.j_8_black_fem,0)+
1283 nvl(l_estab_rec.j_9_black_fem,0)+
1284 nvl(l_estab_rec.j_10_black_fem,0);
1285 --
1286 l_consol_rec.j_10_grand_total := (l_consol_rec.j_10_grand_total +
1287 l_estab_rec.j_10_grand_total);
1288 --
1289 -- grand total for column k (total Native Hawaiian or Other Pacific Islander females - non hisp)
1290 --
1291 l_estab_rec.k_10_grand_total := nvl(l_estab_rec.k_1_latin_fem,0)+
1292 nvl(l_estab_rec.k_2_latin_fem,0)+
1293 nvl(l_estab_rec.k_3_latin_fem,0)+
1294 nvl(l_estab_rec.k_4_latin_fem,0)+
1295 nvl(l_estab_rec.k_5_latin_fem,0)+
1296 nvl(l_estab_rec.k_6_latin_fem,0)+
1297 nvl(l_estab_rec.k_7_latin_fem,0)+
1298 nvl(l_estab_rec.k_8_latin_fem,0)+
1299 nvl(l_estab_rec.k_9_latin_fem,0)+
1300 nvl(l_estab_rec.k_10_latin_fem,0);
1301
1302 l_consol_rec.k_10_grand_total := (l_consol_rec.k_10_grand_total +
1303 l_estab_rec.k_10_grand_total);
1304
1305 --
1306 -- grand total for column L(total Asian females - non hisp)
1307 --
1308 l_estab_rec.l_10_grand_total := nvl(l_estab_rec.l_1_aspac_fem,0)+
1309 nvl(l_estab_rec.l_2_aspac_fem,0)+
1310 nvl(l_estab_rec.l_3_aspac_fem,0)+
1311 nvl(l_estab_rec.l_4_aspac_fem,0)+
1312 nvl(l_estab_rec.l_5_aspac_fem,0)+
1313 nvl(l_estab_rec.l_6_aspac_fem,0)+
1314 nvl(l_estab_rec.l_7_aspac_fem,0)+
1315 nvl(l_estab_rec.l_8_aspac_fem,0)+
1316 nvl(l_estab_rec.l_9_aspac_fem,0)+
1317 nvl(l_estab_rec.l_10_aspac_fem,0);
1318
1319 l_consol_rec.l_10_grand_total := (l_consol_rec.l_10_grand_total +
1323 -- grand total for column M(total American Indian or Alaska Native females - non hisp)
1320 l_estab_rec.l_10_grand_total);
1321
1322 --
1324 --
1325 l_estab_rec.m_10_grand_total := nvl(l_estab_rec.m_1_ameri_fem,0)+
1326 nvl(l_estab_rec.m_2_ameri_fem,0)+
1327 nvl(l_estab_rec.m_3_ameri_fem,0)+
1328 nvl(l_estab_rec.m_4_ameri_fem,0)+
1329 nvl(l_estab_rec.m_5_ameri_fem,0)+
1330 nvl(l_estab_rec.m_6_ameri_fem,0)+
1331 nvl(l_estab_rec.m_7_ameri_fem,0)+
1332 nvl(l_estab_rec.m_8_ameri_fem,0)+
1333 nvl(l_estab_rec.m_9_ameri_fem,0)+
1334 nvl(l_estab_rec.m_10_ameri_fem,0);
1335
1336 l_consol_rec.m_10_grand_total := (l_consol_rec.m_10_grand_total +
1337 l_estab_rec.m_10_grand_total);
1338 --
1339 -- grand total for column N(total Two or more races females - non hisp)
1340 --
1341 l_estab_rec.n_10_grand_total := nvl(l_estab_rec.n_1_tmraces_female,0)+
1342 nvl(l_estab_rec.n_2_tmraces_female,0)+
1343 nvl(l_estab_rec.n_3_tmraces_female,0)+
1344 nvl(l_estab_rec.n_4_tmraces_female,0)+
1345 nvl(l_estab_rec.n_5_tmraces_female,0)+
1346 nvl(l_estab_rec.n_6_tmraces_female,0)+
1347 nvl(l_estab_rec.n_7_tmraces_female,0)+
1348 nvl(l_estab_rec.n_8_tmraces_female,0)+
1349 nvl(l_estab_rec.n_9_tmraces_female,0)+
1350 nvl(l_estab_rec.n_10_tmraces_female,0);
1351
1352 l_consol_rec.n_10_grand_total := (l_consol_rec.n_10_grand_total +
1353 l_estab_rec.n_10_grand_total);
1354
1355 --
1356 -- grand total for column O(total Total Col A - N )
1357 --
1358 l_estab_rec.o_10_grand_total := nvl(l_estab_rec.a_10_grand_total,0)+
1359 nvl(l_estab_rec.b_10_grand_total,0) +
1360 nvl(l_estab_rec.c_10_grand_total,0) +
1361 nvl(l_estab_rec.d_10_grand_total,0) +
1362 nvl(l_estab_rec.e_10_grand_total,0) +
1363 nvl(l_estab_rec.f_10_grand_total,0) +
1364 nvl(l_estab_rec.g_10_grand_total,0) +
1365 nvl(l_estab_rec.h_10_grand_total,0) +
1366 nvl(l_estab_rec.i_10_grand_total,0) +
1367 nvl(l_estab_rec.j_10_grand_total,0) +
1368 nvl(l_estab_rec.k_10_grand_total,0) +
1369 nvl(l_estab_rec.l_10_grand_total,0) +
1370 nvl(l_estab_rec.m_10_grand_total,0) +
1371 nvl(l_estab_rec.n_10_grand_total,0);
1372
1373 l_consol_rec.o_10_grand_total := (l_consol_rec.o_10_grand_total +
1374 l_estab_rec.o_10_grand_total);
1375
1376
1377 -- IF Hawaii
1378 /* IF l_estab_rec.state_8 = 'HI' THEN
1379 --
1380 -- count all men as white
1381 --
1382 l_estab_rec.c_10_grand_total := (l_estab_rec.a_10_grand_total +
1383 l_estab_rec.c_10_grand_total +
1384 l_estab_rec.d_10_grand_total +
1385 l_estab_rec.e_10_grand_total +
1386 l_estab_rec.f_10_grand_total +
1387 l_estab_rec.g_10_grand_total+
1388 l_estab_rec.h_10_grand_total);
1389
1390 l_estab_rec.a_10_grand_total := 0;
1391 l_estab_rec.d_10_grand_total := 0;
1392 l_estab_rec.e_10_grand_total := 0;
1393 l_estab_rec.f_10_grand_total := 0;
1394 l_estab_rec.g_10_grand_total := 0;
1395 l_estab_rec.h_10_grand_total := 0;
1396
1397 l_estab_rec.i_10_grand_total := (l_estab_rec.b_10_grand_total +
1398 l_estab_rec.i_10_grand_total +
1399 l_estab_rec.j_10_grand_total +
1400 l_estab_rec.k_10_grand_total +
1401 l_estab_rec.l_10_grand_total +
1402 l_estab_rec.m_10_grand_total+
1403 l_estab_rec.n_10_grand_total);
1404
1405 l_estab_rec.b_10_grand_total := 0;
1406 l_estab_rec.j_10_grand_total := 0;
1407 l_estab_rec.k_10_grand_total := 0;
1408 l_estab_rec.l_10_grand_total := 0;
1409 l_estab_rec.m_10_grand_total := 0;
1410 l_estab_rec.n_10_grand_total := 0;
1411
1412 END IF; --l_estab_rec.state_8 = 'HI' */
1413
1414 l_estab_unit_name_4 := ltrim(substr(
1415 translate(l_estab_rec.unit_name_4,'`;"|\-+=_#$%^&*@!~:<>?/()[]{},',' '),1,35),'1234567890');
1416
1417 l_estab_unit_address_5 := substr(
1418 translate(l_estab_rec.unit_address_req_5,'`;"|\-+=_#$%^&*@!~:<>?/()[]{},',' '),1,34);
1419 l_estab_unit_address_6 := substr(
1420 translate(l_estab_rec.unit_address_6,'`;"|\-+=_#$%^&*@!~:<>?/()[]{},',' '),1,25);
1421
1422
1423
1424 hr_utility.trace('l_estab_unit_name_4 :'||l_estab_unit_name_4);
1425 hr_utility.trace('l_estab_unit_address_5 : '||l_estab_unit_address_5);
1426 hr_utility.trace('l_estab_unit_address_6 : '||l_estab_unit_address_6);
1427
1428 l_string :=
1429 -- 1
1430 nvl(lpad(l_org_rec.company_number_1,7,0),(lpad(' ',7,' ')))
1434 -- 3
1431 -- 2
1432 -- status code
1433 ||l_org_rec.l_status_code_2
1435 ||nvl(lpad(SUBSTR(l_estab_rec.unit_number_3,1,7),7,0),
1436 ('0000000'))
1437 -- 4
1438 ||nvl(rpad(l_estab_unit_name_4,35,' '),(lpad(' ',35,' ')))
1439 -- 5
1440 ||nvl(rpad(l_estab_unit_address_5,34,' '),(lpad(' ',34,' ')))
1441 -- 6
1442 ||nvl(rpad(l_estab_unit_address_6,25,' '),(lpad(' ',25,' ')))
1443
1444 -- 7
1445 ||nvl(rpad(replace(replace(SUBSTR(l_estab_rec.city_7,1,20),',','')
1446 ,'.',''),20,' '),(lpad(' ',20,' ')))
1447 -- 8
1448 ||nvl(rpad(l_estab_rec.state_8,2),(lpad(' ',8,' ')))
1449 -- 9
1450 ||nvl(rpad(l_estab_rec.zip_code_9,5),(lpad(' ',5,' ')))
1451 -- 10
1452 ||rpad(l_estab_rec.reported_last_year_11,1)
1453 -- 11
1454 ||rpad(l_org_rec.c1_over_100_13,1)
1455 -- 12
1456 ||rpad(l_org_rec.c2_affiliated_14,1)
1457 -- 13
1458 ||rpad(nvl(l_estab_rec.gov_contract_15,l_org_rec.gov_contract_15)
1459 ,1)
1460 -- 14
1461 ||nvl(lpad(nvl(l_estab_rec.duns_16,l_org_rec.duns_16),9,0)
1462 ,(lpad(' ',9,' ')))
1463 -- 15
1464 ||nvl(rpad(replace(replace(l_estab_rec.county_17,',',''),'.','')
1465 ,18),(lpad(' ',18,' ')))
1466 -- 16
1467 ||rpad(l_org_rec.l_d1_payroll_period_18,16)
1468 -- 17
1469 ||nvl(lpad(nvl(l_estab_rec.naics_21,l_org_rec.naics_21),6,0), --BUG4494412
1470 (lpad(' ',6,' ')))
1471 -- 18
1472 ||nvl(rpad(ltrim(replace(replace(l_org_rec.title_cert_off_22,',','')
1473 ,'.',''),'1234567890'),35),(lpad(' ',35,' ')))
1474 -- 19
1475 ||nvl(rpad(ltrim(replace(replace(l_org_rec.name_cert_off_23,',','')
1476 ,'.',''),'1234567890'),35),(lpad(' ',35,' ')))
1477 -- 20
1478 ||nvl(rpad(replace(replace(l_org_rec.tel_num_24,',',''),'.','')
1479 ,10),(lpad(' ',10,' ')))
1480 -- 21
1481 ||nvl(rpad(replace(l_org_rec.email_26,',',''),40),
1482 (lpad(' ',40,' ')))||
1483 --
1484 lpad(l_estab_rec.a_1_hl_male,6,0)||
1485 lpad(l_estab_rec.b_1_hl_female,6,0)||
1486 lpad(l_estab_rec.c_1_white_male,6,0)||
1487 lpad(l_estab_rec.d_1_black_male,6,0)||
1488 lpad(l_estab_rec.e_1_latin_male,6,0)||
1489 lpad(l_estab_rec.f_1_aspac_male,6,0)||
1490 lpad(l_estab_rec.g_1_ameri_male,6,0)||
1491 lpad(l_estab_rec.h_1_tmraces_male,6,0)||
1492 lpad(l_estab_rec.i_1_white_fem,6,0)||
1493 lpad(l_estab_rec.j_1_black_fem,6,0)||
1494 lpad(l_estab_rec.k_1_latin_fem,6,0)||
1495 lpad(l_estab_rec.l_1_aspac_fem,6,0)||
1496 lpad(l_estab_rec.m_1_ameri_fem,6,0)||
1497 lpad(l_estab_rec.n_1_tmraces_female,6,0)||
1498 lpad(l_estab_rec.o_1_total_cat,7,0)||
1499 --
1500 lpad(l_estab_rec.a_2_hl_male,6,0)||
1501 lpad(l_estab_rec.b_2_hl_female,6,0)||
1502 lpad(l_estab_rec.c_2_white_male,6,0)||
1503 lpad(l_estab_rec.d_2_black_male,6,0)||
1504 lpad(l_estab_rec.e_2_latin_male,6,0)||
1505 lpad(l_estab_rec.f_2_aspac_male,6,0)||
1506 lpad(l_estab_rec.g_2_ameri_male,6,0)||
1507 lpad(l_estab_rec.h_2_tmraces_male,6,0)||
1508 lpad(l_estab_rec.i_2_white_fem,6,0)||
1509 lpad(l_estab_rec.j_2_black_fem,6,0)||
1510 lpad(l_estab_rec.k_2_latin_fem,6,0)||
1511 lpad(l_estab_rec.l_2_aspac_fem,6,0)||
1512 lpad(l_estab_rec.m_2_ameri_fem,6,0)||
1513 lpad(l_estab_rec.n_2_tmraces_female,6,0)||
1514 lpad(l_estab_rec.o_2_total_cat,7,0)||
1515 --
1516 lpad(l_estab_rec.a_3_hl_male,6,0)||
1517 lpad(l_estab_rec.b_3_hl_female,6,0)||
1518 lpad(l_estab_rec.c_3_white_male,6,0)||
1519 lpad(l_estab_rec.d_3_black_male,6,0)||
1520 lpad(l_estab_rec.e_3_latin_male,6,0)||
1521 lpad(l_estab_rec.f_3_aspac_male,6,0)||
1522 lpad(l_estab_rec.g_3_ameri_male,6,0)||
1523 lpad(l_estab_rec.h_3_tmraces_male,6,0)||
1524 lpad(l_estab_rec.i_3_white_fem,6,0)||
1525 lpad(l_estab_rec.j_3_black_fem,6,0)||
1526 lpad(l_estab_rec.k_3_latin_fem,6,0)||
1527 lpad(l_estab_rec.l_3_aspac_fem,6,0)||
1528 lpad(l_estab_rec.m_3_ameri_fem,6,0)||
1529 lpad(l_estab_rec.n_3_tmraces_female,6,0)||
1530 lpad(l_estab_rec.o_3_total_cat,7,0)||
1531 --
1532 lpad(l_estab_rec.a_4_hl_male,6,0)||
1533 lpad(l_estab_rec.b_4_hl_female,6,0)||
1534 lpad(l_estab_rec.c_4_white_male,6,0)||
1535 lpad(l_estab_rec.d_4_black_male,6,0)||
1536 lpad(l_estab_rec.e_4_latin_male,6,0)||
1537 lpad(l_estab_rec.f_4_aspac_male,6,0)||
1538 lpad(l_estab_rec.g_4_ameri_male,6,0)||
1539 lpad(l_estab_rec.h_4_tmraces_male,6,0)||
1540 lpad(l_estab_rec.i_4_white_fem,6,0)||
1541 lpad(l_estab_rec.j_4_black_fem,6,0)||
1542 lpad(l_estab_rec.k_4_latin_fem,6,0)||
1543 lpad(l_estab_rec.l_4_aspac_fem,6,0)||
1544 lpad(l_estab_rec.m_4_ameri_fem,6,0)||
1545 lpad(l_estab_rec.n_4_tmraces_female,6,0)||
1546 lpad(l_estab_rec.o_4_total_cat,7,0)||
1547 --
1548 lpad(l_estab_rec.a_5_hl_male,6,0)||
1552 lpad(l_estab_rec.e_5_latin_male,6,0)||
1549 lpad(l_estab_rec.b_5_hl_female,6,0)||
1550 lpad(l_estab_rec.c_5_white_male,6,0)||
1551 lpad(l_estab_rec.d_5_black_male,6,0)||
1553 lpad(l_estab_rec.f_5_aspac_male,6,0)||
1554 lpad(l_estab_rec.g_5_ameri_male,6,0)||
1555 lpad(l_estab_rec.h_5_tmraces_male,6,0)||
1556 lpad(l_estab_rec.i_5_white_fem,6,0)||
1557 lpad(l_estab_rec.j_5_black_fem,6,0)||
1558 lpad(l_estab_rec.k_5_latin_fem,6,0)||
1559 lpad(l_estab_rec.l_5_aspac_fem,6,0)||
1560 lpad(l_estab_rec.m_5_ameri_fem,6,0)||
1561 lpad(l_estab_rec.n_5_tmraces_female,6,0)||
1562 lpad(l_estab_rec.o_5_total_cat,7,0)||
1563 --
1564 lpad(l_estab_rec.a_6_hl_male,6,0)||
1565 lpad(l_estab_rec.b_6_hl_female,6,0)||
1566 lpad(l_estab_rec.c_6_white_male,6,0)||
1567 lpad(l_estab_rec.d_6_black_male,6,0)||
1568 lpad(l_estab_rec.e_6_latin_male,6,0)||
1569 lpad(l_estab_rec.f_6_aspac_male,6,0)||
1570 lpad(l_estab_rec.g_6_ameri_male,6,0)||
1571 lpad(l_estab_rec.h_6_tmraces_male,6,0)||
1572 lpad(l_estab_rec.i_6_white_fem,6,0)||
1573 lpad(l_estab_rec.j_6_black_fem,6,0)||
1574 lpad(l_estab_rec.k_6_latin_fem,6,0)||
1575 lpad(l_estab_rec.l_6_aspac_fem,6,0)||
1576 lpad(l_estab_rec.m_6_ameri_fem,6,0)||
1577 lpad(l_estab_rec.n_6_tmraces_female,6,0)||
1578 lpad(l_estab_rec.o_6_total_cat,7,0)||
1579 --
1580 lpad(l_estab_rec.a_7_hl_male,6,0)||
1581 lpad(l_estab_rec.b_7_hl_female,6,0)||
1582 lpad(l_estab_rec.c_7_white_male,6,0)||
1583 lpad(l_estab_rec.d_7_black_male,6,0)||
1584 lpad(l_estab_rec.e_7_latin_male,6,0)||
1585 lpad(l_estab_rec.f_7_aspac_male,6,0)||
1586 lpad(l_estab_rec.g_7_ameri_male,6,0)||
1587 lpad(l_estab_rec.h_7_tmraces_male,6,0)||
1588 lpad(l_estab_rec.i_7_white_fem,6,0)||
1589 lpad(l_estab_rec.j_7_black_fem,6,0)||
1590 lpad(l_estab_rec.k_7_latin_fem,6,0)||
1591 lpad(l_estab_rec.l_7_aspac_fem,6,0)||
1592 lpad(l_estab_rec.m_7_ameri_fem,6,0)||
1593 lpad(l_estab_rec.n_7_tmraces_female,6,0)||
1594 lpad(l_estab_rec.o_7_total_cat,7,0)||
1595 --
1596 lpad(l_estab_rec.a_8_hl_male,6,0)||
1597 lpad(l_estab_rec.b_8_hl_female,6,0)||
1598 lpad(l_estab_rec.c_8_white_male,6,0)||
1599 lpad(l_estab_rec.d_8_black_male,6,0)||
1600 lpad(l_estab_rec.e_8_latin_male,6,0)||
1601 lpad(l_estab_rec.f_8_aspac_male,6,0)||
1602 lpad(l_estab_rec.g_8_ameri_male,6,0)||
1603 lpad(l_estab_rec.h_8_tmraces_male,6,0)||
1604 lpad(l_estab_rec.i_8_white_fem,6,0)||
1605 lpad(l_estab_rec.j_8_black_fem,6,0)||
1606 lpad(l_estab_rec.k_8_latin_fem,6,0)||
1607 lpad(l_estab_rec.l_8_aspac_fem,6,0)||
1608 lpad(l_estab_rec.m_8_ameri_fem,6,0)||
1609 lpad(l_estab_rec.n_8_tmraces_female,6,0)||
1610 lpad(l_estab_rec.o_8_total_cat,7,0)||
1611 --
1612 lpad(l_estab_rec.a_9_hl_male,6,0)||
1613 lpad(l_estab_rec.b_9_hl_female,6,0)||
1614 lpad(l_estab_rec.c_9_white_male,6,0)||
1615 lpad(l_estab_rec.d_9_black_male,6,0)||
1616 lpad(l_estab_rec.e_9_latin_male,6,0)||
1617 lpad(l_estab_rec.f_9_aspac_male,6,0)||
1618 lpad(l_estab_rec.g_9_ameri_male,6,0)||
1619 lpad(l_estab_rec.h_9_tmraces_male,6,0)||
1620 lpad(l_estab_rec.i_9_white_fem,6,0)||
1621 lpad(l_estab_rec.j_9_black_fem,6,0)||
1622 lpad(l_estab_rec.k_9_latin_fem,6,0)||
1623 lpad(l_estab_rec.l_9_aspac_fem,6,0)||
1624 lpad(l_estab_rec.m_9_ameri_fem,6,0)||
1625 lpad(l_estab_rec.n_9_tmraces_female,6,0)||
1626 lpad(l_estab_rec.o_9_total_cat,7,0)||
1627 --
1628 lpad(l_estab_rec.a_10_hl_male,6,0)||
1629 lpad(l_estab_rec.b_10_hl_female,6,0)||
1630 lpad(l_estab_rec.c_10_white_male,6,0)||
1631 lpad(l_estab_rec.d_10_black_male,6,0)||
1632 lpad(l_estab_rec.e_10_latin_male,6,0)||
1633 lpad(l_estab_rec.f_10_aspac_male,6,0)||
1634 lpad(l_estab_rec.g_10_ameri_male,6,0)||
1635 lpad(l_estab_rec.h_10_tmraces_male,6,0)||
1636 lpad(l_estab_rec.i_10_white_fem,6,0)||
1637 lpad(l_estab_rec.j_10_black_fem,6,0)||
1638 lpad(l_estab_rec.k_10_latin_fem,6,0)||
1639 lpad(l_estab_rec.l_10_aspac_fem,6,0)||
1640 lpad(l_estab_rec.m_10_ameri_fem,6,0)||
1641 lpad(l_estab_rec.n_10_tmraces_female,6,0)||
1642 lpad(l_estab_rec.o_10_total_cat,7,0)||
1643 --
1644 lpad(l_estab_rec.a_10_grand_total,6,0) ||
1645 lpad(l_estab_rec.b_10_grand_total,6,0) ||
1646 lpad(l_estab_rec.c_10_grand_total,6,0) ||
1647 lpad(l_estab_rec.d_10_grand_total,6,0) ||
1648 lpad(l_estab_rec.e_10_grand_total,6,0) ||
1649 lpad(l_estab_rec.f_10_grand_total,6,0) ||
1650 lpad(l_estab_rec.g_10_grand_total,6,0) ||
1651 lpad(l_estab_rec.h_10_grand_total,6,0) ||
1652 lpad(l_estab_rec.i_10_grand_total,6,0) ||
1653 lpad(l_estab_rec.j_10_grand_total,6,0) ||
1654 lpad(l_estab_rec.k_10_grand_total,6,0)||
1655 lpad(l_estab_rec.l_10_grand_total,6,0)||
1656 lpad(l_estab_rec.m_10_grand_total,6,0)||
1657 lpad(l_estab_rec.n_10_grand_total,6,0)||
1658 lpad(l_estab_rec.o_10_grand_total,7,0);
1659
1663 (lpad(' ',7,' ')))
1660 /*l_string := nvl(lpad(l_org_rec.company_number_1,7,0),(lpad(' ',7,' ')))
1661 ||rpad(l_org_rec.l_status_code_2,1)
1662 ||nvl(lpad(SUBSTR(l_estab_rec.unit_number_3,1,7),7,0),
1664 ||nvl(rpad(ltrim(replace(replace(l_estab_rec.unit_name_4,',','')
1665 ,'.',''),'1234567890'),35,' '),(lpad(' ',35,' ')))
1666 ||nvl(rpad(replace(replace
1667 (SUBSTR(l_estab_rec.unit_address_req_5,1,34)
1668 ,',',''),'.',''),34,' '),(lpad(' ',34,' ')))
1669 ||nvl(rpad(replace(replace(SUBSTR(l_estab_rec.unit_address_6,1,25)
1670 ,',',''),'.',''),25,' '),(lpad(' ',25,' ')))
1671 ||nvl(rpad(replace(replace(SUBSTR(l_estab_rec.city_7,1,20),',','')
1672 ,'.',''),20,' '),(lpad(' ',20,' ')))
1673 ||nvl(rpad(l_estab_rec.state_8,2),(lpad(' ',8,' ')))
1674 ||nvl(rpad(l_estab_rec.zip_code_9,5),(lpad(' ',5,' ')))
1675 ||nvl(rpad(l_estab_rec.zip_code_last_4_10,4),(lpad(' ',4,' ')))
1676 ||rpad(l_estab_rec.reported_last_year_11,1)
1677 ||nvl(lpad(l_estab_rec.ein_12,9,0),(lpad(' ',9,' ')))
1678 ||rpad(l_org_rec.c1_over_100_13,1)
1679 ||rpad(l_org_rec.c2_affiliated_14,1)
1680 ||rpad(nvl(l_estab_rec.gov_contract_15,l_org_rec.gov_contract_15)
1681 ,1)
1682 ||nvl(lpad(nvl(l_estab_rec.duns_16,l_org_rec.duns_16),9,0)
1683 ,(lpad(' ',9,' ')))
1684 ||nvl(rpad(replace(replace(l_estab_rec.county_17,',',''),'.','')
1685 ,18),(lpad(' ',18,' ')))
1686 ||rpad(l_org_rec.l_d1_payroll_period_18,16)
1687 ||rpad(nvl(l_estab_rec.apprentices_emp_19
1688 ,l_org_rec.apprentices_emp_19),1)
1689 ||nvl(lpad(nvl(l_estab_rec.sic_20,l_org_rec.sic_20),4,0)
1690 ,(lpad(' ',4,' ')))
1691 ||nvl(lpad(nvl(l_estab_rec.naics_21,l_org_rec.naics_21),6,0)
1692 ,(lpad(' ',6,' ')))
1693 ||nvl(rpad(ltrim(replace(replace(l_org_rec.title_cert_off_22,',','')
1694 ,'.',''),'1234567890'),35),(lpad(' ',35,' ')))
1695 ||nvl(rpad(ltrim(replace(replace(l_org_rec.name_cert_off_23,',','')
1696 ,'.',''),'1234567890'),35),(lpad(' ',35,' ')))
1697 ||nvl(rpad(replace(replace(l_org_rec.tel_num_24,',',''),'.','')
1698 ,10),(lpad(' ',10,' ')))
1699 ||nvl(rpad(replace(replace(l_org_rec.fax_num_25,',',''),'.','')
1700 ,10),(lpad(' ',10,' ')))
1701 ||nvl(rpad(replace(l_org_rec.email_26,',',''),40)
1702 ,(lpad(' ',40,' ')))
1703 ||
1704 --
1705 lpad(l_estab_rec.a_1_total_mf,7,0) ||
1706 lpad(l_estab_rec.b_1_white_male,6,0)||
1707 lpad(l_estab_rec.c_1_black_male,6,0)||
1708 lpad(l_estab_rec.d_1_latin_male,6,0)||
1709 lpad(l_estab_rec.e_1_aspac_male,6,0)||
1710 lpad(l_estab_rec.f_1_ameri_male,6,0)||
1711 lpad(l_estab_rec.g_1_white_fem,6,0) ||
1712 lpad(l_estab_rec.h_1_black_fem,6,0) ||
1713 lpad(l_estab_rec.i_1_latin_fem,6,0) ||
1714 lpad(l_estab_rec.j_1_aspac_fem,6,0) ||
1715 lpad(l_estab_rec.k_1_ameri_fem,6,0) ||
1716 --
1717 lpad(l_estab_rec.a_2_total_mf,7,0) ||
1718 lpad(l_estab_rec.b_2_white_male,6,0)||
1719 lpad(l_estab_rec.c_2_black_male,6,0)||
1720 lpad(l_estab_rec.d_2_latin_male,6,0)||
1721 lpad(l_estab_rec.e_2_aspac_male,6,0)||
1722 lpad(l_estab_rec.f_2_ameri_male,6,0)||
1723 lpad(l_estab_rec.g_2_white_fem,6,0) ||
1724 lpad(l_estab_rec.h_2_black_fem,6,0) ||
1725 lpad(l_estab_rec.i_2_latin_fem,6,0) ||
1726 lpad(l_estab_rec.j_2_aspac_fem,6,0) ||
1727 lpad(l_estab_rec.k_2_ameri_fem,6,0) ||
1728 --
1729 lpad(l_estab_rec.a_3_total_mf,7,0) ||
1730 lpad(l_estab_rec.b_3_white_male,6,0) ||
1731 lpad(l_estab_rec.c_3_black_male,6,0) ||
1732 lpad(l_estab_rec.d_3_latin_male,6,0) ||
1733 lpad(l_estab_rec.e_3_aspac_male,6,0) ||
1734 lpad(l_estab_rec.f_3_ameri_male,6,0) ||
1735 lpad(l_estab_rec.g_3_white_fem,6,0) ||
1736 lpad(l_estab_rec.h_3_black_fem,6,0) ||
1737 lpad(l_estab_rec.i_3_latin_fem,6,0) ||
1738 lpad(l_estab_rec.j_3_aspac_fem,6,0) ||
1739 lpad(l_estab_rec.k_3_ameri_fem,6,0) ||
1740 --
1741 lpad(l_estab_rec.a_4_total_mf,7,0) ||
1742 lpad(l_estab_rec.b_4_white_male,6,0) ||
1743 lpad(l_estab_rec.c_4_black_male,6,0) ||
1744 lpad(l_estab_rec.d_4_latin_male,6,0) ||
1745 lpad(l_estab_rec.e_4_aspac_male,6,0) ||
1746 lpad(l_estab_rec.f_4_ameri_male,6,0) ||
1747 lpad(l_estab_rec.g_4_white_fem,6,0) ||
1748 lpad(l_estab_rec.h_4_black_fem,6,0) ||
1749 lpad(l_estab_rec.i_4_latin_fem,6,0) ||
1750 lpad(l_estab_rec.j_4_aspac_fem,6,0) ||
1751 lpad(l_estab_rec.k_4_ameri_fem,6,0) ||
1752 --
1753 lpad(l_estab_rec.a_5_total_mf,7,0) ||
1754 lpad(l_estab_rec.b_5_white_male,6,0) ||
1755 lpad(l_estab_rec.c_5_black_male,6,0) ||
1756 lpad(l_estab_rec.d_5_latin_male,6,0) ||
1757 lpad(l_estab_rec.e_5_aspac_male,6,0) ||
1758 lpad(l_estab_rec.f_5_ameri_male,6,0) ||
1759 lpad(l_estab_rec.g_5_white_fem,6,0) ||
1760 lpad(l_estab_rec.h_5_black_fem,6,0) ||
1761 lpad(l_estab_rec.i_5_latin_fem,6,0) ||
1765 lpad(l_estab_rec.a_6_total_mf,7,0) ||
1762 lpad(l_estab_rec.j_5_aspac_fem,6,0) ||
1763 lpad(l_estab_rec.k_5_ameri_fem,6,0) ||
1764 --
1766 lpad(l_estab_rec.b_6_white_male,6,0) ||
1767 lpad(l_estab_rec.c_6_black_male,6,0) ||
1768 lpad(l_estab_rec.d_6_latin_male,6,0) ||
1769 lpad(l_estab_rec.e_6_aspac_male,6,0) ||
1770 lpad(l_estab_rec.f_6_ameri_male,6,0) ||
1771 lpad(l_estab_rec.g_6_white_fem,6,0) ||
1772 lpad(l_estab_rec.h_6_black_fem,6,0) ||
1773 lpad(l_estab_rec.i_6_latin_fem,6,0) ||
1774 lpad(l_estab_rec.j_6_aspac_fem,6,0) ||
1775 lpad(l_estab_rec.k_6_ameri_fem,6,0) ||
1776 --
1777 lpad(l_estab_rec.a_7_total_mf,7,0) ||
1778 lpad(l_estab_rec.b_7_white_male,6,0) ||
1779 lpad(l_estab_rec.c_7_black_male,6,0) ||
1780 lpad(l_estab_rec.d_7_latin_male,6,0) ||
1781 lpad(l_estab_rec.e_7_aspac_male,6,0) ||
1782 lpad(l_estab_rec.f_7_ameri_male,6,0) ||
1783 lpad(l_estab_rec.g_7_white_fem,6,0) ||
1784 lpad(l_estab_rec.h_7_black_fem,6,0) ||
1785 lpad(l_estab_rec.i_7_latin_fem,6,0) ||
1786 lpad(l_estab_rec.j_7_aspac_fem,6,0) ||
1787 lpad(l_estab_rec.k_7_ameri_fem,6,0) ||
1788 --
1789 lpad(l_estab_rec.a_8_total_mf,7,0) ||
1790 lpad(l_estab_rec.b_8_white_male,6,0) ||
1791 lpad(l_estab_rec.c_8_black_male,6,0) ||
1792 lpad(l_estab_rec.d_8_latin_male,6,0) ||
1793 lpad(l_estab_rec.e_8_aspac_male,6,0) ||
1794 lpad(l_estab_rec.f_8_ameri_male,6,0) ||
1795 lpad(l_estab_rec.g_8_white_fem,6,0) ||
1796 lpad(l_estab_rec.h_8_black_fem,6,0) ||
1797 lpad(l_estab_rec.i_8_latin_fem,6,0) ||
1798 lpad(l_estab_rec.j_8_aspac_fem,6,0) ||
1799 lpad(l_estab_rec.k_8_ameri_fem,6,0) ||
1800 --
1801 lpad(l_estab_rec.a_9_total_mf,7,0) ||
1802 lpad(l_estab_rec.b_9_white_male,6,0) ||
1803 lpad(l_estab_rec.c_9_black_male,6,0) ||
1804 lpad(l_estab_rec.d_9_latin_male,6,0) ||
1805 lpad(l_estab_rec.e_9_aspac_male,6,0) ||
1806 lpad(l_estab_rec.f_9_ameri_male,6,0) ||
1807 lpad(l_estab_rec.g_9_white_fem,6,0) ||
1808 lpad(l_estab_rec.h_9_black_fem,6,0) ||
1809 lpad(l_estab_rec.i_9_latin_fem,6,0) ||
1810 lpad(l_estab_rec.j_9_aspac_fem,6,0) ||
1811 lpad(l_estab_rec.k_9_ameri_fem,6,0) ||
1812 --
1813 lpad(l_estab_rec.a_10_grand_total,7,0) ||
1814 lpad(l_estab_rec.b_10_grand_total,6,0) ||
1815 lpad(l_estab_rec.c_10_grand_total,6,0) ||
1816 lpad(l_estab_rec.d_10_grand_total,6,0) ||
1817 lpad(l_estab_rec.e_10_grand_total,6,0) ||
1818 lpad(l_estab_rec.f_10_grand_total,6,0) ||
1819 lpad(l_estab_rec.g_10_grand_total,6,0) ||
1820 lpad(l_estab_rec.h_10_grand_total,6,0) ||
1821 lpad(l_estab_rec.i_10_grand_total,6,0) ||
1822 lpad(l_estab_rec.j_10_grand_total,6,0) ||
1823 lpad(l_estab_rec.k_10_grand_total,6,0) ||
1824 --
1825 lpad(l_estab_rec.a_11_last_year_grand_total,7,0) ||
1826 lpad(l_estab_rec.b_11_last_year_grand_total,6,0) ||
1827 lpad(l_estab_rec.c_11_last_year_grand_total,6,0) ||
1828 lpad(l_estab_rec.d_11_last_year_grand_total,6,0) ||
1829 lpad(l_estab_rec.e_11_last_year_grand_total,6,0) ||
1830 lpad(l_estab_rec.f_11_last_year_grand_total,6,0) ||
1831 lpad(l_estab_rec.g_11_last_year_grand_total,6,0) ||
1832 lpad(l_estab_rec.h_11_last_year_grand_total,6,0) ||
1833 lpad(l_estab_rec.i_11_last_year_grand_total,6,0) ||
1834 lpad(l_estab_rec.j_11_last_year_grand_total,6,0) ||
1835 lpad(l_estab_rec.k_11_last_year_grand_total,6,0); */
1836
1837 /*g_message_text := 'l_estab_rec.a_1_total_mf -> '|| l_estab_rec.a_1_total_mf;
1838 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1839
1840 g_message_text := 'l_estab_rec.a_2_total_mf -> '|| l_estab_rec.a_2_total_mf;
1841 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1842
1843 g_message_text := 'l_estab_rec.a_3_total_mf -> '|| l_estab_rec.a_3_total_mf;
1844 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1845
1846 g_message_text := 'l_estab_rec.a_4_total_mf -> '|| l_estab_rec.a_4_total_mf;
1847 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1848
1849 g_message_text := 'l_estab_rec.a_5_total_mf -> '|| l_estab_rec.a_5_total_mf;
1850 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1851
1852 g_message_text := 'l_estab_rec.a_6_total_mf -> '|| l_estab_rec.a_6_total_mf;
1853 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1854
1855 g_message_text := 'l_estab_rec.a_7_total_mf -> '|| l_estab_rec.a_7_total_mf;
1856 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1857
1858 g_message_text := 'l_estab_rec.a_8_total_mf -> '|| l_estab_rec.a_8_total_mf;
1859 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1860
1861
1862 g_message_text := 'l_estab_rec.a_9_total_mf -> '|| l_estab_rec.a_9_total_mf;
1863 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);*/
1864
1865
1866 g_message_text := 'l_estab_rec.a_10_grand_total -> '||l_estab_rec.a_10_grand_total;
1867 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
1868
1869
1873 buff => l_string);
1870 --
1871 fnd_file.put_line
1872 (which => fnd_file.output,
1874 hr_utility.set_location('Leaving..' || l_proc,100);
1875
1876 END write_establishment_record;
1877
1878
1879 PROCEDURE loop_through_establishments(p_hierarchy_version_id IN NUMBER,
1880 p_business_group_id IN NUMBER,
1881 p_start_date IN DATE,
1882 p_end_date IN DATE,
1883 p_report_mode IN VARCHAR2) IS
1884 l_hierarchy_node_id NUMBER;
1885 l_proc VARCHAR2(80) := g_package || 'loop_through_establishments';
1886
1887 CURSOR c_estab_details IS
1888 SELECT
1889 hlei1.lei_information2 unit_number_3,
1890 UPPER(hlei1.lei_information1) unit_name_4,
1891 UPPER(eloc.address_line_1||
1892 ' '||
1893 eloc.address_line_2) unit_address_req_5,
1894 UPPER(eloc.address_line_3) unit_address_6,
1895 UPPER(eloc.town_or_city) city_7,
1896 UPPER(eloc.region_2) state_8,
1897 SUBSTR(eloc.postal_code,1,5) zip_code_9,
1898 SUBSTR(eloc.postal_code,7,4) zip_code_last_4_10,
1899 DECODE(hlei1.lei_information9,'Y',1,2) reported_last_year_11,
1900 hlei2.lei_information6 ein_12,
1901 DECODE(hlei1.lei_information4,'Y',1,'N',2) gov_contract_15,
1902 hlei2.lei_information2 duns_16,
1903 UPPER(eloc.region_1) county_17,
1904 DECODE(hlei1.lei_information3,'Y',1,'N',2) apprentices_emp_19,
1905 hlei2.lei_information3 sic_20,
1906 hlei2.lei_information4 naics_21,
1907 pghn.hierarchy_node_id,
1908 hlei2.lei_information10 hq
1909 FROM per_gen_hierarchy_nodes pghn,
1910 hr_location_extra_info hlei1,
1911 hr_location_extra_info hlei2,
1912 hr_locations_all eloc
1913 WHERE -- pghn.hierarchy_version_id = 2683 -- sd1
1914 pghn.hierarchy_version_id = p_hierarchy_version_id -- 2803 sd10plus
1915 -- pghn.hierarchy_version_id = 2823 -- Vik SD Albuquereque
1916 AND pghn.node_type = 'EST'
1917 AND eloc.location_id = pghn.entity_id
1918 AND hlei1.location_id = pghn.entity_id
1919 AND hlei1.location_id = hlei2.location_id
1920 AND hlei1.information_type = 'EEO-1 Specific Information'
1921 AND hlei1.lei_information_category = 'EEO-1 Specific Information'
1922 AND hlei2.information_type = 'Establishment Information'
1923 AND hlei2.lei_information_category = 'Establishment Information';
1924 --order by eloc.region_2,decode(hlei2.lei_information10,'Y',1,2);
1925
1926 l_c_estab_details c_estab_details%ROWTYPE;
1927
1928 CURSOR c_estab_max IS -- find out IF over 50 people at location
1929 /* SELECT count('num_emps_at_location')
1930 FROM per_all_assignments_f paf
1931 WHERE paf.business_group_id = p_business_group_id
1932 AND paf.primary_flag = 'Y'
1933 AND paf.assignment_type = 'E'
1934 AND p_start_date >= paf.effective_start_date
1935 AND p_end_date <= paf.effective_end_date
1936 AND TO_CHAR(paf.location_id) IN
1937 (SELECT entity_id
1938 FROM per_gen_hierarchy_nodes pgn
1939 WHERE
1940 pgn.hierarchy_version_id = p_hierarchy_version_id
1941 AND (
1942 pgn.hierarchy_node_id = l_hierarchy_node_id
1943 OR pgn.parent_hierarchy_node_id = l_hierarchy_node_id)
1944 AND pgn.node_type IN ('EST','LOC')
1945 ); */
1946 -- The above query is replace with the following query for the bug# 6216140
1947 SELECT count(peo.person_id)
1948 FROM per_all_assignments_f ass,
1949 per_all_people_f peo,
1950 per_jobs_vl job
1951 WHERE peo.person_id = ass.person_id
1952 --AND peo.per_information1 is not NULL commented for fix for #12616269
1953 -- Added for fix for #12616269
1954 AND EXISTS (SELECT 'X'
1955 FROM per_people_extra_info pei
1956 WHERE pei.person_id = peo.person_id
1957 AND pei.information_type = 'US_ETHNIC_ORIGIN'
1958 AND (pei.pei_information1 = 'Y'
1959 OR pei.pei_information2 = 'Y'
1960 OR pei.pei_information3 = 'Y'
1961 OR pei.pei_information4 = 'Y'
1962 OR pei.pei_information5 = 'Y'
1963 OR pei.pei_information6 = 'Y'
1964 OR pei.pei_information7 = 'Y'
1965 )
1966 )
1967 -- Added for fix for #12616269
1968 AND job.job_information_category = 'US'
1969 AND p_start_date <= nvl(job.date_to,p_end_date )
1970 AND p_end_date >= job.date_from
1971 AND job.job_information1 is not NULL
1972 AND ass.job_id = job.job_id
1973 AND peo.effective_start_date = (select max(peo1.effective_start_date)
1974 from per_people_f peo1
1975 where p_start_date <= peo1.effective_end_date
1976 and p_end_date >= peo1.effective_start_date
1977 and peo.person_id = peo1.person_id
1978 and peo1.current_employee_flag = 'Y'
1979 )
1980 AND ass.effective_start_date = (select max(ass1.effective_start_date)
1981 from per_all_assignments_f ass1
1982 ,per_assignment_status_types ast --Added for bug##12616269
1983 where p_start_date <= ass1.effective_end_date
1984 and p_end_date >= ass1.effective_start_date
1985 and ass.person_id = ass1.person_id
1989 and ass1.assignment_status_type_id = ast.assignment_status_type_id
1986 and ass1.assignment_type = 'E'
1987 and ass1.primary_flag = 'Y'
1988 /*Added for bug##12616269 */
1990 and ast.per_system_status <> 'TERM_ASSIGN'
1991 )
1992 AND ass.assignment_type = 'E'
1993 AND ass.primary_flag = 'Y'
1994 AND ass.business_group_id = p_business_group_id
1995 AND peo.business_group_id = p_business_group_id
1996 AND job.business_group_id = p_business_group_id
1997 AND EXISTS (
1998 SELECT 'X'
1999 FROM HR_ORGANIZATION_INFORMATION HOI1,
2000 HR_ORGANIZATION_INFORMATION HOI2
2001 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
2002 AND hoi1.org_information_context = 'Reporting Statuses'
2003 AND hoi1.organization_id = p_business_group_id
2004 AND ass.employment_category = hoi2.org_information1
2005 AND hoi2.organization_id = p_business_group_id
2006 AND hoi2.org_information_context = 'Reporting Categories' )
2007 AND p_start_date <= ass.effective_end_date
2008 AND p_end_date >= ass.effective_start_date
2009 AND TO_CHAR(ass.location_id) IN
2010 (SELECT entity_id
2011 FROM per_gen_hierarchy_nodes pgn
2012 WHERE
2013 pgn.hierarchy_version_id = p_hierarchy_version_id
2014 AND (
2015 pgn.hierarchy_node_id = l_hierarchy_node_id
2016 OR pgn.parent_hierarchy_node_id = l_hierarchy_node_id)
2017 AND pgn.node_type IN ('EST','LOC')
2018 );
2019
2020
2021 l_estab_max NUMBER;
2022
2023 CURSOR c_female_details IS
2024 SELECT
2025 /* Added for fix of fix of #12616269 */
2026 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'3',1)) c_hlfemale,
2027 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'1',1)) c_wfemale,
2028 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'2',1)) c_bfemale,
2029 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'5',1)) c_hfemale,
2030 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'4',1)) c_afemale,
2031 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'6',1)) c_ifemale,
2032 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'13',1)) c_tmracesfemale,
2033 /* Added for fix of fix of #12616269 */
2034 /* Commented for fix of #12616269
2035 COUNT(DECODE(peo.per_information1,'3',1)) c_hlfemale,
2036 COUNT(DECODE(peo.per_information1,'1',1)) c_wfemale,
2037 COUNT(DECODE(peo.per_information1,'2',1)) c_bfemale,
2038 COUNT(DECODE(peo.per_information1,'5',1)) c_hfemale,
2039 COUNT(DECODE(peo.per_information1,'4',1)) c_amale,
2040 COUNT(DECODE(peo.per_information1,'6',1)) c_ifemale,
2041 COUNT(DECODE(peo.per_information1,'13',1)) c_tmracesfemale,
2042 Commented for fix of #12616269 */
2043 count(peo.person_id) "c_total_cat",
2044 hrl.lookup_code lookup_code
2045 FROM per_all_people_f peo,
2046 per_all_assignments_f ass,
2047 per_jobs_vl job,
2048 hr_lookups hrl,
2049 per_gen_hierarchy_nodes pgn_est
2050 WHERE peo.person_id = ass.person_id
2051 --AND peo.per_information1 is not NULL commented for fix for #12616269
2052 -- Added for fix for #12616269
2053 AND EXISTS (SELECT 'X'
2054 FROM per_people_extra_info pei
2055 WHERE pei.person_id = peo.person_id
2056 AND pei.information_type = 'US_ETHNIC_ORIGIN'
2057 AND (pei.pei_information1 = 'Y'
2058 OR pei.pei_information2 = 'Y'
2059 OR pei.pei_information3 = 'Y'
2060 OR pei.pei_information4 = 'Y'
2061 OR pei.pei_information5 = 'Y'
2062 OR pei.pei_information6 = 'Y'
2063 OR pei.pei_information7 = 'Y'
2064 )
2065 )
2066 -- Added for fix for #12616269
2067 AND peo.per_information_category = 'US'
2068 AND job.job_information_category = 'US'
2069 AND p_start_date <= nvl(job.date_to,p_start_date)
2070 AND p_end_date >= job.date_from
2071 AND job.job_information1 = hrl.lookup_code
2072 AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
2073 AND ass.job_id = job.job_id
2074 AND peo.effective_start_date =
2075 (SELECT MAX(peo1.effective_start_date)
2076 FROM per_people_f peo1
2077 WHERE p_start_date <= peo1.effective_end_date
2078 AND p_end_date >= peo1.effective_start_date
2079 AND peo.person_id = peo1.person_id
2080 AND peo1.current_employee_flag = 'Y'
2081 )
2082 AND ass.effective_start_date =
2083 (SELECT MAX(ass1.effective_start_date)
2084 FROM per_assignments_f ass1
2085 ,per_assignment_status_types ast --Added for bug##12616269
2086 WHERE p_start_date <= ass1.effective_end_date
2087 AND p_end_date >= ass1.effective_start_date
2088 AND ass.person_id = ass1.person_id
2089 AND ass1.assignment_type = 'E'
2093 AND ast.per_system_status <> 'TERM_ASSIGN'
2090 AND ass1.primary_flag = 'Y'
2091 /*Added for bug##12616269 */
2092 AND ass1.assignment_status_type_id = ast.assignment_status_type_id
2094 )
2095 AND ass.assignment_type = 'E'
2096 AND ass.primary_flag = 'Y'
2097 AND ass.business_group_id = p_business_group_id
2098 AND peo.business_group_id = p_business_group_id
2099 AND job.business_group_id = p_business_group_id
2100 AND EXISTS (
2101 SELECT 'X'
2102 FROM hr_organization_information hoi1,
2103 hr_organization_information hoi2
2104 WHERE TO_CHAR(ass.assignment_status_type_id) = hoi1.org_information1
2105 AND hoi1.org_information_context = 'Reporting Statuses'
2106 AND hoi1.organization_id = p_business_group_id
2107 AND ass.employment_category = hoi2.org_information1
2108 AND hoi2.organization_id = p_business_group_id
2109 AND hoi2.org_information_context = 'Reporting Categories'
2110 )
2111 AND ass.location_id = pgn_est.entity_id
2112 AND (pgn_est.hierarchy_node_id = l_hierarchy_node_id
2113 OR pgn_est.parent_hierarchy_node_id = l_hierarchy_node_id)
2114 AND pgn_est.node_type IN ('EST','LOC')
2115 AND pgn_est.hierarchy_version_id = p_hierarchy_version_id
2116 AND pgn_est.business_group_id = p_business_group_id
2117 AND peo.sex = 'F'
2118 AND 1 > (SELECT count(*)
2119 FROM per_gen_hierarchy_nodes pgn_loc
2120 WHERE pgn_est.entity_id = pgn_loc.entity_id
2121 AND pgn_loc.node_type = 'LOC'
2122 AND pgn_loc.parent_hierarchy_node_id = pgn_est.hierarchy_node_id
2123 AND pgn_loc.business_group_id = p_business_group_id)
2124 GROUP BY hrl.lookup_code;
2125
2126 l_c_female_details c_female_details%ROWTYPE;
2127
2128 CURSOR c_male_details IS
2129 SELECT
2130 /* Added for fix of fix of #12616269 */
2131 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'3',1)) c_hlmale,
2132 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'1',1)) c_wmale,
2133 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'2',1)) c_bmale,
2134 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'5',1)) c_hmale,
2135 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'4',1)) c_amale,
2136 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'6',1)) c_imale,
2137 COUNT(DECODE(per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id),'13',1)) c_tmracesmale,
2138 /* Added for fix of fix of #12616269 */
2139 /* Commented for fix of #12616269
2140 COUNT(DECODE(peo.per_information1,'3',1)) c_hlmale,
2141 COUNT(DECODE(peo.per_information1,'1',1)) c_wmale,
2142 COUNT(DECODE(peo.per_information1,'2',1)) c_bmale,
2143 COUNT(DECODE(peo.per_information1,'5',1)) c_hmale,
2144 COUNT(DECODE(peo.per_information1,'4',1)) c_amale,
2145 COUNT(DECODE(peo.per_information1,'6',1)) c_imale,
2146 COUNT(DECODE(peo.per_information1,'13',1)) c_tmracesmale,
2147 Commented for fix of #12616269 */
2148 hrl.lookup_code lookup_code
2149 FROM per_all_people_f peo,
2150 per_all_assignments_f ass,
2151 per_jobs_vl job,
2152 hr_lookups hrl,
2153 per_gen_hierarchy_nodes pgn_est
2154 WHERE peo.person_id = ass.person_id
2155 --AND peo.per_information1 is not NULL commented for fix for #12616269
2156 -- Added for fix for #12616269
2157 AND EXISTS (SELECT 'X'
2158 FROM per_people_extra_info pei
2159 WHERE pei.person_id = peo.person_id
2160 AND pei.information_type = 'US_ETHNIC_ORIGIN'
2161 AND (pei.pei_information1 = 'Y'
2162 OR pei.pei_information2 = 'Y'
2163 OR pei.pei_information3 = 'Y'
2164 OR pei.pei_information4 = 'Y'
2165 OR pei.pei_information5 = 'Y'
2166 OR pei.pei_information6 = 'Y'
2167 OR pei.pei_information7 = 'Y'
2168 )
2169 )
2170 -- Added for fix for #12616269
2171 AND peo.per_information_category = 'US'
2172 AND job.job_information_category = 'US'
2173 AND p_start_date <= NVL(job.date_to,p_start_date)
2174 AND p_end_date >= job.date_from
2175 AND job.job_information1 = hrl.lookup_code
2176 AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
2177 AND ass.job_id = job.job_id
2178 AND peo.effective_start_date =
2179 (SELECT MAX(peo1.effective_start_date)
2180 FROM per_people_f peo1
2181 WHERE p_start_date <= peo1.effective_end_date
2182 AND p_end_date >= peo1.effective_start_date
2183 AND peo.person_id = peo1.person_id
2184 AND peo1.current_employee_flag = 'Y'
2185 )
2186 AND ass.effective_start_date =
2187 (SELECT MAX(ass1.effective_start_date)
2188 FROM per_assignments_f ass1
2189 ,per_assignment_status_types ast --Added for bug##12616269
2190 WHERE p_start_date <= ass1.effective_end_date
2191 AND p_end_date >= ass1.effective_start_date
2195 /*Added for bug##12616269 */
2192 AND ass.person_id = ass1.person_id
2193 AND ass1.assignment_type = 'E'
2194 AND ass1.primary_flag = 'Y'
2196 AND ass1.assignment_status_type_id = ast.assignment_status_type_id
2197 AND ast.per_system_status <> 'TERM_ASSIGN'
2198 )
2199 AND ass.assignment_type = 'E'
2200 AND ass.primary_flag = 'Y'
2201 AND ass.business_group_id = P_BUSINESS_GROUP_ID
2202 AND peo.business_group_id = P_BUSINESS_GROUP_ID
2203 AND job.business_group_id = P_BUSINESS_GROUP_ID
2204 AND EXISTS (
2205 SELECT 'X'
2209 AND hoi1.org_information_context = 'Reporting Statuses'
2206 FROM HR_ORGANIZATION_INFORMATION HOI1,
2207 HR_ORGANIZATION_INFORMATION HOI2
2208 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
2210 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
2211 AND ass.employment_category = hoi2.org_information1
2212 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
2213 AND hoi2.org_information_context = 'Reporting Categories'
2214 )
2215 AND ass.location_id = pgn_est.entity_id
2216 AND (pgn_est.hierarchy_node_id = l_hierarchy_node_id
2217 OR pgn_est.parent_hierarchy_node_id = l_hierarchy_node_id)
2218 AND pgn_est.node_type IN ('EST','LOC')
2219 AND pgn_est.hierarchy_version_id = p_hierarchy_version_id
2220 AND pgn_est.business_group_id = p_business_group_id
2221 AND peo.sex = 'M'
2222 AND 1 > (SELECT count(*)
2223 FROM per_gen_hierarchy_nodes pgn_loc
2224 WHERE pgn_est.entity_id = pgn_loc.entity_id
2225 AND pgn_loc.node_type = 'LOC'
2226 AND pgn_loc.parent_hierarchy_node_id = pgn_est.hierarchy_node_id
2227 AND pgn_loc.business_group_id = p_business_group_id)
2228 GROUP BY hrl.lookup_code;
2229
2230 l_c_male_details c_male_details%ROWTYPE;
2231
2232 CURSOR c_mf_details IS
2233 -- Updated CURSOR BUG4583250
2234 SELECT
2235 count('all_birds_and_blokes_in_job') c_mf,
2236 hrl.lookup_code lookup_code
2237 FROM per_all_people_f peo,
2238 per_all_assignments_f ass,
2239 per_jobs_vl job,
2240 hr_lookups hrl,
2241 per_gen_hierarchy_nodes pgn_est
2242 WHERE peo.person_id = ass.person_id
2243 -- Modified to add function call per_us_hr_utility_pkg.derive_alien_ethnic_origin instead of peo.per_information1
2244 -- AND peo.per_information1 IN ('1','2','3','4','5','6','13') --BUG4410003
2245 AND per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id) IN ('1','2','3','4','5','6','13')
2249 AND p_end_date >= job.date_from
2246 AND peo.per_information_category = 'US'
2247 AND job.job_information_category = 'US'
2248 AND p_start_date <= nvl(job.date_to,p_start_date)
2250 AND job.job_information1 = hrl.lookup_code
2251 AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
2252 AND ass.job_id = job.job_id
2253 AND peo.effective_start_date =
2254 (SELECT MAX(peo1.effective_start_date)
2255 FROM per_people_f peo1
2256 WHERE p_start_date <= peo1.effective_end_date
2257 AND p_end_date >= peo1.effective_start_date
2258 AND peo.person_id = peo1.person_id
2259 AND peo1.current_employee_flag = 'Y'
2260 )
2261 AND ass.effective_start_date =
2262 (SELECT MAX(ass1.effective_start_date)
2263 FROM per_assignments_f ass1
2264 ,per_assignment_status_types ast --Added for bug##12616269
2265 WHERE p_start_date <= ass1.effective_end_date
2266 AND p_end_date >= ass1.effective_start_date
2267 AND ass.person_id = ass1.person_id
2268 AND ass1.assignment_type = 'E'
2269 AND ass1.primary_flag = 'Y'
2270 /*Added for bug##12616269 */
2271 AND ass1.assignment_status_type_id = ast.assignment_status_type_id
2272 AND ast.per_system_status <> 'TERM_ASSIGN'
2273 )
2274 AND ass.assignment_type = 'E'
2275 AND ass.primary_flag = 'Y'
2276 AND ass.business_group_id = P_BUSINESS_GROUP_ID
2277 AND peo.business_group_id = P_BUSINESS_GROUP_ID
2278 AND job.business_group_id = P_BUSINESS_GROUP_ID
2279 AND EXISTS (
2280 SELECT 'X'
2281 FROM HR_ORGANIZATION_INFORMATION HOI1,
2282 HR_ORGANIZATION_INFORMATION HOI2
2283 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
2284 AND hoi1.org_information_context = 'Reporting Statuses'
2285 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
2286 AND ass.employment_category = hoi2.org_information1
2287 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
2288 AND hoi2.org_information_context = 'Reporting Categories' )
2289 AND ass.location_id = pgn_est.entity_id
2290 AND (pgn_est.hierarchy_node_id = l_hierarchy_node_id
2291 OR pgn_est.parent_hierarchy_node_id = l_hierarchy_node_id)
2292 AND pgn_est.node_type IN ('EST','LOC')
2293 AND pgn_est.hierarchy_version_id = p_hierarchy_version_id
2294 AND pgn_est.business_group_id = p_business_group_id
2295 AND 1 > (SELECT count(*)
2296 FROM per_gen_hierarchy_nodes pgn_loc
2297 WHERE pgn_est.entity_id = pgn_loc.entity_id
2298 AND pgn_loc.node_type = 'LOC'
2299 AND pgn_loc.parent_hierarchy_node_id = pgn_est.hierarchy_node_id
2300 AND pgn_loc.business_group_id = p_business_group_id)
2301 -- Added for fix for #12616269 start
2302 AND EXISTS (SELECT 'X'
2303 FROM per_people_extra_info pei
2304 WHERE pei.person_id = peo.person_id
2305 AND pei.information_type = 'US_ETHNIC_ORIGIN'
2306 AND (pei.pei_information1 = 'Y'
2307 OR pei.pei_information2 = 'Y'
2308 OR pei.pei_information3 = 'Y'
2309 OR pei.pei_information4 = 'Y'
2310 OR pei.pei_information5 = 'Y'
2311 OR pei.pei_information6 = 'Y'
2312 OR pei.pei_information7 = 'Y'
2313 )
2314 )
2315 -- Added for fix for #12616269 end
2316 GROUP BY hrl.lookup_code;
2317
2318 l_start_date DATE := p_start_date;
2319 l_c_mf_details c_mf_details%ROWTYPE;
2320
2321 CURSOR c_lastyears_details IS
2322 SELECT
2323 lei_information14 p_hlmale
2324 ,lei_information15 p_hlfemale
2325 ,lei_information4 p_wmale
2326 ,lei_information5 p_bmale
2327 ,lei_information6 p_hmale
2328 ,lei_information7 p_amale
2329 ,lei_information8 p_imale
2330 ,lei_information16 p_tmracesmale
2331 ,lei_information9 p_wfemale
2332 ,lei_information10 p_bfemale
2333 ,lei_information11 p_hfemale
2334 ,lei_information12 p_afemale
2335 ,lei_information13 p_ifemale
2336 ,lei_information17 p_tmracesfemale
2340 ,per_gen_hierarchy_nodes pgn
2337 ,lei_information3 p_total
2338
2339 FROM hr_location_extra_info lei
2341 WHERE lei.lei_information1 = l_prev_year_filed
2342 AND lei.information_type = 'EEO-1 Archive Information'
2343 -- BUG3646445
2344 AND lei.location_id = pgn.entity_id
2345 AND pgn.hierarchy_node_id = l_hierarchy_node_id
2346 AND pgn.hierarchy_version_id = p_hierarchy_version_id;
2347 -- End of BUG3646445
2348
2349 l_c_lastyears_details c_lastyears_details%ROWTYPE;
2350
2351 PROCEDURE insert_location_eit(p_hierarchy_node_id IN NUMBER,
2352 p_hierarchy_version_id IN NUMBER,
2353 p_report_year IN VARCHAR2) IS
2354
2355 p_update VARCHAR2(1) := 'C';
2356 l_location_id VARCHAR2(40);
2357 l_location_code VARCHAR2(100);
2358 l_location_extra_info_id NUMBER := NULL;
2359 l_object_version_number NUMBER := NULL;
2360
2361 l_eit_count NUMBER := 0;
2362 l_min_year VARCHAR2(4) := NULL;
2363
2364 BEGIN --insert_location_eit
2365
2369 SELECT eloc.location_id,
2366 fnd_file.put_line(which => fnd_file.log,buff =>'insert INTO location eit ');
2367
2368 BEGIN --Local1
2370 eloc.location_code
2371 INTO l_location_id,
2372 l_location_code
2373 FROM per_gen_hierarchy_nodes pgn,
2374 hr_locations_all eloc
2375 WHERE (hierarchy_node_id = p_hierarchy_node_id
2376 or parent_hierarchy_node_id = p_hierarchy_node_id)
2377 AND hierarchy_version_id = p_hierarchy_version_id
2378 AND pgn.node_type = 'EST'
2379 AND eloc.location_id = pgn.entity_id;
2380 END; --Local1
2381 fnd_file.put_line
2382 (which => fnd_file.log,
2383 buff => 'location code IS '||l_location_code);
2384 --
2385 BEGIN --Local2
2386 SELECT 'U',
2387 location_extra_info_id
2388 INTO p_update,
2389 l_location_extra_info_id
2390 FROM hr_location_extra_info
2391 WHERE lei_information1 = p_report_year
2392 AND lei_information_category = 'EEO-1 Archive Information'
2393 AND location_id = l_location_id;
2394 EXCEPTION
2395 WHEN no_data_found THEN
2396 p_update := 'C';
2397 fnd_file.put_line
2398 (which => fnd_file.log,
2399 buff => ' ');
2400 fnd_file.put_line
2401 (which => fnd_file.log,
2402 buff => 'Need to create new eit for location '
2403 ||l_location_id ||' '|| l_location_code);
2404
2405 WHEN OTHERS THEN
2406 NULL;
2407 END;--Local2
2408
2409 IF p_update = 'U' THEN
2410 fnd_file.put_line
2411 (which => fnd_file.log,
2412 buff => ' ');
2413 fnd_file.put_line
2414 (which => fnd_file.log,
2415 buff => 'p_update '||p_update||' location_id to update IS '
2416 ||l_location_id ||' '|| l_location_code);
2417
2418 BEGIN--Local3
2419 SELECT object_version_number
2420 INTO l_object_version_number
2421 FROM hr_location_extra_info
2422 WHERE location_extra_info_id = l_location_extra_info_id;
2423 END;--Local3
2424
2425 BEGIN --Local4
2426 hr_location_extra_info_api.delete_location_extra_info
2427 (p_validate => false -- true
2428 ,p_location_extra_info_id => l_location_extra_info_id
2429 ,p_object_version_number => l_object_version_number
2430 );
2431 END;--Local4
2432 COMMIT;
2433
2434 p_update := 'C';
2435
2436 END IF; --p_update = 'U'
2437
2438 IF p_update = 'C' THEN
2439 fnd_file.put_line
2440 (which => fnd_file.log,
2441 buff => 'p_update '||p_update||' location_id '||l_location_id);
2442 fnd_file.put_line
2443 (which => fnd_file.log,
2444 buff => 'need to create new eit');
2445
2446 BEGIN--Local5
2447 -- Bug 7447266
2448 /*
2449 hr_location_extra_info_api.create_location_extra_info
2450 (p_validate => false -- true
2451 ,p_location_id => l_location_id
2452 ,p_information_type => 'EEO-1 Archive Information'
2453 ,p_lei_information_category => 'EEO-1 Archive Information'
2454 ,p_lei_information1 => p_report_year
2455 ,p_lei_information2 => 'DATE report run '||sysdate -- l_conc_request_id
2456 ,p_lei_information3 => l_estab_rec.c_10_grand_total -- grand tot mf
2457 ,p_lei_information4 => l_estab_rec.d_10_grand_total -- white male
2458 ,p_lei_information5 => l_estab_rec.e_10_grand_total -- black male
2459 ,p_lei_information6 => l_estab_rec.f_10_grand_total -- hispanic males
2460 ,p_lei_information7 => l_estab_rec.g_10_grand_total -- asian pac isle males
2461 ,p_lei_information8 => l_estab_rec.h_10_grand_total -- american native males
2462 ,p_lei_information9 => l_estab_rec.j_10_grand_total -- white females
2463 ,p_lei_information10 => l_estab_rec.k_10_grand_total -- black females
2464 ,p_lei_information11 => l_estab_rec.l_10_grand_total -- hispanic females
2465 ,p_lei_information12 => l_estab_rec.m_10_grand_total -- asian pac isle females
2466 ,p_lei_information13 => l_estab_rec.o_10_grand_total -- american native females
2467 -- Bug# 5259440
2468 ,p_lei_information14 => l_estab_rec.a_10_grand_total -- male hispanic or latino
2469 ,p_lei_information15 => l_estab_rec.b_10_grand_total -- female hispanic or latino
2470 ,p_lei_information16 => l_estab_rec.i_10_grand_total -- male two or more races
2471 ,p_lei_information17 => l_estab_rec.n_10_grand_total -- female two or more races
2472 ,p_location_extra_info_id => l_location_extra_info_id
2473 ,p_object_version_number => l_object_version_number
2474 );
2475 */
2476
2477 hr_location_extra_info_api.create_location_extra_info
2478 (p_validate => false -- true
2479 ,p_location_id => l_location_id
2480 ,p_information_type => 'EEO-1 Archive Information'
2484 ,p_lei_information3 => l_estab_rec.o_10_grand_total -- grand tot mf
2481 ,p_lei_information_category => 'EEO-1 Archive Information'
2482 ,p_lei_information1 => p_report_year
2483 ,p_lei_information2 => 'DATE report run '||sysdate -- l_conc_request_id
2485 ,p_lei_information4 => l_estab_rec.c_10_grand_total -- white male
2486 ,p_lei_information5 => l_estab_rec.d_10_grand_total -- black male
2487 ,p_lei_information6 => l_estab_rec.e_10_grand_total -- hispanic males
2488 ,p_lei_information7 => l_estab_rec.f_10_grand_total -- asian pac isle males
2489 ,p_lei_information8 => l_estab_rec.g_10_grand_total -- american native males
2490 ,p_lei_information9 => l_estab_rec.i_10_grand_total -- white females
2491 ,p_lei_information10 => l_estab_rec.j_10_grand_total -- black females
2492 ,p_lei_information11 => l_estab_rec.k_10_grand_total -- hispanic females
2493 ,p_lei_information12 => l_estab_rec.l_10_grand_total -- asian pac isle females
2494 ,p_lei_information13 => l_estab_rec.m_10_grand_total -- american native females
2495 -- Bug# 5259440
2496 ,p_lei_information14 => l_estab_rec.a_10_grand_total -- male hispanic or latino
2497 ,p_lei_information15 => l_estab_rec.b_10_grand_total -- female hispanic or latino
2498 ,p_lei_information16 => l_estab_rec.h_10_grand_total -- male two or more races
2499 ,p_lei_information17 => l_estab_rec.n_10_grand_total -- female two or more races
2500 ,p_location_extra_info_id => l_location_extra_info_id
2501 ,p_object_version_number => l_object_version_number
2502 );
2503
2504 END;--Local5
2505 COMMIT;
2506 fnd_file.put_line
2507 (which => fnd_file.log,
2508 buff => ' ');
2509 fnd_file.put_line
2510 (which => fnd_file.log,
2511 buff => 'eit created for location_id '||l_location_id ||' year '
2512 ||p_report_year);
2513 /*fnd_file.put_line
2514 (which => fnd_file.log,
2515 buff => 'grand total IS '||l_estab_rec.a_11_last_year_grand_total); */
2516 fnd_file.put_line
2517 (which => fnd_file.log,
2518 buff => ' ');
2519 END IF; -- IF p_update = 'C'
2520 --
2521 -- IF over 4 eits delete the earliest
2522 --
2523 BEGIN--Local6
2524
2525 BEGIN--Local7
2526
2527 SELECT count(*)
2528 INTO l_eit_count
2529 FROM hr_location_extra_info lei
2530 WHERE location_id = l_location_id
2531 AND information_type = 'EEO-1 Archive Information';
2532 EXCEPTION
2533 WHEN no_data_found THEN
2534 NULL;
2535 WHEN OTHERS THEN
2536 NULL;
2537 END;--Local7
2538
2539
2540 IF l_eit_count > 4 THEN
2541 BEGIN--Local8
2542 SELECT min(lei_information1)
2543 INTO l_min_year
2544 FROM hr_location_extra_info lei
2545 WHERE location_id = l_location_id
2546 AND information_type = 'EEO-1 Archive Information';
2547 END;--Local8
2548 BEGIN--Local9
2549 SELECT location_extra_info_id, object_version_number
2550 INTO l_location_extra_info_id,l_object_version_number
2551 FROM hr_location_extra_info lei
2552 WHERE lei_information1 = l_min_year
2553 AND information_type = 'EEO-1 Archive Information'
2554 AND location_id = l_location_id;
2555 END;--Local9
2556 BEGIN--Local10
2557 hr_location_extra_info_api.delete_location_extra_info
2558 (p_validate => false -- true
2559 ,p_location_extra_info_id => l_location_extra_info_id
2560 ,p_object_version_number => l_object_version_number
2561 );
2562 END;--Local10
2563 fnd_file.put_line
2564 (which => fnd_file.log,
2565 buff => '* there are over 4 Archive EITs for location id '
2566 ||l_location_id||' so deleting for year '||l_min_year);
2567 END IF;--l_eit_count > 4
2568
2569 END;--Local6
2570
2571 END insert_location_eit;
2572 --
2573 PROCEDURE insert_org_eit(p_hierarchy_node_id IN NUMBER,
2574 p_hierarchy_version_id IN NUMBER,
2575 p_business_group_id IN NUMBER,
2576 p_report_year IN VARCHAR2) IS
2577
2578 p_update VARCHAR2(1) := 'C';
2579 l_effective_date DATE := sysdate;
2580 l_org_information_id NUMBER := NULL;
2581 l_object_version_number NUMBER := NULL;
2582
2583 l_organization_id NUMBER(15,0);
2584 l_location_code VARCHAR2(100);
2585 l_location_extra_info_id NUMBER := NULL;
2586
2587 l_eit_count NUMBER := 0;
2588 l_min_year VARCHAR2(4) := NULL;
2589 l_proc VARCHAR2(40) := g_package || 'insert_org_eit';
2590
2591 BEGIN--insert_org_eit
2592
2593 BEGIN--Local1
2594 l_organization_id := l_org_rec.par_ent_org_id;
2595 SELECT 'U', org_information_id
2596 INTO p_update, l_org_information_id
2597 FROM hr_organization_information
2598 WHERE org_information1 = p_report_year
2599 AND ORG_INFORMATION_CONTEXT = 'EEO_Archive'
2600 AND organization_id = l_org_rec.par_ent_org_id;
2601
2602 EXCEPTION
2603 WHEN NO_DATA_FOUND THEN
2604 p_update := 'C';
2608 (which => fnd_file.log,
2605 l_organization_id := l_org_rec.par_ent_org_id;
2606 --
2607 fnd_file.put_line
2609 buff => ' ');
2610 fnd_file.put_line
2611 (which => fnd_file.log,
2612 buff => 'need to create new eit for '||l_organization_id);
2613 fnd_file.put_line
2614 (which => fnd_file.log,
2615 buff => ' ');
2616 fnd_file.put_line
2617 (which => fnd_file.log,
2618 buff => 'IMPORTANT. IF YOU FILED LAST YEAR BUT LAST YEARS DATES');
2619 fnd_file.put_line
2620 (which => fnd_file.log,
2621 buff => 'ARE NOT BEING PRINTED, PLEASE EITHER MANUALLY ENTER DATA');
2622 fnd_file.put_line
2623 (which => fnd_file.log,
2624 buff => 'IN THE ORGANIZATION EIT, OR RE-RUN THIS REPORT IN FINAL ');
2625 fnd_file.put_line
2626 (which => fnd_file.log,
2627 buff => 'MODE FOR LAST YEARS DATES. ');
2628 fnd_file.put_line
2629 (which => fnd_file.log,
2630 buff => ' ');
2631 WHEN OTHERS THEN
2632 NULL;
2633 END;--Local1
2634
2635 IF p_update = 'U' THEN
2636 fnd_file.put_line
2637 (which => fnd_file.log,
2638 buff => 'p_update '||p_update||' org_id to update IS '
2639 ||l_organization_id||' IN bus grp '
2640 ||p_business_group_id);
2641
2642 BEGIN --Local2
2643 SELECT object_version_number
2644 INTO l_object_version_number
2645 FROM hr_organization_information
2646 WHERE org_information_id = l_org_information_id;
2647 END;--Local2
2648
2649 --
2650 -- (delete AND turn marker to 'C') ...
2651 --
2652 BEGIN--Local3
2653 hr_organization_api.delete_org_manager
2654 (p_validate => false -- true
2655 ,p_org_information_id => l_org_information_id
2656 ,p_object_version_number => l_object_version_number
2657 );
2658 END;--Local3
2659 COMMIT;
2660 --
2661 p_update := 'C';
2662 --
2663 END IF; --p_update = 'U'
2664
2665 IF p_update = 'C' THEN
2666 fnd_file.put_line
2667 (which => fnd_file.log,
2668 buff => ' ');
2669 fnd_file.put_line
2670 (which => fnd_file.log,
2671 buff => 'p_update '||p_update||' org id '||l_organization_id);
2672 fnd_file.put_line
2673 (which => fnd_file.log,
2674 buff => 'need to create new eit');
2675
2676 BEGIN --Local4
2677 /* hr_organization_api.create_org_information
2678 (p_validate => false
2679 ,p_effective_date => l_effective_date
2680 ,p_organization_id => l_organization_id
2681 ,p_org_info_type_code => 'EEO_Archive'
2682 ,p_org_information1 => p_report_year
2683 ,p_org_information2 => 'DATE report run '||sysdate
2684 ,p_org_information3 => l_estab_rec.c_10_grand_total -- grand tot mf
2685 ,p_org_information4 => l_estab_rec.d_10_grand_total -- white male
2686 ,p_org_information5 => l_estab_rec.e_10_grand_total -- black male
2687 ,p_org_information6 => l_estab_rec.f_10_grand_total -- hispanic males
2688 ,p_org_information7 => l_estab_rec.g_10_grand_total -- asian pac isle males
2689 ,p_org_information8 => l_estab_rec.h_10_grand_total -- american native males
2690 ,p_org_information9 => l_estab_rec.j_10_grand_total -- white females
2691 ,p_org_information10 => l_estab_rec.k_10_grand_total -- black females
2692 ,p_org_information11 => l_estab_rec.l_10_grand_total -- hispanic females
2693 ,p_org_information12 => l_estab_rec.m_10_grand_total -- asian pac isle females
2694 ,p_org_information13 => l_estab_rec.o_10_grand_total -- american native females
2695
2696 ,p_org_information14 => l_estab_rec.a_10_grand_total -- male hispanic or latino
2697 ,p_org_information15 => l_estab_rec.b_10_grand_total -- female hispanic or latino
2698 ,p_org_information16 => l_estab_rec.i_10_grand_total -- male two or more races
2699 ,p_org_information17 => l_estab_rec.n_10_grand_total -- female two or more races
2700 ,p_org_information_id => l_org_information_id
2701 ,p_object_version_number => l_object_version_number
2702 ); */
2703 hr_organization_api.create_org_information
2704 (p_validate => false
2705 ,p_effective_date => l_effective_date
2706 ,p_organization_id => l_organization_id
2707 ,p_org_info_type_code => 'EEO_Archive'
2708 ,p_org_information1 => p_report_year
2709 ,p_org_information2 => 'DATE report run '||sysdate
2710 ,p_org_information3 => l_estab_rec.o_10_grand_total -- grand tot mf
2711 ,p_org_information4 => l_estab_rec.c_10_grand_total -- white male
2712 ,p_org_information5 => l_estab_rec.d_10_grand_total -- black male
2713 ,p_org_information6 => l_estab_rec.e_10_grand_total -- hispanic males
2714 ,p_org_information7 => l_estab_rec.f_10_grand_total -- asian pac isle males
2715 ,p_org_information8 => l_estab_rec.g_10_grand_total -- american native males
2719 ,p_org_information12 => l_estab_rec.l_10_grand_total -- asian pac isle females
2716 ,p_org_information9 => l_estab_rec.i_10_grand_total -- white females
2717 ,p_org_information10 => l_estab_rec.j_10_grand_total -- black females
2718 ,p_org_information11 => l_estab_rec.k_10_grand_total -- hispanic females
2720 ,p_org_information13 => l_estab_rec.m_10_grand_total -- american native females
2721 ,p_org_information14 => l_estab_rec.a_10_grand_total -- male hispanic or latino
2722 ,p_org_information15 => l_estab_rec.b_10_grand_total -- female hispanic or latino
2723 ,p_org_information16 => l_estab_rec.h_10_grand_total -- male two or more races
2724 ,p_org_information17 => l_estab_rec.n_10_grand_total -- female two or more races
2725 ,p_org_information_id => l_org_information_id
2726 ,p_object_version_number => l_object_version_number
2727 );
2728
2729 END;--Local4
2730 COMMIT;
2731
2732 fnd_file.put_line
2733 (which => fnd_file.log,
2734 buff => ' ');
2735 fnd_file.put_line
2736 (which => fnd_file.log,
2737 buff => 'eit created for org id '||l_organization_id
2738 ||' year '||p_report_year);
2739 END IF; --p_update = 'C'
2740
2741 --
2742 -- IF over 4 eits delete the earliest
2743 --
2744 BEGIN--Local5
2745
2746 BEGIN--Local6
2747
2748 SELECT count(*)
2749 INTO l_eit_count
2750 FROM hr_organization_information
2751 WHERE organization_id = p_business_group_id
2752 AND org_information_context = 'EEO_Archive';
2753 EXCEPTION
2754 WHEN NO_DATA_FOUND THEN
2755 NULL;
2756 WHEN OTHERS THEN
2757 NULL;
2758 END;--Local6
2759
2760 IF l_eit_count > 4 THEN
2761 BEGIN--Local7
2762 SELECT MIN(org_information1)
2763 INTO l_min_year
2764 FROM hr_organization_information
2765 WHERE organization_id = p_business_group_id
2766 AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
2767 END;--Local7
2768
2769 BEGIN--Local8
2770 SELECT org_information_id,
2771 object_version_number
2772 INTO l_org_information_id
2773 ,l_object_version_number
2774 FROM hr_organization_information
2775 WHERE org_information1 = l_min_year
2776 AND org_information_context = 'EEO_Archive'
2777 AND organization_id = p_business_group_id;
2778 END;--Local8
2779 BEGIN--Local9
2780 hr_organization_api.delete_org_manager
2781 (p_validate => false -- true
2782 ,p_org_information_id => l_org_information_id
2783 ,p_object_version_number => l_object_version_number
2784 );
2785 END;--Local9
2786 fnd_file.put_line
2787 (which => fnd_file.log,
2788 buff => ' there are over 4 Archive EITs for organization id '
2789 ||p_business_group_id||' so deleting for year '||l_min_year);
2790 END IF;--l_eit_count > 4
2791 END; --Local5
2792 END insert_org_eit;
2793
2794
2795 BEGIN --loop_through_establishments
2796
2797 OPEN c_estab_details;
2798 LOOP
2799 FETCH c_estab_details INTO l_c_estab_details;
2800 EXIT WHEN c_estab_details%NOTFOUND;
2801
2802 BEGIN -- unit name AND address are required.
2803 IF (l_c_estab_details.unit_name_4 IS NULL OR
2804 l_c_estab_details.unit_address_req_5 IS NULL)
2805 THEN
2806 fnd_file.put_line
2807 (which => fnd_file.log,
2808 buff => '======================================================');
2809 fnd_file.put_line
2810 (which => fnd_file.log,
2811 buff => ' ');
2812 fnd_file.put_line
2813 (which => fnd_file.log,
2814 buff => 'Unit name AND address are '
2815 ||'required fields - ');
2816 fnd_file.put_line
2817 (which => fnd_file.log,
2818 buff => 'Please enter ''Reporting Name'' IN location/extra '
2819 ||'info/eeo1 specific information for unit/establishment '
2820 ||l_c_estab_details.unit_name_4||' '
2821 ||l_c_estab_details.unit_address_req_5);
2822 fnd_file.put_line
2823 (which => fnd_file.log,
2824 buff => ' ');
2825 fnd_file.put_line
2826 (which => fnd_file.log,
2827 buff => 'Nav path = location/extra info/EEO1 Specific Data');
2828 fnd_file.put_line
2829 (which => fnd_file.log,
2830 buff => ' ');
2831 fnd_file.put_line
2832 (which => fnd_file.log,
2833 buff => '======================================================');
2834 RAISE hr_utility.hr_error;
2835 ELSIF
2836 (l_c_estab_details.city_7 IS NULL OR
2837 l_c_estab_details.state_8 IS NULL OR
2838 l_c_estab_details.zip_code_9 IS NULL OR
2839 l_c_estab_details.county_17 IS NULL)
2840 THEN
2841 fnd_file.put_line
2842 (which => fnd_file.log,
2843 buff => '==================================');
2844 fnd_file.put_line
2848 (which => fnd_file.log,
2845 (which => fnd_file.log,
2846 buff => ' ');
2847 fnd_file.put_line
2849 buff => 'The Location/Establishment City, State, County AND '
2850 ||'Zip Code are required fields ');
2851 fnd_file.put_line
2852 (which => fnd_file.log,
2853 buff => ' ');
2854 fnd_file.put_line
2855 (which => fnd_file.log,
2856 buff => 'Please enter IN location form for unit '
2857 ||l_c_estab_details.unit_name_4);
2858 fnd_file.put_line
2859 (which => fnd_file.log,
2860 buff => '==================================');
2861 RAISE hr_utility.hr_error;
2862 END IF;
2863 END; -- unit name AND address are required.
2864
2865
2866 --unit name must be a minimum of two(2) positions
2867
2868 IF (length(trim(l_c_estab_details.unit_name_4)) <2)
2869 THEN
2870 fnd_file.put_line
2871 (which => fnd_file.log,
2872 buff => 'The company/establishment names must be a minimum of two (2) positions.');
2873
2874 fnd_file.put_line
2875 (which => fnd_file.log,
2876 buff => 'The Unit name '||l_c_estab_details.unit_name_4||' is less than two (2) positions.');
2877
2878 fnd_file.put_line
2879 (which => fnd_file.log,
2880 buff => 'Please change the "Reporting Name " IN location/extra '
2881 ||'info/eeo1 specific information for unit/establishment ');
2882
2883 RAISE hr_utility.hr_error;
2884 END IF;
2885
2886 -- do not let unit name start with 'The'
2887
2888 IF UPPER(SUBSTR(l_c_estab_details.unit_name_4,1,3)) = UPPER('THE')
2889 THEN
2890 l_c_estab_details.unit_name_4 :=
2891 /* ltrim(l_c_estab_details.unit_name_4,'THEthe'); commented for bug 13509871 */
2892 trim(substr(l_c_estab_details.unit_name_4,4)); --added for bug 13509871
2893
2894 l_c_estab_details.unit_name_4 :=
2895 (l_c_estab_details.unit_name_4||' THE');
2896 END IF;
2897
2898 --unit_address_req_5 must be a minimum of four(4) postions
2899
2900 IF (length(trim(l_c_estab_details.unit_address_req_5)) <4)
2901 THEN
2902 fnd_file.put_line
2903 (which => fnd_file.log,
2904 buff => 'The unit address of '||l_c_estab_details.unit_name_4||' must be a minimum of four (4) positions.');
2905 RAISE hr_utility.hr_error;
2906 END IF;
2907
2908 --unit_address_req_5 must begin with a number or 'PO BOX'
2909
2910 IF NOT((UPPER(SUBSTR(l_c_estab_details.unit_address_req_5,1,6))='PO BOX')
2911 OR (ASCII(SUBSTR(l_c_estab_details.unit_address_req_5,1,1))>=48
2912 AND ASCII(SUBSTR(l_c_estab_details.unit_address_req_5,1,1))<=57))
2913 THEN
2914 fnd_file.put_line
2915 (which => fnd_file.log,
2916 buff => 'The street address of '||l_c_estab_details.unit_name_4||' must begin with a number or "PO BOX"');
2917 RAISE hr_utility.hr_error;
2918 END IF;
2919
2920 --
2921 BEGIN--Local1
2922 -- vikkybbbbb
2923 -- IF answer to question c1, c2 or c3 IS Yes (1)
2924 -- THEN all required fields will be required.
2925 -- 7441123
2926 /* IF (l_org_rec.c2_affiliated_14 = 1 OR l_org_rec.c1_over_100_13 = 1
2927 OR l_c_estab_details.gov_contract_15 = 1) THEN */
2928
2929 IF ( nvl(l_org_rec.c2_affiliated_14,'X') = '1' OR nvl(l_org_rec.c1_over_100_13,'X') = '1'
2930 OR nvl(to_char(l_c_estab_details.gov_contract_15),'X') = '1') THEN
2931
2932 IF (l_c_estab_details.naics_21 IS NULL AND l_org_rec.naics_21 IS NULL) THEN
2933 fnd_file.put_line
2934 (which => fnd_file.log,
2935 buff => '*==================================================*');
2936 fnd_file.put_line
2937 (which => fnd_file.log,
2938 buff => ' ');
2939 fnd_file.put_line
2940 (which => fnd_file.log,
2941 buff => 'The Location/Establishment NAICS NUMBER '
2942 ||'IS a required field ');
2943 fnd_file.put_line
2944 (which => fnd_file.log,
2945 buff => 'Please enter IN either location/extra info'
2946 ||'/eeo1 specific information ');
2947 fnd_file.put_line
2948 (which => fnd_file.log,
2949 buff => 'for unit/establishment: ');
2950 fnd_file.put_line
2951 (which => fnd_file.log,
2952 buff => '<<'||l_c_estab_details.unit_name_4||'>>');
2953 fnd_file.put_line
2954 (which => fnd_file.log,
2955 buff => 'address: ');
2956 fnd_file.put_line
2957 (which => fnd_file.log,
2958 buff => '<<'||l_c_estab_details.unit_address_req_5||' '
2959 ||l_c_estab_details.unit_address_6||' '
2960 ||l_c_estab_details.city_7||' '
2961 ||l_c_estab_details.state_8||' '
2962 ||l_c_estab_details.zip_code_9||'>>');
2963 fnd_file.put_line
2964 (which => fnd_file.log,
2965 buff => '*Nav path = location/extra info/EEO1/VETS Generic Data*');
2966 fnd_file.put_line
2967 (which => fnd_file.log,
2968 buff => ' ');
2969 fnd_file.put_line
2970 (which => fnd_file.log,
2974 buff => ' ');
2971 buff => '~OR~');
2972 fnd_file.put_line
2973 (which => fnd_file.log,
2975 fnd_file.put_line
2976 (which => fnd_file.log,
2977 buff => 'enter at organization level - for the '
2978 ||'top organization IN this hierarchy (ie.GRE/Parent Entity)');
2979 fnd_file.put_line
2980 (which => fnd_file.log,
2981 buff => 'IN parent entity EEO1/VETS Establishment Data');
2982 fnd_file.put_line
2983 (which => fnd_file.log,
2984 buff => '*Nav path = Organization/Description/Parent Entity/'||
2985 'Others/EEO1/VETS Establishment Data*');
2986 fnd_file.put_line
2987 (which => fnd_file.log,
2988 buff => ' ');
2989 fnd_file.put_line
2990 (which => fnd_file.log,
2991 buff => '*==================================================*');
2992 RAISE hr_utility.hr_error;
2993 END IF; --(l_c_estab_details.naics_21 IS NULL AND l_org_rec.naics_21 IS NULL)
2994
2995 IF (l_org_rec.title_cert_off_22 IS NULL OR
2996 l_org_rec.name_cert_off_23 IS NULL OR
2997 l_org_rec.tel_num_24 IS NULL) THEN
2998 fnd_file.put_line
2999 (which => fnd_file.log,
3000 buff => '==================================');
3001 fnd_file.put_line
3002 (which => fnd_file.log,
3003 buff => ' ');
3004 fnd_file.put_line
3005 (which => fnd_file.log,
3006 buff => 'Fields 22/23/24 - Title of certifying official AND'
3007 ||'/or their name AND phone NUMBER IS blank. ');
3008 fnd_file.put_line
3009 (which => fnd_file.log,
3010 buff => 'These are required fields. Please enter '
3011 ||'at organization level for the Business Group ');
3012 fnd_file.put_line
3013 (which => fnd_file.log,
3014 buff => ' ');
3015 fnd_file.put_line
3016 (which => fnd_file.log,
3017 buff => '<<'||l_org_rec.org_name||'>>');
3018 fnd_file.put_line
3019 (which => fnd_file.log,
3020 buff => ' ');
3021 fnd_file.put_line
3022 (which => fnd_file.log,
3023 buff => 'nav path = '
3024 ||'Business Group Organization/Description/Business Group'
3025 ||'/Others/EEO Report Details');
3026 fnd_file.put_line
3027 (which => fnd_file.log,
3028 buff => '(unit name: '
3029 ||l_c_estab_details.unit_name_4||' address: '
3030 ||l_c_estab_details.unit_address_req_5||' '
3031 ||l_c_estab_details.zip_code_9||')');
3032 fnd_file.put_line
3033 (which => fnd_file.log,
3034 buff => ' ');
3035 fnd_file.put_line
3036 (which => fnd_file.log,
3037 buff => '==================================');
3038 RAISE hr_utility.hr_error;
3039 END IF;--l_org_rec.title_cert_off_22 IS NULL
3040
3041 END IF;--l_org_rec.c2_affiliated_14 = 1
3042
3043 END;--Local1
3044
3045 l_estab_rec := l_estab_rec_blank;
3046 l_hierarchy_node_id := l_c_estab_details.hierarchy_node_id;
3047 l_estab_rec.unit_number_3 := l_c_estab_details.unit_number_3;
3048 l_estab_rec.unit_name_4 := l_c_estab_details.unit_name_4;
3049 l_estab_rec.unit_address_req_5 := l_c_estab_details.unit_address_req_5;
3050 l_estab_rec.unit_address_6 := l_c_estab_details.unit_address_6;
3051 l_estab_rec.city_7 := l_c_estab_details.city_7;
3052 l_estab_rec.state_8 := l_c_estab_details.state_8;
3053 l_estab_rec.zip_code_9 := l_c_estab_details.zip_code_9;
3054 l_estab_rec.zip_code_last_4_10 := l_c_estab_details.zip_code_last_4_10;
3055 l_estab_rec.reported_last_year_11 := l_c_estab_details.reported_last_year_11;
3056 l_estab_rec.ein_12 := l_c_estab_details.ein_12;
3057 l_estab_rec.gov_contract_15 := l_c_estab_details.gov_contract_15;
3058 l_estab_rec.duns_16 := l_c_estab_details.duns_16;
3059 l_estab_rec.county_17 := l_c_estab_details.county_17;
3060 l_estab_rec.apprentices_emp_19 := l_c_estab_details.apprentices_emp_19;
3061 l_estab_rec.sic_20 := l_c_estab_details.sic_20;
3062 l_estab_rec.naics_21 := l_c_estab_details.naics_21; -- vik need nvl here?
3063 l_estab_rec.hq := l_c_estab_details.hq;
3064
3065
3066 OPEN c_estab_max;
3067 FETCH c_estab_max INTO l_estab_max;
3068 EXIT WHEN c_estab_details%NOTFOUND;
3069 CLOSE c_estab_max;
3070
3071 IF l_estab_max >= 50 THEN
3072 l_estab_rec.max_count := 'Y';
3073 ELSE
3074 l_estab_rec.max_count := 'N';
3075 END IF;
3076
3077 hr_utility.set_location(l_proc,10);
3078 hr_utility.trace('p_hierarchy_version_id : ' || p_hierarchy_version_id);
3079
3080 /* OPEN c_mf_details;
3081 LOOP
3082 FETCH c_mf_details INTO l_c_mf_details;
3083 EXIT WHEN c_mf_details%NOTFOUND;
3084
3085 hr_utility.trace('l_hierarchy_node_id : ' || l_hierarchy_node_id);
3086
3087 IF l_c_mf_details.lookup_code = '1' THEN
3088 --
3089 -- count First/Mid Level Officials and Managers
3090 --
3091 l_estab_rec.a_1_total_mf := l_c_mf_details.c_mf;
3092 --
3096 ELSIF l_c_mf_details.lookup_code = '2' THEN
3093 l_consol_rec.a_1_total_mf := nvl(l_consol_rec.a_1_total_mf,0) +
3094 nvl(l_estab_rec.a_1_total_mf,0);
3095
3097 --
3098 -- count professionals (pr)
3099 --
3100 l_estab_rec.a_2_total_mf := l_c_mf_details.c_mf;
3101 l_consol_rec.a_2_total_mf := nvl(l_consol_rec.a_2_total_mf,0) +
3102 nvl(l_estab_rec.a_2_total_mf,0);
3103
3104 ELSIF l_c_mf_details.lookup_code = '3' THEN
3105 --
3106 -- count technicians (te)
3107 --
3108 l_estab_rec.a_3_total_mf := l_c_mf_details.c_mf;
3109 l_consol_rec.a_3_total_mf := nvl(l_consol_rec.a_3_total_mf,0) +
3110 nvl(l_estab_rec.a_3_total_mf,0);
3111
3112 ELSIF l_c_mf_details.lookup_code = '4' THEN
3113 --
3114 -- count salesworkers (sa)
3115 --
3116 l_estab_rec.a_4_total_mf := l_c_mf_details.c_mf;
3117 --
3118 l_consol_rec.a_4_total_mf := nvl(l_consol_rec.a_4_total_mf,0) +
3119 nvl(l_estab_rec.a_4_total_mf,0);
3120
3121 ELSIF l_c_mf_details.lookup_code = '5' THEN
3122 --
3123 -- count office AND clerical (oc)
3124 --
3125 l_estab_rec.a_5_total_mf := l_c_mf_details.c_mf;
3126 l_consol_rec.a_5_total_mf := nvl(l_consol_rec.a_5_total_mf,0) +
3127 nvl(l_estab_rec.a_5_total_mf,0);
3128
3129 ELSIF l_c_mf_details.lookup_code = '6' THEN
3130 --
3131 -- count craftworkers - skilled (cw)
3132 --
3133 l_estab_rec.a_6_total_mf := l_c_mf_details.c_mf;
3134 -- BUG4494412
3135 l_consol_rec.a_6_total_mf := nvl(l_consol_rec.a_6_total_mf,0) +
3136 nvl(l_estab_rec.a_6_total_mf,0);
3137
3138 ELSIF l_c_mf_details.lookup_code = '7' THEN
3139 --
3140 -- count operatives - semi skilled (op)
3141 --
3142 l_estab_rec.a_7_total_mf := l_c_mf_details.c_mf;
3143 --
3144 l_consol_rec.a_7_total_mf := nvl(l_consol_rec.a_7_total_mf,0) +
3145 nvl(l_estab_rec.a_7_total_mf,0);
3146
3147 ELSIF l_c_mf_details.lookup_code = '8' THEN
3148 --
3149 -- count laborers - unskilled (la)
3150 --
3151 l_estab_rec.a_8_total_mf := l_c_mf_details.c_mf;
3152 l_consol_rec.a_8_total_mf := nvl(l_consol_rec.a_8_total_mf,0) +
3153 nvl(l_estab_rec.a_8_total_mf,0);
3154 ELSIF l_c_mf_details.lookup_code = '9' THEN
3155 --
3156 -- count service workers (sw)
3157 --
3158 l_estab_rec.a_9_total_mf := l_c_mf_details.c_mf;
3159 l_consol_rec.a_9_total_mf := nvl(l_consol_rec.a_9_total_mf,0) +
3160 nvl(l_estab_rec.a_9_total_mf,0);
3161 END IF;
3162
3163 END LOOP;
3164
3165 CLOSE c_mf_details; */
3166
3167 hr_utility.set_location(l_proc,20);
3168 OPEN c_female_details;
3169 LOOP
3170 FETCH c_female_details INTO l_c_female_details;
3171 EXIT WHEN c_female_details%NOTFOUND;
3172
3173 --
3174 -- hawaii stuff here?
3175 --
3176 IF l_c_female_details.lookup_code = '10' THEN
3177 --
3178 -- count Executive/Senior Level Officials and Managers
3179 --
3180 l_estab_rec.b_1_hl_female := l_c_female_details.c_hlfemale;
3181 l_estab_rec.i_1_white_fem := l_c_female_details.c_wfemale;
3182 l_estab_rec.j_1_black_fem := l_c_female_details.c_bfemale;
3183 l_estab_rec.k_1_latin_fem := l_c_female_details.c_hfemale;
3184 l_estab_rec.l_1_aspac_fem := l_c_female_details.c_afemale;
3185 l_estab_rec.m_1_ameri_fem := l_c_female_details.c_ifemale;
3186 l_estab_rec.n_1_tmraces_female := l_c_female_details.c_tmracesfemale;
3187
3188 l_estab_rec.o_1_total_cat := l_estab_rec.b_1_hl_female +
3189 l_estab_rec.i_1_white_fem +
3190 l_estab_rec.j_1_black_fem +
3191 l_estab_rec.k_1_latin_fem +
3192 l_estab_rec.l_1_aspac_fem +
3193 l_estab_rec.m_1_ameri_fem +
3194 l_estab_rec.n_1_tmraces_female;
3195
3196 hr_utility.trace('l_estab_rec.b_1_hl_female : ' || l_estab_rec.b_1_hl_female);
3197 hr_utility.trace('l_estab_rec.i_1_white_fem : ' || l_estab_rec.i_1_white_fem);
3198 hr_utility.trace('l_estab_rec.j_1_black_fem : ' || l_estab_rec.j_1_black_fem);
3199 hr_utility.trace('l_estab_rec.k_1_latin_fem : ' || l_estab_rec.k_1_latin_fem);
3200 hr_utility.trace('l_estab_rec.l_1_aspac_fem : ' || l_estab_rec.l_1_aspac_fem);
3201 hr_utility.trace('l_estab_rec.m_1_ameri_fem : ' || l_estab_rec.m_1_ameri_fem);
3202 hr_utility.trace('l_estab_rec.n_1_tmraces_female : ' || l_estab_rec.n_1_tmraces_female);
3203
3204 -- IF Hawaii
3205 /* IF l_estab_rec.state_8 = 'HI' THEN
3206 l_estab_rec.i_1_white_fem := (l_estab_rec.b_1_hl_female+
3207 l_estab_rec.i_1_white_fem +
3208 l_estab_rec.j_1_black_fem +
3209 l_estab_rec.k_1_latin_fem +
3210 l_estab_rec.l_1_aspac_fem +
3211 l_estab_rec.m_1_ameri_fem +
3215 l_estab_rec.k_1_latin_fem := 0;
3212 l_estab_rec.n_1_tmraces_female);
3213 l_estab_rec.b_1_hl_female := 0;
3214 l_estab_rec.j_1_black_fem := 0;
3216 l_estab_rec.l_1_aspac_fem := 0;
3217 l_estab_rec.m_1_ameri_fem := 0;
3218 l_estab_rec.n_1_tmraces_female := 0;
3219
3220 END IF; */
3221
3222 l_consol_rec.b_1_hl_female := nvl(l_consol_rec.b_1_hl_female,0) +
3223 nvl(l_estab_rec.b_1_hl_female,0);
3224 l_consol_rec.i_1_white_fem := nvl(l_consol_rec.i_1_white_fem,0) +
3225 nvl(l_estab_rec.i_1_white_fem,0);
3226 l_consol_rec.j_1_black_fem := nvl(l_consol_rec.j_1_black_fem,0) +
3227 nvl(l_estab_rec.j_1_black_fem,0);
3228 l_consol_rec.k_1_latin_fem := nvl(l_consol_rec.k_1_latin_fem,0) +
3229 nvl(l_estab_rec.k_1_latin_fem,0);
3230 l_consol_rec.l_1_aspac_fem := nvl(l_consol_rec.l_1_aspac_fem,0) +
3231 nvl(l_estab_rec.l_1_aspac_fem,0);
3232 l_consol_rec.m_1_ameri_fem := nvl(l_consol_rec.m_1_ameri_fem,0) +
3233 nvl(l_estab_rec.m_1_ameri_fem,0);
3234 l_consol_rec.n_1_tmraces_female := nvl(l_consol_rec.n_1_tmraces_female,0) +
3235 nvl(l_estab_rec.n_1_tmraces_female,0);
3236
3237 ELSIF l_c_female_details.lookup_code = '1' THEN
3238 --
3239 -- count First/Mid Level Officials and Managers
3240 --
3241 l_estab_rec.b_2_hl_female := l_c_female_details.c_hlfemale;
3242 l_estab_rec.i_2_white_fem := l_c_female_details.c_wfemale;
3243 l_estab_rec.j_2_black_fem := l_c_female_details.c_bfemale;
3244 l_estab_rec.k_2_latin_fem := l_c_female_details.c_hfemale;
3245 l_estab_rec.l_2_aspac_fem := l_c_female_details.c_afemale;
3246 l_estab_rec.m_2_ameri_fem := l_c_female_details.c_ifemale;
3247 l_estab_rec.n_2_tmraces_female := l_c_female_details.c_tmracesfemale;
3248
3249 l_estab_rec.o_2_total_cat := l_estab_rec.b_2_hl_female +
3250 l_estab_rec.i_2_white_fem +
3251 l_estab_rec.j_2_black_fem +
3252 l_estab_rec.k_2_latin_fem +
3253 l_estab_rec.l_2_aspac_fem +
3254 l_estab_rec.m_2_ameri_fem +
3255 l_estab_rec.n_2_tmraces_female;
3256
3257 hr_utility.trace('l_estab_rec.b_2_hl_female : ' || l_estab_rec.b_2_hl_female);
3258 hr_utility.trace('l_estab_rec.i_2_white_fem : ' || l_estab_rec.i_2_white_fem);
3259 hr_utility.trace('l_estab_rec.j_2_black_fem : ' || l_estab_rec.j_2_black_fem);
3260 hr_utility.trace('l_estab_rec.k_2_latin_fem : ' || l_estab_rec.k_2_latin_fem);
3261 hr_utility.trace('l_estab_rec.l_2_aspac_fem : ' || l_estab_rec.l_2_aspac_fem);
3262 hr_utility.trace('l_estab_rec.m_2_ameri_fem : ' || l_estab_rec.m_2_ameri_fem);
3263 hr_utility.trace('l_estab_rec.n_2_tmraces_female : ' || l_estab_rec.n_2_tmraces_female);
3264
3265 -- IF Hawaii
3266 /* IF l_estab_rec.state_8 = 'HI' THEN
3267 l_estab_rec.i_2_white_fem := (l_estab_rec.b_2_hl_female +
3268 l_estab_rec.i_2_white_fem +
3269 l_estab_rec.j_2_black_fem +
3270 l_estab_rec.k_2_latin_fem +
3271 l_estab_rec.l_2_aspac_fem +
3272 l_estab_rec.m_2_ameri_fem +
3273 l_estab_rec.n_2_tmraces_female);
3274 l_estab_rec.b_2_hl_female := 0;
3275 l_estab_rec.j_2_black_fem := 0;
3276 l_estab_rec.k_2_latin_fem := 0;
3277 l_estab_rec.l_2_aspac_fem := 0;
3278 l_estab_rec.m_2_ameri_fem := 0;
3279 l_estab_rec.n_2_tmraces_female := 0;
3280
3281 END IF; */
3282
3283 l_consol_rec.b_2_hl_female := nvl(l_consol_rec.b_2_hl_female,0) +
3284 nvl(l_estab_rec.b_2_hl_female,0);
3285 l_consol_rec.i_2_white_fem := nvl(l_consol_rec.i_2_white_fem,0) +
3286 nvl(l_estab_rec.i_2_white_fem,0);
3287 l_consol_rec.j_2_black_fem := nvl(l_consol_rec.j_2_black_fem,0) +
3288 nvl(l_estab_rec.j_2_black_fem,0);
3289 l_consol_rec.k_2_latin_fem := nvl(l_consol_rec.k_2_latin_fem,0) +
3290 nvl(l_estab_rec.k_2_latin_fem,0);
3291 l_consol_rec.l_2_aspac_fem := nvl(l_consol_rec.l_2_aspac_fem,0) +
3292 nvl(l_estab_rec.l_2_aspac_fem,0);
3293 l_consol_rec.m_2_ameri_fem := nvl(l_consol_rec.m_2_ameri_fem,0) +
3294 nvl(l_estab_rec.m_2_ameri_fem,0);
3295 l_consol_rec.n_2_tmraces_female := nvl(l_consol_rec.n_2_tmraces_female,0) +
3296 nvl(l_estab_rec.n_2_tmraces_female,0);
3297
3298 ELSIF l_c_female_details.lookup_code = '2' THEN
3299 --
3300 -- count Professionals
3301 --
3302 l_estab_rec.b_3_hl_female := l_c_female_details.c_hlfemale;
3303 l_estab_rec.i_3_white_fem := l_c_female_details.c_wfemale;
3304 l_estab_rec.j_3_black_fem := l_c_female_details.c_bfemale;
3305 l_estab_rec.k_3_latin_fem := l_c_female_details.c_hfemale;
3306 l_estab_rec.l_3_aspac_fem := l_c_female_details.c_afemale;
3307 l_estab_rec.m_3_ameri_fem := l_c_female_details.c_ifemale;
3308 l_estab_rec.n_3_tmraces_female := l_c_female_details.c_tmracesfemale;
3309
3313 l_estab_rec.k_3_latin_fem +
3310 l_estab_rec.o_3_total_cat := l_estab_rec.b_3_hl_female +
3311 l_estab_rec.i_3_white_fem +
3312 l_estab_rec.j_3_black_fem +
3314 l_estab_rec.l_3_aspac_fem +
3315 l_estab_rec.m_3_ameri_fem +
3316 l_estab_rec.n_3_tmraces_female;
3317
3318 hr_utility.trace('l_estab_rec.b_3_hl_female : ' || l_estab_rec.b_3_hl_female);
3319 hr_utility.trace('l_estab_rec.i_3_white_fem : ' || l_estab_rec.i_3_white_fem);
3320 hr_utility.trace('l_estab_rec.j_3_black_fem : ' || l_estab_rec.j_3_black_fem);
3321 hr_utility.trace('l_estab_rec.k_3_latin_fem : ' || l_estab_rec.k_3_latin_fem);
3322 hr_utility.trace('l_estab_rec.l_3_aspac_fem : ' || l_estab_rec.l_3_aspac_fem);
3323 hr_utility.trace('l_estab_rec.m_3_ameri_fem : ' || l_estab_rec.m_3_ameri_fem);
3324 hr_utility.trace('l_estab_rec.n_3_tmraces_female : ' || l_estab_rec.n_3_tmraces_female);
3325
3326 -- IF Hawaii
3327 /* IF l_estab_rec.state_8 = 'HI' THEN
3328 l_estab_rec.i_3_white_fem := (l_estab_rec.b_3_hl_female +
3329 l_estab_rec.i_3_white_fem +
3330 l_estab_rec.j_3_black_fem +
3331 l_estab_rec.k_3_latin_fem +
3332 l_estab_rec.l_3_aspac_fem +
3333 l_estab_rec.m_3_ameri_fem +
3334 l_estab_rec.n_3_tmraces_female);
3335 l_estab_rec.b_3_hl_female := 0;
3336 l_estab_rec.j_3_black_fem := 0;
3337 l_estab_rec.k_3_latin_fem := 0;
3338 l_estab_rec.l_3_aspac_fem := 0;
3339 l_estab_rec.m_3_ameri_fem := 0;
3340 l_estab_rec.n_3_tmraces_female := 0;
3341
3342 END IF; */
3343
3344 l_consol_rec.b_3_hl_female := nvl(l_consol_rec.b_3_hl_female,0) +
3345 nvl(l_estab_rec.b_3_hl_female,0);
3346 l_consol_rec.i_3_white_fem := nvl(l_consol_rec.i_3_white_fem,0) +
3347 nvl(l_estab_rec.i_3_white_fem,0);
3348 l_consol_rec.j_3_black_fem := nvl(l_consol_rec.j_3_black_fem,0) +
3349 nvl(l_estab_rec.j_3_black_fem,0);
3350 l_consol_rec.k_3_latin_fem := nvl(l_consol_rec.k_3_latin_fem,0) +
3351 nvl(l_estab_rec.k_3_latin_fem,0);
3352 l_consol_rec.l_3_aspac_fem := nvl(l_consol_rec.l_3_aspac_fem,0) +
3353 nvl(l_estab_rec.l_3_aspac_fem,0);
3354 l_consol_rec.m_3_ameri_fem := nvl(l_consol_rec.m_3_ameri_fem,0) +
3355 nvl(l_estab_rec.m_3_ameri_fem,0);
3356 l_consol_rec.n_3_tmraces_female := nvl(l_consol_rec.n_3_tmraces_female,0) +
3357 nvl(l_estab_rec.n_3_tmraces_female,0);
3358
3359 ELSIF l_c_female_details.lookup_code = '3' THEN
3360 --
3361 -- count Technicians
3362 --
3363 l_estab_rec.b_4_hl_female := l_c_female_details.c_hlfemale;
3364 l_estab_rec.i_4_white_fem := l_c_female_details.c_wfemale;
3365 l_estab_rec.j_4_black_fem := l_c_female_details.c_bfemale;
3366 l_estab_rec.k_4_latin_fem := l_c_female_details.c_hfemale;
3367 l_estab_rec.l_4_aspac_fem := l_c_female_details.c_afemale;
3368 l_estab_rec.m_4_ameri_fem := l_c_female_details.c_ifemale;
3369 l_estab_rec.n_4_tmraces_female := l_c_female_details.c_tmracesfemale;
3370
3371 l_estab_rec.o_4_total_cat := l_estab_rec.b_4_hl_female +
3372 l_estab_rec.i_4_white_fem +
3373 l_estab_rec.j_4_black_fem +
3374 l_estab_rec.k_4_latin_fem +
3375 l_estab_rec.l_4_aspac_fem +
3376 l_estab_rec.m_4_ameri_fem +
3377 l_estab_rec.n_4_tmraces_female;
3378
3379 hr_utility.trace('l_estab_rec.b_4_hl_female : ' || l_estab_rec.b_4_hl_female);
3380 hr_utility.trace('l_estab_rec.i_4_white_fem : ' || l_estab_rec.i_4_white_fem);
3381 hr_utility.trace('l_estab_rec.j_4_black_fem : ' || l_estab_rec.j_4_black_fem);
3382 hr_utility.trace('l_estab_rec.k_4_latin_fem : ' || l_estab_rec.k_4_latin_fem);
3383 hr_utility.trace('l_estab_rec.l_4_aspac_fem : ' || l_estab_rec.l_4_aspac_fem);
3384 hr_utility.trace('l_estab_rec.m_4_ameri_fem : ' || l_estab_rec.m_4_ameri_fem);
3385 hr_utility.trace('l_estab_rec.n_4_tmraces_female : ' || l_estab_rec.n_4_tmraces_female);
3386
3387 -- IF Hawaii
3388 /* IF l_estab_rec.state_8 = 'HI' THEN
3389 l_estab_rec.i_4_white_fem := (l_estab_rec.b_4_hl_female +
3390 l_estab_rec.i_4_white_fem +
3391 l_estab_rec.j_4_black_fem +
3392 l_estab_rec.k_4_latin_fem +
3393 l_estab_rec.l_4_aspac_fem +
3394 l_estab_rec.m_4_ameri_fem +
3395 l_estab_rec.n_4_tmraces_female);
3396 l_estab_rec.b_4_hl_female := 0;
3397 l_estab_rec.j_4_black_fem := 0;
3398 l_estab_rec.k_4_latin_fem := 0;
3399 l_estab_rec.l_4_aspac_fem := 0;
3400 l_estab_rec.m_4_ameri_fem := 0;
3401 l_estab_rec.n_4_tmraces_female := 0;
3402 END IF; */
3403
3404 l_consol_rec.b_4_hl_female := nvl(l_consol_rec.b_4_hl_female,0) +
3405 nvl(l_estab_rec.b_4_hl_female,0);
3406 l_consol_rec.i_4_white_fem := nvl(l_consol_rec.i_4_white_fem,0) +
3410 l_consol_rec.k_4_latin_fem := nvl(l_consol_rec.k_4_latin_fem,0) +
3407 nvl(l_estab_rec.i_4_white_fem,0);
3408 l_consol_rec.j_4_black_fem := nvl(l_consol_rec.j_4_black_fem,0) +
3409 nvl(l_estab_rec.j_4_black_fem,0);
3411 nvl(l_estab_rec.k_4_latin_fem,0);
3412 l_consol_rec.l_4_aspac_fem := nvl(l_consol_rec.l_4_aspac_fem,0) +
3413 nvl(l_estab_rec.l_4_aspac_fem,0);
3414 l_consol_rec.m_4_ameri_fem := nvl(l_consol_rec.m_4_ameri_fem,0) +
3415 nvl(l_estab_rec.m_4_ameri_fem,0);
3416 l_consol_rec.n_4_tmraces_female := nvl(l_consol_rec.n_4_tmraces_female,0) +
3417 nvl(l_estab_rec.n_4_tmraces_female,0);
3418
3419 ELSIF l_c_female_details.lookup_code = '4' THEN
3420 --
3421 -- count Sales Workers
3422 --
3423 l_estab_rec.b_5_hl_female := l_c_female_details.c_hlfemale;
3424 l_estab_rec.i_5_white_fem := l_c_female_details.c_wfemale;
3425 l_estab_rec.j_5_black_fem := l_c_female_details.c_bfemale;
3426 l_estab_rec.k_5_latin_fem := l_c_female_details.c_hfemale;
3427 l_estab_rec.l_5_aspac_fem := l_c_female_details.c_afemale;
3428 l_estab_rec.m_5_ameri_fem := l_c_female_details.c_ifemale;
3429 l_estab_rec.n_5_tmraces_female := l_c_female_details.c_tmracesfemale;
3430
3431 l_estab_rec.o_5_total_cat := l_estab_rec.b_5_hl_female +
3432 l_estab_rec.i_5_white_fem +
3433 l_estab_rec.j_5_black_fem +
3434 l_estab_rec.k_5_latin_fem +
3435 l_estab_rec.l_5_aspac_fem +
3436 l_estab_rec.m_5_ameri_fem +
3437 l_estab_rec.n_5_tmraces_female;
3438
3439 hr_utility.trace('l_estab_rec.b_5_hl_female : ' || l_estab_rec.b_5_hl_female);
3440 hr_utility.trace('l_estab_rec.i_5_white_fem : ' || l_estab_rec.i_5_white_fem);
3441 hr_utility.trace('l_estab_rec.j_5_black_fem : ' || l_estab_rec.j_5_black_fem);
3442 hr_utility.trace('l_estab_rec.k_5_latin_fem : ' || l_estab_rec.k_5_latin_fem);
3443 hr_utility.trace('l_estab_rec.l_5_aspac_fem : ' || l_estab_rec.l_5_aspac_fem);
3444 hr_utility.trace('l_estab_rec.m_5_ameri_fem : ' || l_estab_rec.m_5_ameri_fem);
3445 hr_utility.trace('l_estab_rec.n_5_tmraces_female : ' || l_estab_rec.n_5_tmraces_female);
3446
3447 -- IF Hawaii
3448 /* IF l_estab_rec.state_8 = 'HI' THEN
3449 l_estab_rec.i_5_white_fem := (l_estab_rec.b_5_hl_female +
3450 l_estab_rec.i_5_white_fem +
3451 l_estab_rec.j_5_black_fem +
3452 l_estab_rec.k_5_latin_fem +
3453 l_estab_rec.l_5_aspac_fem +
3454 l_estab_rec.m_5_ameri_fem +
3455 l_estab_rec.n_5_tmraces_female);
3456 l_estab_rec.b_5_hl_female := 0;
3457 l_estab_rec.j_5_black_fem := 0;
3458 l_estab_rec.k_5_latin_fem := 0;
3459 l_estab_rec.l_5_aspac_fem := 0;
3460 l_estab_rec.m_5_ameri_fem := 0;
3461 l_estab_rec.n_5_tmraces_female := 0;
3462
3463 END IF; */
3464
3465 l_consol_rec.b_5_hl_female := nvl(l_consol_rec.b_5_hl_female,0) +
3466 nvl(l_estab_rec.b_5_hl_female,0);
3467 l_consol_rec.i_5_white_fem := nvl(l_consol_rec.i_5_white_fem,0) +
3468 nvl(l_estab_rec.i_5_white_fem,0);
3469 l_consol_rec.j_5_black_fem := nvl(l_consol_rec.j_5_black_fem,0) +
3470 nvl(l_estab_rec.j_5_black_fem,0);
3471 l_consol_rec.k_5_latin_fem := nvl(l_consol_rec.k_5_latin_fem,0) +
3472 nvl(l_estab_rec.k_5_latin_fem,0);
3473 l_consol_rec.l_5_aspac_fem := nvl(l_consol_rec.l_5_aspac_fem,0) +
3474 nvl(l_estab_rec.l_5_aspac_fem,0);
3475 l_consol_rec.m_5_ameri_fem := nvl(l_consol_rec.m_5_ameri_fem,0) +
3476 nvl(l_estab_rec.m_5_ameri_fem,0);
3477 l_consol_rec.n_5_tmraces_female := nvl(l_consol_rec.n_5_tmraces_female,0) +
3478 nvl(l_estab_rec.n_5_tmraces_female,0);
3479
3480 ELSIF l_c_female_details.lookup_code = '5' THEN
3481 --
3482 -- count Administrative Support Workers
3483 --
3484 l_estab_rec.b_6_hl_female := l_c_female_details.c_hlfemale;
3485 l_estab_rec.i_6_white_fem := l_c_female_details.c_wfemale;
3486 l_estab_rec.j_6_black_fem := l_c_female_details.c_bfemale;
3487 l_estab_rec.k_6_latin_fem := l_c_female_details.c_hfemale;
3488 l_estab_rec.l_6_aspac_fem := l_c_female_details.c_afemale;
3489 l_estab_rec.m_6_ameri_fem := l_c_female_details.c_ifemale;
3490 l_estab_rec.n_6_tmraces_female := l_c_female_details.c_tmracesfemale;
3491
3492 l_estab_rec.o_6_total_cat := l_estab_rec.b_6_hl_female +
3493 l_estab_rec.i_6_white_fem +
3494 l_estab_rec.j_6_black_fem +
3495 l_estab_rec.k_6_latin_fem +
3496 l_estab_rec.l_6_aspac_fem +
3497 l_estab_rec.m_6_ameri_fem +
3498 l_estab_rec.n_6_tmraces_female;
3499
3500 hr_utility.trace('l_estab_rec.b_6_hl_female : ' || l_estab_rec.b_6_hl_female);
3501 hr_utility.trace('l_estab_rec.i_6_white_fem : ' || l_estab_rec.i_6_white_fem);
3502 hr_utility.trace('l_estab_rec.j_6_black_fem : ' || l_estab_rec.j_6_black_fem);
3506 hr_utility.trace('l_estab_rec.n_6_tmraces_female : ' || l_estab_rec.n_6_tmraces_female);
3503 hr_utility.trace('l_estab_rec.k_6_latin_fem : ' || l_estab_rec.k_6_latin_fem);
3504 hr_utility.trace('l_estab_rec.l_6_aspac_fem : ' || l_estab_rec.l_6_aspac_fem);
3505 hr_utility.trace('l_estab_rec.m_6_ameri_fem : ' || l_estab_rec.m_6_ameri_fem);
3507
3508 -- IF Hawaii
3509 /* IF l_estab_rec.state_8 = 'HI' THEN
3510 l_estab_rec.i_6_white_fem := (l_estab_rec.b_6_hl_female +
3511 l_estab_rec.i_6_white_fem +
3512 l_estab_rec.j_6_black_fem +
3513 l_estab_rec.k_6_latin_fem +
3514 l_estab_rec.l_6_aspac_fem +
3515 l_estab_rec.m_6_ameri_fem +
3516 l_estab_rec.n_6_tmraces_female);
3517 l_estab_rec.b_6_hl_female := 0;
3518 l_estab_rec.j_6_black_fem := 0;
3519 l_estab_rec.k_6_latin_fem := 0;
3520 l_estab_rec.l_6_aspac_fem := 0;
3521 l_estab_rec.m_6_ameri_fem := 0;
3522 l_estab_rec.n_6_tmraces_female := 0;
3523
3524 END IF; */
3525
3526 l_consol_rec.b_6_hl_female := nvl(l_consol_rec.b_6_hl_female,0) +
3527 nvl(l_estab_rec.b_6_hl_female,0);
3528 l_consol_rec.i_6_white_fem := nvl(l_consol_rec.i_6_white_fem,0) +
3529 nvl(l_estab_rec.i_6_white_fem,0);
3530 l_consol_rec.j_6_black_fem := nvl(l_consol_rec.j_6_black_fem,0) +
3531 nvl(l_estab_rec.j_6_black_fem,0);
3532 l_consol_rec.k_6_latin_fem := nvl(l_consol_rec.k_6_latin_fem,0) +
3533 nvl(l_estab_rec.k_6_latin_fem,0);
3534 l_consol_rec.l_6_aspac_fem := nvl(l_consol_rec.l_6_aspac_fem,0) +
3535 nvl(l_estab_rec.l_6_aspac_fem,0);
3536 l_consol_rec.m_6_ameri_fem := nvl(l_consol_rec.m_6_ameri_fem,0) +
3537 nvl(l_estab_rec.m_6_ameri_fem,0);
3538 l_consol_rec.n_6_tmraces_female := nvl(l_consol_rec.n_6_tmraces_female,0) +
3539 nvl(l_estab_rec.n_6_tmraces_female,0);
3540
3541 ELSIF l_c_female_details.lookup_code = '6' THEN
3542 --
3543 -- count Craft Workers
3544 --
3545 l_estab_rec.b_7_hl_female := l_c_female_details.c_hlfemale;
3546 l_estab_rec.i_7_white_fem := l_c_female_details.c_wfemale;
3547 l_estab_rec.j_7_black_fem := l_c_female_details.c_bfemale;
3548 l_estab_rec.k_7_latin_fem := l_c_female_details.c_hfemale;
3549 l_estab_rec.l_7_aspac_fem := l_c_female_details.c_afemale;
3550 l_estab_rec.m_7_ameri_fem := l_c_female_details.c_ifemale;
3551 l_estab_rec.n_7_tmraces_female := l_c_female_details.c_tmracesfemale;
3552
3553 l_estab_rec.o_7_total_cat := l_estab_rec.b_7_hl_female +
3554 l_estab_rec.i_7_white_fem +
3555 l_estab_rec.j_7_black_fem +
3556 l_estab_rec.k_7_latin_fem +
3557 l_estab_rec.l_7_aspac_fem +
3558 l_estab_rec.m_7_ameri_fem +
3559 l_estab_rec.n_7_tmraces_female;
3560
3561 hr_utility.trace('l_estab_rec.b_7_hl_female : ' || l_estab_rec.b_7_hl_female);
3562 hr_utility.trace('l_estab_rec.i_7_white_fem : ' || l_estab_rec.i_7_white_fem);
3563 hr_utility.trace('l_estab_rec.j_7_black_fem : ' || l_estab_rec.j_7_black_fem);
3564 hr_utility.trace('l_estab_rec.k_7_latin_fem : ' || l_estab_rec.k_7_latin_fem);
3565 hr_utility.trace('l_estab_rec.l_7_aspac_fem : ' || l_estab_rec.l_7_aspac_fem);
3566 hr_utility.trace('l_estab_rec.m_7_ameri_fem : ' || l_estab_rec.m_7_ameri_fem);
3567 hr_utility.trace('l_estab_rec.n_7_tmraces_female : ' || l_estab_rec.n_7_tmraces_female);
3568
3569 -- IF Hawaii
3570 /* IF l_estab_rec.state_8 = 'HI' THEN
3571 l_estab_rec.i_7_white_fem := ( l_estab_rec.b_7_hl_female +
3572 l_estab_rec.i_7_white_fem +
3573 l_estab_rec.j_7_black_fem +
3574 l_estab_rec.k_7_latin_fem +
3575 l_estab_rec.l_7_aspac_fem +
3576 l_estab_rec.m_7_ameri_fem +
3577 l_estab_rec.n_7_tmraces_female);
3578 l_estab_rec.b_7_hl_female := 0;
3579 l_estab_rec.j_7_black_fem := 0;
3580 l_estab_rec.k_7_latin_fem := 0;
3581 l_estab_rec.l_7_aspac_fem := 0;
3582 l_estab_rec.m_7_ameri_fem := 0;
3583 l_estab_rec.n_7_tmraces_female := 0;
3584
3585 END IF; */
3586
3587 l_consol_rec.b_7_hl_female := nvl(l_consol_rec.b_7_hl_female,0) +
3588 nvl(l_estab_rec.b_7_hl_female,0);
3589 l_consol_rec.i_7_white_fem := nvl(l_consol_rec.i_7_white_fem,0) +
3590 nvl(l_estab_rec.i_7_white_fem,0);
3591 l_consol_rec.j_7_black_fem := nvl(l_consol_rec.j_7_black_fem,0) +
3592 nvl(l_estab_rec.j_7_black_fem,0);
3593 l_consol_rec.k_7_latin_fem := nvl(l_consol_rec.k_7_latin_fem,0) +
3594 nvl(l_estab_rec.k_7_latin_fem,0);
3595 l_consol_rec.l_7_aspac_fem := nvl(l_consol_rec.l_7_aspac_fem,0) +
3596 nvl(l_estab_rec.l_7_aspac_fem,0);
3597 l_consol_rec.m_7_ameri_fem := nvl(l_consol_rec.m_7_ameri_fem,0) +
3601 --
3598 nvl(l_estab_rec.m_7_ameri_fem,0);
3599 l_consol_rec.n_7_tmraces_female := nvl(l_consol_rec.n_7_tmraces_female,0) +
3600 nvl(l_estab_rec.n_7_tmraces_female,0);
3602 ELSIF l_c_female_details.lookup_code = '7' THEN
3603 --
3604 -- count Operatives
3605 --
3606 l_estab_rec.b_8_hl_female := l_c_female_details.c_hlfemale;
3607 l_estab_rec.i_8_white_fem := l_c_female_details.c_wfemale;
3608 l_estab_rec.j_8_black_fem := l_c_female_details.c_bfemale;
3609 l_estab_rec.k_8_latin_fem := l_c_female_details.c_hfemale;
3610 l_estab_rec.l_8_aspac_fem := l_c_female_details.c_afemale;
3611 l_estab_rec.m_8_ameri_fem := l_c_female_details.c_ifemale;
3612 l_estab_rec.n_8_tmraces_female := l_c_female_details.c_tmracesfemale;
3613
3614 l_estab_rec.o_8_total_cat := l_estab_rec.b_8_hl_female +
3615 l_estab_rec.i_8_white_fem +
3616 l_estab_rec.j_8_black_fem +
3617 l_estab_rec.k_8_latin_fem +
3618 l_estab_rec.l_8_aspac_fem +
3619 l_estab_rec.m_8_ameri_fem +
3620 l_estab_rec.n_8_tmraces_female;
3621
3622 hr_utility.trace('l_estab_rec.b_8_hl_female : ' || l_estab_rec.b_8_hl_female);
3623 hr_utility.trace('l_estab_rec.i_8_white_fem : ' || l_estab_rec.i_8_white_fem);
3624 hr_utility.trace('l_estab_rec.j_8_black_fem : ' || l_estab_rec.j_8_black_fem);
3625 hr_utility.trace('l_estab_rec.k_8_latin_fem : ' || l_estab_rec.k_8_latin_fem);
3626 hr_utility.trace('l_estab_rec.l_8_aspac_fem : ' || l_estab_rec.l_8_aspac_fem);
3627 hr_utility.trace('l_estab_rec.m_8_ameri_fem : ' || l_estab_rec.m_8_ameri_fem);
3628 hr_utility.trace('l_estab_rec.n_8_tmraces_female : ' || l_estab_rec.n_8_tmraces_female);
3629
3630 -- IF Hawaii
3631 /* IF l_estab_rec.state_8 = 'HI' THEN
3632 l_estab_rec.i_8_white_fem := (l_estab_rec.b_8_hl_female +
3633 l_estab_rec.i_8_white_fem +
3634 l_estab_rec.j_8_black_fem +
3635 l_estab_rec.k_8_latin_fem +
3636 l_estab_rec.l_8_aspac_fem +
3637 l_estab_rec.m_8_ameri_fem +
3638 l_estab_rec.n_8_tmraces_female);
3639 l_estab_rec.b_8_hl_female := 0;
3640 l_estab_rec.j_8_black_fem := 0;
3641 l_estab_rec.k_8_latin_fem := 0;
3642 l_estab_rec.l_8_aspac_fem := 0;
3643 l_estab_rec.m_8_ameri_fem := 0;
3644 l_estab_rec.n_8_tmraces_female := 0;
3645
3646 END IF; */
3647
3648 l_consol_rec.b_8_hl_female := nvl(l_consol_rec.b_8_hl_female,0) +
3649 nvl(l_estab_rec.b_8_hl_female,0);
3650 l_consol_rec.i_8_white_fem := nvl(l_consol_rec.i_8_white_fem,0) +
3651 nvl(l_estab_rec.i_8_white_fem,0);
3652 l_consol_rec.j_8_black_fem := nvl(l_consol_rec.j_8_black_fem,0) +
3653 nvl(l_estab_rec.j_8_black_fem,0);
3654 l_consol_rec.k_8_latin_fem := nvl(l_consol_rec.k_8_latin_fem,0) +
3655 nvl(l_estab_rec.k_8_latin_fem,0);
3656 l_consol_rec.l_8_aspac_fem := nvl(l_consol_rec.l_8_aspac_fem,0) +
3657 nvl(l_estab_rec.l_8_aspac_fem,0);
3658 l_consol_rec.m_8_ameri_fem := nvl(l_consol_rec.m_8_ameri_fem,0) +
3659 nvl(l_estab_rec.m_8_ameri_fem,0);
3660 l_consol_rec.n_8_tmraces_female := nvl(l_consol_rec.n_8_tmraces_female,0) +
3661 nvl(l_estab_rec.n_8_tmraces_female,0);
3662
3663 ELSIF l_c_female_details.lookup_code = '8' THEN
3664 --
3665 -- count Laborers and Helpers
3666 --
3667 l_estab_rec.b_9_hl_female := l_c_female_details.c_hlfemale;
3668 l_estab_rec.i_9_white_fem := l_c_female_details.c_wfemale;
3669 l_estab_rec.j_9_black_fem := l_c_female_details.c_bfemale;
3670 l_estab_rec.k_9_latin_fem := l_c_female_details.c_hfemale;
3671 l_estab_rec.l_9_aspac_fem := l_c_female_details.c_afemale;
3672 l_estab_rec.m_9_ameri_fem := l_c_female_details.c_ifemale;
3673 l_estab_rec.n_9_tmraces_female := l_c_female_details.c_tmracesfemale;
3674
3675 l_estab_rec.o_9_total_cat := l_estab_rec.b_9_hl_female +
3676 l_estab_rec.i_9_white_fem +
3677 l_estab_rec.j_9_black_fem +
3678 l_estab_rec.k_9_latin_fem +
3679 l_estab_rec.l_9_aspac_fem +
3680 l_estab_rec.m_9_ameri_fem +
3681 l_estab_rec.n_9_tmraces_female;
3682
3683 hr_utility.trace('l_estab_rec.b_9_hl_female : ' || l_estab_rec.b_9_hl_female);
3684 hr_utility.trace('l_estab_rec.i_9_white_fem : ' || l_estab_rec.i_9_white_fem);
3685 hr_utility.trace('l_estab_rec.j_9_black_fem : ' || l_estab_rec.j_9_black_fem);
3686 hr_utility.trace('l_estab_rec.k_9_latin_fem : ' || l_estab_rec.k_9_latin_fem);
3687 hr_utility.trace('l_estab_rec.l_9_aspac_fem : ' || l_estab_rec.l_9_aspac_fem);
3688 hr_utility.trace('l_estab_rec.m_9_ameri_fem : ' || l_estab_rec.m_9_ameri_fem);
3689 hr_utility.trace('l_estab_rec.n_9_tmraces_female : ' || l_estab_rec.n_9_tmraces_female);
3690
3691 -- IF Hawaii
3692 /* IF l_estab_rec.state_8 = 'HI' THEN
3693 l_estab_rec.i_9_white_fem := (l_estab_rec.b_9_hl_female +
3694 l_estab_rec.i_9_white_fem +
3695 l_estab_rec.j_9_black_fem +
3699 l_estab_rec.n_9_tmraces_female);
3696 l_estab_rec.k_9_latin_fem +
3697 l_estab_rec.l_9_aspac_fem +
3698 l_estab_rec.m_9_ameri_fem +
3700 l_estab_rec.b_9_hl_female := 0;
3701 l_estab_rec.j_9_black_fem := 0;
3702 l_estab_rec.k_9_latin_fem := 0;
3703 l_estab_rec.l_9_aspac_fem := 0;
3704 l_estab_rec.m_9_ameri_fem := 0;
3705 l_estab_rec.n_9_tmraces_female := 0;
3706
3707 END IF; */
3708 --
3709 l_consol_rec.b_9_hl_female := nvl(l_consol_rec.b_9_hl_female,0) +
3710 nvl(l_estab_rec.b_9_hl_female,0);
3711 l_consol_rec.i_9_white_fem := nvl(l_consol_rec.i_9_white_fem,0) +
3712 nvl(l_estab_rec.i_9_white_fem,0);
3713 l_consol_rec.j_9_black_fem := nvl(l_consol_rec.j_9_black_fem,0) +
3714 nvl(l_estab_rec.j_9_black_fem,0);
3715 l_consol_rec.k_9_latin_fem := nvl(l_consol_rec.k_9_latin_fem,0) +
3716 nvl(l_estab_rec.k_9_latin_fem,0);
3717 l_consol_rec.l_9_aspac_fem := nvl(l_consol_rec.l_9_aspac_fem,0) +
3718 nvl(l_estab_rec.l_9_aspac_fem,0);
3719 l_consol_rec.m_9_ameri_fem := nvl(l_consol_rec.m_9_ameri_fem,0) +
3720 nvl(l_estab_rec.m_9_ameri_fem,0);
3721 l_consol_rec.n_9_tmraces_female := nvl(l_consol_rec.n_9_tmraces_female,0) +
3722 nvl(l_estab_rec.n_9_tmraces_female,0);
3723
3724 ELSIF l_c_female_details.lookup_code = '9' THEN
3725 --
3726 -- count Service Workers
3727 --
3728 l_estab_rec.b_10_hl_female := l_c_female_details.c_hlfemale;
3729 l_estab_rec.i_10_white_fem := l_c_female_details.c_wfemale;
3730 l_estab_rec.j_10_black_fem := l_c_female_details.c_bfemale;
3731 l_estab_rec.k_10_latin_fem := l_c_female_details.c_hfemale;
3732 l_estab_rec.l_10_aspac_fem := l_c_female_details.c_afemale;
3733 l_estab_rec.m_10_ameri_fem := l_c_female_details.c_ifemale;
3734 l_estab_rec.n_10_tmraces_female := l_c_female_details.c_tmracesfemale;
3735
3736 l_estab_rec.o_10_total_cat := l_estab_rec.b_10_hl_female +
3737 l_estab_rec.i_10_white_fem +
3738 l_estab_rec.j_10_black_fem +
3739 l_estab_rec.k_10_latin_fem +
3740 l_estab_rec.l_10_aspac_fem +
3741 l_estab_rec.m_10_ameri_fem +
3742 l_estab_rec.n_10_tmraces_female;
3743
3744 hr_utility.trace('l_estab_rec.b_10_hl_female : ' || l_estab_rec.b_10_hl_female);
3745 hr_utility.trace('l_estab_rec.i_10_white_fem : ' || l_estab_rec.i_10_white_fem);
3746 hr_utility.trace('l_estab_rec.j_10_black_fem : ' || l_estab_rec.j_10_black_fem);
3747 hr_utility.trace('l_estab_rec.k_10_latin_fem : ' || l_estab_rec.k_10_latin_fem);
3748 hr_utility.trace('l_estab_rec.l_10_aspac_fem : ' || l_estab_rec.l_10_aspac_fem);
3749 hr_utility.trace('l_estab_rec.m_10_ameri_fem : ' || l_estab_rec.m_10_ameri_fem);
3750 hr_utility.trace('l_estab_rec.n_10_tmraces_female : ' || l_estab_rec.n_10_tmraces_female);
3751
3752 -- IF Hawaii
3753 /* IF l_estab_rec.state_8 = 'HI' THEN
3754 l_estab_rec.i_10_white_fem := (l_estab_rec.b_10_hl_female +
3755 l_estab_rec.i_10_white_fem +
3756 l_estab_rec.j_10_black_fem +
3757 l_estab_rec.k_10_latin_fem +
3758 l_estab_rec.l_10_aspac_fem +
3759 l_estab_rec.m_10_ameri_fem +
3760 l_estab_rec.n_10_tmraces_female);
3761 l_estab_rec.b_10_hl_female := 0;
3762 l_estab_rec.j_10_black_fem := 0;
3763 l_estab_rec.k_10_latin_fem := 0;
3764 l_estab_rec.l_10_aspac_fem := 0;
3765 l_estab_rec.m_10_ameri_fem := 0;
3766 l_estab_rec.n_10_tmraces_female := 0;
3767
3768 END IF; */
3769 --
3770 l_consol_rec.b_10_hl_female := nvl(l_consol_rec.b_10_hl_female,0) +
3771 nvl(l_estab_rec.b_10_hl_female,0);
3772 l_consol_rec.i_10_white_fem := nvl(l_consol_rec.i_10_white_fem,0) +
3773 nvl(l_estab_rec.i_10_white_fem,0);
3774 l_consol_rec.j_10_black_fem := nvl(l_consol_rec.j_10_black_fem,0) +
3775 nvl(l_estab_rec.j_10_black_fem,0);
3776 l_consol_rec.k_10_latin_fem := nvl(l_consol_rec.k_10_latin_fem,0) +
3777 nvl(l_estab_rec.k_10_latin_fem,0);
3778 l_consol_rec.l_10_aspac_fem := nvl(l_consol_rec.l_10_aspac_fem,0) +
3779 nvl(l_estab_rec.l_10_aspac_fem,0);
3780 l_consol_rec.m_10_ameri_fem := nvl(l_consol_rec.m_10_ameri_fem,0) +
3781 nvl(l_estab_rec.m_10_ameri_fem,0);
3782 l_consol_rec.n_10_tmraces_female := nvl(l_consol_rec.n_10_tmraces_female,0) +
3783 nvl(l_estab_rec.n_10_tmraces_female,0);
3784 END IF;
3785
3786 END LOOP;
3787
3788 CLOSE c_female_details;
3789
3790 hr_utility.set_location(l_proc,30);
3791 OPEN c_male_details;
3792 LOOP
3793 FETCH c_male_details INTO l_c_male_details;
3794 EXIT WHEN c_male_details%NOTFOUND;
3795
3799 --
3796 IF l_c_male_details.lookup_code = '10' THEN
3797 --
3798 -- count Executive/Senior Level Officials and Managers
3800 l_estab_rec.a_1_hl_male := l_c_male_details.c_hlmale;
3801 l_estab_rec.c_1_white_male := l_c_male_details.c_wmale;
3802 l_estab_rec.d_1_black_male := l_c_male_details.c_bmale;
3803 l_estab_rec.e_1_latin_male := l_c_male_details.c_hmale;
3804 l_estab_rec.f_1_aspac_male := l_c_male_details.c_amale;
3805 l_estab_rec.g_1_ameri_male := l_c_male_details.c_imale;
3806 l_estab_rec.h_1_tmraces_male := l_c_male_details.c_tmracesmale;
3807
3808 l_estab_rec.o_1_total_cat := l_estab_rec.o_1_total_cat +
3809 l_estab_rec.a_1_hl_male +
3810 l_estab_rec.c_1_white_male +
3811 l_estab_rec.d_1_black_male +
3812 l_estab_rec.e_1_latin_male +
3813 l_estab_rec.f_1_aspac_male +
3814 l_estab_rec.g_1_ameri_male +
3815 l_estab_rec.h_1_tmraces_male;
3816
3817 hr_utility.trace('l_estab_rec.a_1_hl_male : ' || l_estab_rec.a_1_hl_male);
3818 hr_utility.trace('l_estab_rec.c_1_white_male : ' || l_estab_rec.c_1_white_male);
3819 hr_utility.trace('l_estab_rec.d_1_black_male : ' || l_estab_rec.d_1_black_male);
3820 hr_utility.trace('l_estab_rec.e_1_latin_male : ' || l_estab_rec.e_1_latin_male);
3821 hr_utility.trace('l_estab_rec.f_1_aspac_male : ' || l_estab_rec.f_1_aspac_male);
3822 hr_utility.trace('l_estab_rec.g_1_ameri_male : ' || l_estab_rec.g_1_ameri_male);
3823 hr_utility.trace('l_estab_rec.h_1_tmraces_male : ' || l_estab_rec.h_1_tmraces_male);
3824 hr_utility.trace('l_estab_rec.o_1_total_cat : ' || l_estab_rec.o_1_total_cat);
3825
3826 -- IF Hawaii
3827 /* IF l_estab_rec.state_8 = 'HI' THEN
3828 l_estab_rec.c_1_white_male := (l_estab_rec.a_1_hl_male +
3829 l_estab_rec.c_1_white_male +
3830 l_estab_rec.d_1_black_male +
3831 l_estab_rec.e_1_latin_male +
3832 l_estab_rec.f_1_aspac_male +
3833 l_estab_rec.g_1_ameri_male +
3834 l_estab_rec.h_1_tmraces_male);
3835 l_estab_rec.a_1_hl_male := 0;
3836 l_estab_rec.d_1_black_male := 0;
3837 l_estab_rec.e_1_latin_male := 0;
3838 l_estab_rec.f_1_aspac_male := 0;
3839 l_estab_rec.g_1_ameri_male := 0;
3840 l_estab_rec.h_1_tmraces_male := 0;
3841
3842 END IF; */
3843
3844 l_consol_rec.a_1_hl_male := nvl(l_consol_rec.a_1_hl_male,0) +
3845 nvl(l_estab_rec.a_1_hl_male,0);
3846 l_consol_rec.c_1_white_male := nvl(l_consol_rec.c_1_white_male,0) +
3847 nvl(l_estab_rec.c_1_white_male,0);
3848 l_consol_rec.d_1_black_male := nvl(l_consol_rec.d_1_black_male,0) +
3849 nvl(l_estab_rec.d_1_black_male,0);
3850 l_consol_rec.e_1_latin_male := nvl(l_consol_rec.e_1_latin_male,0) +
3851 nvl(l_estab_rec.e_1_latin_male,0);
3852 l_consol_rec.f_1_aspac_male := nvl(l_consol_rec.f_1_aspac_male,0) +
3853 nvl(l_estab_rec.f_1_aspac_male,0);
3854 l_consol_rec.g_1_ameri_male := nvl(l_consol_rec.g_1_ameri_male,0) +
3855 nvl(l_estab_rec.g_1_ameri_male,0);
3856 l_consol_rec.h_1_tmraces_male := nvl(l_consol_rec.h_1_tmraces_male,0) +
3857 nvl(l_estab_rec.h_1_tmraces_male,0);
3858
3859 ELSIF l_c_male_details.lookup_code = '1' THEN
3860 --
3861 -- count First/Mid Level Officials and Managers
3862 --
3863 l_estab_rec.a_2_hl_male := l_c_male_details.c_hlmale;
3864 l_estab_rec.c_2_white_male := l_c_male_details.c_wmale;
3865 l_estab_rec.d_2_black_male := l_c_male_details.c_bmale;
3866 l_estab_rec.e_2_latin_male := l_c_male_details.c_hmale;
3867 l_estab_rec.f_2_aspac_male := l_c_male_details.c_amale;
3868 l_estab_rec.g_2_ameri_male := l_c_male_details.c_imale;
3869 l_estab_rec.h_2_tmraces_male := l_c_male_details.c_tmracesmale;
3870
3871 l_estab_rec.o_2_total_cat := l_estab_rec.o_2_total_cat +
3872 l_estab_rec.a_2_hl_male +
3873 l_estab_rec.c_2_white_male +
3874 l_estab_rec.d_2_black_male +
3875 l_estab_rec.e_2_latin_male +
3876 l_estab_rec.f_2_aspac_male +
3877 l_estab_rec.g_2_ameri_male +
3878 l_estab_rec.h_2_tmraces_male;
3879
3880 hr_utility.trace('l_estab_rec.a_2_hl_male : ' || l_estab_rec.a_2_hl_male);
3881 hr_utility.trace('l_estab_rec.c_2_white_male : ' || l_estab_rec.c_2_white_male);
3882 hr_utility.trace('l_estab_rec.d_2_black_male : ' || l_estab_rec.d_2_black_male);
3883 hr_utility.trace('l_estab_rec.e_2_latin_male : ' || l_estab_rec.e_2_latin_male);
3884 hr_utility.trace('l_estab_rec.f_2_aspac_male : ' || l_estab_rec.f_2_aspac_male);
3885 hr_utility.trace('l_estab_rec.g_2_ameri_male : ' || l_estab_rec.g_2_ameri_male);
3886 hr_utility.trace('l_estab_rec.h_2_tmraces_male : ' || l_estab_rec.h_2_tmraces_male);
3887 hr_utility.trace('l_estab_rec.o_2_total_cat : ' || l_estab_rec.o_2_total_cat);
3888
3889 -- IF Hawaii
3890 /* IF l_estab_rec.state_8 = 'HI' THEN
3891 l_estab_rec.c_2_white_male := (l_estab_rec.a_2_hl_male +
3892 l_estab_rec.c_2_white_male +
3893 l_estab_rec.d_2_black_male +
3894 l_estab_rec.e_2_latin_male +
3895 l_estab_rec.f_2_aspac_male +
3896 l_estab_rec.g_2_ameri_male +
3897 l_estab_rec.h_2_tmraces_male);
3898 l_estab_rec.a_2_hl_male := 0;
3899 l_estab_rec.d_2_black_male := 0;
3900 l_estab_rec.e_2_latin_male := 0;
3901 l_estab_rec.f_2_aspac_male := 0;
3902 l_estab_rec.g_2_ameri_male := 0;
3903 l_estab_rec.h_2_tmraces_male := 0;
3904
3905 END IF; */
3906
3907 l_consol_rec.a_2_hl_male := nvl(l_consol_rec.a_2_hl_male,0) +
3908 nvl(l_estab_rec.a_2_hl_male,0);
3909 l_consol_rec.c_2_white_male := nvl(l_consol_rec.c_2_white_male,0) +
3910 nvl(l_estab_rec.c_2_white_male,0);
3911 l_consol_rec.d_2_black_male := nvl(l_consol_rec.d_2_black_male,0) +
3912 nvl(l_estab_rec.d_2_black_male,0);
3913 l_consol_rec.e_2_latin_male := nvl(l_consol_rec.e_2_latin_male,0) +
3914 nvl(l_estab_rec.e_2_latin_male,0);
3915 l_consol_rec.f_2_aspac_male := nvl(l_consol_rec.f_2_aspac_male,0) +
3916 nvl(l_estab_rec.f_2_aspac_male,0);
3917 l_consol_rec.g_2_ameri_male := nvl(l_consol_rec.g_2_ameri_male,0) +
3918 nvl(l_estab_rec.g_2_ameri_male,0);
3919 l_consol_rec.h_2_tmraces_male := nvl(l_consol_rec.h_2_tmraces_male,0) +
3920 nvl(l_estab_rec.h_2_tmraces_male,0);
3921
3922 ELSIF l_c_male_details.lookup_code = '2' THEN
3923 --
3924 -- count Professionals
3925 --
3926 l_estab_rec.a_3_hl_male := l_c_male_details.c_hlmale;
3927 l_estab_rec.c_3_white_male := l_c_male_details.c_wmale;
3928 l_estab_rec.d_3_black_male := l_c_male_details.c_bmale;
3929 l_estab_rec.e_3_latin_male := l_c_male_details.c_hmale;
3930 l_estab_rec.f_3_aspac_male := l_c_male_details.c_amale;
3931 l_estab_rec.g_3_ameri_male := l_c_male_details.c_imale;
3932 l_estab_rec.h_3_tmraces_male := l_c_male_details.c_tmracesmale;
3933
3934 l_estab_rec.o_3_total_cat := l_estab_rec.o_3_total_cat +
3935 l_estab_rec.a_3_hl_male +
3936 l_estab_rec.c_3_white_male +
3937 l_estab_rec.d_3_black_male +
3938 l_estab_rec.e_3_latin_male +
3939 l_estab_rec.f_3_aspac_male +
3940 l_estab_rec.g_3_ameri_male +
3941 l_estab_rec.h_3_tmraces_male;
3942
3943 hr_utility.trace('l_estab_rec.a_3_hl_male : ' || l_estab_rec.a_3_hl_male);
3944 hr_utility.trace('l_estab_rec.c_3_white_male : ' || l_estab_rec.c_3_white_male);
3945 hr_utility.trace('l_estab_rec.d_3_black_male : ' || l_estab_rec.d_3_black_male);
3946 hr_utility.trace('l_estab_rec.e_3_latin_male : ' || l_estab_rec.e_3_latin_male);
3947 hr_utility.trace('l_estab_rec.f_3_aspac_male : ' || l_estab_rec.f_3_aspac_male);
3948 hr_utility.trace('l_estab_rec.g_3_ameri_male : ' || l_estab_rec.g_3_ameri_male);
3949 hr_utility.trace('l_estab_rec.h_3_tmraces_male : ' || l_estab_rec.h_3_tmraces_male);
3950 hr_utility.trace('l_estab_rec.o_3_total_cat : ' || l_estab_rec.o_3_total_cat);
3951
3952 -- IF Hawaii
3953 /* IF l_estab_rec.state_8 = 'HI' THEN
3954 l_estab_rec.c_3_white_male := (l_estab_rec.a_3_hl_male +
3955 l_estab_rec.c_3_white_male +
3956 l_estab_rec.d_3_black_male +
3957 l_estab_rec.e_3_latin_male +
3958 l_estab_rec.f_3_aspac_male +
3959 l_estab_rec.g_3_ameri_male +
3960 l_estab_rec.h_3_tmraces_male);
3961 l_estab_rec.a_3_hl_male := 0;
3962 l_estab_rec.d_3_black_male := 0;
3963 l_estab_rec.e_3_latin_male := 0;
3964 l_estab_rec.f_3_aspac_male := 0;
3965 l_estab_rec.g_3_ameri_male := 0;
3966 l_estab_rec.h_3_tmraces_male := 0;
3967
3968 END IF; */
3969
3970 l_consol_rec.a_3_hl_male := nvl(l_consol_rec.a_3_hl_male,0) +
3971 nvl(l_estab_rec.a_3_hl_male,0);
3972 l_consol_rec.c_3_white_male := nvl(l_consol_rec.c_3_white_male,0) +
3973 nvl(l_estab_rec.c_3_white_male,0);
3974 l_consol_rec.d_3_black_male := nvl(l_consol_rec.d_3_black_male,0) +
3975 nvl(l_estab_rec.d_3_black_male,0);
3976 l_consol_rec.e_3_latin_male := nvl(l_consol_rec.e_3_latin_male,0) +
3977 nvl(l_estab_rec.e_3_latin_male,0);
3978 l_consol_rec.f_3_aspac_male := nvl(l_consol_rec.f_3_aspac_male,0) +
3979 nvl(l_estab_rec.f_3_aspac_male,0);
3980 l_consol_rec.g_3_ameri_male := nvl(l_consol_rec.g_3_ameri_male,0) +
3981 nvl(l_estab_rec.g_3_ameri_male,0);
3982 l_consol_rec.h_3_tmraces_male := nvl(l_consol_rec.h_3_tmraces_male,0) +
3983 nvl(l_estab_rec.h_3_tmraces_male,0);
3984
3985 ELSIF l_c_male_details.lookup_code = '3' THEN
3986 --
3987 -- count Technicians
3988 --
3989 l_estab_rec.a_4_hl_male := l_c_male_details.c_hlmale;
3990 l_estab_rec.c_4_white_male := l_c_male_details.c_wmale;
3991 l_estab_rec.d_4_black_male := l_c_male_details.c_bmale;
3992 l_estab_rec.e_4_latin_male := l_c_male_details.c_hmale;
3993 l_estab_rec.f_4_aspac_male := l_c_male_details.c_amale;
3994 l_estab_rec.g_4_ameri_male := l_c_male_details.c_imale;
3995 l_estab_rec.h_4_tmraces_male := l_c_male_details.c_tmracesmale;
3996
3997 l_estab_rec.o_4_total_cat := l_estab_rec.o_4_total_cat +
3998 l_estab_rec.a_4_hl_male +
3999 l_estab_rec.c_4_white_male +
4000 l_estab_rec.d_4_black_male +
4001 l_estab_rec.e_4_latin_male +
4002 l_estab_rec.f_4_aspac_male +
4003 l_estab_rec.g_4_ameri_male +
4004 l_estab_rec.h_4_tmraces_male;
4005
4006 hr_utility.trace('l_estab_rec.a_4_hl_male : ' || l_estab_rec.a_4_hl_male);
4007 hr_utility.trace('l_estab_rec.c_4_white_male : ' || l_estab_rec.c_4_white_male);
4008 hr_utility.trace('l_estab_rec.d_4_black_male : ' || l_estab_rec.d_4_black_male);
4009 hr_utility.trace('l_estab_rec.e_4_latin_male : ' || l_estab_rec.e_4_latin_male);
4010 hr_utility.trace('l_estab_rec.f_4_aspac_male : ' || l_estab_rec.f_4_aspac_male);
4011 hr_utility.trace('l_estab_rec.g_4_ameri_male : ' || l_estab_rec.g_4_ameri_male);
4012 hr_utility.trace('l_estab_rec.h_4_tmraces_male : ' || l_estab_rec.h_4_tmraces_male);
4013 hr_utility.trace('l_estab_rec.o_4_total_cat : ' || l_estab_rec.o_4_total_cat);
4014
4015 -- IF Hawaii
4016 /* IF l_estab_rec.state_8 = 'HI' THEN
4017 l_estab_rec.c_4_white_male := (l_estab_rec.a_4_hl_male +
4018 l_estab_rec.c_4_white_male +
4019 l_estab_rec.d_4_black_male +
4020 l_estab_rec.e_4_latin_male +
4021 l_estab_rec.f_4_aspac_male +
4022 l_estab_rec.g_4_ameri_male +
4023 l_estab_rec.h_4_tmraces_male);
4024 l_estab_rec.a_4_hl_male := 0;
4025 l_estab_rec.d_4_black_male := 0;
4026 l_estab_rec.e_4_latin_male := 0;
4027 l_estab_rec.f_4_aspac_male := 0;
4028 l_estab_rec.g_4_ameri_male := 0;
4029 l_estab_rec.h_4_tmraces_male := 0;
4030
4031 END IF; */
4032
4033 l_consol_rec.a_4_hl_male := nvl(l_consol_rec.a_4_hl_male,0) +
4034 nvl(l_estab_rec.a_4_hl_male,0);
4035 l_consol_rec.c_4_white_male := nvl(l_consol_rec.c_4_white_male,0) +
4036 nvl(l_estab_rec.c_4_white_male,0);
4037 l_consol_rec.d_4_black_male := nvl(l_consol_rec.d_4_black_male,0) +
4038 nvl(l_estab_rec.d_4_black_male,0);
4039 l_consol_rec.e_4_latin_male := nvl(l_consol_rec.e_4_latin_male,0) +
4040 nvl(l_estab_rec.e_4_latin_male,0);
4041 l_consol_rec.f_4_aspac_male := nvl(l_consol_rec.f_4_aspac_male,0) +
4042 nvl(l_estab_rec.f_4_aspac_male,0);
4043 l_consol_rec.g_4_ameri_male := nvl(l_consol_rec.g_4_ameri_male,0) +
4044 nvl(l_estab_rec.g_4_ameri_male,0);
4045 l_consol_rec.h_4_tmraces_male := nvl(l_consol_rec.h_4_tmraces_male,0) +
4046 nvl(l_estab_rec.h_4_tmraces_male,0);
4047
4048 ELSIF l_c_male_details.lookup_code = '4' THEN
4049 --
4050 -- count Sales Workers
4051 --
4052 l_estab_rec.a_5_hl_male := l_c_male_details.c_hlmale;
4053 l_estab_rec.c_5_white_male := l_c_male_details.c_wmale;
4054 l_estab_rec.d_5_black_male := l_c_male_details.c_bmale;
4055 l_estab_rec.e_5_latin_male := l_c_male_details.c_hmale;
4056 l_estab_rec.f_5_aspac_male := l_c_male_details.c_amale;
4057 l_estab_rec.g_5_ameri_male := l_c_male_details.c_imale;
4058 l_estab_rec.h_5_tmraces_male := l_c_male_details.c_tmracesmale;
4059
4060 l_estab_rec.o_5_total_cat := l_estab_rec.o_5_total_cat +
4061 l_estab_rec.a_5_hl_male +
4062 l_estab_rec.c_5_white_male +
4063 l_estab_rec.d_5_black_male +
4064 l_estab_rec.e_5_latin_male +
4065 l_estab_rec.f_5_aspac_male +
4066 l_estab_rec.g_5_ameri_male +
4067 l_estab_rec.h_5_tmraces_male;
4068
4069 hr_utility.trace('l_estab_rec.a_5_hl_male : ' || l_estab_rec.a_5_hl_male);
4070 hr_utility.trace('l_estab_rec.c_5_white_male : ' || l_estab_rec.c_5_white_male);
4071 hr_utility.trace('l_estab_rec.d_5_black_male : ' || l_estab_rec.d_5_black_male);
4072 hr_utility.trace('l_estab_rec.e_5_latin_male : ' || l_estab_rec.e_5_latin_male);
4073 hr_utility.trace('l_estab_rec.f_5_aspac_male : ' || l_estab_rec.f_5_aspac_male);
4074 hr_utility.trace('l_estab_rec.g_5_ameri_male : ' || l_estab_rec.g_5_ameri_male);
4075 hr_utility.trace('l_estab_rec.h_5_tmraces_male : ' || l_estab_rec.h_5_tmraces_male);
4076 hr_utility.trace('l_estab_rec.o_5_total_cat : ' || l_estab_rec.o_5_total_cat);
4077
4078 -- IF Hawaii
4079 /* IF l_estab_rec.state_8 = 'HI' THEN
4080 l_estab_rec.c_5_white_male := (l_estab_rec.a_5_hl_male +
4081 l_estab_rec.c_5_white_male +
4082 l_estab_rec.d_5_black_male +
4083 l_estab_rec.e_5_latin_male +
4084 l_estab_rec.f_5_aspac_male +
4085 l_estab_rec.g_5_ameri_male +
4086 l_estab_rec.h_5_tmraces_male);
4087 l_estab_rec.a_5_hl_male := 0;
4088 l_estab_rec.d_5_black_male := 0;
4089 l_estab_rec.e_5_latin_male := 0;
4090 l_estab_rec.f_5_aspac_male := 0;
4091 l_estab_rec.g_5_ameri_male := 0;
4092 l_estab_rec.h_5_tmraces_male := 0;
4093
4094 END IF; */
4095
4096 l_consol_rec.a_5_hl_male := nvl(l_consol_rec.a_5_hl_male,0) +
4097 nvl(l_estab_rec.a_5_hl_male,0);
4098 l_consol_rec.c_5_white_male := nvl(l_consol_rec.c_5_white_male,0) +
4099 nvl(l_estab_rec.c_5_white_male,0);
4100 l_consol_rec.d_5_black_male := nvl(l_consol_rec.d_5_black_male,0) +
4101 nvl(l_estab_rec.d_5_black_male,0);
4102 l_consol_rec.e_5_latin_male := nvl(l_consol_rec.e_5_latin_male,0) +
4103 nvl(l_estab_rec.e_5_latin_male,0);
4104 l_consol_rec.f_5_aspac_male := nvl(l_consol_rec.f_5_aspac_male,0) +
4105 nvl(l_estab_rec.f_5_aspac_male,0);
4106 l_consol_rec.g_5_ameri_male := nvl(l_consol_rec.g_5_ameri_male,0) +
4107 nvl(l_estab_rec.g_5_ameri_male,0);
4108 l_consol_rec.h_5_tmraces_male := nvl(l_consol_rec.h_5_tmraces_male,0) +
4109 nvl(l_estab_rec.h_5_tmraces_male,0);
4110
4111 ELSIF l_c_male_details.lookup_code = '5' THEN
4112 --
4113 -- count Administrative Support Workers
4114 --
4115 l_estab_rec.a_6_hl_male := l_c_male_details.c_hlmale;
4116 l_estab_rec.c_6_white_male := l_c_male_details.c_wmale;
4117 l_estab_rec.d_6_black_male := l_c_male_details.c_bmale;
4118 l_estab_rec.e_6_latin_male := l_c_male_details.c_hmale;
4119 l_estab_rec.f_6_aspac_male := l_c_male_details.c_amale;
4120 l_estab_rec.g_6_ameri_male := l_c_male_details.c_imale;
4121 l_estab_rec.h_6_tmraces_male := l_c_male_details.c_tmracesmale;
4122
4123 l_estab_rec.o_6_total_cat := l_estab_rec.o_6_total_cat +
4124 l_estab_rec.a_6_hl_male +
4125 l_estab_rec.c_6_white_male +
4126 l_estab_rec.d_6_black_male +
4127 l_estab_rec.e_6_latin_male +
4128 l_estab_rec.f_6_aspac_male +
4129 l_estab_rec.g_6_ameri_male +
4130 l_estab_rec.h_6_tmraces_male;
4131
4132 hr_utility.trace('l_estab_rec.a_6_hl_male : ' || l_estab_rec.a_6_hl_male);
4133 hr_utility.trace('l_estab_rec.c_6_white_male : ' || l_estab_rec.c_6_white_male);
4134 hr_utility.trace('l_estab_rec.d_6_black_male : ' || l_estab_rec.d_6_black_male);
4135 hr_utility.trace('l_estab_rec.e_6_latin_male : ' || l_estab_rec.e_6_latin_male);
4136 hr_utility.trace('l_estab_rec.f_6_aspac_male : ' || l_estab_rec.f_6_aspac_male);
4137 hr_utility.trace('l_estab_rec.g_6_ameri_male : ' || l_estab_rec.g_6_ameri_male);
4138 hr_utility.trace('l_estab_rec.h_6_tmraces_male : ' || l_estab_rec.h_6_tmraces_male);
4139 hr_utility.trace('l_estab_rec.o_6_total_cat : ' || l_estab_rec.o_6_total_cat);
4140
4141 -- IF Hawaii
4142 /* IF l_estab_rec.state_8 = 'HI' THEN
4143 l_estab_rec.c_6_white_male := (l_estab_rec.a_6_hl_male +
4144 l_estab_rec.c_6_white_male +
4145 l_estab_rec.d_6_black_male +
4146 l_estab_rec.e_6_latin_male +
4147 l_estab_rec.f_6_aspac_male +
4148 l_estab_rec.g_6_ameri_male +
4149 l_estab_rec.h_6_tmraces_male);
4150 l_estab_rec.a_6_hl_male := 0;
4151 l_estab_rec.d_6_black_male := 0;
4152 l_estab_rec.e_6_latin_male := 0;
4153 l_estab_rec.f_6_aspac_male := 0;
4154 l_estab_rec.g_6_ameri_male := 0;
4155 l_estab_rec.h_6_tmraces_male := 0;
4156
4157 END IF; */
4158
4159 l_consol_rec.a_6_hl_male := nvl(l_consol_rec.a_6_hl_male,0) +
4160 nvl(l_estab_rec.a_6_hl_male,0);
4161 l_consol_rec.c_6_white_male := nvl(l_consol_rec.c_6_white_male,0) +
4162 nvl(l_estab_rec.c_6_white_male,0);
4163 l_consol_rec.d_6_black_male := nvl(l_consol_rec.d_6_black_male,0) +
4164 nvl(l_estab_rec.d_6_black_male,0);
4165 l_consol_rec.e_6_latin_male := nvl(l_consol_rec.e_6_latin_male,0) +
4166 nvl(l_estab_rec.e_6_latin_male,0);
4167 l_consol_rec.f_6_aspac_male := nvl(l_consol_rec.f_6_aspac_male,0) +
4168 nvl(l_estab_rec.f_6_aspac_male,0);
4169 l_consol_rec.g_6_ameri_male := nvl(l_consol_rec.g_6_ameri_male,0) +
4170 nvl(l_estab_rec.g_6_ameri_male,0);
4171 l_consol_rec.h_6_tmraces_male := nvl(l_consol_rec.h_6_tmraces_male,0) +
4172 nvl(l_estab_rec.h_6_tmraces_male,0);
4173
4174 ELSIF l_c_male_details.lookup_code = '6' THEN
4175 --
4176 -- count Craft Workers
4177 --
4178 l_estab_rec.a_7_hl_male := l_c_male_details.c_hlmale;
4179 l_estab_rec.c_7_white_male := l_c_male_details.c_wmale;
4180 l_estab_rec.d_7_black_male := l_c_male_details.c_bmale;
4181 l_estab_rec.e_7_latin_male := l_c_male_details.c_hmale;
4182 l_estab_rec.f_7_aspac_male := l_c_male_details.c_amale;
4183 l_estab_rec.g_7_ameri_male := l_c_male_details.c_imale;
4184 l_estab_rec.h_7_tmraces_male := l_c_male_details.c_tmracesmale;
4185
4186 l_estab_rec.o_7_total_cat := l_estab_rec.o_7_total_cat +
4187 l_estab_rec.a_7_hl_male +
4188 l_estab_rec.c_7_white_male +
4189 l_estab_rec.d_7_black_male +
4190 l_estab_rec.e_7_latin_male +
4191 l_estab_rec.f_7_aspac_male +
4192 l_estab_rec.g_7_ameri_male +
4193 l_estab_rec.h_7_tmraces_male;
4194
4195 hr_utility.trace('l_estab_rec.a_7_hl_male : ' || l_estab_rec.a_7_hl_male);
4196 hr_utility.trace('l_estab_rec.c_7_white_male : ' || l_estab_rec.c_7_white_male);
4197 hr_utility.trace('l_estab_rec.d_7_black_male : ' || l_estab_rec.d_7_black_male);
4198 hr_utility.trace('l_estab_rec.e_7_latin_male : ' || l_estab_rec.e_7_latin_male);
4199 hr_utility.trace('l_estab_rec.f_7_aspac_male : ' || l_estab_rec.f_7_aspac_male);
4200 hr_utility.trace('l_estab_rec.g_7_ameri_male : ' || l_estab_rec.g_7_ameri_male);
4201 hr_utility.trace('l_estab_rec.h_7_tmraces_male : ' || l_estab_rec.h_7_tmraces_male);
4202 hr_utility.trace('l_estab_rec.o_7_total_cat : ' || l_estab_rec.o_7_total_cat);
4203
4204 -- IF Hawaii
4205 /* IF l_estab_rec.state_8 = 'HI' THEN
4206 l_estab_rec.c_7_white_male := (l_estab_rec.a_7_hl_male +
4207 l_estab_rec.c_7_white_male +
4208 l_estab_rec.d_7_black_male +
4209 l_estab_rec.e_7_latin_male +
4210 l_estab_rec.f_7_aspac_male +
4211 l_estab_rec.g_7_ameri_male +
4212 l_estab_rec.h_7_tmraces_male);
4213 l_estab_rec.a_7_hl_male := 0;
4214 l_estab_rec.d_7_black_male := 0;
4215 l_estab_rec.e_7_latin_male := 0;
4216 l_estab_rec.f_7_aspac_male := 0;
4217 l_estab_rec.g_7_ameri_male := 0;
4218 l_estab_rec.h_7_tmraces_male := 0;
4219
4220 END IF; */
4221
4222 l_consol_rec.a_7_hl_male := nvl(l_consol_rec.a_7_hl_male,0) +
4223 nvl(l_estab_rec.a_7_hl_male,0);
4224 l_consol_rec.c_7_white_male := nvl(l_consol_rec.c_7_white_male,0) +
4225 nvl(l_estab_rec.c_7_white_male,0);
4226 l_consol_rec.d_7_black_male := nvl(l_consol_rec.d_7_black_male,0) +
4227 nvl(l_estab_rec.d_7_black_male,0);
4228 l_consol_rec.e_7_latin_male := nvl(l_consol_rec.e_7_latin_male,0) +
4229 nvl(l_estab_rec.e_7_latin_male,0);
4230 l_consol_rec.f_7_aspac_male := nvl(l_consol_rec.f_7_aspac_male,0) +
4231 nvl(l_estab_rec.f_7_aspac_male,0);
4232 l_consol_rec.g_7_ameri_male := nvl(l_consol_rec.g_7_ameri_male,0) +
4233 nvl(l_estab_rec.g_7_ameri_male,0);
4234 l_consol_rec.h_7_tmraces_male := nvl(l_consol_rec.h_7_tmraces_male,0) +
4235 nvl(l_estab_rec.h_7_tmraces_male,0);
4236 --
4237 ELSIF l_c_male_details.lookup_code = '7' THEN
4238 --
4239 -- count Operatives
4240 --
4241 l_estab_rec.a_8_hl_male := l_c_male_details.c_hlmale;
4242 l_estab_rec.c_8_white_male := l_c_male_details.c_wmale;
4243 l_estab_rec.d_8_black_male := l_c_male_details.c_bmale;
4244 l_estab_rec.e_8_latin_male := l_c_male_details.c_hmale;
4245 l_estab_rec.f_8_aspac_male := l_c_male_details.c_amale;
4246 l_estab_rec.g_8_ameri_male := l_c_male_details.c_imale;
4247 l_estab_rec.h_8_tmraces_male := l_c_male_details.c_tmracesmale;
4248
4249 l_estab_rec.o_8_total_cat := l_estab_rec.o_8_total_cat +
4250 l_estab_rec.a_8_hl_male +
4251 l_estab_rec.c_8_white_male +
4252 l_estab_rec.d_8_black_male +
4253 l_estab_rec.e_8_latin_male +
4254 l_estab_rec.f_8_aspac_male +
4255 l_estab_rec.g_8_ameri_male +
4256 l_estab_rec.h_8_tmraces_male;
4257
4258 hr_utility.trace('l_estab_rec.a_8_hl_male : ' || l_estab_rec.a_8_hl_male);
4259 hr_utility.trace('l_estab_rec.c_8_white_male : ' || l_estab_rec.c_8_white_male);
4260 hr_utility.trace('l_estab_rec.d_8_black_male : ' || l_estab_rec.d_8_black_male);
4261 hr_utility.trace('l_estab_rec.e_8_latin_male : ' || l_estab_rec.e_8_latin_male);
4262 hr_utility.trace('l_estab_rec.f_8_aspac_male : ' || l_estab_rec.f_8_aspac_male);
4263 hr_utility.trace('l_estab_rec.g_8_ameri_male : ' || l_estab_rec.g_8_ameri_male);
4264 hr_utility.trace('l_estab_rec.h_8_tmraces_male : ' || l_estab_rec.h_8_tmraces_male);
4265 hr_utility.trace('l_estab_rec.o_8_total_cat : ' || l_estab_rec.o_8_total_cat);
4266
4267 -- IF Hawaii
4268 /* IF l_estab_rec.state_8 = 'HI' THEN
4269 l_estab_rec.c_8_white_male := (l_estab_rec.a_8_hl_male +
4270 l_estab_rec.c_8_white_male +
4271 l_estab_rec.d_8_black_male +
4272 l_estab_rec.e_8_latin_male +
4273 l_estab_rec.f_8_aspac_male +
4274 l_estab_rec.g_8_ameri_male +
4275 l_estab_rec.h_8_tmraces_male);
4276 l_estab_rec.a_8_hl_male := 0;
4277 l_estab_rec.d_8_black_male := 0;
4278 l_estab_rec.e_8_latin_male := 0;
4279 l_estab_rec.f_8_aspac_male := 0;
4280 l_estab_rec.g_8_ameri_male := 0;
4281 l_estab_rec.h_8_tmraces_male := 0;
4282 END IF; */
4283
4284 l_consol_rec.a_8_hl_male := nvl(l_consol_rec.a_8_hl_male,0) +
4285 nvl(l_estab_rec.a_8_hl_male,0);
4286 l_consol_rec.c_8_white_male := nvl(l_consol_rec.c_8_white_male,0) +
4287 nvl(l_estab_rec.c_8_white_male,0);
4288 l_consol_rec.d_8_black_male := nvl(l_consol_rec.d_8_black_male,0) +
4289 nvl(l_estab_rec.d_8_black_male,0);
4290 l_consol_rec.e_8_latin_male := nvl(l_consol_rec.e_8_latin_male,0) +
4291 nvl(l_estab_rec.e_8_latin_male,0);
4292 l_consol_rec.f_8_aspac_male := nvl(l_consol_rec.f_8_aspac_male,0) +
4293 nvl(l_estab_rec.f_8_aspac_male,0);
4294 l_consol_rec.g_8_ameri_male := nvl(l_consol_rec.g_8_ameri_male,0) +
4295 nvl(l_estab_rec.g_8_ameri_male,0);
4296 l_consol_rec.h_8_tmraces_male := nvl(l_consol_rec.h_8_tmraces_male,0) +
4297 nvl(l_estab_rec.h_8_tmraces_male,0);
4298
4299 ELSIF l_c_male_details.lookup_code = '8' THEN
4300 --
4301 -- count Laborers and Helpers
4302 --
4303 l_estab_rec.a_9_hl_male := l_c_male_details.c_hlmale;
4304 l_estab_rec.c_9_white_male := l_c_male_details.c_wmale;
4305 l_estab_rec.d_9_black_male := l_c_male_details.c_bmale;
4306 l_estab_rec.e_9_latin_male := l_c_male_details.c_hmale;
4307 l_estab_rec.f_9_aspac_male := l_c_male_details.c_amale;
4308 l_estab_rec.g_9_ameri_male := l_c_male_details.c_imale;
4309 l_estab_rec.h_9_tmraces_male := l_c_male_details.c_tmracesmale;
4310
4311 l_estab_rec.o_9_total_cat := l_estab_rec.o_9_total_cat +
4312 l_estab_rec.a_9_hl_male +
4313 l_estab_rec.c_9_white_male +
4314 l_estab_rec.d_9_black_male +
4315 l_estab_rec.e_9_latin_male +
4316 l_estab_rec.f_9_aspac_male +
4317 l_estab_rec.g_9_ameri_male +
4318 l_estab_rec.h_9_tmraces_male;
4319
4320 hr_utility.trace('l_estab_rec.a_9_hl_male : ' || l_estab_rec.a_9_hl_male);
4321 hr_utility.trace('l_estab_rec.c_9_white_male : ' || l_estab_rec.c_9_white_male);
4322 hr_utility.trace('l_estab_rec.d_9_black_male : ' || l_estab_rec.d_9_black_male);
4323 hr_utility.trace('l_estab_rec.e_9_latin_male : ' || l_estab_rec.e_9_latin_male);
4324 hr_utility.trace('l_estab_rec.f_9_aspac_male : ' || l_estab_rec.f_9_aspac_male);
4325 hr_utility.trace('l_estab_rec.g_9_ameri_male : ' || l_estab_rec.g_9_ameri_male);
4326 hr_utility.trace('l_estab_rec.h_9_tmraces_male : ' || l_estab_rec.h_9_tmraces_male);
4327 hr_utility.trace('l_estab_rec.o_9_total_cat : ' || l_estab_rec.o_9_total_cat);
4328
4329 -- IF Hawaii
4330 /* IF l_estab_rec.state_8 = 'HI' THEN
4331 l_estab_rec.c_9_white_male := (l_estab_rec.a_9_hl_male +
4332 l_estab_rec.c_9_white_male +
4333 l_estab_rec.d_9_black_male +
4334 l_estab_rec.e_9_latin_male +
4335 l_estab_rec.f_9_aspac_male +
4336 l_estab_rec.g_9_ameri_male +
4337 l_estab_rec.h_9_tmraces_male);
4338 l_estab_rec.a_9_hl_male := 0;
4339 l_estab_rec.d_9_black_male := 0;
4340 l_estab_rec.e_9_latin_male := 0;
4341 l_estab_rec.f_9_aspac_male := 0;
4342 l_estab_rec.g_9_ameri_male := 0;
4343 l_estab_rec.h_9_tmraces_male := 0;
4344 END IF; */
4345
4346 l_consol_rec.a_9_hl_male := nvl(l_consol_rec.a_9_hl_male,0) +
4347 nvl(l_estab_rec.a_9_hl_male,0);
4348 l_consol_rec.c_9_white_male := nvl(l_consol_rec.c_9_white_male,0) +
4349 nvl(l_estab_rec.c_9_white_male,0);
4350 l_consol_rec.d_9_black_male := nvl(l_consol_rec.d_9_black_male,0) +
4351 nvl(l_estab_rec.d_9_black_male,0);
4352 l_consol_rec.e_9_latin_male := nvl(l_consol_rec.e_9_latin_male,0) +
4353 nvl(l_estab_rec.e_9_latin_male,0);
4354 l_consol_rec.f_9_aspac_male := nvl(l_consol_rec.f_9_aspac_male,0) +
4355 nvl(l_estab_rec.f_9_aspac_male,0);
4356 l_consol_rec.g_9_ameri_male := nvl(l_consol_rec.g_9_ameri_male,0) +
4357 nvl(l_estab_rec.g_9_ameri_male,0);
4358 l_consol_rec.h_9_tmraces_male := nvl(l_consol_rec.h_9_tmraces_male,0) +
4359 nvl(l_estab_rec.h_9_tmraces_male,0);
4360
4361 ELSIF l_c_male_details.lookup_code = '9' THEN
4362 --
4363 -- count Service Workers
4364 --
4365 l_estab_rec.a_10_hl_male := l_c_male_details.c_hlmale;
4366 l_estab_rec.c_10_white_male := l_c_male_details.c_wmale;
4367 l_estab_rec.d_10_black_male := l_c_male_details.c_bmale;
4368 l_estab_rec.e_10_latin_male := l_c_male_details.c_hmale;
4369 l_estab_rec.f_10_aspac_male := l_c_male_details.c_amale;
4370 l_estab_rec.g_10_ameri_male := l_c_male_details.c_imale;
4371 l_estab_rec.h_10_tmraces_male := l_c_male_details.c_tmracesmale;
4372
4373 l_estab_rec.o_10_total_cat := l_estab_rec.o_10_total_cat +
4374 l_estab_rec.a_10_hl_male +
4375 l_estab_rec.c_10_white_male +
4376 l_estab_rec.d_10_black_male +
4377 l_estab_rec.e_10_latin_male +
4378 l_estab_rec.f_10_aspac_male +
4379 l_estab_rec.g_10_ameri_male +
4380 l_estab_rec.h_10_tmraces_male;
4381
4382 hr_utility.trace('l_estab_rec.a_10_hl_male : ' || l_estab_rec.a_10_hl_male);
4383 hr_utility.trace('l_estab_rec.c_10_white_male : ' || l_estab_rec.c_10_white_male);
4384 hr_utility.trace('l_estab_rec.d_10_black_male : ' || l_estab_rec.d_10_black_male);
4385 hr_utility.trace('l_estab_rec.e_10_latin_male : ' || l_estab_rec.e_10_latin_male);
4386 hr_utility.trace('l_estab_rec.f_10_aspac_male : ' || l_estab_rec.f_10_aspac_male);
4387 hr_utility.trace('l_estab_rec.g_10_ameri_male : ' || l_estab_rec.g_10_ameri_male);
4388 hr_utility.trace('l_estab_rec.h_10_tmraces_male : ' || l_estab_rec.h_10_tmraces_male);
4389 hr_utility.trace('l_estab_rec.o_10_total_cat : ' || l_estab_rec.o_10_total_cat);
4390
4391 -- IF Hawaii
4392 /* IF l_estab_rec.state_8 = 'HI' THEN
4393 l_estab_rec.c_10_white_male := (l_estab_rec.a_10_hl_male +
4394 l_estab_rec.c_10_white_male +
4395 l_estab_rec.d_10_black_male +
4396 l_estab_rec.e_10_latin_male +
4397 l_estab_rec.f_10_aspac_male +
4398 l_estab_rec.g_10_ameri_male +
4399 l_estab_rec.h_10_tmraces_male);
4400 l_estab_rec.a_10_hl_male := 0;
4401 l_estab_rec.d_10_black_male := 0;
4402 l_estab_rec.e_10_latin_male := 0;
4403 l_estab_rec.f_10_aspac_male := 0;
4404 l_estab_rec.g_10_ameri_male := 0;
4405 l_estab_rec.h_10_tmraces_male := 0;
4406 END IF; */
4407
4408 l_consol_rec.a_10_hl_male := nvl(l_consol_rec.a_10_hl_male,0) +
4409 nvl(l_estab_rec.a_10_hl_male,0);
4410 l_consol_rec.c_10_white_male := nvl(l_consol_rec.c_10_white_male,0) +
4411 nvl(l_estab_rec.c_10_white_male,0);
4412 l_consol_rec.d_10_black_male := nvl(l_consol_rec.d_10_black_male,0) +
4413 nvl(l_estab_rec.d_10_black_male,0);
4414 l_consol_rec.e_10_latin_male := nvl(l_consol_rec.e_10_latin_male,0) +
4415 nvl(l_estab_rec.e_10_latin_male,0);
4416 l_consol_rec.f_10_aspac_male := nvl(l_consol_rec.f_10_aspac_male,0) +
4417 nvl(l_estab_rec.f_10_aspac_male,0);
4418 l_consol_rec.g_10_ameri_male := nvl(l_consol_rec.g_10_ameri_male,0) +
4419 nvl(l_estab_rec.g_10_ameri_male,0);
4420 l_consol_rec.h_10_tmraces_male := nvl(l_consol_rec.h_10_tmraces_male,0) +
4421 nvl(l_estab_rec.h_10_tmraces_male,0);
4422 END IF;
4423
4424 END LOOP;
4425
4426 CLOSE c_male_details;
4427 /*
4428 hr_utility.set_location(l_proc,40);
4429 OPEN c_lastyears_details;
4430 LOOP
4431 FETCH c_lastyears_details INTO l_c_lastyears_details;
4432 EXIT WHEN c_lastyears_details%NOTFOUND;
4433
4434 l_estab_rec.a_11_last_year_grand_total := l_c_lastyears_details.p_total_a;
4435 l_consol_rec.a_11_last_year_grand_total
4436 := nvl(l_consol_rec.a_11_last_year_grand_total,0) +
4437 nvl(l_estab_rec.a_11_last_year_grand_total,0);
4438
4439 g_message_text := 'l_c_lastyears_details.p_total_a -> '|| l_c_lastyears_details.p_total_a;
4440 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
4441
4442 g_message_text := 'l_consol_rec.a_11_last_year_grand_total -> '|| l_consol_rec.a_11_last_year_grand_total;
4443 fnd_file.put_line(which => fnd_file.log, buff => g_message_text);
4444
4445 l_estab_rec.b_11_last_year_grand_total := l_c_lastyears_details.p_wmale_b;
4446 l_consol_rec.b_11_last_year_grand_total
4447 := nvl(l_consol_rec.b_11_last_year_grand_total,0) +
4448 nvl(l_estab_rec.b_11_last_year_grand_total,0);
4449
4450 l_estab_rec.c_11_last_year_grand_total := l_c_lastyears_details.p_bmale_c;
4451 l_consol_rec.c_11_last_year_grand_total
4452 := nvl(l_consol_rec.c_11_last_year_grand_total,0) +
4453 nvl(l_estab_rec.c_11_last_year_grand_total,0);
4454
4455 l_estab_rec.d_11_last_year_grand_total := l_c_lastyears_details.p_hmale_d;
4456 l_consol_rec.d_11_last_year_grand_total
4457 := nvl(l_consol_rec.d_11_last_year_grand_total,0) +
4458 nvl(l_estab_rec.d_11_last_year_grand_total,0);
4459
4460 l_estab_rec.e_11_last_year_grand_total := l_c_lastyears_details.p_amale_e;
4461 l_consol_rec.e_11_last_year_grand_total
4462 := nvl(l_consol_rec.e_11_last_year_grand_total,0) +
4463 nvl(l_estab_rec.e_11_last_year_grand_total,0);
4464
4465 l_estab_rec.f_11_last_year_grand_total := l_c_lastyears_details.p_imale_f;
4466 l_consol_rec.f_11_last_year_grand_total
4467 := nvl(l_consol_rec.f_11_last_year_grand_total,0) +
4468 nvl(l_estab_rec.f_11_last_year_grand_total,0);
4469
4470 l_estab_rec.g_11_last_year_grand_total := l_c_lastyears_details.p_wfemale_g;
4471 l_consol_rec.g_11_last_year_grand_total
4472 := nvl(l_consol_rec.g_11_last_year_grand_total,0) +
4473 nvl(l_estab_rec.g_11_last_year_grand_total,0);
4477 := nvl(l_consol_rec.h_11_last_year_grand_total,0) +
4474
4475 l_estab_rec.h_11_last_year_grand_total := l_c_lastyears_details.p_bfemale_h;
4476 l_consol_rec.h_11_last_year_grand_total
4478 nvl(l_estab_rec.h_11_last_year_grand_total,0);
4479
4480 l_estab_rec.i_11_last_year_grand_total := l_c_lastyears_details.p_hfemale_i;
4481 l_consol_rec.i_11_last_year_grand_total
4482 := nvl(l_consol_rec.i_11_last_year_grand_total,0) +
4483 nvl(l_estab_rec.i_11_last_year_grand_total,0);
4484
4485 l_estab_rec.j_11_last_year_grand_total := l_c_lastyears_details.p_afemale_j;
4486 l_consol_rec.j_11_last_year_grand_total
4487 := nvl(l_consol_rec.j_11_last_year_grand_total,0) +
4488 nvl(l_estab_rec.j_11_last_year_grand_total,0);
4489
4490 l_estab_rec.k_11_last_year_grand_total := l_c_lastyears_details.p_ifemale_k;
4491 l_consol_rec.k_11_last_year_grand_total
4492 := nvl(l_consol_rec.k_11_last_year_grand_total,0) +
4493 nvl(l_estab_rec.k_11_last_year_grand_total,0);
4494 END loop;
4495
4496 CLOSE c_lastyears_details; */
4497
4498 hr_utility.set_location(l_proc,50);
4499
4500 write_establishment_record;
4501
4502
4503 IF p_report_mode = 'F'
4504 AND l_org_rec.l_status_code_2 IN ('1','3','4','9','8')
4505 -- added status 8 here, bug 3544973
4506 THEN
4507
4508 insert_location_eit(p_hierarchy_node_id => l_hierarchy_node_id,
4509 p_hierarchy_version_id => p_hierarchy_version_id,
4510 p_report_year => l_report_year);
4511
4512 END IF;
4513
4514 END LOOP; -- C_estab_details
4515
4516 CLOSE c_estab_details;
4517 --
4518 hr_utility.set_location(l_proc,60);
4519
4520 write_consolidated_record;
4521
4522 IF p_report_mode = 'F'
4523 AND l_org_rec.l_status_code_2 = '2' -- form type?
4524 THEN
4525 insert_org_eit(p_hierarchy_node_id => l_hierarchy_node_id,
4526 p_hierarchy_version_id => p_hierarchy_version_id,
4527 p_business_group_id => p_business_group_id,
4528 p_report_year => l_report_year);
4529 END IF;
4530
4531 hr_utility.set_location('Leaving..' ||l_proc,100);
4532
4533 END loop_through_establishments;
4534
4535
4536
4537 PROCEDURE eeo_mag_report
4538 (errbuf OUT NOCOPY VARCHAR2,
4539 retcode OUT NOCOPY NUMBER,
4540 p_start_date IN VARCHAR2,
4541 p_end_date IN VARCHAR2,
4542 p_hierarchy_id IN NUMBER,
4543 p_hierarchy_version_id IN NUMBER,
4544 p_report_mode IN VARCHAR2,
4545 p_business_group_id IN NUMBER
4546 ) IS
4547
4548 l_proc VARCHAR2(80) := g_package || 'eeo_mag_report';
4549 l_start_date DATE := fnd_date.canonical_to_date(p_start_date);
4550 l_end_date DATE := fnd_date.canonical_to_date(p_end_date);
4551 l_string VARCHAR2(5000); --1074
4552
4553 CURSOR c_nodes(p_hierarchy_version_id NUMBER) IS
4554 SELECT count('establishments')
4555 FROM per_gen_hierarchy_nodes
4556 WHERE node_type = 'EST'
4557 AND hierarchy_version_id = p_hierarchy_version_id;
4558
4559 l_count NUMBER;
4560
4561 BEGIN
4562 --hr_utility.trace_on(NULL,'ORACLE');
4563 hr_utility.set_location('Entering..'||l_proc,10);
4564
4565 g_message_text := 'EEO1 Entering...'||l_proc||' 10';
4566 fnd_file.put_line(which => fnd_file.log,buff => g_message_text);
4567
4568
4569
4570 set_org_details(p_hierarchy_version_id => p_hierarchy_version_id,
4571 p_business_group_id => p_business_group_id,
4572 p_start_date => l_start_date,
4573 p_end_date => l_end_date);
4574
4575
4576
4577 OPEN c_nodes(p_hierarchy_version_id);
4578 FETCH c_nodes INTO l_count;
4579 IF l_count = 1 THEN
4580 l_org_rec.form_type := 'S';
4581 ELSE
4582 l_org_rec.form_type := 'M';
4583 END IF;
4584 CLOSE c_nodes;
4585
4586 g_message_text := 'EEO1 l_org_rec.form_type -> '||l_org_rec.form_type;
4587 fnd_file.put_line(which => fnd_file.log,buff => g_message_text);
4588
4589 g_message_text := 'EEO1 p_hierarchy_version_id -> '||p_hierarchy_version_id;
4590 fnd_file.put_line(which => fnd_file.log,buff => g_message_text);
4591
4592 g_message_text := 'EEO1 p_business_group_id -> '|| p_business_group_id;
4593 fnd_file.put_line(which => fnd_file.log,buff => g_message_text);
4594
4595 g_message_text := 'EEO1 l_start_date -> '|| l_start_date;
4596 fnd_file.put_line(which => fnd_file.log,buff => g_message_text);
4597
4598 g_message_text := 'EEO1 l_end_date -> '|| l_end_date;
4599 fnd_file.put_line(which => fnd_file.log,buff => g_message_text);
4600
4601 g_message_text := 'EEO1 p_report_mode -> '|| p_report_mode;
4602 fnd_file.put_line(which => fnd_file.log,buff => g_message_text);
4603
4604
4605 loop_through_establishments(p_hierarchy_version_id => p_hierarchy_version_id,
4606 p_business_group_id => p_business_group_id,
4607 p_start_date => l_start_date,
4608 p_end_date => l_end_date,
4609 p_report_mode => p_report_mode);
4610 --
4613 END per_eeo_mag_report;
4611 END eeo_mag_report;
4612 --