[Home] [Help]
PACKAGE BODY: APPS.PER_US_VETS_100A_CONS_PKG
Source
1 PACKAGE BODY per_us_vets_100a_cons_pkg as
2 /* $Header: pervetsc100a.pkb 120.16 2011/11/17 15:32:00 emunisek ship $ */
3
4 function check_recent_or_not(l_person_id IN per_all_people_f.person_id%TYPE,
5 l_report_end_date IN date)
6 return number
7 is
8 l_count number;
9 begin
10 select count(person_id) into l_count
11 from PER_PEOPLE_EXTRA_INFO ppei where
12 l_person_id = ppei.person_id
13 and ppei.information_type ='VETS 100A'
14 and pei_information1 is not null
15 and
16 ( months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),0)) between 0 and 12
17 or
18 months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),12)) between 0 and 12
19 or
20 months_between(l_report_end_date,add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),24)) between 0 and 12
21 or
22 months_between(l_report_end_date,(add_months(to_char(to_date(substr(pei_information1,1,10),'YYYY/MM/DD'),'DD-MON-YYYY'),36)-2)) between 0 and 12
23 );
24 return l_count;
25 exception
26 when others then
27 return 0;
28 end;
29
30 procedure GET_VETS100A_DATA (
31 errbuf out nocopy varchar2,
32 retcode out nocopy number,
33 p_business_group_id in number,
34 p_hierarchy_id in number,
35 p_hierarchy_version_id in number,
36 p_date_start in varchar2,
37 p_date_end in varchar2,
38 p_state in varchar2,
39 p_show_new_hires in varchar2,
40 p_show_totals in varchar2,
41 p_audit_report in varchar2) is
42
43 cursor c_hier_details is
44 select
45 pgh.name, pgv.version_number, pgn.entity_id, pgn.hierarchy_node_id
46 from
47 per_gen_hierarchy pgh,
48 per_gen_hierarchy_versions pgv,
49 per_gen_hierarchy_nodes pgn
50 where
51 pgh.hierarchy_id = p_hierarchy_id
52 and pgh.hierarchy_id = pgv.hierarchy_id
53 and pgv.hierarchy_version_id = p_hierarchy_version_id
54 and pgn.hierarchy_version_id = pgv.hierarchy_version_id
55 and pgn.node_type = 'PAR';
56
57 cursor c_count_est is
58 select
59 count(pghn.hierarchy_node_id)
60 from
61 per_gen_hierarchy_nodes pghn
62 where
63 pghn.hierarchy_version_id = p_hierarchy_version_id
64 and pghn.node_type = 'EST';
65
66 cursor c_parent(l_parent_org_id varchar2)is
67
68 select
69 upper(hoi1.org_information1) "Reporting Name"
70 ,hoi1.org_information2 "Company Number"
71 ,hoi1.org_information3 "Type of Rep Org"
72 ,upper(rpad(cloc.address_line_1 ||' '|| cloc.address_line_2 ||' '||
73 cloc.address_line_3,35)) "Parent Address"
74 ,upper(cloc.town_or_city) "Parent City"
75 ,upper(cloc.region_1) "Parent County"
76 ,upper(cloc.region_2) "Parent State"
77 ,upper(cloc.postal_code) "Parent Zip"
78 ,hoi2.org_information17 contact_name
79 ,substr(hoi2.org_information18,1,20) contact_telnum
80 ,hoi2.org_information20 contact_email
81 ,substr(hoi2.org_information18,1,20) || ' ' ||hoi2.org_information20 contact_telnum_and_email
82 from
83 hr_organization_information hoi1
84 ,hr_locations_all cloc
85 ,hr_organization_units hou
86 ,hr_organization_information hoi2
87 where
88 hoi1.organization_id = l_parent_org_id
89 and hoi1.org_information_context = 'VETS_Spec'
90 and hoi1.organization_id = hou.organization_id
91 and hou.location_id = cloc.location_id
92 and hoi2.organization_id = p_business_group_id
93 and hoi2.org_information_context = 'EEO_REPORT' ;
94
95 cursor c_defaults(l_parent_org_id varchar2) is
96 select
97 org_information1
98 ,org_information2
99 ,org_information3
100 ,org_information4
101 ,org_information5
102 from
103 hr_organization_information
104 where
105 organization_id = l_parent_org_id
106 and org_information_context = 'VETS_EEO_Dup' ;
107
108 cursor c_state(l_parent_node_id varchar2) is
109 Select
110 distinct(eloc.region_2) "State"
111 from
112 hr_location_extra_info hlei1
113 ,hr_location_extra_info hlei2
114 ,per_gen_hierarchy_nodes pghn
115 ,hr_locations_all eloc
116 where
117 pghn.parent_hierarchy_node_id = l_parent_node_id
118 and pghn.node_type = 'EST'
119 and eloc.location_id = pghn.entity_id
120 and hlei1.location_id = pghn.entity_id
121 and hlei1.location_id = hlei2.location_id
122 and hlei1.information_type = 'VETS-100 Specific Information'
123 and hlei1.lei_information_category= 'VETS-100 Specific Information'
124 and hlei2.information_type = 'Establishment Information'
125 and hlei2.lei_information_category= 'Establishment Information'
126 and hlei2.lei_information10 = 'N'
127 and eloc.region_2 = nvl(P_STATE,eloc.region_2);
128
129 cursor c_job_categories is
130 SELECT
131 decode(lookup_code,1,2,2,3,3,4,4,5,5,6,6,7
132 ,7,8,8,9,9,10,10,1) diplay_order,
133 lookup_code,
134 upper(rpad(meaning,26,'.'))||lookup_code cons_job_category_name,
135 lookup_code cons_job_category_code,
136 decode(lookup_code,'8','LABORERS/HELPERS'
137 ,upper(meaning)) job_category_name
138 FROM hr_lookups
139 WHERE lookup_type = 'US_EEO1_JOB_CATEGORIES'
140 ORDER BY diplay_order ;
141
142 cursor c_est_entity(p_state in varchar2) is
143 select
144 pghn1.entity_id
145 ,pghn1.hierarchy_node_id
146 from
147 per_gen_hierarchy_nodes pghn1
148 ,hr_location_extra_info hlei1
149 ,hr_location_extra_info hlei2
150 ,hr_locations_all eloc
151 where
152 pghn1.hierarchy_version_id = P_HIERARCHY_VERSION_ID
153 and pghn1.node_type = 'EST'
154 and eloc.location_id = pghn1.entity_id
155 and hlei1.location_id = pghn1.entity_id
156 and hlei1.location_id = hlei2.location_id
157 and hlei1.information_type = 'VETS-100 Specific Information'
158 and hlei1.lei_information_category= 'VETS-100 Specific Information'
159 and hlei2.information_type = 'Establishment Information'
160 and hlei2.lei_information_category= 'Establishment Information'
161 and hlei2.lei_information10 = 'N'
162 and eloc.region_2 = p_state;
163
164 cursor c_tot_emps(l_est_node_id in varchar2,l_month_end_date in date) is
165 select count(distinct asg.person_id)
166 from
167 per_all_assignments_f asg
168 ,per_gen_hierarchy_nodes pgn
169 ,per_periods_of_service pps --8667924
170 where
171 asg.business_group_id = P_BUSINESS_GROUP_ID
172 and asg.person_id = pps.person_id
173 and asg.business_group_id = pps.business_group_id
174 and asg.assignment_type = 'E'
175 and asg.primary_flag = 'Y'
176 and l_month_end_date between asg.effective_start_date and asg.effective_end_date
177 and asg.effective_start_date = (select max(paf2.effective_start_date)
178 from per_all_assignments_f paf2
179 where paf2.person_id = asg.person_id
180 and paf2.primary_flag = 'Y'
181 and paf2.assignment_type = 'E'
182 and paf2.effective_start_date
183 <= l_month_end_date)
184 AND
185 (EXISTS (
186 SELECT 'X'
187 FROM HR_ORGANIZATION_INFORMATION HOI1,
188 HR_ORGANIZATION_INFORMATION HOI2
189 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
190 AND hoi1.org_information_context = 'Reporting Statuses'
191 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
192 AND asg.employment_category = hoi2.org_information1
193 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
194 AND hoi2.org_information_context = 'Reporting Categories'
195 AND hoi1.organization_id = hoi2.organization_id
196 )
197 OR /*8667924*/
198 months_between(l_month_end_date,pps.actual_termination_date) between 0 and 12 )
199 and asg.location_id = pgn.entity_id
200 and (pgn.hierarchy_node_id = l_est_node_id
201 or pgn.parent_hierarchy_node_id = l_est_node_id)
202 and pgn.node_type in ('EST','LOC');
203
204
205 --Variable Declaration
206 l_business_group_name varchar2(240); --Increased variable size for Bug#9038285
207 lc_state varchar2(3);
208
209 l_est_no number;
210 l_hierarchy_name per_gen_hierarchy.name%type;
211 l_hierarchy_version_num per_gen_hierarchy_versions.VERSION_NUMBER%type;
212 l_parent_org_id varchar2(20);
213 l_parent_node_id varchar2(20);
214
215 l_reporting_name hr_organization_information.org_information1%type;
216 l_company_number hr_organization_information.org_information2%type;
217 l_type_of_rep_org hr_organization_information.org_information3%type;
218 l_parent_address clob;
219 l_parent_city hr_locations_all.town_or_city%type;
220 l_parent_county hr_locations_all.region_1%type;
221 l_parent_state hr_locations_all.region_2%type;
222 l_parent_zip hr_locations_all.postal_code%type;
223 l_contact_name hr_organization_information.org_information17%type;
224 l_contact_telnum hr_organization_information.org_information18%type;
225 l_contact_email hr_organization_information.org_information20%type;
226 l_contact_telnum_and_email hr_organization_information.org_information20%type;
227
228 l_state hr_locations_all.region_2%type;
229 l_state_meaning varchar2(20);
230
231 l_def_sic varchar2(20);
232 l_def_naics varchar2(20);
233 l_def_gre varchar2(20);
234 l_def_duns varchar2(20);
235 l_def_gov_con varchar2(20);
236
237 l_display_order varchar2(2);
238 l_job_code varchar2(2);
239 l_cons_job_category_name varchar2(50);
240 l_cons_job_category_code varchar2(50);
241 l_job_category_name varchar2(50);
242
243 cp_prime_contractor varchar2(10);
244 cp_sub_contractor varchar2(10);
245
246 CS_NO_DIS_VETS number :=0;
247 CS_NO_OTHER_VETS number :=0;
248 CS_NO_ARMED_VETS number :=0;
249 CS_NO_RECSEP_VETS number:=0;
250 CS_NO_TOT_VETS number:=0;
251 CS_NH_DIS_VETS number :=0;
252 CS_NH_OTHER_VETS number :=0;
253 CS_NH_ARMED_VETS number :=0;
254 CS_NH_RECSEP_VETS number:=0;
255 CS_NH_TOT_VETS number :=0;
256
257 CP_MSC number;
258
259 l_xml_string clob :='';
260 P_DATE_END1 varchar2(10);
261
262 l_buffer varchar2(200);
263 g_delimiter varchar2(10) := ',' ;
264 g_eol varchar2(1) := fnd_global.local_chr(10);
265
266 function count_locsFormula(state in varchar2 , p_parent_node_id varchar2) return Number is
267
268 no_ests number := 0;
269 est_id number;
270 no_tot_emps number := 0;
271 ent_id number;
272 no_loc_emps number;
273
274
275 cursor c_locs(est_id number ) is
276 select entity_id
277 from
278 per_gen_hierarchy_nodes
279 where
280 (hierarchy_node_id = est_id
281 or parent_hierarchy_node_id = est_id)
282 and hierarchy_version_id = p_hierarchy_version_id;
283
284
285 cursor c_ests is
286 select
287 pghn.hierarchy_node_id
288 from
289 hr_location_extra_info hlei1
290 ,hr_location_extra_info hlei2
291 ,per_gen_hierarchy_nodes pghn
292 ,hr_locations_all eloc
293 where
294 pghn.hierarchy_version_id = p_hierarchy_version_id
295 and pghn.parent_hierarchy_node_id = p_parent_node_id
296 and eloc.location_id = pghn.entity_id
297 and hlei1.location_id = pghn.entity_id
298 and hlei1.location_id = hlei2.location_id
299 and hlei1.information_type = 'VETS-100 Specific Information'
300 and hlei1.lei_information_category= 'VETS-100 Specific Information'
301 and hlei2.information_type = 'Establishment Information'
302 and hlei2.lei_information_category= 'Establishment Information'
303 and hlei2.lei_information10 = 'N'
304 and eloc.region_2 = state;
305
306 begin
307 open c_ests;
308 loop
309 fetch c_ests into est_id;
310 exit when c_ests%notfound;
311
312 open c_locs(est_id);
313 loop
314 fetch c_locs into ent_id;
315 exit when c_locs%notfound;
316
317 select count(distinct asg.person_id)
318 into no_loc_emps
319 from per_all_assignments_f asg
320 , per_periods_of_service pps
321 where
322 asg.assignment_type = 'E'
323 and asg.primary_flag = 'Y'
324 and pps.person_id=asg.person_id
325 and pps.business_group_id = asg.business_group_id
326 and P_DATE_END1 between asg.effective_start_date and asg.effective_end_date
327 and asg.effective_start_date = (select max(paf2.effective_start_date)
328 from per_all_assignments_f paf2
329 where paf2.person_id = asg.person_id
330 and paf2.primary_flag = 'Y'
331 and paf2.assignment_type = 'E'
332 and paf2.effective_start_date
333 <= P_DATE_END1
334 )
335 and asg.business_group_id = P_BUSINESS_GROUP_ID
336 AND
337 ( EXISTS (
338 SELECT 'X'
339 FROM HR_ORGANIZATION_INFORMATION HOI1,
340 HR_ORGANIZATION_INFORMATION HOI2
341 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
342 AND hoi1.org_information_context = 'Reporting Statuses'
343 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
344 AND asg.employment_category = hoi2.org_information1
345 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
346 AND hoi2.org_information_context = 'Reporting Categories'
347 AND hoi1.organization_id = hoi2.organization_id)
348 OR
349 months_between(P_DATE_END1,pps.actual_termination_date) between 0 and 12 /*8667924*/
350
351 )
352 and asg.location_id = ent_id;
353
354 no_tot_emps := no_tot_emps + no_loc_emps;
355
356 end loop;
357 close c_locs;
358
359 if no_tot_emps between 0 and 50 then
360 no_ests := no_ests + 1;
361
362 end if;
363 no_tot_emps := 0;
364 no_loc_emps := 0;
365 end loop;
366 close c_ests;
367
368 return (no_ests);
369
370 end count_locsFormula;
371
372 procedure min_max(p_state varchar2) is
373
374 cursor C_MINMAX_EMPS(l_est_node_id varchar2,l_month_start_date date,l_month_end_date date) is
375 select count('asg')
376 from per_all_assignments_f asg
377 ,per_gen_hierarchy_nodes pgn
378 where asg.assignment_type = 'E'
379 and asg.primary_flag = 'Y'
380 --9011580
381 --AND l_month_start_date between asg.effective_start_date and asg.effective_end_date
382 and asg.effective_end_date >= l_month_start_date
383 AND l_month_end_date between asg.effective_start_date and asg.effective_end_date
384 and asg.effective_start_date = (select max(paf2.effective_start_date)
385 from per_all_assignments_f paf2
386 where paf2.person_id = asg.person_id
387 and paf2.primary_flag = 'Y'
388 and paf2.assignment_type = 'E'
389 and paf2.effective_start_date
390 <= l_month_end_date)
391 and asg.business_group_id = P_BUSINESS_GROUP_ID
392 AND EXISTS (
393 SELECT 'X'
394 FROM HR_ORGANIZATION_INFORMATION HOI1,
395 HR_ORGANIZATION_INFORMATION HOI2
396 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
397 AND hoi1.org_information_context = 'Reporting Statuses'
398 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
399 AND asg.employment_category = hoi2.org_information1
400 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
401 AND hoi2.org_information_context = 'Reporting Categories'
402 AND hoi1.organization_id = hoi2.organization_id)
403 and asg.location_id = pgn.entity_id
404 and (pgn.hierarchy_node_id = l_est_node_id
405 or pgn.parent_hierarchy_node_id = l_est_node_id)
406 and pgn.node_type in ('EST','LOC');
407
408 l_est_entity number := 0;
409 l_est_node_id number := 0;
410 l_no_est_emps number := 0;
411 l_month_number number :=0;
412
413 l_month_start_date date;
414 l_month_end_date date := P_DATE_END1;
415 l_period_end_date date := P_DATE_END1;
416
417 l_emps_this_month_n number:= 0;
418 l_estab_min_for_year number := 0;
419 l_state_min_for_year number := 0;
420
421 l_emps_this_month_x number:= 0;
422 l_estab_max_for_year number := 50;
423 l_state_max_for_year number := 0;
424
425 begin
426
427 open c_est_entity(p_state);
428
429 loop
430 fetch c_est_entity into l_est_entity,l_est_node_id;
431 exit when c_est_entity%notfound;
432 l_emps_this_month_n := 0;
433 l_estab_min_for_year := 0;
434 l_emps_this_month_x := 0;
435 l_estab_max_for_year := 0;
436
437 open c_tot_emps(l_est_node_id,l_month_end_date);
438 fetch c_tot_emps into l_no_est_emps;
439 close c_tot_emps;
440
441 if l_no_est_emps < 50
442 then
443 l_emps_this_month_n := 0;
444 l_estab_min_for_year := 50;
445
446 for l_month_number in 1 .. 12
447 loop
448
449 --9000119
450 l_month_start_date := ADD_MONTHS(l_period_end_date,-l_month_number)+1;
451 l_month_end_date := ADD_MONTHS(l_month_start_date,1)-1;
452
453 open c_minmax_emps(l_est_node_id,l_month_start_date,l_month_end_date);
454 fetch c_minmax_emps into l_emps_this_month_n;
455
456 if (l_emps_this_month_n is null or l_emps_this_month_n = 0) then
457 l_emps_this_month_n := 0;
458 l_estab_min_for_year := 0;
459 else
460 if l_estab_min_for_year > l_emps_this_month_n then
461 l_estab_min_for_year := l_emps_this_month_n;
462 end if;
463 end if;
464
465 close c_minmax_emps;
466
467 end loop;
468
469 l_state_min_for_year := l_state_min_for_year + l_estab_min_for_year;
470
471 for l_month_number in 1 .. 12
472 loop
473
474 --9000119
475 l_month_start_date := ADD_MONTHS(l_period_end_date,-l_month_number)+1;
476 l_month_end_date := ADD_MONTHS(l_month_start_date,1)-1;
477
478 open c_minmax_emps(l_est_node_id,l_month_start_date,l_month_end_date);
479 fetch c_minmax_emps into l_emps_this_month_x;
480 if l_emps_this_month_x > l_estab_max_for_year
481 then
482 l_estab_max_for_year := l_emps_this_month_x;
483 end if;
484 close c_minmax_emps;
485 end loop;
486
487 l_state_max_for_year := l_state_max_for_year + l_estab_max_for_year;
488
489 end if;
490
491 end loop;
492 close c_est_entity;
493
494 l_xml_string := l_xml_string ||'<LIST_G_6>';
495 l_xml_string := l_xml_string ||'<G_6>';
496 l_xml_string := l_xml_string ||convert_into_xml('CP_MIN_EMPS',l_state_min_for_year,'D');
497 l_xml_string := l_xml_string ||convert_into_xml('CP_MAX_EMPS',l_state_max_for_year,'D');
498 l_xml_string := l_xml_string ||convert_into_xml('CP_NO_EST_EMPS',l_no_est_emps,'D');
499 l_xml_string := l_xml_string ||'</G_6>';
500 l_xml_string := l_xml_string ||'</LIST_G_6>';
501
502 end min_max;
503
504
505 procedure vets_data(p_state varchar2,p_job_code varchar2,p_job_category_name varchar2) is
506
507 l_month_end_date date := P_DATE_END1;
508
509 cursor c_audit_report_emps(l_est_node_id in varchar2) is
510
511 SELECT
512 peo.person_id
513 ,peo.last_name
514 ,peo.first_name
515 ,peo.employee_number
516 ,peo.per_information25 veteran
517 ,job.job_information1 job_category
518 ,asg.assignment_id
519 ,decode(peo.per_information25,'NOTVET',' ','VET',' ')||
520 decode(peo.per_information25,'VETDIS','L','AFSMNSDIS','L','OTEDV','L','AFSMDIS','L','NSDIS','L','AFSMDISOP','L','AFSMNSDISOP','L','NSDISOP','L')||
521 decode(peo.per_information25,'OTEV','M','OTEDV','M','AFSMDISOP','M','AFSMNSDISOP','M','AFSMOP','M','NSOP','M','AFSMNSOP','M','NSDISOP','M')||
522 decode(peo.per_information25,'AFSM','N','AFSMNSDIS','N','AFSMDIS','N','AFSMDISOP','N','AFSMNSDISOP','N','AFSMOP','N','AFSMNSOP','N','AFSMNS','N') veteran_category
523
524 from
525 per_all_people_f peo,
526 per_all_assignments_f asg,
527 per_jobs_vl job
528 ,per_gen_hierarchy_nodes pgn
529 where
530 peo.person_id = asg.person_id
531 and l_month_end_date between peo.effective_start_date and peo.effective_end_date
532 and peo.current_employee_flag = 'Y'
533 and asg.assignment_type = 'E'
534 and asg.primary_flag = 'Y'
535 and l_month_end_date between asg.effective_start_date and asg.effective_end_date
536 and peo.effective_start_date = (select max(peo2.effective_start_date)
537 from per_all_people_f peo2
538 where peo2.person_id = peo.person_id
539 and peo2.current_employee_flag = 'Y'
540 and peo2.effective_start_date <= l_month_end_date )
541 and asg.effective_start_date = (select max(paf2.effective_start_date)
542 from per_all_assignments_f paf2
543 where paf2.person_id = asg.person_id
544 and paf2.primary_flag = 'Y'
545 and paf2.assignment_type = 'E'
546 and paf2.effective_start_date <= l_month_end_date)
547 and job.job_id = asg.job_id
548 and job.job_information_category = 'US'
549 and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
550 AND job.job_information1 = p_job_code
551 and asg.business_group_id = P_BUSINESS_GROUP_ID
552 and peo.business_group_id = P_BUSINESS_GROUP_ID
553 and job.business_group_id = P_BUSINESS_GROUP_ID
554 AND EXISTS (
555 SELECT 'X'
556 FROM HR_ORGANIZATION_INFORMATION HOI1,
557 HR_ORGANIZATION_INFORMATION HOI2
558 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
559 AND hoi1.org_information_context = 'Reporting Statuses'
560 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
561 AND asg.employment_category = hoi2.org_information1
562 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
563 AND hoi2.org_information_context = 'Reporting Categories'
564 AND hoi1.organization_id = hoi2.organization_id
565 )
566 and asg.location_id = pgn.entity_id
567 and (pgn.hierarchy_node_id = l_est_node_id
568 or pgn.parent_hierarchy_node_id = l_est_node_id)
569 and pgn.node_type in ('EST','LOC')
570
571
572 union
573
574 SELECT
575 peo.person_id
576 ,peo.last_name
577 ,peo.first_name
578 ,peo.employee_number
579 ,peo.per_information25 veteran
580 ,job.job_information1 job_category
581 ,asg.assignment_id
582 ,decode(peo.per_information25,'NS','O','AFSMNSDIS','O','NSDIS','O','AFSMNSDISOP','O', 'NSOP','O','AFSMNSOP','O','AFSMNS','O','NSDISOP','O') veteran_category
583
584 from
585 per_all_people_f peo,
586 per_all_assignments_f asg,
587 per_jobs_vl job
588 ,per_gen_hierarchy_nodes pgn
589 where
590 peo.person_id = asg.person_id
591 and l_month_end_date between peo.effective_start_date and peo.effective_end_date
592 and peo.current_employee_flag = 'Y'
593 and asg.assignment_type = 'E'
594 and asg.primary_flag = 'Y'
595 and l_month_end_date between asg.effective_start_date and asg.effective_end_date
596 and peo.effective_start_date = (select max(peo2.effective_start_date)
597 from per_all_people_f peo2
598 where peo2.person_id = peo.person_id
599 and peo2.current_employee_flag = 'Y'
600 and peo2.effective_start_date <= l_month_end_date )
601 and asg.effective_start_date = (select max(paf2.effective_start_date)
602 from per_all_assignments_f paf2
603 where paf2.person_id = asg.person_id
604 and paf2.primary_flag = 'Y'
605 and paf2.assignment_type = 'E'
606 and paf2.effective_start_date <= l_month_end_date)
607 and job.job_id = asg.job_id
608 and job.job_information_category = 'US'
609 and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
610 AND job.job_information1 = p_job_code
611 and asg.business_group_id = P_BUSINESS_GROUP_ID
612 and peo.business_group_id = P_BUSINESS_GROUP_ID
613 and job.business_group_id = P_BUSINESS_GROUP_ID
614 AND EXISTS (
615 SELECT 'X'
616 FROM HR_ORGANIZATION_INFORMATION HOI1,
617 HR_ORGANIZATION_INFORMATION HOI2
618 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
619 AND hoi1.org_information_context = 'Reporting Statuses'
620 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
621 AND asg.employment_category = hoi2.org_information1
622 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
623 AND hoi2.org_information_context = 'Reporting Categories'
624 AND hoi1.organization_id = hoi2.organization_id
625 )
626 and asg.location_id = pgn.entity_id
627 and (pgn.hierarchy_node_id = l_est_node_id
628 or pgn.parent_hierarchy_node_id = l_est_node_id)
629 and pgn.node_type in ('EST','LOC')
630 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
631 and check_recent_or_not(peo.person_id,l_month_end_date) > 0 ;
632
633 cursor c_audit_report_nh(l_est_node_id in varchar2) is
634 SELECT
635 peo.person_id
636 ,peo.last_name
637 ,peo.first_name
638 ,peo.employee_number
639 ,peo.per_information25 veteran
640 ,job.job_information1 job_category
641 ,asg.assignment_id
642 ,decode(peo.per_information25,'NOTVET',' ','VET',' ')||
643 decode(peo.per_information25,'VETDIS','Q','AFSMNSDIS','Q','OTEDV','Q','AFSMDIS','Q','NSDIS','Q','AFSMDISOP','Q','AFSMNSDISOP','Q','NSDISOP','Q')||
644 decode(peo.per_information25,'OTEV','R','OTEDV','R','AFSMDISOP','R','AFSMNSDISOP','R','AFSMOP','R','NSOP','R','AFSMNSOP','R','NSDISOP','R')||
645 decode(peo.per_information25,'AFSM','S','AFSMNSDIS','S','AFSMDIS','S','AFSMDISOP','S','AFSMNSDISOP','S','AFSMOP','S','AFSMNSOP','S','AFSMNS','S') veteran_category
646
647 FROM per_all_people_f peo,
648 per_all_assignments_f asg,
649 per_jobs_vl job,
650 per_gen_hierarchy_nodes pgn,
651 per_periods_of_service pps
652 WHERE pps.person_id = peo.person_id
653 AND peo.person_id = asg.person_id
654 AND pps.business_group_id = P_BUSINESS_GROUP_ID
655 AND peo.business_group_id = P_BUSINESS_GROUP_ID
656 AND asg.business_group_id = P_BUSINESS_GROUP_ID
657 AND job.business_group_id = P_BUSINESS_GROUP_ID
658 AND peo.current_employee_flag = 'Y'
659 AND asg.assignment_type = 'E'
660 AND asg.primary_flag = 'Y'
661 and asg.effective_start_date <= l_month_end_date
662 AND asg.effective_start_date = (select max(paf2.effective_start_date)
663 from per_all_assignments_f paf2
664 where paf2.person_id = asg.person_id
665 AND paf2.assignment_id = asg.assignment_id
666 AND paf2.effective_start_date = peo.effective_start_date
667 AND paf2.business_group_id = P_BUSINESS_GROUP_ID
668 AND paf2.primary_flag = 'Y'
669 AND paf2.assignment_type = 'E'
670 AND paf2.effective_start_date <= l_month_end_date)
671 AND months_between(l_month_end_date,pps.date_start) <= 12
672 AND months_between(l_month_end_date,pps.date_start) >= 0
673 AND peo.effective_start_date = pps.date_start
674
675 AND EXISTS (
676 SELECT 'X'
677 FROM HR_ORGANIZATION_INFORMATION HOI1,
678 HR_ORGANIZATION_INFORMATION HOI2
679 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
680 AND hoi1.org_information_context = 'Reporting Statuses'
681 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
682 AND asg.employment_category = hoi2.org_information1
683 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
684 AND hoi2.org_information_context = 'Reporting Categories'
685 AND hoi1.organization_id = hoi2.organization_id)
686 AND asg.job_id = job.job_id
687 AND job.job_information_category = 'US'
688 AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
689 AND job.job_information1 = p_job_code
690 and asg.location_id = pgn.entity_id
691 and (pgn.hierarchy_node_id = l_est_node_id
692 or pgn.parent_hierarchy_node_id = l_est_node_id)
693 and pgn.node_type in ('EST','LOC')
694
695 union
696
697 SELECT
698 peo.person_id
699 ,peo.last_name
700 ,peo.first_name
701 ,peo.employee_number
702 ,peo.per_information25 veteran
703 ,job.job_information1 job_category
704 ,asg.assignment_id
705 ,decode(peo.per_information25,'NS','T','AFSMNSDIS','T','NSDIS','T','AFSMNSDISOP','T', 'NSOP','T','AFSMNSOP','T','AFSMNS','T','NSDISOP','T') veteran_category
706
707 FROM per_all_people_f peo,
708 per_all_assignments_f asg,
709 per_jobs_vl job,
710 per_gen_hierarchy_nodes pgn,
711 per_periods_of_service pps
712 WHERE pps.person_id = peo.person_id
713 AND peo.person_id = asg.person_id
714 AND pps.business_group_id = P_BUSINESS_GROUP_ID
715 AND peo.business_group_id = P_BUSINESS_GROUP_ID
716 AND asg.business_group_id = P_BUSINESS_GROUP_ID
717 AND job.business_group_id = P_BUSINESS_GROUP_ID
718 AND peo.current_employee_flag = 'Y'
719 AND asg.assignment_type = 'E'
720 AND asg.primary_flag = 'Y'
721 and asg.effective_start_date <= l_month_end_date
722 AND asg.effective_start_date = (select max(paf2.effective_start_date)
723 from per_all_assignments_f paf2
724 where paf2.person_id = asg.person_id
725 AND paf2.assignment_id = asg.assignment_id
726 AND paf2.effective_start_date = peo.effective_start_date
727 AND paf2.business_group_id = P_BUSINESS_GROUP_ID
728 AND paf2.primary_flag = 'Y'
729 AND paf2.assignment_type = 'E'
730 AND paf2.effective_start_date <= l_month_end_date)
731 AND months_between(l_month_end_date,pps.date_start) <= 12
732 AND months_between(l_month_end_date,pps.date_start) >= 0
733 AND peo.effective_start_date = pps.date_start
734
735 AND EXISTS (
736 SELECT 'X'
737 FROM HR_ORGANIZATION_INFORMATION HOI1,
738 HR_ORGANIZATION_INFORMATION HOI2
739 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
740 AND hoi1.org_information_context = 'Reporting Statuses'
741 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
742 AND asg.employment_category = hoi2.org_information1
743 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
744 AND hoi2.org_information_context = 'Reporting Categories'
745 AND hoi1.organization_id = hoi2.organization_id)
746 AND asg.job_id = job.job_id
747 AND job.job_information_category = 'US'
748 AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
749 AND job.job_information1 = p_job_code
750 and asg.location_id = pgn.entity_id
751 and (pgn.hierarchy_node_id = l_est_node_id
752 or pgn.parent_hierarchy_node_id = l_est_node_id)
753 and pgn.node_type in ('EST','LOC')
754 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
755 and check_recent_or_not(peo.person_id,l_month_end_date) > 0 ;
756
757
758 cursor c_emps(l_est_node_id in varchar2) is
759
760 select A.loc_no_dis_vets, A.loc_no_other_vets,A.loc_no_armed_vets,B.loc_no_recsep_vets,A.loc_no_not_vets,A.loc_tot_emps -- #10113747
761 FROM
762 (
763 SELECT
764 count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,'AFSMDIS',1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) loc_no_dis_vets,
765 count(decode(peo.per_information25,'OTEV',1,'OTEDV',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'AFSMOP',1,'NSOP',1,'AFSMNSOP',1,'NSDISOP',1,null)) loc_no_other_vets,
766 count(decode(peo.per_information25,'AFSM',1,'AFSMNSDIS',1,'AFSMDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'AFSMOP',1,'AFSMNSOP',1,'AFSMNS',1,null)) loc_no_armed_vets,
767 null loc_no_recsep_vets,
768 count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) loc_no_not_vets,
769 count(1) loc_tot_emps -- #10113747
770 from
771 per_all_people_f peo,
772 per_all_assignments_f asg,
773 per_jobs_vl job
774 ,per_gen_hierarchy_nodes pgn
775 where
776 peo.person_id = asg.person_id
777 and l_month_end_date between peo.effective_start_date and peo.effective_end_date
778 and peo.current_employee_flag = 'Y'
779 and asg.assignment_type = 'E'
780 and asg.primary_flag = 'Y'
781 and l_month_end_date between asg.effective_start_date and asg.effective_end_date
782 and peo.effective_start_date = (select max(peo2.effective_start_date)
783 from per_all_people_f peo2
784 where peo2.person_id = peo.person_id
785 and peo2.current_employee_flag = 'Y'
786 and peo2.effective_start_date <= l_month_end_date)
787 and asg.effective_start_date = (select max(paf2.effective_start_date)
788 from per_all_assignments_f paf2
789 where paf2.person_id = asg.person_id
790 and paf2.primary_flag = 'Y'
791 and paf2.assignment_type = 'E'
792 and paf2.effective_start_date <= l_month_end_date)
793
794 and job.job_id = asg.job_id
795 and job.job_information_category = 'US'
796 and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
797 AND job.job_information1 = p_job_code
798 and asg.business_group_id = P_BUSINESS_GROUP_ID
799 and peo.business_group_id = P_BUSINESS_GROUP_ID
800 and job.business_group_id = P_BUSINESS_GROUP_ID
801 AND EXISTS (
802 SELECT 'X'
803 FROM HR_ORGANIZATION_INFORMATION HOI1,
804 HR_ORGANIZATION_INFORMATION HOI2
805 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
806 AND hoi1.org_information_context = 'Reporting Statuses'
807 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
808 AND asg.employment_category = hoi2.org_information1
809 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
810 AND hoi2.org_information_context = 'Reporting Categories'
811 AND hoi1.organization_id = hoi2.organization_id
812 )
813 and asg.location_id = pgn.entity_id
814 and (pgn.hierarchy_node_id = l_est_node_id
815 or pgn.parent_hierarchy_node_id = l_est_node_id)
816 and pgn.node_type in ('EST','LOC')) A
817 ,
818 (SELECT
819 null loc_no_dis_vets,
820 null loc_no_other_vets,
821 null loc_no_armed_vets,
822 count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,'AFSMNSDISOP',1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',1,'NSDISOP',1,null)) loc_no_recsep_vets,
823 null loc_no_not_vets,
824 null loc_tot_emps -- #10113747
825 from
826 per_all_people_f peo,
827 per_all_assignments_f asg,
828 per_jobs_vl job
829 ,per_gen_hierarchy_nodes pgn
830 where
831 peo.person_id = asg.person_id
832 and l_month_end_date between peo.effective_start_date and peo.effective_end_date
833 and peo.current_employee_flag = 'Y'
834 and asg.assignment_type = 'E'
835 and asg.primary_flag = 'Y'
836 and l_month_end_date between asg.effective_start_date and asg.effective_end_date
837 and peo.effective_start_date = (select max(peo2.effective_start_date)
838 from per_all_people_f peo2
839 where peo2.person_id = peo.person_id
840 and peo2.current_employee_flag = 'Y'
841 and peo2.effective_start_date <= l_month_end_date)
842 and asg.effective_start_date = (select max(paf2.effective_start_date)
843 from per_all_assignments_f paf2
844 where paf2.person_id = asg.person_id
845 and paf2.primary_flag = 'Y'
846 and paf2.assignment_type = 'E'
847 and paf2.effective_start_date <= l_month_end_date)
848
849 and job.job_id = asg.job_id
850 and job.job_information_category = 'US'
851 and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
852 AND job.job_information1 = p_job_code
853 and asg.business_group_id = P_BUSINESS_GROUP_ID
854 and peo.business_group_id = P_BUSINESS_GROUP_ID
855 and job.business_group_id = P_BUSINESS_GROUP_ID
856 AND EXISTS (
857 SELECT 'X'
858 FROM HR_ORGANIZATION_INFORMATION HOI1,
859 HR_ORGANIZATION_INFORMATION HOI2
860 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
861 AND hoi1.org_information_context = 'Reporting Statuses'
862 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
863 AND asg.employment_category = hoi2.org_information1
864 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
865 AND hoi2.org_information_context = 'Reporting Categories'
866 AND hoi1.organization_id = hoi2.organization_id
867 )
868 and asg.location_id = pgn.entity_id
869 and (pgn.hierarchy_node_id = l_est_node_id
870 or pgn.parent_hierarchy_node_id = l_est_node_id)
871 and pgn.node_type in ('EST','LOC')
872 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
873 and check_recent_or_not(peo.person_id,l_month_end_date) > 0
874
875 ) B;
876
877 cursor c_nh(l_est_node_id in varchar2) is
878
879 select A.loc_nh_dis_vets,A.loc_nh_other_vets, A.loc_nh_armed_vets,B.loc_nh_recsep_vets,A.loc_nh_not_vets,A.loc_nh_tot_emps -- #10113747
880 FROM
881 (
882 SELECT
883 count(decode(peo.per_information25,'VETDIS',1,'AFSMNSDIS',1,'OTEDV',1,'AFSMDIS',1,'NSDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'NSDISOP',1,null)) loc_nh_dis_vets,
884 count(decode(peo.per_information25,'OTEV',1,'OTEDV',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'AFSMOP',1,'NSOP',1,'AFSMNSOP',1,'NSDISOP',1,null)) loc_nh_other_vets,
885 count(decode(peo.per_information25,'AFSM',1,'AFSMNSDIS',1,'AFSMDIS',1,'AFSMDISOP',1,'AFSMNSDISOP',1,'AFSMOP',1,'AFSMNSOP',1,'AFSMNS',1,null)) loc_nh_armed_vets,
886 NULL loc_nh_recsep_vets,
887 count(decode(peo.per_information25,'NOTVET',1,NULL,1,'VET',1,null)) loc_nh_not_vets,
888 count(1) loc_nh_tot_emps -- #10113747
889 FROM per_all_people_f peo,
890 per_all_assignments_f asg,
891 per_jobs_vl job,
892 per_gen_hierarchy_nodes pgn,
893 per_periods_of_service pps
894 WHERE peo.person_id = asg.person_id
895 AND peo.person_id = pps.person_id
896 AND peo.business_group_id = P_BUSINESS_GROUP_ID
897 AND asg.business_group_id = P_BUSINESS_GROUP_ID
898 AND job.business_group_id = P_BUSINESS_GROUP_ID
899 AND pps.business_group_id = P_BUSINESS_GROUP_ID
900 AND peo.current_employee_flag = 'Y'
901 AND asg.assignment_type = 'E'
902 AND asg.primary_flag = 'Y'
903 and asg.effective_start_date <= l_month_end_date
904 AND asg.effective_start_date = (select max(paf2.effective_start_date)
905 from per_all_assignments_f paf2
906 where paf2.person_id = asg.person_id
907 and paf2.assignment_id = asg.assignment_id
908 and paf2.effective_start_date = peo.effective_start_date
909 and paf2.primary_flag = 'Y'
910 and paf2.assignment_type = 'E'
911 and paf2.effective_start_date <= l_month_end_date)
912 AND months_between (l_month_end_date,pps.date_start) <= 12
913 AND months_between (l_month_end_date,pps.date_start) >= 0
914 AND peo.effective_start_date = pps.date_start
915 AND EXISTS (
916 SELECT 'X'
917 FROM HR_ORGANIZATION_INFORMATION HOI1,
918 HR_ORGANIZATION_INFORMATION HOI2
919 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
920 AND hoi1.org_information_context = 'Reporting Statuses'
921 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
922 AND asg.employment_category = hoi2.org_information1
923 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
924 AND hoi2.org_information_context = 'Reporting Categories'
925 AND hoi1.organization_id = hoi2.organization_id)
926 AND asg.job_id = job.job_id
927 AND job.job_information_category = 'US'
928 AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
929 AND job.job_information1 = p_job_code
930 and asg.location_id = pgn.entity_id
931 and (pgn.hierarchy_node_id = l_est_node_id
932 or pgn.parent_hierarchy_node_id = l_est_node_id)
933 and pgn.node_type in ('EST','LOC')) A
934 ,
935 (
936 SELECT
937 NULL loc_nh_dis_vets,
938 NULL loc_nh_other_vets,
939 NULL loc_nh_armed_vets,
940 count(decode(peo.per_information25,'NS',1,'AFSMNSDIS',1,'NSDIS',1,'AFSMNSDISOP',1,'NSOP',1,'AFSMNSOP',1,'AFSMNS',1,'NSDISOP',1,null)) loc_nh_recsep_vets,
941 NULL loc_nh_not_vets,
942 NULL loc_nh_tot_emps -- #10113747
943 FROM per_all_people_f peo,
944 per_all_assignments_f asg,
945 per_jobs_vl job,
946 per_gen_hierarchy_nodes pgn,
947 per_periods_of_service pps
948 WHERE peo.person_id = asg.person_id
949 AND peo.person_id = pps.person_id
950 AND peo.business_group_id = P_BUSINESS_GROUP_ID
951 AND asg.business_group_id = P_BUSINESS_GROUP_ID
952 AND job.business_group_id = P_BUSINESS_GROUP_ID
953 AND pps.business_group_id = P_BUSINESS_GROUP_ID
954 AND peo.current_employee_flag = 'Y'
955 AND asg.assignment_type = 'E'
956 AND asg.primary_flag = 'Y'
957 and asg.effective_start_date <= l_month_end_date
958 AND asg.effective_start_date = (select max(paf2.effective_start_date)
959 from per_all_assignments_f paf2
960 where paf2.person_id = asg.person_id
961 and paf2.assignment_id = asg.assignment_id
962 and paf2.effective_start_date = peo.effective_start_date
963 and paf2.primary_flag = 'Y'
964 and paf2.assignment_type = 'E'
965 and paf2.effective_start_date <= l_month_end_date)
966 AND months_between (l_month_end_date,pps.date_start) <= 12
967 AND months_between (l_month_end_date,pps.date_start) >= 0
968 AND peo.effective_start_date = pps.date_start
969 AND EXISTS (
970 SELECT 'X'
971 FROM HR_ORGANIZATION_INFORMATION HOI1,
972 HR_ORGANIZATION_INFORMATION HOI2
973 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
974 AND hoi1.org_information_context = 'Reporting Statuses'
975 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
976 AND asg.employment_category = hoi2.org_information1
977 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
978 AND hoi2.org_information_context = 'Reporting Categories'
979 AND hoi1.organization_id = hoi2.organization_id)
980 AND asg.job_id = job.job_id
981 AND job.job_information_category = 'US'
982 AND l_month_end_date between job.date_from and nvl(job.date_to, l_month_end_date)
983 AND job.job_information1 = p_job_code
984 and asg.location_id = pgn.entity_id
985 and (pgn.hierarchy_node_id = l_est_node_id
986 or pgn.parent_hierarchy_node_id = l_est_node_id)
987 and pgn.node_type in ('EST','LOC')
988 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
989 and check_recent_or_not(peo.person_id,l_month_end_date) > 0
990 )B;
991
992 cursor c_nonreported(l_est_node_id in varchar2) is
993
994 SELECT
995 peo.person_id
996 ,peo.last_name
997 ,peo.first_name
998 ,peo.employee_number
999 ,peo.per_information25 veteran
1000 ,job.job_information1 job_category
1001 ,asg.assignment_id
1002
1003 from
1004 per_all_people_f peo,
1005 per_all_assignments_f asg,
1006 per_jobs_vl job
1007 ,per_gen_hierarchy_nodes pgn
1008 where
1009 peo.person_id = asg.person_id
1010 and l_month_end_date between peo.effective_start_date and peo.effective_end_date
1011 and peo.current_employee_flag = 'Y'
1012 and asg.assignment_type = 'E'
1013 and asg.primary_flag = 'Y'
1014 and l_month_end_date between asg.effective_start_date and asg.effective_end_date
1015 and peo.effective_start_date = (select max(peo2.effective_start_date)
1016 from per_all_people_f peo2
1017 where peo2.person_id = peo.person_id
1018 and peo2.current_employee_flag = 'Y'
1019 and peo2.effective_start_date <= l_month_end_date )
1020 and asg.effective_start_date = (select max(paf2.effective_start_date)
1021 from per_all_assignments_f paf2
1022 where paf2.person_id = asg.person_id
1023 and paf2.primary_flag = 'Y'
1024 and paf2.assignment_type = 'E'
1025 and paf2.effective_start_date <= l_month_end_date)
1026 and job.job_id = asg.job_id
1027 and job.job_information_category = 'US'
1028 and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
1029 AND job.job_information1 = p_job_code
1030 and asg.business_group_id = P_BUSINESS_GROUP_ID
1031 and peo.business_group_id = P_BUSINESS_GROUP_ID
1032 and job.business_group_id = P_BUSINESS_GROUP_ID
1033 AND EXISTS (
1034 SELECT 'X'
1035 FROM HR_ORGANIZATION_INFORMATION HOI1,
1036 HR_ORGANIZATION_INFORMATION HOI2
1037 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1038 AND hoi1.org_information_context = 'Reporting Statuses'
1039 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
1040 AND asg.employment_category = hoi2.org_information1
1041 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
1042 AND hoi2.org_information_context = 'Reporting Categories'
1043 AND hoi1.organization_id = hoi2.organization_id
1044 )
1045 and asg.location_id = pgn.entity_id
1046 and (pgn.hierarchy_node_id = l_est_node_id
1047 or pgn.parent_hierarchy_node_id = l_est_node_id)
1048 and pgn.node_type in ('EST','LOC')
1049 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
1050
1051 MINUS
1052
1053 SELECT
1054 peo.person_id
1055 ,peo.last_name
1056 ,peo.first_name
1057 ,peo.employee_number
1058 ,peo.per_information25 veteran
1059 ,job.job_information1 job_category
1060 ,asg.assignment_id
1061
1062 from
1063 per_all_people_f peo,
1064 per_all_assignments_f asg,
1065 per_jobs_vl job
1066 ,per_gen_hierarchy_nodes pgn
1067 where
1068 peo.person_id = asg.person_id
1069 and l_month_end_date between peo.effective_start_date and peo.effective_end_date
1070 and peo.current_employee_flag = 'Y'
1071 and asg.assignment_type = 'E'
1072 and asg.primary_flag = 'Y'
1073 and l_month_end_date between asg.effective_start_date and asg.effective_end_date
1074 and peo.effective_start_date = (select max(peo2.effective_start_date)
1075 from per_all_people_f peo2
1076 where peo2.person_id = peo.person_id
1077 and peo2.current_employee_flag = 'Y'
1078 and peo2.effective_start_date <= l_month_end_date )
1079 and asg.effective_start_date = (select max(paf2.effective_start_date)
1080 from per_all_assignments_f paf2
1081 where paf2.person_id = asg.person_id
1082 and paf2.primary_flag = 'Y'
1083 and paf2.assignment_type = 'E'
1084 and paf2.effective_start_date <= l_month_end_date)
1085 and job.job_id = asg.job_id
1086 and job.job_information_category = 'US'
1087 and l_month_end_date between job.date_from and nvl(job.date_to,l_month_end_date)
1088 AND job.job_information1 = p_job_code
1089 and asg.business_group_id = P_BUSINESS_GROUP_ID
1090 and peo.business_group_id = P_BUSINESS_GROUP_ID
1091 and job.business_group_id = P_BUSINESS_GROUP_ID
1092 AND EXISTS (
1093 SELECT 'X'
1094 FROM HR_ORGANIZATION_INFORMATION HOI1,
1095 HR_ORGANIZATION_INFORMATION HOI2
1096 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1097 AND hoi1.org_information_context = 'Reporting Statuses'
1098 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
1099 AND asg.employment_category = hoi2.org_information1
1100 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
1101 AND hoi2.org_information_context = 'Reporting Categories'
1102 AND hoi1.organization_id = hoi2.organization_id
1103 )
1104 and asg.location_id = pgn.entity_id
1105 and (pgn.hierarchy_node_id = l_est_node_id
1106 or pgn.parent_hierarchy_node_id = l_est_node_id)
1107 and pgn.node_type in ('EST','LOC')
1108 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
1109 and check_recent_or_not(peo.person_id,l_month_end_date) > 0 ;
1110
1111
1112
1113 l_est_entity number := 0;
1114 l_est_node_id number := 0;
1115 l_no_est_emps number := 0;
1116
1117 loc_no_tot_emps number := 0;
1118 loc_no_dis_vets number := 0;
1119 loc_no_other_vets number := 0;
1120 loc_no_armed_vets number := 0;
1121 loc_no_recsep_vets number := 0;
1122 loc_no_not_vets number :=0;
1123
1124 state_no_dis_vets number := 0;
1125 state_no_other_vets number := 0;
1126 state_no_armed_vets number := 0;
1127 state_no_recsep_vets number := 0;
1128 state_no_tot_emps number := 0;
1129
1130 loc_no_tot_nh number :=0;
1131 loc_nh_dis_vets number :=0;
1132 loc_nh_other_vets number :=0;
1133 loc_nh_armed_vets number :=0;
1134 loc_nh_recsep_vets number :=0;
1135 loc_nh_not_vets number :=0;
1136
1137 state_nh_dis_vets number := 0;
1138 state_nh_other_vets number := 0;
1139 state_nh_armed_vets number := 0;
1140 state_nh_recsep_vets number := 0;
1141 state_nh_tot_vets number := 0;
1142
1143 begin
1144
1145 open c_est_entity(p_state);
1146
1147 loop
1148 fetch c_est_entity into l_est_entity,l_est_node_id;
1149 exit when c_est_entity%notfound;
1150
1151 l_no_est_emps := 0;
1152
1153 loc_no_tot_emps := 0;
1154 loc_no_dis_vets := 0;
1155 loc_no_other_vets := 0;
1156 loc_no_armed_vets := 0;
1157 loc_no_recsep_vets := 0;
1158 loc_no_not_vets := 0;
1159
1160 loc_no_tot_nh := 0;
1161 loc_nh_dis_vets := 0;
1162 loc_nh_other_vets := 0;
1163 loc_nh_armed_vets := 0;
1164 loc_nh_recsep_vets := 0;
1165 loc_nh_not_vets := 0;
1166
1167
1168 open c_tot_emps(l_est_node_id,l_month_end_date);
1169 fetch c_tot_emps into l_no_est_emps;
1170 close c_tot_emps;
1171
1172 if l_no_est_emps < 50
1173 then
1174
1175 open c_emps(l_est_node_id);
1176 fetch c_emps
1177 into loc_no_dis_vets,loc_no_other_vets, loc_no_armed_vets,loc_no_recsep_vets,loc_no_not_vets,loc_no_tot_emps; --#10113747
1178
1179 /* #10113747 commented as adding individual column data will lead to inflated count
1180 loc_no_tot_emps := loc_no_dis_vets + loc_no_other_vets + loc_no_armed_vets + loc_no_recsep_vets + loc_no_not_vets;
1181 */
1182
1183 state_no_dis_vets := state_no_dis_vets + loc_no_dis_vets;
1184 state_no_other_vets := state_no_other_vets + loc_no_other_vets;
1185 state_no_armed_vets := state_no_armed_vets + loc_no_armed_vets;
1186 state_no_recsep_vets := state_no_recsep_vets + loc_no_recsep_vets;
1187 state_no_tot_emps := state_no_tot_emps + loc_no_tot_emps;
1188
1189 close c_emps;
1190
1191
1192
1193 open c_nh(l_est_node_id);
1194 fetch c_nh
1195 INTO loc_nh_dis_vets,
1196 loc_nh_other_vets,
1197 loc_nh_armed_vets,
1198 loc_nh_recsep_vets,
1199 loc_nh_not_vets,
1200 loc_no_tot_nh ; --#10113747
1201
1202 /* #10113747 commented as adding individual column data will lead to inflated count
1203 loc_no_tot_nh := loc_nh_dis_vets+loc_nh_other_vets+loc_nh_armed_vets+loc_nh_recsep_vets+loc_nh_not_vets;
1204 */
1205
1206 state_nh_dis_vets := state_nh_dis_vets + loc_nh_dis_vets;
1207 state_nh_other_vets := state_nh_other_vets + loc_nh_other_vets ;
1208 state_nh_armed_vets := state_nh_armed_vets + loc_nh_armed_vets ;
1209 state_nh_recsep_vets := state_nh_recsep_vets + loc_nh_recsep_vets ;
1210 state_nh_tot_vets := state_nh_tot_vets + loc_no_tot_nh ;
1211
1212 close c_nh;
1213
1214 if P_AUDIT_REPORT = 'Y' then
1215 for per in c_audit_report_emps(l_est_node_id)
1216 loop
1217 l_buffer := per.person_id || g_delimiter ||
1218 per.last_name || g_delimiter ||
1219 nvl(per.first_name,' ') || g_delimiter ||
1220 nvl(per.employee_number,' ') || g_delimiter ||
1221 per.veteran || g_delimiter ||
1222 per.veteran_category || g_delimiter ||
1223 p_job_category_name || g_delimiter ||
1224 per.assignment_id || g_delimiter ||
1225 p_state ||
1226 g_eol;
1227 write_audit.put(l_buffer);
1228 end loop;
1229 end if;
1230 if P_AUDIT_REPORT = 'Y' and P_SHOW_NEW_HIRES = 'Y' then
1231 for per in c_audit_report_nh(l_est_node_id)
1232 loop
1233 l_buffer := per.person_id || g_delimiter ||
1234 per.last_name || g_delimiter ||
1235 nvl(per.first_name,' ') || g_delimiter ||
1236 nvl(per.employee_number,' ') || g_delimiter ||
1237 per.veteran || g_delimiter ||
1238 per.veteran_category || g_delimiter ||
1239 p_job_category_name || g_delimiter ||
1240 per.assignment_id || g_delimiter ||
1241 p_state ||
1242 g_eol;
1243 write_audit.put(l_buffer);
1244 end loop;
1245 end if;
1246
1247 for per in c_nonreported(l_est_node_id)
1248 loop
1249 l_buffer := per.person_id || g_delimiter ||
1250 per.last_name || g_delimiter ||
1251 nvl(per.first_name,' ') || g_delimiter ||
1252 nvl(per.employee_number,' ') || g_delimiter ||
1253 per.veteran || g_delimiter ||
1254 p_job_category_name || g_delimiter ||
1255 per.assignment_id || g_delimiter ||
1256 p_state ||
1257 g_eol;
1258 write_to_concurrent_log(l_buffer);
1259 end loop;
1260 end if;
1261
1262 end loop;
1263 close c_est_entity;
1264
1265 l_xml_string := l_xml_string||'<LIST_G_4>';
1266 l_xml_string := l_xml_string||'<G_4>';
1267 l_xml_string := l_xml_string ||convert_into_xml('CP_NO_DIS_VETS',state_no_dis_vets,'D');
1268 l_xml_string := l_xml_string ||convert_into_xml('CP_NO_OTHER_VETS',state_no_other_vets,'D');
1269 l_xml_string := l_xml_string ||convert_into_xml('CP_NO_ARMED_VETS',state_no_armed_vets,'D');
1270 l_xml_string := l_xml_string ||convert_into_xml('CP_NO_RECSEP_VETS',state_no_recsep_vets,'D');
1271 l_xml_string := l_xml_string ||convert_into_xml('CP_NO_TOT_VETS',state_no_tot_emps,'D');
1272 l_xml_string := l_xml_string||'</G_4>';
1273 l_xml_string := l_xml_string||'</LIST_G_4>';
1274
1275 l_xml_string := l_xml_string||'<LIST_G_5>';
1276 l_xml_string := l_xml_string||'<G_5>';
1277 l_xml_string := l_xml_string ||convert_into_xml('CP_NH_DIS_VETS',state_nh_dis_vets,'D');
1278 l_xml_string := l_xml_string ||convert_into_xml('CP_NH_OTHER_VETS',state_nh_other_vets,'D');
1279 l_xml_string := l_xml_string ||convert_into_xml('CP_NH_ARMED_VETS',state_nh_armed_vets,'D');
1280 l_xml_string := l_xml_string ||convert_into_xml('CP_NH_RECSEP_VETS',state_nh_recsep_vets,'D');
1281 l_xml_string := l_xml_string ||convert_into_xml('CP_NH_TOT_VETS',state_nh_tot_vets,'D');
1282 l_xml_string := l_xml_string||'</G_5>';
1283 l_xml_string := l_xml_string||'</LIST_G_5>';
1284
1285
1286 CS_NO_DIS_VETS := CS_NO_DIS_VETS + state_no_dis_vets;
1287 CS_NO_OTHER_VETS := CS_NO_OTHER_VETS + state_no_other_vets;
1288 CS_NO_ARMED_VETS := CS_NO_ARMED_VETS + state_no_armed_vets;
1289 CS_NO_RECSEP_VETS := CS_NO_RECSEP_VETS + state_no_recsep_vets;
1290 CS_NO_TOT_VETS := CS_NO_TOT_VETS + state_no_tot_emps;
1291
1292 CS_NH_DIS_VETS := CS_NH_DIS_VETS + state_nh_dis_vets;
1293 CS_NH_OTHER_VETS := CS_NH_OTHER_VETS + state_nh_other_vets;
1294 CS_NH_ARMED_VETS := CS_NH_ARMED_VETS + state_nh_armed_vets;
1295 CS_NH_RECSEP_VETS := CS_NH_RECSEP_VETS + state_nh_recsep_vets;
1296 CS_NH_TOT_VETS := CS_NH_TOT_VETS + state_nh_tot_vets;
1297
1298 end vets_data;
1299
1300
1301 begin
1302
1303
1304 l_business_group_name := hr_reports.get_business_group(p_business_group_id);
1305 P_DATE_END1 := fnd_date.canonical_to_date(P_DATE_END);
1306
1307 if P_STATE is NULL then
1308 lc_state := 'All';
1309 else
1310 lc_state := P_STATE;
1311 end if;
1312
1313 open c_hier_details;
1314 fetch c_hier_details into
1315 l_hierarchy_name, l_hierarchy_version_num, l_parent_org_id, l_parent_node_id ;
1316 close c_hier_details;
1317
1318 open c_count_est;
1319 fetch c_count_est into l_est_no;
1320 close c_count_est;
1321
1322 l_xml_string := '<?xml version="1.0"?>';
1323 l_xml_string := l_xml_string ||'<PERRPVTC_100A>';
1324 l_xml_string := l_xml_string ||convert_into_xml('C_BUSINESS_GROUP_NAME',l_business_group_name,'D');
1325 l_xml_string := l_xml_string ||convert_into_xml('C_END_OF_TIME',P_DATE_END1,'D');
1326 l_xml_string := l_xml_string ||convert_into_xml('C_HIERARCHY_NAME',l_hierarchy_name,'D');
1327 l_xml_string := l_xml_string ||convert_into_xml('C_HIERARCHY_VERSION_NUM',l_hierarchy_version_num,'D');
1328 l_xml_string := l_xml_string ||convert_into_xml('C_PARENT_ORG_ID',l_parent_org_id,'D');
1329 l_xml_string := l_xml_string ||convert_into_xml('C_PARENT_NODE_ID',l_parent_node_id,'D');
1330 l_xml_string := l_xml_string ||convert_into_xml('C_NO_OF_ESTABLISHMENTS',l_est_no,'D');
1331 l_xml_string := l_xml_string ||convert_into_xml('C_DATE_END',P_DATE_END1,'D');
1332 l_xml_string := l_xml_string ||convert_into_xml('C_SHOW_NEW_HIRES',p_show_new_hires,'D');
1333 l_xml_string := l_xml_string ||convert_into_xml('C_SHOW_TOTALS',p_show_totals,'D');
1334 l_xml_string := l_xml_string ||convert_into_xml('C_STATE',lc_state,'D');
1335 write_to_concurrent_out(l_xml_string);
1336 l_xml_string :='';
1337
1338 if P_AUDIT_REPORT = 'Y' then
1339 write_audit.open('PERRPVTC_100A');
1340 l_buffer := 'Person Id' || g_delimiter ||
1341 'Last Name' || g_delimiter ||
1342 'First Name' || g_delimiter ||
1343 'Employee Number' || g_delimiter ||
1344 'Veteran Status' || g_delimiter ||
1345 'Veteran Category' || g_delimiter ||
1346 'Job Category' || g_delimiter ||
1347 'Assignment Id' || g_delimiter ||
1348 'State' ||
1349 g_eol;
1350 write_audit.put(l_buffer);
1351 end if;
1352
1353 write_to_concurrent_log('Please find the Employee details who are not counted under Recently Seperated Veteran Category.Please Correct them');
1354
1355 l_buffer := 'Person Id' || g_delimiter ||
1356 'Last Name' || g_delimiter ||
1357 'First Name' || g_delimiter ||
1358 'Employee Number' || g_delimiter ||
1359 'Veteran Status' || g_delimiter ||
1360 'Job Category' || g_delimiter ||
1361 'Assignment Id' || g_delimiter ||
1362 'State' ||
1363 g_eol;
1364
1365 write_to_concurrent_log(l_buffer);
1366
1367 l_xml_string := l_xml_string||'<LIST_G_PARENT>';
1368
1369 open c_parent(l_parent_org_id);
1370 fetch c_parent into
1371 l_reporting_name,l_company_number,l_type_of_rep_org,l_parent_address,l_parent_city,
1372 l_parent_county,l_parent_state,l_parent_zip,l_contact_name,l_contact_telnum,l_contact_email,
1373 l_contact_telnum_and_email ;
1374
1375 open c_defaults(l_parent_org_id);
1376 fetch c_defaults into
1377 l_def_sic,l_def_naics,l_def_gre,l_def_duns,l_def_gov_con ;
1378 close c_defaults;
1379
1380
1381 l_xml_string := l_xml_string||'<G_PARENT>';
1382
1383 l_xml_string := l_xml_string ||convert_into_xml('CONTACT_NAME',l_contact_name,'D');
1384 l_xml_string := l_xml_string ||convert_into_xml('CONTACT_TELNUM',l_contact_telnum,'D');
1385 l_xml_string := l_xml_string ||convert_into_xml('CONTACT_EMAIL',l_contact_email,'D');
1386 l_xml_string := l_xml_string ||convert_into_xml('PARENT_NODE_ID',l_parent_node_id,'D');
1387 l_xml_string := l_xml_string ||convert_into_xml('REPORTING_NAME',l_reporting_name,'D');
1388 l_xml_string := l_xml_string ||convert_into_xml('COMPANY_NUMBER',l_company_number,'D');
1389 l_xml_string := l_xml_string ||convert_into_xml('TYPE_OF_REP_ORG',l_type_of_rep_org,'D');
1390 l_xml_string := l_xml_string ||convert_into_xml('PARENT_ADDRESS',l_parent_address,'D');
1391 l_xml_string := l_xml_string ||convert_into_xml('PARENT_CITY',l_parent_city,'D');
1392 l_xml_string := l_xml_string ||convert_into_xml('PARENT_COUNTY',l_parent_county,'D');
1393 l_xml_string := l_xml_string ||convert_into_xml('PARENT_STATE',l_parent_state,'D');
1394 l_xml_string := l_xml_string ||convert_into_xml('PARENT_ZIP',l_parent_zip,'D');
1395 l_xml_string := l_xml_string ||convert_into_xml('C_DEF_SIC',l_def_sic,'D');
1396 l_xml_string := l_xml_string ||convert_into_xml('C_DEF_NAICS',l_def_naics,'D');
1397 l_xml_string := l_xml_string ||convert_into_xml('C_DEF_GRE',l_def_gre,'D');
1398 l_xml_string := l_xml_string ||convert_into_xml('C_DEF_DUNS',l_def_duns,'D');
1399 l_xml_string := l_xml_string ||convert_into_xml('C_DEF_GOV_CON',l_def_gov_con,'D');
1400
1401 l_xml_string := l_xml_string||'<LIST_G_STATE>';
1402
1403 write_to_concurrent_out(l_xml_string);
1404 l_xml_string :='';
1405
1406 if l_type_of_rep_org = '1P' then
1407 cp_prime_contractor := '[X]';
1408 cp_sub_contractor := '[ ]';
1409 elsif l_type_of_rep_org = '2S' then
1410 cp_prime_contractor := '[ ]';
1411 cp_sub_contractor := '[X]';
1412 elsif l_type_of_rep_org = '3B' then
1413 cp_prime_contractor := '[X]';
1414 cp_sub_contractor := '[X]';
1415 else
1416 cp_prime_contractor := '[ ]';
1417 cp_sub_contractor := '[ ]';
1418 end if;
1419
1420 open c_state(l_parent_node_id);
1421 loop
1422 fetch c_state into l_state;
1423 exit when c_state%notfound;
1424
1425 select
1426 meaning
1427 into l_state_meaning
1428 from
1429 hr_lookups
1430 where
1431 lookup_type = 'US_STATE'
1432 and lookup_code = l_state;
1433
1434 CS_NO_DIS_VETS :=0;
1435 CS_NO_OTHER_VETS :=0;
1436 CS_NO_ARMED_VETS :=0;
1437 CS_NO_RECSEP_VETS :=0;
1438 CS_NO_TOT_VETS :=0;
1439 CS_NH_DIS_VETS :=0;
1440 CS_NH_OTHER_VETS :=0;
1441 CS_NH_ARMED_VETS :=0;
1442 CS_NH_RECSEP_VETS :=0;
1443 CS_NH_TOT_VETS :=0;
1444 CP_MSC := 0;
1445
1446 l_xml_string := l_xml_string||'<G_STATE>';
1447 l_xml_string := l_xml_string ||convert_into_xml('CONTACT_NAME1',l_contact_name,'D');
1448 l_xml_string := l_xml_string ||convert_into_xml('CONTACT_TELNUM_AND_EMAIL',l_contact_telnum,'D');
1449 l_xml_string := l_xml_string ||convert_into_xml('P_DATE_END1',P_DATE_END1,'D');
1450 l_xml_string := l_xml_string ||convert_into_xml('STATE',l_state,'D');
1451 l_xml_string := l_xml_string ||convert_into_xml('PAR_REP_NAME',l_reporting_name,'D');
1452 l_xml_string := l_xml_string ||convert_into_xml('PAR_COMP_NUMBER',l_company_number,'D');
1453 l_xml_string := l_xml_string ||convert_into_xml('PAR_TYPE_OF_ORG',l_type_of_rep_org,'D');
1454 l_xml_string := l_xml_string ||convert_into_xml('PAR_ADDRESS',l_parent_address,'D');
1455 l_xml_string := l_xml_string ||convert_into_xml('PAR_CITY',l_parent_city,'D');
1456 l_xml_string := l_xml_string ||convert_into_xml('PAR_COUNTY',l_parent_county,'D');
1457 l_xml_string := l_xml_string ||convert_into_xml('PAR_STATE',l_parent_state,'D');
1458 l_xml_string := l_xml_string ||convert_into_xml('PAR_ZIP',l_parent_zip,'D');
1459 l_xml_string := l_xml_string ||convert_into_xml('CP_REP_STATE',l_state_meaning,'D');
1460 l_xml_string := l_xml_string ||convert_into_xml('CP_PRIME_CONTRACTOR',cp_prime_contractor,'D');
1461 l_xml_string := l_xml_string ||convert_into_xml('CP_SUB_CONTRACTOR',cp_sub_contractor,'D');
1462 l_xml_string := l_xml_string ||convert_into_xml('SHOW_TOTALS',P_SHOW_TOTALS,'D');
1463
1464 l_xml_string := l_xml_string||'<LIST_G_3>';
1465
1466 write_to_concurrent_out(l_xml_string);
1467 l_xml_string :='';
1468
1469 open c_job_categories;
1470 loop
1471 fetch c_job_categories into l_display_order,l_job_code , l_cons_job_category_name ,
1472 l_cons_job_category_code , l_job_category_name ;
1473 exit when c_job_categories%notfound;
1474
1475 l_xml_string := l_xml_string||'<G_3>';
1476
1477 l_xml_string := l_xml_string ||convert_into_xml('DISPLAY_ORDER',l_display_order,'D');
1478 l_xml_string := l_xml_string ||convert_into_xml('JOB_CODE',l_job_code,'D');
1479 l_xml_string := l_xml_string ||convert_into_xml('JOB_CATEGORY_NAME',l_job_category_name,'D');
1480 l_xml_string := l_xml_string ||convert_into_xml('CONS_JOB_CATEGORY_NAME',l_cons_job_category_name,'D');
1481 l_xml_string := l_xml_string ||convert_into_xml('CONS_JOB_CATEGORY_CODE',l_cons_job_category_code,'D');
1482 l_xml_string := l_xml_string ||convert_into_xml('SHOW_NEW_HIRES',P_SHOW_NEW_HIRES,'D');
1483
1484 vets_data(l_state,l_job_code,l_job_category_name);
1485
1486 l_xml_string := l_xml_string||'</G_3>';
1487 write_to_concurrent_out(l_xml_string);
1488 l_xml_string :='';
1489
1490 end loop;
1491 close c_job_categories;
1492
1493 l_xml_string := l_xml_string||'</LIST_G_3>';
1494
1495
1496 min_max(l_state);
1497
1498 cp_msc := count_locsFormula(l_state,l_parent_node_id);
1499
1500 l_xml_string := l_xml_string ||convert_into_xml('CP_MSC',cp_msc,'D');
1501 l_xml_string := l_xml_string ||convert_into_xml('CS_NO_DIS_VETS',CS_NO_DIS_VETS,'D');
1502 l_xml_string := l_xml_string ||convert_into_xml('CS_NO_OTHER_VETS',CS_NO_OTHER_VETS,'D');
1503 l_xml_string := l_xml_string ||convert_into_xml('CS_NO_ARMED_VETS',CS_NO_ARMED_VETS,'D');
1504 l_xml_string := l_xml_string ||convert_into_xml('CS_NO_RECSEP_VETS',CS_NO_RECSEP_VETS,'D');
1505 l_xml_string := l_xml_string ||convert_into_xml('CS_NO_TOT_VETS',CS_NO_TOT_VETS,'D');
1506 l_xml_string := l_xml_string ||convert_into_xml('CS_NH_DIS_VETS',CS_NH_DIS_VETS,'D');
1507 l_xml_string := l_xml_string ||convert_into_xml('CS_NH_OTHER_VETS',CS_NH_OTHER_VETS,'D');
1508 l_xml_string := l_xml_string ||convert_into_xml('CS_NH_ARMED_VETS',CS_NH_ARMED_VETS,'D');
1509 l_xml_string := l_xml_string ||convert_into_xml('CS_NH_RECSEP_VETS',CS_NH_RECSEP_VETS,'D');
1510 l_xml_string := l_xml_string ||convert_into_xml('CS_NH_TOT_VETS',CS_NH_TOT_VETS,'D');
1511
1512 l_xml_string := l_xml_string||'</G_STATE>';
1513
1514 write_to_concurrent_out(l_xml_string);
1515 l_xml_string :='';
1516
1517 end loop;
1518 close c_state;
1519
1520 l_xml_string := l_xml_string||'</LIST_G_STATE>';
1521 l_xml_string := l_xml_string||'</G_PARENT>';
1522
1523 close c_parent;
1524
1525 l_xml_string := l_xml_string||'</LIST_G_PARENT>';
1526 l_xml_string := l_xml_string||'</PERRPVTC_100A>';
1527
1528 write_to_concurrent_out(l_xml_string);
1529 l_xml_string :='';
1530
1531 if P_AUDIT_REPORT = 'Y' then
1532 write_audit.close;
1533 end if;
1534
1535 End GET_VETS100A_DATA;
1536
1537 FUNCTION convert_into_xml( p_name IN VARCHAR2,
1538 p_value IN VARCHAR2,
1539 p_type IN char)
1540 RETURN VARCHAR2 IS
1541 l_convert_data VARCHAR2(300);
1542 BEGIN
1543 IF p_type = 'D' THEN
1544 l_convert_data := '<'||p_name||'>'||'<![CDATA['||p_value||']]>'||'</'||p_name||'>';
1545 ELSE
1546 l_convert_data := '<'||p_name||'>';
1547 END IF;
1548 RETURN(l_convert_data);
1549 END convert_into_xml;
1550
1551 PROCEDURE write_to_concurrent_out (p_text VARCHAR2) IS
1552 BEGIN
1553
1554 fnd_file.put_line(fnd_file.OUTPUT, p_text);
1555
1556 END write_to_concurrent_out;
1557
1558 PROCEDURE write_to_concurrent_log (p_text VARCHAR2) IS
1559 BEGIN
1560
1561 fnd_file.put_line(fnd_file.LOG, p_text);
1562
1563 END write_to_concurrent_log;
1564
1565
1566 End per_us_vets_100a_cons_pkg ;
1567