1 PACKAGE BODY EDW_LOG AS
2 /* $Header: EDWSRLGB.pls 120.1 2005/06/13 00:31:32 amitgupt noship $ */
3 VERSION CONSTANT CHAR(80) := '$Header: EDWSRLGB.pls 120.1 2005/06/13 00:31:32 amitgupt noship $';
4
5
6 -- ------------------------------------------------------------------
7 -- Name: put_names
8 -- Desc: Setup which directory to put the log and what the log file
9 -- name is. The directory setup is used only if the program
10 -- is not run thru concurrent manager
11 -- -----------------------------------------------------------------
12 PROCEDURE put_names(
13 p_log_file VARCHAR2,
14 p_out_file VARCHAR2,
15 p_directory VARCHAR2) IS
16 BEGIN
17 /*
18 logic used is :
19 if edw_debug is set to yes, we need detailed file logging.
20 if edw_debug is set to no, but the fnd profile AFLOG_ENABLED is 'Y' and
21 AFLOG_LEVEL is at statement level, then also we need g_debug to be true.
22 when a user says that AFLOG_LEVEL is at statement level, they are expecting
23 detailed logging.
24 scenario:
25 A user may set the AFLOG level to statement and may not set the EDW_DEBUG flag
26 to Yes. In this case, we need to start the detailed logging.
27 we need to have two separate entities, edw_debug and then fnd since we
28 want to keep separate the file logging and the fnd logging
29 */
30 --do not directly access AFLOG_ENABLED and AFLOG_LEVEL
31 IF (fnd_profile.value('EDW_DEBUG') = 'Y') or FND_LOG.G_CURRENT_RUNTIME_LEVEL=FND_LOG.LEVEL_STATEMENT then
32 g_debug := true;
33 ELSE
34 g_debug := false;
35 END IF;
36 FND_FILE.PUT_NAMES(p_log_file,p_out_file,p_directory);
37 g_version_GT_1159:=is_oracle_apps_GT_1159;
38 END put_names;
39
40
41 -- ------------------------------------------------------------------
42 -- Name: print_duration
43 -- Desc: Given a duration in days, it return the dates in
44 -- a more readable format: x days HH:MM:SS
45 -- -----------------------------------------------------------------
46 FUNCTION duration(
47 p_duration number) return VARCHAR2 IS
48 BEGIN
49 return(to_char(floor(p_duration)) ||' Days '||
50 to_char(mod(floor(p_duration*24), 24))||':'||
51 to_char(mod(floor(p_duration*24*60), 60))||':'||
52 to_char(mod(floor(p_duration*24*60*60), 60)));
53 END duration;
54
55 -- ------------------------------------------------------------------
56 -- Name: debug_line
57 -- Desc: If debug flag is turned on, the log will be printed
58 -- -----------------------------------------------------------------
59 PROCEDURE debug_line(
60 p_text VARCHAR2) IS
61 BEGIN
62 IF (g_debug) THEN
63 put_line(p_text,FND_LOG.LEVEL_STATEMENT);
64 END IF;
65 END debug_line;
66
67 procedure put_conc_log(p_text varchar2) is
68 l_len number;
69 l_start number:=1;
70 l_end number:=1;
71 last_reached boolean:=false;
72 Begin
73 if p_text is null or p_text='' then
74 return;
75 end if;
76 l_len:=nvl(length(p_text),0);
77 if l_len <=0 then
78 return;
79 end if;
80 while true loop
81 l_end:=l_start+250;
82 if l_end >= l_len then
83 l_end:=l_len;
84 last_reached:=true;
85 end if;
86 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_text, l_start, 250));
87 l_start:=l_start+250;
88 if last_reached then
89 exit;
90 end if;
91 end loop;
92 Exception when others then
93 null;
94 End;
95
96 -- ------------------------------------------------------------------
97 -- Name: put_line
98 -- Desc: For now, just a wrapper on top of fnd_file
99 -- -----------------------------------------------------------------
100 PROCEDURE put_line(
101 p_text VARCHAR2
102 ) IS
103 BEGIN
104 put_line(p_text,FND_LOG.LEVEL_PROCEDURE);
105 END put_line;
106 ----------------------
107 PROCEDURE put_line(
108 p_text VARCHAR2,
109 p_severity number
110 ) IS
111 Begin
112 put_conc_log(p_text);
113 if p_severity>=FND_LOG.G_CURRENT_RUNTIME_LEVEL and g_version_GT_1159 then --this is for perf
114 put_fnd_log(p_text,p_severity);
115 end if;
116 Exception when others then
117 null;
118 End;
119
120 procedure put_fnd_log(p_text varchar2,p_severity number) is
121 l_len number;
122 l_start number:=1;
123 l_end number:=1;
124 last_reached boolean:=false;
125 Begin
126 if p_text is null or p_text='' then
127 return;
128 end if;
129 l_len:=nvl(length(p_text),0);
130 if l_len <=0 then
131 return;
132 end if;
133 if g_fnd_log_module is null then
134 g_fnd_log_module:='bis.edw.collection';
135 end if;
136 while true loop
137 l_end:=l_start+3990;
138 if l_end>=l_len then
139 last_reached:=true;
140 end if;
141 --check added to supress GSCC warning
142 if(p_severity >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
143 FND_LOG.STRING(p_severity,g_fnd_log_module,substr(p_text, l_start,3990));
144 end if;
145 l_start:=l_start+3990;
146 if last_reached then
147 exit;
148 end if;
149 end loop;
150 Exception when others then
151 put_conc_log('Error in put_fnd_log '||sqlerrm);
152 null;
153 End;
154
155 function is_oracle_apps_GT_1159 return boolean is
156 l_list varcharTableType;
157 l_number_list number;
158 l_version varchar2(200);
159 l_version_GT_1159 boolean;
160 Begin
161 l_version:=get_app_version;
162 if l_version is null then
163 l_version_GT_1159:=false;
164 return l_version_GT_1159;
165 end if;
166 if parse_names(l_version,'.',l_list,l_number_list)=false then
167 return false;
168 end if;
169 if to_number(l_list(1))>11 then
170 l_version_GT_1159:=true;
171 elsif to_number(l_list(2))>5 then
172 l_version_GT_1159:=true;
173 elsif to_number(l_list(3))>9 then
174 l_version_GT_1159:=true;
175 else
176 l_version_GT_1159:=false;
177 end if;
178 if l_version_GT_1159 then
179 put_conc_log('Oracle Apps version > 11.5.9');
180 else
181 put_conc_log('Oracle Apps version NOT > 11.5.9');
182 end if;
183 return l_version_GT_1159;
184 Exception when others then
185 put_conc_log('Error in is_oracle_apps_GT_1159 '||sqlerrm);
186 return false;
187 End;
188
189 function get_app_version return varchar2 is
190 cursor c1 is select release_name from fnd_product_groups;
191 l_version varchar2(200);
192 Begin
193 open c1;
194 fetch c1 into l_version;
195 close c1;
196 put_conc_log('Oracle Apps Version '||l_version);
197 return l_version;
198 Exception when others then
199 put_conc_log('Error in get_app_version '||sqlerrm);
200 return null;
201 End;
202
203 function parse_names(
204 p_list varchar2,
205 p_separator varchar2,
206 p_names out NOCOPY varcharTableType,
207 p_number_names out NOCOPY number)
208 return boolean is
209 l_start number;
210 l_end number;
211 l_len number;
212 Begin
213 p_number_names:=0;
214 if p_list is null then
215 return true;
216 end if;
217 l_len:=length(p_list);
218 if l_len<=0 then
219 return true;
220 end if;
221 if instr(p_list,p_separator)=0 then
222 p_number_names:=1;
223 p_names(p_number_names):=ltrim(rtrim(p_list));
224 return true;
225 end if;
226 l_start:=1;
227 loop
228 l_end:=instr(p_list,p_separator,l_start);
229 if l_end=0 then
230 l_end:=l_len+1;
231 end if;
232 p_number_names:=p_number_names+1;
233 p_names(p_number_names):=ltrim(rtrim(substr(p_list,l_start,(l_end-l_start))));
234 l_start:=l_end+1;
235 if l_end>=l_len then
236 exit;
237 end if;
238 end loop;
239 return true;
240 Exception when others then
241 put_conc_log('Error in parse_names '||sqlerrm);
242 return false;
243 End;
244
245 End;