[Home] [Help]
PACKAGE BODY: APPS.PER_VETS_MAG_REPORT
Source
1 package body per_vets_mag_report as
2 /* $Header: pevetmag.pkb 120.6.12010000.2 2008/08/06 09:38:52 ubhat ship $ */
3 --
4 type org_rec is record
5 (company_number varchar2(20),
6 ending_period varchar2(30),
7 contractor_type varchar2(80),
8 form_type varchar2(80),
9 msc_number varchar2(80),
10 number_of_estabs number,
11 parent_company varchar2(200),
12 street varchar2(200),
13 city varchar2(200),
14 county varchar2(200),
15 state varchar2(200),
16 zip_code varchar2(200),
17 naics varchar2(200),
18 duns varchar2(200),
19 ein varchar2(200));
20 --
21 l_org_rec org_rec;
22 --
23 type month_rec is record
24 (jan number,
25 feb number,
26 mar number,
27 apr number,
28 may number,
29 jun number,
30 jul number,
31 aug number,
32 sep number,
33 oct number,
34 nov number,
35 dec number);
36 --
37 l_month_rec month_rec;
38 l_month_rec_blank month_rec;
39 --
40 type estab_rec is record
41 (unit_number varchar2(30),
42 reporting_name varchar2(200),
43 street varchar2(200),
44 city varchar2(200),
45 county varchar2(200),
46 state varchar2(200),
47 zip_code varchar2(200),
48 naics varchar2(200),
49 duns varchar2(200),
50 ein varchar2(200),
51 hq varchar2(200),
52 l1_total number,
53 m1_total number,
54 n1_total number,
55 o1_total number,
56 p1_total number,
57 q1_total number,
58 r1_total number,
59 s1_total number,
60 l2_total number,
61 m2_total number,
62 n2_total number,
63 o2_total number,
64 p2_total number,
65 q2_total number,
66 r2_total number,
67 s2_total number,
68 l3_total number,
69 m3_total number,
70 n3_total number,
71 o3_total number,
72 p3_total number,
73 q3_total number,
74 r3_total number,
75 s3_total number,
76 l4_total number,
77 m4_total number,
78 n4_total number,
79 o4_total number,
80 p4_total number,
81 q4_total number,
82 r4_total number,
83 s4_total number,
84 l5_total number,
85 m5_total number,
86 n5_total number,
87 o5_total number,
88 p5_total number,
89 q5_total number,
90 r5_total number,
91 s5_total number,
92 l6_total number,
93 m6_total number,
94 n6_total number,
95 o6_total number,
96 p6_total number,
97 q6_total number,
98 r6_total number,
99 s6_total number,
100 l7_total number,
101 m7_total number,
102 n7_total number,
103 o7_total number,
104 p7_total number,
105 q7_total number,
106 r7_total number,
107 s7_total number,
108 l8_total number,
109 m8_total number,
110 n8_total number,
111 o8_total number,
112 p8_total number,
113 q8_total number,
114 r8_total number,
115 s8_total number,
116 l9_total number,
117 m9_total number,
118 n9_total number,
119 o9_total number,
120 p9_total number,
121 q9_total number,
122 r9_total number,
123 s9_total number,
124 l_grand_total number,
125 m_grand_total number,
126 n_grand_total number,
127 o_grand_total number,
128 p_grand_total number,
129 q_grand_total number,
130 r_grand_total number,
131 s_grand_total number,
132 min_count number,
133 max_count number);
134 --
135 l_estab_rec estab_rec;
136 l_consol_rec estab_rec;
137 l_holder_rec estab_rec;
138 l_estab_rec_blank estab_rec;
139 l_all_estab varchar2(1); -- BUG3695203
140 l_tot_emps number;
141 --
142 /* old: cost 45
143 procedure set_org_details(p_hierarchy_version_id in number) is
144 --
145 cursor c1 is
146 select hoi1.org_information2 company_number,
147 decode(hoi1.org_information3,'2S','S','1P','P','3B','B') contractor_type,
148 nvl(hoi1.org_information1,hou.name) parent_company,
149 upper(loc.address_line_1||
150 ' '||
151 loc.address_line_2||
152 ' '||
153 loc.address_line_3) street,
154 upper(loc.town_or_city) city,
155 upper(loc.region_1) county,
156 loc.region_2 state,
157 loc.postal_code zip_code,
158 hoi2.org_information2 naics,
159 hoi2.org_information4 duns,
160 hoi2.org_information3 ein
161 from --per_gen_hierarchy_nodes pghn,
162 per_gen_hierarchy_nodes pgn,
163 hr_organization_units hou,
164 hr_organization_information hoi1,
165 hr_organization_information hoi2,
166 hr_locations_all loc
167 where pgn.hierarchy_version_id = p_hierarchy_version_id
168 and pgn.node_type = 'PAR'
169 and pgn.entity_id = hou.organization_id
170 and hoi1.org_information_context = 'VETS_Spec'
171 and hoi1.organization_id = hou.organization_id
172 and hoi2.org_information_context = 'VETS_EEO_Dup'
173 and hoi2.organization_id = hou.organization_id
174 --and hou.location_id(+)= loc.location_id;
175 and hou.location_id = loc.location_id(+);
176 */
177 -- new: cost 12
178 procedure set_org_details(p_hierarchy_version_id in number,
179 p_business_group_id in number) is
180 --
181 cursor c1 is
182 select hoi1.org_information2 company_number,
183 decode(hoi1.org_information3,'2S','S','1P','P','3B','B') contractor_type,
184 replace(nvl(hoi1.org_information1,hou.name),',',' ') parent_company,
185 upper(replace(loc.address_line_1,',',' ')||
186 ' '||
187 replace(loc.address_line_2,',',' ')||
188 ' '||
189 replace(loc.address_line_3,',',' ')) street,
190 upper(loc.town_or_city) city,
191 upper(loc.region_1) county,
192 loc.region_2 state,
193 loc.postal_code zip_code,
194 hoi2.org_information2 naics,
195 hoi2.org_information4 duns,
196 hoi2.org_information3 ein
197 from per_gen_hierarchy_nodes pgn,
198 hr_all_organization_units hou, -- vik
199 -- hr_organization_units hou, -- vik
200 hr_organization_information hoi1,
201 hr_organization_information hoi2,
202 hr_locations_all loc
203 where pgn.hierarchy_version_id = p_hierarchy_version_id
204 and pgn.node_type = 'PAR'
205 and pgn.entity_id = hou.organization_id
206 and pgn.business_group_id = p_business_group_id -- vik
207 --and hou.organization_id = p_business_group_id -- vik BUG4179427
208 -- and loc.business_group_id = p_business_group_id -- vik bg_id
209 and hoi1.org_information_context = 'VETS_Spec'
210 and hoi1.organization_id = hou.organization_id
211 and hoi2.org_information_context = 'VETS_EEO_Dup'
212 and hoi2.organization_id = hou.organization_id
213 --and hou.location_id(+)= loc.location_id;
214 and hou.location_id = loc.location_id(+);
215 --
216 begin
217 --
218 open c1;
219 --
220 fetch c1 into l_org_rec.company_number,
221 l_org_rec.contractor_type,
222 l_org_rec.parent_company,
223 l_org_rec.street,
224 l_org_rec.city,
225 l_org_rec.county,
226 l_org_rec.state,
227 l_org_rec.zip_code,
228 l_org_rec.naics,
229 l_org_rec.duns,
230 l_org_rec.ein;
231 --
232 close c1;
233 --
234 end set_org_details;
235 --
236 procedure get_min_max(p_value1 in number,
237 p_value2 in number,
238 p_value3 in number,
239 p_value4 in number,
240 p_value5 in number,
241 p_value6 in number,
242 p_value7 in number,
243 p_value8 in number,
244 p_value9 in number,
245 p_value10 in number,
246 p_value11 in number,
247 p_value12 in number,
248 p_min_out out nocopy number,
249 p_max_out out nocopy number) is
250 --
251 l_min number := p_value1;
252 l_max number := p_value1;
253 --
254 begin
255 --
256 if l_max < p_value2 then
257 --
258 l_max := p_value2;
259 --
260 elsif l_min > p_value2 then
261 --
262 l_min := p_value2;
263 --
264 end if;
265 --
266 if l_max < p_value3 then
267 --
268 l_max := p_value3;
269 --
270 elsif l_min > p_value3 then
271 --
272 l_min := p_value3;
273 --
274 end if;
275 --
276 if l_max < p_value4 then
277 --
278 l_max := p_value4;
279 --
280 elsif l_min > p_value4 then
281 --
282 l_min := p_value4;
283 --
284 end if;
285 --
286 if l_max < p_value5 then
287 --
288 l_max := p_value5;
289 --
290 elsif l_min > p_value5 then
291 --
292 l_min := p_value5;
293 --
294 end if;
295 --
296 if l_max < p_value6 then
297 --
298 l_max := p_value6;
299 --
300 elsif l_min > p_value6 then
301 --
302 l_min := p_value6;
303 --
304 end if;
305 --
306 if l_max < p_value7 then
307 --
308 l_max := p_value7;
309 --
310 elsif l_min > p_value7 then
311 --
312 l_min := p_value7;
313 --
314 end if;
315 --
316 if l_max < p_value8 then
317 --
318 l_max := p_value8;
319 --
320 elsif l_min > p_value8 then
321 --
322 l_min := p_value8;
323 --
324 end if;
325 --
326 if l_max < p_value9 then
327 --
328 l_max := p_value9;
329 --
330 elsif l_min > p_value9 then
331 --
332 l_min := p_value9;
333 --
334 end if;
335 --
336 if l_max < p_value10 then
337 --
338 l_max := p_value10;
339 --
340 elsif l_min > p_value10 then
341 --
342 l_min := p_value10;
343 --
344 end if;
345 --
346 if l_max < p_value11 then
347 --
348 l_max := p_value11;
349 --
350 elsif l_min > p_value11 then
351 --
352 l_min := p_value11;
353 --
354 end if;
355 --
356 if l_max < p_value12 then
357 --
358 l_max := p_value12;
359 --
360 elsif l_min > p_value12 then
361 --
362 l_min := p_value12;
363 --
364 end if;
365 --
366 p_max_out := l_max;
367 p_min_out := l_min;
368 --
369 end get_min_max;
370 --
371 procedure write_consolidated_record is
372 --
373 l_string varchar2(2000);
374 --
375 begin
376 --
377 -- Set l_consol_rec.min_count and l_consol_rec.max_count
378 --
379 get_min_max(p_value1 => l_month_rec.jan,
380 p_value2 => l_month_rec.feb,
381 p_value3 => l_month_rec.mar,
382 p_value4 => l_month_rec.apr,
383 p_value5 => l_month_rec.may,
384 p_value6 => l_month_rec.jun,
385 p_value7 => l_month_rec.jul,
386 p_value8 => l_month_rec.aug,
387 p_value9 => l_month_rec.sep,
388 p_value10 => l_month_rec.oct,
389 p_value11 => l_month_rec.nov,
390 p_value12 => l_month_rec.dec,
391 p_min_out => l_consol_rec.min_count,
392 p_max_out => l_consol_rec.max_count);
393 --
394 l_string := l_org_rec.company_number||','||
395 l_org_rec.ending_period||','||
396 l_org_rec.contractor_type||','||
397 'MSC'||','||
398 l_org_rec.number_of_estabs||','||
399 l_org_rec.parent_company||','||
400 l_org_rec.street||','||
401 l_org_rec.city||','||
402 l_org_rec.county||','||
403 l_org_rec.state||','||
404 l_org_rec.zip_code||','||
405 l_consol_rec.unit_number||','||
406 l_consol_rec.reporting_name||','||
407 l_consol_rec.street||','||
408 l_consol_rec.city||','||
409 l_consol_rec.county||','||
410 l_consol_rec.state||','||
411 l_consol_rec.zip_code||','||
412 l_org_rec.naics||','||
413 l_org_rec.duns||','||
414 l_org_rec.ein||','||
415 nvl(l_consol_rec.l1_total,0)||','||
416 nvl(l_consol_rec.m1_total,0)||','||
417 nvl(l_consol_rec.n1_total,0)||','||
418 nvl(l_consol_rec.o1_total,0)||','||
419 nvl(l_consol_rec.p1_total,0)||','||
420 nvl(l_consol_rec.q1_total,0)||','||
421 nvl(l_consol_rec.r1_total,0)||','||
422 nvl(l_consol_rec.s1_total,0)||','||
423 nvl(l_consol_rec.l2_total,0)||','||
424 nvl(l_consol_rec.m2_total,0)||','||
425 nvl(l_consol_rec.n2_total,0)||','||
426 nvl(l_consol_rec.o2_total,0)||','||
427 nvl(l_consol_rec.p2_total,0)||','||
428 nvl(l_consol_rec.q2_total,0)||','||
429 nvl(l_consol_rec.r2_total,0)||','||
433 nvl(l_consol_rec.n3_total,0)||','||
430 nvl(l_consol_rec.s2_total,0)||','||
431 nvl(l_consol_rec.l3_total,0)||','||
432 nvl(l_consol_rec.m3_total,0)||','||
434 nvl(l_consol_rec.o3_total,0)||','||
435 nvl(l_consol_rec.p3_total,0)||','||
436 nvl(l_consol_rec.q3_total,0)||','||
437 nvl(l_consol_rec.r3_total,0)||','||
438 nvl(l_consol_rec.s3_total,0)||','||
439 nvl(l_consol_rec.l4_total,0)||','||
440 nvl(l_consol_rec.m4_total,0)||','||
441 nvl(l_consol_rec.n4_total,0)||','||
442 nvl(l_consol_rec.o4_total,0)||','||
443 nvl(l_consol_rec.p4_total,0)||','||
444 nvl(l_consol_rec.q4_total,0)||','||
445 nvl(l_consol_rec.r4_total,0)||','||
446 nvl(l_consol_rec.s4_total,0)||','||
447 nvl(l_consol_rec.l5_total,0)||','||
448 nvl(l_consol_rec.m5_total,0)||','||
449 nvl(l_consol_rec.n5_total,0)||','||
450 nvl(l_consol_rec.o5_total,0)||','||
451 nvl(l_consol_rec.p5_total,0)||','||
452 nvl(l_consol_rec.q5_total,0)||','||
453 nvl(l_consol_rec.r5_total,0)||','||
454 nvl(l_consol_rec.s5_total,0)||','||
455 nvl(l_consol_rec.l6_total,0)||','||
456 nvl(l_consol_rec.m6_total,0)||','||
457 nvl(l_consol_rec.n6_total,0)||','||
458 nvl(l_consol_rec.o6_total,0)||','||
459 nvl(l_consol_rec.p6_total,0)||','||
460 nvl(l_consol_rec.q6_total,0)||','||
461 nvl(l_consol_rec.r6_total,0)||','||
462 nvl(l_consol_rec.s6_total,0)||','||
463 nvl(l_consol_rec.l7_total,0)||','||
464 nvl(l_consol_rec.m7_total,0)||','||
465 nvl(l_consol_rec.n7_total,0)||','||
466 nvl(l_consol_rec.o7_total,0)||','||
467 nvl(l_consol_rec.p7_total,0)||','||
468 nvl(l_consol_rec.q7_total,0)||','||
469 nvl(l_consol_rec.r7_total,0)||','||
470 nvl(l_consol_rec.s7_total,0)||','||
471 nvl(l_consol_rec.l8_total,0)||','||
472 nvl(l_consol_rec.m8_total,0)||','||
473 nvl(l_consol_rec.n8_total,0)||','||
474 nvl(l_consol_rec.o8_total,0)||','||
475 nvl(l_consol_rec.p8_total,0)||','||
476 nvl(l_consol_rec.q8_total,0)||','||
477 nvl(l_consol_rec.r8_total,0)||','||
478 nvl(l_consol_rec.s8_total,0)||','||
479 nvl(l_consol_rec.l9_total,0)||','||
480 nvl(l_consol_rec.m9_total,0)||','||
481 nvl(l_consol_rec.n9_total,0)||','||
482 nvl(l_consol_rec.o9_total,0)||','||
483 nvl(l_consol_rec.p9_total,0)||','||
484 nvl(l_consol_rec.q9_total,0)||','||
485 nvl(l_consol_rec.r9_total,0)||','||
486 nvl(l_consol_rec.s9_total,0)||','||
487 nvl(l_consol_rec.l_grand_total,0)||','||
488 nvl(l_consol_rec.m_grand_total,0)||','||
489 nvl(l_consol_rec.n_grand_total,0)||','||
490 nvl(l_consol_rec.o_grand_total,0)||','||
491 nvl(l_consol_rec.p_grand_total,0)||','||
492 nvl(l_consol_rec.q_grand_total,0)||','||
493 nvl(l_consol_rec.r_grand_total,0)||','||
494 nvl(l_consol_rec.s_grand_total,0)||','||
495 nvl(l_consol_rec.max_count,0)||','||
496 nvl(l_consol_rec.min_count,0);
497 --
498 fnd_file.put_line
499 (which => fnd_file.output,
500 buff => l_string);
501 --
502 l_org_rec.number_of_estabs := null;
503 --
504 end write_consolidated_record;
505 --
506 procedure write_establishment_record is
507 --
508 l_string varchar2(2000);
509 l_proc varchar2(40) := 'write_establishment_record';
510 --
511 begin
512 --
513 -- Set form type
514 --
515 hr_utility.set_location(l_proc,10);
516 hr_utility.trace('l_estab_rec.max_count : ' || l_estab_rec.max_count);
517 if l_estab_rec.hq = 'Y' and
518 nvl(l_org_rec.form_type,'-1') <> 'S' then
519 --
520 hr_utility.set_location(l_proc,20);
521 l_org_rec.form_type := 'MHQ';
522 --
523 end if;
524 --
525 if l_estab_rec.hq = 'N' and
526 nvl(l_org_rec.form_type,'-1') <> 'S' and
527 (
528 -- l_estab_rec.max_count >= 50
529 l_tot_emps >= 50
530 or l_all_estab = 'N'
531 ) then
532 --
533 hr_utility.set_location(l_proc,30);
534 l_org_rec.form_type := 'MHL';
535 --
536 end if;
537 --
538 -- Set totals
539 --
540 l_estab_rec.l_grand_total := nvl(l_estab_rec.l1_total,0)+
541 nvl(l_estab_rec.l2_total,0)+
542 nvl(l_estab_rec.l3_total,0)+
543 nvl(l_estab_rec.l4_total,0)+
544 nvl(l_estab_rec.l5_total,0)+
545 nvl(l_estab_rec.l6_total,0)+
546 nvl(l_estab_rec.l7_total,0)+
547 nvl(l_estab_rec.l8_total,0)+
548 nvl(l_estab_rec.l9_total,0);
549 --
550 l_estab_rec.m_grand_total := nvl(l_estab_rec.m1_total,0)+
554 nvl(l_estab_rec.m5_total,0)+
551 nvl(l_estab_rec.m2_total,0)+
552 nvl(l_estab_rec.m3_total,0)+
553 nvl(l_estab_rec.m4_total,0)+
555 nvl(l_estab_rec.m6_total,0)+
556 nvl(l_estab_rec.m7_total,0)+
557 nvl(l_estab_rec.m8_total,0)+
558 nvl(l_estab_rec.m9_total,0);
559 --
560 l_estab_rec.m_grand_total := nvl(l_estab_rec.m1_total,0)+
561 nvl(l_estab_rec.m2_total,0)+
562 nvl(l_estab_rec.m3_total,0)+
563 nvl(l_estab_rec.m4_total,0)+
564 nvl(l_estab_rec.m5_total,0)+
565 nvl(l_estab_rec.m6_total,0)+
566 nvl(l_estab_rec.m7_total,0)+
567 nvl(l_estab_rec.m8_total,0)+
568 nvl(l_estab_rec.m9_total,0);
569 --
570 l_estab_rec.n_grand_total := nvl(l_estab_rec.n1_total,0)+
571 nvl(l_estab_rec.n2_total,0)+
572 nvl(l_estab_rec.n3_total,0)+
573 nvl(l_estab_rec.n4_total,0)+
574 nvl(l_estab_rec.n5_total,0)+
575 nvl(l_estab_rec.n6_total,0)+
576 nvl(l_estab_rec.n7_total,0)+
577 nvl(l_estab_rec.n8_total,0)+
578 nvl(l_estab_rec.n9_total,0);
579 --
580 l_estab_rec.o_grand_total := nvl(l_estab_rec.o1_total,0)+
581 nvl(l_estab_rec.o2_total,0)+
582 nvl(l_estab_rec.o3_total,0)+
583 nvl(l_estab_rec.o4_total,0)+
584 nvl(l_estab_rec.o5_total,0)+
585 nvl(l_estab_rec.o6_total,0)+
586 nvl(l_estab_rec.o7_total,0)+
587 nvl(l_estab_rec.o8_total,0)+
588 nvl(l_estab_rec.o9_total,0);
589 --
590 l_estab_rec.p_grand_total := nvl(l_estab_rec.p1_total,0)+
591 nvl(l_estab_rec.p2_total,0)+
592 nvl(l_estab_rec.p3_total,0)+
593 nvl(l_estab_rec.p4_total,0)+
594 nvl(l_estab_rec.p5_total,0)+
595 nvl(l_estab_rec.p6_total,0)+
596 nvl(l_estab_rec.p7_total,0)+
597 nvl(l_estab_rec.p8_total,0)+
598 nvl(l_estab_rec.p9_total,0);
599 --
600 l_estab_rec.q_grand_total := nvl(l_estab_rec.q1_total,0)+
601 nvl(l_estab_rec.q2_total,0)+
602 nvl(l_estab_rec.q3_total,0)+
603 nvl(l_estab_rec.q4_total,0)+
604 nvl(l_estab_rec.q5_total,0)+
605 nvl(l_estab_rec.q6_total,0)+
606 nvl(l_estab_rec.q7_total,0)+
607 nvl(l_estab_rec.q8_total,0)+
608 nvl(l_estab_rec.q9_total,0);
609 --
610 l_estab_rec.r_grand_total := nvl(l_estab_rec.r1_total,0)+
611 nvl(l_estab_rec.r2_total,0)+
612 nvl(l_estab_rec.r3_total,0)+
613 nvl(l_estab_rec.r4_total,0)+
614 nvl(l_estab_rec.r5_total,0)+
615 nvl(l_estab_rec.r6_total,0)+
616 nvl(l_estab_rec.r7_total,0)+
617 nvl(l_estab_rec.r8_total,0)+
618 nvl(l_estab_rec.r9_total,0);
619 --
620 l_estab_rec.s_grand_total := nvl(l_estab_rec.s1_total,0)+
621 nvl(l_estab_rec.s2_total,0)+
622 nvl(l_estab_rec.s3_total,0)+
623 nvl(l_estab_rec.s4_total,0)+
624 nvl(l_estab_rec.s5_total,0)+
625 nvl(l_estab_rec.s6_total,0)+
626 nvl(l_estab_rec.s7_total,0)+
627 nvl(l_estab_rec.s8_total,0)+
628 nvl(l_estab_rec.s9_total,0);
629 --
630 -- This means we are dealing with a state consolidated report
631 -- which means we have to do some clever processing
632 --
633 if l_org_rec.form_type is null then
634 --
635 -- we need to add the new totals to the consolidate totals
636 --
637 l_consol_rec.state := l_estab_rec.state;
638 l_consol_rec.max_count := nvl(l_consol_rec.max_count,0)+
639 l_estab_rec.max_count;
640 l_consol_rec.min_count := nvl(l_consol_rec.min_count,0)+
641 l_estab_rec.min_count;
642 l_consol_rec.l_grand_total := nvl(l_consol_rec.l_grand_total,0)+
643 l_estab_rec.l_grand_total;
644 l_consol_rec.m_grand_total := nvl(l_consol_rec.m_grand_total,0)+
645 l_estab_rec.m_grand_total;
646 l_consol_rec.n_grand_total := nvl(l_consol_rec.n_grand_total,0)+
647 l_estab_rec.n_grand_total;
648 l_consol_rec.o_grand_total := nvl(l_consol_rec.o_grand_total,0)+
649 l_estab_rec.o_grand_total;
653 l_estab_rec.q_grand_total;
650 l_consol_rec.p_grand_total := nvl(l_consol_rec.p_grand_total,0)+
651 l_estab_rec.p_grand_total;
652 l_consol_rec.q_grand_total := nvl(l_consol_rec.q_grand_total,0)+
654 l_consol_rec.r_grand_total := nvl(l_consol_rec.r_grand_total,0)+
655 l_estab_rec.r_grand_total;
656 l_consol_rec.s_grand_total := nvl(l_consol_rec.s_grand_total,0)+
657 l_estab_rec.s_grand_total;
658 l_consol_rec.l1_total := nvl(l_consol_rec.l1_total,0)+
659 nvl(l_estab_rec.l1_total,0);
660 l_consol_rec.l2_total := nvl(l_consol_rec.l2_total,0)+
661 nvl(l_estab_rec.l2_total,0);
662 l_consol_rec.l3_total := nvl(l_consol_rec.l3_total,0)+
663 nvl(l_estab_rec.l3_total,0);
664 l_consol_rec.l4_total := nvl(l_consol_rec.l4_total,0)+
665 nvl(l_estab_rec.l4_total,0);
666 l_consol_rec.l5_total := nvl(l_consol_rec.l5_total,0)+
667 nvl(l_estab_rec.l5_total,0);
668 l_consol_rec.l6_total := nvl(l_consol_rec.l6_total,0)+
669 nvl(l_estab_rec.l6_total,0);
670 l_consol_rec.l7_total := nvl(l_consol_rec.l7_total,0)+
671 nvl(l_estab_rec.l7_total,0);
672 l_consol_rec.l8_total := nvl(l_consol_rec.l8_total,0)+
673 nvl(l_estab_rec.l8_total,0);
674 l_consol_rec.l9_total := nvl(l_consol_rec.l9_total,0)+
675 nvl(l_estab_rec.l9_total,0);
676 l_consol_rec.m1_total := nvl(l_consol_rec.m1_total,0)+
677 nvl(l_estab_rec.m1_total,0);
678 l_consol_rec.m2_total := nvl(l_consol_rec.m2_total,0)+
679 nvl(l_estab_rec.m2_total,0);
680 l_consol_rec.m3_total := nvl(l_consol_rec.m3_total,0)+
681 nvl(l_estab_rec.m3_total,0);
682 l_consol_rec.m4_total := nvl(l_consol_rec.m4_total,0)+
683 nvl(l_estab_rec.m4_total,0);
684 l_consol_rec.m5_total := nvl(l_consol_rec.m5_total,0)+
685 nvl(l_estab_rec.m5_total,0);
686 l_consol_rec.m6_total := nvl(l_consol_rec.m6_total,0)+
687 nvl(l_estab_rec.m6_total,0);
688 l_consol_rec.m7_total := nvl(l_consol_rec.m7_total,0)+
689 nvl(l_estab_rec.m7_total,0);
690 l_consol_rec.m8_total := nvl(l_consol_rec.m8_total,0)+
691 nvl(l_estab_rec.m8_total,0);
692 l_consol_rec.m9_total := nvl(l_consol_rec.m9_total,0)+
693 nvl(l_estab_rec.m9_total,0);
694 l_consol_rec.n1_total := nvl(l_consol_rec.n1_total,0)+
695 nvl(l_estab_rec.n1_total,0);
696 l_consol_rec.n2_total := nvl(l_consol_rec.n2_total,0)+
697 nvl(l_estab_rec.n2_total,0);
698 l_consol_rec.n3_total := nvl(l_consol_rec.n3_total,0)+
699 nvl(l_estab_rec.n3_total,0);
700 l_consol_rec.n4_total := nvl(l_consol_rec.n4_total,0)+
701 nvl(l_estab_rec.n4_total,0);
702 l_consol_rec.n5_total := nvl(l_consol_rec.n5_total,0)+
703 nvl(l_estab_rec.n5_total,0);
704 l_consol_rec.n6_total := nvl(l_consol_rec.n6_total,0)+
705 nvl(l_estab_rec.n6_total,0);
706 l_consol_rec.n7_total := nvl(l_consol_rec.n7_total,0)+
707 nvl(l_estab_rec.n7_total,0);
708 l_consol_rec.n8_total := nvl(l_consol_rec.n8_total,0)+
709 nvl(l_estab_rec.n8_total,0);
710 l_consol_rec.n9_total := nvl(l_consol_rec.n9_total,0)+
711 nvl(l_estab_rec.n9_total,0);
712 l_consol_rec.o1_total := nvl(l_consol_rec.o1_total,0)+
713 nvl(l_estab_rec.o1_total,0);
714 l_consol_rec.o2_total := nvl(l_consol_rec.o2_total,0)+
715 nvl(l_estab_rec.o2_total,0);
716 l_consol_rec.o3_total := nvl(l_consol_rec.o3_total,0)+
717 nvl(l_estab_rec.o3_total,0);
718 l_consol_rec.o4_total := nvl(l_consol_rec.o4_total,0)+
719 nvl(l_estab_rec.o4_total,0);
720 l_consol_rec.o5_total := nvl(l_consol_rec.o5_total,0)+
721 nvl(l_estab_rec.o5_total,0);
722 l_consol_rec.o6_total := nvl(l_consol_rec.o6_total,0)+
723 nvl(l_estab_rec.o6_total,0);
724 l_consol_rec.o7_total := nvl(l_consol_rec.o7_total,0)+
725 nvl(l_estab_rec.o7_total,0);
726 l_consol_rec.o8_total := nvl(l_consol_rec.o8_total,0)+
727 nvl(l_estab_rec.o8_total,0);
728 l_consol_rec.o9_total := nvl(l_consol_rec.o9_total,0)+
729 nvl(l_estab_rec.o9_total,0);
730 l_consol_rec.p1_total := nvl(l_consol_rec.p1_total,0)+
731 nvl(l_estab_rec.p1_total,0);
732 l_consol_rec.p2_total := nvl(l_consol_rec.p2_total,0)+
733 nvl(l_estab_rec.p2_total,0);
734 l_consol_rec.p3_total := nvl(l_consol_rec.p3_total,0)+
735 nvl(l_estab_rec.p3_total,0);
736 l_consol_rec.p4_total := nvl(l_consol_rec.p4_total,0)+
737 nvl(l_estab_rec.p4_total,0);
738 l_consol_rec.p5_total := nvl(l_consol_rec.p5_total,0)+
739 nvl(l_estab_rec.p5_total,0);
740 l_consol_rec.p6_total := nvl(l_consol_rec.p6_total,0)+
741 nvl(l_estab_rec.p6_total,0);
745 nvl(l_estab_rec.p8_total,0);
742 l_consol_rec.p7_total := nvl(l_consol_rec.p7_total,0)+
743 nvl(l_estab_rec.p7_total,0);
744 l_consol_rec.p8_total := nvl(l_consol_rec.p8_total,0)+
746 l_consol_rec.p9_total := nvl(l_consol_rec.p9_total,0)+
747 nvl(l_estab_rec.p9_total,0);
748 l_consol_rec.q1_total := nvl(l_consol_rec.q1_total,0)+
749 nvl(l_estab_rec.q1_total,0);
750 l_consol_rec.q2_total := nvl(l_consol_rec.q2_total,0)+
751 nvl(l_estab_rec.q2_total,0);
752 l_consol_rec.q3_total := nvl(l_consol_rec.q3_total,0)+
753 nvl(l_estab_rec.q3_total,0);
754 l_consol_rec.q4_total := nvl(l_consol_rec.q4_total,0)+
755 nvl(l_estab_rec.q4_total,0);
756 l_consol_rec.q5_total := nvl(l_consol_rec.q5_total,0)+
757 nvl(l_estab_rec.q5_total,0);
758 l_consol_rec.q6_total := nvl(l_consol_rec.q6_total,0)+
759 nvl(l_estab_rec.q6_total,0);
760 l_consol_rec.q7_total := nvl(l_consol_rec.q7_total,0)+
761 nvl(l_estab_rec.q7_total,0);
762 l_consol_rec.q8_total := nvl(l_consol_rec.q8_total,0)+
763 nvl(l_estab_rec.q8_total,0);
764 l_consol_rec.q9_total := nvl(l_consol_rec.q9_total,0)+
765 nvl(l_estab_rec.q9_total,0);
766 l_consol_rec.r1_total := nvl(l_consol_rec.r1_total,0)+
767 nvl(l_estab_rec.r1_total,0);
768 l_consol_rec.r2_total := nvl(l_consol_rec.r2_total,0)+
769 nvl(l_estab_rec.r2_total,0);
770 l_consol_rec.r3_total := nvl(l_consol_rec.r3_total,0)+
771 nvl(l_estab_rec.r3_total,0);
772 l_consol_rec.r4_total := nvl(l_consol_rec.r4_total,0)+
773 nvl(l_estab_rec.r4_total,0);
774 l_consol_rec.r5_total := nvl(l_consol_rec.r5_total,0)+
775 nvl(l_estab_rec.r5_total,0);
776 l_consol_rec.r6_total := nvl(l_consol_rec.r6_total,0)+
777 nvl(l_estab_rec.r6_total,0);
778 l_consol_rec.r7_total := nvl(l_consol_rec.r7_total,0)+
779 nvl(l_estab_rec.r7_total,0);
780 l_consol_rec.r8_total := nvl(l_consol_rec.r8_total,0)+
781 nvl(l_estab_rec.r8_total,0);
782 l_consol_rec.r9_total := nvl(l_consol_rec.r9_total,0)+
783 nvl(l_estab_rec.r9_total,0);
784 l_consol_rec.s1_total := nvl(l_consol_rec.s1_total,0)+
785 nvl(l_estab_rec.s1_total,0);
786 l_consol_rec.s2_total := nvl(l_consol_rec.s2_total,0)+
787 nvl(l_estab_rec.s2_total,0);
788 l_consol_rec.s3_total := nvl(l_consol_rec.s3_total,0)+
789 nvl(l_estab_rec.s3_total,0);
790 l_consol_rec.s4_total := nvl(l_consol_rec.s4_total,0)+
791 nvl(l_estab_rec.s4_total,0);
792 l_consol_rec.s5_total := nvl(l_consol_rec.s5_total,0)+
793 nvl(l_estab_rec.s5_total,0);
794 l_consol_rec.s6_total := nvl(l_consol_rec.s6_total,0)+
795 nvl(l_estab_rec.s6_total,0);
796 l_consol_rec.s7_total := nvl(l_consol_rec.s7_total,0)+
797 nvl(l_estab_rec.s7_total,0);
798 l_consol_rec.s8_total := nvl(l_consol_rec.s8_total,0)+
799 nvl(l_estab_rec.s8_total,0);
800 l_consol_rec.s9_total := nvl(l_consol_rec.s9_total,0)+
801 nvl(l_estab_rec.s9_total,0);
802 --
803 l_org_rec.number_of_estabs := nvl(l_org_rec.number_of_estabs,0)+1;
804 return;
805 --
806 end if;
807 --
808 l_string := l_org_rec.company_number||','||
809 l_org_rec.ending_period||','||
810 l_org_rec.contractor_type||','||
811 l_org_rec.form_type||','||
812 l_org_rec.msc_number||','||
813 l_org_rec.parent_company||','||
814 l_org_rec.street||','||
815 l_org_rec.city||','||
816 l_org_rec.county||','||
817 l_org_rec.state||','||
818 l_org_rec.zip_code||','||
819 l_estab_rec.unit_number||','||
820 l_estab_rec.reporting_name||','||
821 l_estab_rec.street||','||
822 l_estab_rec.city||','||
823 l_estab_rec.county||','||
824 l_estab_rec.state||','||
825 l_estab_rec.zip_code||','||
826 nvl(l_estab_rec.naics,l_org_rec.naics)||','||
827 nvl(l_estab_rec.duns,l_org_rec.duns)||','||
828 nvl(l_estab_rec.ein,l_org_rec.ein)||','||
829 nvl(l_estab_rec.l1_total,0)||','||
830 nvl(l_estab_rec.m1_total,0)||','||
831 nvl(l_estab_rec.n1_total,0)||','||
832 nvl(l_estab_rec.o1_total,0)||','||
833 nvl(l_estab_rec.p1_total,0)||','||
834 nvl(l_estab_rec.q1_total,0)||','||
835 nvl(l_estab_rec.r1_total,0)||','||
836 nvl(l_estab_rec.s1_total,0)||','||
837 nvl(l_estab_rec.l2_total,0)||','||
838 nvl(l_estab_rec.m2_total,0)||','||
839 nvl(l_estab_rec.n2_total,0)||','||
840 nvl(l_estab_rec.o2_total,0)||','||
841 nvl(l_estab_rec.p2_total,0)||','||
842 nvl(l_estab_rec.q2_total,0)||','||
843 nvl(l_estab_rec.r2_total,0)||','||
847 nvl(l_estab_rec.n3_total,0)||','||
844 nvl(l_estab_rec.s2_total,0)||','||
845 nvl(l_estab_rec.l3_total,0)||','||
846 nvl(l_estab_rec.m3_total,0)||','||
848 nvl(l_estab_rec.o3_total,0)||','||
849 nvl(l_estab_rec.p3_total,0)||','||
850 nvl(l_estab_rec.q3_total,0)||','||
851 nvl(l_estab_rec.r3_total,0)||','||
852 nvl(l_estab_rec.s3_total,0)||','||
853 nvl(l_estab_rec.l4_total,0)||','||
854 nvl(l_estab_rec.m4_total,0)||','||
855 nvl(l_estab_rec.n4_total,0)||','||
856 nvl(l_estab_rec.o4_total,0)||','||
857 nvl(l_estab_rec.p4_total,0)||','||
858 nvl(l_estab_rec.q4_total,0)||','||
859 nvl(l_estab_rec.r4_total,0)||','||
860 nvl(l_estab_rec.s4_total,0)||','||
861 nvl(l_estab_rec.l5_total,0)||','||
862 nvl(l_estab_rec.m5_total,0)||','||
863 nvl(l_estab_rec.n5_total,0)||','||
864 nvl(l_estab_rec.o5_total,0)||','||
865 nvl(l_estab_rec.p5_total,0)||','||
866 nvl(l_estab_rec.q5_total,0)||','||
867 nvl(l_estab_rec.r5_total,0)||','||
868 nvl(l_estab_rec.s5_total,0)||','||
869 nvl(l_estab_rec.l6_total,0)||','||
870 nvl(l_estab_rec.m6_total,0)||','||
871 nvl(l_estab_rec.n6_total,0)||','||
872 nvl(l_estab_rec.o6_total,0)||','||
873 nvl(l_estab_rec.p6_total,0)||','||
874 nvl(l_estab_rec.q6_total,0)||','||
875 nvl(l_estab_rec.r6_total,0)||','||
876 nvl(l_estab_rec.s6_total,0)||','||
877 nvl(l_estab_rec.l7_total,0)||','||
878 nvl(l_estab_rec.m7_total,0)||','||
879 nvl(l_estab_rec.n7_total,0)||','||
880 nvl(l_estab_rec.o7_total,0)||','||
881 nvl(l_estab_rec.p7_total,0)||','||
882 nvl(l_estab_rec.q7_total,0)||','||
883 nvl(l_estab_rec.r7_total,0)||','||
884 nvl(l_estab_rec.s7_total,0)||','||
885 nvl(l_estab_rec.l8_total,0)||','||
886 nvl(l_estab_rec.m8_total,0)||','||
887 nvl(l_estab_rec.n8_total,0)||','||
888 nvl(l_estab_rec.o8_total,0)||','||
889 nvl(l_estab_rec.p8_total,0)||','||
890 nvl(l_estab_rec.q8_total,0)||','||
891 nvl(l_estab_rec.r8_total,0)||','||
892 nvl(l_estab_rec.s8_total,0)||','||
893 nvl(l_estab_rec.l9_total,0)||','||
894 nvl(l_estab_rec.m9_total,0)||','||
895 nvl(l_estab_rec.n9_total,0)||','||
896 nvl(l_estab_rec.o9_total,0)||','||
897 nvl(l_estab_rec.p9_total,0)||','||
898 nvl(l_estab_rec.q9_total,0)||','||
899 nvl(l_estab_rec.r9_total,0)||','||
900 nvl(l_estab_rec.s9_total,0)||','||
901 nvl(l_estab_rec.l_grand_total,0)||','||
902 nvl(l_estab_rec.m_grand_total,0)||','||
903 nvl(l_estab_rec.n_grand_total,0)||','||
904 nvl(l_estab_rec.o_grand_total,0)||','||
905 nvl(l_estab_rec.p_grand_total,0)||','||
906 nvl(l_estab_rec.q_grand_total,0)||','||
907 nvl(l_estab_rec.r_grand_total,0)||','||
908 nvl(l_estab_rec.s_grand_total,0)||','||
909 nvl(l_estab_rec.max_count,0)||','||
910 nvl(l_estab_rec.min_count,0);
911 --
912 l_org_rec.form_type := null;
913 l_month_rec := l_month_rec_blank;
914 --
915 fnd_file.put_line
916 (which => fnd_file.output,
917 buff => l_string);
918 --
919 end;
920 --
921 procedure loop_through_establishments(p_hierarchy_version_id in number,
922 p_business_group_id in number,
923 p_start_date in date,
924 p_end_date in date) is
925 --
926 l_hierarchy_node_id number;
927 --
928 -- orig cursor, cost of 9 as stands.
929 cursor c1 is
930 select upper(replace(hlei1.lei_information1,',',' ')) reporting_name,
931 hlei1.lei_information2 unit_number,
932 upper(replace(eloc.address_line_1,',',' ')||
933 ' '||
934 replace(eloc.address_line_2,',',' ')||
935 ' '||
936 replace(eloc.address_line_3,',',' ')) street,
937 upper(eloc.town_or_city) city,
938 upper(eloc.region_1) county,
939 upper(eloc.region_2) state,
940 eloc.postal_code zip_code,
941 hlei2.lei_information4 naics,
942 hlei2.lei_information2 duns,
943 hlei2.lei_information6 ein,
944 hlei2.lei_information10 hq,
945 eloc.location_id,
946 pghn.hierarchy_node_id
947 from per_gen_hierarchy_nodes pghn,
948 hr_location_extra_info hlei1,
949 hr_location_extra_info hlei2,
950 hr_locations_all eloc
951 where pghn.hierarchy_version_id = p_hierarchy_version_id
952 and pghn.node_type = 'EST'
953 and eloc.location_id = pghn.entity_id
954 and hlei1.location_id = pghn.entity_id
955 and hlei1.location_id = hlei2.location_id
956 and hlei1.information_type = 'VETS-100 Specific Information'
960 order by eloc.region_2,decode(hlei2.lei_information10,'Y',1,2);
957 and hlei1.lei_information_category= 'VETS-100 Specific Information'
958 and hlei2.information_type = 'Establishment Information'
959 and hlei2.lei_information_category= 'Establishment Information'
961 --
962 l_c1 c1%rowtype;
963 --
964 --Modified for Bug# 6759680
965 cursor c2 is
966 /* old cost 453
967 select nvl(count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
968 'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNS',1,
969 'VIETDISNSOP',1,null)),0) no_dis_vets,
970 nvl(count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
971 'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
972 'VIETDISNSOP',1,null)),0) no_viet_vets,
973 nvl(count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
974 'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
975 null)),0) no_other_vets,
976 hrl.lookup_code
977 from per_periods_of_service pds,
978 per_people_f peo,
979 per_assignments_f ass,
980 hr_organization_information hoi1,
981 hr_organization_information hoi2,
982 per_jobs job,
983 hr_lookups hrl
984 where (pds.date_start <= p_end_date
985 and nvl(pds.actual_termination_date,hr_api.g_eot) >= p_end_date
986 or pds.date_start between p_start_date and p_end_date
987 and p_end_date between pds.date_start and nvl(pds.actual_termination_date,hr_api.g_eot))
988 and pds.person_id = ass.person_id
989 and peo.person_id = ass.person_id
990 and job.date_from <= p_end_date
991 and nvl(job.date_to,hr_api.g_eot) >= p_start_date
992 and job.job_information1 = hrl.lookup_code
993 and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
994 and ass.job_id = job.job_id
995 and peo.effective_start_date <= p_end_date
996 and peo.effective_end_date >= p_start_date
997 and peo.current_employee_flag = 'Y'
998 and ass.assignment_type = 'E'
999 and ass.primary_flag = 'Y'
1000 and ass.effective_start_date =
1001 (select max(paf2.effective_start_date)
1002 from per_assignments_f paf2
1003 where paf2.person_id = ass.person_id
1004 and paf2.primary_flag = 'Y'
1005 and paf2.assignment_type = 'E'
1006 and paf2.effective_start_date <= p_end_date)
1007 and peo.effective_start_date =
1008 (select max(peo2.effective_start_date)
1009 from per_people_f peo2
1010 where peo2.person_id = peo.person_id
1011 and peo.current_employee_flag = 'Y'
1012 and peo2.effective_start_date < p_end_date)
1013 and to_char(ass.assignment_status_type_id) = hoi1.org_information1
1014 and hoi1.org_information_context = 'Reporting Statuses'
1015 and hoi1.organization_id = p_business_group_id
1016 and ass.employment_category = hoi2.org_information1
1017 and hoi2.organization_id = p_business_group_id
1018 and hoi2.org_information_context = 'Reporting Categories'
1019 and ass.effective_start_date <= p_end_date
1020 and ass.effective_end_date >= p_start_date
1021 and to_char(ass.location_id) in
1022 (select entity_id
1023 from per_gen_hierarchy_nodes
1024 where hierarchy_version_id = p_hierarchy_version_id
1025 and (hierarchy_node_id = l_hierarchy_node_id
1026 or parent_hierarchy_node_id = l_hierarchy_node_id))
1027 group by hrl.lookup_code; */
1028
1029 -- new; cost 169
1030
1031 select nvl(
1032 count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
1033 'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNS',1,
1034 'VIETDISNSOP',1,'AFSMDIS',1,'AFSMDISOP',1,'AFSMDISNS',1,
1035 'AFSMNSDISOP',1,'AFSMDISVIET',1,'AFSMVIETNSDIS',1,'AFSMVIETOPDIS'
1036 ,1,'AFSMVIETNSDISOP',1,null))
1037 ,0)
1038 no_dis_vets,
1039 nvl(
1040 count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
1041 'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
1042 'VIETDISNSOP',1,'AFSMVIET',1,'AFSMVIETOP',1,'AFSMNSVIET',1,
1043 'AFSMVIETNSOP',1,'AFSMDISVIET',1,'AFSMVIETNSDIS',1,'AFSMVIETOPDIS',1,
1044 'AFSMVIETNSDISOP',1,null))
1045 ,0)
1046 no_viet_vets,
1047 nvl(
1048 count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
1049 'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
1050 'AFSMOP',1,'AFSMDISOP',1,'AFSMNSOP',1,'AFSMNSDISOP', 1,'AFSMVIETNSOP',1,
1051 'AFSMVIETOPDIS',1,'AFSMVIETNSDISOP',1,'AFSMVIETOP',1,
1052 null))
1053 ,0)
1054 no_other_vets,
1055 decode(hrl.lookup_code,10,1,hrl.lookup_code) lookup_code
1056 from per_periods_of_service pds,
1057 per_all_people_f peo,
1058 per_all_assignments_f ass,
1059 hr_organization_information hoi1,
1060 hr_organization_information hoi2,
1061 per_jobs job,
1062 hr_lookups hrl
1066 and pds.person_id = ass.person_id
1063 where
1064 pds.date_start <= p_end_date
1065 and nvl(pds.actual_termination_date,p_end_date + 1) >= p_end_date
1067 and peo.person_id = ass.person_id
1068 -- Bug# 5000214
1069 --and job.date_from <= p_end_date
1070 --and nvl(job.date_to,(p_end_date + 1)) >= p_start_date
1071 and p_end_date between job.date_from and nvl(job.date_to, p_end_date)
1072 and job.job_information1 = hrl.lookup_code
1073 and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
1074 and ass.job_id = job.job_id
1075 -- Bug# 5000214
1076 --and peo.effective_start_date <= p_end_date
1077 --and peo.effective_end_date >= p_start_date
1078 and p_end_date between peo.effective_start_date and peo.effective_end_date
1079 and peo.current_employee_flag = 'Y'
1080 and ass.assignment_type = 'E'
1081 and ass.primary_flag = 'Y'
1082 --
1083 and ass.effective_start_date =
1084 (select max(paf2.effective_start_date)
1085 from per_all_assignments_f paf2
1086 where paf2.person_id = ass.person_id
1087 and paf2.primary_flag = 'Y'
1088 and paf2.assignment_type = 'E'
1089 and paf2.effective_start_date <= p_end_date
1090 )
1091 --
1092 and peo.effective_start_date =
1093 (select max(peo2.effective_start_date)
1094 from per_all_people_f peo2
1095 where peo2.person_id = peo.person_id
1096 and peo.current_employee_flag = 'Y'
1097 and peo2.effective_start_date < p_end_date
1098 )
1099 --
1100 and to_char(ass.assignment_status_type_id) = hoi1.org_information1
1101 and hoi1.org_information_context = 'Reporting Statuses'
1102 and hoi1.organization_id = p_business_group_id
1103 and ass.employment_category = hoi2.org_information1
1104 and hoi2.organization_id = p_business_group_id
1105 and hoi2.org_information_context = 'Reporting Categories'
1106 -- Bug 5000214
1107 --and ass.effective_start_date <= p_end_date
1108 --and ass.effective_end_date >= p_start_date
1109 and p_end_date between ass.effective_start_date and ass.effective_end_date
1110 --
1111 and ass.location_id in
1112 (select entity_id
1113 from per_gen_hierarchy_nodes
1114 where hierarchy_version_id = p_hierarchy_version_id
1115 and (hierarchy_node_id = l_hierarchy_node_id
1116 or parent_hierarchy_node_id = l_hierarchy_node_id
1117 ))
1118 group by decode(hrl.lookup_code,10,1,hrl.lookup_code);
1119
1120 --
1121 l_c2 c2%rowtype;
1122 --
1123 cursor c3 is
1124 -----
1125 /* old c3: cost 448
1126 select count(decode(peo.per_information5,'NOTVET',1,NULL,1,
1127 null)) tot_new_hires,
1128 count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
1129 'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNSOP',1,
1130 'VIETDISNS',1,null)) no_nh_dis_vets,
1131 count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
1132 'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
1133 'VIETDISNSOP',1,null)) no_nh_viet_vets,
1134 count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
1135 'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
1136 null)) no_nh_other_vets,
1137 count(decode(peo.per_information5,'NS',1,'NSDIS',1,'NSOP',1,
1138 'NSDISOP',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
1139 'VIETDISNSOP',1,null)) no_nh_newly_sep_vets,
1140 hrl.lookup_code
1141 from per_periods_of_service pds,
1142 per_people_f peo,
1143 per_assignments_f ass,
1144 hr_organization_information hoi1,
1145 hr_organization_information hoi2,
1146 per_jobs job,
1147 hr_lookups hrl
1148 where peo.person_id = ass.person_id
1149 and peo.current_employee_flag = 'Y'
1150 and pds.date_start
1151 between p_start_date
1152 and p_end_date
1153 and pds.person_id = ass.person_id
1154 and ass.assignment_type = 'E'
1155 and ass.primary_flag = 'Y'
1156 and ass.effective_start_date <= p_end_date
1157 and ass.effective_end_date >= p_start_date
1158 and ass.effective_start_date =
1159 (select max(paf2.effective_start_date)
1160 from per_assignments_f paf2
1161 where paf2.person_id = ass.person_id
1162 and paf2.primary_flag = 'Y'
1163 and paf2.assignment_type = 'E'
1164 and paf2.effective_start_date <= p_end_date)
1165 and peo.effective_start_date =
1166 (select max(peo2.effective_start_date)
1167 from per_people_f peo2
1168 where peo2.person_id = peo.person_id
1169 and peo2.current_employee_flag = 'Y'
1170 and peo2.effective_start_date <= p_end_date)
1171 and to_char(ass.assignment_status_type_id) = hoi1.org_information1
1172 and hoi1.org_information_context = 'Reporting Statuses'
1173 and hoi1.organization_id = p_business_group_id
1174 and ass.employment_category = hoi2.org_information1
1178 and job.job_information_category = 'US'
1175 and hoi2.org_information_context = 'Reporting Categories'
1176 and hoi2.organization_id = p_business_group_id
1177 and ass.job_id = job.job_id
1179 and job.date_from <= p_end_date
1180 and nvl(job.date_to,hr_api.g_eot) >= p_start_date
1181 and job.job_information1 = hrl.lookup_code
1182 and hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
1183 and to_char(ass.location_id) in
1184 (select entity_id
1185 from per_gen_hierarchy_nodes
1186 where hierarchy_version_id = p_hierarchy_version_id
1187 and (hierarchy_node_id = l_hierarchy_node_id
1188 or parent_hierarchy_node_id = l_hierarchy_node_id))
1189 group by hrl.lookup_code;
1190 */
1191 -- new more performant c3: (cost 122)
1192 -- cursor to retrieve new hires
1193 -- The following query is modified for the bugs# 5000214 and 5608926
1194 --Modified for Bug# 6759680
1195 select count(decode(peo.per_information5,'NOTVET',1,NULL,1,
1196 null)) tot_new_hires,
1197 count(decode(peo.per_information5,'VETDIS',1,'VIETVETDIS',1,
1198 'OTEDV',1,'DVOEV',1,'NSDIS',1,'NSDISOP',1,'VIETDISNSOP',1,
1199 'VIETDISNS',1,'AFSMDIS',1,'AFSMDISOP',1,'AFSMDISNS',1,
1200 'AFSMNSDISOP',1,'AFSMDISVIET',1, 'AFSMVIETNSDIS',1,
1201 'AFSMVIETOPDIS',1,'AFSMVIETNSDISOP',1,null)) no_nh_dis_vets,
1202 count(decode(peo.per_information5,'VIETVET',1,'VIETVETDIS',1,
1203 'DVOEV',1,'VOEVV',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
1204 'VIETDISNSOP',1,'AFSMVIET',1,'AFSMVIETOP',1,'AFSMNSVIET',1,
1205 'AFSMVIETNSOP',1,'AFSMDISVIET',1,'AFSMVIETNSDIS',1,'AFSMVIETOPDIS',1,
1206 'AFSMVIETNSDISOP',1,null)) no_nh_viet_vets,
1207 count(decode(peo.per_information5,'OTEV',1,'OTEDV',1,'DVOEV',1,
1208 'VOEVV',1,'NSOP',1,'NSDISOP',1,'VIETNSOP',1,'VIETDISNSOP',1,
1209 'AFSMOP',1,'AFSMDISOP',1,'AFSMNSOP',1,'AFSMNSDISOP',1,'AFSMVIETNSOP',1,
1210 'AFSMVIETOPDIS',1,'AFSMVIETNSDISOP',1,'AFSMVIETOP',1,
1211 null)) no_nh_other_vets,
1212 count(decode(peo.per_information5,'NS',1,'NSDIS',1,'NSOP',1,
1213 'NSDISOP',1,'VIETNS',1,'VIETDISNS',1,'VIETNSOP',1,
1214 'VIETDISNSOP',1,'AFSMNS',1,'AFSMDISNS',1,'AFSMNSOP',1,
1215 'AFSMNSVIET',1,'AFSMNSDISOP',1,'AFSMVIETNSOP',1,'AFSMVIETNSDIS',1,
1216 'AFSMVIETNSDISOP',1,null)) no_nh_newly_sep_vets,
1217 decode(hrl.lookup_code,'10','1',hrl.lookup_code) lookup_code
1218 FROM per_all_people_f peo,
1219 per_all_assignments_f ass,
1220 per_jobs job,
1221 hr_lookups hrl,
1222 per_periods_of_service pps
1223 WHERE peo.person_id = ass.person_id
1224 AND peo.person_id = pps.person_id
1225 AND peo.business_group_id = p_business_group_id
1226 AND ass.business_group_id = p_business_group_id
1227 AND job.business_group_id = p_business_group_id
1228 AND pps.business_group_id = p_business_group_id
1229 AND ass.job_id = job.job_id
1230 AND peo.current_employee_flag = 'Y'
1231 AND ass.assignment_type = 'E'
1232 AND ass.primary_flag = 'Y'
1233 AND job.job_information_category = 'US'
1234 AND ass.effective_start_date <= p_end_date
1235 AND job.job_information1 = hrl.lookup_code
1236 AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
1237 AND ass.effective_start_date = (select max(paf2.effective_start_date)
1238 from per_all_assignments_f paf2
1239 where paf2.person_id = ass.person_id
1240 and paf2.assignment_id = ass.assignment_id
1241 and paf2.effective_start_date = peo.effective_start_date
1242 and paf2.primary_flag = 'Y'
1243 and paf2.assignment_type = 'E'
1244 and paf2.effective_start_date <= p_end_date)
1245 AND months_between (p_end_date,pps.date_start) <= 12
1246 AND months_between (p_end_date,pps.date_start) >= 0
1247 AND peo.effective_start_date = pps.date_start
1248 AND EXISTS (
1249 SELECT 'X'
1250 FROM HR_ORGANIZATION_INFORMATION HOI1,
1251 HR_ORGANIZATION_INFORMATION HOI2
1252 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1253 AND hoi1.org_information_context = 'Reporting Statuses'
1254 AND hoi1.organization_id = p_business_group_id
1255 AND ass.employment_category = hoi2.org_information1
1256 AND hoi2.organization_id = p_business_group_id
1257 AND hoi2.org_information_context = 'Reporting Categories'
1258 AND hoi1.organization_id = hoi2.organization_id)
1259 and ass.location_id in
1260 (select entity_id
1261 from per_gen_hierarchy_nodes
1262 where hierarchy_version_id = p_hierarchy_version_id
1263 and (hierarchy_node_id = l_hierarchy_node_id
1264 or parent_hierarchy_node_id = l_hierarchy_node_id
1265 ))
1266 group by decode(hrl.lookup_code,'10','1',hrl.lookup_code);
1267 --
1268 l_c3 c3%rowtype;
1269 -- Bug# 5000214
1270 --l_start_date date := p_start_date;
1274 /*
1271 --
1272 -- cursor c_min_max is
1273 /* old version taking a long time to run on cust sites */
1275 select count(*) num_people
1276 from per_people_f ppf,
1277 per_assignments_f paf
1278 where ppf.person_id = paf.person_id
1279 and ppf.business_group_id = p_business_group_id
1280 and ppf.current_employee_flag = 'Y'
1281 and paf.primary_flag = 'Y'
1282 and paf.assignment_type = 'E'
1283 and l_start_date
1284 between paf.effective_start_date
1285 and paf.effective_end_date
1286 and l_start_date
1287 between ppf.effective_start_date
1288 and ppf.effective_end_date
1289 and to_char(paf.location_id) in
1290 (select entity_id
1291 from per_gen_hierarchy_nodes
1292 where hierarchy_version_id = p_hierarchy_version_id
1293 and (hierarchy_node_id = l_hierarchy_node_id
1294 or parent_hierarchy_node_id = l_hierarchy_node_id));
1295 */
1296 --
1297 -- This one is quicker, more accurate and has cost of just 102
1298 /* BUT this doesn't pick up correct emp number 18-FEB-2005
1299 cursor c_min_max is
1300 select count ('x') num_people
1301 from per_all_assignments_f ass,
1302 hr_organization_information hoi1,
1303 hr_organization_information hoi2
1304 where ass.assignment_type = 'e'
1305 and ass.primary_flag = 'y'
1306 and l_month_start_date between ass.effective_start_date and ass.effective_end_date
1307 and ass.business_group_id = p_business_group_id
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.org_information_context = 'REPORTING CATEGORIES'
1314 and hoi2.organization_id = p_business_group_id
1315 --
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 */
1324 --
1325 -- This is new c_min_max cursor 115.11
1326 --
1327 l_month_start_date date := null;
1328 l_month_end_date date := null;
1329 cursor c_min_max is
1330 SELECT count(*) num_people
1331 FROM per_all_assignments_f paf
1332 WHERE paf.business_group_id = p_business_group_id
1333 AND paf.primary_flag = 'Y'
1334 AND paf.assignment_type = 'E'
1335 -- The following condition is modified for the bug# 5000214
1336 AND l_month_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date
1337 -- The following 2 condtions are added to sync with perusvts.rdf min-max cursor query.
1338 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1339 from per_all_assignments_f paf2
1340 where paf2.person_id = paf.person_id
1341 and paf2.primary_flag = 'Y'
1342 and paf2.assignment_type = 'E'
1343 and paf2.effective_start_date
1344 <= l_month_end_date
1345 )
1346 AND paf.business_group_id = p_business_group_id
1347 AND to_char(paf.location_id) in
1348 (SELECT entity_id
1349 FROM per_gen_hierarchy_nodes
1350 WHERE hierarchy_version_id = p_hierarchy_version_id
1351 AND (hierarchy_node_id = l_hierarchy_node_id
1352 OR parent_hierarchy_node_id = l_hierarchy_node_id
1353 ))
1354 AND EXISTS (
1355 SELECT 'X'
1356 FROM HR_ORGANIZATION_INFORMATION HOI1,
1357 HR_ORGANIZATION_INFORMATION HOI2
1358 WHERE TO_CHAR(paf.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1359 AND hoi1.org_information_context = 'Reporting Statuses'
1360 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
1361 AND paf.employment_category = hoi2.org_information1
1362 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
1363 AND hoi2.org_information_context = 'Reporting Categories');
1364 --
1365 l_min_max c_min_max%rowtype;
1366
1367 --
1368 -- Count total employee at the end of reporting period
1369 --
1370 cursor c_tot_emps is
1371 SELECT count(*) num_people
1372 FROM per_all_assignments_f paf
1373 WHERE paf.business_group_id = p_business_group_id
1374 AND paf.primary_flag = 'Y'
1375 AND paf.assignment_type = 'E'
1376 -- Bug# 5000214
1377 and p_end_date between paf.effective_start_date and paf.effective_end_date
1378 /*and paf.effective_start_date <= p_end_date
1379 and paf.effective_end_date >= p_start_date*/
1380 and paf.effective_start_date = (select max(paf2.effective_start_date)
1381 from per_all_assignments_f paf2
1382 where paf2.person_id = paf.person_id
1386 <= p_end_date)
1383 and paf2.primary_flag = 'Y'
1384 and paf2.assignment_type = 'E'
1385 and paf2.effective_start_date
1387 AND to_char(paf.location_id) in
1388 (SELECT entity_id
1389 FROM per_gen_hierarchy_nodes
1390 WHERE hierarchy_version_id = p_hierarchy_version_id
1391 AND (hierarchy_node_id = l_hierarchy_node_id
1392 OR parent_hierarchy_node_id = l_hierarchy_node_id
1393 ))
1394 AND EXISTS (
1395 SELECT 'X'
1396 FROM HR_ORGANIZATION_INFORMATION HOI1,
1397 HR_ORGANIZATION_INFORMATION HOI2
1398 WHERE TO_CHAR(paf.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1399 AND hoi1.org_information_context = 'Reporting Statuses'
1400 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
1401 AND paf.employment_category = hoi2.org_information1
1402 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
1403 AND hoi2.org_information_context = 'Reporting Categories');
1404
1405
1406 l_proc varchar2(40) := 'loop_through_establishments';
1407 --
1408 begin
1409 --
1410 hr_utility.set_location(l_proc,10);
1411 --
1412 open c1;
1413 --
1414 loop
1415 --
1416 fetch c1 into l_c1;
1417 exit when c1%notfound;
1418 --
1419 hr_utility.set_location(l_proc,20);
1420 hr_utility.trace('l_c1.street : ' || l_c1.street);
1421 l_estab_rec := l_estab_rec_blank;
1422 --
1423 l_hierarchy_node_id := l_c1.hierarchy_node_id;
1424 l_estab_rec.reporting_name := l_c1.reporting_name;
1425 l_estab_rec.unit_number := l_c1.unit_number;
1426 l_estab_rec.street := l_c1.street;
1427 l_estab_rec.city := l_c1.city;
1428 l_estab_rec.county := l_c1.county;
1429 l_estab_rec.state := l_c1.state;
1430 l_estab_rec.zip_code := l_c1.zip_code;
1431 l_estab_rec.naics := l_c1.naics;
1432 l_estab_rec.duns := l_c1.duns;
1433 l_estab_rec.ein := l_c1.ein;
1434 l_estab_rec.hq := l_c1.hq;
1435 --
1436 open c2;
1437 --
1438 loop
1439 --
1440 fetch c2 into l_c2;
1441 exit when c2%notfound;
1442 --
1443 hr_utility.set_location(l_proc,30);
1444 if l_c2.lookup_code = '1' then
1445 --
1446 l_estab_rec.l1_total := l_c2.no_dis_vets;
1447 l_estab_rec.m1_total := l_c2.no_viet_vets;
1448 l_estab_rec.n1_total := l_c2.no_other_vets;
1449 --
1450 elsif l_c2.lookup_code = '2' then
1451 --
1452 l_estab_rec.l2_total := l_c2.no_dis_vets;
1453 l_estab_rec.m2_total := l_c2.no_viet_vets;
1454 l_estab_rec.n2_total := l_c2.no_other_vets;
1455 --
1456 elsif l_c2.lookup_code = '3' then
1457 --
1458 l_estab_rec.l3_total := l_c2.no_dis_vets;
1459 l_estab_rec.m3_total := l_c2.no_viet_vets;
1460 l_estab_rec.n3_total := l_c2.no_other_vets;
1461 --
1462 elsif l_c2.lookup_code = '4' then
1463 --
1464 l_estab_rec.l4_total := l_c2.no_dis_vets;
1465 l_estab_rec.m4_total := l_c2.no_viet_vets;
1466 l_estab_rec.n4_total := l_c2.no_other_vets;
1467 --
1468 elsif l_c2.lookup_code = '5' then
1469 --
1470 l_estab_rec.l5_total := l_c2.no_dis_vets;
1471 l_estab_rec.m5_total := l_c2.no_viet_vets;
1472 l_estab_rec.n5_total := l_c2.no_other_vets;
1473 --
1474 elsif l_c2.lookup_code = '6' then
1475 --
1476 l_estab_rec.l6_total := l_c2.no_dis_vets;
1477 l_estab_rec.m6_total := l_c2.no_viet_vets;
1478 l_estab_rec.n6_total := l_c2.no_other_vets;
1479 --
1480 elsif l_c2.lookup_code = '7' then
1481 --
1482 l_estab_rec.l7_total := l_c2.no_dis_vets;
1483 l_estab_rec.m7_total := l_c2.no_viet_vets;
1484 l_estab_rec.n7_total := l_c2.no_other_vets;
1485 --
1486 elsif l_c2.lookup_code = '8' then
1487 --
1488 l_estab_rec.l8_total := l_c2.no_dis_vets;
1489 l_estab_rec.m8_total := l_c2.no_viet_vets;
1490 l_estab_rec.n8_total := l_c2.no_other_vets;
1491 --
1492 elsif l_c2.lookup_code = '9' then
1493 --
1494 l_estab_rec.l9_total := l_c2.no_dis_vets;
1495 l_estab_rec.m9_total := l_c2.no_viet_vets;
1496 l_estab_rec.n9_total := l_c2.no_other_vets;
1497 --
1498 end if;
1499 --
1500 end loop;
1501 --
1502 close c2;
1503 --
1504 open c3;
1505 --
1506 loop
1507 --
1508 fetch c3 into l_c3;
1509 exit when c3%notfound;
1510 --
1511 hr_utility.set_location(l_proc,40);
1512 if l_c3.lookup_code = '1' then
1513 --
1517 l_estab_rec.r1_total := l_c3.no_nh_other_vets;
1514 l_estab_rec.o1_total := l_c3.no_nh_dis_vets;
1515 l_estab_rec.p1_total := l_c3.no_nh_viet_vets;
1516 l_estab_rec.q1_total := l_c3.no_nh_newly_sep_vets;
1518 l_estab_rec.s1_total := l_c3.tot_new_hires+
1519 l_c3.no_nh_dis_vets+
1520 l_c3.no_nh_viet_vets+
1521 l_c3.no_nh_newly_sep_vets+
1522 l_c3.no_nh_other_vets;
1523 --
1524 elsif l_c3.lookup_code = '2' then
1525 --
1526 l_estab_rec.o2_total := l_c3.no_nh_dis_vets;
1527 l_estab_rec.p2_total := l_c3.no_nh_viet_vets;
1528 l_estab_rec.q2_total := l_c3.no_nh_newly_sep_vets;
1529 l_estab_rec.r2_total := l_c3.no_nh_other_vets;
1530 l_estab_rec.s2_total := l_c3.tot_new_hires+
1531 l_c3.no_nh_dis_vets+
1532 l_c3.no_nh_viet_vets+
1533 l_c3.no_nh_newly_sep_vets+
1534 l_c3.no_nh_other_vets;
1535 --
1536 elsif l_c3.lookup_code = '3' then
1537 --
1538 l_estab_rec.o3_total := l_c3.no_nh_dis_vets;
1539 l_estab_rec.p3_total := l_c3.no_nh_viet_vets;
1540 l_estab_rec.q3_total := l_c3.no_nh_newly_sep_vets;
1541 l_estab_rec.r3_total := l_c3.no_nh_other_vets;
1542 l_estab_rec.s3_total := l_c3.tot_new_hires+
1543 l_c3.no_nh_dis_vets+
1544 l_c3.no_nh_viet_vets+
1545 l_c3.no_nh_newly_sep_vets+
1546 l_c3.no_nh_other_vets;
1547 --
1548 elsif l_c3.lookup_code = '4' then
1549 --
1550 l_estab_rec.o4_total := l_c3.no_nh_dis_vets;
1551 l_estab_rec.p4_total := l_c3.no_nh_viet_vets;
1552 l_estab_rec.q4_total := l_c3.no_nh_newly_sep_vets;
1553 l_estab_rec.r4_total := l_c3.no_nh_other_vets;
1554 l_estab_rec.s4_total := l_c3.tot_new_hires+
1555 l_c3.no_nh_dis_vets+
1556 l_c3.no_nh_viet_vets+
1557 l_c3.no_nh_newly_sep_vets+
1558 l_c3.no_nh_other_vets;
1559 --
1560 elsif l_c3.lookup_code = '5' then
1561 --
1562 l_estab_rec.o5_total := l_c3.no_nh_dis_vets;
1563 l_estab_rec.p5_total := l_c3.no_nh_viet_vets;
1564 l_estab_rec.q5_total := l_c3.no_nh_newly_sep_vets;
1565 l_estab_rec.r5_total := l_c3.no_nh_other_vets;
1566 l_estab_rec.s5_total := l_c3.tot_new_hires+
1567 l_c3.no_nh_dis_vets+
1568 l_c3.no_nh_viet_vets+
1569 l_c3.no_nh_newly_sep_vets+
1570 l_c3.no_nh_other_vets;
1571 --
1572 elsif l_c3.lookup_code = '6' then
1573 --
1574 l_estab_rec.o6_total := l_c3.no_nh_dis_vets;
1575 l_estab_rec.p6_total := l_c3.no_nh_viet_vets;
1576 l_estab_rec.q6_total := l_c3.no_nh_newly_sep_vets;
1577 l_estab_rec.r6_total := l_c3.no_nh_other_vets;
1578 l_estab_rec.s6_total := l_c3.tot_new_hires+
1579 l_c3.no_nh_dis_vets+
1580 l_c3.no_nh_viet_vets+
1581 l_c3.no_nh_newly_sep_vets+
1582 l_c3.no_nh_other_vets;
1583 --
1584 elsif l_c3.lookup_code = '7' then
1585 --
1586 l_estab_rec.o7_total := l_c3.no_nh_dis_vets;
1587 l_estab_rec.p7_total := l_c3.no_nh_viet_vets;
1588 l_estab_rec.q7_total := l_c3.no_nh_newly_sep_vets;
1589 l_estab_rec.r7_total := l_c3.no_nh_other_vets;
1590 l_estab_rec.s7_total := l_c3.tot_new_hires+
1591 l_c3.no_nh_dis_vets+
1592 l_c3.no_nh_viet_vets+
1593 l_c3.no_nh_newly_sep_vets+
1594 l_c3.no_nh_other_vets;
1595 --
1596 elsif l_c3.lookup_code = '8' then
1597 --
1598 l_estab_rec.o8_total := l_c3.no_nh_dis_vets;
1599 l_estab_rec.p8_total := l_c3.no_nh_viet_vets;
1600 l_estab_rec.q8_total := l_c3.no_nh_newly_sep_vets;
1601 l_estab_rec.r8_total := l_c3.no_nh_other_vets;
1602 l_estab_rec.s8_total := l_c3.tot_new_hires+
1603 l_c3.no_nh_dis_vets+
1604 l_c3.no_nh_viet_vets+
1605 l_c3.no_nh_newly_sep_vets+
1606 l_c3.no_nh_other_vets;
1607 --
1608 elsif l_c3.lookup_code = '9' then
1609 --
1610 l_estab_rec.o9_total := l_c3.no_nh_dis_vets;
1611 l_estab_rec.p9_total := l_c3.no_nh_viet_vets;
1612 l_estab_rec.q9_total := l_c3.no_nh_newly_sep_vets;
1613 l_estab_rec.r9_total := l_c3.no_nh_other_vets;
1614 l_estab_rec.s9_total := l_c3.tot_new_hires+
1618 l_c3.no_nh_other_vets;
1615 l_c3.no_nh_dis_vets+
1616 l_c3.no_nh_viet_vets+
1617 l_c3.no_nh_newly_sep_vets+
1619 --
1620 end if;
1621 --
1622 end loop;
1623 --
1624 close c3;
1625 --
1626 hr_utility.trace('l_c1.state : ' || l_c1.state);
1627 hr_utility.trace('l_consol_rec.state : ' || l_consol_rec.state);
1628 if l_c1.state <> nvl(l_consol_rec.state,l_c1.state) then
1629 --
1630 -- Write out the old consolidated report and
1631 -- process the next record which is for a different state.
1632 --
1633 hr_utility.set_location(l_proc,50);
1634 write_consolidated_record;
1635 l_consol_rec := l_estab_rec_blank;
1636 l_month_rec := l_month_rec_blank;
1637 --
1638 end if;
1639 --
1640
1641 --
1642 open c_tot_emps;
1643 fetch c_tot_emps into l_tot_emps;
1644 close c_tot_emps;
1645 hr_utility.trace('l_tot_emps : ' || l_tot_emps);
1646
1647 --while l_start_date < p_end_date loop
1648 for l_month_number in 1 .. 12 loop
1649
1650 l_month_start_date := ADD_MONTHS(p_end_date,-l_month_number);
1651 l_month_end_date := ADD_MONTHS(l_month_start_date,1);
1652
1653 hr_utility.trace('l_month_start_date : ' || l_month_start_date);
1654 hr_utility.trace('l_month_end_date : ' || l_month_end_date);
1655 --
1656 open c_min_max;
1657 --
1658 fetch c_min_max into l_min_max;
1659
1660 --
1661 hr_utility.set_location(l_proc,60);
1662 hr_utility.trace('l_min_max : ' || l_min_max.num_people);
1663 hr_utility.trace('p_hierarchy_version_id : ' || p_hierarchy_version_id);
1664 hr_utility.trace('l_hierarchy_node_id : ' || l_hierarchy_node_id);
1665 if l_estab_rec.min_count is null then
1666 --
1667 l_estab_rec.min_count := l_min_max.num_people;
1668 --
1669 end if;
1670 --
1671 if l_estab_rec.max_count is null then
1672 --
1673 l_estab_rec.max_count := l_min_max.num_people;
1674 --
1675 end if;
1676 --
1677 if l_estab_rec.min_count > l_min_max.num_people then
1678 --
1679 l_estab_rec.min_count := l_min_max.num_people;
1680 --
1681 end if;
1682 --
1683 if l_estab_rec.max_count < l_min_max.num_people then
1684 --
1685 l_estab_rec.max_count := l_min_max.num_people;
1686 --
1687 end if;
1688 --
1689 if to_char(l_month_start_date,'MM') = '01' then
1690 --
1691 l_month_rec.jan := nvl(l_month_rec.jan,0) +
1692 l_min_max.num_people;
1693 --
1694 elsif to_char(l_month_start_date,'MM') = '02' then
1695 --
1696 l_month_rec.feb := nvl(l_month_rec.feb,0) +
1697 l_min_max.num_people;
1698 --
1699 elsif to_char(l_month_start_date,'MM') = '03' then
1700 --
1701 l_month_rec.mar := nvl(l_month_rec.mar,0) +
1702 l_min_max.num_people;
1703 --
1704 elsif to_char(l_month_start_date,'MM') = '04' then
1705 --
1706 l_month_rec.apr := nvl(l_month_rec.apr,0) +
1707 l_min_max.num_people;
1708 --
1709 elsif to_char(l_month_start_date,'MM') = '05' then
1710 --
1711 l_month_rec.may := nvl(l_month_rec.may,0) +
1712 l_min_max.num_people;
1713 --
1714 elsif to_char(l_month_start_date,'MM') = '06' then
1715 --
1716 l_month_rec.jun := nvl(l_month_rec.jun,0) +
1717 l_min_max.num_people;
1718 --
1719 elsif to_char(l_month_start_date,'MM') = '07' then
1720 --
1721 l_month_rec.jul := nvl(l_month_rec.jul,0) +
1722 l_min_max.num_people;
1723 --
1724 elsif to_char(l_month_start_date,'MM') = '08' then
1725 --
1726 l_month_rec.aug := nvl(l_month_rec.aug,0) +
1727 l_min_max.num_people;
1728 --
1729 elsif to_char(l_month_start_date,'MM') = '09' then
1730 --
1731 l_month_rec.sep := nvl(l_month_rec.sep,0) +
1732 l_min_max.num_people;
1733 --
1734 elsif to_char(l_month_start_date,'MM') = '10' then
1735 --
1736 l_month_rec.oct := nvl(l_month_rec.oct,0) +
1737 l_min_max.num_people;
1738 --
1739 elsif to_char(l_month_start_date,'MM') = '11' then
1740 --
1741 l_month_rec.nov := nvl(l_month_rec.nov,0) +
1742 l_min_max.num_people;
1743 --
1744 elsif to_char(l_month_start_date,'MM') = '12' then
1745 --
1746 l_month_rec.dec := nvl(l_month_rec.dec,0) +
1747 l_min_max.num_people;
1748 --
1749 end if;
1750 --
1751 --l_start_date := add_months(l_start_date,1);
1752 --
1753 close c_min_max;
1754 --
1755 end loop;
1756 --
1757 write_establishment_record;
1758 --
1759 end loop;
1760 --
1761 close c1;
1762 --
1763 -- Last case unhandled MSC records
1764 --
1765 if l_consol_rec.state is not null then
1766 --
1767 write_consolidated_record;
1768 --
1769 end if;
1770 --
1771 end loop_through_establishments;
1772 --
1773 procedure vets_mag_report
1774 (errbuf out nocopy varchar2,
1775 retcode out nocopy number,
1776 p_start_date in varchar2,
1777 p_end_date in varchar2,
1778 p_hierarchy_id in number,
1779 p_hierarchy_version_id in number,
1780 p_business_group_id in number,
1781 p_all_establishments in varchar2 -- BUG3695203
1782 ) is
1783 --
1784 l_start_date date := fnd_date.canonical_to_date(p_start_date);
1785 l_end_date date := fnd_date.canonical_to_date(p_end_date);
1786 l_string varchar2(2000);
1787 --
1788 cursor c2 is
1789 select count(*)
1790 from per_gen_hierarchy_nodes
1791 where node_type = 'EST'
1792 and hierarchy_version_id = p_hierarchy_version_id;
1793 --
1794 l_count number;
1795 --
1796 begin
1797 --
1798 --hr_utility.trace_on(null,'ORACLE');
1799 l_org_rec.ending_period := to_char(l_end_date,'MMDDYYYY');
1800 l_all_estab := p_all_establishments; -- BUG3695203
1801 set_org_details(p_hierarchy_version_id => p_hierarchy_version_id,
1802 p_business_group_id => p_business_group_id);
1803 --
1804 open c2;
1805 --
1806 fetch c2 into l_count;
1807 --
1808 if l_count = 1 then
1809 --
1810 l_org_rec.form_type := 'S';
1811 --
1812 end if;
1813 --
1814 close c2;
1815 --
1816 loop_through_establishments(p_hierarchy_version_id => p_hierarchy_version_id,
1817 p_business_group_id => p_business_group_id,
1818 p_start_date => l_start_date,
1819 p_end_date => l_end_date);
1820 --
1821 end vets_mag_report;
1822 --
1823 end per_vets_mag_report;