[Home] [Help]
PACKAGE BODY: APPS.IEM_TAG_RUN_PROC_PVT
Source
1 PACKAGE BODY IEM_TAG_RUN_PROC_PVT AS
2 /* $Header: iemvrprb.pls 120.1 2005/06/23 18:32:24 appldev shipped $ */
3 --
4 --
5 -- Purpose: Assistant api to dynamically run procedure.
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 3/26/2002 created
10 -- Liang Xia 3/18/2003 Fixed bug 2852915. DBMS_SQL.VARIABLE_VALUE
11 -- Liang Xia 11/1/2004 Fixed bug 3982076. Valid procedure name for 'TEST'
12 -- Liang Xia 04/06/2005 Fixed GSCC sql.46 ( bug 4256769 )
13 -- --------- ------ ------------------------------------------
14
15 G_PKG_NAME CONSTANT varchar2(30) :='IEM_TAG_RUN_PROC_PVT ';
16
17 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
18
19 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
20
21 -- internal DBMS_DESCRIBE.DESCRIBE_PROCEDURE variables
22 v_OverLoad DBMS_DESCRIBE.NUMBER_TABLE;
23 v_Position DBMS_DESCRIBE.NUMBER_TABLE;
24 v_Level DBMS_DESCRIBE.NUMBER_TABLE;
25 v_ArgumentName DBMS_DESCRIBE.VARCHAR2_TABLE;
26 v_DataType DBMS_DESCRIBE.NUMBER_TABLE;
27 v_DefaultValue DBMS_DESCRIBE.NUMBER_TABLE;
28 v_InOut DBMS_DESCRIBE.NUMBER_TABLE;
29 v_Length DBMS_DESCRIBE.NUMBER_TABLE;
30 v_Precision DBMS_DESCRIBE.NUMBER_TABLE;
31 v_Scale DBMS_DESCRIBE.NUMBER_TABLE;
32 v_Radix DBMS_DESCRIBE.NUMBER_TABLE;
33 v_Spare DBMS_DESCRIBE.NUMBER_TABLE;
34
35 /*GLOBAL VARIABLES FOR PRIVATE USE
36 ==================================*/
37
38
39 PROCEDURE validProcedure
40 ( p_api_version_number IN NUMBER,
41 P_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
42 p_commit IN VARCHAR2 := FND_API.G_FALSE,
43 p_ProcName IN VARCHAR2,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2)
47 IS
48 i INTEGER;
49 l_api_name varchar2(30):='validProcedure';
50 l_api_version_number number:=1.0;
51
52 v_ArgCounter NUMBER :=1;
53
54 IEM_TAG_PROC_NOT_EXIST EXCEPTION;
55 IEM_TAG_PROC_NOT_EXIST_PACK EXCEPTION;
56 IEM_TAG_PROC_INVALID EXCEPTION;
57 IEM_TAG_PROC_SYNTAX_ERR EXCEPTION;
58 IEM_TAG_PROC_NOT_EXIST_PACK_1 EXCEPTION;
59 IEM_TAG_PROC_INVALID_1 EXCEPTION;
60
61 IEM_TAG_PROC_WRONG_SIGNATURE EXCEPTION;
62
63 -- ORA-20003: the object is invalid can not described.
64 -- ORA-20001: the object is not exist.
65 PRAGMA EXCEPTION_INIT( IEM_TAG_PROC_NOT_EXIST, -20001 );
66 PRAGMA EXCEPTION_INIT( IEM_TAG_PROC_NOT_EXIST_PACK, -06564 );
67 PRAGMA EXCEPTION_INIT( IEM_TAG_PROC_NOT_EXIST_PACK_1, -06508 );
68 PRAGMA EXCEPTION_INIT( IEM_TAG_PROC_INVALID, -20003 );
69 PRAGMA EXCEPTION_INIT( IEM_TAG_PROC_INVALID, -10036 );
70 PRAGMA EXCEPTION_INIT( IEM_TAG_PROC_SYNTAX_ERR, -20004 );
71
72 -- Errorcode and errorText
73 v_ErrorCode NUMBER;
74 v_ErrorText VARCHAR2(200);
75
76 BEGIN
77 --Standard Savepoint
78 SAVEPOINT validProcedure;
79
80 -- Standard call to check for call compatibility.
81 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
82 p_api_version_number,
83 l_api_name,
84 G_PKG_NAME)
85 THEN
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87 END IF;
88
89 --Initialize the message list if p_init_msg_list is set to TRUE
90 If FND_API.to_Boolean(p_init_msg_list) THEN
91 FND_MSG_PUB.initialize;
92 END IF;
93
94 --Initialize API status return
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96
97
98 if UPPER(p_ProcName) = UPPER('test') then
99 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
100 end if;
101
102 --Actual API starts here
103 DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
104 p_ProcName,
105 null,
106 null,
107 v_Overload,
108 v_Position,
109 v_Level,
110 v_ArgumentName,
111 v_Datatype,
112 v_DefaultValue,
113 v_InOut,
114 v_Length,
115 v_Precision,
116 v_Scale,
117 v_Radix,
118 v_Spare );
119
120
121 -- Valid signature of procedure. The corrrect procedure signature is
122 -- procedure_name( key_value IN IEM_ROUTE_PUB.keyVals_tbl_type, result OUT VARCHAR2)
123 IF (v_ArgumentName.count <> 6) THEN
124 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
125 END IF;
126
127 if v_Datatype(1) <> 251 then
128 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
129 elsif v_Datatype(2) <> 250 then
130 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
131 elsif v_Datatype(3) <> 1 then
132 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
133 elsif v_Datatype(4) <> 1 then
134 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
135 elsif v_Datatype(5) <> 1 then
136 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
137 elsif v_Datatype(6) <> 1 then
138 raise IEM_TAG_PROC_WRONG_SIGNATURE ;
139 end if;
140
141 EXCEPTION
142 WHEN IEM_TAG_PROC_INVALID THEN
143 ROLLBACK TO validProcedure;
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_PROC_INVALID');
146
147 FND_MSG_PUB.ADD;
148 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
149
150 WHEN IEM_TAG_PROC_NOT_EXIST THEN
151 ROLLBACK TO validProcedure;
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_PROC_NOT_EXIST');
154
155 FND_MSG_PUB.ADD;
156 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
157
158 WHEN IEM_TAG_PROC_NOT_EXIST_PACK THEN
159 ROLLBACK TO validProcedure;
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_PROC_NOT_EXIST');
162 FND_MSG_PUB.ADD;
163 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
164
165 WHEN IEM_TAG_PROC_SYNTAX_ERR THEN
166 ROLLBACK TO validProcedure;
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_PROC_SYNTAX_ERR');
169 FND_MSG_PUB.ADD;
170 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
171
172 WHEN IEM_TAG_PROC_NOT_EXIST_PACK_1 THEN
173 ROLLBACK TO validProcedure;
174 x_return_status := FND_API.G_RET_STS_ERROR;
175 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_PROC_NOT_EXIST');
176 FND_MSG_PUB.ADD;
177 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
178
179 WHEN IEM_TAG_PROC_INVALID_1 THEN
180 ROLLBACK TO validProcedure;
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_PROC_INVALID');
183 FND_MSG_PUB.ADD;
184 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
185
186 WHEN IEM_TAG_PROC_WRONG_SIGNATURE THEN
187 ROLLBACK TO validProcedure;
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_PROC_WRONG_SIGNATURE');
190
191 FND_MSG_PUB.ADD;
192 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
193
194 WHEN FND_API.G_EXC_ERROR THEN
195 ROLLBACK TO validProcedure;
196 x_return_status := FND_API.G_RET_STS_ERROR ;
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count,p_data => x_msg_data);
199
200
201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
202 ROLLBACK TO validProcedure;
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
204 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
205
206
207 WHEN OTHERS THEN
208 ROLLBACK TO validProcedure;
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
211 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
212 END IF;
213
214 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
215 END;
216
217
218
219 PROCEDURE run_Procedure (
220 p_api_version_number IN NUMBER,
221 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
222 p_commit IN VARCHAR2 := FND_API.G_FALSE,
223 p_procedure_name IN VARCHAR2,
224 p_key_value IN IEM_TAGPROCESS_PUB.keyVals_tbl_type,
225 x_result OUT NOCOPY VARCHAR2,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2
229 ) is
230 l_api_name VARCHAR2(255):='run_Procedure';
231 l_api_version_number NUMBER:=1.0;
232 l_seq_id NUMBER;
233
234 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
235 l_msg_count NUMBER := 0;
236 l_msg_data VARCHAR2(2000);
237
238 l_proc_name VARCHAR2(256);
239 l_ProcCall VARCHAR2(500);
240 l_result VARCHAR2(256) := null;
241
242 l_Cursor NUMBER;
243 l_NumRows NUMBER;
244 l_para_out_name VARCHAR2(500);
245
246 l_IEM_INVALID_PROCEDURE EXCEPTION;
247 logMessage VARCHAR2(2000);
248 -- l_error_text varchar2(2000);
249 BEGIN
250 -- Standard Start of API savepoint
251 SAVEPOINT run_Procedure_PVT;
252
253 -- Standard call to check for call compatibility.
254 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
255 p_api_version_number,
256 l_api_name,
257 G_PKG_NAME)
258 THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261
262
263 -- Initialize message list if p_init_msg_list is set to TRUE.
264 IF FND_API.to_Boolean( p_init_msg_list )
265 THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269
270 -- Initialize API return status to SUCCESS
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272
273 -- Actual API begins here
274
275 -- Valid procedure first
276 l_proc_name := LTRIM(RTRIM(p_procedure_name));
277
278 IF l_proc_name is NOT NULL THEN
279
280 IEM_TAG_RUN_PROC_PVT.validProcedure(
281 p_api_version_number => P_Api_Version_Number,
282 p_init_msg_list => FND_API.G_FALSE,
283 p_commit => P_Commit,
284 p_ProcName => l_proc_name,
285 x_return_status => l_return_status,
286 x_msg_count => l_msg_count,
287 x_msg_data => l_msg_data
288 );
289 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
290 raise l_IEM_INVALID_PROCEDURE;
291 end if;
292
293 END IF;
294
295 -- Populate the key-value glabe variable
296 G_key_value.delete;
297
298 for i in 1..p_key_value.count loop
299 G_key_value(i).key := p_key_value(i).key;
300 G_key_value(i).value := p_key_value(i).value;
301 G_key_value(i).datatype := p_key_value(i).datatype;
302 end loop;
303
304 -- Get the name of the OUT parameter
305 if v_ArgumentName(6) is not null then
306 l_para_out_name := v_ArgumentName(6);
307 end if;
308
309 l_ProcCall := 'Begin '|| l_proc_name || '(IEM_TAG_RUN_PROC_PVT.G_key_value, :' ||l_para_out_name||'); END;';
310
311 --Open the cursor and parse the statement.
312 l_Cursor := DBMS_SQL.OPEN_CURSOR;
313 DBMS_SQL.PARSE(l_Cursor, l_ProcCall, DBMS_SQL.native);
314
315 DBMS_SQL.BIND_VARIABLE(l_Cursor,l_para_out_name,
316 l_result, 500);
317
318 --Execute the procedure.
319 l_NumRows := DBMS_SQL.EXECUTE(l_Cursor);
320
321 DBMS_SQL.VARIABLE_VALUE(l_Cursor, l_para_out_name, l_result);
322
323 x_result := l_result;
324
325 DBMS_SQL.close_cursor( l_Cursor );
326
327 -- Standard Check Of p_commit.
328 IF FND_API.To_Boolean(p_commit) THEN
329 COMMIT WORK;
330 END IF;
331
332 -- Standard callto get message count and if count is 1, get message info.
333 FND_MSG_PUB.Count_And_Get
334 ( p_count => x_msg_count,
335 p_data => x_msg_data
336 );
337
338 EXCEPTION
339 WHEN l_IEM_INVALID_PROCEDURE THEN
340 ROLLBACK TO run_Procedure_PVT;
341
342 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
343 logMessage := '[Invalid procedure: ' || l_proc_name|| ']';
344 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAG_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
345 end if;
346 x_return_status := FND_API.G_RET_STS_ERROR ;
347
348 WHEN FND_API.G_EXC_ERROR THEN
349 ROLLBACK TO run_Procedure_PVT;
350 x_return_status := FND_API.G_RET_STS_ERROR ;
351 FND_MSG_PUB.Count_And_Get
352 ( p_count => x_msg_count,
353 p_data => x_msg_data
354 );
355 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
356 logMessage := '[FND_API.G_EXC_ERROR happened in RUN_PROCEDURE - ' || l_proc_name|| ' error:'||sqlerrm||']';
357 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAG_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
358 end if;
359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
360 ROLLBACK TO run_Procedure_PVT;
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
362 FND_MSG_PUB.Count_And_Get
363 ( p_count => x_msg_count,
364 p_data => x_msg_data
365 );
366 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
367 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR happened in RUN_PROCEDURE - ' || l_proc_name|| ' error:'||sqlerrm||']';
368 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAG_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
369 end if;
370 WHEN OTHERS THEN
371 ROLLBACK TO run_Procedure_PVT;
372 x_return_status := FND_API.G_RET_STS_ERROR;
373
374 IF FND_MSG_PUB.Check_Msg_Level
375 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
376 THEN
377 FND_MSG_PUB.Add_Exc_Msg
378 ( G_PKG_NAME ,
379 l_api_name
380 );
381 END IF;
382
383 FND_MSG_PUB.Count_And_Get
384 ( p_count => x_msg_count,
385 p_data => x_msg_data
386 );
387
388
389 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
390 logMessage := '[OTHER exception happened in RUN_PROCEDURE - ' || l_proc_name|| ' error:'||sqlerrm||']';
391 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAG_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
392 end if;
393 -- dbms_output.put_line('Exception in Run Procedure happened ' || SUBSTR (SQLERRM , 1 , 100));
394 END run_Procedure;
395
396
397
398 procedure dummy_procedure( key_value IN IEM_TAGPROCESS_PUB.keyVals_tbl_type,
399 result OUT NOCOPY VARCHAR2)
400 is
401 begin
402 /*
403 for i in 1..key_value.count loop
404 dbms_output.put_line('Key is : ' ||key_value(i).key);
405 dbms_output.put_line('Value is : ' ||key_value(i).value);
406 dbms_output.put_line('Type is : ' ||key_value(i).datatype);
407 end loop;
408 */
409 result := 'SUCCESS!';
410
411 exception
412 when others then
413 null;
414 end;
415
416 procedure dummy_procedure2( key_value IN IEM_TAGPROCESS_PUB.keyVals_tbl_type,
417 result OUT NOCOPY VARCHAR2)
418 is
419 begin
420 /*
421 for i in 1..key_value.count loop
422 dbms_output.put_line('Key is : ' ||key_value(i).key);
423 dbms_output.put_line('Value is : ' ||key_value(i).value);
424 dbms_output.put_line('Type is : ' ||key_value(i).datatype);
425 end loop;
426 */
427 result := 'SUCCESS2 454325324!';
428
429 exception
430 when others then
431 null;
432 end;
433 END IEM_TAG_RUN_PROC_PVT;