[Home] [Help]
PACKAGE BODY: APPS.GMD_UTILITY_PKG
Source
1 PACKAGE BODY GMD_UTILITY_PKG AS
2 /* $Header: GMDUTLPB.pls 120.2 2006/08/22 07:45:14 rlnagara noship $ */
3 PROCEDURE check_if_oprnd_exist(x_exptab IN exptab,
4 x_operand IN VARCHAR2,
5 x_value OUT NOCOPY NUMBER,
6 x_return_status OUT NOCOPY VARCHAR2) IS
7 l_exist BOOLEAN := FALSE;
8 BEGIN
9 x_return_status := FND_API.G_RET_STS_SUCCESS;
10 FOR i IN 1..x_exptab.count LOOP
11 IF (x_exptab(i).pvalue_type = 'O') THEN
12 IF (x_exptab(i).poperand = x_operand) THEN
13 x_value := x_exptab(i).pvalue;
14 l_exist := TRUE;
15 RETURN;
16 END IF;
17 END IF;
18 END LOOP;
19 IF (not l_exist) THEN
20 x_value := null;
21 x_return_status := FND_API.G_RET_STS_ERROR;
22 END IF;
23
24 EXCEPTION
25 WHEN OTHERS THEN
26 x_return_status := FND_API.G_RET_STS_ERROR;
27 END check_if_oprnd_exist;
28
29 PROCEDURE parse(x_exp IN VARCHAR2,
30 x_exptab OUT NOCOPY exptab,
31 x_return_status OUT NOCOPY VARCHAR2) IS
32 q_count integer := 0;
33 ob_count integer := 0;
34 cb_count integer := 0;
35 l_exptab exptab;
36 x_len INTEGER;
37 x_value NUMBER;
38 x_result NUMBER;
39 BEGIN
40 FND_MSG_PUB.initialize;
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 IF (x_exp IS NOT NULL) THEN
43 x_len := length(x_exp);
44 FOR i IN 1..x_len LOOP
45 IF (substr(x_exp,i,1) = '"') THEN
46 q_count := q_count + 1;
47 ELSIF (substr(x_exp,i,1) = '(') THEN
48 ob_count := ob_count + 1;
49 ELSIF (substr(x_exp,i,1) = ')') THEN
50 cb_count := cb_count + 1;
51 END IF;
52 END LOOP;
53
54 IF q_count > 0 THEN
55 SELECT MOD(q_count,2) into x_result from sys.dual;
56 IF x_result > 0 THEN
57 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_OPERANDS');
58 FND_MSG_PUB.ADD;
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 RETURN;
61 END IF;
62 END IF;
63 IF (ob_count <> cb_count) THEN
64 FND_MESSAGE.SET_NAME('GMD','GMD_WRONG_PARENTHESIS');
65 FND_MSG_PUB.ADD;
66 x_return_status := FND_API.G_RET_STS_ERROR;
67 RETURN;
68 END IF;
69 tokenize_exp(x_exp,x_exptab);
70 IF (x_exptab.count > 0 ) THEN
71 evaluate_exp(x_exptab,
72 TRUE,
73 x_value,
74 x_return_status);
75 END IF;
76 END IF;
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 x_return_status := FND_API.G_RET_STS_ERROR;
81 END parse;
82
83
84 PROCEDURE tokenize_exp(pexp IN VARCHAR2,
85 x_exptab OUT NOCOPY exptab) IS
86 i NUMBER := 1;
87 j NUMBER := 1;
88 k NUMBER := 1;
89 len NUMBER := 0;
90 x_exp VARCHAR2(4000);--BUG#3173796 Increased to 4000 from 2000
91 x_operand VARCHAR2(200);--BUG#3256165 Increased from 50 to 200
92 x_expression VARCHAR2(4000);--BUG#3173796 Increased to 4000 from 2000
93 x_value NUMBER := 1;
94 l_value NUMBER;
95 l_return_status VARCHAR2(3);
96 BEGIN
97 x_exp := pexp;
98 len := length(x_exp);
99 WHILE i <= len LOOP
100 IF (substr(x_exp,i,1) = '"') THEN
101 j := i + 1;
102 IF (j > len) THEN
103 i := i + 1;
104 END IF;
105 WHILE j <= len LOOP
106 IF (substr(x_exp,j,1) <> '"') THEN
107 x_operand := x_operand||substr(x_exp,j,1);
108 j:= j+1;
109 ELSE
110 i := j+1;
111 j:= 0;
112 IF (x_operand IS NOT NULL) THEN
113 -- Check if this is a repeated operand
114 check_if_oprnd_exist(x_exptab,x_operand,l_value,l_return_Status);
115 x_exptab(k).poperand := x_operand;
116 x_exptab(k).pvalue := nvl(l_value,x_value);
117 x_exptab(k).pvalue_type := 'O';
118 k := k + 1;
119 x_operand := null;
120 x_value := x_value + 1;
121 END IF;
122 EXIT;
123 END IF;
124 END LOOP;
125 ELSE
126 x_exptab(k).poperand := substr(x_exp,i,1);
127 x_exptab(k).pvalue := NULL;
128 x_exptab(k).pvalue_type := 'S';
129 k := k + 1;
130 i := i + 1;
131 END IF;
132 END LOOP;
133 END tokenize_exp;
134
135
136 PROCEDURE evaluate_exp(pexptab IN exptab,
137 pexp_test IN BOOLEAN,
138 x_value OUT NOCOPY NUMBER,
139 x_return_status OUT NOCOPY VARCHAR2) IS
140 x_result NUMBER;
141 x_expression VARCHAR2(4000);--BUG#3173796 Increased to 4000 from 2000
142 INVALID_EXPRESSION EXCEPTION;
143 BEGIN
144 x_return_status := FND_API.G_RET_STS_SUCCESS;
145 IF (pexptab.count = 0) THEN
146 FND_MESSAGE.SET_NAME('GMD','GMD_NO_EXPRESSION');
147 FND_MSG_PUB.ADD;
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 RETURN;
150 END IF;
151
152 FOR i in 1..pexptab.count LOOP
153 IF (pexptab(i).pvalue_type = 'S') THEN
154 x_expression := x_expression||pexptab(i).poperand;
155 ELSIF(pexptab(i).pvalue_type = 'O') THEN
156
157 -- B3199585 START
158 -- x_expression := x_expression||' '||to_char(nvl(pexptab(i).pvalue,1));
159 if pexptab(i).pvalue IS NOT NULL THEN
160 x_expression := x_expression||' '||to_char(pexptab(i).pvalue);
161 ELSE
162 x_expression := x_expression||' '|| 'NULL';
163 END IF;
164 -- B3199585 END
165 END IF;
166
167 END LOOP;
168 -- dbms_output.put_line(' expression before execute '||x_expression);
169 gmd_debug.put_line ('Expression to be executed:' || x_expression);
170 --RLNAGARA Bug5473185 Pass in pexp_test from input parameter so that correct error handling occurs
171 -- No longer hard code a TRUE for this input parameter.
172 execute_exp(x_expression,pexp_test,x_value,x_return_Status);
173 --dbms_output.put_line(' after execute '||to_char(x_value)||' '||x_return_Status);
174 EXCEPTION
175 WHEN INVALID_EXPRESSION THEN
176 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_EXPRESSION');
177 FND_MSG_PUB.ADD;
178 x_return_status := FND_API.G_RET_STS_ERROR;
179 WHEN OTHERS THEN
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 END evaluate_exp;
182
183
184 PROCEDURE execute_exp(pexp IN VARCHAR2,
185 pexp_test IN BOOLEAN,
186 x_result OUT NOCOPY NUMBER,
187 x_return_status OUT NOCOPY VARCHAR2) IS
188 cur_hdl INTEGER;
189 stmt_str VARCHAR2(4000);--BUG#3173796 Increased to 4000 from 200
190 rows_processed BINARY_INTEGER;
191 l_result NUMBER;
192 l_dummy INTEGER;
193 format_profile VARCHAR2(30);
194 BEGIN
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 --RLNAGARA Bug 5006158
198
199 format_profile := FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS');
200 IF substr(format_profile,0,1) <> '.' THEN
201 stmt_str := 'SELECT '||replace(pexp,substr(format_profile,0,1),'.')||' from dual ';
202 ELSE
203 stmt_str := 'SELECT '||pexp||' from dual ';
204 END IF;
205
206 --RLNAGARA Bug5006158
207
208 --dbms_output.put_line(pexp);
209 -- open cursor
210 cur_hdl := dbms_sql.open_cursor;
211 -- parse cursor
212 dbms_sql.parse(cur_hdl, stmt_str,dbms_sql.native);
213 DBMS_SQL.DEFINE_COLUMN(cur_hdl,1,l_result);
214 l_dummy := DBMS_SQL.EXECUTE(cur_hdl);
215 IF (DBMS_SQL.FETCH_ROWS(cur_hdl) <> 0) THEN
216 -- dbms_output.put_line(' row fetched ');
217 DBMS_SQL.COLUMN_VALUE(cur_hdl,1,l_result);
218 --dbms_output.put_line(' value is '||to_char(l_result));
219 END IF;
220 -- close cursor
221 dbms_sql.close_cursor(cur_hdl);
222 x_result := l_result;
223 EXCEPTION
224 WHEN OTHERS THEN
225
226 IF (SQLCODE = -1476 AND NOT pexp_test) THEN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 FND_MESSAGE.SET_NAME('GMD','GMD_EVAL_DIVIDE_BY_ZERO');
229 FND_MSG_PUB.ADD;
230 ELSIF(SQLCODE <> -1476) THEN
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_EXPRESSION');
233 FND_MSG_PUB.ADD;
234 ELSE
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 END IF;
237 --dbms_output.put_line(SQLCODE||'-'||SQLERRM);
238 END execute_exp;
239
240 PROCEDURE variable_value(pvar_name IN VARCHAR2,
241 pvar_value IN NUMBER,
242 p_exptab IN OUT NOCOPY exptab,
243 x_return_Status OUT NOCOPY VARCHAR2) IS
244 l_var_assign NUMBER := 0;
245 BEGIN
246 x_return_status := FND_API.G_RET_STS_SUCCESS;
247 FND_MSG_PUB.INITIALIZE;
248
249 -- B3199585
250 -- IF (pvar_name IS NULL OR pvar_value IS NULL) THEN
251 -- x_return_status := FND_API.G_RET_STS_ERROR;
252 -- FND_MESSAGE.SET_NAME('GMD','GMD_NO_PARAMETER_VALUES');
253 -- FND_MESSAGE.SET_TOKEN('VARNAME',pvar_name);
254 -- FND_MSG_PUB.ADD;
255 -- RETURN;
256 -- END IF;
257
258 IF (p_exptab.count = 0) THEN
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 FND_MESSAGE.SET_NAME('GMD','GMD_NO_EXPRESSION');
261 FND_MSG_PUB.ADD;
262 RETURN;
263 END IF;
264
265 FOR i in 1..p_exptab.count LOOP
266 IF (p_exptab(i).pvalue_type = 'O') THEN
267 IF (p_exptab(i).poperand = pvar_name) THEN
268 p_exptab(i).pvalue := pvar_value;
269 l_var_assign := l_var_assign + 1;
270 END IF;
271 END IF;
272 END LOOP;
273 IF (l_var_assign = 0) THEN
274 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_VARIABLE');
275 FND_MESSAGE.SET_TOKEN('VARNAME',pvar_name);
276 FND_MSG_PUB.ADD;
277 x_return_Status := FND_API.G_RET_STS_ERROR;
278 END IF;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 END variable_value;
284
285 END GMD_UTILITY_PKG;