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