1 PACKAGE BODY JTF_DIAG_REPORT_FACTORY AS
2 /* $Header: jtf_diag_report_factory_b.pls 120.3.12010000.4 2008/10/10 06:24:03 sramados ship $*/
3
4
5 PROCEDURE VALIDATE_REPORT_CONTEXT(report_context JTF_DIAG_REPORT_CONTEXT)
6 IS
7 BEGIN
8 IF report_context IS NULL THEN
9 RAISE_APPLICATION_ERROR(-20100,'ReportContext provided to instantiate the component is null');
10 END IF;
11 END VALIDATE_REPORT_CONTEXT;
12
13 FUNCTION CREATE_FOOTER(footer VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_FOOTER
14 IS
15 footer_component JTF_DIAG_FOOTER;
16 exec_id NUMBER;
17 ui_node_id NUMBER;
18 footer_content VARCHAR2(2000);
19 BEGIN
20 VALIDATE_REPORT_CONTEXT(report_context);
21 IF footer IS NOT NULL THEN
22 footer_content := DBMS_XMLGEN.CONVERT(footer,0);
23 exec_id := report_context.EXEC_ID;
24 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
25 footer_component := JTF_DIAG_FOOTER(exec_id,ui_node_id,footer_content,0);
26 ELSE
27 RAISE_APPLICATION_ERROR(-20100,'The content provided for the footer is null');
28 END IF;
29 RETURN footer_component;
30 END CREATE_FOOTER;
31 FUNCTION CREATE_HEADER(header VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_HEADER
32 IS
33 header_component JTF_DIAG_HEADER;
34 exec_id NUMBER;
35 ui_node_id NUMBER;
36 header_content VARCHAR2(2000);
37 BEGIN
38 VALIDATE_REPORT_CONTEXT(report_context);
39 IF header IS NOT NULL THEN
40 header_content := DBMS_XMLGEN.CONVERT(header,0);
41 exec_id := report_context.EXEC_ID;
42 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
43 header_component := JTF_DIAG_HEADER(exec_id,ui_node_id,header_content,0);
44 ELSE
45 RAISE_APPLICATION_ERROR(-20100,'The content provided for the header is null');
46 END IF;
47 RETURN header_component;
48 END CREATE_HEADER;
49 FUNCTION CREATE_SECTION(heading VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_SECTION
50 IS
51 section_component JTF_DIAG_SECTION;
52 exec_id NUMBER;
53 ui_node_id NUMBER;
54 section_heading VARCHAR2(2000);
55 BEGIN
56 VALIDATE_REPORT_CONTEXT(report_context);
57 IF heading IS NOT NULL THEN
58 section_heading := DBMS_XMLGEN.CONVERT(heading,0);
59 exec_id := report_context.EXEC_ID;
60 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
61 section_component := JTF_DIAG_SECTION(exec_id,ui_node_id,section_heading,0);
62 ELSE
63 RAISE_APPLICATION_ERROR(-20100,'Heading of the Section is null');
64 END IF;
65 RETURN section_component;
66 END CREATE_SECTION;
67 FUNCTION CREATE_MESSAGE(message VARCHAR2,message_type VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_MESSAGE
68 IS
69 message_component JTF_DIAG_MESSAGE;
70 exec_id NUMBER;
71 ui_node_id NUMBER;
72 message_content VARCHAR2(2000);
73 BEGIN
74 VALIDATE_REPORT_CONTEXT(report_context);
75 IF message IS NOT NULL THEN
76 message_content := DBMS_XMLGEN.CONVERT(message,0);
77 exec_id := report_context.EXEC_ID;
78 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
79 IF message_type IS NOT NULL AND (message_type = 'info' OR message_type = 'warning' OR message_type = 'error' OR message_type = 'attention') THEN
80 message_component := JTF_DIAG_MESSAGE(exec_id,ui_node_id,message_content,message_type,null,0,0,0);
81 ELSE
82 RAISE_APPLICATION_ERROR(-20100,'The message type '|| message_type||' is wrong: It can only be info, warning or error');
83 END IF;
84 ELSE
85 RAISE_APPLICATION_ERROR(-20100,'The content provided for the message is null');
86 END IF;
87 RETURN message_component;
88 END CREATE_MESSAGE;
89
90 FUNCTION CREATE_MESSAGE(note in out nocopy JTF_DIAG_NOTE,message_type VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_MESSAGE
91 IS
92 message_component JTF_DIAG_MESSAGE;
93 exec_id NUMBER;
94 ui_node_id NUMBER;
95 message_note_id NUMBER;
96 str VARCHAR2(4000);
97 message_content VARCHAR2(2000);
98 BEGIN
99 VALIDATE_REPORT_CONTEXT(report_context);
100 IF note IS NOT NULL THEN
101 message_note_id := note.UI_NODE_ID;
102 exec_id := report_context.EXEC_ID;
103 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
104 IF message_type IS NOT NULL AND (message_type = 'info' OR message_type = 'warning' OR message_type = 'error' OR message_type = 'attention') THEN
105 message_component := JTF_DIAG_MESSAGE(exec_id,ui_node_id,message_content,message_type,null,message_note_id,0,0);
106 IF NOTE.ADDED_TO_PARENT <> 1 THEN
107 str := note.CONSTRUCT_NODE;
108 INSERT INTO JTF_DIAGNOSTIC_REPORT
109 (Execution_ID,UI_Node_ID,type,xmldata,parent_node_id)
110 VALUES(exec_id
111 ,note.UI_Node_ID
112 ,'note'
113 ,xmltype(str)
114 ,ui_node_id);
115 NOTE.SET_ADDED_TO_PARENT;
116 ELSE
117 RAISE_APPLICATION_ERROR(-20100,'The Note component cannot be added
118 to the parent component again');
119 END IF;
120 ELSE
121 RAISE_APPLICATION_ERROR(-20100,'The message type '|| message_type||' is wrong: It can only be info, warning or error');
122 END IF;
123 ELSE
124 RAISE_APPLICATION_ERROR(-20100,'The Note component provided for the message is null');
125 END IF;
126 RETURN message_component;
127 END CREATE_MESSAGE;
128
129 FUNCTION CREATE_HIDE_SHOW(content VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_HIDE_SHOW
130 IS
131 hide_show_component JTF_DIAG_HIDE_SHOW;
132 exec_id NUMBER;
133 ui_node_id NUMBER;
134 hide_show_content VARCHAR2(20000);
135 BEGIN
136 VALIDATE_REPORT_CONTEXT(report_context);
137 IF content IS NOT NULL THEN
138 hide_show_content := DBMS_XMLGEN.CONVERT(content,0);
139 exec_id := report_context.EXEC_ID;
140 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
141 hide_show_component := JTF_DIAG_HIDE_SHOW(exec_id,ui_node_id,hide_show_content,'Hide','Show',0);
142 ELSE
143 RAISE_APPLICATION_ERROR(-20100,'The content provided for the hide show is null');
144 END IF;
145 RETURN hide_show_component;
146 END CREATE_HIDE_SHOW;
147 FUNCTION CREATE_RAW_TEXT(content VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_RAW_TEXT
148 IS
149 raw_text_component JTF_DIAG_RAW_TEXT;
150 exec_id NUMBER;
151 ui_node_id NUMBER;
152 raw_text_content VARCHAR2(20000);
153 BEGIN
154 VALIDATE_REPORT_CONTEXT(report_context);
155 IF content IS NOT NULL THEN
156 raw_text_content := content;
157 exec_id := report_context.EXEC_ID;
158 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
159 raw_text_component := JTF_DIAG_RAW_TEXT(exec_id,ui_node_id,raw_text_content,0);
160 ELSE
161 RAISE_APPLICATION_ERROR(-20100,'The content provided for the raw text is null');
162 END IF;
163 RETURN raw_text_component;
164 END CREATE_RAW_TEXT;
165 FUNCTION CREATE_LINK(linkText VARCHAR2, linkURL VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_LINK
166 IS
167 link_component JTF_DIAG_LINK;
168 exec_id NUMBER;
169 ui_node_id NUMBER;
170 link_text_content VARCHAR2(20000);
171 link_url_content VARCHAR2(20000);
172 BEGIN
173 VALIDATE_REPORT_CONTEXT(report_context);
174 IF linkText IS NOT NULL AND linkURL IS NOT NULL THEN
175 link_text_content := DBMS_XMLGEN.CONVERT(linkText,0);
176 link_url_content := DBMS_XMLGEN.CONVERT(linkURL,0);
177 exec_id := report_context.EXEC_ID;
178 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
179 link_component := JTF_DIAG_LINK(exec_id,ui_node_id,link_text_content,link_url_content,0);
180 ELSE
181 RAISE_APPLICATION_ERROR(-20100,'The content provided for the link is null');
182 END IF;
183 RETURN link_component;
184 END CREATE_LINK;
185 FUNCTION CREATE_FORM(heading VARCHAR2,form_keys JTF_VARCHAR2_TABLE_4000, form_values JTF_VARCHAR2_TABLE_4000,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_FORM
186 IS
187 form_component JTF_DIAG_FORM;
188 form_data_component JTF_DIAG_FORMDATA;
189 exec_id NUMBER;
190 form_ui_node_id NUMBER;
191 form_data_ui_node_id NUMBER;
192 form_heading VARCHAR2(2000);
193 BEGIN
194 VALIDATE_REPORT_CONTEXT(report_context);
195 IF heading IS NOT NULL THEN
196 form_heading := DBMS_XMLGEN.CONVERT(heading,0);
197 exec_id := report_context.EXEC_ID;
198 select JTF_DIAGNOSTIC_REPORT_S.nextval into form_ui_node_id from dual;
199 select JTF_DIAGNOSTIC_REPORT_S.nextval into form_data_ui_node_id from dual;
200 form_data_component := JTF_DIAG_FORMDATA(exec_id,form_data_ui_node_id,form_values,0);
201 form_component := JTF_DIAG_FORM(exec_id,form_ui_node_id,form_heading,form_keys,form_values,form_data_component,0);
202 ELSE
203 RAISE_APPLICATION_ERROR(-20100,'The content provided for the form is null');
204 END IF;
205 RETURN form_component;
206 END CREATE_FORM;
207 FUNCTION CREATE_TABLE(heading VARCHAR2, column_headers JTF_VARCHAR2_TABLE_4000,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TABLE
208 IS
209 table_component JTF_DIAG_TABLE;
210 exec_id NUMBER;
211 ui_node_id NUMBER;
212 no_of_cols NUMBER :=0;
213 table_heading VARCHAR2(4000);
214 BEGIN
215 VALIDATE_REPORT_CONTEXT(report_context);
216 IF heading IS NULL THEN
217 table_heading := ' ';
218 ELSE
219 table_heading := DBMS_XMLGEN.CONVERT(heading,0);
220 END IF;
221 IF column_headers IS NOT NULL THEN
222 exec_id := report_context.EXEC_ID;
223 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
224 FOR x IN column_headers.FIRST .. column_headers.LAST
225 LOOP
226 no_of_cols := no_of_cols + 1;
227 END LOOP;
228 table_component := JTF_DIAG_TABLE(exec_id,ui_node_id,column_headers,table_heading,null,0,no_of_cols,0,0,null);
229 ELSE
230 RAISE_APPLICATION_ERROR(-20100,'The content provided for the column header in table is null');
231 END IF;
232 RETURN table_component;
233 END CREATE_TABLE;
234
235 FUNCTION GET_COLUMN_NAMES(sql_query VARCHAR2) RETURN JTF_VARCHAR2_TABLE_4000
236 IS
237 cursor_id number;
238 columns_describe dbms_sql.desc_tab;
239 column_count number;
240 loop_counter number;
241 column_headers JTF_VARCHAR2_TABLE_4000;
242 BEGIN
243 column_headers := JTF_VARCHAR2_TABLE_4000();
244 cursor_id := DBMS_SQL.OPEN_CURSOR;
245 DBMS_SQL.PARSE(cursor_id, sql_query, DBMS_SQL.V7);
246 DBMS_SQL.DESCRIBE_COLUMNS(cursor_id, column_count, columns_describe);
247 FOR loop_counter in 1..column_count
248 LOOP
249 column_headers.extend(1);
250 column_headers(column_headers.count) := columns_describe(loop_counter).col_name;
251 END LOOP;
252 DBMS_SQL.CLOSE_CURSOR(cursor_id);
253 RETURN column_headers;
254 END GET_COLUMN_NAMES;
255
256
257 FUNCTION CREATE_TABLE(heading VARCHAR2, column_headers JTF_VARCHAR2_TABLE_4000, sql_query VARCHAR2, report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TABLE
258 IS
259 table_component JTF_DIAG_TABLE;
260 exec_id NUMBER;
261 ui_node_id NUMBER;
262 no_of_cols NUMBER :=0;
263 table_heading VARCHAR2(4000);
264 table_column_headers JTF_VARCHAR2_TABLE_4000;
265 BEGIN
266 VALIDATE_REPORT_CONTEXT(report_context);
267 IF sql_query IS NULL THEN
268 RAISE_APPLICATION_ERROR(-20100,'The sql query provided for the table is null');
269 END IF;
270 IF heading IS NULL THEN
271 table_heading := ' ';
272 ELSE
273 table_heading := DBMS_XMLGEN.CONVERT(heading,0);
274 END IF;
275 table_column_headers := GET_COLUMN_NAMES(sql_query);
276 --Check if the column_headers provided by the user is consistent with the query
277 IF column_headers IS NOT NULL THEN
278 IF table_column_headers.last = column_headers.last THEN
279 table_column_headers := column_headers;
280 ELSE
281 RAISE_APPLICATION_ERROR(-20100,'The number of columns/header is inconsistent in table' || table_heading);
282 END IF;
283 ELSE
284 RAISE_APPLICATION_ERROR(-20100,'The content provided for the column header in table is null');
285 END IF;
286 exec_id := report_context.EXEC_ID;
287 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
288 FOR x IN table_column_headers.FIRST .. table_column_headers.LAST
289 LOOP
290 no_of_cols := no_of_cols + 1;
291 END LOOP;
292 table_component := JTF_DIAG_TABLE(exec_id,ui_node_id,table_column_headers,table_heading,sql_query,1,no_of_cols,0,0,null);
293 RETURN table_component;
294 END CREATE_TABLE;
295
296 FUNCTION CREATE_TABLE(heading VARCHAR2, sql_query VARCHAR2, report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TABLE
297 IS
298 table_component JTF_DIAG_TABLE;
299 exec_id NUMBER;
300 ui_node_id NUMBER;
301 no_of_cols NUMBER :=0;
302 table_heading VARCHAR2(4000);
303 table_column_headers JTF_VARCHAR2_TABLE_4000;
304 BEGIN
305 VALIDATE_REPORT_CONTEXT(report_context);
306 IF sql_query IS NULL THEN
307 RAISE_APPLICATION_ERROR(-20100,'The sql query provided for the table is null');
308 END IF;
309 IF heading IS NULL THEN
310 table_heading := ' ';
311 ELSE
312 table_heading := DBMS_XMLGEN.CONVERT(heading,0);
313 END IF;
314 table_column_headers := GET_COLUMN_NAMES(sql_query);
315
316 exec_id := report_context.EXEC_ID;
317 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
318 FOR x IN table_column_headers.FIRST .. table_column_headers.LAST
319 LOOP
320 no_of_cols := no_of_cols + 1;
321 END LOOP;
322 table_component := JTF_DIAG_TABLE(exec_id,ui_node_id,table_column_headers,table_heading,sql_query,1,no_of_cols,0,0,null);
323 RETURN table_component;
324 END CREATE_TABLE;
325
326 FUNCTION CREATE_TABLE(column_headers JTF_VARCHAR2_TABLE_4000,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TABLE IS
327 table_component JTF_DIAG_TABLE;
328 BEGIN
329 table_component := CREATE_TABLE(null,column_headers,report_context);
330 RETURN table_component;
331 END CREATE_TABLE;
332
333 FUNCTION CREATE_TABLE(column_headers JTF_VARCHAR2_TABLE_4000, sql_query VARCHAR2, report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TABLE IS
334 table_component JTF_DIAG_TABLE;
335 BEGIN
336 table_component := CREATE_TABLE(null,column_headers,sql_query,report_context);
337 RETURN table_component;
338 END CREATE_TABLE;
339
340 FUNCTION CREATE_TABLE(sql_query VARCHAR2, report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TABLE IS
341 table_component JTF_DIAG_TABLE;
342 BEGIN
343 table_component := CREATE_TABLE(' ',sql_query,report_context);
344 RETURN table_component;
345 END CREATE_TABLE;
346
347 FUNCTION CREATE_ROW(report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_ROW IS
348 row_component JTF_DIAG_ROW;
349 exec_id NUMBER;
350 ui_node_id NUMBER;
351 --cols JTF_VARCHAR2_TABLE_4000;
352 BEGIN
353 VALIDATE_REPORT_CONTEXT(report_context);
354 exec_id := report_context.EXEC_ID;
355 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
356 --cols := JTF_VARCHAR2_TABLE_4000('');
357 row_component := JTF_DIAG_ROW(exec_id,ui_node_id,NULL,0);
358 RETURN row_component;
359 END CREATE_ROW;
360 FUNCTION CREATE_TREE(heading VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TREE IS
361 tree_component JTF_DIAG_TREE;
362 exec_id NUMBER;
363 ui_node_id NUMBER;
364 tree_heading VARCHAR2(20000);
365 BEGIN
366 VALIDATE_REPORT_CONTEXT(report_context);
367 IF heading IS NOT NULL THEN
368 tree_heading := DBMS_XMLGEN.CONVERT(heading,0);
369 exec_id := report_context.EXEC_ID;
370 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
371 tree_component := JTF_DIAG_TREE(exec_id,ui_node_id,tree_heading,0);
372 ELSE
373 RAISE_APPLICATION_ERROR(-20100,'The content provided for the tree is null');
374 END IF;
375 RETURN tree_component;
376 END CREATE_TREE;
377 FUNCTION CREATE_TREE_NODE(content VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_TREE_NODE IS
378 tree_node_component JTF_DIAG_TREE_NODE;
379 exec_id NUMBER;
380 ui_node_id NUMBER;
381 tree_content VARCHAR2(20000);
382 BEGIN
383 VALIDATE_REPORT_CONTEXT(report_context);
384 IF content IS NOT NULL THEN
385 tree_content := DBMS_XMLGEN.CONVERT(content,0);
386 exec_id := report_context.EXEC_ID;
387 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
388 tree_node_component := JTF_DIAG_TREE_NODE(exec_id,ui_node_id,tree_content,0);
389 ELSE
390 RAISE_APPLICATION_ERROR(-20100,'The content provided for the tree node is null');
391 END IF;
392 RETURN tree_node_component;
393 END CREATE_TREE_NODE;
394 FUNCTION CREATE_NOTE(content VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_NOTE IS
395 note_component JTF_DIAG_NOTE;
396 exec_id NUMBER;
397 ui_node_id NUMBER;
398 no_of_links NUMBER;
399 link_ids JTF_VARCHAR2_TABLE_4000;
400 note_content VARCHAR2(20000);
401 content_lenght NUMBER;
402 BEGIN
403 VALIDATE_REPORT_CONTEXT(report_context);
404 IF content IS NOT NULL THEN
405 note_content := DBMS_XMLGEN.CONVERT(content,0);
406 exec_id := report_context.EXEC_ID;
407 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
408 no_of_links := length(content)-length(replace(content,'?',''));
409 IF no_of_links > 0 THEN
410 link_ids := JTF_VARCHAR2_TABLE_4000();
411 FOR x IN 1 .. no_of_links
412 LOOP
413 link_ids.EXTEND;
414 link_ids(x) := '0';
415 END LOOP;
416 END IF;
417
418 note_component := JTF_DIAG_NOTE(exec_id,ui_node_id,note_content,no_of_links,link_ids,0,null);
419 ELSE
420 RAISE_APPLICATION_ERROR(-20100,'The content provided for the note is null');
421 END IF;
422 RETURN note_component;
423 END CREATE_NOTE;
424
425 FUNCTION CREATE_METALINK(linkText VARCHAR2, note_id VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_LINK
426 IS
427 link_component JTF_DIAG_LINK;
428 exec_id NUMBER;
429 ui_node_id NUMBER;
430 link_text_content VARCHAR2(20000);
431 link_url_content VARCHAR2(20000);
432 metalink_url VARCHAR2(20000);
433 BEGIN
434 VALIDATE_REPORT_CONTEXT(report_context);
435 IF linkText IS NOT NULL AND note_id IS NOT NULL THEN
436 link_text_content := DBMS_XMLGEN.CONVERT(linkText,0);
437 metalink_url := FND_PROFILE.value('OAM_DIAG_METALINK_URL');
438 metalink_url := metalink_url || note_id;
439 link_url_content := DBMS_XMLGEN.CONVERT(metalink_url,0);
440 exec_id := report_context.EXEC_ID;
441 select JTF_DIAGNOSTIC_REPORT_S.nextval into ui_node_id from dual;
442 link_component := JTF_DIAG_LINK(exec_id,ui_node_id,link_text_content,link_url_content,0);
443 ELSE
444 RAISE_APPLICATION_ERROR(-20100,'The content provided for the link is null');
445 END IF;
446 RETURN link_component;
447 END CREATE_METALINK;
448
449 FUNCTION CREATE_METALINK(note_id VARCHAR2,report_context JTF_DIAG_REPORT_CONTEXT) RETURN JTF_DIAG_LINK
450 IS
451 link_component JTF_DIAG_LINK;
452 BEGIN
453 link_component := CREATE_METALINK(note_id,note_id,report_context);
454 RETURN link_component;
455 END CREATE_METALINK;
456
457 END JTF_DIAG_REPORT_FACTORY;
458
[Home] [Help]