1 package body ZPB_ERROR_HANDLER as
2 /* $Header: zpberrhandler.plb 120.0.12010.4 2006/08/03 13:36:10 appldev noship $ */
3
4 /***********************************************************************
5 * PACKAGE Level VARIABLES *
6 ***********************************************************************/
7
8 pv_status varchar2(1);
9
10
11 procedure L_HANDLE_EXCEPTION (P_MODULE in VARCHAR2,
12 P_PROCEDURE in VARCHAR2,
13 P_MESSAGE in VARCHAR2,
14 TOKEN_1_NAME in VARCHAR2,
15 TOKEN_1_VALUE in VARCHAR2,
16 TOKEN_2_NAME in VARCHAR2,
17 TOKEN_2_VALUE in VARCHAR2,
18 TOKEN_3_NAME in VARCHAR2,
19 TOKEN_3_VALUE in VARCHAR2,
20 TOKEN_4_NAME in VARCHAR2,
21 TOKEN_4_VALUE in VARCHAR2,
22 TOKEN_5_NAME in VARCHAR2,
23 TOKEN_5_VALUE in VARCHAR2)
24 is
25 begin
26 if (P_MESSAGE is null) then
27 ZPB_LOG.LOG_PLSQL_EXCEPTION (P_MODULE, P_PROCEDURE, FALSE);
28 else
29 ZPB_LOG.WRITE_EXCEPTION (P_MODULE,
30 P_MESSAGE,
31 TOKEN_1_NAME,
32 TOKEN_1_VALUE,
33 TOKEN_2_NAME,
34 TOKEN_2_VALUE,
35 TOKEN_3_NAME,
36 TOKEN_3_VALUE,
37 TOKEN_4_NAME,
38 TOKEN_4_VALUE,
39 TOKEN_5_NAME,
40 TOKEN_5_VALUE,
41 FALSE);
42 end if;
43 end L_HANDLE_EXCEPTION;
44
45 -------------------------------------------------------------------------------
46 -- HANDLE_EXCEPTION
47 --
48 -- Procedure to handle an exception. Will log the exception at level 4 -
49 -- EXCEPTION and register it to be displayed by OA. The process will be
50 -- returned to your code.
51 --
52 -- IN:
53 -- P_MODULE - The calling package
54 -- P_PROCEDURE - The calling procedure
55 -- P_MESSAGE - The Message name defined in FND_MESSAGES table. If
56 -- omitted, default message used and will pull message from
57 -- SQLERRM (meaning an exception must have been thrown).
58 -- TOKEN_# - Any tokens required in the message. Tokens go as a
59 -- a name/value. Omit if not applicable or using default.
60 --
61 -------------------------------------------------------------------------------
62 procedure HANDLE_EXCEPTION (P_MODULE in VARCHAR2,
63 P_PROCEDURE in VARCHAR2,
64 P_MESSAGE in VARCHAR2,
65 TOKEN_1_NAME in VARCHAR2,
66 TOKEN_1_VALUE in VARCHAR2,
67 TOKEN_2_NAME in VARCHAR2,
68 TOKEN_2_VALUE in VARCHAR2,
69 TOKEN_3_NAME in VARCHAR2,
70 TOKEN_3_VALUE in VARCHAR2,
71 TOKEN_4_NAME in VARCHAR2,
72 TOKEN_4_VALUE in VARCHAR2,
73 TOKEN_5_NAME in VARCHAR2,
74 TOKEN_5_VALUE in VARCHAR2)
75 is
76 begin
77 L_HANDLE_EXCEPTION(P_MODULE,
78 P_PROCEDURE,
79 P_MESSAGE,
80 TOKEN_1_NAME,
81 TOKEN_1_VALUE,
82 TOKEN_2_NAME,
83 TOKEN_2_VALUE,
84 TOKEN_3_NAME,
85 TOKEN_3_VALUE,
86 TOKEN_4_NAME,
87 TOKEN_4_VALUE,
88 TOKEN_5_NAME,
89 TOKEN_5_VALUE);
90 FND_MSG_PUB.ADD;
91 FND_MESSAGE.CLEAR;
92 end HANDLE_EXCEPTION;
93
94 -------------------------------------------------------------------------------
95 -- INITIALIZE
96 --
97 -- Prcoedure that will initialize error handling. This MUST be called ONCE
98 -- each time OA calls a PL/SQL procedure before any error handling methods
99 -- are called.
100 --
101 -------------------------------------------------------------------------------
102 procedure INITIALIZE
103 is
104 begin
105 FND_MSG_PUB.INITIALIZE;
106 end INITIALIZE;
107
108 -------------------------------------------------------------------------------
109 -- Merges the return status, keeping the more severe error
110 --
111 -- IN:
112 -- P_CURR_STATUS - The Current Status
113 -- P_MERGED_STATUS - The status to merge into the current status
114 -------------------------------------------------------------------------------
115 procedure MERGE_STATUS (x_curr_status IN OUT NOCOPY VARCHAR2,
116 p_merge_status IN VARCHAR2)
117 is
118 begin
119 --
120 -- Only 3 possible statuses, unexp error, error or success:
121 --
122 if (p_merge_status <> FND_API.G_RET_STS_SUCCESS and
123 x_curr_status <> FND_API.G_RET_STS_UNEXP_ERROR) then
124 x_curr_status := p_merge_status;
125 end if;
126
127 end MERGE_STATUS;
128
129 -------------------------------------------------------------------------------
130 -- RAISE_EXCEPTION
131 --
132 -- Procedure to handle an exception. Will log the exception at level 4 -
133 -- EXCEPTION and raise a new SQLException. Processing will halt, and it is
134 -- up to the OA code to handle the SQLException properly.
135 --
136 -- IN:
137 -- P_MODULE - The calling package
138 -- P_PROCEDURE - The calling procedure
139 -- P_MESSAGE - The Message name defined in FND_MESSAGES table. If
140 -- omitted, default message used and will pull message from
141 -- SQLERRM (meaning an exception must have been thrown).
142 -- TOKEN_# - Any tokens required in the message. Tokens go as a
143 -- a name/value. Omit if not applicable or using default.
144 --
145 -------------------------------------------------------------------------------
146 procedure RAISE_EXCEPTION (P_MODULE in VARCHAR2,
147 P_PROCEDURE in VARCHAR2,
148 P_MESSAGE in VARCHAR2,
149 TOKEN_1_NAME in VARCHAR2,
150 TOKEN_1_VALUE in VARCHAR2,
151 TOKEN_2_NAME in VARCHAR2,
152 TOKEN_2_VALUE in VARCHAR2,
153 TOKEN_3_NAME in VARCHAR2,
154 TOKEN_3_VALUE in VARCHAR2,
155 TOKEN_4_NAME in VARCHAR2,
156 TOKEN_4_VALUE in VARCHAR2,
157 TOKEN_5_NAME in VARCHAR2,
158 TOKEN_5_VALUE in VARCHAR2)
159 is
160 begin
161 L_HANDLE_EXCEPTION(P_MODULE,
162 P_PROCEDURE,
163 P_MESSAGE,
164 TOKEN_1_NAME,
165 TOKEN_1_VALUE,
166 TOKEN_2_NAME,
167 TOKEN_2_VALUE,
168 TOKEN_3_NAME,
169 TOKEN_3_VALUE,
170 TOKEN_4_NAME,
171 TOKEN_4_VALUE,
172 TOKEN_5_NAME,
173 TOKEN_5_VALUE);
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 end RAISE_EXCEPTION;
176
177 -------------------------------------------------------------------------------
178 -- REGISTER_CONFIRMATION
179 --
180 -- Procedure to register an confirmation box. Message will be logged at
181 -- level 3 - EVENT and register it to be displayed by OA. The process will
182 -- be returned to your code.
183 --
184 -- IN:
185 -- P_MODULE - The calling package
186 -- P_PROCEDURE - The calling procedure
187 -- P_MESSAGE - The Message name defined in FND_MESSAGES table. If
188 -- omitted, default message used and will pull message from
189 -- SQLERRM (meaning an exception must have been thrown).
190 -- TOKEN_# - Any tokens required in the message. Tokens go as a
191 -- a name/value. Omit if not applicable or using default.
192 --
193 -------------------------------------------------------------------------------
194 procedure REGISTER_CONFIRMATION (P_MODULE in VARCHAR2,
195 P_PROCEDURE in VARCHAR2,
196 P_MESSAGE in VARCHAR2,
197 TOKEN_1_NAME in VARCHAR2,
198 TOKEN_1_VALUE in VARCHAR2,
199 TOKEN_2_NAME in VARCHAR2,
200 TOKEN_2_VALUE in VARCHAR2,
201 TOKEN_3_NAME in VARCHAR2,
202 TOKEN_3_VALUE in VARCHAR2,
203 TOKEN_4_NAME in VARCHAR2,
204 TOKEN_4_VALUE in VARCHAR2,
205 TOKEN_5_NAME in VARCHAR2,
206 TOKEN_5_VALUE in VARCHAR2)
207 is
208 begin
209 ZPB_LOG.WRITE_EVENT_TR (P_MODULE||'.'||P_PROCEDURE,
210 P_MESSAGE,
211 TOKEN_1_NAME,
212 TOKEN_1_VALUE,
213 TOKEN_2_NAME,
214 TOKEN_2_VALUE,
215 TOKEN_3_NAME,
216 TOKEN_3_VALUE,
217 TOKEN_4_NAME,
218 TOKEN_4_VALUE,
219 TOKEN_5_NAME,
220 TOKEN_5_VALUE,
221 FALSE);
222 FND_MSG_PUB.ADD_DETAIL(P_MESSAGE_TYPE => FND_MSG_PUB.G_INFORMATION_MSG);
223 FND_MESSAGE.CLEAR;
224 end REGISTER_CONFIRMATION;
225
226 -------------------------------------------------------------------------------
227 -- REGISTER_ERROR
228 --
229 -- Procedure to register an error. Message will be logged at level 5 -
230 -- ERROR and register it to be displayed by OA. The process will be
231 -- returned to your code.
232 --
233 -- IN:
234 -- P_MODULE - The calling package
235 -- P_PROCEDURE - The calling procedure
236 -- P_MESSAGE - The Message name defined in FND_MESSAGES table. If
237 -- omitted, default message used and will pull message from
238 -- SQLERRM (meaning an exception must have been thrown).
239 -- TOKEN_# - Any tokens required in the message. Tokens go as a
240 -- a name/value. Omit if not applicable or using default.
241 --
242 -------------------------------------------------------------------------------
243 procedure REGISTER_ERROR (P_MODULE in VARCHAR2,
244 P_PROCEDURE in VARCHAR2,
245 P_MESSAGE in VARCHAR2,
246 TOKEN_1_NAME in VARCHAR2,
247 TOKEN_1_VALUE in VARCHAR2,
248 TOKEN_2_NAME in VARCHAR2,
249 TOKEN_2_VALUE in VARCHAR2,
250 TOKEN_3_NAME in VARCHAR2,
251 TOKEN_3_VALUE in VARCHAR2,
252 TOKEN_4_NAME in VARCHAR2,
253 TOKEN_4_VALUE in VARCHAR2,
254 TOKEN_5_NAME in VARCHAR2,
255 TOKEN_5_VALUE in VARCHAR2)
256 is
257 begin
258 ZPB_LOG.WRITE_ERROR (P_MODULE||'.'||P_PROCEDURE,
259 P_MESSAGE,
260 TOKEN_1_NAME,
261 TOKEN_1_VALUE,
262 TOKEN_2_NAME,
263 TOKEN_2_VALUE,
264 TOKEN_3_NAME,
265 TOKEN_3_VALUE,
266 TOKEN_4_NAME,
267 TOKEN_4_VALUE,
268 TOKEN_5_NAME,
269 TOKEN_5_VALUE,
270 FALSE);
271 FND_MSG_PUB.ADD;
272 --FND_MSG_PUB.ADD_DETAIL(P_MESSAGE_TYPE => FND_MSG_PUB.G_ERROR_MSG);
273 FND_MESSAGE.CLEAR;
274 end REGISTER_ERROR;
275
276 -------------------------------------------------------------------------------
277 -- REGISTER_INFORMATION
278 --
279 -- Procedure to register an information box. Message will be logged at
280 -- level 3 - EVENT and register it to be displayed by OA. The process will
281 -- be returned to your code.
282 --
283 -- IN:
284 -- P_MODULE - The calling package
285 -- P_PROCEDURE - The calling procedure
286 -- P_MESSAGE - The Message name defined in FND_MESSAGES table. If
287 -- omitted, default message used and will pull message from
288 -- SQLERRM (meaning an exception must have been thrown).
289 -- TOKEN_# - Any tokens required in the message. Tokens go as a
290 -- a name/value. Omit if not applicable or using default.
291 --
292 -------------------------------------------------------------------------------
293 procedure REGISTER_INFORMATION (P_MODULE in VARCHAR2,
294 P_PROCEDURE in VARCHAR2,
295 P_MESSAGE in VARCHAR2,
296 TOKEN_1_NAME in VARCHAR2,
297 TOKEN_1_VALUE in VARCHAR2,
298 TOKEN_2_NAME in VARCHAR2,
299 TOKEN_2_VALUE in VARCHAR2,
300 TOKEN_3_NAME in VARCHAR2,
301 TOKEN_3_VALUE in VARCHAR2,
302 TOKEN_4_NAME in VARCHAR2,
303 TOKEN_4_VALUE in VARCHAR2,
304 TOKEN_5_NAME in VARCHAR2,
305 TOKEN_5_VALUE in VARCHAR2)
306 is
307 begin
308 ZPB_LOG.WRITE_EVENT_TR (P_MODULE||'.'||P_PROCEDURE,
309 P_MESSAGE,
310 TOKEN_1_NAME,
311 TOKEN_1_VALUE,
312 TOKEN_2_NAME,
313 TOKEN_2_VALUE,
314 TOKEN_3_NAME,
315 TOKEN_3_VALUE,
316 TOKEN_4_NAME,
317 TOKEN_4_VALUE,
318 TOKEN_5_NAME,
319 TOKEN_5_VALUE,
320 FALSE);
321 FND_MSG_PUB.ADD_DETAIL(P_MESSAGE_TYPE => FND_MSG_PUB.G_INFORMATION_MSG);
322 FND_MESSAGE.CLEAR;
323 end REGISTER_INFORMATION;
324
325 -------------------------------------------------------------------------------
326 -- REGISTER_WARNING
327 --
328 -- Procedure to register a warning. Message will be logged at level 5 -
329 -- ERROR and register it to be displayed by OA. The process will be
330 -- returned to your code.
331 --
332 -- IN:
333 -- P_MODULE - The calling package
334 -- P_PROCEDURE - The calling procedure
335 -- P_MESSAGE - The Message name defined in FND_MESSAGES table. If
336 -- omitted, default message used and will pull message from
337 -- SQLERRM (meaning an exception must have been thrown).
338 -- TOKEN_# - Any tokens required in the message. Tokens go as a
339 -- a name/value. Omit if not applicable or using default.
340 --
341 -------------------------------------------------------------------------------
342 procedure REGISTER_WARNING (P_MODULE in VARCHAR2,
346 TOKEN_1_VALUE in VARCHAR2,
343 P_PROCEDURE in VARCHAR2,
344 P_MESSAGE in VARCHAR2,
345 TOKEN_1_NAME in VARCHAR2,
347 TOKEN_2_NAME in VARCHAR2,
348 TOKEN_2_VALUE in VARCHAR2,
349 TOKEN_3_NAME in VARCHAR2,
350 TOKEN_3_VALUE in VARCHAR2,
351 TOKEN_4_NAME in VARCHAR2,
352 TOKEN_4_VALUE in VARCHAR2,
353 TOKEN_5_NAME in VARCHAR2,
354 TOKEN_5_VALUE in VARCHAR2)
355 is
356 begin
357 ZPB_LOG.WRITE_ERROR (P_MODULE||'.'||P_PROCEDURE,
358 P_MESSAGE,
359 TOKEN_1_NAME,
360 TOKEN_1_VALUE,
361 TOKEN_2_NAME,
362 TOKEN_2_VALUE,
363 TOKEN_3_NAME,
364 TOKEN_3_VALUE,
365 TOKEN_4_NAME,
366 TOKEN_4_VALUE,
367 TOKEN_5_NAME,
368 TOKEN_5_VALUE,
369 FALSE);
370 FND_MSG_PUB.ADD_DETAIL(P_MESSAGE_TYPE => FND_MSG_PUB.G_WARNING_MSG);
371 FND_MESSAGE.CLEAR;
372 end REGISTER_WARNING;
373
374 -------------------------------------------------------------------------------
375 -- SET_CONC_REQ_STATUS
376 --
377 -- Sets the return status of a concurrent request:
378 --
379 -- IN:
380 -- P_STATUS - Can be one of:
381 -- S = SUCCESS
382 -- W = WARNING
383 -- E = ERROR
384 -------------------------------------------------------------------------------
385 -- A. Budnik 05/09/2006 b 5170327 retcode conc warnings from AW dml |
386 procedure SET_CONC_REQ_STATUS (P_STATUS in VARCHAR2)
387 is
388
389 begin
390
391 if (p_status = 'E') then
392 -- DBMS_OUTPUT.PUT_LINE('E case p_status ' || p_status);
393 pv_status := 'E';
394 -- DBMS_OUTPUT.PUT_LINE('assinged pv_status ' || pv_status);
395
396 elsif (p_status = 'W') then
397
398 -- DBMS_OUTPUT.PUT_LINE('W case p_status ' || p_status);
399
400
401 if pv_status = 'S' or pv_status is NULL then
402 pv_status := 'W';
403 -- DBMS_OUTPUT.PUT_LINE('assinged pv_status ' || pv_status);
404 end if;
405
406 elsif (p_status = 'S') then
407 -- DBMS_OUTPUT.PUT_LINE('S p_status ' || p_status);
408
409 if pv_status = 'S' or pv_status is NULL then
410 pv_status := 'S';
411
412 -- DBMS_OUTPUT.PUT_LINE('assinged pv_status ' || pv_status);
413
414 end if;
415
416 else
417 null;
418 -- DBMS_OUTPUT.PUT_LINE('assinged pv_status ' || pv_status);
419 end if;
420 end SET_CONC_REQ_STATUS;
421
422
423 -------------------------------------------------------------------------------
424 -- INIT_CONC_REQ_STATUS
425 --
426 -- Initializes pkg variables to support concurrent request warning:
427 --
428 -- IN:
429 -- pv_status is intialized to NULL
430 -------------------------------------------------------------------------------
431 -- A. Budnik 05/09/2006 b 5170327 retcode conc warnings from AW dml |
432 procedure INIT_CONC_REQ_STATUS
433 is
434
435 begin
436
437 pv_status := NULL;
438 return;
439
440
441 end INIT_CONC_REQ_STATUS;
442
443
444 -------------------------------------------------------------------------------
445 -- GET_CONC_REQ_STATUS
446 --
447 -- This function returns status set by PUT_CONC_REQ_STATUS and converts it to the
448 -- retcode expected by a concurrent program.
449 -- IN:
450 -- l_conc_retcode can be:
451 -- SUCCESS: S = 0
452 -- WARNING: W = 1
453 -- ERROR: E = 2
454 -------------------------------------------------------------------------------
455 -- A. Budnik 05/09/2006 b 5170327 retcode conc warnings from AW dml |
456 function GET_CONC_REQ_STATUS return varchar2
457 AS
458
459 l_conc_retcode VARCHAR2(1);
460 begin
461
462 if (pv_status = 'E') then
463 l_conc_retcode := '2';
464 elsif (pv_status = 'W') then
465 l_conc_retcode := '1';
466 else
467 l_conc_retcode := '0';
468 end if;
469
470 return l_conc_retcode;
471
472 end GET_CONC_REQ_STATUS;
473
474
475
476 end ZPB_ERROR_HANDLER;