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