[Home] [Help]
PACKAGE BODY: APPS.FA_ASSET_TRACE_PVT
Source
1 PACKAGE BODY FA_ASSET_TRACE_PVT AS
2 /* $Header: faxtrcvb.pls 120.0.12010000.2 2008/10/13 19:43:54 hhafid noship $ */
3
4 g_options_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
5 g_col_exclusions FA_ASSET_TRACE_PUB.t_excl_tbl;
6 g_tmp_opt_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
7 g_tmpc_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
8 g_tbl_hldr FA_ASSET_TRACE_PKG.t_col_tbl;
9 g_anchor_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
10 g_no_rec_tbl t_asset_tbl;
11 g_tmp_tbl t_asset_tbl;
12 g_output_tbl t_asset_tbl;
13 g_req_tbl t_num_tbl;
14 g_fcol_tbl VARCHAR2(100);
15
16 g_chk_cnt BOOLEAN;
17 g_trc_tbl VARCHAR2(100);
18 g_anchor VARCHAR2(32767);
19
20 g_schema VARCHAR2(50);
21 g_print_debug boolean;
22
23 --Load global table.
24
25 PROCEDURE initialize_globals (p_opt_tbl IN FA_ASSET_TRACE_PUB.t_options_tbl,
26 p_exc_tbl IN FA_ASSET_TRACE_PUB.t_excl_tbl,
27 p_schema OUT NOCOPY VARCHAR2,
28 p_debug_flag IN BOOLEAN,
29 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
30
31 l_app_short_name VARCHAR2(50);
32 l_status varchar2(100);
33 l_industry varchar2(100);
34 schema_err exception;
35 null_param_tbl EXCEPTION;
36
37 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.load_globals';
38
39 BEGIN
40 --Validate param tbl.
41 IF g_param_tbl.count = 0 THEN
42 raise null_param_tbl;
43 END IF;
44
45 -- Get schema
46 l_app_short_name := FND_GLOBAL.APPLICATION_SHORT_NAME;
47 if ((nvl(g_use_utl_file, 'N')='Y') and (l_app_short_name is null)) then
48 l_app_short_name :='OFA';
49 end if;
50 if not (fnd_installation.get_app_info (
51 application_short_name => l_app_short_name,
52 status => l_status,
53 industry => l_industry,
54 oracle_schema => g_schema)) then
55 raise schema_err;
56 end if;
57
58 p_schema := g_schema;
59 g_print_debug := TRUE; --p_debug_flag;
60
61 g_anchor_tbl.delete;
62 g_options_tbl.delete;
63 g_col_exclusions.delete;
64
65 IF p_opt_tbl.count > 0 THEN
66 g_options_tbl := p_opt_tbl;
67 ELSE
68 log(l_calling_fn, 'p_opt_tbl has no data');
69 END IF;
70
71 IF p_exc_tbl.count > 0 THEN
72 g_col_exclusions := p_exc_tbl;
73 ELSE
74 log(l_calling_fn, 'p_exc_tbl has no data');
75 END IF;
76
77 for i in g_options_tbl.first .. g_options_tbl.last loop
78 log (l_calling_fn, g_options_tbl(i).l_tbl);
79 end loop;
80
81 EXCEPTION
82 WHEN null_param_tbl THEN
83 log(l_calling_fn, 'Error');
84 log(l_calling_fn,'null_param_tbl exception, param tbl is null');
85 raise;
86 WHEN schema_err THEN
87 log(l_calling_fn, 'Error');
88 raise;
89 WHEN OTHERS THEN
90 log(l_calling_fn, 'Error');
91 raise;
92
93 END initialize_globals;
94 --
95 -- Validates non-setup related tables and calls build_stmt and exec_sql.
96 --
97 PROCEDURE do_primary IS
98
99 l_cursor c_stmt;
100 l_primary_tbls FA_ASSET_TRACE_PUB.t_options_tbl;
101 l_col_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
102 l_t_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
103 l_cur_tab_name varchar2(100);
104 l_tmp_tbl VARCHAR2(100):='NOTHING';
105 l_tblcount number;
106 l_sel VARCHAR2(2000);
107 l_cnt_stmt VARCHAR2(2000);
108 l_stmt VARCHAR2(32767);
109 l_schema VARCHAR2(5);
110
111 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.do_primary';
112
113 BEGIN
114
115 l_primary_tbls := g_options_tbl;
116
117 FOR i IN l_primary_tbls.first .. l_primary_tbls.last LOOP
118 --check if table is from a different schema
119 if l_primary_tbls(i).l_schema is not null then
120 l_schema := l_primary_tbls(i).l_schema;
121 else
122 l_schema := g_schema;
123 end if;
124
125 if (nvl(l_primary_tbls(i).l_fullcol_list, 'N') = 'N') then
126 do_col_exclusions (p_tbl => l_primary_tbls(i).l_tbl,
127 p_schema => l_schema,
128 x_stmt => l_stmt);
129
130 if (g_print_debug) then
131 log(l_calling_fn, l_stmt);
132 end if;
133
134 OPEN l_cursor FOR l_stmt;
135 FETCH l_cursor BULK COLLECT INTO l_t_tbl, l_col_tbl;
136 CLOSE l_cursor;
137 elsif (nvl(l_primary_tbls(i).l_fullcol_list, 'N') = 'Y') then
138 g_fcol_tbl := l_primary_tbls(i).l_tbl;
139 end if; -- if (nvl(l_primary_tbls(i).l_fullcol_list...
140
141 IF ((l_t_tbl.count > 0) or
142 (nvl(l_primary_tbls(i).l_fullcol_list, 'N') = 'Y')) THEN
143
144 build_stmt(p_t_tbl => l_t_tbl, p_col_tbl => l_col_tbl);
145
146 FOR j IN g_sel_tbl.first .. g_sel_tbl.last LOOP
147
148 l_cur_tab_name := g_tbl_hldr(j);
149 l_sel := g_sel_tbl(j);
150 g_dyn_head := g_hdr_tbl(j);
151
152 if (g_print_debug) then
153 log(l_calling_fn,'Inner loop, l_cur_tab_name: '||l_cur_tab_name);
154 log(l_calling_fn,'Inner loop, l_sel: '||l_sel);
155 log(l_calling_fn,'Inner loop, g_dyn_head: '||g_dyn_head);
156 end if;
157
158 IF l_tmp_tbl <> l_cur_tab_name THEN
159 l_cnt_stmt := l_primary_tbls(i).l_cnt_stmt;
160 IF l_cnt_stmt IS NOT NULL THEN
161 g_chk_cnt :=get_tbl_cnt (NULL,l_cnt_stmt,l_schema);
162 ELSE
163 IF (nvl(l_primary_tbls(i).l_gen_select, 'N') = 'Y') THEN
164 g_chk_cnt :=get_tbl_cnt (l_cur_tab_name,NULL,l_schema);
165 ELSIF nvl(l_primary_tbls(i).l_gen_select, 'N') = 'N' THEN
166 g_chk_cnt := TRUE;
167 l_schema :='NONE';
168 END IF;
169 END IF;
170 END IF;
171
172 l_tmp_tbl := l_cur_tab_name;
173
174 --check if row header is needed
175 if (nvl(l_primary_tbls(i).l_no_header, 'N') = 'Y') then
176 g_no_header := 'Y';
177 end if;
178
179 IF g_chk_cnt THEN
180 exec_sql(l_cur_tab_name,l_sel,NULL,l_schema);
181 ELSE
182 l_tblcount := g_no_rec_tbl.count +1;
183 g_no_rec_tbl (l_tblcount) :=l_cur_tab_name;
184 END IF;
185 --
186 END LOOP; --g_sel_tbl
187 l_col_tbl := g_tmpc_tbl;
188 l_t_tbl := g_tmpc_tbl;
189 END IF; --l_t_tbl.count > 0
190 END LOOP; --end l_primary_tbls loop
191 l_primary_tbls := g_tmp_opt_tbl;
192 log(l_calling_fn, 'Done with primary');
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 log(l_calling_fn, 'Error');
197 raise;
198
199 END do_primary;
200 --
201 PROCEDURE do_col_exclusions (p_tbl IN VARCHAR2,
202 p_schema IN VARCHAR2,
203 x_stmt OUT NOCOPY VARCHAR2,
204 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
205
206 l_col_exclusions FA_ASSET_TRACE_PUB.t_excl_tbl;
207 l_tmp_tbl FA_ASSET_TRACE_PUB.t_excl_tbl;
208 l_stmt VARCHAR2(32767);
209 l_part_stmt VARCHAR2(4000);
210 l_col_list VARCHAR2(1000);
211 l_col_order varchar2(2000);
212 l_ord_list VARCHAR2(4000);
213 l_parsed_str varchar2(100);
214
215 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.do_col_exclusions';
216
217 BEGIN
218
219 --Reinitialize the table.
220 l_col_exclusions := l_tmp_tbl;
221 l_col_exclusions := g_col_exclusions;
222
223
224 l_stmt :='SELECT table_name, column_name
225 FROM all_tab_columns
226 WHERE owner = ''' || p_schema || ''' ' ||
227 'AND table_name = '||''''||p_tbl||''''||fnd_global.local_chr(10);
228
229 l_part_stmt := ' AND column_name not in (';
230
231 FOR i IN g_options_tbl.FIRST .. g_options_tbl.LAST LOOP
232 IF g_options_tbl(i).l_tbl = p_tbl THEN
233 --exclude cases where the col_order represents the full list.
234 if (nvl(g_options_tbl(i).l_fullcol_list, 'N') = 'N') then
235 l_col_order := g_options_tbl(i).l_col_order;
236 end if;
237 l_ord_list := g_options_tbl(i).l_lcs||','||l_col_order;
238 IF l_ord_list IS NOT NULL THEN
239 WHILE (TRUE) LOOP
240 l_parsed_str := substr(l_ord_list,1,instr(l_ord_list,',')-1);
241 IF (instr(l_ord_list,',')=0 or l_parsed_str IS NULL) THEN
242 IF l_ord_list IS NOT NULL THEN
243 l_part_stmt :=l_part_stmt||''''||l_ord_list||''''||',';
244 END IF;
245 EXIT;
246 ELSE
247 l_part_stmt :=l_part_stmt||''''||l_parsed_str||''''||',';
248 l_ord_list := substr(l_ord_list,instr(l_ord_list,',')+1,length(l_ord_list));
249 END IF;
250 END LOOP;
251 END IF;
252 EXIT;
253 END IF;
254 END LOOP;
255
256 IF (l_col_exclusions.count > 0) THEN
257 FOR i in l_col_exclusions.first .. l_col_exclusions.last LOOP
258 IF l_col_exclusions(i).cType = 'P' THEN
259 l_col_list := ' AND COLUMN_NAME NOT LIKE '||''''||l_col_exclusions(i).cValue||''''||fnd_global.local_chr(10);
260 l_stmt := l_stmt||l_col_list;
261 ELSE
262 l_part_stmt := l_part_stmt||''''||l_col_exclusions(i).cValue||''''||',';
263 END IF;
264 END LOOP;
265 ELSE
266 log(l_calling_fn, 'No exclusions to process.');
267 END IF; -- l_col_exclusions.count > 0
268
269 l_part_stmt := substr(l_part_stmt, 1, length(l_part_stmt) -1)||')';
270 l_stmt := l_stmt||l_part_stmt||fnd_global.local_chr(10);
271
272 IF nvl(g_jx_enabled, 'N') = 'N' THEN
273 l_stmt := l_stmt||' AND column_name not like '||''''||'GLOBAL_AT%'||'''';
274 END IF;
275
276 l_stmt := l_stmt||fnd_global.local_chr(10)||' Order by column_name';
277 x_stmt := l_stmt;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 log(l_calling_fn, 'Error');
282 raise;
283
284 END do_col_exclusions;
285 --
286 -- builds the header and select clauses.
287 -- note: it's not efficient to build the full header or select clause here
288 -- as there are tables that we want to have certain leading columns to identify
289 -- the row.
290 --
291 PROCEDURE build_stmt(p_t_tbl IN FA_ASSET_TRACE_PKG.t_col_tbl,
292 p_col_tbl IN FA_ASSET_TRACE_PKG.t_col_tbl,
293 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)IS
294
295 l_t_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
296 l_col_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
297 l_dummy_tbl FA_ASSET_TRACE_PUB.t_options_tbl;
298 l_tmp varchar2(100);
299 l_tmpt varchar2(100);
300 l_tmp_col varchar2(100);
301 l_tmp_ctr number;
302 l_counter number :=0;
303 l_count number :=0;
304 l_idx number:=0;
305 l_select_clause varchar2(32767);
306 l_dyn_head VARCHAR2(32767);
307
308 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.build_stmt';
309
310 BEGIN
311 g_tbl_hldr:= g_tmpc_tbl;
312 g_sel_tbl :=g_tmp_tbl;
313 g_hdr_tbl :=g_tmp_tbl;
314
315 IF (p_t_tbl.count > 0) THEN
316 l_tmp_ctr:=p_t_tbl.first;
317 l_tmp := p_t_tbl(l_tmp_ctr);
318 ELSIF (g_fcol_tbl is not null) THEN
319 l_tmp := g_fcol_tbl;
320 g_fcol_tbl := null;
321 END IF;
322
323 --Get desired column order + load the local table with the
324 --ordered columns + the remaining cols (what came into the proc.)
325 l_tmpt := l_tmp;
326 col_order(l_tmpt, l_col_tbl, l_t_tbl);
327
328 l_count := l_col_tbl.count;
329 IF (p_t_tbl.count > 0) THEN
330 IF (p_t_tbl.count = p_col_tbl.count) THEN
331 FOR tc IN p_t_tbl.first .. p_t_tbl.last LOOP
332 l_count := l_count+1;
333 IF p_t_tbl(tc) <> l_tmpt THEN
334 l_tmpt := p_t_tbl(tc);
335 col_order(l_tmpt, l_col_tbl, l_t_tbl);
336 l_count := l_col_tbl.count+1;
337 l_col_tbl(l_count):= p_col_tbl(tc);
338 l_t_tbl(l_count):= p_t_tbl(tc);
339 ELSE
340 l_col_tbl(l_count):=p_col_tbl(tc);
341 l_t_tbl(l_count):=p_t_tbl(tc);
342 END IF;
343 END LOOP;
344 ELSE
345 if g_print_debug then
346 log(l_calling_fn, 'The table count is not equal');
347 log(l_calling_fn, 'p_t_tbl.count: '||to_char(p_t_tbl.count));
348 log(l_calling_fn, 'p_col_tbl.count: '||to_char(p_col_tbl.count));
349 end if;
350 END IF; -- (p_t_tbl.count = p_col_tbl.count)
351 END IF; -- (p_t_tbl.count > 0)
352
353 get_options (p_table => l_tmp,
354 p_opt_tbl => l_dummy_tbl,
355 p_sv_col => l_counter);
356 -- in case it comes back null from above
357 l_counter := nvl(l_counter, 0);
358
359 IF ((l_t_tbl.count > 0) and (l_t_tbl.count = l_col_tbl.count)) THEN
360 FOR i IN l_col_tbl.first .. l_col_tbl.last LOOP
361 l_counter :=l_counter+1;
362
363 IF instr(l_col_tbl(i),'.') >0 THEN --Column has table alias
364 l_tmp_col :=substr(l_col_tbl(i),instr(l_col_tbl(i),'.')+1,length(l_col_tbl(i)));
365 ELSE
366 l_tmp_col :=l_col_tbl(i);
367 END IF;
368
369 IF (l_tmp <> l_t_tbl(i) OR l_counter >= 10) OR (i = l_t_tbl.last) THEN
370 l_idx := g_sel_tbl.count + 1;
371 g_tbl_hldr(l_idx) := l_tmp;
372
373 IF i=l_t_tbl.last THEN
374 l_dyn_head:=l_dyn_head||fparse_header(l_tmp_col);
375 l_select_clause := l_select_clause||fafsc(l_col_tbl(i));
376 END IF;
377 -- NB: -6 to take out the cariage return before the 'From' clause.
378 -- If you change the cariage return representation, then change this accordingly.
379 l_select_clause := substr(l_select_clause, 1, length(l_select_clause) -6);
380 l_select_clause := l_select_clause ||'||'||''''||'</TR>'||'''';
381 g_sel_tbl(l_idx) := l_select_clause;
382 l_dyn_head := l_dyn_head ||'</TR>';
383 g_hdr_tbl(l_idx) := l_dyn_head;
384
385 IF l_tmp = l_t_tbl(i) THEN
386 l_dyn_head:='NH';
387 ELSE
388 l_dyn_head:=NULL;
389 END IF;
390
391 l_tmp := l_t_tbl(i);
392 l_select_clause := fafsc(l_col_tbl(i));
393 l_dyn_head:=l_dyn_head||fparse_header(l_tmp_col);
394 l_counter :=0;
395 get_options (p_table => l_tmp,
396 p_opt_tbl => l_dummy_tbl,
397 p_sv_col => l_counter);
398 -- in case it comes back null from above
399 l_counter := nvl(l_counter, 0);
400 ELSE
401 l_dyn_head:=l_dyn_head||fparse_header(l_tmp_col);
402 l_select_clause := l_select_clause||fafsc(l_col_tbl(i));
403 END IF; -- (l_tmp <> l_t_tbl(i) OR ...
404 END LOOP;
405 END IF; -- ((l_t_tbl.count > 0) and ...
406
407 IF (g_tbl_hldr.count <> g_sel_tbl.count) or (g_tbl_hldr.count <> g_hdr_tbl.count) THEN
408 log(l_calling_fn, 'The table count is not equal');
409 log(l_calling_fn, 'g_tbl_hldr.count: '||to_char(g_tbl_hldr.count));
410 log(l_calling_fn, 'g_sel_tbl.count: '||to_char(g_sel_tbl.count));
411 log(l_calling_fn, 'g_hdr_tbl.count: '||to_char(g_hdr_tbl.count));
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 log(l_calling_fn, 'Error');
417 raise;
418
419 END build_stmt;
420 --
421 -- Builds final header and sql and populates the output table.
422 --
423 PROCEDURE exec_sql (p_table IN VARCHAR2,
424 p_sel_clause IN VARCHAR2,
425 p_stmt IN VARCHAR2,
426 p_schema IN VARCHAR2 DEFAULT 'FA',
427 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
428
429 l_sql_stmt VARCHAR2(4000) :=p_stmt;
430 l_stmt VARCHAR2(4000);
431 l_insert VARCHAR2(4000);
432 l_hdr_insert VARCHAR2(4000);
433 l_tbl VARCHAR2(100) := p_table;
434 l_orderby VARCHAR2(1000);
435 l_header VARCHAR2(1000);
436 l_anchor_cnt NUMBER;
437 l_counter NUMBER;
438 l_cursor c_stmt;
439
440 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.exec_sql';
441
442 BEGIN
443
444 get_final_sql (p_table => l_tbl,
445 p_sel_clause => p_sel_clause,
446 x_header => l_header,
447 px_sql => l_sql_stmt,
448 p_schema => p_schema);
449
450 IF (substr(g_dyn_head, 1,2) = 'NH') THEN
451 g_dyn_head := substr(g_dyn_head, 3, length(g_dyn_head));
452 g_dyn_head := '</TABLE><TABLE BORDER="0" width="93%" cellpadding="5" BORDERCOLOR="#c9cbd3"><TR>'||l_header||g_dyn_head;
453 ELSIF (substr(p_table, 1,2) = 'NH') THEN
454 g_dyn_head := '</TABLE><TABLE BORDER="0" width="93%" cellpadding="5" BORDERCOLOR="#c9cbd3"><TR>'||l_header||g_dyn_head;
455 ELSE
456 g_dyn_head:='</TABLE><TABLE BORDER="0" width="93%" cellpadding="5" BORDERCOLOR="#c9cbd3"><TR><TD BGCOLOR="#3a5a87">
457 <font color="#ffffff"><B><A NAME="'||p_table||'">'||p_table||'</B></TD><TD><A HREF=#ANCHORS>Back To Table Anchors</A></TD>
458 </TR></TABLE><TABLE BORDER="0" width="93%" cellpadding="5" BORDERCOLOR="#c9cbd3"><TR>'||l_header||g_dyn_head;
459
460 l_anchor_cnt:=g_anchor_tbl.count+1;
461 IF ((p_table <> 'NO_ANCHOR') or (l_tbl <> 'NO_ANCHOR')) THEN
462 g_anchor_tbl(l_anchor_cnt):=p_table;
463 END IF;
464 END IF;
465
466 l_hdr_insert := 'SELECT '||''''||g_dyn_head||''''||' FROM DUAL';
467 l_stmt := l_sql_stmt;
468
469 if g_print_debug then
470 log(l_calling_fn,'l_hdr_insert is: '||l_hdr_insert);
471 log(l_calling_fn,'l_stmt is: '||l_stmt);
472 end if;
473
474 DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD-MON-YYYY HH24:MI:SS''');
475
476 l_counter :=g_output_tbl.count;
477 IF NVL(g_no_header,'Y')<>'Y' THEN
478 l_counter := l_counter + 1;
479 EXECUTE IMMEDIATE l_hdr_insert INTO g_output_tbl(l_counter);
480 END IF;
481
482 --reset g_no_header
483 g_no_header:= 'N';
484
485 OPEN l_cursor FOR l_stmt;
486
487 l_counter :=g_output_tbl.count;
488 LOOP
489 l_counter := l_counter + 1;
490 FETCH l_cursor INTO g_output_tbl(l_counter);
491 EXIT WHEN l_cursor%NOTFOUND;
492 END LOOP;
493
494 CLOSE l_cursor;
495
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 log(l_calling_fn, 'Error');
500 log(l_calling_fn,'In exec-sql exception, l_stmt is: '||l_stmt);
501 log(l_calling_fn,'In exec-sql exception, l_hdr_insert is: '||l_hdr_insert);
502 raise;
503
504 END exec_sql;
505 --
506 -- Takes care of the correct ordering of certain tables and also provides
507 -- the leading columns that need to be displayed first to identify the row.
508 -- called from exec_sql.
509 --
510 PROCEDURE get_final_sql (p_table IN OUT NOCOPY VARCHAR2,
511 p_sel_clause IN VARCHAR2,
512 x_header OUT NOCOPY VARCHAR2,
513 px_sql IN OUT NOCOPY VARCHAR2,
514 p_schema IN VARCHAR2,
515 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
516
517 l_sql_stmt VARCHAR2(4000) := ' SELECT '||''''||'<TR>'||''''||'||';
518 l_tbl VARCHAR2(100) := p_table;
519 l_from VARCHAR2(2000);
520 l_chk_col number;
521 l_add_clause VARCHAR2(2000);
522 l_orderby VARCHAR2(1000) := ' ORDER BY ';
523 l_header VARCHAR2(1000);
524 l_order_cols VARCHAR2(1000);
525 l_leading_cols VARCHAR2(1000);
526
527 l_found BOOLEAN :=FALSE;
528
529 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.get_final_sql';
530
531 BEGIN
532
533 IF p_schema <> 'NONE' THEN
534 l_chk_col := check_column (p_table, p_schema);
535 END IF;
536
537 l_from := ' FROM ' || p_table || get_param(l_chk_col);
538
539 IF px_sql IS NOT NULL THEN
540 px_sql:= l_sql_stmt||px_sql;
541 ELSE
542 FOR i IN g_options_tbl.FIRST .. g_options_tbl.LAST LOOP
543 IF g_options_tbl(i).l_tbl = l_tbl THEN
544 l_leading_cols := g_options_tbl(i).l_leading_cols;
545 l_add_clause := g_options_tbl(i).l_add_clause;
546 l_header := g_options_tbl(i).l_lc_header;
547 l_order_cols := g_options_tbl(i).l_order_by;
548 if (nvl(g_options_tbl(i).l_no_anchor, 'N') = 'Y') then
549 p_table := 'NO_ANCHOR';
550 end if;
551 l_found := TRUE;
552 EXIT;
553 END IF;
554 END LOOP;
555
556 IF NOT l_found THEN
557 l_sql_stmt := l_sql_stmt;
558 l_orderby := NULL;
559 l_header := NULL;
560 END IF;
561
562 x_header := l_header;
563
564 if l_order_cols is not null then
565 l_orderby :=l_orderby||l_order_cols;
566 else
567 l_orderby := null;
568 end if;
569
570 IF l_add_clause IS NOT NULL THEN
571 px_sql := l_sql_stmt||l_leading_cols||p_sel_clause||l_add_clause||NVL(l_orderby,'');
572 ELSE
573 px_sql := l_sql_stmt||l_leading_cols||p_sel_clause||l_from||NVL(l_orderby,'');
574 END IF;
575 END IF; --px_sql not null
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 log(l_calling_fn, 'Error');
580 raise;
581
582 END get_final_sql;
583 --
584 --gets table options.
585 --
586 PROCEDURE get_options (p_table IN VARCHAR2,
587 p_opt_tbl IN OUT NOCOPY FA_ASSET_TRACE_PUB.t_options_tbl,
588 p_sv_col IN OUT NOCOPY NUMBER,
589 p_mode IN VARCHAR2 default null,
590 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
591
592 l_count NUMBER:=1;
593
594 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.get_options';
595
596 BEGIN
597
598 FOR i IN g_options_tbl.FIRST .. g_options_tbl.LAST LOOP
599 IF g_options_tbl(i).l_tbl = p_table THEN
600 p_sv_col := g_options_tbl(i).l_num_cols;
601 EXIT;
602 END IF;
603 END LOOP;
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 log(l_calling_fn, 'Error');
608 raise;
609
610 END get_options;
611 --
612 --Get profile and system options.
613 --
614 PROCEDURE get_system_options (p_sys_opt_tbl IN VARCHAR2,
615 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
616
617 l_userid NUMBER;
618 l_respid fnd_profile_option_values.level_value%type;
619 l_appid fnd_profile_option_values.level_value_application_id%type;
620 l_resp_name VARCHAR2(100);
621 l_stmt VARCHAR2(32767);
622 l_counter NUMBER :=0;
623 l_cursor c_stmt;
624 l_col_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
625 l_t_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
626
627 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.get_system_options';
628
629 BEGIN
630 l_appid := FND_GLOBAL.resp_appl_id; log(l_calling_fn, 'l_appid: '||l_appid);
631 l_userid := fnd_global.user_id; log(l_calling_fn, 'l_userid: '||l_userid);
632 l_respid := Fnd_Global.Resp_Id; log(l_calling_fn, 'l_respid: '||l_respid);
633
634 if (nvl(g_use_utl_file, 'Y')='N') then
635 l_stmt :=fafsc('fpon.profile_option_name')||fafsc('site_pov.profile_option_value');
636 l_stmt :=l_stmt||fafsc('appl_pov.profile_option_value')||fafsc('resp_pov.profile_option_value');
637 l_stmt :=l_stmt||fafsc('user_pov.profile_option_value');
638 l_stmt :=substr(l_stmt, 1, length(l_stmt) -6)||'||'||''''||'</TR>'||'''';
639 l_stmt :=l_stmt||' from fnd_profile_options fpon, fnd_profile_option_values user_pov
640 , fnd_profile_option_values resp_pov, fnd_profile_option_values appl_pov
641 , fnd_profile_option_values site_pov
642 where fpon.profile_option_id = user_pov.profile_option_id(+)
643 and fpon.application_id = user_pov.application_id(+)
644 and fpon.profile_option_id = resp_pov.profile_option_id(+)
645 and fpon.application_id = resp_pov.application_id(+)
646 and fpon.profile_option_id = appl_pov.profile_option_id(+)
647 and fpon.application_id = appl_pov.application_id(+)
648 and fpon.profile_option_id = site_pov.profile_option_id(+)
649 and fpon.application_id = site_pov.application_id(+)
650 and fpon.application_id = '||l_appid||
651 ' and resp_pov.LEVEL_VALUE(+) = '||l_respid||
652 ' and user_pov.LEVEL_VALUE(+) = '||l_userid||
653 ' and site_pov.level_id(+) = 10001
654 and appl_pov.level_id(+) = 10002
655 and resp_pov.level_id(+) = 10003
656 and user_pov.level_id(+) = 10004
657 and fpon.start_date_active <= sysdate
658 and nvl(fpon.end_date_active, sysdate) >= sysdate';
659
660 g_dyn_head := fparse_header('Profile_Option')||fparse_header('Site_level')
661 ||fparse_header('Application_level')||fparse_header('Responsibility_level')
662 ||fparse_header('User_level')||'</TR>';
663
664 exec_sql (p_table => 'SYSTEM_PROFILE_OPTIONS',
665 p_sel_clause => NULL,
666 p_stmt => l_stmt,
667 p_schema => 'NONE');
668 else
669 log(l_calling_fn, 'Not doing profiles cause u seem to be calling from backend');
670 end if;
671
672 if (p_sys_opt_tbl is not null) then
673 l_stmt :=NULL;
674 do_col_exclusions (p_sys_opt_tbl,nvl(g_schema,'FA'), l_stmt);
675
676 OPEN l_cursor FOR l_stmt;
677 FETCH l_cursor BULK COLLECT INTO l_t_tbl, l_col_tbl;
678 CLOSE l_cursor;
679
680 build_stmt(p_t_tbl => l_t_tbl, p_col_tbl => l_col_tbl);
681
682 FOR j IN g_sel_tbl.first .. g_sel_tbl.last LOOP
683 l_stmt := g_sel_tbl(j)||' FROM '||p_sys_opt_tbl;
684 g_dyn_head := g_hdr_tbl(j);
685
686 exec_sql (p_table => p_sys_opt_tbl,
687 p_sel_clause => NULL,
688 p_stmt => l_stmt,
689 p_schema => 'NONE');
690 END LOOP;
691 end if;
692 log(l_calling_fn, 'Done with profiles');
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 log(l_calling_fn, 'Error');
697 raise;
698
699 END get_system_options;
700 --
701 FUNCTION get_param (p_numcol IN number,
702 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return VARCHAR2 IS
703
704 l_pclause VARCHAR2(500) := ' WHERE ';
705 l_sclause VARCHAR2(500);
706 l_cnt NUMBER :=0;
707
708 l_calling_fn VARCHAR2(40) := 'fa_asset_trace_pvt.get_param';
709
710 BEGIN
711
712 for i in g_param_tbl.first .. g_param_tbl.last loop
713 l_cnt := l_cnt + 1;
714 if (l_cnt = 1) then
715 if (g_param_tbl(i).cValue is null) and (g_param_tbl(i).nValue is not null) then
716 l_pclause := l_pclause || g_param_tbl(i).param_column || ' = ' ||g_param_tbl(i).nValue;
717 elsif (g_param_tbl(i).nValue is null) and (g_param_tbl(i).cValue is not null) then
718 l_pclause := l_pclause || g_param_tbl(i).param_column || ' = ' ||''''||g_param_tbl(i).cValue||'''';
719 end if;
720 else
721 if (g_param_tbl(i).cValue is null) and (g_param_tbl(i).nValue is not null) then
722 l_sclause := l_sclause || g_param_tbl(i).param_column || ' = ' ||g_param_tbl(i).nValue;
723 elsif (g_param_tbl(i).nValue is null) and (g_param_tbl(i).cValue is not null) then
724 l_sclause := l_sclause || g_param_tbl(i).param_column || ' = ' ||''''||g_param_tbl(i).cValue||'''';
725 end if;
726 end if;
727 end loop;
728
729 if p_numcol <> -99 then
730 if p_numcol = 0 then --Table only has primary column
731 return l_pclause;
732 elsif p_numcol = 2 then --Table has both primary and secondary columns
733 return l_pclause ||' AND '|| l_sclause;
734 else --Table only has secondary column
735 return ' WHERE '||l_sclause;
736 end if;
737 else
738 return '';
739 end if;
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 log(l_calling_fn,'Error');
744 log(l_calling_fn,'l_pclause is: '||l_pclause);
745 log(l_calling_fn,'l_sclause is: '||l_sclause);
746 raise;
747
748 END get_param;
749 --
750 --parse the desired colum order for tables in options tbl.
751 --
752 PROCEDURE col_order (x_table IN VARCHAR2,
753 x_col_tbl IN OUT NOCOPY FA_ASSET_TRACE_PKG.t_col_tbl,
754 x_t_tbl IN OUT NOCOPY FA_ASSET_TRACE_PKG.t_col_tbl,
755 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
756
757 l_ord_list varchar2(2000);
758 l_parsed_str varchar2(100);
759 l_counter number:=0;
760 l_idx NUMBER:=0;
761 l_col_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
762 l_t_tbl FA_ASSET_TRACE_PKG.t_col_tbl;
763 l_found BOOLEAN :=FALSE;
764
765 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.col_order';
766
767 BEGIN
768
769 l_col_tbl := g_tmpc_tbl;
770 l_t_tbl := g_tmpc_tbl;
771
772 FOR i IN g_options_tbl.FIRST .. g_options_tbl.LAST LOOP
773 IF g_options_tbl(i).l_tbl = x_table THEN
774 IF g_options_tbl(i).l_col_order IS NOT NULL THEN
775 -- log(l_calling_fn,'x_table: '||x_table);
776 l_ord_list :=g_options_tbl(i).l_col_order;
777 -- log(l_calling_fn,'l_ord_list: '||l_ord_list);
778 l_found := TRUE;
779 END IF;
780 EXIT;
781 END IF;
782 END LOOP;
783
784 IF l_found THEN
785 WHILE (TRUE) LOOP
786 l_parsed_str := substr(l_ord_list,1,instr(l_ord_list,',')-1);
787 l_counter := l_counter+1;
788 IF (instr(l_ord_list,',')=0 or l_parsed_str IS NULL) THEN
789 l_col_tbl(l_counter):=l_ord_list;
790 l_t_tbl(l_counter):=x_table;
791 EXIT;
792 ELSE
793 l_col_tbl(l_counter):=l_parsed_str;
794 l_t_tbl(l_counter):=x_table;
795 l_ord_list := substr(l_ord_list,instr(l_ord_list,',')+1,length(l_ord_list));
796 END IF;
797 END LOOP;
798
799 l_idx :=x_col_tbl.count+1;
800 FOR j IN l_col_tbl.first .. l_col_tbl.last LOOP
801 x_col_tbl(l_idx) := l_col_tbl(j);
802 x_t_tbl(l_idx) := l_t_tbl(j);
803 l_idx := l_idx+1;
804 END LOOP;
805 END IF;
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 log(l_calling_fn, 'Error');
810 raise;
811
812 END col_order;
813 --
814 -- Used to check if the table contains data.
815 -- If not, then no need to build and run statements for it.
816 --
817 FUNCTION get_tbl_cnt (p_table IN VARCHAR2,
818 p_stmt IN VARCHAR2,
819 p_schema IN VARCHAR2,
820 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
821
822 l_sql_stmt VARCHAR2(4000);
823 l_stmt VARCHAR2(4000);
824 l_clause VARCHAR2(500);
825 l_count number;
826 l_chk_col number;
827
828 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.get_tbl_cnt';
829
830 BEGIN
831
832 IF (p_schema <> 'NONE') and (p_table is not null) THEN
833 l_chk_col := check_column (p_table, p_schema);
834 END IF;
835
836 l_sql_stmt := ' SELECT count(*) FROM ' || p_table || get_param(l_chk_col);
837
838 l_stmt := NVL(p_stmt, l_sql_stmt);
839 if g_print_debug then
840 log(l_calling_fn, l_stmt);
841 end if;
842
843 EXECUTE IMMEDIATE l_stmt INTO l_count;
844
845 IF l_count > 0 THEN
846 RETURN TRUE;
847 ELSE
848 RETURN FALSE;
849 END IF;
850
851 EXCEPTION WHEN OTHERS THEN
852 log(l_calling_fn, 'Error');
853 raise;
854
855 END get_tbl_cnt;
856
857 --Checks whether primary or secondary driving columns exist in the table being processed.
858
859 FUNCTION check_column (p_table IN VARCHAR2,
860 p_schema IN VARCHAR2 DEFAULT 'FA',
861 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN NUMBER IS
862
863 l_primary NUMBER:=0;
864 l_secondary NUMBER;
865 l_both NUMBER;
866 l_primary_column VARCHAR2(40);
867 l_secondary_column VARCHAR2(40);
868 l_stmt VARCHAR2(4000);
869
870 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.check_column';
871
872 BEGIN
873
874 FOR i IN g_param_tbl.first .. g_param_tbl.last LOOP
875 l_primary_column := NVL(l_primary_column, g_param_tbl(i).param_column);
876 l_secondary_column := g_param_tbl(i).param_column;
877 END LOOP;
878
879 IF p_schema <> 'NONE' THEN
880 SELECT count(*)
881 INTO l_both
882 FROM dba_tab_columns
883 WHERE column_name IN (l_primary_column,l_secondary_column)
884 AND owner = p_schema
885 AND table_name = p_table;
886 END IF;
887
888 IF l_both > 0 THEN
889 IF l_both = 2 THEN
890 RETURN l_both;
891 ELSE
892
893 SELECT count(*)
894 INTO l_secondary
895 FROM dba_tab_columns
896 WHERE column_name = l_secondary_column
897 AND owner = p_schema
898 AND table_name = p_table;
899
900 IF l_secondary = 1 THEN
901 RETURN l_secondary;
902 ELSIF l_secondary = 0 THEN
903 RETURN l_primary;
904 END IF;
905 END IF;
906 ELSE
907 Return -99; -- could be some setup table which has neither columns
908 log(l_calling_fn,'table '|| p_table ||' is not in schema');
909 END IF;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 log(l_calling_fn, 'Error');
914 raise;
915
916 END check_column;
917 --
918 --
919 FUNCTION fafsc (p_col IN VARCHAR2,
920 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN VARCHAR2 IS
921
922 l_out VARCHAR2(500);
923
924 BEGIN
925 IF p_col IS NOT NULL THEN
926 l_out := ''''||'<TD bgcolor="#f2f2f5">'||''''||'||'||p_col||'||'||''''||'</TD>'||''''||'||''''||';
927 RETURN l_out;
928 ELSE
929 RETURN NULL;
930 END IF;
931
932 END fafsc;
933 --
934 -- Used to parse the column headers for html output.
935 --
936 FUNCTION fparse_header(p_in_str IN VARCHAR2,
937 p_add_html IN VARCHAR2 DEFAULT 'Y',
938 p_break_size IN NUMBER DEFAULT 14,
939 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN VARCHAR2 IS
940
941 l_start NUMBER:= 1;
942 l_prior_start NUMBER:= 0;
943 l_out_string VARCHAR2(500);
944 l_parsed_str VARCHAR2(500);
945 l_checkStr VARCHAR2(1);
946 l_flag VARCHAR2(1);
947 l_first_run BOOLEAN := TRUE;
948 l_break_size NUMBER := p_break_size; -- Maximum break size.
949
950 l_calling_fn varchar2(40) := 'fa_asset_trace_pvt.fparse_header';
951
952
953 BEGIN
954
955 IF (length(p_in_str) <= l_break_size) THEN
956 if p_add_html = 'N' then
957 l_out_string := p_in_str;
958 else
959 l_out_string :='<TD bgcolor="#cfe0f1"><p align="center"><font color="#343434"><b>'||p_in_str||'</b></font></p></TD>';
960 end if;
961 return (l_out_string);
962 ELSIF (p_in_str IS NOT NULL ) THEN
963
964 IF instr(p_in_str,'_')> l_break_size THEN
965 l_break_size := instr(p_in_str,'_',l_break_size)-1;
966 END IF;
967
968 WHILE (TRUE) LOOP
969 -- we have 3 cases:
970 -- 1. we are passing a truncated string ==> 'T'
971 -- 2. we are passing a meaningful string like 'HEAD_OUT_ID' ==> 'M'
972 -- 3. we are passing the last string ==> 'E'
973
974 l_parsed_str := SUBSTR(p_in_str, l_start, l_break_size);
975 l_checkStr := SUBSTR(p_in_str, l_start + l_break_size, 1);
976
977 IF (l_checkStr = '_') THEN -- 'M'
978 l_out_string := l_out_string || l_parsed_str ||'<BR>';
979 l_start := l_start + length(l_parsed_str) +1;
980 ELSIF (l_checkStr IS NOT NULL) THEN -- 'T'
981 l_out_string := l_out_string || substr(l_parsed_str, 1, instr(l_parsed_str,'_', -1) -1) ||'<BR>';
982 l_start := l_start + length( substr(l_parsed_str, 1, instr(l_parsed_str,'_', -1) -1) ) +1 ;
983 ELSE -- 'E'
984 l_out_string := l_out_string || l_parsed_str;
985 EXIT;
986 END IF;
987
988 END LOOP;
989 if p_add_html = 'N' then
990 l_out_string := l_out_string;
991 else
992 l_out_string :='<TD bgcolor="#cfe0f1"><p align="center"><font color="#343434"><b>'||l_out_string||'</b></font></p></TD>';
993 end if;
994 RETURN (l_out_string);
995
996 ELSE
997 RETURN ('No strings to process');
998 END IF;
999
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 log(l_calling_fn, 'Error');
1003 raise;
1004
1005 END fparse_header;
1006 --
1007 PROCEDURE build_anchors (p_t_tbl IN FA_ASSET_TRACE_PKG.t_col_tbl,
1008 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1009
1010 l_t_tbl FA_ASSET_TRACE_PKG.t_col_tbl := p_t_tbl;
1011 l_anchor VARCHAR2(32767);
1012 l_tbl VARCHAR2(150);
1013 l_count number:=0;
1014
1015 l_calling_fn varchar2(35) := 'fa_asset_trace_pvt.build_anchors';
1016
1017 BEGIN
1018
1019 l_anchor :='<TABLE BORDER=0 width="93%" cellpadding="5" BORDERCOLOR="#c9cbd3"><TR><TD COLSPAN=7 BGCOLOR="#3a5a87"><font color="#ffffff"
1020 face=arial><A NAME=ANCHORS></A><B>Table Anchors</B></TD></TR><TR>';
1021
1022 FOR i IN l_t_tbl.first .. l_t_tbl.last LOOP
1023 l_count:=l_count+1;
1024 l_tbl := fparse_header(l_t_tbl(i),'N');
1025 --l_tbl := l_t_tbl(i);
1026 IF (l_count = 7) OR (i = l_t_tbl.last) THEN
1027 l_anchor:=l_anchor||'<TD bgcolor="#f2f2f5"><A HREF="#'||l_t_tbl(i)||'"><font color="#147590">'||l_tbl||'</font></A></TD></TR><TR>';
1028 l_count:=0;
1029 ELSE
1030 l_anchor:=l_anchor||'<TD bgcolor="#f2f2f5"><A HREF="#'||l_t_tbl(i)||'"><font color="#147590">'||l_tbl||'</font></A></TD>';
1031 END IF;
1032 END LOOP;
1033
1034 g_anchor := l_anchor;
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 log(l_calling_fn, 'Error');
1039 log(l_calling_fn, l_tbl);
1040 raise;
1041
1042 END build_anchors;
1043 --
1044 -- writes output to out file.
1045 --
1046 PROCEDURE save_output(p_calling_prog IN VARCHAR2,
1047 p_use_utl_file IN VARCHAR2,
1048 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1049
1050 utl_file_dir VARCHAR2(2000);
1051 outfile UTL_FILE.FILE_TYPE;
1052 l_filename VARCHAR2(30);
1053 l_position NUMBER(10);
1054 l_length NUMBER(10);
1055 l_cursor c_stmt;
1056 l_no_rec VARCHAR2(10000);
1057 l_stmt VARCHAR2(2000);
1058 l_header VARCHAR2(2000);
1059 l_output VARCHAR2(32767);
1060 l_title VARCHAR2(100);
1061 l_tmp_tbl VARCHAR2(100):='NOTHING';
1062 l_app_version fnd_product_groups.release_name%type;
1063 l_primary_col VARCHAR2(40);
1064 l_sec_col VARCHAR2(40);
1065
1066 l_cnt number;
1067
1068 l_calling_fn varchar2(80) := 'fa_asset_trace_pvt.save_output';
1069
1070 BEGIN
1071 log(l_calling_fn, 'Entered save_output');
1072
1073 l_no_rec :='<TABLE BORDER=0 width="93%" cellpadding="5" BORDERCOLOR="#c9cbd3"><TR><TD BGCOLOR="#3a5a87"><font color="#ffffff" face=arial>';
1074
1075 l_cnt := g_param_tbl.first;
1076 --check just in case we messed up somehow
1077 if g_param_tbl.exists(l_cnt) then
1078 l_primary_col := to_char(NVL(g_param_tbl(l_cnt).cValue, g_param_tbl(g_param_tbl.first).nValue));
1079 end if;
1080 l_cnt := l_cnt + 1;
1081 if g_param_tbl.exists(l_cnt) then
1082 l_sec_col := to_char(NVL(g_param_tbl(l_cnt).cValue, g_param_tbl(g_param_tbl.first).nValue));
1083 end if;
1084
1085 l_title := nvl(FND_GLOBAL.APPLICATION_NAME, 'Assets')||': '||p_calling_prog;
1086 --margin-left: 5%; margin-right: 5%; font-style: normal
1087 l_header := '<html><head><title>'||nvl(FND_GLOBAL.APPLICATION_SHORT_NAME,'FA ')||'- Trace Utility </title>
1088 <STYLE TYPE="text/css"> TD {font-size: 8pt; font-family: arial; font-style: normal} </STYLE></head>
1089 <body bgcolor="#ffffff"><H2><b><font color="#3a5a87">'||l_title||'</font></b></H2><BR>
1090 <table border="0" width="93%" cellpadding="5" bordercolor="#c9cbd3">';
1091
1092 if (p_use_utl_file = 'Y') then
1093 if (substr(p_calling_prog,1,7)='FATRACE') then
1094 --this is being called by the sla subrequest
1095 --if p_calling_prog is changed, change above condition
1096 l_filename := 'GTUSLA_'||replace(ltrim(rtrim(l_primary_col)),' ','_') || '_' || replace(l_sec_col,' ','_') ||'.html';
1097 log(l_calling_fn, 'SLA output file: '||l_filename);
1098 else
1099 l_filename := 'GTU_'||replace(ltrim(rtrim(l_primary_col)),' ','_') || '_' || replace(l_sec_col,' ','_') ||'.html';
1100 end if;
1101
1102 ocfile (g_outfile, l_filename,'O');
1103 end if;
1104
1105 l_header := l_header||g_temp_head; --add banner area content.
1106
1107 if (p_use_utl_file = 'Y') then
1108 UTL_FILE.PUT_LINE(g_outfile, l_header);
1109 else
1110 fnd_file.put(FND_FILE.OUTPUT, l_header);
1111 end if;
1112
1113 build_anchors(g_anchor_tbl);
1114
1115 l_cnt := g_output_tbl.first + 1; --do this to control the spot for table anchors
1116
1117 if (p_use_utl_file = 'Y') then
1118 UTL_FILE.PUT_LINE(g_outfile,g_anchor);
1119 FOR i IN g_output_tbl.first .. g_output_tbl.last LOOP
1120 UTL_FILE.PUT_LINE(g_outfile,g_output_tbl(i));
1121 --UTL_FILE.NEW_LINE(g_outfile,1);
1122 END LOOP;
1123 else
1124 fnd_file.put(FND_FILE.OUTPUT,g_anchor);
1125 FOR i IN g_output_tbl.first .. g_output_tbl.last LOOP
1126 fnd_file.put(FND_FILE.OUTPUT,g_output_tbl(i));
1127 fnd_file.new_line(FND_FILE.OUTPUT,1);
1128 END LOOP;
1129 end if;
1130
1131 l_no_rec := l_no_rec ||'<B>The following table(s) contain no data for this asset:</B></TD></TR>';
1132 IF g_no_rec_tbl.count > 0 THEN
1133 FOR i IN g_no_rec_tbl.first .. g_no_rec_tbl.last LOOP
1134 IF g_no_rec_tbl(i) <> l_tmp_tbl THEN
1135 l_no_rec := l_no_rec ||'<TR><TD bgcolor="#f2f2f5"><font color="#ed1c24">'||g_no_rec_tbl(i)||'</font></TD></TR><TR>';
1136 l_tmp_tbl:=g_no_rec_tbl(i);
1137 END IF;
1138 END LOOP;
1139 l_no_rec := substr(l_no_rec, 1, length(l_no_rec) -4)||'</TABLE>';
1140 if p_use_utl_file = 'Y' then
1141 UTL_FILE.PUT_LINE(g_outfile,l_no_rec);
1142 else
1143 fnd_file.put(FND_FILE.OUTPUT,l_no_rec);
1144 end if;
1145
1146 END IF;
1147
1148 if (p_use_utl_file = 'Y') then
1149 UTL_FILE.PUT_LINE(g_outfile,' </body> </html> ');
1150 log(l_calling_fn, 'Trying to close out file');
1151 ocfile (g_outfile, null,'C');
1152 --g_use_utl_file := 'N'; /* have no clue why resetting this generates an error; so, leaving alone for now. */
1153 else
1154 fnd_file.put(FND_FILE.OUTPUT,' </body> </html> ');
1155 end if;
1156 g_output_tbl.delete; g_no_rec_tbl.delete;
1157 log(l_calling_fn, 'Successfully leaving save_output');
1158
1159 EXCEPTION
1160 WHEN UTL_FILE.WRITE_ERROR then
1161 -- RAISE_APPLICATION_ERROR(-20104,'Write Error');
1162 log(l_calling_fn, 'Write Error.');
1163 WHEN UTL_FILE.READ_ERROR then
1164 --RAISE_APPLICATION_ERROR(-20105,'Read Error');
1165 log(l_calling_fn, 'Read Error.');
1166 WHEN UTL_FILE.INTERNAL_ERROR then
1167 --RAISE_APPLICATION_ERROR(-20106,'Internal Error');
1168 log(l_calling_fn, 'Internal Error.');
1169 WHEN OTHERS THEN
1170 log(l_calling_fn, 'Unexpected error.');
1171 raise;
1172
1173 END save_output;
1174 --
1175 --Message logging routine
1176 --
1177 PROCEDURE log(p_calling_fn IN VARCHAR2,
1178 p_msg IN VARCHAR2 default null,
1179 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1180
1181 l_module varchar2(150);
1182
1183 BEGIN
1184
1185 if ((g_use_utl_file = 'Y') and (utl_file.is_open(g_logfile) = TRUE)) then
1186 UTL_FILE.PUT_LINE(g_logfile,p_calling_fn|| ': '||p_msg);
1187 else
1188 Fnd_File.Put_Line (Fnd_File.Log, p_calling_fn|| ': '||p_msg);
1189 end if;
1190 --
1191 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1192 l_module := FND_GLOBAL.APPLICATION_SHORT_NAME||'.PLSQL.'||p_calling_fn;
1193 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, p_msg);
1194 END IF;
1195
1196 EXCEPTION
1197 when utl_file.invalid_path then
1198 ocfile (g_logfile, null,'C');
1199 when utl_file.write_error then
1200 ocfile (g_logfile, null,'C');
1201 when utl_file.invalid_operation then
1202 ocfile (g_logfile, null,'C');
1203 when others then
1204 ocfile (g_logfile, null,'C');
1205
1206 END log;
1207 --
1208 PROCEDURE ocfile (p_handle IN OUT NOCOPY utl_file.file_type,
1209 p_file IN VARCHAR2,
1210 p_mode IN VARCHAR2) IS
1211
1212 l_calling_fn varchar2(80) := 'fa_asset_trace_pvt.ocfile';
1213 BEGIN
1214
1215 if (p_mode = 'C') then
1216 if (utl_file.is_open(p_handle) = TRUE) then
1217 log(l_calling_fn, 'About to close file');
1218 utl_file.fclose(p_handle);
1219 end if;
1220 elsif (p_mode = 'O') then
1221 if (utl_file.is_open(p_handle) = FALSE) then
1222 p_handle := UTL_FILE.FOPEN(location => 'GTU_DIR',
1223 filename => p_file,
1224 open_mode => 'w',
1225 max_linesize =>32767);
1226 end if;
1227 end if;
1228 EXCEPTION
1229 WHEN UTL_FILE.INVALID_PATH THEN
1230 --RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
1231 log(l_calling_fn, 'Invalid Path.');
1232 WHEN UTL_FILE.INVALID_MODE THEN
1233 --RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
1234 log(l_calling_fn, 'Invalid Mode.');
1235 WHEN UTL_FILE.INVALID_OPERATION then
1236 --RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
1237 log(l_calling_fn, 'Invalid Operation.');
1238 WHEN UTL_FILE.INVALID_FILEHANDLE then
1239 --RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
1240 log(l_calling_fn, 'Invalid Filehandle.');
1241 WHEN UTL_FILE.INTERNAL_ERROR then
1242 --RAISE_APPLICATION_ERROR(-20106,'Internal Error');
1243 log(l_calling_fn, 'Internal Error.');
1244 WHEN OTHERS THEN
1245 log(l_calling_fn, 'Unexpected error in file operation.');
1246 raise;
1247 END ocfile;
1248 --
1249
1250 END FA_ASSET_TRACE_PVT;