[Home] [Help]
PACKAGE BODY: APPS.OTA_FR_PLAN_DFORM
Source
1 PACKAGE BODY OTA_FR_PLAN_DFORM AS
2 /* $Header: otafrpdf.pkb 120.0.12010000.1 2008/10/14 06:31:50 parusia noship $ */
3 --
4 g_convert_to_utf8 boolean;
5 ---------------------------------------------------
6 -- Main procedure for building XML string
7 ------------------------------------------------------
8 PROCEDURE pdf_main_fill_table(p_business_group_id NUMBER,
9 p_company_id NUMBER DEFAULT NULL,
10 p_estab_id NUMBER DEFAULT NULL,
11 p_calendar VARCHAR2,
12 p_time_period_id NUMBER,
13 p_consolidate VARCHAR2,
14 p_training_plan_id NUMBER DEFAULT NULL,
15 p_list_events VARCHAR2,
16 p_template_name VARCHAR2, -- added to match parameters with CP
17 p_xml OUT NOCOPY CLOB)
18 IS
19 --
20 l_tp_select varchar2(150);
21 l_tp_from varchar2(150);
22 l_tp_where varchar2(300);
23 l_tp_order varchar2(50);
24 l_organization_where varchar2(200);
25 l_organization_from varchar2(100);
26 l_where_tplan varchar2(100);
27 l_sql varchar2(1500);
28 --
29 l_plan_id number;
30 l_plan_name ota_training_plans.name%TYPE;
31 l_org_name hr_organization_units.name%TYPE;
32 l_period_from varchar2(25);
33 l_period_to varchar2(25);
34 l_budget_level varchar2(20);
35 --
36 l_total_delg_member number;
37 l_act_number_evt number;
38 l_act_duration_evt number;
39 l_act_total_duration number;
40 --
41 l_evt_number number;
42 l_total_evt_duration_hours number;
43 l_evt_duration_hours number;
44 l_evt_duration_per_delg number;
45 l_evt_delegates number;
46 --
47 l_act_total_class number;
48 l_act_class_duration number;
49 --
50 -- Cursor to choose training plan members
51 Cursor csr_plan_members(c_training_plan_id number) IS
52 select course_tl.version_name member_name
53 ,course.activity_version_id member_id
54 ,course_tl.description member_description
55 ,'ACTIVITY_VERSION' member_level
56 from ota_activity_versions course,
57 ota_activity_versions_tl course_tl
58 where course.activity_version_id = course_tl.activity_version_id(+)
59 and course_tl.language(+) = userenv('LANG')
60 and exists
61 (select null
62 from ota_training_plan_members tpm
63 where tpm.training_plan_id = c_training_plan_id
64 and tpm.activity_version_id = course.activity_version_id
65 and tpm.member_status_type_id <> 'CANCELLED')
66 union
67 select category_tl.name member_name
68 ,category.activity_id member_id
69 ,category_tl.description member_description
70 ,'ACTIVITY_DEFINITION' member_level
71 from ota_activity_definitions category,
72 ota_activity_definitions_tl category_tl
73 where category.activity_id = category_tl.activity_id(+)
74 and category_tl.language(+) = userenv('LANG')
75 and exists
76 (select null
77 from ota_training_plan_members tpm
78 where tpm.training_plan_id = c_training_plan_id
79 and tpm.activity_definition_id = category.activity_id
80 and tpm.member_status_type_id <> 'CANCELLED');
81 --
82 -- Cursor for delegates per employee category
83 Cursor csr_delegates_per_catg(c_member_id number,
84 c_member_level varchar2,
85 c_training_plan_id number,
86 c_business_group_id number)IS
87 select sum(pbv.value) delg_number
88 ,ec.lookup_code emp_code
89 ,ec.meaning emp_catg
90 from per_budgets pb,
91 per_budget_versions pbr,
92 per_budget_values pbv,
93 per_budget_elements pbe,
94 ota_training_plan_members tpm,
95 hr_lookups ec
96 where pb.unit = 'FR_DELEGATES_PER_CATEGORY'
97 and pb.budget_type_code = 'OTA_BUDGET'
98 and pb.business_group_id = tpm.business_group_id
99 and pb.budget_id = pbr.budget_id
100 and pbr.budget_version_id = pbe.budget_version_id
101 and pbv.budget_element_id = pbe.budget_element_id
102 and pbv.business_group_id = tpm.business_group_id
103 and tpm.business_group_id = c_business_group_id
104 and pbe.training_plan_member_id = tpm.training_plan_member_id
105 and pbe.event_id is null
106 and pbe.business_group_id = tpm.business_group_id
107 and tpm.training_plan_id = c_training_plan_id
108 and decode(c_member_level,
109 'ACTIVITY_VERSION', tpm.activity_version_id ,
110 'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id
111 and ec.lookup_type = 'FR_EMPLOYEE_CATEGORY'
112 and ec.lookup_code = pbv.budget_information1
113 group by ec.lookup_code,ec.meaning;
114 --
115 -- Cursor for list of events
116 Cursor csr_event_list (c_member_id number,
117 c_member_level varchar2)IS
118 select distinct class.event_id class_id
119 ,class_tl.title class_title
120 ,fnd_date.date_to_canonical(class.course_start_date) class_from
121 ,fnd_date.date_to_canonical(class.course_end_date) class_to
122 from ota_events class,
123 ota_events_tl class_tl,
124 ota_activity_versions course
125 where class.event_id is not null
126 and class_tl.event_id = class.event_id
127 and class_tl.language(+) = userenv('LANG')
128 and class.activity_version_id = course.activity_version_id
129 and decode(c_member_level,
130 'ACTIVITY_VERSION',course.activity_version_id ,
131 'ACTIVITY_DEFINITION',course.activity_id) = c_member_id;
132 --
133 -- Cursor for delegates per event per employee category
134 Cursor csr_evt_delegates_per_catg(c_event_id number,
135 c_member_id number,
136 c_member_level varchar2,
137 c_training_plan_id number,
138 c_business_group_id number) IS
139 select count(pbv.budget_information1) evt_delg_number
140 ,ec.lookup_code evt_emp_code
141 ,ec.meaning evt_emp_catg
142 from per_budgets pb,
143 per_budget_versions pbr,
144 per_budget_values pbv,
145 per_budget_elements pbe,
146 ota_training_plan_members tpm,
147 hr_lookups ec
148 where pb.unit = 'FR_DELEGATES_PER_CATEGORY'
149 and pb.budget_type_code = 'OTA_BUDGET'
150 and pb.business_group_id = tpm.business_group_id
151 and pb.budget_id = pbr.budget_id
152 and pbr.budget_version_id = pbe.budget_version_id
153 and pbv.budget_element_id = pbe.budget_element_id
154 and pbv.business_group_id = tpm.business_group_id
155 and tpm.business_group_id = c_business_group_id
156 and pbe.training_plan_member_id = tpm.training_plan_member_id
157 and pbe.event_id = c_event_id
158 and pbe.business_group_id = tpm.business_group_id
159 and tpm.training_plan_id = c_training_plan_id
160 and decode(c_member_level,
161 'ACTIVITY_VERSION', tpm.activity_version_id ,
162 'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id
163 and ec.lookup_type = 'FR_EMPLOYEE_CATEGORY'
164 and ec.lookup_code = pbv.budget_information1
165 group by ec.lookup_code,ec.meaning;
166 --
167 TYPE ref_cursor_type IS REF CURSOR;
168 ref_csr_training_plan ref_cursor_type;
169 --
170 BEGIN
171 -- Starting to build the XML string
172 dbms_lob.createtemporary(p_xml, TRUE, dbms_lob.session);
173 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
174 load_xml_declaration(p_xml);
175 load_xml_label(p_xml,'FIELDS',TRUE);
176 --
177 -- Building up the main cursor to fetch training plans and corresponding training plan values
178 l_organization_from := '';
179 --
180 l_tp_select :='Select tp.training_plan_id, tp.name,org_unit.name, fnd_date.date_to_canonical(period.start_date),fnd_date.date_to_canonical(period.end_date)';
181 l_tp_from := ' from ota_training_plans tp ,per_time_periods period, hr_organization_units org_unit';
182 l_tp_where := ' where tp.business_group_id = :p_business_group_id'
183 ||' and tp.time_period_id = period.time_period_id'
184 ||' and period.time_period_id = :p_time_period_id'
185 ||' and period.period_set_name = :p_calendar'
186 ||' and org_unit.organization_id = tp.organization_id';
187 l_tp_order := ' order by tp.name';
188 --
189 IF p_company_id is null THEN
190 IF p_estab_id is null THEN
191 l_organization_where := ' and :p_company_id is null and :p_estab_id is null';
192 ELSE
193 l_organization_where := ' and :p_company_id is null and tp.organization_id = :p_estab_id';
194 END IF;
195 ELSE
196 -- when company is not null
197 IF p_estab_id is null THEN
198 -- check the p_consolidate flag
199 IF p_consolidate = 'N' THEN
200 l_organization_where := ' and tp.organization_id = :p_company_id and :p_estab_id is null';
201 ELSE
202 -- when p_consolidate is 'Y'
203 l_organization_from := ', hr_organization_information org_info';
204 l_organization_where := ' and tp.organization_id = org_info.organization_id'
205 ||' and org_info.org_information1 = fnd_number.number_to_canonical(:p_company_id)'
206 ||' and :p_estab_id is null';
207 --
208 END IF;
209 ELSE
210 -- if estab is not null
211 l_organization_from := ', hr_organization_information org_info';
212 l_organization_where := ' and tp.organization_id = org_info.organization_id'
213 ||' and org_info.org_information1 = fnd_number.number_to_canonical(:p_company_id)'
214 ||' and org_info.organization_id = :p_estab_id';
215 --
216 END IF;
217 END IF;
218 --
219 IF p_training_plan_id is not null THEN
220 l_where_tplan := ' and tp.training_plan_id = :p_training_plan_id';
221 ELSE
222 l_where_tplan := ' and :p_training_plan_id is null';
223 END IF;
224 --
225 l_sql := l_tp_select||l_tp_from||l_organization_from||l_tp_where||l_organization_where||l_where_tplan||l_tp_order;
226 --
227 OPEN ref_csr_training_plan FOR l_sql
228 USING p_business_group_id, p_time_period_id,p_calendar, p_company_id, p_estab_id, p_training_plan_id;
229 LOOP
230 FETCH ref_csr_training_plan INTO
231 l_plan_id, l_plan_name, l_org_name, l_period_from, l_period_to;
232 EXIT WHEN ref_csr_training_plan%NOTFOUND;
233 -- Assign value to training plan label
234 load_xml_label(p_xml, 'TRAIN_PLAN', TRUE);
235 -- Find the budget level
236 l_budget_level:= get_budget_level(p_business_group_id);
237 --
238 -- Assign value to field tags
239 load_xml(p_xml,'plan_name', l_plan_name);
240 load_xml(p_xml, 'org_name', l_org_name);
241 load_xml(p_xml, 'plan_period_from', l_period_from);
242 load_xml(p_xml, 'plan_period_to', l_period_to);
243 load_xml(p_xml, 'plan_budget_level', l_budget_level);
244 -- Call cursor for plan members
245 FOR member_list IN csr_plan_members(l_plan_id) LOOP
246 -- Assign value to plan member label
247 load_xml_label(p_xml, 'PLAN_MEMBER', TRUE);
248 --
249 l_total_delg_member := 0;
250 -- call csr_delegates_per_catg with type and member id
251 FOR count_delg IN csr_delegates_per_catg(member_list.member_id,
252 member_list.member_level,
253 l_plan_id,
254 p_business_group_id)
255 LOOP
256 -- Calculate total delegates
257 l_total_delg_member := l_total_delg_member + count_delg.delg_number;
258 -- Assign value to delegates for activity label
259 load_xml_label(p_xml, 'ACT_EMP_CATG', TRUE);
260 -- Assign value to field tags
261 load_xml(p_xml, 'delg_number', count_delg.delg_number);
262 load_xml(p_xml, 'emp_catg', count_delg.emp_catg);
263 --
264 load_xml_label(p_xml, 'ACT_EMP_CATG', FALSE);
265 END LOOP;
266 --
267 l_act_number_evt := get_member_budget_values('FR_NUMBER_EVENTS'
268 ,member_list.member_level
269 ,member_list.member_id
270 ,l_plan_id
271 ,p_business_group_id);
272 l_act_duration_evt := get_member_budget_values('FR_DURATION_HOURS'
273 ,member_list.member_level
274 ,member_list.member_id
275 ,l_plan_id
276 ,p_business_group_id);
277 l_act_total_duration := l_total_delg_member * l_act_number_evt * l_act_duration_evt;
278 --
279 l_evt_number:= 0;
280 l_total_evt_duration_hours := 0;
281 -- Check if classes are to be listed
282 IF p_list_events = 'Y' THEN
283 -- Assign value to the entire class label
284 load_xml_label(p_xml, 'CLASSES', TRUE);
285 -- call csr_event_list with type and member id
286 FOR class_details IN csr_event_list(member_list.member_id, member_list.member_level)
287 LOOP
288 -- Assign value to class list label
289 load_xml_label(p_xml, 'EVENT_LIST', TRUE);
290 -- Calculate the count by adding each event
291 l_evt_number := l_evt_number +1;
292 -- call procedure for estimating event level values
293 l_evt_duration_per_delg := get_event_duration(class_details.class_id,
294 l_plan_id,
295 member_list.member_level,
296 member_list.member_id,
297 p_business_group_id);
298 l_evt_delegates := 0;
299 -- call csr_evt_det_per_catg
300 FOR count_evt_delg IN csr_evt_delegates_per_catg(class_details.class_id,
301 member_list.member_id,
302 member_list.member_level,
303 l_plan_id,
304 p_business_group_id)
305 LOOP
306 -- calculate the number of delegates for this event
307 l_evt_delegates := l_evt_delegates +1;
308 -- Assign value to delegates for event label
309 load_xml_label(p_xml, 'EVT_EMP_CATG', TRUE);
310 -- Assign value to field tags
311 load_xml(p_xml, 'evt_delg_number', count_evt_delg.evt_delg_number);
312 load_xml(p_xml, 'evt_emp_catg', count_evt_delg.evt_emp_catg);
313 --
314 load_xml_label(p_xml, 'EVT_EMP_CATG', FALSE);
315 END LOOP;
316 l_evt_duration_hours := l_evt_duration_per_delg * l_evt_delegates;
317 -- Add up the duration
318 l_total_evt_duration_hours := l_total_evt_duration_hours + l_evt_duration_hours;
319 --
320 -- Assign value to field tags
321 load_xml(p_xml, 'class_title', class_details.class_title);
322 load_xml(p_xml, 'course_start_date', class_details.class_from);
323 load_xml(p_xml, 'course_end_date', class_details.class_to);
324 load_xml(p_xml, 'class_duration', l_evt_duration_hours);
325 --
326 load_xml_label(p_xml, 'EVENT_LIST', FALSE);
327 --
328 END LOOP; -- event loop
329 load_xml_label(p_xml, 'CLASSES', FALSE);
330 END IF; -- check for listing classes
331 -- Determine values
332 IF l_budget_level = 'EVENT' THEN
333 l_act_total_class := l_evt_number;
334 l_act_class_duration := l_total_evt_duration_hours;
335 ELSIF l_budget_level = 'ACTIVITY' THEN
336 l_act_total_class := l_act_number_evt;
337 l_act_class_duration :=l_act_total_duration;
338 END IF;
339 -- Assign value to field tags
340 load_xml(p_xml, 'member_name', member_list.member_name);
341 load_xml(p_xml, 'member_description', member_list.member_description);
342 load_xml(p_xml, 'member_total_class', l_act_total_class);
343 load_xml(p_xml, 'member_class_duration',l_act_class_duration);
344 --
345 load_xml_label(p_xml, 'PLAN_MEMBER', FALSE);
346 END LOOP; -- member loop
347 load_xml_label(p_xml, 'TRAIN_PLAN', FALSE);
348 END LOOP; -- training plan loop
349 load_xml_label(p_xml,'FIELDS',FALSE);
350 --
351 END pdf_main_fill_table;
352 --------------------------------------------------------------------------
353 -- Function for retrieving budget level for a training plan
354 --------------------------------------------------------------------------
355 FUNCTION get_budget_level(p_business_group_id number) return varchar2 IS
356 --
357 l_bl_training_plan varchar2(20);
358 l_bl_number_events varchar2(20);
359 l_bl_duration_hours varchar2(20);
360 l_bl_delegate_per_category varchar2(20);
361 --
362 function get_level(p_business_group_id number,
363 p_measurement_code varchar2) return varchar2 is
364 --
365 l_budget_level varchar2(30);
366 --
367 cursor csr_budget_level(c_business_group_id number,
368 c_measurement_code varchar2) is
369 select budget_level
370 from ota_tp_measurement_types
371 where business_group_id = p_business_group_id
372 and tp_measurement_code = p_measurement_code;
373 --
374 begin
375 open csr_budget_level(p_business_group_id,p_measurement_code);
376 fetch csr_budget_level into l_budget_level;
377 if csr_budget_level%notfound then
378 l_budget_level := null;
379 fnd_message.set_name('OTA','OTA_13878_PDF_BUD_MISSING');
380 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
381 end if;
382 close csr_budget_level;
383 --
384 return l_budget_level;
385 end;
386 --
387 BEGIN
388 l_bl_number_events := get_level(p_business_group_id, 'FR_NUMBER_EVENTS');
389 l_bl_duration_hours := get_level(p_business_group_id, 'FR_DURATION_HOURS');
390 l_bl_delegate_per_category := get_level(p_business_group_id, 'FR_DELEGATES_PER_CATEGORY');
391 --
392 /* If budgeting at the event level the measurement type 'FR_NUMBER_EVENTS'
393 will not be defined. Therefore set :c_bl_number_events to 'EVENT'.
394 This will ensure that the before-report trigger error checking does not
395 fail because either 1) they all need to be defined at 'ACTIVITY' level
396 or 2) Budgeting at Event level so set c_bl_number_event to 'EVENT'
397 As they all need to match but at event level this measure is undefined */
398 --
399 if l_bl_duration_hours = 'EVENT' and
400 l_bl_delegate_per_category = 'EVENT' then
401 l_bl_number_events := 'EVENT';
402 end if;
403 --
404 l_bl_training_plan := l_bl_number_events;
405 --
406 RETURN l_bl_training_plan;
407 END get_budget_level;
408 --------------------------------------------------------------------------
409 --Function for calculating member budget values:
410 ---------------------------------------------------------------------------
411 FUNCTION get_member_budget_values(p_measure_code varchar2
412 ,p_member_level varchar2
413 ,p_member_id number
414 ,p_training_plan_id number
415 ,p_business_group_id number) return number
416 IS
417 --
418 l_budget_value number;
419 -- Cursor to fetch values according to measurement type
420 Cursor csr_cal_budget_values(c_measure_code varchar2,
421 c_member_id number,
422 c_member_level varchar2,
423 c_training_plan_id number,
424 c_business_group_id number) is
425 select sum(pbv.value) value
426 from per_budgets pb,
427 per_budget_versions pbr,
428 per_budget_values pbv,
429 per_budget_elements pbe,
430 ota_training_plan_members tpm
431 where pb.unit = c_measure_code
432 and pb.budget_type_code = 'OTA_BUDGET'
433 and pb.business_group_id = tpm.business_group_id
434 and pb.budget_id = pbr.budget_id
435 and pbr.budget_version_id = pbe.budget_version_id
436 and pbv.budget_element_id = pbe.budget_element_id
437 and pbv.business_group_id = tpm.business_group_id
438 and tpm.business_group_id = c_business_group_id
439 and pbe.training_plan_member_id = tpm.training_plan_member_id
440 and pbe.event_id is null
441 and pbe.business_group_id = tpm.business_group_id
442 and tpm.training_plan_id = c_training_plan_id
443 and decode(c_member_level,
444 'ACTIVITY_VERSION', tpm.activity_version_id ,
445 'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id;
446 --
447 BEGIN
448 --
449 OPEN csr_cal_budget_values(p_measure_code,
450 p_member_id,
451 p_member_level,
452 p_training_plan_id,
453 p_business_group_id);
454 FETCH csr_cal_budget_values INTO l_budget_value;
455 CLOSE csr_cal_budget_values;
456 --
457 IF l_budget_value is null THEN
458 l_budget_value :=0;
459 END IF;
460 --
461 RETURN l_budget_value;
462 --
463 END get_member_budget_values;
464 ------------------------------------------
465 --Function for calculating event duration
466 ------------------------------------------
467 FUNCTION get_event_duration(p_training_plan_id number,
468 p_event_id number,
469 p_member_level varchar2,
470 p_member_id number,
471 p_business_group_id number) return number is
472 --
473 l_total number;
474 --
475 Cursor csr_sum_evt_duration(c_training_plan_id number,
476 c_event_id number,
477 c_member_level varchar,
478 c_member_id number,
479 c_business_group_id number) is
480 select sum(pbv.value)
481 from per_budgets pb,
482 per_budget_versions pbr,
483 per_budget_values pbv,
484 per_budget_elements pbe,
485 ota_training_plan_members tpm
486 where pb.unit = 'FR_DURATION_HOURS'
487 and pb.budget_type_code = 'OTA_BUDGET'
488 and pb.business_group_id = tpm.business_group_id
489 and pb.budget_id = pbr.budget_id
490 and pbr.budget_version_id = pbe.budget_version_id
491 and pbv.budget_element_id = pbe.budget_element_id
492 and pbv.business_group_id = tpm.business_group_id
493 and tpm.business_group_id = c_business_group_id
494 and pbe.event_id = c_event_id
495 and pbe.training_plan_member_id = tpm.training_plan_member_id
496 and pbe.business_group_id = tpm.business_group_id
497 and tpm.training_plan_id = c_training_plan_id
498 and decode(c_member_level,
499 'ACTIVITY_VERSION', tpm.activity_version_id ,
500 'ACTIVITY_DEFINITION', tpm.activity_definition_id) = c_member_id;
501 --
502 BEGIN
503 --
504 OPEN csr_sum_evt_duration(p_training_plan_id,
505 p_event_id,
506 p_member_level,
507 p_member_id,
508 p_business_group_id);
509 FETCH csr_sum_evt_duration INTO l_total;
510 CLOSE csr_sum_evt_duration;
511 --
512 IF l_total is null THEN
513 l_total := 0;
514 END IF;
515 return l_total;
516 --
517 EXCEPTION
518 when others then
519 return 0;
520 END get_event_duration;
521 --
522 ------------------------------------------------------
523 -- Procedure for writing format of XML string
524 ---------------------------------------------------------
525 procedure load_xml_declaration(p_xml in out nocopy clob)
526 is
527 --
528 cursor csr_get_lookup(p_lookup_type varchar2
529 ,p_lookup_code varchar2
530 ,p_view_app_id number default 3) is
531 select meaning,tag
532 FROM fnd_lookup_values flv
533 WHERE lookup_type = p_lookup_type
534 AND lookup_code = p_lookup_code
535 AND language = userenv('LANG')
536 AND view_application_id = p_view_app_id
537 and SECURITY_GROUP_ID = decode(substr(userenv('CLIENT_INFO'),55,1),
538 ' ', 0,
539 NULL, 0,
540 '0', 0,
541 fnd_global.lookup_security_group(
542 FLV.LOOKUP_TYPE,FLV.VIEW_APPLICATION_ID));
543 rec_lookup csr_get_lookup%ROWTYPE;
544 --
545 begin
546 --
547 open csr_get_lookup('FND_ISO_CHARACTER_SET_MAP',
548 substr(USERENV('LANGUAGE'),instr(USERENV('LANGUAGE'),'.')+1),
549 0);
550 fetch csr_get_lookup into rec_lookup;
551 close csr_get_lookup;
552 --
553 if rec_lookup.tag is null then
554 g_convert_to_utf8 := TRUE;
555 else
556 g_convert_to_utf8 := FALSE;
557 end if;
558 write_to_clob(p_xml,'<?xml version="1.0" encoding="'||
559 nvl(rec_lookup.tag,'UTF-8')||'" ?>');
560 --
561 end load_xml_declaration;
562 --
563 -------------------------------------------------------------
564 -- Procedure for appending labels of the XML string
565 -------------------------------------------------------------
566 procedure load_xml_label(p_xml in out nocopy clob,
567 p_node varchar2,
568 p_open_not_close boolean) is
569 begin
570 if p_open_not_close then
571 write_to_clob (p_xml,'<'||p_node||'>
572 ');
573 else
574 write_to_clob (p_xml,'</'||p_node||'>
575 ');
576 end if;
577 end load_xml_label;
578 --
579 ----------------------------------------------------------------
580 -- Procedure for writing tag names and values
581 ---------------------------------------------------------------
582 procedure load_xml (p_xml in out nocopy clob,
583 p_node varchar2,
584 p_data varchar2,
585 p_attribs varchar2 default null)
586 is
587 l_data varchar2(2000);
588 begin
589 /* Handle special characters in data */
590 l_data := REPLACE (p_data, '&', '&');
591 l_data := REPLACE (l_data, '>', '>');
592 l_data := REPLACE (l_data, '<', '<');
593 l_data := REPLACE (l_data, '''', ''');
594 l_data := REPLACE (l_data, '"', '"');
595 write_to_clob(p_xml,'<'||p_node||ltrim(' '||p_attribs)||'>'||
596 l_data||'</'||p_node||'>');
597 end load_xml;
598 -----------------------------------------------------------
599 -- Procedure for writing the clob
600 -----------------------------------------------------------
601 procedure write_to_clob (p_xml in out nocopy clob,
602 p_data varchar2) is
603 begin
604
605 if g_convert_to_utf8 then
606 dbms_lob.writeappend(p_xml,
607 length(convert(p_data,'UTF8')),
608 convert(p_data,'UTF8'));
609 else
610 dbms_lob.writeappend(p_xml, length(p_data), p_data);
611 end if;
612 end write_to_clob;
613 --------------------------------------------------------------------
614 END OTA_FR_PLAN_DFORM;