[Home] [Help]
PACKAGE BODY: APPS.HRI_UTIL
Source
1 PACKAGE BODY HRI_UTIL AS
2 /* $Header: HRIUTILB.pls 120.0 2011/11/18 10:01:25 vepravee noship $ */
3 VERSION CONSTANT CHAR(80) := '$Header: HRIUTILB.pls 120.0 2011/11/18 10:01:25 vepravee noship $';
4
5
6 -- ------------------------
7 -- Global Variables
8 -- ------------------------
9 g_debug BOOLEAN := FALSE;
10 g_timer_start DATE := NULL;
11 g_duration NUMBER := NULL;
12
13 PROCEDURE initialize(
14 p_log_file VARCHAR2,
15 p_out_file VARCHAR2,
16 p_directory VARCHAR2,
17 p_obj_name VARCHAR2 DEFAULT 'HRI') IS
18 BEGIN
19 IF (fnd_profile.value('HRI_DEBUG_MODE') = 'Y') THEN
20 g_debug := TRUE;
21 ELSE
22 g_debug := FALSE;
23 END IF;
24
25 g_obj_name := p_obj_name;
26
27 FND_FILE.PUT_NAMES(
28 p_log_file,
29 p_out_file,
30 NVL(p_directory, fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY')));
31 END initialize;
32
33
34 PROCEDURE put_timestamp(
35 p_text VARCHAR2) IS
36 BEGIN
37 put_line(p_text||' - '||to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
38 END put_timestamp;
39
40
41
42 PROCEDURE start_timer IS
43 BEGIN
44 g_duration := 0;
45 g_timer_start := sysdate;
46 END start_timer;
47
48
49
50 PROCEDURE stop_timer IS
51 BEGIN
52
53 IF g_timer_start IS NULL THEN
54 g_duration := 0;
55 ELSE
56 g_duration := sysdate - g_timer_start;
57 END IF;
58 g_timer_start := NULL;
59
60 END stop_timer;
61
62
63 PROCEDURE print_timer(
64 p_text VARCHAR2)
65 IS
66 l_duration NUMBER := NULL;
67 BEGIN
68 IF (g_timer_start IS NOT NULL) THEN
69 l_duration := sysdate - g_timer_start;
70 ELSE
71 l_duration := g_duration;
72 END IF;
73
74 IF (l_duration IS NOT NULL) THEN
75 put_line(p_text||' - '||
76 to_char(floor(l_duration)) ||' Days '||
77 to_char(mod(floor(l_duration*24), 24))||':'||
78 to_char(mod(floor(l_duration*24*60), 60))||':'||
79 to_char(mod(floor(l_duration*24*60*60), 60)));
80 END IF;
81
82 END print_timer;
83
84
85
86 PROCEDURE debug_line(
87 p_text VARCHAR2) IS
88 BEGIN
89 IF (g_debug) THEN
90 put_line(p_text);
91 END IF;
92 END debug_line;
93
94
95
96 PROCEDURE put_line (p_text VARCHAR2) IS
97
98 l_len number(10);
99 l_start number(10) :=1;
100 l_end number(10) :=1;
101 last_reached boolean:=false;
102
103 BEGIN
104
105 if p_text is null or p_text='' then
106 return;
107 end if;
108 l_len:=nvl(length(p_text),0);
109 if l_len <=0 then
110 return;
111 end if;
112
113 while true loop
114 l_end:=l_start+150;
115 if l_end >= l_len then
116 l_end:=l_len;
117 last_reached:=true;
118 end if;
119 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_text, l_start, 150));
120 l_start:=l_start+150;
121 if last_reached then
122 exit;
123 end if;
124 end loop;
125
126 ----------------------------------------------------------------------------------
127 -- We need to comment out this call for now, as it impacts the Incremental Update
128 -- for GL Base Summary which uses subworker (the parent request hangs after child
129 -- requests are done). We will find out the reason and make necessary fix for 6.0G
130 ----------------------------------------------------------------------------------
131 --** FND_LOG.String(6, g_obj_name, p_text);
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 NULL;
136 END put_line;
137
138
139
140
141 FUNCTION get_schema_name(
142 p_app_short_name in varchar2 default 'HRI'
143 ) return varchar2 IS
144 l_status varchar2(512);
145 l_industry varchar2(512);
146 l_schema varchar2(512);
147 BEGIN
148 if( fnd_installation.get_app_info( p_app_short_name,
149 l_status, l_industry, l_schema ) )
150 then
151 return l_schema;
152 else
153 return null;
154 end if;
155 END get_schema_name;
156
157
158 FUNCTION get_apps_schema_name RETURN VARCHAR2 IS
159
160 l_apps_schema_name VARCHAR2(30);
161
162 CURSOR c_apps_schema_name IS
163 SELECT oracle_username
164 FROM fnd_oracle_userid WHERE oracle_id
165 BETWEEN 900 AND 999 AND read_only_flag = 'U';
166 BEGIN
167
168 OPEN c_apps_schema_name;
169 FETCH c_apps_schema_name INTO l_apps_schema_name;
170 CLOSE c_apps_schema_name;
171 RETURN l_apps_schema_name;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETURN NULL;
176 END get_apps_schema_name;
177
178
179
180 PROCEDURE truncate_table(
181 p_table_name in varchar2,
182 p_app_short_name in varchar2 default 'HRI',
183 p_retcode out nocopy varchar2
184 ) IS
185 l_schema varchar2(512);
186 l_stmt varchar2(1024);
187 l_debug_flag varchar2(1);
188 bad_schema_ex exception;
189 BEGIN
190 p_retcode := 0;
191 l_debug_flag := nvl( fnd_profile.value( 'HRI_DEBUG_MODE' ), 'N' );
192 l_schema := get_schema_name( p_app_short_name );
193 if( l_schema = null )
194 then
195 raise bad_schema_ex;
196 else
197 l_stmt := 'truncate table ' || l_schema || '.' || p_table_name;
198 execute immediate l_stmt;
199 end if;
200 EXCEPTION
201 when bad_schema_ex then
202 if l_debug_flag = 'Y' then
203 put_line( 'truncate_table : bad schema' );
204 end if;
205 p_retcode := -1;
206 when others then
207 if l_debug_flag = 'Y' then
208 put_line( 'truncate_table : ' || sqlcode || ' ' || sqlerrm );
209 end if;
210 p_retcode := -1;
211 END truncate_table;
212
213 PROCEDURE drop_table(
214 p_table_name in varchar2,
215 p_app_short_name in varchar2 default 'HRI',
216 p_retcode out nocopy varchar2
217 ) IS
218 l_schema varchar2(512);
219 l_stmt varchar2(1024);
220 l_debug_flag varchar2(1);
221 bad_schema_ex exception;
222 BEGIN
223 p_retcode := 0;
224 l_debug_flag := nvl( fnd_profile.value( 'HRI_DEBUG_MODE' ), 'N' );
225 l_schema := get_schema_name( p_app_short_name );
226 if( l_schema = null )
227 then
228 raise bad_schema_ex;
229 else
230 l_stmt := 'drop table ' || l_schema || '.' || p_table_name;
231 execute immediate l_stmt;
232 end if;
233 EXCEPTION
234 when bad_schema_ex then
235 if l_debug_flag = 'Y' then
236 put_line( 'drop_table : bad schema' );
237 end if;
238 p_retcode := -1;
239 when others then
240 if l_debug_flag = 'Y' then
241 put_line( 'drop_table : ' || sqlcode || ' ' || sqlerrm );
242 end if;
243 p_retcode := -1;
244 END drop_table;
245
246
247 Function get_Utl_File_Dir return VARCHAR2 IS
248 l_dir VARCHAR2(1000);
249 l_utl_dir VARCHAR2(100);
250 l_count NUMBER := 0;
251 l_log_begin NUMBER := 0;
252 l_log_end NUMBER := 0;
253 l_comma_pos NUMBER := 0;
254 stmt VARCHAR2(200);
255 cid NUMBER;
256 l_dummy NUMBER;
257
258 BEGIN
259 SELECT value into l_dir
260 FROM v$parameter where upper(name) = 'UTL_FILE_DIR';
261
262 l_dir := l_dir || ','; -- Add sentinel
263
264 l_log_begin := INSTR(l_dir, '/log');
265
266 IF (l_log_begin = 0) THEN /* then get the first string */
267 l_utl_dir := substr(l_dir, 1, INSTR(l_dir, ',') - 1);
268 return l_utl_dir;
269 END IF;
270 l_log_end := INSTR(l_dir, ',', l_log_begin) - 1;
271
272 --have now determined the first occurrence of '/log' and the end pos
273 -- now to determine the start position of the log directory
274
275 l_dir := substr(l_dir, 0, l_log_end);
276
277 LOOP
278 l_comma_pos := INSTR(l_dir, ',', l_comma_pos+1);
279 IF (l_comma_pos <> 0) THEN
280 l_count := l_comma_pos + 1;
281 END IF;
282
283 EXIT WHEN l_comma_pos = 0;
284 END LOOP;
285 l_utl_dir := substr(l_dir, l_count+1, l_log_end);
286
287
288 RETURN l_utl_dir;
289
290 EXCEPTION
291 when others then
292 return null;
293 END;
294
295
296 PROCEDURE write_log( p_text VARCHAR2) IS
297
298 l_len number(10);
299 l_start number(10) :=1;
300 l_end number(10) :=1;
301 last_reached boolean:=false;
302
303 BEGIN
304
305 if p_text is null or p_text='' then
306 return;
307 end if;
308 l_len:=nvl(length(p_text),0);
309 if l_len <=0 then
310 return;
311 end if;
312
313 while true loop
314 l_end:=l_start+150;
315 if l_end >= l_len then
316 l_end:=l_len;
317 last_reached:=true;
318 end if;
319 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_text, l_start, 150));
320 l_start:=l_start+150;
321 if last_reached then
322 exit;
323 end if;
324 end loop;
325
326 ----------------------------------------------------------------------------------
327 -- We need to comment out this call for now, as it impacts the Incremental Update
328 -- for GL Base Summary which uses subworker (the parent request hangs after child
329 -- requests are done). We will find out the reason and make necessary fix for 6.0G
330 ----------------------------------------------------------------------------------
331 --** FND_LOG.String(6, g_obj_name, p_text);
332
333 EXCEPTION
334 WHEN OTHERS THEN
335 NULL;
336 END write_log;
337
338
339 PROCEDURE write_output( p_text VARCHAR2) IS
340 BEGIN
341 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_text);
342 END write_output;
343
344 end;