[Home] [Help]
PACKAGE BODY: APPS.PER_US_VETS_100A_LIST_PKG
Source
1 PACKAGE BODY per_us_vets_100a_list_pkg as
2 /* $Header: pervetsl100a.pkb 120.14.12020000.2 2012/07/05 05:00:30 amnaraya 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
23 );
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
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,l_parent_node_id varchar2)is
67 select
68 l_parent_node_id,
69 hoi1.org_information1 "Par Report Name"
70 from
71 hr_organization_information hoi1
72 where
73 hoi1.organization_id = l_parent_org_id
74 and hoi1.org_information_context = 'VETS_Spec' ;
75
76 cursor c_establishment(l_parent_node_id varchar2) is
77 Select
78 pghn.hierarchy_node_id "Est_Node_Id"
79 ,pghn.parent_hierarchy_node_id "Parent Node Id"
80 ,upper(hlei1.lei_information1) "Est_Rep_Name"
81 ,hlei2.lei_information10 "Headquarters"
82 ,upper(ltrim(rtrim(eloc.address_line_1))||' '||
83 ltrim(rtrim(eloc.address_line_2))||' '||
84 ltrim(rtrim(eloc.address_line_3))||', '||
85 ltrim(rtrim(eloc.town_or_city))||', '||
86 ltrim(rtrim(eloc.region_1))||', '||
87 ltrim(rtrim(eloc.region_2))||' '||
88 ltrim(rtrim(eloc.postal_code))) "Estab Address"
89 from
90 hr_location_extra_info hlei1
91 ,hr_location_extra_info hlei2
92 ,per_gen_hierarchy_nodes pghn
93 ,hr_locations_all eloc
94 where
95 (hlei1.information_type = 'VETS-100 Specific Information'
96 and hlei1.lei_information_category= 'VETS-100 Specific Information')
97 and (hlei2.information_type = 'Establishment Information'
98 and hlei2.lei_information_category= 'Establishment Information')
99 and hlei1.location_id = hlei2.location_id
100 and hlei1.location_id = pghn.entity_id
101 and pghn.parent_hierarchy_node_id = l_parent_node_id
102 and pghn.node_type = 'EST'
103 and eloc.location_id = pghn.entity_id;
104
105 l_business_group_name varchar2(240); --Increased variable size for Bug#9038285
106 c_date_end date;
107 l_hierarchy_name per_gen_hierarchy.name%type;
108 l_hierarchy_version_num per_gen_hierarchy_versions.VERSION_NUMBER%type;
109 l_parent_org_id varchar2(20);
110 l_parent_node_id per_gen_hierarchy_nodes.parent_hierarchy_node_id%type;
111 l_parent_node_id1 per_gen_hierarchy_nodes.parent_hierarchy_node_id%type;
112 l_est_no number;
113
114 l_reporting_name hr_organization_information.org_information1%type;
115 l_Est_Node_Id per_gen_hierarchy_nodes.hierarchy_node_id%type;
116 l_Est_Rep_Name hr_location_extra_info.lei_information1%type;
117 l_Headquarters hr_location_extra_info.lei_information10%type;
118 l_est_add clob;
119 l_report_date varchar2(11);
120
121 l_xml_string clob :='';
122
123 l_buffer varchar2(200);
124 g_delimiter varchar2(10) := ',' ;
125 g_eol varchar2(1) := fnd_global.local_chr(10);
126
127 procedure C_tot_actFormula(l_est_node_id varchar2,l_Est_Rep_Name in varchar2) is
128
129 l_count_emps number := 0;
130 l_end_date date := fnd_date.canonical_to_date(P_DATE_END);
131 TOT_COUNT_EMPS number := 0;
132
133 cursor c_vets is
134
135 SELECT
136 peo.person_id
137 ,peo.last_name
138 ,peo.first_name
139 ,peo.employee_number
140 ,peo.per_information25 veteran
141 ,job.job_information1 job_category
142 ,asg.assignment_id
143 ,decode(peo.per_information25,'NOTVET',' ','VET',' ')||
144 decode(peo.per_information25,'VETDIS','L','AFSMNSDIS','L','OTEDV','L','AFSMDIS','L','NSDIS','L','AFSMDISOP','L','AFSMNSDISOP','L','NSDISOP','L')||
145 decode(peo.per_information25,'OTEV','M','OTEDV','M','AFSMDISOP','M','AFSMNSDISOP','M','AFSMOP','M','NSOP','M','AFSMNSOP','M','NSDISOP','M')||
146 decode(peo.per_information25,'AFSM','N','AFSMNSDIS','N','AFSMDIS','N','AFSMDISOP','N','AFSMNSDISOP','N','AFSMOP','N','AFSMNSOP','N','AFSMNS','N') veteran_category
147 from per_all_people_f peo,
148 per_all_assignments_f asg,
149 per_jobs_vl job
150 where peo.person_id = asg.person_id
151 and job.job_information_category = 'US'
152 and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
156 and asg.assignment_type = 'E'
153 and job.job_information1 is not null
154 and asg.job_id = job.job_id
155 and asg.business_group_id = P_BUSINESS_GROUP_ID
157 and asg.primary_flag = 'Y'
158 and exists (
159 SELECT 'X'
160 FROM HR_ORGANIZATION_INFORMATION HOI1,
161 HR_ORGANIZATION_INFORMATION HOI2
162 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
163 AND hoi1.org_information_context = 'Reporting Statuses'
164 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
165 AND asg.employment_category = hoi2.org_information1
166 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
167 AND hoi2.org_information_context = 'Reporting Categories' )
168 and asg.effective_start_date =
169 (select max(ass1.effective_start_date)
170 from per_all_assignments_f ass1
171 where l_end_date between ass1.effective_start_date and ass1.effective_end_date
172 and asg.person_id = ass1.person_id
173 and ass1.assignment_type = 'E'
174 and ass1.primary_flag = 'Y'
175 )
176 and l_end_date between asg.effective_start_date and asg.effective_end_date
177 and l_end_date between peo.effective_start_date and peo.effective_end_date
178 and asg.location_id in
179 (select distinct pgn.entity_id
180 from per_gen_hierarchy_nodes pgn
181 where pgn.hierarchy_version_id = p_hierarchy_version_id
182 AND (
183 pgn.hierarchy_node_id = l_est_node_id
184 OR pgn.parent_hierarchy_node_id = l_est_node_id)
185 and pgn.node_type in ('EST','LOC')
186 )
187
188 union
189
190 SELECT
191 peo.person_id
192 ,peo.last_name
193 ,peo.first_name
194 ,peo.employee_number
195 ,peo.per_information25 veteran
196 ,job.job_information1 job_category
197 ,asg.assignment_id
198 ,decode(peo.per_information25,'NS','O','AFSMNSDIS','O','NSDIS','O','AFSMNSDISOP','O', 'NSOP','O','AFSMNSOP','O','AFSMNS','O','NSDISOP','O') veteran_category
199 from per_all_people_f peo,
200 per_all_assignments_f asg,
201 per_jobs_vl job
202 where peo.person_id = asg.person_id
203 and job.job_information_category = 'US'
204 and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
205 and job.job_information1 is not null
206 and asg.job_id = job.job_id
207 and asg.business_group_id = P_BUSINESS_GROUP_ID
208 and asg.assignment_type = 'E'
209 and asg.primary_flag = 'Y'
210 and exists (
211 SELECT 'X'
212 FROM HR_ORGANIZATION_INFORMATION HOI1,
213 HR_ORGANIZATION_INFORMATION HOI2
214 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
215 AND hoi1.org_information_context = 'Reporting Statuses'
216 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
217 AND asg.employment_category = hoi2.org_information1
218 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
219 AND hoi2.org_information_context = 'Reporting Categories' )
220 and asg.effective_start_date =
221 (select max(ass1.effective_start_date)
222 from per_all_assignments_f ass1
223 where l_end_date between ass1.effective_start_date and ass1.effective_end_date
224 and asg.person_id = ass1.person_id
225 and ass1.assignment_type = 'E'
226 and ass1.primary_flag = 'Y'
227 )
228 and l_end_date between asg.effective_start_date and asg.effective_end_date
229 and l_end_date between peo.effective_start_date and peo.effective_end_date
230 and asg.location_id in
231 (select distinct pgn.entity_id
232 from per_gen_hierarchy_nodes pgn
233 where pgn.hierarchy_version_id = p_hierarchy_version_id
234 AND (
235 pgn.hierarchy_node_id = l_est_node_id
236 OR pgn.parent_hierarchy_node_id = l_est_node_id)
237 and pgn.node_type in ('EST','LOC')
238 )
239 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
240 and check_recent_or_not(peo.person_id,l_end_date) > 0 ;
241
242 cursor c_nonreported is
243
244 SELECT
245 peo.person_id
246 ,peo.last_name
247 ,peo.first_name
248 ,peo.employee_number
249 ,peo.per_information25 veteran
250 ,asg.assignment_id
251
252 from per_all_people_f peo,
253 per_all_assignments_f asg,
254 per_jobs_vl job
255 where peo.person_id = asg.person_id
256 and job.job_information_category = 'US'
257 and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
258 and job.job_information1 is not null
259 and asg.job_id = job.job_id
260 and asg.business_group_id = P_BUSINESS_GROUP_ID
261 and asg.assignment_type = 'E'
262 and asg.primary_flag = 'Y'
263 and exists (
264 SELECT 'X'
265 FROM HR_ORGANIZATION_INFORMATION HOI1,
266 HR_ORGANIZATION_INFORMATION HOI2
267 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
268 AND hoi1.org_information_context = 'Reporting Statuses'
269 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
270 AND asg.employment_category = hoi2.org_information1
274 (select max(ass1.effective_start_date)
271 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
272 AND hoi2.org_information_context = 'Reporting Categories' )
273 and asg.effective_start_date =
275 from per_all_assignments_f ass1
276 where l_end_date between ass1.effective_start_date and ass1.effective_end_date
277 and asg.person_id = ass1.person_id
278 and ass1.assignment_type = 'E'
279 and ass1.primary_flag = 'Y'
280 )
281 and l_end_date between asg.effective_start_date and asg.effective_end_date
282 and l_end_date between peo.effective_start_date and peo.effective_end_date
283 and asg.location_id in
284 (select distinct pgn.entity_id
285 from per_gen_hierarchy_nodes pgn
286 where pgn.hierarchy_version_id = p_hierarchy_version_id
287 AND (
288 pgn.hierarchy_node_id = l_est_node_id
289 OR pgn.parent_hierarchy_node_id = l_est_node_id)
290 and pgn.node_type in ('EST','LOC')
291 )
292 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
293
294 MINUS
295
296 SELECT
297 peo.person_id
298 ,peo.last_name
299 ,peo.first_name
300 ,peo.employee_number
301 ,peo.per_information25 veteran
302 ,asg.assignment_id
303 from per_all_people_f peo,
304 per_all_assignments_f asg,
305 per_jobs_vl job
306 where peo.person_id = asg.person_id
307 and job.job_information_category = 'US'
308 and l_end_date between job.date_from and nvl(job.date_to,l_end_date)
309 and job.job_information1 is not null
310 and asg.job_id = job.job_id
311 and asg.business_group_id = P_BUSINESS_GROUP_ID
312 and asg.assignment_type = 'E'
313 and asg.primary_flag = 'Y'
314 and exists (
315 SELECT 'X'
316 FROM HR_ORGANIZATION_INFORMATION HOI1,
317 HR_ORGANIZATION_INFORMATION HOI2
318 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
319 AND hoi1.org_information_context = 'Reporting Statuses'
320 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
321 AND asg.employment_category = hoi2.org_information1
322 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
323 AND hoi2.org_information_context = 'Reporting Categories' )
324 and asg.effective_start_date =
325 (select max(ass1.effective_start_date)
326 from per_all_assignments_f ass1
327 where l_end_date between ass1.effective_start_date and ass1.effective_end_date
328 and asg.person_id = ass1.person_id
329 and ass1.assignment_type = 'E'
330 and ass1.primary_flag = 'Y'
331 )
332 and l_end_date between asg.effective_start_date and asg.effective_end_date
333 and l_end_date between peo.effective_start_date and peo.effective_end_date
334 and asg.location_id in
335 (select distinct pgn.entity_id
336 from per_gen_hierarchy_nodes pgn
337 where pgn.hierarchy_version_id = p_hierarchy_version_id
338 AND (
339 pgn.hierarchy_node_id = l_est_node_id
340 OR pgn.parent_hierarchy_node_id = l_est_node_id)
341 and pgn.node_type in ('EST','LOC')
342 )
343 and peo.per_information25 in ('NS','AFSMNSDIS','NSDIS','AFSMNSDISOP','NSOP','AFSMNSOP','AFSMNS','NSDISOP')
344 and check_recent_or_not(peo.person_id,l_end_date) > 0 ;
345
346
347 begin
348
349 --Made fix for 8724355 - Removed the join condition with per_jobs_vl
350 select
351 count(distinct asg.person_id)
352 into l_count_emps
353 from per_all_assignments_f asg,
354 per_periods_of_service pps /*8667924*/
355 where
356 asg.business_group_id = P_BUSINESS_GROUP_ID
357 and asg.assignment_type = 'E'
358 and asg.primary_flag = 'Y'
359 and asg.person_id = pps.person_id
360 and asg.business_group_id = pps.business_group_id
361 and
362 (
363 exists (
364 SELECT 'X'
365 FROM HR_ORGANIZATION_INFORMATION HOI1,
366 HR_ORGANIZATION_INFORMATION HOI2
367 WHERE TO_CHAR(asg.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
368 AND hoi1.org_information_context = 'Reporting Statuses'
369 AND hoi1.organization_id = P_BUSINESS_GROUP_ID
370 AND asg.employment_category = hoi2.org_information1
371 AND hoi2.organization_id = P_BUSINESS_GROUP_ID
372 AND hoi2.org_information_context = 'Reporting Categories' )
373 or
374 months_between(l_end_date,pps.actual_termination_date) between 0 and 12 /*8667924*/
375 )
376 and asg.effective_start_date =
377 (select max(ass1.effective_start_date)
378 from per_all_assignments_f ass1
379 where l_end_date between ass1.effective_start_date and ass1.effective_end_date
380 and asg.person_id = ass1.person_id
381 and ass1.assignment_type = 'E'
382 and ass1.primary_flag = 'Y'
383 )
384 and l_end_date between asg.effective_start_date and asg.effective_end_date
385 and asg.location_id in
386 (select distinct pgn.entity_id
387 from per_gen_hierarchy_nodes pgn
388 where pgn.hierarchy_version_id = p_hierarchy_version_id
389 AND (
390 pgn.hierarchy_node_id = l_est_node_id
391 OR pgn.parent_hierarchy_node_id = l_est_node_id)
392 and pgn.node_type in ('EST','LOC')
396 IF l_count_emps between 0 and 50 THEN
393 );
394
395
397 TOT_COUNT_EMPS := nvl(TOT_COUNT_EMPS,0)+l_count_emps;
398
399 if P_AUDIT_REPORT = 'Y' then
400 for per in c_vets
401 loop
402 l_buffer := per.person_id || g_delimiter ||
403 per.last_name || g_delimiter ||
404 nvl(per.first_name,' ') || g_delimiter ||
405 nvl(per.employee_number,' ') || g_delimiter ||
406 per.veteran || g_delimiter ||
407 per.veteran_category || g_delimiter ||
408 per.job_category || g_delimiter ||
409 per.assignment_id || g_delimiter ||
410 l_Est_Rep_Name ||
411 g_eol;
412 write_audit.put(l_buffer);
413 end loop;
414 end if;
415
416 for per in c_nonreported
417 loop
418 l_buffer := per.person_id || g_delimiter ||
419 per.last_name || g_delimiter ||
420 nvl(per.first_name,' ') || g_delimiter ||
421 nvl(per.employee_number,' ') || g_delimiter ||
422 per.veteran || g_delimiter ||
423 per.assignment_id || g_delimiter ||
424 l_Est_Rep_Name ||
425 g_eol;
426
427 write_to_concurrent_log(l_buffer);
428 end loop;
429
430 END IF;
431
432 l_xml_string := l_xml_string ||convert_into_xml('C_TOT_EMPS',l_count_emps,'D');
433
434 end C_tot_actFormula;
435
436 begin
437
438 l_business_group_name := hr_reports.get_business_group(p_business_group_id);
439 C_DATE_END := fnd_date.canonical_to_date(P_DATE_END);
440
441 select sysdate
442 into l_report_date
443 from dual;
444
445 open c_hier_details;
446 fetch c_hier_details into
447 l_hierarchy_name, l_hierarchy_version_num, l_parent_org_id, l_parent_node_id ;
448 close c_hier_details;
449
450 open c_count_est;
451 fetch c_count_est into l_est_no;
452 close c_count_est;
453
454 l_xml_string := '<?xml version="1.0"?>';
455 l_xml_string := l_xml_string||'<PERUSVEL_100A>';
456 l_xml_string := l_xml_string ||convert_into_xml('C_BUSINESS_GROUP_NAME',l_business_group_name,'D');
457 l_xml_string := l_xml_string ||convert_into_xml('C_HIERARCHY_NAME',l_hierarchy_name,'D');
458 l_xml_string := l_xml_string ||convert_into_xml('C_HIERARCHY_VERSION_NUM',l_hierarchy_version_num,'D');
459 l_xml_string := l_xml_string ||convert_into_xml('C_DATE_END',C_DATE_END,'D');
460 l_xml_string := l_xml_string ||convert_into_xml('CP_REPORT_DATE',l_report_date,'D');
461 l_xml_string := l_xml_string||'<LIST_G_PARENT_NODE_ID>';
462
463 write_to_concurrent_out(l_xml_string);
464 l_xml_string :='';
465
466 if P_AUDIT_REPORT = 'Y' then
467 write_audit.open('PERUSVEL_100A');
468
469 l_buffer := 'Person Id' || g_delimiter ||
470 'Last Name' || g_delimiter ||
471 'First Name' || g_delimiter ||
472 'Employee Number' || g_delimiter ||
473 'Veteran Status' || g_delimiter ||
474 'Veteran Category' || g_delimiter ||
475 'Job Category' || g_delimiter ||
476 'Assignment Id' || g_delimiter ||
477 'Reporting Name' ||
478 g_eol;
479
480 write_audit.put(l_buffer);
481 end if;
482
483 write_to_concurrent_log('Please find the Employee details who are not counted under Recently Seperated Veteran Category.Please Correct them');
484
485 l_buffer := 'Person Id' || g_delimiter ||
486 'Last Name' || g_delimiter ||
487 'First Name' || g_delimiter ||
488 'Employee Number' || g_delimiter ||
489 'Veteran Status' || g_delimiter ||
490 'Assignment Id' || g_delimiter ||
491 'Reporting Name' ||
492 g_eol;
493 write_to_concurrent_log(l_buffer);
494
495 open c_parent(l_parent_org_id,l_parent_node_id);
496 fetch c_parent into l_parent_node_id,l_reporting_name ;
497
498 l_xml_string := l_xml_string||'<G_PARENT_NODE_ID>';
499
500 l_xml_string := l_xml_string ||convert_into_xml('PARENT_NODE_ID',l_parent_node_id,'D');
501 l_xml_string := l_xml_string ||convert_into_xml('PAR_REPORT_NAME',l_reporting_name,'D');
502 l_xml_string := l_xml_string||'<LIST_G_ESTABLISHMENT>';
503
504 write_to_concurrent_out(l_xml_string);
505 l_xml_string :='';
506
507 open c_establishment(l_parent_node_id );
508 loop
509 fetch c_establishment into l_Est_Node_Id , l_parent_node_id1 , l_Est_Rep_Name,
510 l_Headquarters , l_est_add ;
511 exit when c_establishment%notfound;
512
513 l_xml_string := l_xml_string||'<G_ESTABLISHMENT>';
514
515 l_xml_string := l_xml_string ||convert_into_xml('EST_NODE_ID',l_Est_Node_Id,'D');
516 l_xml_string := l_xml_string ||convert_into_xml('PARENT_NODE_ID1',l_parent_node_id1,'D');
517 l_xml_string := l_xml_string ||convert_into_xml('EST_REP_NAME',l_Est_Rep_Name,'D');
518 l_xml_string := l_xml_string ||convert_into_xml('HEADQUARTERS',l_Headquarters,'D');
519 l_xml_string := l_xml_string ||convert_into_xml('ESTB_ADDRESS',l_est_add,'D');
520
521 C_tot_actFormula(l_Est_Node_Id,l_Est_Rep_Name);
522
523 l_xml_string := l_xml_string||'</G_ESTABLISHMENT>';
524
525 write_to_concurrent_out(l_xml_string);
526 l_xml_string :='';
527
528 end loop;
529 close c_establishment;
530
531 l_xml_string := l_xml_string||'</LIST_G_ESTABLISHMENT>';
532 l_xml_string := l_xml_string||'</G_PARENT_NODE_ID>';
533
534 close c_parent;
535
536 l_xml_string := l_xml_string||'</LIST_G_PARENT_NODE_ID>';
537 l_xml_string := l_xml_string||'</PERUSVEL_100A>';
538
539 write_to_concurrent_out(l_xml_string);
540 l_xml_string :='';
541
542 if P_AUDIT_REPORT = 'Y' then
543 write_audit.close;
544 end if;
545
546 End GET_VETS100A_DATA;
547
548 FUNCTION convert_into_xml( p_name IN VARCHAR2,
549 p_value IN VARCHAR2,
550 p_type IN char)
551 RETURN VARCHAR2 IS
552 l_convert_data VARCHAR2(300); /* increased the size from 200 to 300 , bug 13581031 */
553 BEGIN
554 IF p_type = 'D' THEN
555 l_convert_data := '<'||p_name||'>'||'<![CDATA['||p_value||']]>'||'</'||p_name||'>';
556 ELSE
557 l_convert_data := '<'||p_name||'>';
558 END IF;
559 RETURN(l_convert_data);
560 END convert_into_xml;
561
562 PROCEDURE write_to_concurrent_out (p_text VARCHAR2) IS
563 BEGIN
564
565 fnd_file.put_line(fnd_file.OUTPUT, p_text);
566
567 END write_to_concurrent_out;
568
569 PROCEDURE write_to_concurrent_log (p_text VARCHAR2) IS
570 BEGIN
571
572 fnd_file.put_line(fnd_file.LOG, p_text);
573
574 END write_to_concurrent_log;
575
576 End per_us_vets_100a_list_pkg ;
577