[Home] [Help]
PACKAGE BODY: APPS.BSC_UPDATE_LOG
Source
1 PACKAGE BODY BSC_UPDATE_LOG AS
2 /* $Header: BSCDLOGB.pls 120.1 2005/06/30 16:29:10 meastmon noship $ */
3 --
4 -- Package constants
5 --
6
7 -- Formats
8 c_fto_long_date_time CONSTANT VARCHAR2(30) := 'Month DD, YYYY HH24:MI:SS';
9 c_version CONSTANT VARCHAR2(5) := '5.3.0';
10
11 --
12 -- Package variables
13 --
14 g_log_file_dir VARCHAR2(60) := NULL;
15 g_log_file_name VARCHAR2(2000) := NULL;
16
17
18 /*===========================================================================+
19 | FUNCTION Init_Log_File
20 +============================================================================*/
21 FUNCTION Init_Log_File (
22 x_log_file_name IN VARCHAR2
23 ) RETURN BOOLEAN IS
24
25 e_unexpected_error EXCEPTION;
26 e_no_log_file_dir EXCEPTION;
27
28 h_log_file_dir VARCHAR2(60);
29 h_log_file_handle UTL_FILE.FILE_TYPE;
30
31 TYPE t_cursor IS REF CURSOR;
32
33 c_utl_file_dir t_cursor; -- h_utlfiledir
34 c_utl_file_dir_sql VARCHAR2(2000) := 'SELECT VP.value'||
35 ' FROM v$parameter VP'||
36 ' WHERE UPPER(VP.name) = :1';
37
38 h_utlfiledir VARCHAR2(30) := 'UTL_FILE_DIR';
39 h_utl_file_dir VARCHAR2(2000);
40
41 BEGIN
42 OPEN c_utl_file_dir FOR c_utl_file_dir_sql USING h_utlfiledir;
43 FETCH c_utl_file_dir INTO h_utl_file_dir;
44 IF c_utl_file_dir%NOTFOUND THEN
45 h_log_file_dir := NULL;
46 ELSE
47 IF h_utl_file_dir IS NULL THEN
48 h_log_file_dir := NULL;
49 ELSE
50 IF INSTR(h_utl_file_dir, ',') > 0 THEN
51 h_log_file_dir := SUBSTR(h_utl_file_dir, 1, INSTR(h_utl_file_dir, ',') - 1);
52 ELSE
53 h_log_file_dir := h_utl_file_dir;
54 END IF;
55 END IF;
56 END IF;
57 CLOSE c_utl_file_dir;
58
59 IF h_log_file_dir IS NULL THEN
60 RAISE e_no_log_file_dir;
61 END IF;
62
63 h_log_file_handle := UTL_FILE.FOPEN(h_log_file_dir, x_log_file_name, 'w');
64 UTL_FILE.PUT_LINE(h_log_file_handle, '+---------------------------------------------------------------------------+');
65 UTL_FILE.PUT_LINE(h_log_file_handle,'Oracle Balanced Scorecard: Version : '||c_version);
66 UTL_FILE.PUT_LINE(h_log_file_handle, '');
67 UTL_FILE.PUT_LINE(h_log_file_handle, 'Copyright (c) Oracle Corporation 1999. All rights reserved.');
68 UTL_FILE.PUT_LINE(h_log_file_handle, '');
69 UTL_FILE.PUT_LINE(h_log_file_handle, 'Module: BSC Loader');
70 UTL_FILE.PUT_LINE(h_log_file_handle, '+---------------------------------------------------------------------------+');
71 UTL_FILE.PUT_LINE(h_log_file_handle, 'Time: '||TO_CHAR(SYSDATE, c_fto_long_date_time));
72 UTL_FILE.FCLOSE(h_log_file_handle);
73
74 g_log_file_name := x_log_file_name;
75 g_log_file_dir := h_log_file_dir;
76
77 RETURN TRUE;
78
79 EXCEPTION
80 WHEN e_unexpected_error THEN
81 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_LOGFILE_CREATION_FAILED'),
82 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
83 RETURN FALSE;
84
85 WHEN e_no_log_file_dir THEN
86 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_LOGFILE_DIR_NOT_SPECIFIED'),
87 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
88 RETURN FALSE;
89
90 WHEN UTL_FILE.INVALID_PATH THEN
91 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_LOGFILE_PATH_FAILED'),
92 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
93 RETURN FALSE;
94
95 WHEN UTL_FILE.INVALID_MODE THEN
96 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_LOGFILE_MODE_FAILED'),
97 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
98 RETURN FALSE;
99
100 WHEN UTL_FILE.INVALID_OPERATION THEN
101 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_LOGFILE_OPERATION_FAILED'),
102 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
103 RETURN FALSE;
104
105 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
106 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_LOGFILE_HANDLE_FAILED'),
107 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
108 RETURN FALSE;
109
110 WHEN UTL_FILE.WRITE_ERROR THEN
111 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WRITE_LOGFILE_FAILED'),
112 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
113 RETURN FALSE;
114
115 WHEN OTHERS THEN
116 BSC_MESSAGE.Add(x_message => SQLERRM,
117 x_source => 'BSC_UPDATE_LOG.Init_Log_File');
118 RETURN FALSE;
119
120 END Init_Log_File;
121
122
123 /*===========================================================================+
124 | FUNCTION Log_File_Dir
125 +============================================================================*/
126 FUNCTION Log_File_Dir RETURN VARCHAR2 IS
127 BEGIN
128 RETURN g_log_file_dir;
129
130 END Log_File_Dir;
131
132
133 /*===========================================================================+
134 | FUNCTION Log_File_Name
135 +============================================================================*/
136 FUNCTION Log_File_Name RETURN VARCHAR2 IS
137 BEGIN
138 RETURN g_log_file_name;
139
140 END Log_File_Name;
141
142
143 /*===========================================================================+
144 | PROCEDURE Write_Line_Log
145 +============================================================================*/
146 PROCEDURE Write_Line_Log (
147 x_line IN VARCHAR2,
148 x_which IN NUMBER
149 ) IS
150
151 h_log_file_handle UTL_FILE.FILE_TYPE;
152 h_which NUMBER;
153
154 h_line VARCHAR2(32700);
155
156 BEGIN
157 h_line := TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')||' '||x_line;
158
159 IF BSC_APPS.APPS_ENV THEN
160 -- APPS environment (concurrent program)
161
162 -- Due to some issue, when there is an error in the program
163 -- the output file is not saved, i am going to write out put to log
164 -- file also.
165
166 IF x_which = LOG THEN
167 FND_FILE.Put_Line(FND_FILE.LOG, h_line);
168 ELSE
169 FND_FILE.Put_Line(FND_FILE.OUTPUT, h_line);
170 FND_FILE.Put_Line(FND_FILE.LOG, h_line);
171 END IF;
172
173 ELSE
174 -- Personal environment
175 IF g_log_file_name IS NOT NULL THEN
176 h_log_file_handle := UTL_FILE.FOPEN(g_log_file_dir, g_log_file_name, 'a');
177
178 UTL_FILE.PUT_LINE(h_log_file_handle, h_line);
179 UTL_FILE.FCLOSE(h_log_file_handle);
180 END IF;
181 END IF;
182
183 END Write_Line_Log;
184
185
186 /*===========================================================================+
187 | PROCEDURE Write_Errors_To_Log
188 +============================================================================*/
189 PROCEDURE Write_Errors_To_Log IS
190
191 TYPE t_cursor IS REF CURSOR;
192
193 c_messages t_cursor; -- h_sessionid
194 c_messages_sql VARCHAR2(2000) := 'SELECT message'||
195 ' FROM bsc_message_logs'||
196 ' WHERE last_update_login = :1'||
197 ' ORDER BY last_update_date';
198
199 h_sessionid NUMBER := USERENV('SESSIONID');
200
201 h_message bsc_message_logs.message%TYPE;
202
203
204 BEGIN
205 OPEN c_messages FOR c_messages_sql USING h_sessionid;
206 FETCH c_messages INTO h_message;
207
208 WHILE c_messages%FOUND LOOP
209 Write_Line_Log(h_message, BSC_UPDATE_LOG.LOG);
210 FETCH c_messages INTO h_message;
211 END LOOP;
212
213 CLOSE c_messages;
214
215 END Write_Errors_To_Log;
216
217
218 END BSC_UPDATE_LOG;