[Home] [Help]
PACKAGE BODY: APPS.BIS_VG_LOOKUP
Source
1 PACKAGE BODY bis_vg_lookup AS
2 /* $Header: BISTLATB.pls 115.12 2004/03/01 21:23:31 dbowles ship $ */
3
4 -- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --
7 -- FILENAME
8 --
9 -- BISTLATB.pls
10 --
11 -- DESCRIPTION
12 --
13 -- body of view genrator to substitute lookup type
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 --
19 -- 23-JUL-98 Created
20 -- 09-MAR-99 Edited by WNASRALL
21 -- 11-DEC-01 Edited by DBOWLES Added dr driver comments.
22 --
23 G_PKG_NAME CONSTANT VARCHAR(30) := 'bis_vg_lookup';
24 /* ============================================================================
25 PROCEDURE : put_column_name
26 PARAMETERS:
27 1. p_View_Column_Table table of varchars to hold columns OF view text
28 2. p_Column_Pointer pointer to the lookup column in column tab
29 3. p_Mode mode of execution of the program
30 3. x_Column_Table table of varchars to hold additional columns
31 4. x_Column_Pointer pointer to the character after the delimiter
32 (column table)
33 5. x_return_status error or normal
34 6. x_error_Tbl table of error messages
35 --
36 COMMENT : Call this procedure to add a particular lookup column
37 to a view.
38 EXCEPTION : None
39 ===========================================================================*/
40 PROCEDURE put_column_name
41 ( p_View_Column_Table IN BIS_VG_TYPES.View_Text_Table_Type
42 , p_Column_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
43 , p_Mode IN NUMBER
44 , x_Column_Table OUT BIS_VG_TYPES.View_Text_Table_Type
45 , x_Column_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
46 , x_return_status OUT VARCHAR2
47 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
48 )
49 IS
50 l_str VARCHAR2(1000);
51 l_col VARCHAR2(1000);
52 l_num NUMBER;
53 BEGIN
54 BIS_DEBUG_PUB.Add('> put_column_name');
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56 --
57 -- skip the _LA part
58 l_str := bis_vg_parser.Skip_Tag( p_View_Column_Table
59 , p_column_pointer
60 , x_column_pointer
61 , x_return_status
62 , x_error_Tbl
63 );
64 --
65 bis_vg_util.print_view_pointer ( x_column_pointer
66 , x_return_status
67 , x_error_Tbl
68 );
69 --
70 l_str := bis_vg_util.get_row ( p_view_column_table
71 , p_column_pointer
72 , x_return_status
73 , x_error_Tbl
74 );
75 l_col := l_str;
76 l_num := x_column_pointer.col_num;
77 l_str := bis_vg_parser.get_string_token( l_str
78 , l_num
79 , ':'
80 , l_num
81 , x_return_status
82 , x_error_Tbl
83 );
84 --
85 IF (l_num IS NULL) THEN
86 x_column_pointer := bis_vg_util.increment_pointer_by_row
87 ( p_view_column_table
88 , x_column_pointer
89 , x_return_status
90 , x_error_Tbl
91 );
92 ELSE
93 x_column_pointer.col_num := l_num;
94 END IF;
95 --
96 bis_debug_pub.ADD('column pos = '||l_num);
97 --
98 IF (l_str IS NULL) THEN
99 BIS_VG_UTIL.Add_Error_message
100 ( p_error_msg_name => bis_vg_lookup.lat_col_tag_exp_msg
101 , p_error_proc_name => G_PKG_NAME||'.put_column_name'
102 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
103 , p_error_table => x_error_tbl
104 , x_error_table => x_error_tbl
105 );
106 bis_vg_log.update_failure_log( x_error_tbl
107 , x_return_status
108 , x_error_Tbl
109 );
110 RAISE FND_API.G_EXC_ERROR;
111
112 END IF;
113 --
114 -- save to output file
115 x_column_table(x_column_table.COUNT + 1) := l_str;
116 --
117 bis_debug_pub.ADD('column name = '||l_str);
118 -- bis_vg_util.print_view_text(x_column_table, x_return_status, x_error_Tbl );
119 bis_vg_util.print_view_pointer ( x_column_pointer
120 , x_return_status
121 , x_error_Tbl
122 );
123 --
124 IF(p_Mode = bis_vg_types.remove_tags_mode) THEN
125 x_column_table.DELETE;
126 x_column_table(1) := l_str;
127 END IF;
128 BIS_DEBUG_PUB.Add('< put_column_name');
129 --
130 EXCEPTION
131 when FND_API.G_EXC_ERROR then
132 x_return_status := FND_API.G_RET_STS_ERROR ;
133 RAISE FND_API.G_EXC_ERROR;
134 when FND_API.G_EXC_UNEXPECTED_ERROR then
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
137 when others then
138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
139 BIS_VG_UTIL.Add_Error_Message
140 ( p_error_msg_id => SQLCODE
141 , p_error_description => SQLERRM
142 , p_error_proc_name => G_PKG_NAME||'.put_column_name'
143 , p_error_table => x_error_tbl
144 , x_error_table => x_error_tbl
145 );
146 bis_vg_log.update_failure_log( x_error_tbl
147 , x_return_status
148 , x_error_Tbl
149 );
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151
152 END put_column_name;
153 --
154 /* ============================================================================
155 FUNCTION : get_select_statement
156 PARAMETERS:
157 1. p_lookup_table lookup table name
158 2. p_lookup_type lookup type;
159 3. p_lookup_column lookup column in the table to be returned
160 4. x_return_status error or normal
161 5. x_error_Tbl table of error messages
162 --
163 COMMENT : Call this function to get the select statement for lookup table
164 information to a view.
165 EXCEPTION : None
166 ===========================================================================*/
167 FUNCTION get_select_statement
168 ( p_lookup_table IN VARCHAR2
169 , p_lookup_type IN VARCHAR2
170 , p_lookup_column IN VARCHAR2
171 , x_return_status OUT VARCHAR2
172 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
173 )
174 RETURN VARCHAR2
175 IS
176 l_select VARCHAR2(1000);
177 BEGIN
178 BIS_DEBUG_PUB.Add('> get_select_statement');
179 x_return_status := FND_API.G_RET_STS_SUCCESS;
180 --
181 l_select := 'select lookup_code, '||p_lookup_column;
182 l_select := l_select || ' from '||p_lookup_table;
183 l_select := l_select || ' where lookup_type = '''||p_lookup_type||'''';
184 --
185 BIS_DEBUG_PUB.Add(' l_select = '||l_select);
186 --
187 BIS_DEBUG_PUB.Add('< get_select_statement');
188 RETURN l_select;
189 --
190
191 EXCEPTION
192 when FND_API.G_EXC_ERROR then
193 x_return_status := FND_API.G_RET_STS_ERROR ;
194 RAISE FND_API.G_EXC_ERROR;
195 when FND_API.G_EXC_UNEXPECTED_ERROR then
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 when others then
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
200 BIS_VG_UTIL.Add_Error_Message
201 ( p_error_msg_id => SQLCODE
202 , p_error_description => SQLERRM
203 , p_error_proc_name => G_PKG_NAME||'.get_select_statement'
204 , p_error_table => x_error_tbl
205 , x_error_table => x_error_tbl
206 );
207 bis_vg_log.update_failure_log( x_error_tbl
208 , x_return_status
209 , x_error_Tbl
210 );
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212
213 END get_select_statement;
214 --
215 /* ============================================================================
216 PROCEDURE : write_decode_statement
217 PARAMETERS:
218 1. p_expr pl/sql expression for decode
219 2. p_select select statement for the lookup table
220 3. x_Select_Table table of varchars to hold additional select
221 (select table)
222 4. x_return_status error or normal
223 5. x_error_Tbl table of error messages
224 --
225 COMMENT : Call this procedure to add a particular decode lookup
226 information to a view.
227 EXCEPTION : None
228 ===========================================================================*/
229 PROCEDURE write_decode_statement
230 ( p_expr IN VARCHAR2
231 , p_select IN VARCHAR2
232 , x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
233 , x_return_status OUT VARCHAR2
234 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
235 )
236 IS
237 l_cursor_id INTEGER;
238 l_lookup_code VARCHAR2(250);
239 l_meaning VARCHAR2(250);
240 l_dummy INTEGER;
241 l_select_table BIS_VG_TYPES.View_Text_Table_Type;
242 -- flag to allow verification of existence
243 -- of rows in the lookup table for the lookup
244 l_DECODE_flag BOOLEAN := TRUE;
245 l_stmt_count NUMBER := 0;
246 l_decode_nest_level NUMBER := 0;
247 l_lookup_type_valid BOOLEAN := FALSE;
248 l_num_rows NUMBER;
249 BEGIN
250 BIS_DEBUG_PUB.Add('> write_decode_statement');
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252 -- open the cursor
253 l_cursor_id := dbms_sql.open_cursor;
254 --
255 -- parse the statement
256 dbms_sql.parse(l_cursor_id, p_select, dbms_sql.V7);
257 --
258 BIS_DEBUG_PUB.Add('after parsing');
259 --
260 -- define output variables
261 dbms_sql.define_column(l_cursor_id, 1, l_lookup_code, 250);
262 dbms_sql.define_column(l_cursor_id, 2, l_meaning, 250);
263 --
264 -- execute
265 l_dummy := dbms_sql.execute(l_cursor_id);
266 --
267 l_num_rows := dbms_sql.fetch_rows(l_cursor_id);
268 --
269 IF (l_num_rows = 0) THEN
270 -- x_return_status := FND_API.G_RET_STS_ERROR;
271 -- Lookup type undefined set flag to return a NULL for the column
272 l_DECODE_flag := TRUE;
273 END IF;
274 --
275 WHILE (l_num_rows <> 0) LOOP
276 IF(l_DECODE_flag = TRUE) THEN
277 bis_debug_pub.add('l_DECODE_flag is TRUE');
278 ELSE
279 bis_debug_pub.add('l_DECODE_flag is FALSE');
280 END IF;
281 l_lookup_type_valid := TRUE;
282 IF(l_DECODE_flag = TRUE OR l_stmt_count = 100 ) THEN
283 -- first pass, need to start the decode
284 -- start the decode statement
285 IF (l_stmt_count = 100) THEN
286 l_stmt_count := 0;
287 l_decode_nest_level := l_decode_nest_level + 1;
288 x_select_table(x_select_table.COUNT + 1) := ', ';
289 END IF;
290 --
291 x_select_table(x_select_table.COUNT + 1) := 'DECODE';
292 --
293 bis_vg_util.create_Text_Table( '( ' || p_expr
294 , l_select_table
295 , x_return_status
296 , x_error_Tbl
297 );
298 bis_vg_util.concatenate_Tables( x_select_table
299 , l_select_table
300 , x_select_table
301 , x_return_status
302 , x_error_Tbl
303 );
304 l_DECODE_flag := FALSE;
305 END IF;
306 --
307 bis_debug_pub.add('l_lookup_code = ' || l_lookup_code);
308 bis_debug_pub.add('l_meaning = ' || l_meaning);
309 dbms_sql.column_value(l_cursor_id, 1, l_lookup_code);
310 dbms_sql.column_value(l_cursor_id, 2, l_meaning);
311 -- make sure that the ' in l_lookup_code are replace by ''
312 l_lookup_code := REPLACE(l_lookup_code,'''','''''');
313 x_select_table(x_select_table.COUNT + 1) := ', '|| ''''
314 || l_lookup_code
315 || '''';
316 --
317 -- make sure that ' in l_meaning are replaced by ''
318 l_meaning := REPLACE (l_meaning, '''', '''''');
319 x_select_table(x_select_table.COUNT + 1) := ', '|| ''''
320 || l_meaning
321 || '''';
322 l_stmt_count := l_stmt_count + 1;
323 l_num_rows := dbms_sql.fetch_rows(l_cursor_id);
324 END LOOP;
325 --
326 IF(l_DECODE_flag = TRUE) THEN -- no rows in lookup table, hence put NULL
327 x_select_table(x_select_table.COUNT + 1) := 'NULL';
328 ELSE -- need to end the decode as rows were returned and added
329 -- end the decode statement
330 x_select_table(x_select_table.COUNT + 1) := ', NULL )';
331 --
332 FOR i IN 1..l_decode_nest_level LOOP
333 -- write ') for outer level decodes
334 x_select_table(x_select_table.COUNT + 1) := ')';
335 END LOOP;
336 END IF;
337 dbms_sql.close_cursor(l_cursor_id);
338 --
339 BIS_DEBUG_PUB.Add('< write_decode_statement');
340 --
341 --
342 EXCEPTION
343 when FND_API.G_EXC_ERROR then
344 x_return_status := FND_API.G_RET_STS_ERROR ;
345 dbms_sql.close_cursor(l_cursor_id);
346 RAISE FND_API.G_EXC_ERROR;
347 when FND_API.G_EXC_UNEXPECTED_ERROR then
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
349 dbms_sql.close_cursor(l_cursor_id);
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 when others then
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
353 dbms_sql.close_cursor(l_cursor_id);
354 BIS_VG_UTIL.Add_Error_Message
355 ( p_error_msg_id => SQLCODE
356 , p_error_description => SQLERRM
357 , p_error_proc_name => G_PKG_NAME||'.write_decode_statement'
358 , p_error_table => x_error_tbl
359 , x_error_table => x_error_tbl
360 );
361 bis_vg_log.update_failure_log( x_error_tbl
362 , x_return_status
363 , x_error_Tbl
364 );
365
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367
368 END write_decode_statement;
369 --
370 /* ============================================================================
371 PROCEDURE : put_decode_statement
372 PARAMETERS:
373 1. p_expr pl/sql expression for decode
374 2. p_lookup_table lookup table name
375 3. p_lookup_type lookup type;
376 4. p_lookup_column lookup column in the table to be returned
377 5. x_Select_Table table of varchars to hold additional select
378 (select table)
379 6. x_return_status error or normal
380 7. x_error_Tbl table of error messages
381 --
382 COMMENT : Call this procedure to add a particular decode lookup
383 information to a view.
384 EXCEPTION : None
385 ===========================================================================*/
386 PROCEDURE put_decode_statement
387 ( p_expr IN VARCHAR2
388 , p_lookup_table IN VARCHAR2
389 , p_lookup_type IN VARCHAR2
390 , p_lookup_column IN VARCHAR2
391 , x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
392 , x_return_status OUT VARCHAR2
393 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
394 )
395 IS
396 l_select VARCHAR2(1000);
397 BEGIN
398 BIS_DEBUG_PUB.Add('> put_decode_statement');
399 x_return_status := FND_API.G_RET_STS_SUCCESS;
400 BIS_DEBUG_PUB.Add('p_lookup_column = ' || p_lookup_column);
401 BIS_DEBUG_PUB.Add('p_lookup_table = ' || p_lookup_table);
402 BIS_DEBUG_PUB.Add('p_lookup_type = ' || p_lookup_type);
403 BIS_DEBUG_PUB.Add('p_expr = ' || p_expr);
404 -- select statement
405 l_select := get_select_statement( p_lookup_table
406 , p_lookup_type
407 , p_lookup_column
408 , x_return_status
409 , x_error_Tbl
410 );
411 --
412 --
413 BIS_DEBUG_PUB.Add(' l_select = '||l_select);
414 --
415 -- write the decode statement
416 write_decode_statement ( p_expr
417 , l_select
418 , x_select_table
419 , x_return_status
420 , x_error_Tbl
421 );
422 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
423 THEN
424 BIS_VG_UTIL.Add_Error_message
425 ( p_error_msg_name => bis_vg_lookup.LAT_INVALID_LOOKUP_TYPE_MSG
426 , p_error_proc_name => G_PKG_NAME||'. write_decode_statement'
427 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
428 , p_token1 => 'tag'
429 , p_value1 => '_LA:' || p_expr || ':' || p_lookup_table || ':' ||
430 p_lookup_type || ':' || p_lookup_column
431 , p_token2 => 'sel'
432 , p_value2 => l_select
433 , p_error_table => x_error_tbl
434 , x_error_table => x_error_tbl
435 );
436 bis_vg_log.update_failure_log( x_error_tbl
437 , x_return_status
438 , x_error_Tbl
439 );
440 RAISE FND_API.G_EXC_ERROR;
441 END IF;
442
443 --
444 BIS_DEBUG_PUB.Add('< put_decode_statement');
445 --
446
447 EXCEPTION
448 when FND_API.G_EXC_ERROR then
449 x_return_status := FND_API.G_RET_STS_ERROR ;
450 RAISE FND_API.G_EXC_ERROR;
451 when FND_API.G_EXC_UNEXPECTED_ERROR then
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 when others then
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
456 BIS_VG_UTIL.Add_Error_Message
457 ( p_error_msg_id => SQLCODE
458 , p_error_description => SQLERRM
459 , p_error_proc_name => G_PKG_NAME||'.put_decode_statement'
460 , p_error_table => x_error_tbl
461 , x_error_table => x_error_tbl
462 );
463 bis_vg_log.update_failure_log( x_error_tbl
464 , x_return_status
465 , x_error_Tbl
466 );
467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
468
469
470 END put_decode_statement;
471 --
472 /* ============================================================================
473 PROCEDURE : put_decode_statement_lang
474 PARAMETERS:
475 1. p_expr pl/sql expression for decode
476 2. p_lookup_table lookup table name
477 3. p_lookup_type lookup type;
478 4. p_lookup_column lookup column in the table to be returned
479 5. p_language language for this lookup
480 6. x_Select_Table table of varchars to hold additional select
481 7. x_return_status error or normal
482 8. x_error_Tbl table of error messages
483 (select table)
484 --
485 COMMENT : Call this procedure to add a particular decode lookup
486 information to a view for the given language.
487 EXCEPTION : None
488 ===========================================================================*/
489 PROCEDURE put_decode_statement_lang
490 ( p_expr IN VARCHAR2
491 , p_lookup_table IN VARCHAR2
492 , p_lookup_type IN VARCHAR2
493 , p_lookup_column IN VARCHAR2
494 , p_language IN VARCHAR2
495 , x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
496 , x_return_status OUT VARCHAR2
497 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
498 )
499 IS
500 l_select VARCHAR2(1000);
501 l_cursor_id INTEGER;
502 l_lookup_code VARCHAR2(250);
503 l_meaning VARCHAR2(250);
504 l_dummy INTEGER;
505 BEGIN
506 --
507 BIS_DEBUG_PUB.Add('> put_decode_statement_lang');
508 x_return_status := FND_API.G_RET_STS_SUCCESS;
509 --
510 BIS_DEBUG_PUB.Add('> put_decode_statement');
511 BIS_DEBUG_PUB.Add('p_lookup_column = ' || p_lookup_column);
512 BIS_DEBUG_PUB.Add('p_lookup_table = ' || p_lookup_table);
513 BIS_DEBUG_PUB.Add('p_lookup_type = ' || p_lookup_type);
514 BIS_DEBUG_PUB.Add('p_expr = ' || p_expr);
515 BIS_DEBUG_PUB.Add('p_language = ' || p_language);
516 -- select statement
517 l_select := get_select_statement( p_lookup_table
518 , p_lookup_type
519 , p_lookup_column
520 , x_return_status
521 , x_error_Tbl
522 );
523 -- add the language restraint
524 l_select := l_select || ' and language = '''|| p_language ||'''';
525 --
526 BIS_DEBUG_PUB.Add(' l_select = '||l_select);
527 --
528 -- write the decode statement
529 write_decode_statement ( p_expr
530 , l_select
531 , x_select_table
532 , x_return_status
533 , x_error_Tbl
534 );
535 --
536 BIS_DEBUG_PUB.Add('< put_decode_statement_lang');
537 --
538
539 EXCEPTION
540 when FND_API.G_EXC_ERROR then
541 x_return_status := FND_API.G_RET_STS_ERROR ;
542 RAISE FND_API.G_EXC_ERROR;
543 when FND_API.G_EXC_UNEXPECTED_ERROR then
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
546 when others then
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
548 BIS_VG_UTIL.Add_Error_Message
549 ( p_error_msg_id => SQLCODE
550 , p_error_description => SQLERRM
551 , p_error_proc_name => G_PKG_NAME||'.put_decode_statement_lang'
552 , p_error_table => x_error_tbl
553 , x_error_table => x_error_tbl
554 );
555 bis_vg_log.update_failure_log( x_error_tbl
556 , x_return_status
557 , x_error_Tbl
558 );
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560
561 END put_decode_statement_lang;
562 --
563 /* ============================================================================
564 PROCEDURE : put_decode_statement_languages
565 PARAMETERS:
566 1. p_expr pl/sql expression for decode
567 2. p_lookup_table lookup table name
568 3. p_lookup_type lookup type;
569 4. p_lookup_column lookup column in the table to be returned
570 5. x_Select_Table table of varchars to hold additional select
571 (select table)
572 6. x_return_status error or normal
573 7. x_error_Tbl table of error messages
574 --
575 COMMENT : Call this procedure to add a particular decode lookup
576 information to a view.
577 EXCEPTION : None
578 ===========================================================================*/
579 PROCEDURE put_decode_statement_languages
580 ( p_expr IN VARCHAR2
581 , p_lookup_table IN VARCHAR2
582 , p_lookup_type IN VARCHAR2
583 , p_lookup_column IN VARCHAR2
584 , x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
585 , x_return_status OUT VARCHAR2
586 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
587 )
588 IS
589 l_select VARCHAR2(1000);
590 l_cursor_id INTEGER;
591 l_language VARCHAR2(250);
592 l_dummy INTEGER;
593 BEGIN
594 BIS_DEBUG_PUB.Add('> put_decode_statement_languages');
595 -- open the cursor
596 l_cursor_id := dbms_sql.open_cursor;
597 --
598 -- select statement
599 l_select := 'select language from :table_name' ||
600 'where lookup_type = :type';
601 --
602 -- parse the statement
603 dbms_sql.parse(l_cursor_id, l_select, dbms_sql.NATIVE);
604 --
605 -- bind the inout variables
606 dbms_sql.bind_variable(l_cursor_id, ':table_name', p_lookup_table);
607 dbms_sql.bind_variable(l_cursor_id, ':type', p_lookup_type);
608 --
609 -- define output variables
610 dbms_sql.define_column(l_cursor_id, 1, l_language, 250);
611 --
612 -- execute
613 l_dummy := dbms_sql.execute(l_cursor_id);
614 --
615 -- start the decode statement
616 x_select_table(x_select_table.COUNT + 1) := 'DECODE';
617 x_select_table(x_select_table.COUNT + 1) := '( USERENV(''LANG'')';
618 --
619 WHILE (dbms_sql.fetch_rows(l_cursor_id) <> 0) LOOP
620 --
621 dbms_sql.column_value(l_cursor_id, 1, l_language);
622 --
623 -- get the language
624 x_select_table(x_select_table.COUNT + 1) := ', '||l_language;
625 --
626 -- put the decode for that language
627 put_decode_statement_lang ( p_expr
628 , p_lookup_table
629 , p_lookup_type
630 , p_lookup_column
631 , l_language
632 , x_Select_Table
633 , x_return_status
634 , x_error_Tbl
635 );
636 --
637 END LOOP;
638 --
639 -- end the decode statement
640 x_select_table(x_select_table.COUNT + 1) := ', NULL )';
641 --
642 dbms_sql.close_cursor(l_cursor_id);
643 BIS_DEBUG_PUB.Add('< put_decode_statement_languages');
644 --
645
646
647
648 EXCEPTION
649 when FND_API.G_EXC_ERROR then
650 x_return_status := FND_API.G_RET_STS_ERROR ;
651 dbms_sql.close_cursor(l_cursor_id);
652 RAISE FND_API.G_EXC_ERROR;
653 when FND_API.G_EXC_UNEXPECTED_ERROR then
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
655 dbms_sql.close_cursor(l_cursor_id);
656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
657 when others then
658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
659 dbms_sql.close_cursor(l_cursor_id);
660 BIS_VG_UTIL.Add_Error_Message
661 ( p_error_msg_id => SQLCODE
662 , p_error_description => SQLERRM
663 , p_error_proc_name => G_PKG_NAME||'.put_decode_statement_languages'
664 , p_error_table => x_error_tbl
665 , x_error_table => x_error_tbl
666 );
667 bis_vg_log.update_failure_log( x_error_tbl
668 , x_return_status
669 , x_error_Tbl
670 );
671
672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
673
674
675 END put_decode_statement_languages;
676 --
677 /* ============================================================================
678 FUNCTION : check_language
679 PARAMETERS:
680 1. p_lookup_table lookup table name
681 2. x_return_status error or normal
682 3. x_error_Tbl table of error messages
683 RETURN : BOOLEAN - TRUE if mutli language supported
684 COMMENT : Call this procedure to find out if multi language is supported
685 EXCEPTION : None
686 ===========================================================================*/
687 FUNCTION check_language
688 ( p_lookup_table IN VARCHAR2
689 , x_return_status OUT VARCHAR2
690 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
691 )
692 RETURN BOOLEAN
693 IS
694 CURSOR col_cursor IS
695 SELECT 1
696 FROM user_tab_columns
697 WHERE table_name=p_lookup_table
698 AND column_name='LANGUAGE';
699 l_return_value boolean;
700 l_dummy number;
701 BEGIN
702
703 open col_cursor;
704 fetch col_cursor into l_dummy ;
705 l_return_value := col_cursor%found ;
706 close col_cursor ;
707
708 return(l_return_value);
709 --
710
711
712 EXCEPTION
713 when FND_API.G_EXC_ERROR then
714 x_return_status := FND_API.G_RET_STS_ERROR ;
715 if (col_cursor%ISOPEN) THEN
716 CLOSE col_cursor;
717 end if;
718 RAISE FND_API.G_EXC_ERROR;
719 when FND_API.G_EXC_UNEXPECTED_ERROR then
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
721 if (col_cursor%ISOPEN) THEN
722 CLOSE col_cursor;
723 end if;
724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
725 when others then
726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
727 if (col_cursor%ISOPEN) THEN
728 CLOSE col_cursor;
729 end if;
730 BIS_VG_UTIL.Add_Error_Message
731 ( p_error_msg_id => SQLCODE
732 , p_error_description => SQLERRM
733 , p_error_proc_name => G_PKG_NAME||'.check_language'
734 , p_error_table => x_error_tbl
735 , x_error_table => x_error_tbl
736 );
737 bis_vg_log.update_failure_log( x_error_tbl
738 , x_return_status
739 , x_error_Tbl
740 );
741
742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
743
744 END check_language;
745 --
746 /* ============================================================================
747 PROCEDURE : add_select_info
748 PARAMETERS:
749 1. p_expr pl/sql expression for decode
750 2. p_lookup_table lookup table name
751 3. p_lookup_type lookup type;
752 4. p_lookup_column lookup column in the table to be returned
753 5. x_Select_Table table of varchars to hold additional select
754 (select table)
755 6. x_return_status error or normal
756 7. x_error_Tbl table of error messages
757 --
758 COMMENT : Call this procedure to add a particular lookup select
759 information to a view.
760 EXCEPTION : None
761 ===========================================================================*/
762 PROCEDURE add_select_info
763 ( p_expr IN VARCHAR2
764 , p_lookup_table IN VARCHAR2
765 , p_lookup_type IN VARCHAR2
766 , p_lookup_column IN VARCHAR2
767 , x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
768 , x_return_status OUT VARCHAR2
769 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
770 )
771 IS
772 l_lang BOOLEAN := FALSE;
773 BEGIN
774 BIS_DEBUG_PUB.Add('> add_select_info');
775 --
776 -- find if the language is supported for this lookup
777 l_lang := check_language(p_lookup_table, x_return_status, x_error_Tbl);
778 --
779 -- debug info
780 IF (l_lang = TRUE) THEN
781 BIS_DEBUG_PUB.Add('language is there for table '||p_lookup_table);
782 --
783 put_decode_statement_languages( p_expr
784 , p_lookup_table
785 , p_lookup_type
786 , p_lookup_column
787 , x_select_table
788 , x_return_status
789 , x_error_Tbl
790 );
791 --
792 ELSE
793 BIS_DEBUG_PUB.Add('language not there in table '||p_lookup_table);
794 --
795 -- now put in the decode statement
796 put_decode_statement( p_expr
797 , p_lookup_table
798 , p_lookup_type
799 , p_lookup_column
800 , x_select_table
801 , x_return_status
802 , x_error_Tbl
803 );
804 END IF;
805 BIS_DEBUG_PUB.Add('< add_select_info');
806 --
807
808 EXCEPTION
809 when FND_API.G_EXC_ERROR then
810 x_return_status := FND_API.G_RET_STS_ERROR ;
811 RAISE FND_API.G_EXC_ERROR;
812 when FND_API.G_EXC_UNEXPECTED_ERROR then
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
815 when others then
816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
817 BIS_VG_UTIL.Add_Error_Message
818 ( p_error_msg_id => SQLCODE
819 , p_error_description => SQLERRM
820 , p_error_proc_name => G_PKG_NAME||'.add_select_info'
821 , p_error_table => x_error_tbl
822 , x_error_table => x_error_tbl
823 );
824 bis_vg_log.update_failure_log( x_error_tbl
825 , x_return_status
826 , x_error_Tbl
827 );
828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
829
830 END add_select_info;
831 --
832 --============================================================================
833 --PROCEDURE : parse_LA_select
834 -- PARAMETERS:
835 -- 1. p_View_Select_Table table of varchars to hold select OF view text
836 -- 2. p_Select_Pointer pointer to the lookup column in select table
837 -- 3. x_expr PL-SQL expression or column name
838 -- 4. x_lookup_table Lookup table to insert in select
839 -- 5. x_lookup_type Lookup code in the lookup table
840 -- 6. x_lookup_column name of column in lookup table
841 -- 7. x_Select_Pointer pointer to the character after the delimiter
842 -- (select table)
843 -- 8. x_return_status error or normal
844 -- 9. x_error_Tbl table of error messages
845 ----
846 -- COMMENT : Call this procedure to add a particular lookup select
847 -- information to a view.
848 --EXCEPTION : None
849 -- ==========================================================================
850 PROCEDURE parse_LA_select
851 ( p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
852 , p_Select_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
853 , x_expr OUT VARCHAR2
854 , x_lookup_table OUT VARCHAR2
855 , x_lookup_type OUT VARCHAR2
856 , x_lookup_column OUT VARCHAR2
857 , x_Select_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
858 , x_return_status OUT VARCHAR2
859 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
860 )
861 IS
862 l_str VARCHAR2(2000);
863 l_tmp_pointer bis_vg_types.View_Character_Pointer_Type;
864 BEGIN
865
866 BIS_DEBUG_PUB.Add('> parse_LA_select');
867 -- skip the _LA part
868 l_str := bis_vg_parser.Skip_Tag( p_View_Select_Table
869 , p_select_pointer
870 , x_select_pointer
871 , x_return_status
872 , x_error_Tbl
873 );
874 l_str := bis_vg_parser.get_expression( p_View_Select_Table
875 , p_Select_Pointer
876 , l_tmp_pointer
877 , x_return_status
878 , x_error_Tbl
879 );
880 IF (bis_vg_util.equal_pointers(
881 l_tmp_pointer
882 , x_select_pointer
883 , x_return_status
884 , x_error_Tbl
885 )
886 ) THEN
887 BIS_VG_UTIL.Add_Error_message
888 ( p_error_msg_name => bis_vg_lookup.LAT_COL_TAG_EXP_NO_EXP_MSG
889 , p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
890 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
891 , p_token1 => 'tag'
892 , p_value1 => l_str
893 , p_error_table => x_error_tbl
894 , x_error_table => x_error_tbl
895 );
896 bis_vg_log.update_failure_log( x_error_tbl
897 , x_return_status
898 , x_error_Tbl
899 );
900 RAISE FND_API.G_EXC_ERROR;
901 END IF;
902
903 l_tmp_pointer := bis_vg_util.increment_pointer
904 ( p_View_Select_Table
905 , l_tmp_pointer
906 , x_return_status
907 , x_error_Tbl
908 );
909
910 -- get the expression
911 x_expr := bis_vg_parser.get_token_increment_pointer( p_View_Select_Table
912 , x_select_pointer
913 , ':'
914 , x_select_pointer
915 , x_return_status
916 , x_error_Tbl
917 );
918 --
919 IF (x_expr IS NULL
920 OR
921 bis_vg_util.equal_pointers(
922 l_tmp_pointer
923 , x_select_pointer
924 , x_return_status
925 , x_error_Tbl
926 )
927 ) THEN
928 BIS_VG_UTIL.Add_Error_message
929 ( p_error_msg_name => bis_vg_lookup.LAT_COL_TAG_EXP_NO_EXP_MSG
930 , p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
931 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
932 , p_token1 => 'tag'
933 , p_value1 => l_str
934 , p_error_table => x_error_tbl
935 , x_error_table => x_error_tbl
936 );
937 bis_vg_log.update_failure_log( x_error_tbl
938 , x_return_status
939 , x_error_Tbl
940 );
941 RAISE FND_API.G_EXC_ERROR;
942 END IF;
943 --
944 -- replace two consecutive single quotes by one single quote
945 x_expr := REPLACE(x_expr, '''''', '''');
946 BIS_DEBUG_PUB.Add('x_expr = '||x_expr);
947 --
948 -- get the lookup table
949 x_lookup_table := bis_vg_parser.get_token_increment_pointer
950 ( p_View_Select_Table
951 , x_select_pointer
952 , ':'''
953 , x_select_pointer
954 , x_return_status
955 , x_error_Tbl
956 );
957 BIS_DEBUG_PUB.Add('x_lookup_table = '||x_lookup_table);
958 --
959 IF (x_lookup_table IS NULL
960 OR
961 bis_vg_util.equal_pointers(
962 l_tmp_pointer
963 , x_select_pointer
964 , x_return_status
965 , x_error_Tbl
966 )
967 ) THEN
968 BIS_VG_UTIL.Add_Error_message
969 ( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_EXP_NO_TABLE_MSG
970 , p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
971 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
972 , p_token1 => 'tag'
973 , p_value1 => l_str
974 , p_error_table => x_error_tbl
975 , x_error_table => x_error_tbl
976 );
977 bis_vg_log.update_failure_log( x_error_tbl
978 , x_return_status
979 , x_error_Tbl
980 );
981 RAISE FND_API.G_EXC_ERROR;
982
983 END IF;
984 --
985 -- get the lookup type
986 x_lookup_type := bis_vg_parser.get_token_increment_pointer
987 ( p_View_Select_Table
988 , x_select_pointer
989 , ':'''
990 , x_select_pointer
991 , x_return_status
992 , x_error_Tbl
993 );
994 BIS_DEBUG_PUB.Add('x_lookup_type = '||x_lookup_type);
995 --
996 IF (x_lookup_type IS NULL
997 OR
998 bis_vg_util.equal_pointers(
999 l_tmp_pointer
1000 , x_select_pointer
1001 , x_return_status
1002 , x_error_Tbl
1003 )
1004 ) THEN
1005 BIS_VG_UTIL.Add_Error_message
1006 ( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_EXP_NO_TYPE_MSG
1007 , p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
1008 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1009 , p_token1 => 'tag'
1010 , p_value1 => l_str
1011 , p_error_table => x_error_tbl
1012 , x_error_table => x_error_tbl
1013 );
1014 bis_vg_log.update_failure_log( x_error_tbl
1015 , x_return_status
1016 , x_error_Tbl
1017 );
1018 RAISE FND_API.G_EXC_ERROR;
1019
1020 END IF;
1021 --
1022 -- get the lookup column
1023 x_lookup_column := bis_vg_parser.get_token_increment_pointer
1024 ( p_View_Select_Table
1025 , x_select_pointer
1026 , ''''
1027 , x_select_pointer
1028 , x_return_status
1029 , x_error_Tbl
1030 );
1031 --
1032 BIS_DEBUG_PUB.Add('x_lookup_column = '||x_lookup_column);
1033 --
1034 IF (x_lookup_column IS NULL
1035 ) THEN
1036 BIS_VG_UTIL.Add_Error_message
1037 ( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_EXP_NO_COL_MSG
1038 , p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
1039 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1040 , p_token1 => 'tag'
1041 , p_value1 => l_str
1042 , p_error_table => x_error_tbl
1043 , x_error_table => x_error_tbl
1044 );
1045 bis_vg_log.update_failure_log( x_error_tbl
1046 , x_return_status
1047 , x_error_Tbl
1048 );
1049 RAISE FND_API.G_EXC_ERROR;
1050 END IF;
1051
1052 BIS_DEBUG_PUB.Add('Parse_LA_Select Tag = '|| l_str);
1053
1054
1055 BIS_DEBUG_PUB.Add('< parse_LA_select');
1056 --
1057 EXCEPTION
1058 when FND_API.G_EXC_ERROR then
1059 x_return_status := FND_API.G_RET_STS_ERROR ;
1060 RAISE FND_API.G_EXC_ERROR;
1061 when FND_API.G_EXC_UNEXPECTED_ERROR then
1062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1064 when others then
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1066 BIS_VG_UTIL.Add_Error_Message
1067 ( p_error_msg_id => SQLCODE
1068 , p_error_description => SQLERRM
1069 , p_error_proc_name => G_PKG_NAME||'.parse_LA_select'
1070 , p_error_table => x_error_tbl
1071 , x_error_table => x_error_tbl
1072 );
1073 bis_vg_log.update_failure_log( x_error_tbl
1074 , x_return_status
1075 , x_error_Tbl
1076 );
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078
1079
1080 END parse_LA_select;
1081
1082
1083 -- ============================================================================
1084 -- FUNCTION: check_lookup_exists (PRIVATE FUNCTION)
1085 -- RETURNS: boolean - true if the lookup table exists and has the column named
1086 -- 1. p_table the name of the table
1087 -- 2. p_column the name of the column in the table
1088 --
1089 -- COMMENT : Checks that the column/table combination is defined in the
1090 -- database. Does not ckeck for complete lookup validity.
1091 -- EXCEPTION : None
1092 -- ==========================================================================
1093 FUNCTION CHECK_LOOKUP_EXISTS
1094 ( p_table IN VARCHAR2
1095 , p_column IN VARCHAR2
1096 )
1097 return boolean
1098 is
1099 l_return_value boolean ;
1100 l_dummy number;
1101 l_object_type varchar2(30);
1102 cursor l_object_cursor is
1103 select object_type
1104 from user_objects
1105 where object_name = UPPER(p_table);
1106 cursor l_tab_view_cursor is
1107 select 1
1108 from all_tab_columns
1109 where table_name = UPPER(p_table)
1110 and column_name = UPPER(p_column)
1111 and owner = user;
1112 cursor l_tab_syn_cursor is
1113 select 1
1114 from all_tab_columns a, user_synonyms u
1115 where a.table_name = UPPER(p_table)
1116 and u.table_name = UPPER(p_table)
1117 and a.owner = u.table_owner
1118 and a.column_name = UPPER(p_column) ;
1119 begin
1120 BIS_DEBUG_PUB.Add('> check_lookup_exists');
1121 BIS_DEBUG_PUB.Add('Table = '|| p_table);
1122 BIS_DEBUG_PUB.Add('Column = '|| p_column);
1123 open l_object_cursor;
1124 fetch l_object_cursor into l_object_type;
1125 close l_object_cursor;
1126 if l_object_type='VIEW' then
1127 open l_tab_view_cursor ;
1128 fetch l_tab_view_cursor into l_dummy ;
1129 l_return_value := l_tab_view_cursor%found;
1130 close l_tab_view_cursor ;
1131 BIS_DEBUG_PUB.Add('< check_lookup_exists');
1132 return(l_return_value);
1133 elsif l_object_type='SYNONYM' then
1134 open l_tab_syn_cursor ;
1135 fetch l_tab_syn_cursor into l_dummy ;
1136 l_return_value := l_tab_syn_cursor%found;
1137 close l_tab_syn_cursor ;
1138 BIS_DEBUG_PUB.Add('< check_lookup_exists');
1139 return(l_return_value);
1140 else
1141 BIS_DEBUG_PUB.Add('check_lookup_exists returned '||NVL(l_object_type,'NULL')||'object type');
1142 return FALSE;
1143 end if;
1144
1145 END CHECK_LOOKUP_EXISTS;
1146
1147
1148 -- ============================================================================
1149 --PROCEDURE : put_decode_in_select
1150 -- PARAMETERS:
1151 -- 1. p_View_Select_Table table of varchars to hold select OF view text
1152 -- 2. p_Select_Pointer pointer to the lookup column in select table
1153 -- 3. p_Mode mode of execution of program
1154 -- 3. x_Select_Table table of varchars to hold additional select
1155 -- 4. x_Select_Pointer pointer to the character after the delimiter
1156 -- (select table)
1157 -- 5. x_return_status error or normal
1158 -- 6. x_error_Tbl table of error messages
1159 ----
1160 -- COMMENT : Call this procedure to add a particular lookup select
1161 -- information to a view.
1162 -- EXCEPTION : None
1163 -- ==========================================================================
1164 PROCEDURE put_decode_in_select
1165 ( p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
1166 , p_Select_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
1167 , p_Mode IN NUMBER
1168 , x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
1169 , x_Select_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
1170 , x_return_status OUT VARCHAR2
1171 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1172 )
1173 IS
1174 l_expr VARCHAR2(2000);
1175 l_tag VARCHAR2(3000);
1176 l_lookup_table VARCHAR2(100);
1177 l_lookup_type VARCHAR2(1000);
1178 l_lookup_column VARCHAR2(100);
1179
1180 BEGIN
1181 BIS_DEBUG_PUB.Add('> put_decode_in_select');
1182
1183 parse_LA_select ( p_View_Select_Table
1184 , p_Select_Pointer
1185 , l_expr
1186 , l_lookup_table
1187 , l_lookup_type
1188 , l_lookup_column
1189 , x_Select_Pointer
1190 , x_return_status
1191 , x_error_Tbl
1192
1193 );
1194
1195 l_tag := bis_vg_util.get_string ( p_View_Select_Table
1196 , p_Select_Pointer
1197 , x_Select_Pointer
1198 , x_return_status
1199 , x_error_Tbl
1200 );
1201
1202 IF(p_Mode = bis_vg_types.remove_tags_mode) THEN
1203 x_select_table(1) := 'TO_CHAR(NULL)';
1204 ELSE
1205 -- Check for existence of table and column
1206 IF check_lookup_exists(l_lookup_table, l_lookup_column)
1207 THEN
1208 add_select_info( l_expr
1209 , l_lookup_table
1210 , l_lookup_type
1211 , l_lookup_column
1212 , x_select_table
1213 , x_return_status
1214 , x_error_Tbl
1215 );
1216 ELSE
1217 -- The lookup table either does not exist
1218 -- or does not contain the column demanded
1219 BIS_VG_UTIL.Add_Error_message
1220 ( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_UNDEF_TAB
1221 , p_error_proc_name => G_PKG_NAME||'.put_decode_in_select'
1222 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1223 , p_token1 => 'tab'
1224 , p_value1 => l_lookup_table
1225 , p_token2 => 'col'
1226 , p_value2 => l_lookup_column
1227 , p_token3 => 'tag'
1228 , p_value3 => l_tag
1229 , p_error_table => x_error_tbl
1230 , x_error_table => x_error_tbl
1231 );
1232 bis_vg_log.update_failure_log( x_error_tbl
1233 , x_return_status
1234 , x_error_Tbl
1235 );
1236 RAISE FND_API.G_EXC_ERROR;
1237 END IF;
1238 END IF;
1239 --
1240 BIS_DEBUG_PUB.Add('> put_decode_in_select');
1241 --
1242 EXCEPTION
1243 when FND_API.G_EXC_ERROR then
1244 x_return_status := FND_API.G_RET_STS_ERROR ;
1245 RAISE FND_API.G_EXC_ERROR;
1246 when FND_API.G_EXC_UNEXPECTED_ERROR then
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 when others then
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1251 BIS_VG_UTIL.Add_Error_Message
1252 ( p_error_msg_id => SQLCODE
1253 , p_error_description => SQLERRM
1254 , p_error_proc_name => G_PKG_NAME||'.put_decode_in_select'
1255 , p_error_table => x_error_tbl
1256 , x_error_table => x_error_tbl
1257 );
1258 bis_vg_log.update_failure_log( x_error_tbl
1259 , x_return_status
1260 , x_error_Tbl
1261 );
1262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1263
1264 END put_decode_in_select;
1265 --
1266 /* ============================================================================
1267 PROCEDURE : add_Lookup_Info
1268 PARAMETERS:
1269 1. p_View_Column_Table table of varchars to hold columns OF view text
1270 2. p_View_Select_Table table of varchars to hold SELECT clause of view
1271 3. p_Mode mode of execution of the program
1272 4. p_Column_Pointer pointer to the lookup column in column table
1273 5. p_Select_Pointer pointer to the select clause
1274 6. x_Column_Table table of varchars to hold additional columns
1275 7. x_Select_Table table of varchars to hold additional columns
1276 8. x_Column_Pointer pointer to the character after the delimiter
1277 (column table)
1278 9. x_Select_Pointer pointer to the character after the delimiter
1279 (select table)
1280 10. x_return_status error or normal
1281 11. x_error_Tbl table of error messages
1282 --
1283 COMMENT : Call this procedure to add a particular lookup information
1284 to a view.
1285 EXCEPTION : None
1286 ===========================================================================*/
1287 PROCEDURE add_Lookup_Info
1288 ( p_View_Column_Table IN BIS_VG_TYPES.View_Text_Table_Type
1289 , p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
1290 , p_Mode IN NUMBER
1291 , p_Column_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
1292 , p_Select_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
1293 , x_Column_Table OUT BIS_VG_TYPES.View_Text_Table_Type
1294 , x_Select_Table OUT BIS_VG_TYPES.View_Text_Table_Type
1295 , x_Column_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
1296 , x_Select_Pointer OUT BIS_VG_TYPES.View_Character_Pointer_Type
1297 , x_return_status OUT VARCHAR2
1298 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1299 )
1300 IS
1301 --
1302 BEGIN
1303 BIS_DEBUG_PUB.Add('> add_Lookup_Info :-)');
1304 --
1305 put_column_name( p_view_column_table
1306 , p_column_pointer
1307 , p_Mode
1308 , x_column_table
1309 , x_column_pointer
1310 , x_return_status
1311 , x_error_Tbl
1312 );
1313 --
1314 put_decode_in_select( p_view_select_table
1315 , p_select_pointer
1316 , p_Mode
1317 , x_select_table
1318 , x_select_pointer
1319 , x_return_status
1320 , x_error_Tbl
1321 );
1322 --
1323 -- bis_vg_util.print_view_text(x_column_table, x_return_status, x_error_Tbl);
1324 -- bis_vg_util.print_view_text(x_select_table, x_return_status, x_error_Tbl);
1325 --
1326 BIS_DEBUG_PUB.Add('< add_Lookup_Info :-(');
1327 --
1328
1329
1330 EXCEPTION
1331 when FND_API.G_EXC_ERROR then
1332 x_return_status := FND_API.G_RET_STS_ERROR ;
1333 RAISE FND_API.G_EXC_ERROR;
1334 when FND_API.G_EXC_UNEXPECTED_ERROR then
1335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1337 when others then
1338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1339 BIS_VG_UTIL.Add_Error_Message
1340 ( p_error_msg_id => SQLCODE
1341 , p_error_description => SQLERRM
1342 , p_error_proc_name => G_PKG_NAME||'.add_Lookup_Info'
1343 , p_error_table => x_error_tbl
1344 , x_error_table => x_error_tbl
1345 );
1346 bis_vg_log.update_failure_log( x_error_tbl
1347 , x_return_status
1348 , x_error_Tbl
1349 );
1350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1351
1352 END add_Lookup_Info;
1353 --
1354 END bis_vg_lookup;