[Home] [Help]
Skip to content
PACKAGE BODY: APPS.AZ_PROFILEVALUE_CONVERT_PKG
Source
1 PACKAGE BODY AZ_PROFILEVALUE_CONVERT_PKG AS
2 /* $Header: azprofcvtb.pls 120.3 2006/02/14 03:37:00 sbandi noship $ */
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
231 from dba_tab_columns
232 where COLUMN_NAME=P_COL_NAME AND TABLE_NAME=P_TBL_NAME AND OWNER=L_OWNER;
233
234 IF ( L_COL_TYPE IS NULL OR LENGTH(L_COL_TYPE)=0 ) THEN
235 L_COL_TYPE := '';
236 END IF;
237 ELSE
238 L_OWNER := GET_TABLEVIEW_OWNER( L_APP_CODE, 'TABLE' ); -- for table, get HR, APPLSYS, ...
239 SELECT DATA_TYPE into L_COL_TYPE
240 from dba_tab_columns
241 where COLUMN_NAME=P_COL_NAME AND TABLE_NAME=P_TBL_NAME AND OWNER=L_OWNER;
242
243 IF ( L_COL_TYPE IS NULL OR LENGTH(L_COL_TYPE)=0 ) THEN
244 L_COL_TYPE := '';
245 END IF;
246 END IF;
247
248 return L_COL_TYPE;
249 EXCEPTION
250 when OTHERS then return L_COL_TYPE;
251 END GET_TAB_COL_TYPE;
252
253
254 --
255 -- CONVERT
256 --
257 FUNCTION CONVERT (
258 P_PROFILE_NAME IN VARCHAR2,
259 P_PROFILE_VALUE IN VARCHAR2,
260 P_CONVERT_METHOD IN VARCHAR2
261 ) RETURN VARCHAR2 IS
262 -- loval variable, cursor here
263 SQL_STR VARCHAR2(2000);
264 TMP_STR VARCHAR2(2000); -- This is truly a "tmp" string
265 endquote_position NUMBER:=0;
266 idx NUMBER;
267
268 STR_SELECT VARCHAR2(2000);
269 STR_INTO VARCHAR2(2000);
273 SELECT2 VARCHAR2(256);
270 STR_FROM VARCHAR2(2000);
271 STR_WHERE VARCHAR2(2000);
272 SELECT1 VARCHAR2(256);
274 SELECT3 VARCHAR2(256);
275 SELECT4 VARCHAR2(256);
276 INTO1 VARCHAR2(256);
277 INTO2 VARCHAR2(256);
278 INTO3 VARCHAR2(256);
279 INTO4 VARCHAR2(256);
280 SEL_CODE VARCHAR2(256);
281 SEL_DISPLAY VARCHAR2(256);
282 TBL_SHORTCUT VARCHAR2(64);
283 TBL_NAME VARCHAR2(64);
284 SEL_COL VARCHAR2(128);
285 SEL_COL_TYPE VARCHAR2(128);
286
287 --v_CursorID INTEGER;
288 v_SelectStmt VARCHAR2(2000);
289 v_SelDisplay VARCHAR2(240);
290 --v_Dummy INTEGER;
291
292 type SQL_CSR_TYP is ref CURSOR;
293 sql_csr SQL_CSR_TYP;
294 BEGIN
295
296 select SQL_VALIDATION into SQL_STR
297 from fnd_profile_options
298 where ( PROFILE_OPTION_NAME = P_PROFILE_NAME OR PROFILE_OPTION_NAME = UPPER(P_PROFILE_NAME) );
299
300 IF ( SQL_STR IS NULL OR LENGTH(SQL_STR)=0 ) THEN -- we have an empty SQL_VALIDATION string
301 return P_PROFILE_VALUE;
302 END IF;
303
304 SQL_STR := LTRIM( UPPER(SQL_STR) ); -- SQL_STR should be like 'SQL="..."...
305 SQL_STR := LTRIM( SUBSTR(SQL_STR,4) ); -- getting rid of 'SQL'
306 SQL_STR := LTRIM( SUBSTR(SQL_STR,2) ); -- getting rid of '='.
307 SQL_STR := REPLACE( SQL_STR, FND_GLOBAL.LOCAL_CHR(10), ' ' ); -- replacing \n with space
308
309 TMP_STR := LTRIM( SQL_STR ); -- saving a copy in tmp str
310 TMP_STR := REPLACE( TMP_STR, '\"', 'TT' ); -- replace \" to anything(doesn't have to be 'TT'), but be of length 2
311 FOR idx IN 2..LENGTH(TMP_STR) LOOP
312 IF ( SUBSTR(TMP_STR,idx,1) = '"' AND endquote_position = 0 ) THEN
313 endquote_position := idx;
314 END IF;
315 END LOOP;
316 IF ( endquote_position=0 ) THEN -- error, we didn't find the end quote position
317 return P_PROFILE_VALUE;
318 END IF;
319 SQL_STR := SUBSTR( SQL_STR, 2, endquote_position-2 );
320
321 -- as of Jan 27 2003, out of the 3613 profile options, 2262 have a non-empty SQL_VALIDATION string
322 -- one has a "GROUP BY" clause, zero has a "HAVING" clause. for the one that has "GROUP BY", I've checked,
323 -- no processing is needed.
324 idx := SEARCH_SUBSTR( SQL_STR, 'GROUP BY' ); -- SQL_STR is already upper-ed
325 IF idx > 0 THEN
326 return P_PROFILE_VALUE;
327 END IF;
328 idx := SEARCH_SUBSTR( SQL_STR, 'HAVING' ); -- SQL_STR is already upper-ed
329 IF idx > 0 THEN
330 return P_PROFILE_VALUE;
331 END IF;
332 idx := SEARCH_SUBSTR( SQL_STR, 'ORDER BY' ); -- we don't care about "order by"
333 IF idx > 0 THEN
334 SQL_STR := RTRIM( SUBSTR(SQL_STR,1,idx-1) );
335 END IF;
336
337 -- Now SQL_STR should be of exact format SELECT ... INTO ... FROM ... WHERE ... statement,
338 -- Let's parse the hell out of it
339 idx := SEARCH_SUBSTR( SQL_STR, ' INTO ' );
340 IF idx = 0 THEN
341 return P_PROFILE_VALUE;
342 END IF;
343 STR_SELECT := LTRIM(RTRIM( SUBSTR(SQL_STR, 8, idx-8) )); -- 8 is the length of "SELECT "+1
344 SQL_STR := SUBSTR(SQL_STR, idx);
345
346 idx := SEARCH_SUBSTR( SQL_STR, ' FROM ' );
347 IF idx = 0 THEN
348 return P_PROFILE_VALUE;
349 END IF;
350 STR_INTO := LTRIM(RTRIM( SUBSTR(SQL_STR, 7, idx-7) )); -- 7 is the length of " INTO "+1
351 SQL_STR := SUBSTR(SQL_STR, idx);
352
353 idx := SEARCH_SUBSTR( SQL_STR, ' WHERE ' );
354 IF idx = 0 THEN
355 STR_FROM := LTRIM(RTRIM( SUBSTR(SQL_STR, 7) )); -- 7 is the length of " FROM "+1
356 STR_WHERE := '';
357 ELSE
358 STR_FROM := LTRIM(RTRIM( SUBSTR(SQL_STR, 7, idx-7) )); -- 7 is the length of " FROM "+1
359 STR_WHERE := LTRIM(RTRIM( SUBSTR(SQL_STR, idx+7) ));
360 END IF;
361
362 -- Now we have STR_SELECT, STR_INTO, STR_FROM and STR_WHERE
363 IF ( SEARCH_SUBSTR(STR_SELECT, 'DECODE') > 0 OR SEARCH_SUBSTR(STR_SELECT, 'DISTINCT') > 0 ) THEN
364 return P_PROFILE_VALUE;
365 END IF;
366
367 PARSE_COMMA( STR_SELECT, SELECT1, SELECT2, SELECT3, SELECT4 );
368 PARSE_COMMA( STR_INTO, INTO1, INTO2, INTO3, INTO4 );
369 IF ( INTO1 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT1;
370 ELSIF ( INTO2 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT2;
371 ELSIF ( INTO3 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT3;
372 ELSIF ( INTO4 = ':PROFILE_OPTION_VALUE' ) THEN SEL_CODE := SELECT4;
373 END IF;
374 IF ( INTO1 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT1;
375 ELSIF ( INTO2 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT2;
376 ELSIF ( INTO3 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT3;
377 ELSIF ( INTO4 = ':VISIBLE_OPTION_VALUE' ) THEN SEL_DISPLAY := SELECT4;
378 END IF;
379
380 IF ( SEL_CODE IS NULL OR LENGTH(SEL_CODE)=0 OR SEL_DISPLAY IS NULL OR LENGTH(SEL_DISPLAY)=0 ) THEN
381 return P_PROFILE_VALUE;
382 END IF;
383 idx := SEARCH_SUBSTR( SEL_CODE, ' ' );
384 IF ( idx > 0 ) THEN SEL_CODE := SUBSTR( SEL_CODE, 1, idx-1 ); END IF;
385 idx := SEARCH_SUBSTR( SEL_DISPLAY, ' ' );
386 IF ( idx > 0 ) THEN SEL_DISPLAY := SUBSTR( SEL_DISPLAY, 1, idx-1 ); END IF;
387
388 idx := SEARCH_SUBSTR( SEL_CODE, '.' );
389 IF ( idx > 0 ) THEN
390 TBL_SHORTCUT := SUBSTR( SEL_CODE, 1, idx-1 );
394 TBL_NAME := STR_FROM;
391 TBL_NAME := GET_NAME_FROM_SHORTCUT( STR_FROM, TBL_SHORTCUT );
392 SEL_COL := SUBSTR( SEL_CODE, idx+1 );
393 ELSE
395 SEL_COL := SEL_CODE;
396 END IF;
397
398 -- we have SEL_COL, and TBL_NAME. check if it is a number
399 SEL_COL_TYPE := GET_TAB_COL_TYPE ( TBL_NAME, SEL_COL );
400
401 IF ( SEL_COL_TYPE IS NULL OR SEL_COL_TYPE <> 'NUMBER' ) THEN
402 return P_PROFILE_VALUE;
403 END IF;
404
405 -- rsekaran commented out
406 -- finally, we are sure that SEL_CODE is a NUMBER type
407 -- v_CursorID := DBMS_SQL.OPEN_CURSOR;
408
409
410 IF ( P_CONVERT_METHOD = 'DOWNLOAD' ) THEN
411
412 v_SelectStmt := 'SELECT ' || SEL_DISPLAY || ' FROM ' || STR_FROM || ' WHERE ' || SEL_CODE || ' = ' || P_PROFILE_VALUE;
413 IF ( STR_WHERE IS NOT NULL AND LENGTH( LTRIM(RTRIM(STR_WHERE)) ) > 0 ) THEN
414 v_SelectStmt := v_SelectStmt || ' AND ' || STR_WHERE;
415 END IF;
416
417 -- rsekaran
418 -- Use REF CURSOR instead of use of DBMS_SQL package
419 /*
420 DBMS_SQL.PARSE( v_CursorID, v_SelectStmt, DBMS_SQL.NATIVE );
421 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_SelDisplay, 240 );
422 v_Dummy := DBMS_SQL.EXECUTE( v_CursorID );
423 */
424 OPEN sql_csr FOR v_SelectStmt;
425 idx := 0;
426 LOOP
427 FETCH sql_csr INTO v_SelDisplay;
428 EXIT WHEN sql_csr%NOTFOUND;
429 --IF DBMS_SQL.FETCH_ROWS( v_CursorID ) = 0 THEN
430 -- EXIT;
431 --END IF;
432
433 idx := idx + 1;
434 --DBMS_SQL.COLUMN_VALUE( v_CursorID, 1, v_SelDisplay );
435 END LOOP;
436 --DBMS_SQL.CLOSE_CURSOR( v_CursorID );
437
438 CLOSE sql_csr;
439
440 if ( idx = 1 ) THEN
441 return v_SelDisplay;
442 END IF;
443
444 ELSIF ( P_CONVERT_METHOD = 'UPLOAD' ) THEN
445
446 v_SelectStmt := 'SELECT ' || SEL_CODE || ' FROM ' || STR_FROM || ' WHERE LTRIM(RTRIM(' || SEL_DISPLAY || ')) = '
447 || FND_GLOBAL.LOCAL_CHR(39) || LTRIM(RTRIM(P_PROFILE_VALUE)) || FND_GLOBAL.LOCAL_CHR(39);
448 IF ( STR_WHERE IS NOT NULL AND LENGTH( LTRIM(RTRIM(STR_WHERE)) ) > 0 ) THEN
449 v_SelectStmt := v_SelectStmt || ' AND ' || STR_WHERE;
450 END IF;
451
452 /*
453 DBMS_SQL.PARSE( v_CursorID, v_SelectStmt, DBMS_SQL.NATIVE );
454 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_SelDisplay, 240 );
455 v_Dummy := DBMS_SQL.EXECUTE( v_CursorID );
456 */
457 OPEN sql_csr FOR v_SelectStmt;
458 idx := 0;
459 LOOP
460 FETCH sql_csr INTO v_SelDisplay;
461 EXIT WHEN sql_csr%NOTFOUND;
462
463 --IF DBMS_SQL.FETCH_ROWS( v_CursorID ) = 0 THEN
464 -- EXIT;
465 --END IF;
466
467 idx := idx + 1;
468 --DBMS_SQL.COLUMN_VALUE( v_CursorID, 1, v_SelDisplay );
469 END LOOP;
470 --DBMS_SQL.CLOSE_CURSOR( v_CursorID );
471 CLOSE sql_csr;
472
473 if ( idx = 1 ) THEN
474 return v_SelDisplay;
475 else
476 return NULL;
477 END IF;
478 END IF;
479
480 -- return 'Code:' || SEL_CODE || ', tblname:' || TBL_NAME || ', colname:' || SEL_COL || ', coltype:' || SEL_COL_TYPE;
481 return P_PROFILE_VALUE;
482
483 EXCEPTION
484 when OTHERS then
485 -- rsekaran
486 -- Close the cursor if open
487 IF (sql_csr%ISOPEN) THEN
488 CLOSE sql_csr;
489 END IF;
490
491 return P_PROFILE_VALUE;
492
493 end CONVERT;
494
495
496 END AZ_PROFILEVALUE_CONVERT_PKG;