[Home] [Help]
PACKAGE BODY: APPS.BIS_VG_UTIL
Source
1 PACKAGE BODY BIS_VG_UTIL AS
2 /* $Header: BISTUTLB.pls 115.12 2003/06/02 14:52:42 dbowles ship $ */
3
4 -- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --
7 -- FILENAME
8 --
9 -- BISTUTLB.pls
10 --
11 -- DESCRIPTION
12 --
13 -- body of view generator utils to be used in the view generator
14 -- package specifyling the view security
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 21-JUL-98 Created
21 -- 06-APR-01 Edited by DBOWLES Added new function find_Flex_Prompt.
22 -- function returns the translated form prompt for the flex segment.
23 -- Added overloaded procedure concatenate_Tables to accept
24 -- types of BIS_VG_TYPES.Flex_Column_Comment_Table_Type.
25 -- 11-DEC-01 Edited by DBOWLES Added dr driver comments.
26 --
27 G_PKG_NAME CONSTANT VARCHAR(30) := 'BIS_VG_UTIL';
28 -- ============================================================================
29 -- FUNCTION : is_char_delimiter
30 -- PARAMETERS:
31 -- 1. p_character a valid character
32 -- 2. x_return_status error or normal
33 -- 3. x_error_Tbl table of error messages
34 --
35 -- COMMENT : Call this function to find out if the character is a delimiter
36 -- EXCEPTION : None
37 -- ============================================================================
38 FUNCTION is_char_delimiter
39 ( p_character IN VARCHAR2
40 , x_return_status OUT VARCHAR2
41 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
42 )
43 RETURN BOOLEAN IS
44 --
45 BEGIN
46 BIS_DEBUG_PUB.Add('> is_char_delimiter');
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48
49 IF( p_character = ' '
50 -- OR p_character = ' '
51 OR p_character = ','
52 OR p_character = '-'
53 ) THEN
54 BIS_DEBUG_PUB.Add('return TRUE');
55 BIS_DEBUG_PUB.Add('< is_char_delimiter');
56 RETURN TRUE;
57 ELSE
58 bis_debug_pub.Add('char = <' || p_character || '>');
59 BIS_DEBUG_PUB.Add('return FALSE');
60 BIS_DEBUG_PUB.Add('< is_char_delimiter');
61 RETURN FALSE;
62 END IF;
63
64 EXCEPTION
65 when FND_API.G_EXC_ERROR then
66 x_return_status := FND_API.G_RET_STS_ERROR ;
67 RAISE FND_API.G_EXC_ERROR;
68 when FND_API.G_EXC_UNEXPECTED_ERROR then
69 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 when others then
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
73 BIS_VG_UTIL.Add_Error_Message
74 ( p_error_msg_id => SQLCODE
75 , p_error_description => SQLERRM
76 , p_error_proc_name => G_PKG_NAME||'.is_char_delimiter'
77 , p_error_table => x_error_tbl
78 , x_error_table => x_error_tbl
79 );
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81
82 END is_char_delimiter;
83 --
84 --
85 PROCEDURE create_Text_Table
86 ( p_String IN VARCHAR2
87 , x_View_Table OUT bis_vg_types.View_Text_Table_Type
88 , x_return_status OUT VARCHAR2
89 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
90 )
91 IS
92 --
93 l_beg_pos NUMBER := 1;
94 l_pos NUMBER := 0;
95 l_string_len NUMBER;
96 l_chunk_size NUMBER := 200;
97 l_beg_string VARCHAR2(200);
98 l_end_string VARCHAR2(200) := NULL;
99 l_string VARCHAR2(200);
100 l_old_chunk_size NUMBER;
101 --
102 BEGIN
103 bis_debug_pub.Add('> create_Text_Table');
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 l_string_len := LENGTH(p_String);
107 IF(l_string_len <= l_chunk_size) THEN
108 -- string can be stored in one row of table
109 x_View_Table(1) := p_String;
110 ELSE
111 LOOP
112 -- take string og length l_chunk_size from l_beg_pos
113 l_string := SUBSTR(p_string, l_beg_pos, l_chunk_size);
114 l_old_chunk_size := l_chunk_size;
115 bis_debug_pub.add('length of l_string = ' || LENGTH(l_string));
116 bis_debug_pub.add('l_string = ' || l_string);
117 IF( LENGTH(l_string) < l_chunk_size ) THEN
118 -- string can be stored in one row of table;
119 -- prepend l_end_string from previous iteration
120 x_View_Table(x_View_Table.COUNT + 1) := l_end_string || l_string;
121 ELSE
122 -- update l_beg_pos for next iteration
123 l_beg_pos := l_beg_pos + l_chunk_size;
124 -- update l_pos to end of string retrieved
125 l_pos := l_chunk_size;
126 -- loop till you find a valid delimiter
127 WHILE ( NOT is_char_delimiter(SUBSTR(l_string, l_pos, 1)
128 , x_return_status
129 , x_error_Tbl
130 )
131
132 ) LOOP
133 l_pos := l_pos - 1;
134 END LOOP;
135 bis_debug_pub.add('l_pos = ' || l_pos);
136 bis_debug_pub.add('l_beg_pos = ' || l_beg_pos);
137 bis_debug_pub.add('l_chunk_size = ' || l_chunk_size);
138 -- store the portion of string till the valid delimiter
139 l_beg_string := SUBSTR(l_string, 1, l_pos);
140 bis_debug_pub.add('l_beg_string = ' || l_beg_string);
141 -- prepend l_end_string from previous iteration to l_beg_string
142 -- to create a row of the table
143 x_View_Table(x_View_Table.COUNT + 1) := l_end_string || l_beg_string;
144 IF(l_pos = l_chunk_size) THEN
145 -- got delimiter at the end of l_string; reset values
146 l_end_string := NULL;
147 l_chunk_size := 200;
148 bis_debug_pub.add('l_end_string = NULL; l_chunk_size = 200');
149 ELSE
150 -- store the end portion of string beyond the delimiter
151 l_end_string := SUBSTR(l_string, l_pos + 1);
152 -- set l_chunk_size for next iteration to account for the spillover
153 l_chunk_size := 200 - LENGTH(l_end_string);
154 bis_debug_pub.add('l_end_string = ' || l_end_string);
155 bis_debug_pub.add('l_chunk_size = ' || l_chunk_size);
156 END IF;
157 END IF;
158 -- exit if we did not find enough characters ie., end of string reached
159 EXIT WHEN LENGTH(l_string) < l_old_chunk_size;
160 END LOOP;
161 END IF;
162 bis_debug_pub.Add('< create_Text_Table');
163
164
165 EXCEPTION
166 when FND_API.G_EXC_ERROR then
167 x_return_status := FND_API.G_RET_STS_ERROR ;
168 RAISE FND_API.G_EXC_ERROR;
169 when FND_API.G_EXC_UNEXPECTED_ERROR then
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 when others then
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
174 BIS_VG_UTIL.Add_Error_Message
175 ( p_error_msg_id => SQLCODE
176 , p_error_description => SQLERRM
177 , p_error_proc_name => G_PKG_NAME||'.create_Text_Table'
178 , p_error_table => x_error_tbl
179 , x_error_table => x_error_tbl
180 );
181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
182
183 END create_Text_Table;
184 --
185 FUNCTION get_valid_col_name
186 ( p_Col_Name IN VARCHAR2
187 , x_return_status OUT VARCHAR2
188 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
189 )
190 RETURN VARCHAR2 IS
191 --
192 l_String VARCHAR2(100);
193 l_SQL_text VARCHAR2(100);
194 l_CursorID INTEGER;
195 l_dummy INTEGER;
196 --
197 BEGIN
198 bis_debug_pub.Add('> get_string_len30');
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200
201 l_String := REPLACE(p_Col_Name, ' ', '_');
202 l_String := REPLACE(l_String, '^', '_');
203 l_String := REPLACE(l_String, '-', '_');
204 l_SQL_text := 'SELECT NULL ' || l_String || ' FROM DUAL';
205 BEGIN
206 l_CursorID := DBMS_SQL.OPEN_CURSOR;
207 DBMS_SQL.PARSE(l_CursorID, l_SQL_text, DBMS_SQL.NATIVE);
208 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
209 EXCEPTION
210 WHEN OTHERS THEN
211 l_String := '"' || l_String || '"';
212 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
213 END;
214 RETURN l_String;
215 bis_debug_pub.Add('< get_string_len30');
216
217
218 EXCEPTION
219 when FND_API.G_EXC_ERROR then
220 x_return_status := FND_API.G_RET_STS_ERROR ;
221 RAISE FND_API.G_EXC_ERROR;
222 when FND_API.G_EXC_UNEXPECTED_ERROR then
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 when others then
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
227 BIS_VG_UTIL.Add_Error_Message
228 ( p_error_msg_id => SQLCODE
229 , p_error_description => SQLERRM
230 , p_error_proc_name => G_PKG_NAME||'.get_valid_col_name'
231 , p_error_table => x_error_tbl
232 , x_error_table => x_error_tbl
233 );
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235
236 END get_valid_col_name;
237 --
238 --
239 FUNCTION get_string_len30
240 ( p_String IN VARCHAR2
241 , p_Prefix IN VARCHAR2
242 , p_Suffix IN VARCHAR2
243 , x_return_status OUT VARCHAR2
244 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
245 )
246 RETURN VARCHAR2 IS
247 --
248 l_String VARCHAR2(100);
249 l_Suffix_Len NUMBER := 0;
250 --
251 BEGIN
252 BIS_DEBUG_PUB.Add('> get_string_len30');
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 IF(p_Suffix IS NOT NULL) THEN
256 l_Suffix_Len := LENGTH(p_Suffix);
257 END IF;
258 l_String := SUBSTR(p_Prefix || p_String, 1, 30 - l_Suffix_Len) || p_Suffix;
259 RETURN l_String;
260 BIS_DEBUG_PUB.Add('< get_string_len30');
261
262
263 EXCEPTION
264 when FND_API.G_EXC_ERROR then
265 x_return_status := FND_API.G_RET_STS_ERROR ;
266 RAISE FND_API.G_EXC_ERROR;
267 when FND_API.G_EXC_UNEXPECTED_ERROR then
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 when others then
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
272 BIS_VG_UTIL.Add_Error_Message
273 ( p_error_msg_id => SQLCODE
274 , p_error_description => SQLERRM
275 , p_error_proc_name => G_PKG_NAME||'.get_string_len30'
276 , p_error_table => x_error_tbl
277 , x_error_table => x_error_tbl
278 );
279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
280
281 END get_string_len30;
282 --
283 --
284 FUNCTION get_string
285 ( p_view_table IN bis_vg_types.View_Text_Table_Type
286 , p_start_pointer IN bis_vg_types.View_Character_Pointer_Type
287 , p_end_pointer IN bis_vg_types.View_Character_Pointer_Type
288 , x_return_status OUT VARCHAR2
289 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
290 )
291 RETURN VARCHAR2
292 IS
293 l_ret VARCHAR2(32000);
294 l_str VARCHAR2(32000);
295 end_pointer bis_vg_types.view_character_pointer_type;
296 BEGIN
297
298 BIS_DEBUG_PUB.Add('> get_string');
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300
301 print_view_pointer ( p_start_pointer
302 , x_return_status
303 , x_error_Tbl
304 );
305 print_view_pointer ( p_end_pointer
306 , x_return_status
307 , x_error_Tbl
308 );
309
310 IF (p_start_pointer.row_num IS NULL) THEN
311 -- beginning from end of table
312 RETURN NULL;
313 END IF;
314
315 end_pointer := p_end_pointer;
316 IF (end_pointer.row_num IS NULL) THEN
317 -- has to copy entire table after start
318 -- set row num as all the last row
319 -- set column num as one more then length as end pointer char is excluded
320 end_pointer.row_num := p_view_table.COUNT;
321 end_pointer.col_num := Length(p_view_table(end_pointer.row_num)) + 1;
322 END IF;
323
324 print_view_pointer ( end_pointer
325 , x_return_status
326 , x_error_Tbl
327 );
328 IF (p_start_pointer.row_num = end_pointer.row_num) THEN
329 l_str := p_view_table(p_start_pointer.row_num);
330 RETURN Substr( l_str
331 , p_start_pointer.col_num
332 , end_pointer.col_num - p_start_pointer.col_num
333 );
334 ELSE
335 FOR i IN p_start_pointer.row_num .. end_pointer.row_num LOOP
336 l_str := p_view_table(i);
337 IF (i = p_start_pointer.row_num) THEN
338 l_ret := Substr( l_str
339 , p_start_pointer.col_num
340 , Length(l_str) - p_start_pointer.col_num + 1
341 );
342 ELSIF (i = end_pointer.row_num) THEN
343 l_ret := l_ret || Substr(l_str, 1, end_pointer.col_num - 1);
344 ELSE
345 l_ret := l_ret || l_str;
346 END IF;
347 END LOOP;
348 END IF;
349
350 BIS_DEBUG_PUB.Add('< get_string');
351 RETURN l_ret;
352
353 EXCEPTION
354 when FND_API.G_EXC_ERROR then
355 x_return_status := FND_API.G_RET_STS_ERROR ;
356 RAISE FND_API.G_EXC_ERROR;
357 when FND_API.G_EXC_UNEXPECTED_ERROR then
358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 when others then
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
362 BIS_VG_UTIL.Add_Error_Message
363 ( p_error_msg_id => SQLCODE
364 , p_error_description => SQLERRM
365 , p_error_proc_name => G_PKG_NAME||'.get_string'
366 , p_error_table => x_error_tbl
367 , x_error_table => x_error_tbl
368 );
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370
371 END get_string;
372
373 FUNCTION get_char
374 ( p_view_table IN bis_vg_types.View_Text_Table_Type
375 , p_pointer IN bis_vg_types.View_Character_Pointer_Type
376 , x_return_status OUT VARCHAR2
377 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
378 )
379 RETURN VARCHAR2
380 IS
381 BEGIN
382 RETURN Substr(p_view_table(p_pointer.row_num), p_pointer.col_num, 1);
383
384 EXCEPTION
385 when FND_API.G_EXC_ERROR then
386 x_return_status := FND_API.G_RET_STS_ERROR ;
387 RAISE FND_API.G_EXC_ERROR;
388 when FND_API.G_EXC_UNEXPECTED_ERROR then
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
391 when others then
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
393 BIS_VG_UTIL.Add_Error_Message
394 ( p_error_msg_id => SQLCODE
395 , p_error_description => SQLERRM
396 , p_error_proc_name => G_PKG_NAME||'.get_char'
397 , p_error_table => x_error_tbl
398 , x_error_table => x_error_tbl
399 );
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401
402 END get_char;
403
404 FUNCTION increment_pointer
405 ( p_view_table IN bis_vg_types.View_Text_Table_Type
406 , p_pointer IN bis_vg_types.View_Character_Pointer_Type
407 , x_return_status OUT VARCHAR2
408 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
409 )
410 RETURN bis_vg_types.view_character_pointer_type
411 is
412 l_str VARCHAR2(2000);
413 l_pointer bis_vg_types.view_character_pointer_type;
414 begin
415 BIS_DEBUG_PUB.Add('> increment_pointer');
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
417 l_str := p_view_table(p_pointer.row_num);
418 IF (p_pointer.col_num = Length(l_str)) THEN
419 -- if at end of table return null
420 IF(p_pointer.row_num < p_view_table.COUNT) then
421 l_pointer.row_num := p_pointer.row_num + 1;
422 l_pointer.col_num := 1;
423 END IF;
424 ELSE
425 l_pointer.row_num := p_pointer.row_num;
426 l_pointer.col_num := p_pointer.col_num + 1;
427 END IF;
428 BIS_DEBUG_PUB.Add('< increment_pointer');
429 RETURN l_pointer;
430
431
432 EXCEPTION
433 when FND_API.G_EXC_ERROR then
434 x_return_status := FND_API.G_RET_STS_ERROR ;
435 RAISE FND_API.G_EXC_ERROR;
436 when FND_API.G_EXC_UNEXPECTED_ERROR then
437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439 when others then
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
441 BIS_VG_UTIL.Add_Error_Message
442 ( p_error_msg_id => SQLCODE
443 , p_error_description => SQLERRM
444 , p_error_proc_name => G_PKG_NAME||'.increment_pointer'
445 , p_error_table => x_error_tbl
446 , x_error_table => x_error_tbl
447 );
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449
450 END increment_pointer;
451
452 -- concatenates p_View_Table_A with p_View_Table_B
453 PROCEDURE concatenate_Tables
454 ( p_View_Table_A IN BIS_VG_TYPES.View_Text_Table_Type
455 , p_View_Table_B IN BIS_VG_TYPES.View_Text_Table_Type
456 , x_View_Table OUT BIS_VG_TYPES.View_Text_Table_Type
457 , x_return_status OUT VARCHAR2
458 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
459 )
460 IS
461 --
462 l_count INTEGER;
463 BEGIN
464 BIS_DEBUG_PUB.Add('> concatenate_Tables');
465 x_return_status := FND_API.G_RET_STS_SUCCESS;
466 x_View_Table := p_View_Table_A;
467 l_count := x_View_Table.COUNT + 1;
468 for p_ind in 1 .. p_View_Table_B.COUNT loop
469 x_View_Table(l_count) := p_View_Table_B(p_ind);
470 l_count := l_count + 1;
471 end loop;
472 BIS_DEBUG_PUB.Add('< concatenate_Tables');
473
474
475 EXCEPTION
476 when FND_API.G_EXC_ERROR then
477 x_return_status := FND_API.G_RET_STS_ERROR ;
478 RAISE FND_API.G_EXC_ERROR;
479 when FND_API.G_EXC_UNEXPECTED_ERROR then
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
482 when others then
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
484 BIS_VG_UTIL.Add_Error_Message
485 ( p_error_msg_id => SQLCODE
486 , p_error_description => SQLERRM
487 , p_error_proc_name => G_PKG_NAME||'.concatenate_Tables'
488 , p_error_table => x_error_tbl
489 , x_error_table => x_error_tbl
490 );
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492
493 END concatenate_Tables;
494 --
495 --
496 PROCEDURE concatenate_Tables
497 ( p_View_Table_A IN BIS_VG_TYPES.Flex_Column_Comment_Table_Type
498 , p_View_Table_B IN BIS_VG_TYPES.Flex_Column_Comment_Table_Type
499 , x_View_Table OUT BIS_VG_TYPES.Flex_Column_Comment_Table_Type
500 , x_return_status OUT VARCHAR2
501 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
502 )
503 IS
504 --
505 l_count INTEGER;
506 BEGIN
507 BIS_DEBUG_PUB.Add('> concatenate_Tables Flex_Column_Comment');
508 x_return_status := FND_API.G_RET_STS_SUCCESS;
509 x_View_Table := p_View_Table_A;
510 l_count := x_View_Table.COUNT + 1;
511 for p_ind in 1 .. p_View_Table_B.COUNT loop
512 x_View_Table(l_count) := p_View_Table_B(p_ind);
513 l_count := l_count + 1;
514 end loop;
515 BIS_DEBUG_PUB.Add('< concatenate_Tables Flex_Column_Comment');
516
517
518 EXCEPTION
519 when FND_API.G_EXC_ERROR then
520 x_return_status := FND_API.G_RET_STS_ERROR ;
521 RAISE FND_API.G_EXC_ERROR;
522 when FND_API.G_EXC_UNEXPECTED_ERROR then
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 when others then
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
527 BIS_VG_UTIL.Add_Error_Message
528 ( p_error_msg_id => SQLCODE
529 , p_error_description => SQLERRM
530 , p_error_proc_name => G_PKG_NAME||'.concatenate_Tables'
531 , p_error_table => x_error_tbl
532 , x_error_table => x_error_tbl
533 );
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535
536 END concatenate_Tables;
537 --
538 --
539 FUNCTION equal_pointers
540 ( p_start_pointer IN BIS_VG_TYPES.view_character_pointer_type
541 , p_end_pointer IN BIS_VG_TYPES.view_character_pointer_type
542 , x_return_status OUT VARCHAR2
543 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
544 )
545 RETURN BOOLEAN IS
546 --
547 BEGIN
548 BIS_DEBUG_PUB.Add('> equal_pointers');
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550 -- both are null; return true
551 IF (null_pointer(p_start_pointer, x_return_status, x_error_Tbl) = TRUE)
552 AND (null_pointer(p_end_pointer, x_return_status, x_error_Tbl) = TRUE) THEN
553 RETURN TRUE;
554 END IF;
555 -- one of them is null; return false
556 IF (null_pointer(p_start_pointer, x_return_status, x_error_Tbl) = TRUE)
557 OR (null_pointer(p_end_pointer, x_return_status, x_error_Tbl) = TRUE) THEN
558 RETURN FALSE;
559 END IF;
560 -- if start_pointer = end_pointer return true
561 IF (p_start_pointer.row_num = p_end_pointer.row_num)
562 AND (p_start_pointer.col_num = p_end_pointer.col_num) THEN
563 RETURN TRUE;
564 ELSE
565 RETURN FALSE;
566 END IF;
567 BIS_DEBUG_PUB.Add('< equal_pointers');
568
569
570 EXCEPTION
571 when FND_API.G_EXC_ERROR then
572 x_return_status := FND_API.G_RET_STS_ERROR ;
573 RAISE FND_API.G_EXC_ERROR;
574 when FND_API.G_EXC_UNEXPECTED_ERROR then
575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
577 when others then
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
579 BIS_VG_UTIL.Add_Error_Message
580 ( p_error_msg_id => SQLCODE
581 , p_error_description => SQLERRM
582 , p_error_proc_name => G_PKG_NAME||'.equal_pointers'
583 , p_error_table => x_error_tbl
584 , x_error_table => x_error_tbl
585 );
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587
588 END equal_pointers;
589 --
590 --
591 PROCEDURE copy_part_of_Table
592 ( p_View_Table_A IN BIS_VG_TYPES.View_Text_Table_Type
593 , p_start_pointer IN BIS_VG_TYPES.view_character_pointer_type
594 , p_end_pointer IN BIS_VG_TYPES.view_character_pointer_type
595 , x_View_Table OUT BIS_VG_TYPES.View_Text_Table_Type
596 , x_return_status OUT VARCHAR2
597 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
598 )
599 IS
600 l_str VARCHAR2(2000);
601 l_start NUMBER;
602 l_end NUMBER;
603 j NUMBER;
604 BEGIN
605 BIS_DEBUG_PUB.Add('> copy part of table');
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607
608 print_view_pointer ( p_start_pointer
609 , x_return_status
610 , x_error_Tbl
611 );
612 print_view_pointer ( p_end_pointer
613 , x_return_status
614 , x_error_Tbl
615 );
616 IF ( equal_pointers ( p_start_pointer
617 , p_end_pointer
618 , x_return_status
619 , x_error_Tbl
620 )
621 ) THEN
622 RETURN;
623 END IF;
624 BIS_DEBUG_PUB.Add('count = '||p_view_table_a.COUNT);
625
626 IF (null_pointer(p_start_pointer, x_return_status, x_error_Tbl)
627 = TRUE
628 )
629 THEN RETURN;
630 END IF;
631
632 IF ( null_pointer ( p_end_pointer
633 , x_return_status
634 , x_error_Tbl
635 ) = TRUE
636 OR p_start_pointer.row_num < p_end_pointer.row_num
637 ) THEN
638 l_str := p_view_table_a(p_start_pointer.row_num);
639 BIS_DEBUG_PUB.Add('l_str = '||l_str);
640 x_view_table(1) := Substr(l_str, p_start_pointer.col_num);
641 BIS_DEBUG_PUB.Add('part str = '||x_view_table(1));
642
643 l_start := p_start_pointer.row_num + 1;
644
645 BIS_DEBUG_PUB.Add('after assignment');
646
647 IF (p_end_pointer.row_num IS NULL) THEN
648 l_end := p_view_table_a.COUNT;
649 else
650 l_end := p_end_pointer.row_num - 1;
651 END IF;
652
653 j := 2;
654 BIS_DEBUG_PUB.Add('l_end = '||l_end||' l_start = '||l_start);
655
656 IF (l_end >= l_start) THEN
657 FOR i IN l_start .. l_end LOOP
658 BIS_DEBUG_PUB.Add(i||'th = '||p_view_table_a(i));
659 BIS_DEBUG_PUB.Add('j = '||j);
660 x_view_table(j) := p_view_table_a(i);
661 BIS_DEBUG_PUB.Add(j||'th = '||x_view_table(j));
662 j := j + 1;
663 END LOOP;
664 END IF;
665
666 BIS_DEBUG_PUB.Add('after the loop');
667
668 IF (l_end <> p_view_table_a.COUNT AND p_end_pointer.col_num > 1) THEN
669 bis_debug_pub.ADD(' putting in the last line');
670 l_str := p_view_table_a(p_end_pointer.row_num);
671 j := x_view_table.COUNT + 1;
672 x_view_table(j) := Substr(l_str, 1, p_end_pointer.col_num - 1);
673 END IF;
674 ELSIF (p_start_pointer.row_num = p_end_pointer.row_num) THEN
675 l_str := p_view_table_a(p_start_pointer.row_num);
676 BIS_DEBUG_PUB.Add('l_str = '||l_str);
677 x_view_table(1) := Substr( l_str
678 , p_start_pointer.col_num
679 , p_end_pointer.col_num -
680 p_start_pointer.col_num
681 );
682 END IF;
683 BIS_DEBUG_PUB.Add('< copy part of table');
684
685
686 EXCEPTION
687 when FND_API.G_EXC_ERROR then
688 x_return_status := FND_API.G_RET_STS_ERROR ;
689 RAISE FND_API.G_EXC_ERROR;
690 when FND_API.G_EXC_UNEXPECTED_ERROR then
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
693 when others then
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
695 BIS_VG_UTIL.Add_Error_Message
696 ( p_error_msg_id => SQLCODE
697 , p_error_description => SQLERRM
698 , p_error_proc_name => G_PKG_NAME||'.copy_part_of_table'
699 , p_error_table => x_error_tbl
700 , x_error_table => x_error_tbl
701 );
702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
703
704 END copy_part_of_table;
705
706 /* ============================================================================
707 PROCEDURE : print_View_Text
708 PARAMETERS:
709 1. p_View_Text_Table table of varchars which holds the view text
710 2. x_return_status error or normal
711 3. x_error_Tbl table of error messages
712
713 COMMENT : Call this procedure to print the view text.
714 EXCEPTION : None
715 ========================================================================== */
716 PROCEDURE print_View_Text --{
717 ( p_View_Text_Table IN BIS_VG_TYPES.View_Text_Table_Type
718 , x_return_status OUT VARCHAR2
719 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
720 )
721 IS
722 --
723 i NUMBER;
724 --
725 BEGIN
726 BIS_DEBUG_PUB.Add('> print_View_Text');
727 x_return_status := FND_API.G_RET_STS_SUCCESS;
728 bis_debug_pub.ADD('# of rows in table = '||p_view_text_table.COUNT);
729 for i in 1 .. p_View_Text_Table.COUNT loop
730 BIS_DEBUG_PUB.Add(p_View_Text_Table(i));
731 end loop;
732 BIS_DEBUG_PUB.Add('< print_View_Text');
733
734
735 EXCEPTION
736 when FND_API.G_EXC_ERROR then
737 x_return_status := FND_API.G_RET_STS_ERROR ;
738 RAISE FND_API.G_EXC_ERROR;
739 when FND_API.G_EXC_UNEXPECTED_ERROR then
740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
742 when others then
743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
744 BIS_VG_UTIL.Add_Error_Message
745 ( p_error_msg_id => SQLCODE
746 , p_error_description => SQLERRM
747 , p_error_proc_name => G_PKG_NAME||'.print_View_Text'
748 , p_error_table => x_error_tbl
749 , x_error_table => x_error_tbl
750 );
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752
753 END print_View_Text; --}
754 --
755 /* ============================================================================
756 PROCEDURE : print_View_pointer
757 PARAMETERS:
758 1. p_View_Text_Table table of varchars which holds the view text
759 2. x_return_status error or normal
760 3. x_error_Tbl table of error messages
761
762 COMMENT : Call this procedure to print the view text.
763 EXCEPTION : None
764 ============================================================================ */
765 PROCEDURE print_View_pointer --{
766 ( p_pointer IN BIS_VG_TYPES.View_character_pointer_type
767 , x_return_status OUT VARCHAR2
768 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
769 )
770 IS
771 BEGIN
772 BIS_DEBUG_PUB.Add('> print_View_pointer');
773 x_return_status := FND_API.G_RET_STS_SUCCESS;
774 BIS_DEBUG_PUB.Add('pointer row num = '||p_pointer.row_num ||
775 ' pointer col num = '||p_pointer.col_num);
776 BIS_DEBUG_PUB.Add('< print_View_pointer');
777
778
779 EXCEPTION
780 when FND_API.G_EXC_ERROR then
781 x_return_status := FND_API.G_RET_STS_ERROR ;
782 RAISE FND_API.G_EXC_ERROR;
783 when FND_API.G_EXC_UNEXPECTED_ERROR then
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786 when others then
787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
788 BIS_VG_UTIL.Add_Error_Message
789 ( p_error_msg_id => SQLCODE
790 , p_error_description => SQLERRM
791 , p_error_proc_name => G_PKG_NAME||'.print_View_pointer'
792 , p_error_table => x_error_tbl
793 , x_error_table => x_error_tbl
794 );
795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
796
797 END print_View_pointer; --}
798 --
799
800 /* ============================================================================
801 PROCEDURE : position_before_characters
802 PARAMETERS:
803 1. p_View_Text_Table table of varchars which holds the view text
804 2. p_str string of charaters tobe replaced
805 3. x_return_status error or normal
806 4. x_error_Tbl table of error messages
807
808 COMMENT : Call this procedure to remove all the charaters in p_str
809 EXCEPTION : None
810 ============================================================================ */
811 -- remove th trailing characters in the table which are there in the p_str
812 FUNCTION position_before_characters
813 ( p_View_Text_Table IN BIS_VG_TYPES.view_text_table_type
814 , p_str IN VARCHAR2
815 , x_return_status OUT VARCHAR2
816 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
817 )
818 RETURN bis_vg_types.view_character_pointer_type
819 IS
820 l_pointer bis_vg_types.view_character_pointer_type;
821 BEGIN
822 BIS_DEBUG_PUB.Add('> position_before_characters');
823 x_return_status := FND_API.G_RET_STS_SUCCESS;
824 l_pointer.row_num := p_view_text_table.COUNT;
825 l_pointer.col_num := Length(p_view_text_table(l_pointer.row_num));
826 BIS_DEBUG_PUB.Add('< position_before_characters');
827 RETURN (position_before_characters(p_view_text_table
828 , p_str
829 , l_pointer
830 , x_return_status
831 , x_error_Tbl
832 )
833 );
834
835 EXCEPTION
836 when FND_API.G_EXC_ERROR then
837 x_return_status := FND_API.G_RET_STS_ERROR ;
838 RAISE FND_API.G_EXC_ERROR;
839 when FND_API.G_EXC_UNEXPECTED_ERROR then
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 when others then
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
844 BIS_VG_UTIL.Add_Error_Message
845 ( p_error_msg_id => SQLCODE
846 , p_error_description => SQLERRM
847 , p_error_proc_name => G_PKG_NAME||'.position_before_characters'
848 , p_error_table => x_error_tbl
849 , x_error_table => x_error_tbl
850 );
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852
853 END position_before_characters;
854
855 /* ============================================================================
856 FUNCTION : position_before_characters
857 ARGUMENTS:
858 1. p_View_Text_Table table of varchars which holds the view text
859 2. p_str string of charaters tobe replaced
860 3. p_pointer pointer to start positioning from
861 4. x_return_status error or normal
862 5. x_error_Tbl table of error messages
863 RETURNS Pointer to beginning of string (?)
864 COMMENT : Call this procedure to remove all the charaters in p_str
865 EXCEPTION : None
866 ============================================================================ */
867 -- remove th trailing characters in the table which are there in the p_str
868 FUNCTION position_before_characters
869 ( p_View_Text_Table IN BIS_VG_TYPES.view_text_table_type
870 , p_str IN VARCHAR2
871 , p_pointer IN bis_vg_types.view_character_pointer_type
872 , x_return_status OUT VARCHAR2
873 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
874 )
875 RETURN bis_vg_types.view_character_pointer_type
876 IS
877 l_pointer bis_vg_types.view_character_pointer_type;
878 l_char VARCHAR2(1);
879 l_pos NUMBER;
880 BEGIN
881 BIS_DEBUG_PUB.Add('> position_before_characters');
882 x_return_status := FND_API.G_RET_STS_SUCCESS;
883
884 IF (p_pointer.row_num IS NULL) THEN
885 return p_pointer;
886 END IF;
887 BIS_DEBUG_PUB.Add('p_str = ' || p_str);
888 print_view_pointer ( p_pointer
889 , x_return_status
890 , x_error_Tbl
891 );
892 l_pointer := decrement_pointer ( p_view_text_table
893 , p_pointer
894 , x_return_status
895 , x_error_Tbl
896 );
897 l_char := get_char( p_view_text_table
898 , l_pointer
899 , x_return_status
900 , x_error_Tbl
901 );
902 l_pos := Instr(p_str, l_char);
903
904 WHILE (l_pos <> 0) LOOP
905 BIS_DEBUG_PUB.Add('l_char = ' || l_char || ' l_pos = ' || l_pos);
906 BIS_VG_UTIL.print_View_Pointer( l_pointer
907 , x_return_status
908 , x_error_Tbl
909 );
910 l_pointer := decrement_pointer ( p_view_text_table
911 , l_pointer
912 , x_return_status
913 , x_error_Tbl
914 );
915 l_char := get_char( p_view_text_table
916 , l_pointer
917 , x_return_status
918 , x_error_Tbl
919 );
920 l_pos := Instr(p_str, l_char);
921 END LOOP;
922
923 bis_debug_pub.ADD('out of loop');
924 BIS_VG_UTIL.print_View_Pointer ( l_pointer
925 , x_return_status
926 , x_error_Tbl
927 );
928 BIS_DEBUG_PUB.Add('< position_before_characters');
929 RETURN l_pointer;
930
931 EXCEPTION
932 when FND_API.G_EXC_ERROR then
933 x_return_status := FND_API.G_RET_STS_ERROR ;
934 RAISE FND_API.G_EXC_ERROR;
935 when FND_API.G_EXC_UNEXPECTED_ERROR then
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
938 when others then
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
940 BIS_VG_UTIL.Add_Error_Message
941 ( p_error_msg_id => SQLCODE
942 , p_error_description => SQLERRM
943 , p_error_proc_name => G_PKG_NAME||'.position_before_characters'
944 , p_error_table => x_error_tbl
945 , x_error_table => x_error_tbl
946 );
947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
948
949
950 END position_before_characters;
951
952 -- decrements pointer by one
953 FUNCTION decrement_pointer
954 ( p_view_table IN bis_vg_types.View_Text_Table_Type
955 , p_pointer IN bis_vg_types.View_Character_Pointer_Type
956 , x_return_status OUT VARCHAR2
957 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
958 )
959 RETURN bis_vg_types.view_character_pointer_type
960 IS
961 l_str VARCHAR2(2000);
962 l_pointer bis_vg_types.view_character_pointer_type;
963 BEGIN
964 BIS_DEBUG_PUB.Add('> decrement_pointer');
965 x_return_status := FND_API.G_RET_STS_SUCCESS;
966
967 IF (p_pointer.row_num IS NULL) THEN
968 return p_pointer;
969 END IF;
970
971 l_pointer := p_pointer;
972 l_str := p_view_table(l_pointer.row_num);
973 IF (l_pointer.col_num = 1) THEN
974 IF (l_pointer.row_num = 1) THEN
975 RETURN NULL;
976 END IF;
977
978 l_pointer.row_num := l_pointer.row_num - 1;
979 l_str := p_view_table(l_pointer.row_num);
980 l_pointer.col_num := Length(l_str);
981 ELSE
982 l_pointer.col_num := l_pointer.col_num - 1;
983 END IF;
984 BIS_DEBUG_PUB.Add('< decrement_pointer');
985 RETURN l_pointer;
986
987 EXCEPTION
988 when FND_API.G_EXC_ERROR then
989 x_return_status := FND_API.G_RET_STS_ERROR ;
990 RAISE FND_API.G_EXC_ERROR;
991 when FND_API.G_EXC_UNEXPECTED_ERROR then
992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 when others then
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
996 BIS_VG_UTIL.Add_Error_Message
997 ( p_error_msg_id => SQLCODE
998 , p_error_description => SQLERRM
999 , p_error_proc_name => G_PKG_NAME||'.decrement_pointer'
1000 , p_error_table => x_error_tbl
1001 , x_error_table => x_error_tbl
1002 );
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004
1005 END decrement_pointer;
1006
1007 -- return TRUE if pointer is a null
1008 FUNCTION null_pointer
1009 (p_pointer IN bis_vg_types.view_character_pointer_type
1010 , x_return_status OUT VARCHAR2
1011 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1012 )
1013 RETURN BOOLEAN
1014 IS
1015 BEGIN
1016
1017 IF ( p_pointer.row_num IS NULL
1018 OR p_pointer.col_num IS NULL) THEN
1019 RETURN TRUE;
1020 END IF;
1021
1022 RETURN FALSE;
1023
1024 EXCEPTION
1025 when FND_API.G_EXC_ERROR then
1026 x_return_status := FND_API.G_RET_STS_ERROR ;
1027 RAISE FND_API.G_EXC_ERROR;
1028 when FND_API.G_EXC_UNEXPECTED_ERROR then
1029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1031 when others then
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1033 BIS_VG_UTIL.Add_Error_Message
1034 ( p_error_msg_id => SQLCODE
1035 , p_error_description => SQLERRM
1036 , p_error_proc_name => G_PKG_NAME||'.null_pointer'
1037 , p_error_table => x_error_tbl
1038 , x_error_table => x_error_tbl
1039 );
1040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041
1042 END null_pointer;
1043
1044 -- returns the row pointed to by the pointer
1045 FUNCTION get_row
1046 ( p_view_table IN bis_vg_types.View_Text_Table_Type
1047 , p_pointer IN bis_vg_types.View_Character_Pointer_Type
1048 , x_return_status OUT VARCHAR2
1049 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1050 )
1051 RETURN bis_vg_types.view_text_table_rec_type
1052 IS
1053 BEGIN
1054 IF ( null_pointer ( p_pointer
1055 , x_return_status
1056 , x_error_Tbl
1057 ) = TRUE
1058 OR p_pointer.row_num > p_view_table.COUNT
1059 OR p_pointer.row_num < 1) THEN
1060 RETURN NULL;
1061 END IF;
1062
1063 RETURN p_view_table(p_pointer.row_num);
1064
1065 EXCEPTION
1066 when FND_API.G_EXC_ERROR then
1067 x_return_status := FND_API.G_RET_STS_ERROR ;
1068 RAISE FND_API.G_EXC_ERROR;
1069 when FND_API.G_EXC_UNEXPECTED_ERROR then
1070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 when others then
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1074 BIS_VG_UTIL.Add_Error_Message
1075 ( p_error_msg_id => SQLCODE
1076 , p_error_description => SQLERRM
1077 , p_error_proc_name => G_PKG_NAME||'.get_row'
1078 , p_error_table => x_error_tbl
1079 , x_error_table => x_error_tbl
1080 );
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082
1083 END get_row;
1084
1085 -- increments the pointer to next row
1086 FUNCTION increment_pointer_by_row
1087 ( p_view_table IN bis_vg_types.View_Text_Table_Type
1088 , p_pointer IN bis_vg_types.View_Character_Pointer_Type
1089 , x_return_status OUT VARCHAR2
1090 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1091 )
1092 RETURN bis_vg_types.view_character_pointer_type
1093 IS
1094 l_pointer bis_vg_types.view_character_pointer_type;
1095 BEGIN
1096 BIS_DEBUG_PUB.Add('> increment_pointer_by_row ');
1097 x_return_status := FND_API.G_RET_STS_SUCCESS;
1098 IF ( null_pointer ( p_pointer
1099 , x_return_status
1100 , x_error_Tbl
1101 ) = TRUE
1102 OR p_pointer.row_num >= p_view_table.COUNT
1103 OR p_pointer.row_num < 1) THEN
1104 RETURN l_pointer;
1105 END IF;
1106
1107 l_pointer := p_pointer;
1108 l_pointer.row_num := l_pointer.row_num + 1;
1109 l_pointer.col_num := 1;
1110 BIS_DEBUG_PUB.Add('< increment_pointer_by_row ');
1111 RETURN l_pointer;
1112
1113 EXCEPTION
1114 when FND_API.G_EXC_ERROR then
1115 x_return_status := FND_API.G_RET_STS_ERROR ;
1116 RAISE FND_API.G_EXC_ERROR;
1117 when FND_API.G_EXC_UNEXPECTED_ERROR then
1118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 when others then
1121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1122 BIS_VG_UTIL.Add_Error_Message
1123 ( p_error_msg_id => SQLCODE
1124 , p_error_description => SQLERRM
1125 , p_error_proc_name => G_PKG_NAME||'.increment_pointer_by_row'
1126 , p_error_table => x_error_tbl
1127 , x_error_table => x_error_tbl
1128 );
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1130
1131 END increment_pointer_by_row;
1132
1133 -- this function returns the generated view name for the original view name
1134 FUNCTION get_generated_view_name
1135 ( p_view_name IN VARCHAR2
1136 , x_return_status OUT VARCHAR2
1137 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1138 )
1139 RETURN VARCHAR2
1140 IS
1141
1142 l_View_Name VARCHAR2(100);
1143 l_pos NUMBER;
1144 --
1145 BEGIN
1146
1147 bis_debug_pub.Add('> get_generated_view_name');
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149 l_pos := INSTR(p_View_Name, 'V_');
1150 l_View_Name := SUBSTR(p_View_Name, 1, l_pos - 1)
1151 || 'G'
1152 || SUBSTR( p_View_Name
1153 , l_pos + 1
1154 , LENGTH(p_View_Name) - l_pos
1155 );
1156 bis_debug_pub.Add('< get_generated_view_name');
1157 RETURN l_view_name;
1158
1159 EXCEPTION
1160 when FND_API.G_EXC_ERROR then
1161 x_return_status := FND_API.G_RET_STS_ERROR ;
1162 RAISE FND_API.G_EXC_ERROR;
1163 when FND_API.G_EXC_UNEXPECTED_ERROR then
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166 when others then
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1168 BIS_VG_UTIL.Add_Error_Message
1169 ( p_error_msg_id => SQLCODE
1170 , p_error_description => SQLERRM
1171 , p_error_proc_name => G_PKG_NAME||'.get_generated_view_name'
1172 , p_error_table => x_error_tbl
1173 , x_error_table => x_error_tbl
1174 );
1175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1176
1177 END get_generated_view_name;
1178
1179 -- these procedure check and puts the error message on the message stack
1180 PROCEDURE add_message
1181 ( p_msg_name IN VARCHAR2
1182 , p_msg_level IN NUMBER
1183 , x_return_status OUT VARCHAR2
1184 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1185 )
1186 IS
1187 BEGIN
1188 IF (fnd_msg_pub.check_msg_level(p_msg_level) = TRUE) THEN
1189 fnd_message.set_name(BIS_VG_TYPES.message_application, p_msg_name);
1190 fnd_msg_pub.ADD;
1191 END IF;
1192
1193 EXCEPTION
1194 when FND_API.G_EXC_ERROR then
1195 x_return_status := FND_API.G_RET_STS_ERROR ;
1196 RAISE FND_API.G_EXC_ERROR;
1197 when FND_API.G_EXC_UNEXPECTED_ERROR then
1198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1200 when others then
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1202 BIS_VG_UTIL.Add_Error_Message
1203 ( p_error_msg_id => SQLCODE
1204 , p_error_description => SQLERRM
1205 , p_error_proc_name => G_PKG_NAME||'.add_message'
1206 , p_error_table => x_error_tbl
1207 , x_error_table => x_error_tbl
1208 );
1209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1210
1211 END add_message;
1212
1213 PROCEDURE add_message
1214 ( p_msg_name IN VARCHAR2
1215 , p_msg_level IN NUMBER
1216 , p_token1 IN VARCHAR2
1217 , p_value1 IN VARCHAR2
1218 , x_return_status OUT VARCHAR2
1219 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1220 )
1221 IS
1222
1223 BEGIN
1224 IF (fnd_msg_pub.check_msg_level(p_msg_level) = TRUE) THEN
1225 fnd_message.set_name(BIS_VG_TYPES.message_application, p_msg_name);
1226 fnd_message.set_token(p_token1, p_value1);
1227 fnd_msg_pub.ADD;
1228 END IF;
1229
1230 EXCEPTION
1231 when FND_API.G_EXC_ERROR then
1232 x_return_status := FND_API.G_RET_STS_ERROR ;
1233 RAISE FND_API.G_EXC_ERROR;
1234 when FND_API.G_EXC_UNEXPECTED_ERROR then
1235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 when others then
1238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1239 BIS_VG_UTIL.Add_Error_Message
1240 ( p_error_msg_id => SQLCODE
1241 , p_error_description => SQLERRM
1242 , p_error_proc_name => G_PKG_NAME||'.add_message'
1243 , p_error_table => x_error_tbl
1244 , x_error_table => x_error_tbl
1245 );
1246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1247
1248 END add_message;
1249
1250 PROCEDURE add_message
1251 ( p_msg_name IN VARCHAR2
1252 , p_msg_level IN NUMBER
1253 , p_token1 IN VARCHAR2
1254 , p_value1 IN VARCHAR2
1255 , p_token2 IN VARCHAR2
1256 , p_value2 IN VARCHAR2
1257 , x_return_status OUT VARCHAR2
1258 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1259 )
1260 IS
1261 BEGIN
1262 IF (fnd_msg_pub.check_msg_level(p_msg_level) = TRUE) THEN
1263 fnd_message.set_name(BIS_VG_TYPES.message_application, p_msg_name);
1264 fnd_message.set_token(p_token1, p_value1);
1265 fnd_message.set_token(p_token2, p_value2);
1266 fnd_msg_pub.ADD;
1267 END IF;
1268
1269 EXCEPTION
1270 when FND_API.G_EXC_ERROR then
1271 x_return_status := FND_API.G_RET_STS_ERROR ;
1272 RAISE FND_API.G_EXC_ERROR;
1273 when FND_API.G_EXC_UNEXPECTED_ERROR then
1274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1276 when others then
1277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1278 BIS_VG_UTIL.Add_Error_Message
1279 ( p_error_msg_id => SQLCODE
1280 , p_error_description => SQLERRM
1281 , p_error_proc_name => G_PKG_NAME||'.add_message'
1282 , p_error_table => x_error_tbl
1283 , x_error_table => x_error_tbl
1284 );
1285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1286
1287 END add_message;
1288
1289 PROCEDURE add_message
1290 ( p_msg_name IN VARCHAR2
1291 , p_msg_level IN NUMBER
1292 , p_token1 IN VARCHAR2
1293 , p_value1 IN VARCHAR2
1294 , p_token2 IN VARCHAR2
1295 , p_value2 IN VARCHAR2
1296 , p_token3 IN VARCHAR2
1297 , p_value3 IN VARCHAR2
1298 , x_return_status OUT VARCHAR2
1299 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1300 )
1301 IS
1302 BEGIN
1303 IF (fnd_msg_pub.check_msg_level(p_msg_level) = TRUE) THEN
1304 fnd_message.set_name(BIS_VG_TYPES.message_application, p_msg_name);
1305 fnd_message.set_token(p_token1, p_value1);
1306 fnd_message.set_token(p_token2, p_value2);
1307 fnd_message.set_token(p_token3, p_value3);
1308 fnd_msg_pub.ADD;
1309 END IF;
1310
1311 EXCEPTION
1312 when FND_API.G_EXC_ERROR then
1313 x_return_status := FND_API.G_RET_STS_ERROR ;
1314 RAISE FND_API.G_EXC_ERROR;
1315 when FND_API.G_EXC_UNEXPECTED_ERROR then
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318 when others then
1319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1320 BIS_VG_UTIL.Add_Error_Message
1321 ( p_error_msg_id => SQLCODE
1322 , p_error_description => SQLERRM
1323 , p_error_proc_name => G_PKG_NAME||'.add_message'
1324 , p_error_table => x_error_tbl
1325 , x_error_table => x_error_tbl
1326 );
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328
1329 END add_message;
1330 --
1331 -- these procedure check and puts the error message on the message stack
1332 --
1333 PROCEDURE Add_Error_Message
1334 ( p_error_msg_name IN VARCHAR2
1335 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
1336 , p_error_proc_name IN VARCHAR2 := NULL
1337 , p_error_type IN VARCHAR2 := BIS_VG_UTIL.G_ERROR
1338 , p_error_table IN BIS_VG_UTIL.Error_Tbl_Type
1339 , x_error_table OUT BIS_VG_UTIL.Error_Tbl_Type
1340 )
1341 IS
1342 --
1343 l_error_rec BIS_VG_UTIL.Error_Rec_Type;
1344 --
1345 BEGIN
1346 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
1347 fnd_message.set_name(BIS_VG_TYPES.message_application, p_error_msg_name);
1348
1349 l_error_rec.Error_Msg_ID := NULL;
1350 l_error_rec.Error_Msg_Name := p_error_msg_name;
1351 l_error_rec.Error_proc_Name := p_error_proc_name;
1352 l_error_rec.Error_Description := fnd_message.get;
1353 l_error_rec.Error_Type := p_error_type;
1354 --
1355 x_error_table := p_error_table;
1356 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
1357 END IF;
1358 END Add_Error_Message;
1359
1360 PROCEDURE Add_Error_Message
1361 ( p_error_msg_name IN VARCHAR2
1362 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
1363 , p_error_proc_name IN VARCHAR2 := NULL
1364 , p_error_type IN VARCHAR2 := BIS_VG_UTIL.G_ERROR
1365 , p_token1 IN VARCHAR2
1366 , p_value1 IN VARCHAR2
1367 , p_error_table IN BIS_VG_UTIL.Error_Tbl_Type
1368 , x_error_table OUT BIS_VG_UTIL.Error_Tbl_Type
1369 )
1370 IS
1371 --
1372 l_error_rec BIS_VG_UTIL.Error_Rec_Type;
1373 --
1374 BEGIN
1375
1376 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
1377 fnd_message.set_name(BIS_VG_TYPES.message_application, p_error_msg_name);
1378 fnd_message.set_token(p_token1, p_value1);
1379
1380 l_error_rec.Error_Msg_ID := NULL;
1381 l_error_rec.Error_Msg_Name := p_error_msg_name;
1382 l_error_rec.Error_proc_Name := p_error_proc_name;
1383 l_error_rec.Error_Description := fnd_message.get;
1384 l_error_rec.Error_Type := p_error_type;
1385 --
1386 x_error_table := p_error_table;
1387 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
1388 END IF;
1389
1390 END Add_Error_Message;
1391
1392 PROCEDURE Add_Error_Message
1393 ( p_error_msg_name IN VARCHAR2
1394 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
1395 , p_error_proc_name IN VARCHAR2 := NULL
1396 , p_error_type IN VARCHAR2 := BIS_VG_UTIL.G_ERROR
1397 , p_token1 IN VARCHAR2
1398 , p_value1 IN VARCHAR2
1399 , p_token2 IN VARCHAR2
1400 , p_value2 IN VARCHAR2
1401 , p_error_table IN BIS_VG_UTIL.Error_Tbl_Type
1402 , x_error_table OUT BIS_VG_UTIL.Error_Tbl_Type
1403 )
1404 IS
1405 --
1406 l_error_rec BIS_VG_UTIL.Error_Rec_Type;
1407 --
1408 BEGIN
1409
1410 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
1411 fnd_message.set_name(BIS_VG_TYPES.message_application, p_error_msg_name);
1412 fnd_message.set_token(p_token1, p_value1);
1413 fnd_message.set_token(p_token2, p_value2);
1414
1415 l_error_rec.Error_Msg_ID := NULL;
1416 l_error_rec.Error_Msg_Name := p_error_msg_name;
1417 l_error_rec.Error_proc_Name := p_error_proc_name;
1418 l_error_rec.Error_Description := fnd_message.get;
1419 l_error_rec.Error_Type := p_error_type;
1420 --
1421 x_error_table := p_error_table;
1422 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
1423 END IF;
1424 END Add_Error_Message;
1425
1426 PROCEDURE Add_Error_Message
1427 ( p_error_msg_name IN VARCHAR2
1428 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
1429 , p_error_proc_name IN VARCHAR2 := NULL
1430 , p_error_type IN VARCHAR2 := BIS_VG_UTIL.G_ERROR
1431 , p_token1 IN VARCHAR2
1432 , p_value1 IN VARCHAR2
1433 , p_token2 IN VARCHAR2
1434 , p_value2 IN VARCHAR2
1435 , p_token3 IN VARCHAR2
1436 , p_value3 IN VARCHAR2
1437 , p_error_table IN BIS_VG_UTIL.Error_Tbl_Type
1438 , x_error_table OUT BIS_VG_UTIL.Error_Tbl_Type
1439 )
1440 IS
1441 --
1442 l_error_rec BIS_VG_UTIL.Error_Rec_Type;
1443 --
1444 BEGIN
1445
1446 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
1447 fnd_message.set_name(BIS_VG_TYPES.message_application, p_error_msg_name);
1448 fnd_message.set_token(p_token1, p_value1);
1449 fnd_message.set_token(p_token2, p_value2);
1450 fnd_message.set_token(p_token3, p_value3);
1451
1452 l_error_rec.Error_Msg_ID := NULL;
1453 l_error_rec.Error_Msg_Name := p_error_msg_name;
1454 l_error_rec.Error_proc_Name := p_error_proc_name;
1455 l_error_rec.Error_Description := fnd_message.get;
1456 l_error_rec.Error_Type := p_error_type;
1457 --
1458 x_error_table := p_error_table;
1459 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
1460 END IF;
1461 END Add_Error_Message;
1462
1463 PROCEDURE Add_Error_Message
1464 ( p_error_msg_id IN NUMBER := NULL
1465 , p_error_msg_name IN VARCHAR2 := NULL
1466 , p_error_description IN VARCHAR2 := NULL
1467 , p_error_proc_name IN VARCHAR2 := NULL
1468 , p_error_type IN VARCHAR2 := BIS_VG_UTIL.G_ERROR
1469 , p_error_table IN BIS_VG_UTIL.Error_Tbl_Type
1470 , x_error_table OUT BIS_VG_UTIL.Error_Tbl_Type
1471 )
1472 IS
1473 --
1474 l_error_rec BIS_VG_UTIL.Error_Rec_Type;
1475 --
1476 BEGIN
1477
1478 l_error_rec.Error_Msg_ID := p_error_msg_id;
1479 l_error_rec.Error_Msg_Name := p_error_msg_name;
1480 l_error_rec.Error_proc_Name := p_error_proc_name;
1481 l_error_rec.Error_Description := p_error_description;
1482 l_error_rec.Error_Type := p_error_type;
1483 --
1484 x_error_table := p_error_table;
1485 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
1486 END Add_Error_Message;
1487 --
1488
1489 --
1490 -- This function is called by Discoverer to return the translated prompt for a
1491 -- flex derived column in the generated view
1492 FUNCTION find_Flex_Prompt(p_db_link IN VARCHAR2
1493 , p_view_owner IN VARCHAR2
1494 , p_view_name IN VARCHAR2
1495 , p_column_name IN VARCHAR2
1496 , p_language IN VARCHAR2 )
1497 RETURN VARCHAR2
1498 IS
1499 CURSOR l_lang_cur IS
1500 SELECT installed_flag
1501 FROM fnd_languages_vl
1502 WHERE UPPER(language_code) = UPPER(p_language);
1503
1504 CURSOR l_get_comments_cur IS
1505 SELECT comments
1506 FROM all_col_comments
1507 WHERE table_name = UPPER(p_view_name)
1508 AND owner = UPPER(p_view_owner)
1509 AND column_name = UPPER(p_column_name);
1510
1511 CURSOR l_get_key_prmpt_cur(p_app_id IN NUMBER
1512 , p_flex_code IN fnd_id_flex_segments_tl.id_flex_code%TYPE
1513 , p_struc_num IN fnd_id_flex_segments_tl.id_flex_num%TYPE
1514 , p_app_column IN VARCHAR2
1515 ) IS
1516 SELECT form_above_prompt
1517 FROM fnd_id_flex_segments_tl
1518 WHERE application_id = p_app_id
1519 AND id_flex_code = p_flex_code
1520 AND id_flex_num = p_struc_num
1521 AND application_column_name = p_app_column
1522 AND language = UPPER(p_language);
1523
1524 CURSOR l_get_desc_ctxt_prmpt_cur(p_app_id IN NUMBER
1525 , p_desc_flex_name IN fnd_descriptive_flexs_tl.descriptive_flexfield_name%TYPE
1526 ) IS
1527 SELECT form_context_prompt
1528 FROM fnd_descriptive_flexs_tl
1529 WHERE application_id = p_app_id
1530 AND descriptive_flexfield_name = p_desc_flex_name
1531 AND language = UPPER(p_language);
1532
1533 CURSOR l_get_desc_seg_prmpt_cur(p_app_id IN NUMBER
1534 , p_desc_flex_name IN fnd_descriptive_flexs_tl.descriptive_flexfield_name%TYPE
1535 , p_context_code IN fnd_descr_flex_col_usage_tl.descriptive_flex_context_code%TYPE
1536 , p_app_column IN VARCHAR2
1537 ) IS
1538 SELECT form_above_prompt
1539 FROM fnd_descr_flex_col_usage_tl
1540 WHERE application_id = p_app_id
1541 AND descriptive_flexfield_name = p_desc_flex_name
1542 AND descriptive_flex_context_code = p_context_code
1543 AND application_column_name = p_app_column
1544 AND language = UPPER(p_language);
1545
1546 l_comments all_col_comments.comments%TYPE;
1547 l_flex_type VARCHAR2(12);
1548
1549 l_kf_prompt_stmt VARCHAR2(200);
1550
1551 l_flag fnd_languages_vl.installed_flag%TYPE;
1552 ex_lang_not_installed EXCEPTION;
1553 l_prompt VARCHAR2(80) :='';
1554 l_comma_pointer_1 NUMBER;
1555 l_comma_pointer_2 NUMBER;
1556 l_app_id NUMBER;
1557 l_flex_code fnd_id_flex_segments_tl.id_flex_code%TYPE;
1558 l_struc_num fnd_id_flex_segments_tl.id_flex_num%TYPE;
1559 l_app_column VARCHAR2(30);
1560 l_seg_name fnd_id_flex_segments.segment_name%TYPE;
1561 l_desc_flex_name fnd_descriptive_flexs_tl.descriptive_flexfield_name%TYPE;
1562 l_context_code fnd_descr_flex_col_usage_tl.descriptive_flex_context_code%TYPE;
1563 --v_debug VARCHAR2(100);
1564
1565 BEGIN
1566
1567 -- We will ignore if the p_dblink is NULL
1568 IF p_view_owner = '' THEN
1569 RETURN l_prompt;
1570 ELSIF p_view_name = '' THEN
1571 RETURN l_prompt;
1572 ELSIF p_column_name = '' THEN
1573 RETURN l_prompt;
1574 ELSIF p_language = '' THEN
1575 RETURN l_prompt;
1576 END IF;
1577 OPEN l_lang_cur;
1578 FETCH l_lang_cur INTO l_flag;
1579 CLOSE l_lang_cur;
1580 IF (NVL(l_flag,'D') NOT IN ('I','B')) THEN
1581 RAISE ex_lang_not_installed;
1582 ELSE
1583 OPEN l_get_comments_cur;
1584 FETCH l_get_comments_cur INTO l_comments;
1585 CLOSE l_get_comments_cur;
1586 IF l_comments IS NOT NULL THEN
1587 l_comma_pointer_1 := INSTRB(l_comments, ',', 1);
1588 l_comma_pointer_2 := INSTRB(l_comments, ',', l_comma_pointer_1 +1);
1589 l_flex_type := SUBSTRB(l_comments, 1, l_comma_pointer_1 - 1);
1590 IF l_flex_type ='KEY' THEN
1591 -- parse the comments and get info
1592 --v_debug := SUBSTRB(l_comments, (l_comma_pointer_1 +1), (l_comma_pointer_2 -l_comma_pointer_1 -1));
1593 l_app_id := TO_NUMBER(SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 - l_comma_pointer_1 -1));
1594 l_comma_pointer_1 := l_comma_pointer_2;
1595 l_comma_pointer_2 := INSTRB(l_comments, ',', l_comma_pointer_1 +1);
1596 l_flex_code := SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 -l_comma_pointer_1 -1);
1597 l_comma_pointer_1 := l_comma_pointer_2;
1598 l_comma_pointer_2 := INSTRB(l_comments, ',', l_comma_pointer_1 +1);
1599 l_struc_num := TO_NUMBER(SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 -l_comma_pointer_1-1));
1600 l_comma_pointer_1 := l_comma_pointer_2;
1601 -- this should be the last comma in the comment string for KEY Flex column
1602 l_comma_pointer_2 := INSTRB(l_comments, ',', l_comma_pointer_1 +1);
1603 l_seg_name := SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 -l_comma_pointer_1-1);
1604 l_app_column := SUBSTRB(l_comments, l_comma_pointer_2+1);
1605 OPEN l_get_key_prmpt_cur(l_app_id
1606 , l_flex_code
1607 , l_struc_num
1608 , l_app_column);
1609 FETCH l_get_key_prmpt_cur INTO l_prompt;
1610 CLOSE l_get_key_prmpt_cur;
1611 RETURN l_prompt;
1612 ELSIF l_flex_type = 'DESC CONTEXT' THEN
1613 l_app_id := TO_NUMBER(SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 - l_comma_pointer_1 -1));
1614 l_comma_pointer_1 := l_comma_pointer_2;
1615 l_comma_pointer_2 := INSTRB(l_comments, ',', l_comma_pointer_1 +1);
1616 l_desc_flex_name := SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 -l_comma_pointer_1 -1);
1617 OPEN l_get_desc_ctxt_prmpt_cur(l_app_id
1618 , l_desc_flex_name);
1619 FETCH l_get_desc_ctxt_prmpt_cur INTO l_prompt;
1620 CLOSE l_get_desc_ctxt_prmpt_cur;
1621 RETURN l_prompt;
1622 ELSIF l_flex_type = 'DESC SEGMENT' THEN
1623 --v_debug := SUBSTRB(l_comments, (l_comma_pointer_1 +1), (l_comma_pointer_2 -l_comma_pointer_1 -1));
1624 l_app_id := TO_NUMBER(SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 - l_comma_pointer_1 -1));
1625 l_comma_pointer_1 := l_comma_pointer_2;
1626 l_comma_pointer_2 := INSTRB(l_comments, ',', l_comma_pointer_1 +1);
1627 l_desc_flex_name := SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 -l_comma_pointer_1 -1);
1628 l_comma_pointer_1 := l_comma_pointer_2;
1629 l_comma_pointer_2 := INSTRB(l_comments, ',', l_comma_pointer_1 +1);
1630 l_context_code := SUBSTRB(l_comments, l_comma_pointer_1 +1, l_comma_pointer_2 -l_comma_pointer_1-1);
1631 l_app_column := SUBSTRB(l_comments, l_comma_pointer_2+1);
1632 OPEN l_get_desc_seg_prmpt_cur(l_app_id
1633 , l_desc_flex_name
1634 , l_context_code
1635 , l_app_column);
1636 FETCH l_get_desc_seg_prmpt_cur INTO l_prompt;
1637 CLOSE l_get_desc_seg_prmpt_cur;
1638 RETURN l_prompt;
1639 ELSE
1640 --nothing should fall through to here
1641 RETURN l_prompt;
1642 END IF;
1643 END IF;
1644 END IF;
1645 --dbms_output.put_line('fell through to outer if statement');
1646 RETURN l_prompt;
1647 EXCEPTION
1648 WHEN ex_lang_not_installed THEN
1649 fnd_message.set_name(BIS_VG_TYPES.message_application, 'BIS_VG_LANG_NOT_INSTALLED');
1650 fnd_message.set_token('LANG',UPPER(p_language));
1651 raise_application_error(-20001, fnd_message.get);
1652 WHEN OTHERS THEN
1653 raise_application_error(-20002, SQLERRM ||' p_db_link = '||p_db_link
1654 ||' p_view_owner = '||p_view_owner
1655 ||' p_view_name = '||p_view_name
1656 ||' p_column_name = '||p_column_name
1657 ||' p_language = '||p_language);
1658
1659
1660
1661 END find_Flex_Prompt;
1662
1663 END BIS_VG_UTIL;