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