[Home] [Help]
PACKAGE BODY: APPS.IEM_ROUTE_RUN_PROC_PVT
Source
1 PACKAGE BODY IEM_ROUTE_RUN_PROC_PVT AS
2 /* $Header: iemvrunb.pls 120.1 2005/06/23 18:18:04 appldev noship $ */
3 --
4 --
5 -- Purpose: Assistant api to dynamically run procedure.
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 5/29/2002 created
10 -- Liang Xia 12/6/2002 Fixed GSCC warning: NOCOPY, no G_MISS ..
11 -- Liang Xia 12/13/2002 Shipped dummy procedures for testing Dyanmic Classification,
12 -- Route and Excecute External Procedure/workflow
13 -- Liang Xia 03/17/2003 Fixed bug 2852915 DBMS_SQL.VARIABLE_VALUE
14 -- Liang Xia 7/24/2004 Fixed bug 3764934 Performance ( closing cursor )
15 -- Liang Xia 11/16/2004 Fixed bug 3982076 procedure name 'test' validation
16 -- Liang Xia 04/06/2005 Fixed GSCC sql.46 ( bug 4256769 )
17 -- --------- ------ ------------------------------------------
18
19 G_PKG_NAME CONSTANT varchar2(30) :='IEM_ROUTE_RUN_PROC_PVT ';
20
21 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
22
23 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
24
25 -- internal DBMS_DESCRIBE.DESCRIBE_PROCEDURE variables
26 v_OverLoad DBMS_DESCRIBE.NUMBER_TABLE;
27 v_Position DBMS_DESCRIBE.NUMBER_TABLE;
28 v_Level DBMS_DESCRIBE.NUMBER_TABLE;
29 v_ArgumentName DBMS_DESCRIBE.VARCHAR2_TABLE;
30 v_DataType DBMS_DESCRIBE.NUMBER_TABLE;
31 v_DefaultValue DBMS_DESCRIBE.NUMBER_TABLE;
32 v_InOut DBMS_DESCRIBE.NUMBER_TABLE;
33 v_Length DBMS_DESCRIBE.NUMBER_TABLE;
34 v_Precision DBMS_DESCRIBE.NUMBER_TABLE;
35 v_Scale DBMS_DESCRIBE.NUMBER_TABLE;
36 v_Radix DBMS_DESCRIBE.NUMBER_TABLE;
37 v_Spare DBMS_DESCRIBE.NUMBER_TABLE;
38
39 /*GLOBAL VARIABLES FOR PRIVATE USE
40 ==================================*/
41
42 PROCEDURE validProcedure
43 ( p_api_version_number IN NUMBER,
44 P_init_msg_list IN VARCHAR2 := null,
45 p_commit IN VARCHAR2 := null,
46 p_ProcName IN VARCHAR2,
47 p_return_type IN VARCHAR2,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2)
51 IS
52 i INTEGER;
53 l_api_name varchar2(30):='validProcedure';
54 l_api_version_number number:=1.0;
55
56 v_ArgCounter NUMBER :=1;
57
58 IEM_ROUT_PROC_NOT_EXIST EXCEPTION;
59 IEM_ROUT_PROC_NOT_EXIST_PACK EXCEPTION;
60 IEM_ROUT_PROC_INVALID EXCEPTION;
61 IEM_ROUT_PROC_SYNTAX_ERR EXCEPTION;
62 IEM_ROUT_PROC_NOT_EXIST_PACK_1 EXCEPTION;
63 IEM_ROUT_PROC_INVALID_1 EXCEPTION;
64 IEM_ROUT_PROC_WRONG_SIGN_NUM EXCEPTION;
65 IEM_ROUT_PROC_WRONG_SIGN_STG EXCEPTION;
66 l_IEM_UNKNOWN_RETURN_TYPE EXCEPTION;
67
68 -- ORA-20003: the object is invalid can not described.
69 -- ORA-20001: the object is not exist.
70 PRAGMA EXCEPTION_INIT( IEM_ROUT_PROC_NOT_EXIST, -20001 );
71 PRAGMA EXCEPTION_INIT( IEM_ROUT_PROC_NOT_EXIST_PACK, -06564 );
72 PRAGMA EXCEPTION_INIT( IEM_ROUT_PROC_NOT_EXIST_PACK_1, -06508 );
73 PRAGMA EXCEPTION_INIT( IEM_ROUT_PROC_INVALID, -20003 );
74 PRAGMA EXCEPTION_INIT( IEM_ROUT_PROC_INVALID, -10036 );
75 PRAGMA EXCEPTION_INIT( IEM_ROUT_PROC_SYNTAX_ERR, -20004 );
76
77 -- Errorcode and errorText
78 v_ErrorCode NUMBER;
79 v_ErrorText VARCHAR2(200);
80
81 BEGIN
82 --Standard Savepoint
83 SAVEPOINT validProcedure;
84
85 -- Standard call to check for call compatibility.
86 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
87 p_api_version_number,
88 l_api_name,
89 G_PKG_NAME)
90 THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 --Initialize the message list if p_init_msg_list is set to TRUE
95 If FND_API.to_Boolean(p_init_msg_list) THEN
96 FND_MSG_PUB.initialize;
97 END IF;
98
99 --Initialize API status return
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101
102 if UPPER(p_ProcName) = UPPER('test') then
103 if p_return_type='IEMNNUMBER' then
104 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
105 else
106 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
107 end if;
108 end if;
109
110 --Actual API starts here
111 DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
112 p_ProcName,
113 null,
114 null,
115 v_Overload,
116 v_Position,
117 v_Level,
118 v_ArgumentName,
119 v_Datatype,
120 v_DefaultValue,
121 v_InOut,
122 v_Length,
123 v_Precision,
124 v_Scale,
125 v_Radix,
126 v_Spare );
127
128
129 -- Valid signature of procedure. The corrrect procedure signature is
130 -- procedure_name( key_value IN IEM_ROUTE_PUB.keyVals_tbl_type, result OUT VARCHAR2)
131
132 if p_return_type='IEMNNUMBER' then
133 IF (v_ArgumentName.count <> 6) THEN
134 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
135 elsif v_Datatype(6) <> 2 then
136 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
137 elsif v_Datatype(1) <> 251 then
138 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
139 elsif v_Datatype(2) <> 250 then
140 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
141 elsif v_Datatype(3) <> 1 then
142 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
143 elsif v_Datatype(4) <> 1 then
144 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
145 elsif v_Datatype(5) <> 1 then
146 raise IEM_ROUT_PROC_WRONG_SIGN_NUM ;
147 end if;
148 elsif p_return_type='IEMSVARCHAR2' then
149 if (v_ArgumentName.count <> 6) THEN
150 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
151 elsif v_Datatype(6) <> 1 then
152 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
153 elsif v_Datatype(1) <> 251 then
154 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
155 elsif v_Datatype(2) <> 250 then
156 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
157 elsif v_Datatype(3) <> 1 then
158 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
159 elsif v_Datatype(4) <> 1 then
160 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
161 elsif v_Datatype(5) <> 1 then
162 raise IEM_ROUT_PROC_WRONG_SIGN_STG ;
163 end if;
164 else
165 raise l_IEM_UNKNOWN_RETURN_TYPE ;
166 end if;
167
168
169 EXCEPTION
170 WHEN l_IEM_UNKNOWN_RETURN_TYPE THEN
171 ROLLBACK TO validProcedure;
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 FND_MESSAGE.SET_NAME('IEM', 'l_IEM_UNKNOWN_RETURN_TYPE');
174
175 FND_MSG_PUB.ADD;
176 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
177
178 WHEN IEM_ROUT_PROC_INVALID THEN
179 ROLLBACK TO validProcedure;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_INVALID');
182 FND_MSG_PUB.ADD;
183 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
184 WHEN IEM_ROUT_PROC_NOT_EXIST THEN
185 ROLLBACK TO validProcedure;
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_NOT_EXIST');
188
189 FND_MSG_PUB.ADD;
190 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
191
192 WHEN IEM_ROUT_PROC_NOT_EXIST_PACK THEN
193 ROLLBACK TO validProcedure;
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_NOT_EXIST');
196 FND_MSG_PUB.ADD;
197 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
198
199 WHEN IEM_ROUT_PROC_SYNTAX_ERR THEN
200 ROLLBACK TO validProcedure;
201 x_return_status := FND_API.G_RET_STS_ERROR;
202 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_SYNTAX_ERR');
203 FND_MSG_PUB.ADD;
204 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
205
206 WHEN IEM_ROUT_PROC_NOT_EXIST_PACK_1 THEN
207 ROLLBACK TO validProcedure;
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_NOT_EXIST');
210 FND_MSG_PUB.ADD;
211 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
212
213 WHEN IEM_ROUT_PROC_INVALID_1 THEN
214 ROLLBACK TO validProcedure;
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_INVALID');
217 FND_MSG_PUB.ADD;
218 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
219
220 WHEN IEM_ROUT_PROC_WRONG_SIGN_STG THEN
221 ROLLBACK TO validProcedure;
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_WRONG_SIGN_STG');
224
225 FND_MSG_PUB.ADD;
226 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
227
228 WHEN IEM_ROUT_PROC_WRONG_SIGN_NUM THEN
229 ROLLBACK TO validProcedure;
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUT_PROC_WRONG_SIGN_NUM');
232
233 FND_MSG_PUB.ADD;
234 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
235
236 WHEN FND_API.G_EXC_ERROR THEN
237 ROLLBACK TO validProcedure;
238 x_return_status := FND_API.G_RET_STS_ERROR ;
239 FND_MSG_PUB.Count_And_Get
240 ( p_count => x_msg_count,p_data => x_msg_data);
241
242
243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
244 ROLLBACK TO validProcedure;
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
246 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
247
248
249 WHEN OTHERS THEN
250 ROLLBACK TO validProcedure;
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
253 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
254 END IF;
255
256 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
257 END;
258
259
260 PROCEDURE run_Procedure (
261 p_api_version_number IN NUMBER,
262 p_init_msg_list IN VARCHAR2 := null,
263 p_commit IN VARCHAR2 := null,
264 p_procedure_name IN VARCHAR2,
265 p_key_value IN IEM_ROUTE_PUB.keyVals_tbl_type,
266 p_param_type IN VARCHAR2,
267 x_result OUT NOCOPY VARCHAR2,
268 x_return_status OUT NOCOPY VARCHAR2,
269 x_msg_count OUT NOCOPY NUMBER,
270 x_msg_data OUT NOCOPY VARCHAR2
271 ) is
272 l_api_name VARCHAR2(255):='run_Procedure';
273 l_api_version_number NUMBER:=1.0;
274 l_seq_id NUMBER;
275
276 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
277 l_msg_count NUMBER := 0;
278 l_msg_data VARCHAR2(2000);
279
280 l_proc_name VARCHAR2(256);
281 l_ProcCall VARCHAR2(500);
282 l_result_n NUMBER;
283 l_result_s VARCHAR2(256);
284
285 l_Cursor NUMBER;
286 l_NumRows NUMBER;
287 l_para_out_name VARCHAR2(500);
288
289 l_IEM_INVALID_PROCEDURE EXCEPTION;
290 l_IEM_INVALID_PARAM_TYPE EXCEPTION;
291
292 logMessage VARCHAR2(2000);
293 -- l_error_text varchar2(2000);
294 BEGIN
295 -- Standard Start of API savepoint
296 SAVEPOINT run_Procedure_PVT;
297
298 -- Standard call to check for call compatibility.
299 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
300 p_api_version_number,
301 l_api_name,
302 G_PKG_NAME)
303 THEN
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306
307
308 -- Initialize message list if p_init_msg_list is set to TRUE.
309 IF FND_API.to_Boolean( p_init_msg_list )
310 THEN
311 FND_MSG_PUB.initialize;
312 END IF;
313
314
315 -- Initialize API return status to SUCCESS
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 -- Actual API begins here
319
320 -- Valid procedure first
321 l_proc_name := LTRIM(RTRIM(p_procedure_name));
322
323
324 IF l_proc_name is NOT NULL THEN
325
326 IEM_ROUTE_RUN_PROC_PVT.validProcedure(
327 p_api_version_number => P_Api_Version_Number,
328 p_init_msg_list => FND_API.G_FALSE,
329 p_commit => P_Commit,
330 p_ProcName => l_proc_name,
331 p_return_type => p_param_type,
332 x_return_status => l_return_status,
333 x_msg_count => l_msg_count,
334 x_msg_data => l_msg_data
335 );
336 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
337 raise l_IEM_INVALID_PROCEDURE;
338 end if;
339
340 END IF;
341
342 -- Populate the key-value glabe variable
343 G_key_value.delete;
344
345 for i in 1..p_key_value.count loop
346 G_key_value(i).key := p_key_value(i).key;
347 G_key_value(i).value := p_key_value(i).value;
348 G_key_value(i).datatype := p_key_value(i).datatype;
349 end loop;
350
351 -- Get the name of the OUT parameter
352 if v_ArgumentName(6) is not null then
353 l_para_out_name := v_ArgumentName(6);
354 end if;
355
356 l_ProcCall := 'Begin '|| l_proc_name || '(IEM_ROUTE_RUN_PROC_PVT.G_key_value, :' ||l_para_out_name||'); END;';
357 -- l_ProcCall := 'Begin '|| l_proc_name || '(IEM_TAG_RUN_PROC_PVT.G_key_value, :l_para_out_name); END;';
358 -- execute immediate l_ProcCall using OUT l_result;
359
360 --l_str :='begin api_adm.GetPassword'||G_ADMIN_LINK||'(:a_user,:b_domain,:c_password);end;';
361 --execute immediate l_str using l_user,p_domain,OUT l_pass;
362
363 --Open the cursor and parse the statement.
364 l_Cursor := DBMS_SQL.OPEN_CURSOR;
365 DBMS_SQL.PARSE(l_Cursor, l_ProcCall, DBMS_SQL.native);
366
367 if p_param_type = 'IEMNNUMBER' then
368 DBMS_SQL.BIND_VARIABLE(l_Cursor,l_para_out_name,l_result_n);
369 elsif p_param_type = 'IEMSVARCHAR2' then
370 DBMS_SQL.BIND_VARIABLE(l_Cursor,l_para_out_name,l_result_s,500);
371 else
372 raise l_IEM_INVALID_PARAM_TYPE;
373 end if;
374
375 --Execute the procedure.
376 l_NumRows := DBMS_SQL.EXECUTE(l_Cursor);
377
378 --DBMS_SQL.VARIABLE_VALUE(l_Cursor, ':'||l_para_out_name, l_result);
379 if p_param_type = 'IEMNNUMBER' then
380 DBMS_SQL.VARIABLE_VALUE(l_Cursor, l_para_out_name, l_result_n);
381 elsif p_param_type = 'IEMSVARCHAR2' then
382 DBMS_SQL.VARIABLE_VALUE(l_Cursor, l_para_out_name, l_result_s);
383 else
384 raise l_IEM_INVALID_PARAM_TYPE;
385 end if;
386
387 if p_param_type = 'IEMNNUMBER' then
388 x_result := TO_CHAR(l_result_n);
389 elsif p_param_type = 'IEMSVARCHAR2' then
390 x_result := l_result_s;
391 else
392 raise l_IEM_INVALID_PARAM_TYPE;
393 end if;
394
395 DBMS_SQL.close_cursor(l_Cursor);
396
397 -- Standard Check Of p_commit.
398 IF FND_API.To_Boolean(p_commit) THEN
399 COMMIT WORK;
400 END IF;
401
402 -- Standard callto get message count and if count is 1, get message info.
403 FND_MSG_PUB.Count_And_Get
404 ( p_count => x_msg_count,
405 p_data => x_msg_data
406 );
407
408 EXCEPTION
409 WHEN l_IEM_INVALID_PROCEDURE THEN
410 ROLLBACK TO run_Procedure_PVT;
411 FND_MSG_PUB.Count_And_Get
412 ( p_count => x_msg_count,
413 p_data => x_msg_data
414 );
415 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
416 logMessage := '[Invalid procedure: ' || l_proc_name|| ']';
417 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUT_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
418 end if;
419 x_return_status := FND_API.G_RET_STS_ERROR ;
420
421 WHEN FND_API.G_EXC_ERROR THEN
422 ROLLBACK TO run_Procedure_PVT;
423 x_return_status := FND_API.G_RET_STS_ERROR ;
424 FND_MSG_PUB.Count_And_Get
425 ( p_count => x_msg_count,
426 p_data => x_msg_data
427 );
428 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
429 logMessage := '[FND_API.G_EXC_ERROR happened in RUN_PROCEDURE - ' || l_proc_name|| ' error:'||sqlerrm||']';
430 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUT_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
431 end if;
432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
433 ROLLBACK TO run_Procedure_PVT;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
435 FND_MSG_PUB.Count_And_Get
436 ( p_count => x_msg_count,
437 p_data => x_msg_data
438 );
439 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
440 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR happened in RUN_PROCEDURE - ' || l_proc_name|| ' error:'||sqlerrm||']';
441 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUT_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
442 end if;
443 WHEN OTHERS THEN
444 ROLLBACK TO run_Procedure_PVT;
445 x_return_status := FND_API.G_RET_STS_ERROR;
446
447 IF FND_MSG_PUB.Check_Msg_Level
448 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
449 THEN
450 FND_MSG_PUB.Add_Exc_Msg
451 ( G_PKG_NAME ,
452 l_api_name
453 );
454 END IF;
455
456 FND_MSG_PUB.Count_And_Get
457 ( p_count => x_msg_count,
458 p_data => x_msg_data
459 );
460
461
462 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
463 logMessage := '[OTHER exception happened in RUN_PROCEDURE - ' || l_proc_name|| ' error:'||sqlerrm||']';
464 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUT_RUNPROC_PVT.RUN_PROCEDURE', logMessage);
465 end if;
466 -- dbms_output.put_line('Exception in Run Procedure happened ' || SUBSTR (SQLERRM , 1 , 100));
467 END run_Procedure;
468
469 procedure dummy_procedure_number( key_value IN IEM_ROUTE_PUB.keyVals_tbl_type,
470 result OUT NOCOPY NUMBER)
471 is
472 l_result number;
473 begin
474
475 for i in 1..key_value.count loop
476 if key_value(i).key = 'IEMNCUSTOMERID' then
477 if key_value(i).datatype = 'N' then
478 l_result := to_number( key_value(i).value );
479 end if;
480 exit;
481 end if;
482 end loop;
483 result := l_result;
484 exception
485 when others then
486 null;
487 end;
488
489 procedure dummy_procedure_varchar( key_value IN IEM_ROUTE_PUB.keyVals_tbl_type,
490 result OUT NOCOPY VARCHAR2)
491 is
492 begin
493
494 for i in 1..key_value.count loop
495 if key_value(i).key = 'IEMSSUBJECT' then
496 result := key_value(i).value;
497 exit;
498 end if;
499 end loop;
500
501 exception
502 when others then
503 null;
504 end;
505
506
507 procedure dummy_procedure_continue( key_value IN IEM_TAGPROCESS_PUB.keyVals_tbl_type,
508 result OUT NOCOPY VARCHAR2)
509 is
510 begin
511 result := 'Y';
512
513 exception
514 when others then
515 null;
516 end;
517
518 procedure dummy_procedure_stop( key_value IN IEM_TAGPROCESS_PUB.keyVals_tbl_type,
519 result OUT NOCOPY VARCHAR2)
520 is
521 begin
522 /*
523 for i in 1..key_value.count loop
524 dbms_output.put_line('Key is : ' ||key_value(i).key);
525 dbms_output.put_line('Value is : ' ||key_value(i).value);
526 dbms_output.put_line('Type is : ' ||key_value(i).datatype);
527 end loop;
528 */
529 result := 'N';
530
531 exception
532 when others then
533 null;
534 end;
535
536 procedure dummy_procedure( key_value IN IEM_TAGPROCESS_PUB.keyVals_tbl_type,
537 result OUT NOCOPY VARCHAR2)
538 is
539 begin
540 result := 'Y';
541 for i in 1..key_value.count loop
542 if key_value(i).key = 'IEMSPROCEDUREFLAG' then
543
544 if key_value(i).value = 'Y' then
545 result := 'Y';
546 elsif key_value(i).value = 'N' then
547 result := 'N';
548 end if;
549
550 exit;
551 end if;
552 end loop;
553 exception
554 when others then
555 null;
556 end;
557
558
559 END IEM_ROUTE_RUN_PROC_PVT;