[Home] [Help]
PACKAGE BODY: APPS.AZ_PROFILEVALUE_CONVERT_PKG
Source
1 PACKAGE BODY AZ_PROFILEVALUE_CONVERT_PKG AS
2 /* $Header: azprofcvtb.pls 120.5.12020000.2 2012/12/05 18:24:22 tshort ship $ */
3
4 --
5 -- SEARCH_SUBSTR
6 --
7 FUNCTION SEARCH_SUBSTR ( P_STR IN VARCHAR2, P_SUBSTR IN VARCHAR2 ) RETURN NUMBER IS
8 -- local variable, cursor here
9 search_position NUMBER:=0;
10 idx NUMBER;
11 l_len NUMBER := 0;
12 BEGIN
13 l_len := LENGTH(P_SUBSTR);
14 FOR idx IN 1..( LENGTH(P_STR)+1-l_len ) LOOP
15 IF ( SUBSTR(P_STR,idx,l_len) = P_SUBSTR AND search_position = 0 ) THEN
16 search_position := idx;
17 END IF;
18 END LOOP;
19
20 RETURN search_position;
21 EXCEPTION
22 when OTHERS then return search_position;
23 END SEARCH_SUBSTR;
24
25 --
26 -- SEARCH_SUBSTR_REVERSE
27 --
28 FUNCTION SEARCH_SUBSTR_REVERSE ( P_STR IN VARCHAR2, P_SUBSTR IN VARCHAR2 ) RETURN NUMBER IS
29 -- local variable, cursor here
30 search_position NUMBER:=0;
31 idx NUMBER;
32 idx2 NUMBER;
33 l_len NUMBER := 0;
34 BEGIN
35 l_len := LENGTH(P_SUBSTR);
36 FOR idx IN 1..( LENGTH(P_STR)+1-l_len ) LOOP
37 idx2 := LENGTH(P_STR)+2-l_len - idx;
38 IF ( SUBSTR(P_STR,idx2,l_len) = P_SUBSTR AND search_position = 0 ) THEN
39 search_position := idx2;
40 END IF;
41 END LOOP;
42
43 RETURN search_position;
44 EXCEPTION
45 when OTHERS then return search_position;
46 END SEARCH_SUBSTR_REVERSE;
47
48
49
50 --
51 -- PARSE_COMMA
52 --
53 procedure PARSE_COMMA ( P_STR IN VARCHAR2,
54 P_STR1 OUT NOCOPY VARCHAR2,
55 P_STR2 OUT NOCOPY VARCHAR2,
56 P_STR3 OUT NOCOPY VARCHAR2,
57 P_STR4 OUT NOCOPY VARCHAR2 )
58 IS
59 -- loval variable, cursor here
60 l_mode NUMBER := 0; -- 0: normal, 1: in a quote \"
61 idx NUMBER;
62 lastidx NUMBER := 1;
63 cntr NUMBER := 0;
64 BEGIN
65 P_STR1 := ''; P_STR2 := ''; P_STR3 := ''; P_STR4 := '';
66
67 FOR idx IN 1..( LENGTH(P_STR)-1 ) LOOP
68 IF ( SUBSTR(P_STR,idx,2) = '\"' ) THEN
69 IF ( l_mode = 0 ) THEN
70 l_mode := 1;
71 ELSE
72 l_mode := 0;
73 END IF;
74 ELSIF ( SUBSTR(P_STR,idx,1) = ',' ) THEN
75 IF ( l_mode = 0 ) THEN
76 cntr := cntr + 1;
77 IF ( cntr = 1 ) THEN P_STR1 := SUBSTR(P_STR, lastidx, idx-lastidx);
78 ELSIF ( cntr = 2 ) THEN P_STR2 := SUBSTR(P_STR, lastidx, idx-lastidx);
79 ELSIF ( cntr = 3 ) THEN P_STR3 := SUBSTR(P_STR, lastidx, idx-lastidx);
80 ELSIF ( cntr = 4 ) THEN P_STR4 := SUBSTR(P_STR, lastidx, idx-lastidx);
81 END IF;
82 lastidx := idx+1;
83 END IF;
84 END IF;
85 END LOOP;
86
87 IF ( cntr = 1 ) THEN P_STR2 := SUBSTR(P_STR, lastidx);
88 ELSIF ( cntr = 2 ) THEN P_STR3 := SUBSTR(P_STR, lastidx);
89 ELSIF ( cntr = 3 ) THEN P_STR4 := SUBSTR(P_STR, lastidx);
90 END IF;
91
92 P_STR1 := LTRIM(RTRIM(P_STR1));
93 P_STR2 := LTRIM(RTRIM(P_STR2));
94 P_STR3 := LTRIM(RTRIM(P_STR3));
95 P_STR4 := LTRIM(RTRIM(P_STR4));
96 RETURN;
97 EXCEPTION
98 when OTHERS then return;
99 END PARSE_COMMA;
100
101
102 --
103 -- GET_NAME_FROM_SHORTCUT
104 --
105 FUNCTION GET_NAME_FROM_SHORTCUT (
106 STR_FROM IN VARCHAR2,
107 TBL_SHORTCUT IN VARCHAR2 ) RETURN VARCHAR2 IS
108
109 -- loval variable, cursor here
110 TBL1 VARCHAR2(64);
111 TBL2 VARCHAR2(64);
112 TBL3 VARCHAR2(64);
113 TBL4 VARCHAR2(64);
114 idx NUMBER := 0;
115 BEGIN
116 PARSE_COMMA( STR_FROM, TBL1, TBL2, TBL3, TBL4 );
117 idx := SEARCH_SUBSTR( TBL1, ' ' );
118 IF ( idx > 0 ) THEN
119 IF SUBSTR(TBL1,idx+1)=TBL_SHORTCUT THEN RETURN SUBSTR(TBL1,1,idx-1); END IF;
120 END IF;
121 idx := SEARCH_SUBSTR( TBL2, ' ' );
122 IF ( idx > 0 ) THEN
123 IF SUBSTR(TBL2,idx+1)=TBL_SHORTCUT THEN RETURN SUBSTR(TBL2,1,idx-1); END IF;
124 END IF;
125 idx := SEARCH_SUBSTR( TBL3, ' ' );
126 IF ( idx > 0 ) THEN
127 IF SUBSTR(TBL3,idx+1)=TBL_SHORTCUT THEN RETURN SUBSTR(TBL3,1,idx-1); END IF;
128 END IF;
129 idx := SEARCH_SUBSTR( TBL4, ' ' );
130 IF ( idx > 0 ) THEN
131 IF SUBSTR(TBL4,idx+1)=TBL_SHORTCUT THEN RETURN SUBSTR(TBL4,1,idx-1); END IF;
132 END IF;
133 return STR_FROM;
134 EXCEPTION
135 when OTHERS then return STR_FROM;
136 END GET_NAME_FROM_SHORTCUT;
137
138 --
139 -- GET_TABLE_OWNER ( bug 3431739, 3548926 )
140 --
141 FUNCTION GET_TABLE_OWNER ( P_TBL_NAME IN VARCHAR2 ) RETURN VARCHAR2 IS
142 -- local variable, cursor here
143 L_APP_CODE VARCHAR2(64);
144 BEGIN
145 -- bug 3431739
146 L_APP_CODE := '';
147
148 BEGIN
149 select ou.ORACLE_USERNAME into L_APP_CODE
150 from FND_ORACLE_USERID ou, FND_TABLES t
151 where ou.ORACLE_ID = t.APPLICATION_ID AND t.TABLE_NAME = P_TBL_NAME;
152 EXCEPTION
153 when OTHERS then L_APP_CODE := '';
154 END;
155
156 -- bug 3548926: When P_TBL_NAME is a table (as above), we do the check for owner.
157 -- When P_TBL_NAME is a view (as below), we skip, since we will check for owner = 'APPS'.
158 /*
159 IF ( L_APP_CODE IS NOT NULL AND LENGTH(L_APP_CODE)>0 ) THEN
160 return L_APP_CODE;
161 END IF;
162
163 BEGIN
164 select ou.ORACLE_USERNAME into L_APP_CODE
165 from FND_ORACLE_USERID ou, FND_VIEWS v
166 where ou.ORACLE_ID = v.APPLICATION_ID AND v.VIEW_NAME = P_TBL_NAME;
167 EXCEPTION
168 when OTHERS then L_APP_CODE := '';
169 END;
170 */
171
172 return L_APP_CODE;
173
174 EXCEPTION
175 when OTHERS then return L_APP_CODE;
176 END GET_TABLE_OWNER;
177
178
179 --
180 -- GET_TABLEVIEW_OWNER ( bug 3431739, 3548926 )
181 --
182 FUNCTION GET_TABLEVIEW_OWNER ( P_APP_CODE IN VARCHAR2, P_TBL_TYPE IN VARCHAR2 ) RETURN VARCHAR2 IS
183 -- local variable, cursor here
184 L_OWNER VARCHAR2(64);
185 l_return boolean;
186 L_STATUS VARCHAR2(2000);
187 L_INDUSTRY VARCHAR2(2000);
188 BEGIN
189 -- bug 3548926
190 L_OWNER := 'APPS';
191
192 IF ( P_TBL_TYPE = 'VIEW' ) THEN
193 BEGIN
194 select oracle_username into L_OWNER
195 from fnd_oracle_userid where read_only_flag = 'U';
196 EXCEPTION
197 when OTHERS then L_OWNER := 'APPS';
198 END;
199 ELSE -- 'TABLE'
200 l_return := FND_INSTALLATION.GET_APP_INFO( P_APP_CODE, L_STATUS, L_INDUSTRY, L_OWNER );
201 IF ( L_OWNER IS NULL OR LENGTH(L_OWNER)=0 ) THEN
202 L_OWNER := P_APP_CODE;
203 END IF;
204 END IF;
205
206 return L_OWNER;
207
208 EXCEPTION
209 when OTHERS then return L_OWNER;
210 END GET_TABLEVIEW_OWNER;
211
212 --
213 -- GET_TAB_COL_TYPE
214 --
215 FUNCTION GET_TAB_COL_TYPE ( P_TBL_NAME IN VARCHAR2, P_COL_NAME IN VARCHAR2 ) RETURN VARCHAR2 IS
216 -- local variable, cursor here
217 L_COL_TYPE VARCHAR2(64);
218 L_APP_CODE VARCHAR2(64);
219 L_OWNER VARCHAR2(64);
220 BEGIN
221 -- bug 3431739
222 L_APP_CODE := GET_TABLE_OWNER ( P_TBL_NAME );
223
224 -- bug 3548926: When P_TBL_NAME is a table , we will get a valid L_APP_CODE.
225 -- When P_TBL_NAME is a view, we will check for owner = 'APPS' (not hard coded APPS).
226 L_COL_TYPE := '';
227
228 IF ( L_APP_CODE IS NULL OR LENGTH(L_APP_CODE)=0 ) THEN
229 L_OWNER := GET_TABLEVIEW_OWNER( L_APP_CODE, 'VIEW' ); -- for view, will get 'APPS'
230 SELECT DATA_TYPE into L_COL_TYPE FROM
231 (SELECT DATA_TYPE
232 from user_synonyms syn, dba_tab_columns col
233 where col.COLUMN_NAME=P_COL_NAME
234 AND syn.synonym_name=P_TBL_NAME
235 AND col.owner = syn.table_owner
236 AND col.table_name = syn.table_name
237 AND syn.table_OWNER=L_OWNER
238 union
239 select data_type
240 from user_tab_columns col
241 where col.table_name = P_TBL_NAME
242 AND col.column_name = P_COL_NAME);
243 IF ( L_COL_TYPE IS NULL OR LENGTH(L_COL_TYPE)=0 ) THEN
244 L_COL_TYPE := '';
245 END IF;
246 ELSE
247 L_OWNER := GET_TABLEVIEW_OWNER( L_APP_CODE, 'TABLE' ); -- for table, get HR, APPLSYS, ...
248 SELECT DATA_TYPE into L_COL_TYPE FROM
249 (SELECT DATA_TYPE
250 from user_synonyms syn, dba_tab_columns col
251 where col.COLUMN_NAME=P_COL_NAME
252 AND syn.synonym_name=P_TBL_NAME
253 AND col.owner = syn.table_owner
254 AND col.table_name = syn.table_name
255 AND syn.table_OWNER=L_OWNER
256 union
257 select data_type
258 from user_tab_columns col
259 where col.table_name = P_TBL_NAME
260 AND col.column_name = P_COL_NAME);
261 IF ( L_COL_TYPE IS NULL OR LENGTH(L_COL_TYPE)=0 ) THEN
262 L_COL_TYPE := '';
263 END IF;
264 END IF;
265
266 return L_COL_TYPE;
267 EXCEPTION
268 when OTHERS then return L_COL_TYPE;
269 END GET_TAB_COL_TYPE;
270
271
272 --
273 -- CONVERT
274 --
275 FUNCTION CONVERT (
276 P_PROFILE_NAME IN VARCHAR2,
277 P_PROFILE_VALUE IN VARCHAR2,
278 P_CONVERT_METHOD IN VARCHAR2
279 ) RETURN VARCHAR2 IS
280 -- loval variable, cursor here
281 SQL_STR VARCHAR2(2000);
282 TMP_STR VARCHAR2(2000); -- This is truly a "tmp" string
283 endquote_position NUMBER:=0;
284 idx NUMBER;
285
286 STR_SELECT VARCHAR2(2000);
287 STR_INTO VARCHAR2(2000);
288 STR_FROM VARCHAR2(2000);
289 STR_WHERE VARCHAR2(2000);
290 SELECT1 VARCHAR2(256);
291 SELECT2 VARCHAR2(256);
292 SELECT3 VARCHAR2(256);
293 SELECT4 VARCHAR2(256);
294 INTO1 VARCHAR2(256);
295 INTO2 VARCHAR2(256);
296 INTO3 VARCHAR2(256);
297 INTO4 VARCHAR2(256);
298 SEL_CODE VARCHAR2(256);
299 SEL_DISPLAY VARCHAR2(256);
300 TBL_SHORTCUT VARCHAR2(64);
301 TBL_NAME VARCHAR2(64);
302 SEL_COL VARCHAR2(128);
303 SEL_COL_TYPE VARCHAR2(128);
304
305 --v_CursorID INTEGER;
306 v_SelectStmt VARCHAR2(2000);
307 v_SelDisplay VARCHAR2(240);
308 --v_Dummy INTEGER;
309
310 type SQL_CSR_TYP is ref CURSOR;
311 sql_csr SQL_CSR_TYP;
312 BEGIN
313
314 select SQL_VALIDATION into SQL_STR
315 from fnd_profile_options
316 where ( PROFILE_OPTION_NAME = P_PROFILE_NAME OR PROFILE_OPTION_NAME = UPPER(P_PROFILE_NAME) );
317
318 IF ( SQL_STR IS NULL OR LENGTH(SQL_STR)=0 ) THEN -- we have an empty SQL_VALIDATION string
319 return P_PROFILE_VALUE;
320 END IF;
321
322 SQL_STR := LTRIM( UPPER(SQL_STR) ); -- SQL_STR should be like 'SQL="..."...
323 SQL_STR := LTRIM( SUBSTR(SQL_STR,4) ); -- getting rid of 'SQL'
324 SQL_STR := LTRIM( SUBSTR(SQL_STR,2) ); -- getting rid of '='.
328 TMP_STR := REPLACE( TMP_STR, '\"', 'TT' ); -- replace \" to anything(doesn't have to be 'TT'), but be of length 2
325 SQL_STR := REPLACE( SQL_STR, FND_GLOBAL.LOCAL_CHR(10), ' ' ); -- replacing \n with space
326
327 TMP_STR := LTRIM( SQL_STR ); -- saving a copy in tmp str
329 FOR idx IN 2..LENGTH(TMP_STR) LOOP
330 IF ( SUBSTR(TMP_STR,idx,1) = '"' AND endquote_position = 0 ) THEN
331 endquote_position := idx;
332 END IF;
333 END LOOP;
334 IF ( endquote_position=0 ) THEN -- error, we didn't find the end quote position
335 return P_PROFILE_VALUE;
336 END IF;
337 SQL_STR := SUBSTR( SQL_STR, 2, endquote_position-2 );
338
339 -- as of Jan 27 2003, out of the 3613 profile options, 2262 have a non-empty SQL_VALIDATION string
340 -- one has a "GROUP BY" clause, zero has a "HAVING" clause. for the one that has "GROUP BY", I've checked,
341 -- no processing is needed.
342 idx := SEARCH_SUBSTR( SQL_STR, 'GROUP BY' ); -- SQL_STR is already upper-ed
343 IF idx > 0 THEN
344 return P_PROFILE_VALUE;
345 END IF;
346 idx := SEARCH_SUBSTR( SQL_STR, 'HAVING' ); -- SQL_STR is already upper-ed
347 IF idx > 0 THEN
348 return P_PROFILE_VALUE;
349 END IF;
350 idx := SEARCH_SUBSTR( SQL_STR, 'ORDER BY' ); -- we don't care about "order by"
351 IF idx > 0 THEN
352 SQL_STR := RTRIM( SUBSTR(SQL_STR,1,idx-1) );
353 END IF;
354
355 -- Now SQL_STR should be of exact format SELECT ... INTO ... FROM ... WHERE ... statement,
356 -- Let's parse the hell out of it
357 idx := SEARCH_SUBSTR( SQL_STR, ' INTO ' );
358 IF idx = 0 THEN
359 return P_PROFILE_VALUE;
360 END IF;
361 STR_SELECT := LTRIM(RTRIM( SUBSTR(SQL_STR, 8, idx-8) )); -- 8 is the length of "SELECT "+1
362 SQL_STR := SUBSTR(SQL_STR, idx);
363
364 idx := SEARCH_SUBSTR( SQL_STR, ' FROM ' );
365 IF idx = 0 THEN
366 return P_PROFILE_VALUE;
367 END IF;
368 STR_INTO := LTRIM(RTRIM( SUBSTR(SQL_STR, 7, idx-7) )); -- 7 is the length of " INTO "+1
369 SQL_STR := SUBSTR(SQL_STR, idx);
370
371 idx := SEARCH_SUBSTR( SQL_STR, ' WHERE ' );
372 IF idx = 0 THEN
373 STR_FROM := LTRIM(RTRIM( SUBSTR(SQL_STR, 7) )); -- 7 is the length of " FROM "+1
374 STR_WHERE := '';
375 ELSE
376 STR_FROM := LTRIM(RTRIM( SUBSTR(SQL_STR, 7, idx-7) )); -- 7 is the length of " FROM "+1
377 STR_WHERE := LTRIM(RTRIM( SUBSTR(SQL_STR, idx+7) ));
378 END IF;
379
380 -- Now we have STR_SELECT, STR_INTO, STR_FROM and STR_WHERE
381 IF ( SEARCH_SUBSTR(STR_SELECT, 'DECODE') > 0 OR SEARCH_SUBSTR(STR_SELECT, 'DISTINCT') > 0 ) THEN
382 return P_PROFILE_VALUE;
383 END IF;
384
385 PARSE_COMMA( STR_SELECT, SELECT1, SELECT2, SELECT3, SELECT4 );
386 PARSE_COMMA( STR_INTO, INTO1, INTO2, INTO3, INTO4 );
387 IF ( INTO1 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT1;
388 ELSIF ( INTO2 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT2;
389 ELSIF ( INTO3 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT3;
390 ELSIF ( INTO4 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT4;
391 END IF;
392 IF ( INTO1 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT1;
393 ELSIF ( INTO2 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT2;
394 ELSIF ( INTO3 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT3;
395 ELSIF ( INTO4 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT4;
396 END IF;
397
398 IF ( SEL_CODE IS NULL OR LENGTH(SEL_CODE)=0 OR SEL_DISPLAY IS NULL OR LENGTH(SEL_DISPLAY)=0 ) THEN
399 return P_PROFILE_VALUE;
400 END IF;
401 idx := SEARCH_SUBSTR( SEL_CODE, ' ' );
402 IF ( idx > 0 ) THEN SEL_CODE := SUBSTR( SEL_CODE, 1, idx-1 ); END IF;
403 idx := SEARCH_SUBSTR( SEL_DISPLAY, ' ' );
404 IF ( idx > 0 ) THEN SEL_DISPLAY := SUBSTR( SEL_DISPLAY, 1, idx-1 ); END IF;
405
406 idx := SEARCH_SUBSTR( SEL_CODE, '.' );
407 IF ( idx > 0 ) THEN
408 TBL_SHORTCUT := SUBSTR( SEL_CODE, 1, idx-1 );
409 TBL_NAME := GET_NAME_FROM_SHORTCUT( STR_FROM, TBL_SHORTCUT );
410 SEL_COL := SUBSTR( SEL_CODE, idx+1 );
411 ELSE
412 TBL_NAME := STR_FROM;
413 SEL_COL := SEL_CODE;
414 END IF;
415
416 -- we have SEL_COL, and TBL_NAME. check if it is a number
417 SEL_COL_TYPE := GET_TAB_COL_TYPE ( TBL_NAME, SEL_COL );
418
419 IF ( SEL_COL_TYPE IS NULL OR SEL_COL_TYPE <> 'NUMBER' ) THEN
420 return P_PROFILE_VALUE;
421 END IF;
422
423 -- rsekaran commented out
424 -- finally, we are sure that SEL_CODE is a NUMBER type
425 -- v_CursorID := DBMS_SQL.OPEN_CURSOR;
426
427
428 IF ( P_CONVERT_METHOD = 'DOWNLOAD' ) THEN
429
430 v_SelectStmt := 'SELECT ' || SEL_DISPLAY || ' FROM ' || STR_FROM || ' WHERE ' || SEL_CODE || ' = ' || P_PROFILE_VALUE;
431 IF ( STR_WHERE IS NOT NULL AND LENGTH( LTRIM(RTRIM(STR_WHERE)) ) > 0 ) THEN
432 v_SelectStmt := v_SelectStmt || ' AND ' || STR_WHERE;
433 END IF;
434
435 -- rsekaran
436 -- Use REF CURSOR instead of use of DBMS_SQL package
437 /*
438 DBMS_SQL.PARSE( v_CursorID, v_SelectStmt, DBMS_SQL.NATIVE );
439 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_SelDisplay, 240 );
440 v_Dummy := DBMS_SQL.EXECUTE( v_CursorID );
441 */
442 OPEN sql_csr FOR v_SelectStmt;
443 idx := 0;
444 LOOP
445 FETCH sql_csr INTO v_SelDisplay;
446 EXIT WHEN sql_csr%NOTFOUND;
447 --IF DBMS_SQL.FETCH_ROWS( v_CursorID ) = 0 THEN
448 -- EXIT;
449 --END IF;
450
451 idx := idx + 1;
452 --DBMS_SQL.COLUMN_VALUE( v_CursorID, 1, v_SelDisplay );
453 END LOOP;
454 --DBMS_SQL.CLOSE_CURSOR( v_CursorID );
455
456 CLOSE sql_csr;
457
458 if ( idx = 1 ) THEN
459 return v_SelDisplay;
460 END IF;
461
465 || FND_GLOBAL.LOCAL_CHR(39) || LTRIM(RTRIM(P_PROFILE_VALUE)) || FND_GLOBAL.LOCAL_CHR(39);
462 ELSIF ( P_CONVERT_METHOD = 'UPLOAD' ) THEN
463
464 v_SelectStmt := 'SELECT ' || SEL_CODE || ' FROM ' || STR_FROM || ' WHERE LTRIM(RTRIM(' || SEL_DISPLAY || ')) = '
466 IF ( STR_WHERE IS NOT NULL AND LENGTH( LTRIM(RTRIM(STR_WHERE)) ) > 0 ) THEN
467 v_SelectStmt := v_SelectStmt || ' AND ' || STR_WHERE;
468 END IF;
469
470 /*
471 DBMS_SQL.PARSE( v_CursorID, v_SelectStmt, DBMS_SQL.NATIVE );
472 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_SelDisplay, 240 );
473 v_Dummy := DBMS_SQL.EXECUTE( v_CursorID );
474 */
475 OPEN sql_csr FOR v_SelectStmt;
476 idx := 0;
477 LOOP
478 FETCH sql_csr INTO v_SelDisplay;
479 EXIT WHEN sql_csr%NOTFOUND;
480
481 --IF DBMS_SQL.FETCH_ROWS( v_CursorID ) = 0 THEN
482 -- EXIT;
483 --END IF;
484
485 idx := idx + 1;
486 --DBMS_SQL.COLUMN_VALUE( v_CursorID, 1, v_SelDisplay );
487 END LOOP;
488 --DBMS_SQL.CLOSE_CURSOR( v_CursorID );
489 CLOSE sql_csr;
490
491 if ( idx = 1 ) THEN
492 return v_SelDisplay;
493 else
494 return NULL;
495 END IF;
496 END IF;
497
498 -- return 'Code:' || SEL_CODE || ', tblname:' || TBL_NAME || ', colname:' || SEL_COL || ', coltype:' || SEL_COL_TYPE;
499 return P_PROFILE_VALUE;
500
501 EXCEPTION
502 when OTHERS then
503 -- rsekaran
504 -- Close the cursor if open
505 IF (sql_csr%ISOPEN) THEN
506 CLOSE sql_csr;
507 END IF;
508
509 return P_PROFILE_VALUE;
510
511 end CONVERT;
512
513
514 END AZ_PROFILEVALUE_CONVERT_PKG;