1 PACKAGE BODY WSM_log_PVT AS
2 /* $Header: WSMVLOGB.pls 120.4 2006/09/08 00:52:05 nlal noship $ */
3
4 g_log_level_procedure NUMBER := FND_LOG.LEVEL_PROCEDURE;
5 g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6
7 G_MSG_LVL_UNEXP_ERROR NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ;
8 G_MSG_LVL_ERROR NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR ;
9 G_MSG_LVL_SUCCESS NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS ;
10 G_MSG_LVL_DEBUG_HIGH NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
11 G_MSG_LVL_DEBUG_MEDIUM NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM ;
12 G_MSG_LVL_DEBUG_LOW NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ;
13
14 -- Log the parameters...
15 PROCEDURE LogProcParams ( p_module_name IN varchar2 ,
16 p_param_tbl IN WSM_log_PVT.param_tbl_type,
17 p_fnd_log_level IN number
18 )
19
20 IS
21
22 BEGIN
23 -- ( This code is correct : Unforutnately due to
24 -- inefficient GSCC warning/error check, forced to refer to the package public variable
25 --IF (g_log_level_procedure >= p_fnd_log_level) and fnd_log.test(g_log_level_procedure , p_module_name) then
26 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE and fnd_log.test(g_log_level_procedure , p_module_name)
27 THEN
28 FND_LOG.String(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
29 MESSAGE => 'Parameters for [--> ' || p_module_name || ' <---]',
30 MODULE => p_module_name);
31
32 for i in 1..p_param_tbl.count loop
33 --IF p_fnd_log_level <= FND_LOG.LEVEL_PROCEDURE
34 -- ( This code is correct : Unforutnately due to
35 -- inefficient GSCC warning/error check, forced to refer to the package public variable
36
37 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE then
38 FND_LOG.String(
39 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
40 MESSAGE => '[ ' || p_param_tbl(i).paramName || ' ] : ' || p_param_tbl(i).paramValue,
41 MODULE => p_module_name);
42 END IF;
43 end loop;
44 ELSE
45 -- Procedure logging not enabled.. return
46 return;
47 END IF;
48 END LogProcParams;
49
50 -- Log a message..
51 PROCEDURE LogMessage ( p_module_name IN varchar2 ,
52 p_msg_name IN varchar2 DEFAULT NULL,
53 p_msg_appl_name IN VARCHAR2 DEFAULT NULL,
54 p_msg_text IN varchar2 DEFAULT NULL,
55 p_stmt_num IN NUMBER DEFAULT NULL,
56 p_msg_tokens IN token_rec_tbl ,
57 -- pass 1 to p_wsm_warning if the message is a a warning message
58 p_wsm_warning IN NUMBER DEFAULT NULL,
59 -- p_fnd_msg_level has default NULL b'cos we dont do statement level logging into FND_MSG_PUB
60 p_fnd_msg_level IN NUMBER DEFAULT NULL,
61 p_fnd_log_level IN NUMBER ,
62 p_run_log_level IN NUMBER
63 )
64
65 IS
66 l_index NUMBER;
67 l_message VARCHAR2(2000);
68 l_msg_type NUMBER;
69 BEGIN
70 -- If p_msg_text is NOT NULL then it is a statement level logging message which will be logged into
71 -- FND_LOG_MESSAGES alone...
72
73 IF (p_msg_text IS NOT NULL) AND
74 (p_fnd_log_level < FND_LOG.LEVEL_EXCEPTION)
75 -- ST : Added this clause as we need the error messages from the old procedures that don't follow
76 -- Logging framework also have their error statements logged similar to the error messages from the
77 -- new procedures...
78 THEN
79
80 -- ( This code is correct : Unforutnately due to
81 -- inefficient GSCC warning/error check, forced to refer to the package public variable
82 --IF (p_fnd_log_level >= p_run_log_level) and fnd_log.test(p_fnd_log_level , p_module_name) then
83 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT and fnd_log.test(p_fnd_log_level , p_module_name)
84 THEN
85
86 FND_LOG.String(
87 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
88 MESSAGE => '[ ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') || ' ] : '
89 || p_module_name || ' : ' || p_stmt_num
90 || ' : ' || p_msg_text,
91 MODULE => p_module_name);
92 END IF;
93 RETURN;
94 END IF;
95
96
97 IF (p_msg_name IS NOT NULL OR (p_msg_text IS NOT NULL AND p_fnd_log_level >= FND_LOG.LEVEL_EXCEPTION))
98 -- ST : Added the above check on p_msg_text for bug 5233265 as the older procedures will return a error_message to a
99 -- newer API that invokes it rather than a msg_count and msg_txt combination...
100 -- Hence those error messages also have to be logged..
101 AND
102 (FND_MSG_PUB.Check_Msg_Level(p_fnd_msg_level) OR
103 (p_fnd_log_level >= p_run_log_level and FND_LOG.TEST(p_run_log_level,p_module_name))
104 )
105 THEN
106 -- ST : Added the IF clause for bug 5233265 --
107 IF p_msg_name IS NOT NULL THEN
108 -- This to log the message in WIE...
109 FND_MESSAGE.SET_NAME(p_msg_appl_name,p_msg_name);
110 IF p_msg_tokens.count > 0 THEN
111 for l_cnt in p_msg_tokens.first..p_msg_tokens.last loop
112 FND_MESSAGE.SET_TOKEN(p_msg_tokens(l_cnt).TokenName,p_msg_tokens(l_cnt).TokenValue);
113 end loop;
114 END IF;
115 l_message := FND_MESSAGE.GET;
116 ELSE
117 -- The messages is already present...
118 l_message := p_msg_text;
119 END IF;
120
121 -- Indicates that this is a translated message...
122 -- The below is not required as no autologging...
123 -- FND_MESSAGE.SET_MODULE(p_module_name);
124
125 IF FND_MSG_PUB.Check_Msg_Level(p_fnd_msg_level) THEN
126
127 IF (p_wsm_warning IS NULL OR p_wsm_warning<>1) THEN /* Bugfix 5491121 don't put warnings on msg stack */
128
129 IF p_msg_name IS NOT NULL THEN
130 FND_MESSAGE.SET_NAME(p_msg_appl_name,p_msg_name);
131 IF p_msg_tokens.count > 0 THEN
132 for l_cnt in p_msg_tokens.first..p_msg_tokens.last loop
133 FND_MESSAGE.SET_TOKEN(p_msg_tokens(l_cnt).TokenName,p_msg_tokens(l_cnt).TokenValue);
134 end loop;
135 END IF;
136 -- add to FND_MSG_PUB
137 ELSE
138 -- Added for bug 5233265
139 fnd_message.set_name('WSM','WSM_ERROR_TEXT');
140 fnd_message.set_token('ERROR_TEXT',l_message);
141 END IF;
142 -- ST : Fix for bug 5233265 end --
143
144 FND_MSG_PUB.add;
145
146 END IF;
147
148 IF (g_write_to_WIE = 1) THEN
149
150 IF p_wsm_warning IS NOT NULL THEN
151 l_msg_type := 2; --warning
152 ELSE
153 l_msg_type := 1; --error
154 END IF;
155
156 l_index := g_error_msg_tbl.count + 1;
157
158 g_error_msg_tbl(l_index).TRANSACTION_ID := g_txn_id;
159 g_error_msg_tbl(l_index).MESSAGE := l_MESSAGE;
160 g_error_msg_tbl(l_index).LAST_UPDATE_DATE := sysdate;
161 g_error_msg_tbl(l_index).LAST_UPDATED_BY := fnd_global.user_id;
162 g_error_msg_tbl(l_index).CREATION_DATE := sysdate;
163 g_error_msg_tbl(l_index).CREATED_BY := fnd_global.user_id;
164 g_error_msg_tbl(l_index).LAST_UPDATE_LOGIN := fnd_global.login_id;
165 g_error_msg_tbl(l_index).HEADER_ID := g_header_id;
166 g_error_msg_tbl(l_index).REQUEST_ID := fnd_global.conc_request_id;
167 g_error_msg_tbl(l_index).PROGRAM_ID := fnd_global.conc_program_id;
168 g_error_msg_tbl(l_index).PROGRAM_APPLICATION_ID := fnd_global.prog_appl_id;
169 g_error_msg_tbl(l_index).MESSAGE_TYPE := l_msg_type;
170
171 END IF;
172 END IF;
173
174 -- ( This code is correct : Unforutnately due to
175 -- inefficient GSCC warning/error check, forced to refer to the package public variable
176 --IF (p_fnd_log_level >= p_run_log_level and FND_LOG.TEST(p_fnd_log_level,p_module_name))
177 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT and FND_LOG.TEST(p_fnd_log_level,p_module_name)
178 THEN
179 -- To log into fnd_log_messages ...
180 fnd_log.string(log_level => FND_LOG.LEVEL_STATEMENT,
181 module => p_module_name,
182 message => '[ ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') || ' ] : '
183 || p_module_name || ' : ' || p_stmt_num
184 || ' : ' || l_message);
185 END IF;
186
187 END IF;
188
189 END LogMessage;
190
191 PROCEDURE handle_others ( p_module_name IN varchar2,
192 p_stmt_num IN NUMBER,
193 p_fnd_log_level IN NUMBER,
194 p_run_log_level IN NUMBER
195 )
196
197 IS
198 l_msg_tokens token_rec_tbl;
199 BEGIN
200 IF (G_LOG_LEVEL_UNEXPECTED >= p_fnd_log_level) THEN
201
202 l_msg_tokens.delete;
203 WSM_log_PVT.logMessage (p_module_name => p_module_name ,
204 p_msg_text => SUBSTRB('Unexpected Error : SQLCODE '|| SQLCODE ||' : SQLERRM : '|| SQLERRM, 1, 2000),
205 p_stmt_num => p_stmt_num ,
206 p_msg_tokens => l_msg_tokens ,
207 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
208 p_run_log_level => p_fnd_log_level
209 );
210
211 END IF;
212
213 IF G_LOG_LEVEL_UNEXPECTED >= p_fnd_log_level OR
214 FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR)
215 THEN
216 -- <construct tokens record>
217 l_msg_tokens.delete;
218 WSM_log_PVT.logMessage(p_module_name => p_module_name ,
219 p_msg_name => 'WSM_GENERIC_ERROR' ,
220 p_msg_appl_name => 'WSM' ,
221 p_msg_tokens => l_msg_tokens ,
222 p_fnd_msg_level => G_MSG_LVL_UNEXP_ERROR ,
223 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
224 p_run_log_level => p_fnd_log_level
225 );
226 END IF;
227 END handle_others;
228
229 -- will write the message in the PL/SQL table to the database table...
230 PROCEDURE WriteToWIE
231
232 IS
233
234 BEGIN
235 IF g_error_msg_tbl.count > 0 then
236 forall l_index in g_error_msg_tbl.first..g_error_msg_tbl.last
237 insert into wsm_interface_errors values g_error_msg_tbl(l_index);
238 END IF;
239
240 -- reset once done...
241 g_header_id := null;
242 g_txn_id := null;
243 g_write_to_WIE := 0;
244
245 -- clean up the data...
246 g_error_msg_tbl.delete;
247
248 END WriteToWIE;
249
250 -- This procedure will be used to update the g_error_tbl with
251 -- error messages returned from the other product API's
252 Procedure update_errtbl (p_start_index IN NUMBER,
253 p_end_index IN NUMBER
254 )
255 IS
256
257 l_index NUMBER;
258 l_err_msg VARCHAR2(2000);
259 BEGIN
260
261 IF (g_write_to_WIE <> 1) THEN
262 return;
263 END IF;
264
265 FOR i IN p_start_index..p_end_index LOOP
266
267 l_err_msg := fnd_msg_pub.get;
268 l_index := g_error_msg_tbl.count + 1;
269
270 g_error_msg_tbl(l_index).TRANSACTION_ID := g_txn_id;
271 g_error_msg_tbl(l_index).MESSAGE := l_err_msg;
272 g_error_msg_tbl(l_index).LAST_UPDATE_DATE := sysdate;
273 g_error_msg_tbl(l_index).LAST_UPDATED_BY := fnd_global.user_id;
274 g_error_msg_tbl(l_index).CREATION_DATE := sysdate;
275 g_error_msg_tbl(l_index).CREATED_BY := fnd_global.user_id;
276 g_error_msg_tbl(l_index).LAST_UPDATE_LOGIN := fnd_global.login_id;
277 g_error_msg_tbl(l_index).HEADER_ID := g_header_id;
278 -- Is it always ERROR..?
279 g_error_msg_tbl(l_index).REQUEST_ID := fnd_global.conc_request_id;
280 g_error_msg_tbl(l_index).PROGRAM_ID := fnd_global.conc_program_id;
281 g_error_msg_tbl(l_index).PROGRAM_APPLICATION_ID := fnd_global.prog_appl_id;
282
283 g_error_msg_tbl(l_index).MESSAGE_TYPE := G_MSG_LVL_UNEXP_ERROR;
284 END LOOP;
285
286
287 END update_errtbl;
288
289 -- Populate Interface information...
290 --
291 Procedure PopulateIntfInfo ( p_header_id IN NUMBER,
292 p_txn_id IN NUMBER
293 )
294
295 IS
296
297 BEGIN
298 g_header_id := p_header_id;
299 g_txn_id := p_txn_id;
300 g_write_to_WIE := 1;
301 END PopulateIntfInfo;
302
303
304 END WSM_log_PVT;