[Home] [Help]
PACKAGE BODY: APPS.PER_DATA_UPDATE_REPORT
Source
1 PACKAGE BODY PER_DATA_UPDATE_REPORT AS
2 /* $Header: perdtupr.pkb 120.15 2006/09/08 17:33:44 jabubaka noship $ */
3 summCtr NUMBER;
4 critCtr NUMBER;
5 vCtr NUMBER;
6
7 FUNCTION get_parameter_value(f_parameter_name IN varchar2,
8 f_upgrade_definition_id IN number) return varchar2
9 is
10 cursor csr_get_parameter_value(f_parameter_name varchar2,
11 f_upgrade_definition_id number) is
12 select parameter_value
13 from pay_upgrade_parameters
14 where parameter_name = f_parameter_name
15 and upgrade_definition_id = f_upgrade_definition_id;
16
17 l_parameter_value pay_upgrade_parameters.parameter_value%type;
18
19 begin
20 open csr_get_parameter_value(f_parameter_name, f_upgrade_definition_id);
21 fetch csr_get_parameter_value into l_parameter_value;
22 if (csr_get_parameter_value%NOTFOUND) then
23 l_parameter_value := null;
24 end if;
25 close csr_get_parameter_value;
26 return l_parameter_value;
27 end get_parameter_value;
28
29 PROCEDURE DATA_REPORT_INITIATE (p_request_id number,
30 p_report_content varchar2,
31 p_importance varchar2,
32 p_product varchar2,
33 p_template_name varchar2,
34 p_xml OUT NOCOPY BLOB) AS
35 begin
36 POPULATE_REPORT_DATA(p_request_id,
37 p_report_content,
38 p_importance,
39 p_product,
40 p_xml);
41 end DATA_REPORT_INITIATE;
42
43 PROCEDURE POPULATE_REPORT_DATA(p_request_id number,
44 p_report_content varchar2,
45 p_importance varchar2,
46 p_product varchar2,
47 l_xfdf_blob OUT NOCOPY BLOB) AS
48 -- p_output_fname out nocopy varchar2) IS
49 cursor c_processes is
50 select name process_name
51 from pay_upgrade_definitions_tl
52 where language = userenv('LANG');
53
54
55 cursor c_get_process_data is
56 select
57 pud.upgrade_definition_id upgrade_definition_id,
58 pudt.NAME process_name,
59 pud.LEGISLATION_CODE legislation_code,
60 pud.DESCRIPTION description,
61 decode(pud.UPGRADE_LEVEL,'B','Business Group',
62 'L', 'Legislation',
63 'G', 'Global') upgrade_level,
64 pud.CRITICALITY criticality_code,
65 decode(pud.CRITICALITY, 'C', 'Critical',
66 'R', 'Recommended',
67 'O', 'Optional') criticality,
68 decode(pud.criticality, 'C', 1, 'R', 2, 'O', 3) criticality_sort,
69 pud.THREADING_LEVEL threading_level,
70 pud.FAILURE_POINT failure_point,
71 pud.LEGISLATIVELY_ENABLED legislatively_enabled,
72 pud.UPGRADE_METHOD upgrade_method,
73 pud.UPGRADE_PROCEDURE upgrade_procedure,
74 pud.QUALIFYING_PROCEDURE qualifying_procedure,
75 app.DESCRIPTION application_name,
76 pud.VALIDATE_CODE validate_code,
77 pud.FIRST_PATCHSET introduced,
78 pud.ADDITIONAL_INFO additional_info
79 from pay_upgrade_definitions_tl pudt,
80 pay_upgrade_definitions pud,
81 fnd_application_vl app
82 where pud.owner_application_id = app.application_id (+)
83 and pudt.upgrade_definition_id = pud.upgrade_definition_id
84 and ((pud.first_patchset like '%' || p_product || '%')
85 or (p_product is null))
86 and instr(p_importance, pud.criticality) > 0
87 and pudt.language = userenv('LANG')
88 order by criticality_sort, pudt.name;
89
90 cursor c_get_legislation_code(lg_upgrade_definition_id number) is
91 select pul.legislation_code
92 from pay_upgrade_legislations pul
93 where pul.upgrade_definition_id = lg_upgrade_definition_id;
94
95 cursor c_execution_status(lp_upgrade_definition_id number) is
96 select pus.legislation_code status_leg_code,
97 bus.name status_bg_name,
98 decode(pus.status, 'C', 'Complete',
99 'P', 'Processing') status,
100 pus.executed executed,
101 pus.request_id request_id
102 from pay_upgrade_status pus,
103 per_business_groups bus
104 where pus.upgrade_definition_id = lp_upgrade_definition_id
105 and pus.business_group_id = bus.business_group_id (+);
106
107 cursor c_detect_status (p_upgrade_definition_id number) is
108 select upgrade_definition_id
109 from pay_upgrade_status
110 where upgrade_definition_id = p_upgrade_definition_id;
111
112 cursor c_profile_option_name (p_config_option_name varchar2) is
113 select user_profile_option_name
114 from fnd_profile_options_vl
115 where profile_option_name = p_config_option_name;
116
117 l_xfdf_string CLOB;
118 l_c_data_start varchar2(10);
119 l_c_data_end varchar2(5);
120 l_content_both varchar2(30);
121 l_content_summary varchar2(30);
122 l_content_detail varchar2(30);
123 l_critCodeCShown boolean;
124 l_critCodeRShown boolean;
125 l_critCodeOShown boolean;
126 l_database_name varchar2(10);
127 l_process_name pay_upgrade_definitions_tl.name%type;
128 l_legislation_code pay_upgrade_definitions.legislation_code%type;
129 l_enabled_leg_code varchar2(150);
130 l_required varchar2(10);
131 l_prepatch varchar2(10);
132 l_inpatch varchar2(10);
133 l_postpatch varchar2(10);
134 l_when_to_run varchar2(10);
135 l_exec_config_option varchar2(30);
136 l_configurable varchar2(10);
137 l_config_option_name varchar2(80);
138 l_option_name varchar2(80);
139 l_execution_point varchar2(80);
140 l_exec_lookup_type varchar2(80);
141 l_exec_status varchar2(10);
142 l_status_leg_code pay_upgrade_status.legislation_code%type;
143 l_status_bg_name per_business_groups.name%type;
144 l_status pay_upgrade_status.status%type;
145 l_executed varchar2(10);
146 l_leg_code_rows number;
147 l_lookup_execution_point varchar2(10);
148 l_lookup_no varchar2(10);
149 l_lookup_yes varchar2(10);
150 l_request_id pay_upgrade_status.request_id%type;
151 l_rows_found boolean;
152 l_sql varchar2(400);
153 l_str_start varchar2(100);
154 l_str_dbname varchar2(100);
155 l_str_execution_date varchar2(100);
156 l_str_process_name varchar2(100);
157 l_str_legislation_code varchar2(100);
158 l_str_enabled_leg_code varchar2(100);
159 l_str_upgrade_level varchar2(100);
160 l_str_required varchar2(100);
161 l_str_exec_status varchar2(100);
162 l_str_introduced varchar2(100);
163 l_str_criticality varchar2(100);
164 l_str_criticality_code varchar2(100);
165 l_str_prepatch varchar2(100);
166 l_str_inpatch varchar2(100);
167 l_str_postpatch varchar2(100);
168 l_str_configurable varchar2(100);
169 l_str_config_option_name varchar2(100);
170 l_str_execution_point varchar2(100);
171 l_str_additional_info varchar2(100);
172 l_str_g_status varchar2(100);
173 l_str_g_process varchar2(100);
174 l_str_status_leg_code varchar2(100);
175 l_str_status_bg_name varchar2(100);
176 l_str_status varchar2(100);
177 l_str_executed varchar2(100);
178 l_str_request_id varchar2(100);
179 l_str_g_summary varchar2(100);
180 l_str_open varchar2(10);
181 l_str_close varchar2(10);
182 l_str_open_end varchar2(10);
183 l_status_rows number;
184 l_str_list_g_process varchar2(30);
185 l_str_list_g_summary varchar2(30);
186 l_str_list_g_criticality varchar2(30);
187 l_str_g_criticality varchar2(30);
188 l_strsumm1 varchar2(3000);
189 l_strsumm2 varchar2(3000);
190 begin
191 l_c_data_start := '<![CDATA[';
192 l_c_data_end := ']]>';
193 l_content_both := 'BOTH';
194 l_content_summary := 'SUMMARY';
195 l_content_detail := 'DETAIL';
196 l_critCodeCShown := false;
197 l_critCodeRShown := false;
198 l_critCodeOShown := false;
199 l_executed := l_lookup_no;
200 l_exec_status := l_lookup_no;
201 l_leg_code_rows := 0;
202 l_lookup_yes := hr_general.decode_lookup('YES_NO','Y');
203 l_lookup_no := hr_general.decode_lookup('YES_NO','N');
204 l_rows_found := false;
205 l_str_dbname := 'DBNAME';
206 l_str_execution_date := 'EXECUTION_DATE';
207 l_str_process_name := 'PROCESS_NAME';
208 l_str_legislation_code := 'LEGISLATION_CODE';
209 l_str_enabled_leg_code := 'ENABLED_LEG_CODE';
210 l_str_upgrade_level := 'UPGRADE_LEVEL';
211 l_str_required := 'REQUIRED';
212 l_str_exec_status := 'EXEC_STATUS';
213 l_str_introduced := 'INTRODUCED';
214 l_str_criticality := 'CRITICALITY';
215 l_str_criticality_code := 'CRITICALITY_CODE';
216 l_str_prepatch := 'PREPATCH';
217 l_str_inpatch := 'INPATCH';
218 l_str_postpatch := 'POSTPATCH';
219 l_str_configurable := 'CONFIGURABLE';
220 l_str_config_option_name := 'CONFIG_OPTION_NAME';
221 l_str_execution_point := 'EXECUTION_POINT';
222 l_str_additional_info := 'ADDITIONAL_INFO';
223 l_str_status_leg_code := 'STATUS_LEG_CODE';
224 l_str_status_bg_name := 'STATUS_BG_NAME';
225 l_str_status := 'STATUS';
226 l_str_executed := 'EXECUTED';
227 l_str_request_id := 'REQUEST_ID';
228 l_str_start := 'START';
229 l_str_g_summary := 'G_SUMMARY';
230 l_str_list_g_summary := 'LIST_G_SUMMARY';
231 l_str_list_g_criticality := 'LIST_G_CRITICALITY';
232 l_str_list_g_process := 'LIST_G_PROCESS';
233 l_str_g_process := 'G_PROCESS';
234 l_str_g_status := 'G_STATUS';
235 l_str_g_criticality := 'G_CRITICALITY';
236 --
237 dbms_lob.createtemporary(l_xfdf_string, FALSE, DBMS_LOB.CALL);
238 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
239 dbms_lob.createtemporary(l_xfdf_blob,TRUE);
240 hr_xml_pub_utility.clob_to_blob(l_xfdf_string,l_xfdf_blob);
241 --
242 --
243 dt_fndate.set_effective_date(trunc(sysdate));
244 --
245 -- Initialise counters for the XMLTables
246 --
247 PER_DATA_UPDATE_REPORT.summXMLTable.DELETE;
248 summCtr:=0;
249 PER_DATA_UPDATE_REPORT.critXMLTable.DELETE;
250 critCtr:=0;
251 PER_DATA_UPDATE_REPORT.vXMLTable.DELETE;
252 vCtr:=0;
253 --
254 -- <LIST_G_SUMMARY>
255 --
256 if p_report_content in (l_content_summary, l_content_both) then
257 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_list_g_summary || '>' ;
258 summCtr:=summCtr+1;
259 end if;
260 --
261 -- <LIST_G_CRITICALITY>
262 --
263 if p_report_content in (l_content_detail, l_content_both) then
264 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_list_g_criticality || '>';
265 critCtr:=critCtr+1;
266 end if;
267 --
268 -- retrieve the processes
269 --
270 for l_cursor_get_data in c_get_process_data
271 --
272 loop
273 --
274 l_rows_found := true;
275 --
276 -- REQUIRED
277 -- dynamic sql to retrieve result of the code.
278 --
279 declare
280 skip_sub_block exception;
281 begin
282 begin
283 --
284 if l_cursor_get_data.validate_code is not null then
285 l_sql := 'begin ' || l_cursor_get_data.validate_code || '(:x); end;';
286 --
287 execute immediate l_sql
288 using out l_required;
289 if l_required = 'TRUE' then
290 l_required := l_lookup_yes;
291 elsif l_required = 'FALSE' then
292 l_required := l_lookup_no;
293 end if;
294 else
295 l_required := 'Undefined';
296 end if;
297 --
298 exception
299 when others then
300 l_required := 'Error';
301 raise skip_sub_block;
302 end;
303
304 exception
305 when skip_sub_block then null;
306 end;
307 --
308 if p_report_content in (l_content_summary, l_content_both) then
309 -- <G_SUMMARY>
310 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_g_summary || '>';
311 summCtr:=summCtr+1;
312 -- <PROCESS_NAME>
313 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_process_name || '>' || l_c_data_start || l_cursor_get_data.process_name || l_c_data_end || '</' || l_str_process_name || '>';
314 summCtr:=summCtr+1;
315 -- <UPGRADE_LEVEL>
316 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_upgrade_level || '>' || l_cursor_get_data.upgrade_level || '</' || l_str_upgrade_level || '>';
317 summCtr:=summCtr+1;
318 -- <REQUIRED>
319 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_required || '>' || l_required || '</' || l_str_required || '>';
320 summCtr:=summCtr+1;
321 -- <EXEC_STATUS>
322 open c_detect_status(l_cursor_get_data.upgrade_definition_id);
323 fetch c_detect_status into l_status_rows;
324 if c_detect_status%FOUND then
325 l_exec_status := l_lookup_yes;
326 else
327 l_exec_status := l_lookup_no;
328 end if;
329 close c_detect_status;
330 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_exec_status || '>' || l_exec_status || '</' || l_str_exec_status || '>';
331 summCtr:=summCtr+1;
332 -- <INTRODUCED>
333 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_introduced || '>' || l_cursor_get_data.introduced || '</' || l_str_introduced || '>';
334 summCtr:=summCtr+1;
335 -- <CRITICALITY>
336 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '<' || l_str_criticality || '>' || l_cursor_get_data.criticality || '</' || l_str_criticality || '>';
337 summCtr:=summCtr+1;
338 end if;
339 --
340 if p_report_content in (l_content_detail, l_content_both) then
341 --
342 -- </LIST_G_PROCESS>
343 -- </G_CRITICALLY>
344 --
345 -- <CRITICALITY_CODE>
346 -- <LIST_G_PROCESS>
347 --
348 if (l_cursor_get_data.criticality_code = 'C') and
349 not l_critCodeCShown then
350 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_g_criticality || '>';
351 critCtr:=critCtr+1;
352 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_criticality_code || '>' || l_cursor_get_data.criticality_code || '</' || l_str_criticality_code || '>';
353 critCtr:=critCtr+1;
354 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_list_g_process || '>';
355 critCtr:=critCtr+1;
356 l_critCodeCShown := true;
357 end if;
358 if (l_cursor_get_data.criticality_code = 'R') and
359 not l_critCodeRShown then
360 if l_critCodeCShown then
361 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_list_g_process || '>';
362 critCtr:=critCtr+1;
363 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_g_criticality || '>';
364 critCtr:=critCtr+1;
365 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_g_criticality || '>';
366 critCtr:=critCtr+1;
367 else
368 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_g_criticality || '>';
369 critCtr:=critCtr+1;
370 end if;
371 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_criticality_code || '>' || l_cursor_get_data.criticality_code || '</' || l_str_criticality_code || '>';
372 critCtr:=critCtr+1;
373 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_list_g_process || '>';
374 critCtr:=critCtr+1;
375 l_critCodeRShown := true;
376 end if;
377 if (l_cursor_get_data.criticality_code = 'O') and
378 not l_critCodeOShown then
379 if l_critCodeRShown or l_critCodeCShown then
380 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_list_g_process || '>';
381 critCtr:=critCtr+1;
382 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_g_criticality || '>';
383 critCtr:=critCtr+1;
384 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_g_criticality || '>';
385 critCtr:=critCtr+1;
386 else
387 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_g_criticality || '>';
388 critCtr:=critCtr+1;
389 end if;
390 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_criticality_code || '>' || l_cursor_get_data.criticality_code || '</' || l_str_criticality_code || '>';
391 critCtr:=critCtr+1;
392 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_list_g_process || '>';
393 critCtr:=critCtr+1;
394 l_critCodeOShown := true;
395 end if;
396 --
397 -- <G_PROCESS>
398 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_g_process || '>';
399 critCtr:=critCtr+1;
400 --
401 -- derivation of Detailed Report Fields
402 --
403 -- ENABLED_LEG_CODE
404 --
405 l_leg_code_rows := 0;
406 l_enabled_leg_code := null;
407 --
408 if (l_cursor_get_data.legislation_code is null) and
409 (l_cursor_get_data.legislatively_enabled = 'Y') then
410 --
411 for l_cursor_get_leg in c_get_legislation_code(l_cursor_get_data.upgrade_definition_id)
412 --
413 loop
414 if l_leg_code_rows > 0 then
415 l_enabled_leg_code := l_enabled_leg_code || ','
416 || l_cursor_get_leg.legislation_code;
417 else
418 l_enabled_leg_code := l_enabled_leg_code || l_cursor_get_leg.legislation_code;
419 end if;
420 l_leg_code_rows := l_leg_code_rows+1;
421 end loop;
422 end if;
423 --
424 if l_leg_code_rows = 0 then
425 l_enabled_leg_code := 'N/A';
426 end if;
427
428 --
429 -- PREPATCH
430 --
431 l_prepatch := get_parameter_value('CAN_RUN_PRE_PATCH',
432 l_cursor_get_data.upgrade_definition_id);
433 if l_prepatch = 'Y' then
434 l_prepatch := l_lookup_yes;
435 else l_prepatch := l_lookup_no;
436 end if;
437 --
438 -- INPATCH
439 --
440 l_inpatch := get_parameter_value('CAN_RUN_IN_PATCH',
441 l_cursor_get_data.upgrade_definition_id);
442 if l_inpatch = 'Y' then
443 l_inpatch := l_lookup_yes;
444 else l_inpatch := l_lookup_no;
445 end if;
446 --
447 -- POSTPATCH
448 --
449 l_postpatch := get_parameter_value('CAN_RUN_POST_PATCH',
450 l_cursor_get_data.upgrade_definition_id);
451 if l_postpatch = 'Y' then
452 l_postpatch := l_lookup_yes;
453 else l_postpatch := l_lookup_no;
454 end if;
455 --
456 -- WHEN_TO_RUN
457 --
458 l_when_to_run := nvl(get_parameter_value('WHEN_TO_RUN',
459 l_cursor_get_data.upgrade_definition_id),
460 'N/A');
461 --
462 -- EXEC_CONFIG_OPTION
463 --
464 l_exec_config_option := nvl(get_parameter_value('EXEC_CONFIG_OPTION',
465 l_cursor_get_data.upgrade_definition_id),
466 'N/A');
467 --
468 -- CONFIGURABLE
469 --
470 if (l_when_to_run = 'N/A') and (l_exec_config_option = 'N/A')
471 then l_configurable := l_lookup_no;
472 else l_configurable := l_lookup_yes;
473 end if;
474 --
475 -- CONFIG_OPTION_NAME
476 --
477 l_option_name := l_exec_config_option;
478 --
479 --
480 -- EXECUTION_POINT
481 --
482 l_execution_point := null;
483 --
484 if (l_option_name <> 'N/A') then
485 -- config option name
486 open c_profile_option_name(l_option_name);
487 fetch c_profile_option_name into l_config_option_name;
488 if c_profile_option_name%NOTFOUND then
489 l_config_option_name := l_option_name;
490 end if;
491 close c_profile_option_name;
492 -- exec_lookup_type
493 l_exec_lookup_type := nvl(get_parameter_value('EXEC_LOOKUP_TYPE',
494 l_cursor_get_data.upgrade_definition_id),
495 'N/A');
496 if l_exec_lookup_type = 'N/A' then
497 -- no EXEC_LOOKUP_TYPE parameter found so return internal value
498 fnd_profile.get(l_option_name, l_execution_point);
499 l_execution_point := nvl(l_execution_point,'N/A');
500 else
501 -- an EXEC_LOOKUP_TYPE parameter was found so derive lookup value
502 -- for the profile option and meaning
503 fnd_profile.get(l_option_name, l_lookup_execution_point);
504 l_execution_point := nvl(hr_general.decode_lookup(l_exec_lookup_type,
505 l_lookup_execution_point), l_lookup_execution_point);
506 end if;
507 --
508 else
509 l_config_option_name := l_option_name;
510 end if;
511 -- <UPGRADE_LEVEL>
512 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_upgrade_level || '>' || l_cursor_get_data.upgrade_level || '</' || l_str_upgrade_level || '>';
513 critCtr:=critCtr+1;
514 -- <CRITICALITY>
515 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_criticality || '>' || l_cursor_get_data.criticality || '</' || l_str_criticality || '>';
516 critCtr:=critCtr+1;
517 -- <PROCESS_NAME>
518 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_process_name || '>' || l_c_data_start || l_cursor_get_data.process_name || l_c_data_end || '</' || l_str_process_name || '>';
519 critCtr:=critCtr+1;
520 -- <LEGISLATION_CODE>
521 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_legislation_code || '>' || l_cursor_get_data.legislation_code || '</' || l_str_legislation_code || '>';
522 critCtr:=critCtr+1;
523 -- <ENABLED_LEG_CODE>
524 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_enabled_leg_code || '>' || l_enabled_leg_code || '</' || l_str_enabled_leg_code || '>' ;
525 critCtr:=critCtr+1;
526 -- <REQUIRED>
527 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_required || '>' || l_required || '</' || l_str_required || '>';
528 critCtr:=critCtr+1;
529 -- <PREPATCH>
530 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_prepatch || '>' || l_prepatch || '</' || l_str_prepatch || '>';
531 critCtr:=critCtr+1;
532 -- <INPATCH>
533 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_inpatch || '>' || l_inpatch || '</' || l_str_inpatch || '>';
534 critCtr:=critCtr+1;
535 -- <POSTPATCH>
536 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_postpatch || '>' || l_postpatch || '</' || l_str_postpatch || '>';
537 critCtr:=critCtr+1;
538 -- <CONFIGURABLE>
539 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_configurable || '>' || l_configurable || '</' || l_str_configurable || '>';
540 critCtr:=critCtr+1;
541 -- <CONFIG_OPTION_NAME>
542 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_config_option_name || '>' || l_c_data_start || l_config_option_name || l_c_data_end || '</' || l_str_config_option_name || '>';
543 critCtr:=critCtr+1;
544 -- <EXECUTION_POINT>
545 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_execution_point || '>' || l_execution_point || '</' || l_str_execution_point || '>';
546 critCtr:=critCtr+1;
547 -- <INTRODUCED>
548 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_introduced || '>' || l_cursor_get_data.introduced || '</' || l_str_introduced || '>';
549 critCtr:=critCtr+1;
550 -- <ADDITIONAL_INFO>
551 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_additional_info || '>' || l_c_data_start || l_cursor_get_data.additional_info || l_c_data_end || '</' || l_str_additional_info || '>';
552 critCtr:=critCtr+1;
553 ----------- STATUS SECTION ---------------------------------------------
554 for l_cursor_status_data in c_execution_status(l_cursor_get_data.upgrade_definition_id)
555 loop
556 -- <G_STATUS>
557 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_g_status || '>';
558 critCtr:=critCtr+1;
559 -- <STATUS_LEG_CODE>
560 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_status_leg_code || '>' || l_cursor_status_data.status_leg_code || '</' || l_str_status_leg_code || '>';
561 critCtr:=critCtr+1;
562 -- <STATUS_BG_NAME>
563 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_status_bg_name || '>' || l_c_data_start || nvl(l_cursor_status_data.status_bg_name,'N/A') || l_c_data_end || '</' || l_str_status_bg_name || '>';
564 critCtr:=critCtr+1;
565 -- <STATUS>
566 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_status || '>' || l_cursor_status_data.status || '</' || l_str_status || '>';
567 critCtr:=critCtr+1;
568 -- <EXECUTED>
569 if l_cursor_status_data.executed = 'Y'
570 then l_executed := l_lookup_yes;
571 else l_executed := l_lookup_no;
572 end if;
573 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_executed || '>' || l_executed || '</' || l_str_executed || '>';
574 critCtr:=critCtr+1;
575 -- <REQUEST_ID>
576 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '<' || l_str_request_id || '>' || to_char(l_request_id) || '</' || l_str_request_id || '>';
577 critCtr:=critCtr+1;
578 -- </G_STATUS>
579 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_g_status || '>';
580 critCtr:=critCtr+1;
581 end loop;---- for each status
582
583
584 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_g_process || '>';
585 critCtr:=critCtr+1;
586
587 end if;
588 --
589 -- <G_SUMMARY>
590 if p_report_content in (l_content_summary, l_content_both) then
591 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '</' || l_str_g_summary || '>';
592 summCtr:=summCtr+1;
593 end if;
594
595 end loop; -- for each process
596 --
597 -- </LIST G_PROCESS>
598 -- </G_CRITICALITY>
599 -- </LIST_G_CRITICALITY>
600 --
601 if p_report_content in (l_content_detail, l_content_both) then
602 if l_rows_found then
603 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_list_g_process || '>';
604 critCtr:=critCtr+1;
605 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_g_criticality || '>';
606 critCtr:=critCtr+1;
607 end if;
608 PER_DATA_UPDATE_REPORT.critXMLTable(critCtr).TagValue := '</' || l_str_list_g_criticality || '>';
609 critCtr:=critCtr+1;
610 end if;
611 --
612 -- </LIST_G_SUMMARY>
613 --
614 if p_report_content in (l_content_summary, l_content_both) then
615 PER_DATA_UPDATE_REPORT.summXMLTable(summCtr).TagValue := '</LIST_G_SUMMARY>';
616 summCtr:=summCtr+1;
617 end if;
618
619 /*
620 -- Options for writing
621 -- 1. Write from XML Table to XML file
622 --
623 WritetoXML(p_output_fname);
624 --
625 */
626
627 -- 2. Write from XML Table to BLOB
628 -- Writing to a BLOB
629 --
630 -- <START>
631 --
632 dbms_lob.writeAppend(l_xfdf_string, length('<' || l_str_start || '>'),
633 '<' || l_str_start || '>');
634 --
635 -- <DBNAME> DATABASE NAME </DBNAME>
636 --
637 select name into l_database_name from v$database;
638 dbms_lob.writeAppend(l_xfdf_string,
639 length('<DBNAME>' || l_database_name || '</DBNAME>'),
640 '<DBNAME>' || l_database_name || '</DBNAME>');
641 --
642 -- derive SUMMARY information
643 --
644 IF summXMLTable.count > 0 then
645 FOR ctr_summ_table in summXMLTable.FIRST .. summXMLTable.LAST LOOP
646 l_strsumm1 := summXMLTable(ctr_summ_table).TagValue;
647 dbms_lob.writeAppend(l_xfdf_string,
648 length(l_strsumm1),
649 l_strsumm1);
650 END LOOP;
651 END IF;
652 --
653 -- derive CRITICALITY information
654 --
655 IF critXMLTable.count > 0 then
656 FOR ctr_crit_table in critXMLTable.FIRST .. critXMLTable.LAST LOOP
657 l_strsumm2 := critXMLTable(ctr_crit_table).TagValue;
658 dbms_lob.writeAppend(l_xfdf_string,
659 length(l_strsumm2),
660 l_strsumm2);
661 END LOOP;
662 END IF;
663 --
664 -- </START>
665 dbms_lob.writeAppend(l_xfdf_string,length('</' || l_str_start || '>'),
666 '</' || l_str_start || '>');
667 --
668 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob, TRUE);
669 hr_xml_pub_utility.clob_to_blob(l_xfdf_string, l_xfdf_blob);
670 --
671 end POPULATE_REPORT_DATA;
672
673 PROCEDURE WritetoXML (
674 p_output_fname out nocopy varchar2)
675 IS
676 p_l_fp UTL_FILE.FILE_TYPE;
677 l_audit_log_dir varchar2(500);
678 l_file_name varchar2(50);
679 l_check_flag number;
680 l_database_name varchar2(10);
681 BEGIN
682 l_audit_log_dir := '/sqlcom/outbound';
683 -----------------------------------------------------------------------------
684 -- Writing into XML File
685 -----------------------------------------------------------------------------
686 -- Assigning the File name.
687 l_file_name := 'KK' || to_char(sysdate,'HH24:MI:SS') ||'.xml';
688 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
689 BEGIN
690 SELECT value
691 INTO l_audit_log_dir
692 FROM v$parameter
693 WHERE LOWER(name) = 'utl_file_dir';
694 -- Check whether more than one util file directory is found
695 IF INSTR(l_audit_log_dir,',') > 0 THEN
696 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
697 END IF;
698 EXCEPTION
699 when no_data_found then
700 null;
701 END;
702 -- Find out whether the OS is MS or Unix based
703 -- If it's greater than 0, it's unix based environment
704 IF INSTR(l_audit_log_dir,'/') > 0 THEN
705 p_output_fname := l_audit_log_dir || '/' || l_file_name;
706 ELSE
707 p_output_fname := l_audit_log_dir || '\' || l_file_name;
708 END IF;
709 -- getting Agency name
710 p_l_fp := utl_file.fopen(l_audit_log_dir,l_file_name,'A');
711 -- Writing from and to dates
712 --
713 --
714 -- <START>
715 --
716 PER_DATA_UPDATE_REPORT.vXMLTable(vCtr).TagValue := '<START>';
717 vCtr:=vCtr+1;
718 --
719 -- <DBNAME>
720 --
721 select name into l_database_name from v$database;
722 PER_DATA_UPDATE_REPORT.vXMLTable(vCtr).TagValue := '<DBNAME>' || l_database_name || '</DBNAME>';
723 vCtr:=vCtr+1;
724 --
725 -- SUMMARY LISTINGS
726 --
727 IF summXMLTable.count > 0 then
728 FOR ctr_summ_table IN summXMLTable.FIRST .. summXMLTable.LAST LOOP
729 PER_DATA_UPDATE_REPORT.vXMLTable(vCtr).TagValue := summXMLTable(ctr_summ_table).TagValue;
730 vCtr:=vCtr+1;
731 END LOOP;
732 END IF;
733 --
734 -- CRITICALITY LISTINGS
735 --
736 IF critXMLTable.count > 0 then
737 FOR ctr_crit_table IN critXMLTable.FIRST .. critXMLTable.LAST LOOP
738 PER_DATA_UPDATE_REPORT.vXMLTable(vCtr).TagValue := critXMLTable(ctr_crit_table).TagValue;
739 vCtr:=vCtr+1;
740 END LOOP;
741 END IF;
742 --
743 -- </START>
744 --
745 PER_DATA_UPDATE_REPORT.vXMLTable(vCtr).TagValue := '</START>';
746 vCtr:=vCtr+1;
747 -- Write to XML
748 --
749 IF vXMLTable.count > 0 then
750 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
751 WriteXMLvalues(p_l_fp,vXMLTable(ctr_table).TagValue);
752 END LOOP;
753 END IF;
754 utl_file.fclose(p_l_fp);
755 END WritetoXML;
756 ------------------------------------------------------------------
757 PROCEDURE WriteXMLvalues( p_l_fp utl_file.file_type, p_value IN VARCHAR2) IS
758 BEGIN
759 utl_file.put_line(p_l_fp, p_value );
760 null;
761 END WriteXMLvalues;
762 ------------------------------------------------------------------
763 procedure fetch_rtf_blob
764 (p_rtf_blob OUT NOCOPY blob)
765 IS
766 BEGIN
767 select file_data
768 into p_rtf_blob
769 from fnd_lobs
770 where file_name like '%/per/11.5.0/patch/115/publisher/templates/PERDTUPR.rtf';
771 EXCEPTION
772 when no_data_found then
773 null;
774 END fetch_rtf_blob;
775
776
777 end PER_DATA_UPDATE_REPORT;