1 PACKAGE BODY PA_EXCEPTION_REASONS_PUB AS
2 /* $Header: PAXEXPRB.pls 115.0 99/07/16 15:24:06 porting ship $ */
3
4 FUNCTION get_exception_text
5 (x_exception_type IN VARCHAR2,
6 x_exception_code IN VARCHAR2,
7 x_exception_reason IN VARCHAR2,
8 x_return_type IN VARCHAR2)
9 RETURN VARCHAR2 IS
10
11 ls_exception_text VARCHAR2(200);
12 BEGIN
13
14 -- If a reason is already there in the table (the reason text is there and not a code)
15 -- and if a reason is being requested, then nothing has to be done
16
17 IF x_return_type = 'R' and x_exception_reason is not null
18 THEN
19 return x_exception_reason;
20 END IF;
21
22 -- If a reason is there, join to get the corrective action. If the reason is not
23 -- there, then join with the code to get a corrective action. If both are not
24 -- specified, then return undefined
25
26 SELECT decode(x_return_type, 'R', exception_reason, corrective_action)
27 INTO ls_exception_text
28 FROM pa_exception_reasons
29 WHERE decode(x_exception_reason, null, x_exception_code, x_exception_reason) =
30 decode(x_exception_reason, null, exception_code, exception_reason)
31 AND x_exception_type = pa_exception_reasons.exception_category;
32
33 return ls_exception_text;
34
35 EXCEPTION
36 WHEN NO_DATA_FOUND
37 THEN
38 SELECT decode(x_return_type, 'R', exception_reason, corrective_action)
39 INTO ls_exception_text
40 FROM pa_exception_reasons
41 WHERE exception_category = 'UNDEFINED'
42 AND exception_code = 'UNDEFINED';
43 return ls_exception_text;
44
45 END get_exception_text;
46
47 END PA_EXCEPTION_REASONS_PUB;