[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_EXT_UTL
Source
1 PACKAGE BODY ICX_POR_EXT_UTL AS
2 /* $Header: ICXEXTUB.pls 120.1 2006/01/10 11:59:20 sbgeorge noship $*/
3
4 --------------------------------------------------------------
5 -- Global Variables --
6 --------------------------------------------------------------
7 ANALYSIS_REPORT PLS_INTEGER := 1;
8 LOG_REPORT PLS_INTEGER := 0;
9
10 -- bug 2920845: increase size from 200 to 20000
11 -- gFilePath VARCHAR2(200) := NULL;
12 gFilePath VARCHAR2(2000) := NULL;
13 gFileHandle UTL_FILE.FILE_TYPE := NULL;
14 -- FPI feature, Analysis Report
15 gReportHandle UTL_FILE.FILE_TYPE := NULL;
16 gUseFile PLS_INTEGER := USE_CONCURRENT_LOG;
17
18 type tErrorStackType IS TABLE of varchar2(1000)
19 index by binary_integer;
20
21 gErrorStack tErrorStackType;
22
23 gPendingCommitRecords PLS_INTEGER := 0;
24
25 gIcxSchema VARCHAR2(20) := NULL;
26
27 --------------------------------------------------------------
28 -- Write Debug Message --
29 --------------------------------------------------------------
30
31 PROCEDURE clearErrorStack IS
32 BEGIN
33 if (gErrorStack.COUNT > 0) then
34 gErrorStack.DELETE;
35 end if;
36 END clearErrorStack;
37
38 PROCEDURE pushError(pMsg IN VARCHAR2) IS
39 BEGIN
40 if (pMsg is not null) then
41 gErrorStack(gErrorStack.COUNT + 1) := pMsg;
42 end if;
43 END pushError;
44
45 PROCEDURE setFilePath(pFilePath IN VARCHAR2) IS
46 lTmpFilePath VARCHAR2(200) := NULL;
47 BEGIN
48 IF pFilePath = UTL_FILE_DIR THEN
49
50 -- Bug#2876721
51 select trim(value)
52 into gFilePath
53 from v$parameter
54 where name = 'utl_file_dir';
55
56 if(gFilePath is not null) then
57 lTmpFilePath := substrb(translate(ltrim(gFilePath),',',' '), 1,
58 instr(translate(ltrim(gFilePath),',',' '),' ') - 1);
59
60 -- Bug#2876721
61 if ( lTmpFilePath is not null ) then
62 gFilePath := lTmpFilePath;
63 end if;
64 end if;
65
66 ELSIF pFilePath = 'ECE_OUT_FILE_PATH' THEN
67 fnd_profile.get('ECE_OUT_FILE_PATH', gFilePath);
68 ELSE
69 gFilePath := pFilePath;
70 END IF;
71 END setFilePath;
72
73 FUNCTION getFilePath RETURN VARCHAR2 IS
74 BEGIN
75 RETURN gFilePath;
76 END getFilePath;
77
78 PROCEDURE setUseFile(pUseFile IN PLS_INTEGER) IS
79 BEGIN
80 if (pUseFile = USE_FILE_SYSTEM) then
81 gUseFile := USE_FILE_SYSTEM;
82 else
83 gUseFile := USE_CONCURRENT_LOG;
84 end if;
85 END setUseFile;
86
87 PROCEDURE openLog(pFileName IN varchar2,
88 pOpenMode IN varchar2) IS
89 xErrLoc PLS_INTEGER := 100;
90 BEGIN
91 gFileHandle := null;
92 clearErrorStack;
93 xErrLoc := 150;
94
95 -- Bug 1937391
96 if (gDebugLevel = -1) then
97 return;
98 end if;
99
100 -- open the log file
101 if (gUseFile = USE_FILE_SYSTEM) then
102 IF gFilePath IS NULL THEN
103 setFilePath('ECE_OUT_FILE_PATH');
104 END IF;
105
106 gFileHandle := utl_file.fopen(gFilePath, pFileName||'_log', pOpenMode);
107 gReportHandle := utl_file.fopen(gFilePath, pFileName||'_out', pOpenMode);
108 end if;
109
110 xErrLoc := 200;
111 -- for concurrent process log file
112 -- This function does nothing if called from a concurrent program
113 if (gUseFile = USE_CONCURRENT_LOG) then
114 fnd_file.put_names(pFileName||'_log', pFileName||'_out', gFilePath);
115 end if;
116
117 EXCEPTION
118 when fnd_file.utl_file_error then
119 -- Bug#2876721
120 if (gUseFile = USE_CONCURRENT_LOG) then
121 pushError('ICX_POR_EXT_UTL.openLog-'||xErrLoc||
122 ' fnd_file.utl_file_error');
123 raise gFatalException;
124 else
125 gDebugLevel := -1;
126 gFileHandle := null;
127 end if;
128 when others then
129 pushError('ICX_POR_EXT_UTL.openLog-'||xErrLoc||' '||SQLERRM);
130 if (utl_file.is_open(gFileHandle)) then
131 utl_file.fclose(gFileHandle);
132 end if;
133 if (utl_file.is_open(gReportHandle)) then
134 utl_file.fclose(gReportHandle);
135 end if;
136 raise gFatalException;
137 END openLog;
138
139 PROCEDURE closeLog IS
140 BEGIN
141 if (gDebugLevel = NOLOG_LEVEL) then
142 return;
143 end if;
144
145 if (gUseFile = USE_FILE_SYSTEM) then
146 if (utl_file.is_open(gFileHandle)) then
147 utl_file.fclose(gFileHandle);
148 end if;
149 if (utl_file.is_open(gReportHandle)) then
150 utl_file.fclose(gReportHandle);
151 end if;
152 end if;
153
154 if (gUseFile = USE_CONCURRENT_LOG) then
155 -- if the context is not concurrent program then
156 -- close open files. FND_FILE.CLOSE should not
157 -- be called from a concurrent program.
158 if (fnd_global.conc_request_id <= 0) then
159 fnd_file.close;
160 end if;
161 end if;
162
163 EXCEPTION
164 when others then
165 null;
166 END closeLog;
167
168 PROCEDURE log(pString IN VARCHAR2,
169 pReport PLS_INTEGER) IS
170 xErrLoc PLS_INTEGER;
171 xLength PLS_INTEGER;
172 xPnt PLS_INTEGER := 1;
173 BEGIN
174 xErrLoc := 100;
175 if (gUseFile = USE_FILE_SYSTEM) then
176 -- FPI feature, Analysis Report
177 if (pReport = LOG_REPORT) then
178 if (utl_file.is_open(gFileHandle)) then
179 xErrLoc := 200;
180 xLength := length(pString);
181 while (xPnt < xLength) loop
182 utl_file.put_line(gFileHandle, substr(pString, xPnt, 1000));
183 xPnt := xPnt + 1000;
184 end loop;
185 xErrLoc := 240;
186 utl_file.fflush(gFileHandle);
187 end if;
188 else
189 -- FPI feature, Analysis Report
190 if (utl_file.is_open(gReportHandle)) then
191 xErrLoc := 300;
192 xLength := length(pString);
193 while (xPnt < xLength) loop
194 utl_file.put_line(gReportHandle, substr(pString, xPnt, 1000));
195 xPnt := xPnt + 1000;
196 end loop;
197 xErrLoc := 340;
198 utl_file.fflush(gFileHandle);
199 end if;
200 end if;
201 else
202 xErrLoc := 400;
203 xLength := length(pString);
204 while (xPnt < xLength) loop
205 -- FPI feature, Analysis Report
206 if (pReport = LOG_REPORT) then
207 -- Log for concurrent request
208 fnd_file.put_line(fnd_file.log, substr(pString, xPnt, 1000));
209 else
210 -- Out for concurrent request
211 fnd_file.put_line(fnd_file.output, substr(pString, xPnt, 1000));
212 end if;
213 xPnt := xPnt + 1000;
214 end loop;
215 xErrLoc := 420;
216 end if;
217
218 EXCEPTION
219 when fnd_file.utl_file_error then
220 pushError('ICX_POR_EXT_UTL.log-fnd_file.utl_file_error');
221 -- Bug 3488764 : Add custom message to the error stack trace so that customer
222 -- can understand the problem and fix.
223 -- Raise utl_file_error. This is handled seperately by upgrade script
224 raise_application_error(-20100, ICX_POR_EXT_UTL.UTL_FILE_ERR_MSG, TRUE);
225 when others then
226 pushError('ICX_POR_EXT_UTL.log-'||xErrLoc || ': ' ||SQLERRM);
227 raise gException;
228 END log;
229
230 PROCEDURE log(pString IN VARCHAR2) IS
231 BEGIN
232 log(pString, LOG_REPORT);
233 END log;
234
235 PROCEDURE setDebugLevel(pLevel IN PLS_INTEGER) IS
236 BEGIN
237 gDebugLevel := pLevel;
238 END setDebugLevel;
239
240 PROCEDURE debug(pLevel IN PLS_INTEGER,
241 pMsg IN VARCHAR2) IS
242
243 xDebug varchar2(20) := '';
244 xReport PLS_INTEGER := LOG_REPORT;
245 BEGIN
246 if (gDebugLevel = NOLOG_LEVEL) then
247 return;
248 end if;
249
250 if (pLevel <= gDebugLevel) then
251 if (pLevel = ERROR_LEVEL) then
252 xDebug := '[Error] ';
253 elsif (pLevel = ANLYS_LEVEL) then
254 xReport := ANALYSIS_REPORT;
255 elsif (pLevel = INFO_LEVEL) then
256 xDebug := '[Info.] ';
257 elsif (pLevel = DEBUG_LEVEL) then
258 xDebug := '[Debug] ';
259 elsif (pLevel = DETIL_LEVEL) then
260 xDebug := '[Detil] ';
261 end if;
262
263 log(getTimeStamp || ' ' ||
264 xDebug || '' || pMsg, xReport);
265 end if;
266 END debug;
267
268 PROCEDURE debug(pMsg IN VARCHAR2) IS
269 BEGIN
270 debug(INFO_LEVEL, pMsg);
271 END debug;
272
273 PROCEDURE printStackTrace IS
274 xIndex binary_integer;
275 BEGIN
276 if (gErrorStack.COUNT > 0) then
277 log('### Error Stack');
278 xIndex := gErrorStack.FIRST;
279 while (xIndex is not null) loop
280 log('### '||gErrorStack(xIndex));
281 xIndex := gErrorStack.NEXT(xIndex);
282 end loop;
283 Log('### End of Stack');
284 gErrorStack.DELETE;
285 end if;
286 END printStackTrace;
287
288 FUNCTION getStackTraceString RETURN VARCHAR2 IS
289 xString varchar2(2000) := '';
290 xIndex binary_integer;
291 BEGIN
292 if (gErrorStack.COUNT > 0) then
293 xIndex := gErrorStack.FIRST;
294 while (xIndex is not null) loop
295 xString := xString || '>>> ' || gErrorStack(xIndex);
296 xIndex := gErrorStack.NEXT(xIndex);
297 end loop;
298 end if;
299 return xString;
300 END getStackTraceString;
301
302 --------------------------------------------------------------
303 -- Commit/Rollback --
304 --------------------------------------------------------------
305
306 PROCEDURE extCommit IS
307 BEGIN
308 gPendingCommitRecords := gPendingCommitRecords + SQL%ROWCOUNT;
309 if (gPendingCommitRecords >= gCommitSize) then
310 -- debug(DEBUG_LEVEL, 'Commit ' || gPendingCommitRecords || ' Records');
311 -- FND_CONCURRENT.AF_COMMIT is used by concurrent programs that
312 -- use a particular rollback segment. This rollback segment must
313 -- be defined in the Define Concurrent Program form.
314 FND_CONCURRENT.AF_COMMIT;
315
316 gPendingCommitRecords := 0;
317 end if;
318 END extCommit;
319
320 PROCEDURE extAFCommit IS
321 BEGIN
322 -- debug(DEBUG_LEVEL, 'Commit ' || gPendingCommitRecords || ' Records');
323 -- FND_CONCURRENT.AF_COMMIT is used by concurrent programs that
324 -- use a particular rollback segment. This rollback segment must
325 -- be defined in the Define Concurrent Program form.
326 FND_CONCURRENT.AF_COMMIT;
327 gPendingCommitRecords := 0;
328 END extAFCommit;
329
330 PROCEDURE extRollback IS
331 BEGIN
332 -- FND_CONCURRENT.AF_ROLLBACK is used by concurrent programs that
333 -- use a particular rollback segment. This rollback segment must
334 -- be defined in the Define Concurrent Program form.
335 FND_CONCURRENT.AF_ROLLBACK;
336 gPendingCommitRecords := 0;
337 END extRollback;
338
339 --------------------------------------------------------------
340 -- Get PL/SQL Table element --
341 --------------------------------------------------------------
342 FUNCTION getTableElement(pTable IN DBMS_SQL.NUMBER_TABLE,
343 pIndex IN BINARY_INTEGER)
344 RETURN VARCHAR2
345 IS
346 xString varchar2(2000) := '';
347 BEGIN
348 IF pTable.EXISTS(pIndex) THEN
349 xString := xString || pTable(pIndex);
350 ELSE
351 xString := xString || '<Not Exists>';
352 END IF;
353 RETURN xString;
354 END getTableElement;
355
356 FUNCTION getTableElement(pTable IN DBMS_SQL.VARCHAR2_TABLE,
357 pIndex IN BINARY_INTEGER)
358 RETURN VARCHAR2
359 IS
360 xString varchar2(2000) := '';
361 BEGIN
362 IF pTable.EXISTS(pIndex) THEN
363 xString := xString || pTable(pIndex);
364 ELSE
365 xString := xString || '<Not Exists>';
366 END IF;
367 RETURN xString;
368 END getTableElement;
369
370 FUNCTION getTableElement(pTable IN DBMS_SQL.UROWID_TABLE,
371 pIndex IN BINARY_INTEGER)
372 RETURN VARCHAR2
373 IS
374 xString varchar2(2000) := '';
375 BEGIN
376 IF pTable.EXISTS(pIndex) THEN
377 xString := xString || pTable(pIndex);
378 ELSE
379 xString := xString || '<Not Exists>';
380 END IF;
381 RETURN xString;
382 END getTableElement;
383
384 FUNCTION getTableElement(pTable IN DBMS_SQL.DATE_TABLE,
385 pIndex IN BINARY_INTEGER)
386 RETURN VARCHAR2
387 IS
388 xString varchar2(2000) := '';
389 BEGIN
390 IF pTable.EXISTS(pIndex) THEN
391 xString := xString || TO_CHAR(pTable(pIndex), 'MM/DD/YY HH24:MI:SS');
392 ELSE
393 xString := xString || '<Not Exists>';
394 END IF;
395 RETURN xString;
396 END getTableElement;
397
398 --------------------------------------------------------------
399 -- Get schemas --
400 --------------------------------------------------------------
401 FUNCTION getIcxSchema RETURN VARCHAR2
402 IS
403 xStatus varchar2(20);
404 xIndustry varchar2(20);
405 BEGIN
406 IF (gIcxSchema IS NOT NULL OR
407 FND_INSTALLATION.GET_APP_INFO('ICX', xStatus,
408 xIndustry, gIcxSchema))
409 THEN
410 RETURN gIcxSchema;
411 END IF;
412 RETURN 'ICX';
413 END getIcxSchema;
414
415 FUNCTION getTimeStamp RETURN VARCHAR2
416 IS
417 x100Sec PLS_INTEGER;
418 xTimeStamp VARCHAR2(40);
419 BEGIN
420 x100Sec := MOD(dbms_utility.get_time, 100);
421 /*
422 IF x100Sec < 50 THEN
423 xTimeStamp := TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS') ||
424 ':' || x100Sec;
425 ELSE
426 xTimeStamp := TO_CHAR(SYSDATE-1/86400, 'MM/DD/YY HH24:MI:SS') ||
427 ':' || x100Sec;
428 END IF;
429 */
430 xTimeStamp := TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS') ||
431 ':' || x100Sec;
432 RETURN xTimeStamp;
433 END getTimeStamp;
434
435 -- Bug#3453882
436 FUNCTION getDatabaseVersion RETURN NUMBER
437 IS
438 xErrLoc PLS_INTEGER := 100;
439 version NUMBER := 0;
440 majorReleasePos NUMBER := 0;
441 minorReleasePos NUMBER := 0;
442 compatibility VARCHAR2(30) := NULL;
443 majorVersion VARCHAR2(10) := NULL;
444 minorVersion VARCHAR2(10) := NULL;
445 versionString VARCHAR2(30) := NULL;
446 BEGIN
447 dbms_utility.db_version(versionString, compatibility);
448
449 xErrLoc := 110;
450 select instr(versionString, '.') into majorReleasePos from dual;
451 select instr(substr(versionString,majorReleasePos), '.')
452 into minorReleasePos from dual;
453
454 xErrLoc := 120;
455 majorVersion := substr(versionString, 1, majorReleasePos-1);
456 minorVersion := substr(versionString, majorReleasePos+1, minorReleasePos);
457
458 xErrLoc := 130;
459 version := to_number(majorVersion) + (to_number(minorVersion) / 10);
460
461 xErrLoc := 140;
462 debug(MUST_LEVEL, 'Database Version: '|| to_char(version));
463 RETURN version;
464 EXCEPTION
465 when others then
466 pushError('ICX_POR_EXT_UTL.getDatabaseVersion-'||xErrLoc || ': ' || 'versionString=' || versionString || ' majorVersion=' || majorVersion || ' minorVersion=' || majorVersion ||' ReturnedVersion=' || to_char(version) || ' - ' ||SQLERRM);
467 raise gException;
468 END getDatabaseVersion;
469
470
471 END ICX_POR_EXT_UTL;