1 PACKAGE BODY GMF_MSG_PKG AS
2 /* $Header: gmfmesgb.pls 115.5 2002/11/11 00:40:09 rseshadr Exp $ */
3 /*hb***********************************************************************
4 * External
5 * Procedure: get_msg_from_code
6 *
7 * Purpose: Retrieve message_id and message_text from sy_mesg_table
8 * for a given message_code and op_code. The op_code is
9 * used to determine the current session's lang_code.
10 *
11 * Forms: Each publically accessable procedure for extracting
12 * messages has 2 forms. This is done by using the
13 * overloading technique provided in PL/SQL.
14 * 1- Messages without substitution parameters (eg. "%s").
15 * 2- Declaration for messages with substitution variables
16 *
17 * Parameters: The in/out disposition of the first three paramters will
18 depend on which procedure is called. The parameter after
19 * the "_from_" in the procedure name will be an "in" only
20 * parameter and the other two will be "in out".
21 *
22 * po_message_id [in out] or [in] varchar2
23 * pi_message_code [in out] or [in] varchar2
24 * po_message_text [in out] or [in] varchar2
25 * pi_op_code in varchar2
26 * pi_svar in SubstituteTabTyp ***Form 2 only***
27 * po_error_status out number
28 *
29 * Returns: po_error_ststus
30 * 0 == Success
31 * 1 == Operator Code Not Found
32 * 2 == Language Code Not Found
33 *
34 * Jatinder Gogna -05/05/99 -Changed substr and instr to substrb and instrb
35 * as per AOL standards.
36 *
37 * Rajesh Kulkarni 10/27/99 Take the substrb for message_text in a
38 * cursor as out var po_message_text is restricted to 512. Also vars
39 * v_message_text and v_old_message_text are made to 512 from 255. B1043070
40 *
41 * Venkat Chukkapalli 08/16/01 B1933961 Removed reference to APPS schema.
42 * RajaSekhar 30-OCT-2002 Bug#2641405 Added NOCOPY hint.
43 ****************************************************************hf*/
44 procedure get_msg_from_code
45 (po_message_id out NOCOPY number,
46 pi_message_code in varchar2,
47 po_message_text out NOCOPY varchar2,
48 pi_op_code in varchar2,
49 po_error_status out NOCOPY number)
50 is
51 v_message_code fnd_new_messages.message_name%TYPE;
52 v_error_status number;
53 cursor read_mesg_rec
54 (v_message_code_csr char)
55 is
56 select substrb(message_text,1,512)
57 from fnd_new_messages
58 where language_code = nvl(userenv('LANG'), C_DEFAULT_LANG)
59 and message_name = v_message_code_csr;
60 /* and delete_mark = 0; */
61 begin
62 v_message_code := pi_message_code;
63 /*
64 * Now we try to get the message
65 */
66 if (not read_mesg_rec%ISOPEN) then
67 open read_mesg_rec(v_message_code);
68 end if;
69 fetch read_mesg_rec
70 into po_message_text;
71 if (read_mesg_rec%NOTFOUND) then
72 po_message_text := v_message_code ;
73 end if;
74 if (read_mesg_rec%ISOPEN) then
75 close read_mesg_rec;
76 end if;
77 exception
78 when others then
79 po_message_text := v_message_code;
80 end; /* Form 1: get_msg_from_code */
81 /*hb*********************************************************************
82 * External
83 * Procedure: get_msg_from_code - Form 2
84 *
85 * Purpose: Sames as form 1 but allows use of substitution variables
86 * in the message_text. The addtional parameter pi_svar
87 * is required. Variables must be in the form "%s#" where
88 * # is sequentially assigned number starting at 1.
89 * Code logic allows up to 99 sub vars in text. We should
90 * hope that we don't get there!
91 * Note that "%s" without trailing # won't work properly
92 * here.
93 **********************************************************************hf*/
94 procedure get_msg_from_code
95 (po_message_id out NOCOPY number,
96 pi_message_code in varchar2,
97 po_message_text out NOCOPY varchar2,
98 pi_op_code in varchar2,
99 pi_svar in SubstituteTabTyp,
100 po_error_status out NOCOPY number)
101 is
102 v_message_id fnd_new_messages.message_number%TYPE;
103 v_message_text varchar2(512);
104 v_old_message_text varchar2(512);
105 v_error_status number;
106 v_i number default 1;
107 v_j number;
108 v_old_j number default -1;
109 v_len number default 3;
110 v_str varchar2(5);
111 ex_form1_failure exception;
112 /*
113 * WARNING: The hardcoded -20000 is required but must be
114 * in sync with constant "C_GETMSG_NO_OPCODE" if we ever
115 * get info from database.
116 */
117 pragma EXCEPTION_INIT(ex_form1_failure, -20000);
118 begin
119 /*
120 Next line calls Form 1 of proc --why recode
121 */
122 get_msg_from_code (v_message_id,
123 pi_message_code,
124 v_message_text,
125 pi_op_code,
126 v_error_status);
127 po_message_id := v_message_id; /* Get this out of the way */
128 po_error_status := v_error_status;
129 /*
130 * Copy-append each value to temp var
131 * Exception raised when end of array reached
132 */
133 loop
134 /* find starting point of %s# flag */
135 v_j := instrb(lower(v_message_text), '%s');
136 if (v_j > 0 ) then
137 v_str := '%s'||to_char(v_i);
138 /*
139 * Actual sustitution
140 * - Possible error if substitution causes length
141 * greater than message_text%TYPE.
142 * The error in this case is ORA-6502
143 */
144 v_old_message_text := v_message_text;
145 v_message_text := replace(v_message_text, v_str,pi_svar(v_i));
146 exit when v_old_message_text = v_message_text;
147 else
148 exit; /* "%s" not found-return what we have */
149 end if;
150 v_i := v_i + 1;
151 end loop;
152 po_message_text := v_message_text;
153 exception
154 /*
155 * Actually an abnormal ending.
156 * We went past end of pi_svar array.
157 * Time to quit - even if more %s found
158 */
159 when others then
160 po_message_text := v_message_text;
161 end; /* Form 2: get_msg_from_code */
162 end; /* gmmsg_pkg */
[Home] [Help]