[Home] [Help]
PACKAGE BODY: APPS.PER_VETS_MAG_100A_REPORT
Source
1 package body per_vets_mag_100a_report as
2 /* $Header: pevetmag100a.pkb 120.10.12020000.8 2012/10/26 09:09:48 sjawid ship $ */
3 type org_rec is record
4 (company_number varchar2(20),
5 contractor_type varchar2(80),
6 form_type varchar2(80),
7 number_of_estabs number(8),
8 ending_period varchar2(30),
9 parent_company varchar2(200),
10 street varchar2(200),
11 street2 varchar2(200),
12 city varchar2(200),
13 county varchar2(200),
14 state varchar2(200),
15 zip_code varchar2(200),
16 contact varchar2(200),
17 telephone varchar2(200),
18 email varchar2(200),
19 naics varchar2(200),
20 duns varchar2(200),
21 ein varchar2(200));
22 --
23 l_org_rec org_rec;
24 --
25 type month_rec is record
26 (jan number,
27 feb number,
28 mar number,
29 apr number,
30 may number,
31 jun number,
32 jul number,
33 aug number,
34 sep number,
35 oct number,
36 nov number,
37 dec number);
38 --
39 l_month_rec month_rec;
40 l_month_rec_blank month_rec;
41 --
42 type estab_rec is record
43 (unit_number varchar2(30),
44 reporting_name varchar2(200),
45 street varchar2(200),
46 street2 varchar2(200),
47 city varchar2(200),
48 county varchar2(200),
49 state varchar2(200),
50 zip_code varchar2(200),
51 naics varchar2(200),
52 duns varchar2(200),
53 ein varchar2(200),
57 m1_total number(8),
54 hq varchar2(200),
55
56 l1_total number(8),
58 n1_total number(8),
59 o1_total number(8),
60 p1_total number(8),
61 q1_total number(8),
62 r1_total number(8),
63 s1_total number(8),
64 t1_total number(8),
65 u1_total number(8),
66 l2_total number(8),
67 m2_total number(8),
68 n2_total number(8),
69 o2_total number(8),
70 p2_total number(8),
71 q2_total number(8),
72 r2_total number(8),
73 s2_total number(8),
74 t2_total number(8),
75 u2_total number(8),
76 l3_total number(8),
77 m3_total number(8),
78 n3_total number(8),
79 o3_total number(8),
80 p3_total number(8),
81 q3_total number(8),
82 r3_total number(8),
83 s3_total number(8),
84 t3_total number(8),
85 u3_total number(8),
86 l4_total number(8),
87 m4_total number(8),
88 n4_total number(8),
89 o4_total number(8),
90 p4_total number(8),
91 q4_total number(8),
92 r4_total number(8),
93 s4_total number(8),
94 t4_total number(8),
95 u4_total number(8),
96 l5_total number(8),
97 m5_total number(8),
98 n5_total number(8),
99 o5_total number(8),
100 p5_total number(8),
101 q5_total number(8),
102 r5_total number(8),
103 s5_total number(8),
104 t5_total number(8),
105 u5_total number(8),
106 l6_total number(8),
107 m6_total number(8),
108 n6_total number(8),
109 o6_total number(8),
110 p6_total number(8),
111 q6_total number(8),
112 r6_total number(8),
113 s6_total number(8),
114 t6_total number(8),
115 u6_total number(8),
116 l7_total number(8),
117 m7_total number(8),
118 n7_total number(8),
119 o7_total number(8),
120 p7_total number(8),
121 q7_total number(8),
122 r7_total number(8),
123 s7_total number(8),
124 t7_total number(8),
125 u7_total number(8),
126 l8_total number(8),
127 m8_total number(8),
128 n8_total number(8),
129 o8_total number(8),
130 p8_total number(8),
131 q8_total number(8),
132 r8_total number(8),
133 s8_total number(8),
134 t8_total number(8),
135 u8_total number(8),
136 l9_total number(8),
137 m9_total number(8),
138 n9_total number(8),
139 o9_total number(8),
140 p9_total number(8),
141 q9_total number(8),
142 r9_total number(8),
143 s9_total number(8),
144 t9_total number(8),
145 u9_total number(8),
146 l10_total number(8),
147 m10_total number(8),
148 n10_total number(8),
149 o10_total number(8),
150 p10_total number(8),
151 q10_total number(8),
152 r10_total number(8),
153 s10_total number(8),
154 t10_total number(8),
155 u10_total number(8),
156
157 l_grand_total number(8),
158 m_grand_total number(8),
159 n_grand_total number(8),
160 o_grand_total number(8),
161 p_grand_total number(8),
162 q_grand_total number(8),
163 r_grand_total number(8),
164 s_grand_total number(8),
165 t_grand_total number(8),
166 u_grand_total number(8),
167
168 min_count number(8),
169 max_count number(8));
170 --
171 l_estab_rec estab_rec;
172 l_consol_rec estab_rec;
173 l_holder_rec estab_rec;
174 l_estab_rec_blank estab_rec;
175 l_all_estab varchar2(1);
176 l_tot_emps number;
177 --
178
179 function check_recent_or_not(l_person_id IN per_all_people_f.person_id%TYPE,
180 l_report_end_date IN date)
181 return number
182 is
183 l_count number;
184 begin
185 select count(person_id) into l_count
186 from PER_PEOPLE_EXTRA_INFO ppei where
187 l_person_id = ppei.person_id
188 and ppei.information_type ='VETS 100A'
189 and pei_information1 is not null
190 and
191 ( months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),0)) between 0 and 12
192 or
193 months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),12)) between 0 and 12
194 or
195 months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),24)) between 0 and 12
196 or
197 months_between(l_report_end_date,(add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),36)-2)) between 0 and 12
198 );
199 return l_count;
200 exception
201 when others then
202 return 0;
203 end;
204
205
206 procedure set_org_details(p_hierarchy_version_id in number,
207 p_business_group_id in number) is
211 decode(hoi1.org_information3,'2S','S','1P','P','3B','B') contractor_type,
208 --
209 cursor c1 is
210 select upper(replace(hoi1.org_information2,',',' ')) company_number,
212 upper(replace(nvl(hoi1.org_information1,hou.name),',',' ')) parent_company, --converted to upper case, bug 13905482
213 upper(replace(loc.address_line_1,',',' ')||
214 ' ') street1,
215 upper(replace(loc.address_line_2,',',' ')||
216 ' '||
217 replace(loc.address_line_3,',',' ')) street2,
218 upper(replace(loc.town_or_city,',',' ')) city, --removed commas bug 13905482
219 upper(replace(loc.region_1,',',' ')) county, --removed commas bug 13905482
220 loc.region_2 state,
221 loc.postal_code zip_code,
222 upper(replace(replace(hoi2.org_information2,'-',''),',','')) naics, --removed hyphens and commas bug 13905482
223 upper(replace(replace(hoi2.org_information4,'-',''),',','')) duns, --removed hyphens and commas bug 13905482
224 upper(replace(replace(hoi2.org_information3,'-',''),',','')) ein --removed hyphens and commas bug 13905482
225 from per_gen_hierarchy_nodes pgn,
226 hr_all_organization_units hou,
227 hr_organization_information hoi1,
228 hr_organization_information hoi2,
229 hr_locations_all loc
230 where pgn.hierarchy_version_id = p_hierarchy_version_id
231 and pgn.node_type = 'PAR'
232 and pgn.entity_id = hou.organization_id
233 and pgn.business_group_id = p_business_group_id
234 and hoi1.org_information_context = 'VETS_Spec'
235 and hoi1.organization_id = hou.organization_id
236 and hoi2.org_information_context = 'VETS_EEO_Dup'
237 and hoi2.organization_id = hou.organization_id
238 and hou.location_id = loc.location_id(+);
239 --
240
241 cursor c2 is
242 select
243 upper(replace(hoi2.org_information17,',',' ')) contact_name --converted to upper case, bug 13905482
244 --,upper(replace(hoi2.org_information18,',',' ')) contact_telnum --removed commas bug 13905482
245 ,upper(replace(translate(hoi2.org_information18,'''`;"|\-+=_#$%^&*@!~:<>?/()[]{},.',' '),' ','')) contact_telnum -- removed special characters
246 ,upper(replace(hoi2.org_information20,',',' ')) contact_email --converted to upper case, bug 13905482
247 from
248 hr_organization_information hoi1
249 ,hr_locations_all cloc
250 ,hr_organization_units hou
251 ,hr_organization_information hoi2
252 ,per_gen_hierarchy_nodes pgn
253 where
254 hoi1.organization_id = pgn.entity_id
255 and hoi1.org_information_context = 'VETS_Spec'
256 and hoi1.organization_id = hou.organization_id
257 and hou.location_id = cloc.location_id
258 and hoi2.organization_id = p_business_group_id
259 and hoi2.org_information_context = 'EEO_REPORT'
260 and pgn.hierarchy_version_id = p_hierarchy_version_id
261 and pgn.node_type = 'PAR' ;
262
263 begin
264 --
265 open c1;
266 --
267 fetch c1 into l_org_rec.company_number,
268 l_org_rec.contractor_type,
269 l_org_rec.parent_company,
270 l_org_rec.street,
271 l_org_rec.street2,
272 l_org_rec.city,
273 l_org_rec.county,
274 l_org_rec.state,
275 l_org_rec.zip_code,
276 l_org_rec.naics,
277 l_org_rec.duns,
278 l_org_rec.ein;
279 --
280 close c1;
281 --
282 --
283 open c2;
284 --
285 fetch c2 into l_org_rec.contact,
286 l_org_rec.telephone,
287 l_org_rec.email;
288 --
289 close c2;
290 --
291
292 end set_org_details;
293
294 procedure get_min_max(p_value1 in number,
295 p_value2 in number,
296 p_value3 in number,
297 p_value4 in number,
298 p_value5 in number,
299 p_value6 in number,
300 p_value7 in number,
301 p_value8 in number,
302 p_value9 in number,
303 p_value10 in number,
304 p_value11 in number,
305 p_value12 in number,
306 p_min_out out nocopy number,
307 p_max_out out nocopy number) is
308 --
309 l_min number := p_value1;
310 l_max number := p_value1;
311 --
312 begin
313 --
314 if l_max < p_value2 then
315 --
316 l_max := p_value2;
317 --
318 elsif l_min > p_value2 then
319 --
320 l_min := p_value2;
321 --
322 end if;
323 --
324 if l_max < p_value3 then
325 --
326 l_max := p_value3;
327 --
328 elsif l_min > p_value3 then
329 --
330 l_min := p_value3;
331 --
332 end if;
333 --
334 if l_max < p_value4 then
335 --
336 l_max := p_value4;
337 --
338 elsif l_min > p_value4 then
339 --
340 l_min := p_value4;
341 --
342 end if;
343 --
344 if l_max < p_value5 then
345 --
346 l_max := p_value5;
347 --
348 elsif l_min > p_value5 then
349 --
350 l_min := p_value5;
351 --
352 end if;
353 --
354 if l_max < p_value6 then
355 --
356 l_max := p_value6;
357 --
358 elsif l_min > p_value6 then
359 --
360 l_min := p_value6;
361 --
362 end if;
363 --
364 if l_max < p_value7 then
365 --
366 l_max := p_value7;
367 --
368 elsif l_min > p_value7 then
369 --
370 l_min := p_value7;
371 --
372 end if;
373 --
374 if l_max < p_value8 then
375 --
376 l_max := p_value8;
377 --
378 elsif l_min > p_value8 then
379 --
380 l_min := p_value8;
381 --
382 end if;
383 --
384 if l_max < p_value9 then
385 --
386 l_max := p_value9;
387 --
388 elsif l_min > p_value9 then
389 --
390 l_min := p_value9;
391 --
392 end if;
393 --
394 if l_max < p_value10 then
395 --
396 l_max := p_value10;
397 --
398 elsif l_min > p_value10 then
399 --
400 l_min := p_value10;
401 --
402 end if;
403 --
404 if l_max < p_value11 then
405 --
406 l_max := p_value11;
407 --
408 elsif l_min > p_value11 then
409 --
410 l_min := p_value11;
411 --
412 end if;
413 --
414 if l_max < p_value12 then
415 --
416 l_max := p_value12;
417 --
418 elsif l_min > p_value12 then
419 --
420 l_min := p_value12;
421 --
422 end if;
423 --
424 p_max_out := l_max;
425 p_min_out := l_min;
426 --
427 end get_min_max;
428 --
429 procedure write_consolidated_record is
430 --
431 l_string varchar2(2000);
432 --
433 begin
434 --
435 -- Set l_consol_rec.min_count and l_consol_rec.max_count
436 --
437 get_min_max(p_value1 => l_month_rec.jan,
438 p_value2 => l_month_rec.feb,
439 p_value3 => l_month_rec.mar,
440 p_value4 => l_month_rec.apr,
441 p_value5 => l_month_rec.may,
442 p_value6 => l_month_rec.jun,
443 p_value7 => l_month_rec.jul,
444 p_value8 => l_month_rec.aug,
445 p_value9 => l_month_rec.sep,
446 p_value10 => l_month_rec.oct,
447 p_value11 => l_month_rec.nov,
448 p_value12 => l_month_rec.dec,
449 p_min_out => l_consol_rec.min_count,
450 p_max_out => l_consol_rec.max_count);
451 --
452 l_string := substr(l_org_rec.company_number,1,20)||','||
453 l_org_rec.contractor_type||','||
454 'MSC'||','||
455 to_char(l_org_rec.number_of_estabs)||','||
456 l_org_rec.ending_period||','||
457 substr(l_org_rec.parent_company,1,40)||','||
458 substr(l_org_rec.street,1,40)||','||
459 substr(l_org_rec.street2,1,40)||','||
460 substr(l_org_rec.city,1,20)||','||
461 substr(l_org_rec.county,1,20)||','||
462 substr(l_org_rec.state,1,20)||','||
463 substr(l_org_rec.zip_code,1,10)||','||
464 substr(l_org_rec.contact,1,40)||','||
465 substr(l_org_rec.telephone,1,20)||','||
466 substr(l_org_rec.email,1,40)||','||
467 substr(l_consol_rec.reporting_name,1,40)||','||
468 substr(l_consol_rec.street,1,40)||','||
469 substr(l_consol_rec.street2,1,40)||','||
470 substr(l_consol_rec.city,1,20)||','||
471 substr(l_consol_rec.county,1,20)||','||
472 substr(l_consol_rec.state,1,20)||','||
473 substr(l_consol_rec.zip_code,1,10)||','||
474 substr(l_org_rec.naics,1,10)||','||
475 substr(l_org_rec.duns,1,20)||','||
476 substr(l_org_rec.ein,1,20)||','||
477 nvl(l_consol_rec.l1_total,0)||','||
478 nvl(l_consol_rec.l2_total,0)||','||
479 nvl(l_consol_rec.l3_total,0)||','||
480 nvl(l_consol_rec.l4_total,0)||','||
481 nvl(l_consol_rec.l5_total,0)||','||
482 nvl(l_consol_rec.l6_total,0)||','||
483 nvl(l_consol_rec.l7_total,0)||','||
484 nvl(l_consol_rec.l8_total,0)||','||
485 nvl(l_consol_rec.l9_total,0)||','||
486 nvl(l_consol_rec.l10_total,0)||','||
487 nvl(l_consol_rec.l_grand_total,0)||','||
488 nvl(l_consol_rec.m1_total,0)||','||
489 nvl(l_consol_rec.m2_total,0)||','||
490 nvl(l_consol_rec.m3_total,0)||','||
491 nvl(l_consol_rec.m4_total,0)||','||
492 nvl(l_consol_rec.m5_total,0)||','||
493 nvl(l_consol_rec.m6_total,0)||','||
494 nvl(l_consol_rec.m7_total,0)||','||
495 nvl(l_consol_rec.m8_total,0)||','||
496 nvl(l_consol_rec.m9_total,0)||','||
497 nvl(l_consol_rec.m10_total,0)||','||
498 nvl(l_consol_rec.m_grand_total,0)||','||
499 nvl(l_consol_rec.n1_total,0)||','||
500 nvl(l_consol_rec.n2_total,0)||','||
501 nvl(l_consol_rec.n3_total,0)||','||
502 nvl(l_consol_rec.n4_total,0)||','||
503 nvl(l_consol_rec.n5_total,0)||','||
504 nvl(l_consol_rec.n6_total,0)||','||
505 nvl(l_consol_rec.n7_total,0)||','||
506 nvl(l_consol_rec.n8_total,0)||','||
507 nvl(l_consol_rec.n9_total,0)||','||
508 nvl(l_consol_rec.n10_total,0)||','||
509 nvl(l_consol_rec.n_grand_total,0)||','||
510 nvl(l_consol_rec.o1_total,0)||','||
511 nvl(l_consol_rec.o2_total,0)||','||
512 nvl(l_consol_rec.o3_total,0)||','||
513 nvl(l_consol_rec.o4_total,0)||','||
514 nvl(l_consol_rec.o5_total,0)||','||
515 nvl(l_consol_rec.o6_total,0)||','||
516 nvl(l_consol_rec.o7_total,0)||','||
517 nvl(l_consol_rec.o8_total,0)||','||
518 nvl(l_consol_rec.o9_total,0)||','||
519 nvl(l_consol_rec.o10_total,0)||','||
520 nvl(l_consol_rec.o_grand_total,0)||','||
521 nvl(l_consol_rec.p1_total,0)||','||
522 nvl(l_consol_rec.p2_total,0)||','||
523 nvl(l_consol_rec.p3_total,0)||','||
524 nvl(l_consol_rec.p4_total,0)||','||
525 nvl(l_consol_rec.p5_total,0)||','||
526 nvl(l_consol_rec.p6_total,0)||','||
527 nvl(l_consol_rec.p7_total,0)||','||
528 nvl(l_consol_rec.p8_total,0)||','||
529 nvl(l_consol_rec.p9_total,0)||','||
530 nvl(l_consol_rec.p10_total,0)||','||
531 nvl(l_consol_rec.p_grand_total,0)||','||
532 nvl(l_consol_rec.q1_total,0)||','||
533 nvl(l_consol_rec.q2_total,0)||','||
534 nvl(l_consol_rec.q3_total,0)||','||
535 nvl(l_consol_rec.q4_total,0)||','||
536 nvl(l_consol_rec.q5_total,0)||','||
537 nvl(l_consol_rec.q6_total,0)||','||
538 nvl(l_consol_rec.q7_total,0)||','||
539 nvl(l_consol_rec.q8_total,0)||','||
540 nvl(l_consol_rec.q9_total,0)||','||
541 nvl(l_consol_rec.q10_total,0)||','||
542 nvl(l_consol_rec.q_grand_total,0)||','||
543 nvl(l_consol_rec.r1_total,0)||','||
544 nvl(l_consol_rec.r2_total,0)||','||
545 nvl(l_consol_rec.r3_total,0)||','||
546 nvl(l_consol_rec.r4_total,0)||','||
547 nvl(l_consol_rec.r5_total,0)||','||
548 nvl(l_consol_rec.r6_total,0)||','||
549 nvl(l_consol_rec.r7_total,0)||','||
550 nvl(l_consol_rec.r8_total,0)||','||
551 nvl(l_consol_rec.r9_total,0)||','||
552 nvl(l_consol_rec.r10_total,0)||','||
553 nvl(l_consol_rec.r_grand_total,0)||','||
554 nvl(l_consol_rec.s1_total,0)||','||
555 nvl(l_consol_rec.s2_total,0)||','||
556 nvl(l_consol_rec.s3_total,0)||','||
557 nvl(l_consol_rec.s4_total,0)||','||
558 nvl(l_consol_rec.s5_total,0)||','||
559 nvl(l_consol_rec.s6_total,0)||','||
560 nvl(l_consol_rec.s7_total,0)||','||
561 nvl(l_consol_rec.s8_total,0)||','||
562 nvl(l_consol_rec.s9_total,0)||','||
563 nvl(l_consol_rec.s10_total,0)||','||
564 nvl(l_consol_rec.s_grand_total,0)||','||
565 nvl(l_consol_rec.t1_total,0)||','||
566 nvl(l_consol_rec.t2_total,0)||','||
567 nvl(l_consol_rec.t3_total,0)||','||
568 nvl(l_consol_rec.t4_total,0)||','||
569 nvl(l_consol_rec.t5_total,0)||','||
570 nvl(l_consol_rec.t6_total,0)||','||
571 nvl(l_consol_rec.t7_total,0)||','||
572 nvl(l_consol_rec.t8_total,0)||','||
573 nvl(l_consol_rec.t9_total,0)||','||
574 nvl(l_consol_rec.t10_total,0)||','||
575 nvl(l_consol_rec.t_grand_total,0)||','||
576 nvl(l_consol_rec.u1_total,0)||','||
577 nvl(l_consol_rec.u2_total,0)||','||
578 nvl(l_consol_rec.u3_total,0)||','||
579 nvl(l_consol_rec.u4_total,0)||','||
580 nvl(l_consol_rec.u5_total,0)||','||
581 nvl(l_consol_rec.u6_total,0)||','||
582 nvl(l_consol_rec.u7_total,0)||','||
583 nvl(l_consol_rec.u8_total,0)||','||
584 nvl(l_consol_rec.u9_total,0)||','||
585 nvl(l_consol_rec.u10_total,0)||','||
586 nvl(l_consol_rec.u_grand_total,0)||','||
587 nvl(l_consol_rec.max_count,0)||','||
588 nvl(l_consol_rec.min_count,0);
589 --
590 fnd_file.put_line(fnd_file.OUTPUT, l_string);
591 --
592 l_org_rec.number_of_estabs := null;
593 --
594 end write_consolidated_record;
595 --
596 procedure write_establishment_record is
597 --
598 l_string varchar2(2000);
599 l_proc varchar2(40) := 'write_establishment_record';
600 l_number_of_estabs NUMBER := NULL;
601 --
602 begin
603 --
604 -- Set form type
605 --
606 hr_utility.set_location(l_proc,10);
607 hr_utility.trace('l_estab_rec.max_count : ' || l_estab_rec.max_count);
608 if l_estab_rec.hq = 'Y' and
609 nvl(l_org_rec.form_type,'-1') <> 'S' then
610 --
611 hr_utility.set_location(l_proc,20);
612 l_org_rec.form_type := 'MHQ';
613 --
614 end if;
615 --
616 if l_estab_rec.hq = 'N' and
617 nvl(l_org_rec.form_type,'-1') <> 'S' and
618 (
619 -- l_estab_rec.max_count >= 50
620 l_tot_emps >= 50
621 or l_all_estab = 'N'
622 ) then
623 --
624 hr_utility.set_location(l_proc,30);
625 l_org_rec.form_type := 'MHL';
626 --
627 end if;
628 --
629 -- Set totals
630 --
631 l_estab_rec.l_grand_total := nvl(l_estab_rec.l1_total,0)+
632 nvl(l_estab_rec.l2_total,0)+
633 nvl(l_estab_rec.l3_total,0)+
634 nvl(l_estab_rec.l4_total,0)+
635 nvl(l_estab_rec.l5_total,0)+
636 nvl(l_estab_rec.l6_total,0)+
637 nvl(l_estab_rec.l7_total,0)+
638 nvl(l_estab_rec.l8_total,0)+
639 nvl(l_estab_rec.l9_total,0)+
640 nvl(l_estab_rec.l10_total,0);
641 --
642 l_estab_rec.m_grand_total := nvl(l_estab_rec.m1_total,0)+
643 nvl(l_estab_rec.m2_total,0)+
644 nvl(l_estab_rec.m3_total,0)+
645 nvl(l_estab_rec.m4_total,0)+
646 nvl(l_estab_rec.m5_total,0)+
647 nvl(l_estab_rec.m6_total,0)+
648 nvl(l_estab_rec.m7_total,0)+
649 nvl(l_estab_rec.m8_total,0)+
650 nvl(l_estab_rec.m9_total,0)+
651 nvl(l_estab_rec.m10_total,0);
652 --
653 l_estab_rec.n_grand_total := nvl(l_estab_rec.n1_total,0)+
654 nvl(l_estab_rec.n2_total,0)+
655 nvl(l_estab_rec.n3_total,0)+
656 nvl(l_estab_rec.n4_total,0)+
657 nvl(l_estab_rec.n5_total,0)+
658 nvl(l_estab_rec.n6_total,0)+
659 nvl(l_estab_rec.n7_total,0)+
660 nvl(l_estab_rec.n8_total,0)+
661 nvl(l_estab_rec.n9_total,0)+
662 nvl(l_estab_rec.n10_total,0);
663 --
664 l_estab_rec.o_grand_total := nvl(l_estab_rec.o1_total,0)+
665 nvl(l_estab_rec.o2_total,0)+
666 nvl(l_estab_rec.o3_total,0)+
667 nvl(l_estab_rec.o4_total,0)+
668 nvl(l_estab_rec.o5_total,0)+
669 nvl(l_estab_rec.o6_total,0)+
670 nvl(l_estab_rec.o7_total,0)+
671 nvl(l_estab_rec.o8_total,0)+
672 nvl(l_estab_rec.o9_total,0)+
673 nvl(l_estab_rec.o10_total,0);
674 --
675 l_estab_rec.p_grand_total := nvl(l_estab_rec.p1_total,0)+
676 nvl(l_estab_rec.p2_total,0)+
677 nvl(l_estab_rec.p3_total,0)+
678 nvl(l_estab_rec.p4_total,0)+
679 nvl(l_estab_rec.p5_total,0)+
680 nvl(l_estab_rec.p6_total,0)+
684 nvl(l_estab_rec.p10_total,0);
681 nvl(l_estab_rec.p7_total,0)+
682 nvl(l_estab_rec.p8_total,0)+
683 nvl(l_estab_rec.p9_total,0)+
685 --
686 l_estab_rec.q_grand_total := nvl(l_estab_rec.q1_total,0)+
687 nvl(l_estab_rec.q2_total,0)+
688 nvl(l_estab_rec.q3_total,0)+
689 nvl(l_estab_rec.q4_total,0)+
690 nvl(l_estab_rec.q5_total,0)+
691 nvl(l_estab_rec.q6_total,0)+
692 nvl(l_estab_rec.q7_total,0)+
693 nvl(l_estab_rec.q8_total,0)+
694 nvl(l_estab_rec.q9_total,0)+
695 nvl(l_estab_rec.q10_total,0);
696 --
697 l_estab_rec.r_grand_total := nvl(l_estab_rec.r1_total,0)+
698 nvl(l_estab_rec.r2_total,0)+
699 nvl(l_estab_rec.r3_total,0)+
700 nvl(l_estab_rec.r4_total,0)+
701 nvl(l_estab_rec.r5_total,0)+
702 nvl(l_estab_rec.r6_total,0)+
703 nvl(l_estab_rec.r7_total,0)+
704 nvl(l_estab_rec.r8_total,0)+
705 nvl(l_estab_rec.r9_total,0)+
706 nvl(l_estab_rec.r10_total,0);
707 --
708 l_estab_rec.s_grand_total := nvl(l_estab_rec.s1_total,0)+
709 nvl(l_estab_rec.s2_total,0)+
710 nvl(l_estab_rec.s3_total,0)+
711 nvl(l_estab_rec.s4_total,0)+
712 nvl(l_estab_rec.s5_total,0)+
713 nvl(l_estab_rec.s6_total,0)+
714 nvl(l_estab_rec.s7_total,0)+
715 nvl(l_estab_rec.s8_total,0)+
716 nvl(l_estab_rec.s9_total,0)+
717 nvl(l_estab_rec.s10_total,0);
718 --
719 l_estab_rec.t_grand_total := nvl(l_estab_rec.t1_total,0)+
720 nvl(l_estab_rec.t2_total,0)+
721 nvl(l_estab_rec.t3_total,0)+
722 nvl(l_estab_rec.t4_total,0)+
723 nvl(l_estab_rec.t5_total,0)+
724 nvl(l_estab_rec.t6_total,0)+
725 nvl(l_estab_rec.t7_total,0)+
726 nvl(l_estab_rec.t8_total,0)+
727 nvl(l_estab_rec.t9_total,0)+
728 nvl(l_estab_rec.t10_total,0);
729 --
730 l_estab_rec.u_grand_total := nvl(l_estab_rec.u1_total,0)+
731 nvl(l_estab_rec.u2_total,0)+
732 nvl(l_estab_rec.u3_total,0)+
733 nvl(l_estab_rec.u4_total,0)+
734 nvl(l_estab_rec.u5_total,0)+
735 nvl(l_estab_rec.u6_total,0)+
736 nvl(l_estab_rec.u7_total,0)+
737 nvl(l_estab_rec.u8_total,0)+
738 nvl(l_estab_rec.u9_total,0)+
739 nvl(l_estab_rec.u10_total,0);
740
741 --
742 -- This means we are dealing with a state consolidated report
743 -- which means we have to do some clever processing
744 --
745 if l_org_rec.form_type is null then
746 --
747 -- we need to add the new totals to the consolidate totals
748 --
749 l_consol_rec.state := l_estab_rec.state;
750 l_consol_rec.max_count := nvl(l_consol_rec.max_count,0)+
751 l_estab_rec.max_count;
752 l_consol_rec.min_count := nvl(l_consol_rec.min_count,0)+
753 l_estab_rec.min_count;
754 l_consol_rec.l_grand_total := nvl(l_consol_rec.l_grand_total,0)+
755 l_estab_rec.l_grand_total;
756 l_consol_rec.m_grand_total := nvl(l_consol_rec.m_grand_total,0)+
757 l_estab_rec.m_grand_total;
758 l_consol_rec.n_grand_total := nvl(l_consol_rec.n_grand_total,0)+
759 l_estab_rec.n_grand_total;
760 l_consol_rec.o_grand_total := nvl(l_consol_rec.o_grand_total,0)+
761 l_estab_rec.o_grand_total;
762 l_consol_rec.p_grand_total := nvl(l_consol_rec.p_grand_total,0)+
763 l_estab_rec.p_grand_total;
764 l_consol_rec.q_grand_total := nvl(l_consol_rec.q_grand_total,0)+
765 l_estab_rec.q_grand_total;
766 l_consol_rec.r_grand_total := nvl(l_consol_rec.r_grand_total,0)+
767 l_estab_rec.r_grand_total;
768 l_consol_rec.s_grand_total := nvl(l_consol_rec.s_grand_total,0)+
769 l_estab_rec.s_grand_total;
770 l_consol_rec.t_grand_total := nvl(l_consol_rec.t_grand_total,0)+
771 l_estab_rec.t_grand_total;
772 l_consol_rec.u_grand_total := nvl(l_consol_rec.u_grand_total,0)+
773 l_estab_rec.u_grand_total;
774
775 l_consol_rec.l1_total := nvl(l_consol_rec.l1_total,0)+
776 nvl(l_estab_rec.l1_total,0);
777 l_consol_rec.l2_total := nvl(l_consol_rec.l2_total,0)+
778 nvl(l_estab_rec.l2_total,0);
779 l_consol_rec.l3_total := nvl(l_consol_rec.l3_total,0)+
780 nvl(l_estab_rec.l3_total,0);
781 l_consol_rec.l4_total := nvl(l_consol_rec.l4_total,0)+
782 nvl(l_estab_rec.l4_total,0);
783 l_consol_rec.l5_total := nvl(l_consol_rec.l5_total,0)+
784 nvl(l_estab_rec.l5_total,0);
785 l_consol_rec.l6_total := nvl(l_consol_rec.l6_total,0)+
786 nvl(l_estab_rec.l6_total,0);
787 l_consol_rec.l7_total := nvl(l_consol_rec.l7_total,0)+
788 nvl(l_estab_rec.l7_total,0);
789 l_consol_rec.l8_total := nvl(l_consol_rec.l8_total,0)+
790 nvl(l_estab_rec.l8_total,0);
791 l_consol_rec.l9_total := nvl(l_consol_rec.l9_total,0)+
792 nvl(l_estab_rec.l9_total,0);
793 l_consol_rec.l10_total := nvl(l_consol_rec.l10_total,0)+
794 nvl(l_estab_rec.l10_total,0);
795
796
797 l_consol_rec.m1_total := nvl(l_consol_rec.m1_total,0)+
798 nvl(l_estab_rec.m1_total,0);
799 l_consol_rec.m2_total := nvl(l_consol_rec.m2_total,0)+
800 nvl(l_estab_rec.m2_total,0);
801 l_consol_rec.m3_total := nvl(l_consol_rec.m3_total,0)+
802 nvl(l_estab_rec.m3_total,0);
803 l_consol_rec.m4_total := nvl(l_consol_rec.m4_total,0)+
804 nvl(l_estab_rec.m4_total,0);
805 l_consol_rec.m5_total := nvl(l_consol_rec.m5_total,0)+
806 nvl(l_estab_rec.m5_total,0);
807 l_consol_rec.m6_total := nvl(l_consol_rec.m6_total,0)+
808 nvl(l_estab_rec.m6_total,0);
809 l_consol_rec.m7_total := nvl(l_consol_rec.m7_total,0)+
810 nvl(l_estab_rec.m7_total,0);
811 l_consol_rec.m8_total := nvl(l_consol_rec.m8_total,0)+
812 nvl(l_estab_rec.m8_total,0);
813 l_consol_rec.m9_total := nvl(l_consol_rec.m9_total,0)+
814 nvl(l_estab_rec.m9_total,0);
815 l_consol_rec.m10_total := nvl(l_consol_rec.m10_total,0)+
816 nvl(l_estab_rec.m10_total,0);
817
818
819 l_consol_rec.n1_total := nvl(l_consol_rec.n1_total,0)+
820 nvl(l_estab_rec.n1_total,0);
821 l_consol_rec.n2_total := nvl(l_consol_rec.n2_total,0)+
822 nvl(l_estab_rec.n2_total,0);
823 l_consol_rec.n3_total := nvl(l_consol_rec.n3_total,0)+
824 nvl(l_estab_rec.n3_total,0);
825 l_consol_rec.n4_total := nvl(l_consol_rec.n4_total,0)+
826 nvl(l_estab_rec.n4_total,0);
827 l_consol_rec.n5_total := nvl(l_consol_rec.n5_total,0)+
828 nvl(l_estab_rec.n5_total,0);
829 l_consol_rec.n6_total := nvl(l_consol_rec.n6_total,0)+
830 nvl(l_estab_rec.n6_total,0);
831 l_consol_rec.n7_total := nvl(l_consol_rec.n7_total,0)+
832 nvl(l_estab_rec.n7_total,0);
833 l_consol_rec.n8_total := nvl(l_consol_rec.n8_total,0)+
834 nvl(l_estab_rec.n8_total,0);
835 l_consol_rec.n9_total := nvl(l_consol_rec.n9_total,0)+
836 nvl(l_estab_rec.n9_total,0);
837 l_consol_rec.n10_total := nvl(l_consol_rec.n10_total,0)+
838 nvl(l_estab_rec.n10_total,0);
839
840
841 l_consol_rec.o1_total := nvl(l_consol_rec.o1_total,0)+
842 nvl(l_estab_rec.o1_total,0);
843 l_consol_rec.o2_total := nvl(l_consol_rec.o2_total,0)+
844 nvl(l_estab_rec.o2_total,0);
845 l_consol_rec.o3_total := nvl(l_consol_rec.o3_total,0)+
846 nvl(l_estab_rec.o3_total,0);
847 l_consol_rec.o4_total := nvl(l_consol_rec.o4_total,0)+
848 nvl(l_estab_rec.o4_total,0);
849 l_consol_rec.o5_total := nvl(l_consol_rec.o5_total,0)+
850 nvl(l_estab_rec.o5_total,0);
851 l_consol_rec.o6_total := nvl(l_consol_rec.o6_total,0)+
852 nvl(l_estab_rec.o6_total,0);
853 l_consol_rec.o7_total := nvl(l_consol_rec.o7_total,0)+
854 nvl(l_estab_rec.o7_total,0);
855 l_consol_rec.o8_total := nvl(l_consol_rec.o8_total,0)+
856 nvl(l_estab_rec.o8_total,0);
857 l_consol_rec.o9_total := nvl(l_consol_rec.o9_total,0)+
858 nvl(l_estab_rec.o9_total,0);
859 l_consol_rec.o10_total := nvl(l_consol_rec.o10_total,0)+
860 nvl(l_estab_rec.o10_total,0);
861
862
863 l_consol_rec.p1_total := nvl(l_consol_rec.p1_total,0)+
864 nvl(l_estab_rec.p1_total,0);
865 l_consol_rec.p2_total := nvl(l_consol_rec.p2_total,0)+
866 nvl(l_estab_rec.p2_total,0);
867 l_consol_rec.p3_total := nvl(l_consol_rec.p3_total,0)+
868 nvl(l_estab_rec.p3_total,0);
869 l_consol_rec.p4_total := nvl(l_consol_rec.p4_total,0)+
870 nvl(l_estab_rec.p4_total,0);
871 l_consol_rec.p5_total := nvl(l_consol_rec.p5_total,0)+
872 nvl(l_estab_rec.p5_total,0);
873 l_consol_rec.p6_total := nvl(l_consol_rec.p6_total,0)+
874 nvl(l_estab_rec.p6_total,0);
875 l_consol_rec.p7_total := nvl(l_consol_rec.p7_total,0)+
876 nvl(l_estab_rec.p7_total,0);
877 l_consol_rec.p8_total := nvl(l_consol_rec.p8_total,0)+
878 nvl(l_estab_rec.p8_total,0);
879 l_consol_rec.p9_total := nvl(l_consol_rec.p9_total,0)+
880 nvl(l_estab_rec.p9_total,0);
881 l_consol_rec.p10_total := nvl(l_consol_rec.p10_total,0)+
882 nvl(l_estab_rec.p10_total,0);
883
884
885 l_consol_rec.q1_total := nvl(l_consol_rec.q1_total,0)+
886 nvl(l_estab_rec.q1_total,0);
887 l_consol_rec.q2_total := nvl(l_consol_rec.q2_total,0)+
888 nvl(l_estab_rec.q2_total,0);
889 l_consol_rec.q3_total := nvl(l_consol_rec.q3_total,0)+
890 nvl(l_estab_rec.q3_total,0);
891 l_consol_rec.q4_total := nvl(l_consol_rec.q4_total,0)+
892 nvl(l_estab_rec.q4_total,0);
893 l_consol_rec.q5_total := nvl(l_consol_rec.q5_total,0)+
894 nvl(l_estab_rec.q5_total,0);
895 l_consol_rec.q6_total := nvl(l_consol_rec.q6_total,0)+
896 nvl(l_estab_rec.q6_total,0);
897 l_consol_rec.q7_total := nvl(l_consol_rec.q7_total,0)+
898 nvl(l_estab_rec.q7_total,0);
899 l_consol_rec.q8_total := nvl(l_consol_rec.q8_total,0)+
900 nvl(l_estab_rec.q8_total,0);
901 l_consol_rec.q9_total := nvl(l_consol_rec.q9_total,0)+
902 nvl(l_estab_rec.q9_total,0);
903 l_consol_rec.q10_total := nvl(l_consol_rec.q10_total,0)+
904 nvl(l_estab_rec.q10_total,0);
905
906
907 l_consol_rec.r1_total := nvl(l_consol_rec.r1_total,0)+
908 nvl(l_estab_rec.r1_total,0);
909 l_consol_rec.r2_total := nvl(l_consol_rec.r2_total,0)+
910 nvl(l_estab_rec.r2_total,0);
911 l_consol_rec.r3_total := nvl(l_consol_rec.r3_total,0)+
912 nvl(l_estab_rec.r3_total,0);
913 l_consol_rec.r4_total := nvl(l_consol_rec.r4_total,0)+
914 nvl(l_estab_rec.r4_total,0);
915 l_consol_rec.r5_total := nvl(l_consol_rec.r5_total,0)+
916 nvl(l_estab_rec.r5_total,0);
917 l_consol_rec.r6_total := nvl(l_consol_rec.r6_total,0)+
918 nvl(l_estab_rec.r6_total,0);
919 l_consol_rec.r7_total := nvl(l_consol_rec.r7_total,0)+
920 nvl(l_estab_rec.r7_total,0);
921 l_consol_rec.r8_total := nvl(l_consol_rec.r8_total,0)+
922 nvl(l_estab_rec.r8_total,0);
923 l_consol_rec.r9_total := nvl(l_consol_rec.r9_total,0)+
924 nvl(l_estab_rec.r9_total,0);
925 l_consol_rec.r10_total := nvl(l_consol_rec.r10_total,0)+
926 nvl(l_estab_rec.r10_total,0);
927
928
929 l_consol_rec.s1_total := nvl(l_consol_rec.s1_total,0)+
930 nvl(l_estab_rec.s1_total,0);
931 l_consol_rec.s2_total := nvl(l_consol_rec.s2_total,0)+
932 nvl(l_estab_rec.s2_total,0);
933 l_consol_rec.s3_total := nvl(l_consol_rec.s3_total,0)+
934 nvl(l_estab_rec.s3_total,0);
935 l_consol_rec.s4_total := nvl(l_consol_rec.s4_total,0)+
936 nvl(l_estab_rec.s4_total,0);
937 l_consol_rec.s5_total := nvl(l_consol_rec.s5_total,0)+
938 nvl(l_estab_rec.s5_total,0);
939 l_consol_rec.s6_total := nvl(l_consol_rec.s6_total,0)+
940 nvl(l_estab_rec.s6_total,0);
941 l_consol_rec.s7_total := nvl(l_consol_rec.s7_total,0)+
942 nvl(l_estab_rec.s7_total,0);
943 l_consol_rec.s8_total := nvl(l_consol_rec.s8_total,0)+
944 nvl(l_estab_rec.s8_total,0);
945 l_consol_rec.s9_total := nvl(l_consol_rec.s9_total,0)+
946 nvl(l_estab_rec.s9_total,0);
947 l_consol_rec.s10_total := nvl(l_consol_rec.s10_total,0)+
948 nvl(l_estab_rec.s10_total,0);
949
953 nvl(l_estab_rec.t2_total,0);
950 l_consol_rec.t1_total := nvl(l_consol_rec.t1_total,0)+
951 nvl(l_estab_rec.t1_total,0);
952 l_consol_rec.t2_total := nvl(l_consol_rec.t2_total,0)+
954 l_consol_rec.t3_total := nvl(l_consol_rec.t3_total,0)+
955 nvl(l_estab_rec.t3_total,0);
956 l_consol_rec.t4_total := nvl(l_consol_rec.t4_total,0)+
957 nvl(l_estab_rec.t4_total,0);
958 l_consol_rec.t5_total := nvl(l_consol_rec.t5_total,0)+
959 nvl(l_estab_rec.t5_total,0);
960 l_consol_rec.t6_total := nvl(l_consol_rec.t6_total,0)+
961 nvl(l_estab_rec.t6_total,0);
962 l_consol_rec.t7_total := nvl(l_consol_rec.t7_total,0)+
963 nvl(l_estab_rec.t7_total,0);
964 l_consol_rec.t8_total := nvl(l_consol_rec.t8_total,0)+
965 nvl(l_estab_rec.t8_total,0);
966 l_consol_rec.t9_total := nvl(l_consol_rec.t9_total,0)+
967 nvl(l_estab_rec.t9_total,0);
968 l_consol_rec.t10_total := nvl(l_consol_rec.t10_total,0)+
969 nvl(l_estab_rec.t10_total,0);
970
971 l_consol_rec.u1_total := nvl(l_consol_rec.u1_total,0)+
972 nvl(l_estab_rec.u1_total,0);
973 l_consol_rec.u2_total := nvl(l_consol_rec.u2_total,0)+
974 nvl(l_estab_rec.u2_total,0);
975 l_consol_rec.u3_total := nvl(l_consol_rec.u3_total,0)+
976 nvl(l_estab_rec.u3_total,0);
977 l_consol_rec.u4_total := nvl(l_consol_rec.u4_total,0)+
978 nvl(l_estab_rec.u4_total,0);
979 l_consol_rec.u5_total := nvl(l_consol_rec.u5_total,0)+
980 nvl(l_estab_rec.u5_total,0);
981 l_consol_rec.u6_total := nvl(l_consol_rec.u6_total,0)+
982 nvl(l_estab_rec.u6_total,0);
983 l_consol_rec.u7_total := nvl(l_consol_rec.u7_total,0)+
984 nvl(l_estab_rec.u7_total,0);
985 l_consol_rec.u8_total := nvl(l_consol_rec.u8_total,0)+
986 nvl(l_estab_rec.u8_total,0);
987 l_consol_rec.u9_total := nvl(l_consol_rec.u9_total,0)+
988 nvl(l_estab_rec.u9_total,0);
989 l_consol_rec.u10_total := nvl(l_consol_rec.u10_total,0)+
990 nvl(l_estab_rec.u10_total,0);
991
992 --
993 l_org_rec.number_of_estabs := nvl(l_org_rec.number_of_estabs,0)+1;
994 return;
995 --
996 end if;
997 --
998 l_number_of_estabs := NULL;
999 --#10118692 start
1000 if (l_org_rec.form_type = 'MSC') then
1001 l_number_of_estabs := l_org_rec.number_of_estabs;
1002 end if;
1003 --#10118692 end
1004 --
1005 l_string := substr(l_org_rec.company_number,1,20)||','||
1006 l_org_rec.contractor_type||','||
1007 l_org_rec.form_type||','||
1008 NVL(to_char(l_number_of_estabs),'')||','|| --#10118692
1009 l_org_rec.ending_period||','||
1010 substr(l_org_rec.parent_company,1,40)||','||
1011 substr(l_org_rec.street,1,40)||','||
1012 substr(l_org_rec.street2,1,40)||','||
1013 substr(l_org_rec.city,1,20)||','||
1014 substr(l_org_rec.county,1,20)||','||
1015 substr(l_org_rec.state,1,20)||','||
1016 substr(l_org_rec.zip_code,1,10)||','||
1017 substr(l_org_rec.contact,1,40)||','||
1018 substr(l_org_rec.telephone,1,20)||','||
1019 substr(l_org_rec.email,1,40)||','||
1020 substr(l_estab_rec.reporting_name,1,40)||','||
1021 substr(l_estab_rec.street,1,40)||','||
1022 substr(l_estab_rec.street2,1,40)||','||
1023 substr(l_estab_rec.city,1,20)||','||
1024 substr(l_estab_rec.county,1,20)||','||
1025 substr(l_estab_rec.state,1,20)||','||
1026 substr(l_estab_rec.zip_code,1,10)||','||
1027 substr(nvl(l_estab_rec.naics,l_org_rec.naics),1,10)||','||
1028 substr(nvl(l_estab_rec.duns,l_org_rec.duns),1,20)||','||
1029 substr(nvl(l_estab_rec.ein,l_org_rec.ein),1,20)||','||
1030 nvl(l_estab_rec.l1_total,0)||','||
1031 nvl(l_estab_rec.l2_total,0)||','||
1032 nvl(l_estab_rec.l3_total,0)||','||
1033 nvl(l_estab_rec.l4_total,0)||','||
1034 nvl(l_estab_rec.l5_total,0)||','||
1035 nvl(l_estab_rec.l6_total,0)||','||
1036 nvl(l_estab_rec.l7_total,0)||','||
1037 nvl(l_estab_rec.l8_total,0)||','||
1038 nvl(l_estab_rec.l9_total,0)||','||
1039 nvl(l_estab_rec.l10_total,0)||','||
1040 nvl(l_estab_rec.l_grand_total,0)||','||
1041 nvl(l_estab_rec.m1_total,0)||','||
1042 nvl(l_estab_rec.m2_total,0)||','||
1043 nvl(l_estab_rec.m3_total,0)||','||
1044 nvl(l_estab_rec.m4_total,0)||','||
1045 nvl(l_estab_rec.m5_total,0)||','||
1046 nvl(l_estab_rec.m6_total,0)||','||
1047 nvl(l_estab_rec.m7_total,0)||','||
1048 nvl(l_estab_rec.m8_total,0)||','||
1049 nvl(l_estab_rec.m9_total,0)||','||
1050 nvl(l_estab_rec.m10_total,0)||','||
1051 nvl(l_estab_rec.m_grand_total,0)||','||
1052 nvl(l_estab_rec.n1_total,0)||','||
1053 nvl(l_estab_rec.n2_total,0)||','||
1054 nvl(l_estab_rec.n3_total,0)||','||
1055 nvl(l_estab_rec.n4_total,0)||','||
1056 nvl(l_estab_rec.n5_total,0)||','||
1057 nvl(l_estab_rec.n6_total,0)||','||
1058 nvl(l_estab_rec.n7_total,0)||','||
1059 nvl(l_estab_rec.n8_total,0)||','||
1060 nvl(l_estab_rec.n9_total,0)||','||
1061 nvl(l_estab_rec.n10_total,0)||','||
1062 nvl(l_estab_rec.n_grand_total,0)||','||
1063 nvl(l_estab_rec.o1_total,0)||','||
1064 nvl(l_estab_rec.o2_total,0)||','||
1065 nvl(l_estab_rec.o3_total,0)||','||
1066 nvl(l_estab_rec.o4_total,0)||','||
1067 nvl(l_estab_rec.o5_total,0)||','||
1068 nvl(l_estab_rec.o6_total,0)||','||
1069 nvl(l_estab_rec.o7_total,0)||','||
1070 nvl(l_estab_rec.o8_total,0)||','||
1071 nvl(l_estab_rec.o9_total,0)||','||
1072 nvl(l_estab_rec.o10_total,0)||','||
1073 nvl(l_estab_rec.o_grand_total,0)||','||
1074 nvl(l_estab_rec.p1_total,0)||','||
1075 nvl(l_estab_rec.p2_total,0)||','||
1076 nvl(l_estab_rec.p3_total,0)||','||
1077 nvl(l_estab_rec.p4_total,0)||','||
1078 nvl(l_estab_rec.p5_total,0)||','||
1079 nvl(l_estab_rec.p6_total,0)||','||
1080 nvl(l_estab_rec.p7_total,0)||','||
1081 nvl(l_estab_rec.p8_total,0)||','||
1082 nvl(l_estab_rec.p9_total,0)||','||
1083 nvl(l_estab_rec.p10_total,0)||','||
1084 nvl(l_estab_rec.p_grand_total,0)||','||
1085 nvl(l_estab_rec.q1_total,0)||','||
1086 nvl(l_estab_rec.q2_total,0)||','||
1087 nvl(l_estab_rec.q3_total,0)||','||
1088 nvl(l_estab_rec.q4_total,0)||','||
1089 nvl(l_estab_rec.q5_total,0)||','||
1090 nvl(l_estab_rec.q6_total,0)||','||
1091 nvl(l_estab_rec.q7_total,0)||','||
1092 nvl(l_estab_rec.q8_total,0)||','||
1093 nvl(l_estab_rec.q9_total,0)||','||
1094 nvl(l_estab_rec.q10_total,0)||','||
1095 nvl(l_estab_rec.q_grand_total,0)||','||
1096 nvl(l_estab_rec.r1_total,0)||','||
1097 nvl(l_estab_rec.r2_total,0)||','||
1098 nvl(l_estab_rec.r3_total,0)||','||
1099 nvl(l_estab_rec.r4_total,0)||','||
1100 nvl(l_estab_rec.r5_total,0)||','||
1101 nvl(l_estab_rec.r6_total,0)||','||
1102 nvl(l_estab_rec.r7_total,0)||','||
1103 nvl(l_estab_rec.r8_total,0)||','||
1104 nvl(l_estab_rec.r9_total,0)||','||
1105 nvl(l_estab_rec.r10_total,0)||','||
1106 nvl(l_estab_rec.r_grand_total,0)||','||
1107 nvl(l_estab_rec.s1_total,0)||','||
1108 nvl(l_estab_rec.s2_total,0)||','||
1109 nvl(l_estab_rec.s3_total,0)||','||
1110 nvl(l_estab_rec.s4_total,0)||','||
1111 nvl(l_estab_rec.s5_total,0)||','||
1112 nvl(l_estab_rec.s6_total,0)||','||
1113 nvl(l_estab_rec.s7_total,0)||','||
1114 nvl(l_estab_rec.s8_total,0)||','||
1115 nvl(l_estab_rec.s9_total,0)||','||
1116 nvl(l_estab_rec.s10_total,0)||','||
1117 nvl(l_estab_rec.s_grand_total,0)||','||
1118 nvl(l_estab_rec.t1_total,0)||','||
1119 nvl(l_estab_rec.t2_total,0)||','||
1120 nvl(l_estab_rec.t3_total,0)||','||
1121 nvl(l_estab_rec.t4_total,0)||','||
1122 nvl(l_estab_rec.t5_total,0)||','||
1123 nvl(l_estab_rec.t6_total,0)||','||
1124 nvl(l_estab_rec.t7_total,0)||','||
1125 nvl(l_estab_rec.t8_total,0)||','||
1126 nvl(l_estab_rec.t9_total,0)||','||
1127 nvl(l_estab_rec.t10_total,0)||','||
1128 nvl(l_estab_rec.t_grand_total,0)||','||
1129 nvl(l_estab_rec.u1_total,0)||','||
1130 nvl(l_estab_rec.u2_total,0)||','||
1131 nvl(l_estab_rec.u3_total,0)||','||
1132 nvl(l_estab_rec.u4_total,0)||','||
1133 nvl(l_estab_rec.u5_total,0)||','||
1134 nvl(l_estab_rec.u6_total,0)||','||
1135 nvl(l_estab_rec.u7_total,0)||','||
1136 nvl(l_estab_rec.u8_total,0)||','||
1137 nvl(l_estab_rec.u9_total,0)||','||
1138 nvl(l_estab_rec.u10_total,0)||','||
1139 nvl(l_estab_rec.u_grand_total,0)||','||
1140 nvl(l_estab_rec.max_count,0)||','||
1141 nvl(l_estab_rec.min_count,0);
1142 --
1143 l_org_rec.form_type := null;
1144 l_month_rec := l_month_rec_blank;
1145 --
1146 fnd_file.put_line
1147 (which => fnd_file.output,
1148 buff => l_string);
1149 --
1150 end;
1151 --
1152 procedure loop_through_establishments(p_hierarchy_version_id in number,
1153 p_business_group_id in number,
1154 p_start_date in date,
1155 p_end_date in date) is
1156
1157 l_hierarchy_node_id number;
1158
1159 -- orig cursor, cost of 9 as stands.
1160 cursor c1 is
1161 select upper(replace(hlei1.lei_information1,',',' ')) reporting_name,
1162 upper(replace(hlei1.lei_information2,',',' ')) unit_number,
1163 upper(replace(eloc.address_line_1,',',' ')||
1164 ' ') street,
1165 upper(
1166 replace(eloc.address_line_2,',',' ')||
1167 ' '||
1168 replace(eloc.address_line_3,',',' ')
1169 ) street2,
1170 upper(replace(eloc.town_or_city,',',' ')) city, --removed commas bug 13905482
1171 upper(replace(eloc.region_1,',',' ')) county, --removed commas bug 13905482
1172 upper(eloc.region_2) state,
1173 eloc.postal_code zip_code,
1174 upper(replace(replace(hlei2.lei_information4,'-',''),',','')) naics, --removed hyphens and commas bug 13905482
1175 upper(replace(replace(hlei2.lei_information2,'-',''),',','')) duns, --removed hyphens and commas bug 13905482
1176 upper(replace(replace(hlei2.lei_information6,'-',''),',','')) ein, --removed hyphens and commas bug 13905482
1177 hlei2.lei_information10 hq,
1178 eloc.location_id,
1179 pghn.hierarchy_node_id
1180 from per_gen_hierarchy_nodes pghn,
1181 hr_location_extra_info hlei1,
1182 hr_location_extra_info hlei2,
1183 hr_locations_all eloc
1184 where pghn.hierarchy_version_id = p_hierarchy_version_id
1185 and pghn.node_type = 'EST'
1186 and eloc.location_id = pghn.entity_id
1187 and hlei1.location_id = pghn.entity_id
1188 and hlei1.location_id = hlei2.location_id
1189 and hlei1.information_type = 'VETS-100 Specific Information'
1190 and hlei1.lei_information_category= 'VETS-100 Specific Information'
1191 and hlei2.information_type = 'Establishment Information'
1192 and hlei2.lei_information_category= 'Establishment Information'
1193 order by eloc.region_2,decode(hlei2.lei_information10,'Y',1,2);
1194
1195 l_c1 c1%rowtype;
1196
1197 cursor c2 is
1198
1199 select
1200 count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,'AFSMDIS',
1201 1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) no_dis_vets,
1202 count(decode(peo.per_information25,'OTEV',1,'OTEDV',1,'AFSMDISOP',1,'AFSMNSDISOP',
1203 1,'AFSMOP',1,'NSOP',1,'AFSMNSOP',1,'NSDISOP',1,null)) no_other_vets ,
1204 count(decode(peo.per_information25,'AFSM',1,'AFSMNSDIS',1,'AFSMDIS',1,'AFSMDISOP',
1205 1,'AFSMNSDISOP',1,'AFSMOP',1,'AFSMNSOP',1,'AFSMNS',1,null)) no_armed_vets,
1206 count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) no_not_vets,
1207 count(1) no_tot_vets, --#10118692
1208 hrl.lookup_code lookup_code
1209 from per_periods_of_service pds,
1210 per_all_people_f peo,
1211 per_all_assignments_f ass,
1212 hr_organization_information hoi1,
1213 hr_organization_information hoi2,
1214 per_jobs job,
1215 hr_lookups hrl
1216 where
1217 pds.date_start <= p_end_date
1218 and nvl(pds.actual_termination_date,p_end_date + 1) >= p_end_date
1219 and pds.person_id = ass.person_id
1220 and peo.person_id = ass.person_id
1221 and p_end_date between job.date_from and nvl(job.date_to, p_end_date)
1222 and job.job_information1 = hrl.lookup_code
1223 and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
1224 and ass.job_id = job.job_id
1225 and p_end_date between peo.effective_start_date and peo.effective_end_date
1226 and peo.current_employee_flag = 'Y'
1227 and ass.assignment_type = 'E'
1228 and ass.primary_flag = 'Y'
1229 and ass.effective_start_date =
1230 (select max(paf2.effective_start_date)
1231 from per_all_assignments_f paf2
1232 where paf2.person_id = ass.person_id
1233 and paf2.primary_flag = 'Y'
1234 and paf2.assignment_type = 'E'
1235 and paf2.effective_start_date <= p_end_date
1236 )
1237 and peo.effective_start_date =
1238 (select max(peo2.effective_start_date)
1239 from per_all_people_f peo2
1240 where peo2.person_id = peo.person_id
1241 and peo.current_employee_flag = 'Y'
1242 and peo2.effective_start_date < p_end_date
1243 )
1244 and to_char(ass.assignment_status_type_id) = hoi1.org_information1
1245 and hoi1.org_information_context = 'Reporting Statuses'
1246 and hoi1.organization_id = p_business_group_id
1247 and ass.employment_category = hoi2.org_information1
1248 and hoi2.organization_id = p_business_group_id
1249 and hoi2.org_information_context = 'Reporting Categories'
1250 and p_end_date between ass.effective_start_date and ass.effective_end_date
1251 and ass.location_id in
1252 (select entity_id
1253 from per_gen_hierarchy_nodes
1254 where hierarchy_version_id = p_hierarchy_version_id
1255 and (hierarchy_node_id = l_hierarchy_node_id
1256 or parent_hierarchy_node_id = l_hierarchy_node_id
1257 ))
1258 --start of bug 13905482
1259 and peo.business_group_id = p_business_group_id
1260 and ass.business_group_id = p_business_group_id
1261 and job.business_group_id = p_business_group_id
1262 --end of bug 13905482
1263 group by hrl.lookup_code;
1264
1265
1266 l_c2 c2%rowtype;
1267
1268 cursor c2_ns is
1269
1270 select
1271 count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,'AFSMNSDISOP',
1272 1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',1,'NSDISOP',1,null)) no_recently_vets ,
1273 hrl.lookup_code lookup_code
1274 from per_periods_of_service pds,
1275 per_all_people_f peo,
1276 per_all_assignments_f ass,
1277 hr_organization_information hoi1,
1278 hr_organization_information hoi2,
1279 per_jobs job,
1280 hr_lookups hrl
1281 where
1282 pds.date_start <= p_end_date
1283 and nvl(pds.actual_termination_date,p_end_date + 1) >= p_end_date
1284 and pds.person_id = ass.person_id
1285 and peo.person_id = ass.person_id
1286 and p_end_date between job.date_from and nvl(job.date_to, p_end_date)
1287 and job.job_information1 = hrl.lookup_code
1288 and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
1289 and ass.job_id = job.job_id
1290 and p_end_date between peo.effective_start_date and peo.effective_end_date
1291 and peo.current_employee_flag = 'Y'
1292 and ass.assignment_type = 'E'
1293 and ass.primary_flag = 'Y'
1294 and ass.effective_start_date =
1295 (select max(paf2.effective_start_date)
1296 from per_all_assignments_f paf2
1297 where paf2.person_id = ass.person_id
1298 and paf2.primary_flag = 'Y'
1299 and paf2.assignment_type = 'E'
1300 and paf2.effective_start_date <= p_end_date
1301 )
1302 and peo.effective_start_date =
1303 (select max(peo2.effective_start_date)
1304 from per_all_people_f peo2
1305 where peo2.person_id = peo.person_id
1306 and peo.current_employee_flag = 'Y'
1307 and peo2.effective_start_date < p_end_date
1308 )
1309 and to_char(ass.assignment_status_type_id) = hoi1.org_information1
1310 and hoi1.org_information_context = 'Reporting Statuses'
1311 and hoi1.organization_id = p_business_group_id
1312 and ass.employment_category = hoi2.org_information1
1313 and hoi2.organization_id = p_business_group_id
1314 and hoi2.org_information_context = 'Reporting Categories'
1315 and p_end_date between ass.effective_start_date and ass.effective_end_date
1316 and ass.location_id in
1317 (select entity_id
1318 from per_gen_hierarchy_nodes
1319 where hierarchy_version_id = p_hierarchy_version_id
1320 and (hierarchy_node_id = l_hierarchy_node_id
1321 or parent_hierarchy_node_id = l_hierarchy_node_id
1322 ))
1323 and check_recent_or_not(peo.person_id,p_end_date) > 0
1324
1325 group by hrl.lookup_code;
1326
1327
1328 l_c2_ns c2_ns%rowtype;
1329
1330 cursor c3 is
1331 select
1332 count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,
1333 'AFSMDIS',1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) nh_dis_vets,
1334 count(decode(peo.per_information25,'OTEV',1,'OTEDV',1,'AFSMDISOP',1,
1335 'AFSMNSDISOP',1,'AFSMOP',1,'NSOP',1,'AFSMNSOP',1,'NSDISOP',1,null)) nh_other_vets ,
1336 count(decode(peo.per_information25,'AFSM',1,'AFSMNSDIS',1,'AFSMDIS',1,'AFSMDISOP',1,
1337 'AFSMNSDISOP',1,'AFSMOP',1,'AFSMNSOP',1,'AFSMNS',1,null)) nh_armed_vets,
1338 count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) nh_not_vets,
1339 count(1) no_nh_tot_vets, --#10118692
1340 hrl.lookup_code lookup_code
1341 FROM per_all_people_f peo,
1342 per_all_assignments_f ass,
1343 per_jobs job,
1344 hr_lookups hrl,
1345 per_periods_of_service pps
1346 WHERE peo.person_id = ass.person_id
1347 AND peo.person_id = pps.person_id
1348 AND peo.business_group_id = p_business_group_id
1349 AND ass.business_group_id = p_business_group_id
1350 AND job.business_group_id = p_business_group_id
1351 AND pps.business_group_id = p_business_group_id
1352 AND ass.job_id = job.job_id
1353 AND peo.current_employee_flag = 'Y'
1354 AND ass.assignment_type = 'E'
1355 AND ass.primary_flag = 'Y'
1359 AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
1356 AND job.job_information_category = 'US'
1357 AND ass.effective_start_date <= p_end_date
1358 AND job.job_information1 = hrl.lookup_code
1360 AND ass.effective_start_date = (select max(paf2.effective_start_date)
1361 from per_all_assignments_f paf2
1362 where paf2.person_id = ass.person_id
1363 and paf2.assignment_id = ass.assignment_id
1364 and paf2.effective_start_date = peo.effective_start_date
1365 and paf2.primary_flag = 'Y'
1366 and paf2.assignment_type = 'E'
1367 and paf2.effective_start_date <= p_end_date)
1368 AND months_between (p_end_date,pps.date_start) <= 12
1369 AND months_between (p_end_date,pps.date_start) >= 0
1370 AND peo.effective_start_date = pps.date_start
1371 AND EXISTS (
1372 SELECT 'X'
1373 FROM HR_ORGANIZATION_INFORMATION HOI1,
1374 HR_ORGANIZATION_INFORMATION HOI2
1375 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1376 AND hoi1.org_information_context = 'Reporting Statuses'
1377 AND hoi1.organization_id = p_business_group_id
1378 AND ass.employment_category = hoi2.org_information1
1379 AND hoi2.organization_id = p_business_group_id
1380 AND hoi2.org_information_context = 'Reporting Categories'
1381 AND hoi1.organization_id = hoi2.organization_id)
1382 and ass.location_id in
1383 (select entity_id
1384 from per_gen_hierarchy_nodes
1385 where hierarchy_version_id = p_hierarchy_version_id
1386 and (hierarchy_node_id = l_hierarchy_node_id
1387 or parent_hierarchy_node_id = l_hierarchy_node_id
1388 ))
1389 group by hrl.lookup_code;
1390
1391 l_c3 c3%rowtype;
1392
1393 cursor c3_ns is
1394 select
1395 count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,
1396 'AFSMNSDISOP',1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',
1397 1,'NSDISOP',1,null)) nh_recently_vets ,
1398 hrl.lookup_code lookup_code
1399 FROM per_all_people_f peo,
1400 per_all_assignments_f ass,
1401 per_jobs job,
1402 hr_lookups hrl,
1403 per_periods_of_service pps
1404 WHERE peo.person_id = ass.person_id
1405 AND peo.person_id = pps.person_id
1406 AND peo.business_group_id = p_business_group_id
1407 AND ass.business_group_id = p_business_group_id
1408 AND job.business_group_id = p_business_group_id
1409 AND pps.business_group_id = p_business_group_id
1410 AND ass.job_id = job.job_id
1411 AND peo.current_employee_flag = 'Y'
1412 AND ass.assignment_type = 'E'
1413 AND ass.primary_flag = 'Y'
1414 AND job.job_information_category = 'US'
1415 AND ass.effective_start_date <= p_end_date
1416 AND job.job_information1 = hrl.lookup_code
1417 AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
1418 AND ass.effective_start_date = (select max(paf2.effective_start_date)
1419 from per_all_assignments_f paf2
1420 where paf2.person_id = ass.person_id
1421 and paf2.assignment_id = ass.assignment_id
1422 and paf2.effective_start_date = peo.effective_start_date
1423 and paf2.primary_flag = 'Y'
1424 and paf2.assignment_type = 'E'
1425 and paf2.effective_start_date <= p_end_date)
1426 AND months_between (p_end_date,pps.date_start) <= 12
1427 AND months_between (p_end_date,pps.date_start) >= 0
1428 AND peo.effective_start_date = pps.date_start
1429 AND EXISTS (
1430 SELECT 'X'
1431 FROM HR_ORGANIZATION_INFORMATION HOI1,
1432 HR_ORGANIZATION_INFORMATION HOI2
1433 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1434 AND hoi1.org_information_context = 'Reporting Statuses'
1435 AND hoi1.organization_id = p_business_group_id
1436 AND ass.employment_category = hoi2.org_information1
1437 AND hoi2.organization_id = p_business_group_id
1438 AND hoi2.org_information_context = 'Reporting Categories'
1439 AND hoi1.organization_id = hoi2.organization_id)
1440 and ass.location_id in
1441 (select entity_id
1442 from per_gen_hierarchy_nodes
1443 where hierarchy_version_id = p_hierarchy_version_id
1444 and (hierarchy_node_id = l_hierarchy_node_id
1445 or parent_hierarchy_node_id = l_hierarchy_node_id
1446 ))
1447 and check_recent_or_not(peo.person_id,p_end_date) > 0
1448
1449 group by hrl.lookup_code;
1450
1451 l_c3_ns c3_ns%rowtype;
1452
1453 -- cursor c_min_max is
1454
1455 l_month_start_date date := null;
1456 l_month_end_date date := null;
1457
1458 cursor c_min_max is
1459 SELECT count(*) num_people
1460 FROM per_all_assignments_f paf
1461 WHERE paf.business_group_id = p_business_group_id
1462 AND paf.primary_flag = 'Y'
1463 AND paf.assignment_type = 'E'
1464 --9011580
1465 --AND l_month_start_date between asg.effective_start_date and asg.effective_end_date
1466 and paf.effective_end_date >= l_month_start_date
1467 AND l_month_end_date between paf.effective_start_date and paf.effective_end_date
1468 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1469 from per_all_assignments_f paf2
1470 where paf2.person_id = paf.person_id
1471 and paf2.primary_flag = 'Y'
1472 and paf2.assignment_type = 'E'
1473 and paf2.effective_start_date
1474 <= l_month_end_date
1475 )
1476 AND paf.business_group_id = p_business_group_id
1477 AND paf.location_id in /*bug 14803681 - Removed to_char*/
1478 (SELECT entity_id
1479 FROM per_gen_hierarchy_nodes
1480 WHERE hierarchy_version_id = p_hierarchy_version_id
1481 AND (hierarchy_node_id = l_hierarchy_node_id
1482 OR parent_hierarchy_node_id = l_hierarchy_node_id
1483 ))
1484 AND EXISTS (
1485 SELECT 'X'
1486 FROM HR_ORGANIZATION_INFORMATION HOI1,
1487 HR_ORGANIZATION_INFORMATION HOI2
1488 WHERE TO_CHAR(paf.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1489 AND hoi1.org_information_context = 'Reporting Statuses'
1490 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
1491 AND paf.employment_category = hoi2.org_information1
1492 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
1493 AND hoi2.org_information_context = 'Reporting Categories');
1494
1495 l_min_max c_min_max%rowtype;
1496
1497 cursor c_tot_emps is
1498 SELECT count(distinct paf.person_id) num_people
1499 FROM per_all_assignments_f paf
1500 --, per_jobs_vl job
1501 ,per_periods_of_service pps --8667924
1502 WHERE
1503 -- job.job_information_category = 'US'
1504 -- and p_end_date between job.date_from and nvl(job.date_to,p_end_date)
1505 paf.person_id = pps.person_id
1506 and paf.business_group_id = pps.business_group_id
1507 -- and job.job_information1 is not null
1508 --and paf.job_id = job.job_id
1509 and paf.business_group_id = p_business_group_id
1510 AND paf.primary_flag = 'Y'
1511 AND paf.assignment_type = 'E'
1512 and p_end_date between paf.effective_start_date and paf.effective_end_date
1513 and paf.effective_start_date = (select max(paf2.effective_start_date)
1514 from per_all_assignments_f paf2
1515 where paf2.person_id = paf.person_id
1516 and paf2.primary_flag = 'Y'
1517 and paf2.assignment_type = 'E'
1518 and paf2.effective_start_date
1519 <= p_end_date)
1520 AND paf.location_id in /* bug:14803681 - Removed to_char*/
1521 (SELECT entity_id
1522 FROM per_gen_hierarchy_nodes
1523 WHERE hierarchy_version_id = p_hierarchy_version_id
1524 AND (hierarchy_node_id = l_hierarchy_node_id
1525 OR parent_hierarchy_node_id = l_hierarchy_node_id
1526 ))
1527 AND
1528 ( EXISTS (
1529 SELECT 'X'
1530 FROM HR_ORGANIZATION_INFORMATION HOI1,
1531 HR_ORGANIZATION_INFORMATION HOI2
1532 WHERE TO_CHAR(paf.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1533 AND hoi1.org_information_context = 'Reporting Statuses'
1534 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
1535 AND paf.employment_category = hoi2.org_information1
1536 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
1537 AND hoi2.org_information_context = 'Reporting Categories')
1538 /* OR --8667924
1539 months_between(p_end_date,pps.actual_termination_date) between 0 and 12
1540 Commented and added the following condition to ensure that terminated
1541 employees without FPD are not picked up for total count*/
1542 AND p_end_date BETWEEN pps.date_start AND nvl(pps.actual_termination_date,p_end_date)
1543 );
1544
1545
1546 l_proc varchar2(40) := 'loop_through_establishments';
1547 --
1548 begin
1549 --
1550 hr_utility.set_location(l_proc,10);
1551 --
1552 open c1;
1553 --
1554 loop
1555 --
1556 fetch c1 into l_c1;
1557 exit when c1%notfound;
1558 --
1559 hr_utility.set_location(l_proc,20);
1560 hr_utility.trace('l_c1.street : ' || l_c1.street);
1561 l_estab_rec := l_estab_rec_blank;
1562 --
1563 l_hierarchy_node_id := l_c1.hierarchy_node_id;
1564 l_estab_rec.reporting_name := l_c1.reporting_name;
1565 l_estab_rec.unit_number := l_c1.unit_number;
1566 l_estab_rec.street := l_c1.street;
1567 l_estab_rec.street2 := l_c1.street2;
1568 l_estab_rec.city := l_c1.city;
1569 l_estab_rec.county := l_c1.county;
1570 l_estab_rec.state := l_c1.state;
1571 l_estab_rec.zip_code := l_c1.zip_code;
1572 l_estab_rec.naics := l_c1.naics;
1573 l_estab_rec.duns := l_c1.duns;
1574 l_estab_rec.ein := l_c1.ein;
1575 l_estab_rec.hq := l_c1.hq;
1576 --
1577 open c2;
1578 --
1579 loop
1580 --
1581 fetch c2 into l_c2;
1582 exit when c2%notfound;
1583 --
1584 hr_utility.set_location(l_proc,30);
1585 if l_c2.lookup_code = '10' then
1586 --Executive/Senior Level Officials and Managers
1587 l_estab_rec.l1_total := l_c2.no_dis_vets;
1588 l_estab_rec.m1_total := l_c2.no_other_vets;
1589 l_estab_rec.n1_total := l_c2.no_armed_vets;
1590 --l_estab_rec.o1_total := l_c2.no_recently_vets;
1591 -- Commented for #10118692
1592 /*l_estab_rec.p1_total := l_c2.no_dis_vets
1593 + l_c2.no_other_vets
1594 + l_c2.no_armed_vets
1595 + l_c2.no_not_vets;*/
1596 -- Added for #10118692
1597 l_estab_rec.p1_total := l_c2.no_tot_vets;
1598 elsif l_c2.lookup_code = '1' then
1599 --First/Mid Level Officials and Managers
1600 l_estab_rec.l2_total := l_c2.no_dis_vets;
1601 l_estab_rec.m2_total := l_c2.no_other_vets;
1602 l_estab_rec.n2_total := l_c2.no_armed_vets;
1603 --l_estab_rec.o2_total := l_c2.no_recently_vets;
1604 -- Commented for #10118692
1605 /*l_estab_rec.p2_total := l_c2.no_dis_vets
1606 + l_c2.no_other_vets
1607 + l_c2.no_armed_vets
1608 + l_c2.no_not_vets;*/
1609 -- Added for #10118692
1610 l_estab_rec.p2_total := l_c2.no_tot_vets;
1611
1612 elsif l_c2.lookup_code = '2' then
1613 --Professionals
1614 l_estab_rec.l3_total := l_c2.no_dis_vets;
1615 l_estab_rec.m3_total := l_c2.no_other_vets;
1616 l_estab_rec.n3_total := l_c2.no_armed_vets;
1617 --l_estab_rec.o3_total := l_c2.no_recently_vets;
1618 -- Commented for #10118692
1619 /*l_estab_rec.p3_total := l_c2.no_dis_vets
1620 + l_c2.no_other_vets
1621 + l_c2.no_armed_vets
1622 + l_c2.no_not_vets;*/
1623 -- Added for #10118692
1624 l_estab_rec.p3_total := l_c2.no_tot_vets;
1625
1626 elsif l_c2.lookup_code = '3' then
1627 --Technicians
1628 l_estab_rec.l4_total := l_c2.no_dis_vets;
1629 l_estab_rec.m4_total := l_c2.no_other_vets;
1630 l_estab_rec.n4_total := l_c2.no_armed_vets;
1631 --l_estab_rec.o4_total := l_c2.no_recently_vets;
1632 -- Commented for #10118692
1633 /*l_estab_rec.p4_total := l_c2.no_dis_vets
1634 + l_c2.no_other_vets
1635 + l_c2.no_armed_vets
1636 + l_c2.no_not_vets;*/
1637 -- Added for #10118692
1638 l_estab_rec.p4_total := l_c2.no_tot_vets;
1639
1640 elsif l_c2.lookup_code = '4' then
1641 --Sales Workers
1642 l_estab_rec.l5_total := l_c2.no_dis_vets;
1643 l_estab_rec.m5_total := l_c2.no_other_vets;
1644 l_estab_rec.n5_total := l_c2.no_armed_vets;
1645 --l_estab_rec.o5_total := l_c2.no_recently_vets;
1646 -- Commented for #10118692
1647 /*l_estab_rec.p5_total := l_c2.no_dis_vets
1648 + l_c2.no_other_vets
1649 + l_c2.no_armed_vets
1650 + l_c2.no_not_vets;*/
1651 -- Added for #10118692
1652 l_estab_rec.p5_total := l_c2.no_tot_vets;
1653
1654 elsif l_c2.lookup_code = '5' then
1655 --Administrative Support Workers
1656 l_estab_rec.l6_total := l_c2.no_dis_vets;
1657 l_estab_rec.m6_total := l_c2.no_other_vets;
1658 l_estab_rec.n6_total := l_c2.no_armed_vets;
1659 --l_estab_rec.o6_total := l_c2.no_recently_vets;
1660 -- Commented for #10118692
1661 /*l_estab_rec.p6_total := l_c2.no_dis_vets
1662 + l_c2.no_other_vets
1663 + l_c2.no_armed_vets
1664 + l_c2.no_not_vets;*/
1665 -- Added for #10118692
1666 l_estab_rec.p6_total := l_c2.no_tot_vets;
1667
1668 elsif l_c2.lookup_code = '6' then
1669 --Craft Workers
1670 l_estab_rec.l7_total := l_c2.no_dis_vets;
1671 l_estab_rec.m7_total := l_c2.no_other_vets;
1672 l_estab_rec.n7_total := l_c2.no_armed_vets;
1673 --l_estab_rec.o7_total := l_c2.no_recently_vets;
1674 -- Commented for #10118692
1675 /*l_estab_rec.p7_total := l_c2.no_dis_vets
1676 + l_c2.no_other_vets
1677 + l_c2.no_armed_vets
1678 + l_c2.no_not_vets;*/
1679 -- Added for #10118692
1680 l_estab_rec.p7_total := l_c2.no_tot_vets;
1681
1682 elsif l_c2.lookup_code = '7' then
1683 --Operatives
1684 l_estab_rec.l8_total := l_c2.no_dis_vets;
1685 l_estab_rec.m8_total := l_c2.no_other_vets;
1686 l_estab_rec.n8_total := l_c2.no_armed_vets;
1687 --l_estab_rec.o8_total := l_c2.no_recently_vets;
1688 -- Commented for #10118692
1689 /*l_estab_rec.p8_total := l_c2.no_dis_vets
1690 + l_c2.no_other_vets
1691 + l_c2.no_armed_vets
1692 + l_c2.no_not_vets;*/
1693 -- Added for #10118692
1694 l_estab_rec.p8_total := l_c2.no_tot_vets;
1695
1696 elsif l_c2.lookup_code = '8' then
1697 --Laborers and Helpers
1698 l_estab_rec.l9_total := l_c2.no_dis_vets;
1699 l_estab_rec.m9_total := l_c2.no_other_vets;
1700 l_estab_rec.n9_total := l_c2.no_armed_vets;
1701 --l_estab_rec.o9_total := l_c2.no_recently_vets;
1702 -- Commented for #10118692
1703 /*l_estab_rec.p9_total := l_c2.no_dis_vets
1704 + l_c2.no_other_vets
1705 + l_c2.no_armed_vets
1706 + l_c2.no_not_vets;*/
1707 -- Added for #10118692
1708 l_estab_rec.p9_total := l_c2.no_tot_vets;
1709
1710 elsif l_c2.lookup_code = '9' then
1711 --Service Workers
1712 l_estab_rec.l10_total := l_c2.no_dis_vets;
1713 l_estab_rec.m10_total := l_c2.no_other_vets;
1714 l_estab_rec.n10_total := l_c2.no_armed_vets;
1715 --l_estab_rec.o10_total := l_c2.no_recently_vets;
1716 -- Commented for #10118692
1717 /*l_estab_rec.p10_total := l_c2.no_dis_vets
1718 + l_c2.no_other_vets
1719 + l_c2.no_armed_vets
1720 + l_c2.no_not_vets;*/
1721 -- Added for #10118692
1722 l_estab_rec.p10_total := l_c2.no_tot_vets;
1723
1724 end if;
1725
1726 end loop;
1727
1728 close c2;
1729
1730 open c2_ns;
1731 loop
1732 fetch c2_ns into l_c2_ns;
1733 exit when c2_ns%notfound;
1734
1735 if l_c2_ns.lookup_code = '10' then
1736 --Executive/Senior Level Officials and Managers
1737 l_estab_rec.o1_total := l_c2_ns.no_recently_vets;
1738 -- Commented for #10118692
1739 /*l_estab_rec.p1_total := l_estab_rec.p1_total
1740 + l_c2_ns.no_recently_vets;*/
1741
1742 elsif l_c2_ns.lookup_code = '1' then
1743 --First/Mid Level Officials and Managers
1744 l_estab_rec.o2_total := l_c2_ns.no_recently_vets;
1745 -- Commented for #10118692
1746 /*l_estab_rec.p2_total := l_estab_rec.p2_total
1747 + l_c2_ns.no_recently_vets;*/
1748
1749 elsif l_c2_ns.lookup_code = '2' then
1750 --Professionals
1751 l_estab_rec.o3_total := l_c2_ns.no_recently_vets;
1752 -- Commented for #10118692
1753 /* l_estab_rec.p3_total := l_estab_rec.p3_total
1754 + l_c2_ns.no_recently_vets;*/
1755
1756 elsif l_c2_ns.lookup_code = '3' then
1757 --Technicians
1758 l_estab_rec.o4_total := l_c2_ns.no_recently_vets;
1759 -- Commented for #10118692
1760 /* l_estab_rec.p4_total := l_estab_rec.p4_total
1761 + l_c2_ns.no_recently_vets;*/
1762
1763 elsif l_c2_ns.lookup_code = '4' then
1764 --Sales Workers
1765 l_estab_rec.o5_total := l_c2_ns.no_recently_vets;
1766 -- Commented for #10118692
1767 /* l_estab_rec.p5_total := l_estab_rec.p5_total
1768 + l_c2_ns.no_recently_vets; */
1769
1770 elsif l_c2_ns.lookup_code = '5' then
1771 --Administrative Support Workers
1772 l_estab_rec.o6_total := l_c2_ns.no_recently_vets;
1773 -- Commented for #10118692
1774 /* l_estab_rec.p6_total := l_estab_rec.p6_total
1775 + l_c2_ns.no_recently_vets;*/
1776
1777 elsif l_c2_ns.lookup_code = '6' then
1778 --Craft Workers
1779 l_estab_rec.o7_total := l_c2_ns.no_recently_vets;
1780 -- Commented for #10118692
1781 /* l_estab_rec.p7_total := l_estab_rec.p7_total
1782 + l_c2_ns.no_recently_vets;*/
1783
1784 elsif l_c2_ns.lookup_code = '7' then
1785 --Operatives
1786 l_estab_rec.o8_total := l_c2_ns.no_recently_vets;
1787 -- Commented for #10118692
1788 /* l_estab_rec.p8_total := l_estab_rec.p8_total
1789 + l_c2_ns.no_recently_vets; */
1790
1791 elsif l_c2_ns.lookup_code = '8' then
1792 --Laborers and Helpers
1793 l_estab_rec.o9_total := l_c2_ns.no_recently_vets;
1794 -- Commented for #10118692
1795 /* l_estab_rec.p9_total := l_estab_rec.p9_total
1796 + l_c2_ns.no_recently_vets;*/
1797
1798 elsif l_c2_ns.lookup_code = '9' then
1799 --Service Workers
1800 l_estab_rec.o10_total := l_c2_ns.no_recently_vets;
1801 -- Commented for #10118692
1802 /*l_estab_rec.p10_total := l_estab_rec.p10_total
1803 + l_c2_ns.no_recently_vets;*/
1804
1805 end if;
1806
1807 end loop;
1808 close c2_ns;
1809
1810 open c3;
1811 --
1812 loop
1813 --
1814 fetch c3 into l_c3;
1815 exit when c3%notfound;
1816 --
1817 hr_utility.set_location(l_proc,40);
1818 if l_c3.lookup_code = '10' then
1819 --
1820 l_estab_rec.q1_total := l_c3.nh_dis_vets;
1821 l_estab_rec.r1_total := l_c3.nh_other_vets;
1822 l_estab_rec.s1_total := l_c3.nh_armed_vets;
1823 -- Commented for #10118692
1824 /*l_estab_rec.u1_total := l_c3.nh_not_vets+
1825 l_c3.nh_dis_vets+
1826 l_c3.nh_other_vets+
1827 l_c3.nh_armed_vets;*/
1828 -- Added for #10118692
1829 l_estab_rec.u1_total := l_c3.no_nh_tot_vets;
1830 --
1831 elsif l_c3.lookup_code = '1' then
1832 --
1833 l_estab_rec.q2_total := l_c3.nh_dis_vets;
1834 l_estab_rec.r2_total := l_c3.nh_other_vets;
1835 l_estab_rec.s2_total := l_c3.nh_armed_vets;
1836 -- Commented for #10118692
1837 /*l_estab_rec.u2_total := l_c3.nh_not_vets+
1838 l_c3.nh_dis_vets+
1839 l_c3.nh_other_vets+
1840 l_c3.nh_armed_vets;*/
1841 -- Added for #10118692
1842 l_estab_rec.u2_total := l_c3.no_nh_tot_vets;
1843 --
1844 elsif l_c3.lookup_code = '2' then
1845 --
1846 l_estab_rec.q3_total := l_c3.nh_dis_vets;
1847 l_estab_rec.r3_total := l_c3.nh_other_vets;
1848 l_estab_rec.s3_total := l_c3.nh_armed_vets;
1849 -- Commented for #10118692
1850 /*l_estab_rec.u3_total := l_c3.nh_not_vets+
1851 l_c3.nh_dis_vets+
1852 l_c3.nh_other_vets+
1853 l_c3.nh_armed_vets;*/
1854 -- Added for #10118692
1855 l_estab_rec.u3_total := l_c3.no_nh_tot_vets;
1856 --
1857 elsif l_c3.lookup_code = '3' then
1858 --
1859 l_estab_rec.q4_total := l_c3.nh_dis_vets;
1860 l_estab_rec.r4_total := l_c3.nh_other_vets;
1861 l_estab_rec.s4_total := l_c3.nh_armed_vets;
1862 -- Commented for #10118692
1863 /*l_estab_rec.u4_total := l_c3.nh_not_vets+
1864 l_c3.nh_dis_vets+
1865 l_c3.nh_other_vets+
1866 l_c3.nh_armed_vets;*/
1867 -- Added for #10118692
1868 l_estab_rec.u4_total := l_c3.no_nh_tot_vets;
1869 --
1870 elsif l_c3.lookup_code = '4' then
1871 --
1872 l_estab_rec.q5_total := l_c3.nh_dis_vets;
1873 l_estab_rec.r5_total := l_c3.nh_other_vets;
1874 l_estab_rec.s5_total := l_c3.nh_armed_vets;
1875 -- Commented for #10118692
1876 /*l_estab_rec.u5_total := l_c3.nh_not_vets+
1877 l_c3.nh_dis_vets+
1878 l_c3.nh_other_vets+
1879 l_c3.nh_armed_vets;*/
1880 -- Added for #10118692
1881 l_estab_rec.u5_total := l_c3.no_nh_tot_vets;
1882 --
1883 elsif l_c3.lookup_code = '5' then
1884 --
1885 l_estab_rec.q6_total := l_c3.nh_dis_vets;
1886 l_estab_rec.r6_total := l_c3.nh_other_vets;
1887 l_estab_rec.s6_total := l_c3.nh_armed_vets;
1888 -- Commented for #10118692
1889 /*l_estab_rec.u6_total := l_c3.nh_not_vets+
1890 l_c3.nh_dis_vets+
1891 l_c3.nh_other_vets+
1892 l_c3.nh_armed_vets;*/
1893 -- Added for #10118692
1894 l_estab_rec.u6_total := l_c3.no_nh_tot_vets;
1895
1896 elsif l_c3.lookup_code = '6' then
1897 --
1898 l_estab_rec.q7_total := l_c3.nh_dis_vets;
1899 l_estab_rec.r7_total := l_c3.nh_other_vets;
1900 l_estab_rec.s7_total := l_c3.nh_armed_vets;
1901 -- Commented for #10118692
1902 /*l_estab_rec.u7_total := l_c3.nh_not_vets+
1903 l_c3.nh_dis_vets+
1904 l_c3.nh_other_vets+
1905 l_c3.nh_armed_vets;*/
1906 -- Added for #10118692
1907 l_estab_rec.u7_total := l_c3.no_nh_tot_vets;
1908 --
1909 elsif l_c3.lookup_code = '7' then
1910 --
1911 l_estab_rec.q8_total := l_c3.nh_dis_vets;
1912 l_estab_rec.r8_total := l_c3.nh_other_vets;
1913 l_estab_rec.s8_total := l_c3.nh_armed_vets;
1914 -- Commented for #10118692
1915 /*l_estab_rec.u8_total := l_c3.nh_not_vets+
1916 l_c3.nh_dis_vets+
1917 l_c3.nh_other_vets+
1918 l_c3.nh_armed_vets;*/
1919 -- Added for #10118692
1920 l_estab_rec.u8_total := l_c3.no_nh_tot_vets;
1921 --
1922 elsif l_c3.lookup_code = '8' then
1923 --
1924 l_estab_rec.q9_total := l_c3.nh_dis_vets;
1925 l_estab_rec.r9_total := l_c3.nh_other_vets;
1926 l_estab_rec.s9_total := l_c3.nh_armed_vets;
1927 -- Commented for #10118692
1928 /*l_estab_rec.u9_total := l_c3.nh_not_vets+
1929 l_c3.nh_dis_vets+
1930 l_c3.nh_other_vets+
1931 l_c3.nh_armed_vets;*/
1932 -- Added for #10118692
1933 l_estab_rec.u9_total := l_c3.no_nh_tot_vets;
1934 --
1935 elsif l_c3.lookup_code = '9' then
1936 --
1937 l_estab_rec.q10_total := l_c3.nh_dis_vets;
1938 l_estab_rec.r10_total := l_c3.nh_other_vets;
1939 l_estab_rec.s10_total := l_c3.nh_armed_vets;
1940 -- Commented for #10118692
1941 /*l_estab_rec.u10_total := l_c3.nh_not_vets+
1942 l_c3.nh_dis_vets+
1943 l_c3.nh_other_vets+
1944 l_c3.nh_armed_vets;*/
1945 -- Added for #10118692
1946 l_estab_rec.u10_total := l_c3.no_nh_tot_vets;
1947 --
1948 end if;
1949 --
1950 end loop;
1951 --
1952 close c3;
1953
1954 open c3_ns;
1955 --
1956 loop
1957 --
1958 fetch c3_ns into l_c3_ns;
1959 exit when c3_ns%notfound;
1960 --
1961
1962 if l_c3_ns.lookup_code = '10' then
1963 --
1964 l_estab_rec.t1_total := l_c3_ns.nh_recently_vets;
1965 -- Commented for #10118692
1966 /*l_estab_rec.u1_total := l_estab_rec.u1_total
1967 +l_c3_ns.nh_recently_vets;*/
1968 --
1969 elsif l_c3_ns.lookup_code = '1' then
1970 --
1971 l_estab_rec.t2_total := l_c3_ns.nh_recently_vets;
1972 -- Commented for #10118692
1973 /*l_estab_rec.u2_total := l_estab_rec.u2_total
1974 +l_c3_ns.nh_recently_vets;*/
1975 --
1976 elsif l_c3_ns.lookup_code = '2' then
1977 --
1978 l_estab_rec.t3_total := l_c3_ns.nh_recently_vets;
1979 -- Commented for #10118692
1980 /*l_estab_rec.u3_total := l_estab_rec.u3_total
1981 +l_c3_ns.nh_recently_vets;*/
1982 --
1983 elsif l_c3_ns.lookup_code = '3' then
1984 --
1985 l_estab_rec.t4_total := l_c3_ns.nh_recently_vets;
1986 -- Commented for #10118692
1987 /*l_estab_rec.u4_total := l_estab_rec.u4_total
1988 +l_c3_ns.nh_recently_vets;*/
1989 --
1990 elsif l_c3_ns.lookup_code = '4' then
1991 --
1992 l_estab_rec.t5_total := l_c3_ns.nh_recently_vets;
1993 -- Commented for #10118692
1994 /*l_estab_rec.u5_total := l_estab_rec.u5_total
1995 +l_c3_ns.nh_recently_vets;*/
1996 --
1997 elsif l_c3_ns.lookup_code = '5' then
1998 --
1999 l_estab_rec.t6_total := l_c3_ns.nh_recently_vets;
2000 -- Commented for #10118692
2001 /*l_estab_rec.u6_total := l_estab_rec.u6_total
2002 +l_c3_ns.nh_recently_vets;*/
2003
2004 elsif l_c3_ns.lookup_code = '6' then
2005 --
2006 l_estab_rec.t7_total := l_c3_ns.nh_recently_vets;
2007 -- Commented for #10118692
2008 /*l_estab_rec.u7_total := l_estab_rec.u7_total
2009 +l_c3_ns.nh_recently_vets;*/
2010 --
2011 elsif l_c3_ns.lookup_code = '7' then
2012 --
2013 l_estab_rec.t8_total := l_c3_ns.nh_recently_vets;
2014 -- Commented for #10118692
2015 /*l_estab_rec.u8_total := l_estab_rec.u8_total
2016 +l_c3_ns.nh_recently_vets;*/
2017 --
2018 elsif l_c3_ns.lookup_code = '8' then
2019 --
2020 l_estab_rec.t9_total := l_c3_ns.nh_recently_vets;
2021 -- Commented for #10118692
2022 /*l_estab_rec.u9_total := l_estab_rec.u9_total
2023 +l_c3_ns.nh_recently_vets;*/
2024 --
2025 elsif l_c3_ns.lookup_code = '9' then
2026 --
2027 l_estab_rec.t10_total := l_c3_ns.nh_recently_vets;
2028 -- Commented for #10118692
2029 /*l_estab_rec.u10_total := l_estab_rec.u10_total
2030 +l_c3_ns.nh_recently_vets;*/
2031 --
2032 end if;
2033 --
2034 end loop;
2035 --
2036 close c3_ns;
2037 hr_utility.trace('l_c1.state : ' || l_c1.state);
2038 hr_utility.trace('l_consol_rec.state : ' || l_consol_rec.state);
2039 if l_c1.state <> nvl(l_consol_rec.state,l_c1.state) then
2040 --
2041 -- Write out the old consolidated report and
2042 -- process the next record which is for a different state.
2043 --
2044 hr_utility.set_location(l_proc,50);
2045 write_consolidated_record;
2046 l_consol_rec := l_estab_rec_blank;
2047 l_month_rec := l_month_rec_blank;
2048 --
2049 end if;
2050 --
2051
2052 --
2053 open c_tot_emps;
2054 fetch c_tot_emps into l_tot_emps;
2055 close c_tot_emps;
2056 hr_utility.trace('l_tot_emps : ' || l_tot_emps);
2057
2058 --while l_start_date < p_end_date loop
2059 for l_month_number in 1 .. 12 loop
2060
2061 --9000119
2062 l_month_start_date := ADD_MONTHS(p_end_date,-l_month_number)+1;
2063 l_month_end_date := ADD_MONTHS(l_month_start_date,1)-1;
2064
2065
2066 hr_utility.trace('l_month_start_date : ' || l_month_start_date);
2067 hr_utility.trace('l_month_end_date : ' || l_month_end_date);
2068 --
2069 open c_min_max;
2070 --
2071 fetch c_min_max into l_min_max;
2072
2073 --
2074 hr_utility.set_location(l_proc,60);
2075 hr_utility.trace('l_min_max : ' || l_min_max.num_people);
2076 hr_utility.trace('p_hierarchy_version_id : ' || p_hierarchy_version_id);
2077 hr_utility.trace('l_hierarchy_node_id : ' || l_hierarchy_node_id);
2078 if l_estab_rec.min_count is null then
2079 --
2080 l_estab_rec.min_count := l_min_max.num_people;
2081 --
2082 end if;
2083 --
2084 if l_estab_rec.max_count is null then
2085 --
2086 l_estab_rec.max_count := l_min_max.num_people;
2087 --
2088 end if;
2089 --
2090 if l_estab_rec.min_count > l_min_max.num_people then
2091 --
2092 l_estab_rec.min_count := l_min_max.num_people;
2093 --
2094 end if;
2095 --
2096 if l_estab_rec.max_count < l_min_max.num_people then
2097 --
2098 l_estab_rec.max_count := l_min_max.num_people;
2099 --
2100 end if;
2101 --
2102 if to_char(l_month_start_date,'MM') = '01' then
2103 --
2104 l_month_rec.jan := nvl(l_month_rec.jan,0) +
2105 l_min_max.num_people;
2106 --
2107 elsif to_char(l_month_start_date,'MM') = '02' then
2108 --
2109 l_month_rec.feb := nvl(l_month_rec.feb,0) +
2110 l_min_max.num_people;
2111 --
2112 elsif to_char(l_month_start_date,'MM') = '03' then
2113 --
2114 l_month_rec.mar := nvl(l_month_rec.mar,0) +
2115 l_min_max.num_people;
2116 --
2117 elsif to_char(l_month_start_date,'MM') = '04' then
2118 --
2119 l_month_rec.apr := nvl(l_month_rec.apr,0) +
2120 l_min_max.num_people;
2121 --
2122 elsif to_char(l_month_start_date,'MM') = '05' then
2123 --
2124 l_month_rec.may := nvl(l_month_rec.may,0) +
2125 l_min_max.num_people;
2126 --
2127 elsif to_char(l_month_start_date,'MM') = '06' then
2128 --
2129 l_month_rec.jun := nvl(l_month_rec.jun,0) +
2130 l_min_max.num_people;
2131 --
2132 elsif to_char(l_month_start_date,'MM') = '07' then
2133 --
2134 l_month_rec.jul := nvl(l_month_rec.jul,0) +
2135 l_min_max.num_people;
2136 --
2137 elsif to_char(l_month_start_date,'MM') = '08' then
2138 --
2139 l_month_rec.aug := nvl(l_month_rec.aug,0) +
2140 l_min_max.num_people;
2141 --
2142 elsif to_char(l_month_start_date,'MM') = '09' then
2143 --
2144 l_month_rec.sep := nvl(l_month_rec.sep,0) +
2145 l_min_max.num_people;
2146 --
2147 elsif to_char(l_month_start_date,'MM') = '10' then
2148 --
2149 l_month_rec.oct := nvl(l_month_rec.oct,0) +
2150 l_min_max.num_people;
2151 --
2152 elsif to_char(l_month_start_date,'MM') = '11' then
2153 --
2154 l_month_rec.nov := nvl(l_month_rec.nov,0) +
2155 l_min_max.num_people;
2156 --
2157 elsif to_char(l_month_start_date,'MM') = '12' then
2158 --
2159 l_month_rec.dec := nvl(l_month_rec.dec,0) +
2160 l_min_max.num_people;
2161 --
2162 end if;
2163 --
2164 --l_start_date := add_months(l_start_date,1);
2165 --
2166 close c_min_max;
2167 --
2168 end loop;
2169 --
2170 write_establishment_record;
2171 --
2172 end loop;
2173 --
2174 close c1;
2175 --
2176 -- Last case unhandled MSC records
2177 --
2178 if l_consol_rec.state is not null then
2179 --
2180 write_consolidated_record;
2181 --
2182 end if;
2183 --
2184 end loop_through_establishments;
2185
2186 procedure vets_mag_report
2187 (errbuf out nocopy varchar2,
2188 retcode out nocopy number,
2189 p_start_date in varchar2,
2190 p_end_date in varchar2,
2191 p_hierarchy_id in number,
2192 p_hierarchy_version_id in number,
2193 p_business_group_id in number,
2194 p_all_establishments in varchar2
2195 ) is
2196 --
2197 l_start_date date := fnd_date.canonical_to_date(p_start_date);
2198 l_end_date date := fnd_date.canonical_to_date(p_end_date);
2199 l_string varchar2(2000);
2200 --
2201 cursor c2 is
2202 select count(*)
2203 from per_gen_hierarchy_nodes
2204 where node_type = 'EST'
2205 and hierarchy_version_id = p_hierarchy_version_id;
2206 --
2207 l_count number;
2208 --
2209 begin
2210 --
2211 --hr_utility.trace_on(null,'ORACLE');
2212 l_org_rec.ending_period := to_char(l_end_date,'MMDDYYYY');
2213 l_all_estab := p_all_establishments;
2214 set_org_details(p_hierarchy_version_id => p_hierarchy_version_id,
2215 p_business_group_id => p_business_group_id);
2216 --
2217 open c2;
2218 --
2219 fetch c2 into l_count;
2220 --
2221 if l_count = 1 then
2222 --
2223 l_org_rec.form_type := 'S';
2224 --
2225 end if;
2226 --
2227 close c2;
2228 --
2229 loop_through_establishments(p_hierarchy_version_id => p_hierarchy_version_id,
2230 p_business_group_id => p_business_group_id,
2231 p_start_date => l_start_date,
2232 p_end_date => l_end_date);
2233 --
2234 end vets_mag_report;
2235 --
2236 end per_vets_mag_100a_report;