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